Skip Headers

Oracle® Communications Data Model Reference 11g Release 2 (11.2) Part Number E15886-05 |
Home | Book List | Contents | Index | Contact Us

Previous | Next
| PDF · Mobi · ePub

# 7 Oracle Communications Data Model Intra-ETL

This chapter includes the following sections:

- Introduction to Oracle Communications Data Model Intra-ETL

- Value Lookup Models for ETL Mappings

- Intra-ETL Oracle Warehouse Builder Mapping Source and Target Tables

- Intra-ETL PL/SQL Mapping Source and Target Tables

- Intra-ETL Process Flows

## Introduction to Oracle Communications Data Model Intra-ETL

In Oracle Communications Data Model, reference and lookup tables store master, reference, and dimensional data; and the base, derived, and aggregate tables store transaction and fact data at different granularities. The base tables store the transaction data at the lowest level of granularity, while the derived and aggregate tables store consolidated and summary transaction data.

Two types of Extract, Transform, and Load (ETL) operations populate the tables with data. The source-ETL operations populate the reference, lookup, and base tables with data from the source On-Line Transaction Processing (OTLP) applications. Additional Intra-ETL operations populate the derived and aggregate tables with the data in the base, reference, and lookup tables. While the source ETL operations are not a part of Oracle Communications Data Model, the Intra-ETL operations are.

There are two categories of Intra-ETL operations (scripts):

- Derived Population: A database package containing scripts that populate the derived tables based on the content of the base, reference, and lookup tables.

- Aggregate Population: A database package containing scripts to refresh the Oracle Communications Data Model aggregate tables, mostly Materialized Views, based on the content of the derived tables and some reference tables.

Derived tables are implemented using Oracle tables, while the Aggregate tables are implemented using Materialized Views.

Note:

Do not make changes to the ETL as such changes are not supported.

The INTRA_ETL_FLW is actually a process flow designed using the Oracle Warehouse Builder Workflow component which includes the dependency of each individual sub process flow and executes each process flow in the proper order. The result of each table loading is tracked in DWC_ control tables.

For more information, see "Intra-ETL Process Flows" and Oracle Communications Data Model Implementation and Operations Guide.

## Value Lookup Models for ETL Mappings

Oracle Communications Data Model Value_Lookup values contains the Lookup tables and its values which are used in Intra-ETL mapping. Hardcoded values contains the list of tables and values which are used in Join conditions & Filter conditions in Intra-ETL mapping.

Table 7-1 Shows the lookup tables and values which are used in Intra-ETL mapping.

Table 7-1 Value Lookup Values for Intra-ETL Mapping

