select a.agentcode 工号,
a.name 姓名,
(select codename
from ldcode
where codetype = 'sex'
and code = a.sex) 性别,
(select codename
from ldcode
where codetype = 'degree'
and code = a.degree) 学历,
a.birthday 出生日期,
b.Initgrade 入职职级,
b.agentgrade 现任职级,
to_char(b.startdate, 'yyyymm') 任职级起始年月,
(select c.f5
from (select *
from lamucalagentlink
where flag = '3'
order by makedate desc, maketime desc) c
where 1 = 1
and c.agentcode = a.agentcode
and rownum = 1) 专案类型,
(select c.code
from (select *
from lamucalagentlink
where flag = '3'
order by makedate desc, maketime desc) c
where 1 = 1
and c.agentcode = a.agentcode
and rownum = 1) 专案档次,
(case
when a.smokeflag is null or a.smokeflag = '' then
'否'
else
'是'
end) 是否军官班人员,
a.smokeflag 军官班类型,
(select c.startmonth
from (select *
from lamucalagentlink
where flag = '3'
order by makedate desc, maketime desc) c
where 1 = 1
and c.agentcode = a.agentcode
and rownum = 1) 专案起始月份,
(select c.state
from (select *
from lamucalagentlink
where flag = '3'
order by makedate desc, maketime desc) c
where 1 = 1
and c.agentcode = a.agentcode
and rownum = 1) 专案状态,
a.managecom 管理机构,
(select d.branchattr
from labranchgroup d
where d.agentgroup = a.agentgroup) 营销服务部,
e.statcode 营业区代码,
e.groupcode 团队编码,
(select f.codename
from ldcode f
where f.codetype = 'lastatarea'
and f.code = e.statcode) 营业区名称,
(case
when a.agentstate in ('01', '02') then
'A'
else
'T'
end) 状态,
a.employdate 入职日期,
a.outworkdate 离职日期,
a.OldOccupation 工作月份,
a.agentkind 代理人类型，
(select ragentcode
from larelation
where relatype = '03'
and agentcode = a.agentcode) 增员人工号,
(select (case
when agentstate in ('01', '02') then
'A'
else
'T'
end)
from laagent
where agentcode = ((select ragentcode
from larelation
where relatype = '03'
and agentcode = a.agentcode))) 增员者状态,
(select ragentcode
from larelation
where relatype = '04'
and agentcode = a.agentcode) 育成人工号,
(SELECT NAME
FROM laagent
WHERE agentcode = (select ragentcode
from larelation
where relatype = '04'
and agentcode = a.agentcode)) 育成人姓名,
(SELECT ragentgrade
from larelation
where relatype = '04'
and agentcode = a.agentcode) 育成人职级,
(select codename
from ldcode
where codetype = 'agentstate'
and code = (SELECT agentstate
FROM laagent
WHERE agentcode =
(SELECT ragentcode
from larelation
where relatype = '04'
and agentcode = a.agentcode))) 育成人状态,
(case
when b.agentgrade = 'AS' then
b.agentcode
else
(select ragentcode
from larelation
where agentcode = a.agentcode
and relatype = '01'
and state = '11'
and branchtype = a.branchtype
and branchtype2 = a.branchtype2
and ragentgrade = 'AS')
end) AS 工号,
(case
when b.agentgrade = 'AS' then
a.name
else
(select name
from laagent
where agentcode = (select ragentcode
from larelation
where agentcode = a.agentcode
and relatype = '01'
and state = '11'
and branchtype = a.branchtype
and branchtype2 = a.branchtype2
and ragentgrade = 'AS'))
end) AS 姓名,
(case
when b.agentgrade = 'SAS' then
b.agentcode
else
(select ragentcode
from larelation
where agentcode = a.agentcode
and relatype = '01'
and state = '11'
and branchtype = a.branchtype
and branchtype2 = a.branchtype2
and ragentgrade = 'SAS')
end) SAS工号,
(case
when b.agentgrade = 'SAS' then
a.name
else
(select name
from laagent
where agentcode = (select ragentcode
from larelation
where agentcode = a.agentcode
and relatype = '01'
and state = '11'
and branchtype = a.branchtype
and branchtype2 = a.branchtype2
and ragentgrade = 'SAS'))
end) SAS姓名,
(case
when b.agentgrade = 'BM' then
b.agentcode
else
(select ragentcode
from larelation
where agentcode = a.agentcode
and relatype = '01'
and state = '11'
and branchtype = a.branchtype
and branchtype2 = a.branchtype2
and ragentgrade = 'BM')
end) BM工号,
(case
when b.agentgrade = 'BM' then
a.name
else
(select name
from laagent
where agentcode = (select ragentcode
from larelation
where agentcode = a.agentcode
and relatype = '01'
and state = '11'
and branchtype = a.branchtype
and branchtype2 = a.branchtype2
and ragentgrade = 'BM'))
end) BM姓名,
(case
when b.agentgrade = 'SBM' then
b.agentcode
else
(select ragentcode
from larelation
where agentcode = a.agentcode
and relatype = '01'
and state = '11'
and branchtype = a.branchtype
and branchtype2 = a.branchtype2
and ragentgrade = 'SBM')
end) SBM工号,
(case
when b.agentgrade = 'SBM' then
a.name
else
(select name
from laagent
where agentcode = (select ragentcode
from larelation
where agentcode = a.agentcode
and relatype = '01'
and state = '11'
and branchtype = a.branchtype
and branchtype2 = a.branchtype2
and ragentgrade = 'SBM'))
end) SBM姓名,
(case
when b.agentgrade = 'AD' then
b.agentcode
else
(select ragentcode
from larelation
where agentcode = a.agentcode
and relatype = '01'
and state = '11'
and branchtype = a.branchtype
and branchtype2 = a.branchtype2
and ragentgrade = 'AD')
end) AD工号,
(case
when b.agentgrade = 'AD' then
a.name
else
(select name
from laagent
where agentcode = (select ragentcode
from larelation
where agentcode = a.agentcode
and relatype = '01'
and state = '11'
and branchtype = a.branchtype
and branchtype2 = a.branchtype2
and ragentgrade = 'AD'))
end) AD姓名,
(case
when a.agentkind is not null then
(select codename
from ldcode
where codetype = 'agtkind'
and code = a.agentkind)
else
'普通代理人'
end) 代理人类型,
decode(b.isconnman, '1', '是', '0', '否', '否') 是否阳光之星会员
from laagent a, latree b, lagroupmapping e
where 1 = 1
and a.branchtype = '1'
and a.branchtype2 = '01'
and b.agentgroup=e.GroupCode
and a.agentcode = b.agentcode