Transaction Reconcile query for Payment,Receipts,Bank Statements QUERY

Transaction Reconcile query for Payment,Receipts,Bank Statements QUERY


SELECT cbe.bank_account_num "BANK_ACCOUNT_NUMBER" /* RECEIPTS QUERY*/
 ,  to_char(acrha.gl_date, 'DD-MON-YYYY') "GL_DATE"
 , 'RECEIPT' "TYPE"
 , hp.party_name "NAME"
 , TO_CHAR(acra.receipt_date, 'DD-MON-YYYY') "TRANSACTION_DATE"
 , TO_CHAR(aps.due_date, 'DD-MON-YYYY') "MATURITY_DATE"
 , acrha.status "STATUS"
 , to_char(acra.receipt_number) "NUMBER"
 , acra.currency_code "CURRENCY"
 , NULL "LINE_NUMBER"
 , acrha.acctd_amount "FUNCTIONAL_AMOUNT"
 , acrha.amount "ENTERED_AMOUNT"
FROM apps.ar_cash_receipts_all acra
 , apps.ar_cash_receipt_history_all acrha
 , apps.hz_cust_accounts hca
 , apps.hz_parties hp
 , apps.ar_payment_schedules_all aps
 , apps.ar_receipt_methods arm
 , apps.ar_receipt_classes arc
 , ce_bank_accounts cbe   
 , ce_bank_acct_uses_all cba
 WHERE acra.cash_receipt_id = acrha.cash_receipt_id
 AND acra.org_id = acrha.org_id
 AND cba.bank_acct_use_id = acra.remit_bank_acct_use_id
 AND cba.bank_account_id = cbe.bank_account_id(+)
 AND EXISTS (select 1 from apps.q_bank_account_reconcile_v where bank_account_number=cbe.bank_account_num and exclude='N')
 AND SYSDATE BETWEEN NVL(cbe.start_date,SYSDATE)  AND NVL(cbe.end_date,SYSDATE)
 AND cba.bank_account_id= NVL (:p_num_bank_account_id, cba.bank_account_id)
 AND trunc(acrha.gl_date) <= trunc(:P_DTE_GL_DATE)
 AND acra.pay_from_customer = hca.cust_account_id(+)
 AND hca.party_id = hp.party_id(+)
 AND acra.cash_receipt_id = aps.cash_receipt_id(+)
 AND acra.receipt_method_id = arm.receipt_method_id
 AND acrha.cash_receipt_history_id =
 (SELECT MAX (cash_receipt_history_id)
 FROM apps.ar_cash_receipt_history_all acrha1
 WHERE acra.cash_receipt_id = acrha1.cash_receipt_id
 AND trunc(acrha1.gl_date) <= trunc(:P_DTE_GL_DATE)
 )
 AND acrha.status NOT IN ('CLEARED', 'REVERSED')
 AND acra.receipt_method_id = arm.receipt_method_id
 AND arm.receipt_class_id = arc.receipt_class_id
 AND arc.notes_receivable <> 'Y'
 UNION ALL
 SELECT distinct cbe.bank_account_num "BANK_ACCOUNT_NUMBER"  /* PAYMENT QUERY*/
 , TO_CHAR (NVL (apc.cleared_date, :P_DTE_GL_DATE), 'DD-MON-YYYY') "GL_DATE"
 ,'PAYMENT' "TYPE"
 , apc.vendor_name "NAME"
 , TO_CHAR(apc.check_date, 'DD-MON-YYYY') "TRANSACTION_DATE"
 , NULL "MATURITY_DATE"
 , apc.status_lookup_code "STATUS"
 , to_char(apc.check_number) "NUMBER"
 , apc.currency_code "CURRENCY"
 , NULL "LINE_NUMBER"
 , NVL (apc.base_amount, apc.amount) "FUNCTIONAL_AMOUNT"
 , apc.amount "ENTERED_AMOUNT"
 FROM ap_checks_all apc
 , FND_LOOKUP_VALUES alc
 , ce_bank_accounts cbe
 , ap_invoice_payments_all aip
 , ce_bank_acct_uses_all cba   
 WHERE 1=1
 AND cba.bank_account_id = NVL (:p_num_bank_account_id, cba.bank_account_id)
 AND apc.status_lookup_code = alc.lookup_code
 and aip.check_id = apc.check_id
 AND alc.lookup_type = 'CHECK STATE'
 AND alc.LANGUAGE = USERENV ('LANG')
 AND alc.VIEW_APPLICATION_ID = 200
 AND cba.bank_acct_use_id = apc.ce_bank_acct_use_id
 AND cba.bank_account_id = cbe.bank_account_id(+)
 AND EXISTS (select 1 from apps.Q_BANK_ACCOUNT_RECONCILE_V where bank_account_number=cbe.bank_account_num and exclude='N')
 AND apc.check_date BETWEEN (SELECT MIN(check_date) FROM apps.ap_checks_all) AND trunc(:P_DTE_GL_DATE)
 UNION ALL
 SELECT cbe.bank_account_num "BANK_ACCOUNT_NUMBER" /* JOURNAL QUERY*/
      ,to_char(:P_DTE_GL_DATE,'DD-MON-YYYY') "GL_DATE"
      ,UPPER(l2.meaning) "TYPE"
      , null "NAME"
      --, TO_CHAR (jel.effective_date, 'DD-MON-YYYY') "TRANSACTION_DATE"
      , TO_CHAR(jel.effective_date, 'DD-MON-YYYY') "TRANSACTION_DATE"
      , NULL "MATURITY_DATE"
      , NULL "STATUS"
      , NULL "NUMBER"
      , jeh.currency_code "CURRENCY"
      , NULL "LINE_NUMBER"
      , DECODE (l2.meaning , 'Debit', (DECODE (NVL (jel.entered_dr , NVL (jel.accounted_dr, 0) ) , 0, jel.accounted_cr , jel.accounted_dr ) ) , 'Credit', -1 *
           (DECODE (NVL (jel.entered_dr, NVL (jel.accounted_dr, 0) ) , 0, jel.accounted_cr , jel.accounted_dr ) ) ) "FUNCTIONAL_AMOUNT"
      , DECODE (l2.meaning , 'Debit', (DECODE (NVL (jel.entered_dr , NVL (jel.accounted_dr, 0) ) , 0, jel.entered_cr , jel.entered_dr ) ) , 'Credit', -1 *
           (DECODE (NVL (jel.entered_dr , NVL (jel.accounted_dr, 0) ) , 0, jel.entered_dr , jel.entered_cr ) ) ) "ENTERED_AMOUNT"
 FROM apps.gl_je_lines jel
    , apps.ce_lookups l2
    , apps.gl_sets_of_books sob
    , apps.ce_system_parameters SYS
    , apps.gl_je_headers jeh
    , apps.ce_statement_reconcils_all cre
    , ce_bank_accounts cbe
 WHERE l2.lookup_type = 'TRX_TYPE'
 AND l2.lookup_code = DECODE (DECODE (NVL (jel.entered_dr, 0) , 0, NVL (jel.accounted_dr, 0) , jel.entered_dr ) , 0, 'JE_CREDIT' , 'JE_DEBIT' )
 AND DECODE (cbe.currency_code , sob.currency_code, jeh.currency_code , cbe.currency_code) = jeh.currency_code
 AND jeh.je_header_id = jel.je_header_id
 AND jeh.je_source NOT IN('Payables', 'Receivables', 'AP Translator', 'AR Translator', 'Treasury')
 AND EXISTS (select 1 from apps.q_bank_account_reconcile_v where bank_account_number=cbe.bank_account_num and exclude='N')
 AND cre.je_header_id(+) = jel.je_header_id
 AND cre.reference_id(+) = jel.je_line_num
 AND cre.reference_type(+) = 'JE_LINE'
 AND NVL (cre.status_flag, 'U') = 'U'
 AND NVL (cre.current_record_flag, 'Y') = 'Y'
 AND '' || jel.status(+) = 'P'
 AND jel.code_combination_id = cbe.asset_code_combination_id
 AND sob.set_of_books_id = SYS.set_of_books_id
 AND jel.ledger_id = SYS.set_of_books_id
 AND jel.effective_date >= SYS.cashbook_begin_date
 AND jeh.je_category <> 'Revaluation'
 AND NOT EXISTS (
 SELECT NULL
 FROM apps.ce_statement_reconcils_all cre2
 , apps.ce_system_parameters sys2
 WHERE jel.je_header_id = cre2.je_header_id
 AND jel.je_line_num = cre2.reference_id
 AND cre2.status_flag = 'M'
 AND NVL (cre2.current_record_flag, 'Y') = 'Y'
 AND sys2.set_of_books_id = jel.ledger_id
 )
 AND cbe.bank_account_id = NVL (:p_num_bank_account_id, cbe.bank_account_id)
 AND trunc(jel.effective_date) <= trunc(:P_DTE_GL_DATE)
