[Home] [Help]
PACKAGE BODY: APPS.IGF_GR_ELEC_STAT_SUM_PKG
Source
1 PACKAGE BODY igf_gr_elec_stat_sum_pkg AS
2 /* $Header: IGFGI11B.pls 115.6 2002/11/28 14:17:49 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_gr_elec_stat_sum_all%ROWTYPE;
6 new_references igf_gr_elec_stat_sum_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_ess_id IN NUMBER ,
12 x_rep_pell_id IN VARCHAR2,
13 x_duns_id IN VARCHAR2,
14 x_gaps_award_num IN VARCHAR2,
15 x_acct_schedule_number IN VARCHAR2,
16 x_acct_schedule_date IN DATE ,
17 x_prev_obligation_amt IN NUMBER ,
18 x_obligation_adj_amt IN NUMBER ,
19 x_curr_obligation_amt IN NUMBER ,
20 x_prev_obligation_pymt_amt IN NUMBER ,
21 x_obligation_pymt_adj_amt IN NUMBER ,
22 x_curr_obligation_pymt_amt IN NUMBER ,
23 x_ytd_total_recp IN NUMBER ,
24 x_ytd_accepted_disb_amt IN NUMBER ,
25 x_ytd_posted_disb_amt IN NUMBER ,
26 x_ytd_admin_cost_allowance IN NUMBER ,
27 x_caps_drwn_dn_pymts IN NUMBER ,
28 x_gaps_last_date IN DATE ,
29 x_last_pymt_number IN VARCHAR2,
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 : adhawan
38 || Created On : 09-JAN-2001
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_gr_elec_stat_sum_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.ess_id := x_ess_id;
70 new_references.rep_pell_id := x_rep_pell_id;
71 new_references.duns_id := x_duns_id;
72 new_references.gaps_award_num := x_gaps_award_num;
73 new_references.acct_schedule_number := x_acct_schedule_number;
74 new_references.acct_schedule_date := x_acct_schedule_date;
75 new_references.prev_obligation_amt := x_prev_obligation_amt;
76 new_references.obligation_adj_amt := x_obligation_adj_amt;
77 new_references.curr_obligation_amt := x_curr_obligation_amt;
78 new_references.prev_obligation_pymt_amt := x_prev_obligation_pymt_amt;
79 new_references.obligation_pymt_adj_amt := x_obligation_pymt_adj_amt;
80 new_references.curr_obligation_pymt_amt := x_curr_obligation_pymt_amt;
81 new_references.ytd_total_recp := x_ytd_total_recp;
82 new_references.ytd_accepted_disb_amt := x_ytd_accepted_disb_amt;
83 new_references.ytd_posted_disb_amt := x_ytd_posted_disb_amt;
84 new_references.ytd_admin_cost_allowance := x_ytd_admin_cost_allowance;
85 new_references.caps_drwn_dn_pymts := x_caps_drwn_dn_pymts;
86 new_references.gaps_last_date := x_gaps_last_date;
87 new_references.last_pymt_number := x_last_pymt_number;
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_ess_id IN NUMBER
106 ) RETURN BOOLEAN AS
107 /*
108 || Created By : adhawan
109 || Created On : 09-JAN-2001
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_gr_elec_stat_sum_all
119 WHERE ess_id = x_ess_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_ess_id IN NUMBER ,
143 x_rep_pell_id IN VARCHAR2,
144 x_duns_id IN VARCHAR2,
145 x_gaps_award_num IN VARCHAR2,
146 x_acct_schedule_number IN VARCHAR2,
147 x_acct_schedule_date IN DATE ,
148 x_prev_obligation_amt IN NUMBER ,
149 x_obligation_adj_amt IN NUMBER ,
150 x_curr_obligation_amt IN NUMBER ,
151 x_prev_obligation_pymt_amt IN NUMBER ,
152 x_obligation_pymt_adj_amt IN NUMBER ,
153 x_curr_obligation_pymt_amt IN NUMBER ,
154 x_ytd_total_recp IN NUMBER ,
155 x_ytd_accepted_disb_amt IN NUMBER ,
156 x_ytd_posted_disb_amt IN NUMBER ,
157 x_ytd_admin_cost_allowance IN NUMBER ,
158 x_caps_drwn_dn_pymts IN NUMBER ,
159 x_gaps_last_date IN DATE ,
160 x_last_pymt_number IN VARCHAR2,
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 : adhawan
169 || Created On : 09-JAN-2001
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_ess_id,
183 x_rep_pell_id,
184 x_duns_id,
185 x_gaps_award_num,
186 x_acct_schedule_number,
187 x_acct_schedule_date,
188 x_prev_obligation_amt,
189 x_obligation_adj_amt,
190 x_curr_obligation_amt,
191 x_prev_obligation_pymt_amt,
192 x_obligation_pymt_adj_amt,
193 x_curr_obligation_pymt_amt,
194 x_ytd_total_recp,
195 x_ytd_accepted_disb_amt,
196 x_ytd_posted_disb_amt,
197 x_ytd_admin_cost_allowance,
198 x_caps_drwn_dn_pymts,
199 x_gaps_last_date,
200 x_last_pymt_number,
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.ess_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.ess_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_ess_id IN OUT NOCOPY NUMBER,
236 x_rep_pell_id IN VARCHAR2,
237 x_duns_id IN VARCHAR2,
238 x_gaps_award_num IN VARCHAR2,
239 x_acct_schedule_number IN VARCHAR2,
240 x_acct_schedule_date IN DATE,
241 x_prev_obligation_amt IN NUMBER,
242 x_obligation_adj_amt IN NUMBER,
243 x_curr_obligation_amt IN NUMBER,
244 x_prev_obligation_pymt_amt IN NUMBER,
245 x_obligation_pymt_adj_amt IN NUMBER,
246 x_curr_obligation_pymt_amt IN NUMBER,
247 x_ytd_total_recp IN NUMBER,
248 x_ytd_accepted_disb_amt IN NUMBER,
249 x_ytd_posted_disb_amt IN NUMBER,
250 x_ytd_admin_cost_allowance IN NUMBER,
251 x_caps_drwn_dn_pymts IN NUMBER,
252 x_gaps_last_date IN DATE,
253 x_last_pymt_number IN VARCHAR2,
254 x_mode IN VARCHAR2
255 ) AS
256 /*
257 || Created By : adhawan
258 || Created On : 09-JAN-2001
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_gr_elec_stat_sum_all
268 WHERE ess_id = x_ess_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 l_org_id igf_gr_elec_stat_sum_all.org_id%TYPE;
278
279 BEGIN
280
281 l_org_id := igf_aw_gen.get_org_id;
282
283 x_last_update_date := SYSDATE;
284 IF (x_mode = 'I') THEN
285 x_last_updated_by := 1;
286 x_last_update_login := 0;
287 ELSIF (x_mode = 'R') THEN
288 x_last_updated_by := fnd_global.user_id;
289 IF (x_last_updated_by IS NULL) THEN
290 x_last_updated_by := -1;
291 END IF;
292 x_last_update_login := fnd_global.login_id;
293 IF (x_last_update_login IS NULL) THEN
294 x_last_update_login := -1;
295 END IF;
296 x_request_id := fnd_global.conc_request_id;
297 x_program_id := fnd_global.conc_program_id;
298 x_program_application_id := fnd_global.prog_appl_id;
299
300 IF (x_request_id = -1) THEN
301 x_request_id := NULL;
302 x_program_id := NULL;
303 x_program_application_id := NULL;
304 x_program_update_date := NULL;
305 ELSE
306 x_program_update_date := SYSDATE;
307 END IF;
308 ELSE
309 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
310 igs_ge_msg_stack.add;
311 app_exception.raise_exception;
312 END IF;
313 SELECT igf_gr_elec_stat_sum_s.NEXTVAL INTO x_ess_id FROM DUAL;
314 before_dml(
315 p_action => 'INSERT',
316 x_rowid => x_rowid,
317 x_ess_id => x_ess_id,
318 x_rep_pell_id => x_rep_pell_id,
319 x_duns_id => x_duns_id,
320 x_gaps_award_num => x_gaps_award_num,
321 x_acct_schedule_number => x_acct_schedule_number,
322 x_acct_schedule_date => x_acct_schedule_date,
323 x_prev_obligation_amt => x_prev_obligation_amt,
324 x_obligation_adj_amt => x_obligation_adj_amt,
325 x_curr_obligation_amt => x_curr_obligation_amt,
326 x_prev_obligation_pymt_amt => x_prev_obligation_pymt_amt,
327 x_obligation_pymt_adj_amt => x_obligation_pymt_adj_amt,
328 x_curr_obligation_pymt_amt => x_curr_obligation_pymt_amt,
329 x_ytd_total_recp => x_ytd_total_recp,
330 x_ytd_accepted_disb_amt => x_ytd_accepted_disb_amt,
331 x_ytd_posted_disb_amt => x_ytd_posted_disb_amt,
332 x_ytd_admin_cost_allowance => x_ytd_admin_cost_allowance,
333 x_caps_drwn_dn_pymts => x_caps_drwn_dn_pymts,
334 x_gaps_last_date => x_gaps_last_date,
335 x_last_pymt_number => x_last_pymt_number,
336 x_creation_date => x_last_update_date,
337 x_created_by => x_last_updated_by,
338 x_last_update_date => x_last_update_date,
339 x_last_updated_by => x_last_updated_by,
340 x_last_update_login => x_last_update_login
341 );
342
343 INSERT INTO igf_gr_elec_stat_sum_all (
344 ess_id,
345 rep_pell_id,
346 duns_id,
347 gaps_award_num,
348 acct_schedule_number,
349 acct_schedule_date,
350 prev_obligation_amt,
351 obligation_adj_amt,
352 curr_obligation_amt,
353 prev_obligation_pymt_amt,
354 obligation_pymt_adj_amt,
355 curr_obligation_pymt_amt,
356 ytd_total_recp,
357 ytd_accepted_disb_amt,
358 ytd_posted_disb_amt,
359 ytd_admin_cost_allowance,
360 caps_drwn_dn_pymts,
361 gaps_last_date,
362 last_pymt_number,
363 creation_date,
364 created_by,
365 last_update_date,
366 last_updated_by,
367 last_update_login,
368 request_id,
369 program_id,
370 program_application_id,
371 program_update_date,
372 org_id
373 ) VALUES (
374 new_references.ess_id,
375 new_references.rep_pell_id,
376 new_references.duns_id,
377 new_references.gaps_award_num,
378 new_references.acct_schedule_number,
379 new_references.acct_schedule_date,
380 new_references.prev_obligation_amt,
381 new_references.obligation_adj_amt,
382 new_references.curr_obligation_amt,
383 new_references.prev_obligation_pymt_amt,
384 new_references.obligation_pymt_adj_amt,
385 new_references.curr_obligation_pymt_amt,
386 new_references.ytd_total_recp,
387 new_references.ytd_accepted_disb_amt,
388 new_references.ytd_posted_disb_amt,
389 new_references.ytd_admin_cost_allowance,
390 new_references.caps_drwn_dn_pymts,
391 new_references.gaps_last_date,
392 new_references.last_pymt_number,
393 x_last_update_date,
394 x_last_updated_by,
395 x_last_update_date,
396 x_last_updated_by,
397 x_last_update_login ,
398 x_request_id,
399 x_program_id,
400 x_program_application_id,
401 x_program_update_date,
402 l_org_id
403 );
404
405 OPEN c;
406 FETCH c INTO x_rowid;
407 IF (c%NOTFOUND) THEN
408 CLOSE c;
409 RAISE NO_DATA_FOUND;
410 END IF;
411 CLOSE c;
412
413 END insert_row;
414
415
416 PROCEDURE lock_row (
417 x_rowid IN VARCHAR2,
418 x_ess_id IN NUMBER,
419 x_rep_pell_id IN VARCHAR2,
420 x_duns_id IN VARCHAR2,
421 x_gaps_award_num IN VARCHAR2,
422 x_acct_schedule_number IN VARCHAR2,
423 x_acct_schedule_date IN DATE,
424 x_prev_obligation_amt IN NUMBER,
425 x_obligation_adj_amt IN NUMBER,
426 x_curr_obligation_amt IN NUMBER,
427 x_prev_obligation_pymt_amt IN NUMBER,
428 x_obligation_pymt_adj_amt IN NUMBER,
429 x_curr_obligation_pymt_amt IN NUMBER,
430 x_ytd_total_recp IN NUMBER,
431 x_ytd_accepted_disb_amt IN NUMBER,
432 x_ytd_posted_disb_amt IN NUMBER,
433 x_ytd_admin_cost_allowance IN NUMBER,
434 x_caps_drwn_dn_pymts IN NUMBER,
435 x_gaps_last_date IN DATE,
436 x_last_pymt_number IN VARCHAR2
437 ) AS
438 /*
439 || Created By : adhawan
440 || Created On : 09-JAN-2001
441 || Purpose : Handles the LOCK mechanism for the table.
442 || Known limitations, enhancements or remarks :
443 || Change History :
444 || Who When What
445 || (reverse chronological order - newest change first)
446 */
447 CURSOR c1 IS
448 SELECT
449 rep_pell_id,
450 duns_id,
451 gaps_award_num,
452 acct_schedule_number,
453 acct_schedule_date,
454 prev_obligation_amt,
455 obligation_adj_amt,
456 curr_obligation_amt,
457 prev_obligation_pymt_amt,
458 obligation_pymt_adj_amt,
459 curr_obligation_pymt_amt,
460 ytd_total_recp,
461 ytd_accepted_disb_amt,
462 ytd_posted_disb_amt,
463 ytd_admin_cost_allowance,
464 caps_drwn_dn_pymts,
465 gaps_last_date,
466 last_pymt_number
467 FROM igf_gr_elec_stat_sum_all
468 WHERE rowid = x_rowid
469 FOR UPDATE NOWAIT;
470
471 tlinfo c1%ROWTYPE;
472
473 BEGIN
474
475 OPEN c1;
476 FETCH c1 INTO tlinfo;
477 IF (c1%notfound) THEN
478 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
479 igs_ge_msg_stack.add;
480 CLOSE c1;
481 app_exception.raise_exception;
482 RETURN;
483 END IF;
484 CLOSE c1;
485
486 IF (
487 ((tlinfo.rep_pell_id = x_rep_pell_id) OR ((tlinfo.rep_pell_id IS NULL) AND (X_rep_pell_id IS NULL)))
488 AND ((tlinfo.duns_id = x_duns_id) OR ((tlinfo.duns_id IS NULL) AND (X_duns_id IS NULL)))
489 AND ((tlinfo.gaps_award_num = x_gaps_award_num) OR ((tlinfo.gaps_award_num IS NULL) AND (X_gaps_award_num IS NULL)))
490 AND ((tlinfo.acct_schedule_number = x_acct_schedule_number) OR ((tlinfo.acct_schedule_number IS NULL) AND (X_acct_schedule_number IS NULL)))
491 AND ((tlinfo.acct_schedule_date = x_acct_schedule_date) OR ((tlinfo.acct_schedule_date IS NULL) AND (X_acct_schedule_date IS NULL)))
492 AND ((tlinfo.prev_obligation_amt = x_prev_obligation_amt) OR ((tlinfo.prev_obligation_amt IS NULL) AND (X_prev_obligation_amt IS NULL)))
493 AND ((tlinfo.obligation_adj_amt = x_obligation_adj_amt) OR ((tlinfo.obligation_adj_amt IS NULL) AND (X_obligation_adj_amt IS NULL)))
494 AND ((tlinfo.curr_obligation_amt = x_curr_obligation_amt) OR ((tlinfo.curr_obligation_amt IS NULL) AND (X_curr_obligation_amt IS NULL)))
495 AND ((tlinfo.prev_obligation_pymt_amt = x_prev_obligation_pymt_amt) OR ((tlinfo.prev_obligation_pymt_amt IS NULL) AND (X_prev_obligation_pymt_amt IS NULL)))
496 AND ((tlinfo.obligation_pymt_adj_amt = x_obligation_pymt_adj_amt) OR ((tlinfo.obligation_pymt_adj_amt IS NULL) AND (X_obligation_pymt_adj_amt IS NULL)))
497 AND ((tlinfo.curr_obligation_pymt_amt = x_curr_obligation_pymt_amt) OR ((tlinfo.curr_obligation_pymt_amt IS NULL) AND (X_curr_obligation_pymt_amt IS NULL)))
498 AND ((tlinfo.ytd_total_recp = x_ytd_total_recp) OR ((tlinfo.ytd_total_recp IS NULL) AND (X_ytd_total_recp IS NULL)))
499 AND ((tlinfo.ytd_accepted_disb_amt = x_ytd_accepted_disb_amt) OR ((tlinfo.ytd_accepted_disb_amt IS NULL) AND (X_ytd_accepted_disb_amt IS NULL)))
500 AND ((tlinfo.ytd_posted_disb_amt = x_ytd_posted_disb_amt) OR ((tlinfo.ytd_posted_disb_amt IS NULL) AND (X_ytd_posted_disb_amt IS NULL)))
501 AND ((tlinfo.ytd_admin_cost_allowance = x_ytd_admin_cost_allowance) OR ((tlinfo.ytd_admin_cost_allowance IS NULL) AND (X_ytd_admin_cost_allowance IS NULL)))
502 AND ((tlinfo.caps_drwn_dn_pymts = x_caps_drwn_dn_pymts) OR ((tlinfo.caps_drwn_dn_pymts IS NULL) AND (X_caps_drwn_dn_pymts IS NULL)))
503 AND ((tlinfo.gaps_last_date = x_gaps_last_date) OR ((tlinfo.gaps_last_date IS NULL) AND (X_gaps_last_date IS NULL)))
504 AND ((tlinfo.last_pymt_number = x_last_pymt_number) OR ((tlinfo.last_pymt_number IS NULL) AND (X_last_pymt_number IS NULL)))
505 ) THEN
506 NULL;
507 ELSE
508 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
509 igs_ge_msg_stack.add;
510 app_exception.raise_exception;
511 END IF;
512
513 RETURN;
514
515 END lock_row;
516
517
518 PROCEDURE update_row (
519 x_rowid IN VARCHAR2,
520 x_ess_id IN NUMBER,
521 x_rep_pell_id IN VARCHAR2,
522 x_duns_id IN VARCHAR2,
523 x_gaps_award_num IN VARCHAR2,
524 x_acct_schedule_number IN VARCHAR2,
525 x_acct_schedule_date IN DATE,
526 x_prev_obligation_amt IN NUMBER,
527 x_obligation_adj_amt IN NUMBER,
528 x_curr_obligation_amt IN NUMBER,
529 x_prev_obligation_pymt_amt IN NUMBER,
530 x_obligation_pymt_adj_amt IN NUMBER,
531 x_curr_obligation_pymt_amt IN NUMBER,
532 x_ytd_total_recp IN NUMBER,
533 x_ytd_accepted_disb_amt IN NUMBER,
534 x_ytd_posted_disb_amt IN NUMBER,
535 x_ytd_admin_cost_allowance IN NUMBER,
536 x_caps_drwn_dn_pymts IN NUMBER,
537 x_gaps_last_date IN DATE,
538 x_last_pymt_number IN VARCHAR2,
539 x_mode IN VARCHAR2
540 ) AS
541 /*
542 || Created By : adhawan
543 || Created On : 09-JAN-2001
544 || Purpose : Handles the UPDATE DML logic for the table.
545 || Known limitations, enhancements or remarks :
546 || Change History :
547 || Who When What
548 || (reverse chronological order - newest change first)
549 */
550 x_last_update_date DATE ;
551 x_last_updated_by NUMBER;
552 x_last_update_login NUMBER;
553 x_request_id NUMBER;
554 x_program_id NUMBER;
555 x_program_application_id NUMBER;
556 x_program_update_date DATE;
557
558 BEGIN
559
560 x_last_update_date := SYSDATE;
561 IF (X_MODE = 'I') THEN
562 x_last_updated_by := 1;
563 x_last_update_login := 0;
564 ELSIF (x_mode = 'R') THEN
565 x_last_updated_by := fnd_global.user_id;
566 IF x_last_updated_by IS NULL THEN
567 x_last_updated_by := -1;
568 END IF;
569 x_last_update_login := fnd_global.login_id;
570 IF (x_last_update_login IS NULL) THEN
571 x_last_update_login := -1;
572 END IF;
573 ELSE
574 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
575 igs_ge_msg_stack.add;
576 app_exception.raise_exception;
577 END IF;
578
579 before_dml(
580 p_action => 'UPDATE',
581 x_rowid => x_rowid,
582 x_ess_id => x_ess_id,
583 x_rep_pell_id => x_rep_pell_id,
584 x_duns_id => x_duns_id,
585 x_gaps_award_num => x_gaps_award_num,
586 x_acct_schedule_number => x_acct_schedule_number,
587 x_acct_schedule_date => x_acct_schedule_date,
588 x_prev_obligation_amt => x_prev_obligation_amt,
589 x_obligation_adj_amt => x_obligation_adj_amt,
590 x_curr_obligation_amt => x_curr_obligation_amt,
591 x_prev_obligation_pymt_amt => x_prev_obligation_pymt_amt,
592 x_obligation_pymt_adj_amt => x_obligation_pymt_adj_amt,
593 x_curr_obligation_pymt_amt => x_curr_obligation_pymt_amt,
594 x_ytd_total_recp => x_ytd_total_recp,
595 x_ytd_accepted_disb_amt => x_ytd_accepted_disb_amt,
596 x_ytd_posted_disb_amt => x_ytd_posted_disb_amt,
597 x_ytd_admin_cost_allowance => x_ytd_admin_cost_allowance,
598 x_caps_drwn_dn_pymts => x_caps_drwn_dn_pymts,
599 x_gaps_last_date => x_gaps_last_date,
600 x_last_pymt_number => x_last_pymt_number,
601 x_creation_date => x_last_update_date,
602 x_created_by => x_last_updated_by,
603 x_last_update_date => x_last_update_date,
604 x_last_updated_by => x_last_updated_by,
605 x_last_update_login => x_last_update_login
606 );
607
608 IF (x_mode = 'R') THEN
609 x_request_id := fnd_global.conc_request_id;
610 x_program_id := fnd_global.conc_program_id;
611 x_program_application_id := fnd_global.prog_appl_id;
612 IF (x_request_id = -1) THEN
613 x_request_id := old_references.request_id;
614 x_program_id := old_references.program_id;
615 x_program_application_id := old_references.program_application_id;
616 x_program_update_date := old_references.program_update_date;
617 ELSE
618 x_program_update_date := SYSDATE;
619 END IF;
620 END IF;
621
622 UPDATE igf_gr_elec_stat_sum_all
623 SET
624 rep_pell_id = new_references.rep_pell_id,
625 duns_id = new_references.duns_id,
626 gaps_award_num = new_references.gaps_award_num,
627 acct_schedule_number = new_references.acct_schedule_number,
628 acct_schedule_date = new_references.acct_schedule_date,
629 prev_obligation_amt = new_references.prev_obligation_amt,
630 obligation_adj_amt = new_references.obligation_adj_amt,
631 curr_obligation_amt = new_references.curr_obligation_amt,
632 prev_obligation_pymt_amt = new_references.prev_obligation_pymt_amt,
633 obligation_pymt_adj_amt = new_references.obligation_pymt_adj_amt,
634 curr_obligation_pymt_amt = new_references.curr_obligation_pymt_amt,
635 ytd_total_recp = new_references.ytd_total_recp,
636 ytd_accepted_disb_amt = new_references.ytd_accepted_disb_amt,
637 ytd_posted_disb_amt = new_references.ytd_posted_disb_amt,
638 ytd_admin_cost_allowance = new_references.ytd_admin_cost_allowance,
639 caps_drwn_dn_pymts = new_references.caps_drwn_dn_pymts,
640 gaps_last_date = new_references.gaps_last_date,
641 last_pymt_number = new_references.last_pymt_number,
642 last_update_date = x_last_update_date,
643 last_updated_by = x_last_updated_by,
644 last_update_login = x_last_update_login ,
645 request_id = x_request_id,
646 program_id = x_program_id,
647 program_application_id = x_program_application_id,
648 program_update_date = x_program_update_date
649 WHERE rowid = x_rowid;
650
651 IF (SQL%NOTFOUND) THEN
652 RAISE NO_DATA_FOUND;
653 END IF;
654
655 END update_row;
656
657
658 PROCEDURE add_row (
659 x_rowid IN OUT NOCOPY VARCHAR2,
660 x_ess_id IN OUT NOCOPY NUMBER,
661 x_rep_pell_id IN VARCHAR2,
662 x_duns_id IN VARCHAR2,
663 x_gaps_award_num IN VARCHAR2,
664 x_acct_schedule_number IN VARCHAR2,
665 x_acct_schedule_date IN DATE,
666 x_prev_obligation_amt IN NUMBER,
667 x_obligation_adj_amt IN NUMBER,
668 x_curr_obligation_amt IN NUMBER,
669 x_prev_obligation_pymt_amt IN NUMBER,
670 x_obligation_pymt_adj_amt IN NUMBER,
671 x_curr_obligation_pymt_amt IN NUMBER,
672 x_ytd_total_recp IN NUMBER,
673 x_ytd_accepted_disb_amt IN NUMBER,
674 x_ytd_posted_disb_amt IN NUMBER,
675 x_ytd_admin_cost_allowance IN NUMBER,
676 x_caps_drwn_dn_pymts IN NUMBER,
677 x_gaps_last_date IN DATE,
678 x_last_pymt_number IN VARCHAR2,
679 x_mode IN VARCHAR2
680 ) AS
681 /*
682 || Created By : adhawan
683 || Created On : 09-JAN-2001
684 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
685 || Known limitations, enhancements or remarks :
686 || Change History :
687 || Who When What
688 || (reverse chronological order - newest change first)
689 */
690 CURSOR c1 IS
691 SELECT rowid
692 FROM igf_gr_elec_stat_sum_all
693 WHERE ess_id = x_ess_id;
694
695 BEGIN
696
697 OPEN c1;
698 FETCH c1 INTO x_rowid;
699 IF (c1%NOTFOUND) THEN
700 CLOSE c1;
701
702 insert_row (
703 x_rowid,
704 x_ess_id,
705 x_rep_pell_id,
706 x_duns_id,
707 x_gaps_award_num,
708 x_acct_schedule_number,
709 x_acct_schedule_date,
710 x_prev_obligation_amt,
711 x_obligation_adj_amt,
712 x_curr_obligation_amt,
713 x_prev_obligation_pymt_amt,
714 x_obligation_pymt_adj_amt,
715 x_curr_obligation_pymt_amt,
716 x_ytd_total_recp,
717 x_ytd_accepted_disb_amt,
718 x_ytd_posted_disb_amt,
719 x_ytd_admin_cost_allowance,
720 x_caps_drwn_dn_pymts,
721 x_gaps_last_date,
722 x_last_pymt_number,
723 x_mode
724 );
725 RETURN;
726 END IF;
727 CLOSE c1;
728
729 update_row (
730 x_rowid,
731 x_ess_id,
732 x_rep_pell_id,
733 x_duns_id,
734 x_gaps_award_num,
735 x_acct_schedule_number,
736 x_acct_schedule_date,
737 x_prev_obligation_amt,
738 x_obligation_adj_amt,
739 x_curr_obligation_amt,
740 x_prev_obligation_pymt_amt,
741 x_obligation_pymt_adj_amt,
742 x_curr_obligation_pymt_amt,
743 x_ytd_total_recp,
744 x_ytd_accepted_disb_amt,
745 x_ytd_posted_disb_amt,
746 x_ytd_admin_cost_allowance,
747 x_caps_drwn_dn_pymts,
748 x_gaps_last_date,
749 x_last_pymt_number,
750 x_mode
751 );
752
753 END add_row;
754
755
756 PROCEDURE delete_row (
757 x_rowid IN VARCHAR2
758 ) AS
759 /*
760 || Created By : adhawan
761 || Created On : 09-JAN-2001
762 || Purpose : Handles the DELETE DML logic for the table.
763 || Known limitations, enhancements or remarks :
764 || Change History :
765 || Who When What
766 || (reverse chronological order - newest change first)
767 */
768 BEGIN
769
770 before_dml (
771 p_action => 'DELETE',
772 x_rowid => x_rowid
773 );
774
775 DELETE FROM igf_gr_elec_stat_sum_all
776 WHERE rowid = x_rowid;
777
778 IF (SQL%NOTFOUND) THEN
779 RAISE NO_DATA_FOUND;
780 END IF;
781
782 END delete_row;
783
784
785 END igf_gr_elec_stat_sum_pkg;