7 Oracle Communications Data Model Intra-ETL

ACCT_BAL0

Time window: All columns shall represent the status at the end of the period considered (End of the last possible day already passed of current month in which the Intra-ETL is running).

If you run the Intra-ETL on November 15th. It shall take the sum of all bucket amounts with the status of the 14th End of day (and store it in month November) overwriting whatever was already there from the previous run in month November.

ACCT_BAL1

The "Balance Amount" is defined as what will appear directly in Bucket_amount at the time considered by ACCT_BAL0 rule.

It assumes that all snapshots of all balances for any buckets of a given account occur at the same time (within the same second).

SUM(DWB_ACCT_BAL.BAL_AMT) where BAL_DT =MAX(BAL_DT) for this account, account balance type, product offering, product spec and bucket code (if defined)

ACCT_BAL2

MAX_BAL_DT - Maximum date possible at which all balances of this type (of any buckets) from this account will expire.

max(DWB_ACCT_BAL.BAL_DT)

ACCT_BAL3

ACCOUNT BALANCE is filled such that EITHER every bucket is defined and filled OR the BUCKET CODE is always undefined (for a given account). It is mutually exclusive for a given account.

This means in DWB_ACCT_BAL, for a given account and for a given account balance type, either BUCKET_CD = '-5000', OR BUCKET_CD is always defined (not unknown).

See base data assumptions.

ACCT_BAL4

Whenever a balance goes to 0 or expires, this balance (Status) is still taken into account and stored in Oracle Communications Data Model.

See base data assumptions.

ACCT_BAL5

Product Offering Code and Product Specification Code have to be present in the base table (ACCOUNT BALANCE). If not, the default value shall be used. (reporting purpose only)

Default Value is "-5000".

ACCT_BAL6 Obsolete

ACCT_BAL_TYP_CD - Types of Account Balance. No restriction on it.

Restrictions could be added as customization.

ACCT_BAL7

Account Balance Impact whose impact date is greater (AFTER) than the latest ACCOUNT BALANCE snapshot (balance date) shall be ignored in the current run but shall be taken into account in the following run.

It is therefore expected from an ETL perspective to make sure that account balance impact and account balance are synchronized.

ACCT_BAL8

Due Amount is meant in any direction (from the CSP to the customer or vice versa). Defined as the maximum between Balance amount and Minimum required Amount.

It is not necessarily related to a specific invoice.

ACCT_BAL9

The base table Unit Allowance shall contain the Effective Prepaid Allowance (PPA) associated with a given offering and product specification, adding all eventual bonus and promotion.

In case Product Spec and Product Offer Key are undefined in ACCOUNT BALANCE, one shall sum ALL PPA of any product offering and specification whose subscription has been active at least one day within the period concerned.

ACCT_BAL10

PPA Category Code associated with the derived will be the biggest (in alphanumeric sense) of all available.

ACCT_BAL11

Balance Begin Date is the minimum balance begin date of any valid (active) balances of this type for this account within the time period.

ACCT_BAL12

Disputed Amount is only considered if it is related to an Balance Impact (with reason like '%DSPT%').

It is not checking into INVOICE ADJUSTMENT for performance reason.

ACCT_BAL13

There will be no restriction in time (except that it is within the month considered) for the balance impact. Hence, any balance impact of the month shall be considered, independently of the fact that it has already impacted the balance snapshot or not.

This assumption is important to be sure that no impacts are lost between the last balance date available and the end of the month.

It also means that the content of the table for a given month will be overwritten every time it runs within the month.

DWB_ACCT_BAL_IMPC

ACCT_BAL_IMPC_RSN_CD

'ADJ'

An adjustment takes place

DWB_ACCT_BAL_IMPC

ACCT_BAL_IMPC_RSN_CD

'PYMT'

A payment is the source of the impact

DWB_ACCT_BAL_IMPC

ACCT_BAL_IMPC_RSN_CD

'RCHRG

A Recharge is the source of the impact (subtype of payment).

DWB_ACCT_BAL_IMPC

ACCT_BAL_IMPC_RSN_CD

'TRNSFR IN'

Transfer Incoming

DWB_ACCT_BAL_IMPC

ACCT_BAL_IMPC_RSN_CD

'TRNSFR OUT'

Transfer Outgoing

DWB_ACCT_BAL_IMPC

ACCT_BAL_IMPC_RSN_CD

'RFND'

Refund

DWB_ACCT_BAL_IMPC

ACCT_BAL_IMPC_RSN_CD

'WRTOFF

Write-Off (specific type of adjustment).

DWB_ACCT_BAL_IMPC

ACCT_BAL_IMPC_RSN_CD

'%DSPT%'

Dispute - reduce the account balance normally. Any reason with "DSPT" in it will be taken into account.

DWB_ACCT_BAL_IMPC

ACCT_BAL_IMPC_RSN_CD

'USG'

Calls or service usage triggers the impact

DWB_ACCT_BAL_IMPC

ACCT_BAL_IMPC_RSN_CD

'PRMTN'

A Promotion is the reason for this Impact (usually with loyalty points)

DWB_ACCT_BAL_IMPC

ACCT_BAL_IMPC_RSN_CD

'NBR LN'

The Number of lines is the trigger for this Impact (usually with loyalty points)

DWB_ACCT_BAL_IMPC

ACCT_BAL_IMPC_RSN_CD

'SBRP AGE'

The Age On Net of this subscription or customer is the trigger for this Impact (usually with loyalty points)

DWB_ACCT_BAL_IMPC

ACCT_BAL_IMPC_RSN_CD

'DRCT DEBIT'

The fact to move to Direct Debit is the trigger for this Impact (usually with loyalty points)

DWB_ACCT_BAL_TYP

ACCT_BAL_TYP_CD

'%LYTY%

A balance of type Loyalty^Foot 1 

DWB_ACCT_BAL_TYP

ACCT_BAL_TYP_CD

'%LYTY BONUS%

A balance of type Bonus associated with Loyalty balance

DWB_ACCT_BAL_TYP

ACCT_BAL_TYP_CD

'%BONUS%

Any balance of type Bonus

DEBTMO1

The debt is only considered whenever it is present in ACCOUNT DEBT and debt is set within any day in the current month.

There must be a row in DWB_ACCT_DEBT with Month (date considered) between MONTH(DEBT_STRT_DT) and MONTH(DEBT_END:DT) - boarder included.

DEBTMO2

Even if the account has multiple contracts and invoices with different due-dates, it is considered in debt as long as at least one of these invoices is not settled.

It is therefore independent of the total amount due, or the number of in-debt invoices.

As a consequence, a given account can only have one debt - not several.

DEBTMO3

Any Payment of any type (standard, transfer…) made during a Debt period is considered as payment to the debt until the debt is ended.

DEBTMO4

Debt Age is calculated based on the day the 1st time the customer was in debt - whatever the amount, whenever it was.

Example: Assuming 2 invoices over 2 months were sent and the customer finally paid the 1st one but the 2nd one is still due (and the debt flag was not set back either because it was forgotten or because the 2nd bill is also over-due), the debt age will be still considered to be with respect to the due date of 1st invoice!

The reason of this limitation is due to the fact that the DUE DATE is not part of the Account Debt (it is also not the goal nor would it make sense because the current balance might cover multiple bills as shown in the example).

A customization could be added a OLDEST DUE DATE or DEBT_START_DATE column which would need to be updated when a payment occurs.

This could be however complex from an ETL perspective (source to Oracle Communications Data Model).

DEBTMO5

Balance Adjustment taken into account around debt are of 3 types by default:

91% = Penalty = Fee for late payment.

3% = Waiving = Penalty Fee reduction or closing dispute (and only these).

4%= Write-off = Amount that the CSP agrees to write-off. Write off should only apply to churned or suspended customers you cannot reach anymore. It does not cancel the debt as such. It only considers you will never get the debt back, so you need to write it off from your profit. It is an accounting process.

These are stored as Adjustment Reason Code in their respective views (ACCT BAL ADJ and ACCT DEBT WRT OFF).

They must be done by an employee during a business interaction (Party thread interaction).

See default Lookup values tables.

The Employee information that did the adjustment is not stored in the target table as it is not its goal. The employee used is the target table is the one showing up in the ACCOUNT DEBT table.

DEBTMO6

Account Status Code stored is by default the ACCOUNT STATUS TYPE CODE of the ACCOUNT Table. If it is null, STATUS CODE of the same table is used.

nvl(DWR_ACCT.ACCT_STAT_TYP_CD, DWR_ACCT.STAT_CD)

DEBTMO7

Credit Category: The Credit category stored is by default the one currently associated to the Account in ACCOUNT table.

If it is not defined, one shall take the ones in ACCOUNT CREDIT LIMIT associated to this account. Since however, the credit limit is per subscription, the highest internal code will be taken, which is assumed to be the latest one. This is an approximation which could be wrong.

Concretely:

