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.
626 lines
34 KiB
626 lines
34 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[proc_mobile_StaticData] Script Date: 6/21/2024 10:31:15 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
|
|
/*
|
|
proc_mobile_StaticData @flag='receiver'
|
|
*/
|
|
ALTER PROCEDURE [dbo].[proc_mobile_StaticData] (
|
|
@flag VARCHAR(50) = NULL
|
|
,@customerId VARCHAR(100) = NULL
|
|
,@additionalIdType VARCHAR(100) = NULL
|
|
)
|
|
AS
|
|
--------------------------------------------------------
|
|
-- #101 - Mobile Changes
|
|
--------------------------------------------------------
|
|
BEGIN
|
|
DECLARE @nativeCountry VARCHAR(20)
|
|
|
|
----SELECT @customerId = username FROM customerMasterTemp with (nolock) WHERE username = @customerId
|
|
--SELECT @customerId=email,@nativeCountry=x.nativeCountry FROM (SELECT username AS email ,cmt.nativeCountry AS nativeCountry FROM dbo.CustomerMastertemp (NOLOCK) cmt
|
|
--UNION ALL
|
|
--SELECT email,cm.nativeCountry AS nativeCountry FROM dbo.customerMaster(NOLOCK) cm )x WHERE x.email=@customerId
|
|
--additional id card back for Insurance Card
|
|
IF @flag = 'cddi'
|
|
BEGIN
|
|
--SOURCE OF FUND
|
|
SELECT *
|
|
FROM (
|
|
SELECT valueId id
|
|
,detailTitle [text]
|
|
FROM staticDataValue(NOLOCK)
|
|
WHERE TYPEID = 3900
|
|
AND ISNULL(ISACTIVE, 'Y') = 'Y'
|
|
AND ISNULL(IS_DELETE, 'N') = 'N'
|
|
) X
|
|
ORDER BY [text]
|
|
|
|
--PURPOSE OF REMITTANCE
|
|
SELECT *
|
|
FROM (
|
|
SELECT valueId id
|
|
,detailTitle [text]
|
|
FROM staticDataValue(NOLOCK)
|
|
WHERE TYPEID = 3800
|
|
AND ISNULL(ISACTIVE, 'Y') = 'Y'
|
|
AND ISNULL(IS_DELETE, 'N') = 'N'
|
|
) X
|
|
ORDER BY [text]
|
|
END
|
|
|
|
IF @flag = 'img-path'
|
|
BEGIN
|
|
DECLARE @MEMBESHIP_ID VARCHAR(50) = NULL
|
|
,@REGISTERED_DATE VARCHAR(30)
|
|
|
|
IF EXISTS (
|
|
SELECT *
|
|
FROM dbo.customerMaster(NOLOCK)
|
|
WHERE username = @customerId
|
|
)
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT *
|
|
FROM dbo.customerMaster(NOLOCK)
|
|
WHERE username = @customerId
|
|
AND membershipId IS NULL
|
|
)
|
|
BEGIN
|
|
EXEC PROC_GENERATE_MEMBERSHIP_ID @CUSTOMERID = 0
|
|
,@USER = 'mobile'
|
|
,@loginBranchId = 0
|
|
,@MEMBESHIP_ID = @MEMBESHIP_ID OUT
|
|
|
|
UPDATE dbo.customerMaster
|
|
SET MEMBERSHIPID = @MEMBESHIP_ID
|
|
WHERE username = @customerId
|
|
END
|
|
|
|
SELECT MEMBERSHIPID
|
|
,REGISTERED_DATE = CONVERT(VARCHAR(10), CREATEDDATE, 111)
|
|
FROM dbo.customerMaster(NOLOCK)
|
|
WHERE username = @customerId
|
|
|
|
RETURN
|
|
END
|
|
|
|
IF EXISTS (
|
|
SELECT *
|
|
FROM dbo.customerMasterTemp(NOLOCK)
|
|
WHERE username = @customerId
|
|
)
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT *
|
|
FROM dbo.customerMasterTemp(NOLOCK)
|
|
WHERE username = @customerId
|
|
AND membershipId IS NULL
|
|
)
|
|
BEGIN
|
|
EXEC PROC_GENERATE_MEMBERSHIP_ID @CUSTOMERID = 0
|
|
,@USER = 'mobile'
|
|
,@loginBranchId = 0
|
|
,@MEMBESHIP_ID = @MEMBESHIP_ID OUT
|
|
|
|
UPDATE dbo.customerMasterTemp
|
|
SET MEMBERSHIPID = @MEMBESHIP_ID
|
|
WHERE username = @customerId
|
|
END
|
|
|
|
SELECT MEMBERSHIPID
|
|
,REGISTERED_DATE = CONVERT(VARCHAR(10), CREATEDDATE, 111)
|
|
FROM dbo.customerMasterTemp(NOLOCK)
|
|
WHERE username = @customerId
|
|
|
|
RETURN
|
|
END
|
|
|
|
SELECT MEMBERSHIPID = ''
|
|
,REGISTERED_DATE = ''
|
|
|
|
RETURN
|
|
END
|
|
|
|
IF @flag = 'receiver'
|
|
BEGIN
|
|
SELECT CONVERT(VARCHAR, CM.countryId) AS countryId
|
|
,CM.countryName AS country
|
|
,CM.countryCode AS Code
|
|
,IsProvienceReq = CASE
|
|
WHEN TSL.countryId IS NOT NULL
|
|
THEN 'true'
|
|
ELSE 'false'
|
|
END
|
|
FROM dbo.countryMaster(NOLOCK) AS CM
|
|
INNER JOIN (
|
|
SELECT DISTINCT COUNTRYID
|
|
FROM countryReceivingMode(NOLOCK)
|
|
) CR ON CR.COUNTRYID = cm.countryId
|
|
LEFT JOIN (
|
|
SELECT DISTINCT COUNTRYiD
|
|
FROM dbo.tblServicewiseLocation(NOLOCK)
|
|
) AS TSL ON TSL.countryId = CM.countryId
|
|
WHERE CM.isOperativeCountry = 'Y'
|
|
ORDER BY country
|
|
|
|
SELECT CAST(TSL.rowId AS VARCHAR) AS id
|
|
,TSL.location AS [text]
|
|
,CONVERT(VARCHAR, TSL.countryId) AS countryId
|
|
FROM dbo.tblServicewiseLocation(NOLOCK) AS TSL
|
|
ORDER BY [text]
|
|
|
|
----WHERE CONVERT(VARCHAR,TSL.countryId)='203' AND TSL.partnerLocationId='019'
|
|
SELECT CAST(rowId AS VARCHAR) AS id
|
|
,TSL.subLocation AS [text]
|
|
,CONVERT(VARCHAR, TSL.locationId) AS provinceId
|
|
FROM dbo.tblSubLocation(NOLOCK) AS TSL
|
|
ORDER BY [text]
|
|
|
|
SELECT CONVERT(VARCHAR, SDV.valueId) AS id
|
|
,SDV.detailTitle AS [text]
|
|
FROM dbo.staticDataValue(NOLOCK) AS SDV
|
|
WHERE SDV.typeID = '3800'
|
|
AND isActive = 'Y'
|
|
AND ISNULL(IS_DELETE, 'N') = 'N'
|
|
ORDER BY [text]
|
|
|
|
SELECT CONVERT(VARCHAR, SDV.valueId) AS id
|
|
,SDV.detailTitle AS [text]
|
|
FROM dbo.staticDataValue(NOLOCK) AS SDV
|
|
WHERE SDV.typeID = '2100'
|
|
AND isActive = 'Y'
|
|
AND ISNULL(IS_DELETE, 'N') = 'N'
|
|
ORDER BY [text]
|
|
|
|
SELECT CONVERT(VARCHAR, SDV.valueId) AS id
|
|
,SDV.detailTitle AS [text]
|
|
FROM dbo.staticDataValue(NOLOCK) AS SDV
|
|
WHERE SDV.typeID = '7006'
|
|
AND isActive = 'Y'
|
|
ORDER BY [text]
|
|
|
|
RETURN
|
|
END
|
|
IF @flag = 'Query-Address'
|
|
BEGIN
|
|
SELECT CM.countryId AS [id]
|
|
,CM.countryName AS [text]
|
|
,CM.CountryCode AS code
|
|
FROM dbo.countryMaster AS CM(NOLOCK) --WHERE isActive='Y'
|
|
ORDER BY [text] ---country
|
|
|
|
|
|
SELECT valueId AS [id]
|
|
,detailTitle AS [text]
|
|
FROM staticdatavalue WITH (NOLOCK)
|
|
WHERE typeid = 4
|
|
--AND ISNULL(ISActive, 'N') = 'Y'
|
|
AND ISNULL(IS_DELETE, 'N') = 'N'
|
|
ORDER BY [text] --Gender
|
|
END
|
|
IF @flag = 'kyc'
|
|
BEGIN
|
|
SELECT CM.countryId AS [id]
|
|
,CM.countryName AS [text]
|
|
,CM.CountryCode AS Code
|
|
FROM dbo.countryMaster AS CM(NOLOCK)
|
|
ORDER BY [text] ---country
|
|
|
|
SELECT cityName AS [id]
|
|
,cityName [text]
|
|
FROM dbo.CityMaster(NOLOCK)
|
|
ORDER BY cityName --city
|
|
|
|
SELECT valueId AS [id]
|
|
,detailTitle AS [text]
|
|
FROM staticdatavalue WITH (NOLOCK)
|
|
WHERE typeid = 2000
|
|
AND ISNULL(ISActive, 'N') = 'Y'
|
|
AND ISNULL(IS_DELETE, 'N') = 'N'
|
|
ORDER BY [text] --occuptttion
|
|
|
|
SELECT rowId AS [id]
|
|
,bankName AS [text]
|
|
FROM vwBankLists bl(NOLOCK)
|
|
ORDER BY [text] --bankName
|
|
|
|
SELECT valueId AS id
|
|
,detailTitle AS [text]
|
|
,CASE
|
|
WHEN valueId = 1302
|
|
THEN 'docIssueDate,docExpiryDate'
|
|
WHEN valueId = 8008
|
|
THEN 'docIssueDate'
|
|
WHEN valueId = 10997
|
|
THEN 'docIssueDate,docExpiryDate'
|
|
WHEN valueId = 11012
|
|
THEN 'docIssueDate,docExpiryDate'
|
|
ELSE ''
|
|
END AS [dependent]
|
|
FROM staticdatavalue(NOLOCK)
|
|
WHERE typeid = 1300
|
|
AND valueId IN (
|
|
11079
|
|
,8008
|
|
)
|
|
AND ISNULL(ISActive, 'Y') = 'Y'
|
|
AND ISNULL(IS_DELETE, 'N') = 'N' ---- idType
|
|
|
|
SELECT valueId AS id
|
|
,detailTitle AS [text]
|
|
FROM StaticDataValue(NOLOCK)
|
|
WHERE typeID = 3900
|
|
AND ISNULL(IS_DELETE, 'N') = 'N'
|
|
ORDER BY [text] --sourceOfFund
|
|
|
|
RETURN
|
|
END
|
|
|
|
IF @flag IN (
|
|
'kycV3'
|
|
,'kycv3-existing'
|
|
)
|
|
BEGIN
|
|
DECLARE @isKYCSubmited BIT = 0
|
|
,@createdDate VARCHAR(20)
|
|
,@membershipId VARCHAR(30)
|
|
|
|
|
|
|
|
--SELECT cityName AS [id],cityName [text]
|
|
--FROM dbo.CityMaster(NOLOCK) ORDER by cityName --city
|
|
--MONTHLY INCOME
|
|
SELECT *
|
|
FROM (
|
|
SELECT 1 id
|
|
,'JPY 0 - JPY170,000' [text]
|
|
|
|
UNION ALL
|
|
|
|
SELECT 2 id
|
|
,'JPY170,000 - JPY340,000' [text]
|
|
|
|
UNION ALL
|
|
|
|
SELECT 3 id
|
|
,'JPY340,000 - JPY680,000' [text]
|
|
|
|
UNION ALL
|
|
|
|
SELECT 4 id
|
|
,'JPY680,000 - JPY1,300,000' [text]
|
|
|
|
UNION ALL
|
|
|
|
SELECT 5 id
|
|
,'Above JPY1,300,000' [text]
|
|
) X
|
|
ORDER BY id
|
|
|
|
--SOURCE OF FUND
|
|
SELECT *
|
|
FROM (
|
|
SELECT valueId id
|
|
,detailTitle [text]
|
|
FROM staticDataValue(NOLOCK)
|
|
WHERE TYPEID = 3900
|
|
AND ISNULL(ISACTIVE, 'Y') = 'Y'
|
|
AND ISNULL(IS_DELETE, 'N') = 'N'
|
|
) X
|
|
ORDER BY [text]
|
|
|
|
--BUSINESS TYPE
|
|
SELECT *
|
|
INTO #BUSINESS_TYPE
|
|
FROM (
|
|
SELECT valueId id
|
|
,detailTitle [text]
|
|
FROM staticDataValue(NOLOCK)
|
|
WHERE TYPEID = 7004
|
|
AND ISNULL(ISACTIVE, 'Y') = 'Y'
|
|
AND ISNULL(IS_DELETE, 'N') = 'N'
|
|
) X
|
|
ORDER BY [text]
|
|
|
|
SELECT *
|
|
FROM #BUSINESS_TYPE
|
|
|
|
--ID TYPE
|
|
SELECT valueId AS id
|
|
,detailTitle AS [text]
|
|
,isBackRequired = CASE sv.valueId WHEN '10997' THEN 0 ELSE 1 END
|
|
INTO #ID_TYPE
|
|
FROM countryIdType CID WITH (NOLOCK)
|
|
INNER JOIN staticDataValue SV WITH (NOLOCK) ON CID.IdTypeId = SV.valueId
|
|
WHERE ISNULL(SV.ISActive, 'Y') = 'Y'
|
|
AND ISNULL(isDeleted, 'N') <> 'Y'
|
|
AND (
|
|
spFlag IS NULL
|
|
OR ISNULL(spFlag, 0) = 5200
|
|
)
|
|
--AND valueId in( '11168','11079','10997')
|
|
AND COUNTRYID = 233
|
|
|
|
SELECT *
|
|
FROM #ID_TYPE
|
|
|
|
--ADDITIONAL ID TYPE
|
|
SELECT valueId AS id
|
|
,detailTitle AS [text]
|
|
,isBackRequired = CASE valueId WHEN '11313' THEN 1 ELSE 0 END
|
|
INTO #ADDITIONAL_ID_TYPE
|
|
FROM staticdatavalue(NOLOCK)
|
|
WHERE typeid = 7009
|
|
AND valueId NOT IN (11314)
|
|
AND ISNULL(ISActive, 'Y') = 'Y'
|
|
AND ISNULL(IS_DELETE, 'N') = 'N'
|
|
|
|
SELECT *
|
|
FROM #ADDITIONAL_ID_TYPE
|
|
|
|
--OCCUPATION
|
|
SELECT valueId AS id
|
|
,detailTitle AS [text]
|
|
INTO #OCCUPATION_LIST
|
|
FROM StaticDataValue(NOLOCK)
|
|
WHERE typeID = 2000
|
|
AND ISNULL(ISACTIVE, 'Y') = 'Y'
|
|
AND ISNULL(IS_DELETE, 'N') = 'N'
|
|
ORDER BY [text]
|
|
|
|
--VISA STATUS
|
|
SELECT valueId AS id
|
|
,detailTitle AS [text]
|
|
INTO #VISA_STATUS
|
|
FROM StaticDataValue(NOLOCK)
|
|
WHERE typeID = 7005
|
|
AND ISNULL(ISACTIVE, 'Y') = 'Y'
|
|
AND ISNULL(IS_DELETE, 'N') = 'N'
|
|
ORDER BY [text]
|
|
|
|
--PURPOSE OF REGISTRATION
|
|
SELECT valueId AS id
|
|
,detailTitle AS [text]
|
|
INTO #PURPOSE_REGISTRATION
|
|
FROM StaticDataValue(NOLOCK)
|
|
WHERE typeID = 8104
|
|
AND ISNULL(ISACTIVE, 'Y') = 'Y'
|
|
AND ISNULL(IS_DELETE, 'N') = 'N'
|
|
ORDER BY [text]
|
|
|
|
|
|
SELECT CM.countryId AS [id]
|
|
,CM.countryName AS [text]
|
|
,CM.CountryCode AS Code
|
|
INTO #ID_ISSUE_COUNTRY
|
|
FROM dbo.countryMaster AS CM(NOLOCK) --WHERE isActive='Y'
|
|
|
|
ORDER BY [text] ---country
|
|
|
|
IF EXISTS (
|
|
SELECT 'x'
|
|
FROM dbo.customerMasterTemp(NOLOCK)
|
|
WHERE username = @customerId
|
|
)
|
|
BEGIN
|
|
--SELECT TOP 1 firstName,gender=CASE WHEN gender=97 THEN 'M'
|
|
-- WHEN gender=98 THEN 'F' ELSE 'O' end,
|
|
-- CONVERT(VARCHAR(10),dob,120) AS dob,customerEmail AS email,city,address FROM dbo.CustomerMasterTemp(NOLOCK) WHERE email=@customerId
|
|
--SELECT TOP 1 bankName,bankAccountNo AS bankAccount,idNumber AS passportNumber,CONVERT(VARCHAR(10),idIssueDate,120) AS passportIssueDate,
|
|
--CONVERT(VARCHAR(10),idExpiryDate,120) AS passportExpiryDate,idType AS anotherIDType,idNumber AS anotherIDNumber, branchId AS branch
|
|
--,referelCode FROM dbo.CustomerMasterTemp(NOLOCK) WHERE email=@customerId AND bankName IS NOT NUll
|
|
--SELECT TOP 1 customerId AS userId, verifyDoc1 AS passportPicture,verifyDoc2 AS anotherIDPicture
|
|
-- FROM dbo.CustomerMasterTemp(NOLOCK) WHERE email=@customerId AND (verifyDoc1 IS NOT NULL OR verifyDoc2 IS NOT NULL)
|
|
SELECT @REGISTERED_DATE = CONVERT(VARCHAR(10), CREATEDDATE, 111)
|
|
,@MEMBESHIP_ID = membershipId
|
|
FROM dbo.customerMasterTEMP(NOLOCK)
|
|
WHERE username = @customerId
|
|
|
|
SELECT TOP 1 cmt.firstName
|
|
,fullName
|
|
,gender = CASE
|
|
WHEN gender = 97
|
|
THEN 'M'
|
|
WHEN gender = 98
|
|
THEN 'F'
|
|
ELSE NULL
|
|
END
|
|
,idType = CASE
|
|
WHEN ID.ID = '11402'
|
|
THEN CMT.otherIdNumber
|
|
ELSE CAST(ID.id AS VARCHAR)
|
|
END
|
|
,occupation = CASE
|
|
WHEN OCC.id = '11383'
|
|
THEN CMT.occupationOther
|
|
--WHEN OCC.id IS NULL THEN '11405'
|
|
ELSE CAST(OCC.id AS VARCHAR)
|
|
END
|
|
,CONVERT(VARCHAR(10), dob, 120) AS dob
|
|
,email AS email
|
|
,city
|
|
,sourceOfFund
|
|
,address
|
|
,nativeCountry = cm.countryCode
|
|
,bankName
|
|
,monthlyIncome
|
|
,bankAccountNo AS bankAccount
|
|
,idNumber AS passportNumber
|
|
,CONVERT(VARCHAR(10), idIssueDate, 103) AS passportIssueDate
|
|
,CONVERT(VARCHAR(10), idExpiryDate, 103) AS passportExpiryDate
|
|
,additionalIdType = AID.id
|
|
,idIssueCountry
|
|
,mobile
|
|
,anotherIDNumber
|
|
,anotherIDIssueDate
|
|
,anotherIDExpiryDate
|
|
,additionalAddress
|
|
,branchId AS branch
|
|
,referelCode
|
|
,userName AS userId
|
|
,verifyDoc1 AS passportPicture
|
|
,verifyDoc2 AS anotherIDPicture
|
|
,nameOfEmployeer AS employeerName
|
|
,visaStatus = VISA.id
|
|
,BT.id AS businessType
|
|
,purposeOfRegistration = PUR.id
|
|
FROM dbo.customerMasterTEMP(NOLOCK) cmt
|
|
LEFT JOIN #BUSINESS_TYPE BT ON BT.id = cmt.employeeBusinessType
|
|
LEFT JOIN #ID_TYPE ID ON ID.id = cmt.idType
|
|
LEFT JOIN #OCCUPATION_LIST OCC ON OCC.id = cmt.occupation
|
|
LEFT JOIN #ADDITIONAL_ID_TYPE AID ON AID.id = CMT.documentType
|
|
LEFT JOIN #VISA_STATUS VISA ON VISA.id = cmt.visaStatus
|
|
LEFT JOIN #PURPOSE_REGISTRATION PUR ON PUR.id = cmt.purposeOfRegistration
|
|
LEFT JOIN dbo.countryMaster(NOLOCK) cm ON cm.countryId = cmt.nativeCountry
|
|
WHERE username = @customerId
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
--SELECT TOP 1 firstName,gender=CASE WHEN gender=97 THEN 'M'
|
|
-- WHEN gender=98 THEN 'F' ELSE 'O' end,
|
|
-- CONVERT(VARCHAR(10),dob,120) AS dob,customerEmail AS email,city,address FROM dbo.customerMaster(NOLOCK) WHERE email=@customerId
|
|
--SELECT TOP 1 bankName,bankAccountNo AS bankAccount,idNumber AS passportNumber,CONVERT(VARCHAR(10),idIssueDate,120) AS passportIssueDate,
|
|
--CONVERT(VARCHAR(10),idExpiryDate,120) AS passportExpiryDate,idType AS anotherIDType,idNumber AS anotherIDNumber, branchId AS branch
|
|
--,referelCode FROM dbo.customerMaster(NOLOCK) WHERE email=@customerId AND bankName IS NOT NUll
|
|
--SELECT TOP 1 customerId AS userId, verifyDoc1 AS passportPicture,verifyDoc2 AS anotherIDPicture
|
|
-- FROM dbo.customerMaster(NOLOCK) WHERE email=@customerId AND (verifyDoc1 IS NOT NULL OR verifyDoc2 IS NOT NULL)
|
|
SELECT TOP 1 cmt.firstName
|
|
,fullName
|
|
,gender = CASE
|
|
WHEN gender = 97
|
|
THEN 'M'
|
|
WHEN gender = 98
|
|
THEN 'F'
|
|
ELSE NULL
|
|
END
|
|
,idType = CASE
|
|
WHEN ID.ID = '11402'
|
|
THEN CMT.otherIdNumber
|
|
ELSE CAST(ID.id AS VARCHAR)
|
|
END
|
|
,occupation = CASE
|
|
WHEN OCC.id = '11383'
|
|
THEN CMT.occupationOther
|
|
--WHEN OCC.id IS NULL THEN '11405'
|
|
ELSE CAST(OCC.id AS VARCHAR)
|
|
END
|
|
,CONVERT(VARCHAR(10), dob, 120) AS dob
|
|
,email AS email
|
|
,city
|
|
,sourceOfFund
|
|
,address
|
|
,nativeCountry = cm.countryCode
|
|
,additionalAddress
|
|
,CASE
|
|
WHEN @flag = 'kycv3-existing' and salaryRange IS NULL
|
|
THEN ''
|
|
ELSE monthlyIncome
|
|
END AS monthlyIncome
|
|
,bankName
|
|
,bankAccountNo AS bankAccount
|
|
,idNumber AS passportNumber
|
|
,CONVERT(VARCHAR(10), idIssueDate, 103) AS passportIssueDate
|
|
,CONVERT(VARCHAR(10), idExpiryDate, 103) AS passportExpiryDate
|
|
,idIssueCountry
|
|
,additionalIdType = AID.id
|
|
,anotherIDNumber
|
|
,mobile
|
|
,anotherIDIssueDate
|
|
,anotherIDExpiryDate
|
|
,branchId AS branch
|
|
,referelCode
|
|
,customerId AS userId
|
|
,verifyDoc1 AS passportPicture
|
|
,verifyDoc2 AS anotherIDPicture
|
|
,nameOfEmployeer AS employeerName
|
|
,visaStatus = VISA.id
|
|
,employeeBusinessType AS businessType
|
|
,purposeOfRegistration = PUR.id
|
|
FROM dbo.customerMaster(NOLOCK) cmt
|
|
LEFT JOIN #BUSINESS_TYPE BT ON BT.id = cmt.employeeBusinessType
|
|
LEFT JOIN #ID_TYPE ID ON ID.id = cmt.idType
|
|
LEFT JOIN #OCCUPATION_LIST OCC ON OCC.id = cmt.occupation
|
|
LEFT JOIN #ADDITIONAL_ID_TYPE AID ON AID.id = CMT.documentType
|
|
LEFT JOIN #VISA_STATUS VISA ON VISA.id = cmt.visaStatus
|
|
LEFT JOIN #PURPOSE_REGISTRATION PUR ON PUR.id = cmt.purposeOfRegistration
|
|
LEFT JOIN dbo.countryMaster(NOLOCK) cm ON cm.countryId = cmt.nativeCountry
|
|
WHERE username = @customerId
|
|
|
|
SELECT @REGISTERED_DATE = CONVERT(VARCHAR(10), CREATEDDATE, 111)
|
|
,@MEMBESHIP_ID = membershipId
|
|
FROM dbo.customerMaster(NOLOCK)
|
|
WHERE username = @customerId
|
|
END
|
|
|
|
DECLARE @custId BIGINT = NULL
|
|
|
|
SELECT @custId = customerId
|
|
FROM customerMaster(NOLOCK)
|
|
WHERE username = @customerId
|
|
|
|
SELECT [fileName]
|
|
,fileDescription
|
|
,REGISTERED_DATE
|
|
,MEMBESHIP_ID
|
|
FROM (
|
|
SELECT [fileName]
|
|
,fileDescription
|
|
,REGISTERED_DATE = @REGISTERED_DATE
|
|
,MEMBESHIP_ID = @MEMBESHIP_ID
|
|
,ROW_NUMBER() OVER (
|
|
PARTITION BY fileDescription ORDER BY createddate DESC
|
|
) ROW_ID
|
|
FROM customerDocument(NOLOCK)
|
|
WHERE customerId = @custId
|
|
AND ISNULL(isOnlineDoc, 'N') = 'Y'
|
|
) x
|
|
WHERE ROW_ID = 1
|
|
|
|
--Occupation List
|
|
SELECT *
|
|
FROM #OCCUPATION_LIST
|
|
|
|
--Visa Status (in case of existing customer only)
|
|
SELECT *
|
|
FROM #VISA_STATUS
|
|
|
|
--Purpose of Registration
|
|
SELECT *
|
|
FROM #PURPOSE_REGISTRATION
|
|
|
|
-- ID issue country
|
|
SELECT * FROM #ID_ISSUE_COUNTRY
|
|
RETURN
|
|
END
|
|
If @flag = 'additional-Id'
|
|
BEGIN
|
|
SELECT valueId AS id
|
|
,detailTitle AS [text]
|
|
,isBackRequired = CASE
|
|
WHEN valueId IN (
|
|
11316
|
|
,11317
|
|
,11432 --Corona Vaccine Letter
|
|
)
|
|
THEN 0
|
|
ELSE 1
|
|
END
|
|
--INTO #ADDITIONAL_ID_TYPE1
|
|
FROM staticdatavalue(NOLOCK)
|
|
WHERE typeid = 7009
|
|
AND valueId = @additionalIdType
|
|
AND ISNULL(ISActive, 'Y') = 'Y'
|
|
AND ISNULL(IS_DELETE, 'N') = 'N'
|
|
|
|
--SELECT *
|
|
--FROM #ADDITIONAL_ID_TYPE1
|
|
END
|
|
END
|
|
|