ERM-Agreements Costs Cluster Prototype

JIRA Tickets associated with this Report Cluster:

The JIRA ticket for this report cluster is UXPROD-2368 ERM-Agreements Costs Cluster including all respective Rep-Jira Tickets.


Report Description Detail

Overview

  • This report is modeled on ERM-Agreements Costs Cluster, a cluster of ERM reports currently consisting of (REP-XXX, etc.). The purpose of this cluster of reports is to show cost statistics associated with ERM agreements.
  • For e-resources stored in Inventory please see information on ERM Inventory costs  - FOLIO Wiki, JIRA UXPROD-2408


Reports

  • How much did I pay for my agreements? e.g. e-Books
    • Output: time frame / invoice amounts
  • ( REP-41 (costs): Annual E-Resource Counts for ARL, ACRL and NCES and local reporting ) –> please take a look at UXPROD-2529 Association of College and Research Libraries Report
  • REP-265: Digital stocks, altogether (without electronic journals and electronic newspapers) (DBS 113)
  • REP-266: Digital stocks, among these: commercially sold E-Books (digital textual materials which allow for searches on them) (DBS 113.4)
  • REP-267: "Digital stocks, of those: stocks in data bases (contents which are to be retrieved under a common user interface) serials that are not continuous,
    not published on a regular basis; aggregator databases; for example, databases with topically based selections of journals" (DBS 121)
  • REP-68: Journals and newspaper in electronic form: costs for those (licenses) in the reporting year (DBS 134 , expenses)

    • Output: Amount
  • REP-69: Other costs for journals and newspapers in electronic form, e.g. one-time purchase of backfiles (DBS 135, expenses)

    • Output: Amount
  • REP-158: Request and Verify Status of E-Resource Purchase Requests
  • REP-232: All materials expenditures divided into physical vs. electronic (lower priority for breakouts by format within these divisions). (Lower priority for budgeted amounts for materials?)

Data Attributes

  • (high-level list of data fields)
  • Data Attributes will come from the following application areas: Orders, Invoices, and Agreements

Report Criteria

  • (describe report cluster parameters, criteria, filters, etc.)


Information

-See ERM Statistics summary for report descriptions and priority.


Information Resources for Data Attributes

Information

FOLIO Attribute Definition Sources

-API reference documentation for all modules located at:  https://dev.folio.org/reference/api/

-Github

-See eUsage: requirements/fields

-See  UXPROD-1264 - Calculate Statistics: Costs Per Download

-See the Finance Data Model

-RM-Costs Cluster Prototype FOLIO Wiki

-RM-Costs Cluster UXPROD-2255

Folio Attribute

Folio Data Element Description

Notes

Table name: erm_agreements_subscription_agreement

agreement ID
(mod_agreements/subscription_agreement:

sa_id)
UUID of Agreement
agreement Name
(mod_agreements/subscription_agreement:sa_name)
A name for the agreement assigned by the institution
agreement Description
(mod_agreements/subscription_agreement:sa_description)
A description for the agreement assigned by the institution
reference data ID of status
(mod_agreements/subscription_agreement:sa_agreement_status)
ID of reference data value (mod_agreements.refdata_value.rdv_id) which describes the current status of the agreement (e.g. Active, Closed)
reference data ID of reason of closure
(mod_agreements/subscription_agreement:sa_reason_for_closure)
ID of reference data value (mod_agreements.refdata_value.rdv_id) which describes for a closed agreement, the reason the agreement has been closed (a closed agreement is one with status == "Closed" - set via sa_agreement_status)
reference data ID of renewal priority 
(mod_agreements/subscription_agreement:sa_renewal_priority)
ID of reference data value (mod_agreements.refdata_value.rdv_id) which describes whether an agreement should be renewed, reviewed or cancelled
reference data ID of "is perpetual"(mod_agreements/subscription_agreement:sa_is_perpetual)ID of reference data value (mod_agreements.refdata_value.rdv_id) which describes whether the agreement is a perpetual agreement or not
identifier of local reference
(mod_agreements/subscription_agreement:sa_local_reference)
Where an agreement has been created through an integration / data import from an external system the sa_local_reference is used to store a reference/identifier for the agreement in the external system to support ongoing data synchronisation/updates
license notes of agreement
(mod_agreements/subscription_agreement:sa_license_note)
To record any general information about the license for the Agreement
ID to custom properties
(mod_agreements/subscription_agreement:custom_properties_id)
ID used to link custom_properties (aka supplementary properties) via mod_agreements.custom_properties.parent_id to an agreement. (n.b. each agreement can have zero to many custom_properties)
Table name: erm_agreements_entitlement