nvl(DWR_ACCT.CRDT_CTGRY_KEY,

max(DWB_ACCT_CRDT_LMT. CRDT_CTGRY_KEY)

DEBTMO8

A debt agreement is a formal or informal agreement with the customer or account in debt to pay the bill within a certain time.

It might be associated to the standard agreement (contract) as AGREEMENT TERM, whose type could be 'Max Debt Age' or any Debt related term. The status of this agreement Term should be valid.

It may also be associated with a PAYMENT PLAN (see SID 12.5) but it is not considered here.

For simplification and performance, Oracle Communications Data Model assumes that a DEBT agreement is in place if the field EXTENDED DUE DATE of Invoice is not null.

A successful debt agreement is defined with a full payment received with extended due date not null while an unsuccessful debt agreement has the full payment received indicator not set.

Concretely:

For current agreement

the invoice status is open and nvl(ext_due_Dt, to_date('1970','yyyy')) >Date Considered

For all agreement success

DEBTMO9

The organization business unit defined in ACCOUNT DEBT MONTH should be the one associated with the debt. If it is not defined, one shall use the one currently associated with the account (usually equal to the one through which the account was created).

Current default is the one associated with the Account.

Be aware that currently, only the one associated with the account is used.

DEBTMO10

The unit of measure associated with the debt is in fact the currency of the debt amount.

DEBTMO11

The number of invoice in debt (see column INVC_IN_DEBT_CNT) is read by default in ACCOUNT DEBT. Hence, it should be calculated by the ETL that populates it.

Nevertheless, if this information is not defined, the definition of invoice in debt is the number of invoices whose Full payment Received Indicator is not set and whose DUE DATE is passed.

There may be multiple invoices from the same "billing unit" (that is, a same agreement with a given billing cycle over several months, hence including several unpaid bills) and from different billing units (different agreement, with different subscriptions and access numbers and possibly different billing cycles).

DEBTMO12

Disputed Amount is the sum of all invoices of the account that are in dispute, that is, whose status is open and whose dispute amount is not null.

It is assumed that each invoice can be independently under dispute and that the dispute amount of the last invoice does NOT contain any amount of previous dispute (and still open) and the billing date of the invoice must be up to 90 days before the debt starting date.

SUM(DISPTE_AMT) where invc_stat_cd not like '5%'

bllng_dt Must be >= debt-strt_dt-90 days

DEBTMO13

The current invoice balance amount and current due date is based on the biggest billing date of any open invoices associated with this account.

If several invoices are billed the same day, their amount will be summed. If a given invoice comes later than all others, only this invoice will be counted!

DEBTMO14

The employee associated is either the latest one directly involved in the debt collection (employee code) or the call center agent (call center agent).

It is therefore assumed that employee code and call center agent code correspond if they are the same party.

DEBTMO15

The debt escalation level corresponds to the Priority code type of a given interaction.

If the account debt has no debt escalation level code associated, the priority code type of the latest interaction around debt collection will be taken (Code 6% -see lookup).

DEBTMO16

Invoice debt age is based on DUE DATE, not on extended due date.

DEBTMO17

The definition of an "invoice in debt" is the latest invoice which has been sent to the customer, is closed from a billing cycle perspective but open from the balance point of view, has not been fully paid on due time.

Hence, only count one invoice in debt per "bill or billing unit" (group of related invoices created or to be created under a certain agreement for a certain service with a determined billing cycle). Only the latest completed billing cycle shall be considered, whether the due amount is carried forward or not into the next invoice.

DEBTMO18

It is assumed that all information in ACCOUNT DEBT is correctly stored as a cumulative amount in each AMT columns until all money is recovered, adjusted or written-off, including the day it closes.

See also DEBTMO19

DEBTMO19

In the target table, Organization Business Unit should be fed with the part of the CSP Organization that deals with the DEBT of this account.

COLLECTION AGENCY should be fed with the external collection agency engaged to collect the debt if any.

If COLLECTION TYPE is filled with "External", COLLECTION AGENCY is expected to be filled (and not being "-5000" or "unknown") and reciprocally.

DEBTMO20

Adjusted amount are only those associated with any invoice and whose adjustment date is greater or equal the Debt Start date.

If this information is null, ACCOUNT BALANCE IMPACT will be checked with similar conditions.

DEBTMO21

The Total work duration of a debt is defined as the number of days between the debt assignment to someone and the date considered (or the end of debt).

if HOLD_DT is null, nvl(ACCMPLSH_DT, DayConsidered) -ASGN_DT

if HOLD_DT not null but RESUME_DT is null, HOLD_DT-ASGN_DT

if HOLD_DT & RESUME_DT not null, nvl(ACCMPLSH_DT, DayConsidered) -ASGN_DT-(RESUME_DT-HOLD_DT)

DWL_ACCT_ADJ_RSN

ACCT_ADJ_RSN_CD

'PNLTY', 'WVNG', 'RFND', 'DSPT', 'WRTOFF'

Penalty, Waiving, Refund, Dispute, Write-Off

Distribution of details of adjustment when not already present.

DWB_INVC

FULL_PAY_RCVD_IND

'Y', 'N' or NULL

Fully paid, Not fully paid

Only to count the number of invoices still to be paid (or in-debts)

DWR_AGRMNT

STAT_CD

'c', 'f' 'p'

'Closed' (not active anymore) 'future activation', 'pending activation'. Any other mark would mean active.

Optionally needed.

DWD_ACCT_DEBT_DAY

COLLCTN_TYP

'Internal', 'External'

Whether the Collection is done internally (default) or given to a collection agency ('External').

ACCT_1ST_ACTVTY_1

For every account existing at any point in type, this table must be filled. There must be at least 1 row per account even if subscriptions and offerings are not known.

ACCT_1ST_ACTVTY_2

Information about Activity is about USAGE. Any usage of any type must be taken into account.

ACCT_1ST_ACTVTY_3

One needs to distinguish between "Terminating" (or Incoming) usage from "Originating" (or Outgoing) Usage through the Call direction.

CALL_DRCTN = 'T' for terminating or 'O' for Originating. "-5000" or Unknown is not acceptable.

ACCT_1ST_ACTVTY_4

The Payment considered is any payment AFTER the first activation. Hence, for Prepaid, it is the 1st recharge. For postpaid, it is the 1st payment after invoice has been issued.

ACCT_1ST_ACTVTY_5

Links to Product Specification and Product Offering is done through Subscription.

ACCT_1ST_ACTVTY_6

In case of multiple access methods, the access Method considered shall be the Main one associated with the offer.

ACCT_1ST_ACTVTY_7

Once all columns have been filled, a given row will not be updated anymore. Update shall run/be tried as long as one column is empty (null).

ACCT_1ST_ACTVTY_8

It is suggested to leverage the DERIVED layer of VOICE, VAS and DATA to get the 1st account activity instead of running at BASE layer.

This assumes however that these DERIVED have been filled upfront!

ACCT_1ST_ACTVTY_9

VAS usage assumes it is always end-user originating call.

Set call direction Code to be 'O' by default

ACCT_1ST_ACTVTY_10

Day level is enough as approximation. We are not interested to know the exact time.

DWL_CALL_DRCTN

CALL_DRCTN_CD

INTRACN_DRCTN_CD

T,

O,

U

Terminating,

Originating

Unknown (default)

ACCT_LAST_ ACTVTY_1

See also:

ACCT_1ST_ ACTVTY_1

For every account existing at any point in type, this table must be filled. There must be at least 1 row per account even if subscriptions and offerings are not known.

With Payment (of any type like standard payment or recharge or refund or transfer), one shall associate the unknown product offering and unknown product specification. It shall be stored independently from the usage activity itself.

In clear, it means that, per account, there must be 1 row for 1st payment information, and 1 row per product Offering / product Spec combination associated with 1st usage.

ACCT_LAST_ ACTVTY_2

Information about Activity is about USAGE. Any usage of any type must be taken into account.

ACCT_LAST_ ACTVTY_3

One needs to distinguish between "Terminating" (or Incoming) usage from "Originating" (or Outgoing) Usage through the Call direction.

CALL_DRCTN = 'T' for terminating or 'O' for Originating. "-5000" or Unknown is not acceptable.

ACCT_LAST_ ACTVTY_4

The Payment considered is any payment AFTER the first activation. Hence, for Prepaid, it is the last recharge. For postpaid, it is the last payment after invoice has been issued.

ACCT_LAST_ ACTVTY_5

Links to Product Specification and Product Offering is done through CDR information a priori or Subscription otherwise.

This does not apply to payment.

ACCT_LAST_ ACTVTY_6

In case of multiple access methods, the access Method considered shall be the Main one associated with the offer.

Currently, every used access method will be considered, if they are defined in the CDRs with a product offering and a product specification.

ACCT_LAST_ ACTVTY_7

For a given account, all columns shall be updated each run until the account is fully deactivated or terminated within the period considered by the intra-etl.

The accounts to consider are any not terminated. Basically, this means one runs over any activity (usage) of any account, whether declared or not, and insert or update the corresponding row in the derived table.

That is when account stat_cd like '4%' or '5%' before L_START_TIME and never active in-between.

ACCT_LAST_ ACTVTY_8

ACCT_LAST_ ACTVTY_9

VAS usage assumes it is always end-user originating call.

Set call direction Code to be 'O' by default

ACCT_LAST_ ACTVTY_10

Day level is enough as approximation.

ACCT_LAST_ ACTVTY_11

This rule is obsolete.

DWL_CALL_DRCTN

CALL_DRCTN_CD

INTRACN_DRCTN_CD

T,

O,

U

Terminating,

Originating

Unknown (default)

DWL_PYMT_MTHD_TYP

PYMT_MTHD_TYP_CD

Lookup for type of payment For example:

0 PRPD

1 INVC

2 TRNSFR

3 CC

5 DD

6 DC

11 CASH

12 CHQ

13 WTRNSFR

14 PAYORDR

15 PSTORDR

16 VCHR

17 DRCTDPST

55 BNK

20 POINTS

99 OTHR

-5000 UNKNOWN

DWL_ACCT_RFND_RSN

ACCT_RFND_RSN_CD

PRSNT

INVCADJ

-5000

AGRMNT1

The Time Window for this table is the month.

This table contains the status for the month as of that date.

Start date should always be the 1st day of the current month (at 00:00).

End date should change every day (included).

For a given run, it will take the last day available within the time period given.

AGRMNT2

The leading table is AGREEMENT TERM (base) whose validity period contains the first of the month considered.

The associated AGREEMENT ITEM and AGREEMENTs will be then taken into account.

AGRMNT3

The Product Offering dimension used in this table originates from AGREEMENT and not from AGREEMENT ITEM. Hence, it is assumed that only the "main" product offering will be looked at and will be stored in the AGREEMENT table.

AGRMNT4

A change in Customer (Customer Key) implies a change in Agreement (Agreement Key changes because Customer Key it is associated to changes) and all the Agreement Item and Agreement terms associated.

AGRMNT5

Cumulated Term Value (used for AMORTIZED ARPU AMOUNT) and Remaining Agreement Value are calculated based only on Agreement Term that are associated with monthly fees and whose Unit of Measure is the month).

AGRMNT6

Due to the complexity of the calculation of Agreement Value Loss if one has to consider the true usage and revenue associated with a given agreement, Oracle Communications Data Model will restrict its definition of AGREEMENT LOSS AMOUNT by the CONTRACT VALUE (defined by CSP) in agreement term associated to this agreement at ANY TIME during the life cycle of this agreement. One assumes however that it will come only once at agreement starting date.

AGRMNT_CHG1

Old and New agreement must be linked within the reference table through a "PREVIOUS AGREEMENT KEY" field (associated with the new agreement), to be considered as Agreement change.

There is no direct condition on the elapsed time between the closure of the old agreement and the start of the new one, as long as they are linked with one another (see also AGRMNT_CHG5).

There is no limitation in the type of offering (Prepaid, Postpaid and migration between the 2) in the code.

A simple surrogate key change is sufficient to feed this table (as long as the required link is present).

The date of the change considered is the date at which the new agreement starts.

DWR_AGRMNT.PREV_AGRMNT_KEY is not null.

This means that one could easily limit the number of entries in the target tables by limiting the cases when the PREVIOUS AGREEMENT KEY is filled or by simply adding custom conditions on "CHANGE REASON CODE".

AGRMNT_CHG2

Win and loss amount correspond to the "Contract Value" that the Communications Service Provider decided to associate with the old (resp. new) agreement.

AGRMNT_CHG3

For the Count of Subscription Old and new, one wants to count the number of active subscriptions that ends exactly on the day the OLD agreement ends. Similarly, one counts all the subscriptions that start on the day the new agreement start

This number of subscriptions does NOT yet appear in the target table but could be easily added.

AGRMNT_CHG4

Currently, the customer shall stay identical.

The FROM (old) and TO (new) customer key in agreement (old/new) shall be identical

If one wants to remove this condition, one just needs to add a "FROM_CUST_KEY" to the table and feed it with the customer key of the OLD agreement, and remove the where condition "Agreement_old.cust_key= agreement_new.cust_key"

AGRMNT_CHG5

The old agreement must end within the time period considered (Intra ETL parameter).The new agreement must start within the time period considered.

This condition could be loosened by removing the condition that the OLD agreement must end within the time period considered. It could be required that it ends anytime before the end of the period or between the end of the period and a certain amount of days before that.

With this change, one remove any constraint on the time elapsed between the old and the new contract but one takes the risk to deteriorate performance (because one might take all ended agreements before this time in the sub-query if one does set any window!).

CANBLZTN_1

Cannibalization is defined by an agreement change with the same customer (same surrogate key!) and with the change happening within a day.

Cannibalization has become obsolete

OBSOLETE

AGRMNT_ RVN_DAY0

Time window: All fact columns shall represent the status from the beginning until the end of the period considered (here: last passed day).

It is NEVER a status or a balance at the end of the period. Hence, to have the revenue of a given combination or Product Offering and Product Spec, one shall sum each day of the period considered.

Since Revenue Day has to do with Usage (hence CDRs), it is very important to consider the Business Rule RVN_DAY11 for LATE CDRs.

AGRMNT_ RVN_DAY1

Content of DWD_RVN_DAY: stores all information according to all the dimensions for a given day.

In particular, any combination of PRODUCT OFFERING and PRODUCT SPEC can be added wherever needed. Typically, when there is more than one default composite Product Spec to a given Product Offering, or when one wants to have the details of say the handset model chosen depending on various options associated with a given Product Offering.

Since all revenue columns are sum-able, it contain the statistics around any PRODUCT OFFERING alone (whatever the Product Spec), or any PRODUCT SPEC.

See also RVN_DAY10 and CNT_DAY2 rules.

AGRMNT_ RVN_DAY2

Definition of the Revenue Types. There are seven types of Revenue:

