DB_Script/StoredProc/proc_SendTransaction.sql

USE [FastMoneyPro_Remit]
GO
/****** Object:  StoredProcedure [dbo].[proc_SendTransaction]    Script Date: 4/2/2024 12:34:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


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 VARCHAR(15) = NULL
	,@processId VARCHAR(40) = NULL
	,@flag VARCHAR(100)
	,@controlNo VARCHAR(20) = NULL
	,@PartnerPin VARCHAR(20) = NULL
	,@PartnerId VARCHAR(20) = NULL
	,@tranId BIGINT = NULL
	,@errorCode INT = NULL
	,@Message NVARCHAR(500) = NULL
	,@sAdd2 NVARCHAR(150) = NULL
	,@goodsOrigin VARCHAR(100) = NULL
	,@goodsType VARCHAR(100) = NULL
	,@portOfShipment VARCHAR(100) = NULL
	,@discountedFee VARCHAR(100) = NULL
	,@siteReference VARCHAR(100) =NULL
	,@token varchar(100)=NULL
	,@requestedBy varchar(100)=NULL
	,@isPromoCode CHAR(1) = NULL
	,@promoRowId INT = NULL
	,@promoCode VARCHAR(100) = NULL
AS
-----------------------------------------------------------
-- For Broadcast notification , @flag = 'send'
-- #101 - MOBILE CHANGES , #361 - MULTI-LINGUAL
-- #476 - Changes in dotransaction Payment of Import Good
-- #101 - set error code as 19 for unapproved customer
-- Disable ProcBroadCastMobile @Flag='TRANSACTION_SUCCESS'
-- #495 Added condition for @sourceOfFundOther and @purposeOfRemittanceOther
-- Fix issue related with Bank id invalid add @pBankId_O,@payOutPartnerId_O
--#643 Mobile transaction wrongly flag for Questionnaire require due to Visa Status
-- #717 - insert visaStatus in tranSendersTemp
-- check mobileApprovedDate instead of approved date
-- #756 Duplicate pin generated	
-- insert into controllist for M
--#767 Relationship displayed blank for mobile transaction.
-- #767 relationship other
-- #790 Change column to store relationship others for Mobile transaction
--#810 Missing sending commission in mobile Txn voucher
--#831 Disabled customer allowed to do Mobile txn
--#820 Donga wrong commission for mobile txn
--#Bug #1192 Sender Expiry not check from Mobile Transaction
-- #1003 - Reward Points, @flag = 'Send'
-- #1498 - TF NO Balance 
-- #1590 - Customer Loyalty 
-- #11358 - show branch routing number for mobile transaction in search transaction  , pbankBranchName
-- #12985 - invalid routing no case
-- #18970 - change in @flag = 's' to hold txn from verifying if kyc is not completed
-----------------------------------------------------------
SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY
	DECLARE @complianceRuleId INT
		,@cAmtUSD MONEY
		,@complienceMessage VARCHAR(1000) = NULL
		,@shortMsg VARCHAR(100) = NULL
		,@complienceErrorCode TINYINT = NULL
		,@compErrorCode INT
		,@discountType VARCHAR(2) = NULL
		,@discountvalue MONEY = NULL
		,@couponType VARCHAR(3) = NULL
		,@discountPercent MONEY = NULL
		,@couponName VARCHAR(20) = NULL
		,@ServiceCharge_Temp MONEY = NULL
		,@schemePremium MONEY = NULL
		,@customerType INT = NULL
		,@msg VARCHAR(MAX) = NULL
		,@pBankId_O BIGINT = @pBankId
		,@payOutPartnerId_O BIGINT = @payOutPartnerId
		,@RelWithSenderOthers VARCHAR(200) = NULL
		,@receiverName VARCHAR(100) = NULL
		,@createdFrom VARCHAR(10) = NULL
		,@tranCount INT = NULL
		,@schemeCount INT = NULL
		,@isEligible CHAR(1) = NULL
		,@schemeCodeId INT  = NULL
		,@isFirstTran CHAR(1) = 'N'
		,@introducer VARCHAR(25) = NULL
		,@pReceiverBankId varchar(25)=NULL
		,@pReceiverAccount varchar(50)=NULL

	IF EXISTS (
			SELECT *
			FROM STATICDATAVALUE(NOLOCK)
			WHERE VALUEID = @RelWithSender
				AND TYPEID = 2100
				AND isActive = 'N'
			)
	BEGIN
		SELECT TOP 1 @receiverName = ISNULL(firstName, '') + ISNULL(' ' + middleName, '') + ISNULL(' ' + lastName1, '') + ISNULL(' ' + lastName2, '')
		FROM dbo.receiverInformation RI(NOLOCK)
		WHERE receiverId = @receiverId

		SET @msg = 'Please update the details of receiver [ ' + @receiverName + ' ] before performing transaction!';

		EXEC proc_errorHandler 21
			,@msg
			,NULL;
			Rollback Transaction
		RETURN;
	END

	IF EXISTS (
			SELECT *
			FROM STATICDATAVALUE(NOLOCK)
			WHERE VALUEID = @PurposeOfRemittance
				AND TYPEID = 3800
				AND isActive = 'Y'
			)
	BEGIN
		SELECT @PurposeOfRemittance = detailTitle
		FROM STATICDATAVALUE(NOLOCK)
		WHERE VALUEID = @PurposeOfRemittance
			AND TYPEID = 3800
	END

	IF EXISTS (
			SELECT *
			FROM STATICDATAVALUE(NOLOCK)
			WHERE VALUEID = @sourceOfFund
				AND TYPEID = 3900
				AND isActive = 'Y'
			)
	BEGIN
		SELECT @sourceOfFund = detailTitle
		FROM STATICDATAVALUE(NOLOCK)
		WHERE VALUEID = @sourceOfFund
			AND TYPEID = 3900
	END

	SELECT @pcountryId = cm.countryId
		,@RelWithSenderOthers = ISNULL(relationOther, otherRelationDesc), @pReceiverBankId=ri.payOutPartner, 
		@pReceiverAccount= ri.receiverAccountNo
	FROM receiverInformation RI(NOLOCK)
	LEFT JOIN countryMaster CM(NOLOCK) ON CM.countryName = RI.country
	WHERE RI.receiverId = @ReceiverId

	IF EXISTS (
			SELECT *
			FROM STATICDATAVALUE(NOLOCK)
			WHERE VALUEID = @RelWithSender
				AND TYPEID = 2100
				AND isActive = 'Y'
			)
	BEGIN
		IF (@RelWithSender = '11339')
		BEGIN
			SET @RelWithSender = 'Other (please specify) :' + ISNULL(@RelWithSenderOthers, '');
		END
		ELSE
		BEGIN
			SELECT @RelWithSender = detailTitle
			FROM STATICDATAVALUE(NOLOCK)
			WHERE VALUEID = @RelWithSender
				AND TYPEID = 2100
		END
	END

	DECLARE @isRealTime BIT = 0 , @TRANSFER_MODE varchar(10)='';


	SELECT @payOutPartnerId = AGENTID
		,@isRealTime = isRealTime
	FROM TblPartnerwiseCountry(NOLOCK)
	WHERE CountryId = @pCountryId
		AND IsActive = 1
		AND ISNULL(PaymentMethod, @deliveryMethodId) = @deliveryMethodId
		AND IsMobileEnabled = 1

	IF @payOutPartnerId IS NULL
	BEGIN
		EXEC proc_errorHandler 3
			,'Oops, something went wrong! Please perform the transaction again. Route is missing.'
			,NULL

		RETURN;
	END

	
	IF(@pBankId IS NULL OR @pBankId=0)
	 SET  @pBankId= @pReceiverBankId;
	 	 	 
			 	PRINT '@raccountNo'
	IF(@raccountNo IS NULL OR @raccountNo='')
	 SET  @raccountNo= @pReceiverAccount

	IF EXISTS (
			SELECT *
			FROM API_BANK_LIST_MASTER(NOLOCK)
			WHERE MASTER_BANK_ID = @pBankId
				AND IS_ACTIVE = '1'
			)
	BEGIN
		SELECT @pBankId = AB.BANK_ID ,@TRANSFER_MODE= ab.TRANSFER_MODE
		FROM API_BANK_LIST AB(NOLOCK)
		INNER JOIN API_BANK_LIST_MASTER ABM(NOLOCK) ON AB.JME_BANK_CODE = ABM.JME_BANK_CODE
		WHERE MASTER_BANK_ID = @pBankId
			AND ABM.IS_ACTIVE = '1'
			AND AB.API_PARTNER_ID = @payOutPartnerId
	END
	

	PRINT '@pBankId'
	PRINT @pBankId

	IF @flag = 'SEND'
	BEGIN
		--IF NOT EXISTS (
		--		SELECT TOP 1 'X'
		--		FROM dbo.customerMaster(NOLOCK)
		--		WHERE username = @user
		--			AND mobileverifieddate IS NOT NULL
		--		)
		--BEGIN
		--	EXEC proc_errorHandler 19
		--		,'You are not authorized to perform transaction, please contact IME London Support!'
		--		,NULL;

		--	RETURN;
		--END

		DECLARE @remittanceAllowed int,@idExpiryDate DATETIME

		SELECT @remittanceAllowed = remittanceAllowed,@idExpiryDate=idExpiryDate FROM customerMaster(NOLOCK)
		WHERE CUSTOMERID = @SenderId

		--IF ISNULL(@remittanceAllowed, 0) = '0'
		--BEGIN
		--	SELECT @MSG = 'You are not authorized to perform transaction, please contact IME London Support!'

		--	EXEC proc_errorHandler 19
		--		,@MSG
		--		,NULL

		--	RETURN
		--END

		--IF @idExpiryDate < GETDATE()
		--BEGIN
		--	SELECT @MSG = 'Your ID with us has expired. Please upload new ID details in “Renew ID” or contact IME London support.'
		--	EXEC proc_errorHandler 22
		--		,@MSG
		--		,NULL
		--	RETURN;
		--END
		 --DECLARE @kycStatus INT

		 --SELECT @kycStatus = kycStatus
		 --FROM TBL_CUSTOMER_KYC(NOLOCK)
		 --WHERE CUSTOMERID = @senderId
		 --	AND ISDELETED = 0
		 ----AND kycStatus=11044
		 --ORDER BY KYC_DATE

		 --IF ISNULL(@kycStatus, 0) <> 11044
		 --BEGIN
		 --	IF @kycStatus IS NOT NULL
		 --		SELECT @MSG = 'KYC for selected customer is not completed, it is in status:' + detailTitle
		 --		FROM staticDataValue(NOLOCK)
		 --		WHERE valueId = @kycStatus
		 --	ELSE
		 --		SELECT @MSG = 'Please complete KYC status first'

		 --	EXEC proc_errorHandler 2
		 --		,@MSG
		 --		,NULL;

		 --	RETURN
		 --END

		IF @paymentType IS NULL
			SET @paymentType = 'WALLET'

		DECLARE @sCurrCostRate FLOAT
			,@sCurrHoMargin FLOAT
			,@pCurrCostRate FLOAT
			,@customerRate MONEY
			,@agentCrossSettRate FLOAT
			,@iServiceCharge MONEY
			,@iTAmt MONEY
			,@iPAmt MONEY
			,@place INT
			,@currDecimal INT
			,@agentAvlLimit MONEY
			,@serviceCharge MONEY
			,@sCountry VARCHAR(50) = 'United Kingdom'
			,@sAgent BIGINT
			,@sAgentName VARCHAR(100)
			,@sBranch INT
			,@sBranchName VARCHAR(100)
			,@sSuperAgent INT
			,@sSuperAgentName VARCHAR(100)
			,@senderName VARCHAR(100)
			,@sIdNo VARCHAR(50)
			,@sIdType VARCHAR(50)
			,@sMobile VARCHAR(15)
			,@pAgent BIGINT
			,@pSuperAgent BIGINT
			,@pSuperAgentName VARCHAR(100)
			,@pAgentName VARCHAR(100)
			,@controlNoEncrypted VARCHAR(30)
			,@tempCompId BIGINT
			,@pBranch INT
			,@pBranchName VARCHAR(100)
			,@pCountry VARCHAR(100)
			,@RcreatedFrom VARCHAR(5)
			,@ROtpVerified BIT

		SELECT @sCountryId = 233
			,@sBranch = 394395
			,@collCurr='GBP';

		SELECT @SenderId = customerId , 
				@createdFrom = createdFrom
		FROM customerMaster(NOLOCK)
		WHERE USERNAME = @User


		--IF NOT EXISTS(SELECT 1 FROM
		--				(SELECT TOP 1 customerId FROM TRANSENDERS TS (NOLOCK)
		--					inner join remittran (nolock) rt on rt.id = ts.tranId
		--					WHERE customerId = @SenderId AND rt.tranStatus <> 'CANCEL' AND RT.tranType='M'
		--			UNION ALL 
		--					SELECT TOP 1 customerId FROM TRANSENDERSTEMP TT (NOLOCK) 
		--					inner join remittrantemp (nolock) rt on rt.id = tt.tranId
		--					WHERE customerId = @SenderId AND rt.tranStatus <> 'CANCEL'  AND rt.tranType='M'
		--			) a where customerId = @SenderId
		--		 )
		--BEGIN
		--	SET @isFirstTran = 'Y'
		--END


		SELECT @sAgent = sAgent
			,@sAgentName = sAgentName
			,@sBranch = sBranch
			,@sBranchName = sBranchName
			,@sSuperAgent = sSuperAgent
			,@sSuperAgentName = sSuperAgentName
		FROM dbo.FNAGetBranchFullDetails(@sBranch)

		SELECT @pCountry = COUNTRYNAME
		FROM COUNTRYMASTER(NOLOCK)
		WHERE COUNTRYID = @pCountryId

		SELECT TOP 1 @pAgent = AM.agentId
		--,@pCountryId		=	AM.agentCountryId
		FROM agentMaster AM(NOLOCK)
		WHERE AM.parentId = @payOutPartnerId
			AND agentType = 2903
			AND AM.isSettlingAgent = 'Y'
			AND AM.isApiPartner = 1

		SELECT @pSuperAgentName = sSuperAgentName
			,@pSuperAgent = sSuperAgent
			,@pAgent = sAgent
			,@pAgentName = sAgentName
		FROM dbo.FNAGetBranchFullDetails(@pAgent)

		SELECT @pBranch = @pAgent
			,@pBranchName = @pAgentName

		DECLARE @StateId INT
			,@DistrictId INT

		IF @receiverId IS NOT NULL
		BEGIN
			IF NOT EXISTS (
					SELECT '1'
					FROM dbo.receiverInformation(NOLOCK)
					WHERE receiverId = @ReceiverId
					)
			BEGIN
				EXEC proc_errorHandler 4
					,'Receiver Data Not Match !'
					,NULL;

				RETURN;
			END

			SELECT TOP 1 @receiverName = ISNULL(firstName, '') + ISNULL(' ' + middleName, '') + ISNULL(' ' + lastName1, '') + ISNULL(' ' + lastName2, '')
				,@StateId = AI.STATE_ID
				,@DistrictId = AC.CITY_ID
				,@RcreatedFrom = ISNULL(ri.createdFrom, 'C')
				,@ROtpVerified = ISNULL(ri.isOTPVerified, 0)
			FROM dbo.receiverInformation RI(NOLOCK)
			LEFT JOIN API_STATE_LIST AI(NOLOCK) ON AI.STATE_NAME = RI.STATE
				AND AI.API_PARTNER_ID = @payOutPartnerId
			LEFT JOIN API_CITY_LIST AC(NOLOCK) ON AC.STATE_ID = AI.STATE_ID
				AND AC.CITY_NAME = RI.DISTRICT
			WHERE receiverId = @receiverId

			--IF(@RcreatedFrom='M'  AND @ROtpVerified=0 )
			--BEGIN
			--	EXEC proc_errorHandler 4
			--		,'Receiver Data Not Match !'
			--		,NULL;
			--	RETURN;
			--END
		END
		ELSE
			SET @receiverName = ISNULL(@rFirstName, '') + ISNULL(' ' + @rMiddleName, '') + ISNULL(' ' + @rLastName, '')

		IF @rFirstName IS NULL
			AND @receiverId IS NULL
		BEGIN
			EXEC proc_errorHandler 5
				,'Receiver name cannot be empty'
				,NULL;

			RETURN;
		END

		IF ISNULL(@exRate, 0) = 0
		BEGIN
			EXEC proc_errorHandler 6
				,'Transaction cannot be proceed.Exchange Rate not defined'
				,NULL

			RETURN
		END

		IF @pAgent IS NULL
		BEGIN
			EXEC proc_errorHandler 3
				,'Oops, something went wrong (Pagent). Please perform the transaction again'
				,NULL

			RETURN;
		END

		DECLARE @OccupationId INT
			,@sNaCountryId INT
			,@visaStatusId INT
		DECLARE @visaStatusText VARCHAR(200)

		SELECT 
			@senderName = ISNULL(fullName,ISNULL(cm.firstName, '') + ISNULL(' ' + cm.middleName, '') + ISNULL(' ' + cm.lastName1, ''))
			,@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 = '779' + LEFT(CAST(ABS(CHECKSUM(NEWID())) AS VARCHAR(10)) + '0000000000', 9)

		IF EXISTS (
				SELECT TOP 1 'X'
				FROM controlNoList WITH (NOLOCK)
				WHERE controlNo = @controlNo
				)
		BEGIN
			SET @controlNo = '779' + LEFT(CAST(ABS(CHECKSUM(NEWID())) AS VARCHAR(10)) + '0000000000', 7)

			--SELECT  @controlNoEncrypted = dbo.FNAEncryptString(@controlNo);
			IF EXISTS (
					SELECT TOP 1 'X'
					FROM controlNoList WITH (NOLOCK)
					WHERE controlNo = @controlNo
					)
			BEGIN
				EXEC proc_errorHandler 10
					,'Technical error occurred. Please try again'
					,NULL;

				RETURN;
			END
		END;

		SELECT @controlNoEncrypted = dbo.FNAEncryptString(@controlNo);

		IF @deliveryMethodId = 2
			AND @pCountryId <> '151'
		BEGIN
			IF NOT EXISTS (
					SELECT TOP 1 'A'
					FROM api_bank_list(NOLOCK)
					WHERE BANK_ID = @pBankId
						AND PAYMENT_TYPE_ID IN (
							0
							,2
							)
						AND IS_ACTIVE = 1
					)
			BEGIN
				EXEC proc_errorHandler 11
					,'Invalid bank selected'
					,NULL

				RETURN
			END

			IF @raccountNo IS NULL
			BEGIN
				EXEC proc_errorHandler 12
					,'Account number cannot be blank'
					,NULL

				RETURN
			END
		END;

		--4. Get Exchange Rate Details------------------------------------------------------------------------------------------------------------------
		DECLARE @pCurrHoMargin FLOAT, @customerPremium FLOAT
		

		SELECT @customerRate = customerRate
			,@sCurrCostRate = sCurrCostRate
			,@sCurrHoMargin = sCurrHoMargin
			,@pCurrCostRate = pCurrCostRate
			,@agentCrossSettRate = agentCrossSettRate
			,@serviceCharge = serviceCharge
			,@iPAmt = pAmt
			,@schemeId = schemeId
			,@pCurrHoMargin = pCurrHoMargin
			,@schemeCount = CAST( sharingValue AS INT)
			,@customerPremium= customerPremium
			--,@schemeCodeId= schemeId
		FROM exRateCalcHistory(NOLOCK)
		WHERE FOREX_SESSION_ID = @forexSessionId
			AND [USER_ID] = @user
			PRINT '@schemeId';
			PRINT @schemeId;
			
		DECLARE @pAgentCommCurrency VARCHAR(3)
			,@pAgentComm MONEY

		SELECT @pAgentCommCurrency = DBO.FNAGetPayCommCurrency(@sSuperAgent, @sAgent, @sBranch, @SCOUNTRYID, @pSuperAgent, @pBranch, @pCountryId)

			IF (LEN(ISNULL(@TRANSFER_MODE, ''))<1
				AND @pCountryId = '203'
				AND @pAgent = '394133'
				AND @deliveryMethodId = '2'
				) -- DONGA Bank Deposit not real time
		BEGIN
			SET @pAgentComm = (@payoutAmt * 0.17) / 100;
		END
		ELSE
		BEGIN
			SELECT @pAgentComm = amount
			FROM dbo.FNAGetPayComm(@sAgent, @sCountryId, NULL, NULL, @pCountryId, NULL, @pAgent, @pAgentCommCurrency, @deliveryMethodId, @collAmt, @payoutAmt, @serviceCharge, @transferAmt, NULL)
		END

		--IF @customerRate IS NULL
		--BEGIN
		--	EXEC proc_errorHandler 6
		--		,'Transaction cannot be proceed. Exchange Rate not defined'
		--		,NULL

		--	RETURN
		--END

		-- Customer Loyalty 
		--EXEC PROC_Customer_LoyaltyV2 @flag = 'check-eligible-v2'
		--			,@isEligible = @isEligible OUT
		--			,@referralCode = @introducer
		--			,@tranCount = @trancount OUT
		--			,@schemeCount = @schemeCount OUT
		--			,@customerId = @senderId
		--			,@createdFrom = 'M'
	
	 --print '@isEligible.Send' + @isEligible;
	 --	 print '@@trancount.Send' + CAST(@trancount AS VARCHAR);
	 SET @isEligible='N';

		IF @isEligible = 'Y'
		BEGIN 
			SET @iServiceCharge = 0
		END
		ELSE 
		BEGIN 

			PRINT @sCountryId;
		PRINT @sSuperAgent;
			PRINT @sAgent;
				PRINT @sBranch;
				PRINT @pCountryId;
					PRINT @pSuperAgent;
					PRINT @pSuperAgent;
					PRINT @pAgent;
					PRINT @pBranch;
					PRINT @deliveryMethodId;
						PRINT @collAmt;
							PRINT @collCurr;
   			--Get Service Charge----------------------------------------------------------------------------------------------------------------------
			DECLARE @iServiceChargeTemp MONEY
			
			IF @calBy = 'p'
			BEGIN
				SELECT @iServiceChargeTemp=ISNULL(amount, -1) 
				FROM [dbo].FNAGetServiceCharge(@sCountryId, @sSuperAgent, @sAgent, @sBranch, @pCountryId, @pSuperAgent, @pAgent, @pBranch, @deliveryMethodId, @transferAmt, @collCurr)
			END
			ELSE --IF @calBy = 'c'
			BEGIN
				SELECT @iServiceChargeTemp=ISNULL(amount, -1) 
				FROM [dbo].FNAGetServiceCharge(@sCountryId, @sSuperAgent, @sAgent, @sBranch, @pCountryId, @pSuperAgent, @pAgent, @pBranch, @deliveryMethodId, @collAmt, @collCurr)
			END
		

		PRINT '@iServiceChargeTemp';
		PRINT @iServiceChargeTemp;
		SELECT @iServiceCharge = ISNULL(@iServiceChargeTemp,0) --- ISNULL(@discountedFee,0)
			

		IF @iServiceCharge < 0 -- @iServiceCharge = - 1
		BEGIN
			EXEC proc_errorHandler 13
				,'Transaction cannot be proceed. Service Charge is not defined'
				,NULL

			RETURN
		END
		END
			
		--	print @serviceCharge;
		--IF ISNULL(@iServiceCharge, 0) <> ISNULL(@serviceCharge, 1)
		--BEGIN
		--	EXEC proc_errorHandler 14
		--		,'Transaction cannot be proceed. Amount detail not match'
		--		,NULL

		--	RETURN
		--END

		--End Service Charge-------------------------------------------------------------------------------------------------------------------------------------
		--DECLARE @iMsg VARCHAR(MAX)
		IF ISNULL(@exRate, 0) <> ISNULL(@customerRate, 1)
		BEGIN
			--SET @iMsg = 'Amount detail not match. Please re-calculate the amount again' + CAST(isnull(@exRate,0) AS VARCHAR) + ' : ' + CAST(isnull(@customerRate,1) AS VARCHAR) 
			EXEC proc_errorHandler 15
				,'Amount detail not match. Please re-calculate the amount again'
				,NULL

			RETURN
		END

		DECLARE @promoValue INT
		IF @requestedBy='online'
		BEGIN
		IF ISNUMERIC(ISNULL(@discountedFee,0)) = 1
		BEGIN
			SET @isPromoCode = 'N'
			IF ((@discountedFee > '0.00') AND @isPromoCode = 'N')
			BEGIN				
				IF dbo.FNA_GET_AVAILABLE_BALANCE_POINTS(@senderId) < ISNULL(@discountedFee, 0)
				BEGIN
					EXEC proc_errorHandler 1
						,'You do not have sufficient points for redeem!'
						,NULL
					RETURN;
				END
			END
		END
		ELSE
		BEGIN
			SET @isPromoCode = 'Y'
			SELECT @promoRowId = promoRowId FROM exRateCalcHistory WHERE FOREX_SESSION_ID = @forexSessionId

			SELECT @promoValue = PROMOTION_VALUE, @promoCode = PROMOTIONAL_CODE FROM TBL_PROMOTIONAL_CAMAPAIGN WHERE ROW_ID = @promoRowId
			SET @discountedFee = @promoValue
		END
		
		END
		ELSE
		BEGIN
		IF ISNUMERIC(ISNULL(@discountedFee,0)) = 1
		BEGIN

		 SELECT @promoRowId = promoRowId FROM exRateCalcHistory WHERE FOREX_SESSION_ID = @forexSessionId;

		 IF(ISNUMERIC(@promoRowId)=1 AND EXISTS(select 'X' FROM TBL_PROMOTIONAL_CAMAPAIGN WHERE ROW_ID = @promoRowId))
		 BEGIN
		 SET @isPromoCode = 'Y'
			

			SELECT @promoValue = PROMOTION_VALUE, @promoCode = PROMOTIONAL_CODE FROM TBL_PROMOTIONAL_CAMAPAIGN WHERE ROW_ID = @promoRowId
			SET @discountedFee = @promoValue

		 END
		 ELSE
		 BEGIN
		 SET @isPromoCode = 'N'
			IF ((@discountedFee > '0.00') AND @isPromoCode = 'N')
			BEGIN				
				IF dbo.FNA_GET_AVAILABLE_BALANCE_POINTS(@senderId) < ISNULL(@discountedFee, 0)
				BEGIN
					EXEC proc_errorHandler 1
						,'You do not have sufficient points for redeem!'
						,NULL
					RETURN;
				END
			END
		 END
		END
		END
		

		PRINT '@collAmt';
		PRINT @collAmt;
		PRINT '@iServiceCharge';
		PRINT @iServiceCharge;

			PRINT '@discountedFee';
		PRINT @discountedFee;

				PRINT '@@CustomerRate';
		PRINT @CustomerRate;

		SELECT @iTAmt = @collAmt - @iServiceCharge + ISNULL(@discountedFee, 0)

		SELECT TOP 1 @place = place
			,@currDecimal = currDecimal
		FROM currencyPayoutRound(NOLOCK)
		WHERE ISNULL(isDeleted, 'N') = 'N'
			AND currency = @payoutCurr
			AND tranType IS NULL;

		SET @currDecimal = ISNULL(@currDecimal, 0)
		SET @place = ISNULL(@place, 0)
		SET @iPAmt = @iTAmt * @CustomerRate

		IF @payoutAmt - @iPAmt <= 1
			SET @iPAmt = @payoutAmt

		----## WHILE CALCULATING FROM PAYOUT AMOUNT CONSIDARING 10 VND 
		IF ISNULL(@iPAmt, 0) <> ISNULL(@payoutAmt, 1)
		BEGIN
		PRINT  @iPAmt;
		PRINT  @payoutAmt;
			--SET @Msg = 'Amount detail not match. Please re-calculate the amount again.' + CAST(@iPAmt AS VARCHAR) + ' - ' +  CAST(@payoutAmt AS VARCHAR)
			EXEC proc_errorHandler 15
				,'PAmount detail not match. Please re-calculate the amount again.'
				,NULL

			RETURN
		END

		--DECLARE @maxAmt MONEY, @depositType VARCHAR(200)
		--SELECT @maxAmt = maxAmt FROM countryWiseExchangeRate WHERE depositType = 'DEBIT_CARD' AND countryId = @pCountryId
		--SELECT @depositType = schemeId FROM exRateCalcHistory WHERE FOREX_SESSION_ID = @forexSessionId
		--IF(@depositType = 'DEBIT_CARD' AND @collAmt >= @maxAmt)
		--BEGIN
		--	DECLARE @errMsg NVARCHAR(200)
		--	SET @errMsg = 'You cannot send more than ' + CAST(@maxAmt AS NVARCHAR) + ' GBP via Debit Card.'
			
		--	EXEC proc_errorHandler 15, @errMsg, NULL
		--	--EXEC proc_errorHandler 15
		--	--	,'You cannot send more than ' + CAST(@maxAmt AS VARCHAR) +' GBP via Debit Card.'
		--	--	,NULL

		--	RETURN
		--END

		----OFAC Checking
		DECLARE @receiverOfacRes VARCHAR(MAX)
			,@ofacRes VARCHAR(MAX)
			,@ofacReason VARCHAR(200)

		EXEC proc_ofacTracker @flag = 't'
			,@name = @senderName
			,@Result = @ofacRes OUTPUT

		EXEC proc_ofacTracker @flag = 't'
			,@name = @receiverName
			,@Result = @receiverOfacRes OUTPUT

		DECLARE @result VARCHAR(MAX)

		IF ISNULL(@ofacRes, '') <> ''
		BEGIN
			SET @ofacReason = 'Matched by sender name'
		END

		IF ISNULL(@receiverOfacRes, '') <> ''
		BEGIN
			SET @ofacRes = ISNULL(@ofacRes + ',' + @receiverOfacRes, '' + @receiverOfacRes)
			SET @ofacReason = 'Matched by receiver name'
		END

		IF ISNULL(@ofacRes, '') <> ''
			AND ISNULL(@receiverOfacRes, '') <> ''
		BEGIN
			SET @ofacReason = 'Matched by both sender name and receiver name'
		END

		--Ofac Checking End
		DECLARE @agentRefId VARCHAR(50) = NEWID()

		----Compliance Checking 1-> Block, 2-> Hold, 3-> Questionnaire
		CREATE TABLE #TBL_COMPLIANCE_RESULT (
			ERROR_CODE INT
			,MSG VARCHAR(2000)
			,RULE_ID INT
			,SHORT_MSG VARCHAR(1000)
			,[TYPE] VARCHAR(10)
			,IS_D0C_REQUIRED BIT
			)

		INSERT INTO #TBL_COMPLIANCE_RESULT (
			ERROR_CODE
			,MSG
			,RULE_ID
			,SHORT_MSG
			,[TYPE]
			,IS_D0C_REQUIRED
			)
		EXEC [PROC_COMPLIANCE_CHECKING_NEW] @flag = 'core'
			,@user = @user
			,@sIdType = @sIdType
			,@sIdNo = @sIdNo
			,@receiverName = @receiverName
			,@amount = @iTAmt
			,@customerId = @senderId
			,@pCountryId = @pCountryId
			,@deliveryMethod = @deliveryMethodId
			,@professionId = @OccupationId
			,@receiverMobile = @rMobileNo
			,@accountNo = @raccountNo
			,@receiverId = @receiverId
			,@sNaCountryId = @sNaCountryId
			,@visaStatus = @visaStatusId

		IF EXISTS (
				SELECT *
				FROM #TBL_COMPLIANCE_RESULT
				WHERE ERROR_CODE <> 0
				)
		BEGIN
			IF EXISTS (
					SELECT *
					FROM #TBL_COMPLIANCE_RESULT
					WHERE ERROR_CODE IN (1)
					) --transaction blocked
			BEGIN
				INSERT INTO ComplianceLog (
					senderName
					,senderCountry
					,senderIdType
					,senderIdNumber
					,senderMobile
					,receiverName
					,receiverCountry
					,payOutAmt
					,complianceId
					,complianceReason
					,complainceDetailMessage
					,createdBy
					,createdDate
					,agentRefId
					,isDocumentRequired
					)
				SELECT @senderName
					,@sCountry
					,@sIdType
					,@sIdNo
					,@sMobile
					,@receiverName
					,@pCountry
					,@collAmt
					,RULE_ID
					,SHORT_MSG
					,MSG
					,@user
					,GETDATE()
					,@agentRefId
					,IS_D0C_REQUIRED
				FROM #TBL_COMPLIANCE_RESULT

				SELECT @MSG = MSG
				FROM #TBL_COMPLIANCE_RESULT
				WHERE ERROR_CODE IN (1)
					--EXEC proc_errorHandler 1, @MSG, NULL
			END

			IF EXISTS (
					SELECT *
					FROM #TBL_COMPLIANCE_RESULT
					WHERE ERROR_CODE IN (
							2
							,3
							)
					) --transaction hold/questionnaire
			BEGIN
				DELETE
				FROM remitTranComplianceTemp
				WHERE agentRefId = @agentRefId

				INSERT remitTranComplianceTemp (
					csDetailTranId
					,matchTranId
					,agentRefId
					)
				SELECT RULE_ID
					,NULL
					,@agentRefId
				FROM #TBL_COMPLIANCE_RESULT
				WHERE ERROR_CODE IN (
						2
						,3
						)
				ORDER BY ISNULL(IS_D0C_REQUIRED, 0) DESC
			END

			INSERT INTO ComplianceLog (
				senderName
				,senderCountry
				,senderIdType
				,senderIdNumber
				,senderMobile
				,receiverName
				,receiverCountry
				,payOutAmt
				,complianceId
				,complianceReason
				,complainceDetailMessage
				,createdBy
				,createdDate
				,agentRefId
				,isDocumentRequired
				)
			SELECT @senderName
				,@sCountry
				,@sIdType
				,@sIdNo
				,@sMobile
				,@receiverName
				,@pCountry
				,@collAmt
				,RULE_ID
				,SHORT_MSG
				,MSG
				,@user
				,GETDATE()
				,@agentRefId
				,IS_D0C_REQUIRED
			FROM #TBL_COMPLIANCE_RESULT
		END

		----checking for visa status questionnaire
		--IF EXISTS (
		--		SELECT *
		--		FROM VW_VISA_STATUS_QUESTIONNAIRE
		--		WHERE VISA_ID = @visaStatusId
		--		)
		--BEGIN
		--	INSERT INTO ComplianceLog (
		--		senderName
		--		,senderCountry
		--		,senderIdType
		--		,senderIdNumber
		--		,senderMobile
		--		,receiverName
		--		,receiverCountry
		--		,payOutAmt
		--		,complianceId
		--		,complianceReason
		--		,complainceDetailMessage
		--		,createdBy
		--		,createdDate
		--		,agentRefId
		--		,isDocumentRequired
		--		)
		--	SELECT @senderName
		--		,@sCountry
		--		,@sIdType
		--		,@sIdNo
		--		,@sMobile
		--		,@receiverName
		--		,@pCountry
		--		,@collAmt
		--		,0
		--		,'Compliance Hold/Questionnaire due to Visa Status: ' + @visaStatusText
		--		,'Compliance Hold/Questionnaire due to Visa Status: ' + @visaStatusText
		--		,@user
		--		,GETDATE()
		--		,@agentRefId
		--		,0

		--	INSERT remitTranComplianceTemp (
		--		csDetailTranId
		--		,matchTranId
		--		,agentRefId
		--		,reason
		--		)
		--	SELECT 0
		--		,NULL
		--		,@agentRefId
		--		,'Questionnaire require due to Visa Status'
		--END

		--**********Customer Per Day Limit Checking**********
		DECLARE @remitTranTemp TABLE (
			tranId BIGINT
			,controlNo VARCHAR(20)
			,cAmt MONEY
			,receiverName VARCHAR(200)
			,receiverIdType VARCHAR(100)
			,receiverIdNumber VARCHAR(50)
			,dot DATETIME
			);

		--INSERT INTO @remitTranTemp (
		--	tranId
		--	,controlNo
		--	,cAmt
		--	,receiverName
		--	,receiverIdType
		--	,receiverIdNumber
		--	,dot
		--	)
		--SELECT TOP 10 rt.id
		--	,rt.controlNo
		--	,rt.cAmt
		--	,rt.receiverName
		--	,rec.idType
		--	,rec.idNumber
		--	,rt.createdDate
		--FROM vwRemitTran rt WITH (NOLOCK)
		--INNER JOIN vwTranSenders sen WITH (NOLOCK) ON rt.id = sen.tranId
		--INNER JOIN vwTranReceivers rec WITH (NOLOCK) ON rt.id = rec.tranId
		--WHERE sen.customerId = @SenderId
		--	AND (
		--		rt.approvedDate BETWEEN CONVERT(VARCHAR, GETDATE(), 101)
		--			AND CONVERT(VARCHAR, GETDATE(), 101) + ' 23:59:59'
		--		OR (
		--			approvedBy IS NULL
		--			AND cancelApprovedBy IS NULL
		--			)
		--		)
		--ORDER BY rt.createdDate DESC

		--IF EXISTS (
		--		SELECT TOP 1 'X'
		--		FROM @remitTranTemp
		--		WHERE cAmt = @collAmt
		--			AND (receiverName = @receiverName)
		--			AND DATEDIFF(MI, dot, GETDATE()) <= 2
		--		)
		--BEGIN
		--	EXEC proc_errorHandler 16
		--		,'Similar transaction found. Please perform the transaction after 2 minutes.'
		--		,NULL;
		--		Rollback Transaction ;
		--	RETURN;
		--END;

		DECLARE @countryRisk INT
			,@OccupationRisk INT
			,@compFinalRes VARCHAR(5)
		-- #########country and occupation  risk point
		DECLARE @deliveryMethod VARCHAR(30)
			,@pBankName VARCHAR(100)
			,@pBankBranchName VARCHAR(100)
			,@pBankRowId BIGINT

		SELECT TOP 1 @pCountry = COUNTRYNAME
		FROM countryMaster(NOLOCK)
		WHERE countryId = @pCountryId

		SELECT TOP 1 @deliveryMethod = typeTitle
		FROM serviceTypeMaster(NOLOCK)
		WHERE serviceTypeId = @deliveryMethodId

		SELECT TOP 1 @pBankName = bank_name
			,@pBankRowId = bank_id
		FROM api_bank_list
		WHERE bank_id = @pBankId
			AND is_Active = 1

					PRINT '@pBankId'
				PRINT @pBankId

			PRINT '@pBankName'
				PRINT @pBankName

		SELECT TOP 1 @pBankBranchName = branch_name + isnull(BRANCH_CODE1,'')
		FROM api_bank_branch_list
		WHERE bank_id = @pBankRowId
			AND branch_id = @pBranchId
			AND is_Active = 1

		DECLARE @VNo VARCHAR(20);

		--IF @pCountry = 'Nepal'
		--	AND @deliveryMethod = 'CASH PAYMENT'
		--BEGIN
		--	SELECT @pSuperAgent = NULL
		--		,@pSuperAgentName = NULL
		--		,@pAgent = NULL
		--		,@pAgentName = NULL

		--	SELECT @pBankName = '[ANY WHERE]'
		--END

		DECLARE @PayerId INT = NULL

		--GET PAYER DETAILS IN CASE OF TF
		IF @payOutPartnerId = 394130
			AND @deliveryMethod = 'BANK DEPOSIT'
		BEGIN
			SELECT @PayerId = PayerId
			FROM BankPayerSetup(NOLOCK)
			WHERE BankId = @pBankId
				AND IsDefault = 1

			IF @PayerId IS NULL
			BEGIN
				EXEC proc_errorHandler 17
					,'No default payer mapped for current bank, please contact IME London Support!'
					,NULL;
           Rollback Transaction ;
				RETURN;
			END
		END

		BEGIN TRANSACTION;

		IF @PurposeOfRemittanceOther IS NOT NULL
		BEGIN
			SET @PurposeOfRemittanceOther = 'Other (please specify) :' + @PurposeOfRemittanceOther
		END

		IF @SourceOfFundOther IS NOT NULL
		BEGIN
			SET @SourceOfFundOther = 'Other (please specify) :' + @SourceOfFundOther
		END

		IF (@pBranchId IS NULL OR @pBranchId='0')
		BEGIN 
			select @pBranchId = bankLocation FROM receiverInformation WHERE receiverId = @ReceiverId

			IF(ISNULL(@pBranchId,0)=0 and ( @payOutPartnerId='394414' and @deliveryMethod = 'BANK DEPOSIT'))
			BEGIN

			SET @msg = 'Please update the Routing Branch of receiver [ ' + @receiverName + ' ] before performing transaction!';

			EXEC proc_errorHandler 21
				,@msg
				,NULL;
				Rollback Transaction ;
		       RETURN;
			END

		END
	
		PRINT 'Aaaaa'
		INSERT INTO remitTranTemp (
			controlNo
			,sCurrCostRate
			,sCurrHoMargin
			,pCurrCostRate
			,pCurrHoMargin
			,agentCrossSettRate
			,customerRate
			,serviceCharge
			,handlingFee
			,pAgentComm
			,pAgentCommCurrency
			,promotionCode
			,sSuperAgent
			,sSuperAgentName
			,sAgent
			,sAgentName
			,sBranch
			,sBranchName
			,sCountry
			,pSuperAgent
			,pSuperAgentName
			,pAgent
			,pAgentName
			,pCountry
			,paymentMethod
			,pBank
			,pBankName
			,pBankBranch
			,pBankBranchName
			,accountNo
			,collCurr
			,tAmt
			,cAmt
			,pAmt
			,payoutCurr
			,relWithSender
			,purposeOfRemit
			,sourceOfFund
			,tranStatus
			,payStatus
			,createdDate
			,createdDateLocal
			,createdBy
			,tranType
			,senderName
			,receiverName
			,isOnlineTxn
			--,schemeId
			,pState
			,pDistrict
			,sRouteId
			,schemePremium
			,collMode
			,PAYERID
			,routedBy
			,rewardPoints
			,rewardType
			,isBonusUpdated
			,depositType
			,customerPremium
			,schemeId
			)
		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
			,ISNULL(@pBankBranchName,'Headoffice')
			,@raccountNo
			,@collCurr
			,@iTAmt
			,@collAmt
			,@payoutAmt
			,@payoutCurr
			,@RelWithSender
			,ISNULL(@PurposeOfRemittance, @PurposeOfRemittanceOther)
			,ISNULL(@sourceOfFund, @SourceOfFundOther)
			,'Hold'
			,'Unpaid'
			,GETDATE()
			,GETUTCDATE()
			,@user
			,'M'
			,@senderName
			,@receiverName
			,CASE WHEN @requestedBy='online' THEN 'O' WHEN @requestedBy='mobile'  THEN 'M' ELSE  'N' END
			--,0
			,@StateId
			,@DistrictId
			,0
			,ISNULL(@schemePremium, 0)
			,'Bank Deposit'
			,@PayerId
			,CAST(@payOutPartnerId_O AS VARCHAR) + '|' + CAST(@pBankId_O AS VARCHAR)
			,ISNULL(@discountedFee, 0)
			--,CASE 
			--	WHEN ISNULL(@discountedFee, 0) > 0
			--		THEN 'REDEEM'
			--	ELSE NULL
			--	END
			,CASE 
					WHEN (ISNULL(@discountedFee, 0) > 0 AND @isPromoCode = 'Y')
						THEN ISNULL(@promoCode,'PROMOCODE')
					WHEN (ISNULL(@discountedFee, 0) > 0 AND @isPromoCode = 'N')
						THEN 'REDEEM'
					ELSE NULL
					END
			,'N'
			,@schemeId
			,@customerPremium
			,@promoRowId
			--RETURN 
			PRINT '@tranId'
		SET @tranId = SCOPE_IDENTITY();
		PRINT '@tranId'
		INSERT INTO tranSendersTemp (
			tranId
			,customerId
			,membershipId
			,firstName
			,middleName
			,lastName1
			,lastName2
			,fullName
			,country
			,[address]
			,STATE
			,district
			,address2
			,zipCode
			,city
			,email
			,homePhone
			,workPhone
			,mobile
			,nativeCountry
			,dob
			,placeOfIssue
			,idType
			,idNumber
			,idPlaceOfIssue
			,issuedDate
			,validDate
			,occupation
			,countryRiskPoint
			,customerRiskPoint
			,ipAddress
			,visaStatus
			)
		SELECT TOP 1 @tranId
			,@senderId
			,membershipId
			,firstName
			,middleName
			,lastName1
			,lastName2
			,@senderName
			,sc.countryName
			,ISNULL(city, '') + ISNULL(', ' + streetUnicode, '')
			,STATE
			,streetUnicode
			,@sAdd2
			,zipCode
			,city
			,email
			,homePhone
			,workPhone
			,LEFT(mobile, 15)
			,nativeCountry = nc.countryName
			,dob
			,c.placeOfIssue
			,sdv.detailTitle
			,c.idNumber
			,c.placeOfIssue
			,c.idIssueDate
			,c.idExpiryDate
			,c.occupation
			,@countryRisk
			,(@countryRisk + @OccupationRisk)
			,@sIpAddress
			,c.visaStatus
		FROM (
			SELECT TOP 1 *
			FROM dbo.customerMaster c WITH (NOLOCK)
			WHERE c.customerId = @senderId
			) C
		LEFT JOIN countryMaster sc WITH (NOLOCK) ON c.country = sc.countryId
		LEFT JOIN countryMaster nc WITH (NOLOCK) ON c.nativeCountry = nc.countryId
		LEFT JOIN staticDataValue sdv WITH (NOLOCK) ON c.idType = sdv.valueId

		IF @ReceiverId IS NULL
		BEGIN
			IF NOT EXISTS (
					SELECT TOP 1 'X'
					FROM receiverInformation(NOLOCK)
					WHERE fullName = @receiverName
						AND customerId = @senderId
					)
			BEGIN
				INSERT INTO receiverInformation (
					customerId
					,firstName
					,middleName
					,lastName1
					,country
					,address
					,city
					,email
					,homePhone
					,mobile
					,relationship
					,STATE
					,district
					,fullName
					,nativeCountry
					,goodsOrigin
					,goodsType
					,portOfShipment
					,relationOther
					)
				SELECT @senderId
					,@rFirstName
					,@rMiddleName
					,@rLastName
					,@pCountry
					,@rAddress
					,@rCity
					,@rEmail
					,@rMobileNo
					,@rMobileNo
					,@RelWithSender
					,@rStateId
					,@rDistrictId
					,@receiverName
					,@rNativeCountry
					,@goodsOrigin
					,@goodsType
					,@portOfShipment
					,@RelWithSenderOthers

				SET @ReceiverId = SCOPE_IDENTITY()
			END;
					--ELSE
					--               BEGIN
					--                   SELECT TOP 1 @ReceiverId = receiverId
					--                   FROM    receiverInformation(nolock)
					--                   WHERE   fullName = @receiverName AND customerId = @senderId; 
					--END;
		END;

		INSERT INTO tranReceiversTemp (
			tranId
			,customerId
			,firstName
			,middleName
			,lastName1
			,lastName2
			,fullName
			,country
			,[address]
			,[state]
			,district
			,zipCode
			,city
			,email
			,homePhone
			,workPhone
			,mobile
			,nativeCountry
			,dob
			,placeOfIssue
			,idType
			,idNumber
			,idPlaceOfIssue
			,issuedDate
			,relationType
			,validDate
			,gender
			,goodsOrigin
			,goodsType
			,portOfShipment
			)
		SELECT TOP 1 @tranId
			,@ReceiverId
			,firstName
			,middleName
			,lastName1
			,lastName2
			,@receiverName
			,@pCountry
			,[address]
			,[state]
			,district
			,zipCode
			,city
			,email
			,homePhone
			,workPhone
			,mobile
			,NULL
			,@rDob
			,NULL
			,ISNULL(@rIdType, idType)
			,ISNULL(@rIdNo, idNumber)
			,NULL
			,@rIdIssue
			,@RelWithSender
			,@rIdExpiry
			,NULL
			,@goodsOrigin
			,@goodsType
			,@portOfShipment
		FROM receiverInformation(NOLOCK)
		WHERE receiverId = @ReceiverId

		/*For duplicate pin check*/
		INSERT INTO controlNoList (
			controlNo
			,createdby
			)
		SELECT @controlNo
			,'M'

		----IF @paymentType = 'WALLET' 
		--EXEC proc_UpdateCustomerBalance @controlNo = @controlNoEncrypted, @type = 'DEDUCT'
		----## map locked ex rate with transaction for history
		UPDATE exRateCalcHistory
		SET controlNo = @controlNoEncrypted
			,AGENT_TXN_REF_ID = @tranId
			,isExpired = 1
		WHERE FOREX_SESSION_ID = @forexSessionId

		-- UPDATE FOR CUSTOMER LOYALTY	
	--	EXEC PROC_Customer_Loyalty @flag = 'check-eligible' ,  @isEligible = @isEligible OUT  -- CHECKED ABOVE

		--EXEC PROC_Customer_LoyaltyV2 @flag = 'update-v2'
		--			,@customerId = @senderId
		--			,@createdFrom = 'M'
		--			,@tranId = @tranId
		--			,@createdBy = @user
		--			,@controlNo = @controlNo
		--			,@isManualSc = 'N'
		--			,@referralCode = @introducer
		--			,@serviceCharge = @iServiceCharge

				
		--------------------------#########------------OFAC/COMPLIANCE INSERT (IF EXISTS)---------------########----------------------
		IF EXISTS (
				SELECT TOP 1 'X'
				FROM remitTranComplianceTemp WITH (NOLOCK)
				WHERE agentRefId = @agentRefId
				)
		BEGIN
			INSERT INTO remitTranCompliance (
				TranId
				,csDetailTranId
				,matchTranId
				,reason
				)
			SELECT @tranId
				,csDetailTranId
				,matchTranId
				,reason
			FROM remitTranComplianceTemp WITH (NOLOCK)
			WHERE agentRefId = @agentRefId

			SET @compFinalRes = 'C'
		END
			PRINT 'Aa'
		UPDATE ComplianceLog
		SET TRANID = @tranId
		WHERE agentRefId = @agentRefId

		IF (
				ISNULL(@compFinalRes, '') <> ''
				OR ISNULL(@ofacRes, '') <> ''
				OR ISNULL(@receiverOfacRes, '') <> ''
				)
		BEGIN
			IF (
					(
						ISNULL(@ofacRes, '') <> ''
						OR ISNULL(@receiverOfacRes, '') <> ''
						)
					AND ISNULL(@compFinalRes, '') = ''
					)
			BEGIN
				IF ISNULL(@ofacRes, '') <> ''
					INSERT remitTranOfac (
						TranId
						,blackListId
						,reason
						,flag
						)
					SELECT @tranId
						,@ofacRes
						,@ofacReason
						,dbo.FNAGetOFAC_Flag(@ofacRes)

				IF ISNULL(@receiverOfacRes, '') <> ''
					INSERT remitTranOfac (
						TranId
						,blackListId
						,reason
						,flag
						)
					SELECT @tranId
						,@receiverOfacRes
						,@ofacReason
						,dbo.FNAGetOFAC_Flag(@receiverOfacRes)

				UPDATE remitTranTemp
				SET tranStatus = 'OFAC Hold'
				WHERE id = @tranId
			END
			ELSE IF (
					@compFinalRes <> ''
					AND (
						ISNULL(@ofacRes, '') = ''
						OR ISNULL(@receiverOfacRes, '') = ''
						)
					)
			BEGIN
				UPDATE remitTranTemp
				SET tranStatus = 'Compliance Hold'
				WHERE id = @tranId
			END
			ELSE IF (
					ISNULL(@compFinalRes, '') <> ''
					AND (
						ISNULL(@ofacRes, '') <> ''
						OR ISNULL(@receiverOfacRes, '') <> ''
						)
					)
			BEGIN
				IF ISNULL(@ofacRes, '') <> ''
					INSERT remitTranOfac (
						TranId
						,blackListId
						,reason
						,flag
						)
					SELECT @tranId
						,@ofacRes
						,@ofacReason
						,dbo.FNAGetOFAC_Flag(@ofacRes)

				IF ISNULL(@receiverOfacRes, '') <> ''
					INSERT remitTranOfac (
						TranId
						,blackListId
						,reason
						,flag
						)
					SELECT @tranId
						,@receiverOfacRes
						,@ofacReason
						,dbo.FNAGetOFAC_Flag(@receiverOfacRes)

				UPDATE remitTranTemp
				SET tranStatus = 'OFAC/Compliance Hold'
				WHERE id = @tranId
			END
		END

		--Compliance checking done
		DECLARE @hasSufficientBalance CHAR(1) = 'Y'
		 --IF @agentAvlLimit < @payoutAmt 
		 --BEGIN
			--SET @hasSufficientBalance = 'N' 
			--INSERT INTO pushNotificationHistroy(customerId,body,title,createDate,imageURL,sentId,Type,isReservation,isRead,isSend,category, isClickable)
			--SELECT @SenderId,'Tran ID : '+CAST(@tranId AS VARCHAR)+' . Your transaction has been processed successfully but your balance is insufficient. Please load your wallet.','Insufficent Balance.',getdate(),'',@tranId,0,0,0,0,'INFO', 'Y'
		 --END
		 	PRINT 'A'
		IF  @schemeId='ONLINE'
		BEGIN
		SET @hasSufficientBalance='N';
		END
		ELSE
		BEGIN

		DECLARE @NewId uniqueidentifier
         SET @NewId = NEWID()		

			INSERT INTO TRU_TranDetail
	    (
	        refId,
	        email,
			createddate,
			orderreference,
			sitereference,
			Tranid,
			customerid,
			token,
			recordStatus
	    )
	    VALUES
	    (
	        @NewId,
			@user
			,GETDATE()
			,@controlNo
	        ,@sitereference
			,@tranid
			,@senderId
			,@token
			,'DRAFT'
	    )
		END


		 --New logic for referral
		 IF (ISNULL(@discountedFee, 0) > 0 AND @isPromoCode = 'N')
			EXEC proc_InsertRewardPoints @Flag = 'DEBIT', @CustomerId = @senderId, @rewardPoints = @discountedFee, @TranId = @tranId

		--EXEC proc_InsertRewardPoints @Flag = 'TRANSACTION', @CustomerId = @senderId, @TranId = @tranId

		IF @@TRANCOUNT > 0
			COMMIT TRANSACTION;

		SELECT 0 errorCode
			,'Transaction has been sent successfully' msg
			,@tranId id
			,@controlNo extra
			,ISNULL(@schemeId, 0) extra1
			,@hasSufficientBalance extra3
			,@NewId extra4
		RETURN

			-- For BroadCast Notification
			--EXEC ProcBroadCastMobile @Flag='TRANSACTION_SUCCESS', @RowId=@customerId, @ControlNo=@controlNo, @CustomerId= @customerId
	END
END TRY

BEGIN CATCH
	IF @@TRANCOUNT <> 0
		ROLLBACK TRANSACTION;

	DECLARE @errorMessage VARCHAR(MAX);

	SET @errorMessage = ERROR_MESSAGE();

	EXEC proc_errorHandler 1
		,@errorMessage
		,@user;
END CATCH