OPM QUERY TO FIND ONHAND,RESERVATION AND AVAILABLE QUANTITIES

   SELECT BATCH_ID,
          BATCH_NUMBER,
          BATCH_STATUS,
          INVENTORY_ITEM_ID,
          INGREDIENTS,
          ORGANIZATION_ID,
          ORGANIZATION_CODE,
          SUBINVENTORY_CODE,
          LOT_NUMBER,
          COST_CENTER,
          CC_CODE,
          WORK_CENTER,
          WC_CODE,
          RECIPE_ID,
          RECIPE_NUM,
          RECIPE_VERSION,
          FORMULA_ID,
          PLAN_START_DATE,
          PLAN_END_DATE,
          ACTUAL_START_DATE,
          ACTUAL_END_DATE,
          BATCH_CREATION_DATE,
          DUE_DATE,
          FORMULA_NUM,
          FORMULA_VERSION,
          ROUTING_ID,
          ROUTING_NUM,
          ROUTING_VERSION,
          PRIMARY_UOM_CODE,
          DETAIL_UOM,
          CONVERSION_RATE,
          ACTUAL_QUANTITY,
          MAT_TRAN_ACTUAL_QUANTITY,
          PLAN_QUANTITY,
          PRIMARY_PLAN_QUANTITY,
          PRIMARY_ACTUAL_QUANTITY,
          LOT_ACTUAL_QUANTITY,
          ONHAND_QUANTITY,
          PRIMARY_RESERVATION_QUANTITY,
          ONHAND_QUANTITY-PRIMARY_RESERVATION_QUANTITY AVAILABLE_QUANTITY
     FROM (  SELECT SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_ID       BATCH_ID,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_NUM      BATCH_NUMBER,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.ORGANIZATION_ID ORGANIZATION_ID,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.ORGANIZATION_CODE
                       ORGANIZATION_CODE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.SUBINVENTORY_CODE
                       SUBINVENTORY_CODE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.COST_CENTER    COST_CENTER,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.CC_CODE        CC_CODE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.RECIPE_ID,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.RECIPE_NUM,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.RECIPE_VERSION,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.FORMULA_ID     FORMULA_ID,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.PLAN_START_DATE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.PLAN_END_DATE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.ACTUAL_START_DATE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.ACTUAL_END_DATE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_CREATION_DATE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.DUE_DATE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.FORMULA_NUM    FORMULA_NUM,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.FORMULA_VERSION FORMULA_VERSION,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.WORK_CENTER    WORK_CENTER,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.WC_CODE        WC_CODE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.ROUTING_ID,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.ROUTING_NUM,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.ROUTING_VERSION,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.LOT_NUMBER     LOT_NUMBER,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.INVENTORY_ITEM_ID
                       INVENTORY_ITEM_ID,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.INGREDIENTS    INGREDIENTS,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_STATUS   BATCH_STATUS,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.PRIMARY_UOM_CODE PRIMARY_UOM_CODE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.DETAIL_UOM     DETAIL_UOM,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.CONVERSION_RATE CONVERSION_RATE,
                    MAX (PLAN_QTY)                          PLAN_QUANTITY,
                    MAX (PLAN_QTY * CONVERSION_RATE)
                       PRIMARY_PLAN_QUANTITY,
                    MAX (GMD_ACTUAL_QTY)                    ACTUAL_QUANTITY,
                    MAX (GMD_ACTUAL_QTY * CONVERSION_RATE)
                       PRIMARY_ACTUAL_QUANTITY,
                    MAX (MMT_ACTUAL_QTY)
                       MAT_TRAN_ACTUAL_QUANTITY,
                    MAX (MTLN_ACTUAL_QTY)
                       LOT_ACTUAL_QUANTITY,
                    MAX (PRIMARY_RESERVATION_QUANTITY)
                       PRIMARY_RESERVATION_QUANTITY,
                    SUM (NVL (MOQD.PRIMARY_TRANSACTION_QUANTITY, 0))
                       ONHAND_QUANTITY
               FROM (  SELECT BATCH_ID,
                              BATCH_NUM,
                              INVENTORY_ITEM_ID,
                              INGREDIENTS,
                              CC_CODE,
                              RECIPE_ID,
                              RECIPE_NUM,
                              RECIPE_VERSION,
                              FORMULA_ID,
                              PLAN_START_DATE,
                              PLAN_END_DATE,
                              ACTUAL_START_DATE,
                              ACTUAL_END_DATE,
                              BATCH_CREATION_DATE,
                              DUE_DATE,
                              FORMULA_NUM,
                              FORMULA_VERSION,
                              BATCH_STATUS,
                              ORGANIZATION_ID,
                              ORGANIZATION_CODE,
                              COST_CENTER,
                              WORK_CENTER,
                              WC_CODE,
                              ROUTING_ID,
                              ROUTING_NUM,
                              ROUTING_VERSION,
                              DETAIL_UOM,
                              PRIMARY_UOM_CODE,
                              NVL (CONVERSION_RATE, 1) CONVERSION_RATE,
                              SUBINVENTORY_CODE,
                              LOT_NUMBER,
                              --MATERIAL_DETAIL_ID,
                              MAX (GMD_ACTUAL_QTY) GMD_ACTUAL_QTY,
                              MAX (PLAN_QTY)       PLAN_QTY,
                              MAX (MMT_ACTUAL_QTY) MMT_ACTUAL_QTY,
                              MAX (MTLN_ACTUAL_QTY) MTLN_ACTUAL_QTY,
                              SUM (PRIMARY_RESERVATION_QUANTITY)
                                 PRIMARY_RESERVATION_QUANTITY
                         FROM (  SELECT SQ_GMD_MMT.BATCH_ID,
                                        SQ_GMD_MMT.BATCH_NUM,
                                        SQ_GMD_MMT.MATERIAL_DETAIL_ID,
                                        SQ_GMD_MMT.INVENTORY_ITEM_ID,
                                        SQ_GMD_MMT.INGREDIENTS,
                                        SQ_GMD_MMT.CC_CODE,
                                        SQ_GMD_MMT.RECIPE_ID,
                                        SQ_GMD_MMT.RECIPE_NUM,
                                        SQ_GMD_MMT.RECIPE_VERSION,
                                        SQ_GMD_MMT.FORMULA_ID,
                                        SQ_GMD_MMT.PLAN_START_DATE,
                                        SQ_GMD_MMT.PLAN_END_DATE,
                                        SQ_GMD_MMT.ACTUAL_START_DATE,
                                        SQ_GMD_MMT.ACTUAL_END_DATE,
                                        SQ_GMD_MMT.BATCH_CREATION_DATE,
                                        SQ_GMD_MMT.DUE_DATE,
                                        SQ_GMD_MMT.FORMULA_NUM,
                                        SQ_GMD_MMT.FORMULA_VERSION,
                                        SQ_GMD_MMT.BATCH_STATUS,
                                        SQ_GMD_MMT.ORGANIZATION_ID,
                                        SQ_GMD_MMT.ORGANIZATION_CODE,
                                        SQ_GMD_MMT.COST_CENTER,
                                        SQ_GMD_MMT.WORK_CENTER,
                                        SQ_GMD_MMT.WC_CODE,
                                        SQ_GMD_MMT.ROUTING_ID,
                                        SQ_GMD_MMT.ROUTING_NUM,
                                        SQ_GMD_MMT.ROUTING_VERSION,
                                        SQ_GMD_MMT.DETAIL_UOM,
                                        SQ_GMD_MMT.PRIMARY_UOM_CODE,
                                        SQ_GMD_MMT.SUBINVENTORY_CODE,
                                        SQ_GMD_MMT.CONVERSION_RATE,
                                        SQ_GMD_MMT.TRANSACTION_ID,
                                        SQ_MTLN.LOT_NUMBER,
                                        MAX (SQ_GMD_MMT.GMD_ACTUAL_QTY)
                                           GMD_ACTUAL_QTY,
                                        MAX (SQ_GMD_MMT.PLAN_QTY) PLAN_QTY,
                                        MAX (SQ_GMD_MMT.MMT_ACTUAL_QTY)
                                           MMT_ACTUAL_QTY,
                                        SUM (NVL (SQ_MTLN.MTLN_ACTUAL_QTY, 0))
                                           MTLN_ACTUAL_QTY,
                                        SUM (
                                           NVL (
                                              SQ_MTR.PRIMARY_RESERVATION_QUANTITY,
                                              0))
                                           PRIMARY_RESERVATION_QUANTITY
                                   FROM (  SELECT SQ_GMD.BATCH_ID,
                                                  SQ_GMD.BATCH_NUM,
                                                  SQ_GMD.MATERIAL_DETAIL_ID,
                                                  SQ_GMD.INVENTORY_ITEM_ID,
                                                  SQ_GMD.INGREDIENTS,
                                                  SQ_GMD.CC_CODE,
                                                  SQ_GMD.RECIPE_ID,
                                                  SQ_GMD.RECIPE_NUM,
                                                  SQ_GMD.RECIPE_VERSION,
                                                  SQ_GMD.FORMULA_ID,
                                                  SQ_GMD.PLAN_START_DATE,
                                                  SQ_GMD.PLAN_END_DATE,
                                                  SQ_GMD.ACTUAL_START_DATE,
                                                  SQ_GMD.ACTUAL_END_DATE,
                                                  SQ_GMD.BATCH_CREATION_DATE,
                                                  SQ_GMD.DUE_DATE,
                                                  SQ_GMD.FORMULA_NUM,
                                                  SQ_GMD.FORMULA_VERSION,
                                                  SQ_GMD.BATCH_STATUS,
                                                  SQ_GMD.ORGANIZATION_ID,
                                                  SQ_GMD.ORGANIZATION_CODE,
                                                  SQ_GMD.COST_CENTER,
                                                  SQ_GMD.DETAIL_UOM,
                                                  SQ_GMD.PRIMARY_UOM_CODE,
                                                  SQ_GMD.CONVERSION_RATE,
                                                  MMT.SUBINVENTORY_CODE,
                                                  MMT.TRANSACTION_ID,
                                                  MAX (ACTUAL_QTY) GMD_ACTUAL_QTY,
                                                  MAX (PLAN_QTY) PLAN_QTY,
                                                  SUM (NVL (MMT.PRIMARY_QUANTITY, 0))
                                                     MMT_ACTUAL_QTY,
                                                  SQ_WORK_CENTER.WORK_CENTER
                                                     WORK_CENTER,
                                                  SQ_WORK_CENTER.WC_CODE,
                                                  SQ_WORK_CENTER.ROUTING_ID,
                                                  SQ_WORK_CENTER.ROUTING_NUM,
                                                  SQ_WORK_CENTER.ROUTING_VERSION
                                             FROM (SELECT GMD.BATCH_ID BATCH_ID,
                                                          GBH.BATCH_NO BATCH_NUM,
                                                          GMD.MATERIAL_DETAIL_ID
                                                             MATERIAL_DETAIL_ID,
                                                          GMD.INVENTORY_ITEM_ID
                                                             INVENTORY_ITEM_ID,
                                                          MSIB.SEGMENT1 INGREDIENTS,
                                                          MSIB.PRIMARY_UOM_CODE
                                                             PRIMARY_UOM_CODE,
                                                          GMD.ORGANIZATION_ID
                                                             ORGANIZATION_ID,
                                                          MP.ORGANIZATION_CODE
                                                             ORGANIZATION_CODE,
                                                          FVC.DESCRIPTION COST_CENTER,
                                                          GRB.RECIPE_ID RECIPE_ID,
                                                          GRB.RECIPE_NO RECIPE_NUM,
                                                          GRB.RECIPE_VERSION
                                                             RECIPE_VERSION,
                                                          FFM.FORMULA_ID FORMULA_ID,
                                                          FFM.FORMULA_NO FORMULA_NUM,
                                                          FFM.FORMULA_VERS
                                                             FORMULA_VERSION,
                                                          GRB.ATTRIBUTE2 CC_CODE,
                                                          GMD.DTL_UM DETAIL_UOM,
                                                          CASE
                                                             WHEN GBH.BATCH_STATUS =
                                                                     1
                                                             THEN
                                                                'Pending'
                                                             WHEN GBH.BATCH_STATUS =
                                                                     2
                                                             THEN
                                                                'WIP'
                                                          END
                                                             BATCH_STATUS,
                                                          (inv_convert.inv_um_convert(
                                                          GMD.INVENTORY_ITEM_ID,
                                                          NULL,
                                                          GMD.ORGANIZATION_ID,
                                                          8,
                                                          GMD.PLAN_QTY,
                                                          GMD.DTL_UM,
                                                          msib.primary_uom_code,
                                                          NULL,
                                                          NULL)/nvl((case when GMD.PLAN_QTY=0 then 1 else GMD.PLAN_QTY end),1)) CONVERSION_RATE,
                                                          NVL (GMD.ACTUAL_QTY, 0)
                                                             ACTUAL_QTY,
                                                          NVL (GMD.PLAN_QTY, 0)
                                                             PLAN_QTY,
                                                          GBH.PLAN_START_DATE
                                                             PLAN_START_DATE,
                                                          GBH.PLAN_CMPLT_DATE
                                                             PLAN_END_DATE,
                                                          GBH.ACTUAL_START_DATE
                                                             ACTUAL_START_DATE,
                                                          GBH.ACTUAL_CMPLT_DATE
                                                             ACTUAL_END_DATE,
                                                          GBH.CREATION_DATE
                                                             BATCH_CREATION_DATE,
                                                          GBH.DUE_DATE
                                                             DUE_DATE
                                                     FROM GME.GME_MATERIAL_DETAILS
                                                          GMD
                                                          INNER JOIN
                                                          GME.GME_BATCH_HEADER GBH
                                                             ON (GBH.BATCH_ID =
                                                                    GMD.BATCH_ID)
                                                          INNER JOIN
                                                          GMD.GMD_RECIPE_VALIDITY_RULES
                                                          GRVR
                                                             ON (GBH.RECIPE_VALIDITY_RULE_ID =
                                                                    GRVR.RECIPE_VALIDITY_RULE_ID)
                                                          INNER JOIN
                                                          GMD.GMD_RECIPES_B GRB
                                                             ON (    GRB.ROUTING_ID =
                                                                        GBH.ROUTING_ID
                                                                 AND GRB.RECIPE_ID =
                                                                        GRVR.RECIPE_ID)
                                                          INNER JOIN
                                                          INV.MTL_SYSTEM_ITEMS_B MSIB
                                                             ON (    GMD.INVENTORY_ITEM_ID =
                                                                        MSIB.INVENTORY_ITEM_ID
                                                                 AND GMD.ORGANIZATION_ID =
                                                                        MSIB.ORGANIZATION_ID)
                                                          INNER JOIN
                                                          MTL_PARAMETERS MP
                                                             ON (GMD.ORGANIZATION_ID =
                                                                    MP.ORGANIZATION_ID)
                                                          INNER JOIN
                                                          FM_FORM_MST_B FFM
                                                             ON (FFM.FORMULA_ID =
                                                                    GRB.FORMULA_ID)
                                                          INNER JOIN
                                                          FND_LOOKUP_VALUES FVC
                                                             ON (    FVC.LOOKUP_TYPE =
                                                                        'XXGMD_COST_CENTERS'
                                                                 AND FVC.LANGUAGE =
                                                                        'US'
                                                                 AND FVC.LOOKUP_CODE =
                                                                           MP.ORGANIZATION_CODE
                                                                        || '-'
                                                                        || GRB.ATTRIBUTE2)                                                        
                                                    WHERE     1=1
                                                          AND GBH.BATCH_STATUS IN
                                                                 (1, 2)
                                                         ) SQ_GMD
                                                  INNER JOIN
                                                  (  SELECT GBH.BATCH_ID,
                                                            LISTAGG (
                                                               FVW.DESCRIPTION,
                                                               ',')
                                                            WITHIN GROUP (ORDER BY
                                                                             GBH.BATCH_ID)
                                                               WORK_CENTER,
                                                            LISTAGG (
                                                               GOB.ATTRIBUTE1,
                                                               ',')
                                                            WITHIN GROUP (ORDER BY
                                                                             GBH.BATCH_ID)
                                                               WC_CODE,
                                                            FRD.ROUTING_ID,
                                                            GRT.ROUTING_NO ROUTING_NUM,
                                                            GRT.ROUTING_VERS
                                                               ROUTING_VERSION
                                                       FROM GMD_OPERATIONS_B GOB,
                                                            APPS.FM_ROUT_DTL FRD,
                                                            GMD.GMD_RECIPES_B GRB,
                                                            GME_BATCH_HEADER GBH,
                                                            GMD_RECIPE_VALIDITY_RULES
                                                            GRVR,
                                                            FND_LOOKUP_VALUES FVW,
                                                            MTL_PARAMETERS MP,
                                                            GMD_ROUTINGS_B GRT
                                                      WHERE     GRT.ROUTING_ID =
                                                                   FRD.ROUTING_ID
                                                            AND GBH.ORGANIZATION_ID =
                                                                   MP.ORGANIZATION_ID
                                                            AND GBH.RECIPE_VALIDITY_RULE_ID =
                                                                   GRVR.RECIPE_VALIDITY_RULE_ID
                                                            AND GRVR.RECIPE_ID =
                                                                   GRB.RECIPE_ID
                                                            AND FRD.ROUTING_ID =
                                                                   GRB.ROUTING_ID
                                                            AND GOB.OPRN_ID =
                                                                   FRD.OPRN_ID
                                                            AND BATCH_STATUS IN
                                                                   (01, 02)
                                                            AND FVW.LOOKUP_TYPE =
                                                                   'XXGMD_WORK_CENTERS'
                                                            AND FVW.LANGUAGE = 'US'
                                                            AND FVW.LOOKUP_CODE =
                                                                      ORGANIZATION_CODE
                                                                   || '-'
                                                                   || GOB.ATTRIBUTE1
                                                   GROUP BY GBH.BATCH_ID,
                                                            FRD.ROUTING_ID,
                                                            GRT.ROUTING_NO,
                                                            GRT.ROUTING_VERS)
                                                  SQ_WORK_CENTER
                                                     ON (SQ_GMD.BATCH_ID =
                                                            SQ_WORK_CENTER.BATCH_ID)
                                                  LEFT OUTER JOIN
                                                  INV.MTL_MATERIAL_TRANSACTIONS MMT
                                                     ON (    SQ_GMD.BATCH_ID =
                                                                MMT.TRANSACTION_SOURCE_ID
                                                         AND SQ_GMD.MATERIAL_DETAIL_ID =
                                                                MMT.TRX_SOURCE_LINE_ID
                                                         AND SQ_GMD.ORGANIZATION_ID =
                                                                MMT.ORGANIZATION_ID
                                                         AND SQ_GMD.INVENTORY_ITEM_ID =
                                                                MMT.INVENTORY_ITEM_ID)
                                         GROUP BY SQ_GMD.BATCH_ID,
                                                  SQ_GMD.BATCH_NUM,
                                                  SQ_GMD.MATERIAL_DETAIL_ID,
                                                  SQ_GMD.INVENTORY_ITEM_ID,
                                                  SQ_GMD.PRIMARY_UOM_CODE,
                                                  SQ_GMD.ORGANIZATION_ID,
                                                  SQ_GMD.COST_CENTER,
                                                  SQ_WORK_CENTER.WORK_CENTER,
                                                  SQ_WORK_CENTER.WC_CODE,
                                                  SQ_WORK_CENTER.ROUTING_ID,
                                                  SQ_WORK_CENTER.ROUTING_NUM,
                                                  SQ_WORK_CENTER.ROUTING_VERSION,
                                                  SQ_GMD.DETAIL_UOM,
                                                  MMT.SUBINVENTORY_CODE,
                                                  MMT.TRANSACTION_ID,
                                                  SQ_GMD.INGREDIENTS,
                                                  SQ_GMD.CC_CODE,
                                                  SQ_GMD.RECIPE_ID,
                                                  SQ_GMD.RECIPE_NUM,
                                                  SQ_GMD.RECIPE_VERSION,
                                                  SQ_GMD.FORMULA_ID,
                                                  SQ_GMD.PLAN_START_DATE,
                                                  SQ_GMD.PLAN_END_DATE,
                                                  SQ_GMD.ACTUAL_START_DATE,
                                                  SQ_GMD.ACTUAL_END_DATE,
                                                  SQ_GMD.BATCH_CREATION_DATE,
                                                  SQ_GMD.DUE_DATE,
                                                  SQ_GMD.FORMULA_NUM,
                                                  SQ_GMD.FORMULA_VERSION,
                                                  SQ_GMD.BATCH_STATUS,
                                                  SQ_GMD.CONVERSION_RATE,
                                                  SQ_GMD.ORGANIZATION_CODE)
                                        SQ_GMD_MMT
                                        LEFT OUTER JOIN
                                        (  SELECT TRANSACTION_ID,
                                                  ORGANIZATION_ID,
                                                  INVENTORY_ITEM_ID,
                                                  LOT_NUMBER,
                                                  TRANSACTION_SOURCE_ID,
                                                  SUM (NVL (PRIMARY_QUANTITY, 0))
                                                     MTLN_ACTUAL_QTY
                                             FROM INV.MTL_TRANSACTION_LOT_NUMBERS
                                         GROUP BY TRANSACTION_ID,
                                                  ORGANIZATION_ID,
                                                  INVENTORY_ITEM_ID,
                                                  LOT_NUMBER,
                                                  TRANSACTION_SOURCE_ID) SQ_MTLN
                                           ON (    SQ_GMD_MMT.TRANSACTION_ID =
                                                      SQ_MTLN.TRANSACTION_ID
                                               AND SQ_GMD_MMT.ORGANIZATION_ID =
                                                      SQ_MTLN.ORGANIZATION_ID
                                               AND SQ_GMD_MMT.INVENTORY_ITEM_ID =
                                                      SQ_MTLN.INVENTORY_ITEM_ID
                                               AND SQ_GMD_MMT.BATCH_ID =
                                                      SQ_MTLN.TRANSACTION_SOURCE_ID)
                                        LEFT OUTER JOIN
                                        (  SELECT ORGANIZATION_ID,
                                                  INVENTORY_ITEM_ID,
                                                  DEMAND_SOURCE_HEADER_ID,
                                                  DEMAND_SOURCE_LINE_ID,
                                                  SUBINVENTORY_CODE,
                                                  LOT_NUMBER,
                                                  SUM (PRIMARY_RESERVATION_QUANTITY)
                                                     PRIMARY_RESERVATION_QUANTITY
                                             FROM MTL_RESERVATIONS MTR
                                         GROUP BY ORGANIZATION_ID,
                                                  INVENTORY_ITEM_ID,
                                                  DEMAND_SOURCE_LINE_ID,
                                                  DEMAND_SOURCE_HEADER_ID,
                                                  SUBINVENTORY_CODE,
                                                  LOT_NUMBER ) SQ_MTR
                                           ON (   1=1
                                 AND       SQ_MTR.DEMAND_SOURCE_HEADER_ID =  SQ_GMD_MMT.BATCH_ID
                                AND SQ_MTR.DEMAND_SOURCE_LINE_ID =  SQ_GMD_MMT.MATERIAL_DETAIL_ID
                                               AND SQ_MTLN.LOT_NUMBER = SQ_MTR.LOT_NUMBER
                                               AND SQ_MTR.ORGANIZATION_ID =SQ_GMD_MMT.ORGANIZATION_ID
                                               AND SQ_MTR.INVENTORY_ITEM_ID =SQ_GMD_MMT.INVENTORY_ITEM_ID
                                               AND SQ_MTR.SUBINVENTORY_CODE=SQ_GMD_MMT.SUBINVENTORY_CODE
                                               )
                               GROUP BY SQ_GMD_MMT.BATCH_ID,
                                        SQ_GMD_MMT.BATCH_NUM,
                                        SQ_GMD_MMT.MATERIAL_DETAIL_ID,
                                        SQ_GMD_MMT.INVENTORY_ITEM_ID,
                                        SQ_GMD_MMT.PRIMARY_UOM_CODE,
                                        SQ_GMD_MMT.INGREDIENTS,
                                        SQ_GMD_MMT.CC_CODE,
                                        SQ_GMD_MMT.RECIPE_ID,
                                        SQ_GMD_MMT.RECIPE_NUM,
                                        SQ_GMD_MMT.RECIPE_VERSION,
                                        SQ_GMD_MMT.FORMULA_ID,
                                        SQ_GMD_MMT.PLAN_START_DATE,
                                        SQ_GMD_MMT.PLAN_END_DATE,
                                        SQ_GMD_MMT.ACTUAL_START_DATE,
                                        SQ_GMD_MMT.ACTUAL_END_DATE,
                                        SQ_GMD_MMT.BATCH_CREATION_DATE,
                                        SQ_GMD_MMT.DUE_DATE,
                                        SQ_GMD_MMT.FORMULA_NUM,
                                        SQ_GMD_MMT.FORMULA_VERSION,
                                        SQ_GMD_MMT.BATCH_STATUS,
                                        SQ_GMD_MMT.ORGANIZATION_ID,
                                        SQ_GMD_MMT.ORGANIZATION_CODE,
                                        SQ_GMD_MMT.COST_CENTER,
                                        SQ_GMD_MMT.WORK_CENTER,
                                        SQ_GMD_MMT.WC_CODE,
                                        SQ_GMD_MMT.ROUTING_ID,
                                        SQ_GMD_MMT.ROUTING_NUM,
                                        SQ_GMD_MMT.ROUTING_VERSION,
                                        SQ_GMD_MMT.DETAIL_UOM,
                                        SQ_GMD_MMT.SUBINVENTORY_CODE,
                                        SQ_GMD_MMT.TRANSACTION_ID,
                                        SQ_GMD_MMT.CONVERSION_RATE,
                                        SQ_MTLN.LOT_NUMBER) SQ_GMD_MMT_MTLN_MTR
                     GROUP BY BATCH_ID,
                              BATCH_NUM,
                              INVENTORY_ITEM_ID,
                              ORGANIZATION_ID,
                              COST_CENTER,
                              WORK_CENTER,
                              WC_CODE,
                              ROUTING_ID,
                              ROUTING_NUM,
                              ROUTING_VERSION,
                              DETAIL_UOM,
                              SUBINVENTORY_CODE,
                              LOT_NUMBER,
                              INGREDIENTS,
                              CC_CODE,
                              RECIPE_ID,
                              RECIPE_NUM,
                              RECIPE_VERSION,
                              FORMULA_ID,
                              PLAN_START_DATE,
                              PLAN_END_DATE,
                              ACTUAL_START_DATE,
                              ACTUAL_END_DATE,
                              BATCH_CREATION_DATE,
                              DUE_DATE,
                              FORMULA_NUM,
                              FORMULA_VERSION,
                              BATCH_STATUS,
                              ORGANIZATION_CODE,
                              CONVERSION_RATE,
                              PRIMARY_UOM_CODE) SQ_GMD_MMT_MTLN_MTR_MOQD
                    LEFT OUTER JOIN INV.MTL_ONHAND_QUANTITIES_DETAIL MOQD
                       ON (    MOQD.INVENTORY_ITEM_ID =
                                  SQ_GMD_MMT_MTLN_MTR_MOQD.INVENTORY_ITEM_ID
                           AND MOQD.ORGANIZATION_ID =
                                  SQ_GMD_MMT_MTLN_MTR_MOQD.ORGANIZATION_ID
                           AND  CASE WHEN SQ_GMD_MMT_MTLN_MTR_MOQD.SUBINVENTORY_CODE IS NULL THEN '0' ELSE MOQD.SUBINVENTORY_CODE
                                END = NVL(SQ_GMD_MMT_MTLN_MTR_MOQD.SUBINVENTORY_CODE,'0') 
                           AND  CASE WHEN SQ_GMD_MMT_MTLN_MTR_MOQD.LOT_NUMBER IS NULL THEN '0' ELSE MOQD.LOT_NUMBER
                                END = NVL(SQ_GMD_MMT_MTLN_MTR_MOQD.LOT_NUMBER,'0')
                           )
           GROUP BY SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_ID,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_NUM,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.INVENTORY_ITEM_ID,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.INGREDIENTS,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.CC_CODE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.RECIPE_ID,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.RECIPE_NUM,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.RECIPE_VERSION,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.FORMULA_ID,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.PLAN_START_DATE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.PLAN_END_DATE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.ACTUAL_START_DATE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.ACTUAL_END_DATE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_CREATION_DATE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.DUE_DATE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.FORMULA_NUM,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.FORMULA_VERSION,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.ORGANIZATION_CODE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.ORGANIZATION_ID,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.COST_CENTER,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.DETAIL_UOM,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.SUBINVENTORY_CODE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.LOT_NUMBER,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.PRIMARY_UOM_CODE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.CONVERSION_RATE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.WORK_CENTER,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.WC_CODE,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.ROUTING_ID,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.ROUTING_NUM,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.ROUTING_VERSION,
                    SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_STATUS);

