Amazon

Thursday, May 30, 2013

How to get CREDIT MEMO DETAIL AGINST INVOICES



Credit Memo and its invoice

I had few difficulties in finding the corresponding invoice number for a credit memo, for one my issue in Oracle incentive compensation and googled for it, but have not found anything useful

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