1 PACKAGE BODY igs_fi_bill_dpsts_pkg AS
2 /* $Header: IGSSIC5B.pls 115.0 2002/12/05 07:22:11 shtatiko noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_bill_dpsts%ROWTYPE;
6 new_references igs_fi_bill_dpsts%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_bill_id IN NUMBER,
12 x_credit_activity_id IN NUMBER,
13 x_creation_date IN DATE,
14 x_created_by IN NUMBER,
15 x_last_update_date IN DATE,
16 x_last_updated_by IN NUMBER,
17 x_last_update_login IN NUMBER
18 ) AS
19 /*
20 || Created By :
21 || Created On : 04-DEC-2002
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 igs_fi_bill_dpsts
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.bill_id := x_bill_id;
53 new_references.credit_activity_id := x_credit_activity_id;
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 :
73 || Created On : 04-DEC-2002
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.bill_id = new_references.bill_id)) OR
83 ((new_references.bill_id IS NULL))) THEN
84 NULL;
85 ELSIF NOT igs_fi_bill_pkg.get_pk_for_validation (
86 new_references.bill_id
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.credit_activity_id = new_references.credit_activity_id)) OR
94 ((new_references.credit_activity_id IS NULL))) THEN
95 NULL;
96 ELSIF NOT igs_fi_cr_activities_pkg.get_pk_for_validation (
97 new_references.credit_activity_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_bill_id IN NUMBER,
109 x_credit_activity_id IN NUMBER
110 ) RETURN BOOLEAN AS
111 /*
112 || Created By :
113 || Created On : 04-DEC-2002
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 igs_fi_bill_dpsts
123 WHERE bill_id = x_bill_id
124 AND credit_activity_id = x_credit_activity_id
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_fk_igs_fi_bill (
145 x_bill_id IN NUMBER
146 ) AS
147 /*
148 || Created By :
149 || Created On : 04-DEC-2002
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 CURSOR cur_rowid IS
157 SELECT rowid
158 FROM igs_fi_bill_dpsts
159 WHERE ((bill_id = x_bill_id));
160
161 lv_rowid cur_rowid%RowType;
162
163 BEGIN
164
165 OPEN cur_rowid;
166 FETCH cur_rowid INTO lv_rowid;
167 IF (cur_rowid%FOUND) THEN
168 CLOSE cur_rowid;
169 fnd_message.set_name ('IGS', 'IGS_FI_FBDP_FBLLA_FK');
170 igs_ge_msg_stack.add;
171 app_exception.raise_exception;
172 RETURN;
173 END IF;
174 CLOSE cur_rowid;
175
176 END get_fk_igs_fi_bill;
177
178
179 PROCEDURE get_fk_igs_fi_cr_activities (
180 x_credit_activity_id IN NUMBER
181 ) AS
182 /*
183 || Created By :
184 || Created On : 04-DEC-2002
185 || Purpose : Validates the Foreign Keys for the table.
186 || Known limitations, enhancements or remarks :
187 || Change History :
188 || Who When What
189 || (reverse chronological order - newest change first)
190 */
191 CURSOR cur_rowid IS
192 SELECT rowid
193 FROM igs_fi_bill_dpsts
194 WHERE ((credit_activity_id = x_credit_activity_id));
195
196 lv_rowid cur_rowid%RowType;
197
198 BEGIN
199
200 OPEN cur_rowid;
201 FETCH cur_rowid INTO lv_rowid;
202 IF (cur_rowid%FOUND) THEN
203 CLOSE cur_rowid;
204 fnd_message.set_name ('IGS', 'IGS_FI_FBDP_CRAC_FK');
205 igs_ge_msg_stack.add;
206 app_exception.raise_exception;
207 RETURN;
208 END IF;
209 CLOSE cur_rowid;
210
211 END get_fk_igs_fi_cr_activities;
212
213
214 PROCEDURE before_dml (
215 p_action IN VARCHAR2,
216 x_rowid IN VARCHAR2,
217 x_bill_id IN NUMBER,
218 x_credit_activity_id IN NUMBER,
219 x_creation_date IN DATE,
220 x_created_by IN NUMBER,
221 x_last_update_date IN DATE,
222 x_last_updated_by IN NUMBER,
223 x_last_update_login IN NUMBER
224 ) AS
225 /*
226 || Created By :
227 || Created On : 04-DEC-2002
228 || Purpose : Initialises the columns, Checks Constraints, Calls the
229 || Trigger Handlers for the table, before any DML operation.
230 || Known limitations, enhancements or remarks :
231 || Change History :
232 || Who When What
233 || (reverse chronological order - newest change first)
234 */
235 BEGIN
236
237 set_column_values (
238 p_action,
239 x_rowid,
240 x_bill_id,
241 x_credit_activity_id,
242 x_creation_date,
243 x_created_by,
244 x_last_update_date,
245 x_last_updated_by,
246 x_last_update_login
247 );
248
249 IF (p_action = 'INSERT') THEN
250 -- Call all the procedures related to Before Insert.
251 IF ( get_pk_for_validation(
252 new_references.bill_id,
253 new_references.credit_activity_id
254 )
255 ) THEN
256 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
257 igs_ge_msg_stack.add;
258 app_exception.raise_exception;
259 END IF;
260 check_parent_existance;
261 ELSIF (p_action = 'UPDATE') THEN
262 -- Call all the procedures related to Before Update.
263 check_parent_existance;
264 ELSIF (p_action = 'VALIDATE_INSERT') THEN
265 -- Call all the procedures related to Before Insert.
266 IF ( get_pk_for_validation (
267 new_references.bill_id,
268 new_references.credit_activity_id
269 )
270 ) THEN
271 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
272 igs_ge_msg_stack.add;
273 app_exception.raise_exception;
274 END IF;
275 END IF;
276
277 END before_dml;
278
279
280 PROCEDURE insert_row (
281 x_rowid IN OUT NOCOPY VARCHAR2,
282 x_bill_id IN NUMBER,
283 x_credit_activity_id IN NUMBER,
284 x_mode IN VARCHAR2
285 ) AS
286 /*
287 || Created By :
288 || Created On : 04-DEC-2002
289 || Purpose : Handles the INSERT DML logic for the table.
290 || Known limitations, enhancements or remarks :
291 || Change History :
292 || Who When What
293 || (reverse chronological order - newest change first)
294 */
295
296 x_last_update_date DATE;
297 x_last_updated_by NUMBER;
298 x_last_update_login NUMBER;
299 x_request_id NUMBER;
300 x_program_id NUMBER;
301 x_program_application_id NUMBER;
302 x_program_update_date DATE;
303
304 BEGIN
305
306 x_last_update_date := SYSDATE;
307 IF (x_mode = 'I') THEN
308 x_last_updated_by := 1;
309 x_last_update_login := 0;
310 ELSIF (x_mode = 'R') THEN
311 x_last_updated_by := fnd_global.user_id;
312 IF (x_last_updated_by IS NULL) THEN
313 x_last_updated_by := -1;
314 END IF;
315 x_last_update_login := fnd_global.login_id;
316 IF (x_last_update_login IS NULL) THEN
317 x_last_update_login := -1;
318 END IF;
319 x_request_id := fnd_global.conc_request_id;
320 x_program_id := fnd_global.conc_program_id;
321 x_program_application_id := fnd_global.prog_appl_id;
322
323 IF (x_request_id = -1) THEN
324 x_request_id := NULL;
325 x_program_id := NULL;
326 x_program_application_id := NULL;
327 x_program_update_date := NULL;
328 ELSE
329 x_program_update_date := SYSDATE;
330 END IF;
331 ELSE
332 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
333 fnd_message.set_token ('ROUTINE', 'IGS_FI_BILL_DPSTS_PKG.INSERT_ROW');
334 igs_ge_msg_stack.add;
335 app_exception.raise_exception;
336 END IF;
337
338 before_dml(
339 p_action => 'INSERT',
340 x_rowid => x_rowid,
341 x_bill_id => x_bill_id,
342 x_credit_activity_id => x_credit_activity_id,
343 x_creation_date => x_last_update_date,
344 x_created_by => x_last_updated_by,
345 x_last_update_date => x_last_update_date,
346 x_last_updated_by => x_last_updated_by,
347 x_last_update_login => x_last_update_login
348 );
349
350 INSERT INTO igs_fi_bill_dpsts (
351 bill_id,
352 credit_activity_id,
353 creation_date,
354 created_by,
355 last_update_date,
356 last_updated_by,
357 last_update_login,
358 request_id,
359 program_id,
360 program_application_id,
361 program_update_date
362 ) VALUES (
363 new_references.bill_id,
364 new_references.credit_activity_id,
365 x_last_update_date,
366 x_last_updated_by,
367 x_last_update_date,
368 x_last_updated_by,
369 x_last_update_login ,
370 x_request_id,
371 x_program_id,
372 x_program_application_id,
373 x_program_update_date
374 ) RETURNING ROWID INTO x_rowid;
375
376 END insert_row;
377
378
379 PROCEDURE lock_row (
380 x_rowid IN VARCHAR2,
381 x_bill_id IN NUMBER,
382 x_credit_activity_id IN NUMBER
383 ) AS
384 /*
385 || Created By :
386 || Created On : 04-DEC-2002
387 || Purpose : Handles the LOCK mechanism for the table.
388 || Known limitations, enhancements or remarks :
389 || Change History :
390 || Who When What
391 || (reverse chronological order - newest change first)
392 */
393 CURSOR c1 IS
394 SELECT
395 rowid
396 FROM igs_fi_bill_dpsts
397 WHERE rowid = x_rowid
398 FOR UPDATE NOWAIT;
399
400 tlinfo c1%ROWTYPE;
401
402 BEGIN
403
404 OPEN c1;
405 FETCH c1 INTO tlinfo;
406 IF (c1%notfound) THEN
407 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
408 igs_ge_msg_stack.add;
409 CLOSE c1;
410 app_exception.raise_exception;
411 RETURN;
412 END IF;
413 CLOSE c1;
414
415
416 RETURN;
417
418 END lock_row;
419
420
421 PROCEDURE delete_row (
422 x_rowid IN VARCHAR2
423 ) AS
424 /*
425 || Created By :
426 || Created On : 04-DEC-2002
427 || Purpose : Handles the DELETE DML logic for the table.
428 || Known limitations, enhancements or remarks :
429 || Change History :
430 || Who When What
431 || (reverse chronological order - newest change first)
432 */
433 BEGIN
434
435 before_dml (
436 p_action => 'DELETE',
437 x_rowid => x_rowid
438 );
439
440 DELETE FROM igs_fi_bill_dpsts
441 WHERE rowid = x_rowid;
442
443 IF (SQL%NOTFOUND) THEN
444 RAISE NO_DATA_FOUND;
445 END IF;
446
447 END delete_row;
448
449
450 END igs_fi_bill_dpsts_pkg;
[Home] [Help]