USE [IAF]
GO
/****** Object:  StoredProcedure [dbo].[insert_log]    Script Date: 8/19/2019 8:18:21 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CustomerSwitch]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[CustomerSwitch]
GO


CREATE FUNCTION [dbo].[CustomerSwitch] ( @branch VARCHAR(10), @midascustomerno BIGINT ) RETURNS VARCHAR(max) AS
BEGIN
	DECLARE @WorkingBranch  VARCHAR(10)
	DECLARE @MCustomerNo    BIGINT
	DECLARE @CustNoPrefix   VARCHAR(1)
	DECLARE @GlobalClientId VARCHAR(255)
	DECLARE @CustType       VARCHAR(1)
	DECLARE @KeyType        VARCHAR(255)
	DECLARE @ReturnCustNo   VARCHAR(max)

	SET @WorkingBranch = @branch
	SET @MCustomerNo   = @midascustomerno

	IF @WorkingBranch = 'SGP'
		BEGIN
			SET @CustNoPrefix = 'A'
			SET @KeyType = 'MIDAS SIN'
		END
	ELSE IF @WorkingBranch = 'MYL'
		BEGIN
			SET @CustNoPrefix = 'B'
			SET @KeyType = 'MIDAS LAB'
		END
	ELSE
		SET @CustNoPrefix = '0'

	IF @CustNoPrefix <> '0'
		BEGIN
			SELECT @GlobalClientId = k.GlobalClientId
			  FROM Keys   k
			 WHERE k.KeyType  = @KeyType
			   AND k.KeyValue = @MCustomerNo

			SELECT @CustType = customer_type
			  FROM flexcustcopy
			 WHERE REPLACE(SUBSTRING(customer_no,2,8),'0','') = @GlobalClientId
			   AND record_stat = 'O'
			   AND frozen       = 'N'
			   AND local_branch = '000'

			IF @CustType IS NOT NULL
				BEGIN
					IF @CustType = 'B'
						SET @ReturnCustNo = '0' + SUBSTRING(REPLICATE('0',8),1,8 - LEN(@GlobalClientId)) + @GlobalClientId
					ELSE
						SET @ReturnCustNo = @CustNoPrefix + SUBSTRING(REPLICATE('0',8),1,8 - LEN(@GlobalClientId)) + @GlobalClientId
				END
			ELSE
				SET @ReturnCustNo = @CustNoPrefix + SUBSTRING(REPLICATE('0',8),1,8 - LEN(@GlobalClientId)) + @GlobalClientId
		END
	ELSE
		SET @ReturnCustNo = -1

	RETURN @ReturnCustNo
END