ERM - OpenAccess derived table for charges 'openaccess_charges'
- Stefan Dombek
Content
Overview
- The purpose of this derived table is to show informations about the records in the table "folio_oa.charges" and their linked tables and reference data.
Github Issue
- https://github.com/folio-org/folio-analytics/issues/785 (ERM - Create derived table 'openaccess_charges' for Metadb)
Attributes for the derived table
| Folio Table | Folio Attribute | Description | Datatype | Sample Output | FK (From table) | PK (To table) | Notes |
|---|---|---|---|---|---|---|---|
| folio_oa.charge | ch_owner_fk | UUID | 3e0a383d-b1d9-4a8f-ad39-d35670e7158d | Table: folio_oa.charge Attribute: ch_owner_fk | Table: folio_oa.publication_request Attribute: pr_id | Link to derived table publication_request | |
| folio_oa.charge | version | INTEGER | 0 | ||||
| folio_oa.charge | ch_id | UUID | 199ce4f5-feac-43ea-b995-bf571f6e9ffb | ||||
| folio_oa.charge | ch_amount_fk | UUID | 1f5b8b02-56a0-451d-8ccf-14a662ac90a8 | Table: folio_oa.charge Attribute: ch_amount_fk | Table: folio_oa.monetary_value Attribute: id | ||
| folio_oa.monetary_value | basecurrency | TEXT | EUR | ||||
| folio_oa.monetary_value | monval | NUMERIC | 1689 | ||||
| folio_oa.charge | ch_exchange_rate_fk | UUID | 26765a4e-00b8-4b8a-b3ee-49bc6d0166d8 | Table: folio_oa.charge Attribute: ch_exchange_rate_fk | Table: folio_oa.exchange_rate Attribute: id | ||
| folio_oa.exchange_rate | from_currency | TEXT | EUR | ||||
| folio_oa.exchange_rate | to_currency | TEXT | EUR | ||||
| folio_oa.exchange_rate | coefficient | NUMERIC | 1 | ||||
| folio_oa.charge | ch_description | TEXT | OA-Migration | ||||
| folio_oa.charge | ch_discount | NUMERIC | 0 | ||||
| folio_oa.charge | ch_discount_type_fk | UUID | 8aaa80fe859fe8ee0185a0ea5d5e0017 | Table: folio_oa.charge Attribute: ch_discount_type_fk | Table: folio_oa.refdata_value Attribute: rdv_id | ||
| folio_oa.refdata_value | ch_discount_type_value | TEXT | percentage | ||||
| folio_oa.refdata_value | ch_discount_type_label | TEXT | percentage | ||||
| folio_oa.charge | ch_category_fk | UUID | 8aaa80fe859fe8ee0185a0ea5d81001e | Table: folio_oa.charge Attribute: ch_category_fk | Table: folio_oa.refdata_value Attribute: rdv_id | ||
| folio_oa.refdata_value | ch_category_value | TEXT | apc | ||||
| folio_oa.refdata_value | ch_category_label | TEXT | APC | ||||
| folio_oa.charge | ch_invoice_reference | Link to Module Invoice? | |||||
| folio_oa.charge | ch_invoice_line_item_reference | Link to Module Invoice? | |||||
| folio_oa.charge | ch_discount_note | ||||||
| folio_oa.charge | ch_tax | NUMERIC | 19 | ||||
| folio_oa.charge | ch_charge_status_fk | UUID | 8aaa80fe859fe8ee0185a0ea5d70001a | Table: folio_oa.charge Attribute: ch_charge_status_fk | Table: folio_oa.refdata_value Attribute: rdv_id | ||
| folio_oa.refdata_value | ch_charge_status_value | TEXT | expected | ||||
| folio_oa.refdata_value | ch_charge_status_label | TEXT | Erwartet | ||||
| folio_oa.charge | ch_date_created | TIMESTAMP | 2023-03-03 10:59:26.744 | ||||
| folio_oa.charge | ch_last_updated | TIMESTAMP | 2023-03-03 10:59:26.744 | ||||
| folio_oa.charge | ch_payment_period | TEXT | 2023 |
Linking approach
folio_oa.charge
LEFT JOIN folio_oa.monetary_value ON monetary_value.id = charge.ch_amount_fk
LEFT JOIN folio_oa.exchange_rate ON exchange_rate.id = charge.ch_exchange_rate_fk
LEFT JOIN folio_oa.refdata_value AS discount_type ON discount_type.rdv_id = charge.ch_discount_type_fk
LEFT JOIN folio_oa.refdata_value AS category ON category.rdv_id = charge.ch_category_fk
LEFT JOIN folio_oa.refdata_value AS charge_status ON charge_status.rdv_id = charge.ch_charge_status_fk
Explanation to special relations between tables
Relation "folio_oa.charge" to "folio_oa.payer"
The link between the tables "charge" and "payer" has to be an left join. There is no direct connection in the ER diagram, but you can use the FK "cpy_owner_fk" in the table "payer" to link to table "charge" with the attribute "ch_id".
folio_oa.charge
LEFT JOIN folio_oa.payer ON payer.cpy_owner_fk = charge.ch_id