Oracle Apps Technical Stuff: February 2014

Friday, February 21, 2014

Sample Subscription Package used for Business event in Oracle applications.

CREATE OR REPLACE PACKAGE xx_delivery_subscription_pkg
AS
FUNCTION subscription(
p_subscription_guid IN RAW
, p_event IN OUT NOCOPY wf_event_t
)
RETURN VARCHAR2;
END xx_delivery_subscription_pkg;
/

CREATE OR REPLACE PACKAGE BODY xx_delivery_subscription_pkg
AS
FUNCTION subscription(
p_subscription_guid IN RAW
, p_event IN OUT NOCOPY wf_event_t
)
RETURN VARCHAR2
IS
l_event_name VARCHAR2(2000);
l_event_key VARCHAR2(2000);
l_err_text VARCHAR2(3000);
l_param_list wf_parameter_list_t;
l_param_name VARCHAR2(240);
l_param_value VARCHAR2(2000);
l_order_number VARCHAR2(50);
BEGIN
l_param_list := p_event.getparameterlist;
l_event_name := p_event.geteventname();
l_event_key := p_event.geteventkey();

IF l_param_list IS NOT NULL
THEN
FOR i IN l_param_list.FIRST .. l_param_list.LAST
LOOP
l_param_name := l_param_list(i).getname;
l_param_value := l_param_list(i).getvalue;
DBMS_OUTPUT.put_line('Parameter Name : ' || l_param_name || 'Parameter Value : ' || l_param_value);

BEGIN
SELECT oh.order_number
INTO l_order_number
FROM wsh_delivery_details wdd
, oe_order_headers_all oh
, wsh_delivery_assignments wda
, wsh_new_deliveries wnd
WHERE wdd.source_header_id = oh.header_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
AND wnd.delivery_id = l_param_value
AND wnd.status_code <> 'OP'
AND wdd.source_code = 'OE'
AND wdd.released_status = 'C';
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('Unexpected Error : ' || SUBSTR(SQLERRM
, 1
, 250
));
END;

INSERT INTO xx_shiment_notification_stg
VALUES (l_param_value
, l_order_number
);
END LOOP;

COMMIT;
END IF;

RETURN 'SUCCESS';
EXCEPTION
WHEN OTHERS
THEN
RETURN 'ERROR';
END subscription;
END xx_delivery_subscription_pkg;

Query to get the Menu,Submenu and function details For Oracle Menus