SI No. | Hard Coded Value Table Name | Hard Coded Value Column | Value used | ETL Program Name | ETL Usage Type
--- | --- | --- | --- | --- | ---
1 | `DWB_ACCT_DEBT_DT`L | `ACCT_DEBT_DTL_TYP_CD` | `BILL, PNLTY` | `DWD_ACCT_DEBT_DAY` | Hardcoded
2 | `DWB_ACCT_PYMT` | `PYMT_MTHD_TYP_CD` | `BNK` | `DWD_ACCT_PYMT_DAY` | Hardcoded
3 | `DWB_ACCT_PYMT` | `PYMT_TRX_TYP_CD` | `LTPAY, DPST, PNLTY, INVC` | `DWD_ACCT_PYMT_DAY` | Hardcoded
4 | `DWB_EVT_ACCT` | `ACCT_EVT_TYP_CD` | `CRT, VOLDEACT, VOLSUSP, RECNCT, RFSUS, TMNT, INDEACT, DISCNCTN, INSUSP, ACTVTN` | `DWD_ACCT_STAT` | Hardcoded
5 | `DWR_PROD` | `PROD_CD` | `PAYTV, HOMTEL, IDD, WRLS, BRDBND` | `DWD_ACCT _STTSTC` | Hardcoded
6 | `DWB_ACCT_STAT_HIST` | `ACCT_STAT_TYP_CD` | `CHRN` | `DWD_ACCT _STTSTC` | Hardcoded
7 | `DWB_EVT_LYLTY_PROG` | `LYLTY_PROG_EVT_TYP_CD` | `ACMLTN, RDMPTN` | `DWD_ACCT _STTSTC` | Hardcoded
8 | `DWB_INVC_ITEM` | `PROD_CHRG_TYP_CD` | `SRVC, SLPROD, AIRTM` | `DWD_ARPU_BASE` | Hardcoded
9 | `DWB_COST` | `COST_SUBTYP_CD` | `AQSNCOST, RETNCOST, CCNTCOST, OPRNCOST` | `DWD_ARPU_BASE` | Hardcoded
10 | `DWB_EVT_PRTY_INTRACN_CALL` | `INTRACN_RSN_CD` | `CUSTCOMP` | `DWD_CALL_CNTR_CALL_DAY` | Hardcoded
| `INTRACN_RSLT_TYP_CD` | `RESLVD, PNDNG` | `DWD_CALL_CNTR_CALL_DAY` | Hardcoded
11 | `DWR_CNRT_ASGN` | `CNRT_ASGN_RSN_CD` | `OPINIT, CUSTCHNG` | `DWD_CANBLZTN_DTL_DAY` | Hardcoded
14 | `DWD_CNRT_CHNG_MO` | `CNRT_CHNG_TYP_CD` | `RPLC, TMNT` | `DWD_CNRT` | Hardcoded
15 | `DWB_INVC_ITEM` | `INVC_ITEM_TYP_CD` | `MTHLYFEE, PNLTY` | `DWD_CNRT` | Hardcoded
16 | `DWR_CMPGN` | `CMPGN_PRPS` | `ACQR, RTNTN` | `DWD_CNRT` | Hardcoded
17 | `DWR_PRMTN` | `PRMTN_TYP_CD` | `PRMM` | `DWD_CNRT` | Hardcoded
18 | `DWB_CNRT_TERM_VAL` | `CNRT_TERM_TYP_CD` | `MONAMT` | `DWD_CNRT_CHNG` | Hardcoded
19 | `DWR_CNRT_ASGN` | `CNRT_ASGN_TYP_CD` | `RPLC` | `DWD_CNRT_CHNG` | Hardcoded
20 | `DWB_CNRT_STAT` | `CNRT_STAT_TYP_CD` | `TMNT` | `DWD_CNRT_CHNG` | Hardcoded
| `CNRT_STAT_RSN_CD` | `PRMTN, PRODUPGD, CMPLN` | `DWD_CNRT_CHNG` | Hardcoded
21 | `DWB_CUST_COST` | `COST_SUBTYP_CD` | `AQSNCOST, RETNCOST, CMSN, NTWKCOST, CCCOST, RHCOGS, AHCOGS, ACMSNP, ACMSNC, DMREPY, DMCNRT, OTRCOST, SCCPREPY, SCCCNRT, SLNGCOST, OPRNCOST` | `DWD_COST_CUST` | Hardcoded
22 | `DWB_BSNS_UNIT_COST` | `COST_SUBTYP_CD` | `OTRCOST, OPRNCOST, INSTCOST, ADVRCOST, CBUDGET, CATNCOST` | `DWD_COST_ORG` | Hardcoded
23 | `DWB_CNRT_TERM_VAL` | `CNRT_TERM_TYP_CD` | `CNRTVAL` | `DWD_CUST_ACQSTN_SUMM_DAY` | Hardcoded
24 | `DWB_EVT_SBRP` | `EVT_TYP_CD` | `ACTV, TMNT` | `DWD_CUST_ACQSTN_SUMM_DAY` | Hardcoded
25 | `DWB_ACCT_DEBT_DTL` | `ACCT_DEBT_DTL_TYP_CD` | `PNLTY` | `DWD_EXTRNL_DEBT_COLLCTN_DAY` | Hardcoded
26 | `DWR_ITEM` | `ITEM_TYPE_CD` | `HNDST` | `DWD_HNDST_SUBSDY_DAY` | Hardcoded
27 | `DWR_PROD` | `PROD_NAME` | `HANDSET` | `DWD_HNDST_SUBSDY_DAY` | Hardcoded
28 | `DWB_ACCT_DEBT_DTL` | `ACCT_DEBT_DTL_TYP_CD` | `PNLTY` | `DWD_INTRNL_DEBT_COLLCTN_DAY` | Hardcoded
29 | `DWB_EVT_ACCT` | `ACCT_EVT_TYP_CD` | `CRT, TMNATMPT, TMNT` | `DWD_LN_ACTVTN_TMNT_DAY` | Hardcoded
30 | `DWR_CMPGN` | `CMPGN_PRPS_TYP_CD` | `RTNTN, CONDATE` | `DWD_LN_ACTVTN_TMNT_DAY` | Hardcoded
31 | `DWB_MNT_ALLWNC` | `PPA_CTGRY_CD` | `FLANSWER, FLCALL` | `DWD_PRPD_ALWNCE_DAY` | Hardcoded
32 | `DWR_PROD_MKT_PLN` | `PROD_MKT_PLN_TYP_CD` | `PRPD` | `DWD_PRPD_CALL_SUMM_DAY` | Hardcoded
33 | `DWR_EVT_PRTY_RL` | `EVT_PRTY_RL_CD` | `OPRT` | `DWD_RDMPTN_DAY` | Hardcoded
34 | `DWB_UMS_EVT` | `UMS_EVT_TYP_CD` | `RCVD, DEL` | `DWD_VAS_USAGE_DAY` | Hardcoded
35 | `DWB_WRLS_CALL_EVT` | `DVRT_RTRV_TYP_CD` | `RTRV, DVRT` | `DWD_VAS_USAGE_DAY` | Hardcoded
36 | `DWB_EVT_LYLTY_PROG` | `LYLTY_PROG_EVT_TYP_CD` | `ACMLTN` | `DWD_SBCRBR_CHRN_STTSTC` | Hardcoded
37 | `DWB_WRLS_CALL_EVT` | `CALL_TMNT_RSN_CD` | `DRPD, CNCL` |  | Hardcoded
38 | `DWB_CNRT_TERM_VAL` | `CNRT_TERM_TYP_CD` | `NBRLNS` | `DWD_SHRD_PKG_USG_STTSTC_DAY` | Hardcoded
39 | `DWR_PROD_CAPBLTY` | `PROD_CAPBLTY_CD` | `NBRLNS` | `DWD_SHRD_PKG_USG_STTSTC_DAY` | Hardcoded
40 | `DWB_EVT` | `EVT_RSLT_CD` | `SUCC, FAIL` | `DWD_SL_RPRSTV_STTSTC_MO` | Hardcoded
41 | `DWR_PROD_MKT_PLN_ASGN` | `PROD_MKT_PLN_ASGN_CD` | `GIFT` | `DWD_SUBSDY_AMT` | Hardcoded
44 | `DWR_PROD` | `PROD_NAME` | `CALL, SMS, MMS` | `DWD_SUBSDY_AMT` | Hardcoded
1 | `DWL_DEBT_AGNG_BND` | `DEBT_AGNG_BND_CD` | `DAB1, DAB3, DAB2, DAB4`, | `DWD_ACCT_DEBT_DAY, DWD_ACCT_STTSTC, DWD_CRDT_CTGRY_MO, DWD_PYMT_AGNG_DAY, DWD_SBCRBR_CHRN_STTSTC` | Value_Lookup
2 | `DWL_AGE_ON_NET_BND` | `AGE_ON_NET_BND_CD` | `M1 , M3 , M6 , M12 , M24 , M36 , M60 , M96 , M120 , M240 , M240+ ,` | `DWD_ACCT_PYMT_DAY, DWD_CRDT_CTGRY_MO, DWD_HNDST_SUBSDY_DAY, DWD_INVC, DWD_ACCT_STTSTC, DWD_PRPD_ACCT_STTSTC, DWD_PYMT_AGNG_DAY, DWD_RDMPTN_DAY, DWD_LYLTY_PROG_DAY, DWD_SBCRBR_CHRN_STTSTC` | Value_Lookup
3 | `DWL_AGE_ON_NET_BND` | `AGE_ON_NET_BND_FROM` | `0 , 3 , 6 , 12, 25 , 36 , 51 , 101, 201 , 271 , 401` , | `DWD_ACCT_PYMT_DAY` | Value_Lookup
4 | `DWL_AGE_ON_NET_BND` | `AGE_ON_NET_BND_TO` | `2 , 5 , 11, 24 , 35 , 50 , 100, 200 , 270 , 400 , 9999999999,` | `DWD_ACCT_PYMT_DAY` | Value_Lookup
6 | `DWL_AGE_BND` | `AGE_BND_CD` | `AGBND1 , AGBND2 , AGBND3 , AGBND4 , AGBND5` | `DWD_ACCT_STTSTC, DWD_SBCRBR_CHRN_STTSTC` | Value_Lookup
7 | `DWL_CHRN_RSN` | `CHRN_RSN_CD` | `NORSN , SVFLR , CRELOC , CHRNDFLT, CHRNSRVC , RLCTN, DISSAT , SVCTMNT , NONPAID` | `DWD_ACCT _STTSTC` | Value_Lookup
8 | `DWL_CUST_RVN_BND` | `CUST_RVN_BND_CD` | `BAND100, BAND200 , BAND300 , BAND400 , BAND500 , BAND600 , BAND700 , BAND800 , BAND900 , BAND1000, BAND1100 , BAND1600 , BAND1500 , BAND1400 , BAND1300 , BAND1200, BAND1700 , BAND1800 , BAND1900 , BAND2000 , BND2000+` | `DWD_ACCT _STTSTC, DWD_SBCRBR_CHRN_STTSTC` | Value_Lookup
9 | `DWL_ARPU_BAND` | `ARPU_BND_CD` | `ARPU7500+ , ARPU1000 , ARPU2500 , ARPU5000 , ARPU7500` | `DWD_ACCT_STTSTC, DWD_SBCRBR_CHRN_STTSTC` | Value_Lookup
10 | `DWL_PK_OFPK_TIME` | `PK_OFPK_TIME_CD` | `PK , OFPK` | `DWD_CDR_WRLS_DAY, DWD_PRPD_CALL_SUMM_DAY, DWD_VAS_USAGE_DAY` | Value_Lookup
16 | `DWL_RECHRG_RVN_SLB` | `RECHRG_RVN_SLB_CD` | `$0-25, $25-50, $50-100, $100+` | `DWD_PRPD_ACCT_STTSTC` | Value_Lookup
17 | `DWL_INTRACN_RSN` | `INTRACN_RSN_CD` | `CMPLN, DBCOLL, SRVC, IBMKTG, OBMKTG, CUSTCOMP` | `DWD_PRPD_ACCT_STTSTC` | Value_Lookup
18 | `DWL_PRMTN_RSLT_TYP` | `PRMTN_RSLT_TYP_CD` | `OFRACPT, PREVENT` | `DWD_SL_CMPGN_DTL_DAY` | Value_Lookup

