--------------------------------------------
-- Export file for user HAI               --
-- Created by Thái on 3/10/2016, 13:47:22 --
--------------------------------------------

spool KNTC.log

prompt
prompt Creating package KNTC
prompt =====================
prompt
CREATE OR REPLACE PACKAGE HAI.KNTC IS
  TYPE MYCUR IS REF CURSOR; 
  -------------------------------------------------------------------------------
  PROCEDURE SO_TIEP_DAN( P_CUR IN OUT MYCUR,
                                        P_SO_HO_SO_KNTC NVARCHAR2,
                                        P_MA_LINH_VUC_KNTC NVARCHAR2,
                                        P_MA_LOAI_KNTC NVARCHAR2,
                                        P_KNTC_MA_LOAI_DT NVARCHAR2,
                                        P_NGAY_TIEP_NHAN NVARCHAR2,
                                        P_NOI_DUNG_TIEP_DAN NVARCHAR2,
                                        P_DIA_CHI NVARCHAR2,
                                        P_YEU_CAU_GAP_LD INT  
                                        );
  -------------------------------------------------------------------------------
  PROCEDURE SELECT_TIEP_DAN_DETAIL(P_CUR IN OUT MYCUR, P_MA_HO_SO_KNTC INT);
  -------------------------------------------------------------------------------
  PROCEDURE UPDATE_TIEP_DAN(P_VAL OUT NVARCHAR2,
                          P_MA_HO_SO_KNTC INT,
                          P_MA_NGUON_DON_DEN NVARCHAR2,
                          P_NGAY_TIEP_DAN NVARCHAR2,
                          P_VU_VIEC_MOI INT,
                          P_NOI_DUNG_TIEP_DAN NVARCHAR2,
                          P_YEU_CAU_GAP_LD INT,
                          P_NGAY_YCGLD NVARCHAR2,
                          P_MA_LD_TIEP_DAN INT,
                          P_Y_KIEN_LD NVARCHAR2,
                          P_MA_CAN_BO_TIEP_DAN INT,
                          P_MA_CO_QUAN_TIEP_DAN NVARCHAR2,
                          P_MA_LINH_VUC_KNTC NVARCHAR2,   
                          P_MA_LOAI_KNTC NVARCHAR2,
                          P_KNTC_MA_LOAI_DT NVARCHAR2,
                          P_KNTC_CMND NVARCHAR2,
                          P_KNTC_HO_VA_TEN NVARCHAR2,
                          P_KNTC_MA_PHUONG_XA NVARCHAR2,
                          P_KNTC_SO_NHA NVARCHAR2,
                          P_KNTC_TEN_CQTC NVARCHAR2,
                          P_KNTC_DIA_CHI_CQTC NVARCHAR2,
                          P_KNTC_SO_NGUOI_DAI_DIEN INT,
                          P_KNTC_TEN_NHUNG_NGUOI_DD NVARCHAR2,
                          P_MA_HO_SO_KNTC_GOC INT,
                          P_MA_DON_VI_XU_LY_CHINH NVARCHAR2
                        );
  -------------------------------------------------------------------------------
  PROCEDURE UPDATE_NGUOI_BI_KNTC(P_VAL OUT NVARCHAR2,
                          P_MA_HO_SO_KNTC INT,
                          P_BKNTC_MA_LOAI_DT NVARCHAR2,
                          P_BKNTC_HO_VA_TEN NVARCHAR2,
                          P_BKNTC_DIA_CHI NVARCHAR2,
                          P_BKNTC_TEN_CQTC NVARCHAR2,
                          P_BKNTC_DIA_CHI_CQTC NVARCHAR2
                        );
  -------------------------------------------------------------------------------
  PROCEDURE UPDATE_GIAI_QUYET(P_VAL OUT NVARCHAR2,
                          P_MA_HO_SO_KNTC INT,
                          P_MA_GIAI_QUYET INT,
                          P_MA_CAN_BO INT,
                          P_NGAY_GIAI_QUYET NVARCHAR2,
                          P_LAN_GIAI_QUYET INT,
                          P_MA_HUONG_GIAI_QUYET NVARCHAR2,
                          P_NOI_DUNG_GIAI_QUYET NVARCHAR2,
                          P_TIEN_DO_XU_LY NVARCHAR2,
                          P_SO_KY_HIEU NVARCHAR2,
                          P_TRICH_YEU NVARCHAR2,
                          P_CO_QUAN_BAN_HANH NVARCHAR2,
                          P_SO_BUT_LUC NVARCHAR2,
                          P_SO_TRANG NVARCHAR2,
                          P_NGAY_TRINH_LANH_DAO_KY NVARCHAR2,
                          P_NGAY_LANH_DAO_KY NVARCHAR2,
                          P_NGAY_CHUYEN_VAN_BAN NVARCHAR2,
                          P_MA_LANH_DAO_KY INT,
                          P_FILE_VAN_BAN NVARCHAR2,
                          P_DIEU_KIEN_XU_LY INT,
                          P_THAM_QUYEN_GIAI_QUYET INT
                        );
  -------------------------------------------------------------------------------
  PROCEDURE DELETE_GIAI_QUYET(P_VAL OUT NVARCHAR2, P_MA_GIAI_QUYET INT);
  -------------------------------------------------------------------------------
  PROCEDURE SELECT_GIAI_QUYET(P_CUR IN OUT MYCUR, P_MA_HO_SO_KNTC INT);
  -----------------------------------------------
   PROCEDURE UPDATE_FILE_GIAI_QUYET(P_VAL OUT NVARCHAR2, P_MA_GIAI_QUYET NVARCHAR2, P_FILE_VAN_BAN NVARCHAR2);


/*  -------------------------------------------------------------------------------
  PROCEDURE UPDATE_TIEP_DAN_CO_DON(P_VAL OUT NVARCHAR2,
                          P_MA_HO_SO_KNTC INT,
                          P_MA_NGUON_DON_DEN NVARCHAR2,
                          P_NGAY_TIEP_DAN NVARCHAR2,
                          P_VU_VIEC_MOI INT,
                          P_NOI_DUNG_TIEP_DAN NVARCHAR2,
                          P_YEU_CAU_GAP_LD INT,
                          P_MA_LD_TIEP_DAN INT,
                          P_Y_KIEN_LD NVARCHAR2,
                          P_MA_CAN_BO_TIEP_DAN INT,
                          P_MA_CO_QUAN_TIEP_DAN NVARCHAR2,
                          P_MA_LINH_VUC_KNTC NVARCHAR2,   
                          P_MA_LOAI_KNTC NVARCHAR2,
                          P_KNTC_MA_LOAI_DT NVARCHAR2,
                          P_KNTC_CMND NVARCHAR2,
                          P_KNTC_HO_VA_TEN NVARCHAR2,
                          P_KNTC_MA_PHUONG_XA NVARCHAR2,
                          P_KNTC_SO_NHA NVARCHAR2,
                          P_KNTC_TEN_CQTC NVARCHAR2,
                          P_KNTC_DIA_CHI_CQTC NVARCHAR2,
                          P_KNTC_SO_NGUOI_DAI_DIEN INT,
                          P_KNTC_TEN_NHUNG_NGUOI_DD NVARCHAR2,
                          P_MA_HO_SO_KNTC_GOC INT,
                          P_MA_DON_VI_XU_LY_CHINH NVARCHAR2,
                          P_MA_TRANG_THAI_GIAI_QUYET  NVARCHAR2
                        );*/
  -------------------------------------------------------------------------------
  PROCEDURE M2A(P_CUR IN OUT MYCUR, P_TU_NGAY NVARCHAR2, P_DEN_NGAY NVARCHAR2);
  -----------------------------------------------------------------------------
  PROCEDURE M2B(P_CUR IN OUT MYCUR, P_TU_NGAY NVARCHAR2, P_DEN_NGAY NVARCHAR2);
  END KNTC;
