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