Important Queries Discrepancy in Onhand Qty

ORDERS WITH THE ITEM:

SELECT DISTINCT B.ORDER_NUMBER,

A.ORG_ID,

A.ORDERED_ITEM,

A.FLOW_STATUS_CODE

FROM APPS.OE_ORDER_LINES_ALL A,

APPS.OE_ORDER_HEADERS_ALL B,

APPS.MTL_SYSTEM_ITEMS_B C

WHERE C.SEGMENT1 IN ('&ITEM')

AND A.FLOW_STATUS_CODE NOT IN('CLOSED','CANCELLED')

AND A.HEADER_ID = B.HEADER_ID

AND A.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID

ORDER BY A.ORDERED_ITEM

SELECT (ON_HAND_QUANTITY - ORDERED_QUANTITY) DIFF, GAP.*

  FROM (SELECT NVL(STOCK.ORGANIZATION_CODE, DEMD.ORDER_ORGANIZATION) ORG_CODE,

               NVL(STOCK.OH_EBS_ITEM, DEMD.ORD_EBS_ITEM) EBS_ITEM,

               STOCK.ON_HAND_ITEM,

               STOCK.OH_EBS_ITEM,

               NVL(STOCK.PRIMARY_TRANSACTION_QUANTITY, 0) ON_HAND_QUANTITY,

               DEMD.COUNTRY_CODE,

               DEMD.ORDER_ORGANIZATION,

               DEMD.ITEM_NUMBER,

               DEMD.ORD_EBS_ITEM,

               NVL(DEMD.ORDERED_QUANTITY, 0) ORDERED_QUANTITY

          FROM (SELECT MP.ORGANIZATION_CODE,

                       MIRB.ATTRIBUTE1 ON_HAND_ITEM,

                       MSI.SEGMENT1 OH_EBS_ITEM,

                       SUM(MOQD.PRIMARY_TRANSACTION_QUANTITY) PRIMARY_TRANSACTION_QUANTITY

                  FROM APPS.MTL_ONHAND_QUANTITIES_DETAIL MOQD,

                       APPS.MTL_PARAMETERS               MP,

                       APPS.MTL_SYSTEM_ITEMS             MSI,

                       APPS.MTL_ITEM_REVISIONS_B         MIRB

                 WHERE 1 = 1

                   AND MOQD.ORGANIZATION_ID = MP.ORGANIZATION_ID

                   AND MOQD.ORGANIZATION_ID = MSI.ORGANIZATION_ID

                   AND MOQD.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID

                   AND MOQD.ORGANIZATION_ID = MIRB.ORGANIZATION_ID

                   AND MOQD.INVENTORY_ITEM_ID = MIRB.INVENTORY_ITEM_ID

                   AND MOQD.REVISION = MIRB.REVISION

                       AND ORGANIZATION_CODE IN (

                       'C60', 'C65', 'F43'

                       )

                   AND SUBINVENTORY_CODE IN ('PICKLINE', 'STAGING')

                 GROUP BY MP.ORGANIZATION_CODE, MSI.SEGMENT1, MIRB.ATTRIBUTE1

                 ORDER BY ORGANIZATION_CODE, ON_HAND_ITEM) STOCK

          FULL JOIN (SELECT FLV.LOOKUP_CODE COUNTRY_CODE,

                           MP.ORGANIZATION_CODE ORDER_ORGANIZATION,

                           MSI.SEGMENT1 ORD_EBS_ITEM,

                           OOLA.USER_ITEM_DESCRIPTION ITEM_NUMBER,

                           SUM(OOLA.ORDERED_QUANTITY) ORDERED_QUANTITY

                      FROM APPS.OE_ORDER_HEADERS_ALL OOHA,

                           APPS.OE_ORDER_LINES_ALL   OOLA,

                           APPS.FND_LOOKUP_VALUES    FLV,

                           APPS.MTL_PARAMETERS       MP,

                           APPS.MTL_SYSTEM_ITEMS     MSI

                     WHERE 1 = 1

                       AND OOHA.HEADER_ID = OOLA.HEADER_ID

                       AND OOLA.SHIP_FROM_ORG_ID = MP.ORGANIZATION_ID

                       AND OOLA.CANCELLED_FLAG = 'N'

                       AND OOLA.SHIPPABLE_FLAG = 'Y'

                       AND OOLA.OPEN_FLAG = 'Y'

                       AND FLV.LANGUAGE = FLV.SOURCE_LANG

                       AND OOLA.SHIP_FROM_ORG_ID = MSI.ORGANIZATION_ID

                       AND OOLA.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID

                       AND OOLA.LINE_CATEGORY_CODE = 'ORDER'

                       AND FLV.LOOKUP_TYPE IN ('XX_OM_COUNTRY_OU_MAPPING')

                       AND FLV.ATTRIBUTE1 = OOHA.ORG_ID

                       AND FLV.LOOKUP_CODE = SUBSTR(OOHA.ORIG_SYS_DOCUMENT_REF, 1, 3)

                       AND    OOHA.ATTRIBUTE2 IS NOT NULL

--                       AND TRUNC(TO_DATE(OOHA.ATTRIBUTE2)) < TRUNC(SYSDATE + 1)

                       AND EXISTS (SELECT 1 FROM APPS.XX_OM_ORDER_DELIVERIES_STG RP WHERE RP.CONSOLIDATED_ORDER_NUMBER =  OOHA.ATTRIBUTE7

                       AND RP.COUNTRY_CODE = FLV.LOOKUP_CODE AND RP.SHIP_DATE < TRUNC(SYSDATE) )

                       AND ORGANIZATION_CODE IN (

                       'C60', 'C65', 'F43'

                       )

                     GROUP BY FLV.LOOKUP_CODE,

                              MP.ORGANIZATION_CODE,

                              MSI.SEGMENT1,

                              OOLA.USER_ITEM_DESCRIPTION

                     ORDER BY ORGANIZATION_CODE, ITEM_NUMBER) DEMD

            ON DEMD.ITEM_NUMBER = STOCK.ON_HAND_ITEM

           AND STOCK.OH_EBS_ITEM = DEMD.ORD_EBS_ITEM

           AND DEMD.ORDER_ORGANIZATION = STOCK.ORGANIZATION_CODE) GAP

WHERE 1 = 1

  AND (ON_HAND_QUANTITY = 0 OR ORDERED_QUANTITY = 0 OR ON_HAND_QUANTITY <> ORDERED_QUANTITY)

;