[Home] [Help]
PACKAGE BODY: APPS.IGS_HE_EXT_RUN_DTLS_PKG
Source
1 PACKAGE BODY igs_he_ext_run_dtls_pkg AS
2 /* $Header: IGSWI06B.pls 115.9 2002/12/20 08:46:30 bayadav noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_he_ext_run_dtls%ROWTYPE;
6 new_references igs_he_ext_run_dtls%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_extract_run_id IN NUMBER ,
12 x_submission_name IN VARCHAR2 ,
13 x_user_return_subclass IN VARCHAR2 ,
14 x_return_name IN VARCHAR2 ,
15 x_extract_phase IN VARCHAR2 ,
16 x_conc_request_id IN NUMBER ,
17 x_conc_request_status IN VARCHAR2 ,
18 x_extract_run_date IN DATE ,
19 x_file_name IN VARCHAR2 ,
20 x_file_location IN VARCHAR2 ,
21 x_date_file_sent IN DATE ,
22 x_extract_override IN VARCHAR2 ,
23 x_validation_kit_result IN VARCHAR2 ,
24 x_hesa_validation_result IN VARCHAR2 ,
25 x_student_ext_run_id IN VARCHAR2 ,
26 x_creation_date IN DATE ,
27 x_created_by IN NUMBER ,
28 x_last_update_date IN DATE ,
29 x_last_updated_by IN NUMBER ,
30 x_last_update_login IN NUMBER
31 ) AS
32 /*
33 || Created By : rgopalan
34 || Created On : 23-JAN-2002
35 || Purpose : Initialises the Old and New references for the columns of the table.
36 || Known limitations, enhancements or remarks :
37 || Change History :
38 || Who When What
39 || (reverse chronological order - newest change first)
40 */
41
42 CURSOR cur_old_ref_values IS
43 SELECT *
44 FROM IGS_HE_EXT_RUN_DTLS
45 WHERE rowid = x_rowid;
46
47 BEGIN
48
49 l_rowid := x_rowid;
50
51 -- Code for setting the Old and New Reference Values.
52 -- Populate Old Values.
53 OPEN cur_old_ref_values;
54 FETCH cur_old_ref_values INTO old_references;
55 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
56 CLOSE cur_old_ref_values;
57 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
58 igs_ge_msg_stack.add;
59 app_exception.raise_exception;
60 RETURN;
61 END IF;
62 CLOSE cur_old_ref_values;
63
64 -- Populate New Values.
65 new_references.extract_run_id := x_extract_run_id;
66 new_references.submission_name := x_submission_name;
67 new_references.user_return_subclass := x_user_return_subclass;
68 new_references.return_name := x_return_name;
69 new_references.extract_phase := x_extract_phase;
70 new_references.conc_request_id := x_conc_request_id;
71 new_references.conc_request_status := x_conc_request_status;
72 new_references.extract_run_date := x_extract_run_date;
73 new_references.file_name := x_file_name;
74 new_references.file_location := x_file_location;
75 new_references.date_file_sent := x_date_file_sent;
76 new_references.extract_override := x_extract_override;
77 new_references.validation_kit_result := x_validation_kit_result;
78 new_references.hesa_validation_result := x_hesa_validation_result;
79 new_references.student_ext_run_id := x_student_ext_run_id;
80
81 IF (p_action = 'UPDATE') THEN
82 new_references.creation_date := old_references.creation_date;
83 new_references.created_by := old_references.created_by;
84 ELSE
85 new_references.creation_date := x_creation_date;
86 new_references.created_by := x_created_by;
87 END IF;
88
89 new_references.last_update_date := x_last_update_date;
90 new_references.last_updated_by := x_last_updated_by;
91 new_references.last_update_login := x_last_update_login;
92
93 END set_column_values;
94
95
96 PROCEDURE check_parent_existance AS
97 /*
98 || Created By : rgopalan
99 || Created On : 23-JAN-2002
100 || Purpose : Checks for the existance of Parent records.
101 || Known limitations, enhancements or remarks :
102 || Change History :
103 || Who When What
104 || (reverse chronological order - newest change first)
105 */
106 BEGIN
107
108 IF (((old_references.submission_name = new_references.submission_name) AND
109 (old_references.user_return_subclass = new_references.user_return_subclass) AND
110 (old_references.return_name = new_references.return_name)) OR
111 ((new_references.submission_name IS NULL) OR
112 (new_references.user_return_subclass IS NULL) OR
113 (new_references.return_name IS NULL))) THEN
114 NULL;
115 ELSIF NOT igs_he_submsn_return_pkg.get_pk_for_validation (
116 new_references.submission_name,
117 new_references.user_return_subclass,
118 new_references.return_name
119 ) THEN
120 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
121 igs_ge_msg_stack.add;
122 app_exception.raise_exception;
123 END IF;
124
125 END check_parent_existance;
126
127
128 PROCEDURE check_child_existance IS
129 /*
130 || Created By : rgopalan
131 || Created On : 23-JAN-2002
132 || Purpose : Checks for the existance of Child records.
133 || Known limitations, enhancements or remarks :
134 || Change History :
135 || Who When What
136 || (reverse chronological order - newest change first)
137 */
138 BEGIN
139
140 igs_he_ext_run_prms_pkg.get_fk_igs_he_ext_run_dtls (
141 old_references.extract_run_id
142 );
143
144 igs_he_ex_rn_dat_ln_pkg.get_fk_igs_he_ext_run_dtls (
145 old_references.extract_run_id
146 );
147
148 END check_child_existance;
149
150
151 FUNCTION get_pk_for_validation (
152 x_extract_run_id IN NUMBER
153 ) RETURN BOOLEAN AS
154 /*
155 || Created By : rgopalan
156 || Created On : 23-JAN-2002
157 || Purpose : Validates the Primary Key of the table.
158 || Known limitations, enhancements or remarks :
159 || Change History :
160 || Who When What
161 || (reverse chronological order - newest change first)
162 */
163 CURSOR cur_rowid IS
164 SELECT rowid
165 FROM igs_he_ext_run_dtls
166 WHERE extract_run_id = x_extract_run_id
167 FOR UPDATE NOWAIT;
168
169 lv_rowid cur_rowid%RowType;
170
171 BEGIN
172
173 OPEN cur_rowid;
174 FETCH cur_rowid INTO lv_rowid;
175 IF (cur_rowid%FOUND) THEN
176 CLOSE cur_rowid;
177 RETURN(TRUE);
178 ELSE
179 CLOSE cur_rowid;
180 RETURN(FALSE);
181 END IF;
182
183 END get_pk_for_validation;
184
185
186 PROCEDURE get_fk_igs_he_submsn_return (
187 x_submission_name IN VARCHAR2,
188 x_user_return_subclass IN VARCHAR2,
189 x_return_name IN VARCHAR2
190 ) AS
191 /*
192 || Created By : rgopalan
193 || Created On : 23-JAN-2002
194 || Purpose : Validates the Foreign Keys for the table.
195 || Known limitations, enhancements or remarks :
196 || Change History :
197 || Who When What
198 || (reverse chronological order - newest change first)
199 */
200 CURSOR cur_rowid IS
201 SELECT rowid
202 FROM igs_he_ext_run_dtls
203 WHERE ((return_name = x_return_name) AND
204 (submission_name = x_submission_name) AND
205 (user_return_subclass = x_user_return_subclass));
206
207 lv_rowid cur_rowid%RowType;
208
209 BEGIN
210
211 OPEN cur_rowid;
212 FETCH cur_rowid INTO lv_rowid;
213 IF (cur_rowid%FOUND) THEN
214 CLOSE cur_rowid;
215 fnd_message.set_name ('IGS', 'IGS_HE_HEERDTL_HESBRET_FK');
216 igs_ge_msg_stack.add;
217 app_exception.raise_exception;
218 RETURN;
219 END IF;
220 CLOSE cur_rowid;
221
222 END get_fk_igs_he_submsn_return;
223
224
225 PROCEDURE before_dml (
226 p_action IN VARCHAR2,
227 x_rowid IN VARCHAR2 ,
228 x_extract_run_id IN NUMBER ,
229 x_submission_name IN VARCHAR2 ,
230 x_user_return_subclass IN VARCHAR2 ,
231 x_return_name IN VARCHAR2 ,
232 x_extract_phase IN VARCHAR2 ,
233 x_conc_request_id IN NUMBER ,
234 x_conc_request_status IN VARCHAR2 ,
235 x_extract_run_date IN DATE ,
236 x_file_name IN VARCHAR2 ,
237 x_file_location IN VARCHAR2 ,
238 x_date_file_sent IN DATE ,
239 x_extract_override IN VARCHAR2 ,
240 x_validation_kit_result IN VARCHAR2 ,
241 x_hesa_validation_result IN VARCHAR2 ,
242 x_student_ext_run_id IN VARCHAR2 ,
243 x_creation_date IN DATE ,
244 x_created_by IN NUMBER ,
245 x_last_update_date IN DATE ,
246 x_last_updated_by IN NUMBER ,
247 x_last_update_login IN NUMBER
248 ) AS
249 /*
250 || Created By : rgopalan
251 || Created On : 23-JAN-2002
252 || Purpose : Initialises the columns, Checks Constraints, Calls the
253 || Trigger Handlers for the table, before any DML operation.
254 || Known limitations, enhancements or remarks :
255 || Change History :
256 || Who When What
257 || (reverse chronological order - newest change first)
258 */
259 BEGIN
260
261 set_column_values (
262 p_action,
263 x_rowid,
264 x_extract_run_id,
265 x_submission_name,
266 x_user_return_subclass,
267 x_return_name,
268 x_extract_phase,
269 x_conc_request_id,
270 x_conc_request_status,
271 x_extract_run_date,
272 x_file_name,
273 x_file_location,
274 x_date_file_sent,
275 x_extract_override,
276 x_validation_kit_result,
277 x_hesa_validation_result,
278 x_student_ext_run_id,
279 x_creation_date,
280 x_created_by,
281 x_last_update_date,
282 x_last_updated_by,
283 x_last_update_login
284 );
285
286 IF (p_action = 'INSERT') THEN
287 -- Call all the procedures related to Before Insert.
288 IF ( get_pk_for_validation(
289 new_references.extract_run_id
290 )
291 ) THEN
292 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
293 igs_ge_msg_stack.add;
294 app_exception.raise_exception;
295 END IF;
296 check_parent_existance;
297 ELSIF (p_action = 'UPDATE') THEN
298 -- Call all the procedures related to Before Update.
299 check_parent_existance;
300 ELSIF (p_action = 'DELETE') THEN
301 -- Call all the procedures related to Before Delete.
302 check_child_existance;
303 ELSIF (p_action = 'VALIDATE_INSERT') THEN
304 -- Call all the procedures related to Before Insert.
305 IF ( get_pk_for_validation (
306 new_references.extract_run_id
307 )
308 ) THEN
309 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
310 igs_ge_msg_stack.add;
311 app_exception.raise_exception;
312 END IF;
313 ELSIF (p_action = 'VALIDATE_DELETE') THEN
314 check_child_existance;
315 END IF;
316
317 END before_dml;
318
319
320 PROCEDURE insert_row (
321 x_rowid IN OUT NOCOPY VARCHAR2,
322 x_extract_run_id IN OUT NOCOPY NUMBER,
323 x_submission_name IN VARCHAR2,
324 x_user_return_subclass IN VARCHAR2,
325 x_return_name IN VARCHAR2,
326 x_extract_phase IN VARCHAR2,
327 x_conc_request_id IN NUMBER,
328 x_conc_request_status IN VARCHAR2,
329 x_extract_run_date IN DATE,
330 x_file_name IN VARCHAR2,
331 x_file_location IN VARCHAR2,
332 x_date_file_sent IN DATE,
333 x_extract_override IN VARCHAR2,
334 x_validation_kit_result IN VARCHAR2,
335 x_hesa_validation_result IN VARCHAR2,
336 x_student_ext_run_id IN VARCHAR2,
337 x_mode IN VARCHAR2
338 ) AS
339 /*
340 || Created By : rgopalan
341 || Created On : 23-JAN-2002
342 || Purpose : Handles the INSERT DML logic for the table.
343 || Known limitations, enhancements or remarks :
344 || Change History :
345 || Who When What
346 || (reverse chronological order - newest change first)
347 */
348 CURSOR c IS
349 SELECT rowid
350 FROM igs_he_ext_run_dtls
351 WHERE extract_run_id = x_extract_run_id;
352
353 x_last_update_date DATE;
354 x_last_updated_by NUMBER;
355 x_last_update_login NUMBER;
356
357 BEGIN
358
359 x_last_update_date := SYSDATE;
360 IF (x_mode = 'I') THEN
361 x_last_updated_by := 1;
362 x_last_update_login := 0;
363 ELSIF (x_mode = 'R') THEN
364 x_last_updated_by := fnd_global.user_id;
365 IF (x_last_updated_by IS NULL) THEN
366 x_last_updated_by := -1;
367 END IF;
368 x_last_update_login := fnd_global.login_id;
369 IF (x_last_update_login IS NULL) THEN
370 x_last_update_login := -1;
371 END IF;
372 ELSE
373 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
374 igs_ge_msg_stack.add;
375 app_exception.raise_exception;
376 END IF;
377
378 SELECT igs_he_ext_run_dtls_run_id_s.NEXTVAL
379 INTO x_extract_run_id
380 FROM dual;
381
382 before_dml(
383 p_action => 'INSERT',
384 x_rowid => x_rowid,
385 x_extract_run_id => x_extract_run_id,
386 x_submission_name => x_submission_name,
387 x_user_return_subclass => x_user_return_subclass,
388 x_return_name => x_return_name,
389 x_extract_phase => x_extract_phase,
390 x_conc_request_id => x_conc_request_id,
391 x_conc_request_status => x_conc_request_status,
392 x_extract_run_date => x_extract_run_date,
393 x_file_name => x_file_name,
394 x_file_location => x_file_location,
395 x_date_file_sent => x_date_file_sent,
396 x_extract_override => x_extract_override,
397 x_validation_kit_result => x_validation_kit_result,
398 x_hesa_validation_result => x_hesa_validation_result,
399 x_student_ext_run_id => x_student_ext_run_id,
400 x_creation_date => x_last_update_date,
401 x_created_by => x_last_updated_by,
402 x_last_update_date => x_last_update_date,
403 x_last_updated_by => x_last_updated_by,
404 x_last_update_login => x_last_update_login
405 );
406
407 INSERT INTO igs_he_ext_run_dtls (
408 extract_run_id,
409 submission_name,
410 user_return_subclass,
411 return_name,
412 extract_phase,
413 conc_request_id,
414 conc_request_status,
415 extract_run_date,
416 file_name,
417 file_location,
418 date_file_sent,
419 extract_override,
420 validation_kit_result,
421 hesa_validation_result,
422 student_ext_run_id,
423 creation_date,
424 created_by,
425 last_update_date,
426 last_updated_by,
427 last_update_login
428 ) VALUES (
429 new_references.extract_run_id,
430 new_references.submission_name,
431 new_references.user_return_subclass,
432 new_references.return_name,
433 new_references.extract_phase,
434 new_references.conc_request_id,
435 new_references.conc_request_status,
436 new_references.extract_run_date,
437 new_references.file_name,
438 new_references.file_location,
439 new_references.date_file_sent,
440 new_references.extract_override,
441 new_references.validation_kit_result,
442 new_references.hesa_validation_result,
443 new_references.student_ext_run_id,
444 x_last_update_date,
445 x_last_updated_by,
446 x_last_update_date,
447 x_last_updated_by,
448 x_last_update_login
449 );
450
451 OPEN c;
452 FETCH c INTO x_rowid;
453 IF (c%NOTFOUND) THEN
454 CLOSE c;
455 RAISE NO_DATA_FOUND;
456 END IF;
457 CLOSE c;
458
459 END insert_row;
460
461
462 PROCEDURE lock_row (
463 x_rowid IN VARCHAR2,
464 x_extract_run_id IN NUMBER,
465 x_submission_name IN VARCHAR2,
466 x_user_return_subclass IN VARCHAR2,
467 x_return_name IN VARCHAR2,
468 x_extract_phase IN VARCHAR2,
469 x_conc_request_id IN NUMBER,
470 x_conc_request_status IN VARCHAR2,
471 x_extract_run_date IN DATE,
472 x_file_name IN VARCHAR2,
473 x_file_location IN VARCHAR2,
474 x_date_file_sent IN DATE,
475 x_extract_override IN VARCHAR2,
476 x_validation_kit_result IN VARCHAR2,
477 x_hesa_validation_result IN VARCHAR2,
478 x_student_ext_run_id IN VARCHAR2
479 ) AS
480 /*
481 || Created By : rgopalan
482 || Created On : 23-JAN-2002
483 || Purpose : Handles the LOCK mechanism for the table.
484 || Known limitations, enhancements or remarks :
485 || Change History :
486 || Who When What
487 || (reverse chronological order - newest change first)
488 */
489 CURSOR c1 IS
490 SELECT
491 submission_name,
492 user_return_subclass,
493 return_name,
494 extract_phase,
495 conc_request_id,
496 conc_request_status,
497 extract_run_date,
498 file_name,
499 file_location,
500 date_file_sent,
501 extract_override,
502 validation_kit_result,
503 hesa_validation_result,
504 student_ext_run_id
505 FROM igs_he_ext_run_dtls
506 WHERE rowid = x_rowid
507 FOR UPDATE NOWAIT;
508
509 tlinfo c1%ROWTYPE;
510
511 BEGIN
512
513 OPEN c1;
514 FETCH c1 INTO tlinfo;
515 IF (c1%notfound) THEN
516 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
517 igs_ge_msg_stack.add;
518 CLOSE c1;
519 app_exception.raise_exception;
520 RETURN;
521 END IF;
522 CLOSE c1;
523
524 IF (
525 (tlinfo.submission_name = x_submission_name)
526 AND (tlinfo.user_return_subclass = x_user_return_subclass)
527 AND (tlinfo.return_name = x_return_name)
528 AND (tlinfo.extract_phase = x_extract_phase)
529 AND ((tlinfo.conc_request_id = x_conc_request_id) OR ((tlinfo.conc_request_id IS NULL) AND (X_conc_request_id IS NULL)))
530 AND ((tlinfo.conc_request_status = x_conc_request_status) OR ((tlinfo.conc_request_status IS NULL) AND (X_conc_request_status IS NULL)))
531 AND ((tlinfo.extract_run_date = x_extract_run_date) OR ((tlinfo.extract_run_date IS NULL) AND (X_extract_run_date IS NULL)))
532 AND ((tlinfo.file_name = x_file_name) OR ((tlinfo.file_name IS NULL) AND (X_file_name IS NULL)))
533 AND ((tlinfo.file_location = x_file_location) OR ((tlinfo.file_location IS NULL) AND (X_file_location IS NULL)))
534 AND ((tlinfo.date_file_sent = x_date_file_sent) OR ((tlinfo.date_file_sent IS NULL) AND (X_date_file_sent IS NULL)))
535 AND ((tlinfo.extract_override = x_extract_override) OR ((tlinfo.extract_override IS NULL) AND (X_extract_override IS NULL)))
536 AND ((tlinfo.validation_kit_result = x_validation_kit_result) OR ((tlinfo.validation_kit_result IS NULL) AND (X_validation_kit_result IS NULL)))
537 AND ((tlinfo.hesa_validation_result = x_hesa_validation_result) OR ((tlinfo.hesa_validation_result IS NULL) AND (X_hesa_validation_result IS NULL)))
538 AND ((tlinfo.student_ext_run_id = x_student_ext_run_id) OR ((tlinfo.student_ext_run_id IS NULL) AND (X_student_ext_run_id IS NULL)))
539 ) THEN
540 NULL;
541 ELSE
542 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
543 igs_ge_msg_stack.add;
544 app_exception.raise_exception;
545 END IF;
546
547 RETURN;
548
549 END lock_row;
550
551
552 PROCEDURE update_row (
553 x_rowid IN VARCHAR2,
554 x_extract_run_id IN NUMBER,
555 x_submission_name IN VARCHAR2,
556 x_user_return_subclass IN VARCHAR2,
557 x_return_name IN VARCHAR2,
558 x_extract_phase IN VARCHAR2,
559 x_conc_request_id IN NUMBER,
560 x_conc_request_status IN VARCHAR2,
561 x_extract_run_date IN DATE,
562 x_file_name IN VARCHAR2,
563 x_file_location IN VARCHAR2,
564 x_date_file_sent IN DATE,
565 x_extract_override IN VARCHAR2,
566 x_validation_kit_result IN VARCHAR2,
567 x_hesa_validation_result IN VARCHAR2,
568 x_student_ext_run_id IN VARCHAR2,
569 x_mode IN VARCHAR2
570 ) AS
571 /*
572 || Created By : rgopalan
573 || Created On : 23-JAN-2002
574 || Purpose : Handles the UPDATE DML logic for the table.
575 || Known limitations, enhancements or remarks :
576 || Change History :
577 || Who When What
578 || (reverse chronological order - newest change first)
579 */
580 x_last_update_date DATE ;
581 x_last_updated_by NUMBER;
582 x_last_update_login NUMBER;
583
584 BEGIN
585
586 x_last_update_date := SYSDATE;
587 IF (X_MODE = 'I') THEN
588 x_last_updated_by := 1;
589 x_last_update_login := 0;
590 ELSIF (x_mode = 'R') THEN
591 x_last_updated_by := fnd_global.user_id;
592 IF x_last_updated_by IS NULL THEN
593 x_last_updated_by := -1;
594 END IF;
595 x_last_update_login := fnd_global.login_id;
596 IF (x_last_update_login IS NULL) THEN
597 x_last_update_login := -1;
598 END IF;
599 ELSE
600 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
601 igs_ge_msg_stack.add;
602 app_exception.raise_exception;
603 END IF;
604
605 before_dml(
606 p_action => 'UPDATE',
607 x_rowid => x_rowid,
608 x_extract_run_id => x_extract_run_id,
609 x_submission_name => x_submission_name,
610 x_user_return_subclass => x_user_return_subclass,
611 x_return_name => x_return_name,
612 x_extract_phase => x_extract_phase,
613 x_conc_request_id => x_conc_request_id,
614 x_conc_request_status => x_conc_request_status,
615 x_extract_run_date => x_extract_run_date,
616 x_file_name => x_file_name,
617 x_file_location => x_file_location,
618 x_date_file_sent => x_date_file_sent,
619 x_extract_override => x_extract_override,
620 x_validation_kit_result => x_validation_kit_result,
621 x_hesa_validation_result => x_hesa_validation_result,
622 x_student_ext_run_id => x_student_ext_run_id,
623 x_creation_date => x_last_update_date,
624 x_created_by => x_last_updated_by,
625 x_last_update_date => x_last_update_date,
626 x_last_updated_by => x_last_updated_by,
627 x_last_update_login => x_last_update_login
628 );
629
630 UPDATE igs_he_ext_run_dtls
631 SET
632 submission_name = new_references.submission_name,
633 user_return_subclass = new_references.user_return_subclass,
634 return_name = new_references.return_name,
635 extract_phase = new_references.extract_phase,
636 conc_request_id = new_references.conc_request_id,
637 conc_request_status = new_references.conc_request_status,
638 extract_run_date = new_references.extract_run_date,
639 file_name = new_references.file_name,
640 file_location = new_references.file_location,
641 date_file_sent = new_references.date_file_sent,
642 extract_override = new_references.extract_override,
643 validation_kit_result = new_references.validation_kit_result,
644 hesa_validation_result = new_references.hesa_validation_result,
645 student_ext_run_id = new_references.student_ext_run_id,
646 last_update_date = x_last_update_date,
647 last_updated_by = x_last_updated_by,
648 last_update_login = x_last_update_login
649 WHERE rowid = x_rowid;
650
651 IF (SQL%NOTFOUND) THEN
652 RAISE NO_DATA_FOUND;
653 END IF;
654
655 END update_row;
656
657
658 PROCEDURE add_row (
659 x_rowid IN OUT NOCOPY VARCHAR2,
660 x_extract_run_id IN OUT NOCOPY NUMBER,
661 x_submission_name IN VARCHAR2,
662 x_user_return_subclass IN VARCHAR2,
663 x_return_name IN VARCHAR2,
664 x_extract_phase IN VARCHAR2,
665 x_conc_request_id IN NUMBER,
666 x_conc_request_status IN VARCHAR2,
667 x_extract_run_date IN DATE,
668 x_file_name IN VARCHAR2,
669 x_file_location IN VARCHAR2,
670 x_date_file_sent IN DATE,
671 x_extract_override IN VARCHAR2,
672 x_validation_kit_result IN VARCHAR2,
673 x_hesa_validation_result IN VARCHAR2,
674 x_student_ext_run_id IN VARCHAR2,
675 x_mode IN VARCHAR2
676 ) AS
677 /*
678 || Created By : rgopalan
679 || Created On : 23-JAN-2002
680 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
681 || Known limitations, enhancements or remarks :
682 || Change History :
683 || Who When What
684 || (reverse chronological order - newest change first)
685 */
686 CURSOR c1 IS
687 SELECT rowid
688 FROM igs_he_ext_run_dtls
689 WHERE extract_run_id = x_extract_run_id;
690
691 BEGIN
692
693 OPEN c1;
694 FETCH c1 INTO x_rowid;
695 IF (c1%NOTFOUND) THEN
696 CLOSE c1;
697
698 insert_row (
699 x_rowid,
700 x_extract_run_id,
701 x_submission_name,
702 x_user_return_subclass,
703 x_return_name,
704 x_extract_phase,
705 x_conc_request_id,
706 x_conc_request_status,
707 x_extract_run_date,
708 x_file_name,
709 x_file_location,
710 x_date_file_sent,
711 x_extract_override,
712 x_validation_kit_result,
713 x_hesa_validation_result,
714 x_student_ext_run_id,
715 x_mode
716 );
717 RETURN;
718 END IF;
719 CLOSE c1;
720
721 update_row (
722 x_rowid,
723 x_extract_run_id,
724 x_submission_name,
725 x_user_return_subclass,
726 x_return_name,
727 x_extract_phase,
728 x_conc_request_id,
729 x_conc_request_status,
730 x_extract_run_date,
731 x_file_name,
732 x_file_location,
733 x_date_file_sent,
734 x_extract_override,
735 x_validation_kit_result,
736 x_hesa_validation_result,
737 x_student_ext_run_id,
738 x_mode
739 );
740
741 END add_row;
742
743
744 PROCEDURE delete_row (
745 x_rowid IN VARCHAR2
746 ) AS
747 /*
748 || Created By : rgopalan
749 || Created On : 23-JAN-2002
750 || Purpose : Handles the DELETE DML logic for the table.
751 || Known limitations, enhancements or remarks :
752 || Change History :
753 || Who When What
754 || (reverse chronological order - newest change first)
755 */
756 BEGIN
757
758 before_dml (
759 p_action => 'DELETE',
760 x_rowid => x_rowid
761 );
762
763 DELETE FROM igs_he_ext_run_dtls
764 WHERE rowid = x_rowid;
765
766 IF (SQL%NOTFOUND) THEN
767 RAISE NO_DATA_FOUND;
768 END IF;
769
770 END delete_row;
771
772
773 END igs_he_ext_run_dtls_pkg;