VIEW: APPS.MSD_WORK_IN_PROCESS_V

Source

View Text - Preformatted

SELECT sup.plan_id plan_id, plans.compile_designator cs_name, d_org.level_pk org_level_value_pk, d_org.level_id org_level_id, d_itm.level_pk prd_level_value_pk, d_itm.level_id prd_level_id, msd_common_utilities.get_loc_key(sup.sr_instance_id,'-3',null,30) geo_level_value_pk, 30 geo_level_id, nvl(d_dcs.level_pk, msd_common_utilities.get_dcs_key(sup.sr_instance_id, '-777',null,34)) dcs_level_value_pk, 34 dcs_level_id, 9 time_level_id, mfp.allocated_quantity quantity, trunc(sup.new_schedule_date) actual_date, sup.creation_date, sup.created_by, sup.last_update_date, sup.last_updated_by, sup.last_update_login, 1, 1, 'I', to_number(null), to_number(null) FROM msc_supplies sup, msc_plans plans, msc_demands dem, msc_full_pegging mfp, msc_system_items msi, msd_level_values d_itm, msd_level_values d_org, msd_level_values d_dcs, (select nvl(fnd_profile.value('MSD_TWO_LEVEL_PLANNING'), '2') profile_value from dual) tlp, msd_level_associations mla WHERE sup.plan_id <> -1 and sup.plan_id = plans.plan_id and nvl(sup.new_order_quantity,0) <> 0 and sup.order_type in (3,7,14,15,27,28) and sup.organization_id = msi.organization_id and sup.inventory_item_id = msi.inventory_item_id and sup.plan_id = msi.plan_id and sup.sr_instance_id = msi.sr_instance_id and d_itm.sr_level_pk = to_char(msi.sr_inventory_item_id) and d_itm.instance = to_char(msi.sr_instance_id) and d_itm.level_id = 1 and d_org.sr_level_pk = to_char(sup.organization_id) and d_org.instance = to_char(sup.sr_instance_id) and d_org.level_id = 7 and sup.sr_instance_id = mfp.sr_instance_id and sup.plan_id = mfp.plan_id and sup.transaction_id = mfp.transaction_id and mfp.PEGGING_ID = mfp.END_PEGGING_ID and not exists ( select 1 from msc_full_pegging peg where peg.transaction_id = sup.transaction_id and peg.inventory_item_id = sup.inventory_item_id and peg.organization_id = sup.organization_id and peg.plan_id = sup.plan_id and peg.sr_instance_id = sup.sr_instance_id and peg.supply_date = sup.new_schedule_date and peg.allocated_quantity = 0 and peg.demand_id = -2) and dem.sr_instance_id(+) = mfp.sr_instance_id and dem.plan_id(+) = mfp.plan_id and dem.demand_id(+) = mfp.demand_id and d_dcs.level_id(+) = 34 and d_dcs.instance(+) = to_char(dem.sr_instance_id) and d_dcs.sr_level_pk(+) = dem.demand_class and (msi.mrp_planning_code <> 6 or (msi.mrp_planning_code = 6 and msi.pick_components_flag = 'Y')) and mla.instance = to_char(msi.sr_instance_id) and mla.level_id = 1 and mla.parent_level_id = 3 and mla.sr_level_pk = to_char(msi.sr_inventory_item_id) and decode( nvl(tlp.profile_value, '2'), '1', decode(nvl(msi.ato_forecast_control,3), 3, decode(mla.sr_parent_level_pk,'-777',2,1), 1), decode( nvl(msi.ato_forecast_control, 3), 3, 2, 1) ) = 1 union all SELECT sup.plan_id plan_id, plans.compile_designator cs_name, d_org.level_pk org_level_value_pk, d_org.level_id org_level_id, d_itm.level_pk prd_level_value_pk, d_itm.level_id prd_level_id, msd_common_utilities.get_loc_key(sup.sr_instance_id,'-3',null,30) geo_level_value_pk, 30 geo_level_id, nvl(d_dcs.level_pk, msd_common_utilities.get_dcs_key(sup.sr_instance_id, '-777',null,34)) dcs_level_value_pk, 34 dcs_level_id, 9 time_level_id, sup.new_order_quantity quantity, trunc(sup.new_schedule_date) ship_date, sup.creation_date, sup.created_by, sup.last_update_date, sup.last_updated_by, sup.last_update_login, 1, 1, 'I', to_number(null), to_number(null) FROM msc_supplies sup, msc_plans plans, msc_demands dem, msc_full_pegging mfp, msc_system_items msi, msd_level_values d_itm, msd_level_values d_org, msd_level_values d_dcs, (select nvl(fnd_profile.value('MSD_TWO_LEVEL_PLANNING'), '2') profile_value from dual) tlp, msd_level_associations mla WHERE sup.plan_id <> -1 and sup.plan_id = plans.plan_id and nvl(sup.new_order_quantity,0) <> 0 and sup.order_type in (3,7,14,15,27,28) and sup.organization_id = msi.organization_id and sup.inventory_item_id = msi.inventory_item_id and sup.plan_id = msi.plan_id and sup.sr_instance_id = msi.sr_instance_id and d_itm.sr_level_pk = to_char(msi.sr_inventory_item_id) and d_itm.instance = to_char(msi.sr_instance_id) and d_itm.level_id = 1 and d_org.sr_level_pk = to_char(sup.organization_id) and d_org.instance = to_char(sup.sr_instance_id) and d_org.level_id = 7 and sup.transaction_id = mfp.transaction_id and sup.inventory_item_id = mfp.inventory_item_id and sup.organization_id = mfp.organization_id and sup.sr_instance_id = mfp.sr_instance_id and sup.plan_id = mfp.plan_id and mfp.PEGGING_ID = mfp.END_PEGGING_ID and mfp.supply_date = sup.new_schedule_date and mfp.allocated_quantity = 0 and mfp.demand_id = -2 and mfp.rowid = (select min(mpg.rowid) from msc_full_pegging mpg where mpg.transaction_id = sup.transaction_id and mpg.inventory_item_id = sup.inventory_item_id and mpg.organization_id = sup.organization_id and mpg.plan_id = sup.plan_id and mpg.sr_instance_id = sup.sr_instance_id and mpg.supply_date = sup.new_schedule_date and mpg.allocated_quantity = 0 and mpg.demand_id = -2) and dem.sr_instance_id(+) = mfp.sr_instance_id and dem.plan_id(+) = mfp.plan_id and dem.demand_id(+) = mfp.demand_id and d_dcs.level_id(+) = 34 and d_dcs.instance(+) = to_char(dem.sr_instance_id) and d_dcs.sr_level_pk(+) = dem.demand_class and (msi.mrp_planning_code <> 6 or (msi.mrp_planning_code = 6 and msi.pick_components_flag = 'Y')) and mla.instance = to_char(msi.sr_instance_id) and mla.level_id = 1 and mla.parent_level_id = 3 and mla.sr_level_pk = to_char(msi.sr_inventory_item_id) and decode( nvl(tlp.profile_value, '2'), '1', decode(nvl(msi.ato_forecast_control,3), 3, decode(mla.sr_parent_level_pk,'-777',2,1), 1), decode( nvl(msi.ato_forecast_control, 3), 3, 2, 1) ) = 1 UNION ALL SELECT to_number(NULL) plan_id, mcd.CS_NAME cs_name, mlv_org.LEVEL_PK org_level_value_pk, 7 org_level_id, mlv_prd.LEVEL_PK prd_level_value_pk, 1 prd_level_id, msd_common_utilities.get_loc_key(mcd.ATTRIBUTE_1, '-3', null, 30) geo_level_value_pk, 30 geo_level_id, mlv_dcs.LEVEL_PK dcs_level_value_pk, 34 dcs_level_id, 9 time_level_id, to_number(mcd.ATTRIBUTE_41) quantity, to_date(mcd.ATTRIBUTE_43,'yyyy/mm/dd') actual_date, mcd.CREATION_DATE creation_date, mcd.CREATED_BY created_by, mcd.LAST_UPDATE_DATE last_update_date, mcd.LAST_UPDATED_BY last_updated_by, mcd.LAST_UPDATE_LOGIN last_update_login, mcd.LAST_REFRESH_NUM last_refresh_num, mcd.CREATED_BY_REFRESH_NUM created_by_refresh_num, mcd.ACTION_CODE action_code, to_number(NULL) prd_parent_level_id, to_number(NULL) prd_parent_level_value_pk FROM MSD_CS_DATA mcd, MSD_LEVEL_VALUES mlv_org, MSD_LEVEL_VALUES mlv_prd, MSD_LEVEL_VALUES mlv_dcs WHERE mcd.CS_DEFINITION_ID = (SELECT cs_definition_id FROM msd_cs_definitions WHERE name = 'MSD_WORK_IN_PROCESS') AND mcd.ATTRIBUTE_10 = 7 AND mlv_org.level_id = 7 AND mcd.ATTRIBUTE_11 = mlv_org.SR_LEVEL_PK AND mcd.ATTRIBUTE_1 = mlv_org.INSTANCE AND mcd.ATTRIBUTE_2 = 1 AND mlv_prd.level_id = 1 AND mcd.ATTRIBUTE_3 = mlv_prd.SR_LEVEL_PK AND mcd.ATTRIBUTE_1 = mlv_prd.INSTANCE AND mcd.ATTRIBUTE_50 = 34 AND mlv_dcs.level_id = 34 AND mcd.ATTRIBUTE_51 = mlv_dcs.SR_LEVEL_PK AND mcd.ATTRIBUTE_1 = mlv_dcs.INSTANCE

