Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

Monthly Archives: April 2011

Sample code for Service Request and Task APIs

In this post, you will find the sample code for creating a Service Request(SR) using the  CS_SERVICEREQUEST_PUB.CREATE_SERVICEREQUEST API. The Task created for the SR is then updated using the JTF_TASKS_PUB.UPDATE_TASK API. Finally the Task Assignment for the task is done using the JTF_TASK_ASSIGNMENTS_PUB.CREATE_TASK_ASSIGNMENT API.

The procedure creates a SR provided the Service Request type, Problem Summary and Party Number of the customer for whom the SR is to be created are supplied as inputs. All three input parameters are mandatory. The program returns a message informing whether the SR has been created, along with the SR number(in case it has been generated).

If you are using SyntaxHighlighter(too bad it doesn’t support PL/SQL code, yet) for the first time, move your mouse to the top-right corner of the code section to display the icons. Click on the first icon to view the unformatted code.

SyntaxHighlighter update: You can simply select and copy code without the line numbers from the code section (like you would do with any other text). Double-clicking inside the code section will select everything but it might introduce special characters in webkit browsers such as Chrome.

create or replace procedure xxx_custom_regcomp(p_consumer_id in varchar2, --Party Number
p_complaint_type   in varchar2, --Service Request type
p_problem_summary  in varchar2, --Problem Summary
x_complaint_number out varchar2) as --Stores the output of the procedure

lx_msg_count          number;
lx_msg_data           varchar2(2000);
lx_msg_index_out      number;
lx_return_status      varchar2(1);
l_service_request_rec cs_servicerequest_pub.service_request_rec_type;
l_notes_table         cs_servicerequest_pub.notes_table;
l_contacts_tab        cs_servicerequest_pub.contacts_table;
lx_sr_create_out_rec  cs_servicerequest_pub.sr_create_out_rec_type;
v_user_id             number;
v_resp_id             number;
v_appl_id             number;
l_incident_type_id    number;
l_party_id            number;
v_task_id             number;
v_object_version_id   number;
v_owner_id            number;
l_task_assignment_id  number;
v_resource_id         number;
v_country             varchar2(10);
if (p_consumer_id is not null) and (p_complaint_type is not null) and
(p_problem_summary is not null) then

