6 Oracle Airlines Data Model Intra-ETL

This chapter includes the following sections:

Introduction to Oracle Airlines Data Model Intra-ETL
Value Lookup Models for PL/SQL Procedures
Intra-ETL PL/SQL Mapping Source and Target Tables
Intra-ETL Process Flows

Introduction to Oracle Airlines Data Model Intra-ETL

Note:

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

In Oracle Airlines Data Model, reference tables store master, reference, and dimensional data; and the base, derived, aggregate, and dimension 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, reference tables. While the source ETL operations are not a part of Oracle Airlines Data Model, the Intra-ETL operations are:

Derived Population: A database package containing scripts that populate the derived tables based on the content of the base and reference tables.
Aggregate Population: A database package containing scripts to refresh the Oracle Airlines Data Model aggregate tables based on the content of the derived tables and some reference tables.
Dimension Population: A database package containing all the PL/SQL packages for populating dimension tables based on the content of the reference tables.

Derived, Aggregate, and Dimension tables are implemented using Oracle tables.

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

Value Lookup Models for PL/SQL Procedures

Oracle Airlines Data Model Value_Lookup values contains the Lookup tables and its values which are used in Intra-ETL mapping. Table 6-1 shows the tables and values which are used in Join conditions and Filter conditions in Intra-ETL mapping.

Table 6-1 Shows the lookup tables and values which are used 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
DWB_BKG_H	STS_CD	HLUN,RR,TK,UC,GK,KK,HK,HX,HN,SA	DWD_BKG_FACT	Source Input
DWB_BKG_H	ORGL_ACTN_CD	SG,GI,IS,NN,PE,TK,LL,FG,GK,FF,SA,FB,SS	DWD_BKG_FACT	Source Input
DWB_BKG_H	CBN_CD	Y, J	DWD_BKG_FACT	Source Input
DWB_BKG_H	CLID_CARR_CD	AW,YY,FC	DWD_BKG_FACT	Source Input
DWB_BKG_H	TST_PAX_FARE_BASIS_CD	Null	DWD_BKG_FACT	Source Input
DWB_BKG_H	TST_INF_FARE_BASIS_CD	Null	DWD_BKG_FACT	Source Input
DWB_BKG_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWD_BKG_FACT	Parameterized - DWC_ACTIVITY_PARM
DWR_INFLT_MEAL_H	MEAL_CD	Null	DWD_BKG_FACT	Source Input
DWB_PRTY_INTRATN_THRD_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWD_CUST_SRVY	Parameterized - DWC_ACTIVITY_PARM
DWB_FLT_SCHD_H	FLT_CARR_CD	AA,AF,JL,AW,JJ,BA,CX,6X,IB,FC	DWD_FLT_DETLS_FACT	Source Input
DWB_FLT_SCHD_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWD_FLT_DETLS_FACT	Parameterized - DWC_ACTIVITY_PARM
DWB_FLT_SCHD_H	LEG_ACFT_SLBL_CONFIG_CD	Null	DWD_FLT_DETLS_FACT	Source Input
DWB_LYLTY_ACCT_BAL_HIST_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWD_LYLTY_ACCT_BAL_HIST	Parameterized - DWC_ACTIVITY_PARM
DWB_LYLTY_ACCT_LVL_HIST_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWD_LYLTY_ACCT_LVL_HIST	Parameterized - DWC_ACTIVITY_PARM
DWB_LYLTY_PRG_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWD_LYLTY_PRG	Parameterized - DWC_ACTIVITY_PARM
DWB_PNR_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWD_PNR	Parameterized - DWC_ACTIVITY_PARM
DWL_PNR_TYP_H	TYP	Null	DWD_PNR	Source Input
DWB_TKT_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWD_TKT	Parameterized - DWC_ACTIVITY_PARM
DWB_TKT_H	ISNG_OFF_IATA_CD	19491205,19491205, 19491205,38276641, 38276641,38276641, 80202662,97516241, 97516241,19270215, 19270215,19270215, 19270215,19200602, 19270215,19205012, 19205012,97516241, 19270215,19270215	DWD_TKT	Source Input
DWR_ACCT_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWM_ACCT	Parameterized - DWC_ACTIVITY_PARM
DWL_ACV_H	SLBL_CONFIG_CD	57D,A03,57E,140, A07,997,A06,A04, 123,A08,A02,124, A09,120,112,A05, B01,A01	DWM_ACFT_VER	Source Input
DWL_ACV_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWM_ACFT_VER	Parameterized - DWC_ACTIVITY_PARM
DWR_AIP_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWM_AIP	Parameterized - DWC_ACTIVITY_PARM
DWL_BKG_CLS_H	BKG_CLS_CD	X ,A ,E ,T ,I ,D, M, Q ,H ,U ,S ,P ,R ,F , Y ,O ,N ,C ,L ,Z ,B , J ,W, G ,K ,V	DWM_BKG_CLS_TYP	Source Input
DWL_BKG_CLS_H	SVC_CLS_CD	B,E	DWM_BKG_CLS_TYP	Source Input
DWL_BKG_CLS_H	CARR_CD	AF,AA,OK,RJ, CA,JL,SU,AW, BT,KF,JJ,8X, CX,BA,QF,FV, LH,6X,DY,IB, 7X,KA,FC, TP,AB,7S,AI, G3	DWM_BKG_CLS_TYP	Source Input
DWL_BKG_CLS_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWM_BKG_CLS_TYP	Parameterized - DWC_ACTIVITY_PARM
DWR_SMS_AGNT_H	AGNT_REGN_CD	EUR,NOA,AFR,SOA,SEA	DWM_BKG_OFF	Source Input
DWR_SMS_AGNT_H	AGNT_CONT_CD	AS,AF,NA,EU,SA	DWM_BKG_OFF	Source Input
DWR_SMS_AGNT_H	AGNT_CITY_CD	VEC,ESP,DUE,KAI,GRA,BLO, BAI,COL,PAP,ESS,FRA,CAM, HAR,RIV,HAN,BRE,SAN,BEI, ALG,VOI,STU,SCH,VAL,NEW,SHA, STE,DAN,GRO,FAR,TRA,COR,OSL, IGU,BOA,BHM,BER,DEA,MIL,AUB, DEN,KOR,ROS,GOS,SOU,SAO, BOL,JUJ,PHX,OSN,MAP,ZUG	DWM_BKG_OFF	Source Input
DWR_SMS_AGNT_H	AGNT_IATA_CD	5888492,1736851, 2397883,2327312, 5799334,5620987, 2320872,2349115,7834245, 8286762,2025845, 7825909,8300843, 1920178,2323171, 8300154,2325455, 2326660,2349421, 2349141	DWM_BKG_OFF	Source Input
DWR_BKG_OFF_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWM_BKG_OFF	Parameterized - DWC_ACTIVITY_PARM
DWR_BKG_OFF_H	CITY_CD	VLC,NYC,CCS, GHA,MPM,PAR, SGN,SAN,BOG, LUQ,BLR,WAS, SAP,AMS,PER, BNE,DJG,LPA, SHA,MEL	DWM_BKG_OFF	Source Input
DWR_BKG_OFF_H	CORP_CD	AA,1S,AF, MH,1V,1E, 1A,1P,CX, BA,1G,1B	DWM_BKG_OFF	Source Input
DWR_BKG_OFF_H	CTRY_CD	US,ES,AL, DZ,BE,VN, AR,FR,MZ, PF,NO,HK, GB,CN,DE, CO,AU,HN, BR,IN,VE	DWM_BKG_OFF	Source Input
DWR_BKG_OFF_H	IATA_CD	17393165,91238943, 80203443,2405093, 78250406,8286762, 2397883,5888492, 5799334,2327312, 2349115,5620987, 7834245,2320872, 80207540,7825909, 2349421, 2323171	DWM_BKG_OFF	Source Input
DWR_BKG_OFF_H	TRUE_CITY_CD	LON,VLC,CCS, NYC,GHA,PAR, MPM,TEE,BOG, SGN,LUQ,WAS, SAP,BLR,SAN, AMS,PER,BNE, DJG,LPA,DFW, SWI,SHA,MAD, MEL,OSL,ADL, MUC,MJV,DEN, BDL,GNB,PPT, LAX,SYD,ELU, CAN,BJS,HKG, PHX,BRU,SFO, SAO,SNA	DWM_BKG_OFF	Source Input
DWR_SMS_AGNT_H	AGNT_CNTY_CD	Null	DWM_BKG_OFF	Source Input
DWR_BKG_PAX_H	TYP_CD	INF,ADT	DWM_BKG_PAX	Source Input
DWR_BKG_PAX_H	IDFN_CD	Null	DWM_BKG_PAX	Source Input
DWR_BKG_PAX_H	VIP_CARR_CD	Null	DWM_BKG_PAX	Source Input
DWR_BKG_PAX_H	CLNTID_CARR_CD	Null	DWM_BKG_PAX	Source Input
DWR_BKG_PAX_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWM_BKG_PAX	Parameterized - DWC_ACTIVITY_PARM
DWR_BKG_TST_H	VLDT_CARR	Null	DWM_BKG_TST	Source Input
DWR_BKG_TST_H	TOUR_CD	Null	DWM_BKG_TST	Source Input
DWR_BKG_TST_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWM_BKG_TST	Parameterized - DWC_ACTIVITY_PARM
DWR_CARR_H	CARR_CD	AF,AA,OK,RJ,CA,JL,SU, AW,BT,KF,JJ,8X,CX,BA, QF,FV,LH,6X,DY,IB,7X, KA,FC,TP,AB,7S,AI,G3	DWM_CARR	Source Input
DWR_SMS_CUST_H	CUST_CRCY_CD	Null	DWM_CORP_CUST	Source Input
DWR_SMS_CUST_H	CUST_CLNT_CD	Null	DWM_CORP_CUST	Source Input
DWR_SMS_CUST_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWM_CORP_CUST	Parameterized - DWC_ACTIVITY_PARM
DWR_FLT_H	CARR_CD	AF,AA,OK,RJ,CA,JL,SU, AW,BT,KF,JJ,8X,CX,BA, QF,FV,LH,6X,DY,IB, 7X,KA,FC,TP,AB,7S,AI, G3	DWM_FLT	Source Input
DWR_FLT_H	SCNDRY_CARR_CD	DWM_FLT	Source Input
DWR_FLT_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWM_FLT	Parameterized - DWC_ACTIVITY_PARM
DWR_FRQTFLR_H	CARR_CD	AF,AA,OK,RJ, CA,JL,SU,AW, BT,KF,JJ,8X, CX,BA,QF,FV, LH,6X,DY,IB, 7X,KA,FC, TP,AB,7S,AI,G3	DWM_FRQTFLR	Source Input
DWR_FRQTFLR_H	STS_CD	HL,UN,RR, TK,UC,GK, KK,HK,HX, HN,SA	DWM_FRQTFLR	Source Input
DWR_FRQTFLR_H	AIRL_PRORTY_CD	2000,4000,6000, 9997,9999	DWM_FRQTFLR	Source Input
DWR_FRQTFLR_H	ALANC_CD	O,S,*A	DWM_FRQTFLR	Source Input
DWR_FRQTFLR_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWM_FRQTFLR	Parameterized - DWC_ACTIVITY_PARM
DWL_INTRATN_RSLT_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWM_INTRATN_RSLT	Parameterized - DWC_ACTIVITY_PARM
DWL_INTRATN_RSN_H	INTRATN_RSN_CD	Null	DWM_INTRATN_RSN	Source Input
DWL_INTRATN_RSN_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWM_INTRATN_RSN	Parameterized - DWC_ACTIVITY_PARM
DWR_LEG_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWM_LEG	Parameterized - DWC_ACTIVITY_PARM
DWR_LYLTY_LVL_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWM_LYLTY_LVL	Parameterized - DWC_ACTIVITY_PARM
DWL_PDI_CHNL_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWM_PDI_CHNL	Parameterized - DWC_ACTIVITY_PARM
DWL_SALES_CHNL_H	SALES_CHNL_CD	Swiftair Agent, Other Agent, Airline Agent	DWM_SALES_CHNL	Source Input
DWL_SALES_CHNL_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWM_SALES_CHNL	Parameterized - DWC_ACTIVITY_PARM
DWR_SEG_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWM_SEG	Parameterized - DWC_ACTIVITY_PARM
DWR_SEG_H	OFPNT_CITY	NYC,CZL,RIX, LON,REP,CBR, STO,FRA,NGO, LED,MAN,BUJ, DEL,PAR,NCE, HRM,GOT,QAS, HNL,TLL	DWM_SEG_PAIR	Source Input
DWR_SVC_H	SVC_TYP_CD	1,2,3	DWM_SVC	Source Input
DWR_SVC_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWM_SVC	Parameterized - DWC_ACTIVITY_PARM
DWL_TRAF_CATG_H	DATA_MVT_STS_CD	P (Processed), N (New)	DWM_TRAF_CATG	Parameterized - DWC_ACTIVITY_PARM

Intra-ETL PL/SQL Mapping Source and Target Tables

Table 6-3 shows the PL/SQL packages for mapping source tables to target tables to populate Aggregate tables.

Table 6-4 shows the PL/SQL packages for mapping source tables to target tables to populate tables.

Table 6-3 shows the PL/SQL packages for mapping source tables to target tables to populate Dimension tables.

Table 6-2 shows the parameter abbreviations used in PL/SQL mappings.

Table 6-2 Intra-ETL Parameter Abbreviations

Abbreviation	Meaning
pv_	Parameter variable
lv_	Local variable

Table 6-3 Intra-ETL Aggregate Table Mapping Packages

Package Name
PKG_DWA_CUST_SRVY Mapping
PKG_DWA_DLY_BKG_FACT Mapping
PKG_DWA_DLY_CALL_CNTR_PRFMNC Mapping
PKG_DWA_DLY_FLT_DETLS Mapping
PKG_DWA_DLY_LYLTY_ACCT_BKG Mapping
PKG_DWA_DLY_LYLTY_ACCT Mapping

Table 6-4 Intra-ETL Derived Table Mapping Packages

Package Name
PKG_DWD_BKG_FACT Mapping
PKG_DWD_CALL_CNTR_PRFMNC Mapping
PKG_DWD_CUST_SRVY Mapping
PKG_DWD_FLT_DETLS_FACT Mapping
PKG_DWD_LYLTY_ACCT_BAL_HIST Mapping
PKG_DWD_LYLTY_ACCT_LVL_HIST Mapping
PKG_DWD_LYLTY_PRG Mapping
PKG_DWD_PNR Mapping
PKG_DWD_TKT Mapping

Table 6-5 Intra-ETL Dimension Table Mapping Packages

Package Name
PKG_DWM_ACCT Mapping
PKG_DWM_ACFT_VER Mapping
PKG_DWM_AIP Procedure
PKG_DWM_BKG_CLS_TYP Procedure
PKG_DWM_BKG_OFF Mapping
PKG_DWM_BKG_PAX Mapping
PKG_DWM_BKG_TST Mapping
PKG_DWM_CARR Mapping
PKG_DWM_CORP_CUST Mapping
PKG_DWM_FLT Mapping
PKG_DWM_FRQTFLR Mapping
PKG_DWM_INTRATN_RSLT Mapping
PKG_DWM_INTRATN_RSN Mapping
PKG_DWM_LEG Mapping
PKG_DWM_LYLTY_LVL Mapping
PKG_DWM_PDI_CHNL Mapping
PKG_DWM_SALES_CHNL Mapping
PKG_DWM_SEG Mapping
PKG_DWM_SEG_PAIR Mapping
PKG_DWM_SVC Mapping
PKG_DWM_TRAF_CATG Mapping

PKG_DWA_CUST_SRVY Mapping

Table 6-6 shows the source to target mapping to populate target table DWA_CUST_SRVY. For more information, see CUSTOMER SURVEY.

Source Tables

DWD_CUST_SRVY

DWM_CLNDR

DWM_INTRATN_RSN

DWM_INTRATN_RESLT

Table 6-6 PKG_DWA_CUST_SRVY ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
DLY_CUST_SRVY_KEY	DWD_CUST_SRVY	SEQ_DWA_CUST_SRVY.NEXTVAL	Direct mapping. It is the sequence key generated where the data will be inserted in sequential manner
SVC_KEY	DWD_CUST_SRVY	SVC_KEY	Direct Mapping. The foreign key to DWR_SERVICE
INTRATN_RSN_KEY	DWD_CUST_SRVY	INTRATN_RSN_KEY	Direct Mapping. The foreign key to DWL_INTERACTION_RSN
CLNDR_KEY	dwm_clndr	clndr_key	Left outer join is performed on the Calendar table on the basis of Calendar key to get the calendar key values	Join performed between DWM_CLNDR and DWD_CUST_SRVY table on CLNDR_KEY column to fetch value for CLNDR_KEY
STSFY_CNT	DWM_INTRATN_RSN/ DWM_INTRATN_RESLT	STFY_CNT	Left outer join is performed on the INTERACTION REASON table on the basis of INTERACTION REASON Key to get the INTERACTION REASON name values Left outer join is performed on the INTERACTION Result table on the basis of INTERACTION RESULT Key to get the INTERACTION Result name values	COUNT( CASE WHEN SRC.INTRATN_RSLT_NM= 'Satisfy' AND SRC.INTRATN_RSN_NM = 'Survey' THEN CUST_SRVY_KEY END) AS STFY_CNT Join performed between DWM_INTRATN_RSN and DWD_CUST_SRVY table on INTRATN_RSN_KEY column to fetch value for INTRATN_RSN_NM Join performed between DWM_INTRATN_RSLT and DWD_CUST_SRVY table on INTRATN_RSLT_KEY column to fetch value for INTRATN_RSLT_NM
TOT_SRVY_CNT	DWM_INTRATN_RSN	TOT_SRVY_CNT	Left outer join is performed on the INTERACTION REASON table on the basis of INTERACTION REASON Key to get the INTERACTION REASON name values	COUNT( CASE WHEN SRC.INTRATN_RSN_NM = 'Survey' THEN CUST_SRVY_KEY END) AS TOT_SRVY_CNT Join performed between DWM_INTRATN_RSN and DWD_CUST_SRVY table on INTRATN_RSN_KEY column to fetch value for INTRATN_RSN_NM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'

PKG_DWA_DLY_BKG_FACT Mapping

Table 6-7 shows the mapping to populate target table DWA_DLY_BKG_FACT. For more information, see DAILY BOOKING FACT.

Source Tables

DWD_BKG_FACT

DWD_TKT

DWM_FLT

DWM_CLNDR

