1 PACKAGE BODY igs_ps_rsv_ogpri_pkg AS
2 /* $Header: IGSPI1UB.pls 120.1 2005/08/18 07:15:01 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ps_rsv_ogpri_all%ROWTYPE;
6 new_references igs_ps_rsv_ogpri_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_rsv_org_unit_pri_id IN NUMBER DEFAULT NULL,
12 x_org_unit_cd IN VARCHAR2 DEFAULT NULL,
13 x_priority_order IN NUMBER DEFAULT NULL,
14 x_priority_value IN VARCHAR2 DEFAULT NULL,
15 x_creation_date IN DATE DEFAULT NULL,
16 x_created_by IN NUMBER DEFAULT NULL,
17 x_last_update_date IN DATE DEFAULT NULL,
18 x_last_updated_by IN NUMBER DEFAULT NULL,
19 x_last_update_login IN NUMBER DEFAULT NULL
20 ) AS
21 /*
22 || Created By : apelleti
23 || Created On : 02-MAY-2001
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 IGS_PS_RSV_OGPRI_ALL
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.rsv_org_unit_pri_id := x_rsv_org_unit_pri_id;
55 new_references.org_unit_cd := x_org_unit_cd;
56 new_references.priority_order := x_priority_order;
57 new_references.priority_value := x_priority_value;
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_uniqueness AS
75 /*
76 || Created By : apelleti
77 || Created On : 02-MAY-2001
78 || Purpose : Handles the Unique Constraint logic defined for the columns.
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 ( get_uk_for_validation (
87 new_references.org_unit_cd,
88 new_references.priority_value,
89 new_references.org_id
90 )
91 ) THEN
92 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
93 igs_ge_msg_stack.add;
94 app_exception.raise_exception;
95 END IF;
96
97 END check_uniqueness;
98
99
100 PROCEDURE check_parent_existance AS
101 /*
102 || Created By : apelleti
103 || Created On : 02-MAY-2001
104 || Purpose : Checks for the existance of Parent records.
105 || Known limitations, enhancements or remarks :
106 || Change History :
107 || Who When What
108 || sommukhe 12-AUG-2005 Bug#4377818,changed the cursor c_check_hz_exists, included table igs_pe_hz_parties in
109 || FROM clause and modified the WHERE clause by joining HZ_PARTIES and IGS_PE_HZ_PARTIES
110 || using party_id and org unit being compared with oss_org_unit_cd of IGS_PE_HZ_PARTIES.
111 || (reverse chronological order - newest change first)
112 */
113 CURSOR c_check_hz_exists
114 is
115 SELECT
116 'x'
117 FROM
118 hz_parties hp,igs_pe_hz_parties pe
119 WHERE
120 hp.party_id = pe.party_id
121 AND pe.oss_org_unit_cd =new_references.org_unit_cd;
122 cur_rec_hz_exists c_check_hz_exists%ROWTYPE;
123 BEGIN
124
125 IF (((old_references.org_unit_cd = new_references.org_unit_cd)) OR
126 ((new_references.org_unit_cd IS NULL))) THEN
127 NULL;
128 ELSE
129
130 OPEN c_check_hz_exists;
131 FETCH c_check_hz_exists into cur_rec_hz_exists;
132 IF c_check_hz_exists%NotFound THEN
133 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
134 igs_ge_msg_stack.add;
135 app_exception.raise_exception;
136 END IF;
137 END IF;
138
139 END check_parent_existance;
140
141
142 PROCEDURE check_child_existance IS
143 /*
144 || Created By : apelleti
145 || Created On : 02-MAY-2001
146 || Purpose : Checks for the existance of Child records.
147 || Known limitations, enhancements or remarks :
148 || Change History :
149 || Who When What
150 || (reverse chronological order - newest change first)
151 */
152 BEGIN
153
154 igs_ps_rsv_orgun_prf_pkg.get_fk_igs_ps_rsv_ogpri_all (
155 old_references.rsv_org_unit_pri_id
156 );
157
158 END check_child_existance;
159
160
161 FUNCTION get_pk_for_validation (
162 x_rsv_org_unit_pri_id IN NUMBER
163 ) RETURN BOOLEAN AS
164 /*
165 || Created By : apelleti
166 || Created On : 02-MAY-2001
167 || Purpose : Validates the Primary Key of the table.
168 || Known limitations, enhancements or remarks :
169 || Change History :
170 || Who When What
171 || (reverse chronological order - newest change first)
172 */
173 CURSOR cur_rowid IS
174 SELECT rowid
175 FROM igs_ps_rsv_ogpri_all
176 WHERE rsv_org_unit_pri_id = x_rsv_org_unit_pri_id
177 FOR UPDATE NOWAIT;
178
179 lv_rowid cur_rowid%RowType;
180
181 BEGIN
182
183 OPEN cur_rowid;
184 FETCH cur_rowid INTO lv_rowid;
185 IF (cur_rowid%FOUND) THEN
186 CLOSE cur_rowid;
187 RETURN(TRUE);
188 ELSE
189 CLOSE cur_rowid;
190 RETURN(FALSE);
191 END IF;
192
193 END get_pk_for_validation;
194
195
196 FUNCTION get_uk_for_validation (
197 x_org_unit_cd IN VARCHAR2,
198 x_priority_value IN VARCHAR2,
199 x_org_id IN NUMBER
200 ) RETURN BOOLEAN AS
201 /*
202 || Created By : apelleti
203 || Created On : 02-MAY-2001
204 || Purpose : Validates the Unique Keys of the table.
205 || Known limitations, enhancements or remarks :
206 || Change History :
207 || Who When What
208 || vvutukur 18-feb-2002 removed org_id check in cursor cur_rowid and selected from igs_ps_rsv_ogpri
209 || instead of igs_ps_rsv_ogpri_all.bug:2222272(SWSCR006).
210 || (reverse chronological order - newest change first)
211 */
212 CURSOR cur_rowid IS
213 SELECT rowid
214 FROM igs_ps_rsv_ogpri
215 WHERE org_unit_cd = x_org_unit_cd
216 AND priority_value = x_priority_value
217 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
218
219 lv_rowid cur_rowid%RowType;
220
221 BEGIN
222
223 OPEN cur_rowid;
224 FETCH cur_rowid INTO lv_rowid;
225 IF (cur_rowid%FOUND) THEN
226 CLOSE cur_rowid;
227 RETURN (true);
228 ELSE
229 CLOSE cur_rowid;
230 RETURN(FALSE);
231 END IF;
232
233 END get_uk_for_validation ;
234
235
236 PROCEDURE get_ufk_hz_parties (
237 x_party_number IN VARCHAR2
238 ) AS
239 /*
240 || Created By : apelleti
241 || Created On : 02-MAY-2001
242 || Purpose : Validates the Foreign Keys for the table.
243 || Known limitations, enhancements or remarks :
244 || Change History :
245 || Who When What
246 || (reverse chronological order - newest change first)
247 */
248 CURSOR cur_rowid IS
249 SELECT rowid
250 FROM igs_ps_rsv_ogpri_all
251 WHERE ((org_unit_cd = x_party_number));
252
253 lv_rowid cur_rowid%RowType;
254
255 BEGIN
256
257 OPEN cur_rowid;
258 FETCH cur_rowid INTO lv_rowid;
259 IF (cur_rowid%FOUND) THEN
260 CLOSE cur_rowid;
261 fnd_message.set_name ('IGS', 'IGS_PS_ROPF_HZ_PARTIES_FK');
262 igs_ge_msg_stack.add;
263 app_exception.raise_exception;
264 RETURN;
265 END IF;
266 CLOSE cur_rowid;
267
268 END get_ufk_hz_parties;
269
270
271 PROCEDURE before_dml (
272 p_action IN VARCHAR2,
273 x_rowid IN VARCHAR2 DEFAULT NULL,
274 x_rsv_org_unit_pri_id IN NUMBER DEFAULT NULL,
275 x_org_unit_cd IN VARCHAR2 DEFAULT NULL,
276 x_priority_order IN NUMBER DEFAULT NULL,
277 x_priority_value IN VARCHAR2 DEFAULT NULL,
278 x_creation_date IN DATE DEFAULT NULL,
279 x_created_by IN NUMBER DEFAULT NULL,
280 x_last_update_date IN DATE DEFAULT NULL,
281 x_last_updated_by IN NUMBER DEFAULT NULL,
282 x_last_update_login IN NUMBER DEFAULT NULL
283 ) AS
284 /*
285 || Created By : apelleti
286 || Created On : 02-MAY-2001
287 || Purpose : Initialises the columns, Checks Constraints, Calls the
288 || Trigger Handlers for the table, before any DML operation.
289 || Known limitations, enhancements or remarks :
290 || Change History :
291 || Who When What
292 || (reverse chronological order - newest change first)
293 */
294 BEGIN
295
296 set_column_values (
297 p_action,
298 x_rowid,
299 x_rsv_org_unit_pri_id,
300 x_org_unit_cd,
301 x_priority_order,
302 x_priority_value,
303 x_creation_date,
304 x_created_by,
305 x_last_update_date,
306 x_last_updated_by,
307 x_last_update_login
308 );
309
310 IF (p_action = 'INSERT') THEN
311 -- Call all the procedures related to Before Insert.
312 IF ( get_pk_for_validation(
313 new_references.rsv_org_unit_pri_id
314 )
315 ) THEN
316 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
317 igs_ge_msg_stack.add;
318 app_exception.raise_exception;
319 END IF;
320 check_uniqueness;
321 check_parent_existance;
322 ELSIF (p_action = 'UPDATE') THEN
323 -- Call all the procedures related to Before Update.
324 check_uniqueness;
325 check_parent_existance;
326 ELSIF (p_action = 'DELETE') THEN
327 -- Call all the procedures related to Before Delete.
328 check_child_existance;
329 ELSIF (p_action = 'VALIDATE_INSERT') THEN
330 -- Call all the procedures related to Before Insert.
331 IF ( get_pk_for_validation (
332 new_references.rsv_org_unit_pri_id
333 )
334 ) THEN
335 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
336 igs_ge_msg_stack.add;
337 app_exception.raise_exception;
338 END IF;
339 check_uniqueness;
340 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
341 check_uniqueness;
342 ELSIF (p_action = 'VALIDATE_DELETE') THEN
343 check_child_existance;
344 END IF;
345
346 l_rowid:=NULL;
347
348 END before_dml;
349
350
351 PROCEDURE insert_row (
352 x_rowid IN OUT NOCOPY VARCHAR2,
353 x_rsv_org_unit_pri_id IN OUT NOCOPY NUMBER,
354 x_org_unit_cd IN VARCHAR2,
355 x_priority_order IN NUMBER,
356 x_priority_value IN VARCHAR2,
357 x_mode IN VARCHAR2 DEFAULT 'R'
358 ) AS
359 /*
360 || Created By : apelleti
361 || Created On : 02-MAY-2001
362 || Purpose : Handles the INSERT DML logic for the table.
363 || Known limitations, enhancements or remarks :
364 || Change History :
365 || Who When What
366 || (reverse chronological order - newest change first)
367 */
368 CURSOR c IS
369 SELECT rowid
370 FROM igs_ps_rsv_ogpri_all
371 WHERE rsv_org_unit_pri_id = x_rsv_org_unit_pri_id;
372
373 x_last_update_date DATE;
374 x_last_updated_by NUMBER;
375 x_last_update_login NUMBER;
376
377 BEGIN
378
379 x_last_update_date := SYSDATE;
380 IF (x_mode = 'I') THEN
381 x_last_updated_by := 1;
382 x_last_update_login := 0;
383 ELSIF (x_mode = 'R') THEN
384 x_last_updated_by := fnd_global.user_id;
385 IF (x_last_updated_by IS NULL) THEN
386 x_last_updated_by := -1;
387 END IF;
388 x_last_update_login := fnd_global.login_id;
389 IF (x_last_update_login IS NULL) THEN
390 x_last_update_login := -1;
391 END IF;
392 ELSE
393 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
394 igs_ge_msg_stack.add;
395 app_exception.raise_exception;
396 END IF;
397
398 SELECT igs_ps_rsv_ogpri_all_s.NEXTVAL
399 INTO x_rsv_org_unit_pri_id
400 FROM dual;
401
402 new_references.org_id := igs_ge_gen_003.get_org_id;
403
404 before_dml(
405 p_action => 'INSERT',
406 x_rowid => x_rowid,
407 x_rsv_org_unit_pri_id => x_rsv_org_unit_pri_id,
408 x_org_unit_cd => x_org_unit_cd,
409 x_priority_order => x_priority_order,
410 x_priority_value => x_priority_value,
411 x_creation_date => x_last_update_date,
412 x_created_by => x_last_updated_by,
413 x_last_update_date => x_last_update_date,
414 x_last_updated_by => x_last_updated_by,
415 x_last_update_login => x_last_update_login
416 );
417
418 INSERT INTO igs_ps_rsv_ogpri_all (
419 rsv_org_unit_pri_id,
420 org_unit_cd,
421 priority_order,
422 priority_value,
423 org_id,
424 creation_date,
425 created_by,
426 last_update_date,
427 last_updated_by,
428 last_update_login
429 ) VALUES (
430 new_references.rsv_org_unit_pri_id,
431 new_references.org_unit_cd,
432 new_references.priority_order,
433 new_references.priority_value,
434 new_references.org_id,
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_rsv_org_unit_pri_id IN NUMBER,
456 x_org_unit_cd IN VARCHAR2,
457 x_priority_order IN NUMBER,
458 x_priority_value IN VARCHAR2
459 ) AS
460 /*
461 || Created By : apelleti
462 || Created On : 02-MAY-2001
463 || Purpose : Handles the LOCK mechanism for the table.
464 || Known limitations, enhancements or remarks :
465 || Change History :
466 || Who When What
467 || (reverse chronological order - newest change first)
468 */
469 CURSOR c1 IS
470 SELECT
471 org_unit_cd,
472 priority_order,
473 priority_value
474 FROM igs_ps_rsv_ogpri_all
475 WHERE rowid = x_rowid
476 FOR UPDATE NOWAIT;
477
478 tlinfo c1%ROWTYPE;
479
480 BEGIN
481
482 OPEN c1;
483 FETCH c1 INTO tlinfo;
484 IF (c1%notfound) THEN
485 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
486 igs_ge_msg_stack.add;
487 CLOSE c1;
488 app_exception.raise_exception;
489 RETURN;
490 END IF;
491 CLOSE c1;
492
493 IF (
494 (tlinfo.org_unit_cd = x_org_unit_cd)
495 AND (tlinfo.priority_order = x_priority_order)
496 AND (tlinfo.priority_value = x_priority_value)
497 ) THEN
498 NULL;
499 ELSE
500 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
501 igs_ge_msg_stack.add;
502 app_exception.raise_exception;
503 END IF;
504
505 RETURN;
506
507 END lock_row;
508
509
510 PROCEDURE update_row (
511 x_rowid IN VARCHAR2,
512 x_rsv_org_unit_pri_id IN NUMBER,
513 x_org_unit_cd IN VARCHAR2,
514 x_priority_order IN NUMBER,
515 x_priority_value IN VARCHAR2,
516 x_mode IN VARCHAR2 DEFAULT 'R'
517 ) AS
518 /*
519 || Created By : apelleti
520 || Created On : 02-MAY-2001
521 || Purpose : Handles the UPDATE DML logic for the table.
522 || Known limitations, enhancements or remarks :
523 || Change History :
524 || Who When What
525 || (reverse chronological order - newest change first)
526 */
527 x_last_update_date DATE ;
528 x_last_updated_by NUMBER;
529 x_last_update_login NUMBER;
530
531 BEGIN
532
533 x_last_update_date := SYSDATE;
534 IF (X_MODE = 'I') THEN
535 x_last_updated_by := 1;
536 x_last_update_login := 0;
537 ELSIF (x_mode = 'R') THEN
538 x_last_updated_by := fnd_global.user_id;
539 IF x_last_updated_by IS NULL THEN
540 x_last_updated_by := -1;
541 END IF;
542 x_last_update_login := fnd_global.login_id;
543 IF (x_last_update_login IS NULL) THEN
544 x_last_update_login := -1;
545 END IF;
546 ELSE
547 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
548 igs_ge_msg_stack.add;
549 app_exception.raise_exception;
550 END IF;
551
552 before_dml(
553 p_action => 'UPDATE',
554 x_rowid => x_rowid,
555 x_rsv_org_unit_pri_id => x_rsv_org_unit_pri_id,
556 x_org_unit_cd => x_org_unit_cd,
557 x_priority_order => x_priority_order,
558 x_priority_value => x_priority_value,
559 x_creation_date => x_last_update_date,
560 x_created_by => x_last_updated_by,
561 x_last_update_date => x_last_update_date,
562 x_last_updated_by => x_last_updated_by,
563 x_last_update_login => x_last_update_login
564 );
565
566 UPDATE igs_ps_rsv_ogpri_all
567 SET
568 org_unit_cd = new_references.org_unit_cd,
569 priority_order = new_references.priority_order,
570 priority_value = new_references.priority_value,
571 last_update_date = x_last_update_date,
572 last_updated_by = x_last_updated_by,
573 last_update_login = x_last_update_login
574 WHERE rowid = x_rowid;
575
576 IF (SQL%NOTFOUND) THEN
577 RAISE NO_DATA_FOUND;
578 END IF;
579
580 END update_row;
581
582
583 PROCEDURE add_row (
584 x_rowid IN OUT NOCOPY VARCHAR2,
585 x_rsv_org_unit_pri_id IN OUT NOCOPY NUMBER,
586 x_org_unit_cd IN VARCHAR2,
587 x_priority_order IN NUMBER,
588 x_priority_value IN VARCHAR2,
589 x_mode IN VARCHAR2 DEFAULT 'R'
590 ) AS
591 /*
592 || Created By : apelleti
593 || Created On : 02-MAY-2001
594 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
595 || Known limitations, enhancements or remarks :
596 || Change History :
597 || Who When What
598 || (reverse chronological order - newest change first)
599 */
600 CURSOR c1 IS
601 SELECT rowid
602 FROM igs_ps_rsv_ogpri_all
603 WHERE rsv_org_unit_pri_id = x_rsv_org_unit_pri_id;
604
605 BEGIN
606
607 OPEN c1;
608 FETCH c1 INTO x_rowid;
609 IF (c1%NOTFOUND) THEN
610 CLOSE c1;
611
612 insert_row (
613 x_rowid,
614 x_rsv_org_unit_pri_id,
615 x_org_unit_cd,
616 x_priority_order,
617 x_priority_value,
618 x_mode
619 );
620 RETURN;
621 END IF;
622 CLOSE c1;
623
624 update_row (
625 x_rowid,
626 x_rsv_org_unit_pri_id,
627 x_org_unit_cd,
628 x_priority_order,
629 x_priority_value,
630 x_mode
631 );
632
633 END add_row;
634
635
636 PROCEDURE delete_row (
637 x_rowid IN VARCHAR2
638 ) AS
639 /*
640 || Created By : apelleti
641 || Created On : 02-MAY-2001
642 || Purpose : Handles the DELETE DML logic for the table.
643 || Known limitations, enhancements or remarks :
644 || Change History :
645 || Who When What
646 || (reverse chronological order - newest change first)
647 */
648 BEGIN
649
650 before_dml (
651 p_action => 'DELETE',
652 x_rowid => x_rowid
653 );
654
655 DELETE FROM igs_ps_rsv_ogpri_all
656 WHERE rowid = x_rowid;
657
658 IF (SQL%NOTFOUND) THEN
659 RAISE NO_DATA_FOUND;
660 END IF;
661
662 END delete_row;
663
664
665 END igs_ps_rsv_ogpri_pkg;
[Home] [Help]