You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
682 lines
49 KiB
682 lines
49 KiB
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;
|