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
Oracle Database

Avatar of undefined
Last Comment
johnsone

8/22/2022 - Mon
pardeshirahul

ASKER
pardeshirahul

ASKER
pardeshirahul

ASKER
can we use some analytical function to restrict only the rcv_transaction_dates before 30-jun-2012
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
johnsone

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question