Import Billing Schedules

The following tables provide information on the file formats for importing data entities. All data entities in Subscription Billing start with SB.

Tip: To save time and work more efficiently, use the sample import files (SB_ImportTemplates-YYYYMMDD.zip) that are provided with Subscription Billing installation download file. If a sample file does not exist, you can export the data entities.

When importing data entities from a Microsoft Excel file, set Skip staging to No. When data is imported with the staging, all Subscription Billing validations are performed on the data being imported. If any errors occur during import, you can review the staging data to check for data import issues.

Billing Schedule Header

• Target entity: BssiArcbBillingScheduleTableEntity
• Sample template: SB_ARCB_billing_schedule_header.xlsx

When importing data to update billing schedule header, note the following: 

• Charges are not rolled down to the lines within the billing schedule.
• The following data values are not updated: Billing schedule group, Billing schedule number, Customer account, Currency code, and End user account.

Billing Schedule Lines

• Target entity: BssiArcbBillingScheduleLineEntity
• Sample template: SB_ARCB_billing_schedule_line.xlsx

When importing data to update billing schedule lines, only the following values are updated: 

• Quantity
• Unit price (only for lines that have the Pricing method set to Flat)
• Alignment date
• Billing end date
• Auto renew
• Main deferral account

Billing Schedule Lines (Update)

Use the data entity for updating the quantity or unit price of child items of a revenue split in a billing schedule.

• Data entity: BssiArcbBillingScheduleLineRevenueSplitUpdate
• Sample template: SB_ARCB_billing_schedule_line_update.xlsx

The format for the required data is as follows: 

Notes:

• When importing data, ChildQuantity and net amount must both be the same sign (positive or negative). Review the following example.

• You can remove the ChildQuantity or ChildUnitPrice column, but not both. When the column is not in the import file, it is assumed that the value for all lines for the column is zero.
• Only lines that use the flat pricing method are imported.

Billing Usage

Target entity: BssiArcbBillingDetailLine

Unbilled Revenue Transaction Line

Target entity: BssiArcbUnbilledRevenueTransactionLineEntity

Escalation Lines

• Target entity: BssiArcbEscalationTableEntity
• Sample template: SB_ARCB_escalation_line.xlsx

Data can be imported to update the escalation and discount values.

Put Line on Hold

The following data entity allows holds to be put on billing schedules lines.

• Target entity: BssiArcbHoldLine

Remove Hold from Line

The following data entity allows holds to be removed from billing schedules lines.

• Target entity: BssiArcbUnholdLine

Mass Termination Header

The following data entity is also referred to as the termination setup for the termination process via the data entity. The user will need to import this data entity first, and then use the BssiArcbMassTerminationLineEntity entity to terminate the lines. The BssiArcbMassTerminationLineEntity entity should refer to the same TERMINATIONPROCESSID imported in the header entity. These values are saved temporarily and cleared after each import process.

• Target entity: BssiArcbMassTerminationTableEntity
• Sample template: SB_ARCB_mass_termination_table.xlsx

Mass Termination Line

The following data entity allows lines within one or more billing schedules to be terminated. These values are saved temporarily and cleared after each import process.

Target entity: BssiArcbMassTerminationLineEntity

Sample template: SB_ARCB_mass_termination_line.xlsx

Only whole billing schedules or lines that have an Active status can be terminated. During the termination process, the following checks occur: 

• Billing schedule number (if set)
• Customer
• Item number (if set)
• Termination reason code
The customer and item number must match with at least one billing schedule.

View example.

The following billing schedules exist: 

Billing Schedule Number	Customer	Item Number
USMF-00100	US-001	Item 1
USMF-00100	US-001	Item 2
USMF-00101	US-001	Item 1
USMF-00101	US-001	Item 3
USMF-00102	US-001	Item 3
USMF-00102	US-001	Item 5

Import 1: Only the customer number and item number are set. No billing schedule number is specified.

Customer	Item Number
US-001	Item 1

When file with this line is imported, only the lines in red are terminated. These lines match the customer number and the item number.

Billing Schedule Number	Customer	Item Number
USMF-00100	US-001	Item 1
USMF-00100	US-001	Item 2
USMF-00101	US-001	Item 1
USMF-00101	US-001	Item 3
USMF-00102	US-001	Item 3
USMF-00102	US-001	Item 5

Import 2: The import file has the following data.

Customer	Item Number
US-001	Item 1
US-001	Item 2

When file with this line is imported, the whole billing schedule USMF-00100 is terminated because all items within that billing schedule are terminated.

