VIEW: APPS.MSC_COLL_ITEM_ATTRIBUTES_V

Source

View Text - Preformatted

SELECT MSI.ROWID, MSI.PLAN_ID, MSI.SR_INSTANCE_ID, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, MSI.ITEM_NAME, MTP.ORGANIZATION_CODE, MSI.PLANNER_CODE, MIC.SR_CATEGORY_ID, MIC.CATEGORY_SET_ID, MIC.CATEGORY_NAME, MIC.DESCRIPTION, MLK1.MEANING, MSI.FULL_LEAD_TIME, MSI.PREPROCESSING_LEAD_TIME, MSI.POSTPROCESSING_LEAD_TIME, MSI.FIXED_LEAD_TIME, MSI.VARIABLE_LEAD_TIME, MSI.FIXED_ORDER_QUANTITY, MSI.FIXED_DAYS_SUPPLY, MSI.SHRINKAGE_RATE, MSI.FIXED_LOT_MULTIPLIER, MSI.MINIMUM_ORDER_QUANTITY, MSI.MAXIMUM_ORDER_QUANTITY, null, MSI.CARRYING_COST, MSI.DEMAND_TIME_FENCE_DAYS, MLK2.MEANING, MSI.PLANNING_TIME_FENCE_DAYS, MSI.STANDARD_COST, NVL(MSI.LIST_PRICE,0)*(1-NVL(MSI.AVERAGE_DISCOUNT,0)/100), MLK3.MEANING, MSI.LIST_PRICE, MSI.SUBSTITUTION_WINDOW, MSI.SAFETY_STOCK_BUCKET_DAYS, MSI.UNIT_WEIGHT, MSI.UNIT_VOLUME, MLK4.MEANING, MSI.SAFETY_STOCK_PERCENT, MSI.ABC_CLASS_NAME, MLK5.MEANING, MLK6.MEANING, MSI.DAYS_MAX_INV_SUPPLY, MSI.DAYS_MAX_INV_WINDOW, MSI.DAYS_TGT_INV_SUPPLY, MSI.DAYS_TGT_INV_WINDOW, MLK7.MEANING, MLK8.MEANING, MLK9.MEANING, MLK10.MEANING, MSI.PLANNING_EXCEPTION_SET, MSI.INVENTORY_USE_UP_DATE, MLK11.MEANING, NVL(MSI.list_price,0) - NVL(MSI.standard_cost,0), MSI.WEIGHT_UOM, MSI.VOLUME_UOM, MLK12.MEANING, MLK13.MEANING, MLK14.MEANING, null, MSI.LOTS_EXPIRATION, MSI.VMI_MINIMUM_UNITS, MSI.VMI_MINIMUM_DAYS, MSI.VMI_MAXIMUM_UNITS, MSI.VMI_MAXIMUM_DAYS, MSI.VMI_FIXED_ORDER_QUANTITY, MLK15.MEANING, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 'No' FROM msc_system_items msi, msc_trading_partners mtp, mfg_lookups mlk1, mfg_lookups mlk2, mfg_lookups mlk3, mfg_lookups mlk4, mfg_lookups mlk5, mfg_lookups mlk6, mfg_lookups mlk7, mfg_lookups mlk8, mfg_lookups mlk9, mfg_lookups mlk10, mfg_lookups mlk11, mfg_lookups mlk12, mfg_lookups mlk13, mfg_lookups mlk14, mfg_lookups mlk15, msc_item_categories mic WHERE msi.plan_id = -1 and msi.sr_instance_id = mic.sr_instance_id and msi.inventory_item_id = mic.inventory_item_id and msi.organization_id = mic.organization_id and mtp.partner_type = 3 and mtp.sr_tp_id = msi.organization_id and mtp.sr_instance_id = msi.sr_instance_id and mlk1.lookup_type(+) = 'SYS_YES_NO' and mlk1.lookup_code(+) = MSI.CRITICAL_COMPONENT_FLAG and mlk2.lookup_type(+) = 'MRP_ATO_FORECAST_CONTROL' and mlk2.lookup_code(+) = MSI.ATO_FORECAST_CONTROL and mlk3.lookup_type(+) = 'SYS_YES_NO' and mlk3.lookup_code(+) = MSI.PIP_FLAG and mlk4.lookup_type(+) = 'MTL_SAFETY_STOCK' and mlk4.lookup_code(+) = MSI.SAFETY_STOCK_CODE and mlk5.lookup_type(+) = 'MRP_PLANNING_CODE' and mlk5.lookup_code(+) = MSI.MRP_PLANNING_CODE and mlk6.lookup_type(+) = 'SYS_YES_NO' and mlk6.lookup_code(+) = MSI.DRP_PLANNED and mlk7.lookup_type(+) = 'MSC_CONTINOUS_TRANSFER' and mlk7.lookup_code(+) = MSI.CONTINOUS_TRANSFER and mlk8.lookup_type(+) = 'MSC_CONVERGENCE' and mlk8.lookup_code(+) = MSI.CONVERGENCE and mlk9.lookup_type(+) = 'SYS_YES_NO' and mlk9.lookup_code(+) = MSI.CREATE_SUPPLY_FLAG and mlk10.lookup_type(+) = 'MSC_DIVERGENCE' and mlk10.lookup_code(+) = MSI.DIVERGENCE and mlk11.lookup_type(+) = 'MTL_PLANNING_MAKE_BUY' and mlk11.lookup_code(+) = MSI.PLANNING_MAKE_BUY_CODE and mlk12.lookup_type(+) = 'SYS_YES_NO' and mlk12.lookup_code(+) = MSI.ROUNDING_CONTROL_TYPE and mlk13.lookup_type(+) = 'MSC_ATP_COMPONENTS_FLAG' and mlk13.lookup_code(+) = to_number(DECODE(MSI.ATP_FLAG,'N',1,'Y',2,'R',3,'C',4)) and mlk14.lookup_type(+) = 'MSC_ATP_COMPONENTS_FLAG' and mlk14.lookup_code(+) = to_number(DECODE(MSI.ATP_COMPONENTS_FLAG,'N',1,'Y',2,'R',3,'C',4)) and mlk15.lookup_type(+) = 'MSC_CONSIGNED_FLAG' and mlk15.lookup_code(+) = MSI.CONSIGNED_FLAG

