VIEW: APPS.MRP_SYSTEM_ITEMS_SC_V

Source

View Text - Preformatted

SELECT MSI.INVENTORY_ITEM_ID , IFF.ITEM_NUMBER , NVL(MPO.PLANNED_ORGANIZATION, PLANS.ORGANIZATION_ID) , MSI.ORGANIZATION_CODE , PLANS.ORGANIZATION_ID , PLANS.COMPILE_DESIGNATOR , MPC.MEANING , MSI.PREPROCESSING_LEAD_TIME , MSI.POSTPROCESSING_LEAD_TIME , MSI.FULL_LEAD_TIME , MSI.FIXED_LEAD_TIME , MSI.VARIABLE_LEAD_TIME , MBC.MEANING , MSI.REPETITIVE_TYPE , MSI.STANDARD_COST , MSI.ROUNDING_CONTROL_TYPE , round(MSI.NETTABLE_INVENTORY_QUANTITY,6) , round(MSI.NONNETTABLE_INVENTORY_QUANTITY,6) , WST.MEANING , MSI.DESCRIPTION , MSI.CATEGORY_ID , MSI.BUYER_NAME , MSI.PLANNER_CODE , MAC.ABC_CLASS_NAME , MSI.FIXED_DAYS_SUPPLY , MSI.FIXED_ORDER_QUANTITY , MSI.FIXED_LOT_MULTIPLIER , MSI.MINIMUM_ORDER_QUANTITY , MSI.MAXIMUM_ORDER_QUANTITY , MSI.SHRINKAGE_RATE , MSI.PLANNING_EXCEPTION_SET , MSI.BASE_ITEM_ID , BIT.MEANING , FC.MEANING , MSI.PRIMARY_VENDOR_ID ,DECODE(NVL(PLANS.USE_NEW_PLANNER, 2), 2, DECODE(PLANS.PLAN_TYPE, 1, DECODE(PLANS.CURR_SCHEDULE_TYPE, 2, DECODE(MSI.MRP_PLANNING_CODE, 4, 1, 2), 2), 2, DECODE(MSI.MRP_PLANNING_CODE, 3, 1, 2)), DECODE(MSI.IN_SOURCE_PLAN, 1, 1, 2)) ,MBC.LOOKUP_CODE ,EUN.LOOKUP_CODE ,EUN.MEANING ,MSI.PLANNING_TIME_FENCE_DATE ,MSI.UOM_CODE FROM MFG_LOOKUPS FC, MFG_LOOKUPS BIT, MFG_LOOKUPS MPC, MFG_LOOKUPS MBC, MFG_LOOKUPS EUN, MFG_LOOKUPS WST, MTL_ABC_CLASSES MAC, MTL_ITEM_FLEXFIELDS IFF, MRP_SYSTEM_ITEMS MSI, MRP_PLAN_ORGANIZATIONS MPO, MRP_PLANS PLANS WHERE IFF.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND IFF.ITEM_ID = MSI.INVENTORY_ITEM_ID AND MAC.ABC_CLASS_ID(+) = MSI.ABC_CLASS AND MAC.ORGANIZATION_ID(+) = MSI.ORGANIZATION_ID AND FC.LOOKUP_TYPE(+) = 'MRP_ATO_FORECAST_CONTROL' AND FC.LOOKUP_CODE(+) = MSI.ATO_FORECAST_CONTROL AND BIT.LOOKUP_TYPE(+) = 'BOM_ITEM_TYPE' AND BIT.LOOKUP_CODE(+) = MSI.BOM_ITEM_TYPE AND MPC.LOOKUP_TYPE = 'MRP_PLANNING_CODE' AND MPC.LOOKUP_CODE = MSI.MRP_PLANNING_CODE AND MBC.LOOKUP_TYPE = 'MTL_PLANNING_MAKE_BUY' AND MBC.LOOKUP_CODE = MSI.PLANNING_MAKE_BUY_CODE AND EUN.LOOKUP_TYPE(+) = 'MTL_EFFECTIVITY_CONTROL' AND EUN.LOOKUP_CODE(+) = MSI.EFFECTIVITY_CONTROL AND WST.LOOKUP_TYPE(+) = 'WIP_SUPPLY' AND WST.LOOKUP_CODE(+) = MSI.WIP_SUPPLY_TYPE AND MSI.ORGANIZATION_ID = NVL(MPO.PLANNED_ORGANIZATION, PLANS.ORGANIZATION_ID) AND MSI.COMPILE_DESIGNATOR =PLANS.COMPILE_DESIGNATOR AND MPO.PLAN_LEVEL(+) = 2 AND MPO.COMPILE_DESIGNATOR(+) = PLANS.COMPILE_DESIGNATOR AND MPO.ORGANIZATION_ID(+) =PLANS.ORGANIZATION_ID

View Text - HTML Formatted

