Since 2010, OraERP is a Oracle Forums, Community of Oracle Professionals including Fusion/Cloud Application Consultants, Enterprise Architects, ERP Cloud, HCM Cloud, CX Cloud and OCI Experts, ERP Experts, Oracle Apps Functional Consultants, Apps DBAs, DBAs, Cloud DBAs, Digital Architect, PaaS Experts, IaaS, OCI Architects, Technical Consultants, Fusion Middleware Experts, SQL, PL/SQL Developers and Project Managers. Welcome to OraERP.com Social Community, a friendly and active community of Oracle Technology Professionals who believe that technology can ‘make the world a better place’. By joining Oracle ERP Community you will have the ability to Post Topics, Receive our Newsletter, subscribe to threads and access many other special features. Registration is Quick and Simple. Get unlimited access to Oracle Tutorials, Articles, eBooks, Tools and Tips .
Thread Rating:
  • 59 Vote(s) - 2.71 Average
  • 1
  • 2
  • 3
  • 4
  • 5
API - Assign Item to an another Organization in Oracle Inventory
12-26-2012, 03:37 PM, (This post was last modified: 12-26-2012, 03:42 PM by Richard.)
#1
API - Assign Item to an another Organization in Oracle Inventory
Release R12+
Assign Item to an another Organization in Oracle Inventory by using API EGO_ITEM_PUB.ASSIGN_ITEM_TO_ORG

API EGO_ITEM_PUB.ASSIGN_ITEM_TO_ORG provides functionality for maintaining items, item revisions, and etc. let’s use ASSIGN_ITEM_TO_ORG procedure to assign one item to an organization.
The procedure definition is:

PROCEDURE Assign_Item_To_Org(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE
,p_commit IN VARCHAR2 DEFAULT G_FALSE
,p_Inventory_Item_Id IN NUMBER DEFAULT G_MISS_NUM
,p_Item_Number IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Organization_Id IN NUMBER DEFAULT G_MISS_NUM
,p_Organization_Code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Primary_Uom_Code IN VARCHAR2 DEFAULT G_MISS_CHAR
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER);

The parameters are:
 P_API_VERSION – A decimal number indicating major and minor revisions to the API. Pass 1.0 unless otherwise indicated in the API parameter list.
 P_INIT_MSG_LIST – A one-character flag indicating whether to initialize the FND_MSG_PUB package’s message stack at the beginning of API processing (and thus remove any messages that may exist on the stack from prior processing in the same session). Valid values are FND_API.G_TRUE and FND_API.G_FALSE.
 P_COMMIT – A one-character flag indicating whether to commit work at the end of API processing. Valid values are FND_API.G_TRUE and FND_API.G_FALSE.
 P_INVENTORY_ITEM_ID – Inventory Item Id of the Item
 P_ITEM_NUMBER – Segment1 of the Item
 P_ORGANIZATION_ID – Organization Id of the Organization to whom Item must be assigned
 P_ORGANIZATION_CODE – 3 character Organization Code of the Organization to whom Item must be assigned
 P_PRIMARY_UOM_CODE – Primary Unit of Measure of the item.
 X_RETURN_STATUS – A one-character code indicating whether any errors occurred during processing (in which case error messages will be present on the FND_MSG_PUB package’s message stack). Valid values are FND_API.G_RET_STS_SUCCESS, FND_API.G_RET_STS_ERROR, and FND_API.G_RET_STS_UNEXP_ERROR.
 X_MSG_COUNT – An integer indicating the number of messages on the FND_MSG_PUB package’s message stack at the end of API processing.
Sample Code: (Tested in R12.1.3)
DECLARE
g_user_id fnd_user.user_id%TYPE :=NULL;
l_appl_id fnd_application.application_id%TYPE;
l_resp_id fnd_responsibility_tl.responsibility_id%TYPE;
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := fnd_api.g_false;
l_commit VARCHAR2(2) := FND_API.G_FALSE;
x_message_list error_handler.error_tbl_type;
x_return_status VARCHAR2(2);
x_msg_count NUMBER := 0;
BEGIN
SELECT fa.application_id
INTO l_appl_id
FROM fnd_application fa
WHERE fa.application_short_name = 'INV';

SELECT fr.responsibility_id
INTO l_resp_id
FROM fnd_application fa, fnd_responsibility_tl fr
WHERE fa.application_short_name = 'INV'
AND fa.application_id = fr.application_id
AND UPPER (fr.responsibility_name) = 'INVENTORY';

