Basic SQLs – AR Invoice, Receipt, Receipt Application

 

Business Need:

Oracle EBS Financials

AR – Account Receivables

Analysis, Troubleshooting, Reporting, Bug Fixing, User Tracking , <you name it!>

 

Tables:

RA_CUSTOMER_TRX_ALL TRX – AR Transactions (=Customer Invoices)

AR_CASH_RECEIPTS_ALL RCT – AR Receipts

AR_RECEIVABLE_APPLICATIONS_ALL APP – AR Receipt Applications

 

Receipts have Many-to-Many relationship with Transactions. I.e. a single invoice can be applied with 10 receipts. Also a single receipt can be used against 10 invoices.

 

SQLs:

 
SELECT RCT.RECEIPT_NUMBER, TRX.TRX_NUMBER INVOICE_NUM, TRX.CT_REFERENCE SALES_ORDER, TRX.CUSTOMER_TRX_ID, RCT.CASH_RECEIPT_ID, APP.RECEIVABLE_APPLICATION_ID, APP.AMOUNT_APPLIED, TO_CHAR(TRX.CREATION_DATE,'MonDD hh24mi') TRX_CREATION_DATE, TO_CHAR(RCT.CREATION_DATE,'MonDD hh24mi') RCT_CREATION_DATE
    --,  TRX.*, RCT.*, APP.*
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP, RA_CUSTOMER_TRX_ALL TRX, AR_CASH_RECEIPTS_ALL RCT
WHERE APP.APPLIED_CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND TRX.CT_REFERENCE IN ( '4955')
--AND RCT.CREATION_DATE > SYSDATE-1.02
--AND RCT.RECEIPT_NUMBER = '1000026'
--AND TRX.TRX_NUMBER = '20000151'
--AND RCT.CASH_RECEIPT_ID = 1000128
AND APP.CASH_RECEIPT_ID = RCT.CASH_RECEIPT_ID;
 
 
SELECT RCT.RECEIPT_NUMBER, TRX.TRX_NUMBER INVOICE_NUM, TRX.CT_REFERENCE SALES_ORDER, TRX.CUSTOMER_TRX_ID, RCT.CASH_RECEIPT_ID, TO_CHAR(RCT.CREATION_DATE,'MonDD hh24mi') RCT_CREATION_DATE, TO_CHAR(TRX.CREATION_DATE,'MonDD hh24mi') TRX_CREATION_DATE
    -- ,  TRX.*, RCT.*
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP, RA_CUSTOMER_TRX_ALL TRX, AR_CASH_RECEIPTS_ALL RCT
WHERE APP.APPLIED_CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND RCT.CREATION_DATE > SYSDATE-1.02
--AND RCT.RECEIPT_NUMBER = '1000026'
--AND TRX.TRX_NUMBER = '20000151'
--AND TRX.CT_REFERENCE = '4958'
--AND RCT.CASH_RECEIPT_ID = 1000128
AND APP.CASH_RECEIPT_ID = RCT.CASH_RECEIPT_ID;
 
SELECT ARC.RECEIPT_NUMBER, TRX_NUMBER INVOICE_NUM, CT_REFERENCE SALES_ORDER, RAC.*, ARC.*
  FROM AR_RECEIVABLE_APPLICATIONS_ALL APP,       RA_CUSTOMER_TRX_ALL            RAC,        AR_CASH_RECEIPTS_ALL           ARC
 WHERE APP.APPLIED_CUSTOMER_TRX_ID = RAC.CUSTOMER_TRX_ID
   AND APP.CASH_RECEIPT_ID = ARC.CASH_RECEIPT_ID
   AND ARC.RECEIPT_NUMBER = '1000026';
 
 
-- get order number from TX127 ... Merchant Ref Number
 
SELECT NVL(MAX(RAC.CT_REFERENCE),0) SALES_ORDER
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP, RA_CUSTOMER_TRX_ALL RAC, AR_CASH_RECEIPTS_ALL ARC, IBY_FNDCPT_TX_EXTENSIONS IFTE
WHERE APP.APPLIED_CUSTOMER_TRX_ID = RAC.CUSTOMER_TRX_ID
AND IFTE.ORDER_ID = ARC.RECEIPT_NUMBER
AND IFTE.ORIGIN_APPLICATION_ID = 222
AND IFTE.TRXN_EXTENSION_ID = REGEXP_REPLACE('AR127248', '[^0-9]+', '') 
AND APP.CASH_RECEIPT_ID = ARC.CASH_RECEIPT_ID;
 

Keywords:

Oracle EBS, R12, R12.2.8, AR, Accounts Receivables, Financials, Oracle Applications, Query, SQL