Introduction:
This script will used to extract the AR accounted receipts details.
select ‘HEADERS’ “FILE_IDENTIFIER” ,DECODE(ou.name,’IM US BU’,’IM US OU’,’IM CA BU’,’IM Canada OU’,ou.name) “BU_NAME”,
cr.RECEIPT_NUMBER “Receipt Numder”,
cr.type,crh_current.status “Current Receipt Status”,
cr.status,rec_method.name “Method name” ,
cr.COMMENTS “COMMENTS”,
bb.bank_name “Bank Name”,
bb.bank_branch_name “Branch Name” ,
cba.BANK_ACCOUNT_NUM “Bank Account Number” ,
cust.account_number “Customer Account Number” ,
site_uses.location “Site Location” ,
TO_CHAR(cr.receipt_date,’DD-MON-YYYY’) “Receipt Date” ,
TO_CHAR(cr.deposit_date,’DD-MON-YYYY’) “Deposite Date” ,
TO_CHAR(crh_first_posted.gl_date,’DD-MON-YYYY’) “Accounted Date” ,
TO_CHAR(ps.due_date,’DD-MON-YYYY’) “Maturity Date” ,
bat.name “Batch Name”, cr.currency_code “Currency Code”,
cr.amount “Receipt Amount” ,
nvl(-(ps.amount_applied), 0) “Applied Amount” ,
crh_current.acctd_amount “Accounted Amount”,
crh_current.amount “Net Amount”,
cr.reversal_category “Reversal Category”,
cr.reversal_reason_code “Reversal Reason”,
cr.ATTRIBUTE15,
cr.CASH_RECEIPT_ID “Identifier”
from AR_CASH_RECEIPTS_ALL cr,
ar_receipt_methods rec_method ,
CE_BANK_ACCT_USES_ALL remit_bank,
ce_bank_accounts cba,
ce_bank_branches_v bb,
hz_cust_accounts cust,
hz_cust_site_uses_all site_uses,
ar_cash_receipt_history_all crh_first_posted,
ar_batches_all bat,
hr_operating_units ou,
ar_payment_schedules_all ps,
ar_cash_receipt_history_all crh_current
where cr.receipt_method_id = rec_method.receipt_method_id
AND remit_bank.bank_acct_use_id (+) = cr.remit_bank_acct_use_id
AND remit_bank.org_id (+) = cr.org_id
AND remit_bank.bank_account_id = cba.bank_account_id (+)
AND bb.branch_party_id (+) = cba.bank_branch_id
AND cr.pay_from_customer = cust.cust_account_id (+)
AND cr.customer_site_use_id = site_uses.site_use_id (+)
–AND cr.org_id = site_uses.org_id (+)
AND crh_first_posted.cash_receipt_id (+) = cr.cash_receipt_id
AND crh_first_posted.org_id (+) = cr.org_id
AND crh_first_posted.first_posted_record_flag (+) = ‘Y’
AND crh_first_posted.batch_id = bat.batch_id (+)
AND crh_first_posted.org_id = bat.org_id (+)
AND cr.org_id = ou.organization_id
AND ps.cash_receipt_id (+) = cr.cash_receipt_id
AND ps.org_id (+) = cr.org_id
AND crh_current.cash_receipt_id = cr.cash_receipt_id
AND crh_current.org_id = cr.org_id
AND crh_current.current_record_flag = nvl(‘Y’, cr.receipt_number)
AND crh_current.gl_posted_date is not null
AND cr.type not in (‘MISC’)
AND ((cr.receipt_number=NVL(:P_RECEIPT_NUMBER,cr.receipt_number) AND NVL(:P_START_DATE,’1′)=’1′ AND NVL(:P_END_DATE,’1′)=’1′) or (cr.LAST_UPDATE_DATE between NVL(replace(substr ( :P_START_DATE,0,19),’T’,’ ‘),trunc(sysdate)) and NVL(replace(substr ( :P_END_DATE,0,19),’T’,’ ‘),trunc(sysdate-1)) and NVL(:P_RECEIPT_NUMBER,’1′)=’1’))
AND ou.name=NVL(:P_BU_NAME,ou.name)
