[Spike] Inventory Funding Enhancements

[amenity]

Dedicated time to explore

• Can we allow inventory items to be manually JVed before being closed?
• Can we enable manual overrides of TK + FDU values for individual inventory item transactions on the same work order?

[ChristinaTremel]

Scheduled spike discussion for Diana and me on 4/19/2023.

[ChristinaTremel]

Related to #9808.

[ChristinaTremel]

4/26 Inventory Funding Enhancements Spike Brainstorm

Met with Diana and discussed 2 questions Ronnie wanted us to explore:

Can we allow inventory items to be manually JVed before being closed?

• Current JV Process:
  • Work Order Status must be CLOSED
  • Monthly, a JV REPORT is run by Crystal (checked by Ronnie beforehand)
    • JV Status is Ready at this time (ready for finance to process)
    • Transaction Status is Complete (inventory planners have completed the inventory request
    • Work Order Status is Closed (work order including inventory request has been closed)
  • Ronnie would like to remove the rule and have the ability to JV inventory when a work order is not in the Closed status because some items are large ticket and items and Finance would like to see the JV process happen more immediately after the item is pulled by inventory.
  • We could add the JV Status field to Inventory table and to the Work Orders
  • Need to confirm with Finance that they are okay with us JVing inventory when the work order is still open.

Can we enable manual overrides of TK + FDU values for individual inventory item transactions on the same work order?

• Ronnie creates TKs and Ivonne/her team assists.
• Talked to Ivonne and they are actually talking about updating their TK and FDU process anyways.
  • They currently get FDUs/TKs from multiple stakeholders and use different TKs for different inventory items on the same work order, but this is not an efficient way to do work.
  • They are working on streamlining the process by sending cost estimates to stakeholders and, if they want to do want to move forward with the project, either JVing or BGEX the funds to an FDU that they control.
  • Diana showed Ivonne a preview of the new process for Financial Service Requests and she thinks that and AMD's new process could solve the issue of adding FDUs for individual inventory items.
• We will be involving AMD in the Service Request testing in May and have a conversation if this solves AMDs problems

Next Steps

• [x] Schedule meeting with finance to make sure they are on the same page with JVing inventory items when a work order is not in the closed status - scheduled May 8th
• [x] Test the process for removing the rule that a Work Order is Closed to show up on the JV REPORT
• [x] Add JV Status field to all of the Work Orders Details pages
• [x] Add JV Status field to Inventory table
• [ ] In AMD Sprint Review, talk about the Finance Service Requests, new streamlined process AMD is creating to put money into a single FDU, and find out if that solves this problem "Can we enable manual overrides of TK + FDU values for individual inventory item transactions on the same work order?"

[ChristinaTremel]

5/8 Meting Notes

Attendees: Christina, Diana, Ronnie, Ivonne, Dan, Erin, Brian, Crystal, Melissa, Cesar

Finance:

• Would adjustments be considered? - Would the person determining if an item could be JVed be the same person that identifies if an item will be returned.
• Benefits would be that we have a better sense of how inventory is moving through AMD, so Dan would like this!
• One issue in the past, is that if a work order is not completed, it won't charge to a CIP project and it causes problem for Grant Delivery.

AMD:

• We would not JV from inventory items in the "Needs to be Issued"
• AMD would JV anything that was checked out, but when Ronnie looks at the FDU review, he opens the FDU and checks the balance. Tries to avoid creating a negative FDU balance.
  • Dan says that you shouldn't care if you drive an FDU negative and it should be the Project Managers responsibility

Next Steps

• DTS will test the process of removing the work order is CLOSED rule and have Ronnie review the JV report to ensure there are no red flags.
• AMD to discuss internally what a potential process could look like for charging individual inventory items to FDUs - DTS does think this will make the process more complicated

[ChristinaTremel]

5/10 Email from Ronnie

Since the meeting Monday, I spoke with Billy about the possibility of listing a task order and an FDU for each inventory transaction rather than listing a single task order and FDU for the overall work order. This would eliminate the need to create separate work orders when inventory items are used at a location or for a project, but the items are funded from different FDUs or go to different task orders.

We feel that having separate task order and FDU fields for each transaction rather than for the overall work order would be beneficial in some cases. However, doing so would make the FDU review process more difficult and would introduce potential sources for incorrect data entry that could result in charges to incorrect FDUs. We do not feel that the benefits of such a change would exceed the potential for problems. Therefore, we would like to discontinue consideration of listing a task order and an FDU for each inventory transaction rather than for the overall work order.

We do want to continue consideration for

1. the ability to process a transaction on a JV even if the work order has not been closed, and
2. a revision to address cases where one (or more) “needs to be issued” items on a work order allow that work order to bypass the FDU Review queue and be included on a JV.

[dianamartin]

5/15

Test the process for removing the rule that a Work Order is Closed to show up on the JV REPORT

• [ ] Add JV Status field to all of the Work Orders Details pages
  • [x] Add to "Work Order - Technicians"
• [x] Add JV Status field to Inventory table
  • [ ] Add to "Work Order - Technicians"
• [ ] Change Journal Voucher Status - Conditional Rule

  Current

  

After

INSERT SCREEN SHOT

• [ ] In AMD Sprint Review, talk about the Finance Service Requests, new streamlined process AMD is creating to put money into a single FDU, and find out if that solves this problem "Can we enable manual overrides of TK + FDU values for individual inventory item transactions on the same work order?

[ChristinaTremel]

@ChristinaTremel set up work session with Diana for late this week/early next

[ChristinaTremel]

Scheduled work session on 5/22 with Diana.

[dianamartin]

5/23

Test the process for removing the rule that a Work Order is Closed to show up on the JV REPORT

• [x] Add JV Status field to all of the Work Orders Details pages
• Add Journal Voucher Status, rename to JV Status
• Add Display Rule as background #f6fa6b, when JV status is not blank
  • [x] Add to "My Work Orders"
  • non-editable
  • editable table
  • [x] Add to "Work Order - Technicians"
  • non-editable
  • editable table
  • [x] Add to "Work Order - Non-Technicians"
  • editable table
  • [x] Add to "Work Order - Non-Technicians"
  • editable table
• [x] Add JV Status field to Inventory table
  • [X] Add to "Work Order - Technicians"

Change Journal Voucher Status - Conditional Rule

• On inventory_transactions, the "Journal Voucher Status"
• Need to update Rules 6 and 7

Current

Proposed

Spot Check

• [x] Looked on the back end and filtered
• [x] updated "Instructions & Comments field" to include AND CHRISTINA (TEST) to two work orders:
  • WRK23-185870 - checked that before the update the JV Status was "Not Ready"
  • WRK23-185625 - checked that before the update the JV Status was "Not Ready"
• Looked at the Inventory Report and filtered for:
• And see that there are now 2 records showing

  This shows us that the updated conditional rule is working and capturing that work orders do not have to be "Closed" to be put into a JV Status = "Ready".

• Did an bulk update filter with the filter to update the "TestDate" to 5/23/23 - 910 records to update, two already show on the report

Inventory Reported with filter

[ChristinaTremel]

Schedule time to revisit this with Ronnie - 30 minute meeting

Schedule another time to present to the rest of the group when changes have been made.

[dianamartin]

6/16

• Added Notes to AMD Miroboard

JV Status Changes

Requested Changes

• [ ] Add field ON HOLD - 1/0 - number
• [ ] Repurpose Inventory Status field - "Needs AIMS entry" to "On Hold"
• [ ] Add menu filter on Inventory Requests page to "On Hold"
• [ ] Add a button to change "Inventory Status" to "On Hold"

Next Steps

• Testing Needed - "On Hold" testing

Scenario 1

• Add Items - TXN completed
• Add "ON HOLD"
• JV Status - NOT READY
• Lift "ON HOLD"
• JV Status - READY

Scenario 2 - Partially Approved items

• Add Items - TXN Status Completed
• Add New Items - TXN Status Not Completed
• Add "ON HOLD"
• JV Status - NOT READY
• See if WO is On Hold
• Lift "ON HOLD"
• JV Status - READY

[dianamartin]

7/6 - Testing "On Hold" Scenario

Requested Changes

• [x] Add new field on object work_order_signals - INVENTORY_ON_HOLD - 1/0 - number
• [x] Queried on Needs AIMS entry on work_order_signals, zero records found
• [x] Repurpose Inventory Request Status field - Needs AIMS entry to On Hold
• [ ] Added Validation Rule on field
  • INVENTORY ON HOLD higher than zero, set to ON HOLD
• [x] Add menu filter on Inventory Requests page to "On Hold"
• [ ] Add a button to change "Inventory Status" to "On Hold"

WRK23-185870 - checked that before the update the JV Status was "Not Ready" WRK23-185625 - checked that before the update the JV Status was "Not Ready"

Scenario 1

• Add Items - TXN completed
• Add "ON HOLD"
• JV Status - NOT READY
• Lift "ON HOLD"
• JV Status - READY

  The Inventory Request Status is working correctly as expected. The JV Status is not changing even though we updated the Validation Rule # 6 and # 7. Need to keep trying and testing this.

Scenario 2 - Partially Approved items

• Add Items - TXN Status Completed
• Add New Items - TXN Status Not Completed
• Add "ON HOLD"
• JV Status - NOT READY
• See if WO is On Hold
• Lift "ON HOLD"
• JV Status - READY

[dianamartin]

7/12

• WRK23-185870

JV Status Changes

Modified proposed Rule # 7, by added a new rule:

• [x] Tested the "On Hold Status" after we updated the JV Status validation rules
• [ ] Tested the "On Hold Status", remove after we updated the JV Status validation rules

[dianamartin]

Steps followed to enable new JV Status Changes

Back End Updates (object tables)

Change Journal Voucher Status - Conditional Rule

• On inventory_transactions, the "Journal Voucher Status"
• Need to update Rules 6 and 7

Rule Changes

"On Hold" Changes

• [ ] Add new field on object work_order_signals - INVENTORY_ON_HOLD - 1/0 - number
• [ ] Spot check: Query on Needs AIMS entry on work_order_signals, zero records found
• [ ] Repurpose Inventory Request Status field - Needs AIMS entry to On Hold
• [ ] Added Validation Rule on field
  • INVENTORY ON HOLD higher than zero, set to ON HOLD

Front End Updates

"On Hold" Changes

• [x] Add menu filter on Inventory Requests page to "On Hold"
• [ ] Add a button to change "Inventory Status" to "On Hold"

JV Status Changes

• [ ] Add JV Status field to all of the Work Orders Details pages

  thought we already added this to some of the AMD pages

• [ ] Add JV Status field to all of the Work Orders Details pages
• Add Journal Voucher Status, rename to JV Status
• Add Display Rule as background #f6fa6b, when JV status is not blank
  • [ ] Add to "My Work Orders"
  • non-editable
  • editable table
  • [ ] Add to "Work Order - Technicians"
  • non-editable
  • editable table
  • [ ] Add to "Work Order - Non-Technicians"
  • editable table
  • [ ] Add to "Work Order - Communications"
  • editable table
  • [ ] Add JV Status field to Inventory Requests, "Inventory Items" table

[dianamartin]

Had already created https://github.com/cityofaustin/atd-data-tech/issues/12601 - will close this one since new issue will address the changes.