-
Notifications
You must be signed in to change notification settings - Fork 0
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Feature request; programming change: Allow manual changed for OrgR BGEN #182
Comments
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 AllExpenses |
Shannon identified the following accounts used in the query below: Using data returned from the above query, we identified the Orgs listed in the following query were involved: |
Question: Where in the process are the expenses belonging to BGEN mapped over to BMCB? |
These views also use the ExpenseOrgR_X_AD419OrgR table. 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.
|
-- 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 /* |
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. Question: Does labor transactions use OrgR remapping? *Expense OrgR Mappings: This table drives the OrgR remapping. Reporting Organizations: Org R present, but this data comes from the Reporting Org table. Review Tab: Expenses by ARC generation criteria: N/A |
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:
|
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
|
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. |
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 text was updated successfully, but these errors were encountered: