Contents

1 По бизнес-правилу получить форму с контекстным меню и задачу

1.1 Получаем название меню

SELECT tbl3.OBJECT_NAME TaskName
	  ,tbl2.OBJECT_NAME SubTaskName
	  ,tbl4.OBJECT_NAME Form_Name
  FROM [hpl_app07].[dbo].[HSP_TASK] tbl1
  left join [hpl_app07].[dbo].[HSP_OBJECT] tbl2
	ON tbl1.TASK_ID=tbl2.Object_id
  left join [hpl_app07].[dbo].[HSP_OBJECT] tbl3
	ON tbl1.TASK_LIST_ID=tbl3.Object_id
  left join [hpl_app07].[dbo].[HSP_OBJECT] tbl4
	ON tbl1.INT_PROP1=tbl4.OBJECT_ID
  WHERE tbl1.TASK_TYPE=2
  and tbl4.OBJECT_NAME IN (
	  SELECT tbl3.OBJECT_NAME
	  FROM [hpl_app07].[dbo].[HSP_OBJECT] tbl1
	  left join [hpl_app07].[dbo].[HSP_FORM_MENUS] tbl2
		ON tbl1.[OBJECT_ID]=tbl2.MENU_ID
	  left join [hpl_app07].[dbo].[HSP_OBJECT] tbl3
		ON tbl2.FORM_ID=tbl3.OBJECT_ID
	  WHERE tbl1.OBJECT_NAME='br0208.05' 
	  and tbl1.OBJECT_TYPE=103
	  and tbl2.FORM_ID is not null
  )
  ORDER BY TASK_LIST_ID

SELECT tbl3.OBJECT_NAME TaskName

,tbl2.OBJECT_NAME SubTaskName

,tbl4.OBJECT_NAME Form_Name

FROM [hpl_app07].[dbo].[HSP_TASK] tbl1

left join [hpl_app07].[dbo].[HSP_OBJECT] tbl2

ON tbl1.TASK_ID=tbl2.Object_id

left join [hpl_app07].[dbo].[HSP_OBJECT] tbl3

ON tbl1.TASK_LIST_ID=tbl3.Object_id

left join [hpl_app07].[dbo].[HSP_OBJECT] tbl4

ON tbl1.INT_PROP1=tbl4.OBJECT_ID

WHERE tbl1.TASK_TYPE=2

and tbl4.OBJECT_NAME IN (

SELECT tbl3.OBJECT_NAME

FROM [hpl_app07].[dbo].[HSP_OBJECT] tbl1

left join [hpl_app07].[dbo].[HSP_FORM_MENUS] tbl2

ON tbl1.[OBJECT_ID]=tbl2.MENU_ID

left join [hpl_app07].[dbo].[HSP_OBJECT] tbl3

ON tbl2.FORM_ID=tbl3.OBJECT_ID

WHERE tbl1.OBJECT_NAME='br0208.05'

and tbl1.OBJECT_TYPE=103

and tbl2.FORM_ID is not null

)

ORDER BY TASK_LIST_ID

Перечень элементов для форм (права доступа) Task -> Subtask -> Form

1.3 Список элементов из форм
1.4 Отслеживаем недостающие права доступа

2 Работа с элементами измерений
- 2.1 Получить перечень измерений
- 2.2 Получить элементы измерения ‘Scenario’

По бизнес-правилу получить форму с контекстным меню и задачу

Получаем название меню

SELECT OBJECT_NAME MenuName
      ,[LABEL]
      ,[STR_PROP1]
  FROM [hpl_app07].[dbo].[HSP_MENU_ITEM] tbl1
  LEFT JOIN [hpl_app07].[dbo].[HSP_OBJECT] tbl2
	ON tbl1.MENU_ID = tbl2.OBJECT_ID
  WHERE MENU_ITEM_TYPE=3 and LABEL='Расчет юнитов по РО'

SELECT OBJECT_NAME MenuName

,[LABEL]

,[STR_PROP1]

FROM [hpl_app07].[dbo].[HSP_MENU_ITEM] tbl1

LEFT JOIN [hpl_app07].[dbo].[HSP_OBJECT] tbl2

ON tbl1.MENU_ID = tbl2.OBJECT_ID

WHERE MENU_ITEM_TYPE=3 and LABEL='Расчет юнитов по РО'

или

SELECT OBJECT_NAME MenuName
      ,[LABEL]
      ,[STR_PROP1]
  FROM [hpl_app07].[dbo].[HSP_MENU_ITEM] tbl1
  LEFT JOIN [hpl_app07].[dbo].[HSP_OBJECT] tbl2
	ON tbl1.MENU_ID = tbl2.OBJECT_ID
  WHERE MENU_ITEM_TYPE=3 and [STR_PROP1]='BR07 0208.05: Бюджет ИТ - Агрегация по кол-ву магазинов'

