MSC_ANALYTIC_FACT_ORD_V

Details

• Schema: FUSION

• Object owner: MSC

• Object type: VIEW

Columns

Name

PLAN_ID BASE_PLAN_ID ORDER_ID INVENTORY_ITEM_ID ORGANIZATION_ID ORDER_TYPE ORDER_TYPE_TEXT CUSTOMER_ID CUSTOMER_SITE_ID SUPPLIER_ID SUPPLIER_SITE_ID ORDER_DATE ORDER_QUANTITY IMPLEMENTED_SUPPLY_QUANTITY ORDER_VALUE ORDER_NUMBER LINE_NUMBER UOM SUGGESTED_DUE_DATE FIRM_STATUS FIRM_DATE FIRM_QUANTITY RELEASE_STATUS ACTION RECOMMENDATION_TYPE SUGGESTED_SHIP_DATE SUGGESTED_START_DATE SUGGESTED_DOCK_DATE SHIPPING_METHOD NEED_BY_DATE SOURCE_ORGANIZATION IMPLEMENT_QUANTITY IMPLEMENT_ITEM_STRUCTURE_NAME IMPLEMENT_WORK_DEFINITION_NAME IMPLEMENT_ORDER_TYPE IMPLEMENT_DATE IMPLEMENT_FIRM IMPLEMENT_STATUS IMPLEMENT_SUPPLIER IMPLEMENT_SUPPLIER_SITE IMPLEMENT_SHIPPING_METHOD IMPLEMENT_SHIP_DATE IMPLEMENT_DOCK_DATE IMPLEMENT_WORK_ORDER_SUBTYPE IMPLEMENT_SOURCE_ORGANIZATION IMPLEMENTED_QUANTITY RELEASE_ERRORS ACTUAL_START_DATE ITEM_STRUCTURE_NAME WORK_DEFINITION_NAME ARRIVAL_SET_NAME BUCKET_TYPE BUYER_MANAGED_TRANSPORTATION CLEAR_TO_BUILD CLEAR_TO_BLD_COMP_AVBLTY CLEAR_TO_BUILD_HORIZON COMPRESSION_DAYS DAYS_LATE DEMAND_CLASS DESTINATION_TYPE END_DEMAND_VALUE EXPIRATION_DATE FULFILLMENT_LINE TRANSIT_LEAD_TIME LATEST_ACCEPTABLE_DATE LOT NETTABLE_SUP_QTY_OVERRIDE OLD_DOCK_DATE OLD_DUE_DATE ORDER_DATE_TYPE ORIGINAL_ITEM ORIGINAL_ITEM_QUANTITY ORIGINAL_NEED_BY_DATE ORIGINAL_ORDER_QUANTITY PLANNED_ARRIVAL_DATE PRODUCT_FAMILY PROMISED_ARRIVAL_DATE PROMISED_SHIP_DATE QUANTITY_BY_DUE_DATE READY_TO_BUILD_PERCENTAGE REQUESTED_ARRIVAL_DATE REQUESTED_SHIP_DATE RESCHEDULE_DAYS RESCHEDULED RESERVED_QUANTITY REVISED_DEMAND_DATE REVISED_DEMAND_PRIORITY DEMAND_PRIORITY OVERRIDE_DEMAND_PRIORITY SCHEDULE_NAME SCHEDULE_ARRIVAL_DATE MATERIAL_AVLBL_DATE SCHEDULE_SHIP_DATE SHIP_TO SHIPMENT_SET_NAME SOURCE_ORDER_PRIORITY START_QUANTITY SUBINVENTORY SUGGESTED_ORDER_DATE UNIT_NUMBER UNMET_DEMAND_QUANTITY USING_ASSEMBLY WORK_ORDER_STATUS ZONE LAST_UPDATE_DATE LAST_UPDATED_BY DAYS_PAST_DUE QUANTITY_SATISFIED_BY_DUE_DATE EXPENSE_TRANSFER PLANNED_ORDER_TYPE GLOBAL_ATTRIBUTE_NUMBER11 GLOBAL_ATTRIBUTE_NUMBER12 GLOBAL_ATTRIBUTE_NUMBER13 GLOBAL_ATTRIBUTE_NUMBER14 GLOBAL_ATTRIBUTE_NUMBER15 GLOBAL_ATTRIBUTE_NUMBER16 GLOBAL_ATTRIBUTE_NUMBER17 GLOBAL_ATTRIBUTE_NUMBER18 GLOBAL_ATTRIBUTE_NUMBER19 GLOBAL_ATTRIBUTE_NUMBER20 GLOBAL_ATTRIBUTE_NUMBER21 GLOBAL_ATTRIBUTE_NUMBER22 GLOBAL_ATTRIBUTE_NUMBER23 GLOBAL_ATTRIBUTE_NUMBER24 GLOBAL_ATTRIBUTE_NUMBER25 GLOBAL_ATTRIBUTE_NUMBER26 ATTRIBUTE_DISPLAY_VALUE11 ATTRIBUTE_DISPLAY_VALUE12 ATTRIBUTE_DISPLAY_VALUE13 ATTRIBUTE_DISPLAY_VALUE14 ATTRIBUTE_DISPLAY_VALUE15 ATTRIBUTE_DISPLAY_VALUE16 ATTRIBUTE_DISPLAY_VALUE17 ATTRIBUTE_DISPLAY_VALUE18 ATTRIBUTE_DISPLAY_VALUE19 ATTRIBUTE_DISPLAY_VALUE20 ATTRIBUTE_DISPLAY_VALUE21 ATTRIBUTE_DISPLAY_VALUE22 ATTRIBUTE_DISPLAY_VALUE23 ATTRIBUTE_DISPLAY_VALUE24 ATTRIBUTE_DISPLAY_VALUE25 ATTRIBUTE_DISPLAY_VALUE26 SEGMENT_ID SEGMENT_NAME