## Intra-ETL Oracle Warehouse Builder Mapping Source and Target Tables

Shows the Oracle Warehouse Builder mapping to populate derived tables.

### DWD_ACCT_DEBT_DAY__MAP Mapping

Populate target table `DWD_ACCT_DEBT_DAY`. For more information, see ACCOUNT DEBT DAY DRVD.

Table 7-2 DWD_ACCT_DEBT_DAY__MAP ETL Mapping

Source Table Name
DWB_ACCT_BAL_HIST
DWB_ACCT_BAL_ADJ
DWB_ACCT_PYMT
DWB_DEBT_COLLCTN_ASGN
DWL_DEBT_AGNG_BND
DWR_ACCT
DWR_ADDR_LOC
DWR_CUST
DWR_DAY

### DWD_ACCT_PYMT_DAY__MAP Mapping

Populate target table `DWD_ACCT_PYMT_DAY`. For more information, see ACCOUNT PAYMENT DAY DRVD.

Table 7-3 DWD_ACCT_PYMT_DAY__MAP ETL Mapping

Source Table Name
DWB_ACCT_PYMT
DWB_DEBT_COLLCTN_ASGN
DWB_INV_PYMT_ASGN
DWB_INVC
DWL_AGE_ON_NET_BND
DWR_ACCT
DWR_BSNS_MO
DWR_ADDR_LOC
DWR_CUST

### DWD_ACCT_PYMT_MTHD_STAT_HIST__MAP Mapping

Populate target table `DWD_ACCT_PYMT_MTHD_STAT_HIST`. For more information, see ACCOUNT PAYMENT METHOD STATUS HIST DRVD.

Table 7-4 DWD_ACCT_PYMT_MTHD_STAT_HIST__MAP ETL Mapping

Source Table Name
DWB_ACCT_CRDT_LMT
DWB_ACCT_PYMT_MTHD_STAT
DWB_CNRT_TERM_VAL
DWL_AGE_ON_NET_BND
DWR_ACCT
DWR_ACCT_PREF_PYMT_MTHD
DWR_BSNS_MO
DWR_CNRT
DWR_CUST

