This AR intercompany receipts query retrieves detailed information on intercompany cash receipts in Oracle Fusion, including ledger, organization, customer, receipt, applied amount, and other details related to transactions.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves detailed information on intercompany cash receipts
GL.NAME LEDGER_NAME,
HAOU.NAME ORGANIZATION_NAME,
ACRA.RECEIPT_NUMBER,
ACRA.RECEIPT_DATE,
HP.PARTY_NAME CUSTOMER_NAME,
HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
ACRA.CURRENCY_CODE,
ACRA.AMOUNT RECEIPT_AMOUNT,
ARAA.AMOUNT_APPLIED,
ARAA.GL_DATE,
DECODE(ACRA.STATUS,'APP', 'APPLIED','UNAPP','UNAPPLIED','UNID','UNIDENTIFIED','REV','REVERSE','NSF','INSUFFICIENT FUNDS',ACRA.STATUS) RECEIPT_STATUS,
ACRA.TYPE,
RCTA.TRX_NUMBER TRANSACTION_NUMBER,
RCTA.TRX_DATE TRANSACTION_DATE,
DECODE(RCTA.STATUS_TRX,'OP','Open',RCTA.STATUS_TRX) TRANSACTION_STATUS,
RCTA.APPROVAL_CODE TRANSACTION_APPROVAL_CODE,
RCTA.SOURCE_DOCUMENT_TYPE,
RCTA.TRX_CLASS TRANSACTION_CLASS,
RCTA.DELIVERY_METHOD_CODE,
GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5 GL_ACCOUNT
FROM
AR_CASH_RECEIPTS_ALL ACRA,
AR_RECEIVABLE_APPLICATIONS_ALL ARAA,
RA_CUSTOMER_TRX_ALL RCTA,
AR_PAYMENT_SCHEDULES_ALL APSA,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP,
GL_CODE_COMBINATIONS GCC,
HR_ALL_ORGANIZATION_UNITS HAOU,
GL_LEDGERS GL
WHERE
1 = 1
AND ACRA.CASH_RECEIPT_ID = ARAA.CASH_RECEIPT_ID
AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
AND RCTA.CUSTOMER_TRX_ID = APSA.CUSTOMER_TRX_ID
AND ACRA.PAY_FROM_CUSTOMER = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID=HP.PARTY_ID
AND ARAA.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND ACRA.ORG_ID=HAOU.ORGANIZATION_ID
AND ACRA.SET_OF_BOOKS_ID=GL.LEDGER_ID
AND RCTA.INTERCOMPANY_FLAG = 'Y'