fnd_global.apps_initialize (g_user_id, l_resp_id, l_appl_id);

EGO_ITEM_PUB.ASSIGN_ITEM_TO_ORG(
P_API_VERSION => l_api_version
, P_INIT_MSG_LIST => l_init_msg_list
, P_COMMIT => l_commit
, P_INVENTORY_ITEM_ID => 1003
, p_item_number => 000000000001035
, p_organization_id => 11047
, P_ORGANIZATION_CODE => 'DXN'
, P_PRIMARY_UOM_CODE => 'EA'
, X_RETURN_STATUS => x_return_status
, X_MSG_COUNT => x_msg_count
);
DBMS_OUTPUT.PUT_LINE('Status: '||x_return_status);
IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
DBMS_OUTPUT.PUT_LINE('Error Messages :');
Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list);
FOR j IN 1..x_message_list.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(x_message_list(j).message_text);
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception Occured :');
DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
END;

Item Category Creation APIs
There are few APIs in INV_ITEM_CATEGORY_PUB package related to item category. This article will follow a category flexfield structure. Please refer the below post for more detail.
INV_ITEM_CATEGORY_PUB.Create_Category:
DECLARE
l_category_rec INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE;
l_return_status VARCHAR2(80);
l_error_code NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(80);
l_out_category_id NUMBER;
BEGIN
l_category_rec.segment1 := 'RED';

SELECT f.ID_FLEX_NUM
INTO l_category_rec.structure_id
FROM FND_ID_FLEX_STRUCTURES f
WHERE f.ID_FLEX_STRUCTURE_CODE = 'INV_COLORS';

l_category_rec.description := 'Red';

INV_ITEM_CATEGORY_PUB.Create_Category
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
x_return_status => l_return_status,
x_errorcode => l_error_code,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_category_rec => l_category_rec,
x_category_id => l_out_category_id
);
IF l_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line ('Creation of Item Category is Successful : '||l_out_category_id);
ELSE
DBMS_OUTPUT.put_line ('Creation of Item Category Failed with the error :'||l_error_code);
ROLLBACK;
END IF;
END ;

INV_ITEM_CATEGORY_PUB. Delete_Category:

DECLARE
l_return_status VARCHAR2(80);
l_error_code NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(80);
l_category_id NUMBER;
BEGIN
SELECT mcb.CATEGORY_ID
INTO l_category_id
FROM mtl_categories_b mcb
WHERE mcb.SEGMENT1='RED'
AND mcb.STRUCTURE_ID =
(SELECT mcs_b.STRUCTURE_ID
FROM mtl_category_sets_b mcs_b
WHERE mcs_b.CATEGORY_SET_ID =
(SELECT mcs_tl.CATEGORY_SET_ID
FROM mtl_category_sets_tl mcs_tl
WHERE CATEGORY_SET_NAME ='INV_COLORS_SET'
)
);

INV_ITEM_CATEGORY_PUB.Delete_Category
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
x_return_status => l_return_status,
x_errorcode => l_error_code,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_category_id => l_category_id);

IF l_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line ('Deletion of Item Category is Successful : '||l_category_id);
ELSE
DBMS_OUTPUT.put_line ('Deletion of Item Category Failed with the error :'||l_error_code);
ROLLBACK;
END IF;
END ;



INV_ITEM_CATEGORY_PUB.Update_Category_Description
Updates the category description.

DECLARE
l_return_status VARCHAR2(80);
l_error_code NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(80);
l_category_id NUMBER;
l_description VARCHAR2(80);
BEGIN
select mcb.CATEGORY_ID into l_category_id
from mtl_categories_b mcb
where mcb.SEGMENT1='BLACK'
and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
from mtl_category_sets_b mcs_b
where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
from mtl_category_sets_tl mcs_tl
where CATEGORY_SET_NAME ='INV_COLORS_SET'));

l_description := 'new black color';

INV_ITEM_CATEGORY_PUB.Update_Category_Description (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
x_return_status => l_return_status,
x_errorcode => l_error_code,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_category_id => l_category_id,
p_description => l_description);

IF l_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line ('Update of Item Category Description is Successful : '||l_category_id);
ELSE
DBMS_OUTPUT.put_line ('Update of Item Category Description Failed with the error :'||l_error_code);
ROLLBACK;
END IF;
END ;

