BEGIN 
  DELETE 
  FROM   hu_tmp_emp_working_full 
  WHERE  upper(created_by) = upper(:APP_USER);FOR i IN 1 .. apex_application.g_f01.count 
  LOOP 
    FOR employees IN 
    ( 
             SELECT   employee_id 
             FROM     hu_employees 
             WHERE    org_id = apex_application.g_f02(apex_application.g_f01(i)) 
             AND      nvl(work_status,0) <> 4 
                      --WHERE ORG_ID = apex_application.g_f01(i) 
             ORDER BY org_id ) 
    LOOP 
      BEGIN 
        --calling store procedure 
        hu_report_emp_working_full (username => :APP_USER, 
                                    emp_id => employees.employee_id);
      EXCEPTION 
      WHEN OTHERS THEN 
        CONTINUE;
      END;
    END LOOP;
  END LOOP;
END;
--HU_REPORT_EMP_WORKING_FULL store PROCEDURE: 
CREATE 
  OR 
  replace 
PROCEDURE "HU_REPORT_EMP_WORKING_FULL" (username IN VARCHAR2, 
                                        emp_id   IN VARCHAR2) 
IS 
  v_pos_id0 NUMBER;v_pos_id1 NUMBER;v_pos_id2 NUMBER;v_org_id0 NUMBER;v_org_id1 NUMBER;v_org_id2 NUMBER;v_count   NUMBER := 0;CURSOR employee_cur IS
    SELECT "EMPLOYEE_ID", 
           "CIF", 
           "VN_FULLNAME", 
           "GENDER", 
           "BIRTH_DATE", 
           "MARITAL_STATUS", 
           "RELIGION", 
           "NATIONALITY", 
           "CUR_ADDRESS", 
           "PER_ADDRESS", 
           "ID", 
           "ID_NO", 
           "ID_DATE", 
           "ID_PLACE", 
           "WORK_STATUS", 
           "EFFECT_DATE", 
           "APPLIED_BY", 
           "BANK", 
           "BANK_CODE", 
           "ACCOUNT_NO", 
           "MOBILE", 
           "TEL", 
           "WORK_TEL", 
           "EMAIL1", 
           "EMAIL2", 
           "JOIN_DATE", 
           "DOITUONG_ID", 
           "WEND_DATE", 
           "CONTRACT_TYPE", 
           "CEND_DATE", 
           "ACADEMY_LV", 
           "SPECIALIZE", 
           "LOCATION", 
           "POS_ID", 
           "ORG_ID", 
           "ORG_ID_TT" 
    FROM   hu_employees 
    WHERE  employee_id = emp_id 
    AND    nvl(work_status,0) <> 4;v_employee employee_cur%ROWTYPE;CURSOR working_coex_cur IS 
  SELECT DISTINCT pos_id, 
                  org_id 
  FROM            hu_working_coex 
  WHERE           employee_id = emp_id 
  AND             nvl(work_status,0) <> 4;v_working_coex working_coex_cur%ROWTYPE;BEGIN 
  OPEN employee_cur; 
  LOOP 
    FETCH employee_cur 
    INTO  v_employee; 
     
    EXIT 
  WHEN employee_cur%NOTFOUND; 
    OPEN working_coex_cur; 
    LOOP 
      FETCH working_coex_cur 
      INTO  v_working_coex; 
       
      EXIT 
    WHEN working_coex_cur%NOTFOUND; 
      CASE v_count 
      WHEN 0 THEN 
        BEGIN 
          v_pos_id0 := v_working_coex.pos_id; 
          v_org_id0 := v_working_coex.org_id; 
          v_count := v_count + 1; 
        END; 
      WHEN 1 THEN 
        BEGIN 
          v_pos_id1 := v_working_coex.pos_id; 
          v_org_id1 := v_working_coex.org_id; 
          v_count := v_count + 1; 
        END; 
      WHEN 2 THEN 
        BEGIN 
          v_pos_id2 := v_working_coex.pos_id; 
          v_org_id2 := v_working_coex.org_id; 
          v_count := v_count + 1; 
        END; 
      END CASE; 
    END LOOP; 
    CLOSE working_coex_cur; 
    INSERT INTO hu_tmp_emp_working_full 
                ( 
                            employee_id, 
                            cif, 
                            vn_fullname, 
                            gender, 
                            birth_date, 
                            marital_status, 
                            religion, 
                            nationality, 
                            cur_address, 
                            per_address, 
                            id, 
                            id_no, 
                            id_date, 
                            id_place, 
                            work_status, 
                            e ffect_date, 
                            applied_by, 
                            bank, 
                            bank_code, 
                            account_no, 
                            mobile, 
                            tel, 
                            work_tel, 
                            email1, 
                            email2, 
                            join_date, 
                            doituong_id, 
                            wend_date, 
                            contract_type, 
                            cend_ DATE, 
                            academy_lv, 
                            specialize, 
                            location, 
                            pos_id, 
                            org_id, 
                            org_id_tt, 
                            pos_id0, 
                            org_id0, 
                            pos_id1, 
                            org_id1, 
                            pos_id2, 
                            org_id2, 
                            created_by, 
                            created_date 
                ) 
                VALUES 
                ( 
                            v_employee.employee_id, 
                            v_employee.cif, 
                            v_employee.vn_fullname, 
                            v_employee.gender, 
                            v_employee.birth_date, 
                            v_employee.marital_status, 
                            v_emp loyee.religion, 
                            v_employee.nationality, 
                            v_employee.cur_address, 
                            v_employee.per_address, 
                            v_employee.id, 
                            v_employee.id_no, 
                            v_employee.id_date , 
                            v_employee.id_place, 
                            v_employee.work_status, 
                            v_employee.effect_date, 
                            v_employee.applied_by, 
                            v_employee.bank, 
                            v_employee.bank_code, 
                            v_emplo yee.account_no, 
                            v_employee.mobile, 
                            v_employee.tel, 
                            v_employee.work_tel, 
                            v_employee.email1, 
                            v_employee.email2, 
                            v_employee.join_date, 
                            v_employ ee.doituong_id, 
                            v_employee.wend_date, 
                            v_employee.contract_type, 
                            v_employee.cend_date, 
                            v_employee.academy_lv, 
                            v_employee.specialize, 
                            v_emplo yee.location, 
                            v_employee.pos_id, 
                            v_employee.org_id, 
                            v_employee.org_id_tt, 
                            v_pos_id0, 
                            v_org_id0, 
                            v_pos_id1, 
                            v_org_id1, 
                            v_pos_id2, 
                            v_org_id2, 
                            username, 
                            SYSDATE 
                ); 
   
  END LOOP; 
  CLOSE employee_cur; 
  COMMIT; 
END;