Managing Domains and Member Mappings for Time and Labor — Reported Time

The Time and Labor — Reported Time dimension has a number of conformed domains that are used in many of the Time and Labor metrics.

These domains must be configured correctly for the reports to contain accurate information:

Optional or Mandatory

This task is optional; the default values might meet your business requirements.

Applies to

Oracle Fusion Applications, Oracle E-Business Suite, and PeopleSoft.

Task description in detail

Configuring the domains on the Time and Labor - Reported Time dimension are key to the successful attribution of time reporting entries to warehouse reporting unit of measure, punch types, and E-Business Suite Flexfield attributes.

Source Time Entry Unit of Measure Code to Timecard Unit of Measure Code

(Optional - the default values might meet your business requirements.) Used to identify how Source Time Entry Unit of Measure Code maps to delivered target Timecard Unit of Measure Code domain members. The target domain is Extensible - you can add to but not delete from it.

Example for Fusion

The Source Time Entry Unit of Measure Code is the FND lookup type, ORA_HWM_UOM.

Example Implementation

Source Member Code (Name)	Target Member Code (Name)
MI (Minute)
HR (Hour)	HOURS (Hours)
DY (Day)	DAYS (Days)
MN (Month)
YR (Year)

Example for E-Business Suite

The Source Time Entry Unit of Measure Code is the currently always assumed to be Hours.

Example Implementation

Source Member Code (Name)	Target Member Code (Name)
HOURS (Hours)	HOURS (Hours)

Source TRC Type Code and Source Unit Of Measure to Timecard Unit of Measure Code

This domain is a Multi Code domain member type; it uses two source domains (Source TRC Type Code and Source Unit of Measure) in combination to map to a target domain.

Example for PeopleSoft

Source TRC Type Code:

On PeopleSoft the Source TRC Type Code is the PSXLATITEM TRC_TYPE.

Source Unit of Measure (UOM):

On PeopleSoft, the Source Unit of Measure is the PS_UNITS_TBL table.

Example Implementation

Source TRC Type Code	Source Unit Of Measure	Target Member Code
A (Amount)	__ANY__ (Any)	AMOUNT (Amount)
H (Hours)	__ANY__ (Any)	HOURS (Hours)
H (Hours)	MHR (Work Hour)	HOURS (Hours)

Source Timecard Punch Type Code to Timecard Punch Type Code

(Optional - the default values might meet your business requirements.) Used to identify how Source Timecard Punch Type Code maps to delivered target Timecard Punch Type Code domain members. The target domain is Extensible - you can add to but not delete from it.

Example for Fusion

The Source Timecard Punch Type Code is the currently always assumed to be Elapsed.

Example Implementation

Source Member Code (Name)	Target Member Code (Name)
ELAPSED (Elapsed)	ELAPSED (ELAPSED)

Example for E-Business Suite

The Source Timecard Punch Type Code is the currently always assumed to be Elapsed.

Example Implementation

Source Member Code (Name)	Target Member Code (Name)
ELAPSED (Elapsed)	ELAPSED (ELAPSED)

Example for PeopleSoft

Source TRC Type Code

On PeopleSoft the Source Timecard Punch Type Code is the PSXLATITEM PUNCH_TYPE.

Example Implementation

Source Member Code (Name)	Target Member Code (Name)
0 (Elapsed)	ELAPSED (ELAPSED)
1 (In)	IN (In)
2 (Out) *	OUT (Out)
3 (Meal)	MEAL (Meal)
4 (Break)	BRK (Break)
5 (Transfer)	XFR (Transfer)

* To reduce data volume, punch "Out" are not extracted into Oracle Business Analytics Warehouse.

Source Timecard Detail Attributes Descriptive Flexfield to Timecard Detail Flex Attributes

(Optional - the default values might meet your business requirements.) Used to identify how Source Timecard Detail Attributes Descriptive Flexfield maps to delivered target Timecard Detail Flex Attributes domain members. The target domain is Extensible - you can add to but not delete from it.

Example for E-Business Suite

The Source Timecard Detail Attributes Descriptive Flexfield domain includes the following:

Layout Identifier (LAYOUT_ID)
ATTRIBUTE_CATEGORY (HXC_TIME_ATTRIBUTES)
Descriptive flexfield column (e.g. ATTRIBUTE2)

