VIEW: APPS.GMD_MBR_STEP_MATERIAL_V1

Source

View Text - Preformatted

SELECT MATL.FORMULALINE_ID ,MATL.FORMULA_ID ,MATL.LINE_TYPE ,lkup.MEANING ,MATL.LINE_NO ,DECODE(ORIGINAL_ITEM_FLAG, 1, matl.qty, GMDFMVAL_PUB.GET_LINE_QTY(matl.line_item_id ,matl.organization_id ,matl.line_item_qty ,matl.line_item_uom ,matl.item_id ,matl.sub_original_qty ,matl.line_item_primary_uom ,matl.sub_replace_qty ,matl.substitution_item_uom ,matl.replacement_uom)) qty ,MATL.RELEASE_TYPE ,MATL.SCRAP_FACTOR ,MATL.SCALE_TYPE ,MATL.COST_ALLOC ,MATL.PHANTOM_TYPE ,NULL SCALE_UOM ,MATL.CONTRIBUTE_STEP_QTY_IND ,MATL.CONTRIBUTE_YIELD_IND ,MATL.SCALE_MULTIPLE ,MATL.SCALE_ROUNDING_VARIANCE ,MATL.ROUNDING_DIRECTION ,MATL.BY_PRODUCT_TYPE ,MATL.BUFFER_IND ,MATL.ITEM_ID INVENTORY_ITEM_ID ,MSI.CONCATENATED_SEGMENTS ,MSI.DESCRIPTION ,MATL.ORGANIZATION_ID ,MATL.ITEM_UM ,NULL REVISION ,rcp.recipe_id ,rcp.recipe_id||'$'||MATL.FORMULALINE_ID ||'$'||MATL.ITEM_ID ,GMD_MBR_UTIL_PKG.GET_SAMPLING_PLAN(MATL.ITEM_ID ,null ,MATL.ORGANIZATION_ID ,null ,RCP.RECIPE_ID ,RCP.RECIPE_NO ,RCP.RECIPE_VERSION ,FRM.FORMULA_ID ,MATL.FORMULALINE_ID ,FRM.FORMULA_NO ,FRM.FORMULA_VERS ,rout.ROUTING_ID ,rout.ROUTING_NO ,rout.ROUTING_VERS ,RDTL.ROUTINGSTEP_ID ,RDTL.ROUTINGSTEP_NO ,OPRN.OPRN_ID ,OPRN.OPRN_NO ,OPRN.OPRN_VERS ,NULL ,SYSDATE ,'N' ,null ,'N') SAMPLE_PLAN_ID ,RDTL.ROUTINGSTEP_ID, GMO_MBR_UTIL.get_dispense_config(MATL.ITEM_ID,matl.organization_id,rcp.recipe_id) gmo_disp_config_id FROM gmd_recipes_b rcp, gmd_recipe_step_materials smatl, GMD_ROUTINGS_B rout, GMD_OPERATIONS_B OPRN, FM_ROUT_DTL RDTL, (SELECT * FROM (SELECT vw.*, RANK() OVER (PARTITION BY formula_id, line_type, line_no ORDER BY preference, original_item_flag) AS rank FROM gmd_material_effectivities_vw vw WHERE ((sysdate BETWEEN NVL(vw.start_date, sysdate) and NVL(vw.end_date, sysdate)) OR original_item_flag = 1) ) WHERE rank = 1)MATL, FM_FORM_MST FRM, MTL_SYSTEM_ITEMS_VL MSI, GEM_LOOKUPS lkup WHERE rcp.formula_id = matl.formula_id AND matl.formula_id = frm.formula_id AND MSI.INVENTORY_ITEM_ID = MATL.ITEM_ID AND MSI.ORGANIZATION_ID = MATL.ORGANIZATION_ID AND smatl.formulaline_id = matl.formulaline_id AND lkup.LOOKUP_TYPE = 'GMD_FORMULA_ITEM_TYPE' AND MATL.LINE_TYPE = lkup.LOOKUP_CODE AND rcp.formula_id = frm.formula_id AND RDTL.ROUTINGSTEP_ID = smatl.ROUTINGSTEP_ID AND rcp.routing_id = RDTL.ROUTING_ID AND rcp.recipe_id = smatl.recipe_id AND rcp.routing_id = ROUT.ROUTING_ID AND OPRN.OPRN_ID = RDTL.OPRN_ID

View Text - HTML Formatted

