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