1 PACKAGE BODY igf_ap_profile_gen_pkg AS
2 /* $Header: IGFAP48B.pls 120.4 2006/02/14 23:05:30 ridas noship $ */
3 ------------------------------------------------------------------
4 --Created by : ugummall, Oracle India
5 --Date created: 04-AUG-2004
6 --
7 --Purpose: Generic routines used in self-service pages and PROFILE Import Process.
8 --
9 --
10 --Known limitations/enhancements and/or remarks:
11 --
12 --Change History:
13 --Who When What
14 -------------------------------------------------------------------
15
16 FUNCTION convert_int(col_value VARCHAR2)
17 RETURN VARCHAR2 IS
18 /*
19 || Created By : ugummall
20 || Created On : 11-Aug-2004
21 || Purpose : This function will return the numberic value of the given column value , i.e taken through the parameter
22 || Known limitations, enhancements or remarks :
23 || Change History :
24 || Who When What
25 || (reverse chronological order - newest change first)
26 */
27
28 BEGIN
29 RETURN TO_NUMBER(col_value);
30 EXCEPTION WHEN others THEN
31 RETURN col_value;
32 END convert_int ;
33
34 PROCEDURE create_base_record (
35 p_css_id IN NUMBER,
36 p_person_id IN NUMBER,
37 p_batch_year IN NUMBER,
38 x_msg_data OUT NOCOPY VARCHAR2,
39 x_return_status OUT NOCOPY VARCHAR2
40 )
41 IS
42 /*
43 || Created By : ugummall
44 || Created On : 05-Aug-2004
45 || Purpose : This Procedure does the following tasks.
46 || 1. Insert a New FA BASE Record if it doesn't exist.
47 || 2. Create a record in PROFILE matched table.
48 || 3. Create a record in FNAR table.
49 || 4. Update PROFILE interface record status to "MATCHED".
50 || 5. Deletes corresponding records in match details and person match table.
51 || Known limitations, enhancements or remarks :
52 || Change History :
53 || Who When What
54 || ridas 14-Feb-2006 Bug #5021084. Removed trunc function from cursor C_SSN.
55 */
56
57 -- Cursor to get cal_type and sequence_number from batch_year
58 CURSOR cur_get_cal_sequence (cp_batch_year igf_ap_batch_aw_map.batch_year%TYPE) IS
59 SELECT ibm.ci_cal_type, ibm.ci_sequence_number
60 FROM IGF_AP_BATCH_AW_MAP ibm
61 WHERE ibm.batch_year = cp_batch_year;
62
63 rec_get_cal_sequence cur_get_cal_sequence%ROWTYPE;
64
65 lv_cal_type igf_ap_batch_aw_map.ci_cal_type%TYPE;
66 lv_sequence_number igf_ap_batch_aw_map.ci_sequence_number%TYPE;
67 lv_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
68 lv_cssp_id igf_ap_css_profile.cssp_id%TYPE;
69
70 lv_profile_value VARCHAR2(10);
71 CURSOR c_ssn(
72 cp_person_id NUMBER
73 ) IS
74 SELECT api_person_id,
75 api_person_id_uf,
76 end_dt
77 FROM igs_pe_alt_pers_id
78 WHERE pe_person_id = cp_person_id
79 AND person_id_type LIKE 'SSN'
80 AND SYSDATE BETWEEN start_dt AND NVL(end_dt,SYSDATE);
81 l_ssn c_ssn%ROWTYPE;
82
83 BEGIN
84
85 fnd_msg_pub.initialize;
86 x_return_status := fnd_api.g_ret_sts_success;
87 SAVEPOINT SP_PROFILE;
88 x_msg_data := '';
89
90 fnd_profile.get('IGF_AP_SSN_REQ_FOR_BASE_REC',lv_profile_value);
91 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
92 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.wrpr_auto_fa_rec.debug','lv_profile_value:'||NVL(lv_profile_value,'N'));
93 END IF;
94 IF NVL(lv_profile_value,'N') = 'Y' THEN
95 OPEN c_ssn(p_person_id);
96 FETCH c_ssn INTO l_ssn;
97 IF c_ssn%NOTFOUND THEN
98 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
99 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.wrpr_auto_fa_rec.debug','c_ssn%NOTFOUND. raising error');
100 END IF;
101 CLOSE c_ssn;
102 x_return_status := fnd_api.g_ret_sts_error;
103 fnd_message.set_name('IGF','IGF_AP_SSN_FOR_BASEREC');
104 x_msg_data := x_msg_data || ' ' || fnd_message.get;
105 RETURN;
106 ELSE
107 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
108 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.wrpr_auto_fa_rec.debug','c_ssn%FOUND.');
109 END IF;
110 CLOSE c_ssn;
111 END IF;
112 END IF;
113
114 IGF_AP_PROFILE_MATCHING_PKG.ss_wrap_create_base_record(p_css_id,p_person_id,p_batch_year);
115 -- Fa Base Record got created successfully. Store success message.
116 fnd_message.set_name('IGF', 'IGF_AP_SUCCESS_FA_BASE');
117 x_msg_data := x_msg_data || ' ' || fnd_message.get;
118 x_return_status := 'S';
119
120 EXCEPTION WHEN OTHERS THEN
121 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
122 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_profile_gen_pkg.create_base_record.exception','The exception is : ' || SQLERRM );
123 END IF;
124 ROLLBACK TO SP_PROFILE;
125 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
126 fnd_message.set_token('NAME','igf_ap_profile_gen_pkg.create_base_record');
127 fnd_file.put_line(fnd_file.log,fnd_message.get || ' - '|| SQLERRM);
128 igs_ge_msg_stack.add;
129 x_return_status := FND_API.G_RET_STS_ERROR;
130
131 fnd_message.set_name('IGF','IGF_AP_FAIL_FA_BASE');
132 x_msg_data := fnd_message.get;
133 x_return_status := FND_API.G_RET_STS_ERROR;
134
135 END create_base_record;
136
137 PROCEDURE create_person_record (
138 p_css_id IN NUMBER,
139 p_person_id OUT NOCOPY NUMBER,
140 p_batch_year IN NUMBER,
141 x_msg_data OUT NOCOPY VARCHAR2,
142 x_return_status OUT NOCOPY VARCHAR2
143 )
144 IS
145 /*
146 || Created By : ugummall
147 || Created On : 05-Aug-2004
148 || Purpose : This Procedure does the following tasks.
149 || 1. Creates a person record.
150 || 2. Creates a person address record.
151 || 3. Creates a FA Base Record.
152 || 4. Creates a record in PROFILE matched table.
153 || 5. Creates a record in FNAR table.
154 || 6. Updates PROFILE interface record status to "MATCHED".
155 || 7. Deletes corresponding records in match details and person match table.
156 || Known limitations, enhancements or remarks :
157 || Change History :
158 || Who When What
159 */
160
161 lv_message2 VARCHAR2(4000);
162
163 BEGIN
164
165 fnd_msg_pub.initialize;
166 x_return_status := fnd_api.g_ret_sts_success;
167 x_msg_data := '';
168
169 SAVEPOINT SP_CREATE_PERSON;
170
171 IGF_AP_PROFILE_MATCHING_PKG.ss_wrap_create_person_record (p_css_id);
172 fnd_message.set_name('IGF', 'IGF_AP_SUCCESS_CREATE_PERSON');
173 x_msg_data := x_msg_data || ' ' ||fnd_message.get;
174 x_return_status := 'S';
175
176
177 EXCEPTION
178 WHEN OTHERS THEN
179 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
180 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_profile_gen_pkg.create_person_record.exception','The exception is : ' || SQLERRM );
181 END IF;
182 ROLLBACK TO SP_CREATE_PERSON;
183 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
184 fnd_message.set_token('NAME','igf_ap_profile_gen_pkg.create_person_record');
185 fnd_file.put_line(fnd_file.log,fnd_message.get || ' - '|| SQLERRM);
186 igs_ge_msg_stack.add;
187 x_return_status := FND_API.G_RET_STS_ERROR;
188
189 END create_person_record;
190
191 PROCEDURE delete_person_match ( p_css_id IN NUMBER)
192 IS
193 /*
194 || Created By : ugummall
195 || Created On : 05-Aug-2004
196 || Purpose : This Procedure does the following tasks.
197 || 1. Deletes records in match details table (child records)
198 || 2. Deletes the person match record (parent record)
199 || Known limitations, enhancements or remarks :
200 || Change History :
201 || Who When What
202 */
203
204 -- Cursor to fetch apm_id from person match table of an profile interface record.
205 CURSOR cur_get_person_match ( cp_css_id igf_ap_person_match_all.css_id%TYPE) IS
206 SELECT ROWID row_id, apm_id
207 FROM IGF_AP_PERSON_MATCH_ALL permatch
208 WHERE permatch.css_id = cp_css_id;
209
210 -- Cursor to fetch rowids of child records (match detail records) of person match record.
211 CURSOR cur_get_match_detail ( cp_apm_id igf_ap_person_match_all.apm_id%TYPE) IS
212 SELECT ROWID row_id
213 FROM IGF_AP_MATCH_DETAILS matchdtls
214 WHERE matchdtls.apm_id = cp_apm_id;
215
216 rec_get_person_match cur_get_person_match%ROWTYPE;
217 rec_get_match_detail cur_get_match_detail%ROWTYPE;
218
219 BEGIN
220
221 -- Get APM_ID from CSS_ID
222 rec_get_person_match := null;
223 OPEN cur_get_person_match(p_css_id);
224 FETCH cur_get_person_match INTO rec_get_person_match;
225 IF cur_get_person_match%FOUND THEN
226 CLOSE cur_get_person_match;
227
228 -- Delete match detail records (child records)
229 FOR rec_get_match_detail IN cur_get_match_detail(rec_get_person_match.apm_id) LOOP
230 igf_ap_match_details_pkg.delete_row(rec_get_match_detail.row_id);
231 END LOOP;
232 -- Delete person match record (parent record)
233 igf_ap_person_match_pkg.delete_row(rec_get_person_match.row_id);
234 ELSE
235 CLOSE cur_get_person_match;
236 END IF;
237
238
239 EXCEPTION
240 WHEN OTHERS THEN
241 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
242 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_profile_gen_pkg.delete_person_match.exception','The exception is : ' || SQLERRM );
243 END IF;
244 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
245 fnd_message.set_token('NAME','igf_ap_profile_gen_pkg.delete_person_match');
246 fnd_file.put_line(fnd_file.log,fnd_message.get);
247 igs_ge_msg_stack.add;
248 END delete_person_match;
249
250 PROCEDURE delete_interface_record ( p_css_id IN NUMBER,
251 x_return_status OUT NOCOPY VARCHAR2
252 )
253 IS
254 /*
255 || Created By : ugummall
256 || Created On : 05-Aug-2004
257 || Purpose : This Procedure does the following tasks.
258 || 1. Deletes the record in PROFILE interface table.
259 || 2. Deletes the corresponding match detail records.
260 || 3. Deletes the corresponding record in person match table.
261 || Known limitations, enhancements or remarks :
262 || Change History :
263 || Who When What
264 */
265
266 -- Cursor to get rowid of the PROFILE interface record.
267 CURSOR cur_get_rowid_interface ( cp_css_id igf_ap_css_interface_all.css_id%TYPE) IS
268 SELECT ROWID row_id
269 FROM IGF_AP_CSS_INTERFACE_ALL intface
270 WHERE intface.css_id = cp_css_id;
271
272 rec_get_rowid_interface cur_get_rowid_interface%ROWTYPE;
273
274 BEGIN
275
276 -- get row id of the PROFILE interface record to be deleted.
277 OPEN cur_get_rowid_interface(p_css_id);
278 FETCH cur_get_rowid_interface INTO rec_get_rowid_interface;
279 IF (cur_get_rowid_interface%NOTFOUND) THEN
280 CLOSE cur_get_rowid_interface;
281 x_return_status := 'E';
282 RETURN;
283 END IF;
284 CLOSE cur_get_rowid_interface;
285
286 -- delete the interface record.
287 igf_ap_css_interface_pkg.delete_row(rec_get_rowid_interface.row_id);
288
289 -- delete person match record and match details records
290 delete_person_match(p_css_id => p_css_id);
291
292 x_return_status := 'S';
293
294 EXCEPTION
295 WHEN OTHERS THEN
296 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
297 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_profile_gen_pkg.delete_interface_record.exception','The exception is : ' || SQLERRM );
298 END IF;
299 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
300 fnd_message.set_token('NAME','igf_ap_profile_gen_pkg.delete_interface_record');
301 fnd_file.put_line(fnd_file.log,fnd_message.get);
302 igs_ge_msg_stack.add;
303 x_return_status := 'E';
304 END delete_interface_record;
305
306 PROCEDURE delete_int_records ( p_css_ids IN VARCHAR2 )
307 IS
308 /*
309 || Created By : ugummall
310 || Created On : 05-Aug-2004
311 || Purpose : This Procedure does the following tasks.
312 || 1. Deletes the record in PROFILE interface table.
313 || 2. Deletes the corresponding match detail records.
314 || 3. Deletes the corresponding record in person match table.
315 || Known limitations, enhancements or remarks :
316 || Change History :
317 || Who When What
318 */
319
320 l_del_css_id VARCHAR2(10);
321 l_css_id VARCHAR2(10);
322 l_css_ids VARCHAR2(1000);
323 x_return_status VARCHAR2(2);
324
325 BEGIN
326
327 l_css_ids := p_css_ids;
328 LOOP
329 l_css_ids := TRIM(SUBSTR(l_css_ids, INSTR(l_css_ids, '*') + 1, LENGTH(l_css_ids)));
330 l_css_id := TRIM(SUBSTR(l_css_ids, 1, INSTR(l_css_ids, '*') - 1));
331 l_css_ids := TRIM(SUBSTR(l_css_ids, INSTR(l_css_ids, ',') + 1, LENGTH(l_css_ids)));
332
333 IF (l_css_id IS NULL) THEN
334 l_del_css_id := l_css_ids;
335 ELSE
336 l_del_css_id := l_css_id;
337 END IF;
338
339 delete_interface_record( p_css_id => l_del_css_id, x_return_status => x_return_status);
340
341 IF (l_css_id IS NULL) THEN
342 EXIT; -- exit from loop.
343 END IF;
344 END LOOP;
345
346 EXCEPTION
347 WHEN OTHERS THEN
348 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
349 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_profile_gen_pkg.delete_int_records.exception','The exception is : ' || SQLERRM );
350 END IF;
351 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
352 fnd_message.set_token('NAME','igf_ap_profile_gen_pkg.delete_int_records');
353 fnd_file.put_line(fnd_file.log,fnd_message.get);
354 igs_ge_msg_stack.add;
355 END delete_int_records;
356
357 PROCEDURE ss_upload_profile ( p_css_id IN NUMBER,
358 x_msg_data OUT NOCOPY VARCHAR2,
359 x_return_status OUT NOCOPY VARCHAR2
360 )
361 IS
362 /*
363 || Created By : ugummall
364 || Created On : 05-Aug-2004
365 || Purpose : This Procedure does the following tasks.
366 || 1. Upload the PROFILE record from interface table to profile table.
367 || 2. Update PROFILE interface record status to "MATCHED".
368 || 3. Deletes corresponding records in match details and person match table.
369 || Known limitations, enhancements or remarks :
370 || Change History :
371 || Who When What
372 */
373
374
375 BEGIN
376
377 fnd_msg_pub.initialize;
378 x_return_status := fnd_api.g_ret_sts_success;
379 x_msg_data := '';
380
381 igf_ap_profile_matching_pkg.ss_wrap_upload_Profile ( p_css_id => p_css_id,
382 x_msg_data => x_msg_data,
383 x_return_status => x_return_status
384 );
385 EXCEPTION WHEN OTHERS THEN
386 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
387 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_profile_gen_pkg.ss_upload_profile.exception','The exception is : ' || SQLERRM );
388 END IF;
389 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
390 fnd_message.set_token('NAME','igf_ap_profile_gen_pkg.ss_upload_profile');
391 fnd_file.put_line(fnd_file.log,fnd_message.get);
392 igs_ge_msg_stack.add;
393 x_return_status := 'E';
394 END ss_upload_profile;
395
396 END igf_ap_profile_gen_pkg;
[Home] [Help]