Query

SQL_Statement

Select s.plan_id As PLAN_ID, s.base_plan_id, s.transaction_id As ORDER_ID, s.inventory_item_id As inventory_item_id, s.organization_id As organization_id, s.order_type As order_type, msc_get_name.lookup_meaning('MSC_ORDER_TYPE', s.ORDER_TYPE) AS ORDER_TYPE_TEXT, s.drop_ship_cust_id As customer_id , s.drop_ship_cust_site_id As customer_site_id, s.supplier_id As supplier_id, s.supplier_site_id As supplier_site_id, TRUNC(TO_DATE(s.NEW_SCHEDULE_DATE)) As order_date, s.new_order_quantity As Order_Quantity, s.IMPLEMENTED_QUANTITY As Implemented_Supply_Quantity, s.new_order_quantity * nvl(si.list_price,0) * (1- nvl(si.average_discount, 0)/100) As Order_Value, msc_get_name.supply_order_number_new (s.order_type, s.order_number, s.transaction_id, s.disposition_id, s.fg_order_type) As Order_Number, msc_get_name.supply_order_line_number(s.order_type, s.fg_order_type, s.purch_line_num, s.to_line_number) as Line_Number, msc_get_name.get_unit_of_measure(si.uom_code) As UOM, s.NEW_SCHEDULE_DATE As Suggested_Due_Date, msc_get_name.lookup_meaning('ORA_MSC_ORDER_FIRM_STATUS',s.FIRM_PLANNED_TYPE) As Firm_Status, s.firm_date As firm_date, s.FIRM_QUANTITY As FIRM_QUANTITY, msc_spui_common.get_release_status_message(NVL(s.RELEASE_STATUS,-1),MSC_SPUI_COMMON.release_action ('MSC_SUPPLIES',s.plan_id,s.ORGANIZATION_ID,si.INVENTORY_ITEM_ID,s.order_type,s.TRANSACTION_ID,si.PURCHASING_ENABLED_FLAG,nvl(s.reschedule_flag,2),nvl(s.reschedule_days,0),si.release_time_fence_code,si.BUILD_IN_WIP_FLAG,s.SOURCE_ORGANIZATION_ID,s.new_order_quantity,s.FIRM_QUANTITY,s.RELEASE_STATUS,si.wip_supply_type,si.BOM_ITEM_TYPE,si.PLANNING_MAKE_BUY_CODE,s.implemented_quantity,s.original_quantity,s.DISPOSITION_STATUS_TYPE,s.reserved_quantity,s.routing_sequence_id,si.PLANNER_CODE,s.FIRM_PLANNED_TYPE,s.applied,s.drop_ship_dest_type,si.back_to_back_flag,si.in_source_plan,s.release_errors,s.fg_order_type)) as RELEASE_STATUS, msc_get_name.lookup_meaning('ORA_MSC_ACTIONS',substr(MSC_SPUI_COMMON.release_action ('MSC_SUPPLIES',s.plan_id,s.ORGANIZATION_ID,si.INVENTORY_ITEM_ID,s.order_type,s.TRANSACTION_ID,si.PURCHASING_ENABLED_FLAG,nvl(s.reschedule_flag,2),nvl(s.reschedule_days,0),si.release_time_fence_code,si.BUILD_IN_WIP_FLAG,s.SOURCE_ORGANIZATION_ID,s.new_order_quantity,s.FIRM_QUANTITY,s.RELEASE_STATUS,si.wip_supply_type,si.BOM_ITEM_TYPE,si.PLANNING_MAKE_BUY_CODE,s.implemented_quantity,s.original_quantity,s.DISPOSITION_STATUS_TYPE,s.reserved_quantity,s.routing_sequence_id,si.PLANNER_CODE,s.FIRM_PLANNED_TYPE,s.applied,s.drop_ship_dest_type,si.back_to_back_flag,si.in_source_plan,s.release_errors,s.fg_order_type), 7,3)) as ACTION, Decode(s.order_type,5,msc_get_name.lookup_meaning('MSC_ORDER_TYPE',5),1030,msc_get_name.lookup_meaning('MSC_ORDER_TYPE',1030),msc_get_name.lookup_meaning('ORA_MSC_ACTIONS',SUBSTR(MSC_SPUI_COMMON.release_action ('MSC_SUPPLIES',s.plan_id,s.ORGANIZATION_ID,si.INVENTORY_ITEM_ID,s.order_type,s.TRANSACTION_ID,si.PURCHASING_ENABLED_FLAG,nvl(s.reschedule_flag,2),nvl(s.reschedule_days,0),si.release_time_fence_code,si.BUILD_IN_WIP_FLAG,s.SOURCE_ORGANIZATION_ID,s.new_order_quantity,s.FIRM_QUANTITY,s.RELEASE_STATUS,to_number(NULL),si.BOM_ITEM_TYPE,si.PLANNING_MAKE_BUY_CODE,s.implemented_quantity,s.original_quantity,s.DISPOSITION_STATUS_TYPE,s.reserved_quantity,s.routing_sequence_id,si.PLANNER_CODE,s.FIRM_PLANNED_TYPE,s.wip_status_code,s.drop_ship_dest_type,si.back_to_back_flag,si.in_source_plan,s.release_errors,s.fg_order_type),9,1))) AS Recommendation_Type, s.new_ship_date As Suggested_Ship_Date, s.NEW_WIP_START_DATE As Suggested_Start_Date, s.new_dock_date As Suggested_Dock_Date, nvl(s.ship_method, msc_get_name.get_ship_method(s.carrier_id, s.mode_of_transport, s.service_level)) As Shipping_Method, s.need_by_date As need_by_date, msc_get_name.org_code(s.source_organization_id) As Source_Organization, NVL(s.implement_daily_rate,s.IMPLEMENT_QUANTITY) As Implement_Quantity, msc_get_name.get_item_structure_name(si.plan_id, s.implement_bill_sequence_id,s.organization_id,si.SIMULATION_SET_ID) As Implement_Item_Structure_Name, msc_get_name.get_work_definition(si.plan_id, s.implement_routing_sequence_id,s.organization_id,si.SIMULATION_SET_ID) As Implement_Work_Definition_Name, DECODE(s.implement_as, NULL, NULL, msc_get_name.lookup_meaning( 'ORA_MSC_WORKBENCH_IMPLEMENT_AS',s.implement_as)) As Implement_Order_Type, s.IMPLEMENT_DATE As IMPLEMENT_DATE, msc_get_name.lookup_meaning('SYS_YES_NO',s.IMPLEMENT_FIRM ) As IMPLEMENT_FIRM, msc_get_name.lookup_meaning('ORA_MSC_WIP_JOB_STATUS', s.IMPLEMENT_STATUS_CODE) As IMPLEMENT_STATUS, msc_get_name.supplier(s.IMPLEMENT_SUPPLIER_ID) As IMPLEMENT_SUPPLIER, msc_get_name.supplier_site(s.IMPLEMENT_SUPPLIER_SITE_ID) As IMPLEMENT_SUPPLIER_SITE, msc_get_name.get_ship_method(s.implement_carrier_id, s.implement_mode_of_transport_id, s.implement_service_level_id) As Implement_Shipping_Method, s.implement_ship_date As implement_ship_date, s.implement_dock_date As implement_dock_date, msc_get_name.lookup_meaning('ORA_MSC_WO_SUB_TYPE',s.IMPLEMENT_WIP_CLASS_CODE ) As Implement_Work_Order_Subtype, msc_get_name.org_code(s.IMPLEMENT_SOURCE_ORG_ID) As Implement_Source_Organization, s.IMPLEMENTED_QUANTITY As IMPLEMENTED_QUANTITY, msc_spui_common.get_release_error_message(MSC_SPUI_COMMON.release_action ('MSC_SUPPLIES',s.plan_id,s.ORGANIZATION_ID,si.INVENTORY_ITEM_ID,s.order_type,s.TRANSACTION_ID,si.PURCHASING_ENABLED_FLAG,nvl(s.reschedule_flag,2),nvl(s.reschedule_days,0),si.release_time_fence_code,si.BUILD_IN_WIP_FLAG,s.SOURCE_ORGANIZATION_ID,s.new_order_quantity,s.FIRM_QUANTITY,s.RELEASE_STATUS,si.wip_supply_type,si.BOM_ITEM_TYPE,si.PLANNING_MAKE_BUY_CODE,s.implemented_quantity,s.original_quantity,s.DISPOSITION_STATUS_TYPE,s.reserved_quantity,s.routing_sequence_id,si.PLANNER_CODE,s.FIRM_PLANNED_TYPE,s.applied,s.drop_ship_dest_type,si.back_to_back_flag,si.in_source_plan,s.release_errors,s.fg_order_type),msc_get_name.lookup_meaning('MSC_ORDER_TYPE', s.ORDER_TYPE) )as RELEASE_ERRORS, s.actual_start_date As actual_start_date, msc_get_name.get_item_structure_name(si.plan_id, NVL(pe.bill_sequence_id,s.bill_sequence_id),s.organization_id,si.SIMULATION_SET_ID) As Item_Structure_Name, msc_get_name.get_work_definition(si.plan_id, NVL(pe.routing_sequence_id,s.routing_sequence_id),s.organization_id,si.SIMULATION_SET_ID) As Work_Definition_Name, NULL As Arrival_Set_Name, NULL As Bucket_Type, msc_get_name.lookup_meaning('SYS_YES_NO',s.BMT_FLAG ) As Buyer_Managed_Transportation, s.CTB_FLAG As Clear_to_Build, s.CTB_COMP_AVAIL_PERCENT As Clear_to_Bld_Comp_Avblty, s.CTB_EXPECTED_DATE As Clear_to_Build_Horizon, s.schedule_compress_days As Compression_Days, s.days_late As Days_Late, DECODE(s.demand_class ,'-1', NULL, s.demand_class) As demand_class, msc_get_name.lookup_meaning('ORA_MSC_DESTINATION_TYPE',s.drop_ship_dest_type) As Destination_Type, to_number(null) As End_Demand_Value, s.expiration_date As expiration_date, to_number(null) As Fulfillment_Line, s.intransit_lead_time As Transit_Lead_Time, to_date(NULL) As Latest_Acceptable_Date, s.lot_number As Lot, s.net_supply_qty_override As Nettable_Sup_Qty_Override, s.OLD_DOCK_DATE As OLD_DOCK_DATE, s.OLD_SCHEDULE_DATE As Old_Due_Date, to_number(NULL) As Order_Date_Type, to_number(NULL) As Original_Item, to_number(NULL) As Original_Item_Quantity, s.original_need_by_date As Original_Need_by_Date, s.original_quantity As Original_Order_Quantity, to_date(NULL) As Planned_Arrival_Date, si.product_family_id As Product_Family, s.promised_date As Promised_Arrival_Date, decode(s.order_type, 1, s.promised_ship_date, 2, s.promised_ship_date, to_date(null)) As Promised_Ship_Date, to_number(NULL) As Quantity_by_Due_Date, s.RTB_ORDER_QTY_PERCENT As Ready_to_Build_Percentage, decode(s.order_type, 1, s.original_need_by_date, 2, s.original_need_by_date, to_date(null)) As Requested_Arrival_Date, decode(s.order_type, 1, s.requested_ship_date, 2, s.requested_ship_date, to_date(null)) As Requested_Ship_Date, s.reschedule_days As reschedule_days, msc_get_name.lookup_meaning('SYS_YES_NO',s.RESCHEDULE_FLAG) As Rescheduled, decode(si.plan_id, -1, nvl(msc_get_name.get_supply_reserved_quantity(s.order_type, s.organization_id, s.po_line_location_id, s.wip_entity_name, s.to_line_id, s.subinventory_code, s.reserved_quantity), s.reserved_quantity), s.reserved_quantity) As Reserved_Quantity, to_date(NULL) As Revised_Demand_Date, to_number(NULL) As Revised_Demand_Priority, to_number(NULL) as demand_priority, to_number(NULL) as override_demand_priority, s.schedule_group_name As Schedule_Name, to_date(NULL) As SCHEDULE_ARRIVAL_DATE, to_date(NULL) As MATERIAL_AVLBL_DATE, to_date(NULL) As schedule_ship_date, to_number(NULL) As Ship_to, NULL As Shipment_Set_Name, to_number(NULL) As Source_Order_Priority, s.wip_start_quantity As Start_Quantity, s.subinventory_code As Subinventory, NVL(s.NEW_ORDER_PLACEMENT_DATE , s.firm_date) As Suggested_Order_Date, s.unit_number As unit_number, to_number(NULL) As Unmet_Demand_Quantity, NULL As Using_Assembly, msc_get_name.lookup_meaning('ORA_MSC_WIP_JOB_STATUS', s.wip_status_code) As Work_Order_Status, msc_get_name.get_zone_name(s.zone_id) As Zone, s.LAST_UPDATE_DATE As LAST_UPDATE_DATE, s.LAST_UPDATED_BY As LAST_UPDATED_BY, s.days_past_due As days_past_due, to_number(null) As Quantity_Satisfied_by_Due_Date, to_char(null) As Expense_Transfer, DECODE(s.order_type,5,MSC_ANALYTIC_OTBI_UTIL.planned_order(s.ORGANIZATION_ID,s.source_organization_id,s.SUPPLIER_ID),NULL) As Planned_Order_Type, s.GLOBAL_ATTRIBUTE_NUMBER11, s.GLOBAL_ATTRIBUTE_NUMBER12, s.GLOBAL_ATTRIBUTE_NUMBER13, s.GLOBAL_ATTRIBUTE_NUMBER14, s.GLOBAL_ATTRIBUTE_NUMBER15, s.GLOBAL_ATTRIBUTE_NUMBER16, s.GLOBAL_ATTRIBUTE_NUMBER17, s.GLOBAL_ATTRIBUTE_NUMBER18, s.GLOBAL_ATTRIBUTE_NUMBER19, s.GLOBAL_ATTRIBUTE_NUMBER20, s.GLOBAL_ATTRIBUTE_NUMBER21, s.GLOBAL_ATTRIBUTE_NUMBER22, s.GLOBAL_ATTRIBUTE_NUMBER23, s.GLOBAL_ATTRIBUTE_NUMBER24, s.GLOBAL_ATTRIBUTE_NUMBER25, s.GLOBAL_ATTRIBUTE_NUMBER26, null as ATTRIBUTE_DISPLAY_VALUE11, null as ATTRIBUTE_DISPLAY_VALUE12, null as ATTRIBUTE_DISPLAY_VALUE13, null as ATTRIBUTE_DISPLAY_VALUE14, null as ATTRIBUTE_DISPLAY_VALUE15, null as ATTRIBUTE_DISPLAY_VALUE16, null as ATTRIBUTE_DISPLAY_VALUE17, null as ATTRIBUTE_DISPLAY_VALUE18, null as ATTRIBUTE_DISPLAY_VALUE19, null as ATTRIBUTE_DISPLAY_VALUE20, null as ATTRIBUTE_DISPLAY_VALUE21, null as ATTRIBUTE_DISPLAY_VALUE22, null as ATTRIBUTE_DISPLAY_VALUE23, null as ATTRIBUTE_DISPLAY_VALUE24, null as ATTRIBUTE_DISPLAY_VALUE25, null as ATTRIBUTE_DISPLAY_VALUE26, si.segment_id, msc_get_name.segment_name(si.segment_id) as SEGMENT_NAME From MSC_SUPPLIES s, msc_system_items si , msc_plan_definitions mp, msc_process_effectivity pe where s.plan_id=pe.plan_id(+) and s.process_seq_id=pe.process_sequence_id(+) and s.plan_id=mp.plan_id and si.plan_id =NVL(mp.base_plan_id, mp.plan_id) and si.ORGANIZATION_ID =s.ORGANIZATION_ID AND si.INVENTORY_ITEM_ID =s.INVENTORY_ITEM_ID AND ( si.new_plan_id =-1 ) AND ( si.SIMULATION_SET_ID =-1) AND mp.expose_to_analytic = 1 UNION ALL Select d.plan_id As PLAN_ID, d.base_plan_id, d.demand_id As ORDER_ID, d.inventory_item_id As inventory_item_id, d.organization_id As organization_id, d.origination_type As order_type, msc_get_name.lookup_meaning('MSC_ORDER_TYPE', d.origination_type) AS ORDER_TYPE_TEXT, d.customer_id As customer_id , d.customer_site_id As customer_site_id, d.supplier_id As supplier_id, d.supplier_site_id As supplier_site_id, TRUNC(TO_DATE(d.USING_ASSEMBLY_DEMAND_DATE)) As order_date, d.using_requirement_quantity As Order_Quantity, to_number(null) As Implemented_Supply_Quantity, d.using_requirement_quantity * decode(d.origination_type, 1029, nvl(d.selling_price, si.list_price * (1- nvl(si.average_discount, 0)/100)), 1030, nvl(d.selling_price, si.list_price * (1- nvl(si.average_discount, 0)/100)), 1008, nvl(d.selling_price, si.list_price * (1- nvl(si.average_discount, 0)/100)), d.selling_price) As Order_Value, msc_get_name.demand_order_number_new(d.origination_type,d.order_number,d.plan_id,d.disposition_id,d.wip_entity_name, d.orch_order_number,d.schedule_designator_id,d.SALES_ORDER_NUMBER,d.demand_id) As Order_Number, decode(d.origination_type, 1030, nvl(d.ORCH_ORDER_LINE_NUMBER, d.SO_LINE_NUM), d.ORCH_ORDER_LINE_NUMBER) As Line_Number, msc_get_name.get_unit_of_measure(si.uom_code) As UOM, d.USING_ASSEMBLY_DEMAND_DATE As Suggested_Due_Date, msc_get_name.lookup_meaning('ORA_MSC_ORDER_FIRM_STATUS',DECODE(d.org_firm_flag, 1, 1, 3, 2, 2)) As Firm_Status, d.firm_date As firm_date, d.FIRM_QUANTITY As FIRM_QUANTITY, msc_spui_common.get_release_status_message(NVL(d.RELEASE_STATUS,-1), MSC_SPUI_COMMON.release_action ('MSC_DEMANDS',d.plan_id,d.ORGANIZATION_ID,msi.INVENTORY_ITEM_ID,d.origination_type,d.DEMAND_ID,msi.PURCHASING_ENABLED_FLAG,nvl(d.reschedule_flag,2),0,null,msi.BUILD_IN_WIP_FLAG,d.source_organization_id,ABS(d.using_requirement_quantity),d.FIRM_QUANTITY,d.release_status,msi.wip_supply_type,msi.BOM_ITEM_TYPE, msi.PLANNING_MAKE_BUY_CODE,null,d.original_quantity,null,d.reserved_quantity,NULL,msi.PLANNER_CODE,DECODE(d.org_firm_flag, 1, 1, 3, 2, 2),d.applied,to_number(null),msi.back_to_back_flag,msi.in_source_plan,d.release_errors,null)) as RELEASE_STATUS, msc_get_name.lookup_meaning('ORA_MSC_ACTIONS',substr(MSC_SPUI_COMMON.release_action ('MSC_DEMANDS',d.plan_id,d.ORGANIZATION_ID,msi.INVENTORY_ITEM_ID,d.origination_type,d.DEMAND_ID,msi.PURCHASING_ENABLED_FLAG,nvl(d.reschedule_flag,2),0,null,msi.BUILD_IN_WIP_FLAG,d.source_organization_id,ABS(d.using_requirement_quantity),d.FIRM_QUANTITY,d.release_status,msi.wip_supply_type,msi.BOM_ITEM_TYPE, msi.PLANNING_MAKE_BUY_CODE,null,d.original_quantity,null,d.reserved_quantity,NULL,msi.PLANNER_CODE,DECODE(d.org_firm_flag, 1, 1, 3, 2, 2),d.applied,to_number(null),msi.back_to_back_flag,msi.in_source_plan,d.release_errors,null) , 7, 3) )as ACTION, Decode(d.origination_type,5,msc_get_name.lookup_meaning('MSC_ORDER_TYPE',5),1030,msc_get_name.lookup_meaning('MSC_ORDER_TYPE',1030),msc_get_name.lookup_meaning('ORA_MSC_ACTIONS',SUBSTR(MSC_SPUI_COMMON.release_action ('MSC_DEMANDS',d.plan_id,d.ORGANIZATION_ID,si.INVENTORY_ITEM_ID,d.origination_type,d.DEMAND_ID,si.PURCHASING_ENABLED_FLAG,nvl(d.reschedule_flag,2),0,null,si.BUILD_IN_WIP_FLAG,d.source_organization_id,ABS(d.using_requirement_quantity),d.FIRM_QUANTITY,d.release_status,null,si.BOM_ITEM_TYPE,si.PLANNING_MAKE_BUY_CODE,null,d.original_quantity,null,d.reserved_quantity,NULL,si.PLANNER_CODE,DECODE(d.org_firm_flag, 1, 1, 3, 2, 2),to_number(NULL),to_number(null),si.back_to_back_flag,si.in_source_plan,d.release_errors,null),9,1))) As Recommendation_Type, d.planned_ship_date As Suggested_Ship_Date, to_date(null) As Suggested_Start_Date, to_date(null) As Suggested_Dock_Date, nvl(d.ship_method, msc_get_name.get_ship_method(d.carrier_id, d.mode_of_transport, d.service_level)) As Shipping_Method, to_date(null) As need_by_date, DECODE(d.supply_id, NULL, DECODE(d.origination_type, 1006, DECODE( msc_get_name.order_type(d.plan_id, d.disposition_id ), 2, NULL, msc_get_name.org_code(d.source_organization_id)), 1030, DECODE(msc_get_name.order_type( d.plan_id, d.disposition_id), 2, NULL, msc_get_name.org_code(d.source_organization_id)), 1001, DECODE(mp.plan_type, 5, NULL, msc_get_name.org_code(d.source_organization_id)), msc_get_name.org_code( d.source_organization_id)), NULL ) As Source_Organization, to_number(null) As Implement_Quantity, to_char(null) As Implement_Item_Structure_Name, to_char(null) As Implement_Work_Definition_Name, to_char(null) As Implement_Order_Type, d.IMPLEMENT_DATE As IMPLEMENT_DATE, msc_get_name.lookup_meaning('SYS_YES_NO',d.IMPLEMENT_FIRM ) As IMPLEMENT_FIRM, TO_CHAR(NULL) As IMPLEMENT_STATUS, to_char(null) As IMPLEMENT_SUPPLIER, to_char(null) As IMPLEMENT_SUPPLIER_SITE, to_char(null) As Implement_Shipping_Method, d.implement_ship_date As implement_ship_date, to_date(NULL) As implement_dock_date, to_char(null) As Implement_Work_Order_Subtype, msc_get_name.org_code(d.implement_org_id) As Implement_Source_Organization, TO_NUMBER(NULL) As IMPLEMENTED_QUANTITY, msc_spui_common.get_release_error_message(MSC_SPUI_COMMON.release_action ('MSC_DEMANDS',d.plan_id,d.ORGANIZATION_ID,msi.INVENTORY_ITEM_ID,d.origination_type,d.DEMAND_ID,msi.PURCHASING_ENABLED_FLAG,nvl(d.reschedule_flag,2),0,null,msi.BUILD_IN_WIP_FLAG,d.source_organization_id,ABS(d.using_requirement_quantity),d.FIRM_QUANTITY,d.release_status,msi.wip_supply_type,msi.BOM_ITEM_TYPE, msi.PLANNING_MAKE_BUY_CODE,null,d.original_quantity,null,d.reserved_quantity,NULL,msi.PLANNER_CODE,DECODE(d.org_firm_flag, 1, 1, 3, 2, 2),d.applied,to_number(null),msi.back_to_back_flag,msi.in_source_plan,d.release_errors,null),msc_get_name.lookup_meaning('MSC_ORDER_TYPE', d.origination_type) )as RELEASE_ERRORS, to_date(NULL) As actual_start_date, to_char(null) As Item_Structure_Name, to_char(null) As Work_Definition_Name, d.arrival_set_name As Arrival_Set_Name, DECODE(d.origination_type, 1001, NULL, 1004, NULL, 1016, NULL, 1017, NULL, 1018, NULL , 1019, NULL, 1020, NULL, 1021, NULL, 1023, NULL, 1025, NULL, 1026, NULL, 1028, NULL, 1022, NULL, DECODE(d.bucket_type, 1,'Days', 2,'Weeks', 3,'Periods', NULL) ) As Bucket_Type, Null As Buyer_Managed_Transportation, TO_CHAR(NULL) As Clear_to_Build, to_number(NULL) As Clear_to_Bld_Comp_Avblty, to_date(NULL) As Clear_to_Build_Horizon, to_number(NULL) As Compression_Days, d.days_late As Days_Late, d.demand_class As demand_class, null As Destination_Type, decode(d.origination_type, 1029, nvl(d.selling_price,si.list_price * (1- nvl(si.average_discount, 0)/100)), 1030, nvl(d.selling_price,si.list_price * (1- nvl(si.average_discount, 0)/100)), 1008, nvl(d.selling_price, si.list_price * (1- nvl(si.average_discount, 0)/100)), 0) * -( d.using_requirement_quantity) As End_Demand_Value, d.expiration_date As expiration_date, d.FULFILLMENT_LINE_NUM As Fulfillment_Line, d.intransit_lead_time As Transit_Lead_Time, decode( d.order_date_type_code, 1, d.latest_acceptable_ship_date, 2, d.latest_acceptable_arrival_date , NULL) latest_acceptable_date, Null As Lot, to_number(null) As Nettable_Sup_Qty_Override, to_date(NULL) As OLD_DOCK_DATE, d.OLD_DEMAND_DATE As Old_Due_Date, d.ORDER_DATE_TYPE_CODE As Order_Date_Type, d.original_item_id As Original_Item, d.original_quantity As Original_Item_Quantity, to_date(NULL) As Original_Need_by_Date, d.old_demand_quantity As Original_Order_Quantity, d.PLANNED_ARRIVAL_DATE As Planned_Arrival_Date, si.product_family_id As Product_Family, decode(d.origination_type, 1030, d.promise_arrival_date, d.promise_date) As Promised_Arrival_Date, d.promise_ship_date As Promised_Ship_Date, d.quantity_by_due_date*NVL(d.probability, 1) As Quantity_by_Due_Date, to_number(NULL) As Ready_to_Build_Percentage, decode(d.origination_type, 1030, decode(d.order_date_type_code, 2, d.request_date, null), d.request_date) As Requested_Arrival_Date, decode(d.origination_type, 1030, decode(d.order_date_type_code, 1, d.request_date, null),d.request_ship_date) As Requested_Ship_Date, to_number(NULL) As reschedule_days, msc_get_name.lookup_meaning('SYS_YES_NO',d.reschedule_flag) As Rescheduled, decode(si.plan_id, -1, decode(d.origination_type, 1030, nvl(msc_get_name.get_demand_reserved_quantity(d.sales_order_line_id, d.organization_id),d.reserved_quantity), d.reserved_quantity), d.reserved_quantity) As Reserved_Quantity, d.revised_dmd_date As Revised_Demand_Date, d.revised_dmd_priority As Revised_Demand_Priority, d.demand_priority, d.override_demand_priority, NULL As Schedule_Name, d.SCHEDULE_ARRIVAL_DATE As SCHEDULE_ARRIVAL_DATE, d.DMD_SATISFIED_DATE As MATERIAL_AVLBL_DATE, nvl(d.schedule_ship_date, d.USING_ASSEMBLY_DEMAND_DATE) As schedule_ship_date, msc_get_name.customer_site(d.ship_to_site_id) As Ship_to, d.ship_set_name As Shipment_Set_Name, DECODE(d.origination_type, 1006, msc_get_name.source_demand_priority( d.plan_id, d.demand_id), 1007, msc_get_name.source_demand_priority( d.plan_id, d.demand_id), 1008, msc_get_name.source_demand_priority( d.plan_id, d.demand_id), 1029, msc_get_name.source_demand_priority( d.plan_id, d.demand_id), 1030, msc_get_name.source_demand_priority( d.plan_id, d.demand_id), to_number(NULL)) As Source_Order_Priority, to_number(NULL) As Start_Quantity, d.sub_inventory_code As Subinventory, to_date(null) As Suggested_Order_Date, d.unit_number As unit_number, d.unmet_quantity As Unmet_Demand_Quantity, msi.ITEM_NAME As Using_Assembly, TO_CHAR(NULL) As Work_Order_Status, DECODE(d.zone_id, NULL, NULL, msc_get_name.get_zone_name(d.zone_id)) As Zone, d.LAST_UPDATE_DATE As LAST_UPDATE_DATE, d.LAST_UPDATED_BY As LAST_UPDATED_BY, d.days_past_due As days_past_due, d.qty_satisfied_by_rdd As Quantity_Satisfied_by_Due_Date, msc_get_name.lookup_meaning('SYS_YES_NO',nvl(d.EXPENSE_TRANSFER,2) )As Expense_Transfer, DECODE(d.origination_type,5,MSC_ANALYTIC_OTBI_UTIL.planned_order(d.ORGANIZATION_ID,d.source_organization_id,d.SUPPLIER_ID),NULL) As Planned_Order_Type, d.GLOBAL_ATTRIBUTE_NUMBER11, d.GLOBAL_ATTRIBUTE_NUMBER12, d.GLOBAL_ATTRIBUTE_NUMBER13, d.GLOBAL_ATTRIBUTE_NUMBER14, d.GLOBAL_ATTRIBUTE_NUMBER15, d.GLOBAL_ATTRIBUTE_NUMBER16, d.GLOBAL_ATTRIBUTE_NUMBER17, d.GLOBAL_ATTRIBUTE_NUMBER18, d.GLOBAL_ATTRIBUTE_NUMBER19, d.GLOBAL_ATTRIBUTE_NUMBER20, d.GLOBAL_ATTRIBUTE_NUMBER21, d.GLOBAL_ATTRIBUTE_NUMBER22, d.GLOBAL_ATTRIBUTE_NUMBER23, d.GLOBAL_ATTRIBUTE_NUMBER24, d.GLOBAL_ATTRIBUTE_NUMBER25, d.GLOBAL_ATTRIBUTE_NUMBER26, null as ATTRIBUTE_DISPLAY_VALUE11, null as ATTRIBUTE_DISPLAY_VALUE12, null as ATTRIBUTE_DISPLAY_VALUE13, null as ATTRIBUTE_DISPLAY_VALUE14, null as ATTRIBUTE_DISPLAY_VALUE15, null as ATTRIBUTE_DISPLAY_VALUE16, null as ATTRIBUTE_DISPLAY_VALUE17, null as ATTRIBUTE_DISPLAY_VALUE18, null as ATTRIBUTE_DISPLAY_VALUE19, null as ATTRIBUTE_DISPLAY_VALUE20, null as ATTRIBUTE_DISPLAY_VALUE21, null as ATTRIBUTE_DISPLAY_VALUE22, null as ATTRIBUTE_DISPLAY_VALUE23, null as ATTRIBUTE_DISPLAY_VALUE24, null as ATTRIBUTE_DISPLAY_VALUE25, null as ATTRIBUTE_DISPLAY_VALUE26, msi.segment_id, msc_get_name.segment_name(msi.segment_id) as SEGMENT_NAME FROM msc_demands d,msc_plan_definitions mp,msc_system_items si, msc_system_items msi WHERE si.plan_id =NVL(mp.base_plan_id, mp.plan_id) AND si.new_plan_id =-1 AND si.simulation_set_id =-1 AND d.ORGANIZATION_ID =si.ORGANIZATION_ID AND d.INVENTORY_ITEM_ID =si.INVENTORY_ITEM_ID AND d.USING_ASSEMBLY_ITEM_ID = msi.INVENTORY_ITEM_ID AND d.ORGANIZATION_ID =msi.ORGANIZATION_ID and msi.plan_id =NVL(mp.base_plan_id, mp.plan_id) AND msi.new_plan_id =-1 AND msi.simulation_set_id =-1 AND d.origination_type <> 1052 AND mp.plan_id =d.plan_id AND mp.expose_to_analytic = 1