1 PACKAGE BODY igf_ap_pr_prg_type_pkg AS
2 /* $Header: IGFAI75B.pls 115.2 2002/11/28 14:06:52 nsidana noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_ap_pr_prg_type%ROWTYPE;
6 new_references igf_ap_pr_prg_type%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_ppt_id IN NUMBER,
12 x_igs_pr_cs_schdl_id IN NUMBER,
13 x_cal_type IN VARCHAR2,
14 x_sequence_number IN NUMBER,
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 : mesriniv
23 || Created On : 09-OCT-2002
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 igf_ap_pr_prg_type
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.ppt_id := x_ppt_id;
55 new_references.igs_pr_cs_schdl_id := x_igs_pr_cs_schdl_id;
56 new_references.cal_type := x_cal_type;
57 new_references.sequence_number := x_sequence_number;
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 : mesriniv
77 || Created On : 09-OCT-2002
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.cal_type = new_references.cal_type) AND
87 (old_references.sequence_number = new_references.sequence_number)) OR
88 ((new_references.cal_type IS NULL) OR
89 (new_references.sequence_number IS NULL))) THEN
90 NULL;
91 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
92 new_references.cal_type,
93 new_references.sequence_number
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 PROCEDURE check_child_existance AS
104 /*
105 || Created By : mesriniv
106 || Created On : 09-OCT-2002
107 || Purpose : Checks for the existance of Child records.
108 || Known limitations, enhancements or remarks :
109 || Change History :
110 || Who When What
111 || (reverse chronological order - newest change first)
112 */
113 BEGIN
114
115 igf_ap_class_std_map_pkg.get_fk_igf_ap_pr_prg_type (
116 old_references.ppt_id
117 );
118
119 END check_child_existance;
120
121
122 FUNCTION get_pk_for_validation (
123 x_ppt_id IN NUMBER
124 ) RETURN BOOLEAN AS
125 /*
126 || Created By : mesriniv
127 || Created On : 09-OCT-2002
128 || Purpose : Validates the Primary Key of the table.
129 || Known limitations, enhancements or remarks :
130 || Change History :
131 || Who When What
132 || (reverse chronological order - newest change first)
133 */
134 CURSOR cur_rowid IS
135 SELECT rowid
136 FROM igf_ap_pr_prg_type
137 WHERE ppt_id = x_ppt_id
138 FOR UPDATE NOWAIT;
139
140 lv_rowid cur_rowid%RowType;
141
142 BEGIN
143
144 OPEN cur_rowid;
145 FETCH cur_rowid INTO lv_rowid;
146 IF (cur_rowid%FOUND) THEN
147 CLOSE cur_rowid;
148 RETURN(TRUE);
149 ELSE
150 CLOSE cur_rowid;
151 RETURN(FALSE);
152 END IF;
153
154 END get_pk_for_validation;
155
156 FUNCTION get_uk_for_validation (
157 x_igs_pr_cs_schdl_id IN NUMBER,
158 x_cal_type IN VARCHAR2,
159 x_sequence_number IN NUMBER
160 ) RETURN BOOLEAN AS
161 /*
162 || Created By : mesriniv
163 || Created On : 10-oct-2002
164 || Purpose : Validates the Unique Keys of the table.
165 || Known limitations, enhancements or remarks :
166 || Change History :
167 || Who When What
168 || (reverse chronological order - newest change first)
169 */
170 CURSOR cur_rowid IS
171 SELECT rowid
172 FROM igf_ap_pr_prg_type
173 WHERE igs_pr_cs_schdl_id = x_igs_pr_cs_schdl_id
174 AND cal_type =x_caL_type
175 AND sequence_number=x_sequence_number
176 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
177
178 lv_rowid cur_rowid%RowType;
179
180 BEGIN
181
182 OPEN cur_rowid;
183 FETCH cur_rowid INTO lv_rowid;
184 IF (cur_rowid%FOUND) THEN
185 CLOSE cur_rowid;
186 RETURN (true);
187 ELSE
188 CLOSE cur_rowid;
189 RETURN(FALSE);
190 END IF;
191
192 END get_uk_for_validation ;
193
194 PROCEDURE check_uniqueness AS
195 /*
196 || Created By : mesriniv
197 || Created On : 09-oct-2002
198 || Purpose : Handles the Unique Constraint logic defined for the columns.
199 || Known limitations, enhancements or remarks :
200 || Change History :
201 || Who When What
202 || (reverse chronological order - newest change first)
203 */
204 BEGIN
205
206 IF ( get_uk_for_validation (
207 new_references.igs_pr_cs_schdl_id,
208 new_references.cal_type,
209 new_references.sequence_number
210 )
211 ) THEN
212 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
213 igs_ge_msg_stack.add;
214 app_exception.raise_exception;
215 END IF;
216
217 END check_uniqueness;
218
219
220
221 PROCEDURE get_fk_igs_ca_inst (
222 x_cal_type IN VARCHAR2,
223 x_sequence_number IN NUMBER
224 ) AS
225 /*
226 || Created By : mesriniv
227 || Created On : 09-OCT-2002
228 || Purpose : Validates the Foreign Keys for the table.
229 || Known limitations, enhancements or remarks :
230 || Change History :
231 || Who When What
232 || (reverse chronological order - newest change first)
233 */
234 CURSOR cur_rowid IS
235 SELECT rowid
236 FROM igf_ap_pr_prg_type
237 WHERE ((cal_type = x_cal_type) AND
238 (sequence_number = x_sequence_number));
239
240 lv_rowid cur_rowid%RowType;
241
242 BEGIN
243
244 OPEN cur_rowid;
245 FETCH cur_rowid INTO lv_rowid;
246 IF (cur_rowid%FOUND) THEN
247 CLOSE cur_rowid;
248 fnd_message.set_name ('IGF', 'IGF_AP_PPT_CI_FK');
249 igs_ge_msg_stack.add;
250 app_exception.raise_exception;
251 RETURN;
252 END IF;
253 CLOSE cur_rowid;
254
255 END get_fk_igs_ca_inst;
256
257
258 PROCEDURE before_dml (
259 p_action IN VARCHAR2,
260 x_rowid IN VARCHAR2,
261 x_ppt_id IN NUMBER,
262 x_igs_pr_cs_schdl_id IN NUMBER,
263 x_cal_type IN VARCHAR2,
264 x_sequence_number IN NUMBER,
265 x_creation_date IN DATE,
266 x_created_by IN NUMBER,
267 x_last_update_date IN DATE,
268 x_last_updated_by IN NUMBER,
269 x_last_update_login IN NUMBER
270 ) AS
271 /*
272 || Created By : mesriniv
273 || Created On : 09-OCT-2002
274 || Purpose : Initialises the columns, Checks Constraints, Calls the
275 || Trigger Handlers for the table, before any DML operation.
276 || Known limitations, enhancements or remarks :
277 || Change History :
278 || Who When What
279 || (reverse chronological order - newest change first)
280 */
281 BEGIN
282
283 set_column_values (
284 p_action,
285 x_rowid,
286 x_ppt_id,
287 x_igs_pr_cs_schdl_id,
288 x_cal_type,
289 x_sequence_number,
290 x_creation_date,
291 x_created_by,
292 x_last_update_date,
293 x_last_updated_by,
294 x_last_update_login
295 );
296
297 IF (p_action = 'INSERT') THEN
298 -- Call all the procedures related to Before Insert.
299 IF ( get_pk_for_validation(
300 new_references.ppt_id
301 )
302 ) THEN
303 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
304 igs_ge_msg_stack.add;
305 app_exception.raise_exception;
306 END IF;
307 check_uniqueness;
308 check_parent_existance;
309 ELSIF (p_action = 'UPDATE') THEN
310 -- Call all the procedures related to Before Update.
311 check_uniqueness;
312 check_parent_existance;
313 ELSIF (p_action = 'DELETE') THEN
314 -- Call all the procedures related to Before Delete.
315 check_child_existance;
316 ELSIF (p_action = 'VALIDATE_INSERT') THEN
317 -- Call all the procedures related to Before Insert.
318 IF ( get_pk_for_validation (
319 new_references.ppt_id
320 )
321 ) THEN
322 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
323 igs_ge_msg_stack.add;
324 app_exception.raise_exception;
325 END IF;
326 check_uniqueness;
327 ELSIF (p_action = 'VALIDATE_DELETE') THEN
328 check_child_existance;
329 END IF;
330
331 END before_dml;
332
333
334 PROCEDURE insert_row (
335 x_rowid IN OUT NOCOPY VARCHAR2,
336 x_ppt_id IN OUT NOCOPY NUMBER,
337 x_igs_pr_cs_schdl_id IN NUMBER,
338 x_cal_type IN VARCHAR2,
339 x_sequence_number IN NUMBER,
340 x_mode IN VARCHAR2
341 ) AS
342 /*
343 || Created By : mesriniv
344 || Created On : 09-OCT-2002
345 || Purpose : Handles the INSERT DML logic for the table.
346 || Known limitations, enhancements or remarks :
347 || Change History :
348 || Who When What
349 || (reverse chronological order - newest change first)
350 */
351
352 x_last_update_date DATE;
353 x_last_updated_by NUMBER;
354 x_last_update_login NUMBER;
355
356 BEGIN
357
358 x_last_update_date := SYSDATE;
359 IF (x_mode = 'I') THEN
360 x_last_updated_by := 1;
361 x_last_update_login := 0;
362 ELSIF (x_mode = 'R') THEN
363 x_last_updated_by := fnd_global.user_id;
364 IF (x_last_updated_by IS NULL) THEN
365 x_last_updated_by := -1;
366 END IF;
367 x_last_update_login := fnd_global.login_id;
368 IF (x_last_update_login IS NULL) THEN
369 x_last_update_login := -1;
370 END IF;
371 ELSE
372 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
373 igs_ge_msg_stack.add;
374 app_exception.raise_exception;
375 END IF;
376
377 before_dml(
378 p_action => 'INSERT',
379 x_rowid => x_rowid,
380 x_ppt_id => x_ppt_id,
381 x_igs_pr_cs_schdl_id => x_igs_pr_cs_schdl_id,
382 x_cal_type => x_cal_type,
383 x_sequence_number => x_sequence_number,
384 x_creation_date => x_last_update_date,
385 x_created_by => x_last_updated_by,
386 x_last_update_date => x_last_update_date,
387 x_last_updated_by => x_last_updated_by,
388 x_last_update_login => x_last_update_login
389 );
390
391 INSERT INTO igf_ap_pr_prg_type (
392 ppt_id,
393 igs_pr_cs_schdl_id,
394 cal_type,
395 sequence_number,
396 creation_date,
397 created_by,
398 last_update_date,
399 last_updated_by,
400 last_update_login
401 ) VALUES (
402 igf_ap_pr_prg_type_s.NEXTVAL,
403 new_references.igs_pr_cs_schdl_id,
404 new_references.cal_type,
405 new_references.sequence_number,
406 x_last_update_date,
407 x_last_updated_by,
408 x_last_update_date,
409 x_last_updated_by,
410 x_last_update_login
411 ) RETURNING ROWID, ppt_id INTO x_rowid, x_ppt_id;
412
413 END insert_row;
414
415
416 PROCEDURE lock_row (
417 x_rowid IN VARCHAR2,
418 x_ppt_id IN NUMBER,
419 x_igs_pr_cs_schdl_id IN NUMBER,
420 x_cal_type IN VARCHAR2,
421 x_sequence_number IN NUMBER
422 ) AS
423 /*
424 || Created By : mesriniv
425 || Created On : 09-OCT-2002
426 || Purpose : Handles the LOCK mechanism for the table.
427 || Known limitations, enhancements or remarks :
428 || Change History :
429 || Who When What
430 || (reverse chronological order - newest change first)
431 */
432 CURSOR c1 IS
433 SELECT
434 igs_pr_cs_schdl_id,
435 cal_type,
436 sequence_number
437 FROM igf_ap_pr_prg_type
438 WHERE rowid = x_rowid
439 FOR UPDATE NOWAIT;
440
441 tlinfo c1%ROWTYPE;
442
443 BEGIN
444
445 OPEN c1;
446 FETCH c1 INTO tlinfo;
447 IF (c1%notfound) THEN
448 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
449 igs_ge_msg_stack.add;
450 CLOSE c1;
451 app_exception.raise_exception;
452 RETURN;
453 END IF;
454 CLOSE c1;
455
456 IF (
457 (tlinfo.igs_pr_cs_schdl_id = x_igs_pr_cs_schdl_id)
458 AND (tlinfo.cal_type = x_cal_type)
459 AND (tlinfo.sequence_number = x_sequence_number)
460 ) THEN
461 NULL;
462 ELSE
463 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
464 igs_ge_msg_stack.add;
465 app_exception.raise_exception;
466 END IF;
467
468 RETURN;
469
470 END lock_row;
471
472
473 PROCEDURE update_row (
474 x_rowid IN VARCHAR2,
475 x_ppt_id IN NUMBER,
476 x_igs_pr_cs_schdl_id IN NUMBER,
477 x_cal_type IN VARCHAR2,
478 x_sequence_number IN NUMBER,
479 x_mode IN VARCHAR2
480 ) AS
481 /*
482 || Created By : mesriniv
483 || Created On : 09-OCT-2002
484 || Purpose : Handles the UPDATE DML logic for the table.
485 || Known limitations, enhancements or remarks :
486 || Change History :
487 || Who When What
488 || (reverse chronological order - newest change first)
489 */
490 x_last_update_date DATE ;
491 x_last_updated_by NUMBER;
492 x_last_update_login NUMBER;
493
494 BEGIN
495
496 x_last_update_date := SYSDATE;
497 IF (X_MODE = 'I') THEN
498 x_last_updated_by := 1;
499 x_last_update_login := 0;
500 ELSIF (x_mode = 'R') THEN
501 x_last_updated_by := fnd_global.user_id;
502 IF x_last_updated_by IS NULL THEN
503 x_last_updated_by := -1;
504 END IF;
505 x_last_update_login := fnd_global.login_id;
506 IF (x_last_update_login IS NULL) THEN
507 x_last_update_login := -1;
508 END IF;
509 ELSE
510 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
511 igs_ge_msg_stack.add;
512 app_exception.raise_exception;
513 END IF;
514
515 before_dml(
516 p_action => 'UPDATE',
517 x_rowid => x_rowid,
518 x_ppt_id => x_ppt_id,
519 x_igs_pr_cs_schdl_id => x_igs_pr_cs_schdl_id,
520 x_cal_type => x_cal_type,
521 x_sequence_number => x_sequence_number,
522 x_creation_date => x_last_update_date,
523 x_created_by => x_last_updated_by,
524 x_last_update_date => x_last_update_date,
525 x_last_updated_by => x_last_updated_by,
526 x_last_update_login => x_last_update_login
527 );
528
529 UPDATE igf_ap_pr_prg_type
530 SET
531 igs_pr_cs_schdl_id = new_references.igs_pr_cs_schdl_id,
532 cal_type = new_references.cal_type,
533 sequence_number = new_references.sequence_number,
534 last_update_date = x_last_update_date,
535 last_updated_by = x_last_updated_by,
536 last_update_login = x_last_update_login
537 WHERE rowid = x_rowid;
538
539 IF (SQL%NOTFOUND) THEN
540 RAISE NO_DATA_FOUND;
541 END IF;
542
543 END update_row;
544
545
546 PROCEDURE add_row (
547 x_rowid IN OUT NOCOPY VARCHAR2,
548 x_ppt_id IN OUT NOCOPY NUMBER,
549 x_igs_pr_cs_schdl_id IN NUMBER,
550 x_cal_type IN VARCHAR2,
551 x_sequence_number IN NUMBER,
552 x_mode IN VARCHAR2
553 ) AS
554 /*
555 || Created By : mesriniv
556 || Created On : 09-OCT-2002
557 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
558 || Known limitations, enhancements or remarks :
559 || Change History :
560 || Who When What
561 || (reverse chronological order - newest change first)
562 */
563 CURSOR c1 IS
564 SELECT rowid
565 FROM igf_ap_pr_prg_type
566 WHERE ppt_id = x_ppt_id;
567
568 BEGIN
569
570 OPEN c1;
571 FETCH c1 INTO x_rowid;
572 IF (c1%NOTFOUND) THEN
573 CLOSE c1;
574
575 insert_row (
576 x_rowid,
577 x_ppt_id,
578 x_igs_pr_cs_schdl_id,
579 x_cal_type,
580 x_sequence_number,
581 x_mode
582 );
583 RETURN;
584 END IF;
585 CLOSE c1;
586
587 update_row (
588 x_rowid,
589 x_ppt_id,
590 x_igs_pr_cs_schdl_id,
591 x_cal_type,
592 x_sequence_number,
593 x_mode
594 );
595
596 END add_row;
597
598
599 PROCEDURE delete_row (
600 x_rowid IN VARCHAR2
601 ) AS
602 /*
603 || Created By : mesriniv
604 || Created On : 09-OCT-2002
605 || Purpose : Handles the DELETE DML logic for the table.
606 || Known limitations, enhancements or remarks :
607 || Change History :
608 || Who When What
609 || (reverse chronological order - newest change first)
610 */
611 BEGIN
612
613 before_dml (
614 p_action => 'DELETE',
615 x_rowid => x_rowid
616 );
617
618 DELETE FROM igf_ap_pr_prg_type
619 WHERE rowid = x_rowid;
620
621 IF (SQL%NOTFOUND) THEN
622 RAISE NO_DATA_FOUND;
623 END IF;
624
625 END delete_row;
626
627
628 END igf_ap_pr_prg_type_pkg;
[Home] [Help]