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.
228 lines
9.0 KiB
228 lines
9.0 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[[proc_DomesticTransferVoucher]] Script Date: 9/27/2019 2:37:27 PM ******/
|
|
/****** Created by Pralhad ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
----exec [proc_DomesticTransferVoucher] @flag = 'transfer',@User='',@walletAccountNo='9424010721203',@receiverAccountNo='ssss',@transferAmt=10240,@chargeAmt=0,@processId='20190927184373900'
|
|
|
|
ALTER PROC [dbo].[proc_DomesticTransferVoucher]
|
|
@flag VARCHAR(20) ,
|
|
@User VARCHAR(50) ,
|
|
@walletAccountNo VARCHAR(20) ,
|
|
@receiverBankCode VARCHAR(10) ,
|
|
@receiverAccountNo VARCHAR(100) ,
|
|
@transferAmt MONEY ,
|
|
@chargeAmt MONEY ,
|
|
@processId VARCHAR(20)
|
|
|
|
AS
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @availableBalance MONEY
|
|
DECLARE @Narration VARCHAR(MAX)
|
|
DECLARE @vVoucherType CHAR(1) = 'Y'
|
|
DECLARE @Date VARCHAR(10)
|
|
DECLARE @BankCode varchar(100)
|
|
|
|
IF @flag = 'transfer'
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM customerMaster (NOLOCK) WHERE walletAccountNo = @walletAccountNo)
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Invalid customer requested for transfer', NULL
|
|
RETURN
|
|
END
|
|
|
|
--SELECT @availableBalance = ISNULL(availableBalance, 0)
|
|
--FROM customerMaster (NOLOCK) WHERE walletAccountNo = @walletAccountNo
|
|
|
|
--if not exists(select 'a' from TblVirtualBankDepositDetail(nolock) where virtualAccountNo = @walletAccountNo)
|
|
--BEGIN
|
|
-- EXEC proc_errorHandler 1, 'Balance not found for refund', NULL
|
|
-- RETURN
|
|
--END
|
|
--IF ISNULL(@transferAmt,0) <= 10000
|
|
--BEGIN
|
|
-- EXEC proc_errorHandler 1, 'Invalid amount requested for refund', NULL
|
|
-- RETURN
|
|
--END
|
|
--IF ISNULL(@availableBalance - (@transferAmt),0)< 0
|
|
--BEGIN
|
|
-- EXEC proc_errorHandler 1, 'Invalid amount requested for refund', NULL
|
|
-- RETURN
|
|
--END
|
|
|
|
DELETE FROM FastMoneyPro_Account.dbo.temp_tran WHERE SESSIONID = @processId
|
|
|
|
begin transaction
|
|
----insert into TblVirtualBankDepositDetail(processId,obpId,customerName,virtualAccountNo,amount,receivedOn,partnerServiceKey
|
|
----,institution,depositor,no,logDate)
|
|
----select top 1 @processId,obpId,customerName,virtualAccountNo,- @transferAmt,getdate(),'000'
|
|
----,institution,'DT-WalletSuccess',no,getdate() from TblVirtualBankDepositDetail (nolock)
|
|
----where virtualAccountNo= @walletAccountNo
|
|
|
|
----update customerMaster set availableBalance = availableBalance- @transferAmt where walletAccountNo = @walletAccountNo
|
|
|
|
--## 100241027580 Kwangju-(574382) CMS Customer Account-KRW
|
|
INSERT INTO FastMoneyPro_Account.dbo.temp_tran(entry_user_id,acct_num,part_tran_type,tran_amt,field1,field2
|
|
,sessionID,refrence)
|
|
SELECT @User,'100241027580','cr',(@transferAmt - ISNULL(@chargeAmt,0)),@walletAccountNo,'DT-Wallet Deposit',@processId,@processId UNION ALL
|
|
SELECT @User,@walletAccountNo,'dr',@transferAmt,@walletAccountNo,'DT-Wallet Deposit',@processId,@processId
|
|
|
|
IF ISNULL(@chargeAmt,0) >0
|
|
BEGIN
|
|
INSERT INTO FastMoneyPro_Account.dbo.temp_tran(entry_user_id,acct_num,part_tran_type,tran_amt,field1,field2
|
|
,sessionID,refrence)
|
|
SELECT @User,'421793414','cr',ISNULL(@chargeAmt,0),@walletAccountNo,'DT-Wallet Deposit',@processId,@processId
|
|
END
|
|
|
|
COMMIT TRANSACTION
|
|
--DECLARE @vVoucherType CHAR(1) = 'Y',@Date VARCHAR(10),@BankCode varchar(100)
|
|
SELECT @Date = FORMAT(GETDATE(),'yyyy.MM.dd')
|
|
|
|
SELECT @receiverAccountNo= reqAccountNum,@BankCode=reqBankCodeStd
|
|
FROM KFTC_DOMESTIC_HISTORY(nolock) his
|
|
WHERE transferType = 'deposit' AND
|
|
his.processId = @processId
|
|
|
|
SELECT TOP 1 @narration = firstName FROM customerMaster(NOLOCK) WHERE walletAccountNo = @walletAccountNo
|
|
|
|
SELECT @BankCode = BankName FROM KoreanBankList(nolock) where bankCode = @BankCode
|
|
|
|
SET @narration = 'Local Transfer wallet deposit to '+ ISNULL(@narration,'') +'/' +ISNULL(@BankCode,'')+'/'+ISNULL(@receiverAccountNo,'')
|
|
|
|
exec FastMoneyPro_Account.dbo.[spa_saveTempTrn] @flag='i',@sessionID= @processId,@date=@Date,@narration=@Narration,@company_id=1,@v_type= @vVoucherType,@user=@user
|
|
END
|
|
ELSE IF @flag = 'KJAPI-DOMESTIC-TXN'
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM customerMaster (NOLOCK) WHERE walletAccountNo = @walletAccountNo)
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Invalid customer requested for transfer', NULL
|
|
RETURN
|
|
END
|
|
|
|
--DECLARE @availableBalance MONEY
|
|
--DECLARE @Narration VARCHAR(MAX)
|
|
|
|
DELETE FROM FastMoneyPro_Account.dbo.temp_tran WHERE SESSIONID = @processId
|
|
|
|
BEGIN TRANSACTION
|
|
INSERT INTO FastMoneyPro_Account.dbo.temp_tran(entry_user_id,acct_num,part_tran_type,tran_amt,field1,field2
|
|
,sessionID,refrence)
|
|
SELECT @User,'100241011536','cr',(@transferAmt - ISNULL(@chargeAmt,0)),@walletAccountNo,'DT-Wallet Deposit',@processId,@processId
|
|
UNION ALL
|
|
SELECT @User,@walletAccountNo,'dr',@transferAmt,@walletAccountNo,'DT-Wallet Deposit',@processId,@processId
|
|
|
|
IF ISNULL(@chargeAmt,0) >0
|
|
BEGIN
|
|
INSERT INTO FastMoneyPro_Account.dbo.temp_tran(entry_user_id,acct_num,part_tran_type,tran_amt,field1,field2
|
|
,sessionID,refrence)
|
|
SELECT @User,'421793414','cr',ISNULL(@chargeAmt,0),@walletAccountNo,'DT-Wallet Deposit',@processId,@processId
|
|
END
|
|
COMMIT TRANSACTION
|
|
|
|
--DECLARE @vVoucherType CHAR(1) = 'Y'
|
|
DECLARE @today VARCHAR(10)
|
|
DECLARE @vBankName VARCHAR(100)
|
|
|
|
SELECT @today = FORMAT(GETDATE(),'yyyy.MM.dd')
|
|
|
|
-- SELECT @receiverAccountNo= reqAccountNum,
|
|
-- @BankCode=reqBankCodeStd
|
|
-- FROM KFTC_DOMESTIC_HISTORY(nolock) his
|
|
-- WHERE transferType = 'deposit'
|
|
-- AND his.processId = @processId
|
|
|
|
SELECT TOP 1 @narration = firstName
|
|
FROM customerMaster(NOLOCK)
|
|
WHERE walletAccountNo = @walletAccountNo
|
|
|
|
SELECT @vBankName = BankName
|
|
FROM KoreanBankList(nolock)
|
|
WHERE bankCode = @receiverBankCode
|
|
|
|
SET @narration = 'Local Transfer wallet deposit to '+ ISNULL(@narration,'') +'/' +ISNULL(@vBankName,'')+'/'+ISNULL(@receiverAccountNo,'')
|
|
|
|
exec FastMoneyPro_Account.dbo.[spa_saveTempTrn]
|
|
@flag='i',
|
|
@sessionID= @processId,
|
|
@date=@today,
|
|
@narration=@Narration,
|
|
@company_id=1,
|
|
@v_type= @vVoucherType,
|
|
@user=@user
|
|
END
|
|
------------------------------------------------------------------------------------------
|
|
-- 전표테스트
|
|
------------------------------------------------------------------------------------------
|
|
ELSE IF @flag = 'voucher-test'
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM customerMaster (NOLOCK) WHERE walletAccountNo = @walletAccountNo)
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Invalid customer requested for transfer', NULL
|
|
RETURN
|
|
END
|
|
|
|
--SELECT @availableBalance = ISNULL(availableBalance, 0)
|
|
--FROM customerMaster (NOLOCK) WHERE walletAccountNo = @walletAccountNo
|
|
|
|
--if not exists(select 'a' from TblVirtualBankDepositDetail(nolock) where virtualAccountNo = @walletAccountNo)
|
|
--BEGIN
|
|
-- EXEC proc_errorHandler 1, 'Balance not found for refund', NULL
|
|
-- RETURN
|
|
--END
|
|
--IF ISNULL(@transferAmt,0) <= 10000
|
|
--BEGIN
|
|
-- EXEC proc_errorHandler 1, 'Invalid amount requested for refund', NULL
|
|
-- RETURN
|
|
--END
|
|
--IF ISNULL(@availableBalance - (@transferAmt),0)< 0
|
|
--BEGIN
|
|
-- EXEC proc_errorHandler 1, 'Invalid amount requested for refund', NULL
|
|
-- RETURN
|
|
--END
|
|
|
|
DELETE FROM FastMoneyPro_Account.dbo.temp_tran WHERE SESSIONID = @processId
|
|
|
|
begin transaction
|
|
----insert into TblVirtualBankDepositDetail(processId,obpId,customerName,virtualAccountNo,amount,receivedOn,partnerServiceKey
|
|
----,institution,depositor,no,logDate)
|
|
----select top 1 @processId,obpId,customerName,virtualAccountNo,- @transferAmt,getdate(),'000'
|
|
----,institution,'DT-WalletSuccess',no,getdate() from TblVirtualBankDepositDetail (nolock)
|
|
----where virtualAccountNo= @walletAccountNo
|
|
|
|
----update customerMaster set availableBalance = availableBalance- @transferAmt where walletAccountNo = @walletAccountNo
|
|
|
|
--## 100241027580 Kwangju-(574382) CMS Customer Account-KRW
|
|
INSERT INTO FastMoneyPro_Account.dbo.temp_tran(entry_user_id,acct_num,part_tran_type,tran_amt,field1,field2
|
|
,sessionID,refrence)
|
|
SELECT @User,'100241027580','cr',(@transferAmt - ISNULL(@chargeAmt,0)),@walletAccountNo,'DT-Wallet Deposit',@processId,@processId UNION ALL
|
|
SELECT @User,@walletAccountNo,'dr',@transferAmt,@walletAccountNo,'DT-Wallet Deposit',@processId,@processId
|
|
|
|
IF ISNULL(@chargeAmt,0) >0
|
|
BEGIN
|
|
INSERT INTO FastMoneyPro_Account.dbo.temp_tran(entry_user_id,acct_num,part_tran_type,tran_amt,field1,field2
|
|
,sessionID,refrence)
|
|
SELECT @User,'421793414','cr',ISNULL(@chargeAmt,0),@walletAccountNo,'DT-Wallet Deposit',@processId,@processId
|
|
END
|
|
|
|
COMMIT TRANSACTION
|
|
--DECLARE @vVoucherType CHAR(1) = 'Y',@Date VARCHAR(10),@BankCode varchar(100)
|
|
SELECT @Date = FORMAT(GETDATE(),'yyyy.MM.dd')
|
|
|
|
SELECT @receiverAccountNo= reqAccountNum,@BankCode=reqBankCodeStd
|
|
FROM KFTC_DOMESTIC_HISTORY(nolock) his
|
|
WHERE transferType = 'deposit' AND
|
|
his.processId = @processId
|
|
|
|
SELECT TOP 1 @narration = firstName FROM customerMaster(NOLOCK) WHERE walletAccountNo = @walletAccountNo
|
|
|
|
SELECT @BankCode = BankName FROM KoreanBankList(nolock) where bankCode = @BankCode
|
|
|
|
SET @narration = 'Local Transfer wallet deposit to '+ ISNULL(@narration,'') +'/' +ISNULL(@BankCode,'')+'/'+ISNULL(@receiverAccountNo,'')
|
|
|
|
--exec FastMoneyPro_Account.dbo.[spa_saveTempTrn] @flag='i',@sessionID= @processId,@date=@Date,@narration=@Narration,@company_id=1,@v_type= @vVoucherType,@user=@user
|
|
END
|