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