[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_ACT_ASSESSMENTS_PKG
Source
1 PACKAGE BODY IGS_AD_ACT_ASSESSMENTS_PKG AS
2 /* $Header: IGSADD1B.pls 120.8 2006/04/12 03:09:52 akadam noship $ */
3 /* ------------------------------------------------------------------------------------------------------------------------
4 || Created By :
5 || Created On :
6 || Purpose : Import ACT Assessment Details Process
7 || Known limitations, enhancements or remarks :
8 || Change History :
9 stammine 23-Dec-2004 bug# 4085289
10 IGSQ2UKR:IMPORT ACT ASSESSMENT DETAILS PROCESS DOES NOT IMPORT FOR NON-USCOUNTRY
11 stammine 13-Mar-2005 bug# 4252413 ACT - PLANNED COMPLETION DATE INCORRECT
12 stammine 28-Feb-2005 bug#4693325 Import Act Assessment Details Process Cancels When Unexpected Char
13 Found in National/Local Norm columns of ACT_STATISTICS
14 ---------------------------------------------------------------------------------------------------------------------------*/
15 /******************************************************************************
16 -- GLOBAL VARIABLES AND CONSTANTS
17
18 G_SSN_Person_Id_Type IGS_PE_PERSON_ID_TYP.PERSON_ID_TYPE%TYPE;
19 G_ACT_Person_Id_Type IGS_PE_PERSON_ID_TYP.PERSON_ID_TYPE%TYPE;
20 G_Score_Source_id IGS_AD_CODE_CLASSES.CODE_ID%TYPE;
21 G_Transcript_Source IGS_AD_CODE_CLASSES.CODE_ID%TYPE;
22 G_Grading_Scale IGS_AD_CODE_CLASSES.CODE_ID%TYPE;
23 G_Unit_Difficulty IGS_AD_CODE_CLASSES.CODE_ID%TYPE;
24
25 ***************************************************************************** */
26
27
28 batch_id NUMBER := null;
29
30 /* This function is used to generate the batch_id into the ASSESSMENT Table through SQL Loader Process */
31 FUNCTION get_batch_id RETURN NUMBER AS
32 BEGIN
33 IF batch_id IS NULL THEN
34 SELECT
35 IGS_AD_ACT_ASSESSMENTS_S.nextval
36 INTO batch_id
37 FROM dual;
38 END IF;
39 return batch_id;
40 END get_batch_id;
41
42
43 /* Local function to get the lookup meaning */
44
45 FUNCTION get_lookup_meaning (p_lookup_code igs_lookup_values.lookup_code%type) RETURN VARCHAR2 AS
46 l_lookup_meaning igs_lookup_values.meaning%type;
47
48 CURSOR c_lookup_meaning (cp_lookup_code igs_lookup_values.lookup_code%type) IS
49 SELECT meaning from igs_lookup_values where lookup_code = cp_lookup_code and lookup_type = 'ACT_STATISTIC_CATEGORY';
50 BEGIN
51 l_lookup_meaning:= NULL;
52 OPEN c_lookup_meaning(p_lookup_code);
53 FETCH c_lookup_meaning INTO l_lookup_meaning;
54 CLOSE c_lookup_meaning;
55 RETURN l_lookup_meaning;
56 END get_lookup_meaning;
57
58
59
60
61 /* This function checks the Required setups for importing the data From ACT data store to Interface Tables.
62 Returns the Error code 2 if the Required setup is not met.
63 Returns 1 if the Required setup is met.
64 This function also set the global variables to respective values requried while inserting into Interface Tables. */
65
66 FUNCTION Check_Setups (
67 p_ACT_Batch_Id IN IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
68 p_Reporting_Year IN IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
69 p_Test_Type IN IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
70 p_Test_Date IN IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
71 p_ACT_Id IN IGS_AD_ACT_ASSESSMENTS.act_identifier%type)
72 RETURN NUMBER AS
73
74 l_return NUMBER;
75 -- l_Country_Code VARCHAR2(30);
76
77 -- Cursor to select the Person_ID_Type from IGS_PE_PERSON_ID_TYP where Sys_ID_Type = 'SSN'
78 CURSOR c_ssn_Person_Id_Type IS
79 SELECT
80 PERSON_ID_TYPE
81 FROM IGS_PE_PERSON_ID_TYP
82 WHERE S_PERSON_ID_TYPE = 'SSN'
83 AND CLOSED_IND ='N';
84
85 -- Cursor to select the Person_ID_Type from IGS_PE_PERSON_ID_TYP where Sys_ID_Type = 'ACTID'
86 CURSOR c_Act_Person_Id_Type IS
87 SELECT
88 PERSON_ID_TYPE
89 FROM IGS_PE_PERSON_ID_TYP
90 WHERE S_PERSON_ID_TYPE = 'ACTID'
91 AND CLOSED_IND ='N';
92
93 -- Cursor to check and select the corresponding cod_id for Test_Score_Source = ELEC
94 CURSOR c_Test_Score_Source IS
95 SELECT
96 CODE_ID
97 FROM IGS_AD_CODE_CLASSES
98 WHERE CLASS = 'SYS_SCORE_SOURCE'
99 AND NAME = 'ELEC'
100 AND CLOSED_IND = 'N'
101 AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
102
103 -- Cursor to check and select the corresponding cod_id for Transcript_Source = ACT
104 CURSOR c_Transcript_Source IS
105 SELECT
106 CODE_ID
107 FROM IGS_AD_CODE_CLASSES
108 WHERE CLASS = 'TRANSCRIPT_SOURCE'
109 AND CLOSED_IND = 'N'
110 AND NAME = 'ACT'
111 AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
112
113 -- Cursor to check and select the corresponding cod_id for Grading_Scale = 4 POINT
114 CURSOR c_Grading_Scale IS
115 SELECT
116 CODE_ID
117 FROM IGS_AD_CODE_CLASSES
118 WHERE CLASS = 'GRADING_SCALE_TYPES'
119 AND CLOSED_IND = 'N'
120 AND NAME = '4 POINT'
121 AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
122
123 -- Cursor to check and select the corresponding cod_id for Unit_Difficulty = STANDARD
124 CURSOR c_Unit_Difficulty IS
125 SELECT
126 CODE_ID
127 FROM IGS_AD_CODE_CLASSES
128 WHERE CLASS = 'UNIT_DIFFICULTY'
129 AND CLOSED_IND = 'N'
130 AND NAME = 'STANDARD'
131 AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
132
133 -- Cursor to Check the Assessments Grade Level Setup.
134 CURSOR c_Assessment_grade_level ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
135 cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
136 cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
137 cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
138 cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
139 SELECT
140 DISTINCT Grade_Level LEVEL_OF_QUAL
141 FROM IGS_AD_ACT_ASSESSMENTS actas
142 WHERE actas.ACT_Batch_ID = cp_ACT_Batch_Id
143 AND actas.Reporting_Year like nvl(cp_Reporting_Year,'%')
144 AND actas.Test_Type like nvl(cp_Test_Type,'%')
145 AND actas.TEST_DATE_TXT like nvl(cp_Test_Date,'%')
146 AND actas.ACT_Identifier like nvl(cp_ACT_Id,'%')
147 AND actas.Interface_Transfer_Date IS NULL
148 AND actas.Grade_Level IS NOT NULL
149 MINUS
150 SELECT
151 NAME LEVEL_OF_QUAL
152 FROM IGS_AD_CODE_CLASSES
153 WHERE CLASS ='LEVEL_OF_QUAL'
154 AND CLOSED_IND = 'N'
155 AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
156
157 -- Cursor to Check the Institution Codes Setup.
158 CURSOR c_Assessment_Institution_code ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
159 cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
160 cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
161 cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
162 cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
163 SELECT
164 DISTINCT HIGH_SCHOOL_CODE INSTITUTION_CD
165 FROM IGS_AD_ACT_ASSESSMENTS actas
166 WHERE actas.ACT_Batch_ID = cp_ACT_Batch_Id
167 AND actas.Reporting_Year like nvl(cp_Reporting_Year,'%')
168 AND actas.Test_Type like nvl(cp_Test_Type,'%')
169 AND actas.TEST_DATE_TXT like nvl(cp_Test_Date,'%')
170 AND actas.ACT_Identifier like nvl(cp_ACT_Id,'%')
171 AND actas.Interface_Transfer_Date IS NULL
172 AND actas.High_School_Code IS NOT NULL
173 MINUS
174 SELECT INSTITUTION_CD FROM IGS_OR_INSTITUTION WHERE INSTITUTION_STATUS
175 IN (SELECT INSTITUTION_STATUS FROM IGS_OR_INST_STAT WHERE S_INSTITUTION_STATUS = 'ACTIVE' AND CLOSED_IND = 'N');
176
177
178 -- Cursor to Check the Assessments Test Type Setup.
179 CURSOR c_Assessment_Test_Type ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
180 cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
181 cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
182 cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
183 cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
184 SELECT
185 DISTINCT Decode (actas.Test_Type,'D','ACT-DANTES','F','ACT-INTERNATIONAL','I',
186 'ACT-INSTITUTION','R','ACT-RESIDUAL','S','ACT-STATE','Z','ACT-ARRANGED','N','ACT-NATIONAL') ADMISSION_TEST_TYPE
187 FROM IGS_AD_ACT_ASSESSMENTS actas
188 WHERE actas.ACT_Batch_ID = cp_ACT_Batch_Id
189 AND actas.Reporting_Year like nvl(cp_Reporting_Year,'%')
190 AND actas.Test_Type like nvl(cp_Test_Type,'%')
191 AND actas.TEST_DATE_TXT like nvl(cp_Test_Date,'%')
192 AND actas.ACT_Identifier like nvl(cp_ACT_Id,'%')
193 AND actas.Interface_Transfer_Date IS NULL
194 MINUS
195 SELECT
196 ADMISSION_TEST_TYPE
197 FROM IGS_AD_TEST_TYPE
198 WHERE SCORE_TYPE = 'OFFICIAL';
199
200 -- Cursor to Check the Assessments Test Segments Setup.
201 CURSOR c_Test_Segments (
202 cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
203 cp_reporting_year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
204 cp_test_type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
205 cp_test_date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
206 cp_act_id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
207 SELECT/*+ no_expand */
208 DISTINCT Decode (aas.Test_Type,'D','ACT-DANTES','F','ACT-INTERNATIONAL','I',
209 'ACT-INSTITUTION','R','ACT-RESIDUAL','S','ACT-STATE','Z','ACT-ARRANGED','N','ACT-NATIONAL') ADMISSION_TEST_TYPE,
210 Decode(aas.STATISTIC_TYPE,'TEST','SCORE','TEST_SUB_SCORE','SUB SCORE') SEGMENT_TYPE,
211 lkv.meaning TEST_SEGMENT_NAME
212 FROM IGS_AD_ACT_STATISTICS aas ,
213 IGS_LOOKUP_VALUES lkv
214 WHERE aas.Reporting_Year like nvl(cp_Reporting_Year,'%')
215 AND aas.Test_Type like nvl(cp_Test_Type,'%')
216 AND aas.TEST_DATE_TXT like nvl(cp_Test_Date,'%')
217 AND aas.ACT_Identifier like nvl(cp_ACT_Id,'%')
218 AND aas.STATISTIC_TYPE IN ('TEST','TEST_SUB_SCORE')
219 AND STATISTIC_CATEGORY IN ('COMENGWRI','COMPOSITE','ENGLISH','MATHS','READING','SCIENCE','ALGCOGEOM',
220 'ARTSLIT','ELEMALG','PLGEOTRIG','RHESKILLS','SOCSTSCI' ,'USAGMECH' ,'WRITING')
221 AND trim(aas.SCORE) <> '--'
222 AND lkv.Lookup_code = aas.STATISTIC_CATEGORY
223 AND lkv.lookup_type = 'ACT_STATISTIC_CATEGORY'
224 AND aas.ACT_Identifier in (Select ACT_Identifier from IGS_AD_ACT_ASSESSMENTS where ACT_Batch_ID = cp_ACT_Batch_Id)
225 MINUS
226 SELECT
227 ADMISSION_TEST_TYPE,
228 SEGMENT_TYPE,
229 TEST_SEGMENT_NAME
230 FROM IGS_AD_TEST_SEGMENTS
231 WHERE CLOSED_IND = 'N';
232
233
234 BEGIN
235 l_return := 0;
236
237 -- Stammine commented as part of bug# 4085289
238
239 -- This process is available only if the IGS: Country Code profile value is 'US'
240 /* FND_PROFILE.GET('OSS_COUNTRY_CODE',l_Country_code);
241 IF l_Country_code <> 'US' THEN
242 FND_FILE.PUT_LINE(FND_FILE.LOG, 'This Concurrent Request is not available for non-US Country Profile');
243 return 1;
244 END IF; */
245
246
247 OPEN c_ssn_Person_Id_Type;
248 FETCH c_ssn_Person_Id_Type INTO G_SSN_Person_Id_Type;
249 IF c_ssn_Person_Id_Type%NOTFOUND THEN
250 CLOSE c_ssn_Person_Id_Type;
251 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Social Security Number Person ID Type is not Setup in Person Data Setup.');
252 l_return := 1;
253 ELSE
254 CLOSE c_ssn_Person_Id_Type;
255 END IF;
256
257
258 OPEN c_Act_Person_Id_Type;
259 FETCH c_Act_Person_Id_Type INTO G_ACT_Person_Id_Type;
260 IF c_Act_Person_Id_Type%NOTFOUND THEN
261 CLOSE c_Act_Person_Id_Type;
262 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ACT Identifier Person ID Type is not Setup in Person Data Setup.');
263 l_return := 1;
264 ELSE
265 CLOSE c_Act_Person_Id_Type;
266 END IF;
267
268 OPEN c_Test_Score_Source;
269 FETCH c_Test_Score_Source INTO G_Score_Source_id;
270 IF c_Test_Score_Source%NOTFOUND THEN
271 CLOSE c_Test_Score_Source;
272 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Test Score Source of ELEC is not defined in Test Result Information Setup.');
273 l_return := 1;
274 ELSE
275 CLOSE c_Test_Score_Source;
276 END IF;
277
278 OPEN c_Transcript_Source;
279 FETCH c_Transcript_Source INTO G_Transcript_Source;
280 IF c_Transcript_Source%NOTFOUND THEN
281 CLOSE c_Transcript_Source;
282 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Transcript Source of ACT is not defined in Transcript Information Setup.');
283 l_return := 1;
284 ELSE
285 CLOSE c_Transcript_Source;
286 END IF;
287
288 OPEN c_Grading_Scale;
289 FETCH c_Grading_Scale INTO G_Grading_Scale;
290 IF c_Grading_Scale%NOTFOUND THEN
291 CLOSE c_Grading_Scale;
292 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Grading Scale Type of 4 POINT is not defined in Transcript Information Setup.');
293 l_return := 1;
294 ELSE
295 CLOSE c_Grading_Scale;
296 END IF;
297
298 OPEN c_Unit_Difficulty;
299 FETCH c_Unit_Difficulty INTO G_Unit_Difficulty;
300 IF c_Unit_Difficulty%NOTFOUND THEN
301 CLOSE c_Unit_Difficulty;
302 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unit Difficulty of STANDARD is not defined in Transcript Information Setup.');
303 l_return := 1;
304 ELSE
305 CLOSE c_Unit_Difficulty;
306 END IF;
307
308 FOR l_Admission_Test_Type IN c_Assessment_Test_Type ( p_ACT_Batch_Id, p_Reporting_Year,
309 p_Test_Type , p_Test_Date , p_ACT_Id)
310 LOOP
311 FND_MESSAGE.SET_NAME('IGS','IGS_AD_TEST_TYPE_SETUP');
312 FND_MESSAGE.SET_TOKEN('SETUP', l_Admission_Test_Type.ADMISSION_TEST_TYPE);
313 FND_FILE.PUT_LINE(Fnd_File.LOG,FND_MESSAGE.GET);
314 l_return := 1;
315 END LOOP;
316
317 FOR l_Test_Segments IN c_Test_Segments (p_ACT_Batch_Id, p_Reporting_Year,p_Test_Type , p_Test_Date , p_ACT_Id)
318 LOOP
319 FND_FILE.PUT_LINE(Fnd_File.LOG,'FOR Admission Test Type :'||l_Test_Segments.ADMISSION_TEST_TYPE||' Test Segment Name : '||l_Test_Segments.TEST_SEGMENT_NAME ||
320 ' Segment Type : '|| l_Test_Segments.SEGMENT_TYPE || ' is not/incorrect setup');
321 l_return := 1;
322 END LOOP;
323
324 FOR l_Assessment_grade_level IN c_Assessment_grade_level (p_ACT_Batch_Id, p_Reporting_Year,p_Test_Type , p_Test_Date , p_ACT_Id)
325 LOOP
326 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Level of Qualification : '||l_Assessment_grade_level.LEVEL_OF_QUAL ||' is not/incorrect Setup in Application Detail Codes Data Setup.');
327 l_return := 1;
328 END LOOP;
329
330 FOR l_Assessment_Institution_code IN c_Assessment_Institution_code (p_ACT_Batch_Id, p_Reporting_Year,p_Test_Type , p_Test_Date , p_ACT_Id)
331 LOOP
332 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Institution Code : '||l_Assessment_Institution_code.INSTITUTION_CD ||' is not/incorrect Setup in Organization Structure Setup.');
333 l_return := 1;
334 END LOOP;
335
336 RETURN l_return;
337 END Check_Setups;
338
339
340
341
342 -- Loading the values into Interface tables from the ACT Data Store
343 /* This Procedure Import the Act Data into the OSS tables.
344 Process Stores the ACT Data into Interface tables and
345 call the Import Process to import data from Interface tables to OSS functional Tables. */
346
347 PROCEDURE Insert_ACT_to_Interface (
348 ERRBUF OUT NOCOPY VARCHAR2,
349 RETCODE OUT NOCOPY NUMBER,
350 p_ACT_Batch_Id IN IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
351 p_Source_Type_Id IN NUMBER,
352 p_Match_Set_Id IN NUMBER,
353 p_Reporting_Year IN IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
354 p_Test_Type IN IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
355 p_Test_Date IN IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
356 p_ACT_Id IN IGS_AD_ACT_ASSESSMENTS.act_identifier%type,
357 P_ADDR_USAGE_CD IN IGS_AD_ADDRUSAGE_INT_ALL.SITE_USE_CODE%type)
358
359 AS
360
361 /*----------------------------------------------------------------------------------
362 || Created By : stammine
363 || Created On : 09-Nov-2004
364 || Purpose : This procedure Loads the data into Ad Interface tables from ACT Data Store
365 || Known limitations, enhancements or remarks :
366 || Change History :
367 || Who When What
368 || (reverse chronological order - newest change first)
369 ||--------------------------------------------------------------------------------*/
370
371
372 -- Cusrsor to select the ACT Assessment Recrod
373 CURSOR c_Act_Assessment ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
374 cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
375 cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
376 cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
377 cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
378 SELECT
379 actas.ROWID, actas.*,cc.code_id
380 FROM IGS_AD_ACT_ASSESSMENTS actas,
381 IGS_AD_CODE_CLASSES cc
382 WHERE actas.ACT_Batch_ID = cp_ACT_Batch_Id
383 AND actas.Reporting_Year like nvl(cp_Reporting_Year,'%')
384 AND actas.Test_Type like nvl(cp_Test_Type,'%')
385 AND actas.TEST_DATE_TXT like nvl(cp_Test_Date,'%')
386 AND actas.ACT_Identifier like nvl(cp_ACT_Id,'%')
387 AND actas.Interface_Transfer_Date IS NULL
388 AND cc.name (+) = actas.grade_level
389 AND cc.class (+) = 'LEVEL_OF_QUAL'
390 AND cc.closed_ind (+) = 'N'
391 AND cc.class_type_code (+)='ADM_CODE_CLASSES'
392 ORDER BY actas.act_identifier,
393 actas.TEST_DATE_TXT DESC,
394 actas.High_School_Code,
395 actas.High_School_Graduation;
396
397
398 -- Cursor to select the Test Segnemt Id from IGS_AD_TEST_SEGMENTS
399 CURSOR c_test_segment_id (cp_admission_test_type IGS_AD_TEST_SEGMENTS.ADMISSION_TEST_TYPE%type,
400 cp_test_segment_name IGS_AD_TEST_SEGMENTS.TEST_SEGMENT_NAME%type,
401 cp_segment_type IGS_AD_TEST_SEGMENTS.SEGMENT_TYPE%type) IS
402 SELECT
403 TEST_SEGMENT_ID
404 FROM IGS_AD_TEST_SEGMENTS
405 WHERE ADMISSION_TEST_TYPE = cp_admission_test_type
406 AND UPPER(trim(TEST_SEGMENT_NAME)) = UPPER(trim(cp_test_segment_name))
407 AND SEGMENT_TYPE = cp_segment_type;
408
409 -- Cusror to select the Statistic Category Description from Lookups.
410 CURSOR c_statistic_category_desc (cp_Statistic_category igs_lookup_values.LOOKUP_CODE%type) IS
411 SELECT
412 Description
413 FROM IGS_LOOKUP_VALUES
414 WHERE lookup_TYPE = 'ACT_STATISTIC_CATEGORY'
415 AND Lookup_code = cp_Statistic_category;
416
417 -- Cusrsor to select the ACT Statistics Recrods for Test and Sub Test Statistic Types
418 CURSOR c_Act_Statistic_Test (
419 cp_reporting_year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
420 cp_test_type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
421 cp_test_date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
422 cp_act_id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
423 SELECT
424 *
425 FROM IGS_AD_ACT_STATISTICS
426 WHERE REPORTING_YEAR = cp_reporting_year
427 AND TEST_TYPE = cp_test_type
428 AND TEST_DATE_TXT = cp_test_date
429 AND ACT_Identifier = cp_act_id
430 AND STATISTIC_TYPE IN ('TEST','TEST_SUB_SCORE')
431 AND STATISTIC_CATEGORY IN ('COMENGWRI','COMPOSITE','ENGLISH','MATHS','READING','SCIENCE','ALGCOGEOM',
432 'ARTSLIT','ELEMALG','PLGEOTRIG','RHESKILLS','SOCSTSCI' ,'USAGMECH' ,'WRITING')
433 AND trim(SCORE) <> '--';
434
435
436 -- Cusrsor to select the ACT Statistics Recrods for 'HIGH_SCHOOL' Statistic Types
437 CURSOR c_Act_Statistic_High_School (cp_reporting_year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
438 cp_test_type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
439 cp_test_date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
440 cp_act_id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
441 SELECT
442 *
443 FROM IGS_AD_ACT_STATISTICS
444 WHERE REPORTING_YEAR = cp_reporting_year
445 AND TEST_TYPE = cp_test_type
446 AND TEST_DATE_TXT = cp_test_date
447 AND ACT_Identifier = cp_act_id
448 AND STATISTIC_TYPE = 'HIGH_SCHOOL'
449 AND (COURSE_STATUS IN ('1','2') OR COURSE_STATUS IS NULL); -- exam taken(1) or Planned(2) or Numeric Grades
450
451
452 l_Batch_Id igs_ad_imp_batch_det.batch_id%type;
453 l_Batch_Desc igs_ad_imp_batch_det.batch_desc%type;
454 l_interface_id igs_ad_interface_all.interface_id%type;
455 l_interface_addr_id igs_ad_addr_int_all.interface_addr_id%type;
456 l_interface_test_id igs_ad_test_int.interface_test_id%type;
457 l_admission_test_type igs_ad_test_int.admission_test_type%type;
458 l_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%type;
459 l_Interface_Transcript_Id igs_ad_txcpt_int.interface_transcript_id%type;
460 l_interface_term_dtls_id igs_ad_trmdt_int.interface_term_dtls_id%type;
461
462 l_ACT_Exist igs_Ad_Act_Assessments.act_identifier%type;
463 l_rec_count NUMBER;
464 l_rec_fail_count NUMBER;
465 l_ERRBUF VARCHAR2(1000);
466 l_RETCODE NUMBER;
467 l_Interface_Id_exist igs_ad_interface_all.Interface_Id%type;
468 l_High_School_Code igs_Ad_Act_Assessments.High_School_Code%type;
469 l_High_School_Graduation igs_Ad_Act_Assessments.High_School_Graduation%type;
470
471 l_test_segment_id igs_ad_test_segs_int.test_segment_id%type;
472 l_test_score igs_ad_test_segs_int.test_score%type;
473 l_national_percentile igs_ad_act_statistics.national_norm%type;
474 l_state_percentile igs_ad_act_statistics.local_norm%type;
475 l_score_band_upper igs_ad_test_segs_int.score_band_upper%type;
476 l_score_band_lower igs_ad_test_segs_int.score_band_lower%type;
477 l_grade VARCHAR2(10);
478 l_Statistic_Category_desc igs_lookup_values.MEANING%type;
479
480 BEGIN /* Main */
481
482 -- The following code is added for disabling of OSS in R12.IGS.A - Bug 4955192
483 igs_ge_gen_003.set_org_id(null);
484
485 FND_MESSAGE.SET_NAME('IGS','IGS_AD_ACT_IMPORT');
486 FND_MESSAGE.SET_TOKEN('ACT_BATCH_ID', p_ACT_Batch_Id);
487 FND_FILE.PUT_LINE(Fnd_File.LOG,FND_MESSAGE.GET);
488 FND_FILE.PUT_LINE(Fnd_File.LOG,'Source Type ID : '|| p_Source_Type_Id||' MatchSet Id : ' ||p_Match_Set_Id);
489 FND_FILE.PUT_LINE(Fnd_File.LOG,'Reporting Year : '|| p_Reporting_Year||' Test Type : ' ||p_Test_Type);
490 FND_FILE.PUT_LINE(Fnd_File.LOG,'Test_Date : '|| p_Test_Date||' ACT_Id : ' ||p_ACT_Id);
491 FND_FILE.PUT_LINE(FND_FILE.LOG, '---------------------------------------------------------------------------------------');
492
493 retcode := 0;
494 errbuf := NULL;
495 retcode := Check_Setups(p_ACT_Batch_Id,
496 p_Reporting_Year, p_Test_Type,
497 p_Test_Date,p_ACT_Id);
498 IF retcode = 1 THEN
499 errbuf := 'INVALID SETUP';
500 retcode := 0;
501 RETURN;
502 END IF;
506 l_Batch_Desc := 'Import ACT Assessment Details :'||to_char(sysdate,'MM-DD-YY HH24:MI:SS');
503 -- Insert the Batch_id in to the Batch Table IGS_AD_IMP_BATCH_DET for Import Process
504
505
507 INSERT
508 INTO IGS_AD_IMP_BATCH_DET
509 (
510 BATCH_ID ,
511 BATCH_DESC ,
512 CREATED_BY ,
513 CREATION_DATE ,
514 LAST_UPDATED_BY ,
515 LAST_UPDATE_DATE ,
516 LAST_UPDATE_LOGIN
517 )
518 VALUES
519 (
520 IGS_AD_INTERFACE_BATCH_ID_S.NEXTVAL,
521 l_Batch_Desc,
522 FND_GLOBAL.USER_ID,
523 SYSDATE,
524 FND_GLOBAL.USER_ID,
525 SYSDATE,
526 FND_GLOBAL.LOGIN_ID
527 ) returning BATCH_ID into l_Batch_Id
528 ;
529 commit;
530 FND_FILE.PUT_LINE(FND_FILE.LOG, '----------------------------------------------------------------------------------');
531 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Created a Bacth Record with following details :');
532 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch ID : ' || l_Batch_Id || ' Batch Description : '||l_Batch_Desc);
533 FND_FILE.PUT_LINE(FND_FILE.LOG, '-----------------------------------------------------------------------------------');
534
535
536 BEGIN -- Loading the Data to Interface Tables.
537
538 /*---------------------------------------------------------------------------------------------------------
539 Loading the Data into Interface Tables in the Following Hierarchy:
540 IGS_AD_INTERFACE_ALL
541 --> IGS_AD_ADDR_INT_ALL
542 ------> IGS_AD_ADDRUSAGE_INT_ALL -- akadam Bug# 4352471
543 --> IGS_AD_CONTACTS_INT_ALL
544 --> IGS_AD_API_INT_ALL
545 --> IGS_AD_TEST_INT
546 ------> IGS_AD_TEST_SEGS_INT
547 --> IGS_AD_ACADHIS_INT_ALL
548 ------> IGS_AD_TXCPT_INT_ALL
549 ------------> IGS_AD_TRMDT_INT
550 ------------------> IGS_AD_TUNDT_INT
551 ---------------------------------------------------------------------------------------------------------*/
552
553 -- flag to check if Latest Person Details is already inserted into Interface table or Not
554 l_interface_addr_id := NULL;
555 l_ACT_Exist := NULL;
556 -- flags to check if Academic History Record is already inserted into Interface table or Not
557 l_Interface_Id_exist := NULL;
558 l_High_School_Code := NULL;
559 l_High_School_Graduation := NULL;
560
561
562 -- Record count to commit after every 100 Records and used to count Total Transactions Processed.
563 l_rec_count := 0;
564
565 -- Count Failure Transactions
566 l_rec_fail_count:=0;
567
568 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
569 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Loading the values into Interface tables from the ACT Data Store');
570
571 FOR Act_Assessment_rec IN c_Act_Assessment( p_ACT_Batch_Id ,p_Reporting_Year,
572 p_Test_Type , p_Test_Date , p_ACT_Id )
573 LOOP
574 BEGIN
575
576 SAVEPOINT Transact_spoint; -- Save Point at the start of the transaction,
577 -- is used to Rollback complete transaction for any failure in the transaction
578
579 --debug
580 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside for loop : l_ACT_Exist ' || l_ACT_Exist);
581
582 -- Insert Person Details of Latest Record
583 IF ((l_ACT_Exist IS NULL ) OR ( l_ACT_Exist <> Act_Assessment_rec.act_identifier))
584 THEN
585
586 --debug
587 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside IF of loop : l_ACT Exist ' || l_ACT_Exist);
588
589 -- Inserting the Latest Record in to the Main Interface Table (IGS_AD_INTERFACE_ALL)
590 INSERT
591 INTO IGS_AD_INTERFACE_ALL
592 (
593 INTERFACE_ID ,
594 BATCH_ID ,
595 SOURCE_TYPE_ID ,
596 SURNAME ,
597 MIDDLE_NAME ,
598 GIVEN_NAMES ,
599 SEX ,
600 BIRTH_DT ,
601 LEVEL_OF_QUAL ,
602 STATUS ,
603 RECORD_STATUS ,
604 CREATED_BY ,
605 CREATION_DATE ,
606 LAST_UPDATED_BY ,
607 LAST_UPDATE_DATE ,
608 LAST_UPDATE_LOGIN
609 )
610 VALUES
611 (
612 IGS_AD_INTERFACE_S.NEXTVAL,
613 l_Batch_id,
614 p_Source_Type_Id,
615 Act_Assessment_rec.last_name,
616 Act_Assessment_rec.middle_initial,
617 Act_Assessment_rec.first_name,
618 Decode(upper(Act_Assessment_rec.gender),'M','MALE','F','FEMALE','UNKNOWN'),
619 to_date(Act_Assessment_rec.date_of_birth_txt,'YYYYMMDD'),
620 Act_Assessment_rec.code_id, -- Grade level need to be defined in IGS_AD_CODE_CLASSES
621 --WHERE CLASS ='LEVEL_OF_QUAL' code_id is retrieved from the code_classes table
622 '2',
623 '2',
624 FND_GLOBAL.USER_ID,
625 SYSDATE,
626 FND_GLOBAL.USER_ID,
627 SYSDATE,
628 FND_GLOBAL.LOGIN_ID
629 ) returning INTERFACE_ID into l_interface_id;
630
631 --debug
632 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Interface Id : 1' || l_interface_id);
633
634 -- Inserting the Latest Record in to the Child (IGS_AD_ADDR_INT_ALL) of Interface Table (IGS_AD_INTERFACE_ALL).
635 INSERT
636 INTO IGS_AD_ADDR_INT_ALL
637 (
641 POSTCODE ,
638 INTERFACE_ADDR_ID,
639 INTERFACE_ID ,
640 ADDR_LINE_1 ,
642 CITY ,
643 STATE ,
644 --COUNTY, -- temp
645 COUNTRY ,
646 START_DATE ,
647 STATUS ,
648 CREATED_BY ,
649 CREATION_DATE ,
650 LAST_UPDATED_BY ,
651 LAST_UPDATE_DATE ,
652 LAST_UPDATE_LOGIN
653 )
654 VALUES
655 (
656 IGS_AD_ADDR_INT_S.NEXTVAL,
657 l_interface_id,
658 Act_Assessment_rec.street_address,
659 Act_Assessment_rec.zip_code,
660 Act_Assessment_rec.city, --'ALBANY',
661 Act_Assessment_rec.state_abbreviation, --'NY',
662 --'ALBANY', --temp
663 'US',
664 SYSDATE,
665 '2',
666 FND_GLOBAL.USER_ID,
667 SYSDATE,
668 FND_GLOBAL.USER_ID,
669 SYSDATE,
670 FND_GLOBAL.LOGIN_ID
671 )returning INTERFACE_ADDR_ID into l_interface_addr_id;
672
673 -- akadam Included the below logic for Bug# 4352471
674 --Inserting the Latest Record in to the Child (IGS_AD_ADDRUSAGE_INT_ALL) of Interface Table (IGS_AD_ADDR_INT_ALL).
675
676 IF P_ADDR_USAGE_CD IS NOT NULL THEN
677 INSERT INTO igs_ad_addrusage_int_all (
678 interface_addrusage_id,
679 interface_addr_id,
680 site_use_code,
681 status,
682 created_by,
683 creation_date,
684 last_updated_by,
685 last_update_date,
686 last_update_login
687 )
688 VALUES
689 (
690 igs_ad_addrusage_int_s.NEXTVAL,
691 l_interface_addr_id, -- interfaced Id populated in igs_ad_addr_int_all.
692 p_addr_usage_cd,
693 '2',
694 fnd_global.user_id,
695 sysdate,
696 fnd_global.user_id,
697 sysdate,
698 fnd_global.login_id
699 );
700 END IF;
701
702
703 -- Inserting the Latest Record in to the Child (IGS_AD_CONTACTS_INT_ALL) of Interface Table (IGS_AD_INTERFACE_ALL).
704 IF (LENGTH(RTRIM(LTRIM(Act_Assessment_rec.home_phone,' '),' ')) = 10) THEN
705 INSERT
706 INTO IGS_AD_CONTACTS_INT_ALL
707 (
708 INTERFACE_CONTACTS_ID ,
709 INTERFACE_ID ,
710 CONTACT_POINT_TYPE ,
711 PHONE_LINE_TYPE ,
712 PHONE_AREA_CODE ,
713 PHONE_NUMBER ,
714 STATUS ,
715 CREATED_BY ,
716 CREATION_DATE ,
717 LAST_UPDATED_BY ,
718 LAST_UPDATE_DATE ,
719 LAST_UPDATE_LOGIN
720 )
721 VALUES
722 (
723 IGS_AD_CONTACTS_INT_S.NEXTVAL,
724 l_interface_id,
725 'PHONE', -- COMMUNICATION_TYPE
726 'GEN' ,-- Telephone PHONE_LINE_TYPE
727 SUBSTR(Act_Assessment_rec.home_phone,1,3),
728 SUBSTR(Act_Assessment_rec.home_phone,4,7),
729 '2',
730 FND_GLOBAL.USER_ID,
731 SYSDATE,
732 FND_GLOBAL.USER_ID,
733 SYSDATE,
734 FND_GLOBAL.LOGIN_ID
735 );
736 ELSE
737 IF (Act_Assessment_rec.home_phone IS NOT NULL) THEN
738 INSERT
739 INTO IGS_AD_CONTACTS_INT_ALL
740 (
741 INTERFACE_CONTACTS_ID ,
742 INTERFACE_ID ,
743 CONTACT_POINT_TYPE ,
744 PHONE_LINE_TYPE ,
745 PHONE_NUMBER ,
746 STATUS ,
747 CREATED_BY ,
748 CREATION_DATE ,
749 LAST_UPDATED_BY ,
750 LAST_UPDATE_DATE ,
751 LAST_UPDATE_LOGIN
752 )
753 VALUES
754 (
755 IGS_AD_CONTACTS_INT_S.NEXTVAL,
756 l_interface_id,
757 'PHONE', -- COMMUNICATION_TYPE
758 'GEN' ,-- Telephone PHONE_LINE_TYPE
759 Act_Assessment_rec.home_phone,
760 '2',
761 FND_GLOBAL.USER_ID,
762 SYSDATE,
763 FND_GLOBAL.USER_ID,
764 SYSDATE,
765 FND_GLOBAL.LOGIN_ID
766 );
767 END IF;
768 END IF;
769 -- Check if the ALTERNATE_ID is SSN or -ACTID
770 -- IF ALTERNATE_ID is SSN Then Add the two records. 1 with SSN person_id_type and other with ACT person_id_type
771
772 IF (SUBSTR(Act_Assessment_rec.act_identifier,1,1) <> '-') THEN
773 -- Inserting SSN Person_id_type
774 INSERT
775 INTO IGS_AD_API_INT_ALL
776 (
777 INTERFACE_API_ID ,
778 INTERFACE_ID ,
782 CREATED_BY ,
779 PERSON_ID_TYPE ,
780 ALTERNATE_ID ,
781 STATUS ,
783 CREATION_DATE ,
784 LAST_UPDATED_BY ,
785 LAST_UPDATE_DATE ,
786 LAST_UPDATE_LOGIN
787 )
788 VALUES
789 (
790 IGS_AD_API_INT_S.nextval,
791 l_interface_id,
792 G_SSN_Person_Id_Type, -- 'SSN', -- {get the person_type having the System Person Type = SSN}
793 Act_Assessment_rec.act_identifier,
794 '2',
795 FND_GLOBAL.USER_ID,
796 SYSDATE,
797 FND_GLOBAL.USER_ID,
798 SYSDATE,
799 FND_GLOBAL.LOGIN_ID
800 );
801 END IF;
802
803 --debug
804 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Interface Id : 2 ' || l_interface_id);
805
806 -- Inserting ACT Person_id_type
807 INSERT
808 INTO IGS_AD_API_INT_ALL
809 (
810 INTERFACE_API_ID ,
811 INTERFACE_ID ,
812 PERSON_ID_TYPE ,
813 ALTERNATE_ID ,
814 STATUS ,
815 CREATED_BY ,
816 CREATION_DATE ,
817 LAST_UPDATED_BY ,
818 LAST_UPDATE_DATE ,
819 LAST_UPDATE_LOGIN
820 )
821 VALUES
822 (
823 IGS_AD_API_INT_S.nextval,
824 l_interface_id,
825 G_ACT_Person_Id_Type, -- 'ACTID', -- {get the person_type having the System Person Type = ACT}
826 SUBSTR(Act_Assessment_rec.act_identifier,(Decode(SUBSTR(Act_Assessment_rec.act_identifier,1,1),'-',2,1))),
827 '2',
828 FND_GLOBAL.USER_ID,
829 SYSDATE,
830 FND_GLOBAL.USER_ID,
831 SYSDATE,
832 FND_GLOBAL.LOGIN_ID
833 );
834
835 l_ACT_Exist := Act_Assessment_rec.act_identifier;
836
837 END IF; -- End Insert Most Recent Person Details
838
839 -- Loading Test Detials in to Interface Tables.
840 -- Multiple Records
841 -- For each Test Type :
842 --debug
843 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Interface Id 3: ' || l_interface_id);
844 INSERT
845 INTO IGS_AD_TEST_INT
846 (
847 INTERFACE_TEST_ID ,
848 INTERFACE_ID ,
849 ADMISSION_TEST_TYPE ,
850 TEST_DATE ,
851 SCORE_TYPE ,
852 SCORE_SOURCE_ID ,
853 SCORE_REPORT_DATE ,
854 SPECIAL_CODE ,
855 STATUS ,
856 CREATED_BY ,
857 CREATION_DATE ,
858 LAST_UPDATED_BY ,
859 LAST_UPDATE_DATE ,
860 LAST_UPDATE_LOGIN,
861 ACTIVE_IND
862 )
863 VALUES
864 (
865 IGS_AD_TEST_INT_S.NEXTVAL,
866 l_interface_id,
867 Decode (Act_Assessment_rec.test_type,'D','ACT-DANTES','F','ACT-INTERNATIONAL','I',
868 'ACT-INSTITUTION','R','ACT-RESIDUAL','S','ACT-STATE','Z','ACT-ARRANGED','N','ACT-NATIONAL'),
869 to_date( Act_Assessment_rec.test_date_txt||'01','YYYYMMDD'),
870 'OFFICIAL',
871 G_Score_Source_id,
872 SYSDATE,
873 Act_Assessment_rec.corrected_report_ind, -- if the data is ' ' in Data file value in the Assessments table is NULL
874 '2',
875 FND_GLOBAL.USER_ID,
876 SYSDATE,
877 FND_GLOBAL.USER_ID,
878 SYSDATE,
879 FND_GLOBAL.LOGIN_ID,
880 'Y'
881 ) returning INTERFACE_TEST_ID,ADMISSION_TEST_TYPE into l_interface_test_id,l_admission_test_type;
882
883 -- Enter Test Details into IGS_AD_TEST_SEGS_INT from ACT Statistics
884 -- For Inserting multiple records of Test Details for a Person for each Test Type
885
886 FOR Act_Statistic_Rec IN c_Act_Statistic_Test (Act_Assessment_rec.reporting_year,
887 Act_Assessment_rec.test_type,
888 Act_Assessment_rec.TEST_DATE_TXT,
889 Act_Assessment_rec.act_identifier)
890 LOOP
891
892
893 --debug
894 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside for of Statistic record Interface Id 4: ' || l_interface_id);
895 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside for of Statistic record l_admission_test_type ' || l_admission_test_type);
896 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside for of Statistic record Act_Statistic_rec.statistic_category ' || Act_Statistic_rec.statistic_category );
897 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside for of Statistic record Act_Statistic_rec.statistic_type ' || Act_Statistic_rec.statistic_type);
898 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside for of Statistic record get_lookup_meaning() ' || get_lookup_meaning(Act_Statistic_rec.statistic_category));
899
900 l_test_segment_id := NULL;
901 l_test_score := NULL;
902 l_national_percentile := NULL;
903 l_state_percentile := NULL;
904 l_score_band_upper := NULL;
905 l_score_band_lower := NULL;
906
907
908 /* -- { Case 1 }
909 -- STATISTIC_TYPE = 'Test' and STATISTIC_CATEGORY = 'Combined English/Writing'
910 -- IF ACT_STATISTIC.SCORE <> '-'
914 -- { Case 2 }
911 -- Select TEST_SEGMENT_ID From IGS_AD_TEST_SEGMENTS Where ADMISSION_TEST_TYPE = IGS_AD_TEST_INT. ADMISSION_TEST_TYPE
912 -- and TEST_SEGMENT_NAME = 'Combined English/Writing' Set to IGS_AD_TEST_SEGMENTS. TEST_SEGMENT_ID
913
915 -- STATISTIC_TYPE = 'Test' and STATISTIC_CATEGORY = 'Composite'
916 -- IF ACT_STATISTIC. SCORE <> '-'
917 -- Select TEST_SEGMENT_ID From IGS_AD_TEST_SEGMENTS Where ADMISSION_TEST_TYPE = IGS_AD_TEST_INT. ADMISSION_TEST_TYPE
918 -- and TEST_SEGMENT_NAME = 'Composite' Set to IGS_AD_TEST_SEGMENTS. TEST_SEGMENT_ID
919
920 -- { Case 3 }
921 -- STATISTIC_TYPE = 'Test' and STATISTIC_CATEGORY = 'English'
922 -- IF ACT_STATISTIC.SCORE <> '-'
923 -- Select TEST_SEGMENT_ID From IGS_AD_TEST_SEGMENTS Where ADMISSION_TEST_TYPE = IGS_AD_TEST_INT. ADMISSION_TEST_TYPE
924 -- and TEST_SEGMENT_NAME = 'English' Set to IGS_AD_TEST_SEGMENTS. TEST_SEGMENT_ID
925
926
927 -- { Case 4 }
928 -- STATISTIC_TYPE = 'Test' and STATISTIC_CATEGORY = 'Mathematics'
929 -- IF ACT_STATISTIC.SCORE <> '-'
930 -- Select TEST_SEGMENT_ID From IGS_AD_TEST_SEGMENTS Where ADMISSION_TEST_TYPE = IGS_AD_TEST_INT. ADMISSION_TEST_TYPE
931 -- and TEST_SEGMENT_NAME = 'Mathematics' Set to IGS_AD_TEST_SEGMENTS. TEST_SEGMENT_ID
932
933 -- { Case 5 }
934 -- STATISTIC_TYPE = 'Test' and STATISTIC_CATEGORY = 'Reading'
935 -- IF ACT_STATISTIC.SCORE <> '-'
936 -- Select TEST_SEGMENT_ID From IGS_AD_TEST_SEGMENTS Where ADMISSION_TEST_TYPE = IGS_AD_TEST_INT. ADMISSION_TEST_TYPE
937 -- and TEST_SEGMENT_NAME = 'Reading' Set to IGS_AD_TEST_SEGMENTS. TEST_SEGMENT_ID
938
939 -- { Case 6 }
940 -- STATISTIC_TYPE = 'Test' and STATISTIC_CATEGORY = 'Science'
941 -- IF ACT_STATISTIC.SCORE <> '-'
942 -- Select TEST_SEGMENT_ID From IGS_AD_TEST_SEGMENTS Where ADMISSION_TEST_TYPE = IGS_AD_TEST_INT. ADMISSION_TEST_TYPE
943 -- and TEST_SEGMENT_NAME = 'Science' Set to IGS_AD_TEST_SEGMENTS. TEST_SEGMENT_ID */
944
945 IF Act_Statistic_rec.statistic_type = 'TEST' AND Act_Statistic_rec.statistic_category IN ('COMENGWRI','COMPOSITE','ENGLISH',
946 'MATHS','READING','SCIENCE') THEN
947 FOR test_segment_rec IN c_test_segment_id(l_admission_test_type,get_lookup_meaning(Act_Statistic_rec.statistic_category),'SCORE')
948 LOOP
949 l_test_score := Act_Statistic_rec.score;
950 l_national_percentile := Act_Statistic_rec.national_norm;
951 l_state_percentile := NULL;
952 l_score_band_upper := 36;
953 l_score_band_lower := 1;
954 l_test_segment_id:=test_segment_rec.TEST_SEGMENT_ID;
955 END LOOP;
956
957
958 /* -- { Case 7 }
959 -- STATISTIC_TYPE = 'TEST_SUB_SCORE' and STATISTIC_CATEGORY = 'Alg/Cord Geom'
960 -- IF ACT_STATISTIC.SCORE <> '-'
961 -- Select TEST_SEGMENT_ID From IGS_AD_TEST_SEGMENTS Where ADMISSION_TEST_TYPE = IGS_AD_TEST_INT. ADMISSION_TEST_TYPE
962 -- and TEST_SEGMENT_NAME = 'Alg/Cord Geom' Set to IGS_AD_TEST_SEGMENTS. TEST_SEGMENT_ID
963
964 -- { Case 8 }
965 -- STATISTIC_TYPE = 'TEST_SUB_SCORE' and STATISTIC_CATEGORY = 'Arts/Lit.'
966 -- IF ACT_STATISTIC.SCORE <> '-'
967 -- Select TEST_SEGMENT_ID From IGS_AD_TEST_SEGMENTS Where ADMISSION_TEST_TYPE = IGS_AD_TEST_INT. ADMISSION_TEST_TYPE
968 -- and TEST_SEGMENT_NAME = 'Arts/Lit.' Set to IGS_AD_TEST_SEGMENTS. TEST_SEGMENT_ID
969
970 -- { Case 9 }
971 -- STATISTIC_TYPE = 'TEST_SUB_SCORE' and STATISTIC_CATEGORY = 'Elem Algebra.'
972 -- IF ACT_STATISTIC.SCORE <> '-'
973 -- Select TEST_SEGMENT_ID From IGS_AD_TEST_SEGMENTS Where ADMISSION_TEST_TYPE = IGS_AD_TEST_INT. ADMISSION_TEST_TYPE
974 -- and TEST_SEGMENT_NAME = 'Elem Algebra.' Set to IGS_AD_TEST_SEGMENTS. TEST_SEGMENT_ID
975
976 -- { Case 10 }
977 -- STATISTIC_TYPE = 'TEST_SUB_SCORE' and STATISTIC_CATEGORY = 'Plane Geom/Trig.'
978 -- IF ACT_STATISTIC.SCORE <> '-'
979 -- Select TEST_SEGMENT_ID From IGS_AD_TEST_SEGMENTS Where ADMISSION_TEST_TYPE = IGS_AD_TEST_INT. ADMISSION_TEST_TYPE
980 -- and TEST_SEGMENT_NAME = 'Plane Geom/Trig' Set to IGS_AD_TEST_SEGMENTS. TEST_SEGMENT_ID
981
982 -- { Case 11 }
983 -- STATISTIC_TYPE = 'TEST_SUB_SCORE' and STATISTIC_CATEGORY = 'Rhetorical Skills.'
984 -- IF ACT_STATISTIC.SCORE <> '-'
985 -- Select TEST_SEGMENT_ID From IGS_AD_TEST_SEGMENTS Where ADMISSION_TEST_TYPE = IGS_AD_TEST_INT. ADMISSION_TEST_TYPE
986 -- and TEST_SEGMENT_NAME = 'Rhetorical Skills' Set to IGS_AD_TEST_SEGMENTS. TEST_SEGMENT_ID
987
988 -- { Case 12 }
989 -- STATISTIC_TYPE = 'TEST_SUB_SCORE' and STATISTIC_CATEGORY = 'Soc Stud/Sci.'
990 -- IF ACT_STATISTIC.SCORE <> '-'
991 -- Select TEST_SEGMENT_ID From IGS_AD_TEST_SEGMENTS Where ADMISSION_TEST_TYPE = IGS_AD_TEST_INT. ADMISSION_TEST_TYPE
992 -- and TEST_SEGMENT_NAME = 'Soc Stud/Sci' Set to IGS_AD_TEST_SEGMENTS. TEST_SEGMENT_ID
993
994 -- { Case 13 }
995 -- STATISTIC_TYPE = 'TEST_SUB_SCORE' and STATISTIC_CATEGORY = 'Usage/Mech.'
996 -- IF ACT_STATISTIC.SCORE <> '-'
997 -- Select TEST_SEGMENT_ID From IGS_AD_TEST_SEGMENTS Where ADMISSION_TEST_TYPE = IGS_AD_TEST_INT. ADMISSION_TEST_TYPE
998 -- and TEST_SEGMENT_NAME = 'Usage/Mech' Set to IGS_AD_TEST_SEGMENTS. TEST_SEGMENT_ID */
999
1000 ELSIF Act_Statistic_rec.statistic_type = 'TEST_SUB_SCORE' AND Act_Statistic_rec.statistic_category IN ('ALGCOGEOM',
1001 'ARTSLIT','ELEMALG','PLGEOTRIG','RHESKILLS','SOCSTSCI' ,'USAGMECH') THEN
1002 FOR test_segment_rec IN c_test_segment_id(l_admission_test_type,get_lookup_meaning(Act_Statistic_rec.statistic_category),'SUB SCORE')
1003 LOOP
1004 l_test_score := Act_Statistic_rec.score;
1005 l_national_percentile := Act_Statistic_rec.national_norm;
1006 l_state_percentile := Act_Statistic_rec.local_norm;
1007 l_score_band_upper := 18;
1008 l_score_band_lower := 1;
1009 l_test_segment_id:=test_segment_rec.TEST_SEGMENT_ID;
1010 END LOOP;
1011
1012 -- { Case 14 }
1013 -- STATISTIC_TYPE = 'TEST_SUB_SCORE' and STATISTIC_CATEGORY = 'Writing.'
1014 -- IF ACT_STATISTIC.SCORE <> '-'
1015 -- Select TEST_SEGMENT_ID From IGS_AD_TEST_SEGMENTS Where ADMISSION_TEST_TYPE = IGS_AD_TEST_INT. ADMISSION_TEST_TYPE
1016 -- and TEST_SEGMENT_NAME = 'Writing' Set to IGS_AD_TEST_SEGMENTS. TEST_SEGMENT_ID
1017
1018 ELSIF Act_Statistic_rec.statistic_type = 'TEST_SUB_SCORE' AND Act_Statistic_rec.statistic_category = 'WRITING' THEN
1019 for test_segment_rec IN c_test_segment_id(l_admission_test_type,get_lookup_meaning('WRITING'),'SUB SCORE')
1020 LOOP
1021 l_test_score := Act_Statistic_rec.score;
1022 l_national_percentile := Act_Statistic_rec.national_norm;
1023 l_state_percentile := NULL;
1024 l_score_band_upper := 12;
1025 l_score_band_lower := 2;
1026 l_test_segment_id:=test_segment_rec.TEST_SEGMENT_ID;
1027 END LOOP;
1028 END IF;
1029
1030 INSERT
1031 INTO IGS_AD_TEST_SEGS_INT
1032 (
1033 INTERFACE_TESTSEGS_ID ,
1034 INTERFACE_TEST_ID ,
1035 ADMISSION_TEST_TYPE ,
1036 TEST_SEGMENT_ID ,
1037 TEST_SCORE ,
1038 NATIONAL_PERCENTILE ,
1039 STATE_PERCENTILE ,
1040 SCORE_BAND_UPPER ,
1041 SCORE_BAND_LOWER ,
1042 STATUS ,
1043 CREATED_BY ,
1044 CREATION_DATE ,
1045 LAST_UPDATED_BY ,
1046 LAST_UPDATE_DATE ,
1047 LAST_UPDATE_LOGIN
1048 )
1049 VALUES
1050 (
1051 IGS_AD_TEST_SEGS_INT_S.NEXTVAL,
1052 l_interface_test_id,
1053 l_admission_test_type,
1054 l_test_segment_id,
1055 l_test_score,
1056 DECODE(INSTR(l_national_percentile,'-'),0,to_number(l_national_percentile),NULL), --l_national_percentile bug#4693325
1057 DECODE(INSTR(l_state_percentile,'-'),0,to_number(l_state_percentile),NULL), --l_state_percentile
1058 l_score_band_upper,
1059 l_score_band_lower,
1060 '2',
1061 FND_GLOBAL.USER_ID,
1062 SYSDATE,
1063 FND_GLOBAL.USER_ID,
1064 SYSDATE,
1065 FND_GLOBAL.LOGIN_ID
1066 );
1067
1068 END LOOP; --- End Inserting multiple records of Test Details for a Person
1069
1070 -- Enter Acad History Details into IGS_AD_ACADHIS_INT_ALL from ACT Statistics
1071 IF Act_Assessment_Rec.high_school_code IS NULL THEN
1072 FND_FILE.PUT_LINE(Fnd_File.LOG,'Academic History for the follwing Records will not be imported as High School Graduation Institution Code is not defined.');
1073 FND_FILE.PUT_LINE(Fnd_File.LOG,'ACT_ID : '|| Act_Assessment_Rec.ACT_Identifier ||' REPORTING_YEAR :'|| Act_Assessment_Rec.Reporting_Year||' TEST_DATE : '||Act_Assessment_Rec.Test_Date_Txt||' TEST_TYPE : '||Act_Assessment_Rec.Test_Type);
1074 ELSE
1075 IF (((l_Interface_Id_exist IS NULL ) OR ( l_Interface_Id_exist <> l_interface_id)) OR NOT
1076 (( l_High_School_Code = Act_Assessment_Rec.high_school_code)
1077 AND( l_High_School_Graduation = Act_Assessment_Rec.high_school_graduation))) THEN
1078
1079 --debug
1080 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside IF of Acad History Details record Interface Id 5: ' || l_interface_id);
1081
1082 INSERT
1083 INTO IGS_AD_ACADHIS_INT_ALL
1084 (
1085 INTERFACE_ACADHIS_ID ,
1086 INTERFACE_ID ,
1087 INSTITUTION_CODE ,
1088 CURRENT_INST ,
1089 PLANNED_COMPLETION_DATE ,
1090 SELFREP_INST_GPA ,
1091 STATUS ,
1092 CREATED_BY ,
1093 CREATION_DATE ,
1094 LAST_UPDATED_BY ,
1095 LAST_UPDATE_DATE ,
1096 LAST_UPDATE_LOGIN
1097 )
1098 VALUES
1099 (
1100 IGS_AD_ACADHIS_INT_S.NEXTVAL,
1101 l_interface_id,
1102 Act_Assessment_Rec.high_school_code,
1103 'N',
1104 DECODE(Act_Assessment_Rec.high_school_graduation,'',NULL,to_date(Act_Assessment_Rec.high_school_graduation||'0501','YYYYMMDD')),
1105 -- changed planned completion date from 01-jan to 01-may bug# 4252413
1106 DECODE(INSTR(Act_Assessment_Rec.high_school_average,'-'),0,Act_Assessment_Rec.high_school_average,NULL),
1107 '2',
1108 FND_GLOBAL.USER_ID,
1109 SYSDATE,
1110 FND_GLOBAL.USER_ID,
1111 SYSDATE,
1112 FND_GLOBAL.LOGIN_ID
1116 l_High_School_Graduation := Act_Assessment_Rec.high_school_graduation;
1113 ) returning INTERFACE_ACADHIS_ID into l_interface_acadhis_id;
1114
1115 l_High_School_Code := Act_Assessment_Rec.high_school_code;
1117 l_Interface_Id_exist := l_interface_id;
1118 -- Insert data into detail tables
1119 -- in following Hierarchy
1120 -- Setup the Transcript Source User Defined Transcript Source of System Type 'ACT'
1121 --------> IGS_AD_TXCPT_INT_ALL
1122 --------------> IGS_AD_TRMDT_INT
1123 --------------------> IGS_AD_TUNDT_INT
1124
1125 -- Enter Test Details into IGS_AD_TXCPT_INT from ACT Statistics
1126
1127 --debug
1128 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside IF of Acad History Details: IGS_AD_TXCPT_INT record Interface Id 5: ' || l_interface_id);
1129
1130 INSERT
1131 INTO IGS_AD_TXCPT_INT
1132 (
1133 INTERFACE_TRANSCRIPT_ID ,
1134 TRANSCRIPT_STATUS ,
1135 TRANSCRIPT_TYPE ,
1136 TRANSCRIPT_SOURCE ,
1137 INTERFACE_ACADHIS_ID ,
1138 STATUS ,
1139 ENTERED_GS_ID ,
1140 CONV_GS_ID ,
1141 TERM_TYPE ,
1142 CREATED_BY ,
1143 CREATION_DATE ,
1144 LAST_UPDATED_BY ,
1145 LAST_UPDATE_DATE ,
1146 LAST_UPDATE_LOGIN ,
1147 DATE_OF_ISSUE
1148 )
1149 VALUES
1150 (
1151 IGS_AD_TXCPT_INT_S.NEXTVAL,
1152 'FINAL', -- Lookup code for lookup_type = TRANSCRIPT_STATUS
1153 'OFFICIAL', -- Lookup code for lookup_type = TRANSCRIPT_TYPE
1154 G_Transcript_Source, --User Defined 'ACT' Transcript Info Code Class 'Transcript Source'
1155 l_interface_acadhis_id,
1156 '2',
1157 G_Grading_Scale, -- User Defined '4 POINT' Transcript Info Code Class 'Grading Scale Types'
1158 G_Grading_Scale, -- User Defined '4 POINT' Transcript Info Code Class 'Grading Scale Types'
1159 'S', -- Term Type is Defined as 'S' for Semister in lookup for TERM_TYPE
1160 FND_GLOBAL.USER_ID,
1161 SYSDATE,
1162 FND_GLOBAL.USER_ID,
1163 SYSDATE,
1164 FND_GLOBAL.LOGIN_ID,
1165 to_date( Act_Assessment_rec.TEST_DATE_TXT||'01','YYYYMMDD')
1166 ) returning INTERFACE_TRANSCRIPT_ID into l_Interface_Transcript_Id ;
1167
1168 --debug
1169 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside IF of Acad History Details: IGS_AD_TRMDT_INT record Interface Id 5: ' || l_interface_id);
1170
1171 INSERT
1172 INTO IGS_AD_TRMDT_INT
1173 (
1174 INTERFACE_TERM_DTLS_ID ,
1175 INTERFACE_TRANSCRIPT_ID ,
1176 START_DATE ,
1177 END_DATE ,
1178 TERM ,
1179 STATUS ,
1180 CREATED_BY ,
1181 CREATION_DATE ,
1182 LAST_UPDATED_BY ,
1183 LAST_UPDATE_DATE ,
1184 LAST_UPDATE_LOGIN
1185 )
1186 VALUES
1187 (
1188 igs_ad_trmdt_int_s.NEXTVAL,
1189 l_Interface_Transcript_Id,
1190 to_date( Act_Assessment_rec.TEST_DATE_TXT||'01','YYYYMMDD'),
1191 to_date( Act_Assessment_rec.TEST_DATE_TXT||'02','YYYYMMDD'),
1192 'ACT - '||to_char(to_date( Act_Assessment_rec.TEST_DATE_TXT||'01','YYYYMMDD'),'Mon DD, YYYY'), -- 'ACT Test Date - '||to_char(to_date( Act_Assessment_rec.TEST_DATE_TXT||'01','YYYYMMDD'),'Month DD, YYYY'),
1193 '2',
1194 FND_GLOBAL.USER_ID,
1195 SYSDATE,
1196 FND_GLOBAL.USER_ID,
1197 SYSDATE,
1198 FND_GLOBAL.LOGIN_ID
1199 )returning INTERFACE_TERM_DTLS_ID into l_interface_term_dtls_id;
1200
1201 END IF; -- END IF of Graduation School Detials
1202
1203 FOR Act_Statistic_HS_Rec IN c_Act_Statistic_High_School
1204 (Act_Assessment_rec.reporting_year,
1205 Act_Assessment_rec.test_type,
1206 Act_Assessment_rec.TEST_DATE_TXT,
1207 Act_Assessment_rec.act_identifier)
1208 LOOP
1209 l_grade :=NULL;
1210
1211 OPEN c_Statistic_Category_desc(Act_Statistic_HS_Rec.statistic_category);
1212 FETCH c_Statistic_Category_desc INTO l_Statistic_Category_desc;
1213 CLOSE c_Statistic_Category_desc;
1214
1215 --debug
1216 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside FOR of History Details: IGS_AD_TUNDT_INT record Interface Id 5: ' || l_interface_id);
1217
1218
1219 IF Act_Statistic_HS_Rec.statistic_category IN ('ENGLISH','MATHS','NATSCI','SOCSCI') THEN
1220 -- If Numeric Grade is 'NN' not NULL not '--'
1221 IF ((Act_Statistic_HS_Rec.Numeric_Grade IS NOT NULL) AND (INSTR(Act_Statistic_HS_Rec.Numeric_Grade,'-')=0)) THEN
1222 l_grade := SUBSTR(Act_Statistic_HS_Rec.Numeric_Grade,1,1)||'.'||SUBSTR(Act_Statistic_HS_Rec.Numeric_Grade,2);
1223 INSERT
1224 INTO IGS_AD_TUNDT_INT
1225 (
1226 INTERFACE_TERM_UNITDTLS_ID ,
1227 INTERFACE_TERM_DTLS_ID ,
1228 UNIT ,
1229 UNIT_DIFFICULTY ,
1230 UNIT_NAME ,
1231 GRADE ,
1232 STATUS ,
1233 CREATED_BY ,
1234 CREATION_DATE ,
1235 LAST_UPDATED_BY ,
1236 LAST_UPDATE_DATE ,
1237 LAST_UPDATE_LOGIN
1238 )
1239 VALUES
1240 (
1241 IGS_AD_TUNDT_INT_S.NEXTVAL,
1242 l_interface_term_dtls_id,
1243 Act_Statistic_HS_Rec.statistic_category ,
1244 G_Unit_Difficulty,
1245 l_Statistic_Category_desc,
1246 l_grade,
1247 '2',
1248 FND_GLOBAL.USER_ID,
1249 SYSDATE,
1250 FND_GLOBAL.USER_ID,
1251 SYSDATE,
1252 FND_GLOBAL.LOGIN_ID
1253 ) ;
1254
1255 END IF;
1256 ELSIF Act_Statistic_HS_Rec.course_status IN ('1','2') THEN
1257 l_grade := Act_Statistic_HS_Rec.Grade_Earned;
1258 IF ((l_grade IS NULL) AND (Act_Statistic_HS_Rec.course_status = '1')) THEN
1259 l_grade :='T';
1260 END IF;
1261
1262 INSERT
1263 INTO IGS_AD_TUNDT_INT
1264 (
1265 INTERFACE_TERM_UNITDTLS_ID ,
1266 INTERFACE_TERM_DTLS_ID ,
1267 UNIT ,
1268 UNIT_DIFFICULTY ,
1269 UNIT_NAME ,
1270 GRADE ,
1271 STATUS ,
1272 CREATED_BY ,
1273 CREATION_DATE ,
1274 LAST_UPDATED_BY ,
1275 LAST_UPDATE_DATE ,
1276 LAST_UPDATE_LOGIN
1277 )
1278 VALUES
1279 (
1280 IGS_AD_TUNDT_INT_S.NEXTVAL,
1281 l_interface_term_dtls_id,
1282 Act_Statistic_HS_Rec.statistic_category ,
1283 G_Unit_Difficulty,
1284 l_Statistic_Category_desc,
1285 Decode(trim(l_grade),'4','A','3','B','2','C','1','D','0','F','','P',l_grade),
1286 '2',
1287 FND_GLOBAL.USER_ID,
1288 SYSDATE,
1289 FND_GLOBAL.USER_ID,
1290 SYSDATE,
1291 FND_GLOBAL.LOGIN_ID
1292 ) ;
1293
1294 END IF;
1295 END LOOP;
1296 END IF; -- Academic History Details
1297 -- Update the IGS_AD_ACT_ASSSESSMENT table Interface_Transfer_Date with Sysdate
1298 Update IGS_AD_ACT_ASSESSMENTS set Interface_Transfer_Date = SYSDATE where rowid = Act_Assessment_Rec.rowid;
1299
1300 EXCEPTION
1301 WHEN OTHERS THEN
1302 -- Insert a log message that Person Record failed to populate into Interface Tables.
1303
1304 FND_MESSAGE.SET_NAME('IGS','IGS_AD_ACT_INS_FAIL');
1305 FND_MESSAGE.SET_TOKEN('ACT_ID', Act_Assessment_Rec.ACT_Identifier);
1306 FND_MESSAGE.SET_TOKEN('REPORTING_YEAR', Act_Assessment_Rec.Reporting_Year);
1307 FND_MESSAGE.SET_TOKEN('TEST_DATE', Act_Assessment_Rec.Test_Date_Txt);
1308 FND_MESSAGE.SET_TOKEN('TEST_TYPE',' - '|| Act_Assessment_Rec.Test_Type);
1309 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1310 FND_FILE.PUT_LINE(Fnd_File.LOG,FND_MESSAGE.GET);
1311 ROLLBACK TO Transact_spoint;
1312 l_rec_fail_count := l_rec_fail_count + 1;
1313 END; -- END of Inserting a Single Transaction
1314
1315 l_rec_count := l_rec_count + 1;
1316 IF MOD(l_rec_count,100) = 0 THEN
1317 COMMIT;
1318 END IF;
1319
1320 END LOOP --END of Inserting all Person details
1321 COMMIT;
1322
1323 END; -- END Loading the Data to Interface Tables.
1324
1325 FND_FILE.PUT_LINE(FND_FILE.LOG, '---------------------------------------------------------------------------------------------------------------------------------------------------');
1326 FND_MESSAGE.SET_NAME('IGS','IGS_AD_ACT_COUNT');
1327 FND_MESSAGE.SET_TOKEN('COUNT', l_rec_count - l_rec_fail_count);
1328 FND_FILE.PUT_LINE(Fnd_File.LOG,FND_MESSAGE.GET);
1329 FND_FILE.PUT_LINE(Fnd_File.LOG,l_rec_fail_count || ' ACT records have failed to insert into the interface tables');
1330 FND_FILE.PUT_LINE(Fnd_File.LOG,'Total Records Processed : '||l_rec_count);
1331 FND_FILE.PUT_LINE(FND_FILE.LOG, '---------------------------------------------------------------------------------------------------------------------------------------------------');
1332
1333 -- Call to Import Process only if any success records.
1334 IF ((l_rec_count - l_rec_fail_count) <> 0) THEN
1335 igs_ad_imp_001.imp_adm_data(
1336 ERRBUF => l_errbuf,
1337 RETCODE => l_retcode,
1338 P_BATCH_ID => l_Batch_Id,
1339 P_SOURCE_TYPE_ID => p_Source_Type_Id,
1340 P_MATCH_SET_ID => p_Match_set_Id,
1341 P_LEGACY_IND => 'N',
1342 P_ENABLE_LOG => 'Y' );
1343 END IF;
1344 EXCEPTION
1345 WHEN OTHERS THEN
1346 FND_FILE.PUT_LINE(Fnd_File.LOG,' Following Error Occured during the Import Process : ');
1347 FND_FILE.PUT_LINE(Fnd_File.LOG,SQLERRM);
1348 retcode :=2;
1349 errbuf:=SQLERRM;
1350
1351 END Insert_ACT_to_Interface;-- End of Procedure
1352
1353 END IGS_AD_ACT_ASSESSMENTS_PKG;