/*get AR invoice Lines Detailes */
SELECT DISTINCT
rl.customer_trx_line_id,
DECODE (RCTT.TYPE,
'CB', 'Chargeback',
'CM', 'Credit Memo',
'DM', 'Debit Memo',
'DEP', 'Deposit',
'GUAR', 'Guarantee',
'INV', 'Invoice',
'PMT', 'Receipt',
'Invoice')
INVOICE_CLASS, -- transation type name
RCTT.NAME
"TYPE",
RCTA.TRX_NUMBER
"TRANSACTION NUMBER",
TO_CHAR (RCTA.TRX_DATE, 'YYYY/MM/DD')
"TRANSACTION DATE",
RCTA.INVOICE_CURRENCY_CODE
"Currency Code",
RL.QUANTITY_INVOICED
"Quantity",
RL.LINE_NUMBER
"Line Number",
SUM (RL.UNIT_SELLING_PRICE)
"Unit Price",
RL.QUANTITY_INVOICED * SUM (RL.UNIT_SELLING_PRICE)
" Amount",
RL.DESCRIPTION
"Transaction Line Description",
RCTA.COMMENTS,
RT.NAME,
RCTA.BILL_TO_CUSTOMER_ID
"Original System Bill-to Customer Reference customer id",
-- SUM(RL.AMOUNT_DUE_ORIGINAL) "Transaction Line Amount",
RCTA.CUSTOMER_TRX_ID,
RCTA.TERM_DUE_DATE,
HCA.CUST_ACCOUNT_ID,
HCA.ACCOUNT_NUMBER
CUSTOMER_NUMBER,
HP.PARTY_NAME
CUSTOMER_NAME,
-- NOTE.TEXT ,
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
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_CUSTOMER_TRX_LINES_ALL RL,
RA_CUST_TRX_TYPES_ALL RCTT,
RA_CUST_TRX_LINE_GL_DIST_ALL GLD
WHERE 1 = 1
AND RCTA.ORG_ID = :ORG_ID
AND RCTA.TRX_NUMBER = :TRN_NUMBER
AND RL.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 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 RCTA.TRX_DATE BETWEEN :FROM_DATE AND :TO_DATE -- check month format
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,
RCTA.COMMENTS,
RCTT.TYPE,
RCTT.NAME,
aps.gl_date,
RCTA.BILL_TO_CUSTOMER_ID,
RCTA.INVOICE_CURRENCY_CODE,
RCTA.TERM_DUE_DATE,
HP.PARTY_NAME,
RL.LINE_TYPE,
RL.DESCRIPTION,
RL.AMOUNT_DUE_ORIGINAL,
RL.QUANTITY_INVOICED,
RL.UNIT_SELLING_PRICE,
RCTA.COMMENTS,
RT.NAME,
RL.LINE_NUMBER,
RL.ACCOUNTING_RULE_DURATION,
RL.RULE_END_DATE,
RULE_START_DATE,
rl.customer_trx_line_id
ORDER BY RCTA.TRX_NUMBER, RL.LINE_NUMBER
Read More:
Ahmed Hassan