SELECT MSI.INVENTORY_ITEM_ID
, IFF.ITEM_NUMBER
, NVL(MPO.PLANNED_ORGANIZATION
, PLANS.ORGANIZATION_ID)
, MSI.ORGANIZATION_CODE
, PLANS.ORGANIZATION_ID
, PLANS.COMPILE_DESIGNATOR
, MPC.MEANING
, MSI.PREPROCESSING_LEAD_TIME
, MSI.POSTPROCESSING_LEAD_TIME
, MSI.FULL_LEAD_TIME
, MSI.FIXED_LEAD_TIME
, MSI.VARIABLE_LEAD_TIME
, MBC.MEANING
, MSI.REPETITIVE_TYPE
, MSI.STANDARD_COST
, MSI.ROUNDING_CONTROL_TYPE
, ROUND(MSI.NETTABLE_INVENTORY_QUANTITY
, 6)
, ROUND(MSI.NONNETTABLE_INVENTORY_QUANTITY
, 6)
, WST.MEANING
, MSI.DESCRIPTION
, MSI.CATEGORY_ID
, MSI.BUYER_NAME
, MSI.PLANNER_CODE
, MAC.ABC_CLASS_NAME
, MSI.FIXED_DAYS_SUPPLY
, MSI.FIXED_ORDER_QUANTITY
, MSI.FIXED_LOT_MULTIPLIER
, MSI.MINIMUM_ORDER_QUANTITY
, MSI.MAXIMUM_ORDER_QUANTITY
, MSI.SHRINKAGE_RATE
, MSI.PLANNING_EXCEPTION_SET
, MSI.BASE_ITEM_ID
, BIT.MEANING
, FC.MEANING
, MSI.PRIMARY_VENDOR_ID
, DECODE(NVL(PLANS.USE_NEW_PLANNER
, 2)
, 2
, DECODE(PLANS.PLAN_TYPE
, 1
, DECODE(PLANS.CURR_SCHEDULE_TYPE
, 2
, DECODE(MSI.MRP_PLANNING_CODE
, 4
, 1
, 2)
, 2)
, 2
, DECODE(MSI.MRP_PLANNING_CODE
, 3
, 1
, 2))
, DECODE(MSI.IN_SOURCE_PLAN
, 1
, 1
, 2))
, MBC.LOOKUP_CODE
, EUN.LOOKUP_CODE
, EUN.MEANING
, MSI.PLANNING_TIME_FENCE_DATE
, MSI.UOM_CODE
FROM MFG_LOOKUPS FC
, MFG_LOOKUPS BIT
, MFG_LOOKUPS MPC
, MFG_LOOKUPS MBC
, MFG_LOOKUPS EUN
, MFG_LOOKUPS WST
, MTL_ABC_CLASSES MAC
, MTL_ITEM_FLEXFIELDS IFF
, MRP_SYSTEM_ITEMS MSI
, MRP_PLAN_ORGANIZATIONS MPO
, MRP_PLANS PLANS
WHERE IFF.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND IFF.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MAC.ABC_CLASS_ID(+) = MSI.ABC_CLASS
AND MAC.ORGANIZATION_ID(+) = MSI.ORGANIZATION_ID
AND FC.LOOKUP_TYPE(+) = 'MRP_ATO_FORECAST_CONTROL'
AND FC.LOOKUP_CODE(+) = MSI.ATO_FORECAST_CONTROL
AND BIT.LOOKUP_TYPE(+) = 'BOM_ITEM_TYPE'
AND BIT.LOOKUP_CODE(+) = MSI.BOM_ITEM_TYPE
AND MPC.LOOKUP_TYPE = 'MRP_PLANNING_CODE'
AND MPC.LOOKUP_CODE = MSI.MRP_PLANNING_CODE
AND MBC.LOOKUP_TYPE = 'MTL_PLANNING_MAKE_BUY'
AND MBC.LOOKUP_CODE = MSI.PLANNING_MAKE_BUY_CODE
AND EUN.LOOKUP_TYPE(+) = 'MTL_EFFECTIVITY_CONTROL'
AND EUN.LOOKUP_CODE(+) = MSI.EFFECTIVITY_CONTROL
AND WST.LOOKUP_TYPE(+) = 'WIP_SUPPLY'
AND WST.LOOKUP_CODE(+) = MSI.WIP_SUPPLY_TYPE
AND MSI.ORGANIZATION_ID = NVL(MPO.PLANNED_ORGANIZATION
, PLANS.ORGANIZATION_ID)
AND MSI.COMPILE_DESIGNATOR =PLANS.COMPILE_DESIGNATOR
AND MPO.PLAN_LEVEL(+) = 2
AND MPO.COMPILE_DESIGNATOR(+) = PLANS.COMPILE_DESIGNATOR
AND MPO.ORGANIZATION_ID(+) =PLANS.ORGANIZATION_ID