This AR Adjustment Approval query retrieves details of approved adjustments made to receivables (invoices) in Oracle AR, including adjustment status, amount, reason, customer, and invoice details.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves customer adjustment details.
HAOU.NAME ORGANIZATION_NAME,
RCTA.TRX_NUMBER TRANSACTION_NUMBER,
SUBSTRB (HP.PARTY_NAME, 1, 50) CUSTOMER_NAME,
HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
RCTT.NAME TRANSACTION_TYPE,
ARAD.ADJUSTMENT_NUMBER,
ARTA.NAME ADJUSTMENT_NAME,
AL.MEANING ADJUSTMENT_REASON,
AL2.MEANING ADJUSTMENT_APPROVAL_STATUS,
--NVL (GL.CURRENCY_CODE, 'USD') FUNCTIONAL_CURRENCY,
APSA.INVOICE_CURRENCY_CODE CURRENCY_CODE,
ROUND (ARAD.ACCTD_AMOUNT, 2) ACCOUNTED_AMOUNT,
PU.USERNAME ADJUSTMENT_CREATED_BY,
PU1.USERNAME ADJUSTMENT_UPDATED_BY,
ARAD.APPROVED_BY ADJUSTMENT_APPROVED_BY,
APSA.DUE_DATE,
TO_CHAR (ARAD.GL_DATE, 'DD-MON-RR') GL_DATE
--ROUND (ARAD.ACCTD_AMOUNT, 2) ADJUSTMENT_AMOUNT_FUNC,
--FC.PRECISION
FROM
AR_ADJUSTMENTS_ALL ARAD,
AR_RECEIVABLES_TRX_ALL ARTA,
AR_PAYMENT_SCHEDULES_ALL APSA,
RA_CUSTOMER_TRX_ALL RCTA,
RA_CUST_TRX_TYPES_ALL RCTT,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP,
GL_LEDGERS GL,
AR_SYSTEM_PARAMETERS_ALL ASPA,
AR_LOOKUPS AL,
AR_LOOKUPS AL2,
FND_CURRENCIES FC,
PER_USERS PU,
PER_USERS PU1,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND ARAD.RECEIVABLES_TRX_ID = ARTA.RECEIVABLES_TRX_ID
AND ARAD.PAYMENT_SCHEDULE_ID = APSA.PAYMENT_SCHEDULE_ID
AND APSA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
AND RCTA.CUST_TRX_TYPE_SEQ_ID = RCTT.CUST_TRX_TYPE_SEQ_ID
AND APSA.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND GL.LEDGER_ID = ASPA.SET_OF_BOOKS_ID
AND RCTA.SET_OF_BOOKS_ID = ASPA.SET_OF_BOOKS_ID
AND ARAD.REASON_CODE = AL.LOOKUP_CODE(+)
AND AL.LOOKUP_TYPE(+) = 'ADJUST_REASON'
AND ARAD.STATUS = AL2.LOOKUP_CODE
AND AL2.LOOKUP_TYPE = 'APPROVAL_TYPE'
AND ARAD.STATUS != 'U'
AND GL.CURRENCY_CODE = FC.CURRENCY_CODE
AND PU.USERNAME = ARAD.CREATED_BY
AND PU1.USERNAME = ARAD.LAST_UPDATED_BY
AND HAOU.ORGANIZATION_ID = RCTA.ORG_ID