1 PACKAGE BODY igf_ap_gen_setup_pkg AS
2 /* $Header: IGFAI36B.pls 120.1 2005/09/08 14:40:50 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_ap_gen_setup_all%ROWTYPE;
6 new_references igf_ap_gen_setup_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2 ,
10 x_rowid IN VARCHAR2 ,
11 x_genst_id IN NUMBER ,
12 x_ssn_required IN VARCHAR2 ,
13 x_auto_na_complete 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 : kkillams
22 || Created On : 29-MAY-2001
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 IGF_AP_GEN_SETUP_ALL
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.genst_id := x_genst_id;
54 new_references.ssn_required := x_ssn_required;
55 new_references.auto_na_complete := x_auto_na_complete;
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 FUNCTION get_pk_for_validation (
73 x_genst_id IN NUMBER
74 ) RETURN BOOLEAN AS
75 /*
76 || Created By : kkillams
77 || Created On : 29-MAY-2001
78 || Purpose : Validates the Primary Key of the table.
79 || Known limitations, enhancements or remarks :
80 || Change History :
81 || Who When What
82 || (reverse chronological order - newest change first)
83 */
84 CURSOR cur_rowid IS
85 SELECT rowid
86 FROM igf_ap_gen_setup_all
87 WHERE genst_id = x_genst_id
88 FOR UPDATE NOWAIT;
89
90 lv_rowid cur_rowid%RowType;
91
92 BEGIN
93
94 OPEN cur_rowid;
95 FETCH cur_rowid INTO lv_rowid;
96 IF (cur_rowid%FOUND) THEN
97 CLOSE cur_rowid;
98 RETURN(TRUE);
99 ELSE
100 CLOSE cur_rowid;
101 RETURN(FALSE);
102 END IF;
103
104 END get_pk_for_validation;
105
106
107 PROCEDURE before_dml (
108 p_action IN VARCHAR2 ,
109 x_rowid IN VARCHAR2 ,
110 x_genst_id IN NUMBER ,
111 x_ssn_required IN VARCHAR2 ,
112 x_auto_na_complete IN VARCHAR2 ,
113 x_creation_date IN DATE ,
114 x_created_by IN NUMBER ,
115 x_last_update_date IN DATE ,
116 x_last_updated_by IN NUMBER ,
117 x_last_update_login IN NUMBER
118 ) AS
119 /*
120 || Created By : kkillams
121 || Created On : 29-MAY-2001
122 || Purpose : Initialises the columns, Checks Constraints, Calls the
123 || Trigger Handlers for the table, before any DML operation.
124 || Known limitations, enhancements or remarks :
125 || Change History :
126 || Who When What
127 || (reverse chronological order - newest change first)
128 */
129 BEGIN
130
131 set_column_values (
132 p_action,
133 x_rowid,
134 x_genst_id,
135 x_ssn_required,
136 x_auto_na_complete,
137 x_creation_date,
138 x_created_by,
139 x_last_update_date,
140 x_last_updated_by,
141 x_last_update_login
142 );
143
144 IF (p_action = 'INSERT') THEN
145 -- Call all the procedures related to Before Insert.
146 IF ( get_pk_for_validation(
147 new_references.genst_id
148 )
149 ) THEN
150 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
151 igs_ge_msg_stack.add;
152 app_exception.raise_exception;
153 END IF;
154 ELSIF (p_action = 'VALIDATE_INSERT') THEN
155 -- Call all the procedures related to Before Insert.
156 IF ( get_pk_for_validation (
157 new_references.genst_id
158 )
159 ) THEN
160 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
161 igs_ge_msg_stack.add;
162 app_exception.raise_exception;
163 END IF;
164 END IF;
165
166 END before_dml;
167
168
169 PROCEDURE insert_row (
170 x_rowid IN OUT NOCOPY VARCHAR2,
171 x_genst_id IN OUT NOCOPY NUMBER,
172 x_ssn_required IN VARCHAR2,
173 x_auto_na_complete IN VARCHAR2,
174 x_mode IN VARCHAR2
175 ) AS
176 /*
177 || Created By : kkillams
178 || Created On : 29-MAY-2001
179 || Purpose : Handles the INSERT DML logic for the table.
180 || Known limitations, enhancements or remarks :
181 || Change History :
182 || Who When What
183 || (reverse chronological order - newest change first)
184 */
185 CURSOR c IS
186 SELECT rowid
187 FROM igf_ap_gen_setup_all
188 WHERE genst_id = x_genst_id;
189
190 x_last_update_date DATE;
191 x_last_updated_by NUMBER;
192 x_last_update_login NUMBER;
193
194 BEGIN
195
196 x_last_update_date := SYSDATE;
197 IF (x_mode = 'I') THEN
198 x_last_updated_by := 1;
199 x_last_update_login := 0;
200 ELSIF (x_mode = 'R') THEN
201 x_last_updated_by := fnd_global.user_id;
202 IF (x_last_updated_by IS NULL) THEN
203 x_last_updated_by := -1;
204 END IF;
205 x_last_update_login := fnd_global.login_id;
206 IF (x_last_update_login IS NULL) THEN
207 x_last_update_login := -1;
208 END IF;
209 ELSE
210 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
211 igs_ge_msg_stack.add;
212 app_exception.raise_exception;
213 END IF;
214
215 SELECT igf_ap_gen_setup_all_s.NEXTVAL
216 INTO x_genst_id
217 FROM dual;
218
219 new_references.org_id := igs_ge_gen_003.get_org_id;
220
221 before_dml(
222 p_action => 'INSERT',
223 x_rowid => x_rowid,
224 x_genst_id => x_genst_id,
225 x_ssn_required => x_ssn_required,
226 x_auto_na_complete => x_auto_na_complete,
227 x_creation_date => x_last_update_date,
228 x_created_by => x_last_updated_by,
229 x_last_update_date => x_last_update_date,
230 x_last_updated_by => x_last_updated_by,
231 x_last_update_login => x_last_update_login
232 );
233
234 INSERT INTO igf_ap_gen_setup_all (
235 genst_id,
236 org_id,
237 ssn_required,
238 auto_na_complete,
239 creation_date,
240 created_by,
241 last_update_date,
242 last_updated_by,
243 last_update_login
244 ) VALUES (
245 new_references.genst_id,
246 new_references.org_id,
247 new_references.ssn_required,
248 new_references.auto_na_complete,
249 x_last_update_date,
250 x_last_updated_by,
251 x_last_update_date,
252 x_last_updated_by,
253 x_last_update_login
254 );
255
256 OPEN c;
257 FETCH c INTO x_rowid;
258 IF (c%NOTFOUND) THEN
259 CLOSE c;
260 RAISE NO_DATA_FOUND;
261 END IF;
262 CLOSE c;
263
264 END insert_row;
265
266
267 PROCEDURE lock_row (
268 x_rowid IN VARCHAR2,
269 x_genst_id IN NUMBER,
270 x_ssn_required IN VARCHAR2,
271 x_auto_na_complete IN VARCHAR2
272 ) AS
273 /*
274 || Created By : kkillams
275 || Created On : 29-MAY-2001
276 || Purpose : Handles the LOCK mechanism for the table.
277 || Known limitations, enhancements or remarks :
278 || Change History :
279 || Who When What
280 || (reverse chronological order - newest change first)
281 */
282 CURSOR c1 IS
283 SELECT
284 ssn_required,
285 auto_na_complete
286 FROM igf_ap_gen_setup_all
287 WHERE rowid = x_rowid
288 FOR UPDATE NOWAIT;
289
290 tlinfo c1%ROWTYPE;
291
292 BEGIN
293
294 OPEN c1;
295 FETCH c1 INTO tlinfo;
296 IF (c1%notfound) THEN
297 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
298 igs_ge_msg_stack.add;
299 CLOSE c1;
300 app_exception.raise_exception;
301 RETURN;
302 END IF;
303 CLOSE c1;
304
305 IF (
306 (tlinfo.ssn_required = x_ssn_required)
307 AND (tlinfo.auto_na_complete = x_auto_na_complete)
308 ) THEN
309 NULL;
310 ELSE
311 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
312 igs_ge_msg_stack.add;
313 app_exception.raise_exception;
314 END IF;
315
316 RETURN;
317
318 END lock_row;
319
320
321 PROCEDURE update_row (
322 x_rowid IN VARCHAR2,
323 x_genst_id IN NUMBER,
324 x_ssn_required IN VARCHAR2,
325 x_auto_na_complete IN VARCHAR2,
326 x_mode IN VARCHAR2
327 ) AS
328 /*
329 || Created By : kkillams
330 || Created On : 29-MAY-2001
331 || Purpose : Handles the UPDATE DML logic for the table.
332 || Known limitations, enhancements or remarks :
333 || Change History :
334 || Who When What
335 || (reverse chronological order - newest change first)
336 */
337 x_last_update_date DATE ;
338 x_last_updated_by NUMBER;
339 x_last_update_login NUMBER;
340
341 BEGIN
342
343 x_last_update_date := SYSDATE;
344 IF (X_MODE = 'I') THEN
345 x_last_updated_by := 1;
346 x_last_update_login := 0;
347 ELSIF (x_mode = 'R') THEN
348 x_last_updated_by := fnd_global.user_id;
349 IF x_last_updated_by IS NULL THEN
350 x_last_updated_by := -1;
351 END IF;
352 x_last_update_login := fnd_global.login_id;
353 IF (x_last_update_login IS NULL) THEN
354 x_last_update_login := -1;
355 END IF;
356 ELSE
357 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
358 igs_ge_msg_stack.add;
359 app_exception.raise_exception;
360 END IF;
361
362 before_dml(
363 p_action => 'UPDATE',
364 x_rowid => x_rowid,
365 x_genst_id => x_genst_id,
366 x_ssn_required => x_ssn_required,
367 x_auto_na_complete => x_auto_na_complete,
368 x_creation_date => x_last_update_date,
369 x_created_by => x_last_updated_by,
370 x_last_update_date => x_last_update_date,
371 x_last_updated_by => x_last_updated_by,
372 x_last_update_login => x_last_update_login
373 );
374
375 UPDATE igf_ap_gen_setup_all
376 SET
380 last_updated_by = x_last_updated_by,
377 ssn_required = new_references.ssn_required,
378 auto_na_complete = new_references.auto_na_complete,
379 last_update_date = x_last_update_date,
381 last_update_login = x_last_update_login
382 WHERE rowid = x_rowid;
383
384 IF (SQL%NOTFOUND) THEN
385 RAISE NO_DATA_FOUND;
386 END IF;
387
388 END update_row;
389
390
391 PROCEDURE add_row (
392 x_rowid IN OUT NOCOPY VARCHAR2,
393 x_genst_id IN OUT NOCOPY NUMBER,
394 x_ssn_required IN VARCHAR2,
395 x_auto_na_complete IN VARCHAR2,
396 x_mode IN VARCHAR2
397 ) AS
398 /*
399 || Created By : kkillams
400 || Created On : 29-MAY-2001
401 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
402 || Known limitations, enhancements or remarks :
403 || Change History :
404 || Who When What
405 || (reverse chronological order - newest change first)
406 */
407 CURSOR c1 IS
408 SELECT rowid
409 FROM igf_ap_gen_setup_all
410 WHERE genst_id = x_genst_id;
411
412 BEGIN
413
414 OPEN c1;
415 FETCH c1 INTO x_rowid;
416 IF (c1%NOTFOUND) THEN
417 CLOSE c1;
418
419 insert_row (
420 x_rowid,
421 x_genst_id,
422 x_ssn_required,
423 x_auto_na_complete,
424 x_mode
425 );
426 RETURN;
427 END IF;
428 CLOSE c1;
429
430 update_row (
431 x_rowid,
432 x_genst_id,
433 x_ssn_required,
434 x_auto_na_complete,
435 x_mode
436 );
437
438 END add_row;
439
440
441 PROCEDURE delete_row (
442 x_rowid IN VARCHAR2
443 ) AS
444 /*
445 || Created By : kkillams
446 || Created On : 29-MAY-2001
447 || Purpose : Handles the DELETE DML logic for the table.
448 || Known limitations, enhancements or remarks :
449 || Change History :
450 || Who When What
451 || (reverse chronological order - newest change first)
452 */
453 BEGIN
454
455 before_dml (
456 p_action => 'DELETE',
457 x_rowid => x_rowid
458 );
459
460 DELETE FROM igf_ap_gen_setup_all
461 WHERE rowid = x_rowid;
462
463 IF (SQL%NOTFOUND) THEN
464 RAISE NO_DATA_FOUND;
465 END IF;
466
467 END delete_row;
468
469
470 END igf_ap_gen_setup_pkg;
[Home] [Help]