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