C Oracle Communications Data Model Assumptions

Business Unit Code and Business Unit Type Code

These attributes are leveraged for the ORGANIZATION flexible Hierarchy, related to the Organization subject area. Please do not remove and do not mix with ORGANIZATION BUSINESS UNIT CODE (or key).

CALENDAR

Calendars should be filled for 20 years starting with the "smallest" day for which Usage data (in aggregation) will be available (not customer data) and furthest day in the future.

GEOGRAPHY

The default geography of the country, for example, WORLD, REGION, COUNTRY, CITY should be defined upfront whenever possible. Create search functions to simplify the address validation (whether or not add a new CITY or COUNTY row when getting a new address). Addresses should be validated upfront in the source system or at the staging layer. Addresses are supposed to be correct and validated when in Oracle Communications Data Model.

TIME

Please fill the second, minutes, quarter of hours and hours.

TIME SLOT

By default, TIME SLOT should correspond exactly to quarter of hours. You may extend this notion to a bigger time period (for customization), leveraging a time band.

PRODUCT OFFERING

Represents what is externally presented to the market for the market's use. A PRODUCT OFFERING can be assembled from a reusable PRODUCT SPECIFICATION (sometimes referred to as "product spec"). PRODUCT OFFERING is physically associated with DWR_PROD_OFR. Typical abbreviation for PRODUCT OFFERING is "Prod Offer" or "Product Offer" or "Prod Offering".

PRODUCT SPECIFICATION

Represent a tangible object or something intangible provided to customers, realized as a 'service' but not to be confused with a network service (TMF SID). Basically, a PRODUCT SPECIFICATION describes the characteristics of what the customer will be able to do once he purchases (the PRODUCT OFFERING is the market vision, while the PRODUCT SPECIFICATION is the effective Component description, from the perspective of a service provider, that are leveraged to compose the PRODUCT OFFERING). It is physically associated with DWR_PROD_SPECIt is physically associated with DWR_PROD_SPEC.

PRODUCT SPECIFICATION VERSION

A particular form or variety of a PRODUCT SPECIFICATION that is different from others or from the original. The form represents differences in properties that characterize a PRODUCT SPECIFICATION, that are not significant enough for creating a new PRODUCT SPECIFICATION.

PRODUCT

A PRODUCT SUBSCRIPTION represents an instance of a PRODUCT SPECIFICATION, typically available for sales or sold (before or after being subscribed to).

A Product is an Instance of a PRODUCT SPECIFICATION associated with a PRODUCT OFFERING. In Oracle Communications Data Model, it would typically correspond to some hardware (prepaid card, cell phone, and so on) on the shelf (somewhere) with a specific Serial Number ready to be purchased and used. A PRODUCT SUBSCRIPTION would be the realization of a PRODUCT SPECIFICATION when a Service is associated with the PRODUCT SPECIFICATION (for example a GSM service). PRODUCT corresponds to DWR_PROD).

PRODUCT SUBSCRIPTION

Represents the subscription to a PRODUCT OFFERING by a PARTY playing a PARTY ROLE, such as a Customer. A PRODUCT SUBSCRIPTION is an instance of a PRODUCT SPECIFICATION associated with an ACCOUNT, and optionally (usually) with a service and an access method (same definition as PRODUCT otherwise).

PRODUCT SUBSCRIPTION allows each part of a PRODUCT OFFERING (typically based on COMPOSITE PRODUCT SPECIFICATION) to be realized and instantiated separately, "atomic" product specification would be used to describe "trouble ticket" or "product subscription change", by atomic PRODUCT SPECIFICATION to allow actions such as trouble ticket or product subscription change against specific ones (and not just the composite Spec). This is also necessary if a PRODUCT OFFERING has a tariff (PRODUCT OFFERING PRICE) depending on the flexible characteristic associated to a PRODUCT SPECIFICATION.

In Oracle Communications Data Model the PRODUCT SUBSCRIPTION will not carry the AGREEMENT nor its agreement details (AGREEMENT ITEM). AGREEMENT ITEM holds the Product subscription Key. Through AGREEMENT ITEM, one can find out the Agreement Key. As a consequence, a change in PRODUCT SUBSCRIPTION may imply a change in AGREEMENT ITEM (typically for postpaid but not only) but not in AGREEMENT.

