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