[Home] [Help]
784:
785: x_contact_point_id := p_contact_point_rec.contact_point_id;
786:
787: ---Bug No: 3131865
788: -- De-normalize primary contact point to HZ_PARTIES.
789: -- url is mandatory if contact_point_type = 'WEB'.
790: -- email_address is mandatory if contact_point_type = 'EMAIL'.
791:
792: -- Debug info.
799: p_msg_level=>fnd_log.level_statement);
800: END IF;
801:
802: -- Bug 2197181: commented out NOCOPY the data source checking. We will denormalize
803: -- to hz_parties as long as it's a primary contact point regardless of data
804: -- source.
805: IF p_contact_point_rec.status IS NULL OR
806: p_contact_point_rec.status = fnd_api.g_miss_char OR
807: p_contact_point_rec.status = 'A'
806: p_contact_point_rec.status = fnd_api.g_miss_char OR
807: p_contact_point_rec.status = 'A'
808: THEN
809: IF p_contact_point_rec.primary_flag = 'Y' AND
810: p_contact_point_rec.owner_table_name = 'HZ_PARTIES' AND
811: (p_contact_point_rec.contact_point_type IN ('WEB','EMAIL','PHONE'))
812: /* AND
813: (p_contact_point_rec.content_source_type IS NULL OR
814: p_contact_point_rec.content_source_type = fnd_api.g_miss_char OR
1468:
1469: CLOSE c_setpf;
1470: END IF;
1471:
1472: -- De-normalize primary contact point to HZ_PARTIES.
1473: -- url is mandatory if contact_point_type = 'WEB'.
1474: -- email_address is mandatory if contact_point_type = 'EMAIL'.
1475:
1476: -- Bug 2197181: commented out NOCOPY the data source checking. We will
1473: -- url is mandatory if contact_point_type = 'WEB'.
1474: -- email_address is mandatory if contact_point_type = 'EMAIL'.
1475:
1476: -- Bug 2197181: commented out NOCOPY the data source checking. We will
1477: -- denormalize to hz_parties as long as it's a primary contact
1478: -- point regardless of data source.
1479:
1480: IF (p_contact_point_rec.primary_flag = 'Y' OR
1481: l_primary_flag = 'Y') AND
1478: -- point regardless of data source.
1479:
1480: IF (p_contact_point_rec.primary_flag = 'Y' OR
1481: l_primary_flag = 'Y') AND
1482: l_owner_table_name = 'HZ_PARTIES' AND
1483: (l_contact_point_type IN ('WEB','EMAIL','PHONE'))
1484: /* AND
1485: l_content_source_type = hz_party_v2pub.g_miss_content_source_type
1486: */
1570: l_phone_country_code,l_phone_number,l_phone_extension;
1571:
1572: IF c_chkdenorm%NOTFOUND THEN
1573: -- no active contact point of this type left.
1574: -- clear denormalized field in hz_parties.
1575:
1576: IF l_owner_table_name = 'HZ_PARTIES' AND
1577: (l_contact_point_type IN('WEB','EMAIL','PHONE'))
1578: /*AND
1572: IF c_chkdenorm%NOTFOUND THEN
1573: -- no active contact point of this type left.
1574: -- clear denormalized field in hz_parties.
1575:
1576: IF l_owner_table_name = 'HZ_PARTIES' AND
1577: (l_contact_point_type IN('WEB','EMAIL','PHONE'))
1578: /*AND
1579: l_content_source_type = hz_party_v2pub.g_miss_content_source_type */
1580: THEN
1607: UPDATE hz_contact_points
1608: SET primary_flag = 'Y'
1609: WHERE rowid = l_contact_point_rowid;
1610:
1611: -- De-normalize primary contact point to HZ_PARTIES.
1612:
1613: IF l_owner_table_name = 'HZ_PARTIES' AND
1614: (l_contact_point_type IN ('WEB','EMAIL','PHONE'))
1615: /*
1609: WHERE rowid = l_contact_point_rowid;
1610:
1611: -- De-normalize primary contact point to HZ_PARTIES.
1612:
1613: IF l_owner_table_name = 'HZ_PARTIES' AND
1614: (l_contact_point_type IN ('WEB','EMAIL','PHONE'))
1615: /*
1616: l_content_source_type = hz_party_v2pub.g_miss_content_source_type */
1617: THEN
1898: --
1899: -- PRIVATE PROCEDURE do_denormalize_contact_point
1900: --
1901: -- DESCRIPTION
1902: -- Private procedure to denormalize some type of contact point to hz_parties.
1903: --
1904: -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1905: --
1906: -- ARGUMENTS
1965: END IF;
1966:
1967:
1968: IF p_contact_point_type = 'WEB' THEN
1969: UPDATE hz_parties
1970: SET url = p_url,
1971: last_update_date = hz_utility_v2pub.last_update_date,
1972: last_updated_by = hz_utility_v2pub.last_updated_by,
1973: last_update_login = hz_utility_v2pub.last_update_login,
1976: program_id = hz_utility_v2pub.program_id,
1977: program_update_date = sysdate
1978: WHERE party_id = p_party_id;
1979: ELSIF p_contact_point_type = 'EMAIL' THEN
1980: UPDATE hz_parties
1981: /* Bug No : 4355133*/
1982: SET email_address = SUBSTRB(p_email_address,1,320),
1983: last_update_date = hz_utility_v2pub.last_update_date,
1984: last_updated_by = hz_utility_v2pub.last_updated_by,
1988: program_id = hz_utility_v2pub.program_id,
1989: program_update_date = sysdate
1990: WHERE party_id = p_party_id;
1991: ELSIF p_contact_point_type = 'PHONE' THEN
1992: UPDATE hz_parties
1993: SET primary_phone_contact_pt_id = p_phone_contact_pt_id,
1994: primary_phone_purpose = p_phone_purpose,
1995: primary_phone_line_type = p_phone_line_type,
1996: primary_phone_country_code = p_phone_country_code,
2692: -- Bug 3818648: check dss profile before call test_instance
2693: -- Bug 3867562: check dss only in party context
2694:
2695: IF NVL(fnd_profile.value('HZ_DSS_ENABLED'), 'N') = 'Y' AND
2696: l_contact_point_rec.owner_table_name IN ('HZ_PARTIES', 'HZ_PARTY_SITES')
2697: THEN
2698: l_test_security :=
2699: hz_dss_util_pub.test_instance(
2700: p_operation_code => 'INSERT',
2713: --
2714: -- Bug 3835601: replaced the dss message with a more user friendly message
2715: --
2716: FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_INSERT_PRIVILEGE');
2717: IF l_contact_point_rec.owner_table_name = 'HZ_PARTIES' THEN
2718: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',
2719: hz_dss_util_pub.get_display_name(null, 'PARTY_CONTACT_POINTS'));
2720: ELSIF l_contact_point_rec.owner_table_name = 'HZ_PARTY_SITES' THEN
2721: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',
2926: -- Bug 3818648: check dss profile before call test_instance
2927: -- Bug 3867562: check dss only in party context
2928:
2929: IF NVL(fnd_profile.value('HZ_DSS_ENABLED'), 'N') = 'Y' AND
2930: l_old_contact_point_rec.owner_table_name IN ('HZ_PARTIES', 'HZ_PARTY_SITES')
2931: THEN
2932: l_test_security :=
2933: hz_dss_util_pub.test_instance(
2934: p_operation_code => 'UPDATE',
2947: --
2948: -- Bug 3835601: replaced the dss message with a more user friendly message
2949: --
2950: FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_UPDATE_PRIVILEGE');
2951: IF l_old_contact_point_rec.owner_table_name = 'HZ_PARTIES' THEN
2952: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',
2953: hz_dss_util_pub.get_display_name(null, 'PARTY_CONTACT_POINTS'));
2954: ELSIF l_old_contact_point_rec.owner_table_name = 'HZ_PARTY_SITES' THEN
2955: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',
7043:
7044: ----(Party level relationship's contact point )
7045: CURSOR c_pl_rel_cp(p_party_id NUMBER) IS
7046: SELECT distinct ac.CUST_ACCT_SITE_ID
7047: FROM HZ_PARTIES p, HZ_CUST_ACCOUNT_ROLES ar,
7048: HZ_RELATIONSHIPS rel,HZ_CUST_ACCT_SITES_ALL ac
7049: WHERE rel.party_id=p_party_id
7050: AND ar.ROLE_TYPE = 'CONTACT'
7051: AND rel.party_id=ar.party_id
7055:
7056: -----(Site Level relationship's contact point )
7057: CURSOR c_sl_rel_cp(p_party_id NUMBER) IS
7058: SELECT distinct ac.CUST_ACCT_SITE_ID
7059: FROM HZ_PARTIES p, HZ_CUST_ACCOUNT_ROLES ar,
7060: HZ_RELATIONSHIPS rel,HZ_CUST_ACCT_SITES_ALL ac
7061: WHERE rel.party_id=p_party_id
7062: AND ar.ROLE_TYPE = 'CONTACT'
7063: AND ar.party_id = rel.party_id
7066: AND ar.cust_acct_site_id = ac.cust_acct_site_id;
7067:
7068: CURSOR c_party_type(p_party_id NUMBER) IS
7069: SELECT party_type
7070: FROM HZ_PARTIES
7071: WHERE party_id=p_party_id;
7072:
7073: l_owner_table_name HZ_CONTACT_POINTS.OWNER_TABLE_NAME%TYPE;
7074: l_contact_point_type HZ_CONTACT_POINTS.CONTACT_POINT_TYPE%TYPE;
7073: l_owner_table_name HZ_CONTACT_POINTS.OWNER_TABLE_NAME%TYPE;
7074: l_contact_point_type HZ_CONTACT_POINTS.CONTACT_POINT_TYPE%TYPE;
7075: TYPE siteidtab IS TABLE OF HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID%TYPE;
7076: l_siteidtab siteidtab;
7077: l_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
7078: BEGIN
7079: savepoint update_contact_point_search;
7080:
7081: l_owner_table_name := p_cp_rec.owner_table_name;
7081: l_owner_table_name := p_cp_rec.owner_table_name;
7082: l_contact_point_type := p_cp_rec.contact_point_type;
7083:
7084: IF (l_owner_table_name='HZ_PARTY_SITES' AND l_contact_point_type NOT IN ('EDI', 'EMAIL', 'WEB'))
7085: OR (l_owner_table_name='HZ_PARTIES' AND l_contact_point_type NOT IN ('EDI','WEB'))
7086: THEN
7087: IF( isModified( p_old_phone_rec.phone_number ,p_new_phone_rec.phone_number)
7088: OR isModified(p_old_phone_rec.phone_area_code ,p_new_phone_rec.phone_area_code)
7089: OR isModified(p_old_phone_rec.phone_country_code ,p_new_phone_rec.phone_country_code)
7097: IF l_siteidtab.COUNT >0 THEN
7098: FORALL i IN l_siteidtab.FIRST..l_siteidtab.LAST
7099: update HZ_CUST_ACCT_SITES_ALL set address_text=NULL where cust_acct_site_id=l_siteidtab(i);
7100: END IF;
7101: ELSE ---l_owner_table_name ='HZ_PARTIES'
7102: OPEN c_party_type(p_cp_rec.owner_table_id);
7103: FETCH c_party_type INTO l_party_type;
7104: CLOSE c_party_type;
7105: IF l_party_type='PARTY_RELATIONSHIP' THEN