DB_Script/StoredProc/proc_Customerinformation.sql

��USE [FastMoneyPro_Remit]
GO
/****** Object:  StoredProcedure [dbo].[proc_Customerinformation]    Script Date: 11/9/2023 11:37:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






-- =============================================  
-- Author:  <Bikash Regmi>  
-- Create date: <Create Date,,>  
-- Description: <Gives Customer Details>  
-- =============================================  
ALTER PROCEDURE [dbo].[proc_Customerinformation] @flag VARCHAR(50)
	,@customerId BIGINT = NULL
	,@user VARCHAR(50) = NULL
	,@membershipId VARCHAR(20) = NULL
	,@receiverId BIGINT = NULL
	,@rowId BIGINT = NULL
AS
BEGIN
-- EXEC [proc_Customerinformation] @customerId='42', @flag='details'
	------------------------------------------------------------------------------
	--JME-544 -> document view issue for receiver. Set application/pdf
	--#134 -> Allow edit option of address in Town Area
	--Replace with FNAGetCustomerAddress
	--#114 -> Introducer Commission Setup
	--#180 -> SHOW KANJI AND ROMAN TEXT IN ADDRESS STATE AND CITY
	-- #101 -> Mobile Changes @flag='details'
	--#768 Enable zoom in/out functionality for customer document in customer detail 
	--#811 Added new field "Additional ID Type" in Customer Details
	--addition of  Additional ID value
	--Bug #872  Deleted customer shown in Customer details 
	-- #949 - Deposit slip doc show in customer details , @flag = 'details'
	-- #987 -> changes in @flag = 'details' to reflect changes of edit customer in customer details screen
	-- #1056 -> show remarks in customer details
	-- #1084 -> show lawson card no in customer details 
	-- #1224 - Show Transaction Details in customer details , @flag = 'details'
	-- #11272 - @FLAG - DETAILS , ADD NEW COLUMNS IN RECEIVER LIST 
	-- #11751 - @flag = details , add registration Type 
	-- #11863  - add field for EKYC detail in customer details
	-- selected the modifylog by username/email
	------------------------------------------------------------------------------	 
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	IF @flag = 'details'
	BEGIN
		DECLARE @USERTYPE CHAR(1)
			,@fileName VARCHAR(80)
			,@cdId BIGINT

		SELECT @fileName = fileName
			,@cdId = cdId
		FROM customerdocument
		WHERE customerid = @customerId
			AND filedescription = 'customer-letter-save'
			AND fileType = 'letter'

		SELECT @USERTYPE = CASE 
				WHEN ISNULL(AU.USERTYPE, 'HO') = 'HO'
					THEN 'A'
				ELSE 'B'
				END
		FROM applicationUsers AU(NOLOCK)
		WHERE USERNAME = @user

		DECLARE @introducer VARCHAR(100)

		SELECT @introducer = intro.fullName + ' - ' + intro.membershipId
		FROM (
			SELECT cm.referelCode
				,cm.membershipId
				,customerId
			FROM customerMaster cm(NOLOCK)
			WHERE referelCode IS NOT NULL
			) x
		LEFT JOIN customerMaster intro(NOLOCK) ON intro.membershipId = x.referelCode
		WHERE x.customerId = ISNULL(@customerId, x.customerId)

		SELECT cm.fullName
			,cm2.countryName
			,stateName = csm.stateName 
			,city = cm.city 
			--,street = CASE CM.createdfrom WHEN 'M' THEN (STREET_NAME  + ISNULL(' - ' + STREET_JAPANESE, '')) ELSE streetUnicode END
			,street = cm.streetUnicode --#987
			,cm.email
			,cm.mobile
			,zipCode = ISNULL(cm.zipCode, postalCode)
			,cm.address
			,ISNULL(cm.additionalAddress,'N/A') additionalAddress
			,nativeCountry.countryName nativeCountry
			,cm.walletAccountNo
			,convert(VARCHAR(10), cm.createdDate, 121) createdDate
			,cm.createdBy
			,cm.membershipId
			,CONVERT(VARCHAR(10), cm.dob, 111) dob
			,-- cm.dob ,  
			CASE 
				WHEN cm.occupation = '11383'
					THEN cm.occupationother
				ELSE sdv.DETAILTITLE
				END [occupation]
			,sdv1.detailTitle gender
			,CASE 
				WHEN cm.idType = '11402'
					THEN cm.otherIdNumber
				ELSE sdv2.detailTitle
				END idType
			,UPPER(cm.idNumber) idNumber
			,CONVERT(VARCHAR(10), cm.idExpiryDate, 111) idExpiryDate
			,--cm.idExpiryDate ,  
			cm.placeOfIssue
			,visaStatus.detailtitle visastatus
			,sourceOfFund.detailtitle sourceOfFund
			,cm.otherIdNumber
			,sdv2.valueId [idTypeValue]
			,serviceUsedFor = CASE ISNULL(cm.serviceUsedFor, 'C')
				WHEN 'C'
					THEN 'Counter Visit Only'
				WHEN 'CM'
					THEN 'Counter and Mobile'
				WHEN 'CMO'
					THEN 'Counter, Mobile and Online'
				WHEN 'M'
					THEN 'Mobile Only'
				ELSE 'Counter Visit Only'
				END
			,serviceUsedForCode = CASE 
				WHEN MR.CUSTOMERID IS NOT NULL
					THEN 'M'
				ELSE CASE 
						WHEN ISNULL(cm.createdFrom, 'C') = 'M'
							THEN 'M'
						ELSE ISNULL(cm.serviceUsedFor, 'C')
						END
				END
			,fileName = @fileName
			,createdDate = cast(cast(cm.CREATEDDATE AS DATE) AS VARCHAR)
			,cm.customerId
			,cdId = @cdId
			,introducer = @introducer
			,cm.documentType AS additionalId
			,documentType.detailTitle documentType
			,createdFrom = CASE 
				WHEN createdFrom = 'M'
					THEN 'MOBILE'
				WHEN createdFrom = 'C'
					THEN 'COUNTER'
				WHEN createdFrom = 'O'
					THEN 'ONLINE'
				WHEN createdFrom = 'A'
					THEN 'AGENT'
				ELSE createdFrom
				END
			, ISNULL(verificationCode,'NOT_COMPLETED') lawsonCardNo
			, RegistrationType =  CASE WHEN cm.RegistrationType IS NULL THEN 'Manual' ELSE cm.RegistrationType END
			, cm.TrustDocId
		FROM dbo.customerMaster cm(NOLOCK)
		LEFT JOIN DBO.mobile_userRegistration MR(NOLOCK) ON MR.CUSTOMERID = CM.CUSTOMERID
		--LEFT JOIN DBO.tbl_japan_address_detail detail WITH (NOLOCK) ON detail.zip_code = CM.zipcode
		LEFT JOIN dbo.countryMaster cm2(NOLOCK) ON cm2.countryId = cm.country
		LEFT JOIN dbo.countryMaster nativeCountry(NOLOCK) ON nativeCountry.countryId = cm.nativeCountry
		LEFT JOIN dbo.countryStateMaster csm(NOLOCK) ON csm.stateId = cm.STATE
		LEFT JOIN dbo.staticDataValue sdv(NOLOCK) ON sdv.valueId = cm.occupation
		LEFT JOIN dbo.staticDataValue sdv1(NOLOCK) ON sdv1.valueId = cm.gender
		LEFT JOIN dbo.staticDataValue sdv2(NOLOCK) ON sdv2.valueId = cm.idType
		LEFT JOIN dbo.staticDataValue documentType(NOLOCK) ON documentType.valueId = cm.documentType
		LEFT JOIN dbo.staticDataValue visaStatus(NOLOCK) ON visaStatus.valueId = cm.visaStatus
		LEFT JOIN dbo.staticDataValue sourceofFund(NOLOCK) ON sourceofFund.valueId = cm.sourceofFund
		WHERE cm.customerId = @customerId

		SELECT ri.firstName + ' ' + COALESCE(ri.middleName + ' ', '') + COALESCE(ri.lastName1 + ' ', '') + COALESCE(ri.lastName2, '') fullName
			,ri.address
			,CASE 
				WHEN ri.relationship = '11339'
					THEN ri.relationOther
				ELSE rel.detailTitle
				END relationship
			,ri.country
			,CASE isdeleted WHEN 1 THEN 'YES' ELSE 'NO' END isdeleted
			,ri.DeletedBy
		FROM dbo.receiverInformation ri(NOLOCK)
		LEFT JOIN staticDataValue rel ON rel.valueId = ri.relationship
		WHERE ri.customerId = @customerId
			--AND isnull(ri.isdeleted, '0') <> '1'
		ORDER BY ri.createdDate DESC;

		SELECT *
		FROM (
			SELECT cd.createdDate
				,CASE 
					WHEN cd.documentType = '0'
						AND filetype = 'application/pdf'
						THEN cd.filedescription
					ELSE ISNULL(sdv.detailTitle, 'signature')
					END documentType
				,fileType = CASE 
					WHEN ISNULL(cd.fileType, 'image/jpeg') IN ('receiver')
						THEN 'application/pdf'
					ELSE ISNULL(cd.fileType, 'image/jpeg')
					END
				,convert(VARCHAR(10), cd.createddate, 111) uploadedDate
				--,'<a onclick="showDocument(' + Cast(cd.cdId AS VARCHAR) + ',''' + CASE 
				--	WHEN ISNULL(cd.fileType, 'image/jpeg') IN ('receiver')
				--		THEN 'application/pdf'
				--	ELSE ISNULL(cd.fileType, 'image/jpeg')
				--	END + ''');">' + cd.fileName + '</a>' fileName
				,'<a onclick="showDocument(' + Cast(cd.cdId AS VARCHAR) + ',''' + Cast(cm.customerId AS VARCHAR) + ''',''' + Cast(cm.membershipId AS VARCHAR) + ''',''' + (cd.fileName) + ''',''' + convert(VARCHAR(10), cm.createdDate, 111) + ''',''' + CASE 
					WHEN ISNULL(cd.fileType, 'image/jpeg') IN ('receiver')
						THEN 'application/pdf'
					ELSE ISNULL(cd.fileType, 'image/jpeg')
					END + ''');">' + cd.fileName + '</a>' fileName
			FROM dbo.customerDocument cd
			LEFT JOIN dbo.customerMaster cm(NOLOCK) ON cm.customerId = cd.customerId
			LEFT JOIN dbo.staticDataValue sdv ON sdv.valueId = cd.documentType
			WHERE cm.customerId = @customerId
				AND ISNULL(cd.isDeleted, 'N') = 'N'
			-- ORDER BY cd.createdDate;
			
			UNION ALL
			
			SELECT ds.createdDate
				,'Deposit Slip' documentType
				,fileType = ds.fileDescription
				,convert(VARCHAR(10), ds.createddate, 111) uploadedDate
				,'<a onclick="showDocument(' + Cast(ds.cdId AS VARCHAR) + ',''' + Cast(cm.customerId AS VARCHAR) + ''',''' + Cast(cm.membershipId AS VARCHAR) + ''',''' + (ds.fileName) + ''',''' + convert(VARCHAR(10), cm.createdDate, 111) + ''',''' + ds.filedescription + ''',' + '''Deposit Slip''' + ');">' + ds.fileName + '</a>' fileName
			FROM dbo.depositslip ds(NOLOCK)
			LEFT JOIN dbo.customerMaster cm(NOLOCK) ON cm.customerId = ds.customerId
			LEFT JOIN dbo.staticDataValue sdv ON sdv.valueId = ds.fileType
			WHERE cm.customerId = @customerId
				AND ds.[status] = 1
				AND ds.approvedDate IS NOT NULL
			) X
		ORDER BY createdDate;

		SELECT * FROM (
			SELECT sdv.detailTitle method
				,sdv1.detailTitle STATUS
				,tck.remarks, tck.createdDate
			FROM dbo.TBL_CUSTOMER_KYC tck
			INNER JOIN staticDataValue sdv ON tck.kycMethod = sdv.valueId
			INNER JOIN staticDataValue sdv1 ON tck.kycStatus = sdv1.valueId
			WHERE tck.customerId = @customerId
				AND isDeleted = 0

			 UNION ALL 

			 SELECT '' AS METHOD , 'KYC PROCESSING' AS STATUS , newValue as REMARKS , modifiedDate as createdDate
			 FROM TBLCUSTOMERMODIFYLOGS(NOLOCK) 
			 WHERE columnName = 'remarks' AND customerId = @customerId
			)x ORDER BY createdDate desc

		SELECT convert(VARCHAR(10), vrt.createddate, 121) createddate
			,vrt.receiverName
			--,jmeNo = CASE 
			--	WHEN @USERTYPE = 'A'
			--		THEN '<a onclick="OpenInNewWindow(''/Remit/Transaction/ReprintVoucher/SendIntlReceipt.aspx?searchBy=controlNo&controlNo=' + dbo.FNADecryptString(vrt.controlNo) + ''');">' + dbo.FNADecryptString(vrt.controlNo) + '</a>'
						--ELSE '<span class="link" onclick="OpenInNewWindow(''/AgentNew/SearchTxnReport/ViewTxnDetail.aspx?controlNo='+dbo.FNADecryptString(vrt.controlNo)+'|'+tranStatus+''');">'+dbo.FNADecryptString(vrt.controlNo)+'</span>'    
			--	ELSE '<span class="link" onclick="OpenInNewWindow(''/AgentNew/ReprintReceipt/SendTntlReceipt.aspx?controlNo=' + dbo.FNADecryptString(vrt.controlNo) + ''')">' + dbo.FNADecryptString(vrt.controlNo) + '</span>'
			, jmeNo = CASE WHEN @USERTYPE = 'A'
							THEN '<a href="javascript:void(0)" onclick="OpenInNewWindow(''/Remit/Transaction/Reports/SearchTransaction.aspx?commentFlag=Y&showBankDetail=N&showApproveButton=N&controlNo=' + dbo.FNADecryptString(vrt.controlNo) + ''');">' + dbo.FNADecryptString(vrt.controlNo) + '</a>'
						ELSE '<a href="javascript:void(0)" onclick="OpenInNewWindow(''/AgentNew/Modify/ModifyTran.aspx?commentFlag=Y&showBankDetail=N&showApproveButton=N&controlNo=' + dbo.FNADecryptString(vrt.controlNo) + '&clickFunction=Y'');">' + dbo.FNADecryptString(vrt.controlNo) + '</a>'
				END
			,vrt.cAmt cAmt
			,vrt.serviceCharge
			,vrt.tAmt
			,vrt.tranStatus
			,vrt.payStatus
			,vrt.pCountry
			,CASE 
				WHEN COMP.TRANID IS NULL
					THEN '-'
				ELSE 'Compliance Hold'
				END complianceStatus
		FROM dbo.vwRemitTran vrt
		INNER JOIN dbo.vwTranSenders vts ON vrt.id = vts.tranId
		LEFT JOIN (
			SELECT DISTINCT TRANID
			FROM REMITTRANCOMPLIANCE(NOLOCK)
			) COMP ON COMP.TRANID = VRT.HOLDTRANID
		WHERE vts.customerId = @customerId
		ORDER BY vrt.createdDate DESC;

		--SELECT TOP 10  columnName ,  
		--  oldValue ,  
		--  newValue ,  
		--  modifiedBy ,  
		--  CONVERT(VARCHAR(10), modifiedDate, 111)modifiedDate--modifiedDate  
		--FROM    TBLCUSTOMERMODIFYLOGS logs  
		--WHERE logs.customerId = @customerId   
		--ORDER BY logs.modifiedDate DESC;  
		DECLARE @custEmail VARCHAR(200)
		SELECT @custEmail = email FROM customerMaster WHERE customerId = @customerId
		SELECT TOP 10 CML.columnName
			,COALESCE(sdv.detailDesc, CSM.stateName, CML.oldValue) oldValue
			,COALESCE(SDV1.detailDesc, CSM1.stateName, CML.newValue) AS newValue
			,CML.modifiedBy
			,CONVERT(VARCHAR(10), CML.modifiedDate, 111) modifiedDate --modifiedDate  
		FROM TBLCUSTOMERMODIFYLOGS CML(NOLOCK)
		LEFT JOIN staticDataValue SDV(NOLOCK) ON cast(SDV.valueId AS NVARCHAR) = CML.oldValue
		LEFT JOIN staticDataValue SDV1(NOLOCK) ON CAST(SDV1.valueId AS NVARCHAR) = CML.newValue
		LEFT JOIN countryStateMaster CSM(NOLOCK) ON CAST(CSM.stateId AS NVARCHAR) = CML.oldValue
		LEFT JOIN countryStateMaster CSM1(NOLOCK) ON CAST(CSM1.stateId AS NVARCHAR) = CML.newValue
		WHERE --CML.customerId = @customerId
			CML.modifiedBy = @custEmail
		ORDER BY CML.modifiedDate DESC;

		DECLARE @firstTxnDate VARCHAR(20)
			,@totalNoOfReceiver INT
			,@totalAmountOfTxn MONEY
			,@totalNoOfTxn INT
			,@totalAmountOfTxnOfAYear MONEY
			,@totalNoOfTxnOfAYear INT

		SELECT TOP 1 @firstTxnDate = convert(VARCHAR(10), rt.createddate, 121)
		FROM vwremittran rt(NOLOCK)
		INNER JOIN transenders ts(NOLOCK) ON ts.tranId = rt.id
		WHERE customerid = @customerId
			AND rt.TRANSTATUS <> 'Cancel'
		ORDER BY createddate

		SELECT @totalAmountOfTxn = sum(tamt)
			,@totalNoOfTxn = count(*)
		FROM vwremittran rt(NOLOCK)
		INNER JOIN transenders ts(NOLOCK) ON ts.tranId = rt.id
		WHERE customerid = @customerId
			AND rt.TRANSTATUS <> 'Cancel'

		SELECT @totalAmountOfTxnOfAYear = sum(tamt)
			,@totalNoOfTxnOfAYear = count(*)
		FROM vwremittran rt(NOLOCK)
		INNER JOIN transenders ts(NOLOCK) ON ts.tranId = rt.id
		WHERE customerid = @customerId
			AND rt.TRANSTATUS <> 'Cancel'
			AND createdDate BETWEEN GETDATE() - 365
				AND GETDATE()

		SELECT @totalNoOfReceiver = count(*)
		FROM receiverinformation
		WHERE customerId = @customerId
			AND isnull(isdeleted, 'N') <> 'Y'

		SELECT convert(VARCHAR(10), cm.createdDate, 121) registeredDate
			,visa.detailtitle visaStatus
			,@firstTxnDate firstTransactionDate
			,@totalNoOfReceiver numberOfReceivers
			,@totalAmountOfTxn totalAmountOfTransaction
			,@totalNoOfTxn totalNumberOfTransacation
			,@totalAmountOfTxnOfAYear totalAmountOfTransactionOfYear
			,@totalNoOfTxnOfAYear totalNumberOfTransactionOfYear
			,@totalAmountOfTxnOfAYear / @totalNoOfTxnOfAYear averageAmountOfYear
		FROM customermaster cm(NOLOCK)
		LEFT JOIN STATICDATAVALUE visa(NOLOCK) ON visa.valueId = cm.visaStatus
		WHERE customerid = @customerId

		DECLARE @KYC_METHOD INT

		SELECT @KYC_METHOD = kycMethod
		FROM TBL_CUSTOMER_KYC
		WHERE customerId = @customerId
			AND KYCSTATUS = 11047

		SELECT KYC_DETAIL = '<b>Method:</b> ' + METHOD.DETAILTITLE + ' <b>Status:</b> ' + STAT.DETAILTITLE + ' [<b>DTD:</b> ' + CAST(KYC.KYC_DATE AS VARCHAR) + ']'
		FROM TBL_CUSTOMER_KYC KYC(NOLOCK)
		INNER JOIN STATICDATAVALUE METHOD(NOLOCK) ON KYC.KYCMETHOD = METHOD.VALUEID
		INNER JOIN STATICDATAVALUE STAT(NOLOCK) ON KYC.KYCSTATUS = STAT.VALUEID
		WHERE customerId = @customerId
			AND KYCMETHOD = @KYC_METHOD
		ORDER BY rowId
		SELECT CASE 
				WHEN cm.createdFrom = 'M'
					THEN 'MOBILE'
				WHEN cm.createdFrom = 'C'
					THEN 'COUNTER'
				WHEN cm.createdFrom = 'O'
					THEN 'ONLINE'
				WHEN cm.createdFrom = 'A'
					THEN 'AGENT'
				ELSE cm.createdFrom
				END AS createdFrom
			,CASE 
				WHEN cm.createdFrom = 'M'
					THEN cm.mobileverifieddate
				WHEN cm.createdFrom = 'C'
					THEN cm.verifiedDate
				WHEN cm.createdFrom = 'O'
					THEN cm.verifiedDate
				WHEN cm.createdFrom = 'A'
					THEN cm.verifiedDate
				ELSE cm.verifiedDate
				END AS verifiedDate
			,CASE 
				WHEN cm.createdFrom = 'M'
					THEN cm.mobileverifiedby
				WHEN cm.createdFrom = 'C'
					THEN cm.verifiedBy
				WHEN cm.createdFrom = 'O'
					THEN cm.verifiedBy
				WHEN cm.createdFrom = 'A'
					THEN cm.verifiedBy
				ELSE cm.verifiedBy
				END AS verifiedBy
			,CASE 
				WHEN cm.createdFrom = 'M'
					THEN cm.mobileApprovedDate
				WHEN cm.createdFrom = 'C'
					THEN cm.approvedDate
				WHEN cm.createdFrom = 'O'
					THEN cm.approvedDate
				WHEN cm.createdFrom = 'A'
					THEN cm.approvedDate
				ELSE cm.approvedDate
				END AS approvedDate
			,CASE 
				WHEN cm.createdFrom = 'M'
					THEN cm.mobileApprovedBy
				WHEN cm.createdFrom = 'C'
					THEN cm.approvedBy
				WHEN cm.createdFrom = 'O'
					THEN cm.approvedBy
				WHEN cm.createdFrom = 'A'
					THEN cm.approvedBy
				ELSE cm.approvedBy
				END AS approvedBy
			,cm.lastLoginTs
			,mr.phoneBrand
		FROM dbo.customerMaster cm(NOLOCK)
		LEFT JOIN DBO.mobile_userRegistration MR(NOLOCK) ON MR.CUSTOMERID = CM.CUSTOMERID
		WHERE cm.customerId = @customerId
	END;

	IF @flag = 'detals-fromMembershipId'
	BEGIN
		SELECT @customerId = CUSTOMERID
		FROM CUSTOMERMASTER
		WHERE MEMBERSHIPID = @membershipId

		SELECT *
		FROM (
			SELECT fileName
				,fileType
				,documentType = detailTitle
				,ROW_NUMBER() OVER (
					PARTITION BY SV.detailTitle ORDER BY CD.createdDate DESC
					) rn
			FROM customerDocument CD(NOLOCK)
			INNER JOIN STATICDATAVALUE SV(NOLOCK) ON SV.valueId = CD.documentType
			WHERE ISNULL(isDeleted, 'N') = 'N'
				AND customerId = @customerId
				AND valueId IN (
					11054
					,11055
					,11056
					,11057
					)
			) X
		WHERE rn = 1

		SELECT cm.customerId
			,cm.createdDate
			,customerType = TYP.detailTitle
			,cm.fullName
			,CM.membershipId
			,cmb.countryName AS [country]
			,cm.zipcode
			,email
			,sdg.detailTitle AS [gender]
			,cmn.countryName AS [nativeCountry]
			,[address] = dbo.FNAGetCustomerAddress(cm.customerId, '')
			,cm.city
			,COALESCE(cm.telNo, cm.homePhone) telNo
			,cm.mobile
			,CASE 
				WHEN cm.occupation = '11383'
					THEN cm.occupationother
				ELSE sdo.DETAILTITLE
				END [occupation]
			,cm.occupationOther
			,CASE 
				WHEN cm.idType = '11402'
					THEN cm.otherIdNumber
				ELSE sdi.detailTitle
				END AS [idType]
			,cm.idType AS [idTypeCode]
			,UPPER(cm.idNumber) idNumber
			,CONVERT(VARCHAR(10), dob, 121) AS [dob]
			,CONVERT(VARCHAR(10), idIssueDate, 121) AS [idIssueDate]
			,CONVERT(VARCHAR(10), idExpiryDate, 121) AS [idExpiryDate]
			,sdv.detailTitle visaStatus
			,sdv1.detailDesc employeeBusinessType
			,cm.nameOfEmployeer
			,cm.SSNNO
			,sdv2.detailDesc sourceOfFund
			,cm.monthlyIncome
			,CASE cm.remittanceAllowed
				WHEN 1
					THEN 'Yes'
				ELSE 'No'
				END remittanceAllowed
			,CASE cm.onlineUser
				WHEN 'Y'
					THEN 'Yes'
				ELSE 'No'
				END onlineUser
			,CASE cm.mobileUser
				WHEN 'Y'
					THEN 'Yes'
				ELSE 'No'
				END mobileUser
			,cm.remarks
			,cm.occupationOther
			,sdi.valueId [idTypeValue]
			,cm.otherIdNumber
		FROM customerMaster cm(NOLOCK)
		LEFT JOIN staticDataValue TYP(NOLOCK) ON TYP.valueId = cm.customerType
		LEFT JOIN staticDataValue sdg(NOLOCK) ON sdg.valueId = cm.gender
		LEFT JOIN dbo.countryMaster cmb(NOLOCK) ON cmb.countryId = cm.country
		LEFT JOIN dbo.countryMaster cmn(NOLOCK) ON cmn.countryId = cm.nativeCountry
		LEFT JOIN staticDataValue sdo(NOLOCK) ON sdo.valueId = cm.occupation
		LEFT JOIN staticDataValue sdi(NOLOCK) ON sdi.valueId = cm.idType
		LEFT JOIN countryStateMaster CSM(NOLOCK) ON CSM.stateId = CAST(cm.STATE AS VARCHAR)
		LEFT JOIN dbo.staticDataValue sdv(NOLOCK) ON sdv.valueId = cm.visaStatus
		LEFT JOIN dbo.staticDataValue sdv1(NOLOCK) ON sdv1.valueId = cm.employeeBusinessType
		LEFT JOIN dbo.staticDataValue sdv2(NOLOCK) ON sdv2.valueId = cm.sourceOfFund
		WHERE customerId = @customerId;

		SELECT ri.receiverId
			,ri.fullName customerName
			,ri.customerId
			,ri.firstName
			,COM.membershipId
			,ri.middleName
			,ri.lastName1
			,ri.lastName2
			,ri.country
			,ri.address
			,ri.STATE
			,ri.zipCode
			,cm.countryId
			,cm1.countryname [NativeCountry]
			,ri.city
			,ri.email
			,ri.homePhone
			,ri.workPhone
			,ri.mobile
			,SDV.detailTitle relationship
			,ri.otherRelationDesc
			,SDV1.detailTitle purposeOfRemit
			,SDV2.detailTitle receiverType
			,SDV3.detailTitle idType
			,ri.idNumber
			,ri.placeOfIssue
			,CASE 
				WHEN ri.paymentmode = '1'
					THEN 'Cash Payment'
				ELSE 'Bank Deposit'
				END paymentMode
			,ISNULL(ABBL.BRANCH_NAME, '-') bankBranchName
			,ISNULL(CASE 
					WHEN ri.country = 'NEPAL'
						THEN ISNULL(ABL.BANK_NAME, 'ANY WHERE')
					ELSE ISNULL(ABL.BANK_NAME, '-')
					END, '-') payOutPartner
			,ISNULL(ri.bankName, '-') bankName
			,ISNULL(ri.receiverAccountNo, '-') receiverAccountNo
			,ISNULL(ri.remarks, '-') remarks
			,COM.createdDate
			,ri.purposeOther [otherPurpose]
		FROM receiverInformation ri WITH (NOLOCK)
		LEFT JOIN countryMaster cm WITH (NOLOCK) ON ri.country = cm.countryName
		LEFT JOIN dbo.customerMaster COM(NOLOCK) ON com.customerId = ri.customerId
		LEFT JOIN countrymaster CM1(NOLOCK) ON CM1.COUNTRYID = ri.nativecountry
		LEFT JOIN STATICDATAVALUE SDV(NOLOCK) ON SDV.VALUEID = RI.RELATIONSHIP
		LEFT JOIN STATICDATAVALUE SDV1(NOLOCK) ON SDV1.VALUEID = RI.PURPOSEOFREMIT
		LEFT JOIN STATICDATAVALUE SDV2(NOLOCK) ON SDV2.VALUEID = RI.RECEIVERTYPE
		LEFT JOIN STATICDATAVALUE SDV3(NOLOCK) ON SDV3.VALUEID = RI.idtype
		LEFT JOIN API_BANK_BRANCH_LIST ABBL(NOLOCK) ON ABBL.BRANCH_ID = RI.BANKLOCATION
		LEFT JOIN API_BANK_LIST ABL(NOLOCK) ON ABL.BANK_ID = RI.PAYOUTPARTNER
		LEFT JOIN dbo.customerMaster(NOLOCK) cmm ON cmm.customerId = ri.customerId
		WHERE ri.customerid = @customerId;

		SELECT cmm.FullName custFullname
			,upper(smq.idnumber) idNumber
			,receiverFullName
			,receiverAddress
			,receiverMobileNumber
			,accountNumber
			,cAmt
			,tAmt
			,serviceCharge
			,purpose.detailTitle purposeOfRemit
			,relation.detailTitle relationship
			,cm.countryName pcountry
			,CASE 
				WHEN smq.pModeId = '1'
					THEN 'Cash Payment'
				ELSE 'Bank Deposit'
				END pMode
			,ABL.BANK_NAME
			,BRANCH_NAME = ISNULL(ABBL.BRANCH_NAME, BranchManual)
			,smq.otherPurpose
			,smq.otherRelation
		FROM send_money_request smq(NOLOCK)
		INNER JOIN customermaster cmm(NOLOCK) ON cmm.idnumber = smq.idNumber
		LEFT JOIN STATICDATAVALUE purpose(NOLOCK) ON purpose.valueId = smq.purposeOfRemittance
		LEFT JOIN STATICDATAVALUE relation(NOLOCK) ON relation.valueId = smq.relationShip
		LEFT JOIN COUNTRYMASTER CM(NOLOCK) ON CM.COUNTRYID = SMQ.pCountryId
		LEFT JOIN API_BANK_LIST ABL(NOLOCK) ON ABL.BANK_ID = SMQ.pagentId
		LEFT JOIN API_BANK_BRANCH_LIST ABBL(NOLOCK) ON ABBL.BRANCH_ID = SMQ.branchId
		WHERE rowId = @rowId
	END;

	IF @flag = 'detals-customerId'
	BEGIN
		SELECT cm.customerId
			,cm.createdDate
			,customerType = TYP.detailTitle
			,cm.fullName
			,CM.membershipId
			,cmb.countryName AS [country]
			,cm.zipcode
			,email
			,sdg.detailTitle AS [gender]
			,cmn.countryName AS [nativeCountry]
			,ISNULL(CSM.stateName, '') + ', ' + ISNULL(cm.city, '') + ', ' + ISNULL(cm.street, '') + ', ' + ISNULL(cm.additionalAddress, '') [address]
			,cm.city
			,COALESCE(cm.telNo, cm.homePhone) telNo
			,cm.mobile
			,CASE 
				WHEN cm.occupation = '11383'
					THEN cm.occupationother
				ELSE sdo.DETAILTITLE
				END [occupation]
			,cm.occupationOther
			,sdi.detailTitle AS [idType]
			,cm.idType AS [idTypeCode]
			,UPPER(cm.idNumber) idNumber
			,CONVERT(VARCHAR(10), dob, 121) AS [dob]
			,CONVERT(VARCHAR(10), idIssueDate, 121) AS [idIssueDate]
			,CONVERT(VARCHAR(10), idExpiryDate, 121) AS [idExpiryDate]
			,sdv.detailDesc visaStatus
			,sdv1.detailDesc employeeBusinessType
			,cm.nameOfEmployeer
			,cm.SSNNO
			,sdv2.detailDesc sourceOfFund
			,cm.monthlyIncome
			,CASE cm.remittanceAllowed
				WHEN 1
					THEN 'Yes'
				ELSE 'No'
				END remittanceAllowed
			,CASE cm.onlineUser
				WHEN 'Y'
					THEN 'Yes'
				ELSE 'No'
				END onlineUser
			,cm.remarks
			,CASE cm.mobileUser
				WHEN 'Y'
					THEN 'Yes'
				ELSE 'No'
				END mobileUser
		FROM customerMaster cm(NOLOCK)
		LEFT JOIN staticDataValue TYP(NOLOCK) ON TYP.valueId = cm.customerType
		LEFT JOIN staticDataValue sdg(NOLOCK) ON sdg.valueId = cm.gender
		LEFT JOIN dbo.countryMaster cmb(NOLOCK) ON cmb.countryId = cm.country
		LEFT JOIN dbo.countryMaster cmn(NOLOCK) ON cmn.countryId = cm.nativeCountry
		LEFT JOIN staticDataValue sdo(NOLOCK) ON sdo.valueId = cm.occupation
		LEFT JOIN staticDataValue sdi(NOLOCK) ON sdi.valueId = cm.idType
		LEFT JOIN countryStateMaster CSM(NOLOCK) ON CSM.stateId = CAST(cm.STATE AS VARCHAR)
		LEFT JOIN dbo.staticDataValue sdv(NOLOCK) ON sdv.valueId = cm.visaStatus
		LEFT JOIN dbo.staticDataValue sdv1(NOLOCK) ON sdv1.valueId = cm.employeeBusinessType
		LEFT JOIN dbo.staticDataValue sdv2(NOLOCK) ON sdv2.valueId = cm.sourceOfFund
		WHERE customerId = @customerId;
	END;
END;