查询人员及部门的语句

 

查询人员的视图

select * from PER_PEOPLE_V7

 

查询人员对应的部门及隶属部门

CREATE OR REPLACE VIEW SECOM_SVREG_QUERY_SALES AS
select rgm.PERSON_ID , ppv.full_name person_name -- rgm.RESOURCE_NAME person_name
  ,rgm.GROUP_ID,rgv.GROUP_NAME
  ,pgn.related_group_id dept_id,pgn.related_group_name dept_name
 from jtf_rs_group_members_vl rgm
 ,JTF_RS_DEFRESOURCES_VL rds    
 ,jtf_rs_groups_vl rgv  
 ,(--查询父组
  SELECT B.GROUP_RELATE_ID,
         B.GROUP_ID,
         A.GROUP_NAME,
         B.RELATED_GROUP_ID,
         C.GROUP_NUMBER RELATED_GROUP_NUMBER,
         C.GROUP_NAME RELATED_GROUP_NAME,
         B.RELATION_TYPE,
         D.MEANING RELATION_TYPE_NAME,
         B.START_DATE_ACTIVE,
         B.END_DATE_ACTIVE,
         B.DELETE_FLAG,
         B.OBJECT_VERSION_NUMBER,
         B.CREATED_BY,
         B.CREATION_DATE,
         B.LAST_UPDATED_BY,
         B.LAST_UPDATE_DATE,
         B.LAST_UPDATE_LOGIN
    from JTF_RS_GRP_RELATIONS B,
         JTF_RS_GROUPS_VL     A,
         JTF_RS_GROUPS_VL     C,
         fnd_lookups          d
   WHERE a.group_id = b.group_id
     and c.group_id = b.related_group_id
     and b.relation_type = d.lookup_code
     and d.lookup_type = 'JTF_RS_RELATION_TYPE'  
     and  sysdate between b.start_date_active and nvl(b.end_date_active,sysdate)     
 ) pgn
 ,PER_PEOPLE_V7 ppv  
 where rgm.RESOURCE_ID=rds.RESOURCE_ID(+)
 and rgm.GROUP_ID=rgv.GROUP_ID(+)  
 and rgm.GROUP_ID=pgn.GROUP_ID(+)
 and rgm.PERSON_ID=ppv.person_id(+)
 and  sysdate between rgv.START_DATE_ACTIVE and nvl(rgv.END_DATE_ACTIVE,sysdate)
 and  sysdate between rds.START_DATE_ACTIVE and nvl(rds.END_DATE_ACTIVE,sysdate)
 and  nvl(rgm.DELETE_FLAG,'N')='N'