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:
-
Source Time Entry Unit of Measure Code -> Timecard Unit of Measure Code
-
Source TRC Type Code~Source Unit Of Measure-> Timecard Unit of Measure Code
-
Source Timecard Detail Attributes Descriptive Flexfield -> Timecard Detail Flex Attributes
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) |
No value |
HR (Hour) |
HOURS (Hours) |
DY (Day) |
DAYS (Days) |
MN (Month) |
No value |
YR (Year) |
No value |
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
(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.
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:
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.