Supporting Package Orders

Overview

We need the ability to order not just single titles, but collections of titles.  These collections are usually referred to as "packages", but may also be referred to as "containers" within the context of FOLIO inventory.  This page provides the necessary context and details about how this functionality will be incorporated into the orders app.

N.B. Throughout this document you may see references to "containers" - We're holding off on this part for now until there are more details on how it will be implemented in inventory.

TODO:

  • Dennis and A-M to discuss the need to search for POLs by title product Ids w/ small group

Requirements

  1. A purchase order line (POL) must be able to pertain to either an individual title, or a collection of titles (package/container)
  2. A package may be comprised of 0 or more titles, each of which may or may not relate to an instance in inventory.
  3. A title can only relate to a single Package POL
  4. An instance in inventory may be associated with 0 or more Titles
    1. i.e. A book may have been ordered multiple times (an original order, then later another order for additional copies).  In this case there would be multiple POLs, each with a title record for the same instance.  The title records would look very similar if not identical except for their link to the POL.
  5. A package must be able to have its own name - i.e. "The Collected Works of Philip K. Dick"
  6. The UI must be able to query POLs by title (e.g. give me all the POLs with title "The Man in the High Castle")
  7. The UI must be able to retrieve all titles for a given POL (could be one or many depending on if the order is for a package or individual title)
  8. The UI must be able to search for Titles by product Id (receiving)
  9. The UI must be able to search for POL by product Id (order-line search & filter) - but not by title product Ids???
  10. TBD - There are reqs on the receiving side of things which aren't here yet

Where We Are Now

Schemas

Title and instance information lives directly in the POL, which only allows for a single title & instanceId

UI - Search And Filter

UI - POLine Details

Title - a single value that links to the related instance in inventory

UI - POLine Create/Edit Forms

NOTE:  Product IDs - These pertain to the title/instance above.

Purchase Order Details

UI - Receiving/Check-in

TBD

Where We Want To Be

Schemas

A new "Title" schema (and table) is introduced:

title

PropertyTypeDefaultRequiredNotes

id

UUID
NUUID of this Title - system generated if not provided
poLineIdUUID
YUUID of the purchase order line this Title is associated with, Foreign Key
titlestring
YThe title of the work
instanceIdUUID
NUUID of the instance associated with this Title - Invalid to have both this and containerId simultaneously
containerIdUUID
NUUID of the container associated with this Title - Invalid to have both this and instanceId simultaneously
productIdsproductId
N

An array of productIds - each with: id, productIdType, qualifier

NOTE:  ISBNs will need to be validated and converted to ISBN-13 

contributorscontributor
NAn array of contributors - each with: contributor, contributorNameTypeId

publisher

string
NPublisher of the material
publishedDatestring
Ndate (year) of the material's publication - limited to exactly 4 digits
editionstring
NEdition of the material

subscriptionFrom

date-time
NStart date of the subscription - currently lives in po_line → details 
subscriptionTodate-time
NEnd date of the subscription - currently lives in po_line → details 
subsriptionIntervalinteger
Nthe subscription interval in days - currently lives in po_line → details 
isAcknowledgedbooleanfalseNFlag for acknowledge receiving note (introduced in UIREC-32)
packageNamestring
NThe name of the package.  Copied from the package POL's titleOrPackage field.  NOTE:  this could be the POL referenced directly by poLineId, or indirectly, e.g. the POL referenced by the packagePoLineId in that POL.
poLineNumberstring
NThe poLineNumber of the POL identified by poLineId
receivingNotestring
NReceiving note from the POL identified by poLineId - currently lives in po_line → details
expectedReceiptDatedate-time
NFrom the poLine->physical.expectedReceiptDate
metadatametadata
NSystem generated record metadata

po_line

The "po_line" schema changes as follows:

  • Rename "title" to "titleOrPackage"
    • remains required
    • either the title of the individual work or the package name
    • in the case of an individual work, this data is essentially duplicated from the one Title record associated with this POL.
      • This is done to help with display of POLs in search/filter & PO detail views
  • Remove "instanceId"
    • this is now captured in the Title record
  • Add "isPackage"
    • Boolean, not required, default=false
    • Indicates that this POL is for a package
  • Add packagePoLineId
    • UUID referencing the poLine that represents the package that this POLs title belongs to.
    • Optional

composite_po_line

The "composite_po_line" schema changes as follows:

  • Rename "title" to "titleOrPackage"
    • remains required
    • either the title of the individual work or the package name
    • in the case of an individual work, this data is essentially duplicated from the one Title record associated with this POL.
      • This is done to help with display of POLs in search/filter & PO detail views
  • Add "isPackage"
    • Boolean, not required, default=false
    • Indicates that this POL is for a package
  • Add "containerId"
    • UUID of the inventory container record associated with this Title
    • not required, no default
  • NOTE:  instanceId remains here.

pieces