Table 6-7 PKG_DWA_DLY_BKG_FACT ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
BKG_SEG_DEPTR_FACT_KEY	DWD_BKG_FACT	SEQ_DWA_DLY_BKG_FACT.NEXTVAL	Direct mapping. It is the sequence key generated to retrieve the data in sequential order
TRAF_CATG_KEY	DWD_BKG_FACT	TRAFIC_CAT_KEY	Direct Mapping. It indicates the foreign key which is the primary key of the other table	NVL (b.traf_catg_key, -1) AS TRAFIC_CAT_KEY
BKG_CLS_KEY	DWD_BKG_FACT	BKGCLS_KEY	Direct Mapping. It indicates the foreign key which is the primary key of the other table	NVL (b.bkgcls_key, -1) AS BKGCLS_KEY
OPRTNG_CARR_KEY	DWD_BKG_FACT	OPR_CARRIER_KEY	Direct Mapping. It indicates the foreign key which is the primary key of the other table	NVL (b.oprtng_carr_key, -1) AS OPR_CARRIER_KEY
SALES_CHNL_KEY	DWD_BKG_FACT	SALES_CHANNEL_ID	Direct Mapping. Indicates the sales channel identifier	NVL (b.sales_chnl_id, -1) AS SALES_CHANNEL_ID
DEPTR_AIP_KEY	DWD_BKG_FACT	DEPTR_AIP_KEY	Direct Mapping. It indicates the foreign key which is the primary key of the other table	NVL (b.deptr_aip_key, -1) AS DEPTR_AIP_KEY
ARVL_AIP_KEY	DWD_BKG_FACT	ARVL_AIP_KEY	Direct Mapping. It indicates the foreign key which is the primary key of the other table	NVL (b.arvl_aip_key, -1) AS ARVL_AIP_KEY
OFF_KEY	DWD_BKG_FACT	OFFICE_KEY	Direct Mapping. It indicates the foreign key which is the primary key of the other table	NVL (B.OFF_KEY, -1) AS OFFICE_KEY
OPRTNG_FLT_KEY	DWD_BKG_FACT	OPR_FLIGHT_KEY	Direct Mapping. It indicates the foreign key which is the primary key of the other table	NVL (b.oprtng_flt_key, -1) AS OPR_FLIGHT_KEY
MKTG_CARR_KEY	DWD_BKG_FACT	MKT_CARRIER_KEY	Direct Mapping. It indicates the foreign key which is the primary key of the other table	NVL (b.mktg_carr_key, -1) AS MKT_CARRIER_KEY
MKTG_FLT_KEY	DWD_BKG_FACT	MKT_FLIGHT_KEY	Direct Mapping. It indicates the foreign key which is the primary key of the other table	NVL (b.mktg_flt_key, -1) AS MKT_FLIGHT_KEY
OPRTNG_SEG_KEY	DWD_BKG_FACT	OPR_SEG_KEY	Direct Mapping. It indicates the foreign key which is the primary key of the other table	NVL (b.oprtng_seg_key,-1) AS OPR_SEG_KEY
MKTG_SEG_KEY	DWD_BKG_FACT	MKT_SEG_KEY	Direct Mapping. It indicates the foreign key which is the primary key of the other table	NVL (b.mktg_seg_key, -1) AS MKT_SEG_KEY
BKG_CITY_KEY	DWD_BKG_FACT	BKG_CITY_KEY	Direct Mapping. It indicates the foreign key which is the primary key of the other table	CASE WHEN B.BKG_CITY_KEY IS NULL THEN -1 ELSE B.BKG_CITY_KEY END AS BKG_CITY_KEY
CORP_CUST_KEY	DWD_BKG_FACT	corp_cust_key	Direct Mapping. It indicates the foreign key which is the primary key of the other table
IATCI_TRGT_CO_ID	V_IATCI_TRGT_CO_ID_DFLT	Direct Mapping.
CAMPN_ID	DWD_BKG_FACT	BKG_CAMPN_ID	Direct Mapping.	NVL(B.BKG_CAMPN_ID, -1) AS BKG_CAMPN_ID
ROUTE_ID	pv_ROUTE_ID_DFLT	Direct Mapping.
BKD	DWD_BKG_FACT	BOOKED	Direct Mapping. "This indicates the total booked count for all the bookings for the combination of dimensions for the current date for all future departures. This will be derived from the BKG_FACT entity using the column BKG_KEY"	COUNT ( CASE WHEN B.BKG_CREN_TM_ID != 0 THEN 1 ELSE NULL END) AS BOOKED
CNCLD_FRM_CONFMD	DWD_BKG_FACT	CNCLD_FRM_CONFMD	Direct Mapping. "This indicates the total count for all the bookings for the combination of dimensions for a particular snapshot date for all future departures starting the day after the snapshot date where the bookings were canceled from a confirmed status This will be derived from the BKG_FACT entity using the column BKG_STS_CHNG_IND"	COUNT ( CASE WHEN TO_DATE(TO_CHAR(B.CNCL_DTTM,'DD-MON-YYYY')) > TO_DATE(TO_CHAR(B.CONF_DTTM,'DD-MON-YYYY')) THEN 1 ELSE NULL END) AS CNCLD_FRM_CONFMD
WAITLISED	DWD_BKG_FACT	Waitlist_Count	Direct Mapping. "This indicates the total waitlist count for all the bookings for the combination of dimensions for a particular snapshot date for all future departures starting the day after the snapshot date. This will be derived from the BKG_FACT entity using the column WAITLIST_DT_TM"
TKTD	DWD_BKG_FACT	TICKETED	Direct Mapping. "This indicates the total ticketed count for all the bookings for the combination of dimensions for the current date for all future departures. This will be derived from the BKG_FACT entity using the column PAX_TKT_KEY where this column is not null."	SUM( CASE WHEN B.PAX_TKT_KEY IS NOT NULL AND (B.INF_TKT_KEY != -1 OR B.INF_TKT_KEY IS NOT NULL) THEN 2 WHEN B.PAX_TKT_KEY IS NOT NULL AND (B.INF_TKT_KEY = -1 OR B.INF_TKT_KEY IS NULL) THEN 1 ELSE 0 END) AS TICKETED
NET_BKD	DWD_BKG_FACT	NET_BKD	Direct Mapping. "This indicates the net booked data for the current date which is calculated using the following formula below Booked - Cancelled, net total of booked segments (regardless of segment status)"	(SRC.BOOKED - SRC.CNCLD_FRM_CONFMD) AS NET_BKD
NET_CONFMD	DWD_BKG_FACT	NET_CONFMD	Direct Mapping. "This indicates the net confirmed data for the current date which is calculated using the following formula below Confirmed ¨C Cancelled from Confirmed"	(SRC.Confirmation_Count - SRC.CNCLD_FRM_CONFMD) AS NET_CONFMD
MATRLIZATN_RATE	DWD_BKG_FACT	MATRLIZATN_RATE	Direct Mapping. "This indicates the materialization rate data for the current date which is calculated using the following formula below Confirmed-Cancelled from HK Confirmed"	CASE WHEN SRC.Confirmation_Count = 0 THEN 0 ELSE (SRC.Confirmation_Count - SRC.CNCLD_FRM_CONFMD) / SRC.Confirmation_Count * 100 END AS MATRLIZATN_RATE
CONFMD_CNT	DWD_BKG_FACT	Confirmation_Count	Direct Mapping. "This indicates the total waitlist count for all the bookings for the combination of dimensions for the current date for all future departures. This will be derived from the BKG_FACT entity using the column BKG_CONFIRM_DT_TM"
CNCLD_CNT	DWD_BKG_FACT	CANCELLED_COUNT	Direct Mapping. "This indicates the total waitlist count for all the bookings for the combination of dimensions for the current date for all future departures. This will be derived from the BKG_FACT entity using the column BKG_CANCEL_DT_TM"
GRP_BKD_QTY	DWD_BKG_FACT	GRP_BKD_QTY	Direct Mapping. This indicates the Group booked Quantity.
INDV_BKD_QTY	DWD_BKG_FACT	INDV_BKD_QTY	Direct mapping. This indicates the Individual booked Quantity.	COUNT ( CASE WHEN b.BKG_GRP_IND = 'Y' THEN 1 END) AS GRP_BKD_QTY, COUNT ( CASE WHEN b.BKG_GRP_IND = 'N' THEN 1 END) AS INDV_BKD_QTY
GRP_PAX_CNT	DWD_BKG_FACT	GRP_PAX_CNT	Direct mapping. This indicates the Group Passenger Count.	SUM( CASE WHEN b.BKG_GRP_IND = 'Y' AND (B.INF_PAX_KEY != -1 OR B.INF_PAX_KEY IS NOT NULL) THEN 2 WHEN b.BKG_GRP_IND = 'Y' AND (B.INF_PAX_KEY = -1 OR B.INF_PAX_KEY IS NULL) THEN 1 ELSE 0 END) AS GRP_PAX_CNT
INDV_PAX_CNT	DWD_BKG_FACT	INDV_PAX_CNT	Direct mapping. This measure indicates the Individual Passenger Count.	SUM( CASE WHEN b.BKG_GRP_IND = 'N' AND (B.INF_PAX_KEY != -1 OR B.INF_PAX_KEY IS NOT NULL) THEN 2 WHEN b.BKG_GRP_IND = 'N' AND (B.INF_PAX_KEY = -1 OR B.INF_PAX_KEY IS NULL) THEN 1 ELSE 0 END) AS INDV_PAX_CNT
BKD_LY	DWD_BKG_FACT	BKD_LY	Direct Mapping.	COUNT ( CASE WHEN B.BKG_CREN_TM_ID != 0 AND to_date(TO_CHAR(B.bkg_cren_tmstmp,'dd-mon-yyyy'))= add_months (to_date(TO_CHAR(B.bkg_cren_tmstmp,'dd-mon-yyyy')), -12) THEN 1 ELSE NULL END) AS BKD_LY
CNCLD_CNT_LY	DWD_BKG_FACT	CNCLD_CNT_LY	Direct mapping.	COUNT ( CASE WHEN B.BKG_CNCL_TM_ID <> 0 THEN B.BKG_CNCL_TM_ID END ) AS CANCELLED_COUNT, COUNT ( CASE WHEN B.BKG_CNCL_TM_ID <> 0 AND to_date(TO_CHAR(B.bkg_cren_tmstmp,'dd-mon-yyyy'))= add_months (to_date(TO_CHAR(B.bkg_cren_tmstmp,'dd-mon-yyyy')), -12) THEN B.BKG_CNCL_TM_ID END ) AS CNCLD_CNT_LY
CNCLD_FRM_CONFMD_LY	DWD_BKG_FACT	CNCLD_FRM_CONFMD_LY	Direct Mapping.	CASE WHEN SRC.BKG_DATE= add_months (SRC.BKG_DATE, -12) THEN SRC.CNCLD_FRM_CONFMD END AS CNCLD_FRM_CONFMD_LY
CONFMD_CNT_LY	DWD_BKG_FACT	CONFMD_CNT_LY	Direct mapping.	COUNT ( CASE WHEN B.BKG_CONF_TM_ID <> 0 THEN B.BKG_CONF_TM_ID END ) AS Confirmation_Count, COUNT ( CASE WHEN B.BKG_CONF_TM_ID <> 0 AND to_date(TO_CHAR(B.bkg_cren_tmstmp,'dd-mon-yyyy'))= add_months (to_date(TO_CHAR(B.bkg_cren_tmstmp,'dd-mon-yyyy')), -12) THEN B.BKG_CONF_TM_ID END ) AS CONFMD_CNT_LY
NET_BKD_LY	DWD_BKG_FACT	NET_BKD_LY	Direct mapping.	CASE WHEN SRC.BKG_DATE= add_months (SRC.BKG_DATE, -12) THEN (SRC.BOOKED - SRC.CNCLD_FRM_CONFMD) ELSE 0 END AS NET_BKD_LY
NET_CONFMD_LY	DWD_BKG_FACT	NET_CONFMD_LY	Direct mapping.	CASE WHEN SRC.BKG_DATE= add_months (SRC.BKG_DATE, -12) THEN SRC.CNCLD_FRM_CONFMD END AS CNCLD_FRM_CONFMD_LY, CASE WHEN SRC.Confirmation_Count = 0 THEN 0 ELSE (SRC.Confirmation_Count - SRC.CNCLD_FRM_CONFMD) / SRC.Confirmation_Count * 100 END AS MATRLIZATN_RATE, (SRC.Confirmation_Count - SRC.CNCLD_FRM_CONFMD) AS NET_CONFMD, CASE WHEN SRC.BKG_DATE = add_months (SRC.BKG_DATE, -12) THEN (SRC.Confirmation_Count - SRC.CNCLD_FRM_CONFMD) END AS NET_CONFMD_LY
TKTD_LY	DWD_BKG_FACT	TKTD_LY	Direct mapping.	SUM ( CASE WHEN B.PAX_TKT_KEY IS NOT NULL AND (B.INF_TKT_KEY != -1 OR B.INF_TKT_KEY IS NOT NULL) AND to_date(TO_CHAR(B.bkg_cren_tmstmp,'dd-mon-yyyy'))= add_months (to_date(TO_CHAR(B.bkg_cren_tmstmp,'dd-mon-yyyy')), -12) THEN 2 WHEN B.PAX_TKT_KEY IS NOT NULL AND (B.INF_TKT_KEY = -1 OR B.INF_TKT_KEY IS NULL) AND to_date(TO_CHAR(B.bkg_cren_tmstmp,'dd-mon-yyyy'))= add_months (to_date(TO_CHAR(B.bkg_cren_tmstmp,'dd-mon-yyyy')), -12) THEN 1 ELSE 0 END ) AS TKTD_LY
WAITLISED_LY	DWD_BKG_FACT	WAITLISED_LY	Direct mapping.	COUNT ( CASE WHEN B.WTLST_IND = 'Y' THEN B.WTLST_IND END ) AS Waitlist_Count, COUNT ( CASE WHEN B.WTLST_IND = 'Y' AND to_date(TO_CHAR(B.bkg_cren_tmstmp,'dd-mon-yyyy'))= add_months (to_date(TO_CHAR(B.bkg_cren_tmstmp,'dd-mon-yyyy')), -12) THEN B.WTLST_IND END ) AS WAITLISED_LY
OTR_CHARGES	DWD_TKT	OTR_CHARGES	Left outer join is performed on the Ticket table on the basis of Ticket key to get the Other Charges	Join performed between DWD_TKT and DWD_BKG_FACT table on TKT_KEY column to fetch value for OTR_CHARGES
TAX_AMT	DWM_FLT	TAX	Left outer join is performed on the Filter table on the basis of Flight key to get the Tax values	CASE WHEN DWM_FLT.INTNL_DOM_FLG = 'I' THEN LKP.TOT_AMT * 0.18 WHEN DWM_FLT.INTNL_DOM_FLG = 'D' THEN LKP.TOT_AMT * 0.25 ELSE 0 END AS TAX Join performed between DWM_FLT and DWD_BKG_FACT table on flt_key column to fetch value for TAX
TKT_AMT	DWD_TKT	TKT_AMT	Left outer join is performed on the Ticket table on the basis of Ticket key to get the Ticket amount	Join performed between DWD_TKT and DWD_BKG_FACT table on TKT_KEY column to fetch value for TKT_AMT
PAX_CNT	DWD_BKG_FACT	PAX_COUNT	Direct mapping. This indicates the passenger count at day level. ODT requested to add this measure	SUM( CASE WHEN B.INF_PAX_KEY != -1 OR B.INF_PAX_KEY IS NOT NULL THEN 2 ELSE 1 END) AS PAX_COUNT
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
BKG_DT_KEY	dwm_clndr	CLNDR_KEY	Left outer join is performed on the Calendar table on the basis of Calendar date to get the Calendar key.	Join performed between dwm_clndr and DWD_BKG_FACT table on clndr_dt column to fetch value for CLNDR_KEY to_date(TO_CHAR (b.bkg_cren_tmstmp,'dd-mon-yyyy')) AS BKG_DATE
SEG_DEPTR_DT_KEY	pv_SEG_DEPTR_DT_KEY_DFLT
FLN_RVN	DWD_BKG_FACT	FLN_RVN	Direct mapping. This indicates Revenue generated from passengers who checked in and received the boarding pass	SUM(B.FLN_RVN) AS FLN_RVN
FLN_PAX_CNT	DWD_BKG_FACT	FLN_PAX_CNT	Direct mapping. Indicates the number of passengers who checked in and received the boarding pass	SUM(B.FLN_PAX_CNT) AS FLN_PAX_CNT
NON_RVN_FLN_PAX_CNT	DWD_BKG_FACT	NON_RVN_FLN_PAX_CNT	Direct mapping. Indicates the number of passenger who checked in and received the boarding pass and from whom airline does not generate any revenue	SUM(B.NON_RVN_FLN_PAX_CNT) AS NON_RVN_FLN_PAX_CNT
ONFLT_ORGN_TO_DEST_FLT_RVN	DWD_BKG_FACT	ONFLT_ORGN_TO_DEST_FLT_RVN	Direct mapping. Indicates the Onflight Origin to Destination means that the airline services all flight segments starting from Original to Destination.	SUM(B.ONFLT_ORGN_TO_DEST_FLT_RVN) AS ONFLT_ORGN_TO_DEST_FLT_RVN
FLN_RVN_ORGN_TO_DEST_OFRD	DWD_BKG_FACT	FLN_RVN_ORGN_TO_DEST_OFRD	Direct mapping. Indicates the Offered Origin to Destination means that the airline only provide part of Origin and Destination flighting service	SUM(B.FLN_RVN_ORGN_TO_DEST_OFRD) AS FLN_RVN_ORGN_TO_DEST_OFRD
ONFLT_ORGN_TO_DEST_FLN_PAX_CNT	DWD_BKG_FACT	ONFLT_ORGN_TO_DEST_FLN_PAX_CNT	Direct mapping.	SUM(B.ONFLT_ORGN_TO_DEST_FLN_PAX_CNT) AS ONFLT_ORGN_TO_DEST_FLN_PAX_CNT
OFRD_ORGN_TO_DEST_FLN_PAX_CNT	DWD_BKG_FACT	OFRD_ORGN_TO_DEST_FLN_PAX_CNT	Direct mapping.	SUM(B.OFRD_ORGN_TO_DEST_FLN_PAX_CNT) AS OFRD_ORGN_TO_DEST_FLN_PAX_CNT
CPN_CNT	DWD_BKG_FACT	CPN_CNT	Direct mapping.	SUM(B.CPN_CNT) AS CPN_CNT
ONBRD_RVN	DWD_BKG_FACT	ONBRD_RVN	Direct mapping.	SUM(B.ONBRD_RVN) AS ONBRD_RVN
EXCESS_BAG_RVN	DWD_BKG_FACT	EXCESS_BAG_RVN	Direct mapping.	SUM(B.EXCESS_BAG_RVN)AS EXCESS_BAG_RVN
FEES_RVN	DWD_BKG_FACT	FEES_RVN	Direct mapping.	SUM(B.FEES_RVN) AS FEES_RVN
CHARTER_RVN	DWD_BKG_FACT	CHARTER_RVN	Direct mapping.	SUM(B.CHARTER_RVN) AS CHARTER_RVN
BELLY_CARGO_RVN	DWD_BKG_FACT	BELLY_CARGO_RVN	Direct mapping.	SUM(B.BELLY_CARGO_RVN) AS BELLY_CARGO_RVN
CDSH_RVN	DWD_BKG_FACT	CDSH_RVN	Direct mapping.	SUM(B.CDSH_RVN) AS CDSH_RVN
OTR_RVN	DWD_BKG_FACT	OTR_RVN	Direct mapping.	SUM(B.OTR_RVN) AS OTR_RVN

PKG_DWA_DLY_CALL_CNTR_PRFMNC Mapping

Table 6-8 shows the mapping to populate target table DWA_DLY_CALL_CNTR_PRFMNC. For more information, see DAILY CALL CENTER PERFORMANCE.

Source Tables

DWD_CALL_CNTR_PRFMNC

DWM_CLNDR

Table 6-8 PKG_DWA_DLY_CC_PRFM ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
DLY_CALL_CNTR_PRFMNC_KEY	SEQUENCE GENERATOR
OFF_KEY	DWD_CALL_CNTR_PRFMNC	CALL_CNTR_KEY as OFF_KEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
CALL_CNT	DWD_CALL_CNTR_PRFMNC	PRTY_INTRATN_CALL_ID	It indicates the count of number of call received on a daily basis.	Count(PRTY_INTRATN_CALL_ID) As CALL_CNT
ACCSSBL_CNT	DWD_CALL_CNTR_PRFMNC	DLY_CALL_CNTR_PRFMNC_KEY	It indicates the count of accessible call	Count(DLY_CALL_CNTR_PRFMNC_KEY) where TALK_DURN = 0 and HLDD_BY_IVR_IND= 'Y'
STSFY_CNT	DWD_CALL_CNTR_PRFMNC	DLY_CALL_CNTR_PRFMNC_KEY	It indicates the count of satisfy call	Count(DLY_CALL_CNTR_PRFMNC_KEY) where CUST_STSFYN_IND= 'Y'
MINT_OF_CALL_DURN	DWD_CALL_CNTR_PRFMNC	INTRATN_DURN	It indicates the count of total minute of call duration	SUM(INTRATN_DURN) as MIN_AMT
AGNT_CNT	DWD_CALL_CNTR_PRFMNC	CALL_CNTR_AGNT_KEY	It indicates the count of Agents.	Count(CALL_CNTR_AGNT_KEY) as AGNT_CNT
CLNDR_KEY	DWM_CLNDR	CLNDR_KEY	Left outer join is performed on the Calendar table on the basis of calendar key with interaction event data time key to get the calendar key	Join performed between DWM_CLNDR and DWD_CALL_CNTR_PRFMNC table on column INTRATN_EVNT_DTTM_KEY to fetch value for CLNDR_KEY
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'

