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