View Text - HTML Formatted

SELECT MSI.ROWID
, MSI.PLAN_ID
, MSI.SR_INSTANCE_ID
, MSI.ORGANIZATION_ID
, MSI.INVENTORY_ITEM_ID
, MSI.ITEM_NAME
, MTP.ORGANIZATION_CODE
, MSI.PLANNER_CODE
, MIC.SR_CATEGORY_ID
, MIC.CATEGORY_SET_ID
, MIC.CATEGORY_NAME
, MIC.DESCRIPTION
, MLK1.MEANING
, MSI.FULL_LEAD_TIME
, MSI.PREPROCESSING_LEAD_TIME
, MSI.POSTPROCESSING_LEAD_TIME
, MSI.FIXED_LEAD_TIME
, MSI.VARIABLE_LEAD_TIME
, MSI.FIXED_ORDER_QUANTITY
, MSI.FIXED_DAYS_SUPPLY
, MSI.SHRINKAGE_RATE
, MSI.FIXED_LOT_MULTIPLIER
, MSI.MINIMUM_ORDER_QUANTITY
, MSI.MAXIMUM_ORDER_QUANTITY
, NULL
, MSI.CARRYING_COST
, MSI.DEMAND_TIME_FENCE_DAYS
, MLK2.MEANING
, MSI.PLANNING_TIME_FENCE_DAYS
, MSI.STANDARD_COST
, NVL(MSI.LIST_PRICE
, 0)*(1-NVL(MSI.AVERAGE_DISCOUNT
, 0)/100)
, MLK3.MEANING
, MSI.LIST_PRICE
, MSI.SUBSTITUTION_WINDOW
, MSI.SAFETY_STOCK_BUCKET_DAYS
, MSI.UNIT_WEIGHT
, MSI.UNIT_VOLUME
, MLK4.MEANING
, MSI.SAFETY_STOCK_PERCENT
, MSI.ABC_CLASS_NAME
, MLK5.MEANING
, MLK6.MEANING
, MSI.DAYS_MAX_INV_SUPPLY
, MSI.DAYS_MAX_INV_WINDOW
, MSI.DAYS_TGT_INV_SUPPLY
, MSI.DAYS_TGT_INV_WINDOW
, MLK7.MEANING
, MLK8.MEANING
, MLK9.MEANING
, MLK10.MEANING
, MSI.PLANNING_EXCEPTION_SET
, MSI.INVENTORY_USE_UP_DATE
, MLK11.MEANING
, NVL(MSI.LIST_PRICE
, 0) - NVL(MSI.STANDARD_COST
, 0)
, MSI.WEIGHT_UOM
, MSI.VOLUME_UOM
, MLK12.MEANING
, MLK13.MEANING
, MLK14.MEANING
, NULL
, MSI.LOTS_EXPIRATION
, MSI.VMI_MINIMUM_UNITS
, MSI.VMI_MINIMUM_DAYS
, MSI.VMI_MAXIMUM_UNITS
, MSI.VMI_MAXIMUM_DAYS
, MSI.VMI_FIXED_ORDER_QUANTITY
, MLK15.MEANING
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'NO'
FROM MSC_SYSTEM_ITEMS MSI
, MSC_TRADING_PARTNERS MTP
, MFG_LOOKUPS MLK1
, MFG_LOOKUPS MLK2
, MFG_LOOKUPS MLK3
, MFG_LOOKUPS MLK4
, MFG_LOOKUPS MLK5
, MFG_LOOKUPS MLK6
, MFG_LOOKUPS MLK7
, MFG_LOOKUPS MLK8
, MFG_LOOKUPS MLK9
, MFG_LOOKUPS MLK10
, MFG_LOOKUPS MLK11
, MFG_LOOKUPS MLK12
, MFG_LOOKUPS MLK13
, MFG_LOOKUPS MLK14
, MFG_LOOKUPS MLK15
, MSC_ITEM_CATEGORIES MIC
WHERE MSI.PLAN_ID = -1
AND MSI.SR_INSTANCE_ID = MIC.SR_INSTANCE_ID
AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MIC.ORGANIZATION_ID
AND MTP.PARTNER_TYPE = 3
AND MTP.SR_TP_ID = MSI.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND MLK1.LOOKUP_TYPE(+) = 'SYS_YES_NO'
AND MLK1.LOOKUP_CODE(+) = MSI.CRITICAL_COMPONENT_FLAG
AND MLK2.LOOKUP_TYPE(+) = 'MRP_ATO_FORECAST_CONTROL'
AND MLK2.LOOKUP_CODE(+) = MSI.ATO_FORECAST_CONTROL
AND MLK3.LOOKUP_TYPE(+) = 'SYS_YES_NO'
AND MLK3.LOOKUP_CODE(+) = MSI.PIP_FLAG
AND MLK4.LOOKUP_TYPE(+) = 'MTL_SAFETY_STOCK'
AND MLK4.LOOKUP_CODE(+) = MSI.SAFETY_STOCK_CODE
AND MLK5.LOOKUP_TYPE(+) = 'MRP_PLANNING_CODE'
AND MLK5.LOOKUP_CODE(+) = MSI.MRP_PLANNING_CODE
AND MLK6.LOOKUP_TYPE(+) = 'SYS_YES_NO'
AND MLK6.LOOKUP_CODE(+) = MSI.DRP_PLANNED
AND MLK7.LOOKUP_TYPE(+) = 'MSC_CONTINOUS_TRANSFER'
AND MLK7.LOOKUP_CODE(+) = MSI.CONTINOUS_TRANSFER
AND MLK8.LOOKUP_TYPE(+) = 'MSC_CONVERGENCE'
AND MLK8.LOOKUP_CODE(+) = MSI.CONVERGENCE
AND MLK9.LOOKUP_TYPE(+) = 'SYS_YES_NO'
AND MLK9.LOOKUP_CODE(+) = MSI.CREATE_SUPPLY_FLAG
AND MLK10.LOOKUP_TYPE(+) = 'MSC_DIVERGENCE'
AND MLK10.LOOKUP_CODE(+) = MSI.DIVERGENCE
AND MLK11.LOOKUP_TYPE(+) = 'MTL_PLANNING_MAKE_BUY'
AND MLK11.LOOKUP_CODE(+) = MSI.PLANNING_MAKE_BUY_CODE
AND MLK12.LOOKUP_TYPE(+) = 'SYS_YES_NO'
AND MLK12.LOOKUP_CODE(+) = MSI.ROUNDING_CONTROL_TYPE
AND MLK13.LOOKUP_TYPE(+) = 'MSC_ATP_COMPONENTS_FLAG'
AND MLK13.LOOKUP_CODE(+) = TO_NUMBER(DECODE(MSI.ATP_FLAG
, 'N'
, 1
, 'Y'
, 2
, 'R'
, 3
, 'C'
, 4))
AND MLK14.LOOKUP_TYPE(+) = 'MSC_ATP_COMPONENTS_FLAG'
AND MLK14.LOOKUP_CODE(+) = TO_NUMBER(DECODE(MSI.ATP_COMPONENTS_FLAG
, 'N'
, 1
, 'Y'
, 2
, 'R'
, 3
, 'C'
, 4))
AND MLK15.LOOKUP_TYPE(+) = 'MSC_CONSIGNED_FLAG'
AND MLK15.LOOKUP_CODE(+) = MSI.CONSIGNED_FLAG