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.

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 not provided or empty). 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 other locales if columns Name_XX are provided 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 other locales if columns Description_XX are provided 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		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 not provided or empty). 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 a 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 before this moment.	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. This field can be updated for existing plans.	No
ServiceTerms	Yes			Service Terms must be created in advance. This field can be updated for existing plans.	No
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. This field can be updated for existing plans.	Yes
RecurringType	Yes		Charge Before Billing Period Charge After Billing Period Charge Before Subscription Period Use External Rating	Billing Model	No
AutoRenew	No	Before Expiration	Disabled On Last Statement Day Before Expiration		No
RenewOrderInterval	No	1	Integer number	Renewal Point (days) Works only if AutoRenew = Before Expiration	No
AutorenewalPeriod	No	Default	Default / One of the subscription periods in the configured plan (N Year(s) / Month(s)	Autorenewal For the Period Works only if AutoRenew = On Last Statement Day or Before Expiration	No
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.	No
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	It is 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	It is 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

PlanPeriods

This sheet is mandatory.

Column Name	Mandatory Column	Default Value (if an optional field is not provided or empty). 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	It is used to define whether a subscription period is active. It can be changed for existing subscription periods.	No
FullRefundPeriod	No	0	Integer number		No

ResourceRates

This sheet is mandatory.

Column Name	Mandatory Column	Default Value (if an optional field is not provided or empty). No default value means that the value will be empty for this parameter	Possible Value	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	Yes
ShowZeroRecurringFeeInOrder	No	False	True / False	Yes
ShowZeroSetupFeeInOrder	No	False	True / False	Yes
ShowZeroOveruseFeeInOrder	No	False	True / False	Yes
SetupFeePerUnit	No	False	True / False	Yes
RecurringFeePerUnit	No	False	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 this 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	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 in the Period Calculation Algorithm section.	No
ActionPeriodTimezone	Yes	Customer From Plan	Defines the timezone in which CloudBlue Commerce must count days specified in ActionPeriod. Customer: A customer's timezone. From Plan: A timezone 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 this 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	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	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
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 timezone in which CloudBlue Commerce must count days specified in ActionPeriod. Customer: A customer's timezone. From Plan: A timezone 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 not provided or empty). No default value means that the value will be empty for this parameter	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 not provided or empty). No default value means that the value will be empty for this parameter	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 not provided or empty). No default value means that the value will be empty for this parameter	Possible Value	Editable
TemplateName	Yes			No
Language	Yes		A two-letter code (ISO 639)	No
MessageType	No	Subscription Notifications	Subscription Notifications	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	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 not provided or empty). 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 not provided or empty). No default value means that the value will be empty for this parameter	Possible Value	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	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	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