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