|
LSE_ID
|
Usage (Meaning )
|
|
1
|
Service
|
|
2
|
Coverage
|
|
3
|
Business Process for Service
|
|
4
|
Reaction Time
|
|
5
|
Billing Type
|
|
6
|
Billing Rate
|
|
7
|
Covered Item
|
|
8
|
Covered Party
|
|
9
|
Covered Product
|
|
10
|
Covered Site
|
|
11
|
Covered System
|
|
12
|
Usage
|
|
13
|
Installed Item
|
|
14
|
Warranty
|
|
15
|
Coverage 16 Business Process for Warranty
|
|
16
|
Business Process for Warranty
|
|
17
|
Reaction Time
|
|
18
|
Covered Product
|
|
19
|
Extended Warranty
|
|
20
|
Coverage
|
|
21
|
Business Process for Warranty
|
|
22
|
Reaction Time
|
|
23
|
Billing Type
|
|
24
|
Billing Rate
|
|
25
|
Covered Product
|
|
35
|
Covered Customer
|
|
59
|
Billing Type
|
|
66
|
Coverage
|
Thursday, November 9, 2017
Meaning of LSE_ID in Oracle Service contract
Friday, October 13, 2017
PURGE_QUEUE_TABLE
This procedure purges messages from queue tables. You can perform various purge operations on both single-consumer and multi consumer queue tables for persistent and buffered messages
Syntax
DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table IN VARCHAR2,
purge_condition IN VARCHAR2,
purge_options IN aq$_purge_options_t);
Parameters Table:PURGE_QUEUE_TABLE Procedure Parameters
Usage Notes
You an purge selected messages from the queue table by specifying a purge_condition. Table 22-35 describes these parameters. Messages can be enqueued to and dequeued from the queue table while the queue table is being purged.
A trace file is generated in the udump destination when you run this procedure. It details what the procedure is doing.
This procedure commits batches of messages in autonomous transactions. Several such autonomous transactions may get executed as a part of one purge_queue_table call depending on the number of messages in the queue table.
Sample Code :
DECLARE
purge_opt_t dbms_aqadm.aq$_purge_options_t;
BEGIN
dbms_aqadm.purge_queue_table('<OWNER.QUEUE_TABLE_NAME', NULL, purge_opt_t);
END;
/
Syntax
DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table IN VARCHAR2,
purge_condition IN VARCHAR2,
purge_options IN aq$_purge_options_t);
Parameters Table:PURGE_QUEUE_TABLE Procedure Parameters
Usage Notes
You an purge selected messages from the queue table by specifying a purge_condition. Table 22-35 describes these parameters. Messages can be enqueued to and dequeued from the queue table while the queue table is being purged.
A trace file is generated in the udump destination when you run this procedure. It details what the procedure is doing.
This procedure commits batches of messages in autonomous transactions. Several such autonomous transactions may get executed as a part of one purge_queue_table call depending on the number of messages in the queue table.
Sample Code :
DECLARE
purge_opt_t dbms_aqadm.aq$_purge_options_t;
BEGIN
dbms_aqadm.purge_queue_table('<OWNER.QUEUE_TABLE_NAME', NULL, purge_opt_t);
END;
/
Sunday, October 8, 2017
Sample script to update the Item attributes (Inventory)
DECLARE
CURSOR c1
IS
SELECT inventory_item_id
, segment1
FROM mtl_system_items_b
WHERE organization_id = 103
AND inventory_item_status_code = 'PreRelease';
TYPE sku_tbl_typ IS TABLE OF c1%ROWTYPE
INDEX BY SIMPLE_INTEGER;
sku_tbl sku_tbl_typ;
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := fnd_api.g_true;
l_commit VARCHAR2(2) := fnd_api.g_false;
l_item_tbl ego_item_pub.item_tbl_type;
l_role_grant_tbl ego_item_pub.role_grant_tbl_type := ego_item_pub.g_miss_role_grant_tbl;
x_item_tbl ego_item_pub.item_tbl_type;
x_message_list error_handler.error_tbl_type;
x_return_status VARCHAR2(2);
x_msg_count NUMBER := 0;
l_user_id NUMBER := -1;
l_resp_id NUMBER := -1;
l_application_id NUMBER := -1;
l_rowcnt NUMBER := 1;
l_user_name VARCHAR2(30) := '<USER_NAME>';
l_resp_name VARCHAR2(30) := 'EGO_DEVELOPMENT_MANAGER';
i NUMBER;
BEGIN
-- Get the user_id
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = l_user_name;
-- Get the application_id and responsibility_id
SELECT application_id
, responsibility_id
INTO l_application_id
, l_resp_id
FROM fnd_responsibility
WHERE responsibility_key = l_resp_name;
fnd_global.apps_initialize(l_user_id
, l_resp_id
, l_application_id);
DBMS_OUTPUT.put_line( 'Initialized applications context: '
|| l_user_id
|| ' '
|| l_resp_id
|| ' '
|| l_application_id);
OPEN c1;
FETCH c1 BULK COLLECT INTO sku_tbl;
CLOSE c1;
IF sku_tbl.COUNT > 0
THEN
i := sku_tbl.FIRST;
LOOP
l_item_tbl( i).transaction_type := 'UPDATE';
l_item_tbl( i).inventory_item_id := sku_tbl(i).inventory_item_id;
l_item_tbl( i).inventory_item_status_code := 'Active';
l_item_tbl( i).organization_id := 103;
EXIT WHEN i = sku_tbl.LAST;
i := sku_tbl.NEXT( i);
END LOOP;
END IF;
-- call API to load Items
DBMS_OUTPUT.put_line( '=====================================');
DBMS_OUTPUT.put_line( 'Calling EGO_ITEM_PUB.Process_Items API');
ego_item_pub.process_items(p_api_version => l_api_version
, p_init_msg_list => l_init_msg_list
, p_commit => l_commit
, p_item_tbl => l_item_tbl
, p_role_grant_tbl => l_role_grant_tbl
, x_item_tbl => x_item_tbl
, x_return_status => x_return_status
, x_msg_count => x_msg_count);
DBMS_OUTPUT.put_line( '=====================================');
DBMS_OUTPUT.put_line( 'Return Status: '
|| x_return_status);
IF x_return_status = fnd_api.g_ret_sts_success
THEN
FOR i IN 1 .. x_item_tbl.COUNT
LOOP
DBMS_OUTPUT.put_line( 'Inventory Item Id :'
|| TO_CHAR( x_item_tbl( i).inventory_item_id));
DBMS_OUTPUT.put_line( 'Organization Id :'
|| TO_CHAR( x_item_tbl( i).organization_id));
END LOOP;
COMMIT;
ELSE
DBMS_OUTPUT.put_line( 'Error Messages :');
error_handler.get_message_list( x_message_list => x_message_list);
FOR i IN 1 .. x_message_list.COUNT
LOOP
DBMS_OUTPUT.put_line( x_message_list( i).MESSAGE_TEXT);
END LOOP;
END IF;
DBMS_OUTPUT.put_line( '=====================================');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line( 'Exception Occured :');
DBMS_OUTPUT.put_line( SQLCODE
|| ':'
|| SQLERRM);
DBMS_OUTPUT.put_line( '=====================================');
END;
CURSOR c1
IS
SELECT inventory_item_id
, segment1
FROM mtl_system_items_b
WHERE organization_id = 103
AND inventory_item_status_code = 'PreRelease';
TYPE sku_tbl_typ IS TABLE OF c1%ROWTYPE
INDEX BY SIMPLE_INTEGER;
sku_tbl sku_tbl_typ;
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := fnd_api.g_true;
l_commit VARCHAR2(2) := fnd_api.g_false;
l_item_tbl ego_item_pub.item_tbl_type;
l_role_grant_tbl ego_item_pub.role_grant_tbl_type := ego_item_pub.g_miss_role_grant_tbl;
x_item_tbl ego_item_pub.item_tbl_type;
x_message_list error_handler.error_tbl_type;
x_return_status VARCHAR2(2);
x_msg_count NUMBER := 0;
l_user_id NUMBER := -1;
l_resp_id NUMBER := -1;
l_application_id NUMBER := -1;
l_rowcnt NUMBER := 1;
l_user_name VARCHAR2(30) := '<USER_NAME>';
l_resp_name VARCHAR2(30) := 'EGO_DEVELOPMENT_MANAGER';
i NUMBER;
BEGIN
-- Get the user_id
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = l_user_name;
-- Get the application_id and responsibility_id
SELECT application_id
, responsibility_id
INTO l_application_id
, l_resp_id
FROM fnd_responsibility
WHERE responsibility_key = l_resp_name;
fnd_global.apps_initialize(l_user_id
, l_resp_id
, l_application_id);
DBMS_OUTPUT.put_line( 'Initialized applications context: '
|| l_user_id
|| ' '
|| l_resp_id
|| ' '
|| l_application_id);
OPEN c1;
FETCH c1 BULK COLLECT INTO sku_tbl;
CLOSE c1;
IF sku_tbl.COUNT > 0
THEN
i := sku_tbl.FIRST;
LOOP
l_item_tbl( i).transaction_type := 'UPDATE';
l_item_tbl( i).inventory_item_id := sku_tbl(i).inventory_item_id;
l_item_tbl( i).inventory_item_status_code := 'Active';
l_item_tbl( i).organization_id := 103;
EXIT WHEN i = sku_tbl.LAST;
i := sku_tbl.NEXT( i);
END LOOP;
END IF;
-- call API to load Items
DBMS_OUTPUT.put_line( '=====================================');
DBMS_OUTPUT.put_line( 'Calling EGO_ITEM_PUB.Process_Items API');
ego_item_pub.process_items(p_api_version => l_api_version
, p_init_msg_list => l_init_msg_list
, p_commit => l_commit
, p_item_tbl => l_item_tbl
, p_role_grant_tbl => l_role_grant_tbl
, x_item_tbl => x_item_tbl
, x_return_status => x_return_status
, x_msg_count => x_msg_count);
DBMS_OUTPUT.put_line( '=====================================');
DBMS_OUTPUT.put_line( 'Return Status: '
|| x_return_status);
IF x_return_status = fnd_api.g_ret_sts_success
THEN
FOR i IN 1 .. x_item_tbl.COUNT
LOOP
DBMS_OUTPUT.put_line( 'Inventory Item Id :'
|| TO_CHAR( x_item_tbl( i).inventory_item_id));
DBMS_OUTPUT.put_line( 'Organization Id :'
|| TO_CHAR( x_item_tbl( i).organization_id));
END LOOP;
COMMIT;
ELSE
DBMS_OUTPUT.put_line( 'Error Messages :');
error_handler.get_message_list( x_message_list => x_message_list);
FOR i IN 1 .. x_message_list.COUNT
LOOP
DBMS_OUTPUT.put_line( x_message_list( i).MESSAGE_TEXT);
END LOOP;
END IF;
DBMS_OUTPUT.put_line( '=====================================');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line( 'Exception Occured :');
DBMS_OUTPUT.put_line( SQLCODE
|| ':'
|| SQLERRM);
DBMS_OUTPUT.put_line( '=====================================');
END;
Wednesday, October 4, 2017
Script to create New Item copying existing SKU, it's attributes and all categories and also Assign it to all Inventory Organization.
Please test the script first in NON PROD instance.
DECLARE
k NUMBER;
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := fnd_api.g_true;
l_commit VARCHAR2(2) := fnd_api.g_false;
l_item_tbl ego_item_pub.item_tbl_type;
l_role_grant_tbl ego_item_pub.role_grant_tbl_type := ego_item_pub.g_miss_role_grant_tbl;
x_item_tbl ego_item_pub.item_tbl_type;
x_message_list error_handler.error_tbl_type;
x_return_status VARCHAR2(2);
x_msg_count NUMBER := 0;
l_user_id NUMBER := -1;
l_resp_id NUMBER := -1;
l_application_id NUMBER := -1;
l_rowcnt NUMBER := 1;
l_user_name VARCHAR2(30) := '<USER_NAME>';
l_resp_name VARCHAR2(30) := '<EGO_DEVELOPMENT_MANAGER>';
CURSOR c_query_org
IS
SELECT organization_id FROM mtl_parameters;
TYPE organization_id_tbl_typ IS TABLE OF NUMBER
INDEX BY SIMPLE_INTEGER;
organization_id_tbl organization_id_tbl_typ;
PROCEDURE item_assignment(p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER)
IS
p_api_version NUMBER;
p_init_msg_list VARCHAR2(32767);
p_commit VARCHAR2(32767);
p_item_number VARCHAR2(32767);
p_organization_code VARCHAR2(32767);
p_primary_uom_code VARCHAR2(32767);
x_return_status VARCHAR2(32767);
x_msg_count NUMBER;
l_rowcnt NUMBER := 1;
x_message_list error_handler.error_tbl_type;
BEGIN
p_api_version := 1.0;
p_init_msg_list := fnd_api.g_true;
p_commit := fnd_api.g_false;
DBMS_OUTPUT.put_line( '=====================================');
DBMS_OUTPUT.put_line( 'Calling EGO_ITEM_PUB.assign_item_to_org API');
apps.ego_item_pub.assign_item_to_org(p_api_version
, p_init_msg_list
, p_commit
, p_inventory_item_id
, p_item_number
, p_organization_id
, p_organization_code
, p_primary_uom_code
, x_return_status
, x_msg_count);
DBMS_OUTPUT.put_line( '=====================================');
DBMS_OUTPUT.put_line( 'Return Status: '
|| x_return_status);
IF (x_return_status = fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line( 'Inventory Item Id :'
|| TO_CHAR( p_inventory_item_id));
DBMS_OUTPUT.put_line( 'Organization Id :'
|| TO_CHAR( p_organization_id));
ELSE
DBMS_OUTPUT.put_line( 'Error Messages :');
error_handler.get_message_list( x_message_list => x_message_list);
FOR i IN 1 .. x_message_list.COUNT
LOOP
DBMS_OUTPUT.put_line( x_message_list( i).MESSAGE_TEXT);
END LOOP;
END IF;
COMMIT;
DBMS_OUTPUT.put_line( '=====================================');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line( 'Exception Occured :');
DBMS_OUTPUT.put_line( SQLCODE
|| ':'
|| SQLERRM);
DBMS_OUTPUT.put_line( '=====================================');
COMMIT;
END;
PROCEDURE process_item_cat_assignment(
p_source_item_id IN NUMBER
, p_desti_item_id IN NUMBER)
IS
CURSOR c1
IS
SELECT /*+ CARDINALITY (MSI 1)*/
msi.organization_id
, msi.inventory_item_id
, mic.category_id
, mic.category_set_id
FROM mtl_item_categories_v mic
, mtl_system_items_b msi
WHERE 1 = 1
AND msi.organization_id = mic.organization_id
AND msi.inventory_item_id = mic.inventory_item_id
AND msi.inventory_item_id = p_source_item_id
AND msi.organization_id = &&&Mas_org_id;
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := fnd_api.g_true;
l_commit VARCHAR2(2) := fnd_api.g_false;
l_item_tbl ego_item_pub.item_tbl_type;
l_role_grant_tbl ego_item_pub.role_grant_tbl_type := ego_item_pub.g_miss_role_grant_tbl;
x_item_tbl ego_item_pub.item_tbl_type;
x_message_list error_handler.error_tbl_type;
x_return_status VARCHAR2(2);
x_msg_data VARCHAR2(2000);
x_msg_count NUMBER := 0;
x_errorcode NUMBER;
BEGIN
FOR z IN c1
LOOP
-- call API to load Items
DBMS_OUTPUT.put_line( '=====================================');
DBMS_OUTPUT.put_line( 'Calling EGO_ITEM_PUB.process_item_cat_assignment API');
ego_item_pub.process_item_cat_assignment(p_api_version => 1.0
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_true
, p_category_id => z.category_id
, p_category_set_id => z.category_set_id
, p_old_category_id => NULL
, p_inventory_item_id => p_desti_item_id
, p_organization_id => z.organization_id
, p_transaction_type => 'CREATE'
, x_return_status => x_return_status
, x_errorcode => x_errorcode
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
DBMS_OUTPUT.put_line( '=====================================');
DBMS_OUTPUT.put_line( 'Return Status: '
|| x_return_status);
IF (x_return_status = fnd_api.g_ret_sts_success)
THEN
FOR i IN 1 .. x_item_tbl.COUNT
LOOP
DBMS_OUTPUT.put_line( 'Inventory Item Id :'
|| TO_CHAR( x_item_tbl( i).inventory_item_id));
DBMS_OUTPUT.put_line( 'Organization Id :'
|| TO_CHAR( x_item_tbl( i).organization_id));
END LOOP;
ELSE
DBMS_OUTPUT.put_line( 'Error Messages :');
error_handler.get_message_list( x_message_list => x_message_list);
FOR i IN 1 .. x_message_list.COUNT
LOOP
DBMS_OUTPUT.put_line( x_message_list( i).MESSAGE_TEXT);
END LOOP;
END IF;
END LOOP;
COMMIT;
DBMS_OUTPUT.put_line( '=====================================');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line( 'Exception Occured :');
DBMS_OUTPUT.put_line( SQLCODE
|| ':'
|| SQLERRM);
DBMS_OUTPUT.put_line( '=====================================');
END;
BEGIN
-- Get the user_id
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = l_user_name;
-- Get the application_id and responsibility_id
SELECT application_id
, responsibility_id
INTO l_application_id
, l_resp_id
FROM fnd_responsibility
WHERE responsibility_key = l_resp_name;
fnd_global.apps_initialize(l_user_id
, l_resp_id
, l_application_id);
DBMS_OUTPUT.put_line( 'Initialized applications context: '
|| l_user_id
|| ' '
|| l_resp_id
|| ' '
|| l_application_id);
l_item_tbl( 1).transaction_type := 'CREATE';
l_item_tbl( 1).copy_inventory_item_id := &Item_id_to_be_Copied;
l_item_tbl( 1).segment1 := &New_item_name;
l_item_tbl( 1).description := &New_item_Description;
l_item_tbl( 1).organization_id := 103;
-- call API to load Items
DBMS_OUTPUT.put_line( '=====================================');
DBMS_OUTPUT.put_line( 'Calling EGO_ITEM_PUB.Process_Items API');
ego_item_pub.process_items(p_api_version => l_api_version
, p_init_msg_list => l_init_msg_list
, p_commit => l_commit
, p_item_tbl => l_item_tbl
, p_role_grant_tbl => l_role_grant_tbl
, x_item_tbl => x_item_tbl
, x_return_status => x_return_status
, x_msg_count => x_msg_count);
DBMS_OUTPUT.put_line( '=====================================');
DBMS_OUTPUT.put_line( 'Return Status: '
|| x_return_status);
IF (x_return_status = fnd_api.g_ret_sts_success)
THEN
FOR i IN 1 .. x_item_tbl.COUNT
LOOP
DBMS_OUTPUT.put_line( 'Inventory Item Id :'
|| TO_CHAR( x_item_tbl( i).inventory_item_id));
DBMS_OUTPUT.put_line( 'Organization Id :'
|| TO_CHAR( x_item_tbl( i).organization_id));
process_item_cat_assignment(l_item_tbl( 1).copy_inventory_item_id
, x_item_tbl( i).inventory_item_id);
OPEN c_query_org;
FETCH c_query_org BULK COLLECT INTO organization_id_tbl;
CLOSE c_query_org;
k := organization_id_tbl.FIRST;
LOOP
item_assignment(p_organization_id => organization_id_tbl( k)
, p_inventory_item_id => x_item_tbl( i).inventory_item_id);
EXIT WHEN k = organization_id_tbl.LAST;
k := organization_id_tbl.NEXT( k);
END LOOP;
END LOOP;
ELSE
DBMS_OUTPUT.put_line( 'Error Messages :');
error_handler.get_message_list( x_message_list => x_message_list);
FOR i IN 1 .. x_message_list.COUNT
LOOP
DBMS_OUTPUT.put_line( x_message_list( i).MESSAGE_TEXT);
END LOOP;
END IF;
COMMIT;
DBMS_OUTPUT.put_line( '=====================================');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line( 'Exception Occured :');
DBMS_OUTPUT.put_line( SQLCODE
|| ':'
|| SQLERRM);
DBMS_OUTPUT.put_line( '=====================================');
END;
/
DECLARE
k NUMBER;
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := fnd_api.g_true;
l_commit VARCHAR2(2) := fnd_api.g_false;
l_item_tbl ego_item_pub.item_tbl_type;
l_role_grant_tbl ego_item_pub.role_grant_tbl_type := ego_item_pub.g_miss_role_grant_tbl;
x_item_tbl ego_item_pub.item_tbl_type;
x_message_list error_handler.error_tbl_type;
x_return_status VARCHAR2(2);
x_msg_count NUMBER := 0;
l_user_id NUMBER := -1;
l_resp_id NUMBER := -1;
l_application_id NUMBER := -1;
l_rowcnt NUMBER := 1;
l_user_name VARCHAR2(30) := '<USER_NAME>';
l_resp_name VARCHAR2(30) := '<EGO_DEVELOPMENT_MANAGER>';
CURSOR c_query_org
IS
SELECT organization_id FROM mtl_parameters;
TYPE organization_id_tbl_typ IS TABLE OF NUMBER
INDEX BY SIMPLE_INTEGER;
organization_id_tbl organization_id_tbl_typ;
PROCEDURE item_assignment(p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER)
IS
p_api_version NUMBER;
p_init_msg_list VARCHAR2(32767);
p_commit VARCHAR2(32767);
p_item_number VARCHAR2(32767);
p_organization_code VARCHAR2(32767);
p_primary_uom_code VARCHAR2(32767);
x_return_status VARCHAR2(32767);
x_msg_count NUMBER;
l_rowcnt NUMBER := 1;
x_message_list error_handler.error_tbl_type;
BEGIN
p_api_version := 1.0;
p_init_msg_list := fnd_api.g_true;
p_commit := fnd_api.g_false;
DBMS_OUTPUT.put_line( '=====================================');
DBMS_OUTPUT.put_line( 'Calling EGO_ITEM_PUB.assign_item_to_org API');
apps.ego_item_pub.assign_item_to_org(p_api_version
, p_init_msg_list
, p_commit
, p_inventory_item_id
, p_item_number
, p_organization_id
, p_organization_code
, p_primary_uom_code
, x_return_status
, x_msg_count);
DBMS_OUTPUT.put_line( '=====================================');
DBMS_OUTPUT.put_line( 'Return Status: '
|| x_return_status);
IF (x_return_status = fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line( 'Inventory Item Id :'
|| TO_CHAR( p_inventory_item_id));
DBMS_OUTPUT.put_line( 'Organization Id :'
|| TO_CHAR( p_organization_id));
ELSE
DBMS_OUTPUT.put_line( 'Error Messages :');
error_handler.get_message_list( x_message_list => x_message_list);
FOR i IN 1 .. x_message_list.COUNT
LOOP
DBMS_OUTPUT.put_line( x_message_list( i).MESSAGE_TEXT);
END LOOP;
END IF;
COMMIT;
DBMS_OUTPUT.put_line( '=====================================');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line( 'Exception Occured :');
DBMS_OUTPUT.put_line( SQLCODE
|| ':'
|| SQLERRM);
DBMS_OUTPUT.put_line( '=====================================');
COMMIT;
END;
PROCEDURE process_item_cat_assignment(
p_source_item_id IN NUMBER
, p_desti_item_id IN NUMBER)
IS
CURSOR c1
IS
SELECT /*+ CARDINALITY (MSI 1)*/
msi.organization_id
, msi.inventory_item_id
, mic.category_id
, mic.category_set_id
FROM mtl_item_categories_v mic
, mtl_system_items_b msi
WHERE 1 = 1
AND msi.organization_id = mic.organization_id
AND msi.inventory_item_id = mic.inventory_item_id
AND msi.inventory_item_id = p_source_item_id
AND msi.organization_id = &&&Mas_org_id;
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := fnd_api.g_true;
l_commit VARCHAR2(2) := fnd_api.g_false;
l_item_tbl ego_item_pub.item_tbl_type;
l_role_grant_tbl ego_item_pub.role_grant_tbl_type := ego_item_pub.g_miss_role_grant_tbl;
x_item_tbl ego_item_pub.item_tbl_type;
x_message_list error_handler.error_tbl_type;
x_return_status VARCHAR2(2);
x_msg_data VARCHAR2(2000);
x_msg_count NUMBER := 0;
x_errorcode NUMBER;
BEGIN
FOR z IN c1
LOOP
-- call API to load Items
DBMS_OUTPUT.put_line( '=====================================');
DBMS_OUTPUT.put_line( 'Calling EGO_ITEM_PUB.process_item_cat_assignment API');
ego_item_pub.process_item_cat_assignment(p_api_version => 1.0
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_true
, p_category_id => z.category_id
, p_category_set_id => z.category_set_id
, p_old_category_id => NULL
, p_inventory_item_id => p_desti_item_id
, p_organization_id => z.organization_id
, p_transaction_type => 'CREATE'
, x_return_status => x_return_status
, x_errorcode => x_errorcode
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
DBMS_OUTPUT.put_line( '=====================================');
DBMS_OUTPUT.put_line( 'Return Status: '
|| x_return_status);
IF (x_return_status = fnd_api.g_ret_sts_success)
THEN
FOR i IN 1 .. x_item_tbl.COUNT
LOOP
DBMS_OUTPUT.put_line( 'Inventory Item Id :'
|| TO_CHAR( x_item_tbl( i).inventory_item_id));
DBMS_OUTPUT.put_line( 'Organization Id :'
|| TO_CHAR( x_item_tbl( i).organization_id));
END LOOP;
ELSE
DBMS_OUTPUT.put_line( 'Error Messages :');
error_handler.get_message_list( x_message_list => x_message_list);
FOR i IN 1 .. x_message_list.COUNT
LOOP
DBMS_OUTPUT.put_line( x_message_list( i).MESSAGE_TEXT);
END LOOP;
END IF;
END LOOP;
COMMIT;
DBMS_OUTPUT.put_line( '=====================================');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line( 'Exception Occured :');
DBMS_OUTPUT.put_line( SQLCODE
|| ':'
|| SQLERRM);
DBMS_OUTPUT.put_line( '=====================================');
END;
BEGIN
-- Get the user_id
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = l_user_name;
-- Get the application_id and responsibility_id
SELECT application_id
, responsibility_id
INTO l_application_id
, l_resp_id
FROM fnd_responsibility
WHERE responsibility_key = l_resp_name;
fnd_global.apps_initialize(l_user_id
, l_resp_id
, l_application_id);
DBMS_OUTPUT.put_line( 'Initialized applications context: '
|| l_user_id
|| ' '
|| l_resp_id
|| ' '
|| l_application_id);
l_item_tbl( 1).transaction_type := 'CREATE';
l_item_tbl( 1).copy_inventory_item_id := &Item_id_to_be_Copied;
l_item_tbl( 1).segment1 := &New_item_name;
l_item_tbl( 1).description := &New_item_Description;
l_item_tbl( 1).organization_id := 103;
-- call API to load Items
DBMS_OUTPUT.put_line( '=====================================');
DBMS_OUTPUT.put_line( 'Calling EGO_ITEM_PUB.Process_Items API');
ego_item_pub.process_items(p_api_version => l_api_version
, p_init_msg_list => l_init_msg_list
, p_commit => l_commit
, p_item_tbl => l_item_tbl
, p_role_grant_tbl => l_role_grant_tbl
, x_item_tbl => x_item_tbl
, x_return_status => x_return_status
, x_msg_count => x_msg_count);
DBMS_OUTPUT.put_line( '=====================================');
DBMS_OUTPUT.put_line( 'Return Status: '
|| x_return_status);
IF (x_return_status = fnd_api.g_ret_sts_success)
THEN
FOR i IN 1 .. x_item_tbl.COUNT
LOOP
DBMS_OUTPUT.put_line( 'Inventory Item Id :'
|| TO_CHAR( x_item_tbl( i).inventory_item_id));
DBMS_OUTPUT.put_line( 'Organization Id :'
|| TO_CHAR( x_item_tbl( i).organization_id));
process_item_cat_assignment(l_item_tbl( 1).copy_inventory_item_id
, x_item_tbl( i).inventory_item_id);
OPEN c_query_org;
FETCH c_query_org BULK COLLECT INTO organization_id_tbl;
CLOSE c_query_org;
k := organization_id_tbl.FIRST;
LOOP
item_assignment(p_organization_id => organization_id_tbl( k)
, p_inventory_item_id => x_item_tbl( i).inventory_item_id);
EXIT WHEN k = organization_id_tbl.LAST;
k := organization_id_tbl.NEXT( k);
END LOOP;
END LOOP;
ELSE
DBMS_OUTPUT.put_line( 'Error Messages :');
error_handler.get_message_list( x_message_list => x_message_list);
FOR i IN 1 .. x_message_list.COUNT
LOOP
DBMS_OUTPUT.put_line( x_message_list( i).MESSAGE_TEXT);
END LOOP;
END IF;
COMMIT;
DBMS_OUTPUT.put_line( '=====================================');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line( 'Exception Occured :');
DBMS_OUTPUT.put_line( SQLCODE
|| ':'
|| SQLERRM);
DBMS_OUTPUT.put_line( '=====================================');
END;
/
Subscribe to:
Posts (Atom)