[Home] [Help]
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
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
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;
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
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,
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
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
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;
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;
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,
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'
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
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
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;
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;
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
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
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
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;
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
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
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
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