L2PWazd6

1Retained Earnings Report option is 1
2Work Period = S-1
3Reporting Period = 5
4 
5rowsetid: 17
6next rowcalc = 100 TO 250 "#,##0.00;(#,##0.00);0.00"
7calcwork was with quote: +100 TO 250 "#,##0.00;(#,##0.00);0.00"
8calcwork now with quote:+100 TO 250 
9top loop calcwork: +100 TO 250 
10+100 TO 250 
11counter 1 
12check calcwork +100 TO 250 
13reset calcwork to rowcalc +100 TO 250 
14top loop calcwork: +100 TO 250 
15+100 TO 250 
16counter 2 
17check calcwork +100 TO 250 
18reset calcwork to rowcalc +100 TO 250 
19top loop calcwork: +100 TO 250 
20+100 TO 250 
21counter 3 
22check calcwork +100 TO 250 
23TO From Row 100 
24TO Thru Row 250
25TO Oper +
26calcwork was +100 TO 250 
27totext = 100 TO 250
28patptr = 2
29worklen = 10
30just set calcwork to 
31top loop calcwork: 
32 
33counter 3 
34check calcwork 
35reset calcwork to rowcalc +100 TO 250 
36top loop calcwork: +100 TO 250 
37+100 TO 250 
38counter 4 
39check calcwork +100 TO 250 
40getting word
41FromRow
42100
43@PatPtr = 1
44@Position = 1
45top loop calcwork: ~100 TO 250 
46~100 TO 250 
47counter 4 
48check calcwork ~100 TO 250 
49reset calcwork to rowcalc +100 TO 250 
50top loop calcwork: +100 TO 250 
51+100 TO 250 
52counter 5 
53check calcwork +100 TO 250 
54reset calcwork to rowcalc +100 TO 250 
55top loop calcwork: +100 TO 250 
56+100 TO 250 
57counter 6 
58check calcwork +100 TO 250 
59reset calcwork to rowcalc +100 TO 250 
60top loop calcwork: +100 TO 250 
61+100 TO 250 
62counter 7 
63check calcwork +100 TO 250 
64reset calcwork to rowcalc +100 TO 250 
65top loop calcwork: +100 TO 250 
66+100 TO 250 
67counter 8 
68check calcwork +100 TO 250 
69reset calcwork to rowcalc +100 TO 250 
70top loop calcwork: +100 TO 250 
71+100 TO 250 
72counter 9 
73check calcwork +100 TO 250 
74reset calcwork to rowcalc +100 TO 250 
75end rowcalc_cursor
76update caltab 2
77sqlstat = create statistics stat1 on #linked_gl(glnum,glrc1,seg1)
78index1 = create index idx1 on #linked_gl(seg1,glnum,glrc1)
79index2 = create index idx2 on #linked_gl(glnum,glrc1,seg1)
80SEGSQL: update #k1 set filter01 = substring(linklow,1,4),filterh01 = substring(linkhigh,1,4) ,filter02 = substring(linklow,5,2),filterh02 = substring(linkhigh,5,2) ,filter03 = substring(linklow,7,4),filterh03 = substring(linkhigh,7,4) ,filter04 = substring(linklow,11,4),filterh04 = substring(linkhigh,11,4) ,filter05 = substring(linklow,15,4),filterh05 = substring(linkhigh,15,4) 
81UPDATE #glseg SQL:INSERT INTO #glseg select node,parentnode as parent,parentrefid,leaf,segnum,rownum,colnum,negate,crbal,nozero,modifier,dbonly,cronly,glnum,glrc1,suppress_round,bookcode from #linked_gl join #k1 on 1=1  and isnull(query,'') = '' and LTRIM(RTRIM(case when segnum = 0 then glnum collate database_default  when segnum = 1 then seg1 collate database_default when segnum = 2 then seg2 collate database_default when segnum = 3 then seg3 collate database_default when segnum = 4 then seg4 collate database_default when segnum = 5 then seg5 collate database_default end )) like replace(linklow,'?','_') collate database_default where len(linkhigh) = 0 union all select node,parentnode as parent,parentrefid,leaf,segnum,rownum,colnum,negate,crbal,nozero,modifier,dbonly,cronly,glnum,glrc1,suppress_round,bookcode from #linked_gl join #k1 on 1=1  and isnull(query,'') = ''and seg1 collate database_default between replace(filter01,'?',' ') collate database_default  and replace(filterh01,'?','z') collate database_default and seg2 collate database_default = replace(filter02,'?',' ') collate database_default and seg3 collate database_default = replace(filter03,'?',' ') collate database_default and seg4 collate database_default = replace(filter04,'?',' ') collate database_default and seg5 collate database_default = replace(filter05,'?',' ') collate database_default  where len(linkhigh) > 0  and isnull(query,'') = '' and segnum = 0 union all select node,parentnode as parent,parentrefid,leaf,segnum,rownum,colnum,negate,crbal,nozero,modifier,dbonly,cronly,glnum,glrc1,suppress_round,bookcode from #linked_gl join #k1 on 1=1  and isnull(query,'') = '' and case  when segnum = 1 then seg1 collate database_default when segnum = 2 then seg2 collate database_default when segnum = 3 then seg3 collate database_default when segnum = 4 then seg4 collate database_default when segnum = 5 then seg5 collate database_default end between replace(linklow,'?',' ') collate database_default and replace(linkhigh,'?','z')  collate database_default where len(linkhigh) > 0  and isnull(query,'') = '' and segnum <> 0
82AFTER COLMODE #glseg SQL:INSERT INTO #glseg select node,parentnode as parent,parentrefid,leaf,segnum,rownum,colnum,negate,crbal,nozero,modifier,dbonly,cronly,glnum,glrc1,suppress_round,bookcode from #linked_gl join #k1 on 1=1  and isnull(query,'') = '' and LTRIM(RTRIM(case when segnum = 0 then glnum collate database_default  when segnum = 1 then seg1 collate database_default when segnum = 2 then seg2 collate database_default when segnum = 3 then seg3 collate database_default when segnum = 4 then seg4 collate database_default when segnum = 5 then seg5 collate database_default end )) like replace(linklow,'?','_') collate database_default where len(linkhigh) = 0 union all select node,parentnode as parent,parentrefid,leaf,segnum,rownum,colnum,negate,crbal,nozero,modifier,dbonly,cronly,glnum,glrc1,suppress_round,bookcode from #linked_gl join #k1 on 1=1  and isnull(query,'') = ''and seg1 collate database_default between replace(filter01,'?',' ') collate database_default  and replace(filterh01,'?','z') collate database_default and seg2 collate database_default = replace(filter02,'?',' ') collate database_default and seg3 collate database_default = replace(filter03,'?',' ') collate database_default and seg4 collate database_default = replace(filter04,'?',' ') collate database_default and seg5 collate database_default = replace(filter05,'?',' ') collate database_default  where len(linkhigh) > 0  and isnull(query,'') = '' and segnum = 0 union all select node,parentnode as parent,parentrefid,leaf,segnum,rownum,colnum,negate,crbal,nozero,modifier,dbonly,cronly,glnum,glrc1,suppress_round,bookcode from #linked_gl join #k1 on 1=1  and isnull(query,'') = '' and case  when segnum = 1 then seg1 collate database_default when segnum = 2 then seg2 collate database_default when segnum = 3 then seg3 collate database_default when segnum = 4 then seg4 collate database_default when segnum = 5 then seg5 collate database_default end between replace(linklow,'?',' ') collate database_default and replace(linkhigh,'?','z')  collate database_default where len(linkhigh) > 0  and isnull(query,'') = '' and segnum <> 0
83RETURNING because if not exists(select * from #k1 where isnull(query,'') <> '')
84ExecuteAccountSQL sql 1 = DECLARE glrange_list CURSOR FOR SELECT t0.* FROM #k1 AS t0 
85 
86@CacheTable = NULL
87Now @CacheTable = NULL
88Base Period = 5
89Base Year = 2019
90Year Ind = BASE
91Period Ind = BASE
92Periodctl = 
93Returned ColPeriod: 5
94Reporting Currency: 
95Conversion Method: 
96Currency Filter: 
97Column Period = 5
98Column Year = 2019
99Book = 1
100Setdefid = 0
101back from dfxPopEntityOpts
102INSERT INTO #minmax SELECT MIN(fmsbegdt) AS begdt,MAX(fmsenddt) AS enddt FROM fmsper WHERE fmsyr = 2019                 AND fmsper = 5                   
103Going to BuildColumnSQL BB for CURBB and Currency =  and Column = 1
104Calling dfxfn_BuildColumnSQL
105insert into #glcoldata select t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,0,t1.nozero,1, isnull(round(sum(round(cast(t2.glcydb as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END), isnull(round(sum(round(cast(t2.glcycr as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END), isnull(round((sum(round(cast(isnull(t2.glcydb,0) as decimal(16,4)),4))* (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END))-sum(round(cast(t2.glcycr as decimal(16,4)),4))* (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END)* (CASE WHEN t1.cronly = 1 THEN -1 ELSE 1 END)+round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END)* (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END), isnull(round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) * (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END) ,t1.suppress_round from gl as t0 with (nolock)  join #trmethod on old_trmethod = t0.trmethod  join #glacctrow as t1 on t0.glnum = t1.glnum and t0.glrc1 = t1.glrc1 AND rownum <> 0 AND colnum = 0 AND modifier = '' left join #linked_glamt as t2 with (nolock) on t2.glnum = t1.glnum and t2.glrc1 = t1.glrc1 and glyr = 2019       and t2.gltype COLLATE DATABASE_DEFAULT IN (SELECT gltype COLLATE DATABASE_DEFAULT FROM df_dfx_bookcode AS j0 WHERE bookcode = '1' AND j0.gltype COLLATE DATABASE_DEFAULT = t2.gltype COLLATE DATABASE_DEFAULT) where 1=1 and t0.glcat IN ('A','C','L') and ( len(t1.bookcode) = 0 OR t1.bookcode IS NULL OR t1.bookcode = '1') group by t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,t1.nozero,t1.negate,t1.crbal,t1.dbonly,t1.cronly,t1.suppress_round  having sum(glcydb) <> 0 or sum(glcycr) <> 0 or sum(glcybu) <> 0
106Enterprise
107Going to BuildColumnSQL for CURBB and Column = 1
108insert into #glcoldata select t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,0,t1.nozero,1, isnull(round(sum(round(cast(t2.glcydb as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END), isnull(round(sum(round(cast(t2.glcycr as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END), isnull(round((sum(round(cast(isnull(t2.glcydb,0) as decimal(16,4)),4))* (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END))-sum(round(cast(t2.glcycr as decimal(16,4)),4))* (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END)* (CASE WHEN t1.cronly = 1 THEN -1 ELSE 1 END)+round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END)* (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END), isnull(round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) * (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END) ,t1.suppress_round from gl as t0 with (nolock)  join #trmethod on old_trmethod = t0.trmethod  join #glacctrow as t1 on t0.glnum = t1.glnum and t0.glrc1 = t1.glrc1 AND rownum <> 0 AND colnum = 0 AND modifier = '' left join #linked_glamt as t2 with (nolock) on t2.glnum = t1.glnum and t2.glrc1 = t1.glrc1 and glyr = 2019       AND glper < 5                    and t2.gltype COLLATE DATABASE_DEFAULT IN (SELECT gltype COLLATE DATABASE_DEFAULT FROM df_dfx_bookcode AS j0 WHERE bookcode = '1' AND j0.gltype COLLATE DATABASE_DEFAULT = t2.gltype COLLATE DATABASE_DEFAULT) where 1=1 and ( len(t1.bookcode) = 0 OR t1.bookcode IS NULL OR t1.bookcode = '1') group by t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,t1.nozero,t1.negate,t1.crbal,t1.dbonly,t1.cronly,t1.suppress_round  having sum(glcydb) <> 0 or sum(glcycr) <> 0 or sum(glcybu) <> 0
109Warning: Null value is eliminated by an aggregate or other SET operation.
110going to Process Excel
111ProcessExcel:5                    Year: 2019       BalType: CUR/BB Base Period: 5 ColNum: 1 ColPeriod = 5                   
112INSERT INTO #glcoldata (rownum,rowmod,nozero,colnum,debit,credit,balance,budget,acctid,node,parent,parentrefid,leaf) select b1.rownum,0,b1.nozero,1, case when cast(b0.cell as decimal(24,8)) > 0 then cast(b0.cell as decimal(24,8)) else 0 end,  case when cast(b0.cell as decimal(24,8)) < 0 then cast(b0.cell as decimal(24,8)) else 0 end, cast(b0.cell as decimal(24,8)) * (CASE WHEN b1.crbal = 1 THEN -1 ELSE 1 END),0,1, node,parentnode,parentrefid,leaf from #final_rules AS b0  join df_dfx_rowdtl AS b1 ON b0.rownum = b1.rownum AND b1.rowsetid = 17 where isnumeric(b0.cell) = 1 and b0.cell <> '-' AND b0.colnum-1 = 1
113 
114Base Period = 5
115Base Year = 2019
116Year Ind = BASE
117Period Ind = BASE
118Periodctl = 
119Returned ColPeriod: 5
120Reporting Currency: 
121Conversion Method: 
122Currency Filter: 
123Column Period = 5
124Column Year = 2019
125Book = 1
126Setdefid = 0
127back from dfxPopEntityOpts
128INSERT INTO #minmax SELECT MIN(fmsbegdt) AS begdt,MAX(fmsenddt) AS enddt FROM fmsper WHERE fmsyr = 2019                 AND fmsper = 5                   
129Enterprise
130Going to BuildColumnSQL for CUR and Column = 2
131insert into #glcoldata select t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,0,t1.nozero,2, isnull(round(sum(round(cast(t2.glcydb as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END), isnull(round(sum(round(cast(t2.glcycr as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END), isnull(round((sum(round(cast(isnull(t2.glcydb,0) as decimal(16,4)),4))* (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END))-sum(round(cast(t2.glcycr as decimal(16,4)),4))* (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END)* (CASE WHEN t1.cronly = 1 THEN -1 ELSE 1 END)+round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END)* (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END), isnull(round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) * (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END) ,t1.suppress_round from gl as t0 with (nolock)  join #trmethod on old_trmethod = t0.trmethod  join #glacctrow as t1 on t0.glnum = t1.glnum and t0.glrc1 = t1.glrc1 AND rownum <> 0 AND colnum = 0 AND modifier = '' left join #linked_glamt as t2 with (nolock) on t2.glnum = replace(t1.glnum,' ','') and t2.glrc1 = t1.glrc1 and glyr = 2019       AND glper = 5                    and t2.gltype COLLATE DATABASE_DEFAULT IN (SELECT gltype COLLATE DATABASE_DEFAULT FROM df_dfx_bookcode AS j0 WHERE bookcode = '1' AND j0.gltype COLLATE DATABASE_DEFAULT = t2.gltype COLLATE DATABASE_DEFAULT) where 1=1 and ( len(t1.bookcode) = 0 OR t1.bookcode IS NULL OR t1.bookcode = '1') group by t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,t1.nozero,t1.negate,t1.crbal,t1.dbonly,t1.cronly,t1.suppress_round  having sum(glcydb) <> 0 or sum(glcycr) <> 0 or sum(glcybu) <> 0
132Warning: Null value is eliminated by an aggregate or other SET operation.
133going to Process Excel
134ProcessExcel:5                    Year: 2019       BalType: CUR Base Period: 5 ColNum: 2 ColPeriod = 5                   
135INSERT INTO #glcoldata (rownum,rowmod,nozero,colnum,debit,credit,balance,budget,acctid,node,parent,parentrefid,leaf) select b1.rownum,0,b1.nozero,2, case when cast(b0.cell as decimal(24,8)) > 0 then cast(b0.cell as decimal(24,8)) else 0 end,  case when cast(b0.cell as decimal(24,8)) < 0 then cast(b0.cell as decimal(24,8)) else 0 end, cast(b0.cell as decimal(24,8)) * (CASE WHEN b1.crbal = 1 THEN -1 ELSE 1 END),0,1, node,parentnode,parentrefid,leaf from #final_rules AS b0  join df_dfx_rowdtl AS b1 ON b0.rownum = b1.rownum AND b1.rowsetid = 17 where isnumeric(b0.cell) = 1 and b0.cell <> '-' AND b0.colnum-1 = 2
136 
137Base Period = 5
138Base Year = 2019
139Year Ind = BASE
140Period Ind = BASE
141Periodctl = 
142Returned ColPeriod: 5
143Reporting Currency: 
144Conversion Method: 
145Currency Filter: 
146Column Period = 5
147Column Year = 2019
148Book = 1
149Setdefid = 0
150back from dfxPopEntityOpts
151INSERT INTO #minmax SELECT MIN(fmsbegdt) AS begdt,MAX(fmsenddt) AS enddt FROM fmsper WHERE fmsyr = 2019                 AND fmsper = 5                   
152Enterprise
153Going to BuildColumnSQL for CUR and Column = 3
154insert into #glcoldata select t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,0,t1.nozero,3, isnull(round(sum(round(cast(t2.glcydb as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END), isnull(round(sum(round(cast(t2.glcycr as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END), isnull(round((sum(round(cast(isnull(t2.glcydb,0) as decimal(16,4)),4))* (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END))-sum(round(cast(t2.glcycr as decimal(16,4)),4))* (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END)* (CASE WHEN t1.cronly = 1 THEN -1 ELSE 1 END)+round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END)* (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END), isnull(round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) * (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END) ,t1.suppress_round from gl as t0 with (nolock)  join #trmethod on old_trmethod = t0.trmethod  join #glacctrow as t1 on t0.glnum = t1.glnum and t0.glrc1 = t1.glrc1 AND rownum <> 0 AND colnum = 0 AND modifier = '' left join #linked_glamt as t2 with (nolock) on t2.glnum = replace(t1.glnum,' ','') and t2.glrc1 = t1.glrc1 and glyr = 2019       AND glper = 5                    and t2.gltype COLLATE DATABASE_DEFAULT IN (SELECT gltype COLLATE DATABASE_DEFAULT FROM df_dfx_bookcode AS j0 WHERE bookcode = '1' AND j0.gltype COLLATE DATABASE_DEFAULT = t2.gltype COLLATE DATABASE_DEFAULT) where 1=1 and ( len(t1.bookcode) = 0 OR t1.bookcode IS NULL OR t1.bookcode = '1') group by t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,t1.nozero,t1.negate,t1.crbal,t1.dbonly,t1.cronly,t1.suppress_round  having sum(glcydb) <> 0 or sum(glcycr) <> 0 or sum(glcybu) <> 0
155Warning: Null value is eliminated by an aggregate or other SET operation.
156going to Process Excel
157ProcessExcel:5                    Year: 2019       BalType: CUR Base Period: 5 ColNum: 3 ColPeriod = 5                   
158INSERT INTO #glcoldata (rownum,rowmod,nozero,colnum,debit,credit,balance,budget,acctid,node,parent,parentrefid,leaf) select b1.rownum,0,b1.nozero,3, case when cast(b0.cell as decimal(24,8)) > 0 then cast(b0.cell as decimal(24,8)) else 0 end,  case when cast(b0.cell as decimal(24,8)) < 0 then cast(b0.cell as decimal(24,8)) else 0 end, cast(b0.cell as decimal(24,8)) * (CASE WHEN b1.crbal = 1 THEN -1 ELSE 1 END),0,1, node,parentnode,parentrefid,leaf from #final_rules AS b0  join df_dfx_rowdtl AS b1 ON b0.rownum = b1.rownum AND b1.rowsetid = 17 where isnumeric(b0.cell) = 1 and b0.cell <> '-' AND b0.colnum-1 = 3
159 
160Base Period = 5
161Base Year = 2019
162Year Ind = BASE
163Period Ind = BASE
164Periodctl = 
165Returned ColPeriod: 5
166Reporting Currency: 
167Conversion Method: 
168Currency Filter: 
169Column Period = 5
170Column Year = 2019
171Book = 1
172Setdefid = 0
173back from dfxPopEntityOpts
174INSERT INTO #minmax SELECT MIN(fmsbegdt) AS begdt,MAX(fmsenddt) AS enddt FROM fmsper WHERE fmsyr = 2019                 AND fmsper = 5                   
175Going to BuildColumnSQL BB for YTD and Currency =  and Column = 4
176Calling dfxfn_BuildColumnSQL
177insert into #glcoldata select t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,0,t1.nozero,4, isnull(round(sum(round(cast(t2.glcydb as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END), isnull(round(sum(round(cast(t2.glcycr as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END), isnull(round((sum(round(cast(isnull(t2.glcydb,0) as decimal(16,4)),4))* (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END))-sum(round(cast(t2.glcycr as decimal(16,4)),4))* (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END)* (CASE WHEN t1.cronly = 1 THEN -1 ELSE 1 END)+round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END)* (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END), isnull(round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) * (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END) ,t1.suppress_round from gl as t0 with (nolock)  join #trmethod on old_trmethod = t0.trmethod  join #glacctrow as t1 on t0.glnum = t1.glnum and t0.glrc1 = t1.glrc1 AND rownum <> 0 AND colnum = 0 AND modifier = '' left join #linked_glamt as t2 with (nolock) on t2.glnum = t1.glnum and t2.glrc1 = t1.glrc1 and glyr = 2019       and t2.gltype COLLATE DATABASE_DEFAULT IN (SELECT gltype COLLATE DATABASE_DEFAULT FROM df_dfx_bookcode AS j0 WHERE bookcode = '1' AND j0.gltype COLLATE DATABASE_DEFAULT = t2.gltype COLLATE DATABASE_DEFAULT) where 1=1 and t0.glcat IN ('A','C','L') and ( len(t1.bookcode) = 0 OR t1.bookcode IS NULL OR t1.bookcode = '1') group by t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,t1.nozero,t1.negate,t1.crbal,t1.dbonly,t1.cronly,t1.suppress_round  having sum(glcydb) <> 0 or sum(glcycr) <> 0 or sum(glcybu) <> 0
178Enterprise
179Going to BuildColumnSQL for YTD and Column = 4
180insert into #glcoldata select t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,0,t1.nozero,4, isnull(round(sum(round(cast(t2.glcydb as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END), isnull(round(sum(round(cast(t2.glcycr as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END), isnull(round((sum(round(cast(isnull(t2.glcydb,0) as decimal(16,4)),4))* (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END))-sum(round(cast(t2.glcycr as decimal(16,4)),4))* (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END)* (CASE WHEN t1.cronly = 1 THEN -1 ELSE 1 END)+round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END)* (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END), isnull(round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) * (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END) ,t1.suppress_round from gl as t0 with (nolock)  join #trmethod on old_trmethod = t0.trmethod  join #glacctrow as t1 on t0.glnum = t1.glnum and t0.glrc1 = t1.glrc1 AND rownum <> 0 AND colnum = 0 AND modifier = '' left join #linked_glamt as t2 with (nolock) on t2.glnum = t1.glnum and t2.glrc1 = t1.glrc1 and glyr = 2019       AND glper <= 5                    and t2.gltype COLLATE DATABASE_DEFAULT IN (SELECT gltype COLLATE DATABASE_DEFAULT FROM df_dfx_bookcode AS j0 WHERE bookcode = '1' AND j0.gltype COLLATE DATABASE_DEFAULT = t2.gltype COLLATE DATABASE_DEFAULT) where 1=1 and ( len(t1.bookcode) = 0 OR t1.bookcode IS NULL OR t1.bookcode = '1') group by t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,t1.nozero,t1.negate,t1.crbal,t1.dbonly,t1.cronly,t1.suppress_round  having sum(glcydb) <> 0 or sum(glcycr) <> 0 or sum(glcybu) <> 0
181Warning: Null value is eliminated by an aggregate or other SET operation.
182going to Process Excel
183ProcessExcel:5                    Year: 2019       BalType: YTD Base Period: 5 ColNum: 4 ColPeriod = 5                   
184INSERT INTO #glcoldata (rownum,rowmod,nozero,colnum,debit,credit,balance,budget,acctid,node,parent,parentrefid,leaf) select b1.rownum,0,b1.nozero,4, case when cast(b0.cell as decimal(24,8)) > 0 then cast(b0.cell as decimal(24,8)) else 0 end,  case when cast(b0.cell as decimal(24,8)) < 0 then cast(b0.cell as decimal(24,8)) else 0 end, cast(b0.cell as decimal(24,8)) * (CASE WHEN b1.crbal = 1 THEN -1 ELSE 1 END),0,1, node,parentnode,parentrefid,leaf from #final_rules AS b0  join df_dfx_rowdtl AS b1 ON b0.rownum = b1.rownum AND b1.rowsetid = 17 where isnumeric(b0.cell) = 1 and b0.cell <> '-' AND b0.colnum-1 = 4
185 
186going to Process Excel for Non CPO/RPO data
187 
188INSERT INTO #glcoldata (rownum,rowmod,nozero,colnum,debit,credit,balance,budget,acctid,node,parent,parentrefid,leaf) select b1.rownum,0,b1.nozero,-1, case when cast(b0.cell as decimal(24,8)) > 0 then cast(b0.cell as decimal(24,8)) else 0 end,  case when cast(b0.cell as decimal(24,8)) < 0 then cast(b0.cell as decimal(24,8)) else 0 end, cast(b0.cell as decimal(24,8)) * (CASE WHEN b1.crbal = 1 THEN -1 ELSE 1 END),0,1, node,parentnode,parentrefid,leaf from #final_rules AS b0  join df_dfx_rowdtl AS b1 ON b0.rownum = b1.rownum AND b1.rowsetid = 17 where isnumeric(b0.cell) = 1 and b0.cell <> '-' AND b0.colnum-1 = -1
189queueid = NULL
190in dfxRefactorRows
191leaving refactor rows
192going to dfxCalcRows
193factor/proximity
1941
1951
1961
197NO ROUND!
198insert into #calcdata 
199select node,parent,parentrefid,leaf,rownum,rowmod,1,colnum,isnull(sum(debit),0) as debit,isnull(sum(credit),0) as credit,isnull(sum(balance),0) as balance,isnull(sum(budget),0) as budget 
200from #glcoldata 
201where rowmod = 0 and suppress_round <> 1 AND EXISTS(select * from df_dfx_reportdef as j0 JOIN df_dfx_coldtl as j1 ON j0.colsetid = j1.colsetid     AND j1.colnum = #glcoldata.colnum and (j1.format like '%.%' OR j1.suppress_round = 1) WHERE j0.reportdef_id = 11) group by node,parent,parentrefid,leaf,rownum,rowmod,colnum
202SUPPRESS ROUND!
203SUPPRESS ROUND!
204insert into #calcdata 
205select node,parent,parentrefid,leaf,rownum,rowmod,1,colnum,isnull(sum(debit),0) as debit,isnull(sum(credit),0) as credit,isnull(sum(balance),0) as balance,isnull(sum(budget),0) as budget 
206from #glcoldata 
207where rowmod = 0 and suppress_round = 1 AND NOT EXISTS(select * from df_dfx_reportdef as j0 JOIN df_dfx_coldtl as j1 ON j0.colsetid = j1.colsetid     AND j1.colnum = #glcoldata.colnum and (j1.format like '%.%' OR j1.suppress_round = 1) WHERE j0.reportdef_id = 11) group by node,parent,parentrefid,leaf,rownum,rowmod,colnum
208SUPPRESS ROUND!
209insert into #calcdata 
210select node,parent,parentrefid,leaf,rownum,rowmod,1,colnum,isnull(sum(debit),0) as debit,isnull(sum(credit),0) as credit,isnull(sum(balance),0) as balance,isnull(sum(budget),0) as budget 
211from #glcoldata 
212where rowmod = 0 and suppress_round = 1 AND EXISTS(select * from df_dfx_reportdef as j0 JOIN df_dfx_coldtl as j1 ON j0.colsetid = j1.colsetid     AND j1.colnum = #glcoldata.colnum and (j1.format like '%.%' OR j1.suppress_round = 1) WHERE j0.reportdef_id = 11) group by node,parent,parentrefid,leaf,rownum,rowmod,colnum
213ROUND!
214insert into #calcdata 
215select node,parent,parentrefid,leaf,rownum,rowmod,1,colnum,round(isnull(sum(debit),0),2) as debit,round(isnull(sum(credit),0),2) as credit,round(isnull(sum(balance),0),2) as balance,round(isnull(sum(budget),0),2) as budget 
216from #glcoldata 
217where rowmod = 0 and suppress_round = 0 and not exists(select * from df_dfx_reportdef as j0 join df_dfx_coldtl as j1 on j0.colsetid = j1.colsetid     and j1.colnum = #glcoldata.colnum and (j1.format like '%.%' or j1.suppress_round = 1) where j0.reportdef_id = 11) group by node,parent,parentrefid,leaf,rownum,rowmod,colnum
218have a record
219inspecting
220end of file: 0
221lpctl = -1
222local_precedence = 0
223SELECT node,parent,parentrefid,leaf,colnum,1 as rownum, cast(0 as decimal(24,8)) as d001, cast(0 as decimal(24,8)) as c001, cast(0 as decimal(24,8)) as b001, cast(0 as decimal(24,8)) as u001 INTO ##workdata530 FROM #calcdata WHERE 1=0 GROUP BY node,parent,parentrefid,leaf,colnum 
224start operator = +
225lparen = (
226rowctl = 7
227literal = 0
228operator = +
229INSERT INTO ##workdata530 SELECT node,parent,parentrefid,leaf,colnum,1,sum(debit),sum(credit),sum(balance),sum(budget) FROM #calcdata WHERE rownum BETWEEN 1    AND 6    AND (rowmod = 0 or rowmod = 1   ) GROUP BY node,parent,parentrefid,leaf,colnum
230have a record
231inspecting
232into calcdata
233INSERT INTO #calcdata (rownum,rowmod,node,parent,parentrefid,leaf,colnum,nozero,debit,credit,balance,budget)SELECT 7,7,node,parent,parentrefid,leaf,colnum,1,(+SUM(d001)),(+SUM(c001)),(+SUM(b001)),(+SUM(u001)) FROM ##workdata530 GROUP BY node,parent,parentrefid,leaf,colnum
234DROP TABLE ##workdata530
235end of file: 1
236done dfxCalcRows
237column_list start col = -1
238INSERT INTO ##tempresults530(resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,noprint,debit02,credit02,balance02,budget02,debit03,credit03,balance03,budget03,debit04,credit04,balance04,budget04) SELECT 'F',NULL,NULL,NULL,NULL,NULL,NULL,rowdesc,v.rownum,v.nozero,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,v.noprint,isnull(sum(debit02),0) as debit02,isnull(sum(credit02),0) as credit02 ,isnull(sum(balance02),0) as balance02,isnull(sum(budget02),0) as budget02,isnull(sum(debit03),0) as debit03,isnull(sum(credit03),0) as credit03 ,isnull(sum(balance03),0) as balance03,isnull(sum(budget03),0) as budget03,isnull(sum(debit04),0) as debit04,isnull(sum(credit04),0) as credit04 ,isnull(sum(balance04),0) as balance04,isnull(sum(budget04),0) as budget04 FROM (SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,sum(debit) as debit02,sum(credit) as credit02,sum(balance) as balance02,sum(budget) as budget02,cast(0 as decimal(16,2)) as debit03,cast(0 as decimal(16,2)) as credit03,cast(0 as decimal(16,2)) as balance03,cast(0 as decimal(16,2)) as budget03,cast(0 as decimal(16,2)) as debit04,cast(0 as decimal(16,2)) as credit04,cast(0 as decimal(16,2)) as balance04,cast(0 as decimal(16,2)) as budget04 FROM #glcoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 1 and not (t0.nozero = 1 and t0.balance = 0) and acctid is null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor having ((sum(t0.balance) <> 0)and t0.nozero = 1) or t0.nozero = 0 UNION SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,0 as debit02,0 as credit02,0 as balance02,0 as budget02,sum(debit) as debit03,sum(credit) as credit03,sum(balance) as balance03,sum(budget) as budget03,0 as debit04,0 as credit04,0 as balance04,0 as budget04 FROM #glcoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 2 and not (t0.nozero = 1 and t0.balance = 0 and t0.debit = 0 and t0.credit = 0) and acctid is null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor having ((sum(t0.balance) <> 0 or sum(t0.debit) <> 0 or sum(t0.credit) <> 0) and t0.nozero = 1) or t0.nozero = 0 UNION SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,0 as debit02,0 as credit02,0 as balance02,0 as budget02,0 as debit03,0 as credit03,0 as balance03,0 as budget03,sum(debit) as debit04,sum(credit) as credit04,sum(balance) as balance04,sum(budget) as budget04 FROM #glcoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 3 and not (t0.nozero = 1 and t0.balance = 0 and t0.debit = 0 and t0.credit = 0) and acctid is null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor having ((sum(t0.balance) <> 0 or sum(t0.debit) <> 0 or sum(t0.credit) <> 0) and t0.nozero = 1) or t0.nozero = 0) AS v group by v.rowdesc,v.rownum,v.nozero,v.noprint ,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor
239done execute
240INSERT INTO ##tempresults530(resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,noprint,debit02,credit02,balance02,budget02,debit03,credit03,balance03,budget03,debit04,credit04,balance04,budget04) SELECT 'A',NULL,NULL,NULL,NULL,NULL,NULL,rowdesc,v.rownum,v.nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,v.noprint,isnull(sum(debit02),0) as debit02,isnull(sum(credit02),0) as credit02 ,isnull(sum(balance02),0) as balance02,isnull(sum(budget02),0) as budget02,isnull(sum(debit03),0) as debit03,isnull(sum(credit03),0) as credit03 ,isnull(sum(balance03),0) as balance03,isnull(sum(budget03),0) as budget03,isnull(sum(debit04),0) as debit04,isnull(sum(credit04),0) as credit04 ,isnull(sum(balance04),0) as balance04,isnull(sum(budget04),0) as budget04 FROM (SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,sum(debit) as debit02,sum(credit) as credit02,sum(balance) as balance02,sum(budget) as budget02,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,cast(0 as decimal(16,2)) as debit03,cast(0 as decimal(16,2)) as credit03,cast(0 as decimal(16,2)) as balance03,cast(0 as decimal(16,2)) as budget03,cast(0 as decimal(16,2)) as debit04,cast(0 as decimal(16,2)) as credit04,cast(0 as decimal(16,2)) as balance04,cast(0 as decimal(16,2)) as budget04 FROM #glcoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 1 and not (t0.nozero = 1 and t0.balance = 0) and acctid is not null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5 having ((sum(t0.balance) <> 0)and t0.nozero = 1) or t0.nozero = 0 UNION SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,0 as debit02,0 as credit02,0 as balance02,0 as budget02,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,sum(debit) as debit03,sum(credit) as credit03,sum(balance) as balance03,sum(budget) as budget03,0 as debit04,0 as credit04,0 as balance04,0 as budget04 FROM #glcoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 2 and not (t0.nozero = 1 and t0.balance = 0 and t0.debit = 0 and t0.credit = 0) and acctid is not null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5 having ((sum(t0.balance) <> 0 or sum(t0.debit) <> 0 or sum(t0.credit) <> 0) and t0.nozero = 1) or t0.nozero = 0 UNION SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,0 as debit02,0 as credit02,0 as balance02,0 as budget02,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,0 as debit03,0 as credit03,0 as balance03,0 as budget03,sum(debit) as debit04,sum(credit) as credit04,sum(balance) as balance04,sum(budget) as budget04 FROM #glcoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 3 and not (t0.nozero = 1 and t0.balance = 0 and t0.debit = 0 and t0.credit = 0) and acctid is not null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5 having ((sum(t0.balance) <> 0 or sum(t0.debit) <> 0 or sum(t0.credit) <> 0) and t0.nozero = 1) or t0.nozero = 0) AS v group by v.rowdesc,v.rownum,v.nozero,v.noprint ,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5
241done execute
242startcol = 4
243colcount = 6
244column_list start col = 4
245INSERT INTO ##tempresults530(resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,noprint,debit05,credit05,balance05,budget05,debit06,credit06,balance06,budget06) SELECT 'F',NULL,NULL,NULL,NULL,NULL,NULL,rowdesc,v.rownum,v.nozero,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,v.noprint,isnull(sum(debit05),0) as debit05,isnull(sum(credit05),0) as credit05 ,isnull(sum(balance05),0) as balance05,isnull(sum(budget05),0) as budget05,isnull(sum(debit06),0) as debit06,isnull(sum(credit06),0) as credit06 ,isnull(sum(balance06),0) as balance06,isnull(sum(budget06),0) as budget06 FROM (SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,sum(debit) as debit05,sum(credit) as credit05,sum(balance) as balance05,sum(budget) as budget05,cast(0 as decimal(16,2)) as debit06,cast(0 as decimal(16,2)) as credit06,cast(0 as decimal(16,2)) as balance06,cast(0 as decimal(16,2)) as budget06 FROM #glcoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 4 and not (t0.nozero = 1 and t0.balance = 0) and acctid is null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor having ((sum(t0.balance) <> 0)and t0.nozero = 1) or t0.nozero = 0 UNION SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,0 as debit05,0 as credit05,0 as balance05,0 as budget05,sum(debit) as debit06,sum(credit) as credit06,sum(balance) as balance06,sum(budget) as budget06 FROM #glcoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 5 and not (t0.nozero = 1 and t0.balance = 0) and acctid is null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor having ((sum(t0.balance) <> 0)and t0.nozero = 1) or t0.nozero = 0) AS v group by v.rowdesc,v.rownum,v.nozero,v.noprint ,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor
246done execute
247INSERT INTO ##tempresults530(resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,noprint,debit05,credit05,balance05,budget05,debit06,credit06,balance06,budget06) SELECT 'A',NULL,NULL,NULL,NULL,NULL,NULL,rowdesc,v.rownum,v.nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,v.noprint,isnull(sum(debit05),0) as debit05,isnull(sum(credit05),0) as credit05 ,isnull(sum(balance05),0) as balance05,isnull(sum(budget05),0) as budget05,isnull(sum(debit06),0) as debit06,isnull(sum(credit06),0) as credit06 ,isnull(sum(balance06),0) as balance06,isnull(sum(budget06),0) as budget06 FROM (SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,sum(debit) as debit05,sum(credit) as credit05,sum(balance) as balance05,sum(budget) as budget05,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,cast(0 as decimal(16,2)) as debit06,cast(0 as decimal(16,2)) as credit06,cast(0 as decimal(16,2)) as balance06,cast(0 as decimal(16,2)) as budget06 FROM #glcoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 4 and not (t0.nozero = 1 and t0.balance = 0) and acctid is not null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5 having ((sum(t0.balance) <> 0)and t0.nozero = 1) or t0.nozero = 0 UNION SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,0 as debit05,0 as credit05,0 as balance05,0 as budget05,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,sum(debit) as debit06,sum(credit) as credit06,sum(balance) as balance06,sum(budget) as budget06 FROM #glcoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 5 and not (t0.nozero = 1 and t0.balance = 0) and acctid is not null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5 having ((sum(t0.balance) <> 0)and t0.nozero = 1) or t0.nozero = 0) AS v group by v.rowdesc,v.rownum,v.nozero,v.noprint ,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5
248done execute
249startcol = 9
250colcount = 6
251to xfer
252INSERT INTO ##glresults530 SELECT resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,noprint,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,isnull(sum(debit02),0),isnull(sum(credit02),0),isnull(sum(balance02),0),isnull(sum(budget02),0),isnull(sum(debit03),0),isnull(sum(credit03),0),isnull(sum(balance03),0),isnull(sum(budget03),0),isnull(sum(debit04),0),isnull(sum(credit04),0),isnull(sum(balance04),0),isnull(sum(budget04),0),isnull(sum(debit05),0),isnull(sum(credit05),0),isnull(sum(balance05),0),isnull(sum(budget05),0),isnull(sum(debit06),0),isnull(sum(credit06),0),isnull(sum(balance06),0),isnull(sum(budget06),0)
253INSERT INTO ##glresults530 SELECT resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,noprint,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,isnull(sum(debit02),0),isnull(sum(credit02),0),isnull(sum(balance02),0),isnull(sum(budget02),0),isnull(sum(debit03),0),isnull(sum(credit03),0),isnull(sum(balance03),0),isnull(sum(budget03),0),isnull(sum(debit04),0),isnull(sum(credit04),0),isnull(sum(balance04),0),isnull(sum(budget04),0),isnull(sum(debit05),0),isnull(sum(credit05),0),isnull(sum(balance05),0),isnull(sum(budget05),0),isnull(sum(debit06),0),isnull(sum(credit06),0),isnull(sum(balance06),0),isnull(sum(budget06),0) FROM ##tempresults530 GROUP BY resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,noprint,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor
254Warning: Null value is eliminated by an aggregate or other SET operation.
255back xfer
256INSERT INTO ##glresults530(resulttype,rownum,rowdesc,noprint,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,debit02,credit02,balance02,budget02,debit03,credit03,balance03,budget03,debit04,credit04,balance04,budget04,debit05,credit05,balance05,budget05,debit06,credit06,balance06,budget06) select distinct 'F',r0.rownum,r0.rowdesc,r0.noprint,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from df_dfx_rowdtl as r0  left join df_dfx_fontstyle as f0 on r0.fontstyle = f0.fontstyle_id where (r0.rowsetid = 17 AND r0.noprint = 0 AND (r0.rowtype IN (5,6,7,8,10,11,12,13,14) or (r0.rowtype = 0 and r0.setdefid = 0 and r0.nozero = 0 )))
257INSERT INTO ##glresults530(resulttype,rownum,rowdesc,noprint,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,debit02,credit02,balance02,budget02,debit03,credit03,balance03,budget03,debit04,credit04,balance04,budget04,debit05,credit05,balance05,budget05,debit06,credit06,balance06,budget06) select distinct 'F',r0.rownum,r0.rowdesc,r0.noprint,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 from df_dfx_rowdtl as r0  left join df_dfx_fontstyle as f0 on r0.fontstyle = f0.fontstyle_id where r0.rowsetid = 17 and r0.noprint = 0 and r0.nozero = 0 and  r0.rowcalc not like 'RND(%' and  NOT EXISTS (SELECT 1 FROM ##glresults530 as j0 WHERE resulttype = 'F' AND j0.rownum = r0.rownum)
258UPDATE ##glresults530 SET balance03 = debit03
259UPDATE ##glresults530 SET balance04 = credit04
260Calling dfxCalcColumns
261B+C-+D
262returned:B+C-+D
263returned:B+C-+D
264^02+^03-+^04
265@toptr = 
2660
267fetch calcwork: ^02+^03-+^04
268rparen count = 0
269lparen count = 0
270UPDATE ##glresults530 SET balance06=ISNULL((balance02+balance03-+balance04),0)
271return from dfxCalcColumns
272Calling dfxRefactorColumns
273calcrowfinal
274calc row final execution begins
275##fincalc530
276SELECT * INTO ##fincalc530 FROM ##glresults530 AS t0 WHERE 1=1 AND EXISTS(select * from #worktab as j0 where t0.rownum between j0.fromrownum and j0.torownum and 
277				(exists(select * from #worktab as j1 where j1.rownum = j0.rownum  and substring(operator,1,1) in ('*','/','=','+','-'))) or exists(select * from df_dfx_rowdtl as j1 where j1.rowsetid = 17 and j1.rownum = t0.rownum and j1.rowtype in (0,5) and j1.setdefid <> 0)) AND resulttype = 'F'
278INSERT INTO #fincoldata(masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,node,parent,parentrefid,leaf,rownum,rowmod,nozero,colnum,debit,credit,balance,budget) SELECT masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,node,parent,parentrefid,leaf,rownum,0,nozero,1,debit02,credit02,balance02,budget02 FROM ##fincalc530 AS t0 WHERE NOT EXISTS(SELECT * FROM df_dfx_rowdtl AS j0 WHERE j0.rowsetid = 17 AND j0.rownum = t0.rownum AND j0.rowtype in (3))AND (t0.masked_glnum IS NOT NULL or resulttype = 'F')
279INSERT INTO #fincoldata(masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,node,parent,parentrefid,leaf,rownum,rowmod,nozero,colnum,debit,credit,balance,budget) SELECT masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,node,parent,parentrefid,leaf,rownum,0,nozero,2,debit03,credit03,balance03,budget03 FROM ##fincalc530 AS t0 WHERE NOT EXISTS(SELECT * FROM df_dfx_rowdtl AS j0 WHERE j0.rowsetid = 17 AND j0.rownum = t0.rownum AND j0.rowtype in (3))AND (t0.masked_glnum IS NOT NULL or resulttype = 'F')
280INSERT INTO #fincoldata(masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,node,parent,parentrefid,leaf,rownum,rowmod,nozero,colnum,debit,credit,balance,budget) SELECT masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,node,parent,parentrefid,leaf,rownum,0,nozero,3,debit04,credit04,balance04,budget04 FROM ##fincalc530 AS t0 WHERE NOT EXISTS(SELECT * FROM df_dfx_rowdtl AS j0 WHERE j0.rowsetid = 17 AND j0.rownum = t0.rownum AND j0.rowtype in (3))AND (t0.masked_glnum IS NOT NULL or resulttype = 'F')
281INSERT INTO #fincoldata(masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,node,parent,parentrefid,leaf,rownum,rowmod,nozero,colnum,debit,credit,balance,budget) SELECT masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,node,parent,parentrefid,leaf,rownum,0,nozero,4,debit05,credit05,balance05,budget05 FROM ##fincalc530 AS t0 WHERE NOT EXISTS(SELECT * FROM df_dfx_rowdtl AS j0 WHERE j0.rowsetid = 17 AND j0.rownum = t0.rownum AND j0.rowtype in (3))AND (t0.masked_glnum IS NOT NULL or resulttype = 'F')
282INSERT INTO #fincoldata(masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,node,parent,parentrefid,leaf,rownum,rowmod,nozero,colnum,debit,credit,balance,budget) SELECT masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,node,parent,parentrefid,leaf,rownum,0,nozero,5,debit06,credit06,balance06,budget06 FROM ##fincalc530 AS t0 WHERE NOT EXISTS(SELECT * FROM df_dfx_rowdtl AS j0 WHERE j0.rowsetid = 17 AND j0.rownum = t0.rownum AND j0.rowtype in (3))AND (t0.masked_glnum IS NOT NULL or resulttype = 'F')
283factor/proximity
2841
2851
2861
287NO ROUND!
288insert into #calcdata 
289select node,parent,parentrefid,leaf,rownum,rowmod,1,colnum,isnull(sum(debit),0) as debit,isnull(sum(credit),0) as credit,isnull(sum(balance),0) as balance,isnull(sum(budget),0) as budget 
290from #fincoldata 
291where rowmod = 0 and suppress_round <> 1 AND EXISTS(select * from df_dfx_reportdef as j0 JOIN df_dfx_coldtl as j1 ON j0.colsetid = j1.colsetid     AND j1.colnum = #fincoldata.colnum and (j1.format like '%.%' OR j1.suppress_round = 1) WHERE j0.reportdef_id = 11) group by node,parent,parentrefid,leaf,rownum,rowmod,colnum
292SUPPRESS ROUND!
293SUPPRESS ROUND!
294insert into #calcdata 
295select node,parent,parentrefid,leaf,rownum,rowmod,1,colnum,isnull(sum(debit),0) as debit,isnull(sum(credit),0) as credit,isnull(sum(balance),0) as balance,isnull(sum(budget),0) as budget 
296from #fincoldata 
297where rowmod = 0 and suppress_round = 1 AND NOT EXISTS(select * from df_dfx_reportdef as j0 JOIN df_dfx_coldtl as j1 ON j0.colsetid = j1.colsetid     AND j1.colnum = #fincoldata.colnum and (j1.format like '%.%' OR j1.suppress_round = 1) WHERE j0.reportdef_id = 11) group by node,parent,parentrefid,leaf,rownum,rowmod,colnum
298SUPPRESS ROUND!
299insert into #calcdata 
300select node,parent,parentrefid,leaf,rownum,rowmod,1,colnum,isnull(sum(debit),0) as debit,isnull(sum(credit),0) as credit,isnull(sum(balance),0) as balance,isnull(sum(budget),0) as budget 
301from #fincoldata 
302where rowmod = 0 and suppress_round = 1 AND EXISTS(select * from df_dfx_reportdef as j0 JOIN df_dfx_coldtl as j1 ON j0.colsetid = j1.colsetid     AND j1.colnum = #fincoldata.colnum and (j1.format like '%.%' OR j1.suppress_round = 1) WHERE j0.reportdef_id = 11) group by node,parent,parentrefid,leaf,rownum,rowmod,colnum
303ROUND!
304insert into #calcdata 
305select node,parent,parentrefid,leaf,rownum,rowmod,1,colnum,round(isnull(sum(debit),0),2) as debit,round(isnull(sum(credit),0),2) as credit,round(isnull(sum(balance),0),2) as balance,round(isnull(sum(budget),0),2) as budget 
306from #fincoldata 
307where rowmod = 0 and suppress_round = 0 and not exists(select * from df_dfx_reportdef as j0 join df_dfx_coldtl as j1 on j0.colsetid = j1.colsetid     and j1.colnum = #fincoldata.colnum and (j1.format like '%.%' or j1.suppress_round = 1) where j0.reportdef_id = 11) group by node,parent,parentrefid,leaf,rownum,rowmod,colnum
308Warning: Null value is eliminated by an aggregate or other SET operation.
309have a record
310inspecting
311end of file: 0
312lpctl = -1
313local_precedence = 0
314SELECT node,parent,parentrefid,leaf,colnum,1 as rownum, cast(0 as decimal(24,8)) as d001, cast(0 as decimal(24,8)) as c001, cast(0 as decimal(24,8)) as b001, cast(0 as decimal(24,8)) as u001 INTO ##workdata530 FROM #calcdata WHERE 1=0 GROUP BY node,parent,parentrefid,leaf,colnum 
315start operator = +
316lparen = (
317rowctl = 7
318literal = 0
319operator = +
320INSERT INTO ##workdata530 SELECT node,parent,parentrefid,leaf,colnum,1,sum(debit),sum(credit),sum(balance),sum(budget) FROM #calcdata WHERE rownum BETWEEN 1    AND 6    AND (rowmod = 0 or rowmod = 1   ) GROUP BY node,parent,parentrefid,leaf,colnum
321have a record
322inspecting
323into calcdata
324INSERT INTO #calcdata (rownum,rowmod,node,parent,parentrefid,leaf,colnum,nozero,debit,credit,balance,budget)SELECT 7,7,node,parent,parentrefid,leaf,colnum,1,(+SUM(d001)),(+SUM(c001)),(+SUM(b001)),(+SUM(u001)) FROM ##workdata530 GROUP BY node,parent,parentrefid,leaf,colnum
325DROP TABLE ##workdata530
326end of file: 1
327in dfxRefactorRows
328leaving refactor rows
329column_list start col = -1
330INSERT INTO ##tempresults530(resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,noprint,debit02,credit02,balance02,budget02,debit03,credit03,balance03,budget03,debit04,credit04,balance04,budget04) SELECT 'F',NULL,NULL,NULL,NULL,NULL,NULL,rowdesc,v.rownum,v.nozero,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,v.noprint,isnull(sum(debit02),0) as debit02,isnull(sum(credit02),0) as credit02 ,isnull(sum(balance02),0) as balance02,isnull(sum(budget02),0) as budget02,isnull(sum(debit03),0) as debit03,isnull(sum(credit03),0) as credit03 ,isnull(sum(balance03),0) as balance03,isnull(sum(budget03),0) as budget03,isnull(sum(debit04),0) as debit04,isnull(sum(credit04),0) as credit04 ,isnull(sum(balance04),0) as balance04,isnull(sum(budget04),0) as budget04 FROM (SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,sum(debit) as debit02,sum(credit) as credit02,sum(balance) as balance02,sum(budget) as budget02,cast(0 as decimal(16,2)) as debit03,cast(0 as decimal(16,2)) as credit03,cast(0 as decimal(16,2)) as balance03,cast(0 as decimal(16,2)) as budget03,cast(0 as decimal(16,2)) as debit04,cast(0 as decimal(16,2)) as credit04,cast(0 as decimal(16,2)) as balance04,cast(0 as decimal(16,2)) as budget04 FROM #fincoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 1 and not (t0.nozero = 1 and t0.balance = 0) and acctid is null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor having ((sum(t0.balance) <> 0)and t0.nozero = 1) or t0.nozero = 0 UNION SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,0 as debit02,0 as credit02,0 as balance02,0 as budget02,sum(debit) as debit03,sum(credit) as credit03,sum(balance) as balance03,sum(budget) as budget03,0 as debit04,0 as credit04,0 as balance04,0 as budget04 FROM #fincoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 2 and not (t0.nozero = 1 and t0.balance = 0 and t0.debit = 0 and t0.credit = 0) and acctid is null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor having ((sum(t0.balance) <> 0 or sum(t0.debit) <> 0 or sum(t0.credit) <> 0) and t0.nozero = 1) or t0.nozero = 0 UNION SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,0 as debit02,0 as credit02,0 as balance02,0 as budget02,0 as debit03,0 as credit03,0 as balance03,0 as budget03,sum(debit) as debit04,sum(credit) as credit04,sum(balance) as balance04,sum(budget) as budget04 FROM #fincoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 3 and not (t0.nozero = 1 and t0.balance = 0 and t0.debit = 0 and t0.credit = 0) and acctid is null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor having ((sum(t0.balance) <> 0 or sum(t0.debit) <> 0 or sum(t0.credit) <> 0) and t0.nozero = 1) or t0.nozero = 0) AS v group by v.rowdesc,v.rownum,v.nozero,v.noprint ,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor
331done execute
332startcol = 4
333colcount = 6
334column_list start col = 4
335INSERT INTO ##tempresults530(resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,noprint,debit05,credit05,balance05,budget05,debit06,credit06,balance06,budget06) SELECT 'F',NULL,NULL,NULL,NULL,NULL,NULL,rowdesc,v.rownum,v.nozero,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,v.noprint,isnull(sum(debit05),0) as debit05,isnull(sum(credit05),0) as credit05 ,isnull(sum(balance05),0) as balance05,isnull(sum(budget05),0) as budget05,isnull(sum(debit06),0) as debit06,isnull(sum(credit06),0) as credit06 ,isnull(sum(balance06),0) as balance06,isnull(sum(budget06),0) as budget06 FROM (SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,sum(debit) as debit05,sum(credit) as credit05,sum(balance) as balance05,sum(budget) as budget05,cast(0 as decimal(16,2)) as debit06,cast(0 as decimal(16,2)) as credit06,cast(0 as decimal(16,2)) as balance06,cast(0 as decimal(16,2)) as budget06 FROM #fincoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 4 and not (t0.nozero = 1 and t0.balance = 0) and acctid is null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor having ((sum(t0.balance) <> 0)and t0.nozero = 1) or t0.nozero = 0 UNION SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,0 as debit05,0 as credit05,0 as balance05,0 as budget05,sum(debit) as debit06,sum(credit) as credit06,sum(balance) as balance06,sum(budget) as budget06 FROM #fincoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 5 and not (t0.nozero = 1 and t0.balance = 0) and acctid is null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor having ((sum(t0.balance) <> 0)and t0.nozero = 1) or t0.nozero = 0) AS v group by v.rowdesc,v.rownum,v.nozero,v.noprint ,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor
336done execute
337startcol = 9
338colcount = 6
339to xfer
340INSERT INTO ##fincalc530 SELECT resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,noprint,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,isnull(sum(debit02),0),isnull(sum(credit02),0),isnull(sum(balance02),0),isnull(sum(budget02),0),isnull(sum(debit03),0),isnull(sum(credit03),0),isnull(sum(balance03),0),isnull(sum(budget03),0),isnull(sum(debit04),0),isnull(sum(credit04),0),isnull(sum(balance04),0),isnull(sum(budget04),0),isnull(sum(debit05),0),isnull(sum(credit05),0),isnull(sum(balance05),0),isnull(sum(budget05),0),isnull(sum(debit06),0),isnull(sum(credit06),0),isnull(sum(balance06),0),isnull(sum(budget06),0)
341INSERT INTO ##fincalc530 SELECT resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,noprint,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,isnull(sum(debit02),0),isnull(sum(credit02),0),isnull(sum(balance02),0),isnull(sum(budget02),0),isnull(sum(debit03),0),isnull(sum(credit03),0),isnull(sum(balance03),0),isnull(sum(budget03),0),isnull(sum(debit04),0),isnull(sum(credit04),0),isnull(sum(balance04),0),isnull(sum(budget04),0),isnull(sum(debit05),0),isnull(sum(credit05),0),isnull(sum(balance05),0),isnull(sum(budget05),0),isnull(sum(debit06),0),isnull(sum(credit06),0),isnull(sum(balance06),0),isnull(sum(budget06),0) FROM ##tempresults530 GROUP BY resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,noprint,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor
342Warning: Null value is eliminated by an aggregate or other SET operation.
343back xfer
344DELETE FROM ##glresults530 WHERE EXISTS(select * from ##fincalc530 as j0 where j0.rownum = ##glresults530.rownum and isnull(##glresults530.node,0) = isnull(j0.node,0) and isnull(##glresults530.leaf,0) = isnull(j0.leaf,0) and isnull(##glresults530.parent,0) = isnull(j0.parent,0) and isnull(##glresults530.parentrefid,0) = isnull(j0.parentrefid,0) and ##glresults530.resulttype = j0.resulttype)
345INSERT INTO ##glresults530 SELECT * FROM ##fincalc530
346B+C-+D
347returned:B+C-+D
348returned:B+C-+D
349^02+^03-+^04
350@toptr = 
3510
352fetch calcwork: ^02+^03-+^04
353rparen count = 0
354lparen count = 0
355UPDATE ##glresults530 SET balance06=ISNULL((balance02+balance03-+balance04),0) WHERE EXISTS(select * from #calctab as j0 where (##glresults530.rownum = j0.rownum) and 
356			exists(select * from #calctab as j1 where j1.rownum = j0.rownum  and substring(operator,1,1) in ('*','/','=','+','-') and coldest <> 5 ))
357ExceptionLevel:1
358Format:#,##0.00;(#,##0.00);0.00"
359INSERT INTO #ValidRownum(node,leaf,rownum)
360SELECT  node,leaf,rownum FROM ##glresults530 AS g0 
361WHERE g0.resulttype = 'F' and isnull(balance02,0) <> 0 OR isnull(balance03,0) <> 0 OR isnull(balance04,0) <> 0 OR isnull(balance05,0) <> 0 OR isnull(balance06,0) <> 0 
362relatedsql get
363Jun 17 2019  3:26PM
364SELECT * INTO ##related530 FROM ##glresults530 AS t0 WHERE resulttype = 'F' AND EXISTS(select 1 from #calctab as j0 where j0.rownum = t0.rownum) AND (EXISTS (SELECT 1 FROM  #calctab AS j0  JOIN df_dfx_rowdtl AS j1 ON j0.rownum = j1.rownum AND j1.rowsetid = 17 AND j1.rowtype NOT IN (2,3) JOIN df_dfx_rowdtl AS j2 ON upper(j2.rowlabel) = upper(j0.fromrow) AND j2.rowsetid = 17 WHERE j0.rownum = t0.rownum  AND (SELECT COUNT(1) FROM #ValidRownum AS g0 WHERE g0.rownum = j2.rownum ) = 0))
365Jun 17 2019  3:26PM
366CREATE INDEX i_1 ON ##related530(resulttype,rownum)
367SELECT t0.* INTO ##drilldata530 FROM ##glresults530 AS t0  LEFT JOIN df_dfx_rowdtl AS t1 ON t0.rownum = t1.rownum AND t1.rowsetid = 17 WHERE rowtype IN (0,2,3) AND resulttype = 'F'
368UPDATE ##drilldata530 SET resulttype = 'X', leafcode=rowdesc,rowdesc = leafcode
369INSERT INTO #zero SELECT resulttype,isnull(t0.node,0),isnull(t0.leaf,0),t0.rownum,isnull(parentrefid,0),isnull(acctid,0) FROM ##glresults530 AS t0  JOIN df_dfx_rowdtl AS t1 ON t0.rownum = t1.rownum AND t1.rowsetid = 17 WHERE 1=1  AND ((t1.rowtype IN (2,3) OR (t1.rowtype = 0 AND t1.setdefid <> 0)) AND (isnull(t1.nozero,0) = 1 AND round(isnull(balance02,0), 2)= 0  AND  round(isnull(balance03,0), 2)= 0  AND  round(isnull(balance04,0), 2)= 0  AND  round(isnull(balance05,0), 2)= 0  AND  round(isnull(balance06,0), 2)= 0 )) AND isnull(t1.rowcalc,'') not like '%\%%' escape '\'
370SELECT t0.*,CASE WHEN t1.rowtype IN (10,11) THEN 10 WHEN t1.rowtype = 0 AND t1.setdefid = 0 THEN 5 WHEN t1.rowtype IS NULL THEN 5 ELSE t1.rowtype END as rowtype ,fm0.format,CAST('' AS VARCHAR(20)) as acct_string,tr.tabkey,tr.tabkey2,isnull(cs.currsymb,0) as currsymb,ISNULL(rs.sortind,0) as sortind,'1' as bookcode,'USD' as rptcurr, t1.suppress_round FROM ##glresults530 AS t0 LEFT JOIN df_dfx_rowdtl AS t1 ON t0.rownum = t1.rownum AND t1.rowsetid = 17 LEFT JOIN #rowformats AS fm0 ON fm0.rownum = t0.rownum LEFT JOIN #tabrow AS tr ON tr.rownum = t0.rownum LEFT JOIN #currsymb AS cs ON cs.rownum = t0.rownum AND ISNULL(cs.leaf,'') = ISNULL(t0.leaf,'') AND ISNULL(cs.node,'') = ISNULL(t0.node,'') LEFT JOIN #rowsort AS rs ON isnull(rs.node,'') = isnull(t0.node,'') AND isnull(rs.leaf,'') = isnull(t0.leaf,'') AND rs.rownum = t0.rownum WHERE NOT (resulttype = 'A' AND masked_glnum IS NULL) AND (t0.rownum <= 8          OR t0.rownum IN (9998,9999)) AND ISNULL(t1.noprint,0) <> 1 AND isnull(t1.rowtype,'0') <> '9'	AND NOT EXISTS(SELECT * FROM #zero AS j0 WHERE j0.resulttype = t0.resulttype AND j0.node = isnull(t0.node,0) and j0.leaf = isnull(t0.leaf,0) and j0.rownum = t0.rownum and j0.parentrefid = isnull(t0.parentrefid,0) and j0.acctid = isnull(t0.acctid,0))AND NOT EXISTS (SELECT * FROM ##related530 AS g0 WHERE g0.rownum = t0.rownum AND isnull(g0.node,0) = isnull(t0.node,0) and isnull(g0.leaf,0) = isnull(t0.leaf,0)) UNION SELECT t0.*,CASE WHEN t1.rowtype IN (10,11) THEN 10 WHEN t1.rowtype = 0 AND t1.setdefid = 0 THEN 5 WHEN t1.rowtype IS NULL THEN 5 ELSE t1.rowtype END as rowtype ,fm0.format,CAST('' AS VARCHAR(20)) as acct_string,tr.tabkey,tr.tabkey2,isnull(cs.currsymb,0) as currsymb,ISNULL(rs.sortind,0) as sortind,'1' as bookcode,'USD' as rptcurr, t1.suppress_round FROM ##drilldata530 AS t0 LEFT JOIN df_dfx_rowdtl AS t1 ON t0.rownum = t1.rownum AND t1.rowsetid = 17 LEFT JOIN #rowformats AS fm0 ON fm0.rownum = t0.rownum LEFT JOIN #tabrow AS tr ON tr.rownum = t0.rownum LEFT JOIN #currsymb AS cs ON cs.rownum = t0.rownum AND ISNULL(cs.leaf,'') = ISNULL(t0.leaf,'') AND ISNULL(cs.node,'') = ISNULL(t0.node,'') LEFT JOIN #rowsort AS rs ON isnull(rs.node,'') = isnull(t0.node,'') AND isnull(rs.leaf,'') = isnull(t0.leaf,'') AND rs.rownum = t0.rownum WHERE NOT (resulttype = 'A' AND masked_glnum IS NULL) AND (t0.rownum <= 8          OR t0.rownum IN (9998,9999)) AND ISNULL(t1.noprint,0) <> 1 AND isnull(t1.rowtype,'0') <> '9'	AND NOT EXISTS(SELECT * FROM #zero AS j0 WHERE j0.resulttype = t0.resulttype AND j0.node = isnull(t0.node,0) and j0.leaf = isnull(t0.leaf,0) and j0.rownum = t0.rownum and j0.parentrefid = isnull(t0.parentrefid,0) and j0.acctid = isnull(t0.acctid,0))AND NOT EXISTS (SELECT * FROM ##related530 AS g0 WHERE g0.rownum = t0.rownum AND isnull(g0.node,0) = isnull(t0.node,0) and isnull(g0.leaf,0) = isnull(t0.leaf,0)) ORDER BY resulttype desc,t0.node,t0.leaf,sortind,t0.rownum,masked_glnum
371 
372TRUNCATE TABLE dbo.df_dfx_cache11_3
373HERE!
374SELECT * INTO dbo.df_dfx_cache11_3 FROM #glacctrow