PKG_DWA_DLY_FLT_DETLS Mapping

Table 6-9 shows the mapping to populate target table DWA_DLY_FLT_DETLS. For more information, see DAILY FLIGHT DETAILS.

Source Tables

DWD_FLT_DETLS_FACT

DWM_CLNDR

Table 6-9 PKG_DWA_DLY_FLT_DETLS ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
DWA_DLY_FLT_DETLS_KEY	SEQUENCE GENERATOR
SEG_KEY	DWD_FLT_DETLS_FACT	SEG_KEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
FLT_KEY	DWD_FLT_DETLS_FACT	FLT_KEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
FLT_DT_KEY	DWM_CLNDR	CLNDR_KEY as FLT_DT_KEY	Left outer join is performed on the Calendar table on the basis of calendar date with flight date to get the calendar key	Join performed between DWM_CLNDR and DWD_FLT_DETLS_FACT table on column FLT_DT to fetch value for CLNDR_KEY
ACV_TOT_CPCTY	DWD_FLT_DETLS_FACT	ACV_TOT_CPCTY	SUM of AIRCRAFTVERSION TOTAL CAPACITY	SUM(ACV_TOT_CPCTY) AS ACV_TOT_CPCTY
SALEBLE_TOT_CPCTY	DWD_FLT_DETLS_FACT	SALEBLE_TOT_CPCTY	SUM of SALEBLE TOTAL CAPACITY	SUM(SALEBLE_TOT_CPCTY) AS SALEBLE_TOT_CPCTY
NAUTICAL_MLS	DWD_FLT_DETLS_FACT	NAUTICAL_MLS	SUM of NAUTICAL MILES	SUM(NAUTICAL_MLS) AS NAUTICAL_MLS
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'

PKG_DWA_DLY_LYLTY_ACCT_BKG Mapping

Table 6-10 shows the mapping to populate target DWA_DLY_LYLTY_ACCT_BKG. For more information, see DAILY LOYALTY ACCOUNT BOOKING.

Source Tables

DWD_BKG_FACT

DWD_TKT

DWM_CLNDR

DWM_FRQTFLR

DWD_LYLTY_ACCT_LVL_HIST

DWD_LYLTY_PRG

DWD_LYLTY_ACCT_BAL_HIST

Table 6-10 PKG_DWA_DLY_LYLTY_ACCT_BKG ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
DLY_LYLTY_ACCT_BKG_KEY	DWD_BKG_FACT	SEQ_DWA_DLY_LYLTY_ACCT_BKG.NEXTVAL	Sequence Generator. Unique number helps to keep the data integrity between the operational and derived layer.
LYLTY_LVL_KEY	DWD_BKG_FACT DWD_LYLTY_ACCT_LVL_HIST	LYLTY_LVL_KEY	Left outer join performed on the LOYALTY ACCOUNT LEVEL HISTORY table on the basis of FREQUENTFLIERCARDKEY to get LOYALTY LEVEL KEY	Join performed between DWD_LYLTY_ACCT_LVL_HIST table and DWD_BKG_FACT on FRQTFLIER_CARD_KEY column to fetch value for LYLTY_LVL_KEY
OFF_KEY	DWD_BKG_FACT	OFFICE_KEY	Direct mapping Data flows from operational to derived layer	NVL (OFF_KEY, pv_OFFICE_KEY_NVL) AS OFFICE_KEY
LYLTY_ACCT_CNT	DWD_BKG_FACT	LYLTY_ACCT_CNT	Count of distinct Frequent flyer card key	COUNT (DISTINCT CASE WHEN (FF_CARD_KEY != pv_FF_CARD_KEY_nvl OR FF_CARD_KEY IS NOT NULL) THEN SRC1.FF_CARD_KEY ELSE NULL END ) AS LYLTY_ACCT_CNT
ACTV_CNT	ACTV_CNT	Count of Frequent flyer card key when Calendar key is equal to Balance date key and balance date between Booking Date-six months and Booking date	count(FF_CARD_KEY) when CLNDR_KEY=BAL_DT_KEY and BAL_DT between BKG_DATE -6 and BKG_DATE
PAX_CNT	PAX_COUNT	Sum of Infant pax key	SUM(CASE WHEN INF_PAX_KEY != pv_INF_PAX_KEY OR INF_PAX_KEY IS NOT NULL THEN 2 ELSE 1 END)
BKD_CNT	BOOKED	Count of Booking creation time identifier	COUNT ( CASE WHEN BKG_CREN_TM_ID != 0 THEN BKG_CREN_TM_ID ELSE NULL)
CONFMD_CNT	CONFIRMATION_COUNT	Count of Booking creation time identifier When not null	COUNT (CASE WHEN BKG_CONF_TM_ID IS NOT NULL THEN BKG_CONF_TM_ID END ) AS CONFIRMATION_COUNT
CNCLD_CNT	CANCELLED_COUNT	Count of Booking cancellation time id when not null	COUNT ( CASE WHEN BKG_CNCL_TM_ID IS NOT NULL THEN BKG_CNCL_TM_ID END) AS CANCELLED_COUNT
FLN_PAX_CNT	DWD_BKG_FACT	FLN_PAX_CNT	Direct mapping. This indicates the flown passenger count
FLN_RVN_BY_ACTV	FLN_REV_BY_ACTV	Sum of Frequent Flyer card key where Calendar key is equal to balance date key and frequent flyer card key is not null and Balance date between Balance date minus 6 months and Balance date	sum(FF_CARD_KEY from LOYALTY ACCOUNT BALANCE HISTORY, CALENDAR, BOOKING FACT Table WHERE CLNDR_KEY = BAL_DT_KEY AND FRQTFLIER_CARD_KEY IS NOT NULL ) where FF_CARD_KEY AND BAL_DT BETWEEN BKG_DATE-6 AND BKG_DATE
FLN_RVN	DWD_BKG_FACT	FLN_REV	Direct mapping. This indicates the flown revenue
FLT_CNT	DWD_BKG_FACT	FLT_CNT	Count of Operating Flight key when not equal to -1 or when Operating Flight key is not null	COUNT (DISTINCT CASE WHEN SRC1.OPRTNG_FLT_KEY != -1 OR SRC1.OPRTNG_FLT_KEY IS NOT NULL THEN SRC1.OPRTNG_FLT_KEY ELSE NULL END ) AS FLT_CNT
TKT_AMT	DWD_BKG_FACT DWD_TKT	TKT_AMT	Left outer join performed on TICKET table on the basis of TICKET KEY to get TICKET AMOUNT	Join performed between TICKET table and BOOKING FACT table on TKT_KEY column to fetch value for TKT_AMT
CLNDR_KEY	DWM_CLNDR DWD_BKG_FACT	BKG_CLNDR_KEY	Left outer join performed on CALENDAR Table on the basis of CALENDAR DATE to get CALENDAR KEY	Join performed between CALENDAR table and BOOKING FACT table on CLNDR_DT column to fetch value for CLNDR_KEY
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
LYLTY_PRG_KEY	DWD_BKG_FACT DWD_LYLTY_PRG	LYLTY_PRG_KEY	Left outer join performed on LOYALTY PROGRAM Table on the basis of LOYALTY PROGRAM IDENTIFIER to get LOYALTY PROGRAM KEY	Join performed between LOYALTY PROGRAM table and BOOKING FACT table on LYLTY_PRG_ID column to fetch value for LYLTY_PRG_KEY

PKG_DWA_DLY_LYLTY_ACCT Mapping

Table 6-11 shows the mapping to populate target table DWA_DLY_LYLTY_ACCT. For more information, see DAILY LOYALTY ACCOUNT.

Source Tables

DWM_FRQTFLR

DWD_LYLTY_ACCT_LVL_HIST

DWD_LYLTY_PRG

DWD_LYLTY_ACCT_BAL_HIST

DWM_CLNDR

Table 6-11 PKG_DWA_DLY_LYLTY_ACCT ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
DLY_LYLTY_ACCT_KEY	Sequence Generator	SEQ_DWA_DLY_LYLTY_ACCT	The unique key is generated by the sequence generator.
LYLTY_LVL_KEY	DWD_LYLTY_ACCT_BAL_HIST, DWD_LYLTY_ACCT_LVL_HIST, DWM_CLNDR	DWD_LYLTY_ACCT_LVL_HIST. LYLTY_LVL_KEY	Left outer join is performed on the Loyalty Account Level History and Calendar table on the basis of Frequent Flyer Card Key and Calendar Key between Valid Date Key and Expiry Date Key to get the Loyalty Level Key.	Join performed between DWD_LYLTY_ACCT_BAL_HIST, DWD_LYLTY_ACCT_LVL_HIST and DWM_CLNDR tables on DWD_LYLTY_ACCT_BAL_HIST.BAL_DT_KEY = DWM_CLNDR.CLNDR_KEY AND DWD_LYLTY_ACCT_LVL_HIST.FRQTFLIER_CARD_KEY = DWD_LYLTY_ACCT_BAL_HIST.FRQTFLIER_CARD_KEY AND DWM_CLNDR.CLNDR_KEY BETWEEN DWD_LYLTY_ACCT_LVL_HIST.VLD_DT_KEY AND DWD_LYLTY_ACCT_LVL_HIST.EXPRY_DT_KEY columns to fetch value for LYLTY_LVL_KEY
GEO_CITY_KEY	DWD_LYLTY_ACCT_BAL_HIST	GEO_CITY_KEY	Direct mapping. It indicates the City Key of frequent flyers. Data flows from source to operational layer and then to the derived layer.	NVL(DWD_LYLTY_ACCT_BAL_HIST.GEO_CITY_KEY,-1)
CLNDR_KEY	DWD_LYLTY_ACCT_BAL_HIST, DWM_CLNDR	DWM_CLNDR. CLNDR_KEY	Left outer join is performed on the Calendar table to get the Calendar Key.	Join performed between DWD_LYLTY_ACCT_BAL_HIST and DWM_CLNDR tables on DWD_LYLTY_ACCT_BAL_HIST. BAL_DT_KEY = DWM_CLNDR. CLNDR_KEY columns to fetch value for CLNDR_KEY
LYLTY_ACCT_CNT	DWM_FRQTFLR	FRQTFLIER_CARD_KEY	The count of Frequent Flyer Card Key on basis of the Calendar Key between Account Open Date and Account Close Date and Calendar Key is between Valid Date Key and Expiry Date Key of Account Level History table.	Count(FRQTFLIER_CARD_KEY) from DWM_FRQTFLR where CLNDR_KEY between ACCT_OPEN_DT and ACCT_CLOSE_DT and join with DWD_LYLTY_ACCT_LVL_HIST where CLNDR_KEY between VLD_DT_KEY and EXPRY_DT_KEY
UPGRD_CNT	DWD_LYLTY_ACCT_LVL_HIST	FRQTFLIER_CARD_KEY	The count of Frequent Flyer on the basis of Calendar Key between Account Open Date and Account Close date and Calendar Key is equal to Valid date and before Valid Date the Loyalty Level Key is lower than the current Loyalty Level Key.	Count(FRQTFLIER_CARD_KEY) where CLNDR_KEY between ACCT_OPEN_DT and ACCT_CLOSE_DT and join with DWD_LYLTY_ACCT_LVL_HIST where CLNDR_KEY = VLD_DT_KEY and before VLD_DT_KEY the LYLTY_LVL_KEY is lower than current LYLTY_LVL_KEY.
DGRD_CNT	DWD_LYLTY_ACCT_LVL_HIST	FRQTFLIER_CARD_KEY	The count of Frequent Flyer on the basis of Calendar Key between Account Open Date and Account Close date and Calendar Key is equal to Valid date and before Valid Date the Loyalty Level Key is greater than the current Loyalty Level Key.	Count(FRQTFLIER_CARD_KEY) where CLNDR_KEY between ACCT_OPEN_DT and ACCT_CLOSE_DT and join with DWD_LYLTY_ACCT_LVL_HIST where CLNDR_KEY = VLD_DT_KEY and before VLD_DT_KEY the LYLTY_LVL_KEY is greater than current LYLTY_LVL_KEY.
ACTV_CNT	DWM_FRQTFLR	FRQTFLIER_CARD_KEY	The count of Frequent Flyer Card Key on the basis of Calendar Key between Account Open Date and Account Close date and Calendar Key is between Valid Date Key and Expiry Date Key and Balance Date Key between Calendar Date and six months before the Calendar Date.	Count(Distinct(FRQTFLIER_CARD_KEY)) where CLNDR_KEY between ACCT_OPEN_DT and ACCT_CLOSE_DT and join with DWD_LYLTY_ACCT_LVL_HIST where CLNDR_KEY between VLD_DT_KEY and EXPRY_DT_KEY and join with DWD_LYLTY_ACCT_BAL_HIST where BAL_DT_KEY between CLNDR_DT and (CLNDR_DT ¨C 6 month)
TOT_MLS_AMT	DWD_LYLTY_ACCT_BAL_HIST	CURR_MLS_AMT	The sum of the current miles amount earned on the Balance date and on basis of Calendar Key between Valid Date Key and Expiry Date Key.	Sum(CURR_MILES_AMT) where CLNDR_KEY = BAL_DT join with DWD_LYLTY_ACCT_LVL_HIST where CLNDR_KEY between VLD_DT_KEY and EXPRY_DT_KEY
MLS_ERND_AMT	DWD_LYLTY_ACCT_BAL_HIST	CURR_MLS_AMT, LAST_BAL_AMT	The sum of miles earned on the Balance Date and on basis of Calendar Key between Valid Date Key and Expiry Date Key.	Sum(if (CURR_MILES_AMT- LAST_BAL_AMT) >= 0 then (CURR_MILES_AMT- LAST_BAL_AMT) else 0) where CLNDR_KEY = BAL_DT_KEY join with DWD_LYLTY_ACCT_LVL_HIST where CLNDR_KEY between VLD_DT_KEY and EXPRY_DT_KEY
MLS_RDMD_AMT	DWD_LYLTY_ACCT_BAL_HIST	RDM_MLS_AMT	The sum of Redeem Miles Amount on the Balance Date and on basis of Calendar Key between Valid Date Key and Expiry Date Key.	Sum(REDEEM_MILES_AMT) where CLNDR_KEY = BAL_DT_KEY join with DWD_LYLTY_ACCT_LVL_HIST where CLNDR_KEY between VLD_DT_KEY and EXPRY_DT_KEY
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
LYLTY_PRG_KEY	DWD_LYLTY_ACCT_BAL_HIST	LYLTY_PRG_KEY	Direct mapping. The source system generated unique Loyalty Program Key.	NVL(DWD_LYLTY_ACCT_BAL_HIST.LYLTY_PRG_KEY,-1)

PKG_DWD_BKG_FACT Mapping

Table 6-12 shows the list of source tables for PKG_DWD_BKG_FACT. Table 6-13 shows the mapping to populate target table DWD_BKG_FACT. For more information, see BOOKING FACT.

Table 6-12 PKG_DWD_ BKG_FACT ETL Mapping Source Tables

Source Table Name
DWB_BKG_H
DWD_FLT_DETLS_FACT
DWD_PNR
DWD_TKT
DWM_ACCT
DWM_AIP
DWM_BKG_CLS_TYP
DWM_BKG_OFF
DWM_BKG_PAX
DWM_BKG_TST
DWM_CARR
DWM_CLNDR
DWM_CORP_CUST
DWM_FLT
DWM_FRQTFLR
DWM_GEOGRY
DWM_SALES_CHNL
DWM_SEG
DWM_TM
DWM_TRAF_CATG
DWR_INFLT_MEAL_H