/

prompt
prompt Creating package body KNTC
prompt ==========================
prompt
CREATE OR REPLACE PACKAGE BODY HAI.KNTC IS
  -------------------------------------------------------------------------------
  PROCEDURE SO_TIEP_DAN( P_CUR IN OUT MYCUR,
                                  P_SO_HO_SO_KNTC NVARCHAR2,
                                  P_MA_LINH_VUC_KNTC NVARCHAR2,
                                  P_MA_LOAI_KNTC NVARCHAR2,
                                  P_KNTC_MA_LOAI_DT NVARCHAR2,
                                  P_NGAY_TIEP_NHAN NVARCHAR2,
                                  P_NOI_DUNG_TIEP_DAN NVARCHAR2,
                                  P_DIA_CHI NVARCHAR2,
                                  P_YEU_CAU_GAP_LD INT  
                                  ) AS
  P_NGAY_TIEP_NHAN_UPPER NVARCHAR2(200);
  P_NOI_DUNG_TIEP_DAN_UPPER NVARCHAR2(200);
  BEGIN 
     P_NGAY_TIEP_NHAN_UPPER:= UPPER(P_NGAY_TIEP_NHAN);
     P_NOI_DUNG_TIEP_DAN_UPPER:= UPPER(P_NOI_DUNG_TIEP_DAN);
     OPEN P_CUR FOR SELECT A.*, 
                           D.TEN_CAN_BO, 
                           C.TEN_LINH_VUC_KNTC, 
                           DT.TEN_LOAI_DTKNTC,
                           A.KNTC_SO_NHA || ', ' || X.TEN_PHUONG_XA || ', ' || H.TEN_QUAN_HUYEN || ', ' || T.TEN_TINH_THANH AS DIA_CHI,
                           CASE WHEN A.YEU_CAU_GAP_LD =1 THEN 'Có' else 'Không' end AS GAP_LANH_DAO
                    FROM KNTC_HO_SO A,
                         DM_LINH_VUC_KNTC C,
                         CB_CAN_BO D, 
                         DM_PHUONG_XA X, 
                         DM_QUAN_HUYEN H, 
                         DM_TINH_THANH T,
                         DM_LOAI_DTKNTC DT
                    WHERE A.MA_CAN_BO_TIEP_DAN = D.MA_CAN_BO
                    AND A.KNTC_MA_PHUONG_XA=X.MA_PHUONG_XA
                    AND X.MA_QUAN_HUYEN=H.MA_QUAN_HUYEN
                    AND H.MA_TINH_THANH=T.MA_TINH_THANH
                    AND A.KNTC_MA_LOAI_DT=DT.MA_LOAI_DTKNTC
                    AND (P_SO_HO_SO_KNTC IS NULL OR A.SO_HO_SO_KNTC = P_SO_HO_SO_KNTC)
                    AND (P_NGAY_TIEP_NHAN IS NULL OR UPPER(A.NGAY_TIEP_DAN) LIKE P_NGAY_TIEP_NHAN_UPPER)
                    AND A.MA_LINH_VUC_KNTC=C.MA_LINH_VUC_KNTC
                    AND (P_MA_LINH_VUC_KNTC IS NULL OR A.MA_LINH_VUC_KNTC= P_MA_LINH_VUC_KNTC)
                    AND (P_MA_LOAI_KNTC IS NULL OR A.MA_LOAI_KNTC=P_MA_LOAI_KNTC)
                    AND (P_KNTC_MA_LOAI_DT IS NULL OR A.KNTC_MA_LOAI_DT = P_KNTC_MA_LOAI_DT)
                    AND (P_NOI_DUNG_TIEP_DAN IS NULL 
                      OR UPPER(A.NOI_DUNG_TIEP_DAN) LIKE P_NOI_DUNG_TIEP_DAN_UPPER 
                      OR UPPER(A.KNTC_HO_VA_TEN)    LIKE P_NOI_DUNG_TIEP_DAN_UPPER
                      OR UPPER(A.KNTC_CMND)         LIKE P_NOI_DUNG_TIEP_DAN_UPPER
                      )
                    AND (P_DIA_CHI IS NULL 
                         OR UPPER(A.KNTC_SO_NHA || ', ' || X.TEN_PHUONG_XA || ', ' || H.TEN_QUAN_HUYEN || ', ' || T.TEN_TINH_THANH) LIKE UPPER('%'||P_DIA_CHI||'%'))
                    AND (P_YEU_CAU_GAP_LD =-1 OR A.YEU_CAU_GAP_LD = P_YEU_CAU_GAP_LD)
                    ORDER BY A.SO_HO_SO_KNTC DESC, A.NGAY_TIEP_DAN DESC, A.NGAY_NHAP DESC;
  END;
