eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MSC_PDR_PLAN_DETAILS_V

Source

View Text - Preformatted


SELECT /*+ ordered */ distinct mp.plan_id, to_char(trunc(sysdate)), '01', 'Owning Organization', pt.organization_code, 'Plan ', mp.compile_designator , mp.compile_designator from msc_pdr_parameters_new parm2, MSC_PLANS MP, msc_TRADING_PARTNERS PT WHERE mp.plan_id = parm2.number1 AND PT.SR_INSTANCE_ID = MP.SR_INSTANCE_ID AND PT.sr_tp_ID = MP.organization_id AND PT.partner_type =3 and parm2.user_id= fnd_global.user_id and parm2.parm_type=2 union all select plan_id, report_creation_date, '022','Plan Start Date',Curr_start_date, 'Plan End  Date', Plan_completion_date ,plan FROM MSC_PDR_PLAN_DETAILS_V1 UNION ALL select /*+ ordered */ pp.plan_id, to_char(trunc(sysdate)), '023',' ', '  ', 'Planning Method',ml.meaning ,pp.compile_designator from msc_pdr_parameters_new parm2, msc_plans pp, mfg_lookups ml where parm2.user_id = fnd_global.user_id and parm2.parm_type = 2 and PP.CUrr_plan_type = ml.lookup_code and ml.lookup_type = 'MSC_PLAN_TYPE_LONG' and PP.plan_id = parm2.number1 UNION ALL select pp. plan_id, to_char(trunc(sysdate)), '03','Planned Resources',decode( nvl(pp.CURR_PLANNED_RESOURCES,1),1,'All Resources',2,'Bottleneck Resources') , 'Optimized',decode( nvl(pp.OPTIMIZE_FLAG, decode(pp.curr_plan_type,4,1,2)),1,'Yes', 2, 'No') , pp.compile_designator from msc_pdr_parameters_new parm2, msc_plans pp where parm2.user_id = fnd_global.user_id and parm2.parm_type = 2 and PP.plan_id = parm2.number1 UNION ALL select plan_id, report_creation_date, '04','Planned Items', PLANNED_ITEMS , 'Enforce Capacity Constraints', ENFORCE_CAPACITY_CONSTRAINTS ,plan FROM MSC_PDR_PLAN_DETAILS_V1 UNION ALL select plan_id, report_creation_date, '05','Assignment Set', ASSIGNMENT_SET , 'Enforce Demand Due Dates', ENFORCE_DEMAND_DUE_DATES , plan FROM MSC_PDR_PLAN_DETAILS_V1 UNION ALL select 0, to_char(trunc(sysdate)), '055',' ',' ' , 'Plan Objectives(Weights)',' ' , null FROM dual UNION ALL select pp.plan_id, to_char(trunc(sysdate)), '06','Demand Priority Rule', nvl(MSR.MEANING,' '), 'Maximize Inventory Turns', to_char( nvl(PP. OBJECTIVE_WEIGHT_1,0)) ,pp.compile_designator from msc_pdr_parameters_new parm2, MSC_SCHEDULING_RULES MSR, msc_plans pp where parm2.user_id = fnd_global.user_id and parm2.parm_type = 2 and PP.plan_id = parm2.number1 AND MSR.RULE_ID(+)=pp.CURR_DEM_PRIORITY_RULE_ID UNION ALL select pp.plan_id, to_char(trunc(sysdate)), '07','Material Scheduling Method', decode( nvl(NVL(pp.CURR_OPERATION_SCHEDULE_TYPE, P.OPERATION_SCHEDULE_TYPE),2),1,'Operation Start Date', 2, 'Order Start Date'), 'Maximize Plan Profits', to_char( nvl(pP.OBJECTIVE_WEIGHT_2,0)) ,pp.compile_designator from msc_pdr_parameters_new parm2, MSC_PARAMETERS P, msc_plans pp where parm2.user_id = fnd_global.user_id and parm2.parm_type = 2 and PP.plan_id = parm2.number1 AND pp.ORGANIZATION_ID = P.ORGANIZATION_ID(+) AND pp.SR_INSTANCE_ID = P.SR_INSTANCE_ID(+) UNION ALL select plan_id, report_creation_date, '08','Append Planned Orders', APPEND_PLANNED_ORDERS , 'Maximize Ontime Delivery', to_char(MAXIMIZE_ONTIME_DELIVERY) , plan FROM MSC_PDR_PLAN_DETAILS_V1 UNION ALL select 0,to_char(trunc(sysdate)), '085',' ', ' ', 'Penalty Factors',' ' , null from dual UNION ALL select pp.plan_id, to_char(trunc(sysdate)), '09',' ', '  ', 'Exceeding Material Capacity', to_char( nvl( pp.SUPPLIER_CAP_OVER_UTIL_COST,0)) ,pp.compile_designator from msc_pdr_parameters_new parm2, msc_plans pp where parm2.user_id = fnd_global.user_id and parm2.parm_type = 2 and PP.plan_id = parm2.number1 UNION ALL select pp.plan_id, to_char(trunc(sysdate)), '10','Demand Time Fence Control', decode(NVL(pp.CURR_DEMAND_TIME_FENCE_FLAG, P.DEMAND_TIME_FENCE_FLAG),1,'Yes', 2, 'No'), 'Exceeding Resource Capacity', to_char( nvl(pp.RESOURCE_OVER_UTIL_COST,0)) , pp.compile_designator from msc_pdr_parameters_new parm2, MSC_PARAMETERS P, msc_plans pp where parm2.user_id = fnd_global.user_id and parm2.parm_type = 2 and PP.plan_id = parm2.number1 AND pp.ORGANIZATION_ID = P.ORGANIZATION_ID (+) AND pp.SR_INSTANCE_ID = P.SR_INSTANCE_ID (+) UNION ALL select pp.plan_id, to_char(trunc(sysdate)), '11','Planning Time Fence Control', decode( NVL(pp.CURR_PLANNING_TIME_FENCE_FLAG, P.PLANNING_TIME_FENCE_FLAG),1,'Yes', 2, 'No'), 'Exceeding Trans Capacity', to_char( nvl( pp.TRANSPORT_CAP_OVER_UTIL_COST,0)) ,pp.compile_designator from msc_pdr_parameters_new parm2, MSC_PARAMETERS P, msc_plans pp where parm2.user_id = fnd_global.user_id and parm2.parm_type = 2 and PP.plan_id = parm2.number1 AND pp.ORGANIZATION_ID = P.ORGANIZATION_ID (+) AND pp.SR_INSTANCE_ID = P.SR_INSTANCE_ID (+) UNION ALL select pp.plan_id, to_char(trunc(sysdate)), '12','Lot for Lot', decode(pp.LOT_FOR_LOT_FLAG,1,'Yes', 2, 'No'), 'Demand Lateness', to_char( nvl(pp.DMD_LATENESS_PENALTY_COST,0)) , pp.compile_designator from msc_pdr_parameters_new parm2, msc_plans pp where parm2.user_id = fnd_global.user_id and parm2.parm_type = 2 and PP.plan_id = parm2.number1 UNION ALL select pp.plan_id, to_char(trunc(sysdate)), '121','Pegging', decode(PP.CURR_FULL_PEGGING,1,'Yes', 2, 'No'), ' ', ' ' , pp.compile_designator from msc_pdr_parameters_new parm2, msc_plans pp where parm2.user_id = fnd_global.user_id and parm2.parm_type = 2 and PP.plan_id = parm2.number1 union all select pp.plan_id, to_char(trunc(sysdate)), '122', 'Peg Supplies by Demand Priority ', decode(pp.curr_priority_pegging, null , 'No', 1, 'Yes',2, 'No'),'Reservation Level' ,decode(NVL(pp.CURR_RESERVATION_LEVEL, 4), null, 'None', 1, 'Planning Group ',2, 'Project ', 3, 'Project-Task ',4, 'None ') ,pp.compile_designator from msc_pdr_parameters_new parm2, msc_plans pp where parm2.user_id = fnd_global.user_id and parm2.parm_type = 2 and PP.plan_id = parm2.number1 union all select pp.plan_id, to_char(trunc(sysdate)), '123',' ',' ' , 'Hard Pegging Level', decode( NVL(pp.CURR_HARD_PEGGING_LEVEL, 3), null, 'None', 1, 'Project',2, 'Project-Task', 3, 'None') , pp.compile_designator from msc_pdr_parameters_new parm2, msc_plans pp where parm2.user_id = fnd_global.user_id and parm2.parm_type = 2 and PP.plan_id = parm2.number1 union all select 0,to_char( trunc(sysdate)), '13','Planning Buckets', '   ', ' ', ' ' , null from dual union all select 0,to_char(trunc(sysdate)), '14',' ','Days', 'Weeks','Periods' ,null from dual union all select plan_id, report_creation_date, '15','Bucket Size', to_char(DAILY_BUCKET_SIZE), to_char(Weekly_BUCKET_SIZE), to_char(Period_BUCKET_SIZE) ,plan FROM MSC_PDR_PLAN_DETAILS_V1 UNION ALL select plan_id, report_creation_date, '15','Start Date', to_char(msc_snapshot_pk.calculate_start_date1( v1.organization_id, v1.sr_instance_id, v1.curr_start_date, nvl(v1.daily_bucket_size,0), nvl(v1.weekly_bucket_size,0), nvl(v1.period_bucket_size,0), 1)), to_char(msc_snapshot_pk.calculate_start_date1( v1.organization_id, v1.sr_instance_id, v1.curr_start_date, nvl(v1.daily_bucket_size,0), nvl(v1.weekly_bucket_size,0), nvl(v1.period_bucket_size,0), 2)) , to_char(msc_snapshot_pk.calculate_start_date1( v1.organization_id, v1.sr_instance_id, v1.curr_start_date, nvl(v1.daily_bucket_size,0), nvl(v1.weekly_bucket_size,0), nvl(v1.period_bucket_size,0), 3)), plan FROM MSC_PDR_PLAN_DETAILS_V1 v1 UNION ALL select pp.plan_id, to_char(trunc(sysdate)), '16','Items', decode(nvl(pp.DAILY_ITEM_AGGREGATION_LEVEL,1),1,'Items',2,'Product Family'), decode(nvl(pp.WEEKLY_ITEM_AGGREGATION_LEVEL,1),1,'Items',2,'Product Family'), decode(nvl(pp.PERIOD_ITEM_AGGREGATION_LEVEL,1),1,'Items',2,'Product Family'), pp.compile_designator from msc_pdr_parameters_new parm2, msc_plans pp where parm2.user_id = fnd_global.user_id and parm2.parm_type = 2 and PP.plan_id = parm2.number1 UNION ALL select pp.plan_id, to_char(trunc(sysdate)), '17','Resources', decode(nvl(pp.DAILY_RES_AGGREGATION_LEVEL,1),1, 'Individual',2,'Aggregate'), decode(nvl(pp.WEEKLY_RES_AGGREGATION_LEVEL,1),1, 'Individual',2,'Aggregate'), decode(nvl(pp.PERIOD_RES_AGGREGATION_LEVEL,1),1, 'Individual',2,'Aggregate'), pp.compile_designator from msc_pdr_parameters_new parm2, msc_plans pp where parm2.user_id = fnd_global.user_id and parm2.parm_type = 2 and PP.plan_id = parm2.number1 UNION ALL select pp.plan_id, to_char(trunc(sysdate)), '18','Routings', decode(nvl(pp.DAILY_RTG_AGGREGATION_LEVEL, decode(pp.curr_plan_type,2,2,1)),1,'Routings',2,'BOR'), decode(nvl(pp.WEEKLY_RTG_AGGREGATION_LEVEL, decode(pp.curr_plan_type,2,2,1)),1,'Routings',2,'BOR'), decode(nvl(pp.PERIOD_RTG_AGGREGATION_LEVEL, decode(pp.curr_plan_type,2,2,1)) ,1,'Routings',2,'BOR'), pp.compile_designator from msc_pdr_parameters_new parm2, msc_plans pp where parm2.user_id = fnd_global.user_id and parm2.parm_type = 2 and PP.plan_id = parm2.number1 UNION ALL select pp.plan_id, to_char(trunc(sysdate)), '19','Plan Resource Constraints', decode(nvl(pp.DAILY_RESOURCE_CONSTRAINTS,2),1,'Yes', 2, 'No'), decode(nvl(pp.WEEKLY_RESOURCE_CONSTRAINTS,2),1,'Yes', 2, 'No'), decode(nvl(pp.PERIOD_RESOURCE_CONSTRAINTS,2),1,'Yes', 2, 'No') , pp.compile_designator from msc_pdr_parameters_new parm2, msc_plans pp where parm2.user_id = fnd_global.user_id and parm2.parm_type = 2 and PP.plan_id = parm2.number1 UNION ALL select pp.plan_id, to_char(trunc(sysdate)), '20','Plan Material Constraints', decode(nvl(pp.DAILY_MATERIAL_CONSTRAINTS,2),1,'Yes', 2, 'No'), decode(nvl(pp.WEEKLY_MATERIAL_CONSTRAINTS,2),1,'Yes', 2, 'No'), decode(nvl(pp.PERIOD_MATERIAL_CONSTRAINTS,2),1,'Yes', 2, 'No'), pp.compile_designator from msc_pdr_parameters_new parm2, msc_plans pp where parm2.user_id = fnd_global.user_id and parm2.parm_type = 2 and PP.plan_id = parm2.number1 UNION ALL select pp.plan_id,to_char( trunc(sysdate)), '21','Decision Rules', '  ', '  ','  ' ,pp.compile_designator from msc_pdr_parameters_new parm2, msc_plans pp where parm2.user_id = fnd_global.user_id and parm2.parm_type = 2 and PP.plan_id = parm2.number1 and pp.curr_plan_type <> 4 UNION ALL select v1. plan_id, v1.report_creation_date, '22','Use End Item Substitutions', v1.use_end_item_substitutions, ' ' , ' ' , plan FROM MSC_PDR_PLAN_DETAILS_V1 V1 where v1.curr_plan_type <> 4 UNION ALL select v1. plan_id, v1.report_creation_date, '23','Use Alternate Resource', v1.Use_Alternate_Resources, ' ', ' ' , plan FROM MSC_PDR_PLAN_DETAILS_V1 V1 where v1.curr_plan_type <> 4 UNION ALL select plan_id, report_creation_date, '24','Use Substitute Components', v1.use_substitute_components, ' ', ' ', plan FROM MSC_PDR_PLAN_DETAILS_V1 V1 where v1.curr_plan_type <> 4 UNION ALL select plan_id, report_creation_date, '25', 'Use Alternate BOM/Routing' ,v1.Use_Alternate_BOM_Routing, ' ', ' ', plan FROM MSC_PDR_PLAN_DETAILS_V1 V1 where v1.curr_plan_type <> 4 UNION ALL select plan_id, report_creation_date, '26','Use Alternate Sources', v1.use_alternate_sources , ' ' ,' ' , plan FROM MSC_PDR_PLAN_DETAILS_V1 V1 where v1.curr_plan_type <> 4 UNION ALL select plan_id, report_creation_date, '30','Other Plan Organizations',' ', ' ',' ' , plan FROM MSC_PDR_PLAN_DETAILS_V1 union all select mpo.plan_id, to_char(trunc(sysdate)), '31', ' ', mpo.organization_code, ' ', ' ', mp1.compile_designator from msc_plan_organizations mpo, msc_pdr_parameters_new parm2, msc_plans mp1 where mpo.plan_id=parm2.number1 and mp1.plan_id =parm2.number1 and not exists (select 1 from msc_plans mp where mp.organization_id = mpo.organization_id and mp.sr_instance_id = mpo.sr_instance_id and mp.plan_id = parm2.number1) and parm2.user_id= fnd_global.user_id and parm2.parm_type=2