Each entitlement is linked to one and only one agreement

Each agreement can be linked to zero to many entitlements

Each entitlement must to linked to only one resource

entitlement ID
(mod_agreements/entitlement:ent_id)
UUID of Entitlement (aka Agreement Line)
associated agreement ID
(mod_agreements/entitlement:ent_owner_fk)
ID of Agreement (mod_agreements.subscription_agreement.sa_id) to which the entitlement belongs

associated resource ID
(mod_agreements/entitlement:ent_resource_fk)

ID of the resource (mod_agreements.erm_resource.id, mod_agreements.package.id, mod_agreements.package_content_item.id ) which the Entitlement gives access to
active from date
(mod_agreements/entitlement:ent_active_from)
Date from which the entitlement was active in format yyyy-MM-dd. No time or timezone
active to date
(mod_agreements/entitlement:ent_active_to)
Date to which the entitlement was active in format yyyy-MM-dd. No time or timezone
authority
(mod_agreements/entitlement:ent_authority)
Where an entitlement gives access to a resource described in a source other than the mod_agreements internal knowledgebase, the ent_authority is a way of identifying where the definition of the resource is held Where eHoldings is being used as the source of entitlement/agreement line resources, the value of ent_authority will be one of:
EKB-TITLE
EKB-PACKAGE
reference ID of resource
(mod_agreements/entitlement:ent_reference)
Where an entitlement gives access to a resource described in a source other than the mod_agreements internal knowledgebase, the ent_reference is the ID for the resource in the source identified by ent_authority 

Where eHoldings is being used as the source of entitlement/agreement line resources, the ent_reference will be an identifier created from two or three parts, each part being an integer:

<provider id>-<package id>-<title id>

For example:

"Gale Cengage" (provider id = 18) provide a package "Academic ASAP" (package id = 34) which contains a title "Harvard Business Review" (title id = 32090)

  • the identifier for the resource "Academic ASAP from Gale Cenage" is 18-34 (this would have an ent_authority of EKB-PACKAGE)
  • the identifier for the resource "Harvard Business Review in the Academic ASAP package from Gale Cengage" is 18-34-32090  (this would have an ent_authority of EKB-TITLE)
note
(mod_agreements/entitlement:ent_note)
A textual note about the entitlement
Table name: erm_agreements_order_line

Links entitlements to purchase order lines in the Orders app.

Each entitlement can be linked to zero to many purchase order lines.

Each purchase borderline can be linked to zero to many entitlements

record ID
(mod_agreements/order_line:pol_id)
UUID of order_line record
po-line ID
(mod_agreements/order_line:pol_orders_fk)
ID of purchase order line in Orders app
entitlement ID
(mod_agreements/order_line:pol_owner_fk)
ID of entitlement (mod_agreements.entitlement.ent_id) to which purchase order line is linked
Table name: erm_agreements_erm_resource

erm_resource is one facet of resources that can be linked to entitlements

Each erm_resource can be linked to zero to many entitlements

ERM resource ID
(mod_agreements/erm_resource:id)
ID of erm_resource
resource name
(mod_agreements/erm_resource:res_name)
Name of the resource (can be name of a package or other resource type or the title of a published work such as a book or journal)
type ID
(mod_agreements/erm_resource:res_type_fk)
ID of reference data value (mod_agreements.refdata_value.rdv_id) for the type of resource. Common values include "serial", "monograph", "book", "journal".
subtype ID
(mod_agreements/erm_resource:res_subtype_fk)
ID of reference data value (mod_agreements.refdata_value.rdv_id) for the subtype of resourceCommon values include "electronic" or "print".
Table name: erm_agreements_package

A package represents a group of content

Every package is an erm_resource.

Each package contains zero or more package_content_items

package ID
(mod_agreements/package:id)
ID of packageThe ID of the package will appear in the erm_resource table for the erm_resource record associated with the package
provider of package ID
(mod_agreements/package:pkg_vendor_id)
ID of the package provider organisation (in mod_agreements.orgs)Despite the column name, this is for the provider of the package NOT the vendor of the package
source of package
(mod_agreements/package:pkg_source)
String describing the source of the package data
remote kb ID
(mod_agreements/package:pkg_remote_kb)
ID of the remote knowledgebase from which the package data was obtained (in mod_agreements.remotekb)
reference to external system
(mod_agreements/package:pkg_reference)
ID or reference for the package in an external system
Table name: erm_agreements_package_content_item

A package_content_item represents a particular resource (book or journal title) accessible via a particular online platform sold or made available as part of a particular package

Every package_content_item is an erm_resource.

Each package_content_item belongs to exactly one package

