[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_AWD_DISB_PKG
Source
1 PACKAGE BODY igf_aw_awd_disb_pkg AS
2 /* $Header: IGFWI24B.pls 120.5 2006/08/07 08:11:27 veramach ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_aw_awd_disb_all%ROWTYPE;
6 new_references igf_aw_awd_disb_all%ROWTYPE;
7 g_v_called_from VARCHAR2(30);
8
9 PROCEDURE AfterRowInsertUpdateDelete1(
10 p_inserting IN BOOLEAN ,
11 p_updating IN BOOLEAN ,
12 p_deleting IN BOOLEAN
13 );
14
15 PROCEDURE BeforeRowInsertUpdateDelete1(
16 p_rowid IN VARCHAR2,
17 p_inserting IN BOOLEAN ,
18 p_updating IN BOOLEAN ,
19 p_deleting IN BOOLEAN
20 );
21
22 PROCEDURE after_dml (
23 p_action IN VARCHAR2) AS
24 /*--------------------------------------------------------------
25 || Created By : AYEDUBAT
26 || Created On : 15-OCT-2004
27 || Purpose :
28 || Known limitations, enhancements or remarks :
29 || Change History :
30 || Who When What
31 || (reverse chronological order - newest change first)
32 || museshad 05-Jan-2006 Bug 4930323. Fixed issue in DRI validation.
33 || Did code clean up by introducing new flag variable
34 || l_change_value.
35 || ridas 10-Jan-2005 Bug #3701698. NVL is added in parameter new_references.disb_accepted_amt
36 || in procedure igf_aw_db_chg_dtls_pkg.insert_row
37 --------------------------------------------------------------*/
38
39 CURSOR award_year_cal_cur (cp_award_id IGF_AW_AWD_DISB_ALL.award_id%TYPE) IS
40 SELECT fmast.ci_cal_type,fmast.ci_sequence_number, awd.award_status
41 FROM IGF_AW_AWARD_ALL awd,
42 IGF_AW_FUND_MAST fmast
43 WHERE awd.award_id = cp_award_id
44 AND awd.fund_id = fmast.fund_id;
45 award_year_cal_rec award_year_cal_cur%ROWTYPE;
46
47 CURSOR upd_db_chg_dtls_cur( cp_award_id igf_aw_db_chg_dtls.award_id%TYPE,
48 cp_disb_num igf_aw_db_chg_dtls.disb_num%TYPE,
49 cp_disb_seq_num igf_aw_db_chg_dtls.disb_seq_num%TYPE) IS
50 SELECT dbchgdtls.ROWID,dbchgdtls.*
51 FROM igf_aw_db_chg_dtls dbchgdtls
52 WHERE dbchgdtls.award_id = cp_award_id
53 AND dbchgdtls.disb_num = cp_disb_num
54 AND dbchgdtls.disb_seq_num = cp_disb_seq_num;
55 upd_db_chg_dtls_rec upd_db_chg_dtls_cur%ROWTYPE;
56
57 CURSOR max_disb_seq_num_cur(cp_award_id igf_aw_db_chg_dtls.award_id%TYPE,
58 cp_disb_num igf_aw_db_chg_dtls.disb_num%TYPE) IS
59 SELECT max(dbchgdtls.disb_seq_num)+1
60 FROM igf_aw_db_chg_dtls dbchgdtls
61 WHERE dbchgdtls.award_id = cp_award_id
62 AND dbchgdtls.disb_num = cp_disb_num;
63 l_max_disb_seq_num igf_aw_db_chg_dtls.disb_seq_num%TYPE;
64
65 CURSOR loans_cur (cp_award_id igf_aw_db_chg_dtls.award_id%TYPE) IS
66 SELECT sl.ROWID, sl.*
67 FROM IGF_SL_LOANS_ALL sl
68 WHERE sl.award_id = cp_award_id;
69 loans_rec loans_cur%ROWTYPE;
70
71 CURSOR pell_cur (cp_award_id igf_aw_db_chg_dtls.award_id%TYPE) IS
72 SELECT pell.*, pell.ROWID
73 FROM igf_gr_rfms_all pell
74 WHERE pell.award_id = cp_award_id;
75
76 pell_rec pell_cur%ROWTYPE;
77
78 CURSOR min_disb_date_cur(cp_award_id igf_aw_awd_disb_all.award_id%TYPE) IS
79 SELECT min(disb_date)
80 FROM igf_aw_awd_disb_all
81 WHERE award_id = cp_award_id;
82
83 CURSOR cur_latest_accepted_DateAmt ( cp_award_id igf_aw_db_chg_dtls.award_id%TYPE,
84 cp_disb_num igf_aw_db_chg_dtls.disb_num%TYPE,
85 cp_disb_activity igf_aw_db_chg_dtls.disb_activity%TYPE) IS
86 SELECT chgdtls.*
87 FROM IGF_AW_DB_CHG_DTLS chgdtls
88 WHERE chgdtls.award_id = cp_award_id
89 AND chgdtls.disb_num = cp_disb_num
90 AND chgdtls.disb_status = 'A'
91 AND (chgdtls.disb_activity = 'P' OR chgdtls.disb_activity = cp_disb_activity)
92 ORDER BY chgdtls.disb_seq_num DESC;
93 latest_accepted_DateAmount_rec cur_latest_accepted_DateAmt%ROWTYPE;
94
95 CURSOR cur_latest_DateAmt_for_update ( cp_award_id igf_aw_db_chg_dtls.award_id%TYPE,
96 cp_disb_num igf_aw_db_chg_dtls.disb_num%TYPE,
97 cp_disb_activity igf_aw_db_chg_dtls.disb_activity%TYPE) IS
98 SELECT chgdtls.ROWID, chgdtls.*
99 FROM IGF_AW_DB_CHG_DTLS chgdtls
100 WHERE chgdtls.award_id = cp_award_id
101 AND chgdtls.disb_num = cp_disb_num
102 AND chgdtls.disb_status <> 'A'
103 AND chgdtls.disb_activity = cp_disb_activity
104 ORDER BY chgdtls.disb_seq_num DESC;
105 latest_update_rec cur_latest_DateAmt_for_update%ROWTYPE;
106
107 CURSOR cur_updated_award_amount (cp_award_id igf_aw_db_chg_dtls.award_id%TYPE) IS
108 SELECT SUM(awddisb.DISB_NET_AMT) total_amt
109 FROM IGF_AW_AWD_DISB_ALL awddisb
110 WHERE awddisb.award_id = cp_award_id;
111 updated_award_amount cur_updated_award_amount%ROWTYPE;
112
113 l_row_id ROWID;
114 l_message VARCHAR2(2000);
115 l_cod_year_flag BOOLEAN;
116 l_dl_disb_change_status BOOLEAN;
117 lv_first_disb_seq_accepted BOOLEAN;
118 l_fund_code igf_aw_fund_cat.fed_fund_code%TYPE;
119 l_loan_type VARCHAR2(10);
120 l_min_disb_date DATE;
121 l_disb_activity igf_aw_db_chg_dtls.disb_activity%TYPE;
122 l_first_disb_flag igf_aw_db_chg_dtls.first_disb_flag%TYPE;
123 l_change_value NUMBER := 0;
124
125 BEGIN
126 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
127 fnd_log.string( fnd_log.level_statement,
128 'igf.plsql.igf_aw_awd_disb_pkg.after_dml ',
129 'Processing award_id= ' ||new_references.award_id|| ', disb num= ' ||new_references.disb_num);
130 END IF;
131
132 l_rowid := NULL;
133 IF (p_action = 'UPDATE') THEN
134 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
135 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.after_dml ', 'action = update ' );
136 END IF;
137 -- Call all the procedures related to After Update.
138 AfterRowInsertUpdateDelete1
139 (
140 p_inserting => FALSE,
141 p_updating => TRUE ,
142 p_deleting => FALSE
143 );
144 ELSIF (p_action = 'INSERT') THEN
145 -- Call all the procedures related to After insert
146 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
147 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.after_dml ', 'action = insert ' );
148 END IF;
149 AfterRowInsertUpdateDelete1
150 (
151 p_inserting => TRUE,
152 p_updating => FALSE ,
153 p_deleting => FALSE
154 );
155 END IF;
156 l_dl_disb_change_status := FALSE;
157 l_first_disb_flag := NULL;
158 l_min_disb_date := NULL;
159
160 -- Get the Federal Fund Code
161 l_fund_code := NULL;
162 l_fund_code := igf_sl_gen.get_fed_fund_code(new_references.award_id, l_message);
163
164 -- If fund Code is of Type, Direct Loans then Pass DL for COD-XML Processing
165 -- If fund Code is of Type, Pell Grants then Pass PELL for COD-XML Processing
166
167 -- Get the Award Year Calendar Instance
168 OPEN award_year_cal_cur(new_references.award_id);
169 FETCH award_year_cal_cur INTO award_year_cal_rec;
170 CLOSE award_year_cal_cur;
171
172 OPEN loans_cur(new_references.award_id);
173 FETCH loans_cur INTO loans_rec;
174 CLOSE loans_cur;
175
176 OPEN pell_cur(new_references.award_id);
177 FETCH pell_cur INTO pell_rec;
178 CLOSE pell_cur;
179
180 -- Check whether the awarding year is COD-XML processing year
181 l_cod_year_flag := NULL;
182 l_loan_type := NULL;
183
184 IF l_fund_code IN ('DLP','DLS','DLU') AND
185 award_year_cal_rec.award_status <> 'SIMULATED' AND
186 (NVL(loans_rec.loan_status,'*') <> 'S' OR NVL(loans_rec.loan_status,'S') <> 'S' ) THEN
187 l_loan_type := 'DL';
188 l_cod_year_flag := igf_sl_dl_validation.check_full_participant (award_year_cal_rec.ci_cal_type,award_year_cal_rec.ci_sequence_number,'DL');
189 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
190 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug',' DL is true ' );
191 END IF;
192
193 ELSIF l_fund_code = 'PELL' AND
194 award_year_cal_rec.award_status <> 'SIMULATED' AND
195 NVL(pell_rec.orig_action_code,'*') <> 'S' AND
196 (igf_sl_dl_validation.check_full_participant (award_year_cal_rec.ci_cal_type,award_year_cal_rec.ci_sequence_number,'PELL')) THEN
197 l_loan_type := 'PELL';
198 l_cod_year_flag := TRUE;
199
200 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
201 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug',' Pell is true ' );
202 END IF;
203
204 IF NVL(new_references.payment_prd_st_date,TO_DATE('4712/12/31','YYYY/MM/DD')) <> NVL(old_references.payment_prd_st_date,TO_DATE('4712/12/31','YYYY/MM/DD'))
205 OR NVL(new_references.hold_rel_ind,'*') <> NVL(old_references.hold_rel_ind,'*')
206 OR NVL(new_references.disb_accepted_amt,0) <> NVL(old_references.disb_accepted_amt,0)
207 OR new_references.disb_date <> old_references.disb_date THEN
208 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
209 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug',' Before update in RFMS ' );
210 END IF;
211 IF pell_rec.rowid IS NOT NULL THEN
212
213 --akomurav
214 --The pell amount was not updated in the RFMS table when ever there is some change in the PELL amount
215 --this fix will update the RFMS table with the pell amount (if it is updated due to deletion or updation of Disbursments
216 OPEN cur_updated_award_amount(new_references.award_id);
217 FETCH cur_updated_award_amount INTO updated_award_amount;
218 CLOSE cur_updated_award_amount;
219
220 igf_gr_rfms_pkg.update_row(
221 x_rowid => pell_rec.rowid,
222 x_origination_id => pell_rec.origination_id,
223 x_ci_cal_type => pell_rec.ci_cal_type,
224 x_ci_sequence_number => pell_rec.ci_sequence_number,
225 x_base_id => pell_rec.base_id,
226 x_award_id => pell_rec.award_id,
227 x_rfmb_id => pell_rec.rfmb_id,
228 x_sys_orig_ssn => pell_rec.sys_orig_ssn,
229 x_sys_orig_name_cd => pell_rec.sys_orig_name_cd,
230 x_transaction_num => pell_rec.transaction_num,
231 x_efc => pell_rec.efc,
232 x_ver_status_code => pell_rec.ver_status_code,
233 x_secondary_efc => pell_rec.secondary_efc,
234 x_secondary_efc_cd => pell_rec.secondary_efc_cd,
235 x_pell_amount => updated_award_amount.total_amt,
236 x_pell_profile => pell_rec.pell_profile,
237 x_enrollment_status => pell_rec.enrollment_status,
238 x_enrollment_dt => pell_rec.enrollment_dt,
239 x_coa_amount => pell_rec.coa_amount,
240 x_academic_calendar => pell_rec.academic_calendar,
241 x_payment_method => pell_rec.payment_method,
242 x_total_pymt_prds => pell_rec.total_pymt_prds,
243 x_incrcd_fed_pell_rcp_cd => pell_rec.incrcd_fed_pell_rcp_cd,
244 x_attending_campus_id => pell_rec.attending_campus_id,
245 x_est_disb_dt1 => pell_rec.est_disb_dt1,
246 x_orig_action_code => 'R', -- ready to send
247 x_orig_status_dt => TRUNC(SYSDATE),
248 x_orig_ed_use_flags => pell_rec.orig_ed_use_flags,
249 x_ft_pell_amount => pell_rec.ft_pell_amount,
250 x_prev_accpt_efc => pell_rec.prev_accpt_efc,
251 x_prev_accpt_tran_no => pell_rec.prev_accpt_tran_no,
252 x_prev_accpt_sec_efc_cd => pell_rec.prev_accpt_sec_efc_cd,
253 x_prev_accpt_coa => pell_rec.prev_accpt_coa,
254 x_orig_reject_code => pell_rec.orig_reject_code,
255 x_wk_inst_time_calc_pymt => pell_rec.wk_inst_time_calc_pymt,
256 x_wk_int_time_prg_def_yr => pell_rec.wk_int_time_prg_def_yr,
257 x_cr_clk_hrs_prds_sch_yr => pell_rec.cr_clk_hrs_prds_sch_yr,
258 x_cr_clk_hrs_acad_yr => pell_rec.cr_clk_hrs_acad_yr,
259 x_inst_cross_ref_cd => pell_rec.inst_cross_ref_cd,
260 x_low_tution_fee => pell_rec.low_tution_fee,
261 x_rec_source => pell_rec.rec_source,
262 x_pending_amount => pell_rec.pending_amount,
263 x_mode => 'R',
264 x_birth_dt => pell_rec.birth_dt,
265 x_last_name => pell_rec.last_name,
266 x_first_name => pell_rec.first_name,
267 x_middle_name => pell_rec.middle_name,
268 x_current_ssn => pell_rec.current_ssn,
269 x_legacy_record_flag => pell_rec.legacy_record_flag,
270 x_reporting_pell_cd => pell_rec.reporting_pell_cd,
274 x_full_resp_code => pell_rec.full_resp_code,
271 x_rep_entity_id_txt => pell_rec.rep_entity_id_txt,
272 x_atd_entity_id_txt => pell_rec.atd_entity_id_txt,
273 x_note_message => pell_rec.note_message,
275 x_document_id_txt => pell_rec.document_id_txt);
276 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
277 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug',' updated rfms table ' );
278 END IF;
279 END IF;
280 END IF; -- move rfms status
281 END IF; -- loan type
282
283 IF l_loan_type IN ('DL','PELL') THEN
284 -- Get the Minimum Disbursement Date for the Award
285 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
286 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug',' Loan Type is ' || l_loan_type );
287 END IF;
288
289 OPEN min_disb_date_cur (new_references.award_id);
290 FETCH min_disb_date_cur INTO l_min_disb_date;
291 CLOSE min_disb_date_cur;
292
293 -- if the disbursement date is the minimun date then assign true else assign false;
294 IF new_references.disb_date = l_min_disb_date THEN
295 l_first_disb_flag := 'true';
296 ELSE
297 l_first_disb_flag := 'false';
298 END IF;
299
300 -- When a new Disbursement record is created, create a Disbursement change details record
301 IF p_action = 'INSERT' 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_awd_disb_pkg.after_dml.debug',' before insert of change rec 1 ' );
305 END IF;
306
307 -- Insert the new Disbursement change details record
308 l_row_id := NULL;
309 igf_aw_db_chg_dtls_pkg.insert_row(
310 x_rowid => l_row_id,
311 x_award_id => new_references.award_id,
312 x_disb_num => new_references.disb_num,
313 x_disb_seq_num => 1,
314 x_disb_accepted_amt => NVL(new_references.disb_accepted_amt,0),
315 x_orig_fee_amt => new_references.fee_1,
316 x_disb_net_amt => new_references.disb_net_amt,
317 x_disb_date => new_references.disb_date,
318 x_disb_activity => 'P',
319 x_disb_status => 'G',
320 x_disb_status_date => TRUNC(SYSDATE),
321 x_disb_rel_flag => NVL(new_references.hold_rel_ind,'FALSE'),
322 x_first_disb_flag => l_first_disb_flag,
323 x_interest_rebate_amt => new_references.int_rebate_amt,
324 x_disb_conf_flag => new_references.affirm_flag,
325 x_pymnt_prd_start_date => new_references.payment_prd_st_date,
326 x_note_message => NULL,
327 x_batch_id_txt => NULL,
328 x_ack_date => NULL,
329 x_booking_id_txt => NULL,
330 x_booking_date => NULL,
331 x_mode => 'R');
332 IF l_loan_type = 'DL' THEN
333 l_dl_disb_change_status := TRUE;
334 END IF;
335
336 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
337 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug',' after insert of change rec 1 ' );
338 END IF;
339
340 ELSIF (p_action = 'UPDATE') THEN
341
342 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
343 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug','p_action= ' ||p_action);
344 END IF;
345
346 OPEN upd_db_chg_dtls_cur( new_references.award_id, new_references.disb_num, 1);
347 FETCH upd_db_chg_dtls_cur INTO upd_db_chg_dtls_rec;
348 CLOSE upd_db_chg_dtls_cur;
349
350 IF upd_db_chg_dtls_rec.disb_status = 'A' THEN
351 lv_first_disb_seq_accepted := TRUE;
352 ELSE
353 lv_first_disb_seq_accepted := FALSE;
354 END IF;
355
356 -- museshad (Bug 4930323) - DRI Validation Code clean up
357 /*
358 l_change_value is a flag variable that can take one of the following values - 1,2,3.
359
360 l_change_value = 0
361 - No change in DRI/Disb amount/Disb date. Nothing needs to be done.
362
363 l_change_value = 1
364 - DRI is FALSE. Since DRI is FALSE, existing disb sequence needs to be updated, no question of insert.
365 - Disb amount/Disb date has got changed (or) DRI has changed from FALSE to TRUE.
366
367 l_change_value = 2
368 - DRI is TRUE. Insert/Update of disb sequence.
369 - Disb amount/Disb date has got changed.
370 If Disb amount has changed, chk if there exists a disb sequence (in desc order of disb seq)
371 of disb_activity type 'A' in not Accepted status. If it exists, update this disb seq,
372 else insert new disb sequence with the new disb amount.
373 Same logic holds good for Disb date change (disb_activity = 'Q')
374
375 Note: Code clean up is done only for Full-Participant. The new code doesn't look for Phase-in
379 IF l_cod_year_flag THEN
376 participant assuming that Schools can't be in Phase-in Participant. For Phase-in participant
377 l_change_value remains 0 and no change is done to the disb change records.
378 */
380 IF (NVL(new_references.hold_rel_ind, 'FALSE') = 'TRUE' AND NVL(old_references.hold_rel_ind, 'FALSE') = 'FALSE') THEN
381 -- DRI changed from FALSE to TRUE. Update disb sequence
382 l_change_value := 1;
383
384 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
385 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug','DRI has changed from FALSE to TRUE');
386 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug','Marking l_change_value= ' ||l_change_value);
387 END IF;
388 ELSE
389 IF (
390 (old_references.disb_accepted_amt <> new_references.disb_accepted_amt OR
391 NVL(old_references.fee_1,-1) <> NVL(new_references.fee_1,-1) OR
392 old_references.disb_net_amt <> new_references.disb_net_amt OR
393 NVL(old_references.hold_rel_ind, 'FALSE') <> NVL(new_references.hold_rel_ind, 'FALSE') OR
394 NVL(old_references.affirm_flag,' ') <> NVL(new_references.affirm_flag,' ') OR
395 NVL(old_references.payment_prd_st_date,TO_DATE('4712/12/31','YYYY/MM/DD')) <>
396 NVL(new_references.payment_prd_st_date,TO_DATE('4712/12/31','YYYY/MM/DD')))
397 OR
398 (old_references.disb_date <> new_references.disb_date)
399 ) THEN
400 -- Disb amt (or) Disb date changed
401
402 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
403 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug','Disb amt (or) Disb date has changed');
404 END IF;
405
406 IF ( (NVL(new_references.hold_rel_ind, 'FALSE') = 'TRUE') AND (lv_first_disb_seq_accepted) ) THEN
407 l_change_value := 2;
408
409 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
410 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug','Both DRI and lv_first_disb_seq_accepted are TRUE');
411 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug','Marking l_change_value= ' ||l_change_value);
412 END IF;
413 ELSE
414 l_change_value := 1;
415
416 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
417 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug','DRI or lv_first_disb_seq_accepted is FALSE');
418 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug','Marking l_change_value= ' ||l_change_value);
419 END IF;
420 END IF;
421 END IF; -- <<Disb amt (or) Disb date changed>>
422 END IF; -- <<DRI changed from FALSE to TRUE>>
423 END IF; -- <<l_cod_year_flag>>
424
425 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
426 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug','l_change_value= ' ||l_change_value);
427 END IF;
428 -- museshad (Bug 4930323)
429
430 IF (l_change_value = 1) THEN
431
432 -- Update IGF_AW_DB_CHG_DTLS record with disb_Seq_no = 1 with disb_status = G and other details from the record in igf_aw_awd_disb_all
433
434 -- Update the record, IGF_AW_DB_CHG_DTLS
435 igf_aw_db_chg_dtls_pkg.update_row (
436 x_rowid => upd_db_chg_dtls_rec.ROWID,
437 x_award_id => upd_db_chg_dtls_rec.award_id,
438 x_disb_num => upd_db_chg_dtls_rec.disb_num,
439 x_disb_seq_num => upd_db_chg_dtls_rec.disb_seq_num,
440 x_disb_accepted_amt => new_references.disb_accepted_amt,
441 x_orig_fee_amt => new_references.fee_1,
442 x_disb_net_amt => new_references.disb_net_amt,
443 x_disb_date => new_references.disb_date,
444 x_disb_activity => upd_db_chg_dtls_rec.disb_activity,
445 x_disb_status => 'G',
446 x_disb_status_date => TRUNC(SYSDATE),
447 x_disb_rel_flag => NVL(new_references.hold_rel_ind, 'FALSE'),
448 x_first_disb_flag => l_first_disb_flag,
449 x_interest_rebate_amt => new_references.int_rebate_amt,
450 x_disb_conf_flag => new_references.affirm_flag,
451 x_pymnt_prd_start_date => new_references.payment_prd_st_date,
452 x_note_message => upd_db_chg_dtls_rec.note_message,
453 x_batch_id_txt => upd_db_chg_dtls_rec.batch_id_txt,
454 x_ack_date => upd_db_chg_dtls_rec.ack_date,
455 x_booking_id_txt => upd_db_chg_dtls_rec.booking_id_txt,
456 x_booking_date => upd_db_chg_dtls_rec.booking_date,
457 x_mode => 'R'
458 );
459 IF l_loan_type = 'DL' THEN
460 l_dl_disb_change_status := TRUE;
461 END IF;
462 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
466 ELSIF (l_change_value = 2) THEN
463 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug',' after update of change rec disb status chg true 1 ' );
464 END IF;
465
467
468 -- For Disbursement Gross Amount Change
469 IF (old_references.disb_accepted_amt <> new_references.disb_accepted_amt OR
470 NVL(old_references.fee_1,-1) <> NVL(new_references.fee_1,-1) OR
471 old_references.disb_net_amt <> new_references.disb_net_amt OR
472 NVL(old_references.hold_rel_ind, 'FALSE') <> NVL(new_references.hold_rel_ind, 'FALSE') OR
473 NVL(old_references.affirm_flag,' ') <> NVL(new_references.affirm_flag,' ') OR
474 NVL(old_references.payment_prd_st_date,TO_DATE('4712/12/31','YYYY/MM/DD')) <>
475 NVL(new_references.payment_prd_st_date,TO_DATE('4712/12/31','YYYY/MM/DD'))) THEN
476
477 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
478 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug','Disb amount change');
479 END IF;
480
481 -- Get latest AMOUNT record to be updated.
482 OPEN cur_latest_DateAmt_for_update(new_references.award_id, new_references.disb_num, 'A');
483 FETCH cur_latest_DateAmt_for_update INTO latest_update_rec;
484 CLOSE cur_latest_DateAmt_for_update;
485
486 -- Get latest Accepted DATE record.
487 OPEN cur_latest_accepted_DateAmt(new_references.award_id, new_references.disb_num, 'Q');
488 FETCH cur_latest_accepted_DateAmt INTO latest_accepted_DateAmount_rec;
489 CLOSE cur_latest_accepted_DateAmt;
490
491 IF latest_update_rec.award_id IS NOT NULL THEN
492
493 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
494 fnd_log.string(fnd_log.level_statement,
495 'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug',
496 'Updating disb sequence ' ||latest_update_rec.disb_seq_num|| ' with the new disb amount');
497 END IF;
498
499 -- Update the record
500 igf_aw_db_chg_dtls_pkg.update_row (
501 x_rowid => latest_update_rec.ROWID,
502 x_award_id => latest_update_rec.award_id,
503 x_disb_num => latest_update_rec.disb_num,
504 x_disb_seq_num => latest_update_rec.disb_seq_num,
505 x_disb_accepted_amt => new_references.disb_accepted_amt,
506 x_orig_fee_amt => new_references.fee_1,
507 x_disb_net_amt => new_references.disb_net_amt,
508 x_disb_date => latest_accepted_DateAmount_rec.disb_date,
509 x_disb_activity => 'A',
510 x_disb_status => 'G',
511 x_disb_status_date => TRUNC(SYSDATE),
512 x_disb_rel_flag => NVL(new_references.hold_rel_ind, 'FALSE'),
513 x_first_disb_flag => l_first_disb_flag,
514 x_interest_rebate_amt => new_references.int_rebate_amt,
515 x_disb_conf_flag => new_references.affirm_flag,
516 x_pymnt_prd_start_date => new_references.payment_prd_st_date,
517 x_note_message => latest_update_rec.note_message,
518 x_batch_id_txt => latest_update_rec.batch_id_txt,
519 x_ack_date => latest_update_rec.ack_date,
520 x_booking_id_txt => latest_update_rec.booking_id_txt,
521 x_booking_date => latest_update_rec.booking_date,
522 x_mode => 'R'
523 );
524 IF l_loan_type = 'DL' THEN
525 l_dl_disb_change_status := TRUE;
526 END IF;
527 ELSE
528 -- Insert the new record
529 OPEN max_disb_seq_num_cur (new_references.award_id, new_references.disb_num);
530 FETCH max_disb_seq_num_cur INTO l_max_disb_seq_num;
531 CLOSE max_disb_seq_num_cur;
532 l_row_id := NULL;
533
534 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
535 fnd_log.string(fnd_log.level_statement,
536 'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug',
537 'Inserting new disb sequence ' ||NVL(l_max_disb_seq_num,1)|| ' with the new disb amount');
538 END IF;
539
540 igf_aw_db_chg_dtls_pkg.insert_row(
541 x_rowid => l_row_id,
542 x_award_id => new_references.award_id,
543 x_disb_num => new_references.disb_num,
544 x_disb_seq_num => NVL(l_max_disb_seq_num,1),
545 x_disb_accepted_amt => new_references.disb_accepted_amt,
546 x_orig_fee_amt => new_references.fee_1,
547 x_disb_net_amt => new_references.disb_net_amt,
548 x_disb_date => latest_accepted_DateAmount_rec.disb_date,
549 x_disb_activity => 'A',
550 x_disb_status => 'G',
551 x_disb_status_date => TRUNC(SYSDATE),
552 x_disb_rel_flag => NVL(new_references.hold_rel_ind, 'FALSE'),
553 x_first_disb_flag => l_first_disb_flag,
557 x_note_message => NULL,
554 x_interest_rebate_amt => new_references.int_rebate_amt,
555 x_disb_conf_flag => new_references.affirm_flag,
556 x_pymnt_prd_start_date => new_references.payment_prd_st_date,
558 x_batch_id_txt => NULL,
559 x_ack_date => NULL,
560 x_booking_id_txt => NULL,
561 x_booking_date => NULL,
562 x_mode => 'R'
563 );
564 IF l_loan_type = 'DL' THEN
565 l_dl_disb_change_status := TRUE;
566 END IF;
567 END IF; -- for either Update/Insert
568 END IF; -- for Disbursement Amount Change
569
570 -- For Disbursement Date Change
571 IF (old_references.disb_date <> new_references.disb_date) THEN
572
573 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
574 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug','Disb date change');
575 END IF;
576
577 -- Get latest DATE record to be updated.
578 OPEN cur_latest_DateAmt_for_update(new_references.award_id, new_references.disb_num, 'Q');
579 FETCH cur_latest_DateAmt_for_update INTO latest_update_rec;
580 CLOSE cur_latest_DateAmt_for_update;
581
582 -- Get latest Accepted AMOUNT record.
583 OPEN cur_latest_accepted_DateAmt(new_references.award_id, new_references.disb_num, 'A');
584 FETCH cur_latest_accepted_DateAmt INTO latest_accepted_DateAmount_rec;
585 CLOSE cur_latest_accepted_DateAmt;
586
587 IF latest_update_rec.award_id IS NOT NULL THEN
588
589 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
590 fnd_log.string(fnd_log.level_statement,
591 'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug',
592 'Updating disb sequence ' ||latest_update_rec.disb_seq_num|| ' with the new disb date');
593 END IF;
594
595 -- Update the record
596 igf_aw_db_chg_dtls_pkg.update_row (
597 x_rowid => latest_update_rec.ROWID,
598 x_award_id => latest_update_rec.award_id,
599 x_disb_num => latest_update_rec.disb_num,
600 x_disb_seq_num => latest_update_rec.disb_seq_num,
601 x_disb_accepted_amt => latest_accepted_DateAmount_rec.disb_accepted_amt,
602 x_orig_fee_amt => latest_accepted_DateAmount_rec.orig_fee_amt,
603 x_disb_net_amt => latest_accepted_DateAmount_rec.disb_net_amt,
604 x_disb_date => new_references.disb_date,
605 x_disb_activity => 'Q',
606 x_disb_status => 'G',
607 x_disb_status_date => TRUNC(SYSDATE),
608 x_disb_rel_flag => NVL(new_references.hold_rel_ind, 'FALSE'),
609 x_first_disb_flag => l_first_disb_flag,
610 x_interest_rebate_amt => latest_accepted_DateAmount_rec.interest_rebate_amt,
611 x_disb_conf_flag => latest_accepted_DateAmount_rec.disb_conf_flag,
612 x_pymnt_prd_start_date => latest_accepted_DateAmount_rec.pymnt_prd_start_date,
613 x_note_message => latest_update_rec.note_message,
614 x_batch_id_txt => latest_update_rec.batch_id_txt,
615 x_ack_date => latest_update_rec.ack_date,
616 x_booking_id_txt => latest_update_rec.booking_id_txt,
617 x_booking_date => latest_update_rec.booking_date,
618 x_mode => 'R'
619 );
620 IF l_loan_type = 'DL' THEN
621 l_dl_disb_change_status := TRUE;
622 END IF;
623 ELSE
624 -- Insert the new record
625 OPEN max_disb_seq_num_cur (new_references.award_id, new_references.disb_num);
626 FETCH max_disb_seq_num_cur INTO l_max_disb_seq_num;
627 CLOSE max_disb_seq_num_cur;
628 l_row_id := NULL;
629
630 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
631 fnd_log.string(fnd_log.level_statement,
632 'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug',
633 'Inserting new disb sequence ' ||NVL(l_max_disb_seq_num,1)|| ' with the new disb date');
634 END IF;
635
636 igf_aw_db_chg_dtls_pkg.insert_row(
637 x_rowid => l_row_id,
638 x_award_id => new_references.award_id,
639 x_disb_num => new_references.disb_num,
640 x_disb_seq_num => NVL(l_max_disb_seq_num,1),
641 x_disb_accepted_amt => latest_accepted_DateAmount_rec.disb_accepted_amt,
642 x_orig_fee_amt => latest_accepted_DateAmount_rec.orig_fee_amt,
643 x_disb_net_amt => latest_accepted_DateAmount_rec.disb_net_amt,
644 x_disb_date => new_references.disb_date,
645 x_disb_activity => 'Q',
646 x_disb_status => 'G',
647 x_disb_status_date => TRUNC(SYSDATE),
651 x_disb_conf_flag => latest_accepted_DateAmount_rec.disb_conf_flag,
648 x_disb_rel_flag => NVL(new_references.hold_rel_ind, 'FALSE'),
649 x_first_disb_flag => l_first_disb_flag,
650 x_interest_rebate_amt => latest_accepted_DateAmount_rec.interest_rebate_amt,
652 x_pymnt_prd_start_date => latest_accepted_DateAmount_rec.pymnt_prd_start_date,
653 x_note_message => NULL,
654 x_batch_id_txt => NULL,
655 x_ack_date => NULL,
656 x_booking_id_txt => NULL,
657 x_booking_date => NULL,
658 x_mode => 'R'
659 );
660 IF l_loan_type = 'DL' THEN
661 l_dl_disb_change_status := TRUE;
662 END IF;
663 END IF; -- for either Update/Insert
664 END IF; -- for Disbursement Date Change
665 END IF; -- End of COD-XML year or not
666 END IF; -- End of p_action
667
668 -- If Change Details record inserted/updated with status "Ready to Send"
669 -- then update loan change status or loan status. Bug #4390112
670 IF l_dl_disb_change_status = TRUE THEN
671
672 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
673 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.after_dml.debug', 'l_dl_disb_change_status = TRUE');
674 END IF;
675
676 -- Get loans record.
677 OPEN loans_cur(new_references.award_id);
678 FETCH loans_cur INTO loans_rec;
679 IF loans_cur%NOTFOUND THEN
680 CLOSE loans_cur;
681 ELSE
682
683 -- Loan Status Codes and meanings.
684 -- A -> Accepted
685 -- G -> Ready to Send
686 -- N -> Not Ready
687 -- R -> Rejected
688
689 IF loans_rec.loan_status = 'A' THEN
690 loans_rec.loan_chg_status := 'G';
691 loans_rec.loan_chg_status_date := TRUNC(SYSDATE);
692 ELSIF loans_rec.loan_status IN ('N', 'R') THEN
693 loans_rec.loan_status := 'G';
694 loans_rec.loan_status_date := TRUNC(SYSDATE);
695 END IF;
696 igf_sl_loans_pkg.update_row(
697 x_rowid => loans_rec.rowid,
698 x_loan_id => loans_rec.loan_id,
699 x_award_id => loans_rec.award_id,
700 x_seq_num => loans_rec.seq_num,
701 x_loan_number => loans_rec.loan_number,
702 x_loan_per_begin_date => loans_rec.loan_per_begin_date,
703 x_loan_per_end_date => loans_rec.loan_per_end_date,
704 x_loan_status => loans_rec.loan_status,
705 x_loan_status_date => loans_rec.loan_status_date,
706 x_loan_chg_status => loans_rec.loan_chg_status,
707 x_loan_chg_status_date => loans_rec.loan_chg_status_date,
708 x_active => loans_rec.active,
709 x_active_date => loans_rec.active_date,
710 x_borw_detrm_code => loans_rec.borw_detrm_code,
711 x_mode => 'R',
712 x_legacy_record_flag => loans_rec.legacy_record_flag,
713 x_external_loan_id_txt => loans_rec.external_loan_id_txt,
714 x_called_from => NULL
715 );
716 CLOSE loans_cur;
717 END IF;
718 END IF;
719 END IF; -- only for DL and PELL
720
721 END after_dml;
722
723 PROCEDURE set_column_values (
724 p_action IN VARCHAR2,
725 x_rowid IN VARCHAR2 DEFAULT NULL,
726 x_award_id IN NUMBER DEFAULT NULL,
727 x_disb_num IN NUMBER DEFAULT NULL,
728 x_tp_cal_type IN VARCHAR2 DEFAULT NULL,
729 x_tp_sequence_number IN NUMBER DEFAULT NULL,
730 x_disb_gross_amt IN NUMBER DEFAULT NULL,
731 x_fee_1 IN NUMBER DEFAULT NULL,
732 x_fee_2 IN NUMBER DEFAULT NULL,
733 x_disb_net_amt IN NUMBER DEFAULT NULL,
734 x_disb_date IN DATE DEFAULT NULL,
735 x_trans_type IN VARCHAR2 DEFAULT NULL,
736 x_elig_status IN VARCHAR2 DEFAULT NULL,
737 x_elig_status_date IN DATE DEFAULT NULL,
738 x_affirm_flag IN VARCHAR2 DEFAULT NULL,
739 x_hold_rel_ind IN VARCHAR2 DEFAULT NULL,
740 x_manual_hold_ind IN VARCHAR2 DEFAULT NULL,
741 x_disb_status IN VARCHAR2 DEFAULT NULL,
742 x_disb_status_date IN DATE DEFAULT NULL,
743 x_late_disb_ind IN VARCHAR2 DEFAULT NULL,
744 x_fund_dist_mthd IN VARCHAR2 DEFAULT NULL,
745 x_prev_reported_ind IN VARCHAR2 DEFAULT NULL,
746 x_fund_release_date IN DATE DEFAULT NULL,
747 x_fund_status IN VARCHAR2 DEFAULT NULL,
748 x_fund_status_date IN DATE DEFAULT NULL,
752 x_ld_cal_type IN VARCHAR2 DEFAULT NULL,
749 x_fee_paid_1 IN NUMBER DEFAULT NULL,
750 x_fee_paid_2 IN NUMBER DEFAULT NULL,
751 x_cheque_number IN VARCHAR2 DEFAULT NULL,
753 x_ld_sequence_number IN NUMBER DEFAULT NULL,
754 x_disb_accepted_amt IN NUMBER DEFAULT NULL,
755 x_disb_paid_amt IN NUMBER DEFAULT NULL,
756 x_rvsn_id IN NUMBER DEFAULT NULL,
757 x_int_rebate_amt IN NUMBER DEFAULT NULL,
758 x_force_disb IN VARCHAR2 DEFAULT NULL,
759 x_min_credit_pts IN NUMBER DEFAULT NULL,
760 x_disb_exp_dt IN DATE DEFAULT NULL,
761 x_verf_enfr_dt IN DATE DEFAULT NULL,
762 x_fee_class IN VARCHAR2 DEFAULT NULL,
763 x_show_on_bill IN VARCHAR2 DEFAULT NULL,
764 x_attendance_type_code IN VARCHAR2 DEFAULT NULL,
765 x_base_attendance_type_code IN VARCHAR2 DEFAULT NULL,
766 x_creation_date IN DATE DEFAULT NULL,
767 x_created_by IN NUMBER DEFAULT NULL,
768 x_last_update_date IN DATE DEFAULT NULL,
769 x_last_updated_by IN NUMBER DEFAULT NULL,
770 x_last_update_login IN NUMBER DEFAULT NULL,
771 x_payment_prd_st_date IN DATE DEFAULT NULL,
772 x_change_type_code IN VARCHAR2 DEFAULT NULL,
773 x_fund_return_mthd_code IN VARCHAR2 DEFAULT NULL,
774 x_direct_to_borr_flag IN VARCHAR2 DEFAULT NULL
775
776
777 ) AS
778 /*
779 || Created By : adhawan
780 || Created On : 16-NOV-2000
781 || Purpose : Initialises the Old and New references for the columns of the table.
782 || Known limitations, enhancements or remarks :
783 || Change History :
784 || Who When What
785 ||
786 || bannamal 29-Sep-2004 FA 149 3416863 cod xml changes for pell and direct loan
787 || addded a new column
788 || veramach 3-NOV-2003 FA 125 Multiple Distr Methods
789 || Added attendance_type_code to the signature
790 || (reverse chronological order - newest change first)
791 */
792
793 CURSOR cur_old_ref_values IS
794 SELECT *
795 FROM IGF_AW_AWD_DISB_ALL
796 WHERE rowid = x_rowid;
797
798 BEGIN
799
800 l_rowid := x_rowid;
801
802 -- Code for setting the Old and New Reference Values.
803 -- Populate Old Values.
804 OPEN cur_old_ref_values;
805 FETCH cur_old_ref_values INTO old_references;
806 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
807 CLOSE cur_old_ref_values;
808 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
809 igs_ge_msg_stack.add;
810 app_exception.raise_exception;
811 RETURN;
812 END IF;
813 CLOSE cur_old_ref_values;
814
815 -- Populate New Values.
816 new_references.award_id := x_award_id;
817 new_references.disb_num := x_disb_num;
818 new_references.tp_cal_type := x_tp_cal_type;
819 new_references.tp_sequence_number := x_tp_sequence_number;
820 new_references.disb_gross_amt := x_disb_gross_amt;
821 new_references.fee_1 := x_fee_1;
822 new_references.fee_2 := x_fee_2;
823 new_references.disb_net_amt := x_disb_net_amt;
824 new_references.disb_date := x_disb_date;
825 new_references.trans_type := x_trans_type;
826 new_references.elig_status := x_elig_status;
827 new_references.elig_status_date := x_elig_status_date;
828 new_references.affirm_flag := x_affirm_flag;
829 new_references.hold_rel_ind := x_hold_rel_ind;
830 new_references.manual_hold_ind := x_manual_hold_ind;
831 new_references.disb_status := x_disb_status;
832 new_references.disb_status_date := x_disb_status_date;
833 new_references.late_disb_ind := x_late_disb_ind;
834 new_references.fund_dist_mthd := x_fund_dist_mthd;
835 new_references.prev_reported_ind := x_prev_reported_ind;
836 new_references.fund_release_date := x_fund_release_date;
837 new_references.fund_status := x_fund_status;
838 new_references.fund_status_date := x_fund_status_date;
839 new_references.fee_paid_1 := x_fee_paid_1;
840 new_references.fee_paid_2 := x_fee_paid_2;
841 new_references.cheque_number := x_cheque_number;
842 new_references.ld_cal_type := x_ld_cal_type;
843 new_references.ld_sequence_number := x_ld_sequence_number;
844 new_references.disb_accepted_amt := x_disb_accepted_amt;
848 new_references.force_disb := x_force_disb;
845 new_references.disb_paid_amt := x_disb_paid_amt;
846 new_references.rvsn_id := x_rvsn_id;
847 new_references.int_rebate_amt := x_int_rebate_amt;
849 new_references.min_credit_pts := x_min_credit_pts;
850 new_references.disb_exp_dt := x_disb_exp_dt;
851 new_references.verf_enfr_dt := x_verf_enfr_dt;
852 new_references.fee_class := x_fee_class;
853 new_references.show_on_bill := x_show_on_bill;
854 new_references.attendance_type_code := x_attendance_type_code;
855 new_references.base_attendance_type_code := x_base_attendance_type_code;
856 new_references.payment_prd_st_date := x_payment_prd_st_date;
857 new_references.change_type_code := x_change_type_code;
858 new_references.fund_return_mthd_code := x_fund_return_mthd_code;
859 new_references.direct_to_borr_flag := x_direct_to_borr_flag;
860
861 IF (p_action = 'UPDATE') THEN
862 new_references.creation_date := old_references.creation_date;
863 new_references.created_by := old_references.created_by;
864 ELSE
865 new_references.creation_date := x_creation_date;
866 new_references.created_by := x_created_by;
867 END IF;
868
869 new_references.last_update_date := x_last_update_date;
870 new_references.last_updated_by := x_last_updated_by;
871 new_references.last_update_login := x_last_update_login;
872
873 END set_column_values;
874
875 PROCEDURE BeforeRowInsertUpdateDelete1(
876 p_rowid IN VARCHAR2,
877 p_inserting IN BOOLEAN ,
878 p_updating IN BOOLEAN ,
879 p_deleting IN BOOLEAN
880 ) AS
881 /*-----------------------------------------------------------------
882 || Created By : Sanil Madathil
883 || Created On : 24-Nov-2004
884 || Purpose :
885 || Known limitations, enhancements or remarks :
886 || Change History :
887 || Who When What
888 || (reverse chronological order - newest change first)
889 --------------------------------------------------------------------*/
890 CURSOR c_aw_awd_disb (cp_rowid ROWID) IS
891 SELECT award_id
892 ,disb_num
893 FROM igf_aw_awd_disb
894 WHERE row_id = cp_rowid;
895
896 CURSOR c_igf_sl_lorlar(cp_n_award_id igf_aw_award_all.award_id%TYPE) IS
897 SELECT lar.loan_number
898 ,lar.loan_status
899 ,lor.prc_type_code
900 ,lor.cl_rec_status
901 FROM igf_sl_lor_all lor
902 ,igf_sl_loans_all lar
903 WHERE lor.loan_id = lar.loan_id
904 AND lar.award_id = cp_n_award_id;
905
906 rec_c_igf_sl_lorlar c_igf_sl_lorlar%ROWTYPE;
907
908 CURSOR c_sl_clchsn_dtls (
909 cp_v_loan_number igf_sl_loans_all.loan_number%TYPE,
910 cp_new_disb_num igf_aw_awd_disb_all.disb_num%TYPE
911 ) IS
912 SELECT chdt.ROWID row_id,chdt.*
913 FROM igf_sl_clchsn_dtls chdt
914 WHERE chdt.loan_number_txt = cp_v_loan_number
915 AND chdt.disbursement_number = cp_new_disb_num
916 AND chdt.status_code IN ('R','N','D')
917 AND chdt.change_field_code = 'DISB_NUM'
918 AND chdt.change_code_txt = 'D'
919 AND chdt.change_record_type_txt = '09';
920
921 rec_c_sl_clchsn_dtls c_sl_clchsn_dtls%ROWTYPE;
922
923 l_v_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE;
924 l_v_message_name fnd_new_messages.message_name%TYPE;
925 l_b_return_status BOOLEAN;
926 l_n_award_id igf_aw_award_all.award_id%TYPE;
927 l_n_disb_num igf_aw_awd_disb_all.disb_num%TYPE;
928 l_v_loan_number igf_sl_loans_all.loan_number%TYPE;
929 l_n_cl_version igf_sl_cl_setup_all.cl_version%TYPE;
930 l_c_cl_rec_status igf_sl_lor_all.cl_rec_status%TYPE;
931 l_v_prc_type_code igf_sl_lor_all.prc_type_code%TYPE;
932 l_v_loan_status igf_sl_loans_all.loan_status%TYPE;
933 BEGIN
934 IF p_deleting THEN
935 OPEN c_aw_awd_disb (cp_rowid => p_rowid);
936 FETCH c_aw_awd_disb INTO l_n_award_id,l_n_disb_num;
937 CLOSE c_aw_awd_disb ;
938 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
939 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', 'Action = delete ' );
940 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', 'inside BeforeRowInsertUpdateDelete1 ' );
941 END IF;
942 l_v_fed_fund_code := igf_sl_gen.get_fed_fund_code (p_n_award_id => l_n_award_id,
943 p_v_message_name => l_v_message_name
944 );
945 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
946 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', 'l_v_fed_fund_code : '||l_v_fed_fund_code );
947 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', 'g_v_called_from : '||g_v_called_from );
948 END IF;
949 IF l_v_message_name IS NOT NULL THEN
953 END IF;
950 fnd_message.set_name ('IGS',l_v_message_name);
951 igs_ge_msg_stack.add;
952 app_exception.raise_exception;
954 IF g_v_called_from NOT IN ('IGFSL27B','IGFAW038') THEN
955 RETURN;
956 END IF;
957 IF l_v_fed_fund_code NOT IN ('FLS','FLU','FLP','ALT','GPLUSFL') THEN
958 RETURN;
959 END IF;
960 -- get the processing type code, loan record status, loan status and loan number for the input award id
961 OPEN c_igf_sl_lorlar (cp_n_award_id => l_n_award_id);
962 FETCH c_igf_sl_lorlar INTO rec_c_igf_sl_lorlar;
963 CLOSE c_igf_sl_lorlar;
964
965 l_v_loan_number := rec_c_igf_sl_lorlar.loan_number;
966 l_v_loan_status := rec_c_igf_sl_lorlar.loan_status;
967 l_v_prc_type_code := rec_c_igf_sl_lorlar.prc_type_code;
968 l_c_cl_rec_status := rec_c_igf_sl_lorlar.cl_rec_status;
969 -- get the loan version for the input award id
970 l_n_cl_version := igf_sl_award.get_loan_cl_version(p_n_award_id => l_n_award_id);
971 -- Change Record would be created only if
972 -- The version = CommonLine Release 4 Version Loan,
973 -- Loan Status = Accepted
974 -- Loan Record Status is Guaranteed or Accepted
975 -- Processing Type Code is GP or GO
976 -- information is different from the latest guaranteed response for the loan
977 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
978 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', 'l_n_cl_version : '||l_n_cl_version );
979 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', 'l_v_loan_status : '||l_v_loan_status );
980 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', 'l_v_prc_type_code : '||l_v_prc_type_code );
981 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', 'l_c_cl_rec_status : '||l_c_cl_rec_status );
982 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', 'l_n_disb_num : '||l_n_disb_num );
983 END IF;
984 IF (l_n_cl_version = 'RELEASE-4' AND
985 l_v_loan_status = 'A' AND
986 l_v_prc_type_code IN ('GO','GP') AND
987 l_c_cl_rec_status IN ('B','G'))
988 THEN
989 OPEN c_sl_clchsn_dtls (
990 cp_v_loan_number => l_v_loan_number,
991 cp_new_disb_num => l_n_disb_num
992 );
993 FETCH c_sl_clchsn_dtls INTO rec_c_sl_clchsn_dtls;
994 IF c_sl_clchsn_dtls%FOUND THEN
995 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
996 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', ' Change record to be deleted ');
997 END IF;
998 igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => rec_c_sl_clchsn_dtls.row_id);
999 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1000 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.BeforeRowInsertUpdateDelete1 ', ' Change record deleted successfully ');
1001 END IF;
1002 END IF;
1003 CLOSE c_sl_clchsn_dtls;
1004 END IF;
1005 END IF;
1006 END BeforeRowInsertUpdateDelete1;
1007
1008 PROCEDURE AfterRowInsertUpdateDelete1(
1009 p_inserting IN BOOLEAN ,
1010 p_updating IN BOOLEAN ,
1011 p_deleting IN BOOLEAN
1012 ) AS
1013 /*-----------------------------------------------------------------
1014 || Created By : Sanil Madathil
1015 || Created On : 13-Oct-2004
1016 || Purpose :
1017 || Known limitations, enhancements or remarks :
1018 || Change History :
1019 || Who When What
1020 || (reverse chronological order - newest change first)
1021 --------------------------------------------------------------------*/
1022 CURSOR c_igf_sl_lorlar(cp_n_award_id igf_aw_award_all.award_id%TYPE) IS
1023 SELECT lar.loan_number
1024 ,lar.loan_status
1025 ,lor.prc_type_code
1026 ,lor.cl_rec_status
1027 FROM igf_sl_lor_all lor
1028 ,igf_sl_loans_all lar
1029 WHERE lor.loan_id = lar.loan_id
1030 AND lar.award_id = cp_n_award_id;
1031
1032 rec_c_igf_sl_lorlar c_igf_sl_lorlar%ROWTYPE;
1033
1034 l_v_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE;
1035 l_v_message_name fnd_new_messages.message_name%TYPE;
1036 l_b_return_status BOOLEAN;
1037 l_n_clchgsnd_id igf_sl_clchsn_dtls.clchgsnd_id%TYPE;
1038 l_v_rowid ROWID;
1039 l_n_award_id igf_aw_award_all.award_id%TYPE;
1040 l_v_loan_number igf_sl_loans_all.loan_number%TYPE;
1041 l_n_cl_version igf_sl_cl_setup_all.cl_version%TYPE;
1042 l_c_cl_rec_status igf_sl_lor_all.cl_rec_status%TYPE;
1043 l_v_prc_type_code igf_sl_lor_all.prc_type_code%TYPE;
1044 l_v_loan_status igf_sl_loans_all.loan_status%TYPE;
1045
1046 CURSOR c_sl_clchsn_dtls (
1047 cp_v_loan_number igf_sl_loans_all.loan_number%TYPE,
1048 cp_new_disb_num igf_aw_awd_disb_all.disb_num%TYPE
1049 ) IS
1050 SELECT chdt.ROWID row_id,chdt.*
1051 FROM igf_sl_clchsn_dtls chdt
1052 WHERE chdt.loan_number_txt = cp_v_loan_number
1053 AND chdt.disbursement_number = cp_new_disb_num
1054 AND chdt.status_code IN ('R','N','D')
1055 AND chdt.change_field_code = 'DISB_NUM'
1056 AND chdt.change_code_txt = 'D'
1060
1057 AND chdt.change_record_type_txt = '09';
1058
1059 rec_c_sl_clchsn_dtls c_sl_clchsn_dtls%ROWTYPE;
1061 l_d_message_tokens igf_sl_cl_chg_prc.token_tab%TYPE;
1062
1063 BEGIN
1064 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1065 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', 'inside AfterRowInsertUpdateDelete1 ' );
1066 END IF;
1067 l_v_fed_fund_code := igf_sl_gen.get_fed_fund_code (p_n_award_id => new_references.award_id,
1068 p_v_message_name => l_v_message_name
1069 );
1070 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1071 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', ' fund code = '||l_v_fed_fund_code );
1072 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', ' called from = '||g_v_called_from );
1073 END IF;
1074 IF l_v_message_name IS NOT NULL THEN
1075 fnd_message.set_name ('IGS',l_v_message_name);
1076 igs_ge_msg_stack.add;
1077 app_exception.raise_exception;
1078 END IF;
1079 IF g_v_called_from NOT IN ('IGFSL27B','IGFAW038','IGFAW016') THEN
1080 RETURN;
1081 END IF;
1082 IF l_v_fed_fund_code NOT IN ('FLS','FLU','FLP','ALT','GPLUSFL') THEN
1083 RETURN;
1084 END IF;
1085 IF p_updating THEN
1086 IF ((new_references.disb_date <> old_references.disb_date) OR
1087 (new_references.disb_accepted_amT <> old_references.disb_accepted_amt) OR
1088 (NVL(new_references.hold_rel_ind, 'FALSE') <> NVL(old_references.hold_rel_ind, 'FALSE')) OR
1089 (NVL(new_references.change_type_code, '*') <> NVL(old_references.change_type_code, '*'))
1090 ) THEN
1091 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1092 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', 'invoking igf_sl_cl_create_chg.create_disb_chg_rec. ' );
1093 END IF;
1094 -- invoke the procedure to create loan cancellation change record in igf_sl_clchsn_dtls table
1095 igf_sl_cl_create_chg.create_disb_chg_rec(
1096 p_new_disb_rec => new_references,
1097 p_old_disb_rec => old_references,
1098 p_b_return_status => l_b_return_status,
1099 p_v_message_name => l_v_message_name
1100 );
1101 -- if the above call out returns false and error message is returned,
1102 -- add the message to the error stack and error message test should be displayed
1103 -- in the calling form
1104 IF (NOT (l_b_return_status) AND l_v_message_name IS NOT NULL )THEN
1105 -- substring of the out bound parameter l_v_message_name is carried
1106 -- out since it can expect either IGS OR IGF message
1107 fnd_message.set_name(SUBSTR(l_v_message_name,1,3),l_v_message_name);
1108 igf_sl_cl_chg_prc.parse_tokens(
1109 p_t_message_tokens => igf_sl_cl_chg_prc.g_message_tokens);
1110 /*
1111 FOR token_counter IN igf_sl_cl_chg_prc.g_message_tokens.FIRST..igf_sl_cl_chg_prc.g_message_tokens.LAST LOOP
1112 fnd_message.set_token(igf_sl_cl_chg_prc.g_message_tokens(token_counter).token_name, igf_sl_cl_chg_prc.g_message_tokens(token_counter).token_value);
1113 END LOOP;
1114 */
1115 igs_ge_msg_stack.add;
1116 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1117 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', 'Call to igf_sl_cl_create_chg.create_disb_chg_rec returned error '|| l_v_message_name);
1118 END IF;
1119 app_exception.raise_exception;
1120 END IF;
1121 END IF;
1122 l_n_award_id := new_references.award_id;
1123 -- get the processing type code, loan record status, loan status and loan number for the input award id
1124 OPEN c_igf_sl_lorlar (cp_n_award_id => l_n_award_id);
1125 FETCH c_igf_sl_lorlar INTO rec_c_igf_sl_lorlar;
1126 CLOSE c_igf_sl_lorlar;
1127
1128 l_v_loan_number := rec_c_igf_sl_lorlar.loan_number;
1129 l_v_loan_status := rec_c_igf_sl_lorlar.loan_status;
1130 l_v_prc_type_code := rec_c_igf_sl_lorlar.prc_type_code;
1131 l_c_cl_rec_status := rec_c_igf_sl_lorlar.cl_rec_status;
1132 -- get the loan version for the input award id
1133 l_n_cl_version := igf_sl_award.get_loan_cl_version(p_n_award_id => l_n_award_id);
1134 -- Change Record would be created only if
1135 -- The version = CommonLine Release 4 Version Loan,
1136 -- Loan Status = Accepted
1137 -- Loan Record Status is Guaranteed or Accepted
1138 -- Processing Type Code is GP or GO
1139 -- information is different from the latest guaranteed response for the loan
1140 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1141 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', 'l_n_cl_version : '||l_n_cl_version );
1142 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', 'l_v_loan_status : '||l_v_loan_status );
1143 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', 'l_v_prc_type_code : '||l_v_prc_type_code );
1144 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', 'l_c_cl_rec_status : '||l_c_cl_rec_status );
1145 END IF;
1146 IF (l_n_cl_version = 'RELEASE-4' AND
1147 l_v_loan_status = 'A' AND
1148 l_v_prc_type_code IN ('GO','GP') AND
1149 l_c_cl_rec_status IN ('B','G')) THEN
1150 OPEN c_sl_clchsn_dtls (
1154 FETCH c_sl_clchsn_dtls INTO rec_c_sl_clchsn_dtls;
1151 cp_v_loan_number => l_v_loan_number,
1152 cp_new_disb_num => new_references.disb_num
1153 );
1155 IF c_sl_clchsn_dtls%FOUND THEN
1156 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1157 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', 'invoking igf_sl_clchsn_dtls_pkg.update_row to update @9 record of change_code_txt = D ');
1158 END IF;
1159 igf_sl_clchsn_dtls_pkg.update_row (
1160 x_rowid => rec_c_sl_clchsn_dtls.row_id,
1161 x_clchgsnd_id => rec_c_sl_clchsn_dtls.clchgsnd_id ,
1162 x_award_id => rec_c_sl_clchsn_dtls.award_id ,
1163 x_loan_number_txt => rec_c_sl_clchsn_dtls.loan_number_txt ,
1164 x_cl_version_code => rec_c_sl_clchsn_dtls.cl_version_code ,
1165 x_change_field_code => rec_c_sl_clchsn_dtls.change_field_code ,
1166 x_change_record_type_txt => rec_c_sl_clchsn_dtls.change_record_type_txt ,
1167 x_change_code_txt => rec_c_sl_clchsn_dtls.change_code_txt ,
1168 x_status_code => 'R' ,
1169 x_status_date => rec_c_sl_clchsn_dtls.status_date ,
1170 x_response_status_code => rec_c_sl_clchsn_dtls.response_status_code ,
1171 x_old_value_txt => rec_c_sl_clchsn_dtls.old_value_txt ,
1172 x_new_value_txt => new_references.hold_rel_ind ,
1173 x_old_date => rec_c_sl_clchsn_dtls.old_date ,
1174 x_new_date => new_references.disb_date ,
1175 x_old_amt => rec_c_sl_clchsn_dtls.old_amt ,
1176 x_new_amt => new_references.disb_accepted_amt ,
1177 x_disbursement_number => rec_c_sl_clchsn_dtls.disbursement_number ,
1178 x_disbursement_date => rec_c_sl_clchsn_dtls.disbursement_date ,
1179 x_change_issue_code => rec_c_sl_clchsn_dtls.change_issue_code ,
1180 x_disbursement_cancel_date => rec_c_sl_clchsn_dtls.disbursement_cancel_date ,
1181 x_disbursement_cancel_amt => rec_c_sl_clchsn_dtls.disbursement_cancel_amt ,
1182 x_disbursement_revised_amt => rec_c_sl_clchsn_dtls.disbursement_revised_amt ,
1183 x_disbursement_revised_date => rec_c_sl_clchsn_dtls.disbursement_revised_date ,
1184 x_disbursement_reissue_code => rec_c_sl_clchsn_dtls.disbursement_reissue_code ,
1185 x_disbursement_reinst_code => rec_c_sl_clchsn_dtls.disbursement_reinst_code ,
1186 x_disbursement_return_amt => rec_c_sl_clchsn_dtls.disbursement_return_amt ,
1187 x_disbursement_return_date => rec_c_sl_clchsn_dtls.disbursement_return_date ,
1188 x_disbursement_return_code => rec_c_sl_clchsn_dtls.disbursement_return_code ,
1189 x_post_with_disb_return_amt => rec_c_sl_clchsn_dtls.post_with_disb_return_amt ,
1190 x_post_with_disb_return_date => rec_c_sl_clchsn_dtls.post_with_disb_return_date ,
1191 x_post_with_disb_return_code => rec_c_sl_clchsn_dtls.post_with_disb_return_code ,
1192 x_prev_with_disb_return_amt => rec_c_sl_clchsn_dtls.prev_with_disb_return_amt ,
1193 x_prev_with_disb_return_date => rec_c_sl_clchsn_dtls.prev_with_disb_return_date ,
1194 x_school_use_txt => rec_c_sl_clchsn_dtls.school_use_txt ,
1195 x_lender_use_txt => rec_c_sl_clchsn_dtls.lender_use_txt ,
1196 x_guarantor_use_txt => rec_c_sl_clchsn_dtls.guarantor_use_txt ,
1197 x_validation_edit_txt => NULL ,
1198 x_send_record_txt => rec_c_sl_clchsn_dtls.send_record_txt
1199 );
1200 -- invoke validation edits to validate the change record. The validation checks if
1201 -- all the required fields are populated or not for a change record
1202 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1203 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', ' validating the Change record for Change send id: ' ||rec_c_sl_clchsn_dtls.clchgsnd_id);
1204 END IF;
1205 igf_sl_cl_chg_prc.validate_chg (
1206 p_n_clchgsnd_id => rec_c_sl_clchsn_dtls.clchgsnd_id,
1207 p_b_return_status => l_b_return_status,
1208 p_v_message_name => l_v_message_name,
1209 p_t_message_tokens => l_d_message_tokens
1210 );
1211 IF NOT(l_b_return_status) THEN
1212 -- substring of the out bound parameter l_v_message_name is carried
1213 -- out since it can expect either IGS OR IGF message
1214 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1215 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', ' validation of the Change record failed for Change send id: ' ||rec_c_sl_clchsn_dtls.clchgsnd_id);
1216 END IF;
1217 fnd_message.set_name(SUBSTR(l_v_message_name,1,3),l_v_message_name);
1218 igf_sl_cl_chg_prc.parse_tokens(
1219 p_t_message_tokens => l_d_message_tokens);
1220 /*
1221 FOR token_counter IN l_d_message_tokens.FIRST..l_d_message_tokens.LAST LOOP
1222 fnd_message.set_token(l_d_message_tokens(token_counter).token_name, l_d_message_tokens(token_counter).token_value);
1223 END LOOP;
1227 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', ' Change send id: ' ||rec_c_sl_clchsn_dtls.clchgsnd_id);
1224 */
1225 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1226 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send ');
1228 END IF;
1229 igf_sl_clchsn_dtls_pkg.update_row (
1230 x_rowid => rec_c_sl_clchsn_dtls.row_id,
1231 x_clchgsnd_id => rec_c_sl_clchsn_dtls.clchgsnd_id ,
1232 x_award_id => rec_c_sl_clchsn_dtls.award_id ,
1233 x_loan_number_txt => rec_c_sl_clchsn_dtls.loan_number_txt ,
1234 x_cl_version_code => rec_c_sl_clchsn_dtls.cl_version_code ,
1235 x_change_field_code => rec_c_sl_clchsn_dtls.change_field_code ,
1236 x_change_record_type_txt => rec_c_sl_clchsn_dtls.change_record_type_txt ,
1237 x_change_code_txt => rec_c_sl_clchsn_dtls.change_code_txt ,
1238 x_status_code => 'N' ,
1239 x_status_date => rec_c_sl_clchsn_dtls.status_date ,
1240 x_response_status_code => rec_c_sl_clchsn_dtls.response_status_code ,
1241 x_old_value_txt => rec_c_sl_clchsn_dtls.old_value_txt ,
1242 x_new_value_txt => new_references.hold_rel_ind ,
1243 x_old_date => rec_c_sl_clchsn_dtls.old_date ,
1244 x_new_date => new_references.disb_date ,
1245 x_old_amt => rec_c_sl_clchsn_dtls.old_amt ,
1246 x_new_amt => new_references.disb_accepted_amt ,
1247 x_disbursement_number => rec_c_sl_clchsn_dtls.disbursement_number ,
1248 x_disbursement_date => rec_c_sl_clchsn_dtls.disbursement_date ,
1249 x_change_issue_code => rec_c_sl_clchsn_dtls.change_issue_code ,
1250 x_disbursement_cancel_date => rec_c_sl_clchsn_dtls.disbursement_cancel_date ,
1251 x_disbursement_cancel_amt => rec_c_sl_clchsn_dtls.disbursement_cancel_amt ,
1252 x_disbursement_revised_amt => rec_c_sl_clchsn_dtls.disbursement_revised_amt ,
1253 x_disbursement_revised_date => rec_c_sl_clchsn_dtls.disbursement_revised_date ,
1254 x_disbursement_reissue_code => rec_c_sl_clchsn_dtls.disbursement_reissue_code ,
1255 x_disbursement_reinst_code => rec_c_sl_clchsn_dtls.disbursement_reinst_code ,
1256 x_disbursement_return_amt => rec_c_sl_clchsn_dtls.disbursement_return_amt ,
1257 x_disbursement_return_date => rec_c_sl_clchsn_dtls.disbursement_return_date ,
1258 x_disbursement_return_code => rec_c_sl_clchsn_dtls.disbursement_return_code ,
1259 x_post_with_disb_return_amt => rec_c_sl_clchsn_dtls.post_with_disb_return_amt ,
1260 x_post_with_disb_return_date => rec_c_sl_clchsn_dtls.post_with_disb_return_date ,
1261 x_post_with_disb_return_code => rec_c_sl_clchsn_dtls.post_with_disb_return_code ,
1262 x_prev_with_disb_return_amt => rec_c_sl_clchsn_dtls.prev_with_disb_return_amt ,
1263 x_prev_with_disb_return_date => rec_c_sl_clchsn_dtls.prev_with_disb_return_date ,
1264 x_school_use_txt => rec_c_sl_clchsn_dtls.school_use_txt ,
1265 x_lender_use_txt => rec_c_sl_clchsn_dtls.lender_use_txt ,
1266 x_guarantor_use_txt => rec_c_sl_clchsn_dtls.guarantor_use_txt ,
1267 x_validation_edit_txt => fnd_message.get ,
1268 x_send_record_txt => rec_c_sl_clchsn_dtls.send_record_txt
1269 );
1270 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1271 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', ' updated the status to Not Ready to Send for Change send id: ' ||rec_c_sl_clchsn_dtls.clchgsnd_id);
1272 END IF;
1273 END IF;
1274 END IF;
1275 CLOSE c_sl_clchsn_dtls;
1276 END IF;
1277 ELSIF p_inserting THEN
1278 l_n_award_id := new_references.award_id;
1279 -- get the processing type code, loan record status, loan status and loan number for the input award id
1280 OPEN c_igf_sl_lorlar (cp_n_award_id => l_n_award_id);
1281 FETCH c_igf_sl_lorlar INTO rec_c_igf_sl_lorlar;
1282 CLOSE c_igf_sl_lorlar;
1283 l_v_loan_number := rec_c_igf_sl_lorlar.loan_number;
1284 l_v_loan_status := rec_c_igf_sl_lorlar.loan_status;
1285 l_v_prc_type_code := rec_c_igf_sl_lorlar.prc_type_code;
1286 l_c_cl_rec_status := rec_c_igf_sl_lorlar.cl_rec_status;
1287 -- get the loan version for the input award id
1288 l_n_cl_version := igf_sl_award.get_loan_cl_version(p_n_award_id => l_n_award_id);
1289 -- Change Record would be created only if
1290 -- The version = CommonLine Release 4 Version Loan,
1291 -- Loan Status = Accepted
1292 -- Loan Record Status is Guaranteed or Accepted
1293 -- Processing Type Code is GP or GO
1294 -- information is different from the latest guaranteed response for the loan
1295 IF (l_n_cl_version = 'RELEASE-4' AND
1296 l_v_loan_status = 'A' AND
1297 l_v_prc_type_code IN ('GO','GP') AND
1301 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1298 l_c_cl_rec_status IN ('B','G')) THEN
1299 l_v_rowid := NULL;
1300 l_n_clchgsnd_id := NULL;
1302 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', 'invoking igf_sl_clchsn_dtls_pkg.insert_row to insert @9 record of change_code_txt = D ');
1303 END IF;
1304 igf_sl_clchsn_dtls_pkg.insert_row (
1305 x_rowid => l_v_rowid ,
1306 x_clchgsnd_id => l_n_clchgsnd_id ,
1307 x_award_id => l_n_award_id ,
1308 x_loan_number_txt => l_v_loan_number ,
1309 x_cl_version_code => l_n_cl_version ,
1310 x_change_field_code => 'DISB_NUM' ,
1311 x_change_record_type_txt => '09' ,
1312 x_change_code_txt => 'D' ,
1313 x_status_code => 'R' ,
1314 x_status_date => TRUNC(SYSDATE) ,
1315 x_response_status_code => NULL ,
1316 x_old_value_txt => new_references.hold_rel_ind ,
1317 x_new_value_txt => new_references.hold_rel_ind ,
1318 x_old_date => new_references.disb_date ,
1319 x_new_date => new_references.disb_date ,
1320 x_old_amt => 0,
1321 x_new_amt => new_references.disb_accepted_amt,
1322 x_disbursement_number => new_references.disb_num ,
1323 x_disbursement_date => new_references.disb_date ,
1324 x_change_issue_code => 'PRE_DISB' ,
1325 x_disbursement_cancel_date => NULL ,
1326 x_disbursement_cancel_amt => NULL ,
1327 x_disbursement_revised_amt => NULL ,
1328 x_disbursement_revised_date => NULL ,
1329 x_disbursement_reissue_code => NULL ,
1330 x_disbursement_reinst_code => 'N' ,
1331 x_disbursement_return_amt => NULL ,
1332 x_disbursement_return_date => NULL ,
1333 x_disbursement_return_code => NULL ,
1334 x_post_with_disb_return_amt => NULL ,
1335 x_post_with_disb_return_date => NULL ,
1336 x_post_with_disb_return_code => NULL ,
1337 x_prev_with_disb_return_amt => NULL ,
1338 x_prev_with_disb_return_date => NULL ,
1339 x_school_use_txt => NULL ,
1340 x_lender_use_txt => NULL ,
1341 x_guarantor_use_txt => NULL ,
1342 x_validation_edit_txt => NULL ,
1343 x_send_record_txt => NULL
1344 );
1345 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1346 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', 'Inserted @9 record of change_code_txt = D ');
1347 END IF;
1348 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1349 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', ' validating the Change record for Change send id: ' ||l_n_clchgsnd_id);
1350 END IF;
1351 igf_sl_cl_chg_prc.validate_chg (
1352 p_n_clchgsnd_id => l_n_clchgsnd_id,
1353 p_b_return_status => l_b_return_status,
1354 p_v_message_name => l_v_message_name,
1355 p_t_message_tokens => l_d_message_tokens
1356 );
1357 IF NOT(l_b_return_status) THEN
1358 fnd_message.set_name(SUBSTR(l_v_message_name,1,3),l_v_message_name);
1359 igf_sl_cl_chg_prc.parse_tokens(
1360 p_t_message_tokens => l_d_message_tokens);
1361 /*
1362 FOR token_counter IN l_d_message_tokens.FIRST..l_d_message_tokens.LAST LOOP
1363 fnd_message.set_token(l_d_message_tokens(token_counter).token_name, l_d_message_tokens(token_counter).token_value);
1364 END LOOP;
1365 */
1366 igs_ge_msg_stack.add;
1367 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1368 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.AfterRowInsertUpdateDelete1 ', ' validation of the Change record failed for Change send id: ' ||l_n_clchgsnd_id);
1369 END IF;
1370 app_exception.raise_exception;
1371 END IF;
1372 END IF;
1373 END IF;
1374 END AfterRowInsertUpdateDelete1;
1375
1376
1377 PROCEDURE check_constraints (
1378 column_name IN VARCHAR2 DEFAULT NULL,
1379 column_value IN VARCHAR2 DEFAULT NULL
1380 ) AS
1381 /*
1382 || Created By : prchandr
1383 || Created On : 01-JUN-2001
1384 || Purpose : Handles the Check Constraint logic for the the columns.
1385 || Known limitations, enhancements or remarks :
1386 || Change History :
1387 || Who When What
1388 || (reverse chronological order - newest change first)
1389 */
1390 BEGIN
1391
1392 IF (column_name IS NULL) THEN
1393 NULL;
1394 ELSIF (UPPER(column_name) = 'TRANS_TYPE') THEN
1395 new_references.trans_type := column_value;
1396 ELSIF (UPPER(column_name) = 'ELIG_STATUS') THEN
1397 new_references.elig_status := column_value;
1398 ELSIF (UPPER(column_name) = 'AFFIRM_FLAG') THEN
1399 new_references.affirm_flag := column_value;
1400 ELSIF (UPPER(column_name) = 'HOLD_REL_IND') THEN
1401 new_references.hold_rel_ind := column_value;
1402 ELSIF (UPPER(column_name) = 'MANUAL_HOLD_IND') THEN
1403 new_references.manual_hold_ind := column_value;
1404 ELSIF (UPPER(column_name) = 'LATE_DISB_IND') THEN
1405 new_references.late_disb_ind := column_value;
1406 ELSIF (UPPER(column_name) = 'FUND_DIST_MTHD') THEN
1407 new_references.fund_dist_mthd := column_value;
1408 ELSIF (UPPER(column_name) = 'PREV_REPORTED_IND') THEN
1409 new_references.prev_reported_ind := column_value;
1410 ELSIF (UPPER(column_name) = 'FUND_STATUS') THEN
1411 new_references.fund_status := column_value;
1412 ELSIF (UPPER(column_name) = 'DIRECT_TO_BORR_FLAG') THEN
1413 new_references.direct_to_borr_flag := column_value;
1414 END IF;
1418 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1415
1416 IF (UPPER(column_name) = 'TRANS_TYPE' OR column_name IS NULL) THEN
1417 IF igf_aw_gen.lookup_desc('IGF_DB_TRANS_TYPE',new_references.trans_type) IS NULL THEN
1419 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.check_constraints.debug','TBH: Check Constraints Fail: Value ' || new_references.trans_type || ' for TRANS_TYPE is invalid ');
1420 END IF;
1421 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1422 igs_ge_msg_stack.add;
1423 app_exception.raise_exception;
1424 END IF;
1425 END IF;
1426
1427 IF (UPPER(column_name) = 'ELIG_STATUS' OR column_name IS NULL) THEN
1428 IF new_references.elig_status IS NOT NULL THEN
1429 IF igf_aw_gen.lookup_desc('IGF_DB_ELIG_STATUS',new_references.elig_status) IS NULL THEN
1430
1431 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1432 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.check_constraints.debug','TBH: Check Constraints Fail: Value ' || new_references.elig_status || ' for ELIG_STAUS is invalid');
1433 END IF;
1434 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1435 igs_ge_msg_stack.add;
1436 app_exception.raise_exception;
1437 END IF;
1438 END IF;
1439 END IF;
1440
1441 IF (UPPER(column_name) = 'AFFIRM_FLAG' OR column_name IS NULL) THEN
1442 IF new_references.affirm_flag IS NOT NULL THEN
1443 IF igf_aw_gen.lookup_desc('IGF_AP_YES_NO',new_references.affirm_flag) IS NULL THEN
1444 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1445 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.check_constraints.debug','TBH: Check Constraints Fail: Value ' || new_references.affirm_flag || ' for AFFIRM_FLAG is invalid ');
1446 END IF;
1447 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1448 igs_ge_msg_stack.add;
1449 app_exception.raise_exception;
1450 END IF;
1451 END IF;
1452 END IF;
1453
1454 --
1455 -- Bug 2983181
1456 --
1457 IF (UPPER(column_name) = 'HOLD_REL_IND' OR column_name IS NULL) THEN
1458 IF new_references.hold_rel_ind IS NOT NULL THEN
1459 IF igf_aw_gen.lookup_desc('IGF_SL_CL_HOLD_REL_IND_TF',new_references.hold_rel_ind) IS NULL THEN
1460 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1461 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.check_constraints.debug','TBH: Check Constraints Fail: Value ' || new_references.hold_rel_ind || ' for HOLD_REL_IND is invalid ');
1462 END IF;
1463 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1464 igs_ge_msg_stack.add;
1465 app_exception.raise_exception;
1466 END IF;
1467 END IF;
1468 END IF;
1469
1470 IF (UPPER(column_name) = 'FUND_DIST_MTHD' OR column_name IS NULL) THEN
1471 IF new_references.fund_dist_mthd IS NOT NULL THEN
1472 IF igf_aw_gen.lookup_desc('IGF_SL_CL_DB_FUND_DISB_METH',new_references.fund_dist_mthd) IS NULL THEN
1473 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1474 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.check_constraints.debug','TBH: Check Constraints Fail: Value ' || new_references.fund_dist_mthd || ' for FUND_DIST_MTHD is invalid ');
1475 END IF;
1476 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1477 igs_ge_msg_stack.add;
1478 app_exception.raise_exception;
1479 END IF;
1480 END IF;
1481 END IF;
1482
1483 IF (UPPER(column_name) = 'PREV_REPORTED_IND' OR column_name IS NULL) THEN
1484 IF new_references.prev_reported_ind IS NOT NULL THEN
1485 IF igf_aw_gen.lookup_desc('IGF_AP_YES_NO',new_references.prev_reported_ind) IS NULL THEN
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_awd_disb_pkg.check_constraints.debug','TBH: Check Constraints Fail: Value ' || new_references.prev_reported_ind || ' for PREV_REPORTED_IND is invalid ');
1488 END IF;
1489 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1490 igs_ge_msg_stack.add;
1491 app_exception.raise_exception;
1492 END IF;
1493 END IF;
1494 END IF;
1495
1496 IF (UPPER(column_name) = 'FUND_STATUS' OR column_name IS NULL) THEN
1497 IF new_references.fund_status IS NOT NULL THEN
1498 IF igf_aw_gen.lookup_desc('IGF_SL_CL_DB_FUND_STATUS',new_references.fund_status) IS NULL THEN
1499 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1500 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.check_constraints.debug','TBH: Check Constraints Fail: Value ' || new_references.fund_status || ' for FUND_STATUS is invalid ');
1501 END IF;
1502 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1503 igs_ge_msg_stack.add;
1504 app_exception.raise_exception;
1505 END IF;
1506 END IF;
1507 END IF;
1508
1509 -- Added a value of 'Y' w.r to Disbursement and Sponsership Build
1510 -- Bug 2154941.
1511 -- This is required as IGF_DB_DISB_HOLDS_PKG calls this Table Handler
1512 -- for Update Row.
1513 IF (UPPER(column_name) = 'MANUAL_HOLD_IND' OR column_name IS NULL) THEN
1514 IF NOT (new_references.manual_hold_ind IN ('N','Y')) THEN
1515 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1516 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.check_constraints.debug','TBH: Check Constraints Fail: Value ' || new_references.manual_hold_ind || ' for MANUAL_HOLD_IND is invalid ');
1517 END IF;
1521 END IF;
1518 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1519 igs_ge_msg_stack.add;
1520 app_exception.raise_exception;
1522 END IF;
1523
1524 IF (UPPER(column_name) = 'LATE_DISB_IND' OR column_name IS NULL) THEN
1525 IF NOT (new_references.late_disb_ind IN ('Y', 'N')) THEN
1526 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1527 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.check_constraints.debug','TBH: Check Constraints Fail: Value ' || new_references.late_disb_ind || ' for LATE_DISB_IND is invalid ');
1528 END IF;
1529 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1530 igs_ge_msg_stack.add;
1531 app_exception.raise_exception;
1532 END IF;
1533 END IF;
1534
1535 IF (UPPER(column_name) = 'DIRECT_TO_BORR_FLAG' OR column_name IS NULL) THEN
1536 IF NOT (new_references.direct_to_borr_flag IN ('Y', 'N')) THEN
1537 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1538 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_awd_disb_pkg.check_constraints.debug','TBH: Check Constraints Fail: Value ' || new_references.direct_to_borr_flag || ' for DIRECT_TO_BORR_FLAG is invalid ');
1539 END IF;
1540 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1541 igs_ge_msg_stack.add;
1542 app_exception.raise_exception;
1543 END IF;
1544 END IF;
1545
1546 END check_constraints;
1547
1548
1549 PROCEDURE check_parent_existance AS
1550 /*
1551 || Created By : adhawan
1552 || Created On : 16-NOV-2000
1553 || Purpose : Checks for the existance of Parent records.
1554 || Known limitations, enhancements or remarks :
1555 || Change History :
1556 || Who When What
1557 || (reverse chronological order - newest change first)
1558 */
1559 BEGIN
1560
1561 IF (((old_references.award_id = new_references.award_id)) OR
1562 ((new_references.award_id IS NULL))) THEN
1563 NULL;
1564 ELSIF NOT igf_aw_award_pkg.get_pk_for_validation (
1565 new_references.award_id
1566 ) THEN
1567 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
1568 igs_ge_msg_stack.add;
1569 app_exception.raise_exception;
1570 END IF;
1571
1572 IF (((old_references.rvsn_id = new_references.rvsn_id)) OR
1573 ((new_references.rvsn_id IS NULL))) THEN
1574 NULL;
1575 ELSIF NOT igf_aw_awd_rvsn_rsn_pkg.get_pk_for_validation (
1576 new_references.rvsn_id
1577 ) THEN
1578 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
1579 igs_ge_msg_stack.add;
1580 app_exception.raise_exception;
1581 END IF;
1582
1583 IF (((old_references.tp_cal_type = new_references.tp_cal_type) AND
1584 (old_references.tp_sequence_number = new_references.tp_sequence_number)) OR
1585 ((new_references.tp_cal_type IS NULL) OR
1586 (new_references.tp_sequence_number IS NULL))) THEN
1587 NULL;
1588 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
1589 new_references.tp_cal_type,
1590 new_references.tp_sequence_number
1591 ) THEN
1592 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
1593 igs_ge_msg_stack.add;
1594 app_exception.raise_exception;
1595 END IF;
1596
1597 IF (((old_references.ld_cal_type = new_references.ld_cal_type) AND
1598 (old_references.ld_sequence_number = new_references.ld_sequence_number)) OR
1599 ((new_references.ld_cal_type IS NULL) OR
1600 (new_references.ld_sequence_number IS NULL))) THEN
1601 NULL;
1602 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
1603 new_references.ld_cal_type,
1604 new_references.ld_sequence_number
1605 ) THEN
1606 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
1607 igs_ge_msg_stack.add;
1608 app_exception.raise_exception;
1609 END IF;
1610
1611
1612 END check_parent_existance;
1613
1614 PROCEDURE check_child_existance IS
1615 /*
1616 || Created By : mesriniv
1617 || Created On : 08-JAN-2002
1618 || Purpose : Checks for the existance of Child records.
1619 || Known limitations, enhancements or remarks :
1620 || Change History :
1621 || Bug Id : 2154941
1622 || Disbursement and Sponsership Build Jul 2002 (CCR004)
1623 || Who When What
1624 || vchappid 10-Apr-2002 Enh# 2293676, New Child Table(IGS_FI_BILL_PLN_CRD) added
1625 || mesriniv 08-JAN-2002 To check for child Hold Records
1626 || (reverse chronological order - newest change first)
1627 */
1628 BEGIN
1629
1630 igf_db_disb_holds_pkg.get_fk_igf_aw_awd_disb(
1631 old_references.award_id,
1632 old_references.disb_num
1633 );
1634
1635 igf_db_awd_disb_dtl_pkg.get_fk_igf_aw_awd_disb(
1636 old_references.award_id,
1637 old_references.disb_num );
1638
1639 igs_fi_bill_pln_crd_pkg.get_fk_igf_aw_awd_disb (
1640 old_references.award_id,
1641 old_references.disb_num );
1642
1643 igf_aw_db_chg_dtls_pkg.get_fk_igf_aw_awd_disb(
1644 old_references.award_id,
1645 old_references.disb_num);
1646
1647 END check_child_existance;
1648
1649
1650 FUNCTION get_pk_for_validation (
1651 x_award_id IN NUMBER,
1652 x_disb_num IN NUMBER
1653 ) RETURN BOOLEAN AS
1654 /*
1655 || Created By : adhawan
1656 || Created On : 16-NOV-2000
1657 || Purpose : Validates the Primary Key of the table.
1658 || Known limitations, enhancements or remarks :
1662 */
1659 || Change History :
1660 || Who When What
1661 || (reverse chronological order - newest change first)
1663 CURSOR cur_rowid IS
1664 SELECT rowid
1665 FROM igf_aw_awd_disb_all
1666 WHERE award_id = x_award_id
1667 AND disb_num = x_disb_num
1668 FOR UPDATE NOWAIT;
1669
1670 lv_rowid cur_rowid%RowType;
1671
1672 BEGIN
1673
1674 OPEN cur_rowid;
1675 FETCH cur_rowid INTO lv_rowid;
1676 IF (cur_rowid%FOUND) THEN
1677 CLOSE cur_rowid;
1678 RETURN(TRUE);
1679 ELSE
1680 CLOSE cur_rowid;
1681 RETURN(FALSE);
1682 END IF;
1683
1684 END get_pk_for_validation;
1685
1686
1687 PROCEDURE get_fk_igf_aw_award (
1688 x_award_id IN NUMBER
1689 ) AS
1690 /*
1691 || Created By : adhawan
1692 || Created On : 16-NOV-2000
1693 || Purpose : Validates the Foreign Keys for the table.
1694 || Known limitations, enhancements or remarks :
1695 || Change History :
1696 || Who When What
1697 || (reverse chronological order - newest change first)
1698 */
1699 CURSOR cur_rowid IS
1700 SELECT rowid
1701 FROM igf_aw_awd_disb_all
1702 WHERE ((award_id = x_award_id));
1703
1704 lv_rowid cur_rowid%RowType;
1705
1706 BEGIN
1707
1708 OPEN cur_rowid;
1709 FETCH cur_rowid INTO lv_rowid;
1710 IF (cur_rowid%FOUND) THEN
1711 CLOSE cur_rowid;
1712 fnd_message.set_name ('IGF', 'IGF_AW_ADISB_AWD_FK');
1713 igs_ge_msg_stack.add;
1714 app_exception.raise_exception;
1715 RETURN;
1716 END IF;
1717 CLOSE cur_rowid;
1718
1719 END get_fk_igf_aw_award;
1720
1721 PROCEDURE get_fk_igf_aw_awd_rvsn_rsn (
1722 x_rvsn_id IN NUMBER
1723 ) AS
1724 /*
1725 || Created By : prchandr
1726 || Created On : 01-JUN-2001
1727 || Purpose : Validates the Foreign Keys for the table.
1728 || Known limitations, enhancements or remarks :
1729 || Change History :
1730 || Who When What
1731 || (reverse chronological order - newest change first)
1732 */
1733 CURSOR cur_rowid IS
1734 SELECT rowid
1735 FROM igf_aw_awd_disb_all
1736 WHERE ((rvsn_id = x_rvsn_id));
1737
1738 lv_rowid cur_rowid%RowType;
1739
1740 BEGIN
1741
1742 OPEN cur_rowid;
1743 FETCH cur_rowid INTO lv_rowid;
1744 IF (cur_rowid%FOUND) THEN
1745 CLOSE cur_rowid;
1746 fnd_message.set_name ('IGF', 'IGF_AW_ADISB_RVSN_FK');
1747 igs_ge_msg_stack.add;
1748 app_exception.raise_exception;
1749 RETURN;
1750 END IF;
1751 CLOSE cur_rowid;
1752
1753 END get_fk_igf_aw_awd_rvsn_rsn;
1754
1755
1756 PROCEDURE get_fk_igs_ca_inst (
1757 x_cal_type IN VARCHAR2,
1758 x_sequence_number IN NUMBER
1759 ) AS
1760 /*
1761 || Created By : prchandr
1762 || Created On : 01-JUN-2001
1763 || Purpose : Validates the Foreign Keys for the table.
1764 || Known limitations, enhancements or remarks :
1765 || Change History :
1766 || Who When What
1767 || (reverse chronological order - newest change first)
1768 */
1769 CURSOR cur_rowid IS
1770 SELECT rowid
1771 FROM igf_aw_awd_disb_all
1772 WHERE ((tp_cal_type = x_cal_type) AND
1773 (tp_sequence_number = x_sequence_number))
1774 OR ((ld_cal_type = x_cal_type) AND
1775 (ld_sequence_number = x_sequence_number));
1776
1777 lv_rowid cur_rowid%RowType;
1778
1779 BEGIN
1780
1781 OPEN cur_rowid;
1782 FETCH cur_rowid INTO lv_rowid;
1783 IF (cur_rowid%FOUND) THEN
1784 CLOSE cur_rowid;
1785 fnd_message.set_name ('IGF', 'IGF_AW_ADISB_CI_FK');
1786 igs_ge_msg_stack.add;
1787 app_exception.raise_exception;
1788 RETURN;
1789 END IF;
1790 CLOSE cur_rowid;
1791
1792 END get_fk_igs_ca_inst;
1793
1794 PROCEDURE get_fk_igs_lookups_view (
1795 x_fee_class IN VARCHAR2
1796 ) AS
1797 /*
1798 || Created By : prchandr
1799 || Created On : 01-JUN-2001
1800 || Purpose : Validates the Foreign Keys for the table.
1801 || Known limitations, enhancements or remarks :
1802 || Change History :
1803 || Who When What
1804 || (reverse chronological order - newest change first)
1805 */
1806 CURSOR cur_rowid IS
1807 SELECT rowid
1808 FROM igf_aw_awd_disb_all
1809 WHERE fee_class = x_fee_class;
1810
1811 lv_rowid cur_rowid%RowType;
1812
1813 BEGIN
1814
1815 OPEN cur_rowid;
1816 FETCH cur_rowid INTO lv_rowid;
1817 IF (cur_rowid%FOUND) THEN
1818 CLOSE cur_rowid;
1819 fnd_message.set_name ('IGF', 'IGF_AW_ADISB_LKUP_FK');
1820 igs_ge_msg_stack.add;
1821 app_exception.raise_exception;
1822 RETURN;
1823 END IF;
1824 CLOSE cur_rowid;
1825
1826 END get_fk_igs_lookups_view;
1827
1828
1829 PROCEDURE before_dml (
1830 p_action IN VARCHAR2,
1831 x_rowid IN VARCHAR2 DEFAULT NULL,
1832 x_award_id IN NUMBER DEFAULT NULL,
1833 x_disb_num IN NUMBER DEFAULT NULL,
1837 x_fee_1 IN NUMBER DEFAULT NULL,
1834 x_tp_cal_type IN VARCHAR2 DEFAULT NULL,
1835 x_tp_sequence_number IN NUMBER DEFAULT NULL,
1836 x_disb_gross_amt IN NUMBER DEFAULT NULL,
1838 x_fee_2 IN NUMBER DEFAULT NULL,
1839 x_disb_net_amt IN NUMBER DEFAULT NULL,
1840 x_disb_date IN DATE DEFAULT NULL,
1841 x_trans_type IN VARCHAR2 DEFAULT NULL,
1842 x_elig_status IN VARCHAR2 DEFAULT NULL,
1843 x_elig_status_date IN DATE DEFAULT NULL,
1844 x_affirm_flag IN VARCHAR2 DEFAULT NULL,
1845 x_hold_rel_ind IN VARCHAR2 DEFAULT NULL,
1846 x_manual_hold_ind IN VARCHAR2 DEFAULT NULL,
1847 x_disb_status IN VARCHAR2 DEFAULT NULL,
1848 x_disb_status_date IN DATE DEFAULT NULL,
1849 x_late_disb_ind IN VARCHAR2 DEFAULT NULL,
1850 x_fund_dist_mthd IN VARCHAR2 DEFAULT NULL,
1851 x_prev_reported_ind IN VARCHAR2 DEFAULT NULL,
1852 x_fund_release_date IN DATE DEFAULT NULL,
1853 x_fund_status IN VARCHAR2 DEFAULT NULL,
1854 x_fund_status_date IN DATE DEFAULT NULL,
1855 x_fee_paid_1 IN NUMBER DEFAULT NULL,
1856 x_fee_paid_2 IN NUMBER DEFAULT NULL,
1857 x_cheque_number IN VARCHAR2 DEFAULT NULL,
1858 x_ld_cal_type IN VARCHAR2 DEFAULT NULL,
1859 x_ld_sequence_number IN NUMBER DEFAULT NULL,
1860 x_disb_accepted_amt IN NUMBER DEFAULT NULL,
1861 x_disb_paid_amt IN NUMBER DEFAULT NULL,
1862 x_rvsn_id IN NUMBER DEFAULT NULL,
1863 x_int_rebate_amt IN NUMBER DEFAULT NULL,
1864 x_force_disb IN VARCHAR2 DEFAULT NULL,
1865 x_min_credit_pts IN NUMBER DEFAULT NULL,
1866 x_disb_exp_dt IN DATE DEFAULT NULL,
1867 x_verf_enfr_dt IN DATE DEFAULT NULL,
1868 x_fee_class IN VARCHAR2 DEFAULT NULL,
1869 x_show_on_bill IN VARCHAR2 DEFAULT NULL,
1870 x_attendance_type_code IN VARCHAR2 DEFAULT NULL,
1871 x_base_attendance_type_code IN VARCHAR2 DEFAULT NULL,
1872 x_creation_date IN DATE DEFAULT NULL,
1873 x_created_by IN NUMBER DEFAULT NULL,
1874 x_last_update_date IN DATE DEFAULT NULL,
1875 x_last_updated_by IN NUMBER DEFAULT NULL,
1876 x_last_update_login IN NUMBER DEFAULT NULL,
1877 x_payment_prd_st_date IN DATE DEFAULT NULL,
1878 x_change_type_code IN VARCHAR2 DEFAULT NULL,
1879 x_fund_return_mthd_code IN VARCHAR2 DEFAULT NULL,
1880 x_direct_to_borr_flag IN VARCHAR2 DEFAULT NULL
1881
1882 ) AS
1883 /*
1884 || Created By : adhawan
1885 || Created On : 16-NOV-2000
1886 || Purpose : Initialises the columns, Checks Constraints, Calls the
1887 || Trigger Handlers for the table, before any DML operation.
1888 || Known limitations, enhancements or remarks :
1889 || Change History :
1890 || Who When What
1891 || bannamal 29-Sep-2004 FA 149 3416863 cod xml changes for pell and direct loan
1892 || addded a new column
1893 || veramach 3-NOV-2003 FA 125 Multiple Distr Methods
1894 || Added attendance_type_code to the signature
1895 || (reverse chronological order - newest change first)
1896 */
1897 BEGIN
1898
1899 set_column_values (
1900 p_action,
1901 x_rowid,
1902 x_award_id,
1903 x_disb_num,
1904 x_tp_cal_type,
1905 x_tp_sequence_number,
1906 x_disb_gross_amt,
1907 x_fee_1,
1908 x_fee_2,
1909 x_disb_net_amt,
1910 x_disb_date,
1911 x_trans_type,
1912 x_elig_status,
1913 x_elig_status_date,
1914 x_affirm_flag,
1915 x_hold_rel_ind,
1916 x_manual_hold_ind,
1917 x_disb_status,
1918 x_disb_status_date,
1919 x_late_disb_ind,
1920 x_fund_dist_mthd,
1921 x_prev_reported_ind,
1922 x_fund_release_date,
1923 x_fund_status,
1924 x_fund_status_date,
1925 x_fee_paid_1,
1926 x_fee_paid_2,
1927 x_cheque_number,
1928 x_ld_cal_type,
1929 x_ld_sequence_number,
1930 x_disb_accepted_amt,
1931 x_disb_paid_amt,
1932 x_rvsn_id,
1933 x_int_rebate_amt,
1934 x_force_disb,
1935 x_min_credit_pts,
1936 x_disb_exp_dt,
1937 x_verf_enfr_dt,
1938 x_fee_class,
1939 x_show_on_bill,
1940 x_attendance_type_code,
1941 x_base_attendance_type_code,
1942 x_creation_date,
1943 x_created_by,
1944 x_last_update_date,
1945 x_last_updated_by,
1946 x_last_update_login,
1947 x_payment_prd_st_date,
1948 x_change_type_code,
1949 x_fund_return_mthd_code,
1950 x_direct_to_borr_flag
1951
1952 );
1953
1954 IF (p_action = 'INSERT') THEN
1955 -- Call all the procedures related to Before Insert.
1959 )
1956 IF ( get_pk_for_validation(
1957 new_references.award_id,
1958 new_references.disb_num
1960 ) THEN
1961 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
1962 igs_ge_msg_stack.add;
1963 app_exception.raise_exception;
1964 END IF;
1965 check_constraints;
1966 check_parent_existance;
1967
1968 ELSIF (p_action = 'UPDATE') THEN
1969 -- Call all the procedures related to Before Update.
1970 check_constraints;
1971 check_parent_existance;
1972 ELSIF (p_action = 'VALIDATE_INSERT') THEN
1973 -- Call all the procedures related to Before Insert.
1974 IF ( get_pk_for_validation (
1975 new_references.award_id,
1976 new_references.disb_num
1977 )
1978 ) THEN
1979 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
1980 igs_ge_msg_stack.add;
1981 app_exception.raise_exception;
1982 END IF;
1983 check_constraints;
1984 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
1985 check_constraints;
1986 --
1987 -- This Check has been added as part of the Disbursement and Sponsership Build Jul 2202
1988 -- Holds Check ,its a child to Award Disbursement
1989 -- FACR004
1990 --
1991 ELSIF p_action IN ('DELETE','VALIDATE_DELETE') THEN
1992 check_child_existance;
1993 END IF;
1994
1995 END before_dml;
1996
1997
1998 PROCEDURE insert_row (
1999 x_rowid IN OUT NOCOPY VARCHAR2,
2000 x_award_id IN NUMBER,
2001 x_disb_num IN NUMBER,
2002 x_tp_cal_type IN VARCHAR2,
2003 x_tp_sequence_number IN NUMBER,
2004 x_disb_gross_amt IN NUMBER,
2005 x_fee_1 IN NUMBER,
2006 x_fee_2 IN NUMBER,
2007 x_disb_net_amt IN NUMBER,
2008 x_disb_date IN DATE,
2009 x_trans_type IN VARCHAR2,
2010 x_elig_status IN VARCHAR2,
2011 x_elig_status_date IN DATE,
2012 x_affirm_flag IN VARCHAR2,
2013 x_hold_rel_ind IN VARCHAR2,
2014 x_manual_hold_ind IN VARCHAR2,
2015 x_disb_status IN VARCHAR2,
2016 x_disb_status_date IN DATE,
2017 x_late_disb_ind IN VARCHAR2,
2018 x_fund_dist_mthd IN VARCHAR2,
2019 x_prev_reported_ind IN VARCHAR2,
2020 x_fund_release_date IN DATE,
2021 x_fund_status IN VARCHAR2,
2022 x_fund_status_date IN DATE,
2023 x_fee_paid_1 IN NUMBER,
2024 x_fee_paid_2 IN NUMBER,
2025 x_cheque_number IN VARCHAR2,
2026 x_ld_cal_type IN VARCHAR2,
2027 x_ld_sequence_number IN NUMBER,
2028 x_disb_accepted_amt IN NUMBER,
2029 x_disb_paid_amt IN NUMBER,
2030 x_rvsn_id IN NUMBER,
2031 x_int_rebate_amt IN NUMBER,
2032 x_force_disb IN VARCHAR2,
2033 x_min_credit_pts IN NUMBER,
2034 x_disb_exp_dt IN DATE,
2035 x_verf_enfr_dt IN DATE,
2036 x_fee_class IN VARCHAR2,
2037 x_show_on_bill IN VARCHAR2,
2038 x_mode IN VARCHAR2,
2039 x_attendance_type_code IN VARCHAR2,
2040 x_base_attendance_type_code IN VARCHAR2,
2041 x_payment_prd_st_date IN DATE,
2042 x_change_type_code IN VARCHAR2,
2043 x_fund_return_mthd_code IN VARCHAR2,
2044 x_called_from IN VARCHAR2,
2045 x_direct_to_borr_flag IN VARCHAR2
2046
2047 ) AS
2048 /*
2049 || Created By : adhawan
2050 || Created On : 16-NOV-2000
2051 || Purpose : Handles the INSERT DML logic for the table.
2052 || Known limitations, enhancements or remarks :
2053 || Change History :
2054 || Who When What
2055 || bannamal 29-Sep-2004 FA 149 3416863 cod xml changes for pell and direct loan
2056 || addded a new column
2057 || veramach 3-NOV-2003 FA 125 Multiple Distr Methods
2058 || Added attendance_type_code to the signature
2059 || (reverse chronological order - newest change first)
2060 */
2061 CURSOR c IS
2062 SELECT rowid
2063 FROM igf_aw_awd_disb_all
2064 WHERE award_id = x_award_id
2065 AND disb_num = x_disb_num;
2066
2067 x_last_update_date DATE;
2068 x_last_updated_by NUMBER;
2069 x_last_update_login NUMBER;
2070 x_request_id NUMBER;
2071 x_program_id NUMBER;
2072 x_program_application_id NUMBER;
2073 x_program_update_date DATE;
2074 l_org_id igf_aw_awd_disb_all.org_id%TYPE;
2075
2076 BEGIN
2077 l_org_id := igf_aw_gen.get_org_id;
2078
2079 x_last_update_date := SYSDATE;
2080 IF (x_mode = 'I') THEN
2081 x_last_updated_by := 1;
2082 x_last_update_login := 0;
2083 ELSIF (x_mode = 'R') THEN
2084 x_last_updated_by := fnd_global.user_id;
2088 x_last_update_login := fnd_global.login_id;
2085 IF (x_last_updated_by IS NULL) THEN
2086 x_last_updated_by := -1;
2087 END IF;
2089 IF (x_last_update_login IS NULL) THEN
2090 x_last_update_login := -1;
2091 END IF;
2092 x_request_id := fnd_global.conc_request_id;
2093 x_program_id := fnd_global.conc_program_id;
2094 x_program_application_id := fnd_global.prog_appl_id;
2095
2096 IF (x_request_id = -1) THEN
2097 x_request_id := NULL;
2098 x_program_id := NULL;
2099 x_program_application_id := NULL;
2100 x_program_update_date := NULL;
2101 ELSE
2102 x_program_update_date := SYSDATE;
2103 END IF;
2104 ELSE
2105 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
2106 igs_ge_msg_stack.add;
2107 app_exception.raise_exception;
2108 END IF;
2109
2110 before_dml(
2111 p_action => 'INSERT',
2112 x_rowid => x_rowid,
2113 x_award_id => x_award_id,
2114 x_disb_num => x_disb_num,
2115 x_tp_cal_type => x_tp_cal_type,
2116 x_tp_sequence_number => x_tp_sequence_number,
2117 x_disb_gross_amt => x_disb_gross_amt,
2118 x_fee_1 => x_fee_1,
2119 x_fee_2 => x_fee_2,
2120 x_disb_net_amt => x_disb_net_amt,
2121 x_disb_date => x_disb_date,
2122 x_trans_type => x_trans_type,
2123 x_elig_status => x_elig_status,
2124 x_elig_status_date => x_elig_status_date,
2125 x_affirm_flag => x_affirm_flag,
2126 x_hold_rel_ind => x_hold_rel_ind,
2127 x_manual_hold_ind => x_manual_hold_ind,
2128 x_disb_status => x_disb_status,
2129 x_disb_status_date => x_disb_status_date,
2130 x_late_disb_ind => x_late_disb_ind,
2131 x_fund_dist_mthd => x_fund_dist_mthd,
2132 x_prev_reported_ind => x_prev_reported_ind,
2133 x_fund_release_date => x_fund_release_date,
2134 x_fund_status => NVL(x_fund_status,'N'),
2135 x_fund_status_date => x_fund_status_date,
2136 x_fee_paid_1 => x_fee_paid_1,
2137 x_fee_paid_2 => x_fee_paid_2,
2138 x_cheque_number => x_cheque_number,
2139 x_ld_cal_type => x_ld_cal_type,
2140 x_ld_sequence_number => x_ld_sequence_number,
2141 x_disb_accepted_amt => x_disb_accepted_amt,
2142 x_disb_paid_amt => x_disb_paid_amt,
2143 x_rvsn_id => x_rvsn_id,
2144 x_int_rebate_amt => x_int_rebate_amt,
2145 x_force_disb => x_force_disb,
2146 x_min_credit_pts => x_min_credit_pts,
2147 x_disb_exp_dt => x_disb_exp_dt,
2148 x_verf_enfr_dt => x_verf_enfr_dt,
2149 x_fee_class => x_fee_class,
2150 x_show_on_bill => x_show_on_bill,
2151 x_attendance_type_code => x_attendance_type_code,
2152 x_base_attendance_type_code => x_base_attendance_type_code,
2153 x_creation_date => x_last_update_date,
2154 x_created_by => x_last_updated_by,
2155 x_last_update_date => x_last_update_date,
2156 x_last_updated_by => x_last_updated_by,
2157 x_last_update_login => x_last_update_login,
2158 x_payment_prd_st_date => x_payment_prd_st_date,
2159 x_change_type_code => x_change_type_code,
2160 x_fund_return_mthd_code => x_fund_return_mthd_code,
2161 x_direct_to_borr_flag => x_direct_to_borr_flag
2162 );
2163
2164
2165 INSERT INTO igf_aw_awd_disb_all(
2166 award_id,
2167 disb_num,
2168 tp_cal_type,
2169 tp_sequence_number,
2170 disb_gross_amt,
2171 fee_1,
2172 fee_2,
2173 disb_net_amt,
2174 disb_date,
2175 trans_type,
2176 elig_status,
2177 elig_status_date,
2178 affirm_flag,
2179 hold_rel_ind,
2180 manual_hold_ind,
2181 disb_status,
2182 disb_status_date,
2183 late_disb_ind,
2184 fund_dist_mthd,
2185 prev_reported_ind,
2186 fund_release_date,
2187 fund_status,
2188 fund_status_date,
2189 fee_paid_1,
2190 fee_paid_2,
2191 cheque_number,
2192 ld_cal_type,
2193 ld_sequence_number,
2194 disb_accepted_amt,
2195 disb_paid_amt,
2196 rvsn_id,
2197 int_rebate_amt,
2198 force_disb,
2199 min_credit_pts,
2200 disb_exp_dt,
2201 verf_enfr_dt,
2202 fee_class,
2203 show_on_bill,
2204 attendance_type_code,
2205 base_attendance_type_code,
2206 creation_date,
2207 created_by,
2208 last_update_date,
2209 last_updated_by,
2210 last_update_login,
2211 request_id,
2212 program_id,
2213 program_application_id,
2214 program_update_date,
2215 org_id,
2216 payment_prd_st_date,
2217 change_type_code,
2218 fund_return_mthd_code,
2219 direct_to_borr_flag
2220
2224 new_references.tp_cal_type,
2221 ) VALUES (
2222 new_references.award_id,
2223 new_references.disb_num,
2225 new_references.tp_sequence_number,
2226 new_references.disb_gross_amt,
2227 new_references.fee_1,
2228 new_references.fee_2,
2229 new_references.disb_net_amt,
2230 new_references.disb_date,
2231 new_references.trans_type,
2232 new_references.elig_status,
2233 new_references.elig_status_date,
2234 new_references.affirm_flag,
2235 NVL(new_references.hold_rel_ind, 'FALSE'),
2236 new_references.manual_hold_ind,
2237 new_references.disb_status,
2238 new_references.disb_status_date,
2239 new_references.late_disb_ind,
2240 new_references.fund_dist_mthd,
2241 new_references.prev_reported_ind,
2242 new_references.fund_release_date,
2243 NVL(new_references.fund_status,'N'),
2244 new_references.fund_status_date,
2245 new_references.fee_paid_1,
2246 new_references.fee_paid_2,
2247 new_references.cheque_number,
2248 new_references.ld_cal_type,
2249 new_references.ld_sequence_number,
2250 new_references.disb_accepted_amt,
2251 new_references.disb_paid_amt,
2252 new_references.rvsn_id,
2253 new_references.int_rebate_amt,
2254 new_references.force_disb,
2255 new_references.min_credit_pts,
2256 new_references.disb_exp_dt,
2257 new_references.verf_enfr_dt,
2258 new_references.fee_class,
2259 new_references.show_on_bill,
2260 new_references.attendance_type_code,
2261 new_references.base_attendance_type_code,
2262 x_last_update_date,
2263 x_last_updated_by,
2264 x_last_update_date,
2265 x_last_updated_by,
2266 x_last_update_login ,
2267 x_request_id,
2268 x_program_id,
2269 x_program_application_id,
2270 x_program_update_date,
2271 l_org_id,
2272 new_references.payment_prd_st_date,
2273 new_references.change_type_code,
2274 new_references.fund_return_mthd_code,
2275 new_references.direct_to_borr_flag
2276 );
2277
2278
2279 g_v_called_from := x_called_from;
2280 --
2281 -- To Reflect summation of Disbursement Amounts into Award Table
2282 -- Action INSERT
2283 --
2284 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2285 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.insert_row ', ' g_v_called_from '||g_v_called_from);
2286 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.insert_row ', 'before invoking igf_aw_gen.update_award ' );
2287 END IF;
2288 igf_aw_gen.update_award (
2289 new_references.award_id,
2290 new_references.disb_num,
2291 new_references.disb_net_amt,
2292 new_references.disb_date,
2293 'I',
2294 g_v_called_from
2295 );
2296 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2297 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.insert_row ', 'after invoking igf_aw_gen.update_award ' );
2298 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.insert_row ', 'before invoking after_dml for p_action = INSERT ' );
2299 END IF;
2300 after_dml(p_action => 'INSERT');
2301
2302 OPEN c;
2303 FETCH c INTO x_rowid;
2304 IF (c%NOTFOUND) THEN
2305 CLOSE c;
2306 RAISE NO_DATA_FOUND;
2307 END IF;
2308 CLOSE c;
2309 g_v_called_from := NULL;
2310 END insert_row;
2311
2312
2313 PROCEDURE lock_row (
2314 x_rowid IN VARCHAR2,
2315 x_award_id IN NUMBER,
2316 x_disb_num IN NUMBER,
2317 x_tp_cal_type IN VARCHAR2,
2318 x_tp_sequence_number IN NUMBER,
2319 x_disb_gross_amt IN NUMBER,
2320 x_fee_1 IN NUMBER,
2321 x_fee_2 IN NUMBER,
2322 x_disb_net_amt IN NUMBER,
2323 x_disb_date IN DATE,
2324 x_trans_type IN VARCHAR2,
2325 x_elig_status IN VARCHAR2,
2326 x_elig_status_date IN DATE,
2327 x_affirm_flag IN VARCHAR2,
2328 x_hold_rel_ind IN VARCHAR2,
2329 x_manual_hold_ind IN VARCHAR2,
2330 x_disb_status IN VARCHAR2,
2331 x_disb_status_date IN DATE,
2332 x_late_disb_ind IN VARCHAR2,
2333 x_fund_dist_mthd IN VARCHAR2,
2334 x_prev_reported_ind IN VARCHAR2,
2335 x_fund_release_date IN DATE,
2336 x_fund_status IN VARCHAR2,
2337 x_fund_status_date IN DATE,
2338 x_fee_paid_1 IN NUMBER,
2339 x_fee_paid_2 IN NUMBER,
2340 x_cheque_number IN VARCHAR2,
2341 x_ld_cal_type IN VARCHAR2,
2342 x_ld_sequence_number IN NUMBER,
2343 x_disb_accepted_amt IN NUMBER,
2344 x_disb_paid_amt IN NUMBER,
2345 x_rvsn_id IN NUMBER,
2346 x_int_rebate_amt IN NUMBER,
2347 x_force_disb IN VARCHAR2,
2348 x_min_credit_pts IN NUMBER,
2349 x_disb_exp_dt IN DATE,
2350 x_verf_enfr_dt IN DATE,
2351 x_fee_class IN VARCHAR2,
2355 x_payment_prd_st_date IN DATE,
2352 x_show_on_bill IN VARCHAR2,
2353 x_attendance_type_code IN VARCHAR2 DEFAULT NULL,
2354 x_base_attendance_type_code IN VARCHAR2 DEFAULT NULL,
2356 x_change_type_code IN VARCHAR2,
2357 x_fund_return_mthd_code IN VARCHAR2,
2358 x_direct_to_borr_flag IN VARCHAR2 DEFAULT NULL
2359
2360 ) AS
2361 /*
2362 || Created By : adhawan
2363 || Created On : 16-NOV-2000
2364 || Purpose : Handles the LOCK mechanism for the table.
2365 || Known limitations, enhancements or remarks :
2366 || Change History :
2367 || Who When What
2368 || bannamal 29-Sep-2004 FA 149 3416863 cod xml changes for pell and direct loan
2369 || addded a new column
2370 || veramach 3-NOV-2003 FA 125 Multiple Distr Methods
2371 || Added attendance_type_code to the signature
2372 || (reverse chronological order - newest change first)
2373 */
2374 CURSOR c1 IS
2375 SELECT
2376 tp_cal_type,
2377 tp_sequence_number,
2378 disb_gross_amt,
2379 fee_1,
2380 fee_2,
2381 disb_net_amt,
2382 disb_date,
2383 trans_type,
2384 elig_status,
2385 elig_status_date,
2386 affirm_flag,
2387 hold_rel_ind,
2388 manual_hold_ind,
2389 disb_status,
2390 disb_status_date,
2391 late_disb_ind,
2392 fund_dist_mthd,
2393 prev_reported_ind,
2394 fund_release_date,
2395 fund_status,
2396 fund_status_date,
2397 fee_paid_1,
2398 fee_paid_2,
2399 cheque_number,
2400 ld_cal_type,
2401 ld_sequence_number,
2402 disb_accepted_amt,
2403 disb_paid_amt,
2404 rvsn_id,
2405 int_rebate_amt,
2406 force_disb,
2407 min_credit_pts,
2408 disb_exp_dt,
2409 verf_enfr_dt,
2410 fee_class,
2411 show_on_bill,
2412 attendance_type_code,
2413 base_attendance_type_code,
2414 payment_prd_st_date,
2415 change_type_code,
2416 fund_return_mthd_code,
2417 direct_to_borr_flag
2418
2419 FROM igf_aw_awd_disb_all
2420 WHERE rowid = x_rowid
2421 FOR UPDATE NOWAIT;
2422
2423 tlinfo c1%ROWTYPE;
2424
2425 BEGIN
2426
2427 OPEN c1;
2428 FETCH c1 INTO tlinfo;
2429 IF (c1%notfound) THEN
2430 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
2431 igs_ge_msg_stack.add;
2432 CLOSE c1;
2433 app_exception.raise_exception;
2434 RETURN;
2435 END IF;
2436 CLOSE c1;
2437
2438 IF (
2439 (tlinfo.ld_cal_type = x_ld_cal_type)
2440 AND (tlinfo.ld_sequence_number = x_ld_sequence_number)
2441 AND (tlinfo.disb_gross_amt = x_disb_gross_amt)
2442 AND ((tlinfo.fee_1 = x_fee_1) OR ((tlinfo.fee_1 IS NULL) AND (X_fee_1 IS NULL)))
2443 AND ((tlinfo.fee_2 = x_fee_2) OR ((tlinfo.fee_2 IS NULL) AND (X_fee_2 IS NULL)))
2444 AND (tlinfo.disb_net_amt = x_disb_net_amt)
2445 AND (tlinfo.disb_date = x_disb_date)
2446 AND (tlinfo.trans_type = x_trans_type)
2447 AND ((tlinfo.elig_status = x_elig_status) OR ((tlinfo.elig_status IS NULL) AND (X_elig_status IS NULL)))
2448 AND ((tlinfo.elig_status_date = x_elig_status_date) OR ((tlinfo.elig_status_date IS NULL) AND (X_elig_status_date IS NULL)))
2449 AND ((tlinfo.affirm_flag = x_affirm_flag) OR ((tlinfo.affirm_flag IS NULL) AND (X_affirm_flag IS NULL)))
2450 AND ((tlinfo.hold_rel_ind = x_hold_rel_ind) OR ((tlinfo.hold_rel_ind IS NULL) AND (X_hold_rel_ind IS NULL)))
2451 AND ((tlinfo.manual_hold_ind = x_manual_hold_ind) OR ((tlinfo.manual_hold_ind IS NULL) AND (X_manual_hold_ind IS NULL)))
2452 AND ((tlinfo.disb_status = x_disb_status) OR ((tlinfo.disb_status IS NULL) AND (X_disb_status IS NULL)))
2453 AND ((tlinfo.disb_status_date = x_disb_status_date) OR ((tlinfo.disb_status_date IS NULL) AND (X_disb_status_date IS NULL)))
2454 AND ((tlinfo.late_disb_ind = x_late_disb_ind) OR ((tlinfo.late_disb_ind IS NULL) AND (X_late_disb_ind IS NULL)))
2455 AND ((tlinfo.fund_dist_mthd = x_fund_dist_mthd) OR ((tlinfo.fund_dist_mthd IS NULL) AND (X_fund_dist_mthd IS NULL)))
2456 AND ((tlinfo.prev_reported_ind = x_prev_reported_ind) OR ((tlinfo.prev_reported_ind IS NULL) AND (X_prev_reported_ind IS NULL)))
2457 AND ((tlinfo.fund_release_date = x_fund_release_date) OR ((tlinfo.fund_release_date IS NULL) AND (X_fund_release_date IS NULL)))
2458 AND ((tlinfo.fund_status = x_fund_status) OR ((tlinfo.fund_status IS NULL) AND (X_fund_status IS NULL)))
2459 AND ((tlinfo.fund_status_date = x_fund_status_date) OR ((tlinfo.fund_status_date IS NULL) AND (X_fund_status_date IS NULL)))
2460 AND ((tlinfo.fee_paid_1 = x_fee_paid_1) OR ((tlinfo.fee_paid_1 IS NULL) AND (X_fee_paid_1 IS NULL)))
2461 AND ((tlinfo.fee_paid_2 = x_fee_paid_2) OR ((tlinfo.fee_paid_2 IS NULL) AND (X_fee_paid_2 IS NULL)))
2462 AND ((tlinfo.cheque_number = x_cheque_number) OR ((tlinfo.cheque_number IS NULL) AND (X_cheque_number IS NULL)))
2463 AND ((tlinfo.tp_cal_type = x_tp_cal_type) OR ((tlinfo.tp_cal_type IS NULL) AND (X_tp_cal_type IS NULL)))
2464 AND ((tlinfo.tp_sequence_number = x_tp_sequence_number) OR ((tlinfo.tp_sequence_number IS NULL) AND (X_tp_sequence_number IS NULL)))
2465 AND ((tlinfo.disb_accepted_amt = x_disb_accepted_amt) OR ((tlinfo.disb_accepted_amt IS NULL) AND (X_disb_accepted_amt IS NULL)))
2466 AND ((tlinfo.disb_paid_amt = x_disb_paid_amt) OR ((tlinfo.disb_paid_amt IS NULL) AND (X_disb_paid_amt IS NULL)))
2467 AND ((tlinfo.rvsn_id = x_rvsn_id) OR ((tlinfo.rvsn_id IS NULL) AND (X_rvsn_id IS NULL)))
2471 AND ((tlinfo.disb_exp_dt = x_disb_exp_dt) OR ((tlinfo.disb_exp_dt IS NULL) AND (x_disb_exp_dt IS NULL)))
2468 AND ((tlinfo.int_rebate_amt = x_int_rebate_amt) OR ((tlinfo.int_rebate_amt IS NULL) AND (x_int_rebate_amt IS NULL)))
2469 AND ((tlinfo.force_disb = x_force_disb) OR ((tlinfo.force_disb IS NULL) AND (x_force_disb IS NULL)))
2470 AND ((tlinfo.min_credit_pts = x_min_credit_pts) OR ((tlinfo.min_credit_pts IS NULL) AND (x_min_credit_pts IS NULL)))
2472 AND ((tlinfo.verf_enfr_dt = x_verf_enfr_dt) OR ((tlinfo.verf_enfr_dt IS NULL) AND (x_verf_enfr_dt IS NULL)))
2473 AND ((tlinfo.fee_class = x_fee_class) OR ((tlinfo.fee_class IS NULL) AND (x_fee_class IS NULL)))
2474 AND ((tlinfo.show_on_bill = x_show_on_bill) OR ((tlinfo.show_on_bill IS NULL) AND (x_show_on_bill IS NULL)))
2475 AND ((tlinfo.attendance_type_code = x_attendance_type_code) OR ((tlinfo.attendance_type_code IS NULL) AND (x_attendance_type_code IS NULL)))
2476 AND ((tlinfo.base_attendance_type_code = x_base_attendance_type_code) OR ((tlinfo.base_attendance_type_code IS NULL) AND (x_base_attendance_type_code IS NULL)))
2477 AND ((tlinfo.payment_prd_st_date = x_payment_prd_st_date) OR ((tlinfo.payment_prd_st_date IS NULL) AND (x_payment_prd_st_date IS NULL)))
2478 AND ((tlinfo.change_type_code = x_change_type_code) OR ((tlinfo.change_type_code IS NULL) AND (x_change_type_code IS NULL)))
2479 AND ((tlinfo.fund_return_mthd_code = x_fund_return_mthd_code) OR ((tlinfo.fund_return_mthd_code IS NULL) AND (x_fund_return_mthd_code IS NULL)))
2480 AND ((tlinfo.direct_to_borr_flag = x_direct_to_borr_flag) OR ((tlinfo.direct_to_borr_flag IS NULL) AND (x_direct_to_borr_flag IS NULL)))
2481 ) THEN
2482 NULL;
2483 ELSE
2484 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
2485 igs_ge_msg_stack.add;
2486 app_exception.raise_exception;
2487 END IF;
2488
2489 RETURN;
2490
2491 END lock_row;
2492
2493
2494 PROCEDURE update_row (
2495 x_rowid IN VARCHAR2,
2496 x_award_id IN NUMBER,
2497 x_disb_num IN NUMBER,
2498 x_tp_cal_type IN VARCHAR2,
2499 x_tp_sequence_number IN NUMBER,
2500 x_disb_gross_amt IN NUMBER,
2501 x_fee_1 IN NUMBER,
2502 x_fee_2 IN NUMBER,
2503 x_disb_net_amt IN NUMBER,
2504 x_disb_date IN DATE,
2505 x_trans_type IN VARCHAR2,
2506 x_elig_status IN VARCHAR2,
2507 x_elig_status_date IN DATE,
2508 x_affirm_flag IN VARCHAR2,
2509 x_hold_rel_ind IN VARCHAR2,
2510 x_manual_hold_ind IN VARCHAR2,
2511 x_disb_status IN VARCHAR2,
2512 x_disb_status_date IN DATE,
2513 x_late_disb_ind IN VARCHAR2,
2514 x_fund_dist_mthd IN VARCHAR2,
2515 x_prev_reported_ind IN VARCHAR2,
2516 x_fund_release_date IN DATE,
2517 x_fund_status IN VARCHAR2,
2518 x_fund_status_date IN DATE,
2519 x_fee_paid_1 IN NUMBER,
2520 x_fee_paid_2 IN NUMBER,
2521 x_cheque_number IN VARCHAR2,
2522 x_ld_cal_type IN VARCHAR2,
2523 x_ld_sequence_number IN NUMBER,
2524 x_disb_accepted_amt IN NUMBER,
2525 x_disb_paid_amt IN NUMBER,
2526 x_rvsn_id IN NUMBER,
2527 x_int_rebate_amt IN NUMBER,
2528 x_force_disb IN VARCHAR2,
2529 x_min_credit_pts IN NUMBER,
2530 x_disb_exp_dt IN DATE,
2531 x_verf_enfr_dt IN DATE,
2532 x_fee_class IN VARCHAR2,
2533 x_show_on_bill IN VARCHAR2,
2534 x_mode IN VARCHAR2,
2535 x_attendance_type_code IN VARCHAR2,
2536 x_base_attendance_type_code IN VARCHAR2,
2537 x_payment_prd_st_date IN DATE,
2538 x_change_type_code IN VARCHAR2,
2539 x_fund_return_mthd_code IN VARCHAR2,
2540 x_called_from IN VARCHAR2,
2541 x_direct_to_borr_flag IN VARCHAR2
2542
2543 ) AS
2544 /*
2545 || Created By : adhawan
2546 || Created On : 16-NOV-2000
2547 || Purpose : Handles the UPDATE DML logic for the table.
2548 || Known limitations, enhancements or remarks :
2549 || Change History :
2550 || Who When What
2551 || bannamal 29-Sep-2004 FA 149 3416863 cod xml changes for pell and direct loan
2552 || addded a new column
2553 || veramach 3-NOV-2003 FA 125 Multiple Distr Methods
2554 || Added attendance_type_code to the signature
2555 || (reverse chronological order - newest change first)
2556 */
2557 x_last_update_date DATE ;
2558 x_last_updated_by NUMBER;
2559 x_last_update_login NUMBER;
2560 x_request_id NUMBER;
2561 x_program_id NUMBER;
2562 x_program_application_id NUMBER;
2563 x_program_update_date DATE;
2564
2565 BEGIN
2566
2567 x_last_update_date := SYSDATE;
2568 IF (X_MODE = 'I') THEN
2569 x_last_updated_by := 1;
2570 x_last_update_login := 0;
2571 ELSIF (x_mode = 'R') THEN
2572 x_last_updated_by := fnd_global.user_id;
2573 IF x_last_updated_by IS NULL THEN
2577 IF (x_last_update_login IS NULL) THEN
2574 x_last_updated_by := -1;
2575 END IF;
2576 x_last_update_login := fnd_global.login_id;
2578 x_last_update_login := -1;
2579 END IF;
2580 ELSE
2581 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
2582 igs_ge_msg_stack.add;
2583 app_exception.raise_exception;
2584 END IF;
2585
2586 before_dml(
2587 p_action => 'UPDATE',
2588 x_rowid => x_rowid,
2589 x_award_id => x_award_id,
2590 x_disb_num => x_disb_num,
2591 x_tp_cal_type => x_tp_cal_type,
2592 x_tp_sequence_number => x_tp_sequence_number,
2593 x_disb_gross_amt => x_disb_gross_amt,
2594 x_fee_1 => x_fee_1,
2595 x_fee_2 => x_fee_2,
2596 x_disb_net_amt => x_disb_net_amt,
2597 x_disb_date => x_disb_date,
2598 x_trans_type => x_trans_type,
2599 x_elig_status => x_elig_status,
2600 x_elig_status_date => x_elig_status_date,
2601 x_affirm_flag => x_affirm_flag,
2602 x_hold_rel_ind => x_hold_rel_ind,
2603 x_manual_hold_ind => x_manual_hold_ind,
2604 x_disb_status => x_disb_status,
2605 x_disb_status_date => x_disb_status_date,
2606 x_late_disb_ind => x_late_disb_ind,
2607 x_fund_dist_mthd => x_fund_dist_mthd,
2608 x_prev_reported_ind => x_prev_reported_ind,
2609 x_fund_release_date => x_fund_release_date,
2610 x_fund_status => x_fund_status,
2611 x_fund_status_date => x_fund_status_date,
2612 x_fee_paid_1 => x_fee_paid_1,
2613 x_fee_paid_2 => x_fee_paid_2,
2614 x_cheque_number => x_cheque_number,
2615 x_ld_cal_type => x_ld_cal_type,
2616 x_ld_sequence_number => x_ld_sequence_number,
2617 x_disb_accepted_amt => x_disb_accepted_amt,
2618 x_disb_paid_amt => x_disb_paid_amt,
2619 x_rvsn_id => x_rvsn_id,
2620 x_int_rebate_amt => x_int_rebate_amt,
2621 x_force_disb => x_force_disb,
2622 x_min_credit_pts => x_min_credit_pts,
2623 x_disb_exp_dt => x_disb_exp_dt,
2624 x_verf_enfr_dt => x_verf_enfr_dt,
2625 x_fee_class => x_fee_class,
2626 x_show_on_bill => x_show_on_bill,
2627 x_attendance_type_code => x_attendance_type_code,
2628 x_base_attendance_type_code => x_base_attendance_type_code,
2629 x_creation_date => x_last_update_date,
2630 x_created_by => x_last_updated_by,
2631 x_last_update_date => x_last_update_date,
2632 x_last_updated_by => x_last_updated_by,
2633 x_last_update_login => x_last_update_login,
2634 x_payment_prd_st_date => x_payment_prd_st_date,
2635 x_change_type_code => x_change_type_code,
2636 x_fund_return_mthd_code => x_fund_return_mthd_code,
2637 x_direct_to_borr_flag => x_direct_to_borr_flag
2638
2639 );
2640
2641 IF (x_mode = 'R') THEN
2642 x_request_id := fnd_global.conc_request_id;
2643 x_program_id := fnd_global.conc_program_id;
2644 x_program_application_id := fnd_global.prog_appl_id;
2645 IF (x_request_id = -1) THEN
2646 x_request_id := old_references.request_id;
2647 x_program_id := old_references.program_id;
2648 x_program_application_id := old_references.program_application_id;
2649 x_program_update_date := old_references.program_update_date;
2650 ELSE
2651 x_program_update_date := SYSDATE;
2652 END IF;
2653 END IF;
2654
2655 --
2656 -- To Crate Adjustments
2657 --
2658
2659 igf_aw_gen.update_disb( old_references,new_references );
2660
2661 UPDATE igf_aw_awd_disb_all
2662 SET
2663 tp_cal_type = new_references.tp_cal_type,
2664 tp_sequence_number = new_references.tp_sequence_number,
2665 disb_gross_amt = new_references.disb_gross_amt,
2666 fee_1 = new_references.fee_1,
2667 fee_2 = new_references.fee_2,
2668 disb_net_amt = new_references.disb_net_amt,
2669 disb_date = new_references.disb_date,
2670 trans_type = new_references.trans_type,
2671 elig_status = new_references.elig_status,
2672 elig_status_date = new_references.elig_status_date,
2673 affirm_flag = new_references.affirm_flag,
2674 hold_rel_ind = NVL(new_references.hold_rel_ind, 'FALSE'),
2675 manual_hold_ind = new_references.manual_hold_ind,
2676 disb_status = new_references.disb_status,
2677 disb_status_date = new_references.disb_status_date,
2678 late_disb_ind = new_references.late_disb_ind,
2679 fund_dist_mthd = new_references.fund_dist_mthd,
2680 prev_reported_ind = new_references.prev_reported_ind,
2681 fund_release_date = new_references.fund_release_date,
2685 fee_paid_2 = new_references.fee_paid_2,
2682 fund_status = new_references.fund_status,
2683 fund_status_date = new_references.fund_status_date,
2684 fee_paid_1 = new_references.fee_paid_1,
2686 cheque_number = new_references.cheque_number,
2687 ld_cal_type = new_references.ld_cal_type,
2688 ld_sequence_number = new_references.ld_sequence_number,
2689 disb_accepted_amt = new_references.disb_accepted_amt,
2690 disb_paid_amt = new_references.disb_paid_amt,
2691 rvsn_id = new_references.rvsn_id,
2692 int_rebate_amt = new_references.int_rebate_amt,
2693 force_disb = new_references.force_disb,
2694 min_credit_pts = new_references.min_credit_pts,
2695 disb_exp_dt = new_references.disb_exp_dt,
2696 verf_enfr_dt = new_references.verf_enfr_dt,
2697 fee_class = new_references.fee_class,
2698 show_on_bill = new_references.show_on_bill,
2699 attendance_type_code = new_references.attendance_type_code,
2700 base_attendance_type_code = new_references.base_attendance_type_code,
2701 last_update_date = x_last_update_date,
2702 last_updated_by = x_last_updated_by,
2703 last_update_login = x_last_update_login ,
2704 request_id = x_request_id,
2705 program_id = x_program_id,
2706 program_application_id = x_program_application_id,
2707 program_update_date = x_program_update_date,
2708 payment_prd_st_date = new_references.payment_prd_st_date,
2709 change_type_code = new_references.change_type_code,
2710 fund_return_mthd_code = new_references.fund_return_mthd_code,
2711 direct_to_borr_flag = new_references.direct_to_borr_flag
2712
2713 WHERE rowid = x_rowid;
2714
2715 IF (SQL%NOTFOUND) THEN
2716 RAISE NO_DATA_FOUND;
2717 END IF;
2718 --
2719 -- To Reflect summation of Disbursement Amounts into Award Table
2720 -- Action UPDATE
2721 --
2722 g_v_called_from := x_called_from;
2723 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2724 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.update_row ', ' g_v_called_from '||g_v_called_from);
2725 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.update_row ', 'before invoking after_dml ' );
2726 END IF;
2727 after_dml(p_action => 'UPDATE');
2728 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2729 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.update_row ', 'after invoking after_dml ' );
2730 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.update_row ', 'before invoking igf_aw_gen.update_award ' );
2731 END IF;
2732 igf_aw_gen.update_award (new_references.award_id,
2733 new_references.disb_num,
2734 new_references.disb_net_amt,
2735 new_references.disb_date,
2736 'U',
2737 g_v_called_from
2738 );
2739 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2740 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.update_row ', 'after invoking igf_aw_gen.update_award ' );
2741 END IF;
2742 g_v_called_from := NULL;
2743 END update_row;
2744
2745
2746 PROCEDURE add_row (
2747 x_rowid IN OUT NOCOPY VARCHAR2,
2748 x_award_id IN NUMBER,
2749 x_disb_num IN NUMBER,
2750 x_tp_cal_type IN VARCHAR2,
2751 x_tp_sequence_number IN NUMBER,
2752 x_disb_gross_amt IN NUMBER,
2753 x_fee_1 IN NUMBER,
2754 x_fee_2 IN NUMBER,
2755 x_disb_net_amt IN NUMBER,
2756 x_disb_date IN DATE,
2757 x_trans_type IN VARCHAR2,
2758 x_elig_status IN VARCHAR2,
2759 x_elig_status_date IN DATE,
2760 x_affirm_flag IN VARCHAR2,
2761 x_hold_rel_ind IN VARCHAR2,
2762 x_manual_hold_ind IN VARCHAR2,
2763 x_disb_status IN VARCHAR2,
2764 x_disb_status_date IN DATE,
2765 x_late_disb_ind IN VARCHAR2,
2766 x_fund_dist_mthd IN VARCHAR2,
2767 x_prev_reported_ind IN VARCHAR2,
2768 x_fund_release_date IN DATE,
2769 x_fund_status IN VARCHAR2,
2770 x_fund_status_date IN DATE,
2771 x_fee_paid_1 IN NUMBER,
2772 x_fee_paid_2 IN NUMBER,
2773 x_cheque_number IN VARCHAR2,
2774 x_ld_cal_type IN VARCHAR2,
2775 x_ld_sequence_number IN NUMBER,
2776 x_disb_accepted_amt IN NUMBER,
2777 x_disb_paid_amt IN NUMBER,
2778 x_rvsn_id IN NUMBER,
2779 x_int_rebate_amt IN NUMBER,
2780 x_force_disb IN VARCHAR2,
2781 x_min_credit_pts IN NUMBER,
2782 x_disb_exp_dt IN DATE,
2786 x_mode IN VARCHAR2,
2783 x_verf_enfr_dt IN DATE,
2784 x_fee_class IN VARCHAR2,
2785 x_show_on_bill IN VARCHAR2,
2787 x_attendance_type_code IN VARCHAR2,
2788 x_base_attendance_type_code IN VARCHAR2,
2789 x_payment_prd_st_date IN DATE,
2790 x_change_type_code IN VARCHAR2,
2791 x_fund_return_mthd_code IN VARCHAR2,
2792 x_direct_to_borr_flag IN VARCHAR2
2793
2794 ) AS
2795 /*
2796 || Created By : adhawan
2797 || Created On : 16-NOV-2000
2798 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
2799 || Known limitations, enhancements or remarks :
2800 || Change History :
2801 || Who When What
2802 || (reverse chronological order - newest change first)
2803 || bannamal 29-Sep-2004 FA 149 3416863 cod xml changes for pell and direct loan
2804 || addded a new column
2805 || veramach 3-NOV-2003 FA 125 Multiple Distr Methods
2806 || Added attendance_type_code to the signature
2807 */
2808 CURSOR c1 IS
2809 SELECT rowid
2810 FROM igf_aw_awd_disb_all
2811 WHERE award_id = x_award_id
2812 AND disb_num = x_disb_num;
2813
2814 BEGIN
2815
2816 OPEN c1;
2817 FETCH c1 INTO x_rowid;
2818 IF (c1%NOTFOUND) THEN
2819 CLOSE c1;
2820
2821 insert_row (
2822 x_rowid,
2823 x_award_id,
2824 x_disb_num,
2825 x_tp_cal_type,
2826 x_tp_sequence_number,
2827 x_disb_gross_amt,
2828 x_fee_1,
2829 x_fee_2,
2830 x_disb_net_amt,
2831 x_disb_date,
2832 x_trans_type,
2833 x_elig_status,
2834 x_elig_status_date,
2835 x_affirm_flag,
2836 x_hold_rel_ind,
2837 x_manual_hold_ind,
2838 x_disb_status,
2839 x_disb_status_date,
2840 x_late_disb_ind,
2841 x_fund_dist_mthd,
2842 x_prev_reported_ind,
2843 x_fund_release_date,
2844 x_fund_status,
2845 x_fund_status_date,
2846 x_fee_paid_1,
2847 x_fee_paid_2,
2848 x_cheque_number,
2849 x_ld_cal_type,
2850 x_ld_sequence_number,
2851 x_disb_accepted_amt,
2852 x_disb_paid_amt,
2853 x_rvsn_id,
2854 x_int_rebate_amt,
2855 x_force_disb,
2856 x_min_credit_pts,
2857 x_disb_exp_dt,
2858 x_verf_enfr_dt,
2859 x_fee_class,
2860 x_show_on_bill,
2861 x_mode,
2862 x_attendance_type_code,
2863 x_base_attendance_type_code,
2864 x_payment_prd_st_date,
2865 x_change_type_code,
2866 x_fund_return_mthd_code,
2867 x_direct_to_borr_flag
2868
2869 );
2870 RETURN;
2871 END IF;
2872 CLOSE c1;
2873
2874 update_row (
2875 x_rowid,
2876 x_award_id,
2877 x_disb_num,
2878 x_tp_cal_type,
2879 x_tp_sequence_number,
2880 x_disb_gross_amt,
2881 x_fee_1,
2882 x_fee_2,
2883 x_disb_net_amt,
2884 x_disb_date,
2885 x_trans_type,
2886 x_elig_status,
2887 x_elig_status_date,
2888 x_affirm_flag,
2889 x_hold_rel_ind,
2890 x_manual_hold_ind,
2891 x_disb_status,
2892 x_disb_status_date,
2893 x_late_disb_ind,
2894 x_fund_dist_mthd,
2895 x_prev_reported_ind,
2896 x_fund_release_date,
2897 x_fund_status,
2898 x_fund_status_date,
2899 x_fee_paid_1,
2900 x_fee_paid_2,
2901 x_cheque_number,
2902 x_ld_cal_type,
2903 x_ld_sequence_number,
2904 x_disb_accepted_amt,
2905 x_disb_paid_amt,
2906 x_rvsn_id,
2907 x_int_rebate_amt,
2908 x_force_disb,
2909 x_min_credit_pts,
2910 x_disb_exp_dt,
2911 x_verf_enfr_dt,
2912 x_fee_class,
2913 x_show_on_bill,
2914 x_mode,
2915 x_attendance_type_code,
2916 x_base_attendance_type_code,
2917 x_payment_prd_st_date,
2918 x_change_type_code,
2919 x_fund_return_mthd_code,
2920 x_direct_to_borr_flag
2921
2922 );
2923
2924 END add_row;
2925
2926
2927 PROCEDURE delete_row (
2928 x_rowid IN VARCHAR2,
2929 x_called_from IN VARCHAR2
2930 ) AS
2931 /*
2932 || Created By : adhawan
2933 || Created On : 16-NOV-2000
2934 || Purpose : Handles the DELETE DML logic for the table.
2935 || Known limitations, enhancements or remarks :
2936 || Change History :
2937 || Who When What
2938 || (reverse chronological order - newest change first)
2939 */
2940
2941 --
2942 -- Bug 2255279
2943 -- sjadhav
2944 -- Apr02,2002
2945 -- Reflect The Amoutns into Awards Table once a disbursement
2946 -- is deleted
2947 --
2948
2949 CURSOR cur_get_award ( p_row_id ROWID)
2950 IS
2951 SELECT
2952 award_id
2953 FROM
2954 igf_aw_awd_disb
2955 WHERE
2956 row_id = p_row_id;
2957
2958 get_award_rec cur_get_award%ROWTYPE;
2959
2960 BEGIN
2961
2962 OPEN cur_get_award(x_rowid);
2966 g_v_called_from := x_called_from;
2963 FETCH cur_get_award INTO get_award_rec;
2964 CLOSE cur_get_award;
2965
2967 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2968 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.delete_row ', ' g_v_called_from '||g_v_called_from);
2969 END IF;
2970 before_dml (
2971 p_action => 'DELETE',
2972 x_rowid => x_rowid
2973 );
2974 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2975 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.delete_row ', ' before invoking BeforeRowInsertUpdateDelete1 ');
2976 END IF;
2977 BeforeRowInsertUpdateDelete1(
2978 p_rowid => x_rowid,
2979 p_inserting => FALSE,
2980 p_updating => FALSE ,
2981 p_deleting => TRUE
2982 );
2983 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2984 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.delete_row ', ' after invoking BeforeRowInsertUpdateDelete1 ');
2985 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.delete_row ', ' deleting the record from igf_aw_awd_disb_all table for rowid '||x_rowid);
2986 END IF;
2987 DELETE FROM igf_aw_awd_disb_all
2988 WHERE rowid = x_rowid;
2989
2990 IF (SQL%NOTFOUND) THEN
2991 RAISE NO_DATA_FOUND;
2992 END IF;
2993
2994 --
2995 -- To Reflect summation of Disbursement Amounts into Award Table
2996 -- Action DELETE
2997 --
2998 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2999 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_awd_disb_pkg.delete_row ', ' before invoking igf_aw_gen.update_award ');
3000 END IF;
3001 igf_aw_gen.update_award (get_award_rec.award_id,
3002 0,
3003 0,
3004 TRUNC(SYSDATE),
3005 'D',
3006 g_v_called_from
3007 );
3008 g_v_called_from := NULL;
3009 END delete_row;
3010
3011 END igf_aw_awd_disb_pkg;