This AR Transactions Awaiting Consolidation query retrieves a list of open AR transactions awaiting consolidation for automatic receipt application. It includes details such as invoice number, date, customer, due date, and organization.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves open AR transactions awaiting automatic receipt application and consolidation.
HAOU.NAME ORGANIZATION_NAME,
HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
SUBSTRB(HP.PARTY_NAME, 1, 50) CUSTOMER_NAME,
RCTA.TRX_NUMBER INVOICE_NUMBER,
ABA.NAME BATCH_NAME,
APSA.STATUS STATUS_CODE,
FLV.MEANING STATUS,
RCTA.INVOICE_CURRENCY_CODE CURRENCY_CODE,
SUM(NVL(ARAA.AMOUNT_APPLIED,0)) AMOUNT_APPLIED,
RCTA.TRX_DATE INVOICE_DATE,
APSA.DUE_DATE,
HCSUA.LOCATION
FROM
AR_PAYMENT_SCHEDULES_ALL APSA,
AR_BATCHES_ALL ABA,
RA_CUSTOMER_TRX_ALL RCTA,
AR_RECEIPT_CLASSES ARC,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP,
HZ_CUST_SITE_USES_ALL HCSUA,
HR_ALL_ORGANIZATION_UNITS HAOU,
FND_LOOKUP_VALUES_VL FLV,
AR_RECEIVABLE_APPLICATIONS_ALL ARAA
WHERE
1 = 1
AND APSA.SELECTED_FOR_RECEIPT_BATCH_ID = ABA.BATCH_ID (+)
AND RCTA.CUSTOMER_TRX_ID (+) = APSA.CUSTOMER_TRX_ID
AND RCTA.PAYING_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID (+)
AND HCA.PARTY_ID = HP.PARTY_ID (+)
AND RCTA.PAYING_SITE_USE_ID = HCSUA.SITE_USE_ID (+)
AND HAOU.ORGANIZATION_ID (+) = APSA.ORG_ID
AND APSA.STATUS = 'OP' --indicates payment schedule is open and unpaid.
AND ARC.CREATION_METHOD_CODE IN ('AUTOMATIC', 'BR') --indicates receipts created automatically or by bank reconciliation.
AND FLV.LOOKUP_TYPE = 'PAYMENT_SCHEDULE_STATUS' --filters values for payment schedule status codes.
AND FLV.LOOKUP_CODE = APSA.STATUS
AND APSA.PAYMENT_SCHEDULE_ID = ARAA.APPLIED_PAYMENT_SCHEDULE_ID (+)
GROUP BY
RCTA.TRX_NUMBER,
RCTA.TRX_DATE,
RCTA.INVOICE_CURRENCY_CODE ,
APSA.DUE_DATE,
ABA.NAME,
SUBSTRB(HP.PARTY_NAME, 1, 50),
HCA.ACCOUNT_NUMBER,
HCSUA.LOCATION,
FLV.MEANING,
APSA.STATUS,
HAOU.NAME
ORDER BY HCA.ACCOUNT_NUMBER ,
RCTA.TRX_NUMBER