MOBILE_API/DB/SPs/proc_SendTransaction.sql

ALTER PROC [dbo].[proc_SendTransaction]
@User						VARCHAR(100)	= NULL,
@SenderId					INT				= NULL,
@sIpAddress					VARCHAR(20)		= NULL,
@ReceiverId					INT				= NULL,
@rFirstName					VARCHAR(50)		= NULL,
@rMiddleName				VARCHAR(50)		= NULL,
@rLastName					VARCHAR(50)		= NULL,
@rIdType					VARCHAR(50)		= NULL,
@rIdNo						VARCHAR(30)		= NULL,
@rIdIssue					VARCHAR(10)		= NULL,
@rIdExpiry					VARCHAR(10)		= NULL,
@rDob						VARCHAR(10)		= NULL,
@rMobileNo					VARCHAR(20)		= NULL,
@rNativeCountry				VARCHAR(50)		= NULL,
@rStateId					INT				= NULL,
@rDistrictId				INT				= NULL,
@rAddress					VARCHAR(100)	= NULL,
@rCity						VARCHAR(50)		= NULL,
@rEmail						VARCHAR(50)		= NULL,
@rAccountNo					VARCHAR(50)		= NULL,
@sCountryId					INT				= NULL,
@pCountryId					INT				= NULL,
@deliveryMethodId			INT				= NULL,
@pBankId					BIGINT			= NULL,
@pBranchId					BIGINT			= NULL,
@collCurr					VARCHAR(3)		= NULL,
@payoutCurr					VARCHAR(3)		= NULL,
@collAmt					MONEY			= NULL,
@payoutAmt					MONEY			= NULL,
@transferAmt				MONEY			= NULL,
@exRate						MONEY			= NULL,
@calBy						CHAR(1)			= NULL,
@tpExRate					DECIMAL(30,12)	= NULL,
@payOutPartnerId			BIGINT			= NULL,
@forexSessionId				VARCHAR(40)		= NULL,
@kftcLogId					BIGINT			= NULL,
@paymentType				VARCHAR(20)		= NULL,
@scDiscount					MONEY			= NULL,
@PurposeOfRemittance		VARCHAR(100)	= NULL,
@SourceOfFund				VARCHAR(100)	= NULL,
@PurposeOfRemittanceOther	VARCHAR(100)	= NULL,
@SourceOfFundOther			VARCHAR(100)	= NULL,
@RelWithSender				VARCHAR(200)	= NULL,
@SourceType					CHAR(1)			= NULL,
@schemeId					BIGINT			= NULL,
@processId					VARCHAR(40)		= NULL,
@flag						VARCHAR(100),
@controlNo					VARCHAR(20)		= NULL,
@PartnerPin					VARCHAR(20)		= NULL,
@PartnerId					VARCHAR(20)		= NULL,
@tranId						BIGINT			= NULL,
@errorCode					INT				= NULL,
@Message					NVARCHAR(500)	= NULL,
@sAdd2					NVARCHAR(150)		=	NULL

AS
-----------------------------------------------------------
	-- For Broadcast notification , @flag = 'send'
