CallRecordMaster

CRM stands for 'CallRecordMaster'. This is where the CDR will go after they have been rated. Depending on your actual call volume, you may decide to partition this table on the day, month or year of the CallDateTime field.

This page describes the columns in this table.

Column | Data type | Nulls | Description

CallID | varchar(100) | No Nulls | This should contain the same unique CallID your CDR source uses so you can reference the original record if you ever need to. This value must be unique amongst all your CDR.

CustomerID | varchar(15) | Nulls Allowed | This field is optional though if you do have the value, we recommend supplying it. Alternatively, we might be able to determine the CustomerID by other fields on the CDR, i.e. SourceIP, OriginationNumber, DestinationNumber, SAP ID, etc.

CallType | smallint | Nulls Allowed | Please see [Call Types] for more information. If you do not know this value, use null and we will attempt to derive it from other fields in the CDR.

CallDateTime | timestamp | No Nulls | This is the date/time the call was connected.

Duration | integer | No Nulls | This is the raw duration, in seconds, of the call. We will convert it to 'Billed Duration' during rating.

BilledDuration | integer | No Nulls | During rating, we use the raw duration, apply the billing intervals to it and calculate the new BilledDuration of the call.

Direction | char(1) | Nulls Allowed | This field is optional but if you do know it, send it in. If you are populating this field, use "I" for incoming calls and "O" for outgoing calls.

SourceIP | vachar(15) | Nulls Allowed | This field is optional. We may use this field to determine the CustomerID if the CustomerID field is blank/null.

OriginatingNumber | varchar(50) | No Nulls | This field is required. It should be in E.164 format. If it cannot be supplied in E.164 then scripting in the rating engine may attempt to convert it to E.164. This field can also be used to help determine the CustomerID.

DestinationNumber | varchar(50) | No Nulls | This field is required. It should be in E.164 format. If it cannot be supplied in E.164 then scripting in the rating engine may attempt to convert it to E.164. This field can also be used to help determine the CustomerID.

LRN | varchar(50) | Nulls Allowed | If the LRN is supplied, we can bill termination using LRN instead of DestinationNumber. This will be a customer-level setting.

BilledNumber | varchar(50) | No Nulls | This is the number used to determine the per-minute rate during the rating process. This value will differ based on whether we rated off DestinationNumber or LRN.

BilledPrefix | varchar(10) | Nulls Allowed | This is the full or partial match rating made when compare the BilledNumber to the rate sheets.

CNAMDipped | boolean | Nulls Allowed | Per-call surcharges can be added if the call was dipped for CNAM. This is customer-level setting.

CNAMFee | numeric(9, 9) | No Nulls | This is the CNAM dip fee applied to the call.

LRNFee | numeric(9, 9) | No Nulls | This is the LRN dip fee applied to the call.

RetailRate | numeric(9, 7) | No Nulls | This is the per-minute rate applied to the call.

RetailPrice | numeric(19, 7) | No Nulls | This is the total cost of the call after the per-minute rate and surcharges (CNAM, LRN, etc) have been applied.

RatedDateTime | timestamp | No Nulls | This is the date/time the call was successfully rated.

BilledTier | 1-byte integer | Nulls Allowed | This is the billed tier we calculated for [Tiered Origination] calls.

RateCenter | varchar(50) | Nulls Allowed | Rate center for [Tiered Origination] calls.

BillingBatchID | varchar(15) | Nulls Allowed | This is the batchid assigned to the call during the billing process.

CarrierID | varchar(100) | Nulls Allowed | Optional. Used primarily for reporting.

WholesaleRate | numeric(19, 7) | No Nulls | This is the per-minute rate applied to the call by your carrier. This value might be supplied in the CDR your carrier sends you. This information can be used to calculate margins or profitability to certain destinations.

WholesalePrice | numeric(19, 7) | No Nulls | This is the price you carrier charged you for this call. This value might be supplied in the CDR your carrier sends you. This information can be used for profit analysis reporting.

RoutingPrefix | varchar(10) | Nulls Allowed | Optional. You can supply it or we can parse it from DestinationNumber using '#' as a separator. We use routing prefix for rating and [Carrier Reconciliation].

RowID | integer | Auto-increment | This is an internal field used primarily for adhoc queries. Do not supply a value for this field. Do not remove this field.

If you have any questions, please contact [Support].

-The OpenCDRRate team.

Follow us on twitter to receive new feature notifications: http://www.twitter.com/opencdrrate/

Join our LinkedIn group to open discussions on features you would like to see in the system. http://www.linkedin.com/groups/OpenCDRRate-4381972