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