100% found this document useful (1 vote)

150 views158 pages

Target To Source Mapping RT

The document describes the mapping of data elements from source databases like FND_FCML_DB and source tables like AgreementStatus to target dimensions like DimAgreementStatus. It includes the target table name, data element name, source database and table, and the corresponding source data element for each mapping. Transformation rules are also referenced for certain data elements mapped from lookup tables.

Uploaded by

shreyatha reddy

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as XLS, PDF, TXT or read online on Scribd

100% found this document useful (1 vote)

150 views158 pages

Target To Source Mapping RT

Uploaded by

shreyatha reddy

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as XLS, PDF, TXT or read online on Scribd

You are on page 1/ 158

Tab Resource Status Status Key

DimAgreementStatus Larry Jewell Review Not Started

DimCalendarDate Evan VanGelder Review Draft
DimCommercialInsurancePolicy Chad Webb Review Devel
DimCommercialPackage Dale Lindquist Review Test
DimDBAName Leisa Miller Review Review
DimInsuredName Leisa Miller Review N/A
DimInsuredCompany Leisa Miller Review
DimInsuringCompany Neha Agrawal Review
DimManagedProduct Chad Review
DimMoneyScheduler Neha Agrawal Review
DimOrganizationUnit Neha Agrawal Review
DimPostalAddress Chad Webb Review
PopulationInformation Rakesh Verma N/A
FactMoPackageRetentionPrfl Larry Jewell Review
FactMoMngdPrdctPrfl ? Review
FactMoPolicyRetentionPrfl Evan VanGelder Review
InternalOrganizationHrchy Dale Lindquist Review
Description
Still defining fields
Draft View SQL complete
Developing Table creation SQL
Test Table created in database
Peer Review in progress
Not required for POC
Generic Mapping

How To Use This Mapping Workbook

Data mapping is the process of defining the relationship between two data structures and any data transformation requirements that result. The
primary inputs to this process are data structures such as logical and physical data models, copybooks, screen layouts, interface formats, file
structures, and dictionaries. The primary output of this process is documentation of the data mapping relationships and their corresponding
transformation rules. This documentation is used for initial conversion or ongoing manipulation of data from one structure to another. It can also be
used for traceability. In order to be successful, this process requires knowledge of the source and target data structures and should be reviewed
with source and target development and business experts. This design documentation will be input to a data transformation tool.

1. Determine if additional columns are needed on the worksheets to manage change or provide additional functionality.
2. Create a generic data element worksheet for each source or target that you wish to map. Examples include: business data glossary, application
system data store or data model, a logical model subject area, IAA, or a data warehouse data store. The red corner of the column heading on the
worksheet contains a definition of column.
3. Create a generic mapping worksheet for each pair of source to target mappings. All data elements needed to map for data conversion purposes
to a given target data element should be included on the same worksheet. This may require data elements from multiple data sources. The red
corner of the column heading on the worksheet contains a definition of column.
4. Be sure the source and target system id's, data element ids, group names, and data element names on the mapping worksheet match those on
the corresponding data element worksheet.
5. Investigate source data structures and known data quality issues in moving data to target structures.
6. Document transformation rules, exceptions, validity or data quality checks that are needed.
7. Optionally create code tables for validity checks and transformations of domain or allowed values.
8. Review data mappings with source and target experts.
Generic Mapping
DimAgreementStatus

The DimAgreementStatus structure is defined in this tab.

The SQL used to create an example view appears below.
The mapping information is provided below the SQL.

Resource Status

Larry Jewell Review

Source
Target Instance Target Instance Target Data Element Name Source Source Data Element Name
Target Table Name Segment
Identifier Rule Type (column, field, attribute) Database(s) (column, field, attribute)
Code(s)

Dimension Row Building Rule DimAgreementStatus FND_FCML_DB AgreementStatus

Dimension Data Mapping DimAgreementStatus AgreementStatus_Id FND_FCML_DB AgreementStatus AgreementStatus_Id

Dimension Data Mapping DimAgreementStatus Agreement_Id FND_FCML_DB AgreementStatus Agreement_Id

Page 3 of 158
DimAgreementStatus

Dimension Data Mapping DimAgreementStatus DerivedStatus_Tp FND_FCML_DB AgreementStatus See Transformation Rule

Dimension Data Mapping DimAgreementStatus DerivedStatus_Cd FND_FCML_DB MASTER_DATA See Transformation Rule

Page 4 of 158
DimAgreementStatus

Dimension Data Mapping DimAgreementStatus DerivedStatus_Ds FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimAgreementStatus Status_Tp FND_FCML_DB AgreementStatus Status_Tp

Dimension Data Mapping DimAgreementStatus Status_Cd FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimAgreementStatus Status_Ds FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimAgreementStatus StatusEffective_Dt FND_FCML_DB AgreementStatus StatusEffective_Dt

Dimension Data Mapping DimAgreementStatus StatusReason_Tp FND_FCML_DB AgreementStatus StatusReason_Tp

Dimension Data Mapping DimAgreementStatus StatusReason_Cd FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimAgreementStatus StatusReason_Ds FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimAgreementStatus StatusReasonDetail_Tp FND_FCML_DB AgreementStatus StatusReasonDetail_Tp

Dimension Data Mapping DimAgreementStatus StatusReasonDetail_Cd FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimAgreementStatus StatusReasonDetail_Ds FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimAgreementStatus Effective_Dt FND_FCML_DB AgreementStatus Effective_Dt

Dimension Data Mapping DimAgreementStatus Expiration_Dt FND_FCML_DB AgreementStatus Expiration_Dt

Dimension Data Mapping DimAgreementStatus Transaction_Ts FND_FCML_DB AgreementStatus Transaction_Ts
Dimension Data Mapping DimAgreementStatus Revision_Ts FND_FCML_DB AgreementStatus Revision_Ts

Page 5 of 158
DimAgreementStatus

Dimension Data Mapping DimAgreementStatus PopulationInfo_Id FND_FCML_DB PopulationInfo See transformation rule

Page 6 of 158
DimAgreementStatus

Target
Form Additiona
Referenc
Transfor Target Target Rated/No l Last Last EA Data
Target e or Additional
Transformation Rule 1 mation Form # Form # n- Exclusio Updated Updated Status ISSUE Element
Form # Lookup Comments
Rule 2 mapped tested Rated/Ma ns / By Date ID
Table
nually Validity
Rated

For each row in AgreementStatus, generate 1 DimAgreementStatus

Data row.
Move
Move

Page 7 of 158
DimAgreementStatus

if AgreementType_id = 011003010000 (Commercial

Package) then:

if CommercialPackage.Agreement_Id is not NULL AND

(CURRENT_DATE >= Effective_Dt AND
CURRENT_DATE < Expiration_Dt AND
CURRENT_DATE >= StatusEffective_Dt AND
AND (PolicyCancel.Status_Tp IN (6,7,8,16,20,21,22)
OR (PolicyCancel.Status_Tp = 0
AND PolicyCancel.StatusReason_Tp <> 0)) IS FALSE
then DerivedStatus_Tp = 3

if PackageFuture.Agreement_Id is not NULL

then DerivedStatus_Tp = 5

else DerivedStatus_Tp = 4
See
Example
CDW 11/1/2007
SQL for
if AgreementType_id = 013001060000 (Commercial JOIN Logic
Policy) then:

if CommercialPolicy.Agreement_Id is not NULL AND

if PolicyFuture.Agreement_Id is not NULL

then DerivedStatus_Tp = 5

else DerivedStatus_Tp = 4

find first occurance of following:

(LSD_Value)
Where PTD_Name = CDW 11/1/2007
FND_FCML_DB.AGREEMENTSTATUS.DERIVEDSTATUS_CD AND
PTD_Value = DerivedStatus_Tp

Page 8 of 158
DimAgreementStatus

find first occurance of following:

(DESC)
Where PTD_Name = CDW 11/1/2007
FND_FCML_DB.AGREEMENTSTATUS.DERIVEDSTATUS_CD AND
PTD_Value = DerivedStatus_Tp

Move
find first occurance of following: (LSD_Value)
Where PTD_Name =
CDW 11/1/2007
FND_FCML_DB.AGREEMENTSTATUS.STATUS_TP AND
PTD_Value = Status_Tp

find first occurance of following: (DESC)

Where PTD_Name =
CDW 11/1/2007
FND_FCML_DB.AGREEMENTSTATUS.STATUS_TP AND
PTD_Value = Status_Tp
Move
Move

find first occurance of following: (LSD_Value)

Where PTD_Name =
CDW 11/1/2007
FND_FCML_DB.AGREEMENTSTATUS.STATUSREASON_TP AND
PTD_Value = STATUSREASON_Tp

find first occurance of following: (DESC)

Where PTD_Name =
CDW 11/1/2007
FND_FCML_DB.AGREEMENTSTATUS.STATUSREASON_TP AND
PTD_Value = STATUSREASON_Tp

Move

find first occurance of following: (LSD_Value)

Where PTD_Name =
FND_FCML_DB.AGREEMENTSTATUS.STATUSREASONDETAIL_T DJL 1/29/2008
P AND
PTD_Value = STATUSREASONDETAIL_Tp

find first occurance of following: (DESC)

Where PTD_Name =
FND_FCML_DB.AGREEMENTSTATUS.STATUSREASONDETAIL_T CDW 11/1/2007
P AND
PTD_Value = STATUSREASONDETAIL_Tp

Move

Move
Move
Move

Page 9 of 158
DimAgreementStatus

- Population Cycle Description

- Population Timestamp ( date and time of population cycle)
(Transaction_Ts) RLJ 11/9/2007
- Source System Label (description of application or system from which
the information last used to update the entity instance was populated).

Page 10 of 158
DimAgreementStatus

EA Data
Element
Group
EA EA Data Element EA EA Re- Comment
EA System Name EA Layout Name (Copy- EA Data
Group Name (column, field, Occurs defines (EA va IS
ID (table, book) Type
Level attribute) Number Flag vs IAA)
segment,
file,
entity)

Page 11 of 158
DimAgreementStatus

Page 12 of 158
DimAgreementStatus

Page 13 of 158
DimAgreementStatus

Page 14 of 158
DimCalendarDate

The DimCalendarDate is defined as a VIEW

ONLY for the December 2007 release.
There is no ETL required.
Resource Status
CREATE VIEW
RPT_FCML_APPS_TEST.DimCalendarDate
AS
SELECT
AI.CalendarDate_Id, -- DECIMAL(18,0)
NOT NULL, Evan VanGelder Review
AI.Calendar_Dt, -- DATE FORMAT
'YYYY-MM-DD',
AI.CalendarDateShort_Ds, --
VARCHAR(64)
Target CHARACTER SET LATIN Target Data Element Source Source Data Element
NOT Target Instance Target Table
CASESPECIFIC, Target Data Logical Name Source
Instance Name (column, field, Segment Name (column, field, Transformation Rule 1
Rule Type
AI.CalendarDateLong_Ds, -- Name (column, field, attribute) Database(s)
Identifier attribute) Code(s) attribute)
VARCHAR(64) CHARACTER SET LATIN
NOT CASESPECIFIC,
AI.CalendarDateBeginning_Ts, --
TIMESTAMP(6),
AI.CalendarDateEnding_Ts, -- For each set of rows (grouped by
TIMESTAMP(6)) day) in
FND_FSHR_DB.Calendar,
AI.CalendarDayShort_Ds, -- CHAR(3) generate 1 DimCalendarDate
CHARACTER
Dimension RowSET LATIN
Building NOTDimCalendarDate
Rule FND_FSHR_DB calendar See transformation rule row. (One set of fields for the
CASESPECIFIC, Allied calendar and another set
AI.CalendarDayLong_Ds, -- CHAR(1) for the Nationwide calendar, as
indicated by
CHARACTER SET LATIN NOT AccountingMethod_Cd)
CASESPECIFIC,
AI.CalendarWeek_Id,
Dimension Data Mapping -- SMALLINT,
DimCalendarDate Calendar Date Identifier CalendarDate_Id FND_FSHR_DB calendar CalendarDate_Id Move
AI.CalendarWeek_Nb,
Dimension Data Mapping -- DECIMAL(2,0),
DimCalendarDate Calendar Date Date Calendar_Dt FND_FSHR_DB calendar Calendar_Dt Move
AI.CalendarWeek_Ds, -- VARCHAR(16)
Dimension Data Mapping DimCalendarDate Calendar Date Short Description CalendarDateShort_Ds FND_FSHR_DB calendar CalendarDateShort_Ds Move
CHARACTER SET LATIN NOT
Dimension Data Mapping DimCalendarDate Calendar Date Long Description CalendarDateLong_Ds FND_FSHR_DB calendar CalendarDateLong_Ds Move
CASESPECIFIC,
AI.CalendarMonth_Id,
Dimension Data Mapping -- SMALLINT NOT Calendar Date Beginnning Timestamp
DimCalendarDate CalendarDateBeginning_Ts FND_FSHR_DB calendar CalendarDateBeginning_Ts Move
NULL,
Dimension Data Mapping
AI.CalendarMonth_Nb, DimCalendarDate Calendar Date Ending Timestamp
-- BYTEINT, CalendarDateEnding_Ts FND_FSHR_DB calendar CalendarDateEnding_Ts Move
AI.CalendarMonthDay_Nb,
Dimension Data Mapping --
DimCalendarDate Calendar Day Short Description CalendarDayShort_Ds FND_FSHR_DB calendar CalendarDayShort_Ds Move
DECIMAL(2,0),
Dimension Data Mapping DimCalendarDate Calendar Day Long Description CalendarDayLong_Ds FND_FSHR_DB calendar CalendarDayLong_Ds Move
AI.CalendarMonthShort_Ds,
Dimension Data Mapping -- CHAR(3) Calendar Week Identifier
DimCalendarDate CalendarWeek_Id FND_FSHR_DB calendar CalendarWeek_Id Move
CHARACTER
Dimension SET LATIN NOTDimCalendarDate Calendar Week Number
Data Mapping CalendarWeek_Nb FND_FSHR_DB calendar CalendarWeek_Nb Move
CASESPECIFIC,
Dimension Data Mapping DimCalendarDate Calendar Week Description CalendarWeek_Ds FND_FSHR_DB calendar CalendarWeek_Ds Move
AI.CalendarMonthLong_Ds, --
Dimension Data Mapping DimCalendarDate Calendar Month Identifier CalendarMonth_Id FND_FSHR_DB calendar CalendarMonth_Id Move
VARCHAR(16) CHARACTER SET LATIN
Dimension
NOT Data Mapping
CASESPECIFIC, DimCalendarDate Calendar Month Number CalendarMonth_Nb FND_FSHR_DB calendar CalendarMonth_Nb Move
AI.CalendarQuarter_Id,
Dimension Data Mapping -- INTEGER,
DimCalendarDate Calendar Month Day Number CalendarMonthDay_Nb FND_FSHR_DB calendar CalendarMonthDay_Nb Move
AI.CalendarQuarter_Nb,
Dimension Data Mapping --DimCalendarDate
BYTEINT, Calendar Month Short Description CalendarMonthShort_Ds FND_FSHR_DB calendar CalendarMonthShort_Ds Move
AI.CalendarQuarter_Ds,
Dimension Data Mapping --DimCalendarDate
CHAR(10) Calendar Month Long Description CalendarMonthLong_Ds FND_FSHR_DB calendar CalendarMonthLong_Ds Move
CHARACTER
Dimension SET LATIN NOTDimCalendarDate Calendar Quarter Identifier
Data Mapping CalendarQuarter_Id FND_FSHR_DB calendar CalendarQuarter_Id Move
CASESPECIFIC,
Dimension Data Mapping DimCalendarDate Calendar Quarter Number CalendarQuarter_Nb FND_FSHR_DB calendar CalendarQuarter_Nb Move
AI.CalendarYear_Nb, -- DECIMAL(4,0),
Dimension Data Mapping DimCalendarDate Calendar Quarter Description CalendarQuarter_Ds FND_FSHR_DB calendar CalendarQuarter_Ds Move
AI.CalendarYearMonth_Ds, -- CHAR(12)
Dimension
CHARACTER Data Mapping
SET LATIN NOTDimCalendarDate Calendar Year Number CalendarYear_Nb FND_FSHR_DB calendar CalendarYear_Nb Move
Dimension
CASESPECIFIC, Data Mapping DimCalendarDate Calendar Year Month Description CalendarYearMonth_Ds FND_FSHR_DB calendar CalendarYearMonth_Ds Move
AI.CalendarYearDay_Nb, --
DECIMAL(3,0),
AI.CalendarPrevious30Days_Dt, -- DATE Page 1 of 158
FORMAT 'YYYY-MM-DD',
--AccountingMethod_Cd, -- CHAR(2)
CHARACTER SET LATIN NOT
CASESPECIFIC,
DimCalendarDate

Target Target Data Element Source Source Data Element

Target Instance Target Table Target Data Logical Name Source
Instance Name (column, field, Segment Name (column, field, Transformation Rule 1
Rule Type Name (column, field, attribute) Database(s)
Identifier attribute) Code(s) attribute)

Dimension Data Mapping DimCalendarDate Calendar Year Day Number CalenderYearDay_Nb FND_FSHR_DB calendar CalenderYearDay_Nb Move
Dimension Data Mapping DimCalendarDate Calendar Previous 30 Days Date CalendarPrevious30Days_Dt FND_FSHR_DB calendar CalendarPrevious30Days_Dt Move
Dimension Data Mapping DimCalendarDate Accounting Day Short Description AIAccountingDayShort_Ds FND_FSHR_DB calendar AccountingDayShort_Ds Move
Dimension Data Mapping DimCalendarDate Accounting Day Long Description AIAccountingDayLong_Ds FND_FSHR_DB calendar AccountingDayLong_Ds Move
Dimension Data Mapping DimCalendarDate Accounting Week Day Number AIAccountingWeekDay_Nb FND_FSHR_DB calendar AccountingWeekDay_Nb Move
Dimension Data Mapping DimCalendarDate Accounting Month Identifier AIAccountingMonth_Id FND_FSHR_DB calendar AccountingMonth_Id Move
Dimension Data Mapping DimCalendarDate Accounting Month Number AIAccountingMonth_Nb FND_FSHR_DB calendar AccountingMonth_Nb Move

Dimension Data Mapping DimCalendarDate Accounting Month Short Description FND_FSHR_DB calendar Move
AIAccountingMonthShort_Ds AccountingMonthShort_Ds
Dimension Data Mapping DimCalendarDate Accounting Month Long Description AIAccountingMonthLong_Ds FND_FSHR_DB calendar AccountingMonthLong_Ds Move
Dimension Data Mapping DimCalendarDate Accounting Month Day Number AIAccountingMonthDay_Nb FND_FSHR_DB calendar AccountingMonthDay_Nb Move
Dimension Data Mapping DimCalendarDate Accounting Quarter Identifier AIAccountingQuarter_Id FND_FSHR_DB calendar AccountingQuarter_Id Move
Dimension Data Mapping DimCalendarDate Accounting Quarter Number AIAccountingQuarter_Nb FND_FSHR_DB calendar AccountingQuarter_Nb Move
Dimension Data Mapping DimCalendarDate Accounting Quarter Description AIAccountingQuarter_Ds FND_FSHR_DB calendar AccountingQuarter_Ds Move
Dimension Data Mapping DimCalendarDate Accounting Year Number AIAccountingYear_Nb FND_FSHR_DB calendar AccountingYear_Nb Move
Dimension Data Mapping DimCalendarDate Accounting Year Day Number AIAccountingYearDay_Nb FND_FSHR_DB calendar AccountingYearDay_Nb Move
Dimension Data Mapping DimCalendarDate Accounting Year Week Number AIAccountingYearWeek_Nb FND_FSHR_DB calendar AccountingYearWeek_Nb Move
Dimension Data Mapping DimCalendarDate Accounting Year Month Description AIAccountingYearMonth_Ds FND_FSHR_DB calendar AccountingYearMonth_Ds Move

Dimension Data Mapping DimCalendarDate Last Accounting Day in Month Indicator FND_FSHR_DB calendar Move
AILastAccountingDayInMonth_In LastAccountingDayInMonth_In
Dimension Data Mapping DimCalendarDate Holiday Indicator AIHoliday_In FND_FSHR_DB calendar Holiday_In Move
Dimension Data Mapping DimCalendarDate Business Day Indicator AIBusinessDay_In FND_FSHR_DB calendar BusinessDay_In Move
Dimension Data Mapping DimCalendarDate Business Day in Month Number AIBusinessDayInMonth_Nb FND_FSHR_DB calendar BusinessDayInMonth_Nb Move
Dimension Data Mapping DimCalendarDate Accounting Day Short Description NWAccountingDayShort_Ds FND_FSHR_DB calendar AccountingDayShort_Ds Move
Dimension Data Mapping DimCalendarDate Accounting Day Long Description NWAccountingDayLong_Ds FND_FSHR_DB calendar AccountingDayLong_Ds Move
Dimension Data Mapping DimCalendarDate Accounting Week Day Number NWAccountingWeekDay_Nb FND_FSHR_DB calendar AccountingWeekDay_Nb Move
Dimension Data Mapping DimCalendarDate Accounting Month Identifier NWAccountingMonth_Id FND_FSHR_DB calendar AccountingMonth_Id Move
Dimension Data Mapping DimCalendarDate Accounting Month Number NWAccountingMonth_Nb FND_FSHR_DB calendar AccountingMonth_Nb Move

Dimension Data Mapping DimCalendarDate Accounting Month Short Description FND_FSHR_DB calendar Move
NWAccountingMonthShort_Ds AccountingMonthShort_Ds
Dimension Data Mapping DimCalendarDate Accounting Month Long Description NWAccountingMonthLong_Ds FND_FSHR_DB calendar AccountingMonthLong_Ds Move
Dimension Data Mapping DimCalendarDate Accounting Month Day Number NWAccountingMonthDay_Nb FND_FSHR_DB calendar AccountingMonthDay_Nb Move
Dimension Data Mapping DimCalendarDate Accounting Quarter Identifier NWAccountingQuarter_Id FND_FSHR_DB calendar AccountingQuarter_Id Move
Dimension Data Mapping DimCalendarDate Accounting Quarter Number NWAccountingQuarter_Nb FND_FSHR_DB calendar AccountingQuarter_Nb Move
Dimension Data Mapping DimCalendarDate Accounting Quarter Description NWAccountingQuarter_Ds FND_FSHR_DB calendar AccountingQuarter_Ds Move
Dimension Data Mapping DimCalendarDate Accounting Year Number NWAccountingYear_Nb FND_FSHR_DB calendar AccountingYear_Nb Move
Dimension Data Mapping DimCalendarDate Accounting Year Day Number NWAccountingYearDay_Nb FND_FSHR_DB calendar AccountingYearDay_Nb Move
Dimension Data Mapping DimCalendarDate Accounting Year Week Number NWAccountingYearWeek_Nb FND_FSHR_DB calendar AccountingYearWeek_Nb Move
Dimension Data Mapping DimCalendarDate Accounting Year Month Description NWAccountingYearMonth_Ds FND_FSHR_DB calendar AccountingYearMonth_Ds Move

Dimension Data Mapping DimCalendarDate Last Accounting Day in Month Indicator FND_FSHR_DB calendar Move
NWLastAccountingDayInMonth_In LastAccountingDayInMonth_In
Dimension Data Mapping DimCalendarDate Holiday Indicator NWHoliday_In FND_FSHR_DB calendar Holiday_In Move

Page 2 of 158
DimCalendarDate

Target Target Data Element Source Source Data Element

Dimension Data Mapping DimCalendarDate Business Day Indicator NWBusinessDay_In FND_FSHR_DB calendar BusinessDay_In Move
Dimension Data Mapping DimCalendarDate Business Day in Month Number NWBusinessDayInMonth_Nb FND_FSHR_DB calendar BusinessDayInMonth_Nb Move

Page 3 of 158
DimCalendarDate

EA Data
Target
Element
Form Additiona
Referenc Group
Target Target Rated/No l Last Last EA Data
Transformation Target e or Additional EA System Name
Form # Form # n- Exclusio Updated Updated Status ISSUE Element
Rule 2 Form # Lookup Comments ID (table,
mapped tested Rated/Ma ns / By Date ID
Table segment,
nually Validity
file,
Rated
entity)

Page 4 of 158
DimCalendarDate

See join logic in SQL

See join logic in SQL
See join logic in SQL
See join logic in SQL
See join logic in SQL

See join logic in SQL

See join logic in SQL
See join logic in SQL
See join logic in SQL
See join logic in SQL
See join logic in SQL
See join logic in SQL
See join logic in SQL
See join logic in SQL

See join logic in SQL

See join logic in SQL
See join logic in SQL
See join logic in SQL
See join logic in SQL
See join logic in SQL
See join logic in SQL
See join logic in SQL

See join logic in SQL

See join logic in SQL
See join logic in SQL
See join logic in SQL
See join logic in SQL
See join logic in SQL
See join logic in SQL
See join logic in SQL
See join logic in SQL

See join logic in SQL

Page 5 of 158
DimCalendarDate

Page 6 of 158
DimCalendarDate

EA EA Data Element EA EA Re- Comment

EA Layout Name (Copy- EA Data
Group Name (column, field, Occurs defines (EA va IS
book) Type
Level attribute) Number Flag vs IAA)

Page 7 of 158
DimCalendarDate

EA EA Data Element EA EA Re- Comment

EA Layout Name (Copy- EA Data
Group Name (column, field, Occurs defines (EA va IS
book) Type
Level attribute) Number Flag vs IAA)

Page 8 of 158
DimCalendarDate

EA EA Data Element EA EA Re- Comment

EA Layout Name (Copy- EA Data
Group Name (column, field, Occurs defines (EA va IS
book) Type
Level attribute) Number Flag vs IAA)

Page 9 of 158
DimCommercialInsurancePolicy

The DimCommercialInsurancePolicy structure is defined in

this tab. The SQL used to create an example view appears
below. The mapping information is provided below the SQL.

Resource Status

Chad Webb Review

Target
Target Instance Rule Target Data Element Name Source Source Segment Source Data Element Name
Instance Target Table Name
Type (column, field, attribute) Database(s) Code(s) (column, field, attribute)
Identifier

Dimension Row Building Rule DimCommercialInsurancePolicy FND_FCML_DB CommercialInsurancePolicy

Dimension Data Mapping DimCommercialInsurancePolicy CommercialInsurancePlcy_Id FND_FCML_DB CommercialInsurancePolicy CommercialInsurancePlcy_Id

Dimension Data Mapping DimCommercialInsurancePolicy Agreement_Id FND_FCML_DB CommercialInsurancePolicy Agreement_Id
Dimension Data Mapping DimCommercialInsurancePolicy PackagePrefix_Cd FND_FCML_DB CommercialInsurancePolicy PackagePrefix_Cd
Dimension Data Mapping DimCommercialInsurancePolicy PolicyPrefix_Cd FND_FCML_DB CommercialInsurancePolicy PolicyPrefix_Cd
Dimension Data Mapping DimCommercialInsurancePolicy PolicyIssuedBranch_Nb FND_FCML_DB CommercialInsurancePolicy RegionalOffice_Cd
Dimension Data Mapping DimCommercialInsurancePolicy Declaration_Nb FND_FCML_DB CommercialInsurancePolicy Declaration_Nb
Dimension Data Mapping DimCommercialInsurancePolicy SevenDigitPolicy_Nb FND_FCML_DB CommercialInsurancePolicy SevenDigitPolicy_Nb
Dimension Data Mapping DimCommercialInsurancePolicy TenDigitPolicy_Nb FND_FCML_DB CommercialInsurancePolicy TenDigitPolicy_Nb
Dimension Data Mapping DimCommercialInsurancePolicy DerivedStatus_Tp See Transformation Rule
Dimension Data Mapping DimCommercialInsurancePolicy DerivedStatus_Cd See Transformation Rule
Dimension Data Mapping DimCommercialInsurancePolicy DerivedStatus_Ds See Transformation Rule
ADW_INFRASTR
Dimension Data Mapping DimCommercialInsurancePolicy ManagedProduct_Cd DWP_SHR_DIM_PRDCT C_MNG_PRDCT
UCTURE
ADW_INFRASTR
Dimension Data Mapping DimCommercialInsurancePolicy ManagedProduct_Ds DWP_SHR_DIM_PRDCT T_MNG_PRDCT_DSC
UCTURE

ADW_INFRASTR
Dimension Data Mapping DimCommercialInsurancePolicy CommercialSeries_In DWP_SHR_DIM_PRDCT See Transformation Rule
UCTURE

Dimension Data Mapping DimCommercialInsurancePolicy MultiplePolicyDiscount_In FND_FCML_DB CommercialInsurancePolicy MultiplePolicyDiscount_In

Dimension Data Mapping DimCommercialInsurancePolicy OriginalPolicyEffective_Dt FND_FCML_DB CommercialInsurancePolicy OriginalPolicyEffective_Dt
Dimension Data Mapping DimCommercialInsurancePolicy PolicyEffective_Dt FND_FCML_DB CommercialInsurancePolicy PolicyEffective_Dt
Dimension Data Mapping DimCommercialInsurancePolicy PolicyEntry_Dt FND_FCML_DB CommercialInsurancePolicy PolicyEntry_Dt
Dimension Data Mapping DimCommercialInsurancePolicy PolicyExpiration_Dt FND_FCML_DB CommercialInsurancePolicy PolicyExpiration_Dt
Dimension Data Mapping DimCommercialInsurancePolicy Audit_In FND_FCML_DB CommercialInsurancePolicy Audit_In
Dimension Data Mapping DimCommercialInsurancePolicy AuditFrequency_Tp FND_FCML_DB CommercialInsurancePolicy AuditFrequency_Tp

Page 1 of 158
DimCommercialInsurancePolicy

Dimension Data Mapping DimCommercialInsurancePolicy AuditFrequency_Cd FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimCommercialInsurancePolicy AuditFrequency_Ds FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimCommercialInsurancePolicy Audit_Tp FND_FCML_DB CommercialInsurancePolicy Audit_Tp

Dimension Data Mapping DimCommercialInsurancePolicy Audit_Cd FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimCommercialInsurancePolicy Audit_Ds FND_FCML_DB MASTER_DATA See Transformation Rule

Data Mapping DimCommercialInsurancePolicy LineOfBusiness_Tp FND_FCML_DB CommercialInsurancePolicy LineOfBusiness_Tp

Dimension Data Mapping DimCommercialInsurancePolicy LineOfBusiness_Cd FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimCommercialInsurancePolicy LineOfBusiness_Ds FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimCommercialInsurancePolicy NewRenewal_In FND_FCML_DB CommercialInsurancePolicy NewRenewal_In

Dimension DimCommercialInsurancePolicy RatingMethod_Tp FND_FCML_DB CommercialInsurancePolicy RatingMethod_Tp

Dimension Data Mapping DimCommercialInsurancePolicy RatingMethod_Cd FND_FCML_DB MASTER_DATA See Transformation Rule

Page 2 of 158
DimCommercialInsurancePolicy

Dimension Data Mapping DimCommercialInsurancePolicy RatingMethod_Ds FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimCommercialInsurancePolicy ThreeYearRating_In FND_FCML_DB CommercialInsurancePolicy ThreeYearRating_In

Dimension Data Mapping DimCommercialInsurancePolicy PolicyIssue_Dt FND_FCML_DB CommercialInsurancePolicy OriginalPolicyProcess_Dt
Dimension Data Mapping DimCommercialInsurancePolicy ManualPolicy_In FND_FCML_DB CommercialInsurancePolicy ManualPolicy_In
Dimension Data Mapping DimCommercialInsurancePolicy Monoline_In FND_FCML_DB CommercialInsurancePolicy Monoline_In
Dimension Data Mapping DimCommercialInsurancePolicy BillingAccount_Nb FND_FCML_DB CommercialInsurancePolicy Account_Nm
Dimension Data Mapping DimCommercialInsurancePolicy BillingGroup_Nb FND_FCML_DB CommercialInsurancePolicy Group_Nm
Dimension Data Mapping DimCommercialInsurancePolicy PackageModification_Tp FND_FCML_DB CommercialInsurancePolicy PackageModification_Tp

Dimension Data Mapping DimCommercialInsurancePolicy PackageModification_Cd FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimCommercialInsurancePolicy PackageModification_Ds FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimCommercialInsurancePolicy Effective_Dt FND_FCML_DB CommercialInsurancePolicy Effective_Dt

Dimension Data Mapping DimCommercialInsurancePolicy Expiration_Dt FND_FCML_DB CommercialInsurancePolicy Expiration_Dt
Dimension Data Mapping DimCommercialInsurancePolicy Transaction_Ts FND_FCML_DB CommercialInsurancePolicy Transaction_Ts
Dimension Data Mapping DimCommercialInsurancePolicy Revision_Ts FND_FCML_DB CommercialInsurancePolicy Revision_Ts

Dimension Data Mapping DimCommercialInsurancePolicy PopulationInfo_Id FND_FCML_DB PopulationInfo See transformation rule

Page 3 of 158
DimCommercialInsurancePolicy

Target
Form Additiona
Referenc
Target Target Rated/No l Last Last
Target e or Additional
Transformation Rule 1 Transformation Rule 2 Form # Form # n- Exclusio Updated Updated Status ISSUE
Form # Lookup Comments
mapped tested Rated/Ma ns / By Date
Table
nually Validity
Rated

For each CommercialInsurancePolicy row, generate 1

DJL 1/29/2007
DimCommercialInsurancePolicy row
Move
Move
Move
Move
Move
Move
Move
Concatenate RegionalOffice_Cd, Declaration_Nb, SevenDigitPAKPolicy_Nb EVG 11/8/2007
Default (zero) EVG 11/6/2007
Default (Blank) EVG 11/7/2007
Default (Blank) EVG 11/8/2007

Move

IF Infrastructure.T_PLCY_PFX_GRP_DSC LIKE '%allied series

%'
CDW 10/29/2007
THEN 1
ELSE 0
Move
Move
Move
Move
Move
Move
Move

Page 4 of 158
DimCommercialInsurancePolicy

find first occurance of following:

(LSD_Value)
Where PTD_Name =
RLJ 11/2/2007
FND_FCML_DB.COMMERCIALINSURANCEPOLICY.AUDITF
REQUENCY_TP AND
PTD_Value = AuditFrequency_Tp

find first occurance of following:

(DESC)
Where PTD_Name =
RLJ 11/9/2007
FND_FCML_DB.COMMERCIALINSURANCEPOLICY.AUDITF
REQUENCY_TP AND
PTD_Value = AuditFrequency_Tp

Move

find first occurance of following:

(LSD_Value)
Where PTD_Name =
RLJ 11/2/2007
FND_FCML_DB.COMMERCIALINSURANCEPOLICY.AUDIT_
TP AND
PTD_Value = Audit_Tp

find first occurance of following:

(DESC)
Where PTD_Name =
RLJ 11/2/2007
FND_FCML_DB.COMMERCIALINSURANCEPOLICY.AUDIT_
TP AND
PTD_Value = Audit_Tp

Move

find first occurance of following:

(LSD_Value)
Where PTD_Name =
RLJ 11/2/2007
FND_FCML_DB.COMMERCIALINSURANCEPOLICY.LINEOF
BUSINESS_TP AND
PTD_Value = LineOfBusiness_Tp

find first occurance of following:

(DESC)
Where PTD_Name =
RLJ 11/2/2007
FND_FCML_DB.COMMERCIALINSURANCEPOLICY.LINEOF
BUSINESS_TP AND
PTD_Value = LineOfBusiness_Tp

Move
Move

find first occurance of following:

(LSD_Value)
Where PTD_Name =
RLJ 11/2/2007
FND_FCML_DB.COMMERCIALINSURANCEPOLICY.RATING
METHOD_TP AND
PTD_Value = RatingMethod_Tp

Page 5 of 158
DimCommercialInsurancePolicy

find first occurance of following:

(DESC)
Where PTD_Name =
RLJ 11/2/2007
FND_FCML_DB.COMMERCIALINSURANCEPOLICY.RATING
METHOD_TPD AND
PTD_Value = RatingMethod_Tp

Move
Move
Move
Move
Move
Move
Move

find first occurance of following:

(LSD_Value)
Where PTD_Name =
RLJ 11/2/2007
FND_FCML_DB.CommercialInsurancePolicy.PackageModifica
tion_Tp AND
PTD_Value = PackageModification_Tp

find first occurance of following:

(DESC)
Where PTD_Name =
RLJ 11/2/2007
FND_FCML_DB.CommercialInsurancePolicy.PackageModifica
tion_Tp AND
PTD_Value = PackageModification_Tp

Move
Move
Move
Move

- Population Cycle Description

- Population Timestamp ( date and time of population cycle)
(Transaction_Ts)
RLJ 11/9/2007
- Source System Label (description of application or system
from which the information last used to update the entity
instance was populated).

Page 6 of 158
DimCommercialInsurancePolicy

EA Data
Element
Group
EA Data EA EA Data Element EA EA Re- Comment
EA System Name EA Layout Name (Copy- EA Data
Element Group Name (column, field, Occurs defines (EA va IS
ID (table, book) Type
ID Level attribute) Number Flag vs IAA)
segment,
file,
entity)

Page 7 of 158
DimCommercialInsurancePolicy

Page 8 of 158
DimCommercialInsurancePolicy

Page 9 of 158
DimCommercialPackage

The DimCommercialPackage structure is defined in this

tab. The SQL used to create an example view appears
below. The mapping information is provided below the
SQL.

Resource Status

Dale Lindquist Review

Target Instance Target Instance Target Data Element Name Source Source Segment Source Data Element Name (column,
Target Table Name
Identifier Rule Type (column, field, attribute) Database(s) Code(s) field, attribute)

Dimension Row Building Rule DimCommercialPackage FND_FCML_DB CommercialPackage

Dimension Data Mapping DimCommercialPackage CommercialPackage_Id FND_FCML_DB CommercialPackage CommercialPackage_Id

Dimension Data Mapping DimCommercialPackage Agreement_Id FND_FCML_DB CommercialPackage Agreement_Id
Dimension Data Mapping DimCommercialPackage PackagePrefix_Cd FND_FCML_DB CommercialPackage PackagePrefix_Cd
Dimension Data Mapping DimCommercialPackage PolicyIssuedBranch_Nb FND_FCML_DB CommercialPackage RegionalOffice_Cd
Dimension Data Mapping DimCommercialPackage Declaration_Nb FND_FCML_DB CommercialPackage Declaration_Nb
Dimension Data Mapping DimCommercialPackage SevenDigitPAKPolicy_Nb FND_FCML_DB CommercialPackage SevenDigitPAKPolicy_Nb
Dimension Data Mapping DimCommercialPackage TenDigitPAKPolicy_Nb FND_FCML_DB CommercialPackage See Transformation Rule
Dimension Data Mapping DimCommercialPackage DerivedStatus_Tp See Transformation Rule
Dimension Data Mapping DimCommercialPackage DerivedStatus_Cd See Transformation Rule
Dimension Data Mapping DimCommercialPackage DerivedStatus_Ds See Transformation Rule
Dimension Data Mapping DimCommercialPackage ServiceCenter_In FND_FCML_DB CommercialPackage ServiceCenter_In
Dimension Data Mapping DimCommercialPackage MembershipDiscount_Tp FND_FCML_DB CommercialPackage MembershipDiscount_Tp

Dimension Data Mapping DimCommercialPackage MembershipDiscount_Cd FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimCommercialPackage MembershipDiscount_Ds FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimCommercialPackage NewRenewal_In FND_FCML_DB CommercialPackage NewRenewal_In

Dimension Data Mapping DimCommercialPackage Program_Tp FND_FCML_DB CommercialPackage SystemPopulation_Tp

Page 1 of 158
DimCommercialPackage

Dimension Data Mapping DimCommercialPackage Program_Cd FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimCommercialPackage Program_Ds FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimCommercialPackage ThreeYearRatingEffective_Dt FND_FCML_DB CommercialPackage RatingEffectiveStart_Dt

Dimension Data Mapping DimCommercialPackage ThreeYearRatingExpiration_Dt FND_FCML_DB CommercialPackage RatingExpirationEnd_Dt

Dimension Data Mapping DimCommercialPackage CancelRelease_Dt FND_FCML_DB CommercialPackage CancelRelease_Dt

Dimension Data Mapping DimCommercialPackage PackageEffective_Dt FND_FCML_DB CommercialPackage PolicyEffective_Dt

Dimension Data Mapping DimCommercialPackage PackageExpiration_Dt FND_FCML_DB CommercialPackage PolicyExpiration_Dt

Dimension Data Mapping DimCommercialPackage IssueRelease_Dt FND_FCML_DB CommercialPackage IssueRelease_Dt

Dimension Data Mapping DimCommercialPackage OriginalPackageEffective_Dt FND_FCML_DB CommercialPackage OriginalPolicyEffective_Dt

Dimension Data Mapping DimCommercialPackage PackageCancel_Dt FND_FCML_DB CommercialPackage PackageCancel_Dt

Dimension Data Mapping DimCommercialPackage InternetQuote_In FND_FCML_DB CommercialPackage InternetQuote_In

Dimension Data Mapping DimCommercialPackage MemberDiscount_In FND_FCML_DB CommercialPackage MemberDiscount_In
Dimension Data Mapping DimCommercialPackage Reinsurance_In FND_FCML_DB CommercialPackage Reinsurance_In
Dimension Data Mapping DimCommercialPackage Transfer_Tp FND_FCML_DB CommercialPackage TransferUnit_Tp

Dimension Data Mapping DimCommercialPackage Transfer_Cd FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimCommercialPackage Transfer_Ds FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimCommercialPackage AutoRenewal_In FND_FCML_DB CommercialPackage AutoRenewal_In

Dimension Data Mapping DimCommercialPackage PreviousPackage_Nb FND_FCML_DB CommercialPackage PreviousPackage_Nb

Page 2 of 158
DimCommercialPackage

Data Mapping DimCommercialPackage UniqueLinesOnPackage_Ct FND_FCML_DB CommercialPackage See Transformation Rule

Dimension Data Mapping DimCommercialPackage UnderwriterProgram_Nb FND_FCML_DB CommercialPackage UnderwriterProgram_Nb

Dimension Data Mapping DimCommercialPackage Effective_Dt FND_FCML_DB CommercialPackage Effective_Dt
Dimension Data Mapping DimCommercialPackage Expiration_Dt FND_FCML_DB CommercialPackage Expiration_Dt
Dimension Data Mapping DimCommercialPackage Transaction_Ts FND_FCML_DB CommercialPackage Transaction_Ts
Dimension Data Mapping DimCommercialPackage Revision_Ts FND_FCML_DB CommercialPackage Revision_Ts
Dimension Data Mapping DimCommercialPackage InsuredBusiness_Ds FND_FCML_DB CommercialPackage InsuredBusiness_Ds

Dimension Data Mapping DimCommercialPackage PopulationInfo_Id FND_FCML_DB PopulationInfo See transformation rule

Page 3 of 158
DimCommercialPackage

For each CommercialPackage row, generate 1

DJL 1/29/2008
DimCommercialPackage row
Move
Move
Move
Move
Move
Move
Concatenate RegionalOffice_Cd, Declaration_Nb, SevenDigitPAKPolicy_Nb
Default (zero) EVG 11/6/2007
Default (Blank) EVG 11/7/2007
Default (Blank) EVG 11/8/2007
Move NOTE: RFC will do lookups in foundation
Move

find first occurance of following:

(LSD_Value)
Where PTD_Name =
RLJ 11/2/2007
FND_FCML_DB.COMMERCIALPACKAGE.MEMBERSHIPDIS
COUNT_TP AND
PTD_Value = MembershipDiscount_Tp

find first occurance of following:

(DESC)
Where PTD_Name =
RLJ 11/2/2007
FND_FCML_DB.COMMERCIALPACKAGE.MEMBERSHIPDIS
COUNT_TP AND
PTD_Value = MembershipDiscount_Tp

Move
Move

Page 4 of 158
DimCommercialPackage

find first occurance of following:

(LSD_Value)
Where PTD_Name =
RLJ 11/9/2007
FND_FCML_DB.COMMERCIALPACKAGE.SYSTEMPOPULA
TION_TP AND
PTD_Value = SystemPopulation_Tp

find first occurance of following:

(DESC)
Where PTD_Name =
RLJ 11/9/2007
FND_FCML_DB.COMMERCIALPACKAGE.SYSTEMPOPULA
TION_TP AND
PTD_Value = SystemPopulation_Tp

Move

Move DJL 10/25/2007

Move

Move DJL 10/25/2007

Move

Move
Move
Move
Move

find first occurance of following:

(LSD_Value)
Where PTD_Name =
RLJ 11/9/2007
FND_FCML_DB.COMMERCIALPACKAGE.TRANSFERUNIT_
TP AND
PTD_Value = TransferUnit_Tp

find first occurance of following:

(DESC)
Where PTD_Name =
RLJ 11/9/2007
FND_FCML_DB.COMMERCIALPACKAGE.TRANSFERUNIT_
TP AND
PTD_Value = TransferUnit_Tp

Move
Move

Page 5 of 158
DimCommercialPackage

Changed from
LineOfBusines
count the distinct LineOfBusiness_Tp for each CDW 11/20/2007 s_Cd to
unique commercial package LineOfBusines
s_Tp

Move
Move
Move
Move
Move
Move

- Population Cycle Description

