工序导入

--- CREATE TEMP TABLE FOR Operation

CREATE TABLE DKC_OPRN_STG
(
  OPRN_NO                 VARCHAR2(16),
  OPRN_DESC               VARCHAR2(40),
  OPRN_VERS               NUMBER(5),
  ORGANIZATION_CODE       VARCHAR2(4,
  INVENTORY_ORG           NUMBER,
  PROCESS_QTY_UM          VARCHAR2(4),
  PROCESS_QTY_UOM         VARCHAR2(3),
  MINIMUM_TRANSFER_QTY    NUMBER,
  OPRN_CLASS              VARCHAR2(4),
  ACTIVITY                VARCHAR2(16),
  OFFSET_INTERVAL         NUMBER,
  ACTIVITY_FACTOR         NUMBER,
  SEQUENCE_DEPENDENT_IND  NUMBER(5),
  BREAK_IND               NUMBER(5),
  MAX_BREAK               NUMBER,
  MATERIAL_IND            NUMBER,
  RESOURCES               VARCHAR2(16),
  RESOURCE_USAGE          NUMBER,
  RESOURCE_COUNT          NUMBER,
  USAGE_UM                VARCHAR2(4,
  PROCESS_QTY             NUMBER,
  PROCESS_UOM             VARCHAR2(4),
  PRIM_RSRC_IND           NUMBER,
  SCALE_TYPE              NUMBER,
  COST_ANALYSIS_CODE      VARCHAR2(10),
  COST_CMPNTCLS_ID        NUMBER,
  RES_OFFSET_INTERVAL     NUMBER,
  PROCESS_STATUS          CHAR(1),
  ERROR_MESSAGE           VARCHAR2(2000),
  RES_PROCESS_STATUS      CHAR(1),
  RES_ERROR_MESSAGE       VARCHAR2(2000),
  OPRN_LINE_ID            NUMBER
)
/

-- operation generation api

Declare

 l_operations             gmd_operations%ROWTYPE;
 l_oprn_act_tbl           GMD_OPERATIONS_PUB.GMD_OPRN_ACTIVITIES_TBL_TYPE;
 l_oprn_inst_tbl_dummy    GMD_OPERATIONS_PUB.GMD_OPRN_ACTIVITIES_TBL_TYPE;
 l_oprn_res_tbl           GMD_OPERATION_RESOURCES_PUB.GMD_OPRN_RESOURCES_TBL_TYPE;
 l_return_status          Varchar2(10);
 l_msg_count              Number;
 l_msg_data               Varchar2(240);
 l_version                Number:= 3;
 l_my_text                Varchar2(2000);
 l_my_index               Number;
 l_user_id                Number:= 1130;
 l_responsibility_id      Number := 50649;
 l_responsibility_app_id  Number:= 552;
 l_out_index              Number :=0;
 l_user_name              Varchar2(50) := 'KAUSHIKB';
 l_count                  Number;
 l_act_count              Number;
 l_rec_count              Number;
 l_suc_rec_cnt            Number := 0;
 l_rej_rec_cnt            Number := 0;
 l_organization_id        Number :=0;
 l_return_sts             boolean;
 l_oprn_id                number;
 l_oprn_no                varchar2(50);
 l_oprn_vers              number;
 test                     number;
 test1                    varchar2(2000);
 test2                    varchar2(2000);

-- All operations from staging table
 cursor c_oprn_main is
  select distinct
    oprn_no,
    oprn_desc,
    oprn_vers,
    organization_code,
    inventory_org
  from dkc_oprn_stg
  where nvl(PROCESS_STATUS,'E') = 'E'
   AND OPRN_NO NOT IN (SELECT OPRN_NO FROM GMD_OPERATIONS)
   order by oprn_no;

   -- Operations
  cursor c_oprn_all(p_oprn_no  varchar2,p_oprn_ver number) is
  select distinct
    oprn_no,
    oprn_desc,
    oprn_vers,
    process_qty_um,
    minimum_transfer_qty,
    oprn_class,
    organization_code,
    inventory_org
  from  dkc_oprn_stg
  where  oprn_no = p_oprn_no
    and oprn_vers = p_oprn_ver
    order by oprn_no;

-- All activities  from staging table for a particular operation
 cursor c_oprn_activities(p_oprn_no  varchar2,p_oprn_ver number) is
  select  distinct
    oprn_no,
    activity               ,
    offset_interval        ,
    activity_factor        ,
    sequence_dependent_ind ,
    break_ind              ,
    max_break              ,
    material_ind
  from dkc_oprn_stg
  where oprn_no is not null
    and oprn_no = p_oprn_no
    and oprn_vers = p_oprn_ver
    and activity != 'COSTING'
    order by sequence_dependent_ind;

-- All resources from staging table for a particular Operation Activity
 cursor c_oprn_resource(p_oprn_no varchar2,p_oprn_ver number,p_activity varchar2) is
  select  distinct
          oprn_no
        , activity
        , resources
        , resource_usage
        , resource_count
        , usage_um
        , process_qty
        , process_uom
        , prim_rsrc_ind
        , scale_type
        , cost_analysis_code
        , cost_cmpntcls_id
        , res_offset_interval
        , oprn_line_id
  from    dkc_oprn_stg
  where oprn_no   = p_oprn_no
    and oprn_vers = p_oprn_ver
    and activity  = p_activity
    and activity != 'COSTING';
BEGIN

   FND_PROFILE.PUT ('USER_ID',1130);
   FND_GLOBAL.APPS_INITIALIZE('1130', '50649','552');
   l_return_sts := gmigutl.setup ('OPM');
    For for_rec in c_oprn_main Loop
      Begin
        -- Initializing Counter variable to 1
          l_count     := 1;
          l_rec_count := 1;
          l_act_count := 1;
          begin
          select organization_id
          into l_organization_id
          from mtl_parameters
          where organization_code = for_rec.inventory_org;

          exception when others then
          Null;
          end;
          l_oprn_act_tbl := l_oprn_inst_tbl_dummy;
          -- Assigning the Product Information
           FND_MSG_PUB.delete_msg;
           For oprn in c_oprn_all(for_rec.oprn_no,for_rec.oprn_vers) Loop
             Begin
            fnd_file.put_line (fnd_file.LOG,' Operation :: '||' Line :: '||' Count :: '||l_count);
            l_operations.oprn_no                := oprn.oprn_no;
            l_operations.oprn_vers              := oprn.oprn_vers;
            l_operations.oprn_desc              := oprn.oprn_desc;
            l_operations.oprn_class             := oprn.oprn_class;
            l_operations.process_qty_uom        := oprn.process_qty_um;
            l_operations.effective_start_date   := TO_DATE ('01-JAN-2011', 'DD-MON-RRRR');
            l_operations.owner_organization_id  := l_organization_id;
            l_operations.operation_status       := '700';
            l_operations.creation_date          := sysdate; --TO_DATE ('01-APR-2010', 'DD-MON-RRRR');
            l_operations.last_update_date       := sysdate;  --TO_DATE ('01-APR-2010', 'DD-MON-RRRR');
            l_operations.created_by             := 1130; --l_user_id;
            l_operations.last_updated_by        := 1130; --l_user_id;
            l_operations.delete_mark            := 0;

            --   fnd_file.put_line (fnd_file.LOG,'Ingredient UOM :: '||l_ing_uom_code);
            --FND_GLOBAL.APPS_INITIALIZE('1111', '51279','552');

               for act in c_oprn_activities(oprn.oprn_no,oprn.oprn_vers) loop
               -- l_act_count :=1;
                begin
                dbms_output.put_line('ACTIVITY '||l_act_count||' -> '||act.activity);
                l_oprn_act_tbl(l_act_count).activity         := act.activity;
                l_oprn_act_tbl(l_act_count).offset_interval  := 0.0000;
                l_oprn_act_tbl(l_act_count).activity_factor  := act.activity_factor;
                l_oprn_act_tbl(l_act_count).sequence_dependent_ind := act.sequence_dependent_ind;
                l_oprn_act_tbl(l_act_count).break_ind        := act.break_ind;
                l_oprn_act_tbl(l_act_count).max_break        := act.max_break;
                l_oprn_act_tbl(l_act_count).material_ind     := act.material_ind;
                l_oprn_act_tbl(l_act_count).delete_mark      := 0;
                l_oprn_act_tbl(l_act_count).creation_date    := sysdate; --TO_DATE ('01-APR-2010', 'DD-MON-RRRR');
                l_oprn_act_tbl(l_act_count).last_update_date := sysdate; --TO_DATE ('01-APR-2010', 'DD-MON-RRRR');
                l_oprn_act_tbl(l_act_count).created_by       := 1130; --l_user_id;
                l_oprn_act_tbl(l_act_count).last_updated_by  := 1130; --l_user_id;
                 for res in c_oprn_resource(for_rec.oprn_no,for_rec.oprn_vers,act.activity) loop
                    --l_rec_count:=1;
                      begin
                   l_oprn_res_tbl(l_rec_count).ACTIVITY           := res.ACTIVITY;
                       l_oprn_res_tbl(l_rec_count).resources           := res.resources;
                       l_oprn_res_tbl(l_rec_count).resource_usage      := res.resource_usage;
                       l_oprn_res_tbl(l_rec_count).resource_count      := 1;
                       l_oprn_res_tbl(l_rec_count).resource_usage_uom  := res.usage_um;
                       l_oprn_res_tbl(l_rec_count).process_qty         := res.process_qty;
                       l_oprn_res_tbl(l_rec_count).RESOURCE_PROCESS_UOM:= res.process_uom;
                       l_oprn_res_tbl(l_rec_count).prim_rsrc_ind       := res.prim_rsrc_ind;
                       l_oprn_res_tbl(l_rec_count).scale_type          := res.scale_type;
                       l_oprn_res_tbl(l_rec_count).cost_analysis_code  := res.cost_analysis_code;
                       l_oprn_res_tbl(l_rec_count).cost_cmpntcls_id    := res.cost_cmpntcls_id;
                       l_oprn_res_tbl(l_rec_count).OFFSET_INTERVAL     := 0.0000; -- res.res_offset_interval;
                       l_oprn_res_tbl(l_rec_count).oprn_line_id        := l_rec_count ;   -- res.oprn_line_id;
                       l_rec_count:= l_rec_count + 1;
                        end;
                    end loop;
                 l_act_count:= l_act_count + 1;
                 end;
                end loop;
            End;
            End Loop;
             --|---------------------------------------------------------------------------------------------------------
             --|----  assigning the formula and Ingredients values
             --|---------------------------------------------------------------------------------------------------------
            FND_GLOBAL.APPS_INITIALIZE('1130', '50649','552');

            gmd_operations_pub.insert_operation (p_api_version => 1.0,
                                         p_init_msg_list => TRUE,
                                         p_commit        => TRUE,
                                         p_operations    => l_operations,
                                         p_oprn_actv_tbl => l_oprn_act_tbl,
                                         x_message_count => l_msg_count,
                                         x_message_list  => l_msg_data,
                                         x_return_status => l_return_status,
                                         p_oprn_rsrc_tbl => l_oprn_res_tbl);
            Commit;
            If l_return_status <> 'S' Then
                 l_rej_rec_cnt := l_rej_rec_cnt + 1;
                For i IN 1 .. l_msg_count Loop
                FND_MSG_PUB.get(p_msg_index => i,
                       p_encoded   => 'F',
                       p_data      => l_msg_data,
                       p_msg_index_out => l_out_index);
                dbms_output.put_line('l_msg_data'||l_msg_data);
                End Loop;
            Else
            l_suc_rec_cnt := l_suc_rec_cnt + 1;
            End If;
           Begin
           Update dkc_oprn_stg
           Set  process_status = l_return_status
                ,error_message      = l_msg_data
           Where oprn_no = for_rec.oprn_no
           and oprn_vers = for_rec.oprn_vers;
            Exception When Others Then
            Null;
            End;
        End;
    l_count    := l_count     + 1;
    dbms_output.put_line('OPERATIONS '||l_count||'- > '||for_rec.oprn_no);
    Commit;
  End loop;
  fnd_file.put_line (fnd_file.LOG,' ');
  fnd_file.put_line (fnd_file.LOG,' +----------------------------------------------------------+');
  fnd_file.put_line (fnd_file.LOG,' | Total records processed :           '||l_suc_rec_cnt);
  dbms_output.put_line('Total records processed :           '||l_suc_rec_cnt);
  dbms_output.put_line('Total records rejected  :           '||l_rej_rec_cnt);

  fnd_file.put_line (fnd_file.LOG,' | Total records rejected  :           '||l_rej_rec_cnt);
  fnd_file.put_line (fnd_file.LOG,' +----------------------------------------------------------+');
  fnd_file.put_line (fnd_file.LOG,' ');
end;