DECLARE

ln_person_id          NUMBER;
lv_emp_number         per_all_people_f.employee_number%TYPE; -- IN/OUT
ln_ovn                per_all_people_f.object_version_number%TYPE; -- IN/OUT
ld_eff_start_date     DATE;  --OUT
ld_eff_end_date       DATE; -- OUT
lv_email_address      per_all_people_f.email_address%TYPE;
lv_title              per_all_people_f.title%TYPE;
ld_date_of_birth      per_all_people_f.date_of_birth%TYPE;
lv_first_name         per_all_people_f.first_name%TYPE;
lv_middle_name        per_all_people_f.middle_names%TYPE;
lv_last_name          per_all_people_f.last_name%TYPE;
lv_sex                per_all_people_f.sex%TYPE;
lv_country_of_birth   per_all_people_f.country_of_birth%TYPE;
lv_known_as           per_all_people_f.known_as%TYPE;
lc_mode               VARCHAR2(50);
lc_chg_date            DATE;
--lv_umid             future development;

--OUT
lv_full_name          per_all_people_f.full_name%TYPE; 
ln_comment_id         NUMBER;
lb_cmbn_warn          BOOLEAN;
lb_payroll_warn       BOOLEAN;
lb_orig_hire_warn     BOOLEAN;

--- Constants
lc_emp_apl             CONSTANT VARCHAR2(1)   := 'Y';
c_chg_date             CONSTANT DATE           := TRUNC(SYSDATE);
lc_upd_mode            CONSTANT VARCHAR2(50)  := 'UPDATE';
lc_upd_ovr_mode        CONSTANT VARCHAR2(50)  := 'UPDATE_OVERRIDE';
lc_correct_mode        CONSTANT VARCHAR2(50)  := 'CORRECTION';


CURSOR csr_get_person 
IS 
SELECT *
FROM  per_all_people_f papf
WHERE (c_chg_date BETWEEN papf.effective_start_date AND papf.effective_end_date
        OR papf.effective_start_date > c_chg_date)

--AND papf.current_employee_flag = lc_emp_apl
;

BEGIN
  FOR per_rec IN csr_get_person LOOP
    ln_person_id            := per_rec.person_id;
    lv_emp_number           := per_rec.employee_number;
    ln_ovn                  := per_rec.object_version_number;
    ld_eff_start_date       := per_rec.effective_start_date;
    ld_eff_end_date         := per_rec.effective_end_date;
    lv_email_address        := per_rec.email_address;
    lv_title                := per_rec.title;
    ld_date_of_birth        := per_rec.date_of_birth;
    lv_first_name           := per_rec.first_name;
    lv_middle_name          := per_rec.middle_names;
    lv_last_name            := per_rec.last_name;
    lv_sex                  := per_rec.sex;
    lv_country_of_birth     := per_rec.country_of_birth;
    lv_known_as             := per_rec.known_as;
    
    
---- effective date and date track     
    IF ld_eff_end_date = hr_general.end_of_time THEN 
       lc_mode := lc_upd_mode;
    ELSE
      lc_mode := lc_upd_ovr_mode;
    END IF;
    
    IF ld_eff_start_date = c_chg_date THEN
      lc_mode := lc_correct_mode;
    END IF;
    
    IF ld_eff_start_date > c_chg_date THEN
      lc_chg_date := ld_eff_start_date;
      lc_mode := lc_correct_mode;
    ELSE
      lc_chg_date := c_chg_date;
    END IF;

    hr_au_person_api.update_au_person
    ( p_effective_date                  => c_chg_date
    ,p_datetrack_update_mode            => lc_mode 
    ,p_person_id                        => ln_person_id
    ,p_object_version_number            => ln_ovn 
    ,p_employee_number                  => lv_emp_number
    ,p_title                            => lv_title
    ,p_date_of_birth                    => ld_date_of_birth
    ,p_first_name                       => lv_first_name 
    ,p_middle_name                      => lv_middle_name
    ,p.last_name                        => lv_last_name
    ,p_sex                              => lv_sex
    ,p_country_of_birth                 => lv_country_of_birth
    ,p_known_as                         => lv_known_as
    ,p_effective_start_date             => ld_eff_start_date  --out
    ,p_effective_end_date               => ld_eff_end_date --out
    ,p_full_name                        => lv_full_name --out
    ,p_comment_id                       => ln_comment_id --out
    ,p_cmbn_warn                        => lb_cmbn_warn --out
    ,p_payroll_warn                     => lb_payroll_warn --out
    ,p_orig_hire_warn                   => lb_orig_hire_warn --out
);
 
  END LOOP; -- per rec
--  EXCEPTION
END;