处方导入

-- create temp table for recipes

  CREATE TABLE DKC_RECIPE_STG
(
  RECIPE_NO                VARCHAR2(32),
  RECIPE_VER               NUMBER(5),
  RECIPE_DESC              VARCHAR2(70),
  RECIPE_STATUS            VARCHAR2(30),
  ROUTING_NO               VARCHAR2(32),
  ROUTING_VER              NUMBER(5),
  FORMULA_NO               VARCHAR2(32),
  FORMULA_VER              NUMBER,
  ORGN_CODE                VARCHAR2(4),
  PLANNED_PROCESS_LOSS     NUMBER,
  CALCULATE_STEP_QUANTITY  NUMBER,
  PROCESS_FLAG             CHAR(1),
  ERROR_TEXT               VARCHAR2(2000),
  ERROR_COUNT              NUMBER,
  ITEM_CODE                VARCHAR2(15 ),
  ITEM_QTY                 NUMBER
)
/

 

-- Write a pl/sql block or procedure to upload the recipes in OPM

Declare
 l_recp_hdr_tbl             gmd_recipe_header.recipe_tbl;
 l_recp_hdr_tbl_dummy       gmd_recipe_header.recipe_tbl;
 l_recp_hdr_flex            gmd_recipe_header.recipe_flex;
 l_recp_tbl                 gmd_recipe_detail.recipe_vr_tbl;
 l_recp_tbl_dummy           gmd_recipe_detail.recipe_vr_tbl;
 x_status                   varchar2(30);
 x_msg_cnt                  number;
 x_msg_dat                  varchar2(1000);
 l_row_cnt                  number := 1;
 l_login                    number := 1130;  -- fnd_global.login_id;
 l_formula_id               number;
 l_routing_id               number;
 x_return_status            varchar2(1);
 x_msg_data                 varchar2(240);
 x_msg_count                number;
 l_recipe_flex_tbl          gmd_recipe_detail.recipe_flex;
 l_recipe_up_flex           gmd_recipe_detail.recipe_update_flex;
 l_my_cnt                   number := 1;
 l_create_type              varchar2(30) := 'VR';
 l_suc_rec_cnt              number := 0;
 l_rej_rec_cnt              number := 0;
 l_out_index                number := 0;
 l_msg_data                 varchar2(240);
 l_vr_count                 number := 0;
 l_vr_item_id               mtl_system_items_b.inventory_item_id%type;
 l_organization_code        varchar2(5);
 l_organization_id          number;
 l_prc_rec_id               number;

 cursor c_recipe_main is
   select distinct recipe_no
         ,routing_no
         ,routing_ver
         ,recipe_status
         ,recipe_desc
         ,recipe_ver
         ,formula_no
         ,formula_ver
         ,orgn_code
         ,planned_process_loss
         ,CALCULATE_STEP_QUANTITY
   from dkc_recipe_stg ars
   where nvl(process_flag,'E') = 'E'
   AND formula_no in(select formula_no from fm_form_mst ffm where ffm.formula_no =ars.formula_no and ffm.formula_vers = ars.formula_ver)
   and recipe_no not in (select recipe_no from gmd_recipes gr where gr.recipe_no = ars.recipe_no and gr.recipe_version = ars.recipe_ver)
   ;

    Cursor c_recp_vr(p_recipe_no Varchar2,p_recp_ver  Number)is
      select *
      from gmd_recipes
      where recipe_no = p_recipe_no
      and recipe_version = p_recp_ver
      ;

    Cursor c_vr_dtl(p_formula_id Number) is
      select msib.segment1 ||'-'||msib.segment2 item,
             d.item_qty,
             dtl.detail_uom item_um
      from mtl_system_items_b msib,
           fm_matl_dtl dtl,
           dkc_recipe_stg d
      where  dtl.formula_id = p_formula_id
         and line_no = 1
         and line_type = 1
         and msib.inventory_item_id = dtl.inventory_item_id
         and d.item_code = msib.segment1 ||'-'||msib.segment2
         and msib.organization_id in (select organization_id from mtl_parameters where organization_code ='405');

