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