Table 6-13 PKG_DWD_ BKG_FACT ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
BKG_KEY	DWB_BKG_H	DWB_BKG_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
PAX_TKT_KEY	DWB_BKG_H, DWD_TKT	DWD_TKT.TKT_KEY	Left outer join is performed on the Ticket table on the basis of source system generated Ticket Identifier to get unique Ticket Key.	Join performed between DWB_BKG_H and DWD_TKT table on TKT_ID column to fetch value for TKT_KEY
OPRTNG_CARR_KEY	DWB_BKG_H, DWM_CARR	DWM_CARR. CARR_KEY	Left outer join is performed on the Carrier table on the basis of source system generated Carrier Identifier to get the unique Carrier Key.	Join performed between DWB_BKG_H and DWM_CARR table on CARR_ID column to fetch value for CARR_KEY
MKTG_CARR_KEY	DWM_CARR	DWM_CARR. CARR_KEY	Left outer join is performed on the Carrier table on the basis of source system generated Marketing Carrier code to get the unique Carrier Key.	Join performed between DWB_BKG_H and DWM_CARR table on CARR_CD column to fetch value for CARR_KEY
PAX_KEY	DWB_BKG_H, DWM_BKG_PAX	DWM_BKG_PAX.PAX_KEY	Left outer join is performed on the Booking Passenger table on the basis of source system generated Passenger Identifier to get the unique Passenger Key.	Join performed between DWB_BKG_H and DWM_BKG_PAX table on PAX_ID column to fetch value for PAX_KEY
TST_PAX_KEY	DWB_BKG_H, DWM_BKG_TST	DWM_BKG_TST.TST_KEY	Left outer join is performed on the Booking TST table on the basis of source system generated TST Identifier to get the unique TST Key.	Join performed between DWB_BKG_H and DWM_BKG_TST table on TST_ID column to fetch value for TST_KEY
ACCT_KEY	DWB_BKG_H, DWM_ACCT	DWM_ACCT. ACCT_KEY	Left outer join is performed on the Account table on the basis of source system generated Account Identifier to get the unique Account Key.	Join performed between DWB_BKG_H and DWM_ACCT table on ACCT_ID column to fetch value for ACCT_KEY
FRQTFLIER_CARD_KEY	DWB_BKG_H, DWM_FRQTFLR	DWM_FRQTFLR.FRQTFLIER_CARD_KEY	Left outer join is performed on the Frequent flyer table on the basis of unique Frequent flyer number to get the unique Frequent Flyer Card Key.	Join performed between DWB_BKG_H and DWM_FRQTFLR table on FRQTFLIER_NBR column to fetch value for FRQTFLIER_CARD_KEY
SALES_CHNL_ID	DWB_BKG_H, DWM_BKG_OFF, DWM_SALES_CHNL	DWM_SALES_CHNL. SALES_CHNL_KEY	Left outer join is performed on the Booking Office and Sales Channel table on the basis of sales channel type to get the unique Sales Channel Key. And is later joined with booking table on the basis of office identifier.	Join performed between DWB_BKG_Hand DWM_BKG_OFF and DWM_FRQTFLR table on OFF_ID column to fetch value for SALES_CHNL_KEY
SEG_PAIR_KEY	DWB_BKG_H, DWM_SEG_PAIR	DWM_SEG_PAIR. SEG_PAIR_KEY	Left outer join is performed on the Segment Pair table on the basis of Segment Pair Identifier to get the unique Segment Pair Key.	Join performed between DWB_BKG_Hand DWM_SEG_PAIR table on SEG_PAIR_ID column to fetch value for SEG_PAIR_KEY
BKGCLS_KEY	DWB_BKG_H, DWM_BKG_CLS_TYP	DWM_BKG_CLS_TYP. BKG_CLS_KEY	Left outer join is performed on the Booking Class Type table on the basis of source system generated Booking Class Identifier to get the unique Booking Class Key.	Join performed between DWB_BKG_H and DWM_BKG_CLS_TYP table on BKG_CLS_ID column to fetch value for BKG_CLS_KEY
INF_TST_KEY	DWB_BKG_H, DWM_BKG_TST	DWM_BKG_TST.TST_KEY	Left outer join is performed on the Booking TST table on the basis of source system generated Infant TST Identifier to get the unique TST Key.	Join performed between DWB_BKG_H and DWM_BKG_TST table on TST_ID column to fetch value for TST_KEY
OPRTNG_SEG_KEY	DWB_BKG_H, DWM_SEG	DWM_SEG. SEG_KEY	Left outer join is performed on the Segment table on the basis of source system generated Segment Identifier to get the unique Segment Key.	Join performed between DWB_BKG_H and DWM_SEG. table on SEG_ID column to fetch value for SEG_KEY
MKTG_SEG_KEY	DWB_BKG_H, DWM_SEG	DWM_SEG.SEG_KEY	Left outer join is performed on the Segment table on the basis of source system generated Marketing Segment Identifier to get the unique Segment Key.	Join performed between DWB_BKG_H and DWM_SEG. table on SEG_ID column to fetch value for SEG_KEY
INF_TKT_KEY	DWB_BKG_H, DWD_TKT	DWD_TKT.TKT_KEY	Left outer join is performed on the Ticket table on the basis of source system generated Infant Ticket Identifier to get unique Ticket Key.	Join performed between DWB_BKG_H and DWD_TKT table on TKT_ID column to fetch value for TKT_KEY
INF_PAX_KEY	DWB_BKG_H, DWM_BKG_PAX	DWM_BKG_PAX.PAX_KEY	Left outer join is performed on the Booking Passenger table on the basis of source system generated Infant Passenger Identifier to get the unique Passenger Key.	Join performed between DWB_BKG_H and DWM_BKG_PAX table on PAX_ID column to fetch value for PAX_KEY
OPRTNG_FLT_KEY	DWB_BKG_H, DWM_FLT	DWM_FLT. FLT_KEY	Left outer join is performed on the Flight table on the basis of source system generated operating Flight Identifier to get the unique Flight Key.	Join performed between DWB_BKG_H and DWM_FLT table on FLT_ID column to fetch value for FLT_KEY
MKTG_FLT_KEY	DWB_BKG_H, DWM_FLT	DWM_FLT. FLT_KEY	Left outer join is performed on the Flight table on the basis of source system generated marketing Flight Identifier to get the unique Flight Key.	Join performed between DWB_BKG_H and DWM_FLT table on FLT_ID column to fetch value for FLT_KEY
ARVL_AIP_KEY	DWB_BKG_H, DWM_AIP, DWM_SEG	DWM_AIP. AIP_KEY	Left outer join is performed on the Airport and Segment table on the basis of source system generated Segment Identifier and Offpoint Airport name to get the unique Airport Key.	Join performed between DWB_BKG_H and DWM_AIP and DWM_SEG tables on OFPNT_AIP_NM and SEG_ID columns to fetch value for AIP_KEY
DEPTR_AIP_KEY	DWB_BKG_H, DWM_AIP, DWM_SEG	DWM_AIP. AIP_KEY	Left outer join is performed on the Airport and Segment table on the basis of source system generated Segment Identifier and Board Airport name to get the unique Airport Key.	Join performed between DWB_BKG_H and DWM_AIP and DWM_SEG tables on BRD_AIP_NMand SEG_ID columns to fetch value for AIP_KEY
BKG_CITY_KEY	DWB_BKG_H, DWM_GEOGRY	DWB_BKG_H.BKG_CITY_CD	Left outer join is performed on the Geography table on the basis of source system generated Booking City Code to get the City Key.	Join performed between DWB_BKG_H and DWM_GEOGRY on BKG_CITY_CD columns to fetch value for CITY_KEY.
CORP_CUST_KEY	DWB_BKG_H, DWM_CORP_CUST	DWM_CORP_CUST. CORP_CUST_KEY	Left outer join is performed on the Corporate Customer table on the basis of Customer client code to get the unique Corporate Customer Key.	Join performed between DWB_BKG_H and DWM_CORP_CUST table on CUST_CLNT_CD column to fetch value for CORP_CUST_KEY
TRAF_CATG_KEY	DWB_BKG_H, DWM_TRAF_CATG to check in the package.	DWM_TRAF_CATG. TRAF_CATG_KEY	Left outer join is performed on the Traffic Category table on the basis of source system generated Traffic Category Identifier and Route Identifier to get the unique Traffic Category Key.	Join performed between DWB_BKG_H and DWM_TRAF_CATG table on TRAF_CATG_ID and ROUTE_ID columns to fetch value for TRAF_CATG_KEY
PNR_KEY	DWB_BKG_H , DWD_PNR	DWD_PNR.PNR_KEY	Left outer join is performed on the PNR table on the basis of source system generated PNR Identifier, Record locator and PNR Creation Date to get the unique PNR Key.	Join performed between DWB_BKG_H and DWD_PNR table on PNR_ID, RLOC and PNR_CREN_DT column to fetch value for PNR_KEY
BKG_CAMPN_ID	DWB_BKG_H	CAMPN_ID	Direct mapping. It is a unique number generated at the source system and the value flows from operational to derived layer. It helps to identify the record as unique throughout the system. That is from source to derived layer.
BKG_CREN_TM_ID	DWB_BKG_H, DWM_TM	DWM_TM. TM_ID	Left outer join is performed on the Time table on the basis of Hour of the day and Minute of the day to get the unique Time Identifier.	Join performed between DWB_BKG_H and DWM_TM table on HOUR_OF_DAY and MINT_OF_DAY column to fetch value for TM_ID
WTLST_TM_ID	DWM_TM	DWM_TM. TM_ID	Left outer join is performed on the Time table on the basis of Hour of the day and Minute of the day to get the unique Time Identifier.	Join performed between DWB_BKG_H and DWM_TM table on HOUR_OF_DAY and MINT_OF_DAY column to fetch value for TM_ID
BKG_LAST_UPDT_TM_ID	DWM_TM	DWM_TM. TM_ID	Left outer join is performed on the Time table on the basis of Hour of the day and Minute of the day to get the unique Time Identifier.	Join performed between DWB_BKG_H and DWM_TM table on HOUR_OF_DAY and MINT_OF_DAY column to fetch value for TM_ID
BKG_CNCL_TM_ID	DWM_TM	DWM_TM. TM_ID	Left outer join is performed on the Time table on the basis of Hour of the day and Minute of the day to get the unique Time Identifier.	Join performed between DWB_BKG_H and DWM_TM table on HOUR_OF_DAY and MINT_OF_DAY column to fetch value for TM_ID
BKG_CONF_TM_ID	DWM_TM	DWM_TM. TM_ID	Left outer join is performed on the Time table on the basis of Hour of the day and Minute of the day to get the unique Time Identifier.	Join performed between DWB_BKG_H and DWM_TM table on HOUR_OF_DAY and MINT_OF_DAY column to fetch value for TM_ID
CPN_ID	DWB_BKG_H	CPN_ID	Direct mapping. It is the Coupon Identifier generated at source system and the data flows from operational to derived layer. It helps to identify the record as unique throughout the system. That is from source to derived layer.
INF_CPN_ID	DWB_BKG_H	INF_CPN_ID	Direct mapping. It is the Infant Coupon Identifier generated at source system and the data flows from operational to derived layer. It helps to identify the record as unique throughout the system. That is from source to derived layer.
CLNT_ID
CLS	DWB_BKG_H	CLS	Direct mapping. It is the Class booked. Data flows from operational to derived layer.
NIGHT_IND	DWB_BKG_H	NIGHT_IND	Direct mapping. It is the flag if the class booked is a Night Class, it is set to ¡¯Y¡¯ or else ¡¯N¡¯.
STS_CD	DWB_BKG_H	STS_CD	Direct mapping. It is the status of the booking. Data flows from operational to derived layer.
DEAD_IND	DWB_BKG_H	DEAD_IND	Direct mapping. It is the source system generated indicator whether the booking is canceled or not. Data flows from operational to derived layer.	Cancelled = ¡¯Y¡¯
BKG_LAST_UPDT_TMSTMP	DWB_BKG_H	BKG_LAST_UPD_TMSTMP	Direct mapping. It is the updated timestamp of the source system when the booking was last updated. Data flows from operational to derived layer.
ORGNL_ACTN_CD	DWB_BKG_H	ORGNL_ACTN_CD	Direct mapping. It is the action code used at the sell time. It is helpful in identifying overbooking. Data flows from operational to derived layer.
WTLST_IND	DWB_BKG_H	WTLST_IND	Direct mapping. It indicates if the booking has a waitlist status code. Data flows from operational to derived layer.
WTLST_DTTM	DWB_BKG_H	WTLST_DTTM	Direct mapping. It indicates the timestamp of the booking, if it is waitlisted. Data flows from operational to derived layer.
CNCL_DTTM	DWB_BKG_H	BKG_CNCL_DTTM	Direct mapping. It indicates the timestamp if the booking is canceled. Data flows from operational to derived layer.
CONF_DTTM	DWB_BKG_H	BKG_CONF_DTTM	Direct mapping. It indicates the timestamp if the booking is confirmed. Data flows from operational to derived layer.
CONF_IND	DWB_BKG_H	CONF_IND	Direct mapping. It indicates confirmed status code of the booking. Data flows from operational to derived layer.
BKG_CREN_TMSTMP	DWB_BKG_H	BKG_CREN_DTTM	Direct mapping. It indicates the timestamp of the source system when the ticket was booked. Data flows from operational to derived layer.
FST_IND	DWB_BKG_H	FST_IND	Direct mapping. Data flows from operational to derived layer.
BUSNS_IND	DWB_BKG_H	BUSNS_IND	Direct mapping. It indicates whether the booking is business class or not. Data flows from operational to derived layer.
ECONMY_IND	DWB_BKG_H	ECONMY_IND	Direct mapping. It indicates whether the booking is economy or not.
CDSH_CLS	DWB_BKG_H	CDSH_CLS	Direct mapping.
CBN_CD	DWB_BKG_H	CBN_CD	Direct mapping.
CPN_AMT	DWB_BKG_H	CPN_AMT	Direct mapping.
INF_CPN_AMT	DWB_BKG_H	INF_CPN_AMT	Direct mapping.
PNR_RLOC	DWB_BKG_H	BKG_PNR_RLOC	Direct mapping.
PNR_CREN_DT	DWB_BKG_H	PNR_CREN_DT	Direct mapping.
ACCT_NBR	DWB_BKG_H	ACCT_NBR	Direct mapping.
MKTG_REF_RLOC	DWB_BKG_H	MKTG_REF_RLOC	Direct mapping.
BKG_OPRTNG_FLT_DT_UTC	DWB_BKG_H	BKG_OPRTNG_FLT_DT_UTC	Direct mapping. It indicates the operating flight date in the UTC time zone.
BKG_MKTG_FLT_DT_LCL	DWB_BKG_H	BKG_MKTG_FLT_DT_LCL	Direct mapping. It indicates the marketing flight date in the local time zone.
BKG_MKTG_FLT_DT_UTC	DWB_BKG_H	BKG_MKTG_FLT_DT_UTC	Direct mapping. It indicates the marketing flight date in the UTC time zone.
CDSH_IND	DWB_BKG_H	CDSH_IND	Direct mapping.
RQST_TYP	DWB_BKG_H	RQST_TYP	Direct mapping.
SMOKNG_IND	DWB_BKG_H	SMOKNG_IND	Direct mapping.
SEAT_STS	DWB_BKG_H	SEAT_STS	Direct mapping.
SEAT_NBR	DWB_BKG_H	SEAT_NBR	Direct mapping. It indicates the seat number of the booking.
OVRBKG_TYP	DWB_BKG_H	OVRBKG_TYP	Direct mapping.
OVRBKG_RSN	DWB_BKG_H	OVRBKG_RSN_DESC	Direct mapping. It indicates the description for over booking.
BKG_STS_CHNG_IND	DWB_BKG_H	BKG_STS_CHNG_IND	Direct mapping. It indicates the booking status changes from booking to waitlisted or canceled.
BRDNG_IND	DWB_BKG_H	BRDNG_IND	Direct mapping. It is flag which indicates whether the booking has converted to checkin or the passenger of the booking has boarded.
GRPNG_IND	DWB_BKG_H	GRPNG_IND	Direct mapping. It indicates whether it is a group booking or not.
CLID_CARR_CD	DWB_BKG_H	CLID_CARR_CD	Direct mapping. It indicates the carrier code of the client.
CDSH_AGMT	DWB_BKG_H	CDSH_AGMT	Direct mapping.
FRQTFLIER_NBR	DWB_BKG_H	FRQTFLIER_NBR	Direct mapping. It is a source system generated unique Frequent Flyer number if the booking is done by the Frequent Flyer.
MEAL_CD	DWB_BKG_H, DWR_INFLT_MEAL_H	DWR_INFLT_MEAL_H. MEAL_CD	Left outer join is performed on the Inflight Meal table on the basis of source system generated Meal Identifier to get the Meal Code.	Join performed between DWR_BKG_OFF_H and DWR_INFLT_MEAL_H table on MEAL_ID column to fetch value for MEAL_CD
OPEN_IND	DWB_BKG_H	OPEN_IND	Direct mapping.
INFO_IND	DWB_BKG_H	INFRMTNL_IND	Direct mapping. It indicates if the booking is created as informational copy.
BKG_OPRL_FLT_DT	DWB_BKG_H	BKG_OPRTNG_FLT_DT_LCL	Direct mapping. It converts the Booking operating flight local timestamp to date.	TO_DATE(TO_CHAR(BKG_OPRTNG_FLT_DT_LCL,'DD-MON-YYYY'))
MKTG_SEG_DEPTR_DT_UTC	Column not listed in the package.
BKG_MKTG_FLT_DT	DWB_BKG_H	BKG_MKTG_FLT_DT_LCL	Direct mapping. It converts the Booking marketing flight local timestamp to date.	TO_DATE(TO_CHAR(SRC2.BKG_MKTG_FLT_DT_LCL,'DD-MON-YYYY'))
MKTG_SEG_DEPTR_DT_LCL	DWB_BKG_H	MKTG_SEG_DEPTR_DT_LCL	Direct mapping.
NGSPC_REF	DWB_BKG_H	NGSPC_REF	Direct mapping. It indicates the DBID of a NegoSpace block (if the booking is sold in a block)
OPRTNG_INFRMTNL_COPY	DWB_BKG_H	OPRTNG_INFRMTNL_COPY	Direct mapping. It indicates the operating information copy of the booking.
SUBCLS_CLS_SRC	DWB_BKG_H	SUBCLS_CLS_SRC	Direct mapping.
SUBCLS_CLS_CTRY	DWB_BKG_H	SUBCLS_CLS_CTRY	Direct mapping.
SUBCLS_CLS_SYS	DWB_BKG_H	SUBCLS_CLS_SYS	Direct mapping.
BKG_TYP	DWB_BKG_H	BKG_TYP	Direct mapping. It indicates the type of booking done. Data flows from operational layer to derived layer.
APIS_CMPLT_IND	DWB_BKG_H	APIS_CMPLT_IND	Direct mapping.
TST_PAX_FARE_BASIS_CD	DWB_BKG_H	TST_PAX_FARE_BASIS_CD	Direct mapping. It indicates the fare basis code of the passenger according to booking.
TST_INF_FARE_BASIS_CD	DWB_BKG_H	TST_INF_FARE_BASIS_CD	Direct mapping. It indicates the fare basis code of the infant passenger according to booking.
BKG_IP_ADDR	DWB_BKG_H	BKG_IP_ADDR	Direct mapping. It indicates the IP address used for booking.
BID_PRC	DWB_BKG_H	BID_PRC	Direct mapping. It indicates the bid price for the booking.
YLD	DWB_BKG_H	YLD	Direct mapping.
RVN_LOSS	DWB_BKG_H	RVN_LOSS	Direct mapping.
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is new or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.
BKG_GRP_IND	DWB_BKG_H	GRP_BKG_IND	Direct Mapping.
FLN_RVN	DWB_BKG_H	FLN_RVN	Direct Mapping. It indicates the flown revenue.
FLN_PAX_CNT	DWB_BKG_H	FLN_PAX_CNT	Direct Mapping. It indicates the flown passenger count.
NON_RVN_FLN_PAX_CNT	DWB_BKG_H	NON_RVN_FLN_PAX_CNT	Direct Mapping.
ONFLT_ORGN_TO_DEST_FLT_RVN	DWB_BKG_H	ONFLT_ORGN_TO_DEST_FLT_RVN	Direct Mapping.
FLN_RVN_ORGN_TO_DEST_OFRD	DWB_BKG_H	FLN_RVN_ORGN_TO_DEST_OFRD	Direct Mapping.
ONFLT_ORGN_TO_DEST_FLN_PAX_CNT	DWB_BKG_H	ONFLT_ORGN_TO_DEST_FLN_PAX_CNT	Direct Mapping.
OFRD_ORGN_TO_DEST_FLN_PAX_CNT	DWB_BKG_H	OFRD_ORGN_TO_DEST_FLN_PAX_CNT	Direct Mapping.
CPN_CNT	DWB_BKG_H	CPN_CNT	Direct Mapping.
ONBRD_RVN	DWB_BKG_H	ONBRD_RVN	Direct Mapping.
EXCESS_BAG_RVN	DWB_BKG_H	EXCESS_BAG_RVN	Direct Mapping.
FEES_RVN	DWB_BKG_H	FEES_RVN	Direct Mapping.
CHARTER_RVN	DWB_BKG_H	CHARTER_RVN	Direct Mapping.
BELLY_CARGO_RVN	DWB_BKG_H	BELLY_CARGO_RVN	Direct Mapping.
CDSH_RVN	DWB_BKG_H	CDSH_RVN	Direct Mapping.
OTR_RVN	DWB_BKG_H	OTR_RVN	Direct Mapping.
BKG_OPRTNG_FLT_DT_LCL	DWB_BKG_H	BKG_OPRTNG_FLT_DT_LCL	Direct mapping. It indicates the marketing flight date in the Local time zone.
OFF_KEY	DWB_BKG_H, DWM_BKG_OFF, DWM_SALES_CHNL	DWM_BKG_OFF. OFF_KEY	Left outer join is performed on the Booking Office and Sales Channel table on the basis of source system generated Office Identifier to get the unique Office Key.	Join performed between DWB_BKG_Hand DWM_BKG_OFF and DWM_FRQTFLR table on OFF_ID column to fetch value for OFF_KEY
OPRTNG_SEG_DEPTR_LCL_DT_KEY	DWD_FLT_DETLS_FACT	DWD_FLT_DETLS_FACT. DEPTR_LCL_DT_KEY	Left outer join is performed on the Flight Details Fact Booking Office on the basis of Segment key, Flight key, Flight date, Effective date and End date to get the Departure Local Date Key.	Join performed between DWB_BKG_Hand DWD_FLT_DETLS_FACT table on SEG_KEY, FLT_KEY, FLT_DT, EFFECTIVE_DT and END_DT column to fetch value for DEPTR_LCL_DT_KEY
OPRTNG_SEG_DEPTR_UTC_DT_KEY	DWD_FLT_DETLS_FACT	DWD_FLT_DETLS_FACT. DEPTR_UTC_DT_KEY	Left outer join is performed on the Flight Details Fact Booking Office on the basis of Segment key, Flight key, Flight date, Effective date and End date to get the Departure UTC Date Key.	Join performed between DWB_BKG_Hand DWD_FLT_DETLS_FACT table on SEG_KEY, FLT_KEY, FLT_DT, EFFECTIVE_DT and END_DT column to fetch value for DEPTR_UTC_DT_KEY

PKG_DWD_CALL_CNTR_PRFMNC Mapping

