AMS - VB

ALTER PROCEDURE dbo.MW_TRANSFER_PRIVATE_Ins

    @p_TRANFER_PRIVATE_CODE VARCHAR(15) = NULL,

    @p_EMP_NAME NVARCHAR(200) = NULL,

    @p_FROM_DATE VARCHAR(20) = NULL,

    @p_BRANCH_NAME NVARCHAR(200) = NULL,

    @p_BRANCH_ID VARCHAR(15) = NULL,

    @p_DEP_ID VARCHAR(15) = NULL,

    @p_NOTES  NVARCHAR(1000) = NULL,

    @p_WARE_ID VARCHAR(15) = NULL,

    @p_MAKER_ID VARCHAR(100) = NULL,

    @p_CHECKER_ID VARCHAR(100) = NULL,

    @p_AUTH_STATUS VARCHAR(1) = NULL,

    @p_APPROVE_DT VARCHAR(20) = NULL,

    @p_RECORD_STATUS VARCHAR(1) = NULL,

    @p_CREATE_DT varchar(20) = NULL,

    @p_XmlData XML = NULL

AS

IF (EXISTS ( SELECT * FROM MW_TRANFER_PRIVATE cd WHERE cd.TRANFER_PRIVATE_CODE = @p_TRANFER_PRIVATE_CODE ))

	BEGIN

		SELECT '-1' Result, ''  MW_TRANFER_PRIVATE_CODE, N'Mã điều chuyển kho nội đã tồn tại' ErrorDesc

		RETURN '-1'

	END

DECLARE 

@TRANFER_PRIVATE_ID VARchar(15),

@MATERIAL_ID	VARCHAR(15),

@MATERIAL_CODE	VARCHAR(15),

@MATERIAL_NAME NVARCHAR(200),

@STOCK_QUANTITY DECIMAL(18,2),

@DEFAULT_WARE_ID VARCHAR(15),

@DEFAULT_WARE_NAME NVARCHAR(200),

@DEFAULT_PROMOTION_ID VARCHAR(15),

@DEFAULT_PROMOTION_NAME NVARCHAR(200),

@TRANFER_QUANTITY DECIMAL(18,2),

@REMAINING_QUANTITY DECIMAL(18,2),

@WARE_TRANFER_ID VARCHAR(15),

@WARE_TRANFER_NAME NVARCHAR(200),

@PROMOTION_TRANFER_ID VARCHAR(15),

@PROMOTION_TRANFER_NAME NVARCHAR(200),

@MAST_BAL_ID VARCHAR(15)

Declare @hdoc INT

Exec sp_xml_preparedocument @hdoc Output,@p_XmlData

DECLARE XmlData CURSOR FOR

SELECT *

FROM OPENXML(@hdoc,'/Root/XmlData',2)

WITH 

(

TRANFER_PRIVATE_ID VARCHAR(15),

MATERIAL_ID	VARCHAR(15),

MATERIAL_CODE	VARCHAR(15),

MATERIAL_NAME_NHOM NVARCHAR(200),

STOCK_QUANTITY DECIMAL(18,2),

DEFAULT_WARE_ID VARCHAR(15),

DEFAULT_WARE_NAME NVARCHAR(200),

DEFAULT_PROMOTION_ID VARCHAR(15),

DEFAULT_PROMOTION_NAME NVARCHAR(200),

TRANFER_QUANTITY DECIMAL(18,2),

REMAINING_QUANTITY DECIMAL(18,2),

WARE_TRANFER_ID VARCHAR(15),

WARE_TRANFER_NAME NVARCHAR(200),

PROMOTION_TRANFER_ID VARCHAR(15),

PROMOTION_TRANFER_NAME NVARCHAR(200),

MAST_BAL_ID VARCHAR(15)

)

OPEN XmlData

BEGIN TRANSACTION

