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