ERM - OpenAccess derived table for charges 'openaccess_charges'
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