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