Friday, July 29, 2016

Query to check discrepancy in stock R12 stock discrepancy

--Query to check discrepency in stock

 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 (
                       'xx60', 'xx5', 'xx3' --  ORG CODES
                       )
                     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)

No comments:

Post a Comment

Types of animations

Animation is a diverse and creative field with a variety of styles that artists and filmmakers use to bring their visions to life. Here are ...