### DWD_ACCT_RFND_DAY__MAP Mapping

Populate target table `DWD_ACCT_RFND_DAY`. For more information, see ACCOUNT REFUND DAY DRVD.

Table 7-5 DWD_ACCT_RFND_DAY__MAP ETL Mapping

Source Table Name
DWB_ACCT_PYMT
DWB_ACCT_RFND
DWB_INVC
DWB_INVC_ADJ
DWB_INVC_ITEM
DWR_ADDR_LOC
DWR_CUST
DWR_DAY

### DWD_ACCT_STAT__MAP Mapping

Populate target table `DWD_ACCT_STAT`. For more information, see ACCOUNT STATUS DRVD.

Table 7-6 DWD_ACCT_STAT__MAP ETL Mapping

Source Table Name
DWB_ACCT_RFND
DWB_EVT_ACCT
DWR_ACCT
DWR_ADDR_LOC
DWR_BSNS_MO
DWR_CUST
DWR_PRTY_LYLTY_PROG_PRTCPTN
DWR_SBRP

### DWD_ACCT_STTSTC__MAP Mapping

Populate target table `DWD_ACCT_STTSTC`. For more information, see ACCOUNT STATISTIC DRVD.

Table 7-7 DWD_ACCT_STTSTC__MAP ETL Mapping

Source Table Name
DWB_ACCT_BAL_HIST
DWB_ACCT_CRDT_LMT
DWB_ACCT_PYMT
DWB_ACCT_STAT_HIST
DWB_EVT_LYLTY_PROG
DWB_PRTY_INTRACN_THRD
DWL_AGE_BND
DWL_AGE_ON_NET_BND
DWL_ARPU_BAND
DWL_CHRN_RSN
DWL_CUST_RVN_BND
DWL_DEBT_AGNG_BND
DWR_ACCT
DWR_ACCT_1
DWR_ACCT_PREF_PYMT_MTHD
DWR_ADDR_LOC
DWR_BSNS_MO
DWR_CNRT
DWR_CUST
DWR_DAY
DWR_PROD
DWR_PROD_MKT_PLN
DWR_PRTY
DWR_PRTY_LYLTY_PROG_PRTCPTN
DWR_SBRP
DWR_SL_CHNL_RPRSTV

### DWD_ARPU_BASE__MAP Mapping

Populate target table `DWD_ARPU_BASE`. For more information, see ARPU BASE DRVD.

Table 7-8 DWD_ARPU_BASE__MAP ETL Mapping

Source Table Name
DWB_ACCT_COST
DWB_INVC
DWB_INVC_ITEM
DWB_PROD_COST
DWB_SL_CMISN_DTL
DWD_VOI_CALL_DAY
DWL_RECHRG_RVN_SLB
DWR_ACCT
DWR_ADDR_LOC
DWR_BSNS_MO
DWR_CUST
DWR_SBRP
DWR_SL_CHNL_RPRSTV

### DWD_CALL_CNTR_CALL_DAY__MAP Mapping

Populate target table `DWD_CALL_CNTR_CALL_DAY`. For more information, see CALL CENTER CALL DAY DRVD.

Table 7-9 DWD_CALL_CNTR_CALL_DAY__MAP ETL Mapping

Source Table Name
DWB_EVT_PRTY_INTRACN_CALL
DWR_ACCT
DWR_DAY
DWR_TIME_SLT

### DWD_CALL_CNTR_CASE_DAY__MAP Mapping

Populate target table `DWD_CALL_CNTR_CASE_DAY`. For more information, see CALL CENTER CASE DAY DRVD.

Table 7-10 DWD_CALL_CNTR_CASE_DAY__MAP ETL Mapping

Source Table Name
DWB_PRTY_INTRACN_THRD
DWR_CUST
DWR_DAY

### DWD_CANBLZTN_DTL_DAY__MAP Mapping

Populate target table `DWD_CANBLZTN_DTL_DAY`. For more information, see CANNIBALIZATION DETAIL DAY DRVD.

Table 7-11 DWD_CANBLZTN_DTL_DAY__MAP ETL Mapping

Source Table Name
DWR_CHNL
DWR_CNRT_ASGN
DWR_CNRT_NEW
DWR_CNRT_OLD
DWR_DAY
DWR_PROD_MKT_PLN1
DWR_PROD_MKT_PLN2

### DWD_CMISN_DAY__MAP Mapping

Populate target table `DWD_CMISN_DAY`. For more information, see COMMISSION DAY DRVD.

Table 7-12 DWD_CMISN_DAY__MAP ETL Mapping

Source Table Name
DWB_CNRT_TERM_VAL
DWB_INVC_ITEM
DWB_SL_CMISN_DTL
DWR_CNRT
DWR_DAY
DWR_SBRP

### DWD_CNCT_DSCNCT_DAY__MAP Mapping

Populate target table `DWD_CNCT_DSCNCT_DAY`. For more information, see CONNECT DISCONNECT DAY DRVD.

Contains connect and disconnect information of particular subscriber.

Table 7-13 DWD_CNCT_DSCNCT_DAY__MAP ETL Mapping

Source Table Name
DWB_EVT
DWB_EVT_ACCS_MTHD_ACTVTY
DWR_ACCS_MTHD
DWR_ADDR_LOC
DWR_DAY
DWR_EVT_LOC

### DWD_CNRT__MAP Mapping

Populate target table `DWD_CNRT`. For more information, see CONTRACT DRVD.

Table 7-14 DWD_CNRT__MAP ETL Mapping

Source Table Name
DWB_CNRT_TERM_VAL
DWB_INVC
DWB_INVC_ITEM
DWD_CNRT_CHNG
DWL_AGE_ON_NET_BND
DWR_ACCT
DWR_ADDR_LOC
DWR_BSNS_MO
DWR_CMPGN
DWR_CNRT
DWR_CUST
DWR_PRMTN
DWR_SBRP

### DWD_CNRT_CHNG__MAP Mapping

Populate target table `DWD_CNRT_CHNG`. For more information, see CONTRACT CHANGED DRVD.

Table 7-15 DWD_CNRT_CHNG__MAP ETL Mapping

Source Table Name
DWB_CNRT_STAT
DWB_CNRT_TERM_VAL
DWB_CNRT_TERM_VAL_NEW
DWB_CNRT_TERM_VAL_OLD
DWR_BSNS_MO
DWR_CNRT
DWR_CNRT_ASGN
DWR_CNRT_NEW
DWR_CNRT_OLD
DWR_DAY
DWR_DAY_1
DWR_DAY_OLD_END_DAY
DWR_DAY_OLD_STRT_DAY

### DWD_COST_CUST__MAP Mapping

Populate target table `DWD_COST_CUST`. For more information, see COST CUSTOMER DRVD.

Table 7-16 DWD_COST_CUST__MAP ETL Mapping

Source Table Name
DWB_CUST_COST
DWR_BSNS_MO
DWR_CUST
DWR_SL_CHNL_RPRSTV

### DWD_COST_ORG__Map Mapping

Populate target table `DWD_COST_ORG`. For more information, see COST ORGANIZATIONAL DRVD.

Table 7-17 DWD_COST_ORG__MAP ETL Mapping

Source Table Name
DWB_BSNS_UNIT_COST
DWR_BSNS_MO
DWR_ORG_BSNS_UNIT

### DWD_CRDT_CTGRY__MAP Mapping

Populate target table `DWD_CRDT_CTGRY`. For more information, see CREDIT CATEGORY DRVD.

Table 7-18 DWD_CRDT_CTGRY__MAP ETL Mapping

Source Table Name
DWB_ACCT_CRDT_LMT
DWL_AGE_ON_NET_BND
DWR_BSNS_MO
DWR_CNRT
DWR_SBRP

### DWD_CUST_ACQSTN_SUMM_DAY__MAP Mapping

Populate target table `DWD_CUST_ACQSTN_SUMM_DAY`. For more information, see CUSTOMER ACQUISITION SUMMARY DAY DRVD.

Table 7-19 DWD_CUST_ACQSTN_SUMM_DAY__MAP ETL Mapping

Source Table Name
DWB_CNRT_TERM_VAL
DWB_EVT_SBRP
DWR_ADDR_LOC
DWR_CNRT
DWR_CUST
DWR_DAY
DWR_PRMTN
DWR_SBRP
DWR_SBRP_1
DWR_SL_CHNL_RPRSTV

### DWD_CUST_DEBT_COLLCTN__MAP Mapping

Populate target table `DWD_CUST_DEBT_COLLCTN`. For more information, see CUSTOMER DEBT COLLECTION DRVD.

Table 7-20 DWD_CUST_DEBT_COLLCTN__MAP ETL Mapping

Source Table Name
DWB_ACCT_BAL_HIST
DWB_ACCT_PYMT
DWB_INVC
DWR_ACCT
DWR_ADDR_LOC
DWR_CUST
DWR_DAY
DWR_PRTY_ASGN
DWR_SL_CHNL_RPRSTV

### DWD_EXTRNL_DEBT_COLLCTN_DAY__MAP Mapping

Populate target table `DWD_EXTRNL_DEBT_COLLCTN_DAY`. For more information, see EXTERNAL DEBT COLLECTION DAY DRVD.

Table 7-21 DWD_EXTRNL_DEBT_COLLCTN_DAY__MAP ETL Mapping

Source Table Name
DWB_ACCT_BAL_ADJ
DWB_ACCT_BAL_HIST
DWB_ACCT_PYMT
DWB_DEBT_COLLCTN_ASGN
DWR_ACCT
DWR_DAY

### DWD_GIVE_AWAY_ITEM_DAY__MAP Mapping

Populate target table `DWD_GIVE_AWAY_ITEM_DAY`. For more information see GIVE AWAY ITEM DAY DRVD.

Table 7-22 DWD_GIVE_AWAY_ITEM_DAY__MAP ETL Mapping

Source Table Name
DWB_EVT_LYLTY_PROG
DWB_EVT_LYLTY_PROG_RDMPTN
DWR_CNRT
DWR_DAY
DWR_PROD_MKT_PLN
DWR_PROD_RTNG_PLN_DTL

### DWD_HNDST_SUBSDY_DAY__MAP Mapping

Populate target table `DWD_HNDST_SUBSDY_DAY`. For more information, see HANDSET SUBSIDY DAY DRVD.

Table 7-23 DWD_HNDST_SUBSDY_DAY__MAP ETL Mapping

Source Table Name
DWB_ACCT_CRDT_LMT
DWB_CUST_ORDR
DWB_CUST_ORDR_LN_ITEM
DWB_CUST_ORDR_PYMT
DWB_EVT_GFT_RDMPTN
DWB_EVT_LYLTY_PROG_RDMPTN
DWL_AGE_ON_NET_BND
DWR_CNRT
DWR_CUST
DWR_DAY
DWR_HNDST_MDL
DWR_ITEM
DWR_ORG_ITEM_SLNG_PRICE
DWR_PROD
DWR_PROD_MKT_PLN
DWR_SBRP

### DWD_INTRNL_DEBT_COLLCTN_DAY__MAP Mapping

Populate target table `DWD_INTRNL_DEBT_COLLCTN_DAY`. For more information, see INTERNAL DEBT COLLECTION DAY DRVD.

