Stored Procedure - usp_FactPositionExpense

Property	Value
Name	usp_FactPositionExpense_Select
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
POSITION_BUDGET_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
POSITION_BUDGET_A	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_FactPositionExpense_Select] 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]
AS
SET NOCOUNT ON
IF 2=3
    BEGIN
    SELECT
       CAST('aaa' AS VARCHAR(8)) AS CompensationTypeAK
      ,CAST('aaa' AS VARCHAR(10)) AS BudgetedAK
      ,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

-- Load Benefits from Position Budget - only Distinct records since there could be more than one
-- We are just trying to determine if these are budgeted.
CREATE TABLE #BenefitBudget
(
    BenefitTypeCode VARCHAR(8) NOT NULL
   ,BenefitPositionCode VARCHAR(8) NOT NULL
   ,BenefitFiscalYear VARCHAR(4) NOT NULL
)
INSERT INTO #BenefitBudget
(
    BenefitTypeCode
   ,BenefitPositionCode
   ,BenefitFiscalYear
)
Select DISTINCT
    PosBudBen.BENEFIT_TYPE_CODE
    ,PosBudBen.BEN_POSITION_CODE
    ,PosBudBen.BEN_FISCAL_YEAR
    from POSITION_BUDGET_A PosBudBen
    WHERE BENEFIT_TYPE_CODE IS NOT NULL
    AND BEN_POSITION_CODE IS NOT NULL
    AND BEN_FISCAL_YEAR IS NOT NULL

SELECT
       CAST('SALARY' AS VARCHAR(8)) AS CompensationTypeAK
      ,CAST(
        CASE
            WHEN PosBudAcct.ACCOUNT_CODE IS NULL THEN CAST('UNBUDGETED' AS VARCHAR(10))
            ELSE CAST('BUDGETED' AS VARCHAR(10))
        END AS VARCHAR(10)) AS BudgetedAK
      ,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 POSITION_BUDGET_A PosBudAcct
  ON
  (
    AcctDist.GL_ACCT_NO = PosBudAcct.ACCOUNT_CODE
    AND CheckComp.POSITION_CODE = PosBudAcct.ACCOUNT_POSITION_CODE
    AND PosBudAcct.ACCOUNT_FISCAL_YEAR =
    CASE
    WHEN (SUBSTRING(CheckComp.PAY_CHECK_DATE,5,2) < '07') THEN
        SUBSTRING(CheckComp.PAY_CHECK_DATE,1,4)
    ELSE
        (CAST(SUBSTRING(CheckComp.PAY_CHECK_DATE,1,4) AS numeric(4)) + 1)
    END
    AND (CheckComp.PAY_CHECK_DATE >= PosBudAcct.ACCOUNT_CODE_START_DATE
        AND CheckComp.PAY_CHECK_DATE <= PosBudAcct.ACCOUNT_CODE_END_DATE)
  )

  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 BComp1.CompensationTypeAK IS NOT NULL THEN BComp1.CompensationTypeAK
           WHEN BComp2.CompensationTypeAK IS NOT NULL THEN BComp2.CompensationTypeAK
           ELSE  'N/A'
         END
         AS VARCHAR(8)) AS CompensationTypeAK
       ,CAST(
        CASE
            WHEN PosBudBen.BenefitTypeCode IS NULL THEN CAST('UNBUDGETED' AS VARCHAR(10))
            ELSE CAST('BUDGETED' AS VARCHAR(10))
        END AS VARCHAR(10)) AS BudgetedAK
      ,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)
  )

  LEFT OUTER JOIN #BenefitBudget PosBudBen
  ON
  (
    SUBSTRING(BenHist.PAY_CHECK_COMPONENT_TYPE_CODE,3,8) = PosBudBen.BenefitTypeCode
    AND PosBudBen.BenefitPositionCode = BenHist.POSITION_CODE
    AND PosBudBen.BenefitFiscalYear =
    CASE
    WHEN (SUBSTRING(BenHist.PAY_CHECK_DATE,5,2) < '07') THEN
        SUBSTRING(BenHist.PAY_CHECK_DATE,1,4)
    ELSE
        (CAST(SUBSTRING(BenHist.PAY_CHECK_DATE,1,4) AS numeric(4)) + 1)
    END
  )

  WHERE LEFT(PAY_CHECK_COMPONENT_TYPE_CODE,2) =  '16' AND BENEFIT_HISTORY_TYPE = 'PF'
UNION ALL
SELECT
       CAST(
         CASE
           WHEN BComp1.CompensationTypeAK IS NOT NULL THEN BComp1.CompensationTypeAK
           WHEN BComp2.CompensationTypeAK IS NOT NULL THEN BComp2.CompensationTypeAK
           ELSE  'N/A'
         END
         AS VARCHAR(8)) AS CompensationTypeAK
       ,CAST(
        CASE
            WHEN PosBudBen.BenefitTypeCode IS NULL THEN CAST('UNBUDGETED' AS VARCHAR(10))
            ELSE CAST('BUDGETED' AS VARCHAR(10))
        END AS VARCHAR(10)) AS BudgetedAK
      ,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 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)
  )

  LEFT OUTER JOIN #BenefitBudget PosBudBen
  ON
  (
    SUBSTRING(BenHist.PAY_CHECK_COMPONENT_TYPE_CODE,3,8) = PosBudBen.BenefitTypeCode
    AND PosBudBen.BenefitPositionCode = BenHist.POSITION_CODE
    AND PosBudBen.BenefitFiscalYear =
    CASE
    WHEN (SUBSTRING(BenHist.PAY_CHECK_DATE,5,2) < '07') THEN
        SUBSTRING(BenHist.PAY_CHECK_DATE,1,4)
    ELSE
        (CAST(SUBSTRING(BenHist.PAY_CHECK_DATE,1,4) AS numeric(4)) + 1)
    END
  )

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