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!

Receive Not Deliver in INV Module

Discussion in 'Oracle Apps Technical' started by indrajeet, Dec 3, 2010.

  1. indrajeet

    indrajeet Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi

    This query Receive Not Deliver is coming wrong output.
    I want to only Reccive material which is not Deliver.
    Please give the solution......



    Select rsh.receipt_num receipt_number
    ,to_char(rt.transaction_date,'DD-MON-YYYY') Receipt_date
    ,haou.name org_name
    ,rsh.shipment_header_id
    ,rsl.shipment_line_id
    ,a.transaction_id
    from rcv_shipment_headers rsh
    ,rcv_shipment_lines rsl
    ,hr_all_organization_units haou
    ,rcv_transactions rt
    ,(select k.transaction_id
    from rcv_transactions k
    where k.transaction_type = 'RECEIVE'
    AND trunc(k.transaction_date) between '01-APR-2010' AND '31-OCT-2010'
    MINUS
    select l.parent_transaction_id
    from rcv_transactions l
    where
    l.transaction_type in ('DELIVER','RETURN TO VENDOR','RETURN TO RECEIVING','CORRECT','TRANSFER' )) A
    where a.transaction_id = rt.transaction_id
    and rsh.shipment_header_id = rsl.shipment_header_id
    and rt.shipment_line_id = rsl.shipment_line_id