VIEW: APPS.MSD_AVAIL_RESOURCE_CAP_DS_V

Source

View Text - Preformatted

SELECT d_org.demand_plan_id, res.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(res.sr_instance_id,'-3',null,30) geo_level_value_pk, 30 geo_level_id, msd_common_utilities.get_dcs_key(res.sr_instance_id,'-6',null,40) dcs_level_value_pk, 40 dcs_level_id, 9 time_level_id, (((res.to_time - res.from_time)/3600) * res.capacity_units) quantity, res.shift_date schedule_date, res.creation_date, res.created_by, res.last_update_date, res.last_updated_by, res.last_update_login, 1, 1, 'I', to_number(null), to_number(null) FROM msd_dp_session mds, MSC_NET_RESOURCE_AVAIL res, msc_department_resources dep, msc_plans plans, msd_level_values_ds d_itm, msd_level_values_ds d_org WHERE res.plan_id = plans.plan_id and res.plan_id = dep.plan_id and res.sr_instance_id = dep.sr_instance_id and res.organization_id = dep.organization_id and res.resource_id = dep.resource_id and res.department_id = dep.department_id and dep.bottleneck_flag = 1 and d_itm.sr_level_pk = to_char('R'||'.'||dep.resource_code) and d_itm.instance = to_char(dep.sr_instance_id) and d_itm.level_id = 1 and d_itm.demand_plan_id = mds.demand_plan_id and d_itm.SYSTEM_ATTRIBUTE1 =msd_common_utilities.get_system_attribute1_desc('R') and d_org.sr_level_pk = to_char(res.organization_id) and d_org.instance = to_char(res.sr_instance_id) and d_org.level_id = 7 and d_org.demand_plan_id = mds.demand_plan_id and res.plan_id <> -1 and res.resource_id <> -1 and dep.line_flag <> 1 UNION ALL SELECT d_org.demand_plan_id, res.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(res.sr_instance_id,'-3',null,30) geo_level_value_pk, 30 geo_level_id, msd_common_utilities.get_dcs_key(res.sr_instance_id,'-6',null,40) dcs_level_value_pk, 40 dcs_level_id, 9 time_level_id, (((res.to_time - res.from_time)/3600) * dep.max_rate) quantity, res.shift_date end_date, res.creation_date, res.created_by, res.last_update_date, res.last_updated_by, res.last_update_login, 1, 1, 'I', to_number(null), to_number(null) FROM msd_dp_session mds, MSC_NET_RESOURCE_AVAIL res, msc_department_resources dep, msc_plans plans, msd_level_values_ds d_itm, msd_level_values_ds d_org WHERE res.plan_id = plans.plan_id and res.plan_id = dep.plan_id and res.sr_instance_id = dep.sr_instance_id and res.organization_id = dep.organization_id and res.resource_id = dep.resource_id and res.department_id = dep.department_id and dep.bottleneck_flag = 1 and d_itm.sr_level_pk = to_char('L'||'.'||dep.department_code) and d_itm.instance = to_char(dep.sr_instance_id) and d_itm.level_id = 1 and d_itm.demand_plan_id = mds.demand_plan_id and d_itm.SYSTEM_ATTRIBUTE1 =msd_common_utilities.get_system_attribute1_desc('L') and d_org.sr_level_pk = to_char(res.organization_id) and d_org.instance = to_char(res.sr_instance_id) and d_org.level_id = 7 and d_org.demand_plan_id = mds.demand_plan_id and res.plan_id <> -1 and res.resource_id = -1 and dep.line_flag = 1 UNION ALL SELECT d_org.demand_plan_id, to_number(null) plan_id, cs_name cs_name, d_org.level_pk org_level_value_pk, 7 org_level_id, d_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, msd_common_utilities.get_dcs_key(mcd.attribute_1,'-6',null,40) dcs_level_value_pk, 40 dcs_level_id, 9 time_level_id, to_number(mcd.attribute_41) quantity, to_date(mcd.attribute_43, 'yyyy/mm/dd') schedule_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_dp_session mds, msd_cs_data mcd, msd_level_values_ds d_org, msd_level_values_ds d_prd WHERE mcd.cs_definition_id = (select cs_definition_id from msd_cs_definitions where name = 'MSD_AVAIL_RESOURCE_CAPACITY') and mcd.attribute_11 = d_org.sr_level_pk and mcd.attribute_10 = 7 and d_org.level_id = 7 and d_org.demand_plan_id = mds.demand_plan_id and mcd.attribute_1 = d_org.instance and mcd.attribute_3 = d_prd.sr_level_pk and mcd.attribute_2 = 1 and d_prd.level_id = 1 and d_prd.demand_plan_id = mds.demand_plan_id and mcd.attribute_1 = d_prd.instance

