CE General Ledger Reconciliation - draft

Description
Categories: BI Publisher, Financials
Application: Cash Management
Source: General Ledger Reconciliation Report (XML) - Not Supported: Reserved For Future Use
Short Name: CEXRECRE_XML
DB package: CE_CEXRECRE_XMLP_PKG
SELECT  'RECEIPT'                                                       C_AR_TYPE,
	DECODE(:C_BANK_CURR_DSP, :C_GL_CURRENCY_CODE,
	DECODE(crh.status, 'REVERSED', - crh.acctd_amount, crh.acctd_amount),
	DECODE(crh.status, 'REVERSED', - crh.amount, crh.amount))   C_AR_AMOUNT,
	hz.party_name		C_AR_CUSTOMER_NAME,
	crh.gl_date			C_AR_GL_DATE,
	cr.receipt_date		C_AR_REMIT_DATE,
	arm.name			C_AR_PAYMENT_METHOD,
	cr.receipt_number		C_AR_RECEIPT_NUMBER,
	cr.currency_code		C_AR_CURRENCY,
	cr.amount			C_AR_TRANS_AMOUNT,
	crh.status			C_AR_STATUS,
	fnd_access_control_util.get_org_name(cr.org_id)		C_ORG_NAME_AR
FROM    ar_cash_receipts cr,
	ar_cash_receipt_history crh,
	hz_cust_accounts cu,
                hz_parties hz,
	ar_receipt_methods arm, 
	ce_bank_acct_uses_ou_v bau,
	ce_bank_accounts  ba,	
	ce_GL_ACCOUNTS_CCID gac,
	ce_system_parameters  sys
WHERE   cr.cash_receipt_id = crh.cash_receipt_id
 and     cr.remit_bank_acct_use_id = bau.bank_acct_use_id
AND	gac.bank_acct_use_id = bau.bank_acct_use_id
AND bau.bank_account_id = :P_BANK_ACCOUNT_ID
and bau.org_id = cr.org_id
and bau.bank_account_id = ba.bank_account_id
and ba.account_owner_org_id = sys.legal_entity_id
AND     crh.account_code_combination_id = gac.ar_asset_ccid  AND     crh.status in ('REMITTED', 'CLEARED', 'REVERSED')
AND     crh.gl_date <= :C_AS_OF_DATE
AND     crh.gl_date >= sys.cashbook_begin_date AND     crh.gl_posted_date is not null
AND     crh.current_record_flag = 'Y'
AND     cu.cust_account_id(+) = cr.pay_from_customer
AND     hz.party_id(+) = cu.party_id
AND     arm.receipt_method_id = cr.receipt_method_id
AND     nvl(cr.status, 'X') = decode(nvl(cr.status,'X'),'REV','U',nvl(cr.status,'X'))
AND NOT EXISTS
   (select null
    from ce_statement_recon_gt_v sr,
         ce_statement_lines sl,
         ce_statement_headers sh
    where sr.reference_id = crh.cash_receipt_history_id
    and   sr.reference_type = 'RECEIPT'
    and   sr.status_flag = 'M'
    and   sr.current_record_flag = 'Y'
    and   sl.statement_line_id = sr.statement_line_id
    and   sl.statement_header_id = sh.statement_header_id
    and   sh.bank_account_id = :P_BANK_ACCOUNT_ID
	and   sh.statement_date <= :C_AS_OF_DATE
	and   sh.statement_date >= sys.cashbook_begin_date     UNION
select null
from ce_statement_recon_gt_v sr,
         ce_statement_lines sl,
         ce_statement_headers sh,
         ar_cash_receipt_history      crh_rc 
    where sr.reference_id = crh_rc.cash_receipt_history_id
    and   sr.reference_type = 'RECEIPT'
    and   sr.status_flag = 'M'
    and   sr.current_record_flag = 'Y'
    and   sl.statement_line_id = sr.statement_line_id
    and   sl.statement_header_id = sh.statement_header_id
    and   sh.bank_account_id = :P_BANK_ACCOUNT_ID
	and   sh.statement_date <= :C_AS_OF_DATE
	and   sh.statement_date >= sys.cashbook_begin_date     and   crh_rc.cash_receipt_id = cr.cash_receipt_id )
UNION ALL
SELECT 'RECEIPT'                                                       C_AR_TYPE,
	DECODE(:C_BANK_CURR_DSP, :C_GL_CURRENCY_CODE,  crh.acctd_amount,  crh.amount)   C_AR_AMOUNT,
	hz.party_name		C_AR_CUSTOMER_NAME,
	crh.gl_date			C_AR_GL_DATE,
	cr.receipt_date		C_AR_REMIT_DATE,
	arm.name			C_AR_PAYMENT_METHOD,
	cr.receipt_number		C_AR_RECEIPT_NUMBER,
	cr.currency_code		C_AR_CURRENCY,
	cr.amount			C_AR_TRANS_AMOUNT,
	crh.status			C_AR_STATUS,
	fnd_access_control_util.get_org_name(cr.org_id)		C_ORG_NAME_AR
FROM    ar_cash_receipts cr,
	ar_cash_receipt_history crh2,
	ar_cash_receipt_history crh,
	hz_cust_accounts cu,
                hz_parties  hz,
	ar_receipt_methods arm, 
	ce_bank_acct_uses_ou_v bau,
	ce_bank_accounts	ba,
	ce_GL_ACCOUNTS_CCID gac,
	ce_system_parameters sys
WHERE   cr.cash_receipt_id = crh.cash_receipt_id
and     cr.remit_bank_acct_use_id = bau.bank_acct_use_id
AND	gac.bank_acct_use_id = bau.bank_acct_use_id
AND bau.bank_account_id = :P_BANK_ACCOUNT_ID
and bau.org_id = cr.org_id
and bau.bank_account_id = ba.bank_account_id
and ba.account_owner_org_id = sys.legal_entity_id
AND     crh.account_code_combination_id = gac.ar_asset_ccid AND     crh.status in ('REMITTED', 'CLEARED')
AND     crh.gl_date <= :C_AS_OF_DATE
AND     crh.gl_date >= sys.cashbook_begin_date AND     crh.gl_posted_date is not null
AND     crh2.cash_receipt_id = crh.cash_receipt_id
AND     crh2.cash_receipt_history_id = crh.reversal_cash_receipt_hist_id
AND     crh2.status = 'REVERSED'
AND     cu.cust_account_id(+) = cr.pay_from_customer
AND     hz.party_id(+) = cu.party_id
AND     arm.receipt_method_id = cr.receipt_method_id
AND     nvl(cr.status, 'X') = decode(nvl(cr.status,'X'),'REV','U',nvl(cr.status,'X'))
AND NOT EXISTS
   (select null
    from ce_statement_recon_gt_v sr,
         ce_statement_lines sl,
         ce_statement_headers sh
    where sr.reference_id = crh2.cash_receipt_history_id
    and   sr.reference_type = 'RECEIPT'
    and   sr.status_flag = 'M'
    and   sr.current_record_flag = 'Y'
    and   sl.statement_line_id = sr.statement_line_id
    and   sl.statement_header_id = sh.statement_header_id
    and   sh.bank_account_id = :P_BANK_ACCOUNT_ID
	and   sh.statement_date <= :C_AS_OF_DATE
	and   sh.statement_date >= sys.cashbook_begin_date)
	ORDER BY C_ORG_NAME_AR, C_AR_GL_DATE, C_AR_CUSTOMER_NAME
Parameter Name SQL text Validation
Report Type
 
LOV Oracle
Closing Balance
 
Number
Period Name
 
LOV Oracle
Bank Account Number
 
LOV Oracle