DBA Data[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;