Each package_content_item is linked to exactly one platform_title_instance

record ID
(mod_agreements/package_content_item:id)
ID of the package content itemThe ID of the package_content_item will appear in the erm_resource table for the erm_resource record associated with the package_content_item
platform title instance ID
(mod_agreements/package_content_item:pci_pti_fk)
ID of the platform_title_instance that the package_content_item is linked to
package ID
(mod_agreements/package_content_item:pci_pkg_fk)
ID of the package that the package_content_item belongs to
access start date
(mod_agreements/package_content_item:pci_access_start)
The date on which a package_content_item was first accessible in a packageIf `null` then it is assumed that the package_content_item was accessible in the package at the earliest possible data
access end date
(mod_agreements/mod_agreements/package_content_item:pci_access_end)
The date on which a package_content_item was last accessible in a packageIf `null` then it is assumed that the package_content_item is still accessible in the package at the current date
Table name: erm_agreements_platform_title_instance

A platform_title_instance represents a particular resource (book or journal title) accessible via a particular online platform.

Every platform_title_instance is an erm_resource. 

Each platform_title_instance belongs to exactly one platform

Each platform_title_instance is linked to exactly one title_instance

Each platform_title_instance is linked to zero or more package_content_items

platform title instance ID
(mod_agreements/platform_title_instance:id)
ID of the platform_title_instanceThe ID of the platform_title_instance will appear in the erm_resource table for the erm_resource record associated with the platform_title_instance
associated platform ID
(mod_agreements/platform_title_instance:pti_pt_fk)
The ID of the platform that the platform_title_instance belongs to
associated title instance ID
(mod_agreements/platform_title_instance:pti_ti_fk)
The ID of the title_instance to which the platform_title_instance is linked
URL (mod_agreements/platform_title_instance:pti_url)The URL of the platform_title_instanceThis is the URL where the relevant resource content can be accessed
Table name: erm_agreements_platform

A platform (in Agreements) is where a resource can be accessed online. An example might be “Highwire”. Each platform_title_instance belongs to a platform

platform ID
(mod_agreements/platform:pt_id)

UUID of Platform


platform name
(mod_agreements/platform:pt_name)

Name/label of the platform


Table name: erm_agreements_title_instance

A title_instance represents a resource (e.g. book or journal title) Every title_instance is an erm_resource
Each title_instance is linked to a work. Multiple title_instance records can link to a single work

title instance ID
(mod_agreements/title_instance:id)

ID of the title_instance

The ID of the title_instance will appear in the erm_resource table for the erm_resource record associated with the title_instance

associated work ID
(mod_agreements/title_instance:ti_work_fk)

The ID of the work to which the title_instance is linked


monograph published date
(mod_agreements/title_instance:ti_date_monograph_published)

For monographs (books), the date the monograph was first published in the media specified by the linked erm_resource subtype (typically “print” or “electronic”)

