View: MSC_PDR_VERT_LIST_V

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

View Text

SELECT /*+ ORDERED */ MSI.ORGANIZATION_CODE
, MIC.CATEGORY_NAME
, MSI.ITEM_NAME
, SUP.PLANNING_GROUP
, SUP.PROJECT_ID
, SUP.TASK_ID
, L1.MEANING
, DECODE(SUP.ORDER_TYPE
, 5
, TO_CHAR(SUP.TRANSACTION_ID)
, SUP.ORDER_NUMBER)
, CAL.CALENDAR_DATE
, DECODE (MSC_GET_NAME.ACTION( 'MSC_SUPPLIES'
, MSI.BOM_ITEM_TYPE
, MSI.BASE_ITEM_ID
, MSI.WIP_SUPPLY_TYPE
, SUP.ORDER_TYPE
, DECODE(SUP.FIRM_PLANNED_TYPE
, 1
, 1
, SUP.RESCHEDULE_FLAG)
, SUP.DISPOSITION_STATUS_TYPE
, SUP.NEW_SCHEDULE_DATE
, SUP.OLD_SCHEDULE_DATE
, SUP.IMPLEMENTED_QUANTITY
, SUP.QUANTITY_IN_PROCESS
, DECODE( SUP.NEW_ORDER_QUANTITY
, 0
, SUP.FIRM_QUANTITY
, SUP.NEW_ORDER_QUANTITY ))
, 'NONE'
, DECODE(SIGN(SUP.NEW_SCHEDULE_DATE -SUP.OLD_SCHEDULE_DATE)
, 1
, MSC_GET_NAME.LOOKUP_MEANING( 'MRP_ACTIONS'
, 3)
, -1
, MSC_GET_NAME.LOOKUP_MEANING('MRP_ACTIONS'
, 2)
, 'NONE')
, MSC_GET_NAME.ACTION('MSC_SUPPLIES'
, MSI.BOM_ITEM_TYPE
, MSI.BASE_ITEM_ID
, MSI.WIP_SUPPLY_TYPE
, SUP.ORDER_TYPE
, DECODE( SUP.FIRM_PLANNED_TYPE
, 1
, 1
, SUP.RESCHEDULE_FLAG)
, SUP.DISPOSITION_STATUS_TYPE
, SUP.NEW_SCHEDULE_DATE
, SUP.OLD_SCHEDULE_DATE
, SUP.IMPLEMENTED_QUANTITY
, SUP.QUANTITY_IN_PROCESS
, DECODE(SUP.NEW_ORDER_QUANTITY
, 0
, SUP.FIRM_QUANTITY
, SUP.NEW_ORDER_QUANTITY)))
, NVL(SUP.DAILY_RATE
, SUP.NEW_ORDER_QUANTITY)
, SUP.OLD_SCHEDULE_DATE
, SUP.NEW_ORDER_PLACEMENT_DATE
, SUP.NEW_SCHEDULE_DATE
, SUP.NEW_DOCK_DATE
, SUP.NEW_WIP_START_DATE
, SUP.IMPLEMENT_AS
, SUP.FIRM_DATE
, SUP.FIRM_QUANTITY
, NVL(SUP.DAILY_RATE
, SUP.NEW_ORDER_QUANTITY) - NVL(SUP.QUANTITY_IN_PROCESS
, 0)
, SUP.SCHEDULE_COMPRESS_DAYS
, TO_CHAR(NULL)
, MSC_GET_NAME.DESIGNATOR (SUP.SCHEDULE_DESIGNATOR_ID)
, MSC_GET_NAME.DESIGNATOR( SUP.SCHEDULE_DESIGNATOR_ID)
, MSC_GET_NAME.ORG_CODE( SUP.SOURCE_ORGANIZATION_ID
, SUP.SOURCE_SR_INSTANCE_ID)
, MSC_GET_NAME.SUPPLIER(DECODE(SUP.PLAN_ID
, -1
, SUP.SUPPLIER_ID
, DECODE(SUP.ORDER_TYPE
, 1
, SUP.SUPPLIER_ID
, 2
, SUP.SUPPLIER_ID
, SUP.SOURCE_SUPPLIER_ID)))
, MP.COMPILE_DESIGNATOR
, TRUNC(SYSDATE)
, SUP.PLAN_ID
, SUP.ORGANIZATION_ID
, SUP.SR_INSTANCE_ID
, MIC.CATEGORY_SET_ID
, MIC.SR_CATEGORY_ID
, MSI.ABC_CLASS
, SUP.INVENTORY_ITEM_ID
, MSI.PLANNER_CODE
, MSI.BUYER_NAME
, SUP.SUPPLIER_ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, DECODE( SUP.IMPLEMENT_AS
, NULL
, NULL
, MSC_GET_NAME.LOOKUP_MEANING( 'MRP_WORKBENCH_IMPLEMENT_AS'
, SUP.IMPLEMENT_AS))
, DECODE( SUP.PROJECT_ID
, NULL
, NULL
, MSC_GET_NAME.PROJECT(SUP.PROJECT_ID
, SUP.ORGANIZATION_ID
, SUP.PLAN_ID
, SUP.SR_INSTANCE_ID))
, DECODE( SUP.TASK_ID
, NULL
, NULL
, MSC_GET_NAME.TASK(SUP.TASK_ID
, SUP.PROJECT_ID
, SUP.ORGANIZATION_ID
, SUP.PLAN_ID
, SUP.SR_INSTANCE_ID))
, DECODE(SUP.DISPOSITION_STATUS_TYPE
, 2
, 0.0
, NVL (SUP.DAILY_RATE
, SUP.NEW_ORDER_QUANTITY))
FROM MSC_ITEM_CATEGORIES MIC
, MSC_SYSTEM_ITEMS MSI
, MSC_SUPPLIES SUP
, MSC_TRADING_PARTNERS MTP
, MSC_CALENDAR_DATES CAL
, MFG_LOOKUPS L1
, MSC_PLANS MP
WHERE CAL.CALENDAR_DATE BETWEEN TRUNC(SUP.NEW_SCHEDULE_DATE)
AND NVL(TRUNC(SUP.LAST_UNIT_COMPLETION_DATE)
, TRUNC( SUP.NEW_SCHEDULE_DATE))
AND DECODE(SUP.LAST_UNIT_COMPLETION_DATE
, NULL
, 1
, CAL.SEQ_NUM) IS NOT NULL
AND CAL.EXCEPTION_SET_ID = MTP.CALENDAR_EXCEPTION_SET_ID
AND CAL.CALENDAR_CODE = MTP.CALENDAR_CODE
AND CAL.SR_INSTANCE_ID =MTP.SR_INSTANCE_ID
AND MTP.SR_TP_ID = SUP.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID = SUP.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE = 3
AND SUP.PLAN_ID = MSI.PLAN_ID
AND SUP.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND SUP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND SUP.INVENTORY_ITEM_ID =MSI.INVENTORY_ITEM_ID
AND NVL(SUP.DAILY_RATE
, SUP.NEW_ORDER_QUANTITY) <> 0
AND L1.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
AND L1.LOOKUP_CODE = SUP.ORDER_TYPE
AND MP.PLAN_ID= SUP.PLAN_ID
AND MSI.SR_INSTANCE_ID =MIC.SR_INSTANCE_ID
AND MSI.ORGANIZATION_ID = MIC.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND MSI.PLAN_ID =(SELECT PLAN_ID
FROM MSC_PDR_PARAMETERS
WHERE USER_ID =FND_GLOBAL.USER_ID
AND PLAN_ID IS NOT NULL)
AND ( (NVL(SUP.ORGANIZATION_ID
, -1)
, NVL(SUP.SR_INSTANCE_ID
, -1)) IN ( SELECT NVL(ORGANIZATION_ID
, NVL(SUP.ORGANIZATION_ID
, -1))
, NVL( SR_INSTANCE_ID
, NVL(SUP.SR_INSTANCE_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID =FND_GLOBAL.USER_ID
AND NVL(ORGANIZATION_ID
, -2) <> -1 ) )
AND MIC.CATEGORY_SET_ID = (SELECT CATEGORY_SET_ID
FROM MSC_PDR_PARAMETERS
WHERE USER_ID =FND_GLOBAL.USER_ID
AND CATEGORY_SET_ID <> -1)
AND (NVL(MIC.SR_CATEGORY_ID
, -1) IN ( SELECT NVL(PRODUCT_CATEGORY_ID
, NVL(MIC.SR_CATEGORY_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID =FND_GLOBAL.USER_ID
AND NVL(PRODUCT_CATEGORY_ID
, -2) <> -1))
AND (NVL(MSI.ABC_CLASS
, -1) IN ( SELECT NVL(ABC_CLASS_ID
, NVL(MSI.ABC_CLASS
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(ABC_CLASS_ID
, -2) <> -1))
AND (NVL(SUP.INVENTORY_ITEM_ID
, -1) IN ( SELECT NVL(INVENTORY_ITEM_ID
, NVL(SUP.INVENTORY_ITEM_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(INVENTORY_ITEM_ID
, -2) <> -1))
AND (NVL(MSI.PLANNER_CODE
, '-1') IN ( SELECT NVL(PLANNER_CODE_ID
, NVL(MSI.PLANNER_CODE
, '-1'))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(PLANNER_CODE_ID
, '-2') <> '-1'))
AND (NVL(MSI.BUYER_NAME
, '-1') IN ( SELECT NVL(BUYER_NAME_ID
, NVL(MSI.BUYER_NAME
, '-1'))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(BUYER_NAME_ID
, ' -2') <> '-1'))
AND (NVL(SUP.SUPPLIER_ID
, -1) IN ( SELECT NVL(SUPPLIER_ID
, NVL(SUP.SUPPLIER_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(SUPPLIER_ID
, -2) <> -1))
AND (NVL(SUP.PLANNING_GROUP
, '-1') IN ( SELECT NVL(PLANNING_GROUP_ID
, NVL(SUP.PLANNING_GROUP
, '-1'))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID =FND_GLOBAL.USER_ID
AND NVL(PLANNING_GROUP_ID
, '-2') <> '-1'))
AND (NVL(SUP.PROJECT_ID
, -1) IN ( SELECT NVL( PROJECT_ID
, NVL(SUP.PROJECT_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID =FND_GLOBAL.USER_ID
AND NVL(PROJECT_ID
, -2) <> -1))
AND (TRUNC(CAL.CALENDAR_DATE) >= ( SELECT TRUNC(NVL(MPP.REPORT_PERIOD_FROM
, TRUNC(CAL.CALENDAR_DATE)) )
FROM MSC_PDR_PARAMETERS MPP
WHERE MPP.USER_ID = FND_GLOBAL.USER_ID
AND MPP.REPORT_DATE_REC = 1) OR SUP.DISPOSITION_STATUS_TYPE =2)
AND TRUNC(CAL.CALENDAR_DATE) <= (SELECT TRUNC(NVL(MPP.REPORT_PERIOD_TO
, TRUNC(CAL.CALENDAR_DATE)))
FROM MSC_PDR_PARAMETERS MPP
WHERE MPP.USER_ID =FND_GLOBAL.USER_ID
AND MPP.REPORT_DATE_REC = 1) UNION ALL SELECT /*+ ORDERED MIC MSI DEM MTP CAL*/ MSI.ORGANIZATION_CODE
, MIC.CATEGORY_NAME
, MSI.ITEM_NAME
, DEM.PLANNING_GROUP
, DEM.PROJECT_ID
, DEM.TASK_ID
, L1.MEANING
, NVL( DEM.ORDER_NUMBER
, DECODE(DEM.ORIGINATION_TYPE
, 29
, MSC_GET_NAME.SCENARIO_DESIGNATOR(DEM.FORECAST_SET_ID
, DEM.PLAN_ID
, DEM.ORGANIZATION_ID
, DEM.SR_INSTANCE_ID)
, MSC_GET_NAME.DESIGNATOR( DEM.SCHEDULE_DESIGNATOR_ID)))
, CAL.CALENDAR_DATE
, MSC_GET_NAME.LOOKUP_MEANING('MRP_ACTIONS'
, 6)
, - NVL( DEM.DAILY_DEMAND_RATE
, DEM.USING_REQUIREMENT_QUANTITY)
, DEM.OLD_DEMAND_DATE
, TO_DATE(NULL)
, DEM.USING_ASSEMBLY_DEMAND_DATE
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, DEM.FIRM_DATE
, DEM.FIRM_QUANTITY
, - NVL( DEM.DAILY_DEMAND_RATE
, DEM.USING_REQUIREMENT_QUANTITY) - TO_NUMBER (NULL)
, TO_NUMBER(NULL)
, MSC_GET_NAME.ITEM_NAME( DEM.USING_ASSEMBLY_ITEM_ID
, NULL
, NULL
, NULL)
, DECODE( DEM.SCHEDULE_DESIGNATOR_ID
, NULL
, NULL
, DECODE(DEM.ORIGINATION_TYPE
, 29
, MSC_GET_NAME.FORECASTSETNAME(DEM.FORECAST_SET_ID
, DEM.PLAN_ID
, DEM.ORGANIZATION_ID
, DEM.SR_INSTANCE_ID)
, MSC_GET_NAME.DESIGNATOR( DEM.SCHEDULE_DESIGNATOR_ID) ))
, DECODE(DEM.SCHEDULE_DESIGNATOR_ID
, NULL
, NULL
, DECODE(DEM.ORIGINATION_TYPE
, 29
, MSC_GET_NAME.FORECASTSETNAME(DEM.FORECAST_SET_ID
, DEM.PLAN_ID
, DEM.ORGANIZATION_ID
, DEM.SR_INSTANCE_ID)
, MSC_GET_NAME.DESIGNATOR( DEM.SCHEDULE_DESIGNATOR_ID) ))
, MSC_GET_NAME.ORG_CODE( DEM.SOURCE_ORGANIZATION_ID
, DEM.SOURCE_ORG_INSTANCE_ID)
, NULL
, MP.COMPILE_DESIGNATOR
, TRUNC(SYSDATE)
, DEM.PLAN_ID
, DEM.ORGANIZATION_ID
, DEM.SR_INSTANCE_ID
, MIC.CATEGORY_SET_ID
, MIC.SR_CATEGORY_ID
, MSI.ABC_CLASS
, DEM.INVENTORY_ITEM_ID
, MSI.PLANNER_CODE
, MSI.BUYER_NAME
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_CHAR(NULL)
, DECODE(DEM.PROJECT_ID
, NULL
, NULL
, MSC_GET_NAME.PROJECT(DEM.PROJECT_ID
, DEM.ORGANIZATION_ID
, DEM.PLAN_ID
, DEM.SR_INSTANCE_ID))
, DECODE(DEM.TASK_ID
, NULL
, NULL
, MSC_GET_NAME.TASK(DEM.TASK_ID
, DEM.PROJECT_ID
, DEM.ORGANIZATION_ID
, DEM.PLAN_ID
, DEM.SR_INSTANCE_ID))
, - NVL(DEM.DAILY_DEMAND_RATE
, DEM.USING_REQUIREMENT_QUANTITY)
FROM MSC_ITEM_CATEGORIES MIC
, MSC_SYSTEM_ITEMS MSI
, MSC_DEMANDS DEM
, MSC_TRADING_PARTNERS MTP
, MSC_CALENDAR_DATES CAL
, MFG_LOOKUPS L1
, MSC_PLANS MP
WHERE CAL.CALENDAR_DATE BETWEEN TRUNC(DEM.USING_ASSEMBLY_DEMAND_DATE)
AND NVL(TRUNC(DEM.ASSEMBLY_DEMAND_COMP_DATE)
, TRUNC( DEM.USING_ASSEMBLY_DEMAND_DATE))
AND DECODE(DEM.ASSEMBLY_DEMAND_COMP_DATE
, NULL
, 1
, CAL.SEQ_NUM) IS NOT NULL
AND CAL.EXCEPTION_SET_ID =MTP.CALENDAR_EXCEPTION_SET_ID
AND CAL.CALENDAR_CODE = MTP.CALENDAR_CODE
AND CAL.SR_INSTANCE_ID = MTP.SR_INSTANCE_ID
AND MTP.SR_TP_ID =DEM.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID = DEM.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE = 3
AND DEM.PLAN_ID = MSI.PLAN_ID
AND DEM.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND DEM.ORGANIZATION_ID =MSI.ORGANIZATION_ID
AND DEM.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND NVL(DEM.DAILY_DEMAND_RATE
, DEM.USING_REQUIREMENT_QUANTITY) <> 0
AND L1.LOOKUP_TYPE = 'MSC_DEMAND_ORIGINATION'
AND L1.LOOKUP_CODE =DEM.ORIGINATION_TYPE
AND MP.PLAN_ID= DEM.PLAN_ID
AND MSI.SR_INSTANCE_ID = MIC.SR_INSTANCE_ID
AND MSI.ORGANIZATION_ID = MIC.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID =MIC.INVENTORY_ITEM_ID
AND MSI.PLAN_ID = (SELECT PLAN_ID
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND PLAN_ID IS NOT NULL)
AND ((NVL(DEM.ORGANIZATION_ID
, -1)
, NVL(DEM.SR_INSTANCE_ID
, -1)) IN ( SELECT NVL(ORGANIZATION_ID
, NVL(DEM.ORGANIZATION_ID
, -1))
, NVL( SR_INSTANCE_ID
, NVL(DEM.SR_INSTANCE_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID =FND_GLOBAL.USER_ID
AND NVL(ORGANIZATION_ID
, -2) <> -1 ) )
AND MIC.CATEGORY_SET_ID = (SELECT CATEGORY_SET_ID
FROM MSC_PDR_PARAMETERS
WHERE USER_ID =FND_GLOBAL.USER_ID
AND CATEGORY_SET_ID <> -1)
AND (NVL(MIC.SR_CATEGORY_ID
, -1) IN ( SELECT NVL(PRODUCT_CATEGORY_ID
, NVL(MIC.SR_CATEGORY_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID =FND_GLOBAL.USER_ID
AND NVL(PRODUCT_CATEGORY_ID
, -2) <> -1))
AND (NVL(MSI.ABC_CLASS
, -1) IN ( SELECT NVL(ABC_CLASS_ID
, NVL(MSI.ABC_CLASS
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(ABC_CLASS_ID
, -2) <> -1))
AND (NVL(DEM.INVENTORY_ITEM_ID
, -1) IN ( SELECT NVL(INVENTORY_ITEM_ID
, NVL(DEM.INVENTORY_ITEM_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(INVENTORY_ITEM_ID
, -2) <> -1))
AND (NVL(MSI.PLANNER_CODE
, '-1') IN ( SELECT NVL(PLANNER_CODE_ID
, NVL(MSI.PLANNER_CODE
, '-1'))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(PLANNER_CODE_ID
, '-2') <> '-1'))
AND (NVL(MSI.BUYER_NAME
, '-1') IN ( SELECT NVL(BUYER_NAME_ID
, NVL(MSI.BUYER_NAME
, '-1'))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(BUYER_NAME_ID
, ' -2') <> '-1'))
AND (NVL(DEM.PLANNING_GROUP
, '-1') IN ( SELECT NVL(PLANNING_GROUP_ID
, NVL(DEM.PLANNING_GROUP
, '-1'))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(PLANNING_GROUP_ID
, '-2') <> '-1'))
AND (NVL(DEM.PROJECT_ID
, -1) IN ( SELECT NVL( PROJECT_ID
, NVL(DEM.PROJECT_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(PROJECT_ID
, -2) <> -1))
AND NOT EXISTS ( SELECT *
FROM MSC_PDR_PARAMETERS
WHERE USER_ID =FND_GLOBAL.USER_ID
AND SUPPLIER_ID <> -1
AND SUPPLIER_ID IS NOT NULL)
AND TRUNC(CAL.CALENDAR_DATE) >= (SELECT TRUNC(NVL(MPP.REPORT_PERIOD_FROM
, TRUNC(CAL.CALENDAR_DATE)))
FROM MSC_PDR_PARAMETERS MPP
WHERE MPP.USER_ID =FND_GLOBAL.USER_ID
AND MPP.REPORT_DATE_REC =1)
AND TRUNC(CAL.CALENDAR_DATE) <=(SELECT TRUNC(NVL(MPP.REPORT_PERIOD_TO
, TRUNC(CAL.CALENDAR_DATE)))
FROM MSC_PDR_PARAMETERS MPP
WHERE MPP.USER_ID =FND_GLOBAL.USER_ID
AND MPP.REPORT_DATE_REC = 1)

Columns