DWStaging
 usp_FactPositionBudget_Select (Stored Procedure)
Home  Diagrams  Documents  Images  DWStaging  Storage  Users  Roles  Schemas  Tables  Views  Indexes  Triggers  Stored Procedures  Functions  Data Types  XML Schemas  Rules  Defaults  Fulltext Catalogs  Synonyms  Keys  Partitions  Assemblies 
Current Path:  DWStaging   Stored Procedures   usp_FactPositionBudget_Select
  Properties
Property Value
Name usp_FactPositionBudget_Select
Schema dbo
Is Encrypted False
Ansi Nulls Status True
Quoted Identifier Status True
Description
  Parameters
Name Data Type Direction Description
  Parent Dependencies (objects that usp_FactPositionBudget_Select depends on)
Name Type
Table
Table
  Child Dependencies (objects that depend on usp_FactPositionBudget_Select)
Name Type
TABLE
TABLE
  Extended Properties
Object Property Value
   Annotations
Object Property Value
  DDL
/****** Object: StoredProcedure [dbo].[usp_FactPositionBudget_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/* Primary Position Budget Source SELECT Statement including Incremental Modifications*/
CREATE PROC [dbo].[usp_FactPositionBudget_Select]
AS 
SET NOCOUNT ON
-- Extract Salary data from Position Budget
SELECT 
       'SALARY' AS CompensationTypeAK
      ,CAST(ISNULL(SalBud.ACCOUNT_CODE_START_DATE, '19000101') AS DATETIME) AS StartDateAK
      ,CAST(ISNULL(SalBud.ACCOUNT_CODE_END_DATE, '20981231') AS DATETIME) AS EndDateAK       
      ,SalBud.ACCOUNT_POSITION_CODE AS PositionAK
      ,LEFT(SalBud.ACCOUNT_CODE,16) AS OrgUnitAK 
      ,SUBSTRING(SalBud.ACCOUNT_CODE,17,9) AS GlCodeAK 
      ,SalBud.ACCOUNT_CODE AS GlAccountCodeAK      
      ,CAST(
         CASE
           WHEN PosBud.REVISED_BUDGET_AMT IS NULL THEN 0
-- Calculate Number of days the budget is for, work out the percentage of the year and multiply by the account Percentage
           ELSE ROUND(PosBud.REVISED_BUDGET_AMT * CAST(ROUND(((CAST(DATEDIFF(DD, CAST(SalBud.ACCOUNT_CODE_START_DATE AS DATE), CAST(SalBud.ACCOUNT_CODE_END_DATE AS DATE)) AS NUMERIC(5,2)) + 1) / 365),2) AS NUMERIC(5,2)) * ISNULL(SalBud.ACCOUNT_PERC,0) / 100,2)
         END AS NUMERIC(11,2)) AS BudgetAmount
FROM POSITION_BUDGET_A SalBud
INNER JOIN POSITION_BUDGET_A PosBud
ON
(
   PosBud.POSITION_CODE = SalBud.ACCOUNT_POSITION_CODE
   AND PosBud.FISCAL_YEAR = SalBud.ACCOUNT_FISCAL_YEAR
   AND PosBud.POSITION_CODE IS NOT NULL
)
WHERE SalBud.ACCOUNT_POSITION_CODE IS NOT NULL 
      AND SalBud.ACCOUNT_PRIMARY_IND = '1'
      AND SalBud.ACCOUNT_FISCAL_YEAR > '2007'
UNION ALL
-- Extract Position Budget Data for Benefits
SELECT 
      BenBud.BENEFIT_TYPE_CODE AS CompensationTypeAK
      ,CAST(ISNULL(PosBudget.ACCOUNT_CODE_START_DATE, '19000101') AS DATETIME) AS StartDateAK
      ,CAST(ISNULL(PosBudget.ACCOUNT_CODE_END_DATE, '20981231') AS DATETIME) AS EndDateAK       
      ,PosBudget.ACCOUNT_POSITION_CODE AS PositionAK
      ,LEFT(PosBudget.ACCOUNT_CODE,16) AS OrgUnitAK 
      ,SUBSTRING(PosBudget.ACCOUNT_CODE,17,9) AS GlCodeAK 
      ,PosBudget.ACCOUNT_CODE AS GlAccountCodeAK     
      ,CAST(
         CASE
           WHEN BenBud.BEN_ORIGINAL_BUDGET_AMT IS NULL THEN 0
-- Calculate Number of days the budget is for, work out the percentage of the year and multiply by the account Percentage
           ELSE ROUND(BenBud.BEN_ORIGINAL_BUDGET_AMT * CAST(ROUND(((CAST(DATEDIFF(DD, CAST(PosBudget.ACCOUNT_CODE_START_DATE AS DATE), CAST(PosBudget.ACCOUNT_CODE_END_DATE AS DATE)) AS NUMERIC(5,2)) + 1) / 365),2) AS NUMERIC(5,2)) * ISNULL(BenBud.ACCOUNT_PERC,0) / 100, 2)
         END AS NUMERIC(11,2)) AS BudgetAmount
