VIEW: APPS.GME_MATERIAL_DETAILS_V

Source

View Text - Preformatted

SELECT GMD.ORGANIZATION_ID, GMD.MATERIAL_DETAIL_ID, GMD.BATCH_ID, GMD.FORMULALINE_ID, GMD.INVENTORY_ITEM_ID, GMD.LINE_NO, GMD.LINE_TYPE, lkup.MEANING, GMD.PLAN_QTY, GMD.WIP_PLAN_QTY, GMD.ACTUAL_QTY, GMD.SUBINVENTORY, GMD.LOCATOR_ID, GMD.REVISION, GMD.BACKORDERED_QTY, GMD.MATERIAL_REQUIREMENT_DATE, fnd_date.date_to_displayDT(GMD.MATERIAL_REQUIREMENT_DATE,FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) MATERIAL_REQUIREMENT_DSPLY_DT, GMD.DTL_UM , MSI.CONCATENATED_SEGMENTS ITEM_NO, MSI.DESCRIPTION ITEM_DESC, GMD.PHANTOM_ID , GMD.SCRAP_FACTOR, GMD.SCALE_TYPE , GMD.ROUNDING_DIRECTION , GMD.SCALE_ROUNDING_VARIANCE, GMD.SCALE_MULTIPLE , GMD.PHANTOM_LINE_ID, MILK.CONCATENATED_SEGMENTS LOCATOR, GBH.BATCH_NO PHANTOM_BATCH_NO, GMD.TEXT_CODE, GMD.RELEASE_TYPE, rel_type.meaning RELEASE_TYPE_meaning, GMD.PHANTOM_TYPE, GMD.COST_ALLOC, GMD.CONTRIBUTE_YIELD_IND, decode(GMD.CONTRIBUTE_YIELD_IND,'Y',lkup_yes.meaning,lkup_no.meaning) CONTRIBUTE_YIELD_IND_MEANING, decode(GMD.CONTRIBUTE_STEP_QTY_IND,'Y',lkup_yes.meaning,lkup_no.meaning) CONTRIBUTE_STEP_QTY_MEANING, GMD.CONTRIBUTE_STEP_QTY_IND, GMD.ORIGINAL_QTY, GMD.BY_PRODUCT_TYPE, by_prod_typ.meaning BY_PRODUCT_TYPE_MEANING, decode(GMD.DISPENSE_IND,'Y',lkup_yes.meaning,lkup_no.meaning) DISPENSE_IND_MEANING, GMD.DISPENSE_IND FROM GME_MATERIAL_DETAILS GMD, MTL_SYSTEM_ITEMS_VL MSI , GEM_LOOKUPS lkup, MTL_ITEM_LOCATIONS_KFV MILK, GME_BATCH_HEADER GBH, GEM_LOOKUPS lkup_yes, GEM_LOOKUPS lkup_no, GEM_LOOKUPS rel_type, GEM_LOOKUPS by_prod_typ WHERE MSI.INVENTORY_ITEM_ID = GMD.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID = GMD.ORGANIZATION_ID AND lkup.LOOKUP_TYPE = 'GMD_FORMULA_ITEM_TYPE' AND GMD.LINE_TYPE = lkup.LOOKUP_CODE AND lkup_yes.lookup_type = 'GME_YES' AND lkup_yes.lookup_code = 'Y' AND lkup_No.lookup_type = 'GME_YES' AND lkup_no.lookup_code = 'N' AND rel_type.lookup_type = 'GMD_MATERIAL_RELEASE_TYPE' AND rel_type.lookup_code = GMD.RELEASE_TYPE AND by_prod_typ.lookup_type(+) = 'GMD_BY_PRODUCT_TYPE' AND by_prod_typ.lookup_code(+) = GMD.BY_PRODUCT_TYPE AND MILK.ORGANIZATION_ID (+) = GMD.ORGANIZATION_ID AND MILK.SUBINVENTORY_CODE (+) = GMD.SUBINVENTORY AND MILK.INVENTORY_LOCATION_ID (+) = GMD.LOCATOR_ID AND GBH.batch_id (+) = GMD.PHANTOM_ID

View Text - HTML Formatted

