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