[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_IMP_014
Source
1 PACKAGE BODY Igs_Ad_Imp_014 AS
2 /* $Header: IGSAD92B.pls 115.23 2003/12/09 11:57:21 akadam ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 27-AUG-2001 Bug No. 1956374 .The procedure declaration of PRC_RELNS_EMP_DTLS removed
7 -------------------------------------------------------------------------------------------
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 cst_mi_val_11 CONSTANT VARCHAR2(2) := '11';
16 cst_mi_val_12 CONSTANT VARCHAR2(2) := '12';
17 cst_mi_val_13 CONSTANT VARCHAR2(2) := '13';
18 cst_mi_val_14 CONSTANT VARCHAR2(2) := '14';
19 cst_mi_val_15 CONSTANT VARCHAR2(2) := '15';
20 cst_mi_val_16 CONSTANT VARCHAR2(2) := '16';
21 cst_mi_val_17 CONSTANT VARCHAR2(2) := '17';
22 cst_mi_val_18 CONSTANT VARCHAR2(2) := '18';
23 cst_mi_val_19 CONSTANT VARCHAR2(2) := '19';
24 cst_mi_val_20 CONSTANT VARCHAR2(2) := '20';
25 cst_mi_val_21 CONSTANT VARCHAR2(2) := '21';
26 cst_mi_val_22 CONSTANT VARCHAR2(2) := '22';
27 cst_mi_val_23 CONSTANT VARCHAR2(2) := '23';
28 cst_mi_val_24 CONSTANT VARCHAR2(2) := '24';
29 cst_mi_val_25 CONSTANT VARCHAR2(2) := '25';
30 cst_mi_val_27 CONSTANT VARCHAR2(2) := '27';
31
32 cst_s_val_1 CONSTANT VARCHAR2(1) := '1';
33 cst_s_val_2 CONSTANT VARCHAR2(1) := '2';
34 cst_s_val_3 CONSTANT VARCHAR2(1) := '3';
35 cst_s_val_4 CONSTANT VARCHAR2(1) := '4';
36
37 cst_ec_val_E322 CONSTANT VARCHAR2(4) := 'E322';
38 cst_ec_val_E014 CONSTANT VARCHAR2(4) := 'E014';
39
40 cst_insert CONSTANT VARCHAR2(6) := 'INSERT';
41 cst_update CONSTANT VARCHAR2(6) := 'UPDATE';
42 cst_unique_record CONSTANT NUMBER := 1;
43
44 cst_et_val_E700 VARCHAR2(100) := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405);
45
46 cst_ec_val_E700 VARCHAR2(4) := 'E700';
47
48 /***************************Status,Discrepancy Rule, Match Indicators, Error Codes********************/
49
50
51
52
53 PROCEDURE prc_pe_recruitments_dtl(
54 p_interface_run_id IN NUMBER,
55 p_enable_log IN VARCHAR2,
56 p_rule IN VARCHAR2 )
57 AS
58 /*
59 || Created By : [email protected]
60 || Created On : 06-Jul-2001
61 || Purpose : This procedure is for importing person recruitment details.
62 || DLD: Modelling and Forecasting_SDQ. Enh Bug# 1834307.
63 || Known limitations, enhancements or remarks :
64 || Change History :
65 || Who When What
66 || npalanis 11-SEP-2002 bug - 2608360
67 || igs_pe_code_classes is
68 || removed due to transition of code
69 || class to lookups , new columns added
70 || for codes. the tbh call are modified accordingly .
71 || pkpatel 24-JUL-2001 Bug no.1890270 Admissions Standards and Rules Dld_adsr_setup
72 || Removed the processing for 'probability' in the call to TBH igs_ad_recruitments_pkg.insert_row, update_row
73 || and in the cursor for discrepancy check
74 || (reverse chronological order - newest change first)
75 */
76
77 l_prog_label VARCHAR2(100);
78 l_label VARCHAR2(100);
79 l_debug_str VARCHAR2(2000);
80 l_request_id NUMBER;
81 l_error_code igs_ad_recruit_int.error_code%TYPE;
82
83
84
85
86 PROCEDURE crt_upd_recruitments_dtls(
87 p_interface_run_id NUMBER)
88 AS
89 CURSOR c_igs_ad_recruit_int IS
90 SELECT cst_insert dmlmode, rowid, in_rec.* FROM igs_ad_recruit_int in_rec
91 WHERE interface_run_id = p_interface_run_id
92 AND status = cst_s_val_2
93 AND ( ( NVL(match_ind,'15') = '15'
94 AND NOT EXISTS (SELECT 1
95 FROM igs_ad_recruitments mn_rec
96 WHERE mn_rec.person_id = in_rec.person_id))
97 OR ( p_rule = cst_rule_val_R
98 AND match_ind IN (cst_mi_val_16, cst_mi_val_25)))
99 UNION ALL
100 SELECT cst_update dmlmode, rowid, in_rec.* FROM igs_ad_recruit_int in_rec
101 WHERE interface_run_id = p_interface_run_id
102 AND status = cst_s_val_2
103 AND ( (p_rule = cst_rule_val_I)
104 OR (p_rule = cst_rule_val_R AND match_ind = cst_mi_val_21))
105 AND EXISTS ( SELECT 1
106 FROM igs_ad_recruitments mn_rec
107 WHERE mn_rec.person_id = in_rec.person_id);
108
109
110 CURSOR c_null_hdlg_recru_cur(cp_person_id igs_ad_recruitments.person_id%TYPE) IS
111 SELECT ROWID, ar.*
112 FROM igs_ad_recruitments ar
113 WHERE person_id = cp_person_id;
114
115 c_null_hdlg_recru_rec c_null_hdlg_recru_cur%ROWTYPE;
116
117 l_status VARCHAR2(1);
118 l_error_code VARCHAR2(30);
119 l_error_text igs_ad_recruit_int.error_text%TYPE;
120 l_msg_at_index NUMBER := 0;
121 l_return_status VARCHAR2(1);
122 l_msg_count NUMBER ;
123 l_msg_data VARCHAR2(2000);
124 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
125
126 l_records_processed NUMBER;
127 l_recruitments_ID igs_ad_recruitments.recruitment_id%TYPE;
128 l_rowid VARCHAR2(30);
129
130 BEGIN
131
132 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
133
134
135 l_label := 'igs.plsql.igs_ad_imp_014.prc_pe_recruitments_dtl.crt_upd_recruitments_dtls';
136 l_debug_str := 'Interface Run ID' || p_interface_run_id;
137
138 fnd_log.string_with_context( fnd_log.level_procedure,
139 l_label,
140 l_debug_str, NULL,
141 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
142 END IF;
143
144 l_records_processed := 0;
145
146 FOR recruit_rec IN c_igs_ad_recruit_int
147 LOOP
148 BEGIN
149
150 SAVEPOINT before_creatupdate;
151 l_msg_at_index := igs_ge_msg_stack.count_msg;
152
153 IF recruit_rec.dmlmode = cst_insert THEN
154 igs_ad_recruitments_pkg.INSERT_ROW (
155 X_ROWID => l_rowid,
156 x_CERTAINTY_OF_CHOICE_ID => recruit_rec.CERTAINTY_OF_CHOICE_ID,
157 x_religion_cd => recruit_rec.religion_cd,
158 x_ADV_STUDIES_CLASSES => recruit_rec.ADV_STUDIES_CLASSES,
159 x_HONORS_CLASSES => recruit_rec.HONORS_CLASSES,
160 x_CLASS_SIZE => recruit_rec.CLASS_SIZE,
161 x_SEC_SCHOOL_LOCATION_ID => recruit_rec.SEC_SCHOOL_LOCATION_ID,
162 x_PERCENT_PLAN_HIGHER_EDU => recruit_rec.PERCENT_PLAN_HIGHER_EDU,
163 x_RECRUITMENT_ID => l_recruitments_ID,
164 x_PERSON_ID => recruit_rec.PERSON_ID,
165 x_SPECIAL_INTEREST_ID => recruit_rec.SPECIAL_INTEREST_ID,
166 x_PRIORITY => recruit_rec.PRIORITY,
167 x_VIP => recruit_rec.VIP,
168 x_DEACTIVATE_RECRUIT_STATUS => recruit_rec.DEACTIVATE_RECRUIT_STATUS,
169 x_PROGRAM_INTEREST_ID => recruit_rec.PROGRAM_INTEREST_ID,
170 x_INSTITUTION_SIZE_ID => recruit_rec.INSTITUTION_SIZE_ID,
171 x_INSTITUTION_CONTROL_ID => recruit_rec.INSTITUTION_CONTROL_ID,
172 x_INSTITUTION_SETTING_ID => recruit_rec.INSTITUTION_SETTING_ID,
173 x_INSTITUTION_LOCATION_ID => recruit_rec.INSTITUTION_LOCATION_ID,
174 x_SPECIAL_SERVICES_ID => recruit_rec.SPECIAL_SERVICES_ID,
175 x_EMPLOYMENT_ID => recruit_rec.EMPLOYMENT_ID,
176 x_HOUSING_ID => recruit_rec.HOUSING_ID,
177 x_DEGREE_GOAL_ID => recruit_rec.DEGREE_GOAL_ID,
178 x_UNIT_SET_ID => recruit_rec.UNIT_SET_ID,
179 X_MODE => 'R'
180 );
181
182 ELSIF recruit_rec.dmlmode = cst_update THEN
183
184 OPEN c_null_hdlg_recru_cur(recruit_rec.person_id);
185 FETCH c_null_hdlg_recru_cur INTO c_null_hdlg_recru_rec;
186 CLOSE c_null_hdlg_recru_cur;
187
188 igs_ad_recruitments_pkg.UPDATE_ROW(
189 x_rowid => c_null_hdlg_recru_rec.rowid,
190 x_CERTAINTY_OF_CHOICE_ID => NVL(recruit_rec.CERTAINTY_OF_CHOICE_ID, c_null_hdlg_recru_rec.CERTAINTY_OF_CHOICE_ID),
191 x_religion_cd => NVL(recruit_rec.religion_cd, c_null_hdlg_recru_rec.religion_cd),
192 x_ADV_STUDIES_CLASSES => NVL(recruit_rec.ADV_STUDIES_CLASSES, c_null_hdlg_recru_rec.ADV_STUDIES_CLASSES),
193 x_HONORS_CLASSES => NVL(recruit_rec.HONORS_CLASSES, c_null_hdlg_recru_rec.HONORS_CLASSES),
194 x_CLASS_SIZE => NVL(recruit_rec.CLASS_SIZE, c_null_hdlg_recru_rec.CLASS_SIZE),
195 x_SEC_SCHOOL_LOCATION_ID => NVL(recruit_rec.SEC_SCHOOL_LOCATION_ID, c_null_hdlg_recru_rec.SEC_SCHOOL_LOCATION_ID),
196 x_PERCENT_PLAN_HIGHER_EDU => NVL(recruit_rec.PERCENT_PLAN_HIGHER_EDU, c_null_hdlg_recru_rec.PERCENT_PLAN_HIGHER_EDU),
197 x_RECRUITMENT_ID => c_null_hdlg_recru_rec.recruitment_ID,
198 x_PERSON_ID => NVL(recruit_rec.PERSON_ID,c_null_hdlg_recru_rec.PERSON_ID),
199 x_SPECIAL_INTEREST_ID => NVL(recruit_rec.SPECIAL_INTEREST_ID, c_null_hdlg_recru_rec.SPECIAL_INTEREST_ID),
200 x_PRIORITY => NVL(recruit_rec.PRIORITY, c_null_hdlg_recru_rec.PRIORITY),
201 x_VIP => NVL(recruit_rec.VIP, c_null_hdlg_recru_rec.VIP),
202 x_DEACTIVATE_RECRUIT_STATUS => NVL(recruit_rec.DEACTIVATE_RECRUIT_STATUS, c_null_hdlg_recru_rec.DEACTIVATE_RECRUIT_STATUS),
203 x_PROGRAM_INTEREST_ID => NVL(recruit_rec.PROGRAM_INTEREST_ID, c_null_hdlg_recru_rec.PROGRAM_INTEREST_ID),
204 x_INSTITUTION_SIZE_ID => NVL(recruit_rec.INSTITUTION_SIZE_ID, c_null_hdlg_recru_rec.INSTITUTION_SIZE_ID),
205 x_INSTITUTION_CONTROL_ID => NVL(recruit_rec.INSTITUTION_CONTROL_ID, c_null_hdlg_recru_rec.INSTITUTION_CONTROL_ID),
206 x_INSTITUTION_SETTING_ID => NVL(recruit_rec.INSTITUTION_SETTING_ID, c_null_hdlg_recru_rec.INSTITUTION_SETTING_ID),
207 x_INSTITUTION_LOCATION_ID => NVL(recruit_rec.INSTITUTION_LOCATION_ID, c_null_hdlg_recru_rec.INSTITUTION_LOCATION_ID),
208 x_SPECIAL_SERVICES_ID => NVL(recruit_rec.SPECIAL_SERVICES_ID, c_null_hdlg_recru_rec.SPECIAL_SERVICES_ID),
209 x_EMPLOYMENT_ID => NVL(recruit_rec.EMPLOYMENT_ID, c_null_hdlg_recru_rec.EMPLOYMENT_ID),
210 x_HOUSING_ID => NVL(recruit_rec.HOUSING_ID, c_null_hdlg_recru_rec.HOUSING_ID),
211 x_DEGREE_GOAL_ID => NVL(recruit_rec.DEGREE_GOAL_ID, c_null_hdlg_recru_rec.DEGREE_GOAL_ID),
212 x_UNIT_SET_ID => NVL(recruit_rec.UNIT_SET_ID, c_null_hdlg_recru_rec.UNIT_SET_ID),
213 x_mode =>'R'
214 );
215 END IF;
216
217 UPDATE igs_ad_recruit_int
218 SET
219 status = cst_s_val_1
220 , match_ind = DECODE (recruit_rec.dmlmode,cst_update, cst_mi_val_18,cst_insert, cst_mi_val_11)
221 WHERE rowid = recruit_rec.rowid;
222
223 l_records_processed := l_records_processed + 1;
224
225 IF l_records_processed = 100 THEN
226 COMMIT;
227 l_records_processed := 0;
228 END IF;
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 ROLLBACK TO before_creatupdate;
233
234 l_msg_data := SQLERRM;
235 l_status := '3';
236
237 IF recruit_rec.dmlmode = cst_insert THEN
238 l_error_code := 'E322'; -- Person Recruitment Insertion Failed
239 ELSIF recruit_rec.dmlmode = cst_update THEN
240 l_error_code := 'E014'; -- Could not update Person Recruitment
241 END IF;
242
243 igs_ad_gen_016.extract_msg_from_stack (
244 p_msg_at_index => l_msg_at_index,
245 p_return_status => l_return_status,
246 p_msg_count => l_msg_count,
247 p_msg_data => l_msg_data,
248 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
249
250 l_error_text := NVL(l_msg_data,igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405));
251
252 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
253 IF p_enable_log = 'Y' THEN
254 igs_ad_imp_001.logerrormessage(recruit_rec.interface_recruitment_id,l_msg_data,'IGS_AD_RECRUIT_INT');
255 END IF;
256 ELSE
257 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
258
259 l_label := 'igs.plsql.igs_ad_imp_014.prc_pe_recruitments_dtl.crt_upd_recruitments_dtls.for_loop.execption'||l_error_code;
260
261 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
262 fnd_message.set_token('INTERFACE_ID',recruit_rec.interface_recruitment_id);
263 fnd_message.set_token('ERROR_CD',l_error_code);
264
265 l_debug_str := fnd_message.get;
266 fnd_log.string_with_context( fnd_log.level_exception,
267 l_label,
268 l_debug_str, NULL,
269 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
270 END IF;
271
272 END IF;
273
274
275 UPDATE igs_ad_recruit_int
276 SET
277 status = cst_s_val_3
278 , match_ind = DECODE ( recruit_rec.dmlmode, cst_update, DECODE ( match_ind, NULL, cst_mi_val_12, match_ind)
279 ,cst_insert, DECODE ( p_rule, cst_rule_val_R,
280 DECODE ( match_ind, NULL, cst_mi_val_11, match_ind), cst_mi_val_11))
281 , error_code = l_error_code
282 , error_text = l_error_text
283 WHERE rowid = recruit_rec.rowid;
284 l_records_processed := l_records_processed + 1;
285
286
287 END;
288 IF l_records_processed = 100 THEN
289 COMMIT;
290 l_records_processed := 0;
291 END IF;
292
293 END LOOP;
294 IF l_records_processed < 100 AND l_records_processed > 0 THEN
295 COMMIT;
296 END IF;
297
298
299 END crt_upd_recruitments_dtls; -- End of local procedure crt_upd_recruitments_dtls.
300
301 -- begin of main process prc_pe_recruitments_dtl
302 BEGIN
303
304 l_prog_label := 'igs.plsql.igs_ad_imp_014.prc_pe_recruitments_dtl';
305 l_label := 'igs.plsql.igs_ad_imp_014.prc_pe_recruitments_dtl.';
306 l_request_id := fnd_global.conc_request_id;
307
308 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
309
310
311 l_label := 'igs.plsql.igs_ad_imp_014.prc_pe_recruitments_dtl.begin';
312 l_debug_str := 'igs_ad_imp_014.prc_pe_recruitments_dtl';
313
314 fnd_log.string_with_context( fnd_log.level_procedure,
315 l_label,
316 l_debug_str, NULL,
317 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
318 END IF;
319
320 -- Set STATUS to 3 for interface records with RULE = E or I and MATCH IND
321 IF p_rule IN ('E','I') THEN
322 UPDATE igs_ad_recruit_int
323 SET
324 status = cst_s_val_3
325 , error_code = cst_ec_val_E700
326 , error_text = cst_et_val_E700
327 WHERE interface_run_id = p_interface_run_id
328 AND status = cst_s_val_2
329 AND NVL (match_ind, cst_mi_val_15) <> cst_mi_val_15;
330 COMMIT;
331 END IF;
332
333 -- Set STATUS to 1 for interface records with RULE = R and
334 -- MATCH IND = 17,18,19,22,23,24,27
335 IF p_rule IN ('R') THEN
336 UPDATE igs_ad_recruit_int
337 SET
338 status = cst_s_val_1
339 WHERE interface_run_id = p_interface_run_id
340 AND status = cst_s_val_2
341 AND match_ind IN (cst_mi_val_17, cst_mi_val_18, cst_mi_val_19,
342 cst_mi_val_22, cst_mi_val_23, cst_mi_val_24, cst_mi_val_27);
343 COMMIT;
344 END IF;
345
346 -- Set STATUS to 1 and MATCH IND to 19 for interface records with RULE =
347 -- E matching OSS record(s)
348 IF p_rule IN ('E') THEN
349 UPDATE igs_ad_recruit_int in_rec
350 SET
351 status = cst_s_val_1
352 , match_ind = cst_mi_val_19
353 WHERE interface_run_id = p_interface_run_id
354 AND status = cst_s_val_2
355 AND EXISTS ( SELECT 1
356 FROM igs_ad_recruitments mn_rec
357 WHERE mn_rec.person_id = in_rec.person_id);
358 COMMIT;
359 END IF;
360
361 -- Create / Update the OSS record after validating successfully the interface record
362 -- Create
363 -- If RULE E/I/R (match indicator will be 15 or NULL by now no need to check) and
364 -- matching system record not found OR RULE = R and MATCH IND = 16, 25
365 -- Update
366 -- If RULE = I (match indicator will be 15 or NULL by now no need to check) OR
367 -- RULE = R and MATCH IND = 21
368
369 -- Selecting together the interface records for INSERT / UPDATE with DMLMODE identifying
370 -- the DML operation. This is done to have one code section for record validation, exception
371 -- handling and interface table update. This avoids call to separate PLSQL blocks, tuning
372 -- performance on stack maintenance during the process.
373
374 crt_upd_recruitments_dtls(p_interface_run_id);
375
376 -- Set STATUS to 1 and MATCH IND to 23 for interface records with RULE = R matching
377 -- OSS record(s) in ALL updateable column values, if column nullification is not
378 -- allowed then the 2 DECODE should be replaced by a single NVL
379 IF p_rule IN ('R') THEN
380 UPDATE igs_ad_recruit_int in_rec
381 SET
382 status = cst_s_val_1
383 , match_ind = cst_mi_val_23
384 WHERE interface_run_id = p_interface_run_id
385 AND status = cst_s_val_2
386 AND NVL (match_ind, cst_mi_val_15) = cst_mi_val_15
387 AND EXISTS (
388 SELECT 1
389 FROM igs_ad_recruitments mn_rec
390 WHERE NVL(mn_rec.adv_studies_classes, -99) = NVL(in_rec.adv_studies_classes,NVL(mn_rec.adv_studies_classes, -99) )
391 AND NVL(mn_rec.certainty_of_choice_id, -99) = NVL(in_rec.certainty_of_choice_id, NVL(mn_rec.certainty_of_choice_id, -99))
392 AND NVL(mn_rec.class_size, -99) = NVL(in_rec.class_size,NVL(mn_rec.class_size, -99) )
393 AND NVL(mn_rec.deactivate_recruit_status,'~') = NVL(in_rec.deactivate_recruit_status, NVL(mn_rec.deactivate_recruit_status,'~') )
394 AND NVL(mn_rec.degree_goal_id, -99) = NVL(in_rec.degree_goal_id, NVL(mn_rec.degree_goal_id, -99))
395 AND NVL(mn_rec.employment_id, -99) = NVL(in_rec.employment_id, NVL(mn_rec.employment_id, -99))
396 AND NVL(mn_rec.honors_classes, -99) = NVL(in_rec.honors_classes,NVL(mn_rec.honors_classes, -99) )
397 AND NVL(mn_rec.housing_id, -99) = NVL(in_rec.housing_id, NVL(mn_rec.housing_id, -99))
398 AND NVL(mn_rec.institution_control_id, -99) = NVL(in_rec.institution_control_id, NVL(mn_rec.institution_control_id, -99))
399 AND NVL(mn_rec.institution_location_id, -99) = NVL(in_rec.institution_location_id, NVL(mn_rec.institution_location_id, -99))
400 AND NVL(mn_rec.institution_setting_id, -99) = NVL(in_rec.institution_setting_id,NVL(mn_rec.institution_setting_id, -99) )
401 AND NVL(mn_rec.institution_size_id, -99) = NVL(in_rec.institution_size_id, NVL(mn_rec.institution_size_id, -99))
402 AND NVL(mn_rec.percent_plan_higher_edu, -99) = NVL(in_rec.percent_plan_higher_edu, NVL(mn_rec.percent_plan_higher_edu, -99))
403 AND NVL(mn_rec.person_id, -99) = NVL(in_rec.person_id,NVL(mn_rec.person_id, -99) )
404 AND NVL(mn_rec.priority,'~') = NVL(in_rec.priority,NVL(mn_rec.priority,'~'))
405 AND NVL(mn_rec.program_interest_id, -99) = NVL(in_rec.program_interest_id,NVL(mn_rec.program_interest_id,-99))
406 AND NVL(mn_rec.religion_cd, -99) = NVL(in_rec.religion_cd, NVL(mn_rec.religion_cd, -99))
407 AND NVL(mn_rec.sec_school_location_id, -99) = NVL(in_rec.sec_school_location_id,NVL(mn_rec.sec_school_location_id, -99) )
408 AND NVL(mn_rec.special_interest_id, -99) = NVL(in_rec.special_interest_id,NVL(mn_rec.special_interest_id, -99) )
409 AND NVL(mn_rec.special_services_id, -99) = NVL(in_rec.special_services_id,NVL(mn_rec.special_services_id, -99) )
410 AND NVL(mn_rec.unit_set_id, -99) = NVL(in_rec.unit_set_id, NVL(mn_rec.unit_set_id, -99))
411 AND NVL(mn_rec.vip,'~') = NVL(in_rec.vip, NVL(mn_rec.vip,'~'))
412 );
413 COMMIT;
414 END IF;
415
416 -- Set STATUS to 3 and MATCH IND = 20 for interface records with RULE = R and MATCH IND
417 -- <> 21, 25, ones failed discrepancy check
418 IF p_rule IN ('R') THEN
419 UPDATE igs_ad_recruit_int in_rec
420 SET
421 status = cst_s_val_3
422 , match_ind = cst_mi_val_20
423 , dup_recruitment_id = ( SELECT recruitment_id FROM igs_ad_recruitments mn_rec
424 WHERE mn_rec.person_id = in_rec.person_id)
425 WHERE interface_run_id = p_interface_run_id
426 AND status = cst_s_val_2
427 AND NVL (match_ind, cst_mi_val_15) = cst_mi_val_15
428 AND EXISTS ( SELECT rowid FROM igs_ad_recruitments mn_rec
429 WHERE mn_rec.person_id = in_rec.person_id);
430 COMMIT;
431 END IF;
432
433 -- Set STATUS to 3 for interface records with RULE = R and invalid MATCH IND
434 IF p_rule IN ('R') THEN
435 UPDATE igs_ad_recruit_int
436 SET
437 status = cst_s_val_3
438 , error_code = cst_ec_val_E700
439 , error_text = cst_et_val_E700
440 WHERE interface_run_id = p_interface_run_id
441 AND status = cst_s_val_2
442 AND match_ind IS NOT NULL;
443 COMMIT;
444 END IF;
445
446
447 END prc_pe_recruitments_dtl;
448
449 END Igs_Ad_Imp_014;