You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
265 lines
21 KiB
265 lines
21 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[PROC_KFTC_DOMESTIC_HISTORY] Script Date: 2019-09-27 오후 2:52:28 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
----------------------------------------------------------------------------------
|
|
-- Procedure ID : PROC_KFTC_DOMESTIC_HISTORY
|
|
-- Procedure Name : 입금이체 로그 테이블
|
|
-- Dev Date : 2019.09.11
|
|
-- Developer : 김한성
|
|
-- Desc :
|
|
-- Text Desc : 돋움체, 9pt, Tab=8
|
|
-- Return Value : Record Set
|
|
-- SAMPLE :
|
|
----------------------------------------------------------------------------------
|
|
|
|
ALTER PROC [dbo].[PROC_KFTC_DOMESTIC_HISTORY]
|
|
(
|
|
@flag VARCHAR(20),
|
|
@customerId BIGINT = NULL,
|
|
@processId VARCHAR(50) = NULL,
|
|
@transferType VARCHAR(20),
|
|
@reqTransferDtime VARCHAR(20)= NULL,
|
|
@reqWdPrintContent NVARCHAR(20) = NULL,
|
|
@reqNameCheckOption VARCHAR(5)= NULL,
|
|
@reqCnt TINYINT= NULL,
|
|
@reqTranNo TINYINT= NULL,
|
|
@reqFintechUseNum VARCHAR(30) = NULL,
|
|
@reqBankCodeStd VARCHAR(3)= NULL,
|
|
@reqAccountNum VARCHAR(20)= NULL,
|
|
@reqAccountHolderName NVARCHAR(20)= NULL,
|
|
@reqPrintContent NVARCHAR(20)= NULL,
|
|
@reqAmt MONEY= NULL,
|
|
@serviceFee MONEY= NULL,
|
|
@reqTranAmt MONEY= NULL,
|
|
@reqMobileNo VARCHAR(20)= NULL,
|
|
@resApiTranId VARCHAR(20)= NULL,
|
|
@resApiTranDtime VARCHAR(20)= NULL,
|
|
@resRspCode VARCHAR(5)= NULL,
|
|
@resRspMessage NVARCHAR(100)= NULL,
|
|
@resWdBankCodeStd VARCHAR(3)= NULL,
|
|
@resWdBankCodeSub VARCHAR(7)= NULL,
|
|
@resWdBankName NVARCHAR(20)= NULL,
|
|
@resWdAccountNumMasked VARCHAR(20)= NULL,
|
|
@resWdPrintContent NVARCHAR(20)= NULL,
|
|
@resWdAccountHolderName NVARCHAR(20)= NULL,
|
|
@resCnt TINYINT= NULL,
|
|
@resTranNo TINYINT= NULL,
|
|
@resBankTranId VARCHAR(20)= NULL,
|
|
@resBankTranDate VARCHAR(8)= NULL,
|
|
@resBankCodeTran VARCHAR(3)= NULL,
|
|
@resBankRspCode VARCHAR(3)= NULL,
|
|
@resBankRspMessage NVARCHAR(100)= NULL,
|
|
@resBankCodeStd VARCHAR(3)= NULL,
|
|
@resBankCodeSub VARCHAR(7)= NULL,
|
|
@resBankName NVARCHAR(20)= NULL,
|
|
@resAccountNum VARCHAR(20)= NULL,
|
|
@resAccountNumMasked VARCHAR(20)= NULL,
|
|
@resPrintContent NVARCHAR(20)= NULL,
|
|
@resAccountHolderName NVARCHAR(20)= NULL,
|
|
@resTranAmt MONEY= NULL,
|
|
@logDate DATETIME= NULL,
|
|
@accountType NVARCHAR(20)= NULL
|
|
|
|
)
|
|
AS
|
|
|
|
SET NOCOUNT ON
|
|
BEGIN TRY
|
|
----------------------------------------------------------------------------------
|
|
-- Procedure Start
|
|
----------------------------------------------------------------------------------
|
|
BEGIN
|
|
declare @charge Money
|
|
|
|
IF @flag = 'INSERT'
|
|
BEGIN
|
|
|
|
DECLARE @vProcessId VARCHAR(30)
|
|
|
|
IF ISNULL(@processId,'') <> '' -- processId가 있다면 새로 생성하지 않음
|
|
BEGIN
|
|
SET @vProcessId = @processId
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SET @vProcessId = Convert(varchar(10),Getdate(),112) + Replace(Convert(varchar(12),Getdate(),114),':','') + @customerId
|
|
END
|
|
|
|
/*Check Easy remit customer block or not*/
|
|
IF EXISTS (SELECT 'X' FROM CUSTOMER_BLOCK_LIST(nolock) where customerid=@customerId and isBlock='Y')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1,'You account is blocked.Please contact GME Support!' ,null
|
|
RETURN;
|
|
END
|
|
|
|
IF(SELECT dbo.CheckCustomerPerDayAmt(@customerId,@reqAmt))=1
|
|
BEGIN
|
|
EXEC proc_errorHandler 1,'Easy remit Per day limit exceeded.!' ,null
|
|
RETURN;
|
|
END
|
|
|
|
----------------------------------------------
|
|
-- 가상계좌를 이용한 국내송금 customer 계좌정보 조회
|
|
----------------------------------------------
|
|
IF (@transferType = 'withdraw')
|
|
BEGIN
|
|
IF (ISNULL(@reqFintechUseNum,'') <> '')
|
|
BEGIN
|
|
SELECT @reqBankCodeStd=S.BANKCODESTD
|
|
,@reqAccountNum=S.ACCOUNTNUM
|
|
,@reqAccountHolderName=S.ACCOUNTNAME
|
|
FROM DBO.KFTC_CUSTOMER_SUB S(NOLOCK) WHERE S.CUSTOMERID=@customerId AND S.FINTECHUSENO=@reqFintechUseNum
|
|
END
|
|
END
|
|
----------------------------------------------
|
|
-- 가상계좌를 이용한 국내송금 wallet 조회
|
|
----------------------------------------------
|
|
ELSE IF(@transferType ='walletWithdraw')
|
|
BEGIN
|
|
SELECT @reqAccountNum = walletAccountNo ,@reqAccountHolderName=fullName,@transferType = 'withdraw',@resRspCode = 'A0000'
|
|
FROM CustomerMaster(nolock)
|
|
WHERE CUSTOMERID=@customerId
|
|
END
|
|
|
|
INSERT INTO KFTC_DOMESTIC_HISTORY
|
|
(
|
|
customerId, processId, transferType, reqTransferDtime, reqWdPrintContent,
|
|
reqNameCheckOption, reqCnt, reqTranNo, reqFintechUseNum, reqBankCodeStd,
|
|
reqAccountNum, reqAccountHolderName, reqPrintContent, reqAmt, serviceFee,
|
|
reqTranAmt, reqMobileNo, logDate, accountType, resRspCode
|
|
)
|
|
VALUES
|
|
(
|
|
@customerId, @vProcessId, @transferType, GETDATE(), @reqWdPrintContent,
|
|
@reqNameCheckOption, @reqCnt, @reqTranNo, @reqFintechUseNum, @reqBankCodeStd,
|
|
@reqAccountNum, @reqAccountHolderName, @reqPrintContent, @reqAmt, @serviceFee,
|
|
@reqTranAmt, @reqMobileNo, GETDATE(), @accountType, @resRspCode
|
|
)
|
|
|
|
SELECT '0' errorCode,@customerId customerId,@vProcessId ID
|
|
|
|
END
|
|
ELSE IF @flag = 'UPDATE'
|
|
BEGIN
|
|
BEGIN TRANSACTION
|
|
UPDATE KFTC_DOMESTIC_HISTORY
|
|
SET resApiTranId=@resApiTranId, resApiTranDtime=@resApiTranDtime, resRspCode=@resRspCode, resRspMessage=@resRspMessage,
|
|
resWdBankCodeStd=@resWdBankCodeStd, resWdBankCodeSub=@resWdBankCodeSub, resWdBankName=@resWdBankName, resWdAccountNumMasked=@resWdAccountNumMasked, resWdPrintContent=@resWdPrintContent,
|
|
resWdAccountHolderName=@resWdAccountHolderName, resCnt=@resCnt, resTranNo=@resTranNo, resBankTranId=@resBankTranId, resBankTranDate=@resBankTranDate,
|
|
resBankCodeTran=@resBankCodeTran, resBankRspCode=@resBankRspCode, resBankRspMessage=@resBankRspMessage, resBankCodeStd=@resBankCodeStd, resBankCodeSub=@resBankCodeSub,
|
|
resBankName=@resBankName, resAccountNum=@resAccountNum, resAccountNumMasked=@resAccountNumMasked, resPrintContent=@resPrintContent, resAccountHolderName=@resAccountHolderName,
|
|
resTranAmt=@resTranAmt, logDate=GETDATE()
|
|
WHERE customerId=@customerId AND processId = @processId AND transferType = @transferType
|
|
|
|
SELECT @reqTranAmt = reqTranAmt,@charge = serviceFee,@accountType = accountType
|
|
FROM KFTC_DOMESTIC_HISTORY(nolock)
|
|
WHERE customerId=@customerId AND processId = @processId AND transferType = @transferType
|
|
|
|
IF @accountType = 'wallet'
|
|
BEGIN
|
|
COMMIT TRANSACTION
|
|
SELECT '0' errorCode,@customerId customerId,@processId ID
|
|
RETURN
|
|
END
|
|
|
|
--Withdraw is success now generating withdraw voucher
|
|
IF @transferType='withdraw' AND @resRspCode='A0000'
|
|
BEGIN
|
|
insert into TblVirtualBankDepositDetail(customerName,virtualAccountNo,amount,receivedOn,depositor,processId)
|
|
select
|
|
firstName,walletAccountNo,@reqTranAmt,GETDATE(),'DT-W'+ @processId,@processId
|
|
FROM dbo.customerMaster(NOLOCK) WHERE customerId= @customerId
|
|
|
|
|
|
update customerMaster set availableBalance = isnull(availableBalance,0)+@reqTranAmt
|
|
WHERE customerId= @customerId
|
|
END
|
|
|
|
--deposit is success now generating deposit voucher
|
|
IF @transferType='deposit' AND @resRspCode='A0000'
|
|
BEGIN
|
|
SELECT @charge = serviceFee
|
|
FROM KFTC_DOMESTIC_HISTORY(nolock)
|
|
WHERE customerId = @customerId AND processId = @processId AND transferType = 'withdraw'
|
|
|
|
SET @reqTranAmt = @reqTranAmt+@charge
|
|
insert into TblVirtualBankDepositDetail(customerName,virtualAccountNo,amount,receivedOn,depositor,processId)
|
|
select
|
|
firstName,walletAccountNo, - @reqTranAmt,GETDATE(),'DT-D'+ @processId,@processId
|
|
FROM dbo.customerMaster(NOLOCK) WHERE customerId= @customerId
|
|
|
|
|
|
update customerMaster set availableBalance = isnull(availableBalance,0)-@reqTranAmt
|
|
WHERE customerId= @customerId
|
|
END
|
|
|
|
SELECT '0' errorCode,@customerId customerId,@processId ID
|
|
COMMIT TRANSACTION
|
|
IF @resRspCode='A0000'
|
|
EXEC proc_online_PushFromDomestic @tranId=@processId, @type = @transferType
|
|
END
|
|
ELSE IF @flag = 'UPDATE-HISTORY'
|
|
BEGIN
|
|
BEGIN TRANSACTION
|
|
UPDATE KFTC_DOMESTIC_HISTORY
|
|
SET resApiTranId=@resApiTranId, resApiTranDtime=@resApiTranDtime, resRspCode=@resRspCode, resRspMessage=@resRspMessage,
|
|
resWdBankCodeStd=@resWdBankCodeStd, resWdBankCodeSub=@resWdBankCodeSub, resWdBankName=@resWdBankName, resWdAccountNumMasked=@resWdAccountNumMasked, resWdPrintContent=@resWdPrintContent,
|
|
resWdAccountHolderName=@resWdAccountHolderName, resCnt=@resCnt, resTranNo=@resTranNo, resBankTranId=@resBankTranId, resBankTranDate=@resBankTranDate,
|
|
resBankCodeTran=@resBankCodeTran, resBankRspCode=@resBankRspCode, resBankRspMessage=@resBankRspMessage, resBankCodeStd=@resBankCodeStd, resBankCodeSub=@resBankCodeSub,
|
|
resBankName=@resBankName, resAccountNum=@resAccountNum, resAccountNumMasked=@resAccountNumMasked, resPrintContent=@resPrintContent, resAccountHolderName=@resAccountHolderName,
|
|
resTranAmt=@resTranAmt, logDate=GETDATE()
|
|
WHERE customerId=@customerId AND processId = @processId AND transferType = @transferType AND accountType = @accountType
|
|
|
|
SELECT '0' errorCode,@customerId customerId,@processId ID
|
|
COMMIT TRANSACTION
|
|
END
|
|
ELSE IF @flag = 'UPDATE-KJAPI'
|
|
BEGIN
|
|
BEGIN TRANSACTION
|
|
UPDATE KFTC_DOMESTIC_HISTORY
|
|
SET accountType=@accountType, resRspCode = @resRspCode, logDate=GETDATE()
|
|
WHERE customerId=@customerId AND processId = @processId AND transferType = @transferType
|
|
|
|
SELECT @reqTranAmt = reqTranAmt, @charge = serviceFee
|
|
FROM KFTC_DOMESTIC_HISTORY(nolock)
|
|
WHERE customerId = @customerId
|
|
AND processId = @processId
|
|
AND transferType = @transferType
|
|
|
|
--deposit is success now generating deposit voucher
|
|
IF @transferType='deposit' AND @resRspCode='A0000'
|
|
BEGIN
|
|
SELECT @charge = serviceFee
|
|
FROM KFTC_DOMESTIC_HISTORY(nolock)
|
|
WHERE customerId = @customerId AND processId = @processId AND transferType = 'withdraw'
|
|
|
|
SET @reqTranAmt = @reqTranAmt+@charge
|
|
insert into TblVirtualBankDepositDetail(customerName,virtualAccountNo,amount,receivedOn,depositor,processId)
|
|
select
|
|
firstName,walletAccountNo, - @reqTranAmt,GETDATE(),'DT-D'+ @processId,@processId
|
|
FROM dbo.customerMaster(NOLOCK) WHERE customerId= @customerId
|
|
|
|
|
|
UPDATE customerMaster set availableBalance = isnull(availableBalance,0)-@reqTranAmt
|
|
WHERE customerId= @customerId
|
|
END
|
|
|
|
SELECT '0' errorCode,@customerId customerId,@processId ID
|
|
COMMIT TRANSACTION
|
|
IF @resRspCode='A0000'
|
|
EXEC proc_online_PushFromDomestic @tranId=@processId, @type = 'KJAPI-DEPOSIT'
|
|
END
|
|
END
|
|
----------------------------------------------------------------------------------
|
|
-- Procedure End
|
|
----------------------------------------------------------------------------------
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF @@TRANCOUNT <>1
|
|
ROLLBACK TRANSACTION
|
|
END CATCH
|