RFC - Design Doc - MaterialSubLot Location History

RFCs
1

Material SubLot Location History

Nominated Owners: @geoff

Summary

One paragraph explanation of the feature.

MES applications often keep track of where stuff is located. In ISA95,uniquely identifiable stuff is a materialSubLot. A materialSubLot has a storageLocationOperationalLocation relationship which defines where that sublot is physically located.
There are many scenarios where we want to be able to see where the materialSubLot has been in the past, not just where it is now. This design doc explores the solution for materialSubLot location history.

Retrospective

This section is essential to allow us to learn from the things we are implementing.

When is the retrospective?

Retro completed?

(where/how it will be held, how can people get involved, where are the results?)

Motivation

Why are we doing this? What use cases does it support? What is the expected outcome?

We currently have a solution for this implemented at Skyzer using Rhize v2. We implemented a solution there as we had a requirement to be able to generate ‘Point-in-time’ inventory reports. I.e. "What inventory did we have in the warehouse on friday last week? What inventory arrived into the warehouse last week? Which sublots moved to a different location within the warehouse last friday?

We have a similar requirement at the moment to implement with Rhize v3. The customer tracks sublots along locations on a conveyor, and would like to be able to analyse the time a sublot spends at various locations to help with optimizing the overall processing time.

In both scenarios, the expected outcome can be described as:

  • the changes in location of a material are recorded and kept for analysis and reporting
  • the changes in quantity of a material in a location are kept for analysis and reporting
  • the changes in state of a material in a location are kept for analysis and reporting.

Guide-level explanation

Explain the proposal as if it was already existing and you were teaching it to another engineer. That generally means:

  • Introducing new named concepts.

    • Inventory Journal: An Inventory Journal is a ledger that stores changes to inventory over time. Like all ledgers, it is double-entry containing a debit record and a credit record representing where the inventory moved from and where it moved to.

  • Explaining the feature largely in terms of examples.

    • Example: A user moves a sublot to a new warehouse location.
      • Assume a sublot exists in location A, and a user moves it to location B: We would want to update the subLot’s storageLocationOperationalLocation to B AND record the InventoryJournal entries to record that the sublot moved from A to B like this:
TimeStamp TransactionID MaterialSubLotID OperationalLocationID Quantity
2024-04-06T11:52:00+11:00 T001 MSL001 A -1
2024-04-06T11:52:00+11:00 T001 MSL001 B 1
  • Example: A materialSubLot moving along a conveyor from location to location:
    • The PLC contains tags which indicate the SubLotID of the materialSubLot currently in a location
    • Rhize monitors those PLC tags using OPCUA, and when a change in value is detected:
      • If the value changes to nil: - This is indicating that the sublot is no longer in this location. In most cases, in the PLC logic, this would coincide with the SubLotID appearing in a new location.
      • if the value changes to a new value - This is indicating that a new sublot is now at this location, and the old sublot is no longer in this location.
      • if the value changes from nil to a new value. This is indicating that the sublot has moved to this location from it’s previous location.
    • Assume the conveyor has locations A,B,C,D and that a materialSubLot can only exist in one of those locations, and must exist in one of those locations: As a MaterialSubLot moves along the conveyor, we could see entries in the ledger like:
TimeStamp TransactionID MaterialSubLotID OperationalLocationID Quantity
2024-04-06T11:52:00+11:00 T001 MSL001 UNKNOWN -1
2024-04-06T11:52:00+11:00 T001 MSL001 A 1
2024-04-06T12:02:00+11:00 T002 MSL001 A -1
2024-04-06T12:02:00+11:00 T002 MSL001 B 1
2024-04-06T12:12:00+11:00 T003 MSL001 B -1
2024-04-06T12:12:00+11:00 T003 MSL001 C 1
2024-04-06T12:22:00+11:00 T004 MSL001 C -1
2024-04-06T12:22:00+11:00 T004 MSL001 D 1
2024-04-06T12:32:00+11:00 T005 MSL001 D -1
2024-04-06T12:32:00+11:00 T005 MSL001 UNKNOWN 1

It is worth noting that it is possible to derive the current status of a material sub lot from the ledger without storing the current state, but the additional computing overhead and query complexity generally out-weigh the benefits provided the ledger entries and the update to the material sub-lot current state can be guaranteed to remain consistent.

  • Explaining how engineers should think about the feature. It should explain the impact as concretely as possible.

    The Inventory Journal is like a bank account statement. It shows the changes in your bank account over time, rather than how much money you currently have.

  • If applicable (e.g. code/architecture proposal), provide sample error messages, deprecation warnings, or migration guidance.

  • If applicable, describe the differences between teaching this to existing engineers and new engineers.