Table 6-14 shows the mapping to populate target table DWD_CALL_CNTR_PRFMNC. For more information, see CALL CENTER PERFORMANCE.

Source Tables

DWB_PRTY_INTRATN_CALL_H

DWB_PRTY_INTRATN_H

DWM_CLNDR

DWM_BKG_OFF

DWR_SMS_AGNT_H

Table 6-14 PKG_DWD_CALL_CNTR_PRFMNC ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
DLY_CALL_CNTR_PRFMNC_KEY	DWB_PRTY_INTRATN_CALL_H	DWB_PRTY_INTRATN_CALL_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
INTRATN_EVNT_DTTM_KEY	DWB_PRTY_INTRATN_H	INTRATN_EVNT_DTTM_KEY	Left outer join is performed on the PARTY INTERACTION H table to get INTRATN_EVNT_DT_AND_TM and Left outer join CALENDAR table to get INTRATN_EVNT_DTTM_KEY	Join performed between PARTY INTERACTION H and CALENDAR table on CLNDR_DT column to fetch value for INTRATN_EVNT_DTTM_KEY
PRTY_INTRATN_CALL_ID	DWB_PRTY_INTRATN_CALL_H	PRTY_INTRATN_CALL_ID	Direct mapping. It is the unique number generated at source system and the data flows from source to operational and then to derived layer. It helps to identify the record as unique throughout the system. That is from source to derived layer.
TALK_DURN	DWB_PRTY_INTRATN_CALL_H	TALK_DURN	Direct mapping. It indicates the duration of the talk time. Data flows from operational to derived layer.
HLDD_BY_IVR_IND	DWB_PRTY_INTRATN_CALL_H	HLDD_BY_IVR_IND	Direct mapping. The call was handled by IVR. Y, N, P(artially). Data flows from operational to derived layer.
CUST_STSFYN_IND	DWB_PRTY_INTRATN_CALL_H	CUST_STSFYN_IND	Direct mapping. It indicates whether the customer was satisfied with the interaction. Data flows from operational to derived layer.
INTRATN_DURN	DWB_PRTY_INTRATN_CALL_H	INTRATN_DURN	Direct mapping. It indicates the interaction duration in minutes. Data flows from operational to derived layer.
CALL_CNTR_AGNT_KEY	DWB_PRTY_INTRATN_CALL_H	CALL_CNTR_AGNT_KEY
QUE_DURN	DWB_PRTY_INTRATN_CALL_H	No column mapping
HLD_DURN	DWB_PRTY_INTRATN_CALL_H	No column mapping
CALL_CNTR_KEY	DWB_PRTY_INTRATN_CALL_H	CALL_CNTR_KEY	A unique key generated by the source system. Data flows from operational to derived layer.
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.

PKG_DWD_CUST_SRVY Mapping

Table 6-15 shows the mapping to populate target table DWD_CUST_SRVY. For more information, see CUSTOMER SURVEY.

Source Tables

DWB_PRTY_INTRATN_THREAD

DWM_CLNDR

DWM_SVC

DWM_INTRATN_RSN

DWM_INTRATN_RSLT

Table 6-15 PKG_DWD_CUST_SRVY ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
CUST_SRVY_KEY	DWB_PRTY_INTRATN_THRD_H	DWB_PRTY_INTRATN_THRD_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
SVC_KEY	DWB_PRTY_INTRATN_THRD_H, DWM_SVC	SVC_KEY	Left outer join is performed on the SERVICE table on the basis of SERVICE ID to get SERVICE KEY.	Join performed between DWB_PRTY_INTRATN_THRD_H and DWM_SVC table on SVC_ID column to fetch value for SVC_KEY.
INTRATN_RSN_KEY	DWB_PRTY_INTRATN_THRD_H, DWM_INTRATN_RSN	INTRATN_RSN_KEY	Left outer join is performed on the INTERACTION REASON table on the basis of INTERACTION REASON ID to get the INTERACTION REASON KEY.	Join performed between DWB_PRTY_INTRATN_THRD_H and DWM_INTRATN_RSN table on INTRATN_RSN_ID column to fetch value for INTRATN_RSN_KEY
INTRATN_THRD_STRT_DT_KEY	DWB_PRTY_INTRATN_THRD_H, DWM_CLNDR	INTRATN_THRD_STRT_DT	Left outer join is performed on the CALENDAR table on the basis of CLNDR_DT to get the INTERACTION THREADSTARTDATEKEY	Join performed between DWB_PRTY_INTRATN_THRD_H and DWM_CLNDR table on CLNDR_DT column to fetch value for INTRATN_THRD_STRT_DT_KEY
PRTY_INTRATN_THRD_ID	DWB_PRTY_INTRATN_THRD_H	PRTY_INTRATN_THRD_ID	Direct mapping. Data flows from operational to derived layer.
INTRATN_THRD_CLOSE_DT_KEY	DWB_PRTY_INTRATN_THRD_H, DWM_CLNDR	INTRATN_THRD_CLOSE_DT	Left outer join is performed on the CALENDAR table on the basis of CALENDAR DATE to get the INTERACTION THREADCLOSEDATEKEY	Join performed between DWB_PRTY_INTRATN_THRD_H and DWM_CLNDR table on CLNDR_ DT column to fetch value for INTRATN_THRD_CLOSE_DT_KEY
INTRATN_THRD_TRGT_DT_KEY	DWB_PRTY_INTRATN_THRD_H , DWM_CLNDR	INTRATN_TRGT_DT	Left outer join is performed on the CALENDAR table on the basis of CALENDAR DATE to get the INTERACTION THREAD TARGET DATEKEY	Join performed between DWB_PRTY_INTRATN_THRD_H and DWM_CLNDR table on CLNDR_DT column to fetch value for INTRATN_THRD_TRGT_DT_KEY
INTRATN_RSLT_KEY	DWB_PRTY_INTRATN_THRD_H , DWM_INTRATN_RSLT	INTRATN_RSLT_TYP_ID	Left outer join is performed on the INTERACTION RESULT table on the basis of INTERACTION RESULT ID to get the INTERACTION RESULT KEY	Join performed between DWB_PRTY_INTRATN_THRD_H and DWM_INTRATN_RSLT table on INTRATN_RSLT_ ID column to fetch value for INTRATN_RSLT_KEY
CUST_ID	DWB_PRTY_INTRATN_THRD_H	CUST_ID	Direct mapping. Data flows from operational to derived layer.
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.

PKG_DWD_FLT_DETLS_FACT Mapping

Table 6-16 shows the mapping to populate target table DWD_FLT_DETLS_FACT. For more information, see FLIGHT DETAILS FACT.

Source Tables

DWB_FLT_SCHD_H

DWM_SEG

DWM_LEG

DWM_FLT

DWM_ACFT_VER

DWM_TRAF_CATG

DWM_CARR

DWM_CLNDR

DWM_AIP

Table 6-16 PKG_DWD_FLT_DETLS_FACT ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
FLT_DETLS_FACT_KEY	DWB_FLT_SCHD_H	DWB_FLT_SCHD_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
SEG_KEY	DWB_FLT_SCHD_H DWM_SEG	SEG_KEY	Left outer join is performed on the SEGMENT table on the basis of SEGMENT ID to get the SEGMENT KEY	Join performed between DWB_FLT_SCHD_H table and DWM_SEG table on SEG_ID column to fetch value for SEG_ KEY
LEG_KEY	DWB_FLT_SCHD_H DWM_LEG	LEG_KEY	Left outer join is performed on the LEG table on the basis of LEGID to get the LEGKEY	Join performed between DWB_FLT_SCHD_H table and DWM_LEG table on LEG_ID column to fetch value for LEG_KEY
ARVL_AIP_KEY	DWM_LEG DWM_AIP DWB_FLT_SCHD_H	ARVL_AIP_KEY	Left outer join is performed on the LEG and AIRPORT table with FLIGHT SCHEDULE Table on the basis of LEGID to get ARRIVALAIRPORTKEY	Join performed between DWM_AIP table and DWM_LEG table with DWB_FLT_SCHD_H Table on the basis of LEG_ID to fetch value for ARVL_AIP_KEY
DEPTR_AIP_KEY	DWM_LEG DWM_AIP DWB_FLT_SCHD_H	DEPTR_AIP_KEY	Left outer join is performed on the LEG and AIRPORT table ON the basis of LEGID to get DEPARTUREAIRPORTKEY	Join performed between DWM_LEG and DWM_AIP table and left outer join with DWB_FLT_SCHD_H table on the basis of LEG_ID to fetch value for DEPTR_AIP_KEY
TRAF_CATG_KEY	DWM_TRAF_CATG DWB_FLT_SCHD_H	TRAF_CATG_KEY	Left outer join is performed on the TRAFFIC CATEGORY table on the basis of TRAFFIC CATEGORY ID to get TRAFFIC CATEGORY KEY	Join performed between DWB_FLT_SCHD_H and DWM_TRAF_CATG table on the basis of TRAF_CATG_ID column to fetch value for TRAF_CATG_ KEY
ACV_KEY	DWB_FLT_SCHD_H	ACV_KEY
FLT_KEY	DWB_FLT_SCHD_H DWM_FLT	FLT_KEY	Left outer join is performed on the FLIGHT on the basis of FLIGHT ID to get FLIGHT KEY	NVL(FLT_KEY,-1)
CDSH_TYP	DWB_FLT_SCHD_H	FLT_CDSH_TYP	Direct mapping. This indicates the type of the flight in a codeshare. Data flows from operational to derived layer.
STS_FLG	DWB_FLT_SCHD_H	STS_FLG	Direct mapping. This store the active status flag for the flight used for analysis of active flights this Data flows from operational to derived layer.
EFFECTIVE_DT	DWB_FLT_SCHD_H	EFFECTIVE_DT	Direct mapping. This stores the start date of the flight in the system. That is when the first time this flight is introduced. Data flows from operational to derived layer.
END_DT	DWB_FLT_SCHD_H	END_DT	Direct mapping. This stores the end date of the flight in the system. That is when the flight stops operation. Data flows from operational to derived layer.
ACV_TOT_CPCTY	DWB_FLT_SCHD_H	LEG_ACV_TOT_CPCTY	Direct mapping. This stores the total capacity of the flight. Data flows from operational to derived layer.
SALEBLE_TOT_CPCTY	DWB_FLT_SCHD_H	TOT_SLBL_CPCTY	Direct mapping This stores the total salable capacity of the flight. Data flows from operational to derived layer.
NAUTICAL_MLS	DWB_FLT_SCHD_H	NAUTICAL_MLS	Direct mapping. Data flows from operational to derived layer.
NAUTICAL_TO_KILOMETER_CONV_IND	DWB_FLT_SCHD_H	NAUTICAL_TO_KILOMETER_CONV_IND	Direct mapping. Data flows from operational to derived layer.
ACV	DWB_FLT_SCHD_H	LEG_ACV	Direct mapping. This stores the aircraft version of the flight. Data flows from operational to derived layer.
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.
ARVL_LCL_DT_KEY	DWB_FLT_SCHD_H DWM_CLNDR	ARVL_LCL_DT_KEY	Left outer join is performed on the CALENDAR table on the basis of CALENDAR DATE to get ARRIVAL LOCAL DATE KEY	NVL(ARVL_LCL_DT_KEY,-1)
ARVL_UTC_DT_KEY	DWB_FLT_SCHD_H DWM_CLNDR	ARVL_UTC_DT_KEY	Left outer join is performed on the CALENDAR table on the basis of CALENDAR DATE to get ARRIVAL UTC DATE KEY	NVL(ARVL_UTC_DT_KEY,-1)
DEPTR_LCL_DT_KEY	DWB_FLT_SCHD_H DWM_CLNDR	DEPTR_LCL_DT_KEY	Left outer join is performed on the CALENDAR table on the basis of CALENDAR DATE get DEPARTURE LOCAL DATE KEY	NVL(DEPTR_LCL_DT_KEY,-1)
DEPTR_UTC_DT_KEY	DWB_FLT_SCHD_H DWM_CLNDR	DEPTR_DTTM_UTC	Left outer join is performed on the CALENDAR table on the basis of CALENDAR DATE to get DEPARTURE UTCDATEKEY	NVL(DEPTR_UTC_DT_KEY,-1)
FLT_CARR_CD	DWM_CARR DWB_FLT_SCHD_H	FLT_CARR_KEY	Left outer join is performed on the CARRIER table on the basis of CARRIER CODE to get the value of FLIGHTCARRIERCODE	Join performed between DWB_FLT_SCHD_H and DWM_CARR table on SEG_ID column to fetch value for FLT_CARR_KEY
FLT_DT	DWB_FLT_SCHD_H	FLT_DT	Direct mapping. Data flows from operational to derived layer.
FLT_NBR	DWB_FLT_SCHD_H	FLT_NBR	Direct mapping. This stores the flight number of the carrier. Data flows from operational to derived layer.
LEG_ACFT_SLBL_CONFIG_CD	DWB_FLT_SCHD_H	LEG_ACFT_SLBL_CONFIG_CD	Direct mapping. This identifies the fitted configuration of the aircraft. Data flows from operational to derived layer.
FLT_ALPHA_SFX	DWB_FLT_SCHD_H	FLT_ALPHA_SFX	Direct mapping. Data flows from operational to derived layer.
LEG_ACFT_TYP	DWB_FLT_SCHD_H	LEG_ACFT_TYP	Direct mapping. Data flows from operational to derived layer.

PKG_DWD_LYLTY_ACCT_BAL_HIST Mapping

Table 6-17 shows the mapping to populate target table DWD_LYLTY_ACCT_BAL_HIST. For more information, see LOYALTY ACCOUNT BALANCE HISTORY.

Source Tables

DWB_LYLTY_ACCT_BAL_HIST_H

DWM_CLNDR

DWM_FRQTFLR

DWD_LYLTY_PRG

Table 6-17 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)
LYLTY_ACCT_BAL_HIST_KEY	DWB_LYLTY_ACCT_BAL_HIST_H	DWB_LYLTY_ACCT_BAL_HIST_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
GEO_CITY_KEY	DWB_LYLTY_ACCT_BAL_HIST_H, DWM_FRQTFLR	DWM_FRQTFLR.CITY_KEY	Left outer join is performed on the Frequent Flyer table on the basis of source system generated Frequent Flyer Identifier to get the City Key.	Join performed between DWB_LYLTY_ACCT_BAL_HIST_H and DWM_FRQTFLR table on FRQTFLR_CARD_ID column to fetch value for CITY_KEY
BAL_DT_KEY	DWB_LYLTY_ACCT_BAL_HIST_H, DWM_CLNDR	DWM_CLNDR. CLNDR_KEY	Left outer join is performed on the Calendar table on the basis of the Calendar Date to get the Calendar Key.	Join performed between DWB_LYLTY_ACCT_BAL_HIST_H and DWM_CLNDR table on DWB_LYLTY_ACCT_BAL_HIST_H. BAL_DT = DWM_CLNDR.CLNDR_DT columns to fetch value for CLNDR_KEY
LYLTY_ACCT_BAL_HIST_ID	DWB_LYLTY_ACCT_BAL_HIST_H	LYLTY_ACCT_BAL_HIST_ID	Direct mapping. It is the unique number generated at source system and the data flows from source to operational and then to derived layer. It helps to identify the record as unique throughout the system. That is from source to derived layer.
CURR_MLS_AMT	DWB_LYLTY_ACCT_BAL_HIST_H	CURR_MLS_AMT	Direct mapping. It indicates the current miles amount after balance. Data flows from operational to derived layer.
LAST_BAL_AMT	DWB_LYLTY_ACCT_BAL_HIST_H	LAST_BAL_AMT	Direct mapping. It indicates the last balance points before update of any points. Data flows from operational to derived layer.
PROM_MLS_AMT	DWB_LYLTY_ACCT_BAL_HIST_H	PROM_MLS_AMT	Direct mapping. It indicates the points earned in a promotion. Data flows from operational to derived layer.
EXTRA_MLS_AMT	DWB_LYLTY_ACCT_BAL_HIST_H	EXTRA_MLS_AMT	Direct mapping. It indicates the frequent flyer gets extra points based on his account level for a particular flight. Data flows from operational to derived layer.
EXPRD_MLS_AMT	DWB_LYLTY_ACCT_BAL_HIST_H	EXPRD_MLS_AMT	Direct mapping. It indicates the points expired on the balance day. Data flows from operational to derived layer.
RDM_MLS_AMT	DWB_LYLTY_ACCT_BAL_HIST_H	RDM_MLS_AMT	Direct mapping. It indicates the points redeemed on the balance day. Data flows from operational to derived layer.
OTR_NON_AIR_MLS_AMT	DWB_LYLTY_ACCT_BAL_HIST_H	OTR_NON_AIR_MLS_AMT	Direct mapping. It indicates the points earned from non airline partners. Data flows from operational to derived layer.
FRQTFLIER_CARD_KEY	DWB_LYLTY_ACCT_BAL_HIST_H, DWM_FRQTFLR	DWM_FRQTFLR.FRQTFLIER_CARD_KEY	Left outer join is performed on the Frequent Flyer table on the basis of source system generated Frequent Flyer Card Identifier to get the unique Frequent Flyer Card Key.	Join performed between DWB_LYLTY_ACCT_BAL_HIST_H and DWM_FRQTFLR table on FRQTFLR_CARD_ID columns to fetch value for FRQTFLIER_CARD_KEY
CRT_DT_KEY	DWB_LYLTY_ACCT_BAL_HIST_H, DWM_CLNDR	DWM_CLNDR. CLNDR_KEY	Left outer join is performed on the Calendar table on the basis of the Calendar Date to get the Calendar Key.	Join performed between DWB_LYLTY_ACCT_BAL_HIST_H and DWM_CLNDR table on DWB_LYLTY_ACCT_BAL_HIST_H. CRT_DT = DWM_CLNDR.CLNDR_DT columns to fetch value for CLNDR_KEY
UPDT_DT_KEY	DWB_LYLTY_ACCT_BAL_HIST_H, DWM_CLNDR	DWM_CLNDR. CLNDR_KEY	Left outer join is performed on the Calendar table on the basis of the Calendar Date to get the Calendar Key.	Join performed between DWB_LYLTY_ACCT_BAL_HIST_H and DWM_CLNDR table on DWB_LYLTY_ACCT_BAL_HIST_H. UPDT_DT = DWM_CLNDR.CLNDR_DT columns to fetch value for CLNDR_KEY
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.
LYLTY_PRG_KEY	DWD_LYLTY_PRG, DWM_FRQTFLR	DWD_LYLTY_PRG. LYLTY_PRG_KEY	Left outer join is performed on the Loyalty Program table on the basis of the source system generated Loyalty Program Identifier to get the unique Program Key.	Join performed between DWD_LYLTY_PRG and DWM_FRQTFLR table on LYLTY_PRG_ID column to fetch value for LYLTY_PRG_KEY

PKG_DWD_LYLTY_ACCT_LVL_HIST Mapping

Table 6-18 shows the mapping to populate target table DWD_LYLTY_ACCT_LVL_HIST. For more information, see LOYALTY ACCOUNT LEVEL HISTORY.

Source Tables

DWB_LYLTY_ACCT_LVL_HIST_H

DWM_CLNDR

DWM_LYLTY_LVL

DWM_FREQUENT_FLYER

