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
| CREATE VIEW SCORECARD_EURO
AS
SELECT
m1.entry_id AS ENTRY_ID1,
m2.entry_id AS ENTRY_ID2,
cross_scr.ENTRY_ID AS ENTRY_ID3,
m1.title AS TITLE1,
m2.title AS TITLE2,
m3.title AS TITLE3,
cross_scr.ROW_ID AS ROW_ID,
scorecard_row.position AS ROW_POSITION,
scorecard_rowgroup.position AS ROWGROUP_POSITION,
CASE
WHEN scorecard_row.reserved = 'EDITABLE' THEN scorecard.col_0003
ELSE scorecard_row.label
END AS ROW_LABEL,
scorecard_rowgroup.label AS ROWGROUP_LABEL,
scorecard_row.reserved AS RESERVED,
m3.COL_CURRENCY AS CURRENCY_ID,
CURRENCY.MULTIPLIER,
CURRENCY.LABEL AS CURRENCY_LABEL,
m1.COL_NATURE_L1 AS NATURE_L1,
m3.COL_NATURE_L3 AS NATURE_L3,
nature_level3.GROUPNAME AS NATURE_L3_CLASS,
m3.COL_NATURE_FINE AS NATURE_FINE,
m3.COL_MATURITY AS MATURITY,
m3.COL_PILOT_ENTITY AS PILOT_ENTITY,
m1.COL_PILOT_ENTITY AS PILOT_ENTITY_LEVEL1,
m3.COL_LEGAL_ENTITY AS LEGAL_ENTITY,
m3.COL_USED_METHODOLOGY AS USED_METHODOLOGY,
m3.COL_TRACING AS TRACING,
m3.COL_SC_VALIDATED AS SC_VALIDATED,
org_departments.groupname AS POL,
org_departments_level1.groupname AS POL_LEVEL1,
scorecard.history_date AS HISTORY_DATE,
m3.COL_SC_COMM AS SC_COMM,
m3.COL_SC_COMM2 AS SC_COMM2,
m3.COL_RESP_EOG AS RESP_EOG,
m3.COL_PLANNING AS PLANNING,
m3.COL_STOCK AS STOCK,
m3.COL_TERRITORY AS TERRITORY,
m3.COL_MINORITY_SHAREHOLDER AS MINORITY_SHAREHOLDER,
m3.COL_BUDGET_PROCEDURE AS BUDGET_PROCEDURE,
m3.COL_IMPACT,
CASE
WHEN cross_scr.ROW_ID <= 49 OR cross_scr.ROW_ID=99 OR cross_scr.ROW_ID=100 THEN 0
ELSE 49
END AS SC_PART,
CASE
WHEN cross_scr.row_id <> 15 AND cross_scr.row_id <> 18 AND cross_scr.row_id<>64 AND cross_scr.row_id <> 67
AND cross_scr.row_id <> 30 AND cross_scr.row_id <> 39 AND cross_scr.row_id<>79 AND cross_scr.row_id <> 88
THEN
ISNULL(SCORECARD.COL_0036*(ISNULL(currency_management.COL_0036,1)*ISNULL(currency.multiplier,1))/1000000,0)+
ISNULL(SCORECARD.COL_0086*(ISNULL(currency_management.COL_0086,1)*ISNULL(currency.multiplier,1))/1000000,0)+
ISNULL(SCORECARD.COL_0136*(ISNULL(currency_management.COL_0136,1)*ISNULL(currency.multiplier,1))/1000000,0)+
ISNULL(SCORECARD.COL_0186*(ISNULL(currency_management.COL_0186,1)*ISNULL(currency.multiplier,1))/1000000,0)
WHEN cross_scr.row_id = 30 OR cross_scr.row_id = 39 OR cross_scr.row_id=79 OR cross_scr.row_id = 88 THEN
ISNULL(COL_0001,0)
ELSE 0
END
AS COL_0001,
CASE
WHEN cross_scr.row_id <> 15 AND cross_scr.row_id <> 18 AND cross_scr.row_id<>64 AND cross_scr.row_id <> 67
AND cross_scr.row_id <> 30 AND cross_scr.row_id <> 39 AND cross_scr.row_id<>79 AND cross_scr.row_id <> 88
THEN
ISNULL(
ISNULL(SCORECARD.COL_0036*(ISNULL(currency_management.COL_0036,1)*ISNULL(currency.multiplier,1))/1000000,0)+
ISNULL(SCORECARD.COL_0086*(ISNULL(currency_management.COL_0086,1)*ISNULL(currency.multiplier,1))/1000000,0)+
ISNULL(SCORECARD.COL_0136*(ISNULL(currency_management.COL_0136,1)*ISNULL(currency.multiplier,1))/1000000,0)+
ISNULL(SCORECARD.COL_0186*(ISNULL(currency_management.COL_0186,1)*ISNULL(currency.multiplier,1))/1000000,0)
,0)
-
(
SELECT
ISNULL(SUM(
ISNULL(
ISNULL(SCORECARD_49.COL_0036*(ISNULL(currency_management.COL_0036,1)*ISNULL(currency.multiplier,1))/1000000,0)+
ISNULL(SCORECARD_49.COL_0086*(ISNULL(currency_management.COL_0086,1)*ISNULL(currency.multiplier,1))/1000000,0)+
ISNULL(SCORECARD_49.COL_0136*(ISNULL(currency_management.COL_0136,1)*ISNULL(currency.multiplier,1))/1000000,0)+
ISNULL(SCORECARD_49.COL_0186*(ISNULL(currency_management.COL_0186,1)*ISNULL(currency.multiplier,1))/1000000,0)
,0) ),0)
FROM
SCORECARD as SCORECARD_49
inner join maindata on maindata.entry_id=scorecard_49.entry_id
left outer join currency_management on currency_management.currency_id=maindata.col_currency
inner join currency on currency.id=maindata.col_currency
WHERE SCORECARD_49.entry_id = cross_scr.entry_id AND SCORECARD_49.row_id=cross_scr.row_id+49 AND SCORECARD_49.row_id<>99 AND SCORECARD_49.row_id<>100
)
WHEN cross_scr.row_id = 30 OR cross_scr.row_id = 39 OR cross_scr.row_id=79 OR cross_scr.row_id = 88 THEN
ISNULL(COL_0002,0)
ELSE 0
END AS COL_0002,
SCORECARD.COL_0003 AS COL_0003,
SCORECARD.col_0006*CASE WHEN cross_scr.row_id <> 15 AND cross_scr.row_id <> 18 AND cross_scr.row_id <> 64 AND cross_scr.row_id <> 67 THEN (ISNULL(currency_management.col_0006,1)*ISNULL(currency.multiplier,1))/1000000 ELSE 1 END AS COL_0006,
SCORECARD.COL_0016*CASE WHEN cross_scr.row_id <> 15 AND cross_scr.row_id <> 18 AND cross_scr.row_id <> 64 AND cross_scr.row_id <> 67 THEN (ISNULL(currency_management.COL_0016,1)*ISNULL(currency.multiplier,1))/1000000 ELSE 1 END AS COL_0016,
SCORECARD.COL_0026*CASE WHEN cross_scr.row_id <> 15 AND cross_scr.row_id <> 18 AND cross_scr.row_id <> 64 AND cross_scr.row_id <> 67 THEN (ISNULL(currency_management.COL_0026,1)*ISNULL(currency.multiplier,1))/1000000 ELSE 1 END AS COL_0026
FROM
(select
distinct scorecard.entry_id as entry_id,scorecard_row.row_id as row_id
from
scorecard_row ,scorecard) cross_scr
left outer join scorecard on cross_scr.entry_id=scorecard.entry_id and cross_scr.row_id = scorecard.row_id
inner join maindata m3 on m3.entry_id=cross_scr.entry_id and m3.deleted=0
left outer join currency_management on currency_management.currency_id=m3.col_currency
inner join currency on currency.id=m3.col_currency
left outer join maindata m2 on m2.entry_id = m3.parent_entry_id
left outer join maindata m1 on m1.entry_id = m2.parent_entry_id
left outer join scorecard_row on scorecard_row.row_id = cross_scr.row_id
left outer join scorecard_rowgroup on scorecard_rowgroup.group_id = scorecard_row.group_id
left outer join org_departments on m3.col_pilot_entity = org_departments.id
left outer join org_departments org_departments_level1 on m1.col_pilot_entity = org_departments_level1.id
left outer join nature_level3 on nature_level3.id = m3.col_nature_l3 |