Billing Schedule Number	Customer	Item Number
USMF-00100	US-001	Item 1
USMF-00100	US-001	Item 2
USMF-00101	US-001	Item 1
USMF-00101	US-001	Item 3
USMF-00102	US-001	Item 3
USMF-00102	US-001	Item 5

Mass Termination Composite

Target entity: BssiArcbMassTerminationLineEntity

Sample template: SB_ARCB_Mass_termination_composite.xml

View example.

Select Copy to copy the sample to the clipboard.

Note: Use the FireFox web browser when selecting Copy to ensure that the tab spaces in the code are copied correctly. With other web browsers, the tab spaces are copied as non-breaking spaces, which can cause the code to fail.

Copy

Mass Termination Composite example

<?xml version="1.0" encoding="utf-8"?>
<Document>
    <BSSIARCBMASSTERMINATIONTABLEENTITY>
        <TERMINATIONPROCESSID>Term200</TERMINATIONPROCESSID>
        <CONSOLIDATEBYCUSTOMER>0</CONSOLIDATEBYCUSTOMER>
        <DEFERRALADJUSTMENTMETHOD>0</DEFERRALADJUSTMENTMETHOD>
        <INVOICEDATE>2019-10-17T00:00:00</INVOICEDATE>
        <INVOICETRANSACTIONTYPE>0</INVOICETRANSACTIONTYPE>
        <ISSUECREDIT>1</ISSUECREDIT>
        <POSTINGOPTION>0</POSTINGOPTION>
        <PRORATEDAILY>1</PRORATEDAILY>
        <REASONCODE>SUBC</REASONCODE>
        <TERMINATIONDATE>2019-02-14T00:00:00</TERMINATIONDATE>
        <BSSIARCBMASSTERMINATIONLINEENTITY>
            <TERMINATIONPROCESSID>Term200</TERMINATIONPROCESSID>
            <CUSTOMERACCOUNT />
            <ITEM>1000</ITEM>
            <BILLINGSCHEDULENUMBER>USMF-00754</BILLINGSCHEDULENUMBER>
            <LINENUM>0.0000000000000000</LINENUM>
        </BSSIARCBMASSTERMINATIONLINEENTITY>
    </BSSIARCBMASSTERMINATIONTABLEENTITY>
</Document>

Support and Renewal Entities

Review the information for the data entities when you import support and renewal data.

Data Entities - Sales Line Join

For the sales line entity (SalesOrderLineV2Entity), the primary key is InventoryLotId, which maps to InventTransId on the table. This mapping is used to update existing sales order lines.

To be able to correctly import the support and renewal data, the InventoryLotId will be added on the data entity. With this addition, the SalesId and LineNumber as the primary keys on the table. These primary keys are automatically populated from the sales line determined by InventoryLotId.

Optionally, when importing data, InventoryLotId can be left empty, with the SalesId and LineNumber containing values. If all three keys have values, InventoryLotId takes precedence over SalesId and LineNumber.

Data Entities - Line without Header

When support and renewal lines are imported without any header information, the header values from the corresponding Support and Renewal Process page and billing schedule are used.

Data Entities - Line Fields

Any support and renewal line data that is available in the page for must be provided for the data import.

• The value for amount or percentage is set at the line level. For example, the support and renewal level is percentage for a support item, the amount value must be zero (0) or empty and the percentage value is required.
• Optionally, the support and renewal level can be left empty. In this case, only one of the amount or percentage requires a value.
• On the Advanced Recurring Contract Billing Parameters page, the Unique schedule type option is Customer: When the billing schedule number is empty, it is automatically updated when the data is imported.

Support and Renewal Header

Target entity: BssiArcbSupportRenewalProcessTableEntity

Sample template: SB_ARCB_support_and_renewal_header.xlsx

Support and Renewal Lines

Target entity: BssiArcbSupportRenewalProcessLineEntity

Sample template: SB_ARCB_support_and_renewal_lines.xlsx

Sales Order Lines

Target entity: SalesOrderLineV2Entity

Sample template: Sales_order_lines_V2.xlsx

Sales Line Revenue Split (Update)

Target entity: BssiMeraSalesLineRevenueSplitEntity

Sample template: SB_MERA_sales_line_revenue_split_update.xlsx

Use the data entity for updating the financial dimensions, quantity, or unit price of revenue split child items in a sales order. When importing data, keep in mind the following: 

• ChildQuantity and net amount must both be the same sign (positive or negative).
• Only one of the ChildQuantity or ChildUnitPrice columns can be removed, not both. When the column is not in the import file, the value for all lines of the column is zero (0).

General Journal with Deferrals

Target entity:

Sample template: General_journal_with_SB_ARED_Deferrals.xlsx

See also

Import Billing Schedule Data