-------------------------------------------------------------------------------
  PROCEDURE SELECT_TIEP_DAN_DETAIL(P_CUR IN OUT MYCUR, P_MA_HO_SO_KNTC INT) AS
  V_MAX_LAN_GIAI_QUYET INT;
  BEGIN
    SELECT MAX(LAN_GIAI_QUYET) INTO V_MAX_LAN_GIAI_QUYET FROM KNTC_GIAI_QUYET A WHERE A.MA_HO_SO_KNTC = P_MA_HO_SO_KNTC;
    IF V_MAX_LAN_GIAI_QUYET IS NULL THEN
       V_MAX_LAN_GIAI_QUYET:=0;
    END IF; 
    OPEN P_CUR FOR
     SELECT A.*,
            V_MAX_LAN_GIAI_QUYET AS MAX_LAN_GIAI_QUYET,
            D.TEN_CAN_BO, 
            C.TEN_LINH_VUC_KNTC, 
            H.MA_QUAN_HUYEN,
            H.MA_TINH_THANH,
            A.KNTC_SO_NHA || ', ' || X.TEN_PHUONG_XA || ', ' || H.TEN_QUAN_HUYEN || ', ' || T.TEN_TINH_THANH AS DIA_CHI,
            CASE WHEN A.YEU_CAU_GAP_LD =1 THEN 'Có' else 'Không' end AS GAP_LANH_DAO
        FROM KNTC_HO_SO A,DM_LINH_VUC_KNTC C,CB_CAN_BO D, DM_PHUONG_XA X, DM_QUAN_HUYEN H, DM_TINH_THANH T
        WHERE A.MA_CAN_BO_TIEP_DAN = D.MA_CAN_BO
        AND A.MA_LINH_VUC_KNTC = C.MA_LINH_VUC_KNTC
        AND A.KNTC_MA_PHUONG_XA=X.MA_PHUONG_XA
        AND X.MA_QUAN_HUYEN=H.MA_QUAN_HUYEN
        AND H.MA_TINH_THANH=T.MA_TINH_THANH
        AND A.MA_HO_SO_KNTC = P_MA_HO_SO_KNTC;
  END;
 -------------------------------------------------------------------------------
 PROCEDURE UPDATE_TIEP_DAN(P_VAL OUT NVARCHAR2,
                          P_MA_HO_SO_KNTC INT,
                          P_MA_NGUON_DON_DEN NVARCHAR2,
                          P_NGAY_TIEP_DAN NVARCHAR2,
                          P_VU_VIEC_MOI INT,
                          P_NOI_DUNG_TIEP_DAN NVARCHAR2,
                          P_YEU_CAU_GAP_LD INT,
                          P_NGAY_YCGLD NVARCHAR2,
                          P_MA_LD_TIEP_DAN INT,
                          P_Y_KIEN_LD NVARCHAR2,
                          P_MA_CAN_BO_TIEP_DAN INT,
                          P_MA_CO_QUAN_TIEP_DAN NVARCHAR2,
                          P_MA_LINH_VUC_KNTC NVARCHAR2,   
                          P_MA_LOAI_KNTC NVARCHAR2,
                          P_KNTC_MA_LOAI_DT NVARCHAR2,
                          P_KNTC_CMND NVARCHAR2,
                          P_KNTC_HO_VA_TEN NVARCHAR2,
                          P_KNTC_MA_PHUONG_XA NVARCHAR2,
                          P_KNTC_SO_NHA NVARCHAR2,
                          P_KNTC_TEN_CQTC NVARCHAR2,
                          P_KNTC_DIA_CHI_CQTC NVARCHAR2,
                          P_KNTC_SO_NGUOI_DAI_DIEN INT,
                          P_KNTC_TEN_NHUNG_NGUOI_DD NVARCHAR2,
                          P_MA_HO_SO_KNTC_GOC INT,
                          P_MA_DON_VI_XU_LY_CHINH NVARCHAR2
                        ) AS
 V_KNTC_SO_NGUOI_DAI_DIEN INT;
 V_SO_HO_SO_KNTC INT;
 BEGIN
    P_VAL:='THAT_BAI';
    IF P_KNTC_MA_LOAI_DT = 'TC'  OR P_KNTC_MA_LOAI_DT = 'CN' or P_KNTC_SO_NGUOI_DAI_DIEN IS NULL THEN
       V_KNTC_SO_NGUOI_DAI_DIEN:=1;
    ELSE 
       V_KNTC_SO_NGUOI_DAI_DIEN:=P_KNTC_SO_NGUOI_DAI_DIEN;
    END IF;
    IF P_MA_HO_SO_KNTC = 0 THEN
                SELECT MAX(SO_HO_SO_KNTC) INTO V_SO_HO_SO_KNTC FROM KNTC_HO_SO;
                IF V_SO_HO_SO_KNTC IS NULL THEN
                   V_SO_HO_SO_KNTC:=1;
                ELSE
                   V_SO_HO_SO_KNTC:= V_SO_HO_SO_KNTC + 1;
                END IF;
                INSERT INTO KNTC_HO_SO(
                                SO_HO_SO_KNTC,
                                NGAY_NHAP,
                                MA_NGUON_DON_DEN,
                                NGAY_TIEP_DAN,
                                VU_VIEC_MOI,
                                NOI_DUNG_TIEP_DAN,
                                YEU_CAU_GAP_LD,
                                NGAY_YCGLD,
                                MA_LD_TIEP_DAN,
                                Y_KIEN_LD,
                                MA_CAN_BO_TIEP_DAN,
                                MA_CO_QUAN_TIEP_DAN,
                                MA_LINH_VUC_KNTC,   
                                MA_LOAI_KNTC,
                                KNTC_MA_LOAI_DT,
                                KNTC_CMND,
                                KNTC_HO_VA_TEN,
                                KNTC_MA_PHUONG_XA,
                                KNTC_SO_NHA,
                                KNTC_TEN_CQTC,
                                KNTC_DIA_CHI_CQTC,
                                KNTC_SO_NGUOI_DAI_DIEN,
                                KNTC_TEN_NHUNG_NGUOI_DD,
                                MA_HO_SO_KNTC_GOC,
                                MA_DON_VI_XU_LY_CHINH
                                )
                         VALUES (
                                V_SO_HO_SO_KNTC,
                                SYSDATE,
                                P_MA_NGUON_DON_DEN,
                                P_NGAY_TIEP_DAN,
                                P_VU_VIEC_MOI,
                                P_NOI_DUNG_TIEP_DAN,
                                P_YEU_CAU_GAP_LD,
                                P_NGAY_YCGLD,
                                CASE WHEN P_MA_LD_TIEP_DAN =0 THEN NULL ELSE P_MA_LD_TIEP_DAN END,
                                P_Y_KIEN_LD,
                                P_MA_CAN_BO_TIEP_DAN,
                                P_MA_CO_QUAN_TIEP_DAN,
                                P_MA_LINH_VUC_KNTC,   
                                P_MA_LOAI_KNTC,
                                P_KNTC_MA_LOAI_DT,
                                P_KNTC_CMND,
                                P_KNTC_HO_VA_TEN,
                                P_KNTC_MA_PHUONG_XA,
                                P_KNTC_SO_NHA,
                                P_KNTC_TEN_CQTC,
                                P_KNTC_DIA_CHI_CQTC,
                                V_KNTC_SO_NGUOI_DAI_DIEN,
                                P_KNTC_TEN_NHUNG_NGUOI_DD,
                                P_MA_HO_SO_KNTC_GOC,
                                P_MA_DON_VI_XU_LY_CHINH
                                );
    ELSE
                 UPDATE KNTC_HO_SO A SET A.MA_NGUON_DON_DEN=P_MA_NGUON_DON_DEN,
                                         A.NGAY_TIEP_DAN=P_NGAY_TIEP_DAN,
                                         A.VU_VIEC_MOI=P_VU_VIEC_MOI,
                                         A.NOI_DUNG_TIEP_DAN=P_NOI_DUNG_TIEP_DAN,
                                         A.YEU_CAU_GAP_LD=P_YEU_CAU_GAP_LD,
                                         A.NGAY_YCGLD=P_NGAY_YCGLD,
                                         A.MA_LD_TIEP_DAN= CASE WHEN P_MA_LD_TIEP_DAN =0 THEN NULL ELSE P_MA_LD_TIEP_DAN END,
                                         A.Y_KIEN_LD=P_Y_KIEN_LD,
                                         A.MA_LINH_VUC_KNTC=P_MA_LINH_VUC_KNTC,
                                         A.MA_LOAI_KNTC=P_MA_LOAI_KNTC,
                                         A.KNTC_MA_LOAI_DT=P_KNTC_MA_LOAI_DT,
                                         A.KNTC_CMND=P_KNTC_CMND,
                                         A.KNTC_HO_VA_TEN=P_KNTC_HO_VA_TEN,
                                         A.KNTC_MA_PHUONG_XA=P_KNTC_MA_PHUONG_XA,
                                         A.KNTC_SO_NHA=P_KNTC_SO_NHA,
                                         A.KNTC_TEN_CQTC=P_KNTC_TEN_CQTC,
                                         A.KNTC_DIA_CHI_CQTC=P_KNTC_DIA_CHI_CQTC,
                                         A.KNTC_SO_NGUOI_DAI_DIEN=V_KNTC_SO_NGUOI_DAI_DIEN,
                                         A.KNTC_TEN_NHUNG_NGUOI_DD=P_KNTC_TEN_NHUNG_NGUOI_DD,
                                         A.MA_HO_SO_KNTC_GOC=P_MA_HO_SO_KNTC_GOC,
                                         A.MA_DON_VI_XU_LY_CHINH=P_MA_DON_VI_XU_LY_CHINH
              WHERE A.MA_HO_SO_KNTC=P_MA_HO_SO_KNTC;
    END IF;
    P_VAL:='THANH_CONG';
  END;
  -------------------------------------------------------------------------------
  PROCEDURE UPDATE_NGUOI_BI_KNTC(P_VAL OUT NVARCHAR2,
                          P_MA_HO_SO_KNTC INT,
                          P_BKNTC_MA_LOAI_DT NVARCHAR2,
                          P_BKNTC_HO_VA_TEN NVARCHAR2,
                          P_BKNTC_DIA_CHI NVARCHAR2,
                          P_BKNTC_TEN_CQTC NVARCHAR2,
                          P_BKNTC_DIA_CHI_CQTC NVARCHAR2
                        ) AS
 BEGIN
    P_VAL:='THAT_BAI';
            UPDATE KNTC_HO_SO A SET A.BKNTC_MA_LOAI_DT=P_BKNTC_MA_LOAI_DT,
                                         A.BKNTC_HO_VA_TEN=P_BKNTC_HO_VA_TEN,
                                         A.BKNTC_DIA_CHI=P_BKNTC_DIA_CHI,
                                         A.BKNTC_TEN_CQTC=P_BKNTC_TEN_CQTC,
                                         A.BKNTC_DIA_CHI_CQTC=P_BKNTC_DIA_CHI_CQTC
              WHERE A.MA_HO_SO_KNTC=P_MA_HO_SO_KNTC;
    P_VAL:='THANH_CONG';
  END;


    -------------------------------------------------------------------------------
  PROCEDURE UPDATE_GIAI_QUYET(P_VAL OUT NVARCHAR2,
                          P_MA_HO_SO_KNTC INT,
                          P_MA_GIAI_QUYET INT,
                          P_MA_CAN_BO INT,
                          P_NGAY_GIAI_QUYET NVARCHAR2,
                          P_LAN_GIAI_QUYET INT,
                          P_MA_HUONG_GIAI_QUYET NVARCHAR2,
                          P_NOI_DUNG_GIAI_QUYET NVARCHAR2,
                          P_TIEN_DO_XU_LY NVARCHAR2,
                          P_SO_KY_HIEU NVARCHAR2,
                          P_TRICH_YEU NVARCHAR2,
                          P_CO_QUAN_BAN_HANH NVARCHAR2,
                          P_SO_BUT_LUC NVARCHAR2,
                          P_SO_TRANG NVARCHAR2,
                          P_NGAY_TRINH_LANH_DAO_KY NVARCHAR2,
                          P_NGAY_LANH_DAO_KY NVARCHAR2,
                          P_NGAY_CHUYEN_VAN_BAN NVARCHAR2,
                          P_MA_LANH_DAO_KY INT,
                          P_FILE_VAN_BAN NVARCHAR2,
                          P_DIEU_KIEN_XU_LY INT,
                          P_THAM_QUYEN_GIAI_QUYET INT
                        ) AS
 BEGIN
    P_VAL:='THAT_BAI';
    IF P_MA_GIAI_QUYET = 0 THEN
       INSERT INTO KNTC_GIAI_QUYET (
                                    MA_HO_SO_KNTC,
                                    MA_CAN_BO,
                                    NGAY_GIAI_QUYET,
                                    MA_HUONG_GIAI_QUYET,
                                    NOI_DUNG_GIAI_QUYET,
                                    TIEN_DO_XU_LY,
                                    SO_KY_HIEU,
                                    TRICH_YEU,
                                    CO_QUAN_BAN_HANH,
                                    SO_BUT_LUC,
                                    SO_TRANG,
                                    LAN_GIAI_QUYET,
                                    NGAY_TRINH_LANH_DAO_KY,
                                    NGAY_LANH_DAO_KY,
                                    NGAY_CHUYEN_VAN_BAN,
                                    MA_LANH_DAO_KY,
                                    FILE_VAN_BAN,
                                    DIEU_KIEN_XU_LY,
                                    THAM_QUYEN_GIAI_QUYET
                                    )
                              VALUES(
                                    P_MA_HO_SO_KNTC,
                                    P_MA_CAN_BO,
                                    P_NGAY_GIAI_QUYET,
                                    P_MA_HUONG_GIAI_QUYET,
                                    P_NOI_DUNG_GIAI_QUYET,
                                    P_TIEN_DO_XU_LY,
                                    P_SO_KY_HIEU,
                                    P_TRICH_YEU,
                                    P_CO_QUAN_BAN_HANH,
                                    P_SO_BUT_LUC,
                                    P_SO_TRANG,
                                    P_LAN_GIAI_QUYET,
                                    P_NGAY_TRINH_LANH_DAO_KY,
                                    P_NGAY_LANH_DAO_KY,
                                    P_NGAY_CHUYEN_VAN_BAN,
                                    P_MA_LANH_DAO_KY,
                                    P_FILE_VAN_BAN,
                                    P_DIEU_KIEN_XU_LY,
                                    P_THAM_QUYEN_GIAI_QUYET
                                    );
    ELSE
       UPDATE KNTC_GIAI_QUYET A SET A.NGAY_GIAI_QUYET     = P_NGAY_GIAI_QUYET,
                                    A.MA_HUONG_GIAI_QUYET = P_MA_HUONG_GIAI_QUYET,
                                    A.NOI_DUNG_GIAI_QUYET = P_NOI_DUNG_GIAI_QUYET,
                                    A.TIEN_DO_XU_LY       = P_TIEN_DO_XU_LY,
                                    A.SO_KY_HIEU          = P_SO_KY_HIEU,
                                    A.TRICH_YEU           = P_TRICH_YEU,
                                    A.CO_QUAN_BAN_HANH    = P_CO_QUAN_BAN_HANH,
                                    A.SO_BUT_LUC          = P_SO_BUT_LUC,
                                    A.SO_TRANG            = P_SO_TRANG,
                                    A.LAN_GIAI_QUYET      = P_LAN_GIAI_QUYET,
                                    A.NGAY_TRINH_LANH_DAO_KY= P_NGAY_TRINH_LANH_DAO_KY,
                                    A.NGAY_LANH_DAO_KY      = P_NGAY_LANH_DAO_KY,
                                    A.NGAY_CHUYEN_VAN_BAN   = P_NGAY_CHUYEN_VAN_BAN,
                                    A.MA_LANH_DAO_KY        = P_MA_LANH_DAO_KY,                                    
                                    A.FILE_VAN_BAN          = P_FILE_VAN_BAN,
                                    A.DIEU_KIEN_XU_LY       = P_DIEU_KIEN_XU_LY,
                                    A.THAM_QUYEN_GIAI_QUYET = P_THAM_QUYEN_GIAI_QUYET
                              WHERE A.MA_GIAI_QUYET=P_MA_GIAI_QUYET;
    END IF;        
    P_VAL:='THANH_CONG';
  END;
  -------------------------------------------------------------------------------
  PROCEDURE DELETE_GIAI_QUYET(P_VAL OUT NVARCHAR2, P_MA_GIAI_QUYET INT) AS
  BEGIN
    P_VAL:='THAT_BAI';
    DELETE KNTC_GIAI_QUYET A WHERE A.MA_GIAI_QUYET = P_MA_GIAI_QUYET;      
    P_VAL:='THANH_CONG';
  END;
  -------------------------------------------------------------------------------
  PROCEDURE SELECT_GIAI_QUYET(P_CUR IN OUT MYCUR, P_MA_HO_SO_KNTC INT) AS
  V_MAX_LAN_GIAI_QUYET INT;
  BEGIN
    SELECT MAX(LAN_GIAI_QUYET) INTO V_MAX_LAN_GIAI_QUYET FROM KNTC_GIAI_QUYET A WHERE A.MA_HO_SO_KNTC = P_MA_HO_SO_KNTC;
    IF V_MAX_LAN_GIAI_QUYET IS NULL THEN
       V_MAX_LAN_GIAI_QUYET:=0;
    END IF; 
    OPEN P_CUR FOR
     SELECT A.*,
            B.TEN_HUONG_GIAI_QUYET, 
            V_MAX_LAN_GIAI_QUYET AS MAX_LAN_GIAI_QUYET
        FROM KNTC_GIAI_QUYET A, DM_HUONG_GIAI_QUYET B
        WHERE A.MA_HO_SO_KNTC = P_MA_HO_SO_KNTC
          AND A.MA_HUONG_GIAI_QUYET = B.MA_HUONG_GIAI_QUYET
          ORDER BY A.LAN_GIAI_QUYET;
  END;
  
 PROCEDURE UPDATE_FILE_GIAI_QUYET(P_VAL OUT NVARCHAR2, P_MA_GIAI_QUYET NVARCHAR2, P_FILE_VAN_BAN NVARCHAR2) AS
   BEGIN
      UPDATE KNTC_GIAI_QUYET A SET A.FILE_VAN_BAN          = P_FILE_VAN_BAN
                              WHERE A.MA_GIAI_QUYET=P_MA_GIAI_QUYET;
      P_VAL:='TC';
   END;