SELECT MATL.FORMULALINE_ID
, MATL.FORMULA_ID
, MATL.LINE_TYPE
, LKUP.MEANING
, MATL.LINE_NO
, DECODE(ORIGINAL_ITEM_FLAG
, 1
, MATL.QTY
, GMDFMVAL_PUB.GET_LINE_QTY(MATL.LINE_ITEM_ID
, MATL.ORGANIZATION_ID
, MATL.LINE_ITEM_QTY
, MATL.LINE_ITEM_UOM
, MATL.ITEM_ID
, MATL.SUB_ORIGINAL_QTY
, MATL.LINE_ITEM_PRIMARY_UOM
, MATL.SUB_REPLACE_QTY
, MATL.SUBSTITUTION_ITEM_UOM
, MATL.REPLACEMENT_UOM)) QTY
, MATL.RELEASE_TYPE
, MATL.SCRAP_FACTOR
, MATL.SCALE_TYPE
, MATL.COST_ALLOC
, MATL.PHANTOM_TYPE
, NULL SCALE_UOM
, MATL.CONTRIBUTE_STEP_QTY_IND
, MATL.CONTRIBUTE_YIELD_IND
, MATL.SCALE_MULTIPLE
, MATL.SCALE_ROUNDING_VARIANCE
, MATL.ROUNDING_DIRECTION
, MATL.BY_PRODUCT_TYPE
, MATL.BUFFER_IND
, MATL.ITEM_ID INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS
, MSI.DESCRIPTION
, MATL.ORGANIZATION_ID
, MATL.ITEM_UM
, NULL REVISION
, RCP.RECIPE_ID
, RCP.RECIPE_ID||'$'||MATL.FORMULALINE_ID ||'$'||MATL.ITEM_ID
, GMD_MBR_UTIL_PKG.GET_SAMPLING_PLAN(MATL.ITEM_ID
, NULL
, MATL.ORGANIZATION_ID
, NULL
, RCP.RECIPE_ID
, RCP.RECIPE_NO
, RCP.RECIPE_VERSION
, FRM.FORMULA_ID
, MATL.FORMULALINE_ID
, FRM.FORMULA_NO
, FRM.FORMULA_VERS
, ROUT.ROUTING_ID
, ROUT.ROUTING_NO
, ROUT.ROUTING_VERS
, RDTL.ROUTINGSTEP_ID
, RDTL.ROUTINGSTEP_NO
, OPRN.OPRN_ID
, OPRN.OPRN_NO
, OPRN.OPRN_VERS
, NULL
, SYSDATE
, 'N'
, NULL
, 'N') SAMPLE_PLAN_ID
, RDTL.ROUTINGSTEP_ID
, GMO_MBR_UTIL.GET_DISPENSE_CONFIG(MATL.ITEM_ID
, MATL.ORGANIZATION_ID
, RCP.RECIPE_ID) GMO_DISP_CONFIG_ID
FROM GMD_RECIPES_B RCP
, GMD_RECIPE_STEP_MATERIALS SMATL
, GMD_ROUTINGS_B ROUT
, GMD_OPERATIONS_B OPRN
, FM_ROUT_DTL RDTL
, (SELECT *
FROM (SELECT VW.*
, RANK() OVER (PARTITION BY FORMULA_ID
, LINE_TYPE
, LINE_NO ORDER BY PREFERENCE
, ORIGINAL_ITEM_FLAG) AS RANK
FROM GMD_MATERIAL_EFFECTIVITIES_VW VW
WHERE ((SYSDATE BETWEEN NVL(VW.START_DATE
, SYSDATE)
AND NVL(VW.END_DATE
, SYSDATE)) OR ORIGINAL_ITEM_FLAG = 1) )
WHERE RANK = 1)MATL
, FM_FORM_MST FRM
, MTL_SYSTEM_ITEMS_VL MSI
, GEM_LOOKUPS LKUP
WHERE RCP.FORMULA_ID = MATL.FORMULA_ID
AND MATL.FORMULA_ID = FRM.FORMULA_ID
AND MSI.INVENTORY_ITEM_ID = MATL.ITEM_ID
AND MSI.ORGANIZATION_ID = MATL.ORGANIZATION_ID
AND SMATL.FORMULALINE_ID = MATL.FORMULALINE_ID
AND LKUP.LOOKUP_TYPE = 'GMD_FORMULA_ITEM_TYPE'
AND MATL.LINE_TYPE = LKUP.LOOKUP_CODE
AND RCP.FORMULA_ID = FRM.FORMULA_ID
AND RDTL.ROUTINGSTEP_ID = SMATL.ROUTINGSTEP_ID
AND RCP.ROUTING_ID = RDTL.ROUTING_ID
AND RCP.RECIPE_ID = SMATL.RECIPE_ID
AND RCP.ROUTING_ID = ROUT.ROUTING_ID
AND OPRN.OPRN_ID = RDTL.OPRN_ID