VIEW: APPS.MSD_DEM_REQR_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, mtp_org.organization_code org_code, mrr.resource_hours quantity, mrr.start_date sdate FROM msc_resource_requirements mrr, msc_department_resources dep, msc_plans plans, msc_trading_partners mtp_org WHERE mrr.plan_id = plans.plan_id AND mrr.plan_id = dep.plan_id AND mrr.sr_instance_id = dep.sr_instance_id AND mrr.organization_id = dep.organization_id AND mrr.resource_id = dep.resource_id AND mrr.department_id = dep.department_id AND mrr.parent_id = 1 /* detail records */ AND dep.bottleneck_flag = 1 AND mtp_org.sr_instance_id = dep.sr_instance_id AND mtp_org.sr_tp_id = dep.organization_id AND mtp_org.partner_type = 3 AND mrr.plan_id <> -1 AND mrr.resource_id <> -1 AND DEP.LINE_FLAG <> 1 AND ( PLANS.DAILY_MATERIAL_CONSTRAINTS = 1 OR plans.daily_resource_constraints = 1 OR plans.weekly_material_constraints = 1 OR plans.weekly_resource_constraints = 1 OR plans.period_material_constraints = 1 OR plans.period_resource_constraints = 1 ) UNION ALL SELECT plans.plan_id, plans.compile_designator supply_plan, plans.compile_designator || ':' || dep.department_code supply_plan_resource, mtp_org.organization_code org_code, mrr.resource_hours quantity, mrr.start_date sdate FROM msc_resource_requirements mrr, msc_department_resources dep, msc_plans plans, msc_trading_partners mtp_org WHERE mrr.plan_id = plans.plan_id AND mrr.plan_id = dep.plan_id AND mrr.sr_instance_id = dep.sr_instance_id AND mrr.organization_id = dep.organization_id AND mrr.resource_id = dep.resource_id AND mrr.department_id = dep.department_id AND mrr.parent_id = 1 /* detail records */ AND dep.bottleneck_flag = 1 AND mtp_org.sr_instance_id = dep.sr_instance_id AND mtp_org.sr_tp_id = dep.organization_id AND mtp_org.partner_type = 3 AND mrr.plan_id <> -1 AND mrr.resource_id = -1 AND dep.line_flag = 1 AND ( plans.daily_material_constraints = 1 OR plans.daily_resource_constraints = 1 OR plans.weekly_material_constraints = 1 OR plans.weekly_resource_constraints = 1 OR plans.period_material_constraints = 1 OR plans.period_resource_constraints = 1 ) UNION ALL SELECT plans.plan_id plan_id, plans.compile_designator supply_plan , plans.compile_designator || ':' || dep.resource_code supply_plan_resource , mrr.organization_code org_code, mrr.resource_hours quantity, mrr.resource_date sdate FROM msc_resource_requirements_v mrr, msc_department_resources dep, msc_plans plans WHERE mrr.plan_id = plans.plan_id AND mrr.plan_id = dep.plan_id AND mrr.sr_instance_id = dep.sr_instance_id AND mrr.organization_id = dep.organization_id AND mrr.resource_id = dep.resource_id AND mrr.department_id = dep.department_id /* detail records */ AND dep.bottleneck_flag = 1 AND mrr.plan_id <> -1 AND mrr.resource_id <> -1 AND dep.line_flag <> 1 AND ( plans.daily_material_constraints = 2 AND plans.daily_resource_constraints = 2 AND plans.weekly_material_constraints = 2 AND plans.weekly_resource_constraints = 2 AND plans.period_material_constraints = 2 AND PLANS.PERIOD_RESOURCE_CONSTRAINTS = 2 ) UNION ALL SELECT plans.plan_id, plans.compile_designator supply_plan, plans.compile_designator || ':' || dep.department_code supply_plan_resource, mrr.organization_code org_code, mrr.resource_hours quantity, mrr.resource_date sdate FROM msc_resource_requirements_v mrr, msc_department_resources dep, msc_plans plans WHERE mrr.plan_id = plans.plan_id AND mrr.plan_id = dep.plan_id AND mrr.sr_instance_id = dep.sr_instance_id AND mrr.organization_id = dep.organization_id AND mrr.resource_id = dep.resource_id AND mrr.department_id = dep.department_id /* detail records */ AND dep.bottleneck_flag = 1 AND mrr.plan_id <> -1 AND mrr.resource_id = -1 AND dep.line_flag = 1 AND ( plans.daily_material_constraints = 2 AND plans.daily_resource_constraints = 2 AND plans.weekly_material_constraints = 2 AND plans.weekly_resource_constraints = 2 AND plans.period_material_constraints = 2 AND PLANS.PERIOD_RESOURCE_CONSTRAINTS = 2 )