SELECT OBJECT_NAME MenuName

,[LABEL]

,[STR_PROP1]

FROM [hpl_app07].[dbo].[HSP_MENU_ITEM] tbl1

LEFT JOIN [hpl_app07].[dbo].[HSP_OBJECT] tbl2

ON tbl1.MENU_ID = tbl2.OBJECT_ID

WHERE MENU_ITEM_TYPE=3 and [STR_PROP1]='BR07 0208.05: Бюджет ИТ - Агрегация по кол-ву магазинов'

Подписывайся на мой канал "Data Engineering" в телеграме

Получаем форму и задачу

[crayon-68230f2b858d3140826545/]

Перечень элементов для форм (права доступа)

Task -> Subtask -> Form

SELECT tbl3.OBJECT_NAME TaskName ,tbl2.OBJECT_NAME SubTaskName ,tbl4.OBJECT_NAME Form_Name FROM [hpl_app07].[dbo].[HSP_TASK] tbl1 left join [hpl_app07].[dbo].[HSP_OBJECT] tbl2 ON tbl1.TASK_ID=tbl2.Object_id left join [hpl_app07].[dbo].[HSP_OBJECT] tbl3 ON tbl1.TASK_LIST_ID=tbl3.Object_id left join [hpl_app07].[dbo].[HSP_OBJECT] tbl4 ON tbl1.INT_PROP1=tbl4.OBJECT_ID WHERE tbl1.TASK_TYPE=2 and tbl3.OBJECT_NAME = '008 Планирование Расходов на ИТ (OPEX)' ORDER BY TASK_LIST_ID

1
2
3
4
5
6
7
8
9
10
11
12
13

  SELECT tbl3.OBJECT_NAME TaskName
  ,tbl2.OBJECT_NAME SubTaskName
  ,tbl4.OBJECT_NAME Form_Name
  FROM [hpl_app07].[dbo].[HSP_TASK] tbl1
  left join [hpl_app07].[dbo].[HSP_OBJECT] tbl2
ON tbl1.TASK_ID=tbl2.Object_id
  left join [hpl_app07].[dbo].[HSP_OBJECT] tbl3
ON tbl1.TASK_LIST_ID=tbl3.Object_id
  left join [hpl_app07].[dbo].[HSP_OBJECT] tbl4
ON tbl1.INT_PROP1=tbl4.OBJECT_ID
  WHERE tbl1.TASK_TYPE=2
  and tbl3.OBJECT_NAME = '008 Планирование Расходов на ИТ (OPEX)'
  ORDER BY TASK_LIST_ID

Список элементов из форм