SELECT second.application_id "App ID"
, second.application_name "App Name"
, second.responsibility_id "Resp ID"
, second.responsibility_name "Responsibility"
, second.menu_id "Menu ID"
, second.user_menu_name "Main Menu Name"
, second.entry_sequence "Seq"
, second.prompt "Prompt"
, second.function_id "Function ID"
, second.user_function_name "Function"
, second.func_descrip "Function Descrip"
, second.sub_menu_id "SubMenu ID"
, second.sub_menu_name "SubMenu Name"
, second.sub_seq "Sub Seq"
, second.sub_prompt "SubPrompt"
, second.sub_func_id "SubFunction ID"
, second.sub_func "SubFunction"
, second.sub_func_descrip "SubFunction Descrip"
, second.sub_sub_menu_id "Sub-SubMenu ID"
, second.grant_flag "Grant Flag"
, second.resp_end_date "Resp End Date"
, DECODE( exc.rule_type
, 'F', ( SELECT 'Ex F: ' || exc.action_id
FROM fnd_form_functions_vl fnc
WHERE fnc.function_id = exc.action_id
AND second.function_id = exc.action_id ) )
excluded_function
, DECODE( exc.rule_type
, 'F', ( SELECT 'Ex SF: ' || exc.action_id
FROM fnd_form_functions_vl fnc
WHERE fnc.function_id = exc.action_id
AND second.sub_func_id = exc.action_id ) )
excluded_sub_function
, DECODE( exc.rule_type
, 'M', ( SELECT 'Ex M: ' || exc.action_id
FROM fnd_form_functions_vl fnc
WHERE fnc.function_id = exc.action_id
AND second.menu_id = exc.action_id ) )
excluded_menu
, DECODE( exc.rule_type
, 'M', ( SELECT 'Ex SM: ' || exc.action_id
FROM fnd_form_functions_vl fnc
WHERE fnc.function_id = exc.action_id
AND second.sub_menu_id = exc.action_id ) )
excluded_sub_menu
, DECODE( exc.rule_type
, 'M', ( SELECT 'Ex SSM: ' || exc.action_id
FROM fnd_form_functions_vl fnc
WHERE fnc.function_id = exc.action_id
AND second.sub_sub_menu_id = exc.action_id ) )
excluded_sub_sub_menu
FROM ( SELECT FIRST.application_id
, FIRST.application_name
, FIRST.responsibility_id
, FIRST.responsibility_name
, FIRST.end_date AS resp_end_date
, FIRST.menu_id
, FIRST.user_menu_name
, FIRST.entry_sequence
, FIRST.prompt
, FIRST.function_id
, ffft.user_function_name
, ffft.description AS func_descrip
, FIRST.sub_menu_id
, fmv2.user_menu_name AS sub_menu_name
, fme2.entry_sequence AS sub_seq
, fmet2.prompt AS sub_prompt
, fme2.function_id AS sub_func_id
, ffft2.user_function_name AS sub_func
, ffft2.description AS sub_func_descrip
, fme2.sub_menu_id AS sub_sub_menu_id
, FIRST.grant_flag
FROM ( SELECT fat.application_id
, fat.application_name
, fr.responsibility_id
, frt.responsibility_name
, fr.end_date
, fr.menu_id
, fmv.user_menu_name
, fme.entry_sequence
, fmet.prompt
, fme.sub_menu_id
, fme.function_id
, fme.grant_flag
FROM apps.fnd_application_tl fat
, apps.fnd_responsibility fr
, apps.fnd_menus_vl fmv
, apps.fnd_responsibility_tl frt
, apps.fnd_menu_entries fme
, apps.fnd_menu_entries_tl fmet
--joins and constant selection
WHERE fat.application_id = fr.application_id(+)
AND fr.menu_id = fmv.menu_id(+)
AND fr.responsibility_id = frt.responsibility_id(+)
AND fr.menu_id = fme.menu_id(+)
AND fme.menu_id = fmet.menu_id(+)
AND fme.entry_sequence = fmet.entry_sequence(+)
AND fmet.language = 'US'
--------------------------------------
-- add specific selection criteria --
--------------------------------------
--and fat.application_id = 840
--for DEVL 19080 rows
--and fr.responsibility_id = 51856
AND fat.application_id = &appid
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) FIRST ---for application, responsibility and main menu info
, apps.fnd_menus_vl fmv2 ---for submenu info
, apps.fnd_menu_entries fme2
, apps.fnd_menu_entries_tl fmet2
, apps.fnd_form_functions_tl ffft ---for function info
, apps.fnd_form_functions_tl ffft2 ---for subfunction info
--left outer joins keep original records and add any sub menu and function info
WHERE FIRST.function_id = ffft.function_id(+)
AND FIRST.sub_menu_id = fmv2.menu_id(+)
AND FIRST.sub_menu_id = fme2.menu_id(+)
AND fme2.menu_id = fmet2.menu_id(+)
AND fme2.entry_sequence = fmet2.entry_sequence(+)
AND fme2.function_id = ffft2.function_id(+)
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21) second -- adds any sub menu and function info
LEFT OUTER JOIN apps.fnd_resp_functions exc ---for exclusions
ON ( second.application_id = exc.application_id
AND second.responsibility_id = exc.responsibility_id
AND ( second.function_id = exc.action_id
OR second.sub_func_id = exc.action_id
OR second.menu_id = exc.action_id
OR second.sub_menu_id = exc.action_id
OR second.sub_sub_menu_id = exc.action_id ) )
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21;

How to Pass the File name as Parameter and populate the request id of the concurrent Program dyanamically using SQL Loader

For one of my client i was having a requirement to pass the file name as parameter to the SQL loader program and capture the file name and the request id in the table.After surfing a lot on google and don't find anything so i come up with the following solution which works for me so thought of sharing the solution. Please find below the details.

1. Create Control file as below
2. In the control file we can use $FILE as the parameter to pass file name as parameter to the SQL Loader Program

