VIEW: APPS.MSC_PDR_VERT_LIST_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)