SELECT GMD.ORGANIZATION_ID
, GMD.MATERIAL_DETAIL_ID
, GMD.BATCH_ID
, GMD.FORMULALINE_ID
, GMD.INVENTORY_ITEM_ID
, GMD.LINE_NO
, GMD.LINE_TYPE
, LKUP.MEANING
, GMD.PLAN_QTY
, GMD.WIP_PLAN_QTY
, GMD.ACTUAL_QTY
, GMD.SUBINVENTORY
, GMD.LOCATOR_ID
, GMD.REVISION
, GMD.BACKORDERED_QTY
, GMD.MATERIAL_REQUIREMENT_DATE
, FND_DATE.DATE_TO_DISPLAYDT(GMD.MATERIAL_REQUIREMENT_DATE
, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) MATERIAL_REQUIREMENT_DSPLY_DT
, GMD.DTL_UM
, MSI.CONCATENATED_SEGMENTS ITEM_NO
, MSI.DESCRIPTION ITEM_DESC
, GMD.PHANTOM_ID
, GMD.SCRAP_FACTOR
, GMD.SCALE_TYPE
, GMD.ROUNDING_DIRECTION
, GMD.SCALE_ROUNDING_VARIANCE
, GMD.SCALE_MULTIPLE
, GMD.PHANTOM_LINE_ID
, MILK.CONCATENATED_SEGMENTS LOCATOR
, GBH.BATCH_NO PHANTOM_BATCH_NO
, GMD.TEXT_CODE
, GMD.RELEASE_TYPE
, REL_TYPE.MEANING RELEASE_TYPE_MEANING
, GMD.PHANTOM_TYPE
, GMD.COST_ALLOC
, GMD.CONTRIBUTE_YIELD_IND
, DECODE(GMD.CONTRIBUTE_YIELD_IND
, 'Y'
, LKUP_YES.MEANING
, LKUP_NO.MEANING) CONTRIBUTE_YIELD_IND_MEANING
, DECODE(GMD.CONTRIBUTE_STEP_QTY_IND
, 'Y'
, LKUP_YES.MEANING
, LKUP_NO.MEANING) CONTRIBUTE_STEP_QTY_MEANING
, GMD.CONTRIBUTE_STEP_QTY_IND
, GMD.ORIGINAL_QTY
, GMD.BY_PRODUCT_TYPE
, BY_PROD_TYP.MEANING BY_PRODUCT_TYPE_MEANING
, DECODE(GMD.DISPENSE_IND
, 'Y'
, LKUP_YES.MEANING
, LKUP_NO.MEANING) DISPENSE_IND_MEANING
, GMD.DISPENSE_IND
FROM GME_MATERIAL_DETAILS GMD
, MTL_SYSTEM_ITEMS_VL MSI
, GEM_LOOKUPS LKUP
, MTL_ITEM_LOCATIONS_KFV MILK
, GME_BATCH_HEADER GBH
, GEM_LOOKUPS LKUP_YES
, GEM_LOOKUPS LKUP_NO
, GEM_LOOKUPS REL_TYPE
, GEM_LOOKUPS BY_PROD_TYP
WHERE MSI.INVENTORY_ITEM_ID = GMD.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = GMD.ORGANIZATION_ID
AND LKUP.LOOKUP_TYPE = 'GMD_FORMULA_ITEM_TYPE'
AND GMD.LINE_TYPE = LKUP.LOOKUP_CODE
AND LKUP_YES.LOOKUP_TYPE = 'GME_YES'
AND LKUP_YES.LOOKUP_CODE = 'Y'
AND LKUP_NO.LOOKUP_TYPE = 'GME_YES'
AND LKUP_NO.LOOKUP_CODE = 'N'
AND REL_TYPE.LOOKUP_TYPE = 'GMD_MATERIAL_RELEASE_TYPE'
AND REL_TYPE.LOOKUP_CODE = GMD.RELEASE_TYPE
AND BY_PROD_TYP.LOOKUP_TYPE(+) = 'GMD_BY_PRODUCT_TYPE'
AND BY_PROD_TYP.LOOKUP_CODE(+) = GMD.BY_PRODUCT_TYPE
AND MILK.ORGANIZATION_ID (+) = GMD.ORGANIZATION_ID
AND MILK.SUBINVENTORY_CODE (+) = GMD.SUBINVENTORY
AND MILK.INVENTORY_LOCATION_ID (+) = GMD.LOCATOR_ID
AND GBH.BATCH_ID (+) = GMD.PHANTOM_ID