Figure out how to index MHLD data if/when it changes

[cbeer]

In #766, we determined that order pieces does not include timestamps.

Is there another table we can check, a ticket to file upstream, or do we need to do something clever on our end?

[shelleydoljack]

First of all, the MHLD data in SRS does not get automatically updated when an item that is on order is received through the receiving app in folio. Need to check with @ahafele or others on the staff workflow regarding receiving material where there is an mhld in SRS (i think they decided to stop maintaining mhlds??). Will staff update the MHLD record too? Or is SearchWorks constructing an "mhld" display using SRS MHLD + wherever it is in folio that we get the recently received item? Also, when is checkin = true/false and displayOnHolding = true/false and how should that affect what displays in SW?

For orders, there is the API endpoint /orders/receiving-history which returns some data that looks like it could be used to retrieve recently received stuff:

{
  "id": "0735cecc-e51f-4f19-84ec-94e028d7b2ee",
  "checkin": true,
  "dateOrdered": "2023-06-27T21:55:18.547+00:00",
  "displayOnHolding": true,
  "itemId": "513bb100-2b12-4b20-aab8-17d1d581db2c",
  "pieceFormat": "Physical",
  "poLineId": "25c71650-5477-4578-a0a3-ee02c4d87c7d",
  "poLineNumber": "4727L15-2",
  "poLineReceiptStatus": "Ongoing",
  "purchaseOrderId": "cc9b1567-da81-588c-b738-62ad0d278a34",
  "receivedDate": "2023-06-27T21:56:50.420+00:00",
  "receivingStatus": "Received",
  "supplement": false,
  "title": "FEDERAL INCOME TAXATION."
},
{
  "id": "000ebdc8-2aa7-4d4a-b3dc-7360f49d9d06",
  "caption": "test barcode receiving date",
  "checkin": true,
  "chronology": "2026",
  "comment": "This is a demo.",
  "dateOrdered": "2017-08-29T08:00:00.000+00:00",
  "displayOnHolding": true,
  "enumeration": "V. 2026",
  "copyNumber": "1",
  "itemId": "72b373cd-96de-4c9e-93e6-13bca58bd3a2",
  "pieceFormat": "Physical",
  "poLineId": "5a62985e-7b82-47a5-b67b-78b7414f65b4",
  "poLineNumber": "1002L18-1",
  "poLineReceiptStatus": "Ongoing",
  "purchaseOrderId": "338fe956-c424-5a46-9bb0-1e1e71b721f3",
  "receiptDate": "2023-07-25T00:00:00.000+00:00",
  "receivedDate": "2023-06-11T04:26:28.789+00:00",
  "receivingNote": "LAW-10873 (ACTIVE, cumulative suppl. pamphlet) | Cat1: CONTIN | Cat2: SUBS | Chronology: NUMERATION | Cycle: 1:Y!1:D.5:M! | Issues: 0 | Copies: 1 | Routing: STACKS-2 | No barcode ## LAW-10870 (CHECK-DIR, bound) | Cat1: CONTIN | Cat2: SUBS | Chronology: NUMERATION | Issues: 0 | Copies: 1 | Routing: STACKS-2 | No barcode ## LAW-10871 (CHECK-DIR, general index bound) | Cat1: CONTIN | Cat2: SUBS | Chronology: NUMERATION | Issues: 0 | Copies: 1 | Routing: STACKS-2 | No barcode ## LAW-10872 (ACTIVE, pocket parts) | Cat1: UPKEEP | Cat2: SUBS | Enum: SUPPL. | Chronology: YEAR | Cycle: 1:Y!1:D.5:M! | Issues: 0 | Copies: 1 | Routing: STACKS-2 | No barcode ## LAW-10878 (ACTIVE, interim update service) | Cat1: CONTIN | Cat2: LINKED | Enum: YEAR | Chronology: YEAR | Cycle: 1:Y!1:D.9:M! | Issues: 0 | Copies: 1 | Routing: STACKS-2 | No barcode",
  "receivingStatus": "Received",
  "supplement": false,
  "title": "West's Oregon revised statutes annotated"
},
{
  "id": "06821f58-1176-428b-a359-c6df0c48d987",
  "checkin": false,
  "dateOrdered": "2023-06-27T20:41:24.257+00:00",
  "displayOnHolding": true,
  "enumeration": "V.4",
  "itemId": "9f43ba52-6380-4002-b861-a589984348f9",
  "pieceFormat": "Physical",
  "poLineId": "4316096b-3484-46f5-b4d4-3dfe1e61f2dc",
  "poLineNumber": "11354-1",
  "poLineReceiptStatus": "Ongoing",
  "purchaseOrderId": "820c6c69-3441-45eb-af28-b9aa60c4443e",
  "receivedDate": "2023-06-27T20:42:59.219+00:00",
  "receivingStatus": "Received",
  "supplement": false,
  "title": "Representing phonological detail / edited by Jeroen van de Weijer."
}

[shelleydoljack]

Maybe something to keep in mind as we develop our own solution: https://issues.folio.org/browse/UXPROD-3525

[cbeer]

I think receiving history is just using order/pieces under the hood, and the problem with order/pieces is it doesn't index any kind of updated timestamp.

[shelleydoljack]

I think receiving history is just using order/pieces under the hood, and the problem with order/pieces is it doesn't index any kind of updated timestamp.

Yea, I think you're right about that.

[ahafele]

