|
SELECT
(
SELECT
PARTY_VALUE
FROM
T_TRANSFORM_DICT ttd5
WHERE
HRS_TYPE = ‘view_zdxzzw‘
AND HRS_VALUE = ZYJSZW.ZYJSZWJBM
) AS POST_RANK,
(
SELECT
PARTY_VALUE
FROM
T_TRANSFORM_DICT ttd5
WHERE
HRS_TYPE = ‘view_zyjszw‘
AND HRS_VALUE = ZYJSZW.RZZGMCM
) AS RZZGMCM,
ZYJSZW.PRQSRQ AS PRQSRQ,
cs.DUTIES AS xrzw,
cs.TENURE_YEAR || NVL2 (cs.TENURE_YEAR,‘-‘,‘‘) || cs.TENURE_MONTH || NVL2 (cs.TENURE_MONTH,‘‘) || cs.TENURE_DAY AS RENZHISJ,
gbxx.RTJSJ,
qp.PRESERVE01,
(
CASE
WHEN qp.QUFEN_FLAG = ‘1‘ THEN
‘专职党政‘
WHEN qp.QUFEN_FLAG = ‘2‘ THEN
‘双肩挑‘
ELSE
‘‘
END
) AS QUFEN_FLAG,
QP.CREATE_DATE,
QP.QUASI_ID,
QP.DEL_FLAG,
QP.LS,
QP.HYTJ_ID,
QP.NUM_ID AS HXMD_NUM_ID,
RP.ZGH,
RP.NUM_ID AS PER_NUM_ID,
RP.XM,
RP.JGM,
(
SELECT
PARTY_VALUE
FROM
T_TRANSFORM_DICT
WHERE
HRS_TYPE = ‘view_xb‘
AND HRS_VALUE = rp.XBM
) AS XB,
rp.csrq,
TRUNC (
MONTHS_BETWEEN (SYSDATE,CSRQ) / 12
) AS AGE,
(
SELECT
PARTY_VALUE
FROM
T_TRANSFORM_DICT
WHERE
HRS_TYPE = ‘view_zzmm‘
AND HRS_VALUE = zp.POLITICAL
) AS zzmm,‘‘) || cs.TENURE_DAY AS TENURE,
rp.num_id AS emp_id,
(
SELECT
PARTY_VALUE
FROM
T_TRANSFORM_DICT
WHERE
HRS_TYPE = ‘view_mz‘
AND HRS_VALUE = rp.MZM
) AS MZMV,
(
SELECT
PARTY_VALUE
FROM
T_TRANSFORM_DICT
WHERE
HRS_TYPE = ‘view_xzdq‘
AND HRS_VALUE = rp.JGM
) AS JGMV,
(
SELECT
PARTY_VALUE
FROM
T_TRANSFORM_DICT
WHERE
HRS_TYPE = ‘view_zyjszw‘
AND HRS_VALUE = zp.POLITICAL
) AS ZYJSZC,
ao. NAME AS DWHN,
(
SELECT
listagg (
TAO. NAME || CASE
WHEN NVL (TAO. NAME,NULL) IS NULL
OR NVL (TCS.DUTIES,NULL) IS NULL THEN
‘‘
ELSE
‘‘
END || TCS.DUTIES,
‘，‘
) WITHIN GROUP (ORDER BY TCS.SERIAL_NUMBER) DUTIES FROM T_CADRE_SELECTION TCS LEFT JOIN T_ADMIN_ORG TAO ON TCS.TENURE_UNIT = TAO.NUM_ID WHERE TCS.base_info_id = rp.NUM_ID AND TCS.DEL_FLAG = ‘0‘ AND Tcs.INCUMBENT = ‘1‘ ) AS DUTIES,zp.JOIN_DATE rdrq,( SELECT PARTY_VALUE FROM T_TRANSFORM_DICT WHERE HRS_TYPE = ‘view_xl‘ AND HRS_VALUE = XXJL.XLM ) AS ZGXL,( SELECT PARTY_VALUE FROM T_TRANSFORM_DICT WHERE HRS_TYPE = ‘view_xw‘ AND HRS_VALUE = XXJL1.HDXWM ) AS ZGXW,( SELECT listagg ( TO_CHAR (tab.RXNY,‘yyyy-mm‘) || CASE WHEN tab.RXNY IS NULL THEN ‘‘ ELSE ‘ ~ ‘ END || TO_CHAR (tab.JSXYNY,‘yyyy-mm‘) || ‘ ‘ || tab.BSYXXHDW || ‘ ‘ || tab.HXWZYM_NAME || ‘ ‘ || tab.XLM_NAME,CHR (10) ) WITHIN GROUP (ORDER BY tab.BSYXXHDW) FROM ( SELECT XXJL.*,( SELECT label FROM SYS_DICT WHERE TYPE = ‘SXZY‘ AND VALUE = XXJL.HXWZYM ) AS HXWZYM_NAME,( SELECT sd.label FROM SYS_DICT sd,T_TRANSFORM_DICT ttd WHERE sd. TYPE = ttd.PARTY_TYPE AND sd. VALUE = ttd.PARTY_VALUE AND ttd.HRS_TYPE = ‘view_xl‘ AND ttd.HRS_VALUE = XXJL.XLM ) AS XLM_NAME,XXJL.JYLBM AS JYLBM_VALUE,-- 教育类别 ( SELECT PARTY_VALUE FROM T_TRANSFORM_DICT WHERE HRS_TYPE = ‘view_xl‘ AND HRS_VALUE = XXJL.XLM ) AS XLM_VALUE,-- 学历 ( SELECT PARTY_VALUE FROM T_TRANSFORM_DICT WHERE HRS_TYPE = ‘view_xw‘ AND HRS_VALUE = XXJL.HDXWM ) AS HDXWM_VALUE,-- 学位 XXJL.SXWGJDQM AS SXWGJDQM_VALUE,( SELECT label FROM SYS_DICT WHERE TYPE = ‘XXXS‘ AND VALUE = XXJL.XXXSM ) AS XXXSM_NAME FROM V_MID_RS_XXJL_PARTY XXJL LEFT JOIN V_MID_RS_PARTY RS ON RS.ZGH = XXJL.ZGH WHERE xxjl.zgh = qp.QUASI_ID ) tab ) AS xxjlaa,( SELECT listagg ( TO_CHAR (cad.gzqsrq,‘yyyy-mm‘) || CASE WHEN cad.gzqsrq IS NULL THEN ‘‘ ELSE ‘ ~ ‘ END || TO_CHAR (cad.GZZZRQ,‘yyyy-mm‘) || ‘ ‘ || cad.GZDW || ‘ ‘ -- 如果专业技术职务码有值 || CASE WHEN cad.zyjszwm IS NULL THEN cad.dzzw -- 如果没值显示党政职务 ELSE SD.LABEL END,CHR (10) ) WITHIN GROUP (ORDER BY cad.ZGH) AS qqq FROM T_MID_RS_GZJL_PARTY cad LEFT JOIN SYS_DICT SD ON SD. VALUE = cad.zyjszwm AND SD. TYPE = ‘ZYJSZW‘ WHERE cad.zgh = qp.QUASI_ID ) GZDWFROM T_CADRE_HYTJ_HXMD qpINNER JOIN V_MID_RS_PARTY rp ON QP.QUASI_ID = RP.ZGHLEFT JOIN T_ADMIN_ORG ao ON ao.NUM_ID = rp.DWHLEFT JOIN ( SELECT * FROM T_EMPLOYEE_BASE_INFO ZZMM INNER JOIN T_P_BASE_INFO TPBI ON TPBI.EMP_ID = ZZMM.NUM_ID AND TPBI.HISTORY_FLAG = ‘0‘ WHERE ZZMM.STAFF_TYPE = ‘0‘) zp ON zp.EMPLOYEE_CODE = rp.ZGHLEFT JOIN T_CADRE_SELECTION cs ON cs.EMP_ID = qp.QUASI_IDAND cs.DEL_FLAG = ‘0‘AND SERIAL_NUMBER = ‘1‘LEFT JOIN T_CADRE_USER_INFO gbxx ON gbxx.BASE_INFO_ID = cs.BASE_INFO_IDAND gbxx.FORMAL = ‘0‘AND gbxx.HISTORICAL_CADRES = ‘0‘AND gbxx.DEL_FLAG = ‘0‘ --LEFT JOIN T_MID_RS_ZZMM_PARTY ZZMM ON qp.QUASI_ID = ZZMM.ZGH AND ZZMM.SFDQ = ‘1‘LEFT JOIN V_MID_RS_ZYJSZW_PARTY ZYJSZW ON ZYJSZW.ZGH = qp.QUASI_IDAND ZYJSZW.SFDQ = ‘1‘LEFT JOIN V_MID_RS_XXJL_PARTY XXJL ON XXJL.ZGH = qp.QUASI_IDAND XXJL.SFZGXL = ‘1‘LEFT JOIN V_MID_RS_XXJL_PARTY XXJL1 ON XXJL1.ZGH = qp.QUASI_IDAND XXJL1.SFZGXW = ‘1‘
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
