[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_AWD_LTR_TMP_PKG
Source
1 PACKAGE BODY igf_aw_awd_ltr_tmp_pkg AS
2 /* $Header: IGFWI51B.pls 120.0 2005/06/01 13:53:06 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGF_AW_AWD_LTR_TMP%ROWTYPE;
6 new_references IGF_AW_AWD_LTR_TMP%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_line_id IN NUMBER DEFAULT NULL,
12 x_person_id IN NUMBER DEFAULT NULL,
13 x_fund_code IN VARCHAR2 DEFAULT NULL,
14 x_fund_description IN VARCHAR2 DEFAULT NULL,
15 x_award_name IN VARCHAR2 DEFAULT NULL,
16 x_ci_cal_type IN VARCHAR2 DEFAULT NULL,
17 x_ci_sequence_number IN NUMBER DEFAULT NULL,
18 x_award_total IN NUMBER DEFAULT NULL,
19 x_term_amount_text IN VARCHAR2 DEFAULT NULL,
20 x_creation_date IN DATE DEFAULT NULL,
21 x_created_by IN NUMBER DEFAULT NULL,
22 x_last_update_date IN DATE DEFAULT NULL,
23 x_last_updated_by IN NUMBER DEFAULT NULL,
24 x_last_update_login IN NUMBER DEFAULT NULL
25 ) AS
26 /*
27 || Created By : svenkata
28 || Created On : 07-FEB-2002
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_AW_AWD_LTR_TMP
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.line_id := x_line_id;
60 new_references.person_id := x_person_id;
61 new_references.fund_code := x_fund_code;
62 new_references.fund_description := x_fund_description;
63 new_references.award_name := x_award_name;
64 new_references.ci_cal_type := x_ci_cal_type;
65 new_references.ci_sequence_number := x_ci_sequence_number;
66 new_references.award_total := x_award_total;
67 new_references.term_amount_text := x_term_amount_text;
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 PROCEDURE before_dml (
85 p_action IN VARCHAR2,
86 x_rowid IN VARCHAR2 DEFAULT NULL,
87 x_line_id IN NUMBER DEFAULT NULL,
88 x_person_id IN NUMBER DEFAULT NULL,
89 x_fund_code IN VARCHAR2 DEFAULT NULL,
90 x_fund_description IN VARCHAR2 DEFAULT NULL,
91 x_award_name IN VARCHAR2 DEFAULT NULL,
92 x_ci_cal_type IN VARCHAR2 DEFAULT NULL,
93 x_ci_sequence_number IN NUMBER DEFAULT NULL,
94 x_award_total IN NUMBER DEFAULT NULL,
95 x_term_amount_text IN VARCHAR2 DEFAULT NULL,
96 x_creation_date IN DATE DEFAULT NULL,
97 x_created_by IN NUMBER DEFAULT NULL,
98 x_last_update_date IN DATE DEFAULT NULL,
99 x_last_updated_by IN NUMBER DEFAULT NULL,
100 x_last_update_login IN NUMBER DEFAULT NULL
101 ) AS
102 /*
103 || Created By : svenkata
104 || Created On : 07-FEB-2002
105 || Purpose : Initialises the columns, Checks Constraints, Calls the
106 || Trigger Handlers for the table, before any DML operation.
107 || Known limitations, enhancements or remarks :
108 || Change History :
109 || Who When What
110 || (reverse chronological order - newest change first)
111 */
112 BEGIN
113
114 set_column_values (
115 p_action,
116 x_rowid,
117 x_line_id,
118 x_person_id,
119 x_fund_code,
120 x_fund_description,
121 x_award_name,
122 x_ci_cal_type,
123 x_ci_sequence_number,
124 x_award_total ,
125 x_term_amount_text ,
126 x_creation_date,
127 x_created_by,
128 x_last_update_date,
129 x_last_updated_by,
130 x_last_update_login
131 );
132
133 IF (p_action = 'INSERT') THEN
134 -- Call all the procedures related to Before Insert.
135 IF ( get_pk_for_validation(
136 new_references.line_id,
137 new_references.person_id,
138 new_references.ci_cal_type,
139 new_references.ci_sequence_number
140 )
141 ) THEN
142 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
143 igs_ge_msg_stack.add;
144 app_exception.raise_exception;
145 END IF;
146
147 ELSIF (p_action = 'VALIDATE_INSERT') THEN
148 -- Call all the procedures related to Before Insert.
149 IF ( get_pk_for_validation (
150 new_references.line_id,
151 new_references.person_id,
152 new_references.ci_cal_type,
153 new_references.ci_sequence_number
154 )
155 ) THEN
156 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
157 igs_ge_msg_stack.add;
158 app_exception.raise_exception;
159 END IF;
160
161 END IF;
162
163 END before_dml;
164
165
166 PROCEDURE insert_row (
167 x_rowid IN OUT NOCOPY VARCHAR2,
168 x_line_id IN NUMBER,
169 x_person_id IN NUMBER,
170 x_fund_code IN VARCHAR2,
171 x_fund_description IN VARCHAR2 DEFAULT NULL,
172 x_award_name IN VARCHAR2,
173 x_ci_cal_type IN VARCHAR2 DEFAULT NULL,
174 x_ci_sequence_number IN NUMBER DEFAULT NULL,
175 x_award_total IN NUMBER DEFAULT NULL,
176 x_term_amount_text IN VARCHAR2 DEFAULT NULL,
177 x_mode IN VARCHAR2 DEFAULT 'R'
178 ) AS
179 /*
180 || Created By : svenkata
181 || Created On : 07-FEB-2002
182 || Purpose : Handles the INSERT DML logic for the table.
183 || Known limitations, enhancements or remarks :
184 || Change History :
185 || Who When What
186 || (reverse chronological order - newest change first)
187 */
188 CURSOR c IS
189 SELECT ROWID
190 FROM IGF_AW_AWD_LTR_TMP
191 WHERE line_id = new_references.line_id ;
192
193 x_last_update_date DATE;
194 x_last_updated_by NUMBER;
195 x_last_update_login NUMBER;
196
197 BEGIN
198
199 x_last_update_date := SYSDATE;
200 IF (x_mode = 'I') THEN
201 x_last_updated_by := 1;
202 x_last_update_login := 0;
203 ELSIF (x_mode = 'R') THEN
204 x_last_updated_by := fnd_global.user_id;
205 IF (x_last_updated_by IS NULL) THEN
206 x_last_updated_by := -1;
207 END IF;
208 x_last_update_login := fnd_global.login_id;
209 IF (x_last_update_login IS NULL) THEN
210 x_last_update_login := -1;
211 END IF;
212 ELSE
213 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
214 igs_ge_msg_stack.ADD;
215 app_exception.raise_exception;
216 END IF;
217
218 before_dml(
219 p_action => 'INSERT',
220 x_rowid => x_rowid,
221 x_line_id => x_line_id,
222 x_person_id => x_person_id,
223 x_fund_code => x_fund_code,
224 x_fund_description => x_fund_description,
225 x_award_name => x_award_name,
226 x_ci_cal_type => x_ci_cal_type,
227 x_ci_sequence_number => x_ci_sequence_number,
228 x_award_total => x_award_total ,
229 x_term_amount_text => x_term_amount_text,
230 x_creation_date => x_last_update_date,
231 x_created_by => x_last_updated_by,
232 x_last_update_date => x_last_update_date,
233 x_last_updated_by => x_last_updated_by,
234 x_last_update_login => x_last_update_login
235 );
236
237 INSERT INTO IGF_AW_AWD_LTR_TMP (
238 line_id,
239 person_id,
240 fund_code,
241 fund_description,
242 award_name,
243 ci_cal_type,
244 ci_sequence_number,
245 award_total ,
246 term_amount_text ,
247 creation_date,
248 created_by,
249 last_update_date,
250 last_updated_by,
251 last_update_login
252 ) VALUES (
253 new_references.line_id,
254 new_references.person_id,
255 new_references.fund_code,
256 new_references.fund_description,
257 new_references.award_name,
258 new_references.ci_cal_type,
259 new_references.ci_sequence_number,
260 new_references.award_total ,
261 new_references.term_amount_text ,
262 x_last_update_date,
263 x_last_updated_by,
264 x_last_update_date,
265 x_last_updated_by,
266 x_last_update_login
267 );
268
269 OPEN c;
270 FETCH c INTO x_rowid;
271 IF (c%NOTFOUND) THEN
272 CLOSE c;
273 RAISE NO_DATA_FOUND;
274 END IF;
275 CLOSE c;
276
277 END insert_row;
278
279
280 PROCEDURE lock_row (
281 x_rowid IN VARCHAR2,
282 x_line_id IN NUMBER,
283 x_person_id IN NUMBER,
284 x_fund_code IN VARCHAR2 DEFAULT NULL,
285 x_fund_description IN VARCHAR2 DEFAULT NULL,
286 x_award_name IN VARCHAR2 DEFAULT NULL,
287 x_ci_cal_type IN VARCHAR2 DEFAULT NULL,
288 x_ci_sequence_number IN NUMBER DEFAULT NULL,
289 x_award_total IN NUMBER DEFAULT NULL,
290 x_term_amount_text IN VARCHAR2 DEFAULT NULL
291 ) AS
292 /*
293 || Created By : svenkata
294 || Created On : 07-FEB-2002
295 || Purpose : Handles the LOCK mechanism 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 c1 IS
302 SELECT
303 line_id,
304 person_id,
305 fund_code,
306 fund_description,
307 award_name,
308 ci_cal_type,
309 ci_sequence_number,
310 award_total ,
311 term_amount_text
312 FROM IGF_AW_AWD_LTR_TMP
313 WHERE ROWID = x_rowid
314 FOR UPDATE NOWAIT;
315
316 tlinfo c1%ROWTYPE;
317
318 BEGIN
319
320 OPEN c1;
321 FETCH c1 INTO tlinfo;
322 IF (c1%NOTFOUND) THEN
323 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
324 igs_ge_msg_stack.ADD;
325 CLOSE c1;
326 app_exception.raise_exception;
327 RETURN;
328 END IF;
329 CLOSE c1;
330
331 IF (
332 (tlinfo.line_id = x_line_id)
333 AND (tlinfo.person_id = x_person_id)
334 AND ((tlinfo.fund_code = X_fund_code) OR ((tlinfo.fund_code IS NULL) AND (X_fund_code IS NULL)))
335 AND ((tlinfo.fund_code = X_fund_description) OR ((tlinfo.fund_code IS NULL) AND (X_fund_description IS NULL)))
336 AND ((tlinfo.fund_code = X_award_name) OR ((tlinfo.award_name IS NULL) AND (X_award_name IS NULL)))
337 AND ((tlinfo.fund_code = X_ci_cal_type) OR ((tlinfo.ci_cal_type IS NULL) AND (X_ci_cal_type IS NULL)))
338 AND ((tlinfo.fund_code = X_ci_sequence_number) OR ((tlinfo.ci_sequence_number IS NULL) AND (X_ci_sequence_number IS NULL)))
339 AND ((tlinfo.fund_code = X_award_total) OR ((tlinfo.award_total IS NULL) AND (X_award_total IS NULL)))
340 AND ((tlinfo.fund_code = X_term_amount_text) OR ((tlinfo.term_amount_text IS NULL) AND (X_term_amount_text IS NULL)))
341 ) THEN
342 NULL;
343 ELSE
344 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
345 igs_ge_msg_stack.ADD;
346 app_exception.raise_exception;
347 END IF;
348
349 RETURN;
350
351 END lock_row;
352
353 FUNCTION get_pk_for_validation (
354 x_line_id IN NUMBER,
355 x_person_id IN NUMBER,
356 x_ci_cal_type IN VARCHAR2,
357 x_ci_sequence_number IN NUMBER
358 ) RETURN BOOLEAN AS
359 /*
360 || Created By : prchandr
361 || Created On : 01-JUN-2001
362 || Purpose : Validates the Primary Key of the table.
363 || Known limitations, enhancements or remarks :
364 || Change History :
365 || Who When What
366 || (reverse chronological order - newest change first)
367 */
368 CURSOR cur_rowid IS
369 SELECT rowid
370 FROM igf_aw_awd_ltr_tmp
371 WHERE person_id = x_person_id
372 AND line_id = x_line_id
373 AND ci_cal_type = x_ci_cal_type
374 AND ci_sequence_number = x_ci_sequence_number
375 FOR UPDATE NOWAIT;
376
377 lv_rowid cur_rowid%RowType;
378
379 BEGIN
380
381 OPEN cur_rowid;
382 FETCH cur_rowid INTO lv_rowid;
383 IF (cur_rowid%FOUND) THEN
384 CLOSE cur_rowid;
385 RETURN(TRUE);
386 ELSE
387 CLOSE cur_rowid;
388 RETURN(FALSE);
389 END IF;
390
391 END get_pk_for_validation;
392
393 PROCEDURE update_row (
397 x_fund_code IN VARCHAR2,
394 x_rowid IN VARCHAR2,
395 x_line_id IN NUMBER,
396 x_person_id IN NUMBER,
398 x_fund_description IN VARCHAR2 DEFAULT NULL,
399 x_award_name IN VARCHAR2,
400 x_ci_cal_type IN VARCHAR2 DEFAULT NULL,
401 x_ci_sequence_number IN NUMBER DEFAULT NULL,
402 x_award_total IN NUMBER DEFAULT NULL,
403 x_term_amount_text IN VARCHAR2 DEFAULT NULL,
404 x_mode IN VARCHAR2 DEFAULT 'R'
405 ) AS
406 /*
407 || Created By : svenkata
408 || Created On : 07-FEB-2002
409 || Purpose : Handles the UPDATE DML logic for the table.
410 || Known limitations, enhancements or remarks :
411 || Change History :
412 || Who When What
413 || (reverse chronological order - newest change first)
414 */
415 x_last_update_date DATE ;
416 x_last_updated_by NUMBER;
417 x_last_update_login NUMBER;
418
419 BEGIN
420
421 x_last_update_date := SYSDATE;
422 IF (X_MODE = 'I') THEN
423 x_last_updated_by := 1;
424 x_last_update_login := 0;
425 ELSIF (x_mode = 'R') THEN
426 x_last_updated_by := fnd_global.user_id;
427 IF x_last_updated_by IS NULL THEN
428 x_last_updated_by := -1;
429 END IF;
430 x_last_update_login := fnd_global.login_id;
431 IF (x_last_update_login IS NULL) THEN
432 x_last_update_login := -1;
433 END IF;
434 ELSE
435 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
436 igs_ge_msg_stack.ADD;
437 app_exception.raise_exception;
438 END IF;
439
440 before_dml(
441 p_action => 'UPDATE',
442 x_rowid => x_rowid,
443 x_line_id => x_line_id,
444 x_person_id => x_person_id,
445 x_fund_code => x_fund_code,
446 x_fund_description => x_fund_description,
447 x_award_name => x_award_name,
448 x_ci_cal_type => x_ci_cal_type,
449 x_ci_sequence_number => x_ci_sequence_number,
450 x_award_total => x_award_total,
451 x_creation_date => x_last_update_date,
452 x_created_by => x_last_updated_by,
453 x_last_update_date => x_last_update_date,
454 x_last_updated_by => x_last_updated_by,
455 x_last_update_login => x_last_update_login
456 );
457
458 UPDATE IGF_AW_AWD_LTR_TMP
459 SET
460 line_id = new_references.line_id,
461 person_id = new_references.person_id,
462 fund_code = new_references.fund_code,
463 fund_description = new_references.fund_description,
464 award_name = new_references.award_name,
465 ci_cal_type = new_references.ci_cal_type,
466 ci_sequence_number = new_references.ci_sequence_number,
467 award_total = new_references.award_total,
468 last_update_date = x_last_update_date,
469 last_updated_by = x_last_updated_by,
470 last_update_login = x_last_update_login
471 WHERE ROWID = x_rowid;
472
473 IF (SQL%NOTFOUND) THEN
474 RAISE NO_DATA_FOUND;
475 END IF;
476
477 END update_row;
478
479
480 PROCEDURE add_row (
481 x_rowid IN OUT NOCOPY VARCHAR2,
482 x_line_id IN NUMBER,
483 x_person_id IN NUMBER,
484 x_fund_code IN VARCHAR2 DEFAULT NULL,
485 x_fund_description IN VARCHAR2 DEFAULT NULL,
486 x_award_name IN VARCHAR2 DEFAULT NULL,
487 x_ci_cal_type IN VARCHAR2 DEFAULT NULL,
488 x_ci_sequence_number IN NUMBER DEFAULT NULL,
489 x_award_total IN NUMBER DEFAULT NULL,
490 x_term_amount_text IN VARCHAR2 DEFAULT NULL,
491 x_mode IN VARCHAR2 DEFAULT 'R'
492 ) AS
493 /*
494 || Created By : svenkata
495 || Created On : 07-FEB-2002
496 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
497 || Known limitations, enhancements or remarks :
498 || Change History :
499 || Who When What
500 || (reverse chronological order - newest change first)
501 */
502 CURSOR c1 IS
503 SELECT ROWID
504 FROM IGF_AW_AWD_LTR_TMP
505 WHERE line_id = x_line_id;
506
507 BEGIN
508
509 OPEN c1;
510 FETCH c1 INTO x_rowid;
511 IF (c1%NOTFOUND) THEN
512 CLOSE c1;
513
514 insert_row (
515 x_rowid,
516 x_line_id,
517 x_person_id,
518 x_fund_code,
519 x_fund_description,
520 x_award_name,
521 x_ci_cal_type,
522 x_ci_sequence_number,
523 x_award_total ,
524 x_term_amount_text ,
525 x_mode
526 );
527 RETURN;
528 END IF;
529 CLOSE c1;
530
531 update_row (
532 x_rowid,
533 x_line_id,
534 x_person_id,
535 x_fund_code,
536 x_fund_description,
537 x_award_name,
538 x_ci_cal_type,
539 x_ci_sequence_number,
540 x_award_total ,
541 x_term_amount_text ,
542 x_mode
543 );
544
545 END add_row;
546
547
548 PROCEDURE delete_row (
549 x_rowid IN VARCHAR2
550 ) AS
551 /*
552 || Created By : svenkata
553 || Created On : 07-FEB-2002
554 || Purpose : Handles the DELETE DML logic for the table.
555 || Known limitations, enhancements or remarks :
556 || Change History :
557 || Who When What
558 || (reverse chronological order - newest change first)
559 */
560 BEGIN
561
562 before_dml (
563 p_action => 'DELETE',
564 x_rowid => x_rowid
565 );
566
567 DELETE FROM IGF_AW_AWD_LTR_TMP
568 WHERE ROWID = x_rowid;
569
570 IF (SQL%NOTFOUND) THEN
571 RAISE NO_DATA_FOUND;
572 END IF;
573
574 END delete_row;
575
576
577 END igf_aw_awd_ltr_tmp_pkg;