1 PACKAGE BODY igs_pr_class_std_pkg AS
2 /* $Header: IGSQI29B.pls 115.14 2003/12/05 05:46:34 ckasu noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pr_class_std%ROWTYPE;
6 new_references igs_pr_class_std%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_igs_pr_class_std_id IN NUMBER DEFAULT NULL,
12 x_class_standing IN VARCHAR2 DEFAULT NULL,
13 x_description IN VARCHAR2 DEFAULT NULL,
14 x_closed_ind IN VARCHAR2 DEFAULT NULL,
15 x_creation_date IN DATE DEFAULT NULL,
16 x_created_by IN NUMBER DEFAULT NULL,
17 x_last_update_date IN DATE DEFAULT NULL,
18 x_last_updated_by IN NUMBER DEFAULT NULL,
19 x_last_update_login IN NUMBER DEFAULT NULL
20 ) AS
21 /*
22 || Created By : avenkatr
23 || Created On : 12-JUL-2001
24 || Purpose : Initialises the Old and New references for the columns of the table.
25 || Known limitations, enhancements or remarks :
26 || Change History :
27 || Who When What
28 || (reverse chronological order - newest change first)
29 */
30
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM IGS_PR_CLASS_STD
34 WHERE rowid = x_rowid;
35
36 BEGIN
37
38 l_rowid := x_rowid;
39
40 -- Code for setting the Old and New Reference Values.
41 -- Populate Old Values.
42 OPEN cur_old_ref_values;
43 FETCH cur_old_ref_values INTO old_references;
44 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
45 CLOSE cur_old_ref_values;
46 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
47 igs_ge_msg_stack.add;
48 app_exception.raise_exception;
49 RETURN;
50 END IF;
51 CLOSE cur_old_ref_values;
52
53 -- Populate New Values.
54 new_references.igs_pr_class_std_id := x_igs_pr_class_std_id;
55 new_references.class_standing := x_class_standing;
56 new_references.description := x_description;
57 new_references.closed_ind := x_closed_ind;
58
59 IF (p_action = 'UPDATE') THEN
60 new_references.creation_date := old_references.creation_date;
61 new_references.created_by := old_references.created_by;
62 ELSE
63 new_references.creation_date := x_creation_date;
64 new_references.created_by := x_created_by;
65 END IF;
66
67 new_references.last_update_date := x_last_update_date;
68 new_references.last_updated_by := x_last_updated_by;
69 new_references.last_update_login := x_last_update_login;
70
71 END set_column_values;
72
73 PROCEDURE check_uniqueness AS
74 /*
75 || Created By : avenkatr
76 || Created On : 18-JUL-2001
77 || Purpose : Handles the Unique Constraint logic defined for the columns.
78 || Known limitations, enhancements or remarks :
79 || Change History :
80 || Who When What
81 || (reverse chronological order - newest change first)
82 */
83 BEGIN
84
85 IF ( get_uk_for_validation (
86 new_references.class_standing
87 )
88 ) THEN
89 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
90 igs_ge_msg_stack.add;
91 app_exception.raise_exception;
92 END IF;
93
94 END check_uniqueness;
95
96 PROCEDURE check_child_existance IS
97 /*
98 || Created By : avenkatr
99 || Created On : 12-JUL-2001
100 || Purpose : Checks for the existance of Child records.
101 || Known limitations, enhancements or remarks :
102 || Change History :
103 || Who When What
104 || (reverse chronological order - newest change first)
105 || ckasu 04-Dec-2003 Added IGS_EN_SPA_TERMS_PKG.GET_FK_IGS_PR_CLASS_STD
106 for Term Records Build Bug# 2829263
107
108 */
109 BEGIN
110
111 igs_pr_css_class_std_pkg.get_fk_igs_pr_class_std (
112 old_references.igs_pr_class_std_id
113 );
114
115 igs_as_gpc_cls_stndg_pkg.get_ufk_igs_pr_class_std (
116 old_references.class_standing
117 );
118
119 -- Enhancement bug no 1877222, pmarada
120 igs_en_stdnt_ps_att_pkg.get_fk_igs_pr_class_std (
121 old_references.igs_pr_class_std_id
122 );
123
124 igs_fi_fee_as_rate_pkg.get_ufk_igs_pr_class_std (
125 old_references.class_standing
126 );
127
128 -- Added as a part of Term records build
129 IGS_EN_SPA_TERMS_PKG.get_fk_igs_pr_class_std (
130 old_references.igs_pr_class_std_id
131 );
132
133 -- This Call is Added as part of Enhancement Bug No : 2138644 , ayedubat
134 igs_pr_ru_appl_pkg.get_fk_igs_pr_class_std (
135 old_references.igs_pr_class_std_id
136 );
137 END check_child_existance;
138
139
140 FUNCTION get_pk_for_validation (
141 x_igs_pr_class_std_id IN NUMBER
142 ) RETURN BOOLEAN AS
143 /*
144 || Created By : avenkatr
145 || Created On : 12-JUL-2001
146 || Purpose : Validates the Primary Key of the table.
147 || Known limitations, enhancements or remarks :
148 || Change History :
149 || Who When What
150 || (reverse chronological order - newest change first)
151 */
152 CURSOR cur_rowid IS
153 SELECT rowid
154 FROM igs_pr_class_std
155 WHERE igs_pr_class_std_id = x_igs_pr_class_std_id ;
156
157 lv_rowid cur_rowid%RowType;
158
159 BEGIN
160
161 OPEN cur_rowid;
162 FETCH cur_rowid INTO lv_rowid;
163 IF (cur_rowid%FOUND) THEN
164 CLOSE cur_rowid;
165 RETURN(TRUE);
166 ELSE
167 CLOSE cur_rowid;
168 RETURN(FALSE);
169 END IF;
170
171 END get_pk_for_validation;
172
173 FUNCTION get_uk_for_validation (
174 x_class_standing IN VARCHAR2
175 ) RETURN BOOLEAN AS
176 /*
177 || Created By : avenkatr
178 || Created On : 18-JUL-2001
179 || Purpose : Validates the Unique Keys of the table.
180 || Known limitations, enhancements or remarks :
181 || Change History :
182 || Who When What
183 || (reverse chronological order - newest change first)
184 */
185 CURSOR cur_rowid IS
186 SELECT rowid
187 FROM igs_pr_class_std
188 WHERE class_standing = x_class_standing
189 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
190
191 lv_rowid cur_rowid%RowType;
192
193 BEGIN
194
195 OPEN cur_rowid;
196 FETCH cur_rowid INTO lv_rowid;
197 IF (cur_rowid%FOUND) THEN
198 CLOSE cur_rowid;
199 RETURN (true);
200 ELSE
201 CLOSE cur_rowid;
202 RETURN(FALSE);
203 END IF;
204
205 END get_uk_for_validation ;
206
207
208 PROCEDURE before_dml (
209 p_action IN VARCHAR2,
210 x_rowid IN VARCHAR2 DEFAULT NULL,
211 x_igs_pr_class_std_id IN NUMBER DEFAULT NULL,
212 x_class_standing IN VARCHAR2 DEFAULT NULL,
213 x_description IN VARCHAR2 DEFAULT NULL,
214 x_closed_ind IN VARCHAR2 DEFAULT NULL,
215 x_creation_date IN DATE DEFAULT NULL,
216 x_created_by IN NUMBER DEFAULT NULL,
217 x_last_update_date IN DATE DEFAULT NULL,
218 x_last_updated_by IN NUMBER DEFAULT NULL,
219 x_last_update_login IN NUMBER DEFAULT NULL
220 ) AS
221 /*
222 || Created By : avenkatr
223 || Created On : 12-JUL-2001
224 || Purpose : Initialises the columns, Checks Constraints, Calls the
225 || Trigger Handlers for the table, before any DML operation.
226 || Known limitations, enhancements or remarks :
227 || Change History :
228 || Who When What
229 || (reverse chronological order - newest change first)
230 */
231 BEGIN
232
233 set_column_values (
234 p_action,
235 x_rowid,
236 x_igs_pr_class_std_id,
237 x_class_standing,
238 x_description,
239 x_closed_ind,
240 x_creation_date,
241 x_created_by,
242 x_last_update_date,
243 x_last_updated_by,
244 x_last_update_login
245 );
246
247 IF (p_action = 'INSERT') THEN
248 -- Call all the procedures related to Before Insert.
249 IF ( get_pk_for_validation(
250 new_references.igs_pr_class_std_id
251 )
252 ) THEN
253 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
254 igs_ge_msg_stack.add;
255 app_exception.raise_exception;
256 END IF;
257 check_uniqueness;
258 ELSIF (p_action = 'UPDATE') THEN
259 -- Call all the procedures related to Before Update.
260 check_uniqueness;
261 ELSIF (p_action = 'DELETE') THEN
262 -- Call all the procedures related to Before Delete.
263 check_child_existance;
264 ELSIF (p_action = 'VALIDATE_INSERT') THEN
265 -- Call all the procedures related to Before Insert.
266 IF ( get_pk_for_validation (
267 new_references.igs_pr_class_std_id
268 )
269 ) THEN
270 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
271 igs_ge_msg_stack.add;
272 app_exception.raise_exception;
273 END IF;
274 check_uniqueness;
275 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
276 check_uniqueness;
277 ELSIF (p_action = 'VALIDATE_DELETE') THEN
278 check_child_existance;
279 END IF;
280
281 END before_dml;
282
283
284 PROCEDURE insert_row (
285 x_rowid IN OUT NOCOPY VARCHAR2,
286 x_igs_pr_class_std_id IN OUT NOCOPY NUMBER,
287 x_class_standing IN VARCHAR2,
288 x_description IN VARCHAR2,
289 x_closed_ind IN VARCHAR2,
290 x_mode IN VARCHAR2 DEFAULT 'R'
291 ) AS
292 /*
293 || Created By : avenkatr
294 || Created On : 12-JUL-2001
295 || Purpose : Handles the INSERT DML logic for the table.
296 || Known limitations, enhancements or remarks :
297 || Change History :
298 || Who When What
299 || (reverse chronological order - newest change first)
300 */
301 CURSOR c IS
302 SELECT rowid
303 FROM igs_pr_class_std
304 WHERE igs_pr_class_std_id = x_igs_pr_class_std_id;
305
306 x_last_update_date DATE;
307 x_last_updated_by NUMBER;
308 x_last_update_login NUMBER;
309
310 BEGIN
311
312 x_last_update_date := SYSDATE;
313 IF (x_mode = 'I') THEN
314 x_last_updated_by := 1;
315 x_last_update_login := 0;
316 ELSIF (x_mode = 'R') THEN
317 x_last_updated_by := fnd_global.user_id;
318 IF (x_last_updated_by IS NULL) THEN
319 x_last_updated_by := -1;
320 END IF;
321 x_last_update_login := fnd_global.login_id;
322 IF (x_last_update_login IS NULL) THEN
323 x_last_update_login := -1;
324 END IF;
325 ELSE
326 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
327 igs_ge_msg_stack.add;
328 app_exception.raise_exception;
329 END IF;
330
331 SELECT igs_pr_class_std_s.NEXTVAL
332 INTO x_igs_pr_class_std_id
333 FROM dual;
334
335 before_dml(
336 p_action => 'INSERT',
337 x_rowid => x_rowid,
338 x_igs_pr_class_std_id => x_igs_pr_class_std_id,
339 x_class_standing => x_class_standing,
340 x_description => x_description,
341 x_closed_ind => x_closed_ind,
342 x_creation_date => x_last_update_date,
343 x_created_by => x_last_updated_by,
344 x_last_update_date => x_last_update_date,
345 x_last_updated_by => x_last_updated_by,
346 x_last_update_login => x_last_update_login
347 );
348
349 INSERT INTO igs_pr_class_std (
350 igs_pr_class_std_id,
351 class_standing,
352 description,
353 closed_ind,
354 creation_date,
355 created_by,
356 last_update_date,
357 last_updated_by,
358 last_update_login
359 ) VALUES (
360 new_references.igs_pr_class_std_id,
361 new_references.class_standing,
362 new_references.description,
363 new_references.closed_ind,
364 x_last_update_date,
365 x_last_updated_by,
366 x_last_update_date,
367 x_last_updated_by,
368 x_last_update_login
369 );
370
371 OPEN c;
372 FETCH c INTO x_rowid;
373 IF (c%NOTFOUND) THEN
374 CLOSE c;
375 RAISE NO_DATA_FOUND;
376 END IF;
377 CLOSE c;
378
379 END insert_row;
380
381
382 PROCEDURE lock_row (
383 x_rowid IN VARCHAR2,
384 x_igs_pr_class_std_id IN NUMBER,
385 x_class_standing IN VARCHAR2,
386 x_description IN VARCHAR2,
387 x_closed_ind IN VARCHAR2
388 ) AS
389 /*
390 || Created By : avenkatr
391 || Created On : 12-JUL-2001
392 || Purpose : Handles the LOCK mechanism for the table.
393 || Known limitations, enhancements or remarks :
394 || Change History :
395 || Who When What
396 || (reverse chronological order - newest change first)
397 */
398 CURSOR c1 IS
399 SELECT
400 class_standing,
401 description,
402 closed_ind
403 FROM igs_pr_class_std
404 WHERE rowid = x_rowid
405 FOR UPDATE NOWAIT;
406
407 tlinfo c1%ROWTYPE;
408
409 BEGIN
410
411 OPEN c1;
412 FETCH c1 INTO tlinfo;
413 IF (c1%notfound) THEN
414 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
415 igs_ge_msg_stack.add;
416 CLOSE c1;
417 app_exception.raise_exception;
418 RETURN;
419 END IF;
420 CLOSE c1;
421
422 IF (
423 (tlinfo.class_standing = x_class_standing)
424 AND (tlinfo.description = x_description)
425 AND (tlinfo.closed_ind = x_closed_ind)
426 ) THEN
427 NULL;
428 ELSE
429 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
430 igs_ge_msg_stack.add;
431 app_exception.raise_exception;
432 END IF;
433
434 RETURN;
435
436 END lock_row;
437
438
439 PROCEDURE update_row (
440 x_rowid IN VARCHAR2,
441 x_igs_pr_class_std_id IN NUMBER,
442 x_class_standing IN VARCHAR2,
443 x_description IN VARCHAR2,
444 x_closed_ind IN VARCHAR2,
445 x_mode IN VARCHAR2 DEFAULT 'R'
446 ) AS
447 /*
448 || Created By : avenkatr
449 || Created On : 12-JUL-2001
450 || Purpose : Handles the UPDATE DML logic for the table.
451 || Known limitations, enhancements or remarks :
452 || Change History :
453 || Who When What
454 || (reverse chronological order - newest change first)
455 */
456 x_last_update_date DATE ;
457 x_last_updated_by NUMBER;
458 x_last_update_login NUMBER;
459
460 BEGIN
461
462 x_last_update_date := SYSDATE;
463 IF (X_MODE = 'I') THEN
464 x_last_updated_by := 1;
465 x_last_update_login := 0;
466 ELSIF (x_mode = 'R') THEN
467 x_last_updated_by := fnd_global.user_id;
468 IF x_last_updated_by IS NULL THEN
469 x_last_updated_by := -1;
470 END IF;
471 x_last_update_login := fnd_global.login_id;
472 IF (x_last_update_login IS NULL) THEN
473 x_last_update_login := -1;
474 END IF;
475 ELSE
476 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
477 igs_ge_msg_stack.add;
478 app_exception.raise_exception;
479 END IF;
480
481 before_dml(
482 p_action => 'UPDATE',
483 x_rowid => x_rowid,
484 x_igs_pr_class_std_id => x_igs_pr_class_std_id,
485 x_class_standing => x_class_standing,
486 x_description => x_description,
487 x_closed_ind => x_closed_ind,
488 x_creation_date => x_last_update_date,
489 x_created_by => x_last_updated_by,
490 x_last_update_date => x_last_update_date,
491 x_last_updated_by => x_last_updated_by,
492 x_last_update_login => x_last_update_login
493 );
494
495 UPDATE igs_pr_class_std
496 SET
497 class_standing = new_references.class_standing,
498 description = new_references.description,
499 closed_ind = new_references.closed_ind,
500 last_update_date = x_last_update_date,
501 last_updated_by = x_last_updated_by,
502 last_update_login = x_last_update_login
503 WHERE rowid = x_rowid;
504
505 IF (SQL%NOTFOUND) THEN
506 RAISE NO_DATA_FOUND;
507 END IF;
508
509 END update_row;
510
511
512 PROCEDURE add_row (
513 x_rowid IN OUT NOCOPY VARCHAR2,
514 x_igs_pr_class_std_id IN OUT NOCOPY NUMBER,
515 x_class_standing IN VARCHAR2,
516 x_description IN VARCHAR2,
517 x_closed_ind IN VARCHAR2,
518 x_mode IN VARCHAR2 DEFAULT 'R'
519 ) AS
520 /*
521 || Created By : avenkatr
522 || Created On : 12-JUL-2001
523 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
524 || Known limitations, enhancements or remarks :
525 || Change History :
526 || Who When What
527 || (reverse chronological order - newest change first)
528 */
529 CURSOR c1 IS
530 SELECT rowid
531 FROM igs_pr_class_std
532 WHERE igs_pr_class_std_id = x_igs_pr_class_std_id;
533
534 BEGIN
535
536 OPEN c1;
537 FETCH c1 INTO x_rowid;
538 IF (c1%NOTFOUND) THEN
539 CLOSE c1;
540
541 insert_row (
542 x_rowid,
543 x_igs_pr_class_std_id,
544 x_class_standing,
545 x_description,
546 x_closed_ind,
547 x_mode
548 );
549 RETURN;
550 END IF;
551 CLOSE c1;
552
553 update_row (
554 x_rowid,
555 x_igs_pr_class_std_id,
556 x_class_standing,
557 x_description,
558 x_closed_ind,
559 x_mode
560 );
561
562 END add_row;
563
564
565 /*Removed procedure Delete_row for Records Locking Bug 2784198 */
566
567
568 END igs_pr_class_std_pkg;
[Home] [Help]