Introduction:
This script will used to extract the AR partial credit memo’s with reference invoice details.
select c.trx_number CM_Number,
c.trx_date CM_DATE,
(select a1.account_number from hz_cust_accounts_all a1 where a1.cust_account_id=c.bill_to_customer_id)CM_Customer_Number,
d.line_type CM_LINE_TYPE,
d.interface_line_attribute3 CM_LINE_NUM,
d.description CM_DESCRIPTION,
d.extended_amount CM_AMOUNT,
d.attribute6 CM_ATTRIBUTE6,
a.trx_number REF_INV_NUM,
a.trx_date REF_INV_DATE,
(select a2.account_number from hz_cust_accounts_all a2 where a2.cust_account_id=a.bill_to_customer_id)REF_INV_Customer_Number,
b.line_type REF_INV_TYPE,
b.interface_line_attribute3 REF_INV_LINE_NUM,
b.description REF_INV_DESC,
b.attribute6 REF_INV_ATTR6,
B.extended_amount REF_INV_AMOUNT
from ra_customer_trx_all a ,
ra_customer_trx_lines_all b,
ra_customer_trx_all c,
ra_customer_trx_lines_all d
where 1=1
and a.customer_trx_id=b.customer_trx_id
and a.customer_trx_id=c.previous_customer_trx_id
and b.customer_trx_line_id=d.previous_customer_trx_line_id(+)
and c.trx_number IN (‘XXXX’);
