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