1 PACKAGE BODY igs_he_code_assoc_pkg AS
2 /* $Header: IGSWI01B.pls 120.1 2006/02/06 20:39:50 anwest noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_he_code_assoc%ROWTYPE;
6 new_references igs_he_code_assoc%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_association_code IN VARCHAR2 ,
12 x_description IN VARCHAR2 ,
13 x_display_title IN VARCHAR2 ,
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 : rgopalan
22 || Created On : 15-JUN-2001
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_HE_CODE_ASSOC
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.association_code := x_association_code;
54 new_references.description := x_description;
55 new_references.display_title := x_display_title;
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 PROCEDURE check_child_existance IS
73 /*
74 || Created By : rgopalan
75 || Created On : 15-JUN-2001
76 || Purpose : Checks for the existance of Child records.
77 || Known limitations, enhancements or remarks :
78 || Change History :
79 || Who When What
80 || (reverse chronological order - newest change first)
81 || ANWEST 09-JAN-06 Changes as per HE305
82 */
83 BEGIN
84
85 igs_he_code_ass_val_pkg.get_fk_igs_he_code_assoc (
86 old_references.association_code
87 );
88
89 igs_he_code_map_val_pkg.get_fk_igs_he_code_assoc (
90 old_references.association_code
91 );
92
93 igs_he_sys_rt_cl_ass_pkg.get_fk_igs_he_code_assoc (
94 old_references.association_code
95 );
96
97 END check_child_existance;
98
99
100 FUNCTION get_pk_for_validation (
101 x_association_code IN VARCHAR2
102 ) RETURN BOOLEAN AS
103 /*
104 || Created By : rgopalan
105 || Created On : 15-JUN-2001
106 || Purpose : Validates the Primary Key of the table.
107 || Known limitations, enhancements or remarks :
108 || Change History :
109 || Who When What
110 || (reverse chronological order - newest change first)
111 */
112 CURSOR cur_rowid IS
113 SELECT rowid
114 FROM igs_he_code_assoc
115 WHERE association_code = x_association_code
116 FOR UPDATE NOWAIT;
117
118 lv_rowid cur_rowid%RowType;
119
120 BEGIN
121
122 OPEN cur_rowid;
123 FETCH cur_rowid INTO lv_rowid;
124 IF (cur_rowid%FOUND) THEN
125 CLOSE cur_rowid;
126 RETURN(TRUE);
127 ELSE
128 CLOSE cur_rowid;
129 RETURN(FALSE);
130 END IF;
131
132 END get_pk_for_validation;
133
134
135 PROCEDURE before_dml (
136 p_action IN VARCHAR2,
137 x_rowid IN VARCHAR2 ,
138 x_association_code IN VARCHAR2 ,
139 x_description IN VARCHAR2 ,
140 x_display_title IN VARCHAR2 ,
141 x_creation_date IN DATE ,
142 x_created_by IN NUMBER ,
143 x_last_update_date IN DATE ,
144 x_last_updated_by IN NUMBER ,
145 x_last_update_login IN NUMBER
146 ) AS
147 /*
148 || Created By : rgopalan
149 || Created On : 15-JUN-2001
150 || Purpose : Initialises the columns, Checks Constraints, Calls the
151 || Trigger Handlers for the table, before any DML operation.
152 || Known limitations, enhancements or remarks :
153 || Change History :
154 || Who When What
155 || (reverse chronological order - newest change first)
156 */
157 BEGIN
158
159 set_column_values (
160 p_action,
161 x_rowid,
162 x_association_code,
163 x_description,
164 x_display_title,
165 x_creation_date,
166 x_created_by,
167 x_last_update_date,
168 x_last_updated_by,
169 x_last_update_login
170 );
171
172 IF (p_action = 'INSERT') THEN
173 -- Call all the procedures related to Before Insert.
174 IF ( get_pk_for_validation(
175 new_references.association_code
176 )
177 ) THEN
178 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
179 igs_ge_msg_stack.add;
180 app_exception.raise_exception;
181 END IF;
182 ELSIF (p_action = 'DELETE') THEN
183 -- Call all the procedures related to Before Delete.
184 check_child_existance;
185 ELSIF (p_action = 'VALIDATE_INSERT') THEN
186 -- Call all the procedures related to Before Insert.
187 IF ( get_pk_for_validation (
188 new_references.association_code
189 )
190 ) THEN
191 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
192 igs_ge_msg_stack.add;
193 app_exception.raise_exception;
194 END IF;
195 ELSIF (p_action = 'VALIDATE_DELETE') THEN
196 check_child_existance;
197 END IF;
198
199 END before_dml;
200
201
202 PROCEDURE insert_row (
203 x_rowid IN OUT NOCOPY VARCHAR2,
204 x_association_code IN OUT NOCOPY VARCHAR2,
205 x_description IN VARCHAR2,
206 x_display_title IN VARCHAR2,
207 x_mode IN VARCHAR2
208 ) AS
209 /*
210 || Created By : rgopalan
211 || Created On : 15-JUN-2001
212 || Purpose : Handles the INSERT DML logic for the table.
213 || Known limitations, enhancements or remarks :
214 || Change History :
215 || Who When What
216 || (reverse chronological order - newest change first)
217 */
218 CURSOR c IS
219 SELECT rowid
220 FROM igs_he_code_assoc
221 WHERE association_code = x_association_code;
222
223 x_last_update_date DATE;
224 x_last_updated_by NUMBER;
225 x_last_update_login NUMBER;
226
227 BEGIN
228
229 x_last_update_date := SYSDATE;
230 IF (x_mode = 'I') THEN
231 x_last_updated_by := 1;
232 x_last_update_login := 0;
233 ELSIF (x_mode = 'R') THEN
234 x_last_updated_by := fnd_global.user_id;
235 IF (x_last_updated_by IS NULL) THEN
236 x_last_updated_by := -1;
237 END IF;
238 x_last_update_login := fnd_global.login_id;
239 IF (x_last_update_login IS NULL) THEN
240 x_last_update_login := -1;
241 END IF;
242 ELSE
243 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
244 igs_ge_msg_stack.add;
245 app_exception.raise_exception;
246 END IF;
247
248 before_dml(
249 p_action => 'INSERT',
250 x_rowid => x_rowid,
251 x_association_code => x_association_code,
252 x_description => x_description,
253 x_display_title => x_display_title,
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_he_code_assoc (
262 association_code,
263 description,
264 display_title,
265 creation_date,
266 created_by,
267 last_update_date,
268 last_updated_by,
269 last_update_login
270 ) VALUES (
271 new_references.association_code,
272 new_references.description,
273 new_references.display_title,
274 x_last_update_date,
275 x_last_updated_by,
276 x_last_update_date,
277 x_last_updated_by,
278 x_last_update_login
279 );
280
281 OPEN c;
282 FETCH c INTO x_rowid;
283 IF (c%NOTFOUND) THEN
284 CLOSE c;
285 RAISE NO_DATA_FOUND;
286 END IF;
287 CLOSE c;
288
289 END insert_row;
290
291
292 PROCEDURE lock_row (
293 x_rowid IN VARCHAR2,
294 x_association_code IN VARCHAR2,
295 x_description IN VARCHAR2,
296 x_display_title IN VARCHAR2
297 ) AS
298 /*
299 || Created By : rgopalan
300 || Created On : 15-JUN-2001
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 description,
310 display_title
311 FROM igs_he_code_assoc
312 WHERE rowid = x_rowid
313 FOR UPDATE NOWAIT;
314
315 tlinfo c1%ROWTYPE;
316
317 BEGIN
318
319 OPEN c1;
320 FETCH c1 INTO tlinfo;
321 IF (c1%notfound) THEN
322 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
323 igs_ge_msg_stack.add;
324 CLOSE c1;
325 app_exception.raise_exception;
326 RETURN;
327 END IF;
328 CLOSE c1;
329
330 IF (
331 (tlinfo.description = x_description)
332 AND (tlinfo.display_title = x_display_title)
333 ) THEN
334 NULL;
335 ELSE
336 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
337 igs_ge_msg_stack.add;
338 app_exception.raise_exception;
339 END IF;
340
341 RETURN;
342
343 END lock_row;
344
345
346 PROCEDURE update_row (
347 x_rowid IN VARCHAR2,
348 x_association_code IN VARCHAR2,
349 x_description IN VARCHAR2,
350 x_display_title IN VARCHAR2,
351 x_mode IN VARCHAR2
352 ) AS
353 /*
354 || Created By : rgopalan
355 || Created On : 15-JUN-2001
356 || Purpose : Handles the UPDATE DML logic for the table.
357 || Known limitations, enhancements or remarks :
358 || Change History :
359 || Who When What
360 || (reverse chronological order - newest change first)
361 */
362 x_last_update_date DATE ;
363 x_last_updated_by NUMBER;
364 x_last_update_login NUMBER;
365
366 BEGIN
367
368 x_last_update_date := SYSDATE;
369 IF (X_MODE = 'I') THEN
370 x_last_updated_by := 1;
371 x_last_update_login := 0;
372 ELSIF (x_mode = 'R') THEN
373 x_last_updated_by := fnd_global.user_id;
374 IF x_last_updated_by IS NULL THEN
375 x_last_updated_by := -1;
376 END IF;
377 x_last_update_login := fnd_global.login_id;
378 IF (x_last_update_login IS NULL) THEN
379 x_last_update_login := -1;
380 END IF;
381 ELSE
382 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
383 igs_ge_msg_stack.add;
384 app_exception.raise_exception;
385 END IF;
386
387 before_dml(
388 p_action => 'UPDATE',
389 x_rowid => x_rowid,
390 x_association_code => x_association_code,
391 x_description => x_description,
392 x_display_title => x_display_title,
393 x_creation_date => x_last_update_date,
394 x_created_by => x_last_updated_by,
395 x_last_update_date => x_last_update_date,
396 x_last_updated_by => x_last_updated_by,
397 x_last_update_login => x_last_update_login
398 );
399
400 UPDATE igs_he_code_assoc
401 SET
402 description = new_references.description,
403 display_title = new_references.display_title,
404 last_update_date = x_last_update_date,
405 last_updated_by = x_last_updated_by,
406 last_update_login = x_last_update_login
407 WHERE rowid = x_rowid;
408
409 IF (SQL%NOTFOUND) THEN
410 RAISE NO_DATA_FOUND;
411 END IF;
412
413 END update_row;
414
415
416 PROCEDURE add_row (
417 x_rowid IN OUT NOCOPY VARCHAR2,
418 x_association_code IN OUT NOCOPY VARCHAR2,
419 x_description IN VARCHAR2,
420 x_display_title IN VARCHAR2,
421 x_mode IN VARCHAR2
422 ) AS
423 /*
424 || Created By : rgopalan
425 || Created On : 15-JUN-2001
426 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
427 || Known limitations, enhancements or remarks :
428 || Change History :
429 || Who When What
430 || (reverse chronological order - newest change first)
431 */
432 CURSOR c1 IS
433 SELECT rowid
434 FROM igs_he_code_assoc
435 WHERE association_code = x_association_code;
436
437 BEGIN
438
439 OPEN c1;
440 FETCH c1 INTO x_rowid;
441 IF (c1%NOTFOUND) THEN
442 CLOSE c1;
443
444 insert_row (
445 x_rowid,
446 x_association_code,
447 x_description,
448 x_display_title,
449 x_mode
450 );
451 RETURN;
452 END IF;
453 CLOSE c1;
454
455 update_row (
456 x_rowid,
457 x_association_code,
458 x_description,
459 x_display_title,
460 x_mode
461 );
462
463 END add_row;
464
465
466 PROCEDURE delete_row (
467 x_rowid IN VARCHAR2
468 ) AS
469 /*
470 || Created By : rgopalan
471 || Created On : 15-JUN-2001
472 || Purpose : Handles the DELETE DML logic for the table.
473 || Known limitations, enhancements or remarks :
474 || Change History :
475 || Who When What
476 || (reverse chronological order - newest change first)
477 */
478 BEGIN
479
480 before_dml (
481 p_action => 'DELETE',
482 x_rowid => x_rowid
483 );
484
485 DELETE FROM igs_he_code_assoc
486 WHERE rowid = x_rowid;
487
488 IF (SQL%NOTFOUND) THEN
489 RAISE NO_DATA_FOUND;
490 END IF;
491
492 END delete_row;
493
494
495 END igs_he_code_assoc_pkg;
[Home] [Help]