CREATE OR REPLACE PACKAGE A07_REPLENISHMENT_INT AUTHID CURRENT_USER AS
/******************************************************************************
   NAME:       A07_REPLENISH_INTERFACE
   PURPOSE:    Item Replenishment Interface.

   PARAMETERS:
   INPUT:
   OUTPUT:
   RETURNED VALUE:
   CALLED BY:
   CALLS:
   EXAMPLE USE:     NUMBER := A07_REPLENISH_INTERFACE.MyFuncName(Number);
                    A07_REPLENISH_INTERFACE.MyProcName(Number, Varchar2);
   ASSUMPTIONS:+
   LIMITATIONS:
   ALGORITHM:
   NOTES:
******************************************************************************/
— All the Global Variables to be used in this package
      g_create_dt       date   := sysdate;
      g_create_by       number := 10005218;
      g_update_dt       date   := sysdate;
      g_update_by       number := 10005218;
      v_sqlcode         number;
      v_sqlerrm         varchar2(255);
— All Global Variables specific to the package
— All the Functions used in this package
   Function CHK_ORG_EXISTS (org_id mtl_parameters.organization_id%type) return boolean;
   Function CHK_ITEM_EXISTS (item_id mtl_system_items_b.inventory_item_id%type,
                                     v_org_id mtl_system_items_b.organization_id%type) return boolean;
   Function CHK_ITEM_SUBINV (v_item_id number, v_subinv varchar2, v_org_id number) return boolean;
   Function GET_ITEM_PLAN (v_org_id number, v_item_id number, v_subinv varchar2) return number;
   Function GET_ITEM_UOM (v_org_id number, v_item_id number) return varchar2;
— All the Procedures used in this package
   Procedure Validate_Organization;
   Procedure Validate_Item;
   Procedure Validate_Item_SubInv;
   Procedure Validate_Plan_Method;
   Procedure Validate_Uom;
   Procedure Validate ;
   Procedure Populate_Interface;
   Procedure Run (errcode out number, errbuf out varchar2);
END A07_REPLENISHMENT_INT;
/
CREATE OR REPLACE PACKAGE BODY A07_REPLENISHMENT_INT AS
Function CHK_ORG_EXISTS (org_id mtl_parameters.organization_id%type) return boolean
Is
   cnt number;
Begin
      Select count(*) into cnt
      From hr_all_organization_units
      where organization_id = org_id;
      if cnt <> 0
      then
         return true;
      Else
         return false;
      End if;
End;
Function CHK_ITEM_EXISTS (item_id mtl_system_items_b.inventory_item_id%type,
                                     v_org_id mtl_system_items_b.organization_id%type) return boolean
Is
    cnt     number;
Begin
      Select count(*) into cnt
      From mtl_system_items_b
      Where inventory_item_id = item_id
      And   organization_id = v_org_id;
      if cnt <> 0
      then
         return true;
      else
         return false;
      end if;
End;
Function CHK_ITEM_SUBINV (v_item_id number, v_subinv varchar2, v_org_id number) return boolean
Is
      cnt number;
Begin
      Select count(*) into cnt
      From mtl_item_sub_inventories
      Where inventory_item_id = v_item_id
      and  secondary_inventory = v_subinv
      and organization_id = v_org_id;
      if cnt <> 0
      then
         return true;
      else
         return false;
      end if;
End;
Function GET_ITEM_PLAN (v_org_id number, v_item_id number, v_subinv varchar2) return number
Is
      ipm number;
Begin
      Select inventory_planning_code into ipm
      From mtl_item_sub_inventories
      Where inventory_item_id = v_item_id
      And   organization_id = v_org_id
      And   secondary_inventory = v_subinv;
      Return ipm;
Exception
      When NO_DATA_FOUND then
             return 2;
      When OTHERS then
             return 1;
End;
Function GET_ITEM_UOM (v_org_id number, v_item_id number) return varchar2
is
      uom varchar2(3);
Begin
      Select primary_uom_code into uom
      From mtl_system_items_b
      Where organization_id = v_org_id
      And inventory_item_id = v_item_id;
      Return uom;
Exception
      When NO_DATA_FOUND then
             return NULL;
      When OTHERS then
             return NULL;
End;
Procedure Validate_Organization
As
      Cursor c_org is Select distinct organization_id from A07_replenish_interface;
      v_org  c_org%rowtype;
