Describes the source and target tables for the Intra-ETL.
Parent topic: Intra-ETL, OLAP, Data Mining, and Utility Scripts
Note:
Do not make changes to the ETL as such changes are not supported.
In Oracle Airlines Data Model, reference tables store master and reference 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 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 and reference tables. While the source ETL operations are not a part of Oracle Airlines Data Model, the Intra-ETL operations are:
Derived Population Oracle Data Integrator defines the scripts that populate the derived tables based on the content of the base and reference tables.
Aggregate Population Oracle Data Integrator defines the scripts to refresh the Oracle Airlines Data Model aggregate tables based on the content of the derived tables and some reference tables.
Derived and Aggregate tables are implemented using Oracle tables.
For more information, see Intra-ETL Process Flows see "Intra-ETL Process Flows".
Parent topic: Oracle Airlines Data Model Intra-ETL
The value lookup models contains the lookup tables and the associated values which are used in Intra-ETL mapping.
The following table shows the tables and values which are used in join conditions and filter conditions in Intra-ETL mapping:
Table 6-1 Value Lookup Values for Intra-ETL Mapping
| Hard Coded Value Table Name | Hard Coded Value Column | Value used | ETL Program Name | ETL Usage Type |
|---|---|---|---|---|
|
DWR_SL_CHNL |
SL_CHNL_CD |
Airline Agent, Other Agent |
PKG_DWD_CALL_CNTR_PRFMNC |
Source Input |
|
DWR_INTRACN_RSLT |
INTRACN_RSLT_NAME |
Satisfy, Dissatisfy |
PKG_DWA_CUST_SRVEY |
Source Input |
|
DWR_INTRACN_RSN |
INTRACN_RSN_NAME |
Survey,Service Call,Inbound Marketing,Outbound Marketing,Customer Complaint |
PKG_DWA_CUST_SRVEY |
Source Input |
Parent topic: Oracle Airlines Data Model Intra-ETL
Lists the transformations for mapping source tables to target tables to populate Derived and Aggregate tables.
Parent topic: Oracle Airlines Data Model Intra-ETL
Shows the PKG_DWA_CUST_SRVY source and target table mapping.
Source Tables
DWD_CUST_SRVY
DWR_INTRATN_RSN
DWR_INTRATN_RSLT
Table 6-2 PKG_DWA_CUST_SRVY ETL Source to Target Mapping
| Column Name | Source Table Name | Source Column Name | Transformation Description |
|---|---|---|---|
|
CLNDR_KEY |
DWD_CUST_SRVY |
CLNDR_KEY |
Direct Mapping. The foreign key to DWR_CLNDR |
|
INTRACN_RSN_KEY |
DWD_CUST_SRVY |
INTRACN_RSN_KEY |
Direct Mapping. The foreign key to DWR_INTRACN_RSN |
|
MO_KEY |
DWD_CUST_SRVY |
MO_KEY |
Direct Mapping. It indicates the foreign key which is the primary key of the other table |
|
SATISFY_CN |
DWR_INTRACN_RSLT rslt,DWR_INTRACN_RSN rsn |
rslt.INTRACN_RSLT_NAME,rsn.INTRACN_RSN_NAME |
None |
|
SRVC_KEY |
DWD_CUST_SRVY |
SRVC_KEY |
Direct Mapping. The foreign key to DWR_SRVC |
|
TOT_SRVY_CNT |
DWR_INTRACN_RSLT rslt |
INTRACN_RSLT_NAME |
DWD_CUST_SRVY left join DWR_INTRACN_RSLT rslt on (ccp.INTRACN_RSLT_KEY=rslt.INTRACN_RSLT_KEY and rslt.CURR_IND='Y') |
Related Topics
Parent topic: Intra-ETL PL/SQL Mapping Source and Target Tables
Show the source tables for the mapping to populate target table DWA_DLY_BKG.
Source Tables
DWD_BKG_FCT
DWD_RVN_CST
DWR_CMPGN
Table 6-3 PKG_DWA_DLY_BKG_ ETL Source to Target Mapping
| Column Name | Source Table Name | Source Column Name | Transformation Description | Comments (Formula If Any) |
|---|---|---|---|---|
|
BELLY_CARGO_RVN |
DWD_RVN_CST |
CARGO_FRGHT_RVN |
DWD_BKG_FCT left join DWD_RVN_CST rvn on (rvn.DAY_KEY=bkg.CLNDR_KEY and rvn.FLT_KEY=bkg.FLT_KEY) |
SUM(Nvl(rvn.CARGO_FRGHT_RVN,0)) |
|
BKD |
DWD_BKG_FCT |
BKG_CD |
None |
Count(bkg.BKG_CD) |
|
BKG_CLS_KEY |
DWD_BKG_FCT |
BKG_CLS_KEY |
Direct Mapping. The foreign key to DWR_BKG_CLS |
None |
|
CARRIER_KEY |
DWD_BKG_FCT |
CARRIER_KEY |
Direct Mapping. The foreign key to DWR_CARRIER |
None |
|
CDSHR_RVN |
DWD_BKG_FCT |
CPN_AMT |
None |
Sum(Case When bkg.CDSHR_IND='Y' Then nvl(bkg.CPN_AMT,0) Else 0 end ) |
|
CHARTER_RVN |
DWD_RVN_CST |
OTHR_RVN_CHARTER |
DWD_BKG_FCT left join DWD_RVN_CST rvn on (rvn.DAY_KEY=bkg.CLNDR_KEY and rvn.FLT_KEY=bkg.FLT_KEY) |
SUM(Nvl(rvn.OTHR_RVN_CHARTER,0)) CHARTER_RVN |
|
CITY_KEY |
DWD_BKG_FCT |
CITY_KEY |
Direct Mapping. The foreign key to DWR_GEO |
None |
|
CLNDR_KEY |
DWD_BKG_FCT |
CLNDR_KEY |
Direct Mapping. The foreign key to DWR_CLNDR |
None |
|
CMPGN_KEY |
DWR_CMPGN |
CMPGN_KEY |
Direct Mapping. The foreign key to DWR_CMPGN |
None |
|
CNCL_CNT |
DWD_BKG_FCT |
BKG_CD |
None |
Count(Case When bkg.CNCL_DTTIME is not null Then bkg.BKG_CD end) CNCL_CNT |
|
CNCL_FROM_CNFRMD |
DWD_BKG_FCT |
BKG_CD |
None |
Count(Case When bkg.CNFRM_DTTIME<bkg.CNCL_DTTIME then bkg.BKG_CD end ) CNCL_FROM_CNFRMD |
|
CNFRMD_CNT |
DWD_BKG_FCT |
BKG_CD |
None |
Count(Case When bkg.CNFRM_DTTIME is not null Then bkg.BKG_CD end ) CNFRMD_CNT |
|
CPN_CNT |
DWD_BKG_FCT |
CPN_CD |
None |
Count(bkg.CPN_CD) CPN_CNT |
|
CRPRT_CUST_KEY |
DWD_BKG_FCT |
CRPRT_CUST_KEY |
Direct Mapping. The foreign key to DWR_CRPRT_CUST |
None |
|
EXCSS_BAG_RVN |
DWD_RVN_CST |
EXCSS_BAG_RVN |
DWD_BKG_FCT left join DWD_RVN_CST rvn on (rvn.DAY_KEY=bkg.CLNDR_KEY and rvn.FLT_KEY=bkg.FLT_KEY) |
Sum(Nvl(rvn.EXCSS_BAG_RVN,0)) EXCSS_BAG_RVN |
|
FLN_PAX_CNT |
DWD_BKG_FCT |
FLN_PAX_CNT |
Direct Mapping. This indicates the FLOWN PASSENGER COUNT |
Sum(Nvl(bkg.FLN_PAX_CNT,0)) |
|
FLN_RVN |
DWD_BKG_FCT |
FLN_RVN |
Direct Mapping. This indicates the FLOWN REVENUE |
Sum(Nvl(bkg.FLN_RVN,0)) |
|
FLN_RVN_ORGN_TO_DSTN_OFRD |
DWD_BKG_FCT |
FLN_RVN_ORGN_TO_DSTN_OFRD |
Direct Mapping. This indicates the FLOWN REVENUE ORIGIN TO DESTINATION OFFERED |
Sum(Nvl(bkg.FLN_RVN_ORGN_TO_DSTN_OFRD,0)) |
|
FLT_KEY |
DWD_BKG_FCT |
FLT_KEY |
Direct Mapping. The foreign key to DWR_FLT |
None |
|
GRP_BKD_QTY |
DWD_BKG_FCT |
BKG_CD |
None |
Count(Case When bkg.GRPNG_IND is not null Then bkg.BKG_CD end ) GRP_BKD_QTY |
|
GRP_PAX_CNT |
DWD_BKG_FCT |
PAX_KEY |
None |
Count(Case When bkg.GRPNG_IND is not null Then bkg.PAX_KEY end ) GRP_PAX_CNT |
|
INDVL_BKD_QTY |
DWD_BKG_FCT |
BKG_CD |
None |
Count(Case When bkg.GRPNG_IND is null Then bkg.BKG_CD end ) INDVL_BKD_QTY |
|
INDVL_PAX_CNT |
DWD_BKG_FCT |
PAX_KEY |
None |
Count(Case When bkg.GRPNG_IND is null Then bkg.PAX_KEY end ) INDVL_PAX_CNT |
|
MO_KEY |
DWD_BKG_FCT |
MO_KEY |
Direct Mapping. It indicates the foreign key which is the primary key of the other table |
None |
|
NET_BKD |
DWD_BKG_FCT |
BKG_CD |
None |
Count(Case When not bkg.CNFRM_DTTIME<bkg.CNCL_DTTIME then bkg.BKG_CD end ) NET_BKD |
|
NET_CNFRMD |
DWD_BKG_FCT |
KG_CD |
None |
Count(Case When bkg.CNFRM_DTTIME is not null and not bkg.CNFRM_DTTIME<bkg.CNCL_DTTIME Then bkg.BKG_CD end ) NET_CNFRMD |
|
NON_RVN_FLN_PAX_CNT |
DWD_BKG_FCT |
NON_RVN_FLN_PAX_CNT |
Direct Mapping. This indicates the NON REVENUE FLOWN PASSENGER COUNT |
Sum(Nvl(bkg.NON_RVN_FLN_PAX_CNT,0)) |
|
OFF_KEY |
DWD_BKG_FCT |
OFFC_KEY |
Direct Mapping. It indicates the foreign key which is the primary key of the other table |
No value |
|
OFRD_ORGN_TO_DSTN_FLN_PAX_CNT |
DWD_BKG_FCT |
OFRD_ORGN_TO_DSTN_FLN_PAX_CNT |
Direct Mapping. This indicates the OFFERED ORIGIN TO DESTINATION FLOWN PASSENGER COUNT |
Sum(Nvl(bkg.OFRD_ORGN_TO_DSTN_FLN_PAX_CNT,0)) |
|
ONBOARD_RVN |
DWD_BKG_FCT |
ONBOARD_RVN |
Direct Mapping. This indicates the ONBOARD REVENUE |
Sum(Nvl(bkg.ONBOARD_RVN,0)) |
|
ONFLT_ORGN_TO_DSTN_FLT_RVN |
DWD_BKG_FCT |
ONFLT_ORGN_TO_DSTN_FLT_RVN |
Direct Mapping. This indicates the ONFLIGHT ORIGIN TO DESTINATION FLIGHT REVENUE |
Sum(Nvl(bkg.ONFLT_ORGN_TO_DSTN_FLT_RVN,0)) |
|
ONFLT_ORGNTO_DSTN_FLN_PAX_CNT |
DWD_BKG_FCT |
ONFLT_ORGNTO_DSTN_FLN_PAX_CNT |
Direct Mapping. |
Sum(Nvl(bkg.ONFLT_ORGNTO_DSTN_FLN_PAX_CNT,0)) |
|
OTHR_CHRGS |
DWD_RVN_CST |
OTHR_OVRFLYNG_CHRGS |
DWD_BKG_FCT left join DWD_RVN_CST rvn on (rvn.DAY_KEY=bkg.CLNDR_KEY and rvn.FLT_KEY=bkg.FLT_KEY) |
Sum(Nvl(rvn.OTHR_OVRFLYNG_CHRGS,0)) OTHR_CHRGS |
|
OTHR_RVN |
DWD_RVN_CST |
OTHR_RVN_CHARTER |
DWD_BKG_FCT left join DWD_RVN_CST rvn on (rvn.DAY_KEY=bkg.CLNDR_KEY and rvn.FLT_KEY=bkg.FLT_KEY) |
Sum(Nvl(rvn.OTHR_RVN_CHARTER,0)) OTHR_RVN |
|
PAX_CNT |
DWD_BKG_FCT |
PAX_KEY |
None |
Count(bkg.PAX_KEY) PAX_CNT |
|
SEG_KEY |
DWD_BKG_FCT |
SEG_KEY |
Direct Mapping. The foreign key to DWR_SEG |
None |
|
SEG_PAIR_KEY |
DWD_BKG_FCT |
SEG_PAIR_KEY |
Direct Mapping. The foreign key to DWR_SEG_PAIR |
None |
|
SL_CHNL_KEY |
DWD_BKG_FCT |
SL_CHNL_KEY |
Direct Mapping. The foreign key to DWR_SL_CHNL |
None |
|
TAX_AMT |
DWD_RVN_CST |
PAX_TAX |
DWD_BKG_FCT left join DWD_RVN_CST rvn on (rvn.DAY_KEY=bkg.CLNDR_KEY and rvn.FLT_KEY=bkg.FLT_KEY) |
Sum(Nvl(rvn.PAX_TAX,0)) TAX_AMT |
|
TCKT_AMT |
DWD_BKG_FCT |
CPN_AMT |
None |
Sum(Case When bkg.TCKT_CD is not null Then Nvl(CPN_AMT,0) Else 0 end) TCKT_AMT |
|
TCKTD |
DWD_BKG_FCT |
BKG_CD |
None |
Count(Case When bkg.TCKT_CD is not null Then bkg.BKG_CD end ) TCKTD |
|
TRFC_CTGRY_KEY |
DWD_BKG_FCT |
TRFC_CTGRY_KEY |
Direct Mapping. The foreign key to DWR_TRFC_CTGRY |
None |
|
WTLSTD |
DWD_BKG_FCT |
BKG_CD |
None |
None |
Parent topic: Intra-ETL PL/SQL Mapping Source and Target Tables
Shows the source table and mapping to populate target table DWA_DLY_CALL_CNTR__PRFMNC.
Source Table
DWD_CALL_CNTR_PRFMNC
Table 6-4 PKG_DWA_DLY_CALL_CNTR_PRFMNC ETL Source to Target Mapping
| Column Name | Source Table Name | Source Column Name | Transformation Description | Comments (Formula If Any) |
|---|---|---|---|---|
|
ACCESSIBLE_CNT |
DWD_CALL_CNTR_PRFMNC |
TALK_DRTN,HNDL_BY_IVR_IND |
None |
SUM(CASE WHEN TALK_DRTN = 0 AND HNDL_BY_IVR_IND= 'Y' THEN 1 ELSE 0 END ) AS ACCESSIBLE_CNT ,COUNT(CALL_CNTR_AGNT_KEY) AS AGNT_CNT |
|
AGNT_CNT |
DWD_CALL_CNTR_PRFMNC |
CALL_CNTR_AGNT_KEY |
This indicates the CALL CENTER AGENT KEY |
COUNT(CALL_CNTR_AGNT_KEY) AS AGNT_CNT |
|
CALL_CNT |
DWD_CALL_CNTR_PRFMNC |
PRTY_INTRACN_CALL_CD |
None |
COUNT(PRTY_INTRACN_CALL_CD ) AS CALL_CNT |
|
CLNDR_KEY |
DWD_CALL_CNTR_PRFMNC |
CLNDR_KEY |
Direct Mapping. The foreign key to DWR_CLNDR |
None |
|
MNT_OF_CALL_DRTN |
DWD_CALL_CNTR_PRFMNC |
CUST_SATISFACTN_IND,DLY_CALL_CNTR_PRFMNC_KEY |
None |
COUNT(CASE WHEN CUST_SATISFACTN_IND= 'Y' THEN DLY_CALL_CNTR_PRFMNC_KEY END ) AS STFY_CNT |
|
MO_KEY |
DWD_CALL_CNTR_PRFMNC |
MO_KEY |
Direct Mapping. It indicates the foreign key which is the primary key of the other table |
None |
|
OFFC_KEY |
DWD_CALL_CNTR_PRFMNC |
OFFC_KEY |
Direct Mapping. The foreign key to DWR_BKG_OFFC |
None |
|
SATISFY_CALL |
DWD_CALL_CNTR_PRFMNC |
CUST_SATISFACTN_IND |
This indicates the SOURCE SYSTEM IDENTIFIER |
SUM(CASE WHEN CUST_SATISFACTN_IND='Y' THEN 1 ELSE 0 END) |
Parent topic: Intra-ETL PL/SQL Mapping Source and Target Tables
Shows the source table and mapping to populate target table DWA_DLY_FLT_DTLS.
Source Table
DWD_FLT_DETLS
Table 6-5 PKG_DWA_DLY_FLT_DTLS ETL Source to Target Mapping
| Column Name | Source Table Name | Source Column Name | Transformation Description | Comments (Formula If Any) |
|---|---|---|---|---|
|
ACV_TOT_CPCTY |
DWD_FLT_DTLS |
ACV_TOT_CPCTY |
Direct Mapping. This indicates the AIRCRAFTVERSION TOTAL CAPACITY |
Sum(nvl(ACV_TOT_CPCTY,0)) |
|
FLT_DT_KEY |
DWD_FLT_DTLS |
CLNDR_KEY |
Direct Mapping. The foreign key to DWR_CLNDR |
None |
|
FLT_KEY |
DWD_FLT_DTLS |
FLT_KEY |
Direct Mapping. The foreign key to DWR_FLT |
None |
|
NAUTICAL_MLS |
DWD_FLT_DTLS |
NAUTICAL_MILES |
Direct Mapping. This indicates the NAUTICAL MILES |
Sum(nvl(NAUTICAL_MILES,0)) |
|
SALEBLE_TOT_CPCTY |
DWD_FLT_DTLS |
SALEBLE_TOT_CPCTY |
Direct Mapping. This indicates the SALEBLE TOTAL CAPACITY |
Sum(nvl(SALEBLE_TOT_CPCTY,0)) |
|
SEG_KEY |
DWD_FLT_DTLS |
SEG_KEY |
Direct Mapping. The foreign key to DWR_SEG |
None |
Parent topic: Intra-ETL PL/SQL Mapping Source and Target Tables
Shows the source table and mapping to populate target DWA_DLY_LYALTY_ACCT_BKG.
Source Tables
DWD_BKG_FACT
DWR_FREQ_FLYR
Table 6-6 PKG_DWA_DLY_LYALTY_ACCT_BKG ETL Source to Target Mapping
| Column Name | Source Table Name | Source Column Name | Transformation Description | Comments (Formula If Any) |
|---|---|---|---|---|
|
ACTVTY_CNT |
DWR_FREQ_FLYR |
ACTV_ACCT_IND |
Direct Mapping. |
Sum ( Case When ff.ACTV_ACCT_IND='Y' Then 1 Else 0 end ) ACTVTY_CNT |
|
BKD_CNT |
DWD_BKG_FCT |
None |
count(1) --bkg.BKD_CNT |
|
|
CLNDR_KEY |
DWD_BKG_FCT |
CLNDR_KEY |
Direct Mapping. The foreign key to DWR_CLNDR |
None |
|
CNCL_CNT |
DWD_BKG_FCT |
CNCL_DTTIME |
None |
Sum ( Case When bkg.CNCL_DTTIME is not null Then 1 Else 0 end ) CNCL_CNT |
|
CNFRMD_CNT |
DWD_BKG_FCT |
CNFRM_DTTIME |
None |
Sum ( Case When bkg.CNFRM_DTTIME is not null Then 1 Else 0 end ) CNFRMD_CNT |
|
FLN_PAX_CNT |
DWD_BKG_FCT |
FLN_PAX_CNT |
Direct Mapping. This indicates the FLOWN PASSENGER COUNT |
Sum (Nvl(bkg.FLN_PAX_CNT,0)) FLN_PAX_CNT |
|
FLN_RVN |
DWD_BKG_FCT |
FLN_RVN |
Direct Mapping. This indicates the FLOWN REVENUE |
Sum (Nvl(bkg.FLN_RVN,0)) FLN_RVN |
|
FLN_RVN_BY_ACTVTY |
DWD_BKG_FCT,DWR_FREQ_FLYR |
FLN_RVN |
Direct Mapping. |
Sum ( Case When ff.ACTV_ACCT_IND='Y' Then Nvl(bkg.FLN_RVN,0) Else 0 end ) FLN_RVN_BY_ACTVTY |
|
FLT_CNT |
DWD_BKG_FCT |
FLT_KEY |
None |
Count(Distinct bkg.FLT_KEY) FLT_CNT |
|
LYLTY_ACCT_CNT |
DWD_BKG_FCT |
FRQTFLR_CARD_KEY |
None |
Count(Distinct bkg.FRQTFLR_CARD_KEY) LYLTY_ACCT_CNT |
|
LYLTY_LVL_KEY |
DWR_FREQ_FLYR |
LYLTY_LVL_KEY |
Direct Mapping. The foreign key to DWR_LYLTY_LVL |
None |
|
LYLTY_PROG_KEY |
DWR_FREQ_FLYR |
LYLTY_PROG_KEY |
Direct Mapping. The foreign key to DWR_LYLTY_PROG |
None |
|
MO_KEY |
DWD_BKG_FCT |
MO_KEY |
Direct Mapping. It indicates the foreign key which is the primary key of the other table |
None |
|
OFFC_KEY |
DWD_BKG_FCT |
OFFC_KEY |
It indicates the foreign key which is the primary key of the other table |
None |
|
PAX_CNT |
DWD_BKG_FCT |
PAX_KEY |
None |
Count(bkg.PAX_KEY) PAX_CNT |
|
TCKT_AMT |
DWD_BKG_FCT |
CPN_AMT |
Direct Mapping. |
Sum(Nvl(bkg.CPN_AMT,0)) TCKT_AMT |
|
TCKT_AMT_LCL |
DWD_BKG_FCT |
CPN_AMT_LCL |
Direct Mapping. |
Sum(Nvl(bkg.CPN_AMT_LCL,0)) TCKT_AMT_LCL |
|
TCKT_AMT_RPT |
DWD_BKG_FCT |
CPN_AMT_RPT |
Direct Mapping. |
Sum(Nvl(bkg.CPN_AMT_RPT,0)) TCKT_AMT_RPT |
Parent topic: Intra-ETL PL/SQL Mapping Source and Target Tables
Shows the source tables and mapping for DWA_DLY_LYALTY_ACCT.
Source Tables
DWR_FREQ_FLYR
DWD_LYLTY_ACCT_LVL_HIST
DWD_LYLTY_ACCT_BAL_HIST
Table 6-7 PKG_DWA_DLY_LYALTY_ACCT ETL Source to Target Mapping
| Column Name | Source Table Name | Source Column Name | Transformation Description | Comments (Formula If Any) |
|---|---|---|---|---|
|
ACTVTY_CNT |
DWD_LYLTY_ACCT_LVL_HIST,DWR_FREQ_FLYR |
FRQTFLR_CARD_KEY |
Direct Mapping. |
Count(Distinct CASE WHEN ff.ACTV_ACCT_IND='Y' Then lalh.FRQTFLR_CARD_KEY end ) ACTVTY_CNT |
|
CITY_KEY |
DWR_FREQ_FLYR |
CITY_KEY |
Direct Mapping. The foreign key to DWR_GEO |
None |
|
CLNDR_KEY |
DWD_LYLTY_ACCT_LVL_HIST |
CLNDR_KEY |
Direct Mapping. The foreign key to DWR_CLNDR |
None |
|
DEGRADE_CNT |
DWD_LYLTY_ACCT_LVL_HIST,DWR_FREQ_FLYR |
lalh.FRQTFLR_CARD_KEY |
DWD_LYLTY_ACCT_BAL_HIST left join DWR_FREQ_FLYR ff on (lalh.FRQTFLR_CARD_KEY=ff.LYLTY_ACCT_KEY and ff.CURR_IND='Y') |
Count(Distinct CASE WHEN last_lvl.LAST_LVL_KEY > ff.LYLTY_LVL_KEY Then lalh.FRQTFLR_CARD_KEY end ) DEGRADE_CNT |
|
LYLTY_ACCT_CNT |
DWD_LYLTY_ACCT_LVL_HIST,DWR_FREQ_FLYR |
FRQTFLR_CARD_KEY |
DWD_LYLTY_ACCT_BAL_HIST left join DWR_FREQ_FLYR ff on (lalh.FRQTFLR_CARD_KEY=ff.LYLTY_ACCT_KEY and ff.CURR_IND='Y') |
Count(Distinct CASE WHEN ff.ACCT_CLS_DT is null Then lalh.FRQTFLR_CARD_KEY end ) --LYLTY_ACCT_CNT |
|
LYLTY_LVL_KEY |
DWD_LYLTY_ACCT_LVL_HIST |
LYLTY_LVL_KEY |
Direct Mapping. The foreign key to DWR_LYLTY_LVL |
None |
|
LYLTY_PTROGRAM_KEY |
DWD_LYLTY_ACCT_BAL_HIST |
LYLTY_PROG_KEY |
The foreign key to DWR_LYLTY_PROG |
None |
|
MILES_ERND_AMT |
DWD_LYLTY_ACCT_BAL_HIST, |
labh.CURR_MILES_AMT,last_amt.LAST_MILES_AMT |
DWD_LYLTY_ACCT_LVL_HIST left join DWD_LYLTY_ACCT_BAL_HIST labh on (lalh.FRQTFLR_CARD_KEY=labh.FRQTFLR_CARD_KEY and lalh.MO_KEY=labh.MO_KEY and lalh.CLNDR_KEY=labh.CLNDR_KEY) |
Sum(Case when labh.CURR_MILES_AMT>last_amt.LAST_MILES_AMT then labh.CURR_MILES_AMT-last_amt.LAST_MILES_AMT Else 0 end ) MILES_ERND_AMT |
|
MILES_ERND_AMT_LCL |
DWD_LYLTY_ACCT_BAL_HIST |
labh.CURR_MILES_AMT_LCL,last_amt.LAST_MILES_AMT_LCL |
Direct Mapping. |
Sum(Case when labh.CURR_MILES_AMT_LCL>last_amt.LAST_MILES_AMT_LCL then labh.CURR_MILES_AMT_LCL-last_amt.LAST_MILES_AMT_LCL Else 0 end ) MILES_ERND_AMT_LCL |
|
MILES_ERND_AMT_RPT |
DWD_LYLTY_ACCT_BAL_HIST |
labh.CURR_MILES_AMT_RPT,last_amt.LAST_MILES_AMT_RPT |
Direct Mapping. |
Sum(Case when labh.CURR_MILES_AMT_RPT>last_amt.LAST_MILES_AMT_RPT then labh.CURR_MILES_AMT_RPT-last_amt.LAST_MILES_AMT_RPT Else 0 end ) MILES_ERND_AMT_RPT |
|
MILES_REDMD_AMT |
DWD_LYLTY_ACCT_BAL_HIST |
last_amt.LAST_MILES_AMT,labh.CURR_MILES_AMT |
DWD_LYLTY_ACCT_LVL_HIST left join DWD_LYLTY_ACCT_BAL_HIST labh on (lalh.FRQTFLR_CARD_KEY=labh.FRQTFLR_CARD_KEY and lalh.MO_KEY=labh.MO_KEY and lalh.CLNDR_KEY=labh.CLNDR_KEY) |
Sum(Case when labh.CURR_MILES_AMT<last_amt.LAST_MILES_AMT then last_amt.LAST_MILES_AMT - labh.CURR_MILES_AMT Else 0 end ) MILES_REDMD_AMT |
|
MILES_REDMD_AMT_LCL |
DWD_LYLTY_ACCT_BAL_HIST |
last_amt.LAST_MILES_AMT_LCL - labh.CURR_MILES_AMT_LCL |
Direct Mapping. |
Sum(Case when labh.CURR_MILES_AMT_LCL<last_amt.LAST_MILES_AMT_LCL then last_amt.LAST_MILES_AMT_LCL - labh.CURR_MILES_AMT_LCL Else 0 end ) MILES_REDMD_AMT_LCL |
|
MILES_REDMD_AMT_RPT |
DWD_LYLTY_ACCT_BAL_HIST |
last_amt.LAST_MILES_AMT_RPT - labh.CURR_MILES_AMT_RPT |
Direct Mapping. |
Sum(Case when labh.CURR_MILES_AMT_RPT<last_amt.LAST_MILES_AMT_RPT then last_amt.LAST_MILES_AMT_RPT - labh.CURR_MILES_AMT_RPT Else 0 end ) MILES_REDMD_AMT_RPT |
|
MO_KEY |
DWD_LYLTY_ACCT_LVL_HIST |
MO_KEY |
Direct Mapping. It indicates the foreign key which is the primary key of the other table |
None |
|
TOT_MILES_AMT |
DWD_LYLTY_ACCT_BAL_HIST |
labh.CURR_MILES_AMT |
DWD_LYLTY_ACCT_LVL_HIST left join DWD_LYLTY_ACCT_BAL_HIST labh on (lalh.FRQTFLR_CARD_KEY=labh.FRQTFLR_CARD_KEY and lalh.MO_KEY=labh.MO_KEY and lalh.CLNDR_KEY=labh.CLNDR_KEY) |
Sum(Case When labh.CLNDR_KEY Between labh.VALID_FROM and labh.VALID_UPTO Then labh.CURR_MILES_AMT else 0 end ) TOT_MILES_AMT |
|
TOT_MILES_AMT_LCL |
DWD_LYLTY_ACCT_BAL_HIST |
labh.CURR_MILES_AMT_LCL |
Direct Mapping. |
Sum(Case When labh.CLNDR_KEY Between labh.VALID_FROM and labh.VALID_UPTO Then labh.CURR_MILES_AMT_LCL else 0 end ) TOT_MILES_AMT_LCL |
|
TOT_MILES_AMT_RPT |
DWD_LYLTY_ACCT_BAL_HIST |
labh.CURR_MILES_AMT_RPT |
Direct Mapping. |
Sum(Case When labh.CLNDR_KEY Between labh.VALID_FROM and labh.VALID_UPTO Then labh.CURR_MILES_AMT_RPT else 0 end ) TOT_MILES_AMT_RPT |
|
UPGD_CNT |
DWD_LYLTY_ACCT_LVL_HIST |
lalh.FRQTFLR_CARD_KEY |
None |
Count(Distinct CASE WHEN last_lvl.LAST_LVL_KEY < ff.LYLTY_LVL_KEY Then lalh.FRQTFLR_CARD_KEY end ) UPGD_CNT |
Parent topic: Intra-ETL PL/SQL Mapping Source and Target Tables
Shows the source tables and mapping for PKG_DWD_BKG_FCT.
PKG_DWD_ BKG_FCT ETL Mapping Source Tables
DWB_BKG
DWB_BKG_CMPGN_ASGN
DWB_BKG_SSR
DWB_SEG_SCHL
DWB_TCKT
DWB_TCKT_CPN
DWR_CARRIER
DWR_FLT
DWR_GEO
DWR_SL_CHNL
DWR_TIME
DWR_TRFC_CTGRY
Table 6-8 PKG_DWD_ BKG_FCT ETL Source to Target Mapping
| Column Name | Source Table Name | Source Column Name | Transformation Description | Comments (Formula If Any) |
|---|---|---|---|---|
|
ACCT_KEY |
DWB_BKG |
ACCT_KEY |
Direct Mapping. |
None |
|
APIS_CMPLT_IND |
DWB_BKG |
APIS_CMPLT_IND |
Direct Mapping. |
None |
|
BID_PRC |
DWB_BKG |
BID_PRC |
Direct Mapping. |
None |
|
BKG_CD |
DWB_BKG |
BKG_CD |
Direct Mapping. |
None |
|
BKG_CLS_KEY |
DWB_BKG |
BKG_CLS_KEY |
Direct Mapping. |
None |
|
BKG_CMPGN_CD |
DWB_BKG_CMPGN_ASGN |
CMPGN_CD |
DWB_BKG left join DWB_BKG_CMPGN_ASGN bca on (bkg.BKG_CD=bca.BKG_CD) |
None |
|
BKG_CRTN_TMSTMP |
DWB_BKG |
SRC_SYS_CRTD_TMSTMP |
Direct Mapping. |
None |
|
BKG_GRP_IND |
DWB_BKG |
BKG_GRP_IND |
Direct Mapping. |
None |
|
BKG_LAST_UPDT_TMSTMP |
DWB_BKG |
SRC_SYS_UPDTD_TMSTMP |
Direct Mapping. |
None |
|
BKG_MKTG_FLT_DT |
DWB_BKG |
MKTG_FLT_DPRT_DT_TIME |
Direct Mapping. |
None |
|
BKG_MKTG_FLT_DT_LCL |
DWB_BKG |
MKTG_FLT_DPRT_DT_TIME |
Direct Mapping. |
None |
|
BKG_OPERTNG_FLT_DT_UTC |
DWB_BKG |
OPRN_FLT_DPTR_DT_UTC |
Direct Mapping. |
None |
|
BKG_OPRTNG_FLT_DT_LCL |
DWB_BKG |
OPRN_FLT_DPTR_DT |
Direct Mapping. |
None |
|
BKG_OPRTNL_FLT_DT |
DWB_BKG |
OPRN_FLT_DPTR_DT |
Direct Mapping. |
None |
|
BKG_STAT_CHNG_IND |
DWB_BKG |
BKG_STAT_CHNG_IND |
Direct Mapping. |
None |
|
BKG_TYP |
DWB_BKG |
BKG_TYP |
Direct Mapping. |
None |
|
BRDNG_IND |
DWB_BKG |
BRDNG_IND |
Direct Mapping. |
None |
|
BSNS_IND |
DWB_BKG |
BSNS_IND |
Direct Mapping. |
None |
|
CARRIER_KEY |
DWR_CARRIER |
CARRIER_KEY |
DWB_BKG left join DWR_CARRIER dc on (bkg.CARRIER_CD=dc.CARRIER_CD and dc.CURR_IND='Y') |
None |
|
CBN_CD |
DWB_BKG |
CBN_CD |
Direct Mapping. |
None |
|
CDSHR_AGRMNT |
DWB_BKG |
CDSHR_AGRMNT |
Direct Mapping. |
None |
|
CDSHR_CLS |
DWB_BKG |
CDSHR_CLS |
Direct Mapping. |
None |
|
CDSHR_IND |
CDSHR_CLS |
case when CDSHR_CLS is not null then 'Y' else 'N' end --bkg.CDSHR_IND |
||
|
CITY_KEY |
DWR_GEO |
CITY_KEY |
DWR_BKG_OFFC left join DWR_GEO geo on (bkgoff.CITY_CD=geo.CITY_CD and geo.CURR_IND='Y') |
None |
|
CLNDR_KEY |
DWB_BKG |
PNR_CRTN_DT |
to_char(bkg.PNR_CRTN_DT,'YYYYMMDD') --CLNDR_KEY |
None |
|
CLS |
DWB_BKG |
BKG_CLS |
Direct Mapping. |
None |
|
CNCL_DTTIME |
DWB_BKG |
BKG_CNCL_DTTIME |
Direct Mapping. |
None |
|
CNFRM_DTTIME |
DWB_BKG |
BKG_CNFRM_DTTIME |
Direct Mapping. |
None |
|
CNFRM_IND |
DWB_BKG |
CNFRM_IND |
Direct Mapping. |
None |
|
CPN_AMT |
DWB_TCKT |
TOT_AMT |
DWB_TCKT_CPN left join DWB_TCKT tckt on (tcpn.CPN_CD=tckt.TCKT_CD) |
None |
|
CPN_CD |
DWB_BKG |
PAX_CPN_CD |
Direct Mapping. |
None |
|
CRPRT_CUST_KEY |
DWB_BKG |
PAX_KEY |
Case When bkg.PAX_TYP='CRPRT_CUST' Then bkg.PAX_KEY end |
None |
|
CURR_STAT |
DWB_BKG |
CURR_IND |
Direct Mapping. |
None |
|
DEAD_IND |
BKG_CNCL_DTTIME |
case when BKG_CNCL_DTTIME is not null then 'Y' else 'N' end --bkg.DEAD_IND |
||
|
DWFEED_CD |
DWB_BKG |
DWFEED_CD |
Direct Mapping. |
None |
|
ECONOMY_IND |
DWB_BKG |
ECONOMY_IND |
Direct Mapping. |
None |
|
FIRST_IND |
DWB_BKG |
FIRST_IND |
Direct Mapping. |
None |
|
FLN_PAX_CNT |
DWB_BKG |
FLN_PAX_CNT |
Direct Mapping. |
None |
|
FLN_RVN |
DWB_TCKT |
TOT_AMT |
DWB_TCKT_CPN left join DWB_TCKT tckt2 on (tcpn2.CPN_CD=tckt2.TCKT_CD) |
case when bkg.FLN_DT is not null then tckt2.TOT_AMT else 0 end --bkg.FLN_RVN |
|
FLN_RVN_ORGN_TO_DSTN_OFRD |
DWB_TCKT |
TOT_AMT |
DWB_TCKT_CPN left join DWB_TCKT tckt2 on (tcpn2.CPN_CD=tckt2.TCKT_CD) |
case when bkg.FLN_DT is not null then tckt2.TOT_AMT else 0 end --bkg.FLN_RVN_ORGN_TO_DSTN_OFRD |
|
FLT_KEY |
DWR_FLT |
FLT_KEY |
DWB_BKG left join DWR_FLT flt on (bkg.FLT_NBR=flt.FLT_NBR and to_char(bkg.OPRN_FLT_DPTR_DT,'DY') =flt.WKDAY_CD and flt.CURR_IND='Y') |
None |
|
FRQTFLR_CARD_KEY |
DWB_BKG |
FRQTFLR_CARD_KEY |
Direct Mapping. |
None |
|
FRQTFLR_NBR |
DWB_BKG |
FRQTFLR_NBR |
Direct Mapping. |
None |
|
GRPNG_IND |
DWB_BKG |
GRPNG_IND |
Direct Mapping. |
None |
|
INFNT_CPN_AMT |
DWB_TCKT |
TOT_AMT |
DWB_TCKT_CPN left join DWB_TCKT tckt2 on (tcpn2.CPN_CD=tckt2.TCKT_CD) |
None |
|
INFNT_CPN_CD |
DWB_BKG |
INFNT_CPN_CD |
Direct Mapping. |
None |
|
INFO_IND |
DWB_BKG |
INFORMATIONAL_IND |
Direct Mapping. |
None |
|
MEAL_CD |
DWB_BKG |
MEAL_CD |
Direct Mapping. |
None |
|
MKTG_SEG_DPRT_DT_LCL |
DWB_SEG_SCHL |
SCHL_SEG_DPRT_DTTIME_LCL |
DWB_BKG left join DWB_SEG_SCHL segs2 on (bkg.MKTG_SEG_SCHL_CD=segs2.SEG_SCHL_CD) |
None |
|
MKTG_SEG_DPRT_DT_UTC |
DWB_SEG_SCHL |
SCHL_SEG_DPRT_DTTIME_GMT |
DWB_BKG left join DWB_SEG_SCHL segs2 on (bkg.MKTG_SEG_SCHL_CD=segs2.SEG_SCHL_CD) |
None |
|
MO_KEY |
DWB_BKG |
PNR_CRTN_DT |
It indicates the foreign key which is the primary key of the other table |
to_char(bkg.PNR_CRTN_DT,'YYYYMM') || '01' --bkg.MO_KEY |
|
NEGOSPACE_REF |
DWB_BKG |
NEGOSPACE_REF |
Direct Mapping. |
None |
|
NIGHT_IND |
DWB_BKG |
NIGHT_IND |
Direct Mapping. |
None |
|
NON_RVN_FLN_PAX_CNT |
DWB_BKG |
NON_RVN_FLN_PAX_CNT |
Direct Mapping. |
None |
|
OFF_KEY |
DWB_BKG |
OFFC_KEY |
Direct Mapping.It indicates the foreign key which is the primary key of the other table |
None |
|
OFRD_ORGN_TO_DSTN_FLN_PAX_CNT |
DWB_BKG |
OFRD_ORGN_TO_DSTN_FLN_PAX_CNT |
Direct Mapping. |
None |
|
ONBOARD_RVN |
DWB_TCKT |
TOT_AMT |
DWB_TCKT_CPN left join DWB_TCKT tckt2 on (tcpn2.CPN_CD=tckt2.TCKT_CD) |
case when bkg.CNFRM_IND='Y' then tckt2.TOT_AMT else 0 end --bkg.ONBOARD_RVN |
|
OPEN_IND |
DWB_BKG |
OPEN_IND |
Direct Mapping. |
None |
|
OPRTNG_SEG_DEPTR_LCL_DT_CD |
DWB_SEG_SCHL |
SCHL_SEG_DPRT_DTTIME_LCL |
DWB_BKG left join DWB_SEG_SCHL segs on (bkg.OPERTNG_SEG_SCHL_CD=segs.SEG_SCHL_CD ) |
None |
|
OPRTNG_SEG_DEPTR_UTC_DT_CD |
DWB_SEG_SCHL |
SCHL_SEG_DPRT_DTTIME_GMT |
DWB_BKG left join DWB_SEG_SCHL segs on (bkg.OPERTNG_SEG_SCHL_CD=segs.SEG_SCHL_CD ) |
None |
|
ORIGINL_ACTN_CD |
DWB_BKG |
ORGNL_ACTN_CD |
Direct Mapping. |
None |
|
OVERBKG_RSN |
DWB_BKG |
OVERBKG_RSN_DSCR |
Direct Mapping. |
None |
|
OVERBKG_TYP |
DWB_BKG |
OVERBKG_TYP |
Direct Mapping. |
None |
|
PAX_KEY |
DWB_BKG |
PAX_KEY |
Direct Mapping. |
None |
|
PNR_CRTN_DT |
DWB_BKG |
PNR_CRTN_DT |
Direct Mapping. |
None |
|
PNR_KEY |
DWB_BKG |
PNR_KEY |
Direct Mapping. |
None |
|
PNR_RECORDLOCATOR |
DWB_BKG |
BKG_PNR_RLOC |
Direct Mapping. |
None |
|
SEAT_NBR |
DWB_BKG |
SEAT_NBR |
Direct Mapping. |
None |
|
SEG_KEY |
DWB_SEG_SCHL |
SEG_KEY |
DWB_BKG left join DWB_SEG_SCHL segs on (bkg.OPERTNG_SEG_SCHL_CD=segs.SEG_SCHL_CD ) |
None |
|
SL_CHNL_KEY |
DWR_SL_CHNL |
SL_CHNL_KEY |
DWB_BKG left join DWR_SL_CHNL schn on (bkg.OFFC_KEY=schn.OFFC_KEY and schn.CURR_IND='Y') |
None |
|
SRC_SYS_CRTD_TMSTMP |
DWB_BKG |
SRC_SYS_CRTD_TMSTMP |
Direct Mapping. |
None |
|
SRC_SYS_DEL_IND |
DWB_BKG |
SRC_SYS_DEL_IND |
Direct Mapping. |
None |
|
SRC_SYS_ID |
DWB_BKG |
SRC_SYS_ID |
Direct Mapping. |
None |
|
SRC_SYS_UPDTD_TMSTMP |
DWB_BKG |
SRC_SYS_UPDTD_TMSTMP |
Direct Mapping. |
None |
|
SSR_KEY |
DWB_BKG_SSR |
SSR_KEY |
DWB_BKG left join DWB_BKG_SSR bssr on (bkg.BKG_CD=bssr.BKG_CD) |
None |
|
STAT_CD |
DWB_BKG |
STAT_CD |
Direct Mapping. |
None |
|
TCKT_CD |
DWB_TCKT_CPN |
TCKT_CD |
DWB_BKG left join DWB_TCKT_CPN tcpn on (bkg.PAX_CPN_CD=tcpn.CPN_CD) |
None |
|
TIME_KEY |
DWR_TIME |
TIME_KEY |
DWB_BKG left join DWR_TIME tim on (to_char(bkg.PNR_CRTN_DT,'HH24')=tim.HR_OF_DAY and to_char(bkg.PNR_CRTN_DT,'MI')=tim.MNT_OF_HR ) |
None |
|
TRFC_CTGRY_KEY |
DWR_TRFC_CTGRY |
TRFC_CTGRY_KEY |
DWB_BKG left join DWR_TRFC_CTGRY tc on (bkg.FLT_NBR=tc.FLT_NBR and tc.CURR_IND='Y') |
None |
|
TST_CD |
DWB_BKG |
TST_CD |
Direct Mapping. |
None |
|
TST_INFNT_FARE_BASIS_CD |
DWB_BKG |
TST_INFNT_FARE_BASIS_CD |
Direct Mapping. |
None |
|
TST_PAX_FARE_BASIS_CD |
DWB_BKG |
TST_PAX_FARE_BASIS_CD |
Direct Mapping. |
None |
|
WAITLIST_DTTIME |
DWB_BKG |
WAITLIST_DTTIME |
Direct Mapping. |
None |
|
WAITLIST_IND |
DWB_BKG |
WAITLIST_IND |
Direct Mapping. |
None |
|
YLD |
DWB_BKG |
YLD |
Direct Mapping. |
None |
Parent topic: Intra-ETL PL/SQL Mapping Source and Target Tables
Shows the source tables and mapping to populate target table DWD_CALL_CNTR_PRFMNC.
Source Tables
DWB_PRTY_INTRACN_CALL
DWR_CALL_CNTR
DWR_CALL_CNTR_AGNT
Table 6-9 PKG_DWD_CALL_CNTR_PRFMNC ETL Source to Target Mapping
| Column Name | Source Table Name | Source Column Name | Transformation Description | Comments (Formula If Any) |
|---|---|---|---|---|
|
CALL_CNTR_AGNT_KEY |
DWR_CALL_CNTR_AGNT |
CALL_CNTR_AGNT_KEY |
DWB_PRTY_INTRACN_CALL left join DWR_CALL_CNTR_AGNT cca on (pic.CALL_CNTR_AGNT_CD=cca.CALL_CNTR_AGNT_CD and cca.CURR_IND='Y') |
None |
|
CLNDR_KEY |
DWB_PRTY_INTRACN_CALL |
INTRACN_EVT_DT_AND_TIME |
It indicates the foreign key which is the primary key of the other table |
to_char(pic.INTRACN_EVT_DT_AND_TIME,'YYYYMMDD') |
|
CUST_SATISFACTN_IND |
DWB_PRTY_INTRACN_CALL |
CUST_SATISFACTN_IND |
Direct Mapping. This indicates the CUSTOMER SATISFACTION INDICATOR |
None |
|
HLD_DRTN |
DWB_PRTY_INTRACN_CALL |
HLD_DRTN |
Direct Mapping. This indicates the HOLD DURATION |
None |
|
HNDL_BY_IVR_IND |
DWB_PRTY_INTRACN_CALL |
HNDL_BY_IVR_IND |
Direct Mapping. This indicates the HANDLED BY IVR INDICATOR |
None |
|
INTRACN_DRTN |
DWB_PRTY_INTRACN_CALL |
INTRACN_DRTN |
Direct Mapping. This indicates the INTERACTION DURATION |
None |
|
MO_KEY |
DWB_PRTY_INTRACN_CALL |
INTRACN_EVT_DT_AND_TIME |
It indicates the foreign key which is the primary key of the other table |
to_char(pic.INTRACN_EVT_DT_AND_TIME,'YYYYMM') || '01' |
|
OFFC_KEY |
DWR_CALL_CNTR |
CALL_CNTR_KEY |
Direct Mapping. It indicates the foreign key which is the primary key of the other table |
None |
|
PRTY_INTRACN_CALL_CD |
DWB_PRTY_INTRACN_CALL |
PRTY_INTRACN_CALL_CD |
Direct Mapping. |
None |
|
QUE_DRTN |
DWB_PRTY_INTRACN_CALL |
QUE_DRTN |
Direct Mapping. This indicates the QUEUE DURATION |
None |
|
TALK_DRTN |
DWB_PRTY_INTRACN_CALL |
TALK_DRTN |
Direct Mapping. This indicates the TALK DURATION |
None |
Parent topic: Intra-ETL PL/SQL Mapping Source and Target Tables
Shows the source tables and mapping for DWD_CHKIN.
Source Tables
DWB_BKG
DWB_CHKIN
DWB_SEG_SCHL
DWR_CHKIN_BAG_GRP
DWR_LEG
Table 6-10 PKG_DWD_CHKIN ETL Source to Target Mapping
| Column Name | Source Table Name | Source Column Name | Transformation Description | Comments (Formula If Any) |
|---|---|---|---|---|
|
ACPT_DSCR |
DWB_CHKIN |
ACPT_DSCR |
Direct Mapping. This indicates the ACCEPTANCE DESCRIPTION |
None |
|
ACPT_STAT |
DWB_CHKIN |
ACPT_STAT |
Direct Mapping. This indicates the ACCEPTANCE STATUS |
None |
|
ARPRT_KEY |
DWB_CHKIN |
DPRT_ARPRT_KEY |
Direct Mapping. |
None |
|
ARRVL_ARPRT |
DWB_CHKIN |
ARRVL_ARPRT_KEY |
Direct Mapping. |
None |
|
BAG_GRP_CHKIN_BAG_CNT |
DWB_CHKIN |
TOT_BAG_NBR |
Direct Mapping. This indicates the TOTAL BAGGAGE NUMBER |
None |
|
BAG_GRP_CHKIN_BAG_WEIGHTT |
DWB_CHKIN |
TOT_BAG_WT |
Direct Mapping. This indicates the TOTAL BAGGAGE WEIGHT |
None |
|
BAG_GRP_KEY |
DWR_CHKIN_BAG_GRP |
BAG_GRP_KEY |
DWB_CHKIN left join DWR_CHKIN_BAG_GRP cbg on (dc.PDI_CD=cbg.PDI_CD and leg. CURR_IND='Y') |
None |
|
BAG_GRP_POOL_REF |
DWB_CHKIN |
BAG_GRP_POOL_REF |
Direct Mapping. |
None |
|
BAG_GRP_TYP |
DWR_CHKIN_BAG_GRP |
GRP_TYP |
DWB_CHKIN left join DWR_CHKIN_BAG_GRP cbg on (dc.PDI_CD=cbg.PDI_CD and leg. CURR_IND='Y') |
None |
|
BAG_STAT |
DWB_CHKIN |
BAG_STAT |
Direct Mapping. This indicates the BAGGAGE STATUS |
None |
|
BKG_KEY |
DWB_CHKIN |
BKG_CD |
Direct Mapping. |
None |
|
BKG_PDI_INFNT_IND |
DWB_CHKIN |
BKG_PDI_INFNT_IND |
Direct Mapping. This indicates the BOOKING PDI INFANT INDICATOR |
None |
|
BRDNG_STAT |
DWB_CHKIN |
BRDNG_STAT |
Direct Mapping. This indicates the BOARDING STATUS |
None |
|
CARRIER_KEY |
DWB_CHKIN |
CARRIER_KEY |
Direct Mapping. |
None |
|
CHNL_KEY |
DWB_CHKIN |
CHNL_KEY |
Direct Mapping. |
None |
|
CLNDR_KEY |
DWB_CHKIN |
DPRT_DT |
This indicates the DEPARTURE DATE |
to_char(dc.DPRT_DT,'YYYYMMDD') |
|
CURR_STAT |
DWB_CHKIN |
CURR_IND |
Direct Mapping. |
None |
|
CUST_REC_STAT |
DWB_CHKIN |
CUST_REC_STAT |
Direct Mapping. This indicates the CUSTOMER RECORD STATUS |
None |
|
DPRT_ARPRT |
DWB_CHKIN |
DPRT_ARPRT_KEY |
Direct Mapping. |
None |
|
DWFEED_CD |
DWB_CHKIN |
DWFEED_CD |
Direct Mapping. |
None |
|
FLT_KEY |
DWB_CHKIN |
FLT_KEY |
Direct Mapping. |
None |
|
IATC_ORGNL_INFO |
DWB_CHKIN |
IATC_ORGN_INFO |
Direct Mapping. This indicates the IATC ORIGIN INFORMATION |
None |
|
IATCI_ORGNL_CMPNY |
DWB_CHKIN |
IATCI_ORGN_CMPNY |
Direct Mapping. This indicates the IATCI ORIGIN COMPANY |
None |
|
IATCI_TRGT |
DWB_CHKIN |
IATCI_TRGT |
Direct Mapping. This indicates the IATCI TARGET |
None |
|
IATCI_TRGT_CMPNY |
DWB_CHKIN |
IATCI_TRGT_CMPNY |
Direct Mapping. This indicates the IATCI TARGET COMPANY |
None |
|
IATCI_TRGT_CMPNY_CD |
DWB_CHKIN |
IATCI_TRGT_CMPNY |
Direct Mapping. This indicates the IATCI TARGET COMPANY |
None |
|
LEG_KEY |
DWR_LEG |
LEG_KEY |
DWB_LEG_SCHL left join DWR_LEG leg on (legs.DPRT_ARPRT_KEY=leg.DPRT_ARPRT_KEY and legs. ARRVL_ARPRT_KEY=leg.ARRVL_ARPRT_KEY and leg.CURR_IND='Y') |
None |
|
MO_KEY |
DWB_CHKIN |
DPRT_DT |
to_char(dc.DPRT_DT,'YYYYMM') || '01' |
|
|
OFF_KEY |
DWB_CHKIN |
OFFC_KEY |
Direct Mapping. |
None |
|
PAX_KEY |
DWB_BKG |
PAX_KEY |
DWB_CHKIN left join DWB_BKG bkg on (dc.BKG_CD=bkg.BKG_CD) |
None |
|
PDI_CD |
DWB_CHKIN |
PDI_CD |
Direct Mapping. |
None |
|
REGRADE_CBN_CD |
DWB_CHKIN |
REGRADE_CBN_CD |
Direct Mapping. This indicates the REGRADE CABIN CODE |
None |
|
REGRADE_DSCR |
DWB_CHKIN |
REGRADE_DSCR |
Direct Mapping. This indicates the REGRADE DESCRIPTION |
None |
|
REGRADE_STAT |
DWB_CHKIN |
REGRADE_STAT |
Direct Mapping. This indicates the REGRADE STATUS |
None |
|
RESPBL_CUST_KEY |
DWB_CHKIN |
RESPBL_CUST_KEY |
Direct Mapping. |
None |
|
RESPBL_CUST_NAME |
DWB_CHKIN |
RESPBL_CUST_NAME |
Direct Mapping. This indicates the RESPONSIBLE CUSTOMER NAME |
None |
|
SEAT_DTLS |
DWB_CHKIN |
SEAT_DTL |
Direct Mapping. This indicates the SEAT DETAIL |
None |
|
SEG_KEY |
DWB_SEG_SCHL |
SEG_KEY |
DWB_CHKIN left join DWB_SEG_SCHL segs on (dc.SEG_SCHL_CD=segs.SEG_SCHL_CD) |
None |
|
TOT_BAG_CNT |
DWB_CHKIN |
TOT_BAG_NBR |
Direct Mapping. This indicates the TOTAL BAGGAGE NUMBER |
None |
Parent topic: Intra-ETL PL/SQL Mapping Source and Target Tables
Shows the mapping to populate target table DWD_CUST_SRVY.
Source Tables
DWB_PRTY_INTRACN_THRD
Table 6-11 PKG_DWD_CUST_SRVY ETL Source to Target Mapping
| Column Name | Source Table Name | Source Column Name | Transformation Description | Comments (Formula If Any) |
|---|---|---|---|---|
|
CLNDR_KEY |
DWB_PRTY_INTRACN_THRD |
INTRACN_THRD_STRT_DT |
It indicates the foreign key which is the primary key of the other table |
to_char(INTRACN_THRD_STRT_DT,'YYYYMMDD') CLNDR_KEY |
|
CUST_KEY |
DWB_PRTY_INTRACN_THRD |
CUST_KEY |
Direct Mapping. It indicates the foreign key which is the primary key of the other table |
None |
|
INTRACN_RSLT_KEY |
DWB_PRTY_INTRACN_THRD |
INTRACN_RSLT_KEY |
Direct Mapping. It indicates the foreign key which is the primary key of the other table |
None |
|
INTRACN_RSN_KEY |
DWB_PRTY_INTRACN_THRD |
INTRACN_RSN_KEY |
Direct Mapping. It indicates the foreign key which is the primary key of the other table |
None |
|
MO_KEY |
DWB_PRTY_INTRACN_THRD |
INTRACN_THRD_STRT_DT |
It indicates the foreign key which is the primary key of the other table |
to_char(INTRACN_THRD_STRT_DT,'YYYYMM') || '01' MO_KEY |
|
PRTY_INTRACN_THRD_CD |
DWB_PRTY_INTRACN_THRD |
PRTY_INTRACN_THRD_CD |
Direct Mapping. |
None |
|
SRVC_KEY |
DWB_PRTY_INTRACN_THRD |
SRVC_KEY |
Direct Mapping. It indicates the foreign key which is the primary key of the other table |
None |
Parent topic: Intra-ETL PL/SQL Mapping Source and Target Tables
Shows the mapping to populate target table DWD_FLT_DTLS.
Source Tables
DWB_FLT_SCHD
DWB_LEG_SCHL
DWB_SEG_SCHL
DWR_ARCFT_VRSN
DWR_LEG
Table 6-12 PKG_DWD_FLT_DTLS ETL Source to Target Mapping
| Column Name | Source Table Name | Source Column Name | Transformation Description | Comments (Formula If Any) |
|---|---|---|---|---|
|
ACV_KEY |
DWB_LEG_SCHL |
ACV_KEY |
DWB_FLT_SCHL left join DWB_LEG_SCHL LEG on (flt.FLT_SCHL_CD=leg.FLT_SCHL_CD) |
None |
|
ACV_TOT_CPCTY |
DWR_ARCFT_VRSN |
ACV_TOT_CPCTY |
DWB_LEG_SCHL left join DWR_ARCFT_VRSN ARCFT on (leg.ACV_KEY=arcft.ACV_KEY and arcft.CURR_IND='Y') |
None |
|
CDSHR_TYP |
DWB_FLT_SCHL |
FLT_CDSHR_TYP |
Direct Mapping. This indicates the FLIGHT CODESHARE TYPE |
None |
|
CLNDR_KEY |
DWB_LEG_SCHL |
DPRT_DTTIME_UTC |
DWB_FLT_SCHL left join DWB_LEG_SCHL LEG on (flt.FLT_SCHL_CD=leg.FLT_SCHL_CD) |
to_char(LEG.DPRT_DTTIME_UTC,'yyyymmdd') |
|
EFF_DT |
DWB_FLT_SCHL |
EFF_DT |
Direct Mapping. This indicates the EFFECTIVE DATE |
None |
|
END_DT |
DWB_FLT_SCHL |
END_DT |
Direct Mapping. This indicates the END DATE |
None |
|
FLT_ALPHA_SUFX |
DWB_FLT_SCHL |
FLT_ALPHA_SUFX |
Direct Mapping. This indicates the FLIGHT ALPHA SUFFIX |
None |
|
FLT_CARRIER_CD |
DWB_FLT_SCHL |
FLT_CARRIER_CD |
Direct Mapping. This indicates the FLIGHT CARRIER CODE |
None |
|
FLT_DT |
DWB_FLT_SCHL |
FLT_FST_DPRT_DT |
Direct Mapping. This indicates the FLIGHT FST DEPARTURE DATE |
None |
|
FLT_DTLS_FCT_KEY |
DWB_FLT_SCHL |
FLT_DTLS_FCT_KEY |
Direct Mapping. It indicates the foreign key which is the primary key of the other table |
None |
|
FLT_KEY |
DWB_FLT_SCHL |
FLT_KEY |
Direct Mapping. It indicates the foreign key which is the primary key of the other table |
None |
|
FLT_NBR |
DWB_FLT_SCHL |
FLT_NBR |
Direct Mapping. This indicates the FLIGHT NUMBER |
None |
|
LEG_KEY |
DWR_LEG |
LEG_KEY |
DWB_LEG_SCHL left join DWR_LEG DL on (leg.DPRT_ARPRT_KEY=DL.DPRT_ARPRT_KEY and leg.ARRVL_ARPRT_KEY=dl.ARRVL_ARPRT_KEY and DL.CURR_IND='Y') |
None |
|
MO_KEY |
DWB_FLT_SCHL |
DPRT_DTTIME_UTC |
It indicates the foreign key which is the primary key of the other table |
to_char(flt.DPRT_DTTIME_UTC,'yyyymm')||'01' MO_KEY |
|
NAUTICAL_MILES |
DWB_LEG_SCHL |
NAUTICAL_MILES |
DWB_FLT_SCHL left join DWB_LEG_SCHL LEG on (flt.FLT_SCHL_CD=leg.FLT_SCHL_CD) |
None |
|
NAUTICAL_TO_KM_CNVRSN_IND |
DWB_LEG_SCHL |
NAUTICAL_TO_KM_CNVRSN_IND |
DWB_FLT_SCHL left join DWB_LEG_SCHL LEG on (flt.FLT_SCHL_CD=leg.FLT_SCHL_CD) |
None |
|
SALEBLE_TOT_CPCTY |
DWB_FLT_SCHL |
TOT_SLBL_CPCTY |
Direct Mapping. This indicates the TOTAL SALEABLE CAPACITY |
None |
|
SEG_KEY |
DWB_SEG_SCHL |
SEG_KEY |
DWB_FLT_SCHL left join DWB_SEG_SCHL SEG on (leg.FLT_SCHL_CD=seg.FLT_SCHL_CD and leg.SEG_SEQ_NBR=seg.SEQ_NBR) |
None |
|
STAT_FLG |
DWB_FLT_SCHL |
STAT_FLG |
Direct Mapping. This indicates the STATUS FLAG |
None |
|
TRFC_CTGRY_KEY |
DWB_FLT_SCHL |
TRAFIC_CTGRY_CD |
Direct Mapping. It indicates the foreign key which is the primary key of the other table. |
None |
Parent topic: Intra-ETL PL/SQL Mapping Source and Target Tables
Shows the source tables and mapping to populate the target table DWD_FLT_OPRN.
Source Tables
DWB_ARCFT_DSPTCH
DWB_FLT_SCHL
DWB_CHKIN
DWL_DELAY_CAUSE
DWR_ARPRT
DWR_BKG_CLS
Table 6-13 PKG_DWD_FLT_OPRN Source to Target Mapping
| Target Column | Source Table | Source Column | Source Column - Formula |
|---|---|---|---|
|
NBR_OF_SCHL_FLT |
DWB_ARCFT_DSPTCH |
DPRT_TIME_LCL |
count((CASE WHEN DWB_ARCFT_DSPTCH.DPRT_TIME_LCL<=DWR_CLNDR.CLNDR_DT THEN 1 ELSE NULL END)NBR_OF_SCHL_FLT_1)/COUNT((CASE WHEN (TO_CHAR(DWB_ARCFT_DSPTCH.DPRT_TIME_LCL,'MI')-TO_CHAR(DWB_FLT_SCHL.DPRT_DTTIME_LCL,'MI'))>=15 AND (TO_CHAR(DWB_ARCFT_DSPTCH.DPRT_TIME_LCL,'MI')-TO_CHAR(DWB_FLT_SCHL.DPRT_DTTIME_LCL,'MI'))<=30 THEN 1 ELSE NULL END )).NBR_OF_SCHL_FLT_2) |
|
DWR_CLNDR |
CLNDR_DT |
||
|
DWB_ARCFT_DSPTCH |
DPRT_TIME_LCL |
||
|
DWB_FLT_SCHL |
DPRT_DTTIME_LCL |
||
|
NBR_OF_DPRT_FLT |
DWB_ARCFT_DSPTCH |
DPRT_TIME_LCL |
count((CASE WHEN DWB_ARCFT_DSPTCH.DPRT_TIME_LCL<=DWR_CLNDR.CLNDR_DT THEN 1 ELSE NULL END).NBR_OF_DPRT_FLT) |
|
DWR_CLNDR |
CLNDR_DT |
||
|
NBR_OF_CNCL_FLT |
DWB_ARCFT_DSPTCH |
CLNDR_IND |
COUNT((case when DWB_ARCFT_DSPTCH.CLNDR_IND='Y' THEN '1' ELSE NULL END).NBR_OF_CNCL_FLT) |
|
NBR_OF_DELAY_15_TO_30_MNT_FLT |
DWB_ARCFT_DSPTCH |
DPRT_TIME_LCL |
count((CASE WHEN (TO_CHAR(DWB_ARCFT_DSPTCH.DPRT_TIME_LCL,'MI')-TO_CHAR(DWB_FLT_SCHL.DPRT_DTTIME_LCL,'MI'))>=15 AND (TO_CHAR(DWB_ARCFT_DSPTCH.DPRT_TIME_LCL,'MI')-TO_CHAR(DWB_FLT_SCHL.DPRT_DTTIME_LCL,'MI'))<=30 THEN 1 ELSE NULL END).NBR_OF_DELAY_15_TO_30_MNT_FLT) |
|
DWB_FLT_SCHL |
DPRT_DTTIME_LCL |
||
|
NBR_OF_DELAY_30_TO_45_MNT_FLT |
DWB_ARCFT_DSPTCH |
DPRT_TIME_LCL |
count((CASE WHEN (TO_CHAR(DWB_ARCFT_DSPTCH.DPRT_TIME_LCL,'MI')-TO_CHAR(DWB_FLT_SCHL.DPRT_DTTIME_LCL,'MI'))>=15 AND (TO_CHAR(DWB_ARCFT_DSPTCH.DPRT_TIME_LCL,'MI')-TO_CHAR(DWB_FLT_SCHL.DPRT_DTTIME_LCL,'MI'))<=30 THEN 1 ELSE NULL END).NBR_OF_DELAY_15_TO_30_MNT_FLT) |
|
DWB_FLT_SCHL |
DPRT_DTTIME_LCL |
||
|
NBR_OF_DELAY_GRTR_45_MNT_FLT |
DWB_ARCFT_DSPTCH |
DPRT_TIME_LCL |
count((CASE WHEN (TO_CHAR(DWB_ARCFT_DSPTCH.DPRT_TIME_LCL,'MI')-TO_CHAR(DWB_FLT_SCHL.DPRT_DTTIME_LCL,'MI'))>45 THEN 1 ELSE NULL END ).NBR_OF_DELAY_GRTR_45_MNT_FLT) |
|
DWB_FLT_SCHL |
DPRT_DTTIME_LCL |
||
|
NBR_OF_FLT_OPERATED |
DWB_ARCFT_DSPTCH |
DPRT_TIME_LCL |
sum((CASE WHEN DWB_ARCFT_DSPTCH.DPRT_TIME_LCL<=DWB_ARCFT_DSPTCH.DPRT_TIME_LCL THEN 1 ELSE NULL END).NBR_OF_FLT_OPERATED) |
|
NBR_OF_PAX_BRDED |
DWB_CHKIN |
BRDNG_STAT |
count((CASE WHEN DWB_CHKIN.BRDNG_STAT='Y' THEN 1 ELSE NULL END).NBR_OF_PAX_BRDED) |
|
NBR_OF_DSRPTD_PAX |
DWB_CHKIN |
BRDNG_STAT |
COUNT((CASE WHEN DWB_CHKIN.BRDNG_STAT='Y' AND ((TO_CHAR(DWB_ARCFT_DSPTCH.DPRT_TIME_LCL,'MI')-TO_CHAR(DWB_FLT_SCHL.DPRT_DTTIME_LCL,'MI'))>=15 OR DWB_ARCFT_DSPTCH.CLNDR_IND='Y') THEN 1 ELSE NULL END).NBR_OF_DSRPTD_PAX) |
|
DWB_ARCFT_DSPTCH |
DPRT_TIME_LCL |
||
|
DWB_FLT_SCHL |
DPRT_DTTIME_LCL |
||
|
DWB_ARCFT_DSPTCH |
CLNDR_IND |
||
|
AVG_DELAY_FOR_NON_DSRPTD_PAX |
DWB_CHKIN |
BRDNG_STAT |
COUNT((CASE WHEN DWB_CHKIN.BRDNG_STAT='Y' AND ((TO_CHAR(DWB_ARCFT_DSPTCH.DPRT_TIME_LCL,'MI')-TO_CHAR(DWB_FLT_SCHL.DPRT_DTTIME_LCL,'MI'))<=15 ) THEN 1 ELSE NULL END ).AVG_DELAY_FOR_NON_DSRPTD_PAX_1)/COUNT((CASE WHEN DWB_CHKIN.BRDNG_STAT='Y' THEN 1 ELSE NULL END).AVG_DELAY_FOR_NON_DSRPTD_PAX_2) |
|
DWB_ARCFT_DSPTCH |
DPRT_TIME_LCL |
||
|
DWB_FLT_SCHL |
DPRT_DTTIME_LCL |
||
|
AVG_DELAY_FOR_DSRPTD_PAX |
DWB_CHKIN |
BRDNG_STAT |
COUNT((CASE WHEN DWB_CHKIN.BRDNG_STAT='Y' AND ((TO_CHAR(DWB_ARCFT_DSPTCH.DPRT_TIME_LCL,'MI')-TO_CHAR(DWB_FLT_SCHL.DPRT_DTTIME_LCL,'MI'))>=15 OR DWB_ARCFT_DSPTCH.CLNDR_IND='Y') THEN 1 ELSE NULL END).AVG_DELAY_FOR_DSRPTD_PAX_1)/COUNT((CASE WHEN DWB_CHKIN.BRDNG_STAT='Y' THEN 1 ELSE NULL END).AVG_DELAY_FOR_DSRPTD_PAX_2) |
|
DWB_ARCFT_DSPTCH |
DPRT_TIME_LCL |
||
|
DWB_FLT_SCHL |
DPRT_DTTIME_LCL |
||
|
DWB_ARCFT_DSPTCH |
CLNDR_IND |
||
|
DELAY_CAUSE_CD |
DWL_DELAY_CAUSE |
DELAY_CAUSE_CD |
None |
|
CLNDR_KEY |
DWR_CLNDR |
CLNDR_KEY |
None |
|
ARPRT_KEY |
DWR_ARPRT |
ARPRT_KEY |
None |
|
BKG_CLS_KEY |
DWR_BKG_CLS |
BKG_CLS_KEY |
None |
Parent topic: Intra-ETL PL/SQL Mapping Source and Target Tables
Shows the source tables and mapping to populate target table DWD_LYLTY_ACCT_BAL_HIST.
Source Tables
DWB_LYLTY_ACCT_BAL_HIST
DWR_FREQ_FLYR
DWR_LYLTY_ACCT
Table 6-14 PKG_DWD_LYLTY_ACCT_BAL_HIST ETL Source to Target Mapping
| Column Name | Source Table Name | Source Column Name | Transformation Description | Comments (Formula If Any) |
|---|---|---|---|---|
|
CITY_KEY |
DWR_FREQ_FLYR |
CITY_KEY |
DWB_LYLTY_ACCT_BAL_HIST left join DWR_FREQ_FLYR ff on (labh.LYLTY_ACCT_KEY=ff.LYLTY_ACCT_KEY and ff.CURR_IND='Y') |
None |
|
CLNDR_KEY |
DWB_LYLTY_ACCT_BAL_HIST |
BAL_DT |
It indicates the foreign key which is the primary key of the other table |
to_char(labh.BAL_DT,'YYYYMMDD') |
|
CURR_MILES_AMT |
DWB_LYLTY_ACCT_BAL_HIST |
CURR_MILES_AMT |
Direct Mapping. This indicates the CURRENT MILES AMOUNT |
None |
|
CURR_MILES_AMT_LCL |
DWB_LYLTY_ACCT_BAL_HIST |
CURR_MILES_AMT_LCL |
Direct Mapping. |
None |
|
CURR_MILES_AMT_RPT |
DWB_LYLTY_ACCT_BAL_HIST |
CURR_MILES_AMT_RPT |
Direct Mapping. |
None |
|
EXPIRED_MILES_AMT |
DWB_LYLTY_ACCT_BAL_HIST |
EXPIRED_MILES_AMT |
Direct Mapping. This indicates the EXPIRED MILES AMOUNT |
None |
|
EXPIRED_MILES_AMT_LCL |
DWB_LYLTY_ACCT_BAL_HIST |
EXPIRED_MILES_AMT_LCL |
Direct Mapping. |
None |
|
EXPIRED_MILES_AMT_RPT |
DWB_LYLTY_ACCT_BAL_HIST |
EXPIRED_MILES_AMT_RPT |
Direct Mapping. |
None |
|
EXTRA_MILES_AMT |
DWB_LYLTY_ACCT_BAL_HIST |
EXTRA_MILES_AMT |
Direct Mapping. This indicates the EXTRA MILES AMOUNT |
None |
|
EXTRA_MILES_AMT_LCL |
DWB_LYLTY_ACCT_BAL_HIST |
EXTRA_MILES_AMT_LCL |
Direct Mapping. |
None |
|
EXTRA_MILES_AMT_RPT |
DWB_LYLTY_ACCT_BAL_HIST |
EXTRA_MILES_AMT_RPT |
Direct Mapping. |
None |
|
FRQTFLR_CARD_KEY |
DWR_FREQ_FLYR |
FRQTFLR_CARD_KEY |
DWB_LYLTY_ACCT_BAL_HIST left join DWR_FREQ_FLYR ff on (labh.LYLTY_ACCT_KEY=ff.LYLTY_ACCT_KEY and ff.CURR_IND='Y') |
None |
|
LAST_BAL_AMT |
DWB_LYLTY_ACCT_BAL_HIST |
LAST_BAL_AMT |
Direct Mapping. This indicates the LAST BALANCE AMOUNT |
None |
|
LAST_BAL_AMT_LCL |
DWB_LYLTY_ACCT_BAL_HIST |
LAST_BAL_AMT_LCL |
Direct Mapping. |
None |
|
LAST_BAL_AMT_RPT |
DWB_LYLTY_ACCT_BAL_HIST |
LAST_BAL_AMT_RPT |
Direct Mapping. |
None |
|
LYLTY_ACCT_BAL_HIST_CD |
DWB_LYLTY_ACCT_BAL_HIST |
LYLTY_ACCT_BAL_HIST_CD |
Direct Mapping. This indicates the LOYALTY ACCOUNT BALANCE HISTORY IDENTIFIER |
None |
|
LYLTY_PROG_KEY |
DWR_LYLTY_ACCT |
LYLTY_PROG_KEY |
DWB_LYLTY_ACCT_BAL_HIST left join DWR_LYLTY_ACCT la on (labh.LYLTY_ACCT_KEY=la.LYLTY_ACCT_KEY and la.CURR_IND='Y') |
None |
|
MO_KEY |
DWB_LYLTY_ACCT_BAL_HIST |
BAL_DT |
It indicates the foreign key which is the primary key of the other table |
to_char(labh.BAL_DT,'YYYYMM') || '01' |
|
OTHR_NON_AIR_MILES_AMT |
DWB_LYLTY_ACCT_BAL_HIST |
OTHR_NON_AIR_MILES_AMT |
Direct Mapping. This indicates the OTHER NON AIR MILES AMOUNT |
None |
|
OTHR_NON_AIR_MILES_AMT_LCL |
DWB_LYLTY_ACCT_BAL_HIST |
OTHR_NON_AIR_MILES_AMT_LCL |
Direct Mapping. This indicates the OTHER NON AIR MILES AMOUNT |
None |
|
OTHR_NON_AIR_MILES_AMT_RPT |
DWB_LYLTY_ACCT_BAL_HIST |
OTHR_NON_AIR_MILES_AMT_RPT |
Direct Mapping. This indicates the OTHER NON AIR MILES AMOUNT |
None |
|
PRMTN_MILES_AMT |
DWB_LYLTY_ACCT_BAL_HIST |
PRMTN_MILES_AMT |
Direct Mapping. This indicates the PROMOTION MILES AMOUNT |
None |
|
PRMTN_MILES_AMT_LCL |
DWB_LYLTY_ACCT_BAL_HIST |
PRMTN_MILES_AMT_LCL |
Direct Mapping. |
None |
|
PRMTN_MILES_AMT_RPT |
DWB_LYLTY_ACCT_BAL_HIST |
PRMTN_MILES_AMT_RPT |
Direct Mapping. |
None |
|
REDM_MILES_AMT |
DWB_LYLTY_ACCT_BAL_HIST |
REDM_MILES_AMT |
Direct Mapping. This indicates the REDEEM MILES AMOUNT |
None |
|
REDM_MILES_AMT_LCL |
DWB_LYLTY_ACCT_BAL_HIST |
REDM_MILES_AMT_LCL |
Direct Mapping. |
None |
|
REDM_MILES_AMT_RPT |
DWB_LYLTY_ACCT_BAL_HIST |
REDM_MILES_AMT_RPT |
Direct Mapping. |
None |
Parent topic: Intra-ETL PL/SQL Mapping Source and Target Tables
Shows the mapping to populate target table DWD_LYLTY_ACCT_LVL_HIST.
Source Tables
DWB_LYLTY_ACCT_LVL_HIST
DWR_FREQ_FLYR
DWR_LYLTY_LVL
Table 6-15 PKG_DWD_LYLTY_ACCT_LVL_HIST ETL Source to Target Mapping
| Column Name | Source Table Name | Source Column Name | Transformation Description | Comments (Formula If Any) |
|---|---|---|---|---|
|
ACCT_LVL_HIST_CD |
DWB_LYLTY_ACCT_LVL_HIST |
ACCT_LVL_HIST_CD |
Direct Mapping. |
None |
|
CLNDR_KEY |
DWB_LYLTY_ACCT_LVL_HIST |
VALID_DT |
It indicates the foreign key which is the primary key of the other table |
to_char(lalh.VALID_DT,'YYYYMMDD') |
|
FRQTFLR_CARD_KEY |
DWR_FREQ_FLYR |
FRQTFLR_CARD_KEY |
DWB_LYLTY_ACCT_LVL_HIST left join DWR_FREQ_FLYR ff on (lalh.FRQTFLR_CARD_CD=ff.FRQTFLR_NBR and ff.CURR_IND='Y') |
None |
|
LYLTY_LVL_KEY |
DWR_LYLTY_LVL |
LYLTY_LVL_KEY |
DWB_LYLTY_ACCT_LVL_HIST left join DWR_LYLTY_LVL llvl on (lalh.LYLTY_LVL_CD=llvl.LYLTY_LVL_CD and llvl.CURR_IND='Y') |
None |
|
MO_KEY |
DWB_LYLTY_ACCT_LVL_HIST |
VALID_DT |
It indicates the foreign key which is the primary key of the other table |
to_char(lalh.VALID_DT,'YYYYMM') || '01' |
|
VALID_FROM |
DWB_LYLTY_ACCT_LVL_HIST |
VALID_DT |
Direct Mapping. |
None |
|
VALID_UPTO |
DWB_LYLTY_ACCT_LVL_HIST |
EXP_DT |
Direct Mapping. |
None |
Parent topic: Intra-ETL PL/SQL Mapping Source and Target Tables
Shows the mapping to populate target table DWD_NON_RUTNE_DRVD.
Source Tables
DWR_ARCFT_TYP
DWR_ARPRT
DWL_ATA_CHPTR
DWR_CLNDR
DWR_ORG_BSNS_UNIT
DWL_MEL_ATTR
DWB_WO
DWB_NON_RUTNE
DWL_MNTNCE_TYP
DWL_DFCT_STAT
DWB_MEL_CDL_PROC
DWR_FLT
DWL_DELAY_CAUSE
DWR_GEO
Transformation into Target Table
Table 6-16 PKG_DWD_NON_RUTNE_DRVD Source to Target Mapping
| Target Column | Source Table | Source Column | Source Column - Formula |
|---|---|---|---|
|
ARCFT_TYP_KEY |
DWR_ARCFT_TYP |
ARCFT_TYP_KEY |
None |
|
ARPRT_KEY |
DWR_ARPRT |
ARPRT_KEY |
None |
|
ATA_CHPTR_CD |
DWL_ATA_CHPTR |
ATA_CHPTR_CD |
None |
|
CLNDR_KEY |
DWR_CLNDR |
CLNDR_KEY |
None |
|
ORG_BSNS_UNIT_CD |
DWR_ORG_BSNS_UNIT |
ORG_BSNS_UNIT_KEY |
None |
|
MEL_ATRIB_CD |
DWL_MEL_ATTR |
MEL_ATTR_CD |
None |
|
TOT_OPEN_NON_RUTNES |
DWB_WO |
MNTNCE_TYP_CD |
COUNT(case when DWB_WO.MNTNCE_TYP_CD='NON-ROUTINE' and DWB_WO.STAT_CD='OPEN' THEN 1 ELSE NULL END) |
|
DWB_WO |
STAT_CD |
||
|
TOT_OPEN_MELS_CDLS_OF_CTGRY_A |
DWB_WO |
MNTNCE_TYP_CD |
COUJNT(case when DWB_WO.MNTNCE_TYP_CD IN ('NON-ROUTINE','MEL','CDL') and DWB_WO.STAT_CD='OPEN' AND DWB_WO.RPR_CTGRY_ID='A' THEN 1 ELSE NULL END) |
|
DWB_WO |
STAT_CD |
||
|
DWB_WO |
RPR_CTGRY_ID |
||
|
TOT_OPEN_MELS_CDLS_OF_CTGRY_B |
DWB_WO |
MNTNCE_TYP_CD |
COUNT(case when DWB_WO.MNTNCE_TYP_CD IN ('NON-ROUTINE','MEL','CDL') and DWB_WO.STAT_CD='OPEN' AND DWB_WO.RPR_CTGRY_ID='B' THEN 1 ELSE NULL END) |
|
DWB_WO |
STAT_CD |
||
|
DWB_WO |
RPR_CTGRY_ID |
||
|
TOT_OPEN_MELS_CDLS_OF_CTGRY_C |
DWB_WO |
MNTNCE_TYP_CD |
COUNT(case when DWB_WO.MNTNCE_TYP_CD IN ('NON-ROUTINE','MEL','CDL') and DWB_WO.STAT_CD='OPEN' AND DWB_WO.RPR_CTGRY_ID='C' THEN 1 ELSE NULL END) |
|
DWB_WO |
STAT_CD |
||
|
DWB_WO |
RPR_CTGRY_ID |
||
|
TOT_OPEN_MELS_CDLS_OF_CTGRY_D |
DWB_WO |
MNTNCE_TYP_CD |
COUNT(case when DWB_WO.MNTNCE_TYP_CD IN ('NON-ROUTINE','MEL','CDL') and DWB_WO.STAT_CD='OPEN' AND DWB_WO.RPR_CTGRY_ID='D' THEN 1 ELSE NULL END) |
|
DWB_WO |
STAT_CD |
||
|
DWB_WO |
RPR_CTGRY_ID |
||
|
TOT_OPEN_DFFRLS |
DWB_WO |
MNTNCE_TYP_CD |
COUNT(case when DWB_WO.MNTNCE_TYP_CD IN ('NON-ROUTINE','MEL','CDL') and DWB_WO.STAT_CD='OPEN' and DWB_WO.PLND_END_DT<sysdate THEN 1 ELSE NULL END) |
|
DWB_WO |
STAT_CD |
||
|
DWB_WO |
PLND_END_DT |
||
|
HISTCL_RSLTN_TIME |
DWB_NON_RUTNE |
DRTN |
SUM(DWB_NON_RUTNE.DRTN) |
|
AVG_NR_RPR_TIME |
DWB_WO |
ACT_STRT_DT |
AVG(TO_DATE(DWB_WO.ACT_END_DT, 'yyyy/mm/dd') - TO_DATE(DWB_WO.ACT_STRT_DT, 'yyyy/mm/dd')) |
|
DWB_WO |
ACT_END_DT |
||
|
MNTNCE_TYP_CD |
DWL_MNTNCE_TYP |
MNTNCE_TYP_CD |
None |
|
DFCT_STAT_CD |
DWL_DFCT_STAT |
DFCT_STAT_CD |
None |
|
ARCFT_KEY |
DWR_ARCFT |
ARCFT_KEY |
None |
|
TOT_OPEN_DFCT |
DWB_WO |
MNTNCE_TYP_CD |
COUNT(case when DWB_WO.MNTNCE_TYP_CD IN ('NON-ROUTINE','MEL','CDL') and DWB_WO.STAT_CD='OPEN' THEN 1 ELSE NULL END) |
|
DWB_WO |
STAT_CD |
||
|
TOT_CLOSE_DFCT |
DWB_WO |
MNTNCE_TYP_CD |
COUNT(case when DWB_WO.MNTNCE_TYP_CD IN ('NON-ROUTINE','MEL','CDL') and DWB_WO.STAT_CD='CLOSE' THEN 1 ELSE NULL END) |
|
DWB_WO |
STAT_CD |
||
|
TOT_NEW_DFCT |
DWB_WO |
MNTNCE_TYP_CD |
COUNT(case when DWB_WO.MNTNCE_TYP_CD IN ('NON-ROUTINE','MEL','CDL') and DWB_WO.STAT_CD='NEW' THEN 1 ELSE NULL END) |
|
DWB_WO |
STAT_CD |
||
|
AVG_ARCFT_NBR |
DWR_ARCFT |
ARCFT_KEY |
AVG(DWB_MEL_CDL_PROC.RCTFCATN_INTRVL) OVER (PARTITION BY DWR_ARCFT.ARCFT_KEY) |
|
DWB_MEL_CDL_PROC |
RCTFCATN_INTRVL |
||
|
AVG_ARCFT_TYP |
DWR_ARCFT_TYP |
ARCFT_TYP_KEY |
AVG(DWB_MEL_CDL_PROC.RCTFCATN_INTRVL) OVER (PARTITION BY DWR_ARCFT_TYP.ARCFT_TYP_KEY) |
|
DWB_MEL_CDL_PROC |
RCTFCATN_INTRVL |
||
|
FLT_KEY |
DWR_FLT |
FLT_KEY |
None |
|
DELAY_CAUSE_CD |
DWL_DELAY_CAUSE |
DELAY_CAUSE_CD |
None |
|
ORG_BSNS_UNIT_KEY |
DWR_ORG_BSNS_UNIT |
ORG_BSNS_UNIT_KEY |
None |
|
CITY_KEY |
DWR_GEO |
CITY_KEY |
None |
Parent topic: Intra-ETL PL/SQL Mapping Source and Target Tables
Shows the source table and mapping to populate target table DWD_RVN_CST.
Source Tables
DWB_RVN_CST_TRX
DWL_RVN_CST_ELMNT
Table 6-17 PKG_ DWD_RVN_CST_DRVD ETL Source to Target Mapping
| Column Name | Source Table Name | Source Column Name | Transformation Description | Comments (Formula If Any) |
|---|---|---|---|---|
|
SUPLIR_KEY |
DWB_RVN_CST_TRX |
SUPLIR_KEY |
Direct Mapping |
None |
|
DAY_KEY |
DWB_RVN_CST_TRX |
TRX_DT_AND_TIME |
to_char(rct.TRX_DT_AND_TIME ,'YYYYMMDD') |
|
|
FLT_KEY |
DWB_RVN_CST_TRX |
FLT_KEY |
Direct Mapping |
None |
|
ARPRT_KEY |
DWB_RVN_CST_TRX |
ARPRT_KEY |
Direct Mapping |
None |
|
MO_KEY |
DWB_RVN_CST_TRX |
TRX_DT_AND_TIME |
to_char(rct.TRX_DT_AND_TIME ,'YYYYMM') || '01' |
|
|
RVN_CST_ELMNT_CTGRY_CD |
DWL_RVN_CST_ELMNT |
RVN_CST_ELMNT_CTGRY_CD |
(select * from DWB_RVN_CST_TRX where TRX_DT_AND_TIME between l_start_date and l_end_date) rct left join DWL_RVN_CST_ELMNT rce on (rct.RVN_CST_ELMNT_CD=rce.RVN_CST_ELMNT_CD and rce.CURR_IND='Y') |
Rce. RVN_CST_ELMNT_CTGRY_CD |
|
PAX_RVN |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘PARKNG’ Then rct.AMT Else 0 end ) |
|
COURIER_RVN |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘CONCESSON’ Then rct.AMT Else 0 end ) |
|
MAIL_RVN |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘MALCHRG’ Then rct.AMT Else 0 end ) |
|
FUEL_SRCHRG |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘FLOWNTAXESREBATESEARNED’ Then rct.AMT Else 0 end ) |
|
CARGO_FRGHT_RVN |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘CABNCRWCOSTS’ Then rct.AMT Else 0 end ) |
|
EXCSS_BAG_RVN |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘EXCESSBAGGREVENUE’ Then rct.AMT Else 0 end ) |
|
EXCHNG_GAIN_LOSS |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘ENGNEPERFORMANCERESTORATONRESERVES’ Then rct.AMT Else 0 end ) |
|
INSRNC_SRCHRG_CHARTERED |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘INFLGHTCLUBCLASSHOTCOLDTOWELS’ Then rct.AMT Else 0 end ) |
|
INSRNC_SRCHRG_SCHL |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘INFLGHTHANDLNG’ Then rct.AMT Else 0 end ) |
|
OTHR_RVN_CHARTER |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘OTRHANDLNGSERVCESTOPUPBUDGETONLY’ Then rct.AMT Else 0 end ) |
|
CREW_EXPENSES_CBN |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘COURERHANDLNG’ Then rct.AMT Else 0 end ) |
|
CREW_EXPENSES_COCKPIT |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘COURERREVENUE’ Then rct.AMT Else 0 end ) |
|
CREW_WTED_UNITS_CBN |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘CRGCHRG’ Then rct.AMT Else 0 end ) |
|
CREW_WTED_UNITS_COCKPIT |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘CRGCHRGPERKG’ Then rct.AMT Else 0 end ) |
|
EUROCNTRL_AIRSPACE |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘ENGNEERNGSERVCE’ Then rct.AMT Else 0 end ) |
|
OTHR_AIRSPACE |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘OTRARSPACE’ Then rct.AMT Else 0 end ) |
|
OTHR_OVRFLYNG_CHRGS |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘OTRHANDLNGSERVCESMA’ Then rct.AMT Else 0 end ) |
|
EXCHNG_GAIN_R_LOSS_ON_FWD_CNRT |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘EUROCTRLARSPACE’ Then rct.AMT Else 0 end ) |
|
FUEL_BRNT |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘FLGHTOPERATONS’ Then rct.AMT Else 0 end ) |
|
FUEL_TAX |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘FOLLOWME’ Then rct.AMT Else 0 end ) |
|
FUEL_UPLFT |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘FRETRUCK’ Then rct.AMT Else 0 end ) |
|
ARCFT_STND |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘ARFTCONSUMABLES’ Then rct.AMT Else 0 end ) |
|
ARPRT_DVLPMNT_FEE |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘ARFTENGNEMANTENANCEOTR’ Then rct.AMT Else 0 end ) |
|
DISBRSMNT_LNDNG |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘DELAYEDFLGHTEXPS’ Then rct.AMT Else 0 end ) |
|
DISBRSMNT_LNDNG_NGT_SRCHRG |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘DPTUCTRLSYS’ Then rct.AMT Else 0 end ) |
|
DISBRSMNT_PRM |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘DSBURSEMENTARBRDGE’ Then rct.AMT Else 0 end ) |
|
EMISSNS |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘EMMSSONS’ Then rct.AMT Else 0 end ) |
|
LNDNG_BASE |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘INFRASTRUCTURE’ Then rct.AMT Else 0 end ) |
|
LNDNG_OUTSTN |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘INFRASTRUCTURELANDSDE’ Then rct.AMT Else 0 end ) |
|
LNDNG_OUTSTN_BDGT |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘INFRASTRUCTURELEVY’ Then rct.AMT Else 0 end ) |
|
LNDNG_OUTSTN_RSRV |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘INFRASTRUCTUREPAX’ Then rct.AMT Else 0 end ) |
|
LNDNG_OUTSTN_VARBLE |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘INFRASTRUCTUREPAXSURCHRGREBATE’ Then rct.AMT Else 0 end ) |
|
LNDNG_NGT_SRCHRG |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘INFRASTRUCTUREARSDE’ Then rct.AMT Else 0 end ) |
|
LNDNG_NGT_SRCHRG_BASE |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘INFRASTRUCTUREFXED’ Then rct.AMT Else 0 end ) |
|
LNDNG_NGT_SRCHRG_REBATE |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘INFRASTRUCTUREHANDLNG’ Then rct.AMT Else 0 end ) |
|
LNDNG_AND_NAVGTN_REBATES |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘INFLGHTMEALSSSUES’ Then rct.AMT Else 0 end ) |
|
NOISE_CHRG |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘NAVGATONRESERVE’ Then rct.AMT Else 0 end ) |
|
NOISE_SRCHRG |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘NEWTAX’ Then rct.AMT Else 0 end ) |
|
PRKNG |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘PAXHANDLNG’ Then rct.AMT Else 0 end ) |
|
PRKNG_REBATE |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘PAXHANDLNGMA’ Then rct.AMT Else 0 end ) |
|
NAVGTN |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘NADMSSABLEPAXEXP’ Then rct.AMT Else 0 end ) |
|
NAVGTN_NGT_SRCHRG |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘NAVGATON’ Then rct.AMT Else 0 end ) |
|
NAVGTN_RSRV |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘NAVGATONNGHTSURCHRG’ Then rct.AMT Else 0 end ) |
|
AIR_BRDNG |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘AMBULANCEDOCTORSERVCE’ Then rct.AMT Else 0 end ) |
|
AIR_CNDTNNG_UNIT |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘AMBULFTSERVCE’ Then rct.AMT Else 0 end ) |
|
ARCFT_CLENG |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘ARFTCLEANNG’ Then rct.AMT Else 0 end ) |
|
AMBULIFT_SRVC |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘ARBRDGE’ Then rct.AMT Else 0 end ) |
|
APRON_SRVCS |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘ARCONDTONNGUNT’ Then rct.AMT Else 0 end ) |
|
BAG_HNDLNG |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘BAGGDENTFCATONFXED’ Then rct.AMT Else 0 end ) |
|
BAG_ID_FIXED |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘BAGGDENTFCATONVARABLE’ Then rct.AMT Else 0 end ) |
|
BAG_ID_VARBLE |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘BAGGHANDLNG’ Then rct.AMT Else 0 end ) |
|
BAG_SCRNG |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘BAGGHANDLNGMA’ Then rct.AMT Else 0 end ) |
|
BRIEFING |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘BAGGSCREENNG’ Then rct.AMT Else 0 end ) |
|
BUS_COACHES |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘BUSESCOACHES’ Then rct.AMT Else 0 end ) |
|
BSNS_LOUNGE |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘BREFNG’ Then rct.AMT Else 0 end ) |
|
CATRNG_SRVC |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘CATERNGSERVCE’ Then rct.AMT Else 0 end ) |
|
CHK_IN_SRVCS_VARBLE |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘CHECKNSERVCESVARABLE’ Then rct.AMT Else 0 end ) |
|
CHK_IN_SRVCS |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘CHECKNSERVCES’ Then rct.AMT Else 0 end ) |
|
COCKPIT_CBN_CREW_TRNSPRT |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘CMMSPAX’ Then rct.AMT Else 0 end ) |
|
CMISN |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘CMMS’ Then rct.AMT Else 0 end ) |
|
CNCESN |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘CMMSCRG’ Then rct.AMT Else 0 end ) |
|
COURIER_HNDLNG |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘COCKPTCABNCRWTRANSPORT’ Then rct.AMT Else 0 end ) |
|
CSTM_CHRGS |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘CRGCHRGPERKGMA’ Then rct.AMT Else 0 end ) |
|
DEICING_SRVC |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘DECNGSERVCE’ Then rct.AMT Else 0 end ) |
|
DPRT_CNTRL_SYS |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘DSBURSEMENTBAGGSCREENNG’ Then rct.AMT Else 0 end ) |
|
ENGG_SRVC |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘ENGNEERNGDPRTMLCOSTS’ Then rct.AMT Else 0 end ) |
|
FCLTES |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘FACLTES’ Then rct.AMT Else 0 end ) |
|
FIXED_ELECTRIC_POWR |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘FACLTESMA’ Then rct.AMT Else 0 end ) |
|
FLT_DISPATCH |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘FLGHTDSPATCH’ Then rct.AMT Else 0 end ) |
|
FLT_OPERATIONS |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘FLGHTEQUPMENTDEPRECATON’ Then rct.AMT Else 0 end ) |
|
FOLLOW_ME |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘FLGHTEQUPMENTRENTALS’ Then rct.AMT Else 0 end ) |
|
FUEL_OVRTME |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘FLGHTOPERATONSDPRTMLCOSTS’ Then rct.AMT Else 0 end ) |
|
FUEL_SPRVSN |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘FLGHTOPSMA’ Then rct.AMT Else 0 end ) |
|
GRND_CREWS |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘GROUNDCRWS’ Then rct.AMT Else 0 end ) |
|
GRND_POWR_UNIT |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘GROUNDPOWERUNT’ Then rct.AMT Else 0 end ) |
|
GRND_TO_COCKPIT_COMUNICTN |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘GROUNDTOCOCKPTCOMM’ Then rct.AMT Else 0 end ) |
|
INFRASTRUCTURE |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘INFLGHTBARSUPPLES’ Then rct.AMT Else 0 end ) |
|
OTHR_HNDLNG_SRVCS |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘OTRHANDLNGSERVCES’ Then rct.AMT Else 0 end ) |
|
PUSHBACK |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘PAXREVENUE’ Then rct.AMT Else 0 end ) |
|
RAMP_SRVCS |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘PAXREVENUEO’ Then rct.AMT Else 0 end ) |
|
TCKT_DESK |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘TECHNCALHANDLNG’ Then rct.AMT Else 0 end ) |
|
TOILET_WATER_SRVCS |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘TECHNCALHANDLNGMA’ Then rct.AMT Else 0 end ) |
|
AIR_PAX_DUTY |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘APRONSERVCES’ Then rct.AMT Else 0 end ) |
|
FLN_TAXES_REBATES_ERND |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘FIVEYRSTRUCTURALCHECKRESERVES’ Then rct.AMT Else 0 end ) |
|
GOVT_TAX |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘GOVERNMENTTAX’ Then rct.AMT Else 0 end ) |
|
INSURANCE_TAX |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘INFLGHTMEALS’ Then rct.AMT Else 0 end ) |
|
PAX_SURTAX |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘PARKNGREBATE’ Then rct.AMT Else 0 end ) |
|
PAX_TAX |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘PAXDSRUPTONEXPS’ Then rct.AMT Else 0 end ) |
|
OPRTNL_INSURANCE |
DWB_RVN_CST_TRX |
AMT |
Same as above |
SUM(Case When rct.RVN_CST_ELMNT_CD= ‘OPERLINSU’ Then rct.AMT Else 0 end ) |
Parent topic: Intra-ETL PL/SQL Mapping Source and Target Tables
Shows the source table and mapping to populate target table DWD_TCKT.
Source Table
DWB_TKT
Table 6-18 PKG_DWD_TCKT ETL Source to Target Mapping
| Column Name | Source Table Name | Source Column Name | Transformation Description |
|---|---|---|---|
|
CNJCTVE_TCKT_CNT |
DWB_TCKT |
NBR_OF_CONJUCTIVE_TCKT |
Direct Mapping. This indicates the NUMBER OF CONJUCTIVE TICKET |
|
CRNCY |
DWB_TCKT |
TCKT_CRNCY |
Direct Mapping. This indicates the TICKET CURRENCY |
|
DT_OF_ISSUE |
DWB_TCKT |
TCKT_ISSUING_DT |
Direct Mapping. This indicates the TICKET ISSUING DATE |
|
ISSUE_OFFC_IATA_CD |
DWB_TCKT |
ISSUING_OFFC_IATA_CD |
Direct Mapping. This indicates the ISSUING OFFICE IATA CODE |
|
ISSUING_OFFC_CD |
DWB_TCKT |
ISSUING_OFFC_IATA_CD |
Direct Mapping. This indicates the ISSUING OFFICE IATA CODE |
|
MO_KEY |
DWB_TCKT |
TCKT_ISSUING_DT |
It indicates the foreign key which is the primary key of the other table |
|
PAX_TYP |
DWB_TCKT |
PAX_TYP |
Direct Mapping. This indicates the PASSENGER TYPE |
|
PRMRY_NBR |
DWB_TCKT |
PRMRY_TCKT_NBR |
Direct Mapping. This indicates the PRIMARY TICKET NUMBER |
|
TCKT_CD |
DWB_TCKT |
TCKT_CD |
Direct Mapping. This indicates the TICKET CURRENCY |
|
TOT_AMT |
DWB_TCKT |
TOT_AMT |
Direct Mapping. This indicates the TOTAL AMOUNT |
|
TOT_AMT_LCL |
DWB_TCKT |
TOT_AMT_LCL |
Direct Mapping. |
|
TOT_AMT_RPT |
DWB_TCKT |
TOT_AMT_RPT |
Direct Mapping. |
Parent topic: Intra-ETL PL/SQL Mapping Source and Target Tables
Intra-ETL is delivered as a component of Oracle Airlines Data Model. This intra-ETL is delivered as a PL/SQL package named PKG_INTRA_ETL_PROCESS which is a complete Intra-ETL process composed of sub process flows to populate the derived and aggregate tables with the data from the base and reference tables. The PKG_INTRA_ETL_PROCESS flow respects the dependency of each individual program and executes the programs in the proper order.
The PKG_INTRA_ETL_PROCESS is composed of individual sub-process procedures and functions. The sub-processes execute in the order indicated:
Populate_Derived - Populates the derived (DWD_) tables based on the content of the base (DWB_) tables.
Step 1: Populate table DWD_BKG_FCT, the code in ETL package is as follows:
PKG_DWD_BKG_FCT.Load('DWD_BKG_FCT',p_process_no);
Step 2: Populate table DWD_CALL_CNTR_PRFMNC, the code in ETL package is as follows:
PKG_DWD_CALL_CNTR_PRFMNC.Load('DWD_CALL_CNTR_PRFMNC',p_process_no);
Step 3: Populate table DWD_CHKIN, the code in ETL package is as follows:
PKG_DWD_CHKIN.Load('DWD_CHKIN',p_process_no);
Step 4: Populate table DWD_CUST_SRVY, the code in ETL package is as follows:
PKG_DWD_CUST_SRVY.Load('DWD_CUST_SRVY',p_process_no);
Step 5: Populate table DWD_FLT_DTLS, the code in ETL package is as follows:
PKG_DWD_FLT_DTLS.Load('DWD_FLT_DTLS',p_process_no);
Step 6: Populate table DWD_LYLTY_ACCT_BAL_HIST, the code in ETL package is as follows:
PKG_DWD_LYLTY_ACCT_BAL_HIST.Load('DWD_LYLTY_ACCT_BAL_HIST',p_process_no);
Step 7: Populate table DWD_LYLTY_ACCT_LVL_HIST, the code in ETL package is as follows:
PKG_DWD_LYLTY_ACCT_LVL_HIST.Load('DWD_LYLTY_ACCT_LVL_HIST',p_process_no);
Step 8: Populate table DWD_RVN_CST, the code in ETL package is as follows:
PKG_DWD_RVN_CST.Load('DWD_RVN_CST',p_process_no);
Step 9: Populate table DWD_TCKT, the code in ETL package is as follows:
PKG_DWD_TCKT.Load('DWD_TCKT',p_process_no);
Populate_Aggregate - Refreshes all of the aggregate (DWA_) tables using data from the reference (DWR_) and derived (DWD_) tables.
Step 1: Populate table DWA_CUST_SRVE, the code in ETL package is as follows:
PKG_DWA_CUST_SRVEY.Load('DWA_CUST_SRVEY',p_process_no);
Step 2: Populate table DWA_DLY_BKG, the code in ETL package is as follows:
PKG_DWA_DLY_BKG.Load('DWA_DLY_BKG',p_process_no);
Step 3: Populate table DWA_DLY_CALL_CNTR_PRFMNCE, the code in ETL package is as follows:
PKG_DWA_DLY_CALL_CNTR_PRFMNCE.Load('DWA_DLY_CALL_CNTR_PRFMNCE',p_process_no);
Step 4: Populate table DWA_DLY_FLT_DTLS, the code in ETL package is as follows:
PKG_DWA_DLY_FLT_DTLS.Load('DWA_DLY_FLT_DTLS',p_process_no);
Step 5: Populate table DWA_DLY_LYALTY_ACCT, the code in ETL package is as follows:
PKG_DWA_DLY_LYALTY_ACCT.Load('DWA_DLY_LYALTY_ACCT',p_process_no);
Step 6: Populate table DWA_DLY_LYALTY_ACCT_BKG, the code in ETL package is as follows:
PKG_DWA_DLY_LYALTY_ACCT_BKG.Load('DWA_DLY_LYALTY_ACCT_BKG',p_process_no);
Populate_Aw - Loads data from Oracle Airlines Data Model aggregate (DWA_) tables into the Oracle Airlines Data Model Analytical Workspace and calculates the forecast data. It reads OLAP ETL parameters from DWC_OLAP_ETL_PARM table.
PKG_OADM_OLAP_ETL_AW_LOAD.olap_etl_aw_build(l_build_methd,l_cube_nm,l_maxjobques,l_calc_fcst,l_no_fcst_yrs,l_fcst_mthd,l_fcst_st_yr,l_fcst_end_yr,null,null);
Populate_MINING: This sub-process flow triggers the data mining models.
PKG_OCDM_MINING.REFRESH_MODEL(l_apply_day_key,NULL);
Parent topic: Oracle Airlines Data Model Intra-ETL