DB_Script/StoredProc/proc_online_sendIntlReceipt.sql

��USE [FastMoneyPro_Remit]
GO
/****** Object:  StoredProcedure [dbo].[proc_online_sendIntlReceipt]    Script Date: 10/19/2023 6:21:32 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




ALTER PROC [dbo].[proc_online_sendIntlReceipt] (	 
	 @flag				VARCHAR(50)
	,@user				VARCHAR(200) = NULL
	,@tranId			INT			 = NULL
	,@fltAmount			VARCHAR(20)  = NULL
	,@intStatus			VARCHAR(200) = NULL
) 
AS
--------------------------------------------------------
	--#101 - Mobile Changes
	--Hide control number for unapproved Txn
--------------------------------------------------------
SET NOCOUNT ON;
IF @flag = 'receipt' --All transaction information (sender, receiver, payout)
BEGIN
	SELECT '0' ErrorCode, 'Send Transaction Success - Receipt' Msg

	SELECT 
		 tranId = ISNULL(trn.holdTranId, trn.id)

		,controlNo = CASE  WHEN trn.approvedDate IS NULL THEN ' ' ELSE  dbo.FNADecryptString(trn.controlNo) END
		
		--Sender Information
		,sMemId = sen.membershipId
		,sCustomerId = sen.customerId
		,senderName = sen.firstName + ISNULL( ' ' + sen.middleName, '') + ISNULL( ' ' + sen.lastName1, '') + ISNULL( ' ' + sen.lastName2, '')
		,sCountryName = sen.country
		,sNativeCountry=sen.nativeCountry
		,sAddress = sen.address
		,sContactNo = COALESCE(sen.mobile, sen.homephone, sen.workphone)
		,sIdType = sen.idType
		,sIdNo = sen.idNumber
		,Email = sen.email
		,sPostalCode = sen.zipcode 
		,sCity = sen.city
		,companyName = sen.companyName
		
		--Receiver Information
		,rMemId = rec.membershipId
		,idExpiry = CONVERT(VARCHAR,sen.validDate,101)
		,rCustomerId = rec.customerId
		,receiverName = rec.firstName + ISNULL( ' ' + rec.middleName, '') + ISNULL( ' ' + rec.lastName1, '') + ISNULL( ' ' + rec.lastName2, '')
		,rCountryName = rec.country
		,rAddress = rec.address
		,rContactNo = COALESCE(rec.mobile, rec.homephone, rec.workphone)
		,rIdType = rec.idType
		,rIdNo = rec.idNumber
		,relWithSender = trn.relWithSender
		,rCity	= rec.city
		,rec.firstName AS rFirstName
		,rec.middleName AS rMiddleName
		,rec.lastName1 + ISNULL( ' ' + rec.lastName2, '') AS rLastName
		,rec.email AS rEmail
		,rec.state AS rState
		
		--Sending Agent Information
		,sAgentName = CASE WHEN trn.sAgentName = trn.sBranchName THEN trn.sSuperAgentName ELSE trn.sAgentName END
		,sBranchName = trn.sBranchName
		,sAgentCountry = sa.agentCountry
		,headMsg = sa.headMessage
		,sAgentLocation = sLoc.districtName
		,sAgentAddress = sa.agentAddress
		,agentPhone1 = sa.agentPhone1
		
		--Payout Agent Information
		,pAgentCountry = trn.pCountry
		,trn.sCountry
		,pAgent = ISNULL(trn.pBankName, trn.pAgentName)
		
		,sPremium = ISNULL(ROUND((tAmt * schemePremium) / customerRate, 4),0)
		,exRatePremium = ISNULL(schemePremium,0)
		,pPremium = ISNULL((tAmt * schemePremium),0)
		,premiumDisc = 0
		,trn.collMode
		,trn.tAmt
		,trn.serviceCharge
		,handlingFee = ISNULL(trn.handlingFee, 0)
		,netServiceCharge = serviceCharge
		,totalServiceCharge = serviceCharge + ISNULL(handlingFee, 0)
		
		,perAmt = '1'
		,scAmt2 = serviceCharge - ISNULL(trn.handlingFee, 0)
		,exRate = customerRate + ISNULL(schemePremium, 0)
		,trn.cAmt
		,trn.pAmt
		,trn.paymentMethod
		,trn.accountNo
		,paymentMode = CASE trn.paymentMethod WHEN 'Cash Payment' THEN 'Cash Pay' WHEN 'Bank Deposit' THEN 'Bank Transfer' ELSE trn.paymentMethod END 
		,stm.category
		,pBankName = CASE WHEN trn.paymentMethod = 'Cash Payment' THEN '[ANY WHERE] - ' + trn.pCountry ELSE trn.pBankName END
		,pBranchName = trn.pBankBranchName
		,BankName = trn.pBankName
		,BranchName = trn.pBankBranchName
		,headMsg = sa.headMessage
		,trn.accountNo
		,trn.pCountry
		,relationship = ISNULL(trn.relWithSender, '')
		,purpose = ISNULL(trn.purposeOfRemit, '')
		,sourceOfFund = ISNULL(trn.sourceOfFund, '')
		,occupation = ISNULL(sen.occupation,'')
		,collMode = trn.collMode
		,trn.collCurr
		,paymentMethod = trn.paymentMethod
		,trn.payoutCurr
		,payStatus
		,tranStatus      = CASE  WHEN trn.tranStatus='Reject' then 'DECLINED' 
		                         WHEN trn.tranStatus='Cancel' then 'CANCELLED' 
				                 WHEN trn.tranStatus='paid' then 'PAID' 
				                 WHEN depositType =  'ONLINE' AND trn.verifiedDate IS  NULL THEN 'PENDING'	
								 WHEN depositType ='DEBIT_CARD' AND trn.verifiedDate IS NOT NULL THEN 'PROCESSING'	
								 WHEN depositType in('ONLINE','DEBIT_CARD') AND trn.verifiedDate IS NOT NULL THEN 'PROCESSING'
								 else UPPER(payStatus) end
		,payoutMsg = ISNULL(trn.pMessage, '')
		,trn.createdBy
		--,createdDate = dbo.FNADateFormatTZ(trn.createdDate,trn.createdBy)
		,createdDate = trn.createdDate
		,trn.approvedBy 
		,trn.approvedDate
		,trn.paidBy
		,trn.paidDate
		,trn.payTokenId
		,trn.createdDateLocal
		,'' [schemeAction]
		,trn.handlingFee [schemeFee]
		,[custStatus] = 'Active'
		---- add new fields
		,couponName	=''
		,discountType = ''
		,discountValue = ''
		,discountPercent = ''
		,DisplayActions =  CASE WHEN trn.tranStatus='Payment' THEN 'CANCEL'+','+'AMEND' ELSE '' END
		,rewardPoints 
	FROM vwRemitTran trn WITH(NOLOCK)
	INNER JOIN vwTranSenders sen WITH(NOLOCK) ON trn.id = sen.tranId
	INNER JOIN vwTranReceivers rec WITH(NOLOCK) ON trn.id = rec.tranId
	LEFT JOIN serviceTypeMaster stm WITH(NOLOCK) ON trn.paymentMethod = stm.typeTitle
	LEFT JOIN agentMaster sa WITH(NOLOCK) ON trn.sBranch = sa.agentId
	LEFT JOIN agentMaster pa WITH(NOLOCK) ON trn.pBranch = pa.agentId
	LEFT JOIN api_districtList sLoc WITH(NOLOCK) ON sa.agentLocation = sLoc.districtCode
	--LEFT JOIN schemeSetup SCH WITH (NOLOCK) ON SCH.rowId = trn.SchemeId
	WHERE trn.id = @tranId or trn.holdTranId = @tranId


END