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