data in DB

create or replace procedure EIS_VALIDATE_DB_DATA(out_success out varchar2,
                                                 out_err_msg out varchar2) is

  G_NEW_LINE VARCHAR2(5) := CHR(13);

  G_NUM_AM number := 1;

  G_NUM_SM number := 1;

  G_ID_SM number := 5;

  G_ID_AM number := 4;

  G_ID_PM number := 3;

  v_num_check_1 number default 0;
  v_num_check_2 number default 0;
  v_num_check_3 number default 0;

begin
  out_success := '1';
  -----------------------everyone in EIS should has projects at leaest!!! very important-----------------

  select count(1)
    into v_num_check_1
    from eis_users eu
   WHERE not exists
   (select 'X' from EIS_USER_PROJECTS EUP WHERE EUP.EMP_ID = EU.ID);

  if v_num_check_1 <> 0 then
    out_err_msg := out_err_msg ||
                   ' - Some resource in EIS never in any project' ||
                   G_NEW_LINE;
  end if;

 

  ---------------------------------AM SM can only join project id :0----------------------------------------

  select count(1)
    into v_num_check_1
    from eis_users eu, eis_user_projects eup
   where eu.id = eup.id
     and eu.roles_id in (G_ID_SM, G_ID_AM)
     and eup.project_id <> 0;
     
  if v_num_check_1 <> 0 then
      out_err_msg := out_err_msg ||
                   ' - Some AM or SM join NON-Dummy Projects' ||
                   G_NEW_LINE;
  end if;

  -----------------------There shoud be only 1 pending approve join/relese request for each one----------------
  select count(1)
    into v_num_check_1
    from (select 1
            from eis_project_requests epr
           where epr.request_status_id = 2
           group by epr.requestor_emp_id
          having count(1) > 1);

  if v_num_check_1 <> 0 then
    out_err_msg := out_err_msg ||
                   ' - Some resource have more than one  1 pending approve join/relese request' ||
                   G_NEW_LINE;
  end if;

  -------------------------other check!!--------------------------------------------------------
  

  -----end all validation-----------------------------------
  if out_err_msg is not null then
    out_success := 2;
  end if;

EXCEPTION
  WHEN OTHERS THEN

    out_err_msg := out_err_msg || G_NEW_LINE || G_NEW_LINE || SQLERRM ||
                   ': Procedure Unknown error  ';

    out_success := '0';

end EIS_VALIDATE_DB_DATA;