· 6 years ago · Jun 12, 2019, 10:20 AM
1
2IF OBJECT_ID('[fn_AllChargeUnit_ValidTabsData_ActualsByCUOriginalCode]') IS NOT NULL
3BEGIN
4 DROP FUNCTION [dbo].[fn_AllChargeUnit_ValidTabsData_ActualsByCUOriginalCode]
5END
6
7GO
8
9CREATE FUNCTION [dbo].[fn_AllChargeUnit_ValidTabsData_ActualsByCUOriginalCode]
10 (@PeriodID int,
11 @CompanyID int,
12 @CUOriginalCode varchar(50) = null,
13 @ExpenseCostCode varchar(100) = null,
14 @ReturnOverriddenCUs bit = 0,
15 @ReturnUnallocatedACUs bit = 0,
16 @TargetObjectID int = null,
17 @TargetAppliesTo int = null)
18
19RETURNS @return TABLE
20(
21 CompanyID int,
22 PeriodID int,
23 MUID int,
24 ElementType varchar(3),
25 CUID_ACUID int,
26 CU_ACU_OriginalCode varchar(50),
27 OverriddenCU bit,
28 ExpenseCodeGroupID int,
29 ExpenseCostCode varchar(100),
30 ExpenseCodeID int,
31 IsDefaultExpenseCode bit,
32 LatestActualsPeriodID int,
33 LatestNonZeroActualsPeriodID int
34)
35
36AS
37
38BEGIN
39
40 IF @ExpenseCostCode = ''
41 BEGIN
42 SET @ExpenseCostCode = NULL
43 END
44
45 IF @CUOriginalCode = ''
46 BEGIN
47 SET @CUOriginalCode = NULL
48 END
49
50 DECLARE @AltCUsEnabled bit = 0
51
52 IF EXISTS (SELECT * FROM ApplicationSettings WHERE SettingKey = 'MANAGEALTCU' AND CompanyID IN (0,@CompanyID) AND SettingValue IN ('1','true','Yes','Y'))
53 BEGIN
54 SET @AltCUsEnabled = 1
55 END
56
57 -- If a "Target Applies To" has been specified without an ObjectID, or vice versa, assume no targetting
58
59 IF ISNULL(@TargetAppliesTo,-1) <> -1
60 AND ISNULL(@TargetObjectID,-1) = -1
61 BEGIN
62 SET @TargetAppliesTo = NULL
63 END
64
65 IF ISNULL(@TargetAppliesTo,-1) = -1
66 AND ISNULL(@TargetObjectID,-1) <> -1
67 BEGIN
68 SET @TargetObjectID = NULL
69 END
70
71 -- Derive the "Applies To" Enum key for the targetting
72
73 DECLARE @TargetAppliesToEnumKey varchar(100) = NULL
74 IF ISNULL(@TargetAppliesTo,-1) <> -1
75 BEGIN
76 SELECT @TargetAppliesToEnumKey = AppliesToEnumKey FROM AppliesToDefinition WHERE AppliesToDefinitionID = ISNULL(@TargetAppliesTo,-1)
77 END
78
79 -- Set up the basic data to return
80
81 DECLARE @Tmp table
82 (
83 CompanyID int,
84 PeriodID int,
85 MUID int,
86 ElementType varchar(3),
87 CUID_ACUID int,
88 CU_ACU_OriginalCode varchar(50),
89 OverriddenCU bit,
90 ExpenseCodeGroupID int
91 )
92
93 INSERT INTO @Tmp
94 SELECT CompanyID, PeriodID, MUID, ElementType, CUID_ACUID, CU_ACU_OriginalCode, CAST(0 AS bit), ExpenseCodeGroupID
95 FROM vw_AllChargeUnit_ExpenseCodeGroups
96 WHERE PeriodID = @PeriodID
97 AND CompanyID = @CompanyID
98 AND CU_ACU_OriginalCode = ISNULL(@CUOriginalCode,CU_ACU_OriginalCode)
99 --AND ElementType = CASE WHEN @AltCUsEnabled = 1 THEN ElementType ELSE 'CU' END
100
101 -- Alt Charge Units override conventional Charge Units on a given tab:
102
103 UPDATE t
104 SET t.OverriddenCU = 1
105 FROM @Tmp t
106 WHERE ElementType = 'CU'
107 AND EXISTS (SELECT * FROM @Tmp WHERE MUID = t.MUID AND ExpenseCodeGroupID = t.ExpenseCodeGroupID AND PeriodID = t.PeriodID AND ElementType = 'ACU')
108
109 -- Join to Expense Code, determine latest budget periods and populate return data
110
111 INSERT INTO @return
112 SELECT t.CompanyID, t.PeriodID, t.MUID, t.ElementType, t.CUID_ACUID, t.CU_ACU_OriginalCode, t.OverriddenCU,
113 t.ExpenseCodeGroupID, ec.CostCode, ec.ExpenseCodeID, ec.DefaultCode, MAX(muat.AppliesToPeriodID), MAX(muatnz.AppliesToPeriodID)
114 FROM @Tmp t
115 INNER JOIN ExpenseCode ec
116 ON ec.ExpenseCodeGroupID = t.ExpenseCodeGroupID
117 AND ec.CompanyID IN(0,@CompanyID)
118 AND @PeriodID BETWEEN ec.StartPeriod AND ISNULL(ec.EndPeriod,9999)
119 LEFT JOIN ExpenseCodeCategory ecc
120 ON ecc.ExpenseCodeCategoryID = ec.CategoryID
121 AND ecc.CompanyID IN (0,@CompanyID)
122 AND ecc.ExpenseCodeGroupID = t.ExpenseCodeGroupID
123 AND @PeriodID BETWEEN ecc.StartPeriod AND ISNULL(ecc.EndPeriod,9999)
124 LEFT JOIN ManagementUnitActualTransactions muat
125 ON muat.MUID = t.MUID
126 AND muat.ExpenseCodeID = ec.ExpenseCodeID
127 AND muat.AppliesToPeriodID <= @PeriodID -- Consider all periods up to and including that for which the function is being run
128 LEFT JOIN ManagementUnitActualTransactions muatnz
129 ON muatnz.MUID = t.MUID
130 AND muatnz.ExpenseCodeID = ec.ExpenseCodeID
131 AND muatnz.AppliesToPeriodID <= @PeriodID -- Consider all periods up to and including that for which the function is being run
132 AND muatnz.Value <> CAST(0.00 AS money)
133 WHERE ec.CostCode = ISNULL(@ExpenseCostCode,ec.CostCode)
134 AND t.OverriddenCU = CASE WHEN @ReturnOverriddenCUs = 0 THEN 0 ELSE t.OverriddenCU END
135 AND (ElementType <> 'ACU' OR @ReturnUnallocatedACUs = 1 OR t.CU_ACU_OriginalCode <> 'UnAllocated')
136 AND ISNULL(@TargetObjectID,-1) =
137 CASE ISNULL(@TargetAppliesToEnumKey,'')
138 WHEN '' THEN ISNULL(@TargetObjectID,-1) -- No targetting
139 WHEN 'EXPENSECODE' THEN ec.ExpenseCodeID
140 WHEN 'EXPENSECODEGROUP' THEN t.ExpenseCodeGroupID
141 WHEN 'EXPENSECODECATEGORY' THEN ISNULL(ecc.ExpenseCodeCategoryID, -1)
142 END
143 GROUP BY t.CompanyID, t.PeriodID, t.MUID, t.ElementType, t.CUID_ACUID, t.CU_ACU_OriginalCode, t.OverriddenCU, t.ExpenseCodeGroupID, ec.CostCode, ec.ExpenseCodeID, ec.DefaultCode
144 ORDER BY t.CompanyID, t.PeriodID, t.MUID, t.CU_ACU_OriginalCode, ec.CostCode, t.OverriddenCU, ISNULL(MAX(muat.AppliesToPeriodID),0) DESC, ec.DefaultCode DESC, t.ExpenseCodeGroupID, t.CUID_ACUID, ec.ExpenseCodeID
145
146 RETURN
147
148END