eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MTH_SRC_PROD_MTL_PRODUCED_V

Source

View Text - Preformatted


SELECT WORKORDER_FK, ITEM_FK, START_QUANTITY_G, SYSTEM_FK, START_QUANTITY_SG, EXPECTED_YEILD, PRIMARY_UOM, SECONDARY_UOM, ACTUAL_COMP_QTY_G, ACTUAL_COMP_QTY_SG, ACTUAL_REJECTED_QTY_G, ACTUAL_REJECTED_QTY_SG, ACTUAL_REWORKED_QTY_G, ACTUAL_REWORKED_QTY_SG, ACTUAL_SCRAP_QTY_G, ACTUAL_SCRAP_QTY_SG, ITEM_TYPE_CODE, USER_DIM1_FK, USER_DIM2_FK, USER_DIM3_FK, USER_DIM4_FK, USER_DIM5_FK, USER_ATTR1, USER_ATTR2, USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1, USER_MEASURE2, USER_MEASURE3, USER_MEASURE4, USER_MEASURE5, WIP_PLAN_QTY_G, WIP_PLAN_QTY_SG, CONTRIBUTE_YIELD_FLAG, SOURCE_ORG_CODE, WIP_ENTITY_ID, PRIMARY_ITEM_ID, LAST_UPDATE_DATE FROM (SELECT CAST(NULL AS VARCHAR2(150)) WORKORDER_FK, CAST(NULL AS VARCHAR2(150)) ITEM_FK, WDJ.START_QUANTITY START_QUANTITY_G, fnd_profile.value('MTH_EBS_GLOBAL_NAME') SYSTEM_FK, CAST(NULL AS NUMBER) START_QUANTITY_SG, CAST(NULL AS NUMBER) EXPECTED_YEILD, CAST(NULL AS VARCHAR2(150)) PRIMARY_UOM, CAST(NULL AS VARCHAR2(150)) SECONDARY_UOM, WDJ.QUANTITY_COMPLETED ACTUAL_COMP_QTY_G, CAST(NULL AS NUMBER) ACTUAL_COMP_QTY_SG, CAST(NULL AS NUMBER) ACTUAL_REJECTED_QTY_G, CAST(NULL AS NUMBER) ACTUAL_REJECTED_QTY_SG, CAST(NULL AS NUMBER) ACTUAL_REWORKED_QTY_G, CAST(NULL AS NUMBER) ACTUAL_REWORKED_QTY_SG, WDJ.QUANTITY_SCRAPPED ACTUAL_SCRAP_QTY_G, CAST(NULL AS NUMBER) ACTUAL_SCRAP_QTY_SG, fl.meaning ITEM_TYPE_CODE, CAST(NULL AS VARCHAR2(150)) USER_DIM1_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM2_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM3_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM4_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM5_FK, CAST(NULL AS VARCHAR2(150)) USER_ATTR1, CAST(NULL AS VARCHAR2(150)) USER_ATTR2, CAST(NULL AS VARCHAR2(150)) USER_ATTR3, CAST(NULL AS VARCHAR2(150)) USER_ATTR4, CAST(NULL AS VARCHAR2(150)) USER_ATTR5, CAST(NULL AS NUMBER) USER_MEASURE1, CAST(NULL AS NUMBER) USER_MEASURE2, CAST(NULL AS NUMBER) USER_MEASURE3, CAST(NULL AS NUMBER) USER_MEASURE4, CAST(NULL AS NUMBER) USER_MEASURE5, CAST(NULL AS NUMBER) WIP_PLAN_QTY_G, CAST(NULL AS NUMBER) WIP_PLAN_QTY_SG, CAST(NULL AS VARCHAR2(150)) CONTRIBUTE_YIELD_FLAG, MP.ORGANIZATION_CODE SOURCE_ORG_CODE, WDJ.WIP_ENTITY_ID WIP_ENTITY_ID, WDJ.PRIMARY_ITEM_ID PRIMARY_ITEM_ID, WDJ.LAST_UPDATE_DATE LAST_UPDATE_DATE FROM WIP_DISCRETE_JOBS WDJ, WIP_ENTITIES WE, FND_LOOKUPS MFG_LOOKUPS, MTL_PARAMETERS MP, fnd_lookup_values fl WHERE WE.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID AND WE.ORGANIZATION_ID=WDJ.ORGANIZATION_ID AND WDJ.JOB_TYPE IN (1,3) AND WE.ENTITY_TYPE IN (1,3,5,8) AND MFG_LOOKUPS.LOOKUP_CODE = Decode(WDJ.STATUS_TYPE,16,17,17,1,WDJ.STATUS_TYPE) AND MFG_LOOKUPS.LOOKUP_TYPE='MTH_WORKORDER_STATUS_L' AND WDJ.PRIMARY_ITEM_ID IS NOT NULL AND WDJ.ORGANIZATION_ID = MP.ORGANIZATION_ID AND fl.lookup_type = 'MTH_ITEM_TYPE_L' AND fl.lookup_code = 1 AND fl.LANGUAGE=UserEnv('LANG') UNION ALL SELECT CAST(NULL AS VARCHAR2(150)) WORKORDER_FK, CAST(NULL AS VARCHAR2(150)) ITEM_FK, WFS.PLANNED_QUANTITY START_QUANTITY_G, fnd_profile.value('MTH_EBS_GLOBAL_NAME') SYSTEM_FK, CAST(NULL AS NUMBER) START_QUANTITY_SG, CAST(NULL AS NUMBER) EXPECTED_YEILD, CAST(NULL AS VARCHAR2(150)) PRIMARY_UOM, CAST(NULL AS VARCHAR2(150)) SECONDARY_UOM, WFS.QUANTITY_COMPLETED ACTUAL_COMP_QTY_G, CAST(NULL AS NUMBER) ACTUAL_COMP_QTY_SG, CAST(NULL AS NUMBER) ACTUAL_REJECTED_QTY_G, CAST(NULL AS NUMBER) ACTUAL_REJECTED_QTY_SG, CAST(NULL AS NUMBER) ACTUAL_REWORKED_QTY_G, CAST(NULL AS NUMBER) ACTUAL_REWORKED_QTY_SG, WFS.QUANTITY_SCRAPPED ACTUAL_SCRAP_QTY_G, CAST(NULL AS NUMBER) ACTUAL_SCRAP_QTY_SG, fl.meaning ITEM_TYPE_CODE, CAST(NULL AS VARCHAR2(150)) USER_DIM1_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM2_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM3_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM4_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM5_FK, CAST(NULL AS VARCHAR2(150)) USER_ATTR1, CAST(NULL AS VARCHAR2(150)) USER_ATTR2, CAST(NULL AS VARCHAR2(150)) USER_ATTR3, CAST(NULL AS VARCHAR2(150)) USER_ATTR4, CAST(NULL AS VARCHAR2(150)) USER_ATTR5, CAST(NULL AS NUMBER) USER_MEASURE1, CAST(NULL AS NUMBER) USER_MEASURE2, CAST(NULL AS NUMBER) USER_MEASURE3, CAST(NULL AS NUMBER) USER_MEASURE4, CAST(NULL AS NUMBER) USER_MEASURE5, CAST(NULL AS NUMBER) WIP_PLAN_QTY_G, CAST(NULL AS NUMBER) WIP_PLAN_QTY_SG, CAST(NULL AS VARCHAR2(150)) CONTRIBUTE_YIELD_FLAG, MP.ORGANIZATION_CODE SOURCE_ORG_CODE, WFS.WIP_ENTITY_ID WIP_ENTITY_ID, WFS.PRIMARY_ITEM_ID PRIMARY_ITEM_ID, WFS.LAST_UPDATE_DATE LAST_UPDATE_DATE FROM WIP_FLOW_SCHEDULES WFS, WIP_ENTITIES WE, FND_LOOKUPS MFG_LOOKUPS, WIP_LINES WL, MTL_PARAMETERS MP, fnd_lookup_values fl WHERE MFG_LOOKUPS.LOOKUP_CODE = Decode(WFS.STATUS,1,2,12) AND MFG_LOOKUPS.LOOKUP_TYPE ='MTH_WORKORDER_STATUS_L' AND WE.WIP_ENTITY_ID=WFS.WIP_ENTITY_ID AND WE.ORGANIZATION_ID=WFS.ORGANIZATION_ID AND WE.ENTITY_TYPE =4 AND WFS.LINE_ID=WL.LINE_ID AND WFS.PRIMARY_ITEM_ID IS NOT NULL AND WFS.ORGANIZATION_ID = MP.ORGANIZATION_ID AND fl.lookup_type = 'MTH_ITEM_TYPE_L' AND fl.lookup_code = 1 AND fl.LANGUAGE=UserEnv('LANG') UNION ALL SELECT CAST(NULL AS VARCHAR2(150)) WORKORDER_FK, CAST(NULL AS VARCHAR2(150)) ITEM_FK, GMD.START_QUANTITY_G START_QUANTITY_G, fnd_profile.value('MTH_EBS_GLOBAL_NAME') SYSTEM_FK, CAST(NULL AS NUMBER) START_QUANTITY_SG, CAST(NULL AS NUMBER) EXPECTED_YEILD, CAST(NULL AS VARCHAR2(150)) PRIMARY_UOM, CAST(NULL AS VARCHAR2(150)) SECONDARY_UOM, GMD.ACTUAL_COMP_QTY_G ACTUAL_COMP_QTY_G, CAST(NULL AS NUMBER) ACTUAL_COMP_QTY_SG, GMD.ACTUAL_REJECTED_QTY_G ACTUAL_REJECTED_QTY_G, CAST(NULL AS NUMBER) ACTUAL_REJECTED_QTY_SG, CAST(NULL AS NUMBER) ACTUAL_REWORKED_QTY_G, CAST(NULL AS NUMBER) ACTUAL_REWORKED_QTY_SG, CAST(NULL AS NUMBER) ACTUAL_SCRAP_QTY_G, CAST(NULL AS NUMBER) ACTUAL_SCRAP_QTY_SG, fl.meaning ITEM_TYPE_CODE, CAST(NULL AS VARCHAR2(150)) USER_DIM1_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM2_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM3_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM4_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM5_FK, CAST(NULL AS VARCHAR2(150)) USER_ATTR1, CAST(NULL AS VARCHAR2(150)) USER_ATTR2, CAST(NULL AS VARCHAR2(150)) USER_ATTR3, CAST(NULL AS VARCHAR2(150)) USER_ATTR4, CAST(NULL AS VARCHAR2(150)) USER_ATTR5, CAST(NULL AS NUMBER) USER_MEASURE1, CAST(NULL AS NUMBER) USER_MEASURE2, CAST(NULL AS NUMBER) USER_MEASURE3, CAST(NULL AS NUMBER) USER_MEASURE4, CAST(NULL AS NUMBER) USER_MEASURE5, GMD.WIP_PLAN_QTY_G WIP_PLAN_QTY_G, CAST(NULL AS NUMBER) WIP_PLAN_QTY_SG, GMD.CONTRIBUTE_YIELD_FLAG CONTRIBUTE_YIELD_FLAG, GMD.SOURCE_ORG_CODE SOURCE_ORG_CODE, GMD.WIP_ENTITY_ID WIP_ENTITY_ID, GMD.PRIMARY_ITEM_ID PRIMARY_ITEM_ID, GMD.LAST_UPDATE_DATE LAST_UPDATE_DATE FROM (SELECT GMD.PLAN_QTY START_QUANTITY_G, Decode(GMD.LINE_TYPE,1,GMD.ACTUAL_QTY,NULL) ACTUAL_COMP_QTY_G, Decode(GMD.LINE_TYPE,2,GMD.ACTUAL_QTY,NULL) ACTUAL_REJECTED_QTY_G, Decode(GMD.LINE_TYPE,1,Decode(GMD.ITEM_ID,RVR.ITEM_ID,'1','2'),'-1') ITEM_TYPE_CODE, GMD.WIP_PLAN_QTY WIP_PLAN_QTY_G, GMD.CONTRIBUTE_YIELD_IND CONTRIBUTE_YIELD_FLAG, MP.ORGANIZATION_CODE SOURCE_ORG_CODE, GBH.BATCH_ID WIP_ENTITY_ID, GMD.INVENTORY_ITEM_ID PRIMARY_ITEM_ID, Greatest(GBH.LAST_UPDATE_DATE,GMD.LAST_UPDATE_DATE) LAST_UPDATE_DATE FROM GME_BATCH_HEADER GBH, FND_LOOKUPS GL, GMD_RECIPES_VL GRL, FM_FORM_MST_VL FML, GMD_RECIPE_VALIDITY_RULES RVR, MTL_PARAMETERS MP, GME_MATERIAL_DETAILS GMD WHERE GBH.BATCH_TYPE = 0 AND GL.LOOKUP_TYPE = 'MTH_WORKORDER_STATUS_L' AND GL.LOOKUP_CODE = Decode(GBH.BATCH_STATUS,-1,7,3,4,4,12,GBH.BATCH_STATUS) AND GBH.RECIPE_VALIDITY_RULE_ID = RVR.RECIPE_VALIDITY_RULE_ID AND RVR.RECIPE_ID = GRL.RECIPE_ID AND GBH.FORMULA_ID = FML.FORMULA_ID AND GBH.ORGANIZATION_ID =MP.ORGANIZATION_ID AND GBH.LABORATORY_IND = 0 AND GMD.BATCH_ID = GBH.BATCH_ID AND GMD.LINE_TYPE <> -1) GMD,( SELECT LOOKUP_CODE,meaning FROM fnd_lookup_values fl WHERE fl.LOOKUP_TYPE ='MTH_ITEM_TYPE_L' AND fl.LANGUAGE = USERENV('LANG') AND fl.ENABLED_FLAG ='Y' ) fl WHERE GMD.ITEM_TYPE_CODE=fl.LOOKUP_CODE )

View Text - HTML Formatted

SELECT WORKORDER_FK
, ITEM_FK
, START_QUANTITY_G
, SYSTEM_FK
, START_QUANTITY_SG
, EXPECTED_YEILD
, PRIMARY_UOM
, SECONDARY_UOM
, ACTUAL_COMP_QTY_G
, ACTUAL_COMP_QTY_SG
, ACTUAL_REJECTED_QTY_G
, ACTUAL_REJECTED_QTY_SG
, ACTUAL_REWORKED_QTY_G
, ACTUAL_REWORKED_QTY_SG
, ACTUAL_SCRAP_QTY_G
, ACTUAL_SCRAP_QTY_SG
, ITEM_TYPE_CODE
, USER_DIM1_FK
, USER_DIM2_FK
, USER_DIM3_FK
, USER_DIM4_FK
, USER_DIM5_FK
, USER_ATTR1
, USER_ATTR2
, USER_ATTR3
, USER_ATTR4
, USER_ATTR5
, USER_MEASURE1
, USER_MEASURE2
, USER_MEASURE3
, USER_MEASURE4
, USER_MEASURE5
, WIP_PLAN_QTY_G
, WIP_PLAN_QTY_SG
, CONTRIBUTE_YIELD_FLAG
, SOURCE_ORG_CODE
, WIP_ENTITY_ID
, PRIMARY_ITEM_ID
, LAST_UPDATE_DATE
FROM (SELECT CAST(NULL AS VARCHAR2(150)) WORKORDER_FK
, CAST(NULL AS VARCHAR2(150)) ITEM_FK
, WDJ.START_QUANTITY START_QUANTITY_G
, FND_PROFILE.VALUE('MTH_EBS_GLOBAL_NAME') SYSTEM_FK
, CAST(NULL AS NUMBER) START_QUANTITY_SG
, CAST(NULL AS NUMBER) EXPECTED_YEILD
, CAST(NULL AS VARCHAR2(150)) PRIMARY_UOM
, CAST(NULL AS VARCHAR2(150)) SECONDARY_UOM
, WDJ.QUANTITY_COMPLETED ACTUAL_COMP_QTY_G
, CAST(NULL AS NUMBER) ACTUAL_COMP_QTY_SG
, CAST(NULL AS NUMBER) ACTUAL_REJECTED_QTY_G
, CAST(NULL AS NUMBER) ACTUAL_REJECTED_QTY_SG
, CAST(NULL AS NUMBER) ACTUAL_REWORKED_QTY_G
, CAST(NULL AS NUMBER) ACTUAL_REWORKED_QTY_SG
, WDJ.QUANTITY_SCRAPPED ACTUAL_SCRAP_QTY_G
, CAST(NULL AS NUMBER) ACTUAL_SCRAP_QTY_SG
, FL.MEANING ITEM_TYPE_CODE
, CAST(NULL AS VARCHAR2(150)) USER_DIM1_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM2_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM3_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM4_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM5_FK
, CAST(NULL AS VARCHAR2(150)) USER_ATTR1
, CAST(NULL AS VARCHAR2(150)) USER_ATTR2
, CAST(NULL AS VARCHAR2(150)) USER_ATTR3
, CAST(NULL AS VARCHAR2(150)) USER_ATTR4
, CAST(NULL AS VARCHAR2(150)) USER_ATTR5
, CAST(NULL AS NUMBER) USER_MEASURE1
, CAST(NULL AS NUMBER) USER_MEASURE2
, CAST(NULL AS NUMBER) USER_MEASURE3
, CAST(NULL AS NUMBER) USER_MEASURE4
, CAST(NULL AS NUMBER) USER_MEASURE5
, CAST(NULL AS NUMBER) WIP_PLAN_QTY_G
, CAST(NULL AS NUMBER) WIP_PLAN_QTY_SG
, CAST(NULL AS VARCHAR2(150)) CONTRIBUTE_YIELD_FLAG
, MP.ORGANIZATION_CODE SOURCE_ORG_CODE
, WDJ.WIP_ENTITY_ID WIP_ENTITY_ID
, WDJ.PRIMARY_ITEM_ID PRIMARY_ITEM_ID
, WDJ.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM WIP_DISCRETE_JOBS WDJ
, WIP_ENTITIES WE
, FND_LOOKUPS MFG_LOOKUPS
, MTL_PARAMETERS MP
, FND_LOOKUP_VALUES FL
WHERE WE.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=WDJ.ORGANIZATION_ID
AND WDJ.JOB_TYPE IN (1
, 3)
AND WE.ENTITY_TYPE IN (1
, 3
, 5
, 8)
AND MFG_LOOKUPS.LOOKUP_CODE = DECODE(WDJ.STATUS_TYPE
, 16
, 17
, 17
, 1
, WDJ.STATUS_TYPE)
AND MFG_LOOKUPS.LOOKUP_TYPE='MTH_WORKORDER_STATUS_L'
AND WDJ.PRIMARY_ITEM_ID IS NOT NULL
AND WDJ.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND FL.LOOKUP_TYPE = 'MTH_ITEM_TYPE_L'
AND FL.LOOKUP_CODE = 1
AND FL.LANGUAGE=USERENV('LANG') UNION ALL SELECT CAST(NULL AS VARCHAR2(150)) WORKORDER_FK
, CAST(NULL AS VARCHAR2(150)) ITEM_FK
, WFS.PLANNED_QUANTITY START_QUANTITY_G
, FND_PROFILE.VALUE('MTH_EBS_GLOBAL_NAME') SYSTEM_FK
, CAST(NULL AS NUMBER) START_QUANTITY_SG
, CAST(NULL AS NUMBER) EXPECTED_YEILD
, CAST(NULL AS VARCHAR2(150)) PRIMARY_UOM
, CAST(NULL AS VARCHAR2(150)) SECONDARY_UOM
, WFS.QUANTITY_COMPLETED ACTUAL_COMP_QTY_G
, CAST(NULL AS NUMBER) ACTUAL_COMP_QTY_SG
, CAST(NULL AS NUMBER) ACTUAL_REJECTED_QTY_G
, CAST(NULL AS NUMBER) ACTUAL_REJECTED_QTY_SG
, CAST(NULL AS NUMBER) ACTUAL_REWORKED_QTY_G
, CAST(NULL AS NUMBER) ACTUAL_REWORKED_QTY_SG
, WFS.QUANTITY_SCRAPPED ACTUAL_SCRAP_QTY_G
, CAST(NULL AS NUMBER) ACTUAL_SCRAP_QTY_SG
, FL.MEANING ITEM_TYPE_CODE
, CAST(NULL AS VARCHAR2(150)) USER_DIM1_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM2_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM3_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM4_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM5_FK
, CAST(NULL AS VARCHAR2(150)) USER_ATTR1
, CAST(NULL AS VARCHAR2(150)) USER_ATTR2
, CAST(NULL AS VARCHAR2(150)) USER_ATTR3
, CAST(NULL AS VARCHAR2(150)) USER_ATTR4
, CAST(NULL AS VARCHAR2(150)) USER_ATTR5
, CAST(NULL AS NUMBER) USER_MEASURE1
, CAST(NULL AS NUMBER) USER_MEASURE2
, CAST(NULL AS NUMBER) USER_MEASURE3
, CAST(NULL AS NUMBER) USER_MEASURE4
, CAST(NULL AS NUMBER) USER_MEASURE5
, CAST(NULL AS NUMBER) WIP_PLAN_QTY_G
, CAST(NULL AS NUMBER) WIP_PLAN_QTY_SG
, CAST(NULL AS VARCHAR2(150)) CONTRIBUTE_YIELD_FLAG
, MP.ORGANIZATION_CODE SOURCE_ORG_CODE
, WFS.WIP_ENTITY_ID WIP_ENTITY_ID
, WFS.PRIMARY_ITEM_ID PRIMARY_ITEM_ID
, WFS.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM WIP_FLOW_SCHEDULES WFS
, WIP_ENTITIES WE
, FND_LOOKUPS MFG_LOOKUPS
, WIP_LINES WL
, MTL_PARAMETERS MP
, FND_LOOKUP_VALUES FL
WHERE MFG_LOOKUPS.LOOKUP_CODE = DECODE(WFS.STATUS
, 1
, 2
, 12)
AND MFG_LOOKUPS.LOOKUP_TYPE ='MTH_WORKORDER_STATUS_L'
AND WE.WIP_ENTITY_ID=WFS.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=WFS.ORGANIZATION_ID
AND WE.ENTITY_TYPE =4
AND WFS.LINE_ID=WL.LINE_ID
AND WFS.PRIMARY_ITEM_ID IS NOT NULL
AND WFS.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND FL.LOOKUP_TYPE = 'MTH_ITEM_TYPE_L'
AND FL.LOOKUP_CODE = 1
AND FL.LANGUAGE=USERENV('LANG') UNION ALL SELECT CAST(NULL AS VARCHAR2(150)) WORKORDER_FK
, CAST(NULL AS VARCHAR2(150)) ITEM_FK
, GMD.START_QUANTITY_G START_QUANTITY_G
, FND_PROFILE.VALUE('MTH_EBS_GLOBAL_NAME') SYSTEM_FK
, CAST(NULL AS NUMBER) START_QUANTITY_SG
, CAST(NULL AS NUMBER) EXPECTED_YEILD
, CAST(NULL AS VARCHAR2(150)) PRIMARY_UOM
, CAST(NULL AS VARCHAR2(150)) SECONDARY_UOM
, GMD.ACTUAL_COMP_QTY_G ACTUAL_COMP_QTY_G
, CAST(NULL AS NUMBER) ACTUAL_COMP_QTY_SG
, GMD.ACTUAL_REJECTED_QTY_G ACTUAL_REJECTED_QTY_G
, CAST(NULL AS NUMBER) ACTUAL_REJECTED_QTY_SG
, CAST(NULL AS NUMBER) ACTUAL_REWORKED_QTY_G
, CAST(NULL AS NUMBER) ACTUAL_REWORKED_QTY_SG
, CAST(NULL AS NUMBER) ACTUAL_SCRAP_QTY_G
, CAST(NULL AS NUMBER) ACTUAL_SCRAP_QTY_SG
, FL.MEANING ITEM_TYPE_CODE
, CAST(NULL AS VARCHAR2(150)) USER_DIM1_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM2_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM3_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM4_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM5_FK
, CAST(NULL AS VARCHAR2(150)) USER_ATTR1
, CAST(NULL AS VARCHAR2(150)) USER_ATTR2
, CAST(NULL AS VARCHAR2(150)) USER_ATTR3
, CAST(NULL AS VARCHAR2(150)) USER_ATTR4
, CAST(NULL AS VARCHAR2(150)) USER_ATTR5
, CAST(NULL AS NUMBER) USER_MEASURE1
, CAST(NULL AS NUMBER) USER_MEASURE2
, CAST(NULL AS NUMBER) USER_MEASURE3
, CAST(NULL AS NUMBER) USER_MEASURE4
, CAST(NULL AS NUMBER) USER_MEASURE5
, GMD.WIP_PLAN_QTY_G WIP_PLAN_QTY_G
, CAST(NULL AS NUMBER) WIP_PLAN_QTY_SG
, GMD.CONTRIBUTE_YIELD_FLAG CONTRIBUTE_YIELD_FLAG
, GMD.SOURCE_ORG_CODE SOURCE_ORG_CODE
, GMD.WIP_ENTITY_ID WIP_ENTITY_ID
, GMD.PRIMARY_ITEM_ID PRIMARY_ITEM_ID
, GMD.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM (SELECT GMD.PLAN_QTY START_QUANTITY_G
, DECODE(GMD.LINE_TYPE
, 1
, GMD.ACTUAL_QTY
, NULL) ACTUAL_COMP_QTY_G
, DECODE(GMD.LINE_TYPE
, 2
, GMD.ACTUAL_QTY
, NULL) ACTUAL_REJECTED_QTY_G
, DECODE(GMD.LINE_TYPE
, 1
, DECODE(GMD.ITEM_ID
, RVR.ITEM_ID
, '1'
, '2')
, '-1') ITEM_TYPE_CODE
, GMD.WIP_PLAN_QTY WIP_PLAN_QTY_G
, GMD.CONTRIBUTE_YIELD_IND CONTRIBUTE_YIELD_FLAG
, MP.ORGANIZATION_CODE SOURCE_ORG_CODE
, GBH.BATCH_ID WIP_ENTITY_ID
, GMD.INVENTORY_ITEM_ID PRIMARY_ITEM_ID
, GREATEST(GBH.LAST_UPDATE_DATE
, GMD.LAST_UPDATE_DATE) LAST_UPDATE_DATE
FROM GME_BATCH_HEADER GBH
, FND_LOOKUPS GL
, GMD_RECIPES_VL GRL
, FM_FORM_MST_VL FML
, GMD_RECIPE_VALIDITY_RULES RVR
, MTL_PARAMETERS MP
, GME_MATERIAL_DETAILS GMD
WHERE GBH.BATCH_TYPE = 0
AND GL.LOOKUP_TYPE = 'MTH_WORKORDER_STATUS_L'
AND GL.LOOKUP_CODE = DECODE(GBH.BATCH_STATUS
, -1
, 7
, 3
, 4
, 4
, 12
, GBH.BATCH_STATUS)
AND GBH.RECIPE_VALIDITY_RULE_ID = RVR.RECIPE_VALIDITY_RULE_ID
AND RVR.RECIPE_ID = GRL.RECIPE_ID
AND GBH.FORMULA_ID = FML.FORMULA_ID
AND GBH.ORGANIZATION_ID =MP.ORGANIZATION_ID
AND GBH.LABORATORY_IND = 0
AND GMD.BATCH_ID = GBH.BATCH_ID
AND GMD.LINE_TYPE <> -1) GMD
, ( SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES FL
WHERE FL.LOOKUP_TYPE ='MTH_ITEM_TYPE_L'
AND FL.LANGUAGE = USERENV('LANG')
AND FL.ENABLED_FLAG ='Y' ) FL
WHERE GMD.ITEM_TYPE_CODE=FL.LOOKUP_CODE )