Stored Procedure - usp_DimCheckComponentType

Property	Value
Name	usp_DimCheckComponentType_Select
Schema	dbo
Is Encrypted	False
Ansi Nulls Status	True
Quoted Identifier Status	True
Description

Name

Data Type

Direction

Description

Name	Type
BENEFIT_HISTORY_A	Table
CHECK_COMPONENT_A	Table
PERSONNEL_CODES_1_A	Table
TIME_TYPE_A	Table
UTL_CODE_TABLE	Table

Name	Type
UTL_CODE_TABLE	TABLE
BENEFIT_HISTORY_A	TABLE
CHECK_COMPONENT_A	TABLE
PERSONNEL_CODES_1_A	TABLE
TIME_TYPE_A	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_DimCheckComponentType_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/* Primary CheckComponentType Source SELECT Statement */
/* Sophia Cowan 09/03/2014 Created for HR/Payroll Dashboards */
CREATE PROC [dbo].[usp_DimCheckComponentType_Select]
AS
SET NOCOUNT ON
IF 2=3
    BEGIN
    SELECT
      CAST('aaa' AS VARCHAR(18)) AS CheckComponentTypeAK
     ,CAST('aaa' AS VARCHAR(3)) AS CheckComponentLineCode
     ,CAST('aaa' AS VARCHAR(50)) AS ComponentLine
     ,CAST('aaa' AS VARCHAR(8)) AS ComponentOneCode
     ,CAST('aaa' AS VARCHAR(50)) AS ComponentOne
     ,CAST('aaa' AS VARCHAR(8)) AS  ComponentTwoCode
     ,CAST('aaa' AS VARCHAR(50)) AS ComponentTwo
     ,CAST('aaa' AS VARCHAR(15)) AS ComponentCategory
    END


CREATE TABLE #ComponentType
(
    ComponentLineTemp VARCHAR(18) NOT NULL
)

INSERT INTO #ComponentType
(
  ComponentLineTemp
)
  SELECT
     LEFT(PAY_CHECK_COMPONENT_TYPE_CODE,18)
  FROM CHECK_COMPONENT_A
  WHERE LEFT(PAY_CHECK_COMPONENT_TYPE_CODE,2) <> '17'
  GROUP BY LEFT(PAY_CHECK_COMPONENT_TYPE_CODE,18)
INSERT INTO #ComponentType
(
   ComponentLineTemp
)
   SELECT CAST(CAST('17' AS VARCHAR(2)) + 'BANK' + REPLICATE(' ' ,4) +
            'N/A' + REPLICATE(' ' ,5) AS VARCHAR(18))  AS ComponentLineTemp
INSERT INTO #ComponentType
(
  ComponentLineTemp
)
  SELECT
     LEFT(PAY_CHECK_COMPONENT_TYPE_CODE,18)
  FROM BENEFIT_HISTORY_A
  GROUP BY LEFT(PAY_CHECK_COMPONENT_TYPE_CODE,18)