Re: Ongoing maintenance of MHLDs - workflows are still being worked out but we can count on MHLDs being updated for some time because as we make the switch to folio holdings, the MHLD will be updated by removing certain pieces of information - e.g. the holdings statement and moving it to the source = folio holdings.

[shelleydoljack]

Maybe we implement something along the lines of a "real time availability lookup" but for pieces. It seems this is essentially what folio does when showing "receiving history" in the holdings record:

A request to /orders/pieces with query holdingId==27f0779d-0980-5021-8501-fa2c4a97587b+and+displayOnHolding=="true"+and+receivingStatus="Received" is made to display this info. Is something like this possible? Or maybe /orders/pieces?query=cql.allRecords = 1 and displayOnHolding==true and receivedDate>"2023-07-03*" and iterate through the response for holdingId's to know which holding the received piece is for? If we don't approach it this way, given that folio is half-implemented in hooking up receiving, pieces, and inventory holdings, I'm not sure how else we'd know that a piece was recently received.

Also, hopefully this was discussed in another ticket, or storytime, that the MHLD data and what is then received on a folio holdings record will be two different holdings.

[ahafele]

Yup, and documented here for reference - https://docs.google.com/drawings/d/1fXBVs7i1lLAUu0reju8lwUQmady3lqDfZrixCFu_YAI/edit

[shelleydoljack]

Per https://stanfordlib.slack.com/archives/C03TVH4L95W/p1688595261374089?thread_ts=1688584050.910599&cid=C03TVH4L95W:

For print serials, my understanding is that SearchWorks will look at the Receiving history for the most recently received piece to be displayed.
In the Receiving App, the “Displaying on holding” box for ongoing orders appears to be the default setting. This box should always be checked in order for the received pieces to appear in the Receiving history list in Inventory Holdings.
“Receiving workflow” has to be set at “Independent order and receipt quantity” in order to enable the existing automation between the Receiving App and the Inventory App. This way when a piece is received in the Receiving App, it automatically displays in the Receiving history in Inventory Holdings.

[cbeer]

From @taylor-steve's investigation into new columns in Poppy:

Steve:
Both /orders/pieces and /orders/pieces/{id} have statusUpdatedDate now. Updated when receiving status changes. Is that what we're looking for? Schema suggests it should be in the json in sul_mod_orders_storage.pieces, but it does not occur in our data?
mod-order handling of statusUpdatedDate:
Updated with current date when updatePiece sees a diff between the current and new receiving status: https://github.com/folio-org/mod-orders/blob/bb9ebeddee9f1c90349faef3da954adb29b952f5/src/main/java/org/folio/service/pieces/flows/update/PieceUpdateFlowManager.java#L71
Updated with current date when piece record marked as ready to be received: https://github.com/folio-org/mod-orders/blob/bb9ebeddee9f1c90349faef3da954adb29b952f5/src/main/java/org/folio/helper/CheckinReceivePiecesHelper.java#L444
Composite Order Piece Service will update with current date if there is a receiving status change: https://github.com/folio-org/mod-orders/blob/bb9ebeddee9f1c90349faef3da954adb29b952f5/src/main/java/org/folio/service/orders/flows/update/open/OpenCompositeOrderPieceService.java#L131
statusUpdatedDate is not available in /orders-storage/receiving-history.

[shelleydoljack]

I don't see that there was a database migration when upgrading to mod-orders-storage 13.6.0 that is related to populating this data in the tables. https://github.com/folio-org/mod-orders-storage/tree/v13.6.0/src/main/resources/templates/db_scripts/data-migration/13.6.0

My guess is that the data will get there as users interact with the Receiving app.

[cbeer]

Agreed; I think we're waiting for acq testing to start. If statusUpdatedDate is populated for newly created pieces, then we can consider using statusUpdatedDate. If it's only used on existing items, then it's probably useless?

[ahafele]

@cbeer Acq testing has begun already - https://docs.google.com/spreadsheets/d/1DJCuDYfHna34_n3VjQYIK9xzVAsYZZHGIBVFRpCbD1I/edit#gid=0 Are there particular actions you need them to take on specific records?

[cbeer]

I think were hoping Acq will create some new orders, receive some pieces, etc.

[saseestone]

Feels like a good time to check back up on this. Acquisitions staff have created records. Example instance records are in the spreadsheet that Alissa links above.

[dnoneill]

I am seeing the same thing as Steve was earlier. Field not showing up in the database. Don't have access to api. Kicking back to backlog.

[shelleydoljack]

I had to see for myself. 😞

okapi=# select count(*) from sul_mod_orders_storage.pieces where jsonb ->> 'id' is not null;
 count  
--------
 417207

okapi=# select count(*) from sul_mod_orders_storage.pieces where jsonb ->> 'statusUpdatedDate' is null;
 count  
--------
 417207

okapi=# select count(*) from sul_mod_orders_storage.pieces where jsonb ->> 'statusUpdatedDate' is not null;
 count 
-------
     0

[shelleydoljack]

Oh, well the version of mod-orders we are running (12.7.1) doesn't have that statusUpdatedDate in the OpenCompositeOrderPieceService. https://github.com/folio-org/mod-orders/blob/06d038eb4f675ecf790d443e4aa513c34d55656b/src/main/java/org/folio/service/orders/flows/update/open/OpenCompositeOrderPieceService.java#L117 So now I'm no longer concerned about whether our upgrade worked the way it should or not.

[taylor-steve]

Oh nice, good catch. Mystery solved.