查询人员对应的部门及隶属部门代码,OracleEBS代码库,德仔网

代码语言

代码分类

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

作者:Dezai.CN / 发布于2012/8/28/ 1003

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

<div>
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'
</div>

试试其它关键字

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

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)