Billed: Any amounts appearing either on a bill sent (postpaid) OR (prepaid) when they already paid for their use of a service OR the monetary amount left expired (.

Unbilled: all the other cases. Ignored (that is, columns not fed) in Oracle Communications Data Model.

Billed earned: normal case where customer has been billed for the services/usage he has used.

Unbilled earned: customer is billed upfront; future cycles fall under unearned revenue.

Billed unearned: this is the case when customer has performed some usage (that is, made a call), but has not yet been billed for that usage.

Unbilled unearned: an example here is a payment a customer may make upfront, but future services to be rendered are canceled (or simply overpayments they may have made).

Previously billed earned: Billed earned from previous period considered.

For more information, see Oracle® Communications Billing and Revenue Management Collecting General Ledger Data.

Oracle Communications Data Model deals only with billed and unbilled revenue, which includes earned and unearned revenue as follows:

Billed = billed earned + billed unearned + previously billed earned.

Unbilled = unbilled earned + unbilled unearned

"Billed" means it appears on an invoice sent to customer ('OPEN')

Oracle Communications Data Model includes:

UNEARNED will be ignored and set to 0. One assumes immediate revenue recognition.

UNBILLED will also be set to 0 by default as it would require quite complex and costly calculation.

Note: if the Billing status code is kept in the usage calculation, one could add the unbilled usage to the target table.

Recurring Forward Fees are in general set to 0 unless they are invoiced (and they will be considered as earned).

Additional definitions:

Gross Revenue: reports the total of net and discounted revenue.

Discount or Discounted Revenue: reports the balance impacts of discounted revenue.

Net Revenue: reports the amount of revenue that remains after applying discounts.

Tax: reports the amount of taxes calculated. This data is used for collecting G/L data based on tax codes. Tax is assumed to never be applied in any amounts except on Invoices.

Corresponding SQL Statement

Any Prepaid Usage or Expired Monetary Balance shall be considered as "Billed".

For Postpaid, "Billed" is when DWB_INVC.STAT_CD ='OPEN' or 'CLOSED';

Unbilled will be all other cases. Because the rule is depending on the billing system itself, and the internal processing, Unbilled revenue will be ignored (present as column but NOT FILLED).

AGRMNT_ RVN_DAY3

More Definitions around Revenue:

Prepaid Services Revenue (Excluded): this should count all prepaid usage revenue for the period + any expired prepaid revenue even if not used.

Billed or unbilled is not relevant in this case since that is not applicable to prepaid. It is always considered as "billed".

Postpaid Services Revenue (billed): billed postpaid usage revenue (all services) + billed cycle fees (for example, monthly) + recurring equipment rental (for example, CPE rental).

Equipment Revenue (billed): revenue associated with sale of any devices (for example, handsets) and accessories.

Other Revenue (billed): this should include other non-recurring customer revenue such as one-time purchase or activation fees, late payment fees, cancellation fees, and so on.

Total Gross Revenue (billed): prepaid services revenue + postpaid services revenue + equipment revenue + other revenue.

Total Net Revenue (billed): total gross revenue - deductions (for example, taxes, refunds, write-offs).

AGRMNT_ RVN_DAY4

Condition Definitions:

Usage Revenue: Revenue coming from service usage (pay per use) and calls (pay on event, duration and/or volume). The sources will be VOICE CALL DAY, DATA USAGE DAY, VAS USAGE DAY (and not SUPPLEMENTARY SERVICE USAGE DAY).:

The field TOT_BLLD_AMT or BLLD_AMT shall be used when PLN_TYP='Prepaid' (and day corresponds).

The postpaid Revenue shall come from

DWD_INVC_DAY: USG_RVN_BLLD when it is independent of Product Offering and Product Spec (or this number could be used as X-check). It shall come from DWB_INVC_ITEM when the product Offering and/or the Product Spec is required.

Expired Revenue (excluded): Revenue that comes from prepaid expired amount.

AGRMNT_ RVN_DAY5

Roaming Revenue:

Roaming Revenue is considered for Roaming events on CSP's network, whose paying party is an external operator. It assumes that any event from WIRELESS ROAMING EVENT only contain such events.

It is also expected RMNG_EXTRNL_OPRTR_KEY or EXTRNL_OPRTR_KEY cannot be both null.

For Billed Revenue, Invoices must be of Type "RMNG STTLMT" (Roaming Settlement).

Roaming domestically (MVNO) is allowed.

AGRMNT_ RVN_DAY6

Transfer: A transfer (Account, Agreement, Product Subscription) is a change of ownership and is recognized as such if and only if:

The "Code" of the entity is not changed

The Agreement and its term are not changed. The Product Offering associated is not immediately changed.

Only the ownership changes

The Status Reason Code is associated with Transfer.

There is no time without ownership.

STAT_CD like '2%' (New) and SUBSTR( STAT_CD,1,1) in ('1','2') (old) and new.eff_from_dt -old.eff_to_dt<=1s

AGRMNT_ RVN_DAY7

Geographic County corresponds to the County of the Primary Address of the Customer when defined.

AGRMNT_ RVN_DAY8

Organization Business Unit, Sales Channel and Sales Rep : correspond to the respective Sales Channel and Sales Rep directly associated with the Product Offering and Product Spec of the corresponding product subscription.

If unclear or undefined, one shall take the last one associated with the corresponding Customer (in the customer table).

AGRMNT_ RVN_DAY9

Cost Center is the Cost Center associated with the Organization Business Unit considered (if uniquely defined). Keep it 'UNKNOWN' otherwise.

Not used

AGRMNT_ RVN_DAY10

Product Offering Level and Product Spec Level: (follow-up of RVN_DAY1)

When both set to 0, both PRODUCT SPEC and PRODUCT OFFERING should be defined (normally not unknown, although unknown will be accepted).

If one of the level is set to 1 and the other to 0, it collects the statistics according to the entity whose level is set to 0, independently of the other. The other will be forced to be 'unknown'.

If both levels are set to 1, it collects the global statistics independently of Product Offering or Product Spec. Both are forced to be 'unknown'.

This is necessary to allow the calculation of some KPIs.

See also CNT_DAY10 rule.

OBSOLETE since the facts are sum-able.

AGRMNT_ RVN_DAY11

Late Usage or Late CDRs:

Usage that come later shall be taken into account. Hence, the LOAD DATE should be used as part of the criteria to consider a row or not.

But the REVENUE associated shall be attributed to the right day, corresponding to the Start Day of the event.

This rule could be changed for very late delay (> 3 full months). Those revenues should be excluded.

RVN_DAY12

For Prepaid, the calculation assume that the Sale Channel and Sales Rep information are associated with the Prepaid subscription. These two fields will be then used.

Not relevant for Agreement Revenue Day

RVN_DAY13

Due to the way to feed DWD_AGRMNT_RVN_DAY (for performance), there will be 1 row per combination of key columns: 1 for postpaid and 3 for Prepaid.

As a consequence,

- Empty revenue columns shall be filled with 0 and not with null.

The prepaid columns will stay empty.

AGRMNT_ RVN_DAY14

Most Key columns will come from the information out of the Product Subscription.

In particular, it is expected that the following columns of PRODUCT SUBSCRIPTION are filled (in bold, those critical):

Organization Business Unit Code

Channel Code (preferred, priority) or Campaign Channel Code

Product Offering Code (!)

Product Spec Code (!)

Customer Code (or at least Account Code).

Additionally, it is expected that every PRODUCT SUBSCRIPTION has got an associated AGREEMENT ITEM that points to it.

In DWR_PROD_SBRP, check that the following columns are filled:

ORG_BUS_UNIT_KEY

CHNL_KEY or CMPGN_CHNL_KEY

PROD_OFR_KEY

PROD_SPEC_KEY

CUST_KEY or ACCT_KEY.

Check also that any rows in DWR_PROD_SBRP are pointed at by some rows in DWB_AGRMNT_ITEM. This should be particularly true for the options that impact rating but are not always.

AGRMNT_ RVN_DAY15

Plan Type is only 'Postpaid'. Hybrid product offering cannot be considered and will be ignored (at least for billed usage).

If PLN_TYP <> 'Postpaid', all billed usage related columns will be ignored.

AGRMNT_ RVN_DAY16

Roaming Revenue concerns only revenue that comes from Roaming TAP OUT file. TAP IN file are NOT considered as part of Roaming Revenue.

This is for RMNG_RVN_UBLLD.

AGRMNT_ RVN_DAY17

Interconnect Revenue only concerns revenue from passing traffic. The A & B numbers (if defined at all) are NOT belonging to the Service Provider.

Interconnect Traffic will be recognized by a non null revenue associated with INTERCONNECTION field. Cost will be ignored here.

It is assumed that all interconnection traffic will be stored in FIXED LINE CALL EVENT (whatever the type of call).

INTCONN_RVN>0 is sufficient as criteria.

AGRMNT_ RVN_DAY18

SALES REPRESENTATIVE and COST CENTER columns are currently ignored.

The exact formula used is: nvl(nvl ( AGRMNT.SL_CHNL_KEY, AGRMNT.CHNL_KEY), CUST.CHNL_KEY) for SL_CHNL_KEY and nvl(AGRMNT.SL_CHNL_RPRSTV_KEY, -5000) for SL_CHNL_RPRSTV_KEY

AGRMNT_ RVN_DAY19

For all USAGE, Billing Status Type code successful (that is, BILLED) is explicitly required.

BLLG_STAT_TYP_CD='SUCC' is expected.

The "CASE WHEN" conditions could be all removed (to win time) if one assumes that all usage events stored at the derived layer will always have the same BILLING STATUS TYPE CODE (assumed to be 'SUCC' only).

AGRMNT_ RVN_DAY20

It is assumed that the ORGANIZATION BUSINESS UNIT (as well as others like GEO COUNTY CODE or CUSTOMER TYPE CODE) stored in the derived USAGE tables (VOICE CALL DAY; DATA USAGE DAY and VAS USAGE DAY) are identical to the one set in the invoice.

One could add that it should be equal to the one in AGREEMENT to be coherent but that would add another constraint which is not obvious and not strictly necessary. Hence, it is NOT required today.

There is an explicit full join with these conditions between invoice and the usage tables.

If this is not the case, the USAGE related statement must be modified to force the ORGANIZATION BUSINESS UNIT there to be equal to one of the invoice

AGRMNT_ RVN_DAY21

It is assumed that a PRODUCT SUBSCRIPTION is UNIQUELY associated to a given AGREEMENT ITEM.

We assume that PROD_SBRP_KEY exists only once in any rows of DWR_AGRMNT_ITEM.

Any repeat will lead to multiple lines for the same product subscription, multiplying the same revenue as the number of rows in AGREEMENT ITEM with a given PROD_SBRP_KEY.

This business rule is different from RVN_DAY21

AGRMNT_ RVN_DAY22

Information out of the invoice will only be considered when the billing date of the invoice is between the ETL (Time) Parameters. The status of the invoice is ignored. This implies two approximations:

The invoice is complete at billing date and will NOT be corrected or updated after that date (or this update will not be considered - like the invoice status change, the partial or full payment after billing date, and so on).

Past invoices added at a later time in Oracle Communications Data Model will not be considered.

Note: The status of the invoice is ignored only for the selection of the invoice and invoice items. However, the association to an INVOICED (BILLED) or UNBILLED column is based on the status invoice:

INVOICED when the 1st 2 characters of Invoice Status Code is between 20 and 59 (both limits included)

Unbilled in all other cases.

As customization, one could deal with LOAD DATE and INVOICE CODE to find whether an invoice has already been considered or not. But this could be a heavy job to do it correctly.

AGRMNT_ RVN_DAY23

Discount Revenue: On top of being positive (and to be subtracted from all revenues), it is assumed that only DISC_AMT and related columns have to be considered.

This also mean that if an invoice item is of type DISCOUNT (INVOICE ITEM TYP CD like 6%), only DISC_AMT (and related) should then be filled, and not CHRG_AMT.

AGRMNT_ RVN_DAY24

Invoice items associated with recurring fees will be associated with only price type code for forward fees (Price_TYP_CD like '11%') or arrear fees ((Price_TYP_CD like '12%').

Any other combination is currently excluded from the REVENUE DAY derived table.

AGRMNT_ RVN_DAY25

In this Oracle Communications Data Model, CUST3MO_IND and SBRP1MO_IND are currently not in use.

A minor modification of the code could allow end-users to leverage them.

CANBLZTN_1

Cannibalization is defined by an agreement change with the same customer (same surrogate key!) and with the change happening within a day.

CMPGN1

Any response to any promotion and campaign shall be taken into account. Campaign run over phone (SMS or direct call) without entry in promotion response shall also be considered.

It could be easily extended to any type of interaction for a campaign (shop, and so on).

Limit search to

DWB_PRTY_PRMTN_RESPN and

DWB_EVT_PRTY_INTRACN

CMPGN2

In case more than one campaign channel are used for a given campaign associated with a response, the campaign channel will be chosen to be UNKNOWN

if only 1 Campaign Channel available for the campaign, take it. Otherwise, put -5000

CMPGN3

Contact Lists and scripts may not be defined when a campaign is run. In such case, they will be set to unknown.

Use -5000

CMPGN4

The number of contact is independent on the success of the contact (answered or not).

For EVENT PARTY INTERACTION CALL, it will be similar to CALL COUNT.

CMPGN5

The number of activations is based on the effective activation of the customer within a day of the interaction. The status of the subscription determines the activation.

A customer order is NOT an activation.

For promotion response, you shall look into subscription with the offer to find out when it was activated.

CMPGN6

There is no difference between an SMS campaign and a campaign over the Phone.

In both case, MEDIA OBJECT shall be 'PHONE'.

CMPGN7

When there is no row in PARTY PROMOTION RESPONSE associated to a campaign (over the phone in our case), the PROMOTION RESULT CODE shall contain the OVERAL RESULT CODE of the Interaction.

CMPGN8

Reactivation Count is based on EVENT ACCOUNT table. When the event type code is 'RECNCT', it will be counted whether it was suspended or full deactivated.

The time span between the reactivation and the call should be less than a month.

CMPGN9

For Recharge count, the calculation assumes that any existing customer who accepts a Prepaid offer is a recharge.

When DWR_PROD_OFR.PLN_TYP= 'PREPAID' then count 1.

CMPGN10

A successful recharge is defined by the above + a row in ACCOUNT PAYMENT (associated with recharge) and a successful transaction.

CMPGN11

The total response time is calculated as such:

For a promotion response, the time between the campaign start date and the positive or negative response date. (no response means null).

For a call, the time between the call (call start date) and the final response (accepted/refused).

For a letter, it should be the time between the letter was sent and the final response (accepted/refused).

DWL_INTRACN_RSLT_TYP

Lookup for available types of Interaction Relation:

1000 RSLVD

2000 OFRACCEPT

3000 INTEREST

5000 PENDING

6000 DROP

7000 ABDN

8000 RFSD

9000 NEVERCALL

-5000 UNKNOWN

DWL_PRMTN_RSLT_TYP

Lookup for available type of Promotion Relation:

OFACCPTD Offer Accepted

ATRPRVNT Attribution Prevented

-5000 Unknown

DWL_PROD_SBRP_STAT_TYP

DWL_ACCT_EVT_TYP

Lookup for available type of Account Event.:

• TMNATMPT Termination Attempted

• TMNT Termination

• CRT Create

• ACTVTN Activation

• DISCNCTN Disconnection

• INDEACT Involuntary Deactivation

• VOLDEACT Voluntary Deactivation

• INSUSP Involuntary Suspend

• INSUSP Involuntary Suspend

• VOLSUSP Voluntary Suspend

• RECNCT Reconnect

• RFIS Reactivation From Involuntary Suspend

• -5000 Unknown

CNT_DAY0

Time window: Count represents the status (could be a sum or "as of" depending on the nature of the things that is being counted) for the primary keys combination.

In general, all columns whose names finish by "COUNT" represent the status at the end of the period considered (here: last passed day) and of the other primary keys used as well. It is not sum-able.

For example, on April 27th in the morning, the COUNT of active customer of Month of April would be # as of April 26th".

All columns finishing by "COUNT THIS PERIOD" correspond to a delta between the time period key and the previous time period key (whether win or loss) of the considered period (a day in this case). It is always a positive number and the column meaning will tell whether win or loss. It is sum-able across a time period greater than a day.

CNT_DAY1

Entity Definition:

Household: A Building if it belong to one customer only, 1 level or 1 flat otherwise:

Customer: A party that has a customer role with respect to the Service Provider

Account: The financial vision of the customer for the service provider.

Agreement: A tacit or explicit relationship between a customer and the service provider. It is typically for postpaid only (that is, Agreement = contract), although agreements can also be defined for Prepaid if required. SLA are not explicitly counted here.

Main or Prime Subscription: A critical Product Subscription that may carry other and without which there is no access to the network or service.

Subscription: Any product subscription

Access Method: how a customer accesses or utilizes a service from the Service Provider.

User: Custom - User Defined Field - not used.

Line: Custom - User Defined Field - not used.

Please note: None of these definition requires a specific Status of the corresponding entity.

CNT_DAY2

Content of DWD_CNT_DAY:

It shall store all information according to all the dimensions for a given day.

In particular, any combination of PRODUCT OFFERING and PRODUCT SPEC can be added wherever needed. Typically, when there is more than one default composite Product Spec to a given Product Offering, or when one wants to have the details of say the handset model chosen depending on various options associated with a given Product Offering.

On top of the above, it shall contain statistics around any PRODUCT OFFERING alone (whatever the Product Spec), or any PRODUCT SPEC alone. (see business rule CNT_DAY10 with PRODUCT OFFERING LEVEL and PRODUCT SPEC LEVEL).

Same remark with PRODUCT SPEC TYPE

Finally, it shall contain Statistics independently of any PRODUCT OFFERING and PRODUCT SPEC.

CNT_DAY3

Status Definition:

Various statuses are used in DWD_CNT_DAY. Their definitions differ slightly from the usual definition associated with an entity.

Pending Activation: Not active yet but a process is on-going. Pre-Activated Prepaid Cards or Future Activation (with signed contract) are in such state.

Pre-Activated: Only for off-the-shelf products (typically cards) with immediate use possible. It is a sub-type of Pending Activation.

Active: A status that is neither Pending Activation nor Deactivated or Terminated. Hence, reactivated, suspended or dormant statuses are considered as active.

Inactive: Cancelled, Deactivated or Terminated.

Suspended: A specific status in which customer can receive calls but cannot actively calls except emergency or free numbers.

Reactivated: A previously Suspended or Deactivated Entity that is back to active.

Cancelled: The (approved) cancellation of a given request of entity within a given timeframe. The canceled entity could have been activated or Pre-Activated. It is considered as Inactive but not as Deactivated.

CNT_DAY4

Condition Definitions:

Customer with SLA: Customer which has got at least 1 currently valid SLA associated.

Account with SLA: As Customer for Account.

Agreement with SLA: Agreement which has got at least 1 SLA associated: necessarily >= SLA count since an SLA is a type of agreement!

Main Subscription under SLA: Subscriptions with Essential Indicator set that are under an active SLA.

New (Status) Count: Number of Entities with Status with Entity Activation is within last X months.

Churned Count: Number of Deactivated Entities (household, Customer, Account).

Transferred: Entity "deactivated" for a given Customer and immediately "reactivated" under another, while keeping all the same. It can apply to Account and lower. Please see specific business rule CNT_DAY. It only counts the outgoing entities.

New <Entity> due to Agreement or Account Transfer: Counts the Entity that needed to be created following a transfer. (for example: I give my contract to my daughter who was not defined originally).

Newly Activated and Cancelled: The entity has been activated AND canceled within the period considered (Day!).

Future <Entity> Cancelled: The entity has an activation date sometimes in the future and AND has been canceled before it started.

Active Entity Count for Month/Quarter/Year: it counts ANY distinct entity that has been active at anytime within the period considered. A minimum of 1 second is currently considered.

Voluntary Suspension: Customer triggered to stop service (theft, holidays…). Temporary state ('suspended')

Involuntary Suspension: Service Provider triggered (non-payment, fraud…)

Voluntary Termination: Customer triggered (relocation, contract ends - no renewal…). Permanent State (Deactivated). Specific Claw-back actions are allowed.

Involuntary Termination: Service Provider triggered (death, fraud, debt …). Permanent State (Deactivated). No claw-back actions allowed.

CNT_DAY5

Services: This is only for the SPECIAL VIEW on DWD_CNT_DAY.

Broadband: The Product Offering or Product Spec combination contains or applies to Broadband Service.

Wireless: as above for Wireless (2-4G).

Hometel: as above for simple PSTN Wireline service (no VoIP).

PayTV: As above for PayTV (whatever the deliver channel: wireless, broadband,…).

With <Service>: The Product Offering and Product Spec combination contains <Service>.

With <Service> in Convergent Package: The Product Offering and Product Spec combination contains <Service> as part of a multi-play offering.

Product Subscription Related to <Service >: the corresponding Product Subscription only applies if a <Service> is up & running.

CNT_DAY6

Transfer: A transfer (Account, Agreement, Product Subscription) is a change of ownership and is recognized as such if and only if:

The "Code" of the entity is not changed

The Agreement and its term are not changed. The Product Offering associated is not immediately changed.

Only the ownership changes

The Status Reason Code is associated with Transfer.

There is no time without ownership.

CNT_DAY7

Geographic County corresponds to the County of the Primary Address of the Customer when defined.

CNT_DAY8

Organization Business Unit, Sales Channel and Sales Rep: correspond to the respective Sales Channel and Sales Rep directly associated with the Product Offering and Product Spec of the corresponding customer.

If unclear or undefined, one shall take the last one associated with the corresponding Customer (in the customer table).

CNT_DAY9

Cost Center is the Cost Center associated with the Organization Business Unit considered (if uniquely defined). Keep it 'UNKNOWN' otherwise.

CNT_DAY10

Product Offering Level and Product Spec Level: (follow-up of CNT_DAY2)

• For PROD_OFR hierarchy: 3 levels only: TPRO_OFR, PLN_TYP and PROD_OFR_CD: Possible values are:

• 'TPROD_OFR' (total PROD_OFR) or

• 'PLN_TYP'

• 'PROD_OFR_KEY' for the lower lvl.

In PROD_OFR_KEY column, the content will be respectively:

• '-5000' for highest level

• 'Prepaid' or 'Postpaid' (only)

• The PROD_OFR_KEY (number)

For PROD_SPEC_LVL: the 3 levels would be

• 'TPROD_SPEC' for highest level, or

• 'PROD_SPEC_TYP_CD' for middle level

• 'PROD_SPEC_KEY', for lowest level

In PROD_SPEC_KEY column, you would have respectively:

• '-5000' for highest level

• The PROD_SPEC_TYP_CD for middle level (so the PROD_SPEC TYP on which you aggregate all sub-PROD_SPEC_KEYs), or

• 'PROD_SPEC_KEY', for lowest level

When both set to lowest level, both PRODUCT SPEC and PRODUCT OFFERING should be defined (normally not unknown, although unknown will be accepted).

If one of the level is set to a higher level and the other to lowest, it collects the statistics according to the entity whose level is set to the lowest, aggregated up to the level of the other.

If both levels are set to highest level, it collects the global statistics independently of Product Offering or Product Spec. Both are forced to be '-5000'.

This is necessary to allow the calculation of some KPIs.

CNT_DAY11

Household and County: Households and Counties hardly change. Hence, the Surrogate Key will be assumed not to change for a given Household or County.

DWR_CUST

DWR_ACCT

DWR_AGRMNT

DWR_PROD_SBRP

DWR_ACCS_MTHD

STAT_CD

'1%'

Pending Activation

DWR_AGRMNT

DWR_CUST

DWR_ACCT

DWR_PROD_SBRP

DWR_ACCS_MTHD

STAT_CD

'15%'

Pre-Activated

DWR_CUST

DWR_ACCT

DWR_AGRMNT

DWR_PROD_SBRP

DWR_ACCS_MTHD

STAT_CD

'2%'

Active

DWR_ACCT

DWR_PROD_SBRP

STAT_CD

'29%'

Suspended

DWR_CUST

DWR_ACCT

DWR_AGRMNT

DWR_PROD_SBRP

DWR_ACCS_MTHD

STAT_CD

'4%'

Deactivated / Terminated

DWR_CUST

DWR_ACCT

DWR_AGRMNT

DWR_PROD_SBRP

DWR_ACCS_MTHD

STAT_CD

'5%'

Cancelled

DWR_CUST

DWR_ACCT

DWB_AGRMNT_STA

DWR_PROD_SBRP

DWB_ACCS_MTHD_STAT_HIST

PRMRY_STAT_RSN_CD

ACCT_STAT_RSN_CD

AGRMNT_STAT_RSN_CD

PROD_SBRP_STAT_RSN_CD ACCS_MTHD_STAT_RSN_CD

DWR_CUST

DWR_ACCT

DWB_AGRMNT_STA

DWR_PROD_SBRP

DWB_ACCS_MTHD_STAT_HIST

XXX_RSN_CD

'3%'

Suspension:

37% or 38% for Involuntary Suspension only, all others are Voluntary.

DWR_CUST

DWR_ACCT

DWB_AGRMNT_STA

DWR_PROD_SBRP

DWB_ACCS_MTHD_STAT_HIST

XXX_RSN_CD

'4%'

Voluntary Status Change: Termination Voluntary

DWR_CUST

DWR_ACCT

DWB_AGRMNT_STA

DWR_PROD_SBRP

DWB_ACCS_MTHD_STAT_HIST

XXX_RSN_CD

'8%'

Involuntary Status Change: Termination Involuntary

CNTCT_CNTR _CALL1

Timeslot definition is supposed to be true for any day of the week. It corresponds to quarter of hours.

CNTCT_CNTR _CALL2

The CALL CENTER should be understood as a general CONTACT CENTER. Contact can be done through Email, Chat, Letter, or visit. Important is that it does not take place at customer's site and it is measurable (call/time in - time out).

In this specific intra-ETL, limit to calls only. Hence, only consider the logical entities EVENT PARTY INTERACTION CALL (DWB_EVT_ PRTY_INTRACN_CALL).

CNTCT_CNTR _CALL3

Call Transfer will be treated as another event related to the first call in the same party interaction thread. It is assumed that it deals with the same problem (see otherwise CNTCNTR_CALL6).

Hence, the number of contacts (calls) is increased by the number of Transfers.

CNTCT_CNTR _CALL4

By interaction occurring through the web portal of the CSP, the party interactionis not considered to start when the customer enters the web portal but when the chat session starts.

Click stream analysis is to be treated elsewhere.

CNTCT_CNTR _CALL5

Waiting, Hold, Queue time shall only be considered for Phone calls.

CNTCT_CNTR _CALL6

In case of multiple reasons for a call, a separate case (hence, interaction thread) has to be created.

CNTCT_CNTR _CALL7

The call direction is with respect to the call center view (inbound "I", customer calling, or outbound "O", when the agent calls). It is defined by who is starting the call or the chat.

CNTCT_CNTR _CALL8

Dropped calls or chat is assumed to be generated by the customer only.

Interaction Result Type Code = 'DROP'

CNTCT_CNTR _CALL9

Complaints are only counted when the original reason of the call was the complain itself and from a customer. Otherwise, complaints during a call for another reason will not be considered.

Interaction Reason Code = 'CUSTCOMP'.

CNTCT_CNTR _CALL10

Abandoned call is defined to be either when recognized as such or when the customer drops while in the queue.

Hence, the conditions is an interaction result type of "Abandoned" ('ABDN') or interaction result type as "Dropped Call" ('DROP') and 0 talk time.

CNTCT_CNTR _CALL11

Hangup call is defined when the customer drops while talking to the contact center agent.

Hence, the conditions is interaction result type as "Dropped Call" ('DROP') and talk time>0.

CNTCT_CNTR _CALL12

Released call is defined when the contact agent center stops the call. It is neither dropped nor abandoned.

Hence, the conditions is interaction result type is not "Dropped Call" ('DROP') nor "Abandoned" ('ABND') and talk time>0.

CNTCT_CNTR _CALL13

Handling Time is defined by addition of the queue time and the hold time and the talk time.

DWL_INTRACN_RSLT_TYP

Lookup for available types of Interaction Relation:

1000 RSLVD

2000 OF RACCEPT

3000 INTEREST

5000 PENDING

6000 DROP

7000 ABDN

8000 RFSD

9000 NEVERCALL

-5000 UNKNOWN

DWL_INTRACN_RSN

Lookup for available type of Interaction Reason.:

1000 MKTG QOT-OFR

1100 MKTG INBOND

1200 MKTG OUTBOND

2000 CUST INQ

2100 OFR INQ

2700 TECH INQ

2300 BILL INQ

2200 OFR INQ RESP

2400 BILL INQ RESP

2800 TECH INQ RESP

6000 DBCOLL

6100 DBCOLL CNTCT RETRN

6200 DBCOLL OUTBOND

7000 DSPT

8000 CMPLN

9000 SRVC CNTCT

9100 TECH TRBL REP INBOND

9180 TECH TRBL REP INBOND CMPLN

9200 TECH TRBL CNTCT OUTBOND AFT REP

9210 TECH TRBL CNTCT OUTBOND PROACT

9400 SRVC NOTFCTN

-5000 UNKNOWN

DWL_PROD_SBRP_STAT_TYP

DWL_ACCT_EVT_TYP

Lookup for available type of Account Event.:

TMNATMPT Termination Attempted

TMNT Termination

CRT Create

ACTVTN Activation

DISCNCTN Disconnection

INDEACT Involuntary Deactivation

VOLDEACT Voluntary Deactivation

INSUSP Involuntary Suspend

VOLSUSP Voluntary Suspend

RECNCT Reconnect

RFIS Reactivation From Involuntary Suspend

-5000 Unknown

FLDACT1

Time Window is the day. It is the status of all activities at the end of the given day.

The starting date defines the day.

FLDACT2

By "Activity", it is meant each sequential activity (so the details) and not the global activity. This is required due to the Product Specification dimension, which is only available at this level.

The exception to this rule is for INSTALLATION. The Installation-related activities are looking only at the customer support field activity (header) and overall result.

FLDACT3

One assumes that the CUSTOMER information is stored in the SERVICE FIELD ACTIVITY.

In DWB_CUST_FLD_SRVC_ACTVTY, CUST_KEY is non filled.

FLDACT4

The County associated with this table does correspond to the county of the location of the activity.

It is assumed that the address location of the activity is stored directly into the customer field service detail, and not through Business Interaction Location.

FLDACT5

Leading information is the Customer Field Service Detail. The header (Customer Field Service Activity) and the related customer line item give additional information.

FLDACT6

Returned items shall be identify through customer order line item status.

FLDACT7

For detailed activity (sub-activity), a PENDING status is also considered as FAILED (since it is not successful). This rule could be easily modified by either creating a new column or by considering only the overall activity status.

For overall activity (header), in the case of installation, one considers each status (Successful, Pending, failed) independently on one another.

DWL_FLD_ACTVTY_RSLT_TYP

ACTVTY_RSLT_TYP

FIELD ACTIVITY RESULT TYPE

DATAUSG1

The main sources for this table are the CONTENT DELIVERY EVENT and WIRELESS CONTENT DOWNLOAD EVENT and GPRS USAGE EVENT for PCU related facts.

It is assumed that there is no duplicates between Events of any tables (a given event - with a specific event code - is in only one the tables).

DATAUSG2

When a wireless download event is considered, the resource associated to the CDR is supposed to be the BTS, from which a CELL SITE can be obtained.

DATAUSG3

The number of subscribers concerns only GPRS calls. It counts one per Customer using GPRS.

DATAUSG4

The fields: GPRS_DNLD_VOL, DATA_SZ and BYTES_RCVD are mutually exclusive.

Similarly with the fields:

GPRS_UPLD_VOL, DATA_SZ and BYTES_SENT

DATAUSG5

Channel Request Received and Rejected are currently not filled by Oracle Communications Data Model. Those should be made available upon request by implementation team out of the events.

GIVEAWAY1

The Time Window for this table is the day.

This table contains the status for the day as of that date. It shall select any transactions

For a given run, it will take the last full day available within the time period given.

GIVEAWAY2

For the Retail transaction, it is assume that the promotion code corresponds 1:1 to the product offering code AND that the retail type shall be a promotion ('PROMOTION').

For a customer order, the GIVE_AWAY TYPE CODE should not be null.

PRMTN_KEY = PROD_OFR_KEY in DWB_RTL_SL_RTRN_LI

DWB_RTL_SL_RTRN_LI.RTL_TYP_CD = ' PROMOTIONTYPE'

For DWB_CUST_ORDR, use the GIVE_AWAY_TYP_CD is not null.

GIVEAWAY3

(see also AGRMNT3)

The Product Offering dimension used in this table originates from AGREEMENT and not from AGREEMENT ITEM. Hence, it is assumed that only the "main" product offering will be looked at and will be stored in the AGREEMENT table.

GIVEAWAY4

In order for the give-away item to be considered, there must be a Component Product Offering Price of the main PRODUCT OFFERING PRICE of the agreement of a given customer order, with a PRODUCT SPECIFICATION associated with the ITEM SPECIFICATION which corresponds 1:1.

The conditions are:

DWR_AGRMNT.PROD_OFR_KEY = DWR_PROD_OFR.PROD_OFR_KEY AND XXX.PROD_SPEC_KEY = DWR_CMPNT_PROD_OFR_PRICE.PROD_SPEC_KEY AND DWR_PROD_OFR.PROD_OFR_PRICE_KEY = DWR_CMPNT_PROD_OFR_PRICE. PROD_OFR_PRICE_KEY

Assume that the redeemed object is ALWAYS defined in DWR_CMPNT_PROD_OFR_PRICE

AND DWR_ITEM_SPEC.ITEM_SPEC_KEY = XXX.ITEM_KEY

GIVEAWAY5

The number of units to consider are either:

The redeemed units

The shipped units (and if null, the ordered units)

The effective units sold

Cancellation shall be considered as a negative number in units.

GIVEAWAY6

The giveaway price (as apparent value to the customer) is the maximum price per unit available in any list corresponding to this product offering.

A Max will be used.

GIVEAWAY7

The actual cost or ITEM COST are the total cost and includes the number of units times the cost per unit for the given transaction (loyalty redemption or retail or customer order).

GIVEAWAY8

Similar to GIVEAWAY5 with the EXTENDED AMOUNT (retail and customer order).

GIVEAWAY9

For a Retail transaction, the sales channel is the organization Business Unit associated with it.

ORG_BUS_UNIT_KEY shall be mapped to SL_CHNL_KEY.

DWL_RTL_TYP

RTL_TYP_CD

Lookup for type of retail. For example:

Clearancetype

Promotiontype

Regulartype

-5000 (Unknown)

DWL_INV_DOC_TYP_CD

RECEIVINGDOCUMENT

All inventory items are listed in DWB_INV_ITEM_STATE. DWB_INV_ITEM_STATE is updated when an inventory transaction occurs in the source system. All items that are in-stock but unavailable to sell are accounted for in DWB_INV_INTEM_STATE with State_code as one of the listed lookup values.

DWL_INV_STATE

ONHAND

On Hand

ONORDER

On Order

ONLAYAWAY

On Layaway

DAMAGED

Damaged

TOBERETURNED

To Be Returned

INTRANSIT

In Transit

ALLOCATEDRESERVED

Allocated Reserved

TRANSFERRESERVED

Transfer Reserved

AVAILABLETOSELL

Available to Sell

PASTUSEBYDATE

Past Use by Date

DWL_INV_DOC_TYP_CD

TRANSFERIN

TRANSFEROUT

Doc for transfer in goods.

Doc for transfer out goods.

INVCAG1

Invoice Aging applies only on OPEN Invoices, dispatched to the customers, hence of customers who have at least one Postpaid contract (agreement).The Invoices must have been sent to customer and still be in open state (that is, not fully paid).

INVCAG2

Late Payment Fees are counted in the DUE AMOUNT of the unpaid invoice.

Otherwise, if they appear on the next invoice only, they need to be added explicitly to the still unpaid invoice leveraging INVOICE ADJUSTMENT.

Consequently, the LATE PAYMENT FEE related fields should be filled leveraging the "Deferred Payment Charge Amount" of the INVOICE table

PRCSINVC1

All processes related to billing will be available, whether manual or automatic.

The effective sequential relationship between processes is also available: this means that I could have 2 or 3 invoice generation processes (1 normal, 1 being automatic recycling and 1 being manual recycling), that are linked with the same Invoice issuing process.

This assumption is critical in order to calculate correctly the KPIs

The table PROCESS EVENT ASSIGNMENT should be filled at least for those invoice generation processes.

Note that it does not matter how many processes have run, whether the 3 of them or only 1 or 2. Whatever is available will be filled.

PRCSINVC2

Only successful dispatching processes ended on the day under observation are considered (process status like '4%') to feed non-0 results into the derived table.

The other processes can have failed and be restarted.

PRCS_STAT_CD like '4%' for each type of Process event.

PRCSINVC3

It is assumed that only one billing cycle at a time is processed, and for only one type of customer.

Billing cycle and Customer Type code stored will be the one associated with the FROM parameter of the Invoice process (if filled):

Hence, if one goes over all customer type for a given billing cycle, the result or reports will currently be wrong (that is: the dimension CUSTOMER TYPE will be wrongly filled. The total (counts and amounts) will be have ALL customer types).

PRCSINVC4

In the count of "Outstanding Invoices", one considers only the dispatching processes. Invoices in error in previous processes are currently NOT considered.

Outstanding invoice count is the addition of invoice in error during the dispatching processes AND returned invoices.

In the Amount of Outstanding invoice, only the due amount of successfully dispatched invoices is considered.

Hence, the INVC_OUTSTDNG_CNT and TOT_INVC_OUTSTDNG_AMT does NOT represent the same variable (same way of calculation).

If one wants the average invoice amount outstanding, one needs to link with INVOICE table and check there.

PRCSINVC5

Error, automatically or manually recycled, or recycled more than once Counts consider all errors at any level of the end-to-end process.

Hence, even if a given invoice has been in error once and then successfully recycled, and then to be dispatched successfully with the others, this invoice will be considered when counting the invoice in error.

INVC_WITH_ERROR_CNT = SUM(

DWB_PRCS_INVC_GNRTN_EVT.UNIT_ERR_CNT + DWB_PRCS_INVC_ISSNG_EVT.UNIT_ERR_CNT +

DWB_PRCS_INVC_DSPTCHG_EVT.UNIT_ERR_CNT);

PRCSINVC6

The organization unit considered is from the employee responsible for the invoice issuing process (printing) only.

PRCSINVC7

Most amounts related to invoices considered in the final target tables are only the ones associated with successfully dispatched invoices.

The process must have ended successfully (Process status code like '4%').

PRCSINVC8

An exceptional invoice end-to-end process is considered as such if and only if the generation process is "exceptional". The other processes are automatically assumed to also be "exceptional"

This applies to SUCC_EXCEP_INVC_CNT.

PRCSINVC9

Successfully recycled at least once measure is only based on the dispatching process, not on the end to end process.

This applies to SUCC_RCYCLD_AT_LEAST_1_CNT

PRCSINVC10

The process counts are based on the process event code.

Process without error counts are based on the same but with the condition that process status code is successfully run (PRCS_STAT_CD like '4%') and there is no unit in error.

Note that the dispatching process is ALWAYS successful (except if UNIT_IN_ERR_CNT is not 0 or null) since the selection criteria of the dispatching process requires this condition.

PRCSINVC11

Process Duration considers the time elapsed between the start of the first process and the end of the last process of the same type (invoice generation, issuing or dispatching).

It is the not the sum of each process duration!

If the sum of each process duration is required, it shall be added as a customization.

PRCSINVC12

The time elapsed between the beginning of the invoice generation process and ending of the invoice dispatching process shall NOT exceed 2 months or it will be ignored.

The time elapsed between the beginning of the invoice issuing and ending of the invoice dispatching process shall NOT exceed 2 months or it will be ignored.

These parameters are for performance only. They could be removed.

PRCSINVC13

MEDIA OBJECT TYPE CODE

ignored (Fixed to be '-5000')

RVN_DAY0

Time window: All fact columns shall represent the status from the beginning until the end of the period considered (here: last passed day).

It is NEVER a status or a balance at the end of the period. Hence, to have the revenue of a given combination or Product Offering and Product Spec, one shall sum each day of the period considered.

Since Revenue Day has to do with Usage (hence CDRs), it is very important to consider the Business Rule RVN_DAY11 for LATE CDRs.

RVN_DAY1

Content of DWD_RVN_DAY:

It shall store all information according to all the dimensions for a given day.

In particular, any combination of PRODUCT OFFERING and PRODUCT SPEC can be added wherever needed. Typically, when there is more than one default composite Product Spec to a given Product Offering, or when one wants to have the details of say the handset model chosen depending on various options associated with a given Product Offering.

Since all revenue columns are sum-able, it contain automatically the statistics around any PRODUCT OFFERING alone (whatever the Product Spec), or any PRODUCT SPEC alone. Hence, the use of PRODUCT OFFERING LEVEL and PRODUCT SPEC LEVEL is not necessary.

See also RVN_DAY10 and CNT_DAY2 rule

RVN_DAY2

Definition of the Revenue Types:

There are 7 types of Revenue:

Billed: Any amounts appearing either on a bill sent (postpaid) OR (prepaid) when they already paid for their use of a service OR the monetary amount left expired (.

Unbilled: all the other cases. Ignored (that is columns not fed) in Oracle Communications Data Model.

Billed earned: normal case where customer has been billed for the services/usage he has used.

Unbilled earned: customer is billed upfront; future cycles fall under unearned revenue.

Billed unearned: this is the case when customer has performed some usage (that is, made a call), but has not yet been billed for that usage.

Unbilled unearned: an example here is a payment a customer may make upfront, but future services to be rendered are canceled (or simply overpayments they may have made).

Previously billed earned: Billed earned from previous period considered.

For more information, see Oracle® Communications Billing and Revenue Management Collecting General Ledger Data.

Oracle Communications Data Model deals only with billed and unbilled revenue, which includes earned and unearned revenue as follows:

Billed = billed earned + billed unearned + previously billed earned.

Unbilled = unbilled earned + unbilled unearned

"Billed" means it appears on an invoice sent to customer ('OPEN')

Additional definitions:

Gross Revenue: reports the total of net and discounted revenue.

Discount or Discounted Revenue: reports the balance impacts of discounted revenue.

Net Revenue: reports the amount of revenue that remains after applying discounts.

Tax: reports the amount of taxes calculated. This data is used for collecting G/L data based on tax codes.

Tax is assumed to never be applied in any amounts except on Invoices.

Corresponding SQL Statement

Any Prepaid Usage or Expired Monetary Balance shall be considered as "Billed".

For Postpaid, "Billed" is when DWB_INVC.STAT_CD ='OPEN' or 'CLOSED';

Unbilled will be all other cases. Because the rule is depending on the billing system itself, and the internal processing, Unbilled revenue will be ignored (present as column but NOT FILLED).

RVN_DAY3

More Definitions around Revenue:

Prepaid Services Revenue: this should count all prepaid usage revenue for the period + any expired prepaid revenue even if not used.

Billed or unbilled is not relevant in this case since that is not applicable to prepaid. It is always considered as "billed".

Postpaid Services Revenue (billed): billed postpaid usage revenue (all services) + billed cycle fees (for example: monthly) + recurring equipment rental (for example: CPE rental).

Equipment Revenue (billed): revenue associated with sale of any devices (for example: handsets) and accessories.

Other Revenue (billed): this should include other non-recurring customer revenue such as one-time purchase or activation fees, late payment fees, cancellation fees, and so on…

Total Gross Revenue (billed): prepaid services revenue + postpaid services revenue + equipment revenue + other revenue.

Total Net Revenue (billed): total gross revenue - deductions (for example: taxes, refunds, write-offs)

RVN_DAY4

Condition Definitions:

Usage Revenue: Revenue coming from service usage (pay per use) and calls (pay on event, duration and/or volume). The sources will be VOICE CALL DAY, DATA USAGE DAY, VAS USAGE DAY (and not SUPPLEMENTARY SERVICE USAGE DAY).:

The field TOT_BLLD_AMT or BLLD_AMT shall be used when PLN_TYP='Prepaid' (and day corresponds).

The postpaid Revenue shall come from

DWD_INVC_DAY: USG_RVN_BLLD when it is independent of Product Offering and Product Spec (or this number could be used as X-check). It shall come from DWB_INVC_ITEM when the product Offering and/or the Product Spec is required.

Expired Revenue: Revenue that comes from prepaid expired amount.

It comes from either DWB_EVT_BAL_IMPC. Or DWD_ACCT_BAL_MO.EXPRD_AMT (sum) where ACCT_BAL_TYP_CD is associated with a monetary UOM (for the same product offering and product spec). One also needs to subtract all the expired amounts of DWD_RVN_DAY of the previous DAY of the current month.

RVN_DAY5

Roaming Revenue:

Roaming Revenue is considered for Roaming events on CSP's network, whose paying party is an external operator. It assumes that any event from WIRELESS ROAMING EVENT only contain such events.

It is also expected RMNG_EXTRNL_OPRTR_KEY or EXTRNL_OPRTR_KEY cannot be both null.

For Billed Revenue, Invoices must be of Type "RMNG STTLMT" (Roaming Settlement).

Roaming domestically (MVNO) is allowed.

RVN_DAY6

Transfer: A transfer (Account, Agreement, Product Subscription) is a change of ownership and is recognized as such if and only if:

The "Code" of the entity is not changed

The Agreement and its term are not changed. The Product Offering associated is not immediately changed.

Only the ownership changes

The Status Reason Code is associated with Transfer.

There is no time without ownership.

STAT_CD like '2%' (New) and SUBSTR( STAT_CD,1,1) in ('1','2') (old) and new.eff_from_dt -old.eff_to_dt<=1s

RVN_DAY7

Geographic County corresponds to the County of the Primary Address of the Customer when defined.

RVN_DAY8

Organization Business Unit, Sales Channel and Sales Rep : correspond to the respective Sales Channel and Sales Rep directly associated with the Product Offering and Product Spec of the corresponding product subscription.

If unclear or undefined, one shall take the last one associated with the corresponding Customer (in the customer table).

RVN_DAY9

Cost Center is the Cost Center associated with the Organization Business Unit considered (if uniquely defined). Keep it 'UNKNOWN' otherwise.

RVN_DAY10

Product Offering Level and Product Spec Level: (follow-up of RVN_DAY1)

When both set to 0, both PRODUCT SPEC and PRODUCT OFFERING should be defined (normally not unknown, although unknown will be accepted).

If one of the level is set to 1 and the other to 0, it collects the statistics according to the entity whose level is set to 0, independently of the other. The other will be forced to be 'unknown'.

If both levels are set to 1, it collects the global statistics independently of Product Offering or Product Spec. Both are forced to be 'unknown'.

This is necessary to allow the calculation of some KPIs.

See also CNT_DAY10 rule.

OBSOLETE since the facts are sum-able.

RVN_DAY11

Late Usage or Late CDRs:

Usage that come later shall be taken into account. Hence, the LOAD DATE should be used as part of the criteria to consider a row or not.

But the REVENUE associated shall be attributed to the right day, corresponding to the Start Day of the event.

This rule could be changed for very late delay (> 3 full months). Those revenues should be excluded.

RVN_DAY12

For Prepaid, we assume that the Sale Channel and Sales Rep information are associated with the Prepaid subscription. These 2 fields will be then used.

RVN_DAY13

Due to the way to feed DWD_RVN_DAY (for performance), there will be 4 rows per combination of key columns: 1 for postpaid and 3 for Prepaid.

As a consequence,

- Empty revenue columns shall be filled with 0 and not with null.

- One always needs to sum those 4 rows (per fact) to have the final numbers for a given key combination.

RVN_DAY14

Most Key columns will come from the information out of the Product Subscription.

In particular, it is expected that the following columns of PRODUCT SUBSCRIPTION are filled (in bold, those critical):

Organization Business Unit Code

Channel Code (preferred, priority) or Campaign Channel Code

Product Offering Code (!)

Product Spec Code (!)

Customer Code (or at least Account Code).

In DWR_PROD_SBRP, check that the following columns are filled:

ORG_BUS_UNIT_KEY

CHNL_KEY or CMPGN_CHNL_KEY

PROD_OFR_KEY

PROD_SPEC_KEY

CUST_KEY or ACCT_KEY.

RVN_DAY15

Plan Type is either 'Postpaid' or 'Prepaid'. Hybrid product offering cannot be considered and will be ignored (at least for billed usage).

If PLN_TYP not in ('Prepaid', 'Postpaid'), all billed usage related columns will be 0.

RVN_DAY16

Roaming Revenue concerns only revenue that comes from Roaming TAP OUT file. TAP IN file are NOT considered as part of Roaming Revenue (since they are rather a cost as such - and they should appear in DWD_VOI_CALL_DAY with the final billed amount to the customer).

This is for RMNG_RVN_UBLLD.

RVN_DAY17

Interconnect Revenue only concerns revenue from passing traffic. The A & B numbers (if defined at all) are NOT belonging to the Service Provider.

Interconnect Traffic will be recognized by a non null revenue associated with INTERCONNECTION field. Cost will be ignored here.

It is assumed that all interconnection traffic will be stored into VOICE CALL DAY (whatever the type of call).

INTCONN_RVN>0 is sufficient as criteria.

RVN_DAY18

SALES REPRESENTATIVE and COST CENTER columns are currently ignored.

They could be filled as customization if one adds this column in DWD_VOI_CALL_DAY, DWD_DATA_USG_DAY and DWD_VAS_USG_DAY.

RVN_DAY19

For all USAGE, Billing Status Type code successful (that is, BILLED) is explicitly required.

BLLG_STAT_TYP_CD='SUCC' is expected.

The "CASE WHEN" conditions could be all removed (to win time) if one assumes that all usage events stored at the derived layer will always have the same BILLING STATUS TYPE CODE (assumed to be 'SUCC' only).

RVN_DAY20

It is assumed that the ORGANIZATION BUSINESS UNIT (as well as others like GEO COUNTY CODE or CUSTOMER TYPE CODE) stored in the derived USAGE tables (VOICE CALL DAY; DATA USAGE DAY and VAS USAGE DAY) are identical to the one set in the invoice.

There is an explicit full join with these conditions between invoice and the usage tables.

If this is not the case, the USAGE related statement must be modified to force the ORGANIZATION BUSINESS UNIT there to be equal to one of the invoice

RVN_DAY21

For Prepaid, it is assumed that an individual CUSTOMER (dummy or not) is defined for each prepaid account. This customer information should contain a dummy customer type (the one associated with the targeted customer for this product offer maybe) and address (maybe the one of the Organization Business Unit where it was bought).

The organization business unit comes from the account.

This information shall be used to join with the usage information.

RVN_DAY22

Information out of the invoice will only be considered when the billing date of the invoice is between the ETL (Time) Parameters. The status of the invoice is ignored. This implies two approximations:

-The invoice is complete at billing date and will NOT be corrected or updated after that date (or this update will not be considered such as the invoice status change, the partial or full payment after billing date, and so on),

-Past invoices added at a later time in Oracle Communications Data Model will NOT be considered

Note: The status of the invoice is ignored only for the selection of the invoice and invoice items. However, the association to an INVOICED (BILLED) or UNBILLED column is based on the status invoice:

-INVOICED when the 1st character of Invoice Status Code is between 2 and 5 (both limits included)

-Unbilled in all other cases

As customization, one could deal with LOAD DATE and INVOICE CODE to find whether an invoice has already been considered or not. But this could be a heavy job to do it correctly

RVN_DAY23

Discount Revenue: On top of being positive (and to be subtracted from all revenues), it is assumed that only DISC_AMT and related columns have to be considered.

This also mean that if an invoice item is of type DISCOUNT (INVOICE ITEM TYP CD like 6%), only DISC_AMT (and related) should then be filled, and not CHRG_AMT.

RVN_DAY24

Invoice items associated with recurring fees will be associated with only price type code for forward fees (Price_TYP_CD like '11%') or arrear fees ((Price_TYP_CD like '12%').

Any other combination is currently excluded from the REVENUE DAY derived table.

RVN_DAY25

Amounts will only be considered out of Account Balances that are of type "REGULAR" ("RGLR").

DWR_CUST

DWR_ACCT

DWR_AGRMNT

DWR_PROD_SBRP

DWR_ACCS_MTHD

STAT_CD

'1%'

Pending Activation

DWR_CUSTDWR_ACCTDWR_AGRMNTDWR_PROD_SBRPDWR_ACCS_MTHD

STAT_CD

'15%'

Pre-Activated

DWR_CUST

DWR_ACCT

DWR_AGRMNT

DWR_PROD_SBRP

DWR_ACCS_MTHD

STAT_CD

'2%'

Active

DWR_CUST

DWR_ACCT

DWR_AGRMNT

DWR_PROD_SBRP

DWR_ACCS_MTHD

STAT_CD

'3%'

Suspended

DWR_CUST

DWR_ACCT

DWR_AGRMNT

DWR_PROD_SBRP

DWR_ACCS_MTHD

STAT_CD

'4%'

Deactivated / Terminated

DWR_CUST

DWR_ACCT

DWR_AGRMNT

DWR_PROD_SBRP

DWR_ACCS_MTHD

STAT_CD

'5%'

Cancelled

DWR_CUST

DWR_ACCT

DWB_AGRMNT_STAT

DWR_PROD_SBRP

DWB_ACCS_MTHD_STAT_HIST

PRMRY_STAT_RSN_CD

ACCT_STAT_RSN_CD

AGRMNT_STAT_RSN_CD

PROD_SBRP_STAT_RSN_CD

ACCS_MTHD_STAT_RSN_CD

'%'

DWR_CUST

DWR_ACCT

DWB_AGRMNT_STAT

DWR_PROD_SBRP

DWB_ACCS_MTHD_STAT_HIST

PRMRY_STAT_RSN_CD

ACCT_STAT_RSN_CD

AGRMNT_STAT_RSN_CD

PROD_SBRP_STAT_RSN_CD

ACCS_MTHD_STAT_RSN_CD

'%'

Voluntary Status Change

DWR_CUST

DWR_ACCT

DWB_AGRMNT_STAT

DWR_PROD_SBRP

DWB_ACCS_MTHD_STAT_HIST

PRMRY_STAT_RSN_CD

ACCT_STAT_RSN_CD

AGRMNT_STAT_RSN_CD

PROD_SBRP_STAT_RSN_CD

ACCS_MTHD_STAT_RSN_CD

'%'

Involuntary Status Change

SRVCPB1

Customers appearing in this table are only customers where an SLA has been specifically signed. Customers without SLA shall NOT be considered, even if they are impacted by an outage

Only check Customers from table DWR_SRVC_LVL_AGRMNT. All others will be ignored (set as 'Unknown')

SRVC_PB2

Authorized Outage Time is to be stored is assumed to one of the Term/Condition associated with an SLA.

In case of absence of "Authorized Outage Time" as condition of the SLA, the default should be the 5 9's (99,999% of time). That is 31536 seconds (8h 45Mns, 36 s) per year (365 days).

Default Period is considered to be the current year

AGRMNT_TERM_TYP_CD = "AUTH_OUTG_TIME";

SRVC_PB3

The link between the SLA and the condition "Authorized Outage Time" assumes that the PROD_SPEC_KEY is defined in both the SLA item and the Agreement Term entities.

SRVC_PB4

For SLA, only current rows will be considered. There will not be any other "time" or "Status" constraint.

CURR_IND='Y' for SLA Item

SRVC_PB5

The Intra ETL runs once a day only. (Otherwise, the SLA_BRK_IND may not be reliable - it needs specific adaptation of the code and some assumptions or known (fixed) period value for SLA time outage condition).

In any case, the SLA_BRK_IND indicates whether a given problem breaks by itself an SLA. It does not mean that the sum of Problems may not break the SLA

SRVC_PB6

When summary is for the month, the derived table holds the updated statistic and status of the current month up to the previous full day. Otherwise (daily), it holds the latest statistic of the given day.

SRVC_PB7

It is assumed that all services linked to a subscription are defined (or reachable) in DWR_SRVC only

This is critical to make the link. One cannot really use Customer Facing service or Resource Facing Service (as anyway both are usually affected simultaneously).

SRVC_PB8

Remote Customer Support is only considered through calls. It could be extended to include emails, chats or web portal interaction (to the online help)

Only DWB_EVT_PRTY_INTRACN_CALL will be used

SRVC_PB9

On Site Support is not necessarily at customer site. It counts the number of activities at any site during the time period concerned independently on the status of the activity.

On Site Customer Support must occur at the customer address available in the CUSTOMER entity

SRVC_PB10

The time spent onsite is by default read directly from the CUSTOMER FIELD SERVICE ACTIVITY table. It is either the Total Time Spent On Site field (default) or the total activity duration (End-Start).

SRVC_PB11

The SLA Unit of Measure corresponds to the period on which the SLA is to be measured. Even if there is an agreement on the maximum number of outages, only terms related to duration shall be considered here.

In case several agreement terms of type duration apply, the lowest Agreement Term code shall be taken.

It excludes the case Agreement Term = '91%', but consider any '90%'.

If both are present, use '9000' by default (or MIN (Agreement term type Code).

STORE1

One shall limit the analysis to STOREs. This means that event Party Interaction Visit will only be considered if the Organization Business Unit corresponds to a store.

The fact that one links explicitly with ORGANIZATION BUSINESS UNIT HOURS DAY (which is assumed to be filled for each store and for each day any of the store opens) applies automatically this constraint.

STORE2

A transaction is assumed to occur when the EVENT PARTY INTERACTION VISIT is linked to any other Event through EVENT ASSIGNMENT.

It is independent of the reason of this assignment. It simply means something must have occurred.

STORE3

Customer waiting time is the duration between the time the customer enters the shop/queue until the time he is attended by any of the shop representative.

Note that it cannot be recorded if one does not know when the customer enters the shop

STORE4

The time the customer enters the queue and is attended by any shop representative and his entry is closed is called as the transaction time.

The average transaction time is calculated on all transaction times over a given period.

STORE5

Numbers of customers who enter the queue but do not wait for long and disappear are also counted along with the number of employees in the shop.

STORE6

By default the Sale amount shall correspond to the General Ledger Revenue associated to this visit.

DWL_ORG_BUS_UNIT_TYP

STORE

VASUSG1

The main sources for this table are the reference table VAS (and mailbox) and

• ISP Event

• UMS Event

• Wireless Call event (just in case)

• Rated UDR EVENT (if all else fails)

It is assumed that there is no duplicates between Events of any tables (a given event - with a specific event code - is in only one the tables).

VASUSG2

When any wireless event is considered, the resource associated to the CDR is supposed to be the BTS, from which a CELL SITE can be obtained.

VASUSG3

Free Data Size and Free duration is only counted if the event is totally free (Charged Amount is 0).

DWL_DVRT_RTRV_TYP

-5000

DVRT

RTRV

VOICE1

The CDRs taken into account have to be present from:

• Wireless Call Event

• Fixed Line Call Event

All other tables are excluded.

VOICE2

The Domestic Indicator cannot be set when Local Indicator is set. Those are mutually exclusive.