        UPDATE DC_PHAN_QUYEN
           SET TEN_PHAN_QUYEN    = P_TEN_PHAN_QUYEN,
               MA_CAN_BO    = P_MA_CAN_BO,
               LOAI_PHAN_QUYEN     = P_LOAI
         WHERE MA_PHAN_QUYEN LIKE P_MA_PHAN_QUYEN;
        P_VAL := 'THANH_CONG';
    END IF;
  
  END;
   -------------------------------------------------------------------------------
  PROCEDURE DELETE_DC_PHAN_QUYEN(P_VAL   OUT NVARCHAR2, P_MA_PHAN_QUYEN NVARCHAR2) AS
  BEGIN
    P_VAL := 'CO_LOI';
    DELETE FROM DC_PHAN_QUYEN WHERE MA_PHAN_QUYEN LIKE P_MA_PHAN_QUYEN;
    P_VAL := 'THANH_CONG';
   END;  
   -------------------------------------------------------------------------------

PROCEDURE SELECT_CB_CAN_BO(P_CUR IN OUT MYCUR) AS
  BEGIN
    OPEN P_CUR FOR
      SELECT A.*
        FROM CB_CAN_BO A;
  END;
------------------------------------------------------------------------------------

PROCEDURE SELECT_DC_MUC_DICH_PHAN_QUYEN(P_CUR IN OUT MYCUR, P_MA_CAN_BO INT) AS
  BEGIN
    OPEN P_CUR FOR
     SELECT DISTINCT A.MA_MUC_DICH_SU_DUNG_DAT,CONCAT('(',CONCAT(CONCAT(CHUYEN_MUC_DICH ,')-'),ten_muc_dich_su_dung_dat)) as ten_muc_dich_su_dung_dat,A.STT_MUC_DICH_SU_DUNG_DAT FROM DC_MUC_DICH_SU_DUNG_DAT A, DC_PHAN_QUYEN B
      WHERE  A.MA_MUC_DICH_SU_DUNG_DAT=B.TEN_PHAN_QUYEN
      AND B.MA_CAN_BO=P_MA_CAN_BO
      AND LOAI_PHAN_QUYEN LIKE'MD'
         UNION 
      SELECT N'' AS MA_MUC_DICH_SU_DUNG_DAT,
            N'Tất cả' AS ten_muc_dich_su_dung_dat,
            -1 AS STT_MUC_DICH_SU_DUNG_DAT
           FROM DUAL
       ORDER BY STT_MUC_DICH_SU_DUNG_DAT , ten_muc_dich_su_dung_dat;
  END;
------------------------------------------------------------------------------------

PROCEDURE SELECT_DC_HINH_THUC_PHAN_QUYEN(P_CUR IN OUT MYCUR, P_MA_CAN_BO INT) AS
  BEGIN
    OPEN P_CUR FOR
    SELECT DISTINCT A.MA_HINH_THUC,A.TEN_HINH_THUC,A.STT_HINH_THUC FROM DC_HINH_THUC A, DC_PHAN_QUYEN B
    WHERE  A.MA_HINH_THUC=B.TEN_PHAN_QUYEN
    AND B.MA_CAN_BO=P_MA_CAN_BO
    AND LOAI_PHAN_QUYEN LIKE 'HT'
     UNION 
      SELECT N'' AS MA_HINH_THUC,
            N'Tất cả' AS TEN_HINH_THUC,
            -1 AS STT_HINH_THUC
           FROM DUAL
       ORDER BY STT_HINH_THUC , TEN_HINH_THUC;

  END;
------------------------------------------------------------------------------------
PROCEDURE UPDATE_DC_THUADAT_EXCEL(P_VAL            OUT NVARCHAR2,
                           P_LOAI_DAT    number,
                           P_SO_THUA    INT,
                           P_THUADATGOC    NVARCHAR2,
                           P_MA_PHUONG_XA NVARCHAR2,
                           --P_TRANG_THAI_SD INT,
                           P_MUC_DICH_SD NVARCHAR2,
                           P_MA_HINH_THUC NVARCHAR2,
                           P_NAM_CAP_NHAT NVARCHAR2,
                           P_DIEN_TICH FLOAT,
                           --P_LA_DAT_CONG INT,
                           P_TO_BAN_DO NVARCHAR2,
                           P_LA_THUOC_TO_CHUC NVARCHAR2,
                           P_SO_GCN_DAT NVARCHAR2,
                           P_KET_QUA_XD NVARCHAR2,
                           P_MA_LOAI_LUY_TIEN number,
                           P_DK_KE_KHAI INT,
                           P_VI_TRI NVARCHAR2,
                           P_NGUOI_SU_DUNG NVARCHAR2,
                           P_THOI_HAN INT,
                           P_TG_BD NVARCHAR2,
                           P_TG_KT NVARCHAR2,
                           P_MA_CAP_QUAN_LY   INT,
                           P_MA_HUYEN    NVARCHAR2,
                           P_PHUONG_THUC NVARCHAR2,
                           P_MA_QUOC_TICH NVARCHAR2,
                           P_SDT NVARCHAR2, 
                           P_GHI_CHU NVARCHAR2,
                           P_DAU_GIA INT,
                           P_TIEN_DAU_GIA FLOAT,
                           P_LY_DO NVARCHAR2,
                           P_TIEN_THUE NVARCHAR2,
                           P_MA_DOI_TUONG INT,
                           P_DIEN_TICH_SD FLOAT,
                           P_DON_VI_TO_CHUC_DAU_GIA NVARCHAR2
                           ) AS
 V_TON_TAI_MA  INT;
  BEGIN
    P_VAL := 'CO_LOI';
     SELECT COUNT(SO_THUA)
        INTO V_TON_TAI_MA
        FROM DC_THUADAT
       WHERE SO_THUA LIKE P_SO_THUA
       AND MA_MUC_DICH_SU_DUNG_DAT=P_MUC_DICH_SD
       AND MA_HINH_THUC=P_MA_HINH_THUC;
       
    IF V_TON_TAI_MA >0 THEN
      --UPDATE
       UPDATE DC_THUADAT SET
           MA_LOAI_DAT=P_LOAI_DAT,
           SO_THUA_GOC=P_THUADATGOC,
           MA_PHUONG_XA=P_MA_PHUONG_XA,
           ID_DONG=16,
           TRANG_THAI_SD=0,
           MA_MUC_DICH_SU_DUNG_DAT=P_MUC_DICH_SD,
           MA_HINH_THUC=P_MA_HINH_THUC
        WHERE SO_THUA=P_SO_THUA;
        
        UPDATE DC_LUY_TIEN SET
           NAM_CAP_PHAT=to_date(P_NAM_CAP_NHAT,'DDMMYYYY'),
           DIEN_TICH_THUA=P_DIEN_TICH,
           TOA_DO='',
           LA_DAT_CONG=0,
           TO_BAN_DO=P_TO_BAN_DO,
           LA_THUOC_TO_CHUC=P_LA_THUOC_TO_CHUC,
           SO_GCN_DAT=P_SO_GCN_DAT,
           KET_QUA_XD=P_KET_QUA_XD,
           MA_LOAI_LUY_TIEN=P_MA_LOAI_LUY_TIEN,
           DK_KE_KHAI=P_DK_KE_KHAI,
           VI_TRI=P_VI_TRI,
           NGUOI_DUNG=P_NGUOI_SU_DUNG,
           THOI_HAN=P_THOI_HAN,
           TG_BK=to_date(P_TG_BD,'DDMMYYYY'),
           TG_KT=to_date(P_TG_KT,'DDMMYYYY'),
            MA_CAP_QUAN_LY=P_MA_CAP_QUAN_LY
           WHERE SU_DUNG=0 
           AND MA_THUA=(SELECT MAX(MA_THUA) AS MA_THUA FROM DC_THUADAT 
           WHERE SO_THUA=P_SO_THUA);
           
           IF(P_NGUOI_SU_DUNG IS NOT NULL) THEN
               UPDATE DC_HO_SO_DAT SET
               MA_QUAN_HUYEN=P_MA_HUYEN,
               TEN_DON_VI=P_NGUOI_SU_DUNG,
              PHUONG_THUC_THANH_TOAN=P_PHUONG_THUC,
              MA_QUOC_TICH=P_MA_QUOC_TICH,
              SDT=P_SDT,
               GHI_CHU=P_GHI_CHU,
               DAU_GIA=P_DAU_GIA,
               TIEN_DAU_GIA=P_TIEN_DAU_GIA,
               LY_DO=P_LY_DO,
               TRANG_THAI='HT',
               TIEN_THUE=P_TIEN_THUE,
               ID_DONG=22,
               MA_DOI_TUONG=P_MA_DOI_TUONG,
               DIEN_TICH_SD=P_DIEN_TICH_SD,
               DON_VI_TO_CHUC_DAU_GIA=P_DON_VI_TO_CHUC_DAU_GIA,
               THUOC_TO_CHUC=P_LA_THUOC_TO_CHUC,
               MA_MUC_DICH_SU_DUNG_DAT=P_MUC_DICH_SD,
               MA_HINH_THUC=P_MA_HINH_THUC
               WHERE MA_THUA=(SELECT MAX(MA_THUA) AS MA_THUA FROM DC_THUADAT WHERE SO_THUA=P_SO_THUA)
               ;
           END IF;
         
         P_VAL := 'THANH_CONG';
      ELSE
        
    INSERT INTO DC_THUADAT
    (
           MA_THUA,
           MA_LOAI_DAT,
           SO_THUA,
           SO_THUA_GOC,
           MA_PHUONG_XA,
           ID_DONG,
           TRANG_THAI_SD,
           MA_MUC_DICH_SU_DUNG_DAT,
           MA_HINH_THUC
           --HINH_ANH
         
     )VALUES
     (
           DC_THUA_DAT_SEQ.NEXTVAL,
           P_LOAI_DAT,
           P_SO_THUA,
           P_THUADATGOC,
           P_MA_PHUONG_XA,
           16,
           0,
           P_MUC_DICH_SD,
           P_MA_HINH_THUC
          -- P_FILE_HS
     );
    INSERT INTO DC_LUY_TIEN
    (
           MA_LUY_TIEN,NAM_CAP_PHAT,DIEN_TICH_THUA,TOA_DO,LA_DAT_CONG,TO_BAN_DO,
           LA_THUOC_TO_CHUC,SO_GCN_DAT,KET_QUA_XD,MA_THUA,MA_LOAI_LUY_TIEN,DK_KE_KHAI,
           VI_TRI,NGUOI_DUNG,THOI_HAN,TG_BK,TG_KT,SU_DUNG, MA_CAP_QUAN_LY
     )VALUES
     (
           DC_LUY_TIEN_SEQ.NEXTVAL,to_date(P_NAM_CAP_NHAT,'DDMMYYYY'),
           P_DIEN_TICH,'',0,P_TO_BAN_DO,
           P_LA_THUOC_TO_CHUC,P_SO_GCN_DAT,P_KET_QUA_XD,
           DC_THUA_DAT_SEQ.CURRVAL,P_MA_LOAI_LUY_TIEN, P_DK_KE_KHAI,
           P_VI_TRI,P_NGUOI_SU_DUNG,P_THOI_HAN,to_date(P_TG_BD,'DDMMYYYY'),to_date(P_TG_KT,'DDMMYYYY'),0,
           P_MA_CAP_QUAN_LY
           
     );  
      IF(P_NGUOI_SU_DUNG IS NOT NULL) THEN
               INSERT INTO DC_HO_SO_DAT
              (MA_HS_DAT,MA_QUAN_HUYEN,TEN_DON_VI,
              PHUONG_THUC_THANH_TOAN, MA_QUOC_TICH,SDT, GHI_CHU,
              MA_THUA,DAU_GIA, TIEN_DAU_GIA,
               LY_DO,TRANG_THAI,TIEN_THUE,ID_DONG,MA_DOI_TUONG,
               DIEN_TICH_SD,DON_VI_TO_CHUC_DAU_GIA,THUOC_TO_CHUC,MA_MUC_DICH_SU_DUNG_DAT,MA_HINH_THUC)
            VALUES
              (DC_HO_SO_DAT_SEQ.NEXTVAL,P_MA_HUYEN, P_NGUOI_SU_DUNG,
              P_PHUONG_THUC, P_MA_QUOC_TICH,P_SDT, P_GHI_CHU, 
              DC_THUA_DAT_SEQ.CURRVAL, P_DAU_GIA, P_TIEN_DAU_GIA,
                 P_LY_DO,'HT',P_TIEN_THUE,22,P_MA_DOI_TUONG,
                 P_DIEN_TICH_SD,P_DON_VI_TO_CHUC_DAU_GIA,P_LA_THUOC_TO_CHUC,P_MUC_DICH_SD,P_MA_HINH_THUC );     
                         
           END IF;
        P_VAL := 'THANH_CONG';  
        END IF;
       
  END;
------------------------------------------------------------------------------------
------------------------------DC_LOG------------------------------------------
PROCEDURE SELECT_DC_LOG(P_CUR IN OUT MYCUR) AS
  BEGIN
    OPEN P_CUR FOR
      SELECT A.*, B.*
        FROM DC_LOG A, CB_CAN_BO B
        WHERE A.MA_CAN_BO=B.MA_CAN_BO
       ORDER BY A.MA_LOG;
  END;
    -------------------------------------------------------------------------------
 
  PROCEDURE UPDATE_LOG(P_VAL            OUT NVARCHAR2,
                           P_MA_CAN_BO    NUMBER,
                           P_THAO_TAC    NVARCHAR2) AS
  BEGIN
    P_VAL := 'CO_LOI';
        INSERT INTO DC_LOG
          (MA_LOG,
           MA_CAN_BO,
           THAO_TAC,
           THOI_GIAN)
        VALUES
          (DC_LOG_SEQ.NEXTVAL,
           P_MA_CAN_BO,
           P_THAO_TAC,
           SYSDATE);
        P_VAL := 'THANH_CONG';
  END;
   -------------------------------------------------------------------------------
  PROCEDURE DELETE_DC_LOG(P_VAL   OUT NVARCHAR2) AS
  BEGIN
    P_VAL := 'CO_LOI';
    DELETE FROM DC_LOG ;
    P_VAL := 'THANH_CONG';
   END;  
 -----------------------
    -------------------------------------------------------------------------------
  PROCEDURE DELETE_DC_LOG_ROWS(P_VAL   OUT NVARCHAR2, P_MA_LOG NUMBER) AS
  BEGIN
    P_VAL := 'CO_LOI';
    DELETE FROM DC_LOG WHERE MA_LOG=P_MA_LOG;
    P_VAL := 'THANH_CONG';
   END;  
------------------------------------------------------------------------------------
PROCEDURE SELECT_PHX_ALL(P_CUR IN OUT MYCUR) AS
  BEGIN
    OPEN P_CUR FOR
      SELECT A.*
             
        FROM DM_PHUONG_XA A,  DM_QUAN_HUYEN BC
        WHERE A.MA_QUAN_HUYEN=BC.MA_QUAN_HUYEN AND BC.MA_TINH_THANH=N'TIEN_GIANG';
  END;
  ------------------------------DC_QUOC_TICH------------------------------------------
PROCEDURE SELECT_QUOC_TICH(P_CUR IN OUT MYCUR) AS
  BEGIN
    OPEN P_CUR FOR
      SELECT A.*,
             CASE
               WHEN A.TT_QUOC_TICH = 0 THEN
                'Mở'
               ELSE
                'Ðóng'
             END AS TEN_TRANG_THAI
        FROM DM_QUOC_TICH A
       ORDER BY A.STT_QUOC_TICH , A.TEN_QUOC_TICH;
  END;
