eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IGS_AD_IMP_PRBLTY_VAL_PKG dependencies on IGS_AD_RECRT_PI_INT

Line 5: FUNCTION duplicate_exist(p_person_id igs_ad_recrt_pi_int.person_id%TYPE,

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: /*

Line 6: p_calculation_date igs_ad_recrt_pi_int.calculation_date%TYPE,

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]

Line 7: p_prblty_type_code_id igs_ad_recrt_pi_int.prblty_type_code_id%TYPE)

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

Line 56: PROCEDURE val_prblty_value(prblty_val_rec igs_ad_recrt_pi_int%ROWTYPE,

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]

Line 57: p_error_code IN OUT NOCOPY igs_ad_recrt_pi_int.error_code%TYPE)

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

Line 82: l_err_code igs_ad_recrt_pi_int.error_code%TYPE;

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

Line 118: p_prblty_val_batch_id IN igs_ad_recrt_pi_int.prblty_val_batch_id%TYPE

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]

Line 134: l_person_id igs_ad_recrt_pi_int.person_id%TYPE;

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;

Line 135: l_error_code igs_ad_recrt_pi_int.error_code%TYPE;

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);

Line 142: CURSOR prblty_val_cur(c_prblty_val_batch_id igs_ad_recrt_pi_int.prblty_val_batch_id%TYPE) IS

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

Line 144: FROM igs_ad_recrt_pi_int arpi

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

Line 150: CURSOR alternate_person_id_cur(c_api_person_id igs_ad_recrt_pi_int.api_person_id%TYPE,

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

Line 151: c_person_id_type igs_ad_recrt_pi_int.person_id_type%TYPE) IS

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 ;

Line 158: CURSOR person_id_cur(c_person_id igs_ad_recrt_pi_int.person_id%TYPE) IS

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:

Line 181: FND_STATS.GATHER_TABLE_STATS(ownname => l_schema, tabname => 'IGS_AD_RECRT_PI_INT_ALL', cascade => TRUE);

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:

Line 212: UPDATE igs_ad_recrt_pi_int

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;

Line 232: UPDATE igs_ad_recrt_pi_int

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;

Line 262: CURSOR null_hdlg_adm_recrt_pi_cur(c_person_id igs_ad_recrt_pi_int.person_id%TYPE,

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

Line 263: c_calculation_date igs_ad_recrt_pi_int.calculation_date%TYPE,

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

Line 264: c_prblty_type_code_id igs_ad_recrt_pi_int.prblty_type_code_id%TYPE) IS

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

Line 302: UPDATE igs_ad_recrt_pi_int

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;

Line 312: UPDATE igs_ad_recrt_pi_int

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;

Line 329: UPDATE igs_ad_recrt_pi_int

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;

Line 343: UPDATE igs_ad_recrt_pi_int

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;

Line 382: UPDATE igs_ad_recrt_pi_int

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;

Line 391: UPDATE igs_ad_recrt_pi_int

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;

Line 407: UPDATE igs_ad_recrt_pi_int

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;

Line 434: FROM igs_ad_recrt_pi_int

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