[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_IMP_UH_TST_PKG
Source
1 PACKAGE BODY IGS_AD_IMP_UH_TST_PKG AS
2 /* $Header: IGSADA5B.pls 115.12 2003/11/04 08:43:39 rghosh ship $ */
3
4 PROCEDURE imp_convt_tst_scrs(
5 errbuf OUT NOCOPY VARCHAR2,
6 retcode OUT NOCOPY NUMBER,
7 p_group_id IN NUMBER,
8 p_org_id IN NUMBER
9 ) IS
10
11 v_session_id NUMBER;
12
13
14 CURSOR c_person_cur(cp_group_id NUMBER) IS
15 SELECT pgm.person_id
16 FROM igs_pe_prsid_grp_mem pgm
17 WHERE pgm.group_id = cp_group_id AND
18 NVL(TRUNC(pgm.start_date),TRUNC(SYSDATE)) <= TRUNC(SYSDATE) AND
19 NVL(TRUNC(pgm.end_date),TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
20
21 l_gather_status VARCHAR2(5);
22 l_industry VARCHAR2(5);
23 l_schema VARCHAR2(30);
24 l_gather_return BOOLEAN;
25 l_owner VARCHAR2(30);
26
27
28 BEGIN
29
30 -- Gather statistics for interface table
31 -- by rrengara on 20-jan-2003 bug 2711176
32
33 BEGIN
34 l_gather_return := fnd_installation.get_app_info('IGS', l_gather_status, l_industry, l_schema);
35
36 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema, tabname => 'IGS_AD_TSTRST_UH_INT', cascade => TRUE);
37 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema, tabname => 'IGS_AD_TSTDTL_UH_INT', cascade => TRUE);
38 EXCEPTION WHEN OTHERS THEN
39 NULL;
40 END;
41
42 -- Issue a savepoint for the purpose of rolling back of transaction
43 SAVEPOINT impuhtst;
44
45 -- To populate org_id
46 igs_ge_gen_003.set_org_id(p_org_id);
47
48 -- Initialize the retcode
49 retcode := 0;
50
51 -- Process the person records based on the group id entered in the
52 -- parameters
53
54 FOR c_person_rec IN c_person_cur(p_group_id) LOOP
55 -- Call the user hook
56 igs_ad_tstuh_call_pkg.call_user_hook
57 (
58 c_person_rec.person_id,
59 v_session_id
60 );
61 -- Call the procedure transfer into OSS only if above call was successful
62 IF v_session_id IS NOT NULL THEN
63 transfer_int_oss (
64 p_session_id => v_session_id,
65 p_person_id => c_person_rec.person_id
66 );
67
68 END IF;
69 END LOOP;
70 FND_FILE.PUT_LINE ( FND_FILE.LOG, FND_MESSAGE.GET_STRING( 'IGS', 'IGS_AD_CONV_TEST_SUCCESS'));
71 EXCEPTION
72
73 WHEN OTHERS THEN
74
75 -- Rollback the transaction
76 ROLLBACK TO impuhtst;
77
78 retcode := 2;
79
80 -- Handle the standard igs-message stack
81 igs_ge_msg_stack.conc_exception_hndl;
82
83 END imp_convt_tst_scrs;
84
85 PROCEDURE transfer_int_oss
86 (
87 p_person_id IN NUMBER,
88 p_session_id IN NUMBER
89 ) IS
90 /*
91 This procedure imports the records from the interface tables
92 1. IGS_AD_TSTRST_UH_INT ( Test Results Interface table)
93 2. IGS_AD_TSTDTL_UH_INT ( Test Results Details Interface table)
94 into the corresponding OSS tables viz.,
95 1. IGS_AD_TEST_RESULTS ( Test results system table)
96 2. IGS_AD_TST_RSLT_DTLS ( Test Results Details system table)
97 */
98 --
99 -- DLD_adsr_Test_Scores
100 -- 2. Create cursor C_TST_CUR for the step 1 . Use the following query
101 --
102 CURSOR c_tst_cur IS
103 SELECT
104 *
105 FROM
106 igs_ad_tstrst_uh_int
107 WHERE
108 PERSON_ID = p_person_id AND
109 SESSION_ID = p_session_id AND
110 STATUS = '2';
111
112 CURSOR c_test_scores IS
113 SELECT SUM(B.TEST_SCORE) FROM IGS_AD_TSTRST_UH_INT A,IGS_AD_TSTDTL_UH_INT B
114 WHERE A.INTERFACE_TST_ID = B.INTERFACE_TST_ID
115 AND A.PERSON_ID = p_person_id
116 AND A.SESSION_ID = p_session_id
117 AND B.TEST_SEGMENT_ID IN (SELECT TEST_SEGMENT_ID
118 FROM IGS_AD_TEST_SEGMENTS
119 WHERE INCLUDE_IN_COMP_SCORE ='Y'
120 AND ADMISSION_TEST_TYPE IN
121 ( SELECT distinct admission_test_type
122 FROM IGS_AD_TEST_RESULTS
123 WHERE person_id = p_person_id));
124 --
125 -- DLD_adsr_Test_Scores
126 -- 6. Get the corresponding SCORE_TYPE for the C_TST_CUR.TEST_TYPE and
127 -- store it in L_SCORE_TYPE from the table IGS_AD_TEST_TYPE
128 --
129 CURSOR c_score_typ_cur (cp_test_type VARCHAR2) IS
130 SELECT
131 score_type
132 FROM
133 igs_ad_test_type
134 WHERE
135 admission_test_type = cp_test_type;
136
137 c_score_typ_rec c_score_typ_cur%ROWTYPE;
138
139 --
140 -- DLD_adsr_Test_Scores
141 -- IF X_ACTIVE_IND is Y THEN
142 -- Make all other Test Types which is same as the test type C_TST_CUR.TEST_TYPE to Active N
143 -- in the table IGS_AD_TEST_RESULTS.
144 -- Use the following query
145 --
146 CURSOR c_other_test_cur ( cp_test_type VARCHAR2, cp_test_results_id NUMBER) IS
147 SELECT
148 a.rowid, a.*
149 FROM
150 igs_ad_test_results a
151 WHERE
152 person_id = p_person_id AND
153 admission_test_type = cp_test_type AND
154 active_ind = 'Y' AND
155 test_results_id <> cp_test_results_id;
156
157 l_error_code IGS_AD_TSTRST_UH_INT.ERROR_CODE%TYPE;
158 l_status IGS_AD_TSTRST_UH_INT.STATUS%TYPE;
159 l_tst_rowid VARCHAR2(25);
160 l_tstdtl_rowid VARCHAR2(25);
161 l_test_results_id NUMBER(15);
162 l_tst_rslt_dtls_id NUMBER(15);
163 l_test_scores NUMBER(15);
164
165 l_return_status BOOLEAN;
166
167
168 --
169 -- Start of local Procedure imp_chld_test_details
170 --
171 PROCEDURE imp_chld_test_details
172 (
173 p_interface_tst_id IN NUMBER,
174 p_return_status OUT NOCOPY BOOLEAN
175 ) IS
176
177 --
178 -- DLD_adsr_Test_Scores
179 -- 8. Create Cursor C_TSTDTL_CUR with the following SELECT statement in order to import
180 -- all the test segments for the imported test type
181 --
182 CURSOR c_tstdtl_cur ( cp_interface_tst_id NUMBER) IS
183 SELECT
184 *
185 FROM
186 igs_ad_tstdtl_uh_int
187 WHERE
188 interface_tst_id = cp_interface_tst_id;
189 BEGIN
190 FOR c_tstdtl_rec IN c_tstdtl_cur ( p_interface_tst_id) LOOP
191 igs_ad_tst_rslt_dtls_pkg.insert_row
192 (
193 X_ROWID => l_tstdtl_rowid,
194 X_TST_RSLT_DTLS_ID => l_tst_rslt_dtls_id,
195 X_TEST_RESULTS_ID => l_test_results_id,
196 X_TEST_SEGMENT_ID => c_tstdtl_rec.test_segment_id,
197 X_TEST_SCORE => c_tstdtl_rec.test_score,
198 X_PERCENTILE => NULL,
199 X_NATIONAL_PERCENTILE => NULL,
200 X_STATE_PERCENTILE => NULL,
201 X_PERCENTILE_YEAR_RANK => NULL,
202 X_SCORE_BAND_LOWER => NULL,
203 X_SCORE_BAND_UPPER => NULL,
204 X_IRREGULARITY_CODE_ID => NULL,
205 X_ATTRIBUTE_CATEGORY => NULL,
206 X_ATTRIBUTE1 => NULL,
207 X_ATTRIBUTE2 => NULL,
208 X_ATTRIBUTE3 => NULL,
209 X_ATTRIBUTE4 => NULL,
210 X_ATTRIBUTE5 => NULL,
211 X_ATTRIBUTE6 => NULL,
212 X_ATTRIBUTE7 => NULL,
213 X_ATTRIBUTE8 => NULL,
214 X_ATTRIBUTE9 => NULL,
215 X_ATTRIBUTE10 => NULL,
216 X_ATTRIBUTE11 => NULL,
217 X_ATTRIBUTE12 => NULL,
218 X_ATTRIBUTE13 => NULL,
219 X_ATTRIBUTE14 => NULL,
220 X_ATTRIBUTE15 => NULL,
221 X_ATTRIBUTE16 => NULL,
222 X_ATTRIBUTE17 => NULL,
223 X_ATTRIBUTE18 => NULL,
224 X_ATTRIBUTE19 => NULL,
225 X_ATTRIBUTE20 => NULL,
226 X_MODE => 'R'
227 );
228 END LOOP;
229 p_return_status := TRUE;
230 EXCEPTION
231 WHEN OTHERS THEN
232 p_return_status := FALSE;
233 END imp_chld_test_details;
234 --
235 -- End of Local Procedure imp_chld_test_details
236 --
237
238 --
239 -- Start of Procedure transfer_int_oss
240 --
241 BEGIN
242 OPEN c_test_scores;
243 FETCH c_test_scores INTO l_test_scores;
244 CLOSE c_test_scores;
245 --
246 -- Loop through the test results interface records
247 --
248 FOR c_tst_rec IN c_tst_cur LOOP
249 l_error_code := NULL;
250 --
251 -- Setting the savepoint before_tsttype for the transaction to be
252 -- rolled out NOCOPY in case of any error occuring while importing the test type
253 -- master record or test result detail record.
254 --
255 SAVEPOINT before_tsttype;
256 -- Outer Begin
257 BEGIN
258 --
259 -- Open the score type cursor and get the corresponding score type
260 --
261 OPEN c_score_typ_cur ( c_tst_rec.test_type);
262 FETCH c_score_typ_cur INTO c_score_typ_rec;
263 CLOSE c_score_typ_cur;
264 IF c_score_typ_rec.score_type IS NOT NULL THEN
265 -- Insert master begin
266 BEGIN
267 --
268 -- DLD_adsr_Test_Scores
269 -- 7. Call IGS_AD_TEST_RESULTS.INSERT_ROW(
270 --
271 igs_ad_test_results_pkg.insert_row
272 (
273 X_ROWID => l_tst_rowid,
274 X_TEST_RESULTS_ID => l_test_results_id,
275 X_PERSON_ID => c_tst_rec.person_id,
276 X_ADMISSION_TEST_TYPE => c_tst_rec.test_type,
277 X_TEST_DATE => c_tst_rec.test_date,
278 X_SCORE_REPORT_DATE => NULL,
279 X_EDU_LEVEL_ID => NULL,
280 X_SCORE_TYPE => c_score_typ_rec.score_type,
281 X_SCORE_SOURCE_ID => NULL,
282 X_NON_STANDARD_ADMIN => NULL,
283 X_COMP_TEST_SCORE => l_test_scores,
284 X_SPECIAL_CODE => NULL,
285 X_REGISTRATION_NUMBER => NULL,
286 X_GRADE_ID => NULL,
287 X_ATTRIBUTE_CATEGORY => NULL,
288 X_ATTRIBUTE1 => NULL,
289 X_ATTRIBUTE2 => NULL,
290 X_ATTRIBUTE3 => NULL,
291 X_ATTRIBUTE4 => NULL,
292 X_ATTRIBUTE5 => NULL,
293 X_ATTRIBUTE6 => NULL,
294 X_ATTRIBUTE7 => NULL,
295 X_ATTRIBUTE8 => NULL,
296 X_ATTRIBUTE9 => NULL,
297 X_ATTRIBUTE10 => NULL,
298 X_ATTRIBUTE11 => NULL,
299 X_ATTRIBUTE12 => NULL,
300 X_ATTRIBUTE13 => NULL,
301 X_ATTRIBUTE14 => NULL,
302 X_ATTRIBUTE15 => NULL,
303 X_ATTRIBUTE16 => NULL,
304 X_ATTRIBUTE17 => NULL,
305 X_ATTRIBUTE18 => NULL,
306 X_ATTRIBUTE19 => NULL,
307 X_ATTRIBUTE20 => NULL,
308 X_MODE => 'R',
309 X_ACTIVE_IND => c_tst_rec.active_ind
310 );
311 EXCEPTION
312 WHEN OTHERS THEN
313 l_error_code := 'E002';
314 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Insertion of Test Result record failed '
315 || ' Person ID :: ' || IGS_GE_NUMBER.TO_CANN ( p_person_id)
316 || ' INTERFACE_TST_ID :: ' || IGS_GE_NUMBER.TO_CANN ( c_tst_rec.interface_tst_id) );
317 -- Insert Master End
318 END;
319 IF l_error_code IS NULL THEN
320 --
321 -- After successful insertion of the master record Check if the active ind is 'Y'
322 -- If 'y' then update the other test results records with same admission_test_type
323 -- to 'N'
324 --
325 IF c_tst_rec.active_ind = 'Y' THEN
326 FOR c_other_test_rec IN c_other_test_cur ( c_tst_rec.test_type, l_test_results_id) LOOP
327 -- Update Active Ind Begin
328 BEGIN
329 igs_ad_test_results_pkg.update_row
330 (
331 X_ROWID => c_other_test_rec.rowid,
332 X_TEST_RESULTS_ID => c_other_test_rec.test_results_id,
333 X_PERSON_ID => c_other_test_rec.person_id,
334 X_ADMISSION_TEST_TYPE => c_other_test_rec.admission_test_type,
335 X_TEST_DATE => c_other_test_rec.test_date,
336 X_SCORE_REPORT_DATE => c_other_test_rec.score_report_date,
337 X_EDU_LEVEL_ID => c_other_test_rec.edu_level_id,
338 X_SCORE_TYPE => c_other_test_rec.score_type,
339 X_SCORE_SOURCE_ID => c_other_test_rec.score_source_id,
340 X_NON_STANDARD_ADMIN => c_other_test_rec.non_standard_admin,
341 X_COMP_TEST_SCORE => c_other_test_rec.comp_test_score,
342 X_SPECIAL_CODE => c_other_test_rec.special_code,
343 X_REGISTRATION_NUMBER => c_other_test_rec.registration_number,
344 X_GRADE_ID => c_other_test_rec.grade_id,
345 X_ATTRIBUTE_CATEGORY => c_other_test_rec.attribute_category,
346 X_ATTRIBUTE1 => c_other_test_rec.attribute1,
347 X_ATTRIBUTE2 => c_other_test_rec.attribute2,
348 X_ATTRIBUTE3 => c_other_test_rec.attribute3,
349 X_ATTRIBUTE4 => c_other_test_rec.attribute4,
350 X_ATTRIBUTE5 => c_other_test_rec.attribute5,
351 X_ATTRIBUTE6 => c_other_test_rec.attribute6,
352 X_ATTRIBUTE7 => c_other_test_rec.attribute7,
353 X_ATTRIBUTE8 => c_other_test_rec.attribute8,
354 X_ATTRIBUTE9 => c_other_test_rec.attribute9,
355 X_ATTRIBUTE10 => c_other_test_rec.attribute10,
356 X_ATTRIBUTE11 => c_other_test_rec.attribute11,
357 X_ATTRIBUTE12 => c_other_test_rec.attribute12,
358 X_ATTRIBUTE13 => c_other_test_rec.attribute13,
359 X_ATTRIBUTE14 => c_other_test_rec.attribute14,
360 X_ATTRIBUTE15 => c_other_test_rec.attribute15,
361 X_ATTRIBUTE16 => c_other_test_rec.attribute16,
362 X_ATTRIBUTE17 => c_other_test_rec.attribute17,
363 X_ATTRIBUTE18 => c_other_test_rec.attribute18,
364 X_ATTRIBUTE19 => c_other_test_rec.attribute19,
365 X_ATTRIBUTE20 => c_other_test_rec.attribute20,
366 X_MODE => 'R',
367 X_ACTIVE_IND => 'N' -- This field alone is updated
368 );
369 EXCEPTION
370 WHEN OTHERS THEN
371 l_error_code := 'E004'; -- Active Indicator Update failed
372 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Active Indicator Update failed '
373 || ' Person ID :: ' || IGS_GE_NUMBER.TO_CANN ( p_person_id)
374 || ' INTERFACE_TST_ID :: ' || IGS_GE_NUMBER.TO_CANN ( c_tst_rec.interface_tst_id) );
375
376 -- Update Active Ind End
377 END;
378 END LOOP;
379 END IF;
380 END IF;
381 --
382 -- After successful insertion of the master record insert the
383 -- child records into the result details table
384 --
385 IF l_error_code IS NULL THEN
386 imp_chld_test_details ( P_RETURN_STATUS => l_return_status, P_INTERFACE_TST_ID => c_tst_rec.interface_tst_id);
387 END IF;
388 IF NOT l_return_status THEN
389 l_error_code := 'E003'; -- Insertion of child record failed
390 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Insertion of child record failed '
391 || ' Person ID :: ' || IGS_GE_NUMBER.TO_CANN ( p_person_id)
392 || ' INTERFACE_TST_ID :: ' || IGS_GE_NUMBER.TO_CANN ( c_tst_rec.interface_tst_id) );
393
394 END IF;
395 END IF;
396 EXCEPTION
397 WHEN OTHERS THEN
398 l_error_code := 'E005'; -- Test Score Import Failed
399 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Test Score Import Failed '
400 || ' Person ID :: ' || IGS_GE_NUMBER.TO_CANN ( p_person_id)
401 || ' INTERFACE_TST_ID :: ' || IGS_GE_NUMBER.TO_CANN ( c_tst_rec.interface_tst_id) );
402 -- Outer End
403 END;
404 IF l_error_code IS NULL THEN
405 DELETE FROM
406 igs_ad_tstdtl_uh_int
407 WHERE
408 interface_tst_id = c_tst_rec.interface_tst_id;
409
410 DELETE FROM
411 igs_ad_tstrst_uh_int
412 WHERE
413 interface_tst_id = c_tst_rec.interface_tst_id;
414 ELSE
415 ROLLBACK TO before_tsttype;
416 UPDATE
417 igs_ad_tstrst_uh_int
418 SET
419 status = '3',
420 error_code = l_error_code
421 WHERE
422 interface_tst_id = c_tst_rec.interface_tst_id;
423 END IF;
424 END LOOP;
425 END transfer_int_oss;
426
427 END IGS_AD_IMP_UH_TST_PKG;