130: p_person_restrict_sql := 'exists ( SELECT 1 from HZ_ORG_CONTACTS oc, hz_relationships r'
131: || ' where oc.org_contact_id = stage.org_contact_id and'
132: || ' r.relationship_id = oc.party_relationship_id'
133: || ' and r.subject_type = ''PERSON'' AND r.object_type = ''ORGANIZATION'' '
134: || ' and exists ( SELECT 1 FROM HZ_PARTIES stage1 where stage1.party_id = r.subject_id'
135: || ' and ' || p_restrict1_sql || ' ) )' ;
136: p_final_restrict_sql := '((stage.org_contact_id is null and ' || p_restrict_sql || ') or (stage.org_contact_id is not null and '
137: || p_person_restrict_sql || ' ))';
138: END IF;
314: SELECT HZ_ORG_CONTACTS.JOB_TITLE
315: INTO l_matched_value
316: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
317: WHERE HZ_ORG_CONTACTS.org_contact_id = p_record_id
318: AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
319: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
320: AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
321: AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
322: AND HZ_RELATIONSHIPS.subject_id = HZ_PERSON_PROFILES.party_id
315: INTO l_matched_value
316: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
317: WHERE HZ_ORG_CONTACTS.org_contact_id = p_record_id
318: AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
319: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
320: AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
321: AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
322: AND HZ_RELATIONSHIPS.subject_id = HZ_PERSON_PROFILES.party_id
323: AND HZ_PERSON_PROFILES.effective_end_date IS NULL and rownum=1;
1338: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1339: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1340: AND r.object_id = p_party_id
1341: AND r.subject_id = stage.party_id
1342: AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1343: AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1344: AND r.relationship_id = oc.party_relationship_id
1345: AND oc.org_contact_id = stage.org_contact_id;
1346: END IF;
1339: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1340: AND r.object_id = p_party_id
1341: AND r.subject_id = stage.party_id
1342: AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1343: AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1344: AND r.relationship_id = oc.party_relationship_id
1345: AND oc.org_contact_id = stage.org_contact_id;
1346: END IF;
1347: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN
1757: AND( (l_search_merged ='Y' )
1758: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1759: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1760: AND r.subject_id = stage.party_id
1761: AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1762: AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1763: AND r.relationship_id = oc.party_relationship_id
1764: AND oc.org_contact_id = stage.org_contact_id;
1765: END IF;
1758: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1759: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1760: AND r.subject_id = stage.party_id
1761: AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1762: AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1763: AND r.relationship_id = oc.party_relationship_id
1764: AND oc.org_contact_id = stage.org_contact_id;
1765: END IF;
1766: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN
3082: RETURN NUMBER IS
3083: l_party_type VARCHAR2(255);
3084: l_person_id NUMBER(15);
3085: BEGIN
3086: SELECT party_type INTO l_party_type from hz_parties where party_id = p_party_id;
3087: IF l_party_type = 'PERSON' THEN
3088: RETURN p_party_id;
3089: ELSIF p_contact_id IS NULL THEN
3090: RETURN NULL;
3089: ELSIF p_contact_id IS NULL THEN
3090: RETURN NULL;
3091: ELSE
3092: BEGIN
3093: SELECT subject_id INTO l_person_id FROM HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, hz_parties p
3094: WHERE oc.org_contact_id = p_contact_id
3095: AND r.relationship_id = oc.party_relationship_id
3096: AND r.object_id = p_party_id
3097: AND p.party_id = r.subject_id
4120: l_match_idx NUMBER;
4121: cursor get_cpt_for_contact_id is select contact_point_id
4122: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4123: where a.party_relationship_id = b.relationship_id
4124: and c.owner_table_name = 'HZ_PARTIES'
4125: and c.primary_flag='Y'
4126: and c.owner_table_id = b.party_id
4127: and b.directional_flag = 'F'
4128: and a.org_contact_id = p_org_contact_id;
5397: FOR CONTACTS IN (
5398: SELECT org_contact_id
5399: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS
5400: WHERE HZ_RELATIONSHIPS.object_id = p_party_id
5401: AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
5402: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
5403: AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
5404: AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
5405: AND ROWNUM<6
5398: SELECT org_contact_id
5399: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS
5400: WHERE HZ_RELATIONSHIPS.object_id = p_party_id
5401: AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
5402: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
5403: AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
5404: AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
5405: AND ROWNUM<6
5406: ) LOOP
5410: FOR CONTACT_POINTS IN (
5411: SELECT CONTACT_POINT_ID
5412: FROM HZ_CONTACT_POINTS
5413: WHERE PRIMARY_FLAG = 'Y'
5414: AND owner_table_name = 'HZ_PARTIES'
5415: AND owner_table_id = p_party_id
5416:
5417: UNION
5418:
5418:
5419: SELECT CONTACT_POINT_ID
5420: FROM HZ_CONTACT_POINTS,HZ_ORG_CONTACTS, HZ_RELATIONSHIPS
5421: WHERE PRIMARY_FLAG = 'Y'
5422: AND owner_table_name = 'HZ_PARTIES'
5423: AND OWNER_TABLE_ID = HZ_RELATIONSHIPS.party_id
5424: AND HZ_RELATIONSHIPS.object_id = p_party_id
5425: AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
5426: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
5421: WHERE PRIMARY_FLAG = 'Y'
5422: AND owner_table_name = 'HZ_PARTIES'
5423: AND OWNER_TABLE_ID = HZ_RELATIONSHIPS.party_id
5424: AND HZ_RELATIONSHIPS.object_id = p_party_id
5425: AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
5426: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
5427: AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
5428: and l_use_contact_info = 'Y'
5429: AND ROWNUM<6
5422: AND owner_table_name = 'HZ_PARTIES'
5423: AND OWNER_TABLE_ID = HZ_RELATIONSHIPS.party_id
5424: AND HZ_RELATIONSHIPS.object_id = p_party_id
5425: AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
5426: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
5427: AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
5428: and l_use_contact_info = 'Y'
5429: AND ROWNUM<6
5430: UNION
5516: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5517: hz_utility_v2pub.debug(p_message=>'Inside calling procedure - get_party_rec',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5518: END IF;
5519: SELECT PARTY_TYPE INTO l_party_type
5520: FROM HZ_PARTIES
5521: WHERE PARTY_ID = p_party_id;
5522:
5523: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5524: hz_utility_v2pub.debug(p_message=>'l_party_type is - '||l_party_type,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
5610: SELECT org_contact_id
5611: ,translate(HZ_ORG_CONTACTS.JOB_TITLE, '%',' ')
5612: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
5613: WHERE HZ_ORG_CONTACTS.org_contact_id = cp_org_contact_id
5614: AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
5615: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
5616: AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
5617: AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
5618: AND HZ_RELATIONSHIPS.subject_id = HZ_PERSON_PROFILES.party_id
5611: ,translate(HZ_ORG_CONTACTS.JOB_TITLE, '%',' ')
5612: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
5613: WHERE HZ_ORG_CONTACTS.org_contact_id = cp_org_contact_id
5614: AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
5615: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
5616: AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
5617: AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
5618: AND HZ_RELATIONSHIPS.subject_id = HZ_PERSON_PROFILES.party_id
5619: AND HZ_PERSON_PROFILES.effective_end_date IS NULL;