[Home] [Help]
PACKAGE BODY: APPS.IGS_CO_SUBMIT
Source
1 PACKAGE BODY igs_co_submit AS
2 /* $Header: IGSCO21B.pls 120.7 2006/01/06 04:12:34 gmaheswa ship $ */
3 /*************************************************************
4 Created By : Paul Cross
5 Date Created on : 11-Sep-2005
6 Purpose : This procedure will build the html call to the Correspondance Workbench form function and write
7 it to the concurrent programs output file.
8 Change History
9 Who When What
10 (reverse chronological order - newest change first)
11 pacross 11-SEP-2005 Implemented code for Correspondance preview and edit fucntionality
12 ***************************************************************/
13 PROCEDURE build_preview_html (p_requestid NUMBER)
14 IS
15 l_function_id NUMBER (15);
16 l_log_request_id NUMBER;
17 l_url VARCHAR2 (2000);
18 l_mouse_over_text VARCHAR2 (2000);
19 l_user_function_name VARCHAR2 (2000);
20 l_temp_string VARCHAR2 (32000);
21 l_count NUMBER;
22
23 CURSOR c_function_details (
24 cp_function_name fnd_form_functions.function_name%TYPE
25 )
26 IS
27 SELECT function_id, user_function_name
28 FROM fnd_form_functions_vl
29 WHERE function_name = cp_function_name;
30
31 CURSOR c_preview_exists (cp_concurrent_req_id NUMBER)
32 IS
33 SELECT COUNT (1)
34 FROM igs_co_prev_reqs
35 WHERE concurrent_request_id = cp_concurrent_req_id;
36 BEGIN
37 -- look up the function id for the correspondence workbench
38
39 --** proc level logging.
40 /* IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
41 IF (l_log_request_id IS NULL) THEN
42 l_log_request_id := fnd_global.conc_request_id;
43 END IF;
44 l_label := 'igs.plsql.igs_co_submit.build_preview_html';
45 l_debug_str := 'JUST entered inside build_preview_html';
46 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_log_request_id));
47 END IF;
48 --** */
49 l_count := 0;
50 OPEN c_preview_exists (p_requestid);
51 FETCH c_preview_exists INTO l_count;
52 CLOSE c_preview_exists;
53
54 IF l_count > 0 THEN
55 -- Get the Form function details
56 OPEN c_function_details ('IGS_CO_WORKBENCH');
57 FETCH c_function_details INTO l_function_id, l_user_function_name;
58 CLOSE c_function_details;
59 -- Update the mouse over text so that it can be used in a URL
60 l_temp_string := REPLACE (l_user_function_name, '''', '\''');
61 l_temp_string := REPLACE (l_temp_string, '"', '`' || '&' || 'quot;');
62 l_user_function_name := REPLACE (l_temp_string, '\\', '\');
63 -- Get the URL
64 l_url :=
65 fnd_run_function.get_run_function_url (
66 l_function_id,
67 fnd_global.resp_appl_id,
68 fnd_global.resp_id,
69 fnd_global.security_group_id,
70 'pRequestId=' || TO_CHAR (p_requestid)
71 );
72 --
73 -- Display the user function name when mousing over the generated link.
74 --
75 l_mouse_over_text := 'onMouseOver="window.status='''
76 || l_user_function_name
77 || '''; return true"';
78 -- Start writing out HTML output
79 fnd_file.put_line (fnd_file.output, '<HTML>');
80 fnd_file.put_line (fnd_file.output, ' <HEAD>');
81 -- write out the auto-refresh URL metatag
82 fnd_file.put (
83 fnd_file.output,
84 ' <META http-equiv="refresh" content="0;URL='
85 );
86 fnd_file.put (fnd_file.output, l_url);
87 fnd_file.put_line (fnd_file.output, '">');
88 -- Finish of header section and start on the body
89 fnd_file.put_line (fnd_file.output, ' </HEAD>');
90 fnd_file.put_line (fnd_file.output, ' <BODY>');
91 -- Write out the translated message for the user to click if the
92 fnd_file.put_line (
93 fnd_file.output,
94 '<a href="'
95 || l_url
96 || '" '
97 || l_mouse_over_text
98 || '>'
99 || fnd_message.get_string (
100 'IGS',
101 'IGS_CO_PREV_VIEW_OUTPUT'
102 ) -- click here if not autotmatically taken to correspondence workbench
103 || '</a>'
104 );
105 -- finialize the html output
106 fnd_file.put_line (fnd_file.output, ' </BODY>');
107 fnd_file.put_line (fnd_file.output, '</HTML>');
108 END IF;
109 /* --** proc level logging.
110 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
111 IF (l_log_request_id IS NULL) THEN
112 l_log_request_id := fnd_global.conc_request_id;
113 END IF;
114 l_label := 'igs.plsql.igs_co_submit.build_preview_html.exit';
115 l_debug_str := 'JUST exiting from build_preview_html';
116 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_log_request_id));
117 END IF;
118 --** */
119 END build_preview_html;
120
121 PROCEDURE submit_correspondence_request (
122 errbuf OUT NOCOPY VARCHAR2,
123 retcode OUT NOCOPY NUMBER,
124 p_map_id IN NUMBER,
125 p_select_type IN VARCHAR2,
126 p_list_id IN NUMBER,
127 p_person_id IN NUMBER,
128 p_override_flag IN VARCHAR2,
129 p_delivery_type IN VARCHAR2,
130 p_destination IN VARCHAR2, -- added as part of bug# 2472250
131 p_dest_fax_number IN VARCHAR2,
132 p_reply_email IN VARCHAR2,
133 p_sender_email IN VARCHAR2,
134 p_cc_email IN VARCHAR2,
135 p_org_unit_id IN NUMBER,
136 p_parameter_1 IN VARCHAR2,
137 p_parameter_2 IN VARCHAR2,
138 p_parameter_3 IN VARCHAR2,
139 p_parameter_4 IN VARCHAR2,
140 p_parameter_5 IN VARCHAR2,
141 p_parameter_6 IN VARCHAR2,
142 p_parameter_8 IN VARCHAR2,
143 p_parameter_7 IN VARCHAR2,
144 p_parameter_9 IN VARCHAR2,
145 p_preview IN VARCHAR2
146 )
147 IS
148 /*************************************************************
149 Created By :Prchandr
150 Date Created on : 05-Feb-2002
151 Purpose : This procedure is the main procedure called aftersubmiiting letters from concurrent jobs.
152 Know limitations, enhancements or remarks
153 Change History
154 Who When What
155 mnade 6/1/2005 FA 157 - 4382371 - Added p_award_prd_cd parameter to corp_post_process
156 Bayadav 24-MAY-2002 Included two system letter codes 'ENADHOC', 'SFADHOC' for adhoc letters as a part of bug 2376434
157 pradhakr 13-Aug-2002 If the delivery type is printer then a new parameter p_destination is added to get the printer name.
158 This is done as part of bug# 2472250.
159 Reversed the order of parameter 8 and 7 due to the way the parameters are defined.
160 kpadiyar 02-Mar-2003 Included nominated_course_cd,appl_sequence_number for letter
161 code 'ADACKMT' for bug 2525936.
162 pkpatel 7-May-2003 Bug 2940810
163 Modified to Bind variable
164 ssawhney 24 Sep 2003 3136817 , validations at time of audit profile setting
165 hreddych 13-oct-2003 Build UK Correspondence Letters
166 gmaheswa 14-nov-2003 Multiple mode of communication for same request is implemented. new parameter fax number is added.
167 ssawhney 3-may-2004 IBC.C patchset changes bug 3565861 + signature of corp_get_letter_type changed
168 ssaleem 02-Jun-2004 extended header usage - Validation added to check for Email delivery option when reply cc or sender
169 Email is given
170 ssaleem 09-SEP-2004 3630073. Added p_org_unit_id as a new parameter
171 pacross 08-SEP-2005 Added preview flag for correspondance preview and edit.
172 gmaheswa 5-Jan-2004 Bug 4869737 Added a call to SET_ORG_ID to disable OSS for R12.
173 ***************************************************************/
174 l_sys_ltr_code igs_co_mapping.sys_ltr_code%TYPE;
175 l_request_id igs_co_ou_co_ref.request_id%TYPE;
176 l_no_of_repeats igs_co_mapping.repeat_times%TYPE;
177 l_elapsed_days igs_co_mapping.elapsed_days%TYPE;
178 l_document_id igs_co_mapping.document_id%TYPE;
179 l_letter_type igs_co_mapping.doc_code%TYPE;
180 l_request_status VARCHAR2 (100);
181 l_send_letter VARCHAR2 (10);
182 l_sql_stmt VARCHAR2 (32767);
183 -- these variables are required becuase when it is FAM related, Admission Application detials will be Null and when
184 -- the letter is NON FAM, then Adm Application details are needed and Award cal type and seq num will be Null.
185 l_adm_appl_number igs_co_interac_hist.adm_application_number%TYPE;
186 l_nominated_course_cd igs_co_interac_hist.nominated_course_cd%TYPE;
187 l_appl_sequence_number igs_co_interac_hist.ci_sequence_number%TYPE;
188 l_awd_ci_seq_number igf_sl_disb_ltr_v.ci_sequence_number%TYPE;
189 l_awd_cal_type igf_sl_disb_ltr_v.ci_cal_type%TYPE;
190 l_gen_request_id igs_co_ou_co_ref.request_id%TYPE;
191 l_fulfillment_req NUMBER;
192 l_crm_user_id NUMBER;
193 l_email_address_dy hz_parties.email_address%TYPE;
194 l_person_id_dy igs_pe_person.person_id%TYPE;
195 l_adm_appl_number_dy igs_co_interac_hist.adm_application_number%TYPE;
196 l_nominated_course_cd_dy igs_co_interac_hist.nominated_course_cd%TYPE;
197 l_appl_sequence_number_dy igs_co_interac_hist.ci_sequence_number%TYPE;
198 l_panel_code_dy igs_ad_interview_letters_v.panel_code%TYPE;
199 l_award_year_dy igf_sl_disb_ltr_v.award_year%TYPE;
200 l_exception VARCHAR2 (1);
201 l_retcode NUMBER (1);
202 l_errbuf VARCHAR2 (1000);
203 -- Created new parameters as part of bug# 2472250
204 l_printer_name VARCHAR2 (240);
205 l_address_found VARCHAR2 (1);
206 l_delivery_type VARCHAR2 (30);
207
208 -- Added cursor to print the person number in case of no email for bug 2742586
209 CURSOR c_person_number (pa_person_id NUMBER)
210 IS
211 SELECT person_number, full_name
212 FROM igs_pe_person_base_v
213 WHERE person_id = pa_person_id;
214
215 l_person_number igs_pe_person_base_v.person_number%TYPE;
216 l_full_name igs_pe_person_base_v.full_name%TYPE;
217 l_cursor_id NUMBER (15);
218 l_num_of_rows NUMBER (15);
219 l_dsql_debug VARCHAR2 (32767);
220 l_person_processed NUMBER (10) := 0;
221 l_resp_id NUMBER;
222 l_resp_appl_id NUMBER;
223 l_login_user_id NUMBER; --user who has logged in
224 CURSOR c_valid_resource (cp_user_id fnd_user.user_id%TYPE)
225 IS
226 SELECT 1
227 FROM jtf_rs_resource_extns
228 WHERE user_id = cp_user_id;
229
230 CURSOR c_lkup_channel (cp_sys_ltr_code VARCHAR2)
231 IS
232 SELECT description
233 FROM igs_lookup_values
234 WHERE lookup_code = cp_sys_ltr_code
235 AND lookup_type = 'IGS_CO_DEL_CHANNEL';
236
237 CURSOR c_avail_del_channel (
238 cp_item_id ibc_content_items.content_item_id%TYPE,
239 cp_node_id ibc_ctype_group_nodes.directory_node_id%TYPE
240 )
241 IS
242 SELECT 1
243 FROM ibc_content_items citem, ibc_ctype_group_nodes ctg
244 WHERE citem.content_item_id = cp_item_id
245 AND citem.content_type_code = ctg.content_type_code
246 AND ctg.directory_node_id = cp_node_id;
247
248 l_user NUMBER;
249 l_fax_number VARCHAR2 (240);
250 l_count NUMBER (10);
251 l_prog_label VARCHAR2 (500);
252 l_log_request_id NUMBER;
253 l_label VARCHAR2 (4000);
254 l_debug_str VARCHAR2 (4000);
255 l_version_id NUMBER;
256 l_del_desc igs_lookups_view.description%TYPE;
257 --l_enable_log BOOLEAN
258
259 --l_prog_label := 'igs.plsql.igs_ad_imp_002.validate_oss_ext_attr';
260
261
262 -- Keep a record that preview batch request row has been stored in sticky variable.
263 -- PACROSS - 27-SEP-2005
264 l_preview_batch_stored BOOLEAN;
265 BEGIN
266 igs_ge_gen_003.set_org_id;
267
268 l_fulfillment_req := fnd_profile.VALUE ('IGS_CO_FUL_SERVER_ID');
269 l_crm_user_id := fnd_profile.VALUE ('IGS_CO_CRM_USER_ID');
270 l_login_user_id := fnd_global.user_id;
271 l_prog_label := 'igs.plsql.igs_co_submit';
272 --l_enable_log := igs_ad_imp_001.g_enable_log;
273
274 l_address_found := 'N';
275 l_exception := 'N';
276 -- Keep a record that preview batch request row has been stored.
277 -- PACROSS - 29-SEP-2005
278 l_preview_batch_stored := FALSE ;
279 igs_co_process.corp_check_request_status (
280 errbuf => l_errbuf,
281 retcode => l_retcode,
282 p_person_id => NULL,
283 p_document_id => NULL,
284 p_application_id => NULL,
285 p_course_cd => NULL,
286 p_adm_seq_no => NULL,
287 p_awd_cal_type => NULL,
288 p_awd_seq_no => NULL,
289 p_elapsed_days => NULL,
290 p_no_of_repeats => NULL,
291 p_sys_ltr_code => NULL
292 );
293 l_delivery_type := p_delivery_type;
294
295 IF p_reply_email IS NOT NULL
296 OR p_sender_email IS NOT NULL
297 OR p_cc_email IS NOT NULL THEN
298 IF INSTR (l_delivery_type, 'EMAIL') <= 0 THEN -- Email option not chosen
299 fnd_message.set_name ('IGS', 'IGS_CO_EMAIL_REQ');
300 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
301 fnd_msg_pub.ADD;
302 RAISE fnd_api.g_exc_error;
303 END IF;
304 END IF;
305
306 IF INSTR (l_delivery_type, 'PRINTER') > 0 THEN
307 --l_delivery_type := 'PRINTER';
308 IF p_destination IS NOT NULL THEN
309 l_printer_name := p_destination;
310 ELSIF fnd_profile.VALUE ('IGS_CO_DESTINATION_NAME') IS NOT NULL THEN
311 l_printer_name := fnd_profile.VALUE ('IGS_CO_DESTINATION_NAME');
312 ELSE
313 fnd_message.set_name ('IGS', 'IGS_CO_DEST_INF_REQ');
314 fnd_message.set_token ('DELTYPE', l_delivery_type);
315 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
316 fnd_msg_pub.ADD;
317 RAISE fnd_api.g_exc_error;
318 END IF;
319 END IF;
320
321 IF INSTR (l_delivery_type, 'FAX') > 0 THEN
322 IF p_dest_fax_number IS NULL THEN
323 IF fnd_profile.VALUE ('IGS_CO_FAX_DESTINATION_NUMBER') IS NOT NULL THEN
324 l_fax_number :=
325 fnd_profile.VALUE (
326 'IGS_CO_FAX_DESTINATION_NUMBER'
327 );
328 ELSE
329 fnd_message.set_name ('IGS', 'IGS_CO_DEST_INF_REQ');
330 fnd_message.set_token ('DELTYPE', l_delivery_type);
331 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
332 fnd_msg_pub.ADD;
333 RAISE fnd_api.g_exc_error;
334 END IF;
335 ELSE
336 l_fax_number := p_dest_fax_number;
337 END IF;
338 END IF;
339
340 --** proc level logging.
341 IF fnd_log.test (fnd_log.level_procedure, l_prog_label) THEN
342 IF (l_log_request_id IS NULL) THEN
343 l_log_request_id := fnd_global.conc_request_id;
344 END IF;
345
346 l_label :=
347 'igs.plsql.igs_co_submit.submit_correspondence_request.deliverycheck';
348 l_debug_str := 'Delivery Type :'
349 || l_delivery_type
350 || ' Printer :'
351 || l_printer_name
352 || ' Fax :'
353 || l_fax_number;
354 fnd_log.string_with_context (
355 fnd_log.level_procedure,
356 l_label,
357 l_debug_str,
358 NULL,
359 NULL,
360 NULL,
361 NULL,
362 NULL,
363 TO_CHAR (l_log_request_id)
364 );
365 END IF;
366
367 --**
368
369 -- ssawhney adding code fix for bug 3136817.
370 -- need to validate AUDIT enabling and decide on the CRM user to be passed.
371
372 IF (fnd_profile.value_specific (
373 'SIGNONAUDIT:LEVEL',
374 l_login_user_id,
375 l_resp_id,
376 l_resp_appl_id
377 ) <> 'A'
378 ) THEN
379 -- this means audit is enabled.
380 -- check if the Login User, is a valid CRM resource.
381
382 OPEN c_valid_resource (l_login_user_id);
383 FETCH c_valid_resource INTO l_user;
384 CLOSE c_valid_resource;
385
386 IF l_user IS NULL THEN
387 -- log a message that the current user is not a valid CRM resource
388 fnd_message.set_name ('IGS', 'IGS_CO_INVALID_JTF_RES');
389 fnd_file.put_line (fnd_file.LOG, fnd_message.get ());
390 fnd_file.put_line (fnd_file.LOG, ' ');
391 retcode := 2;
392 RETURN;
393 ELSE
394 -- do not need to pass server id.
395 -- pass the CRM user as the current logged in person.
396 l_crm_user_id := l_login_user_id;
397 l_fulfillment_req := NULL;
398 END IF;
399 -- if all the above checks pass
400 END IF;
401
402 IF (l_crm_user_id IS NULL) THEN
403 fnd_message.set_name ('JTF', 'JTF_FM_API_MISSING_USER_ID');
404 fnd_file.put_line (fnd_file.LOG, fnd_message.get ());
405 fnd_file.put_line (fnd_file.LOG, ' ');
406 retcode := 0;
407 RETURN;
408 END IF;
409
410 --** proc level logging.
411 IF fnd_log.test (fnd_log.level_procedure, l_prog_label) THEN
412 IF (l_log_request_id IS NULL) THEN
413 l_log_request_id := fnd_global.conc_request_id;
414 END IF;
415
416 l_label :=
417 'igs.plsql.igs_co_submit.submit_correspondence_request.signonaudit';
418 l_debug_str := 'CRM User :' || l_crm_user_id;
419 fnd_log.string_with_context (
420 fnd_log.level_procedure,
421 l_label,
422 l_debug_str,
423 NULL,
424 NULL,
425 NULL,
426 NULL,
427 NULL,
428 TO_CHAR (l_log_request_id)
429 );
430 END IF;
431
432 --**
433
434 -- get the Doc Id , sys ltr code, letter type as out NOCOPY parameters from a call.
435 igs_co_process.corp_get_letter_type (
436 p_map_id => p_map_id,
437 p_document_id => l_document_id,
438 p_sys_ltr_code => l_sys_ltr_code,
439 p_letter_type => l_letter_type,
440 p_version_id => l_version_id --ssawhney
441 );
442
443 --** proc level logging.
444 IF fnd_log.test (fnd_log.level_procedure, l_prog_label) THEN
445 IF (l_log_request_id IS NULL) THEN
446 l_log_request_id := fnd_global.conc_request_id;
447 END IF;
448
449 l_label :=
450 'igs.plsql.igs_co_submit.submit_correspondence_request.paramdetails';
451 l_debug_str := 'Map ID :'
452 || p_map_id
453 || 'Doc id :'
454 || l_document_id
455 || ' Version :'
456 || l_version_id;
457 fnd_log.string_with_context (
458 fnd_log.level_procedure,
459 l_label,
460 l_debug_str,
461 NULL,
462 NULL,
463 NULL,
464 NULL,
465 NULL,
466 TO_CHAR (l_log_request_id)
467 );
468 END IF;
469
470 --**
471
472 -- once we get the doc id, check if the passed delivery_type combination is
473 -- valid as per new IBC.C logic. Email is dirnode=33, fax is dirnode=32, printer is dirnode=34
474
475 IF l_document_id IS NOT NULL THEN
476 IF INSTR (l_delivery_type, 'PRINTER') > 0 THEN
477 OPEN c_avail_del_channel (l_document_id, 34);
478 FETCH c_avail_del_channel INTO l_count;
479 OPEN c_lkup_channel ('PRINTER');
480 FETCH c_lkup_channel INTO l_del_desc;
481 CLOSE c_lkup_channel;
482
483 IF c_avail_del_channel%NOTFOUND THEN
484 fnd_message.set_name ('IGS', 'IGS_CO_UNAVAIL_DEL_TYPE');
485 fnd_message.set_token ('CHANNEL ', l_del_desc);
486 fnd_file.put_line (fnd_file.LOG, fnd_message.get ());
487 retcode := 2;
488 RETURN;
489 END IF;
490
491 CLOSE c_avail_del_channel;
492 END IF;
493
494 IF INSTR (l_delivery_type, 'FAX') > 0 THEN
495 OPEN c_avail_del_channel (l_document_id, 32);
496 FETCH c_avail_del_channel INTO l_count;
497 OPEN c_lkup_channel ('FAX');
498 FETCH c_lkup_channel INTO l_del_desc;
499 CLOSE c_lkup_channel;
500
501 IF c_avail_del_channel%NOTFOUND THEN
502 fnd_message.set_name ('IGS', 'IGS_CO_UNAVAIL_DEL_TYPE');
503 fnd_message.set_token ('CHANNEL ', l_del_desc);
504 fnd_file.put_line (fnd_file.LOG, fnd_message.get ());
505 retcode := 2;
506 RETURN;
507 END IF;
508
509 CLOSE c_avail_del_channel;
510 END IF;
511
512 IF INSTR (l_delivery_type, 'EMAIL') > 0 THEN
513 OPEN c_avail_del_channel (l_document_id, 33);
514 FETCH c_avail_del_channel INTO l_count;
515 OPEN c_lkup_channel ('EMAIL');
516 FETCH c_lkup_channel INTO l_del_desc;
517 CLOSE c_lkup_channel;
518
519 IF c_avail_del_channel%NOTFOUND THEN
520 fnd_message.set_name ('IGS', 'IGS_CO_UNAVAIL_DEL_TYPE');
521 fnd_message.set_token ('CHANNEL ', l_del_desc);
522 fnd_file.put_line (fnd_file.LOG, fnd_message.get ());
523 retcode := 2;
524 RETURN;
525 END IF;
526
527 CLOSE c_avail_del_channel;
528 END IF; -- endif for instr
529 END IF; --end if l_document_id;
530 -- validate all the parameters and their combinations
531 IF l_sys_ltr_code NOT IN ('FAAWARD', 'FAMISTM', 'FADISBT') THEN
532 l_exception := 'N';
533 igs_co_process.corp_validate_parameters (
534 p_sys_ltr_code => l_sys_ltr_code,
535 p_document_id => l_document_id,
536 p_select_type => p_select_type,
537 p_list_id => p_list_id,
538 p_person_id => p_person_id,
539 p_parameter_1 => p_parameter_1,
540 p_parameter_2 => p_parameter_2,
541 p_parameter_3 => p_parameter_3,
542 p_parameter_4 => p_parameter_4,
543 p_parameter_5 => p_parameter_5,
544 p_parameter_6 => p_parameter_6,
545 p_parameter_7 => p_parameter_7,
546 p_parameter_8 => p_parameter_8,
547 p_parameter_9 => p_parameter_9,
548 p_override_flag => p_override_flag,
549 p_delivery_type => l_delivery_type,
550 p_exception => l_exception
551 );
552
553 IF l_exception = 'Y' THEN
554 retcode := 2;
555 RETURN;
556 END IF;
557 END IF;
558
559 -- for FAM letter of type System only (not even Adhoc)
560 IF l_sys_ltr_code IN ('FAAWARD', 'FAMISTM', 'FADISBT')
561 AND (l_letter_type = 'SYSTEM') THEN
562 l_exception := 'N';
563 igf_aw_gen_004.corp_pre_process (
564 p_document_id => l_document_id,
565 p_select_type => p_select_type,
566 p_sys_ltr_code => l_sys_ltr_code,
567 p_list_id => p_list_id,
568 p_letter_type => l_letter_type,
569 p_person_id => p_person_id,
570 p_parameter_1 => p_parameter_1,
571 p_parameter_2 => p_parameter_2,
572 p_parameter_3 => p_parameter_3,
573 p_parameter_4 => p_parameter_4,
574 p_parameter_5 => p_parameter_5,
575 p_parameter_6 => p_parameter_6,
576 p_parameter_7 => p_parameter_7,
577 p_parameter_8 => p_parameter_8,
578 p_parameter_9 => p_parameter_9,
579 p_sql_stmt => l_sql_stmt,
580 p_flag => p_override_flag,
581 p_exception => l_exception
582 );
583
584 IF l_exception = 'Y' THEN
585 retcode := 2;
586 RETURN;
587 END IF;
588
589 -- since it is FAM letter, ADM related fields should be made Null
590 l_appl_sequence_number := NULL;
591 l_adm_appl_number := NULL;
592 l_nominated_course_cd := NULL;
593 l_awd_cal_type := RTRIM (SUBSTR (p_parameter_1, 1, 10));
594 l_awd_ci_seq_number := TO_NUMBER (RTRIM (SUBSTR (p_parameter_1, 11)));
595 END IF;
596
597 --** proc level logging.
598 IF fnd_log.test (fnd_log.level_procedure, l_prog_label) THEN
599 IF (l_log_request_id IS NULL) THEN
600 l_log_request_id := fnd_global.conc_request_id;
601 END IF;
602
603 l_label :=
604 'igs.plsql.igs_co_submit.submit_correspondence_request.FACaldetails';
605 l_debug_str := 'FA Calendar :'
606 || l_awd_cal_type
607 || '-'
608 || l_awd_ci_seq_number;
609 fnd_log.string_with_context (
610 fnd_log.level_procedure,
611 l_label,
612 l_debug_str,
613 NULL,
614 NULL,
615 NULL,
616 NULL,
617 NULL,
618 TO_CHAR (l_log_request_id)
619 );
620 END IF;
621
622 --**
623
624 --
625 IF p_override_flag = 'Y'
626 OR (l_sys_ltr_code IN ('FAAWARD', 'FAMISTM', 'FADISBT')) THEN
627 -- if letter code is NOT FAM related then, we need to call the 'build sql stmt' from igs_co_process_pkg
628 -- otherwise if it FAM related, we need to directly execute the SQL stmt received in pre_process proc.
629 IF (l_sys_ltr_code NOT IN ('FAAWARD', 'FAMISTM', 'FADISBT')) THEN
630 -- call needed only for NON FAM letters and Adhoc Letters
631 l_exception := 'N';
632 igs_co_process.corp_build_sql_stmt (
633 p_document_id => l_document_id,
634 p_select_type => p_select_type,
635 p_sys_ltr_code => l_sys_ltr_code,
636 p_list_id => p_list_id,
637 p_letter_type => l_letter_type,
638 p_person_id => p_person_id,
639 p_parameter_1 => p_parameter_1,
640 p_parameter_2 => p_parameter_2,
641 p_parameter_3 => p_parameter_3,
642 p_parameter_4 => p_parameter_4,
643 p_parameter_5 => p_parameter_5,
644 p_parameter_6 => p_parameter_6,
645 p_parameter_7 => p_parameter_7,
646 p_parameter_8 => p_parameter_8,
647 p_parameter_9 => p_parameter_9,
648 p_sql_stmt => l_sql_stmt,
649 p_exception => l_exception
650 );
651
652 IF l_exception = 'Y' THEN
653 retcode := 2;
654 RETURN;
655 END IF;
656
657 -- since it is Non FAM letter, FAM related fields should be made Null
658 l_awd_cal_type := NULL;
659 l_awd_ci_seq_number := NULL;
660 END IF;
661
662 /* This will print the Dynamic SQL statement prepared. Can be uncommented when testing.
663 Igs_Ad_Imp_001.logDetail('l_sql_stmt :'||l_sql_stmt);
664 */
665 --** proc level logging.
666 IF fnd_log.test (fnd_log.level_procedure, l_prog_label) THEN
667 IF (l_log_request_id IS NULL) THEN
668 l_log_request_id := fnd_global.conc_request_id;
669 END IF;
670
671 l_label :=
672 'igs.plsql.igs_co_submit.submit_correspondence_request.buildsql';
673 l_debug_str := 'Build Sql :' || l_sql_stmt;
674 fnd_log.string_with_context (
675 fnd_log.level_procedure,
676 l_label,
677 l_debug_str,
678 NULL,
679 NULL,
680 NULL,
681 NULL,
682 NULL,
683 TO_CHAR (l_log_request_id)
684 );
685 END IF;
686
687 --**
688
689
690 IF l_sql_stmt IS NOT NULL THEN
691 --Included last two system letter codes as a part of bug 2376434
692
693 l_cursor_id := DBMS_SQL.open_cursor;
694 fnd_dsql.set_cursor (l_cursor_id);
695 DBMS_SQL.parse (l_cursor_id, l_sql_stmt, DBMS_SQL.native);
696 fnd_dsql.do_binds;
697
698 IF l_sys_ltr_code IN ('ADRESID',
699 'FAADHOC',
700 'ADADHOC',
701 'GENERIC',
702 'ENADHOC',
703 'SFADHOC'
704 ) THEN
705 DBMS_SQL.define_column (
706 l_cursor_id,
707 1,
708 l_email_address_dy,
709 2000
710 );
711 DBMS_SQL.define_column (l_cursor_id, 2, l_person_id_dy);
712 ELSIF l_sys_ltr_code = 'ADACKMT' THEN
713 DBMS_SQL.define_column (
714 l_cursor_id,
715 1,
716 l_email_address_dy,
717 2000
718 );
719 DBMS_SQL.define_column (l_cursor_id, 2, l_person_id_dy);
720 DBMS_SQL.define_column (l_cursor_id, 3, l_adm_appl_number_dy);
721 DBMS_SQL.define_column (
722 l_cursor_id,
723 4,
724 l_nominated_course_cd_dy,
725 6
726 );
727 DBMS_SQL.define_column (
728 l_cursor_id,
729 5,
730 l_appl_sequence_number_dy
731 );
732 ELSIF l_sys_ltr_code IN ('FAAWARD', 'FAMISTM', 'FADISBT') THEN
733 DBMS_SQL.define_column (
734 l_cursor_id,
735 1,
736 l_email_address_dy,
737 2000
738 );
739 DBMS_SQL.define_column (l_cursor_id, 2, l_person_id_dy);
740 DBMS_SQL.define_column (l_cursor_id, 3, l_award_year_dy, 17);
741 ELSIF l_sys_ltr_code = 'ADINTRW' THEN
742 DBMS_SQL.define_column (
743 l_cursor_id,
744 1,
745 l_email_address_dy,
746 2000
747 );
748 DBMS_SQL.define_column (l_cursor_id, 2, l_person_id_dy);
749 DBMS_SQL.define_column (l_cursor_id, 3, l_adm_appl_number_dy);
750 DBMS_SQL.define_column (
751 l_cursor_id,
752 4,
753 l_nominated_course_cd_dy,
754 6
755 );
756 DBMS_SQL.define_column (
757 l_cursor_id,
758 5,
759 l_appl_sequence_number_dy
760 );
761 DBMS_SQL.define_column (l_cursor_id, 6, l_panel_code_dy, 200);
762 ELSE
763 DBMS_SQL.define_column (
764 l_cursor_id,
765 1,
766 l_email_address_dy,
767 2000
768 );
769 DBMS_SQL.define_column (l_cursor_id, 2, l_person_id_dy);
770 DBMS_SQL.define_column (l_cursor_id, 3, l_adm_appl_number_dy);
771 DBMS_SQL.define_column (
772 l_cursor_id,
773 4,
774 l_nominated_course_cd_dy,
775 6
776 );
777 DBMS_SQL.define_column (
778 l_cursor_id,
779 5,
780 l_appl_sequence_number_dy
781 );
782 END IF;
783
784 l_num_of_rows := DBMS_SQL.EXECUTE (l_cursor_id);
785
786 /*This will print the Dynamic SQL statement prepared. Can be uncommented when testing.
787 l_dsql_debug := fnd_dsql.get_text(TRUE);
788 Igs_Ad_Imp_001.logDetail('l_dsql_debug :'||l_dsql_debug);
789 */
790 LOOP
791 IF DBMS_SQL.fetch_rows (l_cursor_id) > 0 THEN
792 l_person_processed := l_person_processed + 1;
793
794 IF l_sys_ltr_code IN ('ADRESID',
795 'FAADHOC',
796 'ADADHOC',
797 'GENERIC',
798 'ENADHOC',
799 'SFADHOC'
800 ) THEN
801 DBMS_SQL.column_value (
802 l_cursor_id,
803 1,
804 l_email_address_dy
805 );
806 DBMS_SQL.column_value (l_cursor_id, 2, l_person_id_dy);
807 ELSIF l_sys_ltr_code = 'ADACKMT' THEN
808 DBMS_SQL.column_value (
809 l_cursor_id,
810 1,
811 l_email_address_dy
812 );
813 DBMS_SQL.column_value (l_cursor_id, 2, l_person_id_dy);
814 DBMS_SQL.column_value (
815 l_cursor_id,
816 3,
817 l_adm_appl_number_dy
818 );
819 DBMS_SQL.column_value (
820 l_cursor_id,
821 4,
822 l_nominated_course_cd_dy
823 );
824 DBMS_SQL.column_value (
825 l_cursor_id,
826 5,
827 l_appl_sequence_number_dy
828 );
829 ELSIF l_sys_ltr_code IN ('FAAWARD', 'FAMISTM', 'FADISBT') THEN
830 DBMS_SQL.column_value (
831 l_cursor_id,
832 1,
833 l_email_address_dy
834 );
835 DBMS_SQL.column_value (l_cursor_id, 2, l_person_id_dy);
836 DBMS_SQL.column_value (l_cursor_id, 3, l_award_year_dy);
837 ELSIF l_sys_ltr_code = 'ADINTRW' THEN
838 DBMS_SQL.column_value (
839 l_cursor_id,
840 1,
841 l_email_address_dy
842 );
843 DBMS_SQL.column_value (l_cursor_id, 2, l_person_id_dy);
844 DBMS_SQL.column_value (
845 l_cursor_id,
846 3,
847 l_adm_appl_number_dy
848 );
849 DBMS_SQL.column_value (
850 l_cursor_id,
851 4,
852 l_nominated_course_cd_dy
853 );
854 DBMS_SQL.column_value (
855 l_cursor_id,
856 5,
857 l_appl_sequence_number_dy
858 );
859 DBMS_SQL.column_value (l_cursor_id, 6, l_panel_code_dy);
860 ELSE
861 DBMS_SQL.column_value (
862 l_cursor_id,
863 1,
864 l_email_address_dy
865 );
866 DBMS_SQL.column_value (l_cursor_id, 2, l_person_id_dy);
867 DBMS_SQL.column_value (
868 l_cursor_id,
869 3,
870 l_adm_appl_number_dy
871 );
872 DBMS_SQL.column_value (
873 l_cursor_id,
874 4,
875 l_nominated_course_cd_dy
876 );
877 DBMS_SQL.column_value (
878 l_cursor_id,
879 5,
880 l_appl_sequence_number_dy
881 );
882 END IF;
883 ELSE
884 EXIT;
885 END IF;
886
887 l_address_found := 'N';
888
889 IF INSTR (l_delivery_type, 'EMAIL') > 0
890 AND l_email_address_dy IS NOT NULL THEN
891 l_address_found := 'Y';
892 END IF;
893
894 -- executing Dynamic SQL. Either received from build_sql_stmt procedure (for Admissions) or Pre_Process (for FAM related letters)
895 -- do not submit a request for students without mail Ids.
896 IF INSTR (l_delivery_type, 'EMAIL') > 0 AND l_address_found =
897 'N' THEN
898 OPEN c_person_number (l_person_id_dy);
899 FETCH c_person_number INTO l_person_number, l_full_name;
900 CLOSE c_person_number;
901 -- log a message that Email ID is Null
902 fnd_message.set_name ('IGS', 'IGS_CO_PRSN_NO_EMAIL');
903 fnd_message.set_token (
904 'STUDENT',
905 l_person_number || ' - ' || l_full_name
906 );
907 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
908 ELSE
909 IF l_sys_ltr_code IN ('FAAWARD', 'FAMISTM', 'FADISBT')
910 AND (l_letter_type = 'SYSTEM') THEN
911 --** exec level logging.
912
913 --**
914 igs_co_process.corp_submit_fulfil_request (
915 p_letter_type => l_letter_type,
916 p_person_id => l_person_id_dy,
917 p_sys_ltr_code => l_sys_ltr_code,
918 p_email_address => l_email_address_dy,
919 p_content_id => l_document_id,
920 p_adm_appl_number => l_adm_appl_number_dy,
921 p_nominated_course_cd => l_nominated_course_cd_dy,
922 p_appl_sequence_number => l_appl_sequence_number_dy,
923 p_award_year => p_parameter_1,
924 p_fulfillment_req => l_fulfillment_req,
925 p_crm_user_id => l_crm_user_id,
926 p_media_type => l_delivery_type,
927 p_destination => l_printer_name, -- Added the parameter p_destination which takes the value of the printer name. Bug# 2472250
928 p_fax_number => l_fax_number,
929 p_request_id => l_request_id,
930 p_request_status => l_request_status,
931 p_reply_email => p_reply_email,
932 p_sender_email => p_sender_email,
933 p_cc_email => p_cc_email,
934 p_org_unit_id => p_org_unit_id,
935 p_preview => p_preview,
936 p_awd_cal_type => l_awd_cal_type,
937 p_awd_ci_seq_number => l_awd_ci_seq_number,
938 p_awd_prd_cd => p_parameter_2
939 );
940
941 IF NOT igs_co_process.l_corp_submit_fulfil_request THEN
942 IF p_preview = 'Y' THEN
943 IF NOT l_preview_batch_stored THEN
944 -- Store away request values so they can be used later to fulfill the request after preview.
945 -- PACROSS - 11-SEP-2005
946 BEGIN
947 INSERT INTO igs_co_prv_bch_reqs
948 (concurrent_request_id,
949 master_content_id,
950 master_content_type_code,
951 master_version_id,
952 master_language,
953 master_media_type_code,
954 master_template_updated_flag,
955 batch_cancelled_flag,
956 object_version_number, created_by,
957 creation_date, last_updated_by,
958 last_update_login,
959 last_update_date)
960 VALUES (fnd_global.conc_request_id,
961 l_document_id,
962 l_letter_type,
963 l_version_id,
964 USERENV ('LANG'),
965 l_delivery_type,
966 'N',
967 'N',
968 1, fnd_global.user_id,
969 SYSDATE, fnd_global.user_id,
970 NULL,
971 SYSDATE);
972
973 l_preview_batch_stored := TRUE ;
974 EXCEPTION --added by svadde on 10/31/2005
975 WHEN OTHERS THEN
976 IF fnd_log.test (fnd_log.level_procedure, l_prog_label) THEN
977 IF (l_log_request_id IS NULL) THEN
978 l_log_request_id := fnd_global.conc_request_id;
979 END IF;
980
981 l_label :=
982 'igs.plsql.igs_co_submit.submit_correspondence_request.exception';
983 l_debug_str := 'Insert into igs_co_prv_bch_reqs failed with :' || SQLERRM;
984 fnd_log.string_with_context (
985 fnd_log.level_procedure,
986 l_label,
987 l_debug_str,
988 NULL,
989 NULL,
990 NULL,
991 NULL,
992 NULL,
993 TO_CHAR (l_log_request_id)
994 );
995 END IF;
996 app_exception.RAISE_EXCEPTION;
997 END;
998 END IF;
999 ELSE
1000 -- post-process the request as per pre-preview and edit
1001
1002 igs_co_process.corp_post_process (
1003 p_person_id => l_person_id_dy,
1004 p_request_id => l_request_id,
1005 p_document_id => l_document_id,
1006 p_sys_ltr_code => l_sys_ltr_code,
1007 p_document_type => l_letter_type,
1008 p_adm_appl_number => l_adm_appl_number_dy,
1009 p_nominated_course_cd => l_nominated_course_cd_dy,
1010 p_appl_seq_number => l_appl_sequence_number_dy,
1011 p_award_year => p_parameter_1,
1012 p_awd_cal_type => l_awd_cal_type,
1013 p_awd_ci_seq_number => l_awd_ci_seq_number,
1014 p_delivery_type => p_delivery_type,
1015 p_version_id => l_version_id,
1016 p_award_prd_cd => p_parameter_2
1017 );
1018 END IF;
1019 END IF;
1020 ELSE
1021 --** exec level logging.
1022
1023 --**
1024 igs_co_process.corp_submit_fulfil_request (
1025 p_letter_type => l_letter_type,
1026 p_person_id => l_person_id_dy,
1027 p_sys_ltr_code => l_sys_ltr_code,
1028 p_email_address => l_email_address_dy,
1029 p_content_id => l_document_id,
1030 p_adm_appl_number => l_adm_appl_number_dy,
1031 p_nominated_course_cd => l_nominated_course_cd_dy,
1032 p_appl_sequence_number => l_appl_sequence_number_dy,
1033 p_award_year => p_parameter_1,
1034 p_fulfillment_req => l_fulfillment_req,
1035 p_crm_user_id => l_crm_user_id,
1036 p_media_type => l_delivery_type,
1037 p_destination => l_printer_name, -- Added the parameter p_destination which takes the value of the printer name. Bug# 2472250
1038 p_fax_number => l_fax_number,
1039 p_reply_days => p_parameter_8,
1040 p_panel_code => p_parameter_5,
1041 p_request_id => l_request_id,
1042 p_request_status => l_request_status,
1043 p_reply_email => p_reply_email,
1044 p_sender_email => p_sender_email,
1045 p_cc_email => p_cc_email,
1046 p_org_unit_id => p_org_unit_id,
1047 p_preview => p_preview,
1048 p_awd_cal_type => l_awd_cal_type,
1049 p_awd_ci_seq_number => l_awd_ci_seq_number,
1050 p_awd_prd_cd => p_parameter_2
1051 );
1052
1053 IF NOT igs_co_process.l_corp_submit_fulfil_request THEN
1054 IF p_preview = 'Y' THEN
1055 IF NOT l_preview_batch_stored THEN
1056 -- Store away request values so they can be used later to fulfill the request after preview.
1057 -- PACROSS - 11-SEP-2005
1058 BEGIN
1059 INSERT INTO igs_co_prv_bch_reqs
1060 (concurrent_request_id,
1061 master_content_id,
1062 master_content_type_code,
1063 master_version_id,
1064 master_language,
1065 master_media_type_code,
1066 master_template_updated_flag,
1067 batch_cancelled_flag,
1068 object_version_number, created_by,
1069 creation_date, last_updated_by,
1070 last_update_login,
1071 last_update_date)
1072 VALUES (fnd_global.conc_request_id,
1073 l_document_id,
1074 l_letter_type,
1075 l_version_id,
1076 USERENV ('LANG'),
1077 l_delivery_type,
1078 'N',
1079 'N',
1080 1, fnd_global.user_id,
1081 SYSDATE, fnd_global.user_id,
1082 NULL,
1083 SYSDATE);
1084
1085 l_preview_batch_stored := TRUE ;
1086 EXCEPTION --added by svadde on 10/31/2005
1087 WHEN OTHERS THEN
1088 IF fnd_log.test (fnd_log.level_procedure, l_prog_label) THEN
1089 IF (l_log_request_id IS NULL) THEN
1090 l_log_request_id := fnd_global.conc_request_id;
1091 END IF;
1092
1093 l_label :=
1094 'igs.plsql.igs_co_submit.submit_correspondence_request.exception';
1095 l_debug_str := 'Insert into igs_co_prv_bch_reqs failed with :' || SQLERRM;
1096 fnd_log.string_with_context (
1097 fnd_log.level_procedure,
1098 l_label,
1099 l_debug_str,
1100 NULL,
1101 NULL,
1102 NULL,
1103 NULL,
1104 NULL,
1105 TO_CHAR (l_log_request_id)
1106 );
1107 END IF;
1108 app_exception.RAISE_EXCEPTION;
1109 END;
1110 END IF;
1111 ELSE
1112 -- post-process the request as per pre-preview and edit
1113 igs_co_process.corp_post_process (
1114 p_person_id => l_person_id_dy,
1115 p_request_id => l_request_id,
1116 p_document_id => l_document_id,
1117 p_sys_ltr_code => l_sys_ltr_code,
1118 p_document_type => l_letter_type,
1119 p_adm_appl_number => l_adm_appl_number_dy,
1120 p_nominated_course_cd => l_nominated_course_cd_dy,
1121 p_appl_seq_number => l_appl_sequence_number_dy,
1122 p_award_year => p_parameter_1,
1123 p_awd_cal_type => l_awd_cal_type,
1124 p_awd_ci_seq_number => l_awd_ci_seq_number,
1125 p_delivery_type => p_delivery_type,
1126 p_version_id => l_version_id,
1127 p_award_prd_cd => p_parameter_2
1128 );
1129 END IF;
1130 END IF;
1131 END IF;
1132 END IF;
1133 END LOOP;
1134
1135 DBMS_SQL.close_cursor (l_cursor_id);
1136
1137 IF p_preview = 'Y' THEN
1138 -- If in preview mode then build the HTML in the concurrent request to take the user to the workbench.
1139 -- PACROSS - 11-SEP-2005
1140 --** proc level logging.
1141 IF fnd_log.test (fnd_log.level_procedure, l_prog_label) THEN
1142 IF (l_log_request_id IS NULL) THEN
1143 l_log_request_id := fnd_global.conc_request_id;
1144 END IF;
1145
1146 l_label :=
1147 'igs.plsql.igs_co_submit.submit_correspondence_request.calling_build_preview_1';
1148 l_debug_str := 'calling build_preview_html_1';
1149 fnd_log.string_with_context (
1150 fnd_log.level_procedure,
1151 l_label,
1152 l_debug_str,
1153 NULL,
1154 NULL,
1155 NULL,
1156 NULL,
1157 NULL,
1158 TO_CHAR (l_log_request_id)
1159 );
1160 END IF;
1161
1162 --**
1163 build_preview_html (fnd_global.conc_request_id);
1164 END IF;
1165 ELSE
1166 fnd_message.set_name ('IGS', 'IGS_CO_NO_REC');
1167 fnd_file.put_line (fnd_file.LOG, fnd_message.get ());
1168 fnd_file.put_line (fnd_file.LOG, ' ');
1169 RETURN;
1170 END IF;
1171
1172 IF l_person_processed = 0 THEN
1173 fnd_message.set_name ('IGS', 'IGS_CO_NO_REC');
1174 fnd_file.put_line (fnd_file.LOG, fnd_message.get ());
1175 fnd_file.put_line (fnd_file.LOG, ' ');
1176
1177 --fnd_file.put_line(FND_FILE.LOG,l_sql_stmt);
1178
1179 --** exec level logging.
1180 IF fnd_log.test (fnd_log.level_exception, l_prog_label) THEN
1181 IF (l_log_request_id IS NULL) THEN
1182 l_log_request_id := fnd_global.conc_request_id;
1183 END IF;
1184
1185 l_label :=
1186 'igs.plsql.igs_co_submit.submit_correspondence_request.sqlnodata.Y';
1187 l_debug_str := 'Sql Statement :' || l_sql_stmt;
1188 fnd_log.string_with_context (
1189 fnd_log.level_exception,
1190 l_label,
1191 l_debug_str,
1192 NULL,
1193 NULL,
1194 NULL,
1195 NULL,
1196 NULL,
1197 TO_CHAR (l_log_request_id)
1198 );
1199 END IF;
1200 --**
1201
1202 RETURN;
1203 END IF;
1204 ELSIF p_override_flag = 'N' THEN
1205 -- if FAM letter then Document attribute checking is not required.
1206 IF l_sys_ltr_code NOT IN ('FAAWARD', 'FAMISTM', 'FADISTM') THEN
1207 igs_co_process.corp_check_document_attributes (
1208 p_map_id => p_map_id,
1209 p_elapsed_days => l_elapsed_days,
1210 p_no_of_repeats => l_no_of_repeats
1211 );
1212 -- If NOT FAM letter and if the Elapsed days and No of repeats is not Null then only we need to call the build SQL stmt procedure
1213 -- If it is FAM letter, then the Pre Process procedure call itself gets the Dynamic SQL query and hence we need to bypass the call corp_build_sql_Stmt
1214 -- IF (l_elapsed_days IS NOT NULL OR l_no_of_repeats is NOT NULL) THEN
1215 l_exception := 'N';
1216 igs_co_process.corp_build_sql_stmt (
1217 p_document_id => l_document_id,
1218 p_select_type => p_select_type,
1219 p_sys_ltr_code => l_sys_ltr_code,
1220 p_list_id => p_list_id,
1221 p_letter_type => l_letter_type,
1222 p_person_id => p_person_id,
1223 p_parameter_1 => p_parameter_1,
1224 p_parameter_2 => p_parameter_2,
1225 p_parameter_3 => p_parameter_3,
1226 p_parameter_4 => p_parameter_4,
1227 p_parameter_5 => p_parameter_5,
1228 p_parameter_6 => p_parameter_6,
1229 p_parameter_7 => p_parameter_7,
1230 p_parameter_8 => p_parameter_8,
1231 p_parameter_9 => p_parameter_9,
1232 p_sql_stmt => l_sql_stmt,
1233 p_exception => l_exception
1234 );
1235
1236 IF l_exception = 'Y' THEN
1237 retcode := 2;
1238 RETURN;
1239 END IF;
1240
1241 -- END IF;
1242 l_awd_cal_type := NULL;
1243 l_awd_ci_seq_number := NULL;
1244 END IF;
1245
1246 /* This will print the Dynamic SQL statement prepared. Can be uncommented when testing.
1247 Igs_Ad_Imp_001.logDetail('l_sql_stmt :'||l_sql_stmt);
1248 */
1249
1250 -- executing Dynamic SQL. Either received from build_sql_stmt procedure (for Admissions) or Pre_Process (for FAM related letters)
1251 IF l_sql_stmt IS NOT NULL THEN
1252 --Included last two system letter codes as a part of bug 2376434
1253 l_cursor_id := DBMS_SQL.open_cursor;
1254 fnd_dsql.set_cursor (l_cursor_id);
1255 DBMS_SQL.parse (l_cursor_id, l_sql_stmt, DBMS_SQL.native);
1256 fnd_dsql.do_binds;
1257
1258 IF l_sys_ltr_code IN ('ADRESID',
1259 'FAADHOC',
1260 'ADADHOC',
1261 'GENERIC',
1262 'ENADHOC',
1263 'SFADHOC'
1264 ) THEN
1265 DBMS_SQL.define_column (
1266 l_cursor_id,
1267 1,
1268 l_email_address_dy,
1269 2000
1270 );
1271 DBMS_SQL.define_column (l_cursor_id, 2, l_person_id_dy);
1272 ELSIF l_sys_ltr_code = 'ADACKMT' THEN
1273 DBMS_SQL.define_column (
1274 l_cursor_id,
1275 1,
1276 l_email_address_dy,
1277 2000
1278 );
1279 DBMS_SQL.define_column (l_cursor_id, 2, l_person_id_dy);
1280 DBMS_SQL.define_column (l_cursor_id, 3, l_adm_appl_number_dy);
1281 DBMS_SQL.define_column (
1282 l_cursor_id,
1283 4,
1284 l_nominated_course_cd_dy,
1285 6
1286 );
1287 DBMS_SQL.define_column (
1288 l_cursor_id,
1289 5,
1290 l_appl_sequence_number_dy
1291 );
1292 ELSIF l_sys_ltr_code IN ('FAAWARD', 'FAMISTM', 'FADISBT') THEN
1293 DBMS_SQL.define_column (
1294 l_cursor_id,
1295 1,
1296 l_email_address_dy,
1297 2000
1298 );
1299 DBMS_SQL.define_column (l_cursor_id, 2, l_person_id_dy);
1300 DBMS_SQL.define_column (l_cursor_id, 3, l_award_year_dy, 17);
1301 ELSIF l_sys_ltr_code = 'ADINTRW' THEN
1302 DBMS_SQL.define_column (
1303 l_cursor_id,
1304 1,
1305 l_email_address_dy,
1306 2000
1307 );
1308 DBMS_SQL.define_column (l_cursor_id, 2, l_person_id_dy);
1309 DBMS_SQL.define_column (l_cursor_id, 3, l_adm_appl_number_dy);
1310 DBMS_SQL.define_column (
1311 l_cursor_id,
1312 4,
1313 l_nominated_course_cd_dy,
1314 6
1315 );
1316 DBMS_SQL.define_column (
1317 l_cursor_id,
1318 5,
1319 l_appl_sequence_number_dy
1320 );
1321 DBMS_SQL.define_column (l_cursor_id, 6, l_panel_code_dy, 200);
1322 ELSE
1323 DBMS_SQL.define_column (
1324 l_cursor_id,
1325 1,
1326 l_email_address_dy,
1327 2000
1328 );
1329 DBMS_SQL.define_column (l_cursor_id, 2, l_person_id_dy);
1330 DBMS_SQL.define_column (l_cursor_id, 3, l_adm_appl_number_dy);
1331 DBMS_SQL.define_column (
1332 l_cursor_id,
1333 4,
1334 l_nominated_course_cd_dy,
1335 6
1336 );
1337 DBMS_SQL.define_column (
1338 l_cursor_id,
1339 5,
1340 l_appl_sequence_number_dy
1341 );
1342 END IF;
1343
1344 l_num_of_rows := DBMS_SQL.EXECUTE (l_cursor_id);
1345
1346 /* This will print the Dynamic SQL statement prepared. Can be uncommented when testing.
1347 l_dsql_debug := fnd_dsql.get_text(TRUE);
1348 Igs_Ad_Imp_001.logDetail('l_dsql_debug :'||l_dsql_debug);
1349 */
1350 LOOP
1351 IF DBMS_SQL.fetch_rows (l_cursor_id) > 0 THEN
1352 l_person_processed := l_person_processed + 1;
1353
1354 IF l_sys_ltr_code IN ('ADRESID',
1355 'FAADHOC',
1356 'ADADHOC',
1357 'GENERIC',
1358 'ENADHOC',
1359 'SFADHOC'
1360 ) THEN
1361 DBMS_SQL.column_value (
1362 l_cursor_id,
1363 1,
1364 l_email_address_dy
1365 );
1366 DBMS_SQL.column_value (l_cursor_id, 2, l_person_id_dy);
1367 ELSIF l_sys_ltr_code = 'ADACKMT' THEN
1368 DBMS_SQL.column_value (
1369 l_cursor_id,
1370 1,
1371 l_email_address_dy
1372 );
1373 DBMS_SQL.column_value (l_cursor_id, 2, l_person_id_dy);
1374 DBMS_SQL.column_value (
1375 l_cursor_id,
1376 3,
1377 l_adm_appl_number_dy
1378 );
1379 DBMS_SQL.column_value (
1380 l_cursor_id,
1381 4,
1382 l_nominated_course_cd_dy
1383 );
1384 DBMS_SQL.column_value (
1385 l_cursor_id,
1386 5,
1387 l_appl_sequence_number_dy
1388 );
1389 ELSIF l_sys_ltr_code IN ('FAAWARD', 'FAMISTM', 'FADISBT') THEN
1390 DBMS_SQL.column_value (
1391 l_cursor_id,
1392 1,
1393 l_email_address_dy
1394 );
1395 DBMS_SQL.column_value (l_cursor_id, 2, l_person_id_dy);
1396 DBMS_SQL.column_value (l_cursor_id, 3, l_award_year_dy);
1397 ELSIF l_sys_ltr_code = 'ADINTRW' THEN
1398 DBMS_SQL.column_value (
1399 l_cursor_id,
1400 1,
1401 l_email_address_dy
1402 );
1403 DBMS_SQL.column_value (l_cursor_id, 2, l_person_id_dy);
1404 DBMS_SQL.column_value (
1405 l_cursor_id,
1406 3,
1407 l_adm_appl_number_dy
1408 );
1409 DBMS_SQL.column_value (
1410 l_cursor_id,
1411 4,
1412 l_nominated_course_cd_dy
1413 );
1414 DBMS_SQL.column_value (
1415 l_cursor_id,
1416 5,
1417 l_appl_sequence_number_dy
1418 );
1419 DBMS_SQL.column_value (l_cursor_id, 6, l_panel_code_dy);
1420 ELSE
1421 DBMS_SQL.column_value (
1422 l_cursor_id,
1423 1,
1424 l_email_address_dy
1425 );
1426 DBMS_SQL.column_value (l_cursor_id, 2, l_person_id_dy);
1427 DBMS_SQL.column_value (
1428 l_cursor_id,
1429 3,
1430 l_adm_appl_number_dy
1431 );
1432 DBMS_SQL.column_value (
1433 l_cursor_id,
1434 4,
1435 l_nominated_course_cd_dy
1436 );
1437 DBMS_SQL.column_value (
1438 l_cursor_id,
1439 5,
1440 l_appl_sequence_number_dy
1441 );
1442 END IF;
1443
1444 l_address_found := 'N';
1445
1446 IF INSTR (l_delivery_type, 'EMAIL') > 0
1447 AND l_email_address_dy IS NOT NULL THEN
1448 l_address_found := 'Y';
1449 /* l_delivery_type := 'EMAIL';
1450 ELSIF p_delivery_type = 'FAX' THEN
1451 l_delivery_type := 'FAX';*/
1452 END IF;
1453 ELSE
1454 EXIT;
1455 END IF;
1456
1457 l_send_letter := 'TRUE'; -- default is to be taken as True
1458 -- process only for persons having an email id.
1459 IF INSTR (l_delivery_type, 'EMAIL') > 0 AND l_address_found =
1460 'N' THEN
1461 OPEN c_person_number (l_person_id_dy);
1462 FETCH c_person_number INTO l_person_number, l_full_name;
1463 CLOSE c_person_number;
1464 -- log a message that Email ID is Null
1465 fnd_message.set_name ('IGS', 'IGS_CO_PRSN_NO_EMAIL');
1466 fnd_message.set_token (
1467 'STUDENT',
1468 l_person_number || ' - ' || l_full_name
1469 );
1470 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
1471 ELSE
1472 IF l_sys_ltr_code NOT IN ('FAAWARD', 'FAMISTM', 'FADISTM') THEN
1473 igs_co_process.corp_check_interaction_history (
1474 p_person_id => l_person_id_dy,
1475 p_sys_ltr_code => l_sys_ltr_code,
1476 p_document_id => l_document_id,
1477 p_application_id => l_adm_appl_number_dy,
1478 p_course_cd => l_nominated_course_cd_dy,
1479 p_adm_seq_no => l_appl_sequence_number_dy,
1480 p_awd_cal_type => l_awd_cal_type,
1481 p_awd_seq_no => l_awd_ci_seq_number,
1482 p_elapsed_days => l_elapsed_days,
1483 p_no_of_repeats => l_no_of_repeats,
1484 p_send_letter => l_send_letter
1485 );
1486 END IF;
1487
1488 IF l_send_letter = 'TRUE' THEN
1489 IF l_sys_ltr_code IN
1490 ('FAAWARD', 'FAMISTM', 'FADISTBT')
1491 AND (l_letter_type = 'SYSTEM') THEN
1492 --** exec level logging.
1493 --**
1494 igs_co_process.corp_submit_fulfil_request (
1495 p_letter_type => l_letter_type,
1496 p_person_id => l_person_id_dy,
1497 p_sys_ltr_code => l_sys_ltr_code,
1498 p_email_address => l_email_address_dy,
1499 p_content_id => l_document_id,
1500 p_adm_appl_number => l_adm_appl_number_dy,
1501 p_nominated_course_cd => l_nominated_course_cd_dy,
1502 p_appl_sequence_number => l_appl_sequence_number_dy,
1503 p_award_year => p_parameter_1,
1504 p_fulfillment_req => l_fulfillment_req,
1505 p_crm_user_id => l_crm_user_id,
1506 p_media_type => l_delivery_type,
1507 p_destination => l_printer_name, -- Added the parameter p_destination which takes the value of the printer name. Bug# 2472250
1508 p_fax_number => l_fax_number,
1509 p_request_id => l_request_id,
1510 p_request_status => l_request_status,
1511 p_reply_email => p_reply_email,
1512 p_sender_email => p_sender_email,
1513 p_cc_email => p_cc_email,
1514 p_org_unit_id => p_org_unit_id,
1515 p_preview => p_preview,
1516 p_awd_cal_type => l_awd_cal_type,
1517 p_awd_ci_seq_number => l_awd_ci_seq_number,
1518 p_awd_prd_cd => p_parameter_2
1519 );
1520
1521 IF NOT igs_co_process.l_corp_submit_fulfil_request THEN
1522 IF p_preview = 'Y' THEN
1523 -- Store away request values so they can be used later to fulfill the request after preview.
1524 -- PACROSS - 11-SEP-2005
1525
1526 IF NOT l_preview_batch_stored THEN
1527 BEGIN
1528 INSERT INTO igs_co_prv_bch_reqs
1529 (concurrent_request_id,
1530 master_content_id,
1531 master_content_type_code,
1532 master_version_id,
1533 master_language,
1534 master_media_type_code,
1535 master_template_updated_flag,
1536 batch_cancelled_flag,
1537 object_version_number,
1538 created_by, creation_date,
1539 last_updated_by,
1540 last_update_login,
1541 last_update_date)
1542 VALUES (fnd_global.conc_request_id,
1543 l_document_id,
1544 l_letter_type,
1545 l_version_id,
1546 USERENV ('LANG'),
1547 l_delivery_type,
1548 'N',
1549 'N',
1550 1,
1551 fnd_global.user_id, SYSDATE,
1552 fnd_global.user_id,
1553 NULL,
1554 SYSDATE);
1555
1556 l_preview_batch_stored := TRUE ;
1557 EXCEPTION --added by svadde on 10/31/2005
1558 WHEN OTHERS THEN
1559 IF fnd_log.test (fnd_log.level_procedure, l_prog_label) THEN
1560 IF (l_log_request_id IS NULL) THEN
1561 l_log_request_id := fnd_global.conc_request_id;
1562 END IF;
1563
1564 l_label :=
1565 'igs.plsql.igs_co_submit.submit_correspondence_request.exception';
1566 l_debug_str := 'Insert into igs_co_prv_bch_reqs failed with :' || SQLERRM;
1567 fnd_log.string_with_context (
1568 fnd_log.level_procedure,
1569 l_label,
1570 l_debug_str,
1571 NULL,
1572 NULL,
1573 NULL,
1574 NULL,
1575 NULL,
1576 TO_CHAR (l_log_request_id)
1577 );
1578 END IF;
1579 app_exception.RAISE_EXCEPTION;
1580 END;
1581
1582 END IF;
1583 ELSE
1584 -- post-process the request as per pre-preview and edit
1585
1586 igs_co_process.corp_post_process (
1587 p_person_id => l_person_id_dy,
1588 p_request_id => l_request_id,
1589 p_document_id => l_document_id,
1590 p_sys_ltr_code => l_sys_ltr_code,
1591 p_document_type => l_letter_type,
1592 p_adm_appl_number => l_adm_appl_number_dy,
1593 p_nominated_course_cd => l_nominated_course_cd_dy,
1594 p_appl_seq_number => l_appl_sequence_number_dy,
1595 p_award_year => p_parameter_1,
1596 p_awd_cal_type => l_awd_cal_type,
1597 p_awd_ci_seq_number => l_awd_ci_seq_number,
1598 p_delivery_type => p_delivery_type,
1599 p_version_id => l_version_id,
1600 p_award_prd_cd => p_parameter_2
1601 );
1602 END IF;
1603 END IF;
1604 ELSE
1605 igs_co_process.corp_submit_fulfil_request (
1606 p_letter_type => l_letter_type,
1607 p_person_id => l_person_id_dy,
1608 p_sys_ltr_code => l_sys_ltr_code,
1609 p_email_address => l_email_address_dy,
1610 p_content_id => l_document_id,
1611 p_adm_appl_number => l_adm_appl_number_dy,
1612 p_nominated_course_cd => l_nominated_course_cd_dy,
1613 p_appl_sequence_number => l_appl_sequence_number_dy,
1614 p_award_year => p_parameter_1,
1615 p_fulfillment_req => l_fulfillment_req,
1616 p_crm_user_id => l_crm_user_id,
1617 p_media_type => l_delivery_type,
1618 p_destination => l_printer_name, -- Added the parameter p_destination which takes the value of the printer name. Bug# 2472250
1619 p_fax_number => l_fax_number,
1620 p_reply_days => p_parameter_8,
1621 p_panel_code => p_parameter_5,
1622 p_request_id => l_request_id,
1623 p_request_status => l_request_status,
1624 p_reply_email => p_reply_email,
1625 p_sender_email => p_sender_email,
1626 p_cc_email => p_cc_email,
1627 p_org_unit_id => p_org_unit_id,
1628 p_preview => p_preview,
1629 p_awd_cal_type => l_awd_cal_type,
1630 p_awd_ci_seq_number => l_awd_ci_seq_number,
1631 p_awd_prd_cd => p_parameter_2
1632 );
1633
1634 IF NOT igs_co_process.l_corp_submit_fulfil_request THEN
1635 IF p_preview = 'Y' THEN
1636 -- Store away request values so they can be used later to fulfill the request after preview.
1637 -- PACROSS - 11-SEP-2005
1638 --** exec level logging.
1639
1640 --**
1641
1642 IF NOT l_preview_batch_stored THEN
1643 BEGIN
1644 INSERT INTO igs_co_prv_bch_reqs
1645 (concurrent_request_id,
1646 master_content_id,
1647 master_content_type_code,
1648 master_version_id,
1649 master_language,
1650 master_media_type_code,
1651 master_template_updated_flag,
1652 batch_cancelled_flag,
1653 object_version_number,
1654 created_by, creation_date,
1655 last_updated_by,
1656 last_update_login,
1657 last_update_date)
1658 VALUES (fnd_global.conc_request_id,
1659 l_document_id,
1660 l_letter_type,
1661 l_version_id,
1662 USERENV ('LANG'),
1663 l_delivery_type,
1664 'N',
1665 'N',
1666 1,
1667 fnd_global.user_id, SYSDATE,
1668 fnd_global.user_id,
1669 NULL,
1670 SYSDATE);
1671
1672 l_preview_batch_stored := TRUE ;
1673 EXCEPTION --added by svadde on 10/31/2005
1674 WHEN OTHERS THEN
1675 IF fnd_log.test (fnd_log.level_procedure, l_prog_label) THEN
1676 IF (l_log_request_id IS NULL) THEN
1677 l_log_request_id := fnd_global.conc_request_id;
1678 END IF;
1679
1680 l_label :=
1681 'igs.plsql.igs_co_submit.submit_correspondence_request.exception';
1682 l_debug_str := 'Insert into igs_co_prv_bch_reqs failed with :' || SQLERRM;
1683 fnd_log.string_with_context (
1684 fnd_log.level_procedure,
1685 l_label,
1686 l_debug_str,
1687 NULL,
1688 NULL,
1689 NULL,
1690 NULL,
1691 NULL,
1692 TO_CHAR (l_log_request_id)
1693 );
1694 END IF;
1695 app_exception.RAISE_EXCEPTION;
1696 END;
1697 END IF;
1698
1699 ELSE
1700 -- post-process the request as per pre-preview and edit
1701
1702 igs_co_process.corp_post_process (
1703 p_person_id => l_person_id_dy,
1704 p_request_id => l_request_id,
1705 p_document_id => l_document_id,
1706 p_sys_ltr_code => l_sys_ltr_code,
1707 p_document_type => l_letter_type,
1708 p_adm_appl_number => l_adm_appl_number_dy,
1709 p_nominated_course_cd => l_nominated_course_cd_dy,
1710 p_appl_seq_number => l_appl_sequence_number_dy,
1711 p_award_year => p_parameter_1,
1712 p_awd_cal_type => l_awd_cal_type,
1713 p_awd_ci_seq_number => l_awd_ci_seq_number,
1714 p_delivery_type => p_delivery_type,
1715 p_version_id => l_version_id,
1716 p_award_prd_cd => p_parameter_2
1717 );
1718 END IF;
1719 END IF;
1720 END IF;
1721 END IF;
1722 END IF;
1723 END LOOP;
1724
1725 --** exec level logging.
1726
1727 --**
1728 DBMS_SQL.close_cursor (l_cursor_id);
1729
1730 IF p_preview = 'Y' THEN
1731 -- If in preview mode then build the HTML in the concurrent request to take the user to the workbench.
1732 -- PACROSS - 11-SEP-2005
1733 --** proc level logging.
1734 IF fnd_log.test (fnd_log.level_procedure, l_prog_label) THEN
1735 IF (l_log_request_id IS NULL) THEN
1736 l_log_request_id := fnd_global.conc_request_id;
1737 END IF;
1738
1739 l_label :=
1740 'igs.plsql.igs_co_submit.submit_correspondence_request.calling_build_preview_2';
1741 l_debug_str := 'calling build_preview_html_2';
1742 fnd_log.string_with_context (
1743 fnd_log.level_procedure,
1744 l_label,
1745 l_debug_str,
1746 NULL,
1747 NULL,
1748 NULL,
1749 NULL,
1750 NULL,
1751 TO_CHAR (l_log_request_id)
1752 );
1753 END IF;
1754
1755 --**
1756 build_preview_html (fnd_global.conc_request_id);
1757 END IF;
1758
1759 --** exec level logging.
1760
1761
1762 ELSE
1763 fnd_message.set_name ('IGS', 'IGS_CO_NO_REC');
1764 fnd_file.put_line (fnd_file.LOG, fnd_message.get ());
1765 fnd_file.put_line (fnd_file.LOG, ' ');
1766 RETURN;
1767 END IF;
1768
1769 IF l_person_processed = 0 THEN
1770 fnd_message.set_name ('IGS', 'IGS_CO_NO_REC');
1771 fnd_file.put_line (fnd_file.LOG, fnd_message.get ());
1772 fnd_file.put_line (fnd_file.LOG, ' ');
1773
1774 --fnd_file.put_line(FND_FILE.LOG,l_sql_stmt);
1775 --** exec level logging.
1776 IF fnd_log.test (fnd_log.level_exception, l_prog_label) THEN
1777 IF (l_log_request_id IS NULL) THEN
1778 l_log_request_id := fnd_global.conc_request_id;
1779 END IF;
1780
1781 l_label :=
1782 'igs.plsql.igs_co_submit.submit_correspondence_request.sqlnodata.N';
1783 l_debug_str := 'Sql Statement :' || l_sql_stmt;
1784 fnd_log.string_with_context (
1785 fnd_log.level_exception,
1786 l_label,
1787 l_debug_str,
1788 NULL,
1789 NULL,
1790 NULL,
1791 NULL,
1792 NULL,
1793 TO_CHAR (l_log_request_id)
1794 );
1795 END IF;
1796 --**
1797
1798 RETURN;
1799 END IF;
1800 END IF;
1801 EXCEPTION
1802 WHEN OTHERS THEN
1803 ROLLBACK;
1804 retcode := 2;
1805 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1806 fnd_message.set_token (
1807 'NAME',
1808 'igs_co_submit.submit_correspondence_request' || '-' || SQLERRM
1809 );
1810 igs_ge_msg_stack.conc_exception_hndl;
1811 END submit_correspondence_request;
1812
1813 PROCEDURE distribute_preview_request (
1814 errbuf OUT NOCOPY VARCHAR2,
1815 retcode OUT NOCOPY NUMBER,
1816 p_distribution_id IN NUMBER
1817 )
1818 IS
1819 /*******************************************************************
1820 Created By : Kevin Leggett/Paul Cross
1821 Date Created on : 11-Sep-2005
1822 Purpose : This procedure is the main concurrent program procedure
1823 for distributing previewed correspondence requests. Its
1824 logic is based on the existing functionality inside the
1825 IGS_CO_SUBMIT.submit_correspondence_request concurrent
1826 program procedure. More specifically the segments
1827 responsible performing the actual distribution and post
1828 processing to update the IGS Interaction History.
1829 Know limitations, enhancements or remarks
1830 Change History
1831 Who When What
1832 gmaheswa 5-Jan-2004 Bug 4869737 Added a call to SET_ORG_ID to disable OSS for R12.
1833 ********************************************************************/
1834
1835 -- Cursor to obtain the preview requests for this distribution
1836 CURSOR c_preview_requests (cp_distribution_id NUMBER)
1837 IS
1838 SELECT concurrent_request_id, letter_type_code, person_id,
1839 email_address, original_content_id, current_content_id,
1840 award_year, sys_ltr_code, adm_appl_number, nominated_course_cd,
1841 appl_sequence_number, fulfillment_req, crm_user_id,
1842 media_type_code, destination, fax_number, reply_days, panel_code,
1843 org_unit_id, awd_cal_type, awd_ci_seq_number,
1844 original_version_id, current_version_id, email_subject,
1845 original_content_xml, current_content_xml, ff_request_hist_id,
1846 extended_header, distribution_id, award_prd_cd
1847 FROM igs_co_prev_reqs
1848 WHERE distribution_id = cp_distribution_id
1849 AND NOT ( request_status_code = 'CANCELLED'
1850 OR request_status_code = 'DISTRIBUTED'
1851 );
1852
1853 -- Cursor to log the person's processed
1854 CURSOR c_per_processed (cp_person_id NUMBER)
1855 IS
1856 SELECT person_number, full_name
1857 FROM igs_pe_person_base_v
1858 WHERE person_id = cp_person_id;
1859
1860 -- Get version id of content for interaction history
1861 CURSOR c_content_version (
1862 cp_content_id NUMBER,
1863 cp_content_item_version_id NUMBER
1864 )
1865 IS
1866 SELECT version
1867 FROM ibc_citems_v
1868 WHERE citem_id = cp_content_id
1869 AND citem_ver_id = cp_content_item_version_id;
1870
1871 -- Preview request variables...
1872 l_concurrent_request_id igs_co_prev_reqs.concurrent_request_id%TYPE;
1873 l_letter_type igs_co_prev_reqs.letter_type_code%TYPE;
1874 l_person_id igs_co_prev_reqs.person_id%TYPE;
1875 l_email_address igs_co_prev_reqs.email_address%TYPE;
1876 l_original_content_id igs_co_prev_reqs.original_content_id%TYPE;
1877 l_current_content_id igs_co_prev_reqs.current_content_id%TYPE;
1878 l_award_year igs_co_prev_reqs.award_year%TYPE;
1879 l_sys_ltr_code igs_co_prev_reqs.sys_ltr_code%TYPE;
1880 l_adm_appl_number igs_co_prev_reqs.adm_appl_number%TYPE;
1881 l_nominated_course_cd igs_co_prev_reqs.nominated_course_cd%TYPE;
1882 l_appl_sequence_number igs_co_prev_reqs.appl_sequence_number%TYPE;
1883 l_fulfillment_req igs_co_prev_reqs.fulfillment_req%TYPE;
1884 l_crm_user_id igs_co_prev_reqs.crm_user_id%TYPE;
1885 l_media_type igs_co_prev_reqs.media_type_code%TYPE;
1886 l_destination igs_co_prev_reqs.destination%TYPE;
1887 l_fax_number igs_co_prev_reqs.fax_number%TYPE;
1888 l_reply_days igs_co_prev_reqs.reply_days%TYPE;
1889 l_panel_code igs_co_prev_reqs.panel_code%TYPE;
1890 l_org_unit_id igs_co_prev_reqs.org_unit_id%TYPE;
1891 l_awd_cal_type igs_co_prev_reqs.awd_cal_type%TYPE;
1892 l_awd_ci_seq_number igs_co_prev_reqs.awd_ci_seq_number%TYPE;
1893 l_originalversion_id igs_co_prev_reqs.original_version_id%TYPE;
1894 l_currentversion_id igs_co_prev_reqs.current_version_id%TYPE;
1895 l_email_subject igs_co_prev_reqs.email_subject%TYPE;
1896 l_original_content_xml igs_co_prev_reqs.original_content_xml%TYPE;
1897 l_current_content_xml igs_co_prev_reqs.current_content_xml%TYPE;
1898 l_ff_request_hist_id igs_co_prev_reqs.ff_request_hist_id%TYPE;
1899 l_extended_header igs_co_prev_reqs.extended_header%TYPE;
1900 l_distribution_id igs_co_prev_reqs.distribution_id%TYPE;
1901 l_award_prd_cd igs_co_prev_reqs.award_prd_cd%TYPE;
1902 -- Person details...
1903 l_full_name igs_pe_person_base_v.full_name%TYPE;
1904 l_person_number igs_pe_person_base_v.person_number%TYPE;
1905 -- Version
1906 l_version ibc_citems_v.version%TYPE;
1907 -- Return status
1908 l_return_status VARCHAR2 (30);
1909 l_msg_count NUMBER;
1910 l_msg_data VARCHAR2 (2000);
1911 l_tmp_var VARCHAR2 (4000);
1912 l_tmp_var1 VARCHAR2 (4000);
1913 l_prog_label CONSTANT VARCHAR2 (500) := 'igs.plsql.igs_co_submit';
1914 l_tmp_request_id NUMBER;
1915 l_label VARCHAR2 (4000);
1916 l_debug_str VARCHAR2 (4000);
1917 BEGIN
1918 igs_ge_gen_003.set_org_id;
1919
1920 -- Default to success (0-Success, 1-Success with warnings, 2-Error)
1921 retcode := 0;
1922
1923 IF p_distribution_id IS NOT NULL THEN
1924 OPEN c_preview_requests (p_distribution_id);
1925
1926 LOOP
1927 FETCH c_preview_requests INTO l_concurrent_request_id,
1928 l_letter_type,
1929 l_person_id,
1930 l_email_address,
1931 l_original_content_id,
1932 l_current_content_id,
1933 l_award_year,
1934 l_sys_ltr_code,
1935 l_adm_appl_number,
1936 l_nominated_course_cd,
1937 l_appl_sequence_number,
1938 l_fulfillment_req,
1939 l_crm_user_id,
1940 l_media_type,
1941 l_destination,
1942 l_fax_number,
1943 l_reply_days,
1944 l_panel_code,
1945 l_org_unit_id,
1946 l_awd_cal_type,
1947 l_awd_ci_seq_number,
1948 l_originalversion_id,
1949 l_currentversion_id,
1950 l_email_subject,
1951 l_original_content_xml,
1952 l_current_content_xml,
1953 l_ff_request_hist_id,
1954 l_extended_header,
1955 l_distribution_id,
1956 l_award_prd_cd;
1957 EXIT WHEN c_preview_requests%NOTFOUND;
1958 jtf_fm_request_grp.submit_previewed_request (
1959 p_api_version => 1,
1960 p_init_msg_list => 'T',
1961 x_return_status => l_return_status,
1962 x_msg_count => l_msg_count,
1963 x_msg_data => l_msg_data,
1964 p_request_id => l_ff_request_hist_id
1965 );
1966
1967 IF l_return_status = 'S' THEN
1968 OPEN c_per_processed (l_person_id);
1969 FETCH c_per_processed INTO l_person_number, l_full_name;
1970 fnd_message.set_name ('IGF', 'IGF_AW_PROC_STUD');
1971 fnd_message.set_token (
1972 'STDNT',
1973 l_person_number || ' - ' || l_full_name
1974 );
1975 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
1976 fnd_message.set_name ('IGS', 'IGS_CO_REQ_INFO');
1977 fnd_message.set_token ('REQUEST_ID', l_ff_request_hist_id);
1978 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
1979 CLOSE c_per_processed;
1980 OPEN c_content_version (
1981 l_current_content_id,
1982 l_currentversion_id
1983 );
1984 FETCH c_content_version INTO l_version;
1985 CLOSE c_content_version;
1986 -- Update the IGS_CO_PREV_REQS table to identify this request as fulfilled...
1987 UPDATE igs_co_prev_reqs
1988 SET request_status_code = 'DISTRIBUTED'
1989 WHERE distribution_id = p_distribution_id;
1990
1991 -- Call the post processing procedure (this will log the IGS Interaction History details)...
1992 igs_co_process.corp_post_process (
1993 p_person_id => l_person_id,
1994 p_request_id => l_ff_request_hist_id,
1995 p_document_id => l_current_content_id,
1996 p_sys_ltr_code => l_sys_ltr_code,
1997 p_document_type => l_letter_type,
1998 p_adm_appl_number => l_adm_appl_number,
1999 p_nominated_course_cd => l_nominated_course_cd,
2000 p_appl_seq_number => l_appl_sequence_number,
2001 p_award_year => l_award_year,
2002 p_awd_cal_type => l_awd_cal_type,
2003 p_awd_ci_seq_number => l_awd_ci_seq_number,
2004 p_delivery_type => l_media_type,
2005 p_version_id => l_version,
2006 p_award_prd_cd => l_award_prd_cd
2007 );
2008 -- Commit actions...
2009 COMMIT;
2010 ELSE
2011 IF l_msg_count > 1 THEN
2012 FOR i IN 1 .. l_msg_count
2013 LOOP
2014 l_tmp_var := fnd_msg_pub.get (
2015 p_encoded => fnd_api.g_false
2016 );
2017 l_tmp_var1 := l_tmp_var1 || l_tmp_var;
2018 END LOOP;
2019
2020 fnd_file.put_line (fnd_file.LOG, l_tmp_var1);
2021 ELSE
2022 fnd_file.put_line (
2023 fnd_file.LOG,
2024 l_msg_data || '-' || l_msg_count
2025 );
2026 END IF;
2027 END IF;
2028
2029 --** proc level logging.
2030 IF fnd_log.test (fnd_log.level_procedure, l_prog_label) THEN
2031 IF (l_tmp_request_id IS NULL) THEN
2032 l_tmp_request_id := fnd_global.conc_request_id;
2033 END IF;
2034
2035 l_label :=
2036 'igs.plsql.igs_co_process.corp_submit_fulfil_request.aftersubmitrequest';
2037 l_debug_str := 'Request ID :'
2038 || l_ff_request_hist_id
2039 || 'Return Status :'
2040 || l_return_status
2041 || '-'
2042 || l_msg_data;
2043 fnd_log.string_with_context (
2044 fnd_log.level_procedure,
2045 l_label,
2046 l_debug_str,
2047 NULL,
2048 NULL,
2049 NULL,
2050 NULL,
2051 NULL,
2052 TO_CHAR (l_tmp_request_id)
2053 );
2054 END IF;
2055 --**
2056
2057 END LOOP;
2058 -- Explicitly close the cursor...
2059 CLOSE c_preview_requests;
2060 ELSE
2061 -- log message "Invalid distribution identifier supplied"
2062 fnd_message.set_name ('IGS', 'IGS_CO_ERR_INVLD_DIST_ID');
2063 fnd_file.put_line (fnd_file.LOG, fnd_message.get ());
2064 fnd_file.put_line (fnd_file.LOG, ' ');
2065 retcode := 2;
2066 RETURN;
2067 END IF;
2068 EXCEPTION
2069 WHEN OTHERS THEN
2070 ROLLBACK;
2071 retcode := 2;
2072 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
2073 fnd_message.set_token (
2074 'NAME',
2075 l_prog_label || '.distribute_preview_request' || '-' || SQLERRM
2076 );
2077 igs_ge_msg_stack.conc_exception_hndl;
2078 END distribute_preview_request;
2079 END igs_co_submit;