Use following API for assigning a category to a category set. A category will be available in the list of valid categoies for a category set only if it is assigned to the category set. This is a required step if for categories enforce list is checked on.

INV_ITEM_CATEGORY_PUB.Create_Valid_Category

Create a record in mtl_category_set_valid_cats.

DECLARE
l_return_status VARCHAR2(80);
l_error_code NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(80);
l_category_set_id NUMBER;
l_category_id NUMBER;
BEGIN
select mcs_tl.CATEGORY_SET_ID into l_category_set_id
from mtl_category_sets_tl mcs_tl
where mcs_tl.CATEGORY_SET_NAME ='INV_COLORS_SET';

select mcb.CATEGORY_ID into l_category_id
from mtl_categories_b mcb
where mcb.SEGMENT1='RED'
and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
from mtl_category_sets_b mcs_b
where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
from mtl_category_sets_tl mcs_tl
where CATEGORY_SET_NAME ='INV_COLORS_SET'));

INV_ITEM_CATEGORY_PUB.Create_Valid_Category (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
x_return_status => l_return_status,
x_errorcode => l_error_code,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_category_set_id => l_category_set_id,
p_category_id => l_category_id,
p_parent_category_id => NULL );

IF l_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line ('Create Valid Category is Successful : '||l_category_id);
ELSE
DBMS_OUTPUT.put_line ('Create Valid Category Failed with the error :'||l_error_code);
ROLLBACK;
END IF;
END ;



INV_ITEM_CATEGORY_PUB.Delete_Valid_Category

Delete the record from mtl_category_set_valid_cats.

DECLARE
l_return_status VARCHAR2(80);
l_error_code NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(80);
l_category_set_id NUMBER;
l_category_id NUMBER;
BEGIN
select mcs_tl.CATEGORY_SET_ID into l_category_set_id
from mtl_category_sets_tl mcs_tl
where mcs_tl.CATEGORY_SET_NAME ='INV_COLORS_SET';

select mcb.CATEGORY_ID into l_category_id
from mtl_categories_b mcb
where mcb.SEGMENT1='RED'
and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
from mtl_category_sets_b mcs_b
where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
from mtl_category_sets_tl mcs_tl
where CATEGORY_SET_NAME ='INV_COLORS_SET'));

INV_ITEM_CATEGORY_PUB.Delete_Valid_Category (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
x_return_status => l_return_status,
x_errorcode => l_error_code,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_category_set_id => l_category_set_id,
p_category_id => l_category_id);

IF l_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line ('Delete Valid Category is Successful : '||l_category_id);
ELSE
DBMS_OUTPUT.put_line ('Delete Valid Category Failed with the error :'||l_error_code);
ROLLBACK;
END IF;
END ;
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Oracle EBS R12 Financials Configurations Summary Presentation M Irfan 1 14,051 01-02-2023, 06:50 AM
Last Post: leo.chen
  Oracle eBusiness Suite Practice Instance Free Access Kashif Manzoor 3 51,639 05-26-2022, 06:45 PM
Last Post: Kashif Manzoor
  Oracle R12 i-Procurement step-by-step setup document admin 3 39,363 04-05-2022, 10:48 PM
Last Post: Kashif Manzoor
  Oracle Financials - Data Flow From SLA to GL admin 9 94,082 11-17-2021, 05:53 AM
Last Post: kmorad
  Oracle HCM Cloud Interview Questions MM Ifan 1 55,706 06-06-2021, 12:16 AM
Last Post: zaidmd
  Oracle Implementation Project Plan admin 35 160,184 04-29-2021, 06:59 AM
Last Post: Cwahsayz
  Oracle ERP Implementation Project Schedule based on OUM Kashif Manzoor 7 30,548 03-31-2021, 11:45 PM
Last Post: haydara
  Oracle Inventory Module End User Training Material James Robert 1 31,830 08-27-2020, 08:59 PM
Last Post: shahid
  Oracle Inventory Miscellaneous Transactions Mark Donald 1 31,084 08-20-2020, 05:34 PM
Last Post: shahid
  Oracle Fusion Financials General Ledger Essentials M Irfan 5 100,710 08-20-2020, 05:32 PM
Last Post: shahid



Users browsing this thread: 1 Guest(s)