Improve TKs process for Finance team

[atdservicebot]

Name

Dan V.

What application are you using?

Other / Not Sure

Describe the problem.

TKs are currently being tracked in an excel workbook that resides on the shared drive. the workbook lacks real time information and is difficult to access and requires significant upkeep. This process makes TK information inaccessible to the majority of staff. TKs are very specific numbers and PMs and employees who use them on time sheets should have a resource where they can validate the TK number they need to use. Additionally, time keepers need to be able to look up and validate TKs being used on staff time sheets. PMs also need to be able to track balances and manage the TKs for their capital projects.

Describe the solution you have in mind.

Currently TKs are being tracked through an excel based solution saved on the shared drive (G:). Finance is seeking a web-based solution where TKs can be tracked by TK number and name, show real-time balances, the responsible PM name, division name, and last use (if possible). This would need to be able to have updated data from a source such as mstr or AIMs that could show the updated balance if modifications are made as well as use.

How will we know that our solution is successful?

Success would look like a web-based solution that allowed users to search for and see all TKs used by ATD, the name, number, balance, responsible PM or manager, division name, last use. It would also be beneficial to be able to drill down into charges against a particular TK but not a must as eCAPRIS does provide that information if needed.

Users

ATD Finance, Project managers (PMs), timekeepers, DMs

Sponsorship

Anthony S.

Which Strategic Direction 2023 “Mobility” indicators would this new application impact?

['None']

Which Austin Strategic Mobility Plan goals would this new application support?

['None']

Describe any workarounds you currently have in place or alternative solutions you've considered.

We have a weekly email that goes out to PMs to avoid negative balances and use excel to track all TKs

How have other divisions/departments/cities addressed similar challenges?

Currently all solutions are based in excel to my knowledge. We regularly have TKs that cause payroll errors due to balances going negative. We also have TKs used that have been closed on timesheets causing payroll rejects.

Requested By Dan V.

Request ID: DTS22-105010

[dianamartin]

Scheduled a scoping meeting on 8/31

• Potential Integration requirement with eCapris

[dianamartin]

9/2

Current Process

Tracking TKs

• Excel spreadsheet - saved on the G drive (network server)
• Repository as task order information - anyone can look it up
• TK number and see what funding codes are used
• To know who set up and the Project Number
• Want more a self-serve process
• Timekeepers and Managers ask about tk for time sheets
• search for a sub project and project names
• See current balance

How is the information being populated?

New TK Find appropriate section 2400 - Dept 3 digit - Division Name (Bond group) 4 digit - Key: Memo/Billable

Integration

• Information exists in AIMS
• Manual process where Finance team is creating in "shared space"
• Would like an integration from AIMS to knack
• Data Warehouse: MicroStrategies --> Knack?

Access

• Time keepers (might need access)
• Managers

Service Request --> Task Orders

[dianamartin]

9/27 Meeting Notes

Question: What fields do you need for the Finance and Purchasing Finance needs the field "Requester" Task Order Estimator - Requester Name

• Division Name? (Cross reference Human Resources list)
  • Filter option: TASK_ORDER contains (division abbr)

They are requesting 2 fields:

• Requester Name
• Division Name

[patrickm02L]

Work with Devs to make sure the integration is in Place. @patrickm02L bring up in Sprint Planning 11/16/22.

[amenity]

@chiaberry's related PR here.

[Charlie-Henry]

Do we have a Knack App/dataset built for this? @dianamartin Looks like it was a TODO but I can't find if this was completed or not.

[dianamartin]

@Charlie-Henry I did import TKs information into the Finance and Purchasing application.

• Using financial_task_orders_TK - Finance and Purchasing
  • 10200 records
• from the data tracker object table financial_task_orders - AMD Data Tracker
• 10320 records

It looks like the finance data is already out of date as of 11/30/22

[Charlie-Henry]

The only difference between this table and the new one will be TASK_ORDER_ESTIMATOR right? @dianamartin

[dianamartin]

@Charlie-Henry the "TASK_ORDER_ESTIMATOR" is the newest field on the SQL table view. Does it show up on the data tracker?

[Charlie-Henry]

No I don't see it in either. Are we adding it to data tracker as well? @dianamartin

[dianamartin]

@Charlie-Henry I think this is the issue #10199 with the fields we reviewed, also the list that the Finance team has seen as well.

[Charlie-Henry]

Looks like I was successful at updating the finanical_task_orders_TK object in the finance-purchasing Knack app. I just need to finalize the ETL script 👍

[patrickm02L]

Next step is to update the ETL. See Issue #10960