查询人员所属组，以及查询组对应的上一级组代码,OracleEBS代码库,德仔网

代码语言

代码分类

【OracleEBS】查询人员所属组，以及查询组对应的上一级组

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

查询人员所属组，以及查询组对应的上一级组

<div>
select rgv.GROUP_ID ,rgv.GROUP_NAME  ,rgv.RELATED_GROUP_ID,rgv.RELATED_GROUP_NAME
from(
select row_number()  over(PARTITION BY GROUP_ID  ORDER BY  fSign desc ) rn   ,rg.*
from(
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 

,case  when sysdate between b.start_date_active and  nvl(b.end_date_active,sysdate) then 1 else 0 end fb
,case  when  sysdate between a.START_DATE_ACTIVE and nvl(a.end_date_active,sysdate) then 1  else 0 end fa
,case  when sysdate between c.START_DATE_ACTIVE  and nvl(c.end_date_active,sysdate) then 1 else 0 end fc         
,nvl((case when nvl(b.delete_flag,'N')='N' then 1 else 0 end ),0) fbd     

,case  when sysdate between  b.start_date_active and nvl(b.end_date_active,sysdate) then 1 else 0 end  
+case  when sysdate between a.START_DATE_ACTIVE and  nvl(a.end_date_active,sysdate) then 1 else 0 end 
+case  when  sysdate between c.START_DATE_ACTIVE and nvl(c.end_date_active,sysdate) then 1  else 0 end          
+nvl((case when nvl(b.delete_flag,'N')='N'  then 1 else 0 end ),0) fsign    

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'    
) rg  
) rgv where  rn=1



select SOURCE_LAST_NAME,group_name
from(
select  SOURCE_LAST_NAME,group_name
,row_number() over(PARTITION BY  SOURCE_LAST_NAME  ORDER BY  fSign desc ) rn   
from(
select distinct rds.RESOURCE_NAME , rds.SOURCE_LAST_NAME, rdg.group_name  --,rdr.role_name(角色会重复)
,case  when sysdate between  rds.START_DATE_ACTIVE and nvl(rds.END_DATE_ACTIVE,sysdate) then 1 else 0 end  fRDS
,case  when sysdate between rdg.group_start_date and  nvl(rdg.group_end_date,sysdate) then 1 else 0 end fRDG
,case  when  sysdate between rdr.RES_RL_START_DATE and nvl(rdr.RES_RL_END_DATE,sysdate) then  1 else 0 end fRDR     
,rdg.delete_flag fRDG_del
,rdr.delete_flag fRDR_del

,nvl((case  when sysdate  between rds.START_DATE_ACTIVE and nvl(rds.END_DATE_ACTIVE,sysdate) then 1 else 0  end),0)    
+nvl((case  when sysdate between rdg.group_start_date and  nvl(rdg.group_end_date,sysdate) then 1 else 0 end),0)    
+nvl((case   when sysdate between rdr.RES_RL_START_DATE and nvl(rdr.RES_RL_END_DATE,sysdate)  then 1 else 0 end),0)                   
+nvl((case when  nvl(rdg.delete_flag,'N')='N' then 1 else 0 end ),0)      
+nvl((case  when nvl(rdr.delete_flag,'N')='N' then 1 else 0 end ),0) fSign     

from JTF_RS_DEFRESOURCES_VL  rds
,jtf_rs_defresgroups_vl rdg
,jtf_rs_defresroles_vl rdr
where rds.RESOURCE_ID=rdg.RESOURCE_ID
and  rdg.GROUP_MEMBER_ID=rdr.role_resource_id
) pg
)pgv 
where  pgv.rn=1
</div>

试试其它关键字

　查询人员所属组　

同语言下

可能有用的

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