/*  -----------------------------------------------------------------------------
  PROCEDURE UPDATE_TIEP_DAN_CO_DON(P_VAL OUT NVARCHAR2,
                          P_MA_HO_SO_KNTC INT,
                          P_MA_NGUON_DON_DEN NVARCHAR2,
                          P_NGAY_TIEP_DAN NVARCHAR2,
                          P_VU_VIEC_MOI INT,
                          P_NOI_DUNG_TIEP_DAN NVARCHAR2,
                          P_YEU_CAU_GAP_LD INT,
                          P_MA_LD_TIEP_DAN INT,
                          P_Y_KIEN_LD NVARCHAR2,
                          P_MA_CAN_BO_TIEP_DAN INT,
                          P_MA_CO_QUAN_TIEP_DAN NVARCHAR2,
                          P_MA_LINH_VUC_KNTC NVARCHAR2,   
                          P_MA_LOAI_KNTC NVARCHAR2,
                          P_KNTC_MA_LOAI_DT NVARCHAR2,
                          P_KNTC_CMND NVARCHAR2,
                          P_KNTC_HO_VA_TEN NVARCHAR2,
                          P_KNTC_MA_PHUONG_XA NVARCHAR2,
                          P_KNTC_SO_NHA NVARCHAR2,
                          P_KNTC_TEN_CQTC NVARCHAR2,
                          P_KNTC_DIA_CHI_CQTC NVARCHAR2,
                          P_KNTC_SO_NGUOI_DAI_DIEN INT,
                          P_KNTC_TEN_NHUNG_NGUOI_DD NVARCHAR2,
                          P_MA_HO_SO_KNTC_GOC INT,
                          P_MA_DON_VI_XU_LY_CHINH NVARCHAR2,
                          P_MA_TRANG_THAI_GIAI_QUYET  NVARCHAR2
                        ) AS
 V_KNTC_SO_NGUOI_DAI_DIEN INT;
 V_SO_HO_SO_KNTC INT;
 BEGIN
    P_VAL:='THAT_BAI';
    IF P_KNTC_MA_LOAI_DT = 'TC'  OR P_KNTC_MA_LOAI_DT = 'CN' or P_KNTC_SO_NGUOI_DAI_DIEN IS NULL THEN
       V_KNTC_SO_NGUOI_DAI_DIEN:=1;
    ELSE 
       V_KNTC_SO_NGUOI_DAI_DIEN:=P_KNTC_SO_NGUOI_DAI_DIEN;
    END IF;
    IF P_MA_HO_SO_KNTC = 0 THEN
                SELECT MAX(SO_HO_SO_KNTC) INTO V_SO_HO_SO_KNTC FROM KNTC_HO_SO;
                IF V_SO_HO_SO_KNTC IS NULL THEN
                   V_SO_HO_SO_KNTC:=1;
                END IF;
                INSERT INTO KNTC_HO_SO(
                                SO_HO_SO_KNTC,
                                NGAY_NHAP,
                                MA_NGUON_DON_DEN,
                                NGAY_TIEP_DAN,
                                VU_VIEC_MOI,
                                NOI_DUNG_TIEP_DAN,
                                YEU_CAU_GAP_LD,
                                MA_LD_TIEP_DAN,
                                Y_KIEN_LD,
                                MA_CAN_BO_TIEP_DAN,
                                MA_CO_QUAN_TIEP_DAN,
                                MA_LINH_VUC_KNTC,   
                                MA_LOAI_KNTC,
                                KNTC_MA_LOAI_DT,
                                KNTC_CMND,
                                KNTC_HO_VA_TEN,
                                KNTC_MA_PHUONG_XA,
                                KNTC_SO_NHA,
                                KNTC_TEN_CQTC,
                                KNTC_DIA_CHI_CQTC,
                                KNTC_SO_NGUOI_DAI_DIEN,
                                KNTC_TEN_NHUNG_NGUOI_DD,
                                MA_HO_SO_KNTC_GOC,
                                MA_DON_VI_XU_LY_CHINH,
                                MA_TRANG_THAI_GIAI_QUYET
                                )
                         VALUES (
                                V_SO_HO_SO_KNTC,
                                SYSDATE,
                                P_MA_NGUON_DON_DEN,
                                P_NGAY_TIEP_DAN,
                                P_VU_VIEC_MOI,
                                P_NOI_DUNG_TIEP_DAN,
                                P_YEU_CAU_GAP_LD,
                                CASE WHEN P_MA_LD_TIEP_DAN =0 THEN NULL ELSE P_MA_LD_TIEP_DAN END,
                                P_Y_KIEN_LD,
                                P_MA_CAN_BO_TIEP_DAN,
                                P_MA_CO_QUAN_TIEP_DAN,
                                P_MA_LINH_VUC_KNTC,   
                                P_MA_LOAI_KNTC,
                                P_KNTC_MA_LOAI_DT,
                                P_KNTC_CMND,
                                P_KNTC_HO_VA_TEN,
                                P_KNTC_MA_PHUONG_XA,
                                P_KNTC_SO_NHA,
                                P_KNTC_TEN_CQTC,
                                P_KNTC_DIA_CHI_CQTC,
                                V_KNTC_SO_NGUOI_DAI_DIEN,
                                P_KNTC_TEN_NHUNG_NGUOI_DD,
                                P_MA_HO_SO_KNTC_GOC,
                                P_MA_DON_VI_XU_LY_CHINH,
                                P_MA_TRANG_THAI_GIAI_QUYET
                                );
    ELSE
            UPDATE KNTC_HO_SO A SET A.MA_NGUON_DON_DEN=P_MA_NGUON_DON_DEN,
                                         A.NGAY_TIEP_DAN=P_NGAY_TIEP_DAN,
                                         A.VU_VIEC_MOI=P_VU_VIEC_MOI,
                                         A.NOI_DUNG_TIEP_DAN=P_NOI_DUNG_TIEP_DAN,
                                         A.YEU_CAU_GAP_LD=P_YEU_CAU_GAP_LD,
                                         A.MA_LD_TIEP_DAN= CASE WHEN P_MA_LD_TIEP_DAN =0 THEN NULL ELSE P_MA_LD_TIEP_DAN END,
                                         A.Y_KIEN_LD=P_Y_KIEN_LD,
                                         A.MA_LINH_VUC_KNTC=P_MA_LINH_VUC_KNTC,
                                         A.MA_LOAI_KNTC=P_MA_LOAI_KNTC,
                                         A.KNTC_MA_LOAI_DT=P_KNTC_MA_LOAI_DT,
                                         A.KNTC_CMND=P_KNTC_CMND,
                                         A.KNTC_HO_VA_TEN=P_KNTC_HO_VA_TEN,
                                         A.KNTC_MA_PHUONG_XA=P_KNTC_MA_PHUONG_XA,
                                         A.KNTC_SO_NHA=P_KNTC_SO_NHA,
                                         A.KNTC_TEN_CQTC=P_KNTC_TEN_CQTC,
                                         A.KNTC_DIA_CHI_CQTC = P_KNTC_DIA_CHI_CQTC,
                                         A.KNTC_SO_NGUOI_DAI_DIEN=V_KNTC_SO_NGUOI_DAI_DIEN,
                                         A.KNTC_TEN_NHUNG_NGUOI_DD=P_KNTC_TEN_NHUNG_NGUOI_DD,
                                         A.MA_HO_SO_KNTC_GOC=P_MA_HO_SO_KNTC_GOC,
                                         A.MA_DON_VI_XU_LY_CHINH=P_MA_DON_VI_XU_LY_CHINH,
                                         A.MA_TRANG_THAI_GIAI_QUYET=P_MA_TRANG_THAI_GIAI_QUYET
              WHERE A.MA_HO_SO_KNTC=P_MA_HO_SO_KNTC;
    END IF;
    P_VAL:='THANH_CONG';
  END;*/
