JD Edwards - Get max sequence in role -security

select

distinct FSUSER,FSOBNM,RLTOROLE,AUSEQNO ,FSSETY,FSINSL,FSRUN into #temp_03

from

SY900.F00950

left

outer join SY900.F95921 on fsuser=RLFRROLE

left

outer join SY900.F00926 on auuser=fsuser

where

RLFRROLE is not null and FSUSER not in('ESS','MSS','SYSBLOCK','ERREQ') AND AUSEQNO is not null

AND

FSOBNM<>'' AND FSSETY='3'

order

by 1

select

distinct FSUSER,FSOBNM,RLTOROLE,AUSEQNO ,FSSETY,FSA,FSCHNG,FSDLT,FSTHDV,FSOBID,FSADDC,FSIOK,FSICPY

into

#temp_01

from

SY900.F00950

left

outer join SY900.F95921 on fsuser=RLFRROLE

left

outer join SY900.F00926 on auuser=fsuser

where

RLFRROLE is not null and FSUSER not in('ESS','MSS','SYSBLOCK','ERREQ') AND AUSEQNO is not null

AND

FSOBNM<>'' AND FSSETY='1'

order

by 1

--select * from #temp_01 a

--select * from #temp_03 a

select

a.* ,FSA,FSCHNG,FSDLT,FSTHDV,FSADDC,FSIOK,FSICPY into #temp4

from

#temp_03 a

left

outer join #temp_01 b on a.FSUSER=b.FSUSER and a.FSOBNM=b.FSOBNM and a.RLTOROLE=b.RLTOROLE

where

b.FSUSER is not null

insert into #temp4

select

a.* ,'Y' FSA,'Y'FSCHNG,'Y'FSDLT,'Y'FSTHDV,'Y'FSADDC,'Y'FSIOK,'Y'FSICPY

from

#temp_03 a

left

outer join #temp_01 b on a.FSUSER=b.FSUSER and a.FSOBNM=b.FSOBNM and a.RLTOROLE=b.RLTOROLE

where

b.FSUSER is null

select

FSUSER Roles

, FSOBNM Program, RLTOROLE Users, AUSEQNO SeqNo,FSINSL Install,FSRUN Run,FSA [Add],FSCHNG Change,

FSDLT Del

,FSIOK Ok,FSICPY Copy --into #temp5

from

#temp4 order by 3,2,1,4 desc