392: and effective_end_date is null
393: and rownum = 1;
394: ELSIF l_party_type = 'PERSON' THEN
395: SELECT TAX_REFERENCE INTO l_matched_value
396: FROM HZ_PERSON_PROFILES
397: WHERE party_id = p_record_id
398: and effective_end_date is null
399: and rownum = 1;
400: END IF;
5810: AND HZ_PARTIES.party_id = p_party_id;
5811: ELSIF l_party_type = 'PERSON' THEN
5812: SELECT
5813: translate(HZ_PARTIES.PARTY_NAME, '%',' ')
5814: ,translate(HZ_PERSON_PROFILES.TAX_REFERENCE, '%',' ')
5815: INTO
5816: x_party_search_rec.PARTY_NAME
5817: ,x_party_search_rec.TAX_REFERENCE
5818: FROM HZ_PARTIES, HZ_PERSON_PROFILES
5814: ,translate(HZ_PERSON_PROFILES.TAX_REFERENCE, '%',' ')
5815: INTO
5816: x_party_search_rec.PARTY_NAME
5817: ,x_party_search_rec.TAX_REFERENCE
5818: FROM HZ_PARTIES, HZ_PERSON_PROFILES
5819: WHERE HZ_PARTIES.party_id = HZ_PERSON_PROFILES.party_id
5820: AND HZ_PERSON_PROFILES.effective_end_date is NULL
5821: AND HZ_PARTIES.party_id = p_party_id;
5822: END IF;
5815: INTO
5816: x_party_search_rec.PARTY_NAME
5817: ,x_party_search_rec.TAX_REFERENCE
5818: FROM HZ_PARTIES, HZ_PERSON_PROFILES
5819: WHERE HZ_PARTIES.party_id = HZ_PERSON_PROFILES.party_id
5820: AND HZ_PERSON_PROFILES.effective_end_date is NULL
5821: AND HZ_PARTIES.party_id = p_party_id;
5822: END IF;
5823: x_party_search_rec.PARTY_TYPE := l_party_type;
5816: x_party_search_rec.PARTY_NAME
5817: ,x_party_search_rec.TAX_REFERENCE
5818: FROM HZ_PARTIES, HZ_PERSON_PROFILES
5819: WHERE HZ_PARTIES.party_id = HZ_PERSON_PROFILES.party_id
5820: AND HZ_PERSON_PROFILES.effective_end_date is NULL
5821: AND HZ_PARTIES.party_id = p_party_id;
5822: END IF;
5823: x_party_search_rec.PARTY_TYPE := l_party_type;
5824: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5901: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5902: ) IS
5903: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5904: SELECT org_contact_id
5905: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
5906: WHERE HZ_ORG_CONTACTS.org_contact_id = cp_org_contact_id
5907: AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
5908: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
5909: AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
5907: AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
5908: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
5909: AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
5910: AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
5911: AND HZ_RELATIONSHIPS.subject_id = HZ_PERSON_PROFILES.party_id
5912: AND HZ_PERSON_PROFILES.effective_end_date IS NULL;
5913:
5914: I NUMBER;
5915: l_org_contact_id NUMBER;
5908: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
5909: AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
5910: AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
5911: AND HZ_RELATIONSHIPS.subject_id = HZ_PERSON_PROFILES.party_id
5912: AND HZ_PERSON_PROFILES.effective_end_date IS NULL;
5913:
5914: I NUMBER;
5915: l_org_contact_id NUMBER;
5916: J NUMBER:=1;