Thursday, September 6, 2012

Query to get the discrepancy between the sales orders and purchase orders in Drop ship orders


  SELECT osto.name oe_customer_name,
         osto.customer_number oe_customer_number,
         oeh.header_id oe_header_id,
         oeh.order_number oe_number,
         OTYPE.name oe_type,
         oeh.ordered_date oe_order_date,
         oel.line_id oe_line_id,
         oel.line_number
         || DECODE (oel.shipment_number,
                    NULL, NULL,

                    '.' || oel.shipment_number)
         || DECODE (oel.option_number, NULL, NULL, '.' || oel.option_number)
            line_shipment_option_number,
         oel.item_identifier_type,
         OEL.INVENTORY_ITEM_ID,
         OEL.ORDERED_ITEM_ID,
         OEL.ORDERED_ITEM,
         poh.segment1 po_number,
         pol.line_num po_line_number,
         porel.release_num po_release_number,
         poll.shipment_num po_shipment_number,
         porh.segment1 requisition_number,
         porl.line_num requisition_line_number,
         msi1.description po_item_description,
         msi2.description rq_item_description,
         DECODE (
            oel.line_set_id,
            NULL, (SELECT ordered_quantity
                     FROM oe_order_lines_all
                    WHERE     line_id = oel.line_id
                          AND line_set_id IS NULL
                          AND NVL (org_id, 0) = NVL (oel.org_id, 0)),
            (SELECT ordered_quantity
               FROM oe_order_lines_all
              WHERE     line_set_id = oel.line_set_id
                    AND line_id = oel.line_id
                    AND line_set_id IS NOT NULL
                    AND NVL (org_id, 0) = NVL (oel.org_id, 0)))
            oe_quantity,
         poll.quantity - NVL (poll.quantity_cancelled, 0) po_shipment_quantity,
         porl.quantity - NVL (porl.quantity_cancelled, 0) rq_quantity,
         oel.order_quantity_uom oe_unit,
         muom1.uom_code po_unit,
         muom2.uom_code rq_unit,
         oel.schedule_ship_date oe_schedule_date,
         poll.need_by_date po_needby_date,
         porl.need_by_date rq_needby_date,
         zloc_oe.address1 oe_ship_to_location,
         NVL (rloc_po.location_CODE, zloc_po.address1) po_ship_to_location,
         NVL (rloc_rq.location_code, zloc_rq.address1) rq_ship_to_location,
         DECODE (poh.user_hold_flag, 'Y', 'Y', pol.user_hold_flag) po_hold
    FROM mtl_system_items_vl msi,
         mtl_system_items_vl msi1,
         mtl_system_items_vl msi2,
         mtl_units_of_measure muom1,
         mtl_units_of_measure muom2,
         po_line_locations_all poll,
         po_releases porel,
         po_lines_all pol,
         po_headers_all poh,
         po_requisition_lines_all porl,
         po_requisition_headers_all porh,
         oe_order_lines_all oel,
         OE_TRANSACTION_TYPES_TL OTYPE,
         oe_order_headers oeh,
         oe_drop_ship_sources oes,
         oe_sold_to_orgs_v osto,
         HR_LOCATIONS rloc_po,
         HR_LOCATIONS rloc_rq,
         HZ_LOCATIONS zloc_oe,
         HZ_LOCATIONS zloc_po,
         HZ_LOCATIONS zloc_rq,
         HZ_PARTY_SITES party,
         HZ_CUST_ACCT_SITES_ALL acct,
         HZ_CUST_SITE_USES_ALL cust
   WHERE OEH.HEADER_ID = OES.HEADER_ID
         AND NVL (oeh.org_id, 0) = NVL (:p_organization_id, 0)
         AND NVL (oel.org_id, NVL (:p_organization_id, 0)) =
                NVL (:p_organization_id, 0)
         AND oeh.sold_to_org_id = osto.customer_id(+)
         AND OTYPE.TRANSACTION_TYPE_ID = OEH.ORDER_TYPE_ID
         AND OTYPE.language = USERENV ('lang')
         AND oes.line_id = oel.line_id(+)
         AND MSI.INVENTORY_ITEM_ID = PORL.ITEM_ID
         AND NVL (msi.organization_id, 0) = :c_master_org
         AND poh.po_header_id(+) = oes.po_header_id
         AND pol.po_line_id(+) = oes.po_line_id
         AND msi1.inventory_item_id(+) = pol.item_id
         AND NVL (msi1.organization_id, NVL (:c_master_org, 0)) = :c_master_org
         AND msi2.inventory_item_id(+) = porl.item_id
         AND NVL (msi2.organization_id, 0) = :c_master_org
         AND poll.line_location_id(+) = oes.line_location_id
         AND porh.requisition_header_id(+) = oes.requisition_header_id
         AND porl.requisition_line_id(+) = oes.requisition_line_id
         AND porel.po_release_id(+) = poll.po_release_id
         AND muom1.unit_of_measure(+) = pol.unit_meas_lookup_code
         AND muom2.unit_of_measure(+) = porl.unit_meas_lookup_code
         AND rloc_po.location_id(+) = poll.ship_to_location_id
         AND rloc_rq.location_id(+) = porl.deliver_to_location_id
         AND zloc_po.location_id(+) = poll.ship_to_location_id
         AND zloc_rq.location_id(+) = porl.deliver_to_location_
         AND CUST.SITE_USE_ID(+) = oel.ship_to_org_id
         AND NVL (CUST.STATUS, 'A') = 'A'
         AND NVL (CUST.SITE_USE_CODE, 'SHIP_TO') = 'SHIP_TO'
         AND ACCT.CUST_ACCT_SITE_ID(+) = CUST.CUST_ACCT_SITE_ID
         AND PARTY.PARTY_SITE_ID(+) = ACCT.PARTY_SITE_ID
         AND zloc_oe.location_id(+) = PARTY.location_id
         AND (oes.po_header_id IS NOT NULL
              AND (pol.item_id != oel.inventory_item_id
                   OR TRUNC (poll.need_by_date) !=
                         TRUNC (oel.schedule_ship_date)
                   OR poll.ship_to_location_id != PARTY.LOCATION_ID
                   OR muom1.uom_code != oel.order_quantity_uom
                   OR DECODE (
                         0,
                         0, (SELECT SUM (ordered_quantity)
                               FROM oe_order_lines_all
                              WHERE     line_set_id = oel.line_set_id
                                    AND line_set_id IS NOT NULL
                                    AND NVL (org_id, 0) = NVL (oel.org_id, 0))) NOT IN
                         (SELECT SUM (quantity)
                            FROM po_line_locations_all poll2,
                                 oe_drop_ship_sources oes2
                           WHERE poll2.line_location_id = oes2.line_location_id
                                 AND oes2.line_id = oel.line_id)
                   OR DECODE (
                         0,
                         0, (SELECT ordered_quantity
                               FROM oe_order_lines_all
                              WHERE     line_id = oel.line_id
                                    AND line_set_id IS NULL
                                    AND NVL (org_id, 0) = NVL (oel.org_id, 0))) NOT IN
                         (SELECT SUM (quantity)
                            FROM po_line_locations_all poll2,
                                 oe_drop_ship_sources oes2
                           WHERE poll2.line_location_id = oes2.line_location_id
                                 AND oes2.line_id = oel.line_id)
                   OR DECODE (poh.user_hold_flag,
                              'Y', 1,
                              DECODE (pol.user_hold_flag, 'Y', 1, 0)) NOT IN
                         (SELECT DECODE (COUNT (order_hold_id), 0, 0, 1)
                            FROM oe_order_holds_all orh,
                                 oe_hold_sources_all ohs,
                                 oe_hold_definitions ohd
                           WHERE     orh.header_id = oel.header_id
                                 AND orh.hold_release_id IS NULL
                                 AND orh.hold_source_id = ohs.hold_source_id
                                 AND ohs.hold_id = ohd.hold_id
                                 AND ohd.item_type IS NULL
                                 AND ohd.activity_name IS NULL
                                 AND (orh.line_id = oel.line_id
                                      OR orh.line_id IS NULL)))
              OR (oes.po_header_id IS NULL
                  AND oes.requisition_header_id IS NOT NULL
                  AND (DECODE (oel.inventory_item_id, NULL, -98, porl.item_id) !=
                          NVL (oel.inventory_item_id, -99)
                       OR TRUNC (
                             DECODE (oel.schedule_ship_date,
                                     NULL, SYSDATE,
                                     porl.need_by_date)) !=
                             TRUNC (NVL (oel.schedule_ship_date, SYSDATE - 1))
                       OR NVL (oel.ship_to_org_id, -98) !=
                             NVL (oel.ship_to_org_id, -99)
                       OR porl.deliver_to_location_id != PARTY.LOCATION_ID
                       OR muom2.uom_code != oel.order_quantity_uom
                       OR porl.quantity !=
                             DECODE (
                                0,
                                0, (SELECT SUM (ordered_quantity)
                                      FROM oe_order_lines_all
                                     WHERE line_set_id = oel.line_set_id
                                           AND line_set_id IS NOT NULL
                                           AND NVL (org_id, 0) =
                                                  NVL (oel.org_id, 0)))
                       OR porl.quantity !=
                             DECODE (
                                0,
                                0, (SELECT ordered_quantity
                                      FROM oe_order_lines_all
                                     WHERE line_id = oel.line_id
                                           AND line_set_id IS NULL
                                           AND NVL (org_id, 0) =
                                                  NVL (oel.org_id, 0)))
                       OR 0 NOT IN
                             (SELECT DECODE (COUNT (order_hold_id), 0, 0, 1)
                                FROM oe_order_holds_all orh,
                                     oe_hold_sources_all ohs,
                                     oe_hold_definitions ohd
                               WHERE orh.header_id = oel.header_id
                                     AND orh.hold_source_id =
                                            ohs.hold_source_id
                                     AND ohs.hold_id = ohd.hold_id
                                     AND ohd.item_type IS NULL
                                     AND ohd.activity_name IS NULL
                                     AND orh.hold_release_id IS NULL
                                     AND (orh.line_id = oel.line_id
                                          OR orh.line_id IS NULL))))
              OR oel.line_id IS NULL)
ORDER BY oeh.order_number