[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_COA_HIST_PKG
Source
1 PACKAGE BODY igf_aw_coa_hist_pkg AS
2 /* $Header: IGFWI68B.pls 120.0 2005/06/01 13:54:12 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_aw_coa_hist%ROWTYPE;
6 new_references igf_aw_coa_hist%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_coah_id IN NUMBER,
12 x_base_id IN NUMBER,
13 x_tran_date IN DATE,
14 x_item_code IN VARCHAR2,
15 x_ld_cal_type IN VARCHAR2,
16 x_ld_sequence_number IN NUMBER,
17 x_operation_txt IN VARCHAR2,
18 x_old_value IN NUMBER,
19 x_new_value IN NUMBER,
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 : cdcruz
28 || Created On : 16-NOV-2004
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_coa_hist
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.coah_id := x_coah_id;
60 new_references.base_id := x_base_id;
61 new_references.tran_date := x_tran_date;
62 new_references.item_code := x_item_code;
63 new_references.ld_cal_type := x_ld_cal_type;
64 new_references.ld_sequence_number := x_ld_sequence_number;
65 new_references.operation_txt := x_operation_txt;
66 new_references.old_value := x_old_value;
67 new_references.new_value := x_new_value;
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_coah_id IN NUMBER
86 ) RETURN BOOLEAN AS
87 /*
88 || Created By : cdcruz
89 || Created On : 16-NOV-2004
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_aw_coa_hist
99 WHERE coah_id = x_coah_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_coah_id IN NUMBER,
123 x_base_id IN NUMBER,
124 x_tran_date IN DATE,
125 x_item_code IN VARCHAR2,
126 x_ld_cal_type IN VARCHAR2,
127 x_ld_sequence_number IN NUMBER,
128 x_operation_txt IN VARCHAR2,
129 x_old_value IN NUMBER,
130 x_new_value IN NUMBER,
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 : cdcruz
139 || Created On : 16-NOV-2004
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_coah_id,
153 x_base_id,
154 x_tran_date,
155 x_item_code,
156 x_ld_cal_type,
157 x_ld_sequence_number,
158 x_operation_txt,
159 x_old_value,
160 x_new_value,
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.coah_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.coah_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_coah_id IN OUT NOCOPY NUMBER,
196 x_base_id IN NUMBER,
197 x_tran_date IN DATE,
198 x_item_code IN VARCHAR2,
199 x_ld_cal_type IN VARCHAR2,
200 x_ld_sequence_number IN NUMBER,
201 x_operation_txt IN VARCHAR2,
202 x_old_value IN NUMBER,
203 x_new_value IN NUMBER,
204 x_mode IN VARCHAR2
205 ) AS
206 /*
207 || Created By : cdcruz
208 || Created On : 16-NOV-2004
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
216 x_last_update_date DATE;
217 x_last_updated_by NUMBER;
218 x_last_update_login NUMBER;
219 x_request_id NUMBER;
220 x_program_id NUMBER;
221 x_program_application_id NUMBER;
222 x_program_update_date DATE;
223
224 BEGIN
225
226 x_last_update_date := SYSDATE;
227 IF (x_mode = 'I') THEN
228 x_last_updated_by := 1;
229 x_last_update_login := 0;
230 ELSIF (x_mode = 'R') THEN
231 x_last_updated_by := fnd_global.user_id;
232 IF (x_last_updated_by IS NULL) THEN
233 x_last_updated_by := -1;
234 END IF;
235 x_last_update_login := fnd_global.login_id;
236 IF (x_last_update_login IS NULL) THEN
237 x_last_update_login := -1;
238 END IF;
239 x_request_id := fnd_global.conc_request_id;
240 x_program_id := fnd_global.conc_program_id;
241 x_program_application_id := fnd_global.prog_appl_id;
242
243 IF (x_request_id = -1) THEN
244 x_request_id := NULL;
245 x_program_id := NULL;
246 x_program_application_id := NULL;
247 x_program_update_date := NULL;
248 ELSE
249 x_program_update_date := SYSDATE;
250 END IF;
251 ELSE
252 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
253 fnd_message.set_token ('ROUTINE', 'IGF_AW_COA_HIST_PKG.INSERT_ROW');
254 igs_ge_msg_stack.add;
255 app_exception.raise_exception;
256 END IF;
257
258 x_coah_id := NULL;
259
260 before_dml(
261 p_action => 'INSERT',
262 x_rowid => x_rowid,
263 x_coah_id => x_coah_id,
264 x_base_id => x_base_id,
265 x_tran_date => x_tran_date,
266 x_item_code => x_item_code,
267 x_ld_cal_type => x_ld_cal_type,
268 x_ld_sequence_number => x_ld_sequence_number,
269 x_operation_txt => x_operation_txt,
270 x_old_value => x_old_value,
271 x_new_value => x_new_value,
272 x_creation_date => x_last_update_date,
273 x_created_by => x_last_updated_by,
274 x_last_update_date => x_last_update_date,
275 x_last_updated_by => x_last_updated_by,
276 x_last_update_login => x_last_update_login
277 );
278
279 INSERT INTO igf_aw_coa_hist (
280 coah_id,
281 base_id,
282 tran_date,
283 item_code,
284 ld_cal_type,
285 ld_sequence_number,
286 operation_txt,
287 old_value,
288 new_value,
289 creation_date,
290 created_by,
291 last_update_date,
292 last_updated_by,
293 last_update_login,
294 request_id,
295 program_id,
296 program_application_id,
297 program_update_date
298 ) VALUES (
299 igf_aw_coa_hist_s.NEXTVAL,
300 new_references.base_id,
301 new_references.tran_date,
302 new_references.item_code,
303 new_references.ld_cal_type,
304 new_references.ld_sequence_number,
305 new_references.operation_txt,
306 new_references.old_value,
307 new_references.new_value,
308 x_last_update_date,
309 x_last_updated_by,
310 x_last_update_date,
311 x_last_updated_by,
312 x_last_update_login ,
313 x_request_id,
314 x_program_id,
315 x_program_application_id,
316 x_program_update_date
317 ) RETURNING ROWID, coah_id INTO x_rowid, x_coah_id;
318
319 END insert_row;
320
321
322 PROCEDURE lock_row (
323 x_rowid IN VARCHAR2,
324 x_coah_id IN NUMBER,
325 x_base_id IN NUMBER,
326 x_tran_date IN DATE,
327 x_item_code IN VARCHAR2,
328 x_ld_cal_type IN VARCHAR2,
329 x_ld_sequence_number IN NUMBER,
330 x_operation_txt IN VARCHAR2,
331 x_old_value IN NUMBER,
332 x_new_value IN NUMBER
333 ) AS
334 /*
335 || Created By : cdcruz
336 || Created On : 16-NOV-2004
337 || Purpose : Handles the LOCK mechanism for the table.
338 || Known limitations, enhancements or remarks :
339 || Change History :
340 || Who When What
341 || (reverse chronological order - newest change first)
342 */
343 CURSOR c1 IS
344 SELECT
345 base_id,
346 tran_date,
347 item_code,
348 ld_cal_type,
349 ld_sequence_number,
350 operation_txt,
351 old_value,
352 new_value
353 FROM igf_aw_coa_hist
354 WHERE rowid = x_rowid
355 FOR UPDATE NOWAIT;
356
357 tlinfo c1%ROWTYPE;
358
359 BEGIN
360
361 OPEN c1;
362 FETCH c1 INTO tlinfo;
363 IF (c1%notfound) THEN
364 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
365 igs_ge_msg_stack.add;
366 CLOSE c1;
367 app_exception.raise_exception;
368 RETURN;
369 END IF;
370 CLOSE c1;
371
372 IF (
373 (tlinfo.base_id = x_base_id)
374 AND (tlinfo.tran_date = x_tran_date)
375 AND (tlinfo.item_code = x_item_code)
376 AND (tlinfo.ld_cal_type = x_ld_cal_type)
377 AND (tlinfo.ld_sequence_number = x_ld_sequence_number)
378 AND (tlinfo.operation_txt = x_operation_txt)
379 AND ((tlinfo.old_value = x_old_value) OR ((tlinfo.old_value IS NULL) AND (X_old_value IS NULL)))
380 AND ((tlinfo.new_value = x_new_value) OR ((tlinfo.new_value IS NULL) AND (X_new_value IS NULL)))
381 ) THEN
382 NULL;
383 ELSE
384 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
385 igs_ge_msg_stack.add;
386 app_exception.raise_exception;
387 END IF;
388
389 RETURN;
390
391 END lock_row;
392
393
394 PROCEDURE update_row (
395 x_rowid IN VARCHAR2,
396 x_coah_id IN NUMBER,
397 x_base_id IN NUMBER,
398 x_tran_date IN DATE,
399 x_item_code IN VARCHAR2,
400 x_ld_cal_type IN VARCHAR2,
401 x_ld_sequence_number IN NUMBER,
402 x_operation_txt IN VARCHAR2,
403 x_old_value IN NUMBER,
404 x_new_value IN NUMBER,
405 x_mode IN VARCHAR2
406 ) AS
407 /*
408 || Created By : cdcruz
409 || Created On : 16-NOV-2004
410 || Purpose : Handles the UPDATE DML logic for the table.
411 || Known limitations, enhancements or remarks :
412 || Change History :
413 || Who When What
414 || (reverse chronological order - newest change first)
415 */
416 x_last_update_date DATE ;
417 x_last_updated_by NUMBER;
418 x_last_update_login NUMBER;
419 x_request_id NUMBER;
420 x_program_id NUMBER;
421 x_program_application_id NUMBER;
422 x_program_update_date DATE;
423
424 BEGIN
425
426 x_last_update_date := SYSDATE;
427 IF (X_MODE = 'I') THEN
428 x_last_updated_by := 1;
429 x_last_update_login := 0;
430 ELSIF (x_mode = 'R') THEN
431 x_last_updated_by := fnd_global.user_id;
432 IF x_last_updated_by IS NULL THEN
433 x_last_updated_by := -1;
434 END IF;
435 x_last_update_login := fnd_global.login_id;
436 IF (x_last_update_login IS NULL) THEN
437 x_last_update_login := -1;
438 END IF;
439 ELSE
440 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
441 fnd_message.set_token ('ROUTINE', 'IGF_AW_COA_HIST_PKG.UPDATE_ROW');
442 igs_ge_msg_stack.add;
443 app_exception.raise_exception;
444 END IF;
445
446 before_dml(
447 p_action => 'UPDATE',
448 x_rowid => x_rowid,
449 x_coah_id => x_coah_id,
450 x_base_id => x_base_id,
451 x_tran_date => x_tran_date,
452 x_item_code => x_item_code,
453 x_ld_cal_type => x_ld_cal_type,
454 x_ld_sequence_number => x_ld_sequence_number,
455 x_operation_txt => x_operation_txt,
456 x_old_value => x_old_value,
457 x_new_value => x_new_value,
458 x_creation_date => x_last_update_date,
459 x_created_by => x_last_updated_by,
460 x_last_update_date => x_last_update_date,
461 x_last_updated_by => x_last_updated_by,
462 x_last_update_login => x_last_update_login
463 );
464
465 IF (x_mode = 'R') THEN
466 x_request_id := fnd_global.conc_request_id;
467 x_program_id := fnd_global.conc_program_id;
468 x_program_application_id := fnd_global.prog_appl_id;
469 IF (x_request_id = -1) THEN
470 x_request_id := old_references.request_id;
471 x_program_id := old_references.program_id;
472 x_program_application_id := old_references.program_application_id;
473 x_program_update_date := old_references.program_update_date;
474 ELSE
475 x_program_update_date := SYSDATE;
476 END IF;
477 END IF;
478
479 UPDATE igf_aw_coa_hist
480 SET
481 base_id = new_references.base_id,
482 tran_date = new_references.tran_date,
483 item_code = new_references.item_code,
484 ld_cal_type = new_references.ld_cal_type,
485 ld_sequence_number = new_references.ld_sequence_number,
486 operation_txt = new_references.operation_txt,
487 old_value = new_references.old_value,
488 new_value = new_references.new_value,
489 last_update_date = x_last_update_date,
490 last_updated_by = x_last_updated_by,
491 last_update_login = x_last_update_login ,
492 request_id = x_request_id,
493 program_id = x_program_id,
494 program_application_id = x_program_application_id,
495 program_update_date = x_program_update_date
496 WHERE rowid = x_rowid;
497
498 IF (SQL%NOTFOUND) THEN
499 RAISE NO_DATA_FOUND;
500 END IF;
501
502 END update_row;
503
504
505 PROCEDURE add_row (
506 x_rowid IN OUT NOCOPY VARCHAR2,
507 x_coah_id IN OUT NOCOPY NUMBER,
508 x_base_id IN NUMBER,
509 x_tran_date IN DATE,
510 x_item_code IN VARCHAR2,
511 x_ld_cal_type IN VARCHAR2,
512 x_ld_sequence_number IN NUMBER,
513 x_operation_txt IN VARCHAR2,
514 x_old_value IN NUMBER,
515 x_new_value IN NUMBER,
516 x_mode IN VARCHAR2
517 ) AS
518 /*
519 || Created By : cdcruz
520 || Created On : 16-NOV-2004
521 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
522 || Known limitations, enhancements or remarks :
523 || Change History :
524 || Who When What
525 || (reverse chronological order - newest change first)
526 */
527 CURSOR c1 IS
528 SELECT rowid
529 FROM igf_aw_coa_hist
530 WHERE coah_id = x_coah_id;
531
532 BEGIN
533
534 OPEN c1;
535 FETCH c1 INTO x_rowid;
536 IF (c1%NOTFOUND) THEN
537 CLOSE c1;
538
539 insert_row (
540 x_rowid,
541 x_coah_id,
542 x_base_id,
543 x_tran_date,
544 x_item_code,
545 x_ld_cal_type,
546 x_ld_sequence_number,
547 x_operation_txt,
548 x_old_value,
549 x_new_value,
550 x_mode
551 );
552 RETURN;
553 END IF;
554 CLOSE c1;
555
556 update_row (
557 x_rowid,
558 x_coah_id,
559 x_base_id,
560 x_tran_date,
561 x_item_code,
562 x_ld_cal_type,
563 x_ld_sequence_number,
564 x_operation_txt,
565 x_old_value,
566 x_new_value,
567 x_mode
568 );
569
570 END add_row;
571
572
573 PROCEDURE delete_row (
574 x_rowid IN VARCHAR2
575 ) AS
576 /*
577 || Created By : cdcruz
578 || Created On : 16-NOV-2004
579 || Purpose : Handles the DELETE DML logic for the table.
580 || Known limitations, enhancements or remarks :
581 || Change History :
582 || Who When What
583 || (reverse chronological order - newest change first)
584 */
585 BEGIN
586
587 before_dml (
588 p_action => 'DELETE',
589 x_rowid => x_rowid
590 );
591
592 DELETE FROM igf_aw_coa_hist
593 WHERE rowid = x_rowid;
594
595 IF (SQL%NOTFOUND) THEN
596 RAISE NO_DATA_FOUND;
597 END IF;
598
599 END delete_row;
600
601
602 END igf_aw_coa_hist_pkg;