[Home] [Help]
PACKAGE BODY: APPS.IGF_GR_YTD_ORIG_PKG
Source
1 PACKAGE BODY igf_gr_ytd_orig_pkg AS
2 /* $Header: IGFGI13B.pls 120.1 2006/04/06 06:08:20 veramach noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_gr_ytd_orig_all%ROWTYPE;
6 new_references igf_gr_ytd_orig_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_ytdor_id IN NUMBER ,
12 x_origination_id IN VARCHAR2 ,
13 x_original_ssn IN VARCHAR2 ,
14 x_original_name_cd IN VARCHAR2 ,
15 x_attend_pell_id IN VARCHAR2 ,
16 x_ed_use IN VARCHAR2 ,
17 x_inst_cross_ref_code IN VARCHAR2 ,
18 x_action_code IN VARCHAR2 ,
19 x_accpt_awd_amt IN NUMBER ,
20 x_accpt_disb_dt1 IN DATE ,
21 x_accpt_disb_dt2 IN DATE ,
22 x_accpt_disb_dt3 IN DATE ,
23 x_accpt_disb_dt4 IN DATE ,
24 x_accpt_disb_dt5 IN DATE ,
25 x_accpt_disb_dt6 IN DATE ,
26 x_accpt_disb_dt7 IN DATE ,
27 x_accpt_disb_dt8 IN DATE ,
28 x_accpt_disb_dt9 IN DATE ,
29 x_accpt_disb_dt10 IN DATE ,
30 x_accpt_disb_dt11 IN DATE ,
31 x_accpt_disb_dt12 IN DATE ,
32 x_accpt_disb_dt13 IN DATE ,
33 x_accpt_disb_dt14 IN DATE ,
34 x_accpt_disb_dt15 IN DATE ,
35 x_accpt_enrl_dt IN DATE ,
36 x_accpt_low_tut_flg IN VARCHAR2 ,
37 x_accpt_ver_stat_flg IN VARCHAR2 ,
38 x_accpt_incr_pell_cd IN VARCHAR2 ,
39 x_accpt_tran_num IN VARCHAR2 ,
40 x_accpt_efc IN NUMBER ,
41 x_accpt_sec_efc IN VARCHAR2 ,
42 x_accpt_acad_cal IN VARCHAR2 ,
43 x_accpt_pymt_method IN VARCHAR2 ,
44 x_accpt_coa IN NUMBER ,
45 x_accpt_enrl_stat IN VARCHAR2 ,
46 x_accpt_wks_inst_pymt IN VARCHAR2 ,
47 x_wk_inst_time_calc_pymt IN NUMBER ,
48 x_accpt_wks_acad IN VARCHAR2 ,
49 x_accpt_cr_acad_yr IN VARCHAR2 ,
50 x_inst_seq_num IN VARCHAR2 ,
51 x_sch_full_time_pell IN NUMBER ,
52 x_stud_name IN VARCHAR2 ,
53 x_ssn IN VARCHAR2 ,
54 x_stud_dob IN DATE ,
55 x_cps_ver_sel_cd IN VARCHAR2 ,
56 x_ytd_disb_amt IN NUMBER ,
57 x_batch_id IN VARCHAR2 ,
58 x_process_date IN DATE ,
59 x_creation_date IN DATE ,
60 x_created_by IN NUMBER ,
61 x_last_update_date IN DATE ,
62 x_last_updated_by IN NUMBER ,
63 x_last_update_login IN NUMBER ,
64 x_ci_cal_type IN VARCHAR2 ,
65 x_ci_sequence_number IN NUMBER
66 ) AS
67 /*
68 || Created By : avenkatr
69 || Created On : 21-DEC-2000
70 || Purpose : Initialises the Old and New references for the columns of the table.
71 || Known limitations, enhancements or remarks :
72 || Change History :
73 || Who When What
74 || (reverse chronological order - newest change first)
75 */
76
77 CURSOR cur_old_ref_values IS
78 SELECT *
79 FROM igf_gr_ytd_orig_all
80 WHERE rowid = x_rowid;
81
82 BEGIN
83
84 l_rowid := x_rowid;
85
86 -- Code for setting the Old and New Reference Values.
87 -- Populate Old Values.
88 OPEN cur_old_ref_values;
89 FETCH cur_old_ref_values INTO old_references;
90 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
91 CLOSE cur_old_ref_values;
92 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
93 igs_ge_msg_stack.add;
94 app_exception.raise_exception;
95 RETURN;
96 END IF;
97 CLOSE cur_old_ref_values;
98
99 -- Populate New Values.
100 new_references.ytdor_id := x_ytdor_id;
101 new_references.origination_id := x_origination_id;
102 new_references.original_ssn := x_original_ssn;
103 new_references.original_name_cd := x_original_name_cd;
104 new_references.attend_pell_id := x_attend_pell_id;
105 new_references.ed_use := x_ed_use;
106 new_references.inst_cross_ref_code := x_inst_cross_ref_code;
107 new_references.action_code := x_action_code;
108 new_references.accpt_awd_amt := x_accpt_awd_amt;
109 new_references.accpt_disb_dt1 := x_accpt_disb_dt1;
110 new_references.accpt_disb_dt2 := x_accpt_disb_dt2;
111 new_references.accpt_disb_dt3 := x_accpt_disb_dt3;
112 new_references.accpt_disb_dt4 := x_accpt_disb_dt4;
113 new_references.accpt_disb_dt5 := x_accpt_disb_dt5;
114 new_references.accpt_disb_dt6 := x_accpt_disb_dt6;
115 new_references.accpt_disb_dt7 := x_accpt_disb_dt7;
116 new_references.accpt_disb_dt8 := x_accpt_disb_dt8;
117 new_references.accpt_disb_dt9 := x_accpt_disb_dt9;
118 new_references.accpt_disb_dt10 := x_accpt_disb_dt10;
119 new_references.accpt_disb_dt11 := x_accpt_disb_dt11;
120 new_references.accpt_disb_dt12 := x_accpt_disb_dt12;
121 new_references.accpt_disb_dt13 := x_accpt_disb_dt13;
122 new_references.accpt_disb_dt14 := x_accpt_disb_dt14;
123 new_references.accpt_disb_dt15 := x_accpt_disb_dt15;
124 new_references.accpt_enrl_dt := x_accpt_enrl_dt;
125 new_references.accpt_low_tut_flg := x_accpt_low_tut_flg;
126 new_references.accpt_ver_stat_flg := x_accpt_ver_stat_flg;
127 new_references.accpt_incr_pell_cd := x_accpt_incr_pell_cd;
128 new_references.accpt_tran_num := x_accpt_tran_num;
129 new_references.accpt_efc := x_accpt_efc;
130 new_references.accpt_sec_efc := x_accpt_sec_efc;
131 new_references.accpt_acad_cal := x_accpt_acad_cal;
132 new_references.accpt_pymt_method := x_accpt_pymt_method;
133 new_references.accpt_coa := x_accpt_coa;
134 new_references.accpt_enrl_stat := x_accpt_enrl_stat;
135 new_references.accpt_wks_inst_pymt := x_accpt_wks_inst_pymt;
136 new_references.wk_inst_time_calc_pymt := x_wk_inst_time_calc_pymt;
137 new_references.accpt_wks_acad := x_accpt_wks_acad;
138 new_references.accpt_cr_acad_yr := x_accpt_cr_acad_yr;
139 new_references.inst_seq_num := x_inst_seq_num;
140 new_references.sch_full_time_pell := x_sch_full_time_pell;
141 new_references.stud_name := x_stud_name;
142 new_references.ssn := x_ssn;
143 new_references.stud_dob := x_stud_dob;
144 new_references.cps_ver_sel_cd := x_cps_ver_sel_cd;
145 new_references.ytd_disb_amt := x_ytd_disb_amt;
146 new_references.batch_id := x_batch_id;
147 new_references.process_date := x_process_date;
148 new_references.ci_cal_type := x_ci_cal_type;
149 new_references.ci_sequence_number := x_ci_sequence_number;
150
151 IF (p_action = 'UPDATE') THEN
152 new_references.creation_date := old_references.creation_date;
153 new_references.created_by := old_references.created_by;
154 ELSE
155 new_references.creation_date := x_creation_date;
156 new_references.created_by := x_created_by;
157 END IF;
158
159 new_references.last_update_date := x_last_update_date;
160 new_references.last_updated_by := x_last_updated_by;
161 new_references.last_update_login := x_last_update_login;
162
163 END set_column_values;
164
165
166 FUNCTION get_pk_for_validation (
167 x_ytdor_id IN NUMBER
168 ) RETURN BOOLEAN AS
169 /*
170 || Created By : avenkatr
171 || Created On : 21-DEC-2000
172 || Purpose : Validates the Primary Key of the table.
173 || Known limitations, enhancements or remarks :
174 || Change History :
175 || Who When What
176 || (reverse chronological order - newest change first)
177 */
178 CURSOR cur_rowid IS
179 SELECT rowid
180 FROM igf_gr_ytd_orig_all
181 WHERE ytdor_id = x_ytdor_id
182 FOR UPDATE NOWAIT;
183
184 lv_rowid cur_rowid%RowType;
185
186 BEGIN
187
188 OPEN cur_rowid;
189 FETCH cur_rowid INTO lv_rowid;
190 IF (cur_rowid%FOUND) THEN
191 CLOSE cur_rowid;
192 RETURN(TRUE);
193 ELSE
194 CLOSE cur_rowid;
195 RETURN(FALSE);
196 END IF;
197
198 END get_pk_for_validation;
199
200
201 PROCEDURE before_dml (
202 p_action IN VARCHAR2,
203 x_rowid IN VARCHAR2 ,
204 x_ytdor_id IN NUMBER ,
205 x_origination_id IN VARCHAR2 ,
206 x_original_ssn IN VARCHAR2 ,
207 x_original_name_cd IN VARCHAR2 ,
208 x_attend_pell_id IN VARCHAR2 ,
209 x_ed_use IN VARCHAR2 ,
210 x_inst_cross_ref_code IN VARCHAR2 ,
211 x_action_code IN VARCHAR2 ,
212 x_accpt_awd_amt IN NUMBER ,
213 x_accpt_disb_dt1 IN DATE ,
214 x_accpt_disb_dt2 IN DATE ,
215 x_accpt_disb_dt3 IN DATE ,
216 x_accpt_disb_dt4 IN DATE ,
217 x_accpt_disb_dt5 IN DATE ,
218 x_accpt_disb_dt6 IN DATE ,
219 x_accpt_disb_dt7 IN DATE ,
220 x_accpt_disb_dt8 IN DATE ,
221 x_accpt_disb_dt9 IN DATE ,
222 x_accpt_disb_dt10 IN DATE ,
223 x_accpt_disb_dt11 IN DATE ,
224 x_accpt_disb_dt12 IN DATE ,
225 x_accpt_disb_dt13 IN DATE ,
226 x_accpt_disb_dt14 IN DATE ,
227 x_accpt_disb_dt15 IN DATE ,
228 x_accpt_enrl_dt IN DATE ,
229 x_accpt_low_tut_flg IN VARCHAR2 ,
230 x_accpt_ver_stat_flg IN VARCHAR2 ,
231 x_accpt_incr_pell_cd IN VARCHAR2 ,
232 x_accpt_tran_num IN VARCHAR2 ,
233 x_accpt_efc IN NUMBER ,
234 x_accpt_sec_efc IN VARCHAR2 ,
235 x_accpt_acad_cal IN VARCHAR2 ,
236 x_accpt_pymt_method IN VARCHAR2 ,
237 x_accpt_coa IN NUMBER ,
238 x_accpt_enrl_stat IN VARCHAR2 ,
239 x_accpt_wks_inst_pymt IN VARCHAR2 ,
240 x_wk_inst_time_calc_pymt IN NUMBER ,
241 x_accpt_wks_acad IN VARCHAR2 ,
242 x_accpt_cr_acad_yr IN VARCHAR2 ,
243 x_inst_seq_num IN VARCHAR2 ,
244 x_sch_full_time_pell IN NUMBER ,
245 x_stud_name IN VARCHAR2 ,
246 x_ssn IN VARCHAR2 ,
247 x_stud_dob IN DATE ,
248 x_cps_ver_sel_cd IN VARCHAR2 ,
249 x_ytd_disb_amt IN NUMBER ,
250 x_batch_id IN VARCHAR2 ,
251 x_process_date IN DATE ,
252 x_creation_date IN DATE ,
253 x_created_by IN NUMBER ,
254 x_last_update_date IN DATE ,
255 x_last_updated_by IN NUMBER ,
256 x_last_update_login IN NUMBER ,
257 x_ci_cal_type IN VARCHAR2 ,
258 x_ci_sequence_number IN NUMBER
259 ) AS
260 /*
261 || Created By : avenkatr
262 || Created On : 21-DEC-2000
263 || Purpose : Initialises the columns, Checks Constraints, Calls the
264 || Trigger Handlers for the table, before any DML operation.
265 || Known limitations, enhancements or remarks :
266 || Change History :
267 || Who When What
268 || (reverse chronological order - newest change first)
269 */
270 BEGIN
271
272 set_column_values (
273 p_action,
274 x_rowid,
275 x_ytdor_id,
276 x_origination_id,
277 x_original_ssn,
278 x_original_name_cd,
279 x_attend_pell_id,
280 x_ed_use,
281 x_inst_cross_ref_code,
282 x_action_code,
283 x_accpt_awd_amt,
284 x_accpt_disb_dt1,
285 x_accpt_disb_dt2,
286 x_accpt_disb_dt3,
287 x_accpt_disb_dt4,
288 x_accpt_disb_dt5,
289 x_accpt_disb_dt6,
290 x_accpt_disb_dt7,
291 x_accpt_disb_dt8,
292 x_accpt_disb_dt9,
293 x_accpt_disb_dt10,
294 x_accpt_disb_dt11,
295 x_accpt_disb_dt12,
296 x_accpt_disb_dt13,
297 x_accpt_disb_dt14,
298 x_accpt_disb_dt15,
299 x_accpt_enrl_dt,
300 x_accpt_low_tut_flg,
301 x_accpt_ver_stat_flg,
302 x_accpt_incr_pell_cd,
303 x_accpt_tran_num,
304 x_accpt_efc,
305 x_accpt_sec_efc,
306 x_accpt_acad_cal,
307 x_accpt_pymt_method,
308 x_accpt_coa,
309 x_accpt_enrl_stat,
310 x_accpt_wks_inst_pymt,
311 x_wk_inst_time_calc_pymt,
312 x_accpt_wks_acad,
313 x_accpt_cr_acad_yr,
314 x_inst_seq_num,
315 x_sch_full_time_pell,
316 x_stud_name,
317 x_ssn,
318 x_stud_dob,
319 x_cps_ver_sel_cd,
320 x_ytd_disb_amt,
321 x_batch_id,
322 x_process_date,
323 x_creation_date,
324 x_created_by,
325 x_last_update_date,
326 x_last_updated_by,
327 x_last_update_login,
328 x_ci_cal_type,
329 x_ci_sequence_number
330 );
331
332 IF (p_action = 'INSERT') THEN
333 -- Call all the procedures related to Before Insert.
334 IF ( get_pk_for_validation(
335 new_references.ytdor_id
336 )
337 ) THEN
338 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
339 igs_ge_msg_stack.add;
340 app_exception.raise_exception;
341 END IF;
342 ELSIF (p_action = 'VALIDATE_INSERT') THEN
343 -- Call all the procedures related to Before Insert.
344 IF ( get_pk_for_validation (
345 new_references.ytdor_id
346 )
347 ) THEN
348 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
349 igs_ge_msg_stack.add;
350 app_exception.raise_exception;
351 END IF;
352 END IF;
353
354 END before_dml;
355
356
357 PROCEDURE insert_row (
358 x_rowid IN OUT NOCOPY VARCHAR2,
359 x_ytdor_id IN OUT NOCOPY NUMBER,
360 x_origination_id IN VARCHAR2,
361 x_original_ssn IN VARCHAR2,
362 x_original_name_cd IN VARCHAR2,
363 x_attend_pell_id IN VARCHAR2,
364 x_ed_use IN VARCHAR2,
365 x_inst_cross_ref_code IN VARCHAR2,
366 x_action_code IN VARCHAR2,
367 x_accpt_awd_amt IN NUMBER,
368 x_accpt_disb_dt1 IN DATE,
369 x_accpt_disb_dt2 IN DATE,
370 x_accpt_disb_dt3 IN DATE,
371 x_accpt_disb_dt4 IN DATE,
372 x_accpt_disb_dt5 IN DATE,
373 x_accpt_disb_dt6 IN DATE,
374 x_accpt_disb_dt7 IN DATE,
375 x_accpt_disb_dt8 IN DATE,
376 x_accpt_disb_dt9 IN DATE,
377 x_accpt_disb_dt10 IN DATE,
378 x_accpt_disb_dt11 IN DATE,
379 x_accpt_disb_dt12 IN DATE,
380 x_accpt_disb_dt13 IN DATE,
381 x_accpt_disb_dt14 IN DATE,
382 x_accpt_disb_dt15 IN DATE,
383 x_accpt_enrl_dt IN DATE,
384 x_accpt_low_tut_flg IN VARCHAR2,
385 x_accpt_ver_stat_flg IN VARCHAR2,
386 x_accpt_incr_pell_cd IN VARCHAR2,
387 x_accpt_tran_num IN VARCHAR2,
388 x_accpt_efc IN NUMBER,
389 x_accpt_sec_efc IN VARCHAR2,
390 x_accpt_acad_cal IN VARCHAR2,
391 x_accpt_pymt_method IN VARCHAR2,
392 x_accpt_coa IN NUMBER,
393 x_accpt_enrl_stat IN VARCHAR2,
394 x_accpt_wks_inst_pymt IN VARCHAR2,
395 x_wk_inst_time_calc_pymt IN NUMBER,
396 x_accpt_wks_acad IN VARCHAR2,
397 x_accpt_cr_acad_yr IN VARCHAR2,
398 x_inst_seq_num IN VARCHAR2,
399 x_sch_full_time_pell IN NUMBER,
400 x_stud_name IN VARCHAR2,
401 x_ssn IN VARCHAR2,
402 x_stud_dob IN DATE,
403 x_cps_ver_sel_cd IN VARCHAR2,
404 x_ytd_disb_amt IN NUMBER,
405 x_batch_id IN VARCHAR2,
406 x_process_date IN DATE,
407 x_mode IN VARCHAR2,
408 x_ci_cal_type IN VARCHAR2,
409 x_ci_sequence_number IN NUMBER
410 ) AS
411 /*
412 || Created By : avenkatr
413 || Created On : 21-DEC-2000
414 || Purpose : Handles the INSERT DML logic for the table.
415 || Known limitations, enhancements or remarks :
416 || Change History :
417 || Who When What
418 || (reverse chronological order - newest change first)
419 */
420 CURSOR c IS
421 SELECT rowid
422 FROM igf_gr_ytd_orig_all
423 WHERE ytdor_id = x_ytdor_id;
424
425 x_last_update_date DATE;
426 x_last_updated_by NUMBER;
427 x_last_update_login NUMBER;
428 x_request_id NUMBER;
429 x_program_id NUMBER;
430 x_program_application_id NUMBER;
431 x_program_update_date DATE;
432 l_org_id igf_gr_ytd_orig_all.org_id%TYPE DEFAULT igf_aw_gen.get_org_id;
433
434 BEGIN
435
436 x_last_update_date := SYSDATE;
437 IF (x_mode = 'I') THEN
438 x_last_updated_by := 1;
439 x_last_update_login := 0;
440 ELSIF (x_mode = 'R') THEN
441 x_last_updated_by := fnd_global.user_id;
442 IF (x_last_updated_by IS NULL) THEN
443 x_last_updated_by := -1;
444 END IF;
445 x_last_update_login := fnd_global.login_id;
446 IF (x_last_update_login IS NULL) THEN
447 x_last_update_login := -1;
448 END IF;
449 x_request_id := fnd_global.conc_request_id;
450 x_program_id := fnd_global.conc_program_id;
451 x_program_application_id := fnd_global.prog_appl_id;
452
453 IF (x_request_id = -1) THEN
454 x_request_id := NULL;
455 x_program_id := NULL;
456 x_program_application_id := NULL;
457 x_program_update_date := NULL;
458 ELSE
459 x_program_update_date := SYSDATE;
460 END IF;
461 ELSE
462 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
463 igs_ge_msg_stack.add;
464 app_exception.raise_exception;
465 END IF;
466
467 SELECT igf_gr_ytd_orig_s.nextval INTO x_ytdor_id FROM dual;
468
469 before_dml(
470 p_action => 'INSERT',
471 x_rowid => x_rowid,
472 x_ytdor_id => x_ytdor_id,
473 x_origination_id => x_origination_id,
474 x_original_ssn => x_original_ssn,
475 x_original_name_cd => x_original_name_cd,
476 x_attend_pell_id => x_attend_pell_id,
477 x_ed_use => x_ed_use,
478 x_inst_cross_ref_code => x_inst_cross_ref_code,
479 x_action_code => x_action_code,
480 x_accpt_awd_amt => x_accpt_awd_amt,
481 x_accpt_disb_dt1 => x_accpt_disb_dt1,
482 x_accpt_disb_dt2 => x_accpt_disb_dt2,
483 x_accpt_disb_dt3 => x_accpt_disb_dt3,
484 x_accpt_disb_dt4 => x_accpt_disb_dt4,
485 x_accpt_disb_dt5 => x_accpt_disb_dt5,
486 x_accpt_disb_dt6 => x_accpt_disb_dt6,
487 x_accpt_disb_dt7 => x_accpt_disb_dt7,
488 x_accpt_disb_dt8 => x_accpt_disb_dt8,
489 x_accpt_disb_dt9 => x_accpt_disb_dt9,
490 x_accpt_disb_dt10 => x_accpt_disb_dt10,
491 x_accpt_disb_dt11 => x_accpt_disb_dt11,
492 x_accpt_disb_dt12 => x_accpt_disb_dt12,
493 x_accpt_disb_dt13 => x_accpt_disb_dt13,
494 x_accpt_disb_dt14 => x_accpt_disb_dt14,
495 x_accpt_disb_dt15 => x_accpt_disb_dt15,
496 x_accpt_enrl_dt => x_accpt_enrl_dt,
497 x_accpt_low_tut_flg => x_accpt_low_tut_flg,
498 x_accpt_ver_stat_flg => x_accpt_ver_stat_flg,
499 x_accpt_incr_pell_cd => x_accpt_incr_pell_cd,
500 x_accpt_tran_num => x_accpt_tran_num,
501 x_accpt_efc => x_accpt_efc,
502 x_accpt_sec_efc => x_accpt_sec_efc,
503 x_accpt_acad_cal => x_accpt_acad_cal,
504 x_accpt_pymt_method => x_accpt_pymt_method,
505 x_accpt_coa => x_accpt_coa,
506 x_accpt_enrl_stat => x_accpt_enrl_stat,
507 x_accpt_wks_inst_pymt => x_accpt_wks_inst_pymt,
508 x_wk_inst_time_calc_pymt => x_wk_inst_time_calc_pymt,
509 x_accpt_wks_acad => x_accpt_wks_acad,
510 x_accpt_cr_acad_yr => x_accpt_cr_acad_yr,
511 x_inst_seq_num => x_inst_seq_num,
512 x_sch_full_time_pell => x_sch_full_time_pell,
513 x_stud_name => x_stud_name,
514 x_ssn => x_ssn,
515 x_stud_dob => x_stud_dob,
516 x_cps_ver_sel_cd => x_cps_ver_sel_cd,
517 x_ytd_disb_amt => x_ytd_disb_amt,
518 x_batch_id => x_batch_id,
519 x_process_date => x_process_date,
520 x_creation_date => x_last_update_date,
521 x_created_by => x_last_updated_by,
522 x_last_update_date => x_last_update_date,
523 x_last_updated_by => x_last_updated_by,
524 x_last_update_login => x_last_update_login,
525 x_ci_cal_type => x_ci_cal_type,
526 x_ci_sequence_number => x_ci_sequence_number
527 );
528
529 INSERT INTO igf_gr_ytd_orig_all (
530 ytdor_id,
531 origination_id,
532 original_ssn,
533 original_name_cd,
534 attend_pell_id,
535 ed_use,
536 inst_cross_ref_code,
537 action_code,
538 accpt_awd_amt,
539 accpt_disb_dt1,
540 accpt_disb_dt2,
541 accpt_disb_dt3,
542 accpt_disb_dt4,
543 accpt_disb_dt5,
544 accpt_disb_dt6,
545 accpt_disb_dt7,
546 accpt_disb_dt8,
547 accpt_disb_dt9,
548 accpt_disb_dt10,
549 accpt_disb_dt11,
550 accpt_disb_dt12,
551 accpt_disb_dt13,
552 accpt_disb_dt14,
553 accpt_disb_dt15,
554 accpt_enrl_dt,
555 accpt_low_tut_flg,
556 accpt_ver_stat_flg,
557 accpt_incr_pell_cd,
558 accpt_tran_num,
559 accpt_efc,
560 accpt_sec_efc,
561 accpt_acad_cal,
562 accpt_pymt_method,
563 accpt_coa,
564 accpt_enrl_stat,
565 accpt_wks_inst_pymt,
566 wk_inst_time_calc_pymt,
567 accpt_wks_acad,
568 accpt_cr_acad_yr,
569 inst_seq_num,
570 sch_full_time_pell,
571 stud_name,
572 ssn,
573 stud_dob,
574 cps_ver_sel_cd,
575 ytd_disb_amt,
576 batch_id,
577 process_date,
578 creation_date,
579 created_by,
580 last_update_date,
581 last_updated_by,
582 last_update_login,
583 request_id,
584 program_id,
585 program_application_id,
586 program_update_date,
587 org_id,
588 ci_cal_type,
589 ci_sequence_number
590 ) VALUES (
591 new_references.ytdor_id,
592 new_references.origination_id,
593 new_references.original_ssn,
594 new_references.original_name_cd,
595 new_references.attend_pell_id,
596 new_references.ed_use,
597 new_references.inst_cross_ref_code,
598 new_references.action_code,
599 new_references.accpt_awd_amt,
600 new_references.accpt_disb_dt1,
601 new_references.accpt_disb_dt2,
602 new_references.accpt_disb_dt3,
603 new_references.accpt_disb_dt4,
604 new_references.accpt_disb_dt5,
605 new_references.accpt_disb_dt6,
606 new_references.accpt_disb_dt7,
607 new_references.accpt_disb_dt8,
608 new_references.accpt_disb_dt9,
609 new_references.accpt_disb_dt10,
610 new_references.accpt_disb_dt11,
611 new_references.accpt_disb_dt12,
612 new_references.accpt_disb_dt13,
613 new_references.accpt_disb_dt14,
614 new_references.accpt_disb_dt15,
615 new_references.accpt_enrl_dt,
616 new_references.accpt_low_tut_flg,
617 new_references.accpt_ver_stat_flg,
618 new_references.accpt_incr_pell_cd,
619 new_references.accpt_tran_num,
620 new_references.accpt_efc,
621 new_references.accpt_sec_efc,
622 new_references.accpt_acad_cal,
623 new_references.accpt_pymt_method,
624 new_references.accpt_coa,
625 new_references.accpt_enrl_stat,
626 new_references.accpt_wks_inst_pymt,
627 new_references.wk_inst_time_calc_pymt,
628 new_references.accpt_wks_acad,
629 new_references.accpt_cr_acad_yr,
630 new_references.inst_seq_num,
631 new_references.sch_full_time_pell,
632 new_references.stud_name,
633 new_references.ssn,
634 new_references.stud_dob,
635 new_references.cps_ver_sel_cd,
636 new_references.ytd_disb_amt,
637 new_references.batch_id,
638 new_references.process_date,
639 x_last_update_date,
640 x_last_updated_by,
641 x_last_update_date,
642 x_last_updated_by,
643 x_last_update_login ,
644 x_request_id,
645 x_program_id,
646 x_program_application_id,
647 x_program_update_date,
648 l_org_id,
649 new_references.ci_cal_type,
650 new_references.ci_sequence_number
651 );
652
653 OPEN c;
654 FETCH c INTO x_rowid;
655 IF (c%NOTFOUND) THEN
656 CLOSE c;
657 RAISE NO_DATA_FOUND;
658 END IF;
659 CLOSE c;
660
661 END insert_row;
662
663
664 PROCEDURE lock_row (
665 x_rowid IN VARCHAR2,
666 x_ytdor_id IN NUMBER,
667 x_origination_id IN VARCHAR2,
668 x_original_ssn IN VARCHAR2,
669 x_original_name_cd IN VARCHAR2,
670 x_attend_pell_id IN VARCHAR2,
671 x_ed_use IN VARCHAR2,
672 x_inst_cross_ref_code IN VARCHAR2,
673 x_action_code IN VARCHAR2,
674 x_accpt_awd_amt IN NUMBER,
675 x_accpt_disb_dt1 IN DATE,
676 x_accpt_disb_dt2 IN DATE,
677 x_accpt_disb_dt3 IN DATE,
678 x_accpt_disb_dt4 IN DATE,
679 x_accpt_disb_dt5 IN DATE,
680 x_accpt_disb_dt6 IN DATE,
681 x_accpt_disb_dt7 IN DATE,
682 x_accpt_disb_dt8 IN DATE,
683 x_accpt_disb_dt9 IN DATE,
684 x_accpt_disb_dt10 IN DATE,
685 x_accpt_disb_dt11 IN DATE,
686 x_accpt_disb_dt12 IN DATE,
687 x_accpt_disb_dt13 IN DATE,
688 x_accpt_disb_dt14 IN DATE,
689 x_accpt_disb_dt15 IN DATE,
690 x_accpt_enrl_dt IN DATE,
691 x_accpt_low_tut_flg IN VARCHAR2,
692 x_accpt_ver_stat_flg IN VARCHAR2,
693 x_accpt_incr_pell_cd IN VARCHAR2,
694 x_accpt_tran_num IN VARCHAR2,
695 x_accpt_efc IN NUMBER,
696 x_accpt_sec_efc IN VARCHAR2,
697 x_accpt_acad_cal IN VARCHAR2,
698 x_accpt_pymt_method IN VARCHAR2,
699 x_accpt_coa IN NUMBER,
700 x_accpt_enrl_stat IN VARCHAR2,
701 x_accpt_wks_inst_pymt IN VARCHAR2,
702 x_wk_inst_time_calc_pymt IN NUMBER,
703 x_accpt_wks_acad IN VARCHAR2,
704 x_accpt_cr_acad_yr IN VARCHAR2,
705 x_inst_seq_num IN VARCHAR2,
706 x_sch_full_time_pell IN NUMBER,
707 x_stud_name IN VARCHAR2,
708 x_ssn IN VARCHAR2,
709 x_stud_dob IN DATE,
710 x_cps_ver_sel_cd IN VARCHAR2,
711 x_ytd_disb_amt IN NUMBER,
712 x_batch_id IN VARCHAR2,
713 x_process_date IN DATE,
714 x_ci_cal_type IN VARCHAR2,
715 x_ci_sequence_number IN NUMBER
716 ) AS
717 /*
718 || Created By : avenkatr
719 || Created On : 21-DEC-2000
720 || Purpose : Handles the LOCK mechanism for the table.
721 || Known limitations, enhancements or remarks :
722 || Change History :
723 || Who When What
724 || (reverse chronological order - newest change first)
725 */
726 CURSOR c1 IS
727 SELECT
728 origination_id,
729 original_ssn,
730 original_name_cd,
731 attend_pell_id,
732 ed_use,
733 inst_cross_ref_code,
734 action_code,
735 accpt_awd_amt,
736 accpt_disb_dt1,
737 accpt_disb_dt2,
738 accpt_disb_dt3,
739 accpt_disb_dt4,
740 accpt_disb_dt5,
741 accpt_disb_dt6,
742 accpt_disb_dt7,
743 accpt_disb_dt8,
744 accpt_disb_dt9,
745 accpt_disb_dt10,
746 accpt_disb_dt11,
747 accpt_disb_dt12,
748 accpt_disb_dt13,
749 accpt_disb_dt14,
750 accpt_disb_dt15,
751 accpt_enrl_dt,
752 accpt_low_tut_flg,
753 accpt_ver_stat_flg,
754 accpt_incr_pell_cd,
755 accpt_tran_num,
756 accpt_efc,
757 accpt_sec_efc,
758 accpt_acad_cal,
759 accpt_pymt_method,
760 accpt_coa,
761 accpt_enrl_stat,
762 accpt_wks_inst_pymt,
763 wk_inst_time_calc_pymt,
764 accpt_wks_acad,
765 accpt_cr_acad_yr,
766 inst_seq_num,
767 sch_full_time_pell,
768 stud_name,
769 ssn,
770 stud_dob,
771 cps_ver_sel_cd,
772 ytd_disb_amt,
773 batch_id,
774 process_date,
775 ci_cal_type,
776 ci_sequence_number
777 FROM igf_gr_ytd_orig_all
778 WHERE rowid = x_rowid
779 FOR UPDATE NOWAIT;
780
781 tlinfo c1%ROWTYPE;
782
783 BEGIN
784
785 OPEN c1;
786 FETCH c1 INTO tlinfo;
787 IF (c1%notfound) THEN
788 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
789 igs_ge_msg_stack.add;
790 CLOSE c1;
791 app_exception.raise_exception;
792 RETURN;
793 END IF;
794 CLOSE c1;
795
796 IF (
797 ((tlinfo.origination_id = x_origination_id) OR ((tlinfo.origination_id IS NULL) AND (X_origination_id IS NULL)))
798 AND ((tlinfo.original_ssn = x_original_ssn) OR ((tlinfo.original_ssn IS NULL) AND (X_original_ssn IS NULL)))
799 AND ((tlinfo.original_name_cd = x_original_name_cd) OR ((tlinfo.original_name_cd IS NULL) AND (X_original_name_cd IS NULL)))
800 AND ((tlinfo.attend_pell_id = x_attend_pell_id) OR ((tlinfo.attend_pell_id IS NULL) AND (X_attend_pell_id IS NULL)))
801 AND ((tlinfo.ed_use = x_ed_use) OR ((tlinfo.ed_use IS NULL) AND (X_ed_use IS NULL)))
802 AND ((tlinfo.inst_cross_ref_code = x_inst_cross_ref_code) OR ((tlinfo.inst_cross_ref_code IS NULL) AND (X_inst_cross_ref_code IS NULL)))
803 AND ((tlinfo.action_code = x_action_code) OR ((tlinfo.action_code IS NULL) AND (X_action_code IS NULL)))
804 AND ((tlinfo.accpt_awd_amt = x_accpt_awd_amt) OR ((tlinfo.accpt_awd_amt IS NULL) AND (X_accpt_awd_amt IS NULL)))
805 AND ((tlinfo.accpt_disb_dt1 = x_accpt_disb_dt1) OR ((tlinfo.accpt_disb_dt1 IS NULL) AND (X_accpt_disb_dt1 IS NULL)))
806 AND ((tlinfo.accpt_disb_dt2 = x_accpt_disb_dt2) OR ((tlinfo.accpt_disb_dt2 IS NULL) AND (X_accpt_disb_dt2 IS NULL)))
807 AND ((tlinfo.accpt_disb_dt3 = x_accpt_disb_dt3) OR ((tlinfo.accpt_disb_dt3 IS NULL) AND (X_accpt_disb_dt3 IS NULL)))
808 AND ((tlinfo.accpt_disb_dt4 = x_accpt_disb_dt4) OR ((tlinfo.accpt_disb_dt4 IS NULL) AND (X_accpt_disb_dt4 IS NULL)))
809 AND ((tlinfo.accpt_disb_dt5 = x_accpt_disb_dt5) OR ((tlinfo.accpt_disb_dt5 IS NULL) AND (X_accpt_disb_dt5 IS NULL)))
810 AND ((tlinfo.accpt_disb_dt6 = x_accpt_disb_dt6) OR ((tlinfo.accpt_disb_dt6 IS NULL) AND (X_accpt_disb_dt6 IS NULL)))
811 AND ((tlinfo.accpt_disb_dt7 = x_accpt_disb_dt7) OR ((tlinfo.accpt_disb_dt7 IS NULL) AND (X_accpt_disb_dt7 IS NULL)))
812 AND ((tlinfo.accpt_disb_dt8 = x_accpt_disb_dt8) OR ((tlinfo.accpt_disb_dt8 IS NULL) AND (X_accpt_disb_dt8 IS NULL)))
813 AND ((tlinfo.accpt_disb_dt9 = x_accpt_disb_dt9) OR ((tlinfo.accpt_disb_dt9 IS NULL) AND (X_accpt_disb_dt9 IS NULL)))
814 AND ((tlinfo.accpt_disb_dt10 = x_accpt_disb_dt10) OR ((tlinfo.accpt_disb_dt10 IS NULL) AND (X_accpt_disb_dt10 IS NULL)))
815 AND ((tlinfo.accpt_disb_dt11 = x_accpt_disb_dt11) OR ((tlinfo.accpt_disb_dt11 IS NULL) AND (X_accpt_disb_dt11 IS NULL)))
816 AND ((tlinfo.accpt_disb_dt12 = x_accpt_disb_dt12) OR ((tlinfo.accpt_disb_dt12 IS NULL) AND (X_accpt_disb_dt12 IS NULL)))
817 AND ((tlinfo.accpt_disb_dt13 = x_accpt_disb_dt13) OR ((tlinfo.accpt_disb_dt13 IS NULL) AND (X_accpt_disb_dt13 IS NULL)))
818 AND ((tlinfo.accpt_disb_dt14 = x_accpt_disb_dt14) OR ((tlinfo.accpt_disb_dt14 IS NULL) AND (X_accpt_disb_dt14 IS NULL)))
819 AND ((tlinfo.accpt_disb_dt15 = x_accpt_disb_dt15) OR ((tlinfo.accpt_disb_dt15 IS NULL) AND (X_accpt_disb_dt15 IS NULL)))
820 AND ((tlinfo.accpt_enrl_dt = x_accpt_enrl_dt) OR ((tlinfo.accpt_enrl_dt IS NULL) AND (X_accpt_enrl_dt IS NULL)))
821 AND ((tlinfo.accpt_low_tut_flg = x_accpt_low_tut_flg) OR ((tlinfo.accpt_low_tut_flg IS NULL) AND (X_accpt_low_tut_flg IS NULL)))
822 AND ((tlinfo.accpt_ver_stat_flg = x_accpt_ver_stat_flg) OR ((tlinfo.accpt_ver_stat_flg IS NULL) AND (X_accpt_ver_stat_flg IS NULL)))
823 AND ((tlinfo.accpt_incr_pell_cd = x_accpt_incr_pell_cd) OR ((tlinfo.accpt_incr_pell_cd IS NULL) AND (X_accpt_incr_pell_cd IS NULL)))
824 AND ((tlinfo.accpt_tran_num = x_accpt_tran_num) OR ((tlinfo.accpt_tran_num IS NULL) AND (X_accpt_tran_num IS NULL)))
825 AND ((tlinfo.accpt_efc = x_accpt_efc) OR ((tlinfo.accpt_efc IS NULL) AND (X_accpt_efc IS NULL)))
826 AND ((tlinfo.accpt_sec_efc = x_accpt_sec_efc) OR ((tlinfo.accpt_sec_efc IS NULL) AND (X_accpt_sec_efc IS NULL)))
827 AND ((tlinfo.accpt_acad_cal = x_accpt_acad_cal) OR ((tlinfo.accpt_acad_cal IS NULL) AND (X_accpt_acad_cal IS NULL)))
828 AND ((tlinfo.accpt_pymt_method = x_accpt_pymt_method) OR ((tlinfo.accpt_pymt_method IS NULL) AND (X_accpt_pymt_method IS NULL)))
829 AND ((tlinfo.accpt_coa = x_accpt_coa) OR ((tlinfo.accpt_coa IS NULL) AND (X_accpt_coa IS NULL)))
830 AND ((tlinfo.accpt_enrl_stat = x_accpt_enrl_stat) OR ((tlinfo.accpt_enrl_stat IS NULL) AND (X_accpt_enrl_stat IS NULL)))
831 AND ((tlinfo.accpt_wks_inst_pymt = x_accpt_wks_inst_pymt) OR ((tlinfo.accpt_wks_inst_pymt IS NULL) AND (X_accpt_wks_inst_pymt IS NULL)))
832 AND ((tlinfo.wk_inst_time_calc_pymt = x_wk_inst_time_calc_pymt) OR ((tlinfo.wk_inst_time_calc_pymt IS NULL) AND (X_wk_inst_time_calc_pymt IS NULL)))
833 AND ((tlinfo.accpt_wks_acad = x_accpt_wks_acad) OR ((tlinfo.accpt_wks_acad IS NULL) AND (X_accpt_wks_acad IS NULL)))
834 AND ((tlinfo.accpt_cr_acad_yr = x_accpt_cr_acad_yr) OR ((tlinfo.accpt_cr_acad_yr IS NULL) AND (X_accpt_cr_acad_yr IS NULL)))
835 AND ((tlinfo.inst_seq_num = x_inst_seq_num) OR ((tlinfo.inst_seq_num IS NULL) AND (X_inst_seq_num IS NULL)))
836 AND ((tlinfo.sch_full_time_pell = x_sch_full_time_pell) OR ((tlinfo.sch_full_time_pell IS NULL) AND (X_sch_full_time_pell IS NULL)))
837 AND ((tlinfo.stud_name = x_stud_name) OR ((tlinfo.stud_name IS NULL) AND (X_stud_name IS NULL)))
838 AND ((tlinfo.ssn = x_ssn) OR ((tlinfo.ssn IS NULL) AND (X_ssn IS NULL)))
839 AND ((tlinfo.stud_dob = x_stud_dob) OR ((tlinfo.stud_dob IS NULL) AND (X_stud_dob IS NULL)))
840 AND ((tlinfo.cps_ver_sel_cd = x_cps_ver_sel_cd) OR ((tlinfo.cps_ver_sel_cd IS NULL) AND (X_cps_ver_sel_cd IS NULL)))
841 AND ((tlinfo.ytd_disb_amt = x_ytd_disb_amt) OR ((tlinfo.ytd_disb_amt IS NULL) AND (X_ytd_disb_amt IS NULL)))
842 AND ((tlinfo.batch_id = x_batch_id) OR ((tlinfo.batch_id IS NULL) AND (X_batch_id IS NULL)))
843 AND ((tlinfo.process_date = x_process_date) OR ((tlinfo.process_date IS NULL) AND (X_process_date IS NULL)))
844 AND ((tlinfo.ci_cal_type = x_ci_cal_type) OR ((tlinfo.ci_cal_type IS NULL) AND (X_ci_cal_type IS NULL)))
845 AND ((tlinfo.ci_sequence_number = x_ci_sequence_number) OR ((tlinfo.ci_sequence_number IS NULL) AND (X_ci_sequence_number IS NULL)))
846 ) THEN
847 NULL;
848 ELSE
849 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
850 igs_ge_msg_stack.add;
851 app_exception.raise_exception;
852 END IF;
853
854 RETURN;
855
856 END lock_row;
857
858
859 PROCEDURE update_row (
860 x_rowid IN VARCHAR2,
861 x_ytdor_id IN NUMBER,
862 x_origination_id IN VARCHAR2,
863 x_original_ssn IN VARCHAR2,
864 x_original_name_cd IN VARCHAR2,
865 x_attend_pell_id IN VARCHAR2,
866 x_ed_use IN VARCHAR2,
867 x_inst_cross_ref_code IN VARCHAR2,
868 x_action_code IN VARCHAR2,
869 x_accpt_awd_amt IN NUMBER,
870 x_accpt_disb_dt1 IN DATE,
871 x_accpt_disb_dt2 IN DATE,
872 x_accpt_disb_dt3 IN DATE,
873 x_accpt_disb_dt4 IN DATE,
874 x_accpt_disb_dt5 IN DATE,
875 x_accpt_disb_dt6 IN DATE,
876 x_accpt_disb_dt7 IN DATE,
877 x_accpt_disb_dt8 IN DATE,
878 x_accpt_disb_dt9 IN DATE,
879 x_accpt_disb_dt10 IN DATE,
880 x_accpt_disb_dt11 IN DATE,
881 x_accpt_disb_dt12 IN DATE,
882 x_accpt_disb_dt13 IN DATE,
883 x_accpt_disb_dt14 IN DATE,
884 x_accpt_disb_dt15 IN DATE,
885 x_accpt_enrl_dt IN DATE,
886 x_accpt_low_tut_flg IN VARCHAR2,
887 x_accpt_ver_stat_flg IN VARCHAR2,
888 x_accpt_incr_pell_cd IN VARCHAR2,
889 x_accpt_tran_num IN VARCHAR2,
890 x_accpt_efc IN NUMBER,
891 x_accpt_sec_efc IN VARCHAR2,
892 x_accpt_acad_cal IN VARCHAR2,
893 x_accpt_pymt_method IN VARCHAR2,
894 x_accpt_coa IN NUMBER,
895 x_accpt_enrl_stat IN VARCHAR2,
896 x_accpt_wks_inst_pymt IN VARCHAR2,
897 x_wk_inst_time_calc_pymt IN NUMBER,
898 x_accpt_wks_acad IN VARCHAR2,
899 x_accpt_cr_acad_yr IN VARCHAR2,
900 x_inst_seq_num IN VARCHAR2,
901 x_sch_full_time_pell IN NUMBER,
902 x_stud_name IN VARCHAR2,
903 x_ssn IN VARCHAR2,
904 x_stud_dob IN DATE,
905 x_cps_ver_sel_cd IN VARCHAR2,
906 x_ytd_disb_amt IN NUMBER,
907 x_batch_id IN VARCHAR2,
908 x_process_date IN DATE,
909 x_mode IN VARCHAR2,
910 x_ci_cal_type IN VARCHAR2,
911 x_ci_sequence_number IN NUMBER
912 ) AS
913 /*
914 || Created By : avenkatr
915 || Created On : 21-DEC-2000
916 || Purpose : Handles the UPDATE DML logic for the table.
917 || Known limitations, enhancements or remarks :
918 || Change History :
919 || Who When What
920 || (reverse chronological order - newest change first)
921 */
922 x_last_update_date DATE ;
923 x_last_updated_by NUMBER;
924 x_last_update_login NUMBER;
925 x_request_id NUMBER;
926 x_program_id NUMBER;
927 x_program_application_id NUMBER;
928 x_program_update_date DATE;
929
930 BEGIN
931
932 x_last_update_date := SYSDATE;
933 IF (X_MODE = 'I') THEN
934 x_last_updated_by := 1;
935 x_last_update_login := 0;
936 ELSIF (x_mode = 'R') THEN
937 x_last_updated_by := fnd_global.user_id;
938 IF x_last_updated_by IS NULL THEN
939 x_last_updated_by := -1;
940 END IF;
941 x_last_update_login := fnd_global.login_id;
942 IF (x_last_update_login IS NULL) THEN
943 x_last_update_login := -1;
944 END IF;
945 ELSE
946 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
947 igs_ge_msg_stack.add;
948 app_exception.raise_exception;
949 END IF;
950
951 before_dml(
952 p_action => 'UPDATE',
953 x_rowid => x_rowid,
954 x_ytdor_id => x_ytdor_id,
955 x_origination_id => x_origination_id,
956 x_original_ssn => x_original_ssn,
957 x_original_name_cd => x_original_name_cd,
958 x_attend_pell_id => x_attend_pell_id,
959 x_ed_use => x_ed_use,
960 x_inst_cross_ref_code => x_inst_cross_ref_code,
961 x_action_code => x_action_code,
962 x_accpt_awd_amt => x_accpt_awd_amt,
963 x_accpt_disb_dt1 => x_accpt_disb_dt1,
964 x_accpt_disb_dt2 => x_accpt_disb_dt2,
965 x_accpt_disb_dt3 => x_accpt_disb_dt3,
966 x_accpt_disb_dt4 => x_accpt_disb_dt4,
967 x_accpt_disb_dt5 => x_accpt_disb_dt5,
968 x_accpt_disb_dt6 => x_accpt_disb_dt6,
969 x_accpt_disb_dt7 => x_accpt_disb_dt7,
970 x_accpt_disb_dt8 => x_accpt_disb_dt8,
971 x_accpt_disb_dt9 => x_accpt_disb_dt9,
972 x_accpt_disb_dt10 => x_accpt_disb_dt10,
973 x_accpt_disb_dt11 => x_accpt_disb_dt11,
974 x_accpt_disb_dt12 => x_accpt_disb_dt12,
975 x_accpt_disb_dt13 => x_accpt_disb_dt13,
976 x_accpt_disb_dt14 => x_accpt_disb_dt14,
977 x_accpt_disb_dt15 => x_accpt_disb_dt15,
978 x_accpt_enrl_dt => x_accpt_enrl_dt,
979 x_accpt_low_tut_flg => x_accpt_low_tut_flg,
980 x_accpt_ver_stat_flg => x_accpt_ver_stat_flg,
981 x_accpt_incr_pell_cd => x_accpt_incr_pell_cd,
982 x_accpt_tran_num => x_accpt_tran_num,
983 x_accpt_efc => x_accpt_efc,
984 x_accpt_sec_efc => x_accpt_sec_efc,
985 x_accpt_acad_cal => x_accpt_acad_cal,
986 x_accpt_pymt_method => x_accpt_pymt_method,
987 x_accpt_coa => x_accpt_coa,
988 x_accpt_enrl_stat => x_accpt_enrl_stat,
989 x_accpt_wks_inst_pymt => x_accpt_wks_inst_pymt,
990 x_wk_inst_time_calc_pymt => x_wk_inst_time_calc_pymt,
991 x_accpt_wks_acad => x_accpt_wks_acad,
992 x_accpt_cr_acad_yr => x_accpt_cr_acad_yr,
993 x_inst_seq_num => x_inst_seq_num,
994 x_sch_full_time_pell => x_sch_full_time_pell,
995 x_stud_name => x_stud_name,
996 x_ssn => x_ssn,
997 x_stud_dob => x_stud_dob,
998 x_cps_ver_sel_cd => x_cps_ver_sel_cd,
999 x_ytd_disb_amt => x_ytd_disb_amt,
1000 x_batch_id => x_batch_id,
1001 x_process_date => x_process_date,
1002 x_creation_date => x_last_update_date,
1003 x_created_by => x_last_updated_by,
1004 x_last_update_date => x_last_update_date,
1005 x_last_updated_by => x_last_updated_by,
1006 x_last_update_login => x_last_update_login,
1007 x_ci_cal_type => x_ci_cal_type,
1008 x_ci_sequence_number => x_ci_sequence_number
1009 );
1010
1011 IF (x_mode = 'R') THEN
1012 x_request_id := fnd_global.conc_request_id;
1013 x_program_id := fnd_global.conc_program_id;
1014 x_program_application_id := fnd_global.prog_appl_id;
1015 IF (x_request_id = -1) THEN
1016 x_request_id := old_references.request_id;
1017 x_program_id := old_references.program_id;
1018 x_program_application_id := old_references.program_application_id;
1019 x_program_update_date := old_references.program_update_date;
1020 ELSE
1021 x_program_update_date := SYSDATE;
1022 END IF;
1023 END IF;
1024
1025 UPDATE igf_gr_ytd_orig_all
1026 SET
1027 origination_id = new_references.origination_id,
1028 original_ssn = new_references.original_ssn,
1029 original_name_cd = new_references.original_name_cd,
1030 attend_pell_id = new_references.attend_pell_id,
1031 ed_use = new_references.ed_use,
1032 inst_cross_ref_code = new_references.inst_cross_ref_code,
1033 action_code = new_references.action_code,
1034 accpt_awd_amt = new_references.accpt_awd_amt,
1035 accpt_disb_dt1 = new_references.accpt_disb_dt1,
1036 accpt_disb_dt2 = new_references.accpt_disb_dt2,
1037 accpt_disb_dt3 = new_references.accpt_disb_dt3,
1038 accpt_disb_dt4 = new_references.accpt_disb_dt4,
1039 accpt_disb_dt5 = new_references.accpt_disb_dt5,
1040 accpt_disb_dt6 = new_references.accpt_disb_dt6,
1041 accpt_disb_dt7 = new_references.accpt_disb_dt7,
1042 accpt_disb_dt8 = new_references.accpt_disb_dt8,
1043 accpt_disb_dt9 = new_references.accpt_disb_dt9,
1044 accpt_disb_dt10 = new_references.accpt_disb_dt10,
1045 accpt_disb_dt11 = new_references.accpt_disb_dt11,
1046 accpt_disb_dt12 = new_references.accpt_disb_dt12,
1047 accpt_disb_dt13 = new_references.accpt_disb_dt13,
1048 accpt_disb_dt14 = new_references.accpt_disb_dt14,
1049 accpt_disb_dt15 = new_references.accpt_disb_dt15,
1050 accpt_enrl_dt = new_references.accpt_enrl_dt,
1051 accpt_low_tut_flg = new_references.accpt_low_tut_flg,
1052 accpt_ver_stat_flg = new_references.accpt_ver_stat_flg,
1053 accpt_incr_pell_cd = new_references.accpt_incr_pell_cd,
1054 accpt_tran_num = new_references.accpt_tran_num,
1055 accpt_efc = new_references.accpt_efc,
1056 accpt_sec_efc = new_references.accpt_sec_efc,
1057 accpt_acad_cal = new_references.accpt_acad_cal,
1058 accpt_pymt_method = new_references.accpt_pymt_method,
1059 accpt_coa = new_references.accpt_coa,
1060 accpt_enrl_stat = new_references.accpt_enrl_stat,
1061 accpt_wks_inst_pymt = new_references.accpt_wks_inst_pymt,
1062 wk_inst_time_calc_pymt = new_references.wk_inst_time_calc_pymt,
1063 accpt_wks_acad = new_references.accpt_wks_acad,
1064 accpt_cr_acad_yr = new_references.accpt_cr_acad_yr,
1065 inst_seq_num = new_references.inst_seq_num,
1066 sch_full_time_pell = new_references.sch_full_time_pell,
1067 stud_name = new_references.stud_name,
1068 ssn = new_references.ssn,
1069 stud_dob = new_references.stud_dob,
1070 cps_ver_sel_cd = new_references.cps_ver_sel_cd,
1071 ytd_disb_amt = new_references.ytd_disb_amt,
1072 batch_id = new_references.batch_id,
1073 process_date = new_references.process_date,
1074 last_update_date = x_last_update_date,
1075 last_updated_by = x_last_updated_by,
1076 last_update_login = x_last_update_login ,
1077 request_id = x_request_id,
1078 program_id = x_program_id,
1079 program_application_id = x_program_application_id,
1080 program_update_date = x_program_update_date,
1081 ci_cal_type = x_ci_cal_type,
1082 ci_sequence_number = x_ci_sequence_number
1083 WHERE rowid = x_rowid;
1084
1085 IF (SQL%NOTFOUND) THEN
1086 RAISE NO_DATA_FOUND;
1087 END IF;
1088
1089 END update_row;
1090
1091
1092 PROCEDURE add_row (
1093 x_rowid IN OUT NOCOPY VARCHAR2,
1094 x_ytdor_id IN OUT NOCOPY NUMBER,
1095 x_origination_id IN VARCHAR2,
1096 x_original_ssn IN VARCHAR2,
1097 x_original_name_cd IN VARCHAR2,
1098 x_attend_pell_id IN VARCHAR2,
1099 x_ed_use IN VARCHAR2,
1100 x_inst_cross_ref_code IN VARCHAR2,
1101 x_action_code IN VARCHAR2,
1102 x_accpt_awd_amt IN NUMBER,
1103 x_accpt_disb_dt1 IN DATE,
1104 x_accpt_disb_dt2 IN DATE,
1105 x_accpt_disb_dt3 IN DATE,
1106 x_accpt_disb_dt4 IN DATE,
1107 x_accpt_disb_dt5 IN DATE,
1108 x_accpt_disb_dt6 IN DATE,
1109 x_accpt_disb_dt7 IN DATE,
1110 x_accpt_disb_dt8 IN DATE,
1111 x_accpt_disb_dt9 IN DATE,
1112 x_accpt_disb_dt10 IN DATE,
1113 x_accpt_disb_dt11 IN DATE,
1114 x_accpt_disb_dt12 IN DATE,
1115 x_accpt_disb_dt13 IN DATE,
1116 x_accpt_disb_dt14 IN DATE,
1117 x_accpt_disb_dt15 IN DATE,
1118 x_accpt_enrl_dt IN DATE,
1119 x_accpt_low_tut_flg IN VARCHAR2,
1120 x_accpt_ver_stat_flg IN VARCHAR2,
1121 x_accpt_incr_pell_cd IN VARCHAR2,
1122 x_accpt_tran_num IN VARCHAR2,
1123 x_accpt_efc IN NUMBER,
1124 x_accpt_sec_efc IN VARCHAR2,
1125 x_accpt_acad_cal IN VARCHAR2,
1126 x_accpt_pymt_method IN VARCHAR2,
1127 x_accpt_coa IN NUMBER,
1128 x_accpt_enrl_stat IN VARCHAR2,
1129 x_accpt_wks_inst_pymt IN VARCHAR2,
1130 x_wk_inst_time_calc_pymt IN NUMBER,
1131 x_accpt_wks_acad IN VARCHAR2,
1132 x_accpt_cr_acad_yr IN VARCHAR2,
1133 x_inst_seq_num IN VARCHAR2,
1134 x_sch_full_time_pell IN NUMBER,
1135 x_stud_name IN VARCHAR2,
1136 x_ssn IN VARCHAR2,
1137 x_stud_dob IN DATE,
1138 x_cps_ver_sel_cd IN VARCHAR2,
1139 x_ytd_disb_amt IN NUMBER,
1140 x_batch_id IN VARCHAR2,
1141 x_process_date IN DATE,
1142 x_mode IN VARCHAR2,
1143 x_ci_cal_type IN VARCHAR2,
1144 x_ci_sequence_number IN NUMBER
1145 ) AS
1146 /*
1147 || Created By : avenkatr
1148 || Created On : 21-DEC-2000
1149 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1150 || Known limitations, enhancements or remarks :
1151 || Change History :
1152 || Who When What
1153 || (reverse chronological order - newest change first)
1154 */
1155 CURSOR c1 IS
1156 SELECT rowid
1157 FROM igf_gr_ytd_orig_all
1158 WHERE ytdor_id = x_ytdor_id;
1159
1160 BEGIN
1161
1162 OPEN c1;
1163 FETCH c1 INTO x_rowid;
1164 IF (c1%NOTFOUND) THEN
1165 CLOSE c1;
1166
1167 insert_row (
1168 x_rowid,
1169 x_ytdor_id,
1170 x_origination_id,
1171 x_original_ssn,
1172 x_original_name_cd,
1173 x_attend_pell_id,
1174 x_ed_use,
1175 x_inst_cross_ref_code,
1176 x_action_code,
1177 x_accpt_awd_amt,
1178 x_accpt_disb_dt1,
1179 x_accpt_disb_dt2,
1180 x_accpt_disb_dt3,
1181 x_accpt_disb_dt4,
1182 x_accpt_disb_dt5,
1183 x_accpt_disb_dt6,
1184 x_accpt_disb_dt7,
1185 x_accpt_disb_dt8,
1186 x_accpt_disb_dt9,
1187 x_accpt_disb_dt10,
1188 x_accpt_disb_dt11,
1189 x_accpt_disb_dt12,
1190 x_accpt_disb_dt13,
1191 x_accpt_disb_dt14,
1192 x_accpt_disb_dt15,
1193 x_accpt_enrl_dt,
1194 x_accpt_low_tut_flg,
1195 x_accpt_ver_stat_flg,
1196 x_accpt_incr_pell_cd,
1197 x_accpt_tran_num,
1198 x_accpt_efc,
1199 x_accpt_sec_efc,
1200 x_accpt_acad_cal,
1201 x_accpt_pymt_method,
1202 x_accpt_coa,
1203 x_accpt_enrl_stat,
1204 x_accpt_wks_inst_pymt,
1205 x_wk_inst_time_calc_pymt,
1206 x_accpt_wks_acad,
1207 x_accpt_cr_acad_yr,
1208 x_inst_seq_num,
1209 x_sch_full_time_pell,
1210 x_stud_name,
1211 x_ssn,
1212 x_stud_dob,
1213 x_cps_ver_sel_cd,
1214 x_ytd_disb_amt,
1215 x_batch_id,
1216 x_process_date,
1217 x_mode,
1218 x_ci_cal_type,
1219 x_ci_sequence_number
1220 );
1221 RETURN;
1222 END IF;
1223 CLOSE c1;
1224
1225 update_row (
1226 x_rowid,
1227 x_ytdor_id,
1228 x_origination_id,
1229 x_original_ssn,
1230 x_original_name_cd,
1231 x_attend_pell_id,
1232 x_ed_use,
1233 x_inst_cross_ref_code,
1234 x_action_code,
1235 x_accpt_awd_amt,
1236 x_accpt_disb_dt1,
1237 x_accpt_disb_dt2,
1238 x_accpt_disb_dt3,
1239 x_accpt_disb_dt4,
1240 x_accpt_disb_dt5,
1241 x_accpt_disb_dt6,
1242 x_accpt_disb_dt7,
1243 x_accpt_disb_dt8,
1244 x_accpt_disb_dt9,
1245 x_accpt_disb_dt10,
1246 x_accpt_disb_dt11,
1247 x_accpt_disb_dt12,
1248 x_accpt_disb_dt13,
1249 x_accpt_disb_dt14,
1250 x_accpt_disb_dt15,
1251 x_accpt_enrl_dt,
1252 x_accpt_low_tut_flg,
1253 x_accpt_ver_stat_flg,
1254 x_accpt_incr_pell_cd,
1255 x_accpt_tran_num,
1256 x_accpt_efc,
1257 x_accpt_sec_efc,
1258 x_accpt_acad_cal,
1259 x_accpt_pymt_method,
1260 x_accpt_coa,
1261 x_accpt_enrl_stat,
1262 x_accpt_wks_inst_pymt,
1263 x_wk_inst_time_calc_pymt,
1264 x_accpt_wks_acad,
1265 x_accpt_cr_acad_yr,
1266 x_inst_seq_num,
1267 x_sch_full_time_pell,
1268 x_stud_name,
1269 x_ssn,
1270 x_stud_dob,
1271 x_cps_ver_sel_cd,
1272 x_ytd_disb_amt,
1273 x_batch_id,
1274 x_process_date,
1275 x_mode,
1276 x_ci_cal_type,
1277 x_ci_sequence_number
1278 );
1279
1280 END add_row;
1281
1282
1283 PROCEDURE delete_row (
1284 x_rowid IN VARCHAR2
1285 ) AS
1286 /*
1287 || Created By : avenkatr
1288 || Created On : 21-DEC-2000
1289 || Purpose : Handles the DELETE DML logic for the table.
1290 || Known limitations, enhancements or remarks :
1291 || Change History :
1292 || Who When What
1293 || (reverse chronological order - newest change first)
1294 */
1295 BEGIN
1296
1297 before_dml (
1298 p_action => 'DELETE',
1299 x_rowid => x_rowid
1300 );
1301
1302 DELETE FROM igf_gr_ytd_orig_all
1303 WHERE rowid = x_rowid;
1304
1305 IF (SQL%NOTFOUND) THEN
1306 RAISE NO_DATA_FOUND;
1307 END IF;
1308
1309 END delete_row;
1310
1311
1312 END igf_gr_ytd_orig_pkg;