Untitled
unknown
plain_text
3 months ago
8.4 kB
3
Indexable
create FUNCTION SDH_SO_QUYET_DINH_EXPORT(idSoQuyetDinh IN STRING) RETURN SYS_REFCURSOR AS my_cursor SYS_REFCURSOR; BEGIN OPEN my_cursor FOR WITH TCCB AS (SELECT CBNT.HO || ' ' || CBNT.TEN || '(' || 'CBNT' || ')' AS "hoTen", CAST(CBNT.SHCC AS VARCHAR2(20)) AS "shcc", DT.VIET_TAT AS "trinhDo", 'CBNT' AS "belongTo" FROM DT_CAN_BO_NGOAI_TRUONG CBNT LEFT JOIN DM_TRINH_DO DT ON DT.MA = CBNT.TRINH_DO UNION SELECT CB.HO || ' ' || CB.TEN AS "hoTen", CAST(CB.SHCC AS VARCHAR2(20)) AS "shcc", DT.VIET_TAT AS "trinhDo", CASE WHEN CB.LOAI_CAN_BO = 'TG' THEN 'CBNT' ELSE 'CBTT' END AS "belongTo" FROM TCHC_CAN_BO CB LEFT JOIN DM_TRINH_DO DT ON DT.MA = CB.HOC_VI WHERE CB.NGAY_NGHI IS NULL UNION SELECT SDHCB.HO || ' ' || SDHCB.TEN || '(' || 'CBSDH' || ')' AS "hoTen", CAST(SDHCB.ID AS VARCHAR2(20)) AS "shcc", TRINH_DO AS "trinhDo", 'CBSDH' AS "belongTo" FROM SDH_CAN_BO SDHCB) SELECT SQD.ID as "id", SQD.SO_QUYET_DINH as "soQuyetDinh", SQD.TEN as "tenQuyetDinh", SQD.ACTION as "moTaQuyetDinh", SV.HO || ' ' || SV.TEN as "hoTenSinhVien", SD.TEN_DE_TAI as "tenDeTai", SD.TEN_HOI_DONG as "tenHoiDong", SD.PHONG as "phong", SQD.TIME as "ngayRaQuyetDinh", RES."tenDonViGui" as "tenDonViGui", RES."ngayTao" as "ngayTao", RES."soCongVan" as "soCongVan", SD.NHOM_QUYET_DINH as "mode", regexp_replace(JSON_ARRAYAGG(to_clob(JSON_OBJECT( key TO_CHAR('mscb') VALUE CONVERT(TCCB_HD."shcc", 'utf8'), key TO_CHAR('trinhDo') VALUE CONVERT(TCCB_HD."trinhDo", 'utf8'), key TO_CHAR('hoTenCanBo') VALUE CONVERT(TCCB_HD."hoTen", 'utf8'), key TO_CHAR('vaiTro') VALUE CONVERT(SDH_VAI_TRO_TVHD.MA, 'utf8'), key TO_CHAR('tenVaiTro') VALUE CONVERT(SDH_VAI_TRO_TVHD.VAI_TRO, 'utf8'), key TO_CHAR('from') VALUE CONVERT(TCCB_HD."belongTo", 'utf8') )) returning clob), '}"', '}') AS "thanhVienHoiDong", regexp_replace(JSON_ARRAYAGG(to_clob(JSON_OBJECT( key TO_CHAR('mscb') VALUE CONVERT(TCCB."shcc", 'utf8'), key TO_CHAR('trinhDo') VALUE CONVERT(TCCB."trinhDo", 'utf8'), key TO_CHAR('hoTenCanBo') VALUE CONVERT(TCCB."hoTen", 'utf8'), key TO_CHAR('vaiTro') VALUE CONVERT(VT.ID, 'utf8'), key TO_CHAR('tenVaiTro') VALUE CONVERT(VT.TEN, 'utf8'), key TO_CHAR('from') VALUE CONVERT(TCCB."belongTo", 'utf8') )) returning clob), '}"', '}') AS "canBoHuongDan" FROM SDH_SO_QUYET_DINH SQD LEFT JOIN SDH_SO_QUYET_DINH_DETAIL SD ON SQD.ID = SD.SO_QUYET_DINH LEFT JOIN FW_SINH_VIEN_SDH SV ON SV.MSSV = SQD.MSSV LEFT JOIN TCCB ON TCCB."shcc" = SD.SHCC AND SD.NHOM_QUYET_DINH = 0 LEFT JOIN TCCB TCCB_HD ON TCCB_HD."shcc" = SD.SHCC AND SD.NHOM_QUYET_DINH = 1 LEFT JOIN (SELECT dks.ID AS "id", dks.SO_CONG_VAN AS "soCongVan", dks.NGAY_TAO AS "ngayTao", dks.NAM_HANH_CHINH AS "namHanhChinh", dvg.TEN AS "tenDonViGui", lcv.TEN AS "tenLoaiVanBan", dks.CAP_VAN_BAN AS "capVanBan", HCTH_YEU_CAU_CAP_SO.LY_DO AS "noiDung", HCTH_CONG_VAN_DI.TRICH_YEU AS "trichYeu", HCTH_CONG_VAN_DI.ID AS "idVanBan", TRIM(NVL(cbt.HO, '') || ' ' || NVL(cbt.TEN, '')) AS "hoTenCanBo", ROW_NUMBER() OVER (ORDER BY dks.NAM_HANH_CHINH DESC, TO_NUMBER(NVL(REGEXP_SUBSTR(dks.SO_CONG_VAN, '[0-9]+'), 0)) DESC, dks.SO_DI DESC, dks.NGAY_TAO DESC) R FROM HCTH_SO_DANG_KY dks LEFT JOIN DM_DON_VI dvg ON dvg.MA = dks.DON_VI_GUI LEFT JOIN DM_LOAI_VAN_BAN lcv ON lcv.ID = dks.LOAI_VAN_BAN LEFT JOIN TCHC_CAN_BO cbt ON cbt.SHCC = dks.NGUOI_TAO LEFT JOIN HCTH_YEU_CAU_CAP_SO ON HCTH_YEU_CAU_CAP_SO.SO_VAN_BAN = dks.ID LEFT JOIN HCTH_CONG_VAN_DI ON HCTH_CONG_VAN_DI.SO_DANG_KY = dks.ID) RES ON SQD.SO_QUYET_DINH = RES."id" LEFT JOIN SDH_VAI_TRO_CAN_BO_HUONG_DAN VT ON VT.ID = SD.VAI_TRO AND SD.NHOM_QUYET_DINH = 0 LEFT JOIN SDH_DM_VAI_TRO_HOI_DONG SDH_VAI_TRO_TVHD ON SDH_VAI_TRO_TVHD.MA = SD.VAI_TRO AND SD.NHOM_QUYET_DINH = 1 WHERE SQD.SO_QUYET_DINH IS NOT NULL AND SQD.MSSV IS NOT NULL AND ',' || idSoQuyetDinh || ',' LIKE '%,' || SQD.ID || ',%' GROUP BY SQD.ID, SQD.SO_QUYET_DINH, SQD.TEN, SQD.ACTION, SV.HO, SV.TEN, SD.TEN_DE_TAI, SD.TEN_HOI_DONG, SD.PHONG, SQD.TIME, RES."soCongVan", RES."ngayTao", RES."tenDonViGui", SD.NHOM_QUYET_DINH; RETURN my_cursor; END; /
Editor is loading...
Leave a Comment