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.
669 lines
23 KiB
669 lines
23 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[proc_payTranHo] Script Date: 7/4/2019 11:35:48 AM ******/
|
|
DROP PROCEDURE [dbo].[proc_payTranHo]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[proc_payTranHo] Script Date: 7/4/2019 11:35:48 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
CREATE PROC [dbo].[proc_payTranHo] (
|
|
@flag VARCHAR(50)
|
|
,@controlNo VARCHAR(20) = NULL
|
|
,@user VARCHAR(30) = NULL
|
|
,@agentRefId VARCHAR(20) = NULL
|
|
,@payTokenId VARCHAR(20) = NULL
|
|
,@tranId INT = NULL
|
|
|
|
,@sAgentName VARCHAR(200) = NULL
|
|
,@txnDate DATETIME = NULL
|
|
,@sFirstName VARCHAR(30) = NULL
|
|
,@sMiddleName VARCHAR(30) = NULL
|
|
,@sLastName1 VARCHAR(30) = NULL
|
|
,@sLastName2 VARCHAR(30) = NULL
|
|
,@sMemId VARCHAR(30) = NULL
|
|
,@sId BIGINT = NULL
|
|
,@sCountry VARCHAR(50) = NULL
|
|
,@sAddress VARCHAR(100) = NULL
|
|
,@sCity VARCHAR(50) = NULL
|
|
,@sMobile VARCHAR(20) = NULL
|
|
,@sTranId VARCHAR(50) = NULL
|
|
|
|
,@rFirstName VARCHAR(30) = NULL
|
|
,@rMiddleName VARCHAR(30) = NULL
|
|
,@rLastName1 VARCHAR(30) = NULL
|
|
,@rLastName2 VARCHAR(30) = NULL
|
|
,@rMemId VARCHAR(30) = NULL
|
|
,@rId BIGINT = NULL
|
|
,@rCountry VARCHAR(50) = NULL
|
|
,@rAddress VARCHAR(100) = NULL
|
|
,@rCity VARCHAR(50) = NULL
|
|
,@rMobile VARCHAR(20) = NULL
|
|
|
|
,@rIdType VARCHAR(30) = NULL
|
|
,@rIdNumber VARCHAR(30) = NULL
|
|
,@rPlaceOfIssue VARCHAR(50) = NULL
|
|
,@rIssuedDate DATETIME = NULL
|
|
,@rValidDate DATETIME = NULL
|
|
,@rRelationType VARCHAR(50) = NULL
|
|
,@rRelativeName VARCHAR(100) = NULL
|
|
,@payoutAmt MONEY = NULL
|
|
,@payoutCurr VARCHAR(3) = NULL
|
|
,@paymentType VARCHAR(30) = NULL
|
|
,@sLocation INT = NULL
|
|
,@pLocation INT = NULL
|
|
|
|
,@msgType CHAR(1) = NULL
|
|
|
|
,@pBranch INT = NULL
|
|
,@customerId INT = NULL
|
|
,@sortBy VARCHAR(50) = NULL
|
|
,@sortOrder VARCHAR(5) = NULL
|
|
,@pageSize INT = NULL
|
|
,@pageNumber INT = NULL
|
|
)
|
|
AS
|
|
|
|
DECLARE
|
|
@select_field_list VARCHAR(MAX)
|
|
,@extra_field_list VARCHAR(MAX)
|
|
,@table VARCHAR(MAX)
|
|
,@sql_filter VARCHAR(MAX)
|
|
|
|
SET NOCOUNT ON
|
|
SET XACT_ABORT ON
|
|
|
|
SELECT @pageSize = 1000, @pageNumber = 1
|
|
|
|
DECLARE
|
|
@sBranch INT
|
|
,@pSuperAgent INT
|
|
,@pSuperAgentName VARCHAR(100)
|
|
,@pAgent INT
|
|
,@pAgentName VARCHAR(100)
|
|
,@pBranchName VARCHAR(100)
|
|
,@pCountry VARCHAR(100)
|
|
,@pState VARCHAR(100)
|
|
,@pDistrict VARCHAR(100)
|
|
,@deliveryMethod VARCHAR(100)
|
|
,@tAmt MONEY
|
|
,@cAmt MONEY
|
|
,@pAmt MONEY
|
|
,@serviceCharge MONEY
|
|
,@pAgentComm MONEY
|
|
,@pAgentCommCurrency VARCHAR(3)
|
|
,@pSuperAgentComm MONEY
|
|
,@pSuperAgentCommCurrency VARCHAR(3)
|
|
,@pHubComm MONEY
|
|
,@pHubCommCurrency VARCHAR(3)
|
|
,@collMode INT
|
|
,@sendingCustType INT
|
|
,@receivingCurrency INT
|
|
,@senderId INT
|
|
,@payoutMethod INT
|
|
,@agentType INT
|
|
,@actAsBranchFlag CHAR(1)
|
|
,@tokenId BIGINT
|
|
,@controlNoEncrypted VARCHAR(20)
|
|
,@agentLocation INT
|
|
|
|
DECLARE
|
|
@settlingAgent INT
|
|
,@pCountryId INT
|
|
SELECT @controlNoEncrypted = dbo.FNAEncryptString(UPPER(@controlNo))
|
|
|
|
IF @flag = 'details'
|
|
BEGIN
|
|
SELECT
|
|
trn.id
|
|
,dbo.FNADecryptString(trn.controlNo)
|
|
|
|
,sMemId = sen.membershipId
|
|
,sCustomerId = sen.customerId
|
|
,senderName = sen.firstName + ISNULL( ' ' + sen.middleName, '') + ISNULL( ' ' + sen.lastName1, '') + ISNULL( ' ' + sen.lastName2, '')
|
|
,sCountryName = sen.country
|
|
,sStateName = sen.state
|
|
,sCity = sen.city
|
|
,sAddress = sen.address
|
|
|
|
,rMemId = rec.membershipId
|
|
,rCustomerId = rec.customerId
|
|
,receiverName = rec.firstName + ISNULL( ' ' + rec.middleName, '') + ISNULL( ' ' + rec.lastName1, '') + ISNULL( ' ' + rec.lastName2, '')
|
|
,rCountryName = rec.country
|
|
,rStateName = rec.state
|
|
,rCity = rec.city
|
|
,rAddress = rec.address
|
|
|
|
,rIdType = rci.idType
|
|
,rIdNumber = rci.idNumber
|
|
,rPlaceOfIssue = rci.placeOfIssue
|
|
,rIssuedDate = rci.issuedDate
|
|
,rValidDate = rci.validDate
|
|
|
|
,sAgentCountry = trn.sCountry
|
|
|
|
,relationship = trn.relWithSender
|
|
,purpose = trn.purposeOfRemit
|
|
,trn.pAmt
|
|
,collMode = trn.collMode
|
|
,paymentMethod = trn.paymentMethod
|
|
,trn.payoutCurr
|
|
,sAgent = trn.sAgentName
|
|
,trn.tranStatus
|
|
,trn.payStatus
|
|
,pMessage = ISNULL(trn.pMessage, '-')
|
|
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
|
|
LEFT JOIN customerIdentity rci WITH(NOLOCK) ON rec.customerId = rci.customerId
|
|
WHERE trn.controlNo = @controlNoEncrypted
|
|
|
|
END
|
|
|
|
ELSE IF @flag = 'paySearch' --Pay Search Local
|
|
BEGIN
|
|
/*
|
|
EXEC proc_payTran @flag = 'paySearch', @user = 'bharat', @controlNo = '91598256530', @agentRefId = 'buruysvr5v5k1pfxxjgfq00f'
|
|
*/
|
|
DECLARE @tranStatus VARCHAR(20) = NULL
|
|
SELECT @tranStatus = tranStatus, @tranId = id FROM remitTran WITH(NOLOCK) WHERE controlNo = @controlNoEncrypted
|
|
|
|
SELECT @agentType = agentType, @pLocation = agentLocation FROM agentMaster WITH(NOLOCK) WHERE agentId = @pBranch
|
|
IF @agentType = 2903 --Agent
|
|
BEGIN
|
|
SET @pAgent = @pBranch
|
|
END
|
|
|
|
--Validation Starts----------------------------------------------------------------------------------------------
|
|
IF (@tranStatus IS NOT NULL)
|
|
BEGIN
|
|
INSERT INTO tranViewHistory(
|
|
controlNumber
|
|
,tranViewType
|
|
,agentId
|
|
,createdBy
|
|
,createdDate
|
|
,tranId
|
|
)
|
|
SELECT
|
|
@controlNoEncrypted
|
|
,'PAY'
|
|
,@pBranch
|
|
,@user
|
|
,GETDATE()
|
|
,@tranId
|
|
|
|
SET @payTokenId = SCOPE_IDENTITY()
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
EXEC proc_errorHandler 1000, 'No Transaction Found', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
|
|
IF EXISTS(SELECT 'X' FROM remitTran WITH(NOLOCK) WHERE controlNo = @controlNoEncrypted AND paymentMethod = 'Bank Deposit')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Cannot process payment for Payment Type "Bank Deposit"', NULL
|
|
RETURN
|
|
END
|
|
IF (EXISTS(SELECT 'X' FROM remitTran WHERE controlNo = @controlNoEncrypted AND sBranch = @pBranch)
|
|
OR EXISTS(SELECT 'X' FROM remitTran WHERE controlNo = @controlNoEncrypted AND sAgent = @pAgent))
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Cannot process payment for same POS', @tranId
|
|
RETURN
|
|
END
|
|
|
|
IF (@tranStatus = 'Paid')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction has already been paid', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
IF (@tranStatus = 'Hold')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction is hold', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
IF (@tranStatus = 'Cancel')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction is cancelled', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
|
|
IF (@tranStatus = 'CancelRequest')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction has been requested for cancel', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
IF (@tranStatus = 'Lock' )
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction is locked. Please Contact HO', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
IF (@tranStatus = 'Block')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction is blocked. Please Contact HO', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
|
|
|
|
DECLARE @tranDistrictId INT, @payAgentDistrictId INT
|
|
|
|
SELECT @payAgentDistrictId = districtId FROM apiLocationMapping WITH(NOLOCK) WHERE apiDistrictCode = @pLocation
|
|
SELECT @tranDistrictId = districtId FROM apiLocationMapping WITH(NOLOCK) WHERE apiDistrictCode = (SELECT pLocation FROM remitTran WITH(NOLOCK) WHERE controlNo = @controlNoEncrypted)
|
|
IF @payAgentDistrictId IS NULL
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Location not found. Please Contact HO', @controlNo
|
|
RETURN
|
|
END
|
|
IF @tranDistrictId IS NULL
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Location not found. Please Contact HO', @controlNo
|
|
RETURN
|
|
END
|
|
IF(@tranDistrictId <> @payAgentDistrictId)
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'You are not allowed to pay this TXN. It is not within your district.', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
|
|
EXEC proc_errorHandler 0, 'Transaction Verification Successful', @tranId
|
|
|
|
--End of Validation--------------------------------------------------------------------------------------------------
|
|
|
|
--Select payout details----------------------------------------------------------------------------------------------
|
|
SELECT
|
|
trn.id
|
|
,controlNo = dbo.FNADecryptString(trn.controlNo)
|
|
,sMemId = sen.membershipId
|
|
,sCustomerId = sen.customerId
|
|
,senderName = sen.firstName + ISNULL( ' ' + sen.middleName, '') + ISNULL( ' ' + sen.lastName1, '') + ISNULL( ' ' + sen.lastName2, '')
|
|
,sCountryName = sen.country
|
|
,sStateName = sen.state
|
|
,sDistrict = sen.district
|
|
,sCity = sen.city
|
|
,sAddress = sen.address
|
|
,sContactNo = COALESCE(sen.mobile, sen.homephone, sen.workphone)
|
|
|
|
,sIdType = sen.idType
|
|
,sIdNo = sen.idNumber
|
|
,sValidDate = sen.validDate
|
|
,sEmail = sen.email
|
|
|
|
,rMemId = rec.membershipId
|
|
,rCustomerId = rec.customerId
|
|
,receiverName = rec.firstName + ISNULL( ' ' + rec.middleName, '') + ISNULL( ' ' + rec.lastName1, '') + ISNULL( ' ' + rec.lastName2, '')
|
|
,rCountryName = rec.country
|
|
,rStateName = rec.state
|
|
,rDistrict = rec.district
|
|
,rCity = rec.city
|
|
,rAddress = rec.address
|
|
,rContactNo = COALESCE(rec.mobile, rec.homephone, rec.workphone)
|
|
,rIdType = rec.idType
|
|
,rIdNo = rec.idNumber
|
|
|
|
,sAgent = trn.sBranchName
|
|
,sAgentCountry = sa.agentCountry
|
|
|
|
,pBranchName = ISNULL(trn.pBranchName, 'Any')
|
|
,pCountryName = trn.pCountry
|
|
,pStateName = trn.pState
|
|
,pDistrictName = trn.pDistrict
|
|
,pLocationName = pLoc.districtName
|
|
,pAddress = pa.agentAddress
|
|
|
|
,trn.tAmt
|
|
,trn.serviceCharge
|
|
,handlingFee = ISNULL(trn.handlingFee, 0)
|
|
,trn.cAmt
|
|
,trn.pAmt
|
|
|
|
,relationship = ISNULL(trn.relWithSender, '-')
|
|
,purpose = ISNULL(trn.purposeOfRemit, '-')
|
|
,sourceOfFund = ISNULL(trn.sourceOfFund, '-')
|
|
,trn.pAmt
|
|
,collMode = trn.collMode
|
|
,paymentMethod = trn.paymentMethod
|
|
,trn.payoutCurr
|
|
,trn.tranStatus
|
|
,trn.payStatus
|
|
,payoutMsg = ISNULL(trn.pMessage, '-')
|
|
,send_agent = COALESCE(trn.sBranchName, trn.sAgentName)
|
|
,txn_date = trn.createdDateLocal
|
|
,payTokenId = @payTokenId
|
|
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
|
|
|
|
LEFT JOIN agentMaster sa WITH(NOLOCK) ON trn.sBranch = sa.agentId
|
|
LEFT JOIN agentMaster pa WITH(NOLOCK) ON trn.pBranch = pa.agentId
|
|
LEFT JOIN api_districtList pLoc WITH(NOLOCK) ON trn.pLocation = pLoc.districtCode
|
|
WHERE trn.controlNo = @controlNoEncrypted
|
|
--End of Select payout details--------------------------------------------------------------------------------------
|
|
|
|
--Lock Transaction--------------------------------------------------------------------------------------------------
|
|
UPDATE remitTran SET
|
|
payTokenId = @payTokenId
|
|
,tranStatus = 'Lock'
|
|
,lockedBy = @user
|
|
,lockedDate = GETDATE()
|
|
,lockedDateLocal = dbo.FNADateFormatTZ(GETDATE(), @user)
|
|
WHERE controlNo = @controlNoEncrypted
|
|
--End of Lock Transaction--------------------------------------------------------------------------------------------
|
|
|
|
--Log Details-------------------------------------------------------------------------------------------------------
|
|
SELECT
|
|
message
|
|
--,createdBy = au.firstName + ISNULL( ' ' + au.middleName, '') + ISNULL( ' ' + au.lastName, '')
|
|
,trn.createdBy
|
|
,trn.createdDate
|
|
FROM tranModifyLog trn WITH(NOLOCK)
|
|
LEFT JOIN applicationUsers au WITH(NOLOCK) ON trn.createdBy = au.userName
|
|
WHERE trn.tranId = @tranId OR trn.controlNo = @controlNoEncrypted
|
|
ORDER BY trn.createdDate DESC
|
|
--End of Log Details------------------------------------------------------------------------------------------------
|
|
END
|
|
|
|
ELSE IF @flag = 'payUpdate' --Pay Update Local
|
|
BEGIN
|
|
IF @user IS NULL
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Your session has expired. Cannot pay transaction', NULL
|
|
RETURN
|
|
END
|
|
--1.Start of Validation--------------------------------------------------------------------------------------------------
|
|
SELECT
|
|
@tranStatus = tranStatus
|
|
,@deliveryMethod = paymentMethod
|
|
,@sCountry = sCountry
|
|
,@pLocation = pLocation
|
|
,@tAmt = tAmt
|
|
,@cAmt = cAmt
|
|
,@pAmt = pAmt
|
|
,@serviceCharge = serviceCharge
|
|
,@payoutCurr = payoutCurr
|
|
FROM remitTran WITH(NOLOCK) WHERE controlNo = @controlNoEncrypted
|
|
|
|
IF (@tranStatus = 'CancelRequest')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction has been requested for cancel', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
IF NOT EXISTS(SELECT 'X' FROM remitTran WITH(NOLOCK) WHERE controlNo = @controlNoEncrypted AND lockedBy = @user AND (payTokenId = @payTokenId OR payTokenId IS NULL))
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction is locked. Please Contact HO', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
IF (@tranStatus = 'Block')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction is blocked. Please Contact HO', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
IF (@tranStatus = 'Paid')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction has already been paid', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
IF (@tranStatus = 'Hold')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction is hold', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
IF (@tranStatus = 'Cancel')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transaction is cancelled', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
DECLARE @userId INT, @payPerTxn MONEY, @payPerDay MONEY, @payTodays MONEY
|
|
SELECT @userId = userId FROM applicationUsers WITH(NOLOCK) WHERE userName = @user AND ISNULL(isDeleted, 'N') = 'N'
|
|
SELECT @payPerDay = payPerDay, @payPerTxn = payPerTxn, @payTodays = ISNULL(payTodays, 0) FROM userWiseTxnLimit WITH(NOLOCK) WHERE userId = @userId AND ISNULL(isActive, 'N') = 'Y' AND ISNULL(isDeleted, 'N') = 'N'
|
|
IF(@pAmt > @payPerTxn)
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Transfer Amount exceeds user per Pay Transaction Limit.', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
IF(@payTodays > @payPerDay)
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'User Per Day Pay Transaction Limit exceeded.', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
|
|
SELECT @agentType = agentType, @agentLocation = agentLocation FROM agentMaster WITH(NOLOCK) WHERE agentId = @pBranch
|
|
IF @agentType = 2903 --Agent
|
|
BEGIN
|
|
SET @pAgent = @pBranch
|
|
END
|
|
|
|
SELECT @payAgentDistrictId = districtId FROM apiLocationMapping WHERE apiDistrictCode = @agentLocation
|
|
SELECT @tranDistrictId = districtId FROM apiLocationMapping WHERE apiDistrictCode = @pLocation
|
|
IF @payAgentDistrictId IS NULL
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Location not found. Please Contact HO', @controlNo
|
|
RETURN
|
|
END
|
|
IF @tranDistrictId IS NULL
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Location not found. Please Contact HO', @controlNo
|
|
RETURN
|
|
END
|
|
IF(@tranDistrictId <> @payAgentDistrictId)
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'You are not allowed to pay this TXN. It is not within your district.', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
|
|
IF EXISTS(SELECT 'X' FROM remitTran WITH(NOLOCK) WHERE controlNo = @controlNoEncrypted AND sBranch = @pBranch)
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Cannot process for same POS', @controlNoEncrypted
|
|
RETURN
|
|
END
|
|
--End Of Validation-----------------------------------------------------------------------------------------------
|
|
|
|
--2.Find Sending Agent and Payout Agent Details-------------------------------------------------------------------
|
|
SELECT
|
|
@pCountry = agentCountry
|
|
,@pState = agentState
|
|
,@pDistrict = agentDistrict
|
|
FROM agentMaster WITH(NOLOCK) WHERE agentId = @pBranch
|
|
|
|
--Sending Agent
|
|
SELECT @sBranch = sBranch FROM remitTran WITH(NOLOCK) WHERE controlNo = @controlNoEncrypted
|
|
IF @sBranch IS NULL
|
|
SELECT @sBranch = sAgent FROM remitTran WITH(NOLOCK) WHERE controlNo = @controlNoEncrypted
|
|
|
|
SELECT @sLocation = agentLocation FROM agentMaster WITH(NOLOCK) WHERE agentId = @sBranch
|
|
|
|
--Payout
|
|
SELECT @agentType = agentType, @pBranchName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @pBranch
|
|
|
|
--Check for Branch or Agent Acting as Branch
|
|
IF @agentType = 2903 --Agent
|
|
BEGIN
|
|
SET @pAgent = @pBranch
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT @pAgent = parentId, @pBranchName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @pBranch
|
|
END
|
|
SELECT @pSuperAgent = parentId, @pAgentName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @pAgent
|
|
SELECT @pSuperAgentName = agentName FROM agentMaster WITH(NOLOCK) WHERE agentId = @pSuperAgent
|
|
--End of Find Sending Agent and Payout Agent Details------------------------------------------------------------
|
|
|
|
--Check Sending List From Table dbo.rsList----------------------------------------------------------------------
|
|
--IF EXISTS(SELECT 'X' FROM rsList WITH(NOLOCK)
|
|
-- WHERE agentId = @pBranch
|
|
-- AND rsAgentId = @sBranch
|
|
-- AND agentRole = 's'
|
|
-- AND listType = 'ex'
|
|
-- AND ISNULL(isDeleted, 'N') <> 'Y'
|
|
-- AND ISNULL(isActive, 'Y') = 'Y')
|
|
--BEGIN
|
|
-- EXEC proc_errorHandler 1, 'You are not allowed to pay this transaction', NULL
|
|
-- RETURN
|
|
--END
|
|
--IF NOT EXISTS(SELECT 'X' FROM rsList WITH(NOLOCK)
|
|
-- WHERE agentId = @pBranch
|
|
-- AND rsAgentId = @sBranch
|
|
-- AND agentRole = 's'
|
|
-- AND listType = 'in'
|
|
-- AND ISNULL(isDeleted, 'N') <> 'Y'
|
|
-- AND ISNULL(isActive, 'Y') = 'Y')
|
|
--BEGIN
|
|
-- EXEC proc_errorHandler 1, 'You are not allowed to pay this transaction', NULL
|
|
-- RETURN
|
|
--END
|
|
--End of Checking Sending List------------------------------------------------------------------------------------
|
|
|
|
--3.Find Settlement Agent-----------------------------------------------------------------------------------------
|
|
SELECT @settlingAgent = agentId FROM agentMaster WHERE agentId = @pBranch AND isSettlingAgent = 'Y'
|
|
IF @settlingAgent IS NULL
|
|
SELECT @settlingAgent = agentId FROM agentMaster WHERE agentId = @pAgent AND isSettlingAgent = 'Y'
|
|
IF @settlingAgent IS NULL
|
|
SELECT @settlingAgent = agentId FROM agentMaster WHERE agentId = @pSuperAgent AND isSettlingAgent = 'Y'
|
|
--End of Find Settlement Agent-------------------------------------------------------------------------------
|
|
|
|
--4.Commission Calculation Start-------------------------------------------------------------------------------
|
|
DECLARE @deliveryMethodId INT
|
|
SELECT @deliveryMethodId = serviceTypeId FROM serviceTypeMaster WITH(NOLOCK) WHERE ISNULL(isDeleted, 'N') = 'N' AND typeTitle = @deliveryMethod
|
|
|
|
IF @sCountry = 'Nepal'
|
|
BEGIN
|
|
DECLARE @commissionCheck MONEY
|
|
SELECT
|
|
@pAgentComm = ISNULL(pAgentComm, 0)
|
|
,@pSuperAgentComm = ISNULL(psAgentComm, 0)
|
|
,@commissionCheck = pAgentComm
|
|
FROM dbo.FNAGetDomesticPayComm(@sBranch, @pBranch, @deliveryMethodId, @tAmt)
|
|
|
|
SELECT @pAgentCommCurrency = 'NPR', @pSuperAgentCommCurrency = 'NPR'
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
DECLARE @sCountryId INT
|
|
SELECT @pCountryId = countryId FROM countryMaster WITH(NOLOCK) WHERE countryName = @pCountry
|
|
SELECT @sCountryId = countryId FROM countryMaster WITH(NOLOCK) WHERE countryName = @sCountry
|
|
|
|
--SELECT @pHubComm = ISNULL(amount, 0), @pHubCommCurrency = commissionCurrency FROM dbo.FNAGetPayCommHub(@sBranch, @sCountryId, @sLocation, @pSuperAgent, @pCountryId, @pLocation, @pBranch, @payoutCurr, @deliveryMethodId, @cAmt, @pAmt, @serviceCharge, 0, 0)
|
|
SELECT @pSuperAgentComm = ISNULL(amount, 0), @pSuperAgentCommCurrency = commissionCurrency
|
|
FROM dbo.FNAGetPayCommSA(@sBranch, @sCountryId, @sLocation, @pSuperAgent, @pCountryId, @pLocation, @pBranch, @payoutCurr, @deliveryMethodId, @cAmt, @pAmt, @serviceCharge, @pHubComm, 0)
|
|
SELECT @pAgentComm = ISNULL(amount, 0), @commissionCheck = amount, @pAgentCommCurrency = commissionCurrency
|
|
FROM dbo.FNAGetPayComm(@sBranch, @sCountryId, @sLocation, @pSuperAgent, @pCountryId, @pLocation, @pBranch, @payoutCurr, @deliveryMethodId, @cAmt,
|
|
|
|
@pAmt, @serviceCharge, @pHubComm, @pSuperAgentComm)
|
|
END
|
|
|
|
IF @commissionCheck IS NULL
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Pay Commission has not been defined', NULL
|
|
RETURN
|
|
END
|
|
--Commission Calculation End---------------------------------------------------------------------------------
|
|
|
|
BEGIN TRANSACTION
|
|
--5.Update Transaction Record----------------------------------------------------------------------------
|
|
UPDATE remitTran SET
|
|
pAgentComm = @pAgentComm
|
|
,pAgentCommCurrency = @pAgentCommCurrency
|
|
,pSuperAgentComm = @pSuperAgentComm
|
|
,pSuperAgentCommCurrency = @pSuperAgentCommCurrency
|
|
,pHubComm = @pHubComm
|
|
,pHubCommCurrency = @pHubCommCurrency
|
|
,pBranch = @pBranch
|
|
,pBranchName = @pBranchName
|
|
,pAgent = @pAgent
|
|
,pAgentName = @pAgentName
|
|
,pSuperAgent = @pSuperAgent
|
|
,pSuperAgentName = @pSuperAgentName
|
|
,pCountry = @pCountry
|
|
,pState = @pState
|
|
,pDistrict = @pDistrict
|
|
,tranStatus = 'Paid'
|
|
,payStatus = 'Paid'
|
|
,paidDate = GETDATE()
|
|
,paidDateLocal = DBO.FNADateFormatTZ(GETDATE(), @user)
|
|
,paidBy = @user
|
|
WHERE controlNo = @controlNoEncrypted
|
|
------End of Update Transaction Record------------------------------------------------------------------
|
|
|
|
--6.Update receiver identification details--------------------------------------------------------------
|
|
SELECT @tranId = id FROM remitTran WITH(NOLOCK) WHERE controlNo = @controlNoEncrypted
|
|
UPDATE tranReceivers SET
|
|
idType2 = @rIdType
|
|
,idNumber2 = @rIdNumber
|
|
,issuedDate2 = @rIssuedDate
|
|
,validDate2 = @rValidDate
|
|
,idPlaceOfIssue2 = @rPlaceOfIssue
|
|
,mobile = @rMobile
|
|
,relationType = @rRelationType
|
|
,relativeName = @rRelativeName
|
|
WHERE tranId = @tranId
|
|
|
|
--SELECT
|
|
-- @customerId = customerId
|
|
--FROM remitTran trn WITH(NOLOCK)
|
|
--LEFT JOIN tranReceivers rec WITH(NOLOCK) ON trn.id = rec.tranId
|
|
--WHERE trn.controlNo = @controlNoEncrypted
|
|
|
|
--UPDATE customerIdentity SET
|
|
-- idType = ISNULL(@rIdType, idType)
|
|
-- ,idNumber = ISNULL(@rIdNumber, idNumber)
|
|
-- ,issuedDate = ISNULL(@rIssuedDate, issuedDate)
|
|
-- ,validDate = ISNULL(@rValidDate, validDate)
|
|
-- ,placeOfIssue = ISNULL(@rPlaceOfIssue, placeOfIssue)
|
|
--WHERE customerId = @customerId AND ISNULL(isDeleted, 'N') <> 'Y' AND isPrimary = 'Y'
|
|
|
|
---End of Update receiver identification details-------------------------------------------------------
|
|
|
|
--7.A/C Master----------------------------------------------------------------------------------------------
|
|
EXEC proc_updatePayTopUpLimit @settlingAgent, @pAmt
|
|
|
|
UPDATE userWiseTxnLimit SET
|
|
payTodays = ISNULL(payTodays, 0) + @pAmt
|
|
WHERE userId = @userId AND ISNULL(isActive, 'N') = 'Y' AND ISNULL(isDeleted, 'N') = 'Y'
|
|
--UPDATE ac_master SET
|
|
-- system_reserved_amt = ISNULL(system_reserved_amt, 0) - ISNULL(@payoutAmt, 0)
|
|
--WHERE agent_id = @settlingAgent AND gl_code = 1
|
|
|
|
--End Of A/C Master-----------------------------------------------------------------------------------------
|
|
|
|
---##### PROCEDURE FOR PAID TANSACTIO EOD
|
|
--EXEC proc_paidEODRemit @USER, @tranId
|
|
|
|
IF @@TRANCOUNT > 0
|
|
COMMIT TRANSACTION
|
|
|
|
--8.Accounting Server---------------------------------------------------------------------------------------
|
|
--SELECT * FROM [192.168.1.234].IME_TEST.dbo.[REMIT_TRN_LOCAL]
|
|
DECLARE @mapCodeDom VARCHAR(20)
|
|
SELECT @mapCodeDom = mapCodeDom FROM agentMaster WITH(NOLOCK) WHERE agentId = @pBranch
|
|
|
|
EXEC FastMoneyPro_account.dbo.PROC_REMIT_DATA_UPDATE
|
|
@flag = 'p'
|
|
,@mapCode = @mapCodeDom
|
|
,@user = @user
|
|
,@pAgentComm = @pAgentComm
|
|
,@controlNo = @controlNo
|
|
|
|
/*
|
|
UPDATE [192.168.1.234].IME_TEST.dbo.[REMIT_TRN_LOCAL] SET
|
|
R_BRANCH = @mapCodeDom
|
|
,R_AGENT = @mapCodeDom
|
|
,paidBy = @user
|
|
,P_DATE = GETDATE()
|
|
,PAY_STATUS = 'Paid'
|
|
,TRN_STATUS = 'Paid'
|
|
,R_SC = @pAgentComm
|
|
WHERE TRN_REF_NO = dbo.encryptDbLocal(@controlNo)
|
|
*/
|
|
|
|
EXEC [proc_errorHandler] 0, 'Transaction has been paid successfully', @controlNoEncrypted
|
|
END
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
GO
|