[Home] [Help]
PACKAGE BODY: APPS.IGF_GR_YTD_DISB_PKG
Source
1 PACKAGE BODY igf_gr_ytd_disb_pkg AS
2 /* $Header: IGFGI14B.pls 120.1 2006/04/06 06:09:01 veramach noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_gr_ytd_disb_all%ROWTYPE;
6 new_references igf_gr_ytd_disb_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_ytdds_id IN NUMBER ,
12 x_origination_id IN VARCHAR2 ,
13 x_inst_cross_ref_code IN VARCHAR2 ,
14 x_action_code IN VARCHAR2 ,
15 x_disb_ref_num IN VARCHAR2 ,
16 x_disb_accpt_amt IN NUMBER ,
17 x_db_cr_flag IN VARCHAR2 ,
18 x_disb_dt IN DATE ,
19 x_pymt_prd_start_dt IN DATE ,
20 x_disb_batch_id IN VARCHAR2 ,
21 x_creation_date IN DATE ,
22 x_created_by IN NUMBER ,
23 x_last_update_date IN DATE ,
24 x_last_updated_by IN NUMBER ,
25 x_last_update_login IN NUMBER ,
26 x_ci_cal_type IN VARCHAR2 ,
27 x_ci_sequence_number IN NUMBER ,
28 x_student_name IN VARCHAR2 ,
29 x_current_ssn_txt IN VARCHAR2 ,
30 x_student_birth_date IN DATE ,
31 x_disb_process_date IN DATE ,
32 x_routing_id_txt IN VARCHAR2 ,
33 x_fin_award_year_num IN NUMBER ,
34 x_attend_entity_id_txt IN VARCHAR2 ,
35 x_disb_seq_num IN NUMBER ,
36 x_disb_rel_ind IN VARCHAR2 ,
37 x_prev_disb_seq_num IN NUMBER
38 ) AS
39 /*
40 || Created By : avenkatr
41 || Created On : 21-DEC-2000
42 || Purpose : Initialises the Old and New references for the columns of the table.
43 || Known limitations, enhancements or remarks :
44 || Change History :
45 || Who When What
46 || (reverse chronological order - newest change first)
47 */
48
49 CURSOR cur_old_ref_values IS
50 SELECT *
51 FROM igf_gr_ytd_disb_all
52 WHERE rowid = x_rowid;
53
54 BEGIN
55
56 l_rowid := x_rowid;
57
58 -- Code for setting the Old and New Reference Values.
59 -- Populate Old Values.
60 OPEN cur_old_ref_values;
61 FETCH cur_old_ref_values INTO old_references;
62 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
63 CLOSE cur_old_ref_values;
64 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
65 igs_ge_msg_stack.add;
66 app_exception.raise_exception;
67 RETURN;
68 END IF;
69 CLOSE cur_old_ref_values;
70
71 -- Populate New Values.
72 new_references.ytdds_id := x_ytdds_id;
73 new_references.origination_id := x_origination_id;
74 new_references.inst_cross_ref_code := x_inst_cross_ref_code;
75 new_references.action_code := x_action_code;
76 new_references.disb_ref_num := x_disb_ref_num;
77 new_references.disb_accpt_amt := x_disb_accpt_amt;
78 new_references.db_cr_flag := x_db_cr_flag;
79 new_references.disb_dt := x_disb_dt;
80 new_references.pymt_prd_start_dt := x_pymt_prd_start_dt;
81 new_references.disb_batch_id := x_disb_batch_id;
82 new_references.ci_cal_type := x_ci_cal_type;
83 new_references.ci_sequence_number := x_ci_sequence_number;
84 new_references.student_name := x_student_name;
85 new_references.current_ssn_txt := x_current_ssn_txt;
86 new_references.student_birth_date := x_student_birth_date;
87 new_references.disb_process_date := x_disb_process_date;
88 new_references.routing_id_txt := x_routing_id_txt;
89 new_references.fin_award_year_num := x_fin_award_year_num;
90 new_references.attend_entity_id_txt := x_attend_entity_id_txt;
91 new_references.disb_seq_num := x_disb_seq_num;
92 new_references.disb_rel_ind := x_disb_rel_ind;
93 new_references.prev_disb_seq_num := x_prev_disb_seq_num;
94
95 IF (p_action = 'UPDATE') THEN
96 new_references.creation_date := old_references.creation_date;
97 new_references.created_by := old_references.created_by;
98 ELSE
99 new_references.creation_date := x_creation_date;
100 new_references.created_by := x_created_by;
101 END IF;
102
103 new_references.last_update_date := x_last_update_date;
104 new_references.last_updated_by := x_last_updated_by;
105 new_references.last_update_login := x_last_update_login;
106
107 END set_column_values;
108
109
110 FUNCTION get_pk_for_validation (
111 x_ytdds_id IN NUMBER
112 ) RETURN BOOLEAN AS
113 /*
114 || Created By : avenkatr
115 || Created On : 21-DEC-2000
116 || Purpose : Validates the Primary Key of the table.
117 || Known limitations, enhancements or remarks :
118 || Change History :
119 || Who When What
120 || (reverse chronological order - newest change first)
121 */
122 CURSOR cur_rowid IS
123 SELECT rowid
124 FROM igf_gr_ytd_disb_all
125 WHERE ytdds_id = x_ytdds_id
126 FOR UPDATE NOWAIT;
127
128 lv_rowid cur_rowid%RowType;
129
130 BEGIN
131
132 OPEN cur_rowid;
133 FETCH cur_rowid INTO lv_rowid;
134 IF (cur_rowid%FOUND) THEN
135 CLOSE cur_rowid;
136 RETURN(TRUE);
137 ELSE
138 CLOSE cur_rowid;
139 RETURN(FALSE);
140 END IF;
141
142 END get_pk_for_validation;
143
144
145 PROCEDURE before_dml (
146 p_action IN VARCHAR2,
147 x_rowid IN VARCHAR2 ,
148 x_ytdds_id IN NUMBER ,
149 x_origination_id IN VARCHAR2 ,
150 x_inst_cross_ref_code IN VARCHAR2 ,
151 x_action_code IN VARCHAR2 ,
152 x_disb_ref_num IN VARCHAR2 ,
153 x_disb_accpt_amt IN NUMBER ,
154 x_db_cr_flag IN VARCHAR2 ,
155 x_disb_dt IN DATE ,
156 x_pymt_prd_start_dt IN DATE ,
157 x_disb_batch_id IN VARCHAR2 ,
158 x_creation_date IN DATE ,
159 x_created_by IN NUMBER ,
160 x_last_update_date IN DATE ,
161 x_last_updated_by IN NUMBER ,
162 x_last_update_login IN NUMBER ,
163 x_ci_cal_type IN VARCHAR2 ,
164 x_ci_sequence_number IN NUMBER ,
165 x_student_name IN VARCHAR2 ,
166 x_current_ssn_txt IN VARCHAR2 ,
167 x_student_birth_date IN DATE ,
168 x_disb_process_date IN DATE ,
169 x_routing_id_txt IN VARCHAR2 ,
170 x_fin_award_year_num IN NUMBER ,
171 x_attend_entity_id_txt IN VARCHAR2 ,
172 x_disb_seq_num IN NUMBER ,
173 x_disb_rel_ind IN VARCHAR2 ,
174 x_prev_disb_seq_num IN NUMBER
175 ) AS
176 /*
177 || Created By : avenkatr
178
179 || Created On : 21-DEC-2000
180 || Purpose : Initialises the columns, Checks Constraints, Calls the
181 || Trigger Handlers for the table, before any DML operation.
182 || Known limitations, enhancements or remarks :
183 || Change History :
184 || Who When What
185 || (reverse chronological order - newest change first)
186 */
187 BEGIN
188
189 set_column_values (
190 p_action,
191 x_rowid,
192 x_ytdds_id,
193 x_origination_id,
194 x_inst_cross_ref_code,
195 x_action_code,
196 x_disb_ref_num,
197 x_disb_accpt_amt,
198 x_db_cr_flag,
199 x_disb_dt,
200 x_pymt_prd_start_dt,
201 x_disb_batch_id,
202 x_creation_date,
203 x_created_by,
204 x_last_update_date,
205 x_last_updated_by,
206 x_last_update_login,
207 x_ci_cal_type,
208 x_ci_sequence_number,
209 x_student_name,
210 x_current_ssn_txt,
211 x_student_birth_date,
212 x_disb_process_date,
213 x_routing_id_txt,
214 x_fin_award_year_num,
215 x_attend_entity_id_txt,
216 x_disb_seq_num,
217 x_disb_rel_ind,
218 x_prev_disb_seq_num
219 );
220
221 IF (p_action = 'INSERT') THEN
222 -- Call all the procedures related to Before Insert.
223 IF ( get_pk_for_validation(
224 new_references.ytdds_id
225 )
226 ) THEN
227 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
228 igs_ge_msg_stack.add;
229 app_exception.raise_exception;
230 END IF;
231 ELSIF (p_action = 'VALIDATE_INSERT') THEN
232 -- Call all the procedures related to Before Insert.
233 IF ( get_pk_for_validation (
234 new_references.ytdds_id
235 )
236 ) THEN
237 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
238 igs_ge_msg_stack.add;
239 app_exception.raise_exception;
240 END IF;
241 END IF;
242
243 END before_dml;
244
245
246 PROCEDURE insert_row (
247 x_rowid IN OUT NOCOPY VARCHAR2,
248 x_ytdds_id IN OUT NOCOPY NUMBER,
249 x_origination_id IN VARCHAR2,
250 x_inst_cross_ref_code IN VARCHAR2,
251 x_action_code IN VARCHAR2,
252 x_disb_ref_num IN VARCHAR2,
253 x_disb_accpt_amt IN NUMBER,
254 x_db_cr_flag IN VARCHAR2,
255 x_disb_dt IN DATE,
256 x_pymt_prd_start_dt IN DATE,
257 x_disb_batch_id IN VARCHAR2,
258 x_mode IN VARCHAR2,
259 x_ci_cal_type IN VARCHAR2,
260 x_ci_sequence_number IN NUMBER,
261 x_student_name IN VARCHAR2,
262 x_current_ssn_txt IN VARCHAR2,
263 x_student_birth_date IN DATE,
264 x_disb_process_date IN DATE,
265 x_routing_id_txt IN VARCHAR2,
266 x_fin_award_year_num IN NUMBER,
267 x_attend_entity_id_txt IN VARCHAR2,
268 x_disb_seq_num IN NUMBER,
269 x_disb_rel_ind IN VARCHAR2,
270 x_prev_disb_seq_num IN NUMBER
271 ) AS
272 /*
273 || Created By : avenkatr
274 || Created On : 21-DEC-2000
275 || Purpose : Handles the INSERT DML logic for the table.
276 || Known limitations, enhancements or remarks :
277 || Change History :
278 || Who When What
279 || (reverse chronological order - newest change first)
280 */
281 CURSOR c IS
282 SELECT rowid
283 FROM igf_gr_ytd_disb_all
284 WHERE ytdds_id = x_ytdds_id;
285
286 x_last_update_date DATE;
287 x_last_updated_by NUMBER;
288 x_last_update_login NUMBER;
289 x_request_id NUMBER;
290 x_program_id NUMBER;
291 x_program_application_id NUMBER;
292 x_program_update_date DATE;
293 l_org_id igf_gr_ytd_disb_all.org_id%TYPE DEFAULT igf_aw_gen.get_org_id;
294
295 BEGIN
296
297 x_last_update_date := SYSDATE;
298 IF (x_mode = 'I') THEN
299 x_last_updated_by := 1;
300 x_last_update_login := 0;
301 ELSIF (x_mode = 'R') THEN
302 x_last_updated_by := fnd_global.user_id;
303 IF (x_last_updated_by IS NULL) THEN
304 x_last_updated_by := -1;
305 END IF;
306 x_last_update_login := fnd_global.login_id;
307 IF (x_last_update_login IS NULL) THEN
308 x_last_update_login := -1;
309 END IF;
310 x_request_id := fnd_global.conc_request_id;
311 x_program_id := fnd_global.conc_program_id;
312 x_program_application_id := fnd_global.prog_appl_id;
313
314 IF (x_request_id = -1) THEN
315 x_request_id := NULL;
316 x_program_id := NULL;
317 x_program_application_id := NULL;
318 x_program_update_date := NULL;
319 ELSE
320 x_program_update_date := SYSDATE;
321 END IF;
322 ELSE
323 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
324 igs_ge_msg_stack.add;
325 app_exception.raise_exception;
326 END IF;
327
328 SELECT igf_gr_ytd_disb_s.nextval INTO x_ytdds_id FROM dual;
329
330 before_dml(
331 p_action => 'INSERT',
332 x_rowid => x_rowid,
333 x_ytdds_id => x_ytdds_id,
334 x_origination_id => x_origination_id,
335 x_inst_cross_ref_code => x_inst_cross_ref_code,
336 x_action_code => x_action_code,
337 x_disb_ref_num => x_disb_ref_num,
338 x_disb_accpt_amt => x_disb_accpt_amt,
339 x_db_cr_flag => x_db_cr_flag,
340 x_disb_dt => x_disb_dt,
341 x_pymt_prd_start_dt => x_pymt_prd_start_dt,
342 x_disb_batch_id => x_disb_batch_id,
343 x_creation_date => x_last_update_date,
344 x_created_by => x_last_updated_by,
345 x_last_update_date => x_last_update_date,
346 x_last_updated_by => x_last_updated_by,
347 x_last_update_login => x_last_update_login,
348 x_ci_cal_type => x_ci_cal_type,
349 x_ci_sequence_number => x_ci_sequence_number,
350 x_student_name => x_student_name,
351 x_current_ssn_txt => x_current_ssn_txt,
352 x_student_birth_date => x_student_birth_date,
353 x_disb_process_date => x_disb_process_date,
354 x_routing_id_txt => x_routing_id_txt,
355 x_fin_award_year_num => x_fin_award_year_num,
356 x_attend_entity_id_txt => x_attend_entity_id_txt,
357 x_disb_seq_num => x_disb_seq_num,
358 x_disb_rel_ind => x_disb_rel_ind,
359 x_prev_disb_seq_num => x_prev_disb_seq_num
360 );
361
362 INSERT INTO igf_gr_ytd_disb_all (
363 ytdds_id,
364 origination_id,
365 inst_cross_ref_code,
366 action_code,
367 disb_ref_num,
368 disb_accpt_amt,
369 db_cr_flag,
370 disb_dt,
371 pymt_prd_start_dt,
372 disb_batch_id,
373 creation_date,
374 created_by,
375 last_update_date,
376 last_updated_by,
377 last_update_login,
378 request_id,
379 program_id,
380 program_application_id,
381 program_update_date,
382 org_id,
383 ci_cal_type,
384 ci_sequence_number,
385 student_name,
386 current_ssn_txt,
387 student_birth_date,
388 disb_process_date,
389 routing_id_txt,
390 fin_award_year_num,
391 attend_entity_id_txt,
392 disb_seq_num,
393 disb_rel_ind,
394 prev_disb_seq_num
395 ) VALUES (
396 new_references.ytdds_id,
397 new_references.origination_id,
398 new_references.inst_cross_ref_code,
399 new_references.action_code,
400 new_references.disb_ref_num,
401 new_references.disb_accpt_amt,
402 new_references.db_cr_flag,
403 new_references.disb_dt,
404 new_references.pymt_prd_start_dt,
405 new_references.disb_batch_id,
406 x_last_update_date,
407 x_last_updated_by,
408 x_last_update_date,
409 x_last_updated_by,
410 x_last_update_login ,
411 x_request_id,
412 x_program_id,
413 x_program_application_id,
414 x_program_update_date,
415 l_org_id,
416 new_references.ci_cal_type,
417 new_references.ci_sequence_number,
418 new_references.student_name,
419 new_references.current_ssn_txt,
420 new_references.student_birth_date,
421 new_references.disb_process_date,
422 new_references.routing_id_txt,
423 new_references.fin_award_year_num,
424 new_references.attend_entity_id_txt,
425 new_references.disb_seq_num,
426 new_references.disb_rel_ind,
427 new_references.prev_disb_seq_num
428 );
429
430 OPEN c;
431 FETCH c INTO x_rowid;
432 IF (c%NOTFOUND) THEN
433 CLOSE c;
434 RAISE NO_DATA_FOUND;
435 END IF;
436 CLOSE c;
437
438 END insert_row;
439
440
441 PROCEDURE lock_row (
442 x_rowid IN VARCHAR2,
443 x_ytdds_id IN NUMBER,
444 x_origination_id IN VARCHAR2,
445 x_inst_cross_ref_code IN VARCHAR2,
446 x_action_code IN VARCHAR2,
447 x_disb_ref_num IN VARCHAR2,
448 x_disb_accpt_amt IN NUMBER,
449 x_db_cr_flag IN VARCHAR2,
450 x_disb_dt IN DATE,
451 x_pymt_prd_start_dt IN DATE,
452 x_disb_batch_id IN VARCHAR2,
453 x_ci_cal_type IN VARCHAR2,
454 x_ci_sequence_number IN NUMBER,
455 x_student_name IN VARCHAR2,
456 x_current_ssn_txt IN VARCHAR2,
457 x_student_birth_date IN DATE,
458 x_disb_process_date IN DATE,
459 x_routing_id_txt IN VARCHAR2,
460 x_fin_award_year_num IN NUMBER,
461 x_attend_entity_id_txt IN VARCHAR2,
462 x_disb_seq_num IN NUMBER,
463 x_disb_rel_ind IN VARCHAR2,
464 x_prev_disb_seq_num IN NUMBER
465 ) AS
466 /*
467 || Created By : avenkatr
468 || Created On : 21-DEC-2000
469 || Purpose : Handles the LOCK mechanism for the table.
470 || Known limitations, enhancements or remarks :
471 || Change History :
472 || Who When What
473 || (reverse chronological order - newest change first)
474 */
475 CURSOR c1 IS
476 SELECT
477 origination_id,
478 inst_cross_ref_code,
479 action_code,
480 disb_ref_num,
481 disb_accpt_amt,
482 db_cr_flag,
483 disb_dt,
484 pymt_prd_start_dt,
485 disb_batch_id,
486 ci_cal_type,
487 ci_sequence_number,
488 student_name,
489 current_ssn_txt,
490 student_birth_date,
491 disb_process_date,
492 routing_id_txt,
493 fin_award_year_num,
494 attend_entity_id_txt,
495 disb_seq_num,
496 disb_rel_ind,
497 prev_disb_seq_num
498 FROM igf_gr_ytd_disb_all
499 WHERE rowid = x_rowid
500 FOR UPDATE NOWAIT;
501
502 tlinfo c1%ROWTYPE;
503
504 BEGIN
505
506 OPEN c1;
507 FETCH c1 INTO tlinfo;
508 IF (c1%notfound) THEN
509 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
510 igs_ge_msg_stack.add;
511 CLOSE c1;
512 app_exception.raise_exception;
513 RETURN;
514 END IF;
515 CLOSE c1;
516
517 IF (
518 ((tlinfo.origination_id = x_origination_id) OR ((tlinfo.origination_id IS NULL) AND (X_origination_id IS NULL)))
519 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)))
520 AND ((tlinfo.action_code = x_action_code) OR ((tlinfo.action_code IS NULL) AND (X_action_code IS NULL)))
521 AND (tlinfo.disb_ref_num = x_disb_ref_num)
522 AND (tlinfo.disb_accpt_amt = x_disb_accpt_amt)
523 AND ((tlinfo.db_cr_flag = x_db_cr_flag) OR ((tlinfo.db_cr_flag IS NULL) AND (X_db_cr_flag IS NULL)))
524 AND ((tlinfo.disb_dt = x_disb_dt) OR ((tlinfo.disb_dt IS NULL) AND (X_disb_dt IS NULL)))
525 AND (tlinfo.pymt_prd_start_dt = x_pymt_prd_start_dt)
526 AND ((tlinfo.disb_batch_id = x_disb_batch_id) OR ((tlinfo.disb_batch_id IS NULL) AND (X_disb_batch_id IS NULL)))
527 AND ((tlinfo.ci_cal_type = x_ci_cal_type) OR ((tlinfo.ci_cal_type IS NULL) AND (X_ci_cal_type IS NULL)))
528 AND ((tlinfo.ci_sequence_number = x_ci_sequence_number) OR ((tlinfo.ci_sequence_number IS NULL) AND (X_ci_sequence_number IS NULL)))
529 AND ((tlinfo.student_name = x_student_name) OR ((tlinfo.student_name IS NULL) AND (X_student_name IS NULL)))
530 AND ((tlinfo.current_ssn_txt = x_current_ssn_txt) OR ((tlinfo.current_ssn_txt IS NULL) AND (X_current_ssn_txt IS NULL)))
531 AND ((tlinfo.student_birth_date = x_student_birth_date) OR ((tlinfo.student_birth_date IS NULL) AND (X_student_birth_date IS NULL)))
532 AND ((tlinfo.disb_process_date = x_disb_process_date) OR ((tlinfo.disb_process_date IS NULL) AND (X_disb_process_date IS NULL)))
533 AND ((tlinfo.routing_id_txt = x_routing_id_txt) OR ((tlinfo.routing_id_txt IS NULL) AND (X_routing_id_txt IS NULL)))
534 AND ((tlinfo.fin_award_year_num = x_fin_award_year_num) OR ((tlinfo.fin_award_year_num IS NULL) AND (X_fin_award_year_num IS NULL)))
535 AND ((tlinfo.attend_entity_id_txt = x_attend_entity_id_txt) OR ((tlinfo.attend_entity_id_txt IS NULL) AND (X_attend_entity_id_txt IS NULL)))
536 AND ((tlinfo.disb_seq_num = x_disb_seq_num) OR ((tlinfo.disb_seq_num IS NULL) AND (X_disb_seq_num IS NULL)))
537 AND ((tlinfo.disb_rel_ind = x_disb_rel_ind) OR ((tlinfo.disb_rel_ind IS NULL) AND (X_disb_rel_ind IS NULL)))
538 AND ((tlinfo.prev_disb_seq_num = x_prev_disb_seq_num) OR ((tlinfo.prev_disb_seq_num IS NULL) AND (X_prev_disb_seq_num IS NULL)))
539 ) THEN
540 NULL;
541 ELSE
542 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
543 igs_ge_msg_stack.add;
544 app_exception.raise_exception;
545 END IF;
546
547 RETURN;
548
549 END lock_row;
550
551
552 PROCEDURE update_row (
553 x_rowid IN VARCHAR2,
554 x_ytdds_id IN NUMBER,
555 x_origination_id IN VARCHAR2,
556 x_inst_cross_ref_code IN VARCHAR2,
557 x_action_code IN VARCHAR2,
558 x_disb_ref_num IN VARCHAR2,
559 x_disb_accpt_amt IN NUMBER,
560 x_db_cr_flag IN VARCHAR2,
561 x_disb_dt IN DATE,
562 x_pymt_prd_start_dt IN DATE,
563 x_disb_batch_id IN VARCHAR2,
564 x_mode IN VARCHAR2,
565 x_ci_cal_type IN VARCHAR2,
566 x_ci_sequence_number IN NUMBER,
567 x_student_name IN VARCHAR2,
568 x_current_ssn_txt IN VARCHAR2,
569 x_student_birth_date IN DATE,
570 x_disb_process_date IN DATE,
571 x_routing_id_txt IN VARCHAR2,
572 x_fin_award_year_num IN NUMBER,
573 x_attend_entity_id_txt IN VARCHAR2,
574 x_disb_seq_num IN NUMBER,
575 x_disb_rel_ind IN VARCHAR2,
576 x_prev_disb_seq_num IN NUMBER
577 ) AS
578 /*
579 || Created By : avenkatr
580 || Created On : 21-DEC-2000
581 || Purpose : Handles the UPDATE DML logic for the table.
582 || Known limitations, enhancements or remarks :
583 || Change History :
584 || Who When What
585 || (reverse chronological order - newest change first)
586 */
587 x_last_update_date DATE ;
588 x_last_updated_by NUMBER;
589 x_last_update_login NUMBER;
590 x_request_id NUMBER;
591 x_program_id NUMBER;
592 x_program_application_id NUMBER;
593 x_program_update_date DATE;
594
595 BEGIN
596
597 x_last_update_date := SYSDATE;
598 IF (X_MODE = 'I') THEN
599 x_last_updated_by := 1;
600 x_last_update_login := 0;
601 ELSIF (x_mode = 'R') THEN
602 x_last_updated_by := fnd_global.user_id;
603 IF x_last_updated_by IS NULL THEN
604 x_last_updated_by := -1;
605 END IF;
606 x_last_update_login := fnd_global.login_id;
607 IF (x_last_update_login IS NULL) THEN
608 x_last_update_login := -1;
609 END IF;
610 ELSE
611 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
612 igs_ge_msg_stack.add;
613 app_exception.raise_exception;
614 END IF;
615
616 before_dml(
617 p_action => 'UPDATE',
618 x_rowid => x_rowid,
619 x_ytdds_id => x_ytdds_id,
620 x_origination_id => x_origination_id,
621 x_inst_cross_ref_code => x_inst_cross_ref_code,
622 x_action_code => x_action_code,
623 x_disb_ref_num => x_disb_ref_num,
624 x_disb_accpt_amt => x_disb_accpt_amt,
625 x_db_cr_flag => x_db_cr_flag,
626 x_disb_dt => x_disb_dt,
627 x_pymt_prd_start_dt => x_pymt_prd_start_dt,
628 x_disb_batch_id => x_disb_batch_id,
629 x_creation_date => x_last_update_date,
630 x_created_by => x_last_updated_by,
631 x_last_update_date => x_last_update_date,
632 x_last_updated_by => x_last_updated_by,
633 x_last_update_login => x_last_update_login,
634 x_ci_cal_type => x_ci_cal_type,
635 x_ci_sequence_number => x_ci_sequence_number,
636 x_student_name => x_student_name,
637 x_current_ssn_txt => x_current_ssn_txt,
638 x_student_birth_date => x_student_birth_date,
639 x_disb_process_date => x_disb_process_date,
640 x_routing_id_txt => x_routing_id_txt,
641 x_fin_award_year_num => x_fin_award_year_num,
642 x_attend_entity_id_txt => x_attend_entity_id_txt,
643 x_disb_seq_num => x_disb_seq_num,
644 x_disb_rel_ind => x_disb_rel_ind,
645 x_prev_disb_seq_num => x_prev_disb_seq_num
646 );
647
648 IF (x_mode = 'R') THEN
649 x_request_id := fnd_global.conc_request_id;
650 x_program_id := fnd_global.conc_program_id;
651 x_program_application_id := fnd_global.prog_appl_id;
652 IF (x_request_id = -1) THEN
653 x_request_id := old_references.request_id;
654 x_program_id := old_references.program_id;
655 x_program_application_id := old_references.program_application_id;
656 x_program_update_date := old_references.program_update_date;
657 ELSE
658 x_program_update_date := SYSDATE;
659 END IF;
660 END IF;
661
662 UPDATE igf_gr_ytd_disb_all
663 SET
664 origination_id = new_references.origination_id,
665 inst_cross_ref_code = new_references.inst_cross_ref_code,
666 action_code = new_references.action_code,
667 disb_ref_num = new_references.disb_ref_num,
668 disb_accpt_amt = new_references.disb_accpt_amt,
669 db_cr_flag = new_references.db_cr_flag,
670 disb_dt = new_references.disb_dt,
671 pymt_prd_start_dt = new_references.pymt_prd_start_dt,
672 disb_batch_id = new_references.disb_batch_id,
673 last_update_date = x_last_update_date,
674 last_updated_by = x_last_updated_by,
675 last_update_login = x_last_update_login ,
676 request_id = x_request_id,
677 program_id = x_program_id,
678 program_application_id = x_program_application_id,
679 program_update_date = x_program_update_date,
680 ci_cal_type = new_references.ci_cal_type,
681 ci_sequence_number = new_references.ci_sequence_number,
682 student_name = new_references.student_name,
683 current_ssn_txt = new_references.current_ssn_txt,
684 student_birth_date = new_references.student_birth_date,
685 disb_process_date = new_references.disb_process_date,
686 routing_id_txt = new_references.routing_id_txt,
687 fin_award_year_num = new_references.fin_award_year_num,
688 attend_entity_id_txt = new_references.attend_entity_id_txt,
689 disb_seq_num = new_references.disb_seq_num,
690 disb_rel_ind = new_references.disb_rel_ind,
691 prev_disb_seq_num = new_references.prev_disb_seq_num
692 WHERE rowid = x_rowid;
693
694 IF (SQL%NOTFOUND) THEN
695 RAISE NO_DATA_FOUND;
696 END IF;
697
698 END update_row;
699
700
701 PROCEDURE add_row (
702 x_rowid IN OUT NOCOPY VARCHAR2,
703 x_ytdds_id IN OUT NOCOPY NUMBER,
704 x_origination_id IN VARCHAR2,
705 x_inst_cross_ref_code IN VARCHAR2,
706 x_action_code IN VARCHAR2,
707 x_disb_ref_num IN VARCHAR2,
708 x_disb_accpt_amt IN NUMBER,
709 x_db_cr_flag IN VARCHAR2,
710 x_disb_dt IN DATE,
711 x_pymt_prd_start_dt IN DATE,
712 x_disb_batch_id IN VARCHAR2,
713 x_mode IN VARCHAR2,
714 x_ci_cal_type IN VARCHAR2,
715 x_ci_sequence_number IN NUMBER,
716 x_student_name IN VARCHAR2,
717 x_current_ssn_txt IN VARCHAR2,
718 x_student_birth_date IN DATE,
719 x_disb_process_date IN DATE,
720 x_routing_id_txt IN VARCHAR2,
721 x_fin_award_year_num IN NUMBER,
722 x_attend_entity_id_txt IN VARCHAR2,
723 x_disb_seq_num IN NUMBER,
724 x_disb_rel_ind IN VARCHAR2,
725 x_prev_disb_seq_num IN NUMBER
726 ) AS
727 /*
728 || Created By : avenkatr
729 || Created On : 21-DEC-2000
730 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
731 || Known limitations, enhancements or remarks :
732 || Change History :
733 || Who When What
734 || (reverse chronological order - newest change first)
735 */
736 CURSOR c1 IS
737 SELECT rowid
738 FROM igf_gr_ytd_disb_all
739 WHERE ytdds_id = x_ytdds_id;
740
741 BEGIN
742
743 OPEN c1;
744 FETCH c1 INTO x_rowid;
745 IF (c1%NOTFOUND) THEN
746 CLOSE c1;
747
748 insert_row (
749 x_rowid,
750 x_ytdds_id,
751 x_origination_id,
752 x_inst_cross_ref_code,
753 x_action_code,
754 x_disb_ref_num,
755 x_disb_accpt_amt,
756 x_db_cr_flag,
757 x_disb_dt,
758 x_pymt_prd_start_dt,
759 x_disb_batch_id,
760 x_mode,
761 x_ci_cal_type,
762 x_ci_sequence_number,
763 x_student_name,
764 x_current_ssn_txt,
765 x_student_birth_date,
766 x_disb_process_date,
767 x_routing_id_txt,
768 x_fin_award_year_num,
769 x_attend_entity_id_txt,
770 x_disb_seq_num,
771 x_disb_rel_ind,
772 x_prev_disb_seq_num
773 );
774 RETURN;
775 END IF;
776 CLOSE c1;
777
778 update_row (
779 x_rowid,
780 x_ytdds_id,
781 x_origination_id,
782 x_inst_cross_ref_code,
783 x_action_code,
784 x_disb_ref_num,
785 x_disb_accpt_amt,
786 x_db_cr_flag,
787 x_disb_dt,
788 x_pymt_prd_start_dt,
789 x_disb_batch_id,
790 x_mode,
791 x_ci_cal_type,
792 x_ci_sequence_number,
793 x_student_name,
794 x_current_ssn_txt,
795 x_student_birth_date,
796 x_disb_process_date,
797 x_routing_id_txt,
798 x_fin_award_year_num,
799 x_attend_entity_id_txt,
800 x_disb_seq_num,
801 x_disb_rel_ind,
802 x_prev_disb_seq_num
803 );
804
805 END add_row;
806
807
808 PROCEDURE delete_row (
809 x_rowid IN VARCHAR2
810 ) AS
811 /*
812 || Created By : avenkatr
813 || Created On : 21-DEC-2000
814 || Purpose : Handles the DELETE DML logic for the table.
815 || Known limitations, enhancements or remarks :
816 || Change History :
817 || Who When What
818 || (reverse chronological order - newest change first)
819 */
820 BEGIN
821
822 before_dml (
823 p_action => 'DELETE',
824 x_rowid => x_rowid
825 );
826
827 DELETE FROM igf_gr_ytd_disb_all
828 WHERE rowid = x_rowid;
829
830 IF (SQL%NOTFOUND) THEN
831 RAISE NO_DATA_FOUND;
832 END IF;
833
834 END delete_row;
835
836
837 END igf_gr_ytd_disb_pkg;