LOAD DATA
INFILE '$FILE'
<APPEND/REPLACE/TRUNCATE>
INTO TABLE XX_STAGING_TBL
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
, MAIL_REQUEST_ID DECIMAL EXTERNAL NULLIF (MAIL_REQUEST_ID=BLANKS)
, ORDER_NUMBER DECIMAL EXTERNAL NULLIF (ORDER_NUMBER=BLANKS)
, ORDER_HEADER_ID DECIMAL EXTERNAL NULLIF (ORDER_HEADER_ID=BLANKS)
, LINE_NUMBER DECIMAL EXTERNAL NULLIF (LINE_NUMBER=BLANKS)
, ORDER_LINE_ID DECIMAL EXTERNAL NULLIF (ORDER_LINE_ID=BLANKS)
, MAIL_SEND_STATUS CHAR NULLIF (MAIL_SEND_STATUS=BLANKS)
, TO_RECIPIENT CHAR NULLIF (TO_RECIPIENT=BLANKS)
, CC_RECIPIENT CHAR NULLIF (CC_RECIPIENT=BLANKS)
, CREATION_DATE DATE "MM/DD/YY" NULLIF (CREATION_DATE=BLANKS)
, CREATED_BY DECIMAL EXTERNAL NULLIF (CREATED_BY=BLANKS)
, LAST_UPDATE_DATE DATE "MM/DD/YY" NULLIF (LAST_UPDATE_DATE=BLANKS)
, LAST_UPDATED_BY DECIMAL EXTERNAL NULLIF (LAST_UPDATED_BY=BLANKS)

, REQUEST_ID "STRING_TO_REPLACE"
)

3. Save the file and move it to the XXCUST_TOP/bin directoy.
4. After this write a shell script to execute the Control file as below

### Below command is used to replace the "STRING_TO_REPLACE" with the current request id so that i will insert the constant value in the table for column REQUEST_ID.

sed s/STRING_TO_REPLACE/$p_conc_request_id/ $XX_TOP/bin/XX_DEMO_CTL.ctl > $XX_TOP/bin/${XX_DEMO_CTL}_${p_conc_request_id}.ctl

##go to the directory

cd $XX_TOP/bin

## run the SQLLDR for the New Control file

sqlldr ${p_userid_pwd} control=$XX_TOP/bin/${XX_DEMO_CTL}_${p_conc_request_id}.ctl log=/usr/tmp/XX_DEMO_CTL.log bad=/usr/tmp/XX_DEMO_CTL.bad data=${p_file_name}

## After completion remove the newly created Control file and soft link.

rm ${XX_DEMO_CTL}_${p_conc_request_id}.ctl

exit

5. Define the HOST concurrent program for the above shell script and pass the file name as parameter.

6. submit the Concurrent program and look for the data in the REQUEST_ID column the request id of the Shell Script Concurrent Program should be populated.

7. Please test it before using it on PROD instance.

Note :- We can create new Control file at run time dynamically and remove it after processing the file. Or we can Update the same Control file to keep the value of request id column as constant and once the processing the csv file is complete again run the above mentioned sed command to replace the request id value to a string "STRING_TO_REPLACE"

Wednesday, February 19, 2014

Script to Get the Nth Position String within a String

CREATE OR REPLACE PACKAGE xx_delimited_string
AS

-- p_str - the string to get the i'th word from
-- p_str can be xyz,yxz,zyx,zxy and you want to get the zxy as return ---- Value
-- p_n - the word to get from the string -- an index into the string
-- p_enclosed_by - what the words might be wrapped in. In the above -- -- example, chr(34) is a double quote
-- p_terminated_by - what separates the words. In the above example, ---- chr(44) is a comma.

