Rate items -
This table is perhaps the most complicated of them all. In addition to the number of rate items that are provided, and the fact that new ones can be added as plugins, but many of the rate items share certain properties, and many of the overloaded fields are used to keep the meaning consistent for related rate items. Programmatically this allows for an inherited structure of objects.
Chargeable rate items: There is no rate item called Chargeable, however, rate items that have similar properties and extend from this set of overloaded fields and maintain there meaning: Chargeable rate items are: Rate, Application, Agreement, Adjustment The fields and there meanings are: " enum_1 = billing_cycle, 0=onetime, 1=installments , 2=periodic, 3=na " enum_2 = periodic type, -1=na, 0=daily, 1=weekly, 2=monthly " enum_3 = number of installments " enum_4 = charge type, 0=credit, 1=debit " enum_9 = cost type, 1=sp/st specify, 3=user specified " enum_10 = periodic type interval " text_1 = unit type " text_4 = unit cost text " numeric_1 = unit cost
ChargeableAssociated rate items: Similarly, there is no rate item called Chargeable, however, rate items that have similar properties and extend from this set of overloaded fields and maintain there meaning. What is significant about this set is that ChargeableAssociated rate item also extend from Chargeable rate items. ChargeableAssociated rate items are: Application, Agreement
The fields and there meanings are: " enum_5 = 1 means rate item is associated to a rate plan, 0 is not " enum_6 = tier type, -1=na, 0=lookup, 1=lookup multiple, 2=fixed, 3=fixed incremental, 4=fixed decremental, 5=fixed incremental/decremental, 6=variable lookup, 7=variable fixed, 8=variable fixed incremental, 9=variable fixed decremental, 10=variable fixed incremental/decremental
Associated rate items: Similarly, there is no rate item called Associated, however, rate items that have similar properties and extend from this set of overloaded fields and maintain there meaning: Associated rate items are: Numeric, Boolean, Date The fields and there meanings are: " enum_5 = 1 means rate item is associated to a rate plan, 0 is not " enum_6 = tier type, -1=na, 0=lookup, 1=lookup multiple, 2=fixed, 3=fixed incremental, 4=fixed decremental, 5=fixed incremental/decremental, 6=variable lookup, 7=variable fixed, 8=variable fixed incremental, 9=variable fixed decremental, 10=variable fixed incremental/decremental
Range rate items: Similarly there is no rate item called Range, however, rate items that have similar properties and extend from this set of overloaded fields and maintain there meaning: Range rate items are: Numeric Range, Date Range The fields and there meanings are: " enum_1 = 1 is infinite upper bound, 0 is not.
The following list is a compilation of all the fields used for each type of rate item:
When item_type = 0 (text rate item) " text_1 = textual value
When item_type = 1 (header rate item) does not use overloaded fields
When item_type = 2 (numeric range rate item) " enum_1 = 1 is infinite upper bound, 0 is not. " numeric_1 = lower bound " numeric_2 = upper bound
When item_type = 3 (rate rate item) " enum_1 = billing_cycle, 0=onetime, 1=installments , 2=periodic, 3=na " enum_2 = periodic type, -1=na, 0=daily, 1=weekly, 2=monthly " enum_3 = number of installments " enum_4 = charge type, 0=credit, 1=debit " enum_5 = quantity type, 0=flat rate, 1=specify, 2=admin lookup, 3=user specified, 4=system passes, 5=user lookup " enum_6 = rate item id for quantity if a lookup is being made to another rate item for the quantity " enum_7 = 1 means show quantity on the invoice, 0 means dont " enum_8 = offering id for quantity. This is needed in conjunction with enum_6 because a rate plan can be included in more then one offering and therefore is necessary to not only know the rate item but which offering. " enum_9 = cost type, 0=sp/st specify, 1=user specified " enum_10 = periodic type interval " text_1 = unit type " text_4 = unit cost text " numeric_1 = unit cost " numeric_2 = quantity
When item_type = 4 (application rate item) " enum_1 = billing_cycle, 0=onetime, 1=installments , 2=periodic, 3=na " enum_2 = periodic type, -1=na, 0=daily, 1=weekly, 2=monthly " enum_3 = number of installments " enum_4 = charge type, 0=credit, 1=debit " enum_5 = pricing structure, 0=subscription, 1=tiered, 2=usage based " enum_6 = tier type, -1=na, 0=lookup, 1=lookup multiple, 2=fixed, 3=fixed incremental, 4=fixed decremental, 5=fixed incremental/decremental, 6=variable lookup, 7=variable fixed, 8=variable fixed incremental, 9=variable fixed decremental, 10=variable fixed incremental/decremental " enum_7 = 1 is show mteric result on invoice, 0 is dont " enum_9 = cost type, 1=sp/st specify, 3=user specified " enum_10 = periodic type interval " text_1 = unit type " text_2 = application id " text_3 = metric id " text_4 = unit cost text " numeric_1 = unit cost
When item_type = 5 ( agreement rate item) " enum_1 = billing_cycle, 0=onetime, 1=installments , 2=periodic, 3=na " enum_2 = periodic type, -1=na, 0=daily, 1=weekly, 2=monthly " enum_3 = number of installments " enum_4 = charge type, 0=credit, 1=debit " enum_5 = 1 means rate item is associated to a rate plan, 0 is not " enum_6 = tier type, -1=na, 0=lookup, 1=lookup multiple, 2=fixed, 3=fixed incremental, 4=fixed decremental, 5=fixed incremental/decremental, 6=variable lookup, 7=variable fixed, 8=variable fixed incremental, 9=variable fixed decremental, 10=variable fixed incremental/decremental " enum_7 = 1 is charge for sla, 0 is dont " enum_9 = cost type, 0=sp/st specify, 1=user specified " enum_10 = periodic type interval " text_1 = unit type " text_2 = agreement id " text_4 = unit cost text " numeric_1 = unit cost
When item_type = 6 (numeric rate item) " enum_1 = quantity type, 0=flat rate, 1=specify, 2=admin lookup, 3=user specified, 4=system passes, 5=user lookup " enum_5 = 1 means rate item is associated to a rate plan, 0 is not " enum_6 = tier type, -1=na, 0=lookup, 1=lookup multiple, 2=fixed, 3=fixed incremental, 4=fixed decremental, 5=fixed incremental/decremental, 6=variable lookup, 7=variable fixed, 8=variable fixed incremental, 9=variable fixed decremental, 10=variable fixed incremental/decremental " numeric_1 = numeric value " enum_7 = 1 is show quantity on invoice, 0 is dont
When item_type = 7 (boolean rate item) " enum_3 = the boolean value, 0 = false, 1 = true " enum_5 = 1 means rate item is associated to a rate plan, 0 is not " enum_6 = tier type, -1=na, 0=lookup, 1=lookup multiple, 2=fixed, 3=fixed incremental, 4=fixed decremental, 5=fixed incremental/decremental, 6=variable lookup, 7=variable fixed, 8=variable fixed incremental, 9=variable fixed decremental, 10=variable fixed incremental/decremental
When item_type = 8 (adjustment rate item) " enum_1 = billing_cycle, 0=onetime, 1=installments , 2=periodic, 3=na " enum_2 = periodic type, -1=na, 0=daily, 1=weekly, 2=monthly " enum_3 = number of installments " enum_4 = charge type, 0=credit, 1=debit " enum_5 = 1 means rate item is associated to a rate plan, 0 is not " enum_6 = pointer to the rate item that this adjustment will be applied to " enum_7 = adjustment type, 0=applied amount, 1=multiplier, 2=limit applied amount " numeric_2 = adjusment value " enum_8 = pointer to the offering that contains the rate item being adjusted. This is needed in conjunction with enum_6 because a rate plan can be included in more then one offering and therefore is necessary to not only know the rate item but which offering. " enum_9 = cost type, 0=sp/st specify, 1=user specified " enum_10 = periodic type interval " text_1 = unit type " text_4 = unit cost text " numeric_1 = unit cost
When item_type = 9 (date rate item) " enum_1 = date type, 0=specify, 1=subscription date, 2=invoice date " enum_5 = 1 means rate item is associated to a rate plan, 0 is not " enum_6 = tier type, -1=na, 0=lookup, 1=lookup multiple, 2=fixed, 3=fixed incremental, 4=fixed decremental, 5=fixed incremental/decremental, 6=variable lookup, 7=variable fixed, 8=variable fixed incremental, 9=variable fixed decremental, 10=variable fixed incremental/decremental " text_1 = the date value
When item_type = 10 (date range rate item) " enum_1 = 1 is infinite upper bound, 0 is not. " text_1 = lower bound date " text_2 = upper bound date
When item_type = 11 (day rate item) " enum_1 = billing_cycle, 0=onetime, 1=installments , 2=periodic, 3=na " enum_2 = periodic type, -1=na, 0=daily, 1=weekly, 2=monthly " enum_3 = number of installments " enum_4 = charge type, 0=credit, 1=debit " enum_5 = quantity type, 0=flat rate, 1=specify, 2=admin lookup, 3=user specified, 4=system passes, 5=user lookup " enum_6 = rate item id for quantity if a lookup is being made to another rate item for the quantity " enum_7 = 1 means show quantity on the invoice, 0 means dont " enum_8 = offering id for quantity. This is needed in conjunction with enum_6 because a rate plan can be included in more then one offering and therefore is necessary to not only know the rate item but which offering. " enum_9 = cost type, 0=sp/st specify, 1=user specified " enum_10 = periodic type interval " enum_11 = a value for the day of week or the day of month
" text_1 = unit type " text_4 = unit cost text " numeric_1 = unit cost " numeric_2 = quantity
When item_type = 12 (cost allocation rate item) " enum_1 = billing_cycle, 0=onetime, 1=installments , 2=periodic, 3=na " enum_2 = periodic type, -1=na, 0=daily, 1=weekly, 2=monthly " enum_3 = number of installments " enum_4 = charge type, 0=credit, 1=debit " enum_5 = pricing structure 1=tiered, 2=usage based " enum_6 = tier type, -1=na, 0=lookup, 1=lookup multiple, 6=variable lookup " enum_7 = 1 is show metric result on invoice, 0 is dont " enum_9 = cost type, 1=sp/st specify, 4=allocation " enum_10 = periodic type interval " enum_11 = 0 don't add budget, 1=add budget " text_1 = unit type " text_2 = application id " text_3 = metric id " text_4 = unit cost text " numeric_1 = unit cost
| Column Name | Column Datatype | Column Null Option | Column Comment |
| item_id | integer | not null | The unique id for each rate item |
| rate_plan_id | integer | with null | The unique id for each rate plan |
| base_id | integer | not null | The rate item that this rate item inherits from. |
| association_id | integer | not null | 1=no references, this is the associated rate_plan_id |
| rate_row | integer | not null | The row position of this rate item in the rate plan |
| rate_col | integer | not null | The column position of this rate item in the rate plan |
| code | nvarchar(64) | with null | SP/ST supplies code |
| status | integer | not null | 0=delete, 1=active |
| item_text | nvarchar(512) | with null | The display text of the rate item |
| item_type | integer | not null | the type of the rate item. SP can create there own rate item by plugging them in and defining there own item_typ, the following are rate items provided out-of-the-box: -1=empty, 0=text, 1=header, 2=numeric range, 3=rate, 4=application, 5=agreement, 6=numeric, 7=boolean, 8=adjustment, 9=date, 10=date range, 11=day |
| is_hidden | integer | not null | If set to 0 the item is subscribable and invoicable, if set to 1, then this rate item can be subscribed to but will not be included in any invoice calculations and will not appear on the invoice. If set to 2, then user will not see the rate item during subscription which means they won |
| notes_id | integer | with null | Currently not used. |
| info_link | nvarchar(128) | with null | If this contains a value in the form of a URL a "more info..." link will appear next to the rate item |
| currency_1 | nvarchar(50) | with null | The currency of the SP or ST creating this rate item. |
| currency_2 | nvarchar(50) | with null | Currently not used. |
| date_available | date | with null | The date the rate item is available for subscription and consequently invoicing |
| date_unavailable | date | with null | The date when the rate item is no longer available for subscription and consequently invoicing. If there is already a subscription to this rate item, then it will only be invoiced up to this date. |
| effective | integer | with null | This specifies when the dates_available/date_unavailable take effect for rate items already subscribed to the rate item for matters regarding invoicing. 0=beginning of an account |
| changeto_item_id | integer | with null | This value is -1 if a change to a rate item does not create another rate item along the audit change trail. Otherwise it points to the id of another rate item. |
| enum_1 | integer | with null | See Table Comment |
| enum_2 | integer | with null | See Table Comment |
| enum_3 | integer | with null | See Table Comment |
| enum_4 | integer | with null | See Table Comment |
| enum_5 | integer | with null | See Table Comment |
| enum_6 | integer | with null | See Table Comment |
| enum_7 | integer | with null | See Table Comment |
| enum_8 | integer | with null | See Table Comment |
| enum_9 | integer | with null | See Table Comment |
| enum_10 | integer | with null | See Table Comment |
| enum_11 | integer | with null | See Table Comment |
| enum_12 | integer | with null | See Table Comment |
| enum_13 | integer | with null | See Table Comment |
| enum_14 | integer | with null | |
| numeric_1 | float | with null | See Table Comment |
| numeric_2 | float | with null | See Table Comment |
| text_1 | nvarchar(50) | with null | See Table Comment |
| text_2 | nvarchar(50) | with null | See Table Comment |
| text_3 | nvarchar(50) | with null | See Table Comment |
| text_4 | nvarchar(50) | with null | See Table Comment |
| text_5 | nvarchar(100) | with null | |
| date_1 | date | with null | |
| date_2 | date | with null | |
| sub_note_control | integer | with null | Currently not used. |
| category | integer | with null | This field is used to logically group rate items the logical groups can be: 0=None, 1=Software, 2=Hardware. This field will be used as a field to filter in rule management to trigger different workflow |
| external_id | nvarchar(64) | with null | |
| approval_level | integer | not null | |
| approval_process | integer | with null | |
| category_class | integer | with null | |
| category_subclass | integer | with null | |
| track_as_asset | integer | with null |
Product Name: Unicenter Service Management
Table Type: Table
Relationships:
| Primary Key | Related Table | Related Column |
| item_id | usm_subscription_detail | item_id |
| item_id | usm_offering_ratedef_inclusion | child_id |
| item_id | usm_rateplan_inheritance | item_id |
| item_id | usm_rateplan_inclusion | child_id |
| item_id | usm_plan_data | item_id |