SAP HR 获取人员信息

人员信息"/>

SAP HR 获取人员信息

SELECT DISTINCT  p1.PERNR ,p1.ENAME ,p1.ORGEH,p2.USRID AS telno,p1.PLANS,t1.PLSTX,po.USRID  AS OAID ,p3.ICNUM ,CASE WHEN p4.GESCH = '1' THEN '男' ELSE '女' END  AS sexinfo
FROM PA0001 p1 LEFT JOIN PA0105 p2 ON p2.PERNR  = p1.PERNR AND p2.SUBTY = '1002' AND p2.BEGDA < to_char(SYSDATE,'YYYYMMDD') AND p2.ENDDA > to_char(SYSDATE,'YYYYMMDD') 
LEFT JOIN T528T t1 ON t1.OTYPE = 'S' AND t1.PLANS =  p1.PLANS  AND t1.BEGDA  <= to_char(SYSDATE,'YYYYMMDD')  AND t1.ENDDA >= to_char(SYSDATE,'YYYYMMDD') AND t1.MANDT = p1.MANDT  AND t1.SPRSL  = '1' 
LEFT JOIN Pa0105 po ON po.PERNR  = p1.PERNR  AND po.SUBTY  = '1008' AND po.BEGDA  <= to_char(SYSDATE,'YYYYMMDD')  AND po.ENDDA >= to_char(SYSDATE,'YYYYMMDD')
LEFT join PA0185 p3 ON p3.pernr = p1.pernr AND p3.BEGDA <= to_char(SYSDATE,'YYYYMMDD')  AND p3.ENDDA >= to_char(SYSDATE,'YYYYMMDD')
LEFT JOIN Pa0002 p4 ON p4.pernr = p1.pernr AND p4.BEGDA <= to_char(SYSDATE,'YYYYMMDD')  AND p4.ENDDA >= to_char(SYSDATE,'YYYYMMDD')
WHERE p1.BEGDA < to_char(SYSDATE,'YYYYMMDD') AND p1.ENDDA > to_char(SYSDATE,'YYYYMMDD') 
AND p1.ORGEH <> '00000000' AND p1.pernr  IN (SELECT pernr FROM PA0000 WHERE  MASSN NOT IN ('Z7','Z8')  
AND BEGDA < to_char(SYSDATE,'YYYYMMDD') AND ENDDA > to_char(SYSDATE,'YYYYMMDD')) ORDER BY p1.PERNR