· 7 years ago · Dec 19, 2018, 07:34 AM
1CREATE FUNCTION [dbo].[GInterval](@App_ID int, @Setting_ID int,@Entity_ID
2 int, @Item_ID int)
3 RETURNS int
4 AS
5BEGIN
6DECLARE @SIntVal int
7DECLARE @Error int
8/* This code snipit is taken directly from GInterval because
9 stored procedures can not be called from within functions*/
10
11DECLARE @SP_Nme varchar(40)
12DECLARE @H_Base int
13DECLARE @S_Ent_ID int
14DECLARE @S_Itm_ID int
15DECLARE @AppEntSet_Act int
16DECLARE @AppEntSVal_Act int
17DECLARE @Appl_Item_ID int
18DECLARE @Usr_Ent_ID int
19DECLARE @Cl_Ent_ID int
20DECLARE @Ofc_Ent_ID int
21DECLARE @PrsCenter_Ent_ID int
22DECLARE @Appl_Ent_ID int
23DECLARE @UpstreamHit tinyint
24DECLARE @SrcEnt_ID INT
25
26DECLARE @Appl_ID_loc int
27DECLARE @Set_ID_loc int
28DECLARE @Ent_ID_loc int
29DECLARE @Itm_ID_loc int
30
31SELECT @Appl_ID_loc=@App_ID, @Set_ID_loc=@Setting_ID,
32@Ent_ID_loc=@Entity_ID, @Itm_ID_loc=@Item_ID
33
34SET @SP_Nme = Object_Name(@@PROCID)
35SET @Usr_Ent_ID = 1
36SET @Cl_Ent_ID = 2
37SET @Ofc_Ent_ID = 3
38SET @PrsCenter_Ent_ID = 4
39SET @Appl_Ent_ID = 5
40SET @AppEntSet_Act = 297
41SET @AppEntSVal_Act = 299
42SET @UpstreamHit = 0
43
44SET @SIntVal=NULL
45SET @SrcEnt_ID=NULL
46
47SELECT @SIntVal = AESV.intVal,
48 @SrcEnt_ID = @Ent_ID_loc
49FROM GEICO.dbo.ApplicationEntitySettingVal AESV (nolock)
50WHERE AESV.Application_ID = @Appl_ID_loc
51AND AESV.Setting_ID = @Set_ID_loc
52AND AESV.Entity_ID = @Ent_ID_loc
53AND AESV.Status_ID = @AppEntSVal_Act
54AND AESV.Item_ID = @Itm_ID_loc
55
56IF @@error <> 0
57BEGIN
58 RETURN -999999999
59END
60
61IF ((@SIntVal IS NULL) AND (@SrcEnt_ID IS NULL))
62BEGIN
63
64
65
66 SELECT @H_Base = HierarchyRank
67 FROM GEICO.dbo.ApplicationEntitySetting AES (nolock)
68 WHERE AES.Application_ID = @Appl_ID_loc
69 AND AES.Setting_ID = @Set_ID_loc
70 AND AES.Entity_ID = @Ent_ID_loc
71
72 IF @H_Base IS NULL
73 SET @H_Base = 0
74
75 DECLARE @upstream_temp TABLE (Entity_ID INT, Item_ID INT)
76
77 INSERT INTO @upstream_temp(Entity_ID,Item_ID)
78 SELECT Entity_ID,Item_ID FROM GEICO.dbo.fn_Setting_GetUpstreamIDs(@Ent_ID_loc, @Itm_ID_loc)
79
80 DECLARE SettingChk cursor LOCAL FAST_FORWARD READ_ONLY
81 FOR
82 SELECT Entity_ID
83 FROM GEICO.dbo.ApplicationEntitySetting AES (nolock)
84 WHERE AES.Application_ID = @Appl_ID_loc
85 AND AES.Setting_ID = @Set_ID_loc
86 AND AES.Status_ID = @AppEntSVal_Act
87 AND AES.HierarchyRank > @H_Base
88 ORDER BY AES.HierarchyRank
89
90
91 OPEN SettingChk
92
93 WHILE (10=10)
94 BEGIN
95
96 FETCH NEXT
97 FROM SettingChk
98 INTO @Search_Entity_ID
99
100 IF (@@fetch_status <> 0)
101 BEGIN
102 CLOSE SettingChk
103 DEALLOCATE SettingChk
104 BREAK
105 END
106
107
108
109 SELECT @S_Itm_ID = Item_ID FROM @upstream_temp
110 WHERE Entity_ID = @S_Ent_ID
111
112 --Need to get application ID since it was not returned by the function of getupstreamids.
113 if @S_Ent_ID = @Appl_Ent_ID
114 SELECT @S_Itm_ID = @Appl_ID_loc
115
116
117 IF @S_Itm_ID IS NOT NULL
118 BEGIN
119
120 IF EXISTS
121 (
122 SELECT TOP 1 1
123 FROM GEICO.dbo.ApplicationEntitySettingVal AESV (nolock)
124 WHERE AESV.Application_ID = @Appl_ID_loc
125 AND AESV.Setting_ID = @Set_ID_loc
126 AND AESV.Entity_ID = @S_Ent_ID
127 AND AESV.Status_ID = @AppEntSVal_Act
128 AND AESV.Item_ID = @S_Itm_ID
129 )
130 BEGIN
131 SELECT @SIntVal = AESV.intVal,
132 @SrcEnt_ID = @S_Ent_ID
133 FROM GEICO.dbo.ApplicationEntitySettingVal AESV (nolock)
134 WHERE AESV.Application_ID = @Appl_ID_loc
135 AND AESV.Setting_ID = @Set_ID_loc
136 AND AESV.Entity_ID = @S_Ent_ID
137 AND AESV.Status_ID = @AppEntSVal_Act
138 AND AESV.Item_ID = @S_Itm_ID
139
140 IF @@error <> 0
141 BEGIN
142 CLOSE SettingChk
143 DEALLOCATE SettingChk
144 RETURN -999999999
145 END
146
147 --Exit the loop
148 SET @UpstreamHit = 1
149 BREAK
150 END
151 END -- Make sure Search_Item_ID is not null
152 END -- Loop
153
154 IF @UpstreamHit = 0
155 BEGIN
156 RETURN -999999999
157 END
158
159 CLOSE SettingChk
160 DEALLOCATE SettingChk
161END
162
163SELECT
164 @Error = @@Error
165
166IF @Error <> 0
167BEGIN
168 SET @SIntVal = -999999999
169END
170
171RETURN @SIntVal
172 END