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