FUNCTION nth_word(
p_str IN VARCHAR2
, p_n IN VARCHAR2
, p_enclosed_by IN VARCHAR2 DEFAULT ''''
, p_separated_by IN VARCHAR2 DEFAULT ','
)
RETURN VARCHAR2;

PRAGMA RESTRICT_REFERENCES(nth_word, WNDS, RNDS);
END xx_delimited_string;
/

CREATE OR REPLACE PACKAGE BODY xx_delimited_string
AS
TYPE vcarray IS TABLE OF VARCHAR2(2000)
INDEX BY BINARY_INTEGER;

g_words vcarray;
g_empty vcarray;
g_last_string VARCHAR2(4096);

FUNCTION de_quote(
p_str IN VARCHAR2
, p_enc_by IN VARCHAR2
)
RETURN VARCHAR2
IS
BEGIN
RETURN REPLACE(LTRIM(RTRIM(p_str
, p_enc_by
)
, p_enc_by
)
, p_enc_by || p_enc_by
, p_enc_by
);
END de_quote;

PROCEDURE parse_string(
p_str IN VARCHAR2
, p_delim IN VARCHAR2
, p_sep IN VARCHAR2
)
IS
l_n NUMBER DEFAULT 1;
l_in_quote BOOLEAN DEFAULT FALSE;
l_ch CHAR(1);
l_len NUMBER DEFAULT NVL(LENGTH(p_str)
, 0
);
BEGIN
IF (l_len = 0)
THEN
RETURN;
END IF;

g_words := g_empty;
g_words(1) := NULL;

FOR i IN 1 .. l_len
LOOP
l_ch := SUBSTR(p_str
, i
, 1
);

IF (l_ch = p_delim)
THEN
l_in_quote := NOT l_in_quote;
END IF;

IF ( l_ch = p_sep
AND NOT l_in_quote)
THEN
l_n := l_n + 1;
g_words(l_n) := NULL;
ELSE
g_words(l_n) := g_words(l_n) || l_ch;
END IF;
END LOOP;

FOR i IN 1 .. l_n
LOOP
g_words(i) := de_quote(g_words(i)
, p_delim
);
END LOOP;
END parse_string;

FUNCTION nth_word(
p_str IN VARCHAR2
, p_n IN VARCHAR2
, p_enclosed_by IN VARCHAR2 DEFAULT ''''
, p_separated_by IN VARCHAR2 DEFAULT ','
)
RETURN VARCHAR2
IS
BEGIN
IF ( g_last_string IS NULL
OR p_str <> g_last_string)
THEN
g_last_string := p_str;
parse_string(p_str
, p_enclosed_by
, p_separated_by
);
END IF;

RETURN g_words(p_n);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN NULL;
END nth_word;
END xx_delimited_string;
/

How to Test

SELECT xx_delimited_string.nth_word(:p_str
, 4
, CHR(34)
, CHR(44)
)

FROM DUAL

Store timestamp with time zone in VARCHAR2 column and convert it into Date or timestamp Format

Format Timestamp with time zone ('YYYY-MM-DDTHH:MI:SS.FF+TZ') if stored into VARCHAR2 column and needs to convert into 'YYYY-MM-DD HH:MI:SS' then use the Below select statement for conversion.

SELECT CAST (
TO_TIMESTAMP_TZ ('2014-02-12T23:02:14.000+0000',
'YYYY-MM-DD"T"HH24:MI:SS.FF TZH:TZM')
AT TIME ZONE DBTIMEZONE AS DATE)
FROM DUAL

Tuesday, February 18, 2014

Useful Scripts for Discoverer

--List of Business Areas
SELECT ba_name "Business Area"
     , ba_created_by "Creator"
     , ba_created_date "Creation Date"
     , ba_updated_by "Updated By "
     , ba_updated_date "Last Update Date"
     , ba_id
FROM eul5_us.eul5_bas
/
--List of Folders
SELECT   b.ba_name
       , f.obj_name folder_name
       , f.obj_id
       , f.obj_ext_owner owner
    FROM eul5_us.eul5_objs f
       , eul5_us.eul5_ba_obj_links l
       , eul5_us.eul5_bas b
   WHERE 1 = 1
     AND f.obj_id = l.bol_obj_id
     AND b.ba_id = l.bol_ba_id
     AND UPPER (b.ba_name) LIKE UPPER ('%'||'&Business_area'||'%')
     AND UPPER (f.obj_name) LIKE UPPER ('%'||'&FOLDER_NAME'||'%')
ORDER BY b.ba_name
       , f.obj_name
