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