Service Point Consumption Extracts

Service Point Consumption extracts are measurement data extracts for a set of service points assigned to an entity group.

Configuration options for these extracts include:

Type of measurement data to extract (interval or scalar)
Unique combinations of unit of measure (UOM), time of use (TOU), and service quantity identifier (SQI) associated with the usage subscriptions
Date and time parameters and frequency of extract
Extract file naming and location

Service Point Consumption extracts are created via Requests. Service Point Consumption extracts are exported to a file in a specified file location, either on an application server (in an on-premises implementation) or an Oracle Cloud Infrastructure Object Storage location. External URL attachments linking to extract files are created on the Request for each extract.

This section describes the configuration needed for these types of extracts.

Note: Service Point Consumption Extracts are only available in cloud (SaaS) implementations.

Service Point Consumption Extract Entity Groups

Entity groups used with Service Point Consumption Extracts must be created using the Service Point Measurement Extract Group (D1-SPMeasurementExtractGroup) business object. These entity groups will have a Group Category of "SP Measurement Extract".

Refer to Understanding Entity Groups for more information.

Service Point Consumption Extract Request Types and Requests

Configuration for service point consumption extracts are defined via Requests. See Understanding Requests in the Application Framework Administrative User Guide for more information about creating these records.

Service point consumption extract request types should be created using the SP Measurement Extract Request Type (D1-SPMsrmtExtractRequestType) business object, and define the following parameters:

The Device Identifier Type (such as Asset Number, Badge Number, and so on) to include in the extract file
File parameters, including:
- File Name: String used as the prefix for extract file names
- File Location: The location where extract files will be saved (see External File Storage in the Application Framework Administrative User Guide)
- Child Folder: An optional child folder in the file location
- Record Limit: A limit on the number of records to include in extract files

Service point consumption extract requests should be created using the SP Measurement Extract Request (D1-SPMsrmtExtractRequest) business object, and define the following parameters:

Start/End Date/Time: The date and time range for the extract
Entity Group: The entity to which the service points for which consumption is extracted are assigned
UOM/TOU/SQI: The type of measurement data extracted, filtered based on the Service Type for the selected Entity Group
Device Identifier Type: The device ID type to include in the extract file (such as Asset Number, Badge Number, and so on)
Consumption Type: The type of consumption (Interval or Scalar) for extract
Target Interval Size: The interval size for the extract, applicable only if Consumption Type is Interval. Note that the interval size must be larger than the SPI of the measurement data
Batch Number: The batch number for the batch process used to perform the extract. This is populated by the batch process that performs the extract.

Extract files for Service Point Consumption Requests are created as external URL attachments on the Request that processed the extract.

Request Processing

Creating a request starts batch job to extract the usage to Oracle Object Storage or a file directory in a CSV format. This CSV format can easily be imported into Excel or other software.

When a request is created, it starts in the Pending state, and an Enter algorithm creates a deferred request and the SP Measurement Extract Request Monitor (D1-SPMRQ) batch process moves the request into the Extract state, where the Perform SP Measurement Extract (D1PMSPMSRMEX) algorithm performs the extract process.

The extract process creates an "External URL" attachment in which the attachment contains a URL to the extract file in the location specified in the Request Type. Attachments created for a request can be accessed via the Request Attachments zone in the Request portal.

File Naming and Formats (SECTION)

Extract file names use the following format:

<file location><file name>-<request id>-<start dttm>-<end dttm>_<file number>

where:

<file location> is the File Location specified in the Request Type
<file name> is the File Name specified in the Request Type
<request id> is the ID of the Request
<start dttm> is the start date and time for the request, displayed as YYYYMMDDHH24MISS
<end dttm> is the end date and time for the request, displayed as YYYYMMDDHH24MISS
<file number> is the file number for the extract file. This is a 4-digit number with padded leading zeros. Multiple files are created if a Record Limit is specified on the Request Type and the records extracted exceeds the limit.

For example if the File Location was set to "file-storage://CM-CloudStorage/sp-measurement-extracts/" and the File Name was set to "sp-kWh-extract" with an extract date/time of 2026-01-01 00:00:00 to 2026-02-01 00:00:00 then the first file would be named:

Scalar Record Format

file-storage://CM-CloudStorage/sp-measurement-extracts/sp-kWh-extract-10392739850273-20260101000000-20260102000000_0001.csv.gz

Scalar records will only have one measurement per row. The Start Date/Time isn't the start of the day but rather the start of the measurement. Measurements are extracted based on when they end, so the first measurement chronologically for each measuring component may cover a period that falls before the extract time period. Extract data comes from the following tables and columns:


Header Field	Table	Column	Notes
Start Date/Time	D1_MSRMT	PREV_MSRMT_DTTM	Converted from the time zone defined in Installation Options to the service point time zone
Customer Name	D1_CONTACT_NAME	NAME_VALUE	This is for the "Main" usage subscription Contact and for the Primary name
Address	D1_SP	ADDRESS1 + ADDRESS2 + ADDRESS3 + ADDRESS4	ADDRESS2 through 4 will be trimmed
Service Point	D1_SP	D1_SP_ID
Local Time Zone	CI_TIME_ZONE	F1_TIMEZONE_NAME	The time zone from the service point
Measuring Component	D1_MEASR_COMP	MEASR_COMP_ID
Meter Number	D1_DVC_IDENTIFIER	ID_VALUE	Based on the device identifier type to use (such as badge number, serial number, and so on) from the Request Type
How to Use Flag	D1_MEASR_COMP	MEASR_COMP_USAGE_FLG
UOM	D1_MC_TYPE_VALUE	D1_UOM_CD	Unit of Measure (Description)
TOU	D1_MC_TYPE_VALUE	D1_TOU_CD	Time of Use (Description)
SQI	D1_MC_TYPE_VALUE	D1_SQI_CD	Service Quantity Identifier (Description)
Date/Time	D1_MSRMT	MSRMT_DTTM	Converted from the time zone defined in Installation Options to the service point time zone
Measurement	D1_MSRMT	MSRMT_VAL	The appropriate Measurement Value field based on the Measuring Component Type's Value Identifier that was selected using the UOM/TOU/SQI. This is the main measurement Measurement Value the majority of the time but for Gas or Water, derived values from the other measurement value fields may be requested.
Condition	D1_MSRMT	MSRMT_COND_FLG

