· 8 years ago · Dec 08, 2017, 11:06 AM
1CREATE PROCEDURE dbo.spRebuildTotals (
2 @dtFrom DATETIME = NULL,
3 @dtTo DATETIME = NULL,
4 @hproperty INT = NULL)
5AS
6
7SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
8/*
9--Test Mode
10DECLARE @dtFrom DATETIME
11DECLARE @dtTo DATETIME
12DECLARE @hproperty INT
13SET @dtfrom = '2008-08-01'
14SET @dtto = '2008-08-01'
15SET @hproperty = NULL --457 is 4ph
16PRINT 'Start ' + CONVERT(VARCHAR(50),GETDATE(),14)
17--End Test Mode
18*/
19
20/* Re-write of Rebuild Totals Process explicitly for Housing Authority of Portland */
21/* NOTE: This is designed for an installation with a single chart of accounts!!!! */
22/* if using multiple charts of accounts, run this process for each chart, */
23/* and add chart into all of the queries as a parameter */
24
25DECLARE @hretain INT
26DECLARE @tblProps TABLE (hmy INT PRIMARY KEY CLUSTERED, dtmax DATETIME, dtMin DATETIME, iendofyear INT)
27DECLARE @tblMonths TABLE (dtMonth DATETIME PRIMARY KEY CLUSTERED)
28DECLARE @dtMonth DATETIME
29DECLARE @dtMaxMonth DATETIME
30
31PRINT 'Begin: ' + CONVERT(VARCHAR(50),GETDATE(),14)
32
33/* Set dtTo as Current Operating Month If Null */
34IF (@dtTo IS NULL)
35 BEGIN
36 SELECT @dtTo = dtcurmonth2 FROM param
37 END
38
39
40/* Get retained earnings account from parameters ONLY WORKS WITH SINGLE CHART OF ACCOUNTS */
41SELECT @hretain = hretain FROM param
42
43
44/* Creates List of Properties to rebuild and inserts into table variable */
45IF (@hproperty IS NULL)
46 BEGIN
47 INSERT INTO @tblProps SELECT hmy, @dtTo, '1950-01-01', iendofyear FROM property
48 END
49 ELSE
50 BEGIN
51 INSERT INTO @tblProps SELECT lp.hproperty, @dtto, '1950-01-01', p.iendofyear FROM listprop2 lp
52 INNER JOIN property p ON lp.hproperty = p.hmy AND p.itype = 3
53 WHERE lp.hproplist = @hproperty
54 END
55
56
57/* Gets earliest tran month if no dtfrom is indicated, purged records need not be rebuilt */
58IF (@dtfrom IS NULL)
59 BEGIN
60 SELECT @dtfrom = MIN(upostdate) FROM trans
61 END
62
63/* Create Temp Table to hold the GL aggregates by type, account, month, book, and property */
64/* Data types exactly match total table so no SQL server implicit conversion necessary */
65IF OBJECT_ID('zTEMP_GL') IS NOT NULL
66 DROP TABLE zTEMP_GL
67CREATE TABLE zTemp_GL (
68 hacct NUMERIC(18,0),
69 umonth DATETIME,
70 amt NUMERIC(21,2),
71 ibook INT,
72 hppty NUMERIC(18,0))
73
74PRINT 'Set up Parameters and GL Table: ' + CONVERT(VARCHAR(50),GETDATE(),14)
75
76INSERT INTO zTemp_GL
77
78/* Cash Receipts Cash Account*/
79SELECT
80 d.hacct,
81 d.CashPost,
82 SUM(-d.samount),
83 0,
84 d.hProp
85FROM
86 trans t
87 INNER JOIN detail d ON d.hInvOrRec = t.hMy
88 INNER JOIN @tblProps p ON d.hprop = p.hmy
89WHERE
90 d.hInvOrRec BETWEEN 600000000 AND 699999999
91 AND d.cashPost >= @dtFrom
92 AND t.itype = 6
93GROUP BY
94 d.hacct,
95 d.CashPost,
96 d.hProp
97
98UNION ALL
99/* Cash Receipts Offset Account */
100SELECT
101 t1.hoffsetacct,
102 d.CashPost,
103 SUM(d.samount),
104 0,
105 d.hProp
106FROM
107 trans t1
108 INNER JOIN detail d ON t1.hmy = d.hInvOrRec
109 INNER JOIN @tblProps p ON d.hprop = p.hmy
110WHERE
111 d.hInvOrRec BETWEEN 600000000 AND 699999999
112 AND d.cashPost >= @dtFrom
113 AND t1.itype = 6
114GROUP BY
115 t1.hoffsetacct,
116 d.CashPost,
117 d.hProp
118
119UNION ALL
120/* Receipts Accrual*/
121SELECT
122 ISNULL(d.hAccrualAcct, d.hacct),
123 d.CashPost,
124 SUM(-d.samount),
125 1,
126 d.hProp
127FROM
128 trans t
129 INNER JOIN detail d ON d.hInvOrRec = t.hMy
130 INNER JOIN @tblProps p ON d.hprop = p.hmy
131WHERE
132 d.hInvOrRec BETWEEN 600000000 AND 699999999
133 AND d.cashPost >= @dtFrom
134 AND t.itype = 6
135GROUP BY
136 ISNULL(d.hAccrualAcct, d.hacct),
137 d.CashPost,
138 d.hProp
139
140UNION ALL
141/* Cash Accrual Offset Account */
142SELECT
143 t1.hOffsetAcct,
144 d.CashPost,
145 SUM(d.samount),
146 1,
147 d.hProp
148FROM
149 trans t1
150 INNER JOIN detail d ON t1.hmy = d.hInvOrRec
151 INNER JOIN @tblProps p ON d.hprop = p.hmy
152WHERE
153 d.hInvOrRec BETWEEN 600000000 AND 699999999
154 AND d.cashPost >= @dtFrom
155 AND t1.itype = 6
156GROUP BY
157 t1.hOffsetAcct,
158 d.CashPost,
159 d.hProp
160
161UNION ALL
162/* Paid Invoices */
163SELECT
164 d.hAcct,
165 d.CashPost,
166 SUM(d.sAmount),
167 0,
168 d.hProp
169FROM
170 trans t
171 INNER JOIN detail d ON d.hInvOrRec = t.hMy
172 INNER JOIN @tblProps p ON d.hprop = p.hmy
173WHERE
174 d.hChkOrChg BETWEEN 200000000 AND 299999999
175 AND d.hInvOrRec BETWEEN 300000000 AND 399999999
176 AND d.cashPost >= @dtFrom
177 AND t.itype = 3
178GROUP BY
179 d.hAcct,
180 d.CashPost,
181 d.hProp
182
183UNION ALL
184/* Paid Invoice Offset */
185SELECT
186 t2.hOffsetAcct,
187 d.CashPost,
188 SUM(-d.sAmount),
189 0,
190 d.hProp
191FROM
192 trans t2
193 INNER JOIN detail d ON t2.hMy = d.hInvOrRec
194 INNER JOIN @tblProps p ON d.hprop = p.hmy
195WHERE
196 d.hChkOrChg BETWEEN 200000000 AND 299999999
197 AND d.hInvOrRec BETWEEN 300000000 AND 399999999
198 AND d.cashPost >= @dtFrom
199 AND t2.itype = 3
200GROUP BY
201 t2.hOffsetAcct,
202 d.CashPost,
203 d.hProp
204
205UNION ALL
206/* Invoice Accrual */
207SELECT
208 ISNULL(d.hAccrualAcct,d.hAcct),
209 d.CashPost,
210 SUM(d.sAmount),
211 1,
212 d.hProp
213FROM
214 trans t
215 INNER JOIN detail d ON d.hInvOrRec = t.hMy
216 INNER JOIN @tblProps p ON d.hprop = p.hmy
217WHERE
218 d.hChkOrChg BETWEEN 200000000 AND 299999999
219 AND d.hInvOrrec BETWEEN 300000000 AND 399999999
220 AND d.cashPost >= @dtFrom
221 AND t.itype = 3
222GROUP BY
223 ISNULL(d.hAccrualAcct,d.hAcct),
224 d.CashPost,
225 d.hProp
226
227UNION ALL
228/* Invoice Accrual Offset */
229SELECT
230 t2.hOffsetAcct,
231 d.CashPost,
232 SUM(-d.sAmount),
233 1,
234 d.hProp
235FROM
236 trans t2
237 INNER JOIN detail d ON t2.hMy = d.hInvOrRec
238 INNER JOIN @tblProps p ON d.hprop = p.hmy
239WHERE
240 d.hChkOrChg BETWEEN 200000000 AND 299999999
241 AND d.hInvOrrec BETWEEN 300000000 AND 399999999
242 AND d.cashPost >= @dtFrom
243 AND t2.itype = 3
244GROUP BY
245 t2.hOffsetAcct,
246 d.CashPost,
247 d.hProp
248
249UNION ALL
250/* Accrued Charges Offset*/
251SELECT
252 t1.hOffsetAcct,
253 t1.uPostDate,
254 SUM(-t1.sTotalAmount),
255 1,
256 t1.hProp
257FROM
258 trans t1
259 INNER JOIN @tblProps p ON t1.hprop = p.hmy
260WHERE
261 t1.iType = 7
262 AND t1.hAccrualAcct IS NOT NULL
263 AND t1.uPostDate >= @dtFrom
264GROUP BY
265 t1.hOffsetAcct,
266 t1.uPostDate,
267 t1.hProp
268
269UNION ALL
270/* Accrued Charges Accrual */
271SELECT
272 t1.hAccrualAcct,
273 t1.uPostDate,
274 SUM(t1.sTotalAmount),
275 1,
276 t1.hProp
277FROM
278 trans t1
279 INNER JOIN @tblProps p ON t1.hprop = p.hmy
280WHERE
281 t1.iType = 7
282 AND t1.hAccrualAcct IS NOT NULL
283 AND t1.uPostDate >= @dtFrom
284GROUP BY
285 t1.hAccrualAcct,
286 t1.uPostDate,
287 t1.hProp
288
289UNION ALL
290/* Invoice Accrual Post Date for Detail Account*/
291SELECT
292 d.hAcct,
293 d.AccrualPost,
294 SUM(d.sAmount),
295 1,
296 d.hProp
297FROM
298 trans t
299 INNER JOIN detail d ON d.hInvOrRec = t.hMy
300 INNER JOIN @tblProps p ON d.hprop = p.hmy
301WHERE
302 d.hAccrualAcct > 0
303 AND d.hInvOrRec BETWEEN 300000000 AND 399999999
304 AND d.AccrualPost >= @dtFrom
305 AND t.itype = 3
306GROUP BY
307 d.hAcct,
308 d.AccrualPost,
309 d.hProp
310
311UNION ALL
312/* Invoice Accrual for Accrual Account */
313SELECT
314 t1.hAccrualAcct,
315 d.AccrualPost,
316 SUM(-d.sAmount),
317 1,
318 d.hProp
319FROM
320 trans t1
321 INNER JOIN detail d ON t1.hMy = d.hInvOrRec
322 INNER JOIN @tblProps p ON d.hprop = p.hmy
323WHERE
324 d.hAccrualAcct > 0
325 AND t1.iType = 3
326 AND d.hInvOrRec BETWEEN 300000000 AND 399999999
327 AND d.AccrualPost >= @dtFrom
328GROUP BY
329 t1.hAccrualAcct,
330 d.AccrualPost,
331 d.hProp
332
333UNION ALL
334/* Journal Entries set as different book */
335SELECT
336 d.hAcct,
337 d.CashPost,
338 SUM(d.sAmount),
339 CASE WHEN t.iType2 = 1000 THEN 0 ELSE iType2 END,
340 d.hProp
341FROM
342 detail d
343 INNER JOIN trans t ON d.hInvOrRec = t.hMy
344 INNER JOIN @tblProps p ON d.hprop = p.hmy
345WHERE
346 d.hInvOrRec BETWEEN 1000000000 AND 1099999999
347 AND d.CashPost >= @dtFrom
348 AND t.itype = 10
349GROUP BY
350 d.hAcct,
351 d.CashPost,
352 d.hProp,
353 CASE WHEN t.iType2 = 1000 THEN 0 ELSE iType2 END
354
355UNION ALL
356/* Journal Entries of type 1000 have seperate cash and accrual dates */
357SELECT
358 d.hAcct,
359 d.CashPost ,
360 SUM(d.sAmount),
361 1,
362 d.hProp
363FROM
364 detail d
365 INNER JOIN trans t ON d.hInvOrRec = t.hMy
366 INNER JOIN @tblProps p ON d.hprop = p.hmy
367WHERE
368 t.iType2 = 1000
369 AND d.hInvOrRec BETWEEN 1000000000 AND 1099999999
370 AND d.CashPost >= @dtFrom
371 AND t.itype = 10
372GROUP BY
373 d.hAcct,
374 d.CashPost,
375 d.hProp
376PRINT 'Loaded zTemp_GL ' + CONVERT(VARCHAR(50),GETDATE(),14)
377
378/* Create Aggregations that mimick totals table, then index it like total table */
379/* It is faster to create the table and then index it then vice-versa */
380IF OBJECT_ID('zTEMP_TOTAL') IS NOT NULL
381 DROP TABLE zTEMP_TOTAL
382CREATE TABLE zTemp_TOTAL (
383 hacct NUMERIC(18,0) NOT NULL,
384 umonth DATETIME NOT NULL,
385 amt NUMERIC(21,2),
386 ibook INT NOT NULL,
387 hppty NUMERIC(18,0) NOT NULL)
388
389INSERT INTO zTemp_TOTAL
390SELECT
391 hacct,
392 umonth,
393 SUM(amt),
394 ibook,
395 hppty
396FROM
397 zTemp_GL
398GROUP BY
399 hacct,
400 umonth,
401 ibook,
402 hppty
403PRINT 'Loaded zTemp_Total ' + CONVERT(VARCHAR(50),GETDATE(),14)
404
405/* Index table same as TOTAL table, this will improve performance */
406ALTER TABLE [dbo].[zTEMP_TOTAL] WITH NOCHECK ADD
407 CONSTRAINT [PK_zTEMP_TOTAL] PRIMARY KEY CLUSTERED
408 (
409 [HPPTY],
410 [UMONTH],
411 [IBOOK],
412 [HACCT]
413 )
414
415
416/* Update Total Table with New Totals */
417UPDATE total
418SET smtd = ISNULL(z.amt,0)
419FROM
420 total t
421 INNER JOIN @tblProps p ON t.hppty = p.hmy
422 LEFT JOIN zTemp_TOTAL z ON t.hacct = z.hacct
423 AND t.umonth = z.umonth
424 AND t.ibook = z.ibook
425 AND t.hppty = z.hppty
426WHERE
427 t.umonth >= @dtFrom
428PRINT 'Updated Total Table: ' + CONVERT(VARCHAR(50),GETDATE(),14)
429
430/* Insert New Rows into Total Table that didn't exist */
431INSERT INTO total (umonth, ibook, hacct, hppty, sbegin, smtd, sbeginbudget, sbudget)
432SELECT
433 z.umonth,
434 z.ibook,
435 z.hacct,
436 z.hppty,
437 0,
438 ISNULL(z.amt,0),
439 0,
440 0
441FROM
442 zTemp_TOTAL z
443 LEFT JOIN TOTAL t ON t.hacct = z.hacct
444 AND t.umonth = z.umonth
445 AND t.ibook = z.ibook
446 AND t.hppty = z.hppty
447WHERE
448 t.umonth IS NULL
449PRINT 'Inserted rows in Temp not Total: ' + CONVERT(VARCHAR(50),GETDATE(),14)
450
451/* update the max of the accrual, cash, or operating dates for each property for adding new rows into total */
452UPDATE @tblProps SET dtmax = CASE WHEN cash.dtmax > p1.dtmax THEN cash.dtmax ELSE p1.dtmax END
453FROM @tblProps p1
454 INNER JOIN (SELECT p2.hmy, MAX(d.cashpost) dtmax FROM @tblProps p2 INNER JOIN detail d ON p2.hmy = d.hprop
455 WHERE d.cashpost IS NOT NULL GROUP BY p2.hmy) cash ON p1.hmy = cash.hmy
456--WHERE cash.dtmax IS NOT NULL
457
458UPDATE @tblProps SET dtmax = CASE WHEN ISNULL(accr.dtmax,'1950-03-01') > p1.dtmax THEN accr.dtmax ELSE p1.dtmax END
459FROM @tblProps p1
460 INNER JOIN (SELECT p2.hmy, MAX(d.accrualpost) dtmax FROM @tblProps p2 INNER JOIN detail d ON p2.hmy = d.hprop
461 WHERE d.accrualpost IS NOT NULL GROUP BY p2.hmy) accr ON p1.hmy = accr.hmy
462
463UPDATE @tblProps SET dtmax = CASE WHEN ISNULL(chrg.dtmax,'1950-03-01') > p1.dtmax THEN chrg.dtmax ELSE p1.dtmax END
464FROM @tblProps p1
465 INNER JOIN (SELECT p2.hmy, MAX(t.upostdate) dtmax FROM @tblProps p2 INNER JOIN trans t ON p2.hmy = t.hprop AND t.itype = 7
466 WHERE t.upostdate IS NOT NULL GROUP BY p2.hmy) chrg ON p1.hmy = chrg.hmy
467
468SELECT @dtMaxMonth = MAX(dtMax) FROM @tblProps
469PRINT 'Determined Max Month: ' + CONVERT(VARCHAR(50),GETDATE(),14)
470
471
472/* Create a table of the months needed for any property */
473SET @dtMonth = @dtFrom
474WHILE @dtMonth <= @dtMaxMonth
475BEGIN
476 INSERT INTO @tblMonths SELECT @dtMonth
477 SET @dtMonth = DATEADD(m,1,@dtMonth)
478END
479
480/* Find the first month a property exists in the total table */
481UPDATE @tblProps
482SET dtMin = tot.dtmin
483FROM @tblProps p INNER JOIN
484(SELECT hppty, MIN(umonth) dtmin FROM total t GROUP BY hppty) tot ON p.hmy = tot.hppty
485PRINT 'Found Min Month ' + CONVERT(VARCHAR(50),GETDATE(),14)
486
487
488/* Insert new Rows into Total Table that should Exist, but don't */
489INSERT INTO total (umonth, ibook, hacct, hppty, sbegin, smtd, sbeginbudget, sbudget)
490SELECT
491 m.dtmonth,
492 t.ibook,
493 t.hacct,
494 t.hppty,
495 0,
496 0,
497 0,
498 0
499FROM
500 total t
501 INNER JOIN acct a ON t.hacct = a.hmy
502 INNER JOIN @tblProps p ON t.hppty = p.hmy
503 INNER JOIN @tblmonths m ON m.dtmonth >= CASE WHEN p.dtmin < t.umonth THEN t.umonth ELSE p.dtmin END
504 AND m.dtMonth <= p.dtmax
505 LEFT JOIN total t2 ON t.hppty = t2.hppty AND t.hacct = t2.hacct AND t.ibook = t2.ibook AND m.dtmonth = t2.umonth
506WHERE
507 t.umonth >= @dtFrom
508 AND t2.umonth IS NULL
509GROUP BY
510 t.hppty,
511 t.hacct,
512 t.ibook,
513 p.dtmin,
514 a.irpttype,
515 p.dtMax,
516 m.dtMonth,
517 t2.umonth
518PRINT 'Created Necessary Rows' + CONVERT(VARCHAR(50),GETDATE(),14)
519
520
521/* Retained Earnings By Book Month Property */
522UPDATE total SET smtd = ISNULL(re.RetErn,0)
523FROM
524 total t
525 INNER JOIN @tblProps p ON t.hppty = p.hmy
526 LEFT JOIN
527 (SELECT
528 t.umonth,
529 t.ibook,
530 t.hppty,
531 SUM(CASE WHEN a.iaccttype = 1 THEN ISNULL(-t.smtd,0) ELSE ISNULL(t.smtd,0) END) RetErn
532 FROM
533 total t
534 INNER JOIN total re ON t.umonth = re.umonth AND t.ibook = re.ibook AND t.hppty = re.hppty AND re.hacct = @hretain
535 INNER JOIN acct a ON t.hacct = a.hmy
536 INNER JOIN @tblprops p ON t.hppty = p.hmy
537 WHERE
538 a.irpttype = 0
539 AND t.umonth >= @dtFrom
540 GROUP BY
541 t.umonth,
542 t.ibook,
543 t.hppty) re ON t.umonth = re.umonth AND t.ibook = re.ibook AND t.hppty = re.hppty
544WHERE
545 t.umonth >= @dtFrom
546 AND t.hacct = @hretain
547PRINT 'Retained Earnings: ' + CONVERT(VARCHAR(50),GETDATE(),14)
548
549
550/* Beginning Balances for Balance Sheet Accounts, starting with the beginning balance of the start month */
551UPDATE total SET sbegin = bb.bal
552FROM
553 total
554 INNER JOIN
555 (SELECT
556 t.umonth,
557 t.ibook,
558 t.hppty,
559 t.hacct,
560 ISNULL(ob.sbegin,0) + SUM(ot.smtd) bal
561 FROM
562 total t (NOLOCK)
563 INNER JOIN total ot (NOLOCK) ON t.ibook = ot.ibook AND t.hppty = ot.hppty AND t.hacct = ot.hacct
564 LEFT JOIN total ob (NOLOCK) ON t.ibook = ob.ibook AND t.hppty = ob.hppty AND t.hacct = ob.hacct AND ob.umonth = @dtfrom
565 INNER JOIN acct a ON t.hacct = a.hmy
566 INNER JOIN @tblProps p ON t.hppty = p.hmy
567 WHERE
568 a.irpttype = 1
569 AND ot.umonth < t.umonth AND ot.umonth >= @dtFrom
570 AND t.umonth >= @dtFrom AND t.umonth <= @dtTo
571 GROUP BY
572 t.umonth,
573 ob.sbegin,
574 t.ibook,
575 t.hppty,
576 t.hacct) bb ON total.ibook = bb.ibook AND total.hppty = bb.hppty AND total.hacct = bb.hacct AND total.umonth = bb.umonth
577 INNER JOIN @tblProps p ON total.hppty = p.hmy
578 WHERE
579 total.umonth >= @dtFrom AND total.umonth <= @dtTo
580PRINT 'Beginning Balances Balance Sheet: ' + CONVERT(VARCHAR(50),GETDATE(),14)
581
582
583/* Beginning Balances for Income Sheet Accounts */
584UPDATE total SET sbegin = ISNULL(bb.sbegin,0)
585FROM
586 total
587 INNER JOIN
588 (SELECT
589 t.umonth,
590 t.ibook,
591 t.hppty,
592 t.hacct,
593 ISNULL(SUM(tm.smtd),0) sbegin
594 FROM
595 total t (NOLOCK)
596 INNER JOIN @tblProps p ON t.hppty = p.hmy
597 INNER JOIN total tm (NOLOCK) ON t.ibook = tm.ibook AND t.hppty = tm.hppty AND t.hacct = tm.hacct AND tm.umonth < t.umonth
598 AND tm.umonth >= DATEADD(m,p.iendofyear,DATEADD(yyyy,DATEDIFF(yyyy,0,DATEADD(m,-p.iendofyear,t.umonth)),0))
599 INNER JOIN acct a ON t.hacct = a.hmy
600 WHERE
601 a.irpttype = 0
602 AND t.umonth >= @dtFrom AND t.umonth <= @dtTo
603 GROUP BY
604 t.umonth,
605 t.ibook,
606 t.hppty,
607 t.hacct) bb ON total.ibook = bb.ibook AND total.hppty = bb.hppty AND total.hacct = bb.hacct AND total.umonth = bb.umonth
608 WHERE
609 total.umonth >= @dtFrom AND total.umonth <= @dtTo
610
611PRINT 'Beginning Balances Income Sheet: ' + CONVERT(VARCHAR(50),GETDATE(),14)
612
613TRUNCATE TABLE zTEMP_GL
614TRUNCATE TABLE zTEMP_TOTAL
615
616PRINT 'Done Cleaning Up' + CONVERT(VARCHAR(50),GETDATE(),14)
617
618SET TRANSACTION ISOLATION LEVEL READ COMMITTED
619GO