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.
540 lines
21 KiB
540 lines
21 KiB
ALTER PROC PROC_WSB_DEPOSIT_NOTICE (@Flag VARCHAR(20),
|
|
@customerId BIGINT = NULL,
|
|
@processId VARCHAR(50) = NULL,
|
|
@requestData NVARCHAR(max) = NULL,
|
|
@requestDate DATETIME = NULL,
|
|
@responseData NVARCHAR(max) = NULL,
|
|
@responseDate DATETIME = NULL,
|
|
@dataLen NUMERIC(4) = NULL,
|
|
@orgtId VARCHAR(10) = NULL,
|
|
@srvcDs VARCHAR(6) = NULL,
|
|
@trxTypeCd VARCHAR(4) = NULL,
|
|
@rspsCd VARCHAR(4) = NULL,
|
|
@tlgSeqNo NUMERIC(10) = NULL,
|
|
@tlgSendHur VARCHAR(14) = NULL,
|
|
@filler NVARCHAR(max) = NULL,
|
|
@virtualAccountNo VARCHAR(20) = NULL,
|
|
@maccNo VARCHAR(20) = NULL,
|
|
@trxDt VARCHAR(8) = NULL,
|
|
@trxTime VARCHAR(6) = NULL,
|
|
@trxAmt NUMERIC(15) = NULL,
|
|
@bankCd VARCHAR(3) = NULL,
|
|
@bankBranCd VARCHAR(7) = NULL,
|
|
@vtulAccTrnfCustNm NVARCHAR(max) = NULL,
|
|
@trxSeqNo NUMERIC(9) = NULL,
|
|
@cnclYn VARCHAR(1) = NULL,
|
|
@vtulAccTrnfAccNo VARCHAR(16) = NULL,
|
|
@filler1 NVARCHAR(max) = NULL,
|
|
@rowId INT = NULL,
|
|
@MethodName VARCHAR(100) = NULL)
|
|
AS
|
|
SET nocount ON;
|
|
SET xact_abort ON;
|
|
|
|
BEGIN try
|
|
DECLARE @depositor NVARCHAR(100),
|
|
@receivedOn VARCHAR(30)
|
|
|
|
IF @trxTypeCd = '0210'
|
|
BEGIN
|
|
SET @flag='iulog'
|
|
END
|
|
|
|
IF @flag = 'iulog'
|
|
BEGIN
|
|
INSERT INTO dbo.wsb_deposit_notice
|
|
(customerid,
|
|
processid,
|
|
requestdata,
|
|
requestdate,
|
|
datalen,
|
|
orgtid,
|
|
srvcds,
|
|
trxtypecd,
|
|
rspscd,
|
|
tlgseqno,
|
|
tlgsendhur,
|
|
filler,
|
|
virtualaccountno,
|
|
maccno,
|
|
trxdt,
|
|
trxtime,
|
|
trxamt,
|
|
bankcd,
|
|
bankbrancd,
|
|
vtulacctrnfcustnm,
|
|
trxseqno,
|
|
cnclyn,
|
|
vtulacctrnfaccno,
|
|
filler1,
|
|
responsedata,
|
|
responsedate)
|
|
VALUES ( @customerId,
|
|
@processId,
|
|
@requestData,
|
|
Getdate(),
|
|
@dataLen,
|
|
@orgtId,
|
|
@srvcDs,
|
|
@trxTypeCd,
|
|
@rspsCd,
|
|
@tlgSeqNo,
|
|
@tlgSendHur,
|
|
@filler,
|
|
@virtualAccountNo,
|
|
@maccNo,
|
|
@trxDt,
|
|
@trxTime,
|
|
@trxAmt,
|
|
@bankCd,
|
|
@bankBranCd,
|
|
@vtulAccTrnfCustNm,
|
|
@trxSeqNo,
|
|
Isnull(@cnclYn, 'N'),
|
|
@vtulAccTrnfAccNo,
|
|
@filler1,
|
|
@responseData,
|
|
CASE
|
|
WHEN @responseData = N'' THEN NULL
|
|
ELSE Getdate()
|
|
END )
|
|
|
|
SET @rowId = @@IDENTITY
|
|
|
|
IF @rspsCd = '0000'
|
|
BEGIN
|
|
----##send sms to customer
|
|
DECLARE @SMSBody VARCHAR(90) =
|
|
'Your GME Wallet is successfully credited by KRW '
|
|
+ Format(Cast(@trxAmt AS MONEY), '0,00')
|
|
+ ' Thank you for using GME.'
|
|
DECLARE @Mobile VARCHAR(20)
|
|
,@avaiableBalance MONEY
|
|
|
|
SELECT @Mobile = mobile,
|
|
@depositor = firstname,
|
|
@customerId = customerid
|
|
,@avaiableBalance=availableBalance
|
|
FROM customermaster(nolock)
|
|
WHERE walletaccountno = @virtualAccountNo
|
|
AND customertype = 11068
|
|
|
|
EXEC fastmoneypro_remit.dbo.Proc_calltosendsms
|
|
@FLAG = 'I',
|
|
@SMSBody = @SMSBody,
|
|
@MobileNo = @Mobile
|
|
|
|
----## VOUCHER ENTRY
|
|
UPDATE cm
|
|
SET cm.availablebalance = Isnull(cm.availablebalance, 0) +
|
|
@trxAmt
|
|
FROM dbo.customermaster cm
|
|
WHERE walletAccountNo = @virtualAccountNo
|
|
|
|
|
|
/*block easy remit customer if balance is more than 3 mil*/
|
|
IF(ISNULL(@avaiableBalance,0) + ISNULL(@trxAmt,0))>3000000
|
|
BEGIN
|
|
UPDATE customermaster SET
|
|
onlineUser ='N'
|
|
,isactive ='N'
|
|
,islocked ='Y'
|
|
,modifiedBy = 'SYSTEM'
|
|
,modifieddate = GETDATE()
|
|
WHERE customerid=@customerid
|
|
AND walletaccountno = @virtualAccountNo
|
|
AND customertype = 11068
|
|
END
|
|
|
|
|
|
|
|
--AND bankAccountNo = @no
|
|
INSERT INTO fastmoneypro_account.dbo.temp_tran
|
|
(entry_user_id,
|
|
acct_num,
|
|
part_tran_type,
|
|
tran_amt,
|
|
field1,
|
|
field2,
|
|
sessionid,
|
|
refrence,
|
|
emp_name)
|
|
SELECT 'system',
|
|
'100241008219',
|
|
'DR',
|
|
@trxAmt,
|
|
@virtualAccountNo,
|
|
'Easy Deposit',
|
|
@virtualAccountNo,
|
|
@rowId,
|
|
@depositor
|
|
----Welcome Savings Bank-Customer Account(0678571)
|
|
UNION ALL
|
|
SELECT 'system',
|
|
@virtualAccountNo,
|
|
'CR',
|
|
@trxAmt,
|
|
@virtualAccountNo,
|
|
'Easy Deposit',
|
|
@virtualAccountNo,
|
|
@rowId,
|
|
@depositor
|
|
|
|
SELECT '0' ErrorCode,
|
|
'Record has been added successfully.' Msg,
|
|
@rowId id
|
|
|
|
SELECT @receivedOn = CONVERT(VARCHAR, Getdate(), 101),
|
|
@depositor =
|
|
'Being Easy remit amount deposited on virtual ac : '
|
|
+ @virtualAccountNo
|
|
|
|
EXEC fastmoneypro_account.dbo.[Spa_savetemptrn]
|
|
@flag='i',
|
|
@sessionID= @virtualAccountNo,
|
|
@date=@receivedOn,
|
|
@narration=@depositor,
|
|
@company_id=1,
|
|
@v_type='R',
|
|
@user='WSB'
|
|
END
|
|
ELSE
|
|
SELECT '0' ErrorCode,
|
|
'Record has been added successfully.' Msg,
|
|
@rowId id
|
|
|
|
RETURN
|
|
END
|
|
|
|
IF @flag = 'ilog'
|
|
BEGIN
|
|
SET @processId =Newid()
|
|
|
|
IF NOT EXISTS(SELECT 'A'
|
|
FROM customermaster(nolock)
|
|
WHERE walletaccountno = @virtualAccountNo
|
|
AND customertype = 11068) ---WSB CUSTOMER
|
|
BEGIN
|
|
INSERT INTO dbo.wsb_deposit_notice
|
|
(customerid,
|
|
processid,
|
|
requestdata,
|
|
requestdate,
|
|
datalen,
|
|
orgtid,
|
|
srvcds,
|
|
trxtypecd,
|
|
rspscd,
|
|
tlgseqno,
|
|
tlgsendhur,
|
|
filler,
|
|
virtualaccountno,
|
|
maccno,
|
|
trxdt,
|
|
trxtime,
|
|
trxamt,
|
|
bankcd,
|
|
bankbrancd,
|
|
vtulacctrnfcustnm,
|
|
trxseqno,
|
|
cnclyn,
|
|
vtulacctrnfaccno,
|
|
filler1)
|
|
VALUES ( @customerId,
|
|
@processId,
|
|
@requestData,
|
|
Getdate(),
|
|
@dataLen,
|
|
@orgtId,
|
|
@srvcDs,
|
|
@trxTypeCd,
|
|
@rspsCd,
|
|
@tlgSeqNo,
|
|
@tlgSendHur,
|
|
@filler,
|
|
@virtualAccountNo,
|
|
@maccNo,
|
|
@trxDt,
|
|
@trxTime,
|
|
@trxAmt,
|
|
@bankCd,
|
|
@bankBranCd,
|
|
@vtulAccTrnfCustNm,
|
|
@trxSeqNo,
|
|
Isnull(@cnclYn, 'N'),
|
|
@vtulAccTrnfAccNo,
|
|
@filler1 )
|
|
|
|
SELECT '1' ErrorCode,
|
|
'Invalid Virtual Account Found.' Msg,
|
|
NULL
|
|
|
|
RETURN
|
|
END
|
|
|
|
IF EXISTS (SELECT 'A'
|
|
FROM dbo.wsb_deposit_notice (nolock)
|
|
WHERE processid = @processId)
|
|
BEGIN
|
|
INSERT INTO dbo.wsb_deposit_notice
|
|
(customerid,
|
|
processid,
|
|
requestdata,
|
|
requestdate,
|
|
datalen,
|
|
orgtid,
|
|
srvcds,
|
|
trxtypecd,
|
|
rspscd,
|
|
tlgseqno,
|
|
tlgsendhur,
|
|
filler,
|
|
virtualaccountno,
|
|
maccno,
|
|
trxdt,
|
|
trxtime,
|
|
trxamt,
|
|
bankcd,
|
|
bankbrancd,
|
|
vtulacctrnfcustnm,
|
|
trxseqno,
|
|
cnclyn,
|
|
vtulacctrnfaccno,
|
|
filler1)
|
|
VALUES ( @customerId,
|
|
@processId,
|
|
@requestData,
|
|
Getdate(),
|
|
@dataLen,
|
|
@orgtId,
|
|
@srvcDs,
|
|
@trxTypeCd,
|
|
@rspsCd,
|
|
@tlgSeqNo,
|
|
@tlgSendHur,
|
|
@filler,
|
|
@virtualAccountNo,
|
|
@maccNo,
|
|
@trxDt,
|
|
@trxTime,
|
|
@trxAmt,
|
|
@bankCd,
|
|
@bankBranCd,
|
|
@vtulAccTrnfCustNm,
|
|
@trxSeqNo,
|
|
Isnull(@cnclYn, 'N'),
|
|
@vtulAccTrnfAccNo,
|
|
@filler1 )
|
|
|
|
SELECT '1' ErrorCode,
|
|
'Same record already exists.' Msg,
|
|
NULL
|
|
|
|
RETURN
|
|
END
|
|
|
|
BEGIN TRAN
|
|
|
|
SELECT @Mobile = mobile,
|
|
@depositor = firstname,
|
|
@customerId = customerid
|
|
FROM customermaster(nolock)
|
|
WHERE walletaccountno = @virtualAccountNo
|
|
AND customertype = 11068
|
|
|
|
INSERT INTO dbo.wsb_deposit_notice
|
|
(customerid,
|
|
processid,
|
|
requestdata,
|
|
requestdate,
|
|
datalen,
|
|
orgtid,
|
|
srvcds,
|
|
trxtypecd,
|
|
rspscd,
|
|
tlgseqno,
|
|
tlgsendhur,
|
|
filler,
|
|
virtualaccountno,
|
|
maccno,
|
|
trxdt,
|
|
trxtime,
|
|
trxamt,
|
|
bankcd,
|
|
bankbrancd,
|
|
vtulacctrnfcustnm,
|
|
trxseqno,
|
|
cnclyn,
|
|
vtulacctrnfaccno,
|
|
filler1,
|
|
responsedata,
|
|
responsedate)
|
|
VALUES ( @customerId,
|
|
@processId,
|
|
@requestData,
|
|
Getdate(),
|
|
@dataLen,
|
|
@orgtId,
|
|
@srvcDs,
|
|
@trxTypeCd,
|
|
@rspsCd,
|
|
@tlgSeqNo,
|
|
@tlgSendHur,
|
|
@filler,
|
|
@virtualAccountNo,
|
|
@maccNo,
|
|
@trxDt,
|
|
@trxTime,
|
|
@trxAmt,
|
|
@bankCd,
|
|
@bankBranCd,
|
|
@vtulAccTrnfCustNm,
|
|
@trxSeqNo,
|
|
Isnull(@cnclYn, 'N'),
|
|
@vtulAccTrnfAccNo,
|
|
@filler1,
|
|
@responseData,
|
|
CASE
|
|
WHEN @responseData = N'' THEN NULL
|
|
ELSE Getdate()
|
|
END )
|
|
|
|
SET @rowId = @@IDENTITY
|
|
|
|
INSERT INTO dbo.TblVirtualBankDepositDetail
|
|
(
|
|
processId,
|
|
virtualAccountNo,
|
|
amount,
|
|
receivedOn,
|
|
partnerServiceKey,
|
|
logDate
|
|
)
|
|
VALUES (
|
|
@processId,
|
|
@virtualAccountNo,
|
|
@trxAmt,
|
|
CONVERT(VARCHAR(19), GETDATE(), 120),
|
|
'WSB',
|
|
GETDATE()
|
|
)
|
|
|
|
|
|
/*Customer block logic after deposit 1000000 mil krw*/
|
|
DECLARE @todayTotalAmt NUMERIC(15)
|
|
SELECT @todayTotalAmt=ISNULL(sum(amount),0) FROM TblVirtualBankDepositDetail(NOLOCK)
|
|
WHERE virtualaccountno=@virtualAccountNo AND receivedOn BETWEEN CAST(GETDATE() AS DATE)
|
|
AND GETDATE() and partnerServiceKey='WSB' GROUP BY virtualaccountno
|
|
|
|
print @todayTotalAmt
|
|
|
|
/*new table insert logic here */
|
|
IF @todayTotalAmt >1000000
|
|
BEGIN
|
|
INSERT INTO CUSTOMER_BLOCK_LIST
|
|
(
|
|
customerId, reason, isBlock, blockedDate, blockedBy
|
|
)
|
|
SELECT @customerId,'01', 'Y', Getdate(), 'system'
|
|
|
|
|
|
END
|
|
|
|
|
|
COMMIT TRAN
|
|
|
|
SELECT '0' ErrorCode,
|
|
'Record has been added successfully.' Msg,
|
|
@rowId id
|
|
END
|
|
|
|
IF @flag = 'ulog'
|
|
BEGIN
|
|
BEGIN TRANSACTION
|
|
|
|
SELECT TOP 1 @processId = processid
|
|
FROM wsb_deposit_notice(nolock)
|
|
WHERE rowid = @rowId
|
|
|
|
UPDATE wsb_deposit_notice
|
|
SET responsedata = @responseData,
|
|
responsedate = Getdate(),
|
|
datalen = Isnull(@dataLen, datalen),
|
|
orgtid = Isnull(@orgtId, orgtid),
|
|
srvcds = Isnull(@srvcDs, srvcds),
|
|
trxtypecd = Isnull(@trxTypeCd, trxtypecd),
|
|
rspscd = Isnull(@rspsCd, rspscd),
|
|
tlgseqno = Isnull(@tlgSeqNo, tlgseqno),
|
|
tlgsendhur = Isnull(@tlgSendHur, tlgsendhur),
|
|
filler = Isnull(@filler, filler),
|
|
virtualaccountno = Isnull(@virtualAccountNo, virtualaccountno
|
|
),
|
|
maccno = Isnull(@maccNo, maccno),
|
|
trxdt = Isnull(@trxDt, trxdt),
|
|
trxtime = Isnull(@trxTime, trxtime),
|
|
trxamt = Isnull(@trxAmt, trxamt),
|
|
bankcd = Isnull(@bankCd, bankcd),
|
|
bankbrancd = Isnull(@bankBranCd, bankbrancd),
|
|
vtulacctrnfcustnm = Isnull(@vtulAccTrnfCustNm,
|
|
vtulacctrnfcustnm),
|
|
trxseqno = Isnull(@trxSeqNo, trxseqno),
|
|
cnclyn = Isnull(@cnclYn, cnclyn),
|
|
vtulacctrnfaccno = Isnull(@vtulAccTrnfAccNo, vtulacctrnfaccno
|
|
),
|
|
filler1 = Isnull(@filler1, filler1)
|
|
WHERE rowid = @rowId
|
|
|
|
COMMIT TRANSACTION
|
|
|
|
SELECT 0 AS code,
|
|
'Record saved successfully' message,
|
|
@processId id
|
|
END
|
|
|
|
IF @Flag = 'details'
|
|
BEGIN
|
|
--DECLARE @NewValue VARCHAR(10)
|
|
--SET @NewValue= NEXT VALUE FOR dbo.WSB_SEQUENCE
|
|
--SET @tlgSeqNo=(SELECT CONVERT(VARCHAR(6), GETDATE(), 12)) +@NewValue
|
|
SELECT datalen,
|
|
orgtid,
|
|
srvcds,
|
|
'0210' AS trxTypeCd,
|
|
Isnull(rspscd, '0000') AS rspsCd,
|
|
tlgseqno,
|
|
tlgsendhur,
|
|
filler,
|
|
virtualaccountno AS vtulAccNo,
|
|
maccno,
|
|
trxdt,
|
|
trxtime,
|
|
trxamt,
|
|
bankcd,
|
|
bankbrancd,
|
|
vtulacctrnfcustnm,
|
|
trxseqno,
|
|
Isnull(cnclyn, 'N'),
|
|
vtulacctrnfaccno,
|
|
filler1,
|
|
customerid,
|
|
processid
|
|
FROM wsb_deposit_notice (nolock)
|
|
WHERE rowid = @rowId
|
|
|
|
RETURN
|
|
END
|
|
END try
|
|
|
|
BEGIN catch
|
|
IF @@TRANCOUNT > 0
|
|
ROLLBACK TRANSACTION
|
|
|
|
DECLARE @errorMessage VARCHAR(max)
|
|
|
|
SET @errorMessage = Error_message()
|
|
|
|
EXEC Proc_errorhandler
|
|
1,
|
|
@errorMessage,
|
|
NULL
|
|
END catch
|