UNION ALL
 SELECT cbe.bank_account_num "BANK_ACCOUNT_NUMBER",
       TO_CHAR (csha.gl_date, 'DD-MON-YYYY') "GL_DATE",
       DECODE (csl.trx_type,
               'CREDIT', 'BANK RECEIPT',
               'DEBIT', 'BANK PAYMENT',
               csl.trx_type)
          "TYPE",
       csha.statement_number "NAME",
       TO_CHAR (csl.trx_date, 'DD-MON-YYYY') "TRANSACTION_DATE",
       NULL "MATURITY_DATE",
       DECODE(csl.status,'RECONCILED','PARTIAL RECONCILED',csl.status) "STATUS",
       TO_CHAR (csl.bank_trx_number) "NUMBER",
       csl.currency_code "CURRENCY",
       csl.line_number "LINE_NUMBER",
       DECODE(csl.status,'RECONCILED',(select csl.amount-SUM(cr.amount) from ce_statement_reconcils_all cr where csl.STATEMENT_LINE_ID = cr.STATEMENT_LINE_ID),csl.amount) "FUNCTIONAL_AMOUNT",
      DECODE(csl.status,'RECONCILED',(select csl.amount-SUM(cr.amount) from ce_statement_reconcils_all cr where csl.STATEMENT_LINE_ID = cr.STATEMENT_LINE_ID),csl.amount)  "ENTERED_AMOUNT"
     -- csl.amount "ENTERED_AMOUNT"
  FROM apps.CE_STATEMENT_HEADERS csha,
       apps.ce_statement_lines csl,
       ce_bank_accounts cbe
 WHERE     csha.bank_account_id = cbe.bank_account_id
    AND csha.bank_account_id =
              NVL ( :p_num_bank_account_id, csha.bank_account_id)
       AND TRUNC (csha.statement_date) <= TRUNC ( :P_DTE_GL_DATE)
       AND csha.statement_header_id = csl.statement_header_id
       AND EXISTS
              (SELECT 1
                 FROM apps.q_bank_account_reconcile_v
                WHERE     bank_account_number = cbe.bank_account_num
                      AND exclude = 'N')
       AND NOT EXISTS
                  (SELECT 1
                     FROM ce_statement_lines csl1,
                          ce_statement_reconcils_all csr
                    WHERE     csl1.STATEMENT_HEADER_ID =
                                 csl.STATEMENT_HEADER_ID
                          AND csl1.STATEMENT_LINE_ID = csr.STATEMENT_LINE_ID
                          AND csl1.STATEMENT_LINE_ID = csl.STATEMENT_LINE_ID
                          AND csl1.amount = csr.amount
                          AND csr.status_flag = 'M'
                          AND csr.current_record_flag = 'Y')
       AND UPPER (csha.statement_number) NOT LIKE '%DUMMY ST%'
       AND SYSDATE BETWEEN NVL (cbe.start_date, SYSDATE)
                       AND NVL (cbe.end_date, SYSDATE)