The attributes "Product Subscription Status code" and "Status code" should always be filled and equal.

PRODUCT LINE

A method used to group related PRODUCT SPECIFICATIONs marketed by the same company that differ only in size or style. This strict definition (TMF SID) is in fact open for any definition that fits the Communications Service Provider (CSP).

PRODUCT SPECIFICATION CATEGORY

A method used to group a set of PRODUCT SPECIFICATIONs that are classified together because of common characteristics. This strict definition (TMF SID) is in fact open for any definition that fits the CSP.

Plan Type

The attribute Plan Type in PRODUCT OFFERING corresponds to either "Prepaid" or "Postpaid". It is used in the PRODUCT OFFERING Hierarchy. Possible extensions are "Hybrid" but should be carefully looked at, especially with respect to the implications on some critical intra-ETLs such as COUNT DAY DRVD, REVENUE DAY DRVD and others.

SKU ITEM

SKU ITEM tracks physical inventory. Hence, there must be one row (that is one key) per concrete object. SKU ITEM can roll up to two hierarchies: ITEM SPECIFICATION and PRODUCT SPECIFICATION. Those two hierarchy attributes are present in SKU ITEM.

ACCESSORIES, HANDSET MODEL and EQUIPMENT

These are a subset of SKU ITEM with fewer attributes and some unique attributes. Just like SKU ITEM, they can roll up to ITEM SPECIFICATION or PRODUCT SPECIFICATION.

ACCESSORIES are always add-ons to a main item.

For each instances of ACCESSORIES, EQUIPMENT and HANDSET MODEL (in their respective DWR_XXX_INSTNC tables), do the following:

• Add automatically 1 row to DWR_SKU_ITEM for each row of those instances with all the columns you can feed.

• SKU_TYP_CD values should have additional lookup values such as: 'ACCESSORIES', 'EQUIPMENT', 'HANDSETMODEL';

SUPPLEMENTARY SERVICE

In Oracle Communications Data Model, only Postpaid-related supplementary Services have been picked, leveraging any PRODUCT SPECIFICATIONs defined in DWR_SPLMNTR_SRVC. Note however that the supplementary service derived table only runs over PRODUCT SPECIFICATION, looking for specific Product Specification codes, instead of linking to DWR_SPLMNTR_SRVC, because the target attributes are fixed.

VAS Service

Value-Added Service VAS applies only to WIRELESS CALL EVENT: the following tables should be taken. The current Intra-ETL version for VAS Usage derived, only uses DWB_ISP_USG_EVT, DWB_MMS_EVT, DWB_UDR_EVT, and DWB_SMS_EVT.DWB_UMS_EVT,

DWB_WRLS_CALL_EVT

DWB_WRLS_CALL_EVT

DWB_SMS_EVT

DWB_MMS_EVT

DWB_WRLS_CNTNT_DNLDG_EVT

DWB_CNTNT_DLVRY_EVT

DWB_GPRS_USG_EVT

DWB_ISP_USG_EVT

DWB_UMS_EVT

DWB_WRLS_RMNG_EVT

CUSTOMER

Customer has PROFILES and they have sub types of Individual and Business (termed as Organization). In addition Customer Contains PROSPECT also plus other related reference entities such as Customer Financial and Non Financial relationship (Accounts, Affiliation, Group, Occasion, Preference, Demography). Some of the "Named" Demography attributes are kept in CUSTOMER entity.

PARTY and PARTY ROLE

A party is an umbrella relationship across customer, vendor, employee and similar entities. For a given natural key, a Party ID ties up all these disparate entities and allows to leverage each other while maintaining their individual signatures. Remember PARTY as an after thought which enables you to tie together the disparate entities with a common ID based on same Natural key. By definition all these disparate entities are created at a different time frame with diverse OLTP systems but all of them would carry same natural key if the same party is defined in the system as a customer, an employee and a vendor as well.

By default, only two types of PARTY are available in Oracle Communications Data Model: Individual and ORGANIZATION. This can be extended but consider extending and also updating the various tables associated with a PARTY ROLE such as CUSTOMER if they are impacted.

