Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

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);
begin
if (p_consumer_id is not null) and (p_complaint_type is not null) and
(p_problem_summary is not null) then
begin

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

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

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

--specify the SR attributes
l_service_request_rec.summary             := substr(p_problem_summary,
1,
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
begin
select user_id
into v_user_id
from apps.fnd_user
where upper(user_name) in ('INT_USER');
exception
when others then
dbms_output.put_line('Error in selecting the interface user');
end;

begin
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;
exception
when others then
dbms_output.put_line('Error in selecting the responsibility/application');
end;

--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);
dbms_output.put_line(lx_msg_data);
end loop;
else
--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);
dbms_output.put_line(lx_msg_data);
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;
begin
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;
exception
when others then
dbms_output.put_line('Could not obtain task details for updation');
end;

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 LOOP;
fnd_message.set_encoded(lx_msg_data);
dbms_output.put_line(lx_msg_data);
END IF;
END IF;
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
begin
select supply_resource_id
into v_resource_id
from xxx_supp_resource_map
where territory_code = v_country;
exception
when others then
dbms_output.put_line('Error in selecting resource_id');
end;

dbms_output.put_line('l_task_assignment_id' ||
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 LOOP;
fnd_message.set_encoded(lx_msg_data);
dbms_output.put_line(lx_msg_data);
END IF;
end if;
end if;

--commit all or none
--if task assignment was successfull
if (lx_return_status = 'S') then
commit;
--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.';
else
rollback;
--dbms_output.put_line('The complaint could not be registered:');
x_complaint_number := 'Service Request could not be created';
end if;

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

2 responses to “Sample code for Service Request and Task APIs

  1. Pingback: Using Web ADI to create Service Request | Oracle Apps Notes

  2. Azaz Shaikh October 7, 2013 at 10:42 am

    Can you please suggest the API to submit SR ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 38 other followers

%d bloggers like this: