Sample ETL Mapping Sheet

Legend

Oracle Table

Teradata Source Stage Table

Flat File

Teradata Interface Table Teradata Fact Stage Table

Lookup table

AUDIT_PowerSuite_FW23-2007.csv

DEL_INS

1

Flat File QLT_SRC_COLMN_D

GCD_DAT_SORC_D

Audit-Regulatory Inspections.csv

DEL_INS

2


GCD_DAT_SORC_D


DEL_INS

2


GCD_DAT_SORC_D

TCS Confidential

Table :

QLT_COMPLNT_AUDT_I

Falt File to InterFace table Step 1:

From Falt File to InterFace 1.Data will be deleted from Interface table before Insertion of data by using Bteq(Respcted Sorcedata based on source name 2.Informatica Mappings are used to load the Data from Flat File to Interface table From Falt File to Inter Face

Step 2:

1.Data is Inserted from csv files. 2.Informatica Mappings are used to load this Data

Step 3:

From Falt File to Inter Face 1.Data will be deleted from Interface table before Insertion of data by using Bteq . 2.Informatica Mappings are used to load the Data from Flat File to Interface table.

TCS Confidential

TCS Confidential

TCS Confidential

Teradata Fact Table Informatica Lookup BTEQ

ODS (Teradata)

QLT_COMPLNT_AU DT_I

TCS Confidential

espcted Sorcedata based on source name) .

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

Folder Name

TYP_Typhon_Sourcing_Datamart

WorkFlow Name

wkf_s_m_WL_QLT_COMPLNT_AUDT_I_PS_INS

Session Name

s_m_WL_QLT_COMPLNT_AUDT_I_PS_INS

Mapping Name

m_WL_QLT_COMPLNT_AUDT_I_PS_INS

Source Tables

Database

Target Tables

Database SRC_ETL_TARGET ( Teradata )

Insert

Load Type

n/a

Join Condition

SQ Query

TARGET System

Table/Dimension/Fact

SRC_ETL_TARGET QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I

TCS Confidential

QLT_COMPLNT_AUDT_I

TCS Confidential

TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_COMPLNT_AUDIT_I_REG_INSP_INS

Session Name

s_m_WL_QLT_COMPLNT_AUDIT_I_REG_INSP_INS

Mapping Name

m_WL_QLT_COMPLNT_AUDIT_I_REG_INSP_INS

Source Tables

Database

Target Tables


Load Type Join Condition

TCS Confidential

Insert n/a

TARGET System SRC_ETL_TARGET

Table/Dimension/Fact QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I

TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_COMPLNT_AUDIT_I_GAC_MANUAL_INS

Session Name

s_m_WL_QLT_COMPLNT_AUDIT_I_GAC_MANUAL_INS

Mapping Name

m_WL_QLT_COMPLNT_AUDIT_I_GAC_MANUAL_INS

Source Tables

Database

Target Tables


Insert

Load Type

n/a

Join Condition

TARGET System SRC_ETL_TARGET

Table/Dimension/Fact QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I

TCS Confidential

QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I

TCS Confidential

File name AUDIT_PowerSuite_FW23-2007.csv

Table name QLT_COMPLNT_AUDT_I

TRANSFORMATION

SPECIFICATIONS

TARGET Column Name

Data Type

Length

SRC_SYS_ID

VARCHAR

100

SRC_NM

VARCHAR

50

SITE

VARCHAR

50

MODALITY

VARCHAR

50

P_L SRC_COLMN

VARCHAR VARCHAR

50 50

SRC_COLMN_VAL

VARCHAR

50

SRC_COLMN_DT

DATE

9

STC_UPDT_ID

VARCHAR

50

SRC_CRETN_ID

VARCHAR

50

SRC_UPD_TS

TIMESTAMP

0

SRC_CRETN_TS

TIMESTAMP

0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

51

ODS_POSTNG_TS

TIMESTAMP

0

TCS Confidential

PK

FK

System

ODS_UPD_TS

TCS Confidential

TIMESTAMP

0

TCS Confidential

File name Audit-Regulatory Inspections.csv


TCS Confidential

TRANSFORMATIONSPECIFICATIONS

TARGET Column Name

Data Type

Length

SRC_SYS_ID

VARCHAR

100

SRC_NM

VARCHAR

50

SITE

VARCHAR

50

MODALITY

VARCHAR

50

P_L

VARCHAR

50

SRC_COLMN

VARCHAR

50

SRC_COLMN_VAL

VARCHAR

50

SRC_COLMN_DT

DATE

9

STC_UPDT_ID

VARCHAR

50

SRC_CRETN_ID

VARCHAR

50

SRC_UPD_TS

TIMESTAMP

0

SRC_CRETN_TS

TIMESTAMP

0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

51

ODS_POSTNG_TS ODS_UPD_TS

TIMESTAMP TIMESTAMP

0 0

TCS Confidential

PK

FK

System

NS

File name AUDITS_GAC_AUDIT_REAUDIT_CS.csv


TRANSFORMATION

SPECIFICATIONS

TARGET Column Name

Data Type

Length

SRC_SYS_ID

VARCHAR

100

SRC_NM

VARCHAR

50

SITE

VARCHAR

50

MODALITY

VARCHAR

50

P_L

VARCHAR

50

SRC_COLMN

VARCHAR

50

SRC_COLMN_VAL

VARCHAR

50

SRC_COLMN_DT

DATE

9

STC_UPDT_ID

VARCHAR

50

TCS Confidential

PK

FK

System

SRC_CRETN_ID

VARCHAR

50

SRC_UPD_TS

TIMESTAMP

0

SRC_CRETN_TS

TIMESTAMP

0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

51

ODS_POSTNG_TS

TIMESTAMP

0

ODS_UPD_TS

TIMESTAMP

0

TCS Confidential

CIFICATIONS SOURCE FILE

Column Name

Data Type

AUDIT_PowerSuite_FW23-2007.csv

Site Modality

TCS Confidential

VARCHAR VARCHAR

VARCHAR

ID VARCHAR

Organization VARCHAR

Finding Date VARCHAR

Resp Person Audit Name

VARCHAR VARCHAR

Audit Type Audit Group

VARCHAR VARCHAR

Finding Type VARCHAR

Finding Category Category Group

VARCHAR VARCHAR

NumItems World Region

VARCHAR VARCHAR

Country Building

VARCHAR VARCHAR

Workstation Citation

VARCHAR VARCHAR

Repeat? Implementation Category

VARCHAR VARCHAR

Problem Statement Action Plan

TCS Confidential

VARCHAR

VARCHAR

Auditor/Contact Contact Phone

VARCHAR VARCHAR

Implementation Complete Date Implementation

VARCHAR VARCHAR

Implemented By Person VARCHAR

Finding Status VARCHAR

Implementation Due Date VARCHAR

Closure Verification Status VARCHAR

Effectiveness Determination Person VARCHAR

Actual Effectiveness Determination Date VARCHAR

Effectiveness Determination Due Date VARCHAR

Effectiveness Determination VARCHAR

CAPA Required VARCHAR

Investigation VARCHAR

Root Cause Effectivity Date

TCS Confidential

VARCHAR

VARCHAR

Effectivity VARCHAR

Finding Reference VARCHAR

Reference ID VARCHAR

Update History VARCHAR

Days Open / To Close VARCHAR

Days Past Closure Due

TCS Confidential

CIFICATIONS

SOURCE FILE

TCS Confidential

Column Name

Data Type

Inspection ID

VARCHAR

Data Entered By

VARCHAR

Data Entered Date

VARCHAR

Inspection ID

VARCHAR

Facility

VARCHAR

City

VARCHAR

Country

VARCHAR

Start Date

VARCHAR

End Date

VARCHAR

Inspector

VARCHAR

Product

VARCHAR

Finding ID

VARCHAR

483 Observations

VARCHAR

CFR Citation

VARCHAR

Data Source

VARCHAR

CIFICATIONS SOURCE FILE

Column Name

Inspection ID Site P&L

TCS Confidential

Data Type VARCHAR

TCS Confidential

Data Entered By

VARCHAR

Data Entered Date

VARCHAR

Start FW End FW**

VARCHAR

Status** Modality** Quality Management Rep.** Lead Auditor** Re-audits needed** FDA registration number Data Source

VARCHAR

VARCHAR

VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR

Repeat?Implementation Category

Length

Business Rule

conactination of ID ,Organization and Site with the symbole ~ Hard Coded 250

1 to 1

251

1 to 1 Looup the P_L based on Modality Look up the column name from QLT_SORC_COLMN_D based on the Column number and sorce valuesname of the sorce columns will be populated as rows by using normalizer Respected date values has to be populated as saparate rows by using normalizer Null Null Null Null hard coded hard code Mapping Name SESSSTARTTIME

TCS Confidential

Problem Statement Action PlanAuditor/Contact Contact Phone Implementation Implementation Complete Date

SESSSTARTTIME 250

this source column will be populated as a row in the taget table column SRC_COLMN_VAL

250


250


250


250

this source column will be populated as thisasource column will be populated

250

as a row in the taget table column SRC_COLMN_VAL 250 250

this source column will be populated as asource column will be populated this as a row in the taget table column SRC_COLMN_VAL

250


250


250


this source column will be populated as thisasource column will be populated as a row in the taget table column SRC_COLMN_VAL

250 250


250 250


250 250


250

this source column will be populated as a

TCS Confidential

250


250


250



250


250

this source column will be populated as a row in the taget table column SRC_COLMN_DT

250


250


250


250


250


250


250


250


250

this source column will be populated as a

TCS Confidential

251


252


253


254


255


256


TCS Confidential

Length

Business Rule

250 apply the trim function to remove spaces Hard Coded Null Null Null Look up the column name from QLT_SORC_COLMN_D values of the sorce columns will be populated Respected date values has to be populated Null

250 1 to 1 (convert the string into date) Null 250 1 to 1 (convert the string into date) hard coded hard code Mapping Name SESSSTARTTIME SESSSTARTTIME this source column will be populated as a row in the taget table column 250 SRC_COLMN_VAL this source column will be populated as a row in the taget table column 250 SRC_COLMN_VAL this source column will be populated as a row in the taget table column 250 SRC_COLMN_VAL this source column will be populated as a row in the taget table column 250 SRC_COLMN_VAL this source column will be populated as a row in the taget table column 250 SRC_COLMN_DT this source column will be populated as a row in the taget table column 250 SRC_COLMN_DT this source column will be populated as a row in the taget table column 250 SRC_COLMN_VAL

TCS Confidential

this source column will be populated as a row in the taget table column 250 SRC_COLMN_VAL this source column will be populated as a row in the taget table column 250 SRC_COLMN_VAL this source column will be populated as a row in the taget table column 250 SRC_COLMN_VAL this source column will be populated as a row in the taget table column 250 SRC_COLMN_VAL this source column will be populated as a row in the taget table column 250 SRC_COLMN_VAL

Length

Business Rule

250 Combitnation of Site,P&L and Start FW Hard Coded 1 to 1 Null 1 to 1 Null values of the sorce columns will be populated Respected date values has to be populated Null

TCS Confidential

250 1 to 1 (convert the string into date) Null 250 1 to 1 (convert the string into date) hard coded hard code Mapping Name SESSSTARTTIME SESSSTARTTIME this source column will be populated as a row in the taget table column 250 SRC_COLMN_VAL 250 this source column will be populated as thisasource column will be populated as a row in the taget table column 250 SRC_COLMN_VAL 250 this source column will be populated as asource column will be populated this

250

as a 250 this source column will be populated as a 250 this source column will be populated as a 250 this source column will be populated as a 250 this source column will be populated as a

TCS Confidential

Implemented Finding By Person Status Implementation Closure DueVerification Date Effectiveness Status Actual Determination Effectiveness Effectiveness Person Determination Effectiveness Determination CAPA Date Determination Due Required Date

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

Investigation Root CauseEffectivity Date Effectivity Finding Reference Reference ID Update History Days Open Days / To Close Past Closure Due

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential


TCS Confidential

Legend

Oracle Table


XML File


Lookup table

SRV_PQR_PSR_DATA_CLOSED.XML

DEL_INS

1

XML File QLT_SRC_COLMN_D

GCD_DAT_SORC_D

SRV_PQR_PSR_DATA_OPEN.XML

DEL_INS

2


GCD_DAT_SORC_D


DEL_INS

2


GCD_DAT_SORC_D

TCS Confidential

Table :

QLT_COMPLNT_AUDT_I



Step 2:


Step 3:


TCS Confidential

TCS Confidential

TCS Confidential


ODS (Teradata)

QLT_COMPLNT_AU DT_I

TCS Confidential


TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_COMPLNT_AUDT_I_iTrak_INS

Session Name

s_m_WL_QLT_COMPLNT_AUDT_I_iTrak_INS

Mapping Name

m_WL_QLT_COMPLNT_AUDT_I_iTrak_INS

Source Tables

Database

Target Tables


Insert

Load Type

n/a

Join Condition

SQ Query

TARGET System


SRC_ETL_TARGET QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

File name SRV_PQR_PSR_DATA_OPEN.XML


TRANSFORMATION

SPECIFICATIONS

TARGET Column Name

Data Type

Length

SRC_SYS_ID SRC_NM

VARCHAR VARCHAR

100 50

SITE

VARCHAR

50

MODALITY

VARCHAR

50

P_L SRC_COLMN

VARCHAR VARCHAR

50 50

SRC_COLMN_VAL

VARCHAR

50

SRC_COLMN_DT

DATE

9

STC_UPDT_ID

VARCHAR

50

SRC_CRETN_ID

VARCHAR

50

SRC_UPD_TS

TIMESTAMP

0

SRC_CRETN_TS

TIMESTAMP

0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

51

ODS_POSTNG_TS

TIMESTAMP

0

ODS_UPD_TS

TIMESTAMP

0

TCS Confidential

PK

FK

System

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

SPECIFICATIONS SOURCE FILE

Column Name

Data Type

AUDIT_PowerSuite_FW23-2007.csv ID

Site Modality

TCS Confidential

VARCHAR VARCHAR

VARCHAR REPRT_NUM VARCHAR ISSUE_TYP VARCHAR REPRT_TITLE VARCHAR ISSUE_STS SITE_POLE

VARCHAR VARCHAR

MODALITY_SEGMENT SYS_ID

VARCHAR VARCHAR

GE_PRIMRY_CONTCT VARCHAR CUST_SITE_NM CUST_CONTRY

VARCHAR VARCHAR

CUST_ADD_DESC AUTHR_NM

VARCHAR VARCHAR

AUTHR_ROLE BRAND_PROD_NM

VARCHAR VARCHAR

PROBLM_DESC ISSUE_DESC

VARCHAR VARCHAR

OWNER_GRP COMPLNT_CORDNTR

VARCHAR VARCHAR

COMPLNT_OWNER QLT_CD

VARCHAR VARCHAR

CLOSR_TRGT_DT ROOT_CAUSE

VARCHAR VARCHAR

CORRCTV_ACT VERFCTN_CONTRL

VARCHAR VARCHAR

VALDTN_OF_EFFCTVNSS

TCS Confidential

VARCHAR CLOSR_CD VARCHAR CLOSD_DT VARCHAR COMPLN_CRETN_DT VARCHAR COMPLN_CRETN_BY VARCHAR CAPA_INVSTGTN_SUMRY VARCHAR CAPA_PLN_SUMRY VARCHAR RESLV_DT VARCHAR VERFD_DT VARCHAR LAT_FLG VARCHAR RESPNSBL_SIT ABSLT_AGE

VARCHAR VARCHAR

ADVRS_FLG VARCHAR DAYS_OPN VARCHAR GE_KNOWLDG_DT VARCHAR PREVNTV_ACTN

TCS Confidential

TCS Confidential

TCS Confidential

Repeat?Implementation Category

Length

Business Rule

1 to 1 "iTrak" Hard Coded 250

1 to 1

251

1 to 1 Looup the P_L based on Modality Look up the column name from QLT_SORC_COLMN_D based on the Column number and sorce name values of the sorce columns will be populated as rows by using normalizer Respected date values has to be populated as saparate rows by using normalizer Null Null Null Null "SRV_PQR_PSR_DATA_OPEN.XML" "m_WL_QLT_COMPLNT_AUDT_I_iTrak_INS" SESSSTARTTIME SESSSTARTTIME

TCS Confidential

Problem Statement Action PlanAuditor/Contact Contact Phone Implementation Complete

250


250


250


250


250

this source column will be populated as a row in the taget table SRC_COLMN_VAL this source column willcolumn be populated as a

250

row in the taget table column SRC_COLMN_VAL 250 250

this source column will be populated as a row in the taget table SRC_COLMN_VAL this source column willcolumn be populated as a row in the taget table column SRC_COLMN_VAL

250


250


250

row in the taget table column SRC_COLMN_VAL 250 250


250 250


250 250


250 250


250 250


250 250


250 250


TCS Confidential

250


250


250


250


250


250


250


250


250


250


250


251

row in the taget table column SRC_COLMN_VAL 252


253


254


TCS Confidential

TCS Confidential

TCS Confidential

Implementation Implemented Finding By Person Status Implementation Closure DueVerification Date Effectiveness Status Actual Determination Effectiveness Effectiveness Person Determination Effectiveness Determination Date Determination Due Date

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

CAPA Required Investigation Root CauseEffectivity Date Effectivity Finding Reference Reference ID Update History Days Open / To Close

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential


TCS Confidential

Legend

Oracle Table


Flat File


Lookup table

dummy_Program1.csv

DEL_INS

1


GCD_DAT_SORC_D

TCS Confidential

Table :

QLT_COMPLNT_AUDT_I



Step 2:


Step 3:


TCS Confidential

TCS Confidential

TCS Confidential


ODS (Teradata)

QLT_COMPLNT_AU DT_I

TCS Confidential


TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_COMPLNT_AUDT_I_eNPI_INS

Session Name

s_m_WL_QLT_COMPLNT_AUDT_I_eNPI_INS

Mapping Name

m_WL_QLT_COMPLNT_AUDT_I_eNPI_INS

Source Tables

Database

File name dummy_Program1.csv

Target Tables


Database

Table name

SRC_ETL_TARGET ( Teradata )

QLT_COMPLNT_AUDT_I

Insert n/a

SQ Query

TRANSFORMATION

System

TARGET Column Name

Data Type

QLT_COMPLNT_AUDT_I

SRC_SYS_ID

VARCHAR

100

QLT_COMPLNT_AUDT_I

SRC_NM

VARCHAR

50

QLT_COMPLNT_AUDT_I

SITE

VARCHAR

50

QLT_COMPLNT_AUDT_I

MODALITY

VARCHAR

50

QLT_COMPLNT_AUDT_I

P_L

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_COLMN

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_COLMN_VAL

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_COLMN_DT

DATE

9

QLT_COMPLNT_AUDT_I

STC_UPDT_ID

VARCHAR

50


Length

SRC_ETL_TARGET

TCS Confidential

SP

TCS Confidential

QLT_COMPLNT_AUDT_I

SRC_CRETN_ID

VARCHAR

QLT_COMPLNT_AUDT_I

SRC_UPD_TS

TIMESTAMP 0

50

QLT_COMPLNT_AUDT_I

SRC_CRETN_TS

TIMESTAMP 0

QLT_COMPLNT_AUDT_I

DAT_ORGN

VARCHAR

50

QLT_COMPLNT_AUDT_I

POSTNG_AGNT

VARCHAR

51

QLT_COMPLNT_AUDT_I

ODS_POSTNG_TS

TIMESTAMP 0

QLT_COMPLNT_AUDT_I

ODS_UPD_TS

TIMESTAMP 0

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

TRANSFORMATION

PK

SPECIFICATIONS

FK

System

SOURCE Column Name

FILE

Data Type

dummy_Program1.csv

Site Modality

TCS Confidential

VARCHAR VARCHAR

VARCHAR

Serial No. VARCHAR

Link to eNPI Program VARCHAR

Type VARCHAR

Name VARCHAR

Revision VARCHAR

Object ID VARCHAR

eNPI_Modality VARCHAR

eNPI_PrimaryDesignResponsibility VARCHAR

eNPI_PrimaryMfgResponsibility

TCS Confidential

VARCHAR

eNPI_ProgramCatcher VARCHAR

eNPI_ProgramComments VARCHAR

eNPI_ProgramCurrentMilestoneName VARCHAR

eNPI_ProgramDateActivityBegan VARCHAR

eNPI_ProgramDescription VARCHAR

eNPI_ProgramExecutionLength VARCHAR

eNPI_ProgramFundingSource VARCHAR

eNPI_ProgramGPECountry VARCHAR

eNPI_ProgramLastApprovedMilestoneName VARCHAR

eNPI_ProgramLastApprovedMilestoneSeq VARCHAR

eNPI_ProgramManager

TCS Confidential

VARCHAR

eNPI_ProgramMSDupStatus VARCHAR

eNPI_ProgramName VARCHAR

eNPI_ProgramOwnerName VARCHAR

eNPI_ProgramPitcher VARCHAR

eNPI_ProgramPurchaseFamily VARCHAR

eNPI_ProgramSixSigmaProjectNumber VARCHAR

eNPI_ProgramStatus VARCHAR

eNPI_ProgramSubtype VARCHAR

eNPI_ProgramThreshold VARCHAR

eNPI_ProgramType VARCHAR

eNPI_ProgramVCPProjectNumber

TCS Confidential

VARCHAR

eNPI_QualityComments VARCHAR

eNPI_QualityStatus VARCHAR

eNPI_ScheduleComments VARCHAR

eNPI_ScheduleStatus VARCHAR

eNPI_BuildPlan_ExtEvalM1PlanQty VARCHAR

eNPI_BuildPlan_ExtEvalCurrEstQty VARCHAR

eNPI_BuildPlan_ExtEvalQtyShipped VARCHAR

eNPI_BuildPlan_PilotsM1PlanQty VARCHAR

eNPI_BuildPlan_PilotsCurrEstQty VARCHAR

eNPI_BuildPlan_PilotsQtyShipped

TCS Confidential

VARCHAR

eNPI_MilestoneName VARCHAR

eNPI_MilestoneActualDate VARCHAR

eNPI_MilestoneApprovalsPercentComplete VARCHAR

eNPI_MilestoneApprovalsTotalNumber VARCHAR

eNPI_MilestoneApprovalsTotalOpen VARCHAR

eNPI_MilestoneCloneComment VARCHAR

eNPI_MilestoneCloneReason

TCS Confidential

VARCHAR

eNPI_MilestoneColor VARCHAR

eNPI_MilestoneComments VARCHAR

eNPI_MilestoneEstimateDate VARCHAR

eNPI_MilestoneExecutionSigma VARCHAR

eNPI_MilestonePlanDate VARCHAR

eNPI_MilestoneSlip VARCHAR

eNPI_MilestoneStatus

TCS Confidential

VARCHAR

eNPI_MilestoneTasksOppTotalNumber VARCHAR

eNPI_MilestoneTasksOppTotalOpen VARCHAR

eNPI_MilestoneTasksPercentComplete VARCHAR

eNPI_MilestoneTasksTotalNumber VARCHAR

eNPI_MilestoneTasksTotalOpen ObjectName(Task),DHF Value

TCS Confidential

Repeat?

Length

Implementation Category Problem Statement Action PlanAuditor/Contact Contact Phone

Business Rule

concatination of eNPI_Modality,eNPI_Mil estoneName with the symbole ~ Hard Coded 250

1 to 1

251

1 to 1 Looup the P_L based on Modality Look up the column name from QLT_SORC_COLMN_D based on the Column number and sorce name

values of the sorce columns will be populated as rows by using normalizer Respected date values has to be populated as saparate rows by using normalizer Null

TCS Confidential

Null Null Null hard coded hard code Mapping Name SESSSTARTTIME SESSSTARTTIME 250


250


250


250


250


250


250


250


250


TCS Confidential

250


250


250


250


250


250


250


250


250


250


250


TCS Confidential

250


250


250


250


250


250


250


250


250


250


250


TCS Confidential

250


250


250


250


250


251


252


253


254


254


TCS Confidential

254

this source column will be populated as a row in the taget table column SRC_COLMN_VAL 254







TCS Confidential

254








TCS Confidential

254






TCS Confidential

Legend

Oracle Table


Flat File

Teradata Interface Table

Lookup table

OUTPUT_MDR_AER.lst

Teradata Fact Stage Table

DEL_INS

1


GCD_DAT_SORC_D

OUTPUT_RECALLS.lst

DEL_INS

2


GCD_DAT_SORC_D

OUTPUT_REG_ENFORCEMENT.lst

DEL_INS

3


GCD_DAT_SORC_D

Table :

Falt File to InterFace table Step 1: 2.Informatica Mappings are used to load the Data from Flat File to Interface table From Falt File to Inter Face Step 2:


TCS Confidential

Step 3:


TCS Confidential

TCS Confidential


QLT_SRC_COLMN_D

GCD_DAT_SORC_D

QLT_SRC_COLMN_D

GCD_DAT_SORC_D

QLT_SRC_COLMN_D

GCD_DAT_SORC_D

TCS Confidential

QLT_COMPLNT_AU DT_I

TCS Confidential

TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_MDR_AER_DTL_S_TRUNC_INS_SD

Session Name

s_m_WL_QLT_MDR_AER_DTL_S_TRUNC_INS_SD

Mapping Name

m_WL_QLT_MDR_AER_DTL_S_TRUNC_INS_SD

Source Tables

Source Path /ftp/SrcFiles/oqm

Target Tables

Database FDS_ETL_TARGET ( Teradata )

Load Type

Truncate-Insert

Join Condition

n/a

SQ Query

TR TARGET System


FDS_ETL_TARGET QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S QLT_MDR_AER_DTL_S

Folder Name

TCS Confidential


WorkFlow Name

wkf_s_m_WL_QLT_RECAL_DTL_S_TRUNC_INS_SD

Session Name

s_m_WL_QLT_RECAL_DTL_S_TRUNC_INS_SD

Mapping Name

m_WL_QLT_RECAL_DTL_S_TRUNC_INS_SD

Source Tables


Target Tables


Load Type

Truncate-Insert

Join Condition

n/a

SQ Query

TR TARGET System


FDS_ETL_TARGET QLT_RECAL_DTL_S QLT_RECAL_DTL_S QLT_RECAL_DTL_S QLT_RECAL_DTL_S QLT_RECAL_DTL_S QLT_RECAL_DTL_S QLT_RECAL_DTL_S QLT_RECAL_DTL_S QLT_RECAL_DTL_S QLT_RECAL_DTL_S QLT_RECAL_DTL_S QLT_RECAL_DTL_S QLT_RECAL_DTL_S QLT_RECAL_DTL_S QLT_RECAL_DTL_S QLT_RECAL_DTL_S QLT_RECAL_DTL_S QLT_RECAL_DTL_S QLT_RECAL_DTL_S QLT_RECAL_DTL_S QLT_RECAL_DTL_S

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_REGLTRY_ENFRCMNT_DTL_S_TRUNC_INS_SD

Session Name

s_m_WL_QLT_REGLTRY_ENFRCMNT_DTL_S_TRUNC_INS_SD

Mapping Name

m_WL_QLT_REGLTRY_ENFRCMNT_DTL_S_TRUNC_INS_SD

Source Tables


Target Tables


TCS Confidential

Load Type

Truncate-Insert

Join Condition

n/a

SQ Query

TR TARGET System


FDS_ETL_TARGET QLT_REGLTRY_ENFRCMNT_DTL_S QLT_REGLTRY_ENFRCMNT_DTL_S QLT_REGLTRY_ENFRCMNT_DTL_S QLT_REGLTRY_ENFRCMNT_DTL_S QLT_REGLTRY_ENFRCMNT_DTL_S QLT_REGLTRY_ENFRCMNT_DTL_S QLT_REGLTRY_ENFRCMNT_DTL_S QLT_REGLTRY_ENFRCMNT_DTL_S QLT_REGLTRY_ENFRCMNT_DTL_S QLT_REGLTRY_ENFRCMNT_DTL_S QLT_REGLTRY_ENFRCMNT_DTL_S QLT_REGLTRY_ENFRCMNT_DTL_S QLT_REGLTRY_ENFRCMNT_DTL_S QLT_REGLTRY_ENFRCMNT_DTL_S QLT_REGLTRY_ENFRCMNT_DTL_S QLT_REGLTRY_ENFRCMNT_DTL_S QLT_REGLTRY_ENFRCMNT_DTL_S Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_IFR_MFR_DTL_S_TRUNC_INS_SD

Session Name

s_m_WL_QLT_IFR_MFR_DTL_S_TRUNC_INS_SD

Mapping Name

m_WL_QLT_IFR_MFR_DTL_S_TRUNC_INS_SD

Source Tables


Target Tables



Truncate-Insert n/a

SQ Query

TR TARGET System

TCS Confidential


FDS_ETL_TARGET OUTPUT_IFR_MFR_DTL_S OUTPUT_IFR_MFR_DTL_S OUTPUT_IFR_MFR_DTL_S OUTPUT_IFR_MFR_DTL_S OUTPUT_IFR_MFR_DTL_S OUTPUT_IFR_MFR_DTL_S OUTPUT_IFR_MFR_DTL_S OUTPUT_IFR_MFR_DTL_S OUTPUT_IFR_MFR_DTL_S OUTPUT_IFR_MFR_DTL_S OUTPUT_IFR_MFR_DTL_S OUTPUT_IFR_MFR_DTL_S OUTPUT_IFR_MFR_DTL_S OUTPUT_IFR_MFR_DTL_S OUTPUT_IFR_MFR_DTL_S

TCS Confidential

SD

File Name OUTPUT_MDR_AER.lst Table name QLT_MDR_AER_DTL_S

TRANSFORMATION

SPECIFICATIONS

TARGET

SOURCE Column Name

Data Type

Length

PK FK System

FILE OUTPUT_MDR_AER.lst

REPRT_NUM

VARCHAR

50

MDR_AER_FLG

VARCHAR

50

CONTRY

VARCHAR

50

MDR_AER_REPRT_NBR

VARCHAR

50

VARCHAR

50

MODLTY_SEGMNT

VARCHAR

50

EVNT_TYP

VARCHAR

50

REPRT_TYP

VARCHAR

50

INJRY

VARCHAR

50

MDR_AER_FIL_DT

TIMESTAMP 6

MANL_SRC_NM

VARCHAR

GE_KNOWLDG_DT

TIMESTAMP 6

PRODUCT

COMPLN_CRETN_DT LAT_FLG PQR_PSR_CONVRSN_DT

50

TIMESTAMP 6 VARCHAR

50

TIMESTAMP 6

SRC_NM

VARCHAR

50

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

50

SRC_CRETN_ID

VARCHAR

50

SRC_CRETN_TS

TIMESTAMP 0

SRC_UPD_ID

VARCHAR

SRC_UPD_TS

TIMESTAMP 0

50

ODS_POSTNG_TS

TIMESTAMP 0

ODS_UPD_TS

TIMESTAMP 0

MODALITY

VARCHAR

50

BUSNS

VARCHAR

50

SIT_NAM

VARCHAR

50

TCS Confidential

File Name OUTPUT_RECALLS.lst Table name QLT_RECAL_DTL_S

TRANSFORMATION

SPECIFICATIONS

TARGET

SOURCE Column Name

Data Type

Length

PK FK System

FILE OUTPUT_MDR_AER.lst

RECAL_CORCTN_ID

VARCHAR

50

FISCL_WEK

VARCHAR

50

PRODUCT

VARCHAR

50

RECAL_UNT

VARCHAR

50

RESLTN

VARCHAR

50

OWNR

VARCHAR

50

OWNR_ID

VARCHAR

50

CLOSR_TIMNG

VARCHAR

50

RECAL_FLG

TIMESTAMP 6

MANL_SRC_NM

VARCHAR

50

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

50

SRC_CRETN_ID

VARCHAR

50

SRC_CRETN_TS

TIMESTAMP 0

SRC_UPD_ID

VARCHAR

SRC_UPD_TS

TIMESTAMP 0

50

ODS_POSTNG_TS

TIMESTAMP 0

ODS_UPD_TS

TIMESTAMP 0

MODALITY

VARCHAR

50

BUSNS

VARCHAR

50

SIT_NAM

VARCHAR

50

TRUNC_INS_SD

NC_INS_SD

_INS_SD File Name OUTPUT_REG_ENFORCEMENT.lst Table name QLT_REGLTRY_ENFRCMNT_DTL_S

TCS Confidential

TRANSFORMATION

SPECIFICATIONS

TARGET

SOURCE Column Name

Data Type

Length

PK FK System

FILE OUTPUT_REG_ENFORCEMENT.lst

REG_ENF_SIT

VARCHAR

50

REG_ENF_BUS

VARCHAR

50

START_DT

TIMESTAMP 6

END_DT

TIMESTAMP 6

INSPCTR

VARCHAR

50

ENFRCMNT_TYP

VARCHAR

50

MANL_SRC_NM

VARCHAR

50

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

50

SRC_CRETN_ID

VARCHAR

50

SRC_CRETN_TS

TIMESTAMP 0

ODS_POSTNG_TS

TIMESTAMP 0

ODS_UPD_TS

TIMESTAMP 0

SRC_UPD_ID

VARCHAR

50

SRC_UPD_TS

TIMESTAMP 0

SRC_NM

VARCHAR

50

VARCHAR

50

REG_ENF_MOD

D

File Name OUTPUT_IFR_MFR.lst Table name QLT_IFR_MFR_DTL_S

TRANSFORMATION

SPECIFICATIONS

TARGET

SOURCE Column Name

TCS Confidential

Data Type

Length

PK FK System

FILE

OUTPUT_IFR_MFR.lst IFR_IB

VARCHAR

50

IFR_SCA_90

VARCHAR

50

MFR_IB

VARCHAR

50

MFR_SCA_90

VARCHAR

50

MANL_SRC_NM

VARCHAR

50

SRC_NM

VARCHAR

50

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

50

SRC_CRETN_ID

VARCHAR

50

SRC_CRETN_TS SRC_UPD_ID

TIMESTAMP 0 VARCHAR

50

SRC_UPD_TS

TIMESTAMP 0

ODS_POSTNG_TS

TIMESTAMP 0

ODS_UPD_TS

TIMESTAMP 0

MODLTY

VARCHAR

IFR_IMR_FW

VARCHAR

TCS Confidential

50 50

TIONS SOURCE Column Name

Data Type

Length

PSR#

VARCHAR

250

MDR AER Flag

VARCHAR

250

Country Reported to

VARCHAR

250

MDR AER Report Number**

VARCHAR

250

Product**

VARCHAR

250

Modality Segment**

VARCHAR

250

Event Type**

VARCHAR

250

Report Type

VARCHAR

250

Injury?**

VARCHAR

250

MDR AER Filed Date

VARCHAR

250

VARCHAR

250

PQR To PSR Conversion Date**

VARCHAR

250

Data Source

VARCHAR

250

Data Entered by

VARCHAR

250

Data Entered Date

VARCHAR

250

Data Entered by

VARCHAR

250

Data Entered Date

VARCHAR

250

Modality**

VARCHAR

250

Business

VARCHAR

250

TCS Confidential


Data Type

Length

Recall Correction ID

VARCHAR

250

Fiscal Week

VARCHAR

250

Product

VARCHAR

250

Units

VARCHAR

250

Resolution

VARCHAR

250

Owner**

VARCHAR

250

Owner SSO**

VARCHAR

250

Closure Timing**

VARCHAR

250

Recall

VARCHAR

250

Data Entered by

VARCHAR

250

Data Entered Date

VARCHAR

250

Data Entered by

VARCHAR

250

Data Entered Date

VARCHAR

250

Business

VARCHAR

250

TCS Confidential


Data Type

Length

Facility

VARCHAR

250

Business

VARCHAR

250

Start Date

VARCHAR

250

End Date**

VARCHAR

250

Enforcement Type

VARCHAR

250

Owner**

VARCHAR

250

Data Entered by

VARCHAR

250

Data Entered Date

VARCHAR

250

Data Entered by

VARCHAR

250

Data Entered date

VARCHAR

250

Data Source

VARCHAR

250

Modality**

VARCHAR

250


TCS Confidential

Data Type

Length

IFR-Install Base

VARCHAR

250

IFR-SCA

VARCHAR

250

MFR-Install Base

VARCHAR

250

MFR-SCA

VARCHAR

250

Data Source

VARCHAR

250

Data Entered by

VARCHAR

250

Data Entered Date

VARCHAR

250

Data Entered by

VARCHAR

250

Data Entered date

VARCHAR

250

Modality

VARCHAR

250

Fiscal Week

VARCHAR

250

TCS Confidential

Business Rule

1 to 1 1 to 1 1 to 1 1 to 1 1 to 1 1 to 1 1 to 1 1 to 1 1 to 1 1 to 1 (convert the string into date) Hard coded "http://supportcentral.ge.com/products/sup_products" NULL NULL NULL 1 to 1 (convert the string into date) 1 to 1 Hard coded "OUTPUT_MDR_AER.lst" Hard coded "m_WL_QLT_MDR_AER_DTL_S_TRUNC_INS_SD" 1 to 1 1 to 1 (convert the string into date) 1 to 1 1 to 1 (convert the string into date) SESSSTARTTIME SESSSTARTTIME 1 to 1 1 to 1 Hard coded'-99999'

TCS Confidential

Business Rule

1 to 1 1 to 1 1 to 1 1 to 1 1 to 1 1 to 1 1 to 1 1 to 1 1 to 1 Hard coded "http://supportcentral.ge.com/products/sup_products" Hard coded "OUTPUT_RECALLS.lst" Hard coded "m_WL_QLT_RECAL_DTL_S _TRUNC_INS_SD" 1 to 1 1 to 1 (convert the string into date) 1 to 1 1 to 1 (convert the string into date) SESSSTARTTIME SESSSTARTTIME Hard coded'-99999' 1 to 1 Hard coded'-99999'

TCS Confidential

Business Rule

1 to 1 1 to 1 1 to 1 (convert the string into date) 1 to 1 (convert the string into date) 1 to 1 1 to 1 Hard coded "http://supportcentral.ge.com/products/sup_products" Hard coded "OUTPUT_REG_ENFORCEMENT.lst" Hard coded "m_WL_QLT_REGLTRY_ENFRCMNT_DTL_S _TRUNC_INS_SD" 1 to 1 1 to 1 (convert the string into date) SESSSTARTTIME SESSSTARTTIME 1 to 1 1 to 1 (convert the string into date) 1 to 1 1 to 1

Business Rule

TCS Confidential

1 to 1 1 to 1 1 to 1 1 to 1 Hard coded "http://supportcentral.ge.com/products/sup_products" 1 to 1 Hard coded "OUTPUT_REG_ENFORCEMENT.lst" Hard coded "m_WL_OUTPUT_IFR_MFR_DTL_S _TRUNC_INS_SD" 1 to 1 1 to 1 (convert the string into date) 1 to 1 1 to 1 (convert the string into date) SESSSTARTTIME SESSSTARTTIME 1 to 1 1 to 1

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_COMPLNT_AUDT_I_INS_MDR_AER

Session Name

s_m_WL_QLT_COMPLNT_AUDT_I_INS_MDR_AER

Mapping Name

m_WL_QLT_COMPLNT_AUDT_I_INS_MDR_AER

Source Tables

Database

Table Name

FDS_ETL_TARGET(Teradata)

QLT_MDR_AER_DTL_S

Database


Target Tables

FDS_ETL_TARGET ( Teradata )


Insert n/a

SQ Query TRANSFORMATION

SPECIFICATIONS

TARGET System


Column Name

Data Type

Length

FDS_ETL_TARGET

TCS Confidential

QLT_COMPLNT_AUDT_I

SRC_SYS_ID

VARCHAR

100

QLT_COMPLNT_AUDT_I

SRC_NM

VARCHAR

50

QLT_COMPLNT_AUDT_I

SITE

VARCHAR

50

QLT_COMPLNT_AUDT_I

MODALITY

VARCHAR

50

QLT_COMPLNT_AUDT_I

BUSINESS

VARCHAR

50

TCS Confidential

QLT_COMPLNT_AUDT_I

SRC_COLMN

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_COLMN_VAL

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_COLMN_DT

DATE

9

QLT_COMPLNT_AUDT_I

STC_UPDT_ID

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_CRETN_ID

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_UPD_TS

TIMESTAMP 0

QLT_COMPLNT_AUDT_I

SRC_CRETN_TS

TIMESTAMP 0

QLT_COMPLNT_AUDT_I

DAT_ORGN

VARCHAR

50

QLT_COMPLNT_AUDT_I

POSTNG_AGNT

VARCHAR

51

QLT_COMPLNT_AUDT_I

ODS_POSTNG_TS

TIMESTAMP 0

QLT_COMPLNT_AUDT_I

ODS_UPD_TS

TIMESTAMP 0

TCS Confidential

TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_COMPLNT_AUDIT_I_INS_RECALLS

Session Name

s_m_WL_QLT_COMPLNT_AUDIT_I_INS_RECALLS

Mapping Name

m_WL_QLT_COMPLNT_AUDIT_I_INS_RECALLS

Source Tables

Database

Table Name


QLT_RECAL_DTL_S

Database


Target Tables



Insert n/a

TRANSFORMATIONSPECIFICATIONS TARGET System


Column Name

Data Type

Length

QLT_COMPLNT_AUDT_I

SRC_SYS_ID

VARCHAR

100

QLT_COMPLNT_AUDT_I

SRC_NM

VARCHAR

50

QLT_COMPLNT_AUDT_I

SITE

VARCHAR

50

QLT_COMPLNT_AUDT_I

MODALITY

VARCHAR

50

QLT_COMPLNT_AUDT_I

BUSINESS

VARCHAR

50

FDS_ETL_TARGET

TCS Confidential

TCS Confidential

QLT_COMPLNT_AUDT_I

SRC_COLMN

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_COLMN_VAL

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_COLMN_DT

DATE

9

QLT_COMPLNT_AUDT_I

STC_UPDT_ID

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_CRETN_ID

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_UPD_TS

TIMESTAMP 0

QLT_COMPLNT_AUDT_I

SRC_CRETN_TS

TIMESTAMP 0

QLT_COMPLNT_AUDT_I

DAT_ORGN

VARCHAR

50

QLT_COMPLNT_AUDT_I

POSTNG_AGNT

VARCHAR

51

QLT_COMPLNT_AUDT_I

ODS_POSTNG_TS

TIMESTAMP 0

QLT_COMPLNT_AUDT_I

ODS_UPD_TS

TIMESTAMP 0

TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_COMPLNT_AUDT_I_INS_REGLTRY_ENFRCMNT

Session Name

s_m_WL_QLT_COMPLNT_AUDT_I_INS_REGLTRY_ENFRCMNT

Mapping Name

m_WL_QLT_COMPLNT_AUDT_I_INS_REGLTRY_ENFRCMNT

Source Tables

Database

Table Name


QLT_REGLTRY_ENFRCMNT_DTL_S

Database


Target Tables



Insert n/a TRANSFORMATION

SPECIFICATIONS

TARGET System


Column Name

Data Type

Length

QLT_COMPLNT_AUDT_I

SRC_SYS_ID

VARCHAR

100

QLT_COMPLNT_AUDT_I

SRC_NM

VARCHAR

50

QLT_COMPLNT_AUDT_I

SITE

VARCHAR

50

QLT_COMPLNT_AUDT_I

MODALITY

VARCHAR

50

QLT_COMPLNT_AUDT_I

BUSINESS

VARCHAR

50

FDS_ETL_TARGET

TCS Confidential

TCS Confidential

QLT_COMPLNT_AUDT_I

SRC_COLMN

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_COLMN_VAL

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_COLMN_DT

DATE

9

QLT_COMPLNT_AUDT_I

STC_UPDT_ID

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_CRETN_ID

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_UPD_TS

TIMESTAMP 0

QLT_COMPLNT_AUDT_I

SRC_CRETN_TS

TIMESTAMP 0

QLT_COMPLNT_AUDT_I

DAT_ORGN

VARCHAR

50

QLT_COMPLNT_AUDT_I

POSTNG_AGNT

VARCHAR

51

QLT_COMPLNT_AUDT_I

ODS_POSTNG_TS

TIMESTAMP 0

QLT_COMPLNT_AUDT_I

ODS_UPD_TS

TIMESTAMP 0

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_COMPLNT_AUDIT_I_INS_IFR_MFR

Session Name

s_m_WL_QLT_COMPLNT_AUDIT_I_INS_IFR_MFR

Mapping Name

m_WL_QLT_COMPLNT_AUDIT_I_INS_IFR_MFR

Source Tables

Database

Table Name


QLT_IFR_MFR_DTL_S

Database


Target Tables


TCS Confidential


Insert n/a TRANSFORMATION

SPECIFICATIONS

TARGET System


Column Name

Data Type

Length

QLT_COMPLNT_AUDT_I

SRC_SYS_ID

VARCHAR

100

QLT_COMPLNT_AUDT_I

SRC_NM

VARCHAR

50

QLT_COMPLNT_AUDT_I

SITE

VARCHAR

50

QLT_COMPLNT_AUDT_I

MODALITY

VARCHAR

50

QLT_COMPLNT_AUDT_I

BUSINESS

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_COLMN

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_COLMN_VAL

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_COLMN_DT

DATE

9

QLT_COMPLNT_AUDT_I

STC_UPDT_ID

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_CRETN_ID

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_UPD_TS

TIMESTAMP 0

QLT_COMPLNT_AUDT_I

SRC_CRETN_TS

TIMESTAMP 0

FDS_ETL_TARGET

TCS Confidential

TCS Confidential

QLT_COMPLNT_AUDT_I

DAT_ORGN

VARCHAR

50

QLT_COMPLNT_AUDT_I

POSTNG_AGNT

VARCHAR

51

QLT_COMPLNT_AUDT_I

ODS_POSTNG_TS

TIMESTAMP 0

QLT_COMPLNT_AUDT_I

ODS_UPD_TS

TIMESTAMP 0

TCS Confidential

FORMATION

SPECIFICATIONS SOURCE PK

FK

System

TABLE

Column Name

Data Type

Length

Business Rule

QLT_MDR_AER_DTL_S conactinatio n of PSR,MDR_A ER_FALG and COUNTRY_ REPORT_T O with the symbole ~ Hard Coded "Qlt-MDRAERManualcona ctination of PSR,MDR_A ER_FALG and COUNTRY_ REPORT_T O with the symbole ~

TCS Confidential

MODALITY

VARCHAR

251

Default '99999' 1 to 1

BUSINESS

VARCHAR

251

1 to 1

Look up the column name from QLT_SORC _COLMN_D based on the Column number and sorce name values of the sorce columns will be populated as rows by using normalizer Respected date values has to be populated as saparate rows by using normalizer

DATA_ENTERED_BY VARCHAR

250 1 to 1 250 1 to 1

DATA_ENTERED_DATE VARCHAR

250 1 to 1 (convert the string into date)


250 1 to 1 (convert the string into date) hard coded " QLT_MDR_ AER_DTL_S "


Mapping Name hard coded "m_WL_QLT _COMPLNT _AUDT_I_IN S_MDR_AE R"

VARCHAR

REPRT_NUM

TCS Confidential

SESSSTAR TTIME SESSSTAR TTIME 250 this source column will be populated as a row in the taget table column SRC_COLM N_VAL

VARCHAR

MDR_AER_FLG VARCHAR

250 this source column will be populated as a row in the taget table column SRC_COLM N_VAL


CONTRY VARCHAR

MDR_AER_REPRT_NBR VARCHAR



PRODUCT VARCHAR

MODLTY_SEGMNT VARCHAR

EVNT_TYP

TCS Confidential



VARCHAR


VARCHAR


VARCHAR


REPRT_TYP

INJRY

MDR_AER_FIL_DT VARCHAR

MANL_SRC_NM VARCHAR

GE_KNOWLDG_DT VARCHAR

COMPLN_CRETN_DT

TCS Confidential


this source column will be populated as a row in the taget table column SRC_COLM N_VAL


VARCHAR


VARCHAR


LAT_FLG

PQR_PSR_CONVRSN_DT

SFORMATIONSPECIFICATIONS SOURCE PK

FK

System

TABLE

Column Name

Data Type

Length

Business Rule

apply the trim function to remove spaces

QLT_RECA L_DTL_S

RECALL_CORRECTION_ID VARCHAR

250 hard coded "Qlt-RecallManual"

BUSINESS

TCS Confidential

VARCHAR

Default '99999' Default '99999' 250 1 to 1


DATA_ENTERED_BY VARCHAR DATA_ENTERED_BY VARCHAR DATA_ENTERED_DATE VARCHAR


250 1 to 1 250 1 to 1 250 1 to 1 (convert the string into date) 1 to 1 (convert the string into 250 date) hard coded "QLT_RECA L_DTL_S" hard code Mapping Name "m_WL_QLT _COMPLNT _AUDIT_I_I NS_RECALL S" SESSSTAR TTIME SESSSTAR TTIME this source column will be populated as a row in the taget table column SRC_COLM N_VAL

RECAL_CORCTN_ID VARCHAR

TCS Confidential

250

this source column will be populated as a row in the taget table column SRC_COLM N_VAL FISCL_WEK VARCHAR


PRODUCT

VARCHAR


RECAL_UNT VARCHAR

250 this source column will be populated as a row in the taget table column SRC_COLM N_DT

RESLTN

VARCHAR


OWNR

VARCHAR


OWNR_ID

TCS Confidential

VARCHAR

250

this source column will be populated as a row in the taget table column SRC_COLM N_VAL CLOSR_TIMNG VARCHAR

FORMATION

250


FK

System

TABLE

Column Name

Data Type

QLT_REGLTRY_ENFRCMNT_DTL_S

Length

Business Rule

conactinatio n of PL,FACILITY and START_DAT E with the symbole ~ Hard Coded "QltRegulatoryEnforcementManual"

TCS Confidential

MODALITY

VARCHAR

BUSINESS

VARCHAR

Default '99999' 250 1 to 1 250 1 to 1




250 1 to 1 250 1 to 1 250 1 to 1 (convert the string into date) 1 to 1 (convert the string into 250 date) hard coded "QLT_REGL TRY_ENFR CMNT_DTL _S" hard code Mapping Name"m_W L_QLT_CO MPLNT_AU DT_I_INS_R EGLTRY_E NFRCMNT" SESSSTAR TTIME SESSSTAR TTIME this source column will be populated as a row in the taget table column SRC_COLM N_VAL

START_DT

TCS Confidential

VARCHAR

250

this source column will be populated as a row in the taget table column SRC_COLM N_VAL END_DT

VARCHAR


INSPCTR

VARCHAR


ENFRCMNT_TYP VARCHAR


MANL_SRC_NM VARCHAR


DATA_SOURCE VARCHAR

TCS Confidential

250

FORMATION


FK

System

TABLE

Column Name

Data Type

Length

Business Rule

conactinatio n of Modality and Fiscal Week with the symbole ~ QLT_IFR_MFR_DTL_S Hard Coded "Qlt-IFRMFRManual"

MODALITY

VARCHAR

Default '99999' 250 1 to 1 Default '99999' Look up the column name from QLT_SORC _COLMN_D based on the Column number and sorce name values of the sorce columns will be populated as rows by using normalizer Respected date values has to be populated as saparate rows by using normalizer



TCS Confidential

250 1 to 1 250 1 to 1 250 1 to 1 (convert the string into date) 1 to 1 (convert the string into 250 date)

hard coded " QLT_IFR_M FR_DTL_S" hard code Mapping Name"m_W L_QLT_CO MPLNT_AU DIT_I_INS_I FR_MFR" SESSSTAR TTIME SESSSTAR TTIME this source column will be populated as a row in the taget table column SRC_COLM N_VAL IFR_IB

VARCHAR


IFR_SCA_90 VARCHAR


MFR_IB

VARCHAR


MFR_SCA_90VARCHAR

TCS Confidential

250

this source column will be populated as a row in the taget table column SRC_COLM N_VAL MANL_SRC_NM VARCHAR

TCS Confidential

250

TCS Confidential

to 1 (convert the string into date) to 1 (convert the string into date)

TCS Confidential

TCS Confidential

TCS Confidential

TCS Confidential

to 1 (convert the string into date)

TCS Confidential

TCS Confidential

TCS Confidential


TCS Confidential

TCS Confidential


TCS Confidential

Legend

Oracle Table


Flat File


Lookup table

PERSONNEL.lst


DEL_INS

2


GCD_DAT_SORC_D

Table :



TCS Confidential

Step 3:


TCS Confidential


ODS (Teradata)

QLT_COMPLNT _AUDT_I

QLT_SRC_COLMN_D

GCD_DAT_SORC_D

TCS Confidential

TCS Confidential

TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_EDUNEERING_HR_DTL_S_TRUC_INS

Session Name

s_m_WL_QLT_EDUNEERING_HR_DTL_S_TRUC_INS

Mapping Name

m_WL_QLT_EDUNEERING_HR_DTL_S_TRUC_INS

Source Tables

Source Path

File Name

/ftp/SrcFiles/oqm

PERSONNEL.lst

Database

Table name QLT_EDUNEERING_HR_DTL_S

Target Tables



Truncate-Insert n/a

SQ Query TRANSFORMATION TARGET System


Column Name

Data Type

FDS_ETL_TARGET QLT_EDUNEERING_HR_DTL_S

BUSINESS

VARCHAR

QLT_EDUNEERING_HR_DTL_S

QLFD

VARCHAR


NT_QLFD

VARCHAR

QLT_EDUNEERING_HR_DTL_S GRND_TOT

VARCHAR

QLT_EDUNEERING_HR_DTL_S PRCNTG_NT_QLFD

VARCHAR

QLT_EDUNEERING_HR_DTL_S SRC_CRETN_ID

VARCHAR


TIMESTAMP

SRC_CRETN_TS

QLT_EDUNEERING_HR_DTL_S SRC_UPD_ID

VARCHAR


TIMESTAMP

SRC_UPD_TS

QLT_EDUNEERING_HR_DTL_S DAT_ORGN

VARCHAR

QLT_EDUNEERING_HR_DTL_S POSTNG_AGNT

VARCHAR


TIMESTAMP

ODS_POSTNG_TS

QLT_EDUNEERING_HR_DTL_S ODS_UPD_TS

TCS Confidential

VARCHAR

QLT_EDUNEERING_HR_DTL_S SIT

TIMESTAMP

SPECIFICATIONS

ANSFORMATION

SPECIFICATIONS SOURCE Length

50 50

PK

FK

System

FILE

Column Name

Data Type

PURCHASE_APPROVED.lst VARCHAR Business

50

VARCHAR EduNeering Site VARCHAR Qualified

50

Not Qualified VARCHAR

50

0 50 0 50

Data Source VARCHAR

0 0

TCS Confidential

250 1 to 1 250 1 to 1 250 1 to 1 250 1 to 1 NULL

VARCHAR Data Entered by VARCHAR Data Entered Date VARCHAR Data Entered by VARCHAR Data Entered date

50

Business Rule

NULL

50 50

Length

250 1 to 1 250 1 to 1 (convert the string into date) 250 1 to 1 250 1 to 1 (convert the string into date) 250 1 to 1

Hard coded "m_WL_QLT_EDUNEERING_HR_D SESSSTAR TTIME SESSSTAR TTIME

Business Rule

1 to 1 (convert the string into date) 1 to 1 (convert the string into date) Hard coded "m_WL_QLT_EDUNEERING_HR_DTL_S_TRUNC_INS_SD"

TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_COMPLNT_AUDT_I_EDUNEERING_HR_INS

Session Name

s_m_WL_QLT_COMPLNT_AUDT_I_EDUNEERING_HR_INS

Mapping Name

m_WL_QLT_COMPLNT_AUDT_I_EDUNEERING_HR_INS

Source Tables

Database

File name QLT_EDUNEERING_HR_DTL_S

Target Tables


Database

Table name


QLT_COMPLNT_AUDT_I

Insert n/a

SQ Query

TRANSFORMATION

SPECIFICATIONS

TARGET System


Column Name

Data Type

Length

QLT_COMPLNT_AUDT_I

SRC_SYS_ID

VARCHAR

100

QLT_COMPLNT_AUDT_I

SRC_NM

VARCHAR

50

QLT_COMPLNT_AUDT_I

SITE

VARCHAR

50

QLT_COMPLNT_AUDT_I

MODALITY

VARCHAR

50

QLT_COMPLNT_AUDT_I

BUSINESS

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_COLMN

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_COLMN_VAL

VARCHAR

50

SRC_ETL_TARGET

TCS Confidential

TCS Confidential

QLT_COMPLNT_AUDT_I

SRC_COLMN_DT

DATE

9

QLT_COMPLNT_AUDT_I

STC_UPDT_ID

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_CRETN_ID

VARCHAR

50

QLT_COMPLNT_AUDT_I

SRC_UPD_TS

TIMESTAMP 0

QLT_COMPLNT_AUDT_I

SRC_CRETN_TS

TIMESTAMP 0

QLT_COMPLNT_AUDT_I

DAT_ORGN

VARCHAR

50

QLT_COMPLNT_AUDT_I

POSTNG_AGNT

VARCHAR

51

QLT_COMPLNT_AUDT_I

ODS_POSTNG_TS

TIMESTAMP 0

QLT_COMPLNT_AUDT_I

ODS_UPD_TS

TIMESTAMP 0

TCS Confidential

ORMATION

SPECIFICATIONS

PK

FK

System

FILE

SOURCE Column Data Type Name

Length Business Rule

PERSONNEL_QUALITY_TRAINING_DTL.CSV apply the trim function to remove business EduNeering'

ENDUREEING SITE

1 to 1 Default '99999' Default '99999' Look up the column name from QLT_SORC _COLMN_D based on the Column number and sorce name values of the sorce columns will be populated as rows by using normalizer

TCS Confidential

Respected date values has to be populated as saparate rows by using normalizer

DATA_ENTERED_BY VARCHAR DATA_ENTERED_BY VARCHAR

250 -99999 250 -99999


250 TTIME


250 TTIME

SESSSTAR SESSSTAR PERSONNE L_QUALITY _TRAININGl _DTL.CSV' m_WL_QLT _COMPLNT _AUDT_I_P ERSONNEL _TRAINING_ INS

VARCHAR


BUSINESS VARCHAR


NOT QUALIFIED VARCHAR

QUALIFIED

TCS Confidential


VARCHAR

250 this source column will be populated as a row in the taget table column SRC_COLM N_Val

DATA_SOURCE

r

row in the taget table column SRC_COLM N_VAL

TCS Confidential

TCS Confidential

Legend

Oracle Table


Flat File


Lookup table

PURCHASE_APPROVED.lst


DEL_INS

1


GCD_DAT_SORC_D

PURCHASE_UNVAL.lst

DEL_INS

2


GCD_DAT_SORC_D

Table :



TCS Confidential

Step 3:


TCS Confidential


ODS (Teradata)

QLT_SRC_COLMN_D

GCD_DAT_SORC_D

QLT_SRC_COLMN_D

GCD_DAT_SORC_D

TCS Confidential

QLT_COMPLNT_AU DT_I

TCS Confidential

TCS Confidential

TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_COMPLNT_AUDT_I_INS_PURCHASE_UNVAL_SUPLIER

Session Name

s_m_WL_QLT_COMPLNT_AUDT_I_INS_PURCHASE_UNVAL_SUPLIER

Mapping Name

m_WL_QLT_COMPLNT_AUDT_I_INS_PURCHASE_UNVAL_SUPLIER

Source Tables


Target Tables


Load Type

Truncate-Insert

Join Condition

n/a



FDS_ETL_TARGET QLT_UNVLDTD_SUPLR_DTL_S QLT_UNVLDTD_SUPLR_DTL_S QLT_UNVLDTD_SUPLR_DTL_S

QLT_UNVLDTD_SUPLR_DTL_S QLT_UNVLDTD_SUPLR_DTL_S QLT_UNVLDTD_SUPLR_DTL_S QLT_UNVLDTD_SUPLR_DTL_S

QLT_UNVLDTD_SUPLR_DTL_S QLT_UNVLDTD_SUPLR_DTL_S QLT_UNVLDTD_SUPLR_DTL_S QLT_UNVLDTD_SUPLR_DTL_S QLT_UNVLDTD_SUPLR_DTL_S QLT_UNVLDTD_SUPLR_DTL_S QLT_UNVLDTD_SUPLR_DTL_S QLT_UNVLDTD_SUPLR_DTL_S QLT_UNVLDTD_SUPLR_DTL_S

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_COMPLNT_AUDT_I_INS_PURCHASE_APRVD_SUPLIER

Session Name

s_m_WL_QLT_COMPLNT_AUDT_I_INS_PURCHASE_APRVD_SUPLIER

Mapping Name

m_WL_QLT_COMPLNT_AUDT_I_INS_PURCHASE_APRVD_SUPLIER

Source Tables

Source Path

TCS Confidential

SP

/ftp/SrcFiles/oqm Target Tables


Load Type

Truncate-Insert

Join Condition

n/a



FDS_ETL_TARGET QLT_APRVD_SUPLR_DTL_S QLT_APRVD_SUPLR_DTL_S QLT_APRVD_SUPLR_DTL_S

QLT_APRVD_SUPLR_DTL_S QLT_APRVD_SUPLR_DTL_S QLT_APRVD_SUPLR_DTL_S QLT_APRVD_SUPLR_DTL_S QLT_APRVD_SUPLR_DTL_S QLT_APRVD_SUPLR_DTL_S QLT_APRVD_SUPLR_DTL_S QLT_APRVD_SUPLR_DTL_S QLT_APRVD_SUPLR_DTL_S QLT_APRVD_SUPLR_DTL_S

QLT_APRVD_SUPLR_DTL_S QLT_APRVD_SUPLR_DTL_S QLT_APRVD_SUPLR_DTL_S QLT_APRVD_SUPLR_DTL_S QLT_APRVD_SUPLR_DTL_S QLT_APRVD_SUPLR_DTL_S QLT_APRVD_SUPLR_DTL_S QLT_APRVD_SUPLR_DTL_S QLT_APRVD_SUPLR_DTL_S QLT_APRVD_SUPLR_DTL_S

TCS Confidential

SP

E_UNVAL_SUPLIER

NVAL_SUPLIER

AL_SUPLIER File Name PURCHASE_UNVAL.lst Table name QLT_UNVLDTD_SUPLR_DTL_S

TRANSFORMATION

SPECIFICATIONS

TARGET Column Name

SOURCE Data Type

Length

FACILITY

VARCHAR

50

BUSINESS

VARCHAR

50

MODLTY

VARCHAR

50

UNVLDTD_SUPLR_FW

VARCHAR

50

VARCHAR

50

TOTL_SUPLR_CNT TOTL_UNQLFD_SUPLR_CN MANL_SRC_NM

SRC_NM

VARCHAR

50

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

50

SRC_CRETN_ID

VARCHAR

50


TIMESTAMP 0 VARCHAR

50

SRC_UPD_TS

TIMESTAMP 0

ODS_POSTNG_TS

TIMESTAMP 0

ODS_UPD_TS

TIMESTAMP 0

E_APRVD_SUPLIER

PRVD_SUPLIER

VD_SUPLIER File Name

TCS Confidential

PK

FK

System

PURCHASE_APPROVED.lst Table name QLT_APRVD_SUPLR_DTL_S

TRANSFORMATION

SPECIFICATIONS

TARGET Column Name

SOURCE Data Type

Length

SUPLR_AUDT_ID

VARCHAR

50

BUSINESS

VARCHAR

50

MODLTY

VARCHAR

50

SUPLR_SIT_ID

VARCHAR

50

SUPLR_SIT_NAM

VARCHAR

50

CATGRZTN

VARCHAR

50

AUDT_TYP

VARCHAR

50

LAST_AUDT_DT

TIMESTAMP 6

TARGT_AUDT_DT

TIMESTAMP 6

COMPLT_DT

TIMESTAMP 6

STATUS_CD

VARCHAR

50

ADT_SCOR

VARCHAR

50

MANL_SRC_NM

VARCHAR

50

SRC_NM

VARCHAR

50

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

50

SRC_CRETN_ID

VARCHAR

50


TIMESTAMP 0 VARCHAR

50

SRC_UPD_TS

TIMESTAMP 0

ODS_POSTNG_TS

TIMESTAMP 0

ODS_UPD_TS

TIMESTAMP 0

schedld_audt_dt

TCS Confidential

VARCHAR

10

PK

FK

System

SOURCE FILE

Column Name

Data Type

Length

Business Rule

PURCHASE_UNVAL.lst Site

VARCHAR

Business

VARCHAR

250 1 to 1 250 1 to 1 Hard Coded'99999' NULL

Total SuppliersVARCHAR VARCHAR Total Unvalidated Suppliers

250 1 to 1 250 1 to 1 Hard coded "http://suppo rtcentral.ge.c om/products/ sup_product s"

Data Source VARCHAR

250 1 to 1 Hard coded "PURCHASE_UNVAL.lst"

VARCHAR Data Entered by VARCHAR Data Entered Date VARCHAR Data Entered by VARCHAR Data Entered date

TCS Confidential

Hard coded "m_WL_QLT_UNVLDTD_SUPLR_DTL_S_TRUNC_INS_SD" 250 1 to 1 250 1 to 1 (convert the string into date) 250 1 to 1 250 1 to 1 (convert the string into date) SESSSTAR TTIME SESSSTAR TTIME

SOURCE FILE

Column Name

Data Type

PURCHASE_APPROVED.lst VARCHAR AuditID Business

VARCHAR

Length

Business Rule

250 1 to 1 250 1 to 1 Hard Coded'99999'

VARCHAR Supplier Site ID Supplier NameVARCHAR

250 1 to 1 250 1 to 1

CategorizationVARCHAR Audit Type VARCHAR

250 1 to 1 250 1 to 1

VARCHAR Last Audit Date** VARCHAR Audit Target Date**


VARCHAR Complete Date** VARCHAR Status**

250 1 to 1 (convert the string into date) 250 1 to 1

Audit Score** VARCHAR

250 1 to 1 Hard coded "http://suppo rtcentral.ge.c om/products/ sup_product s"

Data Source VARCHAR

250 1 to 1


Hard coded "PURCHASE_APPROVED.lst" VARCHAR Data Entered by VARCHAR Data Entered Date VARCHAR Data Entered by VARCHAR Data Entered date

VARCHAR Scheduled Audit Date**

TCS Confidential

Hard coded "m_WL_QLT_APRVD_SUPLR_DTL_S_TRUNC_INS_SD" 250 1 to 1 250 1 to 1 (convert the string into date) 250 1 to 1 250 1 to 1 (convert the string into date) SESSSTAR TTIME SESSSTAR TTIME 250 1 to 1 (convert the string into date)

LR_DTL_S_TRUNC_INS_SD"

TCS Confidential

_DTL_S_TRUNC_INS_SD"

TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_COMPLNT_AUDT_I_INS_PURCHASE_APRVD_SUPLIER

Session Name

s_m_WL_QLT_COMPLNT_AUDT_I_INS_PURCHASE_APRVD_SUPLIER

Mapping Name

m_WL_QLT_COMPLNT_AUDT_I_INS_PURCHASE_APRVD_SUPLIER

Source Tables

Database FDS_ETL_TARGET(Teradata)

Target Tables


Load Type

Insert

Join Condition

n/a



FDS_ETL_TARGET QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I

TCS Confidential

SPE

QLT_COMPLNT_AUDT_I


QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I

TCS Confidential

TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_COMPLNT_AUDT_I_INS_PURCHASE_UNVAL_SUPLIER

Session Name

s_m_WL_QLT_COMPLNT_AUDT_I_INS_PURCHASE_UNVAL_SUPLIER

Mapping Name

m_WL_QLT_COMPLNT_AUDT_I_INS_PURCHASE_UNVAL_SUPLIER

Source Tables


Target Tables


Load Type

Insert

Join Condition

n/a

TRANSFORMATIONSPEC TARGET System

TCS Confidential


QLT_COMPLNT_AUDT_I

FDS_ETL_TARGET QLT_COMPLNT_AUDT_I


QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I


QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I

TCS Confidential


TCS Confidential

TCS Confidential

TCS Confidential

HASE_APRVD_SUPLIER

E_APRVD_SUPLIER

APRVD_SUPLIER Table Name QLT_APRVD_SUPLR_DTL_S Table name QLT_COMPLNT_AUDT_I

TRANSFORMATION

SPECIFICATIONS

TARGET Column Name

SOURCE Data Type

Length

SRC_SYS_ID

VARCHAR

100

SRC_NM

VARCHAR

50

SITE

VARCHAR

50

MODALITY

VARCHAR

50

BUSINESS

VARCHAR

50

SRC_COLMN

VARCHAR

50

SRC_COLMN_VAL

VARCHAR

50

TCS Confidential

PK

FK

System

SRC_COLMN_DT

DATE

9

STC_UPDT_ID

VARCHAR

50

SRC_CRETN_ID

VARCHAR

50

SRC_UPD_TS

TIMESTAMP 0

SRC_CRETN_TS

TIMESTAMP 0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

51

ODS_POSTNG_TS

TIMESTAMP 0

ODS_UPD_TS

TIMESTAMP 0

TCS Confidential

TCS Confidential

HASE_UNVAL_SUPLIER

E_UNVAL_SUPLIER

UNVAL_SUPLIER Table Name QLT_UNVLDTD_SUPLR_DTL_S Table name QLT_COMPLNT_AUDT_I

TRANSFORMATIONSPECIFICATIONS TARGET Column Name

TCS Confidential

SOURCE Data Type

Length

PK

FK

System

SRC_SYS_ID

VARCHAR

100

SRC_NM

VARCHAR

50

SITE

VARCHAR

50

MODALITY

VARCHAR

50

BUSINESS

VARCHAR

50

SRC_COLMN

VARCHAR

50

SRC_COLMN_VAL

VARCHAR

50

SRC_COLMN_DT

DATE

9

STC_UPDT_ID

VARCHAR

50

SRC_CRETN_ID

VARCHAR

50

SRC_UPD_TS

TIMESTAMP 0

SRC_CRETN_TS

TIMESTAMP 0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

51

TCS Confidential

ODS_POSTNG_TS

TIMESTAMP 0

ODS_UPD_TS

TIMESTAMP 0

TCS Confidential

TCS Confidential

TCS Confidential

SOURCE TABLE

Column Name

Data Type

Length

Business Rule

QLT_APRVD_SUPLR_DTL_S conactinatio n of Audit ID and BUSINESS with the symbole ~ Hard Coded "QltApprovedSupplierManual"

MODALITY

VARCHAR

251

Default '99999' 1 to 1

BUSINESS

VARCHAR

251

1 to 1 Look up the column name from QLT_SORC _COLMN_D based on the Column number and sorce name values of the sorce columns will be populated as rows by using normalizer

TCS Confidential



250 1 to 1 250 1 to 1




250 1 to 1 (convert the string into date) hard coded " QLT_APRV D_SUPLR_ DTL_S "


Mapping Name hard coded "m_WL_QLT _COMPLNT _AUDT_I_IN S_PURCHA SE_APRVD _SUPLIER"

VARCHAR

SUPLR_AUDT_ID VARCHAR

SUPLR_SIT_ID VARCHAR

SUPLR_SIT_NAM

TCS Confidential




VARCHAR


VARCHAR


VARCHAR


CATGRZTN

AUDT_TYP

LAST_AUDT_DT VARCHAR

TARGT_AUDT_DT VARCHAR

SCHEDLD_AUDT_DT VARCHAR

COMPLT_DT

TCS Confidential




VARCHAR


VARCHAR


VARCHAR


STATUS_CD

ADT_SCOR

MANL_SRC_NM

SOURCE TABLE

TCS Confidential

Column Name

Data Type

Length

Business Rule

QLT_UNVL DTD_SUPL R_DTL_S

VARCHAR

BUSINESS

VARCHAR

conactinatio n of Facility and BUSINESS with the 250 symbole ~ hard coded "QltUnvalidatedSupplierManual" Default '99999' Default '99999' 250 1 to 1 Look up the column name from QLT_SORC _COLMN_D based on the Column number and sorce name values of the sorce columns will be populated as rows by using normalizer Respected date values has to be populated as saparate rows by using normalizer



250 1 to 1 250 1 to 1 250 1 to 1 (convert the string into date) 1 to 1 (convert the string into 250 date) hard coded "QLT_UNVL DTD_SUPL R_DTL_S" hard code Mapping Name "m_WL_QLT _COMPLNT _AUDT_I_IN S_PURCHA SE_UNVAL_ SUPLIER"

TCS Confidential

SESSSTAR TTIME SESSSTAR TTIME this source column will be populated as a row in the taget table column SRC_COLM N_VAL TOTL_SUPLR_NBR VARCHAR


TOTL_UNQLFD_SUPLR VARCHAR


MANL_SRC_NM VARCHAR

TCS Confidential

250

TCS Confidential

TCS Confidential

Legend

Oracle Table


Flat File


Lookup table

OUTPUT_MDR_AER_CS_DTL.CSV


DEL_INS

1


GCD_DAT_SORC_D

OUTPUT_RECALLS.CSV

DEL_INS

2


GCD_DAT_SORC_D

OUTPUT_483.CSV

DEL_INS

3


GCD_DAT_SORC_D

OUTPUT_REG_ENFORCEMENT.CSV

DEL_INS

4

Flat File Table :

QLT_SRC_COLMN_D

GCD_DAT_SORC_D Falt File to InterFace table Step 1:

2.Informatica Mappings are used to load the Data from Flat File to Interface table From Falt File to Inter Face Step 2:


Step 3:



ODS (Teradata)

DEL_INS

7

OUTPUT_MFR.CSV

Flat File

QLT_SRC_COLMN_D

QLT_SRC_COLMN_D

GCD_DAT_SORC_D

GCD_DAT_SORC_D

DEL_INS

6

OUTPUT_IFR.CSV

Flat File

QLT_SRC_COLMN_D

GCD_DAT_SORC_D

QLT_SRC_COLMN_D

QLT_COMPLNT_AU DT_I

GCD_DAT_SORC_D

DEL_INS

5

Flat File

QLT_SRC_COLMN_D QLT_SRC_COLMN_D

GCD_DAT_SORC_D GCD_DAT_SORC_D

QLT_SRC_COLMN_D

GCD_DAT_SORC_D

OUTPUT_eDHR.CSV

OUTPUT_MFR.CSV

Flat File

OUTPUT_IFR.CSV

Flat File

OUTPUT_eDHR.CSV

Flat File

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_COMPLNT_AUDT_I_MDR_AER_INS

Session Name

s_m_WL_QLT_COMPLNT_AUDT_I_MDR_AER_INS

Mapping Name

m_WL_QLT_COMPLNT_AUDT_I_MDR_AER_INS

Source Tables

Database

Target Tables


Load Type

Insert n/a

Join Condition SQ Query

TRANSFORMATIO TARGET System



QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I


QLT_COMPLNT_AUDT_I


Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_COMPLNT_AUDIT_I_RECALLS_INS

Session Name

s_m_WL_QLT_COMPLNT_AUDIT_I_RECALLS_INS

Mapping Name

m_WL_QLT_COMPLNT_AUDIT_I_RECALLS_INS

Source Tables

Database

Target Tables


Load Type

Insert n/a

Join Condition

TRANSFORMAT TARGET System

Table/Dimension/Fact QLT_COMPLNT_AUDT_I



QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I


QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I


Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_COMPLNT_AUDIT_I_483_INS

Session Name

s_m_WL_QLT_COMPLNT_AUDIT_I_483_INS

Mapping Name

m_WL_QLT_COMPLNT_AUDIT_I_483_INS

Source Tables

Database

Target Tables


Load Type

Insert n/a

Join Condition





QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I


QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I


Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_COMPLNT_AUDIT_I_REG_ENFORCEMENT_INS

Session Name

s_m_WL_QLT_COMPLNT_AUDIT_I_REG_ENFORCEMENT_INS

Mapping Name

m_WL_QLT_COMPLNT_AUDIT_I_REG_ENFORCEMENT_INS

Source Tables

Database

Target Tables


Load Type

Insert n/a

Join Condition



SRC_ETL_TARGET

QLT_COMPLNT_AUDT_I


QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I


QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I


Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_COMPLNT_AUDIT_I_eDHR_INS

Session Name

s_m_WL_QLT_COMPLNT_AUDIT_I_eDHR_INS

Mapping Name

m_WL_QLT_COMPLNT_AUDIT_I_eDHR_INS

Source Tables

Database

Target Tables


Load Type

Insert n/a

Join Condition






QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I


QLT_COMPLNT_AUDT_I

QLT_COMPLNT_AUDT_I


File name OUTPUT_MDR_AER_CS_DTL.CSV


TRANSFORMATION

SPECIFICATIONS

TARGET

SOUR

Column Name

Data Type

Length

SRC_SYS_ID

VARCHAR

100

SRC_NM

VARCHAR

50

SITE

VARCHAR

50

MODALITY

VARCHAR

50

P_L

VARCHAR

50

PK

FK

System

SRC_COLMN

VARCHAR

50

SRC_COLMN_VAL

VARCHAR

50

SRC_COLMN_DT

DATE

9

STC_UPDT_ID

VARCHAR

50

SRC_CRETN_ID

VARCHAR

50

SRC_UPD_TS

TIMESTAMP 0

SRC_CRETN_TS

TIMESTAMP 0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

51

ODS_POSTNG_TS

TIMESTAMP 0

ODS_UPD_TS

TIMESTAMP 0

File name OUTPUT_RECALLS.CSV


TRANSFORMATIONSPECIFICATIONS TARGET

SOUR

Column Name

Data Type

Length

SRC_SYS_ID

VARCHAR

100

SRC_NM

VARCHAR

50

SITE

VARCHAR

50

MODALITY

VARCHAR

50

P_L

VARCHAR

50

SRC_COLMN

VARCHAR

50

SRC_COLMN_VAL

VARCHAR

50

PK

FK

System

SRC_COLMN_DT

DATE

9

STC_UPDT_ID

VARCHAR

50

SRC_CRETN_ID

VARCHAR

50

SRC_UPD_TS

TIMESTAMP 0

SRC_CRETN_TS

TIMESTAMP 0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

51

ODS_POSTNG_TS

TIMESTAMP 0

ODS_UPD_TS

TIMESTAMP 0

File name OUTPUT_483.CSV


TRANSFORMATION

SPECIFICATIONS

TARGET

SOUR

Column Name

Data Type

Length

SRC_SYS_ID

VARCHAR

100

SRC_NM

VARCHAR

50

PK

FK

System

SITE

VARCHAR

50

MODALITY

VARCHAR

50

P_L

VARCHAR

50

SRC_COLMN

VARCHAR

50

SRC_COLMN_VAL

VARCHAR

50

SRC_COLMN_DT

DATE

9

STC_UPDT_ID

VARCHAR

50

SRC_CRETN_ID

VARCHAR

50

SRC_UPD_TS

TIMESTAMP 0

SRC_CRETN_TS

TIMESTAMP 0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

51

ODS_POSTNG_TS

TIMESTAMP 0

ODS_UPD_TS

TIMESTAMP 0

File name OUTPUT_REG_ENFORCEMENT.CSV


TRANSFORMATION

SPECIFICATIONS

TARGET

SOUR

Column Name

Data Type

SRC_SYS_ID

VARCHAR

Length 100

PK

FK

System

SRC_NM

VARCHAR

50

SITE

VARCHAR

50

MODALITY

VARCHAR

50

P_L

VARCHAR

50

SRC_COLMN

VARCHAR

50

SRC_COLMN_VAL

VARCHAR

50

SRC_COLMN_DT

DATE

9

STC_UPDT_ID

VARCHAR

50

SRC_CRETN_ID

VARCHAR

50

SRC_UPD_TS

TIMESTAMP 0

SRC_CRETN_TS

TIMESTAMP 0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

51

ODS_POSTNG_TS

TIMESTAMP 0

ODS_UPD_TS

TIMESTAMP 0

File name OUTPUT_eDHR.CSV


TRANSFORMATION

SPECIFICATIONS

TARGET

SOUR

Column Name

Data Type

Length

SRC_SYS_ID

VARCHAR

100

SRC_NM

VARCHAR

50

SITE

VARCHAR

50

MODALITY

VARCHAR

50

PK

FK

System

P_L

VARCHAR

50

SRC_COLMN

VARCHAR

50

SRC_COLMN_VAL

VARCHAR

50

SRC_COLMN_DT

DATE

9

STC_UPDT_ID

VARCHAR

50

SRC_CRETN_ID

VARCHAR

50

SRC_UPD_TS

TIMESTAMP 0

SRC_CRETN_TS

TIMESTAMP 0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

51

ODS_POSTNG_TS

TIMESTAMP 0

ODS_UPD_TS

TIMESTAMP 0

FILE



OUTPUT_MDR_AER_CS_DTL.CSV conactination of PSR,MDR_A ER_FALG and COUNTRY_ REPORT_T O with the symbole ~ Hard Coded "Qlt-MDRAERManualcona ctination of PSR,MDR_A ER_FALG and COUNTRY_ REPORT_T O with the symbole ~

MODALITYVARCHAR PL

251

VARCHAR 251

Default '99999' 1 to 1 1 to 1


DATA_ENTERED_BY VARCHAR DATA_ENTERED_BY VARCHAR DATA_ENTERED_DATE VARCHAR DATA_ENTERED_DATE VARCHAR

250 250 250 250

1 to 1 1 to 1

1 to 1 (convert the string into date) 1 to 1 (convert the string into date) hard coded "OUTPUT_M DR_AER_C S_DTL.CSV" hard code Mapping Name hard coded "OUTPUT_M DR_AER_C S_DTL.CSV"

VARCHAR

PSR


VARCHAR


MDR_AER_FLAG VARCHAR


COUNTRY_REPORT_TO VARCHAR


MDR_AER_REPORT_NUMBER VARCHAR


VARCHAR


PRODUCT

MODALITY_SEGMNET

VARCHAR


EVENT_TYPE VARCHAR


REPORT_TYPE VARCHAR


VARCHAR


INJURY

MDR_AER_FILED_DATE VARCHAR

DATA_SOURCE


FILE



OUTPUT_R ECALLS.CS V

apply the trim function to remove spaces

RECALL_CORRECTION_ID VARCHAR 250 hard coded "Qlt-RecallManual"

PL

VARCHAR

Default '99999' Default '99999' 250 1 to 1 Look up the column name from QLT_SORC _COLMN_D based on the Column number and sorce name values of the sorce columns will be populated as rows by using normalizer




250 1 to 1 250 1 to 1 250 1 to 1 (convert the string into date) 1 to 1 (convert the string into 250 date) hard coded "OUTPUT_R ECALLS.CS V" hard code Mapping Name "m_WL_QLT _COMPLNT _AUDIT_I_R ECALLS_IN S" SESSSTAR TTIME SESSSTAR TTIME this source column will be populated as a row in the taget table column SRC_COLM N_VAL

FISCAL_WEEK VARCHAR


PRODUCT VARCHAR

250


UNITS

VARCHAR


RESOLUTION VARCHAR


OWNER

VARCHAR


OWNER_SSO VARCHAR


TIMING

VARCHAR

250


RECALL

VARCHAR


DATA_SOURCE VARCHAR

FILE


250

Length Business Rule apply the trim function to remove spaces

OUTPUT_483.CSV INSPECTION_ID VARCHAR

250 Hard Coded"Qlt483sManual"

MODALITYVARCHAR PL

VARCHAR

Default '99999' 250 1 to 1 250 1 to 1 Look up the column name from QLT_SORC _COLMN_D based on the Column number and sorce name values of the sorce columns will be populated as rows by using normalizer Respected date values has to be populated as saparate rows by using normalizer


250 1 to 1 250 1 to 1 250 1 to 1 (convert the string into date)


1 to 1 (convert the string into 250 date) hard coded "OUTPUT_4 83.CSV" hard code Mappin Name "m_WL_QLT _COMPLNT _AUDIT_I_4 83_INS" SESSSTAR TTIME SESSSTAR TTIME


FACILITY VARCHAR


START_DATE VARCHAR


END_DATEVARCHAR


INSPECTOR VARCHAR


OBSERVATION VARCHAR

250


CFR_CITATION VARCHAR


DATA_SOURCE VARCHAR

FILE


OUTPUT_REG_ENFORCEMENT.CSV

250

Length Business Rule conactination of PL,FACILIT Y and START_DAT E with the symbole ~

Hard Coded "QltRegulatoryEnforcementManual"

MODALITYVARCHAR PL

VARCHAR

Default '99999' 250 1 to 1 250 1 to 1 Look up the column name from QLT_SORC _COLMN_D based on the Column number and sorce name values of the sorce columns will be populated as rows by using normalizer Respected date values has to be populated as saparate rows by using normalizer



250 1 to 1 250 1 to 1 250 1 to 1 (convert the string into date) 1 to 1 (convert the string into 250 date) hard coded "OUTPUT_R EG_ENFOR CEMENT.CS V"

hard code Mapping Name"m_W L_QLT_CO MPLNT_AU DIT_I_REG_ ENFORCEM ENT_INShar d coded "OUTPUT_R EG_ENFOR CEMENT.CS V" SESSSTAR TTIME SESSSTAR TTIME this source column will be populated as a row in the taget table column SRC_COLM N_VAL

FACILITY VARCHAR


START_DATE VARCHAR


END_DATEVARCHAR


INSPECTOR VARCHAR

250


ENFORCEMENT_TYPE VARCHAR


DATA_SOURCE VARCHAR

FILE


250

Length Business Rule conactination of FACILITY and PL with the symbole ~

OUTPUT_eDHR.CSV Hard Coded "Qlt-eDHRRolloutManual" Default '99999' Default '99999'

PL

VARCHAR

250 1 to 1 Look up the column name from QLT_SORC _COLMN_D based on the Column number and sorce name values of the sorce columns will be populated as rows by using normalizer Respected date values has to be populated as saparate rows by using normalizer



250 1 to 1 250 1 to 1 250 1 to 1 (convert the string into date) 1 to 1 (convert the string into 250 date) hard coded " PRODUCTI ON_EDHR_I MPL.CSV" hard code Mapping Name"m_W L_QLT_CO MPLNT_AU DIT_I_eDHR _INS" SESSSTAR TTIME SESSSTAR TTIME


FACILITY VARCHAR


MES_IMPLEMENTATION_STATUS VARCHAR 250 this source column will be populated as a row in the taget table column SRC_COLM N_VAL

SITE_USING_MES VARCHAR


TARGET_IMPLEMENTAION_DATE VARCHAR 250 this source column will be populated as a row in the taget table column SRC_COLM N_VAL

RESPONSIBLE_EXEC VARCHAR

250


PRODUCT_MANUFACTURED VARCHAR 250 this source column will be populated as a row in the taget table column SRC_COLM N_VAL

DATA_SOURCE VARCHAR

250

Folder Name


Bteq Name

BTEQ_QLT_COMP_AUDT_FS_INTR_STG_INS.sh

Source Tables

Database

File name


QLT_COMPLNT_AUDT_I

Target Tables

Load Type

Database

Table name


QLT_COMPLNT_AUDT_FS

Insert

Join Condition

SQ Query

TRANSFO TARGET System


Column Name

SRC_ETL_TARGET QLT_COMPLNT_AUDT_FS

QLT_COMPLNT_AUDT_IDN

QLT_COMPLNT_AUDT_FS

SRC_SYS_ID

QLT_COMPLNT_AUDT_FS

QLT_SRC_COLMN_VAL

QLT_COMPLNT_AUDT_FS

QLT_SRC_COLMN_DT

QLT_COMPLNT_AUDT_FS

TIM_MAIN_DT

QLT_COMPLNT_AUDT_FS

PL_ORG_MAIN_IDN

QLT_COMPLNT_AUDT_FS

MODLTY_ORG_MAIN_IDN

QLT_COMPLNT_AUDT_FS

SIT_ORG_MAIN_IDN

TCS Confidential

QLT_COMPLNT_AUDT_FS

GCD_CATGRY_REL_IDN

QLT_COMPLNT_AUDT_FS

PARENT_AREA_IDN

QLT_COMPLNT_AUDT_FS

CHILD_AREA_IDN

QLT_COMPLNT_AUDT_FS

QLT_SORC_COLMN_IDN

QLT_COMPLNT_AUDT_FS QLT_COMPLNT_AUDT_FS

SRC_IDN SRC_UPD_IDN

QLT_COMPLNT_AUDT_FS

SRC_UPD_TS

QLT_COMPLNT_AUDT_FS

SRC_CRETN_IDN

QLT_COMPLNT_AUDT_FS

SRC_CRETN_TS

QLT_COMPLNT_AUDT_FS

DAT_ORGN

QLT_COMPLNT_AUDT_FS

POSTNG_AGNT

QLT_COMPLNT_AUDT_FS QLT_COMPLNT_AUDT_FS


TCS Confidential

TRANSFORMATION

SPECIFICATIONS SOURCE

Data Type

Length

PK

FK

System

FILE

Column Name

Data Type

QLT_COMPLNT_AUDT_I DECIMAL

15

VARCHAR

250

VARCHAR

250

SRC_COLMN_VAL

VARCHAR VARCHAR

DATE

9

SRC_COLMN_DT

DATE

DATE

9

DECIMAL

15

DECIMAL

15

DECIMAL

15

SRC_SYS_ID

TCS Confidential

DECIMAL

15

DECIMAL

15

DECIMAL

15

DECIMAL

15

DECIMAL DECIMAL

15 15

TIMESTAMP 0 DECIMAL

15

SRC_CRETN_ID

VARCHAR

DAT_ORGN

VARCHAR

TIMESTAMP 0 VARCHAR

50

VARCHAR

50

TIMESTAMP 0 TIMESTAMP 0

TCS Confidential

Repeat?

Implementation Category

Length

Business Rule

Max Of QLT_COMPLNT_AUDT_IDN from QLT_COMPLNT_AUDT_F

1 to 1

250 250

1 to 1

9

1 to 1 MAX(TIM_MAIN_DT) TIM_MAIN_DT from TIM_MAIN_DT_D TEMP, (select cal_wk_num,cal_yr_num from TIM_MAIN_DT_D where tim_main_dt=current_date)SUB WHERE TEMP.cal_wk_num=SUB.CAL_WK_NUM AND TEMP.cal_yr_num=SUB.cal_yr_num COALESCE(TRIM(TRAILING '.' FROM PL_IDN.PL_ORG_MAIN_IDN),'-99999') PL_MAIN_IDN COALESCE(TRIM(TRAILING '.' FROM MODALITY_IDN.MOD_ORG_IDN),'-99999' COALESCE(TRIM(TRAILING '.' FROM SITE_IDN.SITE_MAIN_IDN),'-99999'

TCS Confidential

Problem Statement Action PlanAuditor/Contact Contact Phone Implementation Complete

Populated with Default '-99999' Populated with Default '-99999' Populated with Default '-99999' (SEL C.QLT_SRC_COLMN_IDN QLT_SRC_COLMN_IDN,C.SRC_SYS_ID SRC_SYS_ID , B.SORC_NM SRC_NM,C.SRC_IDN SRC_IDN FROM GCD_DAT_SORC_D B,QLT_SRC_COLMN_D C WHERE C.SRC_IDN=B.DATA_SORC_IDN) SUB

COALESCE(SUB.SRC_IDN,9999) Populated with Default '-99999' current_timestamp(0)

50 1 to 1 current_timestamp(0) 50

1 to 1 BTEQ_QLT_COMPLNT_ADT_INS_INTR_STG' current_timestamp(0) row in the taget table column SRC_COLMN_VAL current_timestamp(0) row in the taget table column SRC_COLMN_VAL

TCS Confidential

Implementation Implemented Finding By Person Status Implementation Closure DueVerification Date Effectiveness Status Actual Determination Effectiveness Effectiveness Person Determination Effectiveness Determination Date Determination Due Date

TCS Confidential

TCS Confidential

CAPA Required Investigation Root CauseEffectivity Date Effectivity Finding Reference Reference ID Update History Days Open / To Close

TCS Confidential

TCS Confidential


TCS Confidential

Folder Name


Bteq Name

BTEQ_QLT_COMPLNT_AUDT_F_INS.sh

Source Tables

Database

File name


QLT_COMPLNT_AUDT_FS

Target Tables

Load Type

Database

Table name


QLT_COMPLNT_AUDT_F

Insert

Join Condition

SQ Query

TRANSF TARGET System


Column Name

SRC_ETL_TARGET QLT_COMPLNT_AUDT_F


QLT_COMPLNT_AUDT_F

SRC_SYS_ID

QLT_COMPLNT_AUDT_F

QLT_SRC_COLMN_VAL

QLT_COMPLNT_AUDT_F

QLT_SRC_COLMN_DT

QLT_COMPLNT_AUDT_F

TIM_MAIN_DT

QLT_COMPLNT_AUDT_F

PL_ORG_MAIN_IDN

QLT_COMPLNT_AUDT_F

MODLTY_ORG_MAIN_IDN

QLT_COMPLNT_AUDT_F

SIT_ORG_MAIN_IDN

TCS Confidential

QLT_COMPLNT_AUDT_F

GCD_CATGRY_REL_IDN

QLT_COMPLNT_AUDT_F

PARENT_AREA_IDN

QLT_COMPLNT_AUDT_F

CHILD_AREA_IDN

QLT_COMPLNT_AUDT_F

QLT_SORC_COLMN_IDN

QLT_COMPLNT_AUDT_F QLT_COMPLNT_AUDT_F

SRC_IDN SRC_UPD_IDN

QLT_COMPLNT_AUDT_F

SRC_UPD_TS

QLT_COMPLNT_AUDT_F

SRC_CRETN_IDN

QLT_COMPLNT_AUDT_F

SRC_CRETN_TS

QLT_COMPLNT_AUDT_F

DAT_ORGN

QLT_COMPLNT_AUDT_F

POSTNG_AGNT

QLT_COMPLNT_AUDT_F QLT_COMPLNT_AUDT_F


TCS Confidential

TRANSFORMATION

SPECIFICATIONS SOURCE

Data Type

Length

PK

FK

System

FILE

Column Name QLT_COMPLNT_AUDT_FS

DECIMAL

15


VARCHAR

250

SRC_SYS_ID

VARCHAR

250

QLT_SRC_COLMN_VAL

DATE

9

QLT_SRC_COLMN_DT

DATE

9

TIM_MAIN_DT

DECIMAL

15

PL_ORG_MAIN_IDN

DECIMAL

15

MODLTY_ORG_MAIN_IDN

DECIMAL

15

SIT_ORG_MAIN_IDN

TCS Confidential

DECIMAL

15

GCD_CATGRY_REL_IDN

DECIMAL

15

PARENT_AREA_IDN

DECIMAL

15

CHILD_AREA_IDN

DECIMAL

15

QLT_SORC_COLMN_IDN

DECIMAL DECIMAL

15 15

SRC_IDN SRC_UPD_IDN

TIMESTAMP 0

SRC_UPD_TS

DECIMAL

SRC_CRETN_IDN

15

TIMESTAMP 0

SRC_CRETN_TS

VARCHAR

50

DAT_ORGN

VARCHAR

50

POSTNG_AGNT


TCS Confidential


Repeat?

Data Type

Implementation Category

Length

Business Rule

DECIMAL

15

1 to 1

VARCHAR

250

1 to 1

VARCHAR

250

1 to 1

DATE

9

1 to 1

DATE

9

1 to 1

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

TCS Confidential

Problem Statement Action PlanAuditor/Contact Contact Phone

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

DECIMAL DECIMAL

15 15

1 to 1 1 to 1

TIMESTAMP 0

1 to 1

DECIMAL

1 to 1

15

TIMESTAMP 0

1 to 1

VARCHAR

50

1 to 1

VARCHAR

50

1 to 1


TCS Confidential

1 to 1 1 to 1

Implementation Implementation Complete Implemented Date Finding By Person Status Implementation Closure DueVerification Date Effectiveness Status Actual Determination Effectiveness Effectiveness Person Determination Determination DateDue D

TCS Confidential

TCS Confidential

Effectiveness CAPA Determination Required Investigation Root CauseEffectivity Date Effectivity Finding Reference Reference ID Update History

TCS Confidential

TCS Confidential

Days Open Days / To Close Past Closure Due

TCS Confidential

Folder Name

OQM_910_QualityMetrics

WorkFlow Name

wkf_s_m_WL_QLT_MATRX_SUMM_AS_INS_ITRAK_COMPLAINT_OPEN

Session Name

s_m_WL_QLT_MATRX_SUMM_AS_INS_ITRAK_COMPLAINT_OPEN

Mapping Name

m_WL_QLT_MATRX_SUMM_AS_INS_ITRAK_COMPLAINT_OPEN

Source Tables


Target Tables


Load Type

Insert

Join Condition

SQ Query

TRANS TARGET System


SRC_ETL_TARGET QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

TCS Confidential

QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_MATRX_SUMM_AS_INS_ITRAK_COMPLAINT_NEW

Session Name

s_m_WL_QLT_MATRX_SUMM_AS_INS_ITRAK_COMPLAINT_NEW

Mapping Name

m_WL_QLT_MATRX_SUMM_AS_INS_ITRAK_COMPLAINT_NEW

TCS Confidential

Source Tables


Target Tables


Load Type

Insert


TRANS TARGET System


SRC_ETL_TARGET QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_MATRX_SUMM_AS_INS_ITRAK_COMPLAINT_CLOSED

Session Name

s_m_WL_QLT_MATRX_SUMM_AS_INS_ITRAK_COMPLAINT_CLOSED

Mapping Name

m_WL_QLT_MATRX_SUMM_AS_INS_ITRAK_COMPLAINT_CLOSED

TCS Confidential

Source Tables


Target Tables


Load Type

Insert


TRANS TARGET System


SRC_ETL_TARGET QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS

TCS Confidential

K_COMPLAINT_OPEN

OMPLAINT_OPEN

MPLAINT_OPEN

Table name QLT_COMPLNTS_DTL_F_V

Table name QLT_MATRX_SUMM_AS

TRANSFORMATION

SPECIFICATIONS

TARGET Column Name

QLT_MATRX_SUMM_IDN

QLT_MATRX_IDN BUS_ORG_MAIN_IDN DECIMAL(15,0) MODLTY_ORG_MAIN_IDN DECIMAL(15,0) SIT_ORG_MAIN_IDN

SOURCE Data Type

Length

DECIMAL

15

DECIMAL

15

DECIMAL

15

DECIMAL

15

DECIMAL

15

TIM_MAIN_DT

DATE

MATRX_VAL

VARCHAR

50

SRC_IDN

DECIMAL

15

TCS Confidential

PK

FK

System

SRC_SYS_ID

VARCHAR

250

SRC_CRETN_IDN DECIMAL(15,0) SRC_CRETN_TS

DECIMAL

15

SRC_UPD_IDN

DECIMAL

ODS_UPD_TS DAT_ORGN

TIMESTAMP 0 VARCHAR 50

POSTNG_AGNT

VARCHAR

ODS_POSTNG_TS

TIMESTAMP 0

SRC_UPD_TS

TIMESTAMP 0

K_COMPLAINT_NEW

OMPLAINT_NEW

MPLAINT_NEW

TCS Confidential

TIMESTAMP 0 15

50



TRANSFORMATION TARGET Column Name

QLT_MATRX_SUMM_IDN

SOURCE Data Type

Length

DECIMAL

15

QLT_MATRX_IDN

DECIMAL

15

BUS_ORG_MAIN_IDN

DECIMAL

15

MODLTY_ORG_MAIN_IDN

DECIMAL

15

SIT_ORG_MAIN_IDN

DECIMAL

15

TIM_MAIN_DT

DATE

MATRX_VAL

VARCHAR

50

SRC_IDN

DECIMAL

15

SRC_SYS_ID

VARCHAR

250

SRC_CRETN_IDN

DECIMAL

15

SRC_CRETN_TS

TIMESTAMP 0

SRC_UPD_IDN

DECIMAL

ODS_UPD_TS

TIMESTAMP 0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

50

ODS_POSTNG_TS

TIMESTAMP 0

SRC_UPD_TS

TIMESTAMP 0

K_COMPLAINT_CLOSED

OMPLAINT_CLOSED

MPLAINT_CLOSED

TCS Confidential

SPECIFICATIONS

15

PK

FK

System



TRANSFORMATION TARGET Column Name

QLT_MATRX_SUMM_IDN

SOURCE Data Type

Length

DECIMAL

15

DECIMAL

15

BUS_ORG_MAIN_IDN DECIMAL DECIMAL

15

QLT_MATRX_IDN

MODLTY_ORG_MAIN_IDN DECIMAL SIT_ORG_MAIN_IDN

DECIMAL

15

DECIMAL

15

TIM_MAIN_DT

DATE

MATRX_VAL

VARCHAR

50

SRC_IDN

DECIMAL

15

SRC_SYS_ID

VARCHAR

250


DECIMAL

15

SRC_UPD_IDN

DECIMAL

ODS_UPD_TS

TIMESTAMP 0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

50

ODS_POSTNG_TS

TIMESTAMP 0

SRC_UPD_TS

TIMESTAMP 0

TCS Confidential

SPECIFICATIONS

TIMESTAMP 0 15

PK

FK

System

SPECIFICATIONS SOURCE FILE

Column Name

Data Type

Length

QLT_COMPLNTS_DTL_F_V

TCS Confidential

DECIMAL

15

QLT_MATRX_IDN

DECIMAL

15

BUS_ORG_MAIN_IDN DECIMAL(15,0) MODLTY_ORG_MAIN_IDN DECIMAL(15,0) SIT_ORG_MAIN_IDN

DECIMAL

15

DECIMAL

15

DECIMAL

15

MATRX_LAST_REFRSH_DT

DECIMAL

15

Value of each matrx

DECIMAL

15

DATA_SORC_IDN

DECIMAL

15

SRC_SYS_ID

DECIMAL

15

-99999

DECIMAL

15

CURRENT_TIMESTAMP(0)

DECIMAL

15

-99999

DECIMAL

15

CURRENT_TIMESTAMP(0) DECIMAL QLT_COMPLNT_DTL_F_V_COMPLNT DECIMAL

15 15

m_WL_QLT_MATRX_SUMM_AS_INS TIMESTAMP 0 _ITRAK_CAPA_OPEN' CURRENT_TIMESTAMP(0) TIMESTAMP 0 CURRENT_TIMESTAMP(0)

COMPLNTS_AGE_121_180 COMPLNTS_AGE_181_360 COMPLNTS_AGE_30 COMPLNTS_AGE_31_60 COMPLNTS_AGE_361 COMPLNTS_AGE_61_90 COMPLNTS_AGE_91_120 COMPLNTS_OPN_CNT

TCS Confidential

TIMESTAMP 0

SPECIFICATIONS

FILE

SOURCE Column Name

Data Type

Length

QLT_COMPLNTS_DTL_F_V DECIMAL

15

QLT_MATRX_IDN

DECIMAL

15


DECIMAL

15

DECIMAL

15

DECIMAL

15


DECIMAL

15

Value of each matrx

DECIMAL

15

DATA_SORC_IDN

DECIMAL

15

SRC_SYS_ID

DECIMAL

15

-99999

DECIMAL

15


DECIMAL

15

-99999

DECIMAL

15


DECIMAL

15

QLT_COMPLNT_DTL_F_V_COMPLNT DECIMAL

15

m_WL_QLT_MATRX_SUMM_AS_INS TIMESTAMP 0 _ITRAK_COMPLAINT_NEW' CURRENT_TIMESTAMP(0) TIMESTAMP 0 CURRENT_TIMESTAMP(0)

COMPLNTS_LAT_CRET_CNT COMPLNTS_NEW_CNT

TCS Confidential

TIMESTAMP 0

SPECIFICATIONS

FILE

SOURCE Column Name

Data Type

Length


15

QLT_MATRX_IDN

DECIMAL

15


DECIMAL

15

DECIMAL

15

DECIMAL

15


DECIMAL

15

Value of each matrx

DECIMAL

15

DATA_SORC_IDN

DECIMAL

15

SRC_SYS_ID

DECIMAL

15

-99999

DECIMAL

15


DECIMAL

15

-99999

DECIMAL

15


DECIMAL

15

QLT_COMPLNT_DTL_F_V_COMPLNT DECIMAL

15

m_WL_QLT_MATRX_SUMM_AS_INS TIMESTAMP 0 _ITRAK_COMPLAINT_CLOSED' CURRENT_TIMESTAMP(0) TIMESTAMP 0 CURRENT_TIMESTAMP(0)

COMPLNTS_CLOSED_CNT

TCS Confidential

TIMESTAMP 0

Business Rule

Surrogate key

Look up on QLT_MATRX_D table based on SRC_IDN and MATRX_ATTRB 1 to 1 1 to 1 1 to 1

QLT_MATRX_D.MATRX_LAST_REFRSH_DT

Look up om GCD_DAT_SORC_D based on SORC_NM

TCS Confidential

Look up on QLT_MATRX_D table based on SRC_IDN and MATRX_ATTRB HARDCODE 1 to 1 HARDCODE

1 to 1 HARDCODE HARDCODE

this source column will be populated as a row in the taget table column MATRX_VAL this source column will be populated as a row in the taget table MATRX_VAL this source column willcolumn be populated as a row in the taget table column MATRX_VAL this source column will be populated as a row in the taget table MATRX_VAL this source column willcolumn be populated as a row in the taget table column MATRX_VAL this source column will be populated as a row in the taget table column MATRX_VAL this source column will be populated as a row in the taget table MATRX_VAL this source column willcolumn be populated as a row in the taget table column MATRX_VAL

TCS Confidential

Business Rule

Surrogate key Look up on QLT_MATRX_D table based on SRC_IDN and MATRX_ATTRB 1 to 1 1 to 1 1 to 1 QLT_MATRX_D.MATRX_LAST_REFRSH_DT Look up om GCD_DAT_SORC_D based on SORC_NM Look up on QLT_MATRX_D table based on SRC_IDN and MATRX_ATTRB HARDCODE 1 to 1 HARDCODE 1 to 1 HARDCODE HARDCODE

this source column will be populated as a row in the taget table column MATRX_VAL this source column will be populated as a row in the taget table column MATRX_VAL

TCS Confidential

Business Rule

Surrogate key Look up on QLT_MATRX_D table based on SRC_IDN and MATRX_ATTRB 1 to 1 1 to 1 1 to 1 QLT_MATRX_D.MATRX_LAST_REFRSH_DT Look up om GCD_DAT_SORC_D based on SORC_NM Look up on QLT_MATRX_D table based on SRC_IDN and MATRX_ATTRB HARDCODE 1 to 1 HARDCODE 1 to 1 HARDCODE HARDCODE

this source column will be populated as a row in the taget table column MATRX_VAL

TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_MATRX_SUMM_AS_INS_ITRAK_CAPA_OPEN

Session Name

s_m_WL_QLT_MATRX_SUMM_AS_INS_ITRAK_CAPA_OPEN

Mapping Name

m_WL_QLT_MATRX_SUMM_AS_INS_ITRAK_CAPA_OPEN

Source Tables


Target Tables


Load Type

Insert


TRANSFORMATION TARGET System


SRC_ETL_TARGET QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

TCS Confidential

SPEC

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS QLT_MATRX_SUMM_AS


QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

TCS Confidential

TCS Confidential

TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_MATRX_SUMM_AS_INS_ITRAK_CAPA_NEW

Session Name

s_m_WL_QLT_MATRX_SUMM_AS_INS_ITRAK_CAPA_NEW

Mapping Name

m_WL_QLT_MATRX_SUMM_AS_INS_ITRAK_CAPA_NEW

Source Tables


Target Tables


Load Type

Insert




SRC_ETL_TARGET QLT_MATRX_SUMM_AS

TCS Confidential

SPEC

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS


QLT_MATRX_SUMM_AS



QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

TCS Confidential

QLT_MATRX_SUMM_AS

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_MATRX_SUMM_AS_INS_ITRAK_CAPA_CLOSED

Session Name

s_m_WL_QLT_MATRX_SUMM_AS_INS_ITRAK_CAPA_CLOSED

Mapping Name

m_WL_QLT_MATRX_SUMM_AS_INS_ITRAK_CAPA_CLOSED

Source Tables


Target Tables


Load Type

Insert





TCS Confidential

SPEC

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS


QLT_MATRX_SUMM_AS



QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

TCS Confidential

TCS Confidential

TRAK_CAPA_OPEN

K_CAPA_OPEN

_CAPA_OPEN



TRANSFORMATION

SPECIFICATIONS

TARGET Column Name

QLT_MATRX_SUMM_IDN

SOURCE Data Type

Length

DECIMAL

15

QLT_MATRX_IDN

DECIMAL

15

BUS_ORG_MAIN_IDN DECIMAL(15,0)

DECIMAL

15

MODLTY_ORG_MAIN_IDN DECIMAL(15,0)

DECIMAL

15

SIT_ORG_MAIN_IDN

DECIMAL

15

TIM_MAIN_DT

DATE

MATRX_VAL

VARCHAR

TCS Confidential

50

PK

FK

System

SRC_IDN

DECIMAL

15

SRC_SYS_ID

VARCHAR

250

SRC_CRETN_IDN DECIMAL(15,0)

DECIMAL

15

SRC_CRETN_TS

TIMESTAMP 0

SRC_UPD_IDN

DECIMAL

ODS_UPD_TS

TIMESTAMP 0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

50

ODS_POSTNG_TS

TIMESTAMP 0

SRC_UPD_TS

TIMESTAMP 0

TCS Confidential

15

TCS Confidential

TCS Confidential

TRAK_CAPA_NEW

K_CAPA_NEW

_CAPA_NEW



TRANSFORMATION

SPECIFICATIONS

TARGET Column Name

QLT_MATRX_SUMM_IDN

TCS Confidential

SOURCE Data Type

DECIMAL

Length

15

PK

FK

System

QLT_MATRX_IDN

DECIMAL

15

BUS_ORG_MAIN_IDN

DECIMAL

15

MODLTY_ORG_MAIN_IDN

DECIMAL

15

SIT_ORG_MAIN_IDN

DECIMAL

15

TIM_MAIN_DT

DATE

MATRX_VAL

VARCHAR

50

SRC_IDN

DECIMAL

15

SRC_SYS_ID

VARCHAR

250

SRC_CRETN_IDN

DECIMAL

15

SRC_CRETN_TS

TIMESTAMP 0

SRC_UPD_IDN

DECIMAL

ODS_UPD_TS

TIMESTAMP 0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

50

ODS_POSTNG_TS

TIMESTAMP 0

TCS Confidential

15

SRC_UPD_TS

TIMESTAMP 0

TRAK_CAPA_CLOSED

K_CAPA_CLOSED

_CAPA_CLOSED



TRANSFORMATION

SPECIFICATIONS

TARGET Column Name

QLT_MATRX_SUMM_IDN QLT_MATRX_IDN

TCS Confidential

SOURCE Data Type

Length

DECIMAL

15

DECIMAL

15

PK

FK

System

BUS_ORG_MAIN_IDN

DECIMAL

15

MODLTY_ORG_MAIN_IDN

DECIMAL

15

SIT_ORG_MAIN_IDN

DECIMAL

15

TIM_MAIN_DT

DATE

MATRX_VAL

VARCHAR

50

SRC_IDN

DECIMAL

15

SRC_SYS_ID

VARCHAR

250

SRC_CRETN_IDN

DECIMAL

15

SRC_CRETN_TS

TIMESTAMP 0

SRC_UPD_IDN

DECIMAL

ODS_UPD_TS

TIMESTAMP 0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

50

ODS_POSTNG_TS

TIMESTAMP 0

SRC_UPD_TS

TIMESTAMP 0

TCS Confidential

15

TCS Confidential

FILE




15

Surrogate key Look up on QLT_MATR X_D table based on SRC_IDN and MATRX_AT TRB

QLT_MATR DECIMAL X_IDN

15

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

MATRX_LA ST_REFRS H_DT

DECIMAL

15

QLT_MATR X_D.MATRX _LAST_REF RSH_DT

Value of each matrx

DECIMAL

15

BUS_ORG_ MAIN_IDN DECIMAL(15 ,0) MODLTY_O RG_MAIN_I DN DECIMAL(15 ,0) SIT_ORG_M AIN_IDN

TCS Confidential

DATA_SOR DECIMAL C_IDN

15

Look up om GCD_DAT_ SORC_D based on SORC_NM

SRC_SYS_I DECIMAL D

15

Look up on QLT_MATR X_D table based on SRC_IDN and MATRX_AT TRB

-99999

DECIMAL

15

HARDCODE

CURRENT_ DECIMAL TIMESTAMP (0)

15

1 to 1

-99999

DECIMAL

15

HARDCODE


15

1 to 1

QLT_COMP DECIMAL LNT_DTL_F _V_CAPA

15

HARDCODE

m_WL_QLT TIMESTAMP 0 _MATRX_S UMM_AS_IN S_ITRAK_C APA_OPEN'

HARDCODE

CURRENT_ TIMESTAMP 0 TIMESTAMP (0) CURRENT_ TIMESTAMP 0 TIMESTAMP (0)

TCS Confidential

CAPA_FIND NGS_AGE_ 121_180

this source column will be populated as a row in the taget table column MATRX_VA L

CAPA_FIND NGS_AGE_ 181_360


this source column will be populated as a row in the taget table column MATRX_VA L CAPA_FINDNGS_AGE_30 this source column will be populated as a row in the taget table column MATRX_VA L CAPA_FINDNGS_AGE_31_60 this source column will be populated as a row in the taget table column MATRX_VA L CAPA_FINDNGS_AGE_361 this source column will be populated as a row in the taget table column MATRX_VA L CAPA_FINDNGS_AGE_61_90 this source column will be populated as a row in the taget table column MATRX_VA L CAPA_FINDNGS_AGE_91_120 this source column will be populated as a row in the taget table column MATRX_VA L CAPA_FINDNGS_LAT_121_180

TCS Confidential

this source column will be populated as a row in the taget table column MATRX_VA L CAPA_FINDNGS_LAT_181_360 this source column will be populated as a row in the taget table column MATRX_VA L CAPA_FINDNGS_LAT_30 this source column will be populated as a row in the taget table column MATRX_VA L CAPA_FINDNGS_LAT_31_60 this source column will be populated as a row in the taget table column MATRX_VA L CAPA_FINDNGS_LAT_361 this source column will be populated as a row in the taget table column MATRX_VA L CAPA_FINDNGS_LAT_61_90 this source column will be populated as a row in the taget table column MATRX_VA L

CAPA_FINDNGS_LAT_91_120

TCS Confidential


CAPA_OPN_CNT this source column will be populated as a row in the taget table column MATRX_VA L

CAPA_LAT_CNT

FILE




TCS Confidential

15

Surrogate key


15


DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

DECIMAL

15


Value of DECIMAL each matrx DATA_SOR DECIMAL C_IDN

15 15


SRC_SYS_I DECIMAL D

15


-99999

BUS_ORG_ MAIN_IDN DECIMAL(15 ,0) MODLTY_O RG_MAIN_I DN DECIMAL(15 ,0) SIT_ORG_M AIN_IDN MATRX_LA ST_REFRS H_DT

DECIMAL

15

HARDCODE


15

1 to 1

-99999

DECIMAL

15

HARDCODE


15

1 to 1


15

HARDCODE

m_WL_QLT TIMESTAMP 0 _MATRX_S UMM_AS_IN S_ITRAK_C APA_NEW' CURRENT_ TIMESTAMP 0 TIMESTAMP (0)

TCS Confidential

HARDCODE

CURRENT_ TIMESTAMP 0 TIMESTAMP (0)

CAPA_NEW _CNT

FILE




QLT_COMPLNTS_DTL_F_V

TCS Confidential

DECIMAL

15


15


DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

DECIMAL

15



15 15


SRC_SYS_I DECIMAL D

15


-99999


DECIMAL

15

HARDCODE


15

1 to 1

-99999

DECIMAL

15

HARDCODE


15

1 to 1


15

HARDCODE

m_WL_QLT TIMESTAMP 0 _MATRX_S UMM_AS_IN S_ITRAK_C APA_CLOS ED' CURRENT_ TIMESTAMP 0 TIMESTAMP (0) CURRENT_ TIMESTAMP 0 TIMESTAMP (0)

TCS Confidential

HARDCODE

TCS Confidential

CAPA_CLO SD_CNT


CAPA_PEN DNG_VALD TN_CNT


TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_MATRX_SUMM_AS_INS_MDR_AER

Session Name

s_m_WL_QLT_MATRX_SUMM_AS_INS_MDR_AER

Mapping Name

m_WL_QLT_MATRX_SUMM_AS_INS_MDR_AER

Source Tables


Target Tables


Insert

Load Type Join Condition SQ Query




QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

TCS Confidential

SPECIFIC

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS



QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

TCS Confidential

TCS Confidential

TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_MATRX_SUMM_AS_INS_RECALS

Session Name

s_m_WL_QLT_MATRX_SUMM_AS_INS_RECALS

Mapping Name

m_WL_QLT_MATRX_SUMM_AS_INS_RECALS

Source Tables


Target Tables


Insert





TCS Confidential

SPECIFIC

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS


QLT_MATRX_SUMM_AS




QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

TCS Confidential

TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_MATRX_SUMM_AS_INS_REGLTRY_ENFRCMNT

Session Name

s_m_WL_QLT_MATRX_SUMM_AS_INS_REGLTRY_ENFRCMNT

Mapping Name

m_WL_QLT_MATRX_SUMM_AS_INS_REGLTRY_ENFRCMNT

Source Tables


Target Tables



TCS Confidential

Insert




QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS


QLT_MATRX_SUMM_AS



QLT_MATRX_SUMM_AS

TCS Confidential

SPECIFIC

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_MATRX_SUMM_AS_INS_483s

Session Name

s_m_WL_QLT_MATRX_SUMM_AS_INS_483s

Mapping Name

m_WL_QLT_MATRX_SUMM_AS_INS_483s

Source Tables


Target Tables


Insert





QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

TCS Confidential

SPECIFIC

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS


QLT_MATRX_SUMM_AS



QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

TCS Confidential

TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_MATRX_SUMM_AS_INS_IFR_MFR

Session Name

s_m_WL_QLT_MATRX_SUMM_AS_INS_IFR_MFR

Mapping Name

m_WL_QLT_MATRX_SUMM_AS_INS_IFR_MFR

Source Tables


Target Tables

TCS Confidential

Database


Insert





QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS


QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

TCS Confidential

SPECIFIC

QLT_MATRX_SUMM_AS


QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

TCS Confidential

TCS Confidential

NS_MDR_AER

MDR_AER

DR_AER

Table name QLT_MDR_AER_DTL_F_V


TRANSFORMATION

SPECIFICATIONS

TARGET

SOURCE

Column Name

Data Type

Length

PK

FK

System

FILE QLT_MDR_ AER_DTL_F _V

QLT_MATRX_SUMM_IDN

DECIMAL

15

QLT_MATRX_IDN

DECIMAL

15

BUS_ORG_MAIN_IDN DECIMAL(15,0)

DECIMAL

15

MODLTY_ORG_MAIN_IDN DECIMAL(15,0)

DECIMAL

15

SIT_ORG_MAIN_IDN

DECIMAL

15

TIM_MAIN_DT

DATE

MATRX_VAL

VARCHAR

TCS Confidential

50

SRC_IDN

DECIMAL

15

SRC_SYS_ID

VARCHAR

250


DECIMAL

15

SRC_UPD_IDN

DECIMAL

ODS_UPD_TS

TIMESTAMP 0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

50

ODS_POSTNG_TS

TIMESTAMP 0

SRC_UPD_TS

TIMESTAMP 0

TCS Confidential

TIMESTAMP 0

15

TCS Confidential

TCS Confidential

NS_RECALS

RECALS

CALS

Table name QLT_RECAL_DTL_F_V


TRANSFORMATION

SPECIFICATIONS

TARGET

SOURCE

Column Name

Data Type

Length

PK

FK

System

FILE QLT_RECAL_DTL_F_V

QLT_MATRX_SUMM_IDN QLT_MATRX_IDN

TCS Confidential

DECIMAL

15

DECIMAL

15

BUS_ORG_MAIN_IDN

DECIMAL

15

MODLTY_ORG_MAIN_IDN

DECIMAL

15

SIT_ORG_MAIN_IDN

DECIMAL

15

TIM_MAIN_DT

DATE

MATRX_VAL

VARCHAR

50

SRC_IDN

DECIMAL

15

SRC_SYS_ID

VARCHAR

250

SRC_CRETN_IDN

DECIMAL

15

SRC_CRETN_TS

TIMESTAMP 0

SRC_UPD_IDN

DECIMAL

ODS_UPD_TS

TIMESTAMP 0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

50

ODS_POSTNG_TS

TIMESTAMP 0

SRC_UPD_TS

TIMESTAMP 0

TCS Confidential

15

TCS Confidential

NS_REGLTRY_ENFRCMNT

REGLTRY_ENFRCMNT

GLTRY_ENFRCMNT

Table name QLT_REGLTRY_ENFRCMNT_DTL_ F_V


TCS Confidential

TRANSFORMATION

SPECIFICATIONS

TARGET

SOURCE

Column Name

Data Type

Length

PK

FK

System

FILE QLT_REGLTRY_ENFRCMNT_DTL_F_V

QLT_MATRX_SUMM_IDN

DECIMAL

15

QLT_MATRX_IDN

DECIMAL

15

BUS_ORG_MAIN_IDN

DECIMAL

15

MODLTY_ORG_MAIN_IDN

DECIMAL

15

SIT_ORG_MAIN_IDN

DECIMAL

15

TIM_MAIN_DT

DATE

MATRX_VAL

VARCHAR

50

SRC_IDN

DECIMAL

15

SRC_SYS_ID

VARCHAR

250

SRC_CRETN_IDN

DECIMAL

15

SRC_CRETN_TS

TIMESTAMP 0

SRC_UPD_IDN

DECIMAL

ODS_UPD_TS

TIMESTAMP 0

DAT_ORGN

VARCHAR

TCS Confidential

15

50

POSTNG_AGNT

VARCHAR

ODS_POSTNG_TS

TIMESTAMP 0

SRC_UPD_TS

TIMESTAMP 0

TCS Confidential

50

NS_483s

483s

3s

Table name QLT_EXTRNL_INSPCTNS_DTL_F_ V


TRANSFORMATION

SPECIFICATIONS

TARGET

SOURCE

Column Name

Data Type

Length

PK

FK

System

FILE QLT_EXTRNL_INSPCTNS_DTL_F_V

QLT_MATRX_SUMM_IDN

DECIMAL

15

QLT_MATRX_IDN

DECIMAL

15

BUS_ORG_MAIN_IDN

DECIMAL

15

MODLTY_ORG_MAIN_IDN

DECIMAL

15

TCS Confidential

SIT_ORG_MAIN_IDN

DECIMAL

TIM_MAIN_DT

DATE

MATRX_VAL

VARCHAR

50

SRC_IDN

DECIMAL

15

SRC_SYS_ID

VARCHAR

250

SRC_CRETN_IDN

DECIMAL

15

SRC_CRETN_TS

TIMESTAMP 0

SRC_UPD_IDN

DECIMAL

ODS_UPD_TS

TIMESTAMP 0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

50

ODS_POSTNG_TS

TIMESTAMP 0

SRC_UPD_TS

TIMESTAMP 0

TCS Confidential

15

15

TCS Confidential

NS_IFR_MFR

FR_MFR

R_MFR

Table name QLT_IFR_MFR_DTL_F_V

Table name

TCS Confidential

QLT_MATRX_SUMM_AS

TRANSFORMATION

SPECIFICATIONS

TARGET

SOURCE

Column Name

Data Type

Length

PK

FK

System

FILE QLT_IFR_MFR_DTL_F_V

QLT_MATRX_SUMM_IDN

DECIMAL

15

QLT_MATRX_IDN

DECIMAL

15

BUS_ORG_MAIN_IDN

DECIMAL

15

MODLTY_ORG_MAIN_IDN

DECIMAL

15

SIT_ORG_MAIN_IDN

DECIMAL

15

TIM_MAIN_DT

DATE

MATRX_VAL

VARCHAR

50

SRC_IDN

DECIMAL

15

SRC_SYS_ID

VARCHAR

250

SRC_CRETN_IDN

DECIMAL

15

TCS Confidential

SRC_CRETN_TS

TIMESTAMP 0

SRC_UPD_IDN

DECIMAL

ODS_UPD_TS

TIMESTAMP 0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

50

ODS_POSTNG_TS

TIMESTAMP 0

SRC_UPD_TS

TIMESTAMP 0

TCS Confidential

15

TCS Confidential



DECIMAL

15



15

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1


DECIMAL

15


Value of each matrx

DECIMAL

15


TCS Confidential


15


SRC_SYS_I DECIMAL D

15


-99999

DECIMAL

15

HARDCODE


15

1 to 1

-99999

DECIMAL

15

HARDCODE


15

1 to 1

QLT_MDR_ DECIMAL AER_DTL_F _V

15

HARDCODE

m_WL_QLT TIMESTAMP 0 _MATRX_S UMM_AS_IN S_MDR_AE R

HARDCODE


COMPLN_C RETN_DT


CONTRY


TCS Confidential

this source column will be populated as a row in the taget table column MATRX_VA L EVNT_TYP this source column will be populated as a row in the taget table column MATRX_VA L GE_KNOWLDG_DT this source column will be populated as a row in the taget table column MATRX_VA L INJRY this source column will be populated as a row in the taget table column MATRX_VA L LAT_FLG this source column will be populated as a row in the taget table column MATRX_VA L MANL_SRC_NM this source column will be populated as a row in the taget table column MATRX_VA L MDR_AER_FIL_DT

TCS Confidential

this source column will be populated as a row in the taget table column MATRX_VA L MDR_AER_FLG this source column will be populated as a row in the taget table column MATRX_VA L MDR_AER_REPRT_NBR this source column will be populated as a row in the taget table column MATRX_VA L MODLTY_SEGMNT this source column will be populated as a row in the taget table column MATRX_VA L PQR_PSR_CONVRSN_DT this source column will be populated as a row in the taget table column MATRX_VA L PRODUCT this source column will be populated as a row in the taget table column MATRX_VA L REPRT_NUM

TCS Confidential

this source column will be populated as a row in the taget table column MATRX_VA L REPRT_TYP



QLT_RECAL_DTL_F_V DECIMAL

15


15

TCS Confidential


DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

DECIMAL

15



15 15


SRC_SYS_I DECIMAL D

15


-99999


DECIMAL

15

HARDCODE


15

1 to 1

-99999

DECIMAL

15

HARDCODE


15

1 to 1

QLT_RECAL DECIMAL 15 _DTL_F_V m_WL_QLT TIMESTAMP 0 _MATRX_S UMM_AS_IN S_RECALS CURRENT_ TIMESTAMP 0 TIMESTAMP (0) CURRENT_ TIMESTAMP 0 TIMESTAMP (0)

TCS Confidential

HARDCODE HARDCODE

CLOSR_TIM NG


this source column will be populated as a row in the taget table column MATRX_VA L FISCL_WEK this source column will be populated as a row in the taget table column MATRX_VA L MANL_SRC_NM this source column will be populated as a row in the taget table column MATRX_VA L OWNR this source column will be populated as a row in the taget table column MATRX_VA L OWNR_ID this source column will be populated as a row in the taget table column MATRX_VA L PRODUCT

TCS Confidential

this source column will be populated as a row in the taget table column MATRX_VA L RECAL_CORCTN_ID this source column will be populated as a row in the taget table column MATRX_VA L RECAL_FLG this source column will be populated as a row in the taget table column MATRX_VA L RECAL_UNT this source column will be populated as a row in the taget table column MATRX_VA L RESLTN

TCS Confidential



QLT_REGLTRY_ENFRCMNT_DTL_F_V DECIMAL

15



15

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

DECIMAL

15



15 15


SRC_SYS_I DECIMAL D

15


-99999


DECIMAL

15

HARDCODE


15

1 to 1

-99999

DECIMAL

15

HARDCODE


15

1 to 1

QLT_REGLT DECIMAL RY_ENFRC MNT_DTL_F _V

15

HARDCODE

TCS Confidential

m_WL_QLT TIMESTAMP 0 _MATRX_S UMM_AS_IN S_REGLTR Y_ENFRCM NT'

HARDCODE


END_DT


ENFRCMNT _TYP


this source column will be populated as a row in the taget table column MATRX_VA L INSPCTR this source column will be populated as a row in the taget table column MATRX_VA L MANL_SRC_NM

TCS Confidential

this source column will be populated as a row in the taget table column MATRX_VA L START_DT



QLT_EXTRNL_INSPCTNS_DTL_F_V DECIMAL

15



15

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

BUS_ORG_ MAIN_IDN DECIMAL(15 ,0) MODLTY_O RG_MAIN_I DN DECIMAL(15 ,0)

TCS Confidential

DECIMAL

15

1 to 1

DECIMAL

15



15 15


SRC_SYS_I DECIMAL D

15


-99999

SIT_ORG_M AIN_IDN MATRX_LA ST_REFRS H_DT

DECIMAL

15

HARDCODE


15

1 to 1

-99999

DECIMAL

15

HARDCODE


15

1 to 1

QLT_EXTRN DECIMAL L_INSPCTN S_DTL_F_V

15

HARDCODE

m_WL_QLT TIMESTAMP 0 _MATRX_S UMM_AS_IN S_483s'

HARDCODE


END_DT

TCS Confidential


INSPCTN_A GNCY


this source column will be populated as a row in the taget table column MATRX_VA L INSPCTN_ID this source column will be populated as a row in the taget table column MATRX_VA L INSPCTN_TYP this source column will be populated as a row in the taget table column MATRX_VA L INSPCTR this source column will be populated as a row in the taget table column MATRX_VA L KEY_FINDNG_AREA this source column will be populated as a row in the taget table column MATRX_VA L MAJR_FINDNG_CNT

TCS Confidential

this source column will be populated as a row in the taget table column MATRX_VA L MANL_SRC_NM this source column will be populated as a row in the taget table column MATRX_VA L MINR_FINDNG_CNT this source column will be populated as a row in the taget table column MATRX_VA L PRODUCT this source column will be populated as a row in the taget table column MATRX_VA L REPRT_LOCTN_LINK this source column will be populated as a row in the taget table column MATRX_VA L START_DT

TCS Confidential



QLT_IFR_MFR_DTL_F_V DECIMAL

15



15

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

DECIMAL

15



15 15


SRC_SYS_I DECIMAL D

15


-99999

15

HARDCODE


TCS Confidential

DECIMAL


15

1 to 1

-99999

DECIMAL

15

HARDCODE


15

1 to 1

QLT_IFR_M DECIMAL FR_DTL_F_ V

15

HARDCODE

m_WL_QLT TIMESTAMP 0 _MATRX_S UMM_AS_IN S_IFR_MFR'

HARDCODE


IFR_IB


IFR_SCA_90


this source column will be populated as a row in the taget table column MATRX_VA L MANL_SRC_NM this source column will be populated as a row in the taget table column MATRX_VA L MFR_IB

TCS Confidential

this source column will be populated as a row in the taget table column MATRX_VA L MFR_SCA_90

TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_MATRX_SUMM_AS_INS_EDUNEERING_HR

Session Name

s_m_WL_QLT_MATRX_SUMM_AS_INS_EDUNEERING_HR

Mapping Name

m_WL_QLT_MATRX_SUMM_AS_INS_EDUNEERING_HR

Database

Source Tables


Database

Target Tables


Insert





QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

TCS Confidential

SPE

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS



QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

TCS Confidential

S_EDUNEERING_HR

DUNEERING_HR

NEERING_HR

Table name QLT_MATRX_TRANG_SUMM_A_V


TRANSFORMATION

SPECIFICATIONS

TARGET

SOURCE

Column Name

Data Type

Length

PK

FK

System

FILE QLT_MATRX_TRANG_SUMM_A_V

QLT_MATRX_SUMM_IDN

DECIMAL

15

QLT_MATRX_IDN

DECIMAL

15

BUS_ORG_MAIN_IDN

DECIMAL

15

MODLTY_ORG_MAIN_IDN

DECIMAL

15

SIT_ORG_MAIN_IDN

DECIMAL

15

TIM_MAIN_DT

DATE

MATRX_VAL

VARCHAR

TCS Confidential

50

SRC_IDN

DECIMAL

15

SRC_SYS_ID

VARCHAR

250

SRC_CRETN_IDN

DECIMAL

15

SRC_CRETN_TS

TIMESTAMP 0

SRC_UPD_IDN

DECIMAL

ODS_UPD_TS

TIMESTAMP 0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

50

ODS_POSTNG_TS

TIMESTAMP 0

SRC_UPD_TS

TIMESTAMP 0

TCS Confidential

15



QLT_MATRX_TRANG_SUMM_A_V DECIMAL

15



15

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1


DECIMAL

15


Value of each matrx

DECIMAL

15


TCS Confidential


15


SRC_SYS_I DECIMAL D

15


-99999

DECIMAL

15

HARDCODE


15

1 to 1

-99999

DECIMAL

15

HARDCODE


15

1 to 1

QLT_MATR DECIMAL X_TRANG_ SUMM_A_V

15

HARDCODE

m_WL_QLT TIMESTAMP 0 _MATRX_S UMM_AS_IN S_EDUNEE RING_HR'

HARDCODE


NOT_QULF D_CNT


QULFD_CN T


TCS Confidential

Folder Name


WorkFlow Name

wkf_s_m_WL_QLT_MATRX_SUMM_AS_INS_PURCHASE_UNVAL_SUPLIER

Session Name

s_m_WL_QLT_MATRX_SUMM_AS_INS_PURCHASE_UNVAL_SUPLIER

Mapping Name

m_WL_QLT_MATRX_SUMM_AS_INS_PURCHASE_UNVAL_SUPLIER

Database

Source Tables


Database

Target Tables


Insert





QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

TCS Confidential

S

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS



QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

Folder Name

TCS Confidential


WorkFlow Name

wkf_s_m_WL_QLT_MATRX_SUMM_AS_INS_PURCHASE_APRVD_SUPLIER

Session Name

s_m_WL_QLT_MATRX_SUMM_AS_INS_PURCHASE_APRVD_SUPLIER

Mapping Name

m_WL_QLT_MATRX_SUMM_AS_INS_PURCHASE_APRVD_SUPLIER

Database

Source Tables


Database

Target Tables


Insert





QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS


TCS Confidential

S

QLT_MATRX_SUMM_AS



QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

QLT_MATRX_SUMM_AS

TCS Confidential

TCS Confidential

TCS Confidential

ASE_UNVAL_SUPLIER

UNVAL_SUPLIER

NVAL_SUPLIER

Table name QLT_UNVLDTD_SUPLR_DTL_F_V


TRANSFORMATION

SPECIFICATIONS

TARGET

SOURCE Column Name

Data Type

Length

PK

FK

System

FILE QLT_UNVLDTD_SUPLR_DTL_F_V

QLT_MATRX_SUMM_IDN

DECIMAL

15

QLT_MATRX_IDN

DECIMAL

15

BUS_ORG_MAIN_IDN

DECIMAL

15

MODLTY_ORG_MAIN_IDN

DECIMAL

15

SIT_ORG_MAIN_IDN

DECIMAL

15

TIM_MAIN_DT

DATE

MATRX_VAL

VARCHAR

TCS Confidential

50

SRC_IDN

DECIMAL

15

SRC_SYS_ID

VARCHAR

250

SRC_CRETN_IDN

DECIMAL

15

SRC_CRETN_TS

TIMESTAMP 0

SRC_UPD_IDN

DECIMAL

ODS_UPD_TS

TIMESTAMP 0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

50

ODS_POSTNG_TS

TIMESTAMP 0

SRC_UPD_TS

TIMESTAMP 0

TCS Confidential

15

ASE_APRVD_SUPLIER

APRVD_SUPLIER

PRVD_SUPLIER

Table name QLT_APRVD_SUPLR_DTL_F_V


TRANSFORMATION

SPECIFICATIONS

TARGET

SOURCE Column Name

Data Type

Length

PK

FK

System

FILE QLT_APRVD_SUPLR_DTL_F_V

QLT_MATRX_SUMM_IDN

DECIMAL

15

QLT_MATRX_IDN

DECIMAL

15

BUS_ORG_MAIN_IDN

DECIMAL

15

MODLTY_ORG_MAIN_IDN

DECIMAL

15

SIT_ORG_MAIN_IDN

DECIMAL

15

TIM_MAIN_DT

DATE

MATRX_VAL

VARCHAR

50

SRC_IDN

DECIMAL

15

TCS Confidential

SRC_SYS_ID

VARCHAR

250

SRC_CRETN_IDN

DECIMAL

15

SRC_CRETN_TS

TIMESTAMP 0

SRC_UPD_IDN

DECIMAL

ODS_UPD_TS

TIMESTAMP 0

DAT_ORGN

VARCHAR

50

POSTNG_AGNT

VARCHAR

50

ODS_POSTNG_TS

TIMESTAMP 0

SRC_UPD_TS

TIMESTAMP 0

TCS Confidential

15

LAST_AUDT_DT

MANL_SRC_NM

SCHEDLD_AUDT_DT

STATUS_CD

SUPLR_AUDT_ID

TCS Confidential

SUPLR_NAM

SUPLR_SIT_ID

TARGT_AUDT_DT

TCS Confidential



QLT_UNVLDTD_SUPLR_DTL_F_V DECIMAL

15



15

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1


DECIMAL

15


Value of each matrx

DECIMAL

15


TCS Confidential


15


SRC_SYS_I DECIMAL D

15


-99999

DECIMAL

15

HARDCODE


15

1 to 1

-99999

DECIMAL

15

HARDCODE


15

1 to 1

QLT_UNVL DECIMAL DTD_SUPL R_DTL_F_V

15

HARDCODE

m_WL_QLT TIMESTAMP 0 _MATRX_S UMM_AS_IN S_PURCHA SE_UNVAL_ SUPLIER'

HARDCODE


TOTL_SUPL R_NBR


TOTL_UNQ LFD_SUPLR


TCS Confidential



QLT_APRVD_SUPLR_DTL_F_V DECIMAL

15



15

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

DECIMAL

15

1 to 1

DECIMAL

15



15


TCS Confidential

15


SRC_SYS_I DECIMAL D

15


-99999

DECIMAL

15

HARDCODE


15

1 to 1

-99999

DECIMAL

15

HARDCODE


15

1 to 1

QLT_APRV D_SUPLR_ DTL_F_V

15

HARDCODE

DECIMAL

m_WL_QLT TIMESTAMP 0 _MATRX_S UMM_AS_IN S_PURCHA SE_APRVD _SUPLIER'

HARDCODE


ADT_SCOR


AUDT_TYP


this source column will be populated as a row in the taget table column MATRX_VA L CATGRZTN

TCS Confidential

this source column will be populated as a row in the taget table column MATRX_VA L COMPLT_DT this source column will be populated as a row in the taget table column MATRX_VA L LAST_AUDT_DT this source column will be populated as a row in the taget table column MATRX_VA L MANL_SRC_NM this source column will be populated as a row in the taget table column MATRX_VA L SCHEDLD_AUDT_DT this source column will be populated as a row in the taget table column MATRX_VA L STATUS_CD this source column will be populated as a row in the taget table column MATRX_VA L SUPLR_AUDT_ID

TCS Confidential

this source column will be populated as a row in the taget table column MATRX_VA L SUPLR_NAM this source column will be populated as a row in the taget table column MATRX_VA L SUPLR_SIT_ID this source column will be populated as a row in the taget table column MATRX_VA L TARGT_AUDT_DT

TCS Confidential

Sample ETL Mapping Sheet

Recommend Documents