The Source Timecard Detail Attributes Descriptive Flexfield domain should have been loaded by the domain task SDE_ORA_DESCRIPTIVEFLEXFIELD_COLUMN_TIMECARDLAYOUT.

Example Implementation

Source Member Code (Name)	Target Member Code (Name)
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_MAP_ELEMENT_TYPE_ID	ELEMENT_TYPE_ID_CHAR
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_MAP_HR_ORG_ID	HR_ORG_ID_CHAR
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_MAP_JOB_ID	JOB_ID_CHAR
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_MAP_PAY_GRADE_ID	PAY_GRADE_ID_CHAR
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_MAP_POSITION_ID	POSITION_ID_CHAR
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_MAP_LOCATION_ID	LOCATION_ID_CHAR
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_MAP_COST_CENTER_ID	COST_CENTER_ID_CHAR
11:PROJECTS:ATTRIBUTE1 Projects Timecard Layout - Project Id (Character)	PROJECT_ID_CHAR
11:PROJECTS:ATTRIBUTE2 Projects Timecard Layout - Task Id (Character)	TASK_ID_CHAR
11:PROJECTS:ATTRIBUTE3 Projects Timecard Layout - Expenditure Type (Character)	EXP_TYPE_ID_CHAR
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_MAP_BILLABLE_FLG	BILLABLE_FLAG_CHAR
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_MAP_PO_NUMBER	PO_NUMBER
1198:PURCHASING:ATTRIBUTE8 Projects-Purchasing Timecard Layout - PO Header ID (Character)	PO_HEADER_ID
1198:PURCHASING:ATTRIBUTE8 Projects-Purchasing Timecard Layout - PO Line ID (Character)	PO_LINE_ID
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_MAP_BUSINESS_GROUP_ID	BUSINESS_GROUP_ID_CHAR
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_MAP_PEOPLE_GROUP_ID	PEOPLE_GROUP_ID_CHAR
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_DET_ATTR1_CHAR	FLEX_DET_ATTR1_CHAR
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_DET_ATTR2_CHAR	FLEX_DET_ATTR2_CHAR
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_DET_ATTR3_CHAR	FLEX_DET_ATTR3_CHAR
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_DET_ATTR4_CHAR	FLEX_DET_ATTR4_CHAR
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_DET_ATTR5_CHAR	FLEX_DET_ATTR5_CHAR
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_DET_ATTR6_CHAR	FLEX_DET_ATTR6_CHAR
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_DET_ATTR7_CHAR	FLEX_DET_ATTR7_CHAR
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_DET_ATTR8_CHAR	FLEX_DET_ATTR8_CHAR
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_DET_ATTR9_CHAR	FLEX_DET_ATTR9_CHAR
No seeded mapping, see Associated Variable HR_TIMECARD_FLEX_DET_ATTR10_CHAR	FLEX_DET_ATTR10_CHAR

Associated Variables

Each Target Domain Code has an associated ODI Variable, listed in the table below. Each variable has a default value which should map the seeded Oracle E-Business Time and Labor Engine Timecard Layouts correctly in the majority of cases.

The case statement expression for mapped domains is retrieved from the Oracle Business Analytics Warehouse table, W_FLEX_SQL_G, via a Variable Refresh of the form:

SELECT COLUMN_EXPRESSION
FROM W_FLEX_SQL_G
WHERE DOMAIN_CODE = 'W_FLEX_TIMECARD_DETAIL_ATTRIBUTES'
AND DOMAIN_MEMBER_CODE = '<Target Domain Code>'
AND DATASOURCE_NUM_ID = #DATASOURCE_NUM_ID

The following table lists variable names and default expressions.

