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