Link to home
Start Free TrialLog in
Avatar of pardeshirahul
pardeshirahul

asked on

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

SELECT DISTINCT
         poh.org_id,
         poh.currency_code,
         ORDER_TYPE_LOOKUP_CODE,
         PURCHASE_BASIS,
         pov.vendor_name,
         poh.segment1,
         por.closed_code,
         NULL closed_date,
         por.authorization_status,
         TO_CHAR (por.approved_date, 'DD-MON-YYYY'),
         line_num,
         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,
         PL.UNIT_PRICE,
         (SELECT segment1
            FROM mtl_system_items_b msi
           WHERE organization_id = 103 AND PL.ITEM_ID = msi.inventory_item_id)
            item,
         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') <=
                    '06302012')
                    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,
         poh.currency_code,
         ORDER_TYPE_LOOKUP_CODE,
         PURCHASE_BASIS,
         pov.vendor_name,
         poh.segment1,
         por.closed_code,
         NULL,
         por.authorization_status,
         TO_CHAR (por.approved_date, 'DD-MON-YYYY'),
         line_num,
         TO_CHAR (por.creation_date, 'DD-MON-YYYY'),
         TO_CHAR (release_date, 'DD-MON-YYYY'),
         release_num,
         plla.quantity,
         PL.UNIT_PRICE,
         PL.ITEM_ID,
         item_description                            


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,
po-received.xls
Avatar of pardeshirahul
pardeshirahul

ASKER

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

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