1 PACKAGE BODY igf_aw_gen_004 AS
2 /* $Header: IGFAW13B.pls 120.7 2006/06/06 07:29:33 akomurav noship $ */
3
4 /*************************************************************
5 Change History
6 Who When What
7 mnade 6/6/2005 FA 157 - 4382371 - Changes in award notification letter.
8 Also added get_base_id_for_person function.
9 svuppala 4-Nov-2004 #3416936 FA 134 TBH impacts for newly added columns
10 veramach Oct 2004 FA 152/FA 137 - Changes to wrappers to
11 bring in the awarding period setup
12 veramach 02-Sep-2004 bug 3869507 Resolved an issue where freq_attempt of an item was being added wrongly to other items.
13 veramach 06-OCT-2003 FA 124
14 Added functions efc_i,is_inas_integrated,need_i,unmetneed_i
15 KUMMA 07-jun-2003 2853531, Modified corp_pre_process and build_sql_stmt for adding the dynamic and static group
16 functionality for financial aid system letter
17 kumma 24-JUN-2003 2853531, Modified the call to igs_pe_dynamic_persid_group.IGS_GET_DYNAMIC_SQL as earlier it was a procedure
18 and now it is a function
19 (reverse chronological order - newest change first)
20
21 ***************************************************************/
22
23 -- bvisvana - bug 3724328 - For Code refactoring (Issue with huge person id groups)
24 TYPE temp_person_id IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
25 temp_person_id_array temp_person_id;
26
27
28 FUNCTION get_award_data_wrap (
29 p_person_id IN NUMBER,
30 p_fund_id IN VARCHAR2,
31 p_param1 IN VARCHAR2,
32 p_param2 IN VARCHAR2 ,
33 p_param3 IN VARCHAR2 ,
34 p_param4 IN VARCHAR2 ,
35 p_param5 IN VARCHAR2 ,
36 p_param6 IN VARCHAR2 ,
37 p_param7 IN VARCHAR2 ,
38 p_flag IN VARCHAR2,
39 p_awd_tot OUT NOCOPY NUMBER
40 )
41 RETURN VARCHAR2;
42
43
44 FUNCTION get_term_total_wrap (
45 p_person_id IN NUMBER,
46 p_param1 IN VARCHAR2,
47 p_param2 IN VARCHAR2 ,
48 p_param3 IN VARCHAR2 ,
49 p_param4 IN VARCHAR2 ,
50 p_param5 IN VARCHAR2 ,
51 p_param6 IN VARCHAR2 ,
52 p_param7 IN VARCHAR2 ,
53 p_flag IN VARCHAR2,
54 p_awd_tot OUT NOCOPY NUMBER
55 )
56 RETURN VARCHAR2;
57
58
59 PROCEDURE award_letter_matrix (
60 p_person_id IN NUMBER,
61 p_param1 IN VARCHAR2,
62 p_param2 IN VARCHAR2 ,
63 p_param3 IN VARCHAR2 ,
64 p_param4 IN VARCHAR2 ,
65 p_param5 IN VARCHAR2 ,
66 p_param6 IN VARCHAR2 ,
67 p_param7 IN VARCHAR2 ,
68 p_flag IN VARCHAR2,
69 p_return_status OUT NOCOPY VARCHAR2
70 );
71
72
73 PROCEDURE missing_items (
74 p_person_id IN NUMBER,
75 p_param1 IN VARCHAR2,
76 p_return_status OUT NOCOPY VARCHAR2
77 );
78
79
80 -- mnade 5/28/2005 - Added the common cursors here.
81
82 CURSOR g_c_get_details
83 (
84 cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
85 cp_fa_cal_type igs_ca_inst_all.cal_type%TYPE,
86 cp_fa_sequence_number igs_ca_inst_all.sequence_number%TYPE,
87 cp_ld_cal_type igs_ca_inst_all.cal_type%TYPE,
88 cp_ld_sequence_number igs_ca_inst_all.sequence_number%TYPE,
89 cp_fund_id igf_aw_fund_mast.fund_id%TYPE,
90 cp_award_prd_cd igf_aw_awd_prd_term.award_prd_cd%TYPE
91 ) IS
92 SELECT
93 cai.start_dt,
94 cai.alternate_code,
95 cai.cal_type,
96 cai.sequence_number,
97 sum(NVL (disb_gross_amt, 0)) load_total_offered_amt,
98 count(distinct awd.award_id) award_count,
99 fmast.fund_id fund_id,
100 fmast.description fund_name
101 FROM
102 igf_aw_award_all awd,
103 igf_aw_fund_mast fmast,
104 igs_ca_inst_all cai,
105 igf_aw_awd_disb disb
106 WHERE
107 fmast.ci_cal_type = cp_fa_cal_type AND
108 fmast.ci_sequence_number = cp_fa_sequence_number AND
109 awd.base_id = igf_aw_gen_004.get_base_id_for_person (cp_person_id, cp_fa_cal_type, cp_fa_sequence_number) AND
110 awd.fund_id = fmast.fund_id AND
111 awd.award_status IN ('ACCEPTED', 'OFFERED') AND
112 -- awd.notification_status_code IN ('R', 'F') AND
113 awd.award_id = disb.award_id AND
114 cai.cal_type = disb.ld_cal_type AND
115 cai.sequence_number = disb.ld_sequence_number AND
116 disb.ld_cal_type = NVL(cp_ld_cal_type, disb.ld_cal_type) AND
117 disb.ld_sequence_number = NVL(cp_ld_sequence_number, disb.ld_sequence_number) AND
118 fmast.fund_id = NVL(cp_fund_id, fmast.fund_id)
119 AND
120 NOT EXISTS
121 (SELECT disb.ld_cal_type, disb.ld_sequence_number
122 FROM igf_aw_awd_disb disb
123 WHERE
124 disb.award_id = awd.award_id
125 MINUS
126 SELECT ld_cal_type, ld_sequence_number
127 FROM igf_aw_awd_prd_term apt
128 WHERE apt.ci_cal_type = cp_fa_cal_type AND
129 apt.ci_sequence_number = cp_fa_sequence_number AND
130 apt.award_prd_cd = NVL(cp_award_prd_cd, award_prd_cd))
131 GROUP BY
132 cai.start_dt,
133 cai.alternate_code,
134 cai.cal_type,
135 cai.sequence_number,
136 fmast.fund_id,
137 fmast.description
138 order by cai.start_dt, fmast.description;
139
140 CURSOR g_c_get_load_cals
141 (
142 cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
143 cp_fa_cal_type igs_ca_inst_all.cal_type%TYPE,
144 cp_fa_sequence_number igs_ca_inst_all.sequence_number%TYPE,
145 cp_award_prd_cd igf_aw_awd_prd_term.award_prd_cd%TYPE
146 ) IS
147 SELECT
148 DISTINCT
149 cai.start_dt,
150 cai.alternate_code,
151 cai.cal_type,
152 cai.sequence_number
153 FROM
154 igf_aw_award_all awd,
155 igf_aw_fund_mast fmast,
156 igs_ca_inst_all cai,
157 igf_aw_awd_disb disb
158 WHERE
159 fmast.ci_cal_type = cp_fa_cal_type AND
160 fmast.ci_sequence_number = cp_fa_sequence_number AND
161 awd.base_id = igf_aw_gen_004.get_base_id_for_person (cp_person_id, cp_fa_cal_type, cp_fa_sequence_number) AND
162 awd.fund_id = fmast.fund_id AND
163 awd.award_status IN ('ACCEPTED', 'OFFERED') AND
164 -- awd.notification_status_code IN ('R', 'F') AND
165 awd.award_id = disb.award_id AND
166 cai.cal_type = disb.ld_cal_type AND
167 cai.sequence_number = disb.ld_sequence_number
168 AND
169 NOT EXISTS
170 (SELECT disb.ld_cal_type, disb.ld_sequence_number
171 FROM igf_aw_awd_disb disb
172 WHERE
173 disb.award_id = awd.award_id
174 MINUS
175 SELECT ld_cal_type, ld_sequence_number
176 FROM igf_aw_awd_prd_term apt
177 WHERE apt.ci_cal_type = cp_fa_cal_type AND
178 apt.ci_sequence_number = cp_fa_sequence_number AND
179 apt.award_prd_cd = NVL(cp_award_prd_cd, award_prd_cd))
180 order by cai.start_dt;
181
182 --mnade 5/28/2005 Generic function to get base id and avoid join with igf_ap_base_rec_all
183 FUNCTION get_base_id_for_person (
184 p_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
185 p_fa_cal_type igs_ca_inst_all.cal_type%TYPE,
186 p_fa_sequence_number igs_ca_inst_all.sequence_number%TYPE
187 ) RETURN NUMBER IS
188 CURSOR c_base_id (
189 cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
190 cp_fa_cal_type igs_ca_inst_all.cal_type%TYPE,
191 cp_fa_sequence_number igs_ca_inst_all.sequence_number%TYPE
192 )
193 IS
194 SELECT
195 base.base_id
196 FROM igf_ap_fa_base_rec_all base
197 WHERE
198 person_id = cp_person_id
199 AND base.ci_cal_type = cp_fa_cal_type
200 AND base.ci_sequence_number = cp_fa_sequence_number;
201 l_c_base_id c_base_id%ROWTYPE;
202
203 BEGIN
204 OPEN c_base_id (
205 cp_person_id => p_person_id,
206 cp_fa_cal_type => p_fa_cal_type,
207 cp_fa_sequence_number => p_fa_sequence_number
208 );
209 FETCH c_base_id INTO l_c_base_id;
210 CLOSE c_base_id ;
211 RETURN l_c_base_id.base_id;
212
213 EXCEPTION
214 WHEN OTHERS THEN
215 RETURN NULL;
216 END get_base_id_for_person;
217
218 PROCEDURE log_to_fnd ( p_v_module IN VARCHAR2,
219 p_v_log_category IN VARCHAR2,
220 p_v_string IN VARCHAR2 ) AS
221 ------------------------------------------------------------------
222 --Created by : bvisvana, Oracle IDC
223 --Date created: 22 May 2006
224 --Known limitations/enhancements and/or remarks:
225 --
226 --Change History:
227 --Who When What
228 ------------------------------------------------------------------
229 BEGIN
230 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
231 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_gen_004.'||p_v_module||'.'||p_v_log_category, p_v_string);
232 END IF;
233 END log_to_fnd;
234
235
236 FUNCTION get_person_id RETURN person_id_array PIPELINED IS
237 ------------------------------------------------------------------
238 --Created by : bvisvana, Oracle IDC
239 --Date created: 22 May 2006
240 --Known limitations/enhancements and/or remarks:
241 --Purpose : This is a pipelined function. The person id are collected in temp_person_id_array and
242 -- this temp_person_id_array is transferred into person_id_array through this pipelined function
243 -- Using pipelined function you could treat the data in a PLSQL table as a normal table.
244 -- You could make a query on those PLSQL similar to a database table.
245 --Change History:
246 --Who When What
247 ------------------------------------------------------------------
248 BEGIN
249 log_to_fnd('get_person_id','debug','Inside the pipelined function - get_person_id');
250 FOR i IN 1..temp_person_id_array.COUNT LOOP
251 pipe row(temp_person_id_array(i));
252 END LOOP;
253 log_to_fnd('get_person_id','debug','Before RETRUN from the pipelined function - get_person_id');
254 RETURN;
255 END get_person_id;
256
257 PROCEDURE build_sql_stmt (
258 p_award_year IN VARCHAR2,
259 p_sys_ltr_code IN VARCHAR2,
260 p_select_type IN VARCHAR2,
261 p_sql_stmt OUT NOCOPY VARCHAR2
262 ) IS
263
264 l_award_year igf_ap_mis_itms_ltr_v.award_year%TYPE;
265 BEGIN
266
267 /*************************************************************
268 Created By :Prajeesh
269 Date Created on : 05-Feb-2002
270 Purpose : This Procedure will accept person id and award year with
271 system letter code and select type as input parameter
272 and return the select clause to the main procedure
273 Know limitations, enhancements or remarks
274 Change History
275 Who When What
276 pkpatel 5-May-2003 Bug 2941138
277 Modified to use Bind variable
278 kumma 7-JUN-2003 2853531, Modified for adding the dynamic and static group functionality for financial aid system letter
279 pkpatel 19-AUG-2003 Bug 3104422 passed the Award year as per the system letter, since the underlying views have been modified.
280 bvisvana 22-May-2006 Bug 3724328 - For Code refactoring (Issue with huge person id groups).
281 Removed the p_person_id parameter from the build_sql_stm call and
282 procedure since the person id are stored in PLSQL table (and treated as PIPELINED function)
283 (reverse chronological order - newest change first)
284 ***************************************************************/
285 fnd_dsql.init;
286 fnd_dsql.add_text(' SELECT distinct email_address,person_id,award_year FROM ');
287
288 IF p_sys_ltr_code IN ('FAMISTM','FADISBT') THEN
289 l_award_year := igf_gr_gen.get_calendar_desc( RTRIM(SUBSTR(p_award_year,1,10)), TO_NUMBER(RTRIM(SUBSTR(p_award_year,11))));
290 ELSE
291 l_award_year := p_award_year;
292 END IF;
293
294 -- bvisvana - bug 3724328 - For Code refactoring (Issue with huge person id groups)
295 -- See the use of pipelined function igf_aw_gen_004.get_person_id();
296 log_to_fnd('build_sql_stmt','debug','IT IS LETTER CODE of type '||p_sys_ltr_code||' with select type as '||p_select_type||' and award year = '||l_award_year);
297 IF p_sys_ltr_code IN ('FAAWARD', 'FAMISTM','FADISBT') THEN
298 IF p_select_type IN ('S','G','A','L') THEN
299 IF p_sys_ltr_code='FAAWARD' THEN
300 fnd_dsql.add_text(' IGF_AW_PER_LIST_V WHERE person_id IN (select column_value from table(igf_aw_gen_004.get_person_id())');
301 fnd_dsql.add_text(') AND award_year =');
302 fnd_dsql.add_bind(l_award_year);
303 ELSIF p_sys_ltr_code='FAMISTM' THEN
304 fnd_dsql.add_text(' IGF_AP_MIS_ITMS_LTR_V WHERE person_id IN (select column_value from table(igf_aw_gen_004.get_person_id())');
305 fnd_dsql.add_text(') AND award_year =');
306 fnd_dsql.add_bind(l_award_year);
307 ELSIF p_sys_ltr_code='FADISBT' THEN
308 fnd_dsql.add_text(' IGF_SL_DISB_LTR_V WHERE person_id IN (select column_value from table(igf_aw_gen_004.get_person_id())');
309 fnd_dsql.add_text(') AND award_year =');
310 fnd_dsql.add_bind(l_award_year);
311 END IF;
312 END IF;
313 END IF;
314
315 p_sql_stmt := fnd_dsql.get_text(FALSE);
316 log_to_fnd('build_sql_stmt','debug','SQL Stmt got from build_sql_stmt is '||p_sql_stmt);
317 END build_sql_stmt;
318
319 FUNCTION efc_i(
320 l_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
321 p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE
322 ) RETURN NUMBER AS
323 ------------------------------------------------------------------
324 --Created by : veramach, Oracle India
325 --Date created: 06-OCT-2003
326 --
327 --Purpose:
328 -- Calculate IM EFC.
329 --
330 --Known limitations/enhancements and/or remarks:
331 --
332 --Change History:
333 --Who When What
334 -------------------------------------------------------------------
335
336 -- Get the details of EFC
337 CURSOR c_im_efc(
338 cp_base_id igf_aw_award_all.base_id%TYPE
339 ) IS
340 SELECT coa_duration_num,
341 coa_duration_efc_amt
342 FROM igf_ap_css_profile_all
343 WHERE active_profile = 'Y'
344 AND base_id = cp_base_id;
345
346 l_im_efc c_im_efc%ROWTYPE;
347 l_im_efc_amt igf_ap_css_profile_all.coa_duration_efc_amt%TYPE := NULL;
348
349 BEGIN
350 IF p_awd_prd_code IS NULL THEN
351 OPEN c_im_efc(l_base_id);
352 FETCH c_im_efc into l_im_efc;
353 IF c_im_efc%FOUND THEN
354
355 IF l_im_efc.coa_duration_num IS NULL THEN
356 --im efc is not calculated. SO, defaulting im efc to zero
357 CLOSE c_im_efc;
358 l_im_efc_amt := 0;
359 ELSE
360 CLOSE c_im_efc;
361 --im efc is calculated.
362 l_im_efc_amt := l_im_efc.coa_duration_efc_amt;
363 END IF;
364 ELSE
365 RETURN NULL;
366 END IF;
367 ELSE
368 l_im_efc_amt := igf_ap_uhk_inas_pkg.efc_i_award_prd(l_base_id,p_awd_prd_code);
369 END IF;
370 RETURN l_im_efc_amt;
371
372 END efc_i;
373
374
375 -- ADDED BY GMURALID FOR BUG 2737925 ON 8-JAN-2003
376
377 FUNCTION efc_f(
378 l_base_id IN NUMBER,
379 p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE DEFAULT NULL
380 )
381 RETURN NUMBER
382 IS
383 /*************************************************************
384 Created By : Gautam S.M
385 Date Created on : 08-JAN-2003
386 Purpose : The function is used for obtaining the efc for a given base id
387 Change History
388 Who When What
389 veramach 11-Oct-2004 FA152 Changes to bring in awarding period setup
390 veramach 08-Apr-2004 bug 3547237
391 Added a check that if auto_zero_efc is set to 'Y' in the active_isir,
392 then EFC returned must be zero
393 adhawan 11-feb-2003 Select the efc from the Active isir instead of the payment isir
394 2758804 Modified the c_efc for it .
395 --rasahoo 05-Aug-2003 #3024112 Changed the parameters in call igf_ap_efc_calc.get_efc_no_of_months
396 --
397 (reverse chronological order - newest change first)
398 ***************************************************************/
399
400 CURSOR c_efc(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
401 cp_months NUMBER)
402 IS
403 SELECT DECODE(f.award_fmly_contribution_type,
404 2, DECODE(cp_months, 1 ,isir.sec_alternate_month_1,
405 2 , isir.sec_alternate_month_2,
406 3 , isir.sec_alternate_month_3,
407 4 , isir.sec_alternate_month_4,
408 5 , isir.sec_alternate_month_5,
409 6 , isir.sec_alternate_month_6,
410 7 , isir.sec_alternate_month_7,
411 8 , isir.sec_alternate_month_8,
412 9 , isir.secondary_efc,
413 10, isir.sec_alternate_month_10,
414 11, isir.sec_alternate_month_11,
415 12, isir.sec_alternate_month_12),
416 DECODE(cp_months, 1 , isir.primary_alternate_month_1,
417 2 , isir.primary_alternate_month_2,
418 3 , isir.primary_alternate_month_3,
419 4 , isir.primary_alternate_month_4,
420 5 , isir.primary_alternate_month_5,
421 6 , isir.primary_alternate_month_6,
422 7 , isir.primary_alternate_month_7,
423 8 , isir.primary_alternate_month_8,
424 9 , isir.primary_efc,
425 10, isir.primary_alternate_month_10,
426 11, isir.primary_alternate_month_11,
427 12, isir.primary_alternate_month_12)
428 ) efc,
429 isir.primary_efc primary_efc,
430 NVL(isir.auto_zero_efc,'N') auto_zero_efc
431 FROM igf_ap_isir_matched isir,
432 igf_ap_fa_base_rec_all f
433 WHERE isir.base_id = cp_base_id
434 AND isir.base_id = f.base_id
435 AND isir.active_isir='Y';
436
437
438 l_efc_months NUMBER ;
439 l_efc_rec c_efc%ROWTYPE ;
440 l_awdprd_startdt DATE;
441
442
443 CURSOR get_round_off(
444 cp_base_id igf_ap_fa_base_rec.base_id%TYPE
445 ) IS
446 SELECT num_days_divisor,
447 roundoff_fact
448 FROM igf_ap_efc_v efc,
449 igf_ap_fa_base_rec_all fabase
450 WHERE efc.ci_cal_type = fabase.ci_cal_type
451 AND efc.ci_sequence_number = fabase.ci_sequence_number
452 AND fabase.base_id = cp_base_id;
453 lv_round_off_rec get_round_off%ROWTYPE;
454
455 l_ap_months NUMBER;
456 l_ap_start_dt DATE;
457 l_ap_end_dt DATE;
458
459 l_ay_months NUMBER;
460 l_ay_start_dt DATE;
461 l_ay_end_dt DATE;
462 l_ap_efc NUMBER;
463 l_tot_efc NUMBER;
464 l_tot_months NUMBER;
465 l_prior_months NUMBER;
466 l_pre_ap_efc NUMBER;
467
468 BEGIN
469
470 IF p_awd_prd_code IS NULL THEN
471 l_efc_months := igf_aw_coa_gen.coa_duration(l_base_id,p_awd_prd_code ) ;
472 IF l_efc_months >12 OR l_efc_months < 0 THEN
473 l_efc_months := 12 ;
474 END IF ;
475 IF l_efc_months IS NULL OR l_efc_months =0 THEN
476 RETURN NULL;
477 END IF;
478 -- get EFC value for Fed Methodology
479 OPEN c_efc (l_base_id,l_efc_months) ;
480 FETCH c_efc INTO l_efc_rec ;
481 CLOSE c_efc ;
482
483 IF l_efc_rec.primary_efc = 0 AND l_efc_rec.auto_zero_efc = 'Y' THEN
484 RETURN 0;
485 ELSE
486 RETURN l_efc_rec.efc ;
487 END IF;
488
489 ELSE
490 -- Step1: months spanning the Award Period where the studend has COA.
491 -- start/end date of the award period
492 igf_aw_coa_gen.get_coa_months(
493 p_base_id => l_base_id,
494 p_awd_prd_code => p_awd_prd_code,
495 p_start_dt => l_ap_start_dt,
496 p_end_dt => l_ap_end_dt,
497 p_coa_months => l_ap_months
498 );
499
500 -- Step2: months spanning the Award Year where the studend has COA.
501 -- start/end date of the Award Year
502 igf_aw_coa_gen.get_coa_months(
503 p_base_id => l_base_id,
504 p_awd_prd_code => NULL,
505 p_start_dt => l_ay_start_dt,
506 p_end_dt => l_ay_end_dt,
507 p_coa_months => l_ay_months
508 );
509
510 -- Step3: Determine if this is the First AP in the AY
511 -- Get the nth month cumulative EFC and return
512 OPEN c_efc(l_base_id,l_ap_months);
513 FETCH c_efc INTO l_efc_rec;
514 CLOSE c_efc;
515
516 IF l_efc_rec.primary_efc = 0 AND l_efc_rec.auto_zero_efc = 'Y' THEN
517 l_ap_efc := 0;
518 ELSE
519 l_ap_efc := l_efc_rec.efc;
520 END IF;
521
522 IF l_ap_start_dt = l_ay_start_dt THEN
523 RETURN l_ap_efc;
524 END IF;
525
526 -- Step4 : This is not the first Awarding Period.
527 OPEN get_round_off(l_base_id);
528 FETCH get_round_off INTO lv_round_off_rec;
529 CLOSE get_round_off;
530
531 l_tot_months := (l_ap_end_dt - l_ay_start_dt) / NVL(lv_round_off_rec.num_days_divisor,30);
532
533 IF (lv_round_off_rec.roundoff_fact = 'RU') THEN
534 -- Round up to the nearest whole number
535 l_tot_months := CEIL( l_tot_months );
536 ELSIF (lv_round_off_rec.roundoff_fact = 'RD' ) THEN
537 -- Round down to the nearest whole number
538 l_tot_months := FLOOR( l_tot_months );
539 ELSE
540 -- Round off factor is 'RH', Round to the nearest whole number
541 l_tot_months := ROUND( l_tot_months );
542 END IF;
543
544 -- Step6: Get the months prior to the start of the AP
545 l_prior_months := (l_ap_start_dt - l_ay_start_dt) / NVL(lv_round_off_rec.num_days_divisor,30);
546
547 IF (lv_round_off_rec.roundoff_fact = 'RU') THEN
548 -- Round up to the nearest whole number
549 l_prior_months := CEIL( l_prior_months );
550 ELSIF (lv_round_off_rec.roundoff_fact = 'RD' ) THEN
551 -- Round down to the nearest whole number
552 l_prior_months := FLOOR( l_prior_months );
553 ELSE
554 -- Round off factor is 'RH', Round to the nearest whole number
555 l_prior_months := ROUND( l_prior_months );
556 END IF;
557
558 -- get the total cumulative EFC.
559 OPEN c_efc (l_base_id,(LEAST(NVL(l_tot_months,0) ,12) ));
560 FETCH c_efc INTO l_efc_rec;
561 l_tot_efc := l_efc_rec.efc;
562 CLOSE c_efc ;
563
564 -- get the total months ap months prior to start of AP EFC.
565 OPEN c_efc (l_base_id,(LEAST( NVL(l_tot_months,0) - NVL(l_prior_months,0) ,12) ));
566 FETCH c_efc INTO l_efc_rec;
567 l_pre_ap_efc := l_efc_rec.efc;
568 CLOSE c_efc ;
569
570 RETURN (NVL(l_tot_efc,0) - NVL(l_pre_ap_efc,0));
571 END IF;
572
573 EXCEPTION
574 WHEN OTHERS THEN
575 RETURN NULL;
576 END efc_f;
577
578 -- ADDED BY GMURALID FOR BUG 2737925 ON 8-JAN-2003
579
580 FUNCTION unmetneed_f(
581 l_base_id IN NUMBER,
582 p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE DEFAULT NULL
583 ) RETURN NUMBER IS
584 /*************************************************************
585 Created By : Gautam S.M
586 Date Created on : 0*-JAN-2003
587 Purpose : The function is used for obtaining the unmet need for a given base id
588 Change History
589 Who When What
590 gmuralid 16-JAN-03 BUG 2737925 included check to see whether award meeting family contribution
591 is gretaer than efc_f.
592 (reverse chronological order - newest change first)
593 ***************************************************************/
594
595 l_resource_f NUMBER;
596 l_resource_i NUMBER;
597 l_unmet_need_f NUMBER;
598 l_unmet_need_i NUMBER;
599 l_resource_f_fc NUMBER;
600 l_resource_i_fc NUMBER;
601
602 BEGIN
603 igf_aw_gen_002.get_resource_need(
604 p_base_id => l_base_id,
605 p_resource_f => l_resource_f,
606 p_resource_i => l_resource_i,
607 p_unmet_need_f => l_unmet_need_f,
608 p_unmet_need_i => l_unmet_need_i,
609 p_resource_f_fc => l_resource_f_fc,
610 p_resource_i_fc => l_resource_i_fc,
611 p_awd_prd_code => p_awd_prd_code
612 );
613
614 RETURN l_unmet_need_f;
615
616 EXCEPTION
617 WHEN OTHERS THEN
618 RETURN NULL;
619
620 END unmetneed_f;
621
622 FUNCTION unmetneed_i(
623 l_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
624 p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE DEFAULT NULL
625 ) RETURN NUMBER AS
626 ------------------------------------------------------------------
627 --Created by : veramach, Oracle India
628 --Date created: 06-SEP-2003
629 --
630 --Purpose: To calculate unmet need according to institutional methodology
631 --
632 --
633 --Known limitations/enhancements and/or remarks:
634 --
635 --Change History:
636 --Who When What
637 -------------------------------------------------------------------
638
639 l_unmet_need NUMBER;
640 l_resource_f NUMBER;
641 l_resource_i NUMBER;
642 l_unmet_need_f NUMBER;
643 l_unmet_need_i NUMBER;
644 l_resource_f_fc NUMBER;
645 l_resource_i_fc NUMBER;
646
647 BEGIN
648 igf_aw_gen_002.get_resource_need(
649 p_base_id => l_base_id,
650 p_resource_f => l_resource_f,
651 p_resource_i => l_resource_i,
652 p_unmet_need_f => l_unmet_need_f,
653 p_unmet_need_i => l_unmet_need_i,
654 p_resource_f_fc => l_resource_f_fc,
655 p_resource_i_fc => l_resource_i_fc,
656 p_awd_prd_code => p_awd_prd_code
657 );
658 RETURN l_unmet_need_i;
659 END unmetneed_i;
660
661
662 FUNCTION need_f(
663 l_base_id IN NUMBER,
664 p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE
665 ) RETURN NUMBER IS
666 /*************************************************************
667 Created By : Gautam S.M
668 Date Created on : 08-JAN-2003
669 Purpose : The function is used for obtaining the need for a given base id
670 Change History
671 Who When What
672
673 (reverse chronological order - newest change first)
674 ***************************************************************/
675
676 coa_amt igf_ap_fa_base_rec_all.coa_f%TYPE;
677 need_f NUMBER;
678 l_efc_f NUMBER;
679
680 BEGIN
681 coa_amt := igf_aw_coa_gen.coa_amount(p_base_id => l_base_id,p_awd_prd_code => p_awd_prd_code);
682 l_efc_f := igf_aw_gen_004.efc_f(l_base_id => l_base_id,p_awd_prd_code => p_awd_prd_code);
683 IF coa_amt > l_efc_f THEN
684 need_f := coa_amt - l_efc_f;
685 ELSE
686 need_f := 0;
687 END IF;
688 RETURN need_f;
689
690 EXCEPTION
691 WHEN OTHERS THEN
692 RETURN NULL;
693 END need_f;
694
695
696 FUNCTION need_i(
697 l_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
698 p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE DEFAULT NULL
699 ) RETURN NUMBER AS
700 ------------------------------------------------------------------
701 --Created by : veramach, Oracle India
702 --Date created: 06-SEP-2003
703 --
704 --Purpose: To calculate need according to institutional methodology
705 --
706 --
707 --Known limitations/enhancements and/or remarks:
708 --
709 --Change History:
710 --Who When What
711 -------------------------------------------------------------------
712
713 coa_amt igf_ap_fa_base_rec_all.coa_f%TYPE;
714 need_i NUMBER;
715 l_efc_i NUMBER;
716
717
718 BEGIN
719 coa_amt := igf_aw_coa_gen.coa_amount(p_base_id => l_base_id,p_awd_prd_code => p_awd_prd_code);
720 l_efc_i := igf_aw_gen_004.efc_i(l_base_id => l_base_id,p_awd_prd_code => p_awd_prd_code);
721 IF coa_amt > l_efc_i THEN
722 need_i := coa_amt - l_efc_i;
723 ELSE
724 need_i := 0;
725 END IF;
726 RETURN need_i;
727
728 EXCEPTION
729 WHEN OTHERS THEN
730 RETURN NULL;
731 END need_i;
732
733
734 FUNCTION get_headings (
735 p_person_id IN NUMBER,
736 p_param1 IN VARCHAR2,
737 p_param2 IN VARCHAR2 ,
738 p_param3 IN VARCHAR2 ,
739 p_param4 IN VARCHAR2 ,
740 p_param5 IN VARCHAR2 ,
741 p_param6 IN VARCHAR2 ,
742 p_param7 IN VARCHAR2 ,
743 p_flag IN VARCHAR2
744 )
745 RETURN VARCHAR2 IS
746
747 /*************************************************************
748 Created By :Prajeesh
749 Date Created on : 05-Feb-2002
750 Purpose : This Function will get the person id and award year
751 load calendards and P-flag as parameter and create
752 the header in html format and puts it in the temp
753 table. It check if p_flag is 'Y' implies to do
754 automatic population then it ignores the load calendars
755 and generate the header for all the terms for the person
756 with the given award year. If 'N' then it will check
757 for the given load calendar and shows only that given
758 load calendar
759 Know limitations, enhancements or remarks
760 Change History
761 Who When What
762
763 (reverse chronological order - newest change first)
764 ***************************************************************/
765
766 l_term_base_total NUMBER;
767
768 l_header_rec VARCHAR2(32000);
769 BEGIN
770
771
772 FOR l_c_get_load_cals IN g_c_get_load_cals
773 (
774 p_person_id,
775 ltrim(rtrim(substr(p_param1, 1, 10))),
776 to_number(ltrim(rtrim(substr(p_param1, 11)))),
777 p_param2
778 )
779 LOOP -- Get only the alternate codes for load calendars
780 l_header_rec := l_header_rec || '<TH>' || l_c_get_load_cals.alternate_code || '</TH>';
781 END LOOP; -- END Get only the alternate codes for load calendars
782
783 l_header_rec := l_header_rec || '<TH>Award Total</TH>';
784 -- <TH>Award Types</TH> is there in the select query hence not required here.
785
786 -- IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
787 -- fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.get_headings.debug.Header',l_header_rec);
788 -- END IF;
789
790 RETURN l_header_rec;
791
792 EXCEPTION
793 WHEN OTHERS THEN
794 RETURN NULL;
795 END get_headings;
796
797 FUNCTION get_award_data_wrap (
798 p_person_id IN NUMBER,
799 p_fund_id IN VARCHAR2,
800 p_param1 IN VARCHAR2,
801 p_param2 IN VARCHAR2 ,
802 p_param3 IN VARCHAR2 ,
803 p_param4 IN VARCHAR2 ,
804 p_param5 IN VARCHAR2 ,
805 p_param6 IN VARCHAR2 ,
806 p_param7 IN VARCHAR2 ,
807 p_flag IN VARCHAR2 ,
808 p_awd_tot OUT NOCOPY NUMBER
809 )
810 RETURN VARCHAR2 IS
811
812 /*************************************************************
813 Created By :Prajeesh
814 Date Created on : 05-Feb-2002
815 Purpose : This function is used to insert the transaction
816 records(disbursement records for the different funds
817 in an award year to the given person. It checks if
818 p_flag='Y' implies automatic population then it
819 gets all the disbursement records for all the terms
820 in an given award year.Else if 'N' then it
821 generates the records for the given award year
822 for different fund codes to the person
823 Know limitations, enhancements or remarks
824 Change History
825 Who When What
826
827 (reverse chronological order - newest change first)
828 ***************************************************************/
829
830
831 l_fund_found_for_load BOOLEAN;
832 l_fund_total NUMBER := 0;
833 l_data_rec VARCHAR2(32000);
834 BEGIN
835
836 FOR l_c_get_load_cals IN g_c_get_load_cals
837 (
838 p_person_id,
839 ltrim(rtrim(substr(p_param1, 1, 10))),
840 to_number(ltrim(rtrim(substr(p_param1, 11)))),
841 p_param2
842 )
843 LOOP -- Get only the alternate codes for load calendars
844 l_fund_found_for_load := FALSE;
845 FOR l_c_get_details IN g_c_get_details
846 (
847 cp_person_id => p_person_id,
848 cp_fa_cal_type => LTRIM(RTRIM(SUBSTR(p_param1, 1, 10))),
849 cp_fa_sequence_number => TO_NUMBER(LTRIM(RTRIM(SUBSTR(p_param1, 11)))),
850 cp_ld_cal_type => l_c_get_load_cals.cal_type,
851 cp_ld_sequence_number => l_c_get_load_cals.sequence_number,
852 cp_fund_id => p_fund_id,
853 cp_award_prd_cd => p_param2
854 )
855 LOOP -- Iterate for offred amount over terms
856 l_data_rec := l_data_rec || '<TD>' || NVL(l_c_get_details.load_total_offered_amt, 0) || '</TD>' ;
857 l_fund_total := l_fund_total + NVL(l_c_get_details.load_total_offered_amt, 0);
858 l_fund_found_for_load := TRUE;
859 END LOOP; -- END Iterate for offred amount over terms
860 IF NOT(l_fund_found_for_load) THEN
861 l_data_rec := l_data_rec || '<TD>0</TD>' ;
862 END IF;
863 END LOOP; -- END Get only the alternate codes for load calendars
864
865
866
867 --Add the award total
868 l_data_rec := l_data_rec || '<TD>' || NVL (TO_CHAR (l_fund_total), '-') || '</TD>';
869
870 p_awd_tot := NVL(l_fund_total,0);
871
872 RETURN l_data_rec;
873
874 EXCEPTION
875 WHEN OTHERS THEN
876 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
877 FND_MESSAGE.SET_TOKEN('NAME','igf_aw_gen_004.get_award_data_wrap');
878 IGS_GE_MSG_STACK.ADD;
879 App_Exception.Raise_Exception;
880 END get_award_data_wrap;
881
882 FUNCTION get_award_data (
883 p_person_id IN NUMBER,
884 p_fund_id IN VARCHAR2,
885 p_param1 IN VARCHAR2,
886 p_param2 IN VARCHAR2 ,
887 p_param3 IN VARCHAR2 ,
888 p_param4 IN VARCHAR2 ,
889 p_param5 IN VARCHAR2 ,
890 p_param6 IN VARCHAR2 ,
891 p_param7 IN VARCHAR2 ,
892 p_flag IN VARCHAR2
893
894 ) RETURN VARCHAR2 IS
895
896 /*************************************************************
897 Created By :Prajeesh
898 Date Created on : 05-Feb-2002
899 Purpose : This Function is the wrapper for the award data function.
900 This function is created mainly to put it in sql statement
901 which is inserted INTO the table. As the functions award_data
902 wrap has out NOCOPY parameter too thus this wrapper is created to
903 remove the out NOCOPY parameter so that it can be used in the
904 select clause
905 Know limitations, enhancements or remarks
906 Change History
907 Who When What
908
909 (reverse chronological order - newest change first)
910 ***************************************************************/
911
912 l_awd_tot NUMBER;
913 l_ret_data VARCHAR2(32000);
914 BEGIN
915 l_ret_data:=get_award_data_wrap ( p_person_id,
916 p_fund_id,
917 p_param1,
918 p_param2,
919 p_param3,
920 p_param4,
921 p_param5,
922 p_param6,
923 p_param7,
924 p_flag,
925 l_awd_tot
926 ) ;
927
928 RETURN l_ret_data;
929
930 EXCEPTION
931 WHEN OTHERS THEN
932 RETURN NULL;
933
934 END get_award_data;
935
936
937 FUNCTION get_term_total_wrap (
938 p_person_id IN NUMBER,
939 p_param1 IN VARCHAR2,
940 p_param2 IN VARCHAR2 ,
941 p_param3 IN VARCHAR2 ,
942 p_param4 IN VARCHAR2 ,
943 p_param5 IN VARCHAR2 ,
944 p_param6 IN VARCHAR2 ,
945 p_param7 IN VARCHAR2 ,
946 p_flag IN VARCHAR2 ,
947 p_awd_tot OUT NOCOPY NUMBER
948 )
949 RETURN VARCHAR2 IS
950
951 /*************************************************************
952 Created By :Prajeesh
953 Date Created on : 05-Feb-2002
954 Purpose : This Function gets the total term wise totals and total award total
955 If the p_flag='Y' implies automatic population is set implies
956 it gets term totals for all the terms for the person in a given award year.
957 Else it generates the term total for the given load calendar if
958 p_flag='N'
959 Know limitations, enhancements or remarks
960 Change History
961 Who When What
962
963 (reverse chronological order - newest change first)
964 ***************************************************************/
965
966 /*Cursor to gethe load calendar details*/
967
968 l_fund_total NUMBER :=0;
969 l_load_total NUMBER :=0;
970 l_data_rec VARCHAR2 (32000);
971
972 BEGIN
973
974 FOR l_c_get_load_cals IN g_c_get_load_cals
975 (
976 p_person_id,
977 ltrim(rtrim(substr(p_param1, 1, 10))),
978 to_number(ltrim(rtrim(substr(p_param1, 11)))),
979 p_param2
980 )
981 LOOP -- Get only the alternate codes for load calendars
982 l_load_total := 0;
983 FOR l_c_get_details IN g_c_get_details
984 (
985 cp_person_id => p_person_id,
986 cp_fa_cal_type => LTRIM(RTRIM(SUBSTR(p_param1, 1, 10))),
987 cp_fa_sequence_number => TO_NUMBER(LTRIM(RTRIM(SUBSTR(p_param1, 11)))),
988 cp_ld_cal_type => l_c_get_load_cals.cal_type,
989 cp_ld_sequence_number => l_c_get_load_cals.sequence_number,
990 cp_fund_id => NULL,
991 cp_award_prd_cd => p_param2
992 )
993 LOOP -- Iterate for offred amount over terms
994 l_load_total := l_load_total + NVL(l_c_get_details.load_total_offered_amt, 0);
995 END LOOP; -- END Iterate for offred amount over terms
996 l_data_rec := l_data_rec || '<TD>' || l_load_total || '</TD>' ;
997 l_fund_total := l_fund_total + l_load_total;
998 END LOOP; -- END Get only the alternate codes for load calendars
999
1000 p_awd_tot := NVL(l_fund_total,0);
1001 l_data_rec := l_data_rec
1002 || '<TD>' || (l_fund_total) || '<TD>';
1003
1004 RETURN l_data_rec;
1005
1006 EXCEPTION
1007 WHEN OTHERS THEN
1008 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1009 FND_MESSAGE.SET_TOKEN('NAME','IGF_AW_GEN_004.GET_TERM_TOTAL_WRAP');
1010 IGS_GE_MSG_STACK.ADD;
1011 App_Exception.Raise_Exception;
1012 END get_term_total_wrap;
1013
1014 FUNCTION get_term_total (
1015 p_person_id IN NUMBER,
1016 p_param1 IN VARCHAR2,
1017 p_param2 IN VARCHAR2 ,
1018 p_param3 IN VARCHAR2 ,
1019 p_param4 IN VARCHAR2 ,
1020 p_param5 IN VARCHAR2 ,
1021 p_param6 IN VARCHAR2 ,
1022 p_param7 IN VARCHAR2 ,
1023 p_flag IN VARCHAR2
1024
1025 )
1026 RETURN VARCHAR2 IS
1027
1028 /*************************************************************
1029 Created By :Prajeesh
1030 Date Created on : 05-Feb-2002
1031 Purpose :This function is the wrapper for the get_term_total_wrap
1032 as it return an out NOCOPY variables which cant be used in
1033 select clause thus a wrapper is created without out NOCOPY
1034 clause
1035 Know limitations, enhancements or remarks
1036 Change History
1037 Who When What
1038
1039 (reverse chronological order - newest change first)
1040 ***************************************************************/
1041
1042 l_ret_data VARCHAR2(32000);
1043 l_awd_tot NUMBER;
1044 BEGIN
1045 l_ret_data:=get_term_total_wrap(
1046 p_person_id,
1047 p_param1,
1048 p_param2,
1049 p_param3,
1050 p_param4,
1051 p_param5,
1052 p_param6,
1053 p_param7,
1054 p_flag,
1055 l_awd_tot
1056 );
1057
1058 RETURN l_ret_data;
1059
1060 EXCEPTION
1061 WHEN OTHERS THEN
1062 NULL;
1063
1064 END get_term_total;
1065
1066 FUNCTION is_inas_integrated RETURN BOOLEAN AS
1067 ------------------------------------------------------------------
1068 --Created by : veramach, Oracle India
1069 --Date created: 6-OCT-2003
1070 --
1071 --Purpose:
1072 -- To check if INAS is integrated with the system
1073 --
1074 --Known limitations/enhancements and/or remarks:
1075 --
1076 --Change History:
1077 --Who When What
1078 -------------------------------------------------------------------
1079 lv_profile_value VARCHAR2(10);
1080 BEGIN
1081 fnd_profile.get('IGF_AW_INAS_INTEGRATE',lv_profile_value);
1082 IF lv_profile_value ='Y' THEN
1083 RETURN TRUE;
1084 ELSE
1085 RETURN FALSE;
1086 END IF;
1087 END is_inas_integrated;
1088
1089 PROCEDURE award_letter_matrix (
1090 p_person_id IN NUMBER,
1091 p_param1 IN VARCHAR2,
1092 p_param2 IN VARCHAR2 ,
1093 p_param3 IN VARCHAR2 ,
1094 p_param4 IN VARCHAR2 ,
1095 p_param5 IN VARCHAR2 ,
1096 p_param6 IN VARCHAR2 ,
1097 p_param7 IN VARCHAR2 ,
1098 p_flag IN VARCHAR2 ,
1099 p_return_status OUT NOCOPY VARCHAR2
1100 ) IS
1101
1102 /*************************************************************
1103 Created By :Prajeesh
1104 Date Created on : 05-Feb-2002
1105 Purpose : This is the main award procedure which gets called
1106 and it inserts the records in temp table for
1107 the person and award year depending on the values
1108 in particular format
1109 Know limitations, enhancements or remarks
1110 Change History
1111 Who When What
1112
1113 (reverse chronological order - newest change first)
1114 ***************************************************************/
1115 l_awd_tot NUMBER DEFAULT 0;
1116 l_awd_tot_fund NUMBER DEFAULT -1;
1117
1118 --Main Cursor for Award Letter to the award details in an given format*/
1119
1120 CURSOR get_awd_data IS
1121 SELECT NULL fund_code,-1 fund_id,
1122 '<TH>Award Type</TH>' data1,
1123 igf_aw_gen_004.get_headings (
1124 p_person_id,
1125 p_param1,
1126 p_param2,
1127 p_param3,
1128 p_param4,
1129 p_param5,
1130 p_param6,
1131 p_param7,
1132 p_flag
1133 ) data2,
1134 1 seq
1135 FROM DUAL
1136 UNION
1137 SELECT DISTINCT
1138 fmast.fund_code, fmast.fund_id, '<TD>' || fmast.description || '</TD>' data1,
1139 get_award_data (
1140 p_person_id,
1141 fmast.fund_id,
1142 p_param1,
1143 p_param2,
1144 p_param3,
1145 p_param4,
1146 p_param5,
1147 p_param6,
1148 p_param7,
1149 p_flag
1150 ) data2,
1151 2 seq
1152 FROM
1153 igf_aw_award_all awd,
1154 igf_aw_fund_mast fmast,
1155 igs_ca_inst_all cai,
1156 igf_aw_awd_disb disb
1157 WHERE
1158 fmast.ci_cal_type = RTRIM (SUBSTR (p_param1, 1, 10))
1159 AND fmast.ci_sequence_number = TO_NUMBER (RTRIM (SUBSTR (p_param1, 11)))
1160 AND awd.base_id = igf_aw_gen_004.get_base_id_for_person (p_person_id, RTRIM (SUBSTR (p_param1, 1, 10)), TO_NUMBER (RTRIM (SUBSTR (p_param1, 11))))
1161 AND awd.fund_id = fmast.fund_id
1162 AND awd.award_status IN ('ACCEPTED', 'OFFERED')
1163 -- AND awd.notification_status_code IN ('R', 'F')
1164 AND awd.award_id = disb.award_id
1165 AND cai.cal_type = disb.ld_cal_type
1166 AND cai.sequence_number = disb.ld_sequence_number
1167 AND NVL (awd.offered_amt, 0) > 0
1168 AND
1169 NOT EXISTS
1170 (SELECT disb.ld_cal_type, disb.ld_sequence_number
1171 FROM igf_aw_awd_disb disb
1172 WHERE
1173 disb.award_id = awd.award_id
1174 MINUS
1175 SELECT ld_cal_type, ld_sequence_number
1176 FROM igf_aw_awd_prd_term apt
1177 WHERE apt.ci_cal_type = RTRIM (SUBSTR (p_param1, 1, 10))
1178 AND apt.ci_sequence_number = TO_NUMBER (RTRIM (SUBSTR (p_param1, 11)))
1179 AND apt.award_prd_cd = NVL(p_param2, award_prd_cd))
1180 UNION
1181 SELECT NULL fund_code,-1 fund_id,
1182 '<TD>Term Total</TD>' data1,
1183 igf_aw_gen_004.get_term_total (
1184 p_person_id,
1185 p_param1,
1186 p_param2,
1187 p_param3,
1188 p_param4,
1189 p_param5,
1190 p_param6,
1191 p_param7,
1192 p_flag
1193 ) data2,
1194 3 seq
1195 FROM DUAL
1196 ORDER BY seq;
1197
1198 /*get the rowid for the person in temp table for deletion*/
1199 CURSOR get_pers_del IS
1200 SELECT lttmp.rowid row_id FROM
1201 igf_aw_awd_ltr_tmp lttmp
1202 WHERE
1203 person_id = p_person_id AND
1204 ci_cal_type = RTRIM (SUBSTR (p_param1, 1, 10)) AND
1205 ci_sequence_number = TO_NUMBER (RTRIM (SUBSTR (p_param1, 11)));
1206
1207 l_get_pers_del get_pers_del%ROWTYPE;
1208
1209
1210 l_fund_code VARCHAR2 (30);
1211 l_fund_desc VARCHAR2 (80);
1212 i NUMBER;
1213 l_return_status VARCHAR2 (1) ;
1214 l_message VARCHAR2 (512);
1215 l_awd_count NUMBER;
1216 l_ret_data VARCHAR2(32000);
1217 l_rowid ROWID;
1218 BEGIN
1219 l_return_status := 'S';
1220
1221 --First delete the existing record for the person in temp table
1222 OPEN get_pers_del;
1223 LOOP
1224 FETCH get_pers_del INTO l_get_pers_del;
1225 EXIT WHEN get_pers_del%NOTFOUND;
1226
1227 igf_aw_awd_ltr_tmp_pkg.delete_row (
1228 x_rowid => l_get_pers_del.row_id
1229 );
1230 END LOOP;
1231 CLOSE get_pers_del;
1232
1233 FOR get_award_data_rec IN get_awd_data
1234 LOOP
1235 igf_aw_awd_ltr_tmp_pkg.insert_row (
1236 x_rowid => l_rowid,
1237 x_line_id => get_awd_data%ROWCOUNT,
1238 x_person_id => p_person_id,
1239 x_fund_code => get_award_data_rec.fund_code,
1240 x_fund_description => get_award_data_rec.data1,
1241 x_award_name => get_award_data_rec.data1,
1242 x_ci_cal_type => RTRIM (SUBSTR (p_param1,1,10)),
1243 x_ci_sequence_number => TO_NUMBER (RTRIM (SUBSTR (p_param1,11))),
1244 x_award_total => l_awd_tot,
1245 x_term_amount_text => get_award_data_rec.data2,
1246 x_mode => 'R'
1247 );
1248 END LOOP;
1249
1250 p_return_status := l_return_status;
1251
1252
1253
1254
1255 EXCEPTION
1256 WHEN OTHERS THEN
1257 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1258 FND_MESSAGE.SET_TOKEN('NAME','igf_aw_gen_004.award_letter_matrix' || SQLERRM);
1259 IGS_GE_MSG_STACK.ADD;
1260 App_Exception.Raise_Exception;
1261 END award_letter_matrix;
1262
1263 PROCEDURE corp_pre_process (
1264 p_document_id IN NUMBER ,
1265 p_select_type IN VARCHAR2 ,
1266 p_sys_ltr_code IN VARCHAR2 ,
1267 p_person_id IN NUMBER ,
1268 p_list_id IN NUMBER ,
1269 p_letter_type IN VARCHAR2 ,
1270 p_parameter_1 IN VARCHAR2 ,
1271 p_parameter_2 IN VARCHAR2 ,
1272 p_parameter_3 IN VARCHAR2 ,
1273 p_parameter_4 IN VARCHAR2 ,
1274 p_parameter_5 IN VARCHAR2 ,
1275 p_parameter_6 IN VARCHAR2 ,
1276 p_parameter_7 IN VARCHAR2 ,
1277 p_parameter_8 IN VARCHAR2 ,
1278 p_parameter_9 IN VARCHAR2 ,
1279 p_flag IN VARCHAR2 ,
1280 p_sql_stmt OUT NOCOPY VARCHAR2,
1281 p_exception OUT NOCOPY VARCHAR2
1282 ) IS
1283
1284 /*************************************************************
1285 Created By :Prajeesh
1286 Date Created on : 05-Feb-2002
1287 Purpose : This Procedure is the main procedure for pre processing
1288 for both missing items letter and award processing.
1289 It gets the valid persons after pre processing and
1290 generates the sql statement with the valid persons and
1291 this select statement is sent to the main concurrent
1292 manager called procedure
1293 Know limitations, enhancements or remarks
1294 Change History
1295 Who When What
1296 ridas 07-Feb-2006 Bug #5021084. Replaced function IGS_GET_DYNAMIC_SQL with GET_DYNAMIC_SQL.
1297 rajagupt 05-Oct-2005 Bug#4644213 - Award Notification Letter. Return if p_person_id is NULL and p_select_type is 'S'
1298 and if p_list_id is NULL and p_select_type is "G".
1299 bvisvana 04-Sep-2005 FA 157 - Bug # 4382371 - Award Notification Letter.
1300 Make a return without forming the sql stmt if the person_id = '-9999999'
1301 veramach 15-Apr-2004 bug 3543089
1302 Changed sizes of variables to allow more person_ids to be processed.
1303 Also added a error message in the EXCEPTION section
1304 masehgal 14-Jun-2002 # 2413695 Changed message to
1305 'IGF','IGF_AW_NO_LIST'
1306 kumma 7-JUN-2003 2853531, Modified for adding the dynamic and static group functionality for financial aid system letter
1307 Removed the cursor c_query that was fetching query from jtf_fm_queries_all , instead make a call to IGS_CO_API.get_list_query
1308 asbala 19-AUG-2003 3098262:Added check to select only active members for static person_id group
1309 (reverse chronological order - newest change first)
1310 ***************************************************************/
1311
1312 CURSOR c_map IS
1313 SELECT document_id,name
1314 FROM igs_co_mapping_v
1315 WHERE map_id=p_list_id;
1316
1317 CURSOR c_att_id(cp_itm_id ibc_citems_v.citem_id%TYPE) IS
1318 SELECT attach_fid
1319 FROM ibc_citems_v
1320 WHERE CITEM_ID = cp_itm_id;
1321
1322 --Cursor to check that if p_list_id represents a static or a dynamic person id group
1323 CURSOR c_file_name IS
1324 SELECT file_name
1325 FROM igs_pe_persid_group_all
1326 WHERE group_id = p_list_id; --Here p_List id is representing group_id for Financial Aid System Letter
1327
1328
1329
1330
1331 l_return_status VARCHAR2 (1);
1332 l_file_name igs_pe_persid_group_all.file_name%TYPE;
1333 lv_ret_sql VARCHAR2(32767);
1334 lv_status VARCHAR2(1);
1335 lv_count NUMBER;
1336 lv_data VARCHAR2(500);
1337
1338 l_static_group VARCHAR2(1) ;
1339
1340 TYPE cur_query IS REF CURSOR;
1341
1342 l_query_desc cur_query;
1343 p_person VARCHAR2(32767);
1344 l_query_str VARCHAR2(32767);
1345 l_person_id NUMBER;
1346 l_count NUMBER DEFAULT 0;
1347 l_list_numb igs_co_mapping.document_id%TYPE;
1348 l_list_name igs_co_mapping_v.name%TYPE;
1349 l_attach_fid ibc_citems_v.attach_fid%TYPE;
1350 l_query_text VARCHAR2(32767);
1351 lv_sql_code NUMBER;
1352 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
1353
1354 BEGIN
1355
1356 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1357 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.corp_pre_process.debug','Document Name '|| NVL(p_document_id , -99));
1358 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.corp_pre_process.debug','Selection Criteria '|| NVL(p_select_type , 'NULL'));
1359 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.corp_pre_process.debug','derived Letter Code -> FAAWARD/FAMISTM/FADISBT '|| NVL(p_sys_ltr_code, 'NULL'));
1360 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.corp_pre_process.debug','Person ID '|| NVL(p_person_id , -99));
1361 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.corp_pre_process.debug','List Name -> Person ID Group '|| NVL(p_list_id , -99));
1362 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.corp_pre_process.debug','derived Letter Code -> FAAWARD/FAMISTM/FADISBT '|| NVL(p_letter_type , 'NULL'));
1363 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.corp_pre_process.debug','Award Year '|| NVL(p_parameter_1 , 'NULL'));
1364 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.corp_pre_process.debug','Awarding period '|| NVL(p_parameter_2 , 'NULL'));
1365 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.corp_pre_process.debug','p_parameter_3 '|| NVL(p_parameter_3 , 'NULL'));
1366 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.corp_pre_process.debug','p_parameter_3 '|| NVL(p_parameter_4 , 'NULL'));
1367 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.corp_pre_process.debug','p_parameter_3 '|| NVL(p_parameter_5 , 'NULL'));
1368 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.corp_pre_process.debug','p_parameter_3 '|| NVL(p_parameter_6 , 'NULL'));
1369 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.corp_pre_process.debug','p_parameter_3 '|| NVL(p_parameter_7 , 'NULL'));
1370 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.corp_pre_process.debug','Not Used '|| NVL(p_parameter_8 , 'NULL'));
1371 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.corp_pre_process.debug','Not Used '|| NVL(p_parameter_9 , 'NULL'));
1372 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.corp_pre_process.debug','Override Flag '|| NVL(p_flag , 'NULL'));
1373 END IF;
1374
1375 l_static_group := 'Y';
1376
1377 IF p_sys_ltr_code = 'FAAWARD' THEN -- Process FAAWARD Letter
1378 p_person := '-9999999' ;
1379 IF p_select_type = 'S' THEN -- Select Type - S-Person/G-Group/A-Auto select
1380 IF p_person_id IS NULL THEN
1381 fnd_message.set_name('IGF','IGF_AW_NOTI_LTR_NO_PERS_NUM');
1382 fnd_file.put_line(fnd_file.log,fnd_message.get);
1383 p_exception := 'Y';
1384 RETURN;
1385 END IF;
1386 OPEN l_query_desc FOR select DISTINCT p_person_id person_id FROM DUAL;
1387 ELSIF p_select_type = 'G' THEN -- Select Type - S-Person/G-Group/A-Auto select
1388 IF p_list_id IS NULL THEN
1389 fnd_message.set_name('IGF','IGF_AW_NOTI_LTR_NO_PERS_GRP');
1390 fnd_file.put_line(fnd_file.log,fnd_message.get);
1391 p_exception := 'Y';
1392 RETURN;
1393 END IF;
1394
1395 --Bug #5021084. Replaced function IGS_GET_DYNAMIC_SQL with GET_DYNAMIC_SQL
1396 l_query_str := igs_pe_dynamic_persid_group.get_dynamic_sql(p_list_id ,lv_status,lv_group_type);
1397
1398 IF lv_status <> 'S' THEN
1399 FND_MESSAGE.SET_NAME('IGF','IGF_AW_NO_QUERY');
1400 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1401 p_exception := 'Y';
1402 RETURN;
1403 END IF;
1404
1405 --Bug #5021084. Passing Group ID if the group type is STATIC.
1406 IF lv_group_type = 'STATIC' THEN
1407 OPEN l_query_desc FOR l_query_str USING p_list_id; --Here p_list id is representing group_id for Financial Aid System Letter
1408 ELSIF lv_group_type = 'DYNAMIC' THEN
1409 OPEN l_query_desc FOR l_query_str;
1410 END IF;
1411
1412 ELSIF p_select_type = 'A' THEN -- Select Type - S-Person/G-Group/A-Auto select
1413 -- select all students who have any award in ready to send state.
1414 OPEN l_query_desc FOR
1415 SELECT
1416 DISTINCT base.person_id
1417 FROM
1418 igf_aw_award_all awd,
1419 igf_aw_fund_mast fmast,
1420 igs_ca_inst_all cai,
1421 igf_aw_awd_disb disb,
1422 igf_ap_fa_base_rec_all base
1423 WHERE
1424 fmast.ci_cal_type = LTRIM(RTRIM(SUBSTR(p_parameter_1, 1, 10))) AND
1425 fmast.ci_sequence_number = TO_NUMBER(LTRIM(RTRIM(SUBSTR(p_parameter_1, 11)))) AND
1426 awd.base_id = base.base_id AND
1427 awd.fund_id = fmast.fund_id AND
1428 awd.award_status IN ('ACCEPTED', 'OFFERED') AND
1429 awd.notification_status_code IN ('R', 'F') AND
1430 awd.award_id = disb.award_id AND
1431 cai.cal_type = disb.ld_cal_type AND
1432 cai.sequence_number = disb.ld_sequence_number
1433 AND
1434 NOT EXISTS
1435 (SELECT disb.ld_cal_type, disb.ld_sequence_number
1436 FROM igf_aw_awd_disb disb
1437 WHERE
1438 disb.award_id = awd.award_id
1439 MINUS
1440 SELECT ld_cal_type, ld_sequence_number
1441 FROM igf_aw_awd_prd_term apt
1442 WHERE apt.ci_cal_type = LTRIM(RTRIM(SUBSTR(p_parameter_1, 1, 10))) AND
1443 apt.ci_sequence_number = TO_NUMBER(LTRIM(RTRIM(SUBSTR(p_parameter_1, 11)))) AND
1444 apt.award_prd_cd = NVL(p_parameter_2, award_prd_cd));
1445 END IF; -- END Select Type - S-Person/G-Group/A-Auto select
1446 l_count := 0; -- bvisvana - bug 3724328 - For Code refactoring (Issue with huge person id groups)
1447 LOOP -- Process all selected students for award letter creation and update their state as well.
1448 FETCH l_query_desc INTO l_person_id;
1449 EXIT WHEN l_query_desc%NOTFOUND;
1450
1451 award_letter_matrix (
1452 l_person_id,
1453 p_parameter_1,
1454 p_parameter_2,
1455 p_parameter_3,
1456 p_parameter_4,
1457 p_parameter_5,
1458 p_parameter_6,
1459 p_parameter_7,
1460 p_flag,
1461 l_return_status
1462 );
1463 IF l_return_status = 'S' THEN
1464 -- bvisvana - bug 3724328 - For Code refactoring (Issue with huge person id groups)
1465 -- p_person := p_person || ', ' || l_person_id;
1466 l_count := l_count + 1;
1467 temp_person_id_array(l_count) := l_person_id;
1468 END IF;
1469 END LOOP; -- END Process all selected students for award letter creation and update their state as well.
1470 CLOSE l_query_desc;
1471
1472 -- bvisvana - bug 3724328 - For Code refactoring (Issue with huge person id groups).Removed the check for '-9999999',instead checked for l_count > 0
1473 --FA 157 - Award Notification Letter. Added the below IF condition.
1474 -- If there are no persons, then the query wouldn't have any data for the personn_id IN () clause or person_id clause.
1475 -- We RETURN so that in the calling program the 'sql_stmt is NOT NULL' check handles that and prints a message instead
1476 IF l_count = 0 THEN
1477 log_to_fnd('corp_pre_process','debug','No person available to process request. So returning.');
1478 RETURN;
1479 END IF;
1480 log_to_fnd('corp_pre_process','debug','Calling build_sql_stmt..This is for FAAWARD.');
1481 build_sql_stmt (
1482 p_parameter_1,
1483 p_sys_ltr_code,
1484 p_select_type,
1485 p_sql_stmt
1486 );
1487 log_to_fnd('corp_pre_process','debug','After build_sql_stmt. Before return to the Pre processing method of IGSCO21B.pls ');
1488 RETURN;
1489 END IF; -- END Process FAAWARD Letter
1490
1491 l_count := 0;
1492 IF p_select_type = 'S'
1493 THEN
1494 IF p_sys_ltr_code = 'FAMISTM'
1495 THEN
1496 missing_items (p_person_id, p_parameter_1, l_return_status);
1497
1498 IF l_return_status = 'S'
1499 THEN
1500 -- bvisvana - bug 3724328 - For Code refactoring (Issue with huge person id groups)
1501 l_count := l_count + 1;
1502 temp_person_id_array(l_count) := p_person_id;
1503
1504 build_sql_stmt (
1505 p_parameter_1,
1506 p_sys_ltr_code,
1507 p_select_type,
1508 p_sql_stmt
1509 );
1510 END IF;
1511
1512 ELSIF p_sys_ltr_code = 'FADISBT' THEN
1513 -- bvisvana - bug 3724328 - For Code refactoring (Issue with huge person id groups)
1514 l_count := l_count + 1;
1515 temp_person_id_array(l_count) := p_person_id;
1516
1517 build_sql_stmt (
1518 p_parameter_1,
1519 p_sys_ltr_code,
1520 p_select_type,
1521 p_sql_stmt
1522 );
1523 END IF;
1524 ELSIF p_select_type = 'L' THEN
1525 OPEN c_map;
1526 FETCH c_map INTO l_list_numb,l_list_name;
1527 CLOSE c_map;
1528
1529 OPEN c_att_id(l_list_numb);
1530 FETCH c_att_id INTO l_attach_fid;
1531 CLOSE c_att_id;
1532
1533 IF c_att_id%NOTFOUND OR l_attach_fid IS NULL THEN
1534 FND_MESSAGE.SET_NAME('IGF','IGF_AW_NO_LIST');
1535 FND_MESSAGE.SET_TOKEN('LIST', l_list_name);
1536 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1537 p_exception := 'Y';
1538 RETURN;
1539 END IF;
1540
1541 --fetching query
1542 IGS_CO_GEN_004.get_list_query(l_attach_fid,l_query_text);
1543
1544 IF p_flag='N' AND p_sys_ltr_code = 'FAAWARD' THEN
1545 IF p_parameter_2 IS NULL THEN
1546 FND_MESSAGE.SET_NAME('IGF','IGF_AW_SF_PARAM_ERR_DTL');--Bug ID 2539299
1547 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1548 p_exception := 'Y';
1549 RETURN;
1550 END IF;
1551 END IF;
1552
1553 IF l_query_text IS NULL
1554 THEN
1555 FND_MESSAGE.SET_NAME('IGF','IGF_AW_NO_LIST');
1556 FND_MESSAGE.SET_TOKEN('LIST', l_list_name);
1557 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1558 p_exception := 'Y';
1559 RETURN;
1560 END IF;
1561
1562 l_query_str := 'SELECT distinct person_id FROM '
1563 || '('
1564 || l_query_text
1565 || ')';
1566
1567 OPEN l_query_desc FOR l_query_str;
1568 l_count := 0; -- bvisvana - bug 3724328 - For Code refactoring (Issue with huge person id groups)
1569 LOOP
1570 FETCH l_query_desc INTO l_person_id;
1571
1572 EXIT WHEN l_query_desc%NOTFOUND;
1573
1574 IF p_sys_ltr_code = 'FAAWARD'
1575 THEN
1576 award_letter_matrix (
1577 l_person_id,
1578 p_parameter_1,
1579 p_parameter_2,
1580 p_parameter_3,
1581 p_parameter_4,
1582 p_parameter_5,
1583 p_parameter_6,
1584 p_parameter_7,
1585 p_flag,
1586 l_return_status
1587 );
1588 ELSIF p_sys_ltr_code = 'FAMISTM'
1589 THEN
1590 missing_items (l_person_id, p_parameter_1, l_return_status);
1591
1592 ELSIF p_sys_ltr_code = 'FADISBT'
1593 THEN
1594 l_return_status := 'S';
1595 END IF;
1596
1597 IF l_return_status = 'S'
1598 THEN
1599 l_count := l_count + 1;
1600
1601 /*IF l_count = 1 THEN
1602 p_person := l_person_id;
1603 ELSE
1604 p_person := p_person
1605 || ','
1606 || l_person_id;
1607 END IF;*/
1608 temp_person_id_array(l_count) := l_person_id;
1609 END IF;
1610 END LOOP;
1611 CLOSE l_query_desc;
1612
1613 -- bvisvana - bug 3724328 - For Code refactoring (Issue with huge person id groups)
1614 IF l_count > 0 THEN
1615 -- IF p_person IS NOT NULL THEN
1616 log_to_fnd('corp_pre_process','debug','select type = L ..Calling build_sql_stmt');
1617 build_sql_stmt (
1618 p_parameter_1,
1619 p_sys_ltr_code,
1620 p_select_type,
1621 p_sql_stmt
1622 );
1623 END IF;
1624
1625 /*===============================================================================*/
1626 --Logic for Person Id Group
1627 ELSIF p_select_type = 'G' THEN
1628
1629 IF p_list_id IS NULL OR p_person_id IS NOT NULL THEN
1630 FND_MESSAGE.SET_NAME('IGF','IGF_AW_WRNG_PRAM_PG_ID');
1631 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1632 p_exception := 'Y';
1633 RETURN;
1634 END IF;
1635
1636
1637
1638 OPEN c_file_name;
1639 FETCH c_file_name INTO l_file_name;
1640 CLOSE c_file_name;
1641
1642 IF l_file_name IS NOT NULL THEN
1643 --Dynamic Person Id Group
1644 l_static_group := 'N';
1645 --igs_pe_dynamic_persid_group.igs_get_dynamic_sql(p_list_id ,l_query_str,lv_status,lv_count,lv_data);
1646
1647 --Bug #5021084. Replaced function IGS_GET_DYNAMIC_SQL with GET_DYNAMIC_SQL
1648 lv_group_type := NULL;
1649 l_query_str := igs_pe_dynamic_persid_group.get_dynamic_sql(p_list_id ,lv_status,lv_group_type);
1650
1651 IF lv_status <> 'S' THEN
1652 FND_MESSAGE.SET_NAME('IGF','IGF_AW_NO_QUERY');
1653 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1654 p_exception := 'Y';
1655 RETURN;
1656 END IF;
1657 ELSE
1658 l_static_group := 'Y';
1659 -- Static Person Id Group
1660 l_query_str := ' SELECT distinct person_id FROM igs_pe_prsid_grp_mem_all WHERE group_id = :l_group_id AND sysdate BETWEEN start_date AND NVL(end_date,sysdate)';
1661 END IF;
1662
1663
1664 IF p_flag='N' AND p_sys_ltr_code = 'FAAWARD' THEN
1665 IF p_parameter_2 IS NULL THEN
1666 FND_MESSAGE.SET_NAME('IGF','IGF_AW_SF_PARAM_ERR_DTL');--Bug ID 2539299
1667 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1668 p_exception := 'Y';
1669 RETURN;
1670 END IF;
1671 END IF;
1672
1673 IF l_static_group = 'N' THEN
1674 --Bug #5021084. Passing Group ID if the group type is STATIC.
1675 IF lv_group_type = 'STATIC' THEN
1676 OPEN l_query_desc FOR l_query_str USING p_list_id; --Here p_list id is representing group_id
1677 ELSIF lv_group_type = 'DYNAMIC' THEN
1678 OPEN l_query_desc FOR l_query_str;
1679 END IF;
1680 ELSE
1681 OPEN l_query_desc FOR l_query_str USING p_list_id; --Here p_list id is representing group_id for Financial Aid System Letter
1682 END IF;
1683
1684 l_count := 0; -- bvisvana - bug 3724328 - For Code refactoring (Issue with huge person id groups)
1685 LOOP
1686
1687 FETCH l_query_desc INTO l_person_id;
1688
1689 EXIT WHEN l_query_desc%NOTFOUND;
1690 IF p_sys_ltr_code = 'FAAWARD' THEN
1691 award_letter_matrix (
1692 l_person_id,
1693 p_parameter_1,
1694 p_parameter_2,
1695 p_parameter_3,
1696 p_parameter_4,
1697 p_parameter_5,
1698 p_parameter_6,
1699 p_parameter_7,
1700 p_flag,
1701 l_return_status
1702 );
1703 ELSIF p_sys_ltr_code = 'FAMISTM' THEN
1704 missing_items (l_person_id, p_parameter_1, l_return_status);
1705 ELSIF p_sys_ltr_code = 'FADISBT' THEN
1706 l_return_status := 'S';
1707 END IF;
1708
1709 IF l_return_status = 'S' THEN
1710
1711 l_count := l_count + 1;
1712
1713 /*IF l_count = 1 THEN
1714 p_person := l_person_id;
1715 ELSE
1716 p_person := p_person
1717 || ','
1718 || l_person_id;
1719 END IF;*/
1720 temp_person_id_array(l_count) := l_person_id;
1721 END IF;
1722 END LOOP;
1723 CLOSE l_query_desc;
1724
1725 -- bvisvana - bug 3724328 - For Code refactoring (Issue with huge person id groups)
1726 IF l_count > 0 THEN
1727 --IF p_person IS NOT NULL THEN
1728 log_to_fnd('corp_pre_process','debug','select type = G ..Calling build_sql_stmt');
1729 build_sql_stmt (
1730 p_parameter_1,
1731 p_sys_ltr_code,
1732 p_select_type,
1733 p_sql_stmt
1734 );
1735 END IF;
1736
1737
1738 /*===============================================================================*/
1739
1740 END IF;
1741
1742 EXCEPTION
1743 WHEN OTHERS THEN
1744 lv_sql_code := SQLCODE;
1745 IF lv_sql_code = -06502 THEN
1746 fnd_message.set_name('IGF','IGF_AW_PERS_OVFLOW');
1747 ELSE
1748 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1749 FND_MESSAGE.SET_TOKEN('NAME','igf_aw_gen_004.corp_pre_process - ' || SQLERRM);
1750 END IF;
1751 IGS_GE_MSG_STACK.ADD;
1752 App_Exception.Raise_Exception;
1753
1754 END corp_pre_process;
1755
1756 PROCEDURE missing_items (
1757 p_person_id IN NUMBER,
1758 p_param1 IN VARCHAR2,
1759 p_return_status OUT NOCOPY VARCHAR2
1760 ) IS
1761
1762 /*************************************************************
1763 Created By :Prajeesh
1764 Date Created on : 05-Feb-2002
1765 Purpose : This procedure is mainly for the Preprocessing
1766 for the missing items. It checks if any items
1767 is not corresponded for the person and satisfies
1768 the validations like sum of min frequency with
1769 the correspondence date is greater than sysdate and
1770 max notifications has not exceeded for atleast
1771 one item. Then that person is sent a notification
1772 with all missing details
1773 Know limitations, enhancements or remarks
1774 Change History
1775 Who When What
1776
1777 (reverse chronological order - newest change first)
1778 ***************************************************************/
1779
1780 /* Main Cursor to Get the incomplete items*/
1781 CURSOR c_tdcur(l_base_id igf_ap_fa_base_rec_all.base_id%TYPE) IS
1782 SELECT tdii.base_id,
1783 tdii.item_sequence_number,
1784 tdii.add_date,
1785 tdii.status_date,
1786 tdii.corsp_date,
1787 tdii.corsp_count,
1788 tdii.inactive_flag,
1789 tdii.freq_attempt,
1790 tdii.max_attempt
1791 FROM igf_ap_td_item_inst tdii
1792 WHERE NVL(tdii.inactive_flag,'N') = 'N'
1793 AND tdii.status IN ('INC', 'REQ')
1794 AND tdii.base_id = l_base_id;
1795
1796 /* Cursor to get the baseid for a given person id in an award year*/
1797
1798 CURSOR c_base IS
1799 SELECT fabase.base_id
1800 FROM
1801 igf_ap_fa_base_rec_all fabase
1802 WHERE person_id = p_person_id AND
1803 fabase.ci_cal_type = RTRIM (SUBSTR (p_param1, 1, 10)) AND
1804 fabase.ci_sequence_number = TO_NUMBER (RTRIM (SUBSTR (p_param1, 11)));
1805
1806 l_tdrec c_tdcur%ROWTYPE;
1807 l_base igf_ap_fa_base_rec_all.base_id%TYPE;
1808 l_newcorsp_dt igf_ap_td_item_inst_all.corsp_date%TYPE;
1809 l_new_cnt NUMBER;
1810 BEGIN
1811
1812 -- Get the Baseid for the person in an award year
1813
1814 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1815 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.missing_items.debug','p_person_id:'||p_person_id);
1816 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.missing_items.debug','p_param1:'||p_param1);
1817 END IF;
1818
1819 OPEN c_base;
1820 FETCH c_base INTO l_base;
1821 CLOSE c_base;
1822
1823
1824 OPEN c_tdcur(l_base);
1825 --For each record check if correspondenc date exists or sum of frequncy and correspondence date is lesser than
1826 -- sysdate or has not reached the max notifications for atleast on todo item then return success and the person is sent the mail
1827
1828 LOOP
1829
1830 FETCH c_tdcur INTO l_tdrec;
1831 EXIT WHEN c_tdcur%NOTFOUND;
1832 IF l_tdrec.corsp_date IS NULL THEN
1833 p_return_status := 'S';
1834 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1835 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.missing_items.debug','p_return_status(1):'||p_return_status);
1836 END IF;
1837 EXIT;
1838 ELSE
1839 l_newcorsp_dt := l_tdrec.corsp_date + NVL (l_tdrec.freq_attempt, 0);
1840 l_new_cnt := NVL(l_tdrec.corsp_count, 0) + 1;
1841
1842 IF (l_tdrec.max_attempt IS NOT NULL AND l_new_cnt <= l_tdrec.max_attempt AND l_newcorsp_dt <= SYSDATE) OR (l_tdrec.max_attempt IS NULL AND l_newcorsp_dt <= SYSDATE) THEN
1843 p_return_status := 'S';
1844 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1845 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.missing_items.debug','p_return_status(2):'||p_return_status);
1846 END IF;
1847 EXIT;
1848 ELSE
1849 p_return_status := 'F';
1850
1851 END IF;
1852 END IF;
1853 END LOOP;
1854
1855 CLOSE c_tdcur;
1856 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1857 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.missing_items.debug','p_return_status:'||p_return_status);
1858 END IF;
1859 EXCEPTION
1860 WHEN OTHERS THEN
1861 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1862 FND_MESSAGE.SET_TOKEN('NAME','igf_aw_gen_004.missing_items');
1863 IGS_GE_MSG_STACK.ADD;
1864 App_Exception.Raise_Exception;
1865 END missing_items;
1866
1867 PROCEDURE loan_disbursement_update (
1868 p_person_id IN NUMBER,
1869 p_award_year IN VARCHAR2
1870 ) IS
1871
1872 /*************************************************************
1873 Created By :Prajeesh
1874 Date Created on : 05-Feb-2002
1875 Purpose : This Procedure is the Post processing Procedure
1876 for loan disbursement. AFter sending a mail.
1877 It updates the notification date with current
1878 date for each disbursement record for the person
1879 in an award year
1880 Know limitations, enhancements or remarks
1881 Change History
1882 Who When What
1883
1884 (reverse chronological order - newest change first)
1885 ***************************************************************/
1886
1887 /*Cursor to get the disbursement records for the person in an award year*/
1888
1889 CURSOR cur_loan_disb_rec IS
1890 SELECT disb.rowid row_id,disb.*
1891 FROM
1892 igf_db_awd_disb_dtl_all disb
1893 WHERE award_id IN (SELECT award_id
1894 FROM igf_aw_award_all aw,
1895 igf_ap_fa_base_rec_all fbase
1896 WHERE fbase.base_id = aw.base_id AND
1897 fbase.person_id = p_person_id AND
1898 fbase.ci_cal_type = RTRIM(SUBSTR(p_award_year,1,10)) AND
1899 fbase.ci_sequence_number = TO_NUMBER(RTRIM(SUBSTR(p_award_year,11))));
1900 l_cur_loan_disb_rec cur_loan_disb_rec%ROWTYPE;
1901
1902 BEGIN
1903 /* Update the notification date as sysdate for the disbursement records*/
1904 OPEN cur_loan_disb_rec;
1905 LOOP
1906 FETCH cur_loan_disb_rec INTO l_cur_loan_disb_rec;
1907 EXIT WHEN cur_loan_disb_rec%NOTFOUND;
1908 igf_db_awd_disb_dtl_pkg.update_row (
1909 X_Mode => 'R',
1910 x_rowid => l_cur_loan_disb_rec.row_id,
1911 x_award_id => l_cur_loan_disb_rec.award_id,
1912 x_disb_num => l_cur_loan_disb_rec.disb_num,
1913 x_disb_seq_num => l_cur_loan_disb_rec.disb_seq_num,
1914 x_disb_gross_amt => l_cur_loan_disb_rec.disb_gross_amt,
1915 x_fee_1 => l_cur_loan_disb_rec.fee_1,
1916 x_fee_2 => l_cur_loan_disb_rec.fee_2,
1917 x_disb_net_amt => l_cur_loan_disb_rec.disb_net_amt,
1918 x_disb_adj_amt => l_cur_loan_disb_rec.disb_adj_amt,
1919 x_disb_date => l_cur_loan_disb_rec.disb_date,
1920 x_fee_paid_1 => l_cur_loan_disb_rec.fee_paid_1,
1921 x_fee_paid_2 => l_cur_loan_disb_rec.fee_paid_2,
1922 x_disb_activity => l_cur_loan_disb_rec.disb_activity,
1923 x_disb_batch_id => l_cur_loan_disb_rec.disb_batch_id,
1924 x_disb_ack_date => l_cur_loan_disb_rec.disb_ack_date,
1925 x_booking_batch_id => l_cur_loan_disb_rec.booking_batch_id,
1926 x_booked_date => l_cur_loan_disb_rec.booked_date,
1927 x_disb_status => l_cur_loan_disb_rec.disb_status,
1928 x_disb_status_date => l_cur_loan_disb_rec.disb_status_date,
1929 x_sf_status => l_cur_loan_disb_rec.sf_status, -- Accepted
1930 x_sf_status_date => l_cur_loan_disb_rec.sf_status_date,
1931 x_sf_invoice_num => l_cur_loan_disb_rec.sf_invoice_num,
1932 x_spnsr_credit_id => l_cur_loan_disb_rec.spnsr_credit_id,
1933 x_spnsr_charge_id => l_cur_loan_disb_rec.spnsr_charge_id,
1934 x_sf_credit_id => l_cur_loan_disb_rec.sf_credit_id,
1935 x_error_desc => l_cur_loan_disb_rec.error_desc,
1936 x_notification_date => TRUNC(SYSDATE),
1937 x_interest_rebate_amt => l_cur_loan_disb_rec.interest_rebate_amt,
1938 x_ld_cal_type => l_cur_loan_disb_rec.ld_cal_type,
1939 x_ld_sequence_number => l_cur_loan_disb_rec.ld_sequence_number
1940 );
1941
1942 END LOOP;
1943 CLOSE cur_loan_disb_rec;
1944 EXCEPTION
1945 WHEN OTHERS THEN
1946 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1947 FND_MESSAGE.SET_TOKEN('NAME','igf_aw_gen_004.loan_disbursement_update');
1948 IGS_GE_MSG_STACK.ADD;
1949 App_Exception.Raise_Exception;
1950
1951 END loan_disbursement_update;
1952
1953 PROCEDURE missing_items_update (
1954 p_person_id IN NUMBER,
1955 p_award_year IN VARCHAR2
1956 ) IS
1957
1958 /*************************************************************
1959 Created By :Prajeesh
1960 Date Created on : 05-Feb-2002
1961 Purpose : This Procedure is for the post processing. It updates
1962 the correspondence date and count and also the
1963 correspondence text is made active Y
1964 Know limitations, enhancements or remarks
1965 Change History
1966 Who When What
1967 --bkkumar 04-jun-2003 Bug #2858504
1968 -- Added legacy_record_flag
1969 -- in the table handler calls for igf_ap_td_item_inst_pkg.update_row
1970 (reverse chronological order - newest change first)
1971 ***************************************************************/
1972
1973 /*Cursor to get the incomplete to items for the person in an award year*/
1974
1975 CURSOR cur_incomp_items
1976 IS
1977 SELECT tdii.rowid row_id,tdii.*
1978 FROM
1979 igf_ap_td_item_inst_all tdii,
1980 igf_ap_fa_base_rec_all facon
1981 WHERE
1982 facon.base_id = tdii.base_id AND
1983 NVL(tdii.inactive_flag,'N')='N' AND
1984 tdii.status IN ('INC','REQ') AND
1985 facon.person_id = p_person_id AND
1986 facon.ci_cal_type = RTRIM(SUBSTR(p_award_year,1,10)) AND
1987 facon.ci_sequence_number = TO_NUMBER(RTRIM(SUBSTR(p_award_year,11)));
1988 l_cur_incomp_items cur_incomp_items%ROWTYPE;
1989
1990 /*Cursor to get the correspondence text for the person*/
1991
1992 CURSOR cur_corr_text
1993 IS
1994 SELECT ctext.rowid row_id,ctext.*
1995 FROM
1996 igf_ap_st_corr_text ctext
1997 WHERE
1998 ctext.active = 'N' AND
1999 ctext.base_id IN (SELECT base_id
2000 FROM
2001 igf_ap_fa_base_rec_all where person_id=p_person_id);
2002
2003 l_cur_corr_text cur_corr_text%ROWTYPE;
2004
2005
2006 BEGIN
2007
2008 /*Update the correspondence text for the person as active Y thus it cant be changed again*/
2009
2010 OPEN cur_corr_text;
2011 LOOP
2012 FETCH cur_corr_text INTO l_cur_corr_text;
2013 EXIT WHEN cur_corr_text%NOTFOUND;
2014
2015 igf_ap_st_corr_text_pkg.update_row (
2016 x_mode => 'R',
2017 x_rowid => l_cur_corr_text.row_id,
2018 x_corsp_id => l_cur_corr_text.corsp_id,
2019 x_base_id => l_cur_corr_text.base_id,
2020 x_custom_text => l_cur_corr_text.custom_text,
2021 x_run_date => TRUNC(SYSDATE),
2022 x_active => 'Y'
2023 );
2024 END LOOP;
2025 CLOSE cur_corr_text;
2026
2027 /*Increment the incomplete to do items correspondence count with 1 and
2028 update the correspondence date with sysdate*/
2029
2030 OPEN cur_incomp_items;
2031 LOOP
2032 FETCH cur_incomp_items INTO l_cur_incomp_items;
2033 EXIT WHEN cur_incomp_items%NOTFOUND;
2034
2035 igf_ap_td_item_inst_pkg.update_row (
2036 x_rowid => l_cur_incomp_items.row_id,
2037 x_base_id => l_cur_incomp_items.base_id,
2038 x_item_sequence_number => l_cur_incomp_items.item_sequence_number,
2039 x_status => l_cur_incomp_items.status,
2040 x_status_date => l_cur_incomp_items.status_date,
2041 x_add_date => l_cur_incomp_items.add_date,
2042 x_corsp_date => TRUNC(SYSDATE),
2043 x_corsp_count => NVL(l_cur_incomp_items.corsp_count,0) + 1,
2044 x_inactive_flag => l_cur_incomp_items.inactive_flag,
2045 x_required_for_application => l_cur_incomp_items.required_for_application,
2046 x_freq_attempt => l_cur_incomp_items.freq_attempt,
2047 x_max_attempt => l_cur_incomp_items.max_attempt,
2048 x_mode => 'R',
2049 x_legacy_record_flag => l_cur_incomp_items.legacy_record_flag,
2050 x_clprl_id => l_cur_incomp_items.clprl_id
2051 );
2052 END LOOP;
2053 CLOSE cur_incomp_items;
2054
2055 EXCEPTION
2056 WHEN OTHERS THEN
2057 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2058 FND_MESSAGE.SET_TOKEN('NAME','IGF_AW_GEN_004.mising_items_update');
2059 IGS_GE_MSG_STACK.ADD;
2060 App_Exception.Raise_Exception;
2061 END missing_items_update;
2062
2063 FUNCTION get_award_desc(
2064 p_person_id IN NUMBER,
2065 p_cal_type IN VARCHAR2,
2066 p_sequence_number IN NUMBER
2067 ) RETURN VARCHAR2 IS
2068
2069 /*************************************************************
2070 Created By :Prajeesh
2071 Date Created on : 05-Feb-2002
2072 Purpose : This function is used in award letter view to get
2073 the award description and message in given format
2074 Know limitations, enhancements or remarks
2075 Change History
2076 Who When What
2077
2078 (reverse chronological order - newest change first)
2079 ***************************************************************/
2080
2081 /*This function is used in View to get the award message and award description in desired format*/
2082
2083 CURSOR cur_table_data IS
2084 SELECT DECODE (lt.fund_description,
2085 '<B>Award Type</B>', '<TABLE BORDER=1><TR><TD>' || lt.fund_description || '</TD><TD><B>Award Message</B></TD></TR>',
2086 '-', '</TABLE>',
2087 '<TR><TD>' || NVL (lt.fund_description, '-') || '</TD><TD>' || NVL (fmast.awd_notice_txt, '-') || '</TD></TR>') award_description
2088 FROM igf_aw_awd_ltr_tmp lt,
2089 igf_aw_fund_mast_all fmast
2090 WHERE lt.fund_code = fmast.fund_code(+)
2091 AND lt.ci_cal_type = fmast.ci_cal_type(+)
2092 AND lt.ci_sequence_number = fmast.ci_sequence_number(+)
2093 AND lt.person_id = p_person_id
2094 AND lt.ci_cal_type = p_cal_type
2095 AND lt.ci_sequence_number = p_sequence_number
2096 ORDER BY line_id;
2097
2098 l_return_data VARCHAR2(32000);
2099
2100 BEGIN
2101 FOR rec_data IN cur_table_data
2102 LOOP
2103 l_return_data := l_return_data||rec_data.award_description;
2104 END LOOP;
2105 RETURN l_return_data;
2106
2107 EXCEPTION
2108 WHEN OTHERS THEN
2109 RETURN NULL;
2110
2111 END get_award_desc;
2112
2113
2114 PROCEDURE award_letter_update (
2115 p_person_id IN NUMBER,
2116 p_award_year IN VARCHAR2,
2117 p_award_prd_cd IN VARCHAR
2118 ) IS
2119
2120 /*************************************************************
2121 Created By :Prajeesh
2122 Date Created on : 05-Feb-2002
2123 Purpose : This Procedure is post processing one. It updates
2124 the notification status and status date after
2125 sending the letter. Status is made sent
2126 Know limitations, enhancements or remarks
2127 Change History
2128 Who When What
2129 rasahoo 18-NOV-2003 FA 128 - ISIR update 2004-05
2130 added new parameter award_fmly_contribution_type to
2131 igf_ap_fa_base_rec_pkg.update_row
2132 ugummall 25-SEP-2003 FA 126 Multiple FA Offices
2133 added new parameter assoc_org_num to
2134 igf_ap_fa_base_rec_pkg.update_row call.
2135
2136 masehgal 11-Nov-2002 FA 101 - SAP Obsoletion
2137 removed packaging hold
2138
2139 masehgal 25-Sep-2002 FA 104 - To Do Enhancements
2140 Added manual_disb_hold in FA Base update
2141
2142 (reverse chronological order - newest change first)
2143 ***************************************************************/
2144
2145 BEGIN
2146
2147 -- Update award notification status to s = Sent.
2148 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2149 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.award_letter_update.debug', 'p_person_id - ' || p_person_id);
2150 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.award_letter_update.debug', 'p_award_year - ' || p_award_year);
2151 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_004.award_letter_update.debug', 'p_award_prd_cd - ' || p_award_prd_cd);
2152 END IF;
2153
2154 igf_aw_gen.update_notification_status (
2155 p_cal_type => RTRIM (SUBSTR (p_award_year,1,10)),
2156 p_seq_num => TO_NUMBER (RTRIM (SUBSTR (p_award_year, 11))),
2157 p_awarding_period => p_award_prd_cd,
2158 p_base_id => igf_aw_gen_004.get_base_id_for_person (p_person_id, RTRIM (SUBSTR (p_award_year, 1, 10)), TO_NUMBER (RTRIM (SUBSTR (p_award_year, 11)))),
2159 p_notification_status_code => 'S',
2160 p_notification_status_date => TRUNC(SYSDATE),
2161 p_called_from => 'IGFAW13B'
2162 ) ;
2163
2164 EXCEPTION
2165 WHEN OTHERS THEN
2166 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2167 FND_MESSAGE.SET_TOKEN('NAME','IGF_AW_GEN_004.AWARD_LETTER_UPDATE');
2168 IGS_GE_MSG_STACK.ADD;
2169 App_Exception.Raise_Exception;
2170
2171 END award_letter_update ;
2172
2173
2174
2175 FUNCTION get_corr_cust_text(p_person_id IN NUMBER)
2176 /*************************************************************
2177 Created By :Prajeesh
2178 Date Created on : 05-Feb-2002
2179 Purpose : This Function is used in view to get the
2180 correspondence text for the person
2181 Know limitations, enhancements or remarks
2182 Change History
2183 Who When What
2184
2185 (reverse chronological order - newest change first)
2186 ***************************************************************/
2187 RETURN VARCHAR2 IS
2188 l_data_text VARCHAR2(32000);
2189 CURSOR c_cust_text IS
2190 SELECT corr.custom_text
2191 FROM
2192 igf_ap_st_corr_text corr,
2193 igf_ap_fa_base_rec_all fbase
2194 WHERE
2195 fbase.base_id = corr.base_id AND
2196 fbase.person_id = p_person_id AND
2197 corr.active = 'Y';
2198
2199 BEGIN
2200 OPEN c_cust_text;
2201 FETCH c_cust_text INTO l_data_text;
2202 CLOSE c_cust_text;
2203 RETURN l_data_text;
2204
2205 EXCEPTION
2206 WHEN OTHERS THEN
2207 NULL;
2208
2209 END get_corr_cust_text;
2210 END igf_aw_gen_004;
[Home] [Help]