--fetch the incident type
select cs.incident_type_id
into l_incident_type_id
from cs_incident_types cs
where upper( = upper(p_complaint_type);
when others then
dbms_output.put_line('Error in selecting the problem type');

--fetch the party_id, country for the consumer
v_country := null;
select party_id, country
into l_party_id, v_country
from hz_parties
where party_number = p_consumer_id;
when others then
dbms_output.put_line('Error in selecting the party number');

dbms_output.put_line('v_country' || v_country);

--specify the SR attributes
l_service_request_rec.summary             := substr(p_problem_summary,
240); --limit problem_summary to 240 characters
l_service_request_rec.customer_id         := l_party_id;
l_service_request_rec.type_id             := l_incident_type_id;
l_service_request_rec.request_date        := sysdate;
l_service_request_rec.status_id           := 1;
l_service_request_rec.severity_id         := 3;
l_service_request_rec.caller_type         := 'PERSON';
l_service_request_rec.verify_cp_flag      := 'N';
l_service_request_rec.sr_creation_channel := 'PHONE';
l_service_request_rec.problem_code        := 'CON';

--all such SRs will be created from the INT_USER
select user_id
into v_user_id
from apps.fnd_user
where upper(user_name) in ('INT_USER');
when others then
dbms_output.put_line('Error in selecting the interface user');

select fa.application_id, frt.responsibility_id
into v_appl_id, v_resp_id
from apps.fnd_user_resp_groups  furg,
apps.fnd_application       fa,
apps.fnd_responsibility_tl frt
where fa.application_short_name = 'CSS'
and upper(frt.responsibility_name) =
upper('Custom Responsibility')
and fa.application_id = frt.application_id
and furg.responsibility_application_id = fa.application_id
and furg.responsibility_id = frt.responsibility_id
and furg.user_id = v_user_id
and rownum = 1;
when others then
dbms_output.put_line('Error in selecting the responsibility/application');

--initializing the user
apps.fnd_global.apps_initialize(user_id      => v_user_id,
resp_id      => v_resp_id,
resp_appl_id => v_appl_id);
--create Service request API
cs_servicerequest_pub.create_servicerequest(p_api_version => 4.0,
p_init_msg_list            => fnd_api.g_true,
p_commit                   => fnd_api.g_false,
x_return_status            => lx_return_status,
x_msg_count                => lx_msg_count,
x_msg_data                 => lx_msg_data,
p_resp_appl_id             => v_appl_id,
p_resp_id                  => v_resp_id,
p_user_id                  => v_user_id,
p_org_id                   => 103,
p_request_id               => null,
p_request_number           => null,
p_service_request_rec      => l_service_request_rec,
p_notes                    => l_notes_table,
p_contacts                 => l_contacts_tab,
p_auto_assign              => 'N',
p_auto_generate_tasks      => 'Y',
x_sr_create_out_rec        => lx_sr_create_out_rec,
p_default_contract_sla_ind => 'N');

if (lx_return_status <> fnd_api.g_ret_sts_success) then
if (fnd_msg_pub.count_msg > 1) then
--if there are multi-line error messages, display them
for j in 1 .. fnd_msg_pub.count_msg loop
fnd_msg_pub.get(p_msg_index     => j,
p_encoded       => 'F',
p_data          => lx_msg_data,
p_msg_index_out => lx_msg_index_out);
end loop;
--if there is a single-line error message, display it
fnd_msg_pub.get(p_msg_index     => 1,
p_encoded       => 'F',
p_data          => lx_msg_data,
p_msg_index_out => lx_msg_index_out);
end if;
end if;

--The task for this Service Request if created automatically through the use of Task Templates
--and specifying the 'Service: Auto Generate Tasks on SR Create' profile option to Yes
--if SR creation was successfull then update the task with status='Assigned' and
--populate the scheduled_start_date and scheduled_end_date
if lx_return_status = 'S' then
v_task_id           := null;
v_object_version_id := null;
v_owner_id          := null;
select task_id, object_version_number, owner_id
into v_task_id, v_object_version_id, v_owner_id
from jtf_tasks_vl jtb
where jtb.source_object_id = lx_sr_create_out_rec.request_id;
when others then
dbms_output.put_line('Could not obtain task details for updation');

dbms_output.put_line('TaskId:' || v_task_id);
dbms_output.put_line('ObjectVer:' || v_object_version_id);
dbms_output.put_line('OwnId:' || v_owner_id);

--update Task API
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 => v_object_version_id,
p_task_id               => v_task_id,
p_task_status_id        => 14, --'Assigned'
p_owner_id              => v_owner_id,
p_owner_type_code       => 'RS_EMPLOYEE', --Employee Resource
p_scheduled_start_date  => sysdate,
p_scheduled_end_date    => sysdate,
x_return_status         => lx_return_status,
x_msg_count             => lx_msg_count,
x_msg_data              => lx_msg_data,
p_enable_workflow       => NULL,
p_abort_workflow        => NULL);

--display error messages
IF lx_return_status <> fnd_api.g_ret_sts_success THEN
IF lx_msg_count > 0 THEN
lx_msg_data := NULL;
FOR i IN 1 .. lx_msg_count LOOP
lx_msg_data := lx_msg_data || ' ' || fnd_msg_pub.get(1, 'F');
end if;

--if task was updated successfully then assign the task to appropriate resource
if lx_return_status = 'S' then
l_task_assignment_id := null;
v_resource_id        := null;

--select the appropriate resource
--I am using a custom table which stores my country-to-resource mapping
select supply_resource_id
into v_resource_id
from xxx_supp_resource_map
where territory_code = v_country;
when others then
dbms_output.put_line('Error in selecting resource_id');

