View: MSC_PDR_PLAN_DETAILS_V

Product:	MSC - Advanced Supply Chain Planning
Description:
Implementation/DBA Data:	APPS.MSC_PDR_PLAN_DETAILS_V

View Text

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
, TO_DATE(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
, TO_DATE(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
, TO_DATE(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

Columns