NC6.5人员信息工作记录

select t1.*,t2.现部门,t2.原部门,t2.现组织,t2.原组织,t2.调配方式,t2.调整原因,t2.调整类型,t2.日期 from 
(
select * from 
(
SELECT
    a.PK_PSNDOC AS "人员", 
    a.NAME AS "姓名", 
    --a.PK_ORG AS "组织", 
    b.name as "组织名称",
    a.CODE AS "编码", 
    a.SEX AS "性别", 
    a.ID AS "证件号码", 
    --a.MARITAL AS "婚姻状况", 
    --a.NATIONALITY AS "民族", 
    a.RETIREDATE AS "离退休日期", 
    --a.NATIVEPLACE AS "籍贯", 
    a.CENSUSADDR AS "户籍地址", 
    a.POLITY AS "政治面貌", 
    --a.EDU AS "学历", 
    NVL(c.name, '无') as "学历名称",
    --c.name as "学历名称",
    d.school as "毕业学校",
    d.major as "专业",
    --a.PK_DEGREE AS "学位", 
    --a.PROF AS "职业资格", 
    a.AGE AS "年龄", 
    a.WORKAGE AS "工作年龄",
    a.joinworkdate as "入职日期",
    d.begindate as "入学时间",
    e.enablestate  as "账号状态",
    g.name as "现任部门",
    h.jobname as "职务",
    ROW_NUMBER() OVER (PARTITION BY a.PK_PSNDOC ORDER BY d.begindate DESC)  行
FROM
    BD_PSNDOC a
left join org_orgs b on a.PK_ORG=b.PK_ORG
left join hi_psndoc_edu d on a.PK_PSNDOC = d.PK_PSNDOC
left join bd_defdoc c on d.EDUCATION = c.pk_defdoc
left join sm_user e on a.code=e.user_code
left join BD_PSNJOB f on a.code=f.psncode
left join org_dept g on f.pk_dept=g.pk_dept
left join om_job h on f.pk_job=h.pk_job

--where     a.MARITAL ='@@@@Z710000000005L1Y' and a.sex=1
) 
where 行=1 
--and 账号状态=2
) t1
left join 

(
select * from 
(
SELECT
        b.PK_PSNDOC,
    b.name 调配人员,
    c.user_name 制单人,
    a.apply_date 日期,
    CASE
            WHEN a.approve_state = '1'   THEN '审批通过'
            WHEN a.approve_state = '-1'  THEN '自由'
            WHEN a.approve_state = '3'   THEN '提交'
            WHEN a.approve_state = '2'    THEN '审批进行中'
            WHEN a.approve_state = '0'   THEN '审批未通过'
            WHEN a.approve_state = '102'  THEN '已执行'
        END
    AS SPZT,
    d.user_name 审批人,
    e.name 现部门,
    h.name 原部门,
    CASE
            WHEN A.stapply_mode = '1' THEN '组织内调配'
            WHEN A.STAPPLY_MODE = '2' THEN '调出'
            WHEN A.STAPPLY_MODE = '3' THEN '调入'
        END
    AS 调配方式,
        a.stapply_mode,
        a.MEMO 调整原因,
    f.name 现组织,
    g.name 原组织,
    a.pk_group 集团,
    i.trnstypename 调整类型,
    ROW_NUMBER() OVER (PARTITION BY b.PK_PSNDOC ORDER BY a.apply_date DESC)  行号
FROM
    hi_stapply a
    LEFT JOIN bd_psndoc b ON a.pk_psndoc = b.pk_psndoc
    LEFT JOIN sm_user c ON a.creator = c.cuserid
    LEFT JOIN sm_user d ON a.approver = d.cuserid
    LEFT JOIN org_dept e ON a.newpk_dept = e.pk_dept
    LEFT JOIN org_dept h ON a.oldpk_dept = h.pk_dept
    LEFT JOIN org_adminorg f ON a.newpk_org = f.pk_adminorg
    LEFT JOIN org_adminorg g ON a.oldpk_org = g.pk_adminorg
        left join HR_TRNSTYPE i on a.pk_trnstype = i.pk_trnstype


) 
where 行号=1
)t2 on t1.人员=t2.PK_PSNDOC

SELECT * FROM
(select DISTINCT a.begindate 开始时间,a.enddate 结束时间,a.clerkcode 员工号,a.endflag 是否结束,a.lastflag 最新记录,b.name 部门,c.name 人资组织,d.jobname 职务, f.postname 岗位名,g.name 人员,a.poststat 是否在岗,
ROW_NUMBER() OVER (PARTITION BY a.clerkcode ORDER BY A.begindate DESC)  行
from hi_psnjob a
left join org_dept b on a.pk_dept=b.pk_dept
left join org_hrorg c on a.pk_hrorg=c.pk_hrorg
left join om_job d on a.pk_job=d.pk_job
left join om_post f on a.pk_post = f.pk_post
left join bd_psndoc g on a.pk_psndoc= g.pk_psndoc 


where a.lastflag ='Y' AND A.POSTSTAT='Y' AND G.NAME IS NOT NULL

)
WHERE 行=1