Feature request; programming change: Allow manual changed for OrgR BGEN
taylorkj opened this issue · 14 comments
Currently all BGEN orgs are mapped over to BMCB, which is apparently only correct for orgs belonging to a single P.I.: Richard Michelmore. All others should be remapped over to ADNO instead or allow manual OrgR selection by Org via a new interface. We would then need to incorporate this new mapping, and interface into the AD-419 Data Helper application so that the new OrgR assignments would take affect when assigning OrgRs to expenses. Note that the UI would probably update the OrgXOrgR table, and then the OrgR assignments be driven off of that.
The "trick" for this is that we'd somehow need to identify the Orgs involved. Perhaps we do this by simply finding all accounts for orgs that map up to BGEN? Shannon and I are to discuss this after this year's reports have been completed.
In the mean time, I did the following as per Shannon:
update AllOrgXOrgR
set OrgR = 'ADNO' WHERE Org IN ( 'BRAD', 'COMA' , 'DENN' ,'FIEH') --takes care of orgR reassignment in terms of org X orgR mapping.
update AllExpenses
SET OrgR = 'ADNO' WHERE Org IN ( 'BRAD', 'COMA' , 'DENN' ,'FIEH') AND OrgR = 'BMCB' -- takes care of OrgR reassignment in expenses,
Shannon identified the following accounts used in the query below:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT [ExpenseID]
,[DataSource]
,[OrgR]
,[Chart]
,[Account]
,[SubAcct]
,[PI_Name]
,[Org]
,[EID]
,[Employee_Name]
,[TitleCd]
,[Title_Code_Name]
,[Exp_SFN]
,[Expenses]
,[FTE_SFN]
,[FTE]
,[isAssociated]
,[isAssociable]
,[isNonEmpExp]
,[Sub_Exp_SFN]
,[Staff_Grp_Cd]
FROM [AD419].[dbo].[AllExpenses]
WHERE Account IN ('AGRAWAL', 'LCPOTAT','LCPOTPC', 'MDPOPFD','SB059PC')
Using data returned from the above query, we identified the Orgs listed in the following query were involved:
SELECT * FROM OrgXOrgR WHERE Org IN ( 'BRAD', 'COMA' , 'DENN' ,'FIEH')
Question: Where in the process are the expenses belonging to BGEN mapped over to BMCB?
Answer: This happens as a result of the OrgR Expepense Department Remapping where BGEN is the source Expense OrgR and BMCB is the target AD-419 OrgR.
Removing this entry will result in all of the BGEN expenses to maintain BGEN as their OrgR, and they most likely be hidden from any of the departments views as BGEN is not a associating department. Therefore, we will need to manually update all of these expenses' OrgR to either ADNO, BMCB (if appropriate) or some other legitimate reporting department.
- At what stage of the process does the remapping actually take place?
a. usp_Load_UFY_FFY_FIS_Expenses does an update of OrgR;
b. UFY_FFY_FIS_Expenses is basically the data source for all of the FIS expenses, plus is used in conjunction with AnotherLaborTransactions for the salary data as well as it does a join on the OrgXOrgR.view, which includes the remapped OrgRs.
These views also use the ExpenseOrgR_X_AD419OrgR table.
UFYOrganizationsOrgR_v.sql
Expenses.view.sql
OrgXOrgR.view.sql
Basically the remapped OrgRs are entrenched just about everywhere.
Therefore, the best approach would probably be to remove the BGEN -> BMCB entry from the ExpenseOrgR_X_AD419OrgR table, and handle BGEN remapping after that.
- How do we identify which accounts have a reporting OrgR of BGEN?
-- These are the results we get when we just look for remapped OrgRs that would be returned from UFYOrganizationsOrgR_v. It is used as a Left Outer Join when attempting to populate the OrgR based on the department within the usp_Load_UFY_FFY_FIS_Expenses stored procedure.
Question: Perhaps we should leave the original OrgR or include the original OrgR in addition?
SELECT t1.Chart, t1.Org, t1.OrgR OriginalOrgR, COALESCE (t2.AD419OrgR, t1.OrgR) AS OrgR, t1.BeginDate, t1.EndDate
FROM (SELECT Chart, Org, CASE WHEN (TYPE IN ('G', 'N')) THEN NULL WHEN (Org4 = 'BIOS' AND Org5 <> 'CBSD') THEN Chart5 ELSE Chart6 END AS ChartR,
CASE WHEN (TYPE IN ('G', 'N')) THEN NULL WHEN (Org4 = 'BIOS' AND Org5 <> 'CBSD') THEN Org5 ELSE Org6 END AS OrgR, CASE WHEN (TYPE IN ('G',
'N')) THEN NULL WHEN (Org4 = 'BIOS' AND Org5 <> 'CBSD') THEN Name5 ELSE Name6 END AS NameR, BeginDate, EndDate
FROM FISDataMart.dbo.Organizations
WHERE (Year = '9999') AND (Period = '--') AND (Type NOT IN ('G', 'N', 'S'))) AS t1 LEFT OUTER JOIN
dbo.ExpenseOrgR_X_AD419OrgR AS t2 ON t1.OrgR = t2.ExpenseOrgR AND t1.Chart = t2.Chart
WHERE t2.AD419OrgR IS NOT NULL
/*
Chart Org OriginalOrgR OrgR BeginDate EndDate
3 AEDS AEDS AHCE 2006-08-22 00:00:00 NULL
3 AEVE AEVE BEVE 2010-04-07 00:00:00 NULL
3 AFDS AFDS ADNO 1997-04-05 00:00:00 NULL
3 AROW AFDS ADNO 2015-06-10 00:00:00 NULL
3 ENDD AFDS ADNO 2015-07-22 00:00:00 NULL
3 GONE AFDS ADNO 2009-04-09 00:00:00 NULL
3 MAHR AFDS ADNO 2016-07-26 00:00:00 NULL
3 AHIS AHIS ADNO 1997-04-05 00:00:00 2014-11-05 00:00:00.000
3 AMCB AMCB BMCB 2010-04-14 00:00:00 NULL
3 AMIC AMIC BMIC 2010-04-14 00:00:00 NULL
3 ANPB ANPB BNPB 2010-04-14 00:00:00 NULL
3 AGRE APLB BPLB 2015-09-03 00:00:00 NULL
3 APLB APLB BPLB 2010-04-14 00:00:00 NULL
3 APRV APRV ADNO 1997-04-05 00:00:00 NULL
3 BCPB BCPB BEVE 1997-04-08 00:00:00 NULL
3 BCPO BCPB BEVE 2007-01-31 00:00:00 NULL
3 BCPX BCPB BEVE 2007-01-31 00:00:00 NULL
3 BENH BGEN BMCB 2007-11-19 00:00:00 NULL
3 BGEN BGEN BMCB 2007-05-09 00:00:00 NULL
3 BINF BGEN BMCB 2009-10-13 00:00:00 NULL
3 ASDT BGEN BMCB 2015-10-12 00:00:00 NULL
3 BRAD BGEN BMCB 2008-09-18 00:00:00 NULL
3 CARV BGEN BMCB 2012-06-18 00:00:00 NULL
3 COMA BGEN BMCB 2007-11-19 00:00:00 NULL
3 DENN BGEN BMCB 2015-04-02 00:00:00 NULL
3 AAAL BGEN BMCB 2005-07-06 00:00:00 NULL
3 AAAS BGEN BMCB 2005-07-06 00:00:00 NULL
3 FARN BGEN BMCB 2007-11-19 00:00:00 NULL
3 FOCI BGEN BMCB 2012-10-31 00:00:00 NULL
3 FIEH BGEN BMCB 2007-11-19 00:00:00 NULL
3 GCMC BGEN BMCB 2006-06-01 00:00:00 NULL
3 GCME BGEN BMCB 2014-05-28 00:00:00 NULL
3 GCMI BGEN BMCB 2014-05-23 00:00:00 NULL
3 GCDC BGEN BMCB 2006-06-01 00:00:00 NULL
3 GCDI BGEN BMCB 2014-05-23 00:00:00 NULL
3 GCDN BGEN BMCB 2014-05-23 00:00:00 NULL
3 GCDU BGEN BMCB 2014-05-23 00:00:00 NULL
3 GCEC BGEN BMCB 2006-06-01 00:00:00 NULL
3 GCBC BGEN BMCB 2006-06-01 00:00:00 NULL
3 GCBI BGEN BMCB 2014-05-23 00:00:00 NULL
3 GCBN BGEN BMCB 2014-05-23 00:00:00 NULL
3 GCBU BGEN BMCB 2014-05-23 00:00:00 NULL
3 GCCF BGEN BMCB 2004-11-01 00:00:00 NULL
3 DUAN BGEN BMCB 2007-11-19 00:00:00 NULL
3 EISE BGEN BMCB 2007-11-19 00:00:00 NULL
3 EXPR BGEN BMCB 2009-10-12 00:00:00 NULL
3 FAIN BGEN BMCB 2003-08-01 00:00:00 NULL
3 FACC BGEN BMCB 2007-11-19 00:00:00 NULL
3 LEMA BGEN BMCB 2013-07-29 00:00:00 NULL
3 LEAR BGEN BMCB 2007-11-19 00:00:00 NULL
3 LUDA BGEN BMCB 2007-11-19 00:00:00 NULL
3 KFID BGEN BMCB 2007-11-19 00:00:00 NULL
3 KUMA BGEN BMCB 2009-11-18 00:00:00 NULL
3 KOEH BGEN BMCB 2007-11-19 00:00:00 NULL
3 KORF BGEN BMCB 2007-11-19 00:00:00 NULL
3 JNLS BGEN BMCB 2015-10-13 00:00:00 NULL
3 IGEM BGEN BMCB 2013-07-30 00:00:00 NULL
3 HLEW BGEN BMCB 2011-05-13 00:00:00 NULL
3 HORM BGEN BMCB 2015-06-02 00:00:00 NULL
3 MASS BGEN BMCB 2005-07-06 00:00:00 NULL
3 MICH BGEN BMCB 2007-11-19 00:00:00 NULL
3 MSF1 BGEN BMCB 2006-08-14 00:00:00 NULL
3 MSFC BGEN BMCB 2005-07-05 00:00:00 NULL
3 GENO BGEN BMCB 2009-10-12 00:00:00 NULL
3 GCOP BGEN BMCB 2009-10-12 00:00:00 NULL
3 GCPC BGEN BMCB 2006-06-01 00:00:00 NULL
3 GCPI BGEN BMCB 2014-05-23 00:00:00 NULL
3 GCPN BGEN BMCB 2014-05-23 00:00:00 NULL
3 GCPU BGEN BMCB 2014-05-23 00:00:00 NULL
3 GCMU BGEN BMCB 2014-05-23 00:00:00 NULL
3 GBSF BGEN BMCB 2009-10-12 00:00:00 NULL
3 GCTC BGEN BMCB 2006-08-08 00:00:00 NULL
3 GCTI BGEN BMCB 2014-05-23 00:00:00 NULL
3 GCTN BGEN BMCB 2014-05-23 00:00:00 NULL
3 GCTU BGEN BMCB 2014-05-23 00:00:00 NULL
3 WRIG BGEN BMCB 2007-11-19 00:00:00 NULL
3 RANN BGEN BMCB 2007-11-19 00:00:00 NULL
3 QUON BGEN BMCB 2015-10-30 00:00:00 NULL
3 POLL BGEN BMCB 2007-11-19 00:00:00 NULL
3 PRSL BGEN BMCB 2005-07-06 00:00:00 NULL
3 SEGA BGEN BMCB 2007-11-19 00:00:00 NULL
3 SEQA BGEN BMCB 2005-07-06 00:00:00 NULL
3 SETT BGEN BMCB 2015-04-20 00:00:00 NULL
3 SIEG BGEN BMCB 2012-06-18 00:00:00 NULL
3 TAGK BGEN BMCB 2009-10-12 00:00:00 NULL
L APRV APRV ADNO 1997-04-05 00:00:00 NULL
L XPRV APRV ADNO 2014-06-26 00:00:00 NULL
*/
Question: Where do actually start using the remapped OrgR in the DataHelper (or reports), which has been populated in the UFY_FFY_FIS_Expenses table? Or is it just used behind the scenes to populate the other tables?
FTE > 1 report: No OrgR included on report.
Select from SFN 204 Accounts Project Matches: No OrgR included on report.
SFN 204 Accounts with Missing Projects: No OrgR included on report.
Direct and Indirect Expenses by ARC with Account reports: No OrgR included on report.
Current AD-419 Projects: OrgR included, but probably driven directly from the projects table.
Expenses by SFN Sub-Report: Supply the accession number and OrgR. Probably uses the expenses OrgR at this point.
No longer used AD-419 Application reports:
Project AD419: Old report used by AD-419 application, but does not include prorated expenses, I think, and is therefore, not very useful. Supply OrgR and associated/unassociated code. Probably uses expenses OrgR.
Department AD-419: Old AD-419 application report. Supply expenses OrgR, [accession], [intAssociationStatus]:
Department Info: Uses old CRIS Dept Cd instead of OrgR, i.e. 0331. Probably not driven off of the expenses table.
Web Pages:
Maintenance Tab:
Annual Report Codes: No OrgR.
ARC Code/Account Exclusions: No OrgR
Project Maintenance: OrgR, but probably from projects table itself.
Non-204 Expiring Project Cross Reference: OrgR, but probably from projects table itself.
204-20x Project Mappings: No OrgR
DOS Codes: OrgR present if unknown labor transactions exist.
Trans Doc Types: OrgR present if unknown labor transactions exist.
Object Consolidation Codes: OrgR present if unknown labor transactions exist.
Question: Does labor transactions use OrgR remapping?
Answer: No. Labor transactions does not contain a field for OrgR. There is only one for Org, which is populated directly from DaFIS.
*Expense OrgR Mappings: This table drives the OrgR remapping.
Reporting Organizations: Org R present, but this data comes from the Reporting Org table.
Principal Investigator to Employee Matching: No OrgR
Project Status: N/A
Title Code/Staff Types: OrgR present if unknown labor transactions exist.
Review Tab:
Expenses by ARC generation criteria: N/A
FFY Expenses by ARC: No OrgR.
SFY Expenses by ARC: No OrgR.
Current AD-419 Projects: OrgR, but probably from projects table.
*204 Project Associations: Uses 204AcctXProj table, but probably used remapping at some point to populate the OrgR column.
ARC/Account Exclusions: No OrgR.
FTE Calculation Criteria: No OrgR.
FTE > 1 Report: No OrgR
SFN Case Statement Criteria: N/A
Generated CASE Statement: N/A
FFY Vs Adjusted AD-419 Total Expenses Comparison: N/A
AD-419 Final Reports: No OrgR present; only 3-character department, i.e. ASC, ARE, etc.
I am considering removing the OrgR column from all of tables or at least not using it, and waiting until a later step. This would allow us to maintain the original OrgR in the source data for remapping later in the process without having to add an additional OrgR column.
Tentative steps:
- Rewrite usp_Load_UFY_FFY_FIS_Expenses to not do any OrgR remapping or to not use OrgR at all. This is still being analyzed at this time.
Note: The join is being done with UFYOrganizationsOrgR_v, which is actually where the remapping is done.
Removing the join to UFYOrganizationsOrgR_v from UFYOrganizationsOrgR_v is that path I chose as this point as it seemed the least problematic.
At this point the DataHelper Application has been modified to allow the setting of the Org in addition to the department for expense department remapping, plus a new account details section on the same page. This will allow us to include Orgs as well as Departments should the need arise for other Department in additional to BGEN.
The next step is to review all of the stored procedures and ensure all of the department remapping is delayed until after the blank or missing expense departments are reviewed. Currently the last item of step 7: Review and Accept Reporting Orgs.
Stored procedure usp_Repopulate_OrgXOrgR will need to be updated to either use the join to the remap table or the view that includes the join to the remap table.
Testing of SQL to be used. Note that @org is only used for testing, and not in the SQL to be deployed.
DECLARE @orgr varchar(4) = 'ADNO'
SELECT DISTINCT
t1.Chart, t1.Org, COALESCE (t2.AD419OrgR, t1.OrgR) AS OrgR
FROM
[dbo].[UFYOrganizationsOrgR_v] AS t1 LEFT OUTER JOIN
[dbo].[ExpenseOrgR_X_AD419OrgR] AS t2 ON
t1.Chart = t2.Chart AND
t1.OrgR = t2.ExpenseOrgR AND
(
-- We want to make sure we include remapping for both those OrgRs where only the OrgR is used to
-- determine which department to map to, meaning All Orgs under a given department:
(t2.ExpenseOrg IS NULL) OR
-- And those OrgRs, i.e. BGEN, where both OrgR/Org are used to determine which Department to map to, meaning
-- BGEN/MICH: BMCB, BGEN/COMA: BLPB, BGEN/DENN: ADNO, etc.
(t1.Org = t2.ExpenseOrg)
)
WHERE COALESCE (t2.AD419OrgR, t1.OrgR) LIKE @OrgR
ORDER BY
t1.Chart, OrgR, t1.Org
Removed the remapping join from The OrgXOrgR view, as it no longer made sense to have it here since we previously loaded AllOrgXOrgR using it.
I can probably keep the Original Expense OrgR, and do the remapping directly in the Expenses view, which uses the OrgXOrgR table that already includes the remapped orgs.
After long thought and careful consideration, I have decided to take the following approach:
Load the AllOrgXOrgR table using [dbo].[UFYOrganizationsOrgR_v] as the datasource. Do not remap any organizations at this time. Instead, use the OrgXOrg (view) to do the remapping each time (yes, this is less efficient, but less prone to error should someone add a remapped department after the table has been loaded. This way we also preserve the original org data in the AllOrgXOrgR table and use a view to handle modifying the output data, which is the preferred approach. (you should never modify the source data from a data integrity standpoint).
All the other sprocs on the pre-AD-419 UI side will need to be modified to use the UFYOrganizationsOrgR_v as the data source for resolving Org to OrgR. This view does not do any remapping.
The AllExpenses table will also be loaded with the original OrgRs. OrgR remapping will occur using the Expenses view for the same reasons as stated for OrgXOrgR (above).
Now we have a game plan and can proceed forward.