1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

receiving transaction useful SQL

Discussion in 'Oracle Financials' started by Tomader, Oct 12, 2011.

  1. Tomader

    Tomader Guest

    select sum(receive * po_unit_price)receive
    from
    (
    select r.po_line_id ,
    r.transaction_id ,
    r.quantity ,
    rr.quantity , r.quantity - nvl(rr.quantity,0) receive , r.po_unit_price


    from RCV_TRANSACTIONS r ,
    ---*******
    (select a.Parent , sum(a.quantity)quantity from
    (
    select r.po_header_id,r.po_line_id, r.transaction_id , r.transaction_type ,
    r.quantity , r.parent_transaction_id, SYS_CONNECT_BY_PATH(r.transaction_type , '/') "Path" ,
    SYS_CONNECT_BY_PATH(r.transaction_id , '/') b ,CONNECT_BY_ROOT r.transaction_id Parent ,
    substr (SYS_CONNECT_BY_PATH(r.transaction_type , '/') ,2,7) a , level

    from Rcv_Transactions r
    CONNECT BY PRIOR r.transaction_id = r.parent_transaction_id
    --START WITH r.transaction_type = 'RECEIVE'
    order by r.po_header_id
    )a
    where a.a = 'RECEIVE'
    and a.transaction_type = 'RETURN TO VENDOR'
    group by a.Parent) rr
    ---************

    where r.transaction_type = 'RECEIVE'
    and rr.Parent (+)= r.transaction_id
    and to_date (r.transaction_date,'DD-MON-YYYY' ) between '01-JUL-2010' and '22-SEP-2010' )z