File Requirements

Important: An extensive product portfolio and wide distribution chain exceeding 1000 resellers may affect the reseller synchronization performance. If this is your case, please contact your Account Manager to check if there is a solution.

This topic describes sheets and columns presented in the Excel file that is imported and exported using PLM, and requirements for the file.

• It must be a valid Excel (*.xslx) file.

• It must include products and plans that belong to only one product line.

• The names of columns in the same sheet must be unique. If there are two columns with the same name, you will not be able to upload the file.

  • It has the sheets and columns listed below.

    Note: Sheet and column order is ignored during file processing. After importing an Excel file with the initial configuration, you can edit only a limited list of parameters by importing another file. These parameters are marked as Editable in the tables below. Please ensure that the uploaded configuration fits your business needs. To modify other fields, the corresponding objects need to be first removed from the Control Panel and the configuration file needs to be imported again.
    

    Structure Requirements

    This sheet is optional. It is added automatically during the file export and contains supplementary information that helps you to create the correct file for import. It describes all sheets and columns stating their mandatory and default settings.

    Resources

    This sheet is mandatory. It outlines a list of resources (products), which will be used for plan creation. It is not used for creation of new products.

    Column Name	Mandatory Column	Default Value (if an optional field is empty or not provided). No default value means that the value will be empty for this parameter	Possible Value	Comments	Editable
    Name_EN	Yes				No
    Name_XX	No	Name_EN (the value from the EN locale will be filled in with other locales if columns Name_XX are empty)		A product or resource name for other locales. XX is a language code (for example, DE, FR or IT). Note: This influences a Product name in PLM, and a Resource name, Resource Rate, and Resource Rate Period in the classic PCP.	Yes
    Description_EN	Yes				Yes
    Description_XX	No	Description_EN (the value from the EN locale will be filled in with other locales if columns Description_XX are empty)		A product or resource description for other locales. XX is a language code (for example, DE, FR or IT).	Yes
    ResourceCategory	Yes			If a resource category does not exist and it is required, you need to add a ResourceCategories sheet (see below).	No
    UOM	No		Same as in the list of existing UOMs on a particular CloudBlue Commerce instance. For example, Item.	Add this column and set a value in it if the current value needs to be changed. It is case-sensitive.	Yes
    Measurable	No		True / False	Add this column and set a value in it if the current value needs to be changed.	Yes

    ServicePlans

    This sheet is mandatory.

    Note: During plan creation, PLM creates Resource Rates, which can be seen in the classic panel. For every Resource Rate, PLM automatically creates a Resource Rate Period for each subscription period in a plan, even if there is only one subscription period in the plan.

    Column Name	Mandatory Column	Default Value (if an optional field is empty or not provided). No default value means that the value will be empty for this parameter	Possible Value	Comments	Editable
    OldName_(1...N)	No			It is used to change an existing plan name. To rename a plan: The existing name (to be changed) must be specified in the OldName_N column, The new name must be specified instead of the existing name (to be changed) in the Name_EN field, This new name also must be specified instead of the existing name (to be changed) in all other sheets of the file (PlanPeriods, ResourceRates, CancelationPolicies, DownsizePolicies, and OpUnitServicePlans), Optionally, names in other locales and descriptions may be updated. Several OldName_N columns can be used to track a renaming history. Recommendation for the GCSPdM team: track the whole history of plan renaming in the OldName_N columns so that providers can rename plans at any time with the latest PPR no matter how many PPRs (months) they have skipped up until then.	No
    Name_EN	Yes			Renaming works only together with the OldName_N column.	Yes
    Name_XX	No	Name_EN		A plan name for other locales. XX is a language code (for example, DE, FR or IT). Renaming works only together with the OldName_N column.	Yes
    Description_EN	Yes			Renaming works only together with the OldName_N column.	Yes
    Description_XX	No	Description_EN		A plan description for other locales. XX is a language code (for example, DE, FR or IT). Renaming works only together with the OldName_N column.	Yes
    PlanCategory	Yes			It will be created if it does not exist.	Yes
    ServiceTerms	Yes			Service Terms must be created in advance.	Yes
    SalesCategory_(1...N)	No			Name of the existing or a new category. If a new category is specified, it must be defined in the SalesCategories sheet.	No
    BillingPeriodDuration	Yes		Integer number		No
    BillingPeriodType	Yes		Month(s) / Year(s)		No
    AlignBillingOrderWithStatementDay	No	False	True / False		No
    NewDayOfStatement	No		Integer number	By default, it is empty. This means that it is defined by Customer Class settings.	No
    AlignSalesOrderWithStatementDay	No	False	True / False	This can be set to True only if AlignBillingOrderWithStatementDay is True.	No
    FirstBillingPeriodForFree	No	False	True / False		No
    PricePeriod	Yes	Billing Period	Billing Period / Month	Set Recurring Price per billing period or month.	Yes
    RecurringType	Yes		Charge Before Billing Period Charge After Billing Period Charge Before Subscription Period Use External Rating One Time	Billing Model	No
    AutoRenew	No	Before Expiration	Disabled On Last Statement Day Before Expiration		Yes
    RenewOrderInterval	No	1	Integer number	Renewal Point (days) Works only if AutoRenew = Before Expiration.	Yes
    AutoRenewPlan	No			A name of a plan that is configured in upgrade paths must be specified here. An empty value means that the same plan will be automatically renewed.	Yes
    AutoRenewPeriod	No		Integer number Default	Works only if and must be specified when AutoRenew = On Last Statement Day or Before Expiration. AutoRenewPeriod with AutoRenewPeriodType define a subscription period from the current or another plan for renewal.	Yes
    AutoRenewPeriodType	No		Month(s) Year(s) Default	The default period can be specified only if AutoRenewPlan is empty (a plan will not be changed during auto-renewal). This means that a subscription will be renewed for the same subscription period.	Yes
    AllowScheduledChanges	No	False	True / False	This enables the platform capability to schedule plan changes (upsize, downsize, upgrade, downgrade, and the billing period change) at the renewal date. This must be used only if the vendor of a product allows these changes as well.	Yes
    WelcomeNotificationTemplate	No				No
    ExpirationNotificationTemplate	No			If a subscription expiration template is defined, subscription expiration notifications are sent only on the expiration day and for every subscription that expires. It does not matter whether a renewal order is placed or a customer account is active.	No
    ProcessByRatingEngine	No	False	True / False	Only False to True editing is available. Disabling of the new RE for a plan is unsupported by design. For additional information, refer to Setting 'Revenue Only' Rating Mode for Service Plans.	Yes
    BillingPeriodAlignment	No	Subscription Period Start	Subscription Period Start / Subscription Period Expiration	This is a part of co-terming settings. Refer to this section to learn more about co-terming.	Yes
    CotermingPossibilities	No	Restricted	Restricted / Allowed / Fixed Date Provided by Vendor	Defines the co-terming mode. Refer to this section to learn more about co-terming.	Yes
    ExpirationDateAlignedWithEndOfMonth	No	False	True / False	Allows aligning subscription expiration date with the end of month. This is a part of co-terming settings. Refer to this section to learn more about co-terming.	Yes
    ExpirationDateAlignedWithSubscription	No	False	True / False	Allows selecting a subscription to co-term with. This is a part of co-terming settings. Refer to this section to learn more about co-terming.	Yes
    BillingAlignmentResellerRedefineAllowed	No	False	True / False	Enables alignment of billing dates to a specific day of month defined for the reseller account. Learn more about calendar billing in this section.	Yes
    UpgradePath_(1...N)	No			It is possible to define several upgrade paths.	No
    SubscriptionStartDateAfterUpgrade	No	Date of upgrade	Date of Upgrade Start Date of Current Subscription Period	It is the same for all upgrade paths.	No
    OpUnit_<CountryCode>	No		True / False	Used to share a plan with operating units (CountryCode = value of InitWizardCountryCode account custom attribute). If your OpUnits have customized prices, T&C or sales categories, disable propagation of these entities as described in this article.	No
    ResellerGroupName_(1...N)	No			It is used to share a plan with direct resellers. Specify a RESELLERGATE service template name.	No
    Published	No		True / False	Used to publish or unpublish a plan for all accounts (operating units and resellers configured under them) with whom the plan is shared. If for some operating unit the Published parameter must be different, its value can be overridden in the OpUnitServicePlans column. If empty, no changes will be made.	Yes
    VendorTimezone	No		UTC / Default	If not specified, the default time zone that is configured for CloudBlue Commerce installation is used.	No
    TermsConditions_(1...N)	No			It is possible to define several T&C.	No

    CompositeResources

    This sheet is optional.

    Column Name	Mandatory Column	Default Value (if an optional field is empty or not provided). No default value means that the value will be empty for this parameter	Possible Value	Comments	Editable
    CompositeResourceName_EN	Yes				No
    CompositeResourceName_XX	No	Name_EN (the value from the EN locale will be filled in with other locales if columns Name_XX are empty)		A product or resource name for other locales. XX is a language code (for example, DE, FR or IT). Note: This influences a Product name in PLM, and a Resource name, Resource Rate, and Resource Rate Period in the classic PCP.	Yes
    CompositeResourceDescription_EN	Yes			A product or resource description for EN locale.	Yes
    CompositeResourceDescription_XX	No	Description_EN (the value from the EN locale will be filled in with other locales if columns Description_XX are empty)		A product or resource description for other locales. XX is a language code (for example, DE, FR or IT).	Yes
    IncludedResourceName_1	Yes			The name of the resource (product) #1 included into the composite resource (product). At least 1 included resource must be defined for composite resource creation: both name and amount.	No
    IncludedResourceAmount_1	Yes			The amount of the first resource (product) #1 included into composite resource (product). At least 1 included resource must be defined for composite resource creation: both name and amount.	No
    IncludedResourceName_X	No			The name of the resource (product) #2..X included into the composite resource (product).	No
    IncludedResourceAmount_X	No			The amount of the resource (product) #2..X included into the composite resource (product).	No
    ResourceCategory	Yes			If a resource category does not exist and it is required, you need to add a ResourceCategories sheet (see below).	No
    UOM	No		Same as in the list of existing UOMs on a particular CloudBlue Commerce instance. For example, Item.	Add this column and set a value in it if the current value needs to be changed. It is case-sensitive.	Yes
    MPN	Yes			Manufacturer part number	Yes

    PlanSwitchPaths

    Column Name	Mandatory Column	Default Value (if an optional field is empty or not provided). No default value means that the value will be empty for this parameter	Possible Value	Comments	Editable
    FromPlan	Yes			The source plan name	No
    ToPlan	Yes			The destination plan name	No
    ImmediateSwitchAllowed	Yes	True	True / False	Defines if immediate switch is allowed	Yes
    SubsStartDateAfterSwitch	Yes	Date of switch	Date of switch / Start Date of Current Subscription Period	Defines the subscription start date after switch	Yes
    SubsPeriodChange	Yes		Not allowed / Any available period / Same or longer period	Defines if duration change is allowed, and if allowed to which period	Yes
    UpsizeAllowed	Yes	None	True / False	Defines if upsize is allowed. To manage upsize policies, the UpgradeResourceMapping sheet must be added.	Yes
    DownsizeAllowed	Yes		True / False	Defines if downsize is allowed. To manage downsize policies, the UpgradeResourceMapping setting must be added.	Yes
    PartialSwitchAllowed	No	False	True / False	Defines if it is allowed to switch a portion of seats to higher grade SKU	Yes
    RemoveSwitchPath	No	False	True / False	Allows to remove obsolete switch paths	Yes

    PlanPeriods

    This sheet is mandatory.

    Column Name	Mandatory Column	Default Value (if an optional field is empty or not provided). No default value means that the value will be empty for this parameter	Possible Value	Comments	Editable
    ServicePlan	Yes				No
    Period	Yes		Integer number	This parameter together with PeriodType defines subscription period duration.	No
    PeriodType	Yes		Month(s) / Year(s)		No
    Trial	No	False	True / False		No
    AfterRefundPeriod	No	Refund Prorated Recurring Fee	No Refund Refund Prorated Recurring Fee		No
    CancellationType	No	None	None Plan and Resource Rates until Expiration Date Custom	For the RE microservice, only these two values are supported: None Plan and Resource Rates until Expiration Date If a cancellation policy is defined, it will be used instead.	No
    CancellationFeeValue	No		Integer number	Works only for CancellationType = Custom	No
    Active	No	True	True / False	Used to define whether a subscription period is active. It can be changed for existing subscription periods.	Yes
    FullRefundPeriod	No	0	Integer number		No

    ResourceRates

    This sheet is mandatory.

    Column Name	Mandatory Column	Default Value (if an optional field is empty or not provided). No default value means that the value will be empty for this parameter	Possible Value	Comments	Editable
    ServicePlan	Yes				No
    Resource	Yes				No
    IncUnits	Yes		Integer number or floating point number (precision =2) -1 for Unlimited		Yes
    MinUnits	Yes		Integer number or floating point number (precision =2) -1 for Unlimited		Yes
    MaxUnits	Yes		Integer number or floating point number (precision =2) -1 for Unlimited		Yes
    Measurable	No	False	True / False		Yes
    AllowModificationInTrial	No	False	True / False		No
    ShowZeroRecurringFeeInOrder	No	False	True / False		Yes
    ShowZeroSetupFeeInOrder	No	False	True / False		Yes
    ShowZeroOveruseFeeInOrder	No	False	True / False		Yes
    SetupFeePerUnit	No	True	True / False		Yes
    RecurringFeePerUnit	No	True	True / False		Yes

    CancellationPolicies

    This sheet is optional. It is used to define a more sophisticated policy when the FullRefundPeriod and AfterRefundPeriod settings in the PlanPeriods sheet are not enough. For example, Office NCE subscriptions. For additional information, refer to Cancellation and Downsize Policy.

    Important:
    • This works only with the new Rating Engine (RE) enabled (ServicePlans > ProcessByRatingEngine = True).
    • After uploading a file with updated policy settings, all the previous settings will be overridden.
    • You need to either specify actions applicable to the First and Next Subscription Periods, or choose All Subscription Periods in the configuration file.
    • When defining a cancellation policy, you may specify periods and rules to be applied during these periods. From the cancellation allowance point of view, the whole subscription period is divided into the following periods and in the following sequence:
        1. The period when a cancellation is allowed and a full refund is provided.
        2. The period when a cancellation is allowed and a prorated refund is provided.
        3. The remaining period during which a cancellation may be either prohibited or allowed with no refund or cancellation penalty.

    If the policy is defined here, these values in the PlanPeriods sheet are not used by RE:

    • FullRefundPeriod,

    • AfterRefundPeriod,

    • CancellationType,

    • CancellationFeeValue.

    Column Name	Mandatory Column	Default Value (if an optional field is empty or not provided). No default value means that the value will be empty for this parameter	Possible Value	Comments	Editable
    ServicePlan	Yes		ServicePlans > Name_EN		No
    Period	Yes		Integer number	Periods from the PlanPeriods sheet.	No
    PeriodType	Yes		Month(s) / Year(s)		No
    Trial	Yes		True / False		No
    Action	Yes		Full Refund Prorated Refund No Refund Cancellation Penalty Prohibited	Full Refund: A cancellation is allowed with a full refund. Prorated Refund: A cancellation is allowed with a prorated refund. No Refund: A cancellation is allowed without a refund. Cancellation Penalty: A cancellation is allowed. However, a cancellation penalty is charged for a period from the next billing date to the subscription expiration date. The period for a cancellation with a penalty must follow full or prorated refund periods. Prohibited: A cancellation is prohibited. Note: - If the full and prorated refund periods do not cover the whole subscription period, a cancellation will be prohibited for the remaining days. - If you need to prohibit a cancellation for a whole subscription period, use the combination of the Action = Prohibited with ActionPeriod = -1.	No
    ActionPeriod	Yes		Integer number	A number of days starting from the beginning of a subscription period. The -1 value means a whole subscription period. When calculating the remaining period, the general period calculation algorithm is used. Learn more about this in the Period Calculation Algorithm section.	No
    ActionPeriodTimezone	Yes		Customer From Plan	Defines the time zone in which CloudBlue Commerce must count days specified in ActionPeriod. Customer: A customer's time zone. From Plan: A time zone configured in a service plan.	No
    ApplicableTo	Yes		First Subscription Period Next Subscription Periods All Subscription Periods	First Subscription Period: The policy is applied after a sales order. Next Subscription Periods: The policy is applied after a renewal order. All Subscription Periods: The policy is applied in the same way for all periods. Important: You need to either specify rules applicable to the First and Next Subscription Periods, or choose All Subscription Periods in the configuration file.	No

    DownsizePolicies

    This sheet is optional. It is used to define a more sophisticated policy. For example, Office NCE subscriptions. For additional information, refer to Cancellation and Downsize Policy.

    Important:
    • This works only with the new Rating Engine (RE) enabled (ServicePlans > ProcessByRatingEngine = True).
    • After uploading a file with updated policy settings, all the previous settings will be overridden.
    • You need to either specify actions applicable to the First and Next Subscription Periods, or choose All Subscription Periods in the configuration file.
    • When defining a downsize policy, you may specify periods and rules to be applied during these periods. From the downsize allowance point of view, the whole subscription period is divided into the following periods and in the following sequence:
        1. The period when a downsize is allowed and a full refund is provided.
        2. The period when a downsize is allowed and a prorated refund is provided.
        3. The remaining period during which a downsize may be either prohibited or allowed but only after previously made upsize.
    

    Column Name	Mandatory Column	Default Value (if an optional field is empty or not provided). No default value means that the value will be empty for this parameter	Possible Value	Comments	Editable
    ServicePlan	Yes		ServicePlans > Name_EN		No
    Period	Yes		Integer number	Periods from the PlanPeriods sheet.	No
    PeriodType	Yes		Month(s) / Year(s)		No
    Trial	Yes		True / False		No
    Action	Yes		Full Refund Prorated Refund Not Allowed Except For After Upsize Prohibited No Refund	Full Refund: Downsizing is allowed with a full refund. Prorated Refund: Downsizing is allowed with a prorated refund. Note: If the full and prorated refund periods do not cover the whole subscription period, downsizing will be prohibited for the remaining days. Not Allowed Except For After Upsize: Downsizing is not allowed except for cases when resources were previously upsized. For this policy, the one day of a full refund, then a prorated refund till the end of the allowed downsize period is applied. Note: For Not Allowed Except For After Upsize, ActionPeriod is always set to -1 because the action period is defined by the vendor and is received from the fulfillment system. Prohibited: Downsizing is prohibited. No Refund: Downsizing is allowed without a refund.	No
    ActionPeriod	Yes		Integer number	A number of days starting from the beginning of a subscription period. The -1 value means till the end of a subscription period. The -1 value is the only possible option for Prohibited and Not Allowed Except For After Upsize actions.	No
    ActionPeriodTimezone	Yes		Customer From Plan	Defines the time zone in which CloudBlue Commerce must count days specified in ActionPeriod. Customer: A customer's time zone. From Plan: A time zone configured in a service plan.	No
    ApplicableTo	Yes		First Subscription Period Next Subscription Periods All Subscription Periods	First Subscription Period: The policy is applied after a sales order. Next Subscription Periods: The policy is applied after a renewal order. All Subscription Periods: The policy is applied in the same way for all periods. Important: You need to either specify rules applicable to the First and Next Subscription Periods, or choose All Subscription Periods in the configuration file.	No

    SalesCategories

    This sheet is optional. It defines sales categories that are used in the ServicePlan sheet. The SalesCategories sheet must be created.

    Column Name	Mandatory Column	Default Value (if an optional field is empty or not provided). No default value means that the value will be empty for this parameter	Possible Value	Comments	Editable
    Name_EN	Yes			If a category with the specified name already exists, it will be used to create a plan (a new one with the same name will not be created).	No
    Name_X	No	Name_EN			No
    Description_EN	Yes				No
    Description_X	No	Description_EN			No
    ParentCategory	No			It must be created in advance. It will not be created if it does not exist.	No

    ResourceCategories

    This sheet is optional. It defines resource categories that are used in the Resources sheet. The ResourceCategories sheet must be created.

    Column Name	Mandatory Column	Default Value (if an optional field is empty or not provided). No default value means that the value will be empty for this parameter	Possible Value	Comments	Editable
    Name_EN	Yes			If a category with the specified name already exists, it will be used (a new one with the same name will not be created).	No
    Name_X	No	Name_EN			No
    Description_EN	Yes				No
    Description_X	No	Description_EN			No

    NotificationTemplates

    This sheet is optional.

    Column Name	Mandatory Column	Default Value (if an optional field is empty or not provided). No default value means that the value will be empty for this parameter	Possible Value	Comments	Editable
    TemplateName	Yes				No
    Language	Yes		A two-letter code (ISO 639)		No
    MessageType	No	Subscription Notification	Subscription Notification		No
    MessageCategory	Yes		Renewal and Expiration Notices		No
    Subject	Yes				No
    ToAddr	Yes				No
    ToName	Yes				No
    BccAddr	No				No
    FromAddr	Yes				No
    FromName	Yes				No
    PDFFileName	No				No
    Active	No	True	True / False		No
    Condition	No				No
    VisibleToReseller	No	True	True / False		No
    Security	No	False	True / False		No
    HTML	Yes				Yes
    PlainText	Yes				Yes

    ResourceDependencies

    This sheet is optional.

    Column Name	Mandatory Column	Default Value (if an optional field is empty or not provided). No default value means that the value will be empty for this parameter	Possible Value	Comments	Editable
    ChildResource	Yes				No
    ParentResource	Yes				No
    DependenceKind	Yes		Requires Conflicts on Subscription level Conflicts on Account level		No
    DependenceMultiplier	No		Integer number	Works only for the Requires dependence kind.	No

    UpgradeResourceMapping

    This sheet is optional.

    Column Name	Mandatory Column	Default Value (if an optional field is empty or not provided). No default value means that the value will be empty for this parameter	Possible Value	Comments	Editable
    FromResource	Yes				No
    ToResource_1(2,3…N)	Yes				No

    TermsConditions

    This sheet is optional. It defines T&C that must be created. It works together with the ServicePlans and OpUnitServicePlan sheets.

    Column Name	Mandatory Column	Default Value (if an optional field is empty or not provided). No default value means that the value will be empty for this parameter	Possible Value	Comments	Editable
    Name	Yes				No
    Acceptance	No	On first purchase and after every T&C change	Always, on every purchase Only on a first purchase On first purchase and after every T&C change On every purchase and after every T&C change		No
    Active	No	True	True / False		No
    Content	Yes				No

    OpUnitServicePlans

    This sheet is optional. It defines which T&C must be created for which operating units.

    Column Name	Mandatory Column	Default Value (if an optional field is empty or not provided). No default value means that the value will be empty for this parameter	Possible Value	Comments	Editable
    OpUnit	Yes				No
    ServicePlanName	Yes				No
    TermsConditions_(1...N)	Yes				No
    Published	No		True / False	It is used to override the default value set in the ServicePlans column.	Yes
    ResellerGroupName_(1...N)	No			It is used to share a plan with resellers configured under an operating unit. Specify the RESELLERGATE service template name.	No

    OpUnitResourceCategories

    This sheet is optional. It defines a specific tax category for a resource category at the operating unit level.

    • It works only for operating units (L1).

    • It does not change anything at L0 (a resource category owner level). For L0, tax categories are configured manually.

    • It does not change anything for resellers under an operating unit level (L2). L2 resellers configure their tax categories themselves.

    Column Name	Mandatory Column	Default Value (if an optional field is empty or not provided). No default value means that the value will be empty for this parameter	Possible Value	Comments	Editable
    OpUnit	Yes			This is the value of the InitWizardCountryCode account custom attribute of the operating unit.	No
    ResourceCategoryName	Yes			The resource category used here must be specified in the ResourceCategories tab even if it already exists in the platform.	No
    TaxCategory	Yes			The tax category ID. This must be created manually in advance.	No