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