SHARE
TWEET

Untitled

a guest Dec 9th, 2019 75 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE [IAF]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[insert_log]    Script Date: 8/19/2019 8:18:21 AM ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. 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'))
  9. DROP FUNCTION [dbo].[CustomerSwitch]
  10. GO
  11.  
  12.  
  13. CREATE FUNCTION [dbo].[CustomerSwitch] ( @branch VARCHAR(10), @midascustomerno BIGINT ) RETURNS VARCHAR(max) AS
  14. BEGIN
  15.     DECLARE @WorkingBranch  VARCHAR(10)
  16.     DECLARE @MCustomerNo    BIGINT
  17.     DECLARE @CustNoPrefix   VARCHAR(1)
  18.     DECLARE @GlobalClientId VARCHAR(255)
  19.     DECLARE @CustType       VARCHAR(1)
  20.     DECLARE @KeyType        VARCHAR(255)
  21.     DECLARE @ReturnCustNo   VARCHAR(max)
  22.  
  23.     SET @WorkingBranch = @branch
  24.     SET @MCustomerNo   = @midascustomerno
  25.  
  26.     IF @WorkingBranch = 'SGP'
  27.         BEGIN
  28.             SET @CustNoPrefix = 'A'
  29.             SET @KeyType = 'MIDAS SIN'
  30.         END
  31.     ELSE IF @WorkingBranch = 'MYL'
  32.         BEGIN
  33.             SET @CustNoPrefix = 'B'
  34.             SET @KeyType = 'MIDAS LAB'
  35.         END
  36.     ELSE
  37.         SET @CustNoPrefix = '0'
  38.  
  39.     IF @CustNoPrefix <> '0'
  40.         BEGIN
  41.             SELECT @GlobalClientId = k.GlobalClientId
  42.               FROM Keys   k
  43.              WHERE k.KeyType  = @KeyType
  44.                AND k.KeyValue = @MCustomerNo
  45.  
  46.             SELECT @CustType = customer_type
  47.               FROM flexcustcopy
  48.              WHERE REPLACE(SUBSTRING(customer_no,2,8),'0','') = @GlobalClientId
  49.                AND record_stat = 'O'
  50.                AND frozen       = 'N'
  51.                AND local_branch = '000'
  52.  
  53.             IF @CustType IS NOT NULL
  54.                 BEGIN
  55.                     IF @CustType = 'B'
  56.                         SET @ReturnCustNo = '0' + SUBSTRING(REPLICATE('0',8),1,8 - LEN(@GlobalClientId)) + @GlobalClientId
  57.                     ELSE
  58.                         SET @ReturnCustNo = @CustNoPrefix + SUBSTRING(REPLICATE('0',8),1,8 - LEN(@GlobalClientId)) + @GlobalClientId
  59.                 END
  60.             ELSE
  61.                 SET @ReturnCustNo = @CustNoPrefix + SUBSTRING(REPLICATE('0',8),1,8 - LEN(@GlobalClientId)) + @GlobalClientId
  62.         END
  63.     ELSE
  64.         SET @ReturnCustNo = -1
  65.  
  66.     RETURN @ReturnCustNo
  67. END
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top