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)
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)
