VIEW: APPS.GMD_MBR_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 ,rcp.ROUTING_ID ,null ,null ,null ,null ,null ,null ,null ,NULL ,SYSDATE ,'N' ,null ,'N') SAMPLE_PLAN_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, (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 lkup.LOOKUP_TYPE = 'GMD_FORMULA_ITEM_TYPE' AND MATL.LINE_TYPE = lkup.LOOKUP_CODE AND rcp.formula_id = frm.formula_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
, RCP.ROUTING_ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, SYSDATE
, 'N'
, NULL
, 'N') SAMPLE_PLAN_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
, (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 LKUP.LOOKUP_TYPE = 'GMD_FORMULA_ITEM_TYPE'
AND MATL.LINE_TYPE = LKUP.LOOKUP_CODE
AND RCP.FORMULA_ID = FRM.FORMULA_ID