INBOUND_SEND_API/DB/SP's/PROC_REMIT_INBOUND_TRANSACTION_VALIDATE.sql

GO
use FastMoneyPro_Remit;

GO
CREATE OR ALTER   PROC PROC_REMIT_INBOUND_TRANSACTION_VALIDATE
(
	@Flag VARCHAR(20)
	, @UserName VARCHAR(80) = NULL
	, @SendingCountryCode VARCHAR(10) = NULL
	, @PayoutCountryCode VARCHAR(10) = NULL
	, @PayoutTypeCode VARCHAR(10) = NULL
	, @PayoutBankCode VARCHAR(10) = NULL
	, @PayoutBankBranchCode VARCHAR(10) = NULL
	, @PayoutCurrencyCode VARCHAR(10) = NULL
	, @SendingCurrencyCode VARCHAR(10) = NULL
	, @CollectionAmount MONEY = NULL
	, @TransferAmount MONEY = NULL
	, @PayoutAmount MONEY = NULL
	, @ServiceCharge MONEY = NULL
	, @ExchangeRate FLOAT = NULL
	, @RemittancePurpose INT = NULL
	, @SourceOfFund INT = NULL
	, @Relationship INT = NULL
	, @Occupation INT = NULL
	, @PartnerTransactionId VARCHAR(40) = NULL
	, @CalcBy CHAR(1) = NULL
	, @AgentId INT = NULL
	--SENDER DETAILS
	, @SenderFirstName VARCHAR(60) = NULL
	, @SenderMiddleName VARCHAR(60) = NULL
	, @SenderLastName VARCHAR(40) = NULL
	, @SenderIdType INT = NULL
	, @SenderIdNo VARCHAR(20) = NULL
	, @SenderIdIssuedDate VARCHAR(10) = NULL
	, @SenderIdValidDate VARCHAR(10) = NULL
	, @SenderMobile VARCHAR(15) = NULL
	, @SenderNativeCountryCode VARCHAR(10) = NULL
	, @SenderCity VARCHAR(80) = NULL
	, @SenderAddress VARCHAR(150) = NULL
	, @SenderEmail VARCHAR(150) = NULL
	, @SenderGender VARCHAR(10) = NULL
	, @SenderDOB VARCHAR(10) = NULL
	--RECEIVER DETAILS
	, @ReceiverFirstName VARCHAR(60) = NULL
	, @ReceiverMiddleName VARCHAR(60) = NULL
	, @ReceiverLastName VARCHAR(60) = NULL
	, @ReceiverIdType INT = NULL
	, @ReceiverIdNo VARCHAR(20) = NULL
	, @ReceiverIdIssuedDate VARCHAR(10) = NULL
	, @ReceiverIdValidDate VARCHAR(10) = NULL
	, @ReceiverMobile VARCHAR(15) = NULL
	, @ReceiverCity VARCHAR(80) = NULL
	, @ReceiverAddress VARCHAR(150) = NULL
	, @ReceiverEmail VARCHAR(150) = NULL
	, @ReceiverAccountNo VARCHAR(40) = NULL
	, @IpAddress VARCHAR(20) = NULL
)
AS;
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
	BEGIN
		DECLARE @ErrorMsg VARCHAR(MAX) = NULL
		IF @Flag = 'VALIDATE'
		BEGIN
			--IF EXISTS(SELECT * FROM TBL_INBOUND_TRANSACTION_VERIFY (NOLOCK) WHERE PartnerTransactionId = @PartnerTransactionId)
			--BEGIN
			--	EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Duplicate partner transaction id.', @Id = NULL;
			--	RETURN;
			--END

			DECLARE @PCountryId INT, @DeliveryMethodId INT, @PayoutPartner INT, @ExRateCalcByPartner BIT, @ScountryId INT
			DECLARE @SSuperAgentId INT, @PAgent INT, @SAgentId INT = @AgentId, @PCountryName VARCHAR(80), @SCountryName VARCHAR(80)
			
			SELECT @PCountryId = countryId, @PCountryName = countryName
			FROM COUNTRYMASTER (NOLOCK)
			WHERE countryCode = @PayoutCountryCode

			IF ISNULL(@PCountryId, 0) = 0
			BEGIN
				EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid PayoutCountryCode, contact Head Office.', @Id = NULL;
				RETURN;
			END

			SELECT @ScountryId = countryId, @SCountryName = countryName
			FROM COUNTRYMASTER (NOLOCK)
			WHERE countryCode = @SendingCountryCode

			IF ISNULL(@ScountryId, 0) = 0
			BEGIN
				EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid SendingCountryCode, contact Head Office.', @Id = NULL;
				RETURN;
			END
			
			DECLARE @DeliveryMethodName VARCHAR(40)
			SELECT @DeliveryMethodId = serviceTypeId, @DeliveryMethodName = typeTitle
			FROM SERVICETYPEMASTER (NOLOCK)
			WHERE serviceCode = @PayoutTypeCode

			IF ISNULL(@DeliveryMethodId, 0) = 0
			BEGIN
				EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid PaymentTypeCode, contact Head Office.', @Id = NULL;
				RETURN;
			END
			
			SELECT @PayoutPartner = AGENTID
				,@ExRateCalcByPartner = ISNULL(exRateCalByPartner, 0)
			FROM TblPartnerwiseCountry(NOLOCK)
			WHERE CountryId = @PCountryId
			AND IsActive = 1
			AND ISNULL(PaymentMethod, @DeliveryMethodId) = @DeliveryMethodId

			IF ISNULL(@PayoutPartner, 0) = 0
			BEGIN
				EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Pyout partner not defined for selected Payout Country, contact Head Office.', @Id = NULL;
				RETURN;
			END

			SELECT @PAgent = AGENTID
			FROM AGENTMASTER(NOLOCK)
			WHERE PARENTID = @PayoutPartner
			AND ISNULL(ISSETTLINGAGENT, 'N') = 'Y';

			DECLARE @PSuperAgent INT, @PSuperAgentName VARCHAR(150), @PAgentName VARCHAR(150), @PBranch INT, @PBranchName VARCHAR(150)
			SELECT @PSuperAgent = sSuperAgent
				,@PSuperAgentName = sSuperAgentName
				,@PAgent = sAgent
				,@PAgentName = sAgentName
				,@PBranch = sBranch
				,@PBranchName = sBranchName
			FROM dbo.FNAGetBranchFullDetails(@PAGENT)

			DECLARE @PCurrencyCode VARCHAR(10)
			SELECT @PCurrencyCode = pCurrency
			FROM dbo.exRateTreasury WITH (NOLOCK)
			WHERE pCountry = @PCountryId
			AND pAgent = @PAgent

			IF @PCurrencyCode IS NULL
				SELECT @PCurrencyCode = pCurrency
				FROM dbo.exRateTreasury WITH (NOLOCK)
				WHERE pCountry = @PCountryId
				AND pAgent IS NULL

			IF @PCurrencyCode IS NULL
			BEGIN
				EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Currency not been defined yet for receiving country, contact Head Office.', @Id = NULL;
				RETURN;
			END

			IF @PCurrencyCode <> @PayoutCurrencyCode
			BEGIN
				EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid PayoutCurrencyCode, contact Head Office.', @Id = NULL;
				RETURN;
			END
			
			DECLARE @Place INT, @CurrDecimal INT
			SELECT @Place = place
				,@CurrDecimal = currDecimal
			FROM currencyPayoutRound WITH (NOLOCK)
			WHERE ISNULL(isDeleted, 'N') = 'N'
			AND currency = @PCurrencyCode
			AND ISNULL(tranType, @DeliveryMethodId) = @DeliveryMethodId

			SET @currDecimal = ISNULL(@currDecimal, 0)

			
			DECLARE @ExRateCalculated FLOAT, @sCurrCostRate FLOAT, @sCurrHoMargin FLOAT, @sCurrAgentMargin FLOAT
					, @pCurrCostRate FLOAT, @pCurrHoMargin FLOAT, @pCurrAgentMargin FLOAT, @agentCrossSettRate FLOAT

			SELECT @ExRateCalculated = customerRate
				,@sCurrCostRate = sCurrCostRate
				,@sCurrHoMargin = sCurrHoMargin
				,@sCurrAgentMargin = sCurrAgentMargin
				,@pCurrCostRate = pCurrCostRate
				,@pCurrHoMargin = pCurrHoMargin
				,@pCurrAgentMargin = pCurrAgentMargin
				,@agentCrossSettRate = agentCrossSettRate
			FROM dbo.FNAGetExRate(@ScountryId, @SAgentId, NULL, @SendingCurrencyCode, @pCountryId, @pAgent, @PayoutCurrencyCode, @DeliveryMethodId)
			
			IF ISNULL(@ExRateCalculated, 0) = 0
			BEGIN
				SET @ErrorMsg = 'Exchange rate not defined yet for receiving currency (' + @PCurrencyCode + ')'
				EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = @ErrorMsg, @Id = NULL;
				RETURN;
			END
			IF ISNULL(@ExRateCalculated, 0) <> ISNULL(@ExchangeRate, -1)
			BEGIN
				EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid exchange rate passed or exchange rate changed in the system, recalculate the amount details and try again!', @Id = NULL;
				RETURN;
			END

			DECLARE @ServiceChargeCalculated MONEY, @TransferAmountCalculated MONEY, @PayoutAmountCalculated MONEY, @CollectionAmountCalculated MONEY
			DECLARE @SSuperAgentName VARCHAR(150), @SAgentName VARCHAR(150), @SBranch INT, @SBranchName VARCHAR(150)
			SELECT @SSuperAgentId = sSuperAgent
				,@SSuperAgentName = sSuperAgentName
				,@SAgentName = sAgentName
				,@SBranch = sBranch
				,@SBranchName = sBranchName
			FROM dbo.FNAGetBranchFullDetails(@SAgentId)

			IF ISNULL(@CalcBy, '') = ''
			BEGIN
				EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Calc by can not be empty!', @Id = NULL;
				RETURN;
			END
			IF @CalcBy NOT IN ('C', 'P')
			BEGIN
				EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Calc by can not be other than ''C'' or ''P''!', @Id = NULL;
				RETURN;
			END
			IF @CalcBy = 'C' AND ISNULL(@CollectionAmount, 0) = 0
			BEGIN
				EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Collection amount can not be 0 or empty for Calc By ''C''!', @Id = NULL;
				RETURN;
			END
			IF @CalcBy = 'P' AND ISNULL(@PayoutAmount, 0) = 0
			BEGIN
				EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Payout amount can not be 0 or empty for Calc By ''P''!', @Id = NULL;
				RETURN;
			END

			IF @CalcBy = 'C'
			BEGIN
				SELECT @ServiceChargeCalculated = amount
				FROM [dbo].FNAGetServiceCharge(@ScountryId, @SSuperAgentId, @SAgentId, NULL, @PCountryId, @PSuperAgent, @PAgent, @PBranch, @DeliveryMethodId, @CollectionAmount, @SendingCurrencyCode)
				
				IF ISNULL(@ServiceChargeCalculated, 0) = 0
				BEGIN
					EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Service charge not defined yet for receiving country', @Id = NULL;
					RETURN;
				END

				SET @TransferAmountCalculated = @CollectionAmount - @ServiceChargeCalculated
				SET @PayoutAmountCalculated = @TransferAmountCalculated * @ExRateCalculated
				SET @PayoutAmountCalculated = FLOOR(@PayoutAmountCalculated)
			END
			ELSE
			BEGIN
				SET @TransferAmountCalculated = CEILING(@PayoutAmount / (@ExRateCalculated))

				SELECT @ServiceChargeCalculated = amount
				FROM [dbo].FNAGetServiceCharge(@ScountryId, @SSuperAgentId, @SAgentId, NULL, @PCountryId, @PSuperAgent, @PAgent, @PBranch, @DeliveryMethodId, @CollectionAmount, @SendingCurrencyCode)

				IF ISNULL(@ServiceChargeCalculated, 0) = 0
				BEGIN
					EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Service charge not defined yet for receiving country', @Id = NULL;
					RETURN;
				END

				SET @CollectionAmountCalculated = @TransferAmountCalculated + @ServiceChargeCalculated
				SET @CollectionAmountCalculated = CEILING(@CollectionAmountCalculated)
			END
			
			IF @CollectionAmountCalculated <= 0 OR @PayoutAmountCalculated <= 0 OR @TransferAmountCalculated <= 0
			BEGIN
				EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid payout/transfer amount/collect amount found!', @Id = NULL;
				RETURN;
			END

			IF @CalcBy = 'C'
			BEGIN
				IF ISNULL(@PayoutAmount, 0) <> ISNULL(@PayoutAmountCalculated, -1)
				BEGIN
					EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid Payout Amount passed/Amount details changed, recalculate the amount details and try again!', @Id = NULL;
					RETURN;
				END
				IF ISNULL(@TransferAmount, 0) <> ISNULL(@TransferAmountCalculated, -1)
				BEGIN
					EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid Payout Amount passed/Amount details changed, recalculate the amount details and try again!', @Id = NULL;
					RETURN;
				END
			END
			ELSE
			BEGIN
				IF ISNULL(@CollectionAmount, 0) <> ISNULL(@CollectionAmountCalculated, -1)
				BEGIN
					EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid Collection Amount passed/Amount details changed, recalculate the amount details and try again!', @Id = NULL;
					RETURN;
				END
				IF ISNULL(@TransferAmount, 0) <> ISNULL(@TransferAmountCalculated, -1)
				BEGIN
					EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid Payout Amount passed/Amount details changed, recalculate the amount details and try again!', @Id = NULL;
					RETURN;
				END
			END

			DECLARE @ErrorCode VARCHAR(10)
			EXEC PROC_CHECKCOUNTRYLIMIT @flag = 's-limit'
				,@cAmt = @CollectionAmount
				,@pAmt = @PayoutAmount
				,@sCountryId = @sCountryId
				,@collMode = NULL
				,@deliveryMethod = @DeliveryMethodId
				,@sendingCustType = NULL
				,@pCountryId = @PCountryId
				,@pCurr = @PayoutCurrencyCode
				,@collCurr = @SendingCurrencyCode
				,@pAgent = @PAgent
				,@sAgent = @SAgentId
				,@sBranch = NULL
				,@msg = @ErrorMsg OUT
				,@errorCode = @ErrorCode OUT

			IF @ErrorCode <> '0'
			BEGIN
				EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = @ErrorMsg, @Id = NULL;
				RETURN;
			END

			EXEC PROC_CHECKCOUNTRYLIMIT @flag = 'r-limit'
				,@cAmt = @CollectionAmount
				,@pAmt = @PayoutAmount
				,@sCountryId = @sCountryId
				,@collMode = NULL
				,@deliveryMethod = @DeliveryMethodId
				,@sendingCustType = NULL
				,@pCountryId = @PCountryId
				,@pCurr = @PayoutCurrencyCode
				,@collCurr = @SendingCurrencyCode
				,@pAgent = @PAgent
				,@sAgent = @SAgentId
				,@sBranch = NULL
				,@msg = @ErrorMsg OUT
				,@errorCode = @ErrorCode OUT

			IF @ErrorCode <> '0'
			BEGIN
				EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = @ErrorMsg, @Id = NULL;
				RETURN;
			END

			DECLARE @PBankId INT, @PBankName VARCHAR(100), @PBankBranchId INT, @PBankBranchName VARCHAR(100)
			IF @PayoutTypeCode = 'BD'
			BEGIN
				IF @PayoutBankCode IS NULL
				BEGIN
					EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Payout Bank can not be null for transaction type Bank Deposit!', @Id = NULL;
					RETURN;
				END
				IF @ReceiverAccountNo IS NULL
				BEGIN
					EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Receiver Account Number can not be null for transaction type Bank Deposit!', @Id = NULL;
					RETURN;
				END

				SELECT @PBankId = BANK_ID, @PBankName = BANK_NAME
				FROM API_BANK_LIST (NOLOCK)
				WHERE JME_BANK_CODE = @PayoutBankCode
				AND API_PARTNER_ID = @PayoutPartner

				IF ISNULL(@PBankId, 0) = 0
				BEGIN
					EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid bank details!!', @Id = NULL;
					RETURN;
				END

				SELECT @PBankBranchId = BRANCH_ID, @PBankBranchName = BRANCH_NAME
				FROM API_BANK_BRANCH_LIST (NOLOCK)
				WHERE BRANCH_ID = @PayoutBankBranchCode
			END


			--START OFAC Checking     
			DECLARE @ReceiverFullName VARCHAR(200)
				, @SenderFullName VARCHAR(200)
				, @OfacReason VARCHAR(MAX)
				, @OfacReasonReceiver VARCHAR(MAX)
				, @OfacReasonFull VARCHAR(MAX)
				, @OfacErrorMessage VARCHAR(200) = ''

			SET @ReceiverFullName  = @ReceiverFirstName + ISNULL(' ' + @ReceiverMiddleName, '') + @ReceiverLastName
			SET @SenderFullName  = @SenderFirstName + ISNULL(' ' + @SenderMiddleName, '') + @SenderLastName

			EXEC proc_ofacTracker @flag = 't'
				,@name = @SenderFullName
				,@Result = @OfacReason OUTPUT

			SET @OfacReasonFull = @OfacReason
			IF @SenderFullName = @ReceiverFullName
			BEGIN
				SET @OfacReasonReceiver = @OfacReason
			END
			ELSE
			BEGIN
				EXEC proc_ofacTracker @flag = 't'
					,@name = @ReceiverFullName
					,@Result = @OfacReasonReceiver OUTPUT

				SET @OfacReasonFull = @OfacReasonFull + ISNULL(',' + @OfacReasonReceiver, '')
			END

			IF ISNULL(@OfacReason, '') <> ''
			BEGIN
				SET @OfacErrorMessage = 'OFAC matched by Sender Name'
			END
			IF ISNULL(@OfacReasonReceiver, '') <> ''
			BEGIN
				SET @OfacErrorMessage = 'OFAC matched by Receiver Name'
			END

			IF ISNULL(@OfacReason, '') <> '' AND ISNULL(@OfacReasonReceiver, '') <> ''
			BEGIN
				SET @OfacErrorMessage = 'OFAC matched by both Sender Name and Receiver Name'
			END
			
			DECLARE @SendingAgentCommission MONEY, @PayoutAgentCommission MONEY
			DECLARE @PAgentCommCurrency VARCHAR(5), @sAgentCommCurrency VARCHAR(5)
			SELECT @pAgentCommCurrency = DBO.FNAGetPayCommCurrency(@SSuperAgentId, @SAgentId, NULL, @ScountryId, @PSuperAgent, @PBranch, @PCountryId)

			SELECT @PayoutAgentCommission = amount
			FROM dbo.FNAGetPayComm(@SAgentId, @ScountryId, NULL, NULL, @PCountryId, NULL, @PAgent, @PAgentCommCurrency, @DeliveryMethodId, @CollectionAmount, @PayoutAmount, @ServiceCharge, @TransferAmount, NULL)

			DECLARE @SSettlementRate FLOAT, @PSettlementRate FLOAT
			SET @SSettlementRate = @SCurrCostRate + @SCurrHoMargin
			SET @PSettlementRate = @PCurrCostRate - @PCurrHoMargin

			SELECT @SendingAgentCommission = amount, @sAgentCommCurrency = commissionCurrency FROM dbo.FNAGetSendComm(    
			        @ScountryId, @SSuperAgentId, @SAgentId, NULL,    
			        @pCountryId, @pSuperAgent, @pAgent, NULL,    
			        @SendingCurrencyCode, @deliveryMethodId, @CollectionAmount, @PayoutAmount, @ServiceCharge, NULL, NULL,    
			        @SSettlementRate, @PSettlementRate)    
			
			DECLARE @ControlNo VARCHAR(20) = '779' + LEFT(CAST(ABS(CHECKSUM(NEWID())) AS VARCHAR(10)) + '0000000000', 9)

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

				IF EXISTS (
						SELECT 'X'
						FROM controlNoList WITH (NOLOCK)
						WHERE controlNo = @ControlNo
						)
				BEGIN
					EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Technical error occurred. Please try again!', @Id = NULL;
					RETURN;
				END
			END

			DECLARE @ControlNoEncrypted VARCHAR(20) = dbo.FNAEncryptString(@ControlNo)

			DECLARE @TransactionUniqueId VARCHAR(40) = NEWID()
			SELECT @TransactionUniqueId = REPLACE(@TransactionUniqueId, '-', '')

			INSERT INTO TBL_INBOUND_TRANSACTION_VERIFY(PartnerTransactionId, TransactionUniqueId, UserName, AgentId, SendingCountryId, PayoutCountryId, PayoutTypeId, PayoutBankCode, PayoutBankBranchCode, PayoutCurrencyCode
							, SendingCurrencyCode, CollectionAmount, TransferAmount, PayoutAmount, ServiceCharge, SendingAgentCommission, PayoutAgentCommission, ExchangeRate, RemittancePurpose, SourceOfFund, Relationship
							, Occupation, CalcBy, SenderFirstName, SenderMiddleName, SenderLastName, SenderIdType, SenderIdNo, SenderIdIssued, SenderIdValid, SenderMobile, SenderNativeCountryCode, SenderCity, SenderAddress
							, SenderEmail, SenderGender, ReceiverFirstName, ReceiverMiddleName, ReceiverLastName, ReceiverIdType, ReceiverIdNo, ReceiverIdIssued, ReceiverIdValid, ReceiverMobile, ReceiverCity, ReceiverAddress
							, ReceiverEmail, ReceiverAccountNo, CreatedDate, TransactionStatus, IsOfacHold, OfacMessage)
			SELECT @PartnerTransactionId, @TransactionUniqueId, @UserName, @AgentId, @ScountryId, @PCountryId, @DeliveryMethodId, @PayoutBankCode, @PayoutBankBranchCode, @PayoutCurrencyCode
							, @SendingCurrencyCode, @CollectionAmount, @TransferAmount, @PayoutAmount, @ServiceCharge, @SendingAgentCommission, @PayoutAgentCommission, @ExchangeRate, @RemittancePurpose, @SourceOfFund, @Relationship
							, @Occupation, @CalcBy, @SenderFirstName, @SenderMiddleName, @SenderLastName, @SenderIdType, @SenderIdNo, @SenderIdIssuedDate, @SenderIdValidDate, @SenderMobile, @SenderNativeCountryCode, @SenderCity, @SenderAddress
							, @SenderEmail, @SenderGender, @ReceiverFirstName, @ReceiverMiddleName, @ReceiverLastName, @ReceiverIdType, @ReceiverIdNo, @ReceiverIdIssuedDate, @ReceiverIdValidDate, @ReceiverMobile, @ReceiverCity, @ReceiverAddress
							, @ReceiverEmail, @ReceiverAccountNo, GETDATE(), 0, CASE WHEN ISNULL(@OfacErrorMessage, '') = '' THEN 1 ELSE 0 END, @OfacErrorMessage
		
		
			DECLARE @RelationshipName VARCHAR(150), @RemittancePurposeName VARCHAR(150), @SourceOfFundName VARCHAR(150)

			SELECT @RelationshipName = detailTitle
			FROM staticDataValue (NOLOCK)
			WHERE valueId = @Relationship

			SELECT @RemittancePurposeName = detailTitle
			FROM staticDataValue (NOLOCK)
			WHERE valueId = @RemittancePurpose

			SELECT @SourceOfFundName = detailTitle
			FROM staticDataValue (NOLOCK)
			WHERE valueId = @SourceOfFund

			INSERT INTO remitTranTemp (controlNo, sCurrCostRate, sCurrHoMargin, sCurrAgentMargin, pCurrCostRate, pCurrHoMargin, pCurrAgentMargin, agentCrossSettRate
				, customerRate, serviceCharge, sAgentComm, sAgentCommCurrency, pAgentComm, pAgentCommCurrency, sSuperAgent, sSuperAgentName, sAgent
				, sAgentName, sBranch, sBranchName, sCountry, pSuperAgent, pSuperAgentName, pAgent, pAgentName, pBranch, pBranchName
				, pCountry, paymentMethod, pBank, pBankName, pBankBranch, pBankBranchName, accountNo, collCurr, tAmt, cAmt
				, pAmt, payoutCurr, relWithSender, purposeOfRemit, sourceOfFund, tranStatus, payStatus, createdDate, createdDateLocal, createdBy
				, tranType, senderName, receiverName)

			SELECT @controlNoEncrypted, @sCurrCostRate, @sCurrHoMargin, @sCurrAgentMargin, @pCurrCostRate, @pCurrHoMargin, @pCurrAgentMargin, @agentCrossSettRate
				, @ExRateCalculated, @serviceCharge, @SendingAgentCommission, @sAgentCommCurrency, @PayoutAgentCommission, @PAgentCommCurrency ,@SSuperAgentId, @SSuperAgentName, @SAgentId
				, @SAgentName, @SBranch, @SBranchName, @SCountryName, @PSuperAgent, @PSuperAgentName, @PAgent, @PAgentName, @PBranch, @PBranchName
				, @PCountryName, @DeliveryMethodName, @PBankId, @PBankName, @PBankBranchId, @PBankBranchName, @ReceiverAccountNo, @SendingCurrencyCode, @TransferAmount, @CollectionAmount
				, @PayoutAmount, @PayoutCurrencyCode, @RelationshipName, @RemittancePurposeName, @SourceOfFundName, 'Hold', 'Unpaid', GETDATE(), GETDATE(), @UserName
				, 'B', @SenderFullName, @ReceiverFullName

			DECLARE @Id BIGINT = @@IDENTITY

			INSERT INTO controlNoList (
				controlNo
				, createdby
				)
			SELECT @ControlNo
				, @UserName

			SELECT gender,* FROM tranSenders

			DECLARE @SenderNativeCountryName VARCHAR(80), @SenderIdTypeName VARCHAR(150), @OccupationName VARCHAR(150)

			SELECT @SenderNativeCountryName = CountryName
			FROM COUNTRYMASTER (NOLOCK)
			WHERE countryCode = @SenderNativeCountryCode

			SELECT @SenderIdTypeName = detailTitle
			FROM staticDataValue (NOLOCK)
			WHERE valueId = @SenderIdType

			SELECT @OccupationName = detailTitle
			FROM staticDataValue (NOLOCK)
			WHERE valueId = @Occupation

			INSERT INTO tranSendersTemp (tranId, customerId, membershipId, firstName, middleName, lastName1, fullName, country, [address], city, email, mobile
				, nativeCountry, dob, idType, idNumber, gender, issuedDate, validDate, occupation, ipAddress)
			SELECT @Id, 0, NULL, @SenderFirstName, @SenderMiddleName, @SenderLastName, @SenderFullName, @SCountryName, @SenderAddress, @SenderCity, @SenderEmail, @SenderMobile
				, @SenderNativeCountryName, @SenderDOB, @SenderIdTypeName, @SenderIdNo, @SenderGender, @SenderIdIssuedDate, @SenderIdValidDate, @OccupationName, @IpAddress

			DECLARE @ReceiverIdTypeName VARCHAR(150)

			SELECT @ReceiverIdTypeName = detailTitle
			FROM staticDataValue (NOLOCK)
			WHERE valueId = @ReceiverIdType

			INSERT INTO tranReceiversTemp (tranId, customerId, membershipId, firstName, middleName, lastName1, fullName, country, [address], city, email, mobile
				, idType, idNumber, issuedDate, validDate, accountNo)
			SELECT @id, 0, '', @ReceiverFirstName, @ReceiverMiddleName, @ReceiverLastName, @ReceiverFullName, @PCountryName, @ReceiverAddress, @ReceiverCity, @ReceiverEmail, @ReceiverMobile
				, @ReceiverIdType, @ReceiverIdNo, @ReceiverIdIssuedDate, @ReceiverIdValidDate, @ReceiverAccountNo

				SELECT * FROM remitTranOfac ORDER BY ROWID DESC


			IF (ISNULL(@OfacReasonFull, '') <> '')
			BEGIN
				CREATE TABLE #temp (sno INT, rmrks NVARCHAR(MAX))
				
				INSERT INTO #temp (sno, rmrks)
				EXEC proc_sendPageLoadData @flag = 'ofac'
					,@user = @UserName
					,@blackListIds = @OfacReasonFull

				DECLARE @OfacResultJson NVARCHAR(MAX)

				SET @OfacResultJson = (SELECT * 
										FROM #temp
										FOR JSON AUTO)
				
				INSERT remitTranOfac (
						TranId
						,blackListId
						,reason
						,flag
						,TRACK_BY
						,ofacDetail
						)
				SELECT @id
					,@OfacReasonFull
					,@OfacErrorMessage
					,dbo.FNAGetOFAC_Flag(@OfacReasonFull)
					,'OFAC'
					,@OfacResultJson

				UPDATE remitTranTemp
				SET tranStatus = 'OFAC Hold'
				WHERE id = @Id
			END

			SELECT ErrorCode = 100 
				, ErrorMessage = 'Success'
				, IMEControlNo = @ControlNo
				, PartnerTransactionId = @PartnerTransactionId
				, TranStatus = tranStatus
				, ExRate = customerRate
				, SendingCommission = sAgentComm
				, SendingCommissionCurrency = sAgentCommCurrency
				, CollectionAmount = cAmt
				, TransferAmount = tAmt
				, PayoutAmount = pAmt
			FROM remitTranTemp (NOLOCK)
			WHERE id = @Id

			EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Success', @Id = @TransactionUniqueId, @Extra = @PartnerTransactionId, @Extra2 = @OfacErrorMessage;
		END
	END
END TRY
BEGIN CATCH
	IF @@TRANCOUNT>0    
		ROLLBACK TRANSACTION
	
	SET @ErrorMsg = 'Exception executing SP: ' + ERROR_MESSAGE()
	EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = @ErrorMsg, @Id = NULL;
END CATCH