Table 7-24 DWD_INTRNL_DEBT_COLLCTN_DAY__MAP ETL Mapping

Source Table Name
DWB_ACCT_BAL_ADJ
DWB_ACCT_BAL_HIST
DWB_ACCT_PYMT
DWB_DEBT_COLLCTN
DWR_ACCT
DWR_DAY

### DWD_INVC__MAP Mapping

Populate target table `DWD_INVC`. For more information, see INVOICE DRVD.

Table 7-25 DWD_INVC__MAP ETL Mapping

Source Table Name
DWB_ACCT_CRDT_LMT
DWB_INVC
DWB_INVC_ADJ
DWB_INVC_ITEM
DWL_AGE_ON_NET_BND
DWR_ACCT
DWR_ADDR_LOC
DWR_BSNS_MO
DWR_CNRT
DWR_CUST
DWR_SBRP

### DWD_INVC_ADJ__MAP Mapping

Populate target table `DWD_INVC_ADJ`. For more information, see INVOICE ADJUSTMENT DRVD.

Table 7-26 DWD_INVC_ADJ__MAP ETL Mapping

Source Table Name
DWB_INVC
DWB_INVC_ADJ
DWB_INVC_ITEM
DWR_ACCT
DWR_ADDR_LOC
DWR_BSNS_MO
DWR_CUST
DWR_SBRP

### DWD_LN_ACTVTN_TMNT_DAY__MAP Mapping

Populate target table `DWD_LN_ACTVTN_TMNT_DAY`. For more information, see LINE ACTIVATION TERMINATION DAY DRVD.

Table 7-27 DWD_LN_ACTVTN_TMNT_DAY__MAP ETL Mapping

Source Table Name
DWB_EVT_ACCT
DWR_ACCT
DWR_CMPGN
DWR_CNRT
DWR_DAY
DWR_PRMTN
DWR_SBRP
DWR_SL_CHNL_RPRSTV

### DWD_LYLTY_PROG_DAY__MAP Mapping

Populate target table `DWD_LYLTY_PROG_DAY`. For more information, see LOYALTY PROGRAM DAY DRVD.

Table 7-28 DWD_LYLTY_PROG_DAY__MAP ETL Mapping

Source Table Name
DWB_ACCT_CRDT_LMT
DWB_EVT
DWB_EVT_LYLTY_PROG
DWB_LYLTY_PROG_PTS_BAL
DWL_AGE_ON_NET_BND
DWR_ACCT
DWR_CNRT
DWR_CUST
DWR_DAY
DWR_SBRP

### DWD_MKT_SHARE__MAP Mapping

Populate target table `DWD_MKT_SHARE`. For more information, see MARKET SHARE DRVD.

Table 7-29 DWD_MKT_SHARE__MAP ETL Mapping

Source Table Name
DWB_CNRT_TERM_VAL
DWR_ADDR_LOC
DWR_BSNS_MO
DWR_CNRT
DWR_CUST
DWR_PROD
DWR_SBRP

### DWD_MKT_OPRTR_PRTNG__MAP Mapping

Populate target table `DWD_MKT_OPRTR_PRTNG`. For more information, see MARKET OPERATOR PORTING DERIVED.

The summary information about succeeded Number Porting between operators.

Table 7-30 DWD_MKT_OPRTR_PRTNG__MAP ETL Mapping

Source Table Name
DWB_ACCS_MTHD_PORT_HIST
DWR_BSNS_MO

### DWD_PRPD_ACCT_STTSTC__MAP Mapping

Populate target table `DWD_PRPD_ACCT_STTSTC`. For more information, see PREPAID ACCOUNT STATISTIC DRVD.

Table 7-31 DWD_PRPD_ACCT_STTSTC__MAP ETL Mapping

Source Table Name
DWB_ACCT_BAL_HIST
DWB_EVT_PRTY_INTRACN
DWB_PRPD_RCHRG
DWL_AGE_ON_NET_BND
DWL_INTRACN_RSN
DWL_RECHRG_RVN_SLB
DWR_ACCT
DWR_BSNS_MO
DWR_CNRT
DWR_PROD_MKT_PLN

### DWD_PRPD_ALWNCE_DAY__MAP Mapping

Populate target table `DWD_PRPD_ALWNCE_DAY`. For more information, see PREPAID ALLOWANCE DAY DRVD.

Table 7-32 DWD_PRPD_ALWNCE_DAY__MAP Mapping

Source Table Name
DWB_MNT_ALLWNC
DWR_DAY
DWR_SL_CHNL_RPRSTV

### DWD_PRPD_CALL_SUMM_DAY__MAP Mapping

Populate target table `DWD_PRPD_CALL_SUMM_DAY`. For more information, see PREPAID CALL SUMMARY DAY DRVD.

Table 7-33 DWD_PRPD_CALL_SUMM_DAY__MAP ETL Mapping

Source Table Name
DWB_WRLS_CALL_EVT
DWL_PK_OFPK_TIME
DWR_ACCT
DWR_CUST
DWR_DAY
DWR_PROD_MKT_PLN

### DWD_PYMT_AGNG_DAY__MAP Mapping

Populate target table `DWD_PYMT_AGNG_DAY`. For more information, see PAYMENT AGING DAY DRVD.

Table 7-34 DWD_PYMT_AGNG_DAY__MAP ETL Mapping

Source Table Name
DWB_ACCT_BAL_HIST
DWB_ACCT_PYMT
DWB_INV_PYMT_ASGN
DWB_INVC
DWL_AGE_ON_NET_BND
DWL_DEBT_AGNG_BND
DWR_ACCT
DWR_CUST
DWR_DAY
DWR_PRTY_ASGN
DWR_SL_CHNL_RPRSTV

