IME-LONDON
/
MOBILE_API
8
0
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
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.
59 Commits
3 Branches
0 Tags
4.7 MiB
 
 
 
Branch: master
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
MOBILE_API/DB/SPs/proc_sendTranDomesticHo.sql

1566 lines
50 KiB
Raw Permalink Blame History

USE [FastMoneyPro_Remit]
GO
/****** Object: StoredProcedure [dbo].[proc_sendTranDomesticHo] Script Date: 7/4/2019 11:35:48 AM ******/
DROP PROCEDURE [dbo].[proc_sendTranDomesticHo]
GO
/****** Object: StoredProcedure [dbo].[proc_sendTranDomesticHo] Script Date: 7/4/2019 11:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[proc_sendTranDomesticHo] (
@flag VARCHAR(50)
,@user VARCHAR(30)
,@id BIGINT = NULL
,@membershipId VARCHAR(20) = NULL
,@agentUniqueRefId VARCHAR(30) = NULL
,@senderId BIGINT = NULL
,@sMemId VARCHAR(20) = NULL
,@sFirstName VARCHAR(50) = NULL
,@sMiddleName VARCHAR(50) = NULL
,@sLastName1 VARCHAR(50) = NULL
,@sLastName2 VARCHAR(50) = NULL
,@sAddress VARCHAR(200)= NULL
,@sContactNo VARCHAR(20) = NULL
,@sIdType VARCHAR(50) = NULL
,@sIdNo VARCHAR(30) = NULL
,@sEmail VARCHAR(50) = NULL
,@receiverId BIGINT = NULL
,@rMemId VARCHAR(20) = NULL
,@rFirstName VARCHAR(50) = NULL
,@rMiddleName VARCHAR(50) = NULL
,@rLastName1 VARCHAR(50) = NULL
,@rLastName2 VARCHAR(50) = NULL
,@rAddress VARCHAR(200)= NULL
,@rContactNo VARCHAR(20) = NULL
,@rIdType VARCHAR(50) = NULL
,@rIdNo VARCHAR(30) = NULL
,@remarks VARCHAR(200)= NULL
,@sBranch INT = NULL
,@pBranch INT = NULL
,@pBank INT = NULL
,@pBankBranch INT = NULL
,@accountNo VARCHAR(30) = NULL
,@pCountry VARCHAR(100)= NULL --payout Country
,@pState VARCHAR(100)= NULL --payout State
,@pDistrict VARCHAR(100)= NULL --payout District
,@pLocation INT = NULL --payout Location
,@collMode VARCHAR(50) = NULL
,@collCurr VARCHAR(3) = NULL
,@transferAmt MONEY = NULL
,@serviceCharge MONEY = NULL
,@handlingFee MONEY = NULL
,@cAmt MONEY = NULL
,@exRate MONEY = NULL
,@pAmt MONEY = NULL
,@payoutCurr VARCHAR(3) = NULL
,@deliveryMethod VARCHAR(50) = NULL
,@purpose VARCHAR(100)= NULL
,@sourceOfFund VARCHAR(100)= NULL
,@Occupation VARCHAR(200)= NULL
,@relationship VARCHAR(100)= NULL
,@controlNo VARCHAR(20) = NULL
,@txnId INT = NULL
,@enableApi CHAR(1) = NULL
,@sDcInfo VARCHAR(50) = NULL
,@sIpAddress VARCHAR(50) = NULL
,@complianceAction CHAR(1) = NULL
,@compApproveRemark VARCHAR(200) = NULL
,@markSuspicious CHAR(1) = NULL
,@txnbatchId VARCHAR(50) = NULL
,@txnDocFolder VARCHAR(50) = NULL
,@sDOB VARCHAR(25) = NULL
,@sIdIssuedDate VARCHAR(25) = NULL
,@sIdValidDate VARCHAR(25) = NULL
,@sDOBBs VARCHAR(25) = NULL
,@sIdIssuedDateBs VARCHAR(25) = NULL
,@sIdValidDateBs VARCHAR(25) = NULL
,@sIdIssuedPlace VARCHAR(50) = NULL
,@sCustCardId VARCHAR(25) = NULL
,@sGender VARCHAR(25) = NULL
,@sMotherFatherName VARCHAR(25) = NULL
,@sAmountThreshold MONEY = NULL
)
AS
SET XACT_ABORT ON
BEGIN TRY
DECLARE
@sCurrCostRate DECIMAL(15, 9)
,@sCurrHoMargin DECIMAL(15, 9)
,@pCurrCostRate DECIMAL(15, 9)
,@pCurrHoMargin DECIMAL(15, 9)
,@sCurrAgentMargin DECIMAL(15, 9)
,@pCurrAgentMargin DECIMAL(15, 9)
,@sCurrSuperAgentMargin DECIMAL(15, 9)
,@pCurrSuperAgentMargin DECIMAL(15, 9)
,@customerRate DECIMAL(15, 9)
,@sAgentSettRate DECIMAL(15, 9)
,@pDateCostRate DECIMAL(15, 9)
,@sAgentComm MONEY
,@sAgentCommCurrency VARCHAR(3)
,@sSuperAgentComm MONEY
,@sSuperAgentCommCurrency VARCHAR(3)
,@sHubComm MONEY
,@sHubCommCurrency VARCHAR(3)
,@pAgentComm MONEY
,@pAgentCommCurrency VARCHAR(3)
,@pSuperAgentComm MONEY
,@pSuperAgentCommCurrency VARCHAR(3)
,@pHubComm MONEY
,@pHubCommCurrency VARCHAR(3)
,@pBankName VARCHAR(100)
,@pBankBranchName VARCHAR(100)
,@promotionCode INT
,@promotionType INT
,@sSuperAgent INT
,@sSuperAgentName VARCHAR(100)
,@sAgent INT
,@sAgentName VARCHAR(100)
,@sBranchName VARCHAR(100)
,@sCountry VARCHAR(100)
,@pSuperAgent INT
,@pSuperAgentName VARCHAR(100)
,@pAgent INT
,@pAgentName VARCHAR(100)
,@pBranchName VARCHAR(100)
,@settlingAgent INT = NULL
,@code VARCHAR(50)
,@userName VARCHAR(50)
,@password VARCHAR(50)
,@sCountryId VARCHAR(30)
,@msg VARCHAR(200)
,@senderName VARCHAR(100)
,@receiverName VARCHAR(100)
DECLARE
@limitBal MONEY
,@sendingCustType INT
,@sendingCurrency VARCHAR(3)
,@receivingCurrency VARCHAR(3)
,@receivingCustType INT
DECLARE
@sendingCount INT
,@sendingAmount MONEY
,@receivingCount INT
,@receivingAmount MONEY
,@tranCount INT
,@tranAmount MONEY
,@period INT
,@nextAction INT
DECLARE
@pCountryId INT
,@deliveryMethodId INT
,@agentType INT
,@actAsBranchFlag CHAR(1)
,@approveFlag CHAR(1)
,@currentDate DATETIME
,@sFullName VARCHAR(200)
,@rFullName VARCHAR(200)
DECLARE @controlNoEncrypted VARCHAR(20)
DECLARE @csMasterId INT, @complianceRes VARCHAR(20), @totalRows INT, @count INT, @compFinalRes VARCHAR(20), @result VARCHAR(MAX)
DECLARE @csMasterRec TABLE (rowId INT IDENTITY(1,1), masterId INT)
SET @senderName = @sFirstName + ISNULL(' ' + @sMiddleName, '') + ISNULL(' ' + @sLastName1, '') + ISNULL(' ' + @sLastName2, '')
SET @receiverName = @rFirstName + ISNULL(' ' + @rMiddleName, '') + ISNULL(' ' + @rLastName1, '') + ISNULL(' ' + @rLastName2, '')
IF @flag = 'm'
BEGIN
IF NOT EXISTS(SELECT 'X' FROM customers WITH(NOLOCK) WHERE membershipId = @membershipId AND ISNULL(isDeleted, 'N') <> 'Y')
BEGIN
EXEC proc_errorHandler 1, 'Customer with this Membership Id not found', NULL
END
ELSE IF EXISTS(SELECT 'X' FROM customers WITH(NOLOCK)
WHERE membershipId = @membershipId
AND ISNULL(isDeleted, 'N') <> 'Y'
AND ISNULL(isBlackListed, 'N') = 'Y')
BEGIN
EXEC proc_errorHandler 1, 'This customer is blacklisted. Cannot proceed for transaction.', NULL
END
ELSE
BEGIN
EXEC proc_errorHandler 0, 'Customer Found', NULL
END
SELECT
cust.*
,ci.idType
,ci.idNumber
FROM customers cust WITH(NOLOCK)
LEFT JOIN customerIdentity ci WITH(NOLOCK) ON cust.customerId = ci.customerId AND ci.isPrimary = 'Y' AND ISNULL(ci.isDeleted, 'N') <> 'Y' AND ISNULL(ci.isActive, 'Y') = 'Y'
WHERE cust.membershipId = @membershipId AND ISNULL(cust.isDeleted, 'N') <> 'Y'
RETURN
END
ELSE IF @flag = 'u'
BEGIN
UPDATE remitTran SET
modifiedDate = GETDATE()
,modifiedDateLocal = DBO.FNADateFormatTZ(GETDATE(), @user)
,modifiedBy = @user
WHERE id = @id
END
ELSE IF @flag = 'a'
BEGIN
SELECT * FROM remitTran WITH(NOLOCK) WHERE id = @id
END
ELSE IF @flag = 'v'
BEGIN
--1. Find Sender Information
IF (@sBranch IS NULL)
SELECT @sBranch = agentId FROM applicationUsers WITH(NOLOCK) WHERE userName = @user
SELECT @agentType = agentType, @sAgent = parentId, @sBranchName = agentName, @sCountry = agentCountry FROM agentMaster WITH(NOLOCK) WHERE agentId = @sBranch
--Check for Branch or Agent Acting as Branch
IF @agentType = 2903
BEGIN
SET @sAgent = @sBranch
END
SELECT DISTINCT @sSuperAgent = parentId, @sAgentName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @sAgent
SELECT DISTINCT @sSuperAgentName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @sSuperAgent
--2. Find Settling Agent
SELECT @settlingAgent = agentId FROM agentMaster WHERE agentId = @sBranch AND isSettlingAgent = 'Y'
IF @settlingAgent IS NULL
SELECT @settlingAgent = agentId FROM agentMaster WHERE agentId = @sAgent AND isSettlingAgent = 'Y'
IF @settlingAgent IS NULL
SELECT @settlingAgent = agentId FROM agentMaster WHERE agentId = @sSuperAgent AND isSettlingAgent = 'Y'
--3. Check Limit starts
SET @limitBal = [dbo].FNAGetLimitBal(@settlingAgent)
IF @transferAmt > @limitBal
BEGIN
EXEC [proc_errorHandler] 1, 'Transfer amount exceeds Limit. Please, Check your available limit.', NULL
RETURN
END
EXEC proc_errorHandler 0, 'Verification Successful', NULL
END
/*
IF @flag='validate'
BEGIN
SELECT @pCountry = 'Nepal'
SELECT @sCountry = 'Nepal'
SELECT @pCountryId = '151'
SELECT @sCountryId = '151'
DECLARE @cisMasterId INT, @validationRes VARCHAR(50), @validationFinalRes VARCHAR(100)
DECLARE @cisMasterRec TABLE (rowId INT IDENTITY(1,1), masterId INT)
DECLARE @requiredField TABLE (rowId INT IDENTITY(1,1), criteriaId INT,controlId VARCHAR(50),errorMsg VARCHAR(200))
DECLARE @cisValidationResult TABLE (rowId INT IDENTITY(1,1), cisDetailId VARCHAR(20))
SELECT @deliveryMethodId = serviceTypeId FROM serviceTypeMaster WITH(NOLOCK) WHERE typeTitle = @deliveryMethod AND ISNULL(isDeleted, 'N') = 'N'
-------Required Validation Check Begin----------
INSERT @cisMasterRec(masterId)
SELECT masterId FROM dbo.FNAGetIdRuleMaster(@sBranch, @pCountryId, NULL, @pBranch, NULL, @senderId, @receiverId)
SELECT @totalRows = COUNT(*) FROM @cisMasterRec
IF EXISTS(SELECT 'X' FROM @cisMasterRec)
BEGIN
SET @count = 1
WHILE(@count <= @totalRows)
BEGIN
SELECT @cisMasterId = masterId FROM @cisMasterRec WHERE rowId = @count
--PRINT CAST (@count AS VARCHAR)+' count'
--PRINT CAST (@cisMasterId AS VARCHAR)+' cisMasterId'
EXEC proc_idRuleDetail
@user = @user
,@tranId = @id
,@tAmt = @transferAmt
,@senId = @senderId
,@masterId = @cisMasterId
,@paymentMethod = @deliveryMethodId
,@checkingFor = 'v'
,@senderId = @sIdNo
,@senderName = @senderName
,@senderMobile = @sContactNo
,@result = @validationRes OUTPUT
SET @count = @count + 1
IF @validationRes IS NOT NULL
INSERT INTO @cisValidationResult
SELECT DISTINCT VALUE from dbo.udf_Split(@validationRes,',')
END
END
--SELECT * FROM @cisValidationResult
IF EXISTS (SELECT 'X' FROM @cisValidationResult)
BEGIN
SET @msg = 'Field validation failed.'
SELECT 101 errorCode, @msg msg, NULL id
SELECT
criteriaId
,controlId
,errorMsg
FROM cisFieldMapping WITH(NOLOCK) where criteriaId in(
SELECT DISTINCT criteriaId FROM cisCriteria c WITH(NOLOCK) INNER JOIN @cisValidationResult tmpc
ON c.cisDetailId = tmpc.cisDetailId)
AND ISNULL(scope,'D')='D' AND ISNULL(IsActive,'Y')='Y'
ORDER BY ISNULL(controlRankID,0)
--SELECT DISTINCT * FROM @requiredField
RETURN
END
-------Required Validation Check End----------
EXEC [proc_errorHandler] 0, 'Compliance Validation Successful.', NULL
END
ELSE IF @flag = 'vt' --Verify Transaction
BEGIN
IF @user IS NULL
BEGIN
EXEC proc_errorHandler 1, 'Your session has expired. Cannot send transaction', NULL
RETURN
END
IF (@pBranch IS NOT NULL)
BEGIN
SELECT @pAgent = parentId, @pBranchName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @pBranch
SELECT @pSuperAgent = parentId, @pAgentName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @pAgent
SELECT @pSuperAgentName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @pSuperAgent
END
SELECT @pCountry = 'Nepal'
SELECT @sCountry = 'Nepal'
SELECT @pCountryId = '151'
SELECT @sCountryId = '151'
--3. Check Limit starts
SELECT @collCurr = 'NPR', @payoutCurr = 'NPR'
SELECT @sendingCurrency = @collCurr
SELECT @receivingCurrency = @payoutCurr
SET @limitBal = [dbo].FNAGetLimitBal(@settlingAgent)
SET @currentDate = dbo.FNAGetDateInNepalTz()
IF EXISTS(SELECT 'X' FROM remitTran trn WITH(NOLOCK)
LEFT JOIN tranSenders sen WITH(NOLOCK) ON trn.id = sen.tranId
LEFT JOIN tranReceivers rec WITH(NOLOCK) ON trn.id = rec.tranId
WHERE
sen.firstName = @sFirstName
AND ISNULL(sen.middleName, '') = ISNULL(@sMiddleName, '')
AND ISNULL(sen.lastName1, '') = ISNULL(@sLastName1, '')
AND ISNULL(sen.lastName2, '') = ISNULL(@sLastName2, '')
AND rec.firstName = @rFirstName
AND ISNULL(rec.middleName, '') = ISNULL(@rMiddleName, '')
AND ISNULL(rec.lastName1, '') = ISNULL(@rLastName1, '')
AND ISNULL(rec.lastName2, '') = ISNULL(@rLastName2, '')
AND trn.tAmt = @transferAmt
AND trn.pLocation = @pLocation
AND trn.serviceCharge = @serviceCharge
AND trn.cAmt = @cAmt
AND DATEDIFF(MI, trn.createdDate, @currentDate) <= 5)
BEGIN
EXEC proc_errorHandler 1, 'Similar Transaction Found', NULL
RETURN
END
IF @transferAmt > @limitBal
BEGIN
EXEC [proc_errorHandler] 1, 'Transfer amount exceeds Limit. Please, Check your available limit.', NULL
RETURN
END
IF NOT EXISTS (
SELECT
'X'
FROM sendTranLimit
WHERE countryId = @sCountryId
AND (tranType = @collMode OR tranType IS NULL)
AND (paymentType = @deliveryMethod OR paymentType IS NULL)
AND (customerType = @sendingCustType OR customerType IS NULL)
AND currency = @sendingCurrency
AND (receivingCountry = @pCountryId OR receivingCountry IS NULL)
AND ISNULL(minLimitAmt, 0) <= @transferAmt
AND ISNULL(maxLimitAmt, 0) >= @transferAmt
AND ISNULL(isActive, 'N') = 'Y'
)
BEGIN
EXEC [proc_errorHandler] 3, 'Country Sending limit is not defined or exceeds.', NULL
RETURN
END
SELECT @deliveryMethodId = serviceTypeId FROM serviceTypeMaster WITH(NOLOCK) WHERE typeTitle = @deliveryMethod AND ISNULL(isDeleted, 'N') = 'N'
-------Compliance Check Begin----------
--DECLARE @result VARCHAR(MAX)
INSERT @csMasterRec(masterId)
SELECT masterId FROM dbo.FNAGetComplianceRuleMaster(@sBranch, @pCountryId, NULL, @pBranch, NULL, @senderId, @receiverId)
SELECT @totalRows = COUNT(*) FROM @csMasterRec
DECLARE @denyTxn CHAR(1) = 'N'
IF EXISTS(SELECT 'X' FROM @csMasterRec)
BEGIN
DELETE FROM remitTranComplianceTemp WHERE agentRefId = @agentUniqueRefId
SET @count = 1
WHILE(@count <= @totalRows)
BEGIN
SELECT @csMasterId = masterId FROM @csMasterRec WHERE rowId = @count
EXEC proc_complianceRuleDetail
@user = @user
,@tranId = @id
,@tAmt = @transferAmt
,@senId = @senderId
,@benId = @receiverId
,@beneficiaryName = @receiverName
,@beneficiaryMobile = @rContactNo
,@benAccountNo = @accountNo
,@masterId = @csMasterId
,@paymentMethod = @deliveryMethodId
,@checkingFor = 'v'
,@agentRefId = @agentUniqueRefId
,@result = @complianceRes OUTPUT
,@senderId = @sIdNo
,@senderName = @senderName
,@senderMobile = @sContactNo
SET @compFinalRes = ISNULL(@compFinalRes, '') + ISNULL(@complianceRes, '')
IF @complianceRes = 'M' AND ISNULL(@complianceAction, '') <> 'B' AND ISNULL(@complianceAction, '') <> 'C'
SET @complianceAction = 'M'
IF @complianceRes = 'C' AND ISNULL(@complianceAction, '') <> 'B'
SET @complianceAction = 'C'
IF @complianceRes = 'B'
SET @complianceAction = 'B'
SET @count = @count + 1
END
END
IF(ISNULL(@compFinalRes, '') <> '')
BEGIN
IF(@compFinalRes <> '')
BEGIN
IF @complianceAction = 'B'
SET @msg = 'WARNING!!! This customer is under compliance'
ELSE IF @complianceAction = 'M'
BEGIN
SET @compApproveRemark = ISNULL(@compApproveRemark, 'Marked for Compliance')
SET @msg = 'WARNING!!! This customer is under compliance'
END
ELSE
SET @msg = 'WARNING!!! This customer is under compliance'
SELECT 101 errorCode, @msg msg, @complianceAction id, @compApproveRemark compApproveRemark
--SELECT 101 errorCode, @msg msg, 'B' id, @compApproveRemark compApproveRemark
--EXEC proc_sendPageLoadData @flag = 'compliance', @user = @user, @agentRefId = @agentUniqueRefId
END
RETURN
END
-------Compliance Check End----------
EXEC [proc_errorHandler] 0, 'Transaction verification Successful', NULL
--*****Check For Same Name*****
SELECT tranId = rt.id, senderName, sIdType = ISNULL(sdv.detailTitle,sen.IdType), sIdNo = sen.idNumber, pAmt, pCountry
FROM vwRemitTran rt WITH(NOLOCK)
INNER JOIN vwTranSenders sen WITH(NOLOCK) ON rt.id = sen.tranId
LEFT JOIN staticDataValue sdv WITH(NOLOCK) ON sen.idType=CAST(sdv.valueId AS VARCHAR)
WHERE senderName = @senderName AND rt.createdDate BETWEEN CONVERT(VARCHAR, GETDATE(), 101) AND CONVERT(VARCHAR, GETDATE(), 101) + ' 23:59:59'
--*****Check For Same Id*****
SELECT tranId = rt.id, senderName, sIdType = ISNULL(sdv.detailTitle,sen.IdType), sIdNo = sen.idNumber, pAmt, pCountry
FROM vwRemitTran rt WITH(NOLOCK)
INNER JOIN vwTranSenders sen WITH(NOLOCK) ON rt.id = sen.tranId
LEFT JOIN staticDataValue sdv WITH(NOLOCK) ON sen.idType=CAST(sdv.valueId AS VARCHAR)
WHERE idType = @sIdType AND idNumber = @sIdNo AND rt.createdDate BETWEEN CONVERT(VARCHAR, GETDATE(), 101) AND CONVERT(VARCHAR, GETDATE(), 101) + ' 23:59:59'
END
*/
IF @flag = 'vt'
BEGIN
IF EXISTS(SELECT 'X' FROM customerMaster WITH(NOLOCK)
WHERE membershipId = @sMemId
AND ISNULL(isDeleted, 'N') <> 'Y' AND approvedDate IS NULL)
BEGIN
IF ISNULL(@sAmountThreshold,0) <> 0 AND ISNULL(@transferAmt,0)<>0
BEGIN
IF @transferAmt >= @sAmountThreshold
BEGIN
SELECT 1 errorCode,'Customer with this membership ID is not approved yet. Unapproved customer cannot send money greater than or equals to threshold amount using customer card. Please contact head office.' msg,NULL id,'custenroll' vtype
RETURN;
END
END
END
IF @sIdNo IS NULL AND @sContactNo IS NULL
BEGIN
SELECT 0 errorCode,'Transaction verification Successful.' msg,NULL id,'compliance' vtype
RETURN
END
SELECT @pCountry = 'Nepal'
SELECT @sCountry = 'Nepal'
SELECT @pCountryId = '151'
SELECT @sCountryId = '151'
DECLARE @cisMasterId INT, @validationRes VARCHAR(50), @validationFinalRes VARCHAR(100)
DECLARE @cisMasterRec TABLE (rowId INT IDENTITY(1,1), masterId INT)
DECLARE @requiredField TABLE (rowId INT IDENTITY(1,1), criteriaId INT,controlId VARCHAR(50),errorMsg VARCHAR(200))
DECLARE @cisValidationResult TABLE (rowId INT IDENTITY(1,1), cisDetailId VARCHAR(20))
SELECT @deliveryMethodId = serviceTypeId FROM serviceTypeMaster WITH(NOLOCK) WHERE typeTitle = @deliveryMethod AND ISNULL(isDeleted, 'N') = 'N'
-------Required Validation Check Begin----------
INSERT @cisMasterRec(masterId)
SELECT masterId FROM dbo.FNAGetIdRuleMaster(@sBranch, @pCountryId, NULL, @pBranch, NULL, @senderId, @receiverId)
SELECT @totalRows = COUNT(*) FROM @cisMasterRec
IF EXISTS(SELECT 'X' FROM @cisMasterRec)
BEGIN
SET @count = 1
WHILE(@count <= @totalRows)
BEGIN
SELECT @cisMasterId = masterId FROM @cisMasterRec WHERE rowId = @count
EXEC proc_idRuleDetail
@user = @user
,@tranId = @id
,@tAmt = @transferAmt
,@senId = @senderId
,@masterId = @cisMasterId
,@paymentMethod = @deliveryMethodId
,@checkingFor = 'v'
,@senderId = @sIdNo
,@senderName = @senderName
,@senderMobile = @sContactNo
,@result = @validationRes OUTPUT
SET @count = @count + 1
IF @validationRes IS NOT NULL
INSERT INTO @cisValidationResult
SELECT DISTINCT VALUE from dbo.udf_Split(@validationRes,',')
END
END
IF EXISTS (SELECT 'X' FROM @cisValidationResult)
BEGIN
DECLARE @FieldValue TABLE (controlId VARCHAR(50) UNIQUE, value VARCHAR(200))
INSERT @FieldValue
SELECT 'sof', @sourceOfFund UNION ALL
SELECT 'por', @purpose UNION ALL
SELECT 'relWithSender', @relationship UNION ALL
SELECT 'sIdType', @sIdType UNION ALL
SELECT 'sIdNo', @sIdNo UNION ALL
SELECT 'txtSendIdValidDate', @sIdValidDate UNION ALL
SELECT 'txtSendDOB', @sDOB UNION ALL
SELECT 'occupation', @Occupation UNION ALL
SELECT 'sIdIssuedPlace', @sIdIssuedPlace
DELETE FROM @FieldValue WHERE NULLIF(LTRIM(value), '') IS NULL
OR (value = 'select' AND controlId IN ('sof', 'por', 'relWithSender' , 'occupation','sIdIssuedPlace'))
--@sIdType
IF '1301' = @sIdType
DELETE FROM @FieldValue WHERE controlId = 'txtSendIdValidDate'
SELECT
cm.criteriaId
,cm.controlId
,errorMsg
INTO #final
FROM cisFieldMapping cm WITH(NOLOCK)
INNER JOIN cisCriteria c WITH(NOLOCK) ON cm.criteriaId = c.criteriaId
INNER JOIN @cisValidationResult tmpc ON c.cisDetailId = tmpc.cisDetailId
LEFT JOIN @FieldValue fv ON cm.controlId = fv.controlId
WHERE ISNULL(cm.scope,'B')='B' AND ISNULL(cm.IsActive,'Y')='Y'
AND fv.controlId IS NULL AND cm.controlId IS NOT NULL
ORDER BY ISNULL(controlRankID,0)
IF '1301' = @sIdType
DELETE FROM #final WHERE controlId = 'txtSendIdValidDate'
IF EXISTS(SELECT 'X' FROM #final)
BEGIN
SET @msg = 'Field validation failed.'
SELECT 101 errorCode, @msg msg, NULL id,'idrule' vtype
SELECT DISTINCT * FROM #final
RETURN
END
END
IF @user IS NULL
BEGIN
SELECT 1 errorCode,'Your session has expired. Cannot send transaction.' msg,NULL id,'compliance' vtype
RETURN
END
IF (@pBranch IS NOT NULL)
BEGIN
SELECT @pAgent = parentId, @pBranchName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @pBranch
SELECT @pSuperAgent = parentId, @pAgentName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @pAgent
SELECT @pSuperAgentName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @pSuperAgent
END
--3. Check Limit starts
SELECT @collCurr = 'NPR', @payoutCurr = 'NPR'
SELECT @sendingCurrency = @collCurr
SELECT @receivingCurrency = @payoutCurr
SET @limitBal = [dbo].FNAGetLimitBal(@settlingAgent)
SET @currentDate = dbo.FNAGetDateInNepalTz()
IF EXISTS(SELECT 'X' FROM remitTran trn WITH(NOLOCK)
LEFT JOIN tranSenders sen WITH(NOLOCK) ON trn.id = sen.tranId
LEFT JOIN tranReceivers rec WITH(NOLOCK) ON trn.id = rec.tranId
WHERE
sen.firstName = @sFirstName
AND ISNULL(sen.middleName, '') = ISNULL(@sMiddleName, '')
AND ISNULL(sen.lastName1, '') = ISNULL(@sLastName1, '')
AND ISNULL(sen.lastName2, '') = ISNULL(@sLastName2, '')
AND rec.firstName = @rFirstName
AND ISNULL(rec.middleName, '') = ISNULL(@rMiddleName, '')
AND ISNULL(rec.lastName1, '') = ISNULL(@rLastName1, '')
AND ISNULL(rec.lastName2, '') = ISNULL(@rLastName2, '')
AND trn.tAmt = @transferAmt
AND trn.pLocation = @pLocation
AND trn.serviceCharge = @serviceCharge
AND trn.cAmt = @cAmt
AND DATEDIFF(MI, trn.createdDate, @currentDate) <= 5)
BEGIN
SELECT 1 errorCode,'Similar Transaction Found.' msg,NULL id,'compliance' vtype
RETURN
END
IF @transferAmt > @limitBal
BEGIN
SELECT 1 errorCode,'Transfer amount exceeds Limit. Please, Check your available limit.' msg,NULL id,'compliance' vtype
RETURN
END
IF NOT EXISTS (
SELECT
'X'
FROM sendTranLimit
WHERE countryId = @sCountryId
AND (tranType = @collMode OR tranType IS NULL)
AND (paymentType = @deliveryMethod OR paymentType IS NULL)
AND (customerType = @sendingCustType OR customerType IS NULL)
AND currency = @sendingCurrency
AND (receivingCountry = @pCountryId OR receivingCountry IS NULL)
AND ISNULL(minLimitAmt, 0) <= @transferAmt
AND ISNULL(maxLimitAmt, 0) >= @transferAmt
AND ISNULL(isActive, 'N') = 'Y'
)
BEGIN
SELECT 3 errorCode,'Country Sending limit is not defined or exceeds.' msg,NULL id,'compliance' vtype
RETURN
END
SELECT @deliveryMethodId = serviceTypeId FROM serviceTypeMaster WITH(NOLOCK) WHERE typeTitle = @deliveryMethod AND ISNULL(isDeleted, 'N') = 'N'
-------Compliance Check Begin----------
INSERT @csMasterRec(masterId)
SELECT masterId FROM dbo.FNAGetComplianceRuleMaster(@sBranch, @pCountryId, NULL, @pBranch, NULL, @senderId, @receiverId)
SELECT @totalRows = COUNT(*) FROM @csMasterRec
DECLARE @denyTxn CHAR(1) = 'N'
IF EXISTS(SELECT 'X' FROM @csMasterRec)
BEGIN
DELETE FROM remitTranComplianceTemp WHERE agentRefId = @agentUniqueRefId
SET @count = 1
WHILE(@count <= @totalRows)
BEGIN
SELECT @csMasterId = masterId FROM @csMasterRec WHERE rowId = @count
EXEC proc_complianceRuleDetail
@user = @user
,@tranId = @id
,@tAmt = @transferAmt
,@senId = @senderId
,@benId = @receiverId
,@beneficiaryName = @receiverName
,@beneficiaryMobile = @rContactNo
,@benAccountNo = @accountNo
,@masterId = @csMasterId
,@paymentMethod = @deliveryMethodId
,@checkingFor = 'v'
,@agentRefId = @agentUniqueRefId
,@result = @complianceRes OUTPUT
,@senderId = @sIdNo
,@senderName = @senderName
,@senderMobile = @sContactNo
SET @compFinalRes = ISNULL(@compFinalRes, '') + ISNULL(@complianceRes, '')
IF @complianceRes = 'M' AND ISNULL(@complianceAction, '') <> 'B' AND ISNULL(@complianceAction, '') <> 'C'
SET @complianceAction = 'M'
IF @complianceRes = 'C' AND ISNULL(@complianceAction, '') <> 'B'
SET @complianceAction = 'C'
IF @complianceRes = 'B'
SET @complianceAction = 'B'
SET @count = @count + 1
END
END
IF(ISNULL(@compFinalRes, '') <> '')
BEGIN
IF(@compFinalRes <> '')
BEGIN
IF @complianceAction = 'B'
SET @msg = 'WARNING!!! This customer is under compliance'
ELSE IF @complianceAction = 'M'
BEGIN
SET @compApproveRemark = ISNULL(@compApproveRemark, 'Marked for Compliance')
SET @msg = 'WARNING!!! This customer is under compliance'
END
ELSE
SET @msg = 'WARNING!!! This customer is under compliance'
--SELECT 101 errorCode, @msg msg, @complianceAction id, @compApproveRemark compApproveRemark
SELECT 101 errorCode,@msg msg, @complianceAction id, @compApproveRemark compApproveRemark,'compliance' vtype
END
RETURN
END
-------Compliance Check End----------
--EXEC [proc_errorHandler] 0, 'Transaction verification Successful', NULL
SELECT 0 errorCode,'Transaction verification Successful.' msg,NULL id,'compliance' vtype
--*****Check For Same Name*****
SELECT tranId = rt.id, senderName, sIdType = ISNULL(sdv.detailTitle,sen.IdType), sIdNo = sen.idNumber, pAmt, pCountry
FROM vwRemitTran rt WITH(NOLOCK)
INNER JOIN vwTranSenders sen WITH(NOLOCK) ON rt.id = sen.tranId
LEFT JOIN staticDataValue sdv WITH(NOLOCK) ON sen.idType=CAST(sdv.valueId AS VARCHAR)
WHERE senderName = @senderName AND sBranch = @sBranch AND rt.createdDate BETWEEN CONVERT(VARCHAR, GETDATE(), 101) AND CONVERT(VARCHAR, GETDATE(), 101) + ' 23:59:59'
--*****Check For Same Id*****
SELECT tranId = rt.id, senderName, sIdType = ISNULL(sdv.detailTitle,sen.IdType), sIdNo = sen.idNumber, pAmt, pCountry
FROM vwRemitTran rt WITH(NOLOCK)
INNER JOIN vwTranSenders sen WITH(NOLOCK) ON rt.id = sen.tranId
LEFT JOIN staticDataValue sdv WITH(NOLOCK) ON sen.idType=CAST(sdv.valueId AS VARCHAR)
WHERE idType = @sIdType AND idNumber = @sIdNo AND sBranch = @sBranch AND rt.createdDate BETWEEN CONVERT(VARCHAR, GETDATE(), 101) AND CONVERT(VARCHAR, GETDATE(), 101) + ' 23:59:59'
END
ELSE IF @flag = 'i' --Local DB Insert
BEGIN
--Field Validation
IF @user IS NULL
BEGIN
EXEC proc_errorHandler 1, 'Your session has expired. Cannot send transaction', NULL
RETURN
END
IF (
(ISNULL(@pLocation, 0) = 0 and ISNULL(@deliveryMethod, '')='Cash Payment')
OR ISNULL(@deliveryMethod, '') = ''
OR ISNULL(@transferAmt,0) = 0
OR @sFirstName IS NULL
OR @rFirstName IS NULL
)
BEGIN
EXEC proc_errorHandler 1, 'Mandatory Field(s) missing', NULL
RETURN
END
--End Field Validation
SET @controlNo = '777' + LEFT(CAST(ABS(CHECKSUM(NEWID())) AS VARCHAR(10)) + '00000000', 8) + 'B'
SELECT @controlNoEncrypted = dbo.FNAEncryptString(@controlNo)
IF EXISTS(SELECT 'X' FROM dbo.controlNoListDomestic WITH(NOLOCK) WHERE controlNo = @controlNo)
BEGIN
EXEC proc_errorHandler 1, 'Technical error occured. Please try again.', NULL
RETURN
END
--Payout
IF (@pBranch IS NOT NULL)
BEGIN
SELECT @pAgent = parentId, @pBranchName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @pBranch
SELECT @pSuperAgent = parentId, @pAgentName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @pAgent
SELECT @pSuperAgentName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @pSuperAgent
END
IF (@pBankBranch IS NOT NULL)
BEGIN
SELECT @pBank = parentId, @pBankBranchName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @pBankBranch
SELECT @pBankName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @pBank
END
SET @sCountry = 'Nepal'
SET @sCountryId = '151'
SET @pCountry = 'Nepal'
SET @pCountryId = '151'
--Sender
IF (@sBranch IS NULL)
SELECT @sBranch = agentId FROM applicationUsers WITH(NOLOCK) WHERE userName = @user
IF (@sBranch IS NULL)
BEGIN
EXEC proc_errorHandler 1, 'Mandatory Field(s) missing', NULL
RETURN
END
SELECT @agentType = agentType, @sAgent = parentId, @sBranchName = agentName, @sCountry = agentCountry FROM agentMaster WITH(NOLOCK) WHERE agentId = @sBranch
--Check for Branch or Agent Acting as Branch
IF @agentType = 2903
BEGIN
SET @sAgent = @sBranch
END
SELECT DISTINCT @sSuperAgent = parentId, @sAgentName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @sAgent
SELECT DISTINCT @sSuperAgentName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @sSuperAgent
--2. Find Settling Agent
SELECT @settlingAgent = agentId FROM agentMaster WHERE agentId = @sBranch AND isSettlingAgent = 'Y'
IF @settlingAgent IS NULL
SELECT @settlingAgent = agentId FROM agentMaster WHERE agentId = @sAgent AND isSettlingAgent = 'Y'
IF @settlingAgent IS NULL
SELECT @settlingAgent = agentId FROM agentMaster WHERE agentId = @sSuperAgent AND isSettlingAgent = 'Y'
--Validation Start
IF EXISTS(SELECT 'X' FROM customers WHERE mobile = @sContactNo AND ISNULL(isDeleted, 'N') <> 'Y' AND isBlackListed = 'Y')
BEGIN
EXEC proc_errorHandler 1, 'Sending Customer is blacklisted. Cannot proceed transaction', NULL
RETURN
END
IF EXISTS(SELECT 'X' FROM customers WHERE mobile = @rContactNo AND ISNULL(isDeleted, 'N') <> 'Y' AND isBlackListed = 'Y')
BEGIN
EXEC proc_errorHandler 1, 'Receiving Customer is blacklisted. Cannot proceed transaction', NULL
RETURN
END
--3. Check Limit starts
SELECT @collCurr = 'NPR', @payoutCurr = 'NPR'
SELECT @sendingCurrency = @collCurr
SELECT @receivingCurrency = @payoutCurr
SET @limitBal = [dbo].FNAGetLimitBal(@settlingAgent)
SET @currentDate = dbo.FNAGetDateInNepalTz()
IF EXISTS(SELECT 'X' FROM remitTran trn WITH(NOLOCK)
LEFT JOIN tranSenders sen WITH(NOLOCK) ON trn.id = sen.tranId
LEFT JOIN tranReceivers rec WITH(NOLOCK) ON trn.id = rec.tranId
WHERE
sen.firstName = @sFirstName
AND ISNULL(sen.middleName, '') = ISNULL(@sMiddleName, '')
AND ISNULL(sen.lastName1, '') = ISNULL(@sLastName1, '')
AND ISNULL(sen.lastName2, '') = ISNULL(@sLastName2, '')
AND rec.firstName = @rFirstName
AND ISNULL(rec.middleName, '') = ISNULL(@rMiddleName, '')
AND ISNULL(rec.lastName1, '') = ISNULL(@rLastName1, '')
AND ISNULL(rec.lastName2, '') = ISNULL(@rLastName2, '')
AND trn.tAmt = @transferAmt
AND trn.pLocation = @pLocation
AND DATEDIFF(MI, trn.createdDate, @currentDate) <= 5
)
BEGIN
EXEC proc_errorHandler 1, 'Similar Transaction Found', NULL
RETURN
END
IF @transferAmt > @limitBal
BEGIN
EXEC [proc_errorHandler] 1, 'Transfer amount exceeds Limit. Please, Check your available limit.', NULL
RETURN
END
IF EXISTS (
SELECT
'X'
FROM sendTranLimit
WHERE agentId = @settlingAgent
AND (tranType = @collMode OR tranType IS NULL)
AND (paymentType = @deliveryMethod OR paymentType IS NULL)
AND (customerType = @sendingCustType OR customerType IS NULL)
AND currency = @sendingCurrency
AND (receivingCountry = @pCountry OR receivingCountry IS NULL)
AND ISNULL(minLimitAmt, 0) > @transferAmt
AND ISNULL(maxLimitAmt, 0) < @transferAmt
AND ISNULL(isActive, 'N') = 'Y'
)
BEGIN
EXEC [proc_errorHandler] 2, 'Agent Sending limit is exceeded.', NULL
RETURN
END
IF NOT EXISTS (
SELECT
'X'
FROM sendTranLimit
WHERE countryId = @sCountryId
AND (tranType = @collMode OR tranType IS NULL)
AND (paymentType = @deliveryMethod OR paymentType IS NULL)
AND (customerType = @sendingCustType OR customerType IS NULL)
AND currency = @sendingCurrency
AND (receivingCountry = @pCountryId OR receivingCountry IS NULL)
AND ISNULL(minLimitAmt, 0) <= @transferAmt
AND ISNULL(maxLimitAmt, 0) >= @transferAmt
AND ISNULL(isActive, 'N') = 'Y'
)
BEGIN
EXEC [proc_errorHandler] 3, 'Country Sending limit is not defined or exceeds.', NULL
RETURN
END
--5.Txn Amount and Service Charge Validation
SELECT @deliveryMethodId = serviceTypeId FROM serviceTypeMaster WITH(NOLOCK) WHERE typeTitle = @deliveryMethod AND ISNULL(isDeleted, 'N') = 'N'
IF @deliveryMethod = 'Bank Deposit'
BEGIN
SELECT @pLocation = agentLocation FROM agentMaster WITH(NOLOCK) WHERE agentId = @pBankBranch
SET @pAgentComm = 0
SET @pSuperAgentComm = 0
END
ELSE
BEGIN
SELECT
@pAgentComm = ISNULL(pAgentComm, 0)
,@pSuperAgentComm = ISNULL(psAgentComm, 0)
FROM dbo.FNAGetDomesticPayCommForCancel(@sBranch, @pLocation, @deliveryMethodId, @transferAmt)
END
--6.Domestic Service Charge and Commission Calculation
SELECT
@serviceCharge = ISNULL(serviceCharge, 0)
,@sAgentComm = ISNULL(sAgentComm, 0)
,@sSuperAgentComm = ISNULL(ssAgentComm, 0)
FROM dbo.FNAGetDomesticSendComm(@sBranch, NULL, @pLocation, @deliveryMethodId, @transferAmt)
SELECT @sSuperAgentCommCurrency = 'NPR', @sAgentCommCurrency = 'NPR', @pAgentCommCurrency = 'NPR', @pSuperAgentCommCurrency = 'NPR'
IF (@cAmt IS NULL)
BEGIN
SET @cAmt = @transferAmt + @serviceCharge
END
DECLARE @iCollectAmount MONEY
SET @iCollectAmount = @transferAmt + @serviceCharge
IF(@cAmt <> @iCollectAmount)
BEGIN
EXEC proc_errorHandler 1, 'Collection Amount not match', NULL
RETURN
END
IF @transferAmt > @limitBal
BEGIN
EXEC [proc_errorHandler] 1, 'Transfer amount exceeds Limit. Please, Check your available limit.', NULL
RETURN
END
--End of Txn Amount and Service Charge Validation
SET @sFullName = @sFirstName + ISNULL( ' ' + @sMiddleName, '') + ISNULL( ' ' + @sLastName1, '') + ISNULL( ' ' + @sLastName2, '')
SET @rFullName = @rFirstName + ISNULL( ' ' + @rMiddleName, '') + ISNULL( ' ' + @rLastName1, '') + ISNULL( ' ' + @rLastName2, '')
--7.Transaction Insert
BEGIN TRANSACTION
--8.Update A/C Balance
UPDATE creditLimit SET
todaysSent = ISNULL(todaysSent, 0) + ISNULL(@cAmt, 0)
WHERE agentId = @settlingAgent
-- ## customer bonus point
IF (ISNULL(@senderId, 0) <> 0)
BEGIN
DECLARE @maxPointsPerTxn INT, @bonusSchemeId INT, @bonusUnit INT, @bonusPoint INT
SELECT @bonusSchemeId = bonusId
,@maxPointsPerTxn = maxPointsPerTxn
FROM dbo.FNAGetBonusSetupDetail(@sCountryId, @sAgent, @sBranch, @pCountryId, @pAgent)
SELECT @bonusUnit = unit, @bonusPoint = points FROM bonusOperationSetup WITH(NOLOCK) WHERE bonusSchemeId = @bonusSchemeId
IF @bonusSchemeId IS NOT NULL
BEGIN
DECLARE @txnBonusPoint FLOAT
SET @txnBonusPoint = @pAmt * (CAST(@bonusPoint AS FLOAT)/CAST(@bonusUnit AS FLOAT))
SET @txnBonusPoint = CASE WHEN @txnBonusPoint > ISNULL(@maxPointsPerTxn, 0) THEN ISNULL(@maxPointsPerTxn, 0) ELSE ISNULL(@txnBonusPoint, 0) END
UPDATE customerMaster SET
bonusPointPending = ISNULL(bonusPointPending, 0) + @txnBonusPoint
,bonusTxnCount = ISNULL(bonusTxnCount, 0) + 1
,bonusTxnAmount = ISNULL(bonusTxnAmount, 0) + @pAmt
WHERE customerId = @senderId
END
-- ## update total send & activate txn date
UPDATE customerMaster SET
sendTxn = ISNULL(sendTxn,0)+1,
firstTxnDate = ISNULL(firstTxnDate,GETDATE())
WHERE customerId = @senderId
END
--## Start OFAC / Compliance
DECLARE @receiverOfacRes VARCHAR(MAX),
@ofacRes VARCHAR(MAX),
@ofacReason VARCHAR(MAX)
EXEC proc_ofacTrackerDomestic @flag = 't', @name = @sFullName, @Result = @ofacRes OUTPUT
EXEC proc_ofacTrackerDomestic @flag = 't', @name = @rFullName, @Result = @receiverOfacRes OUTPUT
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
INSERT INTO remitTran(
controlNo
,sCurrCostRate
,sCurrHoMargin
,pCurrCostRate
,pCurrHoMargin
,sCurrAgentMargin
,pCurrAgentMargin
,sCurrSuperAgentMargin
,pCurrSuperAgentMargin
,customerRate
,sAgentSettRate
,pDateCostRate
,serviceCharge
,handlingFee
,sAgentComm
,sAgentCommCurrency
,sSuperAgentComm
,sSuperAgentCommCurrency
,sHubComm
,sHubCommCurrency
,pAgentComm
,pAgentCommCurrency
,pSuperAgentComm
,pSuperAgentCommCurrency
,pHubComm
,pHubCommCurrency
,promotionCode
,promotionType
,pMessage
,sSuperAgent
,sSuperAgentName
,sAgent
,sAgentName
,sBranch
,sBranchName
,sCountry
,pSuperAgent
,pSuperAgentName
,pAgent
,pAgentName
,pBranch
,pBranchName
,pCountry
,pState
,pDistrict
,pLocation
,paymentMethod
,pBank
,pBankName
,pBankBranch
,pBankBranchName
,accountNo
,collMode
,collCurr
,tAmt
,cAmt
,pAmt
,payoutCurr
,relWithSender
,purposeOfRemit
,sourceOfFund
,tranStatus
,payStatus
,createdDate
,createdDateLocal
,createdBy
,tranType
,senderName
,receiverName
,bonusPoint
)
SELECT
@controlNoEncrypted
,@sCurrCostRate
,@sCurrHoMargin
,@pCurrCostRate
,@pCurrHoMargin
,@sCurrAgentMargin
,@pCurrAgentMargin
,@sCurrSuperAgentMargin
,@pCurrSuperAgentMargin
,@customerRate
,@sAgentSettRate
,@pDateCostRate
,@serviceCharge
,@handlingFee
,@sAgentComm
,@sAgentCommCurrency
,@sSuperAgentComm
,@sSuperAgentCommCurrency
,@sHubComm
,@sHubCommCurrency
,@pAgentComm
,@pAgentCommCurrency
,@pSuperAgentComm
,@pSuperAgentCommCurrency
,@pHubComm
,@pHubCommCurrency
,@promotionCode
,@promotionType
,@remarks
,@sSuperAgent
,@sSuperAgentName
,@sAgent
,@sAgentName
,@sBranch
,@sBranchName
,@sCountry
,@pSuperAgent
,@pSuperAgentName
,@pAgent
,@pAgentName
,@pBranch
,@pBranchName
,@pCountry
,@pState
,@pDistrict
,@pLocation
,@deliveryMethod
,@pBank
,@pBankName
,@pBankBranch
,@pBankBranchName
,@accountNo
,@collMode
,@collCurr
,@transferAmt
,@cAmt
,@pAmt
,@payoutCurr
,@relationship
,@purpose
,@sourceOfFund
,'Hold'
,'Unpaid'
,dbo.FNAGetDateInNepalTZ()
,dbo.FNAGetDateInNepalTZ()
,@user
,'D'
,@sFullName
,@rFullName
,@txnBonusPoint
SET @id = SCOPE_IDENTITY()
---End of Transaction Insert
---9.Customer Insert
IF (ISNULL(@senderId, 0) <> 0)
BEGIN
INSERT INTO tranSenders(
tranId,customerId,membershipId,firstName,middleName,lastName1,lastName2
,country,[address],[state],district,email,mobile
,dob,idPlaceOfIssue,idType,idNumber,dcInfo,ipAddress,occupation
)
SELECT top 1
@id,@senderId,membershipId,@sFirstName,@sMiddleName,@sLastName1,@sLastName2
,pCountry,@sAddress,pZone,pDistrict,@sEmail,@sContactNo
,dobEng,placeOfIssue,@sIdType,@sIdNo,@sDcInfo,@sIpAddress,@Occupation
FROM customerMaster c WITH(NOLOCK)
WHERE c.customerId = @senderId
END
ELSE
BEGIN
INSERT INTO tranSenders(
tranId,membershipId,firstName,middleName,lastName1,lastName2
,country,[address],[state],zipCode,city,email,homePhone,workPhone,mobile,nativeCountry
,dob,placeOfIssue,idType,idNumber,idPlaceOfIssue,issuedDate,validDate,dcInfo,ipAddress,occupation
)
SELECT
@id,@sMemId,@sFirstName,@sMiddleName,@sLastName1,@sLastName2
,'Nepal',@sAddress,NULL,NULL,NULL,@sEmail,NULL,NULL,@sContactNo,NULL
,@sDOB,@sIdIssuedPlace,@sIdType,@sIdNo,NULL,NULL,NULL,@sDcInfo,@sIpAddress,@Occupation
END
IF (ISNULL(@receiverId, 0) <> 0)
BEGIN
INSERT INTO tranReceivers(
tranId,customerId,membershipId,firstName,middleName,lastName1,lastName2
,country,[address],[state],district,email,mobile
,dob,idPlaceOfIssue,idType,idNumber
)
SELECT top 1
@id,@receiverId,membershipId,@rFirstName,@rMiddleName,@rLastName1,@rLastName2
,pCountry,@rAddress,pZone,pDistrict,email,@rContactNo
,dobEng,c.placeOfIssue,@rIdType,@rIdNo
FROM customerMaster c WITH(NOLOCK)
WHERE c.customerId = @receiverId
END
ELSE
BEGIN
INSERT INTO tranReceivers(
tranId,membershipId,firstName,middleName,lastName1,lastName2
,country,[address],[state],zipCode,city,email,homePhone,workPhone,mobile,nativeCountry
,dob,placeOfIssue,idType,idNumber,idPlaceOfIssue,issuedDate,validDate
)
SELECT
@id,@rMemId,@rFirstName,@rMiddleName,@rLastName1,@rLastName2
,'Nepal',@rAddress,NULL,NULL,NULL,NULL,NULL,NULL,@rContactNo,NULL
,NULL,NULL,@rIdType,@rIdNo,NULL,NULL,NULL
END
--End of Customer Insert
-- Move Txn Document
IF EXISTS(SELECT 'x' FROM dbo.txnDocUploadTEMP WITH(NOLOCK) WHERE batchId = @txnbatchId)
BEGIN
INSERT INTO txnDocUpload (
tranId
,[fileName]
,fileType
,fileDescription
,txnDocFolder
,createdBy
,createdDate
)
SELECT
@id
,[fileName]
,fileType
,fileDescription
,@txnDocFolder
,createdBy
,createdDate FROM dbo.txnDocUploadTEMP WITH(NOLOCK) WHERE batchId = @txnbatchId
DELETE FROM dbo.txnDocUploadTEMP WHERE batchId = @txnbatchId
END
-------Compliance Check Begin----------
IF EXISTS(SELECT 'X' FROM remitTranComplianceTemp WITH(NOLOCK) WHERE agentRefId = @agentUniqueRefId)
BEGIN
INSERT INTO remitTranCompliance(TranId, csDetailTranId, matchTranId)
SELECT @id, csDetailTranId, matchTranId FROM remitTranComplianceTemp WITH(NOLOCK) WHERE agentRefId = @agentUniqueRefId
DELETE FROM dbo.remitTranComplianceTemp WHERE agentRefId = @agentUniqueRefId
SET @compFinalRes = 'C'
END
---------------------------------
IF(ISNULL(@compFinalRes, '') <> '' OR ISNULL(@ofacRes, '') <> '')
BEGIN
IF(ISNULL(@ofacRes, '') <> '' AND ISNULL(@compFinalRes, '') = '')
BEGIN
INSERT remitTranOfac(TranId, blackListId, reason, flag)
SELECT @id, @ofacRes, @ofacReason, dbo.FNAGetOFAC_Flag(@ofacRes)
UPDATE remitTran SET
tranStatus = 'OFAC Hold'
WHERE id = @id
END
ELSE IF(@compFinalRes <> '' AND ISNULL(@ofacRes, '') = '')
BEGIN
IF ISNULL(@complianceAction, '') = 'M'
BEGIN
UPDATE remitTran SET
tranStatus = 'Hold'
WHERE id = @id
UPDATE remitTranCompliance SET
approvedRemarks = @compApproveRemark
,approvedBy = 'system'
,approvedDate = GETDATE()
WHERE tranId = @id
END
ELSE
BEGIN
UPDATE remitTran SET
tranStatus = 'Compliance Hold'
WHERE id = @id
END
END
ELSE IF(ISNULL(@compFinalRes, '') <> '' AND ISNULL(@ofacRes, '') <> '')
BEGIN
INSERT remitTranOfac(TranId, blackListId, reason, flag)
SELECT @id, @ofacRes, @ofacReason, dbo.FNAGetOFAC_Flag(@ofacRes)
IF ISNULL(@complianceAction, '') = 'M'
BEGIN
UPDATE remitTran SET
tranStatus = 'OFAC Hold'
WHERE id = @id
UPDATE remitTranCompliance SET
approvedRemarks = @compApproveRemark
,approvedBy = 'system'
,approvedDate = GETDATE()
WHERE tranId = @id
END
ELSE
BEGIN
UPDATE remitTran SET
tranStatus = 'OFAC/Compliance Hold'
WHERE id = @id
END
END
END
-------Compliance Check End----------
INSERT INTO controlNoListDomestic(controlNo)
SELECT @controlNo
DECLARE @mapCodeDom VARCHAR(20)
SELECT @mapCodeDom = mapCodeDom FROM agentMaster WITH(NOLOCK) WHERE agentId = @sBranch
IF NOT EXISTS(SELECT 'X' FROM FastMoneyPro_account.dbo.REMIT_TRN_LOCAL WITH(NOLOCK) WHERE TRN_REF_NO = dbo.encryptDbLocal(@controlNo))
BEGIN
EXEC FastMoneyPro_account.dbo.[PROC_REMIT_DATA_UPDATE]
@flag = 's'
,@controlNo = @controlNo
,@mapCode = @mapCodeDom
,@sFirstName = @sFirstName
,@sMiddleName = @sMiddleName
,@sLastName1 = @sLastName1
,@sLastName2 = @sLastName2
,@rFirstName = @rFirstName
,@rMiddleName = @rMiddleName
,@rLastName1 = @rLastName1
,@rLastName2 = @rLastName2
,@cAmt = @cAmt
,@pAmt = @pAmt
,@serviceCharge = @serviceCharge
,@sAgentComm = @sAgentComm
,@pAgentComm = @pAgentComm
,@pBank = @pBank
,@pBankName = @pBankName
,@pBankBranch = @pBankBranch
,@deliveryMethod = @deliveryMethod
,@user = @user
,@tranId = @id
END
EXEC Proc_AgentBalanceUpdate @flag = 's',@tAmt = @cAmt ,@settlingAgent = @settlingAgent
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
EXEC [proc_errorHandler] 0, 'Transaction sent successfully and is waiting for approval', @controlNo
IF @remarks IS NOT NULL
BEGIN
INSERT INTO tranModifyLog(tranId,[message],createdBy,createdDate,MsgType,[status])
SELECT @id,@remarks,@user,dbo.FNAGetDateInNepalTZ(),'','Not Resolved'
END
/*Topup Information Send SMS*/
IF @sMemId IS NOT NULL
BEGIN
EXEC proc_topupQueue
@flag = 'a'
,@user = @user
,@tranId = @id
,@tranType = 'D'
,@mode = 's'
END
END
ELSE IF @flag = 'scTBL'
BEGIN
DECLARE @masterId INT
IF @sBranch IS NULL
SELECT @sBranch = agentId FROM applicationUsers WITH(NOLOCK) WHERE userName = @user
SELECT @deliveryMethodId = serviceTypeId FROM serviceTypeMaster WITH(NOLOCK) WHERE typeTitle = @deliveryMethod AND ISNULL(isDeleted, 'N') = 'N'
IF @deliveryMethod = 'Bank Deposit'
BEGIN
SELECT @pLocation = agentLocation FROM agentMaster WITH(NOLOCK) WHERE agentId = @pBankBranch
END
SELECT
@masterId = masterId
FROM dbo.FNAGetDomesticSendComm(@sBranch, NULL, @pLocation, @deliveryMethodId, @transferAmt)
SELECT
fromAmt = fromAmt
,toAmt = toAmt
,pcnt = serviceChargePcnt
,maxAmt = serviceChargeMaxAmt
,minAmt = serviceChargeMinAmt
FROM scDetail WHERE scMasterId = @masterId
ORDER BY fromAmt
END
ELSE IF @flag = 'sc'
BEGIN
IF @sBranch IS NULL
SELECT @sBranch = agentId FROM applicationUsers WITH(NOLOCK) WHERE userName = @user
SELECT @deliveryMethodId = serviceTypeId FROM serviceTypeMaster WITH(NOLOCK) WHERE typeTitle = @deliveryMethod AND ISNULL(isDeleted, 'N') = 'N'
IF @deliveryMethod = 'Bank Deposit'
BEGIN
SELECT @pLocation = agentLocation FROM agentMaster WITH(NOLOCK) WHERE agentId = @pBankBranch
END
ELSE IF @deliveryMethod = 'Cash Payment' AND @transferAmt > dbo.FNAGetDomesticSendLimit() --AND @transferAmt > 100000
BEGIN
EXEC [proc_errorHandler] 1, 'Transfer amount exceeds Limit.', NULL
RETURN
END
SELECT sc = ISNULL(serviceCharge, -1) FROM dbo.FNAGetDomesticSendComm(@sBranch, NULL, @pLocation, @deliveryMethodId, @transferAmt)
END
ELSE IF @flag = 'acBal'
BEGIN
SELECT @sAgent = parentId, @agentType = agentType FROM agentMaster WITH(NOLOCK) WHERE agentId = @sBranch
IF @agentType = 2903
SET @sAgent = @sBranch
SELECT @sSuperAgent = parentId FROM agentMaster WITH(NOLOCK) WHERE agentId = @sAgent
SELECT @settlingAgent = agentId FROM agentMaster WITH(NOLOCK) WHERE agentId = @sBranch AND isSettlingAgent = 'Y'
IF @settlingAgent IS NULL
SELECT @settlingAgent = agentId FROM agentMaster WITH(NOLOCK) WHERE agentId = @sAgent AND isSettlingAgent = 'Y'
IF @settlingAgent IS NULL
SELECT @settlingAgent = agentId FROM agentMaster WITH(NOLOCK) WHERE agentId = @sSuperAgent AND isSettlingAgent = 'Y'
SELECT
availableBal = ISNULL(dbo.FNAGetLimitBal(@settlingAgent), 0)
,balCurrency = cm.currencyCode
,limExpiry = ISNULL(CONVERT(VARCHAR, expiryDate, 101), 'N/A')
FROM creditLimit cl
LEFT JOIN currencyMaster cm WITH(NOLOCK) ON cl.currency = cm.currencyId
WHERE agentId = @settlingAgent
END
ELSE IF @flag = 'sc-v2'
BEGIN
IF @user IS NULL
BEGIN
EXEC proc_errorHandler 1, 'Your session has expired. Cannot send transaction', NULL
RETURN
END
IF @sBranch IS NULL
BEGIN
EXEC proc_errorHandler 1, 'Sending Branch is missing. Cannot send transaction', NULL
RETURN
END
IF @deliveryMethod = 'Cash Payment' AND @transferAmt > dbo.FNAGetDomesticSendLimit() --AND @transferAmt > 100000
BEGIN
EXEC [proc_errorHandler] 1, 'Transfer amount exceeds Limit.', NULL
RETURN
END
--PRINT @deliveryMethod
--PRINT @transferAmt
--PRINT dbo.FNAGetDomesticSendLimit()
DECLARE @agentLocation INT, @agentGrp INT
SELECT
@agentType = agentType,
@sAgent = parentId,
@sBranchName = agentName,
@sCountry = agentCountry,
@agentLocation = agentLocation,
@agentGrp = agentGrp
FROM agentMaster WITH(NOLOCK) WHERE agentId = @sBranch
IF @agentType = 2903
BEGIN
SET @sAgent = @sBranch
END
SELECT DISTINCT @sSuperAgent = parentId, @sAgentName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @sAgent
SELECT DISTINCT @sSuperAgentName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @sSuperAgent
SELECT @settlingAgent = agentId FROM agentMaster WHERE agentId = @sBranch AND isSettlingAgent = 'Y'
IF @settlingAgent IS NULL
SELECT @settlingAgent = agentId FROM agentMaster WHERE agentId = @sAgent AND isSettlingAgent = 'Y'
IF @settlingAgent IS NULL
SELECT @settlingAgent = agentId FROM agentMaster WHERE agentId = @sSuperAgent AND isSettlingAgent = 'Y'
SET @limitBal = [dbo].FNAGetLimitBal(@settlingAgent)
IF @transferAmt > @limitBal
BEGIN
EXEC [proc_errorHandler] 1, 'Transfer amount exceeds Limit. Please, Check your available limit.', NULL
RETURN
END
SELECT @deliveryMethodId = serviceTypeId FROM serviceTypeMaster WITH(NOLOCK) WHERE typeTitle = @deliveryMethod AND ISNULL(isDeleted, 'N') = 'N'
IF @deliveryMethod = 'Bank Deposit'
BEGIN
SELECT @pLocation = agentLocation FROM agentMaster WITH(NOLOCK) WHERE agentId = @pBankBranch
END
SELECT @serviceCharge = ISNULL(serviceCharge, 0)
FROM dbo.FNAGetDomesticSendComm(@sBranch, NULL, @pLocation, @deliveryMethodId, @transferAmt)
if @serviceCharge = 0
BEGIN
EXEC [proc_errorHandler] 1, 'Service Charge not defined.', NULL
RETURN
END
SET @cAmt = @transferAmt + @serviceCharge
SELECT
errorCode = 0,
serviceCharge = dbo.ShowDecimal(@serviceCharge),
cAmt = dbo.ShowDecimal(@cAmt),
agentLocation = @agentLocation,
agentGrp = @agentGrp
RETURN
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
SELECT 1 errorCode, ERROR_MESSAGE() msg, null id
END CATCH
GO