[Home] [Help]
270: c_url varchar2) IS
271: select contact_point_id
272: from hz_contact_points
273: where owner_table_id = c_owner_table_id
274: and owner_table_name = 'HZ_PARTIES'
275: and nvl(email_address,'1') = nvl(c_email_address,'1')
276: and nvl(phone_area_code,'1') = nvl(c_phone_area_code,'1')
277: and nvl(phone_number,'1') = nvl(c_phone_number,'1')
278: and nvl(phone_line_type,'1') = nvl(c_phone_line_type,'1')
487: SELECT COUNT(*) NO_PRIMARY_CPS
488: INTO l_no_of_primary_cps
489: FROM HZ_CONTACT_POINTS
490: WHERE CONTACT_POINT_TYPE = 'PHONE' AND PRIMARY_FLAG = 'Y'
491: AND OWNER_TABLE_NAME = 'HZ_PARTIES' AND OWNER_TABLE_ID = pI.rel_party_id;
492:
493: write_log(3, 'AC : No. of primary contact points found ' || l_no_of_primary_cps || ' for part rel id : ' || pI.rel_party_id);
494:
495: -- Validation
495: -- Validation
496: If l_no_of_primary_cps < 1 then
497: UPDATE HZ_CONTACT_POINTS
498: SET PRIMARY_FLAG = 'Y'
499: WHERE owner_table_name = 'HZ_PARTIES'
500: AND contact_point_type = 'PHONE'
501: AND owner_table_id = pI.rel_party_id
502: AND ROWNUM = 1; --to update 1 row
503:
510: -- name: do_assign_flex
511: -- scope: private
512: -- assign the Recs with flex values
513: -- note: currently, the following entities are only supported
514: -- 1. HZ_PARTIES
515: -- 2. HZ_LOCATIONS
516: -- 3. HZ_CONTACT_POINTS
517: -- 4. HZ_PARTY_SITES
518: -- 5. HZ_ORG_CONTACTS
553: l_index NUMBER := 1;
554:
555: BEGIN
556: For I in cGetFlex Loop
557: if (pEntity = 'HZ_PARTIES') then
558: pHzpRec.attribute_category := I.attr_val_category;
559: pHzpRec.attribute1 := I.attr_val_1;
560: pHzpRec.attribute2 := I.attr_val_2;
561: pHzpRec.attribute3 := I.attr_val_3;
963: -- l_dummy_rec6 ,
964: l_dummy_rec7 ,
965: l_dummy_tbl8,
966: l_dummy_tbl9,
967: 'HZ_PARTIES',
968: pI.import_interface_id,
969: G_return_status
970: );
971: if G_return_status = FND_API.G_RET_STS_SUCCESS Then
1080: into l_res_id
1081: from hz_contact_preferences
1082: where contact_level_table_id = pI.rel_party_id
1083: and contact_type = 'MAIL'
1084: and contact_level_table = 'HZ_PARTIES';
1085:
1086: Exception
1087: when NO_DATA_FOUND then
1088: l_res_rec.contact_type := 'MAIL';
1086: Exception
1087: when NO_DATA_FOUND then
1088: l_res_rec.contact_type := 'MAIL';
1089: l_res_rec.preference_start_date := sysdate;
1090: l_res_rec.contact_level_table := 'HZ_PARTIES';
1091: -- l_res_rec.subject_id:= pI.contact_party_id;
1092: l_res_rec.contact_level_table_id:= pI.rel_party_id;
1093:
1094: write_log (3, 'Creating CntPreference: CMAIL');
1120: into l_res_id
1121: from hz_contact_preferences
1122: where contact_level_table_id = pI.rel_party_id
1123: and contact_type = 'CALL'
1124: and contact_level_table = 'HZ_PARTIES';
1125:
1126: Exception
1127: when NO_DATA_FOUND then
1128: l_res_rec.contact_type := 'CALL';
1126: Exception
1127: when NO_DATA_FOUND then
1128: l_res_rec.contact_type := 'CALL';
1129: l_res_rec.preference_start_date := sysdate;
1130: l_res_rec.contact_level_table := 'HZ_PARTIES';
1131: -- l_res_rec.subject_id:= pI.contact_party_id;
1132: l_res_rec.contact_level_table_id:= pI.rel_party_id;
1133:
1134: write_log (3, 'Creating CntPreference: PHONE');
1194: into l_res_id
1195: from hz_contact_preferences
1196: where contact_level_table_id = pI.rel_party_id
1197: and contact_type = 'FAX'
1198: and contact_level_table = 'HZ_PARTIES';
1199:
1200: Exception
1201: when NO_DATA_FOUND then
1202: l_res_rec.contact_type := 'FAX';
1200: Exception
1201: when NO_DATA_FOUND then
1202: l_res_rec.contact_type := 'FAX';
1203: l_res_rec.preference_start_date := sysdate;
1204: l_res_rec.contact_level_table := 'HZ_PARTIES';
1205: -- l_res_rec.subject_id:= pI.contact_party_id;
1206: l_res_rec.contact_level_table_id:= pI.rel_party_id;
1207:
1208: write_log (3, 'Creating CntPreference: FAX');
1232: into l_res_id
1233: from hz_contact_preferences
1234: where contact_level_table_id = pI.rel_party_id
1235: and contact_type = 'EMAIL'
1236: and contact_level_table = 'HZ_PARTIES';
1237:
1238: Exception
1239: when NO_DATA_FOUND then
1240: l_res_rec.contact_type := 'EMAIL';
1238: Exception
1239: when NO_DATA_FOUND then
1240: l_res_rec.contact_type := 'EMAIL';
1241: l_res_rec.preference_start_date := sysdate;
1242: l_res_rec.contact_level_table := 'HZ_PARTIES';
1243: -- l_res_rec.subject_id:= pI.contact_party_id;
1244: l_res_rec.contact_level_table_id:= pI.rel_party_id;
1245:
1246: write_log (3, 'Creating CntPreference: EMAIL');
1307: l_cp_rec.created_by_module := 'AML_LEAD_IMPORT';
1308: l_cp_rec.application_id := 530;
1309:
1310: l_cp_rec.status := 'A';
1311: l_cp_rec.owner_table_name := 'HZ_PARTIES';
1312: If(pI.party_type = 'ORGANIZATION') Then
1313: l_cp_rec.owner_table_id := pI.rel_party_id;
1314: else
1315: l_cp_rec.owner_table_id := pI.party_id;
1525: pCP.owner_table_name <> FND_API.G_MISS_CHAR)
1526: THEN
1527: l_cp_rec.owner_table_name := pCP.owner_table_name;
1528: ELSE
1529: l_cp_rec.owner_table_name := 'HZ_PARTIES';
1530: END IF;
1531: IF (pCP.owner_table_id is not null and
1532: pCP.owner_table_id <> FND_API.G_MISS_NUM)
1533: THEN
1897: -- l_dummy_rec6 ,
1898: l_dummy_rec7 ,
1899: l_dummy_tbl8 ,
1900: l_dummy_tbl9 ,
1901: 'HZ_PARTIES',
1902: pI.import_interface_id,
1903: G_return_status
1904: );
1905:
2094: Select party_id into pI.rel_party_id
2095: from hz_relationships
2096: where subject_id = pI.contact_party_id
2097: and object_id = pI.party_id
2098: and subject_table_name = 'HZ_PARTIES'
2099: and object_table_name = 'HZ_PARTIES'
2100: and relationship_code = 'CONTACT_OF';
2101:
2102: /* *****
2095: from hz_relationships
2096: where subject_id = pI.contact_party_id
2097: and object_id = pI.party_id
2098: and subject_table_name = 'HZ_PARTIES'
2099: and object_table_name = 'HZ_PARTIES'
2100: and relationship_code = 'CONTACT_OF';
2101:
2102: /* *****
2103: -- ffang 073101, use hz_relationships instead of hz_party_relationships
2128: l_org_con_rec.party_site_id := pI.party_site_id;
2129: l_org_con_rec.orig_system_reference := pI.import_interface_id;
2130: l_org_con_rec.party_rel_rec.subject_id := pI.contact_party_id;
2131: l_org_con_rec.party_rel_rec.subject_type := 'PERSON';
2132: l_org_con_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
2133: l_org_con_rec.party_rel_rec.object_id := pI.party_id;
2134: l_org_con_rec.party_rel_rec.object_type := 'ORGANIZATION';
2135: l_org_con_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
2136: l_org_con_rec.party_rel_rec.relationship_type := 'CONTACT';
2131: l_org_con_rec.party_rel_rec.subject_type := 'PERSON';
2132: l_org_con_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
2133: l_org_con_rec.party_rel_rec.object_id := pI.party_id;
2134: l_org_con_rec.party_rel_rec.object_type := 'ORGANIZATION';
2135: l_org_con_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
2136: l_org_con_rec.party_rel_rec.relationship_type := 'CONTACT';
2137: l_org_con_rec.party_rel_rec.start_date:= sysdate;
2138: l_org_con_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
2139:
2391: -- ajchatto, for retrieving primary contact point of primary contact
2392: CURSOR c_get_primary_cp (c_rel_party_id number) IS
2393: SELECT CONTACT_POINT_ID
2394: FROM HZ_CONTACT_POINTS
2395: WHERE OWNER_TABLE_NAME = 'HZ_PARTIES' AND CONTACT_POINT_TYPE = 'PHONE'
2396: AND PRIMARY_FLAG = 'Y' AND OWNER_TABLE_ID = c_rel_party_id
2397: AND ROWNUM = 1; --TO SELECT ONE ROW
2398:
2399: -- swkhanna 7/30/02 get assign_to_person_id if not being passed in table
2773: -- Get the primary contact point id of the primary contact and use it in phone_id
2774: SELECT CONTACT_POINT_ID
2775: INTO l_contact_point_id
2776: FROM HZ_CONTACT_POINTS
2777: WHERE OWNER_TABLE_NAME = 'HZ_PARTIES' AND CONTACT_POINT_TYPE = 'PHONE'
2778: AND PRIMARY_FLAG = 'Y' AND OWNER_TABLE_ID = pI.REL_PARTY_ID
2779: AND ROWNUM = 1; --TO SELECT ONE ROW
2780:
2781: l_sales_lead_contact_tbl(1).phone_id := l_contact_point_id;
3148: -- and not supported by hz_party_pub ARHPTYSB.pls 115.72
3149: -- Rashmi Goyal sent an email stating that this is a non updatable column.
3150: -- Until that gets fixed, it was decided by Sr Management that we update
3151: -- the table directly.
3152: update hz_parties
3153: set orig_system_reference = l_osysref
3154: where party_id = pI.party_id;
3155:
3156: /* ***
3188:
3189: ----------------------------------------------------------
3190: -- Name: trans_custkey
3191: -- Scope: Public
3192: -- Select customer key from HZ_PARTIES when osysref matches
3193: ----------------------------------------------------------
3194: function trans_custkey (p_osysref IN Varchar2) Return Varchar2
3195: IS
3196: l_tmp hz_parties.customer_key%type;
3192: -- Select customer key from HZ_PARTIES when osysref matches
3193: ----------------------------------------------------------
3194: function trans_custkey (p_osysref IN Varchar2) Return Varchar2
3195: IS
3196: l_tmp hz_parties.customer_key%type;
3197: BEGIN
3198: Select customer_key into l_tmp
3199: from hz_parties hzp
3200: where hzp.orig_system_reference = p_osysref
3195: IS
3196: l_tmp hz_parties.customer_key%type;
3197: BEGIN
3198: Select customer_key into l_tmp
3199: from hz_parties hzp
3200: where hzp.orig_system_reference = p_osysref
3201: and hzp.status = 'A'
3202: and rownum < 2;
3203: Return l_tmp;
3230: p.last_update_date, s.address_key, s.country,
3231: nvl(ps.identifying_address_flag, 'N')
3232: identifying_address_flag,
3233: ps.location_id, ps.party_site_id
3234: from as_import_interface s, hz_parties p, hz_party_sites ps
3235: where s.load_status = 'NEW'
3236: and s.import_interface_id = p_interface_id
3237: and p.customer_key (+) = s.customer_key
3238: and p.party_type (+) = s.party_type
3280: End if;
3281: End If;
3282: if (p_party_id is not null) then
3283: select last_update_date into p_plupd_date
3284: from hz_parties
3285: where party_id = p_party_id and rownum = 1;
3286: End If;
3287:
3288: exception when others then
3410: -- Cursor for getting matched party_id from hz_matched_parties_gt
3411: CURSOR C_matched_party(p_party_type VARCHAR2)
3412: IS
3413: SELECT HZMP.party_id, score, hzp.creation_date
3414: FROM HZ_MATCHED_PARTIES_GT hzmp, HZ_PARTIES hzp
3415: WHERE search_context_id = l_search_context_id
3416: AND hzp.party_id = hzmp.party_id
3417: AND hzp.party_type = p_party_type
3418: AND nvl(hzp.status,'A') = 'A' --bug# 3319259
3423: CURSOR C_matched_party2(p_party_type VARCHAR2)
3424: IS
3425: SELECT HZMP.party_id, score, hzp.creation_date, hzp.status
3426: , hzp.object_version_number
3427: FROM HZ_MATCHED_PARTIES_GT hzmp, HZ_PARTIES hzp
3428: WHERE search_context_id = l_search_context_id
3429: AND hzp.party_id = hzmp.party_id
3430: AND hzp.party_type = p_party_type
3431: ORDER BY score desc, hzp.creation_date desc;
3475: -- Cursor for Orig_system_reference dup check
3476: CURSOR c_check_orig_sys_ref(p_orig_system_ref varchar)
3477: IS
3478: SELECT party_id
3479: FROM hz_parties hzp
3480: WHERE hzp.orig_system_reference = p_orig_system_ref
3481: AND nvl(hzp.status,'A') = 'A';
3482: -- AND rownum < 2;
3483:
3696: -- Full search
3697: HZ_PARTY_SEARCH.find_parties ('T',l_rule_id, party_cond, party_site_cond, contact_cond , contact_point_cond, NULL,
3698: 'N',l_search_context_id, l_num_matches, l_return_status, l_msg_count, l_msg_data);
3699: ELSE
3700: -- l_restrict_sql := ' party_id in (select party_id from hz_parties where orig_system_reference = '''||I.orig_system_reference||''')';
3701: write_log(3,'In Restrict sql');
3702:
3703: -- Restricted search by passing p_restrict_sql
3704: HZ_PARTY_SEARCH.find_parties ('T',l_rule_id, party_cond, party_site_cond, contact_cond , contact_point_cond,
3701: write_log(3,'In Restrict sql');
3702:
3703: -- Restricted search by passing p_restrict_sql
3704: HZ_PARTY_SEARCH.find_parties ('T',l_rule_id, party_cond, party_site_cond, contact_cond , contact_point_cond,
3705: '/* SELECTIVE */ party_id in (select party_id from hz_parties where ORIG_SYSTEM_REFERENCE = '''||I.orig_system_reference||''') ',
3706: 'N',l_search_context_id, l_num_matches, l_return_status, l_msg_count, l_msg_data);
3707:
3708: IF l_num_matches = 0 THEN
3709: -- Full search