1 PACKAGE BODY igs_ps_rsv_uop_prf_pkg AS
2 /* $Header: IGSPI1TB.pls 120.1 2005/08/18 07:13:28 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ps_rsv_uop_prf%ROWTYPE;
6 new_references igs_ps_rsv_uop_prf%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_rsv_uop_prf_id IN NUMBER DEFAULT NULL,
12 x_rsv_uop_pri_id IN NUMBER DEFAULT NULL,
13 x_preference_order IN NUMBER DEFAULT NULL,
14 x_preference_code IN VARCHAR2 DEFAULT NULL,
15 x_preference_version IN NUMBER DEFAULT NULL,
16 x_percentage_reserved IN NUMBER DEFAULT NULL,
17 x_group_id IN NUMBER DEFAULT NULL,
18 x_creation_date IN DATE DEFAULT NULL,
19 x_created_by IN NUMBER DEFAULT NULL,
20 x_last_update_date IN DATE DEFAULT NULL,
21 x_last_updated_by IN NUMBER DEFAULT NULL,
22 x_last_update_login IN NUMBER DEFAULT NULL
23 ) AS
24 /*
25 || Created By : apelleti
26 || Created On : 04-MAY-2001
27 || Purpose : Initialises the Old and New references for the columns of the table.
28 || Known limitations, enhancements or remarks :
29 || Change History :
30 || Who When What
31 || (reverse chronological order - newest change first)
32 */
33
34 CURSOR cur_old_ref_values IS
35 SELECT *
36 FROM IGS_PS_RSV_UOP_PRF
37 WHERE rowid = x_rowid;
38
39 BEGIN
40
41 l_rowid := x_rowid;
42
43 -- Code for setting the Old and New Reference Values.
44 -- Populate Old Values.
45 OPEN cur_old_ref_values;
46 FETCH cur_old_ref_values INTO old_references;
47 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
48 CLOSE cur_old_ref_values;
49 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
50 igs_ge_msg_stack.add;
51 app_exception.raise_exception;
52 RETURN;
53 END IF;
54 CLOSE cur_old_ref_values;
55
56 -- Populate New Values.
57 new_references.rsv_uop_prf_id := x_rsv_uop_prf_id;
58 new_references.rsv_uop_pri_id := x_rsv_uop_pri_id;
59 new_references.preference_order := x_preference_order;
60 new_references.preference_code := x_preference_code;
61 new_references.preference_version := x_preference_version;
62 new_references.percentage_reserved := x_percentage_reserved;
63 new_references.group_id := x_group_id;
64
65 IF (p_action = 'UPDATE') THEN
66 new_references.creation_date := old_references.creation_date;
67 new_references.created_by := old_references.created_by;
68 ELSE
69 new_references.creation_date := x_creation_date;
70 new_references.created_by := x_created_by;
71 END IF;
72
73 new_references.last_update_date := x_last_update_date;
74 new_references.last_updated_by := x_last_updated_by;
75 new_references.last_update_login := x_last_update_login;
76
77 END set_column_values;
78
79
80 PROCEDURE check_uniqueness AS
81 /*
82 || Created By : apelleti
83 || Created On : 04-MAY-2001
84 || Purpose : Handles the Unique Constraint logic defined for the columns.
85 || Known limitations, enhancements or remarks :
86 || Change History :
87 || Who When What
88 || (reverse chronological order - newest change first)
89 */
90 BEGIN
91
92 IF ( get_uk_for_validation (
93 new_references.rsv_uop_pri_id,
94 new_references.preference_code
95 )
96 ) THEN
97 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
98 igs_ge_msg_stack.add;
99 app_exception.raise_exception;
100 END IF;
101
102 END check_uniqueness;
103
104 PROCEDURE check_parent_existance AS
105 CURSOR c_check_hz_exists IS
106 SELECT 'x' FROM hz_parties hp,igs_pe_hz_parties pe
107 WHERE hp.party_id = pe.party_id
108 AND pe.oss_org_unit_cd = new_references.preference_code;
109 cur_rec_hz_exists c_check_hz_exists%ROWTYPE;
110 /*
111 || Created By : apelleti
112 || Created On : 02-MAY-2001
113 || Purpose : Checks for the existance of Parent records.
114 || Known limitations, enhancements or remarks :
115 || Change History :
116 || Who When What
117 || sommukhe 12-AUG-2005 Bug#4377818,changed the cursor c_check_hz_exists, included table igs_pe_hz_parties in
118 || FROM clause and modified the WHERE clause by joining HZ_PARTIES and IGS_PE_HZ_PARTIES
119 || using party_id and org unit being compared with oss_org_unit_cd of IGS_PE_HZ_PARTIES.
120 || (reverse chronological order - newest change first)
121 */
122 CURSOR c_priority is
123 Select priority_value
124 from IGS_PS_RSV_UOP_PRI PRI
125 Where PRI.RSV_UOP_PRI_ID = new_references.rsv_uop_pri_id;
126 priority_value1 VARCHAR2(30);
127 BEGIN
128
129 IF (((old_references.rsv_uop_pri_id = new_references.rsv_uop_pri_id)) OR
130 ((new_references.rsv_uop_pri_id IS NULL))) THEN
131 NULL;
132 ELSIF NOT igs_ps_rsv_uop_pri_pkg.get_pk_for_validation (
133 new_references.rsv_uop_pri_id
134 ) THEN
135 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
136 igs_ge_msg_stack.add;
137 app_exception.raise_exception;
138 ELSE
139 OPEN c_priority;
140 FETCH c_priority INTO priority_value1;
141 IF (c_priority%found) THEN
142 IF (priority_value1 = 'PROGRAM') THEN
143 IF NOT igs_ps_ver_pkg.get_pk_for_validation(
144 new_references.preference_code,
145 new_references.preference_version ) THEN
146 CLOSE c_priority;
147 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
148 igs_ge_msg_stack.add;
149 app_exception.raise_exception;
150 ELSE
151 CLOSE c_priority;
152 END IF;
153
154 ELSIF (priority_value1 = 'PROGRAM_STAGE') THEN
155 IF NOT igs_ps_stage_type_pkg.get_pk_for_validation(
156 new_references.preference_code ) THEN
157 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
158 CLOSE c_priority;
159 igs_ge_msg_stack.add;
160 app_exception.raise_exception;
161 ELSE
162 CLOSE c_priority;
163 END IF;
164 ELSIF (priority_value1 = 'UNIT_SET') THEN
165 IF NOT igs_en_unit_set_pkg.get_pk_for_validation(
166 new_references.preference_code,
167 new_references.preference_version ) THEN
168 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
169 CLOSE c_priority;
170 igs_ge_msg_stack.add;
171 app_exception.raise_exception;
172 ELSE
173 CLOSE c_priority;
174 END IF;
175 ELSIF (priority_value1 = 'PERSON_GRP') THEN
176 IF NOT igs_pe_persid_group_pkg.get_pk_for_validation(
177 new_references.group_id ) THEN
178 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
179 CLOSE c_priority;
180 igs_ge_msg_stack.add;
181 app_exception.raise_exception;
182 ELSE
183 CLOSE c_priority;
184 END IF;
185 ELSIF (priority_value1 = 'ORG_UNIT') THEN
186 OPEN c_check_hz_exists;
187 FETCH c_check_hz_exists into cur_rec_hz_exists;
188 IF c_check_hz_exists%NotFound THEN
189 CLOSE c_check_hz_exists;
190 CLOSE c_priority;
191 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
192 igs_ge_msg_stack.add;
193 app_exception.raise_exception;
194 ELSE
195 CLOSE c_check_hz_exists;
196 CLOSE c_priority;
197 END IF;
198 END IF;
199 --CLOSE c_priority;
200 END IF;
201 END IF;
202 END check_parent_existance;
203
204
205 FUNCTION get_pk_for_validation (
206 x_rsv_uop_prf_id IN NUMBER
207 ) RETURN BOOLEAN AS
208 /*
209 || Created By : apelleti
210 || Created On : 04-MAY-2001
211 || Purpose : Validates the Primary Key of the table.
212 || Known limitations, enhancements or remarks :
213 || Change History :
214 || Who When What
215 || (reverse chronological order - newest change first)
216 */
217 CURSOR cur_rowid IS
218 SELECT rowid
219 FROM igs_ps_rsv_uop_prf
220 WHERE rsv_uop_prf_id = x_rsv_uop_prf_id
221 FOR UPDATE NOWAIT;
222
223 lv_rowid cur_rowid%RowType;
224
225 BEGIN
226
227 OPEN cur_rowid;
228 FETCH cur_rowid INTO lv_rowid;
229 IF (cur_rowid%FOUND) THEN
230 CLOSE cur_rowid;
231 RETURN(TRUE);
232 ELSE
233 CLOSE cur_rowid;
234 RETURN(FALSE);
235 END IF;
236
237 END get_pk_for_validation;
238
239
240 FUNCTION get_uk_for_validation (
241 x_rsv_uop_pri_id IN NUMBER,
242 x_preference_code IN VARCHAR2
243 ) RETURN BOOLEAN AS
244 /*
245 || Created By : apelleti
246 || Created On : 04-MAY-2001
247 || Purpose : Validates the Unique Keys of the table.
248 || Known limitations, enhancements or remarks :
249 || Change History :
250 || Who When What
251 || (reverse chronological order - newest change first)
252 */
253 CURSOR cur_rowid IS
254 SELECT rowid
255 FROM igs_ps_rsv_uop_prf
256 WHERE rsv_uop_pri_id = x_rsv_uop_pri_id
257 AND preference_code = x_preference_code
258 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
259
260 lv_rowid cur_rowid%RowType;
261
262 BEGIN
263
264 OPEN cur_rowid;
265 FETCH cur_rowid INTO lv_rowid;
266 IF (cur_rowid%FOUND) THEN
267 CLOSE cur_rowid;
268 RETURN (true);
269 ELSE
270 CLOSE cur_rowid;
271 RETURN(FALSE);
272 END IF;
273
274 END get_uk_for_validation ;
275
276
277 PROCEDURE get_fk_igs_ps_rsv_uop_pri (
278 x_rsv_uop_pri_id IN NUMBER
279 ) AS
280 /*
281 || Created By : apelleti
282 || Created On : 04-MAY-2001
283 || Purpose : Validates the Foreign Keys for the table.
284 || Known limitations, enhancements or remarks :
285 || Change History :
286 || Who When What
287 || (reverse chronological order - newest change first)
288 */
289 CURSOR cur_rowid IS
290 SELECT rowid
291 FROM igs_ps_rsv_uop_prf
292 WHERE ((rsv_uop_pri_id = x_rsv_uop_pri_id));
293
294 lv_rowid cur_rowid%RowType;
295
296 BEGIN
297
298 OPEN cur_rowid;
299 FETCH cur_rowid INTO lv_rowid;
300 IF (cur_rowid%FOUND) THEN
301 CLOSE cur_rowid;
302 fnd_message.set_name ('IGS', 'IGS_PS_USPF_USPR_FK');
303 igs_ge_msg_stack.add;
304 app_exception.raise_exception;
305 RETURN;
306 END IF;
307 CLOSE cur_rowid;
308
309 END get_fk_igs_ps_rsv_uop_pri;
310
311 PROCEDURE get_fk_igs_ps_ver_all (
312 x_preference_code IN VARCHAR2,
313 x_preference_version IN NUMBER
314 ) AS
315 /*
316 || Created By : apelleti
317 || Created On : 02-MAY-2001
318 || Purpose : Validates the Foreign Keys for the table.
319 || Known limitations, enhancements or remarks :
320 || Change History :
321 || Who When What
322 || (reverse chronological order - newest change first)
323 */
324 CURSOR cur_rowid IS
325 SELECT rowid
326 FROM igs_ps_rsv_uop_prf
327 WHERE ((preference_code = x_preference_code) AND (preference_version = x_preference_version) );
328
329 lv_rowid cur_rowid%RowType;
330
331 BEGIN
332
333 OPEN cur_rowid;
334 FETCH cur_rowid INTO lv_rowid;
335 IF (cur_rowid%FOUND) THEN
336 CLOSE cur_rowid;
337 fnd_message.set_name ('IGS', 'IGS_PS_RUPF_CRV_FK');
338 igs_ge_msg_stack.add;
339 app_exception.raise_exception;
340 RETURN;
341 END IF;
342 CLOSE cur_rowid;
343
344 END get_fk_igs_ps_ver_all;
345
346 PROCEDURE get_fk_igs_ps_stage_type (
347 x_preference_code IN VARCHAR2
348 ) AS
349 /*
350 || Created By : apelleti
351 || Created On : 02-MAY-2001
352 || Purpose : Validates the Foreign Keys for the table.
353 || Known limitations, enhancements or remarks :
354 || Change History :
355 || Who When What
356 || (reverse chronological order - newest change first)
357 */
358 CURSOR cur_rowid IS
359 SELECT rowid
360 FROM igs_ps_rsv_uop_prf
361 WHERE ((preference_code = x_preference_code));
362
363 lv_rowid cur_rowid%RowType;
364
365 BEGIN
366
367 OPEN cur_rowid;
368 FETCH cur_rowid INTO lv_rowid;
369 IF (cur_rowid%FOUND) THEN
370 CLOSE cur_rowid;
371 fnd_message.set_name ('IGS', 'IGS_PS_RUPF_CSTT_FK');
372 igs_ge_msg_stack.add;
373 app_exception.raise_exception;
374 RETURN;
375 END IF;
376 CLOSE cur_rowid;
377
378 END get_fk_igs_ps_stage_type;
379
380 PROCEDURE get_fk_igs_en_unit_set_all (
381 x_preference_code IN VARCHAR2,
382 x_preference_version IN NUMBER
383 ) AS
384 /*
385 || Created By : apelleti
386 || Created On : 02-MAY-2001
387 || Purpose : Validates the Foreign Keys for the table.
388 || Known limitations, enhancements or remarks :
389 || Change History :
390 || Who When What
391 || (reverse chronological order - newest change first)
392 */
393 CURSOR cur_rowid IS
394 SELECT rowid
395 FROM igs_ps_rsv_uop_prf
396 WHERE ((preference_code = x_preference_code) AND (preference_version = x_preference_version));
397
398 lv_rowid cur_rowid%RowType;
399
400 BEGIN
401
402 OPEN cur_rowid;
403 FETCH cur_rowid INTO lv_rowid;
404 IF (cur_rowid%FOUND) THEN
405 CLOSE cur_rowid;
406 fnd_message.set_name ('IGS', 'IGS_PS_RUPF_US_FK');
407 igs_ge_msg_stack.add;
408 app_exception.raise_exception;
409 RETURN;
410 END IF;
411 CLOSE cur_rowid;
412
413 END get_fk_igs_en_unit_set_all;
414
415 PROCEDURE get_fk_hz_parties (
416 x_preference_code IN VARCHAR2
417 ) AS
418 /*
419 || Created By : apelleti
420 || Created On : 02-MAY-2001
421 || Purpose : Validates the Foreign Keys for the table.
422 || Known limitations, enhancements or remarks :
423 || Change History :
424 || Who When What
425 || (reverse chronological order - newest change first)
426 */
427 CURSOR cur_rowid IS
428 SELECT rowid
429 FROM igs_ps_rsv_uop_prf
430 WHERE ((preference_code = x_preference_code));
431
432 lv_rowid cur_rowid%RowType;
433
434 BEGIN
435
436 OPEN cur_rowid;
437 FETCH cur_rowid INTO lv_rowid;
438 IF (cur_rowid%FOUND) THEN
439 CLOSE cur_rowid;
440 fnd_message.set_name ('IGS', 'IGS_PS_RUPF_HZ_PARTIES_FK');
441 igs_ge_msg_stack.add;
442 app_exception.raise_exception;
443 RETURN;
444 END IF;
445 CLOSE cur_rowid;
446
447 END get_fk_hz_parties;
448
449 PROCEDURE BeforeInsertUpdate(p_inserting BOOLEAN , p_updating BOOLEAN) AS
450 p_message_name VARCHAR2(30);
451 BEGIN
452 IF ( p_inserting = TRUE OR (p_updating = TRUE AND new_references.group_id <> old_references.group_id ) ) THEN
453 IF NOT IGS_PE_PERSID_GROUP_PKG.val_persid_group(new_references.group_id,p_message_name) THEN
454 Fnd_Message.Set_Name('IGS', p_message_name);
455 IGS_GE_MSG_STACK.ADD;
456 App_Exception.Raise_Exception;
457 END IF;
458 END IF;
459 END BeforeInsertUpdate;
460
461 PROCEDURE before_dml (
462 p_action IN VARCHAR2,
463 x_rowid IN VARCHAR2 DEFAULT NULL,
464 x_rsv_uop_prf_id IN NUMBER DEFAULT NULL,
465 x_rsv_uop_pri_id IN NUMBER DEFAULT NULL,
466 x_preference_order IN NUMBER DEFAULT NULL,
467 x_preference_code IN VARCHAR2 DEFAULT NULL,
468 x_preference_version IN NUMBER DEFAULT NULL,
469 x_percentage_reserved IN NUMBER DEFAULT NULL,
470 x_group_id IN NUMBER DEFAULT NULL,
471 x_creation_date IN DATE DEFAULT NULL,
472 x_created_by IN NUMBER DEFAULT NULL,
473 x_last_update_date IN DATE DEFAULT NULL,
474 x_last_updated_by IN NUMBER DEFAULT NULL,
475 x_last_update_login IN NUMBER DEFAULT NULL
476 ) AS
477 /*
478 || Created By : apelleti
479 || Created On : 04-MAY-2001
480 || Purpose : Initialises the columns, Checks Constraints, Calls the
481 || Trigger Handlers for the table, before any DML operation.
482 || Known limitations, enhancements or remarks :
483 || Change History :
484 || Who When What
485 || (reverse chronological order - newest change first)
486 */
487 BEGIN
488
489 set_column_values (
490 p_action,
491 x_rowid,
492 x_rsv_uop_prf_id,
493 x_rsv_uop_pri_id,
494 x_preference_order,
495 x_preference_code,
496 x_preference_version,
497 x_percentage_reserved,
498 x_group_id,
499 x_creation_date,
500 x_created_by,
501 x_last_update_date,
502 x_last_updated_by,
503 x_last_update_login
504 );
505
506 IF (p_action = 'INSERT') THEN
507 -- Call all the procedures related to Before Insert.
508 BeforeInsertUpdate(TRUE,FALSE);
509 IF ( get_pk_for_validation(
510 new_references.rsv_uop_prf_id
511 )
512 ) THEN
513 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
514 igs_ge_msg_stack.add;
515 app_exception.raise_exception;
516 END IF;
517 check_uniqueness;
518 check_parent_existance;
519 ELSIF (p_action = 'UPDATE') THEN
520 -- Call all the procedures related to Before Update.
521 BeforeInsertUpdate(FALSE,TRUE);
522 check_uniqueness;
523 check_parent_existance;
524 ELSIF (p_action = 'VALIDATE_INSERT') THEN
525 -- Call all the procedures related to Before Insert.
526 IF ( get_pk_for_validation (
527 new_references.rsv_uop_prf_id
528 )
529 ) THEN
530 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
531 igs_ge_msg_stack.add;
532 app_exception.raise_exception;
533 END IF;
534 check_uniqueness;
535 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
536 check_uniqueness;
537 END IF;
538
539 l_rowid:=NULL;
540
541 END before_dml;
542
543
544 PROCEDURE insert_row (
545 x_rowid IN OUT NOCOPY VARCHAR2,
546 x_rsv_uop_prf_id IN OUT NOCOPY NUMBER,
547 x_rsv_uop_pri_id IN NUMBER,
548 x_preference_order IN NUMBER,
549 x_preference_code IN VARCHAR2,
550 x_preference_version IN NUMBER,
551 x_percentage_reserved IN NUMBER,
552 x_group_id IN NUMBER,
553 x_mode IN VARCHAR2 DEFAULT 'R'
554 ) AS
555 /*
556 || Created By : apelleti
557 || Created On : 04-MAY-2001
558 || Purpose : Handles the INSERT DML logic for the table.
559 || Known limitations, enhancements or remarks :
560 || Change History :
561 || Who When What
562 || (reverse chronological order - newest change first)
563 */
564 CURSOR c IS
565 SELECT rowid
566 FROM igs_ps_rsv_uop_prf
567 WHERE rsv_uop_prf_id = x_rsv_uop_prf_id;
568
569 x_last_update_date DATE;
570 x_last_updated_by NUMBER;
571 x_last_update_login NUMBER;
572
573 BEGIN
574
575 x_last_update_date := SYSDATE;
576 IF (x_mode = 'I') THEN
577 x_last_updated_by := 1;
578 x_last_update_login := 0;
579 ELSIF (x_mode = 'R') THEN
580 x_last_updated_by := fnd_global.user_id;
581 IF (x_last_updated_by IS NULL) THEN
582 x_last_updated_by := -1;
583 END IF;
584 x_last_update_login := fnd_global.login_id;
585 IF (x_last_update_login IS NULL) THEN
586 x_last_update_login := -1;
587 END IF;
588 ELSE
589 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
590 igs_ge_msg_stack.add;
591 app_exception.raise_exception;
592 END IF;
593
594 SELECT igs_ps_rsv_uop_prf_s.NEXTVAL
595 INTO x_rsv_uop_prf_id
596 FROM dual;
597
598 before_dml(
599 p_action => 'INSERT',
600 x_rowid => x_rowid,
601 x_rsv_uop_prf_id => x_rsv_uop_prf_id,
602 x_rsv_uop_pri_id => x_rsv_uop_pri_id,
603 x_preference_order => x_preference_order,
604 x_preference_code => x_preference_code,
605 x_preference_version => x_preference_version,
606 x_percentage_reserved => x_percentage_reserved,
607 x_group_id => x_group_id,
608 x_creation_date => x_last_update_date,
609 x_created_by => x_last_updated_by,
610 x_last_update_date => x_last_update_date,
611 x_last_updated_by => x_last_updated_by,
612 x_last_update_login => x_last_update_login
613 );
614
615 INSERT INTO igs_ps_rsv_uop_prf (
616 rsv_uop_prf_id,
617 rsv_uop_pri_id,
618 preference_order,
619 preference_code,
620 preference_version,
621 percentage_reserved,
622 group_id,
623 creation_date,
624 created_by,
625 last_update_date,
626 last_updated_by,
627 last_update_login
628 ) VALUES (
629 new_references.rsv_uop_prf_id,
630 new_references.rsv_uop_pri_id,
631 new_references.preference_order,
632 new_references.preference_code,
633 new_references.preference_version,
634 new_references.percentage_reserved,
635 new_references.group_id,
636 x_last_update_date,
637 x_last_updated_by,
638 x_last_update_date,
639 x_last_updated_by,
640 x_last_update_login
641 );
642
643 OPEN c;
644 FETCH c INTO x_rowid;
645 IF (c%NOTFOUND) THEN
646 CLOSE c;
647 RAISE NO_DATA_FOUND;
648 END IF;
649 CLOSE c;
650
651 END insert_row;
652
653
654 PROCEDURE lock_row (
655 x_rowid IN VARCHAR2,
656 x_rsv_uop_prf_id IN NUMBER,
657 x_rsv_uop_pri_id IN NUMBER,
658 x_preference_order IN NUMBER,
659 x_preference_code IN VARCHAR2,
660 x_preference_version IN NUMBER,
661 x_percentage_reserved IN NUMBER,
662 x_group_id IN NUMBER
663 ) AS
664 /*
665 || Created By : apelleti
666 || Created On : 04-MAY-2001
667 || Purpose : Handles the LOCK mechanism for the table.
668 || Known limitations, enhancements or remarks :
669 || Change History :
670 || Who When What
671 || (reverse chronological order - newest change first)
672 */
673 CURSOR c1 IS
674 SELECT
675 rsv_uop_pri_id,
676 preference_order,
677 preference_code,
678 preference_version,
679 percentage_reserved,
680 group_id
681 FROM igs_ps_rsv_uop_prf
682 WHERE rowid = x_rowid
683 FOR UPDATE NOWAIT;
684
685 tlinfo c1%ROWTYPE;
686
687 BEGIN
688
689 OPEN c1;
690 FETCH c1 INTO tlinfo;
691 IF (c1%notfound) THEN
692 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
693 igs_ge_msg_stack.add;
694 CLOSE c1;
695 app_exception.raise_exception;
696 RETURN;
697 END IF;
698 CLOSE c1;
699
700 IF (
701 (tlinfo.rsv_uop_pri_id = x_rsv_uop_pri_id)
702 AND (tlinfo.preference_order = x_preference_order)
703 AND (tlinfo.preference_code = x_preference_code)
704 AND ((tlinfo.preference_version = x_preference_version) OR ((tlinfo.preference_version IS NULL) AND (X_preference_version IS NULL)))
705 AND (tlinfo.percentage_reserved = x_percentage_reserved)
706 AND ((tlinfo.group_id = x_group_id) OR ((tlinfo.group_id IS NULL) AND (X_group_id IS NULL)))
707 ) THEN
708 NULL;
709 ELSE
710 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
711 igs_ge_msg_stack.add;
712 app_exception.raise_exception;
713 END IF;
714
715 RETURN;
716
717 END lock_row;
718
719
720 PROCEDURE update_row (
721 x_rowid IN VARCHAR2,
722 x_rsv_uop_prf_id IN NUMBER,
723 x_rsv_uop_pri_id IN NUMBER,
724 x_preference_order IN NUMBER,
725 x_preference_code IN VARCHAR2,
726 x_preference_version IN NUMBER,
727 x_percentage_reserved IN NUMBER,
728 x_group_id IN NUMBER,
729 x_mode IN VARCHAR2 DEFAULT 'R'
730 ) AS
731 /*
732 || Created By : apelleti
733 || Created On : 04-MAY-2001
734 || Purpose : Handles the UPDATE DML logic for the table.
735 || Known limitations, enhancements or remarks :
736 || Change History :
737 || Who When What
738 || (reverse chronological order - newest change first)
739 */
740 x_last_update_date DATE ;
741 x_last_updated_by NUMBER;
742 x_last_update_login NUMBER;
743
744 BEGIN
745
746 x_last_update_date := SYSDATE;
747 IF (X_MODE = 'I') THEN
748 x_last_updated_by := 1;
749 x_last_update_login := 0;
750 ELSIF (x_mode = 'R') THEN
751 x_last_updated_by := fnd_global.user_id;
752 IF x_last_updated_by IS NULL THEN
753 x_last_updated_by := -1;
754 END IF;
755 x_last_update_login := fnd_global.login_id;
756 IF (x_last_update_login IS NULL) THEN
757 x_last_update_login := -1;
758 END IF;
759 ELSE
760 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
761 igs_ge_msg_stack.add;
762 app_exception.raise_exception;
763 END IF;
764
765 before_dml(
766 p_action => 'UPDATE',
767 x_rowid => x_rowid,
768 x_rsv_uop_prf_id => x_rsv_uop_prf_id,
769 x_rsv_uop_pri_id => x_rsv_uop_pri_id,
770 x_preference_order => x_preference_order,
771 x_preference_code => x_preference_code,
772 x_preference_version => x_preference_version,
773 x_percentage_reserved => x_percentage_reserved,
774 x_group_id => x_group_id,
775 x_creation_date => x_last_update_date,
776 x_created_by => x_last_updated_by,
777 x_last_update_date => x_last_update_date,
778 x_last_updated_by => x_last_updated_by,
779 x_last_update_login => x_last_update_login
780 );
781
782 UPDATE igs_ps_rsv_uop_prf
783 SET
784 rsv_uop_pri_id = new_references.rsv_uop_pri_id,
785 preference_order = new_references.preference_order,
786 preference_code = new_references.preference_code,
787 preference_version = new_references.preference_version,
788 percentage_reserved = new_references.percentage_reserved,
789 group_id = new_references.group_id,
790 last_update_date = x_last_update_date,
791 last_updated_by = x_last_updated_by,
792 last_update_login = x_last_update_login
793 WHERE rowid = x_rowid;
794
795 IF (SQL%NOTFOUND) THEN
796 RAISE NO_DATA_FOUND;
797 END IF;
798
799 END update_row;
800
801
802 PROCEDURE add_row (
803 x_rowid IN OUT NOCOPY VARCHAR2,
804 x_rsv_uop_prf_id IN OUT NOCOPY NUMBER,
805 x_rsv_uop_pri_id IN NUMBER,
806 x_preference_order IN NUMBER,
807 x_preference_code IN VARCHAR2,
808 x_preference_version IN NUMBER,
809 x_percentage_reserved IN NUMBER,
810 x_group_id IN NUMBER,
811 x_mode IN VARCHAR2 DEFAULT 'R'
812 ) AS
813 /*
814 || Created By : apelleti
815 || Created On : 04-MAY-2001
816 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
817 || Known limitations, enhancements or remarks :
818 || Change History :
819 || Who When What
820 || (reverse chronological order - newest change first)
821 */
822 CURSOR c1 IS
823 SELECT rowid
824 FROM igs_ps_rsv_uop_prf
825 WHERE rsv_uop_prf_id = x_rsv_uop_prf_id;
826
827 BEGIN
828
829 OPEN c1;
830 FETCH c1 INTO x_rowid;
831 IF (c1%NOTFOUND) THEN
832 CLOSE c1;
833
834 insert_row (
835 x_rowid,
836 x_rsv_uop_prf_id,
837 x_rsv_uop_pri_id,
838 x_preference_order,
839 x_preference_code,
840 x_preference_version,
841 x_percentage_reserved,
842 x_group_id,
843 x_mode
844 );
845 RETURN;
846 END IF;
847 CLOSE c1;
848
849 update_row (
850 x_rowid,
851 x_rsv_uop_prf_id,
852 x_rsv_uop_pri_id,
853 x_preference_order,
854 x_preference_code,
855 x_preference_version,
856 x_percentage_reserved,
857 x_group_id,
858 x_mode
859 );
860
861 END add_row;
862
863
864 PROCEDURE delete_row (
865 x_rowid IN VARCHAR2
866 ) AS
867 /*
868 || Created By : apelleti
869 || Created On : 04-MAY-2001
870 || Purpose : Handles the DELETE DML logic for the table.
871 || Known limitations, enhancements or remarks :
872 || Change History :
873 || Who When What
874 || (reverse chronological order - newest change first)
875 */
876 BEGIN
877
878 before_dml (
879 p_action => 'DELETE',
880 x_rowid => x_rowid
881 );
882
883 DELETE FROM igs_ps_rsv_uop_prf
884 WHERE rowid = x_rowid;
885
886 IF (SQL%NOTFOUND) THEN
887 RAISE NO_DATA_FOUND;
888 END IF;
889
890 END delete_row;
891
892
893 END igs_ps_rsv_uop_prf_pkg;
[Home] [Help]