### DWD_RDMPTN_DAY__MAP Mapping

Populate target table `DWD_RDMPTN_DAY`. For more information, see REDEMPTION DAY DRVD.

Table 7-35 DWD_RDMPTN_DAY__MAP ETL Mapping

Source Table Name
DWB_ACCT_CRDT_LMT
DWB_EVT
DWB_EVT_LYLTY_PROG
DWB_EVT_LYLTY_PROG_RDMPTN
DWB_EVT_PRTY_ASGN
DWB_EVT_PRTY_INTRACN
DWL_AGE_ON_NET_BND
DWR_ACCT
DWR_CNRT
DWR_DAY
DWR_EVT_PRTY_RL

### DWD_SBCRBR_CHRN_STTSTC__MAP Mapping

Populate target table `DWD_SBCRBR_CHRN_STTSTC`. For more information, see SUBSCRIBER CHURN STATISTIC DRVD.

Table 7-36 DWD_SBCRBR_CHRN_STTSTC__MAP

Source Table Name
DWB_ACCT_BAL_HIST
DWB_ACCT_CRDT_LMT
DWB_ACCT_PYMT
DWB_EVT_LYLTY_PROG
DWB_PRTY_INTRACN_THRD
DWL_AGE_BND
DWL_AGE_ON_NET_BND
DWL_ARPU_BAND
DWL_CUST_RVN_BND
DWR_ACCT
DWR_ADDR_LOC
DWR_BSNS_MO
DWR_CNRT
DWR_CUST
DWR_DAY
DWR_HNDST_INSTNC
DWR_PROD_MKT_PLN
DWR_PRTY
DWR_PRTY_LYLTY_PROG_PRTCPTN
DWR_SBRP
DWR_SBRP_ASGN
DWR_SL_CHNL_RPRSTV

### DWD_SHOP_EFFNCY_DAY__MAP Mapping

Populate target table `DWD_SHOP_EFFNCY_DAY`. For more information, see SHOP EFFICIENCY DAY DRVD.

Table 7-37 DWD_SHOP_EFFNCY_DAY__MAP

Source Table Name
DWB_EVT
DWB_EVT_ASGN
DWB_EVT_PRTY_INTRACN_VST
DWR_ADDR_LOC
DWR_DAY
DWR_ORG_BSNS_UNIT

### DWD_SHRD_PKG_USG_STTSTC_DAY__MAP Mapping

Populate target table `DWD_SHRD_PKG_USG_STTSTC_DAY`. For more information, see SHARED PACKAGE USAGE STATISTICS DAY DRVD.

Table 7-38 DWD_SHRD_PKG_USG_STTSTC_DAY__MAP ETL Mapping

Source Table Name
DWB_CNRT_TERM_VAL
DWR_ACCT
DWR_CNRT
DWR_DAY
DWR_PROD_CAPBLTY
DWR_PROD_CAPBLTY_VAL
DWR_PROD_MKT_PLN

### DWD_SL_DAY__MAP ETL Mapping

Populate target table `DWD_SL_DAY`. For more information, see SALES DAY DRVD.

Table 7-39 DWD_SHOP_EFFNCY_DAY__MAP

Source Table Name
DWB_CUST_ORDR
DWB_CUST_ORDR_LN_ITEM
DWB_CUST_ORDR_PYMT
DWB_PRTY_ORDR_ASGN
DWR_CNRT
DWR_DAY

### DWD_SL_RPRSTV_STTSTC_MO__MAP Mapping

Populate target table `DWD_SL_RPRSTV_STTSTC_MO`. For more information, see SALES REPRESENTATIVE STATISTICS DRVD.

Table 7-40 DWD_SL_RPRSTV_STTSTC_MO__MAP ETL Mapping

Source Table Name
DWB_CNRT_TERM_VAL
DWB_EVT
DWB_EVT_EQPMNT_INSTNC
DWB_SL_CMISN_DTL
DWR_BSNS_MO
DWR_CNRT
DWR_SBRP
DWR_SL_CHNL_RPRSTV
DWR_SL_CMISN_PLN_DTL

### DWD_SPLMNTR_SRVC_USG_MAP Mapping

Populate the table `DWD_SPLMNTR_SRVC_USG`. For more information, see SUPPLEMENTARY SERVICE USAGE DRVD.

Table 7-41 DWD_SPLMNTR_SRVC_USG__MAP ETL Mapping

Source Table Name
DWB_INVC
DWB_INVC_ITEM
DWB_NTWK_EVT
DWR_BSNS_MO
DWR_SPLMNTR_SRVC

### DWD_SUBSDY_AMT__MAP Mapping

Populate the table `DWD_SUBSDY_AMT`. For more information, see SUBSIDY AMOUNT DRVD.

Table 7-42 DWD_SUBSDY_AMT__MAP ETL Mapping

Source Table Name
DWR_BSNS_MO
DWR_CNRT
DWR_CUST
DWR_PROD
DWR_PROD_MKT_PLN_ASGN

### DWD_VAS_SBRP_QCK_SUMM__MAP Mapping

Populate target table `DWD_VAS_SBRP_QCK_SUMM`. For more information, see VAS SUBSCRIPTION QUICK SUMMARY DRVD.

Table 7-43 DWD_VAS_SBRP_QCK_SUMM__MAP ETL Mapping

Source Table Name
DWR_BSNS_MO
DWR_CUST
DWR_PROD
DWR_SBRP

## Intra-ETL PL/SQL Mapping Source and Target Tables

Shows the PL/SQL mapping to populate derived tables.

### DWD_DATA_USG_DAY Mapping

Populate target table `DWD_DATA_USG_DAY`. For more information, see DATA USAGE DAY DRVD.