View Text - HTML Formatted

SELECT D_ORG.DEMAND_PLAN_ID
, RES.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(RES.SR_INSTANCE_ID
, '-3'
, NULL
, 30) GEO_LEVEL_VALUE_PK
, 30 GEO_LEVEL_ID
, MSD_COMMON_UTILITIES.GET_DCS_KEY(RES.SR_INSTANCE_ID
, '-6'
, NULL
, 40) DCS_LEVEL_VALUE_PK
, 40 DCS_LEVEL_ID
, 9 TIME_LEVEL_ID
, (((RES.TO_TIME - RES.FROM_TIME)/3600) * RES.CAPACITY_UNITS) QUANTITY
, RES.SHIFT_DATE SCHEDULE_DATE
, RES.CREATION_DATE
, RES.CREATED_BY
, RES.LAST_UPDATE_DATE
, RES.LAST_UPDATED_BY
, RES.LAST_UPDATE_LOGIN
, 1
, 1
, 'I'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MSD_DP_SESSION MDS
, MSC_NET_RESOURCE_AVAIL RES
, MSC_DEPARTMENT_RESOURCES DEP
, MSC_PLANS PLANS
, MSD_LEVEL_VALUES_DS D_ITM
, MSD_LEVEL_VALUES_DS D_ORG
WHERE RES.PLAN_ID = PLANS.PLAN_ID
AND RES.PLAN_ID = DEP.PLAN_ID
AND RES.SR_INSTANCE_ID = DEP.SR_INSTANCE_ID
AND RES.ORGANIZATION_ID = DEP.ORGANIZATION_ID
AND RES.RESOURCE_ID = DEP.RESOURCE_ID
AND RES.DEPARTMENT_ID = DEP.DEPARTMENT_ID
AND DEP.BOTTLENECK_FLAG = 1
AND D_ITM.SR_LEVEL_PK = TO_CHAR('R'||'.'||DEP.RESOURCE_CODE)
AND D_ITM.INSTANCE = TO_CHAR(DEP.SR_INSTANCE_ID)
AND D_ITM.LEVEL_ID = 1
AND D_ITM.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND D_ITM.SYSTEM_ATTRIBUTE1 =MSD_COMMON_UTILITIES.GET_SYSTEM_ATTRIBUTE1_DESC('R')
AND D_ORG.SR_LEVEL_PK = TO_CHAR(RES.ORGANIZATION_ID)
AND D_ORG.INSTANCE = TO_CHAR(RES.SR_INSTANCE_ID)
AND D_ORG.LEVEL_ID = 7
AND D_ORG.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND RES.PLAN_ID <> -1
AND RES.RESOURCE_ID <> -1
AND DEP.LINE_FLAG <> 1 UNION ALL SELECT D_ORG.DEMAND_PLAN_ID
, RES.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(RES.SR_INSTANCE_ID
, '-3'
, NULL
, 30) GEO_LEVEL_VALUE_PK
, 30 GEO_LEVEL_ID
, MSD_COMMON_UTILITIES.GET_DCS_KEY(RES.SR_INSTANCE_ID
, '-6'
, NULL
, 40) DCS_LEVEL_VALUE_PK
, 40 DCS_LEVEL_ID
, 9 TIME_LEVEL_ID
, (((RES.TO_TIME - RES.FROM_TIME)/3600) * DEP.MAX_RATE) QUANTITY
, RES.SHIFT_DATE END_DATE
, RES.CREATION_DATE
, RES.CREATED_BY
, RES.LAST_UPDATE_DATE
, RES.LAST_UPDATED_BY
, RES.LAST_UPDATE_LOGIN
, 1
, 1
, 'I'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MSD_DP_SESSION MDS
, MSC_NET_RESOURCE_AVAIL RES
, MSC_DEPARTMENT_RESOURCES DEP
, MSC_PLANS PLANS
, MSD_LEVEL_VALUES_DS D_ITM
, MSD_LEVEL_VALUES_DS D_ORG
WHERE RES.PLAN_ID = PLANS.PLAN_ID
AND RES.PLAN_ID = DEP.PLAN_ID
AND RES.SR_INSTANCE_ID = DEP.SR_INSTANCE_ID
AND RES.ORGANIZATION_ID = DEP.ORGANIZATION_ID
AND RES.RESOURCE_ID = DEP.RESOURCE_ID
AND RES.DEPARTMENT_ID = DEP.DEPARTMENT_ID
AND DEP.BOTTLENECK_FLAG = 1
AND D_ITM.SR_LEVEL_PK = TO_CHAR('L'||'.'||DEP.DEPARTMENT_CODE)
AND D_ITM.INSTANCE = TO_CHAR(DEP.SR_INSTANCE_ID)
AND D_ITM.LEVEL_ID = 1
AND D_ITM.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND D_ITM.SYSTEM_ATTRIBUTE1 =MSD_COMMON_UTILITIES.GET_SYSTEM_ATTRIBUTE1_DESC('L')
AND D_ORG.SR_LEVEL_PK = TO_CHAR(RES.ORGANIZATION_ID)
AND D_ORG.INSTANCE = TO_CHAR(RES.SR_INSTANCE_ID)
AND D_ORG.LEVEL_ID = 7
AND D_ORG.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND RES.PLAN_ID <> -1
AND RES.RESOURCE_ID = -1
AND DEP.LINE_FLAG = 1 UNION ALL SELECT D_ORG.DEMAND_PLAN_ID
, TO_NUMBER(NULL) PLAN_ID
, CS_NAME CS_NAME
, D_ORG.LEVEL_PK ORG_LEVEL_VALUE_PK
, 7 ORG_LEVEL_ID
, D_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
, MSD_COMMON_UTILITIES.GET_DCS_KEY(MCD.ATTRIBUTE_1
, '-6'
, NULL
, 40) DCS_LEVEL_VALUE_PK
, 40 DCS_LEVEL_ID
, 9 TIME_LEVEL_ID
, TO_NUMBER(MCD.ATTRIBUTE_41) QUANTITY
, TO_DATE(MCD.ATTRIBUTE_43
, 'YYYY/MM/DD') SCHEDULE_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_DP_SESSION MDS
, MSD_CS_DATA MCD
, MSD_LEVEL_VALUES_DS D_ORG
, MSD_LEVEL_VALUES_DS D_PRD
WHERE MCD.CS_DEFINITION_ID = (SELECT CS_DEFINITION_ID
FROM MSD_CS_DEFINITIONS
WHERE NAME = 'MSD_AVAIL_RESOURCE_CAPACITY')
AND MCD.ATTRIBUTE_11 = D_ORG.SR_LEVEL_PK
AND MCD.ATTRIBUTE_10 = 7
AND D_ORG.LEVEL_ID = 7
AND D_ORG.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND MCD.ATTRIBUTE_1 = D_ORG.INSTANCE
AND MCD.ATTRIBUTE_3 = D_PRD.SR_LEVEL_PK
AND MCD.ATTRIBUTE_2 = 1
AND D_PRD.LEVEL_ID = 1
AND D_PRD.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND MCD.ATTRIBUTE_1 = D_PRD.INSTANCE