The same party can play several party roles with respect to the CSP: A person could be customer, employee and dealer of the CSP at the same time. It would have the same party key. But for a given interaction or event, only one party role is typically used (unless he sells to himself a product offering, for example). Standard Party Roles like CUSTOMER, VENDOR, DEALER, EMPLOYEE, SALES CHANNEL REPRESENTATIVE are pre-defined in Oracle Communications Data Model with their sub-types.

Invoice

If Invoice Creation Date is not defined, BILLING DATE shall be used. Note that BILLING DATE must be present as it is used for several Intra-ETLs and several links. Outstanding Invoices are Invoices to be paid, not necessarily overdue, but at least dispatched. Overdue invoices are invoices not being paid by (end of) DUE DATE.

Age On Net

AGE_ON_NET can be defined for whatever "entity" (Agreement, customer, product subscription) one considers. By default, the creation date (CRT_DT) or start date (STRT_DT) shall be used and not worry too much about "holes" (approximation).

The following lists the various "ages" Oracle Communications Data Model could consider (only the first choice is used by default):

• CONTRACT AGE: Age of the current (or considered) contract. it starts on contract start date and stops increasing when contract is churned.

• CUSTOMER AGE ON NET: to calculate it correctly, one would require that the following should be stored at CUSTOMER level (customization):

• AGE_ON_NET_START_DATE: earliest date of any(!) agreement (postpaid or prepaid) at which the customer started being active.

• AGE_ON_NET_TOT_MISSINGMONTHS: Total number of months (decimal) where the customer was absent (not active, without any agreements) to cover any holes, and so on.

The formula should then be: AGE_ON_NET (default in months) =

CEIL(MONTHS_BETWEEN(SYSDATE,AGE_ON_NET_START_DATE) MINUS AGE_ON_NET_TOT_MISSINGMONTHS).

To simplify this in Oracle Communications Data Model: AGE_ON_NET (default in months) =

CEIL(MONTHS_BETWEEN(MIN(SYSDATE, nvl(CHRN_DT, to_date('20990101','yyyymmdd'))),CRT_DT) )

Please note the approximation that CUSTOMER effective start date is considered to be its CREATION date. Similarly, CONTRACT AGE (default in months) =

MONTHS_BETWEEN(MIN(SYSDATE, nvl(END_DT, to_date('20990101','yyyymmdd'))), STRT_DT).

Loyalty

Loyalty is a specific subject area as such. In Oracle Communications Data Model the Loyalty program and balances are associated with a specific MEMBERSHIP ACCOUNT, which is only associated to customer (and not to ACCOUNT). Any event that could impact Loyalty points should be stored in EVENT LOYALTY PROGRAM (DWB_EVT_LYLTY_PROG) and from there, the ETL (not the intra-ETL!) should also feed DWB_ACCRUAL_EVT (points winning) and DWB_REDEM_EVT (redemption, expiration)

ACCOUNT BALANCE IMPACT records with ACCT_BAL_TYP Like '%LYLTY%' should have a corresponding event created into DWB_LYLTY_PROG_EVT records.

MEMBERSHIP ACCOUNT BALANCE HISTORY is a snapshot (similar to ACCOUNT BALANCE)

The events that could feed EVENT LOYALTY PROGRAM as earning points should all come from Billing System, whatever their origin (usage, customer order, payment...).

It is however possible to also link a pure retail transaction but this has to be done explicitly in Oracle Communications Data Model (Customization).

The events that could feed LOYALTY PROGRAM EVENT as redemption are of 3 types:

• Retail transaction

• Customer Order

• Account Payment (Payment with points)

Please not that the Derived Loyalty table is only at LOYALTY PROGRAM level.

Table C-11 provides information on a Loyalty events

Count Day

The COUNT DAY DRVD stores all counts with respect to customer, account, agreement, product subscriptions, to be able to get at any day in time the effective count of the entity status at the end of a given day.

Oracle Communications Data Model assumes that if either Customer Surrogate Key, Account Surrogate Key or Agreement Surrogate key changes, the associated product subscriptions (Product subscription key) shall also change.

If for Customer and ACCOUNT, it is clear due to the (forced) links between account/customer and product subscription, this link is not compulsory between agreement and product subscription. (It could easily be made as customization). The link between agreement and Product subscription is only through AGREEMENT ITEM, and agreement can change in principle without impacting product subscription.