1 PACKAGE BODY igs_pr_inst_sta_ref_pkg AS
2 /* $Header: IGSQI35B.pls 120.1 2005/11/21 02:01:00 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pr_inst_sta_ref%ROWTYPE;
6 new_references igs_pr_inst_sta_ref%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_stat_type IN VARCHAR2 DEFAULT NULL,
12 x_unit_ref_cd IN VARCHAR2 DEFAULT NULL,
13 x_include_or_exclude 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 x_reference_cd_type IN VARCHAR2 DEFAULT NULL
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 */
30
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM igs_pr_inst_sta_ref
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.stat_type := x_stat_type;
55 new_references.unit_ref_cd := x_unit_ref_cd;
56 new_references.include_or_exclude := x_include_or_exclude;
57
58 new_references.reference_cd_type := x_reference_cd_type;
59
60 IF (p_action = 'UPDATE') THEN
61 new_references.creation_date := old_references.creation_date;
62 new_references.created_by := old_references.created_by;
63 ELSE
64 new_references.creation_date := x_creation_date;
65 new_references.created_by := x_created_by;
66 END IF;
67
68 new_references.last_update_date := x_last_update_date;
69 new_references.last_updated_by := x_last_updated_by;
70 new_references.last_update_login := x_last_update_login;
71
72 END set_column_values;
73
74
75 PROCEDURE check_parent_existance AS
76 /*
77 || Created By : nbehera
78 || Created On : 02-NOV-2001
79 || Purpose : Checks for the existance of Parent records.
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 (((old_references.stat_type = new_references.stat_type)) OR
88 ((new_references.stat_type IS NULL))) THEN
89 NULL;
90 ELSIF NOT igs_pr_inst_stat_pkg.get_pk_for_validation (
91 new_references.stat_type
92 ) THEN
93 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
94 igs_ge_msg_stack.add;
95 app_exception.raise_exception;
96 END IF;
97
98 END check_parent_existance;
99
100
101 FUNCTION get_pk_for_validation (
102 x_stat_type IN VARCHAR2,
103 x_unit_ref_cd IN VARCHAR2,
104 x_reference_cd_type IN VARCHAR2
105 ) RETURN BOOLEAN AS
106 /*
107 || Created By : nbehera
108 || Created On : 02-NOV-2001
109 || Purpose : Validates the Primary Key of the table.
110 || Known limitations, enhancements or remarks :
111 || Change History :
112 || Who When What
113 || (reverse chronological order - newest change first)
114 */
115 CURSOR cur_rowid IS
116 SELECT rowid
117 FROM igs_pr_inst_sta_ref
118 WHERE stat_type = x_stat_type
119 AND unit_ref_cd = x_unit_ref_cd
120 AND reference_cd_type = x_reference_cd_type
121 FOR UPDATE NOWAIT;
122
123 lv_rowid cur_rowid%RowType;
124
125 BEGIN
126
127 OPEN cur_rowid;
128 FETCH cur_rowid INTO lv_rowid;
129 IF (cur_rowid%FOUND) THEN
130 CLOSE cur_rowid;
131 RETURN(TRUE);
132 ELSE
133 CLOSE cur_rowid;
134 RETURN(FALSE);
135 END IF;
136
137 END get_pk_for_validation;
138
139
140 PROCEDURE get_fk_igs_pr_inst_stat (
141 x_stat_type IN VARCHAR2
142 ) AS
143 /*
144 || Created By : nbehera
145 || Created On : 02-NOV-2001
146 || Purpose : Validates the Foreign Keys for 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_inst_sta_ref
155 WHERE ((stat_type = x_stat_type));
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 fnd_message.set_name ('IGS', 'IGS_PR_INSTR_STTY_FK');
166 igs_ge_msg_stack.add;
167 app_exception.raise_exception;
168 RETURN;
169 END IF;
170 CLOSE cur_rowid;
171
172 END get_fk_igs_pr_inst_stat;
173
174
175 PROCEDURE before_dml (
176 p_action IN VARCHAR2,
177 x_rowid IN VARCHAR2 DEFAULT NULL,
178 x_stat_type IN VARCHAR2 DEFAULT NULL,
179 x_unit_ref_cd IN VARCHAR2 DEFAULT NULL,
180 x_include_or_exclude IN VARCHAR2 DEFAULT NULL,
181 x_creation_date IN DATE DEFAULT NULL,
182 x_created_by IN NUMBER DEFAULT NULL,
183 x_last_update_date IN DATE DEFAULT NULL,
184 x_last_updated_by IN NUMBER DEFAULT NULL,
185 x_last_update_login IN NUMBER DEFAULT NULL,
186 x_reference_cd_type IN VARCHAR2 DEFAULT NULL
187 ) AS
188 /*
189 || Created By : nbehera
190 || Created On : 02-NOV-2001
191 || Purpose : Initialises the columns, Checks Constraints, Calls the
192 || Trigger Handlers for the table, before any DML operation.
193 || Known limitations, enhancements or remarks :
194 || Change History :
195 || Who When What
196 || (reverse chronological order - newest change first)
197 */
198 BEGIN
199
200 set_column_values (
201 p_action,
202 x_rowid,
203 x_stat_type,
204 x_unit_ref_cd,
205 x_include_or_exclude,
206 x_creation_date,
207 x_created_by,
208 x_last_update_date,
209 x_last_updated_by,
210 x_last_update_login,
211 x_reference_cd_type
212 );
213
214 IF (p_action = 'INSERT') THEN
215 -- Call all the procedures related to Before Insert.
216 IF ( get_pk_for_validation(
217 new_references.stat_type,
218 new_references.unit_ref_cd,
219 new_references.reference_cd_type
220 )
221 ) THEN
222 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
223 igs_ge_msg_stack.add;
224 app_exception.raise_exception;
225 END IF;
226 check_parent_existance;
227 ELSIF (p_action = 'UPDATE') THEN
228 -- Call all the procedures related to Before Update.
229 check_parent_existance;
230 ELSIF (p_action = 'VALIDATE_INSERT') THEN
231 -- Call all the procedures related to Before Insert.
232 IF ( get_pk_for_validation (
233 new_references.stat_type,
234 new_references.unit_ref_cd,
235 new_references.reference_cd_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 END IF;
243
244 END before_dml;
245
246
247 PROCEDURE insert_row (
248 x_rowid IN OUT NOCOPY VARCHAR2,
249 x_stat_type IN VARCHAR2,
250 x_unit_ref_cd IN VARCHAR2,
251 x_include_or_exclude IN VARCHAR2,
252 x_mode IN VARCHAR2 DEFAULT 'R',
253 x_reference_cd_type IN VARCHAR2
254
255 ) AS
256 /*
257 || Created By : nbehera
258 || Created On : 02-NOV-2001
259 || Purpose : Handles the INSERT DML logic for the table.
260 || Known limitations, enhancements or remarks :
261 || Change History :
262 || Who When What
263 || (reverse chronological order - newest change first)
264 */
265 CURSOR c IS
266 SELECT rowid
267 FROM igs_pr_inst_sta_ref
268 WHERE stat_type = x_stat_type
269 AND unit_ref_cd = x_unit_ref_cd;
270
271 CURSOR c1 IS
272 SELECT include_or_exclude
273 FROM igs_pr_inst_sta_ref
274 WHERE stat_type = x_stat_type;
275
276 x_last_update_date DATE;
277 x_last_updated_by NUMBER;
278 x_last_update_login NUMBER;
279 l_include_or_exclude igs_pr_inst_sta_ref.include_or_exclude%TYPE;
280 l_c1 c1%ROWTYPE;
281
282 BEGIN
283 x_last_update_date := SYSDATE;
284 IF (x_mode = 'I') THEN
285 x_last_updated_by := 1;
286 x_last_update_login := 0;
287 ELSIF (x_mode = 'R') THEN
288 x_last_updated_by := fnd_global.user_id;
289 IF (x_last_updated_by IS NULL) THEN
290 x_last_updated_by := -1;
291 END IF;
292 x_last_update_login := fnd_global.login_id;
293 IF (x_last_update_login IS NULL) THEN
294 x_last_update_login := -1;
295 END IF;
296 ELSE
297 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
298 igs_ge_msg_stack.add;
299 app_exception.raise_exception;
300 END IF;
301
302 before_dml(
303 p_action => 'INSERT',
304 x_rowid => x_rowid,
305 x_stat_type => x_stat_type,
306 x_unit_ref_cd => x_unit_ref_cd,
307 x_include_or_exclude => x_include_or_exclude,
308 x_creation_date => x_last_update_date,
309 x_created_by => x_last_updated_by,
310 x_last_update_date => x_last_update_date,
311 x_last_updated_by => x_last_updated_by,
312 x_last_update_login => x_last_update_login,
313 x_reference_cd_type => x_reference_cd_type
314 );
315 -- The code written in the For Loop checks whether the current
316 -- Unit Reference Code is having same value for the field Include
317 -- or Exclude as existing Unit Reference Codes. If not it raises
318 -- an Error indicating there exist Unit ReferenceCodes with differnt
319 -- Include or Exclude value.
320 FOR l_c1 IN c1 LOOP
321 l_include_or_exclude := l_c1.include_or_exclude;
322 IF l_include_or_exclude <> x_include_or_exclude THEN
323 fnd_message.set_name ('IGS', 'IGS_PR_INCLUDE_OR_EXCLUDE');
324 fnd_message.set_token('Include_or_Exclude1',x_include_or_exclude);
325 fnd_message.set_token('Include_or_Exclude2',l_include_or_exclude);
326 igs_ge_msg_stack.add;
327 app_exception.raise_exception;
328 END IF;
329 END LOOP;
330
331 INSERT INTO igs_pr_inst_sta_ref (
332 stat_type,
333 unit_ref_cd,
334 include_or_exclude,
335 creation_date,
336 created_by,
337 last_update_date,
338 last_updated_by,
339 last_update_login,
340 reference_cd_type
341 ) VALUES (
342 new_references.stat_type,
343 new_references.unit_ref_cd,
344 new_references.include_or_exclude,
345 x_last_update_date,
346 x_last_updated_by,
347 x_last_update_date,
348 x_last_updated_by,
349 x_last_update_login,
350 x_reference_cd_type
351 );
352
353 OPEN c;
354 FETCH c INTO x_rowid;
355 IF (c%NOTFOUND) THEN
356 CLOSE c;
357 RAISE NO_DATA_FOUND;
358 END IF;
359 CLOSE c;
360
361 END insert_row;
362
363
364 PROCEDURE lock_row (
365 x_rowid IN VARCHAR2,
366 x_stat_type IN VARCHAR2,
367 x_unit_ref_cd IN VARCHAR2,
368 x_include_or_exclude IN VARCHAR2,
369 x_reference_cd_type IN VARCHAR2
370 ) AS
371 /*
372 || Created By : nbehera
373 || Created On : 02-NOV-2001
374 || Purpose : Handles the LOCK mechanism for the table.
375 || Known limitations, enhancements or remarks :
376 || Change History :
377 || Who When What
378 || (reverse chronological order - newest change first)
379 */
380 CURSOR c1 IS
381 SELECT
382 include_or_exclude
383 FROM igs_pr_inst_sta_ref
384 WHERE rowid = x_rowid
385 FOR UPDATE NOWAIT;
386
387 tlinfo c1%ROWTYPE;
388
389 BEGIN
390
391 OPEN c1;
392 FETCH c1 INTO tlinfo;
393 IF (c1%notfound) THEN
394 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
395 igs_ge_msg_stack.add;
396 CLOSE c1;
397 app_exception.raise_exception;
398 RETURN;
399 END IF;
400 CLOSE c1;
401
402 IF (
403 (tlinfo.include_or_exclude = x_include_or_exclude)
404 ) THEN
405 NULL;
406 ELSE
407 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
408 igs_ge_msg_stack.add;
409 app_exception.raise_exception;
410 END IF;
411
412 RETURN;
413
414 END lock_row;
415
416
417 PROCEDURE update_row (
418 x_rowid IN VARCHAR2,
419 x_stat_type IN VARCHAR2,
420 x_unit_ref_cd IN VARCHAR2,
421 x_include_or_exclude IN VARCHAR2,
422 x_mode IN VARCHAR2 DEFAULT 'R',
423 x_reference_cd_type IN VARCHAR2
424 ) AS
425 /*
426 || Created By : nbehera
427 || Created On : 02-NOV-2001
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_stat_type => x_stat_type,
463 x_unit_ref_cd => x_unit_ref_cd,
464 x_include_or_exclude => x_include_or_exclude,
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 x_reference_cd_type => x_reference_cd_type
471 );
472
473 UPDATE igs_pr_inst_sta_ref
474 SET
475 include_or_exclude = new_references.include_or_exclude,
476 last_update_date = x_last_update_date,
477 last_updated_by = x_last_updated_by,
478 last_update_login = x_last_update_login
479 WHERE rowid = x_rowid;
480
481 IF (SQL%NOTFOUND) THEN
482 RAISE NO_DATA_FOUND;
483 END IF;
484
485 END update_row;
486
487
488 PROCEDURE add_row (
489 x_rowid IN OUT NOCOPY VARCHAR2,
490 x_stat_type IN VARCHAR2,
491 x_unit_ref_cd IN VARCHAR2,
492 x_include_or_exclude IN VARCHAR2,
493 x_mode IN VARCHAR2 DEFAULT 'R',
494 x_reference_cd_type IN VARCHAR2
495 ) AS
496 /*
497 || Created By : nbehera
498 || Created On : 02-NOV-2001
499 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
500 || Known limitations, enhancements or remarks :
501 || Change History :
502 || Who When What
503 || (reverse chronological order - newest change first)
504 */
505 CURSOR c1 IS
506 SELECT rowid
507 FROM igs_pr_inst_sta_ref
508 WHERE stat_type = x_stat_type
509 AND unit_ref_cd = x_unit_ref_cd;
510
511 BEGIN
512
513 OPEN c1;
514 FETCH c1 INTO x_rowid;
515 IF (c1%NOTFOUND) THEN
516 CLOSE c1;
517
518 insert_row (
519 x_rowid,
520 x_stat_type,
521 x_unit_ref_cd,
522 x_include_or_exclude,
523 x_mode,
524 x_reference_cd_type
525 );
526 RETURN;
527 END IF;
528 CLOSE c1;
529
530 update_row (
531 x_rowid,
532 x_stat_type,
533 x_unit_ref_cd,
534 x_include_or_exclude,
535 x_mode,
536 x_reference_cd_type
537 );
538
539 END add_row;
540
541
542 PROCEDURE delete_row (
543 x_rowid IN VARCHAR2
544 ) AS
545 /*
546 || Created By : nbehera
547 || Created On : 02-NOV-2001
548 || Purpose : Handles the DELETE DML logic for the table.
549 || Known limitations, enhancements or remarks :
550 || Change History :
551 || Who When What
552 || (reverse chronological order - newest change first)
553 */
554 BEGIN
555
556 before_dml (
557 p_action => 'DELETE',
558 x_rowid => x_rowid
559 );
560
561 DELETE FROM igs_pr_inst_sta_ref
562 WHERE rowid = x_rowid;
563
564 IF (SQL%NOTFOUND) THEN
565 RAISE NO_DATA_FOUND;
566 END IF;
567
568 END delete_row;
569
570
571 END igs_pr_inst_sta_ref_pkg;
[Home] [Help]