Reference-level explanation

This is the technical portion of the RFC. Explain the design in sufficient detail that:

  • Its interaction with other features is clear.
    • The backend should always update the current state AND add an entry to the ledger as part of a single transaction so that the current state and the ledger are guaranteed to remain consistent
  • It is reasonably clear how the feature would be implemented.
    • this will be implemented as a new mutation that performs a material movement (recordMaterialMovement mutation perhaps?)
  • Corner cases are dissected by example.

The section should return to the examples given in the previous section, and explain more fully how the detailed proposal makes those examples work.

Drawbacks

Why should we not do this?

Rationale and alternatives

  • Why is this design the best in the space of possible designs?

  • What other designs have been considered and what is the rationale for not choosing them?

    • Use MaterialActual records to reflect the movement of materials
    • Do not store the current state, derive it from the inventory ledger
    • Do not store history in the database, use CDC to store it in the DataLake instead.

  • What is the impact of not doing this?

Prior Art

Discuss prior art, both the good and the bad, in relation to this proposal. A few examples of what this can include are:

  • For language, library, tools etc: Does this feature exist in other places and what experience have their community had?

  • For community proposals: Is this done by some other community and what were their experiences with it?

  • For other teams: What lessons can we learn from what other communities have done here?

  • Papers: Are there any published papers or great posts that discuss this? If you have some relevant papers to refer to, this can serve as a more detailed theoretical background.

This section is intended to encourage you as an author to think about the lessons from other places, provide readers of your RFC with a fuller picture. If there is no prior art, that is fine - your ideas are interesting to us whether they are brand new or if it is an adaptation from other places.

Unresolved Questions

  • What parts of the design do you expect to resolve through the RFC process before this gets merged?

    • What standard fields should be part of every InventoryJournal record?
    • Should the Journal live in BAAS or InfluxDB?
    • What other use-cases do we need to consider?
    • What is the over-lap with TimeSeries ?

  • What parts of the design do you expect to resolve through the implementation of this feature before stabilization?

  • What related issues do you consider out of scope for this RFC that could be addressed in the future independently of the solution that comes out of this RFC?

Future Possibilities

Think about what the natural extension and evolution of your proposal would be and how it would affect the teams and projects as a whole in a holistic way. Try to use this section as a tool to more fully consider all possible interactions with the project and teams in your proposal. Also consider how the this all fits into the roadmap for the project and of the relevant sub-team. This is also a good place to “dump ideas”, if they are out of scope for the RFC you are writing but otherwise related. If you have tried and cannot think of any future possibilities, you may simply state that you cannot think of anything. Note that having something written down in the future-possibilities section is not a reason to accept the current or a future RFC; such notes should be in the section on motivation or rationale in this or subsequent RFCs. The section merely provides additional information.

2

When looking into the Inventory Journal, this does not seem to be a part of the ISA-95 standard. So will this be a expansion on our current schema? And is this in aother standard that i can go and read more about?

3

You mention the use of the storageLocationOperationalLocation. MaterialSubLots also have StorageLocationEquipment. In the current project I am working on they seem to be used almost interchangeably, but the benefit of the equipment is that you can have a more structured hierarchy. What is the benefit of using the OperationalLocation Instead of Equipment. Also the OperationalLocation on the Rhize Admin UI has the map, which I made the assumption would mean that it is used for larger areas, not specific location in the work place. Is this not the case?

4

In the Skyzer project, we used InfluxDB to store the InventoryJournal
@cooper.fitzgerald is the guru on how we did that. We then created a graphql schema extension for it.

We could use MaterialActual records to store the movement history directly in the GraphQL schema which would be more ISA95 compliant and native, but more difficult to query for reporting.

This particular requirement is primarily an anlytical requirement rather than a transactional requirement. By this I mean that the history has no apparent value when performing other transactions in the system that I can see, but will be used to answer questions like:

  • how long to material sublots spend queued up waiting for processing?
  • How long does the buffer of material at the entry of the machine last?
  • when the processing line stops, is there material at the entry of the machine? or at the exit?

These types of analytical queries are often a bit more complex that we can handle with straight GraphQL, and so an analytical database provides more power in the queries.

5

A storage location can be an equipment or an operational location. Not all equipment represent storage locations, and there are many scenarios where a storage location is not an equipment.

In your specific case, the storage locations have product sensors in them that are integrated with the PLC and which send data to the Rhize platform, so they would definitely be modelled as Equipment rather than OperationalLocations.

6

An alternate solution would be for us to use the MaterialActual with MaterialUse = Transferred to store the location history of materialSubLots