VIEW: APPS.MSC_VERTICAL_PLAN_V

Source

View Text - Preformatted

SELECT sup.transaction_id, sup.inventory_item_id, sup.organization_id, msi.organization_code, sup.plan_id, sup.sr_instance_id, cal.calendar_date, msc_get_name.supply_order_number ( decode(sup.order_type,92,70,sup.order_type) ,sup.order_number ,sup.plan_id ,sup.sr_instance_id ,sup.transaction_id ,sup.disposition_id ) , decode(sup.order_type,92,70,sup.order_type), msc_get_name.lookup_meaning('MRP_ORDER_TYPE',decode(sup.order_type,92,70,sup.order_type)), NVL(sup.daily_rate,sup.new_order_quantity), msi.item_name, msi.description, TO_NUMBER(NULL), NULL, sup.planning_group, sup.project_id, sup.task_id, DECODE(sup.order_type,18,1,2), 1, to_number(null), to_number(null), NVL(sup.item_type_value,1), NVL(sup.produces_to_stock,2), msc_get_name.lookup_meaning('MSC_PART_CONDITION',nvl(sup.item_type_value,1)) FROM msc_calendar_dates cal, msc_trading_partners mtp, msc_system_items msi , MSC_SUPPLIES SUP WHERE cal.calendar_date BETWEEN TRUNC(sup.new_schedule_date) AND NVL(TRUNC(sup.last_unit_completion_date), TRUNC(sup.new_schedule_date)) AND mtp.calendar_exception_set_id = cal.exception_set_id AND mtp.calendar_code = cal.calendar_code AND mtp.sr_instance_id = cal.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 ((sup.disposition_status_type <> 2 or sup.disposition_status_type is null) OR ((sup.disposition_status_type = 2 OR sup.order_type=74) AND (sup.order_type=74 OR msi.base_item_id IS NOT NULL OR msi.wip_supply_type = 6 OR sup.order_type in (14,15,16,17,18,19)))) UNION ALL SELECT dem.demand_id, dem.inventory_item_id, dem.organization_id, msi.organization_code, dem.plan_id, dem.sr_instance_id, cal.calendar_date, NVL(dem.order_number, decode(dem.origination_type,1, to_char(dem.disposition_id), 3, msc_get_name.job_name(dem.disposition_id, dem.plan_id, dem.sr_instance_id), 22, to_char(dem.disposition_id), 50, msc_get_name.maintenance_plan(dem.schedule_designator_id), 70, msc_get_name.maintenance_plan(dem.schedule_designator_id), 92, msc_get_name.maintenance_plan(dem.schedule_designator_id), 29,decode(dem.plan_id, -11, msc_get_name.designator(dem.schedule_designator_id) , decode(msi.in_source_plan,1,msc_get_name.designator(dem.schedule_designator_id, dem.forecast_set_id ), msc_get_name.scenario_designator(dem.forecast_set_id, dem.plan_id, dem.organization_id, dem.sr_instance_id) || decode(msc_get_name.designator(dem.schedule_designator_id,dem.forecast_set_id ), null, null, '/'||msc_get_name.designator(dem.schedule_designator_id,dem.forecast_set_id )))), 78, to_char(dem.disposition_id), msc_get_name.designator(dem.schedule_designator_id))), Decode(dem.origination_type,70,50,92,50,dem.origination_type), msc_get_name.lookup_meaning(DECODE(dem.origination_type, 1, 'MRP_PLANNED_ORDER_DEMAND', 3 , 'MRP_PLANNED_ORDER_DEMAND', 25, 'MRP_PLANNED_ORDER_DEMAND', 'MSC_DEMAND_ORIGINATION'), Decode(dem.origination_type,70,50,92,50,dem.origination_type)), -(nvl(dem.daily_demand_rate, dem.using_requirement_quantity))*decode(dem.probability,null,1,dem.probability), msi.item_name, msi.description, TO_NUMBER(NULL), NULL, dem.planning_group, dem.project_id, dem.task_id, 2, 2, dem.probability, -(nvl(dem.daily_demand_rate, dem.using_requirement_quantity)), nvl(item_type_value,1), 2, msc_get_name.lookup_meaning('MSC_PART_CONDITION',nvl(item_type_value,1)) FROM msc_calendar_dates cal, msc_trading_partners mtp, msc_system_items msi , msc_demands dem 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 cal.calendar_code = mtp.calendar_code AND cal.exception_set_id = mtp.calendar_exception_set_id AND cal.sr_instance_id = mtp.sr_instance_id AND mtp.sr_instance_id = dem.sr_instance_id AND mtp.sr_tp_id = dem.organization_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 dem.origination_type > 0 AND not exists ( select 'cancelled IR' from msc_supplies sup where dem.origination_type in (6,30) and dem.disposition_id = sup.transaction_id and dem.plan_id = sup.plan_id and dem.sr_instance_id = sup.sr_instance_id and sup.disposition_status_type = 2 ) UNION ALL SELECT dem.demand_id, dem.inventory_item_id, dem.organization_id, msi.organization_code, -1, dem.sr_instance_id, cal.calendar_date, dem.sales_order_number, 30, msc_get_name.lookup_meaning('MSC_DEMAND_ORIGINATION',30), -(dem.primary_uom_quantity), msi.item_name, msi.description, TO_NUMBER(NULL), NULL, dem.planning_group, dem.project_id, dem.task_id, 2, 2, to_number(null), -(dem.primary_uom_quantity), 1, 2, msc_get_name.lookup_meaning('MSC_PART_CONDITION',1) FROM msc_calendar_dates cal, msc_trading_partners mtp, msc_system_items msi , msc_sales_orders dem WHERE cal.calendar_date BETWEEN TRUNC(dem.requirement_date) and TRUNC(dem.requirement_date) AND cal.calendar_code = mtp.calendar_code AND cal.exception_set_id = mtp.calendar_exception_set_id AND cal.sr_instance_id = mtp.sr_instance_id AND mtp.sr_instance_id = dem.sr_instance_id AND mtp.sr_tp_id = dem.organization_id AND mtp.partner_type = 3 AND msi.plan_id = -1 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

