· 6 years ago · Jun 11, 2019, 02:28 PM
1DROP TABLE IF EXISTS SE_01_01_18_01;
2
3CREATE TEMPORARY TABLE SE_01_01_18_01 AS SELECT *
4 FROM (
5 SELECT T__18__C.PK_ID AS SE_01_01_18_01_PK_ID,
6 [416_462]
7 FROM T__18__C
8 JOIN
9 T__18__F1 ON T__18__F1.PK_ID = T__18__C.PK_ID
10 WHERE INSTANCE = @INSTANCE AND
11 (2809 = 2809)
12 );
13
14DROP TABLE IF EXISTS S_26_02_01_01;
15
16CREATE TEMPORARY TABLE S_26_02_01_01 AS SELECT *
17 FROM (
18 SELECT T__301__C.PK_ID AS S_26_02_01_01_PK_ID,
19 [6130],
20 [6112_6127_6129],
21 [6112_6128_6129],
22 [6112_6113_6129],
23 [6112_6124_6129]
24 FROM T__301__C
25 JOIN
26 T__301__F1 ON T__301__F1.PK_ID = T__301__C.PK_ID
27 WHERE INSTANCE = @INSTANCE AND
28 (ifNull([6130], 2809) = 2809 AND
29 ifNull([6130], 2809) = 2809 AND
30 ifNull([6130], 2809) = 2809)
31 );
32
33DROP TABLE IF EXISTS Evaluations;
34
35CREATE TEMPORARY TABLE Evaluations AS SELECT *
36 FROM (
37 SELECT SE_01_01_18_01_PK_ID,
38 [416_462],
39 S_26_02_01_01_PK_ID,
40 [6130],
41 [6112_6127_6129],
42 [6112_6128_6129],
43 [6112_6113_6129],
44 [6112_6124_6129]
45 FROM SE_01_01_18_01
46 LEFT OUTER JOIN
47 S_26_02_01_01
48 UNION
49 SELECT SE_01_01_18_01_PK_ID,
50 [416_462],
51 S_26_02_01_01_PK_ID,
52 [6130],
53 [6112_6127_6129],
54 [6112_6128_6129],
55 [6112_6113_6129],
56 [6112_6124_6129]
57 FROM S_26_02_01_01
58 LEFT OUTER JOIN
59 SE_01_01_18_01
60 );
61
62SELECT CASE WHEN ( ( (ifNull([416_462], '') = 2812 OR
63 ifNull([416_462], '') = 2869 OR
64 ifNull([416_462], '') = 2880) OR
65 ifNull([416_462], '') = 2880) ) THEN (intervalArithmeticEqual(intervalArithmeticGetValueDecimal(ifNull([6112_6127_6129], '{"Radius":0.0,"LeftBound":0.0,"RightBound":0.0,"Value":0.0,"Decimal":"Infinity"}'), 'S.26.02.01.01', 'MONETARY'), intervalArithmeticSubtract(intervalArithmeticGetValueDecimal(ifNull([6112_6128_6129], '{"Radius":0.0,"LeftBound":0.0,"RightBound":0.0,"Value":0.0,"Decimal":"Infinity"}'), 'S.26.02.01.01', 'MONETARY'), (intervalArithmeticAdd(intervalArithmeticGetValueDecimal(ifNull([6112_6113_6129], '{"Radius":0.0,"LeftBound":0.0,"RightBound":0.0,"Value":0.0,"Decimal":"Infinity"}'), 'S.26.02.01.01', 'MONETARY'), intervalArithmeticGetValueDecimal(ifNull([6112_6124_6129], '{"Radius":0.0,"LeftBound":0.0,"RightBound":0.0,"Value":0.0,"Decimal":"Infinity"}'), 'S.26.02.01.01', 'MONETARY') ) ) ) ) ) ELSE 1 END AS RESULT,
66 'if (({t:SE.01.01.18.01,y:r0510,x:c0010,dv:''} = [s2c_CN:x1] or {t:SE.01.01.18.01,y:r0510,x:c0010,dv:''} = [s2c_CN:x60] or {t:SE.01.01.18.01,y:r0510,x:c0010,dv:''} = [s2c_CN:x71]) or {t:SE.01.01.18.01,y:r0510,x:c0010,dv:''} = [s2c_CN:x71]) then {t:S.26.02.01.01,y:r0330,x:c0080,dv:0} = {t:S.26.02.01.01,y:r0400,x:c0080,dv:0} - ({t:S.26.02.01.01,y:r0100,x:c0080,dv:0} + {t:S.26.02.01.01,y:r0300,x:c0080,dv:0})' AS EXPRESSION,
67 'if ((' || ifnull( (
68 SELECT '[' || MemberXBRLCode || ']'
69 FROM mMember
70 WHERE MemberID = ifnull(ifNull([416_462], ''), '')
71 ), '[NOT REPORTED]') || ' = [s2c_CN:x1] or ' || ifnull( (
72 SELECT '[' || MemberXBRLCode || ']'
73 FROM mMember
74 WHERE MemberID = ifnull(ifNull([416_462], ''), '')
75 ), '[NOT REPORTED]') || ' = [s2c_CN:x60] or ' || ifnull( (
76 SELECT '[' || MemberXBRLCode || ']'
77 FROM mMember
78 WHERE MemberID = ifnull(ifNull([416_462], ''), '')
79 ), '[NOT REPORTED]') || ' = [s2c_CN:x71]) or ' || ifnull( (
80 SELECT '[' || MemberXBRLCode || ']'
81 FROM mMember
82 WHERE MemberID = ifnull(ifNull([416_462], ''), '')
83 ), '[NOT REPORTED]') || ' = [s2c_CN:x71]) then ' || ifnull(ifNull([6112_6127_6129], 0), '') || ' = ' || ifnull(ifNull([6112_6128_6129], 0), '') || ' - (' || ifnull(ifNull([6112_6113_6129], 0), '') || ' + ' || ifnull(ifNull([6112_6124_6129], 0), '') || ')' AS FORMULA,
84 ifNull([6112_6127_6129], 0) AS [LEFT],
85 ifNull([6112_6128_6129], 0) - (ifNull([6112_6113_6129], 0) + ifNull([6112_6124_6129], 0) ) AS [RIGHT],
86 '{t:SE.01.01.18.01,y:r0510,x:c0010,dv:''}{t:S.26.02.01.01,y:r0330,x:c0080,dv:0}{t:S.26.02.01.01,y:r0400,x:c0080,dv:0}{t:S.26.02.01.01,y:r0100,x:c0080,dv:0}{t:S.26.02.01.01,y:r0300,x:c0080,dv:0}{t:SE.01.01.18.01,dv:NULL}{t:S.26.02.01.01,z:z0010,dv:2809}' AS SCOPE,
87 '{t:S.26.02.01.01, z:z0010}="' || ifnull([6130], '') || '"' AS CONTEXT
88 FROM Evaluations
89 WHERE (
90 SELECT ifnull(sum(fi.Filed), 0) > 0 AS FICheck
91 FROM mTable t
92 JOIN
93 dFilingIndicator fi ON t.TableID = fi.TableID
94 WHERE t.XbrlFilingIndicatorCode = (
95 SELECT XbrlFilingIndicatorCode
96 FROM mTable
97 WHERE TableID = 18
98 )
99AND
100 fi.InstanceID = @INSTANCE
101 )
102AND
103 (
104 SELECT ifnull(sum(fi.Filed), 0) > 0 AS FICheck
105 FROM mTable t
106 JOIN
107 dFilingIndicator fi ON t.TableID = fi.TableID
108 WHERE t.XbrlFilingIndicatorCode = (
109 SELECT XbrlFilingIndicatorCode
110 FROM mTable
111 WHERE TableID = 301
112 )
113AND
114 fi.InstanceID = @INSTANCE
115 )
116AND
117 IFNULL( ( (ifNull([6130], 2809) = 2809 AND
118 ifNull([6130], 2809) = 2809 AND
119 ifNull([6130], 2809) = 2809 AND
120 2809 = 2809) OR
121 (ifNull([6130], 2809) = 2809 AND
122 ifNull([6130], 2809) = 2809 AND
123 ifNull([6130], 2809) = 2809 AND
124 (2809 = 2809) IS NULL) OR
125 ( (ifNull([6130], 2809) = 2809 AND
126 ifNull([6130], 2809) = 2809 AND
127 ifNull([6130], 2809) = 2809) IS NULL AND
128 2809 = 2809) ), 0);