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.
85 lines
6.6 KiB
85 lines
6.6 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[FNAGetCustomerAddress] Script Date: 11/10/2023 1:15:27 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
|
|
|
|
ALTER FUNCTION [dbo].[FNAGetCustomerAddress]
|
|
(
|
|
@customerOrTranId BIGINT
|
|
, @addressFor VARCHAR(20) = NULL
|
|
)
|
|
RETURNS NVARCHAR(250)
|
|
AS
|
|
------------------------------------------
|
|
--#134 -> Allow edit option of address in Town Area
|
|
--#180 -> SHOW KANJI AND ROMAN TEXT IN ADDRESS STATE AND CITY
|
|
-- ADDED NEW IF CLAUSE 'CUST-SEARCH-DETAILS'
|
|
--#474 -> Remove Kanji character from SOA ma statement
|
|
-- #1320 Remove khanji character from search transaction
|
|
------------------------------------------------------------------------
|
|
BEGIN
|
|
DECLARE @customerAddress NVARCHAR(250)
|
|
IF ISNULL(@addressFor, '') = ''
|
|
SET @addressFor = 'CUST-SEARCH'
|
|
|
|
IF @addressFor IN ('TXN-RECEIPT', 'TXN-SEARCH')
|
|
BEGIN
|
|
SELECT @customerAddress = ISNULL(substring(s.zipcode,1,3),'')+'-'+ ISNULL(substring(s.zipcode,4,7),'')
|
|
+ isnull(', ' + ss.statename, '')
|
|
+ isnull(', ' + s.address, '')
|
|
+ISNULL( ', '+ s.address2, '')
|
|
FROM VWTRANSENDERS S(NOLOCK)
|
|
LEFT JOIN dbo.countryStateMaster SS(NOLOCK) ON CAST(SS.stateId AS VARCHAR) = s.state
|
|
WHERE S.TRANID = @customerOrTranId
|
|
END
|
|
ELSE IF @addressFor IN ('CUST-SEARCH')
|
|
BEGIN
|
|
SELECT @customerAddress = ISNULL(CM.zipcode,'')
|
|
+ isnull(', ' + CM.address, '')
|
|
+ ISNULL( ', '+ UPPER(CM.ADDITIONALADDRESS), '')
|
|
FROM CUSTOMERMASTER CM(NOLOCK)
|
|
--INNER JOIN tbl_japan_address_detail detail WITH (NOLOCK) ON detail.zip_code = CM.zipcode
|
|
WHERE CM.CUSTOMERID = @customerOrTranId
|
|
END
|
|
ELSE IF @addressFor IN ('CUST-SEARCH-NEW')
|
|
BEGIN
|
|
SELECT @customerAddress = ISNULL(substring(CM.zipcode,1,3),'')+'-'+ ISNULL(substring(CM.zipcode,4,7),'')
|
|
+ isnull(', ' + detail.STATE_ENG, '')
|
|
+ isnull(', ' + detail.city_name, '')
|
|
+ isnull( ', ' + detail.street_name, '')
|
|
+ ISNULL( ', '+ UPPER(CM.ADDITIONALADDRESS), '')
|
|
FROM CUSTOMERMASTER CM(NOLOCK)
|
|
INNER JOIN tbl_japan_address_detail detail WITH (NOLOCK) ON detail.zip_code = CM.zipcode
|
|
WHERE CM.CUSTOMERID = @customerOrTranId
|
|
END
|
|
ELSE IF @addressFor IN ('CUST-SEARCH-DETAILS')
|
|
BEGIN
|
|
SELECT @customerAddress = ISNULL(substring(CM.zipcode,1,3),'')+'-'+ ISNULL(substring(CM.zipcode,4,7),'')
|
|
+ isnull(', ' + detail.STATE_ENG, '') + ISNULL(', ' + STATE_JAPANESE, '')
|
|
+ isnull(', ' + detail.city_name, '') + ISNULL(', ' + CITY_JAPANESE, '')
|
|
+ isnull(', ' + CM.STREETUNICODE, ', ' + detail.street_name)
|
|
+ ISNULL( ', '+ UPPER(CM.ADDITIONALADDRESS), '')
|
|
FROM CUSTOMERMASTER CM(NOLOCK)
|
|
INNER JOIN tbl_japan_address_detail detail WITH (NOLOCK) ON detail.zip_code = CM.zipcode
|
|
WHERE CM.CUSTOMERID = @customerOrTranId
|
|
END
|
|
ELSE IF @addressFor IN ('SEARCH-TXN')
|
|
BEGIN
|
|
SELECT @customerAddress = ISNULL(substring(CM.zipcode,1,3),'')+'-'+ ISNULL(substring(CM.zipcode,4,7),'')
|
|
+ isnull(', ' + detail.STATE_ENG, '')
|
|
+ isnull(', ' + detail.city_name, '')
|
|
+ isnull(', ' + CM.street,'')
|
|
+ ISNULL( ', '+ UPPER(CM.ADDITIONALADDRESS), '')
|
|
FROM CUSTOMERMASTER CM(NOLOCK)
|
|
INNER JOIN tbl_japan_address_detail detail WITH (NOLOCK) ON detail.zip_code = CM.zipcode
|
|
WHERE CM.CUSTOMERID = @customerOrTranId
|
|
END
|
|
RETURN @customerAddress
|
|
END
|
|
|