View Text - HTML Formatted

SELECT SUP.TRANSACTION_ID
, SUP.INVENTORY_ITEM_ID
, SUP.ORGANIZATION_ID
, MSI.ORGANIZATION_CODE
, SUP.PLAN_ID
, SUP.SR_INSTANCE_ID
, CAL.CALENDAR_DATE
, MSC_GET_NAME.SUPPLY_ORDER_NUMBER ( DECODE(SUP.ORDER_TYPE
, 92
, 70
, SUP.ORDER_TYPE)
, SUP.ORDER_NUMBER
, SUP.PLAN_ID
, SUP.SR_INSTANCE_ID
, SUP.TRANSACTION_ID
, SUP.DISPOSITION_ID )
, DECODE(SUP.ORDER_TYPE
, 92
, 70
, SUP.ORDER_TYPE)
, MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE'
, DECODE(SUP.ORDER_TYPE
, 92
, 70
, SUP.ORDER_TYPE))
, NVL(SUP.DAILY_RATE
, SUP.NEW_ORDER_QUANTITY)
, MSI.ITEM_NAME
, MSI.DESCRIPTION
, TO_NUMBER(NULL)
, NULL
, SUP.PLANNING_GROUP
, SUP.PROJECT_ID
, SUP.TASK_ID
, DECODE(SUP.ORDER_TYPE
, 18
, 1
, 2)
, 1
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NVL(SUP.ITEM_TYPE_VALUE
, 1)
, NVL(SUP.PRODUCES_TO_STOCK
, 2)
, MSC_GET_NAME.LOOKUP_MEANING('MSC_PART_CONDITION'
, NVL(SUP.ITEM_TYPE_VALUE
, 1))
FROM MSC_CALENDAR_DATES CAL
, MSC_TRADING_PARTNERS MTP
, MSC_SYSTEM_ITEMS MSI
, MSC_SUPPLIES SUP
WHERE CAL.CALENDAR_DATE BETWEEN TRUNC(SUP.NEW_SCHEDULE_DATE)
AND NVL(TRUNC(SUP.LAST_UNIT_COMPLETION_DATE)
, TRUNC(SUP.NEW_SCHEDULE_DATE))
AND MTP.CALENDAR_EXCEPTION_SET_ID = CAL.EXCEPTION_SET_ID
AND MTP.CALENDAR_CODE = CAL.CALENDAR_CODE
AND MTP.SR_INSTANCE_ID = CAL.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 ((SUP.DISPOSITION_STATUS_TYPE <> 2 OR SUP.DISPOSITION_STATUS_TYPE IS NULL) OR ((SUP.DISPOSITION_STATUS_TYPE = 2 OR SUP.ORDER_TYPE=74)
AND (SUP.ORDER_TYPE=74 OR MSI.BASE_ITEM_ID IS NOT NULL OR MSI.WIP_SUPPLY_TYPE = 6 OR SUP.ORDER_TYPE IN (14
, 15
, 16
, 17
, 18
, 19)))) UNION ALL SELECT DEM.DEMAND_ID
, DEM.INVENTORY_ITEM_ID
, DEM.ORGANIZATION_ID
, MSI.ORGANIZATION_CODE
, DEM.PLAN_ID
, DEM.SR_INSTANCE_ID
, CAL.CALENDAR_DATE
, NVL(DEM.ORDER_NUMBER
, DECODE(DEM.ORIGINATION_TYPE
, 1
, TO_CHAR(DEM.DISPOSITION_ID)
, 3
, MSC_GET_NAME.JOB_NAME(DEM.DISPOSITION_ID
, DEM.PLAN_ID
, DEM.SR_INSTANCE_ID)
, 22
, TO_CHAR(DEM.DISPOSITION_ID)
, 50
, MSC_GET_NAME.MAINTENANCE_PLAN(DEM.SCHEDULE_DESIGNATOR_ID)
, 70
, MSC_GET_NAME.MAINTENANCE_PLAN(DEM.SCHEDULE_DESIGNATOR_ID)
, 92
, MSC_GET_NAME.MAINTENANCE_PLAN(DEM.SCHEDULE_DESIGNATOR_ID)
, 29
, DECODE(DEM.PLAN_ID
, -11
, MSC_GET_NAME.DESIGNATOR(DEM.SCHEDULE_DESIGNATOR_ID)
, DECODE(MSI.IN_SOURCE_PLAN
, 1
, MSC_GET_NAME.DESIGNATOR(DEM.SCHEDULE_DESIGNATOR_ID
, DEM.FORECAST_SET_ID )
, MSC_GET_NAME.SCENARIO_DESIGNATOR(DEM.FORECAST_SET_ID
, DEM.PLAN_ID
, DEM.ORGANIZATION_ID
, DEM.SR_INSTANCE_ID) || DECODE(MSC_GET_NAME.DESIGNATOR(DEM.SCHEDULE_DESIGNATOR_ID
, DEM.FORECAST_SET_ID )
, NULL
, NULL
, '/'||MSC_GET_NAME.DESIGNATOR(DEM.SCHEDULE_DESIGNATOR_ID
, DEM.FORECAST_SET_ID ))))
, 78
, TO_CHAR(DEM.DISPOSITION_ID)
, MSC_GET_NAME.DESIGNATOR(DEM.SCHEDULE_DESIGNATOR_ID)))
, DECODE(DEM.ORIGINATION_TYPE
, 70
, 50
, 92
, 50
, DEM.ORIGINATION_TYPE)
, MSC_GET_NAME.LOOKUP_MEANING(DECODE(DEM.ORIGINATION_TYPE
, 1
, 'MRP_PLANNED_ORDER_DEMAND'
, 3
, 'MRP_PLANNED_ORDER_DEMAND'
, 25
, 'MRP_PLANNED_ORDER_DEMAND'
, 'MSC_DEMAND_ORIGINATION')
, DECODE(DEM.ORIGINATION_TYPE
, 70
, 50
, 92
, 50
, DEM.ORIGINATION_TYPE))
, -(NVL(DEM.DAILY_DEMAND_RATE
, DEM.USING_REQUIREMENT_QUANTITY))*DECODE(DEM.PROBABILITY
, NULL
, 1
, DEM.PROBABILITY)
, MSI.ITEM_NAME
, MSI.DESCRIPTION
, TO_NUMBER(NULL)
, NULL
, DEM.PLANNING_GROUP
, DEM.PROJECT_ID
, DEM.TASK_ID
, 2
, 2
, DEM.PROBABILITY
, -(NVL(DEM.DAILY_DEMAND_RATE
, DEM.USING_REQUIREMENT_QUANTITY))
, NVL(ITEM_TYPE_VALUE
, 1)
, 2
, MSC_GET_NAME.LOOKUP_MEANING('MSC_PART_CONDITION'
, NVL(ITEM_TYPE_VALUE
, 1))
FROM MSC_CALENDAR_DATES CAL
, MSC_TRADING_PARTNERS MTP
, MSC_SYSTEM_ITEMS MSI
, MSC_DEMANDS DEM
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 CAL.CALENDAR_CODE = MTP.CALENDAR_CODE
AND CAL.EXCEPTION_SET_ID = MTP.CALENDAR_EXCEPTION_SET_ID
AND CAL.SR_INSTANCE_ID = MTP.SR_INSTANCE_ID
AND MTP.SR_INSTANCE_ID = DEM.SR_INSTANCE_ID
AND MTP.SR_TP_ID = DEM.ORGANIZATION_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 DEM.ORIGINATION_TYPE > 0
AND NOT EXISTS ( SELECT 'CANCELLED IR'
FROM MSC_SUPPLIES SUP
WHERE DEM.ORIGINATION_TYPE IN (6
, 30)
AND DEM.DISPOSITION_ID = SUP.TRANSACTION_ID
AND DEM.PLAN_ID = SUP.PLAN_ID
AND DEM.SR_INSTANCE_ID = SUP.SR_INSTANCE_ID
AND SUP.DISPOSITION_STATUS_TYPE = 2 ) UNION ALL SELECT DEM.DEMAND_ID
, DEM.INVENTORY_ITEM_ID
, DEM.ORGANIZATION_ID
, MSI.ORGANIZATION_CODE
, -1
, DEM.SR_INSTANCE_ID
, CAL.CALENDAR_DATE
, DEM.SALES_ORDER_NUMBER
, 30
, MSC_GET_NAME.LOOKUP_MEANING('MSC_DEMAND_ORIGINATION'
, 30)
, -(DEM.PRIMARY_UOM_QUANTITY)
, MSI.ITEM_NAME
, MSI.DESCRIPTION
, TO_NUMBER(NULL)
, NULL
, DEM.PLANNING_GROUP
, DEM.PROJECT_ID
, DEM.TASK_ID
, 2
, 2
, TO_NUMBER(NULL)
, -(DEM.PRIMARY_UOM_QUANTITY)
, 1
, 2
, MSC_GET_NAME.LOOKUP_MEANING('MSC_PART_CONDITION'
, 1)
FROM MSC_CALENDAR_DATES CAL
, MSC_TRADING_PARTNERS MTP
, MSC_SYSTEM_ITEMS MSI
, MSC_SALES_ORDERS DEM
WHERE CAL.CALENDAR_DATE BETWEEN TRUNC(DEM.REQUIREMENT_DATE)
AND TRUNC(DEM.REQUIREMENT_DATE)
AND CAL.CALENDAR_CODE = MTP.CALENDAR_CODE
AND CAL.EXCEPTION_SET_ID = MTP.CALENDAR_EXCEPTION_SET_ID
AND CAL.SR_INSTANCE_ID = MTP.SR_INSTANCE_ID
AND MTP.SR_INSTANCE_ID = DEM.SR_INSTANCE_ID
AND MTP.SR_TP_ID = DEM.ORGANIZATION_ID
AND MTP.PARTNER_TYPE = 3
AND MSI.PLAN_ID = -1
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