How to get All AR open transactions on EBS R12.

Check out our query to get All AR transaction in Oracle EBS R12.

Use this query EBS R12 for the following requirements:

1 – Extract All open Transactions on oracle EBS r12, AR module, then put it on FBDI template for Oracle fusion cloud data migration.

2 – In addition, if user asked you for all ebs transactions.

3 – For Auditing requirements, Just extract the output and share it end user.

---

while implementing Oracle fusion cloud from Oracle ebs r12, you will need to migrate open transactions only, for that run this query, then extract the output on excel sheet, then map it to your FBDI template, and upload it to fusion.

You should review all the output with end user for validation and confirmation.

Pay attention to the customer structure while uploading the results to FBDI, you may need to add more customer accounts if required,

---

SELECT HOU.NAME
ORGANIZATION_NAME,
DECODE (RCTT.TYPE,
'CB', 'Chargeback',
'CM', 'Credit Memo',
'DM', 'Debit Memo',
'DEP', 'Deposit',
'GUAR', 'Guarantee',
'INV', 'Invoice',
'PMT', 'Receipt',
'Invoice') INVOICE_CLASS,
RCTT.NAME "TYPE",
TO_CHAR (RCTA.TRX_DATE , 'YYYY/MM/DD')
INVOICE_DATE,
TO_CHAR (aps.gl_date,'YYYY/MM/DD') "GL DATE",
RCTA.TRX_NUMBER "TRANSACTION NUMBER",
RCTA.BILL_TO_CUSTOMER_ID "Original System Bill-to Customer Reference customer id",
RCTA.SHIP_TO_CUSTOMER_ID "Original System Ship-to Customer Reference",
Null "Transaction Line Description",
RCTA.INVOICE_CURRENCY_CODE "Currency Code",
NULL "Currency Conversion Type",
NULL "Currency Conversion Date",
NULL "Currency Conversion Rate",
1 "Transaction Line Quantity",
SUM (APS.AMOUNT_DUE_REMAINING)
UNIT_SELLING_PRICE,
SUM (APS.AMOUNT_DUE_REMAINING) "Line Transaction",
RCTA.CUSTOMER_TRX_ID,
RCTA.CUST_TRX_TYPE_ID,
RCTA.TERM_DUE_DATE,
HCA.CUST_ACCOUNT_ID,
HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
HP.PARTY_NAME CUSTOMER_NAME,
NOTE.TEXT ,
RCTA.CUSTOMER_TRX_ID,
RCTA.TERM_DUE_DATE,
HCA.CUST_ACCOUNT_ID,
HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
HP.PARTY_NAME CUSTOMER_NAME,
NOTE.TEXT,
RT.NAME,
RCTA.COMMENTS
FROM RA_CUSTOMER_TRX_ALL RCTA,
AR_NOTES NOTE,
AR_PAYMENT_SCHEDULES_ALL APS,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP,
HR_OPERATING_UNITS HOU,
ra_terms rt,
RA_CUST_TRX_TYPES_ALL RCTT
WHERE 1 = 1
AND RCTA.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID
AND RCTA.ORG_ID = RCTT.ORG_ID
AND RCTA.ORG_ID = :P_ORGID -- Your Organization id
AND aps.customer_trx_id = RCTA.CUSTOMER_TRX_ID
AND rt.TERM_ID = RCTA.TERM_ID
AND rt.TERM_ID = APS.TERM_ID
AND RCTA.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
AND RCTA.CUSTOMER_TRX_ID = NOTE.CUSTOMER_TRX_ID(+)
AND APS.STATUS = 'OP'
AND HCA.CUST_ACCOUNT_ID = RCTA.BILL_TO_CUSTOMER_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HOU.ORGANIZATION_ID = RCTA.ORG_ID
AND RCTA.TRX_DATE <= :p_cutoffdate -- all transactions before this date
GROUP BY HOU.NAME,
RCTA.CUSTOMER_TRX_ID,
RCTA.TRX_NUMBER,
RCTA.TRX_DATE,
HCA.CUST_ACCOUNT_ID,
HCA.ACCOUNT_NUMBER,
HP.PARTY_NAME,
RCTA.TERM_DUE_DATE,
NOTE.TEXT,
RT.NAME,
RCTA.COMMENTS,
RCTT.TYPE,
RCTT.NAME,
aps.gl_date,
RCTA.BILL_TO_CUSTOMER_ID,
RCTA.SHIP_TO_CUSTOMER_ID,
RCTA.INVOICE_CURRENCY_CODE,
RCTA.CUST_TRX_TYPE_ID,
RCTA.TERM_DUE_DATE,
HP.PARTY_NAME

Read More:

How to get open receipts for EBS to Fusion data migration.

Query to find all GL transactions on oracle EBS R12, Easy and fast!

How to get AP invoices in Oracle EBS r12 for Oracle Fusion Data migration FBDI, Simple Query

What is the most common issues in implementing ERP systems, & How to avoid it? All you need to know!

https://erpscope365.blogspot.com/

Tweets by erp_scope

https://www.youtube.com/@ERPScope/featured

AR_TRANSACTIONS