/
--List of Folder Items
SELECT   i.exp_name item_name
       , i.exp_id
       , i.it_ext_column
       , f.obj_name folder_name
       , b.ba_name
    FROM eul5_us.eul5_expressions i
       , eul5_us.eul5_objs f
       , eul5_us.eul5_ba_obj_links l
       , eul5_us.eul5_bas b
   WHERE f.obj_id = i.it_obj_id
     AND f.obj_id = l.bol_obj_id
     AND b.ba_id = l.bol_ba_id
     AND UPPER (i.exp_name) LIKE UPPER ('%'||'&ITEM_NAME'||'%')
     AND UPPER (b.ba_name) LIKE UPPER ('%'||'&Business_area'||'%')
     AND UPPER (f.obj_name) LIKE UPPER ('%'||'&FOLDER_NAME'||'%')
ORDER BY b.ba_name
       , f.obj_name
       , i.exp_name
/
--List of Workbooks
SELECT doc_name "Document"
     , doc_developer_key
     , doc_description "Description"
FROM eul5_us.eul5_documents
/

Way to search a String for LONG data type In oracle using Object types and Pipeline Functions

CREATE OR REPLACE TYPE XX_LONG_DATA_REC_TYPE AS OBJECT(OBJECT_NAME VARCHAR2(240), LONG_DATA_TEXT CLOB);
/

CREATE OR REPLACE TYPE XX_LONG_DATA_TBL AS TABLE OF XX_LONG_DATA_REC_TYPE
/

CREATE OR REPLACE FUNCTION XX_SEARCH_STRING( P_SEARCH_STRING VARCHAR2 )
RETURN XX_LONG_DATA_TBL
PIPELINED
IS
XX_LONG_DATA_REC XX_LONG_DATA_REC_TYPE;
BEGIN
FOR Z IN ( SELECT VIEW_NAME, TEXT
FROM DBA_VIEWS
WHERE ROWNUM <=10 )
LOOP
IF ( UPPER(Z.TEXT) LIKE UPPER(P_SEARCH_STRING) )
THEN
XX_LONG_DATA_REC := XX_LONG_DATA_REC_TYPE( Z.VIEW_NAME, Z.TEXT );
PIPE ROW ( XX_LONG_DATA_REC );
END IF;
END LOOP;
END;
/

Script to test the same

select * from table(xx_search_string('%SELECT%'));

Another way without creating any DB object

DECLARE

L_CLOB_STRING CLOB;

L_LONG_STRING LONG;

L_OBJECT_NAME VARCHAR2( 240 );

BEGIN

SELECT VIEW_NAME, TEXT

INTO L_OBJECT_NAME, L_LONG_STRING

FROM DBA_VIEWS

WHERE ROWNUM = 1;

L_CLOB_STRING := L_LONG_STRING;

IF UPPER( L_CLOB_STRING ) LIKE '%SELECT%'

THEN

DBMS_OUTPUT.PUT_LINE( 'OBJECT NAME : ' || L_OBJECT_NAME );

ELSE

DBMS_OUTPUT.PUT_LINE( 'SELECT SEARCH STRING NOT FOUND IN THE OBJECT NAME : ' || L_OBJECT_NAME );

END IF;

END;

Monday, February 17, 2014

Script to delete the Task in Oracle CRM (JTF_TASKS_PUB.DELETE_TASK)

SET serveroutput on

DECLARE
l_object_ver_num jtf_tasks_v.object_version_number%TYPE := 1;
l_task_status_id jtf_tasks_v.task_status_id%TYPE := 9;
l_task_status_name jtf_tasks_v.task_status%TYPE;
l_task_id jtf_tasks_v.task_id%TYPE := 1333920;
l_task_number jtf_tasks_v.task_number%TYPE;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
BEGIN
jtf_tasks_pub.delete_task (p_api_version => 1.0
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_false
, p_object_version_number => l_object_ver_num
, p_task_id => l_task_id
, p_task_number => l_task_number
, p_delete_future_recurrences => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);

IF l_return_status <> fnd_api.g_ret_sts_success
THEN
IF l_msg_count > 0
THEN
l_msg_data := NULL;

FOR i IN 1 .. l_msg_count
LOOP
l_msg_data := l_msg_data || ' ' || fnd_msg_pub.get (1, 'F');
END LOOP;

fnd_message.set_encoded (l_msg_data);
DBMS_OUTPUT.put_line (SUBSTR (l_msg_data
, 1.1
, 200
));
END IF;

ROLLBACK;
ELSE
DBMS_OUTPUT.put_line ('Task Id = ' || l_task_id);
DBMS_OUTPUT.put_line ('Task Status = ' || l_task_status_id);
DBMS_OUTPUT.put_line ('Return Status = ' || l_return_status);
COMMIT;
END IF;
END;
/

