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