配方导入

---create temp table for OPM formula GENERATION

CREATE TABLE DKC_FORMULA_STG
(
  FORMULA_NO         VARCHAR2(32),
  FORMULA_VERS       NUMBER,
  FORMULA_DESC       VARCHAR2(70),
  FORMULA_CLASS      VARCHAR2(8),
  ORGN_CODE          VARCHAR2(4),
  LINE_NO            NUMBER(5),
  PROD_CODE          VARCHAR2(40),
  PROD_QTY           NUMBER,
  UOM                VARCHAR2(4),
  INGR_CODE          VARCHAR2(40),
  INGR_QTY           NUMBER,
  INGR_UOM           VARCHAR2(4),
  INGR_LINE_NO       NUMBER(5),
  LINE_TYPE          NUMBER(5),
  INGR_LINE_TYPE     NUMBER(5),
  BI_LINE_TYPE       NUMBER(5),
  BI_PROD            VARCHAR2(40),
  BI_PROD_QTY        NUMBER,
  BI_PROD_LINE       NUMBER(5),
  RETURN_STATUS      CHAR(1),
  MSG_COUNT          NUMBER,
  MSG_DATA           VARCHAR2(2000),
  LAST_UPDATED_DATE  DATE,
  LAST_UPDATED_BY    VARCHAR2(100),
  CREATION_DATE      DATE,
  CREATED_BY         VARCHAR2(100),
  LAST_UPDATE_LOGIN  VARCHAR2(100),
  CONT_T_YIELD       CHAR(1),
  SCRAP_FACTOR       NUMBER
)

/

