Untitled

 avatar
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