VIEW: APPS.MSD_SIM_END_ITEM_DEMAND_DS_V

Source

View Text - Preformatted

SELECT mds.demand_plan_id, md.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, d_geo.level_pk geo_level_value_pk, d_geo.level_id geo_level_id, 9 time_level_id, md.using_requirement_quantity *(nvl(ms.new_order_quantity, 0) * mabc.capacity_usage_ratio / iv.usg_qty) quantity, TRUNC(md.using_assembly_demand_date), md.creation_date, md.created_by, md.last_update_date, md.last_updated_by, md.last_update_login, 1, 1, 'I', to_number(NULL), to_number(NULL) FROM msd_dp_session mds, msd_dp_parameters mdp, msc_plans plans, msc_demands md, msc_system_items msi, msd_level_values_ds d_itm, msd_level_values_ds d_org, msd_level_values_ds d_geo, msd_ascp_bom_comp mabc, msc_system_items msi1, msc_supplies ms, msc_trading_partners mtp, (SELECT mabc.sr_assembly_pk, mabc.cap_usg_ratio_obj, SUM(nvl(ms.new_order_quantity, 0) * mabc.capacity_usage_ratio) usg_qty FROM msc_plans mp, msd_ascp_bom_comp mabc, msc_system_items msi, msc_supplies ms, msd_dp_parameters mdp, msd_dp_session mds WHERE mds.demand_plan_id = mdp.demand_plan_id AND mdp.parameter_type = 'MSD_SIM_END_ITEM_DEMAND' AND mp.compile_designator = mdp.parameter_name AND mdp.capacity_usage_ratio = mabc.cap_usg_ratio_obj AND mabc.bom_type = 'WUR' AND mabc.plan_type = 'EOL' AND mp.plan_id = msi.plan_id AND mp.plan_id = ms.plan_id AND mabc.sr_component_pk = msi.sr_inventory_item_id AND msi.sr_instance_id = ms.sr_instance_id AND msi.organization_id = ms.organization_id AND msi.inventory_item_id = ms.inventory_item_id AND ms.supplier_id IS NOT NULL AND ms.order_type IN(5, 17) GROUP BY mabc.sr_assembly_pk, mabc.cap_usg_ratio_obj) iv WHERE mds.demand_plan_id = mdp.demand_plan_id AND mdp.parameter_type = 'MSD_SIM_END_ITEM_DEMAND' AND mdp.capacity_usage_ratio = mabc.cap_usg_ratio_obj AND plans.plan_id <> -1 AND md.plan_id = plans.plan_id AND md.plan_id = msi.plan_id AND md.sr_instance_id = msi.sr_instance_id AND md.organization_id = msi.organization_id AND md.inventory_item_id = msi.inventory_item_id AND md.origination_type IN(29, 30, 9, 11, 24, 6, 7, 8) AND nvl(md.using_requirement_quantity, 0) <> 0 AND plans.plan_id = ms.plan_id AND msi1.plan_id = plans.plan_id AND msi1.sr_instance_id = ms.sr_instance_id AND msi1.organization_id = ms.organization_id AND msi1.inventory_item_id = ms.inventory_item_id AND ms.supplier_id IS NOT NULL AND ms.order_type IN(5, 17) AND mtp.sr_instance_id = ms.sr_instance_id AND mtp.partner_id = ms.supplier_id AND mtp.partner_type = 1 AND mabc.sr_assembly_pk = msi.sr_inventory_item_id AND mabc.sr_component_pk = msi1.sr_inventory_item_id AND mabc.bom_type = 'WUR' AND mabc.plan_type = 'EOL' AND iv.sr_assembly_pk = mabc.sr_assembly_pk AND iv.cap_usg_ratio_obj = mabc.cap_usg_ratio_obj AND d_org.level_id = 7 AND d_org.instance = to_char(md.sr_instance_id) AND d_org.sr_level_pk = to_char(md.organization_id) AND d_org.demand_plan_id = mds.demand_plan_id AND d_itm.level_id = 1 AND d_itm.instance = to_char(msi.sr_instance_id) AND d_itm.sr_level_pk = to_char(msi.sr_inventory_item_id) AND d_itm.demand_plan_id = mds.demand_plan_id AND d_geo.level_id = 50 AND d_geo.instance = to_char(ms.sr_instance_id) AND d_geo.sr_level_pk = to_char('1.' || mtp.sr_tp_id) AND d_geo.demand_plan_id = mds.demand_plan_id UNION ALL SELECT mds.demand_plan_id, 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, mlv_geo.level_pk geo_level_value_pk, 50 geo_level_id, 9 time_level_id, to_number(mcd.attribute_41) quantity, to_date(mcd.attribute_43, 'YYYY/MM/DD'), 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_ds mlv_org, msd_level_values_ds mlv_prd, msd_level_values_ds mlv_geo, msd_dp_session mds WHERE mcd.cs_definition_id = (SELECT cs_definition_id FROM msd_cs_definitions WHERE name = 'MSD_SIM_END_ITEM_DEMAND') 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 mlv_org.demand_plan_id = mds.demand_plan_id 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 mlv_prd.demand_plan_id = mds.demand_plan_id AND mcd.attribute_6 = 50 AND mlv_geo.level_id = 50 AND mcd.attribute_7 = mlv_geo.sr_level_pk AND mcd.attribute_1 = mlv_geo.instance AND mlv_geo.demand_plan_id = mds.demand_plan_id