HOW TO NAVIGATE BETWEEN FOLDERS IN LINUX USING COMMAND LINE

CD command which is similar to windows command.
CD means change directory.

Example:
For our example lets assume the following directory structure

     root/test/etc/etc1

Task 1:  Command to navigate from etc to etc1 folder i.e., from higher level folder to lower level folder. i.e., from etc to etc1 folder

Command:  CD etc1 

Task 2:  Command to navigate from etc1 to test folder i.e., from higher level folder to lower level folder. i.e., from etc to etc1 folder

Command:  CD  /test

Note : Unlike windows type the full path for navigation to folders with correct spelling.


 For example to navigate to root folder type the command CD /




 

 After navigation to root folder type LS the list command it will display all the folders in root folder

to navigate to lower folder  etc type command cd etc

I have navigated to perl folder in etc folder

 now i have navigated backed to etc folder with the command cd /etc

Note : Important thing to note is for navigation to lower folder use cd navigation path and to navigate to upper folders use cd /Navigation path.
           use ls command to display the list of folders.

LINUX TOP COMMAND

TOP command is similar to Task Manager in windows.
when we type TOP we will see all process, CPU usage etc as seen as in the screenshot below

.
It shows PID - Process ID
               USER and other information.

H Command - It is help which will display all the commands related to TOP command.

