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)

1 Comments :

Low Cost Custom Built Order Management Software | ERP GOLD
Orders can be received from businesses, consumers, or a mix of both, depending on the products. with ERP Gold's Low Cost Custom Built Order Management Software you can manage; Product information, Inventory, Vendors, Marketing, Customers/prospects & Orders all at the same time. It is an Easy Order Management Software which helps the user make decisions on order management very easily.
Low Cost Custom Built Order Management Software is specially designed for startups and Small Business so that they won't have to spend a lot of money on softwares and get better results from less investment.
Features of ERP Gold's Order Management Software includes:
It is cloud based with SSL connection
Business Operation Integration
Top Level Security with SSL
Adaptability with fast deployment
For more information, visit our website : https://www.erp.gold/easy-inventory-management-software/
Or Get in touch with us: 1-888-334-4472
Address: Suite 183411, Shelby TWP, MI 48318
Email us: support@erp.gold
Keyword: easy inventory management software

Reply

Post a Comment