[Home] [Help]
PACKAGE BODY: APPS.IGS_CO_GEN_004
Source
1 PACKAGE BODY IGS_CO_GEN_004 AS
2 /* $Header: IGSCO23B.pls 120.0 2005/06/01 19:04:52 appldev noship $ */
3
4 /* +=======================================================================+
5 | Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | NAME |
9 | IGSCO23B.pls |
10 | |
11 | DESCRIPTION |
12 | This package provides service functions and procedures to |
13 | correspondence forms for sending test mails, resending requests |
14 | and view attachments . |
15 | |
16 | NOTES |
17 | |
18 | DEPENDENCIES |
19 | |
20 | USAGE |
21 | |
22 | HISTORY |
23 | who when what |
24 | kumma 20-AUG-2003 3091685, modified the get_attachments |
25 | to use the exists before accessing the |
26 | plsql table |
27 | ssawhney 3-may-04 IBC.C patchset changes bug 3565861 + 3442719
28 | |
29 +=======================================================================+ */
30
31 PROCEDURE resend_request
32 (
33 X_ROWID IN OUT NOCOPY VARCHAR2,
34 X_STUDENT_ID IN NUMBER,
35 X_DOCUMENT_ID IN NUMBER,
36 X_DOCUMENT_TYPE IN VARCHAR2,
37 X_SYS_LTR_CODE IN VARCHAR2,
38 X_ADM_APPLICATION_NUMBER IN NUMBER,
39 X_NOMINATED_COURSE_CD IN VARCHAR2,
40 X_SEQUENCE_NUMBER IN NUMBER,
41 X_CAL_TYPE IN VARCHAR2,
42 X_CI_SEQUENCE_NUMBER IN NUMBER,
43 X_REQUESTED_DATE IN DATE,
44 X_DELIVERY_TYPE IN VARCHAR2,
45 X_OLD_REQUEST_ID IN NUMBER,
46 X_NEW_REQUEST_ID OUT NOCOPY NUMBER,
47 X_MSG_COUNT OUT NOCOPY NUMBER,
48 X_MSG_DATA OUT NOCOPY VARCHAR2,
49 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
50 P_COMMIT IN VARCHAR2 ,
51 X_VERSION_ID IN NUMBER
52 )
53 ------------------------------------------------------------------
54 --Created by : kumma, Oracle India ()
55 --Date created: 06-jun-2003
56 --
57 --Purpose: 2853531
58 -- This procedure is used to re-send the request for a particular template document
59 --
60 --Known limitations/enhancements and/or remarks:
61 --
62 --Change History:
63 --Who When What
64 --ssawhney 12-may-2004 version_id added in CO_INTERACTION_HISTORY
65 -------------------------------------------------------------------
66 IS
67
68 l_api_version NUMBER ;
69 l_init_msg_list VARCHAR2(2) ;
70 l_commit VARCHAR2(2) ;
71 l_validation_level NUMBER ;
72 l_msg_index_out NUMBER;
73 l_tmp_var1 VARCHAR2(2000);
74 l_tmp_var VARCHAR2(2000);
75
76 BEGIN
77 l_api_version := 1.0;
78 l_init_msg_list := FND_API.G_TRUE;
79 l_commit := FND_API.G_FALSE;
80 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
81
82 JTF_FM_Request_GRP.Resubmit_Request
83 (
84 p_api_version => l_api_version,
85 p_init_msg_list => l_init_msg_list,
86 p_commit => l_commit,
87 p_validation_level => l_validation_level,
88 x_return_status => X_RETURN_STATUS,
89 x_msg_count => X_MSG_COUNT,
90 x_msg_data => X_MSG_DATA,
91 p_request_id => x_old_request_id,
92 x_request_id => x_new_request_id
93 );
94
95
96 IF X_RETURN_STATUS IN ('E','U') THEN
97 IF x_msg_count > 1 THEN
98 FOR i IN 1..x_msg_count
99 LOOP
100 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
101 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
102 END LOOP;
103 x_msg_data := l_tmp_var1;
104 END IF;
105 ELSE
106
107 --Request submitted successfully and we need to insert a history record
108 igs_co_interac_hist_pkg.insert_row (
109 x_rowid => x_rowid,
110 x_student_id => x_student_id,
111 x_request_id => x_new_request_id,
112 x_document_id => x_document_id,
113 x_document_type => x_document_type,
114 x_sys_ltr_code => x_sys_ltr_code,
115 x_adm_application_number => x_adm_application_number,
116 x_nominated_course_cd => x_nominated_course_cd,
117 x_sequence_number => x_sequence_number,
118 x_cal_type => x_cal_type,
119 x_ci_sequence_number => x_ci_sequence_number,
120 x_requested_date => x_requested_date,
121 x_delivery_type => x_delivery_type,
122 x_mode => 'R' ,
123 x_version_id => x_version_id
124 );
125
126 IF FND_API.To_Boolean( p_commit ) THEN
127 COMMIT WORK;
128 END IF;
129 END IF;
130 END resend_request;
131
132
133
134 PROCEDURE send_test_mail
135 (
136 X_MAIL_ID IN VARCHAR2,
137 X_SUBJECT IN VARCHAR2,
138 X_CRM_USER_ID IN NUMBER,
139 X_TEMPLATE_ID IN NUMBER,
140 X_VERSION_ID IN NUMBER,
141 X_MSG_COUNT OUT NOCOPY NUMBER,
142 X_MSG_DATA OUT NOCOPY VARCHAR2,
143 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
144 X_REQUEST_ID OUT NOCOPY NUMBER,
145 P_COMMIT IN VARCHAR2
146 )
147 ------------------------------------------------------------------
148 --Created by : kumma, Oracle India ()
149 --Date created: 06-jun-2003
150 --
151 --Purpose: 2853531
152 -- This procedure is used to send the test mails for a particular template document
153 --
154 --Known limitations/enhancements and/or remarks:
155 --
156 --Change History:
157 --Who When What
158 --ssawhney 3-may-04 IBC.C patchset changes bug 3565861 + 3442719
159 -------------------------------------------------------------------
160 IS
161
162 l_init_msg_list VARCHAR2(2);
163 l_api_version NUMBER ;
164 l_order_header_rec JTF_Fulfillment_PUB.ORDER_HEADER_REC_TYPE;
165 y_order_header_rec ASO_ORDER_INT.ORDER_HEADER_REC_TYPE;
166 l_order_line_tbl JTF_Fulfillment_PUB.ORDER_LINE_TBL_TYPE;
167 l_fulfill_electronic_rec JTF_FM_OCM_REQUEST_GRP.FULFILL_ELECTRONIC_REC_TYPE;
168 l_request_type VARCHAR2(32);
169 l_msg_index_out NUMBER;
170 party_id JTF_FM_REQUEST_GRP.G_NUMBER_TBL_TYPE;
171 email JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
172 l_tmp_var1 VARCHAR2(2000);
173 l_tmp_var VARCHAR2(2000);
174
175 i NUMBER;
176 BEGIN
177 l_fulfill_electronic_rec.request_type := 'T';
178 l_fulfill_electronic_rec.template_id := X_TEMPLATE_ID;
179 l_fulfill_electronic_rec.version_id := X_VERSION_ID;
180 l_fulfill_electronic_rec.requestor_id := X_CRM_USER_ID;
181 l_fulfill_electronic_rec.subject := X_SUBJECT;
182
183 --l_fulfill_electronic_rec.party_id(1) := 101; -- Not Required
184 l_fulfill_electronic_rec.email(1) := X_MAIL_ID;
185 l_init_msg_list := FND_API.G_TRUE;
186 l_api_version := 1.0;
187
188 JTF_FM_OCM_REQUEST_GRP.create_fulfillment(
189 p_init_msg_list => l_init_msg_list,
190 p_api_version => l_api_version,
191 p_commit => p_commit,
192 p_order_header_rec => l_order_header_rec,
193 p_order_line_tbl => l_order_line_tbl,
194 p_fulfill_electronic_rec => l_fulfill_electronic_rec,
195 p_request_type => l_request_type,
196 x_return_status => X_RETURN_STATUS,
197 x_msg_count => X_MSG_COUNT,
198 x_msg_data => X_MSG_DATA,
199 x_order_header_rec => y_order_header_rec,
200 x_request_history_id => X_REQUEST_ID
201 );
202
203
204 IF X_RETURN_STATUS IN ('E','U') THEN
205 IF x_msg_count > 1 THEN
206 /* FOR i IN 1..x_msg_count
207 LOOP
208 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
209 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
210 END LOOP;
211 x_msg_data := l_tmp_var1; */
212 FOR i IN 1..x_msg_count
213 LOOP
214 l_tmp_var := fnd_msg_pub.get(i, fnd_api.g_false);
215 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
216 END LOOP;
217 x_msg_data := l_tmp_var1;
218 END IF;
219
220 ELSIF X_RETURN_STATUS ='S' THEN -- if successful, call commit.
221 -- this should resolve issue in bug 3442719
222 COMMIT WORK;
223 END IF;
224
225
226 END send_test_mail;
227
228
229
230
231 PROCEDURE get_attachments
232 (
233 p_version_id IN NUMBER,
234 x_item_id OUT NOCOPY NUMBER,
235 x_item_name OUT NOCOPY VARCHAR2,
236 x_version OUT NOCOPY NUMBER,
237 x_item_description OUT NOCOPY VARCHAR2,
238 x_type_code OUT NOCOPY VARCHAR2,
239 x_type_name OUT NOCOPY VARCHAR2,
240 x_attribute_type_codes OUT NOCOPY T_VARCHAR_100,
241 x_attribute_type_names OUT NOCOPY T_VARCHAR_300,
242 x_attributes OUT NOCOPY T_VARCHAR_4000,
243 x_component_citems OUT NOCOPY T_NUMBER,
244 x_component_attrib_types OUT NOCOPY T_VARCHAR_100,
245 x_component_citem_names OUT NOCOPY T_VARCHAR_300,
246 x_component_owner_ids OUT NOCOPY T_NUMBER,
247 x_component_owner_types OUT NOCOPY T_VARCHAR_100,
248 x_component_sort_orders OUT NOCOPY T_NUMBER,
249 x_return_status OUT NOCOPY VARCHAR2,
250 x_msg_count OUT NOCOPY NUMBER,
251 x_msg_data OUT NOCOPY VARCHAR2
252 )
253 ------------------------------------------------------------------
254 --Created by : kumma, Oracle India ()
255 --Date created: 06-jun-2003
256 --
257 --Purpose: 2853531
258 -- This procedure is used to fetch the attachments/deliverables attached to a particular template
259 --
260 --Known limitations/enhancements and/or remarks:
261 --
262 --Change History:
263 --Who When What
264 -------------------------------------------------------------------
265 IS
266
267 dir_node_id ibc_content_items.directory_node_id%TYPE;
268 dir_node_name ibc_directory_nodes_tl.directory_node_name%TYPE;
269 dir_node_code ibc_directory_nodes_b.directory_node_code%TYPE;
270 item_status ibc_content_items.content_item_status%TYPE;
271 version_status ibc_citem_versions_b.citem_version_status%TYPE;
272 start_date ibc_citem_versions_b.start_date%TYPE;
273 end_date ibc_citem_versions_b.end_date%TYPE;
274 owner_resource_id NUMBER;
275 owner_resource_type VARCHAR2(100);
276 reference_code VARCHAR2(100);
277 trans_required ibc_content_items.translation_required_flag%TYPE;
278 parent_item_id ibc_content_items.parent_item_id%TYPE;
279 locked_by ibc_content_items.locked_by_user_id%TYPE;
280 wd_restricted ibc_content_items.wd_restricted_flag%TYPE;
281 attach_file_id ibc_citem_versions_tl.attachment_file_id%TYPE;
282 attach_file_name ibc_citem_versions_tl.attachment_file_name%TYPE;
283 object_version_number ibc_content_items.object_version_number%TYPE;
284 created_by NUMBER;
285 creation_date DATE;
286 last_updated_by NUMBER;
287 last_update_date DATE;
288
289
290 l_attribute_type_codes JTF_VARCHAR2_TABLE_100 ;
291 l_attribute_type_names JTF_VARCHAR2_TABLE_300 ;
292 l_attributes JTF_VARCHAR2_TABLE_4000 ;
293 l_component_citems JTF_NUMBER_TABLE ;
294 l_component_attrib_types JTF_VARCHAR2_TABLE_100 ;
295 l_component_citem_names JTF_VARCHAR2_TABLE_300 ;
296 l_component_owner_ids JTF_NUMBER_TABLE ;
297 l_component_owner_types JTF_VARCHAR2_TABLE_100 ;
298 l_component_sort_orders JTF_NUMBER_TABLE ;
299
300 l_temp_component_attrib_types IGS_CO_GEN_004.T_VARCHAR_100;
301 l_temp_component_citem_names IGS_CO_GEN_004.T_VARCHAR_300;
302
303 l_tmp_var1 VARCHAR2(2000);
304 l_tmp_var VARCHAR2(2000);
305
306 BEGIN
307
308 l_attribute_type_codes := JTF_VARCHAR2_TABLE_100();
309 l_attribute_type_names := JTF_VARCHAR2_TABLE_300();
310 l_attributes := JTF_VARCHAR2_TABLE_4000();
311 l_component_citems := JTF_NUMBER_TABLE();
312 l_component_attrib_types := JTF_VARCHAR2_TABLE_100();
313 l_component_citem_names := JTF_VARCHAR2_TABLE_300();
314 l_component_owner_ids := JTF_NUMBER_TABLE();
315 l_component_owner_types := JTF_VARCHAR2_TABLE_100();
316 l_component_sort_orders := JTF_NUMBER_TABLE();
317
318 IBC_CITEM_ADMIN_GRP.get_item (
319 p_citem_ver_id => p_version_id, --Version is same as the content_id
320 p_init_msg_list => FND_API.g_true,
321 p_api_version_number => IBC_CITEM_ADMIN_GRP.G_API_VERSION_DEFAULT,
322 x_content_item_id => x_item_id,
323 x_citem_name => x_item_name,
324 x_citem_version => x_version,
325 x_dir_node_id => dir_node_id,
326 x_dir_node_name => dir_node_name,
327 x_dir_node_code => dir_node_code,
328 x_item_status => item_status,
329 x_version_status => version_status,
330 x_citem_description => x_item_description,
331 x_ctype_code => x_type_code,
332 x_ctype_name => x_type_name,
333 x_start_date => start_date,
334 x_end_date => end_date,
335 x_owner_resource_id => owner_resource_id,
336 x_owner_resource_type => owner_resource_type,
337 x_reference_code => reference_code,
338 x_trans_required => trans_required,
339 x_parent_item_id => parent_item_id,
340 x_locked_by => locked_by,
341 x_wd_restricted => wd_restricted,
342 x_attach_file_id => attach_file_id,
343 x_attach_file_name => attach_file_name,
344 x_object_version_number => object_version_number,
345 x_created_by => created_by,
346 x_creation_date => creation_date,
347 x_last_updated_by => last_updated_by,
348 x_last_update_date => last_update_date,
349 x_attribute_type_codes => l_attribute_type_codes,
350 x_attribute_type_names => l_attribute_type_names,
351 x_attributes => l_attributes,
352 x_component_citems => l_component_citems,
353 x_component_attrib_types => l_component_attrib_types,
354 x_component_citem_names => l_component_citem_names,
355 x_component_owner_ids => l_component_owner_ids,
356 x_component_owner_types => l_component_owner_types,
357 x_component_sort_orders => l_component_sort_orders,
358 x_return_status => x_return_status,
359 x_msg_count => x_msg_count,
360 x_msg_data => x_msg_data
361 );
362
363
364
365 IF X_RETURN_STATUS IN ('E','U') THEN
366 IF x_msg_count > 0 THEN
367 /* FOR i IN 1..x_msg_count
368 LOOP
369 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
370 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
371 END LOOP;
372 x_msg_data := l_tmp_var1; */
373 FOR i IN 1..x_msg_count
374 LOOP
375 l_tmp_var := fnd_msg_pub.get(i, fnd_api.g_false);
376 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
377 END LOOP;
378 x_msg_data := l_tmp_var1;
379 END IF;
380 ELSE
381
382 IF l_component_attrib_types.exists(1) THEN
383 FOR count1 IN 1..l_component_attrib_types.count LOOP
384 x_component_attrib_types(count1) := l_component_attrib_types(count1);
385 END LOOP;
386 END IF;
387
388 IF l_component_citem_names.exists(1) THEN
389 FOR count2 IN 1..l_component_citem_names.count LOOP
390 x_component_citem_names(count2) := l_component_citem_names(count2);
391 END LOOP;
392 END IF;
393 END IF;
394
395
396 END get_attachments;
397
398 PROCEDURE get_list_query (
399 p_file_id IN NUMBER,
400 p_query_text OUT NOCOPY VARCHAR2
401 )
402 ------------------------------------------------------------------
403 --Created by : kumma, Oracle India ()
404 --Date created: 06-jun-2003
405 --
406 --Purpose: 2853531
407 -- This procedure is used to fetch the query for a particular dynamic group whose id is passed as a parameter
408 --
409 --Known limitations/enhancements and/or remarks:
410 --
411 --Change History:
412 --Who When What
413 -------------------------------------------------------------------
414 AS
415 l_xmlBlob_loc BLOB;
416 l_rawBuffer RAW(32767);
417 l_amount BINARY_INTEGER;
418 l_chunksize INTEGER;
419 l_totalLen INTEGER;
420 l_offset INTEGER ;
421 l_query VARCHAR2(32000);
422 BEGIN
423 l_amount := 32767;
424 l_offset := 1;
425
426 IF (p_file_id IS NOT NULL) THEN
427 SELECT file_data INTO l_xmlBlob_loc
428 FROM fnd_lobs
429 WHERE file_id = p_file_id;
430
431 l_totalLen := DBMS_LOB.GETLENGTH(l_xmlBlob_loc);
432 l_chunksize := DBMS_LOB.GETCHUNKSIZE(l_xmlBlob_loc);
433
434 IF (l_chunksize < 32767) THEN
435 l_amount := (32767 / l_chunksize) * l_chunksize;
436 END IF;
437
438 l_query := '';
439 WHILE l_totalLen >= l_amount LOOP
440 DBMS_LOB.READ(l_xmlBlob_loc, l_amount, l_offset, l_rawBuffer);
441 --DBMS_LOB.WRITEAPPEND(p_xml_clob_loc, utl_raw.LENGTH(l_rawBuffer), utl_raw.cast_to_varchar2(l_rawBuffer));
442 l_query := l_query || utl_raw.cast_to_varchar2(l_rawBuffer);
443 l_totalLen := l_totalLen - l_amount;
444 l_offset := l_offset + l_amount;
445 END LOOP;
446
447 IF l_totalLen > 0 THEN
448 DBMS_LOB.READ(l_xmlBlob_loc, l_totalLen, l_offset, l_rawBuffer);
449 l_query := l_query || utl_raw.cast_to_varchar2(l_rawBuffer);
450 --DBMS_LOB.WRITEAPPEND(p_xml_clob_loc, utl_raw.LENGTH(l_rawBuffer), utl_raw.cast_to_varchar2(l_rawBuffer));
451 END IF;
452 p_query_text := l_query;
453 END IF;
454
455 -- If Content Item does not have user-defined primitive
456 -- attributes, do nothing.
457
458 END get_list_query;
459
460
461 PROCEDURE create_associations (
462 p_doc_id IN NUMBER,
463 p_assoc_type_codes IN VARCHAR2,
464 p_letter_code IN VARCHAR2,
465 x_return_status OUT NOCOPY VARCHAR2,
466 x_msg_count OUT NOCOPY NUMBER,
467 x_msg_data OUT NOCOPY VARCHAR2
468 )
469 ------------------------------------------------------------------
470 --Created by : ssawhney, Oracle India ()
471 --Date created: 06-may-2004
472 --
473 --Purpose: IBC.C patchset changes bug 3565861
474 -- This procedure is used create associations in OCM for CRM doc_id and OSS letter code
475 -- This will help protect the doc_id of CRM to be deleted from anywhere.
476 --
477 --Known limitations/enhancements and/or remarks:
478 --
479 --Change History:
480 --Who When What
481 -------------------------------------------------------------------
482 AS
483
484 --l_content_item_id JTF_VARCHAR2_TABLE;
485 l_assoc_objects2 JTF_VARCHAR2_TABLE_300 ;
486 l_assoc_objects3 JTF_VARCHAR2_TABLE_300 ;
487 l_assoc_objects4 JTF_VARCHAR2_TABLE_300 ;
488 l_assoc_objects5 JTF_VARCHAR2_TABLE_300 ;
489 l_assoc_type_codes JTF_VARCHAR2_TABLE_100;
490 l_letter_code JTF_VARCHAR2_TABLE_300;
491 l_tmp_var1 VARCHAR2(4000);
492 l_tmp_var VARCHAR2(4000);
493 i number;
494 l_length number;
495
496 BEGIN
497 i:=0;
498 -- initalize all varrays, else we get subscription out of bound or uninitialized collection errors.
499 --l_content_item_id := JTF_NUMBER_TABLE() ;
500 l_assoc_objects2 := JTF_VARCHAR2_TABLE_300() ;
501 l_assoc_objects3 := JTF_VARCHAR2_TABLE_300() ;
502 l_assoc_objects4 := JTF_VARCHAR2_TABLE_300() ;
503 l_assoc_objects5 := JTF_VARCHAR2_TABLE_300() ;
504 l_letter_code := JTF_VARCHAR2_TABLE_300() ;
505 l_assoc_type_codes := JTF_VARCHAR2_TABLE_100() ;
506
507 l_assoc_type_codes.EXTEND;
508 l_letter_code.EXTEND;
509
510 l_letter_code(1) := p_letter_code;
511
512
513 IF p_assoc_type_codes ='SYSTEM' THEN
514 l_assoc_type_codes(1) := 'IGS_SYSTEM' ;
515 ELSIF p_assoc_type_codes ='AD-HOC' THEN
516 l_assoc_type_codes(1) := 'IGS_ADHOC' ;
517 END IF;
518
519
520 IBC_CITEM_ADMIN_GRP.insert_associations(
521 p_content_item_id => p_doc_id
522 ,p_assoc_type_codes => l_assoc_type_codes
523 ,p_assoc_objects1 => l_letter_code
524 ,p_commit => FND_API.G_TRUE
525 ,p_init_msg_list => FND_API.G_TRUE
526 ,p_api_version_number => IBC_CITEM_ADMIN_GRP.G_API_VERSION_DEFAULT
527 ,x_return_status => x_return_status
528 ,x_msg_count => x_msg_count
529 ,x_msg_data => x_msg_data
530 );
531
532
533 IF X_RETURN_STATUS IN ('E','U') THEN
534 IF x_msg_count > 1 THEN
535 FOR i IN 1..x_msg_count
536 LOOP
537 l_tmp_var := fnd_msg_pub.get(i, fnd_api.g_false);
538 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
539 END LOOP;
540 x_msg_data := l_tmp_var1;
541 END IF;
542
543 END IF;
544
545
546 END create_associations;
547
548 END IGS_CO_GEN_004;