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
