EOFFICE - GD4

DECLARE @REQ_ID VARCHAR
SET @REQ_ID=(SELECT REQ_ID FROM PL_REQUEST_DOC WHERE REQ_CODE ='0073/2024/TTr-0690405')
UPDATE PL_REQUEST_DOC SET PROCESS_ID ='HDQT' WHERE REQ_ID =@REQ_ID
UPDATE PL_REQUEST_PROCESS SET STATUS ='P', CHECKER_ID ='toailc',APPROVE_DT =GETDATE, NOTES =N'Thư Ký VP HĐQT đã phê duyệt' WHERE REQ_ID =@REQ_ID AND PROCESS_ID ='TKHDQT'
UPDATE PL_REQUEST_PROCESS SET STATUS ='C' WHERE REQ_ID =@REQ_ID AND PROCESS_ID ='HDQT'
EXEC dbo.PL_REQUEST_PROCESS_App @p_REQ_ID = @REQ_ID, -- varchar(15)
@p_AUTH_STATUS = 'A', -- varchar(1)
@p_CHECKER_ID = 'taila', -- varchar(15)
@p_APPROVE_DT = '21/11/2024', -- datetime
@p_ROLE_LOGIN = 'HDQT', -- varchar(50)
@p_BRANCH_LOGIN = 'DV0001', -- varchar(15)
@p_PROCESS_DESC = N'Phê duyệt' , -- nvarchar(max)
@p_IS_AUTHORITY =0

0011/2024/TTr-0690604

SELECT DISTINCT A.DESCRIPTION,A.HANGHOA_ID,A.TOTAL_AMT,B.APPROVE_DT FROM TR_REQUEST_DOC_DT A INNER JOIN TR_REQUEST_DOC B ON A.REQ_DOC_ID =B.REQ_ID AND B.PROCESS_ID = 'APPROVE' GROUP BY DESCRIPTION,HANGHOA_ID,A.TOTAL_AMT,B.APPROVE_DT

CREATE PROCEDURE dbo.rpt_TR_REQUEST_DOC_ReportPrice10To100M
@p_REQ_ID varchar(15)
AS

----Nhà cung cấp-------
    SELECT DISTINCT A.SUPPLIER_1, A.SUPPLIER_2, A.SUPPLIER_3, A.SUPPLIER_4, A.SUPPLIER_5
    FROM TR_REQ_DOC_XETGIA_DUOI_100M_DT A
    LEFT JOIN TR_REQ_DOC_XETGIA_DUOI_100M B ON A.RECORD_MASTER_ID = B.RECORD_ID
    WHERE B.REQ_DOC_ID = @p_REQ_ID

-------Thông tin chi tiết xét giá-----------
    SELECT ROW_NUMBER() OVER (ORDER BY RESULT.RECORD_DT_ID) AS STT, RESULT.* FROM  
    (   
        SELECT 
        A.RECORD_DT_ID,
        A.GOOD_NAME,
        A.TECH_SPECIFICATION,
        A.UNIT,
        A.QUANTITY,
        (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.UNIT_PRICE_1)) 
        AS UNIT_PRICE_1,
        (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.TOTAL_AMT_1)) 
        AS TOTAL_AMT_1,
        (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.UNIT_PRICE_2)) 
        AS UNIT_PRICE_2,
        (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.TOTAL_AMT_2)) 
        AS TOTAL_AMT_2,
        (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.UNIT_PRICE_3)) 
        AS UNIT_PRICE_3,
        (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.TOTAL_AMT_3)) 
        AS TOTAL_AMT_3,
        (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.UNIT_PRICE_4)) 
        AS UNIT_PRICE_4,
        (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.TOTAL_AMT_4)) 
        AS TOTAL_AMT_4,
        (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.UNIT_PRICE_5)) 
        AS UNIT_PRICE_5,
        (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.TOTAL_AMT_5)) 
        AS TOTAL_AMT_5

FROM TR_REQ_DOC_XETGIA_DUOI_100M_DT A
        LEFT JOIN TR_REQ_DOC_XETGIA_DUOI_100M B ON A.RECORD_MASTER_ID = B.RECORD_ID
        WHERE B.REQ_DOC_ID = @p_REQ_ID
    ) 
    AS RESULT

-------Thành tiền trước thuế-----------
    SELECT
    --CAST(ISNULL(SUM(A.TOTAL_AMT_1),0) AS DECIMAL(18,2)) 
    --AS SUM_TOTAL_AMT_1,
    (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_1),0))) 
    AS SUM_TOTAL_AMT_1,
    --CAST(ISNULL(SUM(A.TOTAL_AMT_2),0) AS DECIMAL(18,2)) 
    --AS SUM_TOTAL_AMT_2,
    (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_2),0))) 
    AS SUM_TOTAL_AMT_2,
    --CAST(ISNULL(SUM(A.TOTAL_AMT_3),0) AS DECIMAL(18,2)) 
    --AS SUM_TOTAL_AMT_3,
    (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_3),0))) 
    AS SUM_TOTAL_AMT_3,
    --CAST(ISNULL(SUM(A.TOTAL_AMT_4),0) AS DECIMAL(18,2)) 
    --AS SUM_TOTAL_AMT_4,
    (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_4),0))) 
    AS SUM_TOTAL_AMT_4,
    --CAST(ISNULL(SUM(A.TOTAL_AMT_5),0) AS DECIMAL(18,2)) 
    --AS SUM_TOTAL_AMT_5
    (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_5),0))) 
    AS SUM_TOTAL_AMT_5