Equivalent to the KBART Phase II (NISO RP-9-2014) properties date_monograph_published_print and date_monograph_published_online (see https://groups.niso.org/apps/group_public/download.php/16900/RP-9-2014_KBART.pdf)

last name of first author if mono
(mod_agreements/title_instance:ti_first_author)

For monographs (books), the last name of the book’s first author.

Equivalent to the KBART Phase II (NISO RP-9-2014) properties first_author (see https://groups.niso.org/apps/group_public/download.php/16900/RP-9-2014_KBART.pdf)

edition if mono
(mod_agreements/title_instance:ti_monograph_edition)

For monographs (books), edition of the book.

Equivalent to the KBART Phase II (NISO RP-9-2014) properties monograph_edition (see https://groups.niso.org/apps/group_public/download.php/16900/RP-9-2014_KBART.pdf)

volume if mono
(mod_agreements/title_instance:ti_monograph_volume)

For monographs (books), the volume number of the book

Equivalent to the KBART Phase II (NISO RP-9-2014) properties monograph_volume (see https://groups.niso.org/apps/group_public/download.php/16900/RP-9-2014_KBART.pdf)

last name of first editor if mono
(mod_agreements/title_instance:ti_first_editor)

For monographs (books), the last name of the book’s first editor.

Equivalent to the KBART Phase II (NISO RP-9-2014) properties first_editor (see https://groups.niso.org/apps/group_public/download.php/16900/RP-9-2014_KBART.pdf)

Table name: erm_agreements_work

A work is broadly equivalent to a BIBFRAME ‘work’ which is “the conceptual essence of the cataloged resource”. See https://www.loc.gov/bibframe/docs/bibframe2-model.html

A work is used to link together title_instances for a single conceptual work that has been made available in different forms (e.g. print and electronic)

A work can be related to 1 or more title instances

work ID
(mod_agreements/work:w_id)

UUID of Work


title
(mod_agreements/work:w_title)

Title of the work


Table name: erm_agreements_identifier_occurrence

identifier_occurrence allows title_instances to be linked to identifiers

record ID
(mod_agreements/identifier_occurrence:io_id)

UUID of identifier occurrence


title instance ID
(mod_agreements/identifier_occurrence:io_ti_fk)

ID of title_instance which is linked to the identifier


reference ID of status
(mod_agreements/identifier_occurrence:io_status)

ID of reference data value (mod_agreements.refdata_value.rdv_id) which describes the status of the identifier occurrence

In the current implementation this is always “approved” and not used for any functionality

identifier ID
(mod_agreements/identifier_occurrence:io_identifier_fk)

ID of the identifier which is ‘occurring’ against a title_instance


Table name:  erm_agreements_identifier

Each identifier has an identifier_namespace. Identifier values should be unique within a namespace

identifier ID
(mod_agreements/identifier:id_id)

UUID of identifier


identifier namespace ID
(mod_agreements/identifier:id_ns_fk)

ID of identifier namespace


identifier value
(mod_agreements/identifier:id_value)

Value of the identifier (i.e. the identifier string)


Table name: erm_agreements_identifier_namespace

Each identifier has an identifier_namespace. Identifier values should be unique within a namespace

identifier namespace ID
(mod_agreements/identifier_namespace:idns_id)

UUID of identifier_namespace


namespace value
(mod_agreements/identifier_namespace:idns_value)

Value of the namespace (i.e. the namespace name as a string)

e.g. ISSN

Table name: erm_agreements_coverage_statement

coverage_statements are used to indicate what parts of a serial published work are available via an erm_resource

Although coverage_statements can exist on any erm_resource, typically the coverage_statement on the package_content_item is the relevant one for reporting

coverage_statements can be overridden by holdings_coverage for an entitlement

An erm_resource can have zero to many coverage_statements

coverage statement ID
(mod_agreements/coverage_statement:cs_id)

UUID for coverage_statement


start issue (mod_agreements/coverage_statement:cs_start_issue)

Earliest issue of the resource available as part of this coverage


end issue
(mod_agreements/coverage_statement:cs_end_issue)

Latest issue of the resource available as part of this coverage


start volume
(mod_agreements/coverage_statement:cs_start_volume)

Earliest volume of the resource available as part of this coverage


end volume
(mod_agreements/coverage_statement:cs_end_volume)

Latest volume of the resource available as part of this coverage


resource ID
(mod_agreements/coverage_statement:cs_resource_fk)

The ID of the erm_resource to which this coverage_statement applies


start date
(mod_agreements/coverage_statement:cs_start_date)

Earliest date of publication of material from the resource available as part of this coverage

Note that this relates to the original publication date of available the material, not the date it is made available to the institution.

end date
(mod_agreements/coverage_statement:cs_end_date)

Latest date of publication of material from resource available as part of this coverage

Note that this relates to the original publication date of available the material, not the date it is made available to the institution.

Table name: erm_agreements_holdings_coverage

holdings_coverage are identical to coverage_statements in structure but apply to an entitlement rather than a resource. They are used to make a statement about what parts of a resource are available as part of an entitlement where that differs to the normal coverage stated for the resource

An example might be where the resource has been obtained through a consortium agreement, but the specific coverage an institution gets depends on when they joined the consortium. In this case an entitlement might require a holdings_coverage record to specify the coverage the institution gets access to which is different from the coverage_statement on the resource

An entitlement can have zero to many coverage_statements

holdings coverage ID
(mod_agreements/holdings_coverage:co_id)

UUID for holdings_coverage


start issue
(mod_agreements/holdings_coverage:co_start_issue)

Earliest issue of the resource linked to the entitlement available as part of this coverage


end issue
(mod_agreements/holdings_coverage:co_end_issue)

Latest issue of the resource linked to the entitlement available as part of this coverage


start volume
(mod_agreements/holdings_coverage:co_start_volume)

Earliest volume of the resource linked to the entitlement available as part of this coverage


end volume
(mod_agreements/holdings_coverage:co_end_volume)

Latest volume of the resource linked to the entitlement available as part of this coverage


entitlement ID
(mod_agreements/holdings_coverage:co_ent_fk)

The ID of the entitlement to which this holdings_coverage applies


start date
(mod_agreements/holdings_coverage:co_start_date)

Earliest date of publication of material from the resource linked to the entitlement available as part of this coverage

Note that this relates to the original publication date of available the material, not the date it is made available to the institution.

end date
(mod_agreements/holdings_coverage:co_end_date)

Latest date of publication of material from resource linked to the entitlemente available as part of this coverage

Note that this relates to the original publication date of available the material, not the date it is made available to the institution.

Table name: erm_agreements_refdata_value
refdata_values (reference data values) are used where specific values for a field can be chosen from a limited list of values. Reference data values are grouped by refdata categories (mod_agreement.refdata_category) which define the context in which the values are used

rdv_id
(mod_agreements/refdata_value:rdv_id)

ID for the refdata value

rdv_value
(mod_agreements/refdata_value:rdv_value)

A string value which is used to identify the refdata value The rdv_value stays constant whereas the rdv_label can be changed, so when building filters etc. for a specific reference data value, it is better to use the rdv_value rather than the rdv_label

rdv_owner
(mod_agreements/refdata_value:rdv_owner)

ID of the refdata category (mod_agreements.refdata_category.rdc_id) to which the rdv_value belongs

rdv_label
(mod_agreements/refdata_value:rdv_label)

A string label for the rdv_valueThis is the value usually displayed to users in the Folio UI
Table name: erm_agreements_refdata_category
refdata categories (mod_agreement.refdata_category) are used to group reference data values. Values from a specific category will be used as the list of valid values for some properties
rdc_id
(mod_agreements/refdata_category:rdc_id)
ID for the refdata category
rdc_description
(mod_agreements/refdata_category:rdc_description)
Text string identifying the refdata categoryThis string is used to describe the context in which values from the refdata category can be used
internal
(mod_agreements/refdata_category:internal)
Boolean flag indicating if the refdata category is an "internal" category or notThe list Refdata values (mod_agreements.refdata_value) belonging to an "internal" category cannot be modified via the UI except for their labels. This is used to ensure that refdata values that are required for system functionality are not edited (beyond the label) or removed.

Plus the data from the RM-Costs Cluster Prototype

For each row in mod_agreements/entitlement with an ent_authority equal to either of:

  • EKB-TITLE
  • EKB-PACKAGE

The description of the resource can be obtained from an eHoldings API using the value stored in ent_reference as follows:

  • If ent_authority = "EKB-TITLE", use mod-kb-ebsco-java/resources/{resourceId} using the value of ent_reference as the {resourceId}
  • If ent_authority = "EKB-PACKAGE", use mod-kb-ebsco-java/packages/{packageId} using the value of ent_reference as the {packageId}

Folio Attribute (Module/Path:Object)

Folio Data Element Description

Parameters/Query

Table Name: mod-kb-ebsco-java/resources/{resourceId}

eHoldings resource ID (mod-kb-ebsco-java/resources:id)

ID for the eHoldings resource.

This will be an identifier created from three parts, each part being an integer:

<provider id>-<package id>-<title id>

For example:

"Gale Cengage" (provider id = 18) provide a package "Academic ASAP" (package id = 34) which contains a title "Harvard Business Review" (title id = 32090)

  • the identifier for the resource "Harvard Business Review in the Academic ASAP package from Gale Cengage" is 18-34-32090


eHoldings resource name (mod-kb-ebsco-java/resources:attributes:name)Name of the resource (i.e. title of book/journal/etc.)
eHoldings resource publication type (mod-kb-ebsco-java/resources:attributes:publicationType)Type of resource (e.g. book/journal/...)
eHoldings resource identifier (mod-kb-ebsco-java/resources:attributes:identifiers{id})External identifier for the resource (e.g. an ISBN or ISSN)

*Repeatable field*

eHoldings resource identifier type (mod-kb-ebsco-java/resources:attributes:identifiers{type})The identifier type (e.g. ISBN/ISSN)
eHoldings resource identifier subtype (mod-kb-ebsco-java/resources:attributes:identifiers{subtype})The identifier subtype (e.g. Print/Electronic)
Table Name: mod-kb-ebsco-java/resources/{resourceId}

eHoldings package ID (mod-kb-ebsco-java/packages:id)

ID for the eHoldings package.

This will be an identifier created from two parts, each part being an integer:

<provider id>-<package id>

For example:

"Gale Cengage" (provider id = 18) provide a package "Academic ASAP" (package id = 34)

  • the identifier for the package "Academic ASAP from Gale Cengage" is 18-34


eHoldings package name (mod-kb-ebsco-java/packages:name)The name of the package
eHoldings package name (mod-kb-ebsco-java/packages:titleCount)The number of titles in the package
eHoldings package name (mod-kb-ebsco-java/packages:selectedCount)The number of titles in the package that have been marked as "selected" by the institution