[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_SPA_FEE_PRDS_PKG
Source
1 PACKAGE BODY igs_fi_spa_fee_prds_pkg AS
2 /* $Header: IGSSIE6B.pls 120.1 2006/05/25 10:06:24 akandreg noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_spa_fee_prds%ROWTYPE;
6 new_references igs_fi_spa_fee_prds%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_person_id IN NUMBER,
12 x_course_cd IN VARCHAR2,
13 x_fee_cal_type IN VARCHAR2,
14 x_fee_ci_sequence_number IN NUMBER,
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 x_transaction_type IN VARCHAR2
21 ) AS
22 /*
23 || Created By : [email protected]
24 || Created On : 02-JUL-2004
25 || Purpose : Initialises the Old and New references for the columns of the table.
26 || Known limitations, enhancements or remarks :
27 || Change History :
28 || Who When What
29 || akandreg 05-Apr-2006 Bug 5134636.Added a new parameter transaction_type to set_column_values call.
30 */
31
32 CURSOR cur_old_ref_values IS
33 SELECT *
34 FROM igs_fi_spa_fee_prds
35 WHERE rowid = x_rowid;
36
37 BEGIN
38
39 l_rowid := x_rowid;
40
41 -- Code for setting the Old and New Reference Values.
42 -- Populate Old Values.
43 OPEN cur_old_ref_values;
44 FETCH cur_old_ref_values INTO old_references;
45 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
46 CLOSE cur_old_ref_values;
47 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
48 igs_ge_msg_stack.add;
49 app_exception.raise_exception;
50 RETURN;
51 END IF;
52 CLOSE cur_old_ref_values;
53
54 -- Populate New Values.
55 new_references.person_id := x_person_id;
56 new_references.course_cd := x_course_cd;
57 new_references.fee_cal_type := x_fee_cal_type;
58 new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
59
60 IF (p_action = 'UPDATE') THEN
61 new_references.creation_date := old_references.creation_date;
62 new_references.created_by := old_references.created_by;
63 ELSE
64 new_references.creation_date := x_creation_date;
65 new_references.created_by := x_created_by;
66 END IF;
67
68 new_references.last_update_date := x_last_update_date;
69 new_references.last_updated_by := x_last_updated_by;
70 new_references.last_update_login := x_last_update_login;
71 new_references.transaction_type := x_transaction_type;
72
73 END set_column_values;
74
75
76 PROCEDURE check_parent_existance AS
77 /*
78 || Created By : [email protected]
79 || Created On : 02-Jul-2004
80 || Purpose : Checks for the existance of Parent records.
81 || Known limitations, enhancements or remarks :
82 || Change History :
83 || Who When What
84 || (reverse chronological order - newest change first)
85 */
86
87 BEGIN
88
89 IF (((old_references.fee_cal_type = new_references.fee_cal_type) AND
90 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number)) OR
91 ((new_references.fee_cal_type IS NULL) OR
92 (new_references.fee_ci_sequence_number IS NULL))) THEN
93 NULL;
94 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
95 new_references.fee_cal_type,
96 new_references.fee_ci_sequence_number
97 ) THEN
98 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
99 igs_ge_msg_stack.add;
100 app_exception.raise_exception;
101 END IF;
102
103 IF (((old_references.person_id = new_references.person_id) AND
104 (old_references.course_cd = new_references.course_cd)) OR
105 ((new_references.person_id IS NULL) OR
106 (new_references.course_cd IS NULL))) THEN
107 NULL;
108 ELSIF NOT igs_en_stdnt_ps_att_pkg.get_pk_for_validation (
109 new_references.person_id,
110 new_references.course_cd
111 ) THEN
112 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
113 igs_ge_msg_stack.add;
114 app_exception.raise_exception;
115 END IF;
116
117 END check_parent_existance;
118
119
120 FUNCTION get_pk_for_validation (
121 x_person_id IN NUMBER,
122 x_course_cd IN VARCHAR2,
123 x_fee_cal_type IN VARCHAR2,
124 x_fee_ci_sequence_number IN NUMBER,
125 x_transaction_type IN VARCHAR2
126 ) RETURN BOOLEAN AS
127 /*
128 || Created By : [email protected]
129 || Created On : 02-JUL-2004
130 || Purpose : Validates the Primary Key of the table.
131 || Known limitations, enhancements or remarks :
132 || Change History :
133 || Who When What
134 || akandreg 17-May-2006 Bug 5134636 - Added transaction_type column
135 */
136 CURSOR cur_rowid IS
137 SELECT rowid
138 FROM igs_fi_spa_fee_prds
139 WHERE person_id = x_person_id
140 AND course_cd = x_course_cd
141 AND fee_cal_type = x_fee_cal_type
142 AND fee_ci_sequence_number = x_fee_ci_sequence_number
143 AND transaction_type = x_transaction_type
144 FOR UPDATE NOWAIT;
145
146 lv_rowid cur_rowid%RowType;
147
148 BEGIN
149
150 OPEN cur_rowid;
151 FETCH cur_rowid INTO lv_rowid;
152 IF (cur_rowid%FOUND) THEN
153 CLOSE cur_rowid;
154 RETURN(TRUE);
155 ELSE
156 CLOSE cur_rowid;
157 RETURN(FALSE);
158 END IF;
159
160 END get_pk_for_validation;
161
162
163 PROCEDURE before_dml (
164 p_action IN VARCHAR2,
165 x_rowid IN VARCHAR2,
166 x_person_id IN NUMBER,
167 x_course_cd IN VARCHAR2,
168 x_fee_cal_type IN VARCHAR2,
169 x_fee_ci_sequence_number IN NUMBER,
170 x_creation_date IN DATE,
171 x_created_by IN NUMBER,
172 x_last_update_date IN DATE,
173 x_last_updated_by IN NUMBER,
174 x_last_update_login IN NUMBER,
175 x_transaction_type IN VARCHAR2
176 ) AS
177 /*
178 || Created By : [email protected]
179 || Created On : 02-JUL-2004
180 || Purpose : Initialises the columns, Checks Constraints, Calls the
181 || Trigger Handlers for the table, before any DML operation.
182 || Known limitations, enhancements or remarks :
183 || Change History :
184 || Who When What
185 || akandreg 17-May-2006 Bug 5134636 - Added transaction_type to get_pk_for_validation
186 || Added a new parameter transaction_type to before_dml call.
187 */
188 BEGIN
189
190 set_column_values (
191 p_action,
192 x_rowid,
193 x_person_id,
194 x_course_cd,
195 x_fee_cal_type,
196 x_fee_ci_sequence_number,
197 x_creation_date,
198 x_created_by,
199 x_last_update_date,
200 x_last_updated_by,
201 x_last_update_login,
202 x_transaction_type
203 );
204
205 IF (p_action = 'INSERT') THEN
206 -- Call all the procedures related to Before Insert.
207 IF ( get_pk_for_validation(
208 new_references.person_id,
209 new_references.course_cd,
210 new_references.fee_cal_type,
211 new_references.fee_ci_sequence_number,
212 new_references.transaction_type
213 )
214 ) THEN
215 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
216 igs_ge_msg_stack.add;
217 app_exception.raise_exception;
218 END IF;
219 ELSIF (p_action = 'VALIDATE_INSERT') THEN
220 -- Call all the procedures related to Before Insert.
221 IF ( get_pk_for_validation (
222 new_references.person_id,
223 new_references.course_cd,
224 new_references.fee_cal_type,
225 new_references.fee_ci_sequence_number,
226 new_references.transaction_type
227 )
228 ) THEN
229 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
230 igs_ge_msg_stack.add;
231 app_exception.raise_exception;
232 END IF;
233 END IF;
234
235 END before_dml;
236
237
238 PROCEDURE insert_row (
239 x_rowid IN OUT NOCOPY VARCHAR2,
240 x_person_id IN NUMBER,
241 x_course_cd IN VARCHAR2,
242 x_fee_cal_type IN VARCHAR2,
243 x_fee_ci_sequence_number IN NUMBER,
244 x_mode IN VARCHAR2,
245 x_transaction_type IN VARCHAR2
246 ) AS
247 /*
248 || Created By : [email protected]
249 || Created On : 02-JUL-2004
250 || Purpose : Handles the INSERT DML logic for the table.
251 || Known limitations, enhancements or remarks :
252 || Change History :
253 || Who When What
254 || akandreg 17-May-2006 Bug 5134636.Added a new parameter transaction_type to insert_row call.
255 */
256
257 x_last_update_date DATE;
258 x_last_updated_by NUMBER;
259 x_last_update_login NUMBER;
260 x_request_id NUMBER;
261 x_program_id NUMBER;
262 x_program_application_id NUMBER;
263 x_program_update_date DATE;
264
265 BEGIN
266
267 x_last_update_date := SYSDATE;
268 IF (x_mode = 'I') THEN
269 x_last_updated_by := 1;
270 x_last_update_login := 0;
271 ELSIF (x_mode = 'R') THEN
272 x_last_updated_by := fnd_global.user_id;
273 IF (x_last_updated_by IS NULL) THEN
274 x_last_updated_by := -1;
275 END IF;
276 x_last_update_login := fnd_global.login_id;
277 IF (x_last_update_login IS NULL) THEN
278 x_last_update_login := -1;
279 END IF;
280 x_request_id := fnd_global.conc_request_id;
281 x_program_id := fnd_global.conc_program_id;
282 x_program_application_id := fnd_global.prog_appl_id;
283
284 IF (x_request_id = -1) THEN
285 x_request_id := NULL;
286 x_program_id := NULL;
287 x_program_application_id := NULL;
288 x_program_update_date := NULL;
289 ELSE
290 x_program_update_date := SYSDATE;
291 END IF;
292 ELSE
293 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
294 fnd_message.set_token ('ROUTINE', 'IGS_FI_SPA_FEE_PRDS_PKG.INSERT_ROW');
295 igs_ge_msg_stack.add;
296 app_exception.raise_exception;
297 END IF;
298
299 before_dml(
300 p_action => 'INSERT',
301 x_rowid => x_rowid,
302 x_person_id => x_person_id,
303 x_course_cd => x_course_cd,
304 x_fee_cal_type => x_fee_cal_type,
305 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
306 x_creation_date => x_last_update_date,
307 x_created_by => x_last_updated_by,
308 x_last_update_date => x_last_update_date,
309 x_last_updated_by => x_last_updated_by,
310 x_last_update_login => x_last_update_login,
311 x_transaction_type => x_transaction_type
312 );
313
314 INSERT INTO igs_fi_spa_fee_prds (
315 person_id,
316 course_cd,
317 fee_cal_type,
318 fee_ci_sequence_number,
319 creation_date,
320 created_by,
321 last_update_date,
322 last_updated_by,
323 last_update_login,
324 request_id,
325 program_id,
326 program_application_id,
327 program_update_date,
328 transaction_type
329 ) VALUES (
330 new_references.person_id,
331 new_references.course_cd,
332 new_references.fee_cal_type,
333 new_references.fee_ci_sequence_number,
334 x_last_update_date,
335 x_last_updated_by,
336 x_last_update_date,
337 x_last_updated_by,
338 x_last_update_login ,
339 x_request_id,
340 x_program_id,
341 x_program_application_id,
342 x_program_update_date,
343 new_references.transaction_type
344 ) RETURNING ROWID INTO x_rowid;
345
346 END insert_row;
347
348
349 PROCEDURE lock_row (
350 x_rowid IN VARCHAR2,
351 x_person_id IN NUMBER,
352 x_course_cd IN VARCHAR2,
353 x_fee_cal_type IN VARCHAR2,
354 x_fee_ci_sequence_number IN NUMBER,
355 x_transaction_type IN VARCHAR2
356 ) AS
357 /*
358 || Created By : [email protected]
359 || Created On : 02-JUL-2004
360 || Purpose : Handles the LOCK mechanism for the table.
361 || Known limitations, enhancements or remarks :
362 || Change History :
363 || Who When What
364 || akandreg 17-May-2006 Bug 5134636.Added a new parameter transaction_type to lock_row call.
365 */
366 CURSOR c1 IS
367 SELECT
368 rowid
369 FROM igs_fi_spa_fee_prds
370 WHERE rowid = x_rowid
371 FOR UPDATE NOWAIT;
372
373 tlinfo c1%ROWTYPE;
374
375 BEGIN
376
377 OPEN c1;
378 FETCH c1 INTO tlinfo;
379 IF (c1%notfound) THEN
380 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
381 igs_ge_msg_stack.add;
382 CLOSE c1;
383 app_exception.raise_exception;
384 RETURN;
385 END IF;
386 CLOSE c1;
387
388
389 RETURN;
390
391 END lock_row;
392
393
394 PROCEDURE delete_row (
395 x_rowid IN VARCHAR2
396 ) AS
397 /*
398 || Created By : [email protected]
399 || Created On : 02-JUL-2004
400 || Purpose : Handles the DELETE DML logic for the table.
401 || Known limitations, enhancements or remarks :
402 || Change History :
403 || Who When What
404 || (reverse chronological order - newest change first)
405 */
406 BEGIN
407
408 before_dml (
409 p_action => 'DELETE',
410 x_rowid => x_rowid
411 );
412
413 DELETE FROM igs_fi_spa_fee_prds
414 WHERE rowid = x_rowid;
415
416 IF (SQL%NOTFOUND) THEN
417 RAISE NO_DATA_FOUND;
418 END IF;
419
420 END delete_row;
421
422
423 END igs_fi_spa_fee_prds_pkg;