VIEW: APPS.MSC_ORDERS_V

Source

View Text - Preformatted

SELECT 'MSC_SUPPLIES', sup.ROWID , sup.TRANSACTION_ID , sup.LAST_UPDATE_DATE , sup.LAST_UPDATED_BY , sup.CREATION_DATE , sup.CREATED_BY , sup.LAST_UPDATE_LOGIN , msi.INVENTORY_ITEM_ID , sup.ORGANIZATION_ID , msi.organization_code, to_number(null) , to_number(null) , sup.sr_instance_id, sup.plan_id , null , msc_get_name.action('MSC_SUPPLIES', msi.bom_item_type, msi.base_item_id, msi.wip_supply_type, sup.order_type, sup.reschedule_flag, sup.disposition_status_type, sup.new_schedule_date, sup.old_schedule_date, sup.implemented_quantity, sup.quantity_in_process, sup.new_order_quantity, msi.release_time_fence_code, sup.reschedule_days, sup.firm_quantity, sup.plan_id, msi.critical_component_flag, msi.mrp_planning_code, msi.lots_exist, sup.item_type_value) , sup.NEW_SCHEDULE_DATE, sup.OLD_SCHEDULE_DATE , sup.NEW_WIP_START_DATE , sup.DISPOSITION_ID , msc_get_name.supply_order_number ( sup.order_type ,sup.order_number ,sup.plan_id ,sup.sr_instance_id ,sup.transaction_id ,sup.disposition_id ) , sup.DISPOSITION_STATUS_TYPE , sup.ORDER_TYPE , decode(mp.plan_type, 8, decode(sup.order_type, 1, msc_get_name.lookup_meaning('SRP_CHANGED_ORDER_TYPE',sup.order_type), 2, decode(sup.source_organization_id, null, msc_get_name.lookup_meaning('SRP_CHANGED_ORDER_TYPE',sup.order_type), msc_get_name.lookup_meaning('MRP_ORDER_TYPE',53) ), 51, msc_get_name.lookup_meaning('SRP_CHANGED_ORDER_TYPE',sup.order_type), l1.meaning),l1.meaning), NVL(sup.daily_rate,sup.NEW_ORDER_QUANTITY), sup.OLD_ORDER_QUANTITY , nvl(sup.NEW_ORDER_PLACEMENT_DATE , sup.firm_date), sup.FIRM_PLANNED_TYPE , sup.RESCHEDULE_FLAG , sup.NEW_PROCESSING_DAYS , sup.IMPLEMENTED_QUANTITY , sup.LAST_UNIT_COMPLETION_DATE , sup.FIRST_UNIT_START_DATE , sup.LAST_UNIT_START_DATE , sup.NEW_DOCK_DATE , sup.QUANTITY_IN_PROCESS , sup.FIRM_QUANTITY , sup.FIRM_DATE , sup.LINE_ID , DECODE(sup.line_id, NULL, NULL, msc_get_name.department_code(1,sup.line_id, sup.organization_id, sup.plan_id, sup.sr_instance_id)) , sup.IMPLEMENT_DEMAND_CLASS , msi.item_name , sup.IMPLEMENT_DATE , NVL(sup.implement_daily_rate,sup.IMPLEMENT_QUANTITY) , sup.IMPLEMENT_FIRM , TO_NUMBER(NULL) , sup.IMPLEMENT_WIP_CLASS_CODE , sup.IMPLEMENT_JOB_NAME , TO_NUMBER(NULL) , sup.IMPLEMENT_STATUS_CODE , sup.IMPLEMENT_LOCATION_ID , NVL(sup.RELEASE_STATUS,2) , sup.IMPLEMENT_AS , DECODE(sup.implement_as, NULL, NULL, msc_get_name.lookup_meaning('MRP_WORKBENCH_IMPLEMENT_AS',sup.implement_as)) , msi.PLANNER_CODE , to_number(null) , mic.sr_category_id , msi.MRP_PLANNING_CODE , msi.REPETITIVE_TYPE , msi.BUILD_IN_WIP_FLAG , msi.PURCHASING_ENABLED_FLAG , msi.PLANNING_MAKE_BUY_CODE , round(GREATEST(0, sup.NEW_ORDER_PLACEMENT_DATE - (TRUNC(SYSDATE) )) , 2), msi.BOM_ITEM_TYPE , msi.BASE_ITEM_ID , msi.WIP_SUPPLY_TYPE , sup.SOURCE_ORGANIZATION_ID, sup.SOURCE_sr_instance_id,msc_get_name.org_code(sup.source_organization_id,sup.source_sr_instance_id), sup.wip_status_code , NULL , msi.FULL_PEGGING , sup.source_supplier_id , msc_get_name.source_supplier(sup.sr_instance_id, sup.plan_id,sup.supplier_id,sup.source_supplier_id,sup.SOURCE_ORGANIZATION_ID,sup.order_type) , sup.source_supplier_site_id , msc_get_name.source_supplier_site(sup.sr_instance_id, sup.plan_id,sup.supplier_site_id,sup.source_supplier_site_id,sup.SOURCE_ORGANIZATION_ID,sup.order_type) , sup.supplier_id , NULL , sup.supplier_site_id , NULL , sup.IMPLEMENT_SOURCE_ORG_ID , sup.IMPLEMENT_sr_instance_id , sup.implement_supplier_id , sup.implement_supplier_site_id , DECODE(msi.IN_SOURCE_PLAN, 1, 1, 2) , sup.schedule_compress_days , sup.project_id , sup.task_id , 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)) , sup.implement_project_id , sup.implement_task_id , sup.planning_group , sup.status , sup.applied , msi.release_time_fence_code , sup.implement_schedule_group_id , sup.implement_build_sequence , DECODE(sup.ORDER_TYPE, 3, sup.BUILD_SEQUENCE, 7, sup.BUILD_SEQUENCE, 14, sup.BUILD_SEQUENCE, 15, sup.BUILD_SEQUENCE, 27, sup.BUILD_SEQUENCE, to_number( NULL)) , DECODE(sup.ORDER_TYPE, 3, sup.SCHEDULE_GROUP_ID, 7, sup.SCHEDULE_GROUP_ID, 14, sup.SCHEDULE_GROUP_ID, 15, sup.SCHEDULE_GROUP_ID, 27, sup.SCHEDULE_GROUP_ID, to_number(NULL)) , decode(sup.plan_id,-1,sup.alternate_bom_designator, nvl(sup.alternate_bom_designator,msc_get_name.alternate_bom(sup.plan_id, sup.sr_instance_id, nvl(pe.bill_sequence_id,sup.bill_sequence_id)))) , decode(sup.plan_id,-1, sup.alternate_routing_designator,nvl(sup.alternate_routing_designator, msc_get_name.alternate_rtg(sup.plan_id, sup.sr_instance_id,nvl(pe.routing_sequence_id,sup.routing_sequence_id)))) , sup.process_seq_id , sup.implement_alternate_bom , sup.implement_alternate_routing , sup.schedule_group_name , sup.implement_employee_id , msc_get_name.cfm_routing_flag(pe.plan_id, pe.sr_instance_id,pe.routing_sequence_id) , msi.buyer_name , sup.release_errors , sup.number1 , sup.unit_number , sup.implement_unit_number , msi.description , nvl(sup.planning_group, '0') , nvl(msi.buyer_name, '0') , nvl(msi.planner_code,'0') , nvl(sup.project_id, 0) , nvl(sup.task_id,0) , null , null , mic.category_set_id , mic.category_name , msi.product_family_id , msc_get_name.item_name(msi.product_family_id,null,null,null) , msc_get_name.item_name(msi.base_item_id,null,null,null) , msi.abc_class_name , msi.uom_code , decode(mp.plan_type, 5, decode(sup.order_type,2, msc_drp_util.get_iso_name(sup.plan_id, sup.sr_instance_id, sup.transaction_id),null),null), decode(mp.plan_type, 5, to_number(null), sup.schedule_priority), sup.promised_date , to_date(null) , to_number(null) , null , to_number(null) , null , to_number(null) , null , sup.lot_number , sup.subinventory_code , sup.parent_id , sup.wip_status_code, msc_get_name.lookup_meaning('WIP_JOB_STATUS', sup.wip_status_code), to_number(null) , to_date(null) , sup.need_by_date , DECODE(sup.schedule_designator_id, NULL, NULL, msc_get_name.designator(sup.schedule_designator_id)) , msi.list_price , msi.standard_cost , to_number(null) , to_number(null) , decode(sup.demand_class ,'-1', null, sup.demand_class), to_number(null) , sup.new_ship_date , nvl(sup.daily_rate,sup.new_order_quantity) , to_date(null) , null , to_number(null) , sup.purch_line_num , sup.reschedule_days , decode(sup.plan_id, -1, sup.new_dock_date, sup.old_need_by_date) , to_number(null) , to_number(null) , null , to_number(null) , sup.ship_method, sup.explosion_date, to_number(NULL), sup.original_quantity, sup.original_need_by_date, sup.earliest_start_date, sup.earliest_completion_date, sup.min_start_date, msi.low_level_code, round(nvl(sup.earliest_completion_date-sup.need_by_date,0),2), sup.ulpsd, sup.ulpcd, sup.uepsd, sup.uepcd, sup.eacd, sup.supply_is_shared, sup.record_source, to_number(null), to_number(null), sup.wip_start_quantity, sup.expiration_date, msc_get_name.lookup_meaning('MRP_PLANNING_CODE',msi.mrp_planning_code), to_number(null) , to_number(null), decode(sup.order_type, 3, decode( msi.rounding_control_type, 1, ceil(decode(sup.implement_quantity, null, sup.new_order_quantity, sup.implement_quantity)/MSC_Rel_Plan_PUB.GET_REV_CUM_YIELD( sup.sr_instance_id, sup.plan_id, sup.process_seq_id, sup.transaction_id, sup.organization_id)), decode(sup.implement_quantity, null, sup.new_order_quantity, sup.implement_quantity)/MSC_Rel_Plan_PUB.GET_REV_CUM_YIELD( sup.sr_instance_id, sup.plan_id, sup.process_seq_id, sup.transaction_id, sup.organization_id)), 5, decode(msi.PLANNING_MAKE_BUY_CODE,1,decode(msi.rounding_control_type, 1, ceil(decode(sup.implement_quantity, null, sup.new_order_quantity, sup.implement_quantity)/ MSC_Rel_Plan_PUB.GET_REV_CUM_YIELD(sup.sr_instance_id, sup.plan_id, sup.process_seq_id, sup.transaction_id, sup.organization_id)), decode(sup.implement_quantity, null, sup.new_order_quantity, sup.implement_quantity)/ MSC_Rel_Plan_PUB.GET_REV_CUM_YIELD(sup.sr_instance_id, sup.plan_id, sup.process_seq_id, sup.transaction_id, sup.organization_id)), to_number(null)), to_number(null)), msi.critical_component_flag, to_number(null), to_char(null), to_date(null), to_date(null), to_date(null), sup.shipment_id, sup.ship_calendar, sup.receiving_calendar, sup.intransit_calendar, sup.intransit_lead_time, sup.implement_dock_date, sup.implement_ship_date, to_date(null), to_date(null), to_date(null), sup.days_late, null, to_number(null), to_number(null), null, nvl(decode(sup.order_type,5,MSC_UTIL.GET_VMI_FLAG ( SUP.PLAN_ID, SUP.SR_INSTANCE_ID,SUP.ORGANIZATION_ID, SUP.INVENTORY_ITEM_ID, SUP.SUPPLIER_ID ,SUP.SUPPLIER_SITE_ID), SUP.VMI_FLAG),2), nvl(msi.unit_weight,0)*nvl(sup.firm_quantity,sup.new_order_quantity), nvl(msi.unit_volume,0)*nvl(sup.firm_quantity,sup.new_order_quantity), to_date(null), null, to_number(null), null, null, null, to_number(null), null, to_number(null), to_number(null), to_number(null), to_number(null), null, to_date(null), to_number(null), to_date(null), msc_get_name.get_zone_name(sup.zone_id, sup.sr_instance_id), to_number(null), to_number(null), to_date(null), null, decode(sup.order_type , 70, msc_get_name.eam_parent_work_order (sup.plan_id, sup.sr_instance_id, sup.transaction_id), to_number(null) ), msc_get_name.item_name (sup.asset_item_id, sup.organization_id, sup.plan_id, sup.sr_instance_id), sup.asset_serial_number, sup.requested_start_date, sup.requested_completion_date, to_number(null), msi.lots_exist, sup.implement_ship_method, sup.actual_start_date, to_date(null), to_date(null), to_date(null), msc_get_name.is_within_rel_time_fence(mp.plan_start_date, NVL(sup.NEW_ORDER_PLACEMENT_DATE , sup.firm_date), msi.release_time_fence_code, msi.cumulative_total_lead_time, msi.cum_manufacturing_lead_time, msi.full_lead_time, msi.release_time_fence_days) WITHIN_REL_TIME_FENCE, (nvl(sup.daily_rate,sup.new_order_quantity) * msi.standard_cost) AMOUNT, to_number(null), to_number(null), to_number(null), sup.delivery_price, to_number(null) sales_order_line_id, to_number(null) ship_set_id, to_number(null) arrival_set_id, sup.item_type_id, sup.item_type_value, decode(mp.plan_type, 8, msc_get_name.lookup_meaning('MSC_PART_CONDITION',sup.item_type_value), null), sup.firm_start_date, sup.bill_sequence_id, sup.routing_sequence_id, to_number(null), to_number(null), null, null, sup.OTM_ARRIVAL_DATE, to_number(null), Msc_GET_name.get_order_Comments(sup.plan_id, 'SUPPLY', sup.TRANSACTION_ID) COMMENTS, to_date(null), to_number(null) FROM MSC_SUPPLIES SUP, mfg_lookups l1, msc_system_items msi , msc_item_categories mic, msc_plans mp, msc_process_effectivity pe WHERE pe.plan_id(+) = sup.plan_id AND pe.sr_instance_id(+) = sup.sr_instance_id AND pe.process_sequence_id(+) = sup.process_seq_id AND mic.sr_instance_id = sup.sr_instance_id AND mic.organization_id = sup.organization_id AND mic.inventory_item_id = sup.inventory_item_id 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 l1.lookup_type = 'MRP_ORDER_TYPE' AND l1.lookup_code = sup.order_type and mp.plan_id = sup.plan_id UNION ALL SELECT 'MSC_DEMANDS', dem.ROWID , dem.DEMAND_ID , dem.LAST_UPDATE_DATE , dem.LAST_UPDATED_BY , dem.CREATION_DATE , dem.CREATED_BY , dem.LAST_UPDATE_LOGIN , msi.INVENTORY_ITEM_ID , dem.ORGANIZATION_ID , msi.organization_code, to_number(null), to_number(null), dem.sr_instance_id, dem.plan_id, null, msc_get_name.action('MSC_DEMANDS',mp.plan_type, decode(dem.plan_id, -1, dem.supply_id,dem.disposition_id), decode(dem.source_org_instance_id,null,dem.sr_instance_id,-23453,dem.sr_instance_id,dem.source_org_instance_id), dem.origination_type, dem.reschedule_flag, dem.demand_id,null,null, dem.sales_order_line_split,dem.fill_kill_flag,null,dem.inventory_item_id,dem.prev_subst_item,null,dem.plan_id ), dem.USING_ASSEMBLY_DEMAND_DATE , dem.OLD_DEMAND_DATE , TO_DATE(NULL) , dem.DISPOSITION_ID , 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), 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 )))), msc_get_name.designator(dem.schedule_designator_id))), TO_NUMBER(NULL) , dem.origination_type, l1.meaning, - NVL(dem.DAILY_DEMAND_RATE, dem.USING_REQUIREMENT_QUANTITY), dem.OLD_DEMAND_QUANTITY , TO_DATE(NULL) , decode(dem.org_firm_flag, 1, 1, 3, 2, 2), dem.reschedule_flag, TO_NUMBER(NULL) , TO_NUMBER(NULL) , dem.ASSEMBLY_DEMAND_COMP_DATE , TO_DATE(NULL) , TO_DATE(NULL) , TO_DATE(NULL) , TO_NUMBER(NULL) , dem.FIRM_QUANTITY , dem.FIRM_DATE , TO_NUMBER(NULL) , NULL , NULL , msi.item_name , dem.implement_date , TO_NUMBER(NULL) , nvl(dem.implement_firm , 2), TO_NUMBER(NULL) , TO_CHAR(NULL) , NULL , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , dem.release_status , TO_NUMBER(NULL) , NULL , msi.PLANNER_CODE , dem.USING_ASSEMBLY_ITEM_ID , mic.sr_category_id, msi.MRP_PLANNING_CODE , msi.REPETITIVE_TYPE , msi.BUILD_IN_WIP_FLAG , msi.PURCHASING_ENABLED_FLAG , msi.PLANNING_MAKE_BUY_CODE , round(GREATEST(0, USING_ASSEMBLY_DEMAND_DATE - (TRUNC(SYSDATE) )) , 2), msi.BOM_ITEM_TYPE , msi.BASE_ITEM_ID , msi.WIP_SUPPLY_TYPE , dem.SOURCE_ORGANIZATION_ID, dem.SOURCE_ORG_instance_id , decode(dem.supply_id, null, decode(dem.origination_type,6, decode(msc_get_name.order_type(dem.plan_id, dem.disposition_id, dem.sr_instance_id), 2, null, msc_get_name.org_code(dem.source_organization_id,dem.source_org_instance_id)), 30, decode(msc_get_name.order_type(dem.plan_id, dem.disposition_id, dem.sr_instance_id), 2, null, msc_get_name.org_code(dem.source_organization_id,dem.source_org_instance_id)), 1, decode(mp.plan_type, 5, null, msc_get_name.org_code(dem.source_organization_id, dem.source_org_instance_id)), msc_get_name.org_code(dem.source_organization_id, dem.source_org_instance_id)), null ), TO_NUMBER(NULL) , msc_get_name.item_name(dem.using_assembly_item_id,null,null,null), msi.FULL_PEGGING , TO_NUMBER(NULL), NULL, TO_NUMBER(NULL), NULL, TO_NUMBER(NULL), NULL, TO_NUMBER(NULL), NULL, dem.implement_org_id, dem.implement_instance_id, TO_NUMBER(NULL), TO_NUMBER(NULL), DECODE(msi.IN_SOURCE_PLAN, 1, 1, 2) , to_number(null) , dem.project_id , dem.task_id , 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)) , to_number(null) , to_number(null) , dem.planning_group , dem.status , dem.applied , to_number(null) , to_number(NULL) , to_number(NULL) , to_number(NULL) , to_number(NULL) , null , null , to_number(null) , null , null , null , to_number(null) , to_number(null) , msi.buyer_name , dem.release_errors , to_number(NULL) , dem.unit_number , null , msi.description , nvl(dem.planning_group, '0') , nvl(msi.buyer_name, '0') , nvl(msi.planner_code,'0') , nvl(dem.project_id,0) , nvl(dem.task_id,0) , DECODE(dem.lending_project_id, NULL, NULL, msc_get_name.project(dem.lending_project_id, dem.organization_id, dem.plan_id, dem.sr_instance_id)) , DECODE(dem.lending_task_id, NULL, NULL, msc_get_name.task(dem.lending_task_id, dem.lending_project_id, dem.organization_id, dem.plan_id, dem.sr_instance_id)) , mic.category_set_id , mic.category_name , msi.product_family_id , msc_get_name.item_name(msi.product_family_id, null,null,null) , msc_get_name.item_name(msi.base_item_id,null,null,null) , msi.abc_class_name , msi.uom_code , DECODE(dem.origination_type, 24, dem.order_number, NULL) , DECODE(dem.plan_id,-1,dem.order_priority,dem.demand_priority) , dem.promise_date , dem.request_date , dem.customer_id , decode( dem.customer_id, null, msc_get_name.get_other_customers(dem.plan_id, dem.schedule_designator_id), msc_get_name.customer(dem.customer_id)) , dem.customer_site_id , decode( dem.customer_site_id, null, msc_get_name.get_other_customers(dem.plan_id, dem.schedule_designator_id), msc_get_name.customer_site(dem.customer_site_id)) , dem.ship_to_site_id , msc_get_name.customer_site(dem.ship_to_site_id) , null , null , dem.parent_id , to_number(null) , null , dem.dmd_lateness_cost , dem.dmd_satisfied_date , to_date(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))) , msi.list_price , msi.standard_cost , dem.selling_price , dem.service_level , dem.demand_class , dem.probability , dem.planned_ship_date, -(nvl(dem.daily_demand_rate,dem.using_requirement_quantity)) * nvl(dem.probability, 1) , dem.assembly_demand_comp_date , decode(dem.origination_type,1,null,4,null,16,null,17,null,18,null, 19,null,20,null,21,null,23,null,25,null,26,null,28,null,22,null, decode(dem.bucket_type,1,'Days',2,'Weeks',3,'Periods',null) ), decode(dem.origination_type, 6, msc_get_name.source_demand_priority( dem.plan_id,dem.demand_id), 7, msc_get_name.source_demand_priority( dem.plan_id,dem.demand_id), 8, msc_get_name.source_demand_priority( dem.plan_id,dem.demand_id), 29, msc_get_name.source_demand_priority( dem.plan_id,dem.demand_id), 30, msc_get_name.source_demand_priority( dem.plan_id,dem.demand_id), to_number(null)) , to_number(null) , to_number(null) , to_date(null), dem.cummulative_probability , dem.original_item_id , msc_get_name.item_name(dem.original_item_id,null,null,null) , decode(dem.original_item_id, null, to_number(null), decode(mp.plan_type, 5, dem.original_quantity, decode(dem.original_item_id,dem.inventory_item_id, dem.using_requirement_quantity, msc_get_name.demand_quantity(dem.plan_id, dem.sr_instance_id, dem.demand_id)) )), dem.ship_method , to_date(null), dem.unmet_quantity, -1*dem.original_quantity, to_date(null), to_date(null), to_date(null), to_date(null), msi.low_level_code, to_number(null), to_date(null), to_date(null), to_date(null), to_date(null), to_date(null), to_number(null), dem.record_source, decode(dem.origination_type,29,msc_get_name.forward_backward_days(dem.plan_id, dem.schedule_designator_id,1), null), decode(dem.origination_type,29,msc_get_name.forward_backward_days(dem.plan_id, dem.schedule_designator_id,2), null), to_number(null), dem.expiration_date, msc_get_name.lookup_meaning('MRP_PLANNING_CODE',msi.mrp_planning_code), dem.original_org_id, dem.original_inst_id, to_number(null), msi.critical_component_flag, dem.ORDER_DATE_TYPE_CODE, msc_get_name.lookup_meaning('MSC_ORDER_DATE_TYPE_CODE',decode(dem.ORDER_DATE_TYPE_CODE,NULL,1,dem.ORDER_DATE_TYPE_CODE)), dem.SCHEDULE_ARRIVAL_DATE, dem.LATEST_ACCEPTABLE_DATE, dem.PLANNED_ARRIVAL_DATE, decode(dem.origination_type, 30, decode(dem.demand_source_type,8, msc_drp_util.get_iso_trip(dem.plan_id, dem.sr_instance_id,dem.disposition_id),to_number(null)),to_number(null)), null, null, null, dem.intransit_lead_time, to_date(null), dem.implement_ship_date, dem.implement_arrival_date, dem.request_ship_date, dem.promise_ship_date, round(decode( sign(dem.dmd_satisfied_date - dem.using_assembly_demand_date), -1, least(dem.dmd_satisfied_date - dem.using_assembly_demand_date, -0.01), 1, greatest(dem.dmd_satisfied_date - dem.using_assembly_demand_date, 0.01), 0),2) days_late, null, to_number(null), to_number(null), NULL, to_number(null), to_number(null), to_number(null), dem.schedule_ship_date, decode(mp.plan_type, 5, decode(dem.origination_type,1, msc_get_name.org_code(dem.source_organization_id,dem.source_org_instance_id), 24, msc_get_name.org_code(dem.source_organization_id,dem.source_org_instance_id), 30, decode(dem.demand_source_type, 8, msc_get_name.org_code(dem.source_organization_id,dem.source_org_instance_id), null), null),null), dem.quantity_by_due_date*nvl(dem.probability,1), dem.ship_set_name, dem.arrival_set_name, msc_get_name.org_code(dem.original_org_id, dem.original_inst_id), dem.orig_intransit_lead_time, dem.orig_shipping_method_code, decode(mp.plan_type, 5, dem.source_organization_id,to_number(null)), decode(mp.plan_type, 5, dem.source_org_instance_id,to_number(null)), to_number(null), to_number(null), NULL, to_date(null), dem.final_use_priority, dem.final_use_date, decode(dem.zone_id, null,null,msc_get_name.get_zone_name(dem.zone_id,dem.sr_instance_id)), dem.manual_allocation_qty, dem.firm_allocation_flag, dem.manual_allocation_date, msc_get_name.lookup_meaning('SYS_YES_NO', nvl(dem.sales_order_line_split,2)), to_number(null), msc_get_name.item_name (dem.asset_item_id, dem.organization_id, dem.plan_id, dem.sr_instance_id), dem.asset_serial_number, to_date(null), to_date(null), dem.fill_kill_flag, msi.lots_exist, null, to_date(null), to_date(null), to_date(null), to_date(null), msc_get_name.is_within_rel_time_fence(mp.plan_start_date, TO_DATE(NULL), msi.release_time_fence_code, msi.cumulative_total_lead_time, msi.cum_manufacturing_lead_time, msi.full_lead_time, msi.release_time_fence_days) WITHIN_REL_TIME_FENCE, ((nvl(dem.daily_demand_rate, dem.using_requirement_quantity)) * nvl(dem.probability, 1) * msi.standard_cost) AMOUNT, nvl(dem.sales_order_line_split,2), dem.forecast_set_id, dem.schedule_designator_id, to_number(null), dem.sales_order_line_id, dem.ship_set_id, dem.arrival_set_id, dem.item_type_id, dem.item_type_value, decode(mp.plan_type, 8, msc_get_name.lookup_meaning('MSC_PART_CONDITION',dem.item_type_value),null), to_date(null), to_number(null), to_number(null), dem.prev_subst_item, dem.prev_subst_org, msc_get_name.item_name(dem.prev_subst_item,null,null,null), msc_get_name.org_code(dem.prev_subst_org, dem.sr_instance_id), dem.OTM_ARRIVAL_DATE, dem.min_rem_shelf_life_days, NULL COMMENTS, to_date(null), to_number(null) FROM msc_demands dem, msc_plans mp, mfg_lookups l1, msc_system_items msi , msc_item_categories mic WHERE ( 'MSC_DEMAND_ORIGINATION') = l1.lookup_type AND l1.lookup_code = dem.origination_type AND mic.sr_instance_id = dem.sr_instance_id AND mic.organization_id = dem.organization_id AND mic.inventory_item_id = dem.inventory_item_id 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 <> 52 and mp.plan_id = dem.plan_id

View Text - HTML Formatted

SELECT 'MSC_SUPPLIES'
, SUP.ROWID
, SUP.TRANSACTION_ID
, SUP.LAST_UPDATE_DATE
, SUP.LAST_UPDATED_BY
, SUP.CREATION_DATE
, SUP.CREATED_BY
, SUP.LAST_UPDATE_LOGIN
, MSI.INVENTORY_ITEM_ID
, SUP.ORGANIZATION_ID
, MSI.ORGANIZATION_CODE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, SUP.SR_INSTANCE_ID
, SUP.PLAN_ID
, NULL
, MSC_GET_NAME.ACTION('MSC_SUPPLIES'
, MSI.BOM_ITEM_TYPE
, MSI.BASE_ITEM_ID
, MSI.WIP_SUPPLY_TYPE
, SUP.ORDER_TYPE
, SUP.RESCHEDULE_FLAG
, SUP.DISPOSITION_STATUS_TYPE
, SUP.NEW_SCHEDULE_DATE
, SUP.OLD_SCHEDULE_DATE
, SUP.IMPLEMENTED_QUANTITY
, SUP.QUANTITY_IN_PROCESS
, SUP.NEW_ORDER_QUANTITY
, MSI.RELEASE_TIME_FENCE_CODE
, SUP.RESCHEDULE_DAYS
, SUP.FIRM_QUANTITY
, SUP.PLAN_ID
, MSI.CRITICAL_COMPONENT_FLAG
, MSI.MRP_PLANNING_CODE
, MSI.LOTS_EXIST
, SUP.ITEM_TYPE_VALUE)
, SUP.NEW_SCHEDULE_DATE
, SUP.OLD_SCHEDULE_DATE
, SUP.NEW_WIP_START_DATE
, SUP.DISPOSITION_ID
, MSC_GET_NAME.SUPPLY_ORDER_NUMBER ( SUP.ORDER_TYPE
, SUP.ORDER_NUMBER
, SUP.PLAN_ID
, SUP.SR_INSTANCE_ID
, SUP.TRANSACTION_ID
, SUP.DISPOSITION_ID )
, SUP.DISPOSITION_STATUS_TYPE
, SUP.ORDER_TYPE
, DECODE(MP.PLAN_TYPE
, 8
, DECODE(SUP.ORDER_TYPE
, 1
, MSC_GET_NAME.LOOKUP_MEANING('SRP_CHANGED_ORDER_TYPE'
, SUP.ORDER_TYPE)
, 2
, DECODE(SUP.SOURCE_ORGANIZATION_ID
, NULL
, MSC_GET_NAME.LOOKUP_MEANING('SRP_CHANGED_ORDER_TYPE'
, SUP.ORDER_TYPE)
, MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE'
, 53) )
, 51
, MSC_GET_NAME.LOOKUP_MEANING('SRP_CHANGED_ORDER_TYPE'
, SUP.ORDER_TYPE)
, L1.MEANING)
, L1.MEANING)
, NVL(SUP.DAILY_RATE
, SUP.NEW_ORDER_QUANTITY)
, SUP.OLD_ORDER_QUANTITY
, NVL(SUP.NEW_ORDER_PLACEMENT_DATE
, SUP.FIRM_DATE)
, SUP.FIRM_PLANNED_TYPE
, SUP.RESCHEDULE_FLAG
, SUP.NEW_PROCESSING_DAYS
, SUP.IMPLEMENTED_QUANTITY
, SUP.LAST_UNIT_COMPLETION_DATE
, SUP.FIRST_UNIT_START_DATE
, SUP.LAST_UNIT_START_DATE
, SUP.NEW_DOCK_DATE
, SUP.QUANTITY_IN_PROCESS
, SUP.FIRM_QUANTITY
, SUP.FIRM_DATE
, SUP.LINE_ID
, DECODE(SUP.LINE_ID
, NULL
, NULL
, MSC_GET_NAME.DEPARTMENT_CODE(1
, SUP.LINE_ID
, SUP.ORGANIZATION_ID
, SUP.PLAN_ID
, SUP.SR_INSTANCE_ID))
, SUP.IMPLEMENT_DEMAND_CLASS
, MSI.ITEM_NAME
, SUP.IMPLEMENT_DATE
, NVL(SUP.IMPLEMENT_DAILY_RATE
, SUP.IMPLEMENT_QUANTITY)
, SUP.IMPLEMENT_FIRM
, TO_NUMBER(NULL)
, SUP.IMPLEMENT_WIP_CLASS_CODE
, SUP.IMPLEMENT_JOB_NAME
, TO_NUMBER(NULL)
, SUP.IMPLEMENT_STATUS_CODE
, SUP.IMPLEMENT_LOCATION_ID
, NVL(SUP.RELEASE_STATUS
, 2)
, SUP.IMPLEMENT_AS
, DECODE(SUP.IMPLEMENT_AS
, NULL
, NULL
, MSC_GET_NAME.LOOKUP_MEANING('MRP_WORKBENCH_IMPLEMENT_AS'
, SUP.IMPLEMENT_AS))
, MSI.PLANNER_CODE
, TO_NUMBER(NULL)
, MIC.SR_CATEGORY_ID
, MSI.MRP_PLANNING_CODE
, MSI.REPETITIVE_TYPE
, MSI.BUILD_IN_WIP_FLAG
, MSI.PURCHASING_ENABLED_FLAG
, MSI.PLANNING_MAKE_BUY_CODE
, ROUND(GREATEST(0
, SUP.NEW_ORDER_PLACEMENT_DATE - (TRUNC(SYSDATE) ))
, 2)
, MSI.BOM_ITEM_TYPE
, MSI.BASE_ITEM_ID
, MSI.WIP_SUPPLY_TYPE
, SUP.SOURCE_ORGANIZATION_ID
, SUP.SOURCE_SR_INSTANCE_ID
, MSC_GET_NAME.ORG_CODE(SUP.SOURCE_ORGANIZATION_ID
, SUP.SOURCE_SR_INSTANCE_ID)
, SUP.WIP_STATUS_CODE
, NULL
, MSI.FULL_PEGGING
, SUP.SOURCE_SUPPLIER_ID
, MSC_GET_NAME.SOURCE_SUPPLIER(SUP.SR_INSTANCE_ID
, SUP.PLAN_ID
, SUP.SUPPLIER_ID
, SUP.SOURCE_SUPPLIER_ID
, SUP.SOURCE_ORGANIZATION_ID
, SUP.ORDER_TYPE)
, SUP.SOURCE_SUPPLIER_SITE_ID
, MSC_GET_NAME.SOURCE_SUPPLIER_SITE(SUP.SR_INSTANCE_ID
, SUP.PLAN_ID
, SUP.SUPPLIER_SITE_ID
, SUP.SOURCE_SUPPLIER_SITE_ID
, SUP.SOURCE_ORGANIZATION_ID
, SUP.ORDER_TYPE)
, SUP.SUPPLIER_ID
, NULL
, SUP.SUPPLIER_SITE_ID
, NULL
, SUP.IMPLEMENT_SOURCE_ORG_ID
, SUP.IMPLEMENT_SR_INSTANCE_ID
, SUP.IMPLEMENT_SUPPLIER_ID
, SUP.IMPLEMENT_SUPPLIER_SITE_ID
, DECODE(MSI.IN_SOURCE_PLAN
, 1
, 1
, 2)
, SUP.SCHEDULE_COMPRESS_DAYS
, SUP.PROJECT_ID
, SUP.TASK_ID
, 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))
, SUP.IMPLEMENT_PROJECT_ID
, SUP.IMPLEMENT_TASK_ID
, SUP.PLANNING_GROUP
, SUP.STATUS
, SUP.APPLIED
, MSI.RELEASE_TIME_FENCE_CODE
, SUP.IMPLEMENT_SCHEDULE_GROUP_ID
, SUP.IMPLEMENT_BUILD_SEQUENCE
, DECODE(SUP.ORDER_TYPE
, 3
, SUP.BUILD_SEQUENCE
, 7
, SUP.BUILD_SEQUENCE
, 14
, SUP.BUILD_SEQUENCE
, 15
, SUP.BUILD_SEQUENCE
, 27
, SUP.BUILD_SEQUENCE
, TO_NUMBER( NULL))
, DECODE(SUP.ORDER_TYPE
, 3
, SUP.SCHEDULE_GROUP_ID
, 7
, SUP.SCHEDULE_GROUP_ID
, 14
, SUP.SCHEDULE_GROUP_ID
, 15
, SUP.SCHEDULE_GROUP_ID
, 27
, SUP.SCHEDULE_GROUP_ID
, TO_NUMBER(NULL))
, DECODE(SUP.PLAN_ID
, -1
, SUP.ALTERNATE_BOM_DESIGNATOR
, NVL(SUP.ALTERNATE_BOM_DESIGNATOR
, MSC_GET_NAME.ALTERNATE_BOM(SUP.PLAN_ID
, SUP.SR_INSTANCE_ID
, NVL(PE.BILL_SEQUENCE_ID
, SUP.BILL_SEQUENCE_ID))))
, DECODE(SUP.PLAN_ID
, -1
, SUP.ALTERNATE_ROUTING_DESIGNATOR
, NVL(SUP.ALTERNATE_ROUTING_DESIGNATOR
, MSC_GET_NAME.ALTERNATE_RTG(SUP.PLAN_ID
, SUP.SR_INSTANCE_ID
, NVL(PE.ROUTING_SEQUENCE_ID
, SUP.ROUTING_SEQUENCE_ID))))
, SUP.PROCESS_SEQ_ID
, SUP.IMPLEMENT_ALTERNATE_BOM
, SUP.IMPLEMENT_ALTERNATE_ROUTING
, SUP.SCHEDULE_GROUP_NAME
, SUP.IMPLEMENT_EMPLOYEE_ID
, MSC_GET_NAME.CFM_ROUTING_FLAG(PE.PLAN_ID
, PE.SR_INSTANCE_ID
, PE.ROUTING_SEQUENCE_ID)
, MSI.BUYER_NAME
, SUP.RELEASE_ERRORS
, SUP.NUMBER1
, SUP.UNIT_NUMBER
, SUP.IMPLEMENT_UNIT_NUMBER
, MSI.DESCRIPTION
, NVL(SUP.PLANNING_GROUP
, '0')
, NVL(MSI.BUYER_NAME
, '0')
, NVL(MSI.PLANNER_CODE
, '0')
, NVL(SUP.PROJECT_ID
, 0)
, NVL(SUP.TASK_ID
, 0)
, NULL
, NULL
, MIC.CATEGORY_SET_ID
, MIC.CATEGORY_NAME
, MSI.PRODUCT_FAMILY_ID
, MSC_GET_NAME.ITEM_NAME(MSI.PRODUCT_FAMILY_ID
, NULL
, NULL
, NULL)
, MSC_GET_NAME.ITEM_NAME(MSI.BASE_ITEM_ID
, NULL
, NULL
, NULL)
, MSI.ABC_CLASS_NAME
, MSI.UOM_CODE
, DECODE(MP.PLAN_TYPE
, 5
, DECODE(SUP.ORDER_TYPE
, 2
, MSC_DRP_UTIL.GET_ISO_NAME(SUP.PLAN_ID
, SUP.SR_INSTANCE_ID
, SUP.TRANSACTION_ID)
, NULL)
, NULL)
, DECODE(MP.PLAN_TYPE
, 5
, TO_NUMBER(NULL)
, SUP.SCHEDULE_PRIORITY)
, SUP.PROMISED_DATE
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, SUP.LOT_NUMBER
, SUP.SUBINVENTORY_CODE
, SUP.PARENT_ID
, SUP.WIP_STATUS_CODE
, MSC_GET_NAME.LOOKUP_MEANING('WIP_JOB_STATUS'
, SUP.WIP_STATUS_CODE)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, SUP.NEED_BY_DATE
, DECODE(SUP.SCHEDULE_DESIGNATOR_ID
, NULL
, NULL
, MSC_GET_NAME.DESIGNATOR(SUP.SCHEDULE_DESIGNATOR_ID))
, MSI.LIST_PRICE
, MSI.STANDARD_COST
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(SUP.DEMAND_CLASS
, '-1'
, NULL
, SUP.DEMAND_CLASS)
, TO_NUMBER(NULL)
, SUP.NEW_SHIP_DATE
, NVL(SUP.DAILY_RATE
, SUP.NEW_ORDER_QUANTITY)
, TO_DATE(NULL)
, NULL
, TO_NUMBER(NULL)
, SUP.PURCH_LINE_NUM
, SUP.RESCHEDULE_DAYS
, DECODE(SUP.PLAN_ID
, -1
, SUP.NEW_DOCK_DATE
, SUP.OLD_NEED_BY_DATE)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, SUP.SHIP_METHOD
, SUP.EXPLOSION_DATE
, TO_NUMBER(NULL)
, SUP.ORIGINAL_QUANTITY
, SUP.ORIGINAL_NEED_BY_DATE
, SUP.EARLIEST_START_DATE
, SUP.EARLIEST_COMPLETION_DATE
, SUP.MIN_START_DATE
, MSI.LOW_LEVEL_CODE
, ROUND(NVL(SUP.EARLIEST_COMPLETION_DATE-SUP.NEED_BY_DATE
, 0)
, 2)
, SUP.ULPSD
, SUP.ULPCD
, SUP.UEPSD
, SUP.UEPCD
, SUP.EACD
, SUP.SUPPLY_IS_SHARED
, SUP.RECORD_SOURCE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, SUP.WIP_START_QUANTITY
, SUP.EXPIRATION_DATE
, MSC_GET_NAME.LOOKUP_MEANING('MRP_PLANNING_CODE'
, MSI.MRP_PLANNING_CODE)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(SUP.ORDER_TYPE
, 3
, DECODE( MSI.ROUNDING_CONTROL_TYPE
, 1
, CEIL(DECODE(SUP.IMPLEMENT_QUANTITY
, NULL
, SUP.NEW_ORDER_QUANTITY
, SUP.IMPLEMENT_QUANTITY)/MSC_REL_PLAN_PUB.GET_REV_CUM_YIELD( SUP.SR_INSTANCE_ID
, SUP.PLAN_ID
, SUP.PROCESS_SEQ_ID
, SUP.TRANSACTION_ID
, SUP.ORGANIZATION_ID))
, DECODE(SUP.IMPLEMENT_QUANTITY
, NULL
, SUP.NEW_ORDER_QUANTITY
, SUP.IMPLEMENT_QUANTITY)/MSC_REL_PLAN_PUB.GET_REV_CUM_YIELD( SUP.SR_INSTANCE_ID
, SUP.PLAN_ID
, SUP.PROCESS_SEQ_ID
, SUP.TRANSACTION_ID
, SUP.ORGANIZATION_ID))
, 5
, DECODE(MSI.PLANNING_MAKE_BUY_CODE
, 1
, DECODE(MSI.ROUNDING_CONTROL_TYPE
, 1
, CEIL(DECODE(SUP.IMPLEMENT_QUANTITY
, NULL
, SUP.NEW_ORDER_QUANTITY
, SUP.IMPLEMENT_QUANTITY)/ MSC_REL_PLAN_PUB.GET_REV_CUM_YIELD(SUP.SR_INSTANCE_ID
, SUP.PLAN_ID
, SUP.PROCESS_SEQ_ID
, SUP.TRANSACTION_ID
, SUP.ORGANIZATION_ID))
, DECODE(SUP.IMPLEMENT_QUANTITY
, NULL
, SUP.NEW_ORDER_QUANTITY
, SUP.IMPLEMENT_QUANTITY)/ MSC_REL_PLAN_PUB.GET_REV_CUM_YIELD(SUP.SR_INSTANCE_ID
, SUP.PLAN_ID
, SUP.PROCESS_SEQ_ID
, SUP.TRANSACTION_ID
, SUP.ORGANIZATION_ID))
, TO_NUMBER(NULL))
, TO_NUMBER(NULL))
, MSI.CRITICAL_COMPONENT_FLAG
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, SUP.SHIPMENT_ID
, SUP.SHIP_CALENDAR
, SUP.RECEIVING_CALENDAR
, SUP.INTRANSIT_CALENDAR
, SUP.INTRANSIT_LEAD_TIME
, SUP.IMPLEMENT_DOCK_DATE
, SUP.IMPLEMENT_SHIP_DATE
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, SUP.DAYS_LATE
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NVL(DECODE(SUP.ORDER_TYPE
, 5
, MSC_UTIL.GET_VMI_FLAG ( SUP.PLAN_ID
, SUP.SR_INSTANCE_ID
, SUP.ORGANIZATION_ID
, SUP.INVENTORY_ITEM_ID
, SUP.SUPPLIER_ID
, SUP.SUPPLIER_SITE_ID)
, SUP.VMI_FLAG)
, 2)
, NVL(MSI.UNIT_WEIGHT
, 0)*NVL(SUP.FIRM_QUANTITY
, SUP.NEW_ORDER_QUANTITY)
, NVL(MSI.UNIT_VOLUME
, 0)*NVL(SUP.FIRM_QUANTITY
, SUP.NEW_ORDER_QUANTITY)
, TO_DATE(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, MSC_GET_NAME.GET_ZONE_NAME(SUP.ZONE_ID
, SUP.SR_INSTANCE_ID)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, NULL
, DECODE(SUP.ORDER_TYPE
, 70
, MSC_GET_NAME.EAM_PARENT_WORK_ORDER (SUP.PLAN_ID
, SUP.SR_INSTANCE_ID
, SUP.TRANSACTION_ID)
, TO_NUMBER(NULL) )
, MSC_GET_NAME.ITEM_NAME (SUP.ASSET_ITEM_ID
, SUP.ORGANIZATION_ID
, SUP.PLAN_ID
, SUP.SR_INSTANCE_ID)
, SUP.ASSET_SERIAL_NUMBER
, SUP.REQUESTED_START_DATE
, SUP.REQUESTED_COMPLETION_DATE
, TO_NUMBER(NULL)
, MSI.LOTS_EXIST
, SUP.IMPLEMENT_SHIP_METHOD
, SUP.ACTUAL_START_DATE
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, MSC_GET_NAME.IS_WITHIN_REL_TIME_FENCE(MP.PLAN_START_DATE
, NVL(SUP.NEW_ORDER_PLACEMENT_DATE
, SUP.FIRM_DATE)
, MSI.RELEASE_TIME_FENCE_CODE
, MSI.CUMULATIVE_TOTAL_LEAD_TIME
, MSI.CUM_MANUFACTURING_LEAD_TIME
, MSI.FULL_LEAD_TIME
, MSI.RELEASE_TIME_FENCE_DAYS) WITHIN_REL_TIME_FENCE
, (NVL(SUP.DAILY_RATE
, SUP.NEW_ORDER_QUANTITY) * MSI.STANDARD_COST) AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, SUP.DELIVERY_PRICE
, TO_NUMBER(NULL) SALES_ORDER_LINE_ID
, TO_NUMBER(NULL) SHIP_SET_ID
, TO_NUMBER(NULL) ARRIVAL_SET_ID
, SUP.ITEM_TYPE_ID
, SUP.ITEM_TYPE_VALUE
, DECODE(MP.PLAN_TYPE
, 8
, MSC_GET_NAME.LOOKUP_MEANING('MSC_PART_CONDITION'
, SUP.ITEM_TYPE_VALUE)
, NULL)
, SUP.FIRM_START_DATE
, SUP.BILL_SEQUENCE_ID
, SUP.ROUTING_SEQUENCE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, SUP.OTM_ARRIVAL_DATE
, TO_NUMBER(NULL)
, MSC_GET_NAME.GET_ORDER_COMMENTS(SUP.PLAN_ID
, 'SUPPLY'
, SUP.TRANSACTION_ID) COMMENTS
, TO_DATE(NULL)
, TO_NUMBER(NULL)
FROM MSC_SUPPLIES SUP
, MFG_LOOKUPS L1
, MSC_SYSTEM_ITEMS MSI
, MSC_ITEM_CATEGORIES MIC
, MSC_PLANS MP
, MSC_PROCESS_EFFECTIVITY PE
WHERE PE.PLAN_ID(+) = SUP.PLAN_ID
AND PE.SR_INSTANCE_ID(+) = SUP.SR_INSTANCE_ID
AND PE.PROCESS_SEQUENCE_ID(+) = SUP.PROCESS_SEQ_ID
AND MIC.SR_INSTANCE_ID = SUP.SR_INSTANCE_ID
AND MIC.ORGANIZATION_ID = SUP.ORGANIZATION_ID
AND MIC.INVENTORY_ITEM_ID = SUP.INVENTORY_ITEM_ID
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 L1.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
AND L1.LOOKUP_CODE = SUP.ORDER_TYPE
AND MP.PLAN_ID = SUP.PLAN_ID UNION ALL SELECT 'MSC_DEMANDS'
, DEM.ROWID
, DEM.DEMAND_ID
, DEM.LAST_UPDATE_DATE
, DEM.LAST_UPDATED_BY
, DEM.CREATION_DATE
, DEM.CREATED_BY
, DEM.LAST_UPDATE_LOGIN
, MSI.INVENTORY_ITEM_ID
, DEM.ORGANIZATION_ID
, MSI.ORGANIZATION_CODE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DEM.SR_INSTANCE_ID
, DEM.PLAN_ID
, NULL
, MSC_GET_NAME.ACTION('MSC_DEMANDS'
, MP.PLAN_TYPE
, DECODE(DEM.PLAN_ID
, -1
, DEM.SUPPLY_ID
, DEM.DISPOSITION_ID)
, DECODE(DEM.SOURCE_ORG_INSTANCE_ID
, NULL
, DEM.SR_INSTANCE_ID
, -23453
, DEM.SR_INSTANCE_ID
, DEM.SOURCE_ORG_INSTANCE_ID)
, DEM.ORIGINATION_TYPE
, DEM.RESCHEDULE_FLAG
, DEM.DEMAND_ID
, NULL
, NULL
, DEM.SALES_ORDER_LINE_SPLIT
, DEM.FILL_KILL_FLAG
, NULL
, DEM.INVENTORY_ITEM_ID
, DEM.PREV_SUBST_ITEM
, NULL
, DEM.PLAN_ID )
, DEM.USING_ASSEMBLY_DEMAND_DATE
, DEM.OLD_DEMAND_DATE
, TO_DATE(NULL)
, DEM.DISPOSITION_ID
, 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)
, 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 ))))
, MSC_GET_NAME.DESIGNATOR(DEM.SCHEDULE_DESIGNATOR_ID)))
, TO_NUMBER(NULL)
, DEM.ORIGINATION_TYPE
, L1.MEANING
, - NVL(DEM.DAILY_DEMAND_RATE
, DEM.USING_REQUIREMENT_QUANTITY)
, DEM.OLD_DEMAND_QUANTITY
, TO_DATE(NULL)
, DECODE(DEM.ORG_FIRM_FLAG
, 1
, 1
, 3
, 2
, 2)
, DEM.RESCHEDULE_FLAG
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DEM.ASSEMBLY_DEMAND_COMP_DATE
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, DEM.FIRM_QUANTITY
, DEM.FIRM_DATE
, TO_NUMBER(NULL)
, NULL
, NULL
, MSI.ITEM_NAME
, DEM.IMPLEMENT_DATE
, TO_NUMBER(NULL)
, NVL(DEM.IMPLEMENT_FIRM
, 2)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DEM.RELEASE_STATUS
, TO_NUMBER(NULL)
, NULL
, MSI.PLANNER_CODE
, DEM.USING_ASSEMBLY_ITEM_ID
, MIC.SR_CATEGORY_ID
, MSI.MRP_PLANNING_CODE
, MSI.REPETITIVE_TYPE
, MSI.BUILD_IN_WIP_FLAG
, MSI.PURCHASING_ENABLED_FLAG
, MSI.PLANNING_MAKE_BUY_CODE
, ROUND(GREATEST(0
, USING_ASSEMBLY_DEMAND_DATE - (TRUNC(SYSDATE) ))
, 2)
, MSI.BOM_ITEM_TYPE
, MSI.BASE_ITEM_ID
, MSI.WIP_SUPPLY_TYPE
, DEM.SOURCE_ORGANIZATION_ID
, DEM.SOURCE_ORG_INSTANCE_ID
, DECODE(DEM.SUPPLY_ID
, NULL
, DECODE(DEM.ORIGINATION_TYPE
, 6
, DECODE(MSC_GET_NAME.ORDER_TYPE(DEM.PLAN_ID
, DEM.DISPOSITION_ID
, DEM.SR_INSTANCE_ID)
, 2
, NULL
, MSC_GET_NAME.ORG_CODE(DEM.SOURCE_ORGANIZATION_ID
, DEM.SOURCE_ORG_INSTANCE_ID))
, 30
, DECODE(MSC_GET_NAME.ORDER_TYPE(DEM.PLAN_ID
, DEM.DISPOSITION_ID
, DEM.SR_INSTANCE_ID)
, 2
, NULL
, MSC_GET_NAME.ORG_CODE(DEM.SOURCE_ORGANIZATION_ID
, DEM.SOURCE_ORG_INSTANCE_ID))
, 1
, DECODE(MP.PLAN_TYPE
, 5
, NULL
, MSC_GET_NAME.ORG_CODE(DEM.SOURCE_ORGANIZATION_ID
, DEM.SOURCE_ORG_INSTANCE_ID))
, MSC_GET_NAME.ORG_CODE(DEM.SOURCE_ORGANIZATION_ID
, DEM.SOURCE_ORG_INSTANCE_ID))
, NULL )
, TO_NUMBER(NULL)
, MSC_GET_NAME.ITEM_NAME(DEM.USING_ASSEMBLY_ITEM_ID
, NULL
, NULL
, NULL)
, MSI.FULL_PEGGING
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, DEM.IMPLEMENT_ORG_ID
, DEM.IMPLEMENT_INSTANCE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(MSI.IN_SOURCE_PLAN
, 1
, 1
, 2)
, TO_NUMBER(NULL)
, DEM.PROJECT_ID
, DEM.TASK_ID
, 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))
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DEM.PLANNING_GROUP
, DEM.STATUS
, DEM.APPLIED
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, MSI.BUYER_NAME
, DEM.RELEASE_ERRORS
, TO_NUMBER(NULL)
, DEM.UNIT_NUMBER
, NULL
, MSI.DESCRIPTION
, NVL(DEM.PLANNING_GROUP
, '0')
, NVL(MSI.BUYER_NAME
, '0')
, NVL(MSI.PLANNER_CODE
, '0')
, NVL(DEM.PROJECT_ID
, 0)
, NVL(DEM.TASK_ID
, 0)
, DECODE(DEM.LENDING_PROJECT_ID
, NULL
, NULL
, MSC_GET_NAME.PROJECT(DEM.LENDING_PROJECT_ID
, DEM.ORGANIZATION_ID
, DEM.PLAN_ID
, DEM.SR_INSTANCE_ID))
, DECODE(DEM.LENDING_TASK_ID
, NULL
, NULL
, MSC_GET_NAME.TASK(DEM.LENDING_TASK_ID
, DEM.LENDING_PROJECT_ID
, DEM.ORGANIZATION_ID
, DEM.PLAN_ID
, DEM.SR_INSTANCE_ID))
, MIC.CATEGORY_SET_ID
, MIC.CATEGORY_NAME
, MSI.PRODUCT_FAMILY_ID
, MSC_GET_NAME.ITEM_NAME(MSI.PRODUCT_FAMILY_ID
, NULL
, NULL
, NULL)
, MSC_GET_NAME.ITEM_NAME(MSI.BASE_ITEM_ID
, NULL
, NULL
, NULL)
, MSI.ABC_CLASS_NAME
, MSI.UOM_CODE
, DECODE(DEM.ORIGINATION_TYPE
, 24
, DEM.ORDER_NUMBER
, NULL)
, DECODE(DEM.PLAN_ID
, -1
, DEM.ORDER_PRIORITY
, DEM.DEMAND_PRIORITY)
, DEM.PROMISE_DATE
, DEM.REQUEST_DATE
, DEM.CUSTOMER_ID
, DECODE( DEM.CUSTOMER_ID
, NULL
, MSC_GET_NAME.GET_OTHER_CUSTOMERS(DEM.PLAN_ID
, DEM.SCHEDULE_DESIGNATOR_ID)
, MSC_GET_NAME.CUSTOMER(DEM.CUSTOMER_ID))
, DEM.CUSTOMER_SITE_ID
, DECODE( DEM.CUSTOMER_SITE_ID
, NULL
, MSC_GET_NAME.GET_OTHER_CUSTOMERS(DEM.PLAN_ID
, DEM.SCHEDULE_DESIGNATOR_ID)
, MSC_GET_NAME.CUSTOMER_SITE(DEM.CUSTOMER_SITE_ID))
, DEM.SHIP_TO_SITE_ID
, MSC_GET_NAME.CUSTOMER_SITE(DEM.SHIP_TO_SITE_ID)
, NULL
, NULL
, DEM.PARENT_ID
, TO_NUMBER(NULL)
, NULL
, DEM.DMD_LATENESS_COST
, DEM.DMD_SATISFIED_DATE
, TO_DATE(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)))
, MSI.LIST_PRICE
, MSI.STANDARD_COST
, DEM.SELLING_PRICE
, DEM.SERVICE_LEVEL
, DEM.DEMAND_CLASS
, DEM.PROBABILITY
, DEM.PLANNED_SHIP_DATE
, -(NVL(DEM.DAILY_DEMAND_RATE
, DEM.USING_REQUIREMENT_QUANTITY)) * NVL(DEM.PROBABILITY
, 1)
, DEM.ASSEMBLY_DEMAND_COMP_DATE
, DECODE(DEM.ORIGINATION_TYPE
, 1
, NULL
, 4
, NULL
, 16
, NULL
, 17
, NULL
, 18
, NULL
, 19
, NULL
, 20
, NULL
, 21
, NULL
, 23
, NULL
, 25
, NULL
, 26
, NULL
, 28
, NULL
, 22
, NULL
, DECODE(DEM.BUCKET_TYPE
, 1
, 'DAYS'
, 2
, 'WEEKS'
, 3
, 'PERIODS'
, NULL) )
, DECODE(DEM.ORIGINATION_TYPE
, 6
, MSC_GET_NAME.SOURCE_DEMAND_PRIORITY( DEM.PLAN_ID
, DEM.DEMAND_ID)
, 7
, MSC_GET_NAME.SOURCE_DEMAND_PRIORITY( DEM.PLAN_ID
, DEM.DEMAND_ID)
, 8
, MSC_GET_NAME.SOURCE_DEMAND_PRIORITY( DEM.PLAN_ID
, DEM.DEMAND_ID)
, 29
, MSC_GET_NAME.SOURCE_DEMAND_PRIORITY( DEM.PLAN_ID
, DEM.DEMAND_ID)
, 30
, MSC_GET_NAME.SOURCE_DEMAND_PRIORITY( DEM.PLAN_ID
, DEM.DEMAND_ID)
, TO_NUMBER(NULL))
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, DEM.CUMMULATIVE_PROBABILITY
, DEM.ORIGINAL_ITEM_ID
, MSC_GET_NAME.ITEM_NAME(DEM.ORIGINAL_ITEM_ID
, NULL
, NULL
, NULL)
, DECODE(DEM.ORIGINAL_ITEM_ID
, NULL
, TO_NUMBER(NULL)
, DECODE(MP.PLAN_TYPE
, 5
, DEM.ORIGINAL_QUANTITY
, DECODE(DEM.ORIGINAL_ITEM_ID
, DEM.INVENTORY_ITEM_ID
, DEM.USING_REQUIREMENT_QUANTITY
, MSC_GET_NAME.DEMAND_QUANTITY(DEM.PLAN_ID
, DEM.SR_INSTANCE_ID
, DEM.DEMAND_ID)) ))
, DEM.SHIP_METHOD
, TO_DATE(NULL)
, DEM.UNMET_QUANTITY
, -1*DEM.ORIGINAL_QUANTITY
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, MSI.LOW_LEVEL_CODE
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, DEM.RECORD_SOURCE
, DECODE(DEM.ORIGINATION_TYPE
, 29
, MSC_GET_NAME.FORWARD_BACKWARD_DAYS(DEM.PLAN_ID
, DEM.SCHEDULE_DESIGNATOR_ID
, 1)
, NULL)
, DECODE(DEM.ORIGINATION_TYPE
, 29
, MSC_GET_NAME.FORWARD_BACKWARD_DAYS(DEM.PLAN_ID
, DEM.SCHEDULE_DESIGNATOR_ID
, 2)
, NULL)
, TO_NUMBER(NULL)
, DEM.EXPIRATION_DATE
, MSC_GET_NAME.LOOKUP_MEANING('MRP_PLANNING_CODE'
, MSI.MRP_PLANNING_CODE)
, DEM.ORIGINAL_ORG_ID
, DEM.ORIGINAL_INST_ID
, TO_NUMBER(NULL)
, MSI.CRITICAL_COMPONENT_FLAG
, DEM.ORDER_DATE_TYPE_CODE
, MSC_GET_NAME.LOOKUP_MEANING('MSC_ORDER_DATE_TYPE_CODE'
, DECODE(DEM.ORDER_DATE_TYPE_CODE
, NULL
, 1
, DEM.ORDER_DATE_TYPE_CODE))
, DEM.SCHEDULE_ARRIVAL_DATE
, DEM.LATEST_ACCEPTABLE_DATE
, DEM.PLANNED_ARRIVAL_DATE
, DECODE(DEM.ORIGINATION_TYPE
, 30
, DECODE(DEM.DEMAND_SOURCE_TYPE
, 8
, MSC_DRP_UTIL.GET_ISO_TRIP(DEM.PLAN_ID
, DEM.SR_INSTANCE_ID
, DEM.DISPOSITION_ID)
, TO_NUMBER(NULL))
, TO_NUMBER(NULL))
, NULL
, NULL
, NULL
, DEM.INTRANSIT_LEAD_TIME
, TO_DATE(NULL)
, DEM.IMPLEMENT_SHIP_DATE
, DEM.IMPLEMENT_ARRIVAL_DATE
, DEM.REQUEST_SHIP_DATE
, DEM.PROMISE_SHIP_DATE
, ROUND(DECODE( SIGN(DEM.DMD_SATISFIED_DATE - DEM.USING_ASSEMBLY_DEMAND_DATE)
, -1
, LEAST(DEM.DMD_SATISFIED_DATE - DEM.USING_ASSEMBLY_DEMAND_DATE
, -0.01)
, 1
, GREATEST(DEM.DMD_SATISFIED_DATE - DEM.USING_ASSEMBLY_DEMAND_DATE
, 0.01)
, 0)
, 2) DAYS_LATE
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DEM.SCHEDULE_SHIP_DATE
, DECODE(MP.PLAN_TYPE
, 5
, DECODE(DEM.ORIGINATION_TYPE
, 1
, MSC_GET_NAME.ORG_CODE(DEM.SOURCE_ORGANIZATION_ID
, DEM.SOURCE_ORG_INSTANCE_ID)
, 24
, MSC_GET_NAME.ORG_CODE(DEM.SOURCE_ORGANIZATION_ID
, DEM.SOURCE_ORG_INSTANCE_ID)
, 30
, DECODE(DEM.DEMAND_SOURCE_TYPE
, 8
, MSC_GET_NAME.ORG_CODE(DEM.SOURCE_ORGANIZATION_ID
, DEM.SOURCE_ORG_INSTANCE_ID)
, NULL)
, NULL)
, NULL)
, DEM.QUANTITY_BY_DUE_DATE*NVL(DEM.PROBABILITY
, 1)
, DEM.SHIP_SET_NAME
, DEM.ARRIVAL_SET_NAME
, MSC_GET_NAME.ORG_CODE(DEM.ORIGINAL_ORG_ID
, DEM.ORIGINAL_INST_ID)
, DEM.ORIG_INTRANSIT_LEAD_TIME
, DEM.ORIG_SHIPPING_METHOD_CODE
, DECODE(MP.PLAN_TYPE
, 5
, DEM.SOURCE_ORGANIZATION_ID
, TO_NUMBER(NULL))
, DECODE(MP.PLAN_TYPE
, 5
, DEM.SOURCE_ORG_INSTANCE_ID
, TO_NUMBER(NULL))
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_DATE(NULL)
, DEM.FINAL_USE_PRIORITY
, DEM.FINAL_USE_DATE
, DECODE(DEM.ZONE_ID
, NULL
, NULL
, MSC_GET_NAME.GET_ZONE_NAME(DEM.ZONE_ID
, DEM.SR_INSTANCE_ID))
, DEM.MANUAL_ALLOCATION_QTY
, DEM.FIRM_ALLOCATION_FLAG
, DEM.MANUAL_ALLOCATION_DATE
, MSC_GET_NAME.LOOKUP_MEANING('SYS_YES_NO'
, NVL(DEM.SALES_ORDER_LINE_SPLIT
, 2))
, TO_NUMBER(NULL)
, MSC_GET_NAME.ITEM_NAME (DEM.ASSET_ITEM_ID
, DEM.ORGANIZATION_ID
, DEM.PLAN_ID
, DEM.SR_INSTANCE_ID)
, DEM.ASSET_SERIAL_NUMBER
, TO_DATE(NULL)
, TO_DATE(NULL)
, DEM.FILL_KILL_FLAG
, MSI.LOTS_EXIST
, NULL
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, MSC_GET_NAME.IS_WITHIN_REL_TIME_FENCE(MP.PLAN_START_DATE
, TO_DATE(NULL)
, MSI.RELEASE_TIME_FENCE_CODE
, MSI.CUMULATIVE_TOTAL_LEAD_TIME
, MSI.CUM_MANUFACTURING_LEAD_TIME
, MSI.FULL_LEAD_TIME
, MSI.RELEASE_TIME_FENCE_DAYS) WITHIN_REL_TIME_FENCE
, ((NVL(DEM.DAILY_DEMAND_RATE
, DEM.USING_REQUIREMENT_QUANTITY)) * NVL(DEM.PROBABILITY
, 1) * MSI.STANDARD_COST) AMOUNT
, NVL(DEM.SALES_ORDER_LINE_SPLIT
, 2)
, DEM.FORECAST_SET_ID
, DEM.SCHEDULE_DESIGNATOR_ID
, TO_NUMBER(NULL)
, DEM.SALES_ORDER_LINE_ID
, DEM.SHIP_SET_ID
, DEM.ARRIVAL_SET_ID
, DEM.ITEM_TYPE_ID
, DEM.ITEM_TYPE_VALUE
, DECODE(MP.PLAN_TYPE
, 8
, MSC_GET_NAME.LOOKUP_MEANING('MSC_PART_CONDITION'
, DEM.ITEM_TYPE_VALUE)
, NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DEM.PREV_SUBST_ITEM
, DEM.PREV_SUBST_ORG
, MSC_GET_NAME.ITEM_NAME(DEM.PREV_SUBST_ITEM
, NULL
, NULL
, NULL)
, MSC_GET_NAME.ORG_CODE(DEM.PREV_SUBST_ORG
, DEM.SR_INSTANCE_ID)
, DEM.OTM_ARRIVAL_DATE
, DEM.MIN_REM_SHELF_LIFE_DAYS
, NULL COMMENTS
, TO_DATE(NULL)
, TO_NUMBER(NULL)
FROM MSC_DEMANDS DEM
, MSC_PLANS MP
, MFG_LOOKUPS L1
, MSC_SYSTEM_ITEMS MSI
, MSC_ITEM_CATEGORIES MIC
WHERE ( 'MSC_DEMAND_ORIGINATION') = L1.LOOKUP_TYPE
AND L1.LOOKUP_CODE = DEM.ORIGINATION_TYPE
AND MIC.SR_INSTANCE_ID = DEM.SR_INSTANCE_ID
AND MIC.ORGANIZATION_ID = DEM.ORGANIZATION_ID
AND MIC.INVENTORY_ITEM_ID = DEM.INVENTORY_ITEM_ID
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 <> 52
AND MP.PLAN_ID = DEM.PLAN_ID