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