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