[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_COA_GEN
Source
1 PACKAGE BODY igf_aw_coa_gen AS
2 /* $Header: IGFAW17B.pls 120.7 2006/04/19 01:17:34 akomurav noship $ */
3 ------------------------------------------------------------------
4 --Created by : veramach, Oracle India
5 --Date created: 07-OCT-2004
6 --
7 --Purpose:
8 -- Generic APIs for the COA module
9 --
10 --Known limitations/enhancements and/or remarks:
11 --
12 --
13 --Change History:
14 --Who When What
15 --veramach 21-Dec-2004 Bug 4078547
16 -- Modified cursors c_org,c_prg_type,c_prog_loc,c_prog_code,c_att_type,c_att_mode
17 --
18 --ridas 09-Aug-2005 Bug #4164450. Added new validations to check
19 -- the program offering options
20 -------------------------------------------------------------------
21
22 g_unlock_level NUMBER;
23
24 FUNCTION coa_amount(
25 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
26 p_awd_prd_code igf_aw_award_prd.award_prd_cd%TYPE,
27 p_use_direct_costs igf_aw_coa_items.fixed_cost%TYPE
28 ) RETURN NUMBER AS
29 ------------------------------------------------------------------
30 --Created by : veramach, Oracle India
31 --Date created: 07-OCT-2004
32 --
33 --Purpose:
34 --
35 --
36 --Known limitations/enhancements and/or remarks:
37 --
38 --Change History:
39 --Who When What
40 -------------------------------------------------------------------
41 CURSOR c_coa_amount(
42 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
43 cp_awd_prd_code igf_aw_award_prd.award_prd_cd%TYPE,
44 cp_use_direct_costs igf_aw_coa_items.fixed_cost%TYPE
45 ) IS
46 SELECT SUM(coa.amount)
47 FROM igf_aw_coa_itm_terms coa,
48 igf_aw_awd_prd_term trms,
49 igf_ap_fa_base_rec_all fa,
50 igf_aw_coa_items item
51 WHERE fa.base_id = coa.base_id
52 AND fa.ci_cal_type = trms.ci_cal_type
53 AND fa.ci_sequence_number = trms.ci_sequence_number
54 AND coa.ld_cal_type = trms.ld_cal_type
55 AND coa.ld_sequence_number = trms.ld_sequence_number
56 AND fa.base_id = cp_base_id
57 AND trms.award_prd_cd = cp_awd_prd_code
58 AND item.base_id = coa.base_id
59 AND item.item_code = coa.item_code
60 AND (
61 (cp_use_direct_costs = 'Y' AND NVL(item.fixed_cost, 'N') = 'Y')
62 OR (cp_use_direct_costs = 'N' AND NVL(item.fixed_cost, 'N') IN ('Y','N')
63 ));
64
65 ln_coa NUMBER;
66
67 CURSOR c_coa_amount_awd(
68 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
69 cp_use_direct_costs igf_aw_coa_items.fixed_cost%TYPE
70 ) IS
71 SELECT SUM(coa.amount)
72 FROM igf_aw_coa_itm_terms coa,
73 igf_aw_coa_items item
74 WHERE coa.base_id = cp_base_id
75 AND item.base_id = coa.base_id
76 AND item.item_code = coa.item_code
77 AND (
78 (cp_use_direct_costs = 'Y' AND NVL(item.fixed_cost, 'N') = 'Y')
79 OR (cp_use_direct_costs = 'N' AND NVL(item.fixed_cost, 'N') IN('Y','N')
80 ));
81
82 BEGIN
83
84 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
85 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.coa_amount.debug','p_base_id:'||p_base_id);
86 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.coa_amount.debug','p_awd_prd_code:'||p_awd_prd_code);
87 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.coa_amount.debug','p_use_direct_costs:'||p_use_direct_costs);
88 END IF;
89
90 IF p_awd_prd_code IS NOT NULL THEN
91 ln_coa := NULL;
92 OPEN c_coa_amount(p_base_id,p_awd_prd_code,NVL(p_use_direct_costs,'N'));
93 FETCH c_coa_amount INTO ln_coa;
94 CLOSE c_coa_amount;
95 ELSE
96 ln_coa := NULL;
97 OPEN c_coa_amount_awd(p_base_id,NVL(p_use_direct_costs,'N'));
98 FETCH c_coa_amount_awd INTO ln_coa;
99 CLOSE c_coa_amount_awd;
100 END IF;
101 RETURN ln_coa;
102
103 EXCEPTION
104 WHEN OTHERS THEN
105 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
106 fnd_message.set_token('NAME','IGF_AW_COA_GEN.COA_AMOUNT ' || SQLERRM);
107 igs_ge_msg_stack.add;
108 app_exception.raise_exception;
109
110 END coa_amount;
111
112 FUNCTION award_amount(
113 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
114 p_awd_prd_code igf_aw_award_prd.award_prd_cd%TYPE,
115 p_award_id igf_aw_award_all.award_id%TYPE DEFAULT NULL
116 ) RETURN NUMBER AS
117 ------------------------------------------------------------------
118 --Created by : veramach, Oracle India
119 --Date created: 07-OCT-2004
120 --
121 --Purpose:
122 --
123 --
124 --Known limitations/enhancements and/or remarks:
125 --
126 --Change History:
127 --Who When What
128 -------------------------------------------------------------------
129
130 CURSOR c_award_amount(
131 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
132 cp_awd_prd_code igf_aw_award_prd.award_prd_cd%TYPE,
133 cp_award_id igf_aw_award_all.award_id%TYPE
134 ) IS
135 SELECT SUM(NVL(disb.disb_gross_amt, 0)) award_amount
136 FROM igf_aw_award_all awd,
137 igf_aw_awd_disb_all disb,
138 igf_aw_awd_prd_term aprd,
139 igf_ap_fa_base_rec_all fa
140 WHERE disb.award_id = awd.award_id
141 AND awd.base_id = cp_base_id
142 AND awd.base_id = fa.base_id
143 AND awd.award_status IN('OFFERED', 'ACCEPTED')
144 AND disb.trans_type <> 'C'
145 AND disb.ld_cal_type = aprd.ld_cal_type
146 AND disb.ld_sequence_number = aprd.ld_sequence_number
147 AND aprd.award_prd_cd = cp_awd_prd_code
148 AND aprd.ci_cal_type = fa.ci_cal_type
149 AND aprd.ci_sequence_number = fa.ci_sequence_number
150 AND awd.award_id = NVL(cp_award_id,awd.award_id);
151
152 ln_award NUMBER;
153
154 CURSOR c_award_amount_awd(
155 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
156 cp_award_id igf_aw_award_all.award_id%TYPE
157 ) IS
158 SELECT SUM(NVL(disb.disb_gross_amt, 0)) award_amount
159 FROM igf_aw_award_all awd,
160 igf_aw_awd_disb_all disb
161 WHERE disb.award_id = awd.award_id
162 AND awd.base_id = cp_base_id
163 AND awd.award_status IN ('OFFERED', 'ACCEPTED')
164 AND disb.trans_type <> 'C'
165 AND awd.award_id = NVL(cp_award_id,awd.award_id);
166
167 BEGIN
168
169 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
170 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.award_amount.debug','p_base_id:'||p_base_id);
171 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.award_amount.debug','p_awd_prd_code:'||p_awd_prd_code);
172 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.award_amount.debug','p_award_id:'||p_award_id);
173 END IF;
174 IF p_awd_prd_code IS NOT NULL THEN
175 ln_award := NULL;
176 OPEN c_award_amount(p_base_id,p_awd_prd_code,p_award_id);
177 FETCH c_award_amount INTO ln_award;
178 CLOSE c_award_amount;
179 ELSE
180 ln_award := NULL;
181 OPEN c_award_amount_awd(p_base_id,p_award_id);
182 FETCH c_award_amount_awd INTO ln_award;
183 CLOSE c_award_amount_awd;
184 END IF;
185 RETURN ln_award;
186
187 EXCEPTION
188 WHEN OTHERS THEN
189 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
190 fnd_message.set_token('NAME','IGF_AW_COA_GEN.AWARD_AMOUNT ' || SQLERRM);
191 igs_ge_msg_stack.add;
192 app_exception.raise_exception;
193
194 END award_amount;
195
196 FUNCTION isCoaLocked(
197 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
198 p_item_code igf_aw_item.item_code%TYPE DEFAULT NULL,
199 p_ld_cal_type igs_ca_inst.cal_type%TYPE DEFAULT NULL,
200 p_ld_sequence_number igs_ca_inst.sequence_number%TYPE DEFAULT NULL
201 ) RETURN BOOLEAN AS
202 ------------------------------------------------------------------
203 --Created by : veramach, Oracle India
204 --Date created: 07-OCT-2004
205 --
206 --Purpose:
207 --
208 --
209 --Known limitations/enhancements and/or remarks:
210 --
211 --Change History:
212 --Who When What
213 -------------------------------------------------------------------
214
215 -- Get item/term level lock
216 CURSOR c_item_term(
217 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
218 cp_item_code igf_aw_item.item_code%TYPE,
219 cp_ld_cal_type igs_ca_inst.cal_type%TYPE,
220 cp_ld_sequence_number igs_ca_inst.sequence_number%TYPE
221 ) IS
222 SELECT NVL(lock_flag,'N') lock_flag
223 FROM igf_aw_coa_itm_terms
224 WHERE base_id = cp_base_id
225 AND item_code = cp_item_code
226 AND ld_cal_type = cp_ld_cal_type
227 AND ld_sequence_number = cp_ld_sequence_number;
228 l_item_term c_item_term%ROWTYPE;
229
230 -- Get item level lock
231 CURSOR c_item(
232 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
233 cp_item_code igf_aw_item.item_code%TYPE
234 ) IS
235 SELECT NVL(lock_flag,'N') lock_flag
236 FROM igf_aw_coa_items
237 WHERE base_id = cp_base_id
238 AND item_code = cp_item_code;
239 l_item c_item%ROWTYPE;
240
241 -- Get base record level lock
242 CURSOR c_base(
243 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
244 ) IS
245 SELECT NVL(lock_coa_flag,'N') lock_coa_flag
246 FROM igf_ap_fa_base_rec_all
247 WHERE base_id = cp_base_id;
248 l_base c_base%ROWTYPE;
249
250 BEGIN
251
252 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
253 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.isCoaLocked.debug','p_base_id:'||p_base_id);
254 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.isCoaLocked.debug','p_item_code:'||p_item_code);
255 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.isCoaLocked.debug','p_ld_cal_type:'||p_ld_cal_type);
256 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.isCoaLocked.debug','p_ld_sequence_number:'||p_ld_sequence_number);
257 END IF;
258
259 IF p_base_id IS NOT NULL AND p_item_code IS NOT NULL AND p_ld_cal_type IS NOT NULL AND p_ld_sequence_number IS NOT NULL THEN
260
261 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
262 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.isCoaLocked.debug','Scenario 1');
263 END IF;
264
265 l_item_term := NULL;
266 OPEN c_item_term(p_base_id,p_item_code,p_ld_cal_type,p_ld_sequence_number);
267 FETCH c_item_term INTO l_item_term;
268 IF c_item_term%FOUND THEN
269
270 CLOSE c_item_term;
271 IF l_item_term.lock_flag = 'Y' THEN
272 RETURN TRUE;
273 ELSE
274 RETURN FALSE;
275 END IF;
276 ELSE
277 CLOSE c_item_term;
278 RETURN FALSE;
279 END IF;
280 ELSIF p_base_id IS NOT NULL AND p_item_code IS NOT NULL AND p_ld_cal_type IS NULL AND p_ld_sequence_number IS NULL THEN
281
282 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
283 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.isCoaLocked.debug','Scenario 2');
284 END IF;
285
286 l_item := NULL;
287 OPEN c_item(p_base_id,p_item_code);
288 FETCH c_item INTO l_item;
289 IF c_item%FOUND THEN
290
291 CLOSE c_item;
292 IF l_item.lock_flag = 'Y' THEN
293 RETURN TRUE;
294 ELSE
295 RETURN FALSE;
296 END IF;
297 ELSE
298 CLOSE c_item;
299 RETURN FALSE;
300 END IF;
301 ELSIF p_base_id IS NOT NULL AND p_item_code IS NULL AND p_ld_cal_type IS NULL AND p_ld_sequence_number IS NULL THEN
302
303 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
304 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.isCoaLocked.debug','Scenario 3');
305 END IF;
306
307 l_base := NULL;
308 OPEN c_base(p_base_id);
309 FETCH c_base INTO l_base;
310 IF c_base%FOUND THEN
311
312 CLOSE c_base;
313 IF l_base.lock_coa_flag = 'Y' THEN
314 RETURN TRUE;
315 ELSE
316 RETURN FALSE;
317 END IF;
318 ELSE
319 CLOSE c_base;
320 RETURN FALSE;
321 END IF;
322 ELSE
323 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
324 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.isCoaLocked.debug','Scenario 4');
325 END IF;
326 RETURN FALSE;
327 END IF;
328
329
330 EXCEPTION
331 WHEN OTHERS THEN
332 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
333 fnd_message.set_token('NAME','IGF_AW_COA_GEN.ISCOALOCKED ' || SQLERRM);
334 igs_ge_msg_stack.add;
335 app_exception.raise_exception;
336
337 END isCoaLocked;
338
339 PROCEDURE updateLock(
340 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
341 p_mode VARCHAR2,
342 p_item_code igf_aw_item.item_code%TYPE DEFAULT NULL,
343 p_ld_cal_type igs_ca_inst.cal_type%TYPE DEFAULT NULL,
344 p_ld_sequence_number igs_ca_inst.sequence_number%TYPE DEFAULT NULL
345 );
346
347 PROCEDURE doLockInternal(
348 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
349 p_item_code igf_aw_item.item_code%TYPE DEFAULT NULL,
350 p_ld_cal_type igs_ca_inst.cal_type%TYPE DEFAULT NULL,
351 p_ld_sequence_number igs_ca_inst.sequence_number%TYPE DEFAULT NULL
352 ) AS
353 ------------------------------------------------------------------
354 --Created by : veramach, Oracle India
355 --Date created: 07-OCT-2004
356 --
357 --Purpose:
358 --
359 --
360 --Known limitations/enhancements and/or remarks:
361 --
362 --Change History:
363 --Who When What
364 -------------------------------------------------------------------
365
366 BEGIN
367
368 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
369 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.doLockInternal.debug','p_base_id:'||p_base_id);
370 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.doLockInternal.debug','p_item_code:'||p_item_code);
371 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.doLockInternal.debug','p_ld_cal_type:'||p_ld_cal_type);
372 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.doLockInternal.debug','p_ld_sequence_number:'||p_ld_sequence_number);
373 END IF;
374
375 updateLock(p_base_id,'Y',p_item_code,p_ld_cal_type,p_ld_sequence_number);
376 END doLockInternal;
377
378 PROCEDURE doUnlockInternal(
379 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
380 p_item_code igf_aw_item.item_code%TYPE DEFAULT NULL,
381 p_ld_cal_type igs_ca_inst.cal_type%TYPE DEFAULT NULL,
382 p_ld_sequence_number igs_ca_inst.sequence_number%TYPE DEFAULT NULL
383 ) AS
384 ------------------------------------------------------------------
385 --Created by : veramach, Oracle India
386 --Date created: 07-OCT-2004
387 --
388 --Purpose:
389 --
390 --
391 --Known limitations/enhancements and/or remarks:
392 --
393 --Change History:
394 --Who When What
395 -------------------------------------------------------------------
396
397 BEGIN
398
399 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
400 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.doUnlockInternal.debug','p_base_id:'||p_base_id);
401 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.doUnlockInternal.debug','p_item_code:'||p_item_code);
402 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.doUnlockInternal.debug','p_ld_cal_type:'||p_ld_cal_type);
403 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.doUnlockInternal.debug','p_ld_sequence_number:'||p_ld_sequence_number);
404 END IF;
405
406 updateLock(p_base_id,'N',p_item_code,p_ld_cal_type,p_ld_sequence_number);
407 END doUnlockInternal;
408
409 PROCEDURE updateLock(
410 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
411 p_mode VARCHAR2,
412 p_item_code igf_aw_item.item_code%TYPE DEFAULT NULL,
413 p_ld_cal_type igs_ca_inst.cal_type%TYPE DEFAULT NULL,
414 p_ld_sequence_number igs_ca_inst.sequence_number%TYPE DEFAULT NULL
415 ) AS
416 ------------------------------------------------------------------
417 --Created by : veramach, Oracle India
418 --Date created: 07-OCT-2004
419 --
420 --Purpose:
421 --
422 --
423 --Known limitations/enhancements and/or remarks:
424 --
425 --Change History:
426 --Who When What
427 -------------------------------------------------------------------
428
429 CURSOR c_item_term(
430 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
431 cp_item_code igf_aw_item.item_code%TYPE,
432 cp_ld_cal_type igs_ca_inst.cal_type%TYPE,
433 cp_ld_sequence_number igs_ca_inst.sequence_number%TYPE
434 ) IS
435 SELECT terms.rowid row_id,
436 terms.*
437 FROM igf_aw_coa_itm_terms terms
438 WHERE base_id = cp_base_id
439 AND item_code = cp_item_code
440 AND ld_cal_type = cp_ld_cal_type
441 AND ld_sequence_number = cp_ld_sequence_number;
442 l_item_term c_item_term%ROWTYPE;
443
444 CURSOR c_item(
445 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
446 cp_item_code igf_aw_item.item_code%TYPE
447 ) IS
448 SELECT items.rowid row_id,
449 items.*
450 FROM igf_aw_coa_items items
451 WHERE base_id = cp_base_id
452 AND item_code = cp_item_code;
453 l_item c_item%ROWTYPE;
454
455 CURSOR c_base(
456 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
457 ) IS
458 SELECT fa.rowid row_id,
459 fa.*
460 FROM igf_ap_fa_base_rec_all fa
461 WHERE base_id = cp_base_id;
462 l_base c_base%ROWTYPE;
463
464 CURSOR c_terms(
465 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
466 cp_item_code igf_aw_item.item_code%TYPE
467 ) IS
468 SELECT terms.ld_cal_type,
469 terms.ld_sequence_number
470 FROM igf_aw_coa_itm_terms terms
471 WHERE base_id = cp_base_id
472 AND item_code = cp_item_code;
473
474
475 CURSOR c_items(
476 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
477 ) IS
478 SELECT items.item_code
479 FROM igf_aw_coa_items items
480 WHERE base_id = cp_base_id;
481
482
483 BEGIN
484
485 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
486 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','p_base_id:'||p_base_id);
487 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','p_mode:'||p_mode);
488 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','p_item_code:'||p_item_code);
489 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','p_ld_cal_type:'||p_ld_cal_type);
490 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','p_ld_sequence_number:'||p_ld_sequence_number);
491 END IF;
492
493 IF p_mode = 'Y' THEN
494 --starting lock mode
495 IF p_base_id IS NOT NULL AND p_item_code IS NOT NULL AND p_ld_cal_type IS NOT NULL AND p_ld_sequence_number IS NOT NULL THEN
496
497 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
498 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','Scenario 1');
499 END IF;
500
501 IF NOT isCoaLocked(p_base_id,p_item_code,p_ld_cal_type,p_ld_sequence_number) THEN
502 --lock only if its not already locked
503 l_item_term := NULL;
504 OPEN c_item_term(p_base_id,p_item_code,p_ld_cal_type,p_ld_sequence_number);
505 FETCH c_item_term INTO l_item_term;
506 CLOSE c_item_term;
507
508 fnd_message.set_name('IGF','IGF_AW_ITM_TRM_LOCK');
509 fnd_message.set_token('ITEM',p_item_code);
510 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(p_ld_cal_type,p_ld_sequence_number));
511 fnd_file.put_line(fnd_file.log,RPAD(' ',8)||fnd_message.get);
512
513
514 igf_aw_coa_itm_terms_pkg.update_row(
515 x_rowid => l_item_term.row_id,
516 x_base_id => l_item_term.base_id,
517 x_item_code => l_item_term.item_code,
518 x_amount => l_item_term.amount,
519 x_ld_cal_type => l_item_term.ld_cal_type,
520 x_ld_sequence_number => l_item_term.ld_sequence_number,
521 x_mode => 'R',
522 x_lock_flag => 'Y'
523 );
524 END IF;
525
526 RETURN;
527
528 ELSIF p_base_id IS NOT NULL AND p_item_code IS NOT NULL AND p_ld_cal_type IS NULL AND p_ld_sequence_number IS NULL THEN
529
530 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
531 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','Scenario 2');
532 END IF;
533
534 IF NOT isCoaLocked(p_base_id,p_item_code) THEN
535
536 l_item := NULL;
537 OPEN c_item(p_base_id,p_item_code);
538 FETCH c_item INTO l_item;
539 CLOSE c_item;
540
541 igf_aw_coa_items_pkg.update_row(
542 x_rowid => l_item.row_id,
543 x_base_id => l_item.base_id,
544 x_item_code => l_item.item_code,
545 x_amount => l_item.amount,
546 x_pell_coa_amount => l_item.pell_coa_amount,
547 x_alt_pell_amount => l_item.alt_pell_amount,
548 x_fixed_cost => l_item.fixed_cost,
549 x_legacy_record_flag => l_item.legacy_record_flag,
550 x_mode => 'R',
551 x_lock_flag => 'Y'
552 );
553 END IF;
554
555 FOR l_terms IN c_terms(p_base_id,p_item_code) LOOP
556 --Locks are cascaded to the terms attached the item
557 doLockInternal(p_base_id,p_item_code,l_terms.ld_cal_type,l_terms.ld_sequence_number);
558 END LOOP;
559 RETURN;
560
561 ELSIF p_base_id IS NOT NULL AND p_item_code IS NULL AND p_ld_cal_type IS NULL AND p_ld_sequence_number IS NULL THEN
562
563 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
564 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','Scenario 3');
565 END IF;
566
567 IF NOT isCoaLocked(p_base_id) THEN
568
569 l_base := NULL;
570 OPEN c_base(p_base_id);
571 FETCH c_base INTO l_base;
572 CLOSE c_base;
573
574 fnd_message.set_name('IGF','IGF_AW_BUDGET_LOCK');
575 fnd_file.put_line(fnd_file.log,RPAD(' ',4)||fnd_message.get);
576
577
578 igf_ap_fa_base_rec_pkg.update_row(
579 x_rowid => l_base.row_id,
580 x_base_id => l_base.base_id,
581 x_ci_cal_type => l_base.ci_cal_type,
582 x_person_id => l_base.person_id,
583 x_ci_sequence_number => l_base.ci_sequence_number,
584 x_org_id => l_base.org_id,
585 x_coa_pending => l_base.coa_pending,
586 x_verification_process_run => l_base.verification_process_run,
587 x_inst_verif_status_date => l_base.inst_verif_status_date,
588 x_manual_verif_flag => l_base.manual_verif_flag,
589 x_fed_verif_status => l_base.fed_verif_status,
590 x_fed_verif_status_date => l_base.fed_verif_status_date,
591 x_inst_verif_status => l_base.inst_verif_status,
592 x_nslds_eligible => l_base.nslds_eligible,
593 x_ede_correction_batch_id => l_base.ede_correction_batch_id,
594 x_fa_process_status_date => l_base.fa_process_status_date,
595 x_isir_corr_status => l_base.isir_corr_status,
596 x_isir_corr_status_date => l_base.isir_corr_status_date,
597 x_isir_status => l_base.isir_status,
598 x_isir_status_date => l_base.isir_status_date,
599 x_coa_code_f => l_base.coa_code_f,
600 x_coa_code_i => l_base.coa_code_i,
601 x_coa_f => l_base.coa_f,
602 x_coa_i => l_base.coa_i,
603 x_disbursement_hold => l_base.disbursement_hold,
604 x_fa_process_status => l_base.fa_process_status,
605 x_notification_status => l_base.notification_status,
606 x_notification_status_date => l_base.notification_status_date,
607 x_packaging_hold => l_base.packaging_hold,
608 x_packaging_status => l_base.packaging_status,
609 x_packaging_status_date => l_base.packaging_status_date,
610 x_total_package_accepted => l_base.total_package_accepted,
611 x_total_package_offered => l_base.total_package_offered,
612 x_admstruct_id => l_base.admstruct_id,
613 x_admsegment_1 => l_base.admsegment_1,
614 x_admsegment_2 => l_base.admsegment_2,
615 x_admsegment_3 => l_base.admsegment_3,
616 x_admsegment_4 => l_base.admsegment_4,
617 x_admsegment_5 => l_base.admsegment_5,
618 x_admsegment_6 => l_base.admsegment_6,
619 x_admsegment_7 => l_base.admsegment_7,
620 x_admsegment_8 => l_base.admsegment_8,
621 x_admsegment_9 => l_base.admsegment_9,
622 x_admsegment_10 => l_base.admsegment_10,
623 x_admsegment_11 => l_base.admsegment_11,
624 x_admsegment_12 => l_base.admsegment_12,
625 x_admsegment_13 => l_base.admsegment_13,
626 x_admsegment_14 => l_base.admsegment_14,
627 x_admsegment_15 => l_base.admsegment_15,
628 x_admsegment_16 => l_base.admsegment_16,
629 x_admsegment_17 => l_base.admsegment_17,
630 x_admsegment_18 => l_base.admsegment_18,
631 x_admsegment_19 => l_base.admsegment_19,
632 x_admsegment_20 => l_base.admsegment_20,
633 x_packstruct_id => l_base.packstruct_id,
634 x_packsegment_1 => l_base.packsegment_1,
635 x_packsegment_2 => l_base.packsegment_2,
636 x_packsegment_3 => l_base.packsegment_3,
637 x_packsegment_4 => l_base.packsegment_4,
638 x_packsegment_5 => l_base.packsegment_5,
639 x_packsegment_6 => l_base.packsegment_6,
640 x_packsegment_7 => l_base.packsegment_7,
641 x_packsegment_8 => l_base.packsegment_8,
642 x_packsegment_9 => l_base.packsegment_9,
643 x_packsegment_10 => l_base.packsegment_10,
644 x_packsegment_11 => l_base.packsegment_11,
645 x_packsegment_12 => l_base.packsegment_12,
646 x_packsegment_13 => l_base.packsegment_13,
647 x_packsegment_14 => l_base.packsegment_14,
648 x_packsegment_15 => l_base.packsegment_15,
649 x_packsegment_16 => l_base.packsegment_16,
650 x_packsegment_17 => l_base.packsegment_17,
651 x_packsegment_18 => l_base.packsegment_18,
652 x_packsegment_19 => l_base.packsegment_19,
653 x_packsegment_20 => l_base.packsegment_20,
654 x_miscstruct_id => l_base.miscstruct_id,
655 x_miscsegment_1 => l_base.miscsegment_1,
656 x_miscsegment_2 => l_base.miscsegment_2,
657 x_miscsegment_3 => l_base.miscsegment_3,
658 x_miscsegment_4 => l_base.miscsegment_4,
659 x_miscsegment_5 => l_base.miscsegment_5,
660 x_miscsegment_6 => l_base.miscsegment_6,
661 x_miscsegment_7 => l_base.miscsegment_7,
662 x_miscsegment_8 => l_base.miscsegment_8,
663 x_miscsegment_9 => l_base.miscsegment_9,
664 x_miscsegment_10 => l_base.miscsegment_10,
665 x_miscsegment_11 => l_base.miscsegment_11,
666 x_miscsegment_12 => l_base.miscsegment_12,
667 x_miscsegment_13 => l_base.miscsegment_13,
668 x_miscsegment_14 => l_base.miscsegment_14,
669 x_miscsegment_15 => l_base.miscsegment_15,
670 x_miscsegment_16 => l_base.miscsegment_16,
671 x_miscsegment_17 => l_base.miscsegment_17,
672 x_miscsegment_18 => l_base.miscsegment_18,
673 x_miscsegment_19 => l_base.miscsegment_19,
674 x_miscsegment_20 => l_base.miscsegment_20,
675 x_prof_judgement_flg => l_base.prof_judgement_flg,
676 x_nslds_data_override_flg => l_base.nslds_data_override_flg,
677 x_target_group => l_base.target_group,
678 x_coa_fixed => l_base.coa_fixed,
679 x_coa_pell => l_base.coa_pell,
680 x_mode => 'R',
681 x_profile_status => l_base.profile_status,
682 x_profile_status_date => l_base.profile_status_date,
683 x_profile_fc => l_base.profile_fc,
684 x_tolerance_amount => l_base.tolerance_amount,
685 x_manual_disb_hold => l_base.manual_disb_hold,
686 x_pell_alt_expense => l_base.pell_alt_expense,
687 x_assoc_org_num => l_base.assoc_org_num,
688 x_award_fmly_contribution_type => l_base.award_fmly_contribution_type,
689 x_isir_locked_by => l_base.isir_locked_by,
690 x_adnl_unsub_loan_elig_flag => l_base.adnl_unsub_loan_elig_flag,
691 x_lock_coa_flag => 'Y',
692 x_lock_awd_flag => l_base.lock_awd_flag
693 );
694
695 END IF;
696
697 FOR l_items IN c_items(p_base_id) LOOP
698 --Cascade lock to items.
699 doLockInternal(p_base_id,l_items.item_code);
700 END LOOP;
701 RETURN;
702
703 ELSIF p_base_id IS NOT NULL AND p_item_code IS NULL AND p_ld_cal_type IS NOT NULL AND p_ld_sequence_number IS NOT NULL THEN
704 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
705 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','Scenario 4');
706 END IF;
707
708 FOR l_items IN c_items(p_base_id) LOOP
709 doLockInternal(p_base_id,l_items.item_code,p_ld_cal_type,p_ld_sequence_number);
710 END LOOP;
711 RETURN;
712 END IF;
713
714 ELSIF p_mode = 'N' THEN
715 --starting unlock mode
716 IF p_base_id IS NOT NULL AND p_item_code IS NOT NULL AND p_ld_cal_type IS NOT NULL AND p_ld_sequence_number IS NOT NULL THEN
717
718 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
719 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','Scenario 5');
720 END IF;
721
722 IF isCoaLocked(p_base_id,p_item_code,p_ld_cal_type,p_ld_sequence_number) THEN
723 --unlock only if its already locked
724 l_item_term := NULL;
725 OPEN c_item_term(p_base_id,p_item_code,p_ld_cal_type,p_ld_sequence_number);
726 FETCH c_item_term INTO l_item_term;
727 CLOSE c_item_term;
728
729 fnd_message.set_name('IGF','IGF_AW_ITM_TRM_UNLOCK');
730 fnd_message.set_token('ITEM',p_item_code);
731 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(p_ld_cal_type,p_ld_sequence_number));
732 fnd_file.put_line(fnd_file.log,RPAD(' ',4)||fnd_message.get);
733
734
735 igf_aw_coa_itm_terms_pkg.update_row(
736 x_rowid => l_item_term.row_id,
737 x_base_id => l_item_term.base_id,
738 x_item_code => l_item_term.item_code,
739 x_amount => l_item_term.amount,
740 x_ld_cal_type => l_item_term.ld_cal_type,
741 x_ld_sequence_number => l_item_term.ld_sequence_number,
742 x_mode => 'R',
743 x_lock_flag => 'N'
744 );
745 END IF;
746
747 IF g_unlock_level = 3 THEN
748 --Remove item level lock
749 doUnlockInternal(p_base_id,p_item_code);
750 --Remove student level lock
751 doUnlockInternal(p_base_id);
752 END IF;
753 RETURN;
754
755 ELSIF p_base_id IS NOT NULL AND p_item_code IS NOT NULL AND p_ld_cal_type IS NULL AND p_ld_sequence_number IS NULL THEN
756
757 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
758 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','Scenario 6');
759 END IF;
760
761 IF isCoaLocked(p_base_id,p_item_code) THEN
762
763 l_item := NULL;
764 OPEN c_item(p_base_id,p_item_code);
765 FETCH c_item INTO l_item;
766 CLOSE c_item;
767
768 igf_aw_coa_items_pkg.update_row(
769 x_rowid => l_item.row_id,
770 x_base_id => l_item.base_id,
771 x_item_code => l_item.item_code,
772 x_amount => l_item.amount,
773 x_pell_coa_amount => l_item.pell_coa_amount,
774 x_alt_pell_amount => l_item.alt_pell_amount,
775 x_fixed_cost => l_item.fixed_cost,
776 x_legacy_record_flag => l_item.legacy_record_flag,
777 x_mode => 'R',
778 x_lock_flag => 'N'
779 );
780 END IF;
781
782 IF g_unlock_level <= 2 THEN
783 FOR l_terms IN c_terms(p_base_id,p_item_code) LOOP
784 --Unlocks are cascaded to the terms attached the item
785 doUnlockInternal(p_base_id,p_item_code,l_terms.ld_cal_type,l_terms.ld_sequence_number);
786 END LOOP;
787 END IF;
788 IF g_unlock_level = 2 THEN
789 --Remove student level lock
790 doUnlockInternal(p_base_id);
791 END IF;
792 RETURN;
793
794 ELSIF p_base_id IS NOT NULL AND p_item_code IS NULL AND p_ld_cal_type IS NULL AND p_ld_sequence_number IS NULL THEN
795
796 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
797 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','Scenario 7');
798 END IF;
799
800 IF isCoaLocked(p_base_id) THEN
801
802 l_base := NULL;
803 OPEN c_base(p_base_id);
804 FETCH c_base INTO l_base;
805 CLOSE c_base;
806
807 fnd_message.set_name('IGF','IGF_AW_BUDGET_UNLOCK');
808 fnd_file.put_line(fnd_file.log,RPAD(' ',8)||fnd_message.get);
809
810
811 igf_ap_fa_base_rec_pkg.update_row(
812 x_rowid => l_base.row_id,
813 x_base_id => l_base.base_id,
814 x_ci_cal_type => l_base.ci_cal_type,
815 x_person_id => l_base.person_id,
816 x_ci_sequence_number => l_base.ci_sequence_number,
817 x_org_id => l_base.org_id,
818 x_coa_pending => l_base.coa_pending,
819 x_verification_process_run => l_base.verification_process_run,
820 x_inst_verif_status_date => l_base.inst_verif_status_date,
821 x_manual_verif_flag => l_base.manual_verif_flag,
822 x_fed_verif_status => l_base.fed_verif_status,
823 x_fed_verif_status_date => l_base.fed_verif_status_date,
824 x_inst_verif_status => l_base.inst_verif_status,
825 x_nslds_eligible => l_base.nslds_eligible,
826 x_ede_correction_batch_id => l_base.ede_correction_batch_id,
827 x_fa_process_status_date => l_base.fa_process_status_date,
828 x_isir_corr_status => l_base.isir_corr_status,
829 x_isir_corr_status_date => l_base.isir_corr_status_date,
830 x_isir_status => l_base.isir_status,
831 x_isir_status_date => l_base.isir_status_date,
832 x_coa_code_f => l_base.coa_code_f,
833 x_coa_code_i => l_base.coa_code_i,
834 x_coa_f => l_base.coa_f,
835 x_coa_i => l_base.coa_i,
836 x_disbursement_hold => l_base.disbursement_hold,
837 x_fa_process_status => l_base.fa_process_status,
838 x_notification_status => l_base.notification_status,
839 x_notification_status_date => l_base.notification_status_date,
840 x_packaging_hold => l_base.packaging_hold,
841 x_packaging_status => l_base.packaging_status,
842 x_packaging_status_date => l_base.packaging_status_date,
843 x_total_package_accepted => l_base.total_package_accepted,
844 x_total_package_offered => l_base.total_package_offered,
845 x_admstruct_id => l_base.admstruct_id,
846 x_admsegment_1 => l_base.admsegment_1,
847 x_admsegment_2 => l_base.admsegment_2,
848 x_admsegment_3 => l_base.admsegment_3,
849 x_admsegment_4 => l_base.admsegment_4,
850 x_admsegment_5 => l_base.admsegment_5,
851 x_admsegment_6 => l_base.admsegment_6,
852 x_admsegment_7 => l_base.admsegment_7,
853 x_admsegment_8 => l_base.admsegment_8,
854 x_admsegment_9 => l_base.admsegment_9,
855 x_admsegment_10 => l_base.admsegment_10,
856 x_admsegment_11 => l_base.admsegment_11,
857 x_admsegment_12 => l_base.admsegment_12,
858 x_admsegment_13 => l_base.admsegment_13,
859 x_admsegment_14 => l_base.admsegment_14,
860 x_admsegment_15 => l_base.admsegment_15,
861 x_admsegment_16 => l_base.admsegment_16,
862 x_admsegment_17 => l_base.admsegment_17,
863 x_admsegment_18 => l_base.admsegment_18,
864 x_admsegment_19 => l_base.admsegment_19,
865 x_admsegment_20 => l_base.admsegment_20,
866 x_packstruct_id => l_base.packstruct_id,
867 x_packsegment_1 => l_base.packsegment_1,
868 x_packsegment_2 => l_base.packsegment_2,
869 x_packsegment_3 => l_base.packsegment_3,
870 x_packsegment_4 => l_base.packsegment_4,
871 x_packsegment_5 => l_base.packsegment_5,
872 x_packsegment_6 => l_base.packsegment_6,
873 x_packsegment_7 => l_base.packsegment_7,
874 x_packsegment_8 => l_base.packsegment_8,
875 x_packsegment_9 => l_base.packsegment_9,
876 x_packsegment_10 => l_base.packsegment_10,
877 x_packsegment_11 => l_base.packsegment_11,
878 x_packsegment_12 => l_base.packsegment_12,
879 x_packsegment_13 => l_base.packsegment_13,
880 x_packsegment_14 => l_base.packsegment_14,
881 x_packsegment_15 => l_base.packsegment_15,
882 x_packsegment_16 => l_base.packsegment_16,
883 x_packsegment_17 => l_base.packsegment_17,
884 x_packsegment_18 => l_base.packsegment_18,
885 x_packsegment_19 => l_base.packsegment_19,
886 x_packsegment_20 => l_base.packsegment_20,
887 x_miscstruct_id => l_base.miscstruct_id,
888 x_miscsegment_1 => l_base.miscsegment_1,
889 x_miscsegment_2 => l_base.miscsegment_2,
890 x_miscsegment_3 => l_base.miscsegment_3,
891 x_miscsegment_4 => l_base.miscsegment_4,
892 x_miscsegment_5 => l_base.miscsegment_5,
893 x_miscsegment_6 => l_base.miscsegment_6,
894 x_miscsegment_7 => l_base.miscsegment_7,
895 x_miscsegment_8 => l_base.miscsegment_8,
896 x_miscsegment_9 => l_base.miscsegment_9,
897 x_miscsegment_10 => l_base.miscsegment_10,
898 x_miscsegment_11 => l_base.miscsegment_11,
899 x_miscsegment_12 => l_base.miscsegment_12,
900 x_miscsegment_13 => l_base.miscsegment_13,
901 x_miscsegment_14 => l_base.miscsegment_14,
902 x_miscsegment_15 => l_base.miscsegment_15,
903 x_miscsegment_16 => l_base.miscsegment_16,
904 x_miscsegment_17 => l_base.miscsegment_17,
905 x_miscsegment_18 => l_base.miscsegment_18,
906 x_miscsegment_19 => l_base.miscsegment_19,
907 x_miscsegment_20 => l_base.miscsegment_20,
908 x_prof_judgement_flg => l_base.prof_judgement_flg,
909 x_nslds_data_override_flg => l_base.nslds_data_override_flg,
910 x_target_group => l_base.target_group,
911 x_coa_fixed => l_base.coa_fixed,
912 x_coa_pell => l_base.coa_pell,
913 x_mode => 'R',
914 x_profile_status => l_base.profile_status,
915 x_profile_status_date => l_base.profile_status_date,
916 x_profile_fc => l_base.profile_fc,
917 x_tolerance_amount => l_base.tolerance_amount,
918 x_manual_disb_hold => l_base.manual_disb_hold,
919 x_pell_alt_expense => l_base.pell_alt_expense,
920 x_assoc_org_num => l_base.assoc_org_num,
921 x_award_fmly_contribution_type => l_base.award_fmly_contribution_type,
922 x_isir_locked_by => l_base.isir_locked_by,
923 x_adnl_unsub_loan_elig_flag => l_base.adnl_unsub_loan_elig_flag,
924 x_lock_coa_flag => 'N',
925 x_lock_awd_flag => l_base.lock_awd_flag
926 );
927 END IF;
928 IF g_unlock_level = 1 THEN
929 FOR l_items IN c_items(p_base_id) LOOP
930 --Cascade unlock to items.
931 doUnlockInternal(p_base_id,l_items.item_code);
932 END LOOP;
933 END IF;
934
935 ELSIF p_base_id IS NOT NULL AND p_item_code IS NULL AND p_ld_cal_type IS NOT NULL AND p_ld_sequence_number IS NOT NULL THEN
936 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
937 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.updateLock.debug','Scenario 8');
938 END IF;
939
940 FOR l_items IN c_items(p_base_id) LOOP
941 doUnlockInternal(p_base_id,l_items.item_code,p_ld_cal_type,p_ld_sequence_number);
942 END LOOP;
943 RETURN;
944 END IF;
945
946 END IF;
947
948 END updateLock;
949
950 FUNCTION doLock(
951 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
952 p_item_code igf_aw_item.item_code%TYPE DEFAULT NULL,
953 p_ld_cal_type igs_ca_inst.cal_type%TYPE DEFAULT NULL,
954 p_ld_sequence_number igs_ca_inst.sequence_number%TYPE DEFAULT NULL
955 ) RETURN VARCHAR2 AS
956 ------------------------------------------------------------------
957 --Created by : veramach, Oracle India
958 --Date created: 07-OCT-2004
959 --
960 --Purpose:
961 --
962 --
963 --Known limitations/enhancements and/or remarks:
964 --
965 --Change History:
966 --Who When What
967 -------------------------------------------------------------------
968
969 BEGIN
970
971 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
972 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.doLock.debug','p_base_id:'||p_base_id);
973 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.doLock.debug','p_item_code:'||p_item_code);
974 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.doLock.debug','p_ld_cal_type:'||p_ld_cal_type);
975 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.doLock.debug','p_ld_sequence_number:'||p_ld_sequence_number);
976 END IF;
977
978 SAVEPOINT IGF_AW_COA_GEN_DOLOCK;
979 doLockInternal(p_base_id,p_item_code,p_ld_cal_type,p_ld_sequence_number);
980 RETURN 'Y';
981
982 EXCEPTION
983 WHEN OTHERS THEN
984 ROLLBACK TO IGF_AW_COA_GEN_DOLOCK;
985 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
986 fnd_message.set_token('NAME','IGF_AW_COA_GEN.DOLOCK ' || SQLERRM);
987 igs_ge_msg_stack.add;
988 app_exception.raise_exception;
989
990 END doLock;
991
992 FUNCTION doUnlock(
993 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
994 p_item_code igf_aw_item.item_code%TYPE DEFAULT NULL,
995 p_ld_cal_type igs_ca_inst.cal_type%TYPE DEFAULT NULL,
996 p_ld_sequence_number igs_ca_inst.sequence_number%TYPE DEFAULT NULL
997 ) RETURN VARCHAR2 AS
998 ------------------------------------------------------------------
999 --Created by : veramach, Oracle India
1000 --Date created: 07-OCT-2004
1001 --
1002 --Purpose:
1003 --
1004 --
1005 --Known limitations/enhancements and/or remarks:
1006 --
1007 --Change History:
1008 --Who When What
1009 -------------------------------------------------------------------
1010
1011 BEGIN
1012
1013 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1014 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.doUnlock.debug','p_base_id:'||p_base_id);
1015 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.doUnlock.debug','p_item_code:'||p_item_code);
1016 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.doUnlock.debug','p_ld_cal_type:'||p_ld_cal_type);
1017 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.doUnlock.debug','p_ld_sequence_number:'||p_ld_sequence_number);
1018 END IF;
1019
1020 SAVEPOINT IGF_AW_COA_GEN_DOUNLOCK;
1021 IF p_ld_cal_type IS NOT NULL AND p_ld_sequence_number IS NOT NULL THEN
1022 g_unlock_level := 3;
1023 ELSIF p_item_code IS NOT NULL THEN
1024 g_unlock_level := 2;
1025 ELSE
1026 g_unlock_level := 1;
1027 END IF;
1028 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1029 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.doUnlock.debug','g_unlock_level:'||g_unlock_level);
1030 END IF;
1031 doUnlockInternal(p_base_id,p_item_code,p_ld_cal_type,p_ld_sequence_number);
1032 RETURN 'Y';
1033
1034 EXCEPTION
1035 WHEN OTHERS THEN
1036 ROLLBACK TO IGF_AW_COA_GEN_DOUNLOCK;
1037 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1038 fnd_message.set_token('NAME','IGF_AW_COA_GEN.DOUNLOCK ' || SQLERRM);
1039 igs_ge_msg_stack.add;
1040 app_exception.raise_exception;
1041
1042 END doUnlock;
1043
1044 PROCEDURE get_coa_months(
1045 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
1046 p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE DEFAULT NULL,
1047 p_start_dt OUT NOCOPY DATE,
1048 p_end_dt OUT NOCOPY DATE,
1049 p_coa_months OUT NOCOPY NUMBER
1050 ) IS
1051 ------------------------------------------------------------------
1052 --Created by : veramach, Oracle India
1053 --Date created: 6-OCT-2003
1054 --
1055 --Purpose:
1056 -- To check if anticipated values can be used if actual values are not used
1057 --
1058 --Known limitations/enhancements and/or remarks:
1059 --
1060 --Change History:
1061 --Who When What
1062 -------------------------------------------------------------------
1063 CURSOR c_terms(
1064 cp_base_id igf_ap_fa_base_rec.base_id%TYPE,
1065 cp_awd_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
1066 ) IS
1067 SELECT coa.ld_cal_type,
1068 coa.ld_sequence_number
1069 FROM igf_aw_awd_prd_term ap,
1070 igf_ap_fa_base_rec_all fa,
1071 igf_aw_coa_itm_terms coa
1072 WHERE fa.base_id = cp_base_id
1073 AND fa.ci_cal_type = ap.ci_cal_type
1074 AND fa.ci_sequence_number = ap.ci_sequence_number
1075 AND coa.base_id = cp_base_id
1076 AND coa.ld_cal_type = ap.ld_cal_type
1077 AND coa.ld_sequence_number = ap.ld_sequence_number
1078 AND ap.award_prd_cd = cp_awd_prd_code
1079 GROUP BY coa.ld_cal_type,coa.ld_sequence_number;
1080
1081 CURSOR c_terms_awd(
1082 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1083 ) IS
1084 SELECT coa.ld_cal_type,
1085 coa.ld_sequence_number
1086 FROM igf_aw_coa_itm_terms coa
1087 WHERE coa.base_id = cp_base_id
1088 GROUP BY coa.ld_cal_type,coa.ld_sequence_number;
1089
1090 CURSOR get_round_off(
1091 cp_base_id igf_ap_fa_base_rec.base_id%TYPE
1092 ) IS
1093 SELECT num_days_divisor, roundoff_fact
1094 FROM igf_ap_efc_v efc,
1095 igf_ap_fa_base_rec_all fabase
1096 WHERE efc.ci_cal_type = fabase.ci_cal_type
1097 AND efc.ci_sequence_number = fabase.ci_sequence_number
1098 AND fabase.base_id = cp_base_id;
1099 lv_round_off_rec get_round_off%ROWTYPE;
1100
1101 l_start_dt DATE;
1102 l_end_dt DATE;
1103 l_first_cycle VARCHAR2(1);
1104 l_no_of_months NUMBER;
1105
1106 BEGIN
1107 l_first_cycle := 'Y' ;
1108 IF p_awd_prd_code IS NOT NULL THEN
1109 FOR l_terms IN c_terms(p_base_id,p_awd_prd_code) LOOP
1110 igf_ap_gen_001.get_term_dates(
1111 p_base_id => p_base_id,
1112 p_ld_cal_type => l_terms.ld_cal_type,
1113 p_ld_sequence_number => l_terms.ld_sequence_number,
1114 p_ld_start_date => l_start_dt,
1115 p_ld_end_date => l_end_dt
1116 );
1117 IF l_first_cycle = 'Y' THEN
1118 p_start_dt := l_start_dt;
1119 p_end_dt := l_end_dt;
1120 l_first_cycle := 'N';
1121 ELSE
1122 p_start_dt := LEAST(p_start_dt,l_start_dt);
1123 p_end_dt := GREATEST(p_end_dt,l_end_dt);
1124 END IF;
1125 END LOOP;
1126 ELSE
1127 FOR l_terms IN c_terms_awd(p_base_id) LOOP
1128 igf_ap_gen_001.get_term_dates(
1129 p_base_id => p_base_id,
1130 p_ld_cal_type => l_terms.ld_cal_type,
1131 p_ld_sequence_number => l_terms.ld_sequence_number,
1132 p_ld_start_date => l_start_dt,
1133 p_ld_end_date => l_end_dt
1134 );
1135 IF l_first_cycle = 'Y' THEN
1136 p_start_dt := l_start_dt;
1137 p_end_dt := l_end_dt;
1138 l_first_cycle := 'N';
1139 ELSE
1140 p_start_dt := LEAST(p_start_dt,l_start_dt);
1141 p_end_dt := GREATEST(p_end_dt,l_end_dt);
1142 END IF;
1143 END LOOP;
1144 END IF;
1145 OPEN get_round_off(p_base_id);
1146 FETCH get_round_off INTO lv_round_off_rec;
1147 CLOSE get_round_off;
1148
1149 l_no_of_months := (p_end_dt - p_start_dt) / NVL(lv_round_off_rec.num_days_divisor,30);
1150
1151 IF (lv_round_off_rec.roundoff_fact = 'RU') THEN
1152 -- Round up to the nearest whole number
1153 l_no_of_months := CEIL( l_no_of_months );
1154 ELSIF (lv_round_off_rec.roundoff_fact = 'RD' ) THEN
1155 -- Round down to the nearest whole number
1156 l_no_of_months := FLOOR( l_no_of_months );
1157 ELSE
1158 -- Round off factor is 'RH', Round to the nearest whole number
1159 l_no_of_months := ROUND( l_no_of_months );
1160 END IF;
1161 p_coa_months := l_no_of_months;
1162 END get_coa_months;
1163
1164 FUNCTION coa_duration(
1165 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
1166 p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE
1167 ) RETURN NUMBER AS
1168 ------------------------------------------------------------------
1169 --Created by : veramach, Oracle India
1170 --Date created:
1171 --
1172 --Purpose:
1173 --
1174 --
1175 --Known limitations/enhancements and/or remarks:
1176 --
1177 --Change History:
1178 --Who When What
1179 -------------------------------------------------------------------
1180 l_start_dt DATE;
1181 l_end_dt DATE;
1182 l_coa_duration NUMBER;
1183 BEGIN
1184 get_coa_months(
1185 p_base_id => p_base_id,
1186 p_awd_prd_code => p_awd_prd_code,
1187 p_start_dt => l_start_dt,
1188 p_end_dt => l_end_dt,
1189 p_coa_months => l_coa_duration
1190 );
1191 RETURN l_coa_duration;
1192 END coa_duration;
1193
1194 FUNCTION canUseAnticipVal RETURN BOOLEAN AS
1195 ------------------------------------------------------------------
1196 --Created by : veramach, Oracle India
1197 --Date created: 6-OCT-2003
1198 --
1199 --Purpose:
1200 -- To check if anticipated values can be used if actual values are not used
1201 --
1202 --Known limitations/enhancements and/or remarks:
1203 --
1204 --Change History:
1205 --Who When What
1206 -------------------------------------------------------------------
1207 lv_profile_value VARCHAR2(10);
1208 BEGIN
1209 fnd_profile.get('IGF_AW_USE_ANT_DATA',lv_profile_value);
1210 IF lv_profile_value ='Y' THEN
1211 RETURN TRUE;
1212 ELSE
1213 RETURN FALSE;
1214 END IF;
1215 END canUseAnticipVal;
1216
1217 FUNCTION getBaseDetails(
1218 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1219 p_ld_cal_type igs_ca_inst.cal_type%TYPE,
1220 p_ld_sequence_number igs_ca_inst.sequence_number%TYPE
1221 ) RETURN base_details AS
1222 ------------------------------------------------------------------
1223 --Created by : veramach, Oracle India
1224 --Date created: 11-Oct-2004
1225 --
1226 --Purpose:
1227 --
1228 --
1229 --Known limitations/enhancements and/or remarks:
1230 --
1231 --Change History:
1232 --Who When What
1233 --museshad 16-Sep-2005 Bug# 4604393
1234 -- Changed the logic for deriving Actual/Predictive
1235 -- Class Standing
1236 -------------------------------------------------------------------
1237
1238 l_term_start DATE;
1239 l_term_end DATE;
1240
1241 lv_usage VARCHAR2(1); -- 1 -> Actual, 2 -> Actual,if not available then Anticipated, 3 -> Anticipated.
1242
1243 l_person_id hz_parties.party_id%TYPE;
1244
1245 l_base_det base_details;
1246
1247 -- Get person_id
1248 CURSOR c_person_id(
1249 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1250 ) IS
1251 SELECT person_id
1252 FROM igf_ap_fa_base_rec_all
1253 WHERE base_id = cp_base_id;
1254
1255 -- Get anticipated values
1256 CURSOR c_anticip(
1257 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1258 cp_ld_cal_type igs_ca_inst.cal_type%TYPE,
1259 cp_ld_sequence_number igs_ca_inst.sequence_number%TYPE
1260 ) IS
1261 SELECT org_unit_cd,
1262 program_type,
1263 program_location_cd,
1264 program_cd,
1265 class_standing,
1266 residency_status_code,
1267 housing_status_code,
1268 attendance_type,
1269 attendance_mode,
1270 months_enrolled_num,
1271 credit_points_num
1272 FROM igf_ap_fa_ant_data
1273 WHERE base_id = cp_base_id
1274 AND ld_cal_type = cp_ld_cal_type
1275 AND ld_sequence_number = cp_ld_sequence_number;
1276
1277 l_anticip c_anticip%ROWTYPE;
1278
1279 -- Get org unit
1280 CURSOR c_org(
1281 cp_person_id hz_parties.party_id%TYPE,
1282 cp_ld_cal_type igs_ca_inst.cal_type%TYPE,
1283 cp_ld_sequence_number igs_ca_inst.sequence_number%TYPE
1284 ) IS
1285 SELECT ps.responsible_org_unit_cd,1 record_order
1286 FROM igs_en_spa_terms key,
1287 igs_ps_ver ps
1288 WHERE key.key_program_flag = 'Y'
1289 AND person_id = cp_person_id
1290 AND ps.course_cd = key.program_cd
1291 AND ps.version_number = key.program_version
1292 AND key.term_cal_type = cp_ld_cal_type
1293 AND key.term_sequence_number = cp_ld_sequence_number
1294 UNION ALL
1295 SELECT ps.responsible_org_unit_cd,
1296 2 record_order
1297 FROM igs_en_stdnt_ps_att att,
1298 igs_ps_ver ps
1299 WHERE att.person_id = cp_person_id
1300 AND att.key_program = 'Y'
1301 AND att.course_cd = ps.course_cd
1302 AND att.version_number = ps.version_number
1303 ORDER BY record_order;
1304
1305 -- Get program type
1306 CURSOR c_prg_type(
1307 cp_person_id hz_parties.party_id%TYPE,
1308 cp_ld_cal_type igs_ca_inst.cal_type%TYPE,
1309 cp_ld_sequence_number igs_ca_inst.sequence_number%TYPE
1310 ) IS
1311 SELECT pt.course_type,1 record_order
1312 FROM igs_en_spa_terms sp,
1313 igs_ps_ver pv,
1314 igs_ps_type_v pt
1315 WHERE sp.key_program_flag = 'Y'
1316 AND sp.program_cd = pv.course_cd
1317 AND sp.program_version = pv.version_number
1318 AND pv.course_type = pt.course_type
1319 AND sp.person_id = cp_person_id
1320 AND sp.term_cal_type = cp_ld_cal_type
1321 AND sp.term_sequence_number = cp_ld_sequence_number
1322 UNION ALL
1323 SELECT pt.course_type,2 record_order
1324 FROM igs_en_stdnt_ps_att sp,
1325 igs_ps_ver pv,
1326 igs_ps_type_v pt
1327 WHERE sp.key_program = 'Y'
1328 AND sp.course_cd = pv.course_cd
1329 AND sp.version_number = pv.version_number
1330 AND pv.course_type = pt.course_type
1331 AND sp.person_id = cp_person_id
1332 ORDER BY record_order;
1333
1334 -- Get program location
1335 CURSOR c_prog_loc(
1336 cp_person_id hz_parties.party_id%TYPE,
1337 cp_ld_cal_type igs_ca_inst.cal_type%TYPE,
1338 cp_ld_sequence_number igs_ca_inst.sequence_number%TYPE
1339 ) IS
1340 SELECT location_cd,
1341 1 record_order
1342 FROM igs_en_spa_terms
1343 WHERE key_program_flag = 'Y'
1344 AND person_id = cp_person_id
1345 AND term_cal_type = cp_ld_cal_type
1346 AND term_sequence_number = cp_ld_sequence_number
1347 UNION ALL
1348 SELECT location_cd,
1349 2 record_order
1350 FROM igs_en_stdnt_ps_att
1351 WHERE key_program = 'Y'
1352 AND person_id = cp_person_id
1353 ORDER BY record_order;
1354
1355 -- Get get program code
1356 CURSOR c_prog_code(
1357 cp_person_id hz_parties.party_id%TYPE,
1358 cp_ld_cal_type igs_ca_inst.cal_type%TYPE,
1359 cp_ld_sequence_number igs_ca_inst.sequence_number%TYPE
1360 ) IS
1361 SELECT program_cd,
1362 program_version,
1363 1 record_order
1364 FROM igs_en_spa_terms
1365 WHERE key_program_flag = 'Y'
1366 AND person_id = cp_person_id
1367 AND term_cal_type = cp_ld_cal_type
1368 AND term_sequence_number = cp_ld_sequence_number
1369 UNION ALL
1370 SELECT course_cd program_cd,
1371 version_number program_version,
1372 2 record_order
1373 FROM igs_en_stdnt_ps_att
1374 WHERE key_program = 'Y'
1375 AND person_id = cp_person_id
1376 ORDER BY record_order;
1377
1378
1379 -- Get attendance type
1380 CURSOR c_att_type(
1381 cp_person_id hz_parties.party_id%TYPE,
1382 cp_ld_cal_type igs_ca_inst.cal_type%TYPE,
1383 cp_ld_sequence_number igs_ca_inst.sequence_number%TYPE
1384 ) IS
1385 SELECT attendance_type,
1386 1 record_order
1387 FROM igs_en_spa_terms
1388 WHERE key_program_flag = 'Y'
1389 AND person_id = cp_person_id
1390 AND term_cal_type = cp_ld_cal_type
1391 AND term_sequence_number = cp_ld_sequence_number
1392 UNION ALL
1393 SELECT attendance_type,
1394 2 record_order
1395 FROM igs_en_stdnt_ps_att
1396 WHERE key_program = 'Y'
1397 AND person_id = cp_person_id
1398 ORDER BY record_order;
1399
1400 -- Get attendance mode
1401 CURSOR c_att_mode(
1402 cp_person_id hz_parties.party_id%TYPE,
1403 cp_ld_cal_type igs_ca_inst.cal_type%TYPE,
1404 cp_ld_sequence_number igs_ca_inst.sequence_number%TYPE
1405 ) IS
1406 SELECT attendance_mode,
1407 1 record_order
1408 FROM igs_en_spa_terms
1409 WHERE key_program_flag = 'Y'
1410 AND person_id = cp_person_id
1411 AND term_cal_type = cp_ld_cal_type
1412 AND term_sequence_number = cp_ld_sequence_number
1413 UNION ALL
1414 SELECT attendance_mode,
1415 2 record_order
1416 FROM igs_en_stdnt_ps_att
1417 WHERE key_program = 'Y'
1418 AND person_id = cp_person_id
1419 ORDER BY record_order;
1420
1421
1422 l_attendance igs_en_atd_type_load.attendance_type%TYPE;
1423 l_credit_points igs_en_su_attempt.override_achievable_cp%TYPE;
1424 l_fte igs_en_su_attempt.override_achievable_cp%TYPE;
1425
1426 -- Get residency status
1427 l_residency_class igs_pe_res_dtls.residency_class_cd%TYPE;
1428
1429 -- Get housing status
1430 CURSOR c_housing_status(
1431 cp_person_id hz_parties.party_id%TYPE,
1432 cp_ld_cal_type igs_ca_inst.cal_type%TYPE,
1433 cp_ld_sequence_number igs_ca_inst.sequence_number%TYPE
1434 ) IS
1435 SELECT teach_period_resid_stat_cd
1436 FROM igs_pe_teach_periods_all
1437 WHERE person_id = cp_person_id
1438 AND cal_type = cp_ld_cal_type
1439 AND sequence_number = cp_ld_sequence_number;
1440
1441 l_term_duration NUMBER;
1442
1443 CURSOR get_round_off(
1444 cp_base_id igf_ap_fa_base_rec.base_id%TYPE
1445 ) IS
1446 SELECT num_days_divisor, roundoff_fact
1447 FROM igf_ap_efc_v efc,
1448 igf_ap_fa_base_rec_all fabase
1449 WHERE efc.ci_cal_type = fabase.ci_cal_type
1450 AND efc.ci_sequence_number = fabase.ci_sequence_number
1451 AND fabase.base_id = cp_base_id;
1452 lv_round_off_rec get_round_off%ROWTYPE;
1453 l_dummy_number NUMBER;
1454
1455 -- museshad (Bug# 4604393)
1456 l_term_start_date DATE := NULL;
1457 l_pred_flag VARCHAR2(1);
1458 -- museshad (Bug# 4604393)
1459 BEGIN
1460
1461 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1462 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','p_base_id:'||p_base_id);
1463 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','p_ld_cal_type:'||p_ld_cal_type);
1464 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','p_ld_sequence_number:'||p_ld_sequence_number);
1465 END IF;
1466
1467 l_term_start := NULL;
1468 l_term_end := NULL;
1469 lv_usage := NULL;
1470 l_term_duration := NULL;
1471
1472 igf_ap_gen_001.get_term_dates(
1473 p_base_id => p_base_id,
1474 p_ld_cal_type => p_ld_cal_type,
1475 p_ld_sequence_number => p_ld_sequence_number,
1476 p_ld_start_date => l_term_start,
1477 p_ld_end_date => l_term_end
1478 );
1479 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1480 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_term_start:'||l_term_start);
1481 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_term_end:'||l_term_end);
1482 END IF;
1483
1484 IF l_term_end < SYSDATE THEN
1485 --past term. Use actual values
1486 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1487 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','-------------Past Term-------------');
1488 END IF;
1489 IF canUseAnticipVal THEN
1490 lv_usage := '2';
1491 ELSE
1492 lv_usage := '1';
1493 END IF;
1494 ELSIF SYSDATE BETWEEN l_term_start AND l_term_end THEN
1495 --Current term. Use actual values
1496 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1497 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','-------------Current Term-------------');
1498 END IF;
1499 IF canUseAnticipVal THEN
1500 lv_usage := '2';
1501 ELSE
1502 lv_usage := '1';
1503 END IF;
1504 ELSE
1505 --Future term. Use anticipated values
1506 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1507 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','-------------Future Term-------------');
1508 END IF;
1509 lv_usage := '3';
1510 END IF;
1511
1512 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1513 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','lv_usage:'||lv_usage);
1514 END IF;
1515
1516 --Derive person id first
1517 l_person_id := NULL;
1518 OPEN c_person_id(p_base_id);
1519 FETCH c_person_id INTO l_person_id;
1520 CLOSE c_person_id;
1521
1522 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1523 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_person_id:'||l_person_id);
1524 END IF;
1525
1526 l_base_det := NULL;
1527
1528 l_anticip := NULL;
1529
1530 IF lv_usage IN ('2','3') THEN
1531 --derive anticipated values. For usage type '2', the values will be used only if actuals are not available
1532 l_anticip := NULL;
1533 OPEN c_anticip(p_base_id,p_ld_cal_type,p_ld_sequence_number);
1534 FETCH c_anticip INTO l_anticip;
1535 CLOSE c_anticip;
1536 END IF;
1537
1538 IF lv_usage IN ('1','2') THEN
1539
1540 --derive actual org unit code
1541 OPEN c_org(l_person_id,p_ld_cal_type,p_ld_sequence_number);
1542 FETCH c_org INTO l_base_det.org_unit_cd,l_dummy_number;
1543 CLOSE c_org;
1544 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1545 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_base_det.org_unit_cd:'||l_base_det.org_unit_cd);
1546 END IF;
1547
1548 IF l_base_det.org_unit_cd IS NULL AND lv_usage = '2' THEN
1549 --Use anticipated org unit code
1550 l_base_det.org_unit_cd := l_anticip.org_unit_cd;
1551
1552 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1553 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_anticip.org_unit_cd:'||l_anticip.org_unit_cd);
1554 END IF;
1555 END IF;
1556
1557 --derive actual program type code
1558 OPEN c_prg_type(l_person_id,p_ld_cal_type,p_ld_sequence_number);
1559 FETCH c_prg_type INTO l_base_det.program_type,l_dummy_number;
1560 CLOSE c_prg_type;
1561 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1562 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_base_det.program_type:'||l_base_det.program_type);
1563 END IF;
1564
1565 IF l_base_det.program_type IS NULL AND lv_usage = '2' THEN
1566 --Use anticipated prog type
1567 l_base_det.program_type := l_anticip.program_type;
1568
1569 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1570 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_anticip.program_type:'||l_anticip.program_type);
1571 END IF;
1572 END IF;
1573
1574 --derive actual program location
1575 OPEN c_prog_loc(l_person_id,p_ld_cal_type,p_ld_sequence_number);
1576 FETCH c_prog_loc INTO l_base_det.program_location_cd,l_dummy_number;
1577 CLOSE c_prog_loc;
1578 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1579 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_base_det.program_location_cd:'||l_base_det.program_location_cd);
1580 END IF;
1581
1582 IF l_base_det.program_location_cd IS NULL AND lv_usage = '2' THEN
1583 --use anticipated program location
1584 l_base_det.program_location_cd := l_anticip.program_location_cd;
1585
1586 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1587 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_anticip.program_location_cd:'||l_anticip.program_location_cd);
1588 END IF;
1589 END IF;
1590
1591 --derive actual program code
1592 OPEN c_prog_code(l_person_id,p_ld_cal_type,p_ld_sequence_number);
1593 FETCH c_prog_code INTO l_base_det.program_cd,l_base_det.version_number,l_dummy_number;
1594 CLOSE c_prog_code;
1595 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1596 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_base_det.program_cd:'||l_base_det.program_cd);
1597 END IF;
1598
1599 IF l_base_det.program_cd IS NULL AND lv_usage = '2' THEN
1600 --use anticipated program code
1601 l_base_det.program_cd := l_anticip.program_cd;
1602 l_base_det.version_number := NULL;
1603 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1604 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_anticip.program_cd:'||l_anticip.program_cd);
1605 END IF;
1606 END IF;
1607
1608 -- museshad (Bug# 4604393)
1609 -- Derive OSS class standing
1610 l_term_start_date := igf_aw_packaging.get_term_start_date(
1611 p_base_id => p_base_id,
1612 p_ld_cal_type => p_ld_cal_type,
1613 p_ld_sequence_number => p_ld_sequence_number
1614 );
1615
1616 IF l_term_start_date IS NOT NULL THEN
1617 IF l_term_start_date > TRUNC(SYSDATE) THEN
1618 -- Predictive Class Standing
1619 l_pred_flag := 'Y';
1620
1621 -- Log message
1622 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1623 fnd_log.string(fnd_log.level_statement,
1624 'igf.plsql.igf_aw_packng_subfns.get_class_stnd.debug',
1625 'Computing PREDICTIVE class standing for date ' || TO_CHAR(l_term_start_date, 'DD-MON-YYYY'));
1626 END IF;
1627 ELSE
1628 -- Actual Class Standing
1629 l_pred_flag := 'N';
1630
1631 -- Log message
1632 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1633 fnd_log.string(fnd_log.level_statement,
1634 'igf.plsql.igf_aw_packng_subfns.get_class_stnd.debug',
1635 'Computing ACTUAL class standing for date ' || TO_CHAR(l_term_start_date, 'DD-MON-YYYY'));
1636 END IF;
1637 END IF;
1638
1639 -- Get the Class Standing
1640 l_base_det.class_standing := igs_pr_get_class_std.get_class_standing(
1641 p_person_id => l_person_id,
1642 p_course_cd => l_base_det.program_cd,
1643 p_predictive_ind => l_pred_flag,
1644 p_effective_dt => l_term_start_date,
1645 p_load_cal_type => NULL,
1646 p_load_ci_sequence_number => NULL
1647 );
1648
1649 -- Log message
1650 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1651 fnd_log.string(fnd_log.level_statement,
1652 'igf.plsql.igf_aw_packng_subfns.get_class_stnd.debug',
1653 'Class Standing= ' || l_base_det.class_standing);
1654 END IF;
1655 ELSE
1656 -- Cannot compute the start date of the term.
1657 -- So, cannot derive Class Standing
1658 l_base_det.class_standing := NULL;
1659
1660 -- Log
1661 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1662 fnd_log.string(fnd_log.level_statement,
1663 'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug',
1664 'Person_Id= ' ||l_person_id|| ', ld_cal_type= ' ||p_ld_cal_type|| ', ld_sequence_number= ' ||p_ld_sequence_number||
1665 '. Cannot derive class standing bcoz the start date of the term is not defined');
1666 END IF;
1667 END IF;
1668 -- museshad (Bug# 4604393)
1669
1670 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1671 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_base_det.class_standing:'||l_base_det.class_standing);
1672 END IF;
1673
1674 IF l_base_det.class_standing IS NULL AND lv_usage = '2' THEN
1675 --use anticipated class standing
1676 l_base_det.class_standing := l_anticip.class_standing;
1677
1678 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1679 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_anticip.class_standing:'||l_anticip.class_standing);
1680 END IF;
1681 END IF;
1682
1683 --derive actual attendance mode
1684 OPEN c_att_mode(l_person_id,p_ld_cal_type,p_ld_sequence_number);
1685 FETCH c_att_mode INTO l_base_det.attendance_mode,l_dummy_number;
1686 CLOSE c_att_mode;
1687 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1688 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_base_det.attendance_mode:'||l_base_det.attendance_mode);
1689 END IF;
1690
1691 IF l_base_det.attendance_mode IS NULL AND lv_usage = '2' THEN
1692 --use anticipated attendance mode
1693 l_base_det.attendance_mode := l_anticip.attendance_mode;
1694
1695 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1696 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_anticip.attendance_mode:'||l_anticip.attendance_mode);
1697 END IF;
1698 END IF;
1699
1700 BEGIN
1701 --derive actual credit points
1702 igs_en_prc_load.enrp_get_inst_latt(
1703 p_person_id => l_person_id,
1704 p_load_cal_type => p_ld_cal_type,
1705 p_load_seq_number => p_ld_sequence_number,
1706 p_attendance => l_attendance,
1707 p_credit_points => l_base_det.credit_points_num,
1708 p_fte => l_fte
1709 );
1710 EXCEPTION
1711 WHEN OTHERS THEN
1712 l_base_det.credit_points_num := NULL;
1713 l_attendance := NULL;
1714 END;
1715 IF l_base_det.credit_points_num IS NULL AND lv_usage = '2' THEN
1716 --derive anticipated credit points
1717 l_base_det.credit_points_num := l_anticip.credit_points_num;
1718
1719 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1720 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_anticip.credit_points_num:'||l_anticip.credit_points_num);
1721 END IF;
1722
1723 END IF;
1724
1725 --derive actual attendance_type--the value of the l_attendance is derived from the above function call i.e enrp_get_inst_latt
1726 l_base_det.attendance_type := l_attendance;
1727
1728 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1729 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_base_det.attendance_type'||l_base_det.attendance_type);
1730 END IF;
1731
1732 IF l_base_det.attendance_type IS NULL AND lv_usage = '2' THEN
1733 --use anticipated attendance type
1734 l_base_det.attendance_type := l_anticip.attendance_type;
1735
1736 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1737 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_anticip.attendance_type:'||l_anticip.attendance_type);
1738 END IF;
1739
1740 END IF;
1741
1742 --derive months enrolled
1743 OPEN get_round_off(p_base_id);
1744 FETCH get_round_off INTO lv_round_off_rec;
1745 CLOSE get_round_off;
1746
1747 l_term_duration := (l_term_end - l_term_start) / NVL(lv_round_off_rec.num_days_divisor,30);
1748
1749 IF (lv_round_off_rec.roundoff_fact = 'RU') THEN
1750 l_term_duration := CEIL( l_term_duration );
1751 ELSIF (lv_round_off_rec.roundoff_fact = 'RD' ) THEN
1752 l_term_duration := FLOOR( l_term_duration );
1753 ELSE
1754 l_term_duration := ROUND( l_term_duration );
1755 END IF;
1756
1757 l_base_det.months_enrolled_num := l_term_duration;
1758 IF l_base_det.months_enrolled_num < 0 THEN
1759 l_base_det.months_enrolled_num := 0;
1760 END IF;
1761 IF l_base_det.months_enrolled_num IS NULL AND lv_usage = '2' THEN
1762 l_base_det.months_enrolled_num := l_anticip.months_enrolled_num;
1763
1764 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1765 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_anticip.months_enrolled_num:'||l_anticip.months_enrolled_num);
1766 END IF;
1767 END IF;
1768
1769 --derive residency status
1770 --first derive the residency class
1771 fnd_profile.get('IGS_FI_RES_CLASS_ID',l_residency_class);
1772 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1773 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_residency_class:'||l_residency_class);
1774 END IF;
1775
1776 l_base_det.residency_status_code := igs_pe_gen_001.Get_Res_Status(l_person_id,l_residency_class,p_ld_cal_type,p_ld_sequence_number);
1777
1778 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1779 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_base_det.residency_status_code:'||l_base_det.residency_status_code);
1780 END IF;
1781
1782 IF l_base_det.residency_status_code IS NULL AND lv_usage = '2' THEN
1783 --derive anticipated residency status
1784 l_base_det.residency_status_code := l_anticip.residency_status_code;
1785 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1786 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen..debug','l_anticip.residency_status_code:'||l_anticip.residency_status_code);
1787 END IF;
1788 END IF;
1789
1790 --derive housing status
1791 OPEN c_housing_status(l_person_id,p_ld_cal_type,p_ld_sequence_number);
1792 FETCH c_housing_status INTO l_base_det.housing_status_code;
1793 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1794 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_base_det.housing_status_code:'||l_base_det.housing_status_code);
1795 END IF;
1796 CLOSE c_housing_status;
1797 IF l_base_det.housing_status_code IS NULL AND lv_usage = '2' THEN
1798 --derive anticipated housing status
1799 l_base_det.housing_status_code := l_anticip.housing_status_code;
1800 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1801 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.getBaseDetails.debug','l_base_det.housing_status_code:'||l_base_det.housing_status_code);
1802 END IF;
1803 END IF;
1804
1805 ELSIF lv_usage = '3' THEN
1806 --derive only anticipated values for all student attributes
1807 l_base_det.attendance_mode := l_anticip.attendance_mode;
1808 l_base_det.attendance_type := l_anticip.attendance_type;
1809 l_base_det.class_standing := l_anticip.class_standing;
1810 l_base_det.credit_points_num := l_anticip.credit_points_num;
1811 l_base_det.housing_status_code := l_anticip.housing_status_code;
1812 l_base_det.months_enrolled_num := l_anticip.months_enrolled_num;
1813 l_base_det.org_unit_cd := l_anticip.org_unit_cd;
1814 l_base_det.program_cd := l_anticip.program_cd;
1815 l_base_det.program_location_cd := l_anticip.program_location_cd;
1816 l_base_det.program_type := l_anticip.program_type;
1817 l_base_det.residency_status_code := l_anticip.residency_status_code;
1818 l_base_det.version_number := NULL;
1819 END IF;
1820
1821 RETURN l_base_det;
1822 EXCEPTION
1823 WHEN OTHERS THEN
1824 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1825 fnd_message.set_token('NAME','IGF_AW_COA_GEN.GETBASEDETAILS ' || SQLERRM);
1826 igs_ge_msg_stack.add;
1827 app_exception.raise_exception;
1828 END getBaseDetails;
1829
1830 PROCEDURE ins_coa_todo(
1831 p_person_id hz_parties.party_id%TYPE,
1832 p_calling_module VARCHAR2,
1833 p_program_code igs_ps_ver.course_cd%TYPE,
1834 p_version_number igs_ps_ver.version_number%TYPE
1835 ) AS
1836 ------------------------------------------------------------------
1837 --Created by : veramach, Oracle India
1838 --Date created: 07-OCT-2004
1839 --
1840 --Purpose:
1841 -- Inserts records into IGS_PE_STD_TODO and IGS_PE_STD_TODO_REF for the given person_id,
1842 -- thereby scheduling a COA Recomputation via concurrent process for the given person, in all open award years
1843 --
1844 -- Parameters:
1845 -- IN Parameters:
1846 -- 1.p_person_id - person_id of the student for whom COA needs to be recomputed
1847 -- 2.p_calling_module - Module which schedules the COA recomputation because of a change in student attributes
1848 -- 3.p_program_code - This is passed when the key program is changed for the person
1849 -- 4.p_version_number - This is passed when the key program is changed for the person
1850 -- OUT Parameters:
1851 -- None
1852 --
1853 --
1854 --Known limitations/enhancements and/or remarks:
1855 --
1856 --Change History:
1857 --Who When What
1858 -------------------------------------------------------------------
1859
1860 -- Get all open award years
1861 CURSOR c_base_records(
1862 cp_person_id hz_parties.party_id%TYPE
1863 ) IS
1864 SELECT batch.ci_cal_type,
1865 batch.ci_sequence_number
1866 FROM igf_ap_batch_aw_map_all batch,
1867 igf_ap_fa_base_rec_all fa,
1868 igf_aw_coa_items coa
1869 WHERE batch.award_year_status_code = 'O'
1870 AND fa.ci_cal_type = batch.ci_cal_type
1871 AND fa.ci_sequence_number = batch.ci_sequence_number
1872 AND fa.base_id = coa.base_id
1873 AND fa.person_id = cp_person_id
1874 GROUP BY batch.ci_cal_type,
1875 batch.ci_sequence_number;
1876
1877 l_seqnum igs_pe_std_todo.sequence_number%TYPE;
1878
1879 -- Get persons with specified key program
1880 CURSOR c_persons(
1881 cp_program_code igs_ps_ver.course_cd%TYPE,
1882 cp_version_number igs_ps_ver.version_number%TYPE
1883 ) IS
1884 SELECT person_id
1885 FROM igs_en_spa_terms
1886 WHERE key_program_flag = 'Y'
1887 AND program_cd = cp_program_code
1888 AND program_version = cp_version_number;
1889
1890 BEGIN
1891 SAVEPOINT IGFAW17B_INS_COA_TODO;
1892 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1893 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.ins_coa_todo.debug','p_person_id:'||p_person_id);
1894 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.ins_coa_todo.debug','p_calling_module:'||p_calling_module);
1895 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.ins_coa_todo.debug','p_program_code:'||p_program_code);
1896 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.ins_coa_todo.debug','p_version_number:'||p_version_number);
1897 END IF;
1898
1899 IF p_person_id IS NOT NULL THEN
1900 --have to recompute COA for a single student only
1901 --insert a master record.
1902 --also insert child records for all open award years, in which the student has a financial aid base record
1903 l_seqnum := igs_ge_gen_003.genp_ins_stdnt_todo(
1904 p_person_id => p_person_id,
1905 p_s_student_todo_type => 'IGF_COA_COMP',
1906 p_todo_dt => TRUNC(SYSDATE),
1907 p_single_entry_ind => 'Y'
1908 );
1909
1910 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1911 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.ins_coa_todo.debug','l_seqnum:'||l_seqnum);
1912 END IF;
1913
1914 FOR l_base_records IN c_base_records(p_person_id) LOOP
1915 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1916 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.ins_coa_todo.debug','l_base_records.ci_cal_type:'||l_base_records.ci_cal_type);
1917 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_gen.ins_coa_todo.debug','l_base_records.ci_sequence_number:'||l_base_records.ci_sequence_number);
1918 END IF;
1919 igs_ge_gen_003.genp_ins_todo_ref(
1920 p_person_id => p_person_id,
1921 p_s_student_todo_type => 'IGF_COA_COMP',
1922 p_sequence_number => l_seqnum,
1923 p_cal_type => l_base_records.ci_cal_type,
1924 p_ci_sequence_number => l_base_records.ci_sequence_number,
1925 p_course_cd => NULL,
1926 p_unit_cd => NULL,
1927 p_other_reference => NULL,
1928 p_uoo_id => NULL
1929 );
1930 END LOOP;
1931 ELSE
1932 --wrapper has been called for a course code/version number
1933 --find all the persons who have this program/version as the key program
1934 --recompute COA for those persons
1935 FOR l_persons IN c_persons(p_program_code,p_version_number) LOOP
1936 igf_aw_coa_gen.ins_coa_todo(
1937 p_person_id => l_persons.person_id,
1938 p_calling_module => p_calling_module,
1939 p_program_code => NULL,
1940 p_version_number => NULL
1941 );
1942 END LOOP;
1943 END IF;
1944 EXCEPTION
1945 WHEN OTHERS THEN
1946 ROLLBACK TO IGFAW17B_INS_COA_TODO;
1947 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1948 fnd_message.set_token('NAME','IGF_AW_COA_GEN.INS_COA_TODO ' || SQLERRM);
1949 igs_ge_msg_stack.add;
1950 app_exception.raise_exception;
1951
1952 END ins_coa_todo;
1953
1954 -- This procedure is to set and return the Awarding Process Status
1955 FUNCTION set_awd_proc_status(
1956 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
1957 p_award_prd_code IN igf_aw_award_prd.award_prd_cd%TYPE DEFAULT NULL
1958 ) RETURN VARCHAR2 AS
1959 --------------------------------------------------------------------------------
1960 -- Created by : ridas, Oracle India
1961 -- Date created: 27-OCT-2004
1962
1963 -- Change History:
1964 -- Who When What
1965 --------------------------------------------------------------------------------
1966
1967 --Cursor to filter out those awards that fall within the awarding period
1968 CURSOR c_award_prd(
1969 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE ,
1970 c_award_prd_code igf_aw_award_prd.award_prd_cd%TYPE
1971 ) IS
1972 SELECT awd.rowid row_id,
1973 awd.*
1974 FROM igf_aw_award_all awd
1975 WHERE awd.base_id = c_base_id
1976 AND (awd.awd_proc_status_code = 'AWARDED' OR awd.awd_proc_status_code IS NULL)
1977 AND NOT EXISTS
1978 (SELECT disb.ld_cal_type,
1979 disb.ld_sequence_number
1980 FROM igf_aw_awd_disb_all disb
1981 WHERE disb.award_id = awd.award_id
1982 MINUS
1983 SELECT apt.ld_cal_type,
1984 apt.ld_sequence_number
1985 FROM igf_ap_fa_base_rec_all fab,
1986 igf_aw_awd_prd_term apt
1987 WHERE fab.base_id = c_base_id AND
1988 apt.ci_cal_type = fab.ci_cal_type AND
1989 apt.ci_sequence_number = fab.ci_sequence_number AND
1990 apt.award_prd_cd = c_award_prd_code
1991 );
1992
1993
1994 --Cursor to fetch all the awards for the base id
1995 CURSOR c_awards(
1996 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1997 ) IS
1998 SELECT awd.rowid row_id,
1999 awd.*
2000 FROM igf_aw_award_all awd
2001 WHERE awd.base_id = c_base_id
2002 AND (awd.awd_proc_status_code = 'AWARDED' OR awd.awd_proc_status_code IS NULL);
2003
2004 lv_profile_value VARCHAR2(10);
2005 lv_status VARCHAR2(50);
2006 ln_counter NUMBER;
2007
2008 BEGIN
2009 ln_counter := 0;
2010
2011 fnd_profile.get('IGF_AW_REV_REPKG',lv_profile_value);
2012
2013 IF lv_profile_value = 'Y' THEN
2014 lv_status := 'REVIEW';
2015 ELSE
2016 lv_status := 'READY';
2017 END IF;
2018
2019
2020 IF (p_base_id IS NOT NULL AND p_award_prd_code IS NULL) THEN
2021 FOR l_awards IN c_awards(p_base_id)
2022 LOOP
2023 ln_counter := 1;
2024
2025 igf_aw_award_pkg.update_row(
2026 x_mode => 'R',
2027 x_rowid => l_awards.row_id ,
2028 x_award_id => l_awards.award_id ,
2029 x_fund_id => l_awards.fund_id ,
2030 x_base_id => l_awards.base_id ,
2031 x_offered_amt => l_awards.offered_amt ,
2032 x_accepted_amt => l_awards.accepted_amt ,
2033 x_paid_amt => l_awards.paid_amt ,
2034 x_packaging_type => l_awards.packaging_type ,
2035 x_batch_id => l_awards.batch_id ,
2036 x_manual_update => l_awards.manual_update ,
2037 x_rules_override => l_awards.rules_override ,
2038 x_award_date => l_awards.award_date ,
2039 x_award_status => l_awards.award_status ,
2040 x_attribute_category => l_awards.attribute_category ,
2041 x_attribute1 => l_awards.attribute1 ,
2042 x_attribute2 => l_awards.attribute2 ,
2043 x_attribute3 => l_awards.attribute3 ,
2044 x_attribute4 => l_awards.attribute4 ,
2045 x_attribute5 => l_awards.attribute5 ,
2046 x_attribute6 => l_awards.attribute6 ,
2047 x_attribute7 => l_awards.attribute7 ,
2048 x_attribute8 => l_awards.attribute8 ,
2049 x_attribute9 => l_awards.attribute9 ,
2050 x_attribute10 => l_awards.attribute10 ,
2051 x_attribute11 => l_awards.attribute11 ,
2052 x_attribute12 => l_awards.attribute12 ,
2053 x_attribute13 => l_awards.attribute13 ,
2054 x_attribute14 => l_awards.attribute14 ,
2055 x_attribute15 => l_awards.attribute15 ,
2056 x_attribute16 => l_awards.attribute16 ,
2057 x_attribute17 => l_awards.attribute17 ,
2058 x_attribute18 => l_awards.attribute18 ,
2059 x_attribute19 => l_awards.attribute19 ,
2060 x_attribute20 => l_awards.attribute20 ,
2061 x_rvsn_id => l_awards.rvsn_id ,
2062 x_alt_pell_schedule => l_awards.alt_pell_schedule ,
2063 x_award_number_txt => l_awards.award_number_txt ,
2064 x_legacy_record_flag => l_awards.legacy_record_flag ,
2065 x_adplans_id => l_awards.adplans_id ,
2066 x_lock_award_flag => l_awards.lock_award_flag ,
2067 x_app_trans_num_txt => l_awards.app_trans_num_txt ,
2068 x_awd_proc_status_code => lv_status ,
2069 x_notification_status_code => l_awards.notification_status_code,
2070 x_notification_status_date => l_awards.notification_status_date,
2071 x_publish_in_ss_flag => l_awards.publish_in_ss_flag
2072 );
2073
2074 END LOOP;
2075 ELSIF (p_base_id IS NOT NULL AND p_award_prd_code IS NOT NULL) THEN
2076 FOR l_award_prd IN c_award_prd(p_base_id,p_award_prd_code)
2077 LOOP
2078 ln_counter := 1;
2079
2080 igf_aw_award_pkg.update_row(
2081 x_mode => 'R',
2082 x_rowid => l_award_prd.row_id ,
2083 x_award_id => l_award_prd.award_id ,
2084 x_fund_id => l_award_prd.fund_id ,
2085 x_base_id => l_award_prd.base_id ,
2086 x_offered_amt => l_award_prd.offered_amt ,
2087 x_accepted_amt => l_award_prd.accepted_amt ,
2088 x_paid_amt => l_award_prd.paid_amt ,
2089 x_packaging_type => l_award_prd.packaging_type ,
2090 x_batch_id => l_award_prd.batch_id ,
2091 x_manual_update => l_award_prd.manual_update ,
2092 x_rules_override => l_award_prd.rules_override ,
2093 x_award_date => l_award_prd.award_date ,
2094 x_award_status => l_award_prd.award_status ,
2095 x_attribute_category => l_award_prd.attribute_category ,
2096 x_attribute1 => l_award_prd.attribute1 ,
2097 x_attribute2 => l_award_prd.attribute2 ,
2098 x_attribute3 => l_award_prd.attribute3 ,
2099 x_attribute4 => l_award_prd.attribute4 ,
2100 x_attribute5 => l_award_prd.attribute5 ,
2101 x_attribute6 => l_award_prd.attribute6 ,
2102 x_attribute7 => l_award_prd.attribute7 ,
2103 x_attribute8 => l_award_prd.attribute8 ,
2104 x_attribute9 => l_award_prd.attribute9 ,
2105 x_attribute10 => l_award_prd.attribute10 ,
2106 x_attribute11 => l_award_prd.attribute11 ,
2107 x_attribute12 => l_award_prd.attribute12 ,
2108 x_attribute13 => l_award_prd.attribute13 ,
2109 x_attribute14 => l_award_prd.attribute14 ,
2110 x_attribute15 => l_award_prd.attribute15 ,
2111 x_attribute16 => l_award_prd.attribute16 ,
2112 x_attribute17 => l_award_prd.attribute17 ,
2113 x_attribute18 => l_award_prd.attribute18 ,
2114 x_attribute19 => l_award_prd.attribute19 ,
2115 x_attribute20 => l_award_prd.attribute20 ,
2116 x_rvsn_id => l_award_prd.rvsn_id ,
2117 x_alt_pell_schedule => l_award_prd.alt_pell_schedule ,
2118 x_award_number_txt => l_award_prd.award_number_txt ,
2119 x_legacy_record_flag => l_award_prd.legacy_record_flag ,
2120 x_adplans_id => l_award_prd.adplans_id ,
2121 x_lock_award_flag => l_award_prd.lock_award_flag ,
2122 x_app_trans_num_txt => l_award_prd.app_trans_num_txt ,
2123 x_awd_proc_status_code => lv_status ,
2124 x_notification_status_code => l_award_prd.notification_status_code,
2125 x_notification_status_date => l_award_prd.notification_status_date,
2126 x_publish_in_ss_flag => l_award_prd.publish_in_ss_flag
2127 );
2128 END LOOP;
2129 END IF;
2130
2131 IF ln_counter = 1 THEN
2132 RETURN lv_status;
2133 ELSE
2134 RETURN NULL;
2135 END IF;
2136
2137 END set_awd_proc_status;
2138
2139 PROCEDURE get_award_period_dates(
2140 p_ci_cal_type IN igs_ca_inst.cal_type%TYPE,
2141 p_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
2142 p_award_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE,
2143 p_start_date OUT NOCOPY DATE,
2144 p_end_date OUT NOCOPY DATE
2145 ) AS
2146 ------------------------------------------------------------------
2147 --Created by : veramach, Oracle India
2148 --Date created: 29-Oct-2004
2149 --
2150 --Purpose: Get Awarding period start end dates
2151 --
2152 --
2153 --Known limitations/enhancements and/or remarks:
2154 --
2155 --Change History:
2156 --Who When What
2157 -------------------------------------------------------------------
2158
2159 -- Get all terms attached to the awarding period
2160 CURSOR c_load_cal(
2161 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
2162 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
2163 cp_award_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
2164 ) IS
2165 SELECT MIN(cal.start_dt) start_date,
2166 MAX(cal.end_dt) end_date
2167 FROM igf_aw_awd_prd_term aprd,
2168 igs_ca_inst cal
2169 WHERE aprd.ci_cal_type = cp_ci_cal_type
2170 AND aprd.ci_sequence_number = cp_ci_sequence_number
2171 AND aprd.award_prd_cd = cp_award_prd_code
2172 AND aprd.ld_cal_type = cal.cal_type
2173 AND aprd.ld_sequence_number = cal.sequence_number;
2174
2175 BEGIN
2176 p_start_date := NULL;
2177 p_end_date := NULL;
2178
2179 OPEN c_load_cal(p_ci_cal_type,p_ci_sequence_number,p_award_prd_code);
2180 FETCH c_load_cal INTO p_start_date,p_end_date;
2181 CLOSE c_load_cal;
2182
2183 END get_award_period_dates;
2184
2185 PROCEDURE check_oss_attrib(
2186 p_org_unit_code IN igf_ap_fa_ant_data.org_unit_cd%TYPE,
2187 p_program_code IN igf_ap_fa_ant_data.program_cd%TYPE,
2188 p_program_type IN igf_ap_fa_ant_data.program_type%TYPE,
2189 p_program_location IN igf_ap_fa_ant_data.program_location_cd%TYPE,
2190 p_attend_type IN igf_ap_fa_ant_data.attendance_type%TYPE,
2191 p_attend_mode IN igf_ap_fa_ant_data.attendance_mode%TYPE,
2192 p_ret_status OUT NOCOPY VARCHAR2
2193 ) IS
2194 ------------------------------------------------------------------
2195 --Created by : ridas, Oracle India
2196 --Date created: 02-NOV-2004
2197 --
2198 --Purpose:
2199 --
2200 --
2201 --Known limitations/enhancements and/or remarks:
2202 --
2203 --Change History:
2204 --Who When What
2205 --ridas 09-Aug-2005 Bug #4164450. Added new validations to check
2206 -- the program offering options
2207 -------------------------------------------------------------------
2208
2209 -- Cursor to check whether the anticipated org unit is a responsible/owning org unit
2210 -- for the specified anticipated key program or not
2211 CURSOR c_chk_org (cp_org_unit igf_ap_fa_ant_data.org_unit_cd%TYPE,
2212 cp_program_code igf_ap_fa_ant_data.program_cd%TYPE)
2213 IS
2214 SELECT 'x'
2215 FROM igs_ps_ver
2216 WHERE course_cd = cp_program_code
2217 AND ( responsible_org_unit_cd = cp_org_unit
2218 OR EXISTS (SELECT 'x'
2219 FROM igs_ps_own
2220 WHERE course_cd = cp_program_code
2221 AND org_unit_cd = cp_org_unit
2222 )
2223 );
2224
2225 l_chk_org c_chk_org%ROWTYPE;
2226
2227
2228 -- Cursor to check the Anticipated Key Program specified is of Anticipated Program Type or not
2229 CURSOR c_chk_prog (cp_program_code igf_ap_fa_ant_data.program_cd%TYPE,
2230 cp_program_type igf_ap_fa_ant_data.program_type%TYPE)
2231 IS
2232 SELECT 'x'
2233 FROM igs_ps_ver
2234 WHERE course_cd = cp_program_code
2235 AND course_type = cp_program_type;
2236
2237 l_chk_prog c_chk_prog%ROWTYPE;
2238
2239 -- Cursor to check Key Program at the specified Program Location
2240 CURSOR c_chk_prog_offer (cp_program_code igf_ap_fa_ant_data.program_cd%TYPE,
2241 cp_location_code igf_ap_fa_ant_data.program_location_cd%TYPE)
2242 IS
2243 SELECT 'x'
2244 FROM igs_ps_ofr_opt_all offering
2245 WHERE offering.course_cd = cp_program_code
2246 AND offering.location_cd = cp_location_code;
2247
2248 l_chk_prog_offer c_chk_prog_offer%ROWTYPE;
2249
2250
2251 -- Cursor to check the existence of the program offering option for the combination of Key Program,
2252 -- Program Location, Anticipated Attendance Type and Anticipated Attendance Mode
2253 CURSOR c_chk_prog_option (cp_program_code igf_ap_fa_ant_data.program_cd%TYPE,
2254 cp_location_code igf_ap_fa_ant_data.program_location_cd%TYPE,
2255 cp_attend_type igf_ap_fa_ant_data.attendance_type%TYPE,
2256 cp_attend_mode igf_ap_fa_ant_data.attendance_mode%TYPE)
2257 IS
2258 SELECT 'x'
2259 FROM igs_ps_ofr_opt_all offering,
2260 igs_ps_ofr_pat offering_pattern
2261 WHERE offering.course_cd = cp_program_code
2262 AND offering.location_cd = cp_location_code
2263 AND offering.attendance_mode = cp_attend_mode
2264 AND offering.attendance_type = cp_attend_type
2265 AND offering.delete_flag = 'N'
2266 AND offering.coo_id = offering_pattern.coo_id
2267 AND offering_pattern.offered_ind = 'Y';
2268
2269 l_chk_prog_option c_chk_prog_option%ROWTYPE;
2270
2271
2272 BEGIN
2273 p_ret_status := 'S' ;
2274
2275 IF (p_program_code IS NOT NULL AND p_org_unit_code IS NOT NULL) THEN
2276 -- Check responsible/owning org unit
2277 OPEN c_chk_org(p_org_unit_code,
2278 p_program_code);
2279
2280 FETCH c_chk_org INTO l_chk_org;
2281
2282 IF c_chk_org%NOTFOUND THEN
2283 p_ret_status := 'W' ;
2284 fnd_message.set_name('IGF','IGF_AW_NOT_RESP_ORG_UNIT');
2285 fnd_message.set_token('ORG_UNIT',p_org_unit_code);
2286 fnd_message.set_token('PROG_CODE',p_program_code);
2287 igs_ge_msg_stack.add;
2288 END IF;
2289 CLOSE c_chk_org;
2290 END IF;
2291
2292
2293 IF (p_program_code IS NOT NULL AND p_program_type IS NOT NULL) THEN
2294 -- Check the type of the anticipated key program
2295 OPEN c_chk_prog(p_program_code,
2296 p_program_type);
2297
2298 FETCH c_chk_prog INTO l_chk_prog;
2299
2300 IF c_chk_prog%NOTFOUND THEN
2301 p_ret_status := 'W' ;
2302 fnd_message.set_name('IGF','IGF_AW_PROG_NOT_OF_TYPE');
2303 fnd_message.set_token('PROG_CODE',p_program_code);
2304 fnd_message.set_token('PROG_TYPE',p_program_type);
2305 igs_ge_msg_stack.add;
2306 END IF;
2307 CLOSE c_chk_prog;
2308 END IF;
2309
2310
2311 IF (p_program_code IS NOT NULL AND p_program_location IS NOT NULL) THEN
2312 -- Check the program location
2313 OPEN c_chk_prog_offer(p_program_code,
2314 p_program_location
2315 );
2316
2317 FETCH c_chk_prog_offer INTO l_chk_prog_offer;
2318 IF c_chk_prog_offer%NOTFOUND THEN
2319 p_ret_status := 'W' ;
2320 fnd_message.set_name('IGF','IGF_AW_PRG_NT_OFFER');
2321 fnd_message.set_token('PROG_CODE',p_program_code);
2322 fnd_message.set_token('PROG_LOC',p_program_location);
2323 igs_ge_msg_stack.add;
2324 END IF;
2325 CLOSE c_chk_prog_offer;
2326 END IF;
2327
2328
2329 IF (p_program_code IS NOT NULL AND p_program_location IS NOT NULL AND p_attend_type IS NOT NULL AND p_attend_mode IS NOT NULL) THEN
2330 -- Check the program offering options
2331 OPEN c_chk_prog_option(p_program_code,
2332 p_program_location,
2333 p_attend_type,
2334 p_attend_mode);
2335
2336 FETCH c_chk_prog_option INTO l_chk_prog_option;
2337 IF c_chk_prog_option%NOTFOUND THEN
2338 p_ret_status := 'W' ;
2339 fnd_message.set_name('IGF','IGF_AW_NO_PROG_LOC');
2340 fnd_message.set_token('PROG_CODE',p_program_code);
2341 fnd_message.set_token('PROG_LOC',p_program_location);
2342 fnd_message.set_token('ATTND_TYPE',p_attend_type);
2343 fnd_message.set_token('ATTND_MODE',p_attend_mode);
2344 igs_ge_msg_stack.add;
2345 END IF;
2346 CLOSE c_chk_prog_option;
2347 END IF;
2348
2349 END check_oss_attrib;
2350
2351 END igf_aw_coa_gen;