Item Creation Interface Full Process
Purpose
API Program has to be
developed to create Inventory Item.
The Procedure Xx_Item_Creation_Api is developed with oracle seeded API program to
import Inventory Item into Item Master.
Through
API Program, Oracle Inventory Item to be created.
Instead of Creating
Inventory Item from Oracle front end Item Master screen, API program developed
to create item in Item Master.
User enters the Item
Details Which is to be created into Custom Table. When execute the API Program Xx_Item_Creation_Api , it creates the
Inventory Item in Item Master.
Custom
Table Xx_Item_Table Created to get
the below input from users.
1.Item Name
2.Template
3.Organization Id.
When executes the procedure Xx_Item_Creation_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 creates item, only for the
Master Organization.The New Item Will be created with the given template details.It
does not consider the records from custom table if it has the other
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.
Basic Purpose
This Procedure
Creates Inventory Item Only For Master Organization
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 Apps.Xx_Item_Creation_Api
As
V_Errcode Varchar2(100);
V_Errmsg Varchar2(500);
X_Inventory_Item_Id Number;
X_Organization_Id Number;
X_Return_Status Varchar2(4000);
X_Msg_Data Varchar2(4000);
X_Msg_Count Number;
X_Return_Status1 Varchar2(4000);
X_Msg_Count1 Number;
V_Count Number;
Cursor Item_Creation Is
Select Sno, Item, Description, Org_Id, Template_Id From Xx_Item_Table Where Org_Id=(Select Organization_Id From Org_Organization_Definitions Where Organization_Name='Item
Master Organization')
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_Creation Loop
Select Count(*) Into V_Count From Inv.Mtl_System_Items_B Where Segment1=Upper(Trim(J.Item));
If V_Count=0 Then
Ego_Item_Pub.Process_Item (
P_Api_Version => 1.0
, P_Init_Msg_List => 'T'
, P_Commit => 'T'
, P_Transaction_Type => 'CREATE' --
Update For Updating Item
, P_Segment1 => Upper(J.Item) -- Item Code
, P_Description => J.Description -- Item Description
, P_Long_Description => J.Description -- Item Long Description
, P_Organization_Id => J.Org_Id -- Warehouse Organization Id
, P_Template_Id => J.Template_Id
, P_Inventory_Item_Status_Code => 'Active'
, P_Approval_Status => 'A'
, X_Inventory_Item_Id => X_Inventory_Item_Id
,
X_Organization_Id => X_Organization_Id
, X_Return_Status => X_Return_Status
, X_Msg_Count => X_Msg_Count
, X_Msg_Data => X_Msg_Data
);
If X_Inventory_Item_Id Is Not Null Then
Dbms_Output.Put_Line('Item
:'||J.Item||'
Inventory Id :'||X_Inventory_Item_Id||'
Organization_id :'||X_Organization_Id||'
Created Successfully');
Update Xx_Item_Table Set Item_Created_Flag='Y',Item_Org_Assign_Flag='Y',Inventory_Item_Id=X_Inventory_Item_Id
Where Sno=J.Sno And
Item=J.Item And
Nvl(Item_Created_Flag,'N')='N' And Nvl(Item_Org_Assign_Flag,'N')='N';
Commit;
End If;
Else
Dbms_Output.Put_Line('Item
:'||J.Item||'
Already Exist 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_CREATION_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.
The New Inventory
Item Will be created with the given template id.
insert into
xx_item_table values
(201,'TESTITEMAPI',null,'Test
Item Created on 23-Jul-2013 Thru API',3082,103,'N','N',sysdate,sysdate);
exec Apps.Xx_Item_Creation_Api
No comments:
Post a Comment