FROM POSITION_BUDGET_A BenBud
INNER JOIN POSITION_BUDGET_A PosBudget
ON
(
   PosBudget.ACCOUNT_POSITION_CODE = BenBud.BEN_POSITION_CODE
   AND (PosBudget.BENEFIT_IND = '1'
   OR (PosBudget.BENEFIT_IND <> '1' AND PosBudget.ACCOUNT_PRIMARY_IND = '1'))   
   AND PosBudget.ACCOUNT_FISCAL_YEAR = BenBud.BEN_FISCAL_YEAR   
)
INNER JOIN PERSONNEL_CODES_1_A BenType
ON
( 
   BenType.BENEFIT_TYPE_CODE = BenBud.BENEFIT_TYPE_CODE
)
WHERE  BenBud.BEN_POSITION_CODE IS NOT NULL 
     AND (PosBudget.BENEFIT_IND = '1'
   OR (PosBudget.BENEFIT_IND <> '1' AND PosBudget.ACCOUNT_PRIMARY_IND = '1'))     
      AND BenBud.BEN_FISCAL_YEAR > '2007'
UNION ALL
-- Extract the Position Budget data for Taxes
SELECT 
      BenBud.BENEFIT_TYPE_CODE AS CompensationTypeAK
      ,CAST(ISNULL(PosBudget.ACCOUNT_CODE_START_DATE, '19000101') AS DATETIME) AS StartDateAK
      ,CAST(ISNULL(PosBudget.ACCOUNT_CODE_END_DATE, '20981231') AS DATETIME) AS EndDateAK       
      ,PosBudget.ACCOUNT_POSITION_CODE AS PositionAK
      ,LEFT(PosBudget.ACCOUNT_CODE,16) AS OrgUnitAK 
      ,SUBSTRING(PosBudget.ACCOUNT_CODE,17,9) AS GlCodeAK 
      ,PosBudget.ACCOUNT_CODE AS GlAccountCodeAK     
      ,CAST(
         CASE
           WHEN BenBud.BEN_ORIGINAL_BUDGET_AMT IS NULL THEN 0
-- Calculate Number of days the budget is for, work out the percentage of the year and multiply by the account Percentage
           ELSE ROUND(BenBud.BEN_ORIGINAL_BUDGET_AMT  * CAST(ROUND(((CAST(DATEDIFF(DD, CAST(PosBudget.ACCOUNT_CODE_START_DATE AS DATE), CAST(PosBudget.ACCOUNT_CODE_END_DATE AS DATE)) AS NUMERIC(5,2)) + 1) / 365),2) AS NUMERIC(5,2)) * ISNULL(BenBud.ACCOUNT_PERC,0) / 100, 2)
         END AS NUMERIC(11,2)) AS BudgetAmount
FROM 
POSITION_BUDGET_A BenBud
INNER JOIN POSITION_BUDGET_A PosBudget
ON
(
   PosBudget.ACCOUNT_POSITION_CODE = BenBud.BEN_POSITION_CODE
   AND (PosBudget.BENEFIT_IND = '1'
   OR (PosBudget.BENEFIT_IND <> '1' AND PosBudget.ACCOUNT_PRIMARY_IND = '1'))
   AND PosBudget.ACCOUNT_FISCAL_YEAR = BenBud.BEN_FISCAL_YEAR   
)
INNER JOIN PERSONNEL_CODES_1_A TaxType
ON
( 
   TaxType.TAX_TYPE_CODE = BenBud.BENEFIT_TYPE_CODE
)
WHERE BenBud.BEN_POSITION_CODE IS NOT NULL 
      AND (PosBudget.BENEFIT_IND = '1'
   OR (PosBudget.BENEFIT_IND <> '1' AND PosBudget.ACCOUNT_PRIMARY_IND = '1'))
      AND BenBud.BEN_FISCAL_YEAR > '2007'
Powered by BI Documenter