-----------------------------------------------------------
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
BEGIN TRY	
	DECLARE @complianceRuleId	INT
	,@cAmtUSD					MONEY
	,@complienceMessage			VARCHAR(1000)		=	NULL 
	,@shortMsg					VARCHAR(100)		=	NULL 
	,@complienceErrorCode		TINYINT				=	NULL
	,@compErrorCode				INT 
	,@discountType				VARCHAR(2)			=	NULL
	,@discountvalue				MONEY				=	NULL
	,@couponType				VARCHAR(3)			=	NULL
	,@discountPercent			MONEY				=	NULL
	,@couponName				VARCHAR(20)			=	NULL
	,@ServiceCharge_Temp		MONEY				=	NULL
	,@schemePremium				MONEY				=	NULL
	,@customerType 				INT					=	NULL
	,@msg						VARCHAR(MAX)		=	NULL
	

	IF EXISTS(SELECT * FROM STATICDATAVALUE(NOLOCK) WHERE VALUEID = @RelWithSender AND TYPEID = 2100)
	BEGIN
		SELECT @RelWithSender = detailTitle 
		FROM STATICDATAVALUE(NOLOCK) 
		WHERE VALUEID = @RelWithSender
		AND TYPEID = 2100
	END
	IF EXISTS(SELECT * FROM STATICDATAVALUE(NOLOCK) WHERE VALUEID = @PurposeOfRemittance AND TYPEID = 3800)
	BEGIN
		SELECT @PurposeOfRemittance = detailTitle 
		FROM STATICDATAVALUE(NOLOCK) 
		WHERE VALUEID = @PurposeOfRemittance
		AND TYPEID = 3800
	END
	IF EXISTS(SELECT * FROM STATICDATAVALUE(NOLOCK) WHERE VALUEID = @sourceOfFund AND TYPEID = 3900)
	BEGIN
		SELECT @sourceOfFund = detailTitle 
		FROM STATICDATAVALUE(NOLOCK) 
		WHERE VALUEID = @sourceOfFund
		AND TYPEID = 3900
	END
	
	IF EXISTS (SELECT * FROM API_BANK_LIST_MASTER(NOLOCK) WHERE MASTER_BANK_ID = @pBankId AND IS_ACTIVE = '1')
	BEGIN
		SELECT @pBankId = AB.BANK_ID 
		FROM API_BANK_LIST AB(NOLOCK) INNER JOIN  API_BANK_LIST_MASTER ABM(NOLOCK) ON AB.JME_BANK_CODE = ABM.JME_BANK_CODE 
		WHERE MASTER_BANK_ID = @pBankId AND ABM.IS_ACTIVE = '1'
	END
	