BEGIN

   FND_PROFILE.PUT ('USER_ID',1130);
   FND_GLOBAL.APPS_INITIALIZE(1130, '50872','552');
   dbms_output.put_line('Recipe started');

  For recp_rec IN c_recipe_main Loop

  dbms_output.put_line('Recipe started'||recp_rec.recipe_no);

    Begin
        l_recp_hdr_tbl  := l_recp_hdr_tbl_dummy;
        l_recp_tbl      := l_recp_tbl_dummy;
        dbms_output.put_line('Recipe No: '||recp_rec.recipe_NO);
    Begin
      select formula_id
      into l_formula_id
      from fm_form_mst
      where formula_no = recp_rec.formula_no
      and  formula_vers = recp_rec.formula_ver;
            dbms_output.put_line('Formula ID : '||l_formula_id);
      Exception
      when others then
      dbms_output.put_line('Valid Formula ID Not Found for Formula NO:: '||recp_rec.FORMULA_NO||' and formula Version '||recp_rec.FORMULA_VER);
    End;

    if NVL(recp_rec.routing_no,'0')<> '0'  then
     Begin
      select routing_id
      into l_routing_id
      from fm_rout_hdr
      where routing_no = recp_rec.routing_no
      and routing_vers = recp_rec.routing_ver;
      dbms_output.put_line('Routing  ID : '||l_routing_id);
     Exception
      when others then
      dbms_output.put_line('Valid Routing ID Not Found for Routing NO :: '||recp_rec.routing_no||' and Routing Version '||recp_rec.routing_ver);
     End;

    end if;

    Begin
    select organization_id
    into l_organization_id
    from mtl_parameters
    where organization_code =recp_rec.orgn_code;
    exception when others then
    dbms_output.put_line('Invalid Organization Code : '||recp_rec.orgn_code);
    End;

   FND_PROFILE.PUT ('USER_ID',1130);
   FND_GLOBAL.APPS_INITIALIZE(1130, '50872','552');

   dbms_output.put_line('Recipe Uploading started');

      l_recp_hdr_tbl(l_row_cnt).recipe_description     := SUBSTR(recp_rec.recipe_desc,1,70);
      l_recp_hdr_tbl(l_row_cnt).recipe_no              := recp_rec.recipe_no;
      l_recp_hdr_tbl(l_row_cnt).recipe_version         := recp_rec.recipe_ver;
      l_recp_hdr_tbl(l_row_cnt).user_id                := 1130;    --fnd_global.user_id;
      l_recp_hdr_tbl(l_row_cnt).owner_orgn_code        := '405';         --  l_organization_id;
      l_recp_hdr_tbl(l_row_cnt).creation_orgn_code     := '405';         --recp_rec.orgn_code;
      l_recp_hdr_tbl(l_row_cnt).owner_organization_id  := 108;    --l_organization_id;
      l_recp_hdr_tbl(l_row_cnt).creation_organization_id:= 108 ;   --l_organization_id;
      l_recp_hdr_tbl(l_row_cnt).formula_id             :=  l_formula_id;
      l_recp_hdr_tbl(l_row_cnt).routing_id             :=  l_routing_id;
      l_recp_hdr_tbl(l_row_cnt).recipe_status          := 700;
      l_recp_hdr_tbl(l_row_cnt).calculate_step_quantity  := 0;
      l_recp_hdr_tbl(l_row_cnt).delete_mark            := 0;
      l_recp_hdr_tbl(l_row_cnt).creation_date          := sysdate;
      l_recp_hdr_tbl(l_row_cnt).created_by             := 1130;     --fnd_global.user_id;
      l_recp_hdr_tbl(l_row_cnt).last_updated_by        := 1130;     --fnd_global.user_id;
      l_recp_hdr_tbl(l_row_cnt).last_update_date       := sysdate;
      l_recp_hdr_tbl(l_row_cnt).last_update_login      := 1130; --l_login;
      l_recp_hdr_tbl(l_row_cnt).owner_id               := 1130;     --fnd_global.user_id;
      l_recp_hdr_tbl(l_row_cnt).planned_process_loss := recp_rec.planned_process_loss;
      l_recp_hdr_tbl(l_row_cnt).fixed_process_loss   := 0;
      l_recp_hdr_tbl(l_row_cnt).fixed_process_loss_uom:='KG';

    --    l_recp_hdr_flex.count := 0;

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

     gmd_recipe_header.create_recipe_header(
        p_api_version          => 1.0,
        p_init_msg_list        => 'T',
        p_commit               => 'T',
        p_called_from_forms    => 'NO',
        x_return_status        => x_status,
        x_msg_count            => x_msg_cnt,
        x_msg_data             => x_msg_dat,
        p_recipe_header_tbl    => l_recp_hdr_tbl,
        p_recipe_header_flex   => l_recp_hdr_flex);

        dbms_output.put_line('x_status : '||x_status);

       If Nvl(x_status,'E') <> 'S' Then
               l_rej_rec_cnt := l_rej_rec_cnt + 1;
             For i IN 1 .. x_msg_cnt 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_recipe_stg
        set process_flag  = x_status,
            error_text    = x_msg_dat,
            error_count   = x_msg_cnt
            Where recipe_no = recp_rec.recipe_no
            and recipe_ver = recp_rec.recipe_ver;
         Exception When Others Then
            Null;
       End;
        ------------------------------------------------------------------------------------------------------------------------
        --- INITIATING THE VALIDATE RULE CREATION API WHICH WILL UPDATE THE GMD_RECIPE_VALIDITY_RULES TABLE
        -------------------------------------------------------------------------------------------------------------------------
     For vr_rec in c_recp_vr(recp_rec.recipe_no,recp_rec.recipe_ver) Loop
         For vr_dtl_rec in C_vr_dtl(l_formula_id) Loop
           Begin
             Begin
                 select inventory_item_id
                 into l_vr_item_id
                 from mtl_system_items_b
                 where segment1 ||'-'||segment2 = vr_dtl_rec.item
                 and organization_id in (select organization_id from mtl_parameters where organization_code ='405');
                 Exception When Others Then
                   l_vr_item_id := Null;
             End;
             Begin
                 select count(*)
                 Into l_vr_count
                 from gmd_recipe_validity_rules
                 where recipe_id = vr_rec.recipe_id
                 and item_id = l_vr_item_id
                 and sysdate between Nvl(start_date,sysdate-1) and Nvl(end_date,sysdate+1);
                 Exception When Others Then
                 l_vr_count := 0;
             End;
            If l_vr_count = 0 Then
                Begin
                begin
                select organization_code
                into   l_organization_code
                from   mtl_parameters
                where  organization_id = vr_rec.owner_organization_id;
                exception when others then
                null;
                end;
            l_recp_tbl(l_my_cnt).recipe_no          := vr_rec.recipe_no;
            l_recp_tbl(l_my_cnt).recipe_version     := vr_rec.recipe_version;
            l_recp_tbl(l_my_cnt).user_name          := 'KAUSHIKB'; -- fnd_global.user_name;
            l_recp_tbl(l_my_cnt).orgn_code          := '405';  -- l_organization_code;
            l_recp_tbl(l_my_cnt).item_no            := vr_dtl_rec.item;
            l_recp_tbl(l_my_cnt).inventory_item_id  := l_vr_item_id;
            l_recp_tbl(l_my_cnt).recipe_use         := 0;
            l_recp_tbl(l_my_cnt).preference         := 1;
            l_recp_tbl(l_my_cnt).start_date         := sysdate;
            l_recp_tbl(l_my_cnt).std_qty            := vr_dtl_rec.item_qty;
            l_recp_tbl(l_my_cnt).inv_min_qty        := 100;
            l_recp_tbl(l_my_cnt).inv_max_qty        := 1000;
            l_recp_tbl(l_my_cnt).created_by         := 1130; -- fnd_global.user_id;
            l_recp_tbl(l_my_cnt).creation_date      := sysdate;
            l_recp_tbl(l_my_cnt).last_updated_by    := 1130; --fnd_global.user_id;
            l_recp_tbl(l_my_cnt).last_update_date     := sysdate;
            l_recp_tbl(l_my_cnt).last_update_login    := 1130;--  l_login;
            l_recp_tbl(l_my_cnt).delete_mark          := 0;
            l_recp_tbl(l_my_cnt).organization_id      := 108; -- l_organization_id;
            l_recp_tbl(l_my_cnt).planned_process_loss := 0;
            l_recp_tbl(l_my_cnt).validity_rule_status := 700;
            GMD_RECIPE_DETAIL.CREATE_RECIPE_VR
                ( p_api_version        => 1.0 ,
                  p_init_msg_list      => 'T' ,
                  p_commit             => 'T' ,
                  p_called_from_forms  => 'NO' ,
                  x_return_status      => x_return_status ,
                  x_msg_count          => x_msg_count ,
                  x_msg_data           => x_msg_data ,
                  p_recipe_vr_tbl      => l_recp_tbl ,
                  p_recipe_vr_flex     => l_recipe_flex_tbl);
                 Begin
                   Update GMD_RECIPE_VALIDITY_RULES
                   set validity_rule_status = '700'
                   where recipe_id = vr_rec.recipe_id;
                   Exception When Others Then
                   Null;
                 End;
                SELECT count(RECIPE_ID) into l_prc_rec_id
                  FROM gmd_recipe_process_loss
                  WHERE RECIPE_ID = vr_rec.recipe_id;
                    if l_prc_rec_id = 0 then
                            insert into gmd_recipe_process_loss(recipe_id,orgn_code,process_loss,creation_date,created_by,last_updated_by,last_update_date,
                            last_update_login,recipe_process_loss_id,text_code,organization_id,contiguous_ind,fixed_process_loss,fixed_process_loss_uom)
                            VALUES(vr_rec.recipe_id,'405',0,SYSDATE,1130,1130,SYSDATE,NULL,GMD_RECIPE_PROCESS_LOSS_ID_S.NEXTVAL,NULL,108,0,0,vr_dtl_rec.item_um);
                            COMMIT;
                           end if;
                          End;
           End If;
          End;
         End Loop;
       End Loop;
        End;
    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);
  fnd_file.put_line (fnd_file.LOG,' | Total records rejected  :           '||l_rej_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,' ');
  commit;
 END ;