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