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