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