APPS.IGS_AD_IMP_008 dependencies on HZ_CONTACT_POINTS

Line 1614: --2. The duplicate check in the table HZ_CONTACT_POINTS.

1610: FROM igs_ad_rel_con_int_all
1611: WHERE status = '2'
1612: AND interface_relations_id = cp_interface_relations_id;
1613:
1614: --2. The duplicate check in the table HZ_CONTACT_POINTS.
1615: CURSOR rel_cont_del_dup_rec_c
1616: (
1617: p_contact_point_type igs_ad_rel_con_int.contact_point_type%TYPE,
1618: p_email_format igs_ad_rel_con_int.email_format%TYPE,

Line 1633: FROM hz_contact_points

1629: phone_line_type ,
1630: phone_area_code ,
1631: phone_number ,
1632: phone_extension
1633: FROM hz_contact_points
1634: WHERE owner_table_id = p_rel_person_id
1635: AND contact_point_type = p_contact_point_type
1636: AND owner_table_name = 'HZ_PARTIES'
1637: AND (( nvl(email_format,'NulL') = nvl(p_email_format,'NulL') AND

Line 1649: --HZ_CONTACT_POINTS excepting the Primary Key Columns.

1645:
1646: rel_cont_del_dup_rec rel_cont_del_dup_rec_c%ROWTYPE;
1647:
1648: --Check for discrepancy in data for all the columns in the table
1649: --HZ_CONTACT_POINTS excepting the Primary Key Columns.
1650:
1651:
1652: CURSOR get_obj_version(c_contact_point_id NUMBER) IS
1653: SELECT object_version_number

Line 1654: FROM hz_contact_points

1650:
1651:
1652: CURSOR get_obj_version(c_contact_point_id NUMBER) IS
1653: SELECT object_version_number
1654: FROM hz_contact_points
1655: WHERE contact_point_id = c_contact_point_id;
1656:
1657: l_obj_ver hz_contact_points.object_version_number%TYPE;
1658:

Line 1657: l_obj_ver hz_contact_points.object_version_number%TYPE;

1653: SELECT object_version_number
1654: FROM hz_contact_points
1655: WHERE contact_point_id = c_contact_point_id;
1656:
1657: l_obj_ver hz_contact_points.object_version_number%TYPE;
1658:
1659: l_return_status IGS_AD_RELATIONS_INT.STATUS%TYPE;
1660: l_msg_count NUMBER;
1661: l_msg_data VARCHAR2(2000);

Line 1717: --5. if the validation is successful then, insert this record into the table hz_contact_points.

1713: l_debug_str, NULL,
1714: NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1715: END IF;
1716:
1717: --5. if the validation is successful then, insert this record into the table hz_contact_points.
1718: --items in the record p_contact_point_rec
1719: contact_point_rec.contact_point_type := p_rel_con_rec.contact_point_type;
1720: -- contact_point_rec.status := p_rel_con_rec.status; ssomani, these two statuses are different 15 March 2001
1721: contact_point_rec.owner_table_name := 'HZ_PARTIES';

Line 1964: FROM hz_contact_points pe

1960: match_ind = '19'
1961: WHERE mi.interface_relations_id = p_interface_relations_id
1962: AND mi.status = '2'
1963: AND EXISTS ( SELECT '1'
1964: FROM hz_contact_points pe
1965: WHERE pe.owner_table_id = p_rel_person_id
1966: AND pe.contact_point_type = UPPER(mi.contact_point_type)
1967: AND pe.owner_table_name = 'HZ_PARTIES'
1968: AND (pe.contact_point_type = 'EMAIL' AND

Line 2010: FROM hz_contact_points pe

2006: WHERE mi.interface_relations_id = p_interface_relations_id
2007: AND mi.match_ind IS NULL
2008: AND mi.status = '2'
2009: AND EXISTS ( SELECT 1
2010: FROM hz_contact_points pe
2011: WHERE pe.owner_table_id = p_rel_person_id
2012: AND NVL(pe.primary_flag,'N') = NVL(UPPER(mi.primary_flag),'N')
2013: AND NVL(UPPER(pe.phone_extension),'NulL') = NVL(UPPER(mi.phone_extension),'NulL')
2014: AND pe.contact_point_type = UPPER(mi.contact_point_type)

Line 2030: FROM hz_contact_points pe

2026: UPDATE igs_ad_rel_con_int_all mi
2027: SET status = '3',
2028: match_ind = '20',
2029: DUP_CONTACT_POINT_ID = (SELECT pe.contact_point_id
2030: FROM hz_contact_points pe
2031: WHERE pe.owner_table_id = p_rel_person_id
2032: AND pe.contact_point_type = UPPER(mi.contact_point_type)
2033: AND pe.owner_table_name = 'HZ_PARTIES'
2034: AND ((pe.contact_point_type = 'EMAIL' AND

Line 2050: FROM hz_contact_points pe

2046: WHERE mi.interface_relations_id = p_interface_relations_id
2047: AND mi.match_ind IS NULL
2048: AND mi.status = '2'
2049: AND EXISTS (SELECT '1'
2050: FROM hz_contact_points pe
2051: WHERE pe.owner_table_id = p_rel_person_id
2052: AND pe.contact_point_type = UPPER(mi.contact_point_type)
2053: AND pe.owner_table_name = 'HZ_PARTIES'
2054: AND (pe.contact_point_type = 'EMAIL' AND

Line 2091: --3. If validations are successful then update the HZ_CONTACT_POINTS table.

2087: FETCH rel_cont_del_dup_rec_c INTO rel_cont_del_dup_rec;
2088: CLOSE rel_cont_del_dup_rec_c;
2089: IF rel_cont_del_dup_rec.contact_point_id IS NOT NULL THEN
2090: IF l_rule = 'I' THEN
2091: --3. If validations are successful then update the HZ_CONTACT_POINTS table.
2092: --items in the record p_contact_point_rec
2093: contact_point_rec.contact_point_type := relcon_rec.contact_point_type;
2094: contact_point_rec.contact_point_id := rel_cont_del_dup_rec.contact_point_id;
2095: -- contact_point_rec.status := relcon_rec.status; --ssomani, unrelated statuses 15 March 2001

Line 2183: --3. If validations are successful then update the HZ_CONTACT_POINTS table.

2179: END IF;
2180:
2181: ELSIF l_rule = 'R' THEN
2182: IF relcon_rec.match_ind = '21' THEN
2183: --3. If validations are successful then update the HZ_CONTACT_POINTS table.
2184: --items in the record p_contact_point_rec
2185: contact_point_rec.contact_point_type := relcon_rec.contact_point_type;
2186: contact_point_rec.contact_point_id := rel_cont_del_dup_rec.contact_point_id;
2187: -- contact_point_rec.status := relcon_rec.status; --ssomani, unrelated statuses 15 March 2001

Line 2207: -- SELECT LAST_UPDATE_DATEFROM HZ_CONTACT_POINTSWHERE OWNER_TABLE_ID = contact_point_id

2203: phone_rec.phone_number := NVL((NVL(relcon_rec.phone_number,rel_cont_del_dup_rec.phone_number)),FND_API.G_MISS_CHAR);
2204: phone_rec.phone_extension := NVL((NVL(relcon_rec.phone_extension,rel_cont_del_dup_rec.phone_extension)),FND_API.G_MISS_CHAR);
2205: END IF;
2206:
2207: -- SELECT LAST_UPDATE_DATEFROM HZ_CONTACT_POINTSWHERE OWNER_TABLE_ID = contact_point_id
2208: -- returned from duplicate check.
2209:
2210: OPEN get_obj_version(rel_cont_del_dup_rec.contact_point_id);
2211: FETCH get_obj_version INTO l_obj_ver;