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