Research change in totals of FFY ARC with Accounts report between yesterday and today
Closed this issue · 8 comments
Yesterday the ARC with accounts reports showed a grand total of $196,795,938.09. Today the total is $196,045,067.67. The data is not supposed to change once the September fiscal period has closed, which should have been no later than the middle of October. However, now there is a difference of $750,870.42 between the two, and today's amount is $750K less. This should not be possible.
Guesses as to why:
- Our period date selection logic is off by one month, it's using October 31 as the fiscal close date, and October just closed yesterday and we're seeing results for that data.
- FIS goofed up and somehow postdated transactions for the September fiscal period after it had closed.
- September pending transactions posted that were not present yesterday.
- Some combination of the above.
- Something else.
Research plan:
- Capture data displayed in report to temporary table.
- Compare AD-419 expenses by account against above captured data.
- Identify any differences.
- Run DaFIS reports for any accounts and compare against the two.
If the results are due to a FIS issue, then we can simply re-run the data import and transfer to the AD-419 tables. If not, then further investigation will still be necessary.
The data pulled from the report previously is stored in the FFY_ExpensesByARC table.
I am going to pull fresh data into a new table FFY_ExpensesByARC_20161108.
It took 00:01:18 13,242 rows.
I will now check for any difference between the 2 tables:
select * from FFY_ExpensesByARC_20161108
EXCEPT
select * from FFY_ExpensesByARC
Zero rows, meaning the previous data contains every row in the new data.
select * from FFY_ExpensesByARC
EXCEPT
select * from FFY_ExpensesByARC_20161108
13 rows: Meaning there were 13 additional records in the previous data that are not present in the data today:
AnnualReportCode Chart Account ConsolidationCode DirectTotal IndirectTotal Total
440205 3 KMKASD1 INDR 0.00 18794.34 18794.34
440205 3 KMKASD1 SB28 1471.82 0.00 1471.82
440251 3 GLC2299 INDR 0.00 14200.39 14200.39
440205 3 KMKASD1 SUBG 10886.04 0.00 10886.04
440251 3 GLC2299 SB28 -805.36 0.00 -805.36
440251 3 GLC2299 SUBG 39170.88 0.00 39170.88
440205 3 KMKASD1 SB01 4793.30 0.00 4793.30
440251 3 GLC2299 SUB5 1009.79 0.00 1009.79
440205 3 KMKASD1 SUB6 24058.31 0.00 24058.31
440205 3 KMKASD1 SB06 3824.10 0.00 3824.10
440211 L W106EVN SUB5 1238.59 0.00 1238.59
440251 3 GLC2299 SUB6 10971.69 0.00 10971.69
440205 3 KMKASD1 SB07 21601.98 0.00 21601.98
Total: $151,215.87; however, this total does not equal the $750,870.42 difference between yesterday and today.
These are the 3 accounts that are no longer present in the dataset:
AnnualReportCode Chart Account
440205 3 KMKASD1
440251 3 GLC2299
440211 L W106EVN
That is because the above account are now present in the ARCCodeAccountExclusions table, and they were not when we originally ran the report.
Meaning the data pulled for the report appears to be OK.
Both totals appear to indicate the amount, 196,045,067.67, pulled from today's totals:
select sum(total) total from FFY_ExpensesByARC_20161108
/*
total
196,045,067.67
*/
select sum(total) total from FFY_ExpensesByARC
where chart+Account not in (SELECT chart+account from ArcCodeAccountExclusions where year = 2016)
/*
total
196,045,067.67
*/
Perhaps were looking at the 2015 report?
While researching the issue, I found that some of the field station expenses were not mapped across because accession numbers for expired projects were used instead of the ones for the non-expired projects with the same project numbers.
Is also needed to update records where there were 2 projects and 1 of the 2 had not started yet, but the expenses were assigned to the yet-to-be-started one instead of the current project:
I used the following SQL to update the records with the current ones:
update [FieldStationExpenseListImport]
set [ProjectAccessionNum] = t2.AccessionNumber
FROM [FieldStationExpenseListImport] t1
INNER JOIN (
SELECT [ProjectAccessionNum]
,t3.AccessionNumber, t1.[Id]
FROM [AD419].[dbo].[FieldStationExpenseListImport] t1
LEFT OUTER JOIN AllProjectsNew t2 ON t1.[ProjectAccessionNum] = t2.AccessionNumber
LEFT OUTER JOIN AllProjectsNew t3 ON t2.ProjectNumber = t3.ProjectNumber AND t1.[ProjectAccessionNum] not like t3.AccessionNumber AND (
(t2.IsExpired = 1) OR
(t2.IsExpired = 0 AND t2.ProjectStartDate >= '2016-10-01'))
WHERE t3.AccessionNumber IS NOT NULL
) t2 ON t1.Id = t2.Id
Use this check to make sure the Project is not null for all the records:
select t1.ProjectAccessionNum, t1.FieldStationCharge, t3.Project, IsExpired from FieldStationExpenseListImport t1
left outer join AllProjectsNew t2 ON ProjectAccessionNum = AccessionNumber
left outer join Project t3 ON ProjectAccessionNum = Accession
The list should be this:
ProjectAccessionNum FieldStationCharge Project IsExpired
1001481 20000.72 CA-D-PLS-6599-H 0
1005545 492724.43 CA-D-ASC-6578-H 0
1002108 2406.10 CA-D-PLS-7869-H 0
0224328 38572.81 CA-D-PLS-2125-CG 0
1001174 152486.67 CA-D-PLS-6470-H 0
1004482 20376.67 CA-D-PLS-7663-RR 0
1008037 26015.97 CA-D-PLS-2324-OG 0
1008037 7744.64 CA-D-PLS-2324-OG 0
1008044 22782.77 CA-D-PLS-2246-RR 0
1008044 37069.00 CA-D-PLS-2246-RR 0
0231453 9474.02 CA-D-LAW-2178-H 0
1005544 46618.21 CA-D-PPA-2025-H 0
1005544 1729.39 CA-D-PPA-2025-H 0
1005544 13722.30 CA-D-PPA-2025-H 0
1005544 3458.77 CA-D-PPA-2025-H 0
1005544 11504.17 CA-D-PPA-2025-H 0
1005544 5977.66 CA-D-PPA-2025-H 0
1004981 29737.91 CA-D-PLS-7259-RR 0
1004981 34136.56 CA-D-PLS-7259-RR 0
1009853 4962.58 CA-D-PLS-2343-RR 0
1009853 4962.58 CA-D-PLS-2343-RR 0
1004948 1804.58 CA-D-PLS-7311-H 0
1004948 7594.26 CA-D-PLS-7311-H 0
1004948 28497.26 CA-D-PLS-7311-H 0
1004948 16278.78 CA-D-PLS-7311-H 0
1004948 20075.91 CA-D-PLS-7311-H 0
1004948 15564.47 CA-D-PLS-7311-H 0
0233102 24061.01 CA-D-PLS-2173-H 0
1002305 24098.61 CA-D-PPA-6456-H 0
1002305 32971.11 CA-D-PPA-6456-H 0
1002305 61844.32 CA-D-PPA-6456-H 0
1001179 50941.68 CA-D-VIT-4345-H 0
1004338 17669.81 CA-D-PLS-2273-OG 0
0226680 3684.34 CA-D-ENM-2141-H 0
0226680 23233.92 CA-D-ENM-2141-H 0
0227719 96770.39 CA-D-XXX-2145-H 0
0227719 375.95 CA-D-XXX-2145-H 0
0227719 67333.24 CA-D-XXX-2145-H 0
0210323 1353.43 CA-D-PLS-7641-H 0
0210323 601.53 CA-D-PLS-7641-H 0
0213641 89777.65 CA-D-PLS-7749-RR 0
0213641 61393.18 CA-D-PLS-7749-RR 0
0213641 5564.11 CA-D-PLS-7749-RR 0
0175703 77371.20 CA-D-PLS-6278-H 0
0177959 16692.33 CA-D-XXX-6423-H 0
0177959 6391.21 CA-D-XXX-6423-H 0
0177959 9624.41 CA-D-XXX-6423-H 0
I made a copy of the data, and then restored it in order to verify the update worked as desired.
truncate table FieldStationExpenseListImport
SET IDENTITY_INSERT dbo.FieldStationExpenseListImport ON
INSERT INTO dbo.FieldStationExpenseListImport ([ProjectAccessionNum]
,[FieldStationCharge]
,[ProjectDirector]
,[Id])
SELECT * FROM dbo.FieldStationExpenseListImport_copy
SET IDENTITY_INSERT dbo.FieldStationExpenseListImport OFF
This should ultimately be the total displayed on line 22F (field station expenses): TotalFieldStationExpensesImport
1,748,032.62.
The import process will need to be run again now that all the accession numbers correspond to current projects.
I was unable to duplicate the issue. The data I pulled previously, matched the data I pulled today, and the only difference were the expenses that were added to the ARCCodeAccountExclusions table, and this is to be expected. Otherwise, the values were identical.