VIEW: APPS.GMD_MATERIAL_EFFECTIVITIES_VW

Source

View Text - Preformatted

SELECT formulaline_id, formula_id, line_type, line_no, inventory_item_id, qty, detail_uom, release_type, scrap_factor, scale_type, cost_alloc, phantom_type, rework_type, text_code, contribute_step_qty_ind, contribute_yield_ind, scale_multiple, scale_rounding_variance, rounding_direction, by_product_type, buffer_ind, NULL start_date, ingredient_end_date end_date, 1 Original_Item_flag, NULL preference, NULL sub_original_qty, NULL sub_replace_qty, NULL replacement_uom, NULL line_item_id, NULL line_item_uom, NULL line_item_primary_uom, NULL line_item_qty, NULL substitution_item_uom, organization_id FROM fm_matl_dtl WHERE 1 = 1 UNION ALL SELECT d.formulaline_id, d.formula_id, d.line_type, d.line_no, s.inventory_item_id item_id, NULL qty, s.detail_uom item_um, d.release_type, d.scrap_factor, d.scale_type, d.cost_alloc, d.phantom_type, d.rework_type, d.text_code, d.contribute_step_qty_ind, d.contribute_yield_ind, d.scale_multiple, d.scale_rounding_variance, d.rounding_direction, d.by_product_type, d.buffer_ind, h.start_date start_date, h.end_date end_date, 0 Original_Item_flag, h.preference, h.original_qty sub_original_qty, s.unit_qty sub_replace_qty, DECODE(h.replacement_uom_type, 1, d.detail_uom, s.detail_uom) replacement_uom, h.original_inventory_item_id line_item_id, d.detail_uom line_item_uom, h.original_uom line_item_primary_uom, d.qty line_item_qty, s.detail_uom substitution_item_uom, organization_id FROM fm_matl_dtl d, gmd_item_substitution_hdr_b h, gmd_item_substitution_dtl s, gmd_formula_substitution f WHERE d.formula_id = f.formula_id AND d.line_type = - 1 AND d.ingredient_end_date IS NOT NULL AND f.associated_flag = 'Y' AND (h.substitution_status >= 700 AND h.substitution_status < 799) AND f.substitution_id = h.substitution_id AND s.substitution_id = h.substitution_id AND h.original_inventory_item_id = d.inventory_item_id

View Text - HTML Formatted

SELECT FORMULALINE_ID
, FORMULA_ID
, LINE_TYPE
, LINE_NO
, INVENTORY_ITEM_ID
, QTY
, DETAIL_UOM
, RELEASE_TYPE
, SCRAP_FACTOR
, SCALE_TYPE
, COST_ALLOC
, PHANTOM_TYPE
, REWORK_TYPE
, TEXT_CODE
, CONTRIBUTE_STEP_QTY_IND
, CONTRIBUTE_YIELD_IND
, SCALE_MULTIPLE
, SCALE_ROUNDING_VARIANCE
, ROUNDING_DIRECTION
, BY_PRODUCT_TYPE
, BUFFER_IND
, NULL START_DATE
, INGREDIENT_END_DATE END_DATE
, 1 ORIGINAL_ITEM_FLAG
, NULL PREFERENCE
, NULL SUB_ORIGINAL_QTY
, NULL SUB_REPLACE_QTY
, NULL REPLACEMENT_UOM
, NULL LINE_ITEM_ID
, NULL LINE_ITEM_UOM
, NULL LINE_ITEM_PRIMARY_UOM
, NULL LINE_ITEM_QTY
, NULL SUBSTITUTION_ITEM_UOM
, ORGANIZATION_ID
FROM FM_MATL_DTL
WHERE 1 = 1 UNION ALL SELECT D.FORMULALINE_ID
, D.FORMULA_ID
, D.LINE_TYPE
, D.LINE_NO
, S.INVENTORY_ITEM_ID ITEM_ID
, NULL QTY
, S.DETAIL_UOM ITEM_UM
, D.RELEASE_TYPE
, D.SCRAP_FACTOR
, D.SCALE_TYPE
, D.COST_ALLOC
, D.PHANTOM_TYPE
, D.REWORK_TYPE
, D.TEXT_CODE
, D.CONTRIBUTE_STEP_QTY_IND
, D.CONTRIBUTE_YIELD_IND
, D.SCALE_MULTIPLE
, D.SCALE_ROUNDING_VARIANCE
, D.ROUNDING_DIRECTION
, D.BY_PRODUCT_TYPE
, D.BUFFER_IND
, H.START_DATE START_DATE
, H.END_DATE END_DATE
, 0 ORIGINAL_ITEM_FLAG
, H.PREFERENCE
, H.ORIGINAL_QTY SUB_ORIGINAL_QTY
, S.UNIT_QTY SUB_REPLACE_QTY
, DECODE(H.REPLACEMENT_UOM_TYPE
, 1
, D.DETAIL_UOM
, S.DETAIL_UOM) REPLACEMENT_UOM
, H.ORIGINAL_INVENTORY_ITEM_ID LINE_ITEM_ID
, D.DETAIL_UOM LINE_ITEM_UOM
, H.ORIGINAL_UOM LINE_ITEM_PRIMARY_UOM
, D.QTY LINE_ITEM_QTY
, S.DETAIL_UOM SUBSTITUTION_ITEM_UOM
, ORGANIZATION_ID
FROM FM_MATL_DTL D
, GMD_ITEM_SUBSTITUTION_HDR_B H
, GMD_ITEM_SUBSTITUTION_DTL S
, GMD_FORMULA_SUBSTITUTION F
WHERE D.FORMULA_ID = F.FORMULA_ID
AND D.LINE_TYPE = - 1
AND D.INGREDIENT_END_DATE IS NOT NULL
AND F.ASSOCIATED_FLAG = 'Y'
AND (H.SUBSTITUTION_STATUS >= 700
AND H.SUBSTITUTION_STATUS < 799)
AND F.SUBSTITUTION_ID = H.SUBSTITUTION_ID
AND S.SUBSTITUTION_ID = H.SUBSTITUTION_ID
AND H.ORIGINAL_INVENTORY_ITEM_ID = D.INVENTORY_ITEM_ID