Begin
      Open c_org;
      Loop
             Fetch c_org into v_org;
             Exit when c_org%NOTFOUND;
             Begin
                  If chk_org_exists(v_org.organization_id)
                  then
                     null;
                  Else
                     Update A07_replenish_interface
                     Set error_flag = 1,
                          error_description = ‘Organization does not exist’
                     Where organization_id = v_org.organization_id;
                End If;
             Exception
                        When OTHERS then
                               v_sqlcode := sqlcode;
                               v_sqlerrm := sqlerrm;
                               Rollback;
                               update A07_replenish_interface
                               Set error_flag = v_sqlcode,
                                     error_description = v_sqlerrm
                               where organization_id = v_org.organization_id;
             End;
      End Loop;
      Close c_org;
      Commit;
      fnd_file.put_line(FND_FILE.OUTPUT,‘Validate Organization completed successfully’);
Exception
      When OTHERS then
             v_sqlcode := sqlcode;
             v_sqlerrm := sqlerrm;
             Rollback;
             fnd_file.put_line(FND_FILE.LOG,‘In the process : Validate Organization’);
             fnd_file.put_line(FND_FILE.LOG,‘Error Code is ‘ || v_sqlcode);
             fnd_file.put_line(FND_FILE.LOG,‘Error Mesg is ‘ || v_sqlerrm);
End Validate_Organization;
Procedure Validate_Item
As
      Cursor c_item is Select inventory_item_id, organization_id from A07_replenish_interface
                               Where error_flag is null;
      v_item c_item%rowtype;
Begin
    Open c_item;
      Loop
             Fetch c_item into v_item;
             Exit when c_item%NOTFOUND;
             Begin
                  If chk_item_exists(v_item.inventory_item_id, v_item.organization_id)
                then
                     null;
                  else
                     Update A07_replenish_interface
                     Set error_flag = 2,
                           error_description = ‘Item does not exist’
                     Where organization_id = v_item.organization_id
                     And   inventory_item_id = v_item.inventory_item_id;
                  End If;
             Exception
                        When OTHERS then
                               v_sqlcode := sqlcode;
                               v_sqlerrm := sqlerrm;
                               Rollback;
                               update A07_replenish_interface
                               Set error_flag = v_sqlcode,
                                     error_description = v_sqlerrm
                               Where organization_id = v_item.organization_id
                               And   inventory_item_id = v_item.inventory_item_id;
             End;
      End Loop;
      Close c_item;
      Commit;
      fnd_file.put_line(FND_FILE.OUTPUT,‘Validate Item completed successfully’);
Exception
      When OTHERS then
             v_sqlcode := sqlcode;
             v_sqlerrm := sqlerrm;
             Rollback;
             fnd_file.put_line(FND_FILE.LOG,‘In the process : Validate Item’);
             fnd_file.put_line(FND_FILE.LOG,‘Error Code is ‘ || v_sqlcode);
             fnd_file.put_line(FND_FILE.LOG,‘Error Mesg is ‘ || v_sqlerrm);
End Validate_Item;
Procedure Validate_Item_SubInv
As
      Cursor c_item is Select inventory_item_id, subinventory_code,organization_id
                               from A07_replenish_interface
                               Where error_flag is null;
      v_item c_item%rowtype;
Begin
    Open c_item;
      Loop
             Fetch c_item into v_item;
             Exit when c_item%NOTFOUND;
             Begin
                  If chk_item_subinv(v_item.inventory_item_id,v_item.subinventory_code,v_item.organization_id)
                then
                     null;
                  else
                     Update A07_replenish_interface
                     Set error_flag = 2,
                           error_description = ‘Item does not exist in suninventory’
                     Where organization_id = v_item.organization_id
                     And   inventory_item_id = v_item.inventory_item_id
                     And   subinventory_code = v_item.subinventory_code;
                  End If;
             Exception
                        When OTHERS then
                               v_sqlcode := sqlcode;
                               v_sqlerrm := sqlerrm;
                               Rollback;
                               update A07_replenish_interface
                               Set error_flag = v_sqlcode,
                                     error_description = v_sqlerrm
                               Where organization_id = v_item.organization_id
                               And   inventory_item_id = v_item.inventory_item_id
                             And   subinventory_code = v_item.subinventory_code;
             End;
      End Loop;
      Close c_item;
      Commit;
      fnd_file.put_line(FND_FILE.OUTPUT,‘Validate Item completed successfully’);
