Stored Procedure - usp_FactPositionExpense_Select

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

Name

Data Type

Direction

Description

Name	Type
ACCT_DISTRIBUTION_A	Table
BENEFIT_HISTORY_A	Table
CHECK_COMPONENT_A	Table
CHECK_SUMMARY_A	Table
PERSONNEL_CODES_1_A	Table

Name	Type
ACCT_DISTRIBUTION_A	TABLE
BENEFIT_HISTORY_A	TABLE
CHECK_COMPONENT_A	TABLE
CHECK_SUMMARY_A	TABLE
PERSONNEL_CODES_1_A	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_FactPositionExpense_Select_Old] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_FactPositionExpense_Select
Title:            Primary Select for the Fact PositionExpense
Date:            06/25/2014
System/Project:    Integrated Financials
Description:    This procedure will combine the PositionExpense information from DWStaging
Revision History:
10/22/2014 Sophia Cowan        Created
*/
CREATE PROC [dbo].[usp_FactPositionExpense_Select_Old]
AS
SET NOCOUNT ON
IF 2=3
    BEGIN
    SELECT
       CAST('aaa' AS VARCHAR(8)) AS CompensationTypeAK
      ,CAST('19000101' AS DATETIME) AS CheckDateAK
      ,CAST('aaa' AS VARCHAR(3)) AS CheckTypeAK
      ,CAST(0 AS INT) AS EmployeeAK
      ,CAST('aaa' AS VARCHAR(8)) AS PositionAK
      ,CAST('aaa' AS VARCHAR(20)) AS AssignmentAK
      ,CAST('aaa' AS VARCHAR(16)) AS OrgUnitAK
      ,CAST('aaa' AS VARCHAR(9)) AS GlCodeAK
      ,CAST('aaa' AS VARCHAR(25))  AS GlAccountAK
      ,CAST(0 AS NUMERIC(11,2)) AS     ExpenseAmount
     END
CREATE TABLE #CompensationComponents
(
   CompensationTypeAK VARCHAR(8) NOT NULL
)
INSERT INTO #CompensationComponents
(
   CompensationTypeAK
)
SELECT CAST(BENEFIT_TYPE_CODE AS VARCHAR(8)) AS CompensationTypeAK
FROM PERSONNEL_CODES_1_A
  WHERE BENEFIT_TYPE_CODE IS NOT NULL
INSERT INTO #CompensationComponents
(
   CompensationTypeAK
)
SELECT CAST(DEDUCTION_TYPE_CODE AS VARCHAR(8)) AS CompensationTypeAK
FROM PERSONNEL_CODES_1_A
  WHERE DEDUCTION_TYPE_CODE IS NOT NULL
INSERT INTO #CompensationComponents
(
   CompensationTypeAK
)
SELECT CAST(TAX_TYPE_CODE AS VARCHAR(8)) AS CompensationTypeAK
FROM PERSONNEL_CODES_1_A
  WHERE TAX_TYPE_CODE IS NOT NULL

