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