Exception
      When OTHERS then
             v_sqlcode := sqlcode;
             v_sqlerrm := sqlerrm;
             Rollback;
             fnd_file.put_line(FND_FILE.LOG,‘In the process : Validate Item’);
             fnd_file.put_line(FND_FILE.LOG,‘Error Code is ‘ || v_sqlcode);
             fnd_file.put_line(FND_FILE.LOG,‘Error Mesg is ‘ || v_sqlerrm);
End Validate_Item_SubInv;
Procedure Validate_Plan_Method
As
      Cursor c_plan is Select organization_id, inventory_item_id, subinventory_code, count_method
                               from A07_replenish_interface
                               Where error_flag is null;
      v_plan c_plan%rowtype;
      ipm number;
Begin
      Open c_plan;
      Loop
             Fetch c_plan into v_plan;
             Exit when c_plan%NOTFOUND;
             ipm := get_item_plan(v_plan.organization_id, v_plan.inventory_item_id,v_plan.subinventory_code);
             Begin
                  If ipm = 1
                then
                        if v_plan.count_method != 2
                        then
                          Update A07_replenish_interface
                          Set error_flag = 3,
                                error_description = ‘Planning method and Counting Method mismatch’
                          Where organization_id = v_plan.organization_id
                          And   inventory_item_id = v_plan.inventory_item_id
                          and  subinventory_code = v_plan.subinventory_code;
                        end if;
                  Elsif ipm = 2 then
                        if v_plan.count_method != 3
                        then
                          Update A07_replenish_interface
                          Set error_flag = 3,
                                error_description = ‘Planning method and Counting Method mismatch’
                          Where organization_id = v_plan.organization_id
                          And   inventory_item_id = v_plan.inventory_item_id
                          and  subinventory_code = v_plan.subinventory_code;
                        end if;
                  Elsif ipm = 6 then
                        if v_plan.count_method != 1
                        then
                          Update A07_replenish_interface
                          Set error_flag = 3,
                                error_description = ‘Planning method and Counting Method mismatch’
                          Where organization_id = v_plan.organization_id
                          And   inventory_item_id = v_plan.inventory_item_id
                          and  subinventory_code = v_plan.subinventory_code;
                        end if;
                  Else
                          null;
                  End If;
             Exception
                        When OTHERS then
                               v_sqlcode := sqlcode;
                               v_sqlerrm := sqlerrm;
                               Rollback;
                               update A07_replenish_interface
                               Set error_flag = v_sqlcode,
                                     error_description = v_sqlerrm
                               Where organization_id = v_plan.organization_id
                             And   inventory_item_id = v_plan.inventory_item_id
                             And   subinventory_code = v_plan.subinventory_code;
             End;
      End Loop;
      Close c_plan;
      Commit;
      fnd_file.put_line(FND_FILE.OUTPUT,‘Validate Plan Method completed successfully’);
Exception
      When OTHERS then
             v_sqlcode := sqlcode;
             v_sqlerrm := sqlerrm;
             Rollback;
             fnd_file.put_line(FND_FILE.LOG,‘In the process : Validate Plan Method’);
             fnd_file.put_line(FND_FILE.LOG,‘Error Code is ‘ || v_sqlcode);
             fnd_file.put_line(FND_FILE.LOG,‘Error Mesg is ‘ || v_sqlerrm);
End Validate_Plan_Method;
Procedure Validate_UOM
As
      Cursor c_uom is Select distinct inventory_item_id, organization_id
                               from A07_replenish_interface
                               Where error_flag is null;
      v_plan  c_uom%rowtype;
      uom varchar2(3);
