Item Creation With Organization

About this Document

Purpose

           API Program has to be developed to assign Organization for Inventory Item.

           The Procedure Xx_Item_Orgassign_Api is developed with oracle seeded API program to assign organization for Inventory Item.

         

1      Topical Essay

         API Program is developed to assign Organization for Inventory Item.

1.1      Basic Business Needs

         Through API Program, Organization to be assigned to Oracle Inventory Item.

1.2      Definitions

Instead of Assigning Organization for Inventory Item from Oracle front end screen, API program developed to assign organization.

1.3      Application Logic

User enters the Item and Organization Id in Custom Table. When execute the API Program Xx_Item_Creation_Api , it assigns item to mentioned organization.

1.4    Design Details

         Custom Table Xx_Item_Table Created to get the below input from users.

    1.Item Name

    2.Template

    3.Organization Id.

When execute the procedure Xx_Item_Orgassign_Api, it picks up the records from the custom table with Item_Created_Flag=’N’ and Item_Org_Assign_Flag=’N’ And Organization_id<>Master Org.

  This Program assign only child organization for the item.Before assigning the item to child organization, this program checks whether the item already exist for the Master Organization.

 Once The Item is created, This program updates the Item_Created_Flag=’Y’ and Item_Org_Assign_Flag=’Y’ and Inventory_Item_id by the new inventory Item Id in custom table.

2   Concurrent Program Details

Basic Purpose

             This Procedure assigns organization for Inventory Item.

2.1  SQL  & PL/SQL Statements(Optional)

Table:

     CREATE TABLE APPS.XX_ITEM_TABLE

(

  SNO                   NUMBER,

  ITEM                  VARCHAR2(50 BYTE),

  INVENTORY_ITEM_ID     NUMBER,

  DESCRIPTION           VARCHAR2(500 BYTE),

  TEMPLATE_ID           NUMBER,

  ORG_ID                NUMBER,

  ITEM_CREATED_FLAG     VARCHAR2(1 BYTE),

  ITEM_ORG_ASSIGN_FLAG  VARCHAR2(1 BYTE),

  CREATION_DATE         DATE,

  LAST_UPDATION_DATE    DATE

)

ALTER TABLE APPS.XX_ITEM_TABLE ADD (

  CONSTRAINT XX_ITEM_TABLE_PK

 PRIMARY KEY

 (SNO)

                   

Procedure:

Create Or Replace Procedure Xx_Item_Orgassign_Api

As

V_Errcode            Varchar2(100);

V_Errmsg             Varchar2(500);

X_Return_Status1     Varchar2(4000);

X_Msg_Count1         Number;

V_Count              Number;

V_Count1              Number;

Cursor Item_Org_Assignment Is

Select Sno, Item, Description, Org_Id

From Xx_Item_Table

Where Org_Id<>(Select Organization_Id From  Org_Organization_Definitions Where Organization_Name='Item Master Organization')

And Inventory_Item_Id Is Null

And Nvl(Item_Created_Flag,'N')='N'

And Nvl(Item_Org_Assign_Flag,'N')='N';  

Begin

        Fnd_Global.Apps_Initialize(User_Id=>1184,Resp_Id=>50370,Resp_Appl_Id=>7000);

       

    For J In Item_Org_Assignment Loop

   

                Select Count(*) Into V_Count From Inv.Mtl_System_Items_B Where Segment1=Upper(Trim(J.Item))

                And Organization_Id=(Select Organization_Id From  Org_Organization_Definitions Where Organization_Name='Item Master Organization');

                               

                Select Count(*) Into V_Count1 From Inv.Mtl_System_Items_B Where Segment1=Upper(Trim(J.Item))

                And Organization_Id=J.Org_Id;

               

            If V_Count=1 And V_Count1=0 Then

   

               Ego_Item_Pub.Assign_Item_To_Org(

                           P_Api_Version          => 1.0

                          ,P_Init_Msg_List        => 'T'

                          ,P_Commit               => 'T'

                       ---   ,P_Inventory_Item_Id    => J.Inventory_Item_Id

                          ,P_Item_Number          => Upper(Trim(J.Item))

                          ,P_Organization_Id      => J.Org_Id

                       ---   ,P_Primary_Uom_Code     => J.Primary_Uom_Code

                          ,X_Return_Status        => X_Return_Status1

                          ,X_Msg_Count            => X_Msg_Count1

                          );

                         

                       Select Count(*) Into V_Count From Inv.Mtl_System_Items_B Where Segment1=J.Item

                       And Organization_Id=J.Org_Id;

                      

                    If V_Count=1 Then    

                        Dbms_Output.Put_Line('Item :'||J.Item||' Organization_id :'||J.Org_Id||' Assigned Successfully');

          

                        Update Xx_Item_Table Set Item_Org_Assign_Flag='Y'

                        Where Sno=J.Sno

                        And  Item=J.Item

                        And Org_Id=J.Org_Id

                        And Nvl(Item_Created_Flag,'N')='N'

                        And Nvl(Item_Org_Assign_Flag,'N')='N';

                       

                        Commit;

                    End If;

                   

   

                Else

                    Dbms_Output.Put_Line('Either The Item :'||J.Item||' Is Not Exist For Mster Organization or Already Exist For the Organization'||J.Org_Id||' In Item Master');

            End If;

   

       End Loop;

           

                 Exception

                 When Others Then

                 V_Errcode := Sqlcode;

                 V_Errmsg  := Sqlerrm;

                   Insert Into Xx_Error_Table Values ('INV','XX_ITEM_ORGASSIGN_API',V_Errcode,V_Errmsg,Sysdate,Fnd_Global.User_Id);

                   Dbms_Output.Put_Line('Errcode :'||Sqlcode||'Error Message :'||Sqlerrm);

                   Commit;

End;       

                   

2.2 Prerequisites & Dependencies

            Custom Table Xx_Item_Table is created to get the user Input And Xx_Error_Table is used in exception part of the above procedure to store the error details.

2.3 Output

   The item to be assigned to mentioned organizations.

insert into table

(200,’1001’,null,’Standard Desktop',3084,106,'N','N',sysdate,sysdate)