[Home] [Help]
PACKAGE BODY: APPS.IGR_IMP_001
Source
1 PACKAGE BODY IGR_IMP_001 AS
2 /* $Header: IGSRT02B.pls 120.1 2006/06/27 12:24:29 rbezawad noship $ */
3 PROCEDURE trn_ss_inq_int_data(
4 errbuf OUT NOCOPY VARCHAR2,
5 retcode OUT NOCOPY NUMBER,
6 p_inquiry_type_id IN NUMBER,
7 p_inq_start_date IN VARCHAR2 ,
8 p_inq_end_date IN VARCHAR2
9 ) AS
10 /* Variable Declaration */
11 l_bool BOOLEAN;
12 l_batch_desc igs_ad_imp_batch_det.batch_Desc%TYPE;
13 l_batch_id NUMBER;
14 l_interface_id NUMBER;
15 l_status NUMBER := 0;
16 l_tokenstr VARCHAR2(500);
17 completed_flag VARCHAR2(1);
18 inserted CONSTANT VARCHAR2(1) := 'I' ;
19 updated CONSTANT VARCHAR2(1) := 'U' ;
20 l_inq_start_date DATE := igs_ge_date.igsdate(P_INQ_START_DATE);
21 l_inq_end_date DATE := igs_ge_date.igsdate(P_INQ_END_DATE);
22
23 -- need to use todate to run from backend API, so retaining the below two lines
24 -- l_inq_start_date DATE := todate(P_INQ_START_DATE);
25 -- l_inq_end_date DATE := todate(P_INQ_END_DATE);
26
27 null_validation_fails EXCEPTION;
28 date_validation_fails EXCEPTION;
29
30 CURSOR batch_id_cur IS
31 SELECT igs_ad_interface_batch_id_s.nextval FROM dual;
32
33 CURSOR interface_id_cur IS
34 SELECT igs_ad_interface_s.nextval FROM dual;
35
36 /* This Main Cursor retrieves all the Person and Inquiry Details,
37 with Status 'I' or 'U' which are then inserted
38 into IGS_AD_INTERFACE_ALL,IGS_AD_STAT_INT,IGS_PE_CITIZEN_INT and
39 IGS_AD_INQ_APPL_INT after all the validations are successfull
40 */
41
42 CURSOR inq_per_cur IS
43 SELECT
44 pe.org_id,
45 pe.inq_person_id,
46 pe.imp_source_type_id,
47 pe.given_name,
48 pe.middle_name,
49 pe.surname,
50 pe.preferred_given_name,
51 pe.title,
52 pe.suffix,
53 pe.pre_name_adjunct,
54 pe.birth_dt,
55 pe.sex,
56 pe.ethnic_origin,
57 pe.citizenship1_id,
58 pe.citizenship2_id,
59 inq.inq_inq_id,
60 inq.inquiry_type_id,
61 inq.inq_entry_level_id,
62 inq.inquiry_source_type_id,
63 inq.inquiry_date,
64 inq.edu_goal_id,
65 inq.school_of_interest_id,
66 inq.how_knowus_id,
67 inq.who_influenced_id,
68 inq.comments,
69 inq.acad_cal_type,
70 inq.acad_ci_sequence_number,
71 inq.adm_cal_type,
72 inq.adm_ci_sequence_number,
73 inq.attribute_category,
74 inq.attribute1,
75 inq.attribute2,
76 inq.attribute3,
77 inq.attribute4,
78 inq.attribute5,
79 inq.attribute6,
80 inq.attribute7,
81 inq.attribute8,
82 inq.attribute9,
83 inq.attribute10,
84 inq.attribute11,
85 inq.attribute12,
86 inq.attribute13,
87 inq.attribute14,
88 inq.attribute15,
89 inq.attribute16,
90 inq.attribute17,
91 inq.attribute18,
92 inq.attribute19,
93 inq.attribute20,
94 inq.source_promotion_id
95 FROM
96 igr_is_person pe,
97 igr_is_inquiry inq
98 WHERE
99 pe.inq_person_id = inq.inq_person_id AND
100 inq.inquiry_type_id = p_inquiry_type_id AND
101 (
102 inq.inquiry_date is Null OR
103 (TRUNC(inq.inquiry_date) BETWEEN
104 NVL(l_inq_start_date, TRUNC(inq.inquiry_date)) AND
105 NVL(l_inq_end_date,TRUNC(inq.inquiry_date))
106 )
107 ) AND
108 pe.status IN (inserted,updated) AND
109 inq.status IN (inserted,updated);
110
111 CURSOR inq_per_contacts_cur(l_person_id igr_is_contact.inq_person_id%TYPE) IS
112 SELECT
113 inq_contact_id,
114 inq_person_id,
115 request_id,
116 phone_country_code,
117 phone_area_code,
118 phone_number,
119 phone_extension,
120 phone_line_type,
121 email_address
122 FROM
123 igr_is_contact
124 WHERE
125 inq_person_id = l_person_id AND
126 status IN (inserted,updated);
127
128 CURSOR inq_per_addr_cur(l_person_id igr_is_address.inq_person_id%TYPE) IS
129 SELECT
130 addr_line_1,
131 addr_line_2,
132 addr_line_3,
133 addr_line_4,
134 city,
135 state,
136 county,
137 province,
138 country,
139 postcode,
140 start_date,
141 end_date,
142 addr_usage
143 FROM
144 igr_is_address
145 WHERE
146 inq_person_id = l_person_id AND
147 status IN (inserted,updated);
148
149 CURSOR inq_per_acad_cur(l_person_id igr_is_acad.inq_person_id%TYPE) IS
150 SELECT
151 institution_cd,
152 current_inst,
153 start_date,
154 end_date,
155 planned_completion_date,
156 degree_earned,
157 course_major,
158 selfrep_inst_gpa,
159 selfrep_rank_in_class,
160 selfrep_classsize,
161 selfrep_total_cp_earned
162 FROM
163 igr_is_acad
164 WHERE
165 inq_person_id = l_person_id AND
166 status IN (inserted,updated);
167
168 CURSOR inq_per_extra_cur(l_person_id igr_is_extracurr.inq_person_id%TYPE) IS
169 SELECT
170 interest_type_code,
171 interest_name,
172 activity_source_cd,
173 start_date,
174 end_date
175 FROM
176 igr_is_extracurr
177 WHERE
178 inq_person_id = l_person_id AND
179 status IN (inserted,updated);
180
181 CURSOR inq_sub_interest_type_cur (cp_interest_type_code igr_is_extracurr.interest_type_code%TYPE) IS
182 SELECT
183 lookup_type
184 FROM
185 fnd_lookup_values
186 WHERE
187 lookup_code = cp_interest_type_code AND
188 lookup_type in ( 'ENTERTAINMENT', 'INTEREST_TYPE') AND
189 enabled_flag = 'Y';
190
191 l_sub_interest_type_code igs_ad_excurr_int.sub_interest_type_code%TYPE;
192
193 CURSOR inq_per_test_cur(l_person_id igr_is_test.inq_person_id%TYPE) IS
194 SELECT
195 inq_test_id,
196 admission_test_type,
197 comp_test_score,
198 test_date,
199 test_source_id
200 FROM
201 igr_is_test
202 WHERE
203 inq_person_id = l_person_id AND
204 status IN (inserted,updated);
205
206 CURSOR inq_per_testseg_cur(l_test_id igr_is_testseg.inq_test_id%TYPE) IS
207 SELECT
208 test_segment_id,
209 test_score
210 FROM
211 igr_is_testseg
212 WHERE
213 inq_test_id = l_test_id AND
214 status IN (inserted,updated);
215
216 CURSOR inq_info_cur(l_inq_id igr_is_info_req.inq_inq_id%TYPE) IS
217 SELECT
218 package_item_id
219 FROM
220 igr_is_info_req
221 WHERE
222 inq_inq_id = l_inq_id AND
223 status IN (inserted,updated);
224
225 -- Bug no 2843629
226 -- by rrengara on 13-mar-2003
227 -- This cursor will return an empty record if lines table doesnt have
228 -- any record correspoding to the inquiry record
229 -- So that it will insert dummy record in the interface table
230
231 CURSOR inq_lines_cur(l_inq_inq_id igr_is_i_lines.inq_inq_id%TYPE) IS
232 SELECT
233 preference,
234 product_category_id,
235 product_category_set_id
236 FROM
237 igr_is_i_lines
238 WHERE
239 inq_inq_id = l_inq_inq_id AND
240 status IN (inserted,updated);
241
242
243 --2775931 Start
244 CURSOR inq_per_race_cur(l_person_id igr_is_race.person_id%TYPE) IS
245 SELECT
246 race_cd
247 FROM
248 igr_is_race
249 WHERE
250 person_id = l_person_id AND
251 status IN (inserted,updated);
252 --2775931 Start
253
254
255 /* LOCAL PROCEDURES FOR VALIDATIONS */
256 -----------------------------------------------------------------------------------------------------------
257 /* Each record from the Self Service table is validated for Null values
258 before inserting into the corresponding Interface tables
259 using the validation procedures here */
260
261 PROCEDURE validate_interface(inq_rec IN inq_per_cur%ROWTYPE,
262 status OUT NOCOPY NUMBER,
263 batch_id IN NUMBER,
264 interface_id IN NUMBER,
265 l_token OUT NOCOPY VARCHAR2) AS
266 BEGIN
267 status:=1;
268 IF inq_rec.imp_source_type_id IS NULL OR inq_rec.surname IS NULL OR inq_rec.given_name IS NULL OR
269 batch_id IS NULL OR interface_id IS NULL THEN
270 status:=0;
271 l_token := NULL;
272 IF inq_rec.imp_source_type_id IS null THEN
273 l_token := 'imp_source_type_id';
274 END IF;
275 IF inq_rec.surname IS null THEN
276 l_token := l_token ||', surname';
277 END IF;
278 IF inq_rec.given_name IS null THEN
279 l_token:= l_token ||', given_name';
280 END IF;
281 IF batch_id IS null THEN
282 l_token := l_token || ' ,batch_id';
283 END IF;
284 IF interface_id is null THEN
285 l_token := l_token ||' ,interface_id ';
286 END IF;
287 END IF;
288 END validate_interface;
289
290 PROCEDURE validate_inq_appl_int(inq_rec IN inq_per_cur%ROWTYPE, status OUT NOCOPY NUMBER,l_token OUT NOCOPY VARCHAR2) AS
291 BEGIN
292 status:=1;
293 l_token:=NULL;
294 IF inq_rec.inquiry_date IS NULL OR inq_rec.inquiry_source_type_id IS NULL OR inq_rec.inquiry_type_id IS NULL THEN
295 status:=0;
296 END IF;
297 IF inq_rec.inquiry_date IS NULL THEN
298 l_token:= 'inquiry_date';
299 END IF;
300 IF inq_rec.inquiry_source_type_id IS NULL THEN
301 l_token:= l_token ||', inquiry_source_type_id';
302 END IF;
303 IF inq_rec.inquiry_type_id IS NULL THEN
304 l_token:= l_token ||', p_inquiry_type_id';
305 END IF;
306 END validate_inq_appl_int;
307
308 PROCEDURE validate_addr_int(inq_per_addr_rec IN inq_per_addr_cur%ROWTYPE, status OUT NOCOPY NUMBER,l_token OUT NOCOPY VARCHAR2) AS
309 BEGIN
310 status:=1;
311 l_token:=NULL;
312 IF inq_per_addr_rec.addr_line_1 IS NULL OR inq_per_addr_rec.country IS NULL THEN
313 status:=0;
314 END IF;
315 IF inq_per_addr_rec.addr_line_1 IS NULL THEN
316 l_token:='addr_line_1 ';
317 END IF;
318 IF inq_per_addr_rec.country IS NULL THEN
319 l_token:=l_token ||', country';
320 END IF;
321 END validate_addr_int;
322
323 PROCEDURE validate_addrusage_int(inq_per_addr_rec IN inq_per_addr_cur%ROWTYPE,
324 status OUT NOCOPY NUMBER,l_token OUT NOCOPY VARCHAR2) AS
325 BEGIN
326 status:=1;
327 l_token:=NULL;
328 IF inq_per_addr_rec.addr_usage IS NULL THEN
329 status:=0;
330 l_token:='addr_usage';
331 END IF;
332 END validate_addrusage_int;
333
334 PROCEDURE validate_acadhis_int(inq_per_acad_rec IN inq_per_acad_cur%ROWTYPE, status OUT NOCOPY NUMBER,
335 l_token OUT NOCOPY VARCHAR2) AS
336 BEGIN
337 status:=1;
338 l_token:=NULL;
339 IF inq_per_acad_rec.institution_cd IS NULL OR inq_per_acad_rec.current_inst IS NULL THEN
340 status:=0;
341 END IF;
342 IF inq_per_acad_rec.institution_cd IS NULL THEN
343 l_token:='institution_cd ';
344 END IF;
345 IF inq_per_acad_rec.current_inst IS NULL THEN
346 l_token:=l_token ||',current_inst';
347 END IF;
348 END validate_acadhis_int;
349
350 PROCEDURE validate_excurr_act_int(inq_per_extra_rec IN inq_per_extra_cur%ROWTYPE,
351 status OUT NOCOPY NUMBER,l_token OUT NOCOPY VARCHAR2) AS
352 BEGIN
353 status:=1;
354 l_token:=NULL;
355 IF inq_per_extra_rec.interest_name IS NULL THEN
356 status:=0;
357 l_token:='interest_name';
358 END IF;
359 END validate_excurr_act_int;
360
361 PROCEDURE validate_inq_pkg_int(inq_info_rec IN inq_info_cur%ROWTYPE,status OUT NOCOPY NUMBER,l_token OUT NOCOPY VARCHAR2) AS
362 BEGIN
363 status:=1;
364 l_token:=NULL;
365 IF inq_info_rec.package_item_id IS NULL THEN
366 status:=0;
367 l_token:='package_item_id';
371 PROCEDURE validate_test_int(inq_per_test_rec IN inq_per_test_cur%ROWTYPE, status OUT NOCOPY NUMBER,l_token OUT NOCOPY VARCHAR2) AS
368 END IF;
369 END validate_inq_pkg_int;
370
372 BEGIN
373 status :=1;
374 l_token:=NULL;
375 IF inq_per_test_rec.admission_test_type IS NULL OR inq_per_test_rec.test_date IS NULL THEN
376 status :=0;
377 END IF;
378 IF inq_per_test_rec.admission_test_type IS NULL THEN
379 l_token:='admission_test_type';
380 END IF;
381 IF inq_per_test_rec.test_date IS NULL THEN
382 l_token:=l_token||', test_date';
383 END IF;
384 END validate_test_int;
385
386 PROCEDURE validate_test_segs_int(inq_per_testseg_rec IN inq_per_testseg_cur%ROWTYPE,
387 status OUT NOCOPY NUMBER,l_token OUT NOCOPY VARCHAR2) AS
388 BEGIN
389 status:=1;
390 l_token:=NULL;
391 IF inq_per_testseg_rec.test_segment_id IS NULL OR
392 inq_per_testseg_rec.test_score IS NULL THEN
393 status:=0;
394 END IF;
395 IF inq_per_testseg_rec.test_segment_id IS NULL THEN
396 l_token:=l_token || ', test_segment_id';
397 END IF;
401 END validate_test_segs_int;
398 IF inq_per_testseg_rec.test_score IS NULL THEN
399 l_token:=l_token ||', test_score';
400 END IF;
402
403
404 --2775931 start
405
406 PROCEDURE validate_race_int(inq_per_race_rec IN inq_per_race_cur%ROWTYPE, status OUT NOCOPY NUMBER,
407 l_token OUT NOCOPY VARCHAR2) AS
408 CURSOR c_get_lookup_code IS
409 SELECT lookup_code
410 FROM igs_lookup_values
411 WHERE lookup_type = 'PE_RACE'
412 AND enabled_flag = 'Y';
413 BEGIN
414 status:=1;
415 l_token:=NULL;
416 IF inq_per_race_rec.race_cd IS NULL THEN
417 status:=0;
418 END IF;
419 IF inq_per_race_rec.race_cd IS NULL THEN
420 l_token:='race_cd ';
421 END IF;
422 FOR c_get_lookup_code_rec IN c_get_lookup_code LOOP
423
424 IF c_get_lookup_code_rec.lookup_code=inq_per_race_rec.race_cd THEN
425 status:=1;
426 EXIT;
427 ELSE
428 status:=2;
429 l_token:='race_cd ';
430 END IF;
431 END LOOP;
432
433 END validate_race_int;
434
435 --2775931 end
436
437
438 /* LOCAL PROCEDURES END */
439 ----------------------------------------------------------------------------------------------
440 /* Main procedure body */
441
442 BEGIN
443 retcode:=0;
444 igs_ge_gen_003.set_org_id(null);
445 igs_ge_msg_stack.initialize;
446 -- Navin.Sinha 30-Jun-03 Bug No: 3023795 If end date is not entered, then default in the end date to be the sysdate.
447 IF l_inq_start_date IS NOT NULL AND l_inq_end_date IS NULL THEN
448 l_inq_end_date := SYSDATE;
449 END IF;
450
451 -- Navin.Sinha 30-Jun-03 Bug No: 3023795 end dates is => start_date
452 IF ((l_inq_start_date IS NOT NULL) AND (l_inq_end_date IS NOT NULL) AND (l_inq_start_date <= l_inq_end_date))
453 OR ((l_inq_start_date IS NULL) AND (l_inq_end_date IS NULL)) THEN
454
455 /*Entire batch of records processed in this process are given this unique interface id */
456 FOR inq_rec IN inq_per_cur LOOP /* The outermost LOOP starts here */
457 BEGIN
458
459 IF l_batch_id IS NULL THEN
460 /* If the Parameter Start Date is less than End Date then Start processing */
461 /*Entire batch of records processed in this process are given this unique batch id */
462 OPEN batch_id_cur;
463 FETCH batch_id_cur into l_batch_id;
464 CLOSE batch_id_cur;
465 l_batch_Desc := 'Self Service Inquiry import batch ' || IGS_GE_NUMBER.TO_CANN(l_batch_id);
466 fnd_file.put_line(fnd_file.log,'For importing self service inquiry records:');
467 fnd_file.put_line(fnd_file.log,'Batch Id : '||IGS_GE_NUMBER.TO_CANN(l_batch_id));
468 fnd_file.put_line(fnd_file.log,'Batch description : '||l_batch_desc);
469 BEGIN
470 INSERT INTO IGS_AD_IMP_BATCH_DET
471 ( batch_id,
472 batch_Desc,
473 created_by,
474 creation_date,
475 last_updated_by,
476 last_update_date,
477 last_update_login,
478 request_id,
479 program_application_id,
480 program_id,
481 program_update_date
482 )VALUES
483 (
484 l_batch_id,
485 l_batch_desc,
486 1,
487 sysdate,
488 1,
489 sysdate,
490 null,
491 fnd_global.conc_request_id,
492 fnd_global.prog_appl_id,
493 fnd_global.conc_program_id,
494 sysdate);
495 EXCEPTION WHEN OTHERS THEN
496 l_status:=0;
497 fnd_file.put_line(fnd_file.log,'Insert on IGS_AD_IMP_BATCH_DET failed '||SQLERRM);
498 RAISE;
499 END;
500 END IF; --End of Batch ID null check
501
502 /* The Interface ID should be unique for each record --rghosh for bug 3365975 */
503 OPEN interface_id_cur;
504 FETCH interface_id_cur into l_interface_id;
505 CLOSE interface_id_cur;
506
507 SAVEPOINT inqsavepoint ;
508 completed_flag := 'Y'; /* this flag is set to 'N' when any of the validation fails */
509 /* Validate the data in Self Service table for not null before inseting into igs_ad_interface_all */
510 validate_interface(inq_rec, l_status,l_batch_id,l_interface_id,l_tokenstr);
511
512 IF l_status = 1 THEN /* Validation Successful */
513 BEGIN
514 INSERT INTO igs_ad_interface_all /* Insert valid record into this interface table */
515 (
516 org_id,
517 interface_id,
518 batch_id,
519 source_type_id,
520 surname,
521 middle_name,
522 given_names,
523 preferred_given_name,
524 sex,
525 birth_dt,
526 title,
527 suffix,
528 pre_name_adjunct,
529 level_of_qual,
530 proof_of_insurance,
531 proof_of_immun,
532 pref_alternate_id,
533 person_id,
534 status,
535 military_service_reg,
536 veteran,
537 match_ind,
538 person_match_ind,
539 error_code,
540 record_status,
541 interface_run_id,
542 attribute_category,
546 attribute4,
543 attribute1,
544 attribute2,
545 attribute3,
547 attribute5,
548 attribute6,
549 attribute7,
550 attribute8,
551 attribute9,
552 attribute10,
553 attribute11,
554 attribute12,
555 attribute13,
556 attribute14,
557 attribute15,
558 attribute16,
559 attribute17,
560 attribute18,
561 attribute19,
562 attribute20,
563 created_by,
564 creation_date,
565 last_updated_by,
566 last_update_date,
567 last_update_login,
568 request_id,
569 program_application_id,
570 program_id,
571 program_update_date,
572 person_number
573 )
574 VALUES
575 (
576 inq_rec.org_id,
577 l_interface_id,
578 l_batch_id,
579 inq_rec.imp_source_type_id,
580 inq_rec.surname,
581 inq_rec.middle_name,
582 inq_rec.given_name,
583 inq_rec.preferred_given_name,
584 inq_rec.sex,
585 inq_rec.birth_dt,
586 inq_rec.title,
587 inq_rec.suffix,
588 inq_rec.pre_name_adjunct,
589 null,
590 null,
591 null,
592 null,
593 null,
594 '2',
595 null,
596 null,
597 null,
598 null,
599 null,
600 '2',
601 null,
602 null,
603 null,
604 null,
605 null,
606 null,
607 null,
608 null,
609 null,
610 null,
611 null,
612 null,
613 null,
614 null,
615 null,
616 null,
617 null,
618 null,
619 null,
620 null,
621 null,
622 null,
623 1,
624 sysdate,
625 1,
626 sysdate,
627 null,
628 fnd_global.conc_request_id,
629 fnd_global.prog_appl_id,
630 fnd_global.conc_program_id,
631 sysdate,
632 null
633 );
634 EXCEPTION WHEN OTHERS THEN
635 l_status:=0;
636 fnd_file.put_line(fnd_file.log,'Insert on IGS_AD_INTERFACE_ALL failed '||SQLERRM);
637 RAISE;
638 END;
639
640 BEGIN
641 INSERT INTO igs_ad_stat_int
642 (
643 org_id ,
644 interface_stat_id ,
645 interface_id ,
646 marital_status ,
647 religion_cd ,
648 person_id ,
649 status ,
650 match_ind ,
651 error_code ,
652 created_by ,
653 creation_date ,
654 last_updated_by ,
655 last_update_date ,
656 last_update_login ,
657 request_id ,
658 program_application_id ,
659 program_id ,
660 program_update_date ,
661 ethnic_origin ,
662 place_of_birth ,
663 marital_status_effective_date,
664 attribute_category,
665 attribute1,
666 attribute2,
667 attribute3,
668 attribute4,
669 attribute5,
670 attribute6,
671 attribute7,
672 attribute8,
673 attribute9,
674 attribute10,
675 attribute11,
676 attribute12,
677 attribute13,
678 attribute14,
679 attribute15,
680 attribute16,
681 attribute17,
682 attribute18,
683 attribute19,
684 attribute20
685 )VALUES
686 (
687 inq_rec.org_id ,
688 igs_ad_stat_int_s.nextval,
689 l_interface_id ,
690 null ,
691 null ,
692 null ,
693 '2' ,
694 null ,
695 null ,
696 1 ,
697 sysdate ,
698 1 ,
699 sysdate ,
700 null ,
701 fnd_global.conc_request_id,
702 fnd_global.prog_appl_id,
703 fnd_global.conc_program_id,
704 sysdate ,
705 inq_rec.ethnic_origin ,
706 null ,
707 null,
708 null,
709 null,
710 null,
711 null,
712 null,
716 null,
713 null,
714 null,
715 null,
717 null,
718 null,
719 null,
720 null,
721 null,
722 null,
723 null,
724 null,
725 null,
726 null,
727 null,
728 null
729 );
730 EXCEPTION WHEN OTHERS THEN
731 l_status:=0;
732 fnd_file.put_line(fnd_file.log,'Insert on igs_ad_stat_int failed '||SQLERRM);
733 RAISE;
734 END;
735
736 ELSE /* l_status = 0 ie Validation failed*/
737 fnd_message.set_name('IGS','IGS_AD_SS_TO_INT_NULL_FAIL');
738 fnd_message.set_token('TABLE_NAME','igs_ad_interface_all');
739 fnd_message.set_token('COL_NAMES',l_tokenstr);
740 fnd_message.set_token('ID',inq_rec.inq_person_id);
741 fnd_file.put_line(fnd_file.log,fnd_message.get);
742 RAISE null_validation_fails;
743 END IF;
744
745 /* If atleast one of citizenship1_id and citizenship2_id is not null then Insert */
746 IF inq_rec.citizenship1_id IS NOT NULL THEN
747 BEGIN
748 INSERT INTO igs_pe_citizen_int
749 (
750 interface_citizenship_id,
751 interface_id ,
752 country_code ,
753 document_type ,
754 document_reference ,
755 date_disowned ,
756 date_recognized ,
757 end_date ,
758 match_ind ,
759 status ,
760 error_code ,
761 dup_citizenship_id ,
762 program_id ,
763 program_application_id ,
764 program_update_date ,
765 created_by ,
766 creation_date ,
767 last_updated_by ,
768 last_update_date ,
769 last_update_login ,
770 request_id
771 ) VALUES
772 (
773 igs_ad_citizen_int_s.nextval,
774 l_interface_id ,
775 inq_rec.citizenship1_id,
776 null ,
777 null ,
778 null ,
779 null ,
780 null ,
781 null ,
782 '2' ,
783 null ,
784 null ,
785 fnd_global.conc_program_id,
786 fnd_global.prog_appl_id,
787 sysdate,
788 1,
789 sysdate,
790 1,
791 sysdate,
792 fnd_global.login_id,
793 fnd_global.conc_request_id
794 );
795 EXCEPTION WHEN OTHERS THEN
796 l_status:=0;
797 fnd_file.put_line(fnd_file.log,'Insert on IGS_PE_CITIZEN_INT failed '||SQLERRM);
798 RAISE;
799 END;
800
801
802 END IF;
803
804 IF inq_rec.citizenship2_id IS NOT NULL THEN
805 BEGIN
806 INSERT INTO igs_pe_citizen_int
807 (
808 interface_citizenship_id ,
809 interface_id ,
810 country_code ,
811 document_type ,
812 document_reference ,
813 date_disowned ,
814 date_recognized ,
815 end_date ,
816 match_ind ,
817 status ,
818 error_code ,
819 dup_citizenship_id ,
820 program_id ,
821 program_application_id ,
822 program_update_date ,
823 created_by ,
824 creation_date ,
825 last_updated_by ,
826 last_update_date ,
827 last_update_login ,
828 request_id
829 ) VALUES
830 (
831 igs_ad_citizen_int_s.nextval,
832 l_interface_id ,
833 inq_rec.citizenship2_id,
834 null ,
835 null ,
836 null ,
837 null ,
838 null ,
839 null ,
840 '2' ,
841 null ,
842 null ,
843 fnd_global.conc_program_id,
844 fnd_global.prog_appl_id,
845 sysdate,
846 1,
847 sysdate,
848 1,
849 sysdate,
850 fnd_global.login_id,
851 fnd_global.conc_request_id
852 );
853 EXCEPTION WHEN OTHERS THEN
854 l_status:=0;
855 fnd_file.put_line(fnd_file.log,'Insert on IGS_PE_CITIZEN_INT failed '||SQLERRM);
856 RAISE;
857 END;
858
859
860 END IF;
861
862 /* Validate the data in Self Service table for not null before inseting into igs_ad_inq_appl_int */
863 validate_inq_appl_int(inq_rec, l_status,l_tokenstr);
864 IF l_status = 1 THEN /* Validation Successfull*/
865 BEGIN
869 interface_id ,
866 INSERT INTO igr_i_appl_int
867 (
868 interface_inq_appl_id ,
870 enquiry_appl_number ,
871 acad_cal_type ,
872 acad_ci_sequence_number ,
873 adm_cal_type ,
874 adm_ci_sequence_number ,
875 inquiry_status ,
876 inquiry_dt ,
877 -- dup_person_id ,
878 -- dup_enquiry_appl_number ,
879 inquiry_source_type ,
880 inquiry_type_id ,
881 inquiry_entry_level_id ,
882 registering_person_id ,
883 override_process_ind ,
884 indicated_mailing_dt ,
885 last_process_dt ,
886 comments ,
887 edu_goal_id ,
888 inquiry_school_of_interest_id,
889 learn_source_id ,
890 influence_source_id ,
891 status ,
892 match_ind ,
893 error_code ,
894 attribute_category ,
895 attribute1 ,
896 attribute2 ,
897 attribute3 ,
898 attribute4 ,
899 attribute5 ,
900 attribute6 ,
901 attribute7 ,
902 attribute8 ,
903 attribute9 ,
904 attribute10 ,
905 attribute11 ,
906 attribute12 ,
907 attribute13 ,
908 attribute14 ,
909 attribute15 ,
910 attribute16 ,
911 attribute17 ,
912 attribute18 ,
913 attribute19 ,
914 attribute20 ,
915 created_by ,
916 creation_date ,
917 last_updated_by ,
918 last_update_date ,
919 last_update_login ,
920 request_id ,
921 program_application_id ,
922 program_id ,
923 program_update_date,
924 source_promotion_id
925 )VALUES
926 (
927 igr_i_appl_int_s.nextval,
928 l_interface_id ,
929 null ,
930 inq_rec.acad_cal_type ,
931 inq_rec.acad_ci_sequence_number ,
932 inq_rec.adm_cal_type ,
933 inq_rec.adm_ci_sequence_number ,
934 'OSS_REGISTERED' , -- hard coding here as this status is seeded in lookups
935 inq_rec.inquiry_date ,
936 -- null ,
937 -- null ,
938 inq_rec.inquiry_source_type_id ,
939 inq_rec.inquiry_type_id ,
940 inq_rec.inq_entry_level_id ,
941 null ,
942 'N' ,
943 null ,
944 null ,
945 inq_rec.comments ,
946 inq_rec.edu_goal_id ,
947 inq_rec.school_of_interest_id,
948 inq_rec.how_knowus_id ,
949 inq_rec.who_influenced_id ,
950 '2' ,
951 null ,
952 null ,
953 inq_rec.attribute_category ,
954 inq_rec.attribute1 ,
955 inq_rec.attribute2 ,
956 inq_rec.attribute3 ,
957 inq_rec.attribute4 ,
958 inq_rec.attribute5 ,
959 inq_rec.attribute6 ,
960 inq_rec.attribute7 ,
961 inq_rec.attribute8 ,
962 inq_rec.attribute9 ,
963 inq_rec.attribute10 ,
964 inq_rec.attribute11 ,
965 inq_rec.attribute12 ,
966 inq_rec.attribute13 ,
967 inq_rec.attribute14 ,
968 inq_rec.attribute15 ,
969 inq_rec.attribute16 ,
970 inq_rec.attribute17 ,
971 inq_rec.attribute18 ,
972 inq_rec.attribute19 ,
973 inq_rec.attribute20 ,
974 1 ,
975 sysdate ,
976 1 ,
977 sysdate ,
978 null ,
979 fnd_global.conc_request_id,
980 fnd_global.prog_appl_id,
981 fnd_global.conc_program_id,
982 sysdate,
983 inq_rec.source_promotion_id
984 );
985 EXCEPTION WHEN OTHERS THEN
986 l_status:=0;
987 fnd_file.put_line(fnd_file.log,'Insert on IGR_I_APPL_INT failed '||SQLERRM);
988 RAISE;
989 END;
990 ELSE /* l_status = 0 i.e Validation failed*/
991 fnd_message.set_name('IGS','IGS_AD_SS_TO_INT_NULL_FAIL');
992 fnd_message.set_token('TABLE_NAME','igr_i_appl_int');
993 fnd_message.set_token('COL_NAMES',l_tokenstr);
994 fnd_message.set_token('ID',inq_rec.inq_person_id);
998
995 fnd_file.put_line(fnd_file.log,fnd_message.get);
996 RAISE null_validation_fails;
997 END IF;
999 /* if all the validations are successful so far update the Status of
1000 Corresponding Self Service Records to Transferred (T) */
1001 UPDATE igr_is_inquiry SET status = 'T' WHERE inq_person_id = inq_rec.inq_person_id;
1002 UPDATE igr_is_person SET status = 'T' WHERE inq_person_id = inq_rec.inq_person_id;
1003
1004 /* Now import the Child tables */
1005 -- kamohan 21-MAY-2002
1006 -- Bug 2378114 Add a row for Phone and Email separately
1007 FOR inq_per_contacts_rec IN inq_per_contacts_cur(inq_rec.inq_person_id) LOOP
1008 IF inq_per_contacts_rec.phone_number IS NOT NULL THEN
1009 BEGIN
1010 INSERT INTO igs_ad_contacts_int
1011 (
1012 phone_extension,
1013 status,
1014 match_ind,
1015 error_code,
1016 dup_contact_point_id,
1017 created_by,
1018 creation_date,
1019 last_updated_by,
1020 last_update_date,
1021 last_update_login,
1022 request_id,
1023 program_application_id,
1024 program_id,
1025 program_update_date,
1026 org_id,
1027 interface_contacts_id,
1028 interface_id,
1029 contact_point_type,
1030 email_address,
1031 email_format,
1032 primary_flag,
1033 phone_line_type,
1034 phone_country_code,
1035 phone_area_code,
1036 phone_number
1037 ) VALUES
1038 (
1039 inq_per_contacts_rec.phone_extension,
1040 '2',
1041 NULL,
1042 NULL,
1043 NULL,
1044 1,
1045 SYSDATE,
1046 1,
1047 SYSDATE,
1048 NULL,
1049 fnd_global.conc_request_id,
1050 fnd_global.prog_appl_id,
1051 fnd_global.conc_program_id,
1052 SYSDATE,
1053 inq_rec.org_id,
1054 igs_ad_contacts_int_s.nextval,
1055 l_interface_id,
1056 'PHONE',
1057 NULL,
1058 NULL,
1059 NULL,
1060 inq_per_contacts_rec.phone_line_type,
1061 inq_per_contacts_rec.phone_country_code,
1062 inq_per_contacts_rec.phone_area_code,
1063 inq_per_contacts_rec.phone_number
1064 );
1065 EXCEPTION WHEN OTHERS THEN
1066 l_status:=0;
1067 fnd_file.put_line(fnd_file.log,'Insert on IGS_AD_CONTACTS_INT failed '||SQLERRM);
1068 RAISE;
1069 END;
1070
1071 END IF;
1072 IF inq_per_contacts_rec.email_address IS NOT NULL THEN
1073 BEGIN
1074 INSERT INTO igs_ad_contacts_int
1075 (
1076 phone_extension,
1077 status,
1078 match_ind,
1079 error_code,
1080 dup_contact_point_id,
1081 created_by,
1082 creation_date,
1083 last_updated_by,
1084 last_update_date,
1085 last_update_login,
1086 request_id,
1087 program_application_id,
1088 program_id,
1089 program_update_date,
1090 org_id,
1091 interface_contacts_id,
1092 interface_id,
1093 contact_point_type,
1094 email_address,
1095 email_format,
1096 primary_flag,
1097 phone_line_type,
1098 phone_country_code,
1099 phone_area_code,
1100 phone_number
1101 ) VALUES
1102 (
1103 NULL,
1104 '2',
1105 NULL,
1106 NULL,
1107 NULL,
1108 1,
1109 SYSDATE,
1110 1,
1111 SYSDATE,
1112 NULL,
1113 fnd_global.conc_request_id,
1114 fnd_global.prog_appl_id,
1115 fnd_global.conc_program_id,
1116 SYSDATE,
1117 inq_rec.org_id,
1118 igs_ad_contacts_int_s.nextval,
1119 l_interface_id,
1120 'EMAIL',
1121 inq_per_contacts_rec.email_address,
1122 'MAILTEXT', -- kamohan // Bug 2712105
1123 NULL,
1124 NULL,
1125 NULL,
1126 NULL,
1127 NULL
1128 );
1129 EXCEPTION WHEN OTHERS THEN
1130 l_status:=0;
1131 fnd_file.put_line(fnd_file.log,'Insert on IGS_AD_CONTACTS_INT failed '||SQLERRM);
1132 RAISE;
1133 END;
1134
1135 END IF; -- kamohan Bug 2378114 End of Fix
1136
1137 /* Update the Status of corresponding Self Service record to Transferred */
1138 UPDATE igr_is_contact SET status = 'T' WHERE inq_person_id = inq_rec.inq_person_id;
1139 END LOOP;
1140
1141 FOR inq_per_addr_rec IN inq_per_addr_cur(inq_rec.inq_person_id) LOOP
1142 /* Validate the data in Self Service table for not null before inseting into igs_ad_addr_int */
1143 validate_addr_int(inq_per_addr_rec, l_status,l_tokenstr);
1144 IF l_status = 1 THEN /*Validation Successfull*/
1145 BEGIN
1146 INSERT INTO igs_ad_addr_int
1147 (
1148 interface_addr_id ,
1149 interface_id ,
1150 addr_line_1 ,
1151 org_id ,
1152 addr_line_2 ,
1153 addr_line_3 ,
1154 addr_line_4 ,
1155 postcode ,
1156 city ,
1160 country ,
1157 state ,
1158 county ,
1159 province ,
1161 other_details ,
1162 other_details_1 ,
1163 other_details_2 ,
1164 delivery_point_code ,
1165 other_details_3 ,
1166 correspondence_flag ,
1167 start_date ,
1168 end_date ,
1169 match_ind ,
1170 status ,
1171 error_code ,
1172 created_by ,
1173 request_id ,
1174 program_application_id ,
1175 program_id ,
1176 program_update_date ,
1177 contact_person_id ,
1178 date_last_verified ,
1179 dup_party_site_id ,
1180 last_updated_by ,
1181 last_update_date ,
1182 last_update_login ,
1183 creation_date
1184 ) VALUES
1185 (
1186 igs_ad_addr_int_s.nextval,
1187 l_interface_id ,
1188 inq_per_addr_rec.addr_line_1 ,
1189 inq_rec.org_id ,
1190 inq_per_addr_rec.addr_line_2 ,
1191 inq_per_addr_rec.addr_line_3 ,
1192 inq_per_addr_rec.addr_line_4 ,
1193 inq_per_addr_rec.postcode ,
1194 inq_per_addr_rec.city ,
1195 inq_per_addr_rec.state ,
1196 inq_per_addr_rec.county ,
1197 inq_per_addr_rec.province ,
1198 inq_per_addr_rec.country ,
1199 null ,
1200 null ,
1201 null ,
1202 null ,
1203 null ,
1204 null ,
1205 NVL(inq_per_addr_rec.start_date,SYSDATE),
1206 inq_per_addr_rec.end_date ,
1207 null ,
1208 '2' ,
1209 null ,
1210 1 ,
1211 fnd_global.conc_request_id,
1212 fnd_global.prog_appl_id,
1213 fnd_global.conc_program_id,
1214 sysdate,
1215 null ,
1216 null ,
1217 null ,
1218 1 ,
1219 sysdate ,
1220 null ,
1221 sysdate
1222 );
1223 EXCEPTION WHEN OTHERS THEN
1224 l_status:=0;
1225 fnd_file.put_line(fnd_file.log,'Insert on IGS_AD_ADDR_INT failed '||SQLERRM);
1226 RAISE;
1227 END;
1228
1229 ELSE /* l_status = 0 i.e Validation failed*/
1230 fnd_message.set_name('IGS','IGS_AD_SS_TO_INT_NULL_FAIL');
1231 fnd_message.set_token('TABLE_NAME','igs_ad_addr_int');
1232 fnd_message.set_token('COL_NAMES',l_tokenstr);
1233 fnd_message.set_token('ID',inq_rec.inq_person_id);
1234 fnd_file.put_line(fnd_file.log,fnd_message.get);
1235 RAISE null_validation_fails;
1236 END IF;
1237
1238 validate_addrusage_int(inq_per_addr_rec, l_status,l_tokenstr);
1239
1240 IF l_status = 1 THEN /* Validation Successful */
1241 BEGIN
1242 INSERT INTO igs_ad_addrusage_int
1243 (
1244 last_update_login ,
1245 request_id ,
1246 program_application_id ,
1247 program_id ,
1248 program_update_date ,
1249 interface_addr_id ,
1250 org_id ,
1251 interface_addrusage_id ,
1252 site_use_code ,
1253 comments ,
1254 status ,
1255 creation_date ,
1256 last_updated_by ,
1257 last_update_date ,
1258 created_by ,
1259 error_code ,
1260 match_ind
1261 ) VALUES
1262 (
1263 null ,
1264 fnd_global.conc_request_id,
1265 fnd_global.prog_appl_id,
1266 fnd_global.conc_program_id,
1267 sysdate ,
1268 igs_ad_addr_int_s.currval,
1269 inq_rec.org_id ,
1270 igs_ad_addrusage_int_s.nextval,
1271 inq_per_addr_rec.addr_usage ,
1272 null ,
1273 '2' ,
1274 sysdate ,
1275 1 ,
1276 sysdate ,
1277 1 ,
1278 null ,
1279 null
1280 );
1281 EXCEPTION WHEN OTHERS THEN
1282 l_status:=0;
1283 fnd_file.put_line(fnd_file.log,'Insert on IGS_AD_ADDRUSAGE_INT failed '||SQLERRM);
1284 RAISE;
1285 END;
1286
1290 fnd_message.set_token('TABLE_NAME','igs_ad_addrusage_int');
1287 UPDATE igr_is_address SET status = 'T' WHERE inq_person_id = inq_rec.inq_person_id;
1288 ELSE /* l_status = 0 i.e Validation failed*/
1289 fnd_message.set_name('IGS','IGS_AD_SS_TO_INT_NULL_FAIL');
1291 fnd_message.set_token('COL_NAMES',l_tokenstr);
1292 fnd_message.set_token('ID',inq_rec.inq_person_id);
1293 fnd_file.put_line(fnd_file.log,fnd_message.get);
1294 RAISE null_validation_fails;
1295 END IF;
1296 END LOOP;
1297
1298
1299 FOR inq_per_acad_rec IN inq_per_acad_cur(inq_rec.inq_person_id) LOOP
1300 /* Validate the data in Self Service table for not null before inseting into igs_ad_acadhis_int */
1301 validate_acadhis_int(inq_per_acad_rec,l_status,l_tokenstr);
1302 IF l_status = 1 THEN
1303 BEGIN
1304 INSERT INTO igs_ad_acadhis_int
1305 (
1306 org_id ,
1307 attribute9 ,
1308 attribute10 ,
1309 attribute11 ,
1310 attribute12 ,
1311 attribute13 ,
1312 attribute14 ,
1313 attribute15 ,
1314 attribute16 ,
1315 attribute17 ,
1316 selfrep_rank_in_class ,
1317 selfrep_weighted_gpa ,
1318 attribute8 ,
1319 attribute18 ,
1320 attribute19 ,
1321 attribute20 ,
1322 match_ind ,
1323 status ,
1324 error_code ,
1325 dup_acad_history_id ,
1326 created_by ,
1327 creation_date ,
1328 last_updated_by ,
1329 last_update_date ,
1330 last_update_login ,
1331 request_id ,
1332 program_application_id ,
1333 program_id ,
1334 program_update_date ,
1335 type_of_school ,
1336 interface_acadhis_id ,
1337 interface_id ,
1338 institution_code ,
1339 current_inst ,
1340 degree_attempted ,
1341 degree_earned ,
1342 program_code ,
1343 comments ,
1344 start_date ,
1345 end_date ,
1346 planned_completion_date ,
1347 selfrep_total_cp_attempted ,
1348 selfrep_total_cp_earned ,
1349 selfrep_total_gp_units_attemp,
1350 selfrep_inst_gpa ,
1351 selfrep_grading_scale_id ,
1352 attribute6 ,
1353 attribute7 ,
1354 selfrep_weighted_rank ,
1355 attribute_category ,
1356 attribute1 ,
1357 attribute2 ,
1358 attribute3 ,
1359 attribute4 ,
1360 attribute5 ,
1361 class_size
1362 ) VALUES
1363 (
1364 inq_rec.org_id ,
1365 null ,
1366 null ,
1367 null ,
1368 null ,
1369 null ,
1370 null ,
1371 null ,
1372 null ,
1373 null ,
1374 inq_per_acad_rec.selfrep_rank_in_class ,
1375 null ,
1376 null ,
1377 null ,
1378 null ,
1379 null ,
1380 null ,
1381 '2' ,
1382 null ,
1383 null ,
1384 1 ,
1385 sysdate ,
1386 1 ,
1387 sysdate ,
1388 null ,
1389 fnd_global.conc_request_id ,
1390 fnd_global.prog_appl_id ,
1391 fnd_global.conc_program_id ,
1392 sysdate ,
1393 null ,
1394 igs_ad_acadhis_int_s.nextval ,
1395 l_interface_id ,
1396 inq_per_acad_rec.institution_cd ,
1397 inq_per_acad_rec.current_inst ,
1398 null ,
1399 inq_per_acad_rec.degree_earned ,
1403 inq_per_acad_rec.end_date ,
1400 inq_per_acad_rec.course_major ,
1401 null ,
1402 inq_per_acad_rec.start_date ,
1404 inq_per_acad_rec.planned_completion_date ,
1405 null ,
1406 inq_per_acad_rec.selfrep_total_cp_earned ,
1407 null ,
1408 inq_per_acad_rec.selfrep_inst_gpa ,
1409 null ,
1410 null ,
1411 null ,
1412 null ,
1413 null ,
1414 null ,
1415 null ,
1416 null ,
1417 null ,
1418 null ,
1419 inq_per_acad_rec.selfrep_classsize
1420 );
1421 EXCEPTION WHEN OTHERS THEN
1422 l_status:=0;
1423 fnd_file.put_line(fnd_file.log,'Insert on IGS_AD_ACADHIS_INT failed '||SQLERRM);
1424 RAISE;
1425 END;
1426
1427 UPDATE igr_is_acad SET status = 'T' WHERE inq_person_id = inq_rec.inq_person_id;
1428 ELSE /* l_status = 0 i.e Validation failed*/
1429 fnd_message.set_name('IGS','IGS_AD_SS_TO_INT_NULL_FAIL');
1430 fnd_message.set_token('TABLE_NAME','igs_ad_acadhis_int');
1431 fnd_message.set_token('COL_NAMES',l_tokenstr);
1432 fnd_message.set_token('ID',inq_rec.inq_person_id);
1433 fnd_file.put_line(fnd_file.log,fnd_message.get);
1434 RAISE null_validation_fails;
1435 END IF;
1436 END LOOP;
1437
1438 FOR inq_per_extra_rec IN inq_per_extra_cur(inq_rec.inq_person_id) LOOP
1439 /* Validate the data in Self Service table for not null before inseting into igs_ad_excurr_int */
1440 validate_excurr_act_int(inq_per_extra_rec,l_status,l_tokenstr);
1441 IF l_status = 1 THEN
1442 -- nsinha bug 2733230
1443 l_sub_interest_type_code := NULL;
1444 IF inq_per_extra_rec.interest_type_code IS NOT NULL THEN
1445 -- kamohan bug 2722947
1446 OPEN inq_sub_interest_type_cur ( inq_per_extra_rec.interest_type_code);
1447 FETCH inq_sub_interest_type_cur INTO l_sub_interest_type_code;
1448 CLOSE inq_sub_interest_type_cur;
1449 END IF;
1450 BEGIN
1451 INSERT INTO igs_ad_excurr_int
1452 (
1453 sub_interest_type_code ,
1454 hours_per_week ,
1455 weeks_per_year ,
1456 interest_name ,
1457 team ,
1458 org_id ,
1459 interface_excurr_id ,
1460 interface_id ,
1461 comments ,
1462 start_date ,
1463 end_date ,
1464 match_ind ,
1465 status ,
1466 error_code ,
1467 created_by ,
1468 creation_date ,
1469 last_updated_by ,
1470 last_update_date ,
1471 last_update_login ,
1472 request_id ,
1473 program_application_id ,
1474 program_id ,
1475 program_update_date ,
1476 interest_type_code ,
1477 level_of_interest ,
1478 level_of_participation ,
1479 dup_person_interest_id ,
1480 sport_indicator ,
1481 activity_source_cd ,
1482 rank
1483 ) VALUES
1484 (
1485 l_sub_interest_type_code,
1486 null ,
1487 null ,
1488 inq_per_extra_rec.interest_name,
1489 null ,
1490 inq_rec.org_id ,
1491 igs_ad_excurr_int_s.nextval ,
1492 l_interface_id ,
1493 null ,
1494 inq_per_extra_rec.start_date ,
1495 inq_per_extra_rec.end_date ,
1496 null ,
1497 '2' ,
1498 null ,
1499 1 ,
1500 sysdate ,
1501 1 ,
1502 sysdate ,
1503 null ,
1504 fnd_global.conc_request_id,
1505 fnd_global.prog_appl_id,
1506 fnd_global.conc_program_id,
1507 sysdate ,
1508 inq_per_extra_rec.interest_type_code ,
1509 null ,
1510 null ,
1511 null ,
1512 null ,
1513 inq_per_extra_rec.activity_source_cd ,
1517 l_status:=0;
1514 null
1515 );
1516 EXCEPTION WHEN OTHERS THEN
1518 fnd_file.put_line(fnd_file.log,'Insert on IGS_AD_EXCURR_INT failed '||SQLERRM);
1519 RAISE;
1520 END;
1521
1522 UPDATE igr_is_extracurr SET status = 'T' WHERE inq_person_id = inq_rec.inq_person_id;
1523 ELSE /* l_status = 0 i.e Validation failed*/
1524 fnd_message.set_name('IGS','IGS_AD_SS_TO_INT_NULL_FAIL');
1525 fnd_message.set_token('TABLE_NAME','igs_ad_excurr_int');
1526 fnd_message.set_token('COL_NAMES',l_tokenstr);
1527 fnd_message.set_token('ID',inq_rec.inq_person_id);
1528 fnd_file.put_line(fnd_file.log,fnd_message.get);
1529 RAISE null_validation_fails;
1530 END IF;
1531 END LOOP;
1532
1533 FOR inq_per_test_rec IN inq_per_test_cur(inq_rec.inq_person_id) LOOP
1534 /* Validate the data in Self Service table for not null before inseting into igs_ad_test_int */
1535 validate_test_int(inq_per_test_rec , l_status,l_tokenstr);
1536 IF l_status = 1 THEN /*Validation Successfull*/
1537 BEGIN
1538 INSERT INTO igs_ad_test_int
1539 (
1540 interface_test_id ,
1541 interface_id ,
1542 admission_test_type ,
1543 registration_number ,
1544 test_date ,
1545 score_report_date ,
1546 edu_level_id ,
1547 score_type ,
1548 score_source_id ,
1549 non_standard_admin ,
1550 special_code ,
1551 status ,
1552 match_ind ,
1553 error_code ,
1554 created_by ,
1555 creation_date ,
1556 last_updated_by ,
1557 last_update_date ,
1558 last_update_login ,
1559 request_id ,
1560 program_application_id ,
1561 program_id ,
1562 program_update_date
1563 ) VALUES
1564 (
1565 igs_ad_test_int_s.nextval,
1566 l_interface_id ,
1567 inq_per_test_rec.admission_test_type,
1568 null ,
1569 inq_per_test_rec.test_date ,
1570 null ,
1571 null ,
1572 null ,
1573 inq_per_test_rec.test_source_id,
1574 null ,
1575 null ,
1576 '2' ,
1577 null ,
1578 null ,
1579 1 ,
1580 sysdate ,
1581 1 ,
1582 sysdate ,
1583 null ,
1584 fnd_global.conc_request_id,
1585 fnd_global.prog_appl_id,
1586 fnd_global.conc_program_id,
1587 sysdate
1588 );
1589 EXCEPTION WHEN OTHERS THEN
1590 l_status:=0;
1591 fnd_file.put_line(fnd_file.log,'Insert on IGS_AD_TEST_INT failed '||SQLERRM);
1592 RAISE;
1593 END;
1594
1595 UPDATE igr_is_test SET status = 'T' WHERE inq_person_id = inq_rec.inq_person_id;
1596 ELSE /* l_status = 0 i.e Validation failed*/
1597 fnd_message.set_name('IGS','IGS_AD_SS_TO_INT_NULL_FAIL');
1598 fnd_message.set_token('TABLE_NAME','igs_ad_test_int');
1599 fnd_message.set_token('COL_NAMES',l_tokenstr);
1600 fnd_message.set_token('ID',inq_rec.inq_person_id);
1601 fnd_file.put_line(fnd_file.log,fnd_message.get);
1602 RAISE null_validation_fails;
1603 END IF;
1604 /*For each of the record from main cursor process the records in this cursor */
1605 FOR inq_per_testseg_rec IN INQ_PER_TESTSEG_CUR(inq_per_test_rec.inq_test_id) LOOP
1606 /* Validate the data in Self Service table for not null before inseting into igs_ad_test_segs_int */
1607 validate_test_segs_int(inq_per_testseg_rec, l_status,l_tokenstr);
1608 IF l_status = 1 THEN
1609 BEGIN
1610 INSERT INTO igs_ad_test_segs_int
1611 (
1612 percentile ,
1613 national_percentile ,
1614 state_percentile ,
1615 latest_official_percentile ,
1616 percentile_year_rank ,
1617 score_band_upper ,
1618 score_band_lower ,
1619 irregularity_code ,
1620 match_ind ,
1621 status ,
1622 error_code ,
1623 created_by ,
1624 creation_date ,
1625 last_updated_by ,
1626 last_update_date ,
1627 last_update_login ,
1628 request_id ,
1629 program_application_id ,
1630 program_id ,
1631 program_update_date ,
1632 interface_testsegs_id ,
1633 interface_test_id ,
1637 ) VALUES
1634 admission_test_type ,
1635 test_segment_id ,
1636 test_score
1638 (
1639 null ,
1640 null ,
1641 null ,
1642 null ,
1643 null ,
1644 null ,
1645 null ,
1646 null ,
1647 null ,
1648 '2' ,
1649 null ,
1650 1 ,
1651 sysdate ,
1652 1 ,
1653 sysdate ,
1654 null ,
1655 fnd_global.conc_request_id ,
1656 fnd_global.prog_appl_id ,
1657 fnd_global.conc_program_id ,
1658 sysdate ,
1659 igs_ad_test_segs_int_s.nextval ,
1660 igs_ad_test_int_s.currval ,
1661 inq_per_test_rec.admission_test_type,
1662 inq_per_testseg_rec.test_segment_id ,
1663 inq_per_testseg_rec.test_score
1664 );
1665 EXCEPTION WHEN OTHERS THEN
1666 l_status:=0;
1667 fnd_file.put_line(fnd_file.log,'Insert on IGS_AD_TEST_SEGS_INT failed '||SQLERRM);
1668 RAISE;
1669 END;
1670
1671 /* After Successful Insertion Update the corresponding Self Service table with STATUS as 'T' */
1672 UPDATE igr_is_testseg SET status = 'T' WHERE inq_test_id = inq_per_test_rec.inq_test_id;
1673 ELSE /* l_status = 0 i.e Validation failed*/
1674 fnd_message.set_name('IGS','IGS_AD_SS_TO_INT_NULL_FAIL');
1675 fnd_message.set_token('TABLE_NAME','igs_ad_test_segs_int');
1676 fnd_message.set_token('COL_NAMES',l_tokenstr);
1677 fnd_message.set_token('ID',inq_per_test_rec.inq_test_id);
1678 fnd_file.put_line(fnd_file.log,fnd_message.get);
1679 RAISE null_validation_fails;
1680 END IF;
1681 END LOOP; /* inner FOR LOOP is closed here */
1682 END LOOP; /* Outer FOR LOOP is closed here */
1683
1684 FOR inq_info_rec IN inq_info_cur(inq_rec.inq_inq_id) loop
1685 /* Validate the data in Self Service table for not null before inseting into igs_ad_inq_pkg_int */
1686 validate_inq_pkg_int(INQ_INFO_rec,l_status,l_tokenstr);
1687 IF l_status = 1 THEN /*Validation Successfull*/
1688 BEGIN
1689 INSERT INTO igr_i_pkg_int
1690 (
1691 interface_inq_pkg_id ,
1692 interface_inq_appl_id ,
1693 package_item_id ,
1694 status ,
1695 match_ind ,
1696 error_code ,
1697 created_by ,
1698 creation_date ,
1699 last_updated_by ,
1700 last_update_date ,
1701 last_update_login ,
1702 request_id ,
1703 program_application_id ,
1704 program_id ,
1705 program_update_date
1706 ) VALUES
1707 (
1708 igr_i_pkg_int_s.nextval ,
1709 igr_i_appl_int_s.currval ,
1710 inq_info_rec.package_item_id ,
1711 '2' ,
1712 null ,
1713 null ,
1714 1 ,
1715 sysdate ,
1716 1 ,
1717 sysdate ,
1718 null ,
1719 fnd_global.conc_request_id ,
1720 fnd_global.prog_appl_id ,
1721 fnd_global.conc_program_id ,
1722 sysdate
1723 );
1724 EXCEPTION WHEN OTHERS THEN
1725 l_status:=0;
1726 fnd_file.put_line(fnd_file.log,'Insert on IGR_I_PKG_INT failed '||SQLERRM);
1727 RAISE;
1728 END;
1729
1730 UPDATE igr_is_info_req SET status = 'T' WHERE inq_inq_id = inq_rec.inq_inq_id;
1731 ELSE /* l_status = 0 i.e Validation failed*/
1732 fnd_message.set_name('IGS','IGS_AD_SS_TO_INT_NULL_FAIL');
1733 fnd_message.set_token('TABLE_NAME','igs_ad_inq_pkg_int');
1734 fnd_message.set_token('COL_NAMES',l_tokenstr);
1735 fnd_message.set_token('ID',inq_rec.inq_person_id);
1736 fnd_file.put_line(fnd_file.log,fnd_message.get);
1737 RAISE null_validation_fails;
1738 END IF;
1739 END LOOP;
1740
1741 FOR inq_lines_rec IN inq_lines_cur(inq_rec.inq_inq_id) LOOP
1742 /* No validation required for the data that is comming from SS Table -
1743 we just need to transfer this data to the interface table */
1744 BEGIN
1745 INSERT INTO igr_i_lines_int
1746 (
1747 interface_lines_id ,
1748 interface_inq_appl_id ,
1749 preference ,
1750 product_category_id ,
1754 error_code ,
1751 product_category_set_id ,
1752 status ,
1753 match_ind ,
1755 created_by ,
1756 creation_date ,
1757 last_updated_by ,
1758 last_update_date ,
1759 last_update_login ,
1760 request_id ,
1761 program_application_id ,
1762 program_id ,
1763 program_update_date
1764 ) VALUES
1765 (
1766 igr_i_lines_int_s.nextval,
1767 igr_i_appl_int_s.currval,
1768 inq_lines_rec.preference,
1769 inq_lines_rec.product_category_id ,
1770 inq_lines_rec.product_category_set_id ,
1771 '2' ,
1772 null ,
1773 null ,
1774 1 ,
1775 sysdate ,
1776 1 ,
1777 sysdate ,
1778 null ,
1779 fnd_global.conc_request_id,
1780 fnd_global.prog_appl_id,
1781 fnd_global.conc_program_id,
1782 sysdate
1783 );
1784 EXCEPTION WHEN OTHERS THEN
1785 l_status:=0;
1786 fnd_file.put_line(fnd_file.log,'Insert on IGR_I_LINES_INT failed '||SQLERRM);
1787 RAISE;
1788 END;
1789
1790 UPDATE igr_is_i_lines SET status = 'T' WHERE inq_inq_id = inq_rec.inq_inq_id;
1791 END LOOP;
1792
1793
1794
1795 --2775931 start
1796 FOR inq_per_race_rec IN inq_per_race_cur(inq_rec.inq_person_id) LOOP
1797 /* Validate the data in Self Service table igr_is_race for not null before inserting into igs_pe_race_int */
1798 /* Validate the data in Self Service table igr_is_race, race_cd column for value from lookup before inserting into igs_pe_race_int */
1799 validate_race_int(inq_per_race_rec,l_status,l_tokenstr);
1800 IF l_status = 1 THEN
1801 BEGIN
1802 INSERT INTO igs_pe_race_int
1803 (
1804 interface_race_id,
1805 interface_id,
1806 race_cd,
1807 status,
1808 match_ind,
1809 error_code,
1810 request_id,
1811 program_id,
1812 program_application_id,
1813 program_update_date,
1814 created_by,
1815 creation_date,
1816 last_updated_by,
1817 last_update_date,
1818 last_update_login
1819 ) VALUES
1820 (
1821 igs_pe_race_int_s.nextval ,
1822 l_interface_id,
1823 inq_per_race_rec.race_cd,
1824 '2',
1825 null,
1826 null,
1827 fnd_global.conc_request_id,
1828 fnd_global.conc_program_id,
1829 fnd_global.prog_appl_id,
1830 sysdate,
1831 1,
1832 sysdate,
1833 1,
1834 sysdate,
1835 null
1836 );
1837 EXCEPTION WHEN OTHERS THEN
1838 l_status:=0;
1839 fnd_file.put_line(fnd_file.log,'Insert on IGS_PE_RACE_INT failed '||SQLERRM);
1840 RAISE;
1841 END;
1842
1843 UPDATE igr_is_race SET status = 'T' WHERE person_id = inq_rec.inq_person_id;
1844 ELSIF l_status = 2 THEN /* l_status = 0 i.e Validation failed due to lookup_code check */
1845 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1846 fnd_message.set_token('TABLE_NAME','igs_pe_race_int');
1847 fnd_message.set_token('COL_NAMES',l_tokenstr);
1848 fnd_message.set_token('ID',inq_rec.inq_person_id);
1849 fnd_file.put_line(fnd_file.log,fnd_message.get);
1850 RAISE null_validation_fails;
1851 ELSE /* l_status = 0 i.e Validation failed due to null check */
1852
1853 fnd_message.set_name('IGS','IGS_AD_SS_TO_INT_NULL_FAIL');
1854 fnd_message.set_token('TABLE_NAME','igs_pe_race_int');
1855 fnd_message.set_token('COL_NAMES',l_tokenstr);
1856 fnd_message.set_token('ID',inq_rec.inq_person_id);
1857 fnd_file.put_line(fnd_file.log,fnd_message.get);
1858
1859 RAISE null_validation_fails;
1860 END IF;
1861 END LOOP;
1862
1863 --2775931 end
1864
1865
1866
1867 IF completed_flag = 'Y' THEN
1868 /* the records into all the master and child tables inserted successfully */
1869 COMMIT;
1870 END IF;
1871 EXCEPTION -- of inner loop
1872 WHEN null_validation_fails THEN
1873 completed_flag := 'N'; /* validation failed for one of the tables */
1874 ROLLBACK TO inqsavepoint;
1875 /* Rollback the insertion of all the records( for the current master record )
1876 and process the next master record */
1877 WHEN OTHERS THEN
1878 ROLLBACK TO inqsavepoint;
1879 END; -- for inner BEGIN
1880 END LOOP; /* The outermost LOOP ends here */
1881
1882 IF l_batch_id IS NULL THEN
1883 fnd_file.put_line(fnd_file.log,'No Self Service Inquiries are available for import.');
1884 END IF;
1885
1886 /* If the Start Date is Greater than End date then Display corresponding message */
1887 ELSE
1888 retcode:=2;
1889 RAISE date_validation_fails;
1890 END IF;
1891
1892 EXCEPTION -- main block
1896 igs_ge_msg_stack.add;
1893 WHEN date_validation_fails THEN
1894 retcode:=2;
1895 fnd_message.set_name('IGS','IGS_AD_STDATE_GT_ENDDATE_FAIL');
1897 fnd_file.put_line(fnd_file.log,fnd_message.get);
1898
1899 WHEN OTHERS THEN
1900 ROLLBACK;
1901 retcode:=2;
1902 ERRBUF := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1903 Igs_Ge_Msg_Stack.CONC_EXCEPTION_HNDL;
1904
1905 END trn_ss_inq_int_data;
1906 END IGR_IMP_001; --End of Package