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, DECODE(sup.ORDER_TYPE, 1, sup.order_number, 2, sup.order_number, 3, sup.order_number, 7, sup.order_number, 8, sup.order_number, 11, sup.order_number, 12, sup.order_number, 14, sup.WIP_ENTITY_NAME, 15, sup.WIP_ENTITY_NAME, 27, sup.WIP_ENTITY_NAME, 28, sup.WIP_ENTITY_NAME, 5, to_char(sup.transaction_id),NULL), sup.order_type, msc_get_name.lookup_meaning('MRP_ORDER_TYPE',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) 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 AND (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), 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))), 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', 'MRP_DEMAND_ORIGINATION'), 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)) 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('MRP_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) 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
, DECODE(SUP.ORDER_TYPE
, 1
, SUP.ORDER_NUMBER
, 2
, SUP.ORDER_NUMBER
, 3
, SUP.ORDER_NUMBER
, 7
, SUP.ORDER_NUMBER
, 8
, SUP.ORDER_NUMBER
, 11
, SUP.ORDER_NUMBER
, 12
, SUP.ORDER_NUMBER
, 14
, SUP.WIP_ENTITY_NAME
, 15
, SUP.WIP_ENTITY_NAME
, 27
, SUP.WIP_ENTITY_NAME
, 28
, SUP.WIP_ENTITY_NAME
, 5
, TO_CHAR(SUP.TRANSACTION_ID)
, NULL)
, SUP.ORDER_TYPE
, MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE'
, 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)
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
AND (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)
, 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)))
, 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'
, 'MRP_DEMAND_ORIGINATION')
, 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))
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('MRP_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)
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