QUERY TO LINK BETWEEN FA , AP AND RCV SHIPMENTS

SELECT DISTINCT gcc.segment1 bc,
         gcc.segment2 rc,
         gcc.segment3 department_code,
         fad.asset_number asset_number,
         fad.attribute_category_code asset_category,
         fad.description asset_desc,
         fab.cost cost,
         fai.po_number,
         fai.invoice_number,
         CONVERT (fai.description, 'AL32UTF8') line_description,
         fai.fixed_assets_cost line_amount,
         aps.segment1 supplier_number,
         aps.vendor_name supplier_name,
         to_char(fab.date_placed_in_service,'DD-MON-YY') date_in_service
         ,fth.transaction_type_code transaction_type
         ,ftv.period_effective effective_period
         ,ftv.period_entered
         ,aia.doc_sequence_value voucher_num
         ,aia.invoice_currency_code invoice_currency
         ,rsh.receipt_num receipt_number
         ,to_char(rsh.creation_date,'DD-MON-YY') receipt_date
    FROM fa_additions_v fad,
         fa_books fab,
         fa_distribution_history fdh,
         gl_code_combinations gcc,
         fa_asset_invoices fai,
         ap_suppliers aps,
         ap_invoices_all aia,
         fa_book_controls bc,
         ap_invoice_distributions_all aida
         ,fa_transaction_headers fth
         ,fa_transaction_history_trx_v ftv
         ,fa_lookups_tl lotl,
         fa_lookups_b flb
         ,rcv_transactions rt
         ,rcv_shipment_headers rsh
   WHERE     fad.asset_id = fdh.asset_id
         AND fab.book_type_code = :p_book_type
         AND fab.Date_ineffective IS NULL
         AND fai.invoice_id = aia.invoice_id
         AND aida.invoice_id = aia.invoice_id
         AND bc.book_type_code = fab.book_type_code
         AND fdh.transaction_header_id_in = fth.transaction_header_id
         AND flb.lookup_code = lotl.lookup_code
         AND flb.lookup_code = fth.transaction_type_code
         AND rt.transaction_id = aida.rcv_transaction_id
         AND rt.shipment_header_id = rsh.shipment_header_id
         AND ftv.transaction_header_id = fth.transaction_header_id
         AND fdh.date_ineffective IS NULL   
         AND(( (:p_from_period = :p_to_period) AND
                    fdh.date_effective < (NVL((SELECT dp.period_close_date
                   FROM fa_deprn_periods dp
                  WHERE dp.book_type_code = :p_book_type
                        AND dp.period_name = :p_to_period),SYSDATE)))
                OR ( fdh.date_effective between
                (SELECT dp.period_open_date
                   FROM fa_deprn_periods dp
                  WHERE dp.book_type_code = :p_book_type
                        AND dp.period_name = :p_from_period)
            AND NVL((SELECT dp.period_close_date
                   FROM fa_deprn_periods dp
                  WHERE dp.book_type_code = :p_book_type
                        AND dp.period_name = :p_to_period),SYSDATE)) )     
         AND fab.book_type_code = fdh.book_type_code
         AND fab.asset_id = fdh.asset_id
         AND fdh.code_combination_id = gcc.code_combination_id
AND fad.asset_type = DECODE(:p_asset_type,'ALL', fad.asset_type,:p_asset_type)
         AND gcc.segment1 = DECODE (:p_chr_bc, 'ALL', gcc.segment1, :p_chr_bc)
         AND gcc.segment2 = DECODE (:p_chr_rc, 'ALL', gcc.segment2, :p_chr_rc)
         AND fdh.asset_id = fai.asset_id(+)
         AND fai.date_ineffective IS NULL
         AND aps.vendor_id(+) = fai.po_vendor_id
ORDER BY fad.asset_number