Link to home
Start Free TrialLog in
Avatar of pardeshirahul

asked on

need to change the logic for NVL (plla.quantity, 0) - SUM (NVL (rcv.quantity, 0)) remianing_qty,

         NULL closed_date,
         TO_CHAR (por.approved_date, 'DD-MON-YYYY'),
         TO_CHAR (por.creation_date, 'DD-MON-YYYY') release_creation_date,
         TO_CHAR (release_date, 'DD-MON-YYYY') release_date,
         release_num release_num,
         plla.quantity qty_ordered,
         SUM (NVL (rcv.quantity, 0)) qty_recvd,
         NVL (plla.quantity, 0) - SUM (NVL (rcv.quantity, 0)) remianing_qty,
         (SELECT segment1
            FROM mtl_system_items_b msi
           WHERE organization_id = 103 AND PL.ITEM_ID = msi.inventory_item_id)
         item_description                                                  --,
    --  stragg(NVL (rcv.quantity, 0)||' - '||TO_CHAR (rcv.transaction_date, 'DD-MON-YYYY')) receipt_date
    FROM po_line_locations_all plla,
         po_lines_all pl,
         po_headers_all poh,
         rcv_transactions rcv,
         po_releases_all por,
         po_vendors pov
   WHERE     plla.po_line_id = pl.po_line_id
         AND poh.org_id = plla.org_id
         AND poh.org_id = plla.org_id
         AND poh.po_header_id = pl.po_header_id
         AND rcv.po_line_location_id(+) = plla.line_location_id
         AND rcv.transaction_type(+) = 'RECEIVE'
         AND poh.org_id IN (SELECT UNIQUE operating_unit
                              --, a.organization_code, a.organization_name , a.*
                              FROM org_organization_definitions a)
         AND (poh.cancel_flag = 'N' OR poh.cancel_flag IS NULL)
         AND (pl.cancel_flag = 'N' OR pl.cancel_flag IS NULL)
         AND (plla.cancel_flag = 'N' OR plla.cancel_flag IS NULL)
         AND (por.cancel_flag = 'N' OR por.cancel_flag IS NULL)
         AND TRUNC (por.creation_date) BETWEEN TO_DATE ('30-JUN-2011')
                                           AND TO_DATE ('30-JUN-2012')
         AND por.po_release_id = plla.PO_RELEASE_ID
         AND poh.authorization_status <> 'REJECTED'
         AND pov.vendor_id = poh.vendor_id
         AND (NVL (plla.quantity, 0) - NVL (rcv.quantity, 0) > 0
              OR TO_CHAR (NVL (rcv.transaction_date, SYSDATE), 'MMDDYYYY') <=
                    and poh.segment1='119924'
                    and  (SELECT segment1
            FROM mtl_system_items_b msi
           WHERE organization_id = 103 AND PL.ITEM_ID = msi.inventory_item_id)=318918
GROUP BY poh.org_id,
         TO_CHAR (por.approved_date, 'DD-MON-YYYY'),
         TO_CHAR (por.creation_date, 'DD-MON-YYYY'),
         TO_CHAR (release_date, 'DD-MON-YYYY'),

in the output for the 5th record the quantity ordered was 5000
and the quantity received is 15
and the remaining quantity is 4885

but i am only looking for quantity received before 30th june

and the quantity 15 was received in july so some how the system is not calculating the quantity received correctly

i think i have to change  the logic NVL (plla.quantity, 0) - SUM (NVL (rcv.quantity, 0)) remianing_qty,
Avatar of pardeshirahul


can we use some analytical function to restrict only the rcv_transaction_dates before 30-jun-2012
Avatar of johnsone
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial