17,086
社区成员
发帖
与我相关
我的任务
分享
SELECT B.TREE_NODE
, SUM(CASE WHEN A.EMPL_CLASS IN ('001'
, '009'
, '002'
, '003'
, '004'
, '005'
, '006'
, '007'
, '008'
, '010'
, '011'
, '016'
, '017'
, '018'
, '019'
, '020') THEN 1 ELSE 0 END)
, SUM(CASE WHEN A.EMPL_CLASS IN ('001'
, '009'
, '008'
, '010'
, '011'
, '016'
, '017'
, '018'
, '019'
, '020') THEN 1 ELSE 0 END)
, SUM(CASE WHEN A.EMPL_CLASS IN ('001'
,'009') THEN 1 ELSE 0 END)
, SUM(CASE WHEN A.EMPL_CLASS IN ('009') THEN 1 ELSE 0 END)
, SUM(CASE WHEN A.EMPL_CLASS IN ('008'
, '010'
, '011'
, '016'
, '017') THEN 1 ELSE 0 END)
, SUM(CASE WHEN A.EMPL_CLASS IN ('008') THEN 1 ELSE 0 END)
, SUM(CASE WHEN A.EMPL_CLASS IN ('011') THEN 1 ELSE 0 END)
, SUM(CASE WHEN A.EMPL_CLASS IN ('017') THEN 1 ELSE 0 END)
, SUM(CASE WHEN A.EMPL_CLASS IN ('018'
, '019'
, '020') THEN 1 ELSE 0 END)
, SUM(CASE WHEN A.EMPL_CLASS IN ('002'
, '003'
, '004'
, '005'
, '006'
, '007') THEN 1 ELSE 0 END)
, SUM(CASE WHEN A.EMPL_CLASS IN ('002') THEN 1 ELSE 0 END)
, SUM(CASE WHEN A.EMPL_CLASS IN ('004') THEN 1 ELSE 0 END)
, SUM(CASE WHEN A.EMPL_CLASS IN ('003') THEN 1 ELSE 0 END)
, SUM(CASE WHEN A.EMPL_CLASS IN ('006') THEN 1 ELSE 0 END)
, SUM(CASE WHEN A.EMPL_CLASS IN ('005'
,'007') THEN 1 ELSE 0 END)
, SUM(CASE WHEN E.SPG_POS_LEV_ID IN ('01'
, '02' )
AND D.SPG_COMP2_LEVEL = '01'
AND a.EMPL_CLASS IN ('001'
, '009'
, '008'
, '010'
, '011'
, '016'
, '017'
, '018'
, '019'
, '020') THEN 1 ELSE 0 END)
, SUM(CASE WHEN E.SPG_POS_LEV_ID IN ('03'
,'04'
, '05')
AND D.SPG_COMP2_LEVEL = '01'
AND a.EMPL_CLASS IN ('001'
, '009'
, '008'
, '010'
, '011'
, '016'
, '017'
, '018'
, '019'
, '020') THEN 1 ELSE 0 END)
, SUM(CASE WHEN E.SPG_POS_LEV_ID IN ('01'
, '02' )
AND D.SPG_COMP2_LEVEL = '02'
AND a.EMPL_CLASS IN ('001'
, '009'
, '008'
, '010'
, '011'
, '016'
, '017'
, '018'
, '019'
, '020') THEN 1 ELSE 0 END)
, SUM(CASE WHEN E.SPG_POS_LEV_ID IN ('03'
,'04'
, '05')
AND D.SPG_COMP2_LEVEL = '02'
AND a.EMPL_CLASS IN ('001'
, '009'
, '008'
, '010'
, '011'
, '016'
, '017'
, '018'
, '019'
, '020') THEN 1 ELSE 0 END)
, SUM(CASE WHEN E.SPG_POS_LEV_ID IN ('01'
, '02' )
AND D.SPG_COMP2_LEVEL = '03'
AND a.EMPL_CLASS IN ('001'
, '009'
, '008'
, '010'
, '011'
, '016'
, '017'
, '018'
, '019'
, '020') THEN 1 ELSE 0 END)
, SUM(CASE WHEN E.SPG_POS_LEV_ID IN ('03'
,'04'
, '05')
AND D.SPG_COMP2_LEVEL = '03'
AND a.EMPL_CLASS IN ('001'
, '009'
, '008'
, '010'
, '011'
, '016'
, '017'
, '018'
, '019'
, '020') THEN 1 ELSE 0 END)
, SUM(CASE WHEN E.SPG_POS_LEV_ID IN ('01'
, '02' )
AND D.SPG_COMP2_LEVEL = '04'
AND a.EMPL_CLASS IN ('001'
, '009'
, '008'
, '010'
, '011'
, '016'
, '017'
, '018'
, '019'
, '020') THEN 1 ELSE 0 END)
, SUM(CASE WHEN E.SPG_POS_LEV_ID IN ('03'
,'04'
, '05')
AND D.SPG_COMP2_LEVEL = '04'
AND a.EMPL_CLASS IN ('001'
, '009'
, '008'
, '010'
, '011'
, '016'
, '017'
, '018'
, '019'
, '020') THEN 1 ELSE 0 END)
, SUM(CASE WHEN E.SPG_POS_LEV_ID NOT IN ('01'
, '02'
, '03'
, '04'
, '05')
AND a.EMPL_CLASS IN ('001'
, '009'
, '008'
, '010'
, '011'
, '016'
, '017'
, '018'
, '019'
, '020') THEN 1 ELSE 0 END)
FROM PS_JOB A
, PS_SPH_FOR_DEPT_TL B
, PS_DEPT_TBL C
, PS_COMPANY_TBL D
, PS_JOBCODE_TBL E
WHERE A.EFFDT = (
SELECT MAX(A1.EFFDT)
FROM PS_JOB A1
WHERE A1.EMPLID = A.EMPLID
AND A1.EMPL_RCD = A.EMPL_RCD
AND A1.EFFDT <= :1)
AND A.EFFSEQ = (
SELECT MAX(A2.EFFSEQ)
FROM PS_JOB A2
WHERE A2.EMPLID = A.EMPLID
AND A2.EMPL_RCD = A.EMPL_RCD
AND A2.EFFDT = A.EFFDT)
AND A.DEPTID = B.TREE_NODE_CHILD
AND (B.SPG_DEPT_CLASS = '01'
OR B.SPG_DEPT_CLASS = '18')
AND A.JOB_INDICATOR = 'P'
AND A.HR_STATUS = 'A'
AND A.BUSINESS_UNIT = 'BU100'
AND B.SETID = C.SETID
AND B.TREE_NODE = C.DEPTID
AND C.EFFDT = (
SELECT MAX(C1.EFFDT)
FROM PS_DEPT_TBL C1
WHERE C1.SETID = C.SETID
AND C1.DEPTID = C.DEPTID
AND C1.EFFDT <= :1)
AND D.EFFDT = (
SELECT MAX(D1.EFFDT)
FROM PS_COMPANY_TBL D1
WHERE D1.COMPANY = D.COMPANY
AND D1.EFFDT <= :1)
AND E.EFFDT = (
SELECT MAX(E1.EFFDT)
FROM Ps_Jobcode_Tbl E1
WHERE E1.setid = E.setid
AND E1.jobcode = E.jobcode
AND E1.EFFDT <= :1)
AND A.COMPANY = D.COMPANY
AND A.JOBCODE = E.JOBCODE
AND EXISTS (
SELECT 1
FROM PS_SPH_DEPT_TREE M
WHERE M.SPH_PARENT_NODE = :3
AND B.TREE_NODE = DECODE(:2, 'Y', M.SPH_CHILD_NODE, :3)
AND (M.SPH_CHILDNOD_CLASS = '01'
OR M.SPH_CHILDNOD_CLASS = '18'))
GROUP BY B.TREE_NODE, C.SPG_SORT_COMTREE
ORDER BY C.SPG_SORT_COMTREE
WHERE NOT EXISTS ( SELECT 1 FROM PS_JOB
WHERE EMPLID = A.EMPLID AND EMPL_RCD = A.EMPL_RCD
AND ((EFFDT <= :1 AND EFFDT>A.EFFDT)
OR (EFFDT = A.EFFDT AND EFFSEQ>A.EFFSEQ))