- Population Timestamp ( date and time of population cycle)
(Transaction_Ts)
RLJ 11/9/2007
- Source System Label (description of application or system
from which the information last used to update the entity
instance was populated).

Page 6 of 158
DimCommercialPackage

Page 7 of 158
DimCommercialPackage

Page 8 of 158
DimCommercialPackage

Page 9 of 158
DimDBAName

The DimDBAName structure is is defined in this tab. The SQL used to

create an example view appears below. The mapping information is
provided below the SQL.
Resource Status

Leisa Miller Review

Target
Instance Target Instance Rule Type Target Table Name
Target Data Element Name (column, field, Source Source Segment Source Data Element Name (column, field,
Identifier
attribute) Database(s) Code(s) attribute)

Dimension Row Building Rule DimDBAName FND_FCML_DB UnstructuredName

Dimension Data Mapping DimDBAName DBAName_Id FND_FCML_DB UnstructuredName UnstructuredName_Id

Dimension Data Mapping DimDBAName Party_Id FND_FCML_DB UnstructuredName Party_Id
Dimension Data Mapping DimDBAName Usage_Tp FND_FCML_DB UnstructuredName Usage_Tp

Dimension Data Mapping DimDBAName Usage_Cd FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimDBAName Usage_Ds FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimDBAName DBA_Nm FND_FCML_DB UnstructuredName Unstructured_Nm

Dimension Data Mapping DimDBAName Effective_Dt FND_FCML_DB UnstructuredName Effective_Dt
Dimension Data Mapping DimDBAName Expiration_Dt FND_FCML_DB UnstructuredName Expiration_Dt
Dimension Data Mapping DimDBAName Transaction_Ts FND_FCML_DB UnstructuredName Transaction_Ts
Dimension Data Mapping DimDBAName Revision_Ts FND_FCML_DB UnstructuredName Revision_Ts

Dimension Data Mapping DimDBAName PopulationInfo_Id FND_FCML_DB PopulationInfo See transformation rule

Page 1 of 158
DimDBAName

Target Form
Reference Additional Last
Transformat Target Form Target Form Target Form Rated/Non- Last Additional EA Data
Transformation Rule 1 or Lookup Exclusions / Updated Status ISSUE
ion Rule 2 # # mapped # tested Rated/Manu Updated By Comments Element ID
Table Validity Date
ally Rated

For each UnstructuredName row where USAGE_TP = 'DBA'

DJL 1/29/2008
(Doing Business As), generate 1 DimDBAName row

Move
Move
Move

find first occurance of following:

(LSD_Value)
Where PTD_Name = RLJ 11/2/2007
FND_FCML_DB.UNSTRUCTUREDNAME.USAGE_TP AND
PTD_Value = Usage_Tp

find first occurance of following:

(DESC)
Where PTD_Name = RLJ 11/2/2007
FND_FCML_DB.UNSTRUCTUREDNAME.USAGE_TP AND
PTD_Value = Usage_Tp

Move
Move
Move
Move
Move

- Population Cycle Description

- Population Timestamp ( date and time of population cycle)
(Transaction_Ts)
RLJ 11/9/2007
- Source System Label (description of application or system
from which the information last used to update the entity
instance was populated).

Page 2 of 158
DimDBAName

EA Data
Element
Group EA Re- Comment
EA Group EA Data Element Name EA Data EA Occurs
EA System ID Name EA Layout Name (Copy-book) defines Flag (EA va IS vs
Level (column, field, attribute) Type Number
(table, IAA)
segment,
file, entity)

Page 3 of 158
DimInsuredName

The DimInsuredName structure is defined in this tab. The SQL used to

create an example view appears below. The mapping information is
provided below the SQL.
Resource Status

Leisa Miller Review

Dimension Row Building Rule DimInsuredName FND_FCML_DB UnstructuredName

Dimension Data Mapping DimInsuredName InsuredName_Id FND_FCML_DB UnstructuredName UnstructuredName_Id

Dimension Data Mapping DimInsuredName Party_Id FND_FCML_DB UnstructuredName Party_Id
Dimension Data Mapping DimInsuredName Usage_Tp FND_FCML_DB UnstructuredName Usage_Tp

Dimension Data Mapping DimInsuredName Usage_Cd FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimInsuredName Usage_Ds FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimInsuredName Insured_Nm FND_FCML_DB UnstructuredName Unstructured_Nm

Dimension Data Mapping DimInsuredName Effective_Dt FND_FCML_DB UnstructuredName Effective_Dt
Dimension Data Mapping DimInsuredName Expiration_Dt FND_FCML_DB UnstructuredName Expiration_Dt
Dimension Data Mapping DimInsuredName Transaction_Ts FND_FCML_DB UnstructuredName Transaction_Ts
Dimension Data Mapping DimInsuredName Revision_Ts FND_FCML_DB UnstructuredName Revision_Ts

Dimension Data Mapping DimInsuredName PopulationInfo_Id FND_FCML_DB PopulationInfo See transformation rule

Page 1 of 158
DimInsuredName

For each UnstructuredName row where USAGE_TP = 'Default'

DJL 1/29/2008
(Default), generate 1 DimInsuredName row
Move DJL
Move DJL 6/1/2007
Move

find first occurance of following:

(LSD_Value)
Where PTD_Name = RLJ 11/2/2007
FND_FCML_DB.UNSTRUCTUREDNAME.USAGE_TP AND
PTD_Value = Usage_Tp

find first occurance of following:

(DESC)
Where PTD_Name = RLJ 11/2/2007
FND_FCML_DB.UNSTRUCTUREDNAME.USAGE_TP AND
PTD_Value = Usage_Tp

Move EVG 5/14/2007

Move DJL
Move DJL
Move DJL 5/30/2007
Move DJL

- Population Cycle Description

- Population Timestamp ( date and time of population cycle)
(Transaction_Ts)
RLJ 11/9/2007
- Source System Label (description of application or system
from which the information last used to update the entity
instance was populated).

Page 2 of 158
DimInsuredName

Page 3 of 158
DimInsuredCompany

The DimInsuredCompany structure is defined in this tab. The SQL

used to create an example view appears below. The mapping
information is provided below the SQL.

Resource Status

Leisa Miller Review

Target Source
Target Data Element Name Source Source Data Element Name
Instance Target Instance Rule Type Target Table Name Segment
(column, field, attribute) Database(s) (column, field, attribute)
Identifier Code(s)

Dimension Row Building Rule DimInsuredCompany FND_FCML_DB Company

Dimension Data Mapping DimInsuredCompany InsuredCompany_Id FND_FCML_DB Company Company_Id

Dimension Data Mapping DimInsuredCompany Party_Id FND_FCML_DB Company Party_Id
Dimension Data Mapping DimInsuredCompany Company_Nb FND_FCML_DB Party Empty String
Dimension Data Mapping DimInsuredCompany LegalEntity_Tp FND_FCML_DB Company Company_Tp

Dimension Data Mapping DimInsuredCompany LegalEntity_Cd FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimInsuredCompany LegalEntity_Ds FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimInsuredCompany Effective_Dt FND_FCML_DB Company Effective_Dt

Dimension Data Mapping DimInsuredCompany Expiration_Dt FND_FCML_DB Company Expiration_Dt
Dimension Data Mapping DimInsuredCompany Transaction_Ts FND_FCML_DB Company Transaction_Ts
Dimension Data Mapping DimInsuredCompany Revision_Ts FND_FCML_DB Company Revision_Ts

Dimension Data Mapping DimInsuredCompany PopulationInfo_Id FND_FCML_DB PopulationInfo See transformation rule

Page 1 of 158
DimInsuredCompany

For each Company row where COMPANY_TP =

DJL 1/29/2008
'CustomerCompany_Tp', generate 1 DimCompany row
Move DJL 11/5/2007
Move DJL 10/24/2007
Default CDW 11/13/2007
Move DJL 10/24/2007

find first occurance of following:

(LSD_Value)
Where PTD_Name = RLJ 11/9/2007
FND_FCML_DB.COMPANY.COMPANY_TP AND
PTD_Value = Company_Tp

find first occurance of following:

(DESC)
Where PTD_Name = RLJ 11/9/2007
FND_FCML_DB.COMPANY.COMPANY_TP AND
PTD_Value = Company_Tp

Move DJL 10/24/2007

Move DJL 10/24/2007
Move DJL 10/24/2007
Move DJL 10/24/2007

- Population Cycle Description

- Population Timestamp ( date and time of population cycle)
(Transaction_Ts)
RLJ 11/9/2007
- Source System Label (description of application or system
from which the information last used to update the entity
instance was populated).

Page 2 of 158
DimInsuredCompany

Page 3 of 158
DimInsuringCompany

The DimInsuringCompany structure is defined in this tab. The SQL

used to create an example view appears below. The mapping
information is provided below the SQL.

Resource Status

Neha Agrawal Review

Dimension Row Building Rule DimInsuringCompany FND_FCML_DB Company

Dimension Data Mapping DimInsuringCompany InsuringCompany_Id FND_FCML_DB Company Company_Id

Dimension Data Mapping DimInsuringCompany Party_Id FND_FCML_DB Company Party_Id
Dimension Data Mapping DimInsuringCompany Company_Nb FND_FCML_DB Party ExternalReference_Nb
Dimension Data Mapping DimInsuringCompany LegalEntity_Tp FND_FCML_DB Company Company_Tp

Dimension Data Mapping DimInsuringCompany LegalEntity_Cd FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimInsuringCompany LegalEntity_Ds FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimInsuringCompany Effective_Dt FND_FCML_DB Company Effective_Dt

Dimension Data Mapping DimInsuringCompany Expiration_Dt FND_FCML_DB Company Expiration_Dt
Dimension Data Mapping DimInsuringCompany Transaction_Ts FND_FCML_DB Company Transaction_Ts
Dimension Data Mapping DimInsuringCompany Revision_Ts FND_FCML_DB Company Revision_Ts

Dimension Data Mapping DimInsuringCompany PopulationInfo_Id FND_FCML_DB PopulationInfo See transformation rule

Page 1 of 158
DimInsuringCompany

For each Company row where COMPANY_TP =

DJL 1/29/2008
'NationwideCompany_Tp', generate 1 DimCompany row
Move CDW 8/20/2007
Move CDW 8/20/2007
Move CDW 11/13/2007
Move CDW 8/20/2007

find first occurance of following:

(LSD_Value)
Where PTD_Name = RLJ 11/9/2007
FND_FCML_DB.COMPANY.COMPANY_TP AND
PTD_Value = Company_Tp

find first occurance of following:

(DESC)
Where PTD_Name = RLJ 11/9/2007
FND_FCML_DB.COMPANY.COMPANY_TP AND
PTD_Value = Company_Tp

Move CDW 8/20/2007

Move CDW 8/20/2007
Move CDW 8/20/2007
Move CDW 8/20/2007

- Population Cycle Description

- Population Timestamp ( date and time of population cycle)
(Transaction_Ts)
RLJ 11/9/2007
- Source System Label (description of application or system
from which the information last used to update the entity
instance was populated).

Page 2 of 158
DimInsuringCompany

Page 3 of 158
The DimManagedProduct structure is defined in this tab.
The SQL used to create an example view appears below.
The mapping information is provided below the SQL.

Resource

Chad

Target
Target Data Element Name (column, field,
Instance Target Instance Rule Type Target Table Name
attribute)
Identifier

Dimension Row Building Rule DimManagedProduct

Dimension Data Mapping DimManagedProduct ManagedProduct_Id

Dimension Data Mapping DimManagedProduct ManagedProductAgreement_Id

Dimension Data Mapping DimManagedProduct PackagePrefix_Cd

Dimension Data Mapping DimManagedProduct ManagedProduct_Cd
Dimension Data Mapping DimManagedProduct ManagedProduct_Ds
Dimension Data Mapping DimManagedProduct PolicyIssuedBranch_Nb
Dimension Data Mapping DimManagedProduct SevenDigitPolicy_Nb

Dimension Data Mapping DimManagedProduct DerivedStatus_Tp

Dimension Data Mapping DimManagedProduct DerivedStatus_Cd

Dimension Data Mapping DimManagedProduct DerivedStatus_Ds

Dimension Data Mapping DimManagedProduct Effective_Dt

Dimension Data Mapping DimManagedProduct Expiration_Dt

Dimension Data Mapping DimManagedProduct Transaction_Ts

Dimension Data Mapping DimManagedProduct Revision_Ts

Dimension Data Mapping DimManagedProduct PopulationInfo_Id

Status

Review

Source Data Element Name

Source Database(s) Source Segment Code(s)
(column, field, attribute)

FND_FCML_DB CommercialInsurancePolicy

FND_FCML_DB

FND_FCML_DB CommercialInsurancePolicy See Transformation Rule

FND_FCML_DB CommercialInsurancePolicy PackagePrefix_Cd

ADW_INFRASTRUCTURE DWP_SHR_DIM_PRDCT C_MNG_PRDCT
ADW_INFRASTRUCTURE DWP_SHR_DIM_PRDCT T_MNG_PRDCT_DSC
FND_FCML_DB CommercialInsurancePolicy RegionalOffice_Cd
FND_FCML_DB CommercialInsurancePolicy SevenDigitPolicy_Nb

FND_FCML_DB MASTER_DATA See Transformation Rule

FND_FCML_DB CommercialInsurancePolicy Effective_Dt

FND_FCML_DB CommercialInsurancePolicy Expiration_Dt

FND_FCML_DB CommercialInsurancePolicy Transaction_Ts

FND_FCML_DB CommercialInsurancePolicy Revision_Ts

RPT_FCML_DB PopulationInfo See transformation rule

Target Form
Transformation Rule 1 Transformation Rule 2
#

For each grouping of CommercialInsurancePolicy rows by

managed product, generate 1 unique data table row
Reuse existing keys, only create new rows
Generate unique id (Max + 1)
(Squish).

Produce unique id - Business key (PackagePrefix_id,

RegionalOffice_Cd, SevenDigitPolicy_Nb, C_Mng_Prdct)

Move
Move See SQL example for join logic
Move See SQL example for join logic
Move
Move
If any Managed_Product is active then DerivedStatus_Tp = 3
Else DerivedStatus_Tp = 4
Use DerivedStatus_Tp value to lookup code in cross-reference
tables.
Use DerivedStatus_Tp value to lookup description in cross-
reference tables.
Given Managed_Product key where DerivedStatus_Tp = 3,
move MIN(Effective_Dt)
Given Managed_Product key where DerivedStatus_Tp = 3,
Use date chaining rules
move MAX(Expiration_Dt)
Given Managed_Product key where DerivedStatus_Tp = 3,
move MIN(Transaction_Ts)
Given Managed_Product key where DerivedStatus_Tp = 3,
Use date chaining rules
move MAX(Revision_Ts)

- Population Cycle Description