Begin
      Open c_uom;
      Loop
             Fetch c_uom into v_plan;
             Exit when c_uom%NOTFOUND;
             uom := get_item_uom(v_plan.organization_id, v_plan.inventory_item_id);
             Begin
                  If uom is NULL
                then
                          Update A07_replenish_interface
                          Set error_flag = 4,
                                error_description = ‘UOM not found’
                          Where organization_id = v_plan.organization_id
                          And   inventory_item_id = v_plan.inventory_item_id;
                  Else
                          Update A07_replenish_interface
                          Set    UOM_CODE = uom
                          Where organization_id = v_plan.organization_id
                          And   inventory_item_id = v_plan.inventory_item_id;
                  End If;
             Exception
                        When OTHERS then
                               v_sqlcode := sqlcode;
                               v_sqlerrm := sqlerrm;
                               Rollback;
                               update A07_replenish_interface
                               Set error_flag = v_sqlcode,
                                     error_description = v_sqlerrm
                               Where organization_id = v_plan.organization_id
                             And   inventory_item_id = v_plan.inventory_item_id;
             End;
      End Loop;
      Close c_uom;
      Commit;
      fnd_file.put_line(FND_FILE.OUTPUT,‘Validate Plan Method completed successfully’);
Exception
      When OTHERS then
             v_sqlcode := sqlcode;
             v_sqlerrm := sqlerrm;
             Rollback;
             fnd_file.put_line(FND_FILE.LOG,‘In the process : Validate Plan Method’);
             fnd_file.put_line(FND_FILE.LOG,‘Error Code is ‘ || v_sqlcode);
             fnd_file.put_line(FND_FILE.LOG,‘Error Mesg is ‘ || v_sqlerrm);
End Validate_UOM;
Procedure Validate
As
Begin
   Update A07_replenish_interface
   Set error_flag = NULL, error_description = NULL;
— Update the organization_ids to the respective organization
   Validate_organization;
— Check if the items exist for the given organization in MTL_SYSTEM_ITEMS_B
   Validate_item;
— Check if the organization costing method and the item costing method are same.
   Validate_Plan_Method;
— Check if the item is existing in the subinventory
   Validate_Item_SubInv;
— To Check if the item cost already exists in the CST_ITEM_COSTS
   Validate_UOM;
   Update A07_replenish_interface
   Set error_flag = 0, error_description = ‘Clean Data’
   Where error_flag is null;
   Commit;
   fnd_file.put_line(FND_FILE.OUTPUT,‘Validate Interface completed successfully’);
Exception
      When OTHERS then
             v_sqlcode := sqlcode;
             v_sqlerrm := sqlerrm;
             Rollback;
             fnd_file.put_line(FND_FILE.LOG,‘In the process : Validate Interface’);
             fnd_file.put_line(FND_FILE.LOG,‘Error Code is ‘ || v_sqlcode);
             fnd_file.put_line(FND_FILE.LOG,‘Error Mesg is ‘ || v_sqlerrm);
End Validate;
Procedure Populate_Interface
As
    CURSOR C1 IS    SELECT * FROM A07_REPLENISH_INTERFACE;
      Cursor c_hdr is Select distinct organization_id, subinventory_code, count_date
                              from A07_replenish_interface
                              where error_flag = 0;
      Cursor c_lines (org_id in number,subinv_code in varchar2) is
                           Select inventory_item_id, organization_id
                           from A07_replenish_interface
                              where error_flag = 0;
      h_id           number;
      l_id           number;
      h_name               varchar2(15);
    V_HDR          C_HDR%ROWTYPE;
Begin
      Open c_hdr;
      Loop <<OUTER>>
             Fetch c_hdr into v_hdr;
             Exit when c_hdr%NOTFOUND;
             Select mtl_replenish_headers_s.nextval into h_id from dual;
             h_name     := ‘SRT’ || h_id ;
    END LOOP;  
        
     
       FOR REC IN C1
             LOOP
             Insert into mtl_replenish_headers_int
             ( REPLENISHMENT_HEADER_ID,REPLENISHMENT_COUNT_NAME,COUNT_DATE, LAST_UPDATE_DATE,CREATION_DATE,
             CREATED_BY ,LAST_UPDATED_BY,ORGANIZATION_ID,SUBINVENTORY_CODE, PROCESS_STATUS,PROCESS_MODE  )
             Values
             (REC.REPLENISHMENT_HEADER_ID,REC.REPLENISHMENT_COUNT_NAME,REC.COUNT_DATE,REC.LAST_UPDATE_DATE,REC.CREATION_DATE,
             REC.CREATED_BY,REC.LAST_UPDATED_BY,REC.ORGANIZATION_ID,REC.SUBINVENTORY_CODE,REC.PROCESS_STATUS,REC.PROCESS_MODE);
             END LOOP;
END;
END A07_REPLENISHMENT_INT;
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply