Retrieving the Active Address of an Employee using PERSON_ID

note : the critical thing to know is PER_ADDRESSES table; since the employee's address is not explicitly defined in PER_ALL_PEOPLE_F

  FUNCTION get_address_fn (i_application_date IN DATE,
                           i_person_id IN XXHLS_TYPES_PKG.t_person_id)
    RETURN VARCHAR2
   
    IS
   
      l_address_line1 VARCHAR2(240) DEFAULT '';
      l_address_line2 VARCHAR2(240) DEFAULT '';
      l_address_line3 VARCHAR2(240) DEFAULT '';   
   
    BEGIN
   
      SELECT  ADDRESS_LINE1, ADDRESS_LINE2, ADDRESS_LINE3
      INTO    l_address_line1, l_address_line2, l_address_line3
      FROM    PER_ADDRESSES
      WHERE   PERSON_ID = 7890
              AND PRIMARY_FLAG = 'Y'
              AND i_application_date BETWEEN DATE_FROM AND DATE_TO;
   
      RETURN l_address_line1 || ' ' || l_address_line2 || ' ' || l_address_line3;
     
      EXCEPTION
        -- case for future dated record, open ended record
     
        WHEN NO_DATA_FOUND THEN
       
          SELECT  ADDRESS_LINE1, ADDRESS_LINE2, ADDRESS_LINE3
          INTO    l_address_line1, l_address_line2, l_address_line3
          FROM    PER_ADDRESSES
          WHERE   PERSON_ID = 7890
                  AND PRIMARY_FLAG = 'Y'
                  AND DATE_TO IS NULL;
           
          RETURN l_address_line1 || ' ' || l_address_line2 || ' ' || l_address_line3;
   
    END get_address_fn;