490: IF p_attribute = 'JOB_TITLE' THEN
491: BEGIN
492: SELECT HZ_ORG_CONTACTS.JOB_TITLE
493: INTO l_matched_value
494: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
495: WHERE HZ_ORG_CONTACTS.org_contact_id = p_record_id
496: AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
497: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
498: AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
496: AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
497: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
498: AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
499: AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
500: AND HZ_RELATIONSHIPS.subject_id = HZ_PERSON_PROFILES.party_id
501: AND HZ_PERSON_PROFILES.effective_end_date IS NULL and rownum=1;
502: EXCEPTION
503: WHEN NO_DATA_FOUND THEN
504: l_matched_value := 'Err';
497: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
498: AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
499: AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
500: AND HZ_RELATIONSHIPS.subject_id = HZ_PERSON_PROFILES.party_id
501: AND HZ_PERSON_PROFILES.effective_end_date IS NULL and rownum=1;
502: EXCEPTION
503: WHEN NO_DATA_FOUND THEN
504: l_matched_value := 'Err';
505: END;
6098: ) IS
6099: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
6100: SELECT org_contact_id
6101: ,translate(HZ_ORG_CONTACTS.JOB_TITLE, '%',' ')
6102: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
6103: WHERE HZ_ORG_CONTACTS.org_contact_id = cp_org_contact_id
6104: AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
6105: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
6106: AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
6104: AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
6105: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
6106: AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
6107: AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
6108: AND HZ_RELATIONSHIPS.subject_id = HZ_PERSON_PROFILES.party_id
6109: AND HZ_PERSON_PROFILES.effective_end_date IS NULL;
6110:
6111: I NUMBER;
6112: l_org_contact_id NUMBER;
6105: AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
6106: AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
6107: AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
6108: AND HZ_RELATIONSHIPS.subject_id = HZ_PERSON_PROFILES.party_id
6109: AND HZ_PERSON_PROFILES.effective_end_date IS NULL;
6110:
6111: I NUMBER;
6112: l_org_contact_id NUMBER;
6113: J NUMBER:=1;