SELECT
       CAST(
         CASE
           WHEN LEFT(CheckComp.PAY_CHECK_COMPONENT_TYPE_CODE,2) = '01' THEN 'SALARY'
           WHEN BComp1.CompensationTypeAK IS NOT NULL THEN BComp1.CompensationTypeAK
           WHEN BComp2.CompensationTypeAK IS NOT NULL THEN BComp2.CompensationTypeAK
           ELSE  'UNCTGRZD'
         END
         AS VARCHAR(8)) AS CompensationTypeCode
      ,CAST(ISNULL(CheckComp.PAY_CHECK_DATE,'19000101') AS DATETIME) AS CheckDateAK
      ,CAST(CheckComp.CHECK_TYPE AS VARCHAR(3)) AS CheckTypeAK
      ,ISNULL(CheckComp.PERSON_ID,0) AS EmployeeAK
      ,ISNULL(CheckComp.POSITION_CODE,'N/A') AS PositionAK
      ,CAST(
            REPLICATE('0', 9 - LEN(CheckComp.PERSON_ID)) + CAST(CheckComp.PERSON_ID AS VARCHAR(9))
            +
            CAST(CheckComp.POSITION_CODE AS VARCHAR(8)) + REPLICATE(' ', 8 - LEN(CheckComp.POSITION_CODE))
            +
            REPLICATE('0', 3 - LEN(CheckComp.INTERNAL_SEQ)) + CAST(CheckComp.INTERNAL_SEQ AS VARCHAR(3))
            AS VARCHAR(20)) AS AssignmentAK
      ,LEFT(AcctDist.GL_ACCT_NO,16) AS OrgUnitAK
      ,SUBSTRING(AcctDist.GL_ACCT_NO,17,9) AS GlCodeAK
      ,AcctDist.GL_ACCT_NO AS GlAccountAK
      ,CAST(
         CASE
            WHEN AcctDist.ACCOUNT_AMOUNT IS NULL AND CheckComp.CHECK_TYPE = 'OV' THEN (CheckComp.COMPONENT_AMOUNT * -1)
            WHEN AcctDist.ACCOUNT_AMOUNT IS NULL AND CheckComp.CHECK_TYPE <> 'OV' THEN CheckComp.COMPONENT_AMOUNT
            WHEN CheckComp.CHECK_TYPE = 'OV' THEN (AcctDist.ACCOUNT_AMOUNT * -1)
            ELSE AcctDist.ACCOUNT_AMOUNT
          END AS NUMERIC(11,2)) AS ExpenseAmount
  FROM CHECK_COMPONENT_A CheckComp

  INNER JOIN CHECK_SUMMARY_A CheckSum
  ON
  (
     CheckSum.CHECK_TYPE = CheckComp.CHECK_TYPE
     AND CheckSum.PERSON_ID = CheckComp.PERSON_ID
     AND CheckSum.PAY_CHECK_DATE = CheckComp.PAY_CHECK_DATE
     AND CheckSum.UNPROCESSED_IND = 0
     AND CheckSum.CHECK_VOID_CANCEL_PROCESSED = 0
  )

  INNER JOIN ACCT_DISTRIBUTION_A AcctDist
  ON
  (
     CheckComp.ACCT_INTERNAL_ID = AcctDist.ACCT_INTERNAL_ID
     AND AcctDist.ACCT_DISTRIBUTION_CODE <> 'R'
     AND SUBSTRING(AcctDist.GL_ACCT_NO,17,1) = '5'
  )
  LEFT OUTER JOIN #CompensationComponents BComp1
  ON
  (
     BComp1.CompensationTypeAK = SUBSTRING(CheckComp.PAY_CHECK_COMPONENT_TYPE_CODE,3,8)
  )
  LEFT OUTER JOIN #CompensationComponents BComp2
  ON
  (
     BComp2.CompensationTypeAK = SUBSTRING(CheckComp.PAY_CHECK_COMPONENT_TYPE_CODE,11,8)
  )

  WHERE LEFT(PAY_CHECK_COMPONENT_TYPE_CODE,2) IN ('01', '02')
  AND NOT (LEFT(PAY_CHECK_COMPONENT_TYPE_CODE,2) = '01' AND SUBSTRING(PAY_CHECK_COMPONENT_TYPE_CODE,3,8) IN
    ('TASSIGNR', 'TASSIGNO', 'TAXABLE '))
UNION ALL
SELECT
       CAST(
         CASE
           WHEN LEFT(BenHist.PAY_CHECK_COMPONENT_TYPE_CODE,2) = '01' THEN 'SALARY'
           WHEN BComp1.CompensationTypeAK IS NOT NULL THEN BComp1.CompensationTypeAK
           WHEN BComp2.CompensationTypeAK IS NOT NULL THEN BComp2.CompensationTypeAK
           ELSE  'UNCTGRZD'
         END
         AS VARCHAR(8)) AS CompensationTypeCode
      ,CAST(ISNULL(BenHist.PAY_CHECK_DATE,'19000101') AS DATETIME) AS CheckDateAK
      ,CAST(BenHist.CHECK_TYPE AS VARCHAR(3)) AS CheckTypeAK
      ,ISNULL(BenHist.PERSON_ID,0) AS EmployeeAK
      ,ISNULL(BenHist.POSITION_CODE,'N/A') AS PositionAK
      ,CAST(
            REPLICATE('0', 9 - LEN(BenHist.PERSON_ID)) + CAST(BenHist.PERSON_ID AS VARCHAR(9))
            +
            CAST(BenHist.POSITION_CODE AS VARCHAR(8)) + REPLICATE(' ', 8 - LEN(BenHist.POSITION_CODE))
            +
            REPLICATE('0', 3 - LEN(BenHist.INTERNAL_SEQ)) + CAST(BenHist.INTERNAL_SEQ AS VARCHAR(3))
            AS VARCHAR(20)) AS AssignmentAK
      ,LEFT(AcctDist.GL_ACCT_NO,16) AS OrgUnitAK
      ,SUBSTRING(AcctDist.GL_ACCT_NO,17,9) AS GlCodeAK
      ,AcctDist.GL_ACCT_NO AS GlAccountAK
      ,CAST(
         CASE
            WHEN AcctDist.ACCOUNT_AMOUNT IS NULL AND BenHist.CHECK_TYPE = 'OV' THEN (BenHist.COMPONENT_AMOUNT * -1)
            WHEN AcctDist.ACCOUNT_AMOUNT IS NULL AND BenHist.CHECK_TYPE <> 'OV' THEN BenHist.COMPONENT_AMOUNT
            WHEN BenHist.CHECK_TYPE = 'OV' THEN (AcctDist.ACCOUNT_AMOUNT * -1)
            ELSE AcctDist.ACCOUNT_AMOUNT
          END AS NUMERIC(11,2)) AS ExpenseAmount
  FROM BENEFIT_HISTORY_A  BenHist

  INNER JOIN CHECK_SUMMARY_A CheckSum
  ON
  (
     CheckSum.CHECK_TYPE = BenHist.CHECK_TYPE
     AND CheckSum.PERSON_ID = BenHist.PERSON_ID
     AND CheckSum.PAY_CHECK_DATE = BenHist.PAY_CHECK_DATE
     AND CheckSum.UNPROCESSED_IND = 0
     AND CheckSum.CHECK_VOID_CANCEL_PROCESSED = 0
  )

  INNER JOIN ACCT_DISTRIBUTION_A AcctDist
  ON
  (
     BenHist.ACCT_INTERNAL_ID = AcctDist.ACCT_INTERNAL_ID
     AND AcctDist.ACCT_DISTRIBUTION_CODE <> 'R'
     AND SUBSTRING(AcctDist.GL_ACCT_NO,17,1) = '5'
  )
  LEFT OUTER JOIN #CompensationComponents BComp1
  ON
  (
     BComp1.CompensationTypeAK = SUBSTRING(BenHist.PAY_CHECK_COMPONENT_TYPE_CODE,3,8)
  )
  LEFT OUTER JOIN #CompensationComponents BComp2
  ON
  (
     BComp2.CompensationTypeAK = SUBSTRING(BenHist.PAY_CHECK_COMPONENT_TYPE_CODE,11,8)
  )

  WHERE LEFT(PAY_CHECK_COMPONENT_TYPE_CODE,2) =  '16' AND BENEFIT_HISTORY_TYPE = 'PF'