DECLARE @l_TRANFER_PRIVATE_ID VARCHAR(15),@COUNT INT;

 SET @COUNT = 0;

		EXEC SYS_CodeMasters_Gen 'MW_TRANFER_PRIVATE', @l_TRANFER_PRIVATE_ID out

		IF @l_TRANFER_PRIVATE_ID='' OR @l_TRANFER_PRIVATE_ID IS NULL GOTO ABORT

		INSERT INTO MW_TRANFER_PRIVATE(TRANFER_PRIVATE_ID,TRANFER_PRIVATE_CODE,WARE_ID,PROCESS_ID,EMP_NAME,FROM_DATE,BRANCH_NAME,BRANCH_ID,NOTES,[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],DEP_ID)

		VALUES(@l_TRANFER_PRIVATE_ID,@l_TRANFER_PRIVATE_ID,@p_WARE_ID,'ADDNEW',@p_EMP_NAME,CONVERT(DATETIME, @p_FROM_DATE, 103),@p_BRANCH_NAME,@p_BRANCH_ID,@p_NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,'E' ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103),@p_DEP_ID)

    FETCH NEXT FROM XmlData INTO 

                                  @TRANFER_PRIVATE_ID,

                                  @MATERIAL_ID	,

                                  @MATERIAL_CODE,

                                  @MATERIAL_NAME,

                                  @STOCK_QUANTITY ,

                                  @DEFAULT_WARE_ID ,

                                  @DEFAULT_WARE_NAME ,

                                  @DEFAULT_PROMOTION_ID ,

                                  @DEFAULT_PROMOTION_NAME ,

                                  @TRANFER_QUANTITY ,

                                  @REMAINING_QUANTITY,

                                  @WARE_TRANFER_ID, 

                                  @WARE_TRANFER_NAME ,

                                  @PROMOTION_TRANFER_ID ,

                                  @PROMOTION_TRANFER_NAME,

                                  @MAST_BAL_ID

    WHILE @@FETCH_STATUS = 0

    BEGIN

      DECLARE @l_TRANFER_PRIVATE_DT_ID VARCHAR(15)

        SET @COUNT = @COUNT + 1;

--        IF (SELECT TRIM(CW.ACC_ACCOUNTING) FROM CM_WARE CW WHERE CW.WARE_ID = @p_WARE_ID) <> (SELECT TRIM(CW2.ACC_ACCOUNTING) FROM CM_WARE CW2 WHERE CW2.WARE_ID = @WARE_TRANFER_ID)

--        BEGIN

--          CLOSE XmlData;

--          DEALLOCATE XmlData;

--          ROLLBACK TRANSACTION;

--          SELECT '-1' Result, ''  MW_TRANFER_PRIVATE_CODE, (N'DANH SÁCH ĐƠN VỊ NHẬN ĐIỀU CHUYỂN ở dòng' + ' ' + convert(nvarchar(255), @COUNT) + ': ' + N'Không thể điều chuyển nội bộ khác kho') ErrorDesc

--          RETURN '-1'