View Text - HTML Formatted

SELECT MDS.DEMAND_PLAN_ID
, MD.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
, D_GEO.LEVEL_PK GEO_LEVEL_VALUE_PK
, D_GEO.LEVEL_ID GEO_LEVEL_ID
, 9 TIME_LEVEL_ID
, MD.USING_REQUIREMENT_QUANTITY *(NVL(MS.NEW_ORDER_QUANTITY
, 0) * MABC.CAPACITY_USAGE_RATIO / IV.USG_QTY) QUANTITY
, TRUNC(MD.USING_ASSEMBLY_DEMAND_DATE)
, MD.CREATION_DATE
, MD.CREATED_BY
, MD.LAST_UPDATE_DATE
, MD.LAST_UPDATED_BY
, MD.LAST_UPDATE_LOGIN
, 1
, 1
, 'I'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MSD_DP_SESSION MDS
, MSD_DP_PARAMETERS MDP
, MSC_PLANS PLANS
, MSC_DEMANDS MD
, MSC_SYSTEM_ITEMS MSI
, MSD_LEVEL_VALUES_DS D_ITM
, MSD_LEVEL_VALUES_DS D_ORG
, MSD_LEVEL_VALUES_DS D_GEO
, MSD_ASCP_BOM_COMP MABC
, MSC_SYSTEM_ITEMS MSI1
, MSC_SUPPLIES MS
, MSC_TRADING_PARTNERS MTP
, (SELECT MABC.SR_ASSEMBLY_PK
, MABC.CAP_USG_RATIO_OBJ
, SUM(NVL(MS.NEW_ORDER_QUANTITY
, 0) * MABC.CAPACITY_USAGE_RATIO) USG_QTY
FROM MSC_PLANS MP
, MSD_ASCP_BOM_COMP MABC
, MSC_SYSTEM_ITEMS MSI
, MSC_SUPPLIES MS
, MSD_DP_PARAMETERS MDP
, MSD_DP_SESSION MDS
WHERE MDS.DEMAND_PLAN_ID = MDP.DEMAND_PLAN_ID
AND MDP.PARAMETER_TYPE = 'MSD_SIM_END_ITEM_DEMAND'
AND MP.COMPILE_DESIGNATOR = MDP.PARAMETER_NAME
AND MDP.CAPACITY_USAGE_RATIO = MABC.CAP_USG_RATIO_OBJ
AND MABC.BOM_TYPE = 'WUR'
AND MABC.PLAN_TYPE = 'EOL'
AND MP.PLAN_ID = MSI.PLAN_ID
AND MP.PLAN_ID = MS.PLAN_ID
AND MABC.SR_COMPONENT_PK = MSI.SR_INVENTORY_ITEM_ID
AND MSI.SR_INSTANCE_ID = MS.SR_INSTANCE_ID
AND MSI.ORGANIZATION_ID = MS.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MS.INVENTORY_ITEM_ID
AND MS.SUPPLIER_ID IS NOT NULL
AND MS.ORDER_TYPE IN(5
, 17) GROUP BY MABC.SR_ASSEMBLY_PK
, MABC.CAP_USG_RATIO_OBJ) IV
WHERE MDS.DEMAND_PLAN_ID = MDP.DEMAND_PLAN_ID
AND MDP.PARAMETER_TYPE = 'MSD_SIM_END_ITEM_DEMAND'
AND MDP.CAPACITY_USAGE_RATIO = MABC.CAP_USG_RATIO_OBJ
AND PLANS.PLAN_ID <> -1
AND MD.PLAN_ID = PLANS.PLAN_ID
AND MD.PLAN_ID = MSI.PLAN_ID
AND MD.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND MD.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MD.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MD.ORIGINATION_TYPE IN(29
, 30
, 9
, 11
, 24
, 6
, 7
, 8)
AND NVL(MD.USING_REQUIREMENT_QUANTITY
, 0) <> 0
AND PLANS.PLAN_ID = MS.PLAN_ID
AND MSI1.PLAN_ID = PLANS.PLAN_ID
AND MSI1.SR_INSTANCE_ID = MS.SR_INSTANCE_ID
AND MSI1.ORGANIZATION_ID = MS.ORGANIZATION_ID
AND MSI1.INVENTORY_ITEM_ID = MS.INVENTORY_ITEM_ID
AND MS.SUPPLIER_ID IS NOT NULL
AND MS.ORDER_TYPE IN(5
, 17)
AND MTP.SR_INSTANCE_ID = MS.SR_INSTANCE_ID
AND MTP.PARTNER_ID = MS.SUPPLIER_ID
AND MTP.PARTNER_TYPE = 1
AND MABC.SR_ASSEMBLY_PK = MSI.SR_INVENTORY_ITEM_ID
AND MABC.SR_COMPONENT_PK = MSI1.SR_INVENTORY_ITEM_ID
AND MABC.BOM_TYPE = 'WUR'
AND MABC.PLAN_TYPE = 'EOL'
AND IV.SR_ASSEMBLY_PK = MABC.SR_ASSEMBLY_PK
AND IV.CAP_USG_RATIO_OBJ = MABC.CAP_USG_RATIO_OBJ
AND D_ORG.LEVEL_ID = 7
AND D_ORG.INSTANCE = TO_CHAR(MD.SR_INSTANCE_ID)
AND D_ORG.SR_LEVEL_PK = TO_CHAR(MD.ORGANIZATION_ID)
AND D_ORG.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND D_ITM.LEVEL_ID = 1
AND D_ITM.INSTANCE = TO_CHAR(MSI.SR_INSTANCE_ID)
AND D_ITM.SR_LEVEL_PK = TO_CHAR(MSI.SR_INVENTORY_ITEM_ID)
AND D_ITM.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND D_GEO.LEVEL_ID = 50
AND D_GEO.INSTANCE = TO_CHAR(MS.SR_INSTANCE_ID)
AND D_GEO.SR_LEVEL_PK = TO_CHAR('1.' || MTP.SR_TP_ID)
AND D_GEO.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID UNION ALL SELECT MDS.DEMAND_PLAN_ID
, 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
, MLV_GEO.LEVEL_PK GEO_LEVEL_VALUE_PK
, 50 GEO_LEVEL_ID
, 9 TIME_LEVEL_ID
, TO_NUMBER(MCD.ATTRIBUTE_41) QUANTITY
, TO_DATE(MCD.ATTRIBUTE_43
, 'YYYY/MM/DD')
, 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_DS MLV_ORG
, MSD_LEVEL_VALUES_DS MLV_PRD
, MSD_LEVEL_VALUES_DS MLV_GEO
, MSD_DP_SESSION MDS
WHERE MCD.CS_DEFINITION_ID = (SELECT CS_DEFINITION_ID
FROM MSD_CS_DEFINITIONS
WHERE NAME = 'MSD_SIM_END_ITEM_DEMAND')
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 MLV_ORG.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
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 MLV_PRD.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND MCD.ATTRIBUTE_6 = 50
AND MLV_GEO.LEVEL_ID = 50
AND MCD.ATTRIBUTE_7 = MLV_GEO.SR_LEVEL_PK
AND MCD.ATTRIBUTE_1 = MLV_GEO.INSTANCE
AND MLV_GEO.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID