MSC Plan Order Upload

select
mov.*
from
(
select
 null                                            processing_action,
 null                                            processing_status,
 null                                            processing_message,
 null                                            process_request_id,
 nvl(:p_instance_code,mai.instance_code)         instance,
 nvl(:p_plan_name,mp.compile_designator)         plan,
 mpo.organization_code                           organization,
 msi.planner_code                                planner,
 msi.buyer_name                                  buyer_name,
 --
 msc_get_name.lookup_meaning ('MTL_PLANNING_MAKE_BUY',msi.planning_make_buy_code) make_buy,
 msi.item_name item,
 msi.description item_description,
 nvl(:p_category_set_name,(select mcs.category_set_name from msc_category_sets mcs where sr_instance_id = mov.sr_instance_id and mcs.default_flag = 1)) category_set,
 mic.category_name category,
 (select distinct
    max(mss.safety_stock_quantity) keep (dense_rank last order by mss.period_start_date) over (partition by mss.organization_id,mss.inventory_item_id) safety_stock
  from
   msc_safety_stocks mss
  where
      mss.sr_instance_id     = mov.sr_instance_id
  and mss.plan_id            = mov.plan_id
  and mss.organization_id    = mov.organization_id
  and mss.inventory_item_id  = mov.inventory_item_id
  and mss.period_start_date <= sysdate
 ) safety_stock,
 --
 xxen_util.meaning('SUPPLY','MSC_QUESTION_TYPE',3) supply_demand,
 decode(mp.plan_type
       , 8,decode(mov.order_type
                 , 1, msc_get_name.lookup_meaning('SRP_CHANGED_ORDER_TYPE',mov.order_type)
                 , 2, decode(mov.source_organization_id, null, msc_get_name.lookup_meaning('SRP_CHANGED_ORDER_TYPE',mov.order_type), msc_get_name.lookup_meaning('MRP_ORDER_TYPE',53))
                 ,51, msc_get_name.lookup_meaning('SRP_CHANGED_ORDER_TYPE',mov.order_type)
                    , msc_get_name.lookup_meaning('MRP_ORDER_TYPE',decode(mov.order_type,92,70,mov.order_type))
                 )
       ,msc_get_name.lookup_meaning('MRP_ORDER_TYPE',decode(mov.order_type,92,70,mov.order_type))
       ) order_type,
 msc_get_name.supply_order_number (mov.order_type ,mov.order_number ,mov.plan_id ,mov.sr_instance_id ,mov.transaction_id ,mov.disposition_id) order_number,
 to_char(trunc(mov.need_by_date),'DD-Mon-YYYY')  need_by_date,
 to_char(trunc(mov.promised_date),'DD-Mon-YYYY')  promise_date,
 to_char(trunc(mov.old_schedule_date),'DD-Mon-YYYY')  old_due_date,
 to_char(trunc(mov.new_schedule_date),'DD-Mon-YYYY')  suggested_due_date,
 nvl(mov.daily_rate,mov.new_order_quantity) quantity,
 msc_get_name.action('MSC_SUPPLIES', msi.bom_item_type, msi.base_item_id, msi.wip_supply_type, mov.order_type, mov.reschedule_flag, mov.disposition_status_type, mov.new_schedule_date, mov.old_schedule_date, mov.implemented_quantity, mov.quantity_in_process, mov.new_order_quantity, msi.release_time_fence_code, mov.reschedule_days, mov.firm_quantity, mov.plan_id, msi.critical_component_flag, msi.mrp_planning_code, msi.lots_exist, mov.item_type_value, mov.transaction_id) action,
 --
 to_char(nvl(mov.implement_date,nvl2(:p_default_impl_cols,
      xxen_msc_rel_plan_api.implement_date
      ( mov.sr_instance_id
      , mov.plan_id
      , mp.plan_type
      , mov.organization_id
      , 'MSC_SUPPLIES'
      , mov.transaction_id
      , mov.order_type
      , -- implement_as
        nvl(mov.implement_as
           ,xxen_msc_rel_plan_api.implement_as
            ( mov.sr_instance_id
            , mp.plan_type
            , 'MSC_SUPPLIES'
            , mov.order_type
            , mov.organization_id
            , msi.purchasing_enabled_flag
            , msi.planning_make_buy_code
            , msi.build_in_wip_flag
            , msi.repetitive_type
            , mov.source_sr_instance_id
            , mov.source_organization_id
            , mov.source_supplier_id
            , null -- dest_inst_id
            , null -- dest_org_id
            ))
      , mov.new_schedule_date
      , mov.last_unit_completion_date
      , mov.firm_date
      , mov.new_ship_date
      , null -- dmd_satisfied_date
     ),null)),'DD-Mon-YYYY')                      implement_date,
 nvl(nvl(mov.implement_daily_rate,mov.implement_quantity),nvl2(:p_default_impl_cols,
      xxen_msc_rel_plan_api.implement_qty_rate
      ( mov.sr_instance_id
      , mov.plan_id
      , 'MSC_SUPPLIES'
      , mov.transaction_id
      , mov.order_type
      , mov.disposition_status_type
      , mov.original_quantity
      , nvl(mov.daily_rate,mov.new_order_quantity)
      , mov.firm_quantity
      , mov.quantity_in_process
      , mov.implemented_quantity
     ),null))                                    implement_quantity,
 nvl(decode(mov.implement_as,null,null,msc_get_name.lookup_meaning('MRP_WORKBENCH_IMPLEMENT_AS',mov.implement_as))
    ,nvl2(:p_default_impl_cols,
      xxen_util.meaning(xxen_msc_rel_plan_api.implement_as
      ( mov.sr_instance_id
      , mp.plan_type
      , 'MSC_SUPPLIES'
      , mov.order_type
      , mov.organization_id
      , msi.purchasing_enabled_flag
      , msi.planning_make_buy_code
      , msi.build_in_wip_flag
      , msi.repetitive_type
      , mov.source_sr_instance_id
      , mov.source_organization_id
      , mov.source_supplier_id
      , null -- dest_inst_id
      , null -- dest_org_id
      ),'MRP_WORKBENCH_IMPLEMENT_AS',700)
     ,null))                                     implement_as,
 null                                            update_release_status,
 --
 case
 when  mov.release_status = 1
 then  'Selected'
 when  mov.reschedule_flag = 1
 and   nvl(mov.implement_daily_rate,mov.implement_quantity) = 0
 then  'Cancelled'
 when  mov.reschedule_flag = 1
 then  'Recheduled'
 when  mov.implemented_quantity is not null
 then  'Released'
 else  null
 end                                             released_status,
 mov.implemented_quantity                        implemented_quantity,
 --
 decode(mov.firm_planned_type, 1, 'Y',null)      firm,
 to_char(trunc(mov.firm_date),'DD-Mon-YYYY')     firm_date,
 mov.firm_quantity                               firm_quantity,
 --
 msc_get_name.org_code(mov.source_organization_id,mov.source_sr_instance_id) source_organization,
 msc_get_name.supplier(mov.supplier_id) supplier,
 msc_get_name.supplier_site(mov.supplier_site_id) supplier_site,
 --
 mov.reschedule_days                             reschedule_days,
 decode(mp.plan_type, 5, to_number(null), mov.schedule_priority) order_priority,
 --
 'SUPPLY' supply_demand_code,
 mov.transaction_id,
 mov.sr_instance_id,
 mov.plan_id,
 mov.organization_id,
 mov.inventory_item_id,
 mic.category_set_id category_set_id,
 mov.release_status
from
 msc_supplies           mov,
 msc_apps_instances     mai,
 msc_plans              mp,
 msc_plan_organizations mpo,
 msc_system_items       msi,
 msc_item_categories    mic
where
    mov.sr_instance_id              = mai.instance_id
and mov.plan_id                     = mp.plan_id
and mov.sr_instance_id              = mpo.sr_instance_id
and mov.plan_id                     = mpo.plan_id
and mov.organization_id             = mpo.organization_id
and mov.sr_instance_id              = msi.sr_instance_id
and mov.plan_id                     = msi.plan_id
and mov.organization_id             = msi.organization_id
and mov.inventory_item_id           = msi.inventory_item_id
and mov.sr_instance_id              =  mic.sr_instance_id
and mov.inventory_item_id           = mic.inventory_item_id
and mov.organization_id             = mic.organization_id
and mic.category_set_id             = nvl(:p_category_set_id,(select mcs.category_set_id from msc_category_sets mcs where sr_instance_id = mov.sr_instance_id and mcs.default_flag = 1))
 
and nvl(nvl(mov.daily_rate,mov.new_order_quantity),0) != 0
and nvl(mov.implemented_quantity,0) = 0
and (   xxen_util.lookup_code(msc_get_name.action('MSC_SUPPLIES', msi.bom_item_type, msi.base_item_id, msi.wip_supply_type, mov.order_type, mov.reschedule_flag, mov.disposition_status_type, mov.new_schedule_date, mov.old_schedule_date, mov.implemented_quantity, mov.quantity_in_process, mov.new_order_quantity, msi.release_time_fence_code, mov.reschedule_days, mov.firm_quantity, mov.plan_id, msi.critical_component_flag, msi.mrp_planning_code, msi.lots_exist, mov.item_type_value, mov.transaction_id)
                             ,'MRP_ACTIONS',700) in (2,3,4) -- Reschedule In, Reschedule Out, Release
     or mov.release_status = 1
    )
and 1=1
) mov
where
2=2
--
&not_use_first_block
&report_table_select &report_table_name &report_table_where_clause &success_records1 &success_records2
&processed_run
--
order by
 instance,
 plan,
 organization,
 planner,
 item,
 suggested_due_date,
 order_type,
 order_number