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.
639 lines
56 KiB
639 lines
56 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[proc_online_receiverSetup] Script Date: 11/27/2023 4:50:22 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
ALTER PROCEDURE [dbo].[proc_online_receiverSetup]
|
|
(
|
|
@flag VARCHAR(50) = NULL,
|
|
@user VARCHAR(30) = NULL,
|
|
@receiverId VARCHAR(50) = NULL,
|
|
@customerId VARCHAR(50) = NULL,
|
|
@membershipId VARCHAR(50) = NULL,
|
|
@firstName VARCHAR(100) = NULL,
|
|
@middleName VARCHAR(100) = NULL,
|
|
@lastName1 VARCHAR(100) = NULL,
|
|
@lastName2 VARCHAR(100) = NULL,
|
|
@country VARCHAR(200) = NULL,
|
|
@nativeCountry VARCHAR(200) = NULL,
|
|
@address VARCHAR(500) = NULL,
|
|
@state VARCHAR(200) = NULL,
|
|
@zipCode VARCHAR(50) = NULL,
|
|
@city VARCHAR(100) = NULL,
|
|
@email VARCHAR(150) = NULL,
|
|
@homePhone VARCHAR(100) = NULL,
|
|
@workPhone VARCHAR(100) = NULL,
|
|
@mobile VARCHAR(100) = NULL,
|
|
@relationship VARCHAR(100) = NULL,
|
|
@sortBy VARCHAR(50) = NULL,
|
|
@sortOrder VARCHAR(5) = NULL,
|
|
@pageSize INT = NULL,
|
|
@pageNumber INT = NULL,
|
|
@receiverType INT = NULL,
|
|
@idType INT = NULL,
|
|
@idNumber VARCHAR(25) = NULL,
|
|
@placeOfIssue VARCHAR(80) = NULL,
|
|
@paymentMode INT = NULL,
|
|
@bankLocation VARCHAR(100) = NULL,
|
|
@payOutPartner INT = NULL,
|
|
@bankName VARCHAR(150) = NULL,
|
|
@receiverAccountNo VARCHAR(40) = NULL,
|
|
@remarks NVARCHAR(800) = NULL,
|
|
@purposeOfRemit VARCHAR(100) = NULL,
|
|
@fromDate NVARCHAR(20) = NULL,
|
|
@toDate NVARCHAR(20) = NULL,
|
|
@otherRelationDesc VARCHAR(20) = NULL,
|
|
@RelationshipOther VARCHAR(100) = NULL,
|
|
@PurposeOfRemitanceOther VARCHAR(100) = NULL,
|
|
@loginBranchId BIGINT = NULL,
|
|
@goodsType VARCHAR(200) = NULL,
|
|
@goodsOrigin VARCHAR(200) = NULL,
|
|
@portOfShipment VARCHAR(100) = NULL,
|
|
@isFromMobile BIT = NULL,
|
|
@CreatedFrom CHAR = 'C'
|
|
)
|
|
AS
|
|
----------------------------------------
|
|
--JME-544 -> IF @bankLocation = '0' SET @bankLocation = NULL
|
|
-- #727 - @flag = 'i' , createdFrom and isotpverified
|
|
-- #11365 - @flag = 'u' , update bank on changing payment method
|
|
-----------------------------------------
|
|
BEGIN
|
|
DECLARE @table VARCHAR(MAX) ,
|
|
@select_field_list VARCHAR(MAX) ,
|
|
@extra_field_list VARCHAR(MAX) ,
|
|
@sql_filter VARCHAR(MAX);
|
|
--,@customerId1 VARCHAR(50)
|
|
DECLARE @PURPOSEID INT = NULL, @RELATION INT = NULL , @bank INT = NULL
|
|
|
|
SELECT @PURPOSEID = valueId
|
|
FROM STATICDATAVALUE (NOLOCK)
|
|
WHERE detailTitle = @purposeOfRemit
|
|
AND typeID = '3800'
|
|
|
|
IF @PURPOSEID IS NULL
|
|
SET @PURPOSEID = @purposeOfRemit
|
|
|
|
SELECT @RELATION = valueId
|
|
FROM STATICDATAVALUE (NOLOCK)
|
|
WHERE detailTitle = @relationship
|
|
AND typeID = '2100'
|
|
|
|
IF @RELATION IS NULL
|
|
SET @RELATION = @relationship
|
|
IF @flag = 'i'
|
|
BEGIN
|
|
--IF EXISTS ( SELECT 'X' FROM receiverInformation WITH ( NOLOCK )
|
|
-- WHERE mobile = @mobile AND customerId = @customerId )
|
|
-- BEGIN
|
|
-- SELECT '1' errorCode ,'The receiver with this Mobile Number already exists.' msg ,NULL id;
|
|
-- RETURN;
|
|
-- END;
|
|
declare @customerCreatedDate datetime
|
|
SELECT @membershipId= membershipId,@customerCreatedDate = createddate from customermaster WHERE customerId = @customerId
|
|
|
|
IF NOT EXISTS(SELECT 'X' FROM CUSTOMERMASTER WITH(NOLOCK) WHERE customerId = @customerId)
|
|
BEGIN
|
|
SELECT '1' errorCode,''''+cast(@customerId as varchar) + ''' customerId doest not exist exists.' msg,null id
|
|
RETURN
|
|
END
|
|
|
|
|
|
IF EXISTS( SELECT 'X' from countryMaster where CAST(countryId as varchar)= @country)
|
|
BEGIN
|
|
SELECT @country= countryName, @nativeCountry= countryId from countryMaster where CAST(countryId as varchar)= @country;
|
|
END
|
|
|
|
|
|
INSERT INTO receiverInformation
|
|
(fullname
|
|
,membershipId ,customerId ,firstName ,middleName ,lastName1 ,lastName2 ,country ,NativeCountry,[address] ,[state] ,zipCode ,city ,email ,
|
|
homePhone ,workPhone ,mobile ,relationship ,receiverType ,idType ,idNumber ,placeOfIssue ,paymentMode ,bankLocation ,payOutPartner ,
|
|
bankName ,receiverAccountNo ,remarks ,purposeOfRemit ,createdBy ,createdDate,otherRelationDesc,agentId, relationOther, purposeOther, isCDDIUpdated
|
|
,goodsType,goodsOrigin,portOfShipment,isVerified, isFromPhone , createdFrom , isOTPVerified,bankBranchName
|
|
)
|
|
SELECT ISNULL(@firstName,'') + ISNULL(' ' + @middleName,'') +ISNULL(' ' + @lastName1,''),
|
|
@membershipId ,@customerId ,@firstName ,@middleName ,@lastName1 ,@lastName2 ,@country ,@nativeCountry,@address ,@state ,@zipCode ,@city ,@email ,
|
|
@homePhone ,@workPhone ,@mobile ,@RELATION ,@receiverType ,@idType ,@idNumber ,@placeOfIssue ,@paymentMode ,CASE ISNUMERIC(@bankLocation) WHEN 1 THEN @bankLocation ELSE 0 END
|
|
,@payOutPartner , @bankName ,@receiverAccountNo ,@remarks ,@PURPOSEID ,@user ,GETDATE(),@otherRelationDesc,@loginBranchId, @RelationshipOther, @PurposeOfRemitanceOther, 1
|
|
,@goodsType,@goodsOrigin,@portOfShipment,1, @isFromMobile, @CreatedFrom , '1',@bankLocation
|
|
|
|
SET @receiverId=SCOPE_IDENTITY();
|
|
|
|
SELECT '0' errorCode
|
|
,'Receiver Successfully added.' msg
|
|
,id = @receiverId
|
|
,extra = @customerId
|
|
,cast(@customerId as varchar) +'|' + cast(@membershipId as varchar) + '|' + cast(cast(@customerCreatedDate as date) as varchar) extra2; ;
|
|
RETURN;
|
|
END;
|
|
|
|
IF @flag = 'u'
|
|
BEGIN
|
|
--IF EXISTS(SELECT 'X' FROM receiverInformation WITH(NOLOCK) WHERE mobile=@mobile and receiverId =@receiverId)
|
|
--BEGIN
|
|
-- SELECT '1' errorCode,'The receiver with this Mobile Number already exists.' msg,null id
|
|
-- RETURN
|
|
--END
|
|
IF @bankLocation = '0'
|
|
SET @bankLocation = NULL
|
|
|
|
DECLARE @fullName VARCHAR(100)
|
|
SET @fullName = @firstName + ISNULL(' ' + @middleName,'') + ISNULL(' ' + @lastName1,'') + ISNULL(' ' + @lastName2,'')
|
|
|
|
SELECT @customerCreatedDate = createddate from customermaster WHERE customerId = @customerId
|
|
|
|
SELECT @bank = ab.bank_id
|
|
FROM API_BANK_LIST (NOLOCK) AB
|
|
INNER JOIN API_BANK_LIST_MASTER (NOLOCK) ABM ON ABM.JME_BANK_CODE = AB.JME_BANK_CODE
|
|
WHERE ABM.MASTER_BANK_ID = @payOutPartner
|
|
|
|
EXEC PROC_RECEIVERMODIFYLOGS @FLAG = 'i'
|
|
,@user = @user
|
|
,@receiverId = @receiverId
|
|
,@customerId = @customerId
|
|
,@firstName = @firstName
|
|
,@middleName = @middleName
|
|
,@lastName1 = @lastName1
|
|
,@lastName2 = @lastName2
|
|
,@fullName = @fullName
|
|
,@country = @country
|
|
,@nativeCountry = @nativeCountry
|
|
,@address = @address
|
|
,@state = @state
|
|
,@zipCode = @zipCode
|
|
,@city = @city
|
|
,@email = @email
|
|
,@homePhone = @homePhone
|
|
,@workPhone = @workPhone
|
|
,@mobile = @mobile
|
|
,@relationship = @RELATION
|
|
,@receiverType = @receiverType
|
|
,@idType = @idType
|
|
,@idNumber = @idNumber
|
|
,@placeOfIssue = @placeOfIssue
|
|
,@paymentMode = @paymentMode
|
|
,@bankLocation = @bankLocation
|
|
,@payOutPartner = @payOutPartner
|
|
,@bankName = @bankName
|
|
,@receiverAccountNo = @receiverAccountNo
|
|
,@remarks = @remarks
|
|
,@purposeOfRemit = @PURPOSEID
|
|
,@otherRelationDesc = @otherRelationDesc
|
|
,@bank = @bank
|
|
|
|
UPDATE receiverInformation
|
|
SET firstName = @firstName ,
|
|
middleName = @middleName ,
|
|
lastName1 = @lastName1 ,
|
|
lastName2 = @lastName2 ,
|
|
country = @country ,
|
|
NativeCountry = @nativeCountry ,
|
|
address = @address ,
|
|
state = @state ,
|
|
zipCode = @zipCode ,
|
|
city = @city ,
|
|
email = @email ,
|
|
homePhone = @homePhone ,
|
|
workPhone = @workPhone ,
|
|
mobile = @mobile ,
|
|
relationship = @RELATION ,
|
|
receiverType = @receiverType ,
|
|
idType = @idType ,
|
|
idNumber = @idNumber ,
|
|
placeOfIssue = @placeOfIssue ,
|
|
paymentMode = @paymentMode ,
|
|
bankLocation = @bankLocation ,
|
|
payOutPartner = @payOutPartner ,
|
|
bankName = @bankName ,
|
|
bank = @bank,
|
|
receiverAccountNo = @receiverAccountNo ,
|
|
remarks = @remarks ,
|
|
purposeOfRemit = @PURPOSEID ,
|
|
modifiedBy = @user ,
|
|
modifiedDate = GETDATE(),
|
|
otherRelationDesc = @otherRelationDesc,
|
|
relationOther = @RelationshipOther,
|
|
purposeOther = @PurposeOfRemitanceOther,
|
|
isCDDIUpdated = 1,
|
|
goodsType = @goodsType,
|
|
goodsOrigin = @goodsOrigin,
|
|
portOfShipment = @portOfShipment
|
|
WHERE receiverId = @receiverId;
|
|
|
|
SELECT '0' errorCode ,
|
|
'Receiver Information has been updated' msg ,
|
|
id = null,extra = @customerId,
|
|
cast(@customerId as varchar) +'|' + cast(@membershipId as varchar) + '|' + cast(cast(@customerCreatedDate as date) as varchar) extra;
|
|
END;
|
|
|
|
IF @flag = 'd'
|
|
BEGIN
|
|
|
|
--DELETE FROM receiverInformation WHERE receiverId = @receiverId;
|
|
|
|
UPDATE receiverInformation SET isdeleted = 1
|
|
,deletedby = @user
|
|
,deleteddate = cast(Getdate() as date)
|
|
WHERE receiverId = @receiverId;
|
|
|
|
|
|
SELECT '0' errorCode ,'Receiver Deleted Successfully.' msg ,id = @receiverId;
|
|
END;
|
|
|
|
IF @flag = 's'
|
|
BEGIN
|
|
IF @sortBy = 'SN'
|
|
SET @sortBy = NULL;
|
|
IF @sortBy IS NULL
|
|
SET @sortBy = 'firstName';
|
|
IF @sortOrder IS NULL
|
|
SET @sortOrder = 'ASC';
|
|
SET @table = '
|
|
(
|
|
SELECT
|
|
ri.receiverId
|
|
,ri.firstName , ISNULL('' '' + ri.middleName,'''') middleName, ISNULL('' '' + ri.lastName1,'''') lastName1, ISNULL('' '' + ri.lastName2,'''') lastName2
|
|
,ri.firstName + ISNULL('' '' + ri.middleName,'''') + ISNULL('' '' + ri.lastName1,'''') + ISNULL('' '' + ri.lastName2,'''') FullName
|
|
,ri.customerId
|
|
,RI.country
|
|
,Cm.countryName nativeCountry
|
|
,ri.address
|
|
,ri.state
|
|
,ri.zipCode
|
|
,ri.city
|
|
,ri.email
|
|
,ri.homePhone
|
|
,ri.workPhone
|
|
,(Select coalesce(ri.mobile,ri.homePhone)) as Mobile
|
|
,relationship,
|
|
otherRelationDesc,
|
|
ri.receiverType ,
|
|
ri.idType ,
|
|
ri.idNumber ,
|
|
ri.placeOfIssue ,
|
|
ri.paymentMode ,
|
|
ri.bankLocation ,
|
|
ri.payOutPartner ,
|
|
ri.bankName ,
|
|
ri.receiverAccountNo ,
|
|
ri.remarks,
|
|
ri.purposeOfRemit,
|
|
ri.createdDate,
|
|
CMM.membershipId,
|
|
ri.createdBy receiverCreatedBy,
|
|
ri.createdDate receiverCreatedDate,
|
|
hasChanged = CASE WHEN (ri.approvedBy IS NULL) THEN ''Y'' ELSE ''N'' END,
|
|
modifiedBy = CASE WHEN ri.approvedBy IS NULL THEN ri.createdBy ELSE Cm.createdBy END
|
|
FROM receiverInformation ri WITH (NOLOCK)
|
|
LEFT JOIN CountryMaster (NOLOCK) Cm ON Cm.countryId=ri.NativeCountry
|
|
INNER JOIN CUSTOMERMASTER CMM (NOLOCK) ON CMM.CUSTOMERID = RI.CUSTOMERID
|
|
WHERE RI.customerId = ''' + @customerId + '''
|
|
and isnull(ri.isdeleted,0) <> 1';
|
|
|
|
|
|
SET @sql_filter = '';
|
|
IF @country IS NOT NULL
|
|
SET @table = @table + ' and country=''' + @country + '''';
|
|
IF @address IS NOT NULL
|
|
SET @table = @table + ' AND address like ''' + @address
|
|
+ '%'' or city like ''' + @city + '%''';-- or id Like '''+@agentAddress+ '%'''
|
|
SET @table = @table + ' )x';
|
|
PRINT @table;
|
|
IF @receiverId IS NOT NULL
|
|
SET @sql_filter += ' AND receiverId=' + @receiverId;
|
|
IF ISNULL(@fromDate, '') <> '' AND ISNULL(@toDate, '') <> ''
|
|
SET @sql_filter += ' AND receiverCreatedDate BETWEEN '''
|
|
+ @fromDate + ''' AND ''' + @toDate + ' 23:59:59''';
|
|
SET @select_field_list = 'receiverId,customerId,FullName=firstName + middleName + lastName1 + lastName2,firstName,middleName
|
|
,lastName1,lastName2,country,address,state,zipCode,city,email,homePhone,workPhone,Mobile,relationship,receiverType ,idType ,
|
|
idNumber ,placeOfIssue ,paymentMode ,bankLocation ,payOutPartner ,bankName ,receiverAccountNo ,remarks,purposeOfRemit,
|
|
createdDate,membershipId,receiverCreatedBy,receiverCreatedDate';
|
|
|
|
EXEC dbo.proc_paging @table, @sql_filter, @select_field_list,@extra_field_list, @sortBy, @sortOrder, @pageSize,@pageNumber;
|
|
END;
|
|
|
|
IF @flag = 'a'
|
|
BEGIN
|
|
SELECT [receiverId] ,
|
|
[customerId] ,
|
|
[membershipId] ,
|
|
[firstName] ,
|
|
[middleName] ,
|
|
[lastName1] ,
|
|
[lastName2] ,
|
|
[country] ,
|
|
[NativeCountry] ,
|
|
[address] ,
|
|
[state] ,
|
|
[zipCode] ,
|
|
[city] ,
|
|
[email] ,
|
|
[homePhone] ,
|
|
[workPhone] ,
|
|
[mobile] ,
|
|
[relationship] ,
|
|
[receiverType] ,
|
|
[idType] ,
|
|
[idNumber] ,
|
|
[placeOfIssue] ,
|
|
[paymentMode] ,
|
|
[bankLocation] ,
|
|
[payOutPartner] ,
|
|
[bankName] ,
|
|
[receiverAccountNo] ,
|
|
[remarks] ,
|
|
[purposeOfRemit]
|
|
FROM receiverInformation WITH ( NOLOCK )
|
|
WHERE receiverId = @receiverId;
|
|
END;
|
|
|
|
IF @flag = 'sDetail'
|
|
BEGIN
|
|
|
|
|
|
--select * from receiverInformation
|
|
|
|
SELECT ri.receiverId ,
|
|
ri.customerId ,
|
|
ri.firstName ,
|
|
ri.membershipId ,
|
|
ri.middleName ,
|
|
ri.lastName1 ,
|
|
ri.lastName2 ,
|
|
ri.country ,
|
|
ri.NativeCountry ,
|
|
NM.countryName,
|
|
ri.address ,
|
|
ri.state ,
|
|
ri.zipCode ,
|
|
cm.countryId ,
|
|
ri.city ,
|
|
ri.email ,
|
|
ri.homePhone ,
|
|
ri.workPhone ,
|
|
ri.mobile ,
|
|
ri.relationship ,
|
|
ri.receiverType ,
|
|
ri.idType ,
|
|
ri.idNumber ,
|
|
ri.placeOfIssue ,
|
|
ri.paymentMode ,
|
|
ri.bankLocation ,
|
|
ri.payOutPartner ,
|
|
ri.bankName ,
|
|
ri.receiverAccountNo ,
|
|
ri.remarks ,
|
|
ri.purposeOfRemit
|
|
FROM receiverInformation ri WITH ( NOLOCK )
|
|
INNER JOIN countryMaster cm WITH ( NOLOCK ) ON ri.country = cm.countryName
|
|
INNER JOIN countryMaster NM WITH ( NOLOCK ) ON NM.countryId = ri.NativeCountry
|
|
WHERE customerId = @customerId
|
|
AND receiverId = @receiverId;
|
|
|
|
|
|
|
|
|
|
END;
|
|
|
|
IF @flag = 'sDetailByCusId'
|
|
BEGIN
|
|
|
|
|
|
--select * from receiverInformation
|
|
|
|
SELECT ri.receiverId ,
|
|
ri.customerId ,
|
|
ri.firstName ,
|
|
ri.membershipId ,
|
|
ri.middleName ,
|
|
ri.lastName1 ,
|
|
ri.lastName2 ,
|
|
ri.country ,
|
|
Ncm.countryName nativeCountry ,
|
|
ri.address ,
|
|
ri.state ,
|
|
ri.zipCode ,
|
|
cm.countryId ,
|
|
ri.city ,
|
|
ri.email ,
|
|
ri.homePhone ,
|
|
ri.workPhone ,
|
|
ri.mobile ,
|
|
ri.relationship ,
|
|
ri.receiverType ,
|
|
ri.idType ,
|
|
ri.idNumber ,
|
|
ri.placeOfIssue ,
|
|
ri.paymentMode ,
|
|
ri.bankLocation ,
|
|
ri.payOutPartner ,
|
|
ri.bankName ,
|
|
ri.receiverAccountNo ,
|
|
ri.remarks ,
|
|
ri.purposeOfRemit
|
|
FROM receiverInformation ri WITH ( NOLOCK )
|
|
INNER JOIN countryMaster cm WITH ( NOLOCK ) ON ri.country = cm.countryName
|
|
INNER JOIN countryMaster Ncm WITH ( NOLOCK ) ON ri.NativeCountry = Ncm.countryId
|
|
WHERE customerId = @customerId;
|
|
|
|
END;
|
|
IF @flag = 'sDetailByReceiverId'
|
|
BEGIN
|
|
--select * from receiverInformation
|
|
|
|
SELECT ri.receiverId ,
|
|
ri.customerId ,
|
|
ri.firstName ,
|
|
com.membershipId ,
|
|
ri.middleName ,
|
|
ri.lastName1 ,
|
|
ri.lastName2 ,
|
|
ri.country ,
|
|
ri.address ,
|
|
ri.state ,
|
|
ri.zipCode ,
|
|
cm.countryId ,
|
|
ri.NativeCountry ,
|
|
ri.city ,
|
|
ri.email ,
|
|
ri.homePhone ,
|
|
ri.workPhone ,
|
|
ri.mobile ,
|
|
ri.relationship ,
|
|
ri.otherRelationDesc,
|
|
ri.purposeOfRemit ,
|
|
ri.receiverType ,
|
|
ri.idType ,
|
|
ri.idNumber ,
|
|
ri.placeOfIssue ,
|
|
ri.paymentMode ,
|
|
ri.bankLocation ,
|
|
ri.payOutPartner ,
|
|
ri.bankName ,
|
|
ri.receiverAccountNo ,
|
|
ri.remarks ,
|
|
ri.createdDate,
|
|
isnull(RI.otherRelationDesc,ri.relationOther) relationOther,
|
|
RI.purposeOther,
|
|
ri.goodsType,
|
|
ri.goodsOrigin,
|
|
ri.portOfShipment
|
|
|
|
FROM receiverInformation ri WITH ( NOLOCK )
|
|
INNER JOIN countryMaster cm WITH ( NOLOCK ) ON ri.country = cm.countryName
|
|
INNER JOIN dbo.customerMaster COM(NOLOCK) ON com.customerId=ri.customerId
|
|
WHERE receiverId = @receiverId;
|
|
RETURN;
|
|
END;
|
|
If @flag = 'sDetailByReceiverIdForPrint'
|
|
BEGIN
|
|
SELECT TOP 10 ri.receiverId ,
|
|
cmm.fullName customerName,
|
|
ri.customerId ,
|
|
ri.firstName ,
|
|
COM.membershipId ,
|
|
ri.middleName ,
|
|
ri.lastName1 ,
|
|
ri.lastName2 ,
|
|
ri.country ,
|
|
ri.address ,
|
|
ri.state ,
|
|
ri.zipCode ,
|
|
cm.countryId ,
|
|
cm1.countryname [NativeCountry],
|
|
ri.city ,
|
|
ri.email ,
|
|
ri.homePhone ,
|
|
ri.workPhone ,
|
|
ri.mobile ,
|
|
SDV.detailTitle relationship,
|
|
ri.otherRelationDesc,
|
|
|
|
SDV1.detailTitle purposeOfRemit,
|
|
SDV2.detailTitle receiverType,
|
|
SDV3.detailTitle idType ,
|
|
ri.idNumber ,
|
|
ri.placeOfIssue ,
|
|
CASE WHEN ri.paymentmode = '1' THEN 'Cash Payment' ELSE 'Bank Deposit' END paymentMode ,
|
|
ISNULL(ABBL.BRANCH_NAME ,'') bankBranchName ,
|
|
CASE WHEN ri.country = 'NEPAL' THEN ISNULL(ABL.BANK_NAME,'ANY WHERE') ELSE ISNULL(ABL.BANK_NAME ,'') END payOutPartner ,
|
|
ri.bankName ,
|
|
ri.receiverAccountNo ,
|
|
ri.remarks ,
|
|
ri.createdDate
|
|
FROM receiverInformation ri WITH ( NOLOCK )
|
|
INNER JOIN countryMaster cm WITH ( NOLOCK ) ON ri.country = cm.countryName
|
|
INNER JOIN dbo.customerMaster COM(NOLOCK) ON com.customerId=ri.customerId
|
|
LEFT JOIN countrymaster CM1 (NOLOCK) ON CM1.COUNTRYID = ri.nativecountry
|
|
LEFT JOIN STATICDATAVALUE SDV (NOLOCK) ON SDV.VALUEID = RI.RELATIONSHIP
|
|
LEFT JOIN STATICDATAVALUE SDV1 (NOLOCK) ON SDV1.VALUEID = RI.PURPOSEOFREMIT
|
|
LEFT JOIN STATICDATAVALUE SDV2 (NOLOCK) ON SDV2.VALUEID = RI.RECEIVERTYPE
|
|
LEFT JOIN STATICDATAVALUE SDV3 (NOLOCK) ON SDV3.VALUEID = RI.idtype
|
|
LEFT JOIN API_BANK_BRANCH_LIST ABBL (NOLOCK) ON cast(ABBL.BRANCH_ID as varchar) = RI.BANKLOCATION
|
|
LEFT JOIN API_BANK_LIST ABL (NOLOCK) ON ABL.BANK_ID = RI.PAYOUTPARTNER
|
|
LEFT JOIN dbo.customerMaster (NOLOCK) cmm ON cmm.customerId = ri.customerId
|
|
WHERE receiverId = @receiverId ;
|
|
RETURN;
|
|
END
|
|
IF @flag = 'getTtranDetail'
|
|
BEGIN
|
|
--select * from receiverInformation
|
|
SELECT ri.receiverId ,
|
|
ri.customerId ,
|
|
receiverName = ISNULL(ri.firstName, '') + ' '
|
|
+ ISNULL(ri.middleName, '') + ' '
|
|
+ ISNULL(ri.lastName1, '') + ' ' + ISNULL(ri.lastName2,
|
|
'')
|
|
--,ri.middleName
|
|
--,ri.lastName1
|
|
--,ri.lastName2
|
|
,
|
|
countryId = ri.country ,
|
|
ri.address ,
|
|
ri.state ,
|
|
ri.zipCode ,
|
|
ri.city ,
|
|
ri.email ,
|
|
phone = ri.homePhone ,
|
|
ri.workPhone ,
|
|
ri.mobile ,
|
|
ri.relationship
|
|
FROM receiverInformation ri WITH ( NOLOCK )
|
|
INNER JOIN countryMaster cm WITH ( NOLOCK ) ON ri.country = cm.countryName
|
|
WHERE customerId = @customerId
|
|
AND receiverId = @receiverId;
|
|
--and email=@user and
|
|
|
|
END;
|
|
|
|
IF @flag = 'recProfile'
|
|
BEGIN
|
|
|
|
SELECT DISTINCT
|
|
ISNULL(TS.tranid, '')
|
|
FROM receiverInformation ri WITH ( NOLOCK )
|
|
LEFT JOIN ( SELECT tranid = rt.id ,
|
|
TS.customerId
|
|
FROM tranSenders TS
|
|
INNER JOIN remitTran rt WITH ( NOLOCK ) ON TS.tranid = rt.id
|
|
AND TS.customerId = @customerId
|
|
) TS ON ri.customerId = TS.customerId
|
|
INNER JOIN tranReceivers tR WITH ( NOLOCK ) ON tR.fullName = ISNULL(ri.firstName,
|
|
'') + ''
|
|
+ ISNULL(ri.middleName,
|
|
'') + ''
|
|
+ ISNULL(ri.lastName1,
|
|
'') + ''
|
|
+ ISNULL(ri.lastName2,
|
|
'')
|
|
AND tR.mobile = ri.mobile
|
|
WHERE ri.receiverId = @receiverId
|
|
AND ri.customerId = @customerId;
|
|
|
|
|
|
--SELECT tranId=tR.tranId,* FROM receiverInformation ri
|
|
-- LEFT JOIN tranReceivers tR ON ri.customerId=tR.customerId
|
|
-- AND tR.fullName=ISNULL(ri.firstName,'') + '' + ISNULL(ri.middleName,'')+ ''
|
|
-- + ISNULL(ri.lastName1,'') + '' + ISNULL(ri.lastName2,'') AND tR.mobile=ri.mobile
|
|
-- WHERE ri.receiverId = @receiverId AND ri.customerId =@customerId
|
|
END;
|
|
END;
|
|
|
|
|
|
--INNER JOIN (
|
|
-- SELECT
|
|
-- receiverId = MAX(receiverId)
|
|
-- ,fullName= ri.firstName + ISNULL('' '' + ri.middleName,'''') + ISNULL('' '' + ri.lastName1,'''') + ISNULL('' '' + ri.lastName2,'''')
|
|
-- FROM receiverInformation ri WITH (NOLOCK)
|
|
-- GROUP BY ri.firstName + ISNULL('' '' + ri.middleName,'''') + ISNULL('' '' + ri.lastName1,'''') + ISNULL('' '' + ri.lastName2,'''')
|
|
--) x ON ri.receiverId = x.receiverId
|
|
IF @flag = 'i-new'
|
|
BEGIN
|
|
|
|
IF NOT EXISTS(SELECT 'X' FROM CUSTOMERMASTER WITH(NOLOCK) WHERE cast(customerId as varchar) = @customerId)
|
|
BEGIN
|
|
SELECT '1' errorCode,''''+cast(@customerId as varchar) + ''' customerId does not exists.' msg,null id
|
|
RETURN
|
|
END
|
|
SELECT @membershipId = membershipId from CUSTOMERMASTER WITH(NOLOCK) WHERE customerId = @customerId
|
|
INSERT INTO receiverInformation
|
|
(fullname
|
|
,membershipId ,customerId ,firstName ,middleName ,lastName1 ,lastName2 ,country ,NativeCountry,[address] ,[state] ,zipCode ,city ,email ,
|
|
homePhone ,workPhone ,mobile ,relationship ,receiverType ,idType ,idNumber ,placeOfIssue ,paymentMode ,bankLocation ,payOutPartner ,
|
|
bankName ,receiverAccountNo ,remarks ,purposeOfRemit ,createdBy ,createdDate,otherRelationDesc,agentId, relationOther, purposeOther, isCDDIUpdated,isactive
|
|
)
|
|
SELECT ISNULL(@firstName,'') + ISNULL(' ' + @middleName,'') +ISNULL(' ' + @lastName1,''),
|
|
@membershipId ,@customerId ,@firstName ,@middleName ,@lastName1 ,@lastName2 ,@country ,@nativeCountry,@address ,@state ,@zipCode ,@city ,@email ,
|
|
@homePhone ,@workPhone ,@mobile ,@RELATION ,@receiverType ,@idType ,@idNumber ,@placeOfIssue ,@paymentMode ,@bankLocation ,@payOutPartner ,
|
|
@bankName ,@receiverAccountNo ,@remarks ,@PURPOSEID ,@user ,GETDATE(),@otherRelationDesc,@loginBranchId, @RelationshipOther, @PurposeOfRemitanceOther, 1,1
|
|
|
|
SET @receiverId=SCOPE_IDENTITY();
|
|
SELECT '0' errorCode ,'Receiver Successfully added.' msg ,id = @receiverId,extra = cast(@customerId as varchar) + '|' + cast(@membershipId as varchar);
|
|
RETURN;
|
|
END;
|
|
|