1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
314.
315.
316.
317.
318.
319.
320.
321.
322.
323.
324.
325.
326.
327.
328.
329.
330.
331.
332.
333.
334.
335.
336.
337.
338.
339.
340.
341.
342.
343.
344.
345.
346.
347.
348.
349.
350.
351.
352.
353.
354.
355.
356.
357.
358.
359.
360.
361.
362.
363.
364.
365.
366.
367.
368.
369.
USE [ODI_STAGE]
GO
/****** Object: View [dbo].[V_USER_AUDIT_FILE_TEST_FINAL_p3] Script Date: 06/01/2020 15:46:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [dbo].[V_USER_AUDIT_FILE_TEST_FINAL_p3]
as
with cte
(
ROOT_ID, ROOT_NAME , OBJECT_ID,OBJECT_NAME,PARENT_ID, lev
)as
(
select o.OBJECT_ID AS ROOT_ID, o.OBJECT_NAME AS ROOT_NAME, o.OBJECT_ID,o.OBJECT_NAME,o.PARENT_ID, 0 as lev
from
(
select * from HYP_DS_TOPDOWN.dbo.HSP_OBJECT
union all
select * from HYP_DS_REVENUE.dbo.HSP_OBJECT
union all
select * from HYP_DS_OPEX.dbo.HSP_OBJECT
union all
select * from HYP_DS_MARGIN.dbo.HSP_OBJECT
union all
select * from HYP_DS_LOGISTIC.dbo.HSP_OBJECT
union all
select * from HYP_DS_hr.dbo.HSP_OBJECT
union all
select * from HYP_DS_DEVPLAN.dbo.HSP_OBJECT
union all
select * from HYP_DS_CONS.dbo.HSP_OBJECT
union all
select * from HYP_DS_CAPEX.dbo.HSP_OBJECT
)
o
inner join
(
select OBJECT_ID,OBJECT_NAME,PARENT_ID from HYP_DS_TOPDOWN.dbo.HSP_OBJECT
union all
select OBJECT_ID,OBJECT_NAME,PARENT_ID from HYP_DS_REVENUE.dbo.HSP_OBJECT
union all
select OBJECT_ID,OBJECT_NAME,PARENT_ID from HYP_DS_OPEX.dbo.HSP_OBJECT
union all
select OBJECT_ID,OBJECT_NAME,PARENT_ID from HYP_DS_MARGIN.dbo.HSP_OBJECT
union all
select OBJECT_ID,OBJECT_NAME,PARENT_ID from HYP_DS_LOGISTIC.dbo.HSP_OBJECT
union all
select OBJECT_ID,OBJECT_NAME,PARENT_ID from HYP_DS_hr.dbo.HSP_OBJECT
union all
select OBJECT_ID,OBJECT_NAME,PARENT_ID from HYP_DS_DEVPLAN.dbo.HSP_OBJECT
union all
select OBJECT_ID,OBJECT_NAME,PARENT_ID from HYP_DS_CONS.dbo.HSP_OBJECT
union all
select OBJECT_ID,OBJECT_NAME,PARENT_ID from HYP_DS_CAPEX.dbo.HSP_OBJECT
) o2 on o.PARENT_ID=o2.OBJECT_ID
and o2.OBJECT_NAME='Account'
union all
select o3.ROOT_ID, o3.ROOT_NAME, o2.OBJECT_ID,o2.OBJECT_NAME,o2.PARENT_ID, o3.lev+1
from
(
select * from HYP_DS_TOPDOWN.dbo.HSP_OBJECT
union all
select * from HYP_DS_REVENUE.dbo.HSP_OBJECT
union all
select * from HYP_DS_OPEX.dbo.HSP_OBJECT
union all
select * from HYP_DS_MARGIN.dbo.HSP_OBJECT
union all
select * from HYP_DS_LOGISTIC.dbo.HSP_OBJECT
union all
select * from HYP_DS_hr.dbo.HSP_OBJECT
union all
select * from HYP_DS_DEVPLAN.dbo.HSP_OBJECT
union all
select * from HYP_DS_CONS.dbo.HSP_OBJECT
union all
select * from HYP_DS_CAPEX.dbo.HSP_OBJECT
)
o2
inner join cte o3 on o2.PARENT_ID=o3.OBJECT_ID
)
------------------------------------------
select distinct
[LOGIN]
,[FIO]
,[ORGANIZATION]
,[DIVISION]
,[DIVISION_FULL]
,[POSITION_U]
,[GROUP_U]
,[DESCRIPTION]
,[GROUP_EFFECTIVE]
,[GROUP_INHERITANCE]
,[APPLICATION]
,[DIMENSION]
,[ARTIFACT_TYPE]
,[ARTIFACT_NAME]
,prn.prn_name as ARTIFACT_NAME_PARENT
,[ARTIFACT_NAME_FULL]
,[ARTIFACT_NAME_QUALIFIED]
,[ARTIFACT_ALIAS]
,[ARTIFACT_ALIAS_FULL]
,[ARTIFACT_ALIAS_QUALIFIED]
,[ACCESS_RIGHTS]
, d.rel as [RELATION]
,[RIGHTS_RATE]
,[RELATION_L]
,[RELATION_ARTIFACT]
from
(
select
c.OBJECT_NAME,
CASE app.FLAGS
WHEN 0 THEN 'Member'
WHEN 5 THEN 'Children'
WHEN 6 THEN 'Children (inclusive)'
WHEN 8 THEN 'Descendants'
WHEN 9 THEN 'Descendants (inclusive)' end rel
from cte c
inner join
(
select cap.OBJECT_ID,cap.FLAGS from HYP_DS_CAPEX.dbo.HSP_ACCESS_CONTROL cap
union
select topd.OBJECT_ID,topd.FLAGS from HYP_DS_TOPDOWN.dbo.HSP_ACCESS_CONTROL topd
union
select rev.OBJECT_ID,rev.FLAGS from HYP_DS_REVENUE.dbo.HSP_ACCESS_CONTROL rev
union
select op.OBJECT_ID,op.FLAGS from HYP_DS_opex.dbo.HSP_ACCESS_CONTROL op
union
select mg.OBJECT_ID,mg.FLAGS from HYP_DS_MARGIN.dbo.HSP_ACCESS_CONTROL mg
union
select lg.OBJECT_ID,lg.FLAGS from HYP_DS_LOGISTIC.dbo.HSP_ACCESS_CONTROL lg
union
select hr.OBJECT_ID,hr.FLAGS from HYP_DS_hr.dbo.HSP_ACCESS_CONTROL hr
union
select dev.OBJECT_ID,dev.FLAGS from HYP_DS_DEVPLAN.dbo.HSP_ACCESS_CONTROL dev
union
select con.OBJECT_ID,con.FLAGS from HYP_DS_CONS.dbo.HSP_ACCESS_CONTROL con
) app on app.OBJECT_ID=c.ROOT_ID )d
inner join
[ODI_STAGE].[dbo].[V_USER_AUDIT_FILE_test] u on d.OBJECT_NAME=u.ARTIFACT_NAME
inner join
(
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_CAPEX.dbo.HSP_OBJECT o
left join HYP_DS_CAPEX.dbo.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_OPEX.DBO.HSP_OBJECT o
left join HYP_DS_OPEX.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_CAPEX.dbo.HSP_OBJECT o
left join HYP_DS_CAPEX.dbo.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_CONS.DBO.HSP_OBJECT o
left join HYP_DS_CONS.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_DEVPLAN.DBO.HSP_OBJECT o
left join HYP_DS_DEVPLAN.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_HR.DBO.HSP_OBJECT o
left join HYP_DS_HR.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_LOGISTIC.DBO.HSP_OBJECT o
left join HYP_DS_LOGISTIC.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_MARGIN.DBO.HSP_OBJECT o
left join HYP_DS_MARGIN.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_OPEX.DBO.HSP_OBJECT o
left join HYP_DS_OPEX.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_REVENUE.DBO.HSP_OBJECT o
left join HYP_DS_REVENUE.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_TOPDOWN.DBO.HSP_OBJECT o
left join HYP_DS_TOPDOWN.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
)prn on u.ARTIFACT_NAME=prn.OBJECT_NAME
where
u.DIMENSION in ('Account')
and d.rel in ('Descendants (inclusive)','Descendants' )
-------------------------------------------------------------------------------------
union
SELECT [LOGIN]
,[FIO]
,[ORGANIZATION]
,[DIVISION]
,[DIVISION_FULL]
,[POSITION_U]
,[GROUP_U]
,[DESCRIPTION]
,[GROUP_EFFECTIVE]
,[GROUP_INHERITANCE]
,[APPLICATION]
,[DIMENSION]
,[ARTIFACT_TYPE]
,[ARTIFACT_NAME]
,prn.prn_name as ARTIFACT_NAME_PARENT
,[ARTIFACT_NAME_FULL]
,[ARTIFACT_NAME_QUALIFIED]
,[ARTIFACT_ALIAS]
,[ARTIFACT_ALIAS_FULL]
,[ARTIFACT_ALIAS_QUALIFIED]
,[ACCESS_RIGHTS]
,[RELATION]
,[RIGHTS_RATE]
,[RELATION_L]
,[RELATION_ARTIFACT]
FROM [ODI_STAGE].[dbo].[V_USER_AUDIT_FILE_TEST] f left join
(
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_CAPEX.dbo.HSP_OBJECT o
left join HYP_DS_CAPEX.dbo.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_OPEX.DBO.HSP_OBJECT o
left join HYP_DS_OPEX.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_CAPEX.dbo.HSP_OBJECT o
left join HYP_DS_CAPEX.dbo.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_CONS.DBO.HSP_OBJECT o
left join HYP_DS_CONS.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_DEVPLAN.DBO.HSP_OBJECT o
left join HYP_DS_DEVPLAN.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_HR.DBO.HSP_OBJECT o
left join HYP_DS_HR.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_LOGISTIC.DBO.HSP_OBJECT o
left join HYP_DS_LOGISTIC.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_MARGIN.DBO.HSP_OBJECT o
left join HYP_DS_MARGIN.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_OPEX.DBO.HSP_OBJECT o
left join HYP_DS_OPEX.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_REVENUE.DBO.HSP_OBJECT o
left join HYP_DS_REVENUE.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_TOPDOWN.DBO.HSP_OBJECT o
left join HYP_DS_TOPDOWN.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
)prn on f.ARTIFACT_NAME=prn.OBJECT_NAME
where
f.DIMENSION in ('Account','CostCenter')
and f.ARTIFACT_NAME not in
------------------------------------------------------------------------------------
(
select distinct ARTIFACT_NAME
from
(
select
c.OBJECT_NAME,
CASE app.FLAGS
WHEN 0 THEN 'Member'
WHEN 5 THEN 'Children'
WHEN 6 THEN 'Children (inclusive)'
WHEN 8 THEN 'Descendants'
WHEN 9 THEN 'Descendants (inclusive)' end rel
from cte c
inner join
(
select cap.OBJECT_ID,cap.FLAGS from HYP_DS_CAPEX.dbo.HSP_ACCESS_CONTROL cap
union
select topd.OBJECT_ID,topd.FLAGS from HYP_DS_TOPDOWN.dbo.HSP_ACCESS_CONTROL topd
union
select rev.OBJECT_ID,rev.FLAGS from HYP_DS_REVENUE.dbo.HSP_ACCESS_CONTROL rev
union
select op.OBJECT_ID,op.FLAGS from HYP_DS_opex.dbo.HSP_ACCESS_CONTROL op
union
select mg.OBJECT_ID,mg.FLAGS from HYP_DS_MARGIN.dbo.HSP_ACCESS_CONTROL mg
union
select lg.OBJECT_ID,lg.FLAGS from HYP_DS_LOGISTIC.dbo.HSP_ACCESS_CONTROL lg
union
select hr.OBJECT_ID,hr.FLAGS from HYP_DS_hr.dbo.HSP_ACCESS_CONTROL hr
union
select dev.OBJECT_ID,dev.FLAGS from HYP_DS_DEVPLAN.dbo.HSP_ACCESS_CONTROL dev
union
select con.OBJECT_ID,con.FLAGS from HYP_DS_CONS.dbo.HSP_ACCESS_CONTROL con
) app on app.OBJECT_ID=c.ROOT_ID )d
inner join
[ODI_STAGE].[dbo].[V_USER_AUDIT_FILE_test] u on d.OBJECT_NAME=u.ARTIFACT_NAME
inner join
(
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_CAPEX.dbo.HSP_OBJECT o
left join HYP_DS_CAPEX.dbo.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_OPEX.DBO.HSP_OBJECT o
left join HYP_DS_OPEX.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_CAPEX.dbo.HSP_OBJECT o
left join HYP_DS_CAPEX.dbo.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_CONS.DBO.HSP_OBJECT o
left join HYP_DS_CONS.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_DEVPLAN.DBO.HSP_OBJECT o
left join HYP_DS_DEVPLAN.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_HR.DBO.HSP_OBJECT o
left join HYP_DS_HR.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_LOGISTIC.DBO.HSP_OBJECT o
left join HYP_DS_LOGISTIC.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_MARGIN.DBO.HSP_OBJECT o
left join HYP_DS_MARGIN.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_OPEX.DBO.HSP_OBJECT o
left join HYP_DS_OPEX.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_REVENUE.DBO.HSP_OBJECT o
left join HYP_DS_REVENUE.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
union
select o.OBJECT_NAME, o2.OBJECT_NAME as prn_name
from HYP_DS_TOPDOWN.DBO.HSP_OBJECT o
left join HYP_DS_TOPDOWN.DBO.HSP_OBJECT o2 on o.PARENT_ID=o2.OBJECT_ID
)prn on u.ARTIFACT_NAME=prn.OBJECT_NAME
where
u.DIMENSION in ('Account')
and d.rel in ('Descendants (inclusive)','Descendants' )
)
GO