UNION ALL
SELECT
       CAST(
         CASE
           WHEN LEFT(BenHist.PAY_CHECK_COMPONENT_TYPE_CODE,2) = '01' THEN 'SALARY'
           WHEN BComp1.CompensationTypeAK IS NOT NULL THEN BComp1.CompensationTypeAK
           WHEN BComp2.CompensationTypeAK IS NOT NULL THEN BComp2.CompensationTypeAK
           ELSE  'UNCTGRZD'
         END
         AS VARCHAR(8)) AS CompensationTypeCode
      ,CAST(ISNULL(BenHist.PAY_CHECK_DATE,'19000101') AS DATETIME) AS CheckDateAK
      ,CAST(
         CASE
            WHEN BenHist.CHECK_TYPE = ' ' THEN 'MO'
            ELSE BenHist.CHECK_TYPE
          END AS VARCHAR(3)) AS CheckTypeAK
      ,ISNULL(BenHist.PERSON_ID,0) AS EmployeeAK
      ,ISNULL(BenHist.POSITION_CODE,'N/A') AS PositionAK
      ,CAST(
            REPLICATE('0', 9 - LEN(BenHist.PERSON_ID)) + CAST(BenHist.PERSON_ID AS VARCHAR(9))
            +
            CAST(BenHist.POSITION_CODE AS VARCHAR(8)) + REPLICATE(' ', 8 - LEN(BenHist.POSITION_CODE))
            +
            REPLICATE('0', 3 - LEN(BenHist.INTERNAL_SEQ)) + CAST(BenHist.INTERNAL_SEQ AS VARCHAR(3))
            AS VARCHAR(20)) AS AssignmentAK
      ,LEFT(AcctDist.GL_ACCT_NO,16) AS OrgUnitAK
      ,SUBSTRING(AcctDist.GL_ACCT_NO,17,9) AS GlCodeAK
      ,AcctDist.GL_ACCT_NO AS GlAccountAK
      ,CAST(
         CASE
            WHEN AcctDist.ACCOUNT_AMOUNT IS NULL AND BenHist.CHECK_TYPE = 'OV' THEN (BenHist.COMPONENT_AMOUNT * -1)
            WHEN AcctDist.ACCOUNT_AMOUNT IS NULL AND BenHist.CHECK_TYPE <> 'OV' THEN BenHist.COMPONENT_AMOUNT
            WHEN BenHist.CHECK_TYPE = 'OV' THEN (AcctDist.ACCOUNT_AMOUNT * -1)
            ELSE AcctDist.ACCOUNT_AMOUNT
          END AS NUMERIC(11,2)) AS ExpenseAmount
  FROM BENEFIT_HISTORY_A  BenHist

  INNER JOIN ACCT_DISTRIBUTION_A AcctDist
  ON
  (
     BenHist.ACCT_INTERNAL_ID = AcctDist.ACCT_INTERNAL_ID
     AND AcctDist.ACCT_DISTRIBUTION_CODE <> 'R'
     AND SUBSTRING(AcctDist.GL_ACCT_NO,17,1) = '5'
  )
  LEFT OUTER JOIN #CompensationComponents BComp1
  ON
  (
     BComp1.CompensationTypeAK = SUBSTRING(BenHist.PAY_CHECK_COMPONENT_TYPE_CODE,3,8)
  )
  LEFT OUTER JOIN #CompensationComponents BComp2
  ON
  (
     BComp2.CompensationTypeAK = SUBSTRING(BenHist.PAY_CHECK_COMPONENT_TYPE_CODE,11,8)
  )

  WHERE LEFT(PAY_CHECK_COMPONENT_TYPE_CODE,2) =  '16' AND BENEFIT_HISTORY_TYPE = 'MO'