1 PACKAGE BODY igs_or_inst_imp_003_pkg AS
2 /* $Header: IGSOR16B.pls 120.4 2006/06/23 05:51:00 gmaheswa noship $ */
3 /***************************************************************
4 Created By : mesriniv
5 Date Created By : 2001/07/12
6 Purpose : This is the third part of
7 Import Institutions Package
8 Known Limitations,Enhancements or Remarks
9
10 Change History :
11 ENH Bug No : 1872994
12 ENH Desc : Modelling and Forcasting DLD- Institutions Build
13 Who When What
14 npalanis 10-JUN-2003 Bug:2923413 igs_pe_typ_instances_pkg
15 calls modified for the new employment
16 category column added in the table
17 masehgal 19-Aug-2002 # 2502020 Removed validation for Phone Number already existing in OSS while importing institutions
18 Removed Commented Code
19 npalanis 16-feb-2002 In cursor cur_get_oss_contacts customer_id is removed and org_party_id is added
20 Cursor rel_party_id_cur is added to get relationship party id
21 Cursor cur_get_lst_date is modified to get last_update_date from HZ_ORG_CONTACT_ROLES table
22 In Procedure process_contact_phones rel_party_id is passed as argument instead of cust_acct_role_id
23 In Cursor cur_get_oss_phones reference to cust_acct_role_id is removed and owner_table_id is used to pick the record
24 In igs_or_contacts_v.insert_row and igs_or_contacts_v.update_row all the attributes that references to customer are removed
25 In igs_or_phones_v.insert_row and igs_or_phones_v.update_row all the attributes that references to customer are removed
26 pkpatel 25-OCT-2002 Bug No: 2613704
27 Modified stat_type_id to stat_type_cd
28 pkpatel 3-JAn-2003 Bug 2730137
29 Added the validation for contact party id in process_institution_contacts
30 ssawhney V2API OVN changes...igs_or_contacts + igs_or_phones.
31 ssaleem 22-SEP-2003 The following changes were done for IGS.L
32 a) passed values for create method as 'CREATE_IMPORT'
33 b) In the WHEN OTHERS block, replaced FND_MESSAGE.GET with
34 FND_MESSAGE.PARSE_ENCODED
35 c) Removed the check for 'IGS_AD_EVAL_XST_NO_PROSPCT'
36 d) Error code 'E162' is replaced with 'E049'
37 c) FND_FILE.PUT_LINE method calls replaced with
38 logging mechanism using FND_LOG package
39 gmaheswa 27-Jan-2006 Bug:4938278 : process_institution_address: Raise Address Change event at after processing address data of all persons.
40 gmaheswa 22-Jun-06 Bug 5189180: in process_institution_address, if igs_pe_person_addr_pkg.insert_row returns return_status as 'W' then set erro code E022.
41 ***************************************************************/
42
43 PROCEDURE process_institution_notes(
44 p_interface_id IN igs_or_inst_nts_int.interface_id%TYPE,
45 p_party_id IN hz_parties.party_id%TYPE,
46 p_party_number IN hz_parties.party_number%TYPE)
47
48 /***************************************************************
49 Created By : mesriniv
50 Date Created By : 2001/07/12
51 Purpose : This is the third part of
52 Import Institutions Package
53 Known Limitations,Enhancements or Remarks
54 Change History :
55 ENH Bug No : 1872994
56
57 ENH Desc : Modelling and Forcasting DLD- Institutions Build
58 Who When What
59 ***************************************************************/
60 AS
61 l_insert_success BOOLEAN;
62 l_update_success BOOLEAN;
63 l_party_number hz_parties.party_number%TYPE;
64 l_dml_operation VARCHAR2(10);
65 l_row_id ROWID;
66 l_org_note_seq igs_or_inst_nts_int.org_note_sequence%TYPE;
67 l_oss_rowid ROWID;
68 l_val_fail_err_code igs_or_inst_nts_int.error_code%TYPE;
69 l_err_cd igs_or_inst_nts_int.error_code%TYPE;
70 l_exists varchar2(1);
71 SKIP_NOTE EXCEPTION;
72
73 --Cursor to fetch the Interface Notes Records for the Interface Identifier
74 CURSOR cur_get_int_notes(cp_interface_id igs_or_inst_nts_int.interface_id%TYPE,cp_status igs_or_inst_nts_int.status%TYPE) IS
75 SELECT *
76 FROM igs_or_inst_nts_int
77 WHERE interface_id =cp_interface_id
78 AND status =cp_status;
79
80 --Cursor to fetch the Notes records in OSS
81 CURSOR cur_get_oss_notes(p_note_type igs_or_org_notes.org_note_type%TYPE,
82 p_org_seq_num igs_or_org_notes.org_note_sequence%TYPE,
83 cp_party_number hz_parties.party_number%TYPE
84 ) IS
85 SELECT org_notes.rowid,org_notes.*
86 FROM igs_or_org_notes org_notes
87 WHERE org_note_type = p_note_type
88 AND org_structure_id = cp_party_number
89 AND org_note_sequence = p_org_seq_num ;
90
91 oss_notes_rec cur_get_oss_notes%ROWTYPE;
92
93 --Cursor added for field level validation for ORG_NOTE_TYPE
94 CURSOR c_val_note_type(p_org_note_type igs_or_inst_nts_int.org_note_type%TYPE,
95 cp_inst_flag igs_or_org_note_type.inst_flag%TYPE) IS
96 SELECT 'Y'
97 FROM igs_or_org_note_type
98 WHERE org_notes_type = p_org_note_type
99 AND inst_flag=cp_inst_flag;
100
101 c_val_note_type_rec c_val_note_type%rowtype;
102
103 --Procedure to update the Notes Interface Table
104 PROCEDURE update_int_notes(p_int_notes_rec igs_or_inst_nts_int%ROWTYPE,
105 p_err_cd igs_or_inst_nts_int.error_code%type)
106 AS
107
108 BEGIN
109
110 --Since there is no Table Handler Direct Update on Table
111 UPDATE igs_or_inst_nts_int
112 SET status = p_int_notes_rec.status ,
113 error_code = p_err_cd
114 WHERE interface_inst_notes_id = p_int_notes_rec.interface_inst_notes_id;
115
116 --put this information about the failed record in the log file too
117 IF p_int_notes_rec.status = '3' THEN
118 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
119 FND_MESSAGE.SET_NAME('IGS','IGS_OR_INST_IMP_FAIL');
120 FND_MESSAGE.SET_TOKEN('INT_ID', p_int_notes_rec.interface_inst_notes_id);
121 FND_MESSAGE.SET_TOKEN('ERROR_CODE', p_err_cd);
122 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
123 'igs.plsql.igs_or_inst_imp_003.process_institution_notes.' || p_err_cd,
124 FND_MESSAGE.GET,NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
125 END IF;
126 END IF;
127 END update_int_notes;
128
129 BEGIN
130
131 l_party_number := p_party_number;
132 IF l_party_number IS NULL THEN
133 RAISE NO_DATA_FOUND;
134 END IF;
135
136 --Iterate through the Interface Notes records
137 FOR int_notes_rec IN cur_get_int_notes(p_interface_id,'2') LOOP
138
139 BEGIN
140 l_insert_success:=FALSE;
141 l_update_success:=FALSE;
142 l_dml_operation :=NULL;
143
144 --Save Point for every Interface Notes record
145 SAVEPOINT notes;
146
147 --Fetch the Notes records in OSS
148 OPEN cur_get_oss_notes(int_notes_rec.org_note_type,int_notes_rec.org_note_sequence,l_party_number);
149 FETCH cur_get_oss_notes INTO oss_notes_rec;
150
151 IF cur_get_oss_notes%NOTFOUND THEN
152 --validating data before inserting
153 l_exists := NULL;
154 OPEN c_val_note_type(int_notes_rec.org_note_type,'Y');
155 FETCH c_val_note_type INTO l_exists;
156 CLOSE c_val_note_type;
157
158 IF l_exists = 'Y' THEN
159 l_dml_operation:='INSERT';
160 --Insert the Interface Records into OSS
161 l_row_id:=NULL;
162 l_org_note_seq:=NULL;
163 igs_or_org_notes_pkg.insert_row(
164 x_rowid => l_row_id,
165 x_org_structure_id => LTRIM(RTRIM(l_party_number)),
166 x_org_structure_type => 'INSTITUTE',
167 x_org_note_sequence => l_org_note_seq,
168 x_org_note_type => int_notes_rec.org_note_type,
169 x_start_date => int_notes_rec.start_date,
170 x_end_date => int_notes_rec.end_date,
171 x_note_text => int_notes_rec.note_text,
172 x_mode => 'R'
173 );
174
175 l_insert_success:=TRUE;
176 l_val_fail_err_code:=NULL;
177 ELSE
178 l_val_fail_err_code:='E018';
179 int_notes_rec.status :='3';
180 update_int_notes(int_notes_rec,l_val_fail_err_code);
181 RAISE SKIP_NOTE;
182 END IF;
183
184
185 --There is a duplicate record in OSS
186 ELSE
187 --Update the OSS record with new details
188 l_exists := NULL;
189 OPEN c_val_note_type(int_notes_rec.org_note_type,'Y');
190 FETCH c_val_note_type INTO l_exists;
191 CLOSE c_val_note_type;
192
193 IF l_exists = 'Y' THEN
194 l_dml_operation:='UPDATE';
195 igs_or_org_notes_pkg.update_row(
196 x_rowid =>oss_notes_rec.rowid,
197 x_org_structure_id =>oss_notes_rec.org_structure_id,
198 x_org_structure_type =>'INSTITUTE',
199 x_org_note_sequence =>oss_notes_rec.org_note_sequence,
200 x_org_note_type =>oss_notes_rec.org_note_type,
201 x_start_date =>NVL(int_notes_rec.start_date,oss_notes_rec.start_date),
202 x_end_date =>NVL(int_notes_rec.end_date,oss_notes_rec.end_date),
203 x_note_text =>NVL(int_notes_rec.note_text,oss_notes_rec.note_text),
204 x_mode =>'R'
205 );
206 l_update_success:=TRUE;
207 l_val_fail_err_code:=NULL;
208 ELSE
209 l_val_fail_err_code:='E018';
210 int_notes_rec.status :='3';
211 update_int_notes(int_notes_rec,l_val_fail_err_code);
212 RAISE SKIP_NOTE;
213 END IF;
214
215 END IF;
216 CLOSE cur_get_oss_notes;
217 int_notes_rec.status :='1';
218 --Update the Interface record status as SUCCESS
219 update_int_notes(int_notes_rec,NULL);
220 EXCEPTION
221 WHEN SKIP_NOTE THEN
222 IF cur_get_oss_notes%ISOPEN THEN
223 CLOSE cur_get_oss_notes;
224 END IF;
225 IF c_val_note_type%ISOPEN THEN
226 CLOSE c_val_note_type;
227 END IF;
228
229 WHEN OTHERS THEN
230 IF cur_get_oss_notes%ISOPEN THEN
231 CLOSE cur_get_oss_notes;
232 END IF;
233 IF c_val_note_type%ISOPEN THEN
234 CLOSE c_val_note_type;
235 END IF;
236 IF l_dml_operation='INSERT' THEN
237 IF l_val_fail_err_code IS NULL THEN
238 l_err_cd:='E019';
239 ELSE
240 l_err_cd:=l_val_fail_err_code;
241 END IF;
242 ELSIF l_dml_operation='UPDATE' THEN
243 IF l_val_fail_err_code IS NULL THEN
244 l_err_cd:='E020';
245 ELSE
246 l_err_cd:=l_val_fail_err_code;
247 END IF;
248 END IF;
249 int_notes_rec.status :='3';
250 --Update the Interface record status as ERROR
251 update_int_notes(int_notes_rec, l_err_cd);
252 END;
253 END LOOP;
254
255 EXCEPTION
256 WHEN NO_DATA_FOUND THEN
257 --log message that the party_id passed is Invalid, Party ID passed must be present in Hz_parties
258 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
259 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
260 'igs.plsql.igs_or_inst_imp_003.process_institution_notes.nodatafound',
261 'Invalid Party Id: '||p_party_id ||' ,Party ID passed must be present in HZ_parties' || SQLERRM,
262 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
263 END IF;
264 WHEN OTHERS THEN
265 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
266 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
267 FND_MESSAGE.SET_TOKEN('NAME','igs_or_inst_imp_003.process_institution_notes');
268
269 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
270 'igs.plsql.igs_or_inst_imp_003.process_institution_notes.others',
271 FND_MESSAGE.Get || '-' || SQLERRM,
272 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
273 END IF;
274 APP_EXCEPTION.RAISE_EXCEPTION;
275 END process_institution_notes;
276
277
278 --Procedure to process the Contacts
279 PROCEDURE process_institution_contacts(
280 p_interface_id IN igs_or_inst_con_int.interface_id%TYPE,
281 p_person_type IN igs_pe_person.party_type%TYPE,
282 p_party_id IN hz_parties.party_id%TYPE)
283 /***************************************************************
284 Created By : mesriniv
285 Date Created By : 2001/07/12
286 Purpose : This is the third part of
287 Import Institutions Package
288 Known Limitations,Enhancements or Remarks
289
290 Change History :
291 ENH Bug No : 1872994
292 ENH Desc : Modelling and Forcasting DLD- Institutions Build
293 Who When What
294 masehgal 19-Aug-2002 # 2502020 Removed validation for Phone Number
295 already existing in OSS
296 pkpatel 3-JAn-2003 Bug 2730137
297 Added the validation for contact party id
298 vskumar 31-May-2006 Xbuild3 performance related fix. changed cursor select stmt cur_get_oss_contacts.
299 ***************************************************************/
300
301 AS
302
303 l_contact_id hz_parties.party_id%TYPE;
304 l_sys_refer hz_parties.orig_system_reference%TYPE;
305 l_last_update_date DATE;
306 l_party_last_update_date DATE;
307 l_org_cont_last_update_date DATE;
308 l_org_contact_id igs_or_contacts_v.org_contact_id%TYPE;
309 l_or_cont_id igs_or_contacts_v.org_contact_id%TYPE;
310 l_org_id igs_pe_typ_instances_all.org_id%TYPE;
311 l_instance_id igs_pe_typ_instances.type_instance_id%TYPE;
312 l_contact_point_id igs_or_contacts_v.org_contact_id%TYPE;
313 l_contact_number igs_or_contacts_v.contact_number%TYPE;
314 l_cont_dml_operation VARCHAR2(10);
315 l_lst_update_date DATE;
316 l_org_contact_role_id igs_or_contacts_v.contact_id%TYPE;
317 l_rel_party_id igs_or_contacts_v.rel_party_id%TYPE;
318 l_type_rowid ROWID;
319 l_cont_point_last_update_date DATE;
320 l_prel_last_update_date DATE;
321 l_rel_party_last_update_date DATE;
322 l_return_status VARCHAR2(1000);
323 l_msg_count NUMBER;
324 l_msg_data VARCHAR2(1000);
325 l_email_address hz_contact_points.email_address%TYPE;
326 SKIP_CONTACT EXCEPTION;
327 l_val_fail_err_cd igs_or_inst_con_int.error_code%TYPE;
328 l_exists VARCHAR2(1);
329
330 l_org_role_ovn igs_or_contacts_v.org_role_ovn%TYPE;
331 l_rel_ovn igs_or_contacts_v.rel_ovn%TYPE;
332 l_rel_party_ovn igs_or_contacts_v.rel_party_ovn%TYPE;
333 l_org_cont_ovn igs_or_contacts_v.org_cont_ovn%TYPE;
334 l_contact_point_ovn hz_contact_points.object_version_number%TYPE;
335
336 --Cursor to fetch the Pending Interface Contacts
337 CURSOR cur_get_int_contacts(cp_interface_id igs_or_inst_con_int.interface_id%TYPE,
338 cp_status igs_or_inst_con_int.status%TYPE) IS
339 SELECT *
340 FROM igs_or_inst_con_int
341 WHERE interface_id = cp_interface_id
342 AND status = cp_status;
343
344 --Cursor to check if such a Contact ID exists in OSS
345 CURSOR cur_get_oss_contacts(p_contact_party_id igs_or_inst_con_int.contact_party_id%TYPE,cp_party_id hz_parties.party_id%TYPE) IS
346 SELECT org_conts.attribute_category, org_conts.attribute10, org_conts.attribute11, org_conts.attribute12,
347 org_conts.attribute13, org_conts.attribute14, org_conts.attribute15, org_conts.attribute16,
348 org_conts.attribute17, org_conts.attribute18, org_conts.attribute19, org_conts.attribute20,
349 org_conts.attribute1, org_conts.attribute2, org_conts.attribute21, org_conts.attribute22,
350 org_conts.attribute23, org_conts.attribute24, org_conts.attribute3, org_conts.attribute4,
351 org_conts.attribute5, org_conts.attribute6, org_conts.attribute7, org_conts.attribute8,
352 org_conts.attribute9, org_conts.contact_number, TO_NUMBER(NULL) contact_id, org_conts.mail_stop,
353 org_conts.OBJECT_VERSION_NUMBER org_cont_ovn, org_conts.org_contact_id, TO_NUMBER(NULL) org_role_ovn,
354 org_conts.title, rel.last_update_login, rel.last_updated_by, rel.OBJECT_VERSION_NUMBER rel_ovn,
355 rel.PARTY_ID rel_party_id, rel.relationship_id, rel.status, rel.subject_id contact_party_id,
356 hz.OBJECT_VERSION_NUMBER rel_party_ovn
357 FROM
358 HZ_ORG_CONTACTS org_conts,
359 HZ_RELATIONSHIPS rel,
360 HZ_PARTIES hz
361 WHERE
362 org_conts.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
363 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
364 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
365 AND REL.DIRECTIONAL_FLAG = 'F'
366 AND REL.RELATIONSHIP_TYPE ='CONTACT'
367 AND REL.OBJECT_ID = hz.PARTY_ID
368 AND REL.SUBJECT_ID = p_contact_party_id
369 AND REL.OBJECT_ID = cp_party_id;
370
371 -- ssawhney added OVN
372 CURSOR email_cur (cp_rel_party_id igs_or_contacts_v.rel_party_id%TYPE,
373 cp_contact_point_type hz_contact_points.contact_point_type%TYPE,
374 cp_owner_table_name hz_contact_points.owner_table_name%TYPE) IS
375 SELECT email_address, contact_point_id, object_version_number
376 FROM hz_contact_points
377 WHERE owner_table_id = cp_rel_party_id
378 AND contact_point_type = cp_contact_point_type
379 AND owner_table_name = cp_owner_table_name;
380
381 --cursor for field level validation of data , for TITLE field (tray)
382 CURSOR c_val_contact_title(p_title igs_or_inst_con_int.title%TYPE,
383 cp_lookup_type fnd_lookup_values.lookup_type%TYPE,
384 cp_enabled_flag fnd_lookup_values.enabled_flag%TYPE) IS
385 SELECT 'X'
386 FROM fnd_lookup_values
387 WHERE lookup_type = cp_lookup_type
388 AND lookup_code = p_title
389 AND enabled_flag = cp_enabled_flag
390 AND view_application_id = 222
391 AND security_group_id = 0
392 AND language = userenv('LANG');
393
394 CURSOR check_person_cur(cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
395 SELECT 'X'
396 FROM igs_pe_person_base_v
397 WHERE person_id = cp_person_id;
398
399 oss_cont_rec cur_get_oss_contacts%ROWTYPE;
400 c_val_contact_title_rec c_val_contact_title%ROWTYPE;
401
402 --Procedure to Update the status of the Contact Id's Interface Record
403 PROCEDURE update_int_contact(p_status igs_or_inst_con_int.status%TYPE,
404 p_interface_contacts_id igs_or_inst_con_int.interface_contacts_id%TYPE,
405 p_contact_party_id igs_or_inst_con_int.contact_party_id%TYPE,
406 p_err_code igs_or_inst_con_int.error_code%TYPE,
407 p_err_text igs_or_inst_con_int.error_text%TYPE
408 )
409
410 AS
411
412 BEGIN
413
414 --Since there is no TBH direct Updation on Table
415 UPDATE igs_or_inst_con_int
416 SET status = p_status ,
417 error_code = p_err_code,
418 error_text = p_err_text
419 WHERE interface_contacts_id =p_interface_contacts_id;
420
421 --put this information about the failed record in the log file too
422 IF p_status ='3' THEN
423 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
424 FND_MESSAGE.SET_NAME('IGS','IGS_OR_INST_IMP_FAIL');
425 FND_MESSAGE.SET_TOKEN('INT_ID', p_interface_contacts_id);
426 FND_MESSAGE.SET_TOKEN('ERROR_CODE', p_err_code);
427 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
428 'igs.plsql.igs_or_inst_imp_003.update_int_contact.' || p_err_code,
429 FND_MESSAGE.GET,
430 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
431 END IF;
432 END IF;
433
434 END update_int_contact;
435
436 --Associate the person type
437 PROCEDURE associate_persontype(p_interface_contacts_id igs_or_inst_con_int.interface_contacts_id%TYPE,
438 p_contact_party_id igs_or_inst_con_int.contact_party_id%TYPE
439 ) IS
440
441 l_person_type igs_pe_typ_instances.person_type_code%TYPE;
442
443 --Cursor to fetch person type
444 CURSOR cur_person_type(cp_contact_party_id igs_or_inst_con_int.contact_party_id%TYPE) IS
445 SELECT person_type_code
446 FROM igs_pe_typ_instances_all
447 WHERE person_id = cp_contact_party_id AND
448 SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE);
449
450 p_status VARCHAR2(1);
451 p_error_code VARCHAR2(30);
452 l_message_name VARCHAR2(30);
453 l_app VARCHAR2(50);
454
455 BEGIN
456
457 --Check if there is a Person Type for the Contact Id
458 OPEN cur_person_type(p_contact_party_id);
459 FETCH cur_person_type INTO l_person_type;
460
461 --Associate the person type if not defined before.
462 IF cur_person_type%NOTFOUND THEN
463
464 l_type_rowid:=NULL;
465 l_instance_id:=NULL;
466
467 --Create the person type for this contact id
468 igs_pe_typ_instances_pkg.insert_row
469 (
470 x_rowid => l_type_rowid,
471 x_person_id => p_contact_party_id,
472 x_course_cd => NULL,
473 x_type_instance_id => l_instance_id,
474 x_person_type_code => p_person_type,
475 x_cc_version_number => NULL,
476 x_funnel_status => NULL,
477 x_admission_appl_number => NULL,
478 x_nominated_course_cd => NULL,
479 x_ncc_version_number => NULL,
480 x_sequence_number => NULL,
481 x_start_date => TRUNC(SYSDATE),
482 x_end_date => NULL,
483 x_create_method => 'CREATE_IMPORT',
484 x_ended_by => NULL,
485 x_end_method => NULL,
486 x_mode => 'R',
487 x_org_id => l_org_id,
488 x_emplmnt_category_code => NULL
489 );
490 END IF;
491 CLOSE cur_person_type;
492 EXCEPTION
493 WHEN OTHERS THEN
494 CLOSE cur_person_type;
495 --Rollback changes
496 ROLLBACK TO contact;
497 p_status := '3';
498
499 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
500
501 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
502 -- The following code checks what message is returned from the API in case of an exception.
503 -- It notes the message name and puts the same in the log file
504 -- Added as per enhancement during Evaluate Applicant Qualifications and Make decisions build
505
506 IF l_message_name = 'IGS_AD_PROSPCT_XST_NO_EVAL' THEN
507 p_error_code := 'E049';
508 -- write in log message that the evaluator already exists
509 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PROSPCT_XST_NO_EVAL');
510 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
511 'igs.plsql.igs_or_inst_imp_003.associate_persontype.evalexst',
512 FND_MESSAGE.Get || '-' || SQLERRM,
513 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
514
515 ELSE
516 p_error_code := 'E049';
517 -- Write the warning to log file --
518 FND_MESSAGE.SET_NAME('IGS','IGS_OR_ERROR_PERSONTYPE');
519 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
520 'igs.plsql.igs_or_inst_imp_003.associate_persontype.perstype',
521 FND_MESSAGE.Get || '-' || SQLERRM,
522 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
523 END IF;
524
525 END IF;
526 --Update the interface record status as 3
527 --insert into chek values('a1');
528 update_int_contact(p_status,p_interface_contacts_id,p_contact_party_id,p_error_code,NULL);
529 RAISE SKIP_CONTACT;
530 END associate_persontype;
531
532 --Process the Contact Phones
533 PROCEDURE process_contact_phones( p_interface_cont_id IN igs_or_inst_cphn_int.interface_inst_cont_phone_id%TYPE,
534 p_rel_party_id IN igs_or_contacts_v.rel_party_id%TYPE)
535 /*
536 || Change History
537 ||
538 || Who When What
539 || ssawhney OVN logic added. change to signature of IGS_OR_CONTACTS_V
540 */
541 AS
542 l_insert_success BOOLEAN;
543 l_pty_id hz_parties.party_id%TYPE;
544 l_update_success BOOLEAN;
545 l_dml_operation VARCHAR2(10);
546 l_row_id ROWID;
547 l_phone_id igs_or_phones_v.phone_id%TYPE;
548 l_msg_cnt NUMBER;
549 l_msg_dt VARCHAR2(1000);
550 l_ret_status VARCHAR2(1);
551 l_last_update_date DATE;
552 l_orig_sys_ref VARCHAR2(10);
553 SKIP_PHONE EXCEPTION;
554 l_val_fail_err_cd igs_or_inst_cphn_int.error_code%TYPE;
555 l_ovn hz_contact_points.object_version_number%TYPE;
556
557
558 --Cursor to fetch the Phone information from Phone Interface Table for the Interface Identifier
559 CURSOR cur_get_int_phones(cp_interface_cont_id igs_or_inst_cphn_int.interface_inst_cont_phone_id%TYPE,
560 cp_status igs_or_inst_cphn_int.status%TYPE) IS
561 SELECT *
562 FROM igs_or_inst_cphn_int
563 WHERE interface_cont_id =cp_interface_cont_id
564 AND status =cp_status;
565
566 int_phones_rec cur_get_int_phones%ROWTYPE;
567
568 --cursor for validating data before importing phones (tray)
569 CURSOR c_val_phone(p_type igs_or_inst_cphn_int.type%TYPE,
570 cp_lookup_type fnd_lookup_values.lookup_type%TYPE,
571 cp_enabled_flag fnd_lookup_values.enabled_flag%TYPE) IS
572 SELECT 'X'
573 FROM fnd_lookup_values
574 WHERE lookup_type = cp_lookup_type
575 AND lookup_code = p_type
576 AND enabled_flag = cp_enabled_flag
577 AND view_application_id = 222
578 AND security_group_id = 0
579 AND language = userenv('LANG');
580
581 c_val_phone_rec c_val_phone%rowtype;
582
583 CURSOR country_validate (p_country_code IGS_OR_INST_CPHN_INT.COUNTRY_CODE%TYPE)IS
584 SELECT phone_country_code
585 FROM fnd_territories_vl ter, hz_phone_country_codes hzc
586 WHERE ter.territory_code = hzc.territory_code
587 AND hzc.phone_country_code = p_country_code ;
588
589 country_validate_rec country_validate%ROWTYPE;
590
591 --Procedure to update the Phones Interface Table
592 PROCEDURE update_int_phones(p_int_phones_rec igs_or_inst_cphn_int%ROWTYPE,
593 p_err_cd igs_or_inst_cphn_int.error_code%TYPE,
594 p_err_text igs_or_inst_cphn_int.error_text%TYPE
595 )
596 AS
597
598 BEGIN
599
600 --Since there is no Table Handler Direct Update on Table
601 UPDATE igs_or_inst_cphn_int
602 SET status = p_int_phones_rec.status,
603 error_code = p_err_cd,
604 error_text = p_err_text
605 WHERE interface_inst_cont_phone_id = p_int_phones_rec.interface_inst_cont_phone_id;
606
607 --put this information about the failed record in the log file too
608 IF p_int_phones_rec.status='3' THEN
609 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
610 FND_MESSAGE.SET_NAME('IGS','IGS_OR_INST_IMP_FAIL');
611 FND_MESSAGE.SET_TOKEN('INT_ID', p_int_phones_rec.interface_inst_cont_phone_id);
612 FND_MESSAGE.SET_TOKEN('ERROR_CODE', p_err_cd);
613 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
614 'igs.plsql.igs_or_inst_imp_003.update_int_phones.' || p_err_cd,
615 FND_MESSAGE.GET,
616 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
617 END IF;
618 END IF;
619 END update_int_phones;
620
621
622 BEGIN --main proc begins
623
624 --Iterate through the Interface Phones records
625 FOR int_phones_rec IN cur_get_int_phones(p_interface_cont_id,'2') LOOP
626 BEGIN
627 l_insert_success := FALSE;
628 l_update_success := FALSE;
629 l_dml_operation := NULL;
630 l_msg_data := NULL;
631 l_ret_status := NULL;
632 l_msg_count := NULL;
633 l_last_update_date := NULL;
634 l_orig_sys_ref := NULL;
635 l_phone_id := NULL;
636 int_phones_rec.type := UPPER(int_phones_rec.type);
637
638 --Create a save point
639 SAVEPOINT phones;
640 IF int_phones_rec.country_code IS NOT NULL THEN
641 OPEN country_validate(int_phones_rec.country_code);
642 FETCH country_validate INTO country_validate_rec;
643 IF country_validate%NOTFOUND THEN
644 CLOSE country_validate;
645 l_val_fail_err_cd :='E050';
646 int_phones_rec.status:='3';
647 update_int_phones(int_phones_rec,l_val_fail_err_cd,NULL);
648 RAISE SKIP_PHONE;
649 END IF;
650 CLOSE country_validate;
651 END IF;
652
653 -- Validate the phone "TYPE" before inserting
654 OPEN c_val_phone(int_phones_rec.TYPE,'PHONE_LINE_TYPE','Y');
655 FETCH c_val_phone into c_val_phone_rec;
656 IF c_val_phone%FOUND THEN
657 l_dml_operation:='INSERT';
658 --Insert the Interface Records into OSS
659 igs_or_phones_pkg.insert_row(
660 X_Phone_Id => l_phone_id,
661 X_Last_Update_Date => NULL,
662 X_Last_Updated_By => NULL,
663 X_Creation_Date => NULL,
664 X_Created_By => NULL,
665 X_phone_number => int_phones_rec.Phone_Number,
666 X_status => 'A',
667 X_phone_type => int_phones_rec.TYPE,
668 X_Last_Update_Login => NULL,
669 X_Country_code => int_phones_rec.country_Code,
670 X_Area_Code => int_phones_rec.area_code,
671 X_Extension => int_phones_rec.extension,
672 X_Primary_Flag => 'N',
673 X_Orig_System_Reference => l_orig_sys_ref,
674 X_Attribute_Category => int_phones_rec.attribute_category,
675 X_Attribute1 => int_phones_rec.attribute1,
676 X_Attribute2 => int_phones_rec.attribute2,
677 X_Attribute3 => int_phones_rec.attribute3,
678 X_Attribute4 => int_phones_rec.attribute4,
679 X_Attribute5 => int_phones_rec.attribute5,
680 X_Attribute6 => int_phones_rec.attribute6,
681 X_Attribute7 => int_phones_rec.attribute7,
682 X_Attribute8 => int_phones_rec.attribute8,
683 X_Attribute9 => int_phones_rec.attribute9,
684 X_Attribute10 => int_phones_rec.attribute10,
685 X_Attribute11 => int_phones_rec.attribute11,
686 X_Attribute12 => int_phones_rec.attribute12,
687 X_Attribute13 => int_phones_rec.attribute13,
688 X_Attribute14 => int_phones_rec.attribute14,
689 X_Attribute15 => int_phones_rec.attribute15,
690 X_Attribute16 => int_phones_rec.attribute16,
691 X_Attribute17 => int_phones_rec.attribute17,
692 X_Attribute18 => int_phones_rec.attribute18,
693 X_Attribute19 => int_phones_rec.attribute19,
694 X_Attribute20 => int_phones_rec.attribute20,
695 x_party_id => p_rel_party_id ,
696 x_party_site_id => NULL,
697 x_msg_count => l_msg_cnt,
698 x_msg_data => l_msg_dt,
699 x_return_status => l_ret_status,
700 x_contact_point_ovn => l_ovn
701 );
702 --Check if any error in Inserting.
703 IF l_ret_status IN ('E','U') THEN
704 --Error while inserting the Phone details
705
706 l_val_fail_err_cd :='E047';
707 int_phones_rec.status:='3';
708 update_int_phones(int_phones_rec,l_val_fail_err_cd,NULL);
709
710 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
711 FND_MESSAGE.SET_NAME('IGS','IGS_OR_PHONEINSERT_ERROR');
712 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
713 'igs.plsql.igs_or_inst_imp_003.process_contact_phones.' || l_val_fail_err_cd,
714 'contact phone id: ' || l_phone_id || '-' || FND_MESSAGE.GET || '-' || l_msg_dt,
715 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
716 END IF;
717
718 RAISE SKIP_PHONE;
719 ELSE
720 l_insert_success:=TRUE;
721 END IF;
722 ELSE
723 l_val_fail_err_cd :='E026';
724 int_phones_rec.status:='3';
725 update_int_phones(int_phones_rec,l_val_fail_err_cd,NULL);
726 RAISE SKIP_PHONE;
727 END IF;
728 CLOSE c_val_phone;
729
730 int_phones_rec.status :='1';
731 --Update the Interface record status as SUCCESS
732 update_int_phones(int_phones_rec,NULL,NULL);
733
734 EXCEPTION
735 WHEN SKIP_PHONE THEN
736 IF c_val_phone%ISOPEN THEN
737 CLOSE c_val_phone;
738 END IF;
739
740 IF country_validate%ISOPEN THEN
741 CLOSE country_validate;
742 END IF;
743
744
745 WHEN OTHERS THEN
746 IF c_val_phone%ISOPEN THEN
747 CLOSE c_val_phone;
748 END IF;
749 IF country_validate%ISOPEN THEN
750 CLOSE country_validate;
751 END IF;
752 IF l_dml_operation='INSERT' THEN
753 int_phones_rec.status :='3';
754 update_int_phones(int_phones_rec,'E047',NULL);
755 ELSIF l_dml_operation='UPDATE' THEN
756 int_phones_rec.status :='3';
757 update_int_phones(int_phones_rec,'E048',NULL);
758 END IF;
759 RAISE SKIP_PHONE;
760 END;
761 END LOOP;
762 EXCEPTION
763 WHEN OTHERS THEN
764 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
765 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
766 'igs.plsql.igs_or_inst_imp_003.process_contacts_phones.others',
767 SQLERRM,
768 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
769 END IF;
770
771 APP_EXCEPTION.RAISE_EXCEPTION;
772 END process_contact_phones;
773
774 BEGIN
775
776 -- starting main procedure for import of contacts
777
778 IF p_party_id IS NULL THEN
779 RAISE NO_DATA_FOUND;
780 END IF;
781
782 --Fetch the records for processing
783 FOR int_cont_rec IN cur_get_int_contacts(p_interface_id,'2') LOOP
784
785 BEGIN
786
787 l_cont_dml_operation := NULL;
788 l_return_status :=NULL;
789 l_msg_count :=NULL;
790 l_msg_data :=NULL;
791 int_cont_rec.title := UPPER(int_cont_rec.title);
792
793 --Create a savepoint
794 SAVEPOINT contact;
795
796 l_exists := NULL;
797 OPEN check_person_cur(int_cont_rec.contact_party_id);
798 FETCH check_person_cur INTO l_exists;
799 IF check_person_cur%NOTFOUND THEN
800 l_val_fail_err_cd :='E054';
801 int_cont_rec.status:='3';
802 update_int_contact(int_cont_rec.status,int_cont_rec.interface_contacts_id,int_cont_rec.contact_party_id,l_val_fail_err_cd,NULL);
803 RAISE SKIP_CONTACT;
804 END IF;
805 CLOSE check_person_cur;
806
807 --Fetch the Duplicate record from OSS if any
808 OPEN cur_get_oss_contacts(int_cont_rec.contact_party_id,p_party_id);
809 FETCH cur_get_oss_contacts INTO oss_cont_rec;
810
811 --If the Contact Id is already existing then go for update of record IF NO1
812 IF cur_get_oss_contacts%FOUND THEN
813
814 IF int_cont_rec.title IS NOT NULL THEN
815 OPEN c_val_contact_title(int_cont_rec.title,'CONTACT_TITLE','Y');
816 FETCH c_val_contact_title INTO c_val_contact_title_rec;
817 IF c_val_contact_title%NOTFOUND THEN
818 l_val_fail_err_cd :='E021';
819 int_cont_rec.status:='3';
820 update_int_contact(int_cont_rec.status,int_cont_rec.interface_contacts_id,int_cont_rec.contact_party_id,l_val_fail_err_cd,NULL);
821 RAISE SKIP_CONTACT;
822 END IF;
823 CLOSE c_val_contact_title;
824 END IF;
825
826 --update the existing details
827 l_cont_dml_operation :='UPDATE';
828
829 -- re initalise the variables before assigning
830 l_contact_point_id := NULL;
831 l_org_contact_role_id := NULL;
832
833 -- ssawhney OVN modifications
834 OPEN email_cur( oss_cont_rec.rel_party_id,'EMAIL','HZ_PARTIES') ;
835 FETCH email_cur INTO l_email_address , l_contact_point_id ,l_contact_point_ovn;
836 CLOSE email_cur;
837
838 l_org_contact_role_id := oss_cont_rec.contact_id;
839 l_org_role_ovn := oss_cont_rec.org_role_ovn ; -- P_ORG_ROLE_OVN
840 l_rel_ovn := oss_cont_rec.rel_ovn ; -- P_REL_OVN
841 l_rel_party_ovn := oss_cont_rec.rel_party_ovn ; -- P_REL_PARTY_OVN
842 l_org_cont_ovn := oss_cont_rec.org_cont_ovn ; -- P_ORG_CONT_OVN
843
844 igs_or_contacts_pkg.update_row(
845 x_last_name => NULL,
846 x_last_updated_by => oss_cont_rec.last_updated_by,
847 x_last_update_date => l_lst_update_date,
848 x_party_last_update_date => l_party_last_update_date,
849 x_org_cont_last_update_date => l_org_cont_last_update_date,
850 x_cont_point_last_update_date => l_cont_point_last_update_date,
851 x_prel_last_update_date => l_prel_last_update_date,
852 x_rel_party_last_update_date => l_rel_party_last_update_date,
853 x_status => oss_cont_rec.status,
854 x_contact_key => NULL,
855 x_first_name => NULL,
856 x_job_title => NULL,
857 x_last_update_login => oss_cont_rec.last_update_login,
858 x_mail_stop => NVL( int_cont_rec.mail_stop, oss_cont_rec.mail_stop),
859 x_title => NVL(int_cont_rec.title, oss_cont_rec.title),
860 x_attribute_category => NVL( int_cont_rec.attribute_category,oss_cont_rec.attribute_category),
861 x_attribute1 => NVL (int_cont_rec.attribute1,oss_cont_rec.attribute1),
862 x_attribute2 => NVL (int_cont_rec.attribute2,oss_cont_rec.attribute2),
863 x_attribute3 => NVL (int_cont_rec.attribute3,oss_cont_rec.attribute3),
864 x_attribute4 => NVL (int_cont_rec.attribute4,oss_cont_rec.attribute4),
865 x_attribute5 => NVL (int_cont_rec.attribute5,oss_cont_rec.attribute5),
866 x_attribute6 => NVL (int_cont_rec.attribute6,oss_cont_rec.attribute6),
867 x_attribute7 => NVL (int_cont_rec.attribute7,oss_cont_rec.attribute7),
868 x_attribute8 => NVL (int_cont_rec.attribute8,oss_cont_rec.attribute8),
869 x_attribute9 => NVL (int_cont_rec.attribute9,oss_cont_rec.attribute9),
870 x_attribute10 => NVL (int_cont_rec.attribute10,oss_cont_rec.attribute10),
871 x_attribute11 => NVL (int_cont_rec.attribute11,oss_cont_rec.attribute11),
872 x_attribute12 => NVL (int_cont_rec.attribute12,oss_cont_rec.attribute12),
873 x_attribute13 => NVL (int_cont_rec.attribute13,oss_cont_rec.attribute13),
874 x_attribute14 => NVL (int_cont_rec.attribute14,oss_cont_rec.attribute14),
875 x_attribute15 => NVL (int_cont_rec.attribute15,oss_cont_rec.attribute15),
876 x_attribute16 => NVL (int_cont_rec.attribute16,oss_cont_rec.attribute16),
877 x_attribute17 => NVL (int_cont_rec.attribute17,oss_cont_rec.attribute17),
878 x_attribute18 => NVL (int_cont_rec.attribute18,oss_cont_rec.attribute18),
879 x_attribute19 => NVL (int_cont_rec.attribute19,oss_cont_rec.attribute19),
880 x_attribute20 => NVL (int_cont_rec.attribute20,oss_cont_rec.attribute20),
881 x_attribute21 => oss_cont_rec.attribute21, --There are only 20 Attributes in Interface
882 x_attribute22 => oss_cont_rec.attribute22,
883 x_attribute23 => oss_cont_rec.attribute23,
884 x_attribute24 => oss_cont_rec.attribute24,
885 x_attribute25 => NULL,
886 x_email_address => NVL(int_cont_rec.email,l_email_address),
887 x_last_name_alt => NULL,
888 x_first_name_alt => NULL ,
889 x_contact_number => oss_cont_rec.contact_number,
890 x_party_id => p_party_id,
891 x_party_site_id => NULL ,
892 x_contact_party_id => oss_cont_rec.contact_party_id ,
893 x_org_contact_id => oss_cont_rec.org_contact_id,
894 x_contact_point_id => l_contact_point_id,
895 x_org_contact_role_id => l_org_contact_role_id,
896 x_party_relationship_id => oss_cont_rec.relationship_id ,
897 x_return_status => l_return_status,
898 x_msg_count => l_msg_count,
899 x_msg_data => l_msg_data,
900 x_rel_party_id => oss_cont_rec.rel_party_id,
901 P_ORG_ROLE_OVN => l_org_role_ovn,
902 P_REL_OVN => l_rel_ovn,
903 P_REL_PARTY_OVN => l_rel_party_ovn,
904 P_ORG_CONT_OVN => l_org_cont_ovn,
905 P_CONTACT_POINT_OVN => l_contact_point_ovn
906 );
907 l_rel_party_id := oss_cont_rec.rel_party_id;
908
909 --Check if any error in Updating.
910 IF l_return_status IN ('E','U') THEN
911 --Error while updating the Contact Id
912 l_val_fail_err_cd :='E025';
913 int_cont_rec.status:='3';
914 Update_int_contact(int_cont_rec.status,int_cont_rec.interface_contacts_id,int_cont_rec.contact_party_id,l_val_fail_err_cd,NULL);
915
916 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
917 FND_MESSAGE.SET_NAME('IGS','IGS_OR_CONTUPDATE_ERROR');
918 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
919 'igs.plsql.igs_or_inst_imp_003.process_institution_contacts.' || l_val_fail_err_cd,
920 FND_MESSAGE.GET || '-' || l_msg_data,
921 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
922 END IF;
923
924 RAISE SKIP_CONTACT;
925 ELSE
926 --Assign values for uploading Phone details appropriately
927 l_or_cont_id :=oss_cont_rec.org_contact_id;
928 END IF;
929
930 --Elsif the Contact Id is not found creating a new contact
931 ELSE
932 IF int_cont_rec.title IS NOT NULL THEN
933 OPEN c_val_contact_title(int_cont_rec.title,'CONTACT_TITLE','Y');
934 FETCH c_val_contact_title INTO c_val_contact_title_rec;
935 IF c_val_contact_title%NOTFOUND THEN
936 l_val_fail_err_cd :='E021';
937 int_cont_rec.status:='3';
938 update_int_contact(int_cont_rec.status,int_cont_rec.interface_contacts_id,int_cont_rec.contact_party_id,l_val_fail_err_cd,NULL);
939 RAISE SKIP_CONTACT;
940 END IF;
941 CLOSE c_val_contact_title;
942 END IF;
943
944 l_cont_dml_operation := 'INSERT';
945 l_contact_id := NULL;
946 l_org_contact_role_id := NULL;
947 l_org_role_ovn := NULL;
948 l_rel_ovn := NULL;
949 l_rel_party_ovn := NULL;
950 l_org_cont_ovn := NULL;
951 l_contact_point_ovn := NULL;
952
953 igs_or_contacts_pkg.insert_row
954 (
955 x_last_name => NULL,
956 x_orig_system_reference => int_cont_rec.reference,
957 x_status => 'A',
958 x_contact_key => NULL,
959 x_first_name => NULL,
960 x_job_title => NULL,
961 x_mail_stop => int_cont_rec.mail_stop,
962 x_title => int_cont_rec.title,
963 x_attribute_category => int_cont_rec.attribute_category,
964 x_attribute1 => int_cont_rec.attribute1,
965 x_attribute2 => int_cont_rec.attribute2,
966 x_attribute3 => int_cont_rec.attribute3,
967 x_attribute4 => int_cont_rec.attribute4,
968 x_attribute5 => int_cont_rec.attribute5,
969 x_attribute6 => int_cont_rec.attribute6,
970 x_attribute7 => int_cont_rec.attribute7,
971 x_attribute8 => int_cont_rec.attribute8,
972 x_attribute9 => int_cont_rec.attribute9,
973 x_attribute10 => int_cont_rec.attribute10,
974 x_attribute11 => int_cont_rec.attribute11,
975 x_attribute12 => int_cont_rec.attribute12,
976 x_attribute13 => int_cont_rec.attribute13,
977 x_attribute14 => int_cont_rec.attribute14,
978 x_attribute15 => int_cont_rec.attribute15,
979 x_attribute16 => int_cont_rec.attribute16,
980 x_attribute17 => int_cont_rec.attribute17,
981 x_attribute18 => int_cont_rec.attribute18,
982 x_attribute19 => int_cont_rec.attribute19,
983 x_attribute20 => int_cont_rec.attribute20,
984 x_attribute21 => NULL,
985 x_attribute22 => NULL,
986 x_attribute23 => NULL,
987 x_attribute24 => NULL,
988 x_attribute25 => NULL,
989 x_email_address => int_cont_rec.email,
990 x_last_name_alt => NULL,
991 x_first_name_alt => NULL ,
992 x_contact_number => l_contact_number,
993 x_party_id => p_party_id ,
994 x_party_site_id => NULL ,
995 x_contact_party_id => int_cont_rec.contact_party_id ,
996 x_org_contact_id => l_org_contact_id,
997 x_contact_point_id => l_contact_point_id,
998 x_org_contact_role_id => l_org_contact_role_id,
999 x_rel_party_id => l_rel_party_id,
1000 x_created_by => NULL,
1001 x_creation_date => NULL,
1002 x_updated_by => NULL,
1003 x_update_date => NULL,
1004 x_last_update_login => NULL,
1005 x_return_status => l_return_status,
1006 x_msg_count => l_msg_count,
1007 x_msg_data => l_msg_data,
1008 P_ORG_ROLE_OVN => l_org_role_ovn,
1009 P_REL_OVN => l_rel_ovn,
1010 P_REL_PARTY_OVN => l_rel_party_ovn,
1011 P_ORG_CONT_OVN => l_org_cont_ovn,
1012 P_CONTACT_POINT_OVN => l_contact_point_ovn
1013 );
1014
1015
1016 --Check if any error in Inserting
1017 IF l_return_status IN ('E','U') THEN
1018 --Error while Inserting the Contact Id
1019 l_val_fail_err_cd :='E024';
1020 int_cont_rec.status:='3';
1021 Update_int_contact(int_cont_rec.status,int_cont_rec.interface_contacts_id,int_cont_rec.contact_party_id,l_val_fail_err_cd,NULL);
1022
1023 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
1024 FND_MESSAGE.SET_NAME('IGS','IGS_OR_CONTINSERT_ERROR');
1025 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
1026 'igs.plsql.igs_or_inst_imp_003.process_institution_contacts.' || l_val_fail_err_cd ,
1027 FND_MESSAGE.GET || '-' || l_msg_data,
1028 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
1029 END IF;
1030
1031 RAISE SKIP_CONTACT;
1032 END IF;
1033 --Assign values to upload the Phone details appropriately
1034 l_or_cont_id:=l_org_contact_id;
1035
1036 END IF; --End of check for cur_get_oss_contacts found or not
1037 CLOSE cur_get_oss_contacts;
1038
1039 --Associate the Person Type for the Contact ID
1040 associate_persontype(int_cont_rec.interface_contacts_id,int_cont_rec.contact_party_id);
1041
1042
1043
1044 --Based on whether the Contact ID was updated or Inserted
1045 --Need to upload the Phone details
1046 process_contact_phones(int_cont_rec.interface_contacts_id,l_rel_party_id);
1047
1048 --Irrespective of Errors or Complete in Contact Phones Information Update the Contact Info status as
1049 --1,Complete
1050 update_int_contact('1',int_cont_rec.interface_contacts_id,int_cont_rec.contact_party_id,NULL,NULL);
1051
1052 EXCEPTION
1053 WHEN SKIP_CONTACT THEN
1054 IF c_val_contact_title%ISOPEN THEN
1055 CLOSE c_val_contact_title;
1056 END IF;
1057 IF cur_get_oss_contacts%ISOPEN THEN
1058 CLOSE cur_get_oss_contacts;
1059 END IF;
1060 IF check_person_cur%ISOPEN THEN
1061 CLOSE check_person_cur;
1062 END IF;
1063
1064 WHEN OTHERS THEN
1065 IF c_val_contact_title%ISOPEN THEN
1066 CLOSE c_val_contact_title;
1067 END IF;
1068 IF cur_get_oss_contacts%ISOPEN THEN
1069 CLOSE cur_get_oss_contacts;
1070 END IF;
1071 --Error while Updating the Contact Id
1072 IF l_cont_dml_operation='UPDATE' THEN
1073 update_int_contact('3',int_cont_rec.interface_contacts_id,int_cont_rec.contact_party_id,'E025',NULL);
1074 END IF;
1075 --Error while Inserting the Contact Id
1076 IF l_cont_dml_operation='INSERT' THEN
1077 update_int_contact('3',int_cont_rec.interface_contacts_id,int_cont_rec.contact_party_id,'E025',NULL);
1078 END IF;
1079 END;
1080 END LOOP;
1081 EXCEPTION
1082 WHEN NO_DATA_FOUND THEN
1083 --log message that the party_id passed is , Party ID passed must be present in Hz_partie
1084 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
1085 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
1086 'igs.plsql.igs_or_inst_imp_003.process_institution_contacts.nodatafound',
1087 'Invalid Party Id: '||p_party_id ||' ,Party ID passed must be present in HZ_parties - ' || SQLERRM,
1088 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
1089 END IF;
1090 WHEN OTHERS THEN
1091 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
1092 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
1093 'igs.plsql.igs_or_inst_imp_003.process_institution_contacts.others',
1094 SQLERRM,
1095 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
1096 END IF;
1097 APP_EXCEPTION.RAISE_EXCEPTION;
1098 END process_institution_contacts;
1099
1100 --Procedure to Upload the Institution Statistics
1101 PROCEDURE process_institution_statistics (
1102 p_interface_id IN igs_or_inst_stat_int.interface_id%TYPE,
1103 p_party_id IN hz_parties.party_id%TYPE)
1104 /***************************************************************
1105 Created By : mesriniv
1106 Date Created By : 2001/07/12
1107 Purpose : This is the third part of
1108 Import Institutions Package
1109 Known Limitations,Enhancements or Remarks
1110
1111 Change History :
1112 ENH Bug No : 1872994
1113 ENH Desc : Modelling and Forcasting DLD- Institutions Build
1114 Who When What
1115 pkpatel 25-OCT-2002 Bug No: 2613704
1116 Modified stat_type_id to stat_type_cd
1117 ***************************************************************/
1118 AS
1119 l_mast_insert_success BOOLEAN;
1120 l_mast_dml_operation VARCHAR2(10);
1121
1122 l_det_insert_success BOOLEAN;
1123 l_det_update_success BOOLEAN;
1124 l_det_dml_operation VARCHAR2(10);
1125 l_row_id ROWID;
1126 l_inst_stat_id igs_or_inst_stats.inst_stat_id%TYPE;
1127 l_det_row_id ROWID;
1128 l_dtl_id igs_or_inst_stat_dtl.inst_stat_dtl_id%TYPE;
1129 l_party_number hz_parties.party_number%TYPE;
1130
1131 SKIP_STAT_MAST EXCEPTION;
1132
1133 --Cursor to fetch the Interface Statistics Master Records for the Interface Identifier
1134 CURSOR cur_get_int_stat_master(cp_interface_id igs_or_inst_stat_int.interface_id%TYPE,
1135 cp_status igs_or_inst_stat_int.status%TYPE) IS
1136 SELECT *
1137 FROM igs_or_inst_stat_int
1138 WHERE interface_id =cp_interface_id
1139 AND status =cp_status ;
1140
1141 --Cursor to fetch the Interface Statistics Detail Records for this Statistics master record
1142 CURSOR cur_get_int_stat_det(p_int_stat_id igs_or_inst_sdtl_int.interface_inst_stat_id%TYPE,
1143 cp_status igs_or_inst_sdtl_int.status%TYPE) IS
1144 SELECT sdtl.rowid,sdtl.*
1145 FROM igs_or_inst_sdtl_int sdtl
1146 WHERE interface_inst_stat_id = p_int_stat_id
1147 AND status = cp_status;
1148
1149 --Cursor to fetch the OSS Statistics Master Records for this Statistics Master Identifier
1150 --fetched from the Interface Table
1151 CURSOR cur_get_oss_stat_master(p_stat_type_cd igs_or_inst_stat_int.stat_type_cd%TYPE,cp_party_id hz_parties.party_id%TYPE) IS
1152 SELECT *
1153 FROM igs_or_inst_stats
1154 WHERE stat_type_cd =p_stat_type_cd
1155 AND party_id =cp_party_id;
1156
1157 --Cursor to fetch the OSS Statistics Detail Records to check against the recently
1158 --inserted Detail record.
1159 CURSOR cur_get_oss_stat_det(p_int_stat_id igs_or_inst_stat_dtl.inst_stat_id%TYPE,
1160 p_year igs_or_inst_sdtl_int.year%TYPE) IS
1161 SELECT sdtl.rowid,sdtl.*
1162 FROM igs_or_inst_stat_dtl sdtl
1163 WHERE sdtl.inst_stat_id = p_int_stat_id
1164 AND TO_CHAR(sdtl.year,'YYYY') = TO_CHAR(p_year,'YYYY');
1165
1166 oss_stat_master_rec cur_get_oss_stat_master%ROWTYPE;
1167 oss_stat_det_rec cur_get_oss_stat_det%ROWTYPE;
1168
1169 --cursor to do field level validation before importing data into statistics master
1170 CURSOR c_val_stat(p_stat_type_cd igs_or_inst_stat_int.stat_type_cd%TYPE,
1171 cp_lookup_type igs_lookup_values.lookup_type%TYPE,
1172 cp_enabled_flag igs_lookup_values.enabled_flag%TYPE) IS
1173 SELECT 'X'
1174 FROM igs_lookup_values lkv
1175 WHERE lkv.lookup_code = p_stat_type_cd
1176 AND lkv.lookup_type= cp_lookup_type
1177 AND enabled_flag = cp_enabled_flag;
1178
1179 c_val_stat_rec c_val_stat%ROWTYPE;
1180
1181 --Procedure to update the Statistics Master Interface Table
1182 PROCEDURE update_int_stat(p_int_stat_rec igs_or_inst_stat_int%ROWTYPE)
1183 AS
1184
1185 BEGIN
1186
1187 UPDATE igs_or_inst_stat_int
1188 SET status = p_int_stat_rec.status,
1189 error_code =p_int_stat_rec.error_code
1190 WHERE interface_inst_stat_id =p_int_stat_rec.interface_inst_stat_id;
1191
1192 --put this information about the failed record in the log file too
1193 IF p_int_stat_rec.status='3' THEN
1194 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
1195 FND_MESSAGE.SET_NAME('IGS','IGS_OR_INST_IMP_FAIL');
1196 FND_MESSAGE.SET_TOKEN('INT_ID', p_int_stat_rec.interface_inst_stat_id);
1197 FND_MESSAGE.SET_TOKEN('ERROR_CODE', p_int_stat_rec.error_code);
1198 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
1199 'igs.plsql.igs_or_inst_imp_003.update_int_stat.' || p_int_stat_rec.error_code,
1200 FND_MESSAGE.GET,
1201 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
1202 END IF;
1203 END IF;
1204
1205 END update_int_stat;
1206
1207 --Procedure to update the Statistics Detail Interface Table
1208 PROCEDURE update_int_stat_det(p_int_stat_det_rec cur_get_int_stat_det%ROWTYPE)
1209 AS
1210
1211
1212 BEGIN
1213
1214 UPDATE igs_or_inst_sdtl_int
1215 SET status = p_int_stat_det_rec.status,
1216 error_code = p_int_stat_det_rec.error_code
1217 WHERE interface_inst_stat_dtl_id = p_int_stat_det_rec.interface_inst_stat_dtl_id;
1218
1219 --put this information about the failed record in the log file too
1220 IF p_int_stat_det_rec.status='3' THEN
1221 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
1222 FND_MESSAGE.SET_NAME('IGS','IGS_OR_INST_IMP_FAIL');
1223 FND_MESSAGE.SET_TOKEN('INT_ID', p_int_stat_det_rec.interface_inst_stat_dtl_id);
1224 FND_MESSAGE.SET_TOKEN('ERROR_CODE', p_int_stat_det_rec.error_code);
1225 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
1226 'igs.plsql.igs_or_inst_imp_003.update_int_stat_det.' || p_int_stat_det_rec.error_code,
1227 FND_MESSAGE.GET,
1228 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
1229 END IF;
1230 END IF;
1231
1232 END update_int_stat_det;
1233
1234 --Procedure to display the error in when others exception
1235 PROCEDURE display_error(p_int_stat_master_rec IN OUT NOCOPY igs_or_inst_stat_int%ROWTYPE,
1236 p_int_stat_det_rec IN OUT NOCOPY cur_get_int_stat_det%ROWTYPE
1237 )
1238 AS
1239
1240 BEGIN
1241 IF l_det_dml_operation='INSERT' THEN
1242 p_int_stat_det_rec.error_code := 'E030';
1243 ELSIF l_det_dml_operation='UPDATE' THEN
1244 p_int_stat_det_rec.error_code := 'E031';
1245 END IF;
1246 --Rollback changes
1247 ROLLBACK TO stat_mast;
1248 p_int_stat_det_rec.status :='3';
1249
1250 --Update the Interface record status as ERROR
1251 update_int_stat_det(p_int_stat_det_rec);
1252
1253 --Even if one Statistics detail record fails need to ROLLBACK and Update the statistics master record as ERROR
1254 p_int_stat_master_rec.status:='3';
1255
1256 --Update the Master Interface Record as ERROR
1257 update_int_stat(p_int_stat_master_rec);
1258
1259 --Process Next Master Record
1260 RAISE SKIP_STAT_MAST;
1261 END display_error;
1262
1263 BEGIN
1264
1265 IF p_party_id IS NULL THEN
1266 RAISE NO_DATA_FOUND;
1267 END IF;
1268
1269
1270 --Iterate through the Interface Master Stat records
1271 FOR int_stat_master_rec IN cur_get_int_stat_master(p_interface_id,'2') LOOP
1272 BEGIN
1273 l_mast_insert_success:=FALSE;
1274 l_mast_dml_operation :=NULL;
1275
1276 l_det_insert_success:=FALSE;
1277 l_det_update_success:=FALSE;
1278 l_det_dml_operation :=NULL;
1279
1280 --Create a Save Point for every Interface Record Fetched
1281 SAVEPOINT stat_mast;
1282 --Fetch the Statistics Master records in OSS for this Statistics Identifier
1283 OPEN cur_get_oss_stat_master(int_stat_master_rec.stat_type_cd,p_party_id);
1284 FETCH cur_get_oss_stat_master INTO oss_stat_master_rec;
1285 --If there are no OSS Stat Master records
1286 IF cur_get_oss_stat_master%NOTFOUND THEN
1287 l_mast_dml_operation:='INSERT';
1288 --Insert the Interface Records into OSS Master Stats
1289 l_inst_stat_id:=NULL;
1290 l_row_id :=NULL;
1291 OPEN c_val_stat(int_stat_master_rec.stat_type_cd,'OR_INST_STAT_TYPE','Y');
1292 FETCH c_val_stat INTO c_val_stat_rec;
1293 IF c_val_stat%FOUND THEN
1294 igs_or_inst_stats_pkg.insert_row(
1295 x_rowid =>l_row_id,
1296 x_inst_stat_id =>l_inst_stat_id,
1297 x_stat_type_cd =>int_stat_master_rec.stat_type_cd,
1298 x_party_id =>p_party_id,
1299 x_mode =>'R'
1300 );
1301 l_mast_insert_success:=TRUE;
1302 ELSE
1303 int_stat_master_rec.status:='3';
1304 int_stat_master_rec.error_code:='E027';
1305 update_int_stat(int_stat_master_rec);
1306 RAISE SKIP_STAT_MAST;
1307 END IF;
1308 CLOSE c_val_stat;
1309 --For this Master Record Inserted Need to get Detail records if any and Insert into the
1310 --OSS Table
1311
1312 --Fetch the Interface Detail Records
1313 FOR int_stat_det_rec IN cur_get_int_stat_det(int_stat_master_rec.interface_inst_stat_id,'2') LOOP
1314 BEGIN
1315 l_det_insert_success:=FALSE;
1316 l_det_dml_operation:=NULL;
1317 --Check if there is already a Duplicate record available in OSS Detail.
1318 --First Time it will not be but successive Inserts could result in a Duplicate
1319 OPEN cur_get_oss_stat_det(l_inst_stat_id ,int_stat_det_rec.year);
1320 FETCH cur_get_oss_stat_det INTO oss_stat_det_rec;
1321 IF cur_get_oss_stat_det%NOTFOUND THEN
1322 l_det_dml_operation:='INSERT';
1323 l_dtl_id:=NULL;
1324 l_det_row_id:=NULL;
1325
1326 --Insert the Detail Record
1327 igs_or_inst_stat_dtl_pkg.insert_row(
1328 x_rowid => l_det_row_id,
1329 x_inst_stat_dtl_id => l_dtl_id,
1330 x_inst_stat_id => l_inst_stat_id,
1331 x_year => int_stat_det_rec.year ,
1332 x_value => int_stat_det_rec.value,
1333 x_mode => 'R' );
1334
1335 l_det_insert_success:=TRUE;
1336 END IF;
1337 CLOSE cur_get_oss_stat_det;
1338
1339 --After Successful Insert ,Update the detail rec status as COMPLETE
1340 --Even if a Duplicate Detail record is found then Update the
1341 --Status as COMPLETE
1342 int_stat_det_rec.error_code:=NULL;
1343 int_stat_det_rec.status:='1';
1344 update_int_stat_det(int_stat_det_rec);
1345
1346 EXCEPTION --Handle Exception for Every Detail Record
1347 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
1348 CLOSE cur_get_oss_stat_det;
1349
1350 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
1351 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_LOCKED');
1352 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
1353 'igs.plsql.igs_or_inst_imp_003.process_institution_statistics.recordlock1',
1354 FND_MESSAGE.GET || '-' || SQLERRM,
1355 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
1356 END IF;
1357
1358 ROLLBACK TO stat_mast;
1359 RAISE SKIP_STAT_MAST;
1360 WHEN OTHERS THEN
1361 CLOSE cur_get_oss_stat_master;
1362 CLOSE cur_get_oss_stat_det;
1363 display_error(int_stat_master_rec,int_stat_det_rec);
1364 END;
1365 END LOOP; --Process Next Record in Detail Records Loop
1366 --Else if a Master Duplicate Record is found then
1367 ELSE
1368 --Updation of Statistics Master Record is not done.
1369 --Fetch the Detail Records for this Master Statistics
1370 FOR int_stat_det_rec IN cur_get_int_stat_det(int_stat_master_rec.interface_inst_stat_id,'2') LOOP
1371 BEGIN
1372 l_det_insert_success:=FALSE;
1373 l_det_dml_operation:=NULL;
1374 --Check if there is already a Duplicate record available in OSS Detail for the Interface Stat ID
1375 --and Year.
1376 OPEN cur_get_oss_stat_det(oss_stat_master_rec.inst_stat_id ,int_stat_det_rec.year);
1377 FETCH cur_get_oss_stat_det INTO oss_stat_det_rec;
1378 IF cur_get_oss_stat_det%NOTFOUND THEN
1379 l_dtl_id:=NULL;
1380 l_det_row_id:=NULL;
1381 l_det_dml_operation:='INSERT';
1382 --Insert the Detail Record
1383 igs_or_inst_stat_dtl_pkg.insert_row(
1384 x_rowid => l_det_row_id,
1385 x_inst_stat_dtl_id => l_dtl_id,
1386 x_inst_stat_id => oss_stat_master_rec.inst_stat_id,
1387 x_year => int_stat_det_rec.year ,
1388 x_value => int_stat_det_rec.value
1389 );
1390 l_det_insert_success:=TRUE;
1391 --Duplicate Detail Record is Found so update with the Interface Record Details
1392 ELSIF cur_get_oss_stat_det%FOUND THEN
1393 l_det_dml_operation:='UPDATE';
1394 --Update the Detail Record
1395 igs_or_inst_stat_dtl_pkg.update_row(
1396 x_rowid => oss_stat_det_rec.rowid,
1397 x_inst_stat_dtl_id => oss_stat_det_rec.inst_stat_dtl_id,
1398 x_inst_stat_id => oss_stat_det_rec.inst_stat_id,
1399 x_year => oss_stat_det_rec.year ,
1400 x_value => int_stat_det_rec.value
1401 );
1402
1403 l_det_update_success:=TRUE;
1404 END IF;
1405 CLOSE cur_get_oss_stat_det;
1406 --After Successful Insert ,Update the detail rec status as COMPLETE
1407 --Even if a Duplicate Detail record is found then Update the
1408 --Status as COMPLETE
1409 int_stat_det_rec.status:='1';
1410 int_stat_det_rec.error_code:=NULL;
1411 update_int_stat_det(int_stat_det_rec);
1412 --Handle Exception for every detail record processed
1413 EXCEPTION
1414 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
1415 CLOSE cur_get_oss_stat_det;
1416 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
1417 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_LOCKED');
1418 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
1419 'igs.plsql.igs_or_inst_imp_003.process_institution_statistics.recordlock2',
1420 FND_MESSAGE.GET || '-' || SQLERRM,
1421 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
1422 END IF;
1423
1424 --Rollback changes
1425 ROLLBACK TO stat_mast;
1426 RAISE SKIP_STAT_MAST;
1427 WHEN OTHERS THEN
1428 CLOSE cur_get_oss_stat_master;
1429 CLOSE cur_get_oss_stat_det;
1430 display_error(int_stat_master_rec,int_stat_det_rec);
1431 END;
1432 END LOOP; --End of Detail Records in Interface
1433 END IF; --For Duplicate Check against Master Record
1434 CLOSE cur_get_oss_stat_master;
1435
1436 --For every Master statistics record processed ,Display appropriate message and
1437 --Update the Status as COMPLETE
1438
1439 int_stat_master_rec.status:='1';
1440 int_stat_master_rec.error_code:=NULL;
1441 --Update the Interface Record
1442 update_int_stat(int_stat_master_rec);
1443
1444 --For every master record Handle the exception
1445 EXCEPTION
1446 WHEN SKIP_STAT_MAST THEN
1447 IF cur_get_oss_stat_master%ISOPEN THEN
1448 CLOSE cur_get_oss_stat_master;
1449 END IF;
1450 IF c_val_stat%ISOPEN THEN
1451 CLOSE c_val_stat;
1452 END IF;
1453
1454 WHEN OTHERS THEN
1455 IF cur_get_oss_stat_master%ISOPEN THEN
1456 CLOSE cur_get_oss_stat_master;
1457 END IF;
1458 IF c_val_stat%ISOPEN THEN
1459 CLOSE c_val_stat;
1460 END IF;
1461 --Status is ERROR
1462 int_stat_master_rec.status:='3';
1463 int_stat_master_rec.error_code:='E028';
1464 --Update the Interface Record
1465 update_int_stat(int_stat_master_rec);
1466 END;
1467 END LOOP; --For the Master Record
1468
1469 EXCEPTION
1470 WHEN NO_DATA_FOUND THEN
1471 --log message that the party_id passed is Invalid, Party ID passed must be present in Hz_partie
1472
1473 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
1474 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
1475 'igs.plsql.igs_or_inst_imp_003.process_institution_statistics.nodatafound',
1476 'Invalid Party Id: '||p_party_id ||' ,Party ID passed must be present in HZ_parties' || '-' || SQLERRM,
1477 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
1478 END IF;
1479
1480 WHEN OTHERS THEN
1481 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
1482 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1483 FND_MESSAGE.SET_TOKEN('NAME','igs_or_inst_imp_003.process_institution_statistics');
1484 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
1485 'igs.plsql.igs_or_inst_imp_003.process_institution_statistics.others',
1486 FND_MESSAGE.GET || '-' || SQLERRM,
1487 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
1488 END IF;
1489
1490 APP_EXCEPTION.RAISE_EXCEPTION;
1491 END process_institution_statistics;
1492
1493
1494 --Procedure to process the Institution Addresses
1495 PROCEDURE process_institution_address (
1496 p_interface_id IN igs_or_adr_int.interface_id%TYPE,
1497 p_addr_usage IN igs_or_adrusge_int.site_use_code%TYPE,
1498 p_party_id IN hz_parties.party_id%TYPE)
1499 /***************************************************************
1500 Created By : mesriniv
1501 Date Created By : 2001/07/12
1502 Purpose : This is the third part of
1503 Import Institutions Package
1504 Known Limitations,Enhancements or Remarks
1505
1506 Change History :
1507 ENH Bug No : 1872994
1508 ENH Desc : Modelling and Forcasting DLD- Institutions Build
1509 Who When What
1510 pkpatel 15-MAR-2002 Bug no.2238946 :Added the parameter p_status in the call to igs_pe_party_site_use_pkg
1511 asbala 12-nov-03 3227107: address changes - signature of igs_pe_person_addr_pkg.insert_row changed
1512 ***************************************************************/
1513 AS
1514 l_addr_usage igs_or_adrusge_int.site_use_code%TYPE;
1515 l_existing_usage igs_or_adrusge_int.site_use_code%TYPE;
1516 l_location_id hz_locations.location_id%TYPE;
1517 l_date DATE;
1518 l_addr_success BOOLEAN;
1519 l_addr_process BOOLEAN := FALSE;
1520
1521 l_usage_success BOOLEAN;
1522 l_addr_dml VARCHAR2(10);
1523 l_usage_dml VARCHAR2(10);
1524 l_count NUMBER;
1525 l_return_status VARCHAR2(10);
1526 l_msg_data VARCHAR2(1000);
1527 l_addr_row_id VARCHAR2(25);
1528 l_usage_row_id VARCHAR2(25);
1529 l_party_site_id hz_party_sites.party_site_id%TYPE;
1530 l_party_site_use_id igs_or_adrusge_int.interface_addrusage_id%TYPE;
1531 SKIP_ADDR EXCEPTION;
1532 l_party_site_ovn hz_party_sites.object_version_number%TYPE;
1533 l_location_ovn hz_locations.object_version_number%TYPE;
1534 l_addr_warning VARCHAR2(1) := 'N';
1535 --Cursor to fetch the Interface Address Master Records for the Interface Identifier
1536 CURSOR cur_get_int_addr(cp_interface_id igs_or_adr_int.interface_id%TYPE,
1537 cp_status igs_or_adr_int.status%TYPE) IS
1538 SELECT *
1539 FROM igs_or_adr_int
1540 WHERE interface_id =cp_interface_id
1541 AND status = cp_status ;
1542
1543 --Cursor to get the Count of Address Usages from the Interface for an Address ID
1544 CURSOR cur_get_usage_count(p_int_addr_id igs_or_adr_int.interface_addr_id%TYPE,
1545 cp_status igs_or_adrusge_int.status%TYPE) IS
1546 SELECT COUNT(interface_addrusage_id)
1547 FROM igs_or_adrusge_int
1548 WHERE interface_addr_id = p_int_addr_id
1549 AND status = cp_status;
1550
1551 --Cursor to fetch the Address Usage
1552 CURSOR cur_get_addr_usage(p_int_addr_id igs_or_adr_int.interface_addr_id%TYPE,
1553 cp_status igs_or_adrusge_int.status%TYPE) IS
1554 SELECT UPPER(site_use_code)
1555 FROM igs_or_adrusge_int
1556 WHERE interface_addr_id = p_int_addr_id
1557 AND status = cp_status;
1558
1559 --Cursor to check the Duplicate Address
1560 CURSOR cur_get_oss_addr(p_int_addr_rec igs_or_adr_int%ROWTYPE,cp_party_id hz_parties.party_id%TYPE) IS
1561 SELECT party_site_id
1562 FROM hz_party_sites hp,
1563 hz_locations addr
1564 WHERE hp.location_id = addr.location_id
1565 AND hp.party_id = cp_party_id
1566 AND NVL(addr.address1,' ') = NVL(p_int_addr_rec.addr_line_1,' ')
1567 AND NVL(addr.address2,' ') = NVL(p_int_addr_rec.addr_line_2,' ')
1568 AND NVL(addr.address3,' ') = NVL(p_int_addr_rec.addr_line_3,' ')
1569 AND NVL(addr.address4,' ') = NVL(p_int_addr_rec.addr_line_4,' ')
1570 AND NVL(addr.city,' ') = NVL(p_int_addr_rec.city,' ')
1571 AND NVL(addr.state,' ') = NVL(p_int_addr_rec.state,' ')
1572 AND NVL(addr.province,' ') = NVL(p_int_addr_rec.province,' ')
1573 AND NVL(addr.county,' ') = NVL(p_int_addr_rec.county,' ')
1574 AND NVL(addr.country,' ') = NVL(p_int_addr_rec.country,' ')
1575 AND NVL(addr.postal_code,' ') = NVL(p_int_addr_rec.postcode,' ');
1576
1577 --Cursor to fetch the existing address usage
1578 CURSOR cur_get_usage(cp_party_site_id hz_party_sites.party_site_id%TYPE,
1579 cp_addr_usage igs_or_adrusge_int.site_use_code%TYPE) IS
1580 SELECT site_use_type
1581 FROM hz_party_site_uses
1582 WHERE party_site_id = cp_party_site_id
1583 AND site_use_type = cp_addr_usage;
1584
1585 --cursor added to validate country field in address interface table
1586 CURSOR c_adr_val(p_country igs_or_adr_int.country%TYPE) IS
1587 SELECT 'X'
1588 FROM fnd_territories
1589 WHERE territory_code= p_country;
1590
1591 c_adr_val_rec c_adr_val%ROWTYPE;
1592
1593 --Procedure to update the Interface Address Status
1594 PROCEDURE update_address_int(p_interface_id igs_or_adr_int.interface_id%TYPE,
1595 p_addr_id igs_or_adr_int.interface_addr_id%TYPE,
1596 p_status igs_or_adr_int.status%TYPE,
1597 p_err_cd igs_or_adr_int.error_code%TYPE,
1598 p_err_txt igs_or_adr_int.error_text%TYPE,
1599 p_which_tab VARCHAR2)
1600 AS
1601
1602 BEGIN
1603 --Update the status of address record
1604 IF p_which_tab IN ('adr','both')THEN
1605 UPDATE igs_or_adr_int
1606 SET status = p_status ,
1607 error_code = p_err_cd ,
1608 error_text = p_err_txt
1609 WHERE interface_id = p_interface_id
1610 AND interface_addr_id= p_addr_id ;
1611
1612 --put this information about the failed record in the log file too
1613 IF p_status='3' THEN
1614 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
1615 FND_MESSAGE.SET_NAME('IGS','IGS_OR_INST_IMP_FAIL');
1616 FND_MESSAGE.SET_TOKEN('INT_ID', p_addr_id);
1617 FND_MESSAGE.SET_TOKEN('ERROR_CODE', p_err_cd);
1618 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
1619 'igs.plsql.igs_or_inst_imp_003.update_address_int.' || p_err_cd,
1620 FND_MESSAGE.GET,
1621 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
1622 END IF;
1623 END IF;
1624 END IF;
1625
1626 --Update the status of address usage record
1627 IF p_which_tab IN ('adr_usg','both')THEN
1628 UPDATE igs_or_adrusge_int
1629 SET status = p_status , error_code = p_err_cd , error_text = p_err_txt
1630 WHERE interface_addr_id= p_addr_id;
1631
1632 --put this information about the failed record in the log file too
1633 IF p_status='3' THEN
1634 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
1635 FND_MESSAGE.SET_NAME('IGS','IGS_OR_INST_IMP_FAIL');
1636 FND_MESSAGE.SET_TOKEN('INT_ID', p_addr_id);
1637 FND_MESSAGE.SET_TOKEN('ERROR_CODE', p_err_cd);
1638 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
1639 'igs.plsql.igs_or_inst_imp_003.update_address_int.' || p_err_cd,
1640 FND_MESSAGE.GET,
1641 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
1642 END IF;
1643 END IF;
1644
1645 END IF;
1646
1647 END update_address_int;
1648
1649 --Procedure to Insert the Address Usage for the address
1650 PROCEDURE insert_addr_usage(p_interface_id igs_or_adr_int.interface_id%TYPE,
1651 p_int_addr_id igs_or_adr_int.interface_addr_id%TYPE)
1652 AS
1653
1654 --cursor added to validate if the site use code in interface table is present in fnd_lookups
1655 CURSOR c_val_addr_usage(p_site_use_code igs_or_adrusge_int.site_use_code%TYPE,
1656 cp_lookup_typ fnd_lookup_values.lookup_type%TYPE,
1657 cp_enabled_flag fnd_lookup_values.enabled_flag%TYPE) IS
1658 SELECT 'X'
1659 FROM fnd_lookup_values
1660 WHERE lookup_type= cp_lookup_typ
1661 AND lookup_code = p_site_use_code
1662 AND enabled_flag = cp_enabled_flag
1663 AND view_application_id = 222
1664 AND security_group_id = 0
1665 AND language = userenv('LANG');
1666
1667 c_val_addr_usage_rec c_val_addr_usage%ROWTYPE;
1668 l_object_version_number NUMBER;
1669 BEGIN
1670
1671 l_return_status:=NULL;
1672 l_msg_data :=NULL;
1673 l_date :=NULL;
1674 l_party_site_use_id:=NULL;
1675
1676 OPEN c_val_addr_usage(l_addr_usage,'PARTY_SITE_USE_CODE','Y');
1677 FETCH c_val_addr_usage INTO c_val_addr_usage_rec;
1678
1679 IF c_val_addr_usage%FOUND THEN
1680 igs_pe_party_site_use_pkg.hz_party_site_uses_ak(
1681 p_action => 'INSERT',
1682 p_rowid => l_usage_row_id,
1683 p_party_site_use_id => l_party_site_use_id,
1684 p_party_site_id => l_party_site_id,
1685 p_site_use_type => l_addr_usage,
1686 p_status => 'A',
1687 p_return_status => l_return_status,
1688 p_msg_data => l_msg_data,
1689 p_last_update_date => l_date,
1690 p_site_use_last_update_date => l_date,
1691 p_profile_last_update_date => l_date,
1692 p_hz_party_site_use_ovn => l_object_version_number
1693 );
1694
1695 IF l_return_status IN ('E','U') THEN
1696 --Error while associating the Address Usage with the Address
1697
1698 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
1699 FND_MESSAGE.SET_NAME('IGS','IGS_OR_USG_INSERT');
1700 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
1701 'igs.plsql.igs_or_inst_imp_003.insert_addr_usage.fail',
1702 FND_MESSAGE.GET || '-' || l_msg_data,
1703 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
1704 END IF;
1705
1706 --Error in Address Record Status
1707 CLOSE c_val_addr_usage;
1708 update_address_int(p_interface_id,p_int_addr_id,'3', 'E057',l_msg_data,'both');
1709 RAISE SKIP_ADDR;
1710 END IF;
1711 ELSE
1712 update_address_int(p_interface_id,p_int_addr_id,'3','E033',NULL,'both');
1713 CLOSE c_val_addr_usage;
1714 RAISE SKIP_ADDR;
1715 END IF;
1716 CLOSE c_val_addr_usage;
1717 END insert_addr_usage;
1718
1719 BEGIN
1720
1721 IF p_party_id IS NULL THEN
1722 RAISE NO_DATA_FOUND;
1723 END IF;
1724
1725 --Iterate through the Interface Address records
1726 FOR int_addr_rec IN cur_get_int_addr(p_interface_id,'2') LOOP
1727
1728 BEGIN
1729 l_addr_success :=FALSE;
1730 l_usage_success:=FALSE;
1731 l_addr_dml:=NULL;
1732 l_usage_dml:=NULL;
1733 l_addr_warning := 'N';
1734
1735 int_addr_rec.country := UPPER(int_addr_rec.country);
1736 int_addr_rec.correspondence_flag := UPPER(int_addr_rec.correspondence_flag);
1737
1738 --Create a Save Point for every Interface Record Fetched
1739 SAVEPOINT address;
1740 l_count:= 0; --Since the count of address usages would be different for different addresses
1741
1742 --Count the No.of Address Usages for this Address
1743 OPEN cur_get_usage_count(int_addr_rec.interface_addr_id,'2');
1744 FETCH cur_get_usage_count INTO l_count;
1745 CLOSE cur_get_usage_count;
1746
1747 IF l_count = 1 THEN
1748 --Fetch the Address Usage
1749 OPEN cur_get_addr_usage(int_addr_rec.interface_addr_id,'2');
1750 FETCH cur_get_addr_usage INTO l_addr_usage;
1751 CLOSE cur_get_addr_usage;
1752
1753 ELSIF l_count =0 THEN
1754 --Check if the Parameter is Not NULL
1755 IF p_addr_usage IS NULL THEN
1756 --Mark this record as ERROR
1757 --p_address_usage not specified
1758 --Update Record as ERROR
1759 update_address_int(p_interface_id,int_addr_rec.interface_addr_id,'3','E034',NULL,'both');
1760 RAISE SKIP_ADDR;
1761 ELSIF p_addr_usage IS NOT NULL THEN
1762 l_addr_usage:=p_addr_usage;
1763 END IF;
1764
1765 ELSIF l_count > 1 THEN
1766 --Only one address usage should be specified for the an address
1767 --Error in Address Record Status
1768 update_address_int(p_interface_id,int_addr_rec.interface_addr_id,'3','E035',NULL,'both');
1769 RAISE SKIP_ADDR;
1770 END IF; --End of check for Count
1771
1772 l_party_site_id := NULL; --Since the Party Site Id would be different for different addresses
1773
1774 --Check for Duplicate OSS Address
1775 OPEN cur_get_oss_addr(int_addr_rec,p_party_id);
1776 FETCH cur_get_oss_addr INTO l_party_site_id;
1777 CLOSE cur_get_oss_addr;
1778
1779 --There is no such Address
1780 IF l_party_site_id IS NULL THEN
1781 --Address has to be created in OSS
1782 l_addr_dml := 'INSERT';
1783 l_addr_row_id := NULL;
1784 l_location_id := NULL;
1785 l_return_status := NULL;
1786 l_msg_data := NULL;
1787
1788 OPEN c_adr_val(int_addr_rec.country);
1789 FETCH c_adr_val into c_adr_val_rec;
1790
1791 IF c_adr_val%FOUND THEN
1792 igs_pe_person_addr_pkg.insert_row
1793 (
1794 p_action => 'R',
1795 p_rowid => l_addr_row_id,
1796 p_location_id => l_location_id,
1797 p_start_dt => int_addr_rec.start_date,
1798 p_end_dt => int_addr_rec.end_date,
1799 p_country => int_addr_rec.country,
1800 p_address_style => NULL,
1801 p_addr_line_1 => int_addr_rec.addr_line_1 ,
1802 p_addr_line_2 => int_addr_rec.addr_line_2,
1803 p_addr_line_3 => int_addr_rec.addr_line_3 ,
1804 p_addr_line_4 => int_addr_rec.addr_line_4 ,
1805 p_date_last_verified => int_addr_rec.date_last_verified,
1806 p_correspondence => int_addr_rec.correspondence_flag ,
1807 p_city => int_addr_rec.city,
1808 p_state => int_addr_rec.state,
1809 p_province => int_addr_rec.province,
1810 p_county => int_addr_rec.county ,
1811 p_postal_code => int_addr_rec.postcode,
1812 p_address_lines_phonetic => NULL,
1813 p_delivery_point_code => int_addr_rec.delivery_point_code,
1814 p_other_details_1 => int_addr_rec.other_details_1,
1815 p_other_details_2 => int_addr_rec.other_details_2,
1816 p_other_details_3 => int_addr_rec.other_details_3,
1817 l_return_status => l_return_status,
1818 l_msg_data => l_msg_data,
1819 p_party_id => p_party_id,
1820 p_party_site_id => l_party_site_id,
1821 p_party_type => 'ORGANIZATION',
1822 p_last_update_date => l_date,
1823 p_party_site_ovn => l_party_site_ovn,
1824 p_location_ovn => l_location_ovn,
1825 p_status => 'A'
1826 );
1827
1828 IF l_return_status IN ('E','U') THEN
1829 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
1830 FND_MESSAGE.SET_NAME('IGS','IGS_OR_ADDR_NO_INSERT');
1831 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
1832 'igs.plsql.igs_or_inst_imp_003.process_institution_address.addrins',
1833 FND_MESSAGE.GET || '-' || l_msg_data,
1834 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
1835 END IF;
1836
1837 ROLLBACK TO address;
1838 --Error in Address Record Status
1839 update_address_int(p_interface_id,int_addr_rec.interface_addr_id,'3','E036',l_msg_data,'adr');
1840 RAISE SKIP_ADDR;
1841 ELSE
1842 IF l_return_status = 'W' THEN
1843 l_addr_warning := 'Y';
1844 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
1845 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
1846 'igs.plsql.igs_or_inst_imp_003.process_institution_address.addrins',
1847 'Warning: '|| '-' ||l_msg_data,
1848 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
1849 END IF;
1850 --Error in Address Record Status
1851 update_address_int(p_interface_id,int_addr_rec.interface_addr_id,'4','E022',l_msg_data,'adr');
1852 END IF;
1853
1854 --Set Insert address as TRUE if inserted
1855 l_addr_success:=TRUE;
1856 --To indicate that Usage is Inserted
1857 l_usage_dml:='INSERT';
1858 --Associate the Address Usage with the Inserted Address
1859 insert_addr_usage(int_addr_rec.interface_id,int_addr_rec.interface_addr_id);
1860 --Set the Insert as SUCCESS
1861 l_usage_success:=TRUE;
1862 --Set Address of person is processed flag
1863 l_addr_process := TRUE;
1864
1865 END IF;
1866 ELSE
1867 update_address_int(p_interface_id,int_addr_rec.interface_addr_id,'3','E032',NULL,'adr');
1868 RAISE SKIP_ADDR;
1869
1870 END IF;
1871 CLOSE c_adr_val;
1872
1873 --There is an Address existing in HZ Locations for this Party ID
1874 ELSIF l_party_site_id IS NOT NULL THEN
1875 --Need to Check if the Address Usage is already associated for this
1876 --address.Otherwise we need to create an association
1877
1878 --Fetch the Address Usage for this existing Address
1879 l_existing_usage:=NULL;
1880 OPEN cur_get_usage( l_party_site_id,l_addr_usage);
1881 FETCH cur_get_usage INTO l_existing_usage;
1882 CLOSE cur_get_usage;
1883
1884 --There is no Address Usage for this party_site_id
1885 IF l_existing_usage IS NULL THEN
1886 --To indicate that Usage is Inserted
1887 l_usage_dml:='INSERT';
1888
1889 --Associate the incoming address usage for the party_site_id
1890 insert_addr_usage(int_addr_rec.interface_id,int_addr_rec.interface_addr_id);
1891 l_usage_success:=TRUE;
1892 END IF;
1893 END IF;
1894 --After all the successful processing is over.Update the Interface addr and address usage
1895 --status to 1
1896 IF l_addr_warning = 'N' THEN
1897 update_address_int(p_interface_id,int_addr_rec.interface_addr_id,'1',NULL,NULL,'both');
1898 END IF;
1899
1900 EXCEPTION
1901 WHEN SKIP_ADDR THEN
1902 IF c_adr_val%ISOPEN THEN
1903 CLOSE c_adr_val;
1904 END IF;
1905 WHEN OTHERS THEN
1906 IF l_addr_dml='INSERT' AND NOT l_addr_success THEN
1907 --Error has occurred while inserting address
1908 update_address_int(p_interface_id,int_addr_rec.interface_addr_id,'3','E036',NULL,'adr');
1909 END IF;
1910
1911 IF l_usage_dml='INSERT' AND NOT l_usage_success THEN
1912 --Error has occurred while inserting address usage
1913 update_address_int(p_interface_id,int_addr_rec.interface_addr_id,'3','E038',NULL,'adr_usg');
1914 END IF;
1915 ROLLBACK TO address;
1916 END;
1917 END LOOP;
1918
1919 IF (l_addr_process) THEN
1920 --populate IGS_PE_WF_GEN.TI_ADDR_CHG_PERSONS table with party id to generate notification at the end of process
1921 IGS_PE_WF_GEN.TI_ADDR_CHG_PERSONS(NVL(IGS_PE_WF_GEN.TI_ADDR_CHG_PERSONS.LAST,0)+1) := p_party_id;
1922 END IF;
1923
1924 EXCEPTION
1925 WHEN NO_DATA_FOUND THEN
1926 --log message that the party_id passed is Invalid, Party ID passed must be present in Hz_partie
1927 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
1928 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
1929 'igs.plsql.igs_or_inst_imp_003.process_institution_address.nodatafound',
1930 'Invalid Party Id: '||p_party_id ||' ,Party ID passed must be present in HZ_parties - ' || SQLERRM,
1931 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
1932 END IF;
1933 WHEN OTHERS THEN
1934 IF (igs_or_inst_imp_001.gb_write_exception_log3) THEN
1935 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
1936 'igs.plsql.igs_or_inst_imp_003.process_institution_address.others',
1937 SQLERRM,
1938 NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
1939 END IF;
1940 APP_EXCEPTION.RAISE_EXCEPTION;
1941 END process_institution_address;
1942
1943 END igs_or_inst_imp_003_pkg;
[Home] [Help]