480: and effective_end_date is null
481: and rownum = 1;
482: ELSIF l_party_type = 'PERSON' THEN
483: SELECT TAX_REFERENCE INTO l_matched_value
484: FROM HZ_PERSON_PROFILES
485: WHERE party_id = p_record_id
486: and effective_end_date is null
487: and rownum = 1;
488: END IF;
500: and effective_end_date is null
501: and rownum = 1;
502: ELSIF l_party_type = 'PERSON' THEN
503: SELECT JGZZ_FISCAL_CODE INTO l_matched_value
504: FROM HZ_PERSON_PROFILES
505: WHERE party_id = p_record_id
506: and effective_end_date is null
507: and rownum = 1;
508: END IF;
6571: AND HZ_ORGANIZATION_PROFILES.effective_end_date is NULL
6572: AND HZ_PARTIES.party_id = p_party_id;
6573: ELSIF l_party_type = 'PERSON' THEN
6574: SELECT
6575: translate(HZ_PERSON_PROFILES.JGZZ_FISCAL_CODE, '%',' ')
6576: ,translate(HZ_PARTIES.PARTY_NUMBER, '%',' ')
6577: ,translate(HZ_PARTIES.PARTY_TYPE, '%',' ')
6578: ,translate(HZ_PERSON_PROFILES.TAX_REFERENCE, '%',' ')
6579: INTO
6574: SELECT
6575: translate(HZ_PERSON_PROFILES.JGZZ_FISCAL_CODE, '%',' ')
6576: ,translate(HZ_PARTIES.PARTY_NUMBER, '%',' ')
6577: ,translate(HZ_PARTIES.PARTY_TYPE, '%',' ')
6578: ,translate(HZ_PERSON_PROFILES.TAX_REFERENCE, '%',' ')
6579: INTO
6580: x_party_search_rec.JGZZ_FISCAL_CODE
6581: ,x_party_search_rec.PARTY_NUMBER
6582: ,x_party_search_rec.PARTY_TYPE
6580: x_party_search_rec.JGZZ_FISCAL_CODE
6581: ,x_party_search_rec.PARTY_NUMBER
6582: ,x_party_search_rec.PARTY_TYPE
6583: ,x_party_search_rec.TAX_REFERENCE
6584: FROM HZ_PARTIES, HZ_PERSON_PROFILES
6585: WHERE HZ_PARTIES.party_id = HZ_PERSON_PROFILES.party_id
6586: AND HZ_PERSON_PROFILES.effective_end_date is NULL
6587: AND HZ_PARTIES.party_id = p_party_id;
6588: END IF;
6581: ,x_party_search_rec.PARTY_NUMBER
6582: ,x_party_search_rec.PARTY_TYPE
6583: ,x_party_search_rec.TAX_REFERENCE
6584: FROM HZ_PARTIES, HZ_PERSON_PROFILES
6585: WHERE HZ_PARTIES.party_id = HZ_PERSON_PROFILES.party_id
6586: AND HZ_PERSON_PROFILES.effective_end_date is NULL
6587: AND HZ_PARTIES.party_id = p_party_id;
6588: END IF;
6589: x_party_search_rec.PARTY_TYPE := l_party_type;
6582: ,x_party_search_rec.PARTY_TYPE
6583: ,x_party_search_rec.TAX_REFERENCE
6584: FROM HZ_PARTIES, HZ_PERSON_PROFILES
6585: WHERE HZ_PARTIES.party_id = HZ_PERSON_PROFILES.party_id
6586: AND HZ_PERSON_PROFILES.effective_end_date is NULL
6587: AND HZ_PARTIES.party_id = p_party_id;
6588: END IF;
6589: x_party_search_rec.PARTY_TYPE := l_party_type;
6590: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6675: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
6676: ) IS
6677: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
6678: SELECT org_contact_id
6679: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
6680: WHERE HZ_ORG_CONTACTS.org_contact_id = cp_org_contact_id
6681: AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
6682: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
6683: AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
6681: AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
6682: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
6683: AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
6684: AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
6685: AND HZ_RELATIONSHIPS.subject_id = HZ_PERSON_PROFILES.party_id
6686: AND HZ_PERSON_PROFILES.effective_end_date IS NULL;
6687:
6688: I NUMBER;
6689: l_org_contact_id NUMBER;
6682: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
6683: AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
6684: AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
6685: AND HZ_RELATIONSHIPS.subject_id = HZ_PERSON_PROFILES.party_id
6686: AND HZ_PERSON_PROFILES.effective_end_date IS NULL;
6687:
6688: I NUMBER;
6689: l_org_contact_id NUMBER;
6690: J NUMBER:=1;