- Population Timestamp ( date and time of population cycle)
(Transaction_Ts)
- Source System Label (description of application or system
from which the information last used to update the entity
instance was popu
Target Form
Reference Additional Last
Target Form Target Form Rated/Non- Last
or Lookup Exclusions / Updated Status ISSUE
# mapped # tested Rated/Manu Updated By
Table Validity Date
ally Rated

CDW 10/31/2007

CDW 10/29/2007
CDW 10/29/2007

CDW 10/29/2007

CDW 10/31/2007

CDW 11/2/2007
EA Data
Element
Group
Additional EA Data EA Group
EA System ID Name EA Layout Name (Copy-book)
Comments Element ID Level
(table,
segment,
file, entity)

If "Squishing"
which
agreement_id
do we take?
EA Re- Comment
EA Data Element Name EA Data EA Occurs
defines Flag (EA va IS vs
(column, field, attribute) Type Number
IAA)
DimMoneyScheduler

The DimMoneyScheduler structure is defined in this tab. The SQL used

to create an example view appears below. The mapping information is
provided below the SQL.
Resource Status
--CREATE VIEW DimMoneyScheduler AS
SELECT DISTINCT
MoneyScheduler_Id AS MoneyScheduler_Id,
MoneySchedulerType_Id AS MoneySchedulerType_Id,
AnchorType.Type_Ds As MoneyScheduler_Ds,
Agreement_Id AS Agreement_Id, Neha Agrawal Review
Bill_Tp AS Bill_Tp,
BillType.PHYSICAL_SOURCE_DOMAIN_NAME AS Bill_Cd,
BillType.DESCRIPTION AS Bill_Ds,
Target
BillingPlan_Tp AS BillingPlan_Tp,
Instance Target Instance Rule Type Target Table Name
Target Data Element Name (column, field, Source Source Segment Source Data Element Name (column, field,
BillingPlan.PHYSICAL_SOURCE_DOMAIN_NAME
Identifier AS BillingPlan_Cd, attribute) Database(s) Code(s) attribute)
BillingPlan.DESCRIPTION AS BillingPlan_Ds,
BillPayor_Tp AS BillPayor_Tp,
BillPayor.PHYSICAL_SOURCE_DOMAIN_NAME AS BillPayor_Cd,
BillPayor.DESCRIPTION AS BillPayor_Ds,
Dimension Row Building Rule DimMoneyScheduler FND_FCML_DB MoneyScheduler
End_Date AS BillingEnd_Dt,
A.Effective_Dt
Dimension AS Effective_D,
Data Mapping DimMoneyScheduler MoneyScheduler_Id FND_FCML_DB MoneyScheduler MoneyScheduler_Id
A.Expiration_Dt
Dimension AS Expiration_Dt,
Data Mapping DimMoneyScheduler MoneySchedulerType_Id FND_FCML_DB MoneyScheduler MoneySchedulerType_Id
A.Transaction_Ts
Dimension
AS Transaction_Ts,
Data Mapping DimMoneyScheduler MoneyScheduler_Ds FND_FCML_DB AnchorType Type_Ds
A.Revision_Ts AS Revision_Ts,
Dimension Data Mapping DimMoneyScheduler Agreement_Id FND_FCML_DB MoneyScheduler Agreement_Id
A.PopulationInfo_Id AS PopulationInfo_Id
Dimension Data Mapping DimMoneyScheduler Bill_Tp FND_FCML_DB MoneyScheduler Bill_Tp
FROM FND_FCML_DB_TEST.MoneyScheduler A

LEFT OUTER JOIN FND_FCML_DB_TEST.MASTER_DATA BillType

ONDimension Data Mapping DimMoneyScheduler
Bill_Tp = BillType.PHYSICAL_TARGET_DOMAIN_VALUE Bill_Cd FND_FCML_DB MASTER_DATA See Transformation Rule
AND BillType.PHYSICAL_TARGET_DOMAIN_NAME =
'FND_FCML_DB.MONEYSCHEDULER.BILL_TP'

LEFT OUTER JOIN FND_FCML_DB_TEST.MASTER_DATA BillingPlan

ONDimension Data Mapping DimMoneyScheduler
Bill_Tp = BillingPlan.PHYSICAL_TARGET_DOMAIN_VALUE Bill_Ds FND_FCML_DB MASTER_DATA See Transformation Rule
AND BillingPlan.PHYSICAL_TARGET_DOMAIN_NAME =
'FND_FCML_DB.MONEYPROVISIONELEMENT.BILLINGPLAN_TP'
Dimension Data Mapping DimMoneyScheduler BillingPlan_Tp FND_FCML_DB MoneyScheduler BillingPlan_Tp
LEFT OUTER JOIN FND_FCML_DB_TEST.MASTER_DATA BillPayor
ON Bill_Tp = BillPayor.PHYSICAL_TARGET_DOMAIN_VALUE
AND BillPayor.PHYSICAL_TARGET_DOMAIN_NAME
Dimension Data Mapping
=
DimMoneyScheduler BillingPlan_Cd FND_FCML_DB MASTER_DATA See Transformation Rule
'FND_FCML_DB.MONEYSCHEDULER.BILLPAYOR_TP'

JOIN RPT_FCML_DB_TEST.AnchorType
ON MoneySchedulerType_Id = AnchorType.AnchorType_Id
;
Dimension Data Mapping DimMoneyScheduler BillingPlan_Ds FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimMoneyScheduler BillPayor_Tp FND_FCML_DB MoneyScheduler BillPayor_Tp

Page 1 of 158
DimMoneyScheduler

Target Source
Target Data Element Name (column, field, Source Segment Source Data Element Name (column, field,
Instance Target Instance Rule Type Target Table Name
attribute) Database(s) Code(s) attribute)
Identifier

Dimension Data Mapping DimMoneyScheduler BillPayor_Cd FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimMoneyScheduler BillPayor_Ds FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimMoneyScheduler BillingEnd_Dt FND_FCML_DB MoneyScheduler End_Date

Dimension Data Mapping DimMoneyScheduler Effective_Dt FND_FCML_DB MoneyScheduler Effective_Dt
Dimension Data Mapping DimMoneyScheduler Expiration_Dt FND_FCML_DB MoneyScheduler Expiration_Dt
Dimension Data Mapping DimMoneyScheduler Transaction_Ts FND_FCML_DB MoneyScheduler Transaction_Ts
Dimension Data Mapping DimMoneyScheduler Revision_Ts FND_FCML_DB MoneyScheduler Revision_Ts

Dimension Data Mapping DimMoneyScheduler PopulationInfo_Id FND_FCML_DB PopulationInfo See transformation rule

Page 2 of 158
DimMoneyScheduler

For each MoneyScheduler row, generate 1

DJL 1/29/2008
DimMoneyScheduler row
Move CDW 8/20/2007
Move CDW 8/20/2007
See SQL example for JOIN Logic
Move CDW 8/20/2007
Move CDW 8/20/2007

find first occurance of following:

(LSD_Value)
Where PTD_Name = RLJ 11/2/2007
FND_FCML_DB.MONEYSCHEDULER.BILL_TP AND
PTD_Value = Bill_Tp

find first occurance of following:

(DESC)
Where PTD_Name = RLJ 11/2/2007
FND_FCML_DB.MONEYSCHEDULER.BILL_TP AND
PTD_Value = Bill_Tp

Move DJL 10/24/2007

find first occurance of following:

(LSD_Value)
Where PTD_Name =
RLJ 11/2/2007
FND_FCML_DB.MONEYSCHEDULER.BILLINGPLAN_TP
AND
PTD_Value = BillingPlan_Tp

find first occurance of following:

(DESC)
Where PTD_Name =
RLJ 11/2/2007
FND_FCML_DB.MONEYSCHEDULER.BILLINGPLAN_TP
AND
PTD_Value = BillingPlan_Tp

Move CDW 8/20/2007

Page 3 of 158
DimMoneyScheduler

find first occurance of following:

(LSD_Value)
Where PTD_Name = RLJ 11/2/2007
FND_FCML_DB.MONEYSCHEDULER.BILLPAYOR_TP AND
PTD_Value = BillPayor_Tp

find first occurance of following:

(DESC)
Where PTD_Name = RLJ 11/2/2007
FND_FCML_DB.MONEYSCHEDULER.BILLPAYOR_TP AND
PTD_Value = BillPayor_Tp

Move CDW 8/20/2007

Move CDW 8/20/2007
Move CDW 8/20/2007
Move CDW 8/20/2007
Move CDW 8/20/2007

- Population Cycle Description

- Population Timestamp ( date and time of population cycle)
(Transaction_Ts)
RLJ 11/9/2007
- Source System Label (description of application or system
from which the information last used to update the entity
instance was populated).

Page 4 of 158
DimMoneyScheduler

Page 5 of 158
DimMoneyScheduler

Page 6 of 158
DimOrganizationUnit

The DimAgency structure is defined in this tab. The SQL used to create
an example view appears below. The mapping information is provided
below the SQL.

Resource Status
CREATE VIEW RPT_FCML_DB_TEST.DimOrganizationUnit AS
-- AGENCY
SELECT
OrganizationUnit.OrganizationUnit_Id AS OrganizationUnit_Id,
OrganizationUnit.Party_Id AS Party_Id,
Party.PartyType_Id AS PartyAnchor_Tp, Neha Agrawal Review
AnchorType.Type_Ds AS PartyAnchor_Ds,
SUBSTRING(Party.ExternalReference_Nb, 1,6) AS OrganizationUnit_Nb, --
Agency Code, Appt_Rgn_Ofc?, Appt_Super_Region, Appt_Division
State_Type.PHYSICAL_SOURCE_DOMAIN_VALUE
Target AS State_Nb, Target Data Element Source Source Data Element
State_Tp Source
Instance AS State_Tp,
Target Instance Rule Type Target Table Name Name (column, field, Segment Name (column, field,
State_Type.LOGICAL_SOURCE_DOMAIN_VALUE AS State_Cd, Database(s)
Identifier attribute) Code(s) attribute)
State_Type.DESCRIPTION AS State_Ds,
UnstructuredName1.Unstructured_Nm AS AbbreviatedName_Cd,
UnstructuredName2.Unstructured_Nm AS ShortName_Tt,
UnstructuredName3.Unstructured_Nm AS LongName_Tt,
OrganizationUnit.Effective_Dt AS Effective_Dt,
OrganizationUnit.Expiration_Dt
Dimension AS Expiration_Dt,
Row Building Rule DimOrganizationUnit FND_FCML_DB OrganizationUnit
OrganizationUnit.Transaction_Ts AS Transaction_Ts,
OrganizationUnit.Revision_Ts AS Revision_Ts,
OrganizationUnit.PopulationInfo_Id
Dimension Data Mapping AS PopulationInfo_Id
DimOrganizationUnit OrganizationUnit_Id FND_FCML_DB OrganizationUnit OrganizationUnit_Id
FROM FND_FCML_DB_TEST.OrganizationUnit
Dimension Data Mapping OrganizationUnit
DimOrganizationUnit Party_Id FND_FCML_DB OrganizationUnit Party_Id
Dimension Data Mapping DimOrganizationUnit PartyAnchor_Tp FND_FCML_DB Party PartyType_Id
JOIN FND_FCML_DB.Party Party
ONDimension Data Mapping
OrganizationUnit.Party_Id DimOrganizationUnit
= Party.Party_Id PartyAnchor_Ds FND_FCML_DB AnchorType Type_Ds
Dimension
AND Data
Party.PartyType_Id Mapping
= 42002000003 DimOrganizationUnit
-- AGENCY OrganizationalUnit_Nb FND_FCML_DB Party ExternalReference_Nb

Dimension Data Mapping DimOrganizationUnit State_Nb See Transformation Rule

LEFT OUTER JOIN FND_FCML_DB_TEST.MASTER_DATA State_Type
ON State_Tp = State_Type.PHYSICAL_TARGET_DOMAIN_VALUE
Dimension Data Mapping DimOrganizationUnit State_Tp FND_FCML_DB OrganizationUnit State_Tp
AND State_Type.PHYSICAL_TARGET_DOMAIN_NAME =
'FND_FCML_DB.ORGANIZATIONUNIT.STATE_TP'
-- Dimension
the addition of the filter below prevents duplicates,
Data Mapping
but may be too
DimOrganizationUnit State_Cd FND_FCML_DB MASTER_DATA See Transformation Rule
restricting
AND State_Type.PHYSICAL_SOURCE_DOMAIN_NAME =
'STG_CPAK_DB.CPAKV_GENERAL.PZW10100_X_AGENT_STATE_NUM'

LEFT OUTER JOIN RPT_FCML_DB_TEST.AnchorType

Dimension Data Mapping DimOrganizationUnit State_Ds FND_FCML_DB MASTER_DATA See Transformation Rule
ON Party.PartyType_Id = AnchorType.AnchorType_Id

INNER JOIN (
SELECT
Dimension Data Mapping DimOrganizationUnit AbbreviatedName_Cd FND_FCML_DB UnstructuredName Unstructured_Nm
Unstructured_Nm
FROM FND_FCML_DB.UnstructuredName
Dimension Data Mapping DimOrganizationUnit ShortName_Tt FND_FCML_DB UnstructuredName Unstructured_Nm
) AS UnstructuredName1
ON OrganizationUnit.Party_Id
Dimension = UnstructuredName1.Party_Id
Data Mapping DimOrganizationUnit LongName_Tt FND_FCML_DB UnstructuredName Unstructured_Nm
AND Usage_Tp = 6 -- Abbreviation
Dimension Data Mapping DimOrganizationUnit Effective_Dt FND_FCML_DB OrganizationUnit Effective_Dt
Dimension Data Mapping DimOrganizationUnit Expiration_Dt FND_FCML_DB OrganizationUnit Expiration_Dt
INNER JOIN (
Dimension
SELECT Data Mapping DimOrganizationUnit Transaction_Ts FND_FCML_DB OrganizationUnit Transaction_Ts
Unstructured_Nm
Dimension Data Mapping DimOrganizationUnit Revision_Ts FND_FCML_DB OrganizationUnit Revision_Ts
FROM FND_FCML_DB.UnstructuredName
) AS UnstructuredName2 Page 1 of 158
ON OrganizationUnit.Party_Id = UnstructuredName1.Party_Id
AND Usage_Tp = 7 -- Short name

INNER JOIN (
DimOrganizationUnit

Target Target Data Element Source Source Data Element

Source
Instance Target Instance Rule Type Target Table Name Name (column, field, Segment Name (column, field,
Database(s)
Identifier attribute) Code(s) attribute)

Dimension Data Mapping DimOrganizationUnit PopulationInfo_Id FND_FCML_DB PopulationInfo See transformation rule

Page 2 of 158
DimOrganizationUnit

Target
Form Additiona
Referenc
Target Target Rated/No l Last Last
Transformation Rule Target e or Additional
Transformation Rule 1 Form # Form # n- Exclusio Updated Updated Status ISSUE
2 Form # Lookup Comments
mapped tested Rated/Ma ns / By Date
Table
nually Validity
Rated

WHERE PartyType_Id in
For each targeted OrganizationUnit row, generate 1 (042002000003,
DimOrganizationUnit row. (For Agency, Appt Regional Office, 042002000014, DJL 1/29/2008
Appt Super Region and Appt Division only) 042002000015,
042002000016)

Move DJL 11/5/2007

Move DJL 11/5/2007
Move DJL 11/5/2007
See SQL Example for Join logic DJL 11/5/2007
Move DJL 11/20/2007

Default (empty string) CDW 11/16/2007

Move DJL 11/5/2007

find first occurance of following:

(LSD_Value)
Where PTD_Name = DJL 11/5/2007
FND_FCML_DB.ORGANIZATIONUNIT.STATE_TP AND
PTD_Value = State_Tp

find first occurance of following:

(DESC)
Where PTD_Name = DJL 11/5/2007
FND_FCML_DB.ORGANIZATIONUNIT.STATE_TP AND
PTD_Value = State_Tp

Move Where
CDW 11/9/2007
FND_FCML_DB.UNSTRUCTUREDNAME.USAGE_TP = 6
Move Where
CDW 11/9/2007
FND_FCML_DB.UNSTRUCTUREDNAME.USAGE_TP = 7
Move Where
CDW 11/9/2007
FND_FCML_DB.UNSTRUCTUREDNAME.USAGE_TP = 8
Move DJL 11/5/2007
Move DJL 11/5/2007
Move DJL 11/5/2007
Move DJL 11/5/2007

Page 3 of 158
DimOrganizationUnit

- Population Cycle Description

- Population Timestamp ( date and time of population cycle)
(Transaction_Ts)
RLJ 11/9/2007
- Source System Label (description of application or system
from which the information last used to update the entity
instance was populated).

Page 4 of 158
DimOrganizationUnit

Page 5 of 158
DimOrganizationUnit

Page 6 of 158
DimPostalAddress

The DimPostalAddress structure is defined in this tab. The SQL

used to create an example view appears below. The mapping
information is provided below the SQL.
Resource Status

Chad Webb Review

Target Source
Target Data Element Name Source Source Data Element Name (column, field,
Instance Target Instance Rule Type Target Table Name Segment
(column, field, attribute) Database(s) attribute)
Identifier Code(s)

Dimension Row Building Rule DimPostalAddress FND_FCML_DB PostalAddress See transformation rule

Dimension Data Mapping DimPostalAddress PostalAddress_Id FND_FCML_DB PostalAddress PostalAddress_Id

Dimension Data Mapping DimPostalAddress ContactPoint_Id FND_FCML_DB PostalAddress ContactPoint_Id
Dimension Data Mapping DimPostalAddress AddressLine1_Nm FND_FCML_DB PostalAddress AddressLine1_Nm
Dimension Data Mapping DimPostalAddress AddressLine2_Nm FND_FCML_DB PostalAddress AddressLine2_Nm
Dimension Data Mapping DimPostalAddress AddressLine3_Nm FND_FCML_DB PostalAddress AddressLine3_Nm
Dimension Data Mapping DimPostalAddress AddressLine4_Nm FND_FCML_DB PostalAddress AddressLine4_Nm
Dimension Data Mapping DimPostalAddress City_Nm FND_FCML_DB PostalAddress City_Nm
Dimension Data Mapping DimPostalAddress State_Tp FND_FCML_DB PostalAddress State_Tp

Dimension Data Mapping DimPostalAddress State_Cd FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimPostalAddress State_Ds FND_FCML_DB MASTER_DATA See Transformation Rule

Dimension Data Mapping DimPostalAddress Postal_Cd FND_FCML_DB PostalAddress Postal_Cd

Dimension Data Mapping DimPostalAddress ZipPlus4_Cd FND_FCML_DB PostalAddress ZipPlus4_Cd
Dimension Data Mapping DimPostalAddress Effective_Dt FND_FCML_DB PostalAddress Effective_Dt
Dimension Data Mapping DimPostalAddress Expiration_Dt FND_FCML_DB PostalAddress Expiration_Dt
Dimension Data Mapping DimPostalAddress Transaction_Ts FND_FCML_DB PostalAddress Transaction_Ts
Dimension Data Mapping DimPostalAddress Revision_Ts FND_FCML_DB PostalAddress Revision_Ts

Dimension Data Mapping DimPostalAddress PopulationInfo_Id FND_FCML_DB PopulationInfo See transformation rule

Page 1 of 158
DimPostalAddress

For each PostalAddress row, generate 1 PostalAddress data

DJL 1/29/2008
table row
Move DJL 1/29/2008
Move DJL 1/29/2008
Move DJL 1/29/2008
Move DJL 1/29/2008
Move DJL 1/29/2008
Move DJL 1/29/2008
Move DJL 1/29/2008
Move DJL 1/29/2008

find first occurance of following:

(LSD_Value)
Where PTD_Name = DJL 1/29/2008
FND_FCML_DB.POSTALADDRESS.STATE_TP AND
PTD_Value = State_Tp

find first occurance of following:

(DESC)
Where PTD_Name = DJL 1/29/2008
FND_FCML_DB.POSTALADDRESS.STATE_TP AND
PTD_Value = State_Tp

Move DJL 1/29/2008

Move DJL 1/29/2008
Move DJL 1/29/2008
Move DJL 1/29/2008
Move DJL 1/29/2008
Move DJL 1/29/2008

- Population Cycle Description

- Population Timestamp ( date and time of population cycle)
(Transaction_Ts)
DJL 1/29/2008
- Source System Label (description of application or system
from which the information last used to update the entity
instance was populated).

Page 2 of 158
DimPostalAddress

Page 3 of 158
FactMoPackageRetentionPrfl

The FactMonthlyPackageRetentionProfile table is based on Resource Status

See SQL example code the Transformation Rules 2 column Larry Jewell Review

Target
Target Data Element Name (column, field, Source Data Element Name
Instance Target Instance Rule Type Target Table Name Source Database(s) Source Segment Code(s)
attribute) (column, field, attribute)
Identifier

Fact Table Row Building Rule FactMoPackageRetentionPrfl FND_FCML_DB

Fact Table Data Mapping FactMoPackageRetentionPrfl CommercialPackage_Id FND_FCML_DB CommercialPackage CommercialPackage_Id

Page 1 of 158
FactMoPackageRetentionPrfl

Fact Table Data Mapping FactMoPackageRetentionPrfl Agency_Id FND_FCML_DB OrganizationUnit OrganizationUnit_Id

Page 2 of 158
FactMoPackageRetentionPrfl

Fact Table Data Mapping FactMoPackageRetentionPrfl InsuringCompany_Id FND_FCML_DB Company Company_Id

Page 3 of 158
FactMoPackageRetentionPrfl

Fact Table Data Mapping FactMoPackageRetentionPrfl InsuredCompany_Id FND_FCML_DB Company Company_Id

Page 4 of 158
FactMoPackageRetentionPrfl

Fact Table Data Mapping FactMoPackageRetentionPrfl InsuredName_Id FND_FCML_DB UnstructuredName UnstructuredName_Id

Page 5 of 158
FactMoPackageRetentionPrfl

Fact Table Data Mapping FactMoPackageRetentionPrfl DBAName_Id FND_FCML_DB UnstructuredName UnstructuredName_Id

Page 6 of 158
FactMoPackageRetentionPrfl

Fact Table Data Mapping FactMoPackageRetentionPrfl PostalAddress_Id FND_FCML_DB PostalAddress PostalAddress_Id

Page 7 of 158
FactMoPackageRetentionPrfl

Fact Table Data Mapping FactMoPackageRetentionPrfl MoneyScheduler_Id FND_FCML_DB MoneyScheduler MoneyScheduler_Id

Page 8 of 158
FactMoPackageRetentionPrfl

Fact Table Data Mapping FactMoPackageRetentionPrfl AgreementStatus_Id FND_FCML_DB AgreementStatus AgreementStatus_Id

Fact Table Data Mapping FactMoPackageRetentionPrfl Calendar_Dt ETL Processing Date

Page 9 of 158
FactMoPackageRetentionPrfl

Fact Table Data Mapping FactMoPackageRetentionPrfl StandardIndustryClass_Cd FND_FCML_DB BusinessActivity Industry_Tp

Fact Table Data Mapping FactMoPackageRetentionPrfl CurrentFullTermPrem_Am FND_FCML_DB MoneyProvisionElement Base_Am

Page 10 of 158
FactMoPackageRetentionPrfl

Fact Table Data Mapping FactMoPackageRetentionPrfl TwelveMoPriorFullTermPrem_Am FND_FCML_DB MoneyProvisionElement Base_Am

Fact Table Data Mapping FactMoPackageRetentionPrfl CurrentActive_Ct RPT_FCML_DB DimCommercialPackage See Transformation Rule

Page 11 of 158
FactMoPackageRetentionPrfl

Fact Table Data Mapping FactMoPackageRetentionPrfl TwelveMoPriorActive_Ct RPT_FCML_DB DimCommercialPackage See Transformation Rule

Page 12 of 158
FactMoPackageRetentionPrfl

Fact Table Data Mapping FactMoPackageRetentionPrfl EffectiveMoBasicRetained_Ct FND_FCML_DB CommercialPackage See Transformation Rule

Page 13 of 158
FactMoPackageRetentionPrfl

Fact Table Data Mapping FactMoPackageRetentionPrfl EffectiveYeartoDateBscRtn_Ct FND_FCML_DB CommercialPackage See Transformation Rule

Fact Table Data Mapping FactMoPackageRetentionPrfl ExpiringPremium_Am FND_FCML_DB MoneyProvisionElement Base_Am

Page 14 of 158
FactMoPackageRetentionPrfl

Fact Table Data Mapping FactMoPackageRetentionPrfl IssuedPremium_Am FND_FCML_DB MoneyProvisionElement Base_Am

Fact Table Data Mapping FactMoPackageRetentionPrfl Persistance_Ct FND_FCML_DB CommercialPackage OriginalPolicyEffective_Dt

Fact Table Data Mapping FactMoPackageRetentionPrfl UniqueLinesOnPackage_Ct FND_FCML_DB CommercialPackage See Transformation Rule

Fact Table Data Mapping FactMoPackageRetentionPrfl PopulationInfo_Id RPT_FCML_DB PopulationInfo See transformation rule

Page 15 of 158
FactMoPackageRetentionPrfl

Target Form
Reference Additional Last
Target Form Target Form Target Form Rated/Non- Last
Transformation Rule 1 Transformation Rule 2 or Lookup Exclusions / Updated Status ISSUE
# # mapped # tested Rated/Manu Updated By
Table Validity Date
ally Rated

For the most current CommercialPackage row, generate 1 Where ETL processing date is >= Transaction_Ts
EVG 11/8/2007
FactMoPackageRetentionPrfl row AND ETL processing date < Revision_Ts

SELECT DISTINCT
C.CommercialPackage_Id AS
CommercialPackage_Id,
C.PopulationInfo_Id AS PopulationInfo_Id

FROM (
SELECT CommercialPackage_Id,
Agreement_Id,
PopulationInfo_Id
FROM
Move FND_FCML_DB_TEST.CommercialPackage
WHERE Transaction_Ts > '2003-02-28
00:00:00.0000'
AND Transaction_Ts < '2003-04-01 00:00:00.0000'
) AS C --Package

Page 16 of 158
FactMoPackageRetentionPrfl

SELECT DISTINCT
C.CommercialPackage_Id AS
CommercialPackage_Id,
O.OrganizationUnit_Id AS Agency_Id,
C.PopulationInfo_Id AS PopulationInfo_Id

FROM (
SELECT CommercialPackage_Id,
Agreement_Id,
PopulationInfo_Id
FROM
FND_FCML_DB_TEST.CommercialPackage
WHERE Transaction_Ts > '2003-02-28
00:00:00.0000'
AND Transaction_Ts < '2003-04-01 00:00:00.0000'
) AS C --Package
INNER JOIN (
SELECT
Agreement_Id,
Party_Id,
PartyRole_Id
FROM
Move FND_FCML_DB_TEST.PartyRoleInAgreement --
Party Role In Agr for Agency
) AS N
ON C.Agreement_Id = N.Agreement_Id

INNER JOIN (
SELECT
Party_Id,
PartyType_Id,
ExternalReference_Nb
FROM FND_FCML_DB_TEST.Party -- Party for
Agency
WHERE PartyType_Id = 042002000003 -- Agency
(Legal entity)
AND LENGTH(ExternalReference_Nb) > 2 -- The
Agency Number not the State Number

) AS N2
ON N.Party_Id = N2.Party_Id

INNER JOIN (
SELECT
Party_Id
FROM FND_FCML_DB_TEST.Party -- Party for

Page 17 of 158
FactMoPackageRetentionPrfl

SELECT DISTINCT
C.CommercialPackage_Id AS
CommercialPackage_Id,
M.Company_Id AS InsuringCompany_Id,
C.PopulationInfo_Id AS PopulationInfo_Id

INNER JOIN ( -- MAX effective date? -- Party for

Nationwide Company
SELECT
Agreement_Id,
L.Party_Id AS L_Party_Id,
Move L2.Party_Id AS L2_Party_Id
FROM
FND_FCML_DB_TEST.PartyRoleInAgreement L
JOIN FND_FCML_DB_TEST.Party L2 -- Party
for Nationwide Company (Legal Entity)
ON L.Party_Id = L2.Party_Id
AND L2.PartyType_Id = 042002000009 --
Nationwide Company
) AS L
ON C.Agreement_Id = L.Agreement_Id

INNER JOIN (
SELECT
Company_Id,
Party_Id
FROM FND_FCML_DB_TEST.Company
WHERE Company_Tp = 3 -- Nationwide
Operating Company
AND CURRENT_DATE >= Effective_Dt
AND CURRENT_DATE < Expiration_Dt
) AS M
ON L.L2_Party_Id = M.Party_Id
;

Page 18 of 158
FactMoPackageRetentionPrfl

SELECT DISTINCT
C.CommercialPackage_Id AS
CommercialPackage_Id,
E3.Company_Id AS InsuredCompany_Id,
C.PopulationInfo_Id AS PopulationInfo_Id

INNER JOIN (
SELECT
Agreement_Id,
E.Party_Id AS E_Party_Id,
E2.Party_Id AS E2_Party_Id
Move FROM
FND_FCML_DB_TEST.PartyRoleInAgreement E --
Party Role in Agr for Insured Company
JOIN FND_FCML_DB_TEST.Party E2 -- Party
for Nationwide Company (Legal Entity)
ON E.Party_Id = E2.Party_Id
AND E2.PartyType_Id = 42002000007 --
Corporation (Insured)
) AS E
ON C.Agreement_Id = E.Agreement_Id
-- AND C.Effective_Dt = E.Effective_Dt

INNER JOIN (
SELECT
Company_Id,
Party_Id
FROM FND_FCML_DB_TEST.Company
WHERE CURRENT_DATE >= Effective_Dt
AND CURRENT_DATE < Expiration_Dt
) AS E3
ON E2_Party_Id = E3.Party_Id
-- AND C.Effective_Dt = E3.Effective_Dt

Page 19 of 158
FactMoPackageRetentionPrfl

SELECT DISTINCT
C.CommercialPackage_Id AS
CommercialPackage_Id,
E3.Company_Id AS InsuredCompany_Id,
F.UnstructuredName_Id AS InsuredName_Id,
C.PopulationInfo_Id AS PopulationInfo_Id

INNER JOIN (
SELECT
Agreement_Id,
E.Party_Id AS E_Party_Id,
Move E2.Party_Id AS E2_Party_Id
FROM
FND_FCML_DB_TEST.PartyRoleInAgreement E --
Party Role in Agr for Insured Company
JOIN FND_FCML_DB_TEST.Party E2 -- Party
for Nationwide Company (Legal Entity)
ON E.Party_Id = E2.Party_Id
AND E2.PartyType_Id = 42002000007 --
Corporation (Insured)
) AS E
ON C.Agreement_Id = E.Agreement_Id
-- AND C.Effective_Dt = E.Effective_Dt

Page 20 of 158
FactMoPackageRetentionPrfl

SELECT DISTINCT
C.CommercialPackage_Id AS
CommercialPackage_Id,
E3.Company_Id AS InsuredCompany_Id,
G.UnstructuredName_Id AS DBAName_Id,
C.PopulationInfo_Id AS PopulationInfo_Id

Page 21 of 158
FactMoPackageRetentionPrfl

SELECT DISTINCT
C.CommercialPackage_Id AS
CommercialPackage_Id,
K.PostalAddress_Id AS PostalAddress_Id,
C.PopulationInfo_Id AS PopulationInfo_Id

INNER JOIN (
SELECT
Agreement_Id,
Z.PartyRole_Id AS Z_PartyRole_Id,
Z2.Party_Id AS Z2_Party_Id,
Move Z2.PartyType_Id AS PartyType_Id,
Effective_Dt,
Expiration_Dt
FROM
FND_FCML_DB_TEST.PartyRoleInAgreement Z --
Party Role In Agr for Insured
JOIN FND_FCML_DB_TEST.Party Z2 -- Party for
Insured
ON Z.PartyRole_Id = Z2.Party_Id
AND Z2.PartyType_Id = 043003000001 -- Insured
WHERE CURRENT_DATE >= Effective_Dt
AND CURRENT_DATE < Expiration_Dt
) AS Z
ON C.Agreement_Id = Z.Agreement_Id

INNER JOIN (
SELECT
ContactPreference_Id,
Party_Id,
Effective_Dt,
Expiration_Dt
FROM FND_FCML_DB_TEST.ContactPreference
WHERE Purpose_Tp = 3 -- Mailing Address
purpose

Page 22 of 158
FactMoPackageRetentionPrfl

SELECT
C.CommercialPackage_Id AS
CommercialPackage_Id,
V.MoneyScheduler_Id AS MoneyScheduler_Id,
C.PopulationInfo_Id AS PopulationInfo_Id

INNER JOIN (
SELECT
MoneyScheduler_Id,
Agreement_Id,
Effective_Dt,
Move Expiration_Dt
FROM FND_FCML_DB_TEST.MoneyScheduler
WHERE MoneySchedulerType_Id =
061007000000 -- Money Scheduler
/*
AND CURRENT_DATE >= Effective_Dt
AND CURRENT_DATE < Expiration_Dt
*/
AND '2003-03-31' >= Effective_Dt
AND '2003-03-31' < Expiration_Dt
) AS V
ON C.Agreement_Id = V.Agreement_Id
;

Page 23 of 158
FactMoPackageRetentionPrfl

SELECT
C.CommercialPackage_Id,
AgreementStatus_Id,
C.PopulationInfo_Id

FROM (
SELECT
CommercialPackage_Id,
Agreement_Id as C_Agreement_Id,
PopulationInfo_Id
FROM
FND_FCML_DB_TEST.CommercialPackage
WHERE Transaction_Ts > '2003-02-28
00:00:00.0000'
AND Transaction_Ts < '2003-04-01 00:00:00.0000'
) AS C
INNER JOIN (
SELECT Agreement_Id AS A_Agreement_Id
FROM FND_FCML_DB_TEST.Agreement A
WHERE AgreementType_Id = 11003010000
) AS A
ON C.C_Agreement_Id = A.A_Agreement_Id
Move INNER JOIN (
SELECT
Agreement_Id AS S_Agreement_Id,
AgreementStatus_Id,
Status_Tp
FROM FND_FCML_DB_TEST.AgreementStatus
WHERE Effective_Dt <= Current_Date
AND Current_Date < Expiration_Dt
) AS S
ON A.A_Agreement_Id = S.S_Agreement_Id
INNER JOIN (
SELECT PHYSICAL_SOURCE_DOMAIN_VALUE
, PHYSICAL_TARGET_DOMAIN_VALUE
FROM FND_FCML_DB_TEST.MASTER_DATA
WHERE PHYSICAL_TARGET_DOMAIN_NAME =
'FND_FCML_DB.AGREEMENTSTATUS.STATUS_
TP'
GROUP BY
PHYSICAL_SOURCE_DOMAIN_VALUE
, PHYSICAL_TARGET_DOMAIN_VALUE
UNION
SELECT ' ' AS
PHYSICAL_SOURCE_DOMAIN_VALUE
, 0 AS PHYSICAL_TARGET_DOMAIN_VALUE
Max Business Day of ETL Processing Month

Page 24 of 158
FactMoPackageRetentionPrfl

SELECT DISTINCT
C.CommercialPackage_Id AS
CommercialPackage_Id,
Y.Industry_Cd AS Industry_Cd,
C.PopulationInfo_Id AS PopulationInfo_Id

INNER JOIN (
SELECT
Party_Id,
Activity_Id,
Effective_Dt,
Expiration_Dt
FROM
FND_FCML_DB_TEST.ActivityPartyRelationship
-- Activity Party Relationship
WHERE RelationshipNature_Tp = 050020001 --
Associated with
AND CURRENT_DATE >= Effective_Dt
AND CURRENT_DATE < Expiration_Dt
IF ((CommercialPackage.Agreement_Id is not NULL Find MoneyProvisionElement.Base_Am where type
AND PackageCancel.Agreement_Id is NULL) = Full Term Premium and the row is current for the
OR PackageFuture.Agreement_Id is not NULL) reporting date.
THEN MOVE Base_Am EJV 12/20/2007
ELSE '0.00'

Page 25 of 158
FactMoPackageRetentionPrfl

IF ((CommercialPackage.Agreement_Id is not NULL Find MoneyProvisionElement.Base_Am where type

AND PackageCancel.Agreement_Id is NULL) = Full Term Premium and the row was current for
OR PackageFuture.Agreement_Id is not NULL) the reporting date - 1 year.
THEN MOVE Base_Am EJV 12/20/2007
ELSE '0.00'

IF ((CommercialPackage.Agreement_Id is not NULL SELECT DISTINCT

AND PackageCancel.Agreement_Id is NULL) C.CommercialPackage_Id,
OR PackageFuture.Agreement_Id is not NULL) CASE WHEN ((CommercialPackage.Agreement_Id
THEN '1' is not NULL
ELSE '0' AND PackageCancel.Agreement_Id is NULL)
OR PackageFuture.Agreement_Id is not NULL)
THEN '1'
ELSE '0'
END as CurrentActive_Ct,
C.PopulationInfo_Id

-- Current Active Status Joins CDW 10/30/2007

LEFT OUTER JOIN (
SELECT
Agreement_Id,
Effective_Dt,
Expiration_Dt,
PolicyEffective_Dt,
PolicyExpiration_Dt
FROM
FND_FCML_DB_TEST.CommercialPackage
/*
WHERE CURRENT_DATE >= Effective_Dt
AND CURRENT_DATE < Expiration_Dt
AND CURRENT_DATE >= PolicyEffective_Dt
AND CURRENT_DATE < PolicyExpiration_Dt
*/
WHERE '2003-03-31' >= Effective_Dt
AND '2003-03-31' < Expiration_Dt
AND '2003-03-31' >= PolicyEffective_Dt
AND '2003-03-31' < PolicyExpiration_Dt

) AS CommercialPackage
ON C.Agreement_Id =
CommercialPackage.Agreement_Id

Page 26 of 158
FactMoPackageRetentionPrfl

IF ((PrioCommercialPackage.Agreement_Id is not NULL SELECT DISTINCT

AND PrioPackageCancel.Agreement_Id is NULL) C.CommercialPackage_Id,
OR PrioPackageFuture.Agreement_Id is not NULL) CASE WHEN
THEN '1' ((PrioCommercialPackage.Agreement_Id is not
ELSE '0' NULL
AND PrioPackageCancel.Agreement_Id is NULL)
OR PrioPackageFuture.Agreement_Id is not
NULL)
THEN '1'
ELSE '0'
END AS TwelveMoPriorActive_Ct,
C.PopulationInfo_Id

FROM (
SELECT CommercialPackage_Id,
Agreement_Id,
PopulationInfo_Id
FROM
FND_FCML_DB_TEST.CommercialPackage
WHERE Transaction_Ts > '2003-02-28
00:00:00.0000'
AND Transaction_Ts < '2003-04-01 00:00:00.0000'
) AS C --Package CDW 10/30/2007
-- TwelveMoPrior Active Status Joins
LEFT OUTER JOIN (
SELECT
Agreement_Id,
Effective_Dt,
Expiration_Dt,
PolicyEffective_Dt,
PolicyExpiration_Dt
FROM
FND_FCML_DB_TEST.CommercialPackage
/*
WHERE CURRENT_DATE - INTERVAL '1' YEAR
>= Effective_Dt
AND CURRENT_DATE - INTERVAL '1' YEAR <
Expiration_Dt
AND CURRENT_DATE - INTERVAL '1' YEAR >=
PolicyEffective_Dt
AND CURRENT_DATE - INTERVAL '1' YEAR <
PolicyExpiration_Dt
*/
WHERE '2002-03-31' >= Effective_Dt
AND '2002-03-31' < Expiration_Dt

Page 27 of 158
FactMoPackageRetentionPrfl

IF (MoCommercialPackage.Agreement_Id is not NULL) SELECT DISTINCT

THEN '1' C.CommercialPackage_Id,
ELSE '0' CASE WHEN
(MoCommercialPackage.Agreement_Id is not
NULL)
THEN '1'
ELSE '0'
END AS EffectiveMoBasicRetained_Ct,
C.PopulationInfo_Id

-- Effective Month Basic Retained Status Joins

INNER JOIN ( EJV 12/20/2007
SELECT
Calendar_Dt,
CalendarMonth_Nb,
CalendarYear_Nb,
AIAccountingYear_Nb
FROM
RPT_FCML_APPS_TEST.DimCalendarDate
) AS LastYrDate
-- ON CURRENT_DATE - INTERVAL '1' YEAR =
LastYrDate.Calendar_dt -- Find last years date
ON '2002-03-31' = LastYrDate.Calendar_dt -- Find
last years date

INNER JOIN (
SELECT
Calendar_Dt,
CalendarMonth_Nb,
CalendarMonthDay_Nb,
CalendarYear_Nb
FROM
RPT_FCML_APPS_TEST.DimCalendarDate
WHERE CalendarMonthDay_Nb = 1
) AS StartOfMonth -- Find last years month start

Page 28 of 158
FactMoPackageRetentionPrfl

IF (YrCommercialPackage.Agreement_Id is not NULL) SELECT DISTINCT

THEN '1' C.CommercialPackage_Id,
ELSE '0' CASE WHEN
(YrCommercialPackage.Agreement_Id is not NULL)
THEN '1'
ELSE '0'
END AS EffectiveYearToDateBscRtn_Ct,
C.PopulationInfo_Id

-- Effective YTD Basic Retained Status Joins

INNER JOIN (
SELECT EJV 12/20/2007
Calendar_Dt,
AIAccountingMonth_Nb,
CalendarMonthDay_Nb,
AIAccountingYear_Nb
FROM
RPT_FCML_APPS_TEST.DimCalendarDate
WHERE AIAccountingMonth_Nb = 1
AND CalendarMonthDay_Nb = 1
) AS StartOfYear -- Find last years start
ON LastYrDate.AIAccountingYear_Nb =
StartOfYear.AIAccountingYear_Nb

LEFT OUTER JOIN

FND_FCML_DB_TEST.CommercialPackage
YrCommercialPackage
ON YrCommercialPackage.CommercialPackage_Id
=(
SELECT MIN(CommercialPackage_Id)
FROM
FND_FCML_DB_TEST.CommercialPackage
TCPAK
WHERE C.Agreement_Id = TCPAK.Agreement_Id
AND StartOfYear.Calendar_dt <=
Move Find MoneyProvisionElement.Base_Am where type
= Full Term Premium and the row was current at
end of prior term. EJV 12/20/2007

Page 29 of 158
FactMoPackageRetentionPrfl

Move Find MoneyProvisionElement.Base_Am where type

= Full Term Premium and the row was current at
beginning of current term. (see comments)

EJV 12/20/2007

IF OrginalPolicyEffective_Dt is NULL
THEN 999
ELSE Calendar Date - OrginalPolicyEffective date in years CDW 11/20/2007

Count CommercialInsurancePolicy.PolicyPrefix_Cd
See SQL Example for Join Logic (tied to package and in the date range)

- Population Cycle Description

- Population Timestamp ( date and time of population cycle)
(Transaction_Ts)
CDW 11/2/2007
- Source System Label (description of application or system
from which the information last used to update the entity
instance was populated).

Page 30 of 158
FactMoPackageRetentionPrfl

EA Data
Element
Group EA Re- Comment
Additional EA Data EA Group EA Data Element Name EA Data EA Occurs
EA System ID Name EA Layout Name (Copy-book) defines Flag (EA va IS vs
Comments Element ID Level (column, field, attribute) Type Number
(table, IAA)
segment,
file, entity)

Page 31 of 158
FactMoPackageRetentionPrfl

Page 32 of 158
FactMoPackageRetentionPrfl

Page 33 of 158
FactMoPackageRetentionPrfl

Page 34 of 158
FactMoPackageRetentionPrfl

Page 35 of 158
FactMoPackageRetentionPrfl

Page 36 of 158
FactMoPackageRetentionPrfl

Page 37 of 158
FactMoPackageRetentionPrfl

Page 38 of 158
FactMoPackageRetentionPrfl

Page 39 of 158
FactMoPackageRetentionPrfl

See
MoneyView_P
ackage_20071
126.sql

Page 40 of 158
FactMoPackageRetentionPrfl

See
MoneyView_P
ackage_20071
126.sql

Page 41 of 158
FactMoPackageRetentionPrfl

Page 42 of 158
FactMoPackageRetentionPrfl

Page 43 of 158
FactMoPackageRetentionPrfl

See
MoneyView_P
ackage_20071
126.sql

Page 44 of 158
FactMoPackageRetentionPrfl

If multiple
rows before
the term begin,
take the latest.
If only rows
after term
begin, take the
earliest. See
MoneyView_P
ackage_20071
126.sql

changed back
to version 103
per Cheryl's
request

Page 45 of 158
FactMoManagedProductPrfl

The FactMoMngdPrdctPrfl table is based on information from

several tables in the Foundation Layer. The joins between the
tables can be numerous and complex. For this table the grain
is at a Focus Managed Product level. A starter SQL statement
appears in the cell below.
Resource Status
See SQL example code the Transformation Rules 2 column ? Review

Fact Table Row Building Rule FactMoMngdPrdctPrfl FND_FCML_DB

Fact Table Data Mapping FactMoMngdPrdctPrfl CommercialPackage_Id FND_FCML_DB CommercialPackage CommercialPackage_Id

Fact Table Data Mapping FactMoManagedProductPrfl InsuredCompany_Id FND_FCML_DB Company Company_Id

Fact Table Data Mapping FactMoManagedProductPrfl InsuredName_Id FND_FCML_DB UnstructuredName UnstructuredName_Id

Fact Table Data Mapping FactMoManagedProductPrfl DBAName_Id FND_FCML_DB UnstructuredName UnstructuredName_Id

Fact Table Data Mapping FactMoManagedProductPrfl Agency_Id FND_FCML_DB OrganizationUnit OrganizationUnit_Id

Fact Table Data Mapping FactMoManagedProductPrfl PostalAddress_Id FND_FCML_DB PostalAddress PostalAddress_Id

Fact Table Data Mapping FactMoManagedProductPrfl Calendar_Dt FND_FCML_DB ETL Processing Date
Fact Table Data Mapping FactMoManagedProductPrfl PackagePrefix_Cd FND_FCML_DB CommercialPackage PackagePrefix_Cd
Fact Table Data Mapping FactMoManagedProductPrfl ManagedProduct_Cd ADW_INFRASTRUCTURE DWP_SHR_DIM_PRDCT C_PKG_PLCY_PFX

Fact Table Data Mapping FactMoManagedProductPrfl ManagedProduct_Ds ADW_INFRASTRUCTURE DWP_SHR_DIM_PRDCT C_PLCY_PFX_GRP

Fact Table Data Mapping FactMoManagedProductPrfl PolicyIssuedBranch_Nb FND_FCML_DB CommercialPackage RegionalOffice_Cd
Fact Table Data Mapping FactMoManagedProductPrfl SevenDigitPolicy_Nb FND_FCML_DB CommercialPackage SevenDigitPAKPolicy_Nb
Fact Table Data Mapping FactMoManagedProductPrfl StandardIndustryClassification_Cd FND_FCML_DB BusinessActivity Industry_Tp

Fact Table Data Mapping FactMoManagedProductPrfl CurrentFullTermPrem_Am FND_FCML_DB MoneyProvisionElement Base_Am

Fact Table Data Mapping FactMoManagedProductPrfl CurrentActvPriorFullTrmPrm_Am FND_FCML_DB MoneyProvisionElement Base_Am

Fact Table Data Mapping FactMoManagedProductPrfl TwelveMoPriorFullTermPrem_Am FND_FCML_DB MoneyProvisionElement Base_Am

Fact Table Data Mapping FactMoManagedProductPrfl CurrentActive_Ct FND_FCML_DB CommercialPackage See Transformation Rule

Fact Table Data Mapping FactMoManagedProductPrfl TwelveMoPriorActive_Ct FND_FCML_DB CommercialPackage See Transformation Rule

Fact Table Data Mapping FactMoManagedProductPrfl EffectiveMoBasicRetained_Ct FND_FCML_DB CommercialPackage See Transformation Rule

Fact Table Data Mapping FactMoManagedProductPrfl EffectiveYearToDateBscRtn_Ct FND_FCML_DB CommercialPackage See Transformation Rule

Fact Table Data Mapping FactMoManagedProductPrfl ExpiringPremium_Am FND_FCML_DB MoneyProvisionElement Base_Am

Fact Table Data Mapping FactMoManagedProductPrfl IssuedPremium_Am FND_FCML_DB MoneyProvisionElement Base_Am

Page 1 of 158
FactMoManagedProductPrfl

Fact Table Data Mapping FactMoManagedProductPrfl PopulationInfo_Id FND_FCML_DB PopulationInfo See transformation rule

Page 2 of 158
FactMoManagedProductPrfl

Target Form
Reference Additional Last
Target Form Target Form Target Form Rated/Non- Last
Transformation Rule 1 Transformation Rule 2 or Lookup Exclusions / Updated Status
# # mapped # tested Rated/Manu Updated By
Table Validity Date
ally Rated

For each grouping of CommercialInsurancePolicy rows by

PackagePrefix_Cd, ManagedProduct_Cd, Where ETL processing date is >= Transaction_Ts AND ETL
PolicyIssuedBranch_Nb, and SevenDigitPolicy_Nb, generate 1 processing date < Revision_Ts
unique fact table row

Move SELECT DISTINCT

C.CommercialPackage_Id AS CommercialPackage_Id,
Move SELECT
A.CommercialInsurancePlcy_Id AS
A.CommercialPackage_Id
CommercialInsurancePlcy_Id, AS CommercialPackage_Id,
Move SELECT
A.CommercialInsurancePlcy_Id
C.Agreement_Id AS PkgAgreement_Id, AS
A.CommercialPackage_Id
CommercialInsurancePlcy_Id,
A.Agreement_Id AS PolAgreement_Id, AS CommercialPackage_Id,
Move SELECT
A.CommercialInsurancePlcy_Id
MIN(E3.Company_Id)
A.PopulationInfo_Id ASAS AS
InsuredCompany_Id,
PopulationInfo_Id
A.CommercialPackage_Id
--F.UnstructuredName_Id AS
CommercialInsurancePlcy_Id, ASCommercialPackage_Id,
InsuredName_Id,
Move SELECT
--G.UnstructuredName_Id
FROM ( DISTINCT
A.CommercialInsurancePlcy_Id
MIN(F.UnstructuredName_Id) AS ASAS InsuredName_Id,
DBAName_Id,
A.CommercialPackage_Id
CommercialInsurancePlcy_Id,
A.PopulationInfo_Id
SELECT AS CommercialPackage_Id,
AS PopulationInfo_Id
Move SELECT DISTINCT
A.CommercialInsurancePlcy_Id
MIN(G.UnstructuredName_Id)
CommercialInsurancePlcy_Id, ASDBAName_Id,
AS
C.CommercialPackage_Id
CommercialInsurancePlcy_Id,
A.PopulationInfo_Id
FROM (
Agreement_Id, AS CommercialPackage_Id,
AS PopulationInfo_Id
Max Business Day of ETL Processing Month K.PostalAddress_Id
O.OrganizationUnit_Id
SELECT
PopulationInfo_Id AS PostalAddress_Id,
AS Agency_Id,
C.PopulationInfo_Id
A.PopulationInfo_Id
FROM ASASPopulationInfo_Id
PopulationInfo_Id
(FND_FCML_DB_TEST.CommercialInsurancePolicy
CommercialPackage_Id,
FROM
Move SELECT
CommercialInsurancePlcy_Id,
WHERE Transaction_Ts > '2003-02-28 00:00:00.0000' -- ETL
FROM
SELECT
Processing (FND_CPAK_WORKDB_TEST.FactMoPolAgr
CommercialPackage_Id,
PkgAgreement_Id, Date A
Move
SELECT CommercialPackage_Id,
CommercialInsurancePlcy_Id,
PolAgreement_Id,
A.CommercialInsurancePlcy_Id,
AND Transaction_Ts < '2003-04-01 00:00:00.0000' -- ETL
Move INNER
SELECT
C_MNG_PRDCT
Processing Agreement_Id,
JOIN
PkgAgreement_Id,
PopulationInfo_Id (
Date AS ManagedProduct_Cd, CDW 11/7/2007
)PopulationInfo_Id
SELECT
PolAgreement_Id,
A.CommercialInsurancePlcy_Id,
FROM
AS A --FND_CPAK_WORKDB_TEST.FactMoPolAgr
T_MNG_PRDCT_DSC Policy AS ManagedProduct_Ds
Move )FROM
C_MNG_PRDCT AS A --FND_FCML_DB_TEST.CommercialPackage
Agreement_Id,
PopulationInfo_Id
Policy & AS ManagedProduct_Cd,
Package keys
WHERE
Party_Id,
T_MNG_PRDCT_DSC
FROM
INNER Transaction_Ts AS> ManagedProduct_Ds
'2003-02-28 00:00:00.0000'
FROMFND_FCML_DB.CommercialInsurancePolicy
FND_CPAK_WORKDB_TEST.FactMoPolAgr
JOIN ( A
Move
)INNER
JOIN AND Transaction_Ts
PartyRole_Id < '2003-04-01
AS AADW_INFRASTRUCTURE.DWP_SHR_DIM_PRDCT
SELECT --JOIN
Policy( & Package keys 00:00:00.0000'
Move SELECT
)ON
FROM AS
FROM C
SELECT DISTINCT
--Package
FND_FCML_DB_TEST.PartyRoleInAgreement
FND_FCML_DB.CommercialInsurancePolicy
A.PackagePrefix_Cd = C_PKG_PLCY_PFX
Agreement_Id, A --
C.CommercialPackage_Id
Party
INNER
JOIN
AND Role JOIN In (Agr for Agency AS CommercialPackage_Id,
ADW_INFRASTRUCTURE.DWP_SHR_DIM_PRDCT
Agreement_Id,
A.PolicyPrefix_Cd
RelatedAgreement_Id, = C_PLCY_PFX_GRP
Move Find
Y.Industry_Cd
;INNER
)SELECT
ON MoneyProvisionElement.Base_Am
N JOINAS
Z.PartyRole_Id
E.Party_Id AS
ASIndustry_Cd,
( E_Party_Id,
ASA.PackagePrefix_Cd = C_PKG_PLCY_PFX
Z_PartyRole_Id, where type = Full EJV 12/20/2007
RelationshipNature_Tp
Term
AND Premium
C.PopulationInfo_Id
SELECT
ON ASand
A.PkgAgreement_Id
Agreement_Id,
A.PolicyPrefix_Cd
Z2.Party_Id
E2.Party_Id the
AS row is current for the reporting date and
PopulationInfo_Id
==C_PLCY_PFX_GRP
Z2_Party_Id,
E2_Party_Id, N.Agreement_Id
See FROM FND_FCML_DB_TEST.AgreementRelationship
moneyView_MngdProduct_20071126.sql
Move policy
;Agreement_Id, is active for
Z.PartyRole_Id
Z2.PartyType_Id
Effective_Dt, ASthe
AS managed product.
Z_PartyRole_Id,
PartyType_Id, EJV 12/20/2007
WHERE RelationshipNature_Tp = 50010001 -- Associated
FROM
INNER
with Z2.Party_Id(JOIN (ASAS
Z.PartyRole_Id
Effective_Dt,
Expiration_Dt Z_PartyRole_Id,
Z2_Party_Id,
Move Find MoneyProvisionElement.Base_Am
SELECT
Z2.Party_Id
Z2.PartyType_Id CommercialPackage_Id,
AS Z2_Party_Id,
AS PartyType_Id, where type = Full EJV 12/20/2007
Expiration_Dt
FROM FND_FCML_DB_TEST.PartyRoleInAgreement
) AS BPremium and the row is current for the reporting date E -- and
Term Agreement_Id,
Z2.PartyType_Id
Party_Id,
Effective_Dt, ASInsured
PartyType_Id,
Party
SELECT FROM
ON Role FND_FCML_DB_TEST.PartyRoleInAgreement
in
A.Agreement_Id Agr for = Company
B.Agreement_Id Z --
Move the policy is
PopulationInfo_Id
Effective_Dt,
PartyType_Id,
Expiration_Dt active for the managed product.
Party INNER Role
A.PackagePrefix_Cd, JOINIn Agr
( for Insuring Company
INNER FROM
JOIN
SELECT FND_FCML_DB_TEST.CommercialPackage
Expiration_Dt
ExternalReference_Nb
FROM FND_FCML_DB_TEST.PartyRoleInAgreement
FND_FCML_DB_TEST.Party
JOIN ( Z2 -- Party for Z --
Move SELECT
C_MNG_PRDCT
WHERE AS ManagedProduct_Cd,
Transaction_Ts > '2003-02-28
Party FROM
Nationwide Role
Party_Id,
SELECT
A.PackagePrefix_Cd,
FND_FCML_DB_TEST.Party
In Agr for Insuring
Company
DISTINCT (Legal Company 00:00:00.0000'
FND_FCML_DB_TEST.PartyRoleInAgreement
Entity) -- Party for Agency Z --
--T_MNG_PRDCT_DSC
Party AND
WHERE Transaction_Ts
Role <
In Agr for=Insured
PartyType_Id AS ManagedProduct_Ds,
='2003-04-01
042002000003 00:00:00.0000'
-- Party
Agency
SELECT JOIN
ON FND_FCML_DB_TEST.Party
Z.PartyRole_Id
PartyType_Id
CommercialPackage_Id, Z2.Party_Id Z2 -- for(Legal
Move )C_MNG_PRDCT
A.RegionalOffice_Cd,
entity) AS
JOIN
Nationwide
AND
FROMC --Package AS ManagedProduct_Cd,
FND_FCML_DB_TEST.Party
Company (Legal
Z2.PartyType_Id
Agreement_Id = AS
FND_FCML_DB_TEST.Party Z2 -- Party
Entity) E2 -- --Insured
043003000001 for Insured
Party for
A.PackagePrefix_Cd,
--T_MNG_PRDCT_DSC
A.SevenDigitPolicy_Nb, ManagedProduct_Ds,
/* ON
AND
Nationwide
SELECT FROM Z.PartyRole_IdCompany = (Legal
Z2.Party_Id
LENGTH(ExternalReference_Nb) Entity)
FND_FCML_DB_TEST.CommercialPackage > 2 -- The Agency
Move C_MNG_PRDCT
A.RegionalOffice_Cd,
MAX(CASE WHEN AS((CommercialPolicy.Agreement_Id
ManagedProduct_Cd, is not
INNER
Number AND
WHERE JOIN
not (
the
Z2.PartyType_Id State
CURRENT_DATE
) AS E2 Transaction_TsAS Number
= 043003000001
>= Effective_Dt
> '2003-02-28 -- Insured
00:00:00.0000' -- ETL
A.PackagePrefix_Cd,
--T_MNG_PRDCT_DSC
A.SevenDigitPolicy_Nb,
NULL ManagedProduct_Ds,
Move )/*
Find SELECT
WHERE
AS
AND
ON
Processing N2
E.Party_IdCURRENT_DATE
CURRENT_DATE Date = <
E2.Party_Id
MoneyProvisionElement.Base_Am >= Effective_Dt
Expiration_Dt where type = Full
C_MNG_PRDCT
A.RegionalOffice_Cd,
MAX(CASE
AND WHEN AS(PriorCommercialPolicy.Agreement_Id
PolicyCancel.Agreement_IdManagedProduct_Cd,is NULL) is EJV 12/20/2007
*/
Term Agreement_Id,
AND
ON
WHERE
AND
AND CURRENT_DATE
N.Party_Id
Premium
Transaction_Ts = N2.Party_Id
CURRENT_DATE
E2.PartyType_Idand =
the< row< Expiration_Dt
>= Effective_Dt
42002000007
was
'2003-04-01current at--endCorporation
00:00:00.0000'of prior-- term.
ETL
--T_MNG_PRDCT_DSC
A.SevenDigitPolicy_Nb, AS ManagedProduct_Ds,
)not
Find ASNULL
OR
WHERE
(Insured)
Processing
PolicyFuture.Agreement_Id
E.Party_Id
AND ZCURRENT_DATE
A.RegionalOffice_Cd, AS E_Party_Id,
'2003-03-31'
Date >=
MoneyProvisionElement.Base_Am
is
<Effective_Dt
not
Expiration_Dt
NULL)
where type = Full
Move MAX(CASE
AND
THEN WHEN (MoCommercialPolicy.Agreement_Id
PriorPolicyCancel.Agreement_Id
'1' is NULL) is not EJV 12/20/2007
)E2.Party_Id
INNER
*/
Term ON
AND JOIN (ASand
AS C.Agreement_Id
'2003-03-31'
CPremium
E --Package
A.SevenDigitPolicy_Nb,
NULL)THEN
ELSE '1'
'0'
E2_Party_Id
<the= Z.Agreement_Id
Expiration_Dt
row was current at beginning of current
FROM
SELECT
WHERE
)MAX(CASE
term.AS Z(see FND_FCML_DB_TEST.PartyRoleInAgreement
'2003-03-31'
comments)
ON A.PkgAgreement_Id
B.RelatedAgreement_Id >= Effective_Dt
= E.Agreement_Id
= C.Agreement_ID E --
THEN
ELSE
END) '0'
as WHEN (YrCommercialPolicy.Agreement_Id
'1' CurrentActive_Ct is not
Party
INNER
;Party_Id
AND
ON
AND Role JOIN in (Agr for
'2003-03-31'
A.PkgAgreement_Id
'2003-03-31' <>=Insured Company
Expiration_Dt
= Z.Agreement_Id
E.Effective_Dt
NULL)
ELSE
END) '0'
AS TwelveMoPriorActive_Ct
)JOINAS ZFND_FCML_DB_TEST.Party
SELECT
FROM
AND
THEN
END) FND_FCML_DB_TEST.Party
'2003-03-31'
'1'
AS < E.Expiration_Dt
EffectiveMoBasicRetained_Ct
E2 ----Party
Partyfor
forAgency
FROM
Nationwide FND_FCML_DB.CommercialInsurancePolicy
Company
ContactPreference_Id,
WHERE PartyType_Id (Legal Entity)
= =043003000012 A
-- Writing Agency
FROMON A.PkgAgreement_Id
INNER
ELSE '0'JOIN ( Z.Agreement_Id
FND_FCML_DB.CommercialInsurancePolicy A
JOINON ADW_INFRASTRUCTURE.DWP_SHR_DIM_PRDCT
E.Party_Id
FROM
JOIN
ON
Party_Id,
(Role)
SELECT
INNER
END) AS JOIN ( = E2.Party_Id
EffectiveYearToDateBscRtn_Ct
FND_FCML_DB.CommercialInsurancePolicy
ADW_INFRASTRUCTURE.DWP_SHR_DIM_PRDCT
A.PackagePrefix_Cd = C_PKG_PLCY_PFX A
AND
AS E2.PartyType_Id
Effective_Dt,
)INNER N3 JOIN
UnstructuredName_Id,
SELECT ( = 42002000007 -- Corporation
JOIN
ON
AND ADW_INFRASTRUCTURE.DWP_SHR_DIM_PRDCT
A.PackagePrefix_Cd
A.PolicyPrefix_Cd ==C_PLCY_PFX_GRP
C_PKG_PLCY_PFX
(Insured)
Expiration_Dt
ON N.PartyRole_Id
SELECT
Party_Id,
MIN(Company_Id) =
AS N3.Party_Id
Company_Id,
FROM FND_FCML_DB.CommercialInsurancePolicy
ON A.PackagePrefix_Cd
AND ==C_PLCY_PFX_GRP
C_PKG_PLCY_PFX A Page 3 of 158
AS EA.PolicyPrefix_Cd
)JOIN
FROM FND_FCML_DB_TEST.ContactPreference
UnstructuredName_Id,
--Effective_Dt,
Party_Id
AND
ON
WHERE
INNER
ADW_INFRASTRUCTURE.DWP_SHR_DIM_PRDCT
A.PolicyPrefix_Cd
Current Active
C.Agreement_Id
Purpose_Tp
JOIN ( Status==
= C_PLCY_PFX_GRP
Joins
E.Agreement_Id
3 -- Mailing Address purpose
ON
--Party_Id,
Expiration_Dt
FROM FND_FCML_DB_TEST.Company
A.PackagePrefix_Cd
TwelveMoPrior Active = C_PKG_PLCY_PFX
Status Joins
LEFT OUTER
ANDA.PolicyPrefix_Cd
Usage_Tp JOIN
=3 ( -- C_PLCY_PFX_GRP
Default address usage
/*SELECT
--Effective_Dt,
FROM
AND
LEFTEffective
OUTERFND_FCML_DB_TEST.UnstructuredName
Month Basic =Retained Status Joins
SELECT
INNER
AND JOIN
CURRENT_DATE
Party_Id,
Expiration_Dt
WHERE ( JOIN (= 4 >=--Effective_Dt
Usage_Tp
CURRENT_DATE >=Default Name
Effective_Dt
INNER
SELECT JOIN
Agreement_Id, (
--SELECT
AND
AND CURRENT_DATE
MIN(OrganizationUnit_Id)
FROM
/*SELECTCURRENT_DATE
Effective YTD Basic <
< Expiration_Dt
AS OrganizationUnit_Id
FND_FCML_DB_TEST.UnstructuredName
Expiration_Dt
Retained Status Joins
Agreement_Id,
Effective_Dt,
)Party_Id,
AS H FND_FCML_DB_TEST.OrganizationUnit
FROM
FactMoManagedProductPrfl

- Population Cycle Description

Page 4 of 158
FactMoManagedProductPrfl

EA Data
Element
Group EA Re- Comment
Additional EA Data EA Group EA Data Element Name EA Data EA Occurs
ISSUE EA System ID Name EA Layout Name (Copy-book) defines Flag (EA va IS vs
Comments Element ID Level (column, field, attribute) Type Number
(table, IAA)
segment,
file, entity)

See
MoneyView_P
ackage_20071
See
126.sql
MoneyView_P
ackage_20071
126.sql

See
MoneyView_P
If multiple
ackage_20071
rows
126.sqlbefore
the term begin,
take the latest.
If only rows
after term
begin, take the
earliest. See
MoneyView_P Page 5 of 158
ackage_20071
126.sql
FactMoManagedProductPrfl

Page 6 of 158
FactMoPolicyRetentionPrfl

The FactMonthlyPolicyRetentionProfile table is based on

information from several tables in the Foundation Layer. The
joins between the tables can be numerous and complex. The
"grain" of the fact table is generally determined by a specific
"driver" table. In this project the grain is at a policy level. The
appropriate driver table will be CommercialInsurancePolicy table
(for a given time frame.) A starter SQL statement appears in the
cell below.
Resource Status
See SQL example code the Transformation Rules 2 column Evan VanGelder Review

Fact Table Row Building Rule FactMoPolicyRetentionPrfl FND_FCML_DB

Fact Table Data Mapping FactMoPolicyRetentionPrfl CommercialPackage_Id FND_FCML_DB CommercialPackage CommercialPackage_Id

Fact Table Data Mapping FactMoPolicyRetentionPrfl CommercialInsurancePlcy_Id FND_FCML_DB CommercialInsurancePolicy CommercialInsurancePlcy_Id
Fact Table Data Mapping FactMoPolicyRetentionPrfl InsuringCompany_Id FND_FCML_DB Company Company_Id

Fact Table Data Mapping FactMoPolicyRetentionPrfl InsuredCompany_Id FND_FCML_DB Company Company_Id

Fact Table Data Mapping FactMoPolicyRetentionPrfl InsuredName_Id FND_FCML_DB UnstructuredName UnstructuredName_Id

Fact Table Data Mapping FactMoPolicyRetentionPrfl DBAName_Id FND_FCML_DB UnstructuredName UnstructuredName_Id

Fact Table Data Mapping FactMoPolicyRetentionPrfl Agency_Id FND_FCML_DB OrganizationUnit OrganizationUnit_Id

Fact Table Data Mapping FactMoPolicyRetentionPrfl PostalAddress_Id FND_FCML_DB PostalAddress PostalAddress_Id

Fact Table Data Mapping FactMoPolicyRetentionPrfl MoneyScheduler_Id FND_FCML_DB MoneyScheduler MoneyScheduler_Id

Fact Table Data Mapping FactMoPolicyRetentionPrfl AgreementStatus_Id FND_FCML_DB AgreementStatus AgreementStatus_Id

Fact Table Data Mapping FactMoPolicyRetentionPrfl Calendar_Dt ETL Processing Date
Fact Table Data Mapping FactMoPolicyRetentionPrfl StandardIndustryClassification_Cd FND_FCML_DB BusinessActivity Industry_Tp

Fact Table Data Mapping FactMoPolicyRetentionPrfl CurrentFullTermPremium_Am FND_FCML_DB MoneyProvisionElement Base_Am

Fact Table Data Mapping FactMoPolicyRetentionPrfl TwelveMoPriorFullTermPrem_Am FND_FCML_DB MoneyProvisionElement Base_Am

Fact Table Data Mapping FactMoPolicyRetentionPrfl CurrentActive_Ct FND_FCML_DB CommercialPackage See Transformation Rule

Fact Table Data Mapping FactMoPolicyRetentionPrfl TwelveMoPriorActive_Ct FND_FCML_DB CommercialPackage See Transformation Rule

Fact Table Data Mapping FactMoPolicyRetentionPrfl EffectiveMoBasicRetained_Ct FND_FCML_DB CommercialPackage See Transformation Rule

Fact Table Data Mapping FactMoPolicyRetentionPrfl EffectiveYearToDateBscRtn_Ct FND_FCML_DB CommercialPackage See Transformation Rule

Fact Table Data Mapping FactMoPolicyRetentionPrfl ExpiringPremium_Am FND_FCML_DB MoneyProvisionElement Base_Am

Fact Table Data Mapping FactMoPolicyRetentionPrfl IssuedPremium_Am FND_FCML_DB MoneyProvisionElement Base_Am

Page 1 of 158
FactMoPolicyRetentionPrfl

Fact Table Data Mapping FactMoPolicyRetentionPrfl PopulationInfo_Id RPT_FCML_DB PopulationInfo See transformation rule

Page 2 of 158
FactMoPolicyRetentionPrfl

For the most current CommercialInsurancePolicy row for each Where ETL processing date is >= Transaction_Ts
EVG 11/8/2007
policy, generate 1 FactMoPolicyRetentionPrfl row AND ETL processing date < Revision_Ts

Move SELECT DISTINCT

C.CommercialPackage_Id AS
Move CommercialPackage_Id,
A.CommercialInsurancePlcy_Id
SELECT DISTINCT AS
Move CommercialInsurancePlcy_Id,
A.CommercialPackage_Id AS
C.Agreement_Id
SELECT
CommercialPackage_Id, AS PkgAgreement_Id,
Move
A.Agreement_Id AS PolAgreement_Id,
A.CommercialPackage_Id
A.CommercialInsurancePlcy_Id AS AS
A.PopulationInfo_Id AS PopulationInfo_Id
SELECT
CommercialPackage_Id,
CommercialInsurancePlcy_Id,
Move
A.CommercialPackage_Id
A.CommercialInsurancePlcy_Id
M.Company_Id AS AS
AS InsuringCompany_Id,
Move FROM
SELECT
CommercialPackage_Id, (
CommercialInsurancePlcy_Id,
A.PopulationInfo_Id AS PopulationInfo_Id
SELECT
A.CommercialPackage_Id
A.CommercialInsurancePlcy_Id
MIN(E3.Company_Id) ASAS AS
InsuredCompany_Id,
Move SELECT CommercialInsurancePlcy_Id,
CommercialPackage_Id, ( DISTINCT AS PopulationInfo_Id
CommercialInsurancePlcy_Id,
A.PopulationInfo_Id
FROM
Agreement_Id,
A.CommercialPackage_Id
A.CommercialInsurancePlcy_Id
MIN(F.UnstructuredName_Id)
SELECT AS AS AS
Move SELECT ( DISTINCT
PopulationInfo_Id
CommercialPackage_Id,
CommercialInsurancePlcy_Id,
InsuredName_Id,
FROM CommercialPackage_Id,
C.CommercialPackage_Id
FROM
A.CommercialInsurancePlcy_Id
MIN(F.UnstructuredName_Id)
MIN(G.UnstructuredName_Id)
SELECT AS
CommercialInsurancePlcy_Id, ASDBAName_Id, AS
AS
Move SELECT
CommercialPackage_Id,
FND_FCML_DB_TEST.CommercialInsurancePolicy
CommercialInsurancePlcy_Id,
InsuredName_Id,
A.PopulationInfo_Id
PkgAgreement_Id, AS PopulationInfo_Id
CommercialPackage_Id,
A.CommercialPackage_Id
K.PostalAddress_Id
WHERE
O.OrganizationUnit_Id
MIN(G.UnstructuredName_Id) Transaction_Ts
CommercialInsurancePlcy_Id, AS > AS
AS PostalAddress_Id,
'2003-02-28
Agency_Id,
AS DBAName_Id,
SELECT PolAgreement_Id,
Move CommercialPackage_Id,
C.PopulationInfo_Id
00:00:00.0000'
A.PopulationInfo_Id
FROM (
PkgAgreement_Id, ASASPopulationInfo_Id
PopulationInfo_Id
PopulationInfo_Id
C.CommercialPackage_Id AS
A.CommercialInsurancePlcy_Id
AND
SELECT Transaction_Ts
PolAgreement_Id, < '2003-04-01 AS 00:00:00.0000'
Max Business Day of ETL Processing Month FROM
CommercialPackage_Id, EJV 11/8/2007
CommercialnsurancePlcy_Id,
FROM ( Policy
)FND_CPAK_WORKDB_TEST.FactMoPolAgr
AS A --
CommercialPackage_Id,
PopulationInfo_Id
SELECT A.CommercialInsurancePlcy_Id
V.MoneyScheduler_Id
SELECT CommercialPackage_Id,
FND_CPAK_WORKDB_TEST.FactMoPolAgr
DISTINCT
CommercialInsurancePlcy_Id, AS
AS MoneyScheduler_Id, A
Move FROM
)CommercialInsurancePlcy_Id,
AS A -- Policy & Package keys
CurrentPrem.Base_Am
INNER Agreement_Id,
JOIN (
CommercialPackage_Id,
C.CommercialPackage_Id
PkgAgreement_Id,
FND_CPAK_WORKDB_TEST.FactMoPolAgr AS AS
IF ((CommercialPolicy.Agreement_Id is not NULL INNER
Find AgreementStatus_Id
CurrentFullTermPrem_Am,
PopulationInfo_Id
SELECT JOIN ( AS
MoneyProvisionElement.Base_Am
CommercialInsurancePlcy_Id,
CommercialPackage_Id,
PolAgreement_Id, AgreementStatus_Id,
where type
)INNER AS A --JOIN Policy( --& MAX
Package keysdate? -- Party for
effective EJV 12/20/2007
AND PolicyCancel.Agreement_Id is NULL) = A.PopulationInfo_Id
TwelveMoPrior.Base_Am
FROM
Agreement_Id,
SELECT
Full Term Company
PkgAgreement_Id,
Y.Industry_Cd
PopulationInfo_IdPremium AS the
and
AS Industry_Cd, PopulationInfo_Id
ASrow is current for the
Nationwide
IF
OR((CommercialPolicy.Agreement_Id
PolicyFuture.Agreement_Id is notisNULL)
not NULL TwelveMoPriorFullTermPrem_Am,
FND_FCML_DB_TEST.CommercialPackage
Find
reporting RelatedAgreement_Id,
MoneyProvisionElement.Base_Am
Agreement_Id,
C.PopulationInfo_Id
INNER FROM date.(
PolAgreement_Id,
JOIN AS PopulationInfo_Idwhere type EJV 12/20/2007
FROM SELECT ( Transaction_Ts
AND
THENPolicyCancel.Agreement_Id
MOVE Base_Am is NULL) Expiring.Base_Am
= WHERE
Full Term
Party_Id,
PopulationInfo_IdPremiumAS
RelationshipNature_Tp
FND_CPAK_WORKDB_TEST.FactMoPolAgr
SELECT andExpiringPremium_Am,
>the
'2003-02-28
row is current for the
Agreement_Id,
SELECT
IF
OR((CommercialPolicy.Agreement_Id
PolicyFuture.Agreement_Id
ELSE '0' is notis not NULL
NULL) Issued.Base_Am
SELECT
00:00:00.0000'
reporting
FROM
)L.Party_IdFROM
PartyRole_Id
AS A -- date.
( Policy
Agreement_Id, AS
& Package IssuedPremium_Am,
keys
AS L_Party_Id,
CommercialInsurancePlcy_Id,
AND
THENPolicyCancel.Agreement_Id
MOVE Base_Am is NULL) A.PopulationInfo_Id
A.CommercialPackage_Id,
AND Transaction_Ts
FND_FCML_DB_TEST.AgreementRelationship
FROM
FND_CPAK_WORKDB_TEST.FactMoPolAgr
SELECT
E.Party_Id AS
< PopulationInfo_Id
'2003-04-01
CommercialPackage_Id, 00:00:00.0000'
L2.Party_IdAS ASE_Party_Id,
L2_Party_Id
IF
OR((PriorCommercialPolicy.Agreement_Id
ELSEPolicyFuture.Agreement_Id
'0' is not NULL) is not NULL SELECT
INNER Agreement_Id,
A.CommercialInsurancePlcy_Id,
)FND_FCML_DB_TEST.PartyRoleInAgreement
AS C
WHERE A -- --Package
Agreement_Id, RelationshipNature_Tp
Policy
JOIN Package keys = 50010001 -- --
(AS&E2_Party_Id,
E2.Party_Id
FROM
AND
THENPriorPolicyCancel.Agreement_Id
'1' is NULL) FROM
CASE PopulationInfo_Id
A.CommercialPackage_Id,
( In
WHEN ((CommercialPolicy.Agreement_Id
IF (MoCommercialPolicy.Agreement_Id is not NULL)
Associated
Party
SELECT
SELECT Role
PopulationInfo_Id
Effective_Dt, with
Agr for Agency
FND_FCML_DB_TEST.PartyRoleInAgreement L is
OR
ELSEPriorPolicyFuture.Agreement_Id
'0' is not NULL) )FROM
A.CommercialInsurancePlcy_Id,
not
INNER SELECT
ASNULL
FROM JOIN (
BFND_FCML_DB_TEST.Party
N
Agreement_Id,
Expiration_Dt
JOIN L2 -- Party for
THEN '1' A.CommercialPackage_Id,
FND_FCML_DB_TEST.CommercialInsurancePolicy
CASE SELECT
ON WHEN
CommercialPackage_Id,
AND PolicyCancel.Agreement_Id
A.Agreement_Id
A.PkgAgreement_Id
FND_FCML_DB_TEST.CommercialPackage
Z.PartyRole_Id = B.Agreement_Id
= is NULL)
N.Agreement_Id
AS Z_PartyRole_Id,
IF (YrCommercialPolicy.Agreement_Id is not NULL) Nationwide
SELECT FROM Company (Legal Entity)
ELSE '0' A.CommercialInsurancePlcy_Id,
WHERE Transaction_Ts
((PriorCommercialPolicy.Agreement_Id
CommercialInsurancePlcy_Id,
OR > '2003-02-28
'2003-02-28 is not NULL
THEN '1' ON PolicyFuture.Agreement_Id
Agreement_Id,
WHERE
Z2.Party_Id Transaction_Ts
AS= Z2_Party_Id,
FND_FCML_DB_TEST.PartyRoleInAgreement
L.Party_Id
A.CommercialPackage_Id,
CASE WHEN L2.Party_Id > is not NULL)
(MoCommercialPolicy.Agreement_Id
E --
00:00:00.0000'
AND PriorPolicyCancel.Agreement_Id
ELSE '0' Move
INNER
Party
Find
PkgAgreement_Id,
THEN
00:00:00.0000'
AND Role'1'
Z.PartyRole_Id
JOIN
Z2.PartyType_Id in (Agr ASAS
forZ_PartyRole_Id,
Insured
L2.PartyType_Id = 042002000009
MoneyProvisionElement.Base_Am
A.CommercialInsurancePlcy_Id, Company iswhere
PartyType_Id, NULL)
-- type
is AND
OR not NULL)
Transaction_Ts
PolAgreement_Id,
ELSETransaction_Ts
SELECT
AND '0'JOIN
Z2.Party_Id
Effective_Dt, DISTINCT
AS < '2003-04-01
PriorPolicyFuture.Agreement_Id
Z2_Party_Id,
< '2003-04-01 00:00:00.0000'
is not NULL)
00:00:00.0000' EJV 12/20/2007
Nationwide
=
CASE INNER
FullAas
Term
WHEN (
Company
Premium and the row was current at is
(YrCommercialPolicy.Agreement_Id
THEN
))Z2.PartyType_Id
AS
END '1'CurrentActive_Ct,
-- Policy
PopulationInfo_Id
CommercialPackage_Id,
Party_Id,
ASof C --Package
Expiration_Dt AS PartyType_Id,
SELECT
AND '2003-03-31'
Move Find
end
not ELSE
FROMNULL)
A.PopulationInfo_Id'0' term. >= L.Effective_Dt
MoneyProvisionElement.Base_Am
prior
Agreement_Id
PartyType_Id,
Effective_Dt,
FROM
where type EJV 12/20/2007
= Party_Id,
AND
Full
THEN '2003-03-31'
Term
'1' Premium < L.Expiration_Dt
and the row was current at
INNER
INNER END AS JOIN
Expiration_Dt
FROM EffectiveMoBasicRetained_Ct,
ExternalReference_Nb
JOIN (
TwelveMoPriorActive_Ct,
FND_CPAK_WORKDB_TEST.FactMoPolAgr
(
FND_FCML_DB_TEST.PartyRoleInAgreement Z --
)ELSE
beginning PartyType_Id
AS L '0' of current term. (see comments)
A.PopulationInfo_Id
FROM
Party SELECT
)FND_FCML_DB_TEST.CommercialPackage
AS ARole
FROM
SELECT -- Policy & for
Package
(FND_FCML_DB_TEST.Party
In Agr Insuring keys
CompanyE2-- Party for
FROM
ON
END FND_FCML_DB_TEST.Party
A.PkgAgreement_Id
AS = L.Agreement_Id
EffectiveYearToDateBscRtn_Ct, -- Party
Agency Agreement_Id,
SELECT
FND_FCML_DB_TEST.PartyRoleInAgreement
WHERE
Agreement_Id,
JOIN Transaction_Ts
FND_FCML_DB_TEST.Party > '2003-02-28 Z2 -- Party Z --
for
for Nationwide
A.PopulationInfo_Id Company (Legal Entity)
FROM
INNER
Party
00:00:00.0000'
WHERE Role
E.Party_Id (
RelatedAgreement_Id,
JOIN In (
CommercialPackage_Id,
Agr for
PartyType_Id Insured
=
Insuring042002000003
Company -- Agency
INNER ) AS E2JOINAS
Nationwide ( E_Party_Id,
Company (Legal Entity)
(Legal SELECT
RelationshipNature_Tp
CommercialInsurancePlcy_Id,
JOIN
AND FND_FCML_DB_TEST.Party
Transaction_Ts
entity)
E2.Party_Id AS < '2003-04-01
E2_Party_Id Z2 -- Party
00:00:00.0000'
-- Party for
for
ON
ONZ.PartyRole_Id
SELECT E.Party_Id = Z2.Party_Id
= E2.Party_Id
FROM
Insured
)FROM FROM (
CommercialPackage_Id,
MoneyScheduler_Id,
PolAgreement_Id,
AS CLENGTH(ExternalReference_Nb)
AND --Package > ----
2 -- The
Nationwide
AND Company
Z2.PartyType_Id
E2.PartyType_Id
MIN(Company_Id) AS(Legal
= Entity)
=043003000001
42002000007
Company_Id, Insured
Agency SELECT
FND_FCML_DB_TEST.AgreementRelationship
CommercialInsurancePlcy_Id,
Agreement_Id,
PopulationInfo_Id
ON Z.PartyRole_Id
Number not =the
B.RelatedAgreement_Id Z2.Party_Id
State=Number
C.Agreement_IDE --
FND_FCML_DB_TEST.PartyRoleInAgreement
/*
Corporation
Party_Id (Insured)
CommercialPackage_Id,
PolAgreement_Id,
WHERE
Effective_Dt, RelationshipNature_Tp = 50010001 --
))FROM
;Party AND
AS E
WHERE
AS
FROM N2Z2.PartyType_Id
Role = 043003000001
in Agr for Insured
CURRENT_DATE
FND_FCML_DB_TEST.Company
CommercialInsurancePlcy_Id,
Company
>= Effective_Dt -- Insured
PopulationInfo_Id
Associated
Expiration_Dt
ON with
FND_CPAK_WORKDB_TEST.FactMoPolAgr
WHERE CURRENT_DATE
N.Party_Id = N2.Party_Id >= Effective_Dt
/* JOIN
AND
ON
WHERE FND_FCML_DB_TEST.Party
CURRENT_DATE
A.PkgAgreement_Id
Company_Tp
PolAgreement_Id,
FROM BCURRENT_DATE = <=3Expiration_Dt E2 -- Party for
E.Agreement_Id
-- Nationwide Page 3 of 158
)Nationwide
*/ AS A
AND
WHERE
AND
Operating --FND_FCML_DB_TEST.MoneyScheduler
Policy & Package
CURRENT_DATE
Company
'2003-03-31'
Company >=(Legal keys
< Expiration_Dt
>=
E.Effective_DtEffective_Dt
Entity)
)INNERPopulationInfo_Id
FND_CPAK_WORKDB_TEST.FactMoPolAgr
ON
AS A.Agreement_Id
WHERE
AND ZCURRENT_DATE
JOIN =>=
( = E2.Party_IdB.Agreement_Id
MoneySchedulerType_Id <Effective_Dt = 061007000000
Expiration_Dt
/* ON
WHERE
AND E.Party_Id
'2003-03-31'
'2003-03-31' < E.Expiration_Dt
)-- FROM
AS
--
ON A
Money
Current
SELECT
*/AND -- Policy &
Scheduler
Active
C.Agreement_Id Package
Status== Joins keys
Z.Agreement_Id
AND E2.PartyType_Id
'2003-03-31' < Expiration_Dt
CURRENT_DATE 42002000007
>= Effective_Dt --
FND_CPAK_WORKDB_TEST.FactMoPolAgr
INNER
/*
LEFT
Party_Id JOIN
OUTER ( JOIN (
WHERE
Corporation
)INNER '2003-03-31'
AS ZCURRENT_DATE (Insured) >=<Effective_Dt
)AND
-- AS
AND A --JOIN
Effective Policy
TwelveMoPrior
( & Package
Month
DISTINCT Basic
Active
Expiration_Dt
keys
Retained
Status Status--Joins
Joins
)INNER
FROM
AND
AS
ON
SELECTECURRENT_DATE
*/SELECT JOIN
'2003-03-31'(
FND_FCML_DB_TEST.Party
A.PkgAgreement_Id >=
< Expiration_DtEffective_Dt
= Z.Agreement_Id Party for
FactMoPolicyRetentionPrfl

- Population Cycle Description

Page 4 of 158
FactMoPolicyRetentionPrfl

See
See
MoneyView_P
MoneyView_P
ackage_20071
ackage_20071
126.sql
126.sql

See
MoneyView_P
If multiple
ackage_20071
rows
126.sqlbefore
the term begin,
take the latest.
If only rows
after term
begin, take the
earliest. See
MoneyView_P
ackage_20071
126.sql Page 5 of 158
FactMoPolicyRetentionPrfl

Page 6 of 158
InternalOrganizationHrchy

The InternalOrganizationHrchy structure is

defined in this tab. The SQL used to create an
example view appears below. The mapping
information is provided below the SQL.

Resource Status
--CREATE VIEW
RPT_FCML_DB_TEST.InternalOrganizationHrchy AS
-- Division Level (records for all three levels)
SELECT
PartyRelationship_Id AS
InternalOrganizationHrchy_Id, Dale Lindquist Review
RelationshipNature_Tp,
Target Target Target Data Element
AnchorType.Type_Ds AS RelationshipNature_Ds, Source Source Segment Source Data Element Name
Instance Instance Rule Target Table Name Name (column, field, Transformation Rule 1
Parent.OrganizationUnit_Id AS Database(s) Code(s) (column, field, attribute)
Identifier Type attribute)
ParentOrganizationUnit_Id,
Child.OrganizationUnit_Id AS
For each Appt Division row as represented in the
ChildOrganizationUnit_Id, RelationshipNature_Tp of the TARGET, build one Division level
CASE WHEN RelationshipNature_Tp = 040010103 -- row.
Appt Agency for
Relationship RowRegion
Building Rule InternalOrganizationHrchy FND_FCML_DB PartyRelationship The block below is for the 110010101 (Appointment Division
THEN 'Y' -- TRUE Hierarchy) level. Include
PartyRelationship.RelationshipNature_Tp values:
ELSE 'N' -- FALSE 04001????1 (Appt Division for Division)
END
AS Lowest_In, Produce unique ID - Business key (RelationshipNature_Tp
CASE WHEN RelationshipNature_Tp
Relationship Data Mapping = 040010103 -- InternalOrganizationHrchy_Id
InternalOrganizationHrchy FND_FCML_DB PartyRelationship PartyRelationship_Id (110010101 - Appointment Division Hierarchy),
Appt Agency for Region PartyRelationship_Id)
THEN '2'
Relationship Data Mapping InternalOrganizationHrchy RelationshipNature_Tp FND_FCML_DB AnchorType AnchorType_Id 110010101 (Appointment Division Hierarchy)
WHEN RelationshipNature_Tp
Relationship Data Mapping = 040010102 -- Appt RelationshipNature_Ds
InternalOrganizationHrchy FND_FCML_DB AnchorType Type_Ds See SQL example for Join Logic
Region for Super Region Move
Relationship
THEN '1' Data Mapping InternalOrganizationHrchy ParentOrganizationUnit_Id FND_FCML_DB PartyRelationship RelatedParty_Id
WHEN RelationshipNature_Tp
Relationship Data Mapping
= 040010101 -- Appt
InternalOrganizationHrchy ChildOrganizationUnit_Id FND_FCML_DB PartyRelationship Party_Id Move
Super Region for Division
THEN '0'
Relationship Data Mapping InternalOrganizationHrchy Lowest_Fl FND_FCML_DB PartyRelationship See Transformation Rule N
END
0
AS DepthFromParent_Nb,
Relationship Data Mapping InternalOrganizationHrchy DepthFromParent_Nb FND_FCML_DB PartyRelationship See Transformation Rule
CASE WHEN RelationshipNature_Tp = 040010101 --
Appt Agency forData
Relationship Region
Mapping InternalOrganizationHrchy Topmost_Fl FND_FCML_DB PartyRelationship See Transformation Rule Y
THEN 'Y' -- TRUE
Relationship
ELSE Data Mapping
'N' -- FALSE InternalOrganizationHrchy Effective_Dt FND_FCML_DB PartyRelationship Effective_Dt ETL Processing Date
END
Relationship Data Mapping InternalOrganizationHrchy Expiration_Dt FND_FCML_DB PartyRelationship Expiration_Dt Default (high value)
AS Topmost_In,Data Mapping
Relationship InternalOrganizationHrchy Transaction_Ts FND_FCML_DB PartyRelationship Transaction_Ts ETL Processing Date
PartyRelationship.Effective_Dt,
Relationship Data Mapping InternalOrganizationHrchy Revision_Ts FND_FCML_DB PartyRelationship Revision_Ts Default (high value)
PartyRelationship.Expiration_Dt,
PartyRelationship.Transaction_Ts, - Population Cycle Description
PartyRelationship.Revision_Ts, - Population Timestamp ( date and time of population cycle)
(Transaction_Ts)
PartyRelationship.PopulationInfo_Id
Relationship Data Mapping InternalOrganizationHrchy PopulationInfo_Id FND_FCML_DB PopulationInfo See transformation rule
- Source System Label (description of application or system
from which the information last used to update the entity
FROM FND_FCML_DB_TEST.PartyRelationship instance was populated).

LEFT OUTER JOIN

FND_FCML_DB_TEST.OrganizationUnit Parent
ON PartyRelationship.RelatedParty_Id =
Parent.Party_Id

LEFT OUTER JOIN

FND_FCML_DB_TEST.OrganizationUnit Child
ON PartyRelationship.Party_Id = Child.Party_Id
Page 1 of 158
LEFT OUTER JOIN
RPT_FCML_DB_TEST.AnchorType
ON RelationshipNature_Tp =
AnchorType.AnchorType_Id
InternalOrganizationHrchy

Target Target Target Data Element

For each desired level of the InternalOrganizationHrchy table

(Appt Division, Appt Super Region, Appt Region) as
represented in the RelationshipNature_Tp of the TARGET,
traverse each level found in the PartyRelationship source rows
to produce all lower level records.
Relationship Row Building Rule InternalOrganizationHrchy FND_FCML_DB PartyRelationship The block below is for the 110010101 (Appointment Division
Hierarchy) level. Include
PartyRelationship.RelationshipNature_Tp values:
040010101 (Appt Super Region for Division),
040010102 (Appt Region for Super Region),
040010103 (Appt Agency for Region)

Produce unique ID - Business key (RelationshipNature_Tp

Relationship Data Mapping InternalOrganizationHrchy InternalOrganizationHrchy_Id FND_FCML_DB PartyRelationship PartyRelationship_Id (110010101 - Appointment Division Hierarchy),
PartyRelationship_Id)
Relationship Data Mapping InternalOrganizationHrchy RelationshipNature_Tp FND_FCML_DB AnchorType AnchorType_Id 110010101 (Appointment Division Hierarchy)
Relationship Data Mapping InternalOrganizationHrchy RelationshipNature_Ds FND_FCML_DB AnchorType Type_Ds See SQL example for Join Logic
If (PartyRelationship.RelationshipNature_Tp = 040010101) --
Appt Super Region for Division
Then Move RelatedParty_Id
Else If (PartyRelationship.RelationshipNature_Tp =
040010102) -- Appt Region for Super Region
Then Find PartyRelationship where Party_Id = the current
RelatedParty_id and RelationshipNature_Tp = 040010103
(Appt Super Region for Division) and Move that record's
RelatedParty_Id
Else If (PartyRelationship.RelationshipNature_Tp =
040010103) -- Appt Agency for Region
Then Find PartyRelationship where Party_Id = the current
Relationship Data Mapping InternalOrganizationHrchy ParentOrganizationUnit_Id FND_FCML_DB PartyRelationship See Transformation Rule RelatedParty_id and RelationshipNature_Tp = 040010102
(Appt Region for Super Region), next use that record's
RelatedParty_Id to find another PartyRelationship record where
the Party_Id = the interim RelatedParty_Id and
RelationshipNature_Tp = 040010101 (Appt Super Region for
Division) and Move that record's RelatedParty_Id

Relationship Data Mapping InternalOrganizationHrchy ChildOrganizationUnit_Id FND_FCML_DB PartyRelationship Party_Id Move

If (PartyRelationship.RelationshipNature_Tp = 040010103) --
Appt Agency for Region
Relationship Data Mapping InternalOrganizationHrchy Lowest_Fl FND_FCML_DB PartyRelationship See Transformation Rule
Then Lowest_Fl = 'Y'
Else Lowest_Fl = 'N'
If (PartyRelationship.RelationshipNature_Tp = 040010103) --
Appt Agency for Region
Then DepthFromParent_Nb = 3
Else If (PartyRelationship.RelationshipNature_Tp =
040010102) -- Appt Region for Super Region
Relationship Data Mapping InternalOrganizationHrchy DepthFromParent_Nb FND_FCML_DB PartyRelationship See Transformation Rule Then DepthFromParent_Nb =2
Else If (PartyRelationship.RelationshipNature_Tp =
040010101) -- Appt Super Region for Division
Then DepthFromParent_Nb =1

Page 2 of 158
InternalOrganizationHrchy

Target Target Target Data Element

Relationship Data Mapping InternalOrganizationHrchy Topmost_Fl FND_FCML_DB PartyRelationship See Transformation Rule N

Relationship Data Mapping InternalOrganizationHrchy Effective_Dt FND_FCML_DB PartyRelationship Effective_Dt ETL Processing Date
Relationship Data Mapping InternalOrganizationHrchy Expiration_Dt FND_FCML_DB PartyRelationship Expiration_Dt Default (high value)
Relationship Data Mapping InternalOrganizationHrchy Transaction_Ts FND_FCML_DB PartyRelationship Transaction_Ts ETL Processing Date
Relationship Data Mapping InternalOrganizationHrchy Revision_Ts FND_FCML_DB PartyRelationship Revision_Ts Default (high value)

- Population Cycle Description

- Population Timestamp ( date and time of population cycle)
(Transaction_Ts)
Relationship Data Mapping InternalOrganizationHrchy PopulationInfo_Id FND_FCML_DB PopulationInfo See transformation rule
- Source System Label (description of application or system
from which the information last used to update the entity
instance was populated).

For each Appt Super Region as represented in the

RelationshipNature_Tp of the TARGET, build one Super
Region.
Relationship Row Building Rule InternalOrganizationHrchy FND_FCML_DB PartyRelationship The block below is for the 110010102 (Appointment Super
Region Hierarchy) level. Include
PartyRelationship.RelationshipNature_Tp value:
04001???2 (Appt Super Region for Super Region)

Produce unique ID - Business key (RelationshipNature_Tp

Relationship Data Mapping InternalOrganizationHrchy ChildOrganizationUnit_Id FND_FCML_DB PartyRelationship Party_Id Move

Relationship Data Mapping InternalOrganizationHrchy Lowest_Fl FND_FCML_DB PartyRelationship See Transformation Rule N

0
Relationship Data Mapping InternalOrganizationHrchy DepthFromParent_Nb FND_FCML_DB PartyRelationship See Transformation Rule

Relationship Data Mapping InternalOrganizationHrchy Topmost_Fl FND_FCML_DB PartyRelationship See Transformation Rule Y

- Population Cycle Description

Page 3 of 158
InternalOrganizationHrchy

Target Target Target Data Element

For each desired level of the InternalOrganizationHrchy table

(Appt Super Region, Appt Region) as represented in the
RelationshipNature_Tp of the TARGET, traverse each level
found in the PartyRelationship source rows to produce all lower
level records.
Relationship Row Building Rule InternalOrganizationHrchy FND_FCML_DB PartyRelationship
The block below is for the 110010102 (Appointment Super
Region Hierarchy) level. Include
PartyRelationship.RelationshipNature_Tp values:
040010102 (Appt Region for Super Region),
040010103 (Appt Agency for Region)

Produce unique ID - Business key (RelationshipNature_Tp

Relationship Data Mapping InternalOrganizationHrchy InternalOrganizationHrchy_Id FND_FCML_DB PartyRelationship PartyRelationship_Id (110010102 - Appointment Super Region Hierarchy),
PartyRelationship_Id)
Relationship Data Mapping InternalOrganizationHrchy RelationshipNature_Tp See Transformation Rule 110010102 (Appointment Super Region Hierarchy)
Relationship Data Mapping InternalOrganizationHrchy RelationshipNature_Ds FND_FCML_DB AnchorType Type_Ds See SQL example for Join Logic
If (PartyRelationship.RelationshipNature_Tp = 040010102) --
Appt Region for Super Region
Then Move RelatedParty_Id
Else If (PartyRelationship.RelationshipNature_Tp =
040010103) -- Appt Agency for Region
Relationship Data Mapping InternalOrganizationHrchy ParentOrganizationUnit_Id FND_FCML_DB PartyRelationship See Transformation Rule Then Find PartyRelationship where Party_Id = the current
RelatedParty_id and RelationshipNature_Tp = 040010102
(Appt Region for Super Region) and Move that record's
RelatedParty_Id

Relationship Data Mapping InternalOrganizationHrchy ChildOrganizationUnit_Id FND_FCML_DB PartyRelationship Party_Id Move

If (PartyRelationship.RelationshipNature_Tp = 040010103) --
Appt Agency for Region
Then DepthFromParent_Nb = 2
Relationship Data Mapping InternalOrganizationHrchy DepthFromParent_Nb FND_FCML_DB PartyRelationship See Transformation Rule
Else If (PartyRelationship.RelationshipNature_Tp =
040010102) -- Appt Region for Super Region
Then DepthFromParent_Nb =1

Relationship Data Mapping InternalOrganizationHrchy Topmost_Fl FND_FCML_DB PartyRelationship See Transformation Rule N

- Population Cycle Description

Page 4 of 158
InternalOrganizationHrchy

Target Target Target Data Element

For each Appt Region row as represented in the

RelationshipNature_Tp of the TARGET, build one region level
row.
Relationship Row Building Rule InternalOrganizationHrchy FND_FCML_DB PartyRelationship The block below is for the 110010103 (Appointment Division
Hierarchy) level. Include
PartyRelationship.RelationshipNature_Tp values:
04001????3 (Appt Region for Region)

Produce unique ID - Business key (RelationshipNature_Tp

Relationship Data Mapping InternalOrganizationHrchy InternalOrganizationHrchy_Id FND_FCML_DB PartyRelationship PartyRelationship_Id (110010103 - Appointment Region Hierarchy),
PartyRelationship_Id)
Relationship Data Mapping InternalOrganizationHrchy RelationshipNature_Tp FND_FCML_DB AnchorType AnchorType_Id 110010103 (Appointment Region Hierarchy)
Relationship Data Mapping InternalOrganizationHrchy RelationshipNature_Ds FND_FCML_DB AnchorType Type_Ds See SQL example for Join Logic
Move
Relationship Data Mapping InternalOrganizationHrchy ParentOrganizationUnit_Id FND_FCML_DB PartyRelationship RelatedParty_Id

Relationship Data Mapping InternalOrganizationHrchy ChildOrganizationUnit_Id FND_FCML_DB PartyRelationship Party_Id Move

Relationship Data Mapping InternalOrganizationHrchy Lowest_Fl FND_FCML_DB PartyRelationship See Transformation Rule N

0
Relationship Data Mapping InternalOrganizationHrchy DepthFromParent_Nb FND_FCML_DB PartyRelationship See Transformation Rule

Relationship Data Mapping InternalOrganizationHrchy Topmost_Fl FND_FCML_DB PartyRelationship See Transformation Rule Y

- Population Cycle Description

For each desired level of the InternalOrganizationHrchy table

(Appt Division, Appt Super Region, Appt Region) as
represented in the RelationshipNature_Tp of the TARGET,
traverse each level found in the PartyRelationship source rows
Relationship Row Building Rule InternalOrganizationHrchy FND_FCML_DB PartyRelationship to produce all lower level records.
The block below is for the 110010103 (Appointment Region
Hierarchy) level. Include
PartyRelationship.RelationshipNature_Tp value:
040010103 (Appt Agency for Region)

Produce unique ID - Business key (RelationshipNature_Tp

Relationship Data Mapping InternalOrganizationHrchy InternalOrganizationHrchy_Id FND_FCML_DB PartyRelationship PartyRelationship_Id (110010103 - Appointment Super Region Hierarchy),
PartyRelationship_Id)
Relationship Data Mapping InternalOrganizationHrchy RelationshipNature_Tp See Transformation Rule 110010103 (Appointment Region Hierarchy)
Relationship Data Mapping InternalOrganizationHrchy RelationshipNature_Ds FND_FCML_DB AnchorType Type_Ds See SQL example for Join Logic

Relationship Data Mapping InternalOrganizationHrchy ParentOrganizationUnit_Id FND_FCML_DB PartyRelationship RelatedParty_Id Move

Relationship Data Mapping InternalOrganizationHrchy ChildOrganizationUnit_Id FND_FCML_DB PartyRelationship Party_Id Move

Page 5 of 158
InternalOrganizationHrchy

Target Target Target Data Element

Source Source Segment Source Data Element Name
Instance Instance Rule Target Table Name Name (column, field, Transformation Rule 1
Database(s) Code(s) (column, field, attribute)
Identifier Type attribute)
If (PartyRelationship.RelationshipNature_Tp = 040010103) --
Appt Agency for Region
Relationship Data Mapping InternalOrganizationHrchy Lowest_Fl FND_FCML_DB PartyRelationship See Transformation Rule
Then Lowest_Fl = 'Y'
Else Lowest_Fl = 'N'

If (PartyRelationship.RelationshipNature_Tp = 040010103) --
Relationship Data Mapping InternalOrganizationHrchy DepthFromParent_Nb FND_FCML_DB PartyRelationship See Transformation Rule Appt Agency for Region
Then DepthFromParent_Nb = 1

Relationship Data Mapping InternalOrganizationHrchy Topmost_Fl FND_FCML_DB PartyRelationship See Transformation Rule N

- Population Cycle Description

Page 6 of 158
InternalOrganizationHrchy

EA Data
Target
Element
Form Additiona
Referenc Group
Target Target Rated/No l Last Last EA Data
Target e or Additional EA System Name
Transformation Rule 2 Form # Form # n- Exclusio Updated Updated Status ISSUE Element
Form # Lookup Comments ID (table,
mapped tested Rated/Ma ns / By Date ID
Table segment,
nually Validity
file,
Rated
entity)

CDW 11/16/2007

CDW 11/16/2007
CDW 11/16/2007

CDW 11/16/2007

CDW 11/16/2007
CDW 11/16/2007
CDW 11/16/2007
CDW 11/16/2007

CDW 11/16/2007

Page 7 of 158
InternalOrganizationHrchy EA Data
Target
Element
Form Additiona
Referenc Group
Target Target Rated/No l Last Last EA Data
Target e or Additional EA System Name
Transformation Rule 2 Form # Form # n- Exclusio Updated Updated Status ISSUE Element
Form # Lookup Comments ID (table,
mapped tested Rated/Ma ns / By Date ID
Table segment,
nually Validity
file,
Rated
entity)

Will need to build a 0 depth record for Division to

Division heirarchy. (see block above for row CDW 11/16/2007
building.)

CDW 11/16/2007

CDW 11/16/2007
CDW 11/16/2007

CDW 11/16/2007

Page 8 of 158
InternalOrganizationHrchy EA Data
Target
Element
Form Additiona
Referenc Group
Target Target Rated/No l Last Last EA Data
Target e or Additional EA System Name
Transformation Rule 2 Form # Form # n- Exclusio Updated Updated Status ISSUE Element
Form # Lookup Comments ID (table,
mapped tested Rated/Ma ns / By Date ID
Table segment,
nually Validity
file,
Rated CDW 11/16/2007
entity)
CDW 11/16/2007
CDW 11/16/2007
CDW 11/16/2007
CDW 11/16/2007

CDW 11/16/2007

CDW 11/16/2007
CDW 11/16/2007

CDW 11/16/2007

CDW 11/16/2007
CDW 11/16/2007
CDW 11/16/2007
CDW 11/16/2007

CDW 11/16/2007

Page 9 of 158
InternalOrganizationHrchy EA Data
Target
Element
Form Additiona
Referenc Group
Target Target Rated/No l Last Last EA Data
Target e or Additional EA System Name
Transformation Rule 2 Form # Form # n- Exclusio Updated Updated Status ISSUE Element
Form # Lookup Comments ID (table,
mapped tested Rated/Ma ns / By Date ID
Table segment,
nually Validity
file,
Rated
entity)

CDW 11/16/2007

CDW 11/16/2007
CDW 11/16/2007

CDW 11/16/2007

CDW 11/16/2007
CDW 11/16/2007
CDW 11/16/2007
CDW 11/16/2007

CDW 11/16/2007

Page 10 of 158
InternalOrganizationHrchy EA Data
Target
Element
Form Additiona
Referenc Group
Target Target Rated/No l Last Last EA Data
Target e or Additional EA System Name
Transformation Rule 2 Form # Form # n- Exclusio Updated Updated Status ISSUE Element
Form # Lookup Comments ID (table,
mapped tested Rated/Ma ns / By Date ID
Table segment,
nually Validity
file,
Rated
entity)

CDW 11/16/2007

CDW 11/16/2007
CDW 11/16/2007

CDW 11/16/2007

CDW 11/16/2007
CDW 11/16/2007
CDW 11/16/2007
CDW 11/16/2007

CDW 11/16/2007

CDW 11/16/2007
CDW 11/16/2007

CDW 11/16/2007

Page 11 of 158
InternalOrganizationHrchy EA Data
Target
Element
Form Additiona
Referenc Group
Target Target Rated/No l Last Last EA Data
Target e or Additional EA System Name
Transformation Rule 2 Form # Form # n- Exclusio Updated Updated Status ISSUE Element
Form # Lookup Comments ID (table,
mapped tested Rated/Ma ns / By Date ID
Table segment,
nually Validity
file,
Rated
CDW 11/16/2007
entity)

CDW 11/16/2007

CDW 11/16/2007
CDW 11/16/2007
CDW 11/16/2007
CDW 11/16/2007

CDW 11/16/2007

Page 12 of 158
InternalOrganizationHrchy

EA EA Data Element EA EA Re- Comment

EA Layout Name (Copy- EA Data
Group Name (column, field, Occurs defines (EA va IS
book) Type
Level attribute) Number Flag vs IAA)

Page 13 of 158
InternalOrganizationHrchy

EA EA Data Element EA EA Re- Comment

EA Layout Name (Copy- EA Data
Group Name (column, field, Occurs defines (EA va IS
book) Type
Level attribute) Number Flag vs IAA)

Page 14 of 158
InternalOrganizationHrchy

EA EA Data Element EA EA Re- Comment

EA Layout Name (Copy- EA Data
Group Name (column, field, Occurs defines (EA va IS
book) Type
Level attribute) Number Flag vs IAA)

Page 15 of 158
InternalOrganizationHrchy

EA EA Data Element EA EA Re- Comment

EA Layout Name (Copy- EA Data
Group Name (column, field, Occurs defines (EA va IS
book) Type
Level attribute) Number Flag vs IAA)

Page 16 of 158
InternalOrganizationHrchy

EA EA Data Element EA EA Re- Comment

EA Layout Name (Copy- EA Data
Group Name (column, field, Occurs defines (EA va IS
book) Type
Level attribute) Number Flag vs IAA)

Page 17 of 158
InternalOrganizationHrchy

EA EA Data Element EA EA Re- Comment

EA Layout Name (Copy- EA Data
Group Name (column, field, Occurs defines (EA va IS
book) Type
Level attribute) Number Flag vs IAA)

Page 18 of 158
Population Information
Resource Status
Rakesh Verma N/A

Target Source
Target Data Element Name (column, field, Source Source Data Element Name (column, field,
Instance Target Instance Rule Type Target Table Name Segment
attribute) Database(s) attribute)
Identifier Code(s)

PopulationInfo Row Building Rule PopulationInfo See transformation rule

PopulationInfo Data Mapping PopulationInfo PopulationInfo_Id See transformation rule

PopulationInfo Data Mapping PopulationInfo UniqueSource_Id FCML

PopulationInfo Data Mapping PopulationInfo PopulationCycle_Ds ETL DATA LOAD
PopulationInfo Data Mapping PopulationInfo Population_Ts System Timestamp
PopulationInfo Data Mapping PopulationInfo SourceSystem_Nm Commercial Foundation

Page 19 of 158
Population Information

For each ETL batch or cycle, create one row DJL 6/5/2007
Produce unique ID - Business key (Source System, Subject
Area, 'FCML', 'ETL DATA LOAD', System Timestamp, CDW 11/2/2007
'Commercial Foundation')
Move DJL 6/5/2007
Move DJL 6/5/2007
Move DJL 6/5/2007
Move CDW 11/2/2007

Page 20 of 158
Population Information

Page 21 of 158
Data
Element
Group
Name
(table,
Data mapped segment, Layout Name Group
Element ID N/A mapped? Mercator unit tested System ID file, entity) (Copy-book) Level
Last
Transformation Comments/ Last Updated
Data Element Name (column, field, attribute) Data Type Rule Questions Updated By Date
Additional
Status Comments
Resource # Type Name Location

The Process of Data Mapping For Data Integration Projects
No ratings yet
The Process of Data Mapping For Data Integration Projects
6 pages
Data Quality Assessment Guide
No ratings yet
Data Quality Assessment Guide
7 pages
Enterprise Data Planning Overview
100% (1)
Enterprise Data Planning Overview
24 pages
Data Modeling Techniques Overview
100% (1)
Data Modeling Techniques Overview
11 pages
Preso Accenture - INFADAY - 2011
No ratings yet
Preso Accenture - INFADAY - 2011
18 pages
Testing A Data Warehouse
100% (2)
Testing A Data Warehouse
7 pages
Informatica Velocity
No ratings yet
Informatica Velocity
361 pages
ETL Mapping Sheet
No ratings yet
ETL Mapping Sheet
23 pages
Understanding Customer 360 Benefits
No ratings yet
Understanding Customer 360 Benefits
20 pages
Template - Phase 1 - Data Design Document
No ratings yet
Template - Phase 1 - Data Design Document
11 pages
Master Data Management - Wikipedia, The Free Encyclopedia
No ratings yet
Master Data Management - Wikipedia, The Free Encyclopedia
4 pages
Data Dictionary Template
No ratings yet
Data Dictionary Template
10 pages
Best Practices for Data Model Reviews
No ratings yet
Best Practices for Data Model Reviews
3 pages
2.data Modeling Overview
No ratings yet
2.data Modeling Overview
36 pages
ETL Process Definitions and Deliverables
100% (1)
ETL Process Definitions and Deliverables
4 pages
MDM Project Interview Guide
No ratings yet
MDM Project Interview Guide
20 pages
Business Analyst Resume Example
No ratings yet
Business Analyst Resume Example
1 page
Logical Data Modeling Guide
No ratings yet
Logical Data Modeling Guide
13 pages
Informatica MDM for IT Professionals
No ratings yet
Informatica MDM for IT Professionals
8 pages
Salesforce Data Migration Tips
No ratings yet
Salesforce Data Migration Tips
10 pages
5.banking Setup
No ratings yet
5.banking Setup
23 pages
Data Governance & MDM Essentials
No ratings yet
Data Governance & MDM Essentials
22 pages
ETL Project Lifecycle Guide
No ratings yet
ETL Project Lifecycle Guide
3 pages
Etl Testing Guide - Types of Etl Testing
No ratings yet
Etl Testing Guide - Types of Etl Testing
6 pages
ETL QA Sample Scenario V3
100% (2)
ETL QA Sample Scenario V3
3 pages
Bloor Research On Data Migration
No ratings yet
Bloor Research On Data Migration
13 pages
Sakhile Sishi - Systems Analyst CV
No ratings yet
Sakhile Sishi - Systems Analyst CV
22 pages
Data Migration Deloitte Solution-Siemens
No ratings yet
Data Migration Deloitte Solution-Siemens
2 pages
BI Project Management
No ratings yet
BI Project Management
11 pages
SAP BW - ETL Testing Data Warehouse Testing Tutorial (A Complete Guide)
No ratings yet
SAP BW - ETL Testing Data Warehouse Testing Tutorial (A Complete Guide)
66 pages
Data Virtuality Best Practices Guide
No ratings yet
Data Virtuality Best Practices Guide
18 pages
Remedy 7 Data Model User Guide - v1.1
No ratings yet
Remedy 7 Data Model User Guide - v1.1
32 pages
Modern Data Architecture Guide
No ratings yet
Modern Data Architecture Guide
18 pages
Non-Profit Solution For Microsoft Dynamics CRM: Users Guide
No ratings yet
Non-Profit Solution For Microsoft Dynamics CRM: Users Guide
55 pages
DWBI Testing by Puneet
No ratings yet
DWBI Testing by Puneet
27 pages
The Ultimate Guide: To Data Integration
No ratings yet
The Ultimate Guide: To Data Integration
14 pages
Quora - Informatica DW BI Ques ANS
No ratings yet
Quora - Informatica DW BI Ques ANS
7 pages
Data Warehousing & Mining Guide
100% (1)
Data Warehousing & Mining Guide
42 pages
Estimating Data Quality Project Stages
100% (1)
Estimating Data Quality Project Stages
8 pages
Extensive Experience in Data Integration Experience in Developing and Scripts Using
No ratings yet
Extensive Experience in Data Integration Experience in Developing and Scripts Using
9 pages
Data Architect or ETL Architect or BI Architect or Data Warehous
No ratings yet
Data Architect or ETL Architect or BI Architect or Data Warehous
4 pages
ETLDesignMethodologyDocument SSIS
100% (1)
ETLDesignMethodologyDocument SSIS
12 pages
Codeless MDM Integration with Profisee
No ratings yet
Codeless MDM Integration with Profisee
1 page
SAP Data Quality
No ratings yet
SAP Data Quality
58 pages
Meeting DWH QA Challenges Part 1
No ratings yet
Meeting DWH QA Challenges Part 1
9 pages
Big Data Fabric Architecture
No ratings yet
Big Data Fabric Architecture
15 pages
Global Services
No ratings yet
Global Services
15 pages
Datacamp ETL Documentation
No ratings yet
Datacamp ETL Documentation
19 pages
Big Data Testing Essentials
No ratings yet
Big Data Testing Essentials
10 pages
Idc Marketscape Excerpt Worldwide Business Analytics
No ratings yet
Idc Marketscape Excerpt Worldwide Business Analytics
9 pages
Data Mapping Essentials for Enterprises
No ratings yet
Data Mapping Essentials for Enterprises
17 pages
Pos
No ratings yet
Pos
1 page
Data Migration Implementation
No ratings yet
Data Migration Implementation
2 pages
Data Quality Migration
No ratings yet
Data Quality Migration
4 pages
Business Analytics for Enterprises
No ratings yet
Business Analytics for Enterprises
22 pages
Big Data Academy Overview Deck 032918 Updated
No ratings yet
Big Data Academy Overview Deck 032918 Updated
34 pages
Informatica ETL Specification Guide
No ratings yet
Informatica ETL Specification Guide
85 pages
Implementing SCD Type 2 in Informatica
0% (1)
Implementing SCD Type 2 in Informatica
16 pages
Exam - 1: October 5, 2016 Exam - 2: November 23, 2016 Quiz - 2: October 26, 2016 Quiz - 3: November 9, 2016
No ratings yet
Exam - 1: October 5, 2016 Exam - 2: November 23, 2016 Quiz - 2: October 26, 2016 Quiz - 3: November 9, 2016
14 pages
Name of Solution:: Please Rate This Solution and Share Your Feedback On Website
No ratings yet
Name of Solution:: Please Rate This Solution and Share Your Feedback On Website
3 pages
SQL Database Practicum Exam 2021/2022
No ratings yet
SQL Database Practicum Exam 2021/2022
15 pages
ITD102 Final Project Database Design For University Accommodation Office
No ratings yet
ITD102 Final Project Database Design For University Accommodation Office
7 pages
Railway Reservation System
0% (1)
Railway Reservation System
15 pages
BE03000091 DBMS Unit 4
No ratings yet
BE03000091 DBMS Unit 4
25 pages
Storing and Querying XML Data Using Rdbms Yesi Novaria Kunang
No ratings yet
Storing and Querying XML Data Using Rdbms Yesi Novaria Kunang
8 pages
Filesystem Analysis Using TSK
No ratings yet
Filesystem Analysis Using TSK
29 pages
15 Optimization
No ratings yet
15 Optimization
98 pages
Azure Data Analyst Certification Guide
No ratings yet
Azure Data Analyst Certification Guide
1 page
CS3492 Database Management Systems Two Mark Questions 1
100% (1)
CS3492 Database Management Systems Two Mark Questions 1
38 pages
Data Engineer-Resume
No ratings yet
Data Engineer-Resume
1 page
Key Constraints in DBMS
No ratings yet
Key Constraints in DBMS
8 pages
Multimedia Retrieval Insights
No ratings yet
Multimedia Retrieval Insights
33 pages
Module 1
No ratings yet
Module 1
15 pages
Siebel EIM Guide: Import, Export, Merge
No ratings yet
Siebel EIM Guide: Import, Export, Merge
38 pages
21BCS10039 - Faiz - Dbms Worksheet MST 1
No ratings yet
21BCS10039 - Faiz - Dbms Worksheet MST 1
7 pages
Lap 9
No ratings yet
Lap 9
3 pages
Senior Database Administrator Resume
No ratings yet
Senior Database Administrator Resume
6 pages
Atharva Joshi Final Resume 2
No ratings yet
Atharva Joshi Final Resume 2
2 pages
Lab Practical On OLAP (Online Analytical Processing)
No ratings yet
Lab Practical On OLAP (Online Analytical Processing)
5 pages
Database Viva
No ratings yet
Database Viva
10 pages
SQL Queries for HR and Sales Data
No ratings yet
SQL Queries for HR and Sales Data
3 pages
PHP & MySQL Quiz Results
No ratings yet
PHP & MySQL Quiz Results
4 pages
Manulife Harry
No ratings yet
Manulife Harry
11 pages
Cape It Unit 2 Compiled
100% (2)
Cape It Unit 2 Compiled
54 pages
Project 04 - Data Analysis of Covid-19.ipynb
No ratings yet
Project 04 - Data Analysis of Covid-19.ipynb
73 pages
Hadoop Distributed File System
No ratings yet
Hadoop Distributed File System
4 pages
Slow Moving Items - Scenario
No ratings yet
Slow Moving Items - Scenario
2 pages
Data Management Course Guide
No ratings yet
Data Management Course Guide
22 pages
Karakteristik Data Warehouse Terorganisir
No ratings yet
Karakteristik Data Warehouse Terorganisir
3 pages
Internship NIT Delhi
No ratings yet
Internship NIT Delhi
3 pages