-- create view v_user
create view v_user as
SELECT `u`.`sysuserid`, `u`.`emp_code`,`u`.`first_name`,`u`.`last_name`,`u`.`username`, u.email_address, `u`.`password`, `u`.`role_id`,`r`.`role_name`, 
 if(org.parentunit <> 0  , org.parentunit, org.orgunitid ) as `orgunitid`,org.orgunitid as orgunitid_r ,if(org.parentunit <> 0  , (SELECT o.orgunitname from orgunit o WHERE o.orgunitid=org.parentunit),org.orgunitname) as `orgunitname` , org.parentunit, ptn.position_id, ptn.position_name,  `u`.`isdeleted`, `u`.`isinactive`, u.sys_type  
FROM sys_user u INNER JOIN tbl_sysrole r ON (r.role_id = u.role_id)
LEFT JOIN orgunit org ON (org.orgunitid = u.orgunitid) 
LEFT JOIN emp_position ptn ON(ptn.position_id = u.position_id);




