n 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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265

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;