You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
402 lines
30 KiB
402 lines
30 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[PROC_AGENT_CUTOMERSETUP] Script Date: 12/7/2023 9:45:18 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
|
|
|
|
ALTER PROC [dbo].[PROC_AGENT_CUTOMERSETUP]
|
|
@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 VARCHAR(20) = 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 ,
|
|
@mobileUser VARCHAR(50) =NULL,
|
|
@otherIdNumber VARCHAR(100) = NULL,
|
|
@verifyremarks NVARCHAR(800) = NULL,
|
|
@RegistrationType VARCHAR(100) = NULL,
|
|
@UseNFC VARCHAR(10) = NULL
|
|
|
|
AS
|
|
|
|
------------------------------------------
|
|
-- #101 - Mobile Changes
|
|
-- Fix case for @mobileUser
|
|
-- #1094 -> reamrks for verify pending
|
|
------------------------------------------
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
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
|
|
);
|
|
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);
|
|
|
|
|
|
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)
|
|
BEGIN
|
|
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
|
|
|
|
SET @onlineUser = CASE WHEN @onlineUser='true'THEN 'Y' ELSE 'N' END
|
|
--SET @mobileUser = CASE WHEN @mobileUser='Y'THEN 'Y' ELSE 'N' END
|
|
SET @fullName = ISNULL(@firstName, '')
|
|
+ ISNULL(' ' + @middleName, '')
|
|
+ ISNULL(' ' + @lastName1, '')
|
|
+ ISNULL(' ' + @lastName2, '')
|
|
|
|
|
|
DECLARE @approvedBy VARCHAR(30),@approvedDate VARCHAR(50)
|
|
|
|
--IF ISNULL(@street, '') IS NOT NULL
|
|
--BEGIN
|
|
-- SET @district = @street
|
|
-- SELECT @custCity = CITY_NAME, @street = STREET_NAME
|
|
-- FROM TBL_JAPAN_ADDRESS_DETAIL(NOLOCK)
|
|
-- WHERE ROW_ID = @district
|
|
--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,
|
|
@remarks = @remarks,
|
|
--used for customer type organisation
|
|
@companyName = @companyName,
|
|
@registerationNo = @registerationNo,
|
|
@organizationType = @organizationType,
|
|
@dateofIncorporation = @dateofIncorporation,
|
|
@natureOfCompany = @natureOfCompany,
|
|
@nameOfAuthorizedPerson = @nameOfAuthorizedPerson,
|
|
@position = @position,
|
|
@additionaladdress = @additionaladdress,
|
|
@loginBranchId = @loginBranchId,
|
|
@changedSecurityInfo = @changedSecurityInfo OUT,
|
|
@mobileUser = @mobileUser ,
|
|
@verifyremarks = @verifyremarks,
|
|
@RegistrationType = @RegistrationType
|
|
|
|
|
|
--SET DATEFORMAT ymd
|
|
--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
|
|
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 CONVERT(DATETIME, @custDOB, 103) IS NOT NULL THEN CONVERT(DATETIME, @custDOB, 103)
|
|
-- ELSE dob
|
|
-- END ,
|
|
dob = ISNULL(@custDOB,dob),
|
|
onlineUser = @onlineUser,
|
|
customerType = @customerType ,
|
|
isActive = 'Y' ,
|
|
modifiedBy = @user ,
|
|
modifiedDate = GETDATE() ,
|
|
--idIssueDate = ISNULL(CONVERT(DATETIME, @custIdissueDate, 103), idIssueDate) --new added by dhan
|
|
--,
|
|
--idExpiryDate = ISNULL(CONVERT(DATETIME, @custIdValidDate, 103), idExpiryDate) ,
|
|
idIssueDate=ISNULL(@custIdissueDate, idIssueDate) ,
|
|
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 ,
|
|
additionalAddress = ISNULL(@additionalAddress,additionalAddress)
|
|
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,
|
|
documentType = @docType,
|
|
isCDDIUpdated = 1,
|
|
occupationOther = @occupationOther,
|
|
otherIdNumber = @otherIdNumber,
|
|
mobileUser = CASE WHEN @mobileUser ='True' THEN 'Y' ELSE 'N' END ,
|
|
verifyRemarks = @verifyremarks,
|
|
RegistrationType=@RegistrationType
|
|
WHERE customerId = @customerId;
|
|
|
|
IF @UseNFC IS NOT NULL
|
|
UPDATE mobile_userRegistration SET useNfc= @UseNFC WHERE customerId = @customerId;
|
|
|
|
DECLARE @TEST VARCHAR(20)
|
|
|
|
SELECT @membershipId = membershipId,@createdDate = CONVERT(VARCHAR(10),createdDate,121) from customermaster where customerid = @customerId
|
|
|
|
--SELECT TOP 100 CREATEDDATE, * FROM CUSTOMERMASTER ORDER BY CUSTOMERID DESC
|
|
SELECT '0' ErrorCode ,
|
|
'Customer data has been updated successfully.' Msg ,
|
|
cast(@customerId as varchar) +'|' + cast(@membershipId as varchar) + '|' + cast(@createdDate as varchar) id;
|
|
|
|
END
|
|
|
|
END
|
|
END
|