Table 7-44 DWD_DATA_USG_DAY ETL Mapping

Source Table Name
DWB_CNTNT_DLVRY_EVT
DWB_NTWK_EVT
DWB_WRLS_CNTNT_DNLDG_EVT
DWC_INTRA_ETL_ACTIVITY
DWR_ACCS_MTHD
DWR_CNTNT
DWR_CUST
DWR_DAY
DWR_TIME_SLT

### DWD_VAS_USG_DAY Mapping

Populate target table `DWD_VAS_USG_DAY`. For more information, see VAS USAGE DAY DRVD.

Table 7-45 DWD_VAS_USG_DAY ETL Mapping

Source Table Name
DWB_ISP_USG_EVT
DWB_NTWK_EVT
DWB_UMS_EVT
DWB_WRLS_CALL_EVT
DWC_INTRA_ETL_ACTIVITY
DWL_PK_OFPK_TIME
DWR_CUST
DWR_DAY
DWR_MAILBOX
DWR_TIME_SLT
DWR_VAL_ADD_SRVC

### DWD_VOI_CALL_DAY Mapping

Populate target table `DWD_VOI_CALL_DAY`. For more information, see VOICE CALL DAY DRVD.

Table 7-46 DWD_VOI_CALL_DAY ETL Mapping

Source Table Name
DWB_CRNCY_EXCHNG_RATE
DWB_MDTD_CALL_EVT
DWB_WRLS_CALL_EVT
DWC_INTRA_ETL_ACTIVITY
DWL_CALL_TYP
DWL_PK_OFPK_TIME
DWR_ADDR_LOC
DWR_CUST
DWR_DAY
DWR_ORG_BSNS_UNIT
DWR_TIME_SLT

## Intra-ETL Process Flows

The INTRA_ETL_FLW is the complete Intra-ETL process designed using Oracle Warehouse Builder, and is composed of individual sub-process flows to populate derived aggregate tables, and relational materialized views where the data originates from base, reference, and lookup tables. This process flow respects the dependency of each individual program.

Figure 7-1 shows the main process flow INTRA_ETL_FLW.

Figure 7-1 Intra-ETL Main Process Flow

Description of "Figure 7-1 Intra-ETL Main Process Flow"

The process flow INTRA_ETL_FLW is initialized from START_PROCESS, and this checks if any previous process flows are running. If any process is running then START_PROCESS jumps to END_ERROR or START_PROCESS generate the process number from the sequence. This process number is sent as input to the Derived Flow.

In the DRVD_FLW when the START process is initiated this generates the process number and is sent as input to the Derived mapping. Once the number is generated it updates the status at backend (Control Tables). If derived mapping is successful then the derived mapping checks the status in control tables.

### Details of the DRVD_FLW Intra-ETL Flow

The DRVD_FLW sub-process flow contains all the Oracle Warehouse Builder mappings for populating derived tables, based on the content of the base, reference, and lookup tables. This sub-process flow has a dependency on the AGGR_FLW. If the DRVD_FLW is successful then it navigates to AGGR_FLW otherwise the process ends.

Figure 7-2 shows the DRVD_FLW sub-process flow for populating the derived tables.

Figure 7-2 Intra-ETL Derived Flow Sub-process (DRVD_FLW)

Description of "Figure 7-2 Intra-ETL Derived Flow Sub-process (DRVD_FLW) "

After the DRVD_FLW starts successfully, it moves to the fork. The sub-process FORK performs the derived mappings (these run in parallel). Once the activity is started then Start_Activity inserts one record in the control table and the state is set to 'Running'. The End_Activity updates the status in control tables (the state mapping is COMPLETED-SUCCESS or COMPLETED-ERROR) in the control tables. The AND activity specifies whether all the parallel mappings have been completed or not and then switches to the next activity, for example END_SUCCESS. This DRVD_FLW depends on the AGGR_FLW sub-process flow.

### Details of the AGGR_FLW Intra-ETL Flow

The AGGR_FLW sub-process flow contains PL/SQL code using Partitions Change Tracking Strategy for refreshing all the aggregate tables which are Materialized Views in Oracle Communications Data Model.

Figure 7-3 shows the AGGR_FLW sub-process flow for refreshing all the aggregate tables.

Figure 7-3 Intra-ETL Aggregate Flow Sub-process (AGGR_FLW)

Description of "Figure 7-3 Intra-ETL Aggregate Flow Sub-process (AGGR_FLW) "

After the AGGR_FLW is initiated and started successfully it is moved to the Fork. The FORK process makes the aggregates to run in parallel. The AND activity specifies that all the parallel aggregates have been completed or not and then switches over to the next activity, (for example, END_SUCCESS).

### Details of the OLAP_FLW Intra-ETL Flow

The OLAP_FLW sub-process flow triggers the OLAP package which can load data from Oracle Communications Data Model aggregate tables to Oracle Communications Data Model Analytical Workspace and calculate the forecast data. It reads OLAP ETL parameters from DWC_OLAP_ETL_PARAMETER table.

Figure 7-4 shows the OLAP_FLW sub-process flow that triggers the OLAP packages.

Figure 7-4 Intra-ETL OLAP Flow Sub-process (OLAP_FLW)

Description of "Figure 7-4 Intra-ETL OLAP Flow Sub-process (OLAP_FLW) "

### Details of the MINING_FLW Intra-ETL Flow

The MINING_FLW sub process flow triggers the data mining model.

Figure 7-5 shows the MINING_FLW sub-process flow.

Figure 7-5 Intra-ETL Mining Flow Sub-process (MINING_FLW)

Description of "Figure 7-5 Intra-ETL Mining Flow Sub-process (MINING_FLW) "