View Text - HTML Formatted

SELECT /*+ ORDERED */ DISTINCT MP.PLAN_ID
, TO_CHAR(TRUNC(SYSDATE))
, '01'
, 'OWNING ORGANIZATION'
, PT.ORGANIZATION_CODE
, 'PLAN '
, MP.COMPILE_DESIGNATOR
, MP.COMPILE_DESIGNATOR
FROM MSC_PDR_PARAMETERS_NEW PARM2
, MSC_PLANS MP
, MSC_TRADING_PARTNERS PT
WHERE MP.PLAN_ID = PARM2.NUMBER1
AND PT.SR_INSTANCE_ID = MP.SR_INSTANCE_ID
AND PT.SR_TP_ID = MP.ORGANIZATION_ID
AND PT.PARTNER_TYPE =3
AND PARM2.USER_ID= FND_GLOBAL.USER_ID
AND PARM2.PARM_TYPE=2 UNION ALL SELECT PLAN_ID
, REPORT_CREATION_DATE
, '022'
, 'PLAN START DATE'
, CURR_START_DATE
, 'PLAN END DATE'
, PLAN_COMPLETION_DATE
, PLAN
FROM MSC_PDR_PLAN_DETAILS_V1 UNION ALL SELECT /*+ ORDERED */ PP.PLAN_ID
, TO_CHAR(TRUNC(SYSDATE))
, '023'
, ' '
, ' '
, 'PLANNING METHOD'
, ML.MEANING
, PP.COMPILE_DESIGNATOR
FROM MSC_PDR_PARAMETERS_NEW PARM2
, MSC_PLANS PP
, MFG_LOOKUPS ML
WHERE PARM2.USER_ID = FND_GLOBAL.USER_ID
AND PARM2.PARM_TYPE = 2
AND PP.CURR_PLAN_TYPE = ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE = 'MSC_PLAN_TYPE_LONG'
AND PP.PLAN_ID = PARM2.NUMBER1 UNION ALL SELECT PP. PLAN_ID
, TO_CHAR(TRUNC(SYSDATE))
, '03'
, 'PLANNED RESOURCES'
, DECODE( NVL(PP.CURR_PLANNED_RESOURCES
, 1)
, 1
, 'ALL RESOURCES'
, 2
, 'BOTTLENECK RESOURCES')
, 'OPTIMIZED'
, DECODE( NVL(PP.OPTIMIZE_FLAG
, DECODE(PP.CURR_PLAN_TYPE
, 4
, 1
, 2))
, 1
, 'YES'
, 2
, 'NO')
, PP.COMPILE_DESIGNATOR
FROM MSC_PDR_PARAMETERS_NEW PARM2
, MSC_PLANS PP
WHERE PARM2.USER_ID = FND_GLOBAL.USER_ID
AND PARM2.PARM_TYPE = 2
AND PP.PLAN_ID = PARM2.NUMBER1 UNION ALL SELECT PLAN_ID
, REPORT_CREATION_DATE
, '04'
, 'PLANNED ITEMS'
, PLANNED_ITEMS
, 'ENFORCE CAPACITY CONSTRAINTS'
, ENFORCE_CAPACITY_CONSTRAINTS
, PLAN
FROM MSC_PDR_PLAN_DETAILS_V1 UNION ALL SELECT PLAN_ID
, REPORT_CREATION_DATE
, '05'
, 'ASSIGNMENT SET'
, ASSIGNMENT_SET
, 'ENFORCE DEMAND DUE DATES'
, ENFORCE_DEMAND_DUE_DATES
, PLAN
FROM MSC_PDR_PLAN_DETAILS_V1 UNION ALL SELECT 0
, TO_CHAR(TRUNC(SYSDATE))
, '055'
, ' '
, ' '
, 'PLAN OBJECTIVES(WEIGHTS)'
, ' '
, NULL
FROM DUAL UNION ALL SELECT PP.PLAN_ID
, TO_CHAR(TRUNC(SYSDATE))
, '06'
, 'DEMAND PRIORITY RULE'
, NVL(MSR.MEANING
, ' ')
, 'MAXIMIZE INVENTORY TURNS'
, TO_CHAR( NVL(PP. OBJECTIVE_WEIGHT_1
, 0))
, PP.COMPILE_DESIGNATOR
FROM MSC_PDR_PARAMETERS_NEW PARM2
, MSC_SCHEDULING_RULES MSR
, MSC_PLANS PP
WHERE PARM2.USER_ID = FND_GLOBAL.USER_ID
AND PARM2.PARM_TYPE = 2
AND PP.PLAN_ID = PARM2.NUMBER1
AND MSR.RULE_ID(+)=PP.CURR_DEM_PRIORITY_RULE_ID UNION ALL SELECT PP.PLAN_ID
, TO_CHAR(TRUNC(SYSDATE))
, '07'
, 'MATERIAL SCHEDULING METHOD'
, DECODE( NVL(NVL(PP.CURR_OPERATION_SCHEDULE_TYPE
, P.OPERATION_SCHEDULE_TYPE)
, 2)
, 1
, 'OPERATION START DATE'
, 2
, 'ORDER START DATE')
, 'MAXIMIZE PLAN PROFITS'
, TO_CHAR( NVL(PP.OBJECTIVE_WEIGHT_2
, 0))
, PP.COMPILE_DESIGNATOR
FROM MSC_PDR_PARAMETERS_NEW PARM2
, MSC_PARAMETERS P
, MSC_PLANS PP
WHERE PARM2.USER_ID = FND_GLOBAL.USER_ID
AND PARM2.PARM_TYPE = 2
AND PP.PLAN_ID = PARM2.NUMBER1
AND PP.ORGANIZATION_ID = P.ORGANIZATION_ID(+)
AND PP.SR_INSTANCE_ID = P.SR_INSTANCE_ID(+) UNION ALL SELECT PLAN_ID
, REPORT_CREATION_DATE
, '08'
, 'APPEND PLANNED ORDERS'
, APPEND_PLANNED_ORDERS
, 'MAXIMIZE ONTIME DELIVERY'
, TO_CHAR(MAXIMIZE_ONTIME_DELIVERY)
, PLAN
FROM MSC_PDR_PLAN_DETAILS_V1 UNION ALL SELECT 0
, TO_CHAR(TRUNC(SYSDATE))
, '085'
, ' '
, ' '
, 'PENALTY FACTORS'
, ' '
, NULL
FROM DUAL UNION ALL SELECT PP.PLAN_ID
, TO_CHAR(TRUNC(SYSDATE))
, '09'
, ' '
, ' '
, 'EXCEEDING MATERIAL CAPACITY'
, TO_CHAR( NVL( PP.SUPPLIER_CAP_OVER_UTIL_COST
, 0))
, PP.COMPILE_DESIGNATOR
FROM MSC_PDR_PARAMETERS_NEW PARM2
, MSC_PLANS PP
WHERE PARM2.USER_ID = FND_GLOBAL.USER_ID
AND PARM2.PARM_TYPE = 2
AND PP.PLAN_ID = PARM2.NUMBER1 UNION ALL SELECT PP.PLAN_ID
, TO_CHAR(TRUNC(SYSDATE))
, '10'
, 'DEMAND TIME FENCE CONTROL'
, DECODE(NVL(PP.CURR_DEMAND_TIME_FENCE_FLAG
, P.DEMAND_TIME_FENCE_FLAG)
, 1
, 'YES'
, 2
, 'NO')
, 'EXCEEDING RESOURCE CAPACITY'
, TO_CHAR( NVL(PP.RESOURCE_OVER_UTIL_COST
, 0))
, PP.COMPILE_DESIGNATOR
FROM MSC_PDR_PARAMETERS_NEW PARM2
, MSC_PARAMETERS P
, MSC_PLANS PP
WHERE PARM2.USER_ID = FND_GLOBAL.USER_ID
AND PARM2.PARM_TYPE = 2
AND PP.PLAN_ID = PARM2.NUMBER1
AND PP.ORGANIZATION_ID = P.ORGANIZATION_ID (+)
AND PP.SR_INSTANCE_ID = P.SR_INSTANCE_ID (+) UNION ALL SELECT PP.PLAN_ID
, TO_CHAR(TRUNC(SYSDATE))
, '11'
, 'PLANNING TIME FENCE CONTROL'
, DECODE( NVL(PP.CURR_PLANNING_TIME_FENCE_FLAG
, P.PLANNING_TIME_FENCE_FLAG)
, 1
, 'YES'
, 2
, 'NO')
, 'EXCEEDING TRANS CAPACITY'
, TO_CHAR( NVL( PP.TRANSPORT_CAP_OVER_UTIL_COST
, 0))
, PP.COMPILE_DESIGNATOR
FROM MSC_PDR_PARAMETERS_NEW PARM2
, MSC_PARAMETERS P
, MSC_PLANS PP
WHERE PARM2.USER_ID = FND_GLOBAL.USER_ID
AND PARM2.PARM_TYPE = 2
AND PP.PLAN_ID = PARM2.NUMBER1
AND PP.ORGANIZATION_ID = P.ORGANIZATION_ID (+)
AND PP.SR_INSTANCE_ID = P.SR_INSTANCE_ID (+) UNION ALL SELECT PP.PLAN_ID
, TO_CHAR(TRUNC(SYSDATE))
, '12'
, 'LOT FOR LOT'
, DECODE(PP.LOT_FOR_LOT_FLAG
, 1
, 'YES'
, 2
, 'NO')
, 'DEMAND LATENESS'
, TO_CHAR( NVL(PP.DMD_LATENESS_PENALTY_COST
, 0))
, PP.COMPILE_DESIGNATOR
FROM MSC_PDR_PARAMETERS_NEW PARM2
, MSC_PLANS PP
WHERE PARM2.USER_ID = FND_GLOBAL.USER_ID
AND PARM2.PARM_TYPE = 2
AND PP.PLAN_ID = PARM2.NUMBER1 UNION ALL SELECT PP.PLAN_ID
, TO_CHAR(TRUNC(SYSDATE))
, '121'
, 'PEGGING'
, DECODE(PP.CURR_FULL_PEGGING
, 1
, 'YES'
, 2
, 'NO')
, ' '
, ' '
, PP.COMPILE_DESIGNATOR
FROM MSC_PDR_PARAMETERS_NEW PARM2
, MSC_PLANS PP
WHERE PARM2.USER_ID = FND_GLOBAL.USER_ID
AND PARM2.PARM_TYPE = 2
AND PP.PLAN_ID = PARM2.NUMBER1 UNION ALL SELECT PP.PLAN_ID
, TO_CHAR(TRUNC(SYSDATE))
, '122'
, 'PEG SUPPLIES BY DEMAND PRIORITY '
, DECODE(PP.CURR_PRIORITY_PEGGING
, NULL
, 'NO'
, 1
, 'YES'
, 2
, 'NO')
, 'RESERVATION LEVEL'
, DECODE(NVL(PP.CURR_RESERVATION_LEVEL
, 4)
, NULL
, 'NONE'
, 1
, 'PLANNING GROUP '
, 2
, 'PROJECT '
, 3
, 'PROJECT-TASK '
, 4
, 'NONE ')
, PP.COMPILE_DESIGNATOR
FROM MSC_PDR_PARAMETERS_NEW PARM2
, MSC_PLANS PP
WHERE PARM2.USER_ID = FND_GLOBAL.USER_ID
AND PARM2.PARM_TYPE = 2
AND PP.PLAN_ID = PARM2.NUMBER1 UNION ALL SELECT PP.PLAN_ID
, TO_CHAR(TRUNC(SYSDATE))
, '123'
, ' '
, ' '
, 'HARD PEGGING LEVEL'
, DECODE( NVL(PP.CURR_HARD_PEGGING_LEVEL
, 3)
, NULL
, 'NONE'
, 1
, 'PROJECT'
, 2
, 'PROJECT-TASK'
, 3
, 'NONE')
, PP.COMPILE_DESIGNATOR
FROM MSC_PDR_PARAMETERS_NEW PARM2
, MSC_PLANS PP
WHERE PARM2.USER_ID = FND_GLOBAL.USER_ID
AND PARM2.PARM_TYPE = 2
AND PP.PLAN_ID = PARM2.NUMBER1 UNION ALL SELECT 0
, TO_CHAR( TRUNC(SYSDATE))
, '13'
, 'PLANNING BUCKETS'
, ' '
, ' '
, ' '
, NULL
FROM DUAL UNION ALL SELECT 0
, TO_CHAR(TRUNC(SYSDATE))
, '14'
, ' '
, 'DAYS'
, 'WEEKS'
, 'PERIODS'
, NULL
FROM DUAL UNION ALL SELECT PLAN_ID
, REPORT_CREATION_DATE
, '15'
, 'BUCKET SIZE'
, TO_CHAR(DAILY_BUCKET_SIZE)
, TO_CHAR(WEEKLY_BUCKET_SIZE)
, TO_CHAR(PERIOD_BUCKET_SIZE)
, PLAN
FROM MSC_PDR_PLAN_DETAILS_V1 UNION ALL SELECT PLAN_ID
, REPORT_CREATION_DATE
, '15'
, 'START DATE'
, TO_CHAR(MSC_SNAPSHOT_PK.CALCULATE_START_DATE1( V1.ORGANIZATION_ID
, V1.SR_INSTANCE_ID
, V1.CURR_START_DATE
, NVL(V1.DAILY_BUCKET_SIZE
, 0)
, NVL(V1.WEEKLY_BUCKET_SIZE
, 0)
, NVL(V1.PERIOD_BUCKET_SIZE
, 0)
, 1))
, TO_CHAR(MSC_SNAPSHOT_PK.CALCULATE_START_DATE1( V1.ORGANIZATION_ID
, V1.SR_INSTANCE_ID
, V1.CURR_START_DATE
, NVL(V1.DAILY_BUCKET_SIZE
, 0)
, NVL(V1.WEEKLY_BUCKET_SIZE
, 0)
, NVL(V1.PERIOD_BUCKET_SIZE
, 0)
, 2))
, TO_CHAR(MSC_SNAPSHOT_PK.CALCULATE_START_DATE1( V1.ORGANIZATION_ID
, V1.SR_INSTANCE_ID
, V1.CURR_START_DATE
, NVL(V1.DAILY_BUCKET_SIZE
, 0)
, NVL(V1.WEEKLY_BUCKET_SIZE
, 0)
, NVL(V1.PERIOD_BUCKET_SIZE
, 0)
, 3))
, PLAN
FROM MSC_PDR_PLAN_DETAILS_V1 V1 UNION ALL SELECT PP.PLAN_ID
, TO_CHAR(TRUNC(SYSDATE))
, '16'
, 'ITEMS'
, DECODE(NVL(PP.DAILY_ITEM_AGGREGATION_LEVEL
, 1)
, 1
, 'ITEMS'
, 2
, 'PRODUCT FAMILY')
, DECODE(NVL(PP.WEEKLY_ITEM_AGGREGATION_LEVEL
, 1)
, 1
, 'ITEMS'
, 2
, 'PRODUCT FAMILY')
, DECODE(NVL(PP.PERIOD_ITEM_AGGREGATION_LEVEL
, 1)
, 1
, 'ITEMS'
, 2
, 'PRODUCT FAMILY')
, PP.COMPILE_DESIGNATOR
FROM MSC_PDR_PARAMETERS_NEW PARM2
, MSC_PLANS PP
WHERE PARM2.USER_ID = FND_GLOBAL.USER_ID
AND PARM2.PARM_TYPE = 2
AND PP.PLAN_ID = PARM2.NUMBER1 UNION ALL SELECT PP.PLAN_ID
, TO_CHAR(TRUNC(SYSDATE))
, '17'
, 'RESOURCES'
, DECODE(NVL(PP.DAILY_RES_AGGREGATION_LEVEL
, 1)
, 1
, 'INDIVIDUAL'
, 2
, 'AGGREGATE')
, DECODE(NVL(PP.WEEKLY_RES_AGGREGATION_LEVEL
, 1)
, 1
, 'INDIVIDUAL'
, 2
, 'AGGREGATE')
, DECODE(NVL(PP.PERIOD_RES_AGGREGATION_LEVEL
, 1)
, 1
, 'INDIVIDUAL'
, 2
, 'AGGREGATE')
, PP.COMPILE_DESIGNATOR
FROM MSC_PDR_PARAMETERS_NEW PARM2
, MSC_PLANS PP
WHERE PARM2.USER_ID = FND_GLOBAL.USER_ID
AND PARM2.PARM_TYPE = 2
AND PP.PLAN_ID = PARM2.NUMBER1 UNION ALL SELECT PP.PLAN_ID
, TO_CHAR(TRUNC(SYSDATE))
, '18'
, 'ROUTINGS'
, DECODE(NVL(PP.DAILY_RTG_AGGREGATION_LEVEL
, DECODE(PP.CURR_PLAN_TYPE
, 2
, 2
, 1))
, 1
, 'ROUTINGS'
, 2
, 'BOR')
, DECODE(NVL(PP.WEEKLY_RTG_AGGREGATION_LEVEL
, DECODE(PP.CURR_PLAN_TYPE
, 2
, 2
, 1))
, 1
, 'ROUTINGS'
, 2
, 'BOR')
, DECODE(NVL(PP.PERIOD_RTG_AGGREGATION_LEVEL
, DECODE(PP.CURR_PLAN_TYPE
, 2
, 2
, 1))
, 1
, 'ROUTINGS'
, 2
, 'BOR')
, PP.COMPILE_DESIGNATOR
FROM MSC_PDR_PARAMETERS_NEW PARM2
, MSC_PLANS PP
WHERE PARM2.USER_ID = FND_GLOBAL.USER_ID
AND PARM2.PARM_TYPE = 2
AND PP.PLAN_ID = PARM2.NUMBER1 UNION ALL SELECT PP.PLAN_ID
, TO_CHAR(TRUNC(SYSDATE))
, '19'
, 'PLAN RESOURCE CONSTRAINTS'
, DECODE(NVL(PP.DAILY_RESOURCE_CONSTRAINTS
, 2)
, 1
, 'YES'
, 2
, 'NO')
, DECODE(NVL(PP.WEEKLY_RESOURCE_CONSTRAINTS
, 2)
, 1
, 'YES'
, 2
, 'NO')
, DECODE(NVL(PP.PERIOD_RESOURCE_CONSTRAINTS
, 2)
, 1
, 'YES'
, 2
, 'NO')
, PP.COMPILE_DESIGNATOR
FROM MSC_PDR_PARAMETERS_NEW PARM2
, MSC_PLANS PP
WHERE PARM2.USER_ID = FND_GLOBAL.USER_ID
AND PARM2.PARM_TYPE = 2
AND PP.PLAN_ID = PARM2.NUMBER1 UNION ALL SELECT PP.PLAN_ID
, TO_CHAR(TRUNC(SYSDATE))
, '20'
, 'PLAN MATERIAL CONSTRAINTS'
, DECODE(NVL(PP.DAILY_MATERIAL_CONSTRAINTS
, 2)
, 1
, 'YES'
, 2
, 'NO')
, DECODE(NVL(PP.WEEKLY_MATERIAL_CONSTRAINTS
, 2)
, 1
, 'YES'
, 2
, 'NO')
, DECODE(NVL(PP.PERIOD_MATERIAL_CONSTRAINTS
, 2)
, 1
, 'YES'
, 2
, 'NO')
, PP.COMPILE_DESIGNATOR
FROM MSC_PDR_PARAMETERS_NEW PARM2
, MSC_PLANS PP
WHERE PARM2.USER_ID = FND_GLOBAL.USER_ID
AND PARM2.PARM_TYPE = 2
AND PP.PLAN_ID = PARM2.NUMBER1 UNION ALL SELECT PP.PLAN_ID
, TO_CHAR( TRUNC(SYSDATE))
, '21'
, 'DECISION RULES'
, ' '
, ' '
, ' '
, PP.COMPILE_DESIGNATOR
FROM MSC_PDR_PARAMETERS_NEW PARM2
, MSC_PLANS PP
WHERE PARM2.USER_ID = FND_GLOBAL.USER_ID
AND PARM2.PARM_TYPE = 2
AND PP.PLAN_ID = PARM2.NUMBER1
AND PP.CURR_PLAN_TYPE <> 4 UNION ALL SELECT V1. PLAN_ID
, V1.REPORT_CREATION_DATE
, '22'
, 'USE END ITEM SUBSTITUTIONS'
, V1.USE_END_ITEM_SUBSTITUTIONS
, ' '
, ' '
, PLAN
FROM MSC_PDR_PLAN_DETAILS_V1 V1
WHERE V1.CURR_PLAN_TYPE <> 4 UNION ALL SELECT V1. PLAN_ID
, V1.REPORT_CREATION_DATE
, '23'
, 'USE ALTERNATE RESOURCE'
, V1.USE_ALTERNATE_RESOURCES
, ' '
, ' '
, PLAN
FROM MSC_PDR_PLAN_DETAILS_V1 V1
WHERE V1.CURR_PLAN_TYPE <> 4 UNION ALL SELECT PLAN_ID
, REPORT_CREATION_DATE
, '24'
, 'USE SUBSTITUTE COMPONENTS'
, V1.USE_SUBSTITUTE_COMPONENTS
, ' '
, ' '
, PLAN
FROM MSC_PDR_PLAN_DETAILS_V1 V1
WHERE V1.CURR_PLAN_TYPE <> 4 UNION ALL SELECT PLAN_ID
, REPORT_CREATION_DATE
, '25'
, 'USE ALTERNATE BOM/ROUTING'
, V1.USE_ALTERNATE_BOM_ROUTING
, ' '
, ' '
, PLAN
FROM MSC_PDR_PLAN_DETAILS_V1 V1
WHERE V1.CURR_PLAN_TYPE <> 4 UNION ALL SELECT PLAN_ID
, REPORT_CREATION_DATE
, '26'
, 'USE ALTERNATE SOURCES'
, V1.USE_ALTERNATE_SOURCES
, ' '
, ' '
, PLAN
FROM MSC_PDR_PLAN_DETAILS_V1 V1
WHERE V1.CURR_PLAN_TYPE <> 4 UNION ALL SELECT PLAN_ID
, REPORT_CREATION_DATE
, '30'
, 'OTHER PLAN ORGANIZATIONS'
, ' '
, ' '
, ' '
, PLAN
FROM MSC_PDR_PLAN_DETAILS_V1 UNION ALL SELECT MPO.PLAN_ID
, TO_CHAR(TRUNC(SYSDATE))
, '31'
, ' '
, MPO.ORGANIZATION_CODE
, ' '
, ' '
, MP1.COMPILE_DESIGNATOR
FROM MSC_PLAN_ORGANIZATIONS MPO
, MSC_PDR_PARAMETERS_NEW PARM2
, MSC_PLANS MP1
WHERE MPO.PLAN_ID=PARM2.NUMBER1
AND MP1.PLAN_ID =PARM2.NUMBER1
AND NOT EXISTS (SELECT 1
FROM MSC_PLANS MP
WHERE MP.ORGANIZATION_ID = MPO.ORGANIZATION_ID
AND MP.SR_INSTANCE_ID = MPO.SR_INSTANCE_ID
AND MP.PLAN_ID = PARM2.NUMBER1)
AND PARM2.USER_ID= FND_GLOBAL.USER_ID
AND PARM2.PARM_TYPE=2