-- pl/sql Formula generation API

 Declare

 l_frmla_inst_tbl         GMD_FORMULA_PUB.formula_insert_hdr_tbl_type;
 l_frmla_inst_tbl_dummy   GMD_FORMULA_PUB.formula_insert_hdr_tbl_type;
 l_return_status          Varchar2(1);
 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_out_index              Number :=0;
 l_return_sts             Boolean;
 l_user_name              Varchar2(50) := 'KAUSHIKB'; --FND_GLOBAL.USER_NAME;
 l_uom_code               ic_item_mst.item_um%type;
 l_ing_uom_code           ic_item_mst.item_um%type;
 l_prd_uom_code           ic_item_mst.item_um%type;
 l_byprd_uom_code         ic_item_mst.item_um%type;
 l_count                  Number;
 l_suc_rec_cnt            Number := 0;
 l_rej_rec_cnt            Number := 0;
 l_organization_id        Number :=0;
 l_contribute_to_yield    varchar2(1);
 l_scale_type             number;
 l_scale_multiple         number;
 l_rounding_direction     number;

 -- All formulas from staging table
 cursor c_frml_main is
  select  distinct formula_no
        , formula_desc
        , formula_vers
        , orgn_code
  from  dkc_formula_stg afs
  where nvl(return_status,'E') = 'E'
  AND formula_no not in (select formula_no from fm_form_mst_b ffm WHERE FFM.FORMULA_NO = afs.formula_no and ffm.formula_vers = afs.formula_vers)
  order by 1;

  -- All Products from staging table for a particular formula
 cursor c_frml_product(p_form_no  varchar2,p_form_ver number) is
  select  distinct formula_no
        , formula_desc
        , formula_vers
        , orgn_code
        , prod_code
        , prod_qty
        , uom
        , line_no
  from  dkc_formula_stg
  where prod_code is not null
    and formula_no = p_form_no
    and nvl(return_status,'E') = 'E'
    and formula_vers = p_form_ver
    and line_type = 1
  order by formula_no,line_no;

 -- All ByProducts from staging table for a particular formula

 cursor c_frml_byproduct(p_formula_no varchar2,p_form_ver number) is
  select  distinct formula_no
        , bi_prod_line
        , bi_prod
        , bi_prod_qty
        , uom
        , line_no
  from    dkc_formula_stg
  where formula_no = p_formula_no
    and bi_prod is not null
    and nvl(return_status,'E') = 'E'
    and formula_vers = p_form_ver
    and BI_line_type = 2
    order by bi_prod_line;

  -- All Ingredients from staging table for a particular formula
 cursor c_frml_ingrediet(p_formula_no varchar2,p_form_ver number) is
  select  distinct
          formula_no
        , ingr_line_no
        , ingr_code ingredient
        , ingr_qty
        , orgn_code
        , ingr_uom
        , formula_class
        , line_no
        , SCRAP_FACTOR
        , cont_t_yield
  from    dkc_formula_stg
  where formula_no = p_formula_no
    and ingr_code is not null
    and nvl(return_status,'E') = 'E'
    and formula_vers = p_form_ver
    and ingr_line_type=-1
    order by ingr_line_no;

 cursor form_attrs(p_formula_no varchar2,p_form_ver number) is
  select  distinct
          formula_no
        , ingr_line_no
        , ingr_code ingredient
        , ingr_qty ingr_qty
        , orgn_code
        , uom
        , formula_class
        , ingr_line_no
        , ingr_line_type
  from    dkc_formula_stg
  where formula_no = p_formula_no
    and formula_no in (select formula_no from fm_form_mst_b)
    and ingr_code is not null
    and formula_vers = p_form_ver
  --  and (attribute11 is not null or  attribute12 is not null or  attribute13 is not null)
    order by line_no;

 BEGIN

   FND_PROFILE.PUT ('USER_ID',1130);
   FND_GLOBAL.APPS_INITIALIZE(1130, '50830','552');
   dbms_output.put_line('Formula Uploading started');

   For for_rec in c_frml_main Loop
   Begin
    -- Initializing Counter variable to 1
     l_count  := 1;
     l_frmla_inst_tbl := l_frmla_inst_tbl_dummy;
        dbms_output.put_line('Formula No '||for_rec.formula_no);

    -- Assigning the Product Information

   FND_MSG_PUB.delete_msg;

   For Ingre_rec in c_frml_ingrediet(for_rec.formula_no,for_rec.formula_vers) Loop
           Begin

           fnd_file.put_line (fnd_file.LOG,' Ingredient :: '||for_rec.formula_no||' Line :: '||Ingre_rec.ingr_line_no||' Count :: '||l_count);

           l_frmla_inst_tbl(l_count).user_name       := 'KAUSHIKB';  -- l_user_name;
           l_frmla_inst_tbl(l_count).formula_no      := for_rec.formula_no;
           l_frmla_inst_tbl(l_count).formula_vers    := for_rec.FORMULA_VERS;
           l_frmla_inst_tbl(l_count).formula_desc1   := SUBSTR(for_rec.formula_desc,1,70);
           l_frmla_inst_tbl(l_count).formula_class   := ingre_rec.formula_class;
           l_frmla_inst_tbl(l_count).line_type       := -1;
           l_frmla_inst_tbl(l_count).line_no         := Ingre_rec.ingr_line_no;
           l_frmla_inst_tbl(l_count).item_no         := Ingre_rec.ingredient;
        Begin
           Select primary_uom_code
           Into l_ing_uom_code
           From mtl_system_items_b
           Where segment1 = Ingre_rec.ingredient
           and organization_id in (select organization_id From mtl_parameters where organization_code ='404');
            Exception When Others Then
           l_ing_uom_code := Null;
        End;
            BEGIN
            SELECT organization_id
            into l_organization_id
            from mtl_parameters
            where organization_code = Ingre_rec.orgn_code;
            END;
        select decode(ingre_rec.ingr_line_no,1,'Y','N') into l_contribute_to_yield from dual;
        select decode(ingre_rec.ingr_uom,'EA','2','1'),decode(ingre_rec.ingr_uom,'EA','1',NULL) ,decode(ingre_rec.ingr_uom,'EA','1',NULL)
        into l_scale_type,l_scale_multiple,l_rounding_direction from dual;

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

       l_frmla_inst_tbl(l_count).DETAIL_UOM     := Ingre_rec.ingr_uom;
       l_frmla_inst_tbl(l_count).qty            := Ingre_rec.ingr_qty;
       l_frmla_inst_tbl(l_count).release_type   := 0;
       l_frmla_inst_tbl(l_count).scale_type_DTL := l_scale_type;
       l_frmla_inst_tbl(l_count).scale_multiple := l_scale_multiple;
       l_frmla_inst_tbl(l_count).rounding_direction := l_rounding_direction;
       l_frmla_inst_tbl(l_count).scale_type_hdr := 1;
       l_frmla_inst_tbl(l_count).SCRAP_FACTOR   := 0;
       l_frmla_inst_tbl(l_count).inactive_ind   := 0;
       l_frmla_inst_tbl(l_count).owner_organization_id      := 107; --l_organization_id;
       l_frmla_inst_tbl(l_count).formula_status := '700';
       l_frmla_inst_tbl(l_count).phantom_type   := 0;
       l_frmla_inst_tbl(l_count).rework_type    := 0;
       l_frmla_inst_tbl(l_count).creation_date    := SYSDATE;
       l_frmla_inst_tbl(l_count).last_update_date := SYSDATE;
       l_frmla_inst_tbl(l_count).delete_mark     := 0;
       l_frmla_inst_tbl(l_count).created_by      := 1130;--l_user_id;
       l_frmla_inst_tbl(l_count).last_updated_by := 1130;--l_user_id;
       l_frmla_inst_tbl(l_count).owner_id        := 1130;--l_user_id;
       l_frmla_inst_tbl(l_count).contribute_step_qty_ind := 'Y';
       l_frmla_inst_tbl(l_count).contribute_yield_ind := Ingre_rec.cont_t_yield;
       l_count := l_count + 1;
       End;
    End Loop;
   For prod_rec IN c_frml_product(for_rec.formula_no,for_rec.formula_vers) Loop
      Begin
       fnd_file.put_line (fnd_file.LOG,' Product :: '||for_rec.formula_no||' Line :: '||prod_rec.line_no||' Count :: '||l_count);
       l_frmla_inst_tbl(l_count).user_name        := 'KAUSHIKB';  -- l_user_name;
       l_frmla_inst_tbl(l_count).record_type      := 'I';
       l_frmla_inst_tbl(l_count).formula_no       := for_rec.formula_no;
       l_frmla_inst_tbl(l_count).formula_vers     := for_rec.FORMULA_VERS;
       l_frmla_inst_tbl(l_count).formula_desc1    := SUBSTR(for_rec.formula_desc,1,70);
       l_frmla_inst_tbl(l_count).line_type        := 1;
       l_frmla_inst_tbl(l_count).line_no          := prod_rec.line_no;
       l_frmla_inst_tbl(l_count).item_no          := prod_rec.prod_code;
   Begin
        Select primary_uom_code
       Into l_prd_uom_code
      From mtl_system_items_b
       Where segment1= prod_rec.prod_code
         and organization_id in (select organization_id From mtl_parameters where organization_code ='404');
     Exception When Others Then
       l_prd_uom_code := Null;
   End;
        --   fnd_file.put_line (fnd_file.LOG,'Product UOM :: '||l_prd_uom_code);
       l_frmla_inst_tbl(l_count).DETAIL_UOM       := prod_rec.uom; --l_prd_uom_code;
       l_frmla_inst_tbl(l_count).qty              := prod_rec.prod_qty;
       l_frmla_inst_tbl(l_count).release_type     := 0;
       l_frmla_inst_tbl(l_count).SCRAP_FACTOR     := 0;
       l_frmla_inst_tbl(l_count).scale_type_hdr   := 1;
       l_frmla_inst_tbl(l_count).scale_type_dtl   := 1;
  --     l_frmla_inst_tbl(l_count).cost_alloc       := prod_rec.cost_alloc;
       l_frmla_inst_tbl(l_count).inactive_ind     := 0;
       l_frmla_inst_tbl(l_count).owner_organization_id      := 107; --l_organization_id;
       l_frmla_inst_tbl(l_count).formula_status   := '700';
       l_frmla_inst_tbl(l_count).phantom_type     := 0;
       l_frmla_inst_tbl(l_count).rework_type      := 0;
       l_frmla_inst_tbl(l_count).creation_date    := SYSDATE;
       l_frmla_inst_tbl(l_count).last_update_date := SYSDATE;
       l_frmla_inst_tbl(l_count).delete_mark      := 0;
       l_frmla_inst_tbl(l_count).created_by       := 1130;--l_user_id;
       l_frmla_inst_tbl(l_count).last_updated_by  := 1130;--l_user_id;
       l_frmla_inst_tbl(l_count).owner_id         := 1130;--l_user_id;
       l_count := l_count + 1;
      End;
  End Loop;
 --|---------------------------------------------------------------------------------------------------------
 --|----  assigning the formula and Bi-product values
 --|---------------------------------------------------------------------------------------------------------
    For byprod_rec in c_frml_byproduct(for_rec.formula_no,for_rec.formula_vers) Loop
      Begin
        fnd_file.put_line (fnd_file.LOG,' By Product :: '||for_rec.formula_no||' Line :: '||byprod_rec.bi_prod_line);
        l_frmla_inst_tbl(l_count).user_name        := 'KAUSHIKB';  -- l_user_name;
        l_frmla_inst_tbl(l_count).formula_no       := for_rec.formula_no;
        l_frmla_inst_tbl(l_count).formula_vers     := for_rec.FORMULA_VERS;
        l_frmla_inst_tbl(l_count).formula_desc1    := SUBSTR(for_rec.formula_desc,1,70);
        l_frmla_inst_tbl(l_count).line_type        := 2;
        l_frmla_inst_tbl(l_count).line_no          := byprod_rec.bi_prod_line;
        l_frmla_inst_tbl(l_count).item_no          := byprod_rec.bi_prod;
      Begin
        Select primary_uom_code
        Into l_byprd_uom_code
        From mtl_system_items_b
        Where segment1 = byprod_rec.bi_prod
        and organization_id in (select organization_id From mtl_parameters where organization_code ='404');
      Exception When Others Then
       l_byprd_uom_code := Null;
       End;
       l_frmla_inst_tbl(l_count).detail_uom       := byprod_rec.uom; --l_prd_uom_code;
       l_frmla_inst_tbl(l_count).qty              := byprod_rec.bi_prod_qty;
       l_frmla_inst_tbl(l_count).release_type     := 0;
       l_frmla_inst_tbl(l_count).scale_type_DTL   := 1;
       l_frmla_inst_tbl(l_count).scale_type_hdr   := 1;
       l_frmla_inst_tbl(l_count).SCRAP_FACTOR     := 0;
       l_frmla_inst_tbl(l_count).inactive_ind     := 0;
       l_frmla_inst_tbl(l_count).owner_organization_id      := 107; --l_organization_id;
       l_frmla_inst_tbl(l_count).formula_status   := '700';
       l_frmla_inst_tbl(l_count).phantom_type     := 0;
       l_frmla_inst_tbl(l_count).rework_type      := 0;
       l_frmla_inst_tbl(l_count).creation_date    := SYSDATE;
       l_frmla_inst_tbl(l_count).last_update_date := SYSDATE;
       l_frmla_inst_tbl(l_count).delete_mark      := 0;
       l_frmla_inst_tbl(l_count).created_by       := 1;
       l_frmla_inst_tbl(l_count).last_updated_by  := 1;
       l_frmla_inst_tbl(l_count).owner_id         := 1130; -- l_user_id;
      --   l_frmla_inst_tbl(l_count).text_code_dtl    := '';
      --   l_frmla_inst_tbl(l_count).text_code_hdr    := '';
       l_frmla_inst_tbl(l_count).contribute_step_qty_ind := 'Y';
       l_frmla_inst_tbl(l_count).contribute_yield_ind := 'Y';
       l_count := l_count + 1;
       End;
   End Loop;
     --|---------------------------------------------------------------------------------------------------------
     --|----  assigning the formula and Ingredients values
     --|---------------------------------------------------------------------------------------------------------
    dbms_output.put_line('Insert_Formula');
   dbms_output.put_line('l_return_status'||l_return_status|| 'Message '||l_msg_data);
   GMD_FORMULA_PUB.Insert_Formula(p_api_version           => 1.0
                                 ,p_init_msg_list         => 'F'
                                 ,p_commit                => 'T'
                                 ,p_called_from_forms     => 'NO'
                                 ,x_return_status         => l_return_status
                                 ,x_msg_count             => l_msg_count
                                 ,x_msg_data              => l_msg_data
                                 ,p_formula_header_tbl    => l_frmla_inst_tbl
                                 ,p_allow_zero_ing_qty    => 'TRUE');
   dbms_output.put_line('l_return_status'||l_return_status|| 'Message '||l_msg_data);
   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 || l_out_index);
       End Loop;
      Else
         l_suc_rec_cnt := l_suc_rec_cnt + 1;
      End If;
      Begin
     Update dkc_formula_stg
       Set  return_status = l_return_status
           ,msg_count     = l_msg_count
           ,msg_data      = l_msg_data
     Where formula_no = for_rec.formula_no
     and formula_vers = for_rec.formula_vers;
        Exception When Others Then
         Null;
      End;
    End;
   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);
  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;