SELECT obj2.OBJECT_NAME Форма ,CASE WHEN form.[OBJDEF_TYPE] = 0 THEN 'POV' WHEN form.[OBJDEF_TYPE] = 1 THEN 'Page' WHEN form.[OBJDEF_TYPE] = 2 THEN 'Rows' WHEN form.[OBJDEF_TYPE] = 3 THEN 'Columns' END [Тип слоя] ,obj1.OBJECT_NAME [Элемент измерения] FROM [hpl_app07].[dbo].[HSP_FORMOBJ_DEF] form LEFT JOIN [hpl_app07].[dbo].[HSP_FORMOBJ_DEF_MBR] def ON form.[OBJDEF_ID] = def.[OBJDEF_ID] LEFT JOIN [hpl_app07].[dbo].[HSP_OBJECT] obj1 ON def.MBR_ID = obj1.OBJECT_ID LEFT JOIN [hpl_app07].[dbo].[HSP_OBJECT] obj2 ON obj2.OBJECT_ID= form.[FORM_ID] WHERE form.[FORM_ID] IN ( SELECT tbl4.OBJECT_ID Form_id FROM [hpl_app07].[dbo].[HSP_TASK] tbl1 left join [hpl_app07].[dbo].[HSP_OBJECT] tbl2 ON tbl1.TASK_ID=tbl2.Object_id left join [hpl_app07].[dbo].[HSP_OBJECT] tbl3 ON tbl1.TASK_LIST_ID=tbl3.Object_id left join [hpl_app07].[dbo].[HSP_OBJECT] tbl4 ON tbl1.INT_PROP1=tbl4.OBJECT_ID WHERE tbl1.TASK_TYPE=2 and tbl3.OBJECT_NAME = '008 Планирование Расходов на ИТ (OPEX)'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

SELECT obj2.OBJECT_NAME Форма
  ,CASE
WHEN form.[OBJDEF_TYPE] = 0 THEN 'POV'
WHEN form.[OBJDEF_TYPE] = 1 THEN 'Page'
WHEN form.[OBJDEF_TYPE] = 2 THEN 'Rows'
WHEN form.[OBJDEF_TYPE] = 3 THEN 'Columns'
   END [Тип слоя]
  ,obj1.OBJECT_NAME [Элемент измерения]
  FROM [hpl_app07].[dbo].[HSP_FORMOBJ_DEF] form
  LEFT JOIN [hpl_app07].[dbo].[HSP_FORMOBJ_DEF_MBR] def ON form.[OBJDEF_ID] = def.[OBJDEF_ID]
  LEFT JOIN [hpl_app07].[dbo].[HSP_OBJECT] obj1 ON def.MBR_ID = obj1.OBJECT_ID
  LEFT JOIN [hpl_app07].[dbo].[HSP_OBJECT] obj2 ON obj2.OBJECT_ID= form.[FORM_ID]
  WHERE form.[FORM_ID] IN (
  SELECT tbl4.OBJECT_ID Form_id
  FROM [hpl_app07].[dbo].[HSP_TASK] tbl1
  left join [hpl_app07].[dbo].[HSP_OBJECT] tbl2
ON tbl1.TASK_ID=tbl2.Object_id
  left join [hpl_app07].[dbo].[HSP_OBJECT] tbl3
ON tbl1.TASK_LIST_ID=tbl3.Object_id
  left join [hpl_app07].[dbo].[HSP_OBJECT] tbl4
ON tbl1.INT_PROP1=tbl4.OBJECT_ID
  WHERE tbl1.TASK_TYPE=2
  and tbl3.OBJECT_NAME = '008 Планирование Расходов на ИТ (OPEX)'

Отслеживаем недостающие права доступа

SELECT obj2.OBJECT_NAME Форма ,CASE WHEN form.[OBJDEF_TYPE] = 0 THEN 'POV' WHEN form.[OBJDEF_TYPE] = 1 THEN 'Page' WHEN form.[OBJDEF_TYPE] = 2 THEN 'Rows' WHEN form.[OBJDEF_TYPE] = 3 THEN 'Columns' END [Тип слоя] ,obj3.OBJECT_NAME [Измерение] ,obj1.OBJECT_NAME [Элемент измерения] FROM [hpl_app07].[dbo].[HSP_FORMOBJ_DEF] form LEFT JOIN [hpl_app07].[dbo].[HSP_FORMOBJ_DEF_MBR] def ON form.[OBJDEF_ID] = def.[OBJDEF_ID] LEFT JOIN [hpl_app07].[dbo].[HSP_OBJECT] obj1 ON def.MBR_ID = obj1.OBJECT_ID LEFT JOIN [hpl_app07].[dbo].[HSP_OBJECT] obj2 ON obj2.OBJECT_ID= form.[FORM_ID] LEFT JOIN [hpl_app07].[dbo].[HSP_MEMBER] member ON member.[MEMBER_ID]= obj1.OBJECT_ID LEFT JOIN [hpl_app07].[dbo].[HSP_OBJECT] obj3 ON member.[DIM_ID] = obj3.OBJECT_ID WHERE form.[FORM_ID] IN ( SELECT tbl4.OBJECT_ID Form_id FROM [hpl_app07].[dbo].[HSP_TASK] tbl1 left join [hpl_app07].[dbo].[HSP_OBJECT] tbl2 ON tbl1.TASK_ID=tbl2.Object_id left join [hpl_app07].[dbo].[HSP_OBJECT] tbl3 ON tbl1.TASK_LIST_ID=tbl3.Object_id left join [hpl_app07].[dbo].[HSP_OBJECT] tbl4 ON tbl1.INT_PROP1=tbl4.OBJECT_ID WHERE tbl1.TASK_TYPE=2 and tbl3.OBJECT_NAME = '008 Планирование Расходов на ИТ (OPEX)' ) ORDER BY obj2.OBJECT_NAME, [Тип слоя] DESC;

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

SELECT obj2.OBJECT_NAME Форма
  ,CASE
WHEN form.[OBJDEF_TYPE] = 0 THEN 'POV'
WHEN form.[OBJDEF_TYPE] = 1 THEN 'Page'
WHEN form.[OBJDEF_TYPE] = 2 THEN 'Rows'
WHEN form.[OBJDEF_TYPE] = 3 THEN 'Columns'
   END [Тип слоя]
  ,obj3.OBJECT_NAME [Измерение]
  ,obj1.OBJECT_NAME [Элемент измерения]
  FROM [hpl_app07].[dbo].[HSP_FORMOBJ_DEF] form
  LEFT JOIN [hpl_app07].[dbo].[HSP_FORMOBJ_DEF_MBR] def ON form.[OBJDEF_ID] = def.[OBJDEF_ID]
  LEFT JOIN [hpl_app07].[dbo].[HSP_OBJECT] obj1 ON def.MBR_ID = obj1.OBJECT_ID
  LEFT JOIN [hpl_app07].[dbo].[HSP_OBJECT] obj2 ON obj2.OBJECT_ID= form.[FORM_ID]

  LEFT JOIN [hpl_app07].[dbo].[HSP_MEMBER] member ON member.[MEMBER_ID]= obj1.OBJECT_ID
  LEFT JOIN [hpl_app07].[dbo].[HSP_OBJECT] obj3 ON member.[DIM_ID] = obj3.OBJECT_ID
  WHERE form.[FORM_ID] IN (
  SELECT tbl4.OBJECT_ID Form_id
  FROM [hpl_app07].[dbo].[HSP_TASK] tbl1
  left join [hpl_app07].[dbo].[HSP_OBJECT] tbl2
ON tbl1.TASK_ID=tbl2.Object_id
  left join [hpl_app07].[dbo].[HSP_OBJECT] tbl3
ON tbl1.TASK_LIST_ID=tbl3.Object_id
  left join [hpl_app07].[dbo].[HSP_OBJECT] tbl4
ON tbl1.INT_PROP1=tbl4.OBJECT_ID
  WHERE tbl1.TASK_TYPE=2
  and tbl3.OBJECT_NAME = '008 Планирование Расходов на ИТ (OPEX)'
  )
ORDER BY obj2.OBJECT_NAME, [Тип слоя] DESC;

Работа с элементами измерений

Получить перечень измерений

SELECT OBJECT_ID DIM_ID, OBJECT_NAME DIMENSION_NAME FROM [hyp_app].[dbo].[HSP_OBJECT] WHERE OBJECT_ID IN ( SELECT [DIM_ID] FROM [hyp_app].[dbo].[HSP_DIMENSION] ) AND OBJECT_NAME not in ('HSP_Rates','HSP_XCRNCY','PUH')

1
2
3
4
5
6
7

SELECT OBJECT_ID DIM_ID, OBJECT_NAME DIMENSION_NAME
FROM [hyp_app].[dbo].[HSP_OBJECT]
WHERE OBJECT_ID IN (
SELECT [DIM_ID]
FROM [hyp_app].[dbo].[HSP_DIMENSION]
)
AND OBJECT_NAME not in ('HSP_Rates','HSP_XCRNCY','PUH')

Получить элементы измерения ‘Scenario’

SELECT tbl1.MEMBER_ID, tbl1.DIM_ID, tbl4.OBJECT_NAME ElementCode, tbl3.OBJECT_NAME ElementName FROM [hyp_app].[dbo].[HSP_MEMBER] tbl1 LEFT JOIN (select * from [hyp_app].[dbo].[HSP_ALIAS] where ALIASTBL_ID=14) tbl2 ON tbl1.MEMBER_ID = tbl2.MEMBER_ID LEFT JOIN [hyp_app].[dbo].[HSP_OBJECT] tbl3 ON tbl2.[ALIAS_ID] = tbl3.OBJECT_ID LEFT JOIN [hyp_app].[dbo].[HSP_OBJECT] tbl4 ON tbl1.MEMBER_ID = tbl4.OBJECT_ID WHERE tbl1.DIM_ID IN ( SELECT OBJECT_ID FROM [hyp_app].[dbo].[HSP_OBJECT] WHERE OBJECT_ID IN ( SELECT [DIM_ID] FROM [hyp_app].[dbo].[HSP_DIMENSION] ) AND OBJECT_NAME = 'Scenario' ) and tbl4.OBJECT_TYPE<>45 ORDER BY tbl1.MEMBER_ID;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT tbl1.MEMBER_ID, tbl1.DIM_ID, tbl4.OBJECT_NAME ElementCode, tbl3.OBJECT_NAME ElementName
FROM [hyp_app].[dbo].[HSP_MEMBER] tbl1
LEFT JOIN (select * from [hyp_app].[dbo].[HSP_ALIAS] where ALIASTBL_ID=14) tbl2 ON tbl1.MEMBER_ID = tbl2.MEMBER_ID
LEFT JOIN [hyp_app].[dbo].[HSP_OBJECT] tbl3 ON tbl2.[ALIAS_ID] = tbl3.OBJECT_ID
LEFT JOIN [hyp_app].[dbo].[HSP_OBJECT] tbl4 ON tbl1.MEMBER_ID = tbl4.OBJECT_ID
WHERE tbl1.DIM_ID IN (
SELECT OBJECT_ID
FROM [hyp_app].[dbo].[HSP_OBJECT]
WHERE OBJECT_ID IN (
SELECT [DIM_ID]
FROM [hyp_app].[dbo].[HSP_DIMENSION]
)
AND OBJECT_NAME = 'Scenario'
) and tbl4.OBJECT_TYPE<>45
ORDER BY tbl1.MEMBER_ID;