FROM TR_REQ_DOC_XETGIA_DUOI_100M_DT A
    LEFT JOIN TR_REQ_DOC_XETGIA_DUOI_100M B ON A.RECORD_MASTER_ID = B.RECORD_ID
    WHERE B.REQ_DOC_ID = @p_REQ_ID

-------số tiền thuế----------
    SELECT
    --CAST(ISNULL(SUM(A.VAT_AMT_1),0) AS DECIMAL(18,2)) 
    --AS VAT_TOTAL_AMT_1,
    (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.VAT_AMT_1 * A.QUANTITY),0))) 
    AS VAT_TOTAL_AMT_1,
    --CAST(ISNULL(SUM(A.VAT_AMT_2),0) AS DECIMAL(18,2)) 
    --AS VAT_TOTAL_AMT_2,
    (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.VAT_AMT_2 * A.QUANTITY),0))) 
    AS VAT_TOTAL_AMT_2,
    --CAST(ISNULL(SUM(A.VAT_AMT_3),0) AS DECIMAL(18,2)) 
    --AS VAT_TOTAL_AMT_3,
    (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.VAT_AMT_3 * A.QUANTITY),0))) 
    AS VAT_TOTAL_AMT_3,
    --CAST(ISNULL(SUM(A.VAT_AMT_4),0) AS DECIMAL(18,2)) 
    --AS VAT_TOTAL_AMT_4,
    (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.VAT_AMT_4 * A.QUANTITY),0))) 
    AS VAT_TOTAL_AMT_4,
    --CAST(ISNULL(SUM(A.VAT_AMT_5),0) AS DECIMAL(18,2)) 
    --AS VAT_TOTAL_AMT_5
    (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.VAT_AMT_5 * A.QUANTITY),0))) 
    AS VAT_TOTAL_AMT_5

FROM TR_REQ_DOC_XETGIA_DUOI_100M_DT A
    LEFT JOIN TR_REQ_DOC_XETGIA_DUOI_100M B ON A.RECORD_MASTER_ID = B.RECORD_ID
    LEFT JOIN TR_REQUEST_DOC C ON B.REQ_DOC_ID = C.REQ_ID
    WHERE B.REQ_DOC_ID = @p_REQ_ID

-------thành tiền sau thuế------------
    SELECT
    --ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_1), 0) 
    --AS TOTAL_AMT_AFTER_VAT_1,
    (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_1),0))) 
    AS TOTAL_AMT_AFTER_VAT_1,
    --ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_2), 0) 
    --AS TOTAL_AMT_AFTER_VAT_2,
    (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_2),0))) 
    AS TOTAL_AMT_AFTER_VAT_2,
    --ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_3), 0)
    --AS TOTAL_AMT_AFTER_VAT_3,
    (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_3),0))) 
    AS TOTAL_AMT_AFTER_VAT_3,
    --ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_4), 0)
    --AS TOTAL_AMT_AFTER_VAT_4,
    (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_4),0))) 
    AS TOTAL_AMT_AFTER_VAT_4,
    --ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_5), 0)
    --AS TOTAL_AMT_AFTER_VAT_5
    (SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_5),0))) 
    AS TOTAL_AMT_AFTER_VAT_5

FROM TR_REQ_DOC_XETGIA_DUOI_100M_DT A
    LEFT JOIN TR_REQ_DOC_XETGIA_DUOI_100M B ON A.RECORD_MASTER_ID = B.RECORD_ID
    LEFT JOIN TR_REQUEST_DOC C ON B.REQ_DOC_ID = C.REQ_ID
    WHERE B.REQ_DOC_ID = @p_REQ_ID

-----Tên đơn vị DMMS--------
    --SELECT N'Trưởng ĐMMS ' + RESULT.DVDM_NAME + ' ' + RESULT.DVDM_BRANCH AS DMMS_TITLE
    --FROM(
    --    SELECT
    --    CASE
    --        ----TH có DMMS---
    --        WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME
    --        ---TH DMMS là đơn vị tạo---
    --        ELSE BR.BRANCH_NAME
    --    END AS DVDM_NAME,
    --    CASE
    --        ---Nếu DMMS nằm trong hội sở
    --        WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN N'Hội sở'
    --        ELSE ''
    --    END AS DVDM_BRANCH
    --    FROM PL_REQUEST_PROCESS PL
    --    LEFT JOIN CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
    --    LEFT JOIN CM_BRANCH BR ON PL.DVDM_ID = BR.BRANCH_ID
    --    WHERE PL.REQ_ID = @p_REQ_ID
    --) AS RESULT
    --WHERE RESULT.DVDM_NAME IS NOT NULL

---------------Kết luận + ghi chú-----------------------
    SELECT A.CONCLUSION, A.NOTES, 
    CASE WHEN ISNULL(A.NOTES, '') <> '' THEN 'Ghi chú:'
    ELSE ''
    END
    AS TITLE_NOTES
    FROM TR_REQ_DOC_XETGIA_DUOI_100M A
    LEFT JOIN TR_REQUEST_DOC B ON A.REQ_DOC_ID = B.REQ_ID
    WHERE A.REQ_DOC_ID = @p_REQ_ID

----------------------CHỮ KÝ-----------------------------
    DECLARE @t_SIGN_TABLE TABLE(APPROVE_DT VARCHAR(30), SIGN_NAME NVARCHAR(50), TYPE VARCHAR(15),USER_DOMAIN VARCHAR(50))
    DECLARE @p_APPROVE_DT_KSV VARCHAR(25), @p_KSV_NAME NVARCHAR(50),@p_USER_DOMAIN_KSV   VARCHAR(50),--KSV
            @p_APPROVE_DT_GDDV VARCHAR(25), @p_GDDV_NAME NVARCHAR(50), @p_USER_DOMAIN VARCHAR(50) --GDDV

