Add something that help ID any Org R that is not in reporting Orgs
Closed this issue · 8 comments
We need a way to determine if there are any new Org Rs present in the expenses.
This is a 2-fold process.
After loading expenses, we need to check if there are any with Org R that do not have a valid reporting Org R for, and either do 1 of the following:
- Add the Org R to Reporting Orgs or
- Add and entry to Expense Org R Mappings.
USE [AD419]
GO
-- This will list any Expenses that have a reporting Org outside of the ones we know about.
-- It can be used to ID any OrgRs we either need to add to the Reporting Orgs table or add to the remapped Expense OrgRs to AD-419 Departments in the ExpenseOrgR_X_AD419OrgR table.
DECLARE @FiscalYear int = 2015
SELECT t2.OrgR, t1.*
FROM
(
SELECT DISTINCT t2.Org, t1.* FROM [dbo].udf_GetDirectAndIndirectFFYExpensesByARCandAccount t1
INNER JOIN FISDataMart.dbo.Accounts t2 ON t1.Chart = t2.Chart AND t1.Account = t2.Account AND t2.Year = 9999 AND t2.Period = '--'
UNION
SELECT DISTINCT t3.Org, t1.* FROM [dbo].udf_GetDirectAndIndirectFFYExpensesByARCandAccount t1
LEFT JOIN FISDataMart.dbo.Accounts t2 ON t1.Chart = t2.Chart AND t1.Account = t2.Account AND t2.Year = 9999 AND t2.Period = '--'
LEFT JOIN FISDataMart.dbo.Accounts t3 ON t1.Chart = t3.Chart AND t1.Account = t3.Account AND ((t3.Year = @FiscalYear AND t3.Period BETWEEN '04' AND '13') OR
(t3.Year = @FiscalYear + 1 AND t3.Period BETWEEN '01' AND '03'))
WHERE t2.Org IS NULL ) t1 LEFT OUTER JOIN
[AD419].[dbo].[UFYOrganizationsOrgR_v] t2 ON t1.Chart = t2.Chart AND t1.Org = t2.Org AND
(EndDate IS NULL OR EndDate > CONVERT(varchar(4),@FiscalYear) + '-10-01 00:00:00.000')
WHERE t2.OrgR NOT IN (SELECT OrgR FROM ReportingOrg WHERE IsActive = 1)
This step can be run after executing usp_BeginProcessingForNewReportingPeriod, by verifying that all the OrgRs in the UFY_FFY_FIS_Expenses table have an Active orgR that is in Reporting Orgs, I believe. I will need to check this against the query I test above.
Make sure to check that UFY_FFY_FIS_Expenses contains all of the data and just not the non-salary expenses. If not we'll have to add OrgR to the FFY_ExpensesByARC table or something similar in order to figure this out.
Done.
I ended up creating a user-defined function that takes utilizes the data present in the existing UFY_FFY_FIS_Expenses table and looks for OrgRs that are either null or not currently in the ReportingOrgs table.
I then added a new list of expenses with unknown departments or a message stating that there we none, to the top of the Reporting Organizations index page. This should accomplish the intended purpose.
I tested it by changing a few expense entries' OrgR to either NULL or an OrgR not currently in our list using the following SQL:
-- Unknown AD-419 OrgR test:
update [AD419].[dbo].[UFY_FFY_FIS_Expenses]
set OrgR = 'BPPA' where ID between 1 and 3
-- Restore to the original values:
update [AD419].[dbo].[UFY_FFY_FIS_Expenses]
set OrgR = 'APPA' where ID between 1 and 3
Merged.
F.Y.I.: We didn't actually need to load the expenses table. All the data we need to determine this was present in the UFY_FFY_FIS_Expenses table.