[Home] [Help]
794:
795: x_contact_point_id := p_contact_point_rec.contact_point_id;
796:
797: ---Bug No: 3131865
798: -- De-normalize primary contact point to HZ_PARTIES.
799: -- url is mandatory if contact_point_type = 'WEB'.
800: -- email_address is mandatory if contact_point_type = 'EMAIL'.
801:
802: -- Debug info.
809: p_msg_level=>fnd_log.level_statement);
810: END IF;
811:
812: -- Bug 2197181: commented out NOCOPY the data source checking. We will denormalize
813: -- to hz_parties as long as it's a primary contact point regardless of data
814: -- source.
815: IF p_contact_point_rec.status IS NULL OR
816: p_contact_point_rec.status = fnd_api.g_miss_char OR
817: p_contact_point_rec.status = 'A'
816: p_contact_point_rec.status = fnd_api.g_miss_char OR
817: p_contact_point_rec.status = 'A'
818: THEN
819: IF p_contact_point_rec.primary_flag = 'Y' AND
820: p_contact_point_rec.owner_table_name = 'HZ_PARTIES' AND
821: (p_contact_point_rec.contact_point_type IN ('WEB','EMAIL','PHONE'))
822: /* AND
823: (p_contact_point_rec.content_source_type IS NULL OR
824: p_contact_point_rec.content_source_type = fnd_api.g_miss_char OR
1481:
1482: CLOSE c_setpf;
1483: END IF;
1484:
1485: -- De-normalize primary contact point to HZ_PARTIES.
1486: -- url is mandatory if contact_point_type = 'WEB'.
1487: -- email_address is mandatory if contact_point_type = 'EMAIL'.
1488:
1489: -- Bug 2197181: commented out NOCOPY the data source checking. We will
1486: -- url is mandatory if contact_point_type = 'WEB'.
1487: -- email_address is mandatory if contact_point_type = 'EMAIL'.
1488:
1489: -- Bug 2197181: commented out NOCOPY the data source checking. We will
1490: -- denormalize to hz_parties as long as it's a primary contact
1491: -- point regardless of data source.
1492:
1493: IF (p_contact_point_rec.primary_flag = 'Y' OR
1494: l_primary_flag = 'Y') AND
1491: -- point regardless of data source.
1492:
1493: IF (p_contact_point_rec.primary_flag = 'Y' OR
1494: l_primary_flag = 'Y') AND
1495: l_owner_table_name = 'HZ_PARTIES' AND
1496: (l_contact_point_type IN ('WEB','EMAIL','PHONE'))
1497: /* AND
1498: l_content_source_type = hz_party_v2pub.g_miss_content_source_type
1499: */
1583: l_phone_country_code,l_phone_number,l_phone_extension;
1584:
1585: IF c_chkdenorm%NOTFOUND THEN
1586: -- no active contact point of this type left.
1587: -- clear denormalized field in hz_parties.
1588:
1589: IF l_owner_table_name = 'HZ_PARTIES' AND
1590: (l_contact_point_type IN('WEB','EMAIL','PHONE'))
1591: /*AND
1585: IF c_chkdenorm%NOTFOUND THEN
1586: -- no active contact point of this type left.
1587: -- clear denormalized field in hz_parties.
1588:
1589: IF l_owner_table_name = 'HZ_PARTIES' AND
1590: (l_contact_point_type IN('WEB','EMAIL','PHONE'))
1591: /*AND
1592: l_content_source_type = hz_party_v2pub.g_miss_content_source_type */
1593: THEN
1620: UPDATE hz_contact_points
1621: SET primary_flag = 'Y'
1622: WHERE rowid = l_contact_point_rowid;
1623:
1624: -- De-normalize primary contact point to HZ_PARTIES.
1625:
1626: IF l_owner_table_name = 'HZ_PARTIES' AND
1627: (l_contact_point_type IN ('WEB','EMAIL','PHONE'))
1628: /*
1622: WHERE rowid = l_contact_point_rowid;
1623:
1624: -- De-normalize primary contact point to HZ_PARTIES.
1625:
1626: IF l_owner_table_name = 'HZ_PARTIES' AND
1627: (l_contact_point_type IN ('WEB','EMAIL','PHONE'))
1628: /*
1629: l_content_source_type = hz_party_v2pub.g_miss_content_source_type */
1630: THEN
1985: --
1986: -- PRIVATE PROCEDURE do_denormalize_contact_point
1987: --
1988: -- DESCRIPTION
1989: -- Private procedure to denormalize some type of contact point to hz_parties.
1990: --
1991: -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1992: --
1993: -- ARGUMENTS
2052: END IF;
2053:
2054:
2055: IF p_contact_point_type = 'WEB' THEN
2056: UPDATE hz_parties
2057: SET url = p_url,
2058: last_update_date = hz_utility_v2pub.last_update_date,
2059: last_updated_by = hz_utility_v2pub.last_updated_by,
2060: last_update_login = hz_utility_v2pub.last_update_login,
2063: program_id = hz_utility_v2pub.program_id,
2064: program_update_date = sysdate
2065: WHERE party_id = p_party_id;
2066: ELSIF p_contact_point_type = 'EMAIL' THEN
2067: UPDATE hz_parties
2068: /* Bug No : 4355133*/
2069: SET email_address = SUBSTRB(p_email_address,1,320),
2070: last_update_date = hz_utility_v2pub.last_update_date,
2071: last_updated_by = hz_utility_v2pub.last_updated_by,
2075: program_id = hz_utility_v2pub.program_id,
2076: program_update_date = sysdate
2077: WHERE party_id = p_party_id;
2078: ELSIF p_contact_point_type = 'PHONE' THEN
2079: UPDATE hz_parties
2080: SET primary_phone_contact_pt_id = p_phone_contact_pt_id,
2081: primary_phone_purpose = p_phone_purpose,
2082: primary_phone_line_type = p_phone_line_type,
2083: primary_phone_country_code = p_phone_country_code,
2845: -- Bug 3818648: check dss profile before call test_instance
2846: -- Bug 3867562: check dss only in party context
2847:
2848: IF NVL(fnd_profile.value('HZ_DSS_ENABLED'), 'N') = 'Y' AND
2849: l_contact_point_rec.owner_table_name IN ('HZ_PARTIES', 'HZ_PARTY_SITES')
2850: THEN
2851: l_test_security :=
2852: hz_dss_util_pub.test_instance(
2853: p_operation_code => 'INSERT',
2866: --
2867: -- Bug 3835601: replaced the dss message with a more user friendly message
2868: --
2869: FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_INSERT_PRIVILEGE');
2870: IF l_contact_point_rec.owner_table_name = 'HZ_PARTIES' THEN
2871: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',
2872: hz_dss_util_pub.get_display_name(null, 'PARTY_CONTACT_POINTS'));
2873: ELSIF l_contact_point_rec.owner_table_name = 'HZ_PARTY_SITES' THEN
2874: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',
3082: -- Bug 3818648: check dss profile before call test_instance
3083: -- Bug 3867562: check dss only in party context
3084:
3085: IF NVL(fnd_profile.value('HZ_DSS_ENABLED'), 'N') = 'Y' AND
3086: l_old_contact_point_rec.owner_table_name IN ('HZ_PARTIES', 'HZ_PARTY_SITES')
3087: THEN
3088: l_test_security :=
3089: hz_dss_util_pub.test_instance(
3090: p_operation_code => 'UPDATE',
3103: --
3104: -- Bug 3835601: replaced the dss message with a more user friendly message
3105: --
3106: FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_UPDATE_PRIVILEGE');
3107: IF l_old_contact_point_rec.owner_table_name = 'HZ_PARTIES' THEN
3108: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',
3109: hz_dss_util_pub.get_display_name(null, 'PARTY_CONTACT_POINTS'));
3110: ELSIF l_old_contact_point_rec.owner_table_name = 'HZ_PARTY_SITES' THEN
3111: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',
7199:
7200: ----(Party level relationship's contact point )
7201: CURSOR c_pl_rel_cp(p_party_id NUMBER) IS
7202: SELECT distinct ac.CUST_ACCT_SITE_ID
7203: FROM HZ_PARTIES p, HZ_CUST_ACCOUNT_ROLES ar,
7204: HZ_RELATIONSHIPS rel,HZ_CUST_ACCT_SITES_ALL ac
7205: WHERE rel.party_id=p_party_id
7206: AND ar.ROLE_TYPE = 'CONTACT'
7207: AND rel.party_id=ar.party_id
7211:
7212: -----(Site Level relationship's contact point )
7213: CURSOR c_sl_rel_cp(p_party_id NUMBER) IS
7214: SELECT distinct ac.CUST_ACCT_SITE_ID
7215: FROM HZ_PARTIES p, HZ_CUST_ACCOUNT_ROLES ar,
7216: HZ_RELATIONSHIPS rel,HZ_CUST_ACCT_SITES_ALL ac
7217: WHERE rel.party_id=p_party_id
7218: AND ar.ROLE_TYPE = 'CONTACT'
7219: AND ar.party_id = rel.party_id
7222: AND ar.cust_acct_site_id = ac.cust_acct_site_id;
7223:
7224: CURSOR c_party_type(p_party_id NUMBER) IS
7225: SELECT party_type
7226: FROM HZ_PARTIES
7227: WHERE party_id=p_party_id;
7228:
7229: l_owner_table_name HZ_CONTACT_POINTS.OWNER_TABLE_NAME%TYPE;
7230: l_contact_point_type HZ_CONTACT_POINTS.CONTACT_POINT_TYPE%TYPE;
7229: l_owner_table_name HZ_CONTACT_POINTS.OWNER_TABLE_NAME%TYPE;
7230: l_contact_point_type HZ_CONTACT_POINTS.CONTACT_POINT_TYPE%TYPE;
7231: TYPE siteidtab IS TABLE OF HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID%TYPE;
7232: l_siteidtab siteidtab;
7233: l_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
7234: BEGIN
7235: savepoint update_contact_point_search;
7236:
7237: l_owner_table_name := p_cp_rec.owner_table_name;
7237: l_owner_table_name := p_cp_rec.owner_table_name;
7238: l_contact_point_type := p_cp_rec.contact_point_type;
7239:
7240: IF (l_owner_table_name='HZ_PARTY_SITES' AND l_contact_point_type NOT IN ('EDI', 'EMAIL', 'WEB'))
7241: OR (l_owner_table_name='HZ_PARTIES' AND l_contact_point_type NOT IN ('EDI','WEB'))
7242: THEN
7243: IF( isModified( p_old_phone_rec.phone_number ,p_new_phone_rec.phone_number)
7244: OR isModified(p_old_phone_rec.phone_area_code ,p_new_phone_rec.phone_area_code)
7245: OR isModified(p_old_phone_rec.phone_country_code ,p_new_phone_rec.phone_country_code)
7253: IF l_siteidtab.COUNT >0 THEN
7254: FORALL i IN l_siteidtab.FIRST..l_siteidtab.LAST
7255: update HZ_CUST_ACCT_SITES_ALL set address_text=NULL where cust_acct_site_id=l_siteidtab(i);
7256: END IF;
7257: ELSE ---l_owner_table_name ='HZ_PARTIES'
7258: OPEN c_party_type(p_cp_rec.owner_table_id);
7259: FETCH c_party_type INTO l_party_type;
7260: CLOSE c_party_type;
7261: IF l_party_type='PARTY_RELATIONSHIP' THEN