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