You can use the below open receipts query for the following purpose:
Oracle EBS to Fusion data migration – for preparing FBDI sheets.
if user ask you for the open receipts which is not fully paid.
To list out all unapplied receipts.
SELECT
A1.STATUS RECEIPT_STATUS,
A6.DOC_SEQUENCE_VALUE ,
A6.RECEIPT_NUMBER,
A1.AMOUNT_DUE_ORIGINAL RECEIPT_AMOUNT,
A6.RECEIPT_DATE,
A1.GL_DATE ,
A1.EXCHANGE_DATE "Conversion Date",
A1.INVOICE_CURRENCY_CODE "Currency",
A1.EXCHANGE_RATE_TYPE "Conversion Rate Type",
A1.EXCHANGE_RATE,
A6.DOC_SEQUENCE_VALUE "Document Number" ,
A2.CUSTOMER_NAME "Customer Name",
A11.ACCOUNT_NUMBER "Customer Account Number",
A3.SITE_USE_CODE,
A3.SITE_USE_ID,
A8.BANK_ACCOUNT_NUM "Customer Bank Account" ,
A6.UNIQUE_REFERENCE "Key",
NULL "Reference Value" ,
NULL "Reference Type" ,
NULL "Reference Amount",
NULL "Structured Payment Reference" ,
NULL "Customer Reason",
A5.PARTY_ID "Customer Reference PARTY ID",
A1.AMOUNT_DUE_REMAINING UNAPPLIED_AMOUNT,
A1.AMOUNT_APPLIED,
A6.COMMENTS,
A8.BANK_ID ,
A10.BANK_NAME ,
A8.BANK_ACCOUNT_ID ,
A1.DUE_DATE,
A5.PARTY_SITE_NUMBER
FROM
AR_PAYMENT_SCHEDULES_ALL A1,
AR_CUSTOMERS A2,
HZ_CUST_SITE_USES_ALL A3,
HZ_CUST_ACCT_SITES_ALL A4,
HZ_PARTY_SITES A5,
AR_CASH_RECEIPTS_ALL A6,
CE_BANK_ACCOUNTS A8,
CE_BANK_ACCT_USES_ALL A9 ,
CE_BANKS_V A10,
hz_cust_accounts A11
WHERE 1 = 1
AND A11.CUST_ACCOUNT_ID = A4.CUST_ACCOUNT_ID
AND A10.BANK_PARTY_ID = A8.BANK_ID
AND A9.BANK_ACCOUNT_ID (+)= A8.BANK_ACCOUNT_ID
AND A9.BANK_ACCT_USE_ID (+) = A6.REMIT_BANK_ACCT_USE_ID
AND A1.ORG_ID=:P_ORG_ID
AND A1.CUSTOMER_ID=A2.CUSTOMER_ID
AND A1.CASH_RECEIPT_ID=A6.CASH_RECEIPT_ID
AND A1.CLASS='PMT'
AND A1.STATUS='OP'
AND A1.CUSTOMER_SITE_USE_ID=A3.SITE_USE_ID
AND A3.CUST_ACCT_SITE_ID=A4.CUST_ACCT_SITE_ID
AND A4.PARTY_SITE_ID=A5.PARTY_SITE_ID
AND A9.ORG_ID = :ORG_ID
AND A6.RECEIPT_DATE <= :P_MINGTATION_DATE
ORDER BY A6.RECEIPT_NUMBER
Leave a Reply