1 PACKAGE BODY igs_da_cnfg_stat_pkg AS
2 /* $Header: IGSKI47B.pls 115.0 2003/04/15 09:23:24 ddey noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_da_cnfg_stat%ROWTYPE;
6 new_references igs_da_cnfg_stat%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_request_type_id IN NUMBER,
12 x_stat_type IN VARCHAR2,
13 x_timeframe IN VARCHAR2,
14 x_creation_date IN DATE,
15 x_created_by IN NUMBER,
16 x_last_update_date IN DATE,
17 x_last_updated_by IN NUMBER,
18 x_last_update_login IN NUMBER
19 ) AS
20 /*
21 || Created By :
22 || Created On : 19-MAR-2003
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_da_cnfg_stat
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.request_type_id := x_request_type_id;
54 new_references.stat_type := x_stat_type;
55 new_references.timeframe := x_timeframe;
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 :
75 || Created On : 19-MAR-2003
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.request_type_id = new_references.request_type_id)) OR
85 ((new_references.request_type_id IS NULL))) THEN
86 NULL;
87 ELSIF NOT igs_da_cnfg_req_typ_pkg.get_pk_for_validation (
88 new_references.request_type_id
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.stat_type = new_references.stat_type)) OR
96 ((new_references.stat_type IS NULL))) THEN
97 NULL;
98 ELSIF NOT igs_pr_stat_type_pkg.get_pk_for_validation (
99 new_references.stat_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_request_type_id IN NUMBER,
111 x_stat_type IN VARCHAR2
112 ) RETURN BOOLEAN AS
113 /*
114 || Created By :
115 || Created On : 19-MAR-2003
116 || Purpose : Validates the Primary Key of the table.
117 || Known limitations, enhancements or remarks :
118 || Change History :
119 || Who When What
120 || (reverse chronological order - newest change first)
121 */
122 CURSOR cur_rowid IS
123 SELECT rowid
124 FROM igs_da_cnfg_stat
125 WHERE request_type_id = x_request_type_id
126 AND stat_type = x_stat_type
127 FOR UPDATE NOWAIT;
128
129 lv_rowid cur_rowid%RowType;
130
131 BEGIN
132
133 OPEN cur_rowid;
134 FETCH cur_rowid INTO lv_rowid;
135 IF (cur_rowid%FOUND) THEN
136 CLOSE cur_rowid;
137 RETURN(TRUE);
138 ELSE
139 CLOSE cur_rowid;
140 RETURN(FALSE);
141 END IF;
142
143 END get_pk_for_validation;
144
145
146 PROCEDURE get_fk_igs_da_cnfg_req_typ (
147 x_request_type_id IN NUMBER
148 ) AS
149 /*
150 || Created By :
151 || Created On : 19-MAR-2003
152 || Purpose : Validates the Foreign Keys for the table.
153 || Known limitations, enhancements or remarks :
154 || Change History :
155 || Who When What
156 || (reverse chronological order - newest change first)
157 */
158 CURSOR cur_rowid IS
159 SELECT rowid
160 FROM igs_da_cnfg_stat
161 WHERE ((request_type_id = x_request_type_id));
162
163 lv_rowid cur_rowid%RowType;
164
165 BEGIN
166
167 OPEN cur_rowid;
168 FETCH cur_rowid INTO lv_rowid;
169 IF (cur_rowid%FOUND) THEN
170 CLOSE cur_rowid;
171 fnd_message.set_name ('IGS', 'IGS_DA_CNFG_STAT_FK');
172 igs_ge_msg_stack.add;
173 app_exception.raise_exception;
174 RETURN;
175 END IF;
176 CLOSE cur_rowid;
177
178 END get_fk_igs_da_cnfg_req_typ;
179
180
181 PROCEDURE get_fk_igs_pr_stat_type (
182 x_stat_type IN VARCHAR2
183 ) AS
184 /*
185 || Created By :
186 || Created On : 19-MAR-2003
187 || Purpose : Validates the Foreign Keys for the table.
188 || Known limitations, enhancements or remarks :
189 || Change History :
190 || Who When What
191 || (reverse chronological order - newest change first)
192 */
193 CURSOR cur_rowid IS
194 SELECT rowid
195 FROM igs_da_cnfg_stat
196 WHERE ((stat_type = x_stat_type));
197
198 lv_rowid cur_rowid%RowType;
199
200 BEGIN
201
202 OPEN cur_rowid;
203 FETCH cur_rowid INTO lv_rowid;
204 IF (cur_rowid%FOUND) THEN
205 CLOSE cur_rowid;
206 fnd_message.set_name ('IGS', 'IGS_DA_PSTAT_STAT_FK');
207 igs_ge_msg_stack.add;
208 app_exception.raise_exception;
209 RETURN;
210 END IF;
211 CLOSE cur_rowid;
212
213 END get_fk_igs_pr_stat_type;
214
215
216 PROCEDURE before_dml (
217 p_action IN VARCHAR2,
218 x_rowid IN VARCHAR2,
219 x_request_type_id IN NUMBER,
220 x_stat_type IN VARCHAR2,
221 x_timeframe IN VARCHAR2,
222 x_creation_date IN DATE,
223 x_created_by IN NUMBER,
224 x_last_update_date IN DATE,
225 x_last_updated_by IN NUMBER,
226 x_last_update_login IN NUMBER
227 ) AS
228 /*
229 || Created By :
230 || Created On : 19-MAR-2003
231 || Purpose : Initialises the columns, Checks Constraints, Calls the
232 || Trigger Handlers for the table, before any DML operation.
233 || Known limitations, enhancements or remarks :
234 || Change History :
235 || Who When What
236 || (reverse chronological order - newest change first)
237 */
238 BEGIN
239
240 set_column_values (
241 p_action,
242 x_rowid,
243 x_request_type_id,
244 x_stat_type,
245 x_timeframe,
246 x_creation_date,
247 x_created_by,
248 x_last_update_date,
249 x_last_updated_by,
250 x_last_update_login
251 );
252
253 IF (p_action = 'INSERT') THEN
254 -- Call all the procedures related to Before Insert.
255 IF ( get_pk_for_validation(
256 new_references.request_type_id,
257 new_references.stat_type
258 )
259 ) THEN
260 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
261 igs_ge_msg_stack.add;
262 app_exception.raise_exception;
263 END IF;
264 check_parent_existance;
265 ELSIF (p_action = 'UPDATE') THEN
266 -- Call all the procedures related to Before Update.
267 check_parent_existance;
268 ELSIF (p_action = 'VALIDATE_INSERT') THEN
269 -- Call all the procedures related to Before Insert.
270 IF ( get_pk_for_validation (
271 new_references.request_type_id,
272 new_references.stat_type
273 )
274 ) THEN
275 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
276 igs_ge_msg_stack.add;
277 app_exception.raise_exception;
278 END IF;
279 END IF;
280
281 END before_dml;
282
283
284 PROCEDURE insert_row (
285 x_rowid IN OUT NOCOPY VARCHAR2,
286 x_request_type_id IN NUMBER,
287 x_stat_type IN VARCHAR2,
288 x_timeframe IN VARCHAR2,
289 x_mode IN VARCHAR2
290 ) AS
291 /*
292 || Created By :
293 || Created On : 19-MAR-2003
294 || Purpose : Handles the INSERT DML logic for the table.
295 || Known limitations, enhancements or remarks :
296 || Change History :
297 || Who When What
298 || (reverse chronological order - newest change first)
299 */
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 fnd_message.set_token ('ROUTINE', 'IGS_DA_CNFG_STAT_PKG.INSERT_ROW');
323 igs_ge_msg_stack.add;
324 app_exception.raise_exception;
325 END IF;
326
327 before_dml(
328 p_action => 'INSERT',
329 x_rowid => x_rowid,
330 x_request_type_id => x_request_type_id,
331 x_stat_type => x_stat_type,
332 x_timeframe => x_timeframe,
333 x_creation_date => x_last_update_date,
334 x_created_by => x_last_updated_by,
335 x_last_update_date => x_last_update_date,
336 x_last_updated_by => x_last_updated_by,
337 x_last_update_login => x_last_update_login
338 );
339
340 INSERT INTO igs_da_cnfg_stat (
341 request_type_id,
342 stat_type,
343 timeframe,
344 creation_date,
345 created_by,
346 last_update_date,
347 last_updated_by,
348 last_update_login
349 ) VALUES (
350 new_references.request_type_id,
351 new_references.stat_type,
352 new_references.timeframe,
353 x_last_update_date,
354 x_last_updated_by,
355 x_last_update_date,
356 x_last_updated_by,
357 x_last_update_login
358 ) RETURNING ROWID INTO x_rowid;
359
360 END insert_row;
361
362
363 PROCEDURE lock_row (
364 x_rowid IN VARCHAR2,
365 x_request_type_id IN NUMBER,
366 x_stat_type IN VARCHAR2,
367 x_timeframe IN VARCHAR2
368 ) AS
369 /*
370 || Created By :
371 || Created On : 19-MAR-2003
372 || Purpose : Handles the LOCK mechanism for the table.
373 || Known limitations, enhancements or remarks :
374 || Change History :
375 || Who When What
376 || (reverse chronological order - newest change first)
377 */
378 CURSOR c1 IS
379 SELECT
380 timeframe
381 FROM igs_da_cnfg_stat
382 WHERE rowid = x_rowid
383 FOR UPDATE NOWAIT;
384
385 tlinfo c1%ROWTYPE;
386
387 BEGIN
388
389 OPEN c1;
390 FETCH c1 INTO tlinfo;
391 IF (c1%notfound) THEN
392 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
393 igs_ge_msg_stack.add;
394 CLOSE c1;
395 app_exception.raise_exception;
396 RETURN;
397 END IF;
398 CLOSE c1;
399
400 IF (
401 (tlinfo.timeframe = x_timeframe)
402 ) THEN
403 NULL;
404 ELSE
405 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
406 igs_ge_msg_stack.add;
407 app_exception.raise_exception;
408 END IF;
409
410 RETURN;
411
412 END lock_row;
413
414
415 PROCEDURE update_row (
416 x_rowid IN VARCHAR2,
417 x_request_type_id IN NUMBER,
418 x_stat_type IN VARCHAR2,
419 x_timeframe IN VARCHAR2,
420 x_mode IN VARCHAR2
421 ) AS
422 /*
423 || Created By :
424 || Created On : 19-MAR-2003
425 || Purpose : Handles the UPDATE DML logic for the table.
426 || Known limitations, enhancements or remarks :
427 || Change History :
428 || Who When What
429 || (reverse chronological order - newest change first)
430 */
431 x_last_update_date DATE ;
432 x_last_updated_by NUMBER;
433 x_last_update_login NUMBER;
434
435 BEGIN
436
437 x_last_update_date := SYSDATE;
438 IF (X_MODE = 'I') THEN
439 x_last_updated_by := 1;
440 x_last_update_login := 0;
441 ELSIF (x_mode = 'R') THEN
442 x_last_updated_by := fnd_global.user_id;
443 IF x_last_updated_by IS NULL THEN
444 x_last_updated_by := -1;
445 END IF;
446 x_last_update_login := fnd_global.login_id;
447 IF (x_last_update_login IS NULL) THEN
448 x_last_update_login := -1;
449 END IF;
450 ELSE
451 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
452 fnd_message.set_token ('ROUTINE', 'IGS_DA_CNFG_STAT_PKG.UPDATE_ROW');
453 igs_ge_msg_stack.add;
454 app_exception.raise_exception;
455 END IF;
456
457 before_dml(
458 p_action => 'UPDATE',
459 x_rowid => x_rowid,
460 x_request_type_id => x_request_type_id,
461 x_stat_type => x_stat_type,
462 x_timeframe => x_timeframe,
463 x_creation_date => x_last_update_date,
464 x_created_by => x_last_updated_by,
465 x_last_update_date => x_last_update_date,
466 x_last_updated_by => x_last_updated_by,
467 x_last_update_login => x_last_update_login
468 );
469
470 UPDATE igs_da_cnfg_stat
471 SET
472 timeframe = new_references.timeframe,
473 last_update_date = x_last_update_date,
474 last_updated_by = x_last_updated_by,
475 last_update_login = x_last_update_login
476 WHERE rowid = x_rowid;
477
478 IF (SQL%NOTFOUND) THEN
479 RAISE NO_DATA_FOUND;
480 END IF;
481
482 END update_row;
483
484
485 PROCEDURE add_row (
486 x_rowid IN OUT NOCOPY VARCHAR2,
487 x_request_type_id IN NUMBER,
488 x_stat_type IN VARCHAR2,
489 x_timeframe IN VARCHAR2,
490 x_mode IN VARCHAR2
491 ) AS
492 /*
493 || Created By :
494 || Created On : 19-MAR-2003
495 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
496 || Known limitations, enhancements or remarks :
497 || Change History :
498 || Who When What
499 || (reverse chronological order - newest change first)
500 */
501 CURSOR c1 IS
502 SELECT rowid
503 FROM igs_da_cnfg_stat
504 WHERE request_type_id = x_request_type_id
505 AND stat_type = x_stat_type;
506
507 BEGIN
508
509 OPEN c1;
510 FETCH c1 INTO x_rowid;
511 IF (c1%NOTFOUND) THEN
512 CLOSE c1;
513
514 insert_row (
515 x_rowid,
516 x_request_type_id,
517 x_stat_type,
518 x_timeframe,
519 x_mode
520 );
521 RETURN;
522 END IF;
523 CLOSE c1;
524
525 update_row (
526 x_rowid,
527 x_request_type_id,
528 x_stat_type,
529 x_timeframe,
530 x_mode
531 );
532
533 END add_row;
534
535
536 PROCEDURE delete_row (
537 x_rowid IN VARCHAR2
538 ) AS
539 /*
540 || Created By :
541 || Created On : 19-MAR-2003
542 || Purpose : Handles the DELETE DML logic for the table.
543 || Known limitations, enhancements or remarks :
544 || Change History :
545 || Who When What
546 || (reverse chronological order - newest change first)
547 */
548 BEGIN
549
550 before_dml (
551 p_action => 'DELETE',
552 x_rowid => x_rowid
553 );
554
555 DELETE FROM igs_da_cnfg_stat
556 WHERE rowid = x_rowid;
557
558 IF (SQL%NOTFOUND) THEN
559 RAISE NO_DATA_FOUND;
560 END IF;
561
562 END delete_row;
563
564
565 END igs_da_cnfg_stat_pkg;
[Home] [Help]