View Text - HTML Formatted

SELECT PLANS.PLAN_ID PLAN_ID
, PLANS.COMPILE_DESIGNATOR SUPPLY_PLAN
, PLANS.COMPILE_DESIGNATOR || ':' || DEP.RESOURCE_CODE SUPPLY_PLAN_RESOURCE
, MTP_ORG.ORGANIZATION_CODE ORG_CODE
, MRR.RESOURCE_HOURS QUANTITY
, MRR.START_DATE SDATE
FROM MSC_RESOURCE_REQUIREMENTS MRR
, MSC_DEPARTMENT_RESOURCES DEP
, MSC_PLANS PLANS
, MSC_TRADING_PARTNERS MTP_ORG
WHERE MRR.PLAN_ID = PLANS.PLAN_ID
AND MRR.PLAN_ID = DEP.PLAN_ID
AND MRR.SR_INSTANCE_ID = DEP.SR_INSTANCE_ID
AND MRR.ORGANIZATION_ID = DEP.ORGANIZATION_ID
AND MRR.RESOURCE_ID = DEP.RESOURCE_ID
AND MRR.DEPARTMENT_ID = DEP.DEPARTMENT_ID
AND MRR.PARENT_ID = 1 /* DETAIL RECORDS */
AND DEP.BOTTLENECK_FLAG = 1
AND MTP_ORG.SR_INSTANCE_ID = DEP.SR_INSTANCE_ID
AND MTP_ORG.SR_TP_ID = DEP.ORGANIZATION_ID
AND MTP_ORG.PARTNER_TYPE = 3
AND MRR.PLAN_ID <> -1
AND MRR.RESOURCE_ID <> -1
AND DEP.LINE_FLAG <> 1
AND ( PLANS.DAILY_MATERIAL_CONSTRAINTS = 1 OR PLANS.DAILY_RESOURCE_CONSTRAINTS = 1 OR PLANS.WEEKLY_MATERIAL_CONSTRAINTS = 1 OR PLANS.WEEKLY_RESOURCE_CONSTRAINTS = 1 OR PLANS.PERIOD_MATERIAL_CONSTRAINTS = 1 OR PLANS.PERIOD_RESOURCE_CONSTRAINTS = 1 ) UNION ALL SELECT PLANS.PLAN_ID
, PLANS.COMPILE_DESIGNATOR SUPPLY_PLAN
, PLANS.COMPILE_DESIGNATOR || ':' || DEP.DEPARTMENT_CODE SUPPLY_PLAN_RESOURCE
, MTP_ORG.ORGANIZATION_CODE ORG_CODE
, MRR.RESOURCE_HOURS QUANTITY
, MRR.START_DATE SDATE
FROM MSC_RESOURCE_REQUIREMENTS MRR
, MSC_DEPARTMENT_RESOURCES DEP
, MSC_PLANS PLANS
, MSC_TRADING_PARTNERS MTP_ORG
WHERE MRR.PLAN_ID = PLANS.PLAN_ID
AND MRR.PLAN_ID = DEP.PLAN_ID
AND MRR.SR_INSTANCE_ID = DEP.SR_INSTANCE_ID
AND MRR.ORGANIZATION_ID = DEP.ORGANIZATION_ID
AND MRR.RESOURCE_ID = DEP.RESOURCE_ID
AND MRR.DEPARTMENT_ID = DEP.DEPARTMENT_ID
AND MRR.PARENT_ID = 1 /* DETAIL RECORDS */
AND DEP.BOTTLENECK_FLAG = 1
AND MTP_ORG.SR_INSTANCE_ID = DEP.SR_INSTANCE_ID
AND MTP_ORG.SR_TP_ID = DEP.ORGANIZATION_ID
AND MTP_ORG.PARTNER_TYPE = 3
AND MRR.PLAN_ID <> -1
AND MRR.RESOURCE_ID = -1
AND DEP.LINE_FLAG = 1
AND ( PLANS.DAILY_MATERIAL_CONSTRAINTS = 1 OR PLANS.DAILY_RESOURCE_CONSTRAINTS = 1 OR PLANS.WEEKLY_MATERIAL_CONSTRAINTS = 1 OR PLANS.WEEKLY_RESOURCE_CONSTRAINTS = 1 OR PLANS.PERIOD_MATERIAL_CONSTRAINTS = 1 OR PLANS.PERIOD_RESOURCE_CONSTRAINTS = 1 ) UNION ALL SELECT PLANS.PLAN_ID PLAN_ID
, PLANS.COMPILE_DESIGNATOR SUPPLY_PLAN
, PLANS.COMPILE_DESIGNATOR || ':' || DEP.RESOURCE_CODE SUPPLY_PLAN_RESOURCE
, MRR.ORGANIZATION_CODE ORG_CODE
, MRR.RESOURCE_HOURS QUANTITY
, MRR.RESOURCE_DATE SDATE
FROM MSC_RESOURCE_REQUIREMENTS_V MRR
, MSC_DEPARTMENT_RESOURCES DEP
, MSC_PLANS PLANS
WHERE MRR.PLAN_ID = PLANS.PLAN_ID
AND MRR.PLAN_ID = DEP.PLAN_ID
AND MRR.SR_INSTANCE_ID = DEP.SR_INSTANCE_ID
AND MRR.ORGANIZATION_ID = DEP.ORGANIZATION_ID
AND MRR.RESOURCE_ID = DEP.RESOURCE_ID
AND MRR.DEPARTMENT_ID = DEP.DEPARTMENT_ID /* DETAIL RECORDS */
AND DEP.BOTTLENECK_FLAG = 1
AND MRR.PLAN_ID <> -1
AND MRR.RESOURCE_ID <> -1
AND DEP.LINE_FLAG <> 1
AND ( PLANS.DAILY_MATERIAL_CONSTRAINTS = 2
AND PLANS.DAILY_RESOURCE_CONSTRAINTS = 2
AND PLANS.WEEKLY_MATERIAL_CONSTRAINTS = 2
AND PLANS.WEEKLY_RESOURCE_CONSTRAINTS = 2
AND PLANS.PERIOD_MATERIAL_CONSTRAINTS = 2
AND PLANS.PERIOD_RESOURCE_CONSTRAINTS = 2 ) UNION ALL SELECT PLANS.PLAN_ID
, PLANS.COMPILE_DESIGNATOR SUPPLY_PLAN
, PLANS.COMPILE_DESIGNATOR || ':' || DEP.DEPARTMENT_CODE SUPPLY_PLAN_RESOURCE
, MRR.ORGANIZATION_CODE ORG_CODE
, MRR.RESOURCE_HOURS QUANTITY
, MRR.RESOURCE_DATE SDATE
FROM MSC_RESOURCE_REQUIREMENTS_V MRR
, MSC_DEPARTMENT_RESOURCES DEP
, MSC_PLANS PLANS
WHERE MRR.PLAN_ID = PLANS.PLAN_ID
AND MRR.PLAN_ID = DEP.PLAN_ID
AND MRR.SR_INSTANCE_ID = DEP.SR_INSTANCE_ID
AND MRR.ORGANIZATION_ID = DEP.ORGANIZATION_ID
AND MRR.RESOURCE_ID = DEP.RESOURCE_ID
AND MRR.DEPARTMENT_ID = DEP.DEPARTMENT_ID /* DETAIL RECORDS */
AND DEP.BOTTLENECK_FLAG = 1
AND MRR.PLAN_ID <> -1
AND MRR.RESOURCE_ID = -1
AND DEP.LINE_FLAG = 1
AND ( PLANS.DAILY_MATERIAL_CONSTRAINTS = 2
AND PLANS.DAILY_RESOURCE_CONSTRAINTS = 2
AND PLANS.WEEKLY_MATERIAL_CONSTRAINTS = 2
AND PLANS.WEEKLY_RESOURCE_CONSTRAINTS = 2
AND PLANS.PERIOD_MATERIAL_CONSTRAINTS = 2
AND PLANS.PERIOD_RESOURCE_CONSTRAINTS = 2 )