INSERT INTO @t_SIGN_TABLE
        -----------------KSV DMMS----------------
        SELECT TOP 1 ISNULL(FORMAT(D.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT, 
        C.TLFullName AS SIGN_NAME,
        'KSV' AS TYPE, D.CHECKER_ID
        FROM PL_REQUEST_PROCESS_CHILD A
        LEFT JOIN PL_REQUEST_PROCESS B ON A.PROCESS_ID = B.ID
        LEFT JOIN TL_USER C ON A.TLNAME = C.TLNANME
        LEFT JOIN PL_PROCESS D ON B.REQ_ID = D.REQ_ID AND A.TLNAME = D.CHECKER_ID
        WHERE B.REQ_ID = @p_REQ_ID
        AND A.TYPE_JOB = 'KS'
        AND A.STATUS_JOB = 'P'
        AND B.PROCESS_ID = 'DMMS'
        ORDER BY D.ID DESC

INSERT INTO @t_SIGN_TABLE
        ---------------Trưởng đơn vị DMMS------------
        SELECT 
        DISTINCT ISNULL(FORMAT(A.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT,
        B.TLFullName AS SIGN_NAME,
        'GDDV' AS TYPE, A.CHECKER_ID AS USER_DOMAIN
        FROM PL_REQUEST_PROCESS A
        LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
        WHERE A.REQ_ID = @p_REQ_ID
        AND A.PROCESS_ID = 'DMMS'
        AND A.STATUS = 'P'

------KSV DMMS-----
    SET @p_APPROVE_DT_KSV = (SELECT A.APPROVE_DT FROM @t_SIGN_TABLE A WHERE A.TYPE = 'KSV')
    SET @p_KSV_NAME = (SELECT A.SIGN_NAME FROM @t_SIGN_TABLE A WHERE A.TYPE = 'KSV')
    SET @p_USER_DOMAIN_KSV = (SELECT A.USER_DOMAIN FROM @t_SIGN_TABLE A WHERE A.TYPE = 'KSV')
    ------GDDV DMMS----
    SET @p_APPROVE_DT_GDDV = (SELECT A.APPROVE_DT FROM @t_SIGN_TABLE A WHERE A.TYPE = 'GDDV')
    SET @p_GDDV_NAME = (SELECT A.SIGN_NAME FROM @t_SIGN_TABLE A WHERE A.TYPE = 'GDDV')
    SET @p_USER_DOMAIN = (SELECT A.USER_DOMAIN FROM @t_SIGN_TABLE A WHERE A.TYPE = 'GDDV')

------------LẤY TÊN NG DUYỆT---------------
    SELECT    ISNULL(@p_KSV_NAME, '') AS KSV_NAME, 
            ISNULL(@p_GDDV_NAME,'') AS DMMS_NAME,
            CASE WHEN ISNULL(@p_APPROVE_DT_KSV, '') <> '' THEN (SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@p_USER_DOMAIN_KSV)
            ELSE '' END TITLE_KSV,
            CASE WHEN ISNULL(@p_APPROVE_DT_GDDV, '') <> '' THEN (SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@p_USER_DOMAIN) --- LUCTV 30112023: FIX LẠI TÊN CHỨC DANH CHÍNH XÁC
            ELSE '' END TITLE_GDDV

-------------LẤY NGÀY DUYỆT--------------
    SELECT    ISNULL(@p_APPROVE_DT_KSV, '') AS APPROVE_DT_KSV, 
            ISNULL(@p_APPROVE_DT_GDDV, '') AS APPROVE_DT_GDDV

-------------KSV DMMS-------------------
    --SELECT TOP 1 ISNULL(FORMAT(D.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT, 
    --C.TLFullName AS KSV_NAME
    --FROM PL_REQUEST_PROCESS_CHILD A
    --LEFT JOIN PL_REQUEST_PROCESS B ON A.PROCESS_ID = B.ID
    --LEFT JOIN TL_USER C ON A.TLNAME = C.TLNANME
    --LEFT JOIN PL_PROCESS D ON B.REQ_ID = D.REQ_ID AND A.TLNAME = D.CHECKER_ID
    --WHERE B.REQ_ID = @p_REQ_ID
    --AND A.TYPE_JOB = 'KS'
    --AND A.STATUS_JOB = 'P'
    --ORDER BY D.ID DESC

---------Trưởng đơn vị DMMS--------
    --SELECT DISTINCT C.TLFullName AS DMMS_NAME
    --FROM PL_REQUEST_PROCESS A
    --LEFT JOIN PL_REQUEST_PROCESS_CHILD B ON A.ID = B.PROCESS_ID
    --LEFT JOIN TL_USER C ON B.TLNAME = C.TLNANME
    --WHERE A.REQ_ID = @p_REQ_ID
    --AND B.TYPE_JOB = 'TP'
    --AND A.PROCESS_ID = 'DMMS'
    --AND B.STATUS_JOB = 'P'
    --SELECT 
    --DISTINCT ISNULL(FORMAT(A.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT,
    --B.TLFullName AS DMMS_NAME
    --FROM PL_REQUEST_PROCESS A
    --LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
    --WHERE A.REQ_ID = @p_REQ_ID
    --AND A.PROCESS_ID = 'DMMS'
    --AND A.STATUS = 'P'
GO

update CM_EMPLOYEE_LOG SET POS_NAME = 'Phó Phòng Hành Chính' WHERE USER_DOMAIN = 'vanpt2'

INSERT INTO [CM_TRAN_TYPE] ([TRN_TYPE],[TRN_TYPE_NAME],[MODULE],[NOTES])VALUES
INSERT INTO [CM_TRAN_TYPE] ([TRN_TYPE],[TRN_TYPE_NAME],[MODULE],[NOTES])VALUES
INSERT INTO [CM_TRAN_TYPE] ([TRN_TYPE],[TRN_TYPE_NAME],[MODULE],[NOTES])VALUES
INSERT INTO [CM_TRAN_TYPE] ([TRN_TYPE],[TRN_TYPE_NAME],[MODULE],[NOTES])VALUES
INSERT INTO [CM_TRAN_TYPE] ([TRN_TYPE],[TRN_TYPE_NAME],[MODULE],[NOTES])VALUES
INSERT INTO [CM_TRAN_TYPE] ([TRN_TYPE],[TRN_TYPE_NAME],[MODULE],[NOTES])VALUES
INSERT INTO [CM_TRAN_TYPE] ([TRN_TYPE],[TRN_TYPE_NAME],[MODULE],[NOTES])VALUES
INSERT INTO [CM_TRAN_TYPE] ([TRN_TYPE],[TRN_TYPE_NAME],[MODULE],[NOTES])VALUES',N'HDNT',N'MSTT')
INSERT INTO [CM_TRAN_TYPE] ([TRN_TYPE],[TRN_TYPE_NAME],[MODULE],[NOTES])VALUES
INSERT INTO [CM_TRAN_TYPE] ([TRN_TYPE],[TRN_TYPE_NAME],[MODULE],[NOTES])VALUES',N'Y',N'CDTDQ')
INSERT INTO [CM_TRAN_TYPE] ([TRN_TYPE],[TRN_TYPE_NAME],[MODULE],[NOTES])VALUES',N'Y',N'MSTT_CDT')

DECLARE PR_B_ID VARCHAR(15), @PR_B_CODE VARCHAR(15),@PR_B_NAME VARCHAR(2000),@HH_ID VARCHAR(15),@FATHER_ID VARCHAR(15),@REFERENCE_PRICE DECIMAL(18,2),@UNIT_ID VARCHAR(15),
@P_MAKER_ID VARCHAR(15),@P_BRANCH_ID VARCHAR(15),@P_DEP_ID VARCHAR(15),@P_NOTES NVARCHAR(MAX),@P_IS_STATIONERY VARCHAR(1)
-- Khai báo cursor
DECLARE cur_Doc_DT CURSOR FOR
SELECT A.DESCRIPTION, A.HANGHOA_ID, A.HANGHOA_ID, A.PRICE,H.UNIT_ID,A.MAKER_ID,B.BRANCH_DVMS,DP.DEP_ID,A.NOTES + 'UAT - 30112024',0
FROM TR_REQUEST_DOC_DT A INNER JOIN TR_REQUEST_DOC B ON A.REQ_DOC_ID = B.REQ_ID
LEFT JOIN CM_HANGHOA H ON A.HANGHOA_ID = H.HH_ID
LEFT JOIN CM_BRANCH BR ON B.BRANCH_CREATE = BR.BRANCH_ID
LEFT JOIN CM_DEPARTMENT DP ON DP.DEP_ID = B.DEP_CREATE
WHERE B.PROCESS_ID ='APPROVE' AND ISNULL (A.DESCRIPTION,'') <> ''
OPEN cur_Doc_DT
FETCH NEXT FROM cur_Doc_DT INTO @PR_B_NAME, @HH_ID,@FATHER_ID,@REFERENCE_PRICE,@UNIT_ID,@P_MAKER_ID,@P_BRANCH_ID,@P_DEP_ID,@P_NOTES,@P_IS_STATIONERY
WHILE @FETCH_STATUS = 0
BEGIN
--IF)
EXEC CM_PRICE_BANK_Ins @PR_B_CODE,@PR_B_NAME,@HH_ID,@FATHER_ID,@REFERENCE_PRICE,@UNIT_ID,@P_MAKER_ID,@P_BRANCH_ID,@P_DEP_ID,@P_NOTES,@P_IS_STATIONERY,'1'
FETCH NEXT FROM cur_Doc_DT INTO @PR_B_NAME, @HH_ID,@FATHER_ID,@REFERENCE_PRICE,@UNIT_ID,@P_MAKER_ID,@P_BRANCH_ID,@P_DEP_ID,@P_NOTES,@P_IS_STATIONERY
END
CLOSE cur_Doc_DT
DEALLOCATE cur_Doc_DT
SELECT * FROM CM_PRICE_BANK

UPDATE CM_PRICE_BANK SET AUTH_STATUS ='A'

CREATE PROCEDURE dbo.TR_REQ_PAY_BUDGET_Import @p_XmlBudget XML =NULL,
@p_USER_LOGIN VARCHAR = NULL
AS
DECLARE @ERROR_MESSAGE NVARCHAR
DECLARE @Temp TABLE (
STT INT IDENTITY (1, 1) PRIMARY KEY
,GOOD_ID VARCHAR
,GOODS_CODE VARCHAR
,GOOD_NAME NVARCHAR
,BRANCH_ID VARCHAR
,BRANCH_CODE VARCHAR
,BRANCH_NAME NVARCHAR
,YEAR_RATE VARCHAR
,MONTH_RATE VARCHAR
,DISPLAY_NAME NVARCHAR
,AMT_EXE DECIMAL
,REASON NVARCHAR
)
IF OBJECT_ID('tempdb..#tempBudget') IS NULL
BEGIN
CREATE TABLE #tempBudget (
STT INT IDENTITY
,GOODS_CODE VARCHAR COLLATE SQL_Latin1_General_CP1_CI_AS
,BRANCH_CODE VARCHAR COLLATE SQL_Latin1_General_CP1_CI_AS
,YEAR_RATE VARCHAR COLLATE SQL_Latin1_General_CP1_CI_AS
,MONTH_RATE VARCHAR COLLATE SQL_Latin1_General_CP1_CI_AS
,DISPLAY_NAME NVARCHAR COLLATE SQL_Latin1_General_CP1_CI_AS
,AMT_EXE DECIMAL
,REASON NVARCHAR COLLATE SQL_Latin1_General_CP1_CI_AS
);
CREATE INDEX idx_stt ON #tempBudget (STT);
END
ELSE
BEGIN
SET @ERROR_MESSAGE = N'Hệ thống đang bận, vui lòng thao tác lại'
EXEC sp_seterrormessage @ERROR_MESSAGE
RETURN '-1'
END

DECLARE @hdoc INT
  EXEC sp_xml_preparedocument @hdoc OUTPUT
                             ,@p_XmlBudget
  INSERT INTO #tempBudget
    SELECT
    *
    FROM OPENXML(@hdoc, '/Root/XmlDataGood', 2)
    WITH (
    GOODS_CODE VARCHAR(50),
    BRANCH_CODE VARCHAR(50),
    YEAR_RATE VARCHAR(50),
    MONTH_RATE VARCHAR(50),
    DISPLAY_NAME NVARCHAR(250),
    AMT_EXE DECIMAL(18, 0),
    REASON NVARCHAR(MAX)
    )
  --VALIDATION
  IF (NOT EXISTS (SELECT 1 FROM #tempBudget))
  BEGIN
    SET @ERROR_MESSAGE = N'Không tìm thấy dữ liệu trong file import'
    EXEC sp_seterrormessage @ERROR_MESSAGE
    RETURN '-1'
  END
  -- KIỂM TRA MÃ NGÂN SÁCH
  IF (EXISTS (SELECT 1 FROM #tempBudget T WHERE NOT EXISTS (SELECT A.GD_CODE  FROM CM_GOODS A   WHERE A.GD_CODE = T.GOODS_CODE) AND (T.GOODS_CODE <> '' OR T.GOODS_CODE IS NOT NULL)))
  BEGIN
    SELECT  @ERROR_MESSAGE = STUFF((SELECT' &lt;/br&gt; ' + T.GOODS_CODE FROM #tempBudget T WHERE NOT EXISTS (SELECT 1 FROM CM_GOODS A  WHERE T.GOODS_CODE = A.GD_CODE) AND (T.GOODS_CODE <> '' OR T.GOODS_CODE IS NOT NULL)
    FOR XML PATH (''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
  END

IF (LEN(@ERROR_MESSAGE) > 0)
  BEGIN
    SET @ERROR_MESSAGE = N'Mã hạng mục/ngân sách không tồn tại trong hệ thống: ' + @ERROR_MESSAGE
    EXEC sp_seterrormessage @ERROR_MESSAGE
    RETURN '-1'

END
  --KIỂM TRA ĐƠN VỊ CHỊU PHÍ
  IF (EXISTS (SELECT 1 FROM #tempBudget T
  WHERE ((NOT EXISTS (SELECT A.BRANCH_CODE  FROM CM_BRANCH  A   WHERE A.BRANCH_CODE = T.BRANCH_CODE) AND EXISTS (SELECT B.DEP_CODE  FROM CM_DEPARTMENT  B   WHERE B.DEP_CODE = T.BRANCH_CODE))
          AND (NOT EXISTS (SELECT B.DEP_CODE  FROM CM_DEPARTMENT  B   WHERE B.DEP_CODE = T.BRANCH_CODE) ) AND EXISTS (SELECT A.BRANCH_CODE  FROM CM_BRANCH  A   WHERE A.BRANCH_CODE = T.BRANCH_CODE)) 
          AND (T.GOODS_CODE <> '' OR T.GOODS_CODE IS NOT NULL)))
  BEGIN
  SELECT  @ERROR_MESSAGE = STUFF((SELECT' &lt;/br&gt; ' + T.BRANCH_CODE FROM #tempBudget T 
                                  WHERE ((NOT EXISTS (SELECT A.BRANCH_CODE  FROM CM_BRANCH  A   WHERE A.BRANCH_CODE = T.BRANCH_CODE) AND EXISTS (SELECT B.DEP_CODE  FROM CM_DEPARTMENT  B   WHERE B.DEP_CODE = T.BRANCH_CODE))
                                  AND (NOT EXISTS (SELECT B.DEP_CODE  FROM CM_DEPARTMENT  B   WHERE B.DEP_CODE = T.BRANCH_CODE) ) AND EXISTS (SELECT A.BRANCH_CODE  FROM CM_BRANCH  A   WHERE A.BRANCH_CODE = T.BRANCH_CODE)) 
                                  AND (T.GOODS_CODE <> '' OR T.GOODS_CODE IS NOT NULL)
  FOR XML PATH (''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
  end
  IF (LEN(@ERROR_MESSAGE) > 0)
  BEGIN
    SET @ERROR_MESSAGE = N'Mã đơn vị chịu phí không tồn tại trong hệ thống: ' + @ERROR_MESSAGE
    EXEC sp_seterrormessage @ERROR_MESSAGE
    RETURN '-1'

END
  -- NẾU NHẬP THÁNG/ NĂM ĐỊNH MỨC KIỂM TRA MÃ NGÂN SÁCH ĐÓ TỒN TẠI HAY KHÔNG
  IF(EXISTS(SELECT 1 FROM #tempBudget T WHERE NOT EXISTS(SELECT 1 FROM TR_GOODSTYPE A LEFT JOIN CM_GOODS B ON A.GD_ID = B.GD_ID WHERE B.GD_CODE = T.GOODS_CODE)) AND EXISTS(SELECT 1 FROM #tempBudget A WHERE A.YEAR_RATE IS NOT NULL AND A.MONTH_RATE IS NOT NULL AND A.DISPLAY_NAME IS NOT NULL))
  SELECT  @ERROR_MESSAGE = STUFF((SELECT' </br> ' + T.GOODS_CODE FROM #tempBudget T 
                                  WHERE NOT EXISTS(SELECT 1 FROM TR_GOODSTYPE A LEFT JOIN CM_GOODS B ON A.GD_ID = B.GD_ID WHERE B.GD_CODE = T.GOODS_CODE)
  FOR XML PATH (''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

IF (LEN(@ERROR_MESSAGE) > 0)
  BEGIN
    SET @ERROR_MESSAGE = N'Mã ngân sách không nằm trong danh sách định mức chi phí tháng/ năm: ' + @ERROR_MESSAGE
    EXEC sp_seterrormessage @ERROR_MESSAGE

RETURN '-1'
  END
  -- KIỂM TRA MÃ NGÂN SÁCH ĐỊNH MỨC ĐÓ CÓ CỦA ĐƠN VỊ HAY KHÔNG
  IF(EXISTS(SELECT 1 FROM #tempBudget T 
            WHERE (NOT EXISTS(SELECT 1 FROM TR_REQ_BUDGET_MONTH_LIMIT A 
                                                            LEFT JOIN CM_GOODS B ON A.GD_ID = B.GD_ID 
                                                            LEFT JOIN CM_BRANCH BR ON BR.BRANCH_ID  = A.BRANCH_ID 
                                                            LEFT JOIN CM_DEPARTMENT DE ON A.DEP_ID = DE.DEP_ID
                                                            WHERE B.GD_CODE = T.GOODS_CODE AND (BR.BRANCH_CODE = T.BRANCH_CODE OR DE.DEP_CODE = T.BRANCH_CODE) )
                                                            AND EXISTS(SELECT 1 FROM TR_REQ_BUDGET_YEAR_LIMIT A 
                                                            LEFT JOIN CM_GOODS B ON A.GD_ID = B.GD_ID 
                                                            LEFT JOIN CM_BRANCH BR ON BR.BRANCH_ID  = A.BRANCH_ID 
                                                            LEFT JOIN CM_DEPARTMENT DE ON A.DEP_ID = DE.DEP_ID
                                                            WHERE B.GD_CODE = T.GOODS_CODE AND (BR.BRANCH_CODE = T.BRANCH_CODE OR DE.DEP_CODE = T.BRANCH_CODE) )
                                                            ) AND (
                                                            EXISTS(SELECT 1 FROM TR_REQ_BUDGET_MONTH_LIMIT A 
                                                            LEFT JOIN CM_GOODS B ON A.GD_ID = B.GD_ID 
                                                            LEFT JOIN CM_BRANCH BR ON BR.BRANCH_ID  = A.BRANCH_ID 
                                                            LEFT JOIN CM_DEPARTMENT DE ON A.DEP_ID = DE.DEP_ID
                                                            WHERE B.GD_CODE = T.GOODS_CODE AND (BR.BRANCH_CODE = T.BRANCH_CODE OR DE.DEP_CODE = T.BRANCH_CODE) )
                                                            AND NOT EXISTS(SELECT 1 FROM TR_REQ_BUDGET_YEAR_LIMIT  A 
                                                            LEFT JOIN CM_GOODS B ON A.GD_ID = B.GD_ID 
                                                            LEFT JOIN CM_BRANCH BR ON BR.BRANCH_ID  = A.BRANCH_ID 
                                                            LEFT JOIN CM_DEPARTMENT DE ON A.DEP_ID = DE.DEP_ID
                                                            WHERE B.GD_CODE = T.GOODS_CODE AND (BR.BRANCH_CODE = T.BRANCH_CODE OR DE.DEP_CODE = T.BRANCH_CODE) )
                                                            )))
  SELECT  @ERROR_MESSAGE = STUFF((SELECT' </br> ' + T.GOODS_CODE + N' không nằm trong định mức của ' + T.BRANCH_CODE FROM #tempBudget T 
                                  WHERE (NOT EXISTS(SELECT 1 FROM TR_REQ_BUDGET_MONTH_LIMIT A 
                                                            LEFT JOIN CM_GOODS B ON A.GD_ID = B.GD_ID 
                                                            LEFT JOIN CM_BRANCH BR ON BR.BRANCH_ID  = A.BRANCH_ID 
                                                            LEFT JOIN CM_DEPARTMENT DE ON A.DEP_ID = DE.DEP_ID
                                                            WHERE B.GD_CODE = T.GOODS_CODE AND (BR.BRANCH_CODE = T.BRANCH_CODE OR DE.DEP_CODE = T.BRANCH_CODE) )
                                                            AND EXISTS(SELECT 1 FROM TR_REQ_BUDGET_YEAR_LIMIT A 
                                                            LEFT JOIN CM_GOODS B ON A.GD_ID = B.GD_ID 
                                                            LEFT JOIN CM_BRANCH BR ON BR.BRANCH_ID  = A.BRANCH_ID 
                                                            LEFT JOIN CM_DEPARTMENT DE ON A.DEP_ID = DE.DEP_ID
                                                            WHERE B.GD_CODE = T.GOODS_CODE AND (BR.BRANCH_CODE = T.BRANCH_CODE OR DE.DEP_CODE = T.BRANCH_CODE) )
                                                            ) AND (
                                                            EXISTS(SELECT 1 FROM TR_REQ_BUDGET_MONTH_LIMIT A 
                                                            LEFT JOIN CM_GOODS B ON A.GD_ID = B.GD_ID 
                                                            LEFT JOIN CM_BRANCH BR ON BR.BRANCH_ID  = A.BRANCH_ID 
                                                            LEFT JOIN CM_DEPARTMENT DE ON A.DEP_ID = DE.DEP_ID
                                                            WHERE B.GD_CODE = T.GOODS_CODE AND (BR.BRANCH_CODE = T.BRANCH_CODE OR DE.DEP_CODE = T.BRANCH_CODE) )
                                                            AND NOT EXISTS(SELECT 1 FROM TR_REQ_BUDGET_YEAR_LIMIT  A 
                                                            LEFT JOIN CM_GOODS B ON A.GD_ID = B.GD_ID 
                                                            LEFT JOIN CM_BRANCH BR ON BR.BRANCH_ID  = A.BRANCH_ID 
                                                            LEFT JOIN CM_DEPARTMENT DE ON A.DEP_ID = DE.DEP_ID
                                                            WHERE B.GD_CODE = T.GOODS_CODE AND (BR.BRANCH_CODE = T.BRANCH_CODE OR DE.DEP_CODE = T.BRANCH_CODE) )
                                                            )
  FOR XML PATH (''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

IF (LEN(@ERROR_MESSAGE) > 0)
  BEGIN
    SET @ERROR_MESSAGE = N'Mã ngân sách không nằm trong danh sách định mức chi phí tháng/năm: ' + @ERROR_MESSAGE
    EXEC sp_seterrormessage @ERROR_MESSAGE

RETURN '-1'
  END
--    IF(EXISTS(SELECT 1 FROM #tempBudget T 
--            WHERE NOT EXISTS(SELECT 1 FROM TR_REQ_BUDGET_YEAR_LIMIT  A 
--                              LEFT JOIN CM_GOODS B ON A.GD_ID = B.GD_ID 
--                              LEFT JOIN CM_BRANCH BR ON BR.BRANCH_ID  = A.BRANCH_ID 
--                              LEFT JOIN CM_DEPARTMENT DE ON A.DEP_ID = DE.DEP_ID
--                              WHERE B.GD_CODE = T.GOODS_CODE AND (BR.BRANCH_CODE = T.BRANCH_CODE OR DE.DEP_CODE = T.BRANCH_CODE) )))
--  SELECT  @ERROR_MESSAGE = STUFF((SELECT' </br> ' + T.GOODS_CODE + N' không nằm trong định mức của ' + T.BRANCH_CODE FROM #tempBudget T 
--                                  WHERE NOT EXISTS(SELECT 1 FROM TR_REQ_BUDGET_YEAR_LIMIT A 
--                                                            LEFT JOIN CM_GOODS B ON A.GD_ID = B.GD_ID 
--                                                            LEFT JOIN CM_BRANCH BR ON BR.BRANCH_ID  = A.BRANCH_ID 
--                                                            LEFT JOIN CM_DEPARTMENT DE ON A.DEP_ID = DE.DEP_ID
--                                                            WHERE B.GD_CODE = T.GOODS_CODE AND (BR.BRANCH_CODE = T.BRANCH_CODE OR DE.DEP_CODE = T.BRANCH_CODE) )
--  FOR XML PATH (''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
--
--  IF (LEN(@ERROR_MESSAGE) > 0)
--  BEGIN
--    SET @ERROR_MESSAGE = N'Mã ngân sách không nằm trong danh sách định mức chi phí năm: ' + @ERROR_MESSAGE
--    EXEC sp_seterrormessage @ERROR_MESSAGE
--     
--    RETURN '-1'
--  END
  -- KIỂM TRA ĐÃ NHẬP SỐ TIỀN THỰC TẾ HAY CHƯA
  IF(EXISTS(SELECT 1 FROM #tempBudget T WHERE ISNULL(T.AMT_EXE,0) = 0 ))
  BEGIN
    SET  @ERROR_MESSAGE = N'Vui lòng nhập số tiền sử dụng thực tế!'
    EXEC sp_seterrormessage @ERROR_MESSAGE
    RETURN '-1' 
  END

SELECT DISTINCT DT.TRADE_ID,DT.GOODS_ID GD_ID,CG.GD_CODE GOODS_CODE,CG.GD_NAME GOODS_NAME,IIF(CB.BRANCH_ID IS NULL, CD.BRANCH_ID,CB.BRANCH_ID) BRANCH_ID,CD.DEP_ID,  IIF(CB.BRANCH_ID IS NULL, CD.BRANCH_ID,CB.BRANCH_ID) BRANCH_TAKE_COST_ID,CD.DEP_ID DEP_TAKE_COST_ID,CD.KHOI_ID KHOI_TAKE_COST_ID,
          ISNULL( DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0) - ISNULL( DT.AMT_TF,0) AS AMT_APP,
          ISNULL( DT.AMT_EXE,0) AS AMT_DO, 
      BG.REASON,
      BG.AMT_EXE,
      BG.YEAR_RATE,
      IIF(TGT.BUDGET_TYPE = 'nam','M' + CAST(MONTH(GETDATE()) AS VARCHAR(15)),'M'+BG.MONTH_RATE) MONTH_RATE,
      CASE 
          WHEN BG.DISPLAY_NAME LIKE N'%Chung%' THEN 'A'
          WHEN BG.DISPLAY_NAME LIKE N'%Doanh nghiệp%' THEN 'C'
          WHEN BG.DISPLAY_NAME LIKE N'%Cá nhân%' THEN 'Y'
          ELSE NULL
      END  AS BRANCH_KIND , 

ISNULL( DT.AMT_ETM,0)
        +
        (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
        FROM dbo.PL_REQUEST_DOC_DT DDT
        LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_ID = DOC.REQ_ID
        WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
        AND DDT.TRADE_ID = DT.TRADE_ID)
        +
        (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
        FROM dbo.PL_REQUEST_TRANSFER DDT
        LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID
        WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
        AND DDT.FR_TRADE_ID = DT.TRADE_ID)
        AS AMT_ETM,  
          ISNULL( DT.AMT_TF,0) AS AMT_TF,
          ISNULL( DT.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,  
          ISNULL(DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0)- ISNULL( DT.AMT_TF,0)- ISNULL(DT.AMT_EXE,0)
        
        (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
        FROM dbo.PL_REQUEST_TRANSFER DDT
        LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID
        WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
        AND DDT.FR_TRADE_ID = DT.TRADE_ID) AS AMT_REMAIN,
          ISNULL(DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0) ISNULL( DT.AMT_TF,0)-ISNULL(DT.AMT_ETM,0)
        -
        (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)

FROM dbo.PL_REQUEST_DOC_DT DDT
        LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_ID = DOC.REQ_ID
        WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
        AND DDT.TRADE_ID = DT.TRADE_ID)
        -
        (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
        FROM dbo.PL_REQUEST_TRANSFER DDT
        LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID
        WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
        AND DDT.FR_TRADE_ID = DT.TRADE_ID)
      AS AMT_REMAIN_ETM,
          CB.BRANCH_CODE,'I' AS REQDT_TYPE,  
          DT.NOTES,PT.PLAN_TYPE_CODE,PT.PLAN_TYPE_NAME,CM.DVDM_ID AS COST_ID,CM.DVDM_CODE AS COST_CODE,CM.DVDM_NAME AS COST_NAME,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_ID AS KHOI_ID,CK.DVDM_NAME AS KHOI_NAME,  
          DM.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,CD.DEP_CODE,CD.DEP_NAME,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID,
          PM.PLAN_TYPE_ID, CG.GD_TYPE_ID AS TYPE_NS,ISNULL(PM.YEAR,'') AS YEAR_RATE,
          --CB.BRANCH_NAME + CASE WHEN CD.DEP_NAME IS NOT NULL AND CB.BRANCH_TYPE = 'HS' THEN ' - ' + CD.DEP_NAME ELSE '' END AS BRANCH_NAME
      CASE 
          WHEN BG.BRANCH_CODE LIKE '069%' THEN (SELECT BR.BRANCH_NAME + ' - ' + CD.DEP_NAME FROM CM_BRANCH BR WHERE BR.BRANCH_ID = CD.BRANCH_ID)
        ELSE CB.BRANCH_NAME
      END BRANCH_NAME
      ,TGT.BUDGET_TYPE,0 LIMIT_CURRENT,0 LIMIT_USED
      FROM dbo.PL_MASTER PM  
          LEFT JOIN dbo.PL_TRADEDETAIL DT ON DT.PLAN_ID=PM.PLAN_ID  
          LEFT JOIN dbo.CM_PLAN_TYPE PT ON PT.PLAN_TYPE_ID=PM.PLAN_TYPE_ID  
          LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=DT.GOODS_ID  
      INNER JOIN #tempBudget BG ON CG.GD_CODE = BG.GOODS_CODE
          LEFT JOIN dbo.CM_GOODSTYPE GT ON CG.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'  
          LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_CODE=BG.BRANCH_CODE
          LEFT JOIN dbo.CM_DEPARTMENT CD ON CD.DEP_CODE=BG.BRANCH_CODE
          LEFT JOIN dbo.TR_GOODSTYPE TGT ON TGT.GD_ID = CG.GD_ID
      LEFT JOIN TR_REQ_BUDGET_MONTH_LIMIT MONTH ON CG.GD_ID = MONTH.GD_ID
      LEFT JOIN TR_REQ_BUDGET_YEAR_LIMIT YEAR ON CG.GD_ID = YEAR.GD_ID

LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=PM.COST_ID  
          LEFT JOIN dbo.CM_DVDM CM ON CM.DVDM_ID=DM.DVDM_ID  
          LEFT JOIN dbo.CM_DVDM_KHOI DMKH ON DMKH.DVDM_ID = DM.DVDM_ID
          LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=DMKH.KHOI_ID  
      WHERE PM.YEAR = BG.YEAR_RATE
      AND (EXISTS(SELECT 1 FROM #tempBudget T INNER JOIN CM_BRANCH BR ON BR.BRANCH_CODE = T.BRANCH_CODE WHERE BR.BRANCH_ID = PM.BRANCH_ID AND BR.BRANCH_ID <> 'DV0001')
          OR (EXISTS(SELECT 1 FROM #tempBudget TMP INNER JOIN CM_DEPARTMENT DE ON DE.DEP_CODE = TMP.BRANCH_CODE WHERE DE.DEP_ID = PM.DEPT_ID AND DE.BRANCH_ID = 'DV0001')))
      AND PM.PLAN_ID NOT LIKE 'PLMII%'
GO