Credit Memo and its invoice
Finally i have drafted the below simple query with the help of a big query from my team mate.
select A.TRX_NUMBER CMNO,
A.TRX_DATE CMDATE,
O.TRX_NUMBER INVNO,
O.TRX_DATE INVDATE
FROM
RA_CUSTOMER_TRX_ALL A,
RA_CUSTOMER_TRX_ALL O
where
O.CUSTOMER_TRX_ID = A.PREVIOUS_CUSTOMER_TRX_ID
and A.trx_number like &enter_the_invoice_number
the above gives the corressponding the invoice number for a credit memoe with the creation date.
1. select A.TRX_NUMBER CMNO,a.customer_trx_id,
A.TRX_DATE CMDATE,
O.TRX_NUMBER INVNO,
O.TRX_DATE INVDATE,o.customer_trx_id
FROM
RA_CUSTOMER_TRX_ALL A,
RA_CUSTOMER_TRX_ALL O
where
O.CUSTOMER_TRX_ID = A.PREVIOUS_CUSTOMER_TRX_ID
and O.TRX_NUMBER='WVA/SER/13/2011'
2.
select rctl.CUSTOMER_TRX_LINE_ID,rctl.EXTENDED_AMOUNT,rct.customer_trx_id,rctl.PREVIOUS_CUSTOMER_TRX_LINE_ID
from RA_CUSTOMER_TRX_ALL rct,Ra_customer_trx_lines_all rctl
where rct.customer_trx_id =rctl.customer_trx_id
--and nvl(rctl.PREVIOUS_CUSTOMER_TRX_LINE_ID,1)=4783632
and rct.customer_trx_id=a.customer_trx_id
1st query used to get INVOICE NO AND CREDIT MEMO DETAIL AGAINST THAT INVOICE.
IN 2ND QUERY WE GET CREDIT MEMO AMOUNT BY LINE WISE.
No comments:
Post a Comment