VIEW: APPS.MSD_DEM_AVAIL_RESOURCE_CAP_V

Source

View Text - Preformatted

SELECT plans.plan_id plan_id, plans.compile_designator supply_plan, plans.compile_designator || ':' || dep.resource_code supply_plan_resource, dep.resource_code resource_code, mtp_org.organization_code org_code, (((res.to_time -res.from_time) / 3600) * res.capacity_units) quantity, res.shift_date sdate FROM msc_net_resource_avail res, msc_department_resources dep, msc_plans plans, msc_trading_partners mtp_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 mtp_org.sr_instance_id = res.sr_instance_id AND mtp_org.sr_tp_id = res.organization_id AND mtp_org.partner_type = 3 AND res.plan_id <> -1 AND res.resource_id <> -1 AND dep.line_flag <> 1 AND dep.department_id = dep.owning_department_id AND TRUNC(res.shift_date) >= TRUNC(plans.curr_start_date) UNION ALL SELECT plans.plan_id plan_id, plans.compile_designator supply_plan, plans.compile_designator || ':' || dep.department_code supply_plan_resource, dep.department_code resource_code, mtp_org.organization_code org_code, (((res.to_time -res.from_time) / 3600) * dep.max_rate) quantity, res.shift_date sdate FROM msc_net_resource_avail res, msc_department_resources dep, msc_plans plans, msc_trading_partners mtp_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 mtp_org.sr_instance_id = res.sr_instance_id AND mtp_org.sr_tp_id = res.organization_id AND mtp_org.partner_type = 3 AND res.plan_id <> -1 AND res.resource_id = -1 AND dep.line_flag = 1 AND TRUNC(res.shift_date) >= TRUNC(plans.curr_start_date)

View Text - HTML Formatted

SELECT PLANS.PLAN_ID PLAN_ID
, PLANS.COMPILE_DESIGNATOR SUPPLY_PLAN
, PLANS.COMPILE_DESIGNATOR || ':' || DEP.RESOURCE_CODE SUPPLY_PLAN_RESOURCE
, DEP.RESOURCE_CODE RESOURCE_CODE
, MTP_ORG.ORGANIZATION_CODE ORG_CODE
, (((RES.TO_TIME -RES.FROM_TIME) / 3600) * RES.CAPACITY_UNITS) QUANTITY
, RES.SHIFT_DATE SDATE
FROM MSC_NET_RESOURCE_AVAIL RES
, MSC_DEPARTMENT_RESOURCES DEP
, MSC_PLANS PLANS
, MSC_TRADING_PARTNERS MTP_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 MTP_ORG.SR_INSTANCE_ID = RES.SR_INSTANCE_ID
AND MTP_ORG.SR_TP_ID = RES.ORGANIZATION_ID
AND MTP_ORG.PARTNER_TYPE = 3
AND RES.PLAN_ID <> -1
AND RES.RESOURCE_ID <> -1
AND DEP.LINE_FLAG <> 1
AND DEP.DEPARTMENT_ID = DEP.OWNING_DEPARTMENT_ID
AND TRUNC(RES.SHIFT_DATE) >= TRUNC(PLANS.CURR_START_DATE) UNION ALL SELECT PLANS.PLAN_ID PLAN_ID
, PLANS.COMPILE_DESIGNATOR SUPPLY_PLAN
, PLANS.COMPILE_DESIGNATOR || ':' || DEP.DEPARTMENT_CODE SUPPLY_PLAN_RESOURCE
, DEP.DEPARTMENT_CODE RESOURCE_CODE
, MTP_ORG.ORGANIZATION_CODE ORG_CODE
, (((RES.TO_TIME -RES.FROM_TIME) / 3600) * DEP.MAX_RATE) QUANTITY
, RES.SHIFT_DATE SDATE
FROM MSC_NET_RESOURCE_AVAIL RES
, MSC_DEPARTMENT_RESOURCES DEP
, MSC_PLANS PLANS
, MSC_TRADING_PARTNERS MTP_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 MTP_ORG.SR_INSTANCE_ID = RES.SR_INSTANCE_ID
AND MTP_ORG.SR_TP_ID = RES.ORGANIZATION_ID
AND MTP_ORG.PARTNER_TYPE = 3
AND RES.PLAN_ID <> -1
AND RES.RESOURCE_ID = -1
AND DEP.LINE_FLAG = 1
AND TRUNC(RES.SHIFT_DATE) >= TRUNC(PLANS.CURR_START_DATE)