Guest User

Untitled

a guest
Dec 9th, 2019
86
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