dbms_output.put_line('l_task_assignment_id' ||
dbms_output.put_line('v_resource_id' || v_resource_id);

--create Task Assignnmnet API
jtf_task_assignments_pub.create_task_assignment(p_api_version          => 1.0,
p_init_msg_list        => fnd_api.g_true,
p_commit               => fnd_api.g_false,
p_task_assignment_id   => NULL,
p_task_id              => v_task_id,
p_resource_type_code   => 'RS_EMPLOYEE',
p_resource_id          => v_resource_id,
p_assignment_status_id => 3, --Accepted
p_show_on_calendar     => 'Y',
x_return_status        => lx_return_status,
x_msg_count            => lx_msg_count,
x_msg_data             => lx_msg_data,
x_task_assignment_id   => l_task_assignment_id);
--display error messages
IF lx_return_status <> fnd_api.g_ret_sts_success THEN
IF lx_msg_count > 0 THEN
lx_msg_data := NULL;
FOR i IN 1 .. lx_msg_count LOOP
lx_msg_data := lx_msg_data || ' ' || fnd_msg_pub.get(1, 'F');
end if;
end if;

--commit all or none
--if task assignment was successfull
if (lx_return_status = 'S') then
--dbms_output.put_line('Complaint has been registered:'||lx_sr_create_out_rec.request_number);
x_complaint_number := 'Service Request with number:' ||
lx_sr_create_out_rec.request_number||' created.';
--dbms_output.put_line('The complaint could not be registered:');
x_complaint_number := 'Service Request could not be created';
end if;

when others then
--dbms_output.put_line('Unexpected Error ' || sqlerrm);
x_complaint_number := 'Service Request could not be created';
x_complaint_number := 'Invalid input provided';
end if;
end xxx_custom_regcomp;

Accessing Oracle Forms from Workflow(and a nifty Personalization)

It is quite an easy task to enable a user to open a Form directly from an Oracle Workflow Notifications page. In fact, we can also pass parameters to the form so that it automatically queries the form and displays a record based on the parameter which is passed. This post demonstrates how this is done. Along the way we will also look at a small but pretty useful trick – clicking a button through Form Personalization.

Following are the basic steps:

  1. Register a form function as per requirement
  2. Create an attribute of type ‘Form’ which stores the name of the form function and the parameter with which we want to call it.
  3. In a workflow process include a notification message.
  4. Associate the attribute with the notification message

The form that I want to call from Notifications window is the Find Service Request function. When calling the function, I will be passing a parameter which will be populated in the appropriate field and the search button will be clicked automatically.

Since the Find Service Request function does not accept any parameters, first I will register my own function(XXXSRTEST) based on the seeded form so that my function will be able to accept the parameter which I will pass to it from the Workflow.

As you can see from the following screen shots, the only difference between my function and the seeded Find Service Request function(CSXSRISV) is that my function can accept the CUSTOMER_ID parameter.

Add the function to the appropriate menu so that it is accessible from the responsibility from where the Workflow Notifications will be viewed. Now I will open the function and personalize it so that the parameter that I pass is populated in the appropriate field and the Search button is clicked automatically. I create the following personalization rule:
Description:Accept parameter and execute query
Trigger Object:None
Processing Mode:Not In Enter-Query Mode
Level: Site

Then I add three actions to the rule. In the first action(seq#20), I populate the appropriate field in the form with the value stored in the parameter which I created for this function. During runtime, I will pass a value to this parameter from the Workflow.

In the second(seq#30) and third actions(seq#31) I click the Search button automatically through personalization. First I shift focus to the Search button using the ‘GO_ITEM’ builtin.

Then I call the EXECUTE_TRIGGER builtin with argument WHEN-BUTTON-PRESSED to simulate the button press.

Once these personalizations are done, I will make the function inaccessible from the front end by removing the prompt name for the function from the menu.
In the Workflow, I add two attributes – ‘PO Number’ and ‘Open a Form’. ‘PO Number’ is a text type attribute which is set to a value of 4880(this is the parameter which I will pass to my form) and it has the internal name ‘PO_NUM’

‘Open a Form’ is an attribute of type ‘Form’ and it is set to a value XXXSRTEST:CUSTOMER_ID=&PO_NUM. The value indicates that the form function to be opened is XXXSRTEST with the parameter CUSTOMER_ID. The value stored in the attribute PO_NUM will be passed to the parameter.

Create a message and associate both these attributes with the message. Attach the message to a notification.Associating a ‘Form’ type attribute with the notification message displays a link in the Notification at runtime which can be clicked to open the Form.

Run the Workflow and open the notifications page. You will see a link in the ‘References’ sections with the same name as that of the ‘Form’ type attribute.

Clicking on the link opens my custom function ‘XXXSRTEST’ while passing the value 4880(stored in the PO Number attribute) to parameter CUSTOMER_ID. The personalizations in the function populates the Service Request Number field with the value of the parameter and automatically clicks on the Search button to execute the query and the appropriate record is displayed.


  1. The form function will open automatically only if it is accessible from the same responsibility from which the Workflow Notification is viewed otherwise it will prompt you to choose the responsibility manually.
  2. The syntax for accessing a form with parameters may vary depending upon the EBS version, in my instance XXXSRTEST:CUSTOMER_ID=&PO_NUM works. If it doesn’t work in your system try using XXXSRTEST:CUSTOMER_ID=’&PO_NUM’ or XXXSRTEST:CUSTOMER_ID=”&PO_NUM”.