[Home] [Help]
PACKAGE BODY: APPS.IBU_REQ_PKG
Source
1 PACKAGE BODY IBU_REQ_PKG as
2 /* $Header: ibursrb.pls 120.11 2007/11/16 12:41:14 mpathani ship $ */
3 /*======================================================================+
4 | Copyright (c) 1999 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +======================================================================+
8 | History |
9 | 12.20.99 Alex Lau Created |
10 | 6-APR-2001 Alan Lau |
11 | Add major enhancement for 11.5.4.F |
12 | Commented out UpdateStatus, UpdateUrgency, |
13 | CreateTask, and GetContract. |
14 | 03/01/2002 klou, added error code |
15 | 13/03/2002 klou, add logic to handle employee logging SRs |
16 | NOTE: This version is based on 115.81. 115.82 inlcudes |
17 | multiple notes and has been leapfrogged. |
18 | 18/03/2002 klou, |
19 | 1. add p_serial_number to create_service_request. |
20 | 2. add logic to fetch serial number and tag number |
21 | from install base table. |
22 | 25-MAR-2002 allau |
23 | Remove assignment manager logic to leverage TeleService |
24 | auto-assignment feature |
25 | 29-MAR-2002 ALLAU |
26 | Pass LAST_UPDATE_CHANNEL := 'WEB' when calling |
27 | TeleService CreateServiceRequest and |
28 | UpdateServiceRequest APIs. |
29 | 31-MAR-2002 KLOU (UCONTACT) |
30 | Add new parameters to UpdateServiceRequest to hanlde |
31 | update contacts in SR detail. |
32 | 11-APR-2002 KLOU (ASSG) |
33 | SR creation: add logic to check whether CS |
34 | Auto-Assignment is ON. If not, check whether IBU profile|
35 | of "use default resource owner" is ON. If yes, fecth |
36 | owner group, owner id, group type from profile. |
37 | Otherwise, do noting. |
38 | 15-APR-2002 KLOU (PLANG) |
39 | Set perferred language to appropriate column, i.e. not |
40 | use attribute6. |
41 | 25-MAY-2002 WMA |
42 | Add the SR location address information to the create |
43 | API. |
44 | 31-MAY-2002 KLOU |
45 | Set category id to null if it is -1 during SR creation. |
46 | 20-AUG-2002 WMA |
47 | Get the default SR owner type for the SR creation |
48 | 17-OCT-2002 WMA |
49 | 1. modified the create API according to CS change |
50 | 2. add five more parameters for bill to and ship to |
51 | 01-NOV-2002 WMA |
52 | 1. add the attachment category ID |
53 | 06-NOV-2002 SPOLAMRE |
54 | Add DFF attributes |
55 | 13-NOV-2002 WMA |
56 | Check the DEF parameters null case |
57 | 115.100 06-DEC-2002 WZLI changed OUT and IN OUT calls to use NOCOPY |
58 | hint to enable pass by reference. |
59 | changed the api version from 2.0 to 3.0. |
60 | added two parameters: p_bill_to_party_id |
61 | and p_ship_to_party_id in the create |
62 | service request procedure. |
63 | 02-Jan-2002 WMA add one more API get_default_status API |
64 | add the responsibility id to pass in |
65 | 31-Jan-2003 SPOLAMRE |
66 | Changed the PROCEDURE AddAttachment to take file name as|
67 | parameter and pass it to the API |
68 | FND_WEBATTCH.ADD_ATTACHMENT |
69 | 115.106 11-SEP-2003 wzli Made changes for create SR for 11.5.10 |
70 | 115.107 15-SEP-2003 WZLI Change the location type to |
71 | HZ_PARTY_SITE |
72 | 115.108 09-OCT-2003 WZLI added procedure decodeErrorMsg |
73 | 115.109 10-OCT-2003 WZLI Remove debug message from decodeErrorMsg|
74 | 115.110 12-OCT-2003 WMA change the update API for 11510 requirement|
75 | 115.111 20-OCT-2003 wzli added two parameterss: p_street_number and|
76 | p_timezone_id in the create SR procedure. |
77 | 115.112 19-NOV-2003 wzli Fixed problem(bug#3063305): 'No entries |
78 | found for List of Values error when trying|
79 | to query the Item field only for SR's |
80 | created via iSupport. |
81 | 115.113 03-DEC-2003 WZLI add logic to decode message |
82 | FORM_RECORD_CHANGED |
83 | 115.114 15-DEC-2003 WZLI Because the error messages in the error |
84 | stack are separated by chr(0), they need |
85 | to be parsed befor we decode them. |
86 | 115.115 29-DEC-2003 WZLI Made change to code logic: If installed |
87 | base product is selected, don't save the |
88 | serial number. |
89 | 115.116 09-DEC-2003 WMA modify the Email logic, increase the size |
90 | of Email body. |
91 | 115.117 19-Jan-2003 WMA fixed bug 3377241. |
92 | AUTO ASSIGNMENT ON UPDATE THRU I-SUPPORT |
93 | NOT FUNCTIONING |
94 | 115.118 27-JAN-2004 mkcyee In AddAttachment, the function name |
95 | parameter should be obtained from the |
96 | profile and not hard-coded to CSXSRISR |
97 | 115.119 10-MAR-2004 WZLI added parameter: p_note_status. |
98 | 115.120 16-APR-2004 WZLI When creating sr, pass sysdate to |
99 | parameter incident_occurred_date. |
100 | 115.121 09-JUN-2004 WZLI Fixed problem(bug#3676419):In procedure |
101 | get_default_status(),select status_group_id|
102 | from cs_incident_types_b instead of from |
103 | cs_incident_types_vl |
104 | 115.122 29-JUL-2004 WZLI Fixed problem(bug#3796975):Saving Service |
105 | request gives invalid date error. |
106 | 115.123 15-OCT-2004 WMA for sendEmail function, use userID |
107 | fullname as role display name. |
108 | 115.124 28-NOV-2004 WMA add the srID in the Send Email API. |
109 | change the way to start work flow. |
110 | add new API startEmailWorkFlow(). |
111 | 115.125 29-NOV-2004 WMA change the way to create role for Email. |
112 | 115.126 30-NOV-2004 WMA change the rolename. |
113 | 120.1 04-AUG-2005 WMA added pending approval flag checking for |
114 | default statuses. |
115 | 120.2 09-SEP-2005 WMA add the logic to handle the multi bytes |
116 | 120.3 28-NOV-2005 WZLI made change for link object enhancement. |
117 | 120.4 28-NOV-2005 WZLI Fixed GSCC error. |
118 | 120.5 10-DEC-2005 WMA add validate_http_service_ticket() |
119 | change the logic for workflow APIs. |
120 | 120.6 19-JAN-2006 WMA change the sequence name for the WF role |
121 | 120.7 30-JAN-2006 WMA modify the logic for creating emp role. |
122 | 120.9 14-Nov-2007 MPATHANI Profile 'Service : Default Group Owner|
123 | Type for Service Request' is obsoleted, |
124 | added 'RS_GROUP'. |
125 +======================================================================*/
126
127 /**
128 * UpdateServiceRequest
129 */
130 PROCEDURE UpdateServiceRequest(
131 p_request_id IN NUMBER,
132 p_status_id IN NUMBER,
133 p_urgency_id IN NUMBER,
134 p_problem_description IN VARCHAR2,
135 p_problem_detail IN VARCHAR2,
136 p_note_type IN VARCHAR2,
137 p_last_updated_by IN NUMBER,
138 p_language IN VARCHAR2,
139 -- UCONTACT
140 p_contact_party_id IN JTF_NUMBER_TABLE := null,
141 p_contact_type IN JTF_VARCHAR2_TABLE_100 := null,
142 p_contact_point_id IN JTF_NUMBER_TABLE := null,
143 p_contact_point_type IN JTF_VARCHAR2_TABLE_100 := null,
144 p_contact_primary IN JTF_VARCHAR2_TABLE_100 := null,
145 p_sr_contact_point_id IN JTF_NUMBER_TABLE := null,
146 -- DONE
147 x_return_status OUT NOCOPY VARCHAR2,
148 x_msg_count OUT NOCOPY NUMBER,
149 x_msg_data OUT NOCOPY VARCHAR2
150 )
151 IS
152 l_msg_count NUMBER;
153 l_call_id NUMBER;
154 l_last_update_date DATE := SYSDATE;
155 l_object_version_number NUMBER;
156 l_sr_rec CS_SERVICEREQUEST_PVT.service_request_rec_type;
157 l_notes_table CS_SERVICEREQUEST_PVT.notes_table;
158 x_interaction_id NUMBER;
159 l_contacts_table CS_SERVICEREQUEST_PVT.contacts_table;
160 l_org_id NUMBER;
161 l_workflow_process_id NUMBER;
162 l_index BINARY_INTEGER;
163
164 l_validate_sr_closure VARCHAR2(1) := 'N';
165 l_auto_close_child_entities VARCHAR2(1) := 'N';
166 l_temp_close_value VARCHAR2(100);
167
168 l_retainCharNum number := 0;
169 l_truncateCharNum number := 0;
170 l_responsibility_id NUMBER; --Wei Ma
171 cursor cur_contact_type(p_party_id number) is --UCONTACT
172 select party_type
173 from hz_parties
174 where party_id = p_party_id;
175
176 l_sr_update_out_rec CS_ServiceRequest_PVT.sr_update_out_rec_type;
177 l_cs_auto_assignment VARCHAR2(1) := 'N';
178
179 BEGIN
180 select OBJECT_VERSION_NUMBER
181 into l_object_version_number
182 from CS_INCIDENTS_ALL_B
183 where INCIDENT_ID = p_request_id;
184
185 -- UCONTACT
186 if p_contact_point_id.count > 0 then
187 for l_index in p_contact_point_id.FIRST..p_contact_point_id.LAST loop
188 if(nvl(p_contact_point_id(l_index), -1) > 0 ) then
189 if( p_sr_contact_point_id(l_index) = -1) then
190 l_contacts_table(l_index).sr_contact_point_id := null;
191 else
192 l_contacts_table(l_index).sr_contact_point_id := p_sr_contact_point_id(l_index);
193 end if;
194
195 l_contacts_table(l_index).party_id := p_contact_party_id(l_index);
196 l_contacts_table(l_index).contact_point_id := p_contact_point_id(l_index);
197 l_contacts_table(l_index).primary_flag := p_contact_primary(l_index);
198 l_contacts_table(l_index).contact_point_type := p_contact_point_type(l_index);
199 if(p_contact_type(l_index)= 'CUSTOMER') then
200 open cur_contact_type(l_contacts_table(l_index).party_id);
201 fetch cur_contact_type into l_contacts_table(l_index).contact_type;
202 close cur_contact_type;
203 else -- for employee
204 l_contacts_table(l_index).contact_type := 'EMPLOYEE';
205 if p_contact_point_type(l_index) <> 'PHONE' then
206 l_contacts_table(l_index).contact_point_id := null;
207 end if;
208
209 end if;
210 end if;
211 end loop;
212 end if; -- end p_contact_point_id is not null
213 -- DONE
214 if (p_problem_description is NOT NULL ) THEN
215 l_notes_table(1).note_type := p_note_type;
216 -- l_notes_table(1).note_type := FND_PROFILE.VALUE('IBU_SR_UPDATE_NOTE_TYPE');
217 -- l_notes_table(1).note := p_problem_description;
218 check_string_length_bites(
219 p_string => p_problem_description,
220 p_targetlen => 2000,
221 x_returnLen => l_retainCharNum,
222 x_truncateCharNum => l_truncateCharNum
223 );
224 if(l_truncateCharNum > 0) then
225 l_notes_table(1).note := substr(p_problem_description, 0, l_retainCharNum);
226 else
227 l_notes_table(1).note := p_problem_description;
228 end if;
229 l_retainCharNum := 0;
230 l_truncateCharNum := 0;
231 l_notes_table(1).note_detail := p_problem_detail;
232 l_notes_table(1).note_status := 'E';
233 END IF;
234
235 CS_SERVICEREQUEST_PVT.INITIALIZE_REC(p_sr_record => l_sr_rec);
236 l_sr_rec.language := p_language;
237 if (p_status_id > 0) then
238 l_sr_rec.status_id := p_status_id;
239 end if;
240
241 if (p_urgency_id > 0) then
242 l_sr_rec.urgency_id := p_urgency_id;
243 end if;
244
245 l_sr_rec.last_update_channel := 'WEB';
246
247 -- added by weim
248 l_sr_rec.creation_program_code := 'ISUPPORTSRUI';
249 l_sr_rec.last_update_program_code := 'ISUPPORTSRUI';
250
251 -- added by wei ma
252 select fnd_global.resp_id into l_responsibility_id from dual;
253
254
255 -- added for 11.5.10
256 l_temp_close_value := FND_PROFILE.VALUE('CS_SR_AUTO_CLOSE_CHILDREN');
257 if (l_temp_close_value = 'CS_SR_VALIDATE_AND_CLOSE') then
258 l_auto_close_child_entities := 'Y';
259 l_validate_sr_closure := 'Y';
260 end if;
261
262 if (l_temp_close_value = 'CS_SR_NONE') then
263 l_auto_close_child_entities := 'N';
264 l_validate_sr_closure := 'N';
265 end if;
266
267 -- added in 11510 for auto-assignment.
268 Select nvl(fnd_profile.value('CS_AUTO_ASSIGN_OWNER_HTML'), 'N')
269 Into l_cs_auto_assignment From dual;
270
271 CS_SERVICEREQUEST_PVT.UPDATE_SERVICEREQUEST(
272 p_api_version => 4.0,
273 p_init_msg_list => fnd_api.g_true,
274 p_resp_id => l_responsibility_id,
275 p_commit => fnd_api.g_true,
276 p_request_id => p_request_id,
277 p_object_version_number => l_object_version_number,
278 p_last_updated_by => p_last_updated_by,
279 p_last_update_date => l_last_update_date,
280 p_service_request_rec => l_sr_rec,
281 p_notes => l_notes_table,
282 p_contacts => l_contacts_table,
283 p_validate_sr_closure => l_validate_sr_closure,
284 p_auto_close_child_entities => l_auto_close_child_entities,
285 x_msg_count => x_msg_count,
286 p_auto_assign =>l_cs_auto_assignment,
287 x_return_status => x_return_status,
288 x_msg_data => x_msg_data,
289 x_sr_update_out_rec => l_sr_update_out_rec);
290
291 if (x_return_status <> 'S') then
292 decodeErrorMsg();
293 end if;
294
295 /* CS_SERVICEREQUEST_PVT.UPDATE_SERVICEREQUEST(
296 p_api_version => 4.0,
297 p_init_msg_list => fnd_api.g_true,
298 p_resp_id => l_responsibility_id,
299 p_commit => fnd_api.g_true,
300 p_request_id => p_request_id,
301 p_object_version_number => l_object_version_number,
302 p_last_updated_by => p_last_updated_by,
303 p_last_update_date => l_last_update_date,
304 p_service_request_rec => l_sr_rec,
305 p_notes => l_notes_table,
306 p_contacts => l_contacts_table,
307 -- p_validate_sr_closure => l_validate_sr_closure,
308 -- p_auto_close_child_entities => l_auto_close_child_entities,
309 x_msg_count => x_msg_count,
310 x_workflow_process_id => l_workflow_process_id,
311 x_return_status => x_return_status,
312 x_msg_data => x_msg_data,
313 x_interaction_id => x_interaction_id); */
314
315 END UpdateServiceRequest;
316
317 /**
318 * AddAttachment
319 */
320 procedure AddAttachment(
321 p_request_id IN NUMBER,
322 p_user_id IN VARCHAR2,
323 p_media_id IN NUMBER,
324 p_name IN VARCHAR2,
325 p_desc IN VARCHAR2)
326 is
327 seq_num NUMBER := 10;
328 p_category_id NUMBER := nvl(FND_PROFILE.VALUE('IBU_A_SR_DEFAULT_ATTACHMENT_CATEGORY'), 1);
329 -- mkcyee 01/27/2004 - we obtain the function name from the profile
330 -- to secure attachments
331 l_function_name FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := nvl(FND_PROFILE.value('IBU_SR_ATTACH_SECURITY_FUNC'), 'CSXSRISR');
332
333 begin
334 select NVL(max(seq_num),0) + 10
335 into seq_num
336 from fnd_attached_documents
337 where entity_name = 'CS_INCIDENTS'
338 and pk1_value = p_request_id;
339
340 FND_WEBATTCH.ADD_ATTACHMENT(
341 seq_num => seq_num,
342 category_id => p_category_id,
343 document_description => p_desc,
344 datatype_id => '6',
345 text => null,
346 file_name => p_name,
347 url => null,
348 function_name => l_function_name,
349 entity_name => 'CS_INCIDENTS',
350 pk1_value => p_request_id,
351 pk2_value => null,
352 pk3_value => null,
353 pk4_value => null,
354 pk5_value => null,
355 media_id => p_media_id,
356 user_id => p_user_id);
357 end AddAttachment;
358
359
360 /**
361 * CREATE_SERVICE_REQUEST
362 * Thin PL/SQL wrapper for callling TeleService API.
363 */
364 procedure create_service_request(
365 p_request_number IN OUT NOCOPY VARCHAR2,
366 p_type_id IN NUMBER,
367 p_account_id IN NUMBER,
368 p_product IN NUMBER,
369 p_inventory_item IN NUMBER,
370 p_problem_code_id IN VARCHAR2,
371 p_caller_type IN VARCHAR2,
372 p_language IN VARCHAR2,
373 p_urgency_id IN NUMBER,
374 p_summary IN VARCHAR2,
375 p_problem_description IN jtf_varchar2_table_32767,
376 p_problem_detail IN jtf_varchar2_table_32767,
377 p_note_status in jtf_varchar2_table_100,
378 p_contact_party_id in jtf_number_table,
379 p_contact_type in jtf_varchar2_table_100,
380 p_contact_point_id IN jtf_number_table,
381 p_contact_point_type in jtf_varchar2_table_100,
382 p_contact_primary in jtf_varchar2_table_100,
383
384 p_status_id IN NUMBER,
385 p_severity_id IN NUMBER,
386 -- p_owner_id IN NUMBER,
387 p_user_id IN NUMBER,
388 p_customer_id IN NUMBER,
389 p_platform_id IN NUMBER,
390 p_cp_revision_id IN NUMBER,
391 p_inv_item_revision IN VARCHAR2,
392 p_helpdesk_no IN VARCHAR2,
393 p_party_id IN NUMBER,
394 p_solved IN VARCHAR2,
395 p_employee_id IN NUMBER,
396 p_note_type IN jtf_varchar2_table_100,
397 p_contract_id in varchar2,
398 p_project_num in varchar2,
399 p_short_code in varchar2,
400 p_os_version in varchar2,
401 p_db_version in varchar2,
402 p_product_revision in varchar2,
403 -- p_attr_6 in varchar2,
404 p_cust_pref_lang_code in varchar2 := NULL,
405 p_pref_contact_method in varchar2,
406 p_rollout in varchar2,
407 p_error_code in varchar2 := NULL,
408 p_serial_number in varchar2 := NULL,
409 p_inv_category_id in NUMBER,
410 p_time_zone_id in NUMBER,
411 -- for the SR location information
412 p_location_id in NUMBER,
413 p_address in varchar2 := NULL,
414 p_city in varchar2 := NULL,
415 p_state in varchar2 := NULL,
416 p_country in varchar2 := NULL,
417 p_province in varchar2 := NULL,
418 p_postal_code in varchar2 := NULL,
419 p_county in varchar2 := NULL,
420 -- add the following for 11.5.10
421 p_addrLine2 in varchar2 := NULL,
422 p_addrLine3 in varchar2 := NULL,
423 p_addrLine4 in varchar2 := NULL,
424 p_poboxNumber in varchar2 := NULL,
425 p_houseNumber in varchar2 := NULL,
426 p_streetSuffix in varchar2 := NULL,
427 p_street in varchar2 := NULL,
428 p_street_number in varchar2 := NULL,
429 p_floor in varchar2 := NULL,
430 p_suite in varchar2 := NULL,
431 p_postalPlus4Code in varchar2 := NULL,
432 p_position in varchar2 := NULL,
433 p_locationDirections in varchar2 := NULL,
434 p_description in varchar2 := NULL,
435 p_pointOfInterest in varchar2 := NULL,
436 p_crossStreet in varchar2 := NULL,
437 p_directionQualifier in varchar2 := NULL,
438 p_distanceQualifier in varchar2 := NULL,
439 p_distanceQualUom in varchar2 := NULL,
440 --for the bill to and ship to
441 p_bill_to_site_id in NUMBER,
442 p_bill_to_contact_id in NUMBER,
443 p_ship_to_site_id in NUMBER,
444 p_ship_to_contact_id in NUMBER,
445 p_install_site_use_id in NUMBER,
446 p_bill_to_party_id in NUMBER,
447 p_ship_to_party_id in NUMBER,
448 -- added for 11.5.10
449 p_bill_to_account_id in NUMBER,
450 p_ship_to_account_id in NUMBER,
451 -- added for link object enhancement
452 p_ref_object_code in varchar2,
453 p_ref_object_id in number,
454 -- added for eam enhancement
455 p_asset_id in number,
456 p_maint_org_id in number,
457 p_owning_dept_id in number,
458 p_eam_type in varchar2,
459 --for DFF
460 p_external_attribute_1 IN varchar2,
461 p_external_attribute_2 IN varchar2,
462 p_external_attribute_3 IN varchar2,
463 p_external_attribute_4 IN varchar2,
464 p_external_attribute_5 IN varchar2,
465 p_external_attribute_6 IN varchar2,
466 p_external_attribute_7 IN varchar2,
467 p_external_attribute_8 IN varchar2,
468 p_external_attribute_9 IN varchar2,
469 p_external_attribute_10 IN varchar2,
470 p_external_attribute_11 IN varchar2,
471 p_external_attribute_12 IN varchar2,
472 p_external_attribute_13 IN varchar2,
473 p_external_attribute_14 IN varchar2,
474 p_external_attribute_15 IN varchar2,
475 p_external_context IN varchar2,
476
477 x_return_status OUT NOCOPY VARCHAR2,
478 x_msg_count OUT NOCOPY NUMBER,
479 x_msg_data OUT NOCOPY VARCHAR2,
480 x_request_id OUT NOCOPY NUMBER
481 )
482 IS
483
484 cursor cur_contact_type(p_party_id number) is
485 select party_type
486 from hz_parties
487 where party_id = p_party_id;
488
489 cursor get_ib_serial_tag_csr(p_instance_id number) is
490 select serial_number, external_reference
491 from csi_item_instances
492 where instance_id = p_instance_id;
493
494 l_call_id NUMBER;
495 x_interaction_id NUMBER;
496 l_last_update_date DATE := SYSDATE;
497 l_object_version_number NUMBER := 1;
498 l_owner_id NUMBER;
499 l_sr_rec CS_SERVICEREQUEST_PVT.service_request_rec_type;
500 l_notes_table CS_SERVICEREQUEST_PVT.notes_table;
501 l_contacts_table CS_SERVICEREQUEST_PVT.contacts_table;
502 x_workflow_process_id NUMBER;
503 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
504 l_primary_contact_party_type VARCHAR2(30);
505 l_secondary_contact_party_type VARCHAR2(30);
506 l_contact_type varchar2(30);
507 l_contact_point_type varchar2(30);
508
509 l_index BINARY_INTEGER;
510 l_note_index BINARY_INTEGER;
511 l_cs_atuo_assignment VARCHAR2(1);
512 l_ibu_assignment VARCHAR2(1);
513 l_default_coverage_temp_id NUMBER;
514
515 --added by wei ma
516 x_individual_owner VARCHAR2(100);
517 x_group_owner VARCHAR2(100);
518 x_individual_type VARCHAR2(100);
519
520 -- wei ma tempoary
521 l_responsibility_id NUMBER;
522 l_type_return_staus VARCHAR2(1);
523
524 l_retainCharNum Number := 0;
525 l_truncateCharNum Number := 0;
526 l_noteDetailNum number:= 0;
527 -- Made change for 11.5.10, some of the output parameters are saved
528 -- in the following recored.
529 l_sr_create_out_rec CS_ServiceRequest_PVT.sr_create_out_rec_type;
530
531 -- added for link object enhancement
532 l_ref_object_code varchar2(30) := p_ref_object_code;
533 l_ref_object_id number := p_ref_object_id;
534 l_select_id varchar2(200);
535 l_from_table varchar2(200);
536 l_where_clause varchar2(2000);
537 l_ref_object_count number := 0;
538 l_sr_create_link_rec cs_incidentlinks_pvt.CS_INCIDENT_LINK_REC_TYPE;
539 l_user_id number;
540 l_login_id number;
541 l_resp_appl_id number;
542 lx_return_status varchar2(3);
543 lx_msg_count number;
544 lx_msg_data varchar2(4000);
545 lx_object_version_number number;
546 lx_reciprocal_link_id number;
547 lx_link_id number;
548 l_dbg_msg varchar2(4000);
549 BEGIN
550
551 CS_SERVICEREQUEST_PVT.INITIALIZE_REC( p_sr_record => l_sr_rec);
552
553 l_sr_rec.request_date := l_last_update_date;
554 l_sr_rec.incident_occurred_date := l_last_update_date;
555 l_sr_rec.type_id := p_type_id;
556 --l_sr_rec.status_id := p_status_id;
557 --added by wei
558 get_default_status(p_type_id =>p_type_id,
559 x_status_id =>l_sr_rec.status_id,
560 x_return_status=>l_type_return_staus);
561 l_sr_rec.severity_id := p_severity_id;
562 l_sr_rec.summary := p_summary;
563 l_sr_rec.caller_type := p_caller_type;
564
565 l_sr_rec.language := p_language;
566
567 l_sr_rec.current_serial_number := p_serial_number; -- klou IKON
568
569 if p_inv_category_id = -1 then
570 l_sr_rec.category_id := null;
571 l_sr_rec.category_set_id := null;
572 else
573 l_sr_rec.category_id := p_inv_category_id;
574 l_sr_rec.category_set_id := FND_PROFILE.VALUE('CS_SR_DEFAULT_CATEGORY_SET');
575 end if;
576
577 --l_sr_rec.resource_type := 'RS_EMPLOYEE';
578
579 if (p_contract_id is not null and p_contract_id <> '-1') then
580 l_sr_rec.contract_id := p_contract_id;
581 end if;
582
583 if (p_project_num is not null) then
584 l_sr_rec.project_number := p_project_num;
585 end if;
586
587
588 /* for rollout only */
589 if (p_platform_id > 0) then
590 l_sr_rec.platform_id := p_platform_id;
591 end if;
592
593 if (p_os_version is not null) then
594 l_sr_rec.operating_system_version := p_os_version;
595 end if;
596
597 if (p_db_version is not null) then
598 l_sr_rec.db_version := p_db_version;
599 end if;
600
601 if (p_short_code is not null) then
602 l_sr_rec.request_attribute_4 := p_short_code;
603 end if;
604
605 if (p_product_revision is not null) then
606 l_sr_rec.product_revision := p_product_revision;
607 end if;
608
609
610 /*
611 if (p_attr_6 is not null) then
612 l_sr_rec.request_attribute_6 := p_attr_6;
613 end if;
614 */
615 l_sr_rec.cust_pref_lang_code := p_cust_pref_lang_code;
616
617 /* end for rollout */
618
619 if (p_pref_contact_method is not null) then
620 l_sr_rec.comm_pref_code := p_pref_contact_method;
621 end if;
622
623 if (p_urgency_id > 0) then
624 l_sr_rec.urgency_id := p_urgency_id;
625 end if;
626
627 if (p_problem_code_id <> 'NONE') then
628 l_sr_rec.problem_code := p_problem_code_id;
629 end if;
630
631 IF (p_inventory_item > 0) THEN
632 l_sr_rec.inventory_item_id := p_inventory_item;
633 l_sr_rec.inventory_org_id := CS_STD.Get_Item_Valdn_Orgzn_ID();
634 if(p_inv_item_revision <> '-1') then
635 l_sr_rec.inv_item_revision := p_inv_item_revision;
636 end if;
637 END IF;
638
639 l_sr_rec.cust_ticket_number := p_helpdesk_no;
640 l_sr_rec.verify_cp_flag := 'N';
641 l_sr_rec.sr_creation_channel := 'WEB';
642
643 if (p_customer_id > 0) then
644 l_sr_rec.customer_id := p_customer_id;
645 end if;
646
647 if (p_rollout = 'N') then
648
649 if (p_employee_id > 0) THEN
650 l_sr_rec.employee_id := p_employee_id;
651 /* Follwoing is added for handling employee logging SRs 03/07/03, klou */
652 l_sr_rec.customer_id := fnd_profile.value('IBU_EMP_SR_ORG');
653 l_sr_rec.caller_type := 'ORGANIZATION';
654 else
655 IF (p_product > 0) THEN
656 l_sr_rec.customer_product_id := p_product;
657 l_sr_rec.inventory_org_id := CS_STD.Get_Item_Valdn_Orgzn_ID();
658 if(p_cp_revision_id > 0) then
659 l_sr_rec.cp_revision_id := p_cp_revision_id;
660 end if;
661 -- klou IKON
662 -- open get_ib_serial_tag_csr(p_product);
663 -- fetch get_ib_serial_tag_csr
664 -- into l_sr_rec.current_serial_number, l_sr_rec.external_reference;
665 -- close get_ib_serial_tag_csr;
666 end if;
667
668 l_sr_rec.customer_id := p_customer_id;
669
670 if (p_account_id > 0) then
671 l_sr_rec.account_id := p_account_id;
672 end if;
673 end if; /* if employee id */
674
675 -- debugging
676 if p_contact_party_id.count <= 0 then
677 l_sr_rec.summary := l_sr_rec.summary ||' contact tables not passed ';
678 end if;
679
680 if (p_contact_party_id is not null and p_contact_party_id.count > 0) then
681 l_index := p_contact_party_id.first;
682 while (l_index is not null) loop
683 if (p_contact_party_id(l_index) > 0) then
684 if (p_contact_type(l_index) = 'CUSTOMER') then
685 open cur_contact_type(p_contact_party_id(l_index));
686 fetch cur_contact_type into l_contact_type;
687 if (cur_contact_type%notfound) then
688 close cur_contact_type;
689 raise no_data_found;
690 end if;
691 close cur_contact_type;
692
693 l_contacts_table(l_index).contact_type := l_contact_type;
694 else
695 l_contacts_table(l_index).contact_type := 'EMPLOYEE';
696 end if;
697
698 l_contacts_table(l_index).party_id := p_contact_party_id(l_index);
699 l_contacts_table(l_index).primary_flag :=p_contact_primary(l_index);
700
701 if (l_contacts_table(l_index).party_id > 0) then
702 if (p_contact_type(l_index) <> 'EMPLOYEE' or
703 p_contact_point_type(l_index) <> 'EMAIL') then
704 l_contacts_table(l_index).contact_point_id :=
705 p_contact_point_id(l_index);
706 end if;
707
708 -- get contact_point_id for employee if contact_type is PHONE, klou
709 if p_contact_type(l_index) = 'EMPLOYEE' and
710 p_contact_point_type(l_index) = 'PHONE' Then
711 l_contacts_table(l_index).contact_point_id :=
712 p_contact_point_id(l_index);
713 end if;
714
715 l_contacts_table(l_index).contact_point_type :=
716 p_contact_point_type(l_index);
717 end if;
718 end if; /* p_contact_party_id */
719
720 l_index := p_contact_party_id.next(l_index);
721 end loop;
722
723 end if; /* if p_contact_party_id */
724
725 -- end if; /* if employee id */ -- moved up by klou
726
727 else /* rollout = 'Y' */
728
729 if (p_employee_id > 0) then
730 l_sr_rec.employee_id := p_employee_id;
731 end if;
732
733 if (p_customer_id > 0) then
734 if (p_contact_party_id is not null and p_contact_party_id.count > 0) then
735 l_index := p_contact_party_id.first;
736 while (l_index is not null) loop
737 if (p_contact_party_id(l_index) > 0) then
738
739 if (p_contact_type(l_index) = 'CUSTOMER') then
740 open cur_contact_type(p_contact_party_id(l_index));
741 fetch cur_contact_type into l_contact_type;
742 if (cur_contact_type%notfound) then
743 close cur_contact_type;
744 raise no_data_found;
745 end if;
746 close cur_contact_type;
747
748 l_contacts_table(l_index).contact_type := l_contact_type;
749 else
750 l_contacts_table(l_index).contact_type := 'EMPLOYEE';
751 end if;
752
753 l_contacts_table(l_index).party_id := p_contact_party_id(l_index);
754 l_contacts_table(l_index).primary_flag := p_contact_primary(l_index);
755
756 if (l_contacts_table(l_index).party_id > 0) then
757 if (p_contact_type(l_index) <> 'EMPLOYEE' or
758 p_contact_point_type(l_index) <> 'EMAIL') then
759 l_contacts_table(l_index).contact_point_id :=
760 p_contact_point_id(l_index);
761 end if;
762
763 l_contacts_table(l_index).contact_point_type :=
764 p_contact_point_type(l_index);
765 end if;
766 end if; /* if p_contact_party_id */
767
768 l_index := p_contact_party_id.next(l_index);
769 end loop;
770
771 end if; /* if p_contact_party_id */
772
773 end if; /* p_customer_id */
774
775 end if; /* rollout */
776
777 if (p_eam_type = 'Y') then
778 l_sr_rec.inventory_org_id := CS_STD.Get_Item_Valdn_Orgzn_ID();
779 if (p_inventory_item > 0 and p_maint_org_id > 0) then l_sr_rec.maint_organization_id := p_maint_org_id; end if;
780 end if;
781
782 if (p_employee_id > 0 and p_asset_id > 0) then
783 l_sr_rec.customer_product_id := p_asset_id;
784 if (p_maint_org_id > 0) then l_sr_rec.maint_organization_id := p_maint_org_id; end if;
785 if (p_owning_dept_id > 0) then l_sr_rec.owning_dept_id := p_owning_dept_id; end if;
786 end if;
787
788 -- modified by wei
789 -- chnage the notes to be table.
790 if(p_problem_description is not null and p_problem_description.count > 0) then
791 l_note_index := p_problem_description.FIRST;
792 while l_note_index IS NOT NULL LOOP
793 if (p_problem_description(l_note_index)is NOT NULL) THEN
794 l_notes_table(l_note_index).note_type := p_note_type(l_note_index);
795 -- l_notes_table(l_note_index).note := p_problem_description(l_note_index);
796 check_string_length_bites(
797 p_string => p_problem_description(l_note_index),
798 p_targetlen => 2000,
799 x_returnLen => l_retainCharNum,
800 x_truncateCharNum => l_truncateCharNum
801 );
802
803 if(l_truncateCharNum > 0) then
804 l_notes_table(l_note_index).note := substr(p_problem_description(l_note_index), 0, l_retainCharNum);
805 else
806 l_notes_table(l_note_index).note := p_problem_description(l_note_index);
807 end if;
808 l_retainCharNum := 0;
809 l_truncateCharNum := 0;
810 l_notes_table(l_note_index).note_detail:= p_problem_detail(l_note_index);
811 l_notes_table(l_note_index).note_status:= p_note_status(l_note_index);
812 end if;
813 l_note_index := p_problem_description.NEXT(l_note_index);
814 end loop;
815 end if; /* end of the p_problem_description */
816
817 -- Fix to bug 2200212
818 -- Don't set Org ID
819 -- fnd_client_info.set_org_context(CS_STD.Get_Item_Valdn_Orgzn_ID());
820
821
822 /* Removed the logic to getting resource from Assignment Manager in order
823 * to fully leverage TeleService Auto-Assignment feature in 11.5.7.1
824 * 11-APR-2002 KLOU, (ASSG)
825 */
826
827 Select nvl(fnd_profile.value('CS_AUTO_ASSIGN_OWNER_HTML'), 'N')
828 Into l_cs_atuo_assignment From dual;
829
830 If l_cs_atuo_assignment = 'N' Then
831 Select nvl(fnd_profile.value('IBU_R_ASSIGNMENT_USED_OWNERS'), 'Y')
832 Into l_ibu_assignment From dual;
833
834 If l_ibu_assignment = 'Y' Then
835
836 -- Start of Fix to Bug 6621657
837 -- Profile CS_SR_DEFAULT_GROUP_TYPE is obsoleted in 12.0.2 and 12.0.3.
838
839 /* Select fnd_profile.value('CS_SR_DEFAULT_GROUP_TYPE')
840 * Into l_sr_rec.group_type From dual;
841 */
842
843 l_sr_rec.group_type := 'RS_GROUP';
844
845 --End of Fix to Bug 6621657
846
847 Select fnd_profile.value('CS_SR_DEFAULT_GROUP_OWNER')
848 Into l_sr_rec.owner_group_id From dual;
849
850 Select fnd_profile.value('INC_DEFAULT_INCIDENT_OWNER')
851 Into l_sr_rec.owner_id From dual;
852
853 Select fnd_profile.value('CS_SR_DEFAULT_OWNER_TYPE')
854 into l_sr_rec.resource_type from dual;
855
856 End If;
857 End If;
858 -- Done auto assignment
859
860
861 l_sr_rec.error_code := p_error_code;
862 l_sr_rec.last_update_channel := 'WEB';
863
864 l_sr_rec.creation_program_code := 'ISUPPORTSRUI';
865 l_sr_rec.last_update_program_code := 'ISUPPORTSRUI';
866
867 if (p_time_zone_id <> -1) then
868 l_sr_rec.time_zone_id := p_time_zone_id;
869 end if;
870
871 --Next is the section for the service request location address
872 --If p_location_id is -1, we don't need to pass it to the lower level CS
873 --package
874
875 if(p_location_id <> -1) then
876 l_sr_rec.incident_location_id := p_location_id;
877 l_sr_rec.incident_location_type := 'HZ_PARTY_SITE';
878 end if;
879
880 if(p_address is not null) then
881 l_sr_rec.incident_address := p_address;
882 end if;
883
884 if(p_city is not null) then
885 l_sr_rec.incident_city := p_city;
886 end if;
887
888 if(p_state is not null) then
889 l_sr_rec.incident_state := p_state;
890 end if;
891
892 if(p_country is not null) then
893 l_sr_rec.incident_country := p_country;
894 end if;
895
896 if(p_province is not null) then
897 l_sr_rec.incident_province := p_province;
898 end if;
899
900 if(p_postal_code is not null) then
901 l_sr_rec.incident_postal_code := p_postal_code;
902 end if;
903
904 if(p_county is not null) then
905 l_sr_rec.incident_county := p_county;
906 end if;
907
908 if(p_addrLine2 is not null) then
909 l_sr_rec.incident_address2 := p_addrLine2;
910 end if;
911
912 if(p_addrLine3 is not null) then
913 l_sr_rec.incident_address3 := p_addrLine3;
914 end if;
915
916 if(p_addrLine4 is not null) then
917 l_sr_rec.incident_address4 := p_addrLine4;
918 end if;
919
920 if(p_poboxNumber is not null) then
921 l_sr_rec.incident_po_box_number := p_poboxNumber;
922 end if;
923
924 if(p_houseNumber is not null) then
925 l_sr_rec.incident_house_number := p_houseNumber;
926 end if;
927
928 if(p_streetSuffix is not null) then
929 l_sr_rec.incident_street_suffix := p_streetSuffix;
930 end if;
931
932 if(p_street is not null) then
933 l_sr_rec.incident_street := p_street;
934 end if;
935
936 if(p_street_number is not null) then
937 l_sr_rec.incident_street_number := p_street_number;
938 end if;
939
940 if(p_floor is not null) then
941 l_sr_rec.incident_floor := p_floor;
942 end if;
943
944 if(p_suite is not null) then
945 l_sr_rec.incident_suite := p_suite;
946 end if;
947
948 if(p_postalPlus4Code is not null) then
949 l_sr_rec.incident_postal_plus4_code := p_postalPlus4Code;
950 end if;
951
952 if(p_position is not null) then
953 l_sr_rec.incident_position := p_position;
954 end if;
955
956 if(p_locationDirections is not null) then
957 l_sr_rec.incident_location_directions := p_locationDirections;
958 end if;
959
960 if(p_description is not null) then
961 l_sr_rec.incident_location_description := p_description;
962 end if;
963
964 if(p_pointOfInterest is not null) then
965 l_sr_rec.incident_point_of_interest := p_pointOfInterest;
966 end if;
967
968 if(p_crossStreet is not null) then
969 l_sr_rec.incident_cross_street := p_crossStreet;
970 end if;
971
972 if(p_directionQualifier is not null) then
973 l_sr_rec.incident_direction_qualifier := p_directionQualifier;
974 end if;
975
976 if(p_distanceQualifier is not null) then
977 l_sr_rec.incident_distance_qualifier := p_distanceQualifier;
978 end if;
979
980 if(p_distanceQualUom is not null) then
981 l_sr_rec.incident_distance_qual_uom := p_distanceQualUom;
982 end if;
983
984
985 -- the following is for the bill to and ship to
986 if(p_bill_to_site_id <> -1) then
987 l_sr_rec.bill_to_site_id := p_bill_to_site_id ;
988 end if;
989
990 if(p_bill_to_contact_id <> -1) then
991 l_sr_rec.bill_to_contact_id := p_bill_to_contact_id;
992 end if;
993
994 if(p_ship_to_site_id <> -1) then
995 l_sr_rec.ship_to_site_id := p_ship_to_site_id;
996 end if;
997
998 if(p_ship_to_contact_id <> -1) then
999 l_sr_rec.ship_to_contact_id := p_ship_to_contact_id;
1000 end if;
1001
1002 if(p_install_site_use_id <> -1) then
1003 l_sr_rec.install_site_use_id := p_install_site_use_id;
1004 end if;
1005
1006 if(p_bill_to_party_id <> -1) then
1007 l_sr_rec.bill_to_party_id := p_bill_to_party_id;
1008 end if;
1009
1010 if(p_ship_to_party_id <> -1) then
1011 l_sr_rec.ship_to_party_id := p_ship_to_party_id;
1012 end if;
1013
1014 if(p_ship_to_account_id <> -1) then
1015 l_sr_rec.ship_to_account_id := p_ship_to_account_id;
1016 end if;
1017
1018 if(p_bill_to_account_id <> -1) then
1019 l_sr_rec.bill_to_account_id := p_bill_to_account_id;
1020 end if;
1021
1022 if(p_external_attribute_1 is not null) then
1023 l_sr_rec.external_attribute_1 := p_external_attribute_1;
1024 end if ;
1025
1026 if(p_external_attribute_2 is not null) then
1027 l_sr_rec.external_attribute_2 := p_external_attribute_2;
1028 end if;
1029
1030 if(p_external_attribute_3 is not null) then
1031 l_sr_rec.external_attribute_3 := p_external_attribute_3;
1032 end if;
1033
1034 if(p_external_attribute_4 is not null) then
1035 l_sr_rec.external_attribute_4 := p_external_attribute_4;
1036 end if;
1037
1038 if(p_external_attribute_5 is not null) then
1039 l_sr_rec.external_attribute_5 := p_external_attribute_5;
1040 end if;
1041
1042 if(p_external_attribute_6 is not null) then
1043 l_sr_rec.external_attribute_6 := p_external_attribute_6;
1044 end if;
1045
1046 if(p_external_attribute_7 is not null) then
1047 l_sr_rec.external_attribute_7 := p_external_attribute_7;
1048 end if;
1049
1050 if(p_external_attribute_8 is not null) then
1051 l_sr_rec.external_attribute_8 := p_external_attribute_8;
1052 end if;
1053
1054 if(p_external_attribute_9 is not null) then
1055 l_sr_rec.external_attribute_9 := p_external_attribute_9;
1056 end if;
1057
1058 if(p_external_attribute_10 is not null) then
1059 l_sr_rec.external_attribute_10 := p_external_attribute_10;
1060 end if;
1061
1062 if(p_external_attribute_11 is not null) then
1063 l_sr_rec.external_attribute_11 := p_external_attribute_11;
1064 end if;
1065
1066 if(p_external_attribute_12 is not null) then
1067 l_sr_rec.external_attribute_12 := p_external_attribute_12;
1068 end if;
1069
1070 if(p_external_attribute_13 is not null) then
1071 l_sr_rec.external_attribute_13 := p_external_attribute_13;
1072 end if;
1073
1074 if(p_external_attribute_14 is not null) then
1075 l_sr_rec.external_attribute_14 := p_external_attribute_14;
1076 end if;
1077
1078 if(p_external_attribute_15 is not null) then
1079 l_sr_rec.external_attribute_15 := p_external_attribute_15;
1080 end if;
1081
1082 if(p_external_context is not null) then
1083 l_sr_rec.external_context := p_external_context;
1084 end if;
1085
1086 --wei ma added
1087 select fnd_global.resp_id, fnd_global.resp_appl_id, fnd_global.user_id, fnd_global.login_id
1088 into l_responsibility_id, l_resp_appl_id, l_user_id, l_login_id
1089 from dual;
1090 -- added for 11.5.10
1091 l_default_coverage_temp_id := fnd_profile.value('CS_SR_DEFAULT_COVERAGE');
1092
1093 CS_ServiceRequest_PVT.Create_ServiceRequest(
1094 p_api_version => 4.0,
1095 p_init_msg_list => fnd_api.g_true,
1096 p_commit => fnd_api.g_true,
1097 p_resp_id => l_responsibility_id,
1098 p_user_id => p_user_id,
1099 p_service_request_rec => l_sr_rec,
1100 p_notes => l_notes_table,
1101 p_contacts => l_contacts_table,
1102 p_auto_assign => l_cs_atuo_assignment,
1103 p_validation_level => l_validation_level,
1104 p_auto_generate_tasks => 'N',
1105 p_default_contract_sla_ind => 'Y',
1106 p_default_coverage_template_id => l_default_coverage_temp_id,
1107 x_msg_count => x_msg_count,
1108 x_return_status => x_return_status,
1109 x_msg_data => x_msg_data,
1110 x_sr_create_out_rec => l_sr_create_out_rec
1111 );
1112
1113 x_request_id := l_sr_create_out_rec.request_id;
1114 p_request_number := l_sr_create_out_rec.request_number;
1115
1116 if (x_return_status <> 'S') then
1117 decodeErrorMsg();
1118 end if;
1119
1120 -- added for link type enhancement
1121 if (x_return_status = 'S' and (l_ref_object_code is null or length(l_ref_object_code) = 0) and l_ref_object_id <> -1) then
1122 if ((FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
1123 l_dbg_msg := fnd_message.get_string('IBU', 'IBU_SR_REF_OBJ_CODE_NOT_PASSED');
1124 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'ibu.plsql.IBU_REQ_PKG.create_service_request', l_dbg_msg);
1125 end if;
1126 end if;
1127
1128 if (x_return_status = 'S' and l_ref_object_id = -1 and l_ref_object_code is not null and length(l_ref_object_code) > 0) then
1129 if ((FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
1130 l_dbg_msg := fnd_message.get_string('IBU', 'IBU_SR_REF_OBJ_ID_NOT_PASSED');
1131 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'ibu.plsql.IBU_REQ_PKG.create_service_request', l_dbg_msg);
1132 end if;
1133 end if;
1134
1135 if (x_return_status = 'S' and l_ref_object_code is not null and length(l_ref_object_code) > 0 and l_ref_object_id <> -1) then
1136 getObjectInfo(p_ref_object_code => l_ref_object_code,
1137 x_select_id => l_select_id,
1138 x_from_table => l_from_table,
1139 x_where_clause => l_where_clause,
1140 x_object_count => l_ref_object_count);
1141
1142 if (l_ref_object_count = 0) then
1143 if ((FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
1144 l_dbg_msg := fnd_message.get_string('IBU','IBU_SR_REF_OBJ_CODE_INVALID');
1145 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'ibu.plsql.IBU_REQ_PKG.create_service_request', l_dbg_msg);
1146 end if;
1147 else
1148 l_ref_object_count := 0;
1149
1150 checkObjectID(p_ref_object_id => l_ref_object_id,
1151 p_select_id => l_select_id,
1152 p_from_table => l_from_table,
1153 p_where_clause => l_where_clause,
1154 x_object_count => l_ref_object_count);
1155
1156 if (l_ref_object_count = 0) then
1157 if ((FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
1158 l_dbg_msg := fnd_message.get_string('IBU', 'IBU_SR_REF_OBJ_ID_INVALID');
1159 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'ibu.plsql.IBU_REQ_PKG.create_service_request', l_dbg_msg);
1160 end if;
1161 else
1162 l_sr_create_link_rec.SUBJECT_ID := x_request_id;
1163 l_sr_create_link_rec.SUBJECT_TYPE := 'SR';
1164 l_sr_create_link_rec.OBJECT_ID := l_ref_object_id;
1165 l_sr_create_link_rec.OBJECT_TYPE := l_ref_object_code;
1166 l_sr_create_link_rec.LINK_TYPE_ID := 6;
1167 l_sr_create_link_rec.LINK_TYPE := 'REF';
1168
1169 cs_incidentlinks_pvt.CREATE_INCIDENTLINK(P_API_VERSION => 2.0,
1170 P_INIT_MSG_LIST => fnd_api.g_true,
1171 P_COMMIT => fnd_api.g_true,
1172 P_VALIDATION_LEVEL => l_validation_level,
1173 P_USER_ID => l_user_id,
1174 P_LOGIN_ID => null,
1175 P_LINK_REC => l_sr_create_link_rec,
1176 X_RETURN_STATUS => lx_return_status,
1177 X_MSG_COUNT => lx_msg_count,
1178 X_MSG_DATA => lx_msg_data,
1179 X_OBJECT_VERSION_NUMBER => lx_object_version_number,
1180 X_RECIPROCAL_LINK_ID => lx_reciprocal_link_id,
1181 X_LINK_ID => lx_link_id
1182 );
1183
1184 if (lx_return_status <> 'S') then
1185 if ((FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
1186 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'ibu.plsql.IBU_REQ_PKG.create_service_request', lx_msg_data);
1187 end if;
1188 end if;
1189
1190 end if;
1191 end if;
1192 end if;
1193
1194 end create_service_request;
1195
1196
1197 /**
1198 * Send email notification to user
1199 */
1200 procedure send_email(
1201 email_address_in in varchar2,
1202 user_id in varchar2,
1203 subject in varchar2,
1204 msg_body in varchar2,
1205 srID in number,
1206 emailStyleSheet in varchar2,
1207 emailbranding in varchar2,
1208 emaillinkURL in varchar2,
1209 notification_pref in varchar2,
1210 contactType in varchar2,
1211 contactID in number
1212 )
1213 as
1214 user_name varchar2(100) := null;
1215 user_display_name varchar2(100) := null;
1216 language varchar2(100) := 'AMERICAN';
1217 territory varchar2(100) := 'America';
1218 description varchar2(100) := NULL;
1219 --notification_preference varchar2(100) := 'MAILTEXT';
1220 notification_preference varchar2(100) := 'MAILHTML';
1221 email_address varchar2(100) := NULL;
1222 fax varchar2(100) :=NULL;
1223 status varchar2(100) := 'ACTIVE';
1224 expiration_date varchar2(100) := NULL;
1225 role_name varchar2(100) := NULL;
1226 role_display_name varchar2(100) := NULL;
1227 role_description varchar2(100) := NULL;
1228 wf_id Number;
1229 msg_type varchar2(100) := 'IBU_SUBS';
1230 msg_name varchar2(100) := 'IBU_MESG';
1231
1232 due_date date := NULL;
1233 callback varchar2(100) := NULL;
1234 context varchar2(100) := NULL;
1235 send_comment varchar2(100) := NULL;
1236 priority number := null;
1237
1238 email_content Wf_Engine.TextTabTyp;
1239 email_content_count_end number := 1;
1240 temp_email_msg_body varchar2(32000) := null;
1241 truncateCharNum number := 0; -- added by wei ma
1242 retainCharNum number := 0;
1243 i number := 1;
1244 finaltotalCharNum number := 0;
1245 originatotalCharNum number := 0;
1246 temp_email_content_holder varchar2(16000) := null;
1247
1248 -- define cursors for role defintion
1249 temp_role_name varchar2(100) := null;
1250 temp_pref_name varchar2(100) := null;
1251 constructNewRoleName boolean := true;
1252 cursor cur_sr_email_cus_role(p_contact_id number, p_email_address varchar2) is
1253 select name from wf_roles
1254 where orig_system = 'HZ_PARTY'
1255 and orig_system_id = p_contact_id
1256 and email_address = p_email_address;
1257
1258 cursor cur_sr_email_emp_role(p_contact_id number, p_email_address varchar2) is
1259 select notification_preference, name from wf_roles
1260 where orig_system = 'PER'
1261 and orig_system_id = p_contact_id
1262 and email_address = p_email_address;
1263
1264 duplicate_user_or_role exception;
1265 PRAGMA EXCEPTION_INIT (duplicate_user_or_role, -20002);
1266 begin
1267
1268 role_name := 'IBUSR1_'||email_address_in;
1269 role_display_name := user_id; --actual user fullName
1270 email_address := email_address_in;
1271
1272 temp_email_msg_body := substr(msg_body, 0, 29000);
1273 originatotalCharNum := length(temp_email_msg_body);
1274
1275 -- from the here is the section to construct the role
1276 if('CUS' = contactType) then
1277 open cur_sr_email_cus_role(contactID, email_address_in);
1278 fetch cur_sr_email_cus_role into temp_role_name;
1279 close cur_sr_email_cus_role;
1280 if(temp_role_name is not null) then
1281 constructNewRoleName := false;
1282 role_name := temp_role_name;
1283 else
1284 constructNewRoleName := true;
1285 end if;
1286 end if;
1287
1288 if('EMP' = contactType) then
1289 open cur_sr_email_emp_role(contactID, email_address_in);
1290 fetch cur_sr_email_emp_role into temp_pref_name, temp_role_name;
1291 close cur_sr_email_emp_role;
1292
1293 if(temp_pref_name is not null and ('MAILHTML'= temp_pref_name or 'MAILHTM2' = temp_pref_name or
1294 'MAILTEXT' = temp_pref_name or 'MAILATTH' = temp_pref_name)) then
1295 role_name := temp_role_name;
1296 constructNewRoleName := false;
1297 else
1298 constructNewRoleName := true;
1299 end if;
1300 end if;
1301
1302 -- end of the section to construct the role
1303
1304 while (i < 16 ) loop
1305 if(finaltotalCharNum < originatotalCharNum) then
1306 temp_email_content_holder := substr(temp_email_msg_body, 0, 1950);
1307 check_string_length_bites(
1308 p_string => temp_email_content_holder, --email_content(i),
1309 p_targetlen => 1950,
1310 x_returnLen => retainCharNum,
1311 x_truncateCharNum => truncateCharNum);
1312 email_content(i) := substr(temp_email_content_holder, 0, retainCharNum);
1313 temp_email_msg_body := substr(temp_email_msg_body, 1951-truncateCharNum);
1314 finaltotalCharNum := finaltotalCharNum + retainCharNum;
1315
1316 else
1317 email_content(i) := '';
1318 end if;
1319 i := i+1;
1320
1321 end loop;
1322
1323 if(constructNewRoleName) then
1324 notification_preference := notification_pref;
1325 begin
1326 WF_Directory.CreateAdHocUser(role_name, role_display_name, language,
1327 territory, role_description, notification_preference,
1328 email_address, fax, status, expiration_date);
1329 exception
1330 when duplicate_user_or_role then
1331 WF_Directory.SetAdHocUserAttr (role_name, role_display_name,
1332 notification_preference, language, territory, email_address, fax);
1333 end;
1334 end if;
1335
1336 -- next is to use the new startProcess procedure
1337 StartEmailProcess(role_name, srID, subject, email_content,
1338 role_name, 'IBU_SENDMAIL', 'IBUSRDTL', emailStyleSheet, emailBranding, emaillinkURL);
1339
1340 /* wf_id := WF_Notification.send(role_name, 'IBUSRDTL', 'IBU_MESG',
1341 due_date, callback, context, send_comment, priority);
1342 WF_Notification.SetAttrText(wf_id, 'IBU_SUBJECT', subject);
1343 -- WF_Notification.SetAttrText(wf_id, 'IBU_CONTENT', msg_body);
1344
1345 for i in 1..8 loop
1346 if(i = 1) then
1347 WF_Notification.SetAttrText(wf_id, 'IBU_CONTENT', email_content(i));
1348 else
1349 WF_Notification.SetAttrText(wf_id, 'IBUCONTENT'||i, email_content(i));
1350 end if;
1351 end loop; */
1352
1353 end send_email;
1354
1355 procedure get_default_status(
1356 p_type_id in number,
1357 x_status_id out nocopy number,
1358 x_return_status out NOCOPY VARCHAR2
1359 )as
1360 l_responsibility_id number;
1361 l_status_id number;
1362 l_group_id number;
1363 no_status_defined Exception;
1364
1365 cursor cur_sr_def_status_group_a(p_type_id number,p_resp_id number) is
1366 SELECT
1367 TypeMapping.status_group_id
1368 FROM
1369 cs_sr_type_mapping TypeMapping
1370 WHERE
1371 incident_type_id = p_type_id AND
1372 responsibility_id = p_resp_id AND
1373 TRUNC(SYSDATE) BETWEEN TRUNC(NVL(TypeMapping.start_date, SYSDATE)) AND
1374 TRUNC(NVL(TypeMapping.end_date, SYSDATE));
1375
1376 cursor cur_sr_def_status_group_b(p_type_id number) is
1377 SELECT status_group_id
1378 FROM cs_incident_types_b
1379 WHERE incident_type_id = p_type_id AND
1380 TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE)) AND
1381 TRUNC(NVL(end_date_active, SYSDATE));
1382
1383 cursor cur_sr_group_def_staus_id(p_status_group_id number) is
1384 select default_incident_status_id
1385 from cs_sr_status_groups_b
1386 where status_group_id = p_status_group_id
1387 and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date, SYSDATE)) AND
1388 TRUNC(NVL(end_date, SYSDATE));
1389
1390 cursor cur_sr_def_status_from_group(p_status_group_id number) is
1391 select csIncidentStatus.incident_status_id
1392 from CS_SR_ALLOWED_STATUSES allowedStatus,
1393 CS_INCIDENT_STATUSES_B csIncidentStatus
1394 where
1395 allowedStatus.status_group_id = p_status_group_id and
1396 allowedStatus.incident_status_id =
1397 csIncidentStatus.incident_status_id and
1398 csIncidentStatus.valid_in_create_flag = 'Y' and
1399 csIncidentStatus.incident_subtype = 'INC' and
1400 (csIncidentStatus.pending_approval_flag is null or
1401 csIncidentStatus.pending_approval_flag = 'N') and
1402 TRUNC(SYSDATE) BETWEEN TRUNC(NVL(allowedStatus.start_date, SYSDATE))
1403 AND
1404 TRUNC(NVL(allowedStatus.end_date, SYSDATE)) AND
1405 TRUNC(SYSDATE) BETWEEN
1406 TRUNC(NVL(csIncidentStatus.start_date_active, SYSDATE)) AND
1407 TRUNC(NVL(csIncidentStatus.end_date_active, SYSDATE)) and
1408 rownum <=1 ;
1409
1410 begin
1411 select fnd_global.resp_id into l_responsibility_id from dual;
1412
1413 open cur_sr_def_status_group_a(p_type_id, l_responsibility_id);
1414 fetch cur_sr_def_status_group_a into l_group_id;
1415 close cur_sr_def_status_group_a;
1416
1417 if(l_group_id is not null and l_group_id > 0) then
1418 open cur_sr_group_def_staus_id(l_group_id);
1419 fetch cur_sr_group_def_staus_id into l_status_id;
1420 close cur_sr_group_def_staus_id;
1421
1422 if(l_status_id is null or l_status_id <=0) then
1423 open cur_sr_def_status_from_group(l_group_id);
1424 fetch cur_sr_def_status_from_group into l_status_id;
1425 close cur_sr_def_status_from_group;
1426 end if;
1427 end if;
1428
1429 if(l_group_id is null or l_group_id <= 0) then
1430 open cur_sr_def_status_group_b(p_type_id);
1431 fetch cur_sr_def_status_group_b into l_group_id;
1432 close cur_sr_def_status_group_b;
1433
1434 if(l_group_id is not null and l_group_id > 0) then
1435 open cur_sr_group_def_staus_id(l_group_id);
1436 fetch cur_sr_group_def_staus_id into l_status_id;
1437 close cur_sr_group_def_staus_id;
1438 end if;
1439 if(l_status_id is null or l_status_id <=0) then
1440 open cur_sr_def_status_from_group(l_group_id);
1441 fetch cur_sr_def_status_from_group into l_status_id;
1442 close cur_sr_def_status_from_group;
1443 end if;
1444
1445 end if;
1446
1447 if(l_group_id is null or l_group_id <= 0) then
1448 l_status_id := fnd_profile.value('INC_DEFAULT_INCIDENT_STATUS');
1449 end if;
1450 x_status_id := l_status_id;
1451
1452 end get_default_status;
1453
1454 /**
1455 * Decode the error messages:
1456 * CS_SR_CANNOT_CLOSE_SR
1457 * CS_SR_OPEN_TASKS_EXISTS
1458 * CS_SR_OPEN_CHARGES_EXISTS
1459 * CS_SR_SCHEDULED_TASKS_EXISTS
1460 * CS_SR_TASK_DEBRIEF_INCOMPLETE
1461 * TO
1462 * "This service request cannot be closed at this time.
1463 * Please call customer support for assistance."
1464 */
1465
1466 procedure decodeErrorMsg
1467 as
1468 l_count number;
1469 l_data varchar2(2000);
1470 tempMsg varchar2(2000);
1471 l_msg_index number := 1;
1472 l_msg_index_out number;
1473 l_app_code varchar2(10);
1474 l_msg_name varchar2(100);
1475 begin
1476 l_count := FND_MSG_PUB.Count_Msg;
1477
1478 while l_msg_index <= l_count loop
1479 FND_MSG_PUB.Get(p_msg_index => l_msg_index,
1480 p_data => l_data,
1481 p_msg_index_out => l_msg_index_out
1482 );
1483
1484 fnd_message.parse_encoded (
1485 l_data,
1486 l_app_code,
1487 l_msg_name
1488 );
1489
1490 if (l_msg_name = 'CS_SR_CANNOT_CLOSE_SR' or
1491 l_msg_name = 'CS_SR_OPEN_TASKS_EXISTS' or
1492 l_msg_name = 'CS_SR_OPEN_CHARGES_EXISTS' or
1493 l_msg_name = 'CS_SR_SCHEDULED_TASKS_EXISTS' or
1494 l_msg_name = 'CS_SR_TASK_DEBRIEF_INCOMPLETE') then
1495
1496 FND_MSG_PUB.Delete_Msg(l_msg_index_out);
1497 FND_MESSAGE.SET_NAME('IBU', 'IBU_SR_CANNOT_CLOSE_SR');
1498 FND_MSG_PUB.Add;
1499 l_count := l_count - 1;
1500 elsif (l_msg_name = 'FORM_RECORD_CHANGED') then
1501 FND_MSG_PUB.Delete_Msg(l_msg_index_out);
1502 FND_MESSAGE.SET_NAME('IBU', 'IBU_SR_CANNOT_UPDATE_SR');
1503 FND_MSG_PUB.Add;
1504 l_count := l_count - 1;
1505 else
1506 l_msg_index := l_msg_index + 1;
1507 end if;
1508 end loop;
1509 end decodeErrorMsg;
1510
1511 /**
1512 * This procedure is to start the Email work flow.
1513 *
1514 */
1515
1516 procedure StartEmailProcess (
1517 roleName in varchar2,
1518 srID in number,
1519 subject in varchar2,
1520 content Wf_Engine.TextTabTyp,
1521 ProcessOwner in varchar2,
1522 Workflowprocess in varchar2 ,
1523 item_type in varchar2,
1524 emailStyleSheet in varchar2,
1525 emailbranding in varchar2,
1526 emaillinkURL in varchar2) is
1527
1528 ItemType varchar2(30) := nvl(item_type, 'IBUSRDTL');
1529 ItemKey varchar2(200) := 'NOTIF_' || roleName;
1530 ItemUserKey varchar2(200) := roleName;
1531
1532 cnt number := 0;
1533 i number := 0;
1534 l_user varchar2(50);
1535 seq number := 0;
1536 --create_seq varchar2(50) := 'create sequence IBU_SR_NOTIFICATION_S';
1537 --get_seq varchar2(50) := 'select ' || 'IBU_SR_NOTIFICATION_S' || '.nextval from dual';
1538 get_seq varchar2(50) := 'select ' || 'IBU_WF_ITEM_KEY_S' || '.nextval from dual';
1539 mailAttrVals Wf_Engine.TextTabTyp ;
1540 begin
1541 /* Get schema name */
1542 select user into l_user from dual;
1543
1544 /* Get sequence for item key to be unique */
1545 /* select count(*) into cnt from all_objects
1546 where object_name like 'IBU_SR_NOTIFICATION_S'
1547 and object_type = 'SEQUENCE'
1548 and owner = l_user; */
1549
1550 /* if cnt = 0 then
1551 execute immediate create_seq;
1552 else
1553 execute immediate get_seq into seq;
1554 end if; */
1555 execute immediate get_seq into seq;
1556
1557 ItemKey := roleName||seq;
1558
1559 wf_engine.CreateProcess(itemtype => ItemType,
1560 itemkey => ItemKey,
1561 process => WorkflowProcess );
1562
1563 wf_engine.SetItemUserKey(itemtype => Itemtype,
1564 itemkey => Itemkey,
1565 userkey => ItemUserKey);
1566
1567 wf_engine.SetItemAttrText(itemtype => Itemtype,
1568 itemkey => Itemkey,
1569 aname => 'IBU_ROLE',
1570 avalue => roleName);
1571
1572 wf_engine.SetItemAttrText (itemtype => Itemtype,
1573 itemkey => Itemkey,
1574 aname => 'IBU_SUBJECT_ITEM',
1575 avalue => subject);
1576
1577 wf_engine.SetItemAttrNumber(itemtype => Itemtype,
1578 itemkey => Itemkey,
1579 aname => 'IBUSRID',
1580 avalue => srID);
1581
1582 wf_engine.SetItemAttrText(itemtype => Itemtype,
1583 itemkey => Itemkey,
1584 aname => 'IBUSTYLESHEET',
1585 avalue => 'plsql:IBU_SUBS_DOC_PKG.set_msg_body_token/'|| emailStyleSheet);
1586
1587 wf_engine.SetItemAttrText(itemtype => Itemtype,
1588 itemkey => Itemkey,
1589 aname => 'IBUBRANDING',
1590 avalue => 'plsql:IBU_SUBS_DOC_PKG.set_msg_body_token/'|| emailbranding);
1591
1592 wf_engine.SetItemAttrText(itemtype => Itemtype,
1593 itemkey => Itemkey,
1594 aname => 'IBUURL',
1595 avalue => 'plsql:IBU_SUBS_DOC_PKG.set_msg_body_token/'|| emaillinkurl);
1596
1597 wf_engine.SetItemOwner(itemtype => Itemtype,
1598 itemkey => Itemkey,
1599 owner => roleName);
1600
1601 for i in 1..15 loop
1602 mailAttrVals(i) := 'plsql:IBU_SUBS_DOC_PKG.set_msg_body_token/' || content(i);
1603 if(i = 1) then
1604 wf_engine.SetItemAttrText(itemtype => Itemtype,
1605 itemkey => Itemkey,
1606 aname => 'IBU_ITEM_CONTENT',
1607 avalue => mailAttrVals(i));
1608 else
1609 wf_engine.SetItemAttrText(itemtype => Itemtype,
1610 itemkey => Itemkey,
1611 aname => 'IBUCONTENT'||(i-1),
1612 avalue => mailAttrVals(i));
1613 end if;
1614 end loop;
1615
1616 wf_engine.StartProcess (itemtype => Itemtype,
1617 itemkey => Itemkey );
1618
1619 end StartEmailProcess;
1620
1621 procedure check_string_length_bites(
1622 p_string in varchar2,
1623 p_targetlen number,
1624 x_returnLen out NOCOPY number,
1625 x_truncateCharNum out NOCOPY number
1626 ) is
1627 lowBound number := 0;
1628 highBound number := length(p_string);
1629 orginalhighBound number := highBound;
1630 middleBound number := floor((lowBound + highBound)/2);
1631 inputStringBites number := lengthb(p_string);
1632 bitesCharDiff number := inputStringBites - highBound;
1633 tempString varchar2(32000) := '';
1634 tempCounter number := 0;
1635 begin
1636 if(bitesCharDiff = 0 or inputStringBites <= p_targetlen) then
1637 x_returnLen := highBound;
1638 else -- not eaual case:
1639 if(bitesCharDiff > 0 and bitesCharDiff <= 256) then
1640 x_returnLen := highBound - bitesCharDiff;
1641 else
1642 -- we need to use binary search to locate the position
1643 while(lowBound < highBound) loop
1644 tempCounter := tempCounter +1;
1645
1646 middleBound := floor((lowBound + highBound)/2);
1647 tempString := substr(p_string, 0, middleBound);
1648 inputStringBites := lengthb(tempString);
1649
1650 bitesCharDiff := p_targetlen - inputStringBites ;
1651
1652 if(bitesCharDiff = 0) then
1653 x_returnLen := middleBound;
1654 exit;
1655 else
1656 if(bitesCharDiff < 0) then
1657 highBound := middleBound;
1658 else
1659 if(bitesCharDiff > 256) then
1660 lowBound := middleBound;
1661 else
1662 x_returnLen := middleBound;
1663 exit;
1664 end if;
1665 end if;
1666 end if;
1667 end loop;
1668 end if;
1669 end if;
1670
1671 x_truncateCharNum := orginalhighBound - x_returnLen;
1672 end check_string_length_bites;
1673
1674 /**
1675 * get the object info from jtf_object
1676 */
1677
1678 procedure getObjectInfo(
1679 p_ref_object_code in varchar2,
1680 x_select_id out NOCOPY varchar2,
1681 x_from_table out NOCOPY varchar2,
1682 x_where_clause out NOCOPY varchar2,
1683 x_object_count out NOCOPY number
1684 ) as
1685
1686 l_ref_object_code varchar2(30) := p_ref_object_code;
1687 l_ref_object_value varchar2(30) := null;
1688 begin
1689
1690 select object_code, select_id, from_table, where_clause
1691 into l_ref_object_value, x_select_id, x_from_table, x_where_clause
1692 from jtf_objects_b
1693 where sysdate between nvl(start_date_active, sysdate) and nvl(end_date_active, sysdate)
1694 and object_code = l_ref_object_code;
1695
1696 if l_ref_object_value is null then x_object_count :=0; else x_object_count :=1; end if;
1697
1698 EXCEPTION
1699 WHEN OTHERS THEN
1700 x_object_count := 0;
1701
1702 end getObjectInfo;
1703
1704 procedure checkObjectID(
1705 p_ref_object_id in number,
1706 p_select_id in varchar2,
1707 p_from_table in varchar2,
1708 p_where_clause in varchar2,
1709 x_object_count out NOCOPY number
1710 ) as
1711 l_ref_object_id number := p_ref_object_id;
1712 l_select_statement varchar2(3500);
1713 begin
1714
1715 if (p_where_clause is not null and length(p_where_clause) > 0) then
1716 l_select_statement := 'select count(*) from ' || p_from_table || ' where ' || p_where_clause || ' and ' || p_select_id || ' = :p1 and rownum < 2 ';
1717 else
1718 l_select_statement := 'select count(*) from ' || p_from_table || ' where ' || p_select_id || ' = ' || l_ref_object_id;
1719 end if;
1720
1721 execute immediate l_select_statement into x_object_count;
1722
1723 EXCEPTION
1724 WHEN OTHERS THEN
1725 x_object_count := 0;
1726
1727 end checkObjectID;
1728
1729 procedure validate_http_service_ticket(
1730 p_ticket_string in varchar2,
1731 x_return_status out NOCOPY VARCHAR2
1732 )is
1733
1734 ticketValid boolean := false;
1735 X2 raw(32);
1736 SVC varchar2(30) := 'CS_IBU_EMAIL';
1737 ticketNumber varchar2(400) := '';
1738 begin
1739 X2 := FND_HTTP_TICKET.SET_SERVICE_TICKET(SVC);
1740 -- Probably don't need this call to the "get" interface
1741 ticketNumber := FND_HTTP_TICKET.GET_SERVICE_TICKET_STRING(SVC);
1742 ticketValid := FND_HTTP_TICKET.COMPARE_SERVICE_TICKET_STRINGS(ticketNumber, p_ticket_string);
1743 if(ticketValid) then
1744 x_return_status := 'T';
1745 else
1746 x_return_status := 'F';
1747 end if;
1748
1749 Exception
1750 when others then
1751 x_return_status := 'F';
1752
1753 end validate_http_service_ticket;
1754
1755
1756 END IBU_REQ_PKG;