1 PACKAGE BODY igs_ad_imp_prblty_val_pkg AS
2 /* $Header: IGSADB0B.pls 120.1 2006/01/16 20:23:44 rghosh noship $ */
3
4 --Function to check for the duplicate record based on person_id, calculation_date and probability_type_code_id
5 FUNCTION duplicate_exist(p_person_id igs_ad_recrt_pi_int.person_id%TYPE,
6 p_calculation_date igs_ad_recrt_pi_int.calculation_date%TYPE,
7 p_prblty_type_code_id igs_ad_recrt_pi_int.prblty_type_code_id%TYPE)
8 RETURN VARCHAR2 IS
9 /*
10 || Created By : [email protected]
11 || Created On : 03-AUG-2001
12 || Purpose : This function checks for the presence of duplicate records
13 || And accordingly returns 'Y' or 'N'
14 || Known limitations, enhancements or remarks :
15 || Change History :
16 || Who When What
17 || (reverse chronological order - newest change first)
18 */
19
20 --Cursor to check for duplicate record
21 CURSOR duplicate_check_cur IS
22 SELECT 'X'
23 FROM igs_ad_recruit_pi_v
24 WHERE person_id = p_person_id AND
25 TRUNC(calculation_date) = TRUNC(p_calculation_date) AND
26 probability_type_code_id = p_prblty_type_code_id;
27
28 l_duplicate_exists VARCHAR2(1);
29
30 BEGIN
31 --If no duplicate record exists for that combination RETURN 'N',
32 --else return 'Y'
33 OPEN duplicate_check_cur;
34 FETCH duplicate_check_cur INTO l_duplicate_exists;
35 IF duplicate_check_cur%NOTFOUND THEN
36 CLOSE duplicate_check_cur;
37 RETURN 'N';
38 END IF;
39 CLOSE duplicate_check_cur;
40
41 RETURN 'Y';
42
43 EXCEPTION
44 WHEN OTHERS THEN
45 IF duplicate_check_cur%ISOPEN THEN
46 CLOSE duplicate_check_cur;
47 END IF;
48 --return 'N' if any exception occurs to validate the whole record
49 RETURN 'N';
50
51 END duplicate_exist;
52
53 --Procedure to validate the record
54 --And assign corresponding 'error_code' values to the OUT NOCOPY parameter
55
56 PROCEDURE val_prblty_value(prblty_val_rec igs_ad_recrt_pi_int%ROWTYPE,
57 p_error_code IN OUT NOCOPY igs_ad_recrt_pi_int.error_code%TYPE)
58 IS
59 /*
60 || Created By : [email protected]
61 || Created On : 03-AUG-2001
62 || Purpose : This is a privete procedure, which contains the validation for the
63 || Values in different fields in the Interface table
64 || Known limitations, enhancements or remarks :
65 || Change History :
66 || Who When What
67 || (reverse chronological order - newest change first)
68 */
69 CURSOR prblty_type_val_check_cur IS
70 SELECT 'X'
71 FROM igs_ad_code_classes iacc
72 WHERE iacc.code_id = prblty_val_rec.prblty_type_code_id AND
73 iacc.class = 'PROB_TYPE';
74
75 CURSOR prblty_source_val_check_cur IS
76 SELECT 'X'
77 FROM igs_ad_code_classes iacc
78 WHERE iacc.code_id = prblty_val_rec.prblty_source_code_id AND
79 iacc.class = 'PROB_SOURCE';
80
81 l_validity_check VARCHAR2(1);
82 l_err_code igs_ad_recrt_pi_int.error_code%TYPE;
83
84 BEGIN
85
86 --Validate each field. If validation fails RAISE NO_DATA_FOUND exception and
87 --And assign corresponding 'error_code' values to the OUT NOCOPY parameter
88 OPEN prblty_type_val_check_cur;
89 FETCH prblty_type_val_check_cur INTO l_validity_check;
90 IF prblty_type_val_check_cur%NOTFOUND THEN
91 l_err_code := 'E001'; -- 'E001' Validation failed for Probability Type Code Id
92 CLOSE prblty_type_val_check_cur;
93 RAISE NO_DATA_FOUND;
94 END IF;
95
96 CLOSE prblty_type_val_check_cur;
97
98 IF prblty_val_rec.prblty_source_code_id IS NOT NULL THEN
99 OPEN prblty_source_val_check_cur;
100 FETCH prblty_source_val_check_cur INTO l_validity_check;
101 IF prblty_source_val_check_cur%NOTFOUND THEN
102 l_err_code := 'E002'; -- 'E002' Validation failed for Probability Source Code Id
103 CLOSE prblty_source_val_check_cur;
104 RAISE NO_DATA_FOUND;
105 END IF;
106 CLOSE prblty_source_val_check_cur;
107 END IF;
108
109 EXCEPTION
110 WHEN NO_DATA_FOUND THEN
111 p_error_code := l_err_code;
112 END val_prblty_value;
113
114 --The main procedure for processing
115 PROCEDURE prc_prblty_value(
116 errbuf OUT NOCOPY VARCHAR2,
117 retcode OUT NOCOPY NUMBER,
118 p_prblty_val_batch_id IN igs_ad_recrt_pi_int.prblty_val_batch_id%TYPE
119 )
120 IS
121 /*
122 || Created By : [email protected]
123 || Created On : 03-AUG-2001
124 || Purpose : This is the driving procedure for the concurrent job
125 || 'Import Probability Values'
126 || Known limitations, enhancements or remarks :
127 || Change History :
128 || Who When What
129 || (reverse chronological order - newest change first)
130 */
131
132 --User defined exception to skip the record for further processing whenever any error occurs
133 skip_this_record EXCEPTION;
134 l_person_id igs_ad_recrt_pi_int.person_id%TYPE;
135 l_error_code igs_ad_recrt_pi_int.error_code%TYPE;
136 l_rowid ROWID;
137 l_probability_index_id igs_ad_recruit_pi.probability_index_id%TYPE;
138 l_records_processed NUMBER := 0;
139 l_exists VARCHAR2(1);
140
141 --Cursor to select all the records in pending status in the batch_id as given by the user
142 CURSOR prblty_val_cur(c_prblty_val_batch_id igs_ad_recrt_pi_int.prblty_val_batch_id%TYPE) IS
143 SELECT arpi.*
144 FROM igs_ad_recrt_pi_int arpi
145 WHERE arpi.prblty_val_batch_id = c_prblty_val_batch_id AND
146 arpi.status = '2' ; -- '2' pending
147
148 --Cursor to find out NOCOPY the person ID based upon Alternate person ID and Person ID type
149 --while the person ID IS NULL
150 CURSOR alternate_person_id_cur(c_api_person_id igs_ad_recrt_pi_int.api_person_id%TYPE,
151 c_person_id_type igs_ad_recrt_pi_int.person_id_type%TYPE) IS
152 SELECT pe_person_id
153 FROM igs_pe_person_id_type_v
154 WHERE api_person_id = c_api_person_id AND
155 person_id_type = c_person_id_type ;
156
157 --Cursor to check whether the person is valid
158 CURSOR person_id_cur(c_person_id igs_ad_recrt_pi_int.person_id%TYPE) IS
159 SELECT 'X'
160 FROM HZ_PARTIES
161 WHERE party_id = c_person_id;
162
163 prblty_val_rec prblty_val_cur%ROWTYPE;
164
165 l_gather_status VARCHAR2(5);
166 l_industry VARCHAR2(5);
167 l_schema VARCHAR2(30);
168 l_gather_return BOOLEAN;
169 l_owner VARCHAR2(30);
170 BEGIN
171 -- The following code is added for disabling of OSS in R12.IGS.A - Bug 4955192
172 igs_ge_gen_003.set_org_id(null);
173
174 retcode := 0;
175
176 -- Gather statistics for interface table
177 -- by rrengara on 20-jan-2003 bug 2711176
178
179 BEGIN
180 l_gather_return := fnd_installation.get_app_info('IGS', l_gather_status, l_industry, l_schema);
181 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema, tabname => 'IGS_AD_RECRT_PI_INT_ALL', cascade => TRUE);
182 EXCEPTION WHEN OTHERS THEN
183 NULL;
184 END;
185
186
187 --Open the cursor fetching all the records for processing
188 --Process the records one by one
189 OPEN prblty_val_cur(p_prblty_val_batch_id);
190 LOOP
191
192 BEGIN
193
194 FETCH prblty_val_cur INTO prblty_val_rec;
195 EXIT WHEN prblty_val_cur%NOTFOUND;
196
197 l_records_processed := l_records_processed + 1;
198 l_person_id := prblty_val_rec.person_id;
199
200 -- if person ID in the interface table is null then
201 -- find out NOCOPY the person ID based upon alternate person ID and person ID type
202 -- if it is not null find out NOCOPY whether its a valid person
203 IF l_person_id IS NULL THEN
204 OPEN alternate_person_id_cur(prblty_val_rec.api_person_id, prblty_val_rec.person_id_type);
205 FETCH alternate_person_id_cur INTO l_person_id;
206 CLOSE alternate_person_id_cur;
207 ELSE
208 OPEN person_id_cur(prblty_val_rec.person_id) ;
209 FETCH person_id_cur INTO l_exists;
210 IF person_id_cur%NOTFOUND THEN
211 CLOSE person_id_cur;
212 UPDATE igs_ad_recrt_pi_int
213 SET error_code = 'E007', -- 'E007' Invalid person
214 status = '3' , -- '3' Error
215 match_ind = NULL
216 WHERE prblty_val_int_id = prblty_val_rec.prblty_val_int_id;
217
218 l_error_code := 'E007';
219
220 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PRBLTY_VALUE_ERROR');
221 FND_MESSAGE.SET_TOKEN('PRBLTY_VAL_INT_ID',prblty_val_rec.prblty_val_int_id);
222 FND_MESSAGE.SET_TOKEN('ERROR_CODE',l_error_code);
223 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
224 RAISE skip_this_record;
225 END IF;
226 CLOSE person_id_cur;
227 END IF;
228
229 --if the person_id could not be found then stop further processing of the record
230 --update the error_code and status accordingly
231 IF l_person_id IS NULL THEN
232 UPDATE igs_ad_recrt_pi_int
233 SET error_code = 'E006', -- 'E006' Insufficient Information of a person
234 status = '3' , -- '3' Error
235 match_ind = NULL
236 WHERE prblty_val_int_id = prblty_val_rec.prblty_val_int_id;
237 l_error_code := 'E006';
238
239 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PRBLTY_VALUE_ERROR');
240 FND_MESSAGE.SET_TOKEN('PRBLTY_VAL_INT_ID',prblty_val_rec.prblty_val_int_id);
241 FND_MESSAGE.SET_TOKEN('ERROR_CODE',l_error_code);
242 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
243 RAISE skip_this_record;
244 END IF;
245
246 --Check whether its a duplicate record
247 --If it is a duplicate record check the value in match_ind
248 --If match_ind = '21' then validate the record and import the values if validation is successful
249 --If match_ind <> '21' then update the record for the status = '3', error_code = 'E003' and match_ind = '23'
250 --If its not a duplicate record validate the record. If validation successful then import the record.
251 IF duplicate_exist(l_person_id,prblty_val_rec.calculation_date,prblty_val_rec.prblty_type_code_id) = 'Y' THEN
252
253 IF prblty_val_rec.match_ind = '21' THEN -- '21' Match reviewed and to be imported
254
255 --Call the private procedure for validation
256 val_prblty_value(prblty_val_rec,l_error_code);
257
258 --If error_code is null, validation is successful
259 --Import the record by updating the OSS table with values from interface table
260 IF l_error_code IS NULL THEN
261 DECLARE
262 CURSOR null_hdlg_adm_recrt_pi_cur(c_person_id igs_ad_recrt_pi_int.person_id%TYPE,
263 c_calculation_date igs_ad_recrt_pi_int.calculation_date%TYPE,
264 c_prblty_type_code_id igs_ad_recrt_pi_int.prblty_type_code_id%TYPE) IS
265 SELECT *
266 FROM igs_ad_recruit_pi_v
267 WHERE person_id = c_person_id AND
268 calculation_date = c_calculation_date AND
269 probability_type_code_id = c_prblty_type_code_id;
270
271 null_hdlg_adm_recrt_pi_rec null_hdlg_adm_recrt_pi_cur%ROWTYPE;
272 BEGIN
273 OPEN null_hdlg_adm_recrt_pi_cur(l_person_id,prblty_val_rec.calculation_date,prblty_val_rec.prblty_type_code_id);
274 FETCH null_hdlg_adm_recrt_pi_cur INTO null_hdlg_adm_recrt_pi_rec;
275 CLOSE null_hdlg_adm_recrt_pi_cur;
276
277 --Call the lock row of the OSS table TBH to check whether that particular record is locked
278 --If locked then it will throw an exception for nowait condition and the updation will be skipped.
279 igs_ad_recruit_pi_pkg.lock_row (
280 x_rowid => null_hdlg_adm_recrt_pi_rec.row_id,
281 x_probability_index_id => null_hdlg_adm_recrt_pi_rec.probability_index_id,
282 x_person_id => null_hdlg_adm_recrt_pi_rec.person_id,
283 x_probability_type_code_id => null_hdlg_adm_recrt_pi_rec.probability_type_code_id,
284 x_calculation_date => null_hdlg_adm_recrt_pi_rec.calculation_date,
285 x_probability_value => null_hdlg_adm_recrt_pi_rec.probability_value,
286 x_probability_source_code_id => null_hdlg_adm_recrt_pi_rec.probability_source_code_id
287 );
288
289 igs_ad_recruit_pi_pkg.update_row (
290 x_mode => 'R',
291 x_rowid => null_hdlg_adm_recrt_pi_rec.row_id,
292 x_probability_index_id => null_hdlg_adm_recrt_pi_rec.probability_index_id,
293 x_person_id => null_hdlg_adm_recrt_pi_rec.person_id,
294 x_probability_type_code_id => null_hdlg_adm_recrt_pi_rec.probability_type_code_id,
295 x_calculation_date => null_hdlg_adm_recrt_pi_rec.calculation_date,
296 x_probability_value => NVL(prblty_val_rec.probability_value,null_hdlg_adm_recrt_pi_rec.probability_value),
297 x_probability_source_code_id => NVL(prblty_val_rec.prblty_source_code_id,null_hdlg_adm_recrt_pi_rec.probability_source_code_id)
298 );
299
300 --If Updation is successful make the status '1'
301 --so that it can be deleted from interface table
302 UPDATE igs_ad_recrt_pi_int
303 SET error_code = NULL,
304 status = '1' , --'1' Complete
305 match_ind = NULL
306 WHERE prblty_val_int_id = prblty_val_rec.prblty_val_int_id;
307
308 EXCEPTION
309 WHEN OTHERS THEN
310
311 --If Update is unsuccessful then make the status 'Error' with
312 UPDATE igs_ad_recrt_pi_int
313 SET error_code = 'E005', -- 'E005' Update failed
314 status = '3' ,
315 match_ind = NULL
316 WHERE prblty_val_int_id = prblty_val_rec.prblty_val_int_id;
317
318 l_error_code := 'E005';
319
320 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PRBLTY_VALUE_ERROR');
321 FND_MESSAGE.SET_TOKEN('PRBLTY_VAL_INT_ID',prblty_val_rec.prblty_val_int_id);
322 FND_MESSAGE.SET_TOKEN('ERROR_CODE',l_error_code);
323 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
324 RAISE skip_this_record;
325 END;
326
327 ELSE -- Error_code is not NULL after validation.
328 -- Make the status 'Error' and error_code as obtained after validation
329 UPDATE igs_ad_recrt_pi_int
330 SET error_code = l_error_code,
331 status = '3' ,
332 match_ind = NULL
333 WHERE prblty_val_int_id = prblty_val_rec.prblty_val_int_id;
334
335 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PRBLTY_VALUE_ERROR');
336 FND_MESSAGE.SET_TOKEN('PRBLTY_VAL_INT_ID',prblty_val_rec.prblty_val_int_id);
337 FND_MESSAGE.SET_TOKEN('ERROR_CODE',l_error_code);
338 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
339 RAISE skip_this_record;
340 END IF; --error_code comparision
341
342 ELSE -- If match_ind <> '21' then make the status 'Error'
343 UPDATE igs_ad_recrt_pi_int
344 SET error_code = 'E003', -- 'E003' Duplicate record found
345 status = '3' ,
346 match_ind = '23' -- '23' Match to be reviewed, but there was no discrepancy and so retaining the existing
347 WHERE prblty_val_int_id = prblty_val_rec.prblty_val_int_id;
348
349 l_error_code := 'E003';
350
351 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PRBLTY_VALUE_ERROR');
352 FND_MESSAGE.SET_TOKEN('PRBLTY_VAL_INT_ID',prblty_val_rec.prblty_val_int_id);
353 FND_MESSAGE.SET_TOKEN('ERROR_CODE',l_error_code);
354 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
355 RAISE skip_this_record;
356
357
358 END IF; --match_ind comparision
359
360 ELSE --If its not a duplicate record
361 -- do validation for the values in the record
362 val_prblty_value(prblty_val_rec,l_error_code);
363
364 --if error_code is NULL, validation is successful
365 --import the record by creating a new record in OSS table taking values from Interface table
366 IF l_error_code IS NULL THEN
367 BEGIN
368
369 igs_ad_recruit_pi_pkg.insert_row (
370 x_mode => 'R',
371 x_rowid => l_rowid,
372 x_probability_index_id => l_probability_index_id,
373 x_person_id => l_person_id,
374 x_probability_type_code_id => prblty_val_rec.prblty_type_code_id,
375 x_calculation_date => prblty_val_rec.calculation_date,
376 x_probability_value => prblty_val_rec.probability_value,
377 x_probability_source_code_id => prblty_val_rec.prblty_source_code_id
378 );
379
380 --If insertion is successful make the status '1'
381 --so that it can be deleted from Interface table
382 UPDATE igs_ad_recrt_pi_int
383 SET error_code = NULL,
384 status = '1' ,
385 match_ind = NULL
386 WHERE prblty_val_int_id = prblty_val_rec.prblty_val_int_id;
387
388 EXCEPTION
389 WHEN OTHERS THEN
390 --If insertion is unsuccessful make the status 'Error'
391 UPDATE igs_ad_recrt_pi_int
392 SET error_code = 'E004', -- 'E004' Insert failed
393 status = '3' ,
394 match_ind = NULL
395 WHERE prblty_val_int_id = prblty_val_rec.prblty_val_int_id;
396
397 l_error_code := 'E004';
398
399 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PRBLTY_VALUE_ERROR');
400 FND_MESSAGE.SET_TOKEN('PRBLTY_VAL_INT_ID',prblty_val_rec.prblty_val_int_id);
401 FND_MESSAGE.SET_TOKEN('ERROR_CODE',l_error_code);
402 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
403 RAISE skip_this_record;
404 END;
405 ELSE -- Error_code is not NULL after validation.
406 -- Make the status 'Error' and error_code as obtained after validation
407 UPDATE igs_ad_recrt_pi_int
408 SET error_code = l_error_code,
409 status = '3' ,
410 match_ind = NULL
411 WHERE prblty_val_int_id = prblty_val_rec.prblty_val_int_id;
412
413 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PRBLTY_VALUE_ERROR');
414 FND_MESSAGE.SET_TOKEN('PRBLTY_VAL_INT_ID',prblty_val_rec.prblty_val_int_id);
415 FND_MESSAGE.SET_TOKEN('ERROR_CODE',l_error_code);
416 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
417 RAISE skip_this_record;
418 END IF; --error_code comparision
419
420 END IF; --duplicate record check condition
421 EXCEPTION
422 --Whenever any error occurs skip further processing of that record
423 WHEN skip_this_record THEN
424
425 --refresh the variable after the processing of each record
426 l_error_code := null;
427 null;
428
429 END;--End of processing of one record
430 END LOOP; -- Start processing for the next record
431
432 --Delete all the records which have a status 'Complete' after the processing
433 DELETE
434 FROM igs_ad_recrt_pi_int
435 WHERE status = '1' AND
436 prblty_val_batch_id = p_prblty_val_batch_id;
437
438 --Display the no of records processed in the log File
439 FND_MESSAGE.SET_NAME('IGS','IGS_AD_TOT_REC_PRC');
440 FND_MESSAGE.SET_TOKEN('RCOUNT',l_records_processed);
441 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
442
443 EXCEPTION
444 WHEN OTHERS THEN
445 ROLLBACK;
446 RETCODE :=2;
447 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
448 igs_ge_msg_stack.conc_exception_hndl;
449
450 END prc_prblty_value;
451
452 END igs_ad_imp_prblty_val_pkg;
[Home] [Help]