-------------------------------------------------------------------------------
    PROCEDURE SELECT_QUOC_TICH_DETAIL(P_CUR  IN OUT MYCUR,
                                  P_MA_QUOC_TICH NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR
      SELECT A.*,
             CASE
               WHEN A.TT_QUOC_TICH = 0 THEN
                'Mở'
               ELSE
                'Ðóng'
             END AS TEN_TRANG_THAI
        FROM DM_QUOC_TICH A
       WHERE A.MA_QUOC_TICH LIKE P_MA_QUOC_TICH;
  END;
    -------------------------------------------------------------------------------
 
  PROCEDURE UPDATE_QUOC_TICH(P_VAL            OUT NVARCHAR2,
                           P_UPDATE_TYPE    NVARCHAR2,
                           P_MA_QUOC_TICH     NVARCHAR2,
                           P_TEN_QUOC_TICH    NVARCHAR2,
                           P_STT_QUOC_TICH    INT,
                           P_TT_QUOC_TICH     INT) AS
    V_TON_TAI_MA  INT;
    V_TON_TAI_TEN INT;
  BEGIN
    P_VAL := 'CO_LOI';
  
    IF P_UPDATE_TYPE = 'THEM_MOI' THEN
      SELECT COUNT(MA_QUOC_TICH)
        INTO V_TON_TAI_MA
        FROM DM_QUOC_TICH
       WHERE UPPER(MA_QUOC_TICH) LIKE UPPER(P_MA_QUOC_TICH);
      SELECT COUNT(MA_QUOC_TICH)
        INTO V_TON_TAI_TEN
        FROM DM_QUOC_TICH
       WHERE UPPER(TEN_QUOC_TICH) LIKE UPPER(P_TEN_QUOC_TICH);
      IF V_TON_TAI_MA > 0 OR P_MA_QUOC_TICH = '0' THEN
        -- kiem tra trung ma , khong cho them ma='0'
        P_VAL := 'TON_TAI_MA';
      ELSIF V_TON_TAI_TEN > 0 THEN
        -- kiem tra trung ten   
        P_VAL := 'TON_TAI_TEN';
      ELSE
        INSERT INTO DM_QUOC_TICH
          (MA_QUOC_TICH,
           TEN_QUOC_TICH,
           STT_QUOC_TICH,
           TT_QUOC_TICH)
        VALUES
          (P_MA_QUOC_TICH,
           P_TEN_QUOC_TICH,
           P_STT_QUOC_TICH,
           P_TT_QUOC_TICH);
        P_VAL := 'THANH_CONG';
      END IF;
    ELSE
      SELECT COUNT(MA_QUOC_TICH)
        INTO V_TON_TAI_TEN
        FROM DM_QUOC_TICH
       WHERE UPPER(TEN_QUOC_TICH) LIKE UPPER(P_TEN_QUOC_TICH)
         AND MA_QUOC_TICH NOT LIKE P_MA_QUOC_TICH;
      IF V_TON_TAI_TEN > 0 THEN
        -- kiem tra trung ten khac no
        P_VAL := 'TON_TAI_TEN';
      ELSE
        UPDATE DM_QUOC_TICH
           SET TEN_QUOC_TICH    = P_TEN_QUOC_TICH,
               STT_QUOC_TICH    = P_STT_QUOC_TICH,
               TT_QUOC_TICH     = P_TT_QUOC_TICH
         WHERE MA_QUOC_TICH LIKE P_MA_QUOC_TICH;
        P_VAL := 'THANH_CONG';
      END IF;
    END IF;
  
  END;
   -------------------------------------------------------------------------------
  PROCEDURE DELETE_QUOC_TICH(P_VAL   OUT NVARCHAR2, P_MA_QUOC_TICH NVARCHAR2) AS
  BEGIN
    P_VAL := 'CO_LOI';
    DELETE FROM DM_QUOC_TICH WHERE MA_QUOC_TICH LIKE P_MA_QUOC_TICH;
    P_VAL := 'THANH_CONG';
   END;

------------------------------------------------------------------------------------
PROCEDURE SELECT_THUADAT_TO(P_CUR IN OUT MYCUR) AS
  BEGIN
    OPEN P_CUR FOR
      SELECT A.MA_THUA,CONCAT('Số thửa: ',CONCAT(CONCAT(A.SO_THUA ,' Số tờ: '), C.TO_BAN_DO)) as THANG_TO
        FROM DC_THUADAT A,DC_LUY_TIEN C
        WHERE A.MA_THUA=C.MA_THUA
        AND C.NAM_CAP_PHAT IN(SELECT MAX(NAM_CAP_PHAT) FROM DC_LUY_TIEN GROUP BY MA_THUA) 
       ORDER BY MA_THUA;
  END;
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------ 
end TNMT_DATCONG_DM;
/

prompt
prompt Creating package body TNMT_DATCONG_TK
prompt =====================================
prompt
create or replace package body hai.TNMT_DATCONG_TK is

end TNMT_DATCONG_TK;
/

prompt
prompt Creating package body TNMT_DUNGCHUNG
prompt ====================================
prompt
create or replace package body hai.TNMT_DUNGCHUNG is
  -------------------------------------------------------------------------------
  PROCEDURE SELECT_CHUC_VU(P_CUR IN OUT MYCUR) AS
  BEGIN
    OPEN P_CUR FOR
      SELECT A.*,
             CASE
               WHEN A.TT_CHUC_VU = 0 THEN
                'Mở'
               ELSE
                'Ðóng'
             END AS TEN_TRANG_THAI
        FROM DM_CHUC_VU A
       ORDER BY A.STT_CHUC_VU, A.TEN_CHUC_VU;
  END;
  -------------------------------------------------------------------------------
  PROCEDURE SELECT_CHUC_VU_DETAIL(P_CUR        IN OUT MYCUR,
                                  P_MA_CHUC_VU NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR
      SELECT A.*,
             CASE
               WHEN A.TT_CHUC_VU = 0 THEN
                'Mở'
               ELSE
                'Ðóng'
             END AS TEN_TRANG_THAI
        FROM DM_CHUC_VU A
       WHERE A.MA_CHUC_VU LIKE P_MA_CHUC_VU;
  END;
  -------------------------------------------------------------------------------
  PROCEDURE UPDATE_CHUC_VU(P_VAL            OUT NVARCHAR2,
                           P_UPDATE_TYPE    NVARCHAR2,
                           P_MA_CHUC_VU     NVARCHAR2,
                           P_TEN_CHUC_VU    NVARCHAR2,
                           P_STT_CHUC_VU    INT,
                           P_TT_CHUC_VU     INT) AS
    V_TON_TAI_MA  INT;
    V_TON_TAI_TEN INT;
  BEGIN
    P_VAL := 'CO_LOI';
  
    IF P_UPDATE_TYPE = 'THEM_MOI' THEN
      SELECT COUNT(MA_CHUC_VU)
        INTO V_TON_TAI_MA
        FROM DM_CHUC_VU
       WHERE UPPER(MA_CHUC_VU) LIKE UPPER(P_MA_CHUC_VU);
      SELECT COUNT(MA_CHUC_VU)
        INTO V_TON_TAI_TEN
        FROM DM_CHUC_VU
       WHERE UPPER(TEN_CHUC_VU) LIKE UPPER(P_TEN_CHUC_VU);
      IF V_TON_TAI_MA > 0 OR P_MA_CHUC_VU = '0' THEN
        -- kiem tra trung ma , khong cho them ma='0'
        P_VAL := 'TON_TAI_MA';
      ELSIF V_TON_TAI_TEN > 0 THEN
        -- kiem tra trung ten   
        P_VAL := 'TON_TAI_TEN';
      ELSE
        INSERT INTO DM_CHUC_VU
          (MA_CHUC_VU,
           TEN_CHUC_VU,
           STT_CHUC_VU,
           TT_CHUC_VU)
        VALUES
          (P_MA_CHUC_VU,
           P_TEN_CHUC_VU,
           P_STT_CHUC_VU,
           P_TT_CHUC_VU);
        P_VAL := 'THANH_CONG';
      END IF;
    ELSE
      SELECT COUNT(MA_CHUC_VU)
        INTO V_TON_TAI_TEN
        FROM DM_CHUC_VU
       WHERE UPPER(TEN_CHUC_VU) LIKE UPPER(P_TEN_CHUC_VU)
         AND MA_CHUC_VU NOT LIKE P_MA_CHUC_VU;
      IF V_TON_TAI_TEN > 0 THEN
        -- kiem tra trung ten khac no
        P_VAL := 'TON_TAI_TEN';
      ELSE
        UPDATE DM_CHUC_VU
           SET TEN_CHUC_VU    = P_TEN_CHUC_VU,
               STT_CHUC_VU    = P_STT_CHUC_VU,
               TT_CHUC_VU     = P_TT_CHUC_VU
         WHERE MA_CHUC_VU LIKE P_MA_CHUC_VU;
        P_VAL := 'THANH_CONG';
      END IF;
    END IF;
  
  END;
  -------------------------------------------------------------------------------
  PROCEDURE DELETE_CHUC_VU(P_VAL   OUT NVARCHAR2, P_MA_CHUC_VU NVARCHAR2) AS
  BEGIN
    P_VAL := 'CO_LOI';
    DELETE FROM DM_CHUC_VU WHERE MA_CHUC_VU LIKE P_MA_CHUC_VU;
    P_VAL := 'THANH_CONG';
   END;    
------------------------------DM_DAN_TOC------------------------------------------
PROCEDURE SELECT_DAN_TOC(P_CUR IN OUT MYCUR) AS
  BEGIN
    OPEN P_CUR FOR
      SELECT A.*,
             CASE
               WHEN A.TT_DAN_TOC = 0 THEN
                'Mở'
               ELSE
                'Ðóng'
             END AS TEN_TRANG_THAI
        FROM DM_DAN_TOC A
       ORDER BY A.STT_DAN_TOC , A.TEN_DAN_TOC;
  END;
-------------------------------------------------------------------------------
    PROCEDURE SELECT_DAN_TOC_DETAIL(P_CUR  IN OUT MYCUR,
                                  P_MA_DAN_TOC NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR
      SELECT A.*,
             CASE
               WHEN A.TT_DAN_TOC = 0 THEN
                'Mở'
               ELSE
                'Ðóng'
             END AS TEN_TRANG_THAI
        FROM DM_DAN_TOC A
       WHERE A.MA_DAN_TOC LIKE P_MA_DAN_TOC;
  END;
    -------------------------------------------------------------------------------
 
  PROCEDURE UPDATE_DAN_TOC(P_VAL            OUT NVARCHAR2,
                           P_UPDATE_TYPE    NVARCHAR2,
                           P_MA_DAN_TOC     NVARCHAR2,
                           P_TEN_DAN_TOC    NVARCHAR2,
                           P_STT_DAN_TOC    INT,
                           P_TT_DAN_TOC     INT) AS
    V_TON_TAI_MA  INT;
    V_TON_TAI_TEN INT;
  BEGIN
    P_VAL := 'CO_LOI';
  
    IF P_UPDATE_TYPE = 'THEM_MOI' THEN
      SELECT COUNT(MA_DAN_TOC)
        INTO V_TON_TAI_MA
        FROM DM_DAN_TOC
       WHERE UPPER(MA_DAN_TOC) LIKE UPPER(P_MA_DAN_TOC);
      SELECT COUNT(MA_DAN_TOC)
        INTO V_TON_TAI_TEN
        FROM DM_DAN_TOC
       WHERE UPPER(TEN_DAN_TOC) LIKE UPPER(P_TEN_DAN_TOC);
      IF V_TON_TAI_MA > 0 OR P_MA_DAN_TOC = '0' THEN
        -- kiem tra trung ma , khong cho them ma='0'
        P_VAL := 'TON_TAI_MA';
      ELSIF V_TON_TAI_TEN > 0 THEN
        -- kiem tra trung ten   
        P_VAL := 'TON_TAI_TEN';
      ELSE
        INSERT INTO DM_DAN_TOC
          (MA_DAN_TOC,
           TEN_DAN_TOC,
           STT_DAN_TOC,
           TT_DAN_TOC)
        VALUES
          (P_MA_DAN_TOC,
           P_TEN_DAN_TOC,
           P_STT_DAN_TOC,
           P_TT_DAN_TOC);
        P_VAL := 'THANH_CONG';
      END IF;
    ELSE
      SELECT COUNT(MA_DAN_TOC)
        INTO V_TON_TAI_TEN
        FROM DM_DAN_TOC
       WHERE UPPER(TEN_DAN_TOC) LIKE UPPER(P_TEN_DAN_TOC)
         AND MA_DAN_TOC NOT LIKE P_MA_DAN_TOC;
      IF V_TON_TAI_TEN > 0 THEN
        -- kiem tra trung ten khac no
        P_VAL := 'TON_TAI_TEN';
      ELSE
        UPDATE DM_DAN_TOC
           SET TEN_DAN_TOC    = P_TEN_DAN_TOC,
               STT_DAN_TOC    = P_STT_DAN_TOC,
               TT_DAN_TOC     = P_TT_DAN_TOC
         WHERE MA_DAN_TOC LIKE P_MA_DAN_TOC;
        P_VAL := 'THANH_CONG';
      END IF;
    END IF;
  
  END;
   -------------------------------------------------------------------------------
  PROCEDURE DELETE_DAN_TOC(P_VAL   OUT NVARCHAR2, P_MA_DAN_TOC NVARCHAR2) AS
  BEGIN
    P_VAL := 'CO_LOI';
    DELETE FROM DM_DAN_TOC WHERE MA_DAN_TOC LIKE P_MA_DAN_TOC;
    P_VAL := 'THANH_CONG';
   END;  

------------------------------DM_TON_GIAO------------------------------------------
PROCEDURE SELECT_TON_GIAO(P_CUR IN OUT MYCUR) AS
  BEGIN
    OPEN P_CUR FOR
      SELECT A.*,
             CASE
               WHEN A.TT_TON_GIAO = 0 THEN
                'Mở'
               ELSE
                'Ðóng'
             END AS TEN_TRANG_THAI
        FROM DM_TON_GIAO A
       ORDER BY A.STT_TON_GIAO , A.Ten_Ton_Giao;
  END;
   -------------------------------------------------------------------------------
    PROCEDURE SELECT_TON_GIAO_DETAIL(P_CUR  IN OUT MYCUR,
                                  P_MA_TON_GIAO NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR
      SELECT A.*,
             CASE
               WHEN A.TT_TON_GIAO = 0 THEN
                'Mở'
               ELSE
                'Ðóng'
             END AS TEN_TRANG_THAI
        FROM DM_TON_GIAO A
       WHERE A.MA_TON_GIAO LIKE P_MA_TON_GIAO;
  END;
    -------------------------------------------------------------------------------
  PROCEDURE UPDATE_TON_GIAO(P_VAL            OUT NVARCHAR2,
                           P_UPDATE_TYPE    NVARCHAR2,
                           P_MA_TON_GIAO     NVARCHAR2,
                           P_TEN_TON_GIAO    NVARCHAR2,
                           P_STT_TON_GIAO    INT,
                           P_TT_TON_GIAO     INT) AS
    V_TON_TAI_MA  INT;
    V_TON_TAI_TEN INT;
  BEGIN
    P_VAL := 'CO_LOI';
    IF P_UPDATE_TYPE = 'THEM_MOI' THEN
      SELECT COUNT(MA_TON_GIAO)
        INTO V_TON_TAI_MA
        FROM DM_TON_GIAO
       WHERE UPPER(MA_TON_GIAO) LIKE UPPER(P_MA_TON_GIAO);
      SELECT COUNT(MA_TON_GIAO)
        INTO V_TON_TAI_TEN
        FROM DM_TON_GIAO
       WHERE UPPER(TEN_TON_GIAO) LIKE UPPER(P_TEN_TON_GIAO);
      IF V_TON_TAI_MA > 0 OR P_MA_TON_GIAO = '0' THEN
        -- kiem tra trung ma , khong cho them ma='0'
        P_VAL := 'TON_TAI_MA';
      ELSIF V_TON_TAI_TEN > 0 THEN
        -- kiem tra trung ten   
        P_VAL := 'TON_TAI_TEN';
      ELSE
        INSERT INTO DM_TON_GIAO
          (MA_TON_GIAO,
           TEN_TON_GIAO,
           STT_TON_GIAO,
           TT_TON_GIAO)
        VALUES
          (P_MA_TON_GIAO,
           P_TEN_TON_GIAO,
           P_STT_TON_GIAO,
           P_TT_TON_GIAO);
        P_VAL := 'THANH_CONG';
      END IF;
    ELSE
      SELECT COUNT(MA_TON_GIAO)
        INTO V_TON_TAI_TEN
        FROM DM_TON_GIAO
       WHERE UPPER(TEN_TON_GIAO) LIKE UPPER(P_TEN_TON_GIAO)
         AND MA_TON_GIAO NOT LIKE P_MA_TON_GIAO;
      IF V_TON_TAI_TEN > 0 THEN
        -- kiem tra trung ten khac no
        P_VAL := 'TON_TAI_TEN';
      ELSE
        UPDATE DM_TON_GIAO
           SET TEN_TON_GIAO    = P_TEN_TON_GIAO,
               STT_TON_GIAO    = P_STT_TON_GIAO,
               TT_TON_GIAO     = P_TT_TON_GIAO
         WHERE MA_TON_GIAO LIKE P_MA_TON_GIAO;
        P_VAL := 'THANH_CONG';
      END IF;
    END IF;
  
  END;
   -------------------------------------------------------------------------------
  PROCEDURE DELETE_TON_GIAO(P_VAL   OUT NVARCHAR2, P_MA_TON_GIAO NVARCHAR2) AS
  BEGIN
    P_VAL := 'CO_LOI';
    DELETE FROM DM_TON_GIAO WHERE MA_TON_GIAO LIKE P_MA_TON_GIAO;
    P_VAL := 'THANH_CONG';
   END;  

------------------------------DM_QUOC_TICH------------------------------------------
PROCEDURE SELECT_QUOC_TICH(P_CUR IN OUT MYCUR) AS
  BEGIN
    OPEN P_CUR FOR
      SELECT A.*,
             CASE
               WHEN A.TT_QUOC_TICH = 0 THEN
                'Mở'
               ELSE
                'Ðóng'
             END AS TEN_TRANG_THAI
        FROM DM_QUOC_TICH A
       ORDER BY A.STT_QUOC_TICH , A.Ten_QUOC_TICH;
  END;
   -------------------------------------------------------------------------------
    PROCEDURE SELECT_QUOC_TICH_DETAIL(P_CUR  IN OUT MYCUR,
                                  P_MA_QUOC_TICH NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR
      SELECT A.*,
             CASE
               WHEN A.TT_QUOC_TICH = 0 THEN
                'Mở'
               ELSE
                'Ðóng'
             END AS TEN_TRANG_THAI
        FROM DM_QUOC_TICH A
       WHERE A.MA_QUOC_TICH LIKE P_MA_QUOC_TICH;
  END;
    -------------------------------------------------------------------------------
 
  PROCEDURE UPDATE_QUOC_TICH(P_VAL            OUT NVARCHAR2,
                           P_UPDATE_TYPE    NVARCHAR2,
                           P_MA_QUOC_TICH     NVARCHAR2,
                           P_TEN_QUOC_TICH    NVARCHAR2,
                           P_STT_QUOC_TICH    INT,
                           P_TT_QUOC_TICH     INT) AS
    V_TON_TAI_MA  INT;
    V_TON_TAI_TEN INT;
  BEGIN
    P_VAL := 'CO_LOI';
  
    IF P_UPDATE_TYPE = 'THEM_MOI' THEN
      SELECT COUNT(MA_QUOC_TICH)
        INTO V_TON_TAI_MA
        FROM DM_QUOC_TICH
       WHERE UPPER(MA_QUOC_TICH) LIKE UPPER(P_MA_QUOC_TICH);
      SELECT COUNT(MA_QUOC_TICH)
        INTO V_TON_TAI_TEN
        FROM DM_QUOC_TICH
       WHERE UPPER(TEN_QUOC_TICH) LIKE UPPER(P_TEN_QUOC_TICH);
      IF V_TON_TAI_MA > 0 OR P_MA_QUOC_TICH = '0' THEN
        -- kiem tra trung ma , khong cho them ma='0'
        P_VAL := 'TON_TAI_MA';
      ELSIF V_TON_TAI_TEN > 0 THEN
        -- kiem tra trung ten   
        P_VAL := 'TON_TAI_TEN';
      ELSE
        INSERT INTO DM_QUOC_TICH
          (MA_QUOC_TICH,
           TEN_QUOC_TICH,
           STT_QUOC_TICH,
           TT_QUOC_TICH)
        VALUES
          (P_MA_QUOC_TICH,
           P_TEN_QUOC_TICH,
           P_STT_QUOC_TICH,
           P_TT_QUOC_TICH);
        P_VAL := 'THANH_CONG';
      END IF;
    ELSE
      SELECT COUNT(MA_QUOC_TICH)
        INTO V_TON_TAI_TEN
        FROM DM_QUOC_TICH
       WHERE UPPER(TEN_QUOC_TICH) LIKE UPPER(P_TEN_QUOC_TICH)
         AND MA_QUOC_TICH NOT LIKE P_MA_QUOC_TICH;
      IF V_TON_TAI_TEN > 0 THEN
        -- kiem tra trung ten khac no
        P_VAL := 'TON_TAI_TEN';
      ELSE
        UPDATE DM_QUOC_TICH
           SET TEN_QUOC_TICH    = P_TEN_QUOC_TICH,
               STT_QUOC_TICH    = P_STT_QUOC_TICH,
               TT_QUOC_TICH     = P_TT_QUOC_TICH
         WHERE MA_QUOC_TICH LIKE P_MA_QUOC_TICH;
        P_VAL := 'THANH_CONG';
      END IF;
    END IF;
  
  END;
   -------------------------------------------------------------------------------
  PROCEDURE DELETE_QUOC_TICH(P_VAL   OUT NVARCHAR2, P_MA_QUOC_TICH NVARCHAR2) AS
  BEGIN
    P_VAL := 'CO_LOI';
    DELETE FROM DM_QUOC_TICH WHERE MA_QUOC_TICH LIKE P_MA_QUOC_TICH;
    P_VAL := 'THANH_CONG';
   END;

 ------------------------------DM_LOAI_VAN_BAN------------------------------------------
PROCEDURE SELECT_LOAI_VAN_BAN(P_CUR IN OUT MYCUR) AS
  BEGIN
    OPEN P_CUR FOR
      SELECT A.*,
             CASE
               WHEN A.TT_LOAI_VAN_BAN = 0 THEN
                'M?'
               ELSE
                '?óng'
             END AS TEN_TRANG_THAI
        FROM DM_LOAI_VAN_BAN A
       ORDER BY A.STT_LOAI_VAN_BAN , A.Ten_LOAI_VAN_BAN;
  END;
   -------------------------------------------------------------------------------
    PROCEDURE SELECT_LOAI_VAN_BAN_DETAIL(P_CUR  IN OUT MYCUR,
                                  P_MA_LOAI_VAN_BAN NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR
      SELECT A.*,
             CASE
               WHEN A.TT_LOAI_VAN_BAN = 0 THEN
                'M?'
               ELSE
                '?óng'
             END AS TEN_TRANG_THAI
        FROM DM_LOAI_VAN_BAN A
       WHERE A.MA_LOAI_VAN_BAN LIKE P_MA_LOAI_VAN_BAN;
  END;
    -------------------------------------------------------------------------------
 
  PROCEDURE UPDATE_LOAI_VAN_BAN(P_VAL            OUT NVARCHAR2,
                           P_UPDATE_TYPE    NVARCHAR2,
                           P_MA_LOAI_VAN_BAN     NVARCHAR2,
                           P_TEN_LOAI_VAN_BAN    NVARCHAR2,
                           P_STT_LOAI_VAN_BAN    INT,
                           P_TT_LOAI_VAN_BAN     INT) AS
    V_TON_TAI_MA  INT;
    V_TON_TAI_TEN INT;
  BEGIN
    P_VAL := 'CO_LOI';
  
    IF P_UPDATE_TYPE = 'THEM_MOI' THEN
      SELECT COUNT(MA_LOAI_VAN_BAN)
        INTO V_TON_TAI_MA
        FROM DM_LOAI_VAN_BAN
       WHERE UPPER(MA_LOAI_VAN_BAN) LIKE UPPER(P_MA_LOAI_VAN_BAN);
      SELECT COUNT(MA_LOAI_VAN_BAN)
        INTO V_TON_TAI_TEN
        FROM DM_LOAI_VAN_BAN
       WHERE UPPER(TEN_LOAI_VAN_BAN) LIKE UPPER(P_TEN_LOAI_VAN_BAN);
      IF V_TON_TAI_MA > 0 OR P_MA_LOAI_VAN_BAN = '0' THEN
        -- kiem tra trung ma , khong cho them ma='0'
        P_VAL := 'TON_TAI_MA';
      ELSIF V_TON_TAI_TEN > 0 THEN
        -- kiem tra trung ten   
        P_VAL := 'TON_TAI_TEN';
      ELSE
        INSERT INTO DM_LOAI_VAN_BAN
          (MA_LOAI_VAN_BAN,
           TEN_LOAI_VAN_BAN,
           STT_LOAI_VAN_BAN,
           TT_LOAI_VAN_BAN)
        VALUES
          (P_MA_LOAI_VAN_BAN,
           P_TEN_LOAI_VAN_BAN,
           P_STT_LOAI_VAN_BAN,
           P_TT_LOAI_VAN_BAN);
        P_VAL := 'THANH_CONG';
      END IF;
    ELSE
      SELECT COUNT(MA_LOAI_VAN_BAN)
        INTO V_TON_TAI_TEN
        FROM DM_LOAI_VAN_BAN
       WHERE UPPER(TEN_LOAI_VAN_BAN) LIKE UPPER(P_TEN_LOAI_VAN_BAN)
         AND MA_LOAI_VAN_BAN NOT LIKE P_MA_LOAI_VAN_BAN;
      IF V_TON_TAI_TEN > 0 THEN
        -- kiem tra trung ten khac no
        P_VAL := 'TON_TAI_TEN';
      ELSE
        UPDATE DM_LOAI_VAN_BAN
           SET TEN_LOAI_VAN_BAN    = P_TEN_LOAI_VAN_BAN,
               STT_LOAI_VAN_BAN    = P_STT_LOAI_VAN_BAN,
               TT_LOAI_VAN_BAN     = P_TT_LOAI_VAN_BAN
         WHERE MA_LOAI_VAN_BAN LIKE P_MA_LOAI_VAN_BAN;
        P_VAL := 'THANH_CONG';
      END IF;
    END IF;
  
  END;
   -------------------------------------------------------------------------------
  PROCEDURE DELETE_LOAI_VAN_BAN(P_VAL   OUT NVARCHAR2, P_MA_LOAI_VAN_BAN NVARCHAR2) AS
  BEGIN
    P_VAL := 'CO_LOI';
    DELETE FROM DM_LOAI_VAN_BAN WHERE MA_LOAI_VAN_BAN LIKE P_MA_LOAI_VAN_BAN;
    P_VAL := 'THANH_CONG';
   END;  
end TNMT_DUNGCHUNG;
/

prompt
prompt Creating package body TNMT_LICH_CONG_TAC
prompt ========================================
prompt
CREATE OR REPLACE PACKAGE BODY HAI.TNMT_LICH_CONG_TAC IS
--------------------------------
PROCEDURE UPDATE_LICH_CONG_TAC(P_VAL OUT NVARCHAR2,P_STT INT,P_MA_CAN_BO NVARCHAR2, P_NGAY_CONG_TAC NVARCHAR2,P_NGAY_KET_THUC NVARCHAR2,P_VAN_TAT NVARCHAR2, P_NOI_DUNG NVARCHAR2) AS
  BEGIN
    P_VAL:='CL';
    IF P_STT IS NULL THEN
      INSERT INTO LICH_CONG_TAC(MA_CAN_BO,NGAY_CONG_TAC,NGAY_KET_THUC,VAN_TAT,NOI_DUNG_CONG_VIEC)
                  VALUES(P_MA_CAN_BO,P_NGAY_CONG_TAC,P_NGAY_KET_THUC,P_VAN_TAT,P_NOI_DUNG);
      P_VAL:='TC';
    ELSE
      UPDATE LICH_CONG_TAC SET NGAY_CONG_TAC=P_NGAY_CONG_TAC,
                               NGAY_KET_THUC=P_NGAY_KET_THUC,
                               VAN_TAT      =P_VAN_TAT,
                               NOI_DUNG_CONG_VIEC=P_NOI_DUNG;
     P_VAL:='TC';
   END IF;
END;
END TNMT_LICH_CONG_TAC;
/

prompt
prompt Creating package body TNMT_QUANLYNHANUOC
prompt ========================================
prompt
CREATE OR REPLACE PACKAGE BODY HAI.TNMT_QUANLYNHANUOC IS
--============== lop tap huan
PROCEDURE SELECT_LOP_TAP_HUANS(P_CUR IN OUT MYCUR, P_MA_CO_QUAN NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT A.*,B.TEN_LOP_TAP_HUAN FROM TT_LOP_TAP_HUAN A, DM_LOP_TAP_HUAN B
    WHERE A.MA_LOP_TAP_HUAN=B.MA_LOP_TAP_HUAN
    AND (B.MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL);
  END;
--============== llop tap huan
PROCEDURE SELECT_LOP_TAP_HUAN(P_CUR IN OUT MYCUR, P_MA_LOP_TAP_HUAN INT,P_NGAY_MO_LOP NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT A.* FROM TT_LOP_TAP_HUAN A
    WHERE A.MA_LOP_TAP_HUAN=P_MA_LOP_TAP_HUAN
    AND A.NGAY_MO_LOP=P_NGAY_MO_LOP;
  END;
--============== select lop tap huan
PROCEDURE SELECT_DM_LOP_TAP_HUAN(P_CUR IN OUT MYCUR,P_MA_CO_QUAN NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT A.MA_LOP_TAP_HUAN, A.TEN_LOP_TAP_HUAN FROM DM_LOP_TAP_HUAN A
    WHERE (A.MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL);
  END;
--============== update lop tap huan
PROCEDURE UPDATE_LOP_TAP_HUAN(P_VAL OUT NVARCHAR2, P_UPDATE_TYPE NVARCHAR2,P_MA_LOP_TAP_HUAN NVARCHAR2,
                              P_NGAY_MO_LOP NVARCHAR2, P_SO_LUONG NVARCHAR2,P_SO_LUONG_DI_HOC NVARCHAR2,
                              P_GHI_CHU NVARCHAR2, P_TRANG_THAI NVARCHAR2) AS
  V_KT INT;
  BEGIN
    P_VAL:='CL';
    IF P_UPDATE_TYPE='THEM_MOI' THEN
      SELECT COUNT(MA_LOP_TAP_HUAN) INTO V_KT FROM TT_LOP_TAP_HUAN
      WHERE MA_LOP_TAP_HUAN=P_MA_LOP_TAP_HUAN AND NGAY_MO_LOP=P_NGAY_MO_LOP;
      IF V_KT=0 THEN
        INSERT INTO TT_LOP_TAP_HUAN(MA_LOP_TAP_HUAN,NGAY_MO_LOP,SO_LUONG,SO_LUONG_DI_HOC,GHI_CHU,TRANG_THAI)
                         VALUES(P_MA_LOP_TAP_HUAN,P_NGAY_MO_LOP,P_SO_LUONG,P_SO_LUONG_DI_HOC,P_GHI_CHU,P_TRANG_THAI);
        P_VAL:='TC';
      ELSE
        P_VAL:='TON_TAI';
      END IF;
    ELSE
      UPDATE TT_LOP_TAP_HUAN SET SO_LUONG=P_SO_LUONG,
                              SO_LUONG_DI_HOC=P_SO_LUONG_DI_HOC,
                              GHI_CHU=P_GHI_CHU,
                              TRANG_THAI=P_TRANG_THAI
             WHERE MA_LOP_TAP_HUAN=P_MA_LOP_TAP_HUAN
             AND NGAY_MO_LOP=P_NGAY_MO_LOP;
      P_VAL:='TC';
    END IF;
  END;  
--============== delete lop tap huan
PROCEDURE DELETE_LOP_TAP_HUAN(P_VAL OUT NVARCHAR2, P_NGUOI_XOA INT,P_MA_LOP_TAP_HUAN INT, P_NGAY_MO_LOP NVARCHAR2) AS
  BEGIN
    IF P_NGUOI_XOA='0' THEN
      DELETE FROM TT_LOP_TAP_HUAN WHERE MA_LOP_TAP_HUAN=P_MA_LOP_TAP_HUAN AND NGAY_MO_LOP=P_NGAY_MO_LOP;
      P_VAL:='TC';
    ELSE 
      P_VAL:='CL';
    END IF;
  END;               
END TNMT_QUANLYNHANUOC;
/

prompt
prompt Creating package body TNMT_TEST
prompt ===============================
prompt
create or replace package body hai.TNMT_TEST wrapped
a000000
367
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
1905 566
Mb760zsUZPoMOigu6u2RAGxt0Dgwg82TDPHrfC8BzV7S/72N7FFU02VcGDtX4rTCLUNrwiXx
pBfuOXasbAzcFS7I5FweCxJAR2mOMIItGisK+O1IHj2JFEXIRhUt42Tdk/9EsCoHoMuswsVm
q3iUAuzJIQaskVjOUHE+H1KX/xMJoi5EPtZR7sgPQ9nijq3zzKjq8zmKN8OzWMMgxGf8po9a
t4n6nabpoYj1i7BRJwcOv+FduYHiLClP43eO0KbPetmkhaRk5ww2JLeOy6U5U3Yq6uZ1teT5
KiEH3skgnu7QliS8EPmRVuhWp/umsFlZ+dRssOWFN8dif2fhzXVlEF1Il5c1rrNL4k3go68I
gztun4NbO/7tHW17AaFgMmTReg6B6GLgLj8aPZ+I0POykZ1erZxC3e/YWuloS6JzImObwyfm
cREZ3BtdxWYJnp8bz7CEQHBhNYcoCO8vn+Z6KKXT1ATJefqvR/mmSTb7WanUZetxgMAdJaU3
kZItlBoLAM2KS6WGNVVWav/ZmB1G8dCT90D4OIa5WUpTHY5/M/FjH/yeiJjxvo1d456nRu/t
NUkwXtQ2wWqF1JJxiVGaXw8M5Tkro+5TkaEIRB1xtgqueI7cJEs8dD20I4KnISWaaRj2Uezy
rgDZOeKLJBctAkrxKKwfwjNa5dMm5IofMMVbBVjSkXBo01+LZpwK0oezxANNEQSayjDElw84
dq8M5Er+LpMlJ9Y1mvKaVVOhB81pNpugtNcQmlDU54R5n+Vgd/oJK+sYvwefp+gjXwOotNRI
8Ht2JwURreDwf2PEnxDU613Nlk9mr866iw5rDd8BY9PN3z3Qf4oo1FbhIZMP9ukUat22GB6C
4qyINU7ENPltt0FAnL0ZiOwi2gwSumbQQPia+BIP6sS97UByxRlrqT3rPWRPRypDcCG0HUdl
hLBCmDyhjm3+zQkkQoonfTMzgpfF096P4IjRWNF7eM24lGvZXXykBLHxlJzx6RX8z5A/EuLo
8htxfb7Ks5O4+Q8bDtVdAhgignlKQ/LKrre969zPk0lZMEd9NnmAgRBCGvJQ2FtYcHQNY8j6
CQTU6uc5pcU5XuzklJ4VRJ2AoX0RdlapM3xJG6CbmPB1fCYIkT44Sd3xjkVkoV4E9Q9vlGEa
Zy4pK/GsO7GOYNPqEZ5V4HMqmk3CnMwfzoJOrm2Zsg2TMu47RE75QN/bH1cQ/K0yQJ1IgO1r
vxIMGw5hnStCzl1ESOTEHxz9mORWiH8f62hiJTtl5PNzOERRTh5EJl+V4xk5Y6tSqrJIYmCL
iCTj007VAID9F3/Sq+BK3K62xR6Rxqx7MCJYZQDLr8K4/jpM+lEZxrUyitbqnaYk08Sm
/

prompt
prompt Creating package body TNMT_THANHTRABAOCAO
prompt =========================================
prompt
CREATE OR REPLACE PACKAGE BODY HAI.TNMT_THANHTRABAOCAO IS
--===========//// BAO CAO CHUYEN NGANH TNMT DANH SACH//=============--
PROCEDURE BAO_CAO_CHUYEN_NGANH_LIST(P_CUR IN OUT MYCUR,P_TU_NGAY NVARCHAR2,P_DEN_NGAY NVARCHAR2,P_MA_CO_QUAN NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT KQ.* FROM(SELECT A.*,B.TEN_KE_HOACH_TT,C.TEN_DOAN_TT FROM TT_THANH_TRA A, TT_KE_HOACH_THANH_TRA B,TT_DOAN_THANH_TRA C
    WHERE A.MA_KE_HOACH_TT=B.MA_KE_HOACH_TT
    AND A.MA_DOAN_TT=C.MA_DOAN_TT
    UNION SELECT A.*,NULL AS TEN_KE_HOACH_TT,NULL AS TEN_DOAN_THANH_TRA FROM TT_THANH_TRA A
    WHERE SO_THANH_TRA NOT IN(SELECT SO_THANH_TRA FROM TT_THANH_TRA A, TT_KE_HOACH_THANH_TRA B,TT_DOAN_THANH_TRA C
    WHERE A.MA_KE_HOACH_TT=B.MA_KE_HOACH_TT
    AND A.MA_DOAN_TT=C.MA_DOAN_TT)) KQ
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (KQ.MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL);
  END;
--===========//// BAO CAO LINH VUC DANH SACH//=============--
PROCEDURE BAO_CAO_LINH_VUC_LIST(P_CUR IN OUT MYCUR,P_TU_NGAY NVARCHAR2,P_DEN_NGAY NVARCHAR2,P_MA_LINH_VUC NVARCHAR2,P_THAM_NHUNG INT, P_THANH_TRA_LAI INT, P_MA_CO_QUAN NVARCHAR2) AS
  BEGIN
    IF P_THAM_NHUNG=1 AND P_THANH_TRA_LAI!=1 THEN
      OPEN P_CUR FOR SELECT KQ.* FROM(SELECT A.*,B.TEN_KE_HOACH_TT,C.TEN_DOAN_TT FROM TT_THANH_TRA_LINH_VUC A, TT_KE_HOACH_THANH_TRA B,TT_DOAN_THANH_TRA C
      WHERE A.MA_KE_HOACH_TT=B.MA_KE_HOACH_TT
      AND A.MA_DOAN_TT=C.MA_DOAN_TT
      UNION SELECT A.*,NULL AS TEN_KE_HOACH_TT,NULL AS TEN_DOAN_THANH_TRA FROM TT_THANH_TRA_LINH_VUC A
      WHERE SO_THANH_TRA NOT IN(SELECT SO_THANH_TRA FROM TT_THANH_TRA_LINH_VUC A, TT_KE_HOACH_THANH_TRA B,TT_DOAN_THANH_TRA C
      WHERE A.MA_KE_HOACH_TT=B.MA_KE_HOACH_TT
      AND A.MA_DOAN_TT=C.MA_DOAN_TT)) KQ
      WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
      AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
      AND (KQ.SO_TIEN_THAM_NHUNG IS NOT NULL OR KQ.TAI_SAN_THAM_NHUNG IS NOT NULL 
          OR KQ.TS_QUY_DOI_THANH_TIEN IS NOT NULL OR KQ.DAT_THAM_NHUNG IS NOT NULL)
      AND (KQ.MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
      AND (KQ.MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL);
   ELSIF P_THANH_TRA_LAI=1 AND P_THAM_NHUNG!=1 THEN
      OPEN P_CUR FOR SELECT DISTINCT KQ.SO_THANH_TRA,KQ.* FROM(SELECT A.*,B.TEN_KE_HOACH_TT,C.TEN_DOAN_TT FROM TT_THANH_TRA_LINH_VUC A, TT_KE_HOACH_THANH_TRA B,TT_DOAN_THANH_TRA C
      WHERE A.MA_KE_HOACH_TT=B.MA_KE_HOACH_TT
      AND A.MA_DOAN_TT=C.MA_DOAN_TT
      UNION SELECT A.*,NULL AS TEN_KE_HOACH_TT,NULL AS TEN_DOAN_THANH_TRA FROM TT_THANH_TRA_LINH_VUC A
      WHERE SO_THANH_TRA NOT IN(SELECT SO_THANH_TRA FROM TT_THANH_TRA_LINH_VUC A, TT_KE_HOACH_THANH_TRA B,TT_DOAN_THANH_TRA C
      WHERE A.MA_KE_HOACH_TT=B.MA_KE_HOACH_TT
      AND A.MA_DOAN_TT=C.MA_DOAN_TT)) KQ,TT_THANH_TRA_LAI TTL
      WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
      AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
      AND (KQ.MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
      AND (KQ.MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
      AND KQ.SO_THANH_TRA=TTL.SO_THANH_TRA;
   ELSIF P_THAM_NHUNG=1 AND P_THANH_TRA_LAI=1 THEN
      OPEN P_CUR FOR SELECT DISTINCT KQ.SO_THANH_TRA,KQ.* FROM(SELECT A.*,B.TEN_KE_HOACH_TT,C.TEN_DOAN_TT FROM TT_THANH_TRA_LINH_VUC A, TT_KE_HOACH_THANH_TRA B,TT_DOAN_THANH_TRA C
      WHERE A.MA_KE_HOACH_TT=B.MA_KE_HOACH_TT
      AND A.MA_DOAN_TT=C.MA_DOAN_TT
      UNION SELECT A.*,NULL AS TEN_KE_HOACH_TT,NULL AS TEN_DOAN_THANH_TRA FROM TT_THANH_TRA_LINH_VUC A
      WHERE SO_THANH_TRA NOT IN(SELECT SO_THANH_TRA FROM TT_THANH_TRA_LINH_VUC A, TT_KE_HOACH_THANH_TRA B,TT_DOAN_THANH_TRA C
      WHERE A.MA_KE_HOACH_TT=B.MA_KE_HOACH_TT
      AND A.MA_DOAN_TT=C.MA_DOAN_TT)) KQ,TT_THANH_TRA_LAI TTL
      WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
      AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
      AND (KQ.MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
      AND (KQ.SO_TIEN_THAM_NHUNG IS NOT NULL OR KQ.TS_QUY_DOI_THANH_TIEN IS NOT NULL
          OR KQ.TAI_SAN_THAM_NHUNG IS NOT NULL OR KQ.DAT_THAM_NHUNG IS NOT NULL
          OR TTL.SO_TIEN_THAM_NHUNG IS NOT NULL OR TTL.TAI_SAN_THAM_NHUNG IS NOT NULL
          OR TTL.TS_QUY_DOI_THANH_TIEN IS NOT NULL OR TTL.DAT_THAM_NHUNG IS NOT NULL)
      AND (KQ.MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
      AND KQ.SO_THANH_TRA=TTL.SO_THANH_TRA; 
   ELSE
     OPEN P_CUR FOR SELECT KQ.* FROM(SELECT A.*,B.TEN_KE_HOACH_TT,C.TEN_DOAN_TT FROM TT_THANH_TRA_LINH_VUC A, TT_KE_HOACH_THANH_TRA B,TT_DOAN_THANH_TRA C
      WHERE A.MA_KE_HOACH_TT=B.MA_KE_HOACH_TT
      AND A.MA_DOAN_TT=C.MA_DOAN_TT
      UNION SELECT A.*,NULL AS TEN_KE_HOACH_TT,NULL AS TEN_DOAN_THANH_TRA FROM TT_THANH_TRA_LINH_VUC A
      WHERE SO_THANH_TRA NOT IN(SELECT SO_THANH_TRA FROM TT_THANH_TRA_LINH_VUC A, TT_KE_HOACH_THANH_TRA B,TT_DOAN_THANH_TRA C
      WHERE A.MA_KE_HOACH_TT=B.MA_KE_HOACH_TT
      AND A.MA_DOAN_TT=C.MA_DOAN_TT)) KQ
      WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
      AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
      AND (KQ.MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
      AND (KQ.MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL);
   END IF;
  END;
--==========// BAO CAO CHUYEN NGANH TNMT
PROCEDURE BAO_CAO_CHUYEN_NGANH(P_CUR IN OUT MYCUR,P_TU_NGAY NVARCHAR2,P_DEN_NGAY NVARCHAR2,P_MA_CO_QUAN NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT KQ.*,TEN_CO_QUAN FROM (SELECT TS_THANH_TRA,CO_DOAN_THANH_TRA,CO_KE_HOACH,TS_CA_NHAN,TT_CA_NHAN,KT_CA_NHAN,
    TS_TO_CHUC,TT_TO_CHUC,KT_TO_CHUC,LBB_CA_NHAN,QD_CA_NHAN,LBB_TO_CHUC, QD_TO_CHUC,SO_TIEN_TICH_THU,
    SO_TIEN_TIEU_HUY,SO_TIEN_XU_PHAT_CN,SO_TIEN_XU_PHAT_TC,SO_TIEN_TICH_THU_CN,
    SO_TIEN_TIEU_HUY_CN, SO_TIEN_TICH_THU_TC,SO_TIEN_TIEU_HUY_TC, A.MA_CO_QUAN FROM
    --tong so thanh tra
    (SELECT COUNT(SO_THANH_TRA) AS TS_THANH_TRA, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) A,
    --co doan thanh tra
    (SELECT COUNT(SO_THANH_TRA) AS CO_DOAN_THANH_TRA, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND MA_DOAN_TT IS NOT NULL
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) B,
    --co ke hoach
    (SELECT COUNT(SO_THANH_TRA) AS CO_KE_HOACH, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND MA_KE_HOACH_TT IS NOT NULL
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) C,
    --tong so ca nhan
    (SELECT COUNT(SO_THANH_TRA) AS TS_CA_NHAN, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND LOAI_HINH_DOANH_NGHIEP=1
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) D,
    --thanh tra ca nhan 
    (SELECT COUNT(SO_THANH_TRA) AS TT_CA_NHAN, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND LOAI_HINH_DOANH_NGHIEP=1
    AND LOAI_HINH_THANH_TRA='TT'
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) E,
    --kiem tra ca nhan
    (SELECT COUNT(SO_THANH_TRA) AS KT_CA_NHAN, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND LOAI_HINH_DOANH_NGHIEP=1
    AND LOAI_HINH_THANH_TRA='KT'
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) F,
    --tong so to chuc
    --tong so ca nhan
    (SELECT COUNT(SO_THANH_TRA) AS TS_TO_CHUC, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND LOAI_HINH_DOANH_NGHIEP=0
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) G,
    --thanh tra to chuc
    (SELECT COUNT(SO_THANH_TRA) AS TT_TO_CHUC, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND LOAI_HINH_DOANH_NGHIEP=0
    AND LOAI_HINH_THANH_TRA='TT'
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) H,
    --kiem tra to chuc
    (SELECT COUNT(SO_THANH_TRA) AS KT_TO_CHUC, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND LOAI_HINH_DOANH_NGHIEP=0
    AND LOAI_HINH_THANH_TRA='KT'
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) I,
    --tong so ca nhan vi pham
    (SELECT COUNT(SO_THANH_TRA) AS LBB_CA_NHAN, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND LOAI_HINH_DOANH_NGHIEP=1--0 to chuc, 1 ca nhan
    AND HINH_THUC_XU_PHAT=0 --0 lap bien ban 1 ra quyet dinh
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) J,
    --tong to chuc vi pham
    (SELECT COUNT(SO_THANH_TRA) AS LBB_TO_CHUC, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND LOAI_HINH_DOANH_NGHIEP=0--0 to chuc, 1 ca nhan
    AND HINH_THUC_XU_PHAT=0--0 lap bien ban 1 ra quyet dinh
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) K,
    --tong ca nhan ra quyet dinh phat
    (SELECT COUNT(SO_THANH_TRA) AS QD_CA_NHAN, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND LOAI_HINH_DOANH_NGHIEP=1--0 to chuc, 1 ca nhan
    AND HINH_THUC_XU_PHAT=1--0 lap bien ban 1 ra quyet dinh
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) L,
    --tong to chuc ra quyet dinh phat
    (SELECT COUNT(SO_THANH_TRA) AS QD_TO_CHUC, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND LOAI_HINH_DOANH_NGHIEP=0--0 to chuc, 1 ca nhan
    AND HINH_THUC_XU_PHAT=1--0 lap bien ban 1 ra quyet dinh
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) M,
    --so tien tich thu
    (SELECT SUM(SO_TIEN_TICH_THU) AS SO_TIEN_TICH_THU, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) N,
     --so tien tich thu ca nhan
    (SELECT SUM(SO_TIEN_TICH_THU) AS SO_TIEN_TICH_THU_CN, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND LOAI_HINH_DOANH_NGHIEP=1
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) O,
    --so tien tich thu to chuc
    (SELECT SUM(SO_TIEN_TICH_THU) AS SO_TIEN_TICH_THU_TC, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND LOAI_HINH_DOANH_NGHIEP=0
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) P,
    --so tien tieu huy
    (SELECT SUM(SO_TIEN_TIEU_HUY) AS SO_TIEN_TIEU_HUY, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) Q,
    --so tien tieu huy ca nhan
    (SELECT SUM(SO_TIEN_TIEU_HUY) AS SO_TIEN_TIEU_HUY_CN, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND LOAI_HINH_DOANH_NGHIEP=1--0 to chuc, 1 ca nhan
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) R,
    --so tien tieu huy to chuc
    (SELECT SUM(SO_TIEN_TIEU_HUY) AS SO_TIEN_TIEU_HUY_TC, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND LOAI_HINH_DOANH_NGHIEP=0--0 to chuc, 1 ca nhan
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) S,
    --tong so tien xu phat ca nhan
    (SELECT SUM(SO_TIEN_XU_PHAT) AS SO_TIEN_XU_PHAT_CN, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND LOAI_HINH_DOANH_NGHIEP=1--0 to chuc, 1 ca nhan
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) T,
    --tong so tien xu phat to chuc
    (SELECT SUM(SO_TIEN_XU_PHAT) AS SO_TIEN_XU_PHAT_TC, MA_CO_QUAN FROM TT_THANH_TRA 
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND LOAI_HINH_DOANH_NGHIEP=0--0 to chuc, 1 ca nhan
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    GROUP BY MA_CO_QUAN) U
    WHERE A.MA_CO_QUAN=B.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=C.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=D.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=E.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=F.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=G.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=H.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=I.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=J.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=K.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=L.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=M.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=N.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=O.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=P.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=Q.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=R.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=S.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=T.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=U.MA_CO_QUAN(+)) KQ, CB_CO_QUAN W
    WHERE KQ.MA_CO_QUAN=W.MA_CO_QUAN;
  END; 
PROCEDURE BAO_CAO_LINH_VUC(P_CUR IN OUT MYCUR,P_TU_NGAY NVARCHAR2,P_DEN_NGAY NVARCHAR2,P_MA_LINH_VUC NVARCHAR2,P_MA_CO_QUAN NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT KQ.*,TEN_CO_QUAN FROM (SELECT TRONG_KY_THANH_TRA,KY_TRUOC_CHUYEN_SANG,TT_THEO_KE_HOACH,TT_KO_THEO_KE_HOACH,
    KIEN_NGHI_THU_HOI, SO_TIEN_THU_HOI,KIEN_NGHI_KHAC, SO_TIEN_KHAC,XU_LY_HANH_CHINH_TO_CHUC,
    XU_LY_HANH_CHINH_CA_NHAN,CHUYEN_VU_VIEC_QUA_CQ,CHUYEN_DOI_TUONG_QUA_CQ,TS_VI_PHAM,
    DAT_PHAI_THU, SO_TIEN_PHAI_THU,DAT_DA_THU,SO_TIEN_DA_THU,DAT_LAN_CHIEM,
    CAP_SAI_DOI_TUONG,CAP_TRAI_THAM_QUYEN, CAP_GCN_SAI,SAI_QUY_DINH,SAI_MUC_DICH,
    BO_HOANG_HOA,VI_PHAM_KHAC, A.MA_CO_QUAN FROM
    --trong ky thanh tra
    (SELECT COUNT(SO_THANH_TRA) AS TRONG_KY_THANH_TRA,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) A,
    --ky truoc chuyen sang
    (SELECT COUNT(SO_THANH_TRA) AS KY_TRUOC_CHUYEN_SANG,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) B,
    --theo ke hoach
    (SELECT COUNT(SO_THANH_TRA) AS TT_THEO_KE_HOACH,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND MA_KE_HOACH_TT IS NOT NULL
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) C,
    --theo ke hoach
    (SELECT COUNT(SO_THANH_TRA) AS TT_KO_THEO_KE_HOACH,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND MA_KE_HOACH_TT IS NULL
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) D,
    --kien nghi thu hoi
    (SELECT SUM(KIEN_NGHI_THU_HOI) AS KIEN_NGHI_THU_HOI,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) E,
    --so tien thu hoi
    (SELECT SUM(SO_TIEN_THU_HOI) AS SO_TIEN_THU_HOI,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) F,
    --kien nghi khac
    (SELECT SUM(KIEN_NGHI_KHAC) AS KIEN_NGHI_KHAC,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) G,
    --so tien khac
    (SELECT SUM(SO_TIEN_KHAC) AS SO_TIEN_KHAC,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) H,
    --xu ly hanh chinh to chuc
    (SELECT COUNT(SO_THANH_TRA) AS XU_LY_HANH_CHINH_TO_CHUC,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND LOAI_HINH_DOANH_NGHIEP=0--0: TO CHUC, 1: CA NHAN
    AND KIEN_NGHI_XU_LY = 0 --0: hanh chinh, 1: chuyen vu, 2: chuyen doi tuong
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) I,
    --xu ly hanh chinh ca nhan
    (SELECT COUNT(SO_THANH_TRA) AS XU_LY_HANH_CHINH_CA_NHAN, MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND LOAI_HINH_DOANH_NGHIEP=1--0: TO CHUC, 1: CA NHAN
    AND KIEN_NGHI_XU_LY = 0 --0: hanh chinh, 1: chuyen vu, 2: chuyen doi tuong
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) J,
    --chuyen vu viec qua co quan khac
    (SELECT COUNT(SO_THANH_TRA) AS CHUYEN_VU_VIEC_QUA_CQ,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND KIEN_NGHI_XU_LY = 1 --0: hanh chinh, 1: chuyen vu, 2: chuyen doi tuong
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) K,
    --chuyen doi tuong qua co quan khac
    (SELECT COUNT(SO_THANH_TRA) AS CHUYEN_DOI_TUONG_QUA_CQ,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND KIEN_NGHI_XU_LY = 2 --0: hanh chinh, 1: chuyen vu, 2: chuyen doi tuong
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) L,
    --tong so vi pham
    (SELECT COUNT(SO_THANH_TRA) AS TS_VI_PHAM, MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND KIEN_NGHI_XU_LY IS NOT NULL --0: hanh chinh, 1: chuyen vu, 2: chuyen doi tuong
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) M,
    --dat phai thu
    (SELECT SUM(DAT_PHAI_THU) AS DAT_PHAI_THU, MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) N,
     --so tien phai thu
    (SELECT SUM(SO_TIEN_PHAI_THU) AS SO_TIEN_PHAI_THU, MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) O,
    --dat da thu
    (SELECT SUM(DAT_DA_THU) AS DAT_DA_THU, MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) P,
    --so tien da thu
    (SELECT SUM(SO_TIEN_DA_THU) AS SO_TIEN_DA_THU, MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) Q,
    --ket qua xu ly hanh chinh to chuc
    (SELECT COUNT(SO_THANH_TRA) AS KET_QUA_XU_LY_HANH_CHINH_TC,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND LOAI_HINH_DOANH_NGHIEP=0--0: TO CHUC, 1: CA NHAN
    AND KET_QUA_XU_LY = 0 --0: hanh chinh, 1: chuyen vu, 2: chuyen doi tuong
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) R,
    --ket qua xu ly hanh chinh ca nhan
    (SELECT COUNT(SO_THANH_TRA) AS KET_QUA_XU_LY_HANH_CHINH_CN, MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND LOAI_HINH_DOANH_NGHIEP=1--0: TO CHUC, 1: CA NHAN
    AND KET_QUA_XU_LY = 0 --0: hanh chinh, 1: chuyen vu, 2: chuyen doi tuong
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) S,
    --khoi to vu viec qua co quan khac
    (SELECT COUNT(SO_THANH_TRA) AS KT_VU_VIEC,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND KET_QUA_XU_LY = 1 --0: hanh chinh, 1: chuyen vu, 2: chuyen doi tuong
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) T,
    --khoi to doi tuong qua co quan khac
    (SELECT COUNT(SO_THANH_TRA) AS KT_DOI_TUONG,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND KIEN_NGHI_XU_LY = 2 --0: hanh chinh, 1: chuyen vu, 2: chuyen doi tuong
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) U,
    --dang vi pham dat lan chiem
    (SELECT SUM(VI_PHAM) AS DAT_LAN_CHIEM,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC A,TT_THANH_TRA_CO_DANG_VI_PHAM B
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND A.SO_THANH_TRA=B.SO_THANH_TRA
    AND B.MA_DANG_VI_PHAM=1 --dat lan chiem
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) V,
    --dang vi pham cap sai doi tuong
    (SELECT SUM(VI_PHAM) AS CAP_SAI_DOI_TUONG,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC A,TT_THANH_TRA_CO_DANG_VI_PHAM B
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND A.SO_THANH_TRA=B.SO_THANH_TRA
    AND B.MA_DANG_VI_PHAM=2 --dat cap sai doi tuong
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) W,
    --dang vi pham cap trai tham quyen
    (SELECT SUM(VI_PHAM) AS CAP_TRAI_THAM_QUYEN,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC A,TT_THANH_TRA_CO_DANG_VI_PHAM B
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND A.SO_THANH_TRA=B.SO_THANH_TRA
    AND B.MA_DANG_VI_PHAM=3 --dat cap trai tham quyen
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) X,
    --dang vi pham cap GCN sai
    (SELECT SUM(VI_PHAM) AS CAP_GCN_SAI,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC A,TT_THANH_TRA_CO_DANG_VI_PHAM B
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND A.SO_THANH_TRA=B.SO_THANH_TRA
    AND B.MA_DANG_VI_PHAM=4 --dat cap GCN sai
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) Y,
    --dang vi pham chuyen nhuong, cho thue khong dung quy dinh
    (SELECT SUM(VI_PHAM) AS SAI_QUY_DINH,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC A,TT_THANH_TRA_CO_DANG_VI_PHAM B
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND A.SO_THANH_TRA=B.SO_THANH_TRA
    AND B.MA_DANG_VI_PHAM=5 --chuyen nhuong, cho thue khong dung quy dinh
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) Z,
    --dang vi pham su dung dat khong dung muc dich
    (SELECT SUM(VI_PHAM) AS SAI_MUC_DICH,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC A,TT_THANH_TRA_CO_DANG_VI_PHAM B
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND A.SO_THANH_TRA=B.SO_THANH_TRA
    AND B.MA_DANG_VI_PHAM=6 --su dung khong dung muc dich
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) AA,
    --dang vi pham bo hoang hoa
    (SELECT SUM(VI_PHAM) AS BO_HOANG_HOA,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC A,TT_THANH_TRA_CO_DANG_VI_PHAM B
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND A.SO_THANH_TRA=B.SO_THANH_TRA
    AND B.MA_DANG_VI_PHAM=7 --bo hoang hoa
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) AB,
    --dang vi pham khac
    (SELECT SUM(VI_PHAM) AS VI_PHAM_KHAC,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC A,TT_THANH_TRA_CO_DANG_VI_PHAM B
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND A.SO_THANH_TRA=B.SO_THANH_TRA
    AND B.MA_DANG_VI_PHAM=8 --vi pham khac
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) AC
    WHERE A.MA_CO_QUAN=B.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=C.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=D.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=E.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=F.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=G.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=H.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=I.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=J.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=K.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=L.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=M.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=N.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=O.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=P.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=Q.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=R.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=S.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=T.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=U.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=V.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=W.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=X.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=Y.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=Z.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=AA.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=AB.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=AC.MA_CO_QUAN(+)) KQ, CB_CO_QUAN W
    WHERE KQ.MA_CO_QUAN=W.MA_CO_QUAN;
  END;
--BAO CAO THANH TRA LAI
PROCEDURE BAO_CAO_THANH_TRA_LAI(P_CUR IN OUT MYCUR,P_TU_NGAY NVARCHAR2,P_DEN_NGAY NVARCHAR2,P_MA_LINH_VUC NVARCHAR2,P_MA_CO_QUAN NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT KQ.*,TEN_CO_QUAN FROM (SELECT TRONG_KY_THANH_TRA,KY_TRUOC_CHUYEN_SANG,
     VI_PHAM_TRINH_TU,NOI_DUNG_KL_KO_PHU_HOP,SAI_LAM_KHI_KL,CO_Y_KL_SAI,VI_PHAM_NGHIEM_TRONG,
     SO_DON_VI_THANH_TRA,TS_VI_PHAM,SO_TIEN_VI_PHAM,DAT_VI_PHAM,KIEN_NGHI_THU_HOI,SO_TIEN_THU_HOI,
     KIEN_NGHI_KHAC,SO_TIEN_KHAC,KIEN_NGHI_XU_LY_HC_TO_CHUC,KIEN_NGHI_XU_LY_HC_CA_NHAN,
     KIEN_NGHI_CHUYEN_VU_VIEC,KIEN_NGHI_CHUYEN_DOI_TUONG, KET_QUA_XU_LY_HC_TO_CHUC,KET_QUA_XU_LY_HC_CA_NHAN,
     DA_KHOI_TO_VU_VIEC, DA_KHOI_TO_DOI_TUONG, A.MA_CO_QUAN FROM
    --trong ky thanh tra
    (SELECT COUNT(A1.SO_THANH_TRA) AS TRONG_KY_THANH_TRA,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC A1, TT_THANH_TRA_LAI A2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    AND A1.SO_THANH_TRA=A2.SO_THANH_TRA
    GROUP BY MA_CO_QUAN) A,
    --ky truoc chuyen sang
    (SELECT COUNT(B1.SO_THANH_TRA) AS KY_TRUOC_CHUYEN_SANG,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC B1, TT_THANH_TRA_LAI B2
    WHERE TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    AND B1.SO_THANH_TRA=B1.SO_THANH_TRA
    GROUP BY MA_CO_QUAN) B,
    --vi pham trinh tu
    (SELECT COUNT(C1.SO_THANH_TRA) AS VI_PHAM_TRINH_TU,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC C1, TT_THANH_TRA_LAI C2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND C1.SO_THANH_TRA=C2.SO_THANH_TRA
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    AND C2.LY_DO_THANH_TRA_LAI LIKE '%-1-%'--vi pham trinh tu
    GROUP BY MA_CO_QUAN) C,
    --noi dung ket luan khong phu hop
    (SELECT COUNT(D1.SO_THANH_TRA) AS NOI_DUNG_KL_KO_PHU_HOP,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC D1, TT_THANH_TRA_LAI D2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND D1.SO_THANH_TRA=D2.SO_THANH_TRA
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    AND D2.LY_DO_THANH_TRA_LAI LIKE '%-2-%'--noi dung ket luan khong phu hop
    GROUP BY MA_CO_QUAN) D,
    --sai lam khi ket luan
    (SELECT COUNT(E1.SO_THANH_TRA) AS SAI_LAM_KHI_KL,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC E1, TT_THANH_TRA_LAI E2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND E1.SO_THANH_TRA=E2.SO_THANH_TRA
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    AND E2.LY_DO_THANH_TRA_LAI LIKE '%-3-%'--sai lam khi ket luan
    GROUP BY MA_CO_QUAN) E,
    --co y lam sai
    (SELECT COUNT(F1.SO_THANH_TRA) AS CO_Y_KL_SAI,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC F1, TT_THANH_TRA_LAI F2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND F1.SO_THANH_TRA=F2.SO_THANH_TRA
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    AND F2.LY_DO_THANH_TRA_LAI LIKE '%-4-%'--co y lam sai
    GROUP BY MA_CO_QUAN) F,
     --vi pham nghiem trong
    (SELECT COUNT(G1.SO_THANH_TRA) AS VI_PHAM_NGHIEM_TRONG,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC G1, TT_THANH_TRA_LAI G2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND G1.SO_THANH_TRA=G2.SO_THANH_TRA
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    AND G2.LY_DO_THANH_TRA_LAI LIKE '%-5-%'--vi pham nghiem trong
    GROUP BY MA_CO_QUAN) G,
    --so don vi thanh tra
    (SELECT COUNT(SO_THANH_TRA) AS SO_DON_VI_THANH_TRA,MA_CO_QUAN FROM (SELECT DISTINCT H1.SO_THANH_TRA,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC H1, TT_THANH_TRA_LAI H2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND H1.SO_THANH_TRA=H2.SO_THANH_TRA
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL))
    GROUP BY MA_CO_QUAN) H,
     --co vi pham qua thanh tra
    (SELECT COUNT(I1.SO_THANH_TRA) AS TS_VI_PHAM,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC I1, TT_THANH_TRA_LAI I2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND I1.SO_THANH_TRA=I2.SO_THANH_TRA
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    AND I2.KIEN_NGHI_XU_LY IS NOT NULL--co vi pham
    GROUP BY MA_CO_QUAN) I,
     --so tien vi pham ve kinh te
    (SELECT SUM(J2.SO_TIEN_VI_PHAM) AS SO_TIEN_VI_PHAM,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC J1, TT_THANH_TRA_LAI J2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND J1.SO_THANH_TRA=J2.SO_THANH_TRA
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) J,
     --dat vi pham ve kinh te
    (SELECT SUM(K2.DAT_VI_PHAM) AS DAT_VI_PHAM,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC K1, TT_THANH_TRA_LAI K2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND K1.SO_THANH_TRA=K2.SO_THANH_TRA
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) K,
    --kien nghi thu hoi
    (SELECT SUM(L2.KIEN_NGHI_THU_HOI) AS KIEN_NGHI_THU_HOI,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC L1, TT_THANH_TRA_LAI L2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND L1.SO_THANH_TRA=L2.SO_THANH_TRA
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) L,
    --so tien thu hoi
    (SELECT SUM(M2.SO_TIEN_THU_HOI) AS SO_TIEN_THU_HOI,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC M1, TT_THANH_TRA_LAI M2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND M1.SO_THANH_TRA=M2.SO_THANH_TRA
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) M,
    --so tien khac
    (SELECT SUM(N2.KIEN_NGHI_KHAC) AS KIEN_NGHI_KHAC,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC N1, TT_THANH_TRA_LAI N2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND N1.SO_THANH_TRA=N2.SO_THANH_TRA
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) N,
    --so tien khac
    (SELECT SUM(O2.SO_TIEN_KHAC) AS SO_TIEN_KHAC,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC O1, TT_THANH_TRA_LAI O2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND O1.SO_THANH_TRA=O2.SO_THANH_TRA
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) O,
    --kien nghi xu ly hanh chinh to chuc
    (SELECT COUNT(P2.SO_THANH_TRA) AS KIEN_NGHI_XU_LY_HC_TO_CHUC,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC P1, TT_THANH_TRA_LAI P2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND P1.SO_THANH_TRA=P2.SO_THANH_TRA
    AND P2.KIEN_NGHI_XU_LY=0--xu ly hanh chinh
    AND P1.LOAI_HINH_DOANH_NGHIEP=0--to chuc, 1 ca nhan
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) P,
    --kien nghi xu ly hanh chinh ca nhan
    (SELECT COUNT(Q2.SO_THANH_TRA) AS KIEN_NGHI_XU_LY_HC_CA_NHAN,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC Q1, TT_THANH_TRA_LAI Q2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND Q1.SO_THANH_TRA=Q2.SO_THANH_TRA
    AND Q2.KIEN_NGHI_XU_LY=0--xu ly hanh chinh
    AND Q1.LOAI_HINH_DOANH_NGHIEP=1--to chuc, 1 ca nhan
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) Q,
    --kien nghi chuyen vu viec
    (SELECT COUNT(R2.SO_THANH_TRA) AS KIEN_NGHI_CHUYEN_VU_VIEC,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC R1, TT_THANH_TRA_LAI R2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND R1.SO_THANH_TRA=R2.SO_THANH_TRA
    AND R2.KIEN_NGHI_XU_LY=1--xu ly hanh chinh
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) R,
    --kien nghi chuyen doi tuong
    (SELECT COUNT(S2.SO_THANH_TRA) AS KIEN_NGHI_CHUYEN_DOI_TUONG,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC S1, TT_THANH_TRA_LAI S2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND S1.SO_THANH_TRA=S2.SO_THANH_TRA
    AND S2.KIEN_NGHI_XU_LY=2--xu ly hanh chinh
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) S,
     --so tien da thu
    (SELECT SUM(T2.SO_TIEN_DA_THU) AS SO_TIEN_DA_THU,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC T1, TT_THANH_TRA_LAI T2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND T1.SO_THANH_TRA=T2.SO_THANH_TRA
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) T,
     --dat da thu
    (SELECT SUM(U2.DAT_DA_THU) AS DAT_DA_THU,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC U1, TT_THANH_TRA_LAI U2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND U1.SO_THANH_TRA=U2.SO_THANH_TRA
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) U,
     --ket qua xu ly hanh chinh to chuc
    (SELECT COUNT(V2.SO_THANH_TRA) AS KET_QUA_XU_LY_HC_TO_CHUC,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC V1, TT_THANH_TRA_LAI V2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND V1.SO_THANH_TRA=V2.SO_THANH_TRA
    AND V2.KET_QUA_XU_LY=0--xu ly hanh chinh
    AND V1.LOAI_HINH_DOANH_NGHIEP=0--to chuc, 1 ca nhan
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) V,
    --ket qua xu ly hanh chinh to chuc
    (SELECT COUNT(W2.SO_THANH_TRA) AS KET_QUA_XU_LY_HC_CA_NHAN,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC W1, TT_THANH_TRA_LAI W2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND W1.SO_THANH_TRA=W2.SO_THANH_TRA
    AND W2.KET_QUA_XU_LY=0--xu ly hanh chinh
    AND W1.LOAI_HINH_DOANH_NGHIEP=1--to chuc, 1 ca nhan
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) W,
     --da khoi to vu viec
    (SELECT COUNT(X2.SO_THANH_TRA) AS DA_KHOI_TO_VU_VIEC,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC X1, TT_THANH_TRA_LAI X2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
   AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND X1.SO_THANH_TRA=X2.SO_THANH_TRA
    AND X2.KET_QUA_XU_LY=1--xu ly hanh chinh
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) X,
    --da khoi to doi tuong
    (SELECT COUNT(Y2.SO_THANH_TRA) AS DA_KHOI_TO_DOI_TUONG,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC Y1, TT_THANH_TRA_LAI Y2
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND Y1.SO_THANH_TRA=Y2.SO_THANH_TRA
    AND Y2.KET_QUA_XU_LY=2--xu ly hanh chinh
    AND Y1.LOAI_HINH_DOANH_NGHIEP=0--to chuc, 1 ca nhan
    AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
    GROUP BY MA_CO_QUAN) Y
    WHERE A.MA_CO_QUAN=B.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=C.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=D.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=E.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=F.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=G.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=H.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=I.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=J.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=K.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=L.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=M.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=N.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=O.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=P.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=Q.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=R.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=S.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=T.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=U.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=V.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=W.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=X.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=Y.MA_CO_QUAN(+)
   /* AND A.MA_CO_QUAN=Z.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=AA.MA_CO_QUAN(+)*/) KQ, CB_CO_QUAN W
    WHERE KQ.MA_CO_QUAN=W.MA_CO_QUAN;
  END;
PROCEDURE BAO_CAO_THANH_TRA_LAI_LIST(P_CUR IN OUT MYCUR,P_TU_NGAY NVARCHAR2,P_DEN_NGAY NVARCHAR2,P_MA_CO_QUAN NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT KQ1.* FROM (SELECT KQ.* FROM(SELECT A.*,B.TEN_KE_HOACH_TT,C.TEN_DOAN_TT FROM TT_THANH_TRA A, TT_KE_HOACH_THANH_TRA B,TT_DOAN_THANH_TRA C
    WHERE A.MA_KE_HOACH_TT=B.MA_KE_HOACH_TT
    AND A.MA_DOAN_TT=C.MA_DOAN_TT
    UNION SELECT A.*,NULL AS TEN_KE_HOACH_TT,NULL AS TEN_DOAN_THANH_TRA FROM TT_THANH_TRA A
    WHERE SO_THANH_TRA NOT IN(SELECT SO_THANH_TRA FROM TT_THANH_TRA_LINH_VUC A, TT_KE_HOACH_THANH_TRA B,TT_DOAN_THANH_TRA C
    WHERE A.MA_KE_HOACH_TT=B.MA_KE_HOACH_TT
    AND A.MA_DOAN_TT=C.MA_DOAN_TT)) KQ
    WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    AND (KQ.MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)) KQ1, TT_THANH_TRA_LAI D
    WHERE KQ1.SO_THANH_TRA=D.SO_THANH_TRA;
  END;
PROCEDURE BAO_CAO_THAM_NHUNG(P_CUR IN OUT MYCUR,P_TU_NGAY NVARCHAR2,P_DEN_NGAY NVARCHAR2,P_MA_LINH_VUC NVARCHAR2,P_MA_CO_QUAN NVARCHAR2) AS
  BEGIN
     OPEN P_CUR FOR SELECT KQ.*,TEN_CO_QUAN FROM (SELECT TS_VU_VIEC,TS_NGUOI_LIEN_QUAN,SO_TIEN_THAM_NHUNG, TS_QUY_DOI_THANH_TIEN,
                                                  DAT_THAM_NHUNG, KIEN_NGHI_THU_HOI, SO_TIEN_THU_HOI, KIEN_NGHI_KHAC, SO_TIEN_KHAC,
                                                  DAT_DA_THU, SO_TIEN_DA_THU,KIEN_NGHI_XU_LY_HC_TO_CHUC, KIEN_NGHI_XU_LY_HC_CA_NHAN,
                                                  KIEN_NGHI_CHUYEN_VU_VIEC, KIEN_NGHI_CHUYEN_DOI_TUONG, KET_QUA_XU_LY_HC_TO_CHUC,KET_QUA_XU_LY_HC_CA_NHAN,
                                                  KHOI_TO_VU_VIEC, KHOI_TO_DOI_TUONG, A.MA_CO_QUAN FROM
    --tong so vu viec
    (SELECT COUNT(SO_THANH_TRA) AS TS_VU_VIEC,MA_CO_QUAN FROM
      (SELECT SO_THANH_TRA,MA_CO_QUAN  FROM TT_THANH_TRA_LINH_VUC
      WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
      AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
      AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
      AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
      AND (SO_TIEN_THAM_NHUNG IS NOT NULL OR TS_QUY_DOI_THANH_TIEN IS NOT NULL OR DAT_THAM_NHUNG IS NOT NULL)
      UNION 
      SELECT A.SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LAI A, TT_THANH_TRA_LINH_VUC B
      WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
      AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
      AND A.SO_THANH_TRA=B.SO_THANH_TRA
      AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
      AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
      AND (A.SO_TIEN_THAM_NHUNG IS NOT NULL OR A.TAI_SAN_THAM_NHUNG IS NOT NULL 
           OR A.TS_QUY_DOI_THANH_TIEN IS NOT NULL OR A.DAT_THAM_NHUNG IS NOT NULL))
     GROUP BY MA_CO_QUAN) A,
    --so nguoi lien quan
    (SELECT SUM(SO_NGUOI_LIEN_QUAN) AS TS_NGUOI_LIEN_QUAN,MA_CO_QUAN  FROM
      (SELECT SO_THANH_TRA,SO_NGUOI_LIEN_QUAN,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
      WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
      AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
      AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
      AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
      AND (SO_TIEN_THAM_NHUNG IS NOT NULL OR TAI_SAN_THAM_NHUNG IS NOT NULL OR TS_QUY_DOI_THANH_TIEN IS NOT NULL OR DAT_THAM_NHUNG IS NOT NULL)
      UNION
      SELECT A.SO_THANH_TRA,SO_NGUOI_LIEN_QUAN,MA_CO_QUAN FROM TT_THANH_TRA_LAI A, TT_THANH_TRA_LINH_VUC B
      WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
      AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
      AND A.SO_THANH_TRA=B.SO_THANH_TRA
      AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
      AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
      AND (A.SO_TIEN_THAM_NHUNG IS NOT NULL OR A.TAI_SAN_THAM_NHUNG IS NOT NULL OR A.TS_QUY_DOI_THANH_TIEN IS NOT NULL OR A.DAT_THAM_NHUNG IS NOT NULL))
    GROUP BY MA_CO_QUAN) B,
    --so tien tham nhung
    (SELECT SUM(SO_TIEN_THAM_NHUNG) AS SO_TIEN_THAM_NHUNG,MA_CO_QUAN  FROM 
       (SELECT SO_THANH_TRA,SO_TIEN_THAM_NHUNG,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND (SO_TIEN_THAM_NHUNG IS NOT NULL)
        UNION
        SELECT A.SO_THANH_TRA,A.SO_TIEN_THAM_NHUNG,MA_CO_QUAN FROM TT_THANH_TRA_LAI A, TT_THANH_TRA_LINH_VUC B
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND A.SO_THANH_TRA=B.SO_THANH_TRA
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND (A.SO_TIEN_THAM_NHUNG IS NOT NULL))
    GROUP BY MA_CO_QUAN) C,
    -- tai san quy doi thanh tien
    (SELECT SUM(TS_QUY_DOI_THANH_TIEN) AS TS_QUY_DOI_THANH_TIEN,MA_CO_QUAN  FROM
        (SELECT SO_THANH_TRA,TS_QUY_DOI_THANH_TIEN,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND (TS_QUY_DOI_THANH_TIEN IS NOT NULL)
        UNION
        SELECT A.SO_THANH_TRA,A.TS_QUY_DOI_THANH_TIEN,MA_CO_QUAN FROM TT_THANH_TRA_LAI A, TT_THANH_TRA_LINH_VUC B
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND A.SO_THANH_TRA=B.SO_THANH_TRA
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND (A.TS_QUY_DOI_THANH_TIEN IS NOT NULL))
    GROUP BY MA_CO_QUAN) D,
    --dat tham nhung
    (SELECT SUM(DAT_THAM_NHUNG) AS DAT_THAM_NHUNG,MA_CO_QUAN  FROM
        (SELECT SO_THANH_TRA,DAT_THAM_NHUNG,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND (DAT_THAM_NHUNG IS NOT NULL)
        UNION
        SELECT A.SO_THANH_TRA,A.DAT_THAM_NHUNG,MA_CO_QUAN FROM TT_THANH_TRA_LAI A, TT_THANH_TRA_LINH_VUC B
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND A.SO_THANH_TRA=B.SO_THANH_TRA
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND (A.DAT_THAM_NHUNG IS NOT NULL))
    GROUP BY MA_CO_QUAN) E,
    --dat thu hoi, so tien thu hoi, kien nghi khac, ...
    (SELECT SUM(KIEN_NGHI_THU_HOI) AS KIEN_NGHI_THU_HOI,SUM(SO_TIEN_THU_HOI) AS SO_TIEN_THU_HOI,
            SUM(KIEN_NGHI_KHAC) AS KIEN_NGHI_KHAC, SUM(SO_TIEN_KHAC) AS SO_TIEN_KHAC,
            SUM(DAT_DA_THU) AS DAT_DA_THU, SUM(SO_TIEN_DA_THU) AS SO_TIEN_DA_THU,
            MA_CO_QUAN  FROM
        (SELECT SO_THANH_TRA,KIEN_NGHI_THU_HOI,SO_TIEN_THU_HOI,KIEN_NGHI_KHAC,SO_TIEN_KHAC,
                DAT_DA_THU,SO_TIEN_DA_THU,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND (SO_TIEN_THAM_NHUNG IS NOT NULL OR TAI_SAN_THAM_NHUNG IS NOT NULL OR TS_QUY_DOI_THANH_TIEN IS NOT NULL OR DAT_THAM_NHUNG IS NOT NULL)
        UNION
        SELECT A.SO_THANH_TRA,A.KIEN_NGHI_THU_HOI,A.SO_TIEN_THU_HOI,A.KIEN_NGHI_KHAC,A.SO_TIEN_KHAC,
               A.DAT_DA_THU,A.SO_TIEN_DA_THU,MA_CO_QUAN FROM TT_THANH_TRA_LAI A, TT_THANH_TRA_LINH_VUC B
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND A.SO_THANH_TRA=B.SO_THANH_TRA
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND (A.SO_TIEN_THAM_NHUNG IS NOT NULL OR A.TAI_SAN_THAM_NHUNG IS NOT NULL OR A.TS_QUY_DOI_THANH_TIEN IS NOT NULL OR A.DAT_THAM_NHUNG IS NOT NULL))
    GROUP BY MA_CO_QUAN) F,
    --kien nghi xu ly hanh chinh to chuc
   (SELECT COUNT(SO_THANH_TRA) AS KIEN_NGHI_XU_LY_HC_TO_CHUC,MA_CO_QUAN  FROM
        (SELECT SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND LOAI_HINH_DOANH_NGHIEP = 0 -- to chuc, 1 ca nhan,
        AND KIEN_NGHI_XU_LY=0
        AND (SO_TIEN_THAM_NHUNG IS NOT NULL OR TAI_SAN_THAM_NHUNG IS NOT NULL OR TS_QUY_DOI_THANH_TIEN IS NOT NULL OR DAT_THAM_NHUNG IS NOT NULL)
        UNION
        SELECT A.SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LAI A, TT_THANH_TRA_LINH_VUC B
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND A.SO_THANH_TRA=B.SO_THANH_TRA
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND LOAI_HINH_DOANH_NGHIEP = 0
        AND A.KIEN_NGHI_XU_LY = 0
        AND (A.SO_TIEN_THAM_NHUNG IS NOT NULL OR A.TAI_SAN_THAM_NHUNG IS NOT NULL OR A.TS_QUY_DOI_THANH_TIEN IS NOT NULL OR A.DAT_THAM_NHUNG IS NOT NULL))
    GROUP BY MA_CO_QUAN) G,
    --kien nghi xu ly hanh chinh ca nhan
   (SELECT COUNT(SO_THANH_TRA) AS KIEN_NGHI_XU_LY_HC_CA_NHAN,MA_CO_QUAN  FROM
        (SELECT SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND LOAI_HINH_DOANH_NGHIEP = 1 -- to chuc, 1 ca nhan,
        AND KIEN_NGHI_XU_LY=0
        AND (SO_TIEN_THAM_NHUNG IS NOT NULL OR TAI_SAN_THAM_NHUNG IS NOT NULL OR TS_QUY_DOI_THANH_TIEN IS NOT NULL OR DAT_THAM_NHUNG IS NOT NULL)
        UNION
        SELECT A.SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LAI A, TT_THANH_TRA_LINH_VUC B
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND A.SO_THANH_TRA=B.SO_THANH_TRA
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND LOAI_HINH_DOANH_NGHIEP = 1
        AND A.KIEN_NGHI_XU_LY=0
        AND (A.SO_TIEN_THAM_NHUNG IS NOT NULL OR A.TAI_SAN_THAM_NHUNG IS NOT NULL OR A.TS_QUY_DOI_THANH_TIEN IS NOT NULL OR A.DAT_THAM_NHUNG IS NOT NULL))
    GROUP BY MA_CO_QUAN) H,
    --kien nghi chuyen vu viec
   (SELECT COUNT(SO_THANH_TRA) AS KIEN_NGHI_CHUYEN_VU_VIEC,MA_CO_QUAN  FROM
        (SELECT SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND KIEN_NGHI_XU_LY=1
        AND (SO_TIEN_THAM_NHUNG IS NOT NULL OR TAI_SAN_THAM_NHUNG IS NOT NULL OR TS_QUY_DOI_THANH_TIEN IS NOT NULL OR DAT_THAM_NHUNG IS NOT NULL)
        UNION
        SELECT A.SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LAI A, TT_THANH_TRA_LINH_VUC B
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND A.SO_THANH_TRA=B.SO_THANH_TRA
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND A.KIEN_NGHI_XU_LY=1
        AND (A.SO_TIEN_THAM_NHUNG IS NOT NULL OR A.TAI_SAN_THAM_NHUNG IS NOT NULL OR A.TS_QUY_DOI_THANH_TIEN IS NOT NULL OR A.DAT_THAM_NHUNG IS NOT NULL))
    GROUP BY MA_CO_QUAN) I,
    --kien nghi chuyen doi tuong
   (SELECT COUNT(SO_THANH_TRA) AS KIEN_NGHI_CHUYEN_DOI_TUONG,MA_CO_QUAN  FROM
        (SELECT SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND KIEN_NGHI_XU_LY=2
        AND (SO_TIEN_THAM_NHUNG IS NOT NULL OR TAI_SAN_THAM_NHUNG IS NOT NULL OR TS_QUY_DOI_THANH_TIEN IS NOT NULL OR DAT_THAM_NHUNG IS NOT NULL)
        UNION
        SELECT A.SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LAI A, TT_THANH_TRA_LINH_VUC B
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND A.SO_THANH_TRA=B.SO_THANH_TRA
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND A.KIEN_NGHI_XU_LY=2
        AND (A.SO_TIEN_THAM_NHUNG IS NOT NULL OR A.TAI_SAN_THAM_NHUNG IS NOT NULL OR A.TS_QUY_DOI_THANH_TIEN IS NOT NULL OR A.DAT_THAM_NHUNG IS NOT NULL))
    GROUP BY MA_CO_QUAN) J,
    --ket qua xu ly hanh chinh to chuc
   (SELECT COUNT(SO_THANH_TRA) AS KET_QUA_XU_LY_HC_TO_CHUC,MA_CO_QUAN  FROM
        (SELECT SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND LOAI_HINH_DOANH_NGHIEP = 0 -- to chuc, 1 ca nhan,
        AND KET_QUA_XU_LY=0
        AND (SO_TIEN_THAM_NHUNG IS NOT NULL OR TAI_SAN_THAM_NHUNG IS NOT NULL OR TS_QUY_DOI_THANH_TIEN IS NOT NULL OR DAT_THAM_NHUNG IS NOT NULL)
        UNION
        SELECT A.SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LAI A, TT_THANH_TRA_LINH_VUC B
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND A.SO_THANH_TRA=B.SO_THANH_TRA
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND LOAI_HINH_DOANH_NGHIEP = 0
        AND A.KET_QUA_XU_LY=0
        AND (A.SO_TIEN_THAM_NHUNG IS NOT NULL OR A.TAI_SAN_THAM_NHUNG IS NOT NULL OR A.TS_QUY_DOI_THANH_TIEN IS NOT NULL OR A.DAT_THAM_NHUNG IS NOT NULL))
    GROUP BY MA_CO_QUAN) K,
    --ket qua xu ly hanh chinh ca nhan
   (SELECT COUNT(SO_THANH_TRA) AS KET_QUA_XU_LY_HC_CA_NHAN,MA_CO_QUAN  FROM
        (SELECT SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND LOAI_HINH_DOANH_NGHIEP = 0 -- to chuc, 1 ca nhan,
        AND KET_QUA_XU_LY=0
        AND (SO_TIEN_THAM_NHUNG IS NOT NULL OR TAI_SAN_THAM_NHUNG IS NOT NULL OR TS_QUY_DOI_THANH_TIEN IS NOT NULL OR DAT_THAM_NHUNG IS NOT NULL)
        UNION
        SELECT A.SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LAI A, TT_THANH_TRA_LINH_VUC B
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND A.SO_THANH_TRA=B.SO_THANH_TRA
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND LOAI_HINH_DOANH_NGHIEP = 0
        AND A.KET_QUA_XU_LY=0
        AND (A.SO_TIEN_THAM_NHUNG IS NOT NULL OR A.TAI_SAN_THAM_NHUNG IS NOT NULL OR A.TS_QUY_DOI_THANH_TIEN IS NOT NULL OR A.DAT_THAM_NHUNG IS NOT NULL))
    GROUP BY MA_CO_QUAN) L,
   /* --ket qua xu ly hanh chinh ca nhan
   (SELECT COUNT(SO_THANH_TRA) AS KET_QUA_XU_LY_HC_CA_NHAN,MA_CO_QUAN  FROM
        (SELECT SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND LOAI_HINH_DOANH_NGHIEP = 1 -- to chuc, 1 ca nhan,
        AND KET_QUA_XU_LY=0
        AND (SO_TIEN_THAM_NHUNG IS NOT NULL OR TAI_SAN_THAM_NHUNG IS NOT NULL OR TS_QUY_DOI_THANH_TIEN IS NOT NULL OR DAT_THAM_NHUNG IS NOT NULL)
        UNION
        SELECT A.SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LAI A, TT_THANH_TRA_LINH_VUC B
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND A.SO_THANH_TRA=B.SO_THANH_TRA
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND LOAI_HINH_DOANH_NGHIEP = 1
        AND A.KET_QUA_XU_LY=0
        AND (A.SO_TIEN_THAM_NHUNG IS NOT NULL OR A.TAI_SAN_THAM_NHUNG IS NOT NULL OR A.TS_QUY_DOI_THANH_TIEN IS NOT NULL OR A.DAT_THAM_NHUNG IS NOT NULL))
    GROUP BY MA_CO_QUAN) M,*/
    --khoi to vu viec
   (SELECT COUNT(SO_THANH_TRA) AS KHOI_TO_VU_VIEC,MA_CO_QUAN  FROM
        (SELECT SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND KET_QUA_XU_LY=1
        AND (SO_TIEN_THAM_NHUNG IS NOT NULL OR TAI_SAN_THAM_NHUNG IS NOT NULL OR TS_QUY_DOI_THANH_TIEN IS NOT NULL OR DAT_THAM_NHUNG IS NOT NULL)
        UNION
        SELECT A.SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LAI A, TT_THANH_TRA_LINH_VUC B
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND A.SO_THANH_TRA=B.SO_THANH_TRA
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND A.KET_QUA_XU_LY=1
        AND (A.SO_TIEN_THAM_NHUNG IS NOT NULL OR A.TAI_SAN_THAM_NHUNG IS NOT NULL OR A.TS_QUY_DOI_THANH_TIEN IS NOT NULL OR A.DAT_THAM_NHUNG IS NOT NULL))
    GROUP BY MA_CO_QUAN) M,
    --khoi to doi tuong
   (SELECT COUNT(SO_THANH_TRA) AS KHOI_TO_DOI_TUONG,MA_CO_QUAN  FROM
        (SELECT SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND KET_QUA_XU_LY=2
        AND (SO_TIEN_THAM_NHUNG IS NOT NULL OR TAI_SAN_THAM_NHUNG IS NOT NULL OR TS_QUY_DOI_THANH_TIEN IS NOT NULL OR DAT_THAM_NHUNG IS NOT NULL)
        UNION
        SELECT A.SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LAI A, TT_THANH_TRA_LINH_VUC B
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND A.SO_THANH_TRA=B.SO_THANH_TRA
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND (MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC OR P_MA_LINH_VUC IS NULL)
        AND A.KET_QUA_XU_LY=2
        AND (A.SO_TIEN_THAM_NHUNG IS NOT NULL OR A.TAI_SAN_THAM_NHUNG IS NOT NULL OR A.TS_QUY_DOI_THANH_TIEN IS NOT NULL OR A.DAT_THAM_NHUNG IS NOT NULL))
    GROUP BY MA_CO_QUAN) N
    WHERE A.MA_CO_QUAN=B.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=C.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=D.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=E.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=F.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=G.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=H.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=I.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=J.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=K.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=L.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=M.MA_CO_QUAN(+)
    AND A.MA_CO_QUAN=N.MA_CO_QUAN(+)) KQ, CB_CO_QUAN W
    WHERE KQ.MA_CO_QUAN=W.MA_CO_QUAN;
 END;
PROCEDURE THONG_KE_XU_LY_DON(P_CUR IN OUT MYCUR,P_TU_NGAY NVARCHAR2, P_DEN_NGAY NVARCHAR2, P_MA_CO_QUAN NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR 
    /*SELECT DISTINCT(KQ.MA_HO_SO_KNTC),KQ.NGAY_TIEP_DAN, KQ.MA_NGUON_DON_DEN, KQ.KNTC_HO_VA_TEN,KQ.NOI_DUNG_TIEP_DAN,
          KQ.MA_LOAI_KNTC,KQ.KNTC_SO_NHA,KQ.TEN_LINH_VUC_KNTC,
          KQ.TEN_PHUONG_XA,KQ.TEN_QUAN_HUYEN,KQ.TEN_TINH_THANH,KQ.TEN_NGUON_DON_DEN,KQ.NGAY_GIAI_QUYET,KQ.NGAY_TRINH_LANH_DAO_KY,
          KQ.NGAY_LANH_DAO_KY,KQ.NGAY_CHUYEN_VAN_BAN,KQ.NOI_DUNG_GIAI_QUYET,KQ.FILE_VAN_BAN
    FROM
     (*/SELECT A.MA_HO_SO_KNTC,A.NGAY_TIEP_DAN, A.MA_NGUON_DON_DEN, A.KNTC_HO_VA_TEN,A.NOI_DUNG_TIEP_DAN,
             A.MA_LOAI_KNTC,A.KNTC_SO_NHA,D.TEN_LINH_VUC_KNTC,NULL AS MA_LOAI_NHOM_NSD,
             (SELECT A1.TEN_PHUONG_XA FROM DM_PHUONG_XA A1 
              WHERE A1.MA_PHUONG_XA=A.KNTC_MA_PHUONG_XA) AS TEN_PHUONG_XA,
             (SELECT B2.TEN_QUAN_HUYEN FROM DM_PHUONG_XA B1, DM_QUAN_HUYEN B2 
              WHERE B1.MA_PHUONG_XA=A.KNTC_MA_PHUONG_XA AND B1.MA_QUAN_HUYEN=B2.MA_QUAN_HUYEN) AS TEN_QUAN_HUYEN,
             (SELECT C3.TEN_TINH_THANH FROM DM_PHUONG_XA C1, DM_QUAN_HUYEN C2, DM_TINH_THANH C3 
              WHERE C1.MA_PHUONG_XA=A.KNTC_MA_PHUONG_XA AND C1.MA_QUAN_HUYEN=C2.MA_QUAN_HUYEN AND C2.MA_TINH_THANH=C3.MA_TINH_THANH) AS TEN_TINH_THANH,
              B.TEN_NGUON_DON_DEN,NULL AS NGAY_GIAI_QUYET,NULL AS NGAY_TRINH_LANH_DAO_KY,
      NULL AS NGAY_LANH_DAO_KY,NULL AS NGAY_CHUYEN_VAN_BAN,NULL AS NOI_DUNG_GIAI_QUYET,NULL AS FILE_VAN_BAN FROM KNTC_HO_SO A, DM_NGUON_DON_DEN B,DM_LINH_VUC_KNTC D
      WHERE TO_DATE(A.NGAY_TIEP_DAN,'DD/MM/YYYY') >= TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
      AND TO_DATE(A.NGAY_TIEP_DAN,'DD/MM/YYYY') <= TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
      AND A.MA_CO_QUAN_TIEP_DAN=P_MA_CO_QUAN
      AND A.MA_NGUON_DON_DEN = B.MA_NGUON_DON_DEN
      AND A.MA_LINH_VUC_KNTC=D.MA_LINH_VUC_KNTC
      UNION
      SELECT A.MA_HO_SO_KNTC,A.NGAY_TIEP_DAN, A.MA_NGUON_DON_DEN, A.KNTC_HO_VA_TEN,A.NOI_DUNG_TIEP_DAN,
             A.MA_LOAI_KNTC,A.KNTC_SO_NHA,D.TEN_LINH_VUC_KNTC,
             (SELECT D1.MA_LOAI_NHOM_NSD FROM CB_TO_CHUYEN_MON D1, CB_THUOC_TO_CHUYEN_MON D2 WHERE D1.MA_TO_CHUYEN_MON=D2.MA_TO_CHUYEN_MON AND D2.MA_CAN_BO=C.MA_LANH_DAO_KY) AS MA_LOAI_NHOM_NSD,
             (SELECT A1.TEN_PHUONG_XA FROM DM_PHUONG_XA A1 
              WHERE A1.MA_PHUONG_XA=A.KNTC_MA_PHUONG_XA) AS TEN_PHUONG_XA,
             (SELECT B2.TEN_QUAN_HUYEN FROM DM_PHUONG_XA B1, DM_QUAN_HUYEN B2 
              WHERE B1.MA_PHUONG_XA=A.KNTC_MA_PHUONG_XA AND B1.MA_QUAN_HUYEN=B2.MA_QUAN_HUYEN) AS TEN_QUAN_HUYEN,
             (SELECT C3.TEN_TINH_THANH FROM DM_PHUONG_XA C1, DM_QUAN_HUYEN C2, DM_TINH_THANH C3 
              WHERE C1.MA_PHUONG_XA=A.KNTC_MA_PHUONG_XA AND C1.MA_QUAN_HUYEN=C2.MA_QUAN_HUYEN AND C2.MA_TINH_THANH=C3.MA_TINH_THANH) AS TEN_TINH_THANH,
              B.TEN_NGUON_DON_DEN,C.NGAY_GIAI_QUYET,C.NGAY_TRINH_LANH_DAO_KY,
      C.NGAY_LANH_DAO_KY,C.NGAY_CHUYEN_VAN_BAN,C.NOI_DUNG_GIAI_QUYET,C.FILE_VAN_BAN FROM KNTC_HO_SO A, DM_NGUON_DON_DEN B, KNTC_GIAI_QUYET C,DM_LINH_VUC_KNTC D
      WHERE TO_DATE(A.NGAY_TIEP_DAN,'DD/MM/YYYY') >= TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
      AND TO_DATE(A.NGAY_TIEP_DAN,'DD/MM/YYYY') <= TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
      AND A.MA_NGUON_DON_DEN = B.MA_NGUON_DON_DEN
      AND A.MA_HO_SO_KNTC = C.MA_HO_SO_KNTC
      AND A.MA_CO_QUAN_TIEP_DAN=P_MA_CO_QUAN
      AND A.MA_LINH_VUC_KNTC=D.MA_LINH_VUC_KNTC/*) KQ*/;
    
  END; 
PROCEDURE BAO_CAO_QLNN(P_CUR IN OUT MYCUR,P_TU_NGAY NVARCHAR2,P_DEN_NGAY NVARCHAR2,P_MA_CO_QUAN NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT SO_CUOC_THANH_TRA,SO_DON_VI_THANH_TRA,SO_LUONG_LOP,SO_LUONG_DI_HOC FROM 
    (SELECT COUNT(SO_THANH_TRA) AS SO_CUOC_THANH_TRA,MA_CO_QUAN  FROM
    (SELECT SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        UNION
        SELECT A.SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LAI A, TT_THANH_TRA_LINH_VUC B
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND A.SO_THANH_TRA=B.SO_THANH_TRA
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
       )
    GROUP BY MA_CO_QUAN) A,
    (SELECT COUNT(SO_THANH_TRA) AS SO_DON_VI_THANH_TRA,MA_CO_QUAN  FROM
    (SELECT SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LINH_VUC
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND LOAI_HINH_DOANH_NGHIEP=0
        UNION
        SELECT A.SO_THANH_TRA,MA_CO_QUAN FROM TT_THANH_TRA_LAI A, TT_THANH_TRA_LINH_VUC B
        WHERE TO_DATE(NGAY_BD_THANH_TRA,'DD/MM/YYYY')>=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
        AND TO_DATE(NGAY_KT_THANH_TRA,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
        AND A.SO_THANH_TRA=B.SO_THANH_TRA
        AND (MA_CO_QUAN = P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
        AND LOAI_HINH_DOANH_NGHIEP=0
       )
    GROUP BY MA_CO_QUAN) B,
    (SELECT COUNT(A2.MA_LOP_TAP_HUAN) AS SO_LUONG_LOP,SUM(A2.SO_LUONG_DI_HOC) AS SO_LUONG_DI_HOC,MA_CO_QUAN FROM DM_LOP_TAP_HUAN A1, TT_LOP_TAP_HUAN A2
    WHERE A1.MA_LOP_TAP_HUAN=A2.MA_LOP_TAP_HUAN
    AND (A1.MA_CO_QUAN=P_MA_CO_QUAN OR P_MA_CO_QUAN IS NULL)
    AND TO_DATE(A2.NGAY_MO_LOP,'DD/MM/YYYY')<=TO_DATE(P_TU_NGAY,'DD/MM/YYYY')
    AND TO_DATE(A2.NGAY_MO_LOP,'DD/MM/YYYY')<=TO_DATE(P_DEN_NGAY,'DD/MM/YYYY')
    ORDER BY A1.MA_CO_QUAN
    ) C 
    WHERE A.MA_CO_QUAN=B.MA_CO_QUAN
          AND A.MA_CO_QUAN=C.MA_CO_QUAN;
  END;
END TNMT_THANHTRABAOCAO;
/

prompt
prompt Creating package body TNMT_THANHTRA_DT
prompt ======================================
prompt
create or replace package body hai.TNMT_THANHTRA_DT is


end TNMT_THANHTRA_DT;
/

prompt
prompt Creating package body TNMT_THANHTRA_H
prompt =====================================
prompt
CREATE OR REPLACE PACKAGE BODY HAI.TNMT_THANHTRA_H IS

--===========//GET DS DOAN THANH TRA//=============--
PROCEDURE GET_DOAN_THANH_TRAS(P_CUR IN OUT MYCUR, P_MA_CO_QUAN NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT A.* FROM TT_DOAN_THANH_TRA A
    WHERE A.MA_CO_QUAN=P_MA_CO_QUAN;
  END;
--===========//GET DOAN THANH TRA//=============--
PROCEDURE GET_DOAN_THANH_TRA(P_CUR IN OUT MYCUR, P_MA_DOAN_TT NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT A.* FROM TT_DOAN_THANH_TRA A
    WHERE A.MA_DOAN_TT=P_MA_DOAN_TT;
  END;
--===========//UPDATE DOAN THANH TRA//=============--
PROCEDURE UPDATE_DOAN_THANH_TRA(P_VAL OUT NVARCHAR2, P_UPDATE_TYPE NVARCHAR2, P_MA_DOAN_TT NVARCHAR2,P_TEN_DOAN_TT NVARCHAR2,
                                P_MUC_DICH NVARCHAR2, P_NGAY_THANH_LAP NVARCHAR2,P_NGAY_HET_HAN NVARCHAR2,P_NGAY_GIA_HAN NVARCHAR2,
                                P_MA_CO_QUAN NVARCHAR2,P_TRANG_THAI INT) AS
  V_KT INT;
  BEGIN
    P_VAL:='LOI';
    IF P_UPDATE_TYPE='THEM_MOI' THEN
      SELECT COUNT(MA_DOAN_TT) INTO V_KT FROM TT_DOAN_THANH_TRA WHERE UPPER(LIB_NOTABLE.LOC_DAU(TEN_DOAN_TT))=UPPER(LIB_NOTABLE.LOC_DAU(P_TEN_DOAN_TT));
      IF V_KT!=0 THEN
        P_VAL:='TON_TAI_TEN';
      ELSE
        INSERT INTO TT_DOAN_THANH_TRA(TEN_DOAN_TT,MUC_DICH_THANH_LAP,NGAY_THANH_LAP,
                                      NGAY_HET_HAN,NGAY_GIA_HAN,MA_CO_QUAN,TRANG_THAI)
                               VALUES(P_TEN_DOAN_TT,P_MUC_DICH,P_NGAY_THANH_LAP,
                                      P_NGAY_HET_HAN,P_NGAY_GIA_HAN,P_MA_CO_QUAN,P_TRANG_THAI);
        P_VAL:='THANH_CONG';
       END IF;
    ELSE
       UPDATE TT_DOAN_THANH_TRA SET TEN_DOAN_TT=P_TEN_DOAN_TT,
                                    MUC_DICH_THANH_LAP=P_MUC_DICH,
                                    NGAY_THANH_LAP=P_NGAY_THANH_LAP,
                                    NGAY_HET_HAN=P_NGAY_HET_HAN,
                                    NGAY_GIA_HAN=P_NGAY_GIA_HAN,
                                    MA_CO_QUAN=P_MA_CO_QUAN,
                                    TRANG_THAI=P_TRANG_THAI
               WHERE MA_DOAN_TT=P_MA_DOAN_TT;
       P_VAL:='THANH_CONG';
    END IF;
END;
--===========//DELETE DOAN THANH TRA//=============--
PROCEDURE DELETE_DOAN_THANH_TRA(P_VAL OUT NVARCHAR2,P_NGUOI_XOA INT,P_MA_DOAN_TT NVARCHAR2) AS
  BEGIN
    P_VAL:='LOI';
    IF P_NGUOI_XOA=0 THEN
      DELETE TT_DOAN_THANH_TRA WHERE MA_DOAN_TT=P_MA_DOAN_TT;
      P_VAL:='THANH_CONG';
    END IF;
  END;
--===========//SELECT CAN BO THANH TRA//=============-- 
PROCEDURE GET_DS_CAN_BOS(P_CUR IN OUT MYCUR,P_MA_DOAN_TT NVARCHAR2,P_MA_CO_QUAN NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT A.MA_CAN_BO,TEN_CAN_BO,HINH_ANH,TEN_TO_CHUYEN_MON
                   FROM CB_CAN_BO A, CB_THUOC_TO_CHUYEN_MON B, CB_TO_CHUYEN_MON C
                   WHERE A.MA_CAN_BO=B.MA_CAN_BO
                   AND B.MA_TO_CHUYEN_MON=C.MA_TO_CHUYEN_MON
                   AND A.MA_CO_QUAN=P_MA_CO_QUAN
                   AND A.MA_CAN_BO NOT IN(SELECT MA_CAN_BO FROM TT_DOAN_TT_CO_CAN_BO WHERE MA_DOAN_TT=P_MA_DOAN_TT);
  END;
--===========//SELECT CAN BO DOAN THANH TRA//=============-- 
PROCEDURE GET_CAN_BO_DOAN_TT(P_CUR IN OUT MYCUR,P_MA_DOAN_TT NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT A.MA_CAN_BO,TEN_CAN_BO,HINH_ANH,TEN_TO_CHUYEN_MON
    FROM CB_CAN_BO A, TT_DOAN_TT_CO_CAN_BO B,CB_THUOC_TO_CHUYEN_MON C, CB_TO_CHUYEN_MON D
    WHERE A.MA_CAN_BO=B.MA_CAN_BO
    AND A.MA_CAN_BO=C.MA_CAN_BO
    AND C.MA_TO_CHUYEN_MON=D.MA_TO_CHUYEN_MON
    AND B.MA_DOAN_TT=P_MA_DOAN_TT;
  END; 
--===========//DELETE CAN BO DOAN THANH TRA//=============--
PROCEDURE DELETE_CAN_BO_DOAN_TT(P_MA_DOAN_TT NVARCHAR2) AS
  BEGIN
    DELETE FROM TT_DOAN_TT_CO_CAN_BO WHERE MA_DOAN_TT=P_MA_DOAN_TT;
  END;
--===========//INSERT CAN BO DOAN THANH TRA//=============--
PROCEDURE INSERT_CAN_BO_DOAN_TT(P_MA_DOAN_TT NVARCHAR2,P_MA_CAN_BO NVARCHAR2,P_MA_CHUC_DANH NVARCHAR2) AS
  BEGIN
    INSERT INTO TT_DOAN_TT_CO_CAN_BO(MA_DOAN_TT,MA_CAN_BO,MA_CHUC_DANH,NGAY_CAP_NHAT)
                        VALUES(P_MA_DOAN_TT,P_MA_CAN_BO,P_MA_CHUC_DANH,SYSDATE);
  END;
--===========//SELECT KE HOACH THANH TRA//=============--
PROCEDURE SELECT_KE_HOACH_THANH_TRA(P_CUR IN OUT MYCUR,P_MA_CO_QUAN NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT A.MA_KE_HOACH_TT,A.TEN_KE_HOACH_TT FROM TT_KE_HOACH_THANH_TRA A
    WHERE A.MA_CO_QUAN=P_MA_CO_QUAN;
  END;
--===========//SELECT DOAN THANH TRA//=============--
PROCEDURE SELECT_DOAN_THANH_TRA(P_CUR IN OUT MYCUR,P_MA_CO_QUAN NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT A.MA_DOAN_TT,A.TEN_DOAN_TT FROM TT_DOAN_THANH_TRA A
    WHERE A.MA_CO_QUAN=P_MA_CO_QUAN;
  END;
--===========//SELECT LINH VUC THANH TRA//=============--
PROCEDURE SELECT_LINH_VUC_THANH_TRA(P_CUR IN OUT MYCUR,P_MA_CO_QUAN NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT A.MA_LINH_VUC_THANH_TRA,A.TEN_LINH_VUC_THANH_TRA FROM TT_LINH_VUC_THANH_TRA A
    WHERE A.MA_CO_QUAN=P_MA_CO_QUAN;
  END;
--===========//UPDATE BIEN BAN THANH TRA//=============--
PROCEDURE UPDATE_BIEN_BAN_THANH_TRA(P_VAL OUT NVARCHAR2,P_UPDATE_TYPE NVARCHAR2,P_SO_THANH_TRA NVARCHAR2,P_MA_KE_HOACH_TT INT, P_MA_DOAN_TT INT,P_LOAI_HINH_THANH_TRA NVARCHAR2,
                                    P_NGAY_BD_THANH_TRA NVARCHAR2,P_NGAY_KT_THANH_TRA NVARCHAR2,P_NOI_DUNG_THANH_TRA NVARCHAR2,P_TEN_TO_CHUC_CA_NHAN NVARCHAR2,P_LOAI_HINH_DN INT, P_DIA_CHI_TO_CHUC_CA_NHAN NVARCHAR2,
                                    P_KET_QUA_THANH_TRA NVARCHAR2,P_HINH_THUC_XU_PHAT INT, P_LY_DO_XU_PHAT NVARCHAR2, P_TAI_SAN_TICH_THU NVARCHAR2,P_SO_TIEN_TICH_THU NVARCHAR2,
                                    P_TAI_SAN_TIEU_HUY NVARCHAR2,P_SO_TIEN_TIEU_HUY NVARCHAR2,P_SO_TIEN_XU_PHAT NVARCHAR2,P_MA_CO_QUAN NVARCHAR2,
                                    P_FILE_QUYET_DINH NVARCHAR2, P_NGAY_KY_QUYET_DINH NVARCHAR2, P_FILE_NOI_DUNG_THANH_TRA NVARCHAR2,P_SO_BIEN_BAN_VI_PHAM_HC NVARCHAR2,
                                    P_NGAY_LAP_BIEN_BAN NVARCHAR2, P_FILE_BIEN_BAN_VI_PHAM NVARCHAR2,P_SO_QUYET_DINH_XU_PHAT NVARCHAR2,P_TRANG_THAI_NOP_PHAT NVARCHAR2) AS
  TON_TAI INT;
  V_MA_DOAN_TT INT;
  V_MA_KE_HOACH_TT INT;
  V_MA_QUYET_DINH INT;
  BEGIN
    P_VAL:='LOI';
    IF P_UPDATE_TYPE='THEM_MOI' THEN
      SELECT COUNT(SO_THANH_TRA) INTO TON_TAI FROM TT_THANH_TRA WHERE SO_THANH_TRA=P_SO_THANH_TRA;
      IF TON_TAI > 0 THEN
        P_VAL:='TON_TAI';
      ELSE
        /*--ISERT VAO DM QUYET DINH
        SELECT SEQ_TT_QUYET_DINH.NEXTVAL INTO V_MA_QUYET_DINH FROM DUAL;
        INSERT INTO TT_QUYET_DINH(TEN_QUYET_DINH,NGAY_CAP_NHAT,MA_CO_QUAN)
                     VALUES(P_SO_THANH_TRA,SYSDATE,P_MA_CO_QUAN);
        --ISERT VAO DM KE HOACH TT
        SELECT SEQ_TT_KE_HOACH_TT.NEXTVAL INTO V_MA_KE_HOACH_TT FROM DUAL;
        INSERT INTO TT_KE_HOACH_THANH_TRA(TEN_KE_HOACH_TT,NGAY_LAP_KE_HOACH,NGAY_CAP_NHAT,
                                          MA_QUYET_DINH,MA_CO_QUAN)
                    VALUES(P_MA_KE_HOACH_TT,SYSDATE,SYSDATE,'41',P_MA_CO_QUAN);
        ---INSERT VAO DM DOAN TT
        SELECT SEQ_TT_DOAN_THANH_TRA.NEXTVAL INTO V_MA_DOAN_TT FROM DUAL;
        INSERT INTO TT_DOAN_THANH_TRA(TEN_DOAN_TT,NGAY_THANH_LAP,MA_CO_QUAN,TRANG_THAI)
                    VALUES(P_MA_DOAN_TT,SYSDATE,P_MA_CO_QUAN,'0');*/
        INSERT INTO TT_THANH_TRA(SO_THANH_TRA,MA_KE_HOACH_TT,MA_DOAN_TT,LOAI_HINH_THANH_TRA,NGAY_BD_THANH_TRA,NGAY_KT_THANH_TRA,
                               NOI_DUNG_THANH_TRA,TEN_TO_CHUC_CA_NHAN,LOAI_HINH_DOANH_NGHIEP,DIA_CHI_TO_CHUC_CA_NHAN,KET_QUA_THANH_TRA,
                               HINH_THUC_XU_PHAT,LY_DO_XU_PHAT,TAI_SAN_TICH_THU,SO_TIEN_TICH_THU,TAI_SAN_TIEU_HUY, SO_TIEN_TIEU_HUY,SO_TIEN_XU_PHAT,MA_CO_QUAN,
                               FILE_QUYET_DINH,NGAY_KY_QUYET_DINH,FILE_NOI_DUNG_THANH_TRA,SO_BIEN_BAN_VI_PHAM_HC,NGAY_LAP_BIEN_BAN,FILE_BIEN_BAN_VI_PHAM,
                               SO_QUYET_DINH_XU_PHAT,TRANG_THAI_NOP_PHAT)
                        VALUES(P_SO_THANH_TRA,P_MA_KE_HOACH_TT,P_MA_DOAN_TT,P_LOAI_HINH_THANH_TRA,P_NGAY_BD_THANH_TRA,P_NGAY_KT_THANH_TRA,
                               P_NOI_DUNG_THANH_TRA,P_TEN_TO_CHUC_CA_NHAN,P_LOAI_HINH_DN,P_DIA_CHI_TO_CHUC_CA_NHAN,P_KET_QUA_THANH_TRA,
                               P_HINH_THUC_XU_PHAT,P_LY_DO_XU_PHAT,P_TAI_SAN_TICH_THU,P_SO_TIEN_TICH_THU,P_TAI_SAN_TIEU_HUY,P_SO_TIEN_TIEU_HUY,P_SO_TIEN_XU_PHAT,P_MA_CO_QUAN,
                               P_FILE_QUYET_DINH,P_NGAY_KY_QUYET_DINH,P_FILE_NOI_DUNG_THANH_TRA,P_SO_BIEN_BAN_VI_PHAM_HC,P_NGAY_LAP_BIEN_BAN,
                               P_FILE_BIEN_BAN_VI_PHAM,P_SO_QUYET_DINH_XU_PHAT,P_TRANG_THAI_NOP_PHAT);
      P_VAL:='THANH_CONG';
     END IF;
    ELSE
      UPDATE TT_THANH_TRA SET MA_KE_HOACH_TT=P_MA_KE_HOACH_TT,
                              MA_DOAN_TT=P_MA_DOAN_TT,
                              LOAI_HINH_THANH_TRA=P_LOAI_HINH_THANH_TRA,
                              NGAY_BD_THANH_TRA=P_NGAY_BD_THANH_TRA,
                              NGAY_KT_THANH_TRA=P_NGAY_KT_THANH_TRA,
                              NOI_DUNG_THANH_TRA=P_NOI_DUNG_THANH_TRA,
                              TEN_TO_CHUC_CA_NHAN=P_TEN_TO_CHUC_CA_NHAN,
                              LOAI_HINH_DOANH_NGHIEP=P_LOAI_HINH_DN,
                              DIA_CHI_TO_CHUC_CA_NHAN=P_DIA_CHI_TO_CHUC_CA_NHAN,
                              KET_QUA_THANH_TRA=P_KET_QUA_THANH_TRA,
                              HINH_THUC_XU_PHAT=P_HINH_THUC_XU_PHAT,
                              LY_DO_XU_PHAT=P_LY_DO_XU_PHAT,
                              TAI_SAN_TICH_THU=P_TAI_SAN_TICH_THU,
                              SO_TIEN_TICH_THU=P_SO_TIEN_TICH_THU,
                              TAI_SAN_TIEU_HUY=P_TAI_SAN_TIEU_HUY,
                              SO_TIEN_TIEU_HUY=P_SO_TIEN_TIEU_HUY,
                              SO_TIEN_XU_PHAT=P_SO_TIEN_XU_PHAT,
                              MA_CO_QUAN=P_MA_CO_QUAN,
                              FILE_QUYET_DINH=P_FILE_QUYET_DINH,
                              NGAY_KY_QUYET_DINH=P_NGAY_KY_QUYET_DINH,
                              FILE_NOI_DUNG_THANH_TRA=P_FILE_NOI_DUNG_THANH_TRA,
                              SO_BIEN_BAN_VI_PHAM_HC=P_SO_BIEN_BAN_VI_PHAM_HC,
                              NGAY_LAP_BIEN_BAN=P_NGAY_LAP_BIEN_BAN,
                              FILE_BIEN_BAN_VI_PHAM=P_FILE_BIEN_BAN_VI_PHAM,
                              SO_QUYET_DINH_XU_PHAT=P_SO_QUYET_DINH_XU_PHAT,
                              TRANG_THAI_NOP_PHAT=P_TRANG_THAI_NOP_PHAT
      WHERE SO_THANH_TRA=P_SO_THANH_TRA;
      P_VAL:='UP_THANH_CONG';
    END IF;
 END;
--===========//SELECT BIEN BAN THANH TRA//=============--
PROCEDURE SELECT_BIEN_BAN_THANH_TRAS(P_CUR IN OUT MYCUR) AS
  BEGIN
    OPEN P_CUR FOR SELECT A.*,B.TEN_KE_HOACH_TT,C.TEN_DOAN_TT FROM TT_THANH_TRA A, TT_KE_HOACH_THANH_TRA B,TT_DOAN_THANH_TRA C
    WHERE A.MA_KE_HOACH_TT=B.MA_KE_HOACH_TT
    AND A.MA_DOAN_TT=C.MA_DOAN_TT
    UNION SELECT A.*,NULL AS TEN_KE_HOACH_TT,NULL AS TEN_DOAN_THANH_TRA FROM TT_THANH_TRA A
    WHERE SO_THANH_TRA NOT IN(SELECT A.SO_THANH_TRA FROM TT_THANH_TRA A, TT_KE_HOACH_THANH_TRA B,TT_DOAN_THANH_TRA C
    WHERE A.MA_KE_HOACH_TT=B.MA_KE_HOACH_TT
    AND A.MA_DOAN_TT=C.MA_DOAN_TT);
  END;
--===========//SELECT 1 BIEN BAN THANH TRA//=============--
PROCEDURE SELECT_BIEN_BAN_THANH_TRA(P_CUR IN OUT MYCUR,P_SO_THANH_TRA NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT A.* FROM TT_THANH_TRA A
    WHERE A.SO_THANH_TRA=P_SO_THANH_TRA;
  END;
--===========//delete 1 BIEN BAN THANH TRA//=============--
PROCEDURE DELETE_BIEN_BAN_THANH_TRA(P_VAL OUT NVARCHAR2,P_SO_THANH_TRA NVARCHAR2,P_NGUOI_XOA INT) AS
  BEGIN
    P_VAL:='CL';
    IF P_NGUOI_XOA =0 THEN
      DELETE FROM TT_THANH_TRA WHERE SO_THANH_TRA=P_SO_THANH_TRA;
      P_VAL:='THANH_CONG';
    END IF;
  END;
--===========//UPDATE BIEN BAN THANH TRA//=============--
PROCEDURE UPDATE_BIEN_BAN_TT_LINH_VUC(P_VAL OUT NVARCHAR2,P_UPDATE_TYPE NVARCHAR2,P_SO_THANH_TRA NVARCHAR2,P_MA_KE_HOACH_TT INT, P_MA_DOAN_TT INT,P_LOAI_HINH_THANH_TRA NVARCHAR2,
                                    P_NGAY_BD_THANH_TRA NVARCHAR2,P_NGAY_KT_THANH_TRA NVARCHAR2,P_NOI_DUNG_THANH_TRA NVARCHAR2,P_LINH_VUC_THANH_TRA NVARCHAR2,P_TEN_TO_CHUC_CA_NHAN NVARCHAR2,P_LOAI_HINH_DN INT, P_DIA_CHI_TO_CHUC_CA_NHAN NVARCHAR2,
                                    P_KET_QUA_THANH_TRA NVARCHAR2,P_KIEN_NGHI_XU_LY INT, P_NOI_DUNG_KIEN_NGHI_XU_LY NVARCHAR2,P_NOI_DUNG_KIEN_NGHI_THU_HOI NVARCHAR2, P_KIEN_NGHI_THU_HOI NVARCHAR2,P_SO_TIEN_THU_HOI FLOAT,
                                    P_NOI_DUNG_KIEN_NGHI_KHAC NVARCHAR2, P_KIEN_NGHI_KHAC NVARCHAR2,P_SO_TIEN_KIEN_NGHI_KHAC NVARCHAR2,P_KET_QUA_XU_LY INT,P_NOI_DUNG_XU_LY NVARCHAR2,
                                    P_DAT_PHAI_THU FLOAT,P_SO_TIEN_PHAI_THU FLOAT, P_DAT_DA_THU FLOAT,
                                    P_SO_TIEN_DA_THU FLOAT,P_SO_NGUOI_LIEN_QUAN INT,P_SO_TIEN_THAM_NHUNG FLOAT, P_TAI_SAN_THAM_NHUNG NVARCHAR2,
                                    P_TS_QUY_DOI_THANH_TIEN FLOAT, P_DAT_THAM_NHUNG FLOAT,P_FILE_QUYET_DINH NVARCHAR2, P_FILE_NOI_DUNG_THANH_TRA NVARCHAR2,P_NGAY_KY_QUYET_DINH NVARCHAR2, P_MA_CO_QUAN NVARCHAR2) AS
  TON_TAI INT;
  BEGIN
    P_VAL:='LOI';
    IF P_UPDATE_TYPE='THEM_MOI' THEN
      SELECT COUNT(SO_THANH_TRA) INTO TON_TAI FROM TT_THANH_TRA WHERE SO_THANH_TRA=P_SO_THANH_TRA;
      IF TON_TAI > 0 THEN
        P_VAL:='TON_TAI';
      ELSE 
        INSERT INTO TT_THANH_TRA_LINH_VUC(SO_THANH_TRA,MA_KE_HOACH_TT,MA_DOAN_TT,LOAI_HINH_THANH_TRA,NGAY_BD_THANH_TRA,NGAY_KT_THANH_TRA,
                               NOI_DUNG_THANH_TRA,MA_LINH_VUC_THANH_TRA,TEN_TO_CHUC_CA_NHAN,LOAI_HINH_DOANH_NGHIEP,DIA_CHI_TO_CHUC_CA_NHAN,KET_QUA_THANH_TRA,
                               KIEN_NGHI_XU_LY,NOI_DUNG_KIEN_NGHI_XU_LY,NOI_DUNG_KIEN_NGHI_THU_HOI,KIEN_NGHI_THU_HOI,SO_TIEN_THU_HOI,NOI_DUNG_KIEN_NGHI_KHAC,KIEN_NGHI_KHAC,SO_TIEN_KHAC,
                               KET_QUA_XU_LY, NOI_DUNG_XU_LY, DAT_PHAI_THU, SO_TIEN_PHAI_THU, DAT_DA_THU, SO_TIEN_DA_THU
                               ,SO_NGUOI_LIEN_QUAN,SO_TIEN_THAM_NHUNG, TAI_SAN_THAM_NHUNG, TS_QUY_DOI_THANH_TIEN, DAT_THAM_NHUNG,FILE_QUYET_DINH,FILE_NOI_DUNG_THANH_TRA,NGAY_KY_QUYET_DINH,MA_CO_QUAN)
                        VALUES(P_SO_THANH_TRA,P_MA_KE_HOACH_TT,P_MA_DOAN_TT,P_LOAI_HINH_THANH_TRA,P_NGAY_BD_THANH_TRA,P_NGAY_KT_THANH_TRA,
                               P_NOI_DUNG_THANH_TRA,P_LINH_VUC_THANH_TRA,P_TEN_TO_CHUC_CA_NHAN,P_LOAI_HINH_DN,P_DIA_CHI_TO_CHUC_CA_NHAN,P_KET_QUA_THANH_TRA,
                               P_KIEN_NGHI_XU_LY,P_NOI_DUNG_KIEN_NGHI_XU_LY,P_NOI_DUNG_KIEN_NGHI_THU_HOI,P_KIEN_NGHI_THU_HOI,P_SO_TIEN_THU_HOI,P_NOI_DUNG_KIEN_NGHI_KHAC,P_KIEN_NGHI_KHAC,P_SO_TIEN_KIEN_NGHI_KHAC,
                               P_KET_QUA_XU_LY, P_NOI_DUNG_XU_LY, P_DAT_PHAI_THU, P_SO_TIEN_PHAI_THU,P_DAT_DA_THU,
                               P_SO_TIEN_DA_THU,P_SO_NGUOI_LIEN_QUAN,P_SO_TIEN_THAM_NHUNG,P_TAI_SAN_THAM_NHUNG,P_TS_QUY_DOI_THANH_TIEN,P_DAT_THAM_NHUNG,P_FILE_QUYET_DINH, P_FILE_NOI_DUNG_THANH_TRA, P_NGAY_KY_QUYET_DINH, P_MA_CO_QUAN);
      P_VAL:='THANH_CONG';
     END IF;
    ELSE
      UPDATE TT_THANH_TRA_LINH_VUC SET MA_KE_HOACH_TT=P_MA_KE_HOACH_TT,
                              MA_DOAN_TT=P_MA_DOAN_TT,
                              LOAI_HINH_THANH_TRA=P_LOAI_HINH_THANH_TRA,
                              NGAY_BD_THANH_TRA=P_NGAY_BD_THANH_TRA,
                              NGAY_KT_THANH_TRA=P_NGAY_KT_THANH_TRA,
                              NOI_DUNG_THANH_TRA=P_NOI_DUNG_THANH_TRA,
                              MA_LINH_VUC_THANH_TRA=P_LINH_VUC_THANH_TRA,
                              TEN_TO_CHUC_CA_NHAN=P_TEN_TO_CHUC_CA_NHAN,
                              LOAI_HINH_DOANH_NGHIEP=P_LOAI_HINH_DN,
                              DIA_CHI_TO_CHUC_CA_NHAN=P_DIA_CHI_TO_CHUC_CA_NHAN,
                              KET_QUA_THANH_TRA=P_KET_QUA_THANH_TRA,
                              KIEN_NGHI_XU_LY=P_KIEN_NGHI_XU_LY,
                              NOI_DUNG_KIEN_NGHI_XU_LY=P_NOI_DUNG_KIEN_NGHI_XU_LY,
                              NOI_DUNG_KIEN_NGHI_THU_HOI=P_NOI_DUNG_KIEN_NGHI_THU_HOI,
                              KIEN_NGHI_THU_HOI=P_KIEN_NGHI_THU_HOI,
                              SO_TIEN_THU_HOI=P_SO_TIEN_THU_HOI,
                              NOI_DUNG_KIEN_NGHI_KHAC=P_NOI_DUNG_KIEN_NGHI_KHAC,
                              KIEN_NGHI_KHAC=P_KIEN_NGHI_KHAC,
                              SO_TIEN_KHAC=P_SO_TIEN_KIEN_NGHI_KHAC,
                              KET_QUA_XU_LY=P_KET_QUA_XU_LY,
                              NOI_DUNG_XU_LY=P_NOI_DUNG_XU_LY,
                              DAT_PHAI_THU=P_DAT_PHAI_THU,
                              SO_TIEN_PHAI_THU=P_SO_TIEN_PHAI_THU,
                              DAT_DA_THU=P_DAT_DA_THU,
                              SO_TIEN_DA_THU=P_SO_TIEN_DA_THU,
                              SO_NGUOI_LIEN_QUAN=P_SO_NGUOI_LIEN_QUAN,
                              SO_TIEN_THAM_NHUNG=P_SO_TIEN_THAM_NHUNG,
                              TAI_SAN_THAM_NHUNG=P_TAI_SAN_THAM_NHUNG,
                              TS_QUY_DOI_THANH_TIEN=P_TS_QUY_DOI_THANH_TIEN,
                              DAT_THAM_NHUNG=P_DAT_THAM_NHUNG,
                              FILE_QUYET_DINH=P_FILE_QUYET_DINH,
                              FILE_NOI_DUNG_THANH_TRA=P_FILE_NOI_DUNG_THANH_TRA,
                              NGAY_KY_QUYET_DINH=P_NGAY_KY_QUYET_DINH,
                              MA_CO_QUAN=P_MA_CO_QUAN
      WHERE SO_THANH_TRA=P_SO_THANH_TRA;
      P_VAL:='UP_THANH_CONG';
    END IF;
 END;
 --===========//SELECT 1 BIEN BAN THANH TRA LINH VUC//=============--
PROCEDURE SELECT_BIEN_BAN_TT_LINH_VUC(P_CUR IN OUT MYCUR,P_SO_THANH_TRA NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT A.* FROM TT_THANH_TRA_LINH_VUC A
    WHERE A.SO_THANH_TRA=P_SO_THANH_TRA;
  END;
--===========//SELECT BIEN BAN THANH TRA LINH VUC//=============--
PROCEDURE SELECT_BIEN_BAN_TT_LINH_VUCS(P_CUR IN OUT MYCUR) AS
  BEGIN
    OPEN P_CUR FOR SELECT A.*,B.TEN_KE_HOACH_TT,C.TEN_DOAN_TT FROM TT_THANH_TRA_LINH_VUC A, TT_KE_HOACH_THANH_TRA B,TT_DOAN_THANH_TRA C
    WHERE A.MA_KE_HOACH_TT=B.MA_KE_HOACH_TT
    AND A.MA_DOAN_TT=C.MA_DOAN_TT
    UNION SELECT A.*,NULL AS TEN_KE_HOACH_TT,NULL AS TEN_DOAN_THANH_TRA FROM TT_THANH_TRA_LINH_VUC A
    WHERE SO_THANH_TRA NOT IN(SELECT A.SO_THANH_TRA FROM TT_THANH_TRA_LINH_VUC A, TT_KE_HOACH_THANH_TRA B,TT_DOAN_THANH_TRA C
    WHERE A.MA_KE_HOACH_TT=B.MA_KE_HOACH_TT
    AND A.MA_DOAN_TT=C.MA_DOAN_TT);
  END;
--===========//delete 1 BIEN BAN THANH TRA LINH VUC//=============--
PROCEDURE DELETE_BIEN_BAN_TT_LINH_VUC(P_VAL OUT NVARCHAR2,P_SO_THANH_TRA NVARCHAR2,P_NGUOI_XOA INT) AS
  BEGIN
    P_VAL:='CL';
    IF P_NGUOI_XOA =0 THEN
      DELETE FROM TT_THANH_TRA_LINH_VUC WHERE SO_THANH_TRA=P_SO_THANH_TRA;
      P_VAL:='THANH_CONG';
    END IF;
  END;
--===========// select dang thanh tra 
PROCEDURE SELECT_DANG_VI_PHAM(P_CUR IN OUT MYCUR, P_MA_LINH_VUC NVARCHAR2,P_SO_THANH_TRA NVARCHAR2, P_MA_CO_QUAN NVARCHAR2) AS
 V_TS INT;
  BEGIN
    BEGIN
    SELECT COUNT(MA_DANG_VI_PHAM) INTO V_TS FROM TT_DANG_VI_PHAM A
    WHERE A.MA_CO_QUAN=P_MA_CO_QUAN
    AND A.MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC
    GROUP BY MA_CO_QUAN;
    EXCEPTION 
      WHEN NO_DATA_FOUND THEN
      V_TS:=0;
    END;
    OPEN P_CUR FOR SELECT MA_DANG_VI_PHAM,TEN_DANG_VI_PHAM,SUM(VI_PHAM) AS VI_PHAM,STT_HIEN_THI,TS FROM (SELECT A.MA_DANG_VI_PHAM,A.TEN_DANG_VI_PHAM,NULL AS VI_PHAM,A.STT_HIEN_THI,V_TS AS TS FROM TT_DANG_VI_PHAM A
    WHERE A.MA_CO_QUAN=P_MA_CO_QUAN
    AND A.MA_LINH_VUC_THANH_TRA=P_MA_LINH_VUC
    UNION
    SELECT B.MA_DANG_VI_PHAM,C.TEN_DANG_VI_PHAM, B.VI_PHAM,C.STT_HIEN_THI, V_TS AS TS FROM TT_THANH_TRA_CO_DANG_VI_PHAM B,TT_DANG_VI_PHAM C
    WHERE B.MA_DANG_VI_PHAM=C.MA_DANG_VI_PHAM
    AND B.SO_THANH_TRA=P_SO_THANH_TRA) KQ
    GROUP BY MA_DANG_VI_PHAM,TEN_DANG_VI_PHAM,STT_HIEN_THI,TS
    ORDER BY KQ.STT_HIEN_THI;
 END;
PROCEDURE UPDATE_TT_DANG_VI_PHAM(P_MA_DANG_VI_PHAM INT,P_SO_THANH_TRA NVARCHAR2,P_VI_PHAM NVARCHAR2) AS
  BEGIN
    INSERT INTO TT_THANH_TRA_CO_DANG_VI_PHAM(MA_DANG_VI_PHAM,SO_THANH_TRA,VI_PHAM)
                                      VALUES(P_MA_DANG_VI_PHAM,P_SO_THANH_TRA,P_VI_PHAM);
  END;
PROCEDURE DELETE_TT_DANG_VI_PHAM(P_SO_THANH_TRA NVARCHAR2) AS
  BEGIN
    DELETE FROM TT_THANH_TRA_CO_DANG_VI_PHAM
    WHERE SO_THANH_TRA=P_SO_THANH_TRA;
  END;
-----------------------------------------------
PROCEDURE SELECT_LY_DO_TTL(P_CUR IN OUT MYCUR, P_MA_CO_QUAN NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT A.MA_LY_DO_TTL,A.LY_DO_TTL FROM TT_LY_DO_THANH_TRA_LAI A
    WHERE A.MA_CO_QUAN=P_MA_CO_QUAN;
  END;
------------------------------------------------
PROCEDURE UPDATE_THANH_TRA_LAI(P_VAL OUT NVARCHAR2,P_UPDATE_TYPE NVARCHAR2, P_SO_THANH_TRA INT, P_NGAY_TTL NVARCHAR2,P_LAN_TTL NVARCHAR2,
                               P_LY_DO_TTL NVARCHAR2,P_KET_QUA_TTL NVARCHAR2,P_KIEN_NGHI_XU_LY NVARCHAR2, P_NOI_DUNG_KIEN_NGHI_XU_LY NVARCHAR2,
                               P_NOI_DUNG_KIEN_NGHI_THU_HOI NVARCHAR2, P_KIEN_NGHI_THU_HOI NVARCHAR2, P_SO_TIEN_THU_HOI NVARCHAR2, P_NOI_DUNG_KIEN_NGHI_KHAC NVARCHAR2,
                               P_KIEN_NGHI_KHAC NVARCHAR2, P_SO_TIEN_KHAC NVARCHAR2, P_KET_QUA_XU_LY NVARCHAR2, P_NOI_DUNG_XU_LY NVARCHAR2,
                               P_NOI_DUNG_VI_PHAM_KINH_TE NVARCHAR2,P_DAT_VI_PHAM NVARCHAR2,P_SO_TIEN_VI_PHAM NVARCHAR2,P_DAT_DA_THU NVARCHAR2,P_SO_TIEN_DA_THU NVARCHAR2
                               ,P_SO_TIEN_THAM_NHUNG FLOAT, P_TAI_SAN_THAM_NHUNG NVARCHAR2, P_TS_QUY_DOI_THANH_TIEN FLOAT,P_DAT_THAM_NHUNG FLOAT) AS
  V_KT INT;
  BEGIN
    P_VAL:='CL';
    IF P_UPDATE_TYPE='THEM_MOI' THEN
      SELECT COUNT(SO_THANH_TRA) INTO V_KT FROM TT_THANH_TRA_LAI WHERE SO_THANH_TRA=P_SO_THANH_TRA AND LAN_THANH_TRA_LAI=P_LAN_TTL;
      IF V_KT=0 THEN
         INSERT INTO TT_THANH_TRA_LAI(SO_THANH_TRA,NGAY_THANH_TRA_LAI,LAN_THANH_TRA_LAI,LY_DO_THANH_TRA_LAI,KET_QUA_THANH_TRA_LAI,KIEN_NGHI_XU_LY,
                                   NOI_DUNG_KIEN_NGHI_XU_LY,NOI_DUNG_KIEN_NGHI_THU_HOI,KIEN_NGHI_THU_HOI,SO_TIEN_THU_HOI,NOI_DUNG_KIEN_NGHI_KHAC,
                                   KIEN_NGHI_KHAC,SO_TIEN_KHAC,NOI_DUNG_VI_PHAM_KINH_TE,DAT_VI_PHAM,SO_TIEN_VI_PHAM,KET_QUA_XU_LY,NOI_DUNG_XU_LY,DAT_DA_THU,SO_TIEN_DA_THU
                                   ,SO_TIEN_THAM_NHUNG,TAI_SAN_THAM_NHUNG,TS_QUY_DOI_THANH_TIEN,DAT_THAM_NHUNG)
                             VALUES(P_SO_THANH_TRA,P_NGAY_TTL,P_LAN_TTL,P_LY_DO_TTL,P_KET_QUA_TTL,P_KIEN_NGHI_XU_LY,P_NOI_DUNG_KIEN_NGHI_XU_LY,
                                   P_NOI_DUNG_KIEN_NGHI_THU_HOI,P_KIEN_NGHI_THU_HOI,P_SO_TIEN_THU_HOI,P_NOI_DUNG_KIEN_NGHI_KHAC,P_KIEN_NGHI_KHAC,
                                   P_SO_TIEN_KHAC,P_NOI_DUNG_VI_PHAM_KINH_TE,P_DAT_VI_PHAM,P_SO_TIEN_VI_PHAM,P_KET_QUA_XU_LY,P_NOI_DUNG_XU_LY,P_DAT_DA_THU,P_SO_TIEN_DA_THU
                                   ,P_SO_TIEN_THAM_NHUNG, P_TAI_SAN_THAM_NHUNG, P_TS_QUY_DOI_THANH_TIEN, P_DAT_THAM_NHUNG);
         P_VAL:='THANH_CONG';
      ELSE
         P_VAL:='TON_TAI';
      END IF;
    ELSE
      UPDATE TT_THANH_TRA_LAI SET NGAY_THANH_TRA_LAI=P_NGAY_TTL,
                              LY_DO_THANH_TRA_LAI=P_LY_DO_TTL,
                              KET_QUA_THANH_TRA_LAI=P_KET_QUA_TTL,
                              KIEN_NGHI_XU_LY=P_KIEN_NGHI_XU_LY,
                              NOI_DUNG_KIEN_NGHI_XU_LY=P_NOI_DUNG_KIEN_NGHI_XU_LY,
                              NOI_DUNG_KIEN_NGHI_THU_HOI=P_NOI_DUNG_KIEN_NGHI_THU_HOI,
                              KIEN_NGHI_THU_HOI=P_KIEN_NGHI_THU_HOI,
                              SO_TIEN_THU_HOI=P_SO_TIEN_THU_HOI,
                              NOI_DUNG_KIEN_NGHI_KHAC=P_NOI_DUNG_KIEN_NGHI_KHAC,
                              KIEN_NGHI_KHAC=P_KIEN_NGHI_KHAC,
                              SO_TIEN_KHAC=P_SO_TIEN_KHAC,
                              NOI_DUNG_VI_PHAM_KINH_TE=P_NOI_DUNG_VI_PHAM_KINH_TE,
                              DAT_VI_PHAM=P_DAT_VI_PHAM,
                              SO_TIEN_VI_PHAM=P_SO_TIEN_VI_PHAM,
                              KET_QUA_XU_LY=P_KET_QUA_XU_LY,
                              NOI_DUNG_XU_LY=P_NOI_DUNG_XU_LY,
                              DAT_DA_THU=P_DAT_DA_THU,
                              SO_TIEN_DA_THU=P_SO_TIEN_DA_THU
                              ,SO_TIEN_THAM_NHUNG=P_SO_TIEN_THAM_NHUNG,
                              TAI_SAN_THAM_NHUNG=P_TAI_SAN_THAM_NHUNG,
                              TS_QUY_DOI_THANH_TIEN=P_TS_QUY_DOI_THANH_TIEN,
                              DAT_THAM_NHUNG=P_DAT_THAM_NHUNG
        WHERE SO_THANH_TRA=P_SO_THANH_TRA AND LAN_THANH_TRA_LAI=P_LAN_TTL;
       P_VAL:='THANH_CONG';
    END IF;
  END;
PROCEDURE SELECT_THANH_TRA_LAI(P_CUR OUT MYCUR,P_SO_THANH_TRA INT,P_LAN_TTL INT) AS
  BEGIN
    OPEN P_CUR FOR SELECT A.* FROM TT_THANH_TRA_LAI A
    WHERE A.SO_THANH_TRA=P_SO_THANH_TRA
    AND (A.LAN_THANH_TRA_LAI=P_LAN_TTL OR P_LAN_TTL IS NULL)
    AND LAN_THANH_TRA_LAI =( SELECT MAX(LAN_THANH_TRA_LAI) FROM TT_THANH_TRA_LAI A
                           WHERE A.SO_THANH_TRA=P_SO_THANH_TRA
                           AND (A.LAN_THANH_TRA_LAI=P_LAN_TTL OR P_LAN_TTL IS NULL));
  END;
END TNMT_THANHTRA_H;
/

prompt
prompt Creating package body TNMT_THANHTRA_NAT
prompt =======================================
prompt
create or replace package body hai.TNMT_THANHTRA_NAT is
------------------SELECT_ALL_QUYET_DINH-----------------------
PROCEDURE SELECT_ALL_QUYET_DINH(P_CUR IN OUT MYCUR,P_MA_CO_QUAN NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR
      SELECT A.*,
             CASE
               WHEN A.TRANG_THAI = 0 THEN
                'Mở'
               ELSE
                'Ðóng'
             END AS TEN_TRANG_THAI
        FROM TT_QUYET_DINH A
        WHERE A.MA_CO_QUAN=P_MA_CO_QUAN
       ORDER BY A.MA_QUYET_DINH;
  END;
--------------------SELECT_SINGLE_QUYET_DINH-----------------
PROCEDURE SELECT_SINGLE_QUYET_DINH (P_CUR IN OUT MYCUR, P_MA_QUYET_DINH NUMBER)AS
  BEGIN
    OPEN P_CUR FOR
    SELECT A.MA_QUYET_DINH, A.TEN_QUYET_DINH, A.NOI_DUNG, A.FILE_DINH_KEM, A.NGAY_CAP_NHAT,A.TRANG_THAI,
           CASE
             WHEN A.TRANG_THAI = 0 THEN
               'Mở'
               ELSE
                 'Ðóng'
           END AS TEN_TRANG_THAI
           FROM TT_QUYET_DINH A
           WHERE A.MA_QUYET_DINH = P_MA_QUYET_DINH;
  END; 
------------------DELETE_QUYET_DINH---------------------
 PROCEDURE DELETE_QUYET_DINH(  P_VAL            OUT NVARCHAR2,
                               P_MA_QUYET_DINH NUMBER) AS
  BEGIN
    P_VAL := 'CO_LOI';
       DELETE FROM TT_QUYET_DINH
       WHERE MA_QUYET_DINH LIKE P_MA_QUYET_DINH;
      P_VAL := 'THANH_CONG';
  END;
 
-------------------------UPDATE_QUYET_DINH-------------------
PROCEDURE UPDATE_QUYET_DINH(P_VAL          OUT NVARCHAR2,
                           P_UPDATE_TYPE    VARCHAR2,
                           P_MA_QUYET_DINH  INT,
                           P_TEN_QUYET_DINH NVARCHAR2,
                           P_NOI_DUNG       NVARCHAR2,
                           P_FILE_DINH_KEM  NVARCHAR2,
                           P_NGUOI_CAP_NHAT INT,
                           P_TRANG_THAI     INT,
                           P_MA_CO_QUAN NVARCHAR2
                           )  AS
  V_TON_TAI_TEN INT;
  BEGIN
    P_VAL := 'CO_LOI';   
    IF P_UPDATE_TYPE = 'THEM_MOI' THEN
      SELECT COUNT(MA_QUYET_DINH) INTO V_TON_TAI_TEN FROM TT_QUYET_DINH
      WHERE UPPER(TEN_QUYET_DINH) = UPPER(P_TEN_QUYET_DINH);
      IF V_TON_TAI_TEN > 0 THEN P_VAL :='TON_TAI_TEN';
       ELSE
        INSERT INTO TT_QUYET_DINH
           (TEN_QUYET_DINH,
           NOI_DUNG,
           FILE_DINH_KEM,
           NGUOI_CAP_NHAT,
           NGAY_CAP_NHAT,
           TRANG_THAI,
           MA_CO_QUAN
          )
        VALUES
          (P_TEN_QUYET_DINH,
           P_NOI_DUNG,
           P_FILE_DINH_KEM,
           P_NGUOI_CAP_NHAT,
           SYSDATE,
           P_TRANG_THAI,
           P_MA_CO_QUAN
          );
        P_VAL := 'THANH_CONG';
     END IF;
     ELSE
      SELECT COUNT(MA_QUYET_DINH)INTO V_TON_TAI_TEN FROM TT_QUYET_DINH
      WHERE UPPER(TEN_QUYET_DINH) = UPPER(P_TEN_QUYET_DINH)
      AND MA_QUYET_DINH <> P_MA_QUYET_DINH;
      IF V_TON_TAI_TEN >0 THEN P_VAL :='TON_TAI_TEN';
      ELSE   
        UPDATE TT_QUYET_DINH
           SET TEN_QUYET_DINH = P_TEN_QUYET_DINH,
               NOI_DUNG = P_NOI_DUNG,
               FILE_DINH_KEM = P_FILE_DINH_KEM,
               NGUOI_CAP_NHAT = P_NGUOI_CAP_NHAT,
               NGAY_CAP_NHAT = SYSDATE,
               TRANG_THAI = P_TRANG_THAI,
               MA_CO_QUAN=P_MA_CO_QUAN        
         WHERE MA_QUYET_DINH = P_MA_QUYET_DINH;
        P_VAL := 'THANH_CONG';
    END IF;
    END IF;
  END;
--------------------SELECT_ALL_KEHOACH_TT--------------------
PROCEDURE SELECT_ALL_KEHOACH_TT(P_CUR IN OUT MYCUR,P_MA_CO_QUAN NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR
      SELECT A.*,
             CASE
               WHEN A.TRANG_THAI = 0 THEN
                'Mở'
               ELSE
                'Ðóng'
             END AS TEN_TRANG_THAI
        FROM TT_KE_HOACH_THANH_TRA A WHERE A.MA_CO_QUAN=P_MA_CO_QUAN
       ORDER BY A.MA_KE_HOACH_TT;
  END;
--------------------SELECT_SINGLE_KEHOACH_TT------------------
PROCEDURE SELECT_SINGLE_KEHOACH_TT (P_CUR IN OUT MYCUR, P_MA_KE_HOACH_TT INT)AS
  BEGIN
    OPEN P_CUR FOR
    SELECT A.MA_KE_HOACH_TT, A.TEN_KE_HOACH_TT, A.NGAY_LAP_KE_HOACH, A.FILE_DINH_KEM, A.NGAY_CAP_NHAT, A.TRANG_THAI,
           a.ma_quyet_dinh,CASE
             WHEN A.TRANG_THAI = 0 THEN
               'Mở'
               ELSE
                 'Ðóng'
           END AS TEN_TRANG_THAI
           FROM TT_KE_HOACH_THANH_TRA A
           WHERE A.MA_KE_HOACH_TT = P_MA_KE_HOACH_TT; 
  END; 
--------------------DELETE_KEHOACH_TT--------------------
 PROCEDURE DELETE_KEHOACH_TT(  P_VAL            OUT NVARCHAR2,
                               P_MA_KE_HOACH_TT INT) AS
  BEGIN
    P_VAL := 'CO_LOI';
       DELETE FROM TT_KE_HOACH_THANH_TRA
       WHERE MA_KE_HOACH_TT LIKE P_MA_KE_HOACH_TT;
      P_VAL := 'THANH_CONG';
  END;
--------------------UPDATE_KEHOACH_TT--------------------
PROCEDURE UPDATE_KEHOACH_TT(P_VAL          OUT NVARCHAR2,
                           P_UPDATE_TYPE    NVARCHAR2,
                           P_MA_KE_HOACH_TT INT,
                           P_TEN_KE_HOACH_TT NVARCHAR2,
                           P_NGAY_LAP_KE_HOACH       NVARCHAR2,
                           P_FILE_DINH_KEM  NVARCHAR2,
                           P_MA_QUYET_DINH INT,
                           P_TRANG_THAI     INT,
                           P_MA_CO_QUAN NVARCHAR2
                           )  AS
  V_TON_TAI_TEN INT;
  BEGIN
    P_VAL := 'CO_LOI';   
    IF P_UPDATE_TYPE = 'THEM_MOI' THEN
      SELECT COUNT(MA_KE_HOACH_TT) INTO V_TON_TAI_TEN FROM TT_KE_HOACH_THANH_TRA
      WHERE UPPER(TEN_KE_HOACH_TT) = UPPER(P_TEN_KE_HOACH_TT);
      IF V_TON_TAI_TEN > 0 THEN P_VAL :='TON_TAI_TEN';
       ELSE
        INSERT INTO TT_KE_HOACH_THANH_TRA
           (TEN_KE_HOACH_TT,
           NGAY_LAP_KE_HOACH,
           FILE_DINH_KEM,
           NGAY_CAP_NHAT,
           MA_QUYET_DINH,
           TRANG_THAI,
           MA_CO_QUAN
          )
        VALUES
         ( P_TEN_KE_HOACH_TT,
           P_NGAY_LAP_KE_HOACH,
           P_FILE_DINH_KEM,
           SYSDATE,
           P_MA_QUYET_DINH,
           P_TRANG_THAI,
           P_MA_CO_QUAN
          );
        P_VAL := 'THANH_CONG';
     END IF;
     ELSE
      SELECT COUNT(MA_KE_HOACH_TT)INTO V_TON_TAI_TEN FROM TT_KE_HOACH_THANH_TRA
      WHERE UPPER(TEN_KE_HOACH_TT) = UPPER(P_TEN_KE_HOACH_TT)
      AND MA_KE_HOACH_TT <> MA_KE_HOACH_TT;
      IF V_TON_TAI_TEN >0 THEN P_VAL :='TON_TAI_TEN';
      ELSE   
        UPDATE TT_KE_HOACH_THANH_TRA
           SET TEN_KE_HOACH_TT = P_TEN_KE_HOACH_TT,
               NGAY_LAP_KE_HOACH = P_NGAY_LAP_KE_HOACH,
               FILE_DINH_KEM = P_FILE_DINH_KEM,
               NGAY_CAP_NHAT = SYSDATE,
               MA_QUYET_DINH = P_MA_QUYET_DINH,
               TRANG_THAI = P_TRANG_THAI,
               MA_CO_QUAN=P_MA_CO_QUAN                  
         WHERE MA_KE_HOACH_TT = P_MA_KE_HOACH_TT;
        P_VAL := 'THANH_CONG';
    END IF;
    END IF;
  END;
-------------------------------------------------------------  
END TNMT_THANHTRA_NAT;
/

prompt
prompt Creating package body TNMT_THANHTRA_TK
prompt ======================================
prompt
create or replace package body hai.TNMT_THANHTRA_TK is
 

end TNMT_THANHTRA_TK;
/

prompt
prompt Creating trigger TRI_CAN_BO
prompt ===========================
prompt
CREATE OR REPLACE TRIGGER "HAI"."TRI_CAN_BO" BEFORE INSERT ON CB_CAN_BO REFERENCING NEW AS NEW FOR EACH ROW 
BEGIN SELECT SEQ_CAN_BO.NEXTVAL    INTO :NEW.MA_CAN_BO    FROM DUAL; END;
/

prompt
prompt Creating trigger TRI_DM_LOP_TAP_HUAN
prompt ====================================
prompt
CREATE OR REPLACE TRIGGER "HAI"."TRI_DM_LOP_TAP_HUAN" BEFORE INSERT ON DM_LOP_TAP_HUAN REFERENCING NEW AS NEW FOR EACH ROW
BEGIN SELECT SEQ_DM_LOP_TAP_HUAN.NEXTVAL    INTO :NEW.MA_LOP_TAP_HUAN    FROM DUAL; END;
/

prompt
prompt Creating trigger TRI_GIAI_QUYET
prompt ===============================
prompt
CREATE OR REPLACE TRIGGER "HAI"."TRI_GIAI_QUYET" BEFORE INSERT ON KNTC_GIAI_QUYET REFERENCING NEW AS NEW FOR EACH ROW 
BEGIN SELECT SEQ_GIAI_QUYET.NEXTVAL    INTO :NEW.MA_GIAI_QUYET    FROM DUAL; END;
/

prompt
prompt Creating trigger TRI_KNTC_HO_SO
prompt ===============================
prompt
CREATE OR REPLACE TRIGGER "HAI"."TRI_KNTC_HO_SO" BEFORE INSERT ON KNTC_HO_SO REFERENCING NEW AS NEW FOR EACH ROW 
BEGIN SELECT SEQ_KNTC_HO_SO.NEXTVAL    INTO :NEW.MA_HO_SO_KNTC    FROM DUAL; END;
/

prompt
prompt Creating trigger TRI_LICH_CONG_TAC
prompt ==================================
prompt
CREATE OR REPLACE TRIGGER "HAI"."TRI_LICH_CONG_TAC" BEFORE INSERT ON LICH_CONG_TAC REFERENCING NEW AS NEW FOR EACH ROW
BEGIN SELECT SEQ_LICH_CONG_TAC.NEXTVAL    INTO :NEW.STT    FROM DUAL; END;
/

prompt
prompt Creating trigger TRI_TO_CHUYEN_MON
prompt ==================================
prompt
CREATE OR REPLACE TRIGGER "HAI"."TRI_TO_CHUYEN_MON" BEFORE INSERT ON CB_TO_CHUYEN_MON REFERENCING NEW AS NEW FOR EACH ROW 
BEGIN SELECT SEQ_TO_CHUYEN_MON.NEXTVAL    INTO :NEW.MA_TO_CHUYEN_MON    FROM DUAL; END;
/

prompt
prompt Creating trigger TRI_TT_DOAN_THANH_TRA
prompt ======================================
prompt
CREATE OR REPLACE TRIGGER "HAI"."TRI_TT_DOAN_THANH_TRA" BEFORE INSERT ON TT_DOAN_THANH_TRA REFERENCING NEW AS NEW FOR EACH ROW
BEGIN SELECT SEQ_TT_DOAN_THANH_TRA.NEXTVAL    INTO :NEW.MA_DOAN_TT    FROM DUAL; END;
/

prompt
prompt Creating trigger TRI_TT_KE_HOACH_TT
prompt ===================================
prompt
CREATE OR REPLACE TRIGGER "HAI"."TRI_TT_KE_HOACH_TT" BEFORE INSERT ON TT_KE_HOACH_THANH_TRA REFERENCING NEW AS NEW FOR EACH ROW
BEGIN SELECT SEQ_TT_KE_HOACH_TT.NEXTVAL    INTO :NEW.MA_KE_HOACH_TT    FROM DUAL; END;
/

prompt
prompt Creating trigger TRI_TT_QUYET_DINH
prompt ==================================
prompt
CREATE OR REPLACE TRIGGER "HAI"."TRI_TT_QUYET_DINH" BEFORE INSERT ON TT_QUYET_DINH REFERENCING NEW AS NEW FOR EACH ROW
BEGIN SELECT SEQ_TT_QUYET_DINH.NEXTVAL    INTO :NEW.MA_QUYET_DINH    FROM DUAL; END;
/

prompt
prompt Creating trigger TRI_TT_THANH_TRA
prompt =================================
prompt
CREATE OR REPLACE TRIGGER "HAI"."TRI_TT_THANH_TRA" BEFORE INSERT ON TT_THANH_TRA REFERENCING NEW AS NEW FOR EACH ROW
BEGIN SELECT SEQ_TT_THANH_TRA.NEXTVAL    INTO :NEW.STT_THANH_TRA    FROM DUAL; END;
/

prompt
prompt Creating trigger TRI_TT_THANH_TRA_LINH_VUC
prompt ==========================================
prompt
CREATE OR REPLACE TRIGGER "HAI"."TRI_TT_THANH_TRA_LINH_VUC" BEFORE INSERT ON TT_THANH_TRA_LINH_VUC REFERENCING NEW AS NEW FOR EACH ROW
BEGIN SELECT SEQ_TT_THANH_TRA_LINH_VUC.NEXTVAL    INTO :NEW.STT_THANH_TRA    FROM DUAL; END;
/


spool off
