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

/* Formatted on 11/18/2016 3:55:24 PM (QP5 v5.114.809.3010) */
  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