DB_Script/Back/proc_online_core_customerSetup.sql

USE [FastMoneyPro_Remit]
GO
/****** Object:  StoredProcedure [dbo].[proc_online_core_customerSetup]    Script Date: 6/5/2024 6:28:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





ALTER PROC [dbo].[proc_online_core_customerSetup] 
         @flag VARCHAR(50) = NULL
	,@user VARCHAR(30) = NULL
	,@customerId VARCHAR(30) = NULL
	,@fullName NVARCHAR(200) = NULL
	,@passportNo VARCHAR(30) = NULL
	,@mobile VARCHAR(15) = NULL
	,@firstName VARCHAR(100) = NULL
	,@middleName VARCHAR(100) = NULL
	,@lastName1 VARCHAR(100) = NULL
	,@lastName2 VARCHAR(100) = NULL
	,@customerIdType VARCHAR(30) = NULL
	,@customerIdNo VARCHAR(50) = NULL
	,@custIdissueDate VARCHAR(30) = NULL
	,@custIdValidDate VARCHAR(30) = NULL
	,@custDOB VARCHAR(30) = NULL
	,@custTelNo VARCHAR(30) = NULL
	,@custMobile VARCHAR(30) = NULL
	,@custCity VARCHAR(100) = NULL
	,@custPostal VARCHAR(30) = NULL
	,@companyName VARCHAR(100) = NULL
	,@custAdd1 VARCHAR(100) = NULL
	,@custAdd2 VARCHAR(100) = NULL
	,@country VARCHAR(30) = NULL
	,@custNativecountry VARCHAR(30) = NULL
	,@custEmail VARCHAR(50) = NULL
	,@custGender VARCHAR(30) = NULL
	,@custSalary VARCHAR(30) = NULL
	,@memberId VARCHAR(30) = NULL
	,@occupation VARCHAR(30) = NULL
	,@state VARCHAR(30) = NULL
	,@zipCode VARCHAR(30) = NULL
	,@district VARCHAR(30) = NULL
	,@homePhone VARCHAR(30) = NULL
	,@workPhone VARCHAR(30) = NULL
	,@placeOfIssue VARCHAR(30) = NULL
	,@customerType VARCHAR(30) = NULL
	,@isBlackListed VARCHAR(30) = NULL
	,@relativeName VARCHAR(30) = NULL
	,@relationId VARCHAR(30) = NULL
	,@lastTranId VARCHAR(30) = NULL
	,@receiverName VARCHAR(100) = NULL
	,@tranId VARCHAR(20) = NULL
	,@ICN VARCHAR(50) = NULL
	,@bank VARCHAR(100) = NULL
	,@bankId VARCHAR(100) = NULL
	,@accountNumber VARCHAR(100) = NULL
	,@mapCodeInt VARCHAR(10) = NULL
	,@sortBy VARCHAR(50) = NULL
	,@sortOrder VARCHAR(5) = NULL
	,@pageSize INT = NULL
	,@pageNumber INT = NULL
	,@HasDeclare INT = NULL
	,@agent VARCHAR(50) = NULL
	,@branch VARCHAR(50) = NULL
	,@branchId VARCHAR(50) = NULL
	,@onlineUser VARCHAR(50) = NULL
	,@ipAddress VARCHAR(30) = NULL
	,@howDidYouHear VARCHAR(200) = NULL
	,@ansText VARCHAR(200) = NULL
	,@isActive CHAR(1) = NULL
	,@email VARCHAR(150) = NULL
	,@searchCriteria VARCHAR(30) = NULL
	,@searchValue VARCHAR(50) = NULL
	,@newPassword VARCHAR(20) = NULL
	,@createdDate DATETIME = NULL
	,@createdBy VARCHAR(50) = NULL
	,@verifyDoc1 VARCHAR(255) = NULL
	,@verifyDoc2 VARCHAR(255) = NULL
	,@verifyDoc3 VARCHAR(255) = NULL
	,@verifyDoc4 VARCHAR(255) = NULL
	,@membershipId VARCHAR(50) = NULL
	,@sourceOfFound VARCHAR(100) = NULL
	,@street VARCHAR(80) = NULL
	,@streetUnicode NVARCHAR(100) = NULL
	,@cityUnicode NVARCHAR(100) = NULL
	,@visaStatus INT = NULL
	,@employeeBusinessType INT = NULL
	,@nameOfEmployeer VARCHAR(80) = NULL
	,@SSNNO VARCHAR(20) = NULL
	,@remittanceAllowed BIT = NULL
	,@remarks VARCHAR(1000) = NULL
	,@registerationNo VARCHAR(30) = NULL
	,@organizationType INT = NULL
	,@dateofIncorporation DATETIME = NULL
	,@natureOfCompany INT = NULL
	,@position INT = NULL
	,@nameOfAuthorizedPerson VARCHAR(80) = NULL
	,@fromDate NVARCHAR(20) = NULL
	,@toDate NVARCHAR(20) = NULL
	,@monthlyIncome VARCHAR(50) = NULL
	,@isCounterVisited CHAR(1) = NULL
	,@additionalAddress VARCHAR(50) = NULL
	,@loginBranchId BIGINT = NULL
	,@rowid BIGINT = NULL
	,@docType INT = NULL
	,@occupationOther VARCHAR(100) = NULL
	,@changedSecurityInfo VARCHAR(50) = NULL
	,@referralId VARCHAR(10) = NULL
	,@customerOtherIdNo VARCHAR(50) = NULL
	,@serviceUsedFor VARCHAR(10) = NULL
	,@password VARCHAR(20) = NULL
	,@mobileUser VARCHAR(5) = NULL
	,@roleId VARCHAR(50) = NULL
	,@ofacRes VARCHAR(MAX) = NULL
	,@ofacReason VARCHAR(200) = NULL
	,@ofacRemarks VARCHAR(200) = NULL
	,@deleteReason VARCHAR(200) = NULL
	,@registrationType VARCHAR(10) = NULL 
	,@source varchar(10) = NULL
	,@lawsoncardno VARCHAR(10)= NUll
	,@createdFrom CHAR(1)= 'C'
	,@referralCode VARCHAR(100)  = NULL  
	,@kycStatus VARCHAR(100) = NULL
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;

----------------------------------------------------
--#134 -> Allow edit option of address in Town Area
--Replace with FNAGetCustomerAddress
--#180 -> SHOW KANJI AND ROMAN TEXT IN ADDRESS STATE AND CITY
--#178 -> Show only Roman in Remittance Statement
--#101 -> mobile changes
--#712 -> @flag = customerdetailForEnableDisable
-- #698 -> flag = 'customer-details-activate'
-- #740 -> added @flag = 'clearKYC'
-- #733 -> Possible Duplicate Match summary
--format date
-- #947 -> Added back button for re-verification in approve customer page
-- #989 -> Added new @flag = 'deleteCustomer' for deleting customer from verify mobile customer page
-- #950 -> Add additional information on edit customer page (changes in @flag = 'customer-details')
-- #1077 -> Updated ac_master on customer modification for wallet name change
-- #1094 -> remarks for verify pending 
-- #1104 - show dj list in approve customer , @Flag ='verify-customer-details', commented out, chages in @flag = 'ofac-list'
--Replace fullname with lastname, fix verify Remarks
-- #1121 - @flag = 'customer-details' , added introducer
--#1058 Lawson card Enhancement
-- #131 - possible duplicate matches  @Flag ='verify-customer-details' 
-- #11862 - Possible duplicate match sender - Mobile
-- #11968 - menu for delete customer , @flag = 'deleteCustomer-new'
-- #11922 - delete verfication for trustdoc on delete customer , @flag = 'deleteCustomer' 
-- #20532 changes in @flag = 'customer-register-core' for otp email for user reg from customer portal

---------------------------------------------------------------------------
IF @sortBy = 'SN'
	SET @sortBy = NULL;

SELECT @homePhone = @customerIdNo
	,@accountNumber = REPLACE(@accountNumber, '-', '');

IF ISNUMERIC(@country) <> '1'
	SET @country = (
			SELECT TOP 1 countryId
			FROM countryMaster WITH (NOLOCK)
			WHERE countryName = @country
			);

BEGIN TRY
	CREATE TABLE #msg (
		errorCode INT
		,msg VARCHAR(100)
		,id INT
		);

	DECLARE @sql VARCHAR(MAX)
		,@oldValue VARCHAR(MAX)
		,@newValue VARCHAR(MAX)
		,@module VARCHAR(10)
		,@tableAlias VARCHAR(100)
		,@logIdentifier VARCHAR(50)
		,@logParamMod VARCHAR(100)
		,@logParamMain VARCHAR(100)
		,@table VARCHAR(MAX)
		,@select_field_list VARCHAR(MAX)
		,@extra_field_list VARCHAR(MAX)
		,@sql_filter VARCHAR(MAX)
		,@modType VARCHAR(6)
		,@errorMsg VARCHAR(MAX)
		,@bankName VARCHAR(100)
		--,@registrationType VARCHAR(10) = NULL
		,@trustdocId  UNIQUEIDENTIFIER = NULL;


	SELECT @logIdentifier = 'customerId'
		,@logParamMain = 'customerMaster'
		,@module = '20'
		,@tableAlias = 'CustomerMaster';

	SET @fullName = UPPER(@fullName)
	SET @passportNo = UPPER(@passportNo)
	SET @firstName = UPPER(@firstName)
	SET @middleName = UPPER(@middleName)
	SET @lastName1 = UPPER(@lastName1)
	SET @lastName2 = UPPER(@lastName2)

	/***************************************GME Online Core************************************************/
	IF @flag = 'customer-list'
	BEGIN
		IF @sortBy IS NULL
			SET @sortBy = 'createdDate';

		IF @sortOrder IS NULL
			SET @sortOrder = 'DESC';
		SET @table = '(      
  SELECT      
   customerId as Id      
   ,fullName = ISNULL(firstName, '''') + ISNULL('' '' + middleName, '''') + ISNULL('' '' + lastName1, '''') + ISNULL('' '' + lastName2, '''')       
   ,sd.detailTitle as idType      
   ,ISNULL(cm.idNumber,'''') as idNumber      
   ,com.countryName      
   ,cm.city      
   ,ISNULL(cm.email,'''') as email      
   ,ISNULL(cm.mobile,'''') as mobile      
   ,cm.createdDate      
   ,accountName =cm.bankAccountNo      
   ,bankName = bl.bankName      
  FROM dbo.customerMaster cm(nolock)      
  LEFT JOIN dbo.staticDataValue sd(nolock) ON sd.valueId=cm.idType      
  INNER JOIN dbo.countryMaster com(nolock) ON com.countryId = cm.nativeCountry      
  LEFT JOIN vwBankLists bl (NOLOCK) ON cm.bankName = bl.rowId      
  WHERE 1=1 and cm.approvedDate is null       
  ';

		IF @createdDate IS NOT NULL
			SET @table = @table + '  AND cm.createdDate between ''' + CONVERT(VARCHAR, @createdDate, 101) + ''' AND ''' + CONVERT(VARCHAR, @createdDate, 101) + ' 23:59:59''';
		SET @table = @table + ')x';
		SET @sql_filter = '';

		IF @mobile IS NOT NULL
			SET @sql_filter = @sql_filter + '  AND REPLACE(idNumber, ''-'', '''') =''' + REPLACE(@mobile, '-', '') + '''';

		--SET @sql_filter = @sql_filter + '  AND mobile ='''+ @mobile + '''';      
		IF @email IS NOT NULL
			SET @sql_filter += ' AND  email like ''' + @email + '%''';

		IF @custNativecountry IS NOT NULL
			SET @sql_filter += ' AND  countryName = ''' + @custNativecountry + '''';
		SET @select_field_list = 'id,fullName,idType,idNumber,countryName,city,email,mobile,createdDate,accountName,bankName';

		EXEC dbo.proc_paging @table
			,@sql_filter
			,@select_field_list
			,@extra_field_list
			,@sortBy
			,@sortOrder
			,@pageSize
			,@pageNumber;

		RETURN;
	END;

	IF @flag = 'customer-list-approved'
	BEGIN
		IF @sortBy IS NULL
			SET @sortBy = 'createdDate';

		IF @sortOrder IS NULL
			SET @sortOrder = 'DESC';
		SET @table = '(      
  SELECT      
   customerId as Id      
   ,fullName = ISNULL(firstName, '''') + ISNULL('' '' + middleName, '''') + ISNULL('' '' + lastName1, '''') + ISNULL('' '' + lastName2, '''')       
   ,sd.detailTitle as idType      
   ,ISNULL(cm.idNumber,'''') as idNumber      
   ,com.countryName      
   ,cm.city      
   ,ISNULL(cm.email,'''') as email      
   ,ISNULL(cm.mobile,'''') as mobile      
   ,cm.createdDate      
   ,accountName =cm.bankAccountNo      
   ,bankName = bl.bankName      
  FROM dbo.customerMaster cm(nolock)      
  LEFT JOIN dbo.staticDataValue sd(nolock) ON sd.valueId=cm.idType      
  INNER JOIN dbo.countryMaster com(nolock) ON com.countryId = cm.nativeCountry      
  LEFT JOIN vwBankLists bl (NOLOCK) ON cm.bankName = bl.rowId      
  WHERE 1=1 and cm.approvedDate is not null       
  ';

		IF @createdDate IS NOT NULL
			SET @table = @table + '  AND cm.createdDate between ''' + CONVERT(VARCHAR, @createdDate, 101) + ''' AND ''' + CONVERT(VARCHAR, @createdDate, 101) + ' 23:59:59''';
		SET @table = @table + ')x';
		SET @sql_filter = '';

		IF @mobile IS NOT NULL
			SET @sql_filter = @sql_filter + '  AND REPLACE(idNumber, ''-'', '''') =''' + REPLACE(@mobile, '-', '') + '''';

		--SET @sql_filter = @sql_filter + '  AND mobile ='''+ @mobile + '''';      
		IF @email IS NOT NULL
			SET @sql_filter += ' AND  email like ''' + @email + '%''';

		IF @custNativecountry IS NOT NULL
			SET @sql_filter += ' AND  countryName = ''' + @custNativecountry + '''';
		SET @select_field_list = 'id,fullName,idType,idNumber,countryName,city,email,mobile,createdDate,accountName,bankName';

		EXEC dbo.proc_paging @table
			,@sql_filter
			,@select_field_list
			,@extra_field_list
			,@sortBy
			,@sortOrder
			,@pageSize
			,@pageNumber;

		RETURN;
	END;

	IF @flag = 'resetpwd'
	BEGIN
		UPDATE dbo.customerMaster
		SET customerPassword = dbo.FNAEncryptString(@newPassword)
			,isForcedPwdChange = 1
			,invalidAttemptCount = 0
		WHERE customerId = @customerId;

		EXEC dbo.proc_errorHandler '0'
			,'Success!'
			,NULL;

		RETURN;
	END;

	IF @flag = 'autosetpwd'
	BEGIN
		SELECT @mobile = mobile
			,@firstName = firstName
		FROM customerMaster(NOLOCK)
		WHERE customerId = @customerId;

		SELECT @newPassword = LOWER(LEFT(@firstName, 1)) + LOWER(RIGHT(NEWID(), 6)) + '@G';

		SET @newPassword = REPLACE(@newPassword, 'o', 'z');
		SET @newPassword = REPLACE(@newPassword, '0', '9');
		SET @newPassword = REPLACE(@newPassword, 'i', 'L');

		UPDATE dbo.customerMaster
		SET customerPassword = dbo.FNAEncryptString(@newPassword)
			,isForcedPwdChange = 0
			,invalidAttemptCount = 0
			,modifiedBy = @user
			,modifiedDate = GETDATE()
		WHERE customerId = @customerId;

		SET @errorMsg = 'Your JME login password is ' + @newPassword;

		EXEC proc_CallToSendSMS @FLAG = 'I'
			,@SMSBody = @errorMsg
			,@MobileNo = @mobile;

		EXEC dbo.proc_errorHandler '0'
			,'Success!'
			,@mobile;

		RETURN;
	END;

	IF @flag = 'sEmail'
	BEGIN
		SELECT email
		FROM dbo.customerMaster(NOLOCK)
		WHERE customerId = @customerId;

		RETURN;
	END;

	IF @flag = 'modify-list'
	BEGIN
		IF @sortBy IS NULL
			SET @sortBy = 'createdDate';

		IF @sortOrder IS NULL
			SET @sortOrder = 'DESC';
		SET @table = '(      
 SELECT       
  cm.customerId      
  ,fullName = cm.firstName      
  ,sd.detailTitle as idType      
  ,ISNULL(cm.idNumber,'''') as idNumber      
  ,ISNULL(cm.email,'''') as email      
  ,ISNULL(cm.mobile,'''') as mobile      
  ,cm.createdDate      
  ,cm.bankAccountNo      
  ,bankName = bl.bankName      
  ,cm.walletAccountNo      
 FROM dbo.customerMaster cm(nolock)      
 LEFT JOIN dbo.staticDataValue sd(nolock) ON sd.valueId=cm.idType      
 LEFT JOIN vwBankLists bl (NOLOCK) ON cm.bankName = bl.rowId      
 WHERE cm.approvedDate is not null and 1=1       
 ';

		IF @createdDate IS NOT NULL
			SET @table = @table + '  AND cm.verifiedDate between ''' + CONVERT(VARCHAR, @createdDate, 101) + ''' AND ''' + CONVERT(VARCHAR, @createdDate, 101) + ' 23:59:59''';
		SET @table = @table + ')x';
		SET @sql_filter = '';

		IF ISNULL(@searchCriteria, '') <> ''
			AND ISNULL(@searchValue, '') <> ''
		BEGIN
			IF @searchCriteria = 'idNumber'
			BEGIN
				IF ISNUMERIC(@searchValue) <> 1
					SET @searchValue = '-1';--to ignore string value for datatype integer/customerID      
				SET @sql_filter = @sql_filter + ' AND customerId = ''' + @searchValue + '''';
			END;
			ELSE IF @searchCriteria = 'emailId'
				SET @sql_filter = @sql_filter + ' AND email like ''' + @searchValue + '%''';
			ELSE IF @searchCriteria = 'customerName'
				SET @sql_filter = @sql_filter + ' AND fullName like ''' + @searchValue + '%''';
			ELSE IF @searchCriteria = 'mobile'
				SET @sql_filter = @sql_filter + ' AND mobile = ''' + @searchValue + '''';
		END;

		SET @select_field_list = 'customerId,fullName,idType,idNumber,email,mobile,createdDate,bankAccountNo,bankName,walletAccountNo';

		EXEC dbo.proc_paging @table
			,@sql_filter
			,@select_field_list
			,@extra_field_list
			,@sortBy
			,@sortOrder
			,@pageSize
			,@pageNumber;

		RETURN;
	END;

	IF @flag = 's'
	BEGIN
		IF @sortBy IS NULL
			SET @sortBy = 'createdDate';

		IF @sortOrder IS NULL
			SET @sortOrder = 'DESC';
		SET @table = '(      
  SELECT       
   customerId,      
   membershipId  =''<a target="_blank" href="/Remit/Administration/CustomerSetup/CustomerDetails.aspx?customerId=''+ CAST(CM.CUSTOMERID AS VARCHAR) + ''">''  +  CM.MEMBERSHIPID+''</a>''    
   ,fullName =fullName      
   ,sd.detailTitle as idType      
   ,ISNULL(cm.idNumber,'''') as idNumber      
   ,com.countryName,cm.dob,cm.address      
   ,cm.city      
   ,ISNULL(cm.email,'''') as email      
   ,ISNULL(cm.mobile,'''') as mobile      
   ,cm.createdDate      
   ,cm.bankAccountNo      
   ,bankName = bl.bankName      
   ,cm.LawsonCardNo
   ,RegistrationType =  CASE WHEN cm.RegistrationType IS NULL THEN ''Manual'' ELSE cm.RegistrationType END
   ,ISNULL(cm.verificationCode, ''NOT COMPLETED'') as KYC_Status
   ,Introducer=cm.referelCode
  FROM dbo.customerMaster cm(nolock)      
  LEFT JOIN dbo.staticDataValue sd(nolock) ON sd.valueId=cm.idType      
  INNER JOIN dbo.countryMaster com(nolock) ON com.countryId = cm.nativeCountry      
  LEFT JOIN vwBankLists bl (NOLOCK) ON cm.bankName = bl.rowId      
  WHERE 1=1 ) x';
		SET @sql_filter = '';

		IF ISNULL(@fromDate, '') <> ''
			AND ISNULL(@toDate, '') <> ''
			SET @sql_filter += ' AND createdDate BETWEEN ''' + @fromDate + ''' AND ''' + @toDate + ' 23:59:59'''

		IF ISNULL(@kycStatus, '') <> ''
			SET @sql_filter = @sql_filter + ' AND KYC_Status like ''' + @kycStatus + '%''';
		BEGIN
			IF @searchCriteria = 'idNumber'
			BEGIN
				SET @sql_filter = @sql_filter + ' AND REPLACE(idNumber, ''-'', '''') = ''' + REPLACE(@searchValue, '-', '') + '''';
			END;
			ELSE IF @searchCriteria = 'emailId'
				SET @sql_filter = @sql_filter + ' AND email like ''' + @searchValue + '%''';
			ELSE IF @searchCriteria = 'customerName'
				SET @sql_filter = @sql_filter + ' AND fullName like ''' + @searchValue + '%''';
			ELSE IF @searchCriteria = 'mobile'
				SET @sql_filter = @sql_filter + ' AND mobile = ''' + @searchValue + '''';
			ELSE IF @searchCriteria = 'bankAccountNo'
				SET @sql_filter = @sql_filter + ' AND bankAccountNo = ''' + @searchValue + '''';
			ELSE IF @searchCriteria = 'nativeCountry'
				SET @sql_filter = @sql_filter + ' AND countryName = ''' + @searchValue + '''';
			ELSE IF @searchCriteria = 'LawsonCardNo'
				SET @sql_filter = @sql_filter + ' AND LawsonCardNo = ''' + @searchValue + '''';
			ELSE IF @searchCriteria = 'verificationCode'
				SET @sql_filter = @sql_filter + ' AND KYC_Status = ''' + @searchValue + '''';
			ELSE IF @searchCriteria = 'idType'
				SET @sql_filter = @sql_filter + ' AND idType like ''' + @searchValue + '%''';

		END;

		--IF @createdDate IS NOT NULL      
		--           SET @table = @table + '  AND cm.createdDate between '''+ CONVERT(VARCHAR,@createdDate,101) + ''' AND '''+ CONVERT(VARCHAR,@createdDate,101) + ' 23:59:59''';      
		--  SET @table=@table+')x'      
		-- SET @sql_filter = ''      
		--          IF @mobile IS NOT NULL      
		--              SET @sql_filter = @sql_filter + '  AND mobile ='''+ @mobile + '''';      
		--          IF @email IS NOT NULL      
		--              SET @sql_filter += ' AND  email=''' + @email+ '''';      
		SET @select_field_list = 'customerId,membershipId,dob,address,fullName,idType,idNumber,countryName,city,email,mobile,createdDate,bankAccountNo,bankName,LawsonCardNo,RegistrationType,KYC_Status,Introducer';

		EXEC dbo.proc_paging @table
			,@sql_filter
			,@select_field_list
			,@extra_field_list
			,@sortBy
			,@sortOrder
			,@pageSize
			,@pageNumber;

		RETURN;
	END;

	IF @flag = 'customer-register-core'
	BEGIN
		SET @onlineUser = CASE 
				WHEN @onlineUser = 'true'
					THEN 'Y'
				ELSE 'N'
				END
		SET @mobileUser = CASE 
				WHEN @mobileUser = 'true'
					THEN 'Y'
				ELSE 'N'
				END
		
		IF EXISTS (
				SELECT 1
				FROM customermaster
				WHERE (
						email = @custEmail
						OR customerEmail = @custEmail
						OR userName = @custEmail
						)
				)
		BEGIN
			SELECT '1' ErrorCode
				,'email already used' Msg
				,NULL id;

			RETURN
		END

		IF EXISTS (
		    SELECT 1
		    FROM customermaster
		    WHERE 
					--@custMobile = mobile
					--OR
					--(LEN(@custMobile) = 10 AND (
					--    mobile = '44' + @custMobile OR
					--    '+44' + mobile = @custMobile
					--))
					--OR
					--(LEN(@custMobile) = 12 AND LEFT(@custMobile, 2) = '44' AND (
					--    mobile = SUBSTRING(@custMobile, 3, 10)
					--))
					--OR
					--(LEN(@custMobile) = 13 AND LEFT(@custMobile, 3) = '+44' AND (
					--    mobile = SUBSTRING(@custMobile, 4, 10)
					--))
		        @custMobile IN (mobile, '+44' + mobile, '44' + mobile, SUBSTRING(mobile, 3, LEN(mobile)))
		)
		BEGIN
		    SELECT '1' AS ErrorCode,
		           'Mobile No. ''' + @custMobile + ''' is already used' AS Msg,
		           @custMobile AS id;
		
		    RETURN;
		END

		--IF RIGHT(LEFT(@customerIdNo, 7), 1) <> '-'      
		--BEGIN        
		-- SELECT @errorMsg = 'Invalid Id number ' + @customerIdNo + ', your id number must be similar to XXXXXX-XXXXXXX(Include ''-'' also).'        
		-- EXEC proc_errorHandler 1, @errorMsg, @customerId       
		-- RETURN        
		--END        
		DECLARE @OBP_ID INT = NULL

		--SELECT @OBP_ID = MAX(CAST(OBPID AS INT)) FROM CUSTOMERMASTER (NOLOCK)       
		--SET @OBP_ID = @OBP_ID + 1      
		--postalcode      
		IF EXISTS (
				SELECT 'X'
				FROM customerMaster WITH (NOLOCK)
				WHERE email = @custEmail
				)
		BEGIN
			SELECT @errorMsg = 'Customer with email ' + @custEmail + ' already exist.';

			EXEC proc_errorHandler 1
				,@errorMsg
				,@customerId;

			RETURN;
		END;

		IF EXISTS (
				SELECT 'X'
				FROM customerMaster WITH (NOLOCK)
				WHERE email = @custEmail
					AND ISNULL(onlineUser, 'N') = 'Y'
					AND ISNULL(isDeleted, 'N') = 'N'
				)
		BEGIN
			SELECT @errorMsg = 'Customer with email ' + @custEmail + ' already exist.';

			EXEC proc_errorHandler 1
				,@errorMsg
				,@customerId;

			RETURN;
		END;

		IF EXISTS (
				SELECT 'x'
				FROM customerMaster(NOLOCK)
				WHERE REPLACE(idNumber, '-', '') = REPLACE(@customerIdNo, '-', '')
				)
		BEGIN
			SELECT @errorMsg = 'Customer with idnumber ' + @customerIdNo + ' already exist.';

			EXEC proc_errorHandler 1
				,@errorMsg
				,@customerId;

			RETURN;
		END;

		DECLARE @newMobileNumber VARCHAR(20) = REPLACE(@custMobile, '+', '')

		SET @newMobileNumber = CASE 
				WHEN @newMobileNumber LIKE '44%'
					THEN STUFF(@newMobileNumber, 1, 2, '')
				ELSE @newMobileNumber
				END

		--SET @newMobileNumber = '%' + @newMobileNumber      
		--            IF EXISTS ( SELECT  'X' FROM customerMaster WITH (NOLOCK)      
		--      WHERE   mobile LIKE @newMobileNumber)      
		--                BEGIN        
		--                    SELECT  @errorMsg = 'Customer with mobile number '+ @custMobile + ' already exist.';        
		--                    EXEC proc_errorHandler 1, @errorMsg, @customerId;        
		--                    RETURN;        
		--                END;       
		--IF @customerIdType IN (1302,8008)      
		--    AND LEN(@customerIdNo) <> 14      
		--    BEGIN        
		--        SELECT  @errorMsg = 'Invalid Id number '+ @customerIdNo + ', your id number must be similar to XXXXXX-XXXXXXX(Include ''-'' also).';          
		--        EXEC proc_errorHandler 1, @errorMsg, @customerId;        
		--        RETURN;        
		--    END;      
		SELECT @firstName = LTRIM(RTRIM(@firstName))
			,@middleName = LTRIM(RTRIM(@middleName))
			,@lastName1 = LTRIM(RTRIM(@lastName1))
			,@fullName = ISNULL(@firstName, '') + ISNULL(' ' + @middleName, '') + ISNULL(' ' + @lastName1, '') + ISNULL(' ' + @lastName2, '')

		IF EXISTS (
				SELECT 'X'
				FROM customerMaster WITH (NOLOCK)
				WHERE fullName = @fullName
					AND dob = @custDOB
					AND ISNULL(onlineUser, 'N') = 'Y'
					AND ISNULL(mobileUser, 'N') = 'Y'
					AND ISNULL(isDeleted, 'N') = 'N'
				)
		BEGIN
			SELECT @errorMsg = 'It looks like you have already registered with IME London with fullname: ' + @fullName + ' and DOB:  ' + @custDOB + '.\nPlease contact us  on +44 02088660307 or e-mail us at info@imelondon.co.uk for any assistance.';

			EXEC proc_errorHandler 1
				,@errorMsg
				,@customerId;

			RETURN;
		END;

		IF @newPassword IS NULL
		BEGIN
			SET @newPassword = RIGHT('0000000' + CAST(CHECKSUM(NEWID()) AS VARCHAR), 7);
		END

		IF @streetUnicode = 'Nnull'
		BEGIN
			SET @streetUnicode = NULL;
		END;

		IF @cityUnicode = 'Nnull'
		BEGIN
			SET @cityUnicode = NULL;
		END;

		IF @customerType = '4701'
		BEGIN
			SET @employeeBusinessType = NULL;
		END

		--IF ISNULL(@street, '') <> ''
		--BEGIN
		--	SET @district = @street
		--	SET @street = rtrim(ltrim(replace(replace(cast(@streetunicode AS VARCHAR), '?', ''), '-', '')));

		--	SELECT @custCity = CITY_NAME
		--	FROM TBL_JAPAN_ADDRESS_DETAIL(NOLOCK)
		--	WHERE ROW_ID = @district
		--END
		--ELSE
		--BEGIN
		--	SELECT @custCity = CITY_NAME
		--		,@district = ROW_ID
		--	FROM TBL_JAPAN_ADDRESS_DETAIL(NOLOCK)
		--	WHERE ZIP_CODE = @zipCode
		--END

		BEGIN TRANSACTION;

		INSERT INTO customerMaster (
			firstName
			,middleName
			,lastName1
			,lastName2
			,country
			,[address]
			,zipCode
			,district
			,city
			,email
			,homePhone
			,workPhone
			,mobile
			,nativeCountry
			,dob
			,placeOfIssue
			,occupation
			,isBlackListed
			,lastTranId
			,relationId
			,relativeName
			,gender
			,salaryRange
			,address2
			,fullName
			,createdBy
			,createdDate
			,postalCode
			,idIssueDate
			,idExpiryDate
			,idType
			,idNumber
			,telNo
			,agentId
			,branchId
			,onlineUser
			,ipAddress
			,customerPassword
			,customerType
			,isActive
			,verifiedBy
			,verifiedDate
			,isForcedPwdChange
			,bankName
			,bankAccountNo
			,HasDeclare
			,membershipId
			,[state]
			,sourceOfFund
			,street
			,streetUnicode
			,cityUnicode
			,visaStatus
			,employeeBusinessType
			,nameOfEmployeer
			,SSNNO
			,remittanceAllowed
			,remarks
			,registerationNo
			,organizationType
			,dateofIncorporation
			,natureOfCompany
			,position
			,nameOfAuthorizedPerson
			,monthlyIncome
			,OBPID
			,ADDITIONALADDRESS
			,documentType
			,occupationOther
			,isExistingCustomer
			,createdFrom
			,referralId
			,referelCode
			,otherIdNumber
			,serviceUsedFor
			,mobileUser
			,username
			,isEmailVerified
			,lawsoncardno
			,registrationType
			,verificationCode
			
			)
		VALUES (
			@firstName
			,@middleName
			,@lastName1
			,@lastName2
			,@country
			,@custAdd1
			,ISNULL(@zipCode, @custPostal)
			,@district
			,@custCity
			,@custEmail
			,NULL
			,@workPhone
			,@custMobile
			,ISNULL(@custNativecountry,@country)
			,@custDOB
			,@placeOfIssue
			,@occupation
			,@isBlackListed
			,@lastTranId
			,@relationId
			,@relativeName
			,@custGender
			,@custSalary
			,@custAdd2
			,@fullName
			,@user
			,DATEADD(HH, 0, GETUTCDATE())
			,@custPostal
			,@custIdissueDate
			,@custIdValidDate
			,@customerIdType
			,@customerIdNo
			,@custTelNo
			,@loginBranchId
			,@branch
			,@onlineUser
			,@ipAddress
			,dbo.FNAEncryptString(@password)
			,@customerType
			,'Y'
			,@user
			,NULL
			,0
			,@bankId
			,@accountNumber
			,@HasDeclare
			,---- New Added Values      
			@membershipId
			,UPPER(@state)
			,UPPER(@sourceOfFound)
			,UPPER(@street)
			,UPPER(@streetUnicode)
			,UPPER(@cityUnicode)
			,@visaStatus
			,@employeeBusinessType
			,@nameOfEmployeer
			,@SSNNO
			,@remittanceAllowed
			,@remarks
			,@registerationNo
			,@organizationType
			,@dateofIncorporation
			,@natureOfCompany
			,@position
			,@nameOfAuthorizedPerson
			,@monthlyIncome
			,NULL
			,@additionalAddress
			,@docType
			,@occupationOther
			,CASE 
				WHEN @serviceUsedFor = 'on' THEN 0 ELSE 1 END
			,CASE @createdFrom WHEN 'W' THEN 'O' ELSE 'C' END
			,@referralId
			,@referralCode
			,UPPER(@customerOtherIdNo)
			,CASE 
				WHEN @serviceUsedFor = 'on'
					THEN 'CM'
				ELSE 'C'
				END
			,'Y'
			,CASE 
				 WHEN @serviceUsedFor IN ('on', 'online', 'CM') THEN @custEmail ELSE @custEmail END
			, 0
			,CASE @createdFrom WHEN 'W' THEN 'KYC_LATER' ELSE 'KYC_NOW' END
			,'MKYC'
			,CASE @createdFrom WHEN 'C' THEN 'PROCESSING' ELSE 'NOT_COMPLETED' END
			)

		SET @customerId = SCOPE_IDENTITY();

		EXEC PROC_GENERATE_MEMBERSHIP_ID @USER = @user
			,@CUSTOMERID = @customerId
			,@loginBranchId = @loginBranchId
			,@MEMBESHIP_ID = @membershipId OUT;

		UPDATE dbo.customerMaster
		SET membershipId = @membershipId
		WHERE customerId = @customerId;

		--IF @isCounterVisited ='Y'      
		--BEGIN      
		--    INSERT INTO dbo.TBL_CUSTOMER_KYC      
		--     ( customerId,kycMethod,kycStatus,createdBy,createdDate,isDeleted      
		--     )      
		--   VALUES  (       
		--      @customerId,11048,11044,@user,GETDATE(),0      
		--     )      
		--END      
		IF @@TRANCOUNT > 0
			COMMIT TRANSACTION;

		SELECT '0' ErrorCode
			,'Customer has been registered successfully with membershipId: ' + CAST(@membershipId AS VARCHAR) Msg
			,@customerId id
			,cast(@customerId AS VARCHAR) + '|' + cast(@membershipId AS VARCHAR) + '|' + cast(cast(GETDATE() AS DATE) AS VARCHAR) extra
			,cast(@membershipId AS VARCHAR) extra1
	END;

	IF @flag = 'customer-details'
	BEGIN
		DECLARE @isTxnMade CHAR(1) , @introducer VARCHAR(100)

		IF(@customerId IS NULL)
		RETURN;

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

		--if exists (select 1 from remitTran RT(nolock)       
		--   inner join customerMaster CM(nolock) on CM.email = RT.createdBy      
		--   where CM.customerId = @customerId)      
		-- set @isTxnMade = 'Y'      
		--else      
		-- set @isTxnMade = 'N'      
		SELECT cu.customerId
			,isTxnMade = 'N'
			,firstName
			,middleName
			,lastName1
			,lastName2
			,country
			,[address]
			,zipCode = ISNULL(zipCode, postalCode)
			,city
			,email = ISNULL(email, '')
			,mobile = cu.mobile
			,nativeCountry = (
				SELECT countryName
				FROM dbo.countryMaster
				WHERE countryId = cu.nativeCountry
				)
			,cu.nativeCountry nativeCountryId
			,homePhone
			,occupation
			,address2
			,fullName
			,ISNULL(postalCode,zipcode) postalCode
			,CONVERT(VARCHAR, idType) + '|' + CONVERT(VARCHAR, sv.detailDesc) + '|' + CONVERT(VARCHAR, ISNULL(CID.expiryType, 'N')) idType
			,UPPER(idNumber) idNumber
			,telNo
			,
			--companyName ,      
			gender
			,cu.ipAddress
			,verifyDoc1
			,verifyDoc2
			,verifyDoc3
			,verifyDoc4 = SelfieDoc
			,verifiedBy
			,verifiedDate = CONVERT(VARCHAR, verifiedDate)
			,bankAccountNo
			,bankName = vb.rowId
			,isApproved = CASE 
				WHEN cu.approvedBy IS NULL
					THEN 'N'
				ELSE 'Y'
				END
			,CONVERT(VARCHAR(10), dob, 121) AS dob
			,CONVERT(VARCHAR(10), idIssueDate, 121) AS idIssueDate
			,CONVERT(VARCHAR(10), idExpiryDate, 121) AS idExpiryDate
			,
			---added value field on 2018-12-28 by anoj      
			membershipId
			,[state]
			,sourceOfFund
			,additionalAddress
			,street = STREET_NAME
			,streetUnicode = STREET_JAPANESE
			,cityUnicode = CITY_JAPANESE
			,city = CITY_NAME
			,visaStatus
			,employeeBusinessType
			,nameOfEmployeer
			,SSNNO
			,remittanceAllowed
			,remarks
			,registerationNo
			,organizationType
			,CONVERT(VARCHAR(10), dateofIncorporation, 121) AS dateofIncorporation
			,natureOfCompany
			,position
			,onlineUser
			,mobileUser
			,customerType
			,nameOfAuthorizedPerson
			,sv.detailTitle IdTypeName
			,cu.idNumber
			,cu.monthlyIncome
			,documentType
			,district
			,occupationText = occupationOther
			,CONVERT(VARCHAR(10), cu.createdDate, 121) createdDate
			,REF.REFERRAL_NAME
			,REF.REFERRAL_CODE
			,cu.otherIdNumber
			,sv.valueId [idTypeValue]
			,createdFrom = ISNULL(cu.createdFrom, 'C')
			,allowEditEmail = CASE 
				WHEN ISNULL(cu.createdFrom, 'C') = 'C'
					THEN 'Y'
				ELSE 'N'
				END
			,serviceUsedFor = ISNULL(serviceUsedFor, 'C')
			,CONVERT(VARCHAR, cu.createdDate, 23) AS createdDate
			,approvedDate = CONVERT(VARCHAR, cu.approvedDate)
			,cu.approvedBy
			,cu.lawsonCardno
			,cu.verifyRemarks
			,introducer = @introducer
			,cu.registrationType
			,mu.ResidenceType
			,ISNULL(mu.useNfc,'Y') useNfc
		FROM customerMaster cu WITH (NOLOCK)
		LEFT JOIN tbl_japan_address_detail TJ(NOLOCK) ON TJ.ROW_ID = CU.DISTRICT
		LEFT JOIN vwBankLists vb WITH (NOLOCK) ON cu.bankName = vb.rowId
		LEFT JOIN dbo.staticDataValue sv(NOLOCK) ON sv.valueId = cu.idType
		LEFT JOIN dbo.countryMaster CM(NOLOCK) ON CM.countryId = CU.country
		LEFT JOIN countryIdType CID WITH (NOLOCK) ON CID.IdTypeId = cu.idType
		LEFT JOIN REFERRAL_AGENT_WISE REF(NOLOCK) ON REF.REFERRAL_CODE = CU.REFERRALID
		LEFT JOIN mobile_userRegistration mu on mu.customerId=cu.customerId
		WHERE cu.customerId = @customerId;
	END;

	IF @flag = 'customer-update-approve'
	BEGIN
		IF ISNULL(@customerType, '') = ''
			SET @customerType = '4700' --SET CUSTOMER TYPE AS INDIVIDUAL
				--LOG FOR CUSTOMER UPDATE      
		SET @fullName = ISNULL(@firstName, '') + ISNULL(' ' + @middleName, '') + ISNULL(' ' + @lastName1, '');
		SET @onlineUser = CASE 
				WHEN @onlineUser = 'true'
					THEN 'Y'
				ELSE 'N'
				END
		SET @mobileUser = CASE 
				WHEN @mobileUser = 'true'
					THEN 'Y'
				ELSE 'N'
				END

		UPDATE dbo.customerMaster
		SET firstName = @firstName
			,middleName = CASE 
				WHEN @customerType = '4700'
					THEN @middleName
				ELSE NULL
				END
			,lastName1 = CASE 
				WHEN @customerType = '4700'
					THEN @lastName1
				ELSE NULL
				END
			,lastName2 = CASE 
				WHEN @customerType = '4700'
					THEN @lastName2
				ELSE NULL
				END
			,country = @country
			,[address] = @custAdd1
			,district = @district
			,city = @custCity
			,email = @custEmail
			,mobile = @custMobile
			,nativeCountry = @custNativecountry
			,occupation = CASE 
				WHEN @customerType = '4700'
					THEN @occupation
				ELSE NULL
				END
			,gender = CASE 
				WHEN @customerType = '4700'
					THEN @custGender
				ELSE NULL
				END
			,fullName = ISNULL(@firstName, '') + ISNULL(' ' + @middleName, '') + ISNULL(' ' + @lastName1, '') + ISNULL(' ' + @lastName2, '')
			,telNo = @custTelNo
			,agentId = @agent
			,branchId = @branch
			,dob = CASE 
				WHEN @custDOB IS NOT NULL
					THEN @custDOB
				ELSE dob
				END
			,onlineUser = @onlineUser
			,mobileUser = @mobileUser
			,customerType = @customerType
			,isActive = 'Y'
			,modifiedBy = @user
			,modifiedDate = GETDATE()
			,idIssueDate = ISNULL(@custIdissueDate, idIssueDate) --new added by dhan      
			,idExpiryDate = ISNULL(@custIdValidDate, idExpiryDate)
			,idType = ISNULL(@customerIdType, idType)
			,idNumber = ISNULL(@customerIdNo, idNumber)
			,zipCode = @zipCode
			,
			--added New Field Value on 2018-12-28 --added by anoj      
			[state] = @state
			,sourceOfFund = @sourceOfFound
			,street = @street
			,streetUnicode = @streetUnicode
			,cityUnicode = @cityUnicode
			,visaStatus = CASE 
				WHEN @customerType = '4700'
					THEN @visaStatus
				ELSE NULL
				END
			,employeeBusinessType = CASE 
				WHEN @customerType = '4700'
					THEN @employeeBusinessType
				ELSE NULL
				END
			,nameOfEmployeer = CASE 
				WHEN @customerType = '4700'
					THEN @nameOfEmployeer
				ELSE NULL
				END
			,SSNNO = CASE 
				WHEN @customerType = '4700'
					THEN @SSNNO
				ELSE NULL
				END
			,remittanceAllowed = CASE 
				WHEN @customerType = '4700'
					THEN @remittanceAllowed
				ELSE NULL
				END
			,remarks = CASE 
				WHEN @customerType = '4700'
					THEN @remarks
				ELSE NULL
				END
			,registerationNo = CASE 
				WHEN @customerType = '4700'
					THEN NULL
				ELSE @registerationNo
				END
			,organizationType = CASE 
				WHEN @customerType = '4700'
					THEN NULL
				ELSE @organizationType
				END
			,dateofIncorporation = CASE 
				WHEN @customerType = '4700'
					THEN NULL
				ELSE @dateofIncorporation
				END
			,natureOfCompany = CASE 
				WHEN @customerType = '4700'
					THEN NULL
				ELSE @natureOfCompany
				END
			,position = CASE 
				WHEN @customerType = '4700'
					THEN NULL
				ELSE @position
				END
			,nameOfAuthorizedPerson = CASE 
				WHEN @customerType = '4700'
					THEN NULL
				ELSE @nameOfAuthorizedPerson
				END
			,companyName = CASE 
				WHEN @customerType = '4700'
					THEN NULL
				ELSE @companyName
				END
			,monthlyIncome = @monthlyIncome
		WHERE customerId = @customerId;

		SELECT '0' ErrorCode
			,'Customer has been updated successfully.' Msg
			,@customerId id;
	END;

	IF @flag = 'customer-update-new'
	BEGIN
		SET @fullName = ISNULL(@firstName, '') + ISNULL(' ' + @middleName, '') + ISNULL(' ' + @lastName1, '');
		SET @onlineUser = CASE 
				WHEN @onlineUser = 'true'
					THEN 'Y'
				ELSE 'N'
				END
		SET @mobileUser = CASE 
				WHEN @mobileUser = 'true'
					THEN 'Y'
				ELSE 'N'
				END

		EXEC PROC_CUSTOMERMODIFYLOG @flag = 'i'
			,@user = @user
			,@mobileUser = @mobileUser
			,@customerId = @customerId
			,@customerType = @customerType
			,@firstName = @firstName
			,@middleName = @middleName
			,@lastName1 = @lastName1
			,@fullName = @fullName
			,@country = @country
			,@zipCode = @zipCode
			,@state = @state
			,@street = @street
			,@custCity = @custCity
			,@cityUnicode = @cityUnicode
			,@streetUnicode = @streetUnicode
			,@custGender = @custGender
			,@custNativecountry = @custNativecountry
			,@dob = @custDOB
			,@email = @custEmail
			,@custTelNo = @custTelNo
			,@mobileNumber = @custMobile
			,@visaStatus = @visaStatus
			,@employeeBusinessType = @employeeBusinessType
			,@nameOfEmployeer = @nameOfEmployeer
			,@SSNNO = @SSNNO
			,@occupation = @occupation
			,@sourceOfFound = @sourceOfFound
			,@monthlyIncome = @monthlyIncome
			,@idType = @customerIdType
			,@idNumber = @customerIdNo
			,@issueDate = @custIdissueDate
			,@expiryDate = @custIdValidDate
			,@remittanceAllowed = @remittanceAllowed
			,@onlineUser = @onlineUser
			,@remarks = @remarks
			,@companyName = @companyName
			,@registerationNo = @registerationNo
			,@organizationType = @organizationType
			,@dateofIncorporation = @dateofIncorporation
			,@natureOfCompany = @natureOfCompany
			,@nameOfAuthorizedPerson = @nameOfAuthorizedPerson
			,@position = @position
			,@bank = @bank
			,@accNumber = @accountNumber

		UPDATE dbo.customerMaster
		SET firstName = @firstName
			,middleName = CASE 
				WHEN @customerType = '4700'
					THEN @middleName
				ELSE NULL
				END
			,lastName1 = CASE 
				WHEN @customerType = '4700'
					THEN @lastName1
				ELSE NULL
				END
			,lastName2 = CASE 
				WHEN @customerType = '4700'
					THEN @lastName2
				ELSE NULL
				END
			,country = @country
			,[address] = @custAdd1
			,district = @district
			,city = @custCity
			,email = @custEmail
			,mobile = @custMobile
			,nativeCountry = @custNativecountry
			,occupation = CASE 
				WHEN @customerType = '4700'
					THEN @occupation
				ELSE NULL
				END
			,gender = CASE 
				WHEN @customerType = '4700'
					THEN @custGender
				ELSE NULL
				END
			,fullName = ISNULL(@firstName, '') + ISNULL(' ' + @middleName, '') + ISNULL(' ' + @lastName1, '') + ISNULL(' ' + @lastName2, '')
			,telNo = @custTelNo
			,agentId = @agent
			,branchId = @branch
			,dob = CASE 
				WHEN @custDOB IS NOT NULL
					THEN @custDOB
				ELSE dob
				END
			,onlineUser = @onlineUser
			,mobileUser = @mobileUser
			,customerType = @customerType
			,isActive = 'Y'
			,modifiedBy = @user
			,modifiedDate = GETDATE()
			,idIssueDate = ISNULL(@custIdissueDate, idIssueDate) --new added by dhan      
			,idExpiryDate = ISNULL(@custIdValidDate, idExpiryDate)
			,idType = ISNULL(@customerIdType, idType)
			,idNumber = ISNULL(@customerIdNo, idNumber)
			,zipCode = @zipCode
			,
			--added New Field Value on 2018-12-28 --added by anoj      
			[state] = @state
			,sourceOfFund = @sourceOfFound
			,street = @street
			,streetUnicode = @streetUnicode
			,cityUnicode = @cityUnicode
			,visaStatus = CASE 
				WHEN @customerType = '4700'
					THEN @visaStatus
				ELSE NULL
				END
			,employeeBusinessType = CASE 
				WHEN @customerType = '4700'
					THEN @employeeBusinessType
				ELSE NULL
				END
			,nameOfEmployeer = CASE 
				WHEN @customerType = '4700'
					THEN @nameOfEmployeer
				ELSE NULL
				END
			,SSNNO = CASE 
				WHEN @customerType = '4700'
					THEN @SSNNO
				ELSE NULL
				END
			,remittanceAllowed = CASE 
				WHEN @customerType = '4700'
					THEN @remittanceAllowed
				ELSE NULL
				END
			,remarks = CASE 
				WHEN @customerType = '4700'
					THEN @remarks
				ELSE NULL
				END
			,registerationNo = CASE 
				WHEN @customerType = '4700'
					THEN NULL
				ELSE @registerationNo
				END
			,organizationType = CASE 
				WHEN @customerType = '4700'
					THEN NULL
				ELSE @organizationType
				END
			,dateofIncorporation = CASE 
				WHEN @customerType = '4700'
					THEN NULL
				ELSE @dateofIncorporation
				END
			,natureOfCompany = CASE 
				WHEN @customerType = '4700'
					THEN NULL
				ELSE @natureOfCompany
				END
			,position = CASE 
				WHEN @customerType = '4700'
					THEN NULL
				ELSE @position
				END
			,nameOfAuthorizedPerson = CASE 
				WHEN @customerType = '4700'
					THEN NULL
				ELSE @nameOfAuthorizedPerson
				END
			,companyName = CASE 
				WHEN @customerType = '4700'
					THEN NULL
				ELSE @companyName
				END
			,monthlyIncome = @monthlyIncome
		WHERE customerId = @customerId;

		SELECT '0' ErrorCode
			,'Customer has been updated successfully.' Msg
			,@customerId id;
	END;

	IF @flag = 'customer-editeddata'
	BEGIN
		IF EXISTS (
				SELECT 1
				FROM customerMasterEditedDataMod
				WHERE customerId = @customerId
				)
		BEGIN
			SELECT '1' ErrorCode
				,'Approve previous modification First.' Msg
				,@customerId id;

			RETURN
		END

		IF EXISTS (
				SELECT 1
				FROM customermaster
				WHERE idNumber = @customerIdNo
					AND customerId <> @customerId
				)
		BEGIN
			SELECT @membershipId = membershipId
				,@email = email
			FROM customermaster
			WHERE idNumber = @customerIdNo
				AND customerId <> @customerId

			SELECT '1' ErrorCode
				,'ID already existing for customer ' + ISNULL(@membershipId, @email) Msg
				,@customerId id;

			RETURN
		END

		IF EXISTS (
				SELECT 1
				FROM customermaster
				WHERE (
						email = @custEmail
						OR customerEmail = @custEmail
						OR userName = @custEmail
						)
					AND customerId <> @customerId
				)
		BEGIN
			SELECT @membershipId = membershipId
			FROM customermaster
			WHERE (
					email = @custEmail
					OR customerEmail = @custEmail
					OR userName = @custEmail
					)
				AND customerId <> @customerId

			SET @errorMsg = 'email already used for existing customer ' + ISNULL(@membershipId, '');

			SELECT '1' ErrorCode
				,@errorMsg Msg
				,@customerId id;

			RETURN
		END

		
		--IF EXISTS (
		--    SELECT 1
		--    FROM customermaster
		--    WHERE 
		--        customerId <> @customerId
		--        AND (
		--            (LEN(@custMobile) = 10 AND (
		--                mobile = @custMobile OR
		--                CONCAT('+44', mobile) = @custMobile
		--            ))
		--            OR
		--            (LEN(@custMobile) = 13 AND (
		--                mobile = @custMobile OR
		--                @custMobile = CONCAT('+44', SUBSTRING(mobile, 3, 10))
		--            ))
		--            OR
		--            (LEN(@custMobile) = 12 AND (
		--                mobile = @custMobile OR
		--                @custMobile = CONCAT('44', mobile)
		--            ))
		--        )
		--)
		--BEGIN
		--    SELECT '1' AS ErrorCode,
		--           'Mobile No. ''' + @custMobile + ''' is already used' AS Msg,
		--           @custMobile AS id;
		
		--    RETURN;
		--END


		SET @onlineUser = CASE 
				WHEN @onlineUser = 'true'
					THEN 'Y'
				ELSE 'N'
				END
		SET @mobileUser = CASE 
				WHEN @mobileUser = 'true'
					THEN 'Y'
				ELSE 'N'
				END
		SET @fullName = ISNULL(@firstName, '') + ISNULL(' ' + @middleName, '') + ISNULL(' ' + @lastName1, '') + ISNULL(' ' + @lastName2, '')

		DECLARE @approvedBy VARCHAR(30)
			,@approvedDate VARCHAR(50)

		IF @streetunicode = 'Nnull'
			SET @streetunicode = NULL;

		--IF ISNULL(@street, '') <> ''
		--BEGIN
		--	SET @district = @street
		--	SET @street = rtrim(ltrim(replace(replace(cast(@streetunicode AS VARCHAR), '?', ''), '-', '')));

		--	SELECT @custCity = CITY_NAME
		--	FROM TBL_JAPAN_ADDRESS_DETAIL(NOLOCK)
		--	WHERE ROW_ID = @district
		--END
		--ELSE
		--BEGIN
		--	SELECT @custCity = CITY_NAME
		--		,@district = ROW_ID
		--	FROM TBL_JAPAN_ADDRESS_DETAIL(NOLOCK)
		--	WHERE ZIP_CODE = @zipCode
		--END

		EXEC PROC_CUSTOMERMODIFYLOG_NEW @flag = 'i-new'
			,@user = @user
			,@customerId = @customerId
			,@customerType = @customerType
			,@fullName = @fullName
			,@firstName = @firstName
			,@middleName = @middleName
			,@lastName1 = @lastName1
			,@country = @country
			,@zipCode = @zipCode
			,@state = @state
			,@street = @street
			,@custCity = @custCity
			,@cityUnicode = @cityUnicode
			,@streetUnicode = @streetUnicode
			,@custGender = @custGender
			,@custNativecountry = @custNativecountry
			,@dob = @custDOB
			,@email = @custEmail
			,@custTelNo = @custTelNo
			,@mobileNumber = @custMobile
			,@visaStatus = @visaStatus
			,@employeeBusinessType = @employeeBusinessType
			,@nameOfEmployeer = @nameOfEmployeer
			,@SSNNO = @SSNNO
			,@occupation = @occupation
			,@sourceOfFound = @sourceOfFound
			,@monthlyIncome = @monthlyIncome
			,@idType = @customerIdType
			,@idNumber = @customerIdNo
			,@issueDate = @custIdissueDate
			,@expiryDate = @custIdValidDate
			,@remittanceAllowed = @remittanceAllowed
			,@onlineUser = @onlineUser
			,@mobileUser = @mobileUser
			,@remarks = @remarks
			--used for customer type organisation      
			,@companyName = @companyName
			,@registerationNo = @registerationNo
			,@organizationType = @organizationType
			,@dateofIncorporation = @dateofIncorporation
			,@natureOfCompany = @natureOfCompany
			,@nameOfAuthorizedPerson = @nameOfAuthorizedPerson
			,@position = @position
			,@additionaladdress = @additionaladdress
			,@otherIdNumber = @customerOtherIdNo
			,@loginBranchId = @loginBranchId
			,@changedSecurityInfo = @changedSecurityInfo OUT
			,@RegistrationType = @registrationType

		--SELECT @approvedBy = approvedBy,@approvedDate = approvedDate FROM dbo.customerMaster WHERE customerid = @customerId      
		--IF @approvedBy IS NULL AND @approvedDate IS NULL       
		BEGIN
			--IF CUSTOMER IS NOT APPROVED YET DIRECT UPDATE MAIN TABLE      
			SELECT @createdDate = createdDate
			FROM customermaster
			WHERE customerId = @customerId;

			UPDATE dbo.customerMaster
			SET firstName = @firstName
				,middleName = CASE 
					WHEN @customerType = '4700'
						THEN @middleName
					ELSE NULL
					END
				,lastName1 = CASE 
					WHEN @customerType = '4700'
						THEN @lastName1
					ELSE NULL
					END
				,lastName2 = CASE 
					WHEN @customerType = '4700'
						THEN @lastName2
					ELSE NULL
					END
				,country = @country
				,[address] = @custAdd1
				,district = @district
				,city = @custCity
				,email = @custEmail
				,mobile = @custMobile
				,nativeCountry = @custNativecountry
				,occupation = CASE 
					WHEN @customerType = '4700'
						THEN @occupation
					ELSE NULL
					END
				,gender = CASE 
					WHEN @customerType = '4700'
						THEN @custGender
					ELSE NULL
					END
				,fullName = CASE 
					WHEN @customerType = '4700'
						THEN ISNULL(@firstName, '') + ISNULL(' ' + @middleName, '') + ISNULL(' ' + @lastName1, '') + ISNULL(' ' + @lastName2, '')
					ELSE @firstName
					END
				,telNo = @custTelNo
				,agentId = @agent
				,branchId = @branch
				,dob = CASE 
					WHEN @custDOB IS NOT NULL
						THEN @custDOB
					ELSE dob
					END
				,onlineUser = @onlineUser
				,mobileUser = @mobileUser
				,customerType = @customerType
				,isActive = 'Y'
				,modifiedBy = @user
				,modifiedDate = GETDATE()
				,idIssueDate = ISNULL(@custIdissueDate, idIssueDate) --new added by dhan      
				,idExpiryDate = ISNULL(@custIdValidDate, idExpiryDate)
				,idType = ISNULL(@customerIdType, idType)
				,idNumber = ISNULL(@customerIdNo, idNumber)
				,zipCode = @zipCode
				,
				--added New Field Value on 2018-12-28 --added by anoj      
				[state] = @state
				,sourceOfFund = @sourceOfFound
				,street = UPPER(@street)
				,additionalAddress = UPPER(@additionalAddress)
				,streetUnicode = CASE 
					WHEN @customerType = '4700'
						THEN UPPER(@streetUnicode)
					ELSE NULL
					END
				,cityUnicode = CASE 
					WHEN @customerType = '4700'
						THEN UPPER(@cityUnicode)
					ELSE NULL
					END
				,visaStatus = CASE 
					WHEN @customerType = '4700'
						THEN @visaStatus
					ELSE NULL
					END
				,employeeBusinessType = CASE 
					WHEN @customerType = '4700'
						THEN @employeeBusinessType
					ELSE NULL
					END
				,nameOfEmployeer = CASE 
					WHEN @customerType = '4700'
						THEN @nameOfEmployeer
					ELSE NULL
					END
				,SSNNO = CASE 
					WHEN @customerType = '4700'
						THEN @SSNNO
					ELSE NULL
					END
				,remittanceAllowed = CASE 
					WHEN @customerType = '4700'
						THEN @remittanceAllowed
					ELSE NULL
					END
				,remarks = CASE 
					WHEN @customerType = '4700'
						THEN @remarks
					ELSE NULL
					END
				,registerationNo = CASE 
					WHEN @customerType = '4700'
						THEN NULL
					ELSE @registerationNo
					END
				,organizationType = CASE 
					WHEN @customerType = '4700'
						THEN NULL
					ELSE @organizationType
					END
				,dateofIncorporation = CASE 
					WHEN @customerType = '4700'
						THEN NULL
					ELSE @dateofIncorporation
					END
				,natureOfCompany = CASE 
					WHEN @customerType = '4700'
						THEN NULL
					ELSE @natureOfCompany
					END
				,position = CASE 
					WHEN @customerType = '4700'
						THEN NULL
					ELSE @position
					END
				,nameOfAuthorizedPerson = CASE 
					WHEN @customerType = '4700'
						THEN NULL
					ELSE @nameOfAuthorizedPerson
					END
				,companyName = CASE 
					WHEN @customerType = '4700'
						THEN NULL
					ELSE @companyName
					END
				,monthlyIncome = @monthlyIncome
				,documentType = @docType
				,isCDDIUpdated = 1
				,occupationOther = @occupationOther
				,referralId = @referralId
				,otherIdNumber = @customerOtherIdNo
				,registrationType = @registrationType
			WHERE customerId = @customerId;

			DECLARE @walletAccountNo VARCHAR(20), @fullNameWallet VARCHAR(100);

			SELECT @walletAccountNo = walletAccountNo, 
			@fullNameWallet = CASE 
					WHEN @customerType = '4700'
						THEN ISNULL(@firstName, '') + ISNULL(' ' + @middleName, '') + ISNULL(' ' + @lastName1, '') + ISNULL(' ' + @lastName2, '')
					ELSE @fullName
					END
			FROM customermaster
			WHERE customerId = @customerId;

			--UPDATE FastMoneyPro_Account.dbo.ac_master
			--SET acct_name = @fullNameWallet where acct_num = @walletAccountNo

			SELECT '0' ErrorCode
				,'Customer data has been updated successfully.' Msg
				,@customerId id
				,ISNULL(cast(@customerId AS VARCHAR), '') + '|' + ISNULL(cast(@membershipId AS VARCHAR), '') + '|' + ISNULL(cast(cast(@createdDate AS DATE) AS VARCHAR), '') Extra;
				-- --//begin check to know security infor has been updated or not    
				-- CREATE TABLE #changeList (rowId int identity(1,1),columnName varchar(100))    
				-- DECLARE @changedColumns varchar(100),@changedValues varchar(200),@total int,@initial int = 1,@individualColumnName varchar(50)    
				-- DECLARE @idType VARCHAR (30),@idNumber VARCHAR(30),@idIssueDate VARCHAR(30),@idExpiryDate VARCHAR(30)    
				-- DECLARE @dynamicParameters varchar(100)    
				-- SET @idType   = @customerIdType    
				-- SET @idNumber   = @customerIdNo      
				-- SET @idIssueDate  = @custIdissueDate    
				-- SET @idExpiryDate  = @custIdValidDate    
				-- INSERT INTO #changeList    
				-- SELECT @customerId    
				-- INSERT INTO #changeList    
				-- SELECT VALUE  FROM dbo.Split(',', @changedSecurityInfo)    
				-- SELECT @total = count(*) FROM #changeList where columnName <> ''    
				-- WHILE (@total >= @initial)    
				-- BEGIN    
				-- IF @initial = 1    
				-- BEGIN    
				--   SET @changedColumns = 'customerId'    
				--   set @changedValues  = '''' +  @customerId  +''''    
				-- END    
				-- ELSE    
				-- BEGIN    
				--  --prepare column name    
				--  SELECT @individualColumnName = columnName from #changeList where rowid = @initial    
				--  SET @changedColumns = @changedColumns +','+ @individualColumnName    
				--  --prepare column value    
				--  set @individualColumnName = @individualColumnName    
				--  set @changedValues  = case when @individualColumnName = 'idType' then @changedValues +',' + '''' + @idType +''''    
				--              when @individualColumnName = 'idNumber' then @changedValues +','+ '''' + @idNumber +''''    
				--           when @individualColumnName = 'idIssueDate' then @changedValues +','+ '''' + @idIssueDate +''''    
				--           when @individualColumnName = 'idExpiryDate' then @changedValues +','+ '''' + @idExpiryDate +''''    
				--           else '' end    
				-- END    
				-- set @initial = @initial + 1    
				-- END    
				--set @sql = 'insert into customerMasterEditedDataMod (' + @changedColumns + ',modType)'    
				--+ 'values(' + @changedValues +',''u'')'    
				--print (@sql)    
				--exec(@sql)    
				--IF @total > 1    
				--BEGIN     
				-- SELECT  '0' ErrorCode ,      
				--       'Customer data has been updated successfull And is waiting for approval.' Msg ,      
				--       @customerId id;     
				--END    
				--ELSE    
				--BEGIN    
				-- SELECT  '0' ErrorCode ,      
				--       'Customer data has been updated successfully.' Msg ,      
				--       @customerId id;     
				--END    
		END
			--ELSE      
			--BEGIN      
			-- IF EXISTS(SELECT 'X' FROM dbo.customerMasterEditedDataMod WHERE customerid = @customerId)      
			-- BEGIN       
			--  UPDATE dbo.customerMasterEditedDataMod       
			--  SET     firstName = @firstName ,      
			--    middleName = CASE WHEN @customerType = '4700'      
			--          THEN @middleName      
			--          ELSE NULL      
			--        END ,      
			--    lastName1 = CASE WHEN @customerType = '4700'      
			--         THEN @lastName1      
			--         ELSE NULL      
			--       END ,      
			--    lastName2 = CASE WHEN @customerType = '4700'      
			--         THEN @lastName2      
			--         ELSE NULL      
			--       END ,      
			--    country = @country ,      
			--    [address] = @custAdd1 ,      
			--    district = @district ,      
			--    city = @custCity ,      
			--    email = @custEmail ,      
			--    mobile = @custMobile ,      
			--    nativeCountry = @custNativecountry ,      
			--    occupation = CASE WHEN @customerType = '4700'      
			--          THEN @occupation      
			--          ELSE NULL      
			--        END ,      
			--    gender = CASE WHEN @customerType = '4700'      
			--         THEN @custGender      
			--         ELSE NULL      
			--       END ,      
			--    fullName = CASE WHEN @customerType = '4700'      
			--           THEN ISNULL(@firstName, '') + ISNULL(' '      
			--               + @middleName,      
			--               '') + ISNULL(' '      
			--               + @lastName1, '')      
			--          + ISNULL(' ' + @lastName2, '')      
			--           ELSE @firstName      
			--         END,      
			--    telNo = @custTelNo ,      
			--    agentId = @agent ,      
			--    branchId = @branch ,      
			--    dob = CASE WHEN @custDOB IS NOT NULL THEN @custDOB      
			--         ELSE dob      
			--       END ,      
			--    onlineUser = @onlineUser,      
			--    customerType = @customerType ,      
			--    isActive = 'Y' ,      
			--    modifiedBy = @user ,      
			--    modifiedDate = GETDATE() ,      
			--    idIssueDate = ISNULL(@custIdissueDate, idIssueDate) --new added by dhan      
			--    ,      
			--    idExpiryDate = ISNULL(@custIdValidDate, idExpiryDate) ,      
			--    idType = ISNULL(@customerIdType, idType) ,      
			--    idNumber = ISNULL(@customerIdNo, idNumber) ,      
			--    zipCode = @zipCode ,      
			--    postalCode = @custPostal ,      
			--   --added New Field Value on 2018-12-28 --added by anoj      
			--    [state] = @state ,      
			--    sourceOfFund = @sourceOfFound,      
			--    street = @street ,      
			--    streetUnicode = CASE WHEN @customerType = '4700'      
			--          THEN @streetUnicode      
			--          ELSE NULL      
			--        END ,      
			--    cityUnicode = CASE WHEN @customerType = '4700'      
			--           THEN @cityUnicode      
			--           ELSE NULL      
			--         END ,      
			--    visaStatus = CASE WHEN @customerType = '4700'      
			--          THEN @visaStatus      
			--          ELSE NULL      
			--        END ,      
			--    employeeBusinessType = CASE WHEN @customerType = '4700'      
			--           THEN @employeeBusinessType      
			--           ELSE NULL      
			--            END ,      
			--    nameOfEmployeer = CASE WHEN @customerType = '4700'      
			--            THEN @nameOfEmployeer      
			--            ELSE NULL      
			--          END ,      
			--    SSNNO = CASE WHEN @customerType = '4700' THEN @SSNNO      
			--        ELSE NULL      
			--      END ,      
			--    remittanceAllowed = CASE WHEN @customerType = '4700'      
			--           THEN @remittanceAllowed      
			--           ELSE NULL      
			--         END ,      
			--    remarks = CASE WHEN @customerType = '4700'      
			--          THEN @remarks      
			--          ELSE NULL      
			--        END ,      
			--    registerationNo = CASE WHEN @customerType = '4700'      
			--            THEN NULL      
			--            ELSE @registerationNo      
			--          END ,      
			--    organizationType = CASE WHEN @customerType = '4700'      
			--          THEN NULL      
			--          ELSE @organizationType      
			--           END ,      
			--    dateofIncorporation = CASE WHEN @customerType = '4700'      
			--             THEN NULL      
			--             ELSE @dateofIncorporation      
			--           END ,      
			--    natureOfCompany = CASE WHEN @customerType = '4700'      
			--            THEN NULL      
			--            ELSE @natureOfCompany      
			--          END ,      
			--    position = CASE WHEN @customerType = '4700' THEN NULL      
			--        ELSE @position      
			--         END ,      
			--    nameOfAuthorizedPerson = CASE WHEN @customerType = '4700'      
			--             THEN NULL      
			--             ELSE @nameOfAuthorizedPerson      
			--           END ,      
			--    companyName = CASE WHEN @customerType = '4700'      
			--           THEN NULL      
			--           ELSE @companyName      
			--         END,      
			--    monthlyIncome =@monthlyIncome      
			--  WHERE   customerId = @customerId;      
			--  SELECT  '0' ErrorCode ,      
			--     'Customer data has been updated successfully and is waiting for approval' Msg ,      
			--     @customerId id;        
			-- END       
			-- ELSE      
			-- BEGIN      
			--  INSERT  INTO customerMasterEditedDataMod(      
			--   customerId,firstName,middleName,lastName1,lastName2,country,[address],zipCode,district,city,email,workPhone,mobile,      
			--   nativeCountry,dob,placeOfIssue,occupation,isBlackListed,lastTranId,relationId,relativeName,gender,salaryRange,address2,      
			--   fullName,      
			--   createdBy,createdDate,postalCode,idIssueDate,idExpiryDate,idType,idNumber,telNo,agentId,branchId,onlineUser,      
			--   ipAddress,customerType,isActive,verifiedBy,verifiedDate,isForcedPwdChange,bankName,bankAccountNo,HasDeclare,      
			--   membershipId,[state],sourceOfFund,street,streetUnicode,cityUnicode,visaStatus,employeeBusinessType,nameOfEmployeer,SSNNO,      
			--   remittanceAllowed,remarks,registerationNo,organizationType,dateofIncorporation,natureOfCompany,position,nameOfAuthorizedPerson,monthlyIncome,[modType]      
			--  )      
			--  VALUES (      
			--   @customerId,@firstName,@middleName,@lastName1,@lastName2,@country,@custAdd1,ISNULL(@zipCode, @custPostal),@district,@custCity,@custEmail,@workPhone,@custMobile,      
			--   @custNativecountry,@custDOB,@placeOfIssue,@occupation,@isBlackListed,@lastTranId,@relationId,@relativeName,@custGender,@custSalary,@custAdd2,      
			--   ISNULL(@firstName, '') + ISNULL(' '+ @middleName,'') + ISNULL(' '+ @lastName1, '')+ ISNULL(' ' + @lastName2, ''),      
			--   @user,DATEADD(HH, 0, GETUTCDATE()),@custPostal,@custIdissueDate,@custIdValidDate,@customerIdType,@customerIdNo,@custTelNo,@agent,@branch,@onlineUser,      
			--   @ipAddress,@customerType,'Y',@user,GETDATE(),'1',@bankId,@accountNumber,@HasDeclare,---- New Added Values      
			--   @membershipId,@state,@sourceOfFound,@street,@streetUnicode,@cityUnicode,@visaStatus,@employeeBusinessType,@nameOfEmployeer,@SSNNO,      
			--   @remittanceAllowed,@remarks,@registerationNo,CASE WHEN @customerType = '4700'      
			--          THEN NULL      
			--          ELSE @organizationType      
			--           END,@dateofIncorporation,CASE WHEN @customerType = '4700'      
			--            THEN NULL      
			--            ELSE @natureOfCompany      
			--          END,@position,@nameOfAuthorizedPerson,@monthlyIncome,'u'      
			--  )      
			--  SELECT  '0' ErrorCode ,      
			--    'Customer data has been edited successfully and is waiting for approval' Msg ,      
			--    @customerId id;        
			-- END       
			--END      
	END

	IF @flag = 'customer-update-core'
	BEGIN
		SET @onlineUser = CASE 
				WHEN @onlineUser = 'true'
					THEN 'Y'
				ELSE 'N'
				END

		DECLARE @userType VARCHAR(5);

		SELECT @userType = userType
		FROM applicationUsers(NOLOCK)
		WHERE userName = @user;

		IF EXISTS (
				SELECT 'a'
				FROM dbo.customerMaster(NOLOCK)
				WHERE customerId = @customerId
					AND approvedBy IS NOT NULL
					AND @userType <> 'HO'
				)
		BEGIN
			SELECT '1' ErrorCode
				,'Customer is already approved, you can not modify this customer data.' Msg
				,@customerId id;

			RETURN;
		END;

		--LOG FOR CUSTOMER UPDATE      
		EXEC PROC_CUSTOMERMODIFYLOG @flag = 'i'
			,@email = @custEmail
			,@idNumber = @customerIdNo
			,@bank = @bankId
			,@accNumber = @accountNumber
			,@customerId = @customerId
			,@mobileNumber = @custMobile
			,@user = @user;

		UPDATE dbo.customerMaster
		SET firstName = @firstName
			,middleName = middleName
			,lastName1 = @lastName1
			,lastName2 = @lastName2
			,country = @country
			,[address] = @custAdd1
			,zipCode = ISNULL(@zipCode, @custPostal)
			,district = @district
			,city = @custCity
			,email = @custEmail
			--,homePhone = @customerIdNo      
			,workPhone = @workPhone
			,mobile = @custMobile
			,nativeCountry = @custNativecountry
			,dob = @custDOB
			,placeOfIssue = @placeOfIssue
			,occupation = @occupation
			,isBlackListed = @isBlackListed
			,lastTranId = @lastTranId
			,relationId = @relationId
			,relativeName = @relativeName
			,gender = @custGender
			,companyName = @companyName
			,salaryRange = @custSalary
			,address2 = @custAdd2
			,fullName = ISNULL(@firstName, '') + ISNULL(' ' + @middleName, '') + ISNULL(' ' + @lastName1, '') + ISNULL(' ' + @lastName2, '')
			,
			--postalCode = @custPostal ,      
			idIssueDate = @custIdissueDate
			,idExpiryDate = @custIdValidDate
			,idType = @customerIdType
			,idNumber = @customerIdNo
			,telNo = @custTelNo
			,agentId = @agent
			,branchId = @branch
			,onlineUser = @onlineUser
			,customerType = @customerType
			,isActive = 'Y'
			,bankName = @bankId
			,modifiedBy = @user
			,modifiedDate = GETDATE()
			,bankAccountNo = @accountNumber
			,HasDeclare = @HasDeclare
			,mobileUser = @mobileUser
		WHERE customerId = @customerId;

		SELECT '0' ErrorCode
			,'Customer has been updated successfully.' Msg
			,@customerId id;
	END;

	IF @flag = 'verify-customer-details'
	BEGIN
		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 ROW_NUMBER() OVER (
				ORDER BY [customerId] ASC
				) SN
			,cm.walletAccountNo AS [walletNumber]
			,cm.customerId
			,cm.createdDate
			,CM.membershipId
			,ISNULL(email, '-') email
			,cm.fullName
			,cm.firstname
			,cm.middlename
			,cm.lastname1
			,sdg.detailTitle AS [gender]
			,cmb.countryName AS [country]
			,cmn.countryName AS [nativeCountry]
			,ISNULL(cm.homePhone, '-') homePhone
			,cm.postalCode
			,[address] = cm.address --dbo.FNAGetCustomerAddress(cm.customerId, 'CUST-SEARCH-DETAILS')
			,cm.city
			,cm.zipcode
			,ISNULL(COALESCE(cm.telNo, cm.homePhone), '-') telNo
			,ISNULL(cm.mobile, '-') mobile
			,sdo.detailTitle AS [occupation]
			,CASE 
				WHEN cm.idtype = '11402'
					THEN cm.otherIdNumber
				ELSE sdi.detailTitle
				END AS [idType]
			,cm.idType AS [idTypeCode]
			,UPPER(cm.idNumber) idNumber
			,cm.verifyDoc1
			,cm.verifyDoc2
			,cm.verifyDoc3
			,CONVERT(VARCHAR(10), dob, 121) AS [dob]
			,CONVERT(VARCHAR(10), idIssueDate, 121) AS [idIssueDate]
			,CONVERT(VARCHAR(10), idExpiryDate, 121) AS [idExpiryDate]
			,bl.BankName
			,bl.bankCode
			,cm.bankAccountNo
			,cm.walletAccountNo
			,cm.homePhone
			,cm.availableBalance
			,verifyDoc4 = SelfieDoc
			,cm.obpId
			,cm.AuditDate
			,customerType = TYP.detailTitle
			,cm.customerStatus
			,CONVERT(VARCHAR(6), cm.dob, 12) AS [dobYMD]
			,CASE 
				WHEN gender = '97'
					THEN '7'
				WHEN gender = '98'
					THEN '8'
				END AS [genderCode]
			,CASE 
				WHEN nativeCountry = '238'
					THEN '1'
				WHEN nativeCountry = '113'
					THEN '2'
				WHEN nativeCountry = '45'
					THEN '3'
				ELSE '4'
				END AS [nativeCountryCode]
			,doc.detailTitle AS DocumentType
			,visa.detailTitle VisaStatus
			,businessType.detailTitle EmployeeBusinessType
			,ISNULL(cm.NameOfEmployeer, '-') NameOfEmployeer
			,ISNULL(cm.SsnNo, '-') SsnNo
			,sourceOfFund.detailTitle SourceOfFund
			,ISNULL(cm.MonthlyIncome, '-') MonthlyIncome
			,ISNULL(cm.Remarks, '-') Remarks
			,CASE 
				WHEN cm.RemittanceAllowed = '1'
					THEN 'Yes'
				ELSE 'No'
				END RemittanceAllowed
			,CASE 
				WHEN cm.OnlineUser = 'Y'
					THEN 'Yes'
				ELSE 'No'
				END OnlineUser
			,CASE 
				WHEN cm.mobileUser = 'Y'
					THEN 'Yes'
				ELSE 'No'
				END MobileUser
			,cm.createdFrom
			,cm.otherIdNumber
			,sdi.valueId [idTypeValue]
		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 staticDataValue doc(NOLOCK) ON doc.valueId = cm.documentType
		LEFT JOIN KoreanBankList bl(NOLOCK) ON cm.bankName = bl.rowId
		LEFT JOIN countryStateMaster CSM(NOLOCK) ON CSM.stateId = CAST(cm.STATE AS VARCHAR)
		LEFT JOIN StaticDataValue visa(NOLOCK) ON visa.valueId = cm.visaStatus
		LEFT JOIN StaticDataValue businessType(NOLOCK) ON businessType.valueId = cm.employeeBusinessType
		LEFT JOIN StaticDataValue sourceOfFund(NOLOCK) ON sourceOfFund.valueId = cm.sourceOfFund
		LEFT JOIN tbl_japan_address_detail detail(NOLOCK) ON detail.zip_code = cm.zipcode
		WHERE customerId = @customerId;

		-- get matched data
	
		SELECT @fullName = fullName , @registrationType = RegistrationType
		FROM customerMaster cm(NOLOCK)
		WHERE customerId = @customerId

		CREATE TABLE #TEMP_ID_match (ID BIGINT)

		INSERT INTO #TEMP_ID_match
		SELECT CAST(value AS BIGINT)
		FROM [dbo].[Split](',', DBO.GET_MATCHED_ID(@fullName, @customerId, @registrationType))

		CREATE TABLE #TEMP (
			CUSTOMERID VARCHAR(100)
			,[NAME] VARCHAR(100)
			,DOB DATE
			,IDNUMBER VARCHAR(50)
			)

		INSERT INTO #TEMP
		SELECT customerid
			,fullName
			,CAST(dob AS DATE)
			,idNumber
		FROM CustomerMaster cm(NOLOCK)
		INNER JOIN #TEMP_ID_match T ON CM.customerid = t.id
		WHERE customerid <> @customerid

		SELECT ROW_NUMBER() OVER (
				ORDER BY [NAME] ASC
				) SN
			,*
		FROM #TEMP

		SELECT 'X'

		 EXEC proc_online_core_customerSetup @flag = 'ofac-list' , @customerid = @customerId
	END;

	IF @flag = 'get-matched-data'
	BEGIN
		SELECT @fullName = fullName , @registrationType  = RegistrationType
		FROM customerMaster cm(NOLOCK)
		WHERE customerId = @customerId

		CREATE TABLE #TEMP_ID (ID BIGINT)

		INSERT INTO #TEMP_ID
		SELECT CAST(value AS BIGINT)
		FROM [dbo].[Split](',', DBO.GET_MATCHED_ID(@fullName, @customerId,@registrationType))

		CREATE TABLE #TEMP1 (
			CUSTOMERID VARCHAR(100)
			,[NAME] VARCHAR(100)
			,DOB DATE
			,IDNUMBER VARCHAR(50)
			)

		INSERT INTO #TEMP1
		SELECT customerid
			,fullName
			,CAST(dob AS DATE)
			,idNumber
		FROM CustomerMaster cm(NOLOCK)
		INNER JOIN #TEMP_ID T ON CM.customerid = t.id
		WHERE customerid <> @customerid

		SELECT ROW_NUMBER() OVER (
				ORDER BY [NAME] ASC
				) SN
			,*
		FROM #TEMP1
	END;
	
	IF @flag = 'ofac-list'
	BEGIN
			-- START OFAC Checking     
			DECLARE @checkNameBy VARCHAR(10)
				,@matchBy CHAR(1)

			SELECT @checkNameBy = TRACK_BY
			FROM OFACSETTING(NOLOCK)

			IF @checkNameBy IS NULL
				SET @checkNameBy = 'OFAC'

			SELECT @fullName = fullName FROM customerMaster(nolock) where customerId = @customerId
				
			DECLARE @matchPercentage INT
			
			SELECT @matchPercentage = MATCH_PERCENTAGE
			FROM OFACSETTING

			IF @checkNameBy = 'OFAC'
			BEGIN
				EXEC proc_ofacTracker @flag = 't'
					,@name = @fullName
					,@Result = @ofacRes OUTPUT
			END	
			ELSE
			BEGIN
				SELECT @ofacRes = Dowjones.[dbo].[DJ_GET_SUSPICIOUS_NAME_MATCH_IDS_V2](@fullName)
			END

			

			DECLARE @result VARCHAR(MAX)

			IF ISNULL(@ofacRes, '') <> ''
			BEGIN
				SET @matchBy = 'S'
				SET @ofacReason = 'Matched by  name'
			END

			IF (@ofacRes <> '')
			BEGIN
				IF @checkNameBy = 'OFAC'
				BEGIN
					SET @result = @ofacRes + '|' + ISNULL(@ofacReason, '')

					SELECT errorCode = 100
						,msg = 'WARNING!!! This customer is listed on OFAC List'
						,id = @result
						,extra = @checkNameBy

					EXEC proc_sendPageLoadData @flag = 'ofac'
						,@user = @user
						,@blackListIds = @ofacRes
				END
				ELSE
				BEGIN
					SET @result = @ofacRes + '|' + ISNULL(@ofacReason, '')

					SELECT errorCode = 100
						,msg = 'WARNING!!! This customer is listed on DJ List'
						,id = @result
						,extra = @checkNameBy

					EXEC proc_sendPageLoadData @flag = 'ofac'
						,@user = @user
						,@blackListIds = @ofacRes
						,@TRACK_BY = 'DJ'
				END
				SELECT firstname firstName, ISNULL(middleName,'') middleName, lastName1 FROM customerMaster(nolock) where customerId = @customerId
			END

			
			
	END


	IF @flag = 'verify-customer-agent'
	BEGIN
		UPDATE dbo.customerMaster
		SET verifiedDate = GETDATE()
			,verifiedBy = @user
		WHERE customerId = @customerId;

		SELECT '0' ErrorCode
			,'Customer verified successfully.' Msg
			,@customerId id;
	END;

	IF @flag = 'approve-customer-admin'
	BEGIN
		UPDATE dbo.customerMaster
		SET approvedDate = GETDATE()
			,approvedBy = @user
		WHERE customerId = @customerId;

		SELECT '0' ErrorCode
			,'Customer approved successfully.' Msg
			,@customerId id;
	END;

	IF @flag = 'verify-pending'
	BEGIN
		UPDATE dbo.customerMaster
		SET verifiedDate = GETDATE()
			,verifiedBy = @user
		WHERE customerId = @customerId;

		SELECT '0' ErrorCode
			,'Customer verified successfully.' Msg
			,@customerId id;
	END;

	IF @flag = 'approve-pending'
	BEGIN
		UPDATE dbo.customerMaster
		SET approvedDate = GETDATE()
			,approvedBy = @user
			,customerStatus = 'V'
			,userName = email
		WHERE customerId = @customerId;

		SELECT username = ISNULL(cm.userName, '')
			,[password] = LOWER(dbo.FNADecryptString(cm.customerPassword))
			,[channel] = LOWER(ISNULL(cm.createdBy, ''))
			,[account] = ISNULL(cm.membershipId, '')
			,walletAccountNo
			,bankAccountNo
			,fullName
			,CustomerBankName
			,printLetter = CASE 
				WHEN ISNULL(serviceUsedFor, 'C') = 'C'
					THEN 'N'
				ELSE 'Y'
				END
		FROM dbo.customerMaster cm(NOLOCK)
		WHERE cm.customerId = @customerId;

		SELECT '0' ErrorCode
			,'Customer verified successfully.' Msg
			,@customerId id;

		RETURN;
	END;

	--@Max : 2018.09       
	IF @flag = 'kj-modificationList'
	BEGIN
		SELECT k.bankCode
			,bankAccountNo
			,walletAccountNo
			,obpId
			,CustomerBankName
			,c.idType
			,c.idNumber
			,CONVERT(VARCHAR(6), c.dob, 12) AS [dobYMD]
			,CASE 
				WHEN c.gender = '97'
					THEN '7'
				WHEN c.gender = '98'
					THEN '8'
				END AS [genderCode]
			,CASE 
				WHEN c.nativeCountry = '238'
					THEN '1'
				WHEN c.nativeCountry = '113'
					THEN '2'
				WHEN c.nativeCountry = '45'
					THEN '3'
				ELSE '4'
				END AS [nativeCountryCode]
		FROM customerMaster c(NOLOCK)
		INNER JOIN KoreanBankList k(NOLOCK) ON k.rowId = c.bankName
		WHERE customerId = @customerId;

		RETURN;
	END;

	IF @flag = 'kj-modification'
	BEGIN
		UPDATE customerMaster
		SET CustomerBankName = @fullName
		WHERE customerId = @customerId;

		SELECT '0' ErrorCode
			,'Customer updated successfully.' Msg
			,@customerId id;

		RETURN;
	END;

	IF @flag = 'customerdetail'
	BEGIN
		SELECT firstName
			,[address] = dbo.FNAGetCustomerAddress(cm.customerId, '')
			,mobile
			,fullName
			,nativeCoun.countryName nativeCountry
			,sv.detailTitle idType
			,cm.idNumber
			,customerDetail = CASE 
				WHEN ISNULL(cm.createdFrom, 'C') = 'C'
					THEN 'Counter'
				ELSE 'Mobile'
				END + '/' + CASE ISNULL(serviceUsedFor, 'C')
				WHEN 'C'
					THEN 'Counter Visit Only'
				WHEN 'CM'
					THEN 'Counter and Mobile'
				WHEN 'M'
					THEN 'Mobile'
				WHEN 'CMO'
					THEN 'Counter, Mobile and Online'
				ELSE 'Counter Visit Only'
				END
		FROM dbo.customerMaster cm(NOLOCK)
		LEFT JOIN dbo.countryStateMaster csm(NOLOCK) ON csm.stateId = cm.STATE
		LEFT JOIN dbo.countryMaster country(NOLOCK) ON country.countryId = cm.country
		LEFT JOIN dbo.countryMaster nativeCoun(NOLOCK) ON nativeCoun.countryId = cm.nativeCountry
		LEFT JOIN dbo.staticDataValue sv(NOLOCK) ON sv.valueId = cm.idType
		WHERE customerId = @customerId;
	END;

	IF @flag = 'customerdetailForEnableDisable'
	BEGIN
		IF @sortBy IS NULL
			SET @sortBy = 'createdDate';

		IF @sortOrder IS NULL
			SET @sortOrder = 'DESC';
		SET @table = 
			'(      
      SELECT  firstName ,      
      cm.customerId,      
      convert(varchar(10),cm.dob ,121) dob,      
      [address]= dbo.FNAGetCustomerAddress(cm.customerId, '''') ,      
         mobile ,      
         fullName,      
      nativeCoun.countryName nativeCountry,      
      sv.detailTitle idType,      
      cm.idNumber,      
      ISNULL(cm.isactive, ''Y'') isactive,      
      case when ISNULL(cm.isActive,''Y'') = ''Y'' then ''Enabled'' else ''Disabled'' end status      
	  ,case when ISNULL(cm.createdFrom, ''C'') = ''C'' THEN ''CORE'' WHEN cm.createdFrom = ''M'' THEN ''MOBILE'' ELSE cm.createdFrom END createdFrom
      FROM    dbo.customerMaster cm (NOLOCK)      
      LEFT JOIN dbo.countryStateMaster csm (NOLOCK) ON csm.stateId=cm.state      
      LEFT JOIN dbo.countryMaster country (NOLOCK) ON country.countryId =cm.country      
      LEFT JOIN dbo.countryMaster nativeCoun (NOLOCK) ON nativeCoun.countryId =cm.nativeCountry      
      LEFT JOIN dbo.staticDataValue sv(NOLOCK) ON sv.valueId=cm.idType      
      WHERE   customerId = ''' 
			+ @customerId + '''      
      ) x';
		SET @select_field_list = 'customerId,status,dob,address,fullName,idType,idNumber,nativeCountry,mobile,isactive,createdFrom';

		EXEC dbo.proc_paging @table
			,@sql_filter
			,@select_field_list
			,@extra_field_list
			,@sortBy
			,@sortOrder
			,@pageSize
			,@pageNumber;

		RETURN;
	END;

	IF @flag = 'customerforaccept'
	BEGIN
		IF @sortBy IS NULL
			SET @sortBy = 'createdDate';

		IF @sortOrder IS NULL
			SET @sortOrder = 'DESC';
		SET @table = '(      
      SELECT  isnull(firstName,'''') firstname,      
      cm.customerId,      
      convert(varchar(10),cm.dob ,121) dob,      
      [address]= dbo.FNAGetCustomerAddress(cm.customerId, '''') ,      
         mobile ,      
         fullName,      
      nativeCoun.countryName nativeCountry,      
      sv.detailTitle idType,      
      cm.idNumber,      
      ISNULL(cm.isactive, ''Y'') isactive     
    
      FROM    dbo.customerMaster cm (NOLOCK)      
      LEFT JOIN dbo.countryStateMaster csm (NOLOCK) ON csm.stateId=cm.state      
      LEFT JOIN dbo.countryMaster country (NOLOCK) ON country.countryId =cm.country      
      LEFT JOIN dbo.countryMaster nativeCoun (NOLOCK) ON nativeCoun.countryId =cm.nativeCountry      
      LEFT JOIN dbo.staticDataValue sv(NOLOCK) ON sv.valueId=cm.idType      
      WHERE   customerId = ''' + @customerId + '''      
      ) x';
		SET @select_field_list = 'customerId,dob,address,fullName,idType,idNumber,nativeCountry,mobile,isactive';

		EXEC dbo.proc_paging @table
			,@sql_filter
			,@select_field_list
			,@extra_field_list
			,@sortBy
			,@sortOrder
			,@pageSize
			,@pageNumber;

		RETURN;
	END;

	IF @flag = 'fileUpload'
	BEGIN
		UPDATE dbo.customerMaster
		SET verifyDoc1 = CASE 
				WHEN @verifyDoc1 IS NOT NULL
					THEN @verifyDoc1
				ELSE verifyDoc1
				END
			,verifyDoc2 = CASE 
				WHEN @verifyDoc2 IS NOT NULL
					THEN @verifyDoc2
				ELSE verifyDoc2
				END
			,verifyDoc3 = CASE 
				WHEN @verifyDoc3 IS NOT NULL
					THEN @verifyDoc3
				ELSE verifyDoc3
				END
			,SelfieDoc = CASE 
				WHEN @verifyDoc4 IS NOT NULL
					THEN @verifyDoc4
				ELSE SelfieDoc
				END
		WHERE customerId = @customerId;

		SELECT '0' ErrorCode
			,'Customer has been updated successfully.' Msg
			,@customerId id;
	END;

	IF @FLAG = 'delete'
	BEGIN
		IF EXISTS (
				SELECT TOP 1 1
				FROM TRANSENDERS S(NOLOCK)
				WHERE S.CUSTOMERID = @rowid
				)
		BEGIN
			SELECT 1 ErrorCode
				,'You can not delete this customer, customer has already done transaction!' Msg
				,@rowid id

			RETURN
		END

		IF EXISTS (
				SELECT TOP 1 1
				FROM TRANSENDERStemp S(NOLOCK)
				WHERE S.CUSTOMERID = @rowid
				)
		BEGIN
			SELECT 1 ErrorCode
				,'You can not delete this customer, customer has already done transaction!' Msg
				,@rowid id

			RETURN
		END

		--IF EXISTS (
		--		SELECT *
		--		FROM CUSTOMERMASTER(NOLOCK)
		--		WHERE CUSTOMERID = @rowid
		--			AND APPROVEDDATE IS NOT NULL
		--		)
		--BEGIN
		--	SELECT 1 ErrorCode
		--		,'You can not delete this customer, customer has already been approved!' Msg
		--		,@rowid id

		--	RETURN
		--END

		--select * from customermasterdeleted      
		INSERT INTO customermasterdeleted
		SELECT membershipId
			,firstName
			,middleName
			,lastName1
			,lastName2
			,country
			,address
			,STATE
			,zipCode
			,district
			,city
			,email
			,homePhone
			,workPhone
			,mobile
			,nativeCountry
			,dob
			,placeOfIssue
			,customerType
			,occupation
			,CASE WHEN @deleteReason IS NULL THEN '' ELSE 'Y' END
			,createdBy
			,createdDate
			,modifiedBy
			,modifiedDate
			,approvedBy
			,approvedDate
			,isDeleted
			,lastTranId
			,relationId
			,relativeName
			,address2
			,fullName
			,postalCode
			,idExpiryDate
			,idType
			,idNumber
			,telNo
			,companyName
			,gender
			,salaryRange
			,bonusPointPending
			,Redeemed
			,bonusPoint
			,todaysSent
			,todaysNoOfTxn
			,agentId
			,branchId
			,memberIDissuedDate
			,memberIDissuedByUser
			,memberIDissuedAgentId
			,memberIDissuedBranchId
			,totalSent
			,idIssueDate
			,onlineUser
			,customerPassword
			,customerStatus
			,isActive
			,islocked
			,sessionId
			,lastLoginTs
			,howDidYouHear
			,ansText
			,ansEmail
			,state2
			,ipAddress
			,marketingSubscription
			,paidTxn
			,firstTxnDate
			,verifyDoc1
			,verifyDoc2
			,verifiedBy
			,verifiedDate
			,verifyDoc3
			,isForcedPwdChange
			,bankName
			,bankAccountNo
			,walletAccountNo
			,availableBalance
			,obpId
			,CustomerBankName
			,referelCode
			,isEmailVerified
			,verificationCode
			,SelfieDoc
			,HasDeclare
			,AuditDate
			,AuditBy
			,SchemeStartDate
			,invalidAttemptCount
			,sourceOfFund
			,street
			,streetUnicode
			,cityUnicode
			,visaStatus
			,employeeBusinessType
			,nameOfEmployeer
			,SSNNO
			,remittanceAllowed
			,remarks
			,registerationNo
			,organizationType
			,dateofIncorporation
			,natureOfCompany
			,position
			,nameOfAuthorizedPerson
			,monthlyIncome
			,ADDITIONALADDRESS
			,@user
			,GETDATE()
			,customerId
			,mobileUser
			,agreeYn
			,customerEmail
			,anotherIdType
			,anotherIDNumber
			,anotherIDIssueDate
			,anotherIDExpiryDate
			,mobileApprovedBy
			,mobileApprovedDate
			,mobileVerificationType
			,verifyRemarks
			,ISNULL(ofacRemarks,ofacRemarks)
			,isOfac
			,LawsonCardNo
			,RegistrationType
			,TrustDocId
		FROM customerMaster
		WHERE customerId = @rowid

		DELETE
		FROM customerMaster
		WHERE customerid = @rowid
		BEGIN
			EXEC proc_applicationLogs @flag = 'i'
				,@logType = 'Delete'
				,@tableName = 'CustomerMaster'
				,@dataId = @rowid
				,@oldData = 'Deleted customer was registered from core!!'
				,@newData = 'APPROVE REJECT'
				,@module = '50'
				,@user = @user
		END

		SELECT 0 ErrorCode
			,'Customer Deleted Successfully' Msg
			,@rowid id



		SELECT @email = username
		FROM mobile_userRegistration
		WHERE customerId = @customerId

		DELETE
		FROM CUSTOMERMASTER
		WHERE CUSTOMERID = @customerId

		DELETE
		FROM CUSTOMERMASTERTEMP
		WHERE CUSTOMERID = @customerId
			AND username = @email

		DELETE
		FROM mobile_userRegistration
		WHERE CUSTOMERID = @customerId
			AND username = @email and customerId = @customerId

		--	SELECT * FROM CUSTOMER_PROMOTION
		UPDATE Customer_Promotion SET [status] = 2 WHERE destinationCustomerId =  @customerId AND codeType = 'REGISTRATION'
		-- BEGIN
			UPDATE TBL_MOBILE_OTP_REQUEST
			SET IS_EXPIRED = '1'
				,IS_SUCCESS = '0'
				,MODIFIED_DATE = GETDATE()
			WHERE USER_ID = @email
				AND REQUEST_FOR = 'REGISTER'
		-- END

		SET @oldValue = CASE WHEN @ofacReason IS NULL THEN '' ELSE 'OFAC' END

		BEGIN
			EXEC proc_applicationLogs @flag = 'i'
				,@logType = 'Delete'
				,@tableName = 'CustomerMaster'
				,@dataId = @customerId
				,@oldData = @oldValue
				,@newData = 'OFAC REJECT'
				,@module = '40'
				,@user = @user
		END

		SELECT 0 ErrorCode
			,'Customer Deleted Successfully' Msg
			,@customerId id

	END

	IF @FLAG = 'reject'
	BEGIN
		IF EXISTS (
				SELECT *
				FROM CUSTOMERMASTER(NOLOCK)
				WHERE CUSTOMERID = @rowid
					AND approvedDate IS NOT NULL
				)
		BEGIN
			SELECT 1 ErrorCode
				,'Existing customer cannot be rejected. Please proceed to disable mobile login.' Msg
				,@rowid id

			RETURN
		END

		IF EXISTS (
				SELECT *
				FROM mobile_userRegistration(NOLOCK)
				WHERE CUSTOMERID = @rowid
				)
			-- AND APPROVEDDATE IS NOT NULL
		BEGIN
			UPDATE mobile_userRegistration
			SET isDeleted = 'Y'
				,deletedBy = @user
				,deletedDate = GETDATE()
			WHERE customerid = @rowid
				--  RETURN
		END

		IF EXISTS (
				SELECT *
				FROM CUSTOMERMASTER(NOLOCK)
				WHERE CUSTOMERID = @rowid
					AND APPROVEDDATE IS NULL
				)
		BEGIN
			UPDATE customerMaster
			SET isActive = 'N'
				,deletedBy = @user
				,deletedDate = GETDATE()
			WHERE customerid = @rowid
		END

			UPDATE Customer_Promotion SET [status] = 2 WHERE destinationCustomerId =  @customerId AND codeType = 'REGISTRATION'
		SELECT 0 ErrorCode
			,'Customer Rejected Successfully' Msg
			,@rowid id
	END

	IF @FLAG = 'getDetailsOfSendRequest'
	BEGIN
		--SELECT @customerId = CM.CUSTOMERID FROM SEND_MONEY_REQUEST SEQ(nolock)    
		--INNER JOIN CUSTOMERMASTER CM (NOLOCK) ON CM.MEMBERSHIPID = SEQ.MEMBERSHIPID    
		-- WHERE ROWID = @rowid    
		SELECT cm.walletAccountNo AS [walletNumber]
			,cm.customerId
			,seq.createdDate
			,CM.membershipId
			,ISNULL(email, '-') email
			,cm.fullName
			,sdg.detailTitle AS [gender]
			,cmb.countryName AS [country]
			,cmn.countryName AS [nativeCountry]
			,ISNULL(cm.homePhone, '-') homePhone
			,cm.postalCode
			,ISNULL(CSM.stateName, '') + ISNULL(' - ' + detail.state_japanese, '') + ', ' + ISNULL(cm.city, '') + ISNULL(' - ' + detail.city_japanese, '') + ', ' + ISNULL(cm.street, '') + ISNULL(' - ' + detail.STREET_JAPANESE, '') + ', ' + ISNULL(cm.additionalAddress, '') [address]
			,cm.city
			,cm.zipcode
			,ISNULL(COALESCE(cm.telNo, cm.homePhone), '-') telNo
			,ISNULL(cm.mobile, '-') mobile
			,sdo.detailTitle AS [occupation]
			,sdi.detailTitle AS [idType]
			,cm.idType AS [idTypeCode]
			,cm.idNumber
			,cm.verifyDoc1
			,cm.verifyDoc2
			,cm.verifyDoc3
			,CONVERT(VARCHAR(10), dob, 121) AS [dob]
			,CONVERT(VARCHAR(10), idIssueDate, 121) AS [idIssueDate]
			,CONVERT(VARCHAR(10), idExpiryDate, 121) AS [idExpiryDate]
			,cm.homePhone
			,cm.availableBalance
			,verifyDoc4 = SelfieDoc
			,cm.obpId
			,cm.AuditDate
			,customerType = TYP.detailTitle
			,cm.customerStatus
			,CONVERT(VARCHAR(6), cm.dob, 12) AS [dobYMD]
			,CASE 
				WHEN gender = '97'
					THEN '7'
				WHEN gender = '98'
					THEN '8'
				END AS [genderCode]
			,CASE 
				WHEN nativeCountry = '238'
					THEN '1'
				WHEN nativeCountry = '113'
					THEN '2'
				WHEN nativeCountry = '45'
					THEN '3'
				ELSE '4'
				END AS [nativeCountryCode]
			,doc.detailTitle AS DocumentType
			,visa.detailTitle VisaStatus
			,businessType.detailTitle EmployeeBusinessType
			,ISNULL(cm.NameOfEmployeer, '-') NameOfEmployeer
			,ISNULL(cm.SsnNo, '-') SsnNo
			,sourceOfFund.detailTitle SourceOfFund
			,ISNULL(cm.MonthlyIncome, '-') MonthlyIncome
			,ISNULL(cm.Remarks, '-') Remarks
			,CASE 
				WHEN cm.RemittanceAllowed = '1'
					THEN 'Yes'
				ELSE 'No'
				END RemittanceAllowed
			,CASE 
				WHEN cm.OnlineUser = 'Y'
					THEN 'Yes'
				ELSE 'No'
				END OnlineUser
			,CASE 
				WHEN cm.mobileUser = 'Y'
					THEN 'Yes'
				ELSE 'No'
				END MobileUser
			,seq.receiverFullName recFullname
			,seq.receiverAddress recAddress
			,seq.collMode
			,seq.receiverMobileNumber recMobile
			,CM1.countryName pCountry
			,cm1.countryId pCountryId
			,pMode = CASE 
				WHEN SEQ.pModeId = 2
					THEN 'Bank Deposit'
				ELSE 'Cash Payment'
				END
			,pModeId = seq.pModeId
			,ABL.Bank_Name pAgentName
			,ABL.bank_id pAgnetId
			,ISNULL(ABBL.BRANCH_NAME, seq.pBranchName) pBranchName
			,ABBL.BRANCH_ID pBranchId
			,seq.accountNumber
			,cAmt
			,tAmt
			,serviceCharge
			,purpose.detailTitle purposeOfRemittance
			,RELATION.detailTitle relationShip
			,REF.REFERRAL_NAME referralName
		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 staticDataValue doc(NOLOCK) ON doc.valueId = cm.documentType
		LEFT JOIN countryStateMaster CSM(NOLOCK) ON CSM.stateId = CAST(cm.STATE AS VARCHAR)
		LEFT JOIN StaticDataValue visa(NOLOCK) ON visa.valueId = cm.visaStatus
		LEFT JOIN StaticDataValue businessType(NOLOCK) ON businessType.valueId = cm.employeeBusinessType
		LEFT JOIN StaticDataValue sourceOfFund(NOLOCK) ON sourceOfFund.valueId = cm.sourceOfFund
		LEFT JOIN tbl_japan_address_detail detail(NOLOCK) ON detail.zip_code = cm.zipcode
		INNER JOIN SEND_MONEY_REQUEST SEQ(NOLOCK) ON SEQ.membershipId = CM.membershipId
		LEFT JOIN countryMaster CM1(NOLOCK) ON CM1.countryId = cast(SEQ.pCountryId AS VARCHAR)
		LEFT JOIN API_BANK_LIST ABL(NOLOCK) ON ABL.BANK_ID = SEQ.pAgentId
		LEFT JOIN API_BANK_BRANCH_LIST ABBL(NOLOCK) ON ABBL.BRANCH_ID = SEQ.branchId
		LEFT JOIN StaticDataValue purpose(NOLOCK) ON purpose.valueid = seq.purposeOfRemittance
		LEFT JOIN StaticDataValue RELATION(NOLOCK) ON RELATION.VALUEID = seq.relationShip
		LEFT JOIN REFERRAL_AGENT_WISE REF(NOLOCK) ON REF.REFERRAL_CODE = SEQ.CREATEDBY
		WHERE SEQ.ROWID = @ROWID
	END

	IF @FLAG = 'clearKYC'
	BEGIN
		IF EXISTS (
				SELECT *
				FROM customerMaster(NOLOCK)
				WHERE CUSTOMERID = @customerId
				)
			-- AND APPROVEDDATE IS NOT NULL
		BEGIN
			DECLARE @oldData_p VARCHAR(500);

			SELECT @oldData_p = CONCAT (
					'hasDeclare: ' + CAST(hasDeclare AS VARCHAR)
					,' '
					,'agreeYn: ' + CAST(agreeYn AS VARCHAR)
					) + ' ' + ISNULL(mobileverifieddate, 'N/A') + ' ' + ISNULL(mobileverifiedby, 'N/A')
			FROM customerMaster(NOLOCK)
			WHERE CUSTOMERID = @customerId

			EXEC proc_applicationLogs @flag = 'i'
				,@logType = 'Update-kyc'
				,@tableName = 'CustomerMaster'
				,@dataId = @customerId
				,@oldData = @oldData_p
				,@newData = 'hasDeclare =0, agreeYn =0'
				,@module = '20'
				,@user = @user

			UPDATE customerMaster
			SET HasDeclare = '0'
				,mobileverifieddate = NULL
				,mobileApprovedDate = NULL
				,mobileApprovedBy = ''
				,agreeYn = '0'
				,modifiedDate = GETDATE()
				,modifiedBy = @user
			WHERE customerid = @customerId
		END

		SELECT 0 ErrorCode
			,'Customer KYC has been successfully cleared.' Msg
			,@customerId id

		RETURN
	END

	IF @FLAG = 'CUSTOMER-DETAILS-ACTIVATE'
	BEGIN
		SELECT 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
		FROM dbo.customerDocument cd
		LEFT JOIN dbo.staticDataValue sdv ON sdv.valueId = cd.documentType
		WHERE cd.customerId = @customerId
			AND ISNULL(isDeleted, 'N') = 'N'
		ORDER BY cd.createdDate;
	END

	IF @FLAG = 'reVerify'
	BEGIN
		IF EXISTS (
				SELECT *
				FROM customerMaster(NOLOCK)
				WHERE CUSTOMERID = @customerId
				)
			-- AND APPROVEDDATE IS NOT NULL
		BEGIN
			DECLARE @oldData1 VARCHAR(500);

			SELECT @oldData1 = CAST(mobileVerificationType AS VARCHAR) + ' ' + '|' + ' ' + ISNULL(mobileverifieddate, 'N/A') + ' ' + '|' + ' ' + ISNULL(mobileverifiedby, 'N/A')
			FROM customerMaster(NOLOCK)
			WHERE CUSTOMERID = @customerId

			EXEC proc_applicationLogs @flag = 'i'
				,@logType = 'Re-Verify-Customer'
				,@tableName = 'CustomerMaster'
				,@dataId = @customerId
				,@oldData = @oldData1
				,@newData = 'Sent for re-verification'
				,@module = '30'
				,@user = @user

		
				UPDATE customerMaster
			SET mobileverifieddate = NULL
				,mobileVerificationType = ''
				,mobileverifiedby = ''
				,modifiedDate = GETDATE()
				,modifiedBy = @user
				,hasdeclare=0
				,agreeyn=0
				,isAgreeDate=GETDATE()
				,LawsonCardNo=NULL
				,mobileApprovedDate = NULL
			WHERE customerid = @customerId
		END

		SELECT 0 ErrorCode
			,'Customer moved to verification queue.' Msg
			,@customerId id

		RETURN
	END

	IF @FLAG = 'deleteCustomer'
	BEGIN
	
		INSERT INTO customermasterdeleted
		SELECT membershipId
			,firstName
			,middleName
			,lastName1
			,lastName2
			,country
			,address
			,STATE
			,zipCode
			,district
			,city
			,email
			,homePhone
			,workPhone
			,mobile
			,nativeCountry
			,dob
			,placeOfIssue
			,customerType
			,occupation
			,CASE WHEN @ofacReason IS NULL THEN '' ELSE 'Y' END
			,createdBy
			,createdDate
			,modifiedBy
			,modifiedDate
			,approvedBy
			,approvedDate
			,isDeleted
			,lastTranId
			,relationId
			,relativeName
			,address2
			,fullName
			,postalCode
			,idExpiryDate
			,idType
			,idNumber
			,telNo
			,companyName
			,gender
			,salaryRange
			,bonusPointPending
			,Redeemed
			,bonusPoint
			,todaysSent
			,todaysNoOfTxn
			,agentId
			,branchId
			,memberIDissuedDate
			,memberIDissuedByUser
			,memberIDissuedAgentId
			,memberIDissuedBranchId
			,totalSent
			,idIssueDate
			,onlineUser
			,customerPassword
			,customerStatus
			,isActive
			,islocked
			,sessionId
			,lastLoginTs
			,howDidYouHear
			,ansText
			,ansEmail
			,state2
			,ipAddress
			,marketingSubscription
			,paidTxn
			,firstTxnDate
			,verifyDoc1
			,verifyDoc2
			,verifiedBy
			,verifiedDate
			,verifyDoc3
			,isForcedPwdChange
			,bankName
			,bankAccountNo
			,walletAccountNo
			,availableBalance
			,obpId
			,CustomerBankName
			,referelCode
			,isEmailVerified
			,verificationCode
			,SelfieDoc
			,HasDeclare
			,AuditDate
			,AuditBy
			,SchemeStartDate
			,invalidAttemptCount
			,sourceOfFund
			,street
			,streetUnicode
			,cityUnicode
			,visaStatus
			,employeeBusinessType
			,nameOfEmployeer
			,SSNNO
			,remittanceAllowed
			,remarks
			,registerationNo
			,organizationType
			,dateofIncorporation
			,natureOfCompany
			,position
			,nameOfAuthorizedPerson
			,monthlyIncome
			,ADDITIONALADDRESS
			,@user
			,GETDATE()
			,customerId
			,mobileUser
			,agreeYn
			,customerEmail
			,anotherIdType
			,anotherIDNumber
			,anotherIDIssueDate
			,anotherIDExpiryDate
			,mobileApprovedBy
			,mobileApprovedDate
			,mobileVerificationType
			,verifyRemarks
			,@ofacRemarks
			,isOfac
			,LawsonCardNo
			,RegistrationType
			,TrustDocId
		FROM customerMaster
		WHERE customerId = @customerId

		SELECT @email = username  
		FROM mobile_userRegistration
		WHERE customerId = @customerId

		SELECT @registrationType = registrationType , @trustdocId = TrustDocId
		FROM customerMaster (NOLOCK)
		WHERE customerId = @customerId

		DELETE
		FROM CUSTOMERMASTER
		WHERE CUSTOMERID = @customerId

		DELETE
		FROM CUSTOMERMASTERTEMP
		WHERE CUSTOMERID = @customerId
			AND username = @email

		DELETE
		FROM mobile_userRegistration
		WHERE CUSTOMERID = @customerId
			AND username = @email and customerId = @customerId

		UPDATE Customer_Promotion SET [status] = 2 WHERE destinationCustomerId =  @customerId AND codeType = 'REGISTRATION'
		BEGIN
			UPDATE TBL_MOBILE_OTP_REQUEST
			SET IS_EXPIRED = '1'
				,IS_SUCCESS = '0'
				,MODIFIED_DATE = GETDATE()
			WHERE USER_ID = @email
				AND REQUEST_FOR = 'REGISTER'
		END

		SET @oldValue = CASE WHEN @ofacReason IS NULL THEN '' ELSE 'OFAC' END

		SELECT 0 ErrorCode
			,'Customer Deleted Successfully' Msg
			,@customerId id
			,@registrationType extra
			,@trustdocId Extra1

			EXEC proc_applicationLogs @flag = 'i'
				,@logType = 'Delete'
				,@tableName = 'CustomerMaster'
				,@dataId = @customerId
				,@oldData = @oldValue
				,@newData = @registrationType
				,@module = '40'
				,@user = @user
	END

	IF @FLAG = 'download'
	BEGIN
		 SELECT  
		 SN =ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
		   customerId,      
		   membershipId      
		   ,fullName =fullName      
		   ,sd.detailTitle as idType      
		   ,ISNULL(cm.idNumber,'') as idNumber      
		   ,com.countryName,cm.dob,cm.address      
		   ,cm.city      
		   ,ISNULL(cm.email,'') as email      
		   ,ISNULL(cm.mobile,'') as mobile      
		   ,cm.createdDate      
		   ,cm.bankAccountNo      
		   ,bankName = bl.bankName      
		   ,cm.LawsonCardNo
		   ,RegistrationType =  CASE WHEN cm.RegistrationType IS NULL THEN 'Manual' ELSE cm.RegistrationType END
		   ,ISNULL(cm.verificationCode, 'NOT COMPLETED') as KYC_Status
		   ,ISNULL(cm.referelCode,'') as introducer
		  FROM dbo.customerMaster cm(nolock)      
		  LEFT JOIN dbo.staticDataValue sd(nolock) ON sd.valueId=cm.idType      
		  INNER JOIN dbo.countryMaster com(nolock) ON com.countryId = cm.nativeCountry      
		  LEFT JOIN vwBankLists bl (NOLOCK) ON cm.bankName = bl.rowId
	 END
	IF @FLAG = 'deleteCustomer-new'
	BEGIN
		IF EXISTS ( SELECT TOP 1 1 FROM CUSTOMERMASTERTEMP (NOLOCK) WHERE CUSTOMERID = @CUSTOMERID AND @source = 'temp' AND membershipId IS NULL )
		BEGIN 
				SELECT @email = username
				FROM mobile_userRegistration
				WHERE customerId = @customerId
		
				DELETE
				FROM CUSTOMERMASTERTEMP
				WHERE CUSTOMERID = @customerId
		
				DELETE
				FROM mobile_userRegistration
				WHERE CUSTOMERID = @customerId
					AND username = @email and customerId = @customerId
		
				UPDATE TBL_MOBILE_OTP_REQUEST
					SET IS_EXPIRED = '1'
						,IS_SUCCESS = '0'
						,MODIFIED_DATE = GETDATE()
					WHERE USER_ID = @email
						AND REQUEST_FOR = 'REGISTER'
		
				SELECT 0 ErrorCode
					,'Customer Deleted Successfully' Msg
					,@customerId id
					,@registrationType Extra1
					,@trustdocId Extra2
		
					EXEC proc_applicationLogs @flag = 'i'
						,@logType = 'Delete'
						,@tableName = 'CustomerMasterTemp'
						,@dataId = @customerId
						,@oldData = 'Deleted customer from menu!!'
						,@newData = @deleteReason
						,@module = '50'
						,@user = @user
		END 
		ELSE
		BEGIN 
			IF EXISTS (
					SELECT TOP 1 1
					FROM TRANSENDERS S(NOLOCK)
					WHERE S.CUSTOMERID = @customerId
					)
			BEGIN
				SELECT 1 ErrorCode
					,'You can not delete this customer, customer has already done transaction!' Msg
					,@customerId id
			
				RETURN
			END
			
			IF EXISTS (
					SELECT TOP 1 1
					FROM TRANSENDERStemp S(NOLOCK)
					WHERE S.CUSTOMERID = @customerId
					)
			BEGIN
				SELECT 1 ErrorCode
					,'You can not delete this customer, customer has already done transaction!' Msg
					,@customerId id
			
				RETURN
			END
			
			--IF EXISTS (
			--		SELECT *
			--		FROM CUSTOMERMASTER(NOLOCK)
			--		WHERE CUSTOMERID = @customerId
			--			AND APPROVEDDATE IS NOT NULL
			--		)
			--BEGIN
			--	SELECT 1 ErrorCode
			--		,'You can not delete this customer, customer has already been approved!' Msg
			--		,@customerId id
			
			--	RETURN
			--END
			
			--select * from customermasterdeleted      
			INSERT INTO customermasterdeleted
			SELECT membershipId
				,firstName
				,middleName
				,lastName1
				,lastName2
				,country
				,address
				,STATE
				,zipCode
				,district
				,city
				,email
				,homePhone
				,workPhone
				,mobile
				,nativeCountry
				,dob
				,placeOfIssue
				,customerType
				,occupation
				,CASE WHEN @deleteReason IS NULL THEN '' ELSE 'Y' END
				,createdBy
				,createdDate
				,modifiedBy
				,modifiedDate
				,approvedBy
				,approvedDate
				,isDeleted
				,lastTranId
				,relationId
				,relativeName
				,address2
				,fullName
				,postalCode
				,idExpiryDate
				,idType
				,idNumber
				,telNo
				,companyName
				,gender
				,salaryRange
				,bonusPointPending
				,Redeemed
				,bonusPoint
				,todaysSent
				,todaysNoOfTxn
				,agentId
				,branchId
				,memberIDissuedDate
				,memberIDissuedByUser
				,memberIDissuedAgentId
				,memberIDissuedBranchId
				,totalSent
				,idIssueDate
				,onlineUser
				,customerPassword
				,customerStatus
				,isActive
				,islocked
				,sessionId
				,lastLoginTs
				,howDidYouHear
				,ansText
				,ansEmail
				,state2
				,ipAddress
				,marketingSubscription
				,paidTxn
				,firstTxnDate
				,verifyDoc1
				,verifyDoc2
				,verifiedBy
				,verifiedDate
				,verifyDoc3
				,isForcedPwdChange
				,bankName
				,bankAccountNo
				,walletAccountNo
				,availableBalance
				,obpId
				,CustomerBankName
				,referelCode
				,isEmailVerified
				,verificationCode
				,SelfieDoc
				,HasDeclare
				,AuditDate
				,AuditBy
				,SchemeStartDate
				,invalidAttemptCount
				,sourceOfFund
				,street
				,streetUnicode
				,cityUnicode
				,visaStatus
				,employeeBusinessType
				,nameOfEmployeer
				,SSNNO
				,remittanceAllowed
				,remarks
				,registerationNo
				,organizationType
				,dateofIncorporation
				,natureOfCompany
				,position
				,nameOfAuthorizedPerson
				,monthlyIncome
				,ADDITIONALADDRESS
				,@user
				,GETDATE()
				,customerId
				,mobileUser
				,agreeYn
				,customerEmail
				,anotherIdType
				,anotherIDNumber
				,anotherIDIssueDate
				,anotherIDExpiryDate
				,mobileApprovedBy
				,mobileApprovedDate
				,mobileVerificationType
				,verifyRemarks
				,ISNULL(ofacRemarks,ofacRemarks)
				,isOfac
				,LawsonCardNo
				,RegistrationType
				,TrustDocId			
			FROM customerMaster
			WHERE customerId = @customerId
			
			SELECT @email = username
			FROM mobile_userRegistration
			WHERE customerId = @customerId
			
			SELECT @registrationType = RegistrationType , @trustdocId = TrustDocId
			FROM customerMaster (NOLOCK)
			WHERE customerId = @customerId
			
			DELETE
			FROM CUSTOMERMASTER
			WHERE CUSTOMERID = @customerId
			
			DELETE
			FROM CUSTOMERMASTERTEMP
			WHERE CUSTOMERID = @customerId
				AND username = @email
			
			DELETE
			FROM mobile_userRegistration
			WHERE CUSTOMERID = @customerId
				AND username = @email and customerId = @customerId
			
			UPDATE Customer_Promotion SET [status] = 2 WHERE destinationCustomerId =  @customerId AND codeType = 'REGISTRATION'
			
			UPDATE TBL_MOBILE_OTP_REQUEST
				SET IS_EXPIRED = '1'
					,IS_SUCCESS = '0'
					,MODIFIED_DATE = GETDATE()
				WHERE USER_ID = @email
					AND REQUEST_FOR = 'REGISTER'
			
			SELECT 0 ErrorCode
				,'Customer Deleted Successfully' Msg
				,@customerId id
				,@registrationType Extra1
				,@trustdocId Extra2
			
				EXEC proc_applicationLogs @flag = 'i'
					,@logType = 'Delete'
					,@tableName = 'CustomerMaster'
					,@dataId = @customerId
					,@oldData = 'Deleted customer from menu!!'
					,@newData = @deleteReason
					,@module = '50'
					,@user = @user
			END
	END


END TRY

BEGIN CATCH
	IF @@TRANCOUNT > 0
		ROLLBACK TRANSACTION;

	DECLARE @errorMessage VARCHAR(MAX);

	SET @errorMessage = ERROR_MESSAGE();

	EXEC proc_errorHandler 1
		,@errorMessage
		,@customerId;
END CATCH;