1 PACKAGE BODY igs_co_process AS
2 /* $Header: IGSCO22B.pls 120.12 2006/05/31 10:25:46 vskumar ship $ */
3 /*************************************************************
4 Created By :Nalin Kumar
5 Date Created on : 05-Feb-2002
6 Purpose : This package will consist of procedures that will perform validation
7 and processing of correspondence related information and data.
8 Know limitations, enhancements or remarks
9 Change History
10 Who When What
11 svadde 28-Apr-2006 Bug 5126451 removed the dubuging of sql statement
12 pkpatel 6-Feb-2006 Bug 4937960 (MOdified the Person ID group logic to use get_dynamic_sql function to solve literal issue. Uncommented all the FND loggings)
13 pacross 11-APR-2005 Implemented code for Correspondance preview and edit fucntionality
14 mnade 6/1/2005 FA 157 Added p_award_prd_cd parameter to corp_post_process
15 ssaleem 09-SEP-2004 3630073. Added p_org_unit_id as a new parameter
16 ssawhney 3-may-04 IBC.C patchset changes bug 3565861 + 3442719 + signature of corp_get_letter_type changed + interaction history signature changes
17 citemverid changes.
18 gmaheswa 15-Nov-2003 Bug : 3006800 Added New parameter p_fax_number in corp_submit_fulfil_request and
19 fax number is passed to jtf_fm_request_grp.get_content_xml as p_fax.
20 ssaleem 28-OCT-2003 Bug : 3198795
21 Part of the Dynamic/Static Person Groups modifications,
22 Procedure corp_get_parameter_value is modified.
23 npalanis 23-OCT-2002 Bug : 2547368
24 Defaulting arguments in funtion and procedure definitions removed
25 kpadiyar 04-MAR-2003 Bug # 2520895 - Condition added with outcome status <> CANCELLED
26 kpadiyar 07-MAR-2003 Bug # 2836391 - parameter 8 and 9 commented wherever checks being done
27 reason being these 2 parameters are not being used and kept probably for backup.
28 KUMMA 07-jun-2003 2853531, Inside corp_submit_fulfil_request, Changed the cursor cur_get_sub to use the lookup type also
29 kumma 24-JUN-2003 2853531, Before making a CRM API call checked the return status of the earlier call
30 kumma 21-AUG-2003 3104787, Modified the corp_submit_fulfil_request , Added the code to check if the query is attached with a template and accordingly pass the content type 'QUERY' or 'DATA'
31 Modified the else condition to not to consider the Adhoc letters while binding the bind variables.
32 asbala 11-SEP-2003 3071111 GSCC FILE.DATE.5 Compliance
33 hreddych 13-oct-2003 Build UK Correspondence Letters
34 pkpatel 11-DEC-2003 Bug 2863933 (Added the where clause 1=1 in corp_get_system_letter_view for ADRESID. Removed the variable g_parameter_value.)
35 vskumar 30-May-2006 Xbuild3 performance fix. break cursor's select queries in procedure corp_check_interaction_history. e.g cur_c1 to cur_c1_part1 and cur_c1_part2.
36 ***************************************************************/
37 -- package variable declarations
38
39 l_prog_label CONSTANT VARCHAR2(500) :='igs.plsql.igs_co_process';
40 l_request_id NUMBER;
41 l_label VARCHAR2(32000);
42 l_debug_str VARCHAR2(32000);
43
44
45 PROCEDURE corp_get_letter_type(
46 p_map_id IN NUMBER,
47 p_document_id OUT NOCOPY NUMBER,
48 p_sys_ltr_code OUT NOCOPY VARCHAR2,
49 p_letter_type OUT NOCOPY VARCHAR2 ,
50 p_version_id OUT NOCOPY NUMBER
51 ) AS
52 /*************************************************************
53 Created By :Nalin Kumar
54 Date Created on : 05-Feb-2002
55 Purpose : This procedure will accept map id as a parameter and
56 returns document id, system letter code and letter
57 type for the map id.
58 Know limitations, enhancements or remarks
59 Change History
60 Who When What
61
62 (reverse chronological order - newest change first)
63 ***************************************************************/
64 CURSOR cur_c1 (cp_map_id NUMBER)IS
65 SELECT document_id, sys_ltr_code, doc_code , version_id
66 FROM igs_co_mapping_v
67 WHERE map_id = cp_map_id;
68 l_cur_c1 cur_c1%ROWTYPE;
69 BEGIN
70 OPEN cur_c1(p_map_id);
71 FETCH cur_c1 INTO l_cur_c1;
72 IF cur_c1%FOUND THEN
73 p_document_id := l_cur_c1.document_id;
74 p_sys_ltr_code := l_cur_c1.sys_ltr_code;
75 p_letter_type := l_cur_c1.doc_code;
76 p_version_id := l_cur_c1.version_id;
77 ELSE
78 p_document_id := NULL;
79 p_sys_ltr_code := NULL;
80 p_letter_type := NULL;
81 p_version_id := NULL;
82 END IF;
83 CLOSE cur_c1;
84 EXCEPTION
85 WHEN OTHERS THEN
86 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
87 FND_MESSAGE.SET_TOKEN('NAME','IGS_CO_PROCESS.corp_get_letter_type');
88 IGS_GE_MSG_STACK.ADD;
89 App_Exception.Raise_Exception;
90 END corp_get_letter_type;
91
92
93 PROCEDURE corp_build_sql_stmt(
94 p_document_id IN NUMBER,
95 p_sys_ltr_code IN VARCHAR2,
96 p_select_type IN VARCHAR2,
97 p_list_id IN NUMBER,
98 p_person_id IN NUMBER,
99 p_letter_type IN VARCHAR2,
100 p_parameter_1 IN VARCHAR2,
101 p_parameter_2 IN VARCHAR2,
102 p_parameter_3 IN VARCHAR2,
103 p_parameter_4 IN VARCHAR2,
104 p_parameter_5 IN VARCHAR2,
105 p_parameter_6 IN VARCHAR2,
106 p_parameter_7 IN VARCHAR2,
107 p_parameter_8 IN VARCHAR2,
108 p_parameter_9 IN VARCHAR2,
109 p_sql_stmt OUT NOCOPY VARCHAR2,
110 p_exception OUT NOCOPY VARCHAR2
111 ) AS
112 /*************************************************************
113 Created By :Nalin Kumar
114 Date Created on : 05-Feb-2002
115 Purpose : Based on the selection type this procedure will build and return a select statement.
116 Know limitations, enhancements or remarks
117 Change History
118 Who When What
119
120 (reverse chronological order - newest change first)
121 Bayadav 24-MAY-2002 Included two system letter codes 'ENADHOC', 'SFADHOC' for adhoc letters as a part of bug 2376434
122 cheslyn 10-JUN-2002 Chnged the applictaion name from IGS to IGF for message IGF_AW_NO_LIST as a part of bug 2410165
123 pkpatel 7-MAy-2003 Bug 2940810
124 Modified for Bind Variable
125 asbala 19-AUG-2003 3098262:Added check to retrieve only active members of a group
126 pkpatel 26-AUG-2003 Bug 3110793 (Removed the string WHERE while forming the dynamic clause for LIST for adhoc letters)
127 ssaleem 29-OCT-2003 Bug 3198795 For select type 'P' 'G', 'SYSTEM' check is removed, since
128 ADHoc letters were failing due to it
129 ***************************************************************/
130 l_view_name VARCHAR2(30);
131 l_where_clause VARCHAR2(350);
132 l_parameter_value VARCHAR2(2000);
133 l_str VARCHAR2(32767);
134 l_group_type VARCHAR2(10);
135 l_static_group VARCHAR2(1);
136 lv_status VARCHAR2(1);
137
138 CURSOR c_att_id(cp_itm_id ibc_citems_v.citem_id%TYPE) IS
139 SELECT attach_fid
140 FROM ibc_citems_v
141 WHERE CITEM_ID = cp_itm_id
142 AND language = USERENV('LANG');
143 /*
144 CURSOR c_group_member(cp_group_id VARCHAR2) IS
145 SELECT person_id
146 FROM igs_pe_prsid_grp_mem_all
147 WHERE group_id = cp_group_id AND SYSDATE BETWEEN start_date AND NVL(end_date, SYSDATE);
148 */
149 CURSOR cur_c1 (cp_map_id NUMBER)IS
150 SELECT document_id
151 FROM igs_co_mapping
152 WHERE map_id = cp_map_id;
153 l_cur_c1 cur_c1%ROWTYPE;
154 l_list_id igs_co_mapping.map_id%TYPE;
155 /*
156 CURSOR c_file_name IS
157 SELECT file_name
158 FROM igs_pe_persid_group_all
159 WHERE group_id = p_parameter_1;
160
161
162 TYPE cur_query IS REF CURSOR;
163 l_query_desc cur_query;*/
164 l_query_str VARCHAR2(32767);
165 l_person_id NUMBER;
166 l_and_con VARCHAR2(32767);
167 l_attach_fid ibc_citems_v.attach_fid%TYPE;
168 l_query_text VARCHAR2(32767);
169
170 BEGIN
171 l_static_group := 'Y';
172 IF p_select_type = 'L' THEN
173 OPEN cur_c1(p_list_id);
174 FETCH cur_c1 INTO l_cur_c1;
175 l_list_id := l_cur_c1.document_id;
176 CLOSE cur_c1;
177 END IF;
178
179 fnd_dsql.init;
180
181 IF p_sys_ltr_code = 'ADRESID' THEN
182 fnd_dsql.add_text('SELECT DISTINCT email_address, person_id FROM ');
183 ELSIF p_sys_ltr_code IN ('ADADHOC','FAADHOC','GENERIC','ENADHOC','SFADHOC') THEN
184 fnd_dsql.add_text('SELECT DISTINCT email_address, party_id FROM ');
185 ELSIF p_sys_ltr_code = 'ADACKMT' THEN
186 fnd_dsql.add_text('SELECT DISTINCT email_address, person_id, adm_appl_number,nominated_course_cd, appl_sequence_number FROM ');
187 ELSIF p_sys_ltr_code = 'ADINTRW' THEN
188 fnd_dsql.add_text('SELECT DISTINCT email_address, person_id, adm_appl_number,nominated_course_cd, appl_sequence_number, panel_code FROM ');
189 ELSE
190 fnd_dsql.add_text('SELECT DISTINCT email_address, person_id, adm_appl_number,nominated_course_cd, appl_sequence_number FROM ');
191 END IF;
192 corp_get_system_letter_view (p_sys_ltr_code,
193 l_view_name,
194 l_where_clause);
195
196 --** proc level logging.
197 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
198 IF (l_request_id IS NULL) THEN
199 l_request_id := fnd_global.conc_request_id;
200 END IF;
201 l_label := 'igs.plsql.igs_co_process.corp_build_sql_stmt.whereclause';
202 l_debug_str := 'View :'||l_view_name || 'Where Clause :'|| l_where_clause;
203 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
204 END IF;
205 --**
206
207
208 IF p_select_type = 'S' THEN
209 --
210 -- If the System Type is of type 'Student' then
211 --
212 IF p_letter_type = 'SYSTEM' THEN
213 IF l_where_clause IS NOT NULL THEN
214 IF p_sys_ltr_code = 'ADINTRW' THEN
215 fnd_dsql.add_text(l_view_name || ' WHERE person_id = ' );
216 fnd_dsql.add_bind(p_person_id);
217 fnd_dsql.add_text(' AND panel_code = ' );
218 fnd_dsql.add_bind(p_parameter_5);
219 ELSE
220 fnd_dsql.add_text(l_view_name || ' WHERE person_id = ');
221 fnd_dsql.add_bind(p_person_id);
222 END IF;
223 fnd_dsql.add_text(' AND '|| l_where_clause);
224 ELSE
225 fnd_dsql.add_text(l_view_name || ' WHERE person_id = ');
226 fnd_dsql.add_bind(p_person_id);
227 END IF;
228 ELSE
229 fnd_dsql.add_text(' hz_parties WHERE party_id = ');
230 fnd_dsql.add_bind(p_person_id);
231 END IF;
232
233 ELSIF p_select_type = 'L' THEN
234 --
235 -- If the System Type is of type 'List' then get the query string.
236 --
237
238 OPEN c_att_id(l_list_id);
239 FETCH c_att_id INTO l_attach_fid;
240
241
242 IF c_att_id%NOTFOUND OR l_attach_fid IS NULL THEN
243 FND_MESSAGE.SET_NAME('IGF','IGF_AW_NO_LIST');
244 FND_MESSAGE.SET_TOKEN('LIST', l_list_id);
245 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
246 p_exception := 'Y';
247 CLOSE c_att_id;
248 RETURN;
249 END IF;
250
251 CLOSE c_att_id;
252 IGS_CO_GEN_004.get_list_query(l_attach_fid,l_query_text);
253
254 --** proc level logging.
255 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
256 IF (l_request_id IS NULL) THEN
257 l_request_id := fnd_global.conc_request_id;
258 END IF;
259 l_label := 'igs.plsql.igs_co_process.corp_build_sql_stmt.listquery';
260 l_debug_str := 'List Query Text :'||l_query_text;
261 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
262 END IF;
263 --**
264
265 IF l_query_text IS NULL THEN
266 p_exception := 'Y';
267 fnd_message.set_name('IGF','IGF_AW_NO_LIST');
268 fnd_message.set_token('LIST',p_list_id);
269 fnd_file.put_line(fnd_file.log,fnd_message.get());
270 fnd_file.put_line(FND_FILE.LOG,' ');
271 RETURN;
272 ELSE
273 l_query_str := 'SELECT distinct person_id FROM '|| '(' || l_query_text || ')';
274
275 IF l_where_clause IS NOT NULL THEN
276 IF p_sys_ltr_code = 'ADINTRW' THEN
277 fnd_dsql.add_text(l_view_name || ' WHERE '|| l_where_clause );
278 fnd_dsql.add_text(' AND panel_code = ' );
279 fnd_dsql.add_bind(p_parameter_5);
280 ELSE
281 fnd_dsql.add_text(l_view_name || ' WHERE '|| l_where_clause );
282 END IF;
283
284 --IF l_and_con IS NOT NULL THEN
285 IF l_query_str IS NOT NULL THEN
286 IF p_sys_ltr_code = 'ADINTRW' THEN
287 fnd_dsql.add_text(' AND person_id IN ('||l_query_str||' )');
288 fnd_dsql.add_text(' AND panel_code = ' );
289 fnd_dsql.add_bind(p_parameter_5);
290 ELSE
291 fnd_dsql.add_text('AND person_id IN ('||l_query_str||' )');
292 END IF;
293 END IF;
294 ELSE
295
296 fnd_dsql.add_text(l_view_name );
297
298 IF l_query_str IS NOT NULL THEN
299 IF p_sys_ltr_code IN ('ADADHOC','FAADHOC','GENERIC','ENADHOC','SFADHOC') THEN
300 fnd_dsql.add_text(' WHERE party_id IN(' || l_query_str ||' )');
301 ELSE
302 IF p_sys_ltr_code = 'ADINTRW' THEN
303 fnd_dsql.add_text(' WHERE person_id IN(' || l_query_str ||' )');
304 fnd_dsql.add_text(' AND panel_code = ' );
305 fnd_dsql.add_bind(p_parameter_5);
306 ELSE
307 fnd_dsql.add_text(' WHERE person_id IN(' || l_query_str ||' )');
308 END IF;
309 END IF;
310 END IF;
311 END IF;
312 END IF;
313
314 ELSIF p_select_type IN ('P','G') THEN
315 --
316 -- If the System Type is of type 'Parameter' then get the parameter values.
317 --
318 IF p_parameter_1 IS NOT NULL THEN
319
320 -- check whether the group is dynamic or not.
321 -- if file_name is NOT NULL means, the group is dynamic.
322 l_str := igs_pe_dynamic_persid_group.get_dynamic_sql(p_parameter_1 ,lv_status, l_group_type);
323 IF lv_status <> 'S' THEN
324 FND_MESSAGE.SET_NAME('IGF','IGF_AW_NO_QUERY');
325 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
326 p_exception := 'Y';
327 RETURN;
328 END IF;
329
330 IF l_group_type = 'STATIC' THEN
331 l_str := SUBSTR(l_str,1,INSTR(l_str,':p_GroupID')-1);
332 END IF;
333
334 END IF;
335
336 -- the l_str will hold the the select statement. If static bind parameter would be there.
337
338 IF l_where_clause IS NOT NULL THEN
339 IF p_sys_ltr_code = 'ADINTRW' THEN
340 IF l_str IS NOT NULL THEN
341 fnd_dsql.add_text(l_view_name || ' WHERE person_id IN ('||l_str);
342 IF l_group_type = 'STATIC' THEN
343 fnd_dsql.add_bind(p_parameter_1);
344 END IF;
345 fnd_dsql.add_text(') AND '||l_where_clause||' AND panel_code = ' );
346 fnd_dsql.add_bind(p_parameter_5);
347 ELSE
348 fnd_dsql.add_text(l_view_name || ' WHERE '||l_where_clause);
349 fnd_dsql.add_text(' AND panel_code = ' );
350 fnd_dsql.add_bind(p_parameter_5);
351 END IF;
352 ELSE
353 IF l_str IS NOT NULL THEN
354 fnd_dsql.add_text(l_view_name || ' WHERE person_id IN ('||l_str);
355 IF l_group_type = 'STATIC' THEN
356 fnd_dsql.add_bind(p_parameter_1);
357 END IF;
358 fnd_dsql.add_text(') AND '||l_where_clause);
359 ELSE
360 fnd_dsql.add_text(l_view_name || ' WHERE '||l_where_clause);
361 END IF;
362 END IF;
363 ELSE
364
365 -- adhoc letters will not have where clause
366
367 IF p_sys_ltr_code = 'ADINTRW' THEN
368 IF l_str IS NOT NULL THEN
369 fnd_dsql.add_text(l_view_name|| ' WHERE person_id IN ('||l_str);
370 IF l_group_type = 'STATIC' THEN
371 fnd_dsql.add_bind(p_parameter_1);
372 END IF;
373 fnd_dsql.add_text(') AND panel_code = ' );
374 fnd_dsql.add_bind(p_parameter_5);
375 ELSE
376 fnd_dsql.add_text(l_view_name|| ' WHERE ');
377 fnd_dsql.add_text(' panel_code = ' );
378 fnd_dsql.add_bind(p_parameter_5);
379 END IF;
380 ELSE
381 IF l_str IS NOT NULL THEN
382 IF p_sys_ltr_code IN ('ADADHOC','FAADHOC','GENERIC','ENADHOC','SFADHOC') THEN
383 fnd_dsql.add_text(l_view_name|| ' WHERE party_id IN ('||l_str);
384 IF l_group_type = 'STATIC' THEN
385 fnd_dsql.add_bind(p_parameter_1);
386 END IF;
387 fnd_dsql.add_text(')');
388 ELSE
389 fnd_dsql.add_text(l_view_name|| ' WHERE person_id IN ('||l_str);
390 IF l_group_type = 'STATIC' THEN
391 fnd_dsql.add_bind(p_parameter_1);
392 END IF;
393 fnd_dsql.add_text(')');
394 END IF;
395 ELSE
396 fnd_dsql.add_text(l_view_name);
397 END IF;
398 END IF;
399 END IF;
400
401 IF p_letter_type = 'SYSTEM' THEN
402 corp_get_parameter_value(
403 p_sys_ltr_code => p_sys_ltr_code,
404 p_parameter_1 => p_parameter_1,
405 p_parameter_2 => p_parameter_2,
406 p_parameter_3 => p_parameter_3,
407 p_parameter_4 => p_parameter_4,
408 p_parameter_5 => p_parameter_5,
409 p_parameter_6 => p_parameter_6,
410 p_parameter_7 => p_parameter_7,
411 p_parameter_8 => p_parameter_8,
412 p_parameter_9 => p_parameter_9,
413 p_parameter_value => l_parameter_value);
414 END IF;
415
416 END IF;
417
418 p_sql_stmt := fnd_dsql.get_text(FALSE);
419
420 --** proc level logging.
421 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
422
423 l_label := 'igs.plsql.igs_co_process.corp_build_sql_stmt';
424 l_debug_str := 'p_sql_stmt: '||p_sql_stmt;
425
426 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
427 END IF;
428 --**
429 EXCEPTION
430 WHEN OTHERS THEN
431 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
432 FND_MESSAGE.SET_TOKEN('NAME','IGS_CO_PROCESS.corp_build_sql_stmt'||'-'||SQLERRM);
433 IGS_GE_MSG_STACK.ADD;
434 APP_EXCEPTION.RAISE_EXCEPTION;
435 END corp_build_sql_stmt;
436
437 PROCEDURE corp_check_document_attributes(
438 p_map_id IN NUMBER,
439 p_elapsed_days OUT NOCOPY NUMBER,
440 p_no_of_repeats OUT NOCOPY NUMBER
441 ) AS
442 /*************************************************************
443 Created By :Nalin Kumar
444 Date Created on : 05-Feb-2002
445 Purpose : This procedure will check and return attributes assigned to a document.
446 Know limitations, enhancements or remarks
447 Change History
448 Who When What
449
450 (reverse chronological order - newest change first)
451 ***************************************************************/
452 CURSOR cur_c1 (cp_map_id NUMBER)IS
453 SELECT elapsed_days, repeat_times
454 FROM igs_co_mapping
455 WHERE map_id = TO_NUMBER(cp_map_id);
456 l_cur_c1 cur_c1%ROWTYPE;
457 BEGIN
458 OPEN cur_c1(p_map_id);
459 FETCH cur_c1 INTO l_cur_c1;
460 IF cur_c1%FOUND THEN
461 p_elapsed_days := l_cur_c1.elapsed_days;
462 p_no_of_repeats := l_cur_c1.repeat_times;
463 ELSE
464 p_elapsed_days := NULL;
465 p_no_of_repeats := NULL;
466 END IF;
467 CLOSE cur_c1;
468 EXCEPTION
469 WHEN OTHERS THEN
470 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
471 FND_MESSAGE.SET_TOKEN('NAME','IGS_CO_PROCESS.corp_check_document_attributes');
472 IGS_GE_MSG_STACK.ADD;
473 App_Exception.Raise_Exception;
474 END corp_check_document_attributes;
475
476 PROCEDURE corp_check_interaction_history(
477 p_person_id IN NUMBER,
478 p_sys_ltr_code IN VARCHAR2,
479 p_document_id IN NUMBER,
480 p_application_id IN NUMBER ,
481 p_course_cd IN VARCHAR2,
482 p_adm_seq_no IN NUMBER ,
483 p_awd_cal_type IN VARCHAR2,
484 p_awd_seq_no IN NUMBER ,
485 p_elapsed_days IN NUMBER,
486 p_no_of_repeats IN NUMBER,
487 p_send_letter OUT NOCOPY VARCHAR2
488 ) AS
489 /*************************************************************
490 Created By :Nalin Kumar
491 Date Created on : 05-Feb-2002
492 Purpose : This procedure will check interaction history and return a value to
493 inform whether a document can be sent or not.
494 Know limitations, enhancements or remarks
495 Change History
496 Who When What
497
498 (reverse chronological order - newest change first)
499 Pacross 12-Sep-2005 Included references into the IGS_CO_COV_LTR_RELS table, to ensure all
500 letters that were created as an edit of the current one are included in the
501 count
502 Bayadav 24-MAY-2002 Included two system letter codes 'ENADHOC', 'SFADHOC' for adhoc letters as
503 a part of bug 2376434
504 vskumar 30-May-2006 Xbuild3 performance fixes. Changed curosr queries for cur_c1, cur_adhoc1,
505 cur_adackmt1.
506 ***************************************************************/
507 CURSOR cur_c1_part1 (cp_document_id NUMBER,
508 cp_person_id NUMBER,
509 cp_application_id NUMBER,
510 cp_course_cd VARCHAR2,
511 cp_adm_seq_no NUMBER)IS
512 SELECT count(*) cnt, max(requested_date) max_requested_date
513 FROM igs_co_interaction_history_v
514 WHERE document_id = cp_document_id
515 and student_id = cp_person_id
516 and adm_application_number = cp_application_id
517 and nominated_course_cd = cp_course_cd
518 and sequence_number = cp_adm_seq_no
519 and ( comp_status = 'SUCCESS' OR
520 request_id IN (SELECT request_id FROM jtf_fm_status));
521
522 l_cur_c1_part1 cur_c1_part1%ROWTYPE;
523
524 CURSOR cur_c1_part2 (cp_document_id NUMBER,
525 cp_person_id NUMBER,
526 cp_application_id NUMBER,
527 cp_course_cd VARCHAR2,
528 cp_adm_seq_no NUMBER)IS
529 SELECT count(1) cnt, max(requested_date) max_requested_date
530 FROM igs_co_interaction_history_v
531 WHERE document_id in (SELECT CHILD_ITEM_ID
532 FROM IGS_CO_COV_LTR_RELS
533 WHERE BASE_ITEM_ID = cp_document_id)
534 and student_id = cp_person_id
535 and adm_application_number = cp_application_id
536 and nominated_course_cd = cp_course_cd
537 and sequence_number = cp_adm_seq_no
538 and ( comp_status = 'SUCCESS' OR
539 request_id IN (SELECT request_id FROM jtf_fm_status));
540
541 l_cur_c1_part2 cur_c1_part2%ROWTYPE;
542
543 CURSOR cur_adhoc1_part1 (cp_document_id NUMBER,
544 cp_person_id NUMBER)IS
545 SELECT count(1) cnt, max(requested_date) max_requested_date
546 FROM igs_co_interaction_history_v
547 WHERE document_id = cp_document_id
548 and student_id = cp_person_id
549 and ( comp_status = 'SUCCESS' OR
550 request_id IN (SELECT request_id FROM jtf_fm_status));
551
552
553 CURSOR cur_adhoc1_part2 (cp_document_id NUMBER,
554 cp_person_id NUMBER)IS
555 SELECT count(1) cnt, max(requested_date) max_requested_date
556 FROM igs_co_interaction_history_v
557 WHERE document_id in (SELECT CHILD_ITEM_ID
558 FROM IGS_CO_COV_LTR_RELS
559 WHERE BASE_ITEM_ID = cp_document_id)
560 and student_id = cp_person_id
561 and ( comp_status = 'SUCCESS' OR
562 request_id IN (SELECT request_id FROM jtf_fm_status));
563
564 l_cur_adhoc1_part1 cur_adhoc1_part1%ROWTYPE;
565 l_cur_adhoc1_part2 cur_adhoc1_part2%ROWTYPE;
566
567 CURSOR cur_adackmt1_part1 (cp_document_id NUMBER,
568 cp_person_id NUMBER,
569 cp_application_id NUMBER)IS
570 SELECT count(1) cnt, max(requested_date) max_requested_date
571 FROM igs_co_interaction_history_v
572 WHERE document_id = cp_document_id
573 and student_id = cp_person_id
574 and adm_application_number = cp_application_id
575 and ( comp_status = 'SUCCESS' OR
576 request_id IN (SELECT request_id FROM jtf_fm_status));
577
578 CURSOR cur_adackmt1_part2 (cp_document_id NUMBER,
579 cp_person_id NUMBER,
580 cp_application_id NUMBER)IS
581 SELECT count(1) cnt, max(requested_date) max_requested_date
582 FROM igs_co_interaction_history_v
583 WHERE document_id in (SELECT CHILD_ITEM_ID
584 FROM IGS_CO_COV_LTR_RELS
585 WHERE BASE_ITEM_ID = cp_document_id)
586 and student_id = cp_person_id
587 and adm_application_number = cp_application_id
588 and ( comp_status = 'SUCCESS' OR
589 request_id IN (SELECT request_id FROM jtf_fm_status));
590
591
592 l_cur_adackmt1_part1 cur_adackmt1_part1%ROWTYPE;
593 l_cur_adackmt1_part2 cur_adackmt1_part2%ROWTYPE;
594
595 --**
596 CURSOR cur_get_per_num(cp_person_id NUMBER) IS
597 SELECT person_number
598 FROM igs_pe_person_base_v
599 WHERE person_id = TO_NUMBER(cp_person_id);
600 l_cur_get_per_num cur_get_per_num%ROWTYPE;
601
602 l_count NUMBER(16);
603 l_requested_date DATE;
604 l_retcode NUMBER(1);
605 l_errbuf VARCHAR2(1000);
606 BEGIN
607
608 corp_check_request_status(
609 errbuf => l_errbuf,
610 retcode => l_retcode,
611 p_person_id => p_person_id ,
612 p_document_id => p_document_id ,
613 p_application_id => p_application_id ,
614 p_course_cd => p_course_cd ,
615 p_adm_seq_no => p_adm_seq_no ,
616 p_awd_cal_type => p_awd_cal_type ,
617 p_awd_seq_no => p_awd_seq_no ,
618 p_elapsed_days => p_elapsed_days ,
619 p_no_of_repeats => p_no_of_repeats ,
620 p_sys_ltr_code => p_sys_ltr_code);
621
622 IF p_sys_ltr_code NOT IN ('FAAWARD','FAMISTM','FADISBT','ADRESID','ADADHOC','FAADHOC','GENERIC','ADACKMT','ENADHOC','SFADHOC') THEN
623 OPEN cur_c1_part1(p_document_id,
624 p_person_id,
625 p_application_id,
626 p_course_cd,
627 p_adm_seq_no);
628 FETCH cur_c1_part1 INTO l_cur_c1_part1;
629 CLOSE cur_c1_part1;
630
631 OPEN cur_c1_part2(p_document_id,
632 p_person_id,
633 p_application_id,
634 p_course_cd,
635 p_adm_seq_no);
636 FETCH cur_c1_part2 INTO l_cur_c1_part2;
637 CLOSE cur_c1_part2;
638
639 l_count := l_cur_c1_part1.cnt + l_cur_c1_part2.cnt;
640
641
642 IF l_cur_c1_part1.max_requested_date > l_cur_c1_part2.max_requested_date THEN
643 l_requested_date := l_cur_c1_part1.max_requested_date;
644 ELSE
645 l_requested_date := l_cur_c1_part2.max_requested_date;
646 END IF;
647
648 ELSIF p_sys_ltr_code IN ('ADADHOC','FAADHOC','GENERIC','ADRESID','ENADHOC','SFADHOC') THEN
649
650 OPEN cur_adhoc1_part1(p_document_id,
651 p_person_id);
652 FETCH cur_adhoc1_part1 INTO l_cur_adhoc1_part1;
653 CLOSE cur_adhoc1_part1;
654
655 OPEN cur_adhoc1_part2(p_document_id,
656 p_person_id);
657 FETCH cur_adhoc1_part2 INTO l_cur_adhoc1_part2;
658 CLOSE cur_adhoc1_part2;
659
660 l_count := l_cur_adhoc1_part1.cnt + l_cur_adhoc1_part2.cnt;
661
662 IF l_cur_adhoc1_part1.max_requested_date > l_cur_adhoc1_part2.max_requested_date THEN
663 l_requested_date := l_cur_adhoc1_part1.max_requested_date;
664 ELSE
665 l_requested_date := l_cur_adhoc1_part2.max_requested_date;
666 END IF;
667
668 ELSIF p_sys_ltr_code = 'ADACKMT' THEN
669 OPEN cur_adackmt1_part1(p_document_id,
670 p_person_id,
671 p_application_id);
672 FETCH cur_adackmt1_part1 INTO l_cur_adackmt1_part1;
673 CLOSE cur_adackmt1_part1;
674
675 OPEN cur_adackmt1_part2(p_document_id,
676 p_person_id,
677 p_application_id);
678 FETCH cur_adackmt1_part2 INTO l_cur_adackmt1_part2;
679 CLOSE cur_adackmt1_part2;
680
681 l_count := l_cur_adackmt1_part1.cnt + l_cur_adackmt1_part2.cnt;
682
683 IF l_cur_adackmt1_part1.max_requested_date > l_cur_adackmt1_part2.max_requested_date THEN
684 l_requested_date := l_cur_adackmt1_part1.max_requested_date;
685 ELSE
686 l_requested_date := l_cur_adackmt1_part2.max_requested_date;
687 END IF;
688
689 END IF;
690 p_send_letter := 'FALSE';
691 IF p_elapsed_days IS NULL AND p_no_of_repeats IS NULL THEN
692 p_send_letter := 'TRUE';
693 RETURN;
694 END IF;
695 --GSCC FILE.DATE.5 Compliance 3071111 asbala
696 IF (TRUNC(SYSDATE) - TRUNC(l_requested_date)) < NVL(p_elapsed_days,0) THEN
697 p_send_letter := 'FALSE';
698 OPEN cur_get_per_num(p_person_id);
699 FETCH cur_get_per_num into l_cur_get_per_num;
700 fnd_message.set_name('IGS','IGS_CO_ELAPSED_DAYS');
701 fnd_message.set_token('PERSON',l_cur_get_per_num.person_number);
702 fnd_file.put_line(fnd_file.log,fnd_message.get());
703 fnd_file.put_line(FND_FILE.LOG,' ');
704 CLOSE cur_get_per_num;
705 ELSIF NVL(p_no_of_repeats,10000) <= l_count THEN
706 p_send_letter := 'FALSE';
707 OPEN cur_get_per_num(p_person_id);
708 FETCH cur_get_per_num into l_cur_get_per_num;
709 fnd_message.set_name('IGS','IGS_CO_NO_REPEATS');
710 fnd_message.set_token('PERSON',l_cur_get_per_num.person_number);
711 fnd_file.put_line(fnd_file.log,fnd_message.get());
712 fnd_file.put_line(FND_FILE.LOG,' ');
713 CLOSE cur_get_per_num;
714 ELSE
715 p_send_letter := 'TRUE';
716 END IF;
717 EXCEPTION
718 WHEN OTHERS THEN
719 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
720 FND_MESSAGE.SET_TOKEN('NAME','IGS_CO_PROCESS.corp_check_interaction_history');
721 IGS_GE_MSG_STACK.ADD;
722 App_Exception.Raise_Exception;
723 END corp_check_interaction_history;
724
725 PROCEDURE corp_submit_fulfil_request(
726 p_letter_type IN VARCHAR2,
727 p_person_id IN NUMBER,
728 p_email_address IN VARCHAR2,
729 p_content_id IN NUMBER,
730 p_award_year IN VARCHAR2, --New
731 p_sys_ltr_code IN VARCHAR2, --New
732 p_adm_appl_number IN NUMBER, --New
733 p_nominated_course_cd IN VARCHAR2, --New
734 p_appl_sequence_number IN NUMBER, --New
735 p_fulfillment_req IN VARCHAR2,
736 p_crm_user_id IN NUMBER,
737 p_media_type IN VARCHAR2,
738 p_destination IN VARCHAR2,
739 p_fax_number IN VARCHAR2, --New
740 p_reply_days IN VARCHAR2,
741 p_panel_code IN VARCHAR2,
742 p_request_id OUT NOCOPY NUMBER,
743 p_request_status OUT NOCOPY VARCHAR2,
744 p_reply_email IN VARCHAR2 ,
745 p_sender_email IN VARCHAR2 ,
746 p_cc_email IN VARCHAR2 ,
747 p_org_unit_id IN NUMBER,
748 p_preview IN VARCHAR2,
749 p_awd_cal_type IN VARCHAR2,
750 p_awd_ci_seq_number IN NUMBER,
751 p_awd_prd_cd IN VARCHAR2,
752 p_preview_version_id IN NUMBER,
753 p_preview_version IN NUMBER
754 ) AS
755 /*************************************************************
756 Created By :Nalin Kumar
757 Date Created on : 05-Feb-2002
758 Purpose : This procedure will accept parameters and submit fulfilment requests.
759 Know limitations, enhancements or remarks
760 Change History
761 Who When What
762 (reverse chronological order - newest change first)
763 pradhakr 13-Aug-2002 Added the parameter p_destination, which takes the
764 destination name (i.e) printer name if the media type
765 selected is printer. Changes as part of bug# 2472250
766 kpadiyar 19-NOV-2002 Added check if hold exists - Correspondence Enhancement - SWS102
767 Added check for relation between document and query and pass
768 the content type as relevant.
769 kumma 07-JUN-2003 2853531, Changed the cursor cur_get_sub to use the lookup type also
770 Replace the three CRM API calls jtf_fm_request_grp.start_request, jtf_fm_request_grp.get_content_xml and
771 jtf_fm_request_grp.submit_request into a single API Call of IGS_CO_API.SEND_REQUEST, which inturn calls
772 the create_fulfillment CRM API.
773 kumma 21-AUG-2003 3104787, Added the code to check if the query is attached with a template and accordingly pass the content type 'QUERY' or 'DATA'
774 Modified the else condition to not to consider the Adhoc letters while binding the bind variables.
775 ssaleem 09-SEP-2004 3630073. Added p_org_unit_id as a new parameter
776 pacross 11-APR-2005 Implemented code for Correspondance preview and edit fucntionality
777 ***************************************************************/
778 CURSOR cur_c1 (cp_content_id igs_co_mapping.document_id%TYPE,
779 cp_map_code igs_co_mapping.map_code%TYPE,
780 cp_sys_ltr_code igs_co_mapping.sys_ltr_code%TYPE) IS
781 SELECT map_description, version_id, citem_ver_id
782 FROM igs_co_mapping_v
783 WHERE document_id = cp_content_id
784 AND map_code = cp_map_code
785 AND sys_ltr_code = cp_sys_ltr_code
786 AND enable_flag ='Y';
787
788 l_cur_c1 cur_c1%ROWTYPE;
789
790 l_doc_desc igs_co_mapping_v.name%TYPE;
791 l_msg_count NUMBER;
792 l_msg_data VARCHAR2(2000);
793 l_content_xml VARCHAR2(5000);
794 l_varchar_tbl_bind_var jtf_fm_request_grp.g_varchar_tbl_type;
795 l_varchar_tbl_bind_var_type jtf_fm_request_grp.g_varchar_tbl_type;
796 l_varchar_tbl_bind_val jtf_fm_request_grp.g_varchar_tbl_type;
797 l_return_status VARCHAR2(30);
798 l_awd_cal_type igs_co_itm.cal_type%TYPE;
799 l_awd_seq_number igs_co_itm.ci_sequence_number%TYPE;
800 l_reply_date DATE;
801
802 CURSOR cur_get_sub(cp_sys_ltr_code VARCHAR2) IS
803 SELECT description
804 FROM igs_lookups_view
805 WHERE lookup_code = cp_sys_ltr_code AND
806 Lookup_type = 'IGS_CO_SYS_LTR_CODE';
807
808 l_cur_get_sub cur_get_sub%ROWTYPE;
809
810 CURSOR check_hold_exists IS
811 SELECT COUNT ('x')
812 FROM igs_pe_pers_encumb ppe, igs_pe_persenc_effct ppef
813 WHERE ppe.person_id = p_person_id
814 AND ppe.person_id = ppef.person_id
815 AND ppe.encumbrance_type = ppef.encumbrance_type
816 AND ppe.start_dt = ppef.pen_start_dt
817 AND ppef.s_encmb_effect_type = 'S_COR_BLK'
818 AND trunc(ppef.pee_start_dt) <= trunc(sysdate)
819 AND (ppef.expiry_dt IS NULL OR trunc(ppef.expiry_dt) > trunc(sysdate))
820 AND trunc(ppe.start_dt) <= trunc(sysdate)
821 AND (ppe.expiry_dt IS NULL OR trunc(ppe.expiry_dt) > trunc(sysdate));
822
823
824 l_hold_count NUMBER;
825
826 CURSOR log_details IS
827 SELECT ppbv.full_name,ppbv.person_number,fet.description
828 FROM igs_pe_pers_encumb ppe, igs_pe_persenc_effct ppef,igs_pe_person_base_v ppbv,igs_fi_encmb_type fet
829 WHERE ppe.person_id = p_person_id
830 AND ppe.person_id = ppef.person_id
831 AND ppe.person_id = ppbv.person_id
832 AND ppe.encumbrance_type = ppef.encumbrance_type
833 AND ppe.encumbrance_type = fet.encumbrance_type
834 AND ppe.start_dt = ppef.pen_start_dt
835 AND ppef.s_encmb_effect_type = 'S_COR_BLK'
836 AND trunc(ppef.pee_start_dt) <= trunc(sysdate)
837 AND (ppef.expiry_dt IS NULL OR trunc(ppef.expiry_dt) > trunc(sysdate))
838 AND trunc(ppe.start_dt) <= trunc(sysdate)
839 AND (ppe.expiry_dt IS NULL OR trunc(ppe.expiry_dt) > trunc(sysdate));
840
841 l_full_name igs_pe_person_base_v.full_name%TYPE;
842 l_person_number igs_pe_person_base_v.person_number%TYPE;
843 l_encumbrance_desc igs_fi_encmb_type.description%TYPE;
844
845 l_query_id jtf_fm_query_mes.query_id%TYPE;
846 l_content_type VARCHAR2(10);
847 l_tmp_var VARCHAR2(4000);
848 l_tmp_var1 VARCHAR2(4000);
849 l_version_id NUMBER;
850 -- Empty Arrays to reset the value
851 le_varchar_tbl_bind_var jtf_fm_request_grp.g_varchar_tbl_type;
852 le_varchar_tbl_bind_var_type jtf_fm_request_grp.g_varchar_tbl_type;
853 le_varchar_tbl_bind_val jtf_fm_request_grp.g_varchar_tbl_type;
854 l_query_exists VARCHAR2(1);
855 l_citem_ver_id NUMBER;
856 l_extended_header VARCHAR2(32000);
857 l_id VARCHAR2(500);
858
859 l_reply_days VARCHAR2(10);
860
861 -- Cursor to log the person's processed
862 CURSOR c_per_processed (p_person_id NUMBER) IS
863 SELECT person_number,full_name
864 FROM igs_pe_person_base_v
865 WHERe person_id = p_person_id;
866
867 --Cursor to get the citem_version_id
868 CURSOR c_check_relation (cp_item_id ibc_citem_versions_b.content_item_id%TYPE,
869 cp_version_id ibc_citem_versions_b.version_number%TYPE) IS
870 SELECT 'Y'
871 FROM ibc_compound_relations
872 WHERE CITEM_VERSION_ID = (SELECT CITEM_VERSION_ID FROM ibc_citem_versions_b
873 WHERE CONTENT_ITEM_ID = cp_item_id AND
874 VERSION_NUMBER = cp_version_id)
875 AND ATTRIBUTE_TYPE_CODE = 'QUERY'; -- ssawhney modified after OCM migration.
876
877 --Cursor to get the reply date
878 CURSOR c_reply_date(p_reply_days NUMBER) IS
879 SELECT SYSDATE + NVL(TO_NUMBER(p_reply_days),0)
880 FROM DUAL;
881
882 --Cursor to get the reply date
883 CURSOR c_intr_reply_date (
884 p_person_id igs_ad_panel_dtls.person_id%TYPE,
885 p_adm_appl_number igs_ad_panel_dtls.admission_appl_number%TYPE,
886 p_nominated_course_cd igs_ad_panel_dtls.nominated_course_cd%TYPE,
887 p_appl_sequence_number igs_ad_panel_dtls.sequence_number%TYPE,
888 p_panel_code igs_ad_panel_dtls.panel_code%TYPE)IS
889 SELECT NVL(MAX(ipl.INTERVIEW_DATE),MAX(ipm.INTERVIEW_DATE)) - NVL(TO_NUMBER(p_reply_days),0)
890 FROM igs_ad_panel_dtls ipl ,
891 igs_ad_pnmembr_dtls ipm
892 WHERE ipl.panel_dtls_id = ipm.panel_dtls_id AND
893 ipl.person_id = p_person_id AND
894 ipl.admission_appl_number =p_adm_appl_number AND
895 ipl.nominated_course_cd = p_nominated_course_cd AND
896 ipl.sequence_number = p_appl_sequence_number AND
897 ipl.panel_code = p_panel_code ;
898
899 -- Cursor to get address info of an Orgn Unit
900 CURSOR c_org_unit_addr(cp_party_id hz_parties.party_id%TYPE) IS
901 SELECT P.ADDRESS1, P.ADDRESS2, P.ADDRESS3, P.ADDRESS4, P.POSTAL_CODE,
902 P.PARTY_NAME, TERR.TERRITORY_SHORT_NAME COUNTRY, P.CITY,
903 P.STATE, P.PROVINCE, P.COUNTY
904 FROM
905 HZ_PARTIES P, FND_TERRITORIES_VL TERR
906 WHERE
907 P.PARTY_ID = cp_party_id AND
908 TERR.TERRITORY_CODE = P.COUNTRY ;
909
910 l_org_unit_addr c_org_unit_addr%ROWTYPE;
911
912 -- Cursor to obtain the primary email address of the primary address of an Orgn Unit
913 CURSOR c_org_unit_email(cp_id_flag hz_party_sites.IDENTIFYING_ADDRESS_FLAG%TYPE,
914 cp_owner_tbl hz_contact_points.OWNER_TABLE_NAME%TYPE,
915 cp_cnt_type hz_contact_points.CONTACT_POINT_TYPE%TYPE,
916 cp_prim_flag hz_contact_points.PRIMARY_FLAG%TYPE,
917 cp_party_id HZ_PARTY_SITES.PARTY_ID%TYPE) IS
918 SELECT
919 CPE.EMAIL_ADDRESS
920 FROM
921 HZ_PARTY_SITES PS,
922 HZ_CONTACT_POINTS CPE
923 WHERE
924 PS.PARTY_ID = cp_party_id AND
925 PS.IDENTIFYING_ADDRESS_FLAG = cp_id_flag AND
926 CPE.OWNER_TABLE_NAME = cp_owner_tbl AND
927 CPE.CONTACT_POINT_TYPE = cp_cnt_type AND
928 CPE.OWNER_TABLE_ID = PS.PARTY_SITE_ID AND
929 CPE.PRIMARY_FLAG = cp_prim_flag;
930
931 l_org_unit_email c_org_unit_email%ROWTYPE;
932
933 -- Cursor to obtain the Phone/Fax of an Organization Unit
934 CURSOR c_org_unit_phone (cp_id_flag hz_party_sites.IDENTIFYING_ADDRESS_FLAG%TYPE,
935 cp_owner_tbl hz_contact_points.OWNER_TABLE_NAME%TYPE,
936 cp_cnt_type hz_contact_points.CONTACT_POINT_TYPE%TYPE,
937 cp_status hz_contact_points.STATUS%TYPE,
938 cp_party_id hz_party_sites.PARTY_ID%TYPE,
939 cp_line_type1 hz_contact_points.PHONE_LINE_TYPE%TYPE,
940 cp_line_type2 hz_contact_points.PHONE_LINE_TYPE%TYPE) IS
941 SELECT
942 NVL (CPP.PHONE_AREA_CODE,'*') PHONE_AREA_CODE,
943 NVL (CPP.PHONE_COUNTRY_CODE,'*') PHONE_COUNTRY_CODE,
944 NVL (CPP.PHONE_NUMBER,'*') PHONE_NUMBER,
945 NVL (CPP.PHONE_EXTENSION,'*') PHONE_EXTENSION,
946 CPP.PHONE_LINE_TYPE
947 FROM
948 HZ_PARTY_SITES PS,
949 HZ_CONTACT_POINTS CPP
950 WHERE
951 PS.PARTY_ID = cp_party_id AND
952 PS.IDENTIFYING_ADDRESS_FLAG = cp_id_flag AND
953 CPP.OWNER_TABLE_NAME = cp_owner_tbl AND
954 CPP.CONTACT_POINT_TYPE = cp_cnt_type AND
955 CPP.PHONE_LINE_TYPE IN(cp_line_type1,cp_line_type2) AND
956 CPP.OWNER_TABLE_ID = PS.PARTY_SITE_ID AND
957 CPP.STATUS = cp_status
958 ORDER BY
959 CPP.PRIMARY_FLAG DESC ;
960
961 l_org_unit_phone c_org_unit_phone%ROWTYPE;
962 l_fax_count NUMBER;
963 l_phone_count NUMBER;
964
965 l_fax_val VARCHAR2(300);
966 l_phone_val VARCHAR2(300);
967
968 BEGIN
969 l_query_exists := 'N';
970 l_fax_count :=0;
971 l_phone_count := 0;
972 l_fax_val := NULL;
973 l_phone_val := NULL;
974
975 --MMKUMAR, bug 4681183
976 IF p_sys_ltr_code IN ('ADNORSP','ADINTRW') THEN
977 l_reply_days := p_reply_days;
978 ELSE
979 l_reply_days := null;
980 END IF;
981
982 --** proc level logging.
983 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
984 IF (l_request_id IS NULL) THEN
985 l_request_id := fnd_global.conc_request_id;
986 END IF;
987 l_label := 'igs.plsql.igs_co_process.corp_submit_fulfil_request.JUST_ENTERED_INSIDE_corp_submit_fulfil_request';
988 l_debug_str := 'values';
989 l_debug_str := l_debug_str || 'p_letter_type=' || p_letter_type;
990 l_debug_str := l_debug_str || ',p_person_id=' || p_person_id ;
991 l_debug_str := l_debug_str || ',p_email_address=' || p_email_address ;
992 l_debug_str := l_debug_str || ',p_content_id=' || p_content_id ;
993 l_debug_str := l_debug_str || ',p_award_year=' || p_award_year ;
994 l_debug_str := l_debug_str || ',p_sys_ltr_code=' || p_sys_ltr_code ;
995 l_debug_str := l_debug_str || ',p_adm_appl_number=' || p_adm_appl_number;
996 l_debug_str := l_debug_str || ',p_nominated_course_cd=' || p_nominated_course_cd ;
997 l_debug_str := l_debug_str || ',p_appl_sequence_number=' || p_appl_sequence_number;
998 l_debug_str := l_debug_str || ',p_fulfillment_req=' || p_fulfillment_req ;
999 l_debug_str := l_debug_str || ',p_crm_user_id=' || p_crm_user_id ;
1000 l_debug_str := l_debug_str || ',p_media_type=' || p_media_type ;
1001 l_debug_str := l_debug_str || ',p_destination=' || p_destination ;
1002 l_debug_str := l_debug_str || ',p_fax_number=' || p_fax_number ;
1003 l_debug_str := l_debug_str || ',p_reply_days=' || p_reply_days ;
1004 l_debug_str := l_debug_str || ',l_reply_days=' || l_reply_days ;
1005 l_debug_str := l_debug_str || ',p_panel_code=' || p_panel_code ;
1006 l_debug_str := l_debug_str || ',p_reply_email=' || p_reply_email ;
1007 l_debug_str := l_debug_str || ',p_sender_email=' || p_sender_email ;
1008 l_debug_str := l_debug_str || ',p_cc_email=' || p_cc_email ;
1009 l_debug_str := l_debug_str || ',p_org_unit_id=' || p_org_unit_id ;
1010 l_debug_str := l_debug_str || ',p_preview=' || p_preview ;
1011 l_debug_str := l_debug_str || ',p_awd_cal_type=' || p_awd_cal_type ;
1012 l_debug_str := l_debug_str || ',p_awd_ci_seq_number=' || p_awd_ci_seq_number ;
1013 l_debug_str := l_debug_str || ',p_awd_prd_cd=' || p_awd_prd_cd ;
1014
1015 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1016 END IF;
1017 --**
1018
1019 l_doc_desc := NULL;
1020 OPEN cur_c1(p_content_id,'DOCUMENT',p_sys_ltr_code);
1021 FETCH cur_c1 INTO l_cur_c1;
1022 l_doc_desc := l_cur_c1.map_description;
1023 l_version_id := l_cur_c1.version_id; --ssawhney IBC.C version concept changes
1024 l_citem_ver_id := l_cur_c1.citem_ver_id; --ssawhney IBC.C version concept changes
1025 CLOSE cur_c1;
1026
1027 -- If there was no mapping since this is an updated document, then use the passed in version id's.
1028 -- PACROSS - 11-SEP-2005
1029 IF p_preview = 'Y' AND l_version_id IS NULL AND l_citem_ver_id IS NULL
1030 AND p_preview_version IS NOT NULL AND p_preview_version_id IS NOT NULL THEN
1031 l_version_id := p_preview_version;
1032 l_citem_ver_id := p_preview_version_id;
1033 END IF;
1034
1035 --** proc level logging.
1036 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1037 IF (l_request_id IS NULL) THEN
1038 l_request_id := fnd_global.conc_request_id;
1039 END IF;
1040 l_label := 'igs.plsql.igs_co_process.corp_submit_fulfil_request.gotthevalues';
1041 l_debug_str := 'hurrreeeeeeee got the values';
1042 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1043 END IF;
1044 --**
1045
1046 IF l_doc_desc IS NULL THEN
1047 OPEN cur_get_sub(p_sys_ltr_code);
1048 FETCH cur_get_sub INTO l_cur_get_sub;
1049 l_doc_desc := l_cur_get_sub.description;
1050 CLOSE cur_get_sub;
1051 END IF;
1052 IF p_letter_type = 'SYSTEM' THEN
1053 OPEN check_hold_exists;
1054 FETCH check_hold_exists INTO l_hold_count;
1055 CLOSE check_hold_exists;
1056 END IF;
1057
1058 IF NVL(l_hold_count,0) = 0 THEN
1059 --
1060 -- To start the submit fulfilment request by invoking CRM API
1061 --
1062
1063
1064 jtf_fm_request_grp.start_request (
1065 p_api_version => 1,
1066 p_init_msg_list => 'T',
1067 x_return_status => l_return_status,
1068 x_msg_count => l_msg_count,
1069 x_msg_data => l_msg_data,
1070 x_request_id => p_request_id);
1071
1072 --
1073 -- To populate the bind parameters.
1074 --
1075
1076 --** proc level logging.
1077 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1078 IF (l_request_id IS NULL) THEN
1079 l_request_id := fnd_global.conc_request_id;
1080 END IF;
1081 l_label := 'igs.plsql.igs_co_process.corp_submit_fulfil_request.startrequest';
1082 l_debug_str := 'Person ID :' ||p_person_id ||
1083 'FA Calendar :'||l_awd_cal_type||'-'||l_awd_seq_number||
1084 'Appl Details :' ||p_adm_appl_number||'-'||p_appl_sequence_number||
1085 '-' ||p_nominated_course_cd||'-'||l_reply_date ||
1086 'Content ID :' ||p_content_id ||
1087 --'Content Type :'||l_content_type ||
1088 'Version :' ||l_version_id ||
1089 'Citem Ver Id :'||l_citem_ver_id ||
1090 'Return Status :' || l_return_status || '-' || l_msg_data;
1091 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1092 END IF;
1093 --**
1094
1095
1096 IF l_return_status IN ('E','U') THEN
1097 -- FND_MSG_PUB.Count_And_Get( p_count => l_msg_count, p_data => l_msg_data );
1098 -- FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data);
1099
1100 IF l_msg_count > 1 THEN
1101 FOR i IN 1..l_msg_count
1102 LOOP
1103 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1104 l_tmp_var1 := l_tmp_var1 || l_tmp_var;
1105 END LOOP;
1106 FND_FILE.PUT_LINE(FND_FILE.LOG,l_tmp_var1);
1107 ELSE
1108 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data ||'-' ||l_msg_count);
1109 END IF;
1110 RETURN;
1111 END IF;
1112
1113
1114 l_varchar_tbl_bind_var(1) := 'p_person_id';
1115 l_varchar_tbl_bind_var_type(1) := 'NUMBER';
1116 l_varchar_tbl_bind_val(1) := p_person_id;
1117
1118
1119 IF p_org_unit_id IS NOT NULL THEN
1120 OPEN c_org_unit_addr (p_org_unit_id);
1121 FETCH c_org_unit_addr INTO l_org_unit_addr;
1122 CLOSE c_org_unit_addr;
1123
1124 IF l_org_unit_addr.ADDRESS1 IS NOT NULL THEN
1125 OPEN c_org_unit_email ( 'Y','HZ_PARTY_SITES', 'EMAIL', 'Y',p_org_unit_id);
1126 FETCH c_org_unit_email INTO l_org_unit_email;
1127 CLOSE c_org_unit_email;
1128
1129 OPEN c_org_unit_phone ( 'Y','HZ_PARTY_SITES', 'PHONE', 'A',p_org_unit_id,'GEN','FAX');
1130 LOOP
1131 FETCH c_org_unit_phone INTO l_org_unit_phone;
1132 EXIT WHEN (c_org_unit_phone%NOTFOUND OR (l_fax_count = 3 AND l_phone_count = 3));
1133
1134 IF l_org_unit_phone.PHONE_LINE_TYPE = 'FAX' AND l_fax_count < 3 THEN
1135 l_fax_count := l_fax_count + 1;
1136 l_fax_val := l_fax_val || l_org_unit_phone.PHONE_COUNTRY_CODE || '-' ||
1137 l_org_unit_phone.PHONE_AREA_CODE || '-' ||
1138 l_org_unit_phone.PHONE_NUMBER || '-' ||
1139 l_org_unit_phone.PHONE_EXTENSION || ',';
1140 END IF;
1141
1142 IF l_org_unit_phone.PHONE_LINE_TYPE = 'GEN' AND l_phone_count < 3 THEN
1143 l_phone_count := l_phone_count + 1;
1144 l_phone_val := l_phone_val || l_org_unit_phone.PHONE_COUNTRY_CODE || '-' ||
1145 l_org_unit_phone.PHONE_AREA_CODE || '-' ||
1146 l_org_unit_phone.PHONE_NUMBER || '-' ||
1147 l_org_unit_phone.PHONE_EXTENSION || ',';
1148
1149 END IF;
1150
1151 END LOOP;
1152 CLOSE c_org_unit_phone;
1153
1154 IF l_phone_val IS NOT NULL THEN
1155 l_phone_val := SUBSTR(l_phone_val,0,LENGTH(l_phone_val)-1);
1156 END IF;
1157
1158 IF l_fax_val IS NOT NULL THEN
1159 l_fax_val := SUBSTR(l_fax_val,0,LENGTH(l_fax_val)-1);
1160 END IF;
1161 END IF;
1162 END IF;
1163
1164 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1165 IF (l_request_id IS NULL) THEN
1166 l_request_id := fnd_global.conc_request_id;
1167 END IF;
1168 l_label := 'igs.plsql.igs_co_process.corp_submit_fulfil_request.beforecontentxml';
1169 l_debug_str := 'p_org_phone :' ||l_phone_val ||
1170 ' p_org_fax :'||l_fax_val ||' p_org_address1 :'||l_org_unit_addr.ADDRESS1||' p_org_address2 :'||l_org_unit_addr.ADDRESS2||
1171 ' p_org_address3 :'||l_org_unit_addr.ADDRESS3||' p_org_address4 :'||l_org_unit_addr.ADDRESS4||
1172 ' p_org_party_name :'||l_org_unit_addr.PARTY_NAME||' p_org_postal_code :'||l_org_unit_addr.POSTAL_CODE||
1173 ' p_org_country :'||l_org_unit_addr.COUNTRY||' p_org_county :'||l_org_unit_addr.COUNTY||
1174 ' p_org_city :'||l_org_unit_addr.CITY||' p_org_province :'||l_org_unit_addr.PROVINCE||
1175 ' p_org_state :'||l_org_unit_addr.STATE||' p_org_email_address :'||l_org_unit_email.EMAIL_ADDRESS;
1176 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1177
1178 END IF;
1179
1180 l_varchar_tbl_bind_var(2) := 'p_org_phone';
1181 l_varchar_tbl_bind_var_type(2) := 'VARCHAR2';
1182 l_varchar_tbl_bind_val(2) := l_phone_val;
1183
1184 l_varchar_tbl_bind_var(3) := 'p_org_fax';
1185 l_varchar_tbl_bind_var_type(3) := 'VARCHAR2';
1186 l_varchar_tbl_bind_val(3) := l_fax_val;
1187
1188 l_varchar_tbl_bind_var(4) := 'p_org_address1';
1189 l_varchar_tbl_bind_var_type(4) := 'VARCHAR2';
1190 l_varchar_tbl_bind_val(4) := l_org_unit_addr.ADDRESS1;
1191
1192 l_varchar_tbl_bind_var(5) := 'p_org_address2';
1193 l_varchar_tbl_bind_var_type(5) := 'VARCHAR2';
1194 l_varchar_tbl_bind_val(5) := l_org_unit_addr.ADDRESS2;
1195
1196 l_varchar_tbl_bind_var(6) := 'p_org_address3';
1197 l_varchar_tbl_bind_var_type(6) := 'VARCHAR2';
1198 l_varchar_tbl_bind_val(6) := l_org_unit_addr.ADDRESS3;
1199
1200 l_varchar_tbl_bind_var(7) := 'p_org_address4';
1201 l_varchar_tbl_bind_var_type(7) := 'VARCHAR2';
1202 l_varchar_tbl_bind_val(7) := l_org_unit_addr.ADDRESS4;
1203
1204 l_varchar_tbl_bind_var(8) := 'p_org_party_name';
1205 l_varchar_tbl_bind_var_type(8) := 'VARCHAR2';
1206 l_varchar_tbl_bind_val(8) := l_org_unit_addr.PARTY_NAME;
1207
1208 l_varchar_tbl_bind_var(9) := 'p_org_postal_code';
1209 l_varchar_tbl_bind_var_type(9) := 'VARCHAR2';
1210 l_varchar_tbl_bind_val(9) := l_org_unit_addr.POSTAL_CODE;
1211
1212 l_varchar_tbl_bind_var(10) := 'p_org_country';
1213 l_varchar_tbl_bind_var_type(10) := 'VARCHAR2';
1214 l_varchar_tbl_bind_val(10) := l_org_unit_addr.COUNTRY;
1215
1216 l_varchar_tbl_bind_var(11) := 'p_org_county';
1217 l_varchar_tbl_bind_var_type(11) := 'VARCHAR2';
1218 l_varchar_tbl_bind_val(11) := l_org_unit_addr.COUNTY;
1219
1220 l_varchar_tbl_bind_var(12) := 'p_org_city';
1221 l_varchar_tbl_bind_var_type(12) := 'VARCHAR2';
1222 l_varchar_tbl_bind_val(12) := l_org_unit_addr.CITY;
1223
1224 l_varchar_tbl_bind_var(13) := 'p_org_province';
1225 l_varchar_tbl_bind_var_type(13) := 'VARCHAR2';
1226 l_varchar_tbl_bind_val(13) := l_org_unit_addr.PROVINCE;
1227
1228 l_varchar_tbl_bind_var(14) := 'p_org_state';
1229 l_varchar_tbl_bind_var_type(14) := 'VARCHAR2';
1230 l_varchar_tbl_bind_val(14) := l_org_unit_addr.STATE;
1231
1232 l_varchar_tbl_bind_var(15) := 'p_org_email_address';
1233 l_varchar_tbl_bind_var_type(15) := 'VARCHAR2';
1234 l_varchar_tbl_bind_val(15) := l_org_unit_email.EMAIL_ADDRESS;
1235
1236
1237 IF p_sys_ltr_code IN ('FAAWARD','FAMISTM','FADISBT') THEN
1238 IF p_award_year IS NOT NULL THEN
1239 l_awd_cal_type := SUBSTR (p_award_year,1, 10);
1240 l_awd_seq_number := TO_NUMBER(SUBSTR (p_award_year,11));
1241 END IF;
1242
1243 l_varchar_tbl_bind_var(16) := 'p_awd_cal_type';
1244 l_varchar_tbl_bind_var_type(16) := 'VARCHAR2';
1245 l_varchar_tbl_bind_val(16) := l_awd_cal_type;
1246 l_varchar_tbl_bind_var(17) := 'p_awd_seq_number';
1247 l_varchar_tbl_bind_var_type(17) := 'NUMBER';
1248 l_varchar_tbl_bind_val(17) := l_awd_seq_number;
1249
1250 ELSIF p_sys_ltr_code = 'ADACKMT' THEN
1251 l_varchar_tbl_bind_var(16) := 'p_adm_appl_number';
1252 l_varchar_tbl_bind_var_type(16) := 'NUMBER';
1253 l_varchar_tbl_bind_val(16) := p_adm_appl_number;
1254
1255 --kumma, 3104787, Added the following code to not to take adhoc letters
1256 ELSIF p_sys_ltr_code NOT IN ('ADRESID','ADADHOC','FAADHOC','GENERIC','ADRESID','ENADHOC','SFADHOC') THEN
1257 l_varchar_tbl_bind_var(16) := 'p_appl_sequence_number';
1258 l_varchar_tbl_bind_var_type(16) := 'NUMBER';
1259 l_varchar_tbl_bind_val(16) := p_appl_sequence_number;
1260
1261 l_varchar_tbl_bind_var(17) := 'p_adm_appl_number';
1262 l_varchar_tbl_bind_var_type(17) := 'NUMBER';
1263 l_varchar_tbl_bind_val(17) := p_adm_appl_number;
1264
1265 l_varchar_tbl_bind_var(18) := 'p_nominated_course_cd';
1266 l_varchar_tbl_bind_var_type(18) := 'VARCHAR2';
1267 l_varchar_tbl_bind_val(18) := p_nominated_course_cd;
1268
1269 IF p_sys_ltr_code = 'ADNORSP' THEN
1270 OPEN c_reply_date(l_reply_days) ;
1271 FETCH c_reply_date INTO l_reply_date ;
1272 CLOSE c_reply_date;
1273
1274 l_varchar_tbl_bind_var(19) := 'REPLY_DATE';
1275 l_varchar_tbl_bind_var_type(19) := 'DATE';
1276 l_varchar_tbl_bind_val(19) := l_reply_date;
1277 ELSIF p_sys_ltr_code = 'ADINTRW' THEN
1278
1279 OPEN c_intr_reply_date(p_person_id,
1280 p_adm_appl_number,
1281 p_nominated_course_cd,
1282 p_appl_sequence_number,
1283 p_panel_code) ;
1284 FETCH c_intr_reply_date INTO l_reply_date ;
1285 CLOSE c_intr_reply_date;
1286
1287 IF NVL(l_reply_date,SYSDATE) <= SYSDATE THEN
1288 l_reply_date := SYSDATE;
1289 END IF;
1290 l_varchar_tbl_bind_var(19) := 'p_panel_code';
1291 l_varchar_tbl_bind_var_type(19) := 'VARCHAR2';
1292 l_varchar_tbl_bind_val(19) := p_panel_code;
1293
1294 l_varchar_tbl_bind_var(20) := 'REPLY_DATE';
1295 l_varchar_tbl_bind_var_type(20) := 'DATE';
1296 l_varchar_tbl_bind_val(20) := l_reply_date;
1297
1298 END IF;
1299 END IF;
1300
1301
1302 --kumma, 3104787, Added the following code to check that if the content type should be query and data
1303 -- need to pass the version id because the table doesnt hold citem_ver_id
1304
1305 OPEN c_check_relation (p_content_id, l_version_id);
1306 FETCH c_check_relation INTO l_query_exists;
1307 CLOSE c_check_relation;
1308
1309 IF l_query_exists = 'Y' THEN
1310 l_content_type := 'QUERY';
1311 ELSE
1312 l_content_type := 'DATA';
1313
1314 l_varchar_tbl_bind_var := le_varchar_tbl_bind_var;
1315 l_varchar_tbl_bind_var_type := le_varchar_tbl_bind_var_type;
1316 l_varchar_tbl_bind_val := le_varchar_tbl_bind_val;
1317
1318 END IF;
1319
1320
1321
1322 --
1323 -- To Submit fulfilment request by invoking CRM API
1324 --
1325
1326 --** proc level logging.
1327 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1328 IF (l_request_id IS NULL) THEN
1329 l_request_id := fnd_global.conc_request_id;
1330 END IF;
1331 l_label := 'igs.plsql.igs_co_process.corp_submit_fulfil_request.CALLING_GET_CONTENT_XML WITH PARAMS';
1332 l_debug_str := 'values ;';
1333 l_debug_str := l_debug_str || 'p_content_id=' || p_content_id;
1334 l_debug_str := l_debug_str || 'p_media_type,=' || p_media_type;
1335 l_debug_str := l_debug_str || 'p_email_address,=' || p_email_address;
1336 l_debug_str := l_debug_str || 'p_destination,=' || p_destination;
1337 l_debug_str := l_debug_str || 'l_content_type,=' || l_content_type;
1338 l_debug_str := l_debug_str || 'p_request_id,=' || p_request_id;
1339 --l_debug_str := l_debug_str || 'l_content_xml,=' || l_content_xml;
1340 l_debug_str := l_debug_str || 'l_citem_ver_id,=' || l_citem_ver_id;
1341 l_debug_str := l_debug_str || 'p_fax_number,=' || p_fax_number;
1342 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1343 END IF;
1344 --**
1345
1346 jtf_fm_request_grp.get_content_xml (
1347 p_api_version => 1,
1348 p_init_msg_list => 'T',
1349 x_return_status => l_return_status,
1350 x_msg_count => l_msg_count,
1351 x_msg_data => l_msg_data,
1352 p_bind_var => l_varchar_tbl_bind_var,
1353 p_bind_val => l_varchar_tbl_bind_val,
1354 p_bind_var_type => l_varchar_tbl_bind_var_type,
1355 p_content_id => p_content_id,
1356 p_media_type => p_media_type,
1357 p_email => p_email_address,
1358 p_printer => p_destination,
1359 p_content_type => l_content_type,
1360 p_request_id => p_request_id,
1361 x_content_xml => l_content_xml,
1362 P_CONTENT_SOURCE => 'OCM',
1363 P_VERSION => l_citem_ver_id , -- CITEM version information IBC.C changes. THIS SHOULD NOT BE l_version_id
1364 p_fax => p_fax_number
1365 );
1366
1367 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_version_id ||' /'||l_content_xml);
1368 --** proc level logging.
1369 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1370 IF (l_request_id IS NULL) THEN
1371 l_request_id := fnd_global.conc_request_id;
1372 END IF;
1373 l_label := 'igs.plsql.igs_co_process.corp_submit_fulfil_request.aftercontentxml';
1374 l_debug_str := 'Content Type :' ||l_content_type ||
1375 'Return Status :'||l_return_status ||'-' ||l_msg_data;
1376 l_debug_str := l_debug_str || l_content_xml;
1377 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1378 END IF;
1379 --**
1380
1381
1382 IF l_return_status IN ('E','U') THEN
1383 --FND_MSG_PUB.Count_And_Get( p_count => l_msg_count, p_data => l_msg_data );
1384 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data);
1385
1386 IF l_msg_count > 1 THEN
1387 FOR i IN 1..l_msg_count
1388 LOOP
1389 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1390 l_tmp_var1 := l_tmp_var1 || l_tmp_var;
1391 END LOOP;
1392 FND_FILE.PUT_LINE(FND_FILE.LOG,l_tmp_var1);
1393 ELSE
1394 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data ||'-' ||l_msg_count);
1395 END IF;
1396 RETURN;
1397 END IF;
1398
1399 /*
1400 l_id := '[email protected]';
1401 l_extended_header:= '<extended_header><header_name>email_from_address</header_name><header_value>' || l_id || '</header_value>' ;
1402 l_extended_header:= l_extended_header||'<header_name>email_reply_to_address</header_name><header_value>' || l_id || '</header_value></extended_header>';
1403 */
1404
1405 IF l_org_unit_email.EMAIL_ADDRESS IS NOT NULL OR p_reply_email IS NOT NULL OR
1406 p_sender_email IS NOT NULL OR
1407 p_cc_email IS NOT NULL THEN
1408
1409 l_extended_header:= '<extended_header>';
1410
1411 IF p_reply_email IS NOT NULL THEN
1412 l_extended_header := l_extended_header || '<header_name>email_reply_to_address</header_name><header_value>' || p_reply_email || '</header_value>' ;
1413 ELSIF l_org_unit_email.EMAIL_ADDRESS IS NOT NULL THEN
1414 l_extended_header := l_extended_header || '<header_name>email_reply_to_address</header_name><header_value>' || l_org_unit_email.EMAIL_ADDRESS || '</header_value>' ;
1415 END IF;
1416
1417 IF p_sender_email IS NOT NULL THEN
1418 l_extended_header := l_extended_header || '<header_name>email_from_address</header_name><header_value>' || p_sender_email || '</header_value>' ;
1419 ELSIF l_org_unit_email.EMAIL_ADDRESS IS NOT NULL THEN
1420 l_extended_header := l_extended_header || '<header_name>email_from_address</header_name><header_value>' || l_org_unit_email.EMAIL_ADDRESS || '</header_value>' ;
1421 END IF;
1422
1423 IF p_cc_email IS NOT NULL THEN
1424 l_extended_header := l_extended_header || '<header_name>email_cc_address</header_name><header_value>' || p_cc_email || '</header_value>' ;
1425 END IF;
1426
1427 l_extended_header := l_extended_header || '</extended_header>';
1428
1429 END IF;
1430
1431 IF p_preview = 'Y' THEN
1432 -- Store away all of the parameters required for a preview so they can be used later to fulfill the request.
1433 -- PACROSS - 11-SEP-2005
1434
1435 --** proc level logging.
1436 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1437 IF (l_request_id IS NULL) THEN
1438 l_request_id := fnd_global.conc_request_id;
1439 END IF;
1440 l_label := 'igs.plsql.igs_co_process.corp_submit_fulfil_request.before_inserting_into_IGS_CO_PREV_REQS';
1441 l_debug_str := 'just going to insert into IGS_CO_PREV_REQS';
1442 l_debug_str := l_debug_str || 'conc_request_id='||fnd_global.conc_request_id;
1443 l_debug_str := l_debug_str || ',p_letter_type='||p_letter_type;
1444 l_debug_str := l_debug_str || ',p_person_id=' || p_person_id;
1445 l_debug_str := l_debug_str || ',p_email_address=' || p_email_address;
1446 l_debug_str := l_debug_str || ',p_content_id=' || p_content_id;
1447 l_debug_str := l_debug_str || ',p_content_id=' || p_content_id;
1448 l_debug_str := l_debug_str || ',p_award_year=' || p_award_year;
1449 l_debug_str := l_debug_str || ',p_sys_ltr_code=' || p_sys_ltr_code;
1450 l_debug_str := l_debug_str || ',p_adm_appl_number=' || p_adm_appl_number;
1451 l_debug_str := l_debug_str || ',p_nominated_course_cd=' || p_nominated_course_cd;
1452 l_debug_str := l_debug_str || ',p_appl_sequence_number=' || p_appl_sequence_number;
1453 l_debug_str := l_debug_str || ',p_fulfillment_req=' || p_fulfillment_req;
1454 l_debug_str := l_debug_str || ',p_crm_user_id=' || p_crm_user_id;
1455 l_debug_str := l_debug_str || ',p_media_type=' || p_media_type;
1456 l_debug_str := l_debug_str || ',p_destination=' || p_destination;
1457 l_debug_str := l_debug_str || ',p_fax_number=' || p_fax_number;
1458 l_debug_str := l_debug_str || ',p_reply_days=' || p_reply_days;
1459 l_debug_str := l_debug_str || ',l_reply_days=' || l_reply_days;
1460 l_debug_str := l_debug_str || ',p_panel_code=' || p_panel_code;
1461 l_debug_str := l_debug_str || ',p_reply_email=' || p_reply_email;
1462 l_debug_str := l_debug_str || ',p_sender_email=' || p_sender_email;
1463 l_debug_str := l_debug_str || ',p_cc_email=' || p_cc_email;
1464 l_debug_str := l_debug_str || ',p_org_unit_id=' || p_org_unit_id;
1465 l_debug_str := l_debug_str || ',p_awd_cal_type=' || p_awd_cal_type;
1466 l_debug_str := l_debug_str || ',p_awd_ci_seq_number=' || p_awd_ci_seq_number;
1467 l_debug_str := l_debug_str || ',l_citem_ver_id=' || l_citem_ver_id;
1468 l_debug_str := l_debug_str || ',l_citem_ver_id=' || l_citem_ver_id;
1469 l_debug_str := l_debug_str || ',l_doc_desc=' || l_doc_desc;
1470 --l_debug_str := l_debug_str || ',l_content_xml=' || l_content_xml;
1471 --l_debug_str := l_debug_str || ',l_content_xml=' || l_content_xml;
1472 l_debug_str := l_debug_str || ',p_request_id=' || p_request_id;
1473 --l_debug_str := l_debug_str || ',l_extended_header=' || l_extended_header;
1474 l_debug_str := l_debug_str || ',p_awd_prd_cd=' || p_awd_prd_cd;
1475
1476
1477 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1478 END IF;
1479 --**
1480
1481 BEGIN
1482
1483 INSERT INTO IGS_CO_PREV_REQS
1484 (CONCURRENT_REQUEST_ID, LETTER_TYPE_CODE, PERSON_ID,
1485 EMAIL_ADDRESS, ORIGINAL_CONTENT_ID, CURRENT_CONTENT_ID, AWARD_YEAR,
1486 SYS_LTR_CODE, ADM_APPL_NUMBER, NOMINATED_COURSE_CD, APPL_SEQUENCE_NUMBER,
1487 FULFILLMENT_REQ, CRM_USER_ID, MEDIA_TYPE_CODE, DESTINATION, FAX_NUMBER, REPLY_DAYS,
1488 PANEL_CODE, REPLY_EMAIL, SENDER_EMAIL, CC_EMAIL, ORG_UNIT_ID, AWD_CAL_TYPE,
1489 AWD_CI_SEQ_NUMBER, ORIGINAL_VERSION_ID, CURRENT_VERSION_ID, EMAIL_SUBJECT, ORIGINAL_CONTENT_XML,
1490 CURRENT_CONTENT_XML, FF_REQUEST_HIST_ID, EXTENDED_HEADER, DISTRIBUTION_ID, REQUEST_STATUS_CODE,
1491 OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1492 LAST_UPDATE_DATE, AWARD_PRD_CD)
1493 VALUES
1494 (fnd_global.conc_request_id, p_letter_type, p_person_id,
1495 p_email_address, p_content_id, p_content_id, p_award_year,
1496 p_sys_ltr_code, p_adm_appl_number, p_nominated_course_cd, p_appl_sequence_number,
1497 p_fulfillment_req, p_crm_user_id, p_media_type, p_destination, p_fax_number, l_reply_days,
1498 p_panel_code, p_reply_email, p_sender_email, p_cc_email, p_org_unit_id, p_awd_cal_type,
1499 p_awd_ci_seq_number, l_citem_ver_id, l_citem_ver_id, l_doc_desc, l_content_xml,
1500 l_content_xml, p_request_id, l_extended_header, NULL, 'CREATED', 1,
1501 FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.USER_ID, NULL, SYSDATE, p_awd_prd_cd);
1502
1503 EXCEPTION
1504 WHEN OTHERS THEN
1505
1506 --** proc level logging.
1507 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1508 IF (l_request_id IS NULL) THEN
1509 l_request_id := fnd_global.conc_request_id;
1510 END IF;
1511 l_label := 'igs.plsql.igs_co_process.corp_submit_fulfil_request.excep_when_insert';
1512 l_debug_str := 'inside exception section when inserting record in IGS_CO_PREV_REQS and exception is ' || sqlerrm;
1513 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1514 END IF;
1515 --**
1516 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
1517 FND_MESSAGE.SET_TOKEN('NAME','IGS_CO_PROCESS.corp_submit_fulfil_request');
1518 IGS_GE_MSG_STACK.ADD;
1519 App_Exception.Raise_Exception;
1520
1521 END;
1522 --** proc level logging.
1523 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1524 IF (l_request_id IS NULL) THEN
1525 l_request_id := fnd_global.conc_request_id;
1526 END IF;
1527 l_label := 'igs.plsql.igs_co_process.corp_submit_fulfil_request.after_inserting_into_IGS_CO_PREV_REQS';
1528 l_debug_str := 'just after insert into IGS_CO_PREV_REQS';
1529 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1530 END IF;
1531 --**
1532
1533
1534 --** proc level logging.
1535 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1536 IF (l_request_id IS NULL) THEN
1537 l_request_id := fnd_global.conc_request_id;
1538 END IF;
1539 l_label := 'igs.plsql.igs_co_process.corp_submit_fulfil_request.going_to_call_send_request';
1540 l_debug_str := 'calling send_request with parameters l_doc_desc='||l_doc_desc;
1541 l_debug_str := l_debug_str || ',p_crm_user_id=' || p_crm_user_id;
1542 l_debug_str := l_debug_str || ',l_content_xml=' || l_content_xml;
1543 l_debug_str := l_debug_str || ',p_request_id=' || p_request_id;
1544 l_debug_str := l_debug_str || ',p_person_id=' || p_person_id;
1545 l_debug_str := l_debug_str || ',l_extended_header=' || l_extended_header;
1546 l_debug_str := l_debug_str || ',p_content_id=' || p_content_id;
1547 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1548 END IF;
1549 --**
1550
1551 jtf_fm_request_grp.send_request (
1552 p_api_version => 1,
1553 p_init_msg_list => 'T',
1554 x_return_status => l_return_status,
1555 x_msg_count => l_msg_count,
1556 x_msg_data => l_msg_data,
1557 p_subject => l_doc_desc,
1558 p_user_id => p_crm_user_id,
1559 p_content_xml => l_content_xml,
1560 p_request_id => p_request_id,
1561 p_party_id => p_person_id,
1562 p_doc_id => p_request_id,
1563 p_extended_header => l_extended_header,
1564 p_doc_ref => to_char(p_content_id),
1565 p_preview => FND_API.G_TRUE
1566 );
1567
1568 --** proc level logging.
1569 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1570 IF (l_request_id IS NULL) THEN
1571 l_request_id := fnd_global.conc_request_id;
1572 END IF;
1573 l_label := 'igs.plsql.igs_co_process.corp_submit_fulfil_request.after_call_to_send_request';
1574 l_debug_str := 'just after call to send_request with status ' || l_return_status || ' and l_msg_data ' || l_msg_data;
1575 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1576 END IF;
1577 --**
1578
1579 ELSE
1580 -- Submit the request as per pre-preview and edit
1581
1582 jtf_fm_request_grp.submit_request (
1583 p_api_version => 1,
1584 p_init_msg_list => 'T',
1585 x_return_status => l_return_status,
1586 x_msg_count => l_msg_count,
1587 x_msg_data => l_msg_data,
1588 p_subject => l_doc_desc,
1589 p_user_id => p_crm_user_id,
1590 --p_server_id => p_fulfillment_req,
1591 p_content_xml => l_content_xml,
1592 p_request_id => p_request_id,
1593 p_party_id => p_person_id,
1594 p_doc_id => p_request_id,
1595 p_extended_header => l_extended_header, --ssawhney testing.
1596 p_doc_ref => to_char(p_content_id)
1597 );
1598 END IF;
1599
1600 IF l_return_status = 'S' THEN
1601
1602 p_request_status := 'SUBMITTED';
1603 OPEN c_per_processed(p_person_id);
1604 FETCH c_per_processed INTO l_person_number,l_full_name;
1605 Fnd_Message.Set_name('IGF','IGF_AW_PROC_STUD');
1606 FND_MESSAGE.SET_TOKEN('STDNT',l_person_number||' - '||l_full_name);
1607 fnd_file.put_line(fnd_file.log,fnd_message.get);
1608 Fnd_Message.Set_name('IGS','IGS_CO_REQ_INFO');
1609 FND_MESSAGE.SET_TOKEN('REQUEST_ID',p_request_id);
1610 fnd_file.put_line(fnd_file.log,fnd_message.get);
1611
1612 IF igs_co_process.l_message_logged THEN
1613 igs_co_process.l_message_logged := FALSE;
1614 END IF;
1615 CLOSE c_per_processed;
1616 ELSE
1617 p_request_status := 'FAILURE';
1618 -- FND_MSG_PUB.Count_And_Get( p_count => l_msg_count, p_data => l_msg_data );
1619 -- FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data);
1620
1621 IF l_msg_count > 1 THEN
1622 FOR i IN 1..l_msg_count
1623 LOOP
1624 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1625 l_tmp_var1 := l_tmp_var1 || l_tmp_var;
1626 END LOOP;
1627 FND_FILE.PUT_LINE(FND_FILE.LOG,l_tmp_var1);
1628 ELSE
1629 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data ||'-' ||l_msg_count);
1630 END IF;
1631
1632 END IF;
1633
1634 --** proc level logging.
1635 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1636 IF (l_request_id IS NULL) THEN
1637 l_request_id := fnd_global.conc_request_id;
1638 END IF;
1639 l_label := 'igs.plsql.igs_co_process.corp_submit_fulfil_request.aftersubmitrequest';
1640 l_debug_str := 'Request ID :' ||p_request_id ||
1641 'Return Status :' ||l_return_status ||'-' ||l_msg_data;
1642 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1643 END IF;
1644 --**
1645
1646 l_corp_submit_fulfil_request := FALSE;
1647 COMMIT;
1648 ELSE
1649 l_corp_submit_fulfil_request := TRUE;
1650 IF NOT igs_co_process.l_message_logged THEN
1651 fnd_message.set_name('IGS','IGS_CO_HOLD_EXISTS');
1652 fnd_file.put_line(fnd_file.log,fnd_message.get);
1653 igs_co_process.l_message_logged := TRUE;
1654 END IF;
1655 OPEN log_details; LOOP
1656 FETCH log_details INTO l_full_name,l_person_number,l_encumbrance_desc;
1657 EXIT WHEN log_details%NOTFOUND;
1658 fnd_file.put_line(fnd_file.log,rpad(l_person_number,20,' ')||' '||rpad(l_full_name,50,' ')||' '||l_encumbrance_desc);
1659 END LOOP;
1660 CLOSE log_details;
1661 END IF;
1662 EXCEPTION
1663 WHEN OTHERS THEN
1664
1665 --** proc level logging.
1666 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1667 IF (l_request_id IS NULL) THEN
1668 l_request_id := fnd_global.conc_request_id;
1669 END IF;
1670 l_label := 'igs.plsql.igs_co_process.corp_submit_fulfil_request.inside_excep_section';
1671 l_debug_str := 'inside exception section and exception is ' || sqlerrm;
1672 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1673 END IF;
1674 --**
1675 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
1676 FND_MESSAGE.SET_TOKEN('NAME','IGS_CO_PROCESS.corp_submit_fulfil_request');
1677 IGS_GE_MSG_STACK.ADD;
1678 App_Exception.Raise_Exception;
1679 END corp_submit_fulfil_request;
1680
1681 PROCEDURE corp_post_process(
1682 p_person_id IN NUMBER,
1683 p_request_id IN NUMBER,
1684 p_document_id IN NUMBER,
1685 p_sys_ltr_code IN VARCHAR2,
1686 p_document_type IN VARCHAR2,
1687 p_adm_appl_number IN NUMBER,
1688 p_nominated_course_cd IN VARCHAR2,
1689 p_appl_seq_number IN NUMBER,
1690 p_awd_cal_type IN VARCHAR2,
1691 p_awd_ci_seq_number IN NUMBER,
1692 p_award_year IN VARCHAR2,
1693 p_delivery_type IN VARCHAR2,
1694 p_version_id IN NUMBER,
1695 p_award_prd_cd IN VARCHAR2
1696 ) AS
1697 /*************************************************************
1698 Created By :Nalin Kumar
1699 Date Created on : 05-Feb-2002
1700 Purpose : This procedure will perform post-processing.
1701 Know limitations, enhancements or remarks
1702 Change History
1703 Who When What
1704
1705 (reverse chronological order - newest change first)
1706 ***************************************************************/
1707 l_rowid VARCHAR2(25);
1708 BEGIN
1709
1710 IF p_sys_ltr_code = 'FAAWARD' THEN
1711 igf_aw_gen_004.award_letter_update(
1712 p_person_id => p_person_id,
1713 p_award_year => p_award_year,
1714 p_award_prd_cd => p_award_prd_cd);
1715 ELSIF P_SYS_LTR_CODE = 'FAMISTM' THEN
1716 igf_aw_gen_004.missing_items_update(
1717 p_person_id => p_person_id,
1718 p_award_year => p_award_year);
1719 --Commented by Prajeesh as the Disbursement Update was happening before the CRM process it
1720 --pick up the record. As the record is getting updated first when CRM tries to pick, It is
1721 --Unable to pick up the record hence raises an Unhandled Exception.
1722 /*ELSIF P_SYS_LTR_CODE = 'FADISBT' THEN
1723 igf_aw_gen_004.loan_disbursement_update(
1724 p_person_id => p_person_id,
1725 p_award_year => p_award_year);*/
1726 END IF;
1727
1728 --
1729 -- Insert the record into the igs_co_interac_hist table with the all relevant
1730 -- details and status from 'p_request_status'.
1731 --
1732 l_rowid := NULL;
1733 igs_co_interac_hist_pkg.insert_row(
1734 x_rowid => l_rowid,
1735 x_student_id => TO_NUMBER(p_person_id),
1736 x_request_id => TO_NUMBER(p_request_id),
1737 x_document_id => TO_NUMBER(p_document_id),
1738 x_document_type => p_document_type,
1739 x_sys_ltr_code => p_sys_ltr_code,
1740 x_adm_application_number => p_adm_appl_number,
1741 x_nominated_course_cd => p_nominated_course_cd,
1742 x_sequence_number => p_appl_seq_number,
1743 x_cal_type => p_awd_cal_type,
1744 x_ci_sequence_number => p_awd_ci_seq_number,
1745 x_requested_date => SYSDATE,
1746 x_delivery_type => p_delivery_type,
1747 x_version_id => p_version_id
1748 );
1749
1750 --** proc level logging.
1751 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1752 IF (l_request_id IS NULL) THEN
1753 l_request_id := fnd_global.conc_request_id;
1754 END IF;
1755 l_label := 'igs.plsql.igs_co_process.corp_post_process.afterinteractioninsert';
1756 l_debug_str := 'Doc id :' ||p_document_id || 'Version Id :' ||p_version_id ||
1757 'Person Id :'||p_person_id;
1758 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1759 END IF;
1760 --**
1761
1762 EXCEPTION
1763 WHEN OTHERS THEN
1764 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
1765 FND_MESSAGE.SET_TOKEN('NAME','IGS_CO_PROCESS.corp_post_process');
1766 IGS_GE_MSG_STACK.ADD;
1767 App_Exception.Raise_Exception;
1768 END corp_post_process;
1769
1770 PROCEDURE corp_get_system_letter_view(
1771 p_sys_ltr_code IN VARCHAR2,
1772 p_view_name OUT NOCOPY VARCHAR2,
1773 p_where_clause OUT NOCOPY VARCHAR2
1774 ) AS
1775 /*************************************************************
1776 Created By :Nalin Kumar
1777 Date Created on : 05-Feb-2002
1778 Purpose : This procedure returns the view name for the system letter code.
1779 Know limitations, enhancements or remarks
1780 Change History
1781 Who When What
1782
1783 (reverse chronological order - newest change first)
1784 Bayadav 24-MAY-2002 Included two system letter codes 'ENADHOC', 'SFADHOC' for adhoc letters as a part of bug 2376434
1785 ***************************************************************/
1786 BEGIN
1787 IF p_sys_ltr_code IN ('ADADHOC','FAADHOC','GENERIC','ENADHOC','SFADHOC') THEN
1788 p_view_name := ' hz_parties';
1789 ELSIF p_sys_ltr_code = 'ADMISTM' THEN
1790 p_view_name := ' igs_ad_missing_items_letter_v';
1791 p_where_clause := ' s_adm_outcome_status <> ''CANCELLED''';
1792 ELSIF p_sys_ltr_code = 'ADACKMT' THEN
1793 p_view_name := ' igs_ad_ack_letter_v';
1794 p_where_clause := ' s_adm_outcome_status <> ''CANCELLED''';
1795 ELSIF p_sys_ltr_code = 'ADRESID' THEN
1796 p_view_name := ' igs_ad_resi_letter_v';
1797 p_where_clause := ' 1=1 ';
1798 ELSIF p_sys_ltr_code = 'ADACCEP' THEN
1799 p_view_name := ' igs_ad_outcome_letters_v';
1800 p_where_clause := ' s_adm_outcome_status = ''OFFER''
1801 AND previous_term_adm_appl_number IS NULL
1802 AND previous_term_sequence_number IS NULL ';
1803 ELSIF p_sys_ltr_code = 'ADREJEC' THEN
1804 p_view_name := ' igs_ad_outcome_letters_v';
1805 p_where_clause := ' s_adm_outcome_status = ''REJECTED''';
1806 ELSIF p_sys_ltr_code = 'ADWAITL' THEN
1807 p_view_name := ' igs_ad_outcome_letters_v';
1808 p_where_clause := ' s_adm_outcome_status = ''WAITLIST''';
1809 ELSIF p_sys_ltr_code = 'ADNOQUT' THEN
1810 p_view_name := ' igs_ad_outcome_letters_v';
1811 p_where_clause := ' s_adm_outcome_status = ''NO-QUOTA''';
1812 ELSIF p_sys_ltr_code = 'ADFUTSE' THEN
1813 p_view_name := ' igs_ad_outcome_letters_v';
1814 p_where_clause := ' s_adm_outcome_status = ''OFFER-FUTURE-TERM''';
1815 ELSIF p_sys_ltr_code = 'ADCONOF' THEN
1816 p_view_name := ' igs_ad_outcome_letters_v';
1817 p_where_clause := ' s_adm_outcome_status = ''COND-OFFER''';
1818 ELSIF p_sys_ltr_code = 'ADPADMS' THEN
1819 p_view_name := ' igs_ad_postadm_miss_itm_ltr_v';
1820 p_where_clause := ' s_adm_outcome_status <> ''CANCELLED''';
1821 ELSIF p_sys_ltr_code = 'ADMFTSA' THEN
1822 p_view_name := ' igs_ad_outcome_letters_v';
1823 p_where_clause := ' s_adm_outcome_status = ''OFFER''
1824 AND previous_term_adm_appl_number IS NOT NULL
1825 AND previous_term_sequence_number IS NOT NULL ';
1826 ELSIF p_sys_ltr_code = 'FAAWARD' THEN
1827 p_view_name := ' igf_aw_per_list_v';
1828 ELSIF p_sys_ltr_code = 'FAMISTM' THEN
1829 p_view_name := ' igf_ap_mis_itms_ltr_v';
1830 ELSIF p_sys_ltr_code = 'FADISBT' THEN
1831 p_view_name := ' igf_sl_disb_ltr_v';
1832 ELSIF p_sys_ltr_code = 'ADNORSP' THEN
1833 p_view_name := ' igs_ad_outcome_letters_v';
1834 p_where_clause := ' s_adm_outcome_status IN (''OFFER'',''COND-OFFER'') '||
1835 ' AND S_ADM_OFFER_RESP_STATUS = ''PENDING'' '||
1836 ' AND SYSDATE > OFFER_RESPONSE_DT ';
1837 ELSIF p_sys_ltr_code = 'ADINTRW' THEN
1838 p_view_name := ' igs_ad_interview_letters_v';
1839 p_where_clause := '1=1';
1840 END IF;
1841
1842 EXCEPTION
1843 WHEN OTHERS THEN
1844 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
1845 FND_MESSAGE.SET_TOKEN('NAME','IGS_CO_PROCESS.corp_get_system_letter_view');
1846 IGS_GE_MSG_STACK.ADD;
1847 App_Exception.Raise_Exception;
1848 END corp_get_system_letter_view;
1849
1850 PROCEDURE corp_get_parameter_value(
1851 p_sys_ltr_code IN VARCHAR2,
1852 p_parameter_1 IN VARCHAR2,
1853 p_parameter_2 IN VARCHAR2,
1854 p_parameter_3 IN VARCHAR2,
1855 p_parameter_4 IN VARCHAR2,
1856 p_parameter_5 IN VARCHAR2,
1857 p_parameter_6 IN VARCHAR2,
1858 p_parameter_7 IN VARCHAR2,
1859 p_parameter_8 IN VARCHAR2,
1860 p_parameter_9 IN VARCHAR2,
1861 p_parameter_value OUT NOCOPY VARCHAR2
1862 ) AS
1863 /*************************************************************
1864 Created By :Nalin Kumar
1865 Date Created on : 05-Feb-2002
1866 Purpose : This procedure accepts 5 parameters and builds a where
1867 clause for student selection.
1868 Know limitations, enhancements or remarks
1869 Change History
1870 Who When What
1871 ssaleem 28-OCT-2003 Bug : 3198795
1872 Part of the Dynamic/Static Person Groups modifications,
1873 In places where person group id is included to the SQL, a condition 1=1 is replaced.
1874 npalanis 23-OCT-2002 Bug : 2608360
1875 residency_status_id and residency_class_id is being removed from igs_ad_resi_letter_v
1876 and the code class is being moved to igs_lookups therefore those are changed to
1877 residency_status_cd and residency_class_cd.
1878 pkpatel 7-MAy-2003 Bug 2940810
1879 Modified for Bind Variable
1880 (reverse chronological order - newest change first)
1881 ***************************************************************/
1882
1883 l_pers_group_id igs_ad_missing_items_letter_v.pers_group_id%TYPE;
1884 l_acad_cal_type VARCHAR(15); --igs_ad_missing_items_letter_v.acad_cal_type%TYPE;
1885 l_acad_ci_sequence_number igs_ad_missing_items_letter_v.acad_ci_sequence_number%TYPE;
1886 l_adm_cal_type VARCHAR(15); --igs_ad_ps_appl_inst.adm_cal_type%TYPE;
1887 l_adm_ci_sequence_number igs_ad_ps_appl_inst.adm_ci_sequence_number%TYPE;
1888 l_parameter_7 hz_parties.party_id%TYPE;
1889 l_parameter_8 igs_co_interac_hist.adm_application_number%TYPE;
1890 l_parameter_9 igs_co_interac_hist.nominated_course_cd%TYPE;
1891 l_parameter_10 igs_co_interac_hist.ci_sequence_number%TYPE;
1892
1893 BEGIN
1894 --** proc level logging.
1895 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1896
1897 l_label := 'igs.plsql.igs_co_process.corp_get_parameter_value';
1898 l_debug_str := 'p_sys_ltr_code:'||p_sys_ltr_code;
1899 l_debug_str := l_debug_str ||' p_parameter_1 :'||p_parameter_1;
1900 l_debug_str := l_debug_str ||' p_parameter_2 :'||p_parameter_2;
1901 l_debug_str := l_debug_str ||' p_parameter_3 :'||p_parameter_3;
1902 l_debug_str := l_debug_str ||' p_parameter_4 :'||p_parameter_4;
1903 l_debug_str := l_debug_str ||' p_parameter_5 :'||p_parameter_5;
1904 l_debug_str := l_debug_str ||' p_parameter_6 :'||p_parameter_6;
1905 l_debug_str := l_debug_str ||' p_parameter_7 :'||p_parameter_7;
1906 l_debug_str := l_debug_str ||' p_parameter_8 :'||p_parameter_8;
1907 l_debug_str := l_debug_str ||' p_parameter_9 :'||p_parameter_9;
1908
1909 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1910 END IF;
1911 --**
1912
1913 fnd_dsql.add_text(' AND ');
1914
1915 IF p_parameter_1 IS NOT NULL THEN
1916 l_pers_group_id := TO_NUMBER(p_parameter_1);
1917 END IF;
1918
1919 IF p_sys_ltr_code <> 'ADRESID' THEN
1920 IF p_parameter_1 IS NULL THEN
1921 l_acad_cal_type := RTRIM(SUBSTR (p_parameter_2,101, 10));
1922 l_acad_ci_sequence_number := TO_NUMBER(SUBSTR (p_parameter_2,112));
1923 l_adm_cal_type :=RTRIM(SUBSTR (p_parameter_3, 1, 10));
1924 l_adm_ci_sequence_number := TO_NUMBER(SUBSTR (p_parameter_3,11));
1925 END IF;
1926 END IF;
1927
1928 IF p_sys_ltr_code IN('ADPADMS','ADMISTM') THEN
1929 l_parameter_7 := TO_NUMBER(SUBSTR (p_parameter_8,1,15)); --person_id
1930 l_parameter_8 := TO_NUMBER(SUBSTR (p_parameter_8,16,2)); --admission_appl_number
1931 l_parameter_9 := RTRIM(SUBSTR (p_parameter_8,18,6)); --nominated_course_cd
1932 l_parameter_10:= TO_NUMBER(SUBSTR (p_parameter_8,24,6)); --sequence_number
1933 END IF;
1934
1935 IF p_sys_ltr_code = 'ADMISTM' THEN
1936 IF p_parameter_1 IS NOT NULL THEN
1937 fnd_dsql.add_text(' 1=1 ' );
1938 ELSE
1939 fnd_dsql.add_text(' acad_cal_type = ');
1940 fnd_dsql.add_bind(l_acad_cal_type);
1941 fnd_dsql.add_text(' AND acad_ci_sequence_number = ');
1942 fnd_dsql.add_bind(l_acad_ci_sequence_number);
1943 fnd_dsql.add_text(' AND adm_cal_type = ');
1944 fnd_dsql.add_bind(l_adm_cal_type);
1945 fnd_dsql.add_text(' AND adm_ci_sequence_number =');
1946 fnd_dsql.add_bind(l_adm_ci_sequence_number);
1947 fnd_dsql.add_text(' AND adm_process_cat =');
1948 fnd_dsql.add_bind(p_parameter_4);
1949 fnd_dsql.add_text(' AND adm_doc_status = ');
1950 fnd_dsql.add_bind(p_parameter_5);
1951 fnd_dsql.add_text(' AND org_unit_cd = ');
1952 fnd_dsql.add_bind(p_parameter_6);
1953 fnd_dsql.add_text(' AND person_id = ');
1954 fnd_dsql.add_bind(l_parameter_7);
1955 fnd_dsql.add_text(' AND adm_appl_number = ');
1956 fnd_dsql.add_bind(l_parameter_8);
1957 fnd_dsql.add_text(' AND nominated_course_cd = ');
1958 fnd_dsql.add_bind(l_parameter_9);
1959 fnd_dsql.add_text(' AND appl_sequence_number = ');
1960 fnd_dsql.add_bind(l_parameter_10);
1961 END IF;
1962
1963 ELSIF P_SYS_LTR_CODE = 'ADACKMT' THEN
1964 IF p_parameter_1 IS NOT NULL THEN
1965 fnd_dsql.add_text(' 1=1 ' );
1966 ELSE
1967 fnd_dsql.add_text(' acad_cal_type = ');
1968 fnd_dsql.add_bind(l_acad_cal_type);
1969 fnd_dsql.add_text(' AND acad_ci_sequence_number = ');
1970 fnd_dsql.add_bind(l_acad_ci_sequence_number);
1971 fnd_dsql.add_text(' AND adm_cal_type = ');
1972 fnd_dsql.add_bind(l_adm_cal_type);
1973 fnd_dsql.add_text(' AND adm_ci_sequence_number = ');
1974 fnd_dsql.add_bind(l_adm_ci_sequence_number);
1975 fnd_dsql.add_text(' AND adm_process_cat = ');
1976 fnd_dsql.add_bind(p_parameter_4);
1977 fnd_dsql.add_text(' AND TRUNC(appl_dt) >= TRUNC(igs_ge_date.igsdate(');
1978 fnd_dsql.add_bind(p_parameter_5);
1979 fnd_dsql.add_text(')) AND TRUNC(appl_dt) <= TRUNC(igs_ge_date.igsdate(');
1980 fnd_dsql.add_bind(p_parameter_6);
1981 fnd_dsql.add_text('))');
1982 END IF;
1983 ELSIF P_SYS_LTR_CODE = 'ADRESID' THEN
1984 IF p_parameter_1 IS NOT NULL THEN
1985 fnd_dsql.add_text(' 1=1 ' );
1986 ELSE
1987 fnd_dsql.add_text(' residency_status_cd = ');
1988 fnd_dsql.add_bind(p_parameter_2);
1989 fnd_dsql.add_text(' AND residency_class_cd = ');
1990 fnd_dsql.add_bind(p_parameter_3);
1991 fnd_dsql.add_text(' AND TRUNC(evaluation_date) >= TRUNC(igs_ge_date.igsdate( ');
1992 fnd_dsql.add_bind(p_parameter_4);
1993 fnd_dsql.add_text(')) AND TRUNC(evaluation_date) <= TRUNC(igs_ge_date.igsdate( ');
1994 fnd_dsql.add_bind(p_parameter_5);
1995 fnd_dsql.add_text('))');
1996 END IF;
1997 ELSIF P_SYS_LTR_CODE = 'ADACCEP' THEN
1998 IF p_parameter_1 IS NOT NULL THEN
1999 fnd_dsql.add_text(' 1=1 ' );
2000 ELSE
2001 fnd_dsql.add_text(' acad_cal_type = ');
2002 fnd_dsql.add_bind(l_acad_cal_type);
2003 fnd_dsql.add_text(' AND acad_ci_sequence_number = ');
2004 fnd_dsql.add_bind(l_acad_ci_sequence_number);
2005 fnd_dsql.add_text(' AND adm_cal_type = ');
2006 fnd_dsql.add_bind(l_adm_cal_type);
2007 fnd_dsql.add_text(' AND adm_ci_sequence_number = ');
2008 fnd_dsql.add_bind(l_adm_ci_sequence_number);
2009 fnd_dsql.add_text(' AND admission_process_category = ');
2010 fnd_dsql.add_bind(p_parameter_4);
2011 fnd_dsql.add_text(' AND TRUNC(appl_dt) >= TRUNC(igs_ge_date.igsdate(');
2012 fnd_dsql.add_bind(p_parameter_5);
2013 fnd_dsql.add_text(' )) AND TRUNC(appl_dt) <= TRUNC(igs_ge_date.igsdate(');
2014 fnd_dsql.add_bind(p_parameter_6);
2015 fnd_dsql.add_text(' )) AND admission_outcome_status = ');
2016 fnd_dsql.add_bind(p_parameter_7);
2017 END IF;
2018
2019 ELSIF P_SYS_LTR_CODE = 'ADREJEC' THEN
2020 IF p_parameter_1 IS NOT NULL THEN
2021 fnd_dsql.add_text(' 1=1 ' );
2022 ELSE
2023 fnd_dsql.add_text(' acad_cal_type = ');
2024 fnd_dsql.add_bind(l_acad_cal_type);
2025 fnd_dsql.add_text(' AND acad_ci_sequence_number = ');
2026 fnd_dsql.add_bind(l_acad_ci_sequence_number);
2027 fnd_dsql.add_text(' AND adm_cal_type = ');
2028 fnd_dsql.add_bind(l_adm_cal_type);
2029 fnd_dsql.add_text(' AND adm_ci_sequence_number = ');
2030 fnd_dsql.add_bind(l_adm_ci_sequence_number);
2031 fnd_dsql.add_text(' AND admission_process_category = ');
2032 fnd_dsql.add_bind(p_parameter_4);
2033 fnd_dsql.add_text(' AND TRUNC(appl_dt) >= TRUNC(igs_ge_date.igsdate( ');
2034 fnd_dsql.add_bind(p_parameter_5);
2035 fnd_dsql.add_text(')) AND TRUNC(appl_dt) <= TRUNC(igs_ge_date.igsdate( ');
2036 fnd_dsql.add_bind(p_parameter_6);
2037 fnd_dsql.add_text(')) AND admission_outcome_status = ');
2038 fnd_dsql.add_bind(p_parameter_7);
2039 END IF;
2040 ELSIF P_SYS_LTR_CODE = 'ADWAITL' THEN
2041 IF p_parameter_1 IS NOT NULL THEN
2042 fnd_dsql.add_text(' 1=1 ' );
2043 ELSE
2044 fnd_dsql.add_text(' acad_cal_type = ');
2045 fnd_dsql.add_bind(l_acad_cal_type);
2046 fnd_dsql.add_text(' AND acad_ci_sequence_number = ');
2047 fnd_dsql.add_bind(l_acad_ci_sequence_number);
2048 fnd_dsql.add_text(' AND adm_cal_type = ');
2049 fnd_dsql.add_bind(l_adm_cal_type);
2050 fnd_dsql.add_text(' AND adm_ci_sequence_number = ');
2051 fnd_dsql.add_bind(l_adm_ci_sequence_number);
2052 fnd_dsql.add_text(' AND admission_process_category = ');
2053 fnd_dsql.add_bind(p_parameter_4);
2054 fnd_dsql.add_text(' AND TRUNC(appl_dt) >= TRUNC(igs_ge_date.igsdate(');
2055 fnd_dsql.add_bind(p_parameter_5);
2056 fnd_dsql.add_text(' )) AND TRUNC(appl_dt) <= TRUNC(igs_ge_date.igsdate(');
2057 fnd_dsql.add_bind(p_parameter_6);
2058 fnd_dsql.add_text(' )) AND admission_outcome_status = ');
2059 fnd_dsql.add_bind(p_parameter_7);
2060 END IF;
2061 ELSIF P_SYS_LTR_CODE = 'ADNOQUT' THEN
2062 IF p_parameter_1 IS NOT NULL THEN
2063 fnd_dsql.add_text(' 1=1 ' );
2064 ELSE
2065 fnd_dsql.add_text(' acad_cal_type = ');
2066 fnd_dsql.add_bind(l_acad_cal_type);
2067 fnd_dsql.add_text(' AND acad_ci_sequence_number = ');
2068 fnd_dsql.add_bind(l_acad_ci_sequence_number);
2069 fnd_dsql.add_text(' AND adm_cal_type = ');
2070 fnd_dsql.add_bind(l_adm_cal_type);
2071 fnd_dsql.add_text(' AND adm_ci_sequence_number = ');
2072 fnd_dsql.add_bind(l_adm_ci_sequence_number);
2073 fnd_dsql.add_text(' AND admission_process_category = ');
2074 fnd_dsql.add_bind(p_parameter_4);
2075 fnd_dsql.add_text(' AND TRUNC(appl_dt) >= TRUNC(igs_ge_date.igsdate(');
2076 fnd_dsql.add_bind(p_parameter_5);
2077 fnd_dsql.add_text(' )) AND TRUNC(appl_dt) <= TRUNC(igs_ge_date.igsdate(');
2078 fnd_dsql.add_bind(p_parameter_6);
2079 fnd_dsql.add_text(' )) AND admission_outcome_status = ');
2080 fnd_dsql.add_bind(p_parameter_7);
2081 END IF;
2082 ELSIF P_SYS_LTR_CODE = 'ADCONOF' THEN
2083 IF p_parameter_1 IS NOT NULL THEN
2084 fnd_dsql.add_text(' 1=1 ' );
2085 ELSE
2086 fnd_dsql.add_text(' acad_cal_type = ');
2087 fnd_dsql.add_bind(l_acad_cal_type);
2088 fnd_dsql.add_text(' AND acad_ci_sequence_number = ');
2089 fnd_dsql.add_bind(l_acad_ci_sequence_number);
2090 fnd_dsql.add_text(' AND adm_cal_type = ');
2091 fnd_dsql.add_bind(l_adm_cal_type);
2092 fnd_dsql.add_text(' AND adm_ci_sequence_number = ');
2093 fnd_dsql.add_bind(l_adm_ci_sequence_number);
2094 fnd_dsql.add_text(' AND admission_process_category = ');
2095 fnd_dsql.add_bind(p_parameter_4);
2096 fnd_dsql.add_text(' AND TRUNC(appl_dt) >= TRUNC(igs_ge_date.igsdate(');
2097 fnd_dsql.add_bind(p_parameter_5);
2098 fnd_dsql.add_text(' )) AND TRUNC(appl_dt) <= TRUNC(igs_ge_date.igsdate(');
2099 fnd_dsql.add_bind(p_parameter_6);
2100 fnd_dsql.add_text(' )) AND admission_outcome_status = ');
2101 fnd_dsql.add_bind(p_parameter_7);
2102 END IF;
2103 ELSIF P_SYS_LTR_CODE = 'ADPADMS' THEN
2104 IF p_parameter_1 IS NOT NULL THEN
2105 fnd_dsql.add_text(' 1=1 ' );
2106 ELSE
2107 fnd_dsql.add_text(' acad_cal_type = ');
2108 fnd_dsql.add_bind(l_acad_cal_type);
2109 fnd_dsql.add_text(' AND acad_ci_sequence_number = ');
2110 fnd_dsql.add_bind(l_acad_ci_sequence_number);
2111 fnd_dsql.add_text(' AND adm_cal_type = ');
2112 fnd_dsql.add_bind(l_adm_cal_type);
2113 fnd_dsql.add_text(' AND adm_ci_sequence_number = ');
2114 fnd_dsql.add_bind(l_adm_ci_sequence_number);
2115 fnd_dsql.add_text(' AND adm_process_cat = ');
2116 fnd_dsql.add_bind(p_parameter_4);
2117 fnd_dsql.add_text(' AND adm_doc_status = ');
2118 fnd_dsql.add_bind(p_parameter_5);
2119 fnd_dsql.add_text(' AND org_unit_cd = ');
2120 fnd_dsql.add_bind(p_parameter_6);
2121 fnd_dsql.add_text(' AND person_id = ');
2122 fnd_dsql.add_bind(l_parameter_7);
2123 fnd_dsql.add_text(' AND adm_appl_number = ');
2124 fnd_dsql.add_bind(l_parameter_8);
2125 fnd_dsql.add_text(' AND nominated_course_cd = ');
2126 fnd_dsql.add_bind(l_parameter_9);
2127 fnd_dsql.add_text(' AND appl_sequence_number = ');
2128 fnd_dsql.add_bind(l_parameter_10);
2129 END IF;
2130 ELSIF P_SYS_LTR_CODE = 'ADFUTSE' THEN
2131 IF p_parameter_1 IS NOT NULL THEN
2132 fnd_dsql.add_text(' 1=1 ' );
2133 ELSE
2134 fnd_dsql.add_text(' acad_cal_type = ');
2135 fnd_dsql.add_bind(l_acad_cal_type);
2136 fnd_dsql.add_text(' AND acad_ci_sequence_number = ');
2137 fnd_dsql.add_bind(l_acad_ci_sequence_number);
2138 fnd_dsql.add_text(' AND adm_cal_type = ');
2139 fnd_dsql.add_bind(l_adm_cal_type);
2140 fnd_dsql.add_text(' AND adm_ci_sequence_number = ');
2141 fnd_dsql.add_bind(l_adm_ci_sequence_number);
2142 fnd_dsql.add_text(' AND admission_process_category = ');
2143 fnd_dsql.add_bind(p_parameter_4);
2144 fnd_dsql.add_text(' AND TRUNC(appl_dt) >= TRUNC(igs_ge_date.igsdate(');
2145 fnd_dsql.add_bind(p_parameter_5);
2146 fnd_dsql.add_text(' )) AND TRUNC(appl_dt) <= TRUNC(igs_ge_date.igsdate(');
2147 fnd_dsql.add_bind(p_parameter_6);
2148 fnd_dsql.add_text(' )) AND admission_outcome_status = ');
2149 fnd_dsql.add_bind(p_parameter_7);
2150 END IF;
2151 ELSIF P_SYS_LTR_CODE = 'ADMFTSA' THEN
2152 IF p_parameter_1 IS NOT NULL THEN
2153 fnd_dsql.add_text(' 1=1 ' );
2154 ELSE
2155 fnd_dsql.add_text(' acad_cal_type = ');
2156 fnd_dsql.add_bind(l_acad_cal_type);
2157 fnd_dsql.add_text(' AND acad_ci_sequence_number = ');
2158 fnd_dsql.add_bind(l_acad_ci_sequence_number);
2159 fnd_dsql.add_text(' AND adm_cal_type = ');
2160 fnd_dsql.add_bind(l_adm_cal_type);
2161 fnd_dsql.add_text(' AND adm_ci_sequence_number = ');
2162 fnd_dsql.add_bind(l_adm_ci_sequence_number);
2163 fnd_dsql.add_text(' AND admission_process_category = ');
2164 fnd_dsql.add_bind(p_parameter_4);
2165 fnd_dsql.add_text(' AND TRUNC(appl_dt) >= TRUNC(igs_ge_date.igsdate(');
2166 fnd_dsql.add_bind(p_parameter_5);
2167 fnd_dsql.add_text(' )) AND TRUNC(appl_dt) <= TRUNC(igs_ge_date.igsdate(');
2168 fnd_dsql.add_bind(p_parameter_6);
2169 fnd_dsql.add_text(' )) AND admission_outcome_status = ');
2170 fnd_dsql.add_bind(p_parameter_7);
2171 END IF;
2172 ELSIF P_SYS_LTR_CODE = 'ADNORSP' THEN
2173 IF p_parameter_1 IS NOT NULL THEN
2174 -- person id group if dynamic will not be available.
2175 -- all persons in the group already resolved in the build_sql
2176 fnd_dsql.add_text(' 1=1');
2177 ELSE
2178 --** proc level logging.
2179 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2180
2181 l_label := 'igs.plsql.igs_co_process.corp_get_parameter_value';
2182 l_debug_str := 'p_parameter_1 is NULL ';
2183 l_debug_str := l_debug_str ||' p_parameter_7 :'||p_parameter_7;
2184 l_debug_str := l_debug_str ||' p_parameter_8 :'||p_parameter_8;
2185 l_debug_str := l_debug_str ||' p_parameter_9 :'||p_parameter_9;
2186
2187 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
2188 END IF;
2189 --**
2190 fnd_dsql.add_text(' acad_cal_type = ');
2191 fnd_dsql.add_bind(l_acad_cal_type);
2192 fnd_dsql.add_text(' AND acad_ci_sequence_number = ');
2193 fnd_dsql.add_bind(l_acad_ci_sequence_number);
2194 fnd_dsql.add_text(' AND adm_cal_type = ');
2195 fnd_dsql.add_bind(l_adm_cal_type);
2196 fnd_dsql.add_text(' AND adm_ci_sequence_number = ');
2197 fnd_dsql.add_bind(l_adm_ci_sequence_number);
2198 fnd_dsql.add_text(' AND admission_process_category = ');
2199 fnd_dsql.add_bind(p_parameter_4);
2200 fnd_dsql.add_text(' AND course_cd = ');
2201 fnd_dsql.add_bind(p_parameter_5);
2202 fnd_dsql.add_text(' AND location_cd = ');
2203 fnd_dsql.add_bind(p_parameter_6);
2204 fnd_dsql.add_text(' AND attendance_mode = ');
2205 fnd_dsql.add_bind(p_parameter_7);
2206 fnd_dsql.add_text(' AND attendance_type=');
2207 fnd_dsql.add_bind(p_parameter_9);
2208 END IF;
2209 ELSIF P_SYS_LTR_CODE = 'ADINTRW' THEN
2210 IF p_parameter_1 IS NOT NULL THEN
2211 -- person id group if dynamic will not be available.
2212 -- all persons in the group already resolved in the build_sql
2213 fnd_dsql.add_text(' 1=1');
2214 ELSE
2215 fnd_dsql.add_text(' acad_cal_type = ');
2216 fnd_dsql.add_bind(l_acad_cal_type);
2217 fnd_dsql.add_text(' AND acad_ci_sequence_number = ');
2218 fnd_dsql.add_bind(l_acad_ci_sequence_number);
2219 fnd_dsql.add_text(' AND adm_cal_type = ');
2220 fnd_dsql.add_bind(l_adm_cal_type);
2221 fnd_dsql.add_text(' AND adm_ci_sequence_number =');
2222 fnd_dsql.add_bind(l_adm_ci_sequence_number);
2223 fnd_dsql.add_text(' AND admission_process_category =');
2224 fnd_dsql.add_bind(p_parameter_4);
2225 fnd_dsql.add_text(' AND panel_code= ');
2226 fnd_dsql.add_bind(p_parameter_5);
2227 fnd_dsql.add_text(' AND TRUNC(interview_date) = TRUNC(igs_ge_date.igsdate(');
2228 fnd_dsql.add_bind(p_parameter_6);
2229 fnd_dsql.add_text(' )) AND attendance_mode= ');
2230 fnd_dsql.add_bind(p_parameter_7);
2231 fnd_dsql.add_text(' AND attendance_type= ');
2232 fnd_dsql.add_bind(p_parameter_9);
2233 fnd_dsql.add_text(' AND interview_date > SYSDATE');
2234 END IF;
2235 END IF;
2236 EXCEPTION
2237 WHEN OTHERS THEN
2238 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
2239 FND_MESSAGE.SET_TOKEN('NAME','IGS_CO_PROCESS.corp_get_parameter_value');
2240 IGS_GE_MSG_STACK.ADD;
2241 App_Exception.Raise_Exception;
2242 END corp_get_parameter_value;
2243
2244 PROCEDURE corp_check_request_status(
2245 errbuf OUT NOCOPY VARCHAR2,
2246 retcode OUT NOCOPY NUMBER,
2247 p_person_id IN NUMBER ,
2248 p_document_id IN NUMBER ,
2249 p_application_id IN NUMBER ,
2250 p_course_cd IN VARCHAR2,
2251 p_adm_seq_no IN NUMBER ,
2252 p_awd_cal_type IN VARCHAR2,
2253 p_awd_seq_no IN NUMBER ,
2254 p_elapsed_days IN NUMBER ,
2255 p_no_of_repeats IN NUMBER ,
2256 p_sys_ltr_code IN VARCHAR2
2257 ) AS
2258 /*************************************************************
2259 Created By :Nalin Kumar
2260 Date Created on : 05-Feb-2002
2261 Purpose : This procedure will check the request status in OSS Interaction Table
2262 against CRM Interaction History and update the OSS Interaction table.
2263 Know limitations, enhancements or remarks
2264 Change History
2265 Who When What
2266
2267 (reverse chronological order - newest change first)
2268 Bayadav 24-MAY-2002 Included two system letter codes 'ENADHOC', 'SFADHOC' for adhoc letters as a part of bug 2376434
2269 kpadiyar 18-NOV-2002 Removed the calls to the igs_co_interac_hist_pkg.update_row as the only column being updated was the outcome_status column
2270 and as this column has been obsoleted all the relevant calls to the update row have been removed.
2271 gmaheswa 5-Jan-2004 Bug 4869737 Added a call to SET_ORG_ID to disable OSS for R12.
2272 ***************************************************************/
2273 CURSOR c_crm_id(cp_request_id NUMBER) IS
2274 SELECT outcome_code --INTO l_request_status
2275 FROM jtf_fm_request_history
2276 WHERE hist_req_id = TO_NUMBER(cp_request_id);
2277 l_c_crm_id c_crm_id%ROWTYPE;
2278
2279 CURSOR cur_int_hist(cp_request_id NUMBER) IS
2280 SELECT hist.rowid row_id, hist.*
2281 FROM igs_co_interac_hist hist
2282 WHERE request_id = TO_NUMBER(cp_request_id);
2283 l_cur_int_hist cur_int_hist%ROWTYPE;
2284
2285 CURSOR cur_gen_update IS
2286 SELECT request_id,
2287 comp_status
2288 FROM igs_co_interaction_history_v
2289 WHERE comp_status IN ('SUBMITTED'); --Modified by Prajeesh to change NOT IN to IN as it will never change SUBMITED TO OTHER
2290 --STATE if it is NOT IN operator
2291 l_cur_gen_update cur_gen_update%ROWTYPE;
2292 l_called_from_conc VARCHAR2(1);
2293 BEGIN
2294 igs_ge_gen_003.set_org_id;
2295
2296 l_called_from_conc := 'N';
2297 IF p_person_id IS NULL AND
2298 (p_application_id IS NULL AND p_adm_seq_no IS NULL AND p_course_cd IS NULL) AND
2299 (p_awd_cal_type IS NULL AND p_awd_seq_no IS NULL) THEN
2300 --
2301 -- Update the status for all the records.
2302 --
2303 retcode := 0;
2304 l_called_from_conc := 'Y';
2305 OPEN cur_gen_update;
2306 LOOP
2307 FETCH cur_gen_update INTO l_cur_gen_update;
2308 IF cur_gen_update%FOUND THEN
2309 OPEN c_crm_id (l_cur_gen_update.request_id);
2310 LOOP
2311 FETCH c_crm_id INTO l_c_crm_id;
2312 IF c_crm_id%FOUND THEN
2313 IF l_cur_gen_update.comp_status <> l_c_crm_id.outcome_code THEN
2314 OPEN cur_int_hist(l_cur_gen_update.request_id);
2315 FETCH cur_int_hist INTO l_cur_int_hist;
2316 --Modified by Prajeesh on 23-apr-2002 as the disbursement update was happening before the
2317 --letter is actually picked up the fulfilment server of CRM. Thus it will always fail to
2318 -- pick up the record and hence gives an error. This is solved by initially not updating the status
2319 -- and next time when run update the status at the intiaial if it successfully sent by CRM Server.
2320 IF l_cur_int_hist.sys_ltr_code='FADISBT' AND l_c_crm_id.outcome_code='SUCCESS' THEN
2321 igf_aw_gen_004.loan_disbursement_update(
2322 p_person_id => l_cur_int_hist.student_id,
2323 p_award_year => RPAD(l_cur_int_hist.cal_type,10)||to_char(l_cur_int_hist.ci_sequence_number,'999999')
2324 );
2325 END IF;
2326 CLOSE cur_int_hist;
2327 END IF;
2328 ELSE
2329 EXIT WHEN c_crm_id%NOTFOUND;
2330 END IF;
2331 END LOOP;
2332 CLOSE c_crm_id;
2333 ELSE
2334 EXIT WHEN cur_gen_update%NOTFOUND;
2335 END IF;
2336 END LOOP;
2337 CLOSE cur_gen_update;
2338
2339 END IF;
2340
2341 EXCEPTION
2342 WHEN OTHERS THEN
2343 IF l_called_from_conc = 'Y' THEN
2344 ROLLBACK;
2345 RETCODE:=2;
2346 ERRBUF:= Fnd_Message.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
2347 Igs_Ge_Msg_Stack.CONC_EXCEPTION_HNDL;
2348 ELSE
2349 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
2350 FND_MESSAGE.SET_TOKEN('NAME','IGS_CO_PROCESS.corp_check_request_status');
2351 IGS_GE_MSG_STACK.ADD;
2352 App_Exception.Raise_Exception;
2353 END IF;
2354 END corp_check_request_status;
2355
2356 PROCEDURE corp_validate_parameters(
2357 p_sys_ltr_code IN VARCHAR2,
2358 p_document_id IN NUMBER,
2359 p_select_type IN VARCHAR2,
2360 p_list_id IN NUMBER ,
2361 p_person_id IN NUMBER ,
2362 p_parameter_1 IN VARCHAR2,
2363 p_parameter_2 IN VARCHAR2,
2364 p_parameter_3 IN VARCHAR2,
2365 p_parameter_4 IN VARCHAR2,
2366 p_parameter_5 IN VARCHAR2,
2367 p_parameter_6 IN VARCHAR2,
2368 p_parameter_7 IN VARCHAR2,
2369 p_parameter_8 IN VARCHAR2,
2370 p_parameter_9 IN VARCHAR2,
2371 p_override_flag IN VARCHAR2,
2372 p_delivery_type IN VARCHAR2,
2373 p_exception OUT NOCOPY VARCHAR2
2374 ) AS
2375 /*************************************************************
2376 Created By :Nalin Kumar
2377 Date Created on : 05-Feb-2002
2378 Purpose : This procedure will return true or false based on the validation.
2379 Know limitations, enhancements or remarks
2380 Change History
2381 Who When What
2382
2383 (reverse chronological order - newest change first)
2384 ***************************************************************/
2385 l_error_flag VARCHAR2(10);
2386 l_all_not_null VARCHAR2(20);
2387 l_all_null VARCHAR2(20);
2388 BEGIN
2389
2390 --** proc level logging.
2391 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2392 l_label := 'igs.plsql.igs_co_process.corp_validate_parameters';
2393 l_debug_str := 'Entering corp_validate_parameters. values are:';
2394 l_debug_str := l_debug_str || 'p_sys_ltr_code=' || p_sys_ltr_code;
2395 l_debug_str := l_debug_str || ',p_document_id=' || p_document_id ;
2396 l_debug_str := l_debug_str || ',p_select_type=' || p_select_type ;
2397 l_debug_str := l_debug_str || ',p_list_id=' || p_list_id ;
2398 l_debug_str := l_debug_str || ',p_person_id=' || p_person_id ;
2399 l_debug_str := l_debug_str || ',p_parameter_1=' || p_parameter_1 ;
2400 l_debug_str := l_debug_str || ',p_parameter_2=' || p_parameter_2 ;
2401 l_debug_str := l_debug_str || ',p_parameter_3=' || p_parameter_3 ;
2402 l_debug_str := l_debug_str || ',p_parameter_4=' || p_parameter_4 ;
2403 l_debug_str := l_debug_str || ',p_parameter_5=' || p_parameter_5 ;
2404 l_debug_str := l_debug_str || ',p_parameter_6=' || p_parameter_6 ;
2405 l_debug_str := l_debug_str || ',p_parameter_7=' || p_parameter_7 ;
2406 l_debug_str := l_debug_str || ',p_parameter_8=' || p_parameter_8 ;
2407 l_debug_str := l_debug_str || ',p_parameter_9=' || p_parameter_9 ;
2408 l_debug_str := l_debug_str || ',p_override_flag=' || p_override_flag ;
2409 l_debug_str := l_debug_str || ',p_delivery_type=' || p_delivery_type ;
2410 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
2411 END IF;
2412 --**
2413
2414 l_error_flag := 'FALSE';
2415 l_all_not_null := 'FALSE';
2416 l_all_null := 'FALSE';
2417 p_exception := 'N';
2418 IF p_select_type = 'L' THEN
2419 IF p_sys_ltr_code = 'ADINTRW' THEN
2420 IF p_list_id IS NULL OR
2421 p_parameter_1 IS NOT NULL OR p_parameter_2 IS NOT NULL OR p_parameter_3 IS NOT NULL OR
2422 p_parameter_4 IS NOT NULL OR p_parameter_6 IS NOT NULL OR p_parameter_7 IS NOT NULL OR
2423 p_parameter_9 IS NOT NULL OR p_person_id IS NOT NULL THEN
2424 p_exception := 'Y';
2425 fnd_message.set_name('IGS','IGS_CO_LIST_PRAM');
2426 fnd_file.put_line(fnd_file.log,fnd_message.get());
2427 fnd_file.put_line(FND_FILE.LOG,' ');
2428 --** proc level logging.
2429 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2430 l_label := 'igs.plsql.igs_co_process.corp_validate_parameters';
2431 l_debug_str := 'Exiting corp_validate_parameters. values are: ';
2432 l_debug_str := l_debug_str || 'p_select_type is L and p_sys_ltr_code is ADINTRW';
2433 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
2434 END IF;
2435 --**
2436 RETURN;
2437 END IF;
2438 ELSIF p_sys_ltr_code = 'ADNORSP' THEN
2439 IF p_list_id IS NULL OR
2440 p_parameter_1 IS NOT NULL OR p_parameter_2 IS NOT NULL OR p_parameter_3 IS NOT NULL OR
2441 p_parameter_4 IS NOT NULL OR p_parameter_5 IS NOT NULL OR p_parameter_6 IS NOT NULL OR
2442 p_parameter_7 IS NOT NULL OR p_parameter_9 IS NOT NULL OR p_person_id IS NOT NULL THEN
2443 --As letter has been submitted with select type as List
2444 --Only List Name should be specified
2445 p_exception := 'Y';
2446 fnd_message.set_name('IGS','IGS_CO_LIST_PRAM');
2447 fnd_file.put_line(fnd_file.log,fnd_message.get());
2448 fnd_file.put_line(FND_FILE.LOG,' ');
2449 --** proc level logging.
2450 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2451 l_label := 'igs.plsql.igs_co_process.corp_validate_parameters';
2452 l_debug_str := 'Exiting corp_validate_parameters. values are: ';
2453 l_debug_str := l_debug_str || 'p_select_type is L and p_sys_ltr_code is ADNORSP';
2454 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
2455 END IF;
2456 --**
2457 RETURN;
2458 END IF;
2459 ELSE
2460 IF p_list_id IS NULL OR
2461 p_parameter_1 IS NOT NULL OR p_parameter_2 IS NOT NULL OR p_parameter_3 IS NOT NULL OR
2462 p_parameter_4 IS NOT NULL OR p_parameter_5 IS NOT NULL OR p_parameter_6 IS NOT NULL OR
2463 p_parameter_7 IS NOT NULL OR p_person_id IS NOT NULL THEN
2464 --
2465 --As letter has been submitted with select type as List
2466 --Only List Name should be specified
2467 --
2468 p_exception := 'Y';
2469 fnd_message.set_name('IGS','IGS_CO_LIST_PRAM');
2470 fnd_file.put_line(fnd_file.log,fnd_message.get());
2471 fnd_file.put_line(FND_FILE.LOG,' ');
2472 --** proc level logging.
2473 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2474 l_label := 'igs.plsql.igs_co_process.corp_validate_parameters';
2475 l_debug_str := 'Exiting corp_validate_parameters as p_select_type is L, none of the nested if condition is satisfied and ';
2476 l_debug_str := l_debug_str || 'p_list_id is NULL or one of the parameters 1 to 7 is NOT NULL or person id is NOT null';
2477 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
2478 END IF;
2479 --**
2480 RETURN;
2481 END IF;
2482 END IF;
2483
2484 ELSIF p_select_type = 'S' THEN
2485 IF p_sys_ltr_code = 'ADINTRW' THEN
2486 IF p_person_id IS NULL OR
2487 p_parameter_1 IS NOT NULL OR p_parameter_2 IS NOT NULL OR p_parameter_3 IS NOT NULL OR
2488 p_parameter_4 IS NOT NULL OR p_parameter_6 IS NOT NULL OR p_parameter_7 IS NOT NULL OR
2489 p_parameter_9 IS NOT NULL THEN
2490 p_exception := 'Y';
2491
2492 fnd_message.set_name('IGS','IGS_CO_STUD_PRAM');
2493 fnd_file.put_line(fnd_file.log,fnd_message.get());
2494 fnd_file.put_line(FND_FILE.LOG,' ');
2495 --** proc level logging.
2496 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2497 l_label := 'igs.plsql.igs_co_process.corp_validate_parameters';
2498 l_debug_str := 'Exiting corp_validate_parameters. values: ';
2499 l_debug_str := l_debug_str || 'p_select_type is S and p_sys_ltr_code is ADINTRW';
2500 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
2501 END IF;
2502 --**
2503 RETURN;
2504 END IF; -- p_person_id
2505 ELSIF p_sys_ltr_code = 'ADNORSP' THEN
2506 IF p_person_id IS NULL OR
2507 p_parameter_1 IS NOT NULL OR p_parameter_2 IS NOT NULL OR p_parameter_3 IS NOT NULL OR
2508 p_parameter_4 IS NOT NULL OR p_parameter_5 IS NOT NULL OR p_parameter_6 IS NOT NULL OR
2509 p_parameter_7 IS NOT NULL OR p_parameter_9 IS NOT NULL THEN
2510 --As letter has been submitted with select type as List
2511 --Only List Name should be specified
2512 p_exception := 'Y';
2513 fnd_message.set_name('IGS','IGS_CO_STUD_PRAM');
2514 fnd_file.put_line(fnd_file.log,fnd_message.get());
2515 fnd_file.put_line(FND_FILE.LOG,' ');
2516 --** proc level logging.
2517 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2518 l_label := 'igs.plsql.igs_co_process.corp_validate_parameters';
2519 l_debug_str := 'Exiting corp_validate_parameters. values: ';
2520 l_debug_str := l_debug_str || 'p_select_type is S and p_sys_ltr_code is ADNORSP';
2521 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
2522 END IF;
2523 --**
2524 RETURN;
2525 END IF; -- p_person_id
2526 ELSE
2527
2528 IF p_person_id IS NULL OR
2529 p_parameter_1 IS NOT NULL OR p_parameter_2 IS NOT NULL OR p_parameter_3 IS NOT NULL OR
2530 p_parameter_4 IS NOT NULL OR p_parameter_5 IS NOT NULL OR p_parameter_6 IS NOT NULL OR
2531 p_parameter_7 IS NOT NULL OR
2532 p_list_id IS NOT NULL THEN
2533
2534
2535 p_exception := 'Y';
2536 fnd_message.set_name('IGS','IGS_CO_STUD_PRAM');
2537 fnd_file.put_line(fnd_file.log,fnd_message.get());
2538 fnd_file.put_line(FND_FILE.LOG,' ');
2539 --** proc level logging.
2540 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2541 l_label := 'igs.plsql.igs_co_process.corp_validate_parameters';
2542 l_debug_str := 'Exiting corp_validate_parameters as p_select_type is S, none of the nested if condition is satisfied and ';
2543 l_debug_str := l_debug_str || 'p_list_id is NOT NULL or one of the parameters 1 to 7 is NOT NULL or person id is null';
2544 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
2545 END IF;
2546 --**
2547 RETURN;
2548 END IF; -- p_person_id IS NULL
2549 END IF; -- p_select_type = S
2550
2551 ELSIF p_select_type = 'P' THEN
2552 IF p_parameter_1 IS NOT NULL THEN
2553 IF p_sys_ltr_code IN ('ADINTRW','ADNORSP') THEN
2554 IF (p_parameter_2 IS NULL AND p_parameter_3 IS NULL AND p_parameter_4 IS NULL AND
2555 p_parameter_6 IS NULL AND p_parameter_7 IS NULL AND p_parameter_9 IS NULL) THEN
2556 l_all_null := 'TRUE';
2557 ELSIF (p_parameter_2 IS NOT NULL AND p_parameter_3 IS NOT NULL AND p_parameter_4 IS NOT NULL AND
2558 p_parameter_5 IS NOT NULL AND p_parameter_6 IS NOT NULL AND p_parameter_7 IS NOT NULL AND
2559 p_parameter_8 IS NOT NULL AND p_parameter_9 IS NOT NULL) THEN
2560 l_all_not_null := 'TRUE';
2561 END IF;
2562 IF NOT( l_all_null = 'TRUE' OR l_all_not_null = 'TRUE') THEN
2563 p_exception := 'Y';
2564 fnd_message.set_name('IGS','IGS_AD_INVALID_PARAM_COMB');
2565 fnd_file.put_line(fnd_file.log,fnd_message.get());
2566 fnd_file.put_line(FND_FILE.LOG,' ');
2567 --** proc level logging.
2568 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2569 l_label := 'igs.plsql.igs_co_process.corp_validate_parameters';
2570 l_debug_str := 'Exiting corp_validate_parameters. values: ';
2571 l_debug_str := l_debug_str || 'p_select_type is P';
2572 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
2573 END IF;
2574 --**
2575 RETURN;
2576 END IF;
2577 ELSE --for all other letters
2578 IF p_parameter_2 IS NOT NULL OR
2579 p_parameter_3 IS NOT NULL OR
2580 p_parameter_4 IS NOT NULL OR
2581 p_parameter_5 IS NOT NULL OR
2582 p_parameter_6 IS NOT NULL OR
2583 p_parameter_7 IS NOT NULL OR
2584 p_person_id IS NOT NULL OR
2585 p_list_id IS NOT NULL THEN
2586 --
2587 -- As letter has been submitted with select type as Parameter
2588 -- either Person ID Group or other parameters should be selected. Both cannot be specified.
2589 --
2590 p_exception := 'Y';
2591 fnd_message.set_name('IGS','IGS_AD_INVALID_PARAM_COMB');
2592 fnd_file.put_line(fnd_file.log,fnd_message.get());
2593 fnd_file.put_line(FND_FILE.LOG,' ');
2594 --** proc level logging.
2595 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2596 l_label := 'igs.plsql.igs_co_process.corp_validate_parameters';
2597 l_debug_str := 'Exiting corp_validate_parameters for p_select_type P and all other values as none of the if conditions are satisfied.';
2598 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
2599 END IF;
2600 --**
2601 RETURN;
2602 END IF;
2603 END IF; -- p_sys_ltr_code IN ('ADINTRW','ADNORSP')
2604 END IF; -- selection type Parameter.
2605
2606 -- again checking that if Parameter_1 (person_id_group) is NULL then all other params should be
2607 -- specified.
2608 IF p_parameter_1 IS NULL THEN
2609
2610 IF p_sys_ltr_code = 'ADMISTM' AND
2611 (p_parameter_2 IS NULL OR p_parameter_3 IS NULL OR p_parameter_4 IS NULL OR p_parameter_5 IS NULL OR
2612 p_parameter_6 IS NULL OR p_parameter_8 IS NULL) THEN
2613 l_error_flag := 'TRUE';
2614 ELSIF p_sys_ltr_code = 'ADACKMT' AND
2615 (p_parameter_2 IS NULL OR p_parameter_3 IS NULL OR p_parameter_4 IS NULL OR p_parameter_5 IS NULL OR
2616 p_parameter_6 IS NULL) THEN
2617 l_error_flag := 'TRUE';
2618 ELSIF p_sys_ltr_code = 'ADRESID' AND
2619 (p_parameter_2 IS NULL OR p_parameter_3 IS NULL OR p_parameter_4 IS NULL OR p_parameter_5 IS NULL) THEN
2620 l_error_flag := 'TRUE';
2621 ELSIF p_sys_ltr_code = 'ADACCEP' AND
2622 (p_parameter_2 IS NULL OR p_parameter_3 IS NULL OR p_parameter_4 IS NULL OR p_parameter_5 IS NULL OR
2623 p_parameter_6 IS NULL OR p_parameter_7 IS NULL) THEN
2624 l_error_flag := 'TRUE';
2625 ELSIF p_sys_ltr_code = 'ADREJEC' AND
2626 (p_parameter_2 IS NULL OR p_parameter_3 IS NULL OR p_parameter_4 IS NULL OR p_parameter_5 IS NULL OR
2627 p_parameter_6 IS NULL OR p_parameter_7 IS NULL) THEN
2628 l_error_flag := 'TRUE';
2629 ELSIF p_sys_ltr_code = 'ADWAITL' AND
2630 (p_parameter_2 IS NULL OR p_parameter_3 IS NULL OR p_parameter_4 IS NULL OR p_parameter_5 IS NULL OR
2631 p_parameter_6 IS NULL OR p_parameter_7 IS NULL) THEN
2632 l_error_flag := 'TRUE';
2633 ELSIF p_sys_ltr_code = 'ADNOQUT' AND
2634 (p_parameter_2 IS NULL OR p_parameter_3 IS NULL OR p_parameter_4 IS NULL OR p_parameter_5 IS NULL OR
2635 p_parameter_6 IS NULL OR p_parameter_7 IS NULL) THEN
2636 l_error_flag := 'TRUE';
2637 ELSIF p_sys_ltr_code = 'ADCONOF' AND
2638 (p_parameter_2 IS NULL OR p_parameter_3 IS NULL OR p_parameter_4 IS NULL OR p_parameter_5 IS NULL OR
2639 p_parameter_6 IS NULL OR p_parameter_7 IS NULL) THEN
2640 l_error_flag := 'TRUE';
2641 ELSIF p_sys_ltr_code = 'ADPADMS' AND
2642 (p_parameter_2 IS NULL OR p_parameter_3 IS NULL OR p_parameter_4 IS NULL OR p_parameter_5 IS NULL OR
2643 p_parameter_6 IS NULL OR p_parameter_8 IS NULL) THEN
2644 l_error_flag := 'TRUE';
2645 ELSIF p_sys_ltr_code = 'ADFUTSE' AND
2646 (p_parameter_2 IS NULL OR p_parameter_3 IS NULL OR p_parameter_4 IS NULL OR p_parameter_5 IS NULL OR
2647 p_parameter_6 IS NULL OR p_parameter_7 IS NULL) THEN
2648 l_error_flag := 'TRUE';
2649 ELSIF p_sys_ltr_code = 'ADMFTSA' AND
2650 (p_parameter_2 IS NULL OR p_parameter_3 IS NULL OR p_parameter_4 IS NULL OR p_parameter_5 IS NULL OR
2651 p_parameter_6 IS NULL OR p_parameter_7 IS NULL) THEN
2652 l_error_flag := 'TRUE';
2653 ELSIF p_sys_ltr_code = 'ADNORSP' AND
2654 (p_parameter_2 IS NULL OR p_parameter_3 IS NULL OR p_parameter_4 IS NULL OR p_parameter_5 IS NULL OR
2655 p_parameter_6 IS NULL OR p_parameter_7 IS NULL OR p_parameter_8 IS NULL OR p_parameter_9 IS NULL) THEN
2656 l_error_flag := 'TRUE';
2657 ELSIF p_sys_ltr_code = 'ADINTRW' AND
2658 (p_parameter_2 IS NULL OR p_parameter_3 IS NULL OR p_parameter_4 IS NULL OR p_parameter_5 IS NULL OR
2659 p_parameter_6 IS NULL OR p_parameter_7 IS NULL OR p_parameter_8 IS NULL OR p_parameter_9 IS NULL) THEN
2660 l_error_flag := 'TRUE';
2661 END IF;
2662 END IF;
2663
2664 ELSIF p_select_type = 'G' THEN
2665
2666 --check if p_parameter_1 (person_id_grp) is null or not null.
2667 IF p_parameter_1 IS NOT NULL THEN
2668 IF p_sys_ltr_code = 'ADINTRW' THEN
2669 IF p_parameter_2 IS NOT NULL OR p_parameter_3 IS NOT NULL OR p_parameter_4 IS NOT NULL OR
2670 p_parameter_6 IS NOT NULL OR p_parameter_7 IS NOT NULL OR p_parameter_9 IS NOT NULL OR
2671 p_person_id IS NOT NULL OR p_list_id IS NOT NULL THEN
2672 --As letter has been submitted with select type as Group
2673 --Only Group Name should be specified
2674 p_exception := 'Y';
2675 fnd_message.set_name('IGS','IGS_CO_PER_LIS_PRAM');
2676 fnd_file.put_line(fnd_file.log,fnd_message.get());
2677 fnd_file.put_line(FND_FILE.LOG,' ');
2678 --** proc level logging.
2679 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2680 l_label := 'igs.plsql.igs_co_process.corp_validate_parameters';
2681 l_debug_str := 'Exiting corp_validate_parameters. values: ';
2682 l_debug_str := l_debug_str || 'p_select_type is G and p_sys_ltr_code is ADINTRW';
2683 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
2684 END IF;
2685 --**
2686 RETURN;
2687 END IF;
2688 ELSIF p_sys_ltr_code = 'ADNORSP' THEN
2689 IF p_parameter_2 IS NOT NULL OR p_parameter_3 IS NOT NULL OR
2690 p_parameter_4 IS NOT NULL OR p_parameter_5 IS NOT NULL OR p_parameter_6 IS NOT NULL OR
2691 p_parameter_7 IS NOT NULL OR p_parameter_9 IS NOT NULL OR
2692 p_person_id IS NOT NULL OR p_list_id IS NOT NULL THEN
2693 --As letter has been submitted with select type as Group
2694 --Only Group Name should be specified
2695 p_exception := 'Y';
2696 fnd_message.set_name('IGS','IGS_CO_PER_LIS_PRAM');
2697 fnd_file.put_line(fnd_file.log,fnd_message.get());
2698 fnd_file.put_line(FND_FILE.LOG,' ');
2699 --** proc level logging.
2700 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2701 l_label := 'igs.plsql.igs_co_process.corp_validate_parameters';
2702 l_debug_str := 'Exiting corp_validate_parameters. values: ';
2703 l_debug_str := l_debug_str || 'p_select_type is G and p_sys_ltr_code is ADNORSP';
2704 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
2705 END IF;
2706 --**
2707 RETURN;
2708 END IF;
2709 ELSE -- for other letters.
2710 IF p_parameter_2 IS NOT NULL OR p_parameter_3 IS NOT NULL OR p_parameter_4 IS NOT NULL OR
2711 p_parameter_5 IS NOT NULL OR p_parameter_6 IS NOT NULL OR p_parameter_7 IS NOT NULL OR
2712 p_person_id IS NOT NULL OR
2713 p_list_id IS NOT NULL THEN
2714 --
2715 -- As letter has been submitted with select type as Parameter
2716 -- either Person ID Group or other parameters should be selected. Both cannot be specified.
2717 --
2718 p_exception := 'Y';
2719 fnd_message.set_name('IGS','IGS_CO_PER_LIS_PRAM');
2720 fnd_file.put_line(fnd_file.log,fnd_message.get());
2721 fnd_file.put_line(FND_FILE.LOG,' ');
2722 --** proc level logging.
2723 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2724 l_label := 'igs.plsql.igs_co_process.corp_validate_parameters';
2725 l_debug_str := 'Exiting corp_validate_parameters. values: ';
2726 l_debug_str := l_debug_str || 'p_select_type is G and all other letters';
2727 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
2728 END IF;
2729 --**
2730 RETURN;
2731 END IF;
2732 END IF;
2733
2734 ELSIF p_parameter_1 IS NOT NULL AND (
2735 p_person_id IS NOT NULL OR
2736 p_list_id IS NOT NULL ) THEN
2737 --
2738 -- As letter has been submitted with select type as Parameter
2739 -- List nanm should not be selected
2740 --
2741
2742 p_exception := 'N';
2743 fnd_message.set_name('IGS','IGS_CO_PER_LIS_PRAM');
2744 fnd_file.put_line(fnd_file.log,fnd_message.get());
2745 fnd_file.put_line(FND_FILE.LOG,' ');
2746 --** proc level logging.
2747 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2748 l_label := 'igs.plsql.igs_co_process.corp_validate_parameters';
2749 l_debug_str := 'Exiting corp_validate_parameters as ';
2750 l_debug_str := l_debug_str || 'p_parameter_1 IS NOT NULL AND (p_person_id IS NOT NULL OR p_list_id IS NOT NULL';
2751 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
2752 END IF;
2753 --**
2754 RETURN;
2755
2756 END IF; -- p_parameter_1 IS NOT NULL THEN
2757
2758 IF p_parameter_1 IS NULL THEN
2759 p_exception := 'Y';
2760 fnd_message.set_name('IGS','IGS_CO_PER_LIS_PRAM');
2761 fnd_file.put_line(fnd_file.log,fnd_message.get());
2762 fnd_file.put_line(FND_FILE.LOG,' ');
2763 END IF;
2764 END IF; -- final end IF for selection type.
2765
2766 --
2767 -- Check if the parameter values are not correct.
2768 --
2769 IF l_error_flag = 'TRUE' THEN
2770 p_exception := 'Y';
2771 fnd_message.set_name('IGS','IGS_AD_INVALID_PARAM_COMB');
2772 fnd_file.put_line(fnd_file.log,fnd_message.get());
2773 fnd_file.put_line(FND_FILE.LOG,' ');
2774 --** proc level logging.
2775 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2776 l_label := 'igs.plsql.igs_co_process.corp_validate_parameters';
2777 l_debug_str := 'Exiting corp_validate_parameters as l_error_flag is TRUE';
2778 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
2779 END IF;
2780 --**
2781 RETURN;
2782 END IF;
2783 EXCEPTION
2784 WHEN OTHERS THEN
2785 --** proc level logging.
2786 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2787 l_label := 'igs.plsql.igs_co_process.corp_validate_parameters';
2788 l_debug_str := 'Exception in corp_validate_parameters.';
2789 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
2790 END IF;
2791 --**
2792 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
2793 FND_MESSAGE.SET_TOKEN('NAME','IGS_CO_PROCESS.corp_validate_parameters');
2794 IGS_GE_MSG_STACK.ADD;
2795 App_Exception.Raise_Exception;
2796 END corp_validate_parameters;
2797
2798 END igs_co_process;
[Home] [Help]