· 7 years ago · Jan 18, 2019, 01:52 AM
1CREATE TABLE [dbo].[AccessRoleMaster]
2(
3 [Id] [int] IDENTITY (1,1) NOT NULL,
4 [NAME] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
5 [IsActive] [bit] NULL,
6 [CreatedDate] [datetime] NULL,
7 CONSTRAINT [PK__AccessRo__3214EC07D1C045B7] PRIMARY KEY CLUSTERED
8 (
9 [Id] ASC
10 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
11) ON [PRIMARY]
12GO
13
14CREATE TABLE [dbo].[PrimaryRoleMaster]
15(
16 [Id] [int] IDENTITY (1,1) NOT NULL,
17 [NAME] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
18 [IsActive] [bit] NULL,
19 [CreatedDate] [datetime] NULL,
20 CONSTRAINT [PK__PrimaryR__3214EC07B06E27A6] PRIMARY KEY CLUSTERED
21 (
22 [Id] ASC
23 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
24) ON [PRIMARY]
25GO
26
27--SELECT * FROM dbo.Emp_Master em WHERE em.IsActive = 1--6785,6569
28--SELECT * FROM dbo.Emp_Master em WHERE em.IsRMR = 1--9878
29--SELECT * FROM dbo.Emp_Master em WHERE em.IsRMR = 0--8
30ALTER PROC [dbo].[USP_Utility_RefreshEmpMaster]
31AS
32BEGIN
33 --DECLARE @tblEmpMasterSrc TABLE (
34 -- [EMPID] [varchar](50) NOT NULL
35 -- ,[Name] [varchar](101) NULL
36 -- ,[Email] [nvarchar](255) NULL
37 -- ,[NT_ID] [nvarchar](400) NULL
38 -- ,[Status] [nvarchar](255) NULL
39 -- ,[Type] [nvarchar](255) NULL
40 -- ,[support_city] [nvarchar](255) NULL
41 -- ,[City] [nvarchar](255) NULL
42 -- ,[Job_code] [nvarchar](255) NULL
43 -- ,[D_O_J] [nvarchar](255) NULL
44 -- ,[MGR_ID] [nvarchar](255) NULL
45 -- ,[EmployeeFirstName] [nvarchar](255) NULL
46 -- ,[EmployeeLastName] [nvarchar](255) NULL
47 -- ,[EmployeeType] [nvarchar](255) NULL
48 -- ,[EmployeeCategory] [nvarchar](255) NULL
49 -- ,[PrimaryRole] [nvarchar](255) NULL
50 -- ,[SecondaryRole] [nvarchar](255) NULL
51 -- ,[FunctUseDesc] [nvarchar](255) NULL
52 -- ,[ManagerName] [nvarchar](255) NULL
53 -- ,[ManagerID] [nvarchar](255) NULL
54 -- ,[BusRegion] [nvarchar](255) NULL
55 -- ,[Region] [nvarchar](255) NULL
56 -- ,[Country] [nvarchar](255) NULL
57 -- ,[WorkLocation] [nvarchar](255) NULL
58 -- ,[PWorkLocationDesc] [nvarchar](255) NULL
59 -- ,[FacilityDesc] [nvarchar](255) NULL
60 -- ,[FTE] [float] NULL
61 -- ,[ServiceTower] [nvarchar](255) NULL
62 -- ,[SecServiceTower] [nvarchar](255) NULL
63 -- ,[ServiceGroup] [nvarchar](255) NULL
64 -- ,[DeliveryType] [nvarchar](255) NULL
65 -- ,[ServiceTowerCode] [nvarchar](255) NULL
66 -- ,[SecServiceTowerCode] [nvarchar](255) NULL
67 -- ,[DeliveryTypeCode] [nvarchar](255) NULL
68 -- ,[ServiceGroupCode] [nvarchar](255) NULL
69 -- ,[Org] [nvarchar](255) NULL
70 -- ,[OrgLeader] [nvarchar](255) NULL
71 -- ,[FileDate] [datetime] NULL
72 -- ,[IsRMR] [bit] NULL
73 -- ,[ManagerName2] [nvarchar](255) NULL
74 -- ,[ManagerID2] [nvarchar](255) NULL
75 -- ,[ManagerName3] [nvarchar](255) NULL
76 -- ,[ManagerID3] [nvarchar](255) NULL
77 -- )
78 --INSERT INTO @tblEmpMasterSrc (
79 -- EMPID
80 -- ,NAME
81 -- ,Email
82 -- ,NT_ID
83 -- ,STATUS
84 -- ,Type
85 -- ,support_city
86 -- ,City
87 -- ,Job_code
88 -- ,D_O_J
89 -- ,MGR_ID
90 -- ,EmployeeFirstName
91 -- ,EmployeeLastName
92 -- ,EmployeeType
93 -- ,EmployeeCategory
94 -- ,PrimaryRole
95 -- ,SecondaryRole
96 -- ,FunctUseDesc
97 -- ,ManagerName
98 -- ,ManagerID
99 -- ,BusRegion
100 -- ,Region
101 -- ,Country
102 -- ,WorkLocation
103 -- ,PWorkLocationDesc
104 -- ,FacilityDesc
105 -- ,FTE
106 -- ,ServiceTower
107 -- ,SecServiceTower
108 -- ,ServiceGroup
109 -- ,DeliveryType
110 -- ,ServiceTowerCode
111 -- ,SecServiceTowerCode
112 -- ,DeliveryTypeCode
113 -- ,ServiceGroupCode
114 -- ,Org
115 -- ,OrgLeader
116 -- ,FileDate
117 -- ,IsRMR
118 -- ,ManagerName2
119 -- ,ManagerID2
120 -- ,ManagerName3
121 -- ,ManagerID3
122 -- )
123 --SELECT EMPID
124 -- ,NAME
125 -- ,Email
126 -- ,NT_ID
127 -- ,STATUS
128 -- ,Type
129 -- ,support_city
130 -- ,City
131 -- ,Job_code
132 -- ,D_O_J
133 -- ,MGR_ID
134 -- ,EmployeeFirstName
135 -- ,EmployeeLastName
136 -- ,EmployeeType
137 -- ,EmployeeCategory
138 -- ,PrimaryRole
139 -- ,SecondaryRole
140 -- ,FunctUseDesc
141 -- ,ManagerName
142 -- ,ManagerID
143 -- ,BusRegion
144 -- ,Region
145 -- ,Country
146 -- ,WorkLocation
147 -- ,PWorkLocationDesc
148 -- ,FacilityDesc
149 -- ,FTE
150 -- ,ServiceTower
151 -- ,SecServiceTower
152 -- ,ServiceGroup
153 -- ,DeliveryType
154 -- ,ServiceTowerCode
155 -- ,SecServiceTowerCode
156 -- ,DeliveryTypeCode
157 -- ,ServiceGroupCode
158 -- ,Org
159 -- ,OrgLeader
160 -- ,FileDate
161 -- ,IsRMR
162 -- ,ManagerName2
163 -- ,ManagerID2
164 -- ,ManagerName3
165 -- ,ManagerID3
166 --FROM [master].[dbo].[Emp_Master]
167 --SELECT * FROM @tblEmpMasterSrc tems
168 ------------2. Inactivate all records
169 UPDATE emd
170 SET emd.IsActive = 0
171 FROM dbo.Emp_Master emd
172
173 --where emd.IsRMR = 1
174 --LEFT JOIN @tblEmpMasterSrc ems ON emd.EMPID = ems.EMPID
175 --WHERE emd.IsRMR = 1
176 -- AND ems.EMPID IS NULL
177 ------------3. Activate and update all fields if record exists in latest source
178 UPDATE emd
179 SET emd.NAME = ems.NAME
180 ,emd.Email = ems.Email
181 ,emd.NT_ID = ems.NT_ID
182 ,emd.STATUS = ems.STATUS
183 ,emd.Type = ems.Type
184 ,emd.support_city = ems.support_city
185 ,emd.City = ems.City
186 ,emd.Job_code = ems.Job_code
187 ,emd.D_O_J = ems.D_O_J
188 ,emd.MGR_ID = ems.MGR_ID
189 ,emd.EmployeeFirstName = ems.EmployeeFirstName
190 ,emd.EmployeeLastName = ems.EmployeeLastName
191 ,emd.EmployeeType = ems.EmployeeType
192 ,emd.EmployeeCategory = ems.EmployeeCategory
193 ,emd.PrimaryRole = ems.PrimaryRole
194 ,emd.SecondaryRole = ems.SecondaryRole
195 ,emd.FunctUseDesc = ems.FunctUseDesc
196 ,emd.ManagerName = ems.ManagerName
197 ,emd.ManagerID = ems.ManagerID
198 ,emd.BusRegion = ems.BusRegion
199 ,emd.Region = ems.Region
200 ,emd.Country = ems.Country
201 ,emd.WorkLocation = ems.WorkLocation
202 ,emd.PWorkLocationDesc = ems.PWorkLocationDesc
203 ,emd.FacilityDesc = ems.FacilityDesc
204 ,emd.FTE = ems.FTE
205 ,emd.ServiceTower = ems.ServiceTower
206 ,emd.SecServiceTower = ems.SecServiceTower
207 ,emd.ServiceGroup = ems.ServiceGroup
208 ,emd.DeliveryType = ems.DeliveryType
209 ,emd.ServiceTowerCode = ems.ServiceTowerCode
210 ,emd.SecServiceTowerCode = ems.SecServiceTowerCode
211 ,emd.DeliveryTypeCode = ems.DeliveryTypeCode
212 ,emd.ServiceGroupCode = ems.ServiceGroupCode
213 ,emd.Org = ems.Org
214 ,emd.OrgLeader = ems.OrgLeader
215 ,emd.FileDate = ems.FileDate
216 ,emd.IsRMR = ems.IsRMR
217 ,emd.ManagerName2 = ems.ManagerName2
218 ,emd.ManagerID2 = ems.ManagerID2
219 ,emd.ManagerName3 = ems.ManagerName3
220 ,emd.ManagerID3 = ems.ManagerID3
221 ,emd.IsActive = 1
222 FROM dbo.Emp_Master emd
223 INNER JOIN [master].[dbo].[Emp_Master] ems ON emd.EMPID = ems.EMPID
224
225 --WHERE emd.IsRMR = 1
226 ------------4. Insert records which are not in table but in latest source
227 INSERT INTO dbo.Emp_Master (
228 EMPID
229 ,NAME
230 ,Email
231 ,NT_ID
232 ,STATUS
233 ,Type
234 ,support_city
235 ,City
236 ,Job_code
237 ,D_O_J
238 ,MGR_ID
239 ,EmployeeFirstName
240 ,EmployeeLastName
241 ,EmployeeType
242 ,EmployeeCategory
243 ,PrimaryRole
244 ,SecondaryRole
245 ,FunctUseDesc
246 ,ManagerName
247 ,ManagerID
248 ,BusRegion
249 ,Region
250 ,Country
251 ,WorkLocation
252 ,PWorkLocationDesc
253 ,FacilityDesc
254 ,FTE
255 ,ServiceTower
256 ,SecServiceTower
257 ,ServiceGroup
258 ,DeliveryType
259 ,ServiceTowerCode
260 ,SecServiceTowerCode
261 ,DeliveryTypeCode
262 ,ServiceGroupCode
263 ,Org
264 ,OrgLeader
265 ,FileDate
266 ,IsRMR
267 ,ManagerName2
268 ,ManagerID2
269 ,ManagerName3
270 ,ManagerID3
271 ,IsActive
272 )
273 SELECT ems.EMPID
274 ,ems.NAME
275 ,ems.Email
276 ,ems.NT_ID
277 ,ems.STATUS
278 ,ems.Type
279 ,ems.support_city
280 ,ems.City
281 ,ems.Job_code
282 ,ems.D_O_J
283 ,ems.MGR_ID
284 ,ems.EmployeeFirstName
285 ,ems.EmployeeLastName
286 ,ems.EmployeeType
287 ,ems.EmployeeCategory
288 ,ems.PrimaryRole
289 ,ems.SecondaryRole
290 ,ems.FunctUseDesc
291 ,ems.ManagerName
292 ,ems.ManagerID
293 ,ems.BusRegion
294 ,ems.Region
295 ,ems.Country
296 ,ems.WorkLocation
297 ,ems.PWorkLocationDesc
298 ,ems.FacilityDesc
299 ,ems.FTE
300 ,ems.ServiceTower
301 ,ems.SecServiceTower
302 ,ems.ServiceGroup
303 ,ems.DeliveryType
304 ,ems.ServiceTowerCode
305 ,ems.SecServiceTowerCode
306 ,ems.DeliveryTypeCode
307 ,ems.ServiceGroupCode
308 ,ems.Org
309 ,ems.OrgLeader
310 ,ems.FileDate
311 ,ems.IsRMR
312 ,ems.ManagerName2
313 ,ems.ManagerID2
314 ,ems.ManagerName3
315 ,ems.ManagerID3
316 ,1
317 FROM [master].[dbo].[Emp_Master] ems
318 LEFT JOIN dbo.Emp_Master emd ON ems.EMPID = emd.EMPID
319 WHERE emd.EMPID IS NULL
320
321 EXEC USP_Utility_AddEngineersWithAllNonSecuredL2Nodes
322
323 EXEC USP_Utility_RefreshAdminAccessToBaseTree
324
325 --Add New Primary Roles
326 INSERT INTO dbo.PrimaryRoleMaster (
327 NAME
328 ,IsActive
329 ,CreatedDate
330 )
331 SELECT DISTINCT em.PrimaryRole
332 ,1
333 ,getdate()
334 FROM dbo.Emp_Master em
335 LEFT JOIN dbo.PrimaryRoleMaster prm ON em.PrimaryRole = prm.NAME
336 WHERE em.PrimaryRole IS NOT NULL
337 AND prm.NAME IS NULL
338 ORDER BY em.PrimaryRole
339END
340GO
341DROP USER [hc9w01648_ro]
342GO