[Home] [Help]
PACKAGE BODY: APPS.CS_SR_FUL_PKG
Source
1 PACKAGE BODY CS_SR_FUL_PKG AS
2 /* $Header: csvsrflb.pls 120.3.12000000.2 2007/07/16 10:09:50 vpremach ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CS_SR_FUL_PKG';
4
5 PROCEDURE SR_SINGLE_REQUEST(P_API_VERSION in NUMBER,
6 P_INCIDENT_ID in NUMBER,
7 P_INCIDENT_NUMBER in VARCHAR2 ,
8 P_USER_ID in NUMBER,
9 P_EMAIL in VARCHAR2,
10 P_SUBJECT in VARCHAR2, --bug 4527968 prayadur
11 P_FAX in VARCHAR2,
12 X_RETURN_STATUS out NOCOPY VARCHAR2,
13 X_MSG_COUNT out NOCOPY number,
14 X_MSG_DATA out NOCOPY varchar2) IS
15
16 l_api_version NUMBER := 1.0;
17 l_api_name CONSTANT VARCHAR2(30) := 'SR_SINGLE_REQUEST';
18 l_commit VARCHAR2(5) := FND_API.G_TRUE;
19 --
20
21 l_content_id VARCHAR2(30);
22 fulfillment_user_note VARCHAR2(2000);
23 l_bind_var JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ;
24 l_bind_var_type JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ;
25 l_bind_val JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ;
26 --
27 l_template_id NUMBER;
28 l_party_id NUMBER;
29 l_party_name VARCHAR2(100);
30 l_party_name_tag VARCHAR2(100);
31 l_user_id NUMBER;
32 l_server_id NUMBER;
33 l_request_id NUMBER;
34 l_subject VARCHAR2(100);
35 --
36 l_msg_count NUMBER;
37 l_msg_data VARCHAR2(5000);
38 l_return_status VARCHAR2(1000);
39 --l_content_xml VARCHAR(30000);
40 -- To conform w/ out parameter of Get_content_XML()
41 -- Fix for bug 3251623. prayadur 02/25/04.
42 l_content_xml VARCHAR(32767);
43 l_content_nm VARCHAR2(100);
44 l_email VARCHAR2(200);
45 l_printer VARCHAR2(100);
46 l_fax VARCHAR2(100);
47
48 -- Fix for bug 3251623. prayadur 02/25/04.
49 l_media_type VARCHAR2(100);
50 l_request_type VARCHAR2(50);
51 l_document_type VARCHAR2(50);
52
53 --- SR fields --
54
55 l_incident_id NUMBER(15);
56 l_mfg_org_id number;
57 l_doc_type varchar2(30) := 'SR';
58
59 l_status_code varchar2(30);
60 l_incident_type varchar2(30);
61 l_severity varchar2(30);
62 l_urgency varchar2(30);
63 l_owner varchar2(240);
64 l_company_name varchar2(255);
65 l_account_number varchar2(30);
66
67 --l_customer_ticket_number number;
68 -- To solve bug no. 2189108. l_customer_ticket_number changed from
69 -- number to varchar2. Added by pnkalari on 01/18/02.
70 l_customer_ticket_number varchar2(50);
71
72 l_person_first_name varchar2(150);
73 l_person_last_name varchar2(150);
74 l_date_opened varchar2(15);
75 l_date_closed varchar2(15);
76 l_product_name varchar2(40);
77 l_product_description varchar2(240);
78 l_summary varchar2(240);
79 l_system_name varchar2(50);
80
81 l_customer_id number(15);
82 l_serial_number varchar2(30);
83 l_platform_name varchar2(30) := null;
84 l_platform_id number;
85 l_platform_version varchar2(30) := null;
86 l_platform_version_id number;
87
88 l_component_id number;
89 l_component_name varchar2(40);
90 l_component_description varchar2(240);
91 l_component_version varchar2(15);
92 l_subcomponent_id number;
93 l_subcomponent_name varchar2(40);
94 l_subcomponent_description varchar2(240);
95 l_subcomponent_version varchar2(15);
96
97 l_problem_code varchar2(50);
98 l_resolution_code varchar2(50);
99 l_problem_meaning varchar2(80);
100 l_resolution_meaning varchar2(80);
101
102 l_inv_component_id number;
103 l_inv_component_version varchar2(3);
104 l_inv_subcomponent_id number;
105 l_inv_subcomponent_version varchar2(3);
106
107 l_customer_product_id number(15);
108 l_inventory_item_id number(15);
109 l_current_serial_number varchar2(30);
110
111 l_note_type varchar2(30);
112
113 -- Installed Base 11.5.6 Upgrade enhancement# 1875922. 08/07/01 rmanabat
114 ib_version VARCHAR2(25) := csi_utility_grp.ib_version ;
115
116 --declare cursors here
117
118 CURSOR inc_v_csr is
119 select problem_code,resolution_code,customer_product_id,inv_organization_id,
120 cp_component_id,cp_subcomponent_id,inventory_item_id,inv_component_id,
121 inv_component_version,inv_subcomponent_id,inv_subcomponent_version,
122 current_serial_number_nv,platform_id,platform_version_id,
123 status_code,incident_type,severity,urgency,owner,
124 company_name,account,customer_id,
125 customer_ticket_number,person_first_name, person_last_name,
126 incident_date,date_closed,
127 product_name,product_description,summary
128 from cs_incidents_v
129 where incident_id = l_incident_id;
130
131 -- Enhancement 2188129. Replacing view cs_new_incidents_v with cs_sr_incidents_v
132 -- Added by pnkalari on 02/07/2002.
133 -- Added cursors cs_owner_csr,cs_account_csr,cs_person_name_csr,cs_product_csr.
134 -- ===============================================================================================
135
136 CURSOR inc_v_csr_1156 is
137 SELECT problem_code,
138 resolution_code,
139 customer_product_id,
140 inv_organization_id,
141 cp_component_id,
142 cp_subcomponent_id,
143 inventory_item_id,
144 inv_component_id,
145 inv_component_version,
146 inv_subcomponent_id,
147 inv_subcomponent_version,
148 current_serial_number_nv,
149 platform_id,
150 platform_version_id,
151 status_code,
152 incident_type,
153 severity,
154 urgency,
155 customer_id,
156 customer_ticket_number,
157 incident_date,
158 date_closed,
159 SUMMARY
160 FROM
161 CS_SR_INCIDENTS_V
162 WHERE
163 incident_id = l_incident_id;
164
165 CURSOR cs_owner_csr is
166 SELECT a.resource_name from cs_sr_owners_v a, cs_sr_incidents_v b
167 where a.resource_id = b.incident_owner_id
168 and b.incident_id = l_incident_id;
169
170 CURSOR cs_account_csr is
171 SELECT a.account_number from jtf_cust_accounts_all_v a, cs_sr_incidents_v b
172 where b.account_id = a.cust_account_id
173 and b.incident_id = l_incident_id;
174
175 CURSOR cs_person_name_csr is
176 SELECT a.sub_first_name, a.sub_last_name from csc_hz_parties_self_v a, cs_hz_sr_contact_points b,
177 cs_sr_incidents_v c
178 where b.incident_id = c.incident_id
179 and b.party_id = a.party_id
180 and b.primary_flag = 'Y'
181 and c.incident_id = l_incident_id ;
182
183 CURSOR cs_product_csr is
184 SELECT a.concatenated_segments, a.description from mtl_system_items_kfv a, cs_sr_incidents_v b
185 where a.inventory_item_id = b.inventory_item_id
186 and a.organization_id = CS_STD.Get_Item_Valdn_Orgzn_Id
187 and b.incident_id = l_incident_id;
188
189 -- ===============================================================================================
190
191
192 /*
193 CURSOR inc_v_csr_1156 is
194 SELECT problem_code,
195 resolution_code,
196 customer_product_id,
197 inv_organization_id,
198 cp_component_id,
199 cp_subcomponent_id,
200 inventory_item_id,
201 inv_component_id,
202 inv_component_version,
203 inv_subcomponent_id,
204 inv_subcomponent_version,
205 current_serial_number_nv,
206 platform_id,
207 platform_version_id,
208 status_code,
209 incident_type,
210 severity,
211 urgency,
212 owner,
213 company_name,
214 account,
215 customer_id,
216 customer_ticket_number,
217 person_first_name,
218 person_last_name,
219 incident_date,
220 date_closed,
221 PRODUCT_NAME,
222 PRODUCT_DESCRIPTION,
223 SUMMARY
224 FROM
225 CS_NEW_INCIDENTS_V
226 WHERE
227 incident_id = l_incident_id;
228 */
229
230
231 CURSOR jtf_party_csr is
232 select party_name
233 from jtf_parties_all_v
234 where party_id = l_customer_id;
235
236
237 CURSOR cs_lookup_prob_csr is
238 select meaning
239 from cs_lookups
240 where lookup_type = 'REQUEST_PROBLEM_CODE'
241 and lookup_code = l_problem_code;
242
243 CURSOR cs_lookup_res_csr is
244 select meaning
245 from cs_lookups
246 where lookup_type = 'REQUEST_RESOLUTION_CODE'
247 and lookup_code = l_resolution_code;
248
249 CURSOR cs_acc_ser_csr is
250 select current_serial_number, system_name, platform,version
251 from cs_acc_cp_rg_v
252 where customer_product_id = l_customer_product_id;
253
254 -- Installed Base 11.5.6 Upgrade enhancement# 1875922. 08/07/01 rmanabat.
255 CURSOR cs_acc_ser_csr_1156 is
256 select serial_number, system_name, platform,version
257 from cs_sr_new_acc_cp_rg_v
258 where instance_id = l_customer_product_id;
259
260 CURSOR cs_acc_prod_csr is
261 select product_name,substr(product_description,1,15),revision
262 from cs_acc_cp_rg_v
263 where config_parent_id = l_customer_product_id and
264 customer_product_id = l_component_id;
265
266 -- Installed Base 11.5.6 Upgrade enhancement# 1875922. 08/07/01 rmanabat.
267 CURSOR cs_acc_prod_csr_1156 is
268 select product_name,substr(product_description,1,15),inventory_revision
269 from cs_sr_new_acc_cp_rg_v
270 where object_id = l_customer_product_id and
271 instance_id = l_component_id;
272
273 CURSOR cs_acc_sub_csr is
274 select product_name,product_description,revision
275 from cs_acc_cp_rg_v
276 where config_parent_id = l_component_id and
277 customer_product_id = l_subcomponent_id;
278
279 -- Installed Base 11.5.6 Upgrade enhancement# 1875922. 08/07/01 rmanabat.
280 CURSOR cs_acc_sub_csr_1156 is
281 select product_name,product_description,inventory_revision
282 from cs_sr_new_acc_cp_rg_v
283 where object_id = l_component_id and
284 instance_id = l_subcomponent_id;
285
286 -- nov/06/2000 commented out temporarily as defects is not ready as yet
287 --platform name and version will be null until defects is ready
288 -- uncomment all css reference when required
289 /*
290 CURSOR css_plat_csr is
291 select name
292 from css_def_platforms
293 where platform_id = l_platform_id;
294
295 CURSOR css_vers_csr is
296 select version
297 from css_def_plat_versions
298 where platform_version_id = l_platform_version_id;
299 */
300
301 -- Modification of below cursor to include version for Bug 3592225.Prayadur.
302 CURSOR cs_inv_comp_csr is
303 select concatenated_segments, sr_comp.description,revision
304 from cs_sr_inv_components_v sr_comp, mtl_item_revisions rev
305 where
306 sr_comp.component_id = rev.inventory_item_id and
307 sr_comp.organization_id = rev.organization_id and
308 sr_comp.inventory_item_id = l_inventory_item_id and
309 sr_comp.organization_id = l_mfg_org_id and
310 sr_comp.component_id = l_inv_component_id;
311
312 -- Modification of below cursor to include version for Bug 3592225.Prayadur.
313 CURSOR cs_inv_subcomp_csr is
314 select concatenated_segments, sr_sub.description,revision
315 from cs_sr_inv_subcomponents_v sr_sub, mtl_item_revisions rev
316 where
317 sr_sub.component_id = rev.inventory_item_id and
318 sr_sub.organization_id = rev.organization_id and
319 sr_sub.component_id = l_inv_component_id and
320 sr_sub.organization_id = l_mfg_org_id and
321 sr_sub.subcomponent_id = l_inv_subcomponent_id;
322
323
324 -- commented inc_log_csr cursor and added inc_notes_cursor for Enhancement 2248691.
325 -- Will pass notes instead of log for agents comments.
326 /*CURSOR inc_log_csr is
327 select substr(cs_sr_log_pkg.sr_log(p_incident_id),1,27000) log from dual; */
328
329 CURSOR inc_notes_csr is
330 select notes from jtf_notes_vl
331 where source_object_id = p_incident_id and
332 note_type = l_note_type and
333 jtf_note_id = (select max(jtf_note_id) from jtf_notes_vl
334 where source_object_id = p_incident_id
335 and note_type = l_note_type);
336
337 -- Bug fix for 2428307. Added cursor by pnkalari on 07/01/2002.
338
339 CURSOR cs_platform_csr is
340 select incident.platform_version,item.concatenated_segments platform
341 from mtl_system_items_vl item,
342 mtl_item_categories ic,
343 cs_sr_incidents_v incident
344 where item.organization_id = fnd_profile.value('CS_INV_VALIDATION_ORG')
345 and item.serv_req_enabled_code = 'E'
346 and item.organization_id = ic.organization_id
347 and item.inventory_item_id = ic.inventory_item_id
348 and ic.category_set_id = fnd_profile.value('CS_SR_PLATFORM_CATEGORY_SET')
349 and item.inventory_item_id = incident.platform_id
350 and item.inventory_item_id = l_platform_id
351 and incident.incident_id = l_incident_id ;
352
353 BEGIN
354 fnd_msg_pub.initialize;
355
356 /* get all fields for SR */
357
358 l_incident_id := p_incident_id;
359
360 /*
361 IF (ib_version = '1150') THEN
362 OPEN inc_v_csr;
363 FETCH inc_v_csr into
364 l_problem_code,l_resolution_code,l_customer_product_id,l_mfg_org_id,
365 l_component_id,l_subcomponent_id,l_inventory_item_id,l_inv_component_id,
366 l_inv_component_version,l_inv_subcomponent_id,l_inv_subcomponent_version,
367 l_current_serial_number,l_platform_id,l_platform_version_id,
368 l_status_code,l_incident_type,l_severity,l_urgency,l_owner,
369 l_company_name,l_account_number,l_customer_id,
370 l_customer_ticket_number,l_person_first_name,l_person_last_name,
371 l_date_opened,l_date_closed,
372 l_product_name,l_product_description,l_summary;
373
374 if (inc_v_csr%notfound) then
375 null;
376 end if;
377 CLOSE inc_v_csr;
378 ELSE
379 OPEN inc_v_csr_1156;
380 FETCH inc_v_csr_1156 into
381 l_problem_code,l_resolution_code,l_customer_product_id,l_mfg_org_id,
382 l_component_id,l_subcomponent_id,l_inventory_item_id,l_inv_component_id,
383 l_inv_component_version,l_inv_subcomponent_id,l_inv_subcomponent_version,
384 l_current_serial_number,l_platform_id,l_platform_version_id,
385 l_status_code,l_incident_type,l_severity,l_urgency,l_owner,
386 l_company_name,l_account_number,l_customer_id,
387 l_customer_ticket_number,l_person_first_name,l_person_last_name,
388 l_date_opened,l_date_closed,
389 l_product_name,l_product_description,l_summary;
390
391 if (inc_v_csr_1156%notfound) then
392 null;
393 end if;
394 CLOSE inc_v_csr_1156;
395 END IF;
396 */
397
398 OPEN inc_v_csr_1156;
399 FETCH inc_v_csr_1156 into
400 l_problem_code,l_resolution_code,l_customer_product_id,l_mfg_org_id,
401 l_component_id,l_subcomponent_id,l_inventory_item_id,l_inv_component_id,
402 l_inv_component_version,l_inv_subcomponent_id,l_inv_subcomponent_version,
403 l_current_serial_number,l_platform_id,l_platform_version_id,
404 l_status_code,l_incident_type,l_severity,l_urgency,
405 l_customer_id,
406 l_customer_ticket_number,
407 l_date_opened,l_date_closed,
408 l_summary;
409
410 if (inc_v_csr_1156%notfound) then
411 null;
412 end if;
413 CLOSE inc_v_csr_1156;
414
415 -- Bug fix for 2208493. Added by pnkalari. Put fetch cursor in begin end block to trap value_error exception.
416 -- Added on 01/31/02
417
418 BEGIN
419 -- replace inc_log_csr cursor with inc_notes_csr for Enhancement 2248691.
420 /*OPEN inc_log_csr ;
421 FETCH inc_log_csr into fulfillment_user_note;
422 if (inc_log_csr%notfound) then
423 null;
424 end if;
425 CLOSE inc_log_csr; */
426
427 FND_PROFILE.GET('CS_SR_DEFAULT_AGENT_COMMENTS',l_note_type);
428 if (l_note_type is not null) then
429 OPEN inc_notes_csr ;
430 FETCH inc_notes_csr into fulfillment_user_note ;
431 if(inc_notes_csr%notfound) then
432 fulfillment_user_note := null ;
433 end if ;
434 else
435 fulfillment_user_note := null ;
436 end if;
437
438 EXCEPTION
439 WHEN VALUE_ERROR THEN
440 fulfillment_user_note := substr(fulfillment_user_note,1,2000);
441
442 END;
443 -- select party id and party name
444
445 l_party_id := l_customer_id;
446
447 -- ============================================================================
448
449 OPEN cs_owner_csr ;
450 FETCH cs_owner_csr into l_owner ;
451 if(cs_owner_csr%notfound) then
452 null ;
453 end if ;
454 CLOSE cs_owner_csr;
455
456 OPEN cs_account_csr;
457 FETCH cs_account_csr into l_account_number ;
458 if(cs_account_csr%notfound) then
459 null ;
460 end if ;
461 CLOSE cs_account_csr;
462
463 OPEN cs_person_name_csr ;
464 FETCH cs_person_name_csr into l_person_first_name,l_person_last_name ;
465 if(cs_person_name_csr%notfound) then
466 null ;
467 end if ;
468 CLOSE cs_person_name_csr;
469
470 OPEN cs_product_csr ;
471 FETCH cs_product_csr into l_product_name, l_product_description ;
472 if(cs_product_csr%notfound) then
473 null ;
474 end if ;
475 CLOSE cs_product_csr;
476
477 if l_customer_id is not null then
478 OPEN jtf_party_csr;
479 FETCH jtf_party_csr into l_company_name ;
480 if(jtf_party_csr%notfound) then
481 null;
482 end if;
483 CLOSE jtf_party_csr ;
484 end if ;
485
486 -- ============================================================================
487 if l_customer_id is not null then
488 OPEN jtf_party_csr;
489 FETCH jtf_party_csr into l_party_name;
490 if (jtf_party_csr%notfound) then
491 null;
492 end if;
493
494 CLOSE jtf_party_csr;
495 end if;
496
497
498 if l_problem_code is not null then
499 OPEN cs_lookup_prob_csr;
500 FETCH cs_lookup_prob_csr into
501 l_problem_meaning;
502 if (cs_lookup_prob_csr%notfound) then
503 null;
504 end if;
505 CLOSE cs_lookup_prob_csr;
506
507 end if;
508
509 if l_resolution_code is not null then
510 OPEN cs_lookup_res_csr;
511 FETCH cs_lookup_res_csr into
512 l_resolution_meaning;
513 if (cs_lookup_res_csr%notfound) then
514 null;
515 end if;
516 CLOSE cs_lookup_res_csr;
517 end if;
518
519
520 if l_customer_product_id is not null then
521 -- item is an installed base item
522 -- get items from installed base view
523
524 /* IF (ib_version = '1150') THEN
525 OPEN cs_acc_ser_csr;
526 FETCH cs_acc_ser_csr into
527 l_current_serial_number,l_system_name,l_platform_name,l_platform_version;
528 if (cs_acc_ser_csr%notfound) then
529 null;
530 end if;
531 CLOSE cs_acc_ser_csr;
532 ELSE */
533
534 OPEN cs_acc_ser_csr_1156;
535 FETCH cs_acc_ser_csr_1156 into
536 l_current_serial_number,l_system_name,l_platform_name,l_platform_version;
537 if (cs_acc_ser_csr_1156%notfound) then
538 null;
539 end if;
540 CLOSE cs_acc_ser_csr_1156;
541 -- END IF;
542
543 if l_component_id is not null then
544
545 /* IF (ib_version = '1150') THEN
546 OPEN cs_acc_prod_csr;
547 FETCH cs_acc_prod_csr into
548 l_component_name,l_component_description,l_component_version;
549 if (cs_acc_prod_csr%notfound) then
550 null;
551 end if;
552 CLOSE cs_acc_prod_csr;
553 ELSE */
554 OPEN cs_acc_prod_csr_1156;
555 FETCH cs_acc_prod_csr_1156 into
556 l_component_name,l_component_description,l_component_version;
557 if (cs_acc_prod_csr_1156%notfound) then
558 null;
559 end if;
560 CLOSE cs_acc_prod_csr_1156;
561 -- END IF;
562
563 end if;
564
565 if l_subcomponent_id is not null then
566
567 /* IF (ib_version = '1150') THEN
568 OPEN cs_acc_sub_csr;
569 FETCH cs_acc_sub_csr into
570 l_subcomponent_name,l_subcomponent_description,l_subcomponent_version ;
571 if (cs_acc_sub_csr%notfound) then
572 null;
573 end if;
574 CLOSE cs_acc_sub_csr;
575 ELSE */
576 OPEN cs_acc_sub_csr_1156;
577 FETCH cs_acc_sub_csr_1156 into
578 l_subcomponent_name,l_subcomponent_description,l_subcomponent_version ;
579 if (cs_acc_sub_csr_1156%notfound) then
580 null;
581 end if;
582 CLOSE cs_acc_sub_csr_1156;
583 -- END IF;
584
585 end if;
586
587 else
588
589 -- item is not an installed base item
590 -- get items from inventory and platform details from defect tables
591
592 -- Nov/6/2000 defects not ready therefore comment out all reference
593 --to css tables temporarily
594
595 /* if l_platform_id is not null then
596 OPEN css_plat_csr;
597 FETCH css_plat_csr into
598 l_platform_name;
599 if (css_plat_csr%notfound) then
600 null;
601 end if;
602 CLOSE css_plat_csr;
603 end if;
604
605 if l_platform_version_id is not null then
606 OPEN css_vers_csr;
607 FETCH css_vers_csr into
608 l_platform_version;
609 if (css_vers_csr%notfound) then
610 null;
611 end if;
612 CLOSE css_vers_csr;
613 end if;
614 */
615
616 -- Added l_component_version in fetch caluse for Bug 3592225.Prayadur.
617 if l_inv_component_id is not null then
618 OPEN cs_inv_comp_csr;
619 FETCH cs_inv_comp_csr into
620 l_component_name,l_component_description,l_component_version;
621 if (cs_inv_comp_csr%notfound) then
622 null;
623 end if;
624 CLOSE cs_inv_comp_csr;
625 end if;
626
627 -- Added l_subcomponent_version in fetch caluse for Bug 3592225.Prayadur.
628 if l_inv_subcomponent_id is not null then
629 OPEN cs_inv_subcomp_csr;
630 FETCH cs_inv_subcomp_csr into
631 l_subcomponent_name,l_subcomponent_description,l_subcomponent_version;
632 if (cs_inv_subcomp_csr%notfound) then
633 null;
634 end if;
635 CLOSE cs_inv_subcomp_csr;
636
637 end if;
638
639 end if;
640 -----
641 OPEN cs_platform_csr ;
642 FETCH cs_platform_csr into
643 l_platform_version,l_platform_name ;
644 if(cs_platform_csr%notfound) then
645 null ;
646 end if;
647 CLOSE cs_platform_csr ;
648
649 --initialize return status to success before call to
650 x_return_status := FND_API.G_RET_STS_SUCCESS;
651
652
653 -- Start the fulfillment request. The output request_id must be passed
654 -- to all subsequent calls made for this request.
655 JTF_FM_REQUEST_GRP.STart_Request
656 (
657 p_api_version => l_api_version,
658 x_return_status => l_return_status,
659 x_msg_count => l_msg_count,
660 x_msg_data => l_msg_data,
661 x_request_id => l_request_id
662 );
663
664 -- test for failure
665
666 --if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
667 -- 11/10/03. rmanabat . Should check for l_return_status, not x_return_status.
668 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
669 --DBMS_OUTPUT.PUT_LINE('fail');
670 raise FND_API.G_EXC_UNEXPECTED_ERROR ;
671 end if;
672
673 --DBMS_OUTPUT.PUT_LINE('Request_ID: '||to_char(l_request_id));
674
675 -- store the destination addresses into local variables
676 l_email := P_EMAIL;
677 l_fax := P_FAX;
678
679 -- Fix for bug 3251623. prayadur 02/25/04
680 --l_media_type := 'EMAIL,FAX';
681 --Setting dynamically
682 IF (P_EMAIL is not null) THEN
683 l_media_type := 'EMAIL';
684 ELSIF (P_FAX is not null) THEN
685 l_media_type := 'FAX';
686 END IF;
687
688 -- test content id l_content_id := '13065';
689
690 -- content_id is hardcoded to 1000 , which is the seed value for item_id
691 --The content_id is seeded in seed115 ( file has to be uploaded into FND_LOBS
692 -- and seeded into jtf_amv_attachments and jtf_amv_items.This is done thru
693 -- ldt scripts owned by marketing; item_id = content_id = 1000 )
694
695 l_content_id := 1000;
696
697 -- assign bind varibles
698 -- The replace_tag function is used b'cos certain characters
699 -- are not accepted by fulfillment eg. &, <,> and '
700
701 l_bind_var(1) := 'REQUEST_NUMBER';
702 l_bind_var_type(1) := 'VARCHAR2';
703 l_bind_val(1) := jtf_fm_request_grp.replace_tag(p_incident_number);
704
705 l_bind_var(2) := 'STATUS';
706 l_bind_var_type(2) := 'VARCHAR2';
707 l_bind_val(2) := jtf_fm_request_grp.replace_tag(l_status_code);
708
709 l_bind_var(3) := 'REQUEST_TYPE';
710 l_bind_var_type(3) := 'VARCHAR2';
711 l_bind_val(3) := jtf_fm_request_grp.replace_tag(l_incident_type);
712
713 l_bind_var(4) := 'SEVERITY';
714 l_bind_var_type(4) := 'VARCHAR2';
715 l_bind_val(4) := jtf_fm_request_grp.replace_tag(l_severity);
716
717 l_bind_var(5) := 'URGENCY';
718 l_bind_var_type(5) := 'VARCHAR2';
719 l_bind_val(5) := jtf_fm_request_grp.replace_tag(l_urgency);
720
721 l_bind_var(6) := 'OWNER';
722 l_bind_var_type(6) := 'VARCHAR2';
723 l_bind_val(6) := jtf_fm_request_grp.replace_tag(l_owner);
724
725 l_bind_var(7) := 'COMPANY';
726 l_bind_var_type(7) := 'VARCHAR2';
727 l_bind_val(7) := jtf_fm_request_grp.replace_tag(l_company_name);
728
729 l_bind_var(8) := 'ACCOUNT';
730 l_bind_var_type(8) := 'VARCHAR2';
731 l_bind_val(8) := jtf_fm_request_grp.replace_tag(l_account_number);
732
733 l_bind_var(9) := 'HELPDESK_NUMBER';
734 l_bind_var_type(9) := 'VARCHAR2';
735 l_bind_val(9) := jtf_fm_request_grp.replace_tag(l_customer_ticket_number);
736
737 l_bind_var(10) := 'FIRST_NAME';
738 l_bind_var_type(10) := 'VARCHAR2';
739 l_bind_val(10) := jtf_fm_request_grp.replace_tag(l_person_first_name);
740
741 l_bind_var(11) := 'LAST_NAME';
742 l_bind_var_type(11) := 'VARCHAR2';
743 l_bind_val(11) := jtf_fm_request_grp.replace_tag(l_person_last_name);
744
745 l_bind_var(12) := 'DATE_OPENED';
746 l_bind_var_type(12) := 'VARCHAR2';
747 l_bind_val(12) := jtf_fm_request_grp.replace_tag(l_date_opened);
748
749 l_bind_var(13) := 'DATE_CLOSED';
750 l_bind_var_type(13) := 'VARCHAR2';
751 l_bind_val(13) := jtf_fm_request_grp.replace_tag(l_date_closed);
752
753 l_bind_var(14) := 'PRODUCT_NAME';
754 l_bind_var_type(14) := 'VARCHAR2';
755 l_bind_val(14) := jtf_fm_request_grp.replace_tag(l_product_name);
756
757 l_bind_var(15) := 'PRODUCT_DESCRIPTION';
758 l_bind_var_type(15) := 'VARCHAR2';
759 l_bind_val(15) := jtf_fm_request_grp.replace_tag(l_product_description);
760
761 l_bind_var(16) := 'REQUEST_SUMMARY';
762 l_bind_var_type(16) := 'VARCHAR2';
763 l_bind_val(16) := jtf_fm_request_grp.replace_tag(l_summary);
764
765 l_bind_var(17) := 'PROBLEM_MEANING';
766 l_bind_var_type(17) := 'VARCHAR2';
767 l_bind_val(17) := jtf_fm_request_grp.replace_tag(l_problem_meaning);
768
769 l_bind_var(18) := 'RESOLUTION_MEANING';
770 l_bind_var_type(18) := 'VARCHAR2';
771 l_bind_val(18) := jtf_fm_request_grp.replace_tag(l_resolution_meaning);
772
773 l_bind_var(19) := 'SYSTEM_NAME';
774 l_bind_var_type(19) := 'VARCHAR2';
775 l_bind_val(19) := jtf_fm_request_grp.replace_tag(l_system_name);
776
777 l_bind_var(20) := 'SERIAL_NUMBER';
778 l_bind_var_type(20) := 'VARCHAR2';
779 l_bind_val(20) := jtf_fm_request_grp.replace_tag(l_current_serial_number);
780
781 l_bind_var(21) := 'PLATFORM_NAME';
782 l_bind_var_type(21) := 'VARCHAR2';
783 l_bind_val(21) := jtf_fm_request_grp.replace_tag(l_platform_name);
784
785 l_bind_var(22) := 'PLATFORM_VERSION';
786 l_bind_var_type(22) := 'VARCHAR2';
787 l_bind_val(22) := jtf_fm_request_grp.replace_tag(l_platform_version);
788
789 l_bind_var(23) := 'COMPONENT_NAME';
790 l_bind_var_type(23) := 'VARCHAR2';
791 l_bind_val(23) := jtf_fm_request_grp.replace_tag(l_component_name);
792
793 l_bind_var(24) := 'COMPONENT_DESCRIPTION';
794 l_bind_var_type(24) := 'VARCHAR2';
795 l_bind_val(24) := jtf_fm_request_grp.replace_tag(l_component_description);
796
797 l_bind_var(25) := 'COMPONENT_VERSION';
798 l_bind_var_type(25) := 'VARCHAR2';
799 l_bind_val(25) := jtf_fm_request_grp.replace_tag(l_component_version);
800
801 l_bind_var(26) := 'SUBCOMPONENT_NAME';
802 l_bind_var_type(26) := 'VARCHAR2';
803 l_bind_val(26) := jtf_fm_request_grp.replace_tag(l_subcomponent_name);
804
805 l_bind_var(27) := 'SUBCOMPONENT_DESCRIPTION';
806 l_bind_var_type(27) := 'VARCHAR2';
807 l_bind_val(27) := jtf_fm_request_grp.replace_tag(l_subcomponent_description);
808
809 l_bind_var(28) := 'SUBCOMPONENT_VERSION';
810 l_bind_var_type(28) := 'VARCHAR2';
811 l_bind_val(28) := jtf_fm_request_grp.replace_tag(l_subcomponent_version);
812
813 -- Fix for bug 3251623 . prayadur . 02/25/04
814 -- Commented out. XML content should not be manually built
815 /***********************************************************************
816
817 -- The XML request for the content is being formed in the code below
818 l_content_xml := '<item> ';
819 l_content_xml := l_content_xml || '<item_destination>';
820 l_content_xml := l_content_xml || '<media_type>';
821
822 if p_email is not null then
823 -- deliver content by email
824 l_content_xml := l_content_xml || '<email>'||l_email||'</email>';
825 elsif p_fax is not null then
826 -- deliver content by fax
827 l_content_xml := l_content_xml || '<fax>'||l_fax||'</fax>';
828 end if;
829
830 l_content_xml := l_content_xml || '</media_type>';
831 l_content_xml := l_content_xml || '</item_destination>';
832 l_content_xml := l_content_xml || '<item_content content_id="'||l_content_id||'" quantity="1" user_note="'||JTF_FM_REQUEST_GRP.REPLACE_TAG(fulfillment_user_note)||'">';
833 l_content_xml := l_content_xml || '<data>';
834 l_content_xml := l_content_xml || '<record>';
835
836
837
838 FOR i IN 1..l_bind_var.count LOOP
839 l_content_xml := l_content_xml || '<bind_var bind_type="'||l_bind_var_type(i)||'" bind_object="'||l_bind_var(i)||'">'||l_bind_val(i)||'</bind_var>';
840
841 END LOOP;
842
843 l_content_xml := l_content_xml || '</record>';
844 l_content_xml := l_content_xml || '</data>';
845 l_content_xml := l_content_xml || '</item_content>';
846 l_content_xml := l_content_xml || '</item>';
847
848 ***********************************************************************/
849
850 -- Initialize Parameters for submitting the fulfillment request
851 l_user_id := p_user_id;
852
853 -- default user value (for vision) for testing
854 -- l_user_id := 1001;
855
856 --l_subject := 'Service Request Details'; --commented this for bug 4527968 prayadur.
857 l_subject := P_SUBJECT ; --Added this for bug 4527968 prayadur.
858
859 --initialize return status to success before call to
860 x_return_status := FND_API.G_RET_STS_SUCCESS;
861
862
863 -- Start of Fix for bug 3251623 . prayadur . 02/25/04
864 -- Removed the XML getting created and instead calling GET_CONTENT_XML
865 l_request_type := 'DATA' ; -- 'Query' if Query is attached to your document, else DATA
866 l_document_type := 'html'; -- Master Documents are usually htm or html files
867
868 JTF_FM_REQUEST_GRP.Get_Content_XML
869 (
870 p_api_version => l_api_version,
871 x_return_status => l_return_status,
872 x_msg_count => l_msg_count,
873 x_msg_data => l_msg_data,
874 p_content_id => 1000,
875 --p_content_nm => l_content_nm,
876 p_content_nm => FND_API.G_MISS_CHAR, -- using default value.
877 p_document_type => l_document_type,
878 /**** source code says this is deprecated, but using html as stated in bug.
879 p_document_type => FND_API.G_MISS_CHAR,
880 *****/
881 p_media_type => l_media_type,
882 p_printer => null,
883 p_email => l_email,
884 p_file_path => null,
885 p_fax => l_fax,
886 p_user_note => fulfillment_user_note,
887 p_content_type => l_request_type,
888 p_bind_var => l_bind_var,
889 p_bind_val => l_bind_val,
890 p_bind_var_type => l_bind_var_type,
891 p_request_id => l_request_id,
892 x_content_xml => l_content_xml);
893
894 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
895 --DBMS_OUTPUT.PUT_LINE('fail');
896 raise FND_API.G_EXC_UNEXPECTED_ERROR ;
897 end if;
898
899 -- End of fix for bug 3251623.
900
901 -- Submit the fulfillment request
902 JTF_FM_REQUEST_GRP.Submit_Request
903 ( p_api_version => l_api_version,
904 p_commit => l_commit,
905 x_return_status => l_return_status,
906 x_msg_count => l_msg_count,
907 x_msg_data => l_msg_data,
908 p_subject => l_subject,
909 p_party_id => l_party_id,
910 p_party_name => l_party_name,
911 p_doc_id => l_incident_id,
912 p_doc_ref => l_doc_type,
913 p_user_id => l_user_id,
914 p_content_xml => l_content_xml,
915 p_request_id => l_request_id
916 );
917
918
919 --DBMS_OUTPUT.PUT_LINE('Return Status: '||l_return_status);
920 --DBMS_OUTPUT.PUT_LINE('Message_Count: '||l_msg_count);
921 --DBMS_OUTPUT.PUT_LINE('Message Data: '||l_msg_data);
922
923 -- this is for testing failure
924 -- x_return_status := 'F';
925
926 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
927 raise FND_API.G_EXC_UNEXPECTED_ERROR ;
928 else
929 -- if successful then display request id to user
930 FND_MESSAGE.Set_Name('CS','CS_SR_FULFIL_REQUEST');
931 FND_MESSAGE.Set_Token('REQUEST_ID',l_request_id);
932 FND_MSG_PUB.Add;
933 end if;
934
935 FND_MSG_PUB.Count_And_Get
936 ( p_count => x_msg_count,
937 p_data => x_msg_data
938 );
939
940 EXCEPTION
941
942 WHEN FND_API.G_EXC_ERROR THEN
943 x_return_status := FND_API.G_RET_STS_ERROR;
944 FND_MSG_PUB.Count_And_Get
945 ( p_count => x_msg_count,
946 p_data => x_msg_data
947 );
948 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
949 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
950 FND_MSG_PUB.Count_And_Get
951 ( p_count => x_msg_count,
952 p_data => x_msg_data
953 );
954 WHEN OTHERS THEN
955 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
956 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
957 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
958 END IF;
959 FND_MSG_PUB.Count_And_Get
960 ( p_count => x_msg_count,
961 p_data => x_msg_data
962 );
963
964 END SR_SINGLE_REQUEST;
965
966 PROCEDURE SR_RESUBMIT_REQUEST(P_API_VERSION in NUMBER,
967 P_REQUEST_ID in NUMBER,
968 X_RETURN_STATUS out NOCOPY VARCHAR2,
969 X_MSG_COUNT out NOCOPY number,
970 X_MSG_DATA out NOCOPY varchar2) IS
971 --
972 -- Resubmit does not add any additional value so not being called in 11i.1
973 -- Button on form has been disabled
974
975 -- This procedure resubmits an already submitted request and should
976 -- not be used to resubmit a error free request
977 -- If the original request had errored then it will error again
978 -- User should correct error and submit new request instead
979 -- of resubmitting
980 --
981
982 l_api_version NUMBER := 1.0;
983 l_api_name CONSTANT VARCHAR2(30) := 'SR_RESUBMIT_REQUEST';
984 l_commit VARCHAR2(5) := FND_API.G_TRUE;
985 l_msg_count NUMBER;
986 l_msg_data VARCHAR2(5000);
987 l_return_status VARCHAR2(1000);
988 l_request_id NUMBER;
989
990 --initialize return status to success before call
991 BEGIN
992 x_return_status := FND_API.G_RET_STS_SUCCESS;
993
994 -- Resubmit the fulfillment request
995
996 JTF_FM_REQUEST_GRP.Resubmit_Request( p_api_version => l_api_version,
997 p_commit => l_commit,
998 x_return_status => l_return_status,
999 x_msg_count => l_msg_count,
1000 x_msg_data => l_msg_data,
1001 p_request_id => l_request_id
1002 );
1003
1004 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1005 raise FND_API.G_EXC_UNEXPECTED_ERROR ;
1006 else
1007 --if successful then display request id to user
1008 FND_MESSAGE.Set_Name('CS','CS_SR_FULFIL_REQUEST');
1009 FND_MESSAGE.Set_Token('REQUEST_ID',l_request_id);
1010 FND_MSG_PUB.Add;
1011 end if;
1012
1013 FND_MSG_PUB.Count_And_Get
1014 ( p_count => x_msg_count,
1015 p_data => x_msg_data
1016 );
1017
1018 EXCEPTION
1019 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1020 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1021 FND_MSG_PUB.Count_And_Get
1022 ( p_count => x_msg_count,
1023 p_data => x_msg_data
1024 );
1025 WHEN OTHERS THEN
1026 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1027 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1028 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1029 END IF;
1030 FND_MSG_PUB.Count_And_Get
1031 ( p_count => x_msg_count,
1032 p_data => x_msg_data
1033 );
1034
1035 END SR_RESUBMIT_REQUEST;
1036 END CS_SR_FUL_PKG;