For example if you want to kill a process use the command   K PID  

      Ex:    K  2395
                It will kill the process 2395

OPM QUERY TO FIND BATCH STATUS AND DETAILS.

select hou.name organization_name,
       mp.organization_code,
       gbh.batch_no batch_number,
       flv.meaning  batch_status,
       ffmb.attribute4 batch_type,
       gbh.attribute9 lot_number,
       msib.segment1 item_number
from apps.gme_batch_header gbh,gme_material_details gmd,
     mtl_system_items_b msib,hr_all_organization_units hou,mtl_parameters mp,
     apps.fnd_lookup_values flv,fm_form_mst_b ffmb
where     1=1
      AND gbh.batch_id = gmd.batch_id
      AND gbh.organization_id = gmd.organization_id
      AND gmd.organization_id = msib.organization_id
      AND gmd.inventory_item_id = msib.inventory_item_id
      AND gmd.line_type = '1'
      AND gmd.line_no = 1
      and gmd.organization_id = hou.organization_id
      and gmd.organization_id = mp.organization_id
      AND flv.lookup_code = TO_CHAR (gbh.batch_status)
      AND flv.lookup_type = 'GME_BATCH_STATUS'
      AND flv.language = 'US'
      AND ffmb.formula_id=gbh.formula_id
      ;

ORACLE PL/SQL QUERY TO SEARCH FOR A PARTICULAR VALUE IN ALL THE DATABASE TABLES AND COLUMNS

SET SERVEROUTPUT ON;

DECLARE
    match_count   INTEGER;

BEGIN
    FOR i IN (SELECT table_name, column_name
                FROM all_tab_columns
               WHERE table_name LIKE 'XX%')
    LOOP
        BEGIN

            EXECUTE IMMEDIATE
                   'SELECT COUNT(*) FROM '
                || i.table_name
                || ' WHERE TO_CHAR('
                || i.column_name
                || ') LIKE :1'
                INTO match_count
                USING 'ENTER VALUE TO BE SEARCHED HERE';
        EXCEPTION
            WHEN OTHERS

            THEN
                DBMS_OUTPUT.put_line (i.table_name || '~' || i.column_name);
        END;


        IF match_count > 0
        THEN
            DBMS_OUTPUT.put_line (
                i.table_name || ' ' || i.column_name || ' ' || match_count);
        END IF;
    END LOOP;
END;
/