[Home] [Help]
PACKAGE BODY: APPS.IGF_GR_ELEC_STAT_DET_PKG
Source
1 PACKAGE BODY igf_gr_elec_stat_det_pkg AS
2 /* $Header: IGFGI10B.pls 115.6 2002/11/28 14:17:33 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_gr_elec_stat_det_all%ROWTYPE;
6 new_references igf_gr_elec_stat_det_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_esd_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_transaction_date IN DATE ,
16 x_db_cr_flag IN VARCHAR2,
17 x_adj_amt IN NUMBER ,
18 x_gaps_process_date IN DATE ,
19 x_adj_batch_id IN VARCHAR2,
20 x_creation_date IN DATE ,
21 x_created_by IN NUMBER ,
22 x_last_update_date IN DATE ,
23 x_last_updated_by IN NUMBER ,
24 x_last_update_login IN NUMBER
25 ) AS
26 /*
27 || Created By : adhawan
28 || Created On : 20-DEC-2000
29 || Purpose : Initialises the Old and New references for the columns of the table.
30 || Known limitations, enhancements or remarks :
31 || Change History :
32 || Who When What
33 || (reverse chronological order - newest change first)
34 */
35
36 CURSOR cur_old_ref_values IS
37 SELECT *
38 FROM igf_gr_elec_stat_det_all
39 WHERE rowid = x_rowid;
40
41 BEGIN
42
43 l_rowid := x_rowid;
44
45 -- Code for setting the Old and New Reference Values.
46 -- Populate Old Values.
47 OPEN cur_old_ref_values;
48 FETCH cur_old_ref_values INTO old_references;
49 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
50 CLOSE cur_old_ref_values;
51 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
52 igs_ge_msg_stack.add;
53 app_exception.raise_exception;
54 RETURN;
55 END IF;
56 CLOSE cur_old_ref_values;
57
58 -- Populate New Values.
59 new_references.esd_id := x_esd_id;
60 new_references.rep_pell_id := x_rep_pell_id;
61 new_references.duns_id := x_duns_id;
62 new_references.gaps_award_num := x_gaps_award_num;
63 new_references.transaction_date := x_transaction_date;
64 new_references.db_cr_flag := x_db_cr_flag;
65 new_references.adj_amt := x_adj_amt;
66 new_references.gaps_process_date := x_gaps_process_date;
67 new_references.adj_batch_id := x_adj_batch_id;
68
69 IF (p_action = 'UPDATE') THEN
70 new_references.creation_date := old_references.creation_date;
71 new_references.created_by := old_references.created_by;
72 ELSE
73 new_references.creation_date := x_creation_date;
74 new_references.created_by := x_created_by;
75 END IF;
76
77 new_references.last_update_date := x_last_update_date;
78 new_references.last_updated_by := x_last_updated_by;
79 new_references.last_update_login := x_last_update_login;
80
81 END set_column_values;
82
83
84 FUNCTION get_pk_for_validation (
85 x_esd_id IN NUMBER
86 ) RETURN BOOLEAN AS
87 /*
88 || Created By : adhawan
89 || Created On : 20-DEC-2000
90 || Purpose : Validates the Primary Key of the table.
91 || Known limitations, enhancements or remarks :
92 || Change History :
93 || Who When What
94 || (reverse chronological order - newest change first)
95 */
96 CURSOR cur_rowid IS
97 SELECT rowid
98 FROM igf_gr_elec_stat_det_all
99 WHERE esd_id = x_esd_id
100 FOR UPDATE NOWAIT;
101
102 lv_rowid cur_rowid%RowType;
103
104 BEGIN
105
106 OPEN cur_rowid;
107 FETCH cur_rowid INTO lv_rowid;
108 IF (cur_rowid%FOUND) THEN
109 CLOSE cur_rowid;
110 RETURN(TRUE);
111 ELSE
112 CLOSE cur_rowid;
113 RETURN(FALSE);
114 END IF;
115
116 END get_pk_for_validation;
117
118
119 PROCEDURE before_dml (
120 p_action IN VARCHAR2,
121 x_rowid IN VARCHAR2,
122 x_esd_id IN NUMBER ,
123 x_rep_pell_id IN VARCHAR2,
124 x_duns_id IN VARCHAR2,
125 x_gaps_award_num IN VARCHAR2,
126 x_transaction_date IN DATE ,
127 x_db_cr_flag IN VARCHAR2,
128 x_adj_amt IN NUMBER ,
129 x_gaps_process_date IN DATE ,
130 x_adj_batch_id IN VARCHAR2,
131 x_creation_date IN DATE ,
132 x_created_by IN NUMBER ,
133 x_last_update_date IN DATE ,
134 x_last_updated_by IN NUMBER ,
135 x_last_update_login IN NUMBER
136 ) AS
137 /*
138 || Created By : adhawan
139 || Created On : 20-DEC-2000
140 || Purpose : Initialises the columns, Checks Constraints, Calls the
141 || Trigger Handlers for the table, before any DML operation.
142 || Known limitations, enhancements or remarks :
143 || Change History :
144 || Who When What
145 || (reverse chronological order - newest change first)
146 */
147 BEGIN
148
149 set_column_values (
150 p_action,
151 x_rowid,
152 x_esd_id,
153 x_rep_pell_id,
154 x_duns_id,
155 x_gaps_award_num,
156 x_transaction_date,
157 x_db_cr_flag,
158 x_adj_amt,
159 x_gaps_process_date,
160 x_adj_batch_id,
161 x_creation_date,
162 x_created_by,
163 x_last_update_date,
164 x_last_updated_by,
165 x_last_update_login
166 );
167
168 IF (p_action = 'INSERT') THEN
169 -- Call all the procedures related to Before Insert.
170 IF ( get_pk_for_validation(
171 new_references.esd_id
172 )
173 ) THEN
174 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
175 igs_ge_msg_stack.add;
176 app_exception.raise_exception;
177 END IF;
178 ELSIF (p_action = 'VALIDATE_INSERT') THEN
179 -- Call all the procedures related to Before Insert.
180 IF ( get_pk_for_validation (
181 new_references.esd_id
182 )
183 ) THEN
184 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
185 igs_ge_msg_stack.add;
186 app_exception.raise_exception;
187 END IF;
188 END IF;
189
190 END before_dml;
191
192
193 PROCEDURE insert_row (
194 x_rowid IN OUT NOCOPY VARCHAR2,
195 x_esd_id IN OUT NOCOPY NUMBER,
196 x_rep_pell_id IN VARCHAR2,
197 x_duns_id IN VARCHAR2,
198 x_gaps_award_num IN VARCHAR2,
199 x_transaction_date IN DATE,
200 x_db_cr_flag IN VARCHAR2,
201 x_adj_amt IN NUMBER,
202 x_gaps_process_date IN DATE,
203 x_adj_batch_id IN VARCHAR2,
204 x_mode IN VARCHAR2
205 ) AS
206 /*
207 || Created By : adhawan
208 || Created On : 20-DEC-2000
209 || Purpose : Handles the INSERT DML logic for the table.
210 || Known limitations, enhancements or remarks :
211 || Change History :
212 || Who When What
213 || (reverse chronological order - newest change first)
214 */
215 CURSOR c IS
216 SELECT rowid
217 FROM igf_gr_elec_stat_det_all
218 WHERE esd_id = x_esd_id;
219
220 x_last_update_date DATE;
221 x_last_updated_by NUMBER;
222 x_last_update_login NUMBER;
223 x_request_id NUMBER;
224 x_program_id NUMBER;
225 x_program_application_id NUMBER;
226 x_program_update_date DATE;
227
228 l_org_id igf_gr_elec_stat_det_all.org_id%TYPE;
229
230 BEGIN
231
232 l_org_id := igf_aw_gen.get_org_id;
233
234 x_last_update_date := SYSDATE;
235 IF (x_mode = 'I') THEN
236 x_last_updated_by := 1;
237 x_last_update_login := 0;
238 ELSIF (x_mode = 'R') THEN
239 x_last_updated_by := fnd_global.user_id;
240 IF (x_last_updated_by IS NULL) THEN
241 x_last_updated_by := -1;
242 END IF;
243 x_last_update_login := fnd_global.login_id;
244 IF (x_last_update_login IS NULL) THEN
245 x_last_update_login := -1;
246 END IF;
247 x_request_id := fnd_global.conc_request_id;
248 x_program_id := fnd_global.conc_program_id;
249 x_program_application_id := fnd_global.prog_appl_id;
250
251 IF (x_request_id = -1) THEN
252 x_request_id := NULL;
253 x_program_id := NULL;
254 x_program_application_id := NULL;
255 x_program_update_date := NULL;
256 ELSE
257 x_program_update_date := SYSDATE;
258 END IF;
259 ELSE
260 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
261 igs_ge_msg_stack.add;
262 app_exception.raise_exception;
263 END IF;
264 SELECT igf_gr_elec_stat_det_s.NEXTVAL INTO x_esd_id FROM dual;
265
266 before_dml(
267 p_action => 'INSERT',
268 x_rowid => x_rowid,
269 x_esd_id => x_esd_id,
270 x_rep_pell_id => x_rep_pell_id,
271 x_duns_id => x_duns_id,
272 x_gaps_award_num => x_gaps_award_num,
273 x_transaction_date => x_transaction_date,
274 x_db_cr_flag => x_db_cr_flag,
275 x_adj_amt => x_adj_amt,
276 x_gaps_process_date => x_gaps_process_date,
277 x_adj_batch_id => x_adj_batch_id,
278 x_creation_date => x_last_update_date,
279 x_created_by => x_last_updated_by,
280 x_last_update_date => x_last_update_date,
281 x_last_updated_by => x_last_updated_by,
282 x_last_update_login => x_last_update_login
283 );
284
285 INSERT INTO igf_gr_elec_stat_det_all (
286 esd_id,
287 rep_pell_id,
288 duns_id,
289 gaps_award_num,
290 transaction_date,
291 db_cr_flag,
292 adj_amt,
293 gaps_process_date,
294 adj_batch_id,
295 creation_date,
296 created_by,
297 last_update_date,
298 last_updated_by,
299 last_update_login,
300 request_id,
301 program_id,
302 program_application_id,
303 program_update_date,
304 org_id
305 ) VALUES (
306 new_references.esd_id,
307 new_references.rep_pell_id,
308 new_references.duns_id,
309 new_references.gaps_award_num,
310 new_references.transaction_date,
311 new_references.db_cr_flag,
312 new_references.adj_amt,
313 new_references.gaps_process_date,
314 new_references.adj_batch_id,
315 x_last_update_date,
316 x_last_updated_by,
317 x_last_update_date,
318 x_last_updated_by,
319 x_last_update_login ,
320 x_request_id,
321 x_program_id,
322 x_program_application_id,
323 x_program_update_date,
324 l_org_id
325 );
326
327 OPEN c;
328 FETCH c INTO x_rowid;
329 IF (c%NOTFOUND) THEN
330 CLOSE c;
331 RAISE NO_DATA_FOUND;
332 END IF;
333 CLOSE c;
334
335 END insert_row;
336
337
338 PROCEDURE lock_row (
339 x_rowid IN VARCHAR2,
340 x_esd_id IN NUMBER,
341 x_rep_pell_id IN VARCHAR2,
342 x_duns_id IN VARCHAR2,
343 x_gaps_award_num IN VARCHAR2,
344 x_transaction_date IN DATE,
345 x_db_cr_flag IN VARCHAR2,
346 x_adj_amt IN NUMBER,
347 x_gaps_process_date IN DATE,
348 x_adj_batch_id IN VARCHAR2
349 ) AS
350 /*
351 || Created By : adhawan
352 || Created On : 20-DEC-2000
353 || Purpose : Handles the LOCK mechanism for the table.
354 || Known limitations, enhancements or remarks :
355 || Change History :
356 || Who When What
357 || (reverse chronological order - newest change first)
358 */
359 CURSOR c1 IS
360 SELECT
361 rep_pell_id,
362 duns_id,
363 gaps_award_num,
364 transaction_date,
365 db_cr_flag,
366 adj_amt,
367 gaps_process_date,
368 adj_batch_id
369 FROM igf_gr_elec_stat_det_all
370 WHERE rowid = x_rowid
371 FOR UPDATE NOWAIT;
372
373 tlinfo c1%ROWTYPE;
374
375 BEGIN
376
377 OPEN c1;
378 FETCH c1 INTO tlinfo;
379 IF (c1%notfound) THEN
380 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
381 igs_ge_msg_stack.add;
382 CLOSE c1;
383 app_exception.raise_exception;
384 RETURN;
385 END IF;
386 CLOSE c1;
387
388 IF (
389 ((tlinfo.rep_pell_id = x_rep_pell_id) OR ((tlinfo.rep_pell_id IS NULL) AND (X_rep_pell_id IS NULL)))
390 AND ((tlinfo.duns_id = x_duns_id) OR ((tlinfo.duns_id IS NULL) AND (X_duns_id IS NULL)))
391 AND ((tlinfo.gaps_award_num = x_gaps_award_num) OR ((tlinfo.gaps_award_num IS NULL) AND (X_gaps_award_num IS NULL)))
392 AND ((tlinfo.transaction_date = x_transaction_date) OR ((tlinfo.transaction_date IS NULL) AND (X_transaction_date IS NULL)))
393 AND ((tlinfo.db_cr_flag = x_db_cr_flag) OR ((tlinfo.db_cr_flag IS NULL) AND (X_db_cr_flag IS NULL)))
394 AND ((tlinfo.adj_amt = x_adj_amt) OR ((tlinfo.adj_amt IS NULL) AND (X_adj_amt IS NULL)))
395 AND ((tlinfo.gaps_process_date = x_gaps_process_date) OR ((tlinfo.gaps_process_date IS NULL) AND (X_gaps_process_date IS NULL)))
396 AND ((tlinfo.adj_batch_id = x_adj_batch_id) OR ((tlinfo.adj_batch_id IS NULL) AND (X_adj_batch_id IS NULL)))
397 ) THEN
398 NULL;
399 ELSE
400 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
401 igs_ge_msg_stack.add;
402 app_exception.raise_exception;
403 END IF;
404
405 RETURN;
406
407 END lock_row;
408
409
410 PROCEDURE update_row (
411 x_rowid IN VARCHAR2,
412 x_esd_id IN NUMBER,
413 x_rep_pell_id IN VARCHAR2,
414 x_duns_id IN VARCHAR2,
415 x_gaps_award_num IN VARCHAR2,
416 x_transaction_date IN DATE,
417 x_db_cr_flag IN VARCHAR2,
418 x_adj_amt IN NUMBER,
419 x_gaps_process_date IN DATE,
420 x_adj_batch_id IN VARCHAR2,
421 x_mode IN VARCHAR2
422 ) AS
423 /*
424 || Created By : adhawan
425 || Created On : 20-DEC-2000
426 || Purpose : Handles the UPDATE DML logic for the table.
427 || Known limitations, enhancements or remarks :
428 || Change History :
429 || Who When What
430 || (reverse chronological order - newest change first)
431 */
432 x_last_update_date DATE ;
433 x_last_updated_by NUMBER;
434 x_last_update_login NUMBER;
435 x_request_id NUMBER;
436 x_program_id NUMBER;
437 x_program_application_id NUMBER;
438 x_program_update_date DATE;
439
440 BEGIN
441
442 x_last_update_date := SYSDATE;
443 IF (X_MODE = 'I') THEN
444 x_last_updated_by := 1;
445 x_last_update_login := 0;
446 ELSIF (x_mode = 'R') THEN
447 x_last_updated_by := fnd_global.user_id;
448 IF x_last_updated_by IS NULL THEN
449 x_last_updated_by := -1;
450 END IF;
451 x_last_update_login := fnd_global.login_id;
452 IF (x_last_update_login IS NULL) THEN
453 x_last_update_login := -1;
454 END IF;
455 ELSE
456 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
457 igs_ge_msg_stack.add;
458 app_exception.raise_exception;
459 END IF;
460
461 before_dml(
462 p_action => 'UPDATE',
463 x_rowid => x_rowid,
464 x_esd_id => x_esd_id,
465 x_rep_pell_id => x_rep_pell_id,
466 x_duns_id => x_duns_id,
467 x_gaps_award_num => x_gaps_award_num,
468 x_transaction_date => x_transaction_date,
469 x_db_cr_flag => x_db_cr_flag,
470 x_adj_amt => x_adj_amt,
471 x_gaps_process_date => x_gaps_process_date,
472 x_adj_batch_id => x_adj_batch_id,
473 x_creation_date => x_last_update_date,
474 x_created_by => x_last_updated_by,
475 x_last_update_date => x_last_update_date,
476 x_last_updated_by => x_last_updated_by,
477 x_last_update_login => x_last_update_login
478 );
479
480 IF (x_mode = 'R') THEN
481 x_request_id := fnd_global.conc_request_id;
482 x_program_id := fnd_global.conc_program_id;
483 x_program_application_id := fnd_global.prog_appl_id;
484 IF (x_request_id = -1) THEN
485 x_request_id := old_references.request_id;
486 x_program_id := old_references.program_id;
487 x_program_application_id := old_references.program_application_id;
488 x_program_update_date := old_references.program_update_date;
489 ELSE
490 x_program_update_date := SYSDATE;
491 END IF;
492 END IF;
493
494 UPDATE igf_gr_elec_stat_det_all
495 SET
496 rep_pell_id = new_references.rep_pell_id,
497 duns_id = new_references.duns_id,
498 gaps_award_num = new_references.gaps_award_num,
499 transaction_date = new_references.transaction_date,
500 db_cr_flag = new_references.db_cr_flag,
501 adj_amt = new_references.adj_amt,
502 gaps_process_date = new_references.gaps_process_date,
503 adj_batch_id = new_references.adj_batch_id,
504 last_update_date = x_last_update_date,
505 last_updated_by = x_last_updated_by,
506 last_update_login = x_last_update_login ,
507 request_id = x_request_id,
508 program_id = x_program_id,
509 program_application_id = x_program_application_id,
510 program_update_date = x_program_update_date
511 WHERE rowid = x_rowid;
512
513 IF (SQL%NOTFOUND) THEN
514 RAISE NO_DATA_FOUND;
515 END IF;
516
517 END update_row;
518
519
520 PROCEDURE add_row (
521 x_rowid IN OUT NOCOPY VARCHAR2,
522 x_esd_id IN OUT NOCOPY NUMBER,
523 x_rep_pell_id IN VARCHAR2,
524 x_duns_id IN VARCHAR2,
525 x_gaps_award_num IN VARCHAR2,
526 x_transaction_date IN DATE,
527 x_db_cr_flag IN VARCHAR2,
528 x_adj_amt IN NUMBER,
529 x_gaps_process_date IN DATE,
530 x_adj_batch_id IN VARCHAR2,
531 x_mode IN VARCHAR2
532 ) AS
533 /*
534 || Created By : adhawan
535 || Created On : 20-DEC-2000
536 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
537 || Known limitations, enhancements or remarks :
538 || Change History :
539 || Who When What
540 || (reverse chronological order - newest change first)
541 */
542 CURSOR c1 IS
543 SELECT rowid
544 FROM igf_gr_elec_stat_det_all
545 WHERE esd_id = x_esd_id;
546
547 BEGIN
548
549 OPEN c1;
550 FETCH c1 INTO x_rowid;
551 IF (c1%NOTFOUND) THEN
552 CLOSE c1;
553
554 insert_row (
555 x_rowid,
556 x_esd_id,
557 x_rep_pell_id,
558 x_duns_id,
559 x_gaps_award_num,
560 x_transaction_date,
561 x_db_cr_flag,
562 x_adj_amt,
563 x_gaps_process_date,
564 x_adj_batch_id,
565 x_mode
566 );
567 RETURN;
568 END IF;
569 CLOSE c1;
570
571 update_row (
572 x_rowid,
573 x_esd_id,
574 x_rep_pell_id,
575 x_duns_id,
576 x_gaps_award_num,
577 x_transaction_date,
578 x_db_cr_flag,
579 x_adj_amt,
580 x_gaps_process_date,
581 x_adj_batch_id,
582 x_mode
583 );
584
585 END add_row;
586
587
588 PROCEDURE delete_row (
589 x_rowid IN VARCHAR2
590 ) AS
591 /*
592 || Created By : adhawan
593 || Created On : 20-DEC-2000
594 || Purpose : Handles the DELETE DML logic for the table.
595 || Known limitations, enhancements or remarks :
596 || Change History :
597 || Who When What
598 || (reverse chronological order - newest change first)
599 */
600 BEGIN
601
602 before_dml (
603 p_action => 'DELETE',
604 x_rowid => x_rowid
605 );
606
607 DELETE FROM igf_gr_elec_stat_det_all
608 WHERE rowid = x_rowid;
609
610 IF (SQL%NOTFOUND) THEN
611 RAISE NO_DATA_FOUND;
612 END IF;
613
614 END delete_row;
615
616
617 END igf_gr_elec_stat_det_pkg;