Interval Record Format

Interval records will have multiple measurements per row. Extract data comes from the following tables and columns:


Header Field	Table	Column	Notes
Start Date/Time	D1_INT_SNAP_MAP	D1_LOCAL_DT	Converted from the time zone defined in Installation Options to the service point time zone
Customer Name	D1_CONTACT_NAME	NAME_VALUE	This is for the "Main" usage subscription Contact and for the Primary name
Address	D1_SP	ADDRESS1 + ADDRESS2 + ADDRESS3 + ADDRESS4	ADDRESS2 through 4 will be trimmed
Service Point	D1_SP	D1_SP_ID
Local Time Zone	CI_TIME_ZONE	F1_TIMEZONE_NAME	The time zone from the service point
Measuring Component	D1_MEASR_COMP	MEASR_COMP_ID
Meter Number	D1_DVC_IDENTIFIER	ID_VALUE	Based on the device identifier type to use (such as badge number, serial number, and so on) from the Request Type
How to Use Flag	D1_MEASR_COMP	MEASR_COMP_USAGE_FLG
UOM	D1_MC_TYPE_VALUE	D1_UOM_CD	Unit of Measure (Description)
TOU	D1_MC_TYPE_VALUE	D1_TOU_CD	Time of Use (Description)
SQI	D1_MC_TYPE_VALUE	D1_SQI_CD	Service Quantity Identifier (Description)
Total	D1_MSRMT	MSRMT_VAL<N>	The sum of the measurements for the Start Date/Time for the Measuring Component. The appropriate Measurement field will be used based on the Measuring Component Type's Value Identifier that was selected using the UOM/TOU/SQI.
Time<N>	D1_MSRMT	MSRMT_DTTM	This is date and time of each interval, calculated as follows: Start Date/Time (D1_LOCAL_DT) + (interval number * SPI). SPI is based on the Target Interval Size if it was provided in the Request or the Interval Size of the Measuring Component Type if not.
Measurement<N>	D1_MSRMT	MSRMT_VAL<N>	The appropriate Measurement Value field based on the Measuring Component Type's Value Identifier that was selected using the UOM/TOU/SQI. This is the main measurement Measurement Value the majority of the time but for Gas or Water, derived values from the other measurement value fields may be requested.
Condition<N>	D1_MSRMT	MSRMT_COND_FLG	This will either be the the Minimum (MIN) or Maximum (MAX) Measurement Condition Flag based on the configuration of the Perform SP Measurement Extract (D1PMSPMSRMEX) algorithm. The default is to use the MIN. Note: MIN or MAX is only important for Measuring Components that have a lower SPI than the Target Interval Size. If no Target Interval Size is provided then data is extracted based on the Interval Size of the of the Measuring Component Type.

Interval data is values are returned as a single string of comma-separated values that are sub delimited by tildas (~). For example:

3.732~1~501000,5.804~2~501000,6.432~3~501000,5.572~4~501000,7.384~5~501000,7.358~6~501000,4.67772~7~501000,7.8132~8~501000,7.83564~9~501000,6.14856~10~501000,8.0988~11~501000,8.16816~12~501000,4.63186~13~501000,7.7366~14~501000,7.75882~15~501000,6.08828~16~501000,8.0194~17~501000,8.08808~18~501000,3.808~19~501000,4.448~20~501000,7.69~21~501000,6.94~22~501000,7.964~23~501000,7.316~24~501000

Customizing Extract Output

The Perform SP Measurement Extract (D1PMSPMSRMEX) algorithm uses the Extract Measurements for SP Entity Group Library (D1EMSPEGLib) script to return queries, format headers, and format records. This script implements three high level types of methods:

Method to return the query that will extract the measurement data and master data details
1. This takes in the same fields as are available in the plug-in driven script process data plug-in spot
2. This returns a prepared statement
3. There is one script for Interval and one script for Scalar since the queries are very different
Method to populate a header row for the CSV file
1. For scalar this is always the same fields since we only ever extract one row at a time
2. For interval this creates the measurement fields according to the maximum SPI or the target SPI and whether or not the extract period covers a DST transition
Method to process the returned data into the formatted row
1. This takes in the results from executing the queries
2. This formats the data into what will be streamed into the file and return that as a string

Use the Script portal to view this script in more detail.

To customize the data extracted via this process (for example to add an additional column), a custom (CM) version of the Groovy Library script can created. This involves the following steps:

Duplicate the Extract Measurements for SP Entity Group Library (D1EMSPEGLib) script.
Adjust the generate query method(s) to retrieve the additional information.
Adjust the generate header method(s) to return the new field for the header.
Adjust the result formatting method(s) to place the new data into the result string according to their requirements.
Add this new CM script as the value for the Override Extract Groovy Library parameter on the the Perform SP Measurement Extract (D1PMSPMSRMEX) algorithm.