Table 6-18 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)
LYLTY_ACCT_LVL_HIST_KEY	DWB_LYLTY_ACCT_LVL_HIST_H	DWB_LYLTY_ACCT_LVL_HIST_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
ACCT_LVL_HIST_ID	DWB_LYLTY_ACCT_LVL_HIST_H	ACCT_LVL_HIST_ID	Direct mapping Data flows from operational to derived layer.
FRQTFLIER_CARD_KEY	DWB_LYLTY_ACCT_LVL_HIST_H DWM_FRQTFLR	FRQTFLIER_CARD_KEY	Left outer join performed on FREQUENT FLYER table on the basis of FREQUENT FLYER CARDID To get FREQUENT FLYER CARDKEY	Join performed between DWB_LYLTY_ACCT_LVL_HIST and DWM_FRQTFLR table on the basis of FRQTFLIER_CARD_ID column to fetch value for FRQTFLIER_ CARD_KEY
LYLTY_LVL_KEY	DWB_LYLTY_ACCT_LVL_HIST_H DWM_LYLTY_LVL	LYLTY_LVL_KEY	Left outer join performed on LOYALTY LEVEL on the basis of LOYALTY LEVEL ID to get LOYALTY LEVEL KEY	Join performed between DWB_LYLTY_ACCT_LVL_HIST and DWM_LYLTY_LVL table on LYLTY_LVL_ID column to fetch value for LYLTY_LVL_KEY
VLD_DT_KEY	DWB_LYLTY_ACCT_LVL_HIST_H DWM_CLNDR	VLD_DT_KEY	Left outer join performed on CALENDAR table on the basis of CALENDAR DATE to get VALIDDATEKEY	Join performed between DWB_LYLTY_ACCT_LVL_HIST_H and DWM_CLNDR table on CLNDR_DT column to fetch value for VLD_DT_KEY
EXPRY_DT_KEY	DWB_LYLTY_ACCT_LVL_HIST_H DWM_CLNDR	EXPRY_DT_KEY	Left outer join performed on CALENDAR table on the basis of CALENDAR DATE to get EXPIRY DATE KEY	Join performed between DWB_LYLTY_ACCT_LVL_HIST_H and DWM_CLNDR table on CLNDR_DT column to fetch value for EXPRY _DT_KEY
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.

PKG_DWD_LYLTY_PRG Mapping

Table 6-19 shows the mapping to populate target table DWD_LYLTY_PRG. For more information, see LOYALTY PROGRAM.

Source Table

DWB_LYLTY_PRG_H

Table 6-19 PKG_DWD_LYLTY_PRG ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
LYLTY_PRG_KEY	DWB_LYLTY_PRG_H	DWB_LYLTY_PRG_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
LYLTY_PRG_ID	DWB_LYLTY_PRG_H	LYLTY_PRG_ID	Direct mapping. This indicates a unique ID associated with a loyalty program. Data flows from operational to derived layer.
LYLTY_PRG_NM	DWB_LYLTY_PRG_H	LYLTY_PRG_NM	Direct mapping. This indicates Loyalty Program name. Data flows from operational to derived layer.
LYLTY_PRG_DESC	DWB_LYLTY_PRG_H	LYLTY_PRG_DESC	Direct mapping. This gives detail description of the loyalty program. Data flows from operational to derived layer.
LYLTY_PRG_STRT_DT	DWB_LYLTY_PRG_H	LYLTY_PRG_STRT_DT	Direct mapping. This indicates the start date of the loyalty program. Data flows from operational to derived layer.
LYLTY_PRG_END_DT	DWB_LYLTY_PRG_H	LYLTY_PRG_END_DT	Direct mapping. This indicates the end date of the loyalty program. Data flows from operational to derived layer.
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.

PKG_DWD_PNR Mapping

Table 6-20 shows the mapping to populate target table DWD_PNR. For more information, see PASSENGER NAME RECORD.

Source Table

DWL_PNR_TYP_H

DWB_PNR_H

Table 6-20 PKG_DWD_PNR ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
PNR_KEY	DWB_PNR_H	DWB_PNR_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
PNR_ID	DWB_PNR_H	PNR_ID	Direct mapping This indicates the primary key of the table assigned by source system ADS and is called adsuniqueid of the entity Data flows from operational to derived layer.
RLOC	DWB_PNR_H	RLOC	Direct mapping Data flows from operational to derived layer.
CURR_ENVLP	DWB_PNR_H	CURR_ENVLP	Direct mapping Data flows from operational to derived layer.	NVL(CURR_ENVLP,pv_CURRENT_ENVELOPE_NVL) AS CURR_ENVLP
DORMANT_IND	DWB_PNR_H	DORMANT_IND	Direct mapping. This indicates if a PNR is a Dormant PNR or not, This is created during bookings. Data flows from operational to derived layer.
PURGE_DT	DWB_PNR_H	PURGE_DT	Direct mapping Data flows from operational to derived layer.
NON_CMCL_NIP	DWB_PNR_H	NON_CMCL_PNR	Direct mapping. If the PNR is a group PNR; this contains the number of unassigned names. This field is N/A for Individual PNRs, Non Commercial PNRs or Corporate PNR Data flows from operational to derived layer.
NON_CMCL_NM	DWB_PNR_H	NON_CMCL_NM	Direct mapping. This stores the value if the PNR is a non commercial PNR, it contains the non commercial name; this field is not available for individual pnr, group or corp pnr. Data flows from operational to derived layer.
UNASSGND_NIP	DWB_PNR_H	UNASSGND_NIP	Direct mapping Data flows from operational to derived layer.
GRP_NM	DWB_PNR_H	GRP_NM	Direct mapping Data flows from operational to derived layer.
JRNY_ORGN	DWB_PNR_H	JRNY_ORGN	Direct mapping Data flows from operational to derived layer.
JRNY_DEST	DWB_PNR_H	JRNY_DEST	Direct mapping Data flows from operational to derived layer.
JRNY_RET_PNT	DWB_PNR_H	JRNY_RET_PNT	Direct mapping Data flows from operational to derived layer.
JRNY_TRIP_TYP	DWB_PNR_H	JRNY_TRIP_TYP	Direct mapping Data flows from operational to derived layer.
ORGL_SEAT_CNT	DWB_PNR_H	ORGL_SEAT_CNT	Direct mapping. This stores the original seat count of the PNR. Data flows from operational to derived layer.
CURR_SEAT_CNT	DWB_PNR_H	CURR_SEAT_CNT	Direct mapping Data flows from operational to derived layer.
CURR_NM_CNT	DWB_PNR_H	CURR_NM_CNT	Direct mapping Data flows from operational to derived layer
PNR_TYP_CD	DWB_PNR_H DWL_PNR_TYP_H	PNR_TYP	Left outer join performed on PASSENGER NAME RECORD TYPE on the basis of PASSENGER NAME RECORD TYPE IDENTIFIER to get PASSENGER NAME RECORD TYPE	Join performed between DWB_PNR_H and DWL_PNR_TYP_H table on PNR_TYP_ID column to fetch value for PNR_TYP
PNR_CREN_DT	DWB_PNR_H	PNR_CRTD_DTTM	Direct mapping Data flows from operational to derived layer
PNR_TYP_ID	DWB_PNR_H	PNR_TYP_ID	Direct mapping This stores the type of PNR IND=individual, GRP=Group , COR= Corporate NCO= Non Commercial Data flows from operational to derived layer.
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.

PKG_DWD_TKT Mapping

Table 6-21 shows the mapping to populate target table DWD_TKT. For more information, see TICKET.

Source Table

DWB_TKT_H

Table 6-21 PKG_DWD_TKT ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
TKT_KEY	DWB_TKT_H	DWB_TKT_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
TKT_ID	DWB_TKT_H	TKT_ID	Direct mapping This indicates the primary key of the table assigned by source system ADS and is called adsunique id of the entity Data flows from operational to derived layer.
PRIMRY_NBR	DWB_TKT_H	PRIMRY_NBR	Direct mapping This stores the Primary number assigned to the ticket. Data flows from operational to derived layer.
CONJTVE_TKT_CNT	DWB_TKT_H	NBR_OF_CONJUCTIVE_TKT	Direct mapping This stores the number of tickets issued with this ticket. Data flows from operational to derived layer.
CRCY	DWB_TKT_H	TKT_CRCY	Direct mapping. This stores the ticket currency, the base currency in which the ticket is issued. Data flows from operational to derived layer.
TOT_AMT	DWB_TKT_H	TOT_AMT	Direct mapping This stores the total ticket amount including tax. Data flows from operational to derived layer.
PAX_TYP	DWB_TKT_H	PAX_TYP	Direct mapping. This stores the type of passenger for this ticket Data flows from operational to derived layer.
ISNG_OFF_ID	DWB_TKT_H	OFF_ID	Direct mapping. This stores the issuing office id of the ticket Data flows from operational to derived layer.
DT_OF_ISS	DWB_TKT_H	TKT_ISNG_DT	Direct mapping This stores Date of issue of ticket. Available only If ticket issued in Amadeus reservation system. Data flows from operational to derived layer.	TO_TIMESTAMP(TKT_ISNG_DT,'DD-MON-YY H12.MI.SS.FF AM')
ISS_OFF_IATA_CD	DWB_TKT_H	ISNG_OFF_IATA_CD	Direct mapping This stores the Iata code of the ticket issuing office Data flows from operational to derived layer.
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.

PKG_DWM_ACCT Mapping

Table 6-22 shows the mapping to populate target table DWM_ACCT. For more information, see ACCOUNT.

Source Table Name

DWR_ACCT_H

Table 6-22 PKG_DWM_ACCT ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
ACCT_KEY	DWR_ACCT_H	DWR_ACCT_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
ACCT_ID	DWR_ACCT_H	ACCT_ID	Direct mapping. It is the unique number generated at source system and the data flows from source to operational and then to derived layer. It helps to identify the record as unique throughout the system. That is from source to derived layer.
ACCT_NBR	DWR_ACCT_H	ACCT_NBR	Direct mapping. The account number is generated to which the booking is accounted for.
ACCT_COST_CNTR	DWR_ACCT_H	ACCT_COST_CNTR	Direct mapping. The account cost center information of the account is loaded from operational to derived layer.
ACCT_IATA_CO_NBR	DWR_ACCT_H	ACCT_IATA_CO_NBR	Direct mapping. The account iata company number is loaded from operational to derived layer.
ACCT_CLNT_REF_NBR	DWR_ACCT_H	ACCT_CLNT_REF_NBR	Direct mapping. The account client reference number and flows from operational to derived layer.
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.

PKG_DWM_ACFT_VER Mapping

Table 6-23 shows the mapping to populate target table DWM_ACFT_VER. For more information, see AIRCRAFT VERSION.

Source Table Name

DWL_ACV_H

Table 6-23 PKG_DWM_ ACFT_VER ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
ACV_KEY	DWL_ACV_H	DWL_ACV_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
AIRCARFTVER_ID	DWL_ACV_H	ACV_ID	Direct mapping. It is the unique number generated at source system and the data flows from source to operational and then to derived layer. It helps to identify the record as unique throughout the system. That is from source to derived layer.
ACFT_TYP_ID	DWL_ACV_H	ACFT_TYP_ID	Direct mapping. It is the unique number generated at source system and the data flows from source to operational and then to derived layer. It helps to identify the record as unique throughout the system. That is from source to derived layer.
ACV	DWL_ACV_H	ACV	Direct mapping. It indicates the Aircraft Version. Data flows from operational to derived layer.
SLBL_CONFIG_CD	DWL_ACV_H	SLBL_CONFIG_CD	Direct mapping. It indicates the salable configuration code. Data flows from operational to derived layer.
ACFT_TYP	DWL_ACV_H	ACFT_TYP	Direct mapping. It indicates the aircraft type for the aircraft version. Data flows from operational to derived layer.
ACV_TOT_CPCTY	DWL_ACV_H	ACV_TOT_CPCTY	Direct mapping. It indicates the sum(ACV Cabin Capacity for each cabin code)
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.

PKG_DWM_AIP Procedure

Table 6-24 shows the mapping to populate target table DWM_AIP. For more information, see AIRPORT.

Source Table Name

DWR_ AIP_H

Table 6-24 PKG_DWM_ AIP ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description
AIP_KEY	DWR_AIP_H	DWR_AIP_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
AIP_ID	DWR_AIP_H	AIP_ID	Direct mapping. This indicates the system generated unique assigned to the attribute in the operational layer
AIP_NM	DWR_AIP_H	AIP_NM	Direct mapping. This indicates the short name given to the airport
CITY	DWR_AIP_H	CITY	Direct mapping. This indicates the city short name to which the airport belongs
REGN	DWR_AIP_H	REGN	Direct mapping. This indicates the region to which the airport belongs
CTRY	DWR_AIP_H	CTRY	Direct mapping. This indicates the country to which the airport belongs
CONT	DWR_AIP_H	CONT	Direct mapping. This indicates the continent to which the airport belongs
CITY_LONG_NM	lv_CITY_LONG_NM	Parameterized
AIP_LONG_NM	lv_AIP_LONG_NM	Parameterized
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.
CITY_CD	DWR_AIP_H	CITY_CD	Direct mapping.
REGN_CD	DWR_AIP_H	REGN_CD	Direct mapping.
CONT_CD	DWR_AIP_H	CONT_CD	Direct mapping.
CTRY_CD	DWR_AIP_H	CTRY_CD	Direct mapping.

PKG_DWM_BKG_CLS_TYP Procedure

Table 6-25 shows the mapping to populate target table DWM_BKG_CLS_TYP. For more information, see BOOKING CLASS TYPE.

Source Tables

DWR_ BKG_CLS_H

DWL_BKG_CLS_H

Table 6-25 PKG_DWM_ BKG_CLS_TYP ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
BKG_CLS_KEY	DWL_BKG_CLS_H	DWL_BKG_CLS_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
BKG_CLS_ID	DWL_BKG_CLS_H	BKG_CLS_ID	Direct mapping. This indicates the primary key of the table assigned by source system and is called adsunique id of the entity
SVC_CLS_CD	DWL_BKG_CLS_H	SVC_CLS_CD	Direct mapping. This indicates the values of the service class ODT request to rename attribute from SERVICE CLASS to SERVICE CLASS CODE
SVC_CLS_DESC	DWL_BKG_CLS_H	SVC_CLS_DESC	Direct mapping. This indicates the service class description
CARR_CD	DWL_BKG_CLS_H	CARR_CD	Direct mapping. This indicates the carrier code to which the booking class belongs
BKG_CLS_CD	DWL_BKG_CLS_H	BKG_CLS_CD	Direct mapping. This attribute stores the booking class ODT request to rename attribute from BOOKING CLASS to BOOKING CLASS CODE
BKG_CLS_DESC	DWL_BKG_CLS_H	BKG_CLS_DESC	Direct mapping. This indicates the booking class
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.

PKG_DWM_BKG_OFF Mapping

Table 6-26 shows the mapping to populate target table DWM_BKG_OFF. For more information, see BOOKING OFFICE.

Source Tables

DWR_BKG_OFF_H

DWR_SMS_AGNT_H

Table 6-26 PKG_DWM_ BKG_OFF ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
OFF_KEY	DWR_BKG_OFF_H	DWR_BKG_OFF_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
OFF_ID	DWR_BKG_OFF_H	OFF_ID	Direct mapping. It is the unique number generated at source system and the data flows from source to operational and then to derived layer. It helps to identify the record as unique throughout the system. That is from source to derived layer.
CITY_CD	DWR_BKG_OFF_H	CITY_CD	Direct mapping. The city code of the booking office and flows from operational to Derived layer.
CHNL_TYP	DWR_BKG_OFF_H	CHNL_TYP	Direct mapping. The types of channel of booking. Data flows from operational to Derived layer.
CORP_CD	DWR_BKG_OFF_H	CORP_CD	Direct mapping. The GDS ID of the booking office in the source system and flows from operational to Derived layer.
CTRY_CD	DWR_BKG_OFF_H	CTRY_CD	Direct mapping. The country code of the booking office and flows from operational to derived layer.
IATA_CD	DWR_BKG_OFF_H	IATA_CD	Direct mapping. The IATA code assigned to the office in the source system. Data flows from operational to derived layer.
TRUE_CITY_CD	DWR_BKG_OFF_H	TRUE_CITY_CD	Direct mapping. The true city code of the office and flows from operational to derived layer.
OFF_TYP	DWR_BKG_OFF_H	OFF_TYP	Direct mapping. The type of office in the source system and flows from operational to derived layer.
OFF_TYP_DESC	DWR_BKG_OFF_H	OFF_TYP_DESC	Direct mapping. The office type description in the source system and flows from operational to derived layer.
OFF_GRP	DWR_BKG_OFF_H	OFF_GRP	Direct mapping. The group to which the office belongs and flows from operational to derived layer.
OFF_NM	DWR_BKG_OFF_H	OFF_NM	Direct mapping. The name of the booking office in the source system and flows from operational to derived layer.
GDS_ID	DWR_BKG_OFF_H	CORP_CD	Direct mapping. The GDS ID of the booking office in the source system and flows from operational to derived layer.
GDS_NM	DWR_BKG_OFF_H	CORP_CD	Direct Mapping. The GDS name of the office and flows from operational to derived layer.	DECODE (LTRIM (RTRIM (DWR_BKG_OFF_H.CORP_CD)), pv_CORPORATE_CD1, pv_CORPORATE_CD2, pv_CORPORATE_CD3, pv_CORPORATE_CD4, pv_CORPORATE_CD5, pv_CORPORATE_CD6,pv_CORPORATE_CD7, pv_CORPORATE_CD8, pv_CORPORATE_CD9, pv_CORPORATE_CD10, pv_CORPORATE_CD11, pv_CORPORATE_CD12, pv_CORPORATE_CD13, pv_CORPORATE_CD14, pv_CORPORATE_CD15, pv_CORPORATE_CD12, pv_CORPORATE_CD11, pv_CORPORATE_CD12,pv_CORPORATE_CD16, pv_CORPORATE_CD17, pv_CORPORATE_CD18, pv_CORPORATE_CD19, NULL, pv_CORPORATE_CD20)
OFF_AGNT_TYP	DWR_BKG_OFF_H	OFF_AGNT_TYP	Direct mapping. It is the IATA agent type of the booking office and flows from operational to derived layer.
OFF_AGNT_TYP_DESC	DWR_BKG_OFF_H	OFF_AGNT_TYP_DESC	Direct mapping. It is the IATA agent type description of the booking office and flows from operational to derived layer.
AGNT_ROW_ID	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H.AGNT_ROW_ID	Left outer join is performed on the Agent table on the basis of Agent Iata Code to get the source system generated Agent ID.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_ROW_ID
AGNT_NM_LOCN	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_NM_LOCN	Left outer join is performed on the Agent table on the basis of agent IATA Code to get agent location.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_NM_LOCN
AGNT_HRCHY_LVL	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_HRCHY_LVL	Left outer join is performed on the Agent table on the basis of agent IATA Code to get agent hierarchy level.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_HRCHY_LVL
AGNT_CORPN	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_CORPN	Left outer join is performed on the Agent table on the basis of agent IATA Code to get corporation to which the agent belongs.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_CORPN
AGNT_CHAIN	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_CHAIN	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the agent chain information.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_CHAIN
AGNT_STS	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_STS	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the status of the agent.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_STS.
AGNT_STRTG	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_STRTG	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the strategy information of the agent.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_STRTG.
AGNT_STRTG_LVL	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_STRTG_LVL	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the agent strategy level.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_STRTG_LVL
AGNT_CRCY	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_CRCY	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the transaction currency of the agent.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_CRCY
AGNT_KEY_TYP	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_KEY_TYP	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the agent key type.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_KEY_TYP
AGNT_PSTL_OFF	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_PSTL_OFF	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the post office of the agent's address.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_PSTL_OFF
AGNT_ZIP	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_ZIP	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the zip code of the agent's address.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_ZIP
AGNT_CNTY_CD	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_CNTY_CD	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the county code of the agent's address.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_CNTY_CD
AGNT_CNTY	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_CNTY	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the county of the agent's address.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_CNTY
AGNT_PROVNCE	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_PROVNCE	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the province of the agent's address.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_PROVNCE
AGNT_STATE	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_STATE	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the state of the agent's address.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_STATE
AGNT_INDSTRY	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_INDSTRY	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the industry or line of business of the agent.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_INDSTRY
AGNT_DISTRICT	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_DISTRICT	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the district of the agent's address.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_DISTRICT
AGNT_REGN_CD	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_REGN_CD	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the region code of the agent's address.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_REGN_CD
AGNT_REGN	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_REGN	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the region of the agent's address.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_REGN
AGNT_ORGANIZATION	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_ORGANIZATION	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the organization of the agent.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_ORGANIZATION
AGNT_CONT_CD	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_CONT_CD	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the continent code of the agent's address.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_CONT_CD
AGNT_CONT	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_CONT	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the continent of the agent's address.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_CONT
AGNT_OFF_TYP	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_OFF_TYP	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the agent office type.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_OFF_TYP
AGNT_TERR	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_TERR	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the territory of the agent's address.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_TERR
AGNT_POSN	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_POSN	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the agent position.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_POSN
AGNT_CITY_CD	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_CITY_CD	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the city code of the agent's address.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_CITY_CD
AGNT_CITY	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_CITY	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the city of the agent's address.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_CITY
AGNT_CTRY_CD	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_CTRY_CD	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the country code of the agent's address.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_CTRY_CD
AGNT_CTRY	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_CTRY	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the country of the agent's address.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_CTRY
AGNT_IATA_CD	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_IATA_CD	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the agent IATA code.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_IATA_CD
AGNT_NM	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_NM	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the agent name.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_NM
AGNT_TYP	DWR_SMS_AGNT_H, DWR_BKG_OFF_H	DWR_SMS_AGNT_H. AGNT_TYP	Left outer join is performed on the Agent table on the basis of agent IATA Code to get the type of the agent.	Join performed between DWR_BKG_OFF_H and DWR_SMS_AGNT_H table on AGNT_IATA_CD column to fetch value for AGNT_TYP
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.

