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

USE [gAMSPro_VietcapitalBank_v2]

GO

/****** Object:  StoredProcedure [dbo].[TR_REQ_DOC_Ins_To_PO]    Script Date: 15-May-20 08:42:30 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

AS

BEGIN TRANSACTION;

--insert master		

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);

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_TOTAL_AMT= (SELECT SUM(TOTAL_AMT) FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_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(),

 'U', 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)

 DECLARE lstData CURSOR FOR

 SELECT HANGHOA_ID,QUANTITY,PRICE,TOTAL_AMT FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID

OPEN lstData

FETCH NEXT FROM lstData INTO @HANGHOA_ID,@QUANTITY,@PRICE,@TOTAL_AMT

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

 )

 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)

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

     '',        -- UNIT_ID - varchar(15)

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

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

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

     '',        -- 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)

     N'',       -- 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)

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

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

     ''         -- CONTRACT_DT - varchar(15)

     )

   FETCH NEXT FROM lstData INTO @HANGHOA_ID,@QUANTITY,@PRICE,@TOTAL_AMT

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)

	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

	)

	VALUES

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

	    @l_PO_ID,        -- PO_ID - varchar(15)

	    '1',        -- 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

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

	    '',        -- CHECKER_ID - varchar(15)

	    NULL  -- 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

	    'U',        -- 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

COMMIT TRANSACTION;

RETURN 1;

ABORT:

BEGIN

    ROLLBACK TRANSACTION;

    RETURN 1;

END;

ABORT1:

BEGIN

    CLOSE lstData;

    DEALLOCATE lstData;

    ROLLBACK TRANSACTION;

    RETURN 1;

END;