/* AR Invoice Distribution */
SELECT rl.line_number,
SUM (APS.AMOUNT_DUE_ORIGINAL) "Invoice Orignal Amount",
TO_CHAR (RCTA.TRX_DATE, 'YYYY/MM/DD') "TRANSACTION DATE",
TO_CHAR (gld.gl_date, 'DD/MM/YYYY') "GL Date",
RL.ACCOUNTING_RULE_DURATION,
TO_CHAR (RL.RULE_START_DATE, 'DD/MM/YYYY') RULE_START_DATE,
TO_CHAR (RL.RULE_END_DATE, 'DD/MM/YYYY') RULE_END_DATE,
RCTA.TRX_NUMBER "TRANSACTION NUMBER",
RL.DESCRIPTION,
SUM (NVL (GLD.AMOUNT, 0)) Amount,
gld.percent,
gld.account_class,
GCC.SEGMENT3 "COA Natural Account",
HP.PARTY_NAME CUSTOMER_NAME,
HCA.CUST_ACCOUNT_ID,
HCA.ACCOUNT_NUMBER CUSTOMER_ACCOUNT_NUMBER
FROM RA_CUSTOMER_TRX_ALL RCTA,
AR_PAYMENT_SCHEDULES_ALL APS,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP,
HR_OPERATING_UNITS HOU,
RA_CUSTOMER_TRX_LINES_ALL RL,
RA_CUST_TRX_TYPES_ALL RCTT,
RA_CUST_TRX_LINE_GL_DIST_ALL GLD,
GL_CODE_COMBINATIONS GCC --CODE_COMBINATION_ID
WHERE RCTA.ORG_ID = 313
AND GLD.CUSTOMER_TRX_ID = RL.CUSTOMER_TRX_ID
AND GCC.CODE_COMBINATION_ID = GLD.CODE_COMBINATION_ID
AND RL.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
AND RCTA.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
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.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID
AND RCTA.ORG_ID = RCTT.ORG_ID
AND gld.customer_trx_line_id = rl.customer_trx_line_id
AND gld.gl_date IS NOT NULL
AND RCTA.TRX_DATE BETWEEN :FROM_DATE AND :TO_Date
GROUP BY rcta.trx_number,
GCC.SEGMENT3,
gld.gl_date,
gld.percent,
gld.account_class,
RCTA.TRX_NUMBER,
RL.DESCRIPTION,
HCA.CUST_ACCOUNT_ID,
HCA.ACCOUNT_NUMBER,
HP.PARTY_NAME,
RL.ACCOUNTING_RULE_DURATION,
TO_CHAR (RL.RULE_START_DATE, 'DD/MM/YYYY'),
TO_CHAR (RL.RULE_END_DATE, 'DD/MM/YYYY'),
rl.line_number,
RCTA.TRX_DATE
ORDER BY RCTA.TRX_DATE, gld.account_class
Ahmed Hassan