--        END

          IF ((@TRANFER_QUANTITY > @STOCK_QUANTITY))

        	BEGIN

        		GOTO ABORT2

        	END

          IF(@TRANFER_QUANTITY = 0 OR @TRANFER_QUANTITY IS NULL)

          BEGIN

            GOTO ABORT3

          END

      SET @p_AUTH_STATUS = 'E'

  		EXEC SYS_CodeMasters_Gen 'MW_TRANFER_PRIVATE_DT', @l_TRANFER_PRIVATE_DT_ID out

  		IF @l_TRANFER_PRIVATE_DT_ID='' OR @l_TRANFER_PRIVATE_DT_ID IS NULL GOTO ABORT

      INSERT INTO MW_TRANFER_PRIVATE_DT (TRANFER_PRIVATE_DT_ID,TRANFER_PRIVATE_ID,MATERIAL_ID,MAST_BAL_ID,MATERIAL_CODE,MATERIAL_NAME,STOCK_QUANTITY,DEFAULT_WARE_ID, 

      DEFAULT_WARE_NAME,DEFAULT_PROMOTION_ID,DEFAULT_PROMOTION_NAME,TRANFER_QUANTITY,REMAINING_QUANTITY,WARE_TRANFER_ID,WARE_TRANFER_NAME,PROMOTION_TRANFER_NAME,PROMOTION_TRANFER_ID,

      [RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT])

      VALUES (@l_TRANFER_PRIVATE_DT_ID,@l_TRANFER_PRIVATE_ID,@MATERIAL_ID,@MAST_BAL_ID,@MATERIAL_CODE,@MATERIAL_NAME,@STOCK_QUANTITY

      ,@DEFAULT_WARE_ID,@DEFAULT_WARE_NAME,@DEFAULT_PROMOTION_ID,@DEFAULT_PROMOTION_NAME, @TRANFER_QUANTITY,@REMAINING_QUANTITY, @WARE_TRANFER_ID,@WARE_TRANFER_NAME,@PROMOTION_TRANFER_NAME,@PROMOTION_TRANFER_ID

      ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,'E' ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103));

    FETCH NEXT FROM XmlData INTO 

                                  @TRANFER_PRIVATE_ID,

                                  @MATERIAL_ID	,

                                  @MATERIAL_CODE,

                                  @MATERIAL_NAME,

                                  @STOCK_QUANTITY ,

                                  @DEFAULT_WARE_ID ,

                                  @DEFAULT_WARE_NAME ,

                                  @DEFAULT_PROMOTION_ID ,

                                  @DEFAULT_PROMOTION_NAME ,

                                  @TRANFER_QUANTITY ,

                                  @REMAINING_QUANTITY,

                                  @WARE_TRANFER_ID, 

                                  @WARE_TRANFER_NAME ,

                                  @PROMOTION_TRANFER_ID ,

                                  @PROMOTION_TRANFER_NAME,

                                  @MAST_BAL_ID 

    END

    CLOSE XmlData

    DEALLOCATE XmlData

-- Tạo lịch sử xử lý

INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,ROLE_USER,[STATUS],BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, CHECKER_ID,RECEPTION_DT,NOTES)

VALUES(@l_TRANFER_PRIVATE_ID,'ADDNEW',N'NHÂN VIÊN TẠO PHIẾU','NVTT','C',@p_BRANCH_ID,'',@p_DEP_ID, @p_MAKER_ID,GETDATE(),N'Nhân viên tạo phiếu yêu cầu')

    IF @@Error <> 0 GOTO ABORT

COMMIT TRANSACTION;

SELECT '0' AS Result,

       @l_TRANFER_PRIVATE_ID TRANSFER_PRIVATE_ID,

	    @l_TRANFER_PRIVATE_ID TRANSFER_PRIVATE_CODE,

       '' ErrorDesc;

RETURN '0';

ABORT:

BEGIN

    CLOSE XmlData;

    DEALLOCATE XmlData;

    ROLLBACK TRANSACTION;

    SELECT '-1' AS Result,

           '' TRANSFER_PRIVATE_ID,

           '' TRANSFER_RPIVATE_CODE,

           '' ErrorDesc;

    RETURN '-1';

END;

ABORT2:

BEGIN

      CLOSE XmlData;

      DEALLOCATE XmlData;

      ROLLBACK TRANSACTION;

      SELECT '-1' Result, ''  MW_TRANFER_PRIVATE_CODE, (N'Số lượng điều chuyển vượt quá số lượng tồn kho ở dòng' + ' ' + convert(nvarchar(255), @COUNT) + ' ' + N'Trong phần chi tiết lô vật liệu') ErrorDesc

      RETURN '-1'

END

ABORT3:

BEGIN

      CLOSE XmlData;

      DEALLOCATE XmlData;

      ROLLBACK TRANSACTION;

      SELECT '-1' Result, ''  MW_TRANFER_PRIVATE_CODE, (N'Số lượng điều chuyển chưa được nhập hoặc bằng 0 ở dòng' + ' ' + convert(nvarchar(255), @COUNT) + ' ' + N'Trong phần chi tiết lô vật liệu') ErrorDesc

      RETURN '-1'

END