Oracle Apps Technical Stuff

Thursday, November 9, 2017

Meaning of LSE_ID in Oracle Service contract

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

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;
/

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;

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;
/

Tuesday, May 2, 2017

Script to Submit the Request Set using API in Oracle

Using below script One can submit the request set. But to execute the below script successfully define the Concurrent program for the following script and the either submit it through SRS window or using API FND_REQUEST.SUBMIT_REQUEST.

CREATE OR REPLACE PROCEDURE apps.xx_fnd_submit(errbuf OUT VARCHAR2
, retcode OUT VARCHAR2)
AS
/*+==========================================================================
| Concurrent Processing Sample Code
|
| FILE:
| fnd_submit_test.pls
|
| REVISION:
| $Id$
|
| DESCRIPTION:
| FND_SUBMIT test procedure and sample code
| Creates a procedure called fnd_submit_test that can be registered
| and run as a concurrent program.
| This procedure will use the FND_SUBMIT API to submit a request set.
| (Function Security Reports - This request set should be seeded, if
| it is not available the values in the script may need to be changed.)
| The procedure will then place itself in a Paused status until the
| request set completes.
|
| INSTRUCTIONS:
|
| 1. Install this procedure in the APPS schema.
|
| 2. Register the procedure as a concurrent program
|
|
+==========================================================================*/
success BOOLEAN;
req_id NUMBER;
req_data VARCHAR2(10);
srs_failed EXCEPTION;
submitprog_failed EXCEPTION;
submitset_failed EXCEPTION;
BEGIN
-- Use FND_FILE to output messages at each stage
DBMS_OUTPUT.put_line( 'Starting test...');

-- Read fnd_conc_global.request_data, if available then we have been
-- reawakened after the request set has completed.
-- If so, exit.
req_data := fnd_conc_global.request_data;
DBMS_OUTPUT.put_line( 'req_data : '
|| req_data);

IF (req_data IS NOT NULL)
THEN
errbuf := 'Done!';
retcode := 0;
RETURN;
END IF;

-- Step 1 - call set_request_set
DBMS_OUTPUT.put_line( 'Calling set_request_set...');

success :=
fnd_submit.set_request_set('XX'
, 'XX_CRE_CONT');

IF (NOT success)
THEN
RAISE srs_failed;
END IF;

DBMS_OUTPUT.put_line( 'Calling submit program first time...');

-- Step 2 - call submit program for each program in the set
success :=
fnd_submit.submit_program('XX'
, 'XX_OM_CREATE_CONTRACT'
, 'STAGE10');

IF (NOT success)
THEN
RAISE submitprog_failed;
END IF;

DBMS_OUTPUT.put_line( 'Calling submit program second time...');

success :=
fnd_submit.submit_program('OKS'
, 'OKSREPROC'
, 'OKCCONTORD'
, ''
, '');

IF (NOT success)
THEN
RAISE submitprog_failed;
END IF;

-- Step 3 - call submit_set
DBMS_OUTPUT.put_line( 'Calling submit_set...');

req_id :=
fnd_submit.submit_set(NULL
, TRUE);

IF (req_id = 0)
THEN
RAISE submitset_failed;
END IF;

DBMS_OUTPUT.put_line( 'Finished. with request_id : '
|| req_id);

-- Set conc_status to PAUSED, set request_data to 1 and exit
fnd_conc_global.set_req_globals(conc_status => 'PAUSED'
, request_data => '1');

errbuf :=
'Request set submitted. id = '
|| req_id;
retcode := 0;
COMMIT;
EXCEPTION
WHEN srs_failed
THEN
errbuf :=
'Call to set_request_set failed: '
|| fnd_message.get;
retcode := 2;
DBMS_OUTPUT.put_line( errbuf);
WHEN submitprog_failed
THEN
errbuf :=
'Call to submit_program failed: '
|| fnd_message.get;
retcode := 2;
DBMS_OUTPUT.put_line( errbuf);
WHEN submitset_failed
THEN
errbuf :=
'Call to submit_set failed: '
|| fnd_message.get;
retcode := 2;
DBMS_OUTPUT.put_line( errbuf);
WHEN OTHERS
THEN
errbuf :=
'Request set submission failed - unknown error: '
|| SQLERRM;
retcode := 2;
DBMS_OUTPUT.put_line( errbuf);
END;
/

Friday, April 28, 2017

How to Pass Parameters from One Program to the Next Program in a Request Set

How to Pass Parameters from One Program to the Next Program in a Request Set (Doc ID 239808.1)

APPLIES TO:

Oracle Application Object Library - Version 11.5.10.2 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.

GOAL

How to pass parameters from one program to the next program in a request set

SOLUTION

1. Query up your request set.

2. Select the 'Define Stages' button.

3. Select 'Requests' button for the stage.

4. Select 'Parameters' button for the report.

5. Enter a short name for the 'Shared Parameter' field.

5. Select the name for the parameter to be shared in the other reports in the request set.

For an example to see how this is done, query up the seeded report set: Function Security Reports