IF @flag = 'SEND'
BEGIN
	IF NOT EXISTS (SELECT TOP 1  'X' FROM dbo.customerMaster(nolock) WHERE username = @user AND approvedDate IS NOT NULL)
	BEGIN
		EXEC proc_errorHandler 1,'You are not authorized to perform transaction :(', NULL;
        RETURN;
	END
		
	DECLARE @kycStatus INT
		
	SELECT @kycStatus = kycStatus
	FROM TBL_CUSTOMER_KYC (NOLOCK) 
	WHERE CUSTOMERID = @senderId
	AND ISDELETED = 0
	--AND kycStatus=11044
	ORDER BY KYC_DATE 

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

	SELECT @pcountryId = cm.countryId 
	FROM receiverInformation RI(NOLOCK) 
	INNER JOIN countryMaster CM(NOLOCK) ON CM.countryName = RI.country
	WHERE RI.receiverId = @ReceiverId

	IF @paymentType IS NULL
		SET @paymentType = 'WALLET'
        
    DECLARE 
			@sCurrCostRate					FLOAT ,
			@sCurrHoMargin					FLOAT ,
			@pCurrCostRate					FLOAT ,
			@customerRate					MONEY ,
			@agentCrossSettRate				FLOAT,
			@iServiceCharge					MONEY,
			@iTAmt							MONEY,
			@iPAmt							MONEY,
			@place							INT ,
			@currDecimal					INT,
			@agentAvlLimit					MONEY,
			@serviceCharge					MONEY,
			@sCountry						VARCHAR(50)	=	'Japan',
			@sAgent							BIGINT,
			@sAgentName						VARCHAR(100),
			@sBranch						INT,
			@sBranchName					VARCHAR(100),
			@sSuperAgent					INT,
			@sSuperAgentName				VARCHAR(100),
			@senderName						VARCHAR(100),
			@sIdNo							VARCHAR(50),
			@sIdType						VARCHAR(50),
			@sMobile						VARCHAR(15),
			@pAgent							BIGINT,
			@pSuperAgent					BIGINT,
			@pSuperAgentName				VARCHAR(100),
			@pAgentName						VARCHAR(100),
			@receiverName					VARCHAR(100),
			@controlNoEncrypted				VARCHAR(30),
			@tempCompId						BIGINT,
			@pBranch						INT,
			@pBranchName					VARCHAR(100),
			@pCountry						VARCHAR(100)

	SELECT @sCountryId = 113,@sBranch = 394395
		
	SELECT @SenderId = customerId FROM customerMaster (NOLOCK) WHERE USERNAME = @User

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

	SELECT @pCountry = COUNTRYNAME 
	FROM COUNTRYMASTER (NOLOCK) 
	WHERE COUNTRYID = @pCountryId
	DECLARE @isRealTime BIT = 0

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

	IF @payOutPartnerId IS NULL
	BEGIN
		EXEC proc_errorHandler  3,'Oops, something went wrong.Please perform the transaction again' ,null
		RETURN;
	END

	SELECT TOP 1	@pAgent			=	AM.agentId
					--,@pCountryId		=	AM.agentCountryId
	FROM agentMaster AM(NOLOCK) 
	WHERE AM.parentId = @payOutPartnerId AND agentType = 2903 
	AND AM.isSettlingAgent = 'Y' AND AM.isApiPartner = 1
		
	SELECT 
		@pSuperAgentName			=	sSuperAgentName,
		@pSuperAgent				=	sSuperAgent,
		@pAgent						=	sAgent,
		@pAgentName					=	sAgentName 
	FROM dbo.FNAGetBranchFullDetails(@pAgent)

	SELECT @pBranch=@pAgent,@pBranchName=@pAgentName
		 
	DECLARE @StateId INT, @DistrictId INT
	IF @receiverId IS NOT NULL
	BEGIN
		IF NOT EXISTS(SELECT '1' FROM dbo.receiverInformation(NOLOCK) WHERE receiverId=@ReceiverId)
		BEGIN
			EXEC proc_errorHandler 4,'Receiver Data Not Match !', NULL;
			RETURN; 
		END
		SELECT TOP 1   @receiverName = ISNULL(firstName,'')+ISNULL(' '+middleName,'')+ISNULL(' '+lastName1,'') +ISNULL(' '+lastName2,'') 
					, @StateId = AI.STATE_ID, @DistrictId = AC.CITY_ID
		FROM dbo.receiverInformation RI(NOLOCK)
		LEFT JOIN API_STATE_LIST AI(NOLOCK) ON AI.STATE_NAME = RI.state AND AI.API_PARTNER_ID = @payOutPartnerId
		LEFT JOIN API_CITY_LIST AC(NOLOCK) ON AC.STATE_ID = AI.STATE_ID AND AC.CITY_NAME = RI.DISTRICT
		WHERE receiverId = @receiverId
	END
	ELSE
		SET @receiverName = ISNULL(@rFirstName,'')+ISNULL(' '+@rMiddleName,'')+ISNULL(' '+@rLastName,'')


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

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

	IF @pAgent IS NULL
	BEGIN
		EXEC proc_errorHandler  3,'Oops, something went wrong.Please perform the transaction again' ,null
		RETURN;
	END
		
	DECLARE @OccupationId INT, @sNaCountryId INT, @visaStatusId INT
	DECLARE @visaStatusText VARCHAR(200)

	SELECT TOP 1   	
			@agentAvlLimit 	=	dbo.FNAGetCustomerAvailableBalance(@SenderId),
			@senderName 	=	fullName,
			@sIdNo			=	idNumber,
			@sIdType		=	idType,
			@sMobile		=	mobile,
			@customerType 	=	customerType,
			@OccupationId   =   OCCUPATION,
			@sNaCountryId   =   NATIVECOUNTRY,
			@visaStatusId   =   VISASTATUS,
			@visaStatusText =   SV.detailTitle,
			@sAdd2          =   ISNULL(additionalAddress,'')
	FROM 	customerMaster CM(NOLOCK) 
	LEFT JOIN STATICDATAVALUE SV(NOLOCK) ON SV.valueId = CM.visaStatus
	WHERE 	username = @User AND customerId = @SenderId

	IF ISNULL(@paymentType,'') NOT IN ('wallet')
	BEGIN
		EXEC proc_errorHandler 7,'Invalid payment method.Please perform the transaction again!', NULL;
        RETURN;
	END
	
	IF @user in('demo.gme@gmeremit.com')
	BEGIN
		EXEC proc_errorHandler 8,'You can not send money through test GME acocunt :(', NULL;
        RETURN;
	END
		
    IF ISNULL(@collAmt, 0) = 0
    BEGIN
        EXEC proc_errorHandler 9, 'Collection Amount is missing. Cannot send transaction',NULL;
        RETURN;	
    END; 
		
    SET @controlNo = '21' + LEFT(CAST(ABS(CHECKSUM(NEWID())) AS VARCHAR(10)) + '0000000000', 7)
    SELECT  @controlNoEncrypted = dbo.FNAEncryptString(@controlNo);
			
    IF EXISTS (SELECT TOP 1   'X' FROM pinQueueList WITH(NOLOCK) WHERE icn = @controlNoEncrypted) 
    BEGIN
		SET @controlNo = '21' + LEFT(CAST(ABS(CHECKSUM(NEWID())) AS VARCHAR(10)) + '0000000000', 7)
		SELECT  @controlNoEncrypted = dbo.FNAEncryptString(@controlNo);

		IF EXISTS(SELECT TOP 1   'X' FROM pinQueueList WITH(NOLOCK) WHERE icn = @controlNoEncrypted) 
		BEGIN
			EXEC proc_errorHandler 10, 'Technical error occurred. Please try again',NULL;
			RETURN;
		END
    END;
		
    IF @deliveryMethodId = 2 AND @pCountryId<>'151'
    BEGIN                               
		IF NOT EXISTS(SELECT TOP 1   'A' FROM api_bank_list(nolock) where BANK_ID = @pBankId and PAYMENT_TYPE_ID in(0,2) and IS_ACTIVE = 1)
		BEGIN	
			EXEC proc_errorHandler 11, 'Invalid bank selected', NULL
			return
		END
		IF @raccountNo IS NULL
		BEGIN
			EXEC proc_errorHandler 12, 'Account number cannot be blank', NULL
			RETURN
		END
    END;

	DECLARE @pAgentCommCurrency VARCHAR(3),@pAgentComm MONEY

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

	SELECT @pAgentComm = amount FROM dbo.FNAGetPayComm(@sAgent,@sCountryId, 
							NULL, null, @pCountryId, null, @pAgent, @pAgentCommCurrency
							,@deliveryMethodId, @collAmt, @payoutAmt, @serviceCharge, @transferAmt, NULL)
		
	--4. Get Exchange Rate Details------------------------------------------------------------------------------------------------------------------
		DECLARE @pCurrHoMargin FLOAT

		SELECT 
				@customerRate			= customerRate
			,@sCurrCostRate			= sCurrCostRate
			,@sCurrHoMargin			= sCurrHoMargin
			,@pCurrCostRate			= pCurrCostRate
			,@agentCrossSettRate	= agentCrossSettRate
			,@serviceCharge			= serviceCharge
			,@iPAmt					= pAmt
			,@schemeId				= schemeId
			,@pCurrHoMargin			= pCurrHoMargin
		FROM exRateCalcHistory(NOLOCK) 
		WHERE FOREX_SESSION_ID = @forexSessionId AND [USER_ID] = @user

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

		SELECT @iServiceCharge = ISNULL(amount, -1) 
		FROM [dbo].FNAGetServiceCharge(
							@sCountryId, @sSuperAgent, @sAgent, @sBranch, 
							@pCountryId, @pSuperAgent, @pAgent, @pBranch, 
							@deliveryMethodId, @collAmt, @collCurr
						) 
		IF @iServiceCharge = -1
		BEGIN
			EXEC proc_errorHandler 13, 'Transaction cannot be proceed. Service Charge is not defined', NULL
			RETURN
		END
			
		IF ISNULL(@iServiceCharge,0) <> ISNULL(@serviceCharge,1)
		BEGIN
			EXEC proc_errorHandler 14, 'Transaction cannot be proceed. Amount detail not match', NULL
			RETURN
		END 

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

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

		SET @currDecimal = ISNULL(@currDecimal, 0)
		SET @place = ISNULL(@place, 0)
			
		SET @iPAmt = @iTAmt * @CustomerRate
			
		IF @payoutAmt - @iPAmt <= 1
			SET @iPAmt = @payoutAmt

		----## WHILE CALCULATING FROM PAYOUT AMOUNT CONSIDARING 10 VND 
		IF ISNULL(@iPAmt,0) <> ISNULL(@payoutAmt,1)
		BEGIN
			--SET @Msg = 'Amount detail not match. Please re-calculate the amount again.' + CAST(@iPAmt AS VARCHAR) + ' - ' +  CAST(@payoutAmt AS VARCHAR)
			EXEC proc_errorHandler 15, 'Amount detail not match. Please re-calculate the amount again.', NULL
			RETURN
		END
			
		----OFAC Checking
		DECLARE @receiverOfacRes VARCHAR(MAX), @ofacRes VARCHAR(MAX), @ofacReason VARCHAR(200)
		EXEC proc_ofacTracker @flag = 't', @name = @senderName, @Result = @ofacRes OUTPUT
		EXEC proc_ofacTracker @flag = 't', @name = @receiverName, @Result = @receiverOfacRes OUTPUT
			
		DECLARE @result VARCHAR(MAX)
		IF ISNULL(@ofacRes, '') <> ''
		BEGIN
			SET @ofacReason = 'Matched by sender name'
		END
		IF ISNULL(@receiverOfacRes, '') <> ''
		BEGIN
			SET @ofacRes = ISNULL(@ofacRes + ',' + @receiverOfacRes, '' + @receiverOfacRes)
			SET @ofacReason = 'Matched by receiver name'
		END
		IF ISNULL(@ofacRes, '') <> '' AND ISNULL(@receiverOfacRes, '') <> ''
		BEGIN
			SET @ofacReason = 'Matched by both sender name and receiver name'
		END
		--Ofac Checking End

		DECLARE @agentRefId VARCHAR(50) = NEWID()
		----Compliance Checking 1-> Block, 2-> Hold, 3-> Questionnaire
		CREATE TABLE #TBL_COMPLIANCE_RESULT (ERROR_CODE INT, MSG VARCHAR(500), RULE_ID INT, SHORT_MSG VARCHAR(100), [TYPE] VARCHAR(10)
					, IS_D0C_REQUIRED BIT
				)

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

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

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

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

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

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

			INSERT remitTranComplianceTemp (
				csDetailTranId
				,matchTranId
				,agentRefId
				,reason
				)
			SELECT 0
				,NULL
				,@agentRefId
				,'Questionnaire require due to Visa Status'
		END
		
		--**********Customer Per Day Limit Checking**********
        DECLARE @remitTranTemp TABLE (
                tranId BIGINT,controlNo VARCHAR(20),cAmt MONEY,receiverName VARCHAR(200) ,
                receiverIdType VARCHAR(100),receiverIdNumber VARCHAR(50),dot DATETIME
            );
            
        INSERT INTO @remitTranTemp( tranId ,controlNo ,cAmt ,receiverName ,receiverIdType ,receiverIdNumber ,dot )
        SELECT TOP 10 rt.id ,rt.controlNo ,rt.cAmt ,rt.receiverName ,rec.idType ,rec.idNumber ,rt.createdDate
		FROM vwRemitTran rt WITH(NOLOCK)
		INNER JOIN vwTranSenders sen WITH(NOLOCK) ON rt.id = sen.tranId
		INNER JOIN vwTranReceivers rec WITH(NOLOCK) ON rt.id = rec.tranId
		WHERE sen.customerId = @SenderId
		AND ( rt.approvedDate BETWEEN CONVERT(VARCHAR,GETDATE(),101) AND CONVERT(VARCHAR,GETDATE(),101)+ ' 23:59:59'
				OR ( approvedBy IS NULL AND cancelApprovedBy IS NULL )
			)
		ORDER BY rt.createdDate DESC

        IF EXISTS ( SELECT TOP 1    'X' FROM @remitTranTemp
                    WHERE   cAmt = @collAmt
                    AND ( receiverName = @receiverName ) AND DATEDIFF(MI, dot, GETDATE()) <= 2 
					)
		BEGIN
			EXEC proc_errorHandler 16, 'Similar transaction found. Please perform the transaction after 2 minutes.', NULL;
			RETURN;
		END;
			
		DECLARE @countryRisk INT,@OccupationRisk INT,@compFinalRes VARCHAR(5)
			
		-- #########country and occupation  risk point
        DECLARE @deliveryMethod VARCHAR(30),@pBankName VARCHAR(100),@pBankBranchName VARCHAR(100),@pBankRowId bigint
			
		SELECT TOP 1   @pCountry = COUNTRYNAME FROM countryMaster(NOLOCK) WHERE countryId = @pCountryId
		SELECT TOP 1   @deliveryMethod = typeTitle FROM serviceTypeMaster(NOLOCK) WHERE serviceTypeId = @deliveryMethodId

		SELECT TOP 1   @pBankName = bank_name,@pBankRowId = bank_id
						FROM api_bank_list
						WHERE bank_id = @pBankId
						AND is_Active = 1
		SELECT top 1   @pBankBranchName = branch_name from api_bank_branch_list
						WHERE bank_id = @pBankRowId
						AND branch_id = @pBranchId
						AND is_Active = 1
		
		DECLARE @VNo VARCHAR(20); 
			
		IF @pCountry = 'Nepal' AND @deliveryMethod = 'CASH PAYMENT'
		BEGIN
			SELECT @pSuperAgent = NULL, @pSuperAgentName = NULL, @pAgent = NULL, @pAgentName = NULL
			SELECT @pBankName = '[ANY WHERE]'
		END
			
		DECLARE @PayerId INT = NULL
		--GET PAYER DETAILS IN CASE OF TF
		IF @payOutPartnerId = 394130 AND @deliveryMethod = 'BANK DEPOSIT'
		BEGIN
			SELECT @PayerId = PayerId
			FROM BankPayerSetup(NOLOCK)
			WHERE BankId = @pBankId
			AND IsDefault = 1

			IF @PayerId IS NULL
			BEGIN
				EXEC proc_errorHandler 17, 'No default payer mapped for current bank, please contact HQ.', NULL;
				RETURN;
			END
		END

			BEGIN TRANSACTION;
				INSERT  INTO remitTranTemp
				( 
				controlNo ,sCurrCostRate ,sCurrHoMargin ,pCurrCostRate, pCurrHoMargin ,agentCrossSettRate ,customerRate ,
				serviceCharge ,handlingFee ,pAgentComm ,pAgentCommCurrency ,
				promotionCode ,sSuperAgent ,sSuperAgentName ,sAgent ,sAgentName ,sBranch ,sBranchName ,sCountry ,
				pSuperAgent ,pSuperAgentName ,pAgent ,pAgentName ,pCountry ,paymentMethod ,pBank ,pBankName ,pBankBranch ,pBankBranchName ,accountNo ,
				collCurr ,tAmt ,cAmt ,pAmt ,payoutCurr ,relWithSender ,purposeOfRemit ,sourceOfFund ,tranStatus ,payStatus ,createdDate ,
				createdDateLocal ,createdBy ,tranType ,senderName ,receiverName ,isOnlineTxn ,schemeId,pState,pDistrict,
				sRouteId,schemePremium,collMode,PAYERID
				)
				SELECT TOP 1   
				@controlNoEncrypted ,@sCurrCostRate ,@sCurrHoMargin ,@pCurrCostRate, @pCurrHoMargin ,@agentCrossSettRate ,@customerRate,
				@serviceCharge ,ISNULL(@scDiscount, 0) ,@pAgentComm ,@pAgentCommCurrency ,
				null, @sSuperAgent , @sSuperAgentName ,@sAgent ,@sAgentName ,@sBranch ,@sBranchName ,@sCountry , 
				@pSuperAgent ,@pSuperAgentName , @pAgent , @pAgentName ,@pCountry ,@deliveryMethod ,@pBankId , @pBankName ,@pBranchId ,@pBankBranchName ,@raccountNo ,
				@collCurr ,@iTAmt , @collAmt ,@payoutAmt , @payoutCurr , @RelWithSender , ISNULL(@PurposeOfRemittance, @PurposeOfRemittanceOther) ,ISNULL(@sourceOfFund, @SourceOfFundOther) ,'Hold' ,'Unpaid' ,GETDATE() ,
				GETUTCDATE() , @user ,'M'  , @senderName , @receiverName, 'Y' ,@schemeId,@StateId,@DistrictId,
				0,ISNULL(@schemePremium, 0),'Bank Deposit',@PayerId
				
				SET @tranId = SCOPE_IDENTITY();	

				INSERT  INTO tranSendersTemp
						( tranId , customerId ,membershipId ,firstName , middleName ,lastName1 ,lastName2 ,
							fullName ,country ,[address], state, district ,address2 ,zipCode ,city ,email ,homePhone ,
							workPhone ,mobile ,nativeCountry ,dob ,placeOfIssue ,idType ,idNumber ,idPlaceOfIssue ,
							issuedDate ,validDate ,occupation ,countryRiskPoint ,customerRiskPoint ,ipAddress
						)
				SELECT TOP 1
						@tranId ,@senderId ,membershipId ,firstName ,middleName ,lastName1 ,lastName2 ,
						@senderName ,sc.countryName ,ISNULL(city, '')+ISNULL(', '+streetUnicode, ''), state, streetUnicode ,@sAdd2 ,zipCode ,city ,email ,homePhone ,
						workPhone ,LEFT(mobile, 15) ,nativeCountry = nc.countryName ,dob ,c.placeOfIssue ,sdv.detailTitle ,c.idNumber ,c.placeOfIssue ,
						c.idIssueDate ,c.idExpiryDate ,c.occupation ,@countryRisk ,( @countryRisk + @OccupationRisk ) ,@sIpAddress
				FROM   (SELECT TOP 1 * FROM dbo.customerMaster c WITH ( NOLOCK ) WHERE c.customerId = @senderId) C
				LEFT JOIN countryMaster sc WITH ( NOLOCK ) ON c.country = sc.countryId
				LEFT JOIN countryMaster nc WITH ( NOLOCK ) ON c.nativeCountry = nc.countryId
				LEFT JOIN staticDataValue sdv WITH ( NOLOCK ) ON c.idType = sdv.valueId
				
                IF @ReceiverId IS NULL
                BEGIN
					IF NOT EXISTS ( SELECT TOP 1    'X'
                                FROM    receiverInformation(nolock)
                                WHERE   fullName = @receiverName AND customerId = @senderId )
                    BEGIN				 
						INSERT INTO receiverInformation
						( customerId ,firstName,middleName,lastName1 ,country ,address ,city ,email  
							,homePhone ,mobile ,relationship,state,district,fullName,nativeCountry)
						SELECT  @senderId ,@rFirstName,@rMiddleName,@rLastName,@pCountry,@rAddress,@rCity,@rEmail
							,@rMobileNo,@rMobileNo,@RelWithSender,@rStateId,@rDistrictId,@receiverName,@rNativeCountry
						
						SET @ReceiverId = SCOPE_IDENTITY()
                    END;
					--ELSE
     --               BEGIN
     --                   SELECT TOP 1 @ReceiverId = receiverId
     --                   FROM    receiverInformation(nolock)
     --                   WHERE   fullName = @receiverName AND customerId = @senderId; 
					--END;
                END;
											
				INSERT  INTO tranReceiversTemp( tranId ,customerId  ,firstName ,middleName ,lastName1 ,lastName2 ,fullName ,
					country ,[address] ,[state] ,district ,zipCode ,city ,email ,homePhone ,workPhone ,mobile ,nativeCountry ,dob ,
					placeOfIssue ,idType ,idNumber ,idPlaceOfIssue ,issuedDate ,relationType,validDate ,gender
								)
				SELECT TOP 1   @tranId,@ReceiverId,firstName,middleName ,lastName1 ,lastName2 ,@receiverName ,
					@pCountry ,[address] ,[state] ,district ,zipCode ,city ,email ,homePhone ,workPhone ,mobile ,country ,@rDob ,
					null ,ISNULL(@rIdType,idType) ,ISNULL(@rIdNo,idNumber) ,null ,@rIdIssue ,@RelWithSender,@rIdExpiry ,null
				FROM receiverInformation(NOLOCK) WHERE receiverId = @ReceiverId
			
				----IF @paymentType = 'WALLET' 
				--EXEC proc_UpdateCustomerBalance @controlNo = @controlNoEncrypted, @type = 'DEDUCT'
				
				----## map locked ex rate with transaction for history
				UPDATE exRateCalcHistory set controlNo = @controlNoEncrypted,AGENT_TXN_REF_ID = @tranId,isExpired = 1 where FOREX_SESSION_ID = @forexSessionId
				--------------------------#########------------OFAC/COMPLIANCE INSERT (IF EXISTS)---------------########----------------------
				IF EXISTS(SELECT TOP 1 'X' FROM remitTranComplianceTemp WITH(NOLOCK) WHERE agentRefId = @agentRefId)
				BEGIN
					INSERT INTO remitTranCompliance(TranId, csDetailTranId, matchTranId, reason)
					SELECT @tranId, csDetailTranId, matchTranId, reason
					FROM remitTranComplianceTemp WITH(NOLOCK) 
					WHERE agentRefId = @agentRefId

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

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

						UPDATE remitTranTemp SET
								tranStatus	= 'OFAC Hold'
						WHERE id = @tranId
					END
					
					ELSE IF(@compFinalRes <> '' AND (ISNULL(@ofacRes, '') = '' OR ISNULL(@receiverOfacRes, '') = ''))
					BEGIN
						UPDATE remitTranTemp SET
								tranStatus	= 'Compliance Hold'
						WHERE id = @tranId
					END
			
					ELSE IF(ISNULL(@compFinalRes, '') <> '' AND (ISNULL(@ofacRes, '') <> '' OR ISNULL(@receiverOfacRes, '') <> ''))
					BEGIN
						IF ISNULL(@ofacRes, '') <> ''
							INSERT remitTranOfac(TranId, blackListId, reason, flag)
							SELECT @tranId, @ofacRes, @ofacReason, dbo.FNAGetOFAC_Flag(@ofacRes)
						
						IF ISNULL(@receiverOfacRes, '') <> ''
							INSERT remitTranOfac(TranId, blackListId, reason, flag)
							SELECT @tranId, @receiverOfacRes, @ofacReason, dbo.FNAGetOFAC_Flag(@receiverOfacRes)
				
						UPDATE remitTranTemp SET
								tranStatus	= 'OFAC/Compliance Hold'
						WHERE id = @tranId
					END
				END
				--Compliance checking done
		
				IF @@TRANCOUNT > 0
				COMMIT TRANSACTION;
					SElect 0 errorCode, 'Transaction has been sent successfully' msg, @tranId id,@controlNo extra, ISNULL(@isRealTime, 0) extra2
			
				RETURN   

				DECLARE @CustomerId  BIGINT , @availableBalance MONEY
				SELECT  @customerId = (SELECT TST.customerId FROM remitTrantemp RTT WITH(NOLOCK) INNER JOIN dbo.tranSendersTemp TST(NOLOCK) ON TST.TRANID = RTT.ID WHERE controlNo = @controlNo)

				-- For BroadCast Notification
				EXEC ProcBroadCastMobile @Flag='TRANSACTION_SUCCESS', @RowId=@customerId, @ControlNo=@controlNo, @CustomerId= @customerId
END 
END TRY
BEGIN CATCH
    IF @@TRANCOUNT <> 0
        ROLLBACK TRANSACTION;
		
    DECLARE @errorMessage VARCHAR(MAX);
    SET @errorMessage = ERROR_MESSAGE();
	
    EXEC proc_errorHandler 1, @errorMessage, @user;
	
END CATCH