Script to Update the task in Oracle CRM (JTF_TASKS_PUB.UPDATE_TASK)

DECLARE
l_object_ver_num jtf_tasks_v.object_version_number%TYPE := 1;
l_task_status_id jtf_tasks_v.task_status_id%TYPE := 9;
l_task_status_name jtf_tasks_v.task_status%TYPE;
l_task_id jtf_tasks_v.task_id%TYPE := 1333920;
l_task_number jtf_tasks_v.task_number%TYPE;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
BEGIN
JTF_TASKS_PUB.UPDATE_TASK (p_api_version => 1.0
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_false
, p_object_version_number => l_object_ver_num
, p_task_id => l_task_id
, p_task_number => l_task_number
, p_task_status_id => l_task_status_id
, p_source_object_type_code => 'TASK'
, p_source_object_id => l_task_id
, p_source_object_name => l_task_id
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);

IF l_return_status <> fnd_api.g_ret_sts_success
THEN
IF l_msg_count > 0
THEN
l_msg_data := NULL;

FOR i IN 1 .. l_msg_count
LOOP
l_msg_data := l_msg_data || ' ' || fnd_msg_pub.get (1, 'F');
END LOOP;

fnd_message.set_encoded (l_msg_data);
DBMS_OUTPUT.put_line (SUBSTR (l_msg_data
, 1.1
, 200
));
END IF;

ROLLBACK;
ELSE
DBMS_OUTPUT.put_line ('Task Id = ' || l_task_id);
DBMS_OUTPUT.put_line ('Task Status = ' || l_task_status_id);
DBMS_OUTPUT.put_line ('Return Status = ' || l_return_status);
COMMIT;
END IF;
END;
/

Script to Create the tasks in Oracle CRM (JTF_TASKS_PUB.CREATE_TASK)

SET serveroutput on;

DECLARE
l_user_name fnd_user.user_name%TYPE := 'OPERATIONS';
l_task_name jtf_tasks_tl.task_name%TYPE := 'Task1';
l_task_priority jtf_task_priorities_tl.NAME%TYPE := 'Medium';
l_task_status jtf_task_statuses_tl.NAME%TYPE := 'Open';
l_show_on_cal jtf_task_all_assignments.show_on_calendar%TYPE := 'N';
l_planned_start_date DATE := SYSDATE;
l_planned_end_date DATE := SYSDATE;
l_user_id NUMBER := 1318; -- User Id for Operations - Select user_id from fnd_user where user_name = 'OPERATIONS';
l_resource_id NUMBER := 199; -- Resource Id for Operations - select resource_id from jtf_rs_resource_extns where user_name = 'OPERATIONS';
l_task_type_id NUMBER := 9; -- Meeting.
l_task_status_id NUMBER := 10; -- Open;
l_task_priority_id NUMBER;
l_task_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
BEGIN
jtf_tasks_pub.create_task (p_api_version => 1.0
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_false
, p_task_name => l_task_name
, p_task_type_id => l_task_type_id
, p_task_status_id => l_task_status_id
, p_task_priority_id => l_task_priority_id
, p_owner_type_code => 'RS_EMPLOYEE'
, p_owner_id => l_resource_id
, p_show_on_calendar => l_show_on_cal
, p_planned_start_date => l_planned_start_date
, p_planned_end_date => l_planned_end_date
, p_date_selected => 'P'
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_task_id => l_task_id
);

IF l_return_status <> fnd_api.g_ret_sts_success
THEN
IF l_msg_count > 0
THEN
l_msg_data := NULL;

FOR i IN 1 .. l_msg_count
LOOP
l_msg_data := l_msg_data || ' ' || fnd_msg_pub.get (1, 'F');
END LOOP;

fnd_message.set_encoded (l_msg_data);
DBMS_OUTPUT.put_line (l_msg_data);
END IF;

ROLLBACK;
ELSE
DBMS_OUTPUT.put_line ('Task Id = ' || l_task_id);
DBMS_OUTPUT.put_line ('Return Status = ' || l_return_status);
COMMIT;
END IF;
END;
/

Oracle Apps Technical Stuff