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.
1605 lines
37 KiB
1605 lines
37 KiB
|
|
|
|
ALTER PROC [dbo].[proc_SendTransaction]
|
|
@User VARCHAR(100) = NULL
|
|
,@SenderId INT = NULL
|
|
,@sIpAddress VARCHAR(20) = NULL
|
|
,@ReceiverId INT = NULL
|
|
,@rFirstName VARCHAR(50) = NULL
|
|
,@rMiddleName VARCHAR(50) = NULL
|
|
,@rLastName VARCHAR(50) = NULL
|
|
,@rIdType VARCHAR(50) = NULL
|
|
,@rIdNo VARCHAR(30) = NULL
|
|
,@rIdIssue VARCHAR(10) = NULL
|
|
,@rIdExpiry VARCHAR(10) = NULL
|
|
,@rDob VARCHAR(10) = NULL
|
|
,@rMobileNo VARCHAR(20) = NULL
|
|
,@rNativeCountry VARCHAR(50) = NULL
|
|
,@rStateId INT = NULL
|
|
,@rDistrictId INT = NULL
|
|
,@rAddress VARCHAR(100) = NULL
|
|
,@rCity VARCHAR(50) = NULL
|
|
,@rEmail VARCHAR(50) = NULL
|
|
,@rAccountNo VARCHAR(50) = NULL
|
|
,@sCountryId INT = NULL
|
|
,@pCountryId INT = NULL
|
|
,@deliveryMethodId INT = NULL
|
|
,@pBankId BIGINT = NULL
|
|
,@pBranchId BIGINT = NULL
|
|
,@collCurr VARCHAR(3) = NULL
|
|
,@payoutCurr VARCHAR(3) = NULL
|
|
,@collAmt MONEY = NULL
|
|
,@payoutAmt MONEY = NULL
|
|
,@transferAmt MONEY = NULL
|
|
,@exRate MONEY = NULL
|
|
,@calBy CHAR(1) = NULL
|
|
,@tpExRate DECIMAL(30, 12) = NULL
|
|
,@payOutPartnerId BIGINT = NULL
|
|
,@forexSessionId VARCHAR(40) = NULL
|
|
,@kftcLogId BIGINT = NULL
|
|
,@paymentType VARCHAR(20) = NULL
|
|
,@scDiscount MONEY = NULL
|
|
,@PurposeOfRemittance VARCHAR(100) = NULL
|
|
,@SourceOfFund VARCHAR(100) = NULL
|
|
,@PurposeOfRemittanceOther VARCHAR(100) = NULL
|
|
,@SourceOfFundOther VARCHAR(100) = NULL
|
|
,@RelWithSender VARCHAR(200) = NULL
|
|
,@SourceType CHAR(1) = NULL
|
|
,@schemeId BIGINT = NULL
|
|
,@processId VARCHAR(40) = NULL
|
|
,@flag VARCHAR(100)
|
|
,@controlNo VARCHAR(20) = NULL
|
|
,@PartnerPin VARCHAR(20) = NULL
|
|
,@PartnerId VARCHAR(20) = NULL
|
|
,@tranId BIGINT = NULL
|
|
,@errorCode INT = NULL
|
|
,@Message NVARCHAR(500) = NULL
|
|
,@sAdd2 NVARCHAR(150) = NULL
|
|
,@goodsOrigin VARCHAR(100) = NULL
|
|
,@goodsType VARCHAR(100) = NULL
|
|
,@portOfShipment VARCHAR(100) = NULL
|
|
,@discountedFee MONEY = NULL
|
|
AS
|
|
-----------------------------------------------------------
|
|
-- For Broadcast notification , @flag = 'send'
|
|
-- #101 - MOBILE CHANGES , #361 - MULTI-LINGUAL
|
|
-- #476 - Changes in dotransaction Payment of Import Good
|
|
-- #101 - set error code as 19 for unapproved customer
|
|
-- Disable ProcBroadCastMobile @Flag='TRANSACTION_SUCCESS'
|
|
-- #495 Added condition for @sourceOfFundOther and @purposeOfRemittanceOther
|
|
-- Fix issue related with Bank id invalid add @pBankId_O,@payOutPartnerId_O
|
|
--#643 Mobile transaction wrongly flag for Questionnaire require due to Visa Status
|
|
-- #717 - insert visaStatus in tranSendersTemp
|
|
-- check mobileApprovedDate instead of approved date
|
|
-- #756 Duplicate pin generated
|
|
-- insert into controllist for M
|
|
--#767 Relationship displayed blank for mobile transaction.
|
|
-- #767 relationship other
|
|
-- #790 Change column to store relationship others for Mobile transaction
|
|
--#810 Missing sending commission in mobile Txn voucher
|
|
--#831 Disabled customer allowed to do Mobile txn
|
|
--#820 Donga wrong commission for mobile txn
|
|
--#Bug #1192 Sender Expiry not check from Mobile Transaction
|
|
-- #1003 - Reward Points, @flag = 'Send'
|
|
-- #1498 - TF NO Balance
|
|
-- #1590 - Customer Loyalty
|
|
-- #11358 - show branch routing number for mobile transaction in search transaction , pbankBranchName
|
|
-- #12985 - invalid routing no case
|
|
-----------------------------------------------------------
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
|
|
BEGIN TRY
|
|
DECLARE @complianceRuleId INT
|
|
,@cAmtUSD MONEY
|
|
,@complienceMessage VARCHAR(1000) = NULL
|
|
,@shortMsg VARCHAR(100) = NULL
|
|
,@complienceErrorCode TINYINT = NULL
|
|
,@compErrorCode INT
|
|
,@discountType VARCHAR(2) = NULL
|
|
,@discountvalue MONEY = NULL
|
|
,@couponType VARCHAR(3) = NULL
|
|
,@discountPercent MONEY = NULL
|
|
,@couponName VARCHAR(20) = NULL
|
|
,@ServiceCharge_Temp MONEY = NULL
|
|
,@schemePremium MONEY = NULL
|
|
,@customerType INT = NULL
|
|
,@msg VARCHAR(MAX) = NULL
|
|
,@pBankId_O BIGINT = @pBankId
|
|
,@payOutPartnerId_O BIGINT = @payOutPartnerId
|
|
,@RelWithSenderOthers VARCHAR(200) = NULL
|
|
,@receiverName VARCHAR(100) = NULL
|
|
,@createdFrom VARCHAR(10) = NULL
|
|
,@tranCount INT = NULL
|
|
,@schemeCount INT = NULL
|
|
,@isEligible CHAR(1) = NULL
|
|
,@schemeCodeId INT = NULL
|
|
,@isFirstTran CHAR(1) = 'N'
|
|
,@introducer VARCHAR(25) = NULL
|
|
|
|
IF EXISTS (
|
|
SELECT *
|
|
FROM STATICDATAVALUE(NOLOCK)
|
|
WHERE VALUEID = @RelWithSender
|
|
AND TYPEID = 2100
|
|
AND isActive = 'N'
|
|
)
|
|
BEGIN
|
|
SELECT TOP 1 @receiverName = ISNULL(firstName, '') + ISNULL(' ' + middleName, '') + ISNULL(' ' + lastName1, '') + ISNULL(' ' + lastName2, '')
|
|
FROM dbo.receiverInformation RI(NOLOCK)
|
|
WHERE receiverId = @receiverId
|
|
|
|
SET @msg = 'Please update the details of receiver [ ' + @receiverName + ' ] before performing transaction!';
|
|
|
|
EXEC proc_errorHandler 21
|
|
,@msg
|
|
,NULL;
|
|
Rollback Transaction
|
|
RETURN;
|
|
END
|
|
|
|
IF EXISTS (
|
|
SELECT *
|
|
FROM STATICDATAVALUE(NOLOCK)
|
|
WHERE VALUEID = @PurposeOfRemittance
|
|
AND TYPEID = 3800
|
|
AND isActive = 'Y'
|
|
)
|
|
BEGIN
|
|
SELECT @PurposeOfRemittance = detailTitle
|
|
FROM STATICDATAVALUE(NOLOCK)
|
|
WHERE VALUEID = @PurposeOfRemittance
|
|
AND TYPEID = 3800
|
|
END
|
|
|
|
IF EXISTS (
|
|
SELECT *
|
|
FROM STATICDATAVALUE(NOLOCK)
|
|
WHERE VALUEID = @sourceOfFund
|
|
AND TYPEID = 3900
|
|
AND isActive = 'Y'
|
|
)
|
|
BEGIN
|
|
SELECT @sourceOfFund = detailTitle
|
|
FROM STATICDATAVALUE(NOLOCK)
|
|
WHERE VALUEID = @sourceOfFund
|
|
AND TYPEID = 3900
|
|
END
|
|
|
|
SELECT @pcountryId = cm.countryId
|
|
,@RelWithSenderOthers = ISNULL(relationOther, otherRelationDesc)
|
|
FROM receiverInformation RI(NOLOCK)
|
|
INNER JOIN countryMaster CM(NOLOCK) ON CM.countryName = RI.country
|
|
WHERE RI.receiverId = @ReceiverId
|
|
|
|
IF EXISTS (
|
|
SELECT *
|
|
FROM STATICDATAVALUE(NOLOCK)
|
|
WHERE VALUEID = @RelWithSender
|
|
AND TYPEID = 2100
|
|
AND isActive = 'Y'
|
|
)
|
|
BEGIN
|
|
IF (@RelWithSender = '11339')
|
|
BEGIN
|
|
SET @RelWithSender = 'Other (please specify) :' + ISNULL(@RelWithSenderOthers, '');
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT @RelWithSender = detailTitle
|
|
FROM STATICDATAVALUE(NOLOCK)
|
|
WHERE VALUEID = @RelWithSender
|
|
AND TYPEID = 2100
|
|
END
|
|
END
|
|
|
|
DECLARE @isRealTime BIT = 0 , @TRANSFER_MODE varchar(10)='';
|
|
|
|
|
|
SELECT @payOutPartnerId = AGENTID
|
|
,@isRealTime = isRealTime
|
|
FROM TblPartnerwiseCountry(NOLOCK)
|
|
WHERE CountryId = @pCountryId
|
|
AND IsActive = 1
|
|
AND ISNULL(PaymentMethod, @deliveryMethodId) = @deliveryMethodId
|
|
AND IsMobileEnabled = 1
|
|
|
|
IF @payOutPartnerId IS NULL
|
|
BEGIN
|
|
EXEC proc_errorHandler 3
|
|
,'Oops, something went wrong! Please perform the transaction again. Route is missing.'
|
|
,NULL
|
|
|
|
RETURN;
|
|
END
|
|
|
|
IF EXISTS (
|
|
SELECT *
|
|
FROM API_BANK_LIST_MASTER(NOLOCK)
|
|
WHERE MASTER_BANK_ID = @pBankId
|
|
AND IS_ACTIVE = '1'
|
|
)
|
|
BEGIN
|
|
SELECT @pBankId = AB.BANK_ID ,@TRANSFER_MODE= ab.TRANSFER_MODE
|
|
FROM API_BANK_LIST AB(NOLOCK)
|
|
INNER JOIN API_BANK_LIST_MASTER ABM(NOLOCK) ON AB.JME_BANK_CODE = ABM.JME_BANK_CODE
|
|
WHERE MASTER_BANK_ID = @pBankId
|
|
AND ABM.IS_ACTIVE = '1'
|
|
AND AB.API_PARTNER_ID = @payOutPartnerId
|
|
END
|
|
|
|
IF @flag = 'SEND'
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT TOP 1 'X'
|
|
FROM dbo.customerMaster(NOLOCK)
|
|
WHERE username = @user
|
|
AND mobileApprovedDate IS NOT NULL
|
|
)
|
|
BEGIN
|
|
EXEC proc_errorHandler 19
|
|
,'You are not authorized to perform transaction, please contact JME Support!'
|
|
,NULL;
|
|
|
|
RETURN;
|
|
END
|
|
|
|
DECLARE @remittanceAllowed int,@idExpiryDate DATETIME
|
|
|
|
SELECT @remittanceAllowed = remittanceAllowed,@idExpiryDate=idExpiryDate FROM customerMaster(NOLOCK)
|
|
WHERE CUSTOMERID = @SenderId
|
|
|
|
IF ISNULL(@remittanceAllowed, 0) = '0'
|
|
BEGIN
|
|
SELECT @MSG = 'You are not authorized to perform transaction, please contact JME Support!'
|
|
|
|
EXEC proc_errorHandler 19
|
|
,@MSG
|
|
,NULL
|
|
|
|
RETURN
|
|
END
|
|
|
|
IF @idExpiryDate < GETDATE()
|
|
BEGIN
|
|
SELECT @MSG = 'Your ID with us has expired. Please upload new ID details in “Renew ID” or contact JME support.'
|
|
EXEC proc_errorHandler 22
|
|
,@MSG
|
|
,NULL
|
|
RETURN;
|
|
END
|
|
DECLARE @kycStatus INT
|
|
|
|
SELECT @kycStatus = kycStatus
|
|
FROM TBL_CUSTOMER_KYC(NOLOCK)
|
|
WHERE CUSTOMERID = @senderId
|
|
AND ISDELETED = 0
|
|
--AND kycStatus=11044
|
|
ORDER BY KYC_DATE
|
|
|
|
IF ISNULL(@kycStatus, 0) <> 11044
|
|
BEGIN
|
|
IF @kycStatus IS NOT NULL
|
|
SELECT @MSG = 'KYC for selected customer is not completed, it is in status:' + detailTitle
|
|
FROM staticDataValue(NOLOCK)
|
|
WHERE valueId = @kycStatus
|
|
ELSE
|
|
SELECT @MSG = 'Please complete KYC status first'
|
|
|
|
EXEC proc_errorHandler 2
|
|
,@MSG
|
|
,NULL;
|
|
|
|
RETURN
|
|
END
|
|
|
|
IF @paymentType IS NULL
|
|
SET @paymentType = 'WALLET'
|
|
|
|
DECLARE @sCurrCostRate FLOAT
|
|
,@sCurrHoMargin FLOAT
|
|
,@pCurrCostRate FLOAT
|
|
,@customerRate MONEY
|
|
,@agentCrossSettRate FLOAT
|
|
,@iServiceCharge MONEY
|
|
,@iTAmt MONEY
|
|
,@iPAmt MONEY
|
|
,@place INT
|
|
,@currDecimal INT
|
|
,@agentAvlLimit MONEY
|
|
,@serviceCharge MONEY
|
|
,@sCountry VARCHAR(50) = 'Japan'
|
|
,@sAgent BIGINT
|
|
,@sAgentName VARCHAR(100)
|
|
,@sBranch INT
|
|
,@sBranchName VARCHAR(100)
|
|
,@sSuperAgent INT
|
|
,@sSuperAgentName VARCHAR(100)
|
|
,@senderName VARCHAR(100)
|
|
,@sIdNo VARCHAR(50)
|
|
,@sIdType VARCHAR(50)
|
|
,@sMobile VARCHAR(15)
|
|
,@pAgent BIGINT
|
|
,@pSuperAgent BIGINT
|
|
,@pSuperAgentName VARCHAR(100)
|
|
,@pAgentName VARCHAR(100)
|
|
,@controlNoEncrypted VARCHAR(30)
|
|
,@tempCompId BIGINT
|
|
,@pBranch INT
|
|
,@pBranchName VARCHAR(100)
|
|
,@pCountry VARCHAR(100)
|
|
,@RcreatedFrom VARCHAR(5)
|
|
,@ROtpVerified BIT
|
|
|
|
SELECT @sCountryId = 113
|
|
,@sBranch = 394395
|
|
|
|
SELECT @SenderId = customerId ,
|
|
@createdFrom = createdFrom
|
|
FROM customerMaster(NOLOCK)
|
|
WHERE USERNAME = @User
|
|
|
|
|
|
IF NOT EXISTS(SELECT 1 FROM
|
|
(SELECT TOP 1 customerId FROM TRANSENDERS TS (NOLOCK)
|
|
inner join remittran (nolock) rt on rt.id = ts.tranId
|
|
WHERE customerId = @SenderId AND rt.tranStatus <> 'CANCEL' AND RT.tranType='M'
|
|
UNION ALL
|
|
SELECT TOP 1 customerId FROM TRANSENDERSTEMP TT (NOLOCK)
|
|
inner join remittrantemp (nolock) rt on rt.id = tt.tranId
|
|
WHERE customerId = @SenderId AND rt.tranStatus <> 'CANCEL' AND rt.tranType='M'
|
|
) a where customerId = @SenderId
|
|
)
|
|
BEGIN
|
|
SET @isFirstTran = 'Y'
|
|
END
|
|
|
|
|
|
SELECT @sAgent = sAgent
|
|
,@sAgentName = sAgentName
|
|
,@sBranch = sBranch
|
|
,@sBranchName = sBranchName
|
|
,@sSuperAgent = sSuperAgent
|
|
,@sSuperAgentName = sSuperAgentName
|
|
FROM dbo.FNAGetBranchFullDetails(@sBranch)
|
|
|
|
SELECT @pCountry = COUNTRYNAME
|
|
FROM COUNTRYMASTER(NOLOCK)
|
|
WHERE COUNTRYID = @pCountryId
|
|
|
|
SELECT TOP 1 @pAgent = AM.agentId
|
|
--,@pCountryId = AM.agentCountryId
|
|
FROM agentMaster AM(NOLOCK)
|
|
WHERE AM.parentId = @payOutPartnerId
|
|
AND agentType = 2903
|
|
AND AM.isSettlingAgent = 'Y'
|
|
AND AM.isApiPartner = 1
|
|
|
|
SELECT @pSuperAgentName = sSuperAgentName
|
|
,@pSuperAgent = sSuperAgent
|
|
,@pAgent = sAgent
|
|
,@pAgentName = sAgentName
|
|
FROM dbo.FNAGetBranchFullDetails(@pAgent)
|
|
|
|
SELECT @pBranch = @pAgent
|
|
,@pBranchName = @pAgentName
|
|
|
|
DECLARE @StateId INT
|
|
,@DistrictId INT
|
|
|
|
IF @receiverId IS NOT NULL
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT '1'
|
|
FROM dbo.receiverInformation(NOLOCK)
|
|
WHERE receiverId = @ReceiverId
|
|
)
|
|
BEGIN
|
|
EXEC proc_errorHandler 4
|
|
,'Receiver Data Not Match !'
|
|
,NULL;
|
|
|
|
RETURN;
|
|
END
|
|
|
|
SELECT TOP 1 @receiverName = ISNULL(firstName, '') + ISNULL(' ' + middleName, '') + ISNULL(' ' + lastName1, '') + ISNULL(' ' + lastName2, '')
|
|
,@StateId = AI.STATE_ID
|
|
,@DistrictId = AC.CITY_ID
|
|
,@RcreatedFrom = ISNULL(ri.createdFrom, 'C')
|
|
,@ROtpVerified = ISNULL(ri.isOTPVerified, 0)
|
|
FROM dbo.receiverInformation RI(NOLOCK)
|
|
LEFT JOIN API_STATE_LIST AI(NOLOCK) ON AI.STATE_NAME = RI.STATE
|
|
AND AI.API_PARTNER_ID = @payOutPartnerId
|
|
LEFT JOIN API_CITY_LIST AC(NOLOCK) ON AC.STATE_ID = AI.STATE_ID
|
|
AND AC.CITY_NAME = RI.DISTRICT
|
|
WHERE receiverId = @receiverId
|
|
|
|
IF(@RcreatedFrom='M' AND @ROtpVerified=0 )
|
|
BEGIN
|
|
EXEC proc_errorHandler 4
|
|
,'Receiver Data Not Match !'
|
|
,NULL;
|
|
RETURN;
|
|
END
|
|
END
|
|
ELSE
|
|
SET @receiverName = ISNULL(@rFirstName, '') + ISNULL(' ' + @rMiddleName, '') + ISNULL(' ' + @rLastName, '')
|
|
|
|
IF @rFirstName IS NULL
|
|
AND @receiverId IS NULL
|
|
BEGIN
|
|
EXEC proc_errorHandler 5
|
|
,'Receiver name cannot be empty'
|
|
,NULL;
|
|
|
|
RETURN;
|
|
END
|
|
|
|
IF ISNULL(@exRate, 0) = 0
|
|
BEGIN
|
|
EXEC proc_errorHandler 6
|
|
,'Transaction cannot be proceed.Exchange Rate not defined'
|
|
,NULL
|
|
|
|
RETURN
|
|
END
|
|
|
|
IF @pAgent IS NULL
|
|
BEGIN
|
|
EXEC proc_errorHandler 3
|
|
,'Oops, something went wrong.Please perform the transaction again'
|
|
,NULL
|
|
|
|
RETURN;
|
|
END
|
|
|
|
DECLARE @OccupationId INT
|
|
,@sNaCountryId INT
|
|
,@visaStatusId INT
|
|
DECLARE @visaStatusText VARCHAR(200)
|
|
|
|
SELECT TOP 1 @agentAvlLimit = dbo.FNAGetCustomerAvailableBalance(@SenderId)
|
|
,@senderName = fullName
|
|
,@sIdNo = idNumber
|
|
,@sIdType = idType
|
|
,@sMobile = mobile
|
|
,@customerType = customerType
|
|
,@OccupationId = OCCUPATION
|
|
,@sNaCountryId = NATIVECOUNTRY
|
|
,@visaStatusId = VISASTATUS
|
|
,@visaStatusText = SV.detailTitle
|
|
,@sAdd2 = ISNULL(additionalAddress, '')
|
|
FROM customerMaster CM(NOLOCK)
|
|
LEFT JOIN STATICDATAVALUE SV(NOLOCK) ON SV.valueId = CM.visaStatus
|
|
WHERE username = @User
|
|
AND customerId = @SenderId
|
|
|
|
IF ISNULL(@paymentType, '') NOT IN ('wallet')
|
|
BEGIN
|
|
EXEC proc_errorHandler 7
|
|
,'Invalid payment method.Please perform the transaction again!'
|
|
,NULL;
|
|
|
|
RETURN;
|
|
END
|
|
|
|
IF @user IN ('demo.gme@gmeremit.com')
|
|
BEGIN
|
|
EXEC proc_errorHandler 8
|
|
,'You can not send money through test GME acocunt :('
|
|
,NULL;
|
|
|
|
RETURN;
|
|
END
|
|
|
|
IF ISNULL(@collAmt, 0) = 0
|
|
BEGIN
|
|
EXEC proc_errorHandler 9
|
|
,'Collection Amount is missing. Cannot send transaction'
|
|
,NULL;
|
|
|
|
RETURN;
|
|
END;
|
|
|
|
SET @controlNo = '21' + LEFT(CAST(ABS(CHECKSUM(NEWID())) AS VARCHAR(10)) + '0000000000', 7)
|
|
|
|
IF EXISTS (
|
|
SELECT TOP 1 'X'
|
|
FROM controlNoList WITH (NOLOCK)
|
|
WHERE controlNo = @controlNo
|
|
)
|
|
BEGIN
|
|
SET @controlNo = '21' + LEFT(CAST(ABS(CHECKSUM(NEWID())) AS VARCHAR(10)) + '0000000000', 7)
|
|
|
|
--SELECT @controlNoEncrypted = dbo.FNAEncryptString(@controlNo);
|
|
IF EXISTS (
|
|
SELECT TOP 1 'X'
|
|
FROM controlNoList WITH (NOLOCK)
|
|
WHERE controlNo = @controlNo
|
|
)
|
|
BEGIN
|
|
EXEC proc_errorHandler 10
|
|
,'Technical error occurred. Please try again'
|
|
,NULL;
|
|
|
|
RETURN;
|
|
END
|
|
END;
|
|
|
|
SELECT @controlNoEncrypted = dbo.FNAEncryptString(@controlNo);
|
|
|
|
IF @deliveryMethodId = 2
|
|
AND @pCountryId <> '151'
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT TOP 1 'A'
|
|
FROM api_bank_list(NOLOCK)
|
|
WHERE BANK_ID = @pBankId
|
|
AND PAYMENT_TYPE_ID IN (
|
|
0
|
|
,2
|
|
)
|
|
AND IS_ACTIVE = 1
|
|
)
|
|
BEGIN
|
|
EXEC proc_errorHandler 11
|
|
,'Invalid bank selected'
|
|
,NULL
|
|
|
|
RETURN
|
|
END
|
|
|
|
IF @raccountNo IS NULL
|
|
BEGIN
|
|
EXEC proc_errorHandler 12
|
|
,'Account number cannot be blank'
|
|
,NULL
|
|
|
|
RETURN
|
|
END
|
|
END;
|
|
|
|
--4. Get Exchange Rate Details------------------------------------------------------------------------------------------------------------------
|
|
DECLARE @pCurrHoMargin FLOAT
|
|
|
|
SELECT @customerRate = customerRate
|
|
,@sCurrCostRate = sCurrCostRate
|
|
,@sCurrHoMargin = sCurrHoMargin
|
|
,@pCurrCostRate = pCurrCostRate
|
|
,@agentCrossSettRate = agentCrossSettRate
|
|
,@serviceCharge = serviceCharge
|
|
,@iPAmt = pAmt
|
|
,@schemeId = schemeId
|
|
,@pCurrHoMargin = pCurrHoMargin
|
|
,@schemeCount = CAST( sharingValue AS INT)
|
|
,@schemeCodeId= schemeId
|
|
FROM exRateCalcHistory(NOLOCK)
|
|
WHERE FOREX_SESSION_ID = @forexSessionId
|
|
AND [USER_ID] = @user
|
|
|
|
DECLARE @pAgentCommCurrency VARCHAR(3)
|
|
,@pAgentComm MONEY
|
|
|
|
SELECT @pAgentCommCurrency = DBO.FNAGetPayCommCurrency(@sSuperAgent, @sAgent, @sBranch, @SCOUNTRYID, @pSuperAgent, @pBranch, @pCountryId)
|
|
|
|
IF (LEN(ISNULL(@TRANSFER_MODE, ''))<1
|
|
AND @pCountryId = '203'
|
|
AND @pAgent = '394133'
|
|
AND @deliveryMethodId = '2'
|
|
) -- DONGA Bank Deposit not real time
|
|
BEGIN
|
|
SET @pAgentComm = (@payoutAmt * 0.17) / 100;
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT @pAgentComm = amount
|
|
FROM dbo.FNAGetPayComm(@sAgent, @sCountryId, NULL, NULL, @pCountryId, NULL, @pAgent, @pAgentCommCurrency, @deliveryMethodId, @collAmt, @payoutAmt, @serviceCharge, @transferAmt, NULL)
|
|
END
|
|
|
|
IF @customerRate IS NULL
|
|
BEGIN
|
|
EXEC proc_errorHandler 6
|
|
,'Transaction cannot be proceed. Exchange Rate not defined'
|
|
,NULL
|
|
|
|
RETURN
|
|
END
|
|
|
|
-- Customer Loyalty
|
|
EXEC PROC_Customer_LoyaltyV2 @flag = 'check-eligible-v2'
|
|
,@isEligible = @isEligible OUT
|
|
,@referralCode = @introducer
|
|
,@tranCount = @trancount OUT
|
|
,@schemeCount = @schemeCount OUT
|
|
,@customerId = @senderId
|
|
,@createdFrom = 'M'
|
|
|
|
print '@isEligible.Send' + @isEligible;
|
|
print '@@trancount.Send' + CAST(@trancount AS VARCHAR);
|
|
IF @isEligible = 'Y'
|
|
BEGIN
|
|
SET @iServiceCharge = 0
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
--Get Service Charge----------------------------------------------------------------------------------------------------------------------
|
|
DECLARE @iServiceChargeTemp MONEY
|
|
SELECT @iServiceChargeTemp = ISNULL(amount, - 1)
|
|
FROM [dbo].FNAGetServiceCharge(@sCountryId, @sSuperAgent, @sAgent, @sBranch, @pCountryId, @pSuperAgent, @pAgent, @pBranch, @deliveryMethodId, @collAmt, @collCurr)
|
|
|
|
--SELECT @iServiceCharge = ISNULL(@iServiceChargeTemp,0) - ISNULL(@discountedFee,0)
|
|
|
|
IF @iServiceCharge < 0 -- @iServiceCharge = - 1
|
|
BEGIN
|
|
EXEC proc_errorHandler 13
|
|
,'Transaction cannot be proceed. Service Charge is not defined'
|
|
,NULL
|
|
|
|
RETURN
|
|
END
|
|
END
|
|
print @iServiceCharge;
|
|
print @serviceCharge;
|
|
IF ISNULL(@iServiceCharge, 0) <> ISNULL(@serviceCharge, 1)
|
|
BEGIN
|
|
EXEC proc_errorHandler 14
|
|
,'Transaction cannot be proceed. Amount detail not match'
|
|
,NULL
|
|
|
|
RETURN
|
|
END
|
|
|
|
--End Service Charge-------------------------------------------------------------------------------------------------------------------------------------
|
|
--DECLARE @iMsg VARCHAR(MAX)
|
|
IF ISNULL(@exRate, 0) <> ISNULL(@customerRate, 1)
|
|
BEGIN
|
|
--SET @iMsg = 'Amount detail not match. Please re-calculate the amount again' + CAST(isnull(@exRate,0) AS VARCHAR) + ' : ' + CAST(isnull(@customerRate,1) AS VARCHAR)
|
|
EXEC proc_errorHandler 15
|
|
,'Amount detail not match. Please re-calculate the amount again'
|
|
,NULL
|
|
|
|
RETURN
|
|
END
|
|
|
|
IF dbo.FNA_GET_AVAILABLE_BALANCE_POINTS(@senderId) < ISNULL(@discountedFee, 0)
|
|
BEGIN
|
|
EXEC proc_errorHandler 1
|
|
,'You do not have sufficient points for redeem!'
|
|
,NULL
|
|
RETURN;
|
|
END
|
|
|
|
SELECT @iTAmt = @collAmt - @iServiceCharge + ISNULL(@discountedFee, 0)
|
|
|
|
SELECT TOP 1 @place = place
|
|
,@currDecimal = currDecimal
|
|
FROM currencyPayoutRound(NOLOCK)
|
|
WHERE ISNULL(isDeleted, 'N') = 'N'
|
|
AND currency = @payoutCurr
|
|
AND tranType IS NULL;
|
|
|
|
SET @currDecimal = ISNULL(@currDecimal, 0)
|
|
SET @place = ISNULL(@place, 0)
|
|
SET @iPAmt = @iTAmt * @CustomerRate
|
|
|
|
IF @payoutAmt - @iPAmt <= 1
|
|
SET @iPAmt = @payoutAmt
|
|
|
|
----## WHILE CALCULATING FROM PAYOUT AMOUNT CONSIDARING 10 VND
|
|
IF ISNULL(@iPAmt, 0) <> ISNULL(@payoutAmt, 1)
|
|
BEGIN
|
|
--SET @Msg = 'Amount detail not match. Please re-calculate the amount again.' + CAST(@iPAmt AS VARCHAR) + ' - ' + CAST(@payoutAmt AS VARCHAR)
|
|
EXEC proc_errorHandler 15
|
|
,'Amount detail not match. Please re-calculate the amount again.'
|
|
,NULL
|
|
|
|
RETURN
|
|
END
|
|
|
|
----OFAC Checking
|
|
DECLARE @receiverOfacRes VARCHAR(MAX)
|
|
,@ofacRes VARCHAR(MAX)
|
|
,@ofacReason VARCHAR(200)
|
|
|
|
EXEC proc_ofacTracker @flag = 't'
|
|
,@name = @senderName
|
|
,@Result = @ofacRes OUTPUT
|
|
|
|
EXEC proc_ofacTracker @flag = 't'
|
|
,@name = @receiverName
|
|
,@Result = @receiverOfacRes OUTPUT
|
|
|
|
DECLARE @result VARCHAR(MAX)
|
|
|
|
IF ISNULL(@ofacRes, '') <> ''
|
|
BEGIN
|
|
SET @ofacReason = 'Matched by sender name'
|
|
END
|
|
|
|
IF ISNULL(@receiverOfacRes, '') <> ''
|
|
BEGIN
|
|
SET @ofacRes = ISNULL(@ofacRes + ',' + @receiverOfacRes, '' + @receiverOfacRes)
|
|
SET @ofacReason = 'Matched by receiver name'
|
|
END
|
|
|
|
IF ISNULL(@ofacRes, '') <> ''
|
|
AND ISNULL(@receiverOfacRes, '') <> ''
|
|
BEGIN
|
|
SET @ofacReason = 'Matched by both sender name and receiver name'
|
|
END
|
|
|
|
--Ofac Checking End
|
|
DECLARE @agentRefId VARCHAR(50) = NEWID()
|
|
|
|
----Compliance Checking 1-> Block, 2-> Hold, 3-> Questionnaire
|
|
CREATE TABLE #TBL_COMPLIANCE_RESULT (
|
|
ERROR_CODE INT
|
|
,MSG VARCHAR(2000)
|
|
,RULE_ID INT
|
|
,SHORT_MSG VARCHAR(1000)
|
|
,[TYPE] VARCHAR(10)
|
|
,IS_D0C_REQUIRED BIT
|
|
)
|
|
|
|
INSERT INTO #TBL_COMPLIANCE_RESULT (
|
|
ERROR_CODE
|
|
,MSG
|
|
,RULE_ID
|
|
,SHORT_MSG
|
|
,[TYPE]
|
|
,IS_D0C_REQUIRED
|
|
)
|
|
EXEC [PROC_COMPLIANCE_CHECKING_NEW] @flag = 'core'
|
|
,@user = @user
|
|
,@sIdType = @sIdType
|
|
,@sIdNo = @sIdNo
|
|
,@receiverName = @receiverName
|
|
,@amount = @iTAmt
|
|
,@customerId = @senderId
|
|
,@pCountryId = @pCountryId
|
|
,@deliveryMethod = @deliveryMethodId
|
|
,@professionId = @OccupationId
|
|
,@receiverMobile = @rMobileNo
|
|
,@accountNo = @raccountNo
|
|
,@receiverId = @receiverId
|
|
,@sNaCountryId = @sNaCountryId
|
|
,@visaStatus = @visaStatusId
|
|
|
|
IF EXISTS (
|
|
SELECT *
|
|
FROM #TBL_COMPLIANCE_RESULT
|
|
WHERE ERROR_CODE <> 0
|
|
)
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT *
|
|
FROM #TBL_COMPLIANCE_RESULT
|
|
WHERE ERROR_CODE IN (1)
|
|
) --transaction blocked
|
|
BEGIN
|
|
INSERT INTO ComplianceLog (
|
|
senderName
|
|
,senderCountry
|
|
,senderIdType
|
|
,senderIdNumber
|
|
,senderMobile
|
|
,receiverName
|
|
,receiverCountry
|
|
,payOutAmt
|
|
,complianceId
|
|
,complianceReason
|
|
,complainceDetailMessage
|
|
,createdBy
|
|
,createdDate
|
|
,agentRefId
|
|
,isDocumentRequired
|
|
)
|
|
SELECT @senderName
|
|
,@sCountry
|
|
,@sIdType
|
|
,@sIdNo
|
|
,@sMobile
|
|
,@receiverName
|
|
,@pCountry
|
|
,@collAmt
|
|
,RULE_ID
|
|
,SHORT_MSG
|
|
,MSG
|
|
,@user
|
|
,GETDATE()
|
|
,@agentRefId
|
|
,IS_D0C_REQUIRED
|
|
FROM #TBL_COMPLIANCE_RESULT
|
|
|
|
SELECT @MSG = MSG
|
|
FROM #TBL_COMPLIANCE_RESULT
|
|
WHERE ERROR_CODE IN (1)
|
|
--EXEC proc_errorHandler 1, @MSG, NULL
|
|
END
|
|
|
|
IF EXISTS (
|
|
SELECT *
|
|
FROM #TBL_COMPLIANCE_RESULT
|
|
WHERE ERROR_CODE IN (
|
|
2
|
|
,3
|
|
)
|
|
) --transaction hold/questionnaire
|
|
BEGIN
|
|
DELETE
|
|
FROM remitTranComplianceTemp
|
|
WHERE agentRefId = @agentRefId
|
|
|
|
INSERT remitTranComplianceTemp (
|
|
csDetailTranId
|
|
,matchTranId
|
|
,agentRefId
|
|
)
|
|
SELECT RULE_ID
|
|
,NULL
|
|
,@agentRefId
|
|
FROM #TBL_COMPLIANCE_RESULT
|
|
WHERE ERROR_CODE IN (
|
|
2
|
|
,3
|
|
)
|
|
ORDER BY ISNULL(IS_D0C_REQUIRED, 0) DESC
|
|
END
|
|
|
|
INSERT INTO ComplianceLog (
|
|
senderName
|
|
,senderCountry
|
|
,senderIdType
|
|
,senderIdNumber
|
|
,senderMobile
|
|
,receiverName
|
|
,receiverCountry
|
|
,payOutAmt
|
|
,complianceId
|
|
,complianceReason
|
|
,complainceDetailMessage
|
|
,createdBy
|
|
,createdDate
|
|
,agentRefId
|
|
,isDocumentRequired
|
|
)
|
|
SELECT @senderName
|
|
,@sCountry
|
|
,@sIdType
|
|
,@sIdNo
|
|
,@sMobile
|
|
,@receiverName
|
|
,@pCountry
|
|
,@collAmt
|
|
,RULE_ID
|
|
,SHORT_MSG
|
|
,MSG
|
|
,@user
|
|
,GETDATE()
|
|
,@agentRefId
|
|
,IS_D0C_REQUIRED
|
|
FROM #TBL_COMPLIANCE_RESULT
|
|
END
|
|
|
|
--checking for visa status questionnaire
|
|
IF EXISTS (
|
|
SELECT *
|
|
FROM VW_VISA_STATUS_QUESTIONNAIRE
|
|
WHERE VISA_ID = @visaStatusId
|
|
)
|
|
BEGIN
|
|
INSERT INTO ComplianceLog (
|
|
senderName
|
|
,senderCountry
|
|
,senderIdType
|
|
,senderIdNumber
|
|
,senderMobile
|
|
,receiverName
|
|
,receiverCountry
|
|
,payOutAmt
|
|
,complianceId
|
|
,complianceReason
|
|
,complainceDetailMessage
|
|
,createdBy
|
|
,createdDate
|
|
,agentRefId
|
|
,isDocumentRequired
|
|
)
|
|
SELECT @senderName
|
|
,@sCountry
|
|
,@sIdType
|
|
,@sIdNo
|
|
,@sMobile
|
|
,@receiverName
|
|
,@pCountry
|
|
,@collAmt
|
|
,0
|
|
,'Compliance Hold/Questionnaire due to Visa Status: ' + @visaStatusText
|
|
,'Compliance Hold/Questionnaire due to Visa Status: ' + @visaStatusText
|
|
,@user
|
|
,GETDATE()
|
|
,@agentRefId
|
|
,0
|
|
|
|
INSERT remitTranComplianceTemp (
|
|
csDetailTranId
|
|
,matchTranId
|
|
,agentRefId
|
|
,reason
|
|
)
|
|
SELECT 0
|
|
,NULL
|
|
,@agentRefId
|
|
,'Questionnaire require due to Visa Status'
|
|
END
|
|
|
|
--**********Customer Per Day Limit Checking**********
|
|
DECLARE @remitTranTemp TABLE (
|
|
tranId BIGINT
|
|
,controlNo VARCHAR(20)
|
|
,cAmt MONEY
|
|
,receiverName VARCHAR(200)
|
|
,receiverIdType VARCHAR(100)
|
|
,receiverIdNumber VARCHAR(50)
|
|
,dot DATETIME
|
|
);
|
|
|
|
INSERT INTO @remitTranTemp (
|
|
tranId
|
|
,controlNo
|
|
,cAmt
|
|
,receiverName
|
|
,receiverIdType
|
|
,receiverIdNumber
|
|
,dot
|
|
)
|
|
SELECT TOP 10 rt.id
|
|
,rt.controlNo
|
|
,rt.cAmt
|
|
,rt.receiverName
|
|
,rec.idType
|
|
,rec.idNumber
|
|
,rt.createdDate
|
|
FROM vwRemitTran rt WITH (NOLOCK)
|
|
INNER JOIN vwTranSenders sen WITH (NOLOCK) ON rt.id = sen.tranId
|
|
INNER JOIN vwTranReceivers rec WITH (NOLOCK) ON rt.id = rec.tranId
|
|
WHERE sen.customerId = @SenderId
|
|
AND (
|
|
rt.approvedDate BETWEEN CONVERT(VARCHAR, GETDATE(), 101)
|
|
AND CONVERT(VARCHAR, GETDATE(), 101) + ' 23:59:59'
|
|
OR (
|
|
approvedBy IS NULL
|
|
AND cancelApprovedBy IS NULL
|
|
)
|
|
)
|
|
ORDER BY rt.createdDate DESC
|
|
|
|
IF EXISTS (
|
|
SELECT TOP 1 'X'
|
|
FROM @remitTranTemp
|
|
WHERE cAmt = @collAmt
|
|
AND (receiverName = @receiverName)
|
|
AND DATEDIFF(MI, dot, GETDATE()) <= 2
|
|
)
|
|
BEGIN
|
|
EXEC proc_errorHandler 16
|
|
,'Similar transaction found. Please perform the transaction after 2 minutes.'
|
|
,NULL;
|
|
Rollback Transaction ;
|
|
RETURN;
|
|
END;
|
|
|
|
DECLARE @countryRisk INT
|
|
,@OccupationRisk INT
|
|
,@compFinalRes VARCHAR(5)
|
|
-- #########country and occupation risk point
|
|
DECLARE @deliveryMethod VARCHAR(30)
|
|
,@pBankName VARCHAR(100)
|
|
,@pBankBranchName VARCHAR(100)
|
|
,@pBankRowId BIGINT
|
|
|
|
SELECT TOP 1 @pCountry = COUNTRYNAME
|
|
FROM countryMaster(NOLOCK)
|
|
WHERE countryId = @pCountryId
|
|
|
|
SELECT TOP 1 @deliveryMethod = typeTitle
|
|
FROM serviceTypeMaster(NOLOCK)
|
|
WHERE serviceTypeId = @deliveryMethodId
|
|
|
|
SELECT TOP 1 @pBankName = bank_name
|
|
,@pBankRowId = bank_id
|
|
FROM api_bank_list
|
|
WHERE bank_id = @pBankId
|
|
AND is_Active = 1
|
|
|
|
SELECT TOP 1 @pBankBranchName = branch_name + isnull(BRANCH_CODE1,'')
|
|
FROM api_bank_branch_list
|
|
WHERE bank_id = @pBankRowId
|
|
AND branch_id = @pBranchId
|
|
AND is_Active = 1
|
|
|
|
DECLARE @VNo VARCHAR(20);
|
|
|
|
IF @pCountry = 'Nepal'
|
|
AND @deliveryMethod = 'CASH PAYMENT'
|
|
BEGIN
|
|
SELECT @pSuperAgent = NULL
|
|
,@pSuperAgentName = NULL
|
|
,@pAgent = NULL
|
|
,@pAgentName = NULL
|
|
|
|
SELECT @pBankName = '[ANY WHERE]'
|
|
END
|
|
|
|
DECLARE @PayerId INT = NULL
|
|
|
|
--GET PAYER DETAILS IN CASE OF TF
|
|
IF @payOutPartnerId = 394130
|
|
AND @deliveryMethod = 'BANK DEPOSIT'
|
|
BEGIN
|
|
SELECT @PayerId = PayerId
|
|
FROM BankPayerSetup(NOLOCK)
|
|
WHERE BankId = @pBankId
|
|
AND IsDefault = 1
|
|
|
|
IF @PayerId IS NULL
|
|
BEGIN
|
|
EXEC proc_errorHandler 17
|
|
,'No default payer mapped for current bank, please contact JME Support!'
|
|
,NULL;
|
|
Rollback Transaction ;
|
|
RETURN;
|
|
END
|
|
END
|
|
|
|
BEGIN TRANSACTION;
|
|
|
|
IF @PurposeOfRemittanceOther IS NOT NULL
|
|
BEGIN
|
|
SET @PurposeOfRemittanceOther = 'Other (please specify) :' + @PurposeOfRemittanceOther
|
|
END
|
|
|
|
IF @SourceOfFundOther IS NOT NULL
|
|
BEGIN
|
|
SET @SourceOfFundOther = 'Other (please specify) :' + @SourceOfFundOther
|
|
END
|
|
|
|
IF (@pBranchId IS NULL OR @pBranchId='0')
|
|
BEGIN
|
|
select @pBranchId = bankLocation FROM receiverInformation WHERE receiverId = @ReceiverId
|
|
|
|
IF(ISNULL(@pBranchId,0)=0 and ( @payOutPartnerId='394414' and @deliveryMethod = 'BANK DEPOSIT'))
|
|
BEGIN
|
|
|
|
SET @msg = 'Please update the Routing Branch of receiver [ ' + @receiverName + ' ] before performing transaction!';
|
|
|
|
EXEC proc_errorHandler 21
|
|
,@msg
|
|
,NULL;
|
|
Rollback Transaction ;
|
|
RETURN;
|
|
END
|
|
|
|
END
|
|
|
|
|
|
INSERT INTO remitTranTemp (
|
|
controlNo
|
|
,sCurrCostRate
|
|
,sCurrHoMargin
|
|
,pCurrCostRate
|
|
,pCurrHoMargin
|
|
,agentCrossSettRate
|
|
,customerRate
|
|
,serviceCharge
|
|
,handlingFee
|
|
,pAgentComm
|
|
,pAgentCommCurrency
|
|
,promotionCode
|
|
,sSuperAgent
|
|
,sSuperAgentName
|
|
,sAgent
|
|
,sAgentName
|
|
,sBranch
|
|
,sBranchName
|
|
,sCountry
|
|
,pSuperAgent
|
|
,pSuperAgentName
|
|
,pAgent
|
|
,pAgentName
|
|
,pCountry
|
|
,paymentMethod
|
|
,pBank
|
|
,pBankName
|
|
,pBankBranch
|
|
,pBankBranchName
|
|
,accountNo
|
|
,collCurr
|
|
,tAmt
|
|
,cAmt
|
|
,pAmt
|
|
,payoutCurr
|
|
,relWithSender
|
|
,purposeOfRemit
|
|
,sourceOfFund
|
|
,tranStatus
|
|
,payStatus
|
|
,createdDate
|
|
,createdDateLocal
|
|
,createdBy
|
|
,tranType
|
|
,senderName
|
|
,receiverName
|
|
,isOnlineTxn
|
|
,schemeId
|
|
,pState
|
|
,pDistrict
|
|
,sRouteId
|
|
,schemePremium
|
|
,collMode
|
|
,PAYERID
|
|
,routedBy
|
|
,rewardPoints
|
|
,rewardType
|
|
,isBonusUpdated
|
|
)
|
|
SELECT TOP 1 @controlNoEncrypted
|
|
,@sCurrCostRate
|
|
,@sCurrHoMargin
|
|
,@pCurrCostRate
|
|
,@pCurrHoMargin
|
|
,@agentCrossSettRate
|
|
,@customerRate
|
|
,@serviceCharge
|
|
,ISNULL(@scDiscount, 0)
|
|
,@pAgentComm
|
|
,@pAgentCommCurrency
|
|
,NULL
|
|
,@sSuperAgent
|
|
,@sSuperAgentName
|
|
,@sAgent
|
|
,@sAgentName
|
|
,@sBranch
|
|
,@sBranchName
|
|
,@sCountry
|
|
,@pSuperAgent
|
|
,@pSuperAgentName
|
|
,@pAgent
|
|
,@pAgentName
|
|
,@pCountry
|
|
,@deliveryMethod
|
|
,@pBankId
|
|
,@pBankName
|
|
,@pBranchId
|
|
,@pBankBranchName
|
|
,@raccountNo
|
|
,@collCurr
|
|
,@iTAmt
|
|
,@collAmt
|
|
,@payoutAmt
|
|
,@payoutCurr
|
|
,@RelWithSender
|
|
,ISNULL(@PurposeOfRemittance, @PurposeOfRemittanceOther)
|
|
,ISNULL(@sourceOfFund, @SourceOfFundOther)
|
|
,'Hold'
|
|
,'Unpaid'
|
|
,GETDATE()
|
|
,GETUTCDATE()
|
|
,@user
|
|
,'M'
|
|
,@senderName
|
|
,@receiverName
|
|
,'Y'
|
|
,@schemeId
|
|
,@StateId
|
|
,@DistrictId
|
|
,0
|
|
,ISNULL(@schemePremium, 0)
|
|
,'Bank Deposit'
|
|
,@PayerId
|
|
,CAST(@payOutPartnerId_O AS VARCHAR) + '|' + CAST(@pBankId_O AS VARCHAR)
|
|
,ISNULL(@discountedFee, 0)
|
|
,CASE
|
|
WHEN ISNULL(@discountedFee, 0) > 0
|
|
THEN 'REDEEM'
|
|
ELSE NULL
|
|
END
|
|
,'N'
|
|
|
|
SET @tranId = SCOPE_IDENTITY();
|
|
|
|
INSERT INTO tranSendersTemp (
|
|
tranId
|
|
,customerId
|
|
,membershipId
|
|
,firstName
|
|
,middleName
|
|
,lastName1
|
|
,lastName2
|
|
,fullName
|
|
,country
|
|
,[address]
|
|
,STATE
|
|
,district
|
|
,address2
|
|
,zipCode
|
|
,city
|
|
,email
|
|
,homePhone
|
|
,workPhone
|
|
,mobile
|
|
,nativeCountry
|
|
,dob
|
|
,placeOfIssue
|
|
,idType
|
|
,idNumber
|
|
,idPlaceOfIssue
|
|
,issuedDate
|
|
,validDate
|
|
,occupation
|
|
,countryRiskPoint
|
|
,customerRiskPoint
|
|
,ipAddress
|
|
,visaStatus
|
|
)
|
|
SELECT TOP 1 @tranId
|
|
,@senderId
|
|
,membershipId
|
|
,firstName
|
|
,middleName
|
|
,lastName1
|
|
,lastName2
|
|
,@senderName
|
|
,sc.countryName
|
|
,ISNULL(city, '') + ISNULL(', ' + streetUnicode, '')
|
|
,STATE
|
|
,streetUnicode
|
|
,@sAdd2
|
|
,zipCode
|
|
,city
|
|
,email
|
|
,homePhone
|
|
,workPhone
|
|
,LEFT(mobile, 15)
|
|
,nativeCountry = nc.countryName
|
|
,dob
|
|
,c.placeOfIssue
|
|
,sdv.detailTitle
|
|
,c.idNumber
|
|
,c.placeOfIssue
|
|
,c.idIssueDate
|
|
,c.idExpiryDate
|
|
,c.occupation
|
|
,@countryRisk
|
|
,(@countryRisk + @OccupationRisk)
|
|
,@sIpAddress
|
|
,c.visaStatus
|
|
FROM (
|
|
SELECT TOP 1 *
|
|
FROM dbo.customerMaster c WITH (NOLOCK)
|
|
WHERE c.customerId = @senderId
|
|
) C
|
|
LEFT JOIN countryMaster sc WITH (NOLOCK) ON c.country = sc.countryId
|
|
LEFT JOIN countryMaster nc WITH (NOLOCK) ON c.nativeCountry = nc.countryId
|
|
LEFT JOIN staticDataValue sdv WITH (NOLOCK) ON c.idType = sdv.valueId
|
|
|
|
IF @ReceiverId IS NULL
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT TOP 1 'X'
|
|
FROM receiverInformation(NOLOCK)
|
|
WHERE fullName = @receiverName
|
|
AND customerId = @senderId
|
|
)
|
|
BEGIN
|
|
INSERT INTO receiverInformation (
|
|
customerId
|
|
,firstName
|
|
,middleName
|
|
,lastName1
|
|
,country
|
|
,address
|
|
,city
|
|
,email
|
|
,homePhone
|
|
,mobile
|
|
,relationship
|
|
,STATE
|
|
,district
|
|
,fullName
|
|
,nativeCountry
|
|
,goodsOrigin
|
|
,goodsType
|
|
,portOfShipment
|
|
,relationOther
|
|
)
|
|
SELECT @senderId
|
|
,@rFirstName
|
|
,@rMiddleName
|
|
,@rLastName
|
|
,@pCountry
|
|
,@rAddress
|
|
,@rCity
|
|
,@rEmail
|
|
,@rMobileNo
|
|
,@rMobileNo
|
|
,@RelWithSender
|
|
,@rStateId
|
|
,@rDistrictId
|
|
,@receiverName
|
|
,@rNativeCountry
|
|
,@goodsOrigin
|
|
,@goodsType
|
|
,@portOfShipment
|
|
,@RelWithSenderOthers
|
|
|
|
SET @ReceiverId = SCOPE_IDENTITY()
|
|
END;
|
|
--ELSE
|
|
-- BEGIN
|
|
-- SELECT TOP 1 @ReceiverId = receiverId
|
|
-- FROM receiverInformation(nolock)
|
|
-- WHERE fullName = @receiverName AND customerId = @senderId;
|
|
--END;
|
|
END;
|
|
|
|
INSERT INTO tranReceiversTemp (
|
|
tranId
|
|
,customerId
|
|
,firstName
|
|
,middleName
|
|
,lastName1
|
|
,lastName2
|
|
,fullName
|
|
,country
|
|
,[address]
|
|
,[state]
|
|
,district
|
|
,zipCode
|
|
,city
|
|
,email
|
|
,homePhone
|
|
,workPhone
|
|
,mobile
|
|
,nativeCountry
|
|
,dob
|
|
,placeOfIssue
|
|
,idType
|
|
,idNumber
|
|
,idPlaceOfIssue
|
|
,issuedDate
|
|
,relationType
|
|
,validDate
|
|
,gender
|
|
,goodsOrigin
|
|
,goodsType
|
|
,portOfShipment
|
|
)
|
|
SELECT TOP 1 @tranId
|
|
,@ReceiverId
|
|
,firstName
|
|
,middleName
|
|
,lastName1
|
|
,lastName2
|
|
,@receiverName
|
|
,@pCountry
|
|
,[address]
|
|
,[state]
|
|
,district
|
|
,zipCode
|
|
,city
|
|
,email
|
|
,homePhone
|
|
,workPhone
|
|
,mobile
|
|
,NULL
|
|
,@rDob
|
|
,NULL
|
|
,ISNULL(@rIdType, idType)
|
|
,ISNULL(@rIdNo, idNumber)
|
|
,NULL
|
|
,@rIdIssue
|
|
,@RelWithSender
|
|
,@rIdExpiry
|
|
,NULL
|
|
,@goodsOrigin
|
|
,@goodsType
|
|
,@portOfShipment
|
|
FROM receiverInformation(NOLOCK)
|
|
WHERE receiverId = @ReceiverId
|
|
|
|
/*For duplicate pin check*/
|
|
INSERT INTO controlNoList (
|
|
controlNo
|
|
,createdby
|
|
)
|
|
SELECT @controlNo
|
|
,'M'
|
|
|
|
----IF @paymentType = 'WALLET'
|
|
--EXEC proc_UpdateCustomerBalance @controlNo = @controlNoEncrypted, @type = 'DEDUCT'
|
|
----## map locked ex rate with transaction for history
|
|
UPDATE exRateCalcHistory
|
|
SET controlNo = @controlNoEncrypted
|
|
,AGENT_TXN_REF_ID = @tranId
|
|
,isExpired = 1
|
|
WHERE FOREX_SESSION_ID = @forexSessionId
|
|
|
|
-- UPDATE FOR CUSTOMER LOYALTY
|
|
-- EXEC PROC_Customer_Loyalty @flag = 'check-eligible' , @isEligible = @isEligible OUT -- CHECKED ABOVE
|
|
|
|
EXEC PROC_Customer_LoyaltyV2 @flag = 'update-v2'
|
|
,@customerId = @senderId
|
|
,@createdFrom = 'M'
|
|
,@tranId = @tranId
|
|
,@createdBy = @user
|
|
,@controlNo = @controlNo
|
|
,@isManualSc = 'N'
|
|
,@referralCode = @introducer
|
|
,@serviceCharge = @iServiceCharge
|
|
|
|
|
|
--------------------------#########------------OFAC/COMPLIANCE INSERT (IF EXISTS)---------------########----------------------
|
|
IF EXISTS (
|
|
SELECT TOP 1 'X'
|
|
FROM remitTranComplianceTemp WITH (NOLOCK)
|
|
WHERE agentRefId = @agentRefId
|
|
)
|
|
BEGIN
|
|
INSERT INTO remitTranCompliance (
|
|
TranId
|
|
,csDetailTranId
|
|
,matchTranId
|
|
,reason
|
|
)
|
|
SELECT @tranId
|
|
,csDetailTranId
|
|
,matchTranId
|
|
,reason
|
|
FROM remitTranComplianceTemp WITH (NOLOCK)
|
|
WHERE agentRefId = @agentRefId
|
|
|
|
SET @compFinalRes = 'C'
|
|
END
|
|
|
|
UPDATE ComplianceLog
|
|
SET TRANID = @tranId
|
|
WHERE agentRefId = @agentRefId
|
|
|
|
IF (
|
|
ISNULL(@compFinalRes, '') <> ''
|
|
OR ISNULL(@ofacRes, '') <> ''
|
|
OR ISNULL(@receiverOfacRes, '') <> ''
|
|
)
|
|
BEGIN
|
|
IF (
|
|
(
|
|
ISNULL(@ofacRes, '') <> ''
|
|
OR ISNULL(@receiverOfacRes, '') <> ''
|
|
)
|
|
AND ISNULL(@compFinalRes, '') = ''
|
|
)
|
|
BEGIN
|
|
IF ISNULL(@ofacRes, '') <> ''
|
|
INSERT remitTranOfac (
|
|
TranId
|
|
,blackListId
|
|
,reason
|
|
,flag
|
|
)
|
|
SELECT @tranId
|
|
,@ofacRes
|
|
,@ofacReason
|
|
,dbo.FNAGetOFAC_Flag(@ofacRes)
|
|
|
|
IF ISNULL(@receiverOfacRes, '') <> ''
|
|
INSERT remitTranOfac (
|
|
TranId
|
|
,blackListId
|
|
,reason
|
|
,flag
|
|
)
|
|
SELECT @tranId
|
|
,@receiverOfacRes
|
|
,@ofacReason
|
|
,dbo.FNAGetOFAC_Flag(@receiverOfacRes)
|
|
|
|
UPDATE remitTranTemp
|
|
SET tranStatus = 'OFAC Hold'
|
|
WHERE id = @tranId
|
|
END
|
|
ELSE IF (
|
|
@compFinalRes <> ''
|
|
AND (
|
|
ISNULL(@ofacRes, '') = ''
|
|
OR ISNULL(@receiverOfacRes, '') = ''
|
|
)
|
|
)
|
|
BEGIN
|
|
UPDATE remitTranTemp
|
|
SET tranStatus = 'Compliance Hold'
|
|
WHERE id = @tranId
|
|
END
|
|
ELSE IF (
|
|
ISNULL(@compFinalRes, '') <> ''
|
|
AND (
|
|
ISNULL(@ofacRes, '') <> ''
|
|
OR ISNULL(@receiverOfacRes, '') <> ''
|
|
)
|
|
)
|
|
BEGIN
|
|
IF ISNULL(@ofacRes, '') <> ''
|
|
INSERT remitTranOfac (
|
|
TranId
|
|
,blackListId
|
|
,reason
|
|
,flag
|
|
)
|
|
SELECT @tranId
|
|
,@ofacRes
|
|
,@ofacReason
|
|
,dbo.FNAGetOFAC_Flag(@ofacRes)
|
|
|
|
IF ISNULL(@receiverOfacRes, '') <> ''
|
|
INSERT remitTranOfac (
|
|
TranId
|
|
,blackListId
|
|
,reason
|
|
,flag
|
|
)
|
|
SELECT @tranId
|
|
,@receiverOfacRes
|
|
,@ofacReason
|
|
,dbo.FNAGetOFAC_Flag(@receiverOfacRes)
|
|
|
|
UPDATE remitTranTemp
|
|
SET tranStatus = 'OFAC/Compliance Hold'
|
|
WHERE id = @tranId
|
|
END
|
|
END
|
|
|
|
--Compliance checking done
|
|
DECLARE @hasSufficientBalance CHAR(1) = 'Y'
|
|
IF @agentAvlLimit < @payoutAmt
|
|
BEGIN
|
|
SET @hasSufficientBalance = 'N'
|
|
INSERT INTO pushNotificationHistroy(customerId,body,title,createDate,imageURL,sentId,Type,isReservation,isRead,isSend,category, isClickable)
|
|
SELECT @SenderId,'Tran ID : '+CAST(@tranId AS VARCHAR)+' . Your transaction has been processed successfully but your balance is insufficient. Please load your wallet.','Insufficent Balance.',getdate(),'',@tranId,0,0,0,0,'INFO', 'Y'
|
|
END
|
|
|
|
--New logic for referral
|
|
IF ISNULL(@discountedFee, 0) > 0
|
|
EXEC proc_InsertRewardPoints @Flag = 'DEBIT', @CustomerId = @senderId, @rewardValue = @discountedFee, @TranId = @tranId
|
|
|
|
EXEC proc_InsertRewardPoints @Flag = 'TRANSACTION', @CustomerId = @senderId, @TranId = @tranId
|
|
|
|
IF @@TRANCOUNT > 0
|
|
COMMIT TRANSACTION;
|
|
|
|
SELECT 0 errorCode
|
|
,'Transaction has been sent successfully' msg
|
|
,@tranId id
|
|
,@controlNo extra
|
|
,ISNULL(@isRealTime, 0) extra2
|
|
,@hasSufficientBalance extra3
|
|
|
|
RETURN
|
|
|
|
-- For BroadCast Notification
|
|
--EXEC ProcBroadCastMobile @Flag='TRANSACTION_SUCCESS', @RowId=@customerId, @ControlNo=@controlNo, @CustomerId= @customerId
|
|
END
|
|
END TRY
|
|
|
|
BEGIN CATCH
|
|
IF @@TRANCOUNT <> 0
|
|
ROLLBACK TRANSACTION;
|
|
|
|
DECLARE @errorMessage VARCHAR(MAX);
|
|
|
|
SET @errorMessage = ERROR_MESSAGE();
|
|
|
|
EXEC proc_errorHandler 1
|
|
,@errorMessage
|
|
,@user;
|
|
END CATCH
|
|
|