ALTER PROCEDURE [dbo].[PL_COSTCENTER_Ins]
@p_COST_CODE	varchar(15)  = NULL,
@p_COST_NAME	NVARCHAR(300)  = NULL,
@p_PLAN_TYPE_ID	varchar(15)  = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(20)  = NULL,
@p_CREATE_DT	varchar(30) = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(20)  = NULL,
@p_APPROVE_DT	varchar(30) = NULL,--
@p_XMLData XML=NULL
AS
BEGIN TRANSACTION
--Validation is here
--DECLARE @ERRORSYS NVARCHAR(15) = '' 
--  IF ( EXISTS ( SELECT * FROM dbo.PL_COSTCENTER WHERE COST_CODE=@p_COST_CODE))
--	 SET @ERRORSYS = 'COST-0001'
IF ( EXISTS ( SELECT * FROM dbo.PL_COSTCENTER WHERE COST_CODE=@p_COST_CODE))
BEGIN
	ROLLBACK TRANSACTION
	SELECT '-1' Result, ''  COST_ID, N'Mã đơn vị chuyên môn đã tồn tại' ErrorDesc
	RETURN '-1'
END 
DECLARE @DVDM_ID VARCHAR(20), @KHOI_ID VARCHAR(15)
SET @DVDM_ID = (SELECT TOP(1) cd.DVDM_ID FROM CM_DVDM cd WHERE cd.DVDM_CODE =@p_COST_CODE AND cd.IS_DVDM=1)
SET @KHOI_ID = (SELECT TOP(1) cd.DVDM_ID FROM CM_DVDM cd WHERE LEFT(cd.DVDM_CODE,5) =LEFT(@p_COST_CODE,5) AND cd.IS_KHOI=1)
IF (@DVDM_ID IS NULL)
    BEGIN
        EXEC SYS_CodeMasters_Gen 'CM_DVDM', @DVDM_ID OUT
        IF @DVDM_ID='' OR @DVDM_ID IS NULL GOTO ABORT
        INSERT INTO CM_DVDM (DVDM_ID, DVDM_CODE, DVDM_NAME, NOTES, RECORD_STATUS, AUTH_STATUS, MAKER_ID, CREATE_DT, EDITER_ID, EDIT_DT, CHECKER_ID, APPROVE_DT, IS_DVDM, IS_KHOI, IS_DVCM, IS_PTGD, IS_GDK)
        VALUES (@DVDM_ID, @p_COST_CODE, @p_COST_NAME, NULL, 1, 'A', @p_MAKER_ID, GETDATE(), '', GETDATE(), @p_MAKER_ID, GETDATE(), 1, 0, 0, 0, 0);
    END
		DECLARE @l_COST_ID VARCHAR(15)
		--- THEM MAPPING DVCM
		EXEC SYS_CodeMasters_Gen 'PL_COSTCENTER', @l_COST_ID out
		IF @l_COST_ID='' OR @l_COST_ID IS NULL GOTO ABORT
		INSERT INTO dbo.PL_COSTCENTER
		(
		    COST_ID,
		    COST_CODE,
		    PLAN_TYPE_ID,
		    COST_NAME,
		    NOTES,
		    RECORD_STATUS,
		    AUTH_STATUS,
		    MAKER_ID,
		    CREATE_DT,
		    EDITER_ID,
		    EDIT_DT,
		    CHECKER_ID,
		    APPROVE_DT,
            DVDM_ID
		)
		VALUES
		(	@l_COST_ID,
			@p_COST_CODE,        -- COST_CODE - varchar(20)
		    @p_PLAN_TYPE_ID,        -- PLAN_TYPE_CODE - varchar(20)
		    @p_COST_NAME,        -- COST_NAME - varchar(200)
		    @p_NOTES,       -- NOTES - nvarchar(1000)
		    @p_RECORD_STATUS,        -- RECORD_STATUS - varchar(1)
		    @p_AUTH_STATUS,        -- AUTH_STATUS - varchar(1)
		    @p_MAKER_ID,        -- MAKER_ID - varchar(15)
		    Convert(Datetime,@p_CREATE_DT,103), -- CREATE_DT - datetime
		    '',        -- EDITER_ID - varchar(15)
		    NULL, -- EDIT_DT - datetime
		    @p_CHECKER_ID,        -- CHECKER_ID - varchar(15)
		    Convert(Datetime,@p_APPROVE_DT,103),  -- APPROVE_DT - datetime
            (SELECT TOP(1) cd.DVDM_ID FROM CM_DVDM cd WHERE cd.DVDM_CODE =@p_COST_CODE AND cd.IS_DVDM=1)
		)
		--- THEM MAPPING KHOI
		DECLARE @l_COST_ID_KHOI VARCHAR(15)
		EXEC SYS_CodeMasters_Gen 'PL_COSTCENTER', @l_COST_ID_KHOI out
		IF @l_COST_ID_KHOI='' OR @l_COST_ID_KHOI IS NULL GOTO ABORT
		INSERT INTO dbo.PL_COSTCENTER
		(
		    COST_ID,
		    COST_CODE,
		    PLAN_TYPE_ID,
		    COST_NAME,
		    NOTES,
		    RECORD_STATUS,
		    AUTH_STATUS,
		    MAKER_ID,
		    CREATE_DT,
		    EDITER_ID,
		    EDIT_DT,
		    CHECKER_ID,
		    APPROVE_DT,
            DVDM_ID
		)
		VALUES
		(	@l_COST_ID_KHOI,
			@p_COST_CODE,        -- COST_CODE - varchar(20)
		    @p_PLAN_TYPE_ID,        -- PLAN_TYPE_CODE - varchar(20)
		    @p_COST_NAME,        -- COST_NAME - varchar(200)
		    @p_NOTES,       -- NOTES - nvarchar(1000)
		    @p_RECORD_STATUS,        -- RECORD_STATUS - varchar(1)
		    @p_AUTH_STATUS,        -- AUTH_STATUS - varchar(1)
		    @p_MAKER_ID,        -- MAKER_ID - varchar(15)
		    Convert(Datetime,@p_CREATE_DT,103), -- CREATE_DT - datetime
		    '',        -- EDITER_ID - varchar(15)
		    NULL, -- EDIT_DT - datetime
		    @p_CHECKER_ID,        -- CHECKER_ID - varchar(15)
		    Convert(Datetime,@p_APPROVE_DT,103),  -- APPROVE_DT - datetime
           @KHOI_ID
		)
	DECLARE @l_COSTDT_ID VARCHAR(20), @d_COSTDT_ID VARCHAR(20)
	Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output, @p_XMLData	

	DECLARE CostDT CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/COSTDT',2)
	WITH 
	(
		BRANCH_ID VARCHAR(15),
		DEP_ID	varchar(15),
		NOTES NVARCHAR(1000)
	)
	
	OPEN CostDT


	DECLARE @BRANCH_ID VARCHAR(15),@DEP_ID	varchar(15),@NOTES NVARCHAR(1000)


	FETCH NEXT FROM CostDT INTO @BRANCH_ID, @DEP_ID,@NOTES
	
	WHILE @@FETCH_STATUS = 0	
	BEGIN			
		
		
		EXEC SYS_CodeMasters_Gen 'PL_COSTCENTER_DT', @l_COSTDT_ID out
		IF @l_COSTDT_ID='' OR @l_COSTDT_ID IS NULL GOTO ABORT		

			INSERT INTO dbo.PL_COSTCENTER_DT
			(
			    COSTDT_ID,
			    COST_ID,
			    BRANCH_ID,
			    DEP_ID,
			    NOTES,
			    RECORD_STATUS,
			    MAKER_ID,
			    CREATE_DT
			  
			)
			VALUES
			(   @l_COSTDT_ID,        -- COSTDT_ID - varchar(20)
			    @l_COST_ID,        -- COST_ID - varchar(20)
			    @BRANCH_ID,        -- BRANCH_ID - varchar(15)
			    @DEP_ID,        -- DEP_ID - varchar(15)
			    @NOTES,       -- NOTES - nvarchar(1000)
			    '1',        -- RECORD_STATUS - varchar(1)
			    @p_MAKER_ID,        -- MAKER_ID - varchar(15)
			    Convert(Datetime,@p_CREATE_DT,103) -- CREATE_DT - datetime
		
			    )
			--- MAPPING CHO KHOI
			EXEC SYS_CodeMasters_Gen 'PL_COSTCENTER_DT', @d_COSTDT_ID out
			IF @d_COSTDT_ID='' OR @d_COSTDT_ID IS NULL GOTO ABORT		

			INSERT INTO dbo.PL_COSTCENTER_DT
			(
			    COSTDT_ID,
			    COST_ID,
			    BRANCH_ID,
			    DEP_ID,
			    NOTES,
			    RECORD_STATUS,
			    MAKER_ID,
			    CREATE_DT
			  
			)
			VALUES
			(   @d_COSTDT_ID,        -- COSTDT_ID - varchar(20)
			    @l_COST_ID_KHOI,        -- COST_ID - varchar(20)
			    @BRANCH_ID,        -- BRANCH_ID - varchar(15)
			    @DEP_ID,        -- DEP_ID - varchar(15)
			    @NOTES,       -- NOTES - nvarchar(1000)
			    '1',        -- RECORD_STATUS - varchar(1)
			    @p_MAKER_ID,        -- MAKER_ID - varchar(15)
			    Convert(Datetime,@p_CREATE_DT,103) -- CREATE_DT - datetime
		
			    )
				----
		
		IF @@Error <> 0 GOTO ABORT	
	
		FETCH NEXT FROM CostDT INTO @BRANCH_ID, @DEP_ID,@NOTES	
		PRINT @l_COSTDT_ID
	END
	CLOSE CostDT
	DEALLOCATE CostDT
		IF @@Error <> 0 GOTO ABORT
COMMIT TRANSACTION
SELECT '0' as Result, @l_COST_ID  COST_ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' COST_ID, '' ErrorDesc
		RETURN '-1'
End