In Oracle R12, you will often need to create a program/ script for item assignment i.e. copy the item from one inventory org and assign it to another inventory org. A business scenario can be that your client has a store in a geography and they decided to open a new store in separate geography. So they might ask you to make the items available in new store. This is when the new script will come in the picture. In purely technical terms, MTL_SYSTEM_ITEMS_B has 1 row for each combination of item number and Organization_id. This script will create a row for the item you choose in the organization of your preference.
Before I share the script, let me share some technical details:
1. API Name: ego_item_pub.process_item_org_assignments
2. E Biz Suit Version: 12.2.9
3. Database Version: 19.0.0.0.0
Below is the Item Assignment script. Please note that you need to make a few alterations to suit your need like setting the value of variable “l_v_dest_org_code “, altering the cursor cur_get_item_data for your requirement and giving the correct username to initialize the apps.
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
DECLARE
l_v_dest_org_code VARCHAR2(100) := ‘Destination Inv Org Code’;
l_n_dest_org_id NUMBER := NULL;
l_n_user_id NUMBER ;
l_n_appl_id NUMBER ;
l_n_resp_id NUMBER ;
l_msg VARCHAR2(2000) := NULL;
x_message_list Error_Handler.Error_Tbl_Type;
x_return_status VARCHAR2(100);
x_msg_count NUMBER := 0;
l_rowcnt NUMBER := 0;
l_n_asl_count NUMBER := 0;
x_session_key NUMBER;
x_return_msg VARCHAR2 (2000);
x_errors po_asl_api_error_rec;
l_n_purchasing_org_id NUMBER := NULL;
l_n_bec_org_Id NUMBER := NULL;
l_item_cnt NUMBER := 0;
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := FND_API.G_TRUE;
l_commit VARCHAR2(2) := FND_API.G_TRUE;
--Table type to update the must_use_approved_vendor_flag
l_item_tbl_typ ego_item_pub.item_tbl_type;
x_item_table ego_item_pub.item_tbl_type;
x_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
x_organization_id mtl_system_items_b.organization_id%TYPE;
L_V_FINAL_ERROR_MSG VARCHAR2(4000) := NULL;
L_V_ERROR_MESSAGE VARCHAR2(4000) := NULL;
l_v_vendor_rec cur_get_vendor_data%ROWTYPE;
TYPE l_v_vendor_table IS TABLE OF l_v_vendor_rec%TYPE;
l_v_vendor_tab l_v_vendor_table := l_v_vendor_table();
--Cursor to fetch the item data
CURSOR cur_get_item_data ( p_in_n_org_id IN NUMBER)
IS
SELECT m.segment1,
m.inventory_item_id,
p_in_n_org_id dest_org_id
FROM mtl_system_items_b m
WHERE 1=1
AND <Criteria to identify the items which need to be copied like organization_Id =12345>
AND NOT EXISTS ( Select 1 FROM MTL_SYSTEM_ITEMS_B m1
WHERE m1.inventory_item_id = m.inventory_item_id
AND m1.organization_id = p_in_n_org_id );
l_v_item_rec cur_get_item_data%ROWTYPE;
TYPE l_v_item_table IS TABLE OF l_v_item_rec%TYPE;
l_v_item_tab l_v_item_table := l_v_item_table();
l_item_org_assignment_tbl EGO_ITEM_PUB.ITEM_ORG_ASSIGNMENT_TBL_TYPE;BEGIN
dbms_output.put_line(‘Start’);
–Get the destination Org Id
SELECT organization_id
INTO l_n_dest_org_id
FROM mtl_parameters
WHERE organization_code = l_v_dest_org_code;
dbms_output.put_line(‘l_n_dest_org_id: ‘||l_n_dest_org_id);
–get the user name
SELECT USER_ID
INTO l_n_user_id
FROM FND_USER
WHERE user_name = ‘YOUR USER NAME ‘;
dbms_output.put_line(‘l_n_user_id: ‘||l_n_user_id);
--get the essential info
SELECT application_id,
responsibility_id
INTO l_n_appl_id,
l_n_resp_id
FROM fnd_responsibility_tl
WHERE language = 'US'
AND responsibility_name = 'Inventory';
dbms_output.put_line('l_n_appl_id: '||l_n_appl_id);
dbms_output.put_line('l_n_resp_id: '||l_n_resp_id);
--Setting FND global variables.
fnd_global.APPS_INITIALIZE(user_id=>l_n_user_id,
resp_id=>l_n_resp_id,
resp_appl_id=>l_n_appl_id);
--Lets find out the item details which need to be processed
OPEN cur_get_item_data(l_n_dest_org_id);
FETCH cur_get_item_data BULK COLLECT INTO l_v_item_tab;
CLOSE cur_get_item_data;
--Lets Check if the item needs the assignment to destination org
IF l_v_item_tab.count>0
THEN
dbms_output.put_line('Total Records Fetched: '||l_v_item_tab.count);
For i in l_v_item_tab.FIRST..l_v_item_tab.LAST
LOOP
IF l_v_item_tab(i).dest_org_id IS NULL -- it means item doesnt exist in the destination Org
THEN
--Prepare for the item assignment
l_item_cnt := l_item_cnt + 1;
l_item_org_assignment_tbl(l_item_cnt).inventory_item_id := l_v_item_tab(i).inventory_item_id;
l_item_org_assignment_tbl(l_item_cnt).organization_id := l_n_dest_org_id;
END IF ;
END LOOP ;
IF l_item_cnt>0
THEN
ego_item_pub.process_item_org_assignments(
P_API_VERSION => l_api_version
, P_INIT_MSG_LIST => l_init_msg_list
, P_COMMIT => l_commit
, P_ITEM_ORG_ASSIGNMENT_TBL => l_item_org_assignment_tbl
, X_RETURN_STATUS => x_return_status
, X_MSG_COUNT => x_msg_count
);
IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
THEN
--Seems some records failed.. time to print the details
l_msg := 'Oops.. Seems few records failed.. Below are the details.. ';
dbms_output.put_line (l_msg);
Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list);
FOR i IN 1..x_message_list.COUNT
LOOP
l_msg := x_message_list(i).message_text;
dbms_output.put_line (l_msg);
END LOOP;
END IF;
dbms_output.put_line('Item Assignment ends at '||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
END IF ;
END IF ;EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line(‘Unexpected error while processing the items: ‘||SQLERRM);
END ;
Reference:
support.oracle.com
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
THANK YOU
Check out the Amazon Deals of the day based on your interests..
