ACQ Reporting

In this cookbook, some connections and specific SQL statements should be explained for the ACQ area.

Please note: SQL statements concerning finances (for orders and invoices) and related organizations (vendors, bill address, ship to address etc.) are in the Cookbook Finances.

Table of Contents

• 1. Purchase orders and inventory
  • 1.1. Linking purchase order line to instance
  • 1.2. Linking purchase order line to holdings
  • 1.3. Linking purchase order line to items
• 2. Purchase orders and receiving

1. Purchase orders and inventory

There are various UUIDs that you can use to link a purchase order (line) with a record in the inventory. It depends on what level you want to connect to the inventory.

Hierarchy

There are 3 levels in the inventory: instance, holdings and item.

Example:

intance	holdings	item
intance A	holding A	item 1
intance A	holding A	item 2
intance A	holding B	item 1
intance A	holding B	item 2

The Reporting SIG created a derived table po_instance.

[top]

1.1. Linking purchase order line to instance

You can link a purchase order line to an instance record via UUIDs.

Example:

SELECT 
    *
FROM 
    folio_orders.po_line__t
    LEFT JOIN folio_inventory.instance__t ON instance__t.id = po_line__t.instance_id

[top]

1.2. Linking purchase order line to holdings

You can link a purchase order line to a holding record via UUIDs.

Example:

SELECT
    po_line.id AS po_line_id,
    jsonb_extract_path_text(holding_id.jsonb, 'holdingId') AS holding_id
FROM 
    folio_orders.po_line
    CROSS JOIN LATERAL jsonb_array_elements(jsonb_extract_path(po_line.jsonb, 'locations')) WITH ORDINALITY AS holding_id (jsonb)
WHERE 
    jsonb_extract_path_text(holding_id.jsonb, 'holdingId') IS NOT NULL 

[top]

1.3. Linking purchase order line to items

You can link a purchase order line to an item record via UUIDs.

Example:

SELECT
    *
FROM 
    folio_orders.po_line__t
    JOIN folio_inventory.item__t ON item__t.purchase_order_line_identifier = po_line__t.id

[top]

2. Purchase orders and receiving

[top]