-------------------------------------------------------------------------------
  PROCEDURE M2A(P_CUR IN OUT MYCUR, P_TU_NGAY NVARCHAR2, P_DEN_NGAY NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT 
    -- tất cả
     --TAT_CA.MA_DON_VI_XU_LY_CHINH AS MA_DON_VI,
     TAT_CA.TEN_DON_VI,
     TAT_CA.TC_SO_LUOT,
     TAT_CA.TC_SO_NGUOI,
     TAT_CA.TC_MOI_PHAT_SINH,
     TAT_CA.TC_VU_VIEC_CU,
     -- tiếp thường xuyên cá nhân
     TXTCCN.TXTCCN_SO_LUOT,
     TXTCCN.TXTCCN_SO_NGUOI,
     TXTCCN.TXTCCN_MOI_PHAT_SINH,
     TXTCCN.TXTCCN_VU_VIEC_CU,
     -- tiếp thường xuyên tập thể, tổ chức
     TXTT.TXTT_SO_LUOT,
     TXTT.TXTT_SO_NGUOI,
     TXTT.TXTT_MOI_PHAT_SINH,
     TXTT.TXTT_VU_VIEC_CU,
     -- tiếp đột xuất lãnh đạo cá nhân
     LDTCCN.LDTCCN_SO_LUOT,
     LDTCCN.LDTCCN_SO_NGUOI,
     LDTCCN.LDTCCN_MOI_PHAT_SINH,
     LDTCCN.LDTCCN_VU_VIEC_CU,
     -- tiếp đột xuất lãnh đạo tập thể, tổ chức
     LDTT.LDTT_SO_LUOT,
     LDTT.LDTT_SO_NGUOI,
     LDTT.LDTT_MOI_PHAT_SINH,
     LDTT.LDTT_VU_VIEC_CU,
     -- các lĩnh vực khiếu nại
     KN_TCDNGT.KN_TCDNGT,
     KN_CS.KN_CS,
     KN_TS.KN_TS,
     KN_CDCC.KN_CDCC,
     KN_TP.KN_TP,
     KN_CTVHXH.KN_CTVHXH,
     -- các lĩnh vực tố cáo
     TC_HC.TC_HC,
     TC_TP.TC_TP,
     TC_TN.TC_TN,
     -- phản ánh kiến nghị khác
     PA_KN_K.PA_KN_K,
     -- kết quả giải quyết
     CGQ.CGQ,
     CCQD.CCQD,
     DCQD.DCQD,
     DCBA.DCBA
     FROM 
       (SELECT A.MA_DON_VI_XU_LY_CHINH , B.TEN_DON_VI, 
                    COUNT(A.MA_HO_SO_KNTC)                      AS TC_SO_LUOT,
                    SUM(A.KNTC_SO_NGUOI_DAI_DIEN)               AS TC_SO_NGUOI,
                    SUM(A.VU_VIEC_MOI)                          AS TC_MOI_PHAT_SINH,
                    COUNT(A.MA_HO_SO_KNTC) - SUM(A.VU_VIEC_MOI) AS TC_VU_VIEC_CU
                FROM KNTC_HO_SO A, CB_DON_VI B
                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 IS NULL
                AND A.MA_DON_VI_XU_LY_CHINH=B.MA_DON_VI
                GROUP BY A.MA_DON_VI_XU_LY_CHINH, B.TEN_DON_VI
        ) TAT_CA,-- tồng cộng tất cả của đơn vị
        
       (SELECT A.MA_DON_VI_XU_LY_CHINH, 
                    COUNT(A.MA_HO_SO_KNTC)                      AS TXTCCN_SO_LUOT,
                    SUM(A.KNTC_SO_NGUOI_DAI_DIEN)               AS TXTCCN_SO_NGUOI,
                    SUM(A.VU_VIEC_MOI)                          AS TXTCCN_MOI_PHAT_SINH,
                    COUNT(A.MA_HO_SO_KNTC) - SUM(A.VU_VIEC_MOI) AS TXTCCN_VU_VIEC_CU
                FROM KNTC_HO_SO A
                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 IS NULL
                --AND A.KNTC_MA_LOAI_DT <> 'TT'
                AND A.MA_LD_TIEP_DAN IS NULL
                GROUP BY A.MA_DON_VI_XU_LY_CHINH
        ) TXTCCN,-- thường xuyên tiếp cá nhân
        
        (SELECT A.MA_DON_VI_XU_LY_CHINH, 
                    COUNT(A.MA_HO_SO_KNTC)                      AS TXTT_SO_LUOT,
                    SUM(A.KNTC_SO_NGUOI_DAI_DIEN)               AS TXTT_SO_NGUOI,
                    SUM(A.VU_VIEC_MOI)                          AS TXTT_MOI_PHAT_SINH,
                    COUNT(A.MA_HO_SO_KNTC) - SUM(A.VU_VIEC_MOI) AS TXTT_VU_VIEC_CU
                FROM KNTC_HO_SO A
                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 IS NULL
                AND A.KNTC_MA_LOAI_DT = 'TT'
                AND A.MA_LD_TIEP_DAN IS NULL
                GROUP BY A.MA_DON_VI_XU_LY_CHINH
        ) TXTT,-- thường xuyên tiếp tập thể
        
        (SELECT A.MA_DON_VI_XU_LY_CHINH, 
                    COUNT(A.MA_HO_SO_KNTC)                      AS LDTCCN_SO_LUOT,
                    SUM(A.KNTC_SO_NGUOI_DAI_DIEN)               AS LDTCCN_SO_NGUOI,
                    SUM(A.VU_VIEC_MOI)                          AS LDTCCN_MOI_PHAT_SINH,
                    COUNT(A.MA_HO_SO_KNTC) - SUM(A.VU_VIEC_MOI) AS LDTCCN_VU_VIEC_CU
                FROM KNTC_HO_SO A
                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 IS NULL
                AND A.KNTC_MA_LOAI_DT <> 'TT'
                AND A.MA_LD_TIEP_DAN IS NOT NULL
                GROUP BY A.MA_DON_VI_XU_LY_CHINH
         ) LDTCCN,-- lãnh đạo  tiếp cá nhân
         
        (SELECT A.MA_DON_VI_XU_LY_CHINH, 
                    COUNT(A.MA_HO_SO_KNTC)                      AS LDTT_SO_LUOT,
                    SUM(A.KNTC_SO_NGUOI_DAI_DIEN)               AS LDTT_SO_NGUOI,
                    SUM(A.VU_VIEC_MOI)                          AS LDTT_MOI_PHAT_SINH,
                    COUNT(A.MA_HO_SO_KNTC) - SUM(A.VU_VIEC_MOI) AS LDTT_VU_VIEC_CU
                FROM KNTC_HO_SO A
                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 IS NULL
                AND A.KNTC_MA_LOAI_DT = 'TT'
                AND A.MA_LD_TIEP_DAN IS NOT NULL
                GROUP BY A.MA_DON_VI_XU_LY_CHINH
        ) LDTT,-- lãnh đạo tiếp tập thể
        
        (SELECT A.MA_DON_VI_XU_LY_CHINH, COUNT(A.MA_HO_SO_KNTC) AS KN_TCDNGT
                FROM KNTC_HO_SO A
                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 IS NULL
                AND A.MA_LINH_VUC_KNTC = 'TC'
                AND A.MA_LOAI_KNTC = 'KN'
                GROUP BY A.MA_DON_VI_XU_LY_CHINH
        ) KN_TCDNGT,-- HIẾU NẠI-- Về tranh chấp, đòi đất cũ, đền bù, giải tỏa
        
        (SELECT A.MA_DON_VI_XU_LY_CHINH, COUNT(A.MA_HO_SO_KNTC) AS KN_CS
                FROM KNTC_HO_SO A
                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 IS NULL
                AND A.MA_LINH_VUC_KNTC = 'CS'
                AND A.MA_LOAI_KNTC = 'KN'
                GROUP BY A.MA_DON_VI_XU_LY_CHINH
        ) KN_CS,-- KHIẾU NẠI-- Về chính sách
        
        (SELECT A.MA_DON_VI_XU_LY_CHINH, COUNT(A.MA_HO_SO_KNTC) AS KN_TS
                FROM KNTC_HO_SO A
                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 IS NULL
                AND A.MA_LINH_VUC_KNTC = 'TS'
                AND A.MA_LOAI_KNTC = 'KN'
                GROUP BY A.MA_DON_VI_XU_LY_CHINH
        ) KN_TS,-- KHIẾU NẠI-- Về tài sản
        
        (SELECT A.MA_DON_VI_XU_LY_CHINH, COUNT(A.MA_HO_SO_KNTC) AS KN_CDCC
                FROM KNTC_HO_SO A
                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 IS NULL
                AND A.MA_LINH_VUC_KNTC = 'TC'
                AND A.MA_LOAI_KNTC = 'KN'
                GROUP BY A.MA_DON_VI_XU_LY_CHINH
        ) KN_CDCC,-- KHIẾU NẠI-- Về chế dộ công chức viên chức
        
        (SELECT A.MA_DON_VI_XU_LY_CHINH, COUNT(A.MA_HO_SO_KNTC) AS KN_TP
                FROM KNTC_HO_SO A
                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 IS NULL
                AND A.MA_LINH_VUC_KNTC = 'TP'
                AND A.MA_LOAI_KNTC = 'KN'
                GROUP BY A.MA_DON_VI_XU_LY_CHINH
        ) KN_TP,-- KHIẾU NẠI-- về tư pháp
        
        (SELECT A.MA_DON_VI_XU_LY_CHINH, COUNT(A.MA_HO_SO_KNTC) AS KN_CTVHXH
                FROM KNTC_HO_SO A
                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 IS NULL
                AND A.MA_LINH_VUC_KNTC = 'CTVHXH'
                AND A.MA_LOAI_KNTC = 'KN'
                GROUP BY A.MA_DON_VI_XU_LY_CHINH
        ) KN_CTVHXH,-- KHIẾU NẠI-- về chính trị văn hóa xã hội khác
        
        (SELECT A.MA_DON_VI_XU_LY_CHINH, COUNT(A.MA_HO_SO_KNTC) AS TC_HC
                FROM KNTC_HO_SO A
                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 IS NULL
                AND A.MA_LINH_VUC_KNTC = 'HC'
                AND A.MA_LOAI_KNTC = 'TC'
                GROUP BY A.MA_DON_VI_XU_LY_CHINH
        ) TC_HC,-- TỐ CÁO-- Về hành chính
        
        (SELECT A.MA_DON_VI_XU_LY_CHINH, COUNT(A.MA_HO_SO_KNTC) AS TC_TP
                FROM KNTC_HO_SO A
                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 IS NULL
                AND A.MA_LINH_VUC_KNTC = 'TP'
                AND A.MA_LOAI_KNTC = 'TC'
                GROUP BY A.MA_DON_VI_XU_LY_CHINH
        ) TC_TP,-- TỐ CÁO-- Về tư pháp
        
        (SELECT A.MA_DON_VI_XU_LY_CHINH, COUNT(A.MA_HO_SO_KNTC) AS TC_TN
                FROM KNTC_HO_SO A
                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 IS NULL
                AND A.MA_LINH_VUC_KNTC = 'TN'
                AND A.MA_LOAI_KNTC = 'TC'
                GROUP BY A.MA_DON_VI_XU_LY_CHINH
        ) TC_TN,-- TỐ CÁO-- về tham nhũng
        
        (SELECT A.MA_DON_VI_XU_LY_CHINH, COUNT(A.MA_HO_SO_KNTC) AS PA_KN_K
                FROM KNTC_HO_SO A
                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 IS NULL
                  AND A.MA_LOAI_KNTC = 'K'
                GROUP BY A.MA_DON_VI_XU_LY_CHINH
        ) PA_KN_K,-- Phản ánh kiến nghị khác
        
        (SELECT A.MA_DON_VI_XU_LY_CHINH, COUNT(A.MA_HO_SO_KNTC) AS CGQ
                FROM KNTC_HO_SO A
                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 IS NULL
                AND A.MA_TRANG_THAI_GIAI_QUYET = 'CGQ'
                GROUP BY A.MA_DON_VI_XU_LY_CHINH
        ) CGQ,-- chua giai quyet
        
        (SELECT A.MA_DON_VI_XU_LY_CHINH, COUNT(A.MA_HO_SO_KNTC) AS CCQD
                FROM KNTC_HO_SO A
                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 IS NULL
                  AND A.MA_TRANG_THAI_GIAI_QUYET = 'CCQD'
                GROUP BY A.MA_DON_VI_XU_LY_CHINH
        ) CCQD,-- chua co quyet dinh
        (SELECT A.MA_DON_VI_XU_LY_CHINH, COUNT(A.MA_HO_SO_KNTC) AS DCQD
                FROM KNTC_HO_SO A
                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 IS NULL
                  AND A.MA_TRANG_THAI_GIAI_QUYET = 'DCQD'
                GROUP BY A.MA_DON_VI_XU_LY_CHINH
        ) DCQD,-- da co quyet dinh
        
        (SELECT A.MA_DON_VI_XU_LY_CHINH, COUNT(A.MA_HO_SO_KNTC) AS DCBA
                FROM KNTC_HO_SO A
                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 IS NULL
                  AND A.MA_TRANG_THAI_GIAI_QUYET = 'DCBA'
                GROUP BY A.MA_DON_VI_XU_LY_CHINH
        ) DCBA-- da co ban an
        
        
        WHERE TAT_CA.MA_DON_VI_XU_LY_CHINH = TXTCCN.MA_DON_VI_XU_LY_CHINH(+)
          AND TAT_CA.MA_DON_VI_XU_LY_CHINH = TXTT.MA_DON_VI_XU_LY_CHINH(+)
          AND TAT_CA.MA_DON_VI_XU_LY_CHINH = LDTT.MA_DON_VI_XU_LY_CHINH(+)
          AND TAT_CA.MA_DON_VI_XU_LY_CHINH = LDTCCN.MA_DON_VI_XU_LY_CHINH(+)
          AND TAT_CA.MA_DON_VI_XU_LY_CHINH = KN_TCDNGT.MA_DON_VI_XU_LY_CHINH(+)
          AND TAT_CA.MA_DON_VI_XU_LY_CHINH = KN_CS.MA_DON_VI_XU_LY_CHINH(+)
          AND TAT_CA.MA_DON_VI_XU_LY_CHINH = KN_TS.MA_DON_VI_XU_LY_CHINH(+)
          AND TAT_CA.MA_DON_VI_XU_LY_CHINH = KN_CDCC.MA_DON_VI_XU_LY_CHINH(+)
          AND TAT_CA.MA_DON_VI_XU_LY_CHINH = KN_TP.MA_DON_VI_XU_LY_CHINH(+)
          AND TAT_CA.MA_DON_VI_XU_LY_CHINH = KN_CTVHXH.MA_DON_VI_XU_LY_CHINH(+)
          AND TAT_CA.MA_DON_VI_XU_LY_CHINH = TC_HC.MA_DON_VI_XU_LY_CHINH(+)
          AND TAT_CA.MA_DON_VI_XU_LY_CHINH = TC_TP.MA_DON_VI_XU_LY_CHINH(+)
          AND TAT_CA.MA_DON_VI_XU_LY_CHINH = TC_TN.MA_DON_VI_XU_LY_CHINH(+)
          AND TAT_CA.MA_DON_VI_XU_LY_CHINH = PA_KN_K.MA_DON_VI_XU_LY_CHINH(+)
          AND TAT_CA.MA_DON_VI_XU_LY_CHINH = CGQ.MA_DON_VI_XU_LY_CHINH(+)
          AND TAT_CA.MA_DON_VI_XU_LY_CHINH = CCQD.MA_DON_VI_XU_LY_CHINH(+)
          AND TAT_CA.MA_DON_VI_XU_LY_CHINH = DCQD.MA_DON_VI_XU_LY_CHINH(+)
          AND TAT_CA.MA_DON_VI_XU_LY_CHINH = DCBA.MA_DON_VI_XU_LY_CHINH(+)
          ;
  END;   
  -----------------------------------------------------------------------------
  PROCEDURE M2B(P_CUR IN OUT MYCUR, P_TU_NGAY NVARCHAR2, P_DEN_NGAY NVARCHAR2) AS
  BEGIN
    OPEN P_CUR FOR SELECT 
     TAT_CA.MA_DON_VI_XU_LY_CHINH AS MA_DON_VI,
     TAT_CA.TC_SO_DON,
     TNTK.TNTK_NHIEU_NGUOI,
     TAT_CA.TC_SO_DON - TNTK.TNTK_NHIEU_NGUOI AS TNTK_MOT_NGUOI
     FROM 
       (SELECT A.MA_DON_VI_XU_LY_CHINH, COUNT(A.MA_HO_SO_KNTC) AS TC_SO_DON
                FROM KNTC_HO_SO A
                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 IS NOT NULL
                GROUP BY A.MA_DON_VI_XU_LY_CHINH
        ) TAT_CA,
        
       (SELECT A.MA_DON_VI_XU_LY_CHINH, COUNT(A.MA_HO_SO_KNTC) AS TNTK_NHIEU_NGUOI
                FROM KNTC_HO_SO A
                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 IS NOT NULL
                AND A.KNTC_SO_NGUOI_DAI_DIEN > 0
                GROUP BY A.MA_DON_VI_XU_LY_CHINH
        ) TNTK -- tiếp nhận trong kỳ
        
        WHERE TAT_CA.MA_DON_VI_XU_LY_CHINH = TNTK.MA_DON_VI_XU_LY_CHINH(+)
          ;
  END;    
END KNTC;
/


spool off
