VIEW: APPS.MSD_SPF_MODEL_BOM_COMPONENTS_V

Source

View Text - Preformatted

SELECT to_char(mbc.sr_instance_id) || '::' || to_char(mbc.ebs_base_model_sr_pk) || '-' || to_char(mbc.dm_item_sr_pk) spf_code, parent_item.item_name parent_code, child_item.item_name child_code, lr_item.item_name lr_code, decode(mbc.ebs_base_model_sr_pk, mbc.dm_item_sr_pk, msd_dem_common_utilities.get_dem_spf_base_model, msd_dem_common_utilities.get_dem_spf_option) demand_type_code, mbc.effective_date from_date, mbc.disable_date until_date, mbc.ebs_base_model_sr_pk ebs_base_model_sr_pk, mbc.dm_item_sr_pk dm_item_sr_pk, mbc.latest_rev latest_rev, mbc.sr_instance_id sr_instance_id, mbc.chain_id chain_id FROM msd_spf_model_bom_components mbc, msc_system_items parent_item, msc_system_items child_item, msc_system_items lr_item, (select to_number(parameter_value) master_org from msd_dem_setup_parameters WHERE parameter_name = 'MSD_SPF_MASTER_ORG') param WHERE parent_item.plan_id = -1 and parent_item.sr_instance_id = mbc.sr_instance_id and parent_item.organization_id = param.master_org and parent_item.sr_inventory_item_id = mbc.ebs_base_model_sr_pk and child_item.plan_id = -1 and child_item.sr_instance_id = mbc.sr_instance_id and child_item.organization_id = param.master_org and child_item.sr_inventory_item_id = mbc.dm_item_sr_pk and lr_item.plan_id = -1 and lr_item.sr_instance_id = mbc.sr_instance_id and lr_item.organization_id = param.master_org and lr_item.inventory_item_id = nvl(mbc.latest_rev, child_item.inventory_item_id)

View Text - HTML Formatted

SELECT TO_CHAR(MBC.SR_INSTANCE_ID) || '::' || TO_CHAR(MBC.EBS_BASE_MODEL_SR_PK) || '-' || TO_CHAR(MBC.DM_ITEM_SR_PK) SPF_CODE
, PARENT_ITEM.ITEM_NAME PARENT_CODE
, CHILD_ITEM.ITEM_NAME CHILD_CODE
, LR_ITEM.ITEM_NAME LR_CODE
, DECODE(MBC.EBS_BASE_MODEL_SR_PK
, MBC.DM_ITEM_SR_PK
, MSD_DEM_COMMON_UTILITIES.GET_DEM_SPF_BASE_MODEL
, MSD_DEM_COMMON_UTILITIES.GET_DEM_SPF_OPTION) DEMAND_TYPE_CODE
, MBC.EFFECTIVE_DATE FROM_DATE
, MBC.DISABLE_DATE UNTIL_DATE
, MBC.EBS_BASE_MODEL_SR_PK EBS_BASE_MODEL_SR_PK
, MBC.DM_ITEM_SR_PK DM_ITEM_SR_PK
, MBC.LATEST_REV LATEST_REV
, MBC.SR_INSTANCE_ID SR_INSTANCE_ID
, MBC.CHAIN_ID CHAIN_ID
FROM MSD_SPF_MODEL_BOM_COMPONENTS MBC
, MSC_SYSTEM_ITEMS PARENT_ITEM
, MSC_SYSTEM_ITEMS CHILD_ITEM
, MSC_SYSTEM_ITEMS LR_ITEM
, (SELECT TO_NUMBER(PARAMETER_VALUE) MASTER_ORG
FROM MSD_DEM_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_SPF_MASTER_ORG') PARAM
WHERE PARENT_ITEM.PLAN_ID = -1
AND PARENT_ITEM.SR_INSTANCE_ID = MBC.SR_INSTANCE_ID
AND PARENT_ITEM.ORGANIZATION_ID = PARAM.MASTER_ORG
AND PARENT_ITEM.SR_INVENTORY_ITEM_ID = MBC.EBS_BASE_MODEL_SR_PK
AND CHILD_ITEM.PLAN_ID = -1
AND CHILD_ITEM.SR_INSTANCE_ID = MBC.SR_INSTANCE_ID
AND CHILD_ITEM.ORGANIZATION_ID = PARAM.MASTER_ORG
AND CHILD_ITEM.SR_INVENTORY_ITEM_ID = MBC.DM_ITEM_SR_PK
AND LR_ITEM.PLAN_ID = -1
AND LR_ITEM.SR_INSTANCE_ID = MBC.SR_INSTANCE_ID
AND LR_ITEM.ORGANIZATION_ID = PARAM.MASTER_ORG
AND LR_ITEM.INVENTORY_ITEM_ID = NVL(MBC.LATEST_REV
, CHILD_ITEM.INVENTORY_ITEM_ID)