[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_IMP_028
Source
1 PACKAGE BODY Igs_Ad_Imp_028 AS
2 /* $Header: IGSADC8B.pls 115.8 2003/12/09 13:32:42 pbondugu noship $ */
3
4 /*************************************
5 || Change History
6 || who when what
7 || pbondugu 22-Apr-2003 Admissions Legacy Import to import person legacy data
8 */
9
10 /***************************Status,Discrepancy Rule, Match Indicators, Error Codes********************/
11 cst_rule_val_I CONSTANT VARCHAR2(1) := 'I';
12 cst_rule_val_E CONSTANT VARCHAR2(1) := 'E';
13 cst_rule_val_R CONSTANT VARCHAR2(1) := 'R';
14
15
16 cst_mi_val_11 CONSTANT VARCHAR2(2) := '11';
17 cst_mi_val_12 CONSTANT VARCHAR2(2) := '12';
18 cst_mi_val_13 CONSTANT VARCHAR2(2) := '13';
19 cst_mi_val_14 CONSTANT VARCHAR2(2) := '14';
20 cst_mi_val_15 CONSTANT VARCHAR2(2) := '15';
21 cst_mi_val_16 CONSTANT VARCHAR2(2) := '16';
22 cst_mi_val_17 CONSTANT VARCHAR2(2) := '17';
23 cst_mi_val_18 CONSTANT VARCHAR2(2) := '18';
24 cst_mi_val_19 CONSTANT VARCHAR2(2) := '19';
25 cst_mi_val_20 CONSTANT VARCHAR2(2) := '20';
26 cst_mi_val_21 CONSTANT VARCHAR2(2) := '21';
27 cst_mi_val_22 CONSTANT VARCHAR2(2) := '22';
28 cst_mi_val_23 CONSTANT VARCHAR2(2) := '23';
29 cst_mi_val_24 CONSTANT VARCHAR2(2) := '24';
30 cst_mi_val_25 CONSTANT VARCHAR2(2) := '25';
31 cst_mi_val_27 CONSTANT VARCHAR2(2) := '27';
32
33 cst_s_val_1 CONSTANT VARCHAR2(1) := '1';
34 cst_s_val_2 CONSTANT VARCHAR2(1) := '2';
35 cst_s_val_3 CONSTANT VARCHAR2(1) := '3';
36 cst_s_val_4 CONSTANT VARCHAR2(1) := '4';
37
38 cst_ec_val_E322 CONSTANT VARCHAR2(4) := 'E322';
39 cst_ec_val_E014 CONSTANT VARCHAR2(4) := 'E014';
40 cst_ec_val_NULL CONSTANT VARCHAR2(4) := NULL;
41
42 cst_insert CONSTANT VARCHAR2(6) := 'INSERT';
43 cst_update CONSTANT VARCHAR2(6) := 'UPDATE';
44 cst_unique_record CONSTANT NUMBER := 1;
45 l_request_id NUMBER := fnd_global.conc_request_id;
46 /***************************Status,Discrepancy Rule, Match Indicators, Error Codes*******************/
47
48 PROCEDURE prc_pe_qual_details (
49 p_interface_run_id igs_ad_interface_all.interface_run_id%TYPE,
50 p_rule VARCHAR2,
51 p_enable_log VARCHAR2) AS
52
53 /*************************************
54 || Created By :Praveen Bondugula
55 || Date Created By :24-apr-2003
56 || Purpose : To import Qualification details
57 || Know limitations, enhancements or remarks
58 || Change History
59 || Who When What
60 ||
61 */
62
63 l_rowid VARCHAR2(20);
64 l_qual_dets_id igs_uc_qual_dets.qual_dets_id%TYPE;
65 l_status VARCHAR2(1);
66 l_error_code VARCHAR2(30);
67 l_error_text VARCHAR2(2000);
68 l_interface_qual_id igs_uc_qual_ints.interface_qual_id%TYPE;
69 l_person_id igs_ad_interface_all.person_id%TYPE;
70 l_person_id_errored igs_ad_interface_all.person_id%TYPE;
71
72 l_prog_label VARCHAR2(100);
73 l_label VARCHAR2(100);
74 l_debug_str VARCHAR2(2000);
75
76 l_msg_at_index NUMBER := 0;
77 l_return_status VARCHAR2(1);
78 l_msg_count NUMBER ;
79 l_msg_data VARCHAR2(2000);
80 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
81
82 BEGIN
83 l_prog_label := 'igs.plsql.igs_ad_imp_028.prc_pe_qual_details';
84
85 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
86 l_label := 'igs.plsql.igs_ad_imp_028.prc_pe_qual_details.begin';
87 l_debug_str := 'igs_ad_imp_028.prc_pe_qual_details.start';
88
89 fnd_log.string_with_context( fnd_log.level_procedure,
90 l_label,
91 l_debug_str, NULL,
92 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
93 END IF;
94 -- Error out all the interface records, if the corrospending person already having qualification details
95 --- in ths OSS transaction table.
96 UPDATE IGS_UC_QUAL_INTS qints
97 SET error_code = 'E683'
98 ,error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E683', 8405)
99 , status ='3'
100 WHERE STATUS = '2'
101 AND interface_run_id = p_interface_run_id
102 AND EXISTS ( SELECT 1 FROM IGS_UC_QUAL_DETS
103 WHERE person_id = qints.person_id);
104
105 COMMIT;
106 -- Check for duplicates among interface records. If exists error out all the such records.
107 UPDATE IGS_UC_QUAL_INTS qints
108 SET error_code = 'E684'
109 ,error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E684', 8405)
110 , status ='3'
111 WHERE STATUS = '2'
112 AND interface_run_id = p_interface_run_id
113 AND EXISTS (SELECT 1 FROM IGS_UC_QUAL_INTS qints2
114 WHERE qints2.person_id = qints.person_id
115 AND qints2.exam_level = qints.exam_level
116 AND NVL(qints2.subject_code, '-1') = NVL(qints.subject_code, '-1')
117 AND NVL(qints2.awarding_body,'-1') = NVL(qints.awarding_body,'-1')
118 AND NVL(qints2.year,1700) = NVL(qints.year,1700)
119 AND NVL(qints2.sitting,'-1') = NVL(qints.sitting,'-1')
120 AND NVL( qints2.approved_result,'-1') = NVL( qints.approved_result,'-1')
121 AND qints2.rowid <> qints.rowid
122 AND qints2.interface_run_id = p_interface_run_id
123 AND qints2.status ='2' );
124 COMMIT;
125 -- Error out all interface records if the any interface record with same person_id is errored out.
126 UPDATE IGS_UC_QUAL_INTS qints
127 SET error_code = 'E684'
128 ,error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E684', 8405)
129 , status ='3'
130 WHERE STATUS = '2'
131 AND interface_run_id = p_interface_run_id
132 AND EXISTS (SELECT 1 FROM IGS_UC_QUAL_INTS qints2
133 WHERE STATUS = '3'
134 AND qints2.person_id = qints.person_id
135 AND qints2.interface_run_id = p_interface_run_id) ;
136
137
138 COMMIT;
139 l_person_id := NULL;
140 l_person_id_errored := NULL;
141 FOR uc_qual_rec IN IGS_AD_IMP_028.c_uc_qual_cur(p_interface_run_id )
142 LOOP
143 IF uc_qual_rec.person_id <> NVL(l_person_id, uc_qual_rec.person_id) THEN
144 COMMIT;
145 END IF;
146 l_person_id := uc_qual_rec.person_id;
147 -- Skipping the record because the record with same person ID ha failed.
148 IF uc_qual_rec.person_id <> NVL(l_person_id_errored, -99) THEN
149
150 DECLARE
151 invalid_record exception;
152 BEGIN
153 l_error_Code := NULL;
154 l_error_text := NULL;
155 igs_uc_qual_dets_imp_pkg.validate_pe_qual(uc_qual_rec,l_Status,l_error_Code);
156 IF(l_Status ='3' ) THEN
157 RAISE invalid_record;
158 END IF;
159 l_msg_at_index := igs_ge_msg_stack.count_msg;
160 igs_uc_qual_dets_pkg.insert_row(
161 x_rowid => l_rowid,
162 x_qual_dets_id => l_qual_dets_id,
163 x_person_id => uc_qual_rec.person_id,
164 x_exam_level => uc_qual_rec.exam_level,
165 x_subject_code => uc_qual_rec.subject_code,
166 x_year => uc_qual_rec.year,
167 x_sitting => uc_qual_rec.sitting,
168 x_awarding_body => uc_qual_rec.awarding_body,
169 x_grading_schema_cd => uc_qual_rec.grading_schema_cd,
170 x_version_number => uc_qual_rec.version_number,
171 x_predicted_result => uc_qual_rec.predicted_result,
172 x_approved_result => uc_qual_rec.approved_result,
173 x_claimed_result => uc_qual_rec.claimed_result,
174 x_ucas_tariff => uc_qual_rec.ucas_tariff,
175 x_imported_flag => uc_qual_rec.imported_flag,
176 x_imported_date => TRUNC(uc_qual_rec.imported_date),
177 x_mode => 'R');
178 UPDATE igs_uc_qual_ints
179 SET status = cst_s_val_1,
180 error_code = cst_ec_val_NULL
181 WHERE interface_qual_id = uc_qual_rec.interface_qual_id;
182 -- If the all qulaificaton records with same person_id are processed , then commit;
183 --If person_id changes from previous one then it means that records with previous person_id are processed
184
185 EXCEPTION
186 WHEN invalid_record THEN
187 ROLLBACK;
188 l_person_id_errored := uc_qual_rec.person_id ;
189 UPDATE igs_uc_qual_ints
190 SET status = cst_s_val_3,
191 error_code = l_error_code,
192 error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405)
193 WHERE interface_qual_id = uc_qual_rec.interface_qual_id;
194
195 IF p_enable_log = 'Y' THEN
196 igs_ad_imp_001.logerrormessage(uc_qual_rec.interface_qual_id,l_error_code,'IGS_UC_QUAL_INTS');
197 END IF;
198 l_error_code := 'E685';
199 UPDATE igs_uc_qual_ints
200 SET status = cst_s_val_3
201 , error_code = l_error_code
202 , error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405)
203 WHERE person_id = uc_qual_rec.person_id
204 AND status = cst_s_val_2
205 AND interface_qual_id <> uc_qual_rec.interface_qual_id;
206
207
208 WHEN OTHERS THEN
209 ROLLBACK;
210 igs_ad_gen_016.extract_msg_from_stack (
211 p_msg_at_index => l_msg_at_index,
212 p_return_status => l_return_status,
213 p_msg_count => l_msg_count,
214 p_msg_data => l_msg_data,
215 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
216 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
217 l_error_text := l_msg_data;
218 l_error_Code := 'E322';
219
220 IF p_enable_log = 'Y' THEN
221 igs_ad_imp_001.logerrormessage(uc_qual_rec.interface_qual_id,l_msg_data,'IGS_UC_QUAL_INTS');
222 END IF;
223 ELSE
224 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E518', 8405);
225 l_error_Code := 'E518';
226 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
227
228 l_label := 'igs.plsql.igs_ad_imp_028.prc_uc_qual_dtls.exception '||l_msg_data;
229
230 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
231 fnd_message.set_token('INTERFACE_ID',uc_qual_rec.interface_qual_id);
232 fnd_message.set_token('ERROR_CD','E322');
233
234 l_debug_str := fnd_message.get;
235
236 fnd_log.string_with_context( fnd_log.level_exception,
237 l_label,
238 l_debug_str, NULL,
239 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
240 END IF;
241
242 END IF;
243
244
245 l_person_id_errored := uc_qual_rec.person_id ;
246 UPDATE igs_uc_qual_ints
247 SET status = cst_s_val_3, error_code = l_error_code, error_text = l_error_text
248 WHERE interface_qual_id = uc_qual_rec.interface_qual_id;
249
250 l_error_code := 'E685';
251 UPDATE igs_uc_qual_ints
252 SET status = cst_s_val_3,
253 error_code = l_error_code,
254 error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405)
255 WHERE person_id = uc_qual_rec.person_id
256 AND status =cst_s_val_2
257 AND interface_qual_id <> uc_qual_rec.interface_qual_id;
258
259 END;
260 END IF;
261 END LOOP;
262 COMMIT;
263 END prc_pe_qual_details;
264
265 END Igs_Ad_Imp_028;