Variable Name	Default Expression	Refreshed from BIACM*
HR_TIMECARD_FLEX_MAP_ELEMENT_TYPE_ID	SUBSTR(HA.ATTRIBUTE_CATEGORY,1,7) = 'ELEMENT' THEN SUBSTR(HA.ATTRIBUTE_CATEGORY,LENGTH('ELEMENT - ') +1)	Y
HR_TIMECARD_FLEX_MAP_BUSINESS_GROUP_ID	WHEN HA.ATTRIBUTE_CATEGORY = 'SECURITY' THEN HA.ATTRIBUTE2	Y
HR_TIMECARD_FLEX_MAP_HR_ORG_ID	WHEN HA.ATTRIBUTE_CATEGORY = 'SECURITY' THEN HA.ATTRIBUTE1	Y
HR_TIMECARD_FLEX_MAP_JOB_ID	WHEN 1=0 THEN NULL	Y
HR_TIMECARD_FLEX_MAP_POSITION_ID	WHEN 1=0 THEN NULL	Y
HR_TIMECARD_FLEX_MAP_LOCATION_ID	WHEN 1=0 THEN NULL	Y
HR_TIMECARD_FLEX_MAP_PEOPLE_GROUP_ID	WHEN 1=0 THEN NULL	Y
HR_TIMECARD_FLEX_MAP_PO_HEADER_ID	WHEN HA.ATTRIBUTE_CATEGORY = 'PURCHASING' THEN HA.ATTRIBUTE8	Y
HR_TIMECARD_FLEX_MAP_PO_LINE_ID	WHEN HA.ATTRIBUTE_CATEGORY = 'PURCHASING' THEN HA.ATTRIBUTE2	Y
HR_TIMECARD_FLEX_MAP_PO_NUMBER	WHEN HA.ATTRIBUTE_CATEGORY = 'PURCHASING' THEN HA.ATTRIBUTE1	Y
HR_TIMECARD_FLEX_MAP_COST_CENTER_ID	WHEN HA.ATTRIBUTE_CATEGORY = 'Dummy Cost Context' THEN HA.ATTRIBUTE1	Y
HR_TIMECARD_FLEX_MAP_PROJECT_ID	WHEN HA.ATTRIBUTE_CATEGORY = 'PROJECTS' THEN HA.ATTRIBUTE1	Y
HR_TIMECARD_FLEX_MAP_TASK_ID	WHEN HA.ATTRIBUTE_CATEGORY = 'PROJECTS' THEN HA.ATTRIBUTE2	Y
HR_TIMECARD_FLEX_MAP_EXPENDITURE_TYPE_ID	WHEN HA.ATTRIBUTE_CATEGORY = 'PROJECTS' THEN HA.ATTRIBUTE3	Y
HR_TIMECARD_FLEX_MAP_BILLABLE_FLG	WHEN HA.ATTRIBUTE_CATEGORY = 'PROJECTS' THEN HA.ATTRIBUTE7	Y
HR_TIMECARD_FLEX_MAP_APPOVED_DATE	DECODE(HA.ATTRIBUTE_CATEGORY,'APPROVAL',DECODE(TRANSLATE(HA.ATTRIBUTE5, '0123456789', '0000000000'), '0000/00/00 00:00:00', FND_DATE.CANONICAL_TO_DATE(HA.ATTRIBUTE5)))	N
HR_TIMECARD_FLEX_MAP_APPOVER_ID	DECODE(HA.ATTRIBUTE_CATEGORY,'APPROVAL',HA.ATTRIBUTE3)	N
HR_TIMECARD_FLEX_MAP_CLA_COMMENTS	WHEN HA.ATTRIBUTE_CATEGORY = 'REASON' THEN HA.ATTRIBUTE2	Y
HR_TIMECARD_FLEX_MAP_CLA_REASON	WHEN HA.ATTRIBUTE_CATEGORY = 'REASON' THEN HA.ATTRIBUTE1	Y
HR_TIMECARD_FLEX_MAP_CLA_TYPE	WHEN HA.ATTRIBUTE_CATEGORY = 'REASON' THEN HA.ATTRIBUTE3	Y
HR_TIMECARD_FLEX_DET_ATTR1_CHAR, HR_TIMECARD_FLEX_DET_ATTR2_CHAR and so on to HR_TIMECARD_FLEX_DET_ATTR10_CHAR	WHEN 1=0 THEN NULL	Y

* BIACM is Oracle BI Applications Configuration Manager.

Note:

The ODI Interfaces / Expressions that make use of these substitution variable expressions (or domain generated expressions) will surround the expression with CASE statement logic, that is:

Default (no domain mapping):

CASE
WHEN 1=0 THEN NULL
END

With Source -> Target Domain Mappings:

WHEN TC.LAYOUT_ID = 11 
 AND HA.ATTRIBUTE_CATEGORY = 'PROJECTS' THEN HA.ATTRIBUTE2
WHEN TC.LAYOUT_ID = 123456 
 AND HA.ATTRIBUTE_CATEGORY = 'PROJECTS' THEN HA.ATTRIBUTE3
..

Example for PeopleSoft

This feature is not applicable to PeopleSoft.

Example for Fusion

This feature is not applicable to Oracle Fusion Applications.