View Text - HTML Formatted

SELECT SUP.PLAN_ID PLAN_ID
, PLANS.COMPILE_DESIGNATOR CS_NAME
, D_ORG.LEVEL_PK ORG_LEVEL_VALUE_PK
, D_ORG.LEVEL_ID ORG_LEVEL_ID
, D_ITM.LEVEL_PK PRD_LEVEL_VALUE_PK
, D_ITM.LEVEL_ID PRD_LEVEL_ID
, MSD_COMMON_UTILITIES.GET_LOC_KEY(SUP.SR_INSTANCE_ID
, '-3'
, NULL
, 30) GEO_LEVEL_VALUE_PK
, 30 GEO_LEVEL_ID
, NVL(D_DCS.LEVEL_PK
, MSD_COMMON_UTILITIES.GET_DCS_KEY(SUP.SR_INSTANCE_ID
, '-777'
, NULL
, 34)) DCS_LEVEL_VALUE_PK
, 34 DCS_LEVEL_ID
, 9 TIME_LEVEL_ID
, MFP.ALLOCATED_QUANTITY QUANTITY
, TRUNC(SUP.NEW_SCHEDULE_DATE) ACTUAL_DATE
, SUP.CREATION_DATE
, SUP.CREATED_BY
, SUP.LAST_UPDATE_DATE
, SUP.LAST_UPDATED_BY
, SUP.LAST_UPDATE_LOGIN
, 1
, 1
, 'I'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MSC_SUPPLIES SUP
, MSC_PLANS PLANS
, MSC_DEMANDS DEM
, MSC_FULL_PEGGING MFP
, MSC_SYSTEM_ITEMS MSI
, MSD_LEVEL_VALUES D_ITM
, MSD_LEVEL_VALUES D_ORG
, MSD_LEVEL_VALUES D_DCS
, (SELECT NVL(FND_PROFILE.VALUE('MSD_TWO_LEVEL_PLANNING')
, '2') PROFILE_VALUE
FROM DUAL) TLP
, MSD_LEVEL_ASSOCIATIONS MLA
WHERE SUP.PLAN_ID <> -1
AND SUP.PLAN_ID = PLANS.PLAN_ID
AND NVL(SUP.NEW_ORDER_QUANTITY
, 0) <> 0
AND SUP.ORDER_TYPE IN (3
, 7
, 14
, 15
, 27
, 28)
AND SUP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND SUP.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND SUP.PLAN_ID = MSI.PLAN_ID
AND SUP.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND D_ITM.SR_LEVEL_PK = TO_CHAR(MSI.SR_INVENTORY_ITEM_ID)
AND D_ITM.INSTANCE = TO_CHAR(MSI.SR_INSTANCE_ID)
AND D_ITM.LEVEL_ID = 1
AND D_ORG.SR_LEVEL_PK = TO_CHAR(SUP.ORGANIZATION_ID)
AND D_ORG.INSTANCE = TO_CHAR(SUP.SR_INSTANCE_ID)
AND D_ORG.LEVEL_ID = 7
AND SUP.SR_INSTANCE_ID = MFP.SR_INSTANCE_ID
AND SUP.PLAN_ID = MFP.PLAN_ID
AND SUP.TRANSACTION_ID = MFP.TRANSACTION_ID
AND MFP.PEGGING_ID = MFP.END_PEGGING_ID
AND NOT EXISTS ( SELECT 1
FROM MSC_FULL_PEGGING PEG
WHERE PEG.TRANSACTION_ID = SUP.TRANSACTION_ID
AND PEG.INVENTORY_ITEM_ID = SUP.INVENTORY_ITEM_ID
AND PEG.ORGANIZATION_ID = SUP.ORGANIZATION_ID
AND PEG.PLAN_ID = SUP.PLAN_ID
AND PEG.SR_INSTANCE_ID = SUP.SR_INSTANCE_ID
AND PEG.SUPPLY_DATE = SUP.NEW_SCHEDULE_DATE
AND PEG.ALLOCATED_QUANTITY = 0
AND PEG.DEMAND_ID = -2)
AND DEM.SR_INSTANCE_ID(+) = MFP.SR_INSTANCE_ID
AND DEM.PLAN_ID(+) = MFP.PLAN_ID
AND DEM.DEMAND_ID(+) = MFP.DEMAND_ID
AND D_DCS.LEVEL_ID(+) = 34
AND D_DCS.INSTANCE(+) = TO_CHAR(DEM.SR_INSTANCE_ID)
AND D_DCS.SR_LEVEL_PK(+) = DEM.DEMAND_CLASS
AND (MSI.MRP_PLANNING_CODE <> 6 OR (MSI.MRP_PLANNING_CODE = 6
AND MSI.PICK_COMPONENTS_FLAG = 'Y'))
AND MLA.INSTANCE = TO_CHAR(MSI.SR_INSTANCE_ID)
AND MLA.LEVEL_ID = 1
AND MLA.PARENT_LEVEL_ID = 3
AND MLA.SR_LEVEL_PK = TO_CHAR(MSI.SR_INVENTORY_ITEM_ID)
AND DECODE( NVL(TLP.PROFILE_VALUE
, '2')
, '1'
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(MLA.SR_PARENT_LEVEL_PK
, '-777'
, 2
, 1)
, 1)
, DECODE( NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, 2
, 1) ) = 1 UNION ALL SELECT SUP.PLAN_ID PLAN_ID
, PLANS.COMPILE_DESIGNATOR CS_NAME
, D_ORG.LEVEL_PK ORG_LEVEL_VALUE_PK
, D_ORG.LEVEL_ID ORG_LEVEL_ID
, D_ITM.LEVEL_PK PRD_LEVEL_VALUE_PK
, D_ITM.LEVEL_ID PRD_LEVEL_ID
, MSD_COMMON_UTILITIES.GET_LOC_KEY(SUP.SR_INSTANCE_ID
, '-3'
, NULL
, 30) GEO_LEVEL_VALUE_PK
, 30 GEO_LEVEL_ID
, NVL(D_DCS.LEVEL_PK
, MSD_COMMON_UTILITIES.GET_DCS_KEY(SUP.SR_INSTANCE_ID
, '-777'
, NULL
, 34)) DCS_LEVEL_VALUE_PK
, 34 DCS_LEVEL_ID
, 9 TIME_LEVEL_ID
, SUP.NEW_ORDER_QUANTITY QUANTITY
, TRUNC(SUP.NEW_SCHEDULE_DATE) SHIP_DATE
, SUP.CREATION_DATE
, SUP.CREATED_BY
, SUP.LAST_UPDATE_DATE
, SUP.LAST_UPDATED_BY
, SUP.LAST_UPDATE_LOGIN
, 1
, 1
, 'I'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MSC_SUPPLIES SUP
, MSC_PLANS PLANS
, MSC_DEMANDS DEM
, MSC_FULL_PEGGING MFP
, MSC_SYSTEM_ITEMS MSI
, MSD_LEVEL_VALUES D_ITM
, MSD_LEVEL_VALUES D_ORG
, MSD_LEVEL_VALUES D_DCS
, (SELECT NVL(FND_PROFILE.VALUE('MSD_TWO_LEVEL_PLANNING')
, '2') PROFILE_VALUE
FROM DUAL) TLP
, MSD_LEVEL_ASSOCIATIONS MLA
WHERE SUP.PLAN_ID <> -1
AND SUP.PLAN_ID = PLANS.PLAN_ID
AND NVL(SUP.NEW_ORDER_QUANTITY
, 0) <> 0
AND SUP.ORDER_TYPE IN (3
, 7
, 14
, 15
, 27
, 28)
AND SUP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND SUP.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND SUP.PLAN_ID = MSI.PLAN_ID
AND SUP.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND D_ITM.SR_LEVEL_PK = TO_CHAR(MSI.SR_INVENTORY_ITEM_ID)
AND D_ITM.INSTANCE = TO_CHAR(MSI.SR_INSTANCE_ID)
AND D_ITM.LEVEL_ID = 1
AND D_ORG.SR_LEVEL_PK = TO_CHAR(SUP.ORGANIZATION_ID)
AND D_ORG.INSTANCE = TO_CHAR(SUP.SR_INSTANCE_ID)
AND D_ORG.LEVEL_ID = 7
AND SUP.TRANSACTION_ID = MFP.TRANSACTION_ID
AND SUP.INVENTORY_ITEM_ID = MFP.INVENTORY_ITEM_ID
AND SUP.ORGANIZATION_ID = MFP.ORGANIZATION_ID
AND SUP.SR_INSTANCE_ID = MFP.SR_INSTANCE_ID
AND SUP.PLAN_ID = MFP.PLAN_ID
AND MFP.PEGGING_ID = MFP.END_PEGGING_ID
AND MFP.SUPPLY_DATE = SUP.NEW_SCHEDULE_DATE
AND MFP.ALLOCATED_QUANTITY = 0
AND MFP.DEMAND_ID = -2
AND MFP.ROWID = (SELECT MIN(MPG.ROWID)
FROM MSC_FULL_PEGGING MPG
WHERE MPG.TRANSACTION_ID = SUP.TRANSACTION_ID
AND MPG.INVENTORY_ITEM_ID = SUP.INVENTORY_ITEM_ID
AND MPG.ORGANIZATION_ID = SUP.ORGANIZATION_ID
AND MPG.PLAN_ID = SUP.PLAN_ID
AND MPG.SR_INSTANCE_ID = SUP.SR_INSTANCE_ID
AND MPG.SUPPLY_DATE = SUP.NEW_SCHEDULE_DATE
AND MPG.ALLOCATED_QUANTITY = 0
AND MPG.DEMAND_ID = -2)
AND DEM.SR_INSTANCE_ID(+) = MFP.SR_INSTANCE_ID
AND DEM.PLAN_ID(+) = MFP.PLAN_ID
AND DEM.DEMAND_ID(+) = MFP.DEMAND_ID
AND D_DCS.LEVEL_ID(+) = 34
AND D_DCS.INSTANCE(+) = TO_CHAR(DEM.SR_INSTANCE_ID)
AND D_DCS.SR_LEVEL_PK(+) = DEM.DEMAND_CLASS
AND (MSI.MRP_PLANNING_CODE <> 6 OR (MSI.MRP_PLANNING_CODE = 6
AND MSI.PICK_COMPONENTS_FLAG = 'Y'))
AND MLA.INSTANCE = TO_CHAR(MSI.SR_INSTANCE_ID)
AND MLA.LEVEL_ID = 1
AND MLA.PARENT_LEVEL_ID = 3
AND MLA.SR_LEVEL_PK = TO_CHAR(MSI.SR_INVENTORY_ITEM_ID)
AND DECODE( NVL(TLP.PROFILE_VALUE
, '2')
, '1'
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(MLA.SR_PARENT_LEVEL_PK
, '-777'
, 2
, 1)
, 1)
, DECODE( NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, 2
, 1) ) = 1 UNION ALL SELECT TO_NUMBER(NULL) PLAN_ID
, MCD.CS_NAME CS_NAME
, MLV_ORG.LEVEL_PK ORG_LEVEL_VALUE_PK
, 7 ORG_LEVEL_ID
, MLV_PRD.LEVEL_PK PRD_LEVEL_VALUE_PK
, 1 PRD_LEVEL_ID
, MSD_COMMON_UTILITIES.GET_LOC_KEY(MCD.ATTRIBUTE_1
, '-3'
, NULL
, 30) GEO_LEVEL_VALUE_PK
, 30 GEO_LEVEL_ID
, MLV_DCS.LEVEL_PK DCS_LEVEL_VALUE_PK
, 34 DCS_LEVEL_ID
, 9 TIME_LEVEL_ID
, TO_NUMBER(MCD.ATTRIBUTE_41) QUANTITY
, TO_DATE(MCD.ATTRIBUTE_43
, 'YYYY/MM/DD') ACTUAL_DATE
, MCD.CREATION_DATE CREATION_DATE
, MCD.CREATED_BY CREATED_BY
, MCD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, MCD.LAST_UPDATED_BY LAST_UPDATED_BY
, MCD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, MCD.LAST_REFRESH_NUM LAST_REFRESH_NUM
, MCD.CREATED_BY_REFRESH_NUM CREATED_BY_REFRESH_NUM
, MCD.ACTION_CODE ACTION_CODE
, TO_NUMBER(NULL) PRD_PARENT_LEVEL_ID
, TO_NUMBER(NULL) PRD_PARENT_LEVEL_VALUE_PK
FROM MSD_CS_DATA MCD
, MSD_LEVEL_VALUES MLV_ORG
, MSD_LEVEL_VALUES MLV_PRD
, MSD_LEVEL_VALUES MLV_DCS
WHERE MCD.CS_DEFINITION_ID = (SELECT CS_DEFINITION_ID
FROM MSD_CS_DEFINITIONS
WHERE NAME = 'MSD_WORK_IN_PROCESS')
AND MCD.ATTRIBUTE_10 = 7
AND MLV_ORG.LEVEL_ID = 7
AND MCD.ATTRIBUTE_11 = MLV_ORG.SR_LEVEL_PK
AND MCD.ATTRIBUTE_1 = MLV_ORG.INSTANCE
AND MCD.ATTRIBUTE_2 = 1
AND MLV_PRD.LEVEL_ID = 1
AND MCD.ATTRIBUTE_3 = MLV_PRD.SR_LEVEL_PK
AND MCD.ATTRIBUTE_1 = MLV_PRD.INSTANCE
AND MCD.ATTRIBUTE_50 = 34
AND MLV_DCS.LEVEL_ID = 34
AND MCD.ATTRIBUTE_51 = MLV_DCS.SR_LEVEL_PK
AND MCD.ATTRIBUTE_1 = MLV_DCS.INSTANCE