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