DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

ALTER PROCEDURE [dbo].[TR_REQ_DOC_Ins_To_PO] @p_TR_REQ_ID VARCHAR(15)

AS

BEGIN TRANSACTION;

	DECLARE @l_SUP_ID VARCHAR(15)

	DECLARE @p_PO_CODE VARCHAR(15),

        @p_BRANCH_ID VARCHAR(20),

		@p_SUP_ID VARCHAR(20),

		@p_TOTAL_AMT DECIMAL(18,2),

		@p_MAKER_ID VARCHAR(20),

		@p_TR_REQ_CODE VARCHAR(20),

		@p_SUP_NAME NVARCHAR(200),

		@p_SUP_ADDR NVARCHAR(200),

		@p_BRANCH_RE VARCHAR(20),

        @p_PO_NAME NVARCHAR(100);

--- LAY DANH SACH NHA CUNG CAP CUA HANG HOA TRONG PYCMS - LUCTV 25052020

	DECLARE @TABLE_NCC TABLE (SUP_ID VARCHAR(15))

	INSERT INTO @TABLE_NCC SELECT SUP_ID FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_TR_REQ_ID GROUP BY SUP_ID

	DECLARE lstDATANCC CURSOR FOR SELECT SUP_ID FROM @TABLE_NCC

	OPEN lstDATANCC

	FETCH NEXT FROM lstDATANCC INTO @l_SUP_ID

	WHILE @@FETCH_STATUS=0

	BEGIN

	--SET @l_SUP_ID =(SELECT TOP 1 SUP_ID FROM @TABLE_NCC)

	--insert master		

	DECLARE @l_PO_ID VARCHAR(15);

	EXEC SYS_CodeMasters_Gen 'TR_PO_MASTER', @l_PO_ID OUT;

	IF @l_PO_ID = '' OR @l_PO_ID IS NULL

	GOTO ABORT;

	SELECT @p_BRANCH_ID=BRANCH_DVMS,@p_BRANCH_RE=BRANCH_CREATE,@p_TR_REQ_CODE=REQ_CODE,@p_PO_NAME=REQ_REASON,@p_MAKER_ID=USER_DVMS FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_TR_REQ_ID

	--SELECT TOP 1 @p_SUP_ID = SUP_ID FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID

	SET @p_SUP_ID =@l_SUP_ID

	SET @p_TOTAL_AMT= (SELECT SUM(TOTAL_AMT) FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID AND  ISNULL(SUP_ID,'') =ISNULL(@p_SUP_ID,''))

	--TU PHAT SINH SO PO

	DECLARE @ldate INT = (SELECT YEAR(GETDATE()) );

	EXEC [dbo].[TR_PO_MASTER_NO_Gen] @p_BRANCH_ID, @ldate, @p_PO_CODE OUT;

	IF @p_PO_CODE = ''

	   OR @p_PO_CODE IS NULL

		GOTO ABORT;

	INSERT INTO TR_PO_MASTER

	(

		[PO_TYPE],

		[PO_ID],

		[PO_CODE],

		[PO_NAME],

		[CONTRACT_ID],

		[SUP_ID],

		[SUP_NAME],

		[SUP_ADDR],

		[INPUT_DT],

		[PAYMENT_DT],

		[TOTAL_AMT],

		[REQ_DOC_ID],

		[DELIVERY_DT],

		[PAYAPP_DT],

		[NOTES],

		[RECORD_STATUS],

		[MAKER_ID],

		[CREATE_DT],

		[AUTH_STATUS],

		[CHECKER_ID],

		[APPROVE_DT],

		BRANCH_ID

	)

	VALUES

	(1, @l_PO_ID, @p_PO_CODE, @p_PO_NAME, '', @p_SUP_ID, @p_SUP_NAME, @p_SUP_ADDR, GETDATE(),

	 NULL, @p_TOTAL_AMT, @p_TR_REQ_ID, NULL,

	 NULL, '', '1', @p_MAKER_ID, GETDATE(),

	 'E', NULL, NULL, @p_BRANCH_ID);

	IF @@Error <> 0

		GOTO ABORT;

	PRINT 'INSERT MASTER SUCCESS';

	INSERT INTO dbo.TR_PO_MASTER_TEMP

	(

		PO_ID,

		TR_REQ_ID,

		TR_REQ_CODE

	)

	VALUES

	(   @l_PO_ID,      -- PO_ID - varchar(15)

		@p_TR_REQ_ID,  -- TR_REQ_ID - varchar(20)

		@p_TR_REQ_CODE -- TR_REQ_CODE - varchar(20)

	 );

	 DECLARE @HANGHOA_ID VARCHAR(20),@QUANTITY DECIMAL(18,0),@PRICE DECIMAL(18,2),@TOTAL_AMT DECIMAL(18,2),@HH_TYPE VARCHAR(20),@HH_NAME NVARCHAR(100),

	 @CURRENCY VARCHAR(15),@RATE INT ,@TOTAL_AMT_FN DECIMAL(18,2), @HH_DESC NVARCHAR(500)

	 DECLARE lstData CURSOR FOR

	 SELECT HANGHOA_ID,QUANTITY,PRICE,TOTAL_AMT,CURRENCY,EXCHANGE_RATE,TOTAL_AMT_ETM,[DESCRIPTION] FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID AND ISNULL(SUP_ID,'') =ISNULL(@p_SUP_ID,'')

	OPEN lstData

	FETCH NEXT FROM lstData INTO @HANGHOA_ID,@QUANTITY,@PRICE,@TOTAL_AMT,@CURRENCY,@RATE ,@TOTAL_AMT_FN ,@HH_DESC

	WHILE @@FETCH_STATUS = 0

	BEGIN

	SELECT @HH_NAME= HH_NAME,@HH_TYPE=HH_TYPE_ID FROM dbo.CM_HANGHOA WHERE HH_ID=@HANGHOA_ID

	DECLARE @l_PD_ID VARCHAR(15)

			EXEC SYS_CodeMasters_Gen 'TR_PO_DETAIL', @l_PD_ID out

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

	 INSERT INTO dbo.TR_PO_DETAIL

	 (

		 PD_ID,

		 PO_ID,

		 PLAN_ID,

		 TRADE_ID,

		 GOODS_ID,

		 DESCRIPTION,

		 UNIT_ID,

		 QUANTITY,

		 PRICE,

		 TOTAL_AMT,

		 IS_DELIVERY,

		 DELIVERY_DT,

		 PAYMENT_STATUS,

		 AMOUNT_PAID,

		 PAID_DT,

		 INVOICENO,

		 NOTES,

		 RECEIVE_BRANCH,

		 RECEIVE_ADDR,

		 RECEIVE_PERSON,

		 RECEIVE_TEL,

		 RECORD_STATUS,

		 MAKER_ID,

		 CREATE_DT,

		 AUTH_STATUS,

		 CHECKER_ID,

		 APPROVE_DT,

		 EXP_DELIVERY_DT,

		 GOODS_NAME,

		 INVOICE_DT,

		 GOODSTYPE_REAL,

		 VAT,

		 PRICE_VAT,

		 CONTRACT_DT,CURRENCY,RATE,TOTAL_AMT_FN

	 )

	 VALUES

	 (   @l_PD_ID,        -- PD_ID - varchar(15)

		 @l_PO_ID,        -- PO_ID - varchar(15)

		 '',        -- PLAN_ID - varchar(15)

		 '',        -- TRADE_ID - varchar(15)

		 @HANGHOA_ID,        -- GOODS_ID - varchar(15)

		 @HH_DESC,       -- DESCRIPTION - nvarchar(500)

		 '',        -- UNIT_ID - varchar(15)

		 @QUANTITY,      -- QUANTITY - decimal(18, 0)

		 @PRICE,      -- PRICE - decimal(18, 0)

		 @QUANTITY*@PRICE,      -- TOTAL_AMT - decimal(18, 0)6

		 '',        -- IS_DELIVERY - varchar(1)

		 NULL, -- DELIVERY_DT - datetime

		 'CTT',        -- PAYMENT_STATUS - varchar(4)

		 NULL,      -- AMOUNT_PAID - decimal(18, 0)

		 NULL, -- PAID_DT - datetime

		 '',        -- INVOICENO - varchar(1000)

		 @HH_DESC,       -- NOTES - nvarchar(1000)

		 @p_BRANCH_RE,        -- RECEIVE_BRANCH - varchar(15)

		 N'',       -- RECEIVE_ADDR - nvarchar(1000)

		 N'',       -- RECEIVE_PERSON - nvarchar(500)

		 '',        -- RECEIVE_TEL - varchar(100)

		 '',        -- RECORD_STATUS - varchar(1)

		 @p_MAKER_ID,        -- MAKER_ID - varchar(15)

		 GETDATE(), -- CREATE_DT - datetime

		 '',        -- AUTH_STATUS - varchar(50)

		 '',        -- CHECKER_ID - varchar(15)

		 NULL, -- APPROVE_DT - datetime

		 NULL, -- EXP_DELIVERY_DT - datetime

		 @HH_NAME,       -- GOODS_NAME - nvarchar(500)

		 NULL, -- INVOICE_DT - datetime

		 @HH_TYPE,        -- GOODSTYPE_REAL - varchar(15)

		 0,      -- VAT - decimal(18, 2)

		--@TOTAL_AMT -   (ROUND(@PRICE/1.1,0) *@QUANTITY),      -- PRICE_VAT - decimal(18, 0)

		 --@PRICE -ROUND(@PRICE/1.1,0),

		 0.0,

		 '' ,@CURRENCY,@RATE,@TOTAL_AMT_FN       -- CONTRACT_DT - varchar(15)

		 )

	   FETCH NEXT FROM lstData INTO @HANGHOA_ID,@QUANTITY,@PRICE,@TOTAL_AMT,@CURRENCY,@RATE ,@TOTAL_AMT_FN,@HH_DESC

	END

	 CLOSE lstData;

    DEALLOCATE lstData;

	SET @TOTAL_AMT=(SELECT SUM(TOTAL_AMT) AS TOTAL_AMT FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID AND ISNULL(SUP_ID,'') =ISNULL(@p_SUP_ID,''))

	DECLARE @l_PAY_ID VARCHAR(15)

	EXEC SYS_CodeMasters_Gen 'TR_PO_PAYMENT', @l_PAY_ID out

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

	INSERT INTO dbo.TR_PO_PAYMENT

	(

	    PAY_ID,

	    PO_ID,

	    PAY_PHASE,

	    EXP_DT,

	    [PERCENT],

	    AMOUNT,

	    NOTES,

	    RECORD_STATUS,

	    MAKER_ID,

	    CREATE_DT,

	    AUTH_STATUS,

	    CHECKER_ID,

	    APPROVE_DT,CURRENCY,RATE,TOTAL_AMT

	)

	VALUES

	(   @l_PAY_ID,        -- PAY_ID - varchar(15)

	    @l_PO_ID,        -- PO_ID - varchar(15)

	    '1',        -- PAY_PHASE - varchar(20)

	    GETDATE(), -- EXP_DT - datetime

	    100,      -- PERCENT - decimal(18, 0)

	    @TOTAL_AMT ,      -- AMOUNT - decimal(18, 0)

	    N'',       -- NOTES - nvarchar(1000)

	    '1',        -- RECORD_STATUS - varchar(1)

	    @p_MAKER_ID,        -- MAKER_ID - varchar(15)

	    GETDATE(), -- CREATE_DT - datetime

	    'U',        -- AUTH_STATUS - varchar(50)

	    '',        -- CHECKER_ID - varchar(15)

	    NULL,@CURRENCY,@RATE, @TOTAL_AMT*@RATE  -- APPROVE_DT - datetime

	    )

		EXEC SYS_CodeMasters_Gen 'TR_PO_PAYMENT', @l_PAY_ID out

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

	--INSERT INTO dbo.TR_PO_PAYMENT

	--(

	--    PAY_ID,

	--    PO_ID,

	--    PAY_PHASE,

	--    EXP_DT,

	--    [PERCENT],

	--    AMOUNT,

	--    NOTES,

	--    RECORD_STATUS,

	--    MAKER_ID,

	--    CREATE_DT,

	--    AUTH_STATUS,

	--    CHECKER_ID,

	--    APPROVE_DT

	--)

	--VALUES

	--(   @l_PAY_ID,        -- PAY_ID - varchar(15)

	--    @l_PO_ID,        -- PO_ID - varchar(15)

	--    '2',        -- PAY_PHASE - varchar(20)

	--    GETDATE(), -- EXP_DT - datetime

	--    50,      -- PERCENT - decimal(18, 0)

	--    @TOTAL_AMT / 2 ,      -- AMOUNT - decimal(18, 0)

	--    N'',       -- NOTES - nvarchar(1000)

	--    '1',        -- RECORD_STATUS - varchar(1)

	--    @p_MAKER_ID,        -- MAKER_ID - varchar(15)

	--    GETDATE(), -- CREATE_DT - datetime

	--    'E',        -- AUTH_STATUS - varchar(50)

	--    '',        -- CHECKER_ID - varchar(15)

	--    NULL  -- APPROVE_DT - datetime

	--    )

	IF(@TOTAL_AMT <=5000000)

	BEGIN

		UPDATE dbo.TR_PO_MASTER SET AUTH_STATUS='A' WHERE PO_ID=@l_PO_ID

	END

	--XOA DI NHA CUNG CAP

	--DELETE FROM @TABLE_NCC WHERE SUP_ID =@l_SUP_ID

	--- KET THUC VONG LAP KIEM TRA DANH SACH NHA CUNG CAP PYCMS

FETCH NEXT FROM lstDATANCC INTO @l_SUP_ID

	END

	CLOSE lstDATANCC

	DEALLOCATE lstDATANCC

	COMMIT TRANSACTION;

	RETURN 1;

	ABORT:

	BEGIN

		ROLLBACK TRANSACTION;

		RETURN 1;

	END;

	ABORT1:

	BEGIN

		CLOSE lstData;

		DEALLOCATE lstData;

		ROLLBACK TRANSACTION;

		RETURN 1;

	END;