The pieces schema changes as follows:

  • Add "titleId"
    • UUID of the title record the piece is associated with
    • required
    • foreign key

APIs

titles

Standard CRUD API for the new "titles" table.

order-lines 

GET /orders/order-lines/<id> is updated as follows:

  • If isPackage == true
    • populate the containerId fields in the composite_po_line response from the value in the Title record
    • NOTE:  In this case the "package" field would not be a hotlink in the UI - POL Details View.
  • else
    • populate the instanceId field in the composite_po_line response from the value in the Title record
    • NOTE:  This allows the "title" field to be a hotlink to the instance in the UI - POL Details View.

PUT /orders/order-lines/<id> is updated as follows: 

  • if isPackage == true and an instanceId is provided → return an error
  • if isPackage == false and an instanceId is provided → update the title record

POST /orders/order-lines is updated as follows:

  • if isPackage == true and an instanceId is provided → return an error
  • if isPackage == false and an instanceId is provided → use the instanceId when creating the title record

composite-orders

GET /orders/composite-orders/<id> is updated as follows:

  • For each POL:
    • If isPackage == true
      • populate the containerId fields in the composite_po_line response from the value in the Title record
      • NOTE:  In this case the "package" field would not be a hotlink in the UI - POL Details View.
    • else
      • populate the instanceId field in the composite_po_line response from the value in the Title record
      • NOTE:  This allows the "title" field to be a hotlink to the instance in the UI - POL Details View.

PUT /orders/composite-orders/<id> is updated as follows: 

  • For each POL:
    • if isPackage == true and an instanceId is provided → return an error
    • if isPackage == false and an instanceId is provided → update the title record

POST /orders/composite-orders is updated as follows:

  • For each POL:
    • if isPackage == true and an instanceId is provided → return an error 
    • if isPackage == false and an instanceId is provided → use the instanceId when creating the title record


UI - Search/Filter

Need mockups.

Column renamed:  Title → Title/Package

UI - POLine Details

Need mockups

If "isPackage" == true, display "Package" instead of "Title".  The link would bring you to the container in inventory if one is specified.

UI - POLine Create/Edit Forms

Need mockups

UI - Purchase Order Details

Need mockups

UI - Recieving/Checking

Need mockups

JIRA

  • TBD

Open Issues/Considerations

  • TBD

Appendix

Data Model

Two competing data models were considered for this, with only one being chosen as the preferred design.

A. Store both Package and Title information in the Title record

In this option we store both package and title information in the title records and remove the bib information from the POL itself.  The title record would have some way (a flag?) of determining if the record represents a package or a title. 

Pros:

  • Removes the need to duplicate information between the POL and title records.  

Cons:

  • Search and retrieval are significantly more complicated.  
    • GET POL by ID: 
      • If isPackage==true, get the title record that represents the package and use it to populate the POL bib info in the composite_po_line
      • Else, there should only be a single title record.  Get the first title record and use it to populate the POL bib info in the composite_po_line
    • GET POL by query:
      • Significantly more complicated...
      • if searching for POL by title, you'd need to cover the two possible scenarios...
        • titles.title=Java for dummies AND titles.title.isPackage==false AND isPackage==false
        • titles.title=Java for dummies AND titles.title.isPackage==true AND isPackage==true
      • Regardless of the query, the results would need to be augmented w/ the bib information as described above as title is currently displayed in the POL search results.

B. Duplicate the bib information in both the POL and Title records

Here we keep the bib fields in the POL, with some renaming, e.g. title → packageOrTitle.  These fields now have contextual meaning, depending on the value of the isPackage field.  If the POL is for a package, the bib fields pertain to the package, e.g. the productIds are the ids associated with the package, not a title in the package.  

NOTE:  the duplication of information here may be needed to support the ability to search for titles in the receiving area.  It's possible, but not ideal to have the UI search both PoLines.packageOrTitle and Titles.title in the same query.  It's much easier (and less confusing for clients to have to search a single place (the title records in this case).  Example query that would work w/o duplication:  packageOrTitle=Java for Dummies OR titles.title=Java for Dummies.   (Side note - maybe it isn't so bad after all... IDK)

Pros:

  • Search and retrieval stay comparatively simple.  We don't have to jump through the hoops described above in Option A.

Cons:

  • The duplication of information in the POL and Title records opens the door for data inconsistencies, e.g. what happens if the call to update the POL succeeds but the title record update fails?  vice versa?
    • There's precedence for a couple different approaches to solving this...
      • If eventual consistency is acceptable, use the vert.x event bus like we do for paymentStatus/receivingStatus - someday FOLIO will have a pub/sub mechanism that provides durability, QoS, guaranteed delivery, etc. - the idea is that the asynchronous handlers developed to consume event bus messages will be easily adapted to consume pub/sub messages.
      • Else, we can handle this in the storage module since both title and pol live in the same tablespace (they share a storage module).  This means we can update both tables at the same time in a database transaction making it an all-or-nothing operation.