SELECT
     CAST(
       CASE
         WHEN LEFT(ComponentLineTemp,2) = '17' THEN
            CAST(LEFT(ComponentLineTemp,2) AS VARCHAR(2)) +
            'BANK' + REPLICATE(' ' ,4) +
            'N/A' + REPLICATE(' ' ,5)
         WHEN SUBSTRING(ComponentLineTemp,11,8) = ' ' THEN
            CAST(LEFT(ComponentLineTemp,10) AS VARCHAR(10)) +
            'N/A' + REPLICATE(' ' ,5)
         ELSE LEFT(ComponentLineTemp,18)
         END AS VARCHAR(18))
       AS CheckComponentTypeAK
     ,CAST(LEFT(ComponentLineTemp,2) AS VARCHAR(3)) AS CheckComponentLineCode
     ,CAST(
       CASE
         WHEN LEFT(ComponentLineTemp,2) = '01' THEN  'Gross'
         WHEN LEFT(ComponentLineTemp,2) = '02' THEN  'Gross Adjustment'
         WHEN LEFT(ComponentLineTemp,2) = '03' THEN  'Benefit'
         WHEN LEFT(ComponentLineTemp,2) = '04' THEN  'Benefit Adjustment'
         WHEN LEFT(ComponentLineTemp,2) = '05' THEN  'Taxable Benefit'
         WHEN LEFT(ComponentLineTemp,2) = '06' THEN  'Taxable Benefit Adjustment'
         WHEN LEFT(ComponentLineTemp,2) = '07' THEN  'Pre Tax Deduction'
         WHEN LEFT(ComponentLineTemp,2) = '08' THEN  'Pre Tax Deduction Adjustment'
         WHEN LEFT(ComponentLineTemp,2) = '09' THEN  'Total Gross'
         WHEN LEFT(ComponentLineTemp,2) = '10' THEN  'Tax'
         WHEN LEFT(ComponentLineTemp,2) = '11' THEN  'Tax Adjustment'
         WHEN LEFT(ComponentLineTemp,2) = '12' THEN  'Gross Minus Taxes'
         WHEN LEFT(ComponentLineTemp,2) = '13' THEN  'Post Tax Deduction'
         WHEN LEFT(ComponentLineTemp,2) = '14' THEN  'Post Tax Adjustment'
         WHEN LEFT(ComponentLineTemp,2) = '15' THEN  'Net'
         WHEN LEFT(ComponentLineTemp,2) = '16' THEN  'Benefit Accounts'
         WHEN LEFT(ComponentLineTemp,2) = '17' THEN  'ACH Entry'
         WHEN LEFT(ComponentLineTemp,2) = '18' THEN  'CU Entry'
         WHEN LEFT(ComponentLineTemp,2) = '19' THEN  'Live Check'
         WHEN LEFT(ComponentLineTemp,2) = '20' THEN  'Time Type Used'
         ELSE 'N/A'
       END AS VARCHAR(50)) AS ComponentLine
     ,SUBSTRING(ComponentLineTemp,3,8) AS ComponentOneCode
     ,CASE
        WHEN PayComp.DESCRIPTION IS NOT NULL THEN PayComp.DESCRIPTION
        WHEN TaxType.DESCRIPTION IS NOT NULL THEN TaxType.DESCRIPTION
        WHEN BenType.DESCRIPTION IS NOT NULL THEN BenType.DESCRIPTION
        WHEN DedType.DESCRIPTION IS NOT NULL THEN DedType.DESCRIPTION
        WHEN TimeType.DESCRIPTION IS NOT NULL THEN TimeType.DESCRIPTION
        WHEN BenefitType.DESCRIPTION IS NOT NULL THEN BenefitType.DESCRIPTION
        WHEN Deduction.DESCRIPTION IS NOT NULL THEN Deduction.DESCRIPTION
        WHEN SUBSTRING(ComponentLineTemp,3,4) = 'BANK' THEN 'Bank Account'
        ELSE 'N/A'
      END AS ComponentOne
    ,CAST(
       CASE
         WHEN SUBSTRING(ComponentLineTemp,11,8) = ' ' THEN 'N/A'
         ELSE SUBSTRING(ComponentLineTemp,11,8)
       END AS VARCHAR(8)) AS  ComponentTwoCode
    ,CAST(
       CASE
         WHEN SUBSTRING(ComponentLineTemp,11,3) LIKE '[0-9][0-9][0-9]' THEN 'Assignment ' + SUBSTRING(SUBSTRING(ComponentLineTemp,11,3), PATINDEX('%[^0]%', SUBSTRING(ComponentLineTemp,11,3)+'.'), LEN(SUBSTRING(ComponentLineTemp,11,3)))
         WHEN TaxType2.DESCRIPTION IS NOT NULL THEN TaxType2.DESCRIPTION
         WHEN BenType2.DESCRIPTION IS NOT NULL THEN BenType2.DESCRIPTION
         WHEN DedType2.DESCRIPTION IS NOT NULL THEN DedType2.DESCRIPTION
         WHEN TimeType2.DESCRIPTION IS NOT NULL THEN TimeType2.DESCRIPTION
         WHEN BenefitType2.DESCRIPTION IS NOT NULL THEN BenefitType2.DESCRIPTION
         WHEN Deduction2.DESCRIPTION IS NOT NULL THEN Deduction2.DESCRIPTION
         ELSE 'N/A'
       END AS VARCHAR(50)) AS ComponentTwo
    ,CAST(
       CASE
         WHEN LEFT(ComponentLineTemp,2) IN ('01','02','09','12') THEN 'Gross'
         WHEN LEFT(ComponentLineTemp,2) IN ('03','04','16') THEN 'Benefit'
         WHEN LEFT(ComponentLineTemp,2) IN ('05','06') THEN 'Taxable Benefit'
         WHEN LEFT(ComponentLineTemp,2) IN ('07','08','13','14') THEN 'Deduction'
         WHEN LEFT(ComponentLineTemp,2) IN ('10','11') THEN 'Tax'
         WHEN LEFT(ComponentLineTemp,2) = '15' THEN 'Net'
         WHEN LEFT(ComponentLineTemp,2) IN ('17','18','19') THEN 'Pay Check'
         WHEN LEFT(ComponentLineTemp,2) = '20' THEN 'Time'
         ELSE 'N/A'
       END AS VARCHAR(15)) AS ComponentCategory
  FROM #ComponentType

  LEFT OUTER JOIN UTL_CODE_TABLE PayComp
  ON
  (
     PayComp.TABLE_NAME = 'PAYGRCMPNT'
     AND PayComp.CODE = SUBSTRING(ComponentLineTemp,3,8)
     AND PayComp.STATUS = 'A'
  )

  LEFT OUTER JOIN PERSONNEL_CODES_1_A TaxType
  ON
  (
     TaxType.TAX_TYPE_CODE = SUBSTRING(ComponentLineTemp,3,8)
     AND TaxType.TAX_TYPE_CODE IS NOT NULL
  )

  LEFT OUTER JOIN PERSONNEL_CODES_1_A BenType
  ON
  (
     BenType.BENEFIT_TYPE_CODE = SUBSTRING(ComponentLineTemp,3,8)
     AND BenType.BENEFIT_TYPE_CODE IS NOT NULL
  )
  LEFT OUTER JOIN PERSONNEL_CODES_1_A DedType
  ON
  (
     DedType.DEDUCTION_TYPE_CODE = SUBSTRING(ComponentLineTemp,3,8)
     AND DedType.DEDUCTION_TYPE_CODE IS NOT NULL
  )

  LEFT OUTER JOIN TIME_TYPE_A TimeType
  ON
  (
     TimeType.TIME_TYPE_CODE = SUBSTRING(ComponentLineTemp,3,8)
     AND TimeType.TIME_TYPE_CODE IS NOT NULL
  )

  LEFT OUTER JOIN PERSONNEL_CODES_1_A BenefitType
  ON
  (
     BenefitType.BENEFIT_CODE = SUBSTRING(ComponentLineTemp,3,8)
     AND BenefitType.BENEFIT_CODE IS NOT NULL
  )

  LEFT OUTER JOIN PERSONNEL_CODES_1_A Deduction
  ON
  (
     Deduction.DEDUCTION_CODE = SUBSTRING(ComponentLineTemp,3,8)
     AND Deduction.DEDUCTION_CODE IS NOT NULL
  )
  LEFT OUTER JOIN PERSONNEL_CODES_1_A TaxType2
  ON
  (
     TaxType2.TAX_TYPE_CODE = SUBSTRING(ComponentLineTemp,11,8)
     AND TaxType2.TAX_TYPE_CODE IS NOT NULL
  )

  LEFT OUTER JOIN PERSONNEL_CODES_1_A BenType2
  ON
  (
     BenType2.BENEFIT_TYPE_CODE = SUBSTRING(ComponentLineTemp,11,8)
     AND BenType2.BENEFIT_TYPE_CODE IS NOT NULL
  )
  LEFT OUTER JOIN PERSONNEL_CODES_1_A DedType2
  ON
  (
     DedType2.DEDUCTION_TYPE_CODE = SUBSTRING(ComponentLineTemp,11,8)
     AND DedType2.DEDUCTION_TYPE_CODE IS NOT NULL
  )

  LEFT OUTER JOIN TIME_TYPE_A TimeType2
  ON
  (
     TimeType2.TIME_TYPE_CODE = SUBSTRING(ComponentLineTemp,11,8)
     AND TimeType2.TIME_TYPE_CODE IS NOT NULL
  )

  LEFT OUTER JOIN PERSONNEL_CODES_1_A BenefitType2
  ON
  (
     BenefitType2.BENEFIT_CODE = SUBSTRING(ComponentLineTemp,11,8)
     AND BenefitType2.BENEFIT_CODE IS NOT NULL
  )

  LEFT OUTER JOIN PERSONNEL_CODES_1_A Deduction2
  ON
  (
     Deduction2.DEDUCTION_CODE = SUBSTRING(ComponentLineTemp,11,8)
     AND Deduction2.DEDUCTION_CODE IS NOT NULL
  )

ORDER BY ComponentLineTemp