troubleshooting Question

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

Avatar of pardeshirahul
pardeshirahul asked on
Oracle Database
4 Comments1 Solution860 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
johnsone
Senior Oracle DBA

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros