工艺路线导入

-- CREATE TEMP TABLE FOR ROUTINGS...

CREATE TABLE DKC_ROUTINGS_STG
(
  OWNER_ORGN_CODE  VARCHAR2(4),
  ROUTING_NO       VARCHAR2(32),
  ROUTING_VERSION  NUMBER(5),
  ROUTING_QTY      NUMBER,
  ITEM_UOM         VARCHAR2(4),
  ROUTING_DESC     VARCHAR2(40),
  OWNER            VARCHAR2(10),
  ROUTING_CLASS    VARCHAR2(4),
  STEP_NO          NUMBER,
  STEP_QTY         NUMBER,
  OPERATION        VARCHAR2(16),
  PLANNED_LOSS     NUMBER,
  PROCESS_STATUS   CHAR(1),
  ERROR_TEXT       VARCHAR2(2000 )
)

/

--- ROUTING GENERATION API

DECLARE

  l_message_count           number;
  l_message_list            varchar2(2000);
  l_return_status           varchar2(1) ;
  l_user_id                 number := 1130;   -- fnd_global.user_id;
  l_owner_id                number;
  l_owner_organization_id   number;
  l_operation_id            varchar2 (16) ;
  gc_routing_status         number := 700;
  l_tbl_count               number;
  v_routing_rec_type        gmd_routings%rowtype;
  v_routing_step_tbl_type   gmd_routings_pub.gmd_routings_step_tab;
  v_update_tbl_rec_type     gmd_routings_pub.gmd_routings_step_dep_tab;
  v_routing_step_tbl_dummy  gmd_routings_pub.gmd_routings_step_tab;
  v_update_tbl_rec_dummy    gmd_routings_pub.gmd_routings_step_dep_tab;
  l_rej_rec_cnt             number;
  l_suc_rec_cnt             number;
  l_out_index               number :=0;
  l_owner                   number:=1130;

cursor c_rtg_hdr is
select distinct owner_orgn_code
  ,routing_no
  ,routing_version
  ,routing_qty
  ,item_uom
  ,routing_desc
  ,owner
  ,routing_class
from dkc_routings_stg
where  owner_orgn_code = '404'
 AND   routing_no  not in (select routing_no from gmd_routings)
and operation  in (select oprn_no from gmd_operations)
order by routing_no;

cursor routing_cur(p_routing_no  varchar2,p_routing_ver number)
is
select distinct
    step_no
   ,step_qty
   ,routing_version
   ,operation
from dkc_routings_stg
where routing_no = p_routing_no
and routing_version = p_routing_ver
and nvl(process_status,'E') IN('E','U')
order by step_no;

l_temp varchar2(50);

BEGIN
     FND_GLOBAL.APPS_INITIALIZE('1130', '50886','552');

dbms_output.put_line('l_user_id '||l_user_id);

FOR routing_cur_rec IN c_rtg_hdr LOOP
  v_routing_step_tbl_type  :=v_routing_step_tbl_dummy;
  v_update_tbl_rec_type    :=v_update_tbl_rec_dummy;

  dbms_output.put_line('ROUTING NO '||routing_cur_rec.routing_no);

  BEGIN
  SELECT user_id
  INTO l_owner_id
  FROM fnd_user
  WHERE user_name = routing_cur_rec.owner;
     EXCEPTION
       WHEN NO_DATA_FOUND THEN
       fnd_file.put_line(fnd_file.log,'NO DATA ERROR: WHILE GETTING OWNER ID:');
       WHEN OTHERS THEN
       fnd_file.put_line(fnd_file.log,'OTHERS ERROR: WHILE GETTING OWNER ID:'||SUBSTR (SQLERRM,1,200) );
     END;
  begin

  select organization_id
  into l_owner_organization_id
  from mtl_parameters
  where organization_code =routing_cur_rec.owner_orgn_code;

  dbms_output.put_line('l_owner_organization_id '||l_owner_organization_id);

  exception when others then
  dbms_output.put_line('ERROR OCCURED WHILE PICKING DATA FOR ORGANIZATION');
  end;
  v_routing_rec_type.OWNER_ORGANIZATION_ID  := 107; --l_owner_organization_id;
  v_routing_rec_type.routing_no             := routing_cur_rec.routing_no;
  v_routing_rec_type.routing_vers           := routing_cur_rec.routing_version;
  v_routing_rec_type.routing_qty            := routing_cur_rec.routing_qty;
  v_routing_rec_type.routing_uom            := routing_cur_rec.item_uom;
  v_routing_rec_type.routing_class          := routing_cur_rec.routing_class;
  v_routing_rec_type.creation_date          := sysdate;
  v_routing_rec_type.created_by             := 1130;
  v_routing_rec_type.last_update_date       := sysdate;
  v_routing_rec_type.last_updated_by        := 1130;--l_user_id;
  v_routing_rec_type.owner_id               := 1130; --l_owner_id;
  v_routing_rec_type.routing_status         := 100;
  v_routing_rec_type.routing_desc           := routing_cur_rec.routing_desc;
  v_routing_rec_type.delete_mark            := 0;
  l_tbl_count                               := 1;

 --fnd_file.put_line(fnd_file.log,'v_routing_rec_type.routing_qty values is :'||v_routing_rec_type.routing_qty);

For rotg_dtl_rec IN routing_cur(routing_cur_rec.routing_no,routing_cur_rec.routing_version) Loop
 Begin
     BEGIN
         SELECT oprn_id
         INTO l_operation_id
         FROM gmd_operations_vl
         WHERE oprn_no = rotg_dtl_rec.operation
         and oprn_vers = rotg_dtl_rec.routing_version;
         dbms_output.put_line('l_operation_id '||l_operation_id);

     EXCEPTION
       WHEN NO_DATA_FOUND THEN
       fnd_file.put_line(fnd_file.log,'NO DATA ERROR: WHILE GETTING OPERATION ID:');
       WHEN OTHERS THEN
       fnd_file.put_line(fnd_file.log,'OTHERS ERROR: WHILE GETTING OPERATION ID:'||SUBSTR (SQLERRM,1,200) );
     END;

  v_routing_step_tbl_type(l_tbl_count).routingstep_no  := rotg_dtl_rec.step_no;
  v_routing_step_tbl_type(l_tbl_count).oprn_id         := 107; -- l_operation_id;
  v_routing_step_tbl_type(l_tbl_count).step_qty        := rotg_dtl_rec.step_qty;
  v_routing_step_tbl_type(l_tbl_count).last_updated_by := 1130; --l_user_id;
  v_routing_step_tbl_type(l_tbl_count).created_by      := 1130; --l_user_id;
  v_routing_step_tbl_type(l_tbl_count).last_update_date:= sysdate;
  v_routing_step_tbl_type(l_tbl_count).creation_date   := sysdate;
  l_tbl_count := l_tbl_count + 1;

 End;
End Loop;
l_temp := routing_cur_rec.routing_no;

   FND_GLOBAL.APPS_INITIALIZE('1130', '50886','552');

  gmd_routings_pub.insert_routing ( p_api_version            =>  1
      , p_init_msg_list          =>  TRUE
      , p_commit                 =>  TRUE
      , p_routings               =>  v_routing_rec_type  --v_routing_stg_tbl_type
      , p_routings_step_tbl      =>  v_routing_step_tbl_type --v_routing_step_stg_tbl_type
      , p_routings_step_dep_tbl  =>  v_update_tbl_rec_type
      , x_message_count          =>  l_message_count
      , x_message_list           =>  l_message_list
      , x_return_status          =>  l_return_status
                                  );
     dbms_output.put_line('l_return_status  '||l_return_status);
     dbms_output.put_line('l_message_list  '||l_message_list);

        update dkc_routings_stg
        SET process_status = l_return_status
          , error_text = l_message_list
        WHERE routing_no = routing_cur_rec.routing_no
        AND routing_version = routing_cur_rec.routing_version;

    fnd_file.put_line(fnd_file.log,'Return Status is : '||l_return_status||''||'Error Message is:'||l_message_list);

    IF l_message_count > 0 AND l_return_status <> 'S' THEN
        fnd_file.put_line(fnd_file.log,'Error Message in INSERTION OF ROUTING :'||SUBSTR(SQLERRM, 1, 255));
    END IF;
  IF l_return_status ='S' THEN
    Begin
      Update gmd_routings_b
     set routing_status = 700
     where routing_no = routing_cur_rec.routing_no
     and routing_vers = routing_cur_rec.routing_version;
      Exception when Others Then
     Null;
    End;

 ELSif l_return_status  in ('U','E') THEN
   For i IN 1 .. l_message_count Loop
                FND_MSG_PUB.get(p_msg_index     => i,
                                p_encoded       => 'F',
                                p_data          => l_message_list,
                                p_msg_index_out => l_out_index);
                dbms_output.put_line('l_message_list = '||l_message_list);
   end loop;
 END IF;
END LOOP;
COMMIT;
    EXCEPTION
         WHEN NO_DATA_FOUND THEN
         fnd_file.put_line(fnd_file.log,'No Data Found Exception Message: ');
         WHEN OTHERS THEN
        fnd_file.put_line(fnd_file.log,'Error Message: ' || SUBSTR(SQLERRM, 1, 255));
    END;