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