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.
731 lines
28 KiB
731 lines
28 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
|
|
|
|
AS
|
|
-----------------------------------------------------------
|
|
-- For Broadcast notification , @flag = 'send'
|
|
-----------------------------------------------------------
|
|
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
|
|
|
|
|
|
IF EXISTS(SELECT * FROM STATICDATAVALUE(NOLOCK) WHERE VALUEID = @RelWithSender AND TYPEID = 2100)
|
|
BEGIN
|
|
SELECT @RelWithSender = detailTitle
|
|
FROM STATICDATAVALUE(NOLOCK)
|
|
WHERE VALUEID = @RelWithSender
|
|
AND TYPEID = 2100
|
|
END
|
|
IF EXISTS(SELECT * FROM STATICDATAVALUE(NOLOCK) WHERE VALUEID = @PurposeOfRemittance AND TYPEID = 3800)
|
|
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)
|
|
BEGIN
|
|
SELECT @sourceOfFund = detailTitle
|
|
FROM STATICDATAVALUE(NOLOCK)
|
|
WHERE VALUEID = @sourceOfFund
|
|
AND TYPEID = 3900
|
|
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
|
|
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'
|
|
END
|
|
|
|
IF @flag = 'SEND'
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT TOP 1 'X' FROM dbo.customerMaster(nolock) WHERE username = @user AND approvedDate IS NOT NULL)
|
|
BEGIN
|
|
EXEC proc_errorHandler 1,'You are not authorized to perform transaction :(', 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, 'A';
|
|
RETURN
|
|
END
|
|
|
|
SELECT @pcountryId = cm.countryId
|
|
FROM receiverInformation RI(NOLOCK)
|
|
INNER JOIN countryMaster CM(NOLOCK) ON CM.countryName = RI.country
|
|
WHERE RI.receiverId = @ReceiverId
|
|
|
|
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),
|
|
@receiverName VARCHAR(100),
|
|
@controlNoEncrypted VARCHAR(30),
|
|
@tempCompId BIGINT,
|
|
@pBranch INT,
|
|
@pBranchName VARCHAR(100),
|
|
@pCountry VARCHAR(100)
|
|
|
|
SELECT @sCountryId = 113,@sBranch = 394395
|
|
|
|
SELECT @SenderId = customerId FROM customerMaster (NOLOCK) WHERE USERNAME = @User
|
|
|
|
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
|
|
DECLARE @isRealTime BIT = 0
|
|
|
|
SELECT @payOutPartnerId = AGENTID, @isRealTime = isRealTime
|
|
FROM TblPartnerwiseCountry(NOLOCK)
|
|
WHERE CountryId = @pCountryId AND IsActive = 1
|
|
AND ISNULL(PaymentMethod, @deliveryMethodId) = @deliveryMethodId
|
|
|
|
IF @payOutPartnerId IS NULL
|
|
BEGIN
|
|
EXEC proc_errorHandler 3,'Oops, something went wrong.Please perform the transaction again' ,null
|
|
RETURN;
|
|
END
|
|
|
|
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
|
|
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
|
|
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)
|
|
SELECT @controlNoEncrypted = dbo.FNAEncryptString(@controlNo);
|
|
|
|
IF EXISTS (SELECT TOP 1 'X' FROM pinQueueList WITH(NOLOCK) WHERE icn = @controlNoEncrypted)
|
|
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 pinQueueList WITH(NOLOCK) WHERE icn = @controlNoEncrypted)
|
|
BEGIN
|
|
EXEC proc_errorHandler 10, 'Technical error occurred. Please try again',NULL;
|
|
RETURN;
|
|
END
|
|
END;
|
|
|
|
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;
|
|
|
|
DECLARE @pAgentCommCurrency VARCHAR(3),@pAgentComm MONEY
|
|
|
|
SELECT @pAgentCommCurrency = DBO.FNAGetPayCommCurrency(@sSuperAgent,@sAgent,@sBranch,@SCOUNTRYID,@pSuperAgent,@pBranch,@pCountryId)
|
|
|
|
SELECT @pAgentComm = amount FROM dbo.FNAGetPayComm(@sAgent,@sCountryId,
|
|
NULL, null, @pCountryId, null, @pAgent, @pAgentCommCurrency
|
|
,@deliveryMethodId, @collAmt, @payoutAmt, @serviceCharge, @transferAmt, NULL)
|
|
|
|
--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
|
|
FROM exRateCalcHistory(NOLOCK)
|
|
WHERE FOREX_SESSION_ID = @forexSessionId AND [USER_ID] = @user
|
|
|
|
IF @customerRate IS NULL
|
|
BEGIN
|
|
EXEC proc_errorHandler 6, 'Transaction cannot be proceed. Exchange Rate not defined', NULL
|
|
RETURN
|
|
END
|
|
|
|
--Get Service Charge----------------------------------------------------------------------------------------------------------------------
|
|
|
|
|
|
SELECT @iServiceCharge = ISNULL(amount, -1)
|
|
FROM [dbo].FNAGetServiceCharge(
|
|
@sCountryId, @sSuperAgent, @sAgent, @sBranch,
|
|
@pCountryId, @pSuperAgent, @pAgent, @pBranch,
|
|
@deliveryMethodId, @collAmt, @collCurr
|
|
)
|
|
IF @iServiceCharge = -1
|
|
BEGIN
|
|
EXEC proc_errorHandler 13, 'Transaction cannot be proceed. Service Charge is not defined', NULL
|
|
RETURN
|
|
END
|
|
|
|
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
|
|
|
|
SELECT @iTAmt = @collAmt - @iServiceCharge
|
|
|
|
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(500), RULE_ID INT, SHORT_MSG VARCHAR(100), [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 = 11387
|
|
)
|
|
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;
|
|
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 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 HQ.', NULL;
|
|
RETURN;
|
|
END
|
|
END
|
|
|
|
BEGIN TRANSACTION;
|
|
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
|
|
)
|
|
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
|
|
|
|
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
|
|
)
|
|
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
|
|
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)
|
|
SELECT @senderId ,@rFirstName,@rMiddleName,@rLastName,@pCountry,@rAddress,@rCity,@rEmail
|
|
,@rMobileNo,@rMobileNo,@RelWithSender,@rStateId,@rDistrictId,@receiverName,@rNativeCountry
|
|
|
|
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
|
|
)
|
|
SELECT TOP 1 @tranId,@ReceiverId,firstName,middleName ,lastName1 ,lastName2 ,@receiverName ,
|
|
@pCountry ,[address] ,[state] ,district ,zipCode ,city ,email ,homePhone ,workPhone ,mobile ,country ,@rDob ,
|
|
null ,ISNULL(@rIdType,idType) ,ISNULL(@rIdNo,idNumber) ,null ,@rIdIssue ,@RelWithSender,@rIdExpiry ,null
|
|
FROM receiverInformation(NOLOCK) WHERE receiverId = @ReceiverId
|
|
|
|
----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
|
|
--------------------------#########------------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
|
|
|
|
IF @@TRANCOUNT > 0
|
|
COMMIT TRANSACTION;
|
|
SElect 0 errorCode, 'Transaction has been sent successfully' msg, @tranId id,@controlNo extra, ISNULL(@isRealTime, 0) extra2
|
|
|
|
RETURN
|
|
|
|
DECLARE @CustomerId BIGINT , @availableBalance MONEY
|
|
SELECT @customerId = (SELECT TST.customerId FROM remitTrantemp RTT WITH(NOLOCK) INNER JOIN dbo.tranSendersTemp TST(NOLOCK) ON TST.TRANID = RTT.ID WHERE controlNo = @controlNo)
|
|
|
|
-- 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
|