PKG_DWM_BKG_PAX Mapping

Table 6-27 shows the mapping to populate target table DWM_BKG_PAX. For more information, see BOOKING PASSENGER.

Source Tables

DWR_BKG_PAX _H

DWR_BKG_PAX_DOC_INFO_H

Table 6-27 PKG_DWM_ BKG_PAX ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
PAX_KEY	DWR_BKG_PAX _H	DWR_BKG_PAX_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
PAX_ID	DWR_BKG_PAX _H	PAX_ID	Direct Mapping. This is the primary key for the table generated by the source system
LAST_NM	DWR_BKG_PAX _H	LAST_NM	Direct Mapping. Last Name of Passenger
FST_NM	DWR_BKG_PAX _H	FST_NM	Direct Mapping. Passenger First Name
TYP_CD	DWR_BKG_PAX _H	TYP_CD	Direct Mapping. This indicates the type of passenger
STF_TYP	DWR_BKG_PAX _H	STF_TYP	Direct Mapping. This indicates the data will indicate whether staff is booked on standby or on confirmed basis
DOB	DWR_BKG_PAX _H	DOB	Direct Mapping.
IDFN_CD	DWR_BKG_PAX _H	IDFN_CD	Direct Mapping. "¡˚ID875¡± The Id code is a special type of passenger code used for entering an ID number for identification purposes.
GNDR	DWR_BKG_PAX _H	GNDR	Direct Mapping. This indicates the gender of the passenger
PAX_LAST_UPDT_TMSTMP	DWR_BKG_PAX _H	PAX_LAST_UPD_TMSTMP	Direct Mapping.
VIP_CARR_CD	DWR_BKG_PAX _H	VIP_CARR_CD	Direct Mapping. If passenger is a VIP, the carrier to which status applies to
VIP_FREE_TXT	DWR_BKG_PAX _H	VIP_FREE_TXT
CLID_NBR	DWR_BKG_PAX _H	CLID_NBR	Direct Mapping. This indicates the client identification number
CLID_CARR_CD	DWR_BKG_PAX _H	CLID_CARR_CD	Direct Mapping. Value is expected to come from the source input.
PAX_TYP	DWR_BKG_PAX _H	PAX_TYP	Direct Mapping. This indicates the type of passenger for this ticket
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.
TRVL_DOC_TYP	DWR_BKG_PAX_DOC_INFO_H	TRVL_DOC_TYP	Left outer join is performed on the Booking Passenger Document Information table on the basis of Passenger ID to get the Travel Document Type.	Join performed between DWR_BKG_PAX_H and DWR_BKG_PAX_DOC_INFO_H table on PAX_ID column to fetch value for TRVL_DOC_TYP
TRVL_DOC_NBR	DWR_BKG_PAX_DOC_INFO_H	TRVL_DOC_NBR	Left outer join is performed on the Booking Passenger Document Information table on the basis of Passenger ID to get the Travel Document Number.	Join performed between DWR_BKG_PAX_H and DWR_BKG_PAX_DOC_INFO_H table on PAX_ID column to fetch value for TRVL_DOC_NBR

PKG_DWM_BKG_TST Mapping

Table 6-28 shows the mapping to populate target table DWM_BKG_TST. For more information, see BOOKING TST.

Source Table

DWR_BKG_TST_H

Table 6-28 PKG_DWM_ BKG_TST ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
TST_KEY	DWR_BKG_TST_H	DWR_BKG_TST_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
TST_ID	DWR_BKG_TST_H	TST_ID	Direct Mapping. This indicates the primary key of the table assigned by source system and is called adsunique id of the entity
TRST_CRCY	DWR_BKG_TST_H	TRST_CRCY	Direct Mapping. This indicates the transitional currency code used in the BR field of the TST.
VLDATING_CARR_CD	DWR_BKG_TST_H	VALIDATING_CARR_CD	Direct Mapping. This refers to the carrier code for which the TST must be charged. This is taken from the FV element.
TST_NBR	DWR_BKG_TST_H	TST_NBR	Direct Mapping. This indicates the number of the TST.
ORGN	DWR_BKG_TST_H	ORGN	Direct Mapping. This indicates the origin airport code of the TST.
DEST	DWR_BKG_TST_H	DEST	Direct Mapping. This indicates the destination airport code of the TST
MNUL_IND	DWR_BKG_TST_H	MNUL_IND	Direct Mapping.
SALE_IND	DWR_BKG_TST_H	SALE_IND	Direct Mapping. This indicates the international sale indicator used for the TST:
ISS_IND	DWR_BKG_TST_H	ISS_IND	Direct Mapping. This indicates the issuance status of the TST:
OLD_TKT_NBR	DWR_BKG_TST_H	OLD_TKT_NBR	Direct Mapping.
ACTN_FLG	DWR_BKG_TST_H	ACTN_FLG	Direct Mapping. This indicates the action flag related to the TST. For instance:
FARE_ENDRSMNT	DWR_BKG_TST_H	FARE_ENDRSMNT	Direct Mapping.
PYMT_RESTRC	DWR_BKG_TST_H	PYMT_RESTRC	Direct Mapping.
TOUR_CD	DWR_BKG_TST_H	TOUR_CD	Direct Mapping. This indicates the fare print override element transmitted through an FY element
FARE_PRINT_OVRD	DWR_BKG_TST_H	FARE_PRINT_OVRD	Direct Mapping.
LAST_TKT_DT	DWR_BKG_TST_H	LAST_TKT_DT	Direct Mapping.
CMSN	DWR_BKG_TST_H	AGNT_CMSN	Direct Mapping. This indicates the commission (FM) associated to the priced segments and displayed in the commission field of the TST.
FARE_CALC_MODE	DWR_BKG_TST_H	FARE_CALC_MODE	Direct Mapping.
FARE_CALC	DWR_BKG_TST_H	FARE_CALC	Direct Mapping.
FORM_OF_PYMT	DWR_BKG_TST_H	FORM_OF_PYMT	Direct Mapping. This indicates the FP element associated to the priced segments and displayed as FP in the TST display.
TKT_TYP	DWR_BKG_TST_H	TKT_TYP	Direct Mapping. This indicates Ticket type returned from Fare quote. Can be Electronic ticket (E), paper ticket (P).
BKG_TMSTMP	DWR_BKG_TST_H	BKG_TMSTMP	Direct Mapping.
TYP	DWR_BKG_TST_H	TYP	Direct Mapping. This indicates the TST is related to an INF passenger. The TYP is INF if the passenger that the TST refers to is an INF type code passenger. The TYP is ADT for any other passenger type codes.
BNKR_RATE1	DWR_BKG_TST_H	BNKR_RATE1	Direct Mapping.
Bnkr_Rate2	DWR_BKG_TST_H	Bnkr_Rate2	Direct Mapping.
TST_AGNT_SIGN	DWR_BKG_TST_H	TST_AGNT_SIGN	Direct Mapping. This indicates the Agent sign who gives the sign of the agent that made the last update of the TST
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.

PKG_DWM_CARR Mapping

Table 6-29 shows the mapping to populate target table DWM_CARR. For more information, see CARRIER.

Source Table

DWR_CARR_H

Table 6-29 PKG_DWM_CARR ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
CARR_KEY	DWR_CARR_H	DWR_CARR_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
CARR_TYP	DWR_CARR_H	CARR_TYP_ID	Direct Mapping. This indicates the type of the carrier whether Airplane, Rail, Ship, Bus, and so on.
CARR_CD	DWR_CARR_H	CARR_CD_SRC	Direct Mapping. This indicates the short name assigned to the carrier
CARR_DESC	DWR_CARR_H	CARR_DESC	Direct Mapping. This indicates the long name assigned to the carrier
CARR_ID	DWR_CARR_H	CARR_ID	Direct Mapping. This indicates the system generated unique assigned to the attribute in the operational layer
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.

PKG_DWM_CORP_CUST Mapping

Table 6-30 shows the mapping to populate target table DWM_CORP_CUST. For more information, see CORPORATE CUSTOMER.

Source Table

DWR_SMS_CUST_H

Table 6-30 PKG_DWM_CORP_CUST ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
CORP_CUST_KEY	DWR_SMS_CUST_H	DWR_SMS_CUST_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
CUST_ROW_ID	DWM_CORP_CUST	CUST_ROW_ID	Left outer join is performed on the Corporate Customer table on the basis of Customer Row Identifier to get the Customer Row Identifier values	Join performed between DWM_CORP_CUST and DWR_SMS_CUST_H table on CUST_ROW_ID column to fetch value for CUST_ROW_ID
CUST_NM	DWR_SMS_CUST_H	CUST_NM	Direct Mapping.
CUST_NM_LOCN	DWR_SMS_CUST_H	CUST_NM_LOCN	Direct Mapping.
CUST_CLNT_CD	DWR_SMS_CUST_H	CUST_CLNT_CD	Direct Mapping.
CUST_HRCHY_LVL	DWR_SMS_CUST_H	CUST_HRCHY_LVL	Direct Mapping.
CUST_TYP	DWR_SMS_CUST_H	CUST_TYP	Direct Mapping. This indicates the corporate customer type
CUST_CORPN	DWR_SMS_CUST_H	CUST_CORPN	Direct Mapping. This indicates the corporation details
CUST_DIV_NM	DWR_SMS_CUST_H	CUST_DIV_NM	Direct Mapping. This indicates the division details of corporation
CUST_STS	DWR_SMS_CUST_H	CUST_STS	Direct Mapping. This indicates the customer status of the corporate customer
CUST_STRTG	DWR_SMS_CUST_H	CUST_STRTG	Direct Mapping. This indicates the corporate customer strategy information
CUST_STRTG_LVL	DWR_SMS_CUST_H	CUST_STRTG_LVL	Direct Mapping. This indicates the customer hierarchy level.
CUST_CRCY_CD	DWR_SMS_CUST_H	CUST_CRCY_CD	Direct Mapping. This indicates the currency of the corporate customer
CUST_KEY_TYP	DWR_SMS_CUST_H	CUST_KEY_TYP	Direct Mapping. This attribute stores the key type of the corporate customer
CUST_CITY	DWR_SMS_CUST_H	CUST_CITY	This indicates the city of the corporate customer
CUST_PSTL_OFF	DWR_SMS_CUST_H	CUST_PSTL_OFF	Direct Mapping. This indicates the postal office of the customer
CUST_ZIP	DWR_SMS_CUST_H	CUST_ZIP	Direct Mapping. This indicates the zip code of the customer address
CUST_CNTY	DWR_SMS_CUST_H	CUST_CNTY	Direct Mapping. This indicates the county to which the customer belongs
CUST_PROVNCE	DWR_SMS_CUST_H	CUST_PROVNCE	Direct Mapping. This indicates the province of the customer
CUST_STATE	DWR_SMS_CUST_H	CUST_STATE	Direct Mapping. This indicates the state of the customer
CUST_CTRY	DWR_SMS_CUST_H	CUST_CTRY	Direct Mapping. This indicates the country of the customer
CUST_INDSTRY	DWR_SMS_CUST_H	CUST_INDSTRY	Direct Mapping. This indicates the industry and line of business of the customer
CUST_DISTRICT	DWR_SMS_CUST_H	CUST_DISTRICT	Direct Mapping. This indicates the district of the customer and populated where applicable.
CUST_REGN	DWR_SMS_CUST_H	CUST_REGN	Direct Mapping. This indicates the region of the customer
CUST_ORGANIZATION	DWR_SMS_CUST_H	CUST_ORGANIZATION	Direct Mapping. This indicates the organization. That is, the country of the customer
CUST_CONT	DWR_SMS_CUST_H	CUST_CONT	Direct Mapping. This indicates the continent of the customer.
CUST_TERR	DWR_SMS_CUST_H	CUST_TERR	Direct Mapping. This indicates the customer territory
CUST_POSN	DWR_SMS_CUST_H	CUST_POSN	Direct Mapping. This indicates the customer position
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.

PKG_DWM_FLT Mapping

Table 6-31 shows the mapping to populate target table DWM_FLT. For more information, see FLIGHT.

Source Tables

DWR_FLT_H

DWM_ROUTES

Table 6-31 PKG_DWM_FLT ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
FLT_KEY	DWR_FLT_H	FLT_KEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
VHCL_ID	DWR_FLT_H	FLT_ID	Direct Mapping.
FLT_NBR	DWR_FLT_H	FLT_NBR	Direct Mapping. This indicates the flight number of the carrier.
ALPHASFX	DWR_FLT_H	ALPHA_SFX	Direct Mapping. This indicates the alphasuffix
ELCTRNC_TKT_IND	DWR_FLT_H	ELCTRNC_TKT_IND	Direct Mapping. This indicates whether E-ticket can be issued for the flight date of flight, if flight traverses multiple dates, then date of the first leg is considered
STS	DWR_FLT_H	FLT_STS	Direct Mapping. This indicates the flight status
INTNL_DOM_FLG	DWR_FLT_H	FLT_TYP	Direct Mapping. This indicates the type of flight whether international and domestic I for International D Domestic
TYP_HAUL	DWR_FLT_H	TYP_HAUL	Direct Mapping. This indicates the type of Haul for the flight
TRAF_CATG_ID	DWM_ROUTES	TRAF_CATG_ID	Left outer join is performed on the Routes table on the basis of Flight Number to get the Traffic Category ID	Join performed between DWM_ROUTES and DWR_FLT_H table on FLT_NBR column to fetch value for TRAF_CATG_ID
CDSH_TYP	DWR_FLT_H	CDSH_TYP	Direct Mapping. This indicates the type of the flight in a codeshare
FRNCHS_PTNR	DWR_FLT_H	FRNCHS_PTNR	Direct Mapping. This indicates airline carrier code of the partner in a franchise agreement.
CARR_CD	DWR_FLT_H	CARR_CD	Direct Mapping. This indicates the carrier of the flight
SCNDRY_CARR_CD	DWR_FLT_H	SCNDRY_CARR_CD	Direct Mapping. This indicates the secondary carrier of the flight
FLT_TXT_DESC	DWR_FLT_H	FLT_TXT_DESC	This attribute indicates the concatenation of the carrier code and the flight number
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
FLT_ID	DWR_FLT_H	FLT_ID	DIRECT MAPPING.

PKG_DWM_FRQTFLR Mapping

Table 6-32 shows the mapping to populate target table DWM_FRQTFLR. For more information, see FREQUENT FLYER.

Source Tables

DWR_FRQTFLR_H

DWM_GEOGRY

Table 6-32 PKG_DWM_FRQTFLR ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
FRQTFLR_CARD_KEY	DWR_FRQTFLR_H	DWR_FRQTFLR_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
FRQTFLR_NBR	DWR_FRQTFLR_H	FRQTFLR_NBR	Direct Mapping. This indicates the Frequent flier number
CARD_CARR	DWR_FRQTFLR_H	CARD_CARR	Direct Mapping. This indicates the carrier to which the card/member belongs to
CARR_CD	DWR_FRQTFLR_H	CARR_CD	Direct Mapping. Carrier to whom the number is associated to for a particular booking.
RQST_TYP	DWR_FRQTFLR_H	RQST_TYP	Direct Mapping. This indicates the SSR request:
STS_CD	DWR_FRQTFLR_H	STS_CD	Direct Mapping.
AIRL_MBSHP_LVL	DWR_FRQTFLR_H	AIRL_MBSHP_LVL	Direct Mapping. This indicates the airline memberTier level. Plus, Platinum, and so on.
AIRL_PRORTY_CD	DWR_FRQTFLR_H	AIRL_PRORTY_CD	Direct Mapping. This indicates the airline defined priority code for the FFP based on various criteria
AIRL_TIER_DESC	DWR_FRQTFLR_H	AIRL_TIER_DESC	Direct Mapping. This indicates the Description of tier levels
AIRL_CUST_VAL	DWR_FRQTFLR_H	AIRL_CUST_VAL	Direct Mapping. This indicates the airline defined value for the customer.
ALAN_MBR_LVL	DWR_FRQTFLR_H	ALAN_MBR_LVL	Direct Mapping. This indicates the airline memberTier level. Plus, Platinum, and so on.
ALAN_TIER_DESC	DWR_FRQTFLR_H	ALAN_TIER_DESC	Direct Mapping. This indicates the Description of tier levels
CERT_NBR	DWR_FRQTFLR_H	CERT_NBR	Direct Mapping. This indicates the certificate number of the frequent flyer
ALANC_CD	DWR_FRQTFLR_H	ALANC_CD	Direct Mapping. This indicates the alliance code
STK_CNTRL_NBR	DWR_FRQTFLR_H	STK_CNTRL_NBR	Direct Mapping. This indicates the stock control number associated with the frequent flyer
CLS_BEF_UPGRD	DWR_FRQTFLR_H	PAX_CLS_BEF_UPGRD	Direct Mapping.
MLS_CR_IND	DWR_FRQTFLR_H	MLS_CRDTD_IND	Direct Mapping.
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.
CITY_KEY	DWM_GEOGRY	CITY_KEY	Left outer join is performed on the Geography table on the basis of City Code to get the City Key	Join performed between DWM_GEOGRY and DWR_FRQTFLR_H table on CITY_CD column to fetch value for City Key
LYLTY_PRG_ID	DWR_FRQTFLR_H	LYLTY_PRG_ID	Direct Mapping.
FRQTFLR_CARD_ID	DWR_FRQTFLR_H	FRQTFLR_CARD_ID	Direct Mapping.
LYLTY_LVL_ID	DWR_FRQTFLR_H	LYLTY_LVL_ID	Direct Mapping.
ACCT_ID	DWR_FRQTFLR_H	ACCT_ID	Direct Mapping.
ACCT_OPEN_DT	DWR_FRQTFLR_H	ACCT_OPEN_DT	Direct Mapping.
ACCT_CLOSE_DT	DWR_FRQTFLR_H	ACCT_CLOSE_DT	Direct Mapping.
ACCT_EXPRY_DT	DWR_FRQTFLR_H	ACCT_EXPR_DT	Direct Mapping.
ACCT_RNWL_DT	DWR_FRQTFLR_H	ACCT_RNW_DT	Direct Mapping.
DOB	DWR_FRQTFLR_H	DOB	Direct Mapping.
GNDR	DWR_FRQTFLR_H	GNDR	Direct Mapping.
INCM_LVL	DWR_FRQTFLR_H	INCM_LVL	Direct Mapping.
MRTL_STS	DWR_FRQTFLR_H	MRTL_STS	Direct Mapping.
EDU	DWR_FRQTFLR_H	EDU	Direct Mapping.
OCCUPTN	DWR_FRQTFLR_H	OCCUPTN	Direct Mapping.

