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