1 PACKAGE BODY Igs_En_Pig_Cp_Setup_Pkg AS
2 /* $Header: IGSEI68B.pls 115.5 2003/02/24 14:18:19 npalanis noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_en_pig_cp_setup%ROWTYPE;
6 new_references igs_en_pig_cp_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_prsid_max_cp IN NUMBER,
13 x_prsid_min_cp IN NUMBER,
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_cp_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.prsid_max_cp := x_prsid_max_cp;
55 new_references.prsid_min_cp := x_prsid_min_cp;
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
99 FUNCTION get_pk_for_validation (
100 x_group_id IN NUMBER
101 ) RETURN BOOLEAN AS
102 /*
103 || Created By : nbehera
104 || Created On : 28-OCT-2002
105 || Purpose : Validates the Primary Key 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_cp_setup
114 WHERE group_id = x_group_id
115 FOR UPDATE NOWAIT;
116
117 lv_rowid cur_rowid%ROWTYPE;
118
119 BEGIN
120
121 OPEN cur_rowid;
122 FETCH cur_rowid INTO lv_rowid;
123 IF (cur_rowid%FOUND) THEN
124 CLOSE cur_rowid;
125 RETURN(TRUE);
126 ELSE
127 CLOSE cur_rowid;
128 RETURN(FALSE);
129 END IF;
130
131 END get_pk_for_validation;
132
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_cp_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_PIGECP_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_prsid_max_cp IN NUMBER,
186 x_prsid_min_cp IN NUMBER,
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_prsid_max_cp,
210 x_prsid_min_cp,
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 )
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_parent_existance;
230 ELSIF (p_action = 'UPDATE') THEN
231 -- Call all the procedures related to Before Update.
232 BeforeInsertUpdate(FALSE,TRUE);
233 check_parent_existance;
234 ELSIF (p_action = 'VALIDATE_INSERT') THEN
235 -- Call all the procedures related to Before Insert.
236 BeforeInsertUpdate(TRUE,FALSE);
237 IF ( get_pk_for_validation (
238 new_references.group_id
239 )
240 ) THEN
241 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
242 igs_ge_msg_stack.ADD;
243 app_exception.raise_exception;
244 END IF;
245 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
246 BeforeInsertUpdate(FALSE,TRUE);
247 END IF;
248
249 END before_dml;
250
251
252 PROCEDURE insert_row (
253 x_rowid IN OUT NOCOPY VARCHAR2,
254 x_group_id IN NUMBER,
255 x_prsid_max_cp IN NUMBER,
256 x_prsid_min_cp IN NUMBER,
257 x_mode IN VARCHAR2
258 ) AS
259 /*
260 || Created By : nbehera
261 || Created On : 28-OCT-2002
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
269 x_last_update_date DATE;
270 x_last_updated_by NUMBER;
271 x_last_update_login NUMBER;
272
273 BEGIN
274
275 x_last_update_date := SYSDATE;
276 IF (x_mode = 'I') THEN
277 x_last_updated_by := 1;
278 x_last_update_login := 0;
279 ELSIF (x_mode = 'R') THEN
280 x_last_updated_by := fnd_global.user_id;
281 IF (x_last_updated_by IS NULL) THEN
282 x_last_updated_by := -1;
283 END IF;
284 x_last_update_login := fnd_global.login_id;
285 IF (x_last_update_login IS NULL) THEN
286 x_last_update_login := -1;
287 END IF;
288 ELSE
289 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
290 igs_ge_msg_stack.ADD;
291 app_exception.raise_exception;
292 END IF;
293
294 before_dml(
295 p_action => 'INSERT',
296 x_rowid => x_rowid,
297 x_group_id => x_group_id,
298 x_prsid_max_cp => x_prsid_max_cp,
299 x_prsid_min_cp => x_prsid_min_cp,
300 x_creation_date => x_last_update_date,
301 x_created_by => x_last_updated_by,
302 x_last_update_date => x_last_update_date,
303 x_last_updated_by => x_last_updated_by,
304 x_last_update_login => x_last_update_login
305 );
306
307 INSERT INTO igs_en_pig_cp_setup (
308 group_id,
309 prsid_max_cp,
310 prsid_min_cp,
311 creation_date,
312 created_by,
313 last_update_date,
314 last_updated_by,
315 last_update_login
316 ) VALUES (
317 new_references.group_id,
318 new_references.prsid_max_cp,
319 new_references.prsid_min_cp,
320 x_last_update_date,
321 x_last_updated_by,
322 x_last_update_date,
323 x_last_updated_by,
324 x_last_update_login
325 ) RETURNING ROWID INTO x_rowid;
326
327 END insert_row;
328
329
330 PROCEDURE lock_row (
331 x_rowid IN VARCHAR2,
332 x_group_id IN NUMBER,
333 x_prsid_max_cp IN NUMBER,
334 x_prsid_min_cp IN NUMBER
335 ) AS
336 /*
337 || Created By : nbehera
338 || Created On : 28-OCT-2002
339 || Purpose : Handles the LOCK mechanism for the table.
340 || Known limitations, enhancements or remarks :
341 || Change History :
342 || Who When What
343 || (reverse chronological order - newest change first)
344 */
345 CURSOR c1 IS
346 SELECT
347 group_id,
348 prsid_max_cp,
349 prsid_min_cp
350 FROM igs_en_pig_cp_setup
351 WHERE ROWID = x_rowid
352 FOR UPDATE NOWAIT;
353
354 tlinfo c1%ROWTYPE;
355
356 BEGIN
357
358 OPEN c1;
359 FETCH c1 INTO tlinfo;
360 IF (c1%NOTFOUND) THEN
361 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
362 igs_ge_msg_stack.ADD;
363 CLOSE c1;
364 app_exception.raise_exception;
365 RETURN;
366 END IF;
367 CLOSE c1;
368
369 IF (
370 (tlinfo.group_id = x_group_id)
371 AND ((tlinfo.prsid_max_cp = x_prsid_max_cp) OR ((tlinfo.prsid_max_cp IS NULL) AND (X_prsid_max_cp IS NULL)))
372 AND ((tlinfo.prsid_min_cp = x_prsid_min_cp) OR ((tlinfo.prsid_min_cp IS NULL) AND (X_prsid_min_cp IS NULL)))
373 ) THEN
374 NULL;
375 ELSE
376 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
377 igs_ge_msg_stack.ADD;
378 app_exception.raise_exception;
379 END IF;
380
381 RETURN;
382
383 END lock_row;
384
385
386 PROCEDURE update_row (
387 x_rowid IN VARCHAR2,
388 x_group_id IN NUMBER,
389 x_prsid_max_cp IN NUMBER,
390 x_prsid_min_cp IN NUMBER,
391 x_mode IN VARCHAR2
392 ) AS
393 /*
394 || Created By : nbehera
395 || Created On : 28-OCT-2002
396 || Purpose : Handles the UPDATE DML logic for the table.
397 || Known limitations, enhancements or remarks :
398 || Change History :
399 || Who When What
400 || (reverse chronological order - newest change first)
401 */
402 x_last_update_date DATE ;
403 x_last_updated_by NUMBER;
404 x_last_update_login NUMBER;
405
406 BEGIN
407
408 x_last_update_date := SYSDATE;
409 IF (X_MODE = 'I') THEN
410 x_last_updated_by := 1;
411 x_last_update_login := 0;
412 ELSIF (x_mode = 'R') THEN
413 x_last_updated_by := fnd_global.user_id;
414 IF x_last_updated_by IS NULL THEN
415 x_last_updated_by := -1;
416 END IF;
417 x_last_update_login := fnd_global.login_id;
418 IF (x_last_update_login IS NULL) THEN
419 x_last_update_login := -1;
420 END IF;
421 ELSE
422 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
423 igs_ge_msg_stack.ADD;
424 app_exception.raise_exception;
425 END IF;
426
427 before_dml(
428 p_action => 'UPDATE',
429 x_rowid => x_rowid,
430 x_group_id => x_group_id,
431 x_prsid_max_cp => x_prsid_max_cp,
432 x_prsid_min_cp => x_prsid_min_cp,
433 x_creation_date => x_last_update_date,
434 x_created_by => x_last_updated_by,
435 x_last_update_date => x_last_update_date,
436 x_last_updated_by => x_last_updated_by,
437 x_last_update_login => x_last_update_login
438 );
439
440 UPDATE igs_en_pig_cp_setup
441 SET
442 group_id = new_references.group_id,
443 prsid_max_cp = new_references.prsid_max_cp,
444 prsid_min_cp = new_references.prsid_min_cp,
445 last_update_date = x_last_update_date,
446 last_updated_by = x_last_updated_by,
447 last_update_login = x_last_update_login
448 WHERE ROWID = x_rowid;
449
450 IF (SQL%NOTFOUND) THEN
451 RAISE NO_DATA_FOUND;
452 END IF;
453
454 END update_row;
455
456
457 PROCEDURE add_row (
458 x_rowid IN OUT NOCOPY VARCHAR2,
459 x_group_id IN NUMBER,
460 x_prsid_max_cp IN NUMBER,
461 x_prsid_min_cp IN NUMBER,
462 x_mode IN VARCHAR2
463 ) AS
464 /*
465 || Created By : nbehera
466 || Created On : 28-OCT-2002
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_en_pig_cp_setup
476 WHERE group_id = x_group_id;
477
478 BEGIN
479
480 OPEN c1;
481 FETCH c1 INTO x_rowid;
482 IF (c1%NOTFOUND) THEN
483 CLOSE c1;
484
485 insert_row (
486 x_rowid,
487 x_group_id,
488 x_prsid_max_cp,
489 x_prsid_min_cp,
490 x_mode
491 );
492 RETURN;
493 END IF;
494 CLOSE c1;
495
496 update_row (
497 x_rowid,
498 x_group_id,
499 x_prsid_max_cp,
500 x_prsid_min_cp,
501 x_mode
502 );
503
504 END add_row;
505
506
507 PROCEDURE delete_row (
508 x_rowid IN VARCHAR2
509 ) AS
510 /*
511 || Created By : nbehera
512 || Created On : 28-OCT-2002
513 || Purpose : Handles the DELETE DML logic for the table.
514 || Known limitations, enhancements or remarks :
515 || Change History :
516 || Who When What
517 || (reverse chronological order - newest change first)
518 */
519 BEGIN
520
521 before_dml (
522 p_action => 'DELETE',
523 x_rowid => x_rowid
524 );
525
526 DELETE FROM igs_en_pig_cp_setup
527 WHERE ROWID = x_rowid;
528
529 IF (SQL%NOTFOUND) THEN
530 RAISE NO_DATA_FOUND;
531 END IF;
532
533 END delete_row;
534
535
536 END Igs_En_Pig_Cp_Setup_Pkg;
[Home] [Help]