PKG_DWM_INTRATN_RSLT Mapping

Table 6-33 shows the mapping to populate target table DWM_INTRATN_RSLT. For more information, see INTERACTION RESULT.

Source Table

DWL_INTRATN_RSLT_H

Table 6-33 PKG_DWM_INTRATN_RSLT ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description
INTRATN_RSLT_KEY	DWL_INTRATN_RSLT_H	DWL_INTRATN_RSLT_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
INTRATN_RSLT_ID	DWL_INTRATN_RSLT_H	INTRATN_RSLT_ID	Direct Mapping.
INTRATN_RSLT_NM	DWL_INTRATN_RSLT_H	INTRATN_RSLT_NM	Direct Mapping.
INTRATN_RSLT_DESC	DWL_INTRATN_RSLT_H	INTRATN_RSLT_DESC	Direct Mapping.
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.

PKG_DWM_INTRATN_RSN Mapping

Table 6-34 shows the mapping to populate target table DWM_INTRATN_RSN. For more information, see INTERACTION REASON.

Source Table

DWL_INTRATN_RSN_H

Table 6-34 PKG_DWM_INTRATN_RSN ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
INTRATN_RSN_ID	DWL_INTRATN_RSN_H	INTRATN_RSN_ID	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
INTRATN_RSN_NM	DWL_INTRATN_RSN_H	INTRATN_RSN_NM	Direct Mapping.
INTRATN_RSN_DESC	DWL_INTRATN_RSN_H	INTRATN_RSN_DESC	Direct Mapping.
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.

PKG_DWM_LEG Mapping

Table 6-35 shows the mapping to populate target table DWM_LEG. For more information, see LEG.

Source Tables

DWR_LEG_H

DWM_AIP

Table 6-35 PKG_DWM_LEG ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
LEG_KEY	DWR_LEG_H	DWR_LEG_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
DEPTR_TRML	DWR_LEG_H	DEPTR_TRML	Direct Mapping.
ARVL_TRML	DWR_LEG_H	ARVL_TRML	Direct Mapping. This indicates the terminal to where the flight arrives into
ARVL_AIP_NM	DWR_LEG_H	ARVL_AIP_NM	Direct Mapping. This indicates the arrival airport name which is same as the off point
DEPTR_AIP_NM	DWR_LEG_H	DEPTR_AIP_NM	Direct Mapping. This indicates the departure airport name which is same as the board point
DEPTR_CITY	DWM_AIP	DEPTR_CITY	Left outer join is performed on the Airport table on the basis of Airport ID to get the Departure City	Join performed between DWM_AIP and DWR_LEG_H table on AIP_ID and AIP_NM column to fetch value for DEPTR_CITY
DEPTR_CTRY	DWM_AIP	DEPTR__CTRY	Left outer join is performed on the Airport table on the basis of Airport ID to get the Departure Country	Join performed between DWM_AIP and DWR_LEG_H table on AIP_ID and AIP_NM column to fetch value for DEPTR_CTRY
DEPTR_REGN	DWM_AIP	DEPTR__REGN	Left outer join is performed on the Airport table on the basis of Airport ID to get the Departure Region	Join performed between DWM_AIP and DWR_LEG_H table on AIP_ID and AIP_NM column to fetch value for DEPTR_REGN
DEPTR_CONT	DWM_AIP	DEPTR__CONT	Left outer join is performed on the Airport table on the basis of Airport ID and Airport name to get the Departure Continent	Join performed between DWM_AIP and DWR_LEG_H table on AIP_ID and AIP_NM column to fetch value for DEPTR_CONT
ARVL_CITY	DWM_AIP	ARVL_CITY	Left outer join is performed on the Airport table on the basis of Airport ID and Airport name to get the Arrival City	Join performed between DWM_AIP and DWR_LEG_H table on AIP_ID and AIP_NM column to fetch value for ARVL_CITY
ARVL_CTRY	DWM_AIP	ARVL__CTRY	Left outer join is performed on the Airport table on the basis of Airport ID and Airport name to get the Arrival Country	Join performed between DWM_AIP and DWR_LEG_H table on AIP_ID and AIP_NM column to fetch value for ARVL_CTRY
ARVL_REGN	DWM_AIP	ARVL__REGN	Left outer join is performed on the Airport table on the basis of Airport ID and Airport name to get the Arrival Region	Join performed between DWM_AIP and DWR_LEG_H table on AIP_ID and AIP_NM column to fetch value for ARVL_REGN
ARVL_CONT	DWM_AIP	ARVL__CONT	Left outer join is performed on the Airport table on the basis of Airport ID and Airport name to get the Arrival Continent	Join performed between DWM_AIP and DWR_LEG_H table on AIP_ID and AIP_NM column to fetch value for ARVL_CONT
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.
LEG_ID	DWR_LEG_H	LEG_ID	Direct Mapping.

PKG_DWM_LYLTY_LVL Mapping

Table 6-36 shows the mapping to populate target table DWM_LYLTY_LVL. For more information, see LOYALTY LEVEL.

Source Table

DWR_LYLTY_LVL_H

Table 6-36 PKG_DWM_LYLTY_LVL ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
LYLTY_LVL_KEY	DWR_LYLTY_LVL_H	DWR_LYLTY_LVL_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
LYLTY_LVL_ID	DWR_LYLTY_LVL_H	LYLTY_LVL_ID	Direct Mapping.
LYLTY_LVL_NM	DWR_LYLTY_LVL_H	LYLTY_LVL_NM	Direct Mapping.
LVL_QLFYNG_STRT_PNTS	DWR_LYLTY_LVL_H	LVL_QLFYNG_STRT_PNTS	Direct Mapping.
LYLTY_PRG_ID	DWR_LYLTY_LVL_H	LYLTY_PRG_ID	Direct Mapping.
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO ¡	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	®9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.

PKG_DWM_PDI_CHNL Mapping

Table 6-37 shows the mapping to populate target table DWM_PDI_CHNL. For more information, see PDI CHANNEL.

Source Table

DWL_PDI_CHNL_H

Table 6-37 PKG_DWM_PDI_CHNL ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
CHNL_KEY	DWL_PDI_CHNL_H	DWL_PDI_CHNL_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
CHNL_ID	DWL_PDI_CHNL_H	CHNL_ID	Direct Mapping. This indicates the unique identifier assigned to the channel in the operational layer of the
ACCPTNCE_CHNL_TYP	DWL_PDI_CHNL_H	ACCPTNCE_CHNL_TYP	Direct Mapping. This indicates the check-in channel origin.
ACCPTNCE_CHNL_ORGN	DWL_PDI_CHNL_H	ACCPTNCE_CHNL_ORGN	Direct Mapping. This indicates the check-in channel origin.
ACCPTNCE_CHNL_TYP_DESC	DWL_PDI_CHNL_H	ACCPTNCE_CHNL_TYP_DESC	Direct Mapping. This indicates the application type of the check-in channel.
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.

PKG_DWM_SALES_CHNL Mapping

Table 6-38 shows the mapping to populate target table DWM_SALES_CHNL. For more information, see SALES CHANNEL.

Source Table

DWR_BKG_OFF_H

Table 6-38 PKG_DWM_SALES_CHNL ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
SALES_CHNL_KEY	DWL_SALES_CHNL_H	DWL_SALES_CHNL_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
SALES_CHNL_CD	DWL_SALES_CHNL_H	SALES_CHNL_CD	Direct mapping This stores the sales channel code Data flows from operational to derived layer.
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.

PKG_DWM_SEG Mapping

Table 6-39 shows the mapping to populate target table DWM_SEG. For more information, see SEGMENT.

Source Tables

DWR_SEG_H

DWM_AIP

Table 6-39 PKG_DWM_SEG ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
SEG_KEY	DWR_SEG_H	DWR_SEG_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
SEG_TYP	DWR_SEG_H	SEG_TYP	Direct Mapping. This indicates the segment type for the flight
LAST_CHECK_TM_LCL	DWR_SEG_H	LAST_CHKIN_TM_LCL	Direct Mapping.
BRD_AIP_NM	DWM_AIP	BP_AIP_NM	Left outer join is performed on the Airport table on the basis of Airport ID and Airport name to get the Board Point Airport name	Join performed between DWM_AIP and DWR_SEG_H table on AIP_ID and AIP_NM column to fetch value for BP_AIP_NM
OFPNT_AIP_NM	DWM_AIP	lkp_op_AIP_NM	Left outer join is performed on the Airport table on the basis of Airport ID and Airport name to get the OFF Point Airport name	Join performed between DWM_AIP and DWR_SEG_H table on AIP_ID and AIP_NM column to fetch value for lkp_op_AIP_NM
BP_CITY	DWM_AIP	lkp_bp_CITY	Left outer join is performed on the Airport table on the basis of Airport ID and Airport name to get the Board Point City	Join performed between DWM_AIP and DWR_SEG_H table on AIP_ID and AIP_NM column to fetch value for lkp_bp_CITY
BP_CTRY	DWM_AIP	lkp_bp_CTRY	Left outer join is performed on the Airport table on the basis of Airport ID and Airport name to get the Board Point Country	Join performed between DWM_AIP and DWR_SEG_H table on AIP_ID and AIP_NM column to fetch value for lkp_bp_CTRY
OFPNT_CTRY	DWM_AIP	lkp_op_CTRY	Left outer join is performed on the Airport table on the basis of Airport ID and Airport name to get the Off Point Country	Join performed between DWM_AIP and DWR_SEG_H table on AIP_ID and AIP_NM column to fetch value for lkp_op_CTRY
OFPNT_CITY	DWM_AIP	lkp_op_CITY	Left outer join is performed on the Airport table on the basis of Airport ID and Airport name to get the Off Point City	Join performed between DWM_AIP and DWR_SEG_H table on AIP_ID and AIP_NM column to fetch value for lkp_op_CITY
BP_REGN	DWM_AIP	lkp_bp_REGN	Left outer join is performed on the Airport table on the basis of Airport ID and Airport name to get the Board Point Region	Join performed between DWM_AIP and DWR_SEG_H table on AIP_ID and AIP_NM column to fetch value for lkp_bp_REGN
OFPNT_REGN	DWM_AIP	lkp_op_REGN	Left outer join is performed on the Airport table on the basis of Airport ID and Airport name to get the OFF Point Region	Join performed between DWM_AIP and DWR_SEG_H table on AIP_ID and AIP_NM column to fetch value for lkp_op_REGN
BP_CONT	DWM_AIP	lkp_bp_CONT	Left outer join is performed on the Airport table on the basis of Airport ID and Airport name to get the Board Point Continent	Join performed between DWM_AIP and DWR_SEG_H table on AIP_ID and AIP_NM column to fetch value for lkp_bp_CONT
OFPNT_CONT	DWM_AIP	lkp_op_CONT	Left outer join is performed on the Airport table on the basis of Airport ID and Airport name to get the Off Point Continent	Join performed between DWM_AIP and DWR_SEG_H table on AIP_ID and AIP_NM column to fetch value for lkp_op_CONT
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.
SEG_ID	DWR_SEG_H	SEG_ID	Direct Mapping.

PKG_DWM_SEG_PAIR Mapping

Table 6-40 shows the mapping to populate target table DWM_SEG_PAIR. For more information, see SEGMENT PAIR.

Source Table

DWR_SEG_H

Table 6-40 PKG_DWM_SEG_PAIR ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
SEG_PAIR_KEY	DWR_SEG_H	DWR_SEG_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
SEG_PAIR_NM	DWR_SEG_H	SEG_PAIR	Direct Mapping. This indicates the segment pair details.
BP	DWR_SEG_H	BOARD_POINT	Direct Mapping. This indicates the board point of the segment pair.
OFPNT_CD	DWR_SEG_H	OFF_POINT	Direct Mapping. This indicates the off point of the segment pair.
PTP	DWR_SEG_H	PTP	Direct Mapping. This indicates the details of PTP (Point to Point). Boardpoint Offpoint
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.
SEG_PAIR_ID	DWR_SEG_H	SEG_ID	Direct Mapping.

PKG_DWM_SVC Mapping

Table 6-41 shows the mapping to populate target table DWM_SVC. For more information, see SERVICE.

Source Table

DWR_SVC_H

Table 6-41 PKG_DWM_SVC ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
SVC_KEY	DWR_SVC_H	DWR_SVC_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
SVC_NM	DWR_SVC_H	SVC_NM	Direct Mapping. This indicates Service Name, like Reservation&Tkt, Staff Reservation&Tkt, Check-In, Boarding, Lounge, and so on.
SVC_ID	DWR_SVC_H	SVC_ID	Direct Mapping.
SVC_DESC	DWR_SVC_H	SVC_DESC	Direct Mapping. This indicates Service description
SVC_TYP_CD	DWR_SVC_H	SVC_TYP_CD	Direct Mapping. This indicates Service type code
SVC_TYP_DESC	DWR_SVC_H	SVC_TYP_DESC
SVC_TYP_NM	DWR_SVC_H	SVC_TYP_NM	Direct Mapping. This indicates Service Type Name like Sales, Pre-Flight and on-board, and so on.
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.

PKG_DWM_TRAF_CATG Mapping

Table 6-42 shows the mapping to populate target table DWM_TRAF_CATG. For more information, see TRAFFIC CATEGORY.

Source Table

DWL_TRAF_CATG_H

Table 6-42 PKG_DWM_TRAF_CATG ETL Source to Target Mapping

Column Name	Source Table Name	Source Column Name	Transformation Description	Comments (Formula If Any)
TRAF_CATG_KEY	DWL_TRAF_CATG_H	DWL_TRAF_CATG_H_SKEY	Direct mapping. It is the surrogate key generated at operational layer. The unique number helps to keep the data integrity between the operational and derived layer.
CATG_TYP	DWL_TRAF_CATG_H	CATG_TYP	Direct mapping This stores the category type for the Traffic category. Data flows from operational to derived layer.
LVL_1	DWL_TRAF_CATG_H	LVL1	Direct mapping. This stores the level 1of the traffic category. Data flows from operational to derived layer.
LVL_2	DWL_TRAF_CATG_H	LVL2	Direct mapping. This stores the level 2 of the traffic category. Data flows from operational to derived layer
LVL_3	DWL_TRAF_CATG_H	LVL3	Direct mapping. This stores the level 3 of the traffic category. Data flows from operational to derived layer
LVL_4	DWL_TRAF_CATG_H	LVL4	Direct mapping. This stores the level 4 of the traffic category. Data flows from operational to derived layer
LVL_5	DWL_TRAF_CATG_H	LVL5	Direct mapping. This stores the level 5 of the traffic category. Data flows from operational to derived layer
CALC_MONTH	DWL_TRAF_CATG_H	CALCD_MONTH	Direct mapping. This store the calculation month of the traffic category. Data flows from operational to derived layer
CALC_YR	DWL_TRAF_CATG_H	CALCD_YR	Direct mapping. This stores the calculation year of the traffic category calculation month. Data flows from operational to derived layer
TRAF_CATG_CD	DWL_TRAF_CATG_H	TRAF_CATG_CD	Direct mapping
TRAF_CATG_ID	DWL_TRAF_CATG_H	TRAFIC_CATG_ID	Direct mapping. This stores the primary key assigned by the source system. Data flows from operational to derived layer
DWFEED_ID	DWR_ACCT_H	DWFEED_ID	Direct mapping. It is the identifier of the data warehouse feed used to populate the load cycle.
SRC_SYS_ID	DWR_ACCT_H	SRC_SYS_ID	Direct mapping. It is the identifier of the source system.
SRC_SYS_CRTD_TMSTMP	DWR_ACCT_H	SRC_SYS_CRTD_TMSTMP	Direct mapping. It is the timestamp of the source system when the respective was generated in the source system
SRC_SYS_UPD_TMSTMP	DWR_ACCT_H	SRC_SYS_UPD_TMSTMP	Direct mapping. It is the timestamp of source system when the respective record was updated in the source system.
SRC_SYS_DEL_IND	DWR_ACCT_H	SRC_SYS_DEL_IND	Direct mapping. It is the delete flag that indicates the record is deleted in the source system.	'Y' if deleted or 'N' if not deleted
ETL_BATCH_ID	DWC_JOB_PARM	pv_ETL_BATCH_ID	It is the sequence of the load cycle in which the records are inserted / updated in the table.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_BY	DWC_JOB_PARM	pv_ETL_BATCH_CRE_BY	It is the name of the source system which created and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_CRTD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_CRE_TMSTMP	It is the current timestamp when a record is created as active record or present record.	The active flag i.e CURR_STS ='Y' or SRC_SYS_DEL_IND ='N'
ETL_BATCH_UPD_BY	DWC_JOB_PARM	pv_ETL_BATCH_UPD_BY	It is the name of the source system which updated and executed this load cycle.	Values are passed as parameterized from DWC_JOB_PARM
ETL_BATCH_UPD_TMSTMP	DWC_JOB_PARM	pv_ETL_BATCH_UPD_TMSTMP	It is the current timestamp when a record is updated as inactive record or deleted record.	The active flag i.e CURR_STS ='N' or SRC_SYS_DEL_IND ='Y'
DATA_MVT_STS_CD	DWC_JOB_PARM	pv_DATA_MVT_STS_CD	It is the status information of the Data movement from the source system. That is, the data is New or the data is processed.	P = Processed or N = New
VLD_FRM	DWR_ACCT_H	VLD_FRM	Direct mapping. It is the timestamp of the source system when the records was valid from in the load cycle.	Current Date
VLD_UPTO	DWR_ACCT_H	VLD_UPTO	Direct mapping. It is the timestamp of the source system when the records was valid upto in the load cycle.	'9999-12-31' in case of latest record and Current Date ¨C 1 in case of expirey record
CURR_STS	DWR_ACCT_H	CURR_STS	Direct mapping. It is the current status of the records in the load cycle from the source system where it is active or inactive.	Y = Active or N = Inactive.
ROUTE_ID	DWL_TRAF_CATG_H	ROUTE_ID	Direct mapping Data flows from operational to derived layer
FLT_NBR	DWL_TRAF_CATG_H	FLT_NBR	Direct mapping Data flows from operational to derived layer
CITY_PAIR_ID	DWL_TRAF_CATG_H	CITY_PAIR_ID	Direct mapping Data flows from operational to derived layer
MKTG_AREA_ID	DWL_TRAF_CATG_H	MKTG_AREA_ID	Direct mapping Data flows from operational to derived layer
GEO_AREA_ID	DWL_TRAF_CATG_H	GEO_AREA_ID	Direct mapping Data flows from operational to derived layer
ROUTE_PAIR_ID	DWL_TRAF_CATG_H	ROUTE_PAIR_ID	Direct mapping Data flows from operational to derived layer
MKTG_AREA_NM	DWL_TRAF_CATG_H	MKTG_AREA_NM	Direct mapping Data flows from operational to derived layer

Intra-ETL Process Flows

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, aggregate, and dimension 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_Dimension - Populates the dimension (DWM_) tables based on the content of the reference (DWR_) tables.
Populate_Derived - Populates the derived (DWD_) tables based on the content of the base (DWB_) tables.
Populate_Aggregate - Refreshes all of the aggregate (DWA_) tables using data from the dimension (DWM_) and derived (DWD_) tables.
Populate_Aw - Loads data from Oracle Communications Data Model aggregate (DWA_) tables into the Oracle Communications Data Model Analytical Workspace and calculates the forecast data. It reads OLAP ETL parameters from DWC_OLAP_ETL_PARM table.
Populate_MINING - This sub-process flow triggers the data mining models.

Figure 6-1 shows the top-level Intra-ETL process flow.

Figure 6-1 Oracle Airlines Data Model Main Intra-ETL Process Flow