configuring timecards app
DESCRIPTION
time cardTRANSCRIPT
Oracle Time and Labor
Configuring Timecards App Implementation and Usage notes for Administrators
An Oracle Whitepaper
EBS Oracle Time and Labor Development
2 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Contributors:
Ashok Rajagopal Praveen Kumar Anima Prasad
Change History: Version Date Comments 1.0 08-Jun-2014 Initial Version
Acronyms Used: Usage Meaning OTL Oracle Time and Labor Timecards App Oracle Mobile Timecards for Oracle E-Business
Suite™ SSWA Self Service Web Applications LOV List of Values OTLR Oracle Time and Labor Rules Engine
EBS Oracle Time and Labor Development
3 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Contents SSWA time entry VS Smartphone Time entry .................................................................................... 4 Smartphone Time entry ...................................................................................................................... 4
Time entry using Smartphone App ................................................................................................. 8 Time Entry Implementation ........................................................................................................... 8 Non SSWA time entry and Timecards App ..................................................................................... 9
Using Timecards App ........................................................................................................................ 10 Administrators .............................................................................................................................. 10 Mobile Framework Configuration: ............................................................................................... 10 OTL Setup/Configuration: ............................................................................................................. 12 Users ............................................................................................................................................. 12
Configuring Timecards App .............................................................................................................. 13 Layout Components – Limitations and Restrictions ......................................................................... 15 Features in SSWA not available in Timecards App ........................................................................... 17 Features not supported in Timecards App ....................................................................................... 18 Features with limitations .................................................................................................................. 19 Seeded Layouts ready for use in Timecards App .............................................................................. 20 Using a custom Responsibility with Timecards App ......................................................................... 20 Using a Custom Menu with Timecards App ..................................................................................... 20 Using a configured layout with Timecards App ................................................................................ 21 Creating Value Sets for View Objects ............................................................................................... 21 Creating Value Sets for Hidden Attributes ....................................................................................... 26 Dependent LOVs ............................................................................................................................... 34
Method 1: Using Layout LDT files. ................................................................................................ 34 Method 2: ..................................................................................................................................... 36
Running OTL: Upgrade Layouts for Mobile Time Entry .................................................................... 45 Trouble Shooting .............................................................................................................................. 48
Mobile Foundation: ...................................................................................................................... 48 Timecards: .................................................................................................................................... 49
EBS Oracle Time and Labor Development
4 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
SSWA time entry VS Smartphone Time entry
SSWA time entry layouts follow a matrix layout where the whole timecard can be seen an edited in the same page. Sample screenshot provided below.
Time entry in the Smartphone App however has to make use of limited space on screen with maximum usability. Consequently the time entry mode in Timecards App is designed to make use of a day level time entry with means to navigate to next and previous days within the timecard period. While editing/viewing/creating a timecard above requires user to go to Recent Timecards-> Create/update in SSWA, the same action happens the following way in Timecards App.
Smartphone Time entry
Here is a quick overview of the Time entry process in the Timecards app.
Pic 1: You can use the same login credentials that you use to login to use the SSWA Timecard to login to the Timecards app.
Pic 2: You can use the same responsibility that you use to enter/update the SSWA Timecards in the Timecards app.
Pic 3: Just like the way a responsibility and functions are displayed in SSWA, Timecards app has a Springboard with all the time entry related features
EBS Oracle Time and Labor Development
5 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Pic 4: Similar to the Recent Timecards page on SSWA, Time Entry page on Timecards app lists all details for the selected date. Selecting a date and tapping on the Create button displays the Time Entry page PS: The week starts on Sunday by default, it can be changed to match you SSWA Timecard period start date by using Settings-> Start Week on
Pic 5: Time entry page displays the same number of Timecard Attributes as per the SSWA Timecard entry layout. Sample of Seeded Payroll Timecard layout on Timecards app.
Pic 6: Time entry page displays the same number of Timecard Attributes as per the SSWA Timecard entry layout. Sample of Seeded Projects Timecard layout on Timecards app.
EBS Oracle Time and Labor Development
6 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Pic 7: Tapping on Submit Time on the Springboard displays the list of all Timecards which need to be submitted for further processing. Timecards in Error status are displayed with a Error Icon
Pic 8: Just like Clicking on Details icon on the SSWA Recent Timecards page, Tapping on the Timecard period on Submit Time page on Timecards app displays the day wise summary of all Time entries and the status of the Timecards in the header region. You can Submit the Timecard by tapping on the Submit button on top right corner.
Pic 9: Just like the way, Time entry rule error messages are displayed when Timecard is submitted in SSWA, Timecards mobile app also displays the errors messages as part of Timecards submission process. You also have the option to edit the time entires from Time Details page.
EBS Oracle Time and Labor Development
7 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Pic 10: Tapping on View Time on the Springboard displays the list of all timecard for the current month which have been submitted and the latest status of that timecard.
Pic 11: Similar to the concept of Templates in SSWA Timecard, there is an option of Quick Entry which defaults either the Last Saved Entry or the your Favourite time entry for today
Pic 12: Clicking on the Action button on the Footer displays the other options to quickly enter the time. You can enable the Default Quick Time by using Settings page.
EBS Oracle Time and Labor Development
8 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Time entry using Smartphone App
Time entry from Timecards App, as a result of the innate mobility is different from SSWA applications in the following ways.
Time entry/edit/delete follows a one-detail per entry mode. It means that each detail needs to be entered and a save issued.
Due to limited availability of space, the maximum number of layout attributes allowed in Timecards App is set to Seven.
A timecard gets automatically created for each detail Saved as mentioned above. This timecard has a NEW status Draft.
A timecard saved in Draft status in the device is not visible in SSWA applications. However, this timecard period is not available for Create/Edit/Approve or Retrieve to recipient applications. This is to ensure data integrity.
A timecard Saved (status: Working), Submitted (status: Submitted), Approved (status: Approved), or Rejected (status: Rejected), acted upon in the Device with an edit would be automatically converted to Draft Status. However, this status change and the changes to timecard data are not visible in SSWA until the following action is done.
Timecard(s) saved in Draft status need to be submitted by the user from the Device to complete timecard creation/edit activity.
Time Entry Implementation
With the multiple flavors of time entry available in OTL with flexible options, the primary objective
of the design of the Timecards App time entry mode is to ensure that basic functionality of time entry and configurations are made available to the smartphone devices with best performance and easiest configuration options. Timecards App time entry follows the Self Service timecard layout concepts as the foundation to build on. The following salient features may be noted.
OTL Self Service time entry uses the concept of Self Service Timecard Layouts which are packaged in LDT files. Each of the LDT files defines an array of layout components, the definition of which is available for further configuration. Self Service Timecard Layouts use View Objects for various Layout Attributes to search for, select and display Timecard attributes. To aid ease of configuration, upgrade and optimum performance, Timecards App uses Value Sets in place of View Objects for Layout Attributes to search for, select and display Timecard Attributes. Consequently all self service layouts require an upgrade path to enable them for Time entry using Timecards App. This is detailed in the later sections.
The following table shows the basic differences between SSWA based time entry and Timecards App based time entry
EBS Oracle Time and Labor Development
9 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Table 1
SSWA Timecards App
Follows matrix layout where timecards are created/edited as a whole
Follows a detail layout where time entries are created/edited detail by detail
Requires one Save per timecard after editing one or more details
Requires one Save(to Draft status) per time entry.
Layout attributes are primarily based on View Objects Layout Attributes are primarily based on Value Sets
Allows extensive configuration by multiple permutations of LDT file definitions
Configuration limited to Layout attributes definition and order.
No Draft status Saved time entries move into Draft Status timecards
Periods available for edit dependent upon Self Service status that allows Edits
Maximum 3 months periods available for edit. Self Service status that allows Edits may be used to further limit time entries.
Does not allow edit of timecards in Error Status Allows edit of timecards in Error status.
LOV components are allowed to have multiple columns for additional information
LOV components are allowed to have a Single column to select with no additional columns shown. However, a concatenated list of columns may be configured.
No restriction on number of layout attributes available on Self Service layout
Number of layout attributes allowed is limited to 7
Non SSWA time entry and Timecards App
OTL also provides other time entry modes like Timekeeper and Timestore Deposit API. While these are meant to work hand in hand with SSWA, there are shops which use OTL exclusively with Timekeepers and Timestore APIs. Such implementations however do not fall in the primary usage of Smartphone where an employee is supposed to key in time for themselves. However in cases where TK or API based time entry complements SSWA based time entry, Timecards App functionality has a value.
If your OTL implementation does not use a SSWA time entry, and no Self Service layouts (Seeded or custom) are used for time entry, it is not possible to use Timecards App. Timecards App requires a valid Self Service layout attached to the employee’s preferences used as the foundation for time entry.
The following points about usage of Timecards App may be noted for implementations using Timekeeper and/or Timestore Deposit API for time entry.
Timecards App requires a Self Service layout attached to the employee for functioning.
It may be possible for Administrators to attach Self service layouts to employees in preferences, though SSWA is not used by employees. Timecards App may be usable in this case, but with respect to the following point.
In cases where Self Service layout and TK layout are not equivalent to each other, Timecards App time entry bases itself on Self Service layout. Eg. If Self Service layout uses seeded Payroll layout (with Hours type and Cost centre), while timekeeper uses a layout with Hours Type, Cost Centre and Activity Type, Timecards App follows the attributes from Self Service layout.
In direct consequence to the above factor, a layout attribute configured in Timekeeper only would not be available for use in Timecards App.
EBS Oracle Time and Labor Development
10 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Using Timecards App
Administrators
Administrators need to perform the following steps to make Timecards App available for users.
1. Mobile Framework Configuration 2. OTL Setup/Configuration
Mobile Framework Configuration:
For Timecards App to access the EBS server, the mobile configuration service needs to be enabled using the following steps given below. The following MOS note can be accessed to get more details on the Mobile Framework Configuration steps: Oracle E-Business Suite Mobile Apps Administrator's Guide for Release 12.1 and Release 12.2 (Doc ID 1642431.1)
On an instance where mobile framework patches are installed, Login as SYSADMIN user and navigate to Mobile Framework responsibility.
Enter Time and Labor Engine in the Parent Application Field and search.
Click on the configure button, most of the parameter values are defaulted, except APPS_SESSION_COOKIE, mostly this value is the Database SID value.
EBS Oracle Time and Labor Development
11 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Click on the connections.xml and you will see the connections.xml file updated with the values you have provided.
Change the Status to Enabled, and click on the “Save and Generate Files” button.
To confirm if the configuration service has been successfully enabled, try this url http(s):<<your_host>>:<<port>>/OA_HTML/config/com.oracle.ebs.hr.hxc.Timecards/connections.xml
This URL should display the connections.xml file. If the configuration service is not enabled properly then a corresponding error is thrown.
EBS Oracle Time and Labor Development
12 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
OTL Setup/Configuration:
The initial version of Timecards App has limited support; Adjust OTL preferences to turn off features which are not required for mobile use.
Set Self Service Timecard Layouts to one that is already supported (see Table 3) or follow steps mentioned in this document to upgrade the Layouts used.
Add Role “Mobile Time Entry” to the responsibility used by the users. 1 Refer Using a custom Responsibility with Timecards App for more details.
Add Menu “Mobile Time Entry” as a submenu to the menu used for Time entry. 2
Users
To use Timecards App, users may need the following information.
Username/Password to log into Oracle E-Business Suite instance.
Oracle E-Business Suite Server URL in the following format : http(s)://<hostname>:<port>/OA_HTML/config
To start using Timecards App, a user must need to follow the steps below.
Download and install the App from authorized location.
In the one-time configuration screen, key in username, password and the configuration service URL.
After above configuration completes key in username and password in the Login page to authenticate the session.
In the Responsibilities page, choose the appropriate responsibility to use Timecards App.
1 This step may be skipped for seeded OTL self service responsibilities. This is a mandatory step for all custom
Responsibilities used to enter Time in SSWA. 2 This step may be skipped for seeded OTL Menus. This is a mandatory step for all custom menus.
EBS Oracle Time and Labor Development
13 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Configuring Timecards App
Seeded Projects Layout shown below.
The above screenshot shows Seeded Projects Layout. There are 3 layout attributes in the layout, Project, Task and Type. Each of these attributes are based on a View Object, shown in the picture. The respective View Objects are used to display the List of valid Values to select by the user and to translate already selected values. The same timecard, for the same user looks like below in Timecards App.
ProjectLOVVO
TaskLOVVO
ExpenditureType2LOVVO
ProjectLOVVO_VS
ExpenditureType2LOVVO_VS
TaskLOVVO_VS
EBS Oracle Time and Labor Development
14 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Each layout attribute in the Timecards App is based on a Value set for displaying List of Valid Values, and Translation of already stored attributes. These value sets are built based on the existing View Objects and are available out of the box for seeded layouts listed below. The following pages describe how a Self Service timecard layout is upgraded for usage in Timecards App. For some seeded layouts this is already taken care of out of the box, while for some this may be covered in future releases. Refer Table 3 for the list of Self Service Layouts upgraded out of the box. For all configured layouts, the detailed steps may be executed to enabled time entry using Timecards App.
EBS Oracle Time and Labor Development
15 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Layout Components – Limitations and Restrictions
List of Values
The following LOVs are supported
Seeded LOVs for the layouts listed in Table 3.
Configured LOVs based on CustomXVOs, in turn based on HXC_CUI_CUSTOMX_V, listed in section LOV Custom Objects in Timecard Config UI WP. These layouts require running of OTL: Upgrade layouts for Mobile time entry. Refer to page 45.
Any custom LOV object that works on a Self Service timecard layout, that is not dependent on a Hidden Field.
Any custom LOV object that works on a Self Service timecard layout, that stores not more than 3 hidden attributes.
Any custom LOV object that works on a Self Service timecard Layout which has not more than a single component with Hidden attributes.
Any custom LOV object that works on a Self Service timecard layout that uses the bind variables limited to this list.
“RESOURCE_IDENTIFIER_ID,TIMECARD_BIND_START_DATE,TIMECARD_BIND_END_DATE,ALIASID”
For all Layouts other than the Seeded Layouts listed in Table 3, running OTL: Upgrade Timecard Layouts program is mandatory.
Choice Lists
The following Choice lists are supported.
Seeded choice lists belonging to the layouts mentioned in Table 3.
Any custom choice list based on CustomXVO, in turn based on HXC_CUI_CUSTOMX_V views.
Any custom choice list that works on a Self Service Timecard Layout with not more than 3 hidden attributes, and a single component storing the hidden attributes.
Any custom choice list that works on a Self Service timecard layout that uses the bind variables limited to this list.
“RESOURCE_IDENTIFIER_ID,TIMECARD_BIND_START_DATE,TIMECARD_BIND_END_DATE,ALIASID”
For all Layouts other than the Seeded Layouts listed in Table 3, running OTL: Upgrade Timecard Layouts program is mandatory.
Package Choice Lists
The following package choice lists are supported.
Seeded package choice lists belonging to layouts mentioned in Table 3.
Any ONE custom package choice list per layout, using parameters limited to the following. RESOURCE_IDENTIFIER_ID,TIMECARD_BIND_START_DATE,TIMECARD_BIND_
END_DATE
Text fields
Layouts holding Text Fields for Timecard Attributes are not allowed to be used in Timecards App currently.
EBS Oracle Time and Labor Development
16 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Descriptive Flexfields
Any descriptive flexfield based layout attribute is not supported in Timecards App. This is planned for a future release.
EBS Oracle Time and Labor Development
17 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Features in SSWA not available in Timecards App
Following features in SSWA based time entry are not available in Timecards App. These are planned for a future release.
Override/Default Approver
Facility to enter Override approver is not available in Timecards App. However, the timecards created in devices may be edited in SSWA to key in Override approvers.
Timecard Level Comments
Timecards App does not allow users to key in timecard level comments. This is planned for a future release.
Detail Level Comments
Though not available in Seeded layouts, Self Service layouts can be configured to enable detail level comments for each time entry. This functionality is also available in Timekeeper. Timecards App however does not support a provision to enter detail level comments.
Additional Details
Self Service Layouts functionality of entering Additional details for Projects timecards, configured on PA Expenditure Item DFF is not available currently in Timecards App.
Absences/PTO Accrual region
Self Service functionality of displaying Absences/PTO in the timecard header is not available in Timecards App.
Timecard Header level Message
Since there is no provision in Timecards App to display any message in the timecard header, configuration option of timecard level message header is not available as well. This is planned for a future release.
Regular/Overtime Totals – Time category based Totals
The Hours totals on Confirmation page are not available for viewing in Timecards App. However, timecards created by the device may be opened in SSWA to view totals.
EBS Oracle Time and Labor Development
18 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Features not supported in Timecards App
The following features of OTL are not supported in Timecards App. If your implementation uses either of these functionalities, you may have to wait for a future release to use Timecards App.
Change and Late Audit
CLA functionality is not supported in Timecards App. This also means that employees using Timecards App should have no Audit Layout or Audit Time Entry rules attached to preferences along with Self service timecard flow set to standard.
OTL Absences Integration
OTL Absences Integration is not supported for Timecards App. An employee for whom Absences is configured as part of OTL preferences is not allowed to use Timecards App.
Quarterly/Half yearly/Yearly Time periods
Employees who use quarterly, half yearly or yearly time card or approval periods are not allowed to use Timecards App. Support to this feature is planned for a future release.
Templates Functionality
Facility to create and use Templates is not supported in Timecards App. This is planned for a future release
EBS Oracle Time and Labor Development
19 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Features with limitations
Following features of Oracle Time and Labor, though supported for Timecards App do have certain limitations of use.
OTLR Engine
Timecards App allows time entry and edit for OTLR users. However, time explosion is not available for users to see on Timecards App. For viewing explosion of time, the respective timecards have to be submitted from smartphone devices and must be opened thru Recent Timecards page in SSWA.
Time Entry Rules
Timecards App allows Time entry rule functionality for all cases except rules to be enforced during Timecard Save. Save action from devices move the timecards to status Draft which does not enforce Time entry rules. However, all time entry rules enabled to fire during submission and delete work as intended.
Supported Application sets
The following matrix shows application sets and their usability with Timecards App. Table 2
Application Set Layout Used Usage Note
Payroll Listed in Table 3 No Upgrade Required
Payroll and Human Resources Listed in Table 3 No Upgrade Required
Payroll Configured Layout Upgrade Required
Projects Listed in Table 3 No Upgrade Required
Projects Configured Layout Upgrade Required
Projects and Payroll Listed in Table 3 No Upgrade Required
Projects and Payroll Configured Layout Upgrade Required
Any application set mentioned above with Usage mentioned as Upgrade Required requires Administrators to run OTL: Upgrade Timecard Layouts for Mobile Time Entry for each layout used. This also means that such layouts may work only within the limitations mentioned in various sections in this document. Any application set for which layouts do not follow the standards listed in this document may not work seamlessly even after running OTL: Upgrade Timecard Layouts for Mobile Time entry.
Any application set which is not mentioned in the above Table, which may be a permutation of the available time recipients for OTL, is not certified to work with Timecards App.
EBS Oracle Time and Labor Development
20 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Seeded Layouts ready for use in Timecards App
The following table lists the seeded layouts ready for use out of the box in this release. Table 3
LAYOUT_NAME DISPLAY_LAYOUT_NAME
PAYROLL-TIMECARD Payroll Timecard Layout
PROJECTS-TIMECARD Projects Timecard Layout
EXCEPTION-TIMECARD Exception Layout - Timecard Entry Page
PROJECTS-ALTERNATE-TIMECARD Projects and Payroll Timecard Layout
EXCPTO-TIMECARD Exception Layout with ACC BAL - Timecard Entry Page
US-EXCEPTION-TIMECARD US Exception Layout - Timecard Entry Page
US-PROJECTS-ALTERNATE-TIMECARD US Projects and Payroll Timecard Layout
PROJECTS-DETAILS-TIMECARD Projects Timecard Layout - With Project & Task details concatenated
PROJECTS-DET-ALT-TIMECARD Projects and Payroll Timecard Layout - With Project & Task details concatenated
US-PROJPAY-DET-TIMECARD US Projects and Payroll Timecard Layout - With Project & Task details concatenated
Using a custom Responsibility with Timecards App
Timecards App is configured to use seeded OTL Self service responsibilities. However, if you use one of your own responsibilities, you must perform the below steps.
1. Login as Sysadmin -> User Management. 2. Go to Roles & Role Inheritance. 3. Query your responsibility and add role Mobile Time Entry to the responsibility.
A sample screenshot shown below.
Using a Custom Menu with Timecards App
Timecards App requires Menu Mobile Time Entry to function. For responsibilities that do not use seeded OTL menu Time and Expenses Self Service Menu, add Mobile Time Entry as a submenu to your custom menu.
EBS Oracle Time and Labor Development
21 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Using a configured layout with Timecards App
Upgrading Self Service timecard Layouts involves the following steps.
1. Analyze the layout to list the View Objects used for Layout attributes. 2. Create Value sets for each View Object. 3. Run OTL: Upgrade Layouts for Mobile Time Entry.
If these steps are not performed for the Self Service timecard Layout attached to the employee, logging in to Timecards App throws the following error.
“Invalid Setup: XXX Timecard Layout” where XXX Timecard Layout is the name of the Self Service timecard layout attached to the
employee. This error may be thrown in other cases where no Self Service timecard layout is attached to the employee explicitly by the Administrator (the default layout may be picked up by the application in this case) and the default Self Service timecard Layout is not upgraded for Timecards App.
Creating Value Sets for View Objects
1. Locate the name of the View Object The name of the view object used for any layout attribute is available in the LDT file for the layout, as qualifier_attribute1 . Examples of Layout attributes Project (from seeded Projects Layout) and Cost Center (from Seeded payroll layout) provided below. Name of the VO shown in Red.
BEGIN HXC_LAYOUT_COMPONENTS "Payroll Timecard Layout - Cost Center" OWNER = "ORACLE12.2.0" COMPONENT_VALUE = "COSTCENTER" REGION_CODE = "HXC_CUI_TIMECARD" REGION_CODE_APP_SHORT_NAME = "HXC" ATTRIBUTE_CODE = "HXC_TIMECARD_COST_CENTER"
ATTRIBUTE_CODE_APP_SHORT_NAME = "HXC" SEQUENCE = "190" COMPONENT_DEFINITION = "CHOICE_LIST" RENDER_TYPE = "WEB" PARENT_COMPONENT = "Payroll Timecard Layout - Day Scope Building blocks for worker timecard matrix" LAST_UPDATE_DATE = "2004/05/24" BEGIN HXC_LAYOUT_COMP_QUALIFIERS "Payroll Timecard Layout - Cost Center" OWNER = "ORACLE12.2.0" QUALIFIER_ATTRIBUTE_CATEGORY = "CHOICE_LIST" QUALIFIER_ATTRIBUTE1 = "CostCenter2VO" QUALIFIER_ATTRIBUTE4 = "N" QUALIFIER_ATTRIBUTE8 = "DisplayValue" QUALIFIER_ATTRIBUTE9 = "Value#NUMBER" QUALIFIER_ATTRIBUTE10 = "oracle.apps.hxc.selfservice.timecard.server.CostCenter2VO" QUALIFIER_ATTRIBUTE11 = "TIMECARD_BIND_END_DATE|TIMECARD_BIND_END_DATE" QUALIFIER_ATTRIBUTE17 = "OraTableCellText" QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y"
QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "Dummy Cost Context" QUALIFIER_ATTRIBUTE27 = "Attribute1" LAST_UPDATE_DATE = "2004/05/24" END HXC_LAYOUT_COMP_QUALIFIERS END HXC_LAYOUT_COMPONENTS BEGIN HXC_LAYOUT_COMPONENTS "Projects Timecard Layout - Project" OWNER = "ORACLE12.2.0" COMPONENT_VALUE = "PROJECT" REGION_CODE = "HXC_CUI_TIMECARD" REGION_CODE_APP_SHORT_NAME = "HXC" ATTRIBUTE_CODE = "HXC_TIMECARD_PROJECT" ATTRIBUTE_CODE_APP_SHORT_NAME = "HXC"
EBS Oracle Time and Labor Development
22 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
SEQUENCE = "210"
COMPONENT_DEFINITION = "LOV" RENDER_TYPE = "WEB" PARENT_COMPONENT = "Projects Timecard Layout - Day Scope Building blocks for worker timecard matrix" LAST_UPDATE_DATE = "2004/05/24" BEGIN HXC_LAYOUT_COMP_QUALIFIERS "Projects Timecard Layout - Project" OWNER = "ORACLE12.2.0" QUALIFIER_ATTRIBUTE_CATEGORY = "LOV" QUALIFIER_ATTRIBUTE1 = "ProjectLOVVO" QUALIFIER_ATTRIBUTE2 = "N" QUALIFIER_ATTRIBUTE3 = "HXC_CUI_PROJECT_LOV" QUALIFIER_ATTRIBUTE4 = "809" QUALIFIER_ATTRIBUTE5 = "12" QUALIFIER_ATTRIBUTE6 = "HxcCuiProjectNumber|PROJECT-DISPLAY|CRITERIA|N| HxcCuiProjectId|PROJECT|RESULT|N| HxcCuiProjectNumber|PROJECT-DISPLAY|RESULT|N" QUALIFIER_ATTRIBUTE8 = "ProjectNumber" QUALIFIER_ATTRIBUTE9 = "ProjectId#NUMBER" QUALIFIER_ATTRIBUTE10 = "oracle.apps.hxc.selfservice.timecard.server.ProjectLOVVO" QUALIFIER_ATTRIBUTE17 = "OraTableCellText" QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y" QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "PROJECTS" QUALIFIER_ATTRIBUTE27 = "Attribute1" QUALIFIER_ATTRIBUTE28 = "PROJECT" LAST_UPDATE_DATE = "2004/05/24"
END HXC_LAYOUT_COMP_QUALIFIERS END HXC_LAYOUT_COMPONENTS
Alternatively you can run the below query to figure out the View objects attached to each of your layout attributes.
select Component_name, VO_NAME, Layout_attribute, Bind_parameters
from hxc_mob_layout_comps_v where layout_id = :layout_id
Snapshot below shows the query results for seeded Projects Layout, returning the values for 3 attributes, Project, Task and Expenditure Type.
2. Analyze the query for in your VO A View Object usually has a query of the following structure
SELECT idcolumn, display_column, additional_columns
FROM XXX_TABLE WHERE filter_conditions
EBS Oracle Time and Labor Development
23 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
The View Object usually selects multiple columns, one of which would be the Id or the Value to be stored in HXC_TIME_ATTRIBUTES table. There would be one display column, which would be the user readable value or name of the attribute. There may be additional columns shown as additional information in the LOV popup pages. The key items we are looking for in this case are the Id or the Value to be stored, and the display value. For ease of reference in this and following sections, let’s call these as Id Column and Display Column. To easily figure out the Id and Display columns for your View Object, you may look at qualifier_attribute9 and qualifier_attribute8 in your layout attribute definition. These are shown in Blue below.
BEGIN HXC_LAYOUT_COMP_QUALIFIERS "Payroll Timecard Layout - Cost Center" OWNER = "ORACLE12.2.0" QUALIFIER_ATTRIBUTE_CATEGORY = "CHOICE_LIST"
QUALIFIER_ATTRIBUTE1 = "CostCenter2VO" QUALIFIER_ATTRIBUTE4 = "N"
QUALIFIER_ATTRIBUTE8 = "DisplayValue" QUALIFIER_ATTRIBUTE9 = "Value#NUMBER" QUALIFIER_ATTRIBUTE10 = "oracle.apps.hxc.selfservice.timecard.server.CostCenter2VO" QUALIFIER_ATTRIBUTE11 = "TIMECARD_BIND_END_DATE|TIMECARD_BIND_END_DATE" QUALIFIER_ATTRIBUTE17 = "OraTableCellText" QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y" QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "Dummy Cost Context" QUALIFIER_ATTRIBUTE27 = "Attribute1" LAST_UPDATE_DATE = "2004/05/24" END HXC_LAYOUT_COMP_QUALIFIERS BEGIN HXC_LAYOUT_COMP_QUALIFIERS "Projects Timecard Layout - Project" OWNER = "ORACLE12.2.0" QUALIFIER_ATTRIBUTE_CATEGORY = "LOV" QUALIFIER_ATTRIBUTE1 = "ProjectLOVVO" QUALIFIER_ATTRIBUTE2 = "N"
QUALIFIER_ATTRIBUTE3 = "HXC_CUI_PROJECT_LOV" QUALIFIER_ATTRIBUTE4 = "809" QUALIFIER_ATTRIBUTE5 = "12" QUALIFIER_ATTRIBUTE6 = "HxcCuiProjectNumber|PROJECT-DISPLAY|CRITERIA|N| HxcCuiProjectId|PROJECT|RESULT|N| HxcCuiProjectNumber|PROJECT-DISPLAY|RESULT|N"
QUALIFIER_ATTRIBUTE8 = "ProjectNumber" QUALIFIER_ATTRIBUTE9 = "ProjectId#NUMBER" QUALIFIER_ATTRIBUTE10 = "oracle.apps.hxc.selfservice.timecard.server.ProjectLOVVO" QUALIFIER_ATTRIBUTE17 = "OraTableCellText" QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y" QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "PROJECTS" QUALIFIER_ATTRIBUTE27 = "Attribute1" QUALIFIER_ATTRIBUTE28 = "PROJECT" LAST_UPDATE_DATE = "2004/05/24"
END HXC_LAYOUT_COMP_QUALIFIERS END HXC_LAYOUT_COMPONENTS
For ProjectLOVVO above, used for Project, VO query from ProjectLOVVO.xml is
EBS Oracle Time and Labor Development
24 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
select project_number projectnumber, project_name projectname, project_details projectdetails, project_id projectid, start_date, completion_date, carrying_out_organization_id from pa_online_projects_v
From the LDT file definition, the Id column is ProjectId, which in the above query is a Column Alias for project_id. The display column is ProjectNumber, which in the above query is a column Alias for project_number.
3. Once we have figured out the required values for Id column and Display column, we may proceed to creation of Value Sets. Steps below shows creation of Value set for View Object ProjectLOVVO.xml for the seeded projects layout.
From Application Developer -> Application -> Validation -> Sets34
3 The Value set created should always be of Validation Type Table. Value sets of other validation types are not
supported in this release. 4 The Value set example shown here follows a naming convention of VO_NAME_VS. This is followed to make
the Value sets created Well Defined while upgrading the layouts using OTL: Upgrade layouts for Mobile Time Entry. Administrators may follow any naming convention.
EBS Oracle Time and Labor Development
25 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Following section explains the values to use for fields in the Validation Information page and
the rationale in arriving at the right values. Table Name: The table in the FROM clause of the SELECT query in the View object is used
here. If there are multiple tables, a comma separated list may be used here, shown in examples below. If there are multiple tables, using Table aliases may be required. You must ensure that the Value set is tested successfully. For cases where there are more table names than what can be put in this field, a View may be created out of the View Object’s query and used here. An example of this case is ExpTypeElementLOVVO_VS, delivered for the Seeded Projects Layout.
Value: This field represents the Display column from the View Object’s query. It must be noted here that only one column is allowed to be a display column, and hence multiple additional columns are never displayed in Timecard App’s LOV searches. However, a concatenated list of columns is allowed to be used in this field. The example above shows “project_number” as the Display column. It may be modified to “project_number||’-‘||project_name” to show Project Name along with Project Number.
Meaning: This field must be left blank. Id: This field represents the Id column of the layout attribute. The example above shows
“project_id” column. No concatenated values are allowed in this field. It must be noted that the column mentioned in this field is the value that actually gets stored in HXC_TIME_ATTRIBUTES and must be able to uniquely identify a value selected. In most cases, the column used here should be the primary key.
Where/Order By: Any filter conditions used in the View Objects’ query may be inserted here. Since our example above does not have any filter conditions in the View Objects, Expenditure Type LOV’s Value set is provided below as an example. Any bind variable or any other condition must be used here.
EBS Oracle Time and Labor Development
26 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Note the WHERE/Order By field that uses bind variables :TIMECARD_BIND_START_DATE and
:TIMECARD_BIND_END_DATE amongst other conditions. Additional Columns: This field is to be used to add Hidden Attributes to the Value sets. For
more explanation of how to use this field, refer to the following section.
Creating Value Sets for Hidden Attributes
Definition of Hidden Attributes
Definition of Hidden attributes can be found in the LDT file used to create the configured layouts. Hidden Attributes, conceptually are attributes which are not selected explicitly by the user nor are they displayed on the timecard. A sample hidden field definition is provided below. Example used is seeded Projects layout with System Linkage Function as hidden field.
BEGIN HXC_LAYOUT_COMPONENTS "Projects Timecard Layout - System Linkage Function" OWNER = "ORACLE12.2.0"
COMPONENT_VALUE = "SYSTEMLINKAGEFUNCTION" SEQUENCE = "200" COMPONENT_DEFINITION = "HIDDEN_FIELD" RENDER_TYPE = "WEB" PARENT_COMPONENT = "Projects Timecard Layout - Day Scope Building blocks for worker timecard matrix" LAST_UPDATE_DATE = "2004/05/24" BEGIN HXC_LAYOUT_COMP_QUALIFIERS "Projects Timecard Layout - System Linkage Function" OWNER = "ORACLE12.2.0" QUALIFIER_ATTRIBUTE_CATEGORY = "HIDDEN_FIELD" QUALIFIER_ATTRIBUTE18 = "EXCLUDE" QUALIFIER_ATTRIBUTE19 = "|CSV|"
EBS Oracle Time and Labor Development
27 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y" QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE23 = "FORM" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "PROJECTS" QUALIFIER_ATTRIBUTE27 = "Attribute5" QUALIFIER_ATTRIBUTE28 = "SYSLINKFUNC" LAST_UPDATE_DATE = "2004/05/24" END HXC_LAYOUT_COMP_QUALIFIERS END HXC_LAYOUT_COMPONENTS
Qualifier_attribute28 is the key here, for populating hidden field. The value provided in
qualifier_attribute28 here would be attached to another layout attribute, which will be picked up by the layout when time is entered. For the above sample hidden field, the value in qualifier_attribute28 is “SYSLINKFUNC”. This value is picked up from another layout component, Expenditure type. Layout Component qualifiers would be as shown below.
BEGIN HXC_LAYOUT_COMPONENTS "Projects Timecard Layout - Expenditure Type" OWNER = "ORACLE12.2.0" COMPONENT_VALUE = "EXPENDITURETYPE" REGION_CODE = "HXC_CUI_TIMECARD" REGION_CODE_APP_SHORT_NAME = "HXC" ATTRIBUTE_CODE = "HXC_TIMECARD_EXPTYPE" ATTRIBUTE_CODE_APP_SHORT_NAME = "HXC" SEQUENCE = "230" COMPONENT_DEFINITION = "LOV" RENDER_TYPE = "WEB" PARENT_COMPONENT = "Projects Timecard Layout - Day Scope Building blocks for worker timecard matrix" LAST_UPDATE_DATE = "2004/05/24" BEGIN HXC_LAYOUT_COMP_QUALIFIERS "Projects Timecard Layout - Expenditure Type" OWNER = "ORACLE12.2.0" QUALIFIER_ATTRIBUTE_CATEGORY = "LOV" QUALIFIER_ATTRIBUTE1 = "ExpenditureType2LOVVO"
QUALIFIER_ATTRIBUTE2 = "N" QUALIFIER_ATTRIBUTE3 = "HXC_CUI_EXPTYPE2_LOV" QUALIFIER_ATTRIBUTE4 = "809" QUALIFIER_ATTRIBUTE5 = "12" QUALIFIER_ATTRIBUTE6 = "HxcCuiExptypeDispColumn|EXPTYPE-
DISPLAY|CRITERIA|N|HxcCuiExptypeExpType|EXPTYPE|RESULT|N| HxcCuiExptypeDispColumn|EXPTYPE-
DISPLAY|RESULT|N|HxcCuiExptypeSysLinkFunc|SYSLINKFUNC|RESULT|N" QUALIFIER_ATTRIBUTE7 = "SYSLINKFUNC|SystemLinkageFunction" QUALIFIER_ATTRIBUTE8 = "DisplayColumn" QUALIFIER_ATTRIBUTE9 = "ExpenditureType" QUALIFIER_ATTRIBUTE10 = "oracle.apps.hxc.selfservice.timecard.server.ExpenditureType2LOVVO" QUALIFIER_ATTRIBUTE11 = "TIMECARD_BIND_END_DATE|TIMECARD_BIND_END_DATE|TIMECARD_BIND_START_DATE| TIMECARD_BIND_END_DATE|TIMECARD_BIND_END_DATE|TIMECARD_BIND_START_DATE" QUALIFIER_ATTRIBUTE17 = "OraTableCellText" QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y" QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "PROJECTS" QUALIFIER_ATTRIBUTE27 = "Attribute3" QUALIFIER_ATTRIBUTE28 = "EXPTYPE" LAST_UPDATE_DATE = "2004/05/24" END HXC_LAYOUT_COMP_QUALIFIERS END HXC_LAYOUT_COMPONENTS
Qualifier_attribute28 of Hidden field is linked with qualifier_attribute7 of the component that picks up the value for the hidden field ( in this case, SystemLinkageFunction).
EBS Oracle Time and Labor Development
28 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Another method to get the value to be used for Hidden field in the value set to be created is to run the query as provided below*.
select *
from hxc_mob_hidden_field_details_v
where layout_id = :1;
The above query returns a column called “column_name”. Value in this column is the key in specifying the hidden attributes in the value set to be prepared.
If there are multiple hidden fields, the above query would return more than one rows.
All of the 3 hidden attributes should have corresponding columns in the Value set to be defined.
Steps
Analyze the query in the VO used for hidden attribute. The VO used for another layout attribute should return the value to be used for the hidden attribute(s) too. The format of the query in the VO typically would be like this.
SELECT idcolumn, displaycolumn, additional_info, more_additional_info, hidden_attribute1, hidden_attribute2, hidden_attribute3 FROM VO_TABLE WHERE filter_conditions
The value set would be created for the VO with the following specifications in the additional information page.
Table Application: Time and Labor Engine Table Name: VO_TABLE Value: displaycolumn ID: idcolumn WHERE/Order By: filter_conditions
EBS Oracle Time and Labor Development
29 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
In addition to these values, the Additional Columns field should be populated with columns for hidden attributes, with value returned by column_name from query above as aliases.
Additional Columns: hidden_attribute1 column_name1, hidden_attribute2 column_name2,hidden_attribute3 column_name3
Example
Let’s take a scenario example of an LOV with 3 hidden attributes.
Analyze the definition of the attribute
Hidden Attributes’ definition as per LDT file BEGIN HXC_LAYOUT_COMPONENTS "OTL Demo Exception Timecard Layout - Location LOV State" OWNER = "ORACLE12.1.3"
COMPONENT_VALUE = "STATE" SEQUENCE = "220" COMPONENT_DEFINITION = "HIDDEN_FIELD" RENDER_TYPE = "WEB" PARENT_COMPONENT = "OTL Demo Exception Timecard Layout - Day Scope Building blocks for worker timecard
matrix" LAST_UPDATE_DATE = "2004/05/24" BEGIN HXC_LAYOUT_COMP_QUALIFIERS "OTL Demo Exception Timecard Layout - Location LOV State" OWNER = "ANONYMOUS" QUALIFIER_ATTRIBUTE_CATEGORY = "HIDDEN_FIELD" QUALIFIER_ATTRIBUTE18 = "EXCLUDE" QUALIFIER_ATTRIBUTE19 = "|CSV|" QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y" QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE23 = "FORM" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "Dummy Element Context"
QUALIFIER_ATTRIBUTE27 = "Attribute27" QUALIFIER_ATTRIBUTE28 = "STATE" LAST_UPDATE_DATE = "2013/12/30" END HXC_LAYOUT_COMP_QUALIFIERS END HXC_LAYOUT_COMPONENTS BEGIN HXC_LAYOUT_COMPONENTS "OTL Demo Exception Timecard Layout - Location LOV County" OWNER = "ORACLE12.1.3" COMPONENT_VALUE = "COUNTY" SEQUENCE = "230" COMPONENT_DEFINITION = "HIDDEN_FIELD" RENDER_TYPE = "WEB" PARENT_COMPONENT = "OTL Demo Exception Timecard Layout - Day Scope Building blocks for worker timecard
matrix" LAST_UPDATE_DATE = "2004/05/24" BEGIN HXC_LAYOUT_COMP_QUALIFIERS "OTL Demo Exception Timecard Layout - Location LOV County" OWNER = "ANONYMOUS"
QUALIFIER_ATTRIBUTE_CATEGORY = "HIDDEN_FIELD" QUALIFIER_ATTRIBUTE18 = "EXCLUDE" QUALIFIER_ATTRIBUTE19 = "|CSV|" QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y" QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE23 = "FORM" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "Dummy Element Context" QUALIFIER_ATTRIBUTE27 = "Attribute28" QUALIFIER_ATTRIBUTE28 = "COUNTY" LAST_UPDATE_DATE = "2013/12/30" END HXC_LAYOUT_COMP_QUALIFIERS END HXC_LAYOUT_COMPONENTS BEGIN HXC_LAYOUT_COMPONENTS
EBS Oracle Time and Labor Development
30 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
"OTL Demo Exception Timecard Layout - Location LOV City" OWNER = "ORACLE12.1.3" COMPONENT_VALUE = "CITY" SEQUENCE = "240" COMPONENT_DEFINITION = "HIDDEN_FIELD" RENDER_TYPE = "WEB" PARENT_COMPONENT = "OTL Demo Exception Timecard Layout - Day Scope Building blocks for worker timecard
matrix" LAST_UPDATE_DATE = "2004/05/24" BEGIN HXC_LAYOUT_COMP_QUALIFIERS "OTL Demo Exception Timecard Layout - Location LOV City" OWNER = "ANONYMOUS" QUALIFIER_ATTRIBUTE_CATEGORY = "HIDDEN_FIELD" QUALIFIER_ATTRIBUTE18 = "EXCLUDE" QUALIFIER_ATTRIBUTE19 = "|CSV|" QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y" QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE23 = "FORM" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "Dummy Element Context" QUALIFIER_ATTRIBUTE27 = "Attribute29" QUALIFIER_ATTRIBUTE28 = "CITY" LAST_UPDATE_DATE = "2013/12/30" END HXC_LAYOUT_COMP_QUALIFIERS END HXC_LAYOUT_COMPONENTS
LOV that picks out the hidden attributes defined like below.
BEGIN HXC_LAYOUT_COMPONENTS "OTL Demo Exception Timecard Layout - Location LOV" OWNER = "ORACLE12.1.3" COMPONENT_VALUE = "LOC" REGION_CODE = "HXC_CUI_TIMECARD" REGION_CODE_APP_SHORT_NAME = "HXC" ATTRIBUTE_CODE = "HXC_CUI_LOCATION_LABEL" ATTRIBUTE_CODE_APP_SHORT_NAME = "HXC" SEQUENCE = "250" COMPONENT_DEFINITION = "LOV" RENDER_TYPE = "WEB" PARENT_COMPONENT =
"OTL Demo Exception Timecard Layout - Day Scope Building blocks for worker timecard matrix" LAST_UPDATE_DATE = "2006/09/29" BEGIN HXC_LAYOUT_COMP_QUALIFIERS "OTL Demo Exception Timecard Layout - Location LOV" OWNER = "ANONYMOUS" QUALIFIER_ATTRIBUTE_CATEGORY = "LOV" QUALIFIER_ATTRIBUTE1 = "CityListVO" QUALIFIER_ATTRIBUTE2 = "N" QUALIFIER_ATTRIBUTE3 = "HXC_CUI_LOCATION_LOV" QUALIFIER_ATTRIBUTE4 = "809" QUALIFIER_ATTRIBUTE5 = "12" QUALIFIER_ATTRIBUTE6 = "LocationId|LOCDISPLAY|CRITERIA|N| Zip|LOC|RESULT|N|LocationId| LOC-DISPLAY|RESULT|N| County|COUNTY|RESULT|N| City|CITY|RESULT|N| State|STATE|RESULT|N" QUALIFIER_ATTRIBUTE7 = "STATE|State|COUNTY|County|CITY|City"
QUALIFIER_ATTRIBUTE8 = "Locationid" QUALIFIER_ATTRIBUTE9 = "Zip#STRING" QUALIFIER_ATTRIBUTE10 = "oracle.apps.hxc.selfservice.configui.us.server.CityListVO" QUALIFIER_ATTRIBUTE14 = "State|STATE|Y# County|COUNTY|Y# City|CITY|Y" QUALIFIER_ATTRIBUTE15 = "upper(State) = upper(::State)# upper(County) = upper(::County)# upper(City) = upper(::City)" QUALIFIER_ATTRIBUTE17 = "OraTableCellText" QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y"
EBS Oracle Time and Labor Development
31 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "Dummy Element Context" QUALIFIER_ATTRIBUTE27 = "Attribute30" QUALIFIER_ATTRIBUTE28 = "LOC" LAST_UPDATE_DATE = "2013/12/30" END HXC_LAYOUT_COMP_QUALIFIERS
END HXC_LAYOUT_COMPONENTS
As we see above, the LOV uses a VO named CityListVO. Query from the VO is as follows select pcy.CITY_NAME||','|| pst.STATE_NAME||','|| pct.COUNTY_NAME||' county,'|| pzc.ZIP_START LocationId, pst.STATE_ABBREV StateId, pst.STATE_NAME State, pct.COUNTY_NAME County, pcy.CITY_NAME City, pzc.ZIP_START Zip, pzc.ZIP_END ZipEnd from pay_us_states pst, pay_us_counties pct, pay_us_city_names pcy, PAY_US_ZIP_CODES pzc where (pst.state_code < 52 or pst.state_code = 72) and pcy.STATE_CODE = pst.STATE_CODE and pcy.COUNTY_CODE = pct.COUNTY_CODE and pct.STATE_CODE = pst.STATE_CODE and pcy.CITY_CODE = pzc.CITY_CODE and pcy.STATE_CODE = pzc.STATE_CODE and pcy.COUNTY_CODE = pzc.COUNTY_CODE order by pcy.CITY_NAME, pst.STATE_NAME, pct.COUNTY_NAME, pzc.ZIP_START
Let’s run the query to get Hidden Field details.
We have 3 hidden_attributes coming out from the above LOV component. For this case, let’s see the steps to create the value set. Following are the specifications from the VO and Layout comp qualifiers.
Display Column: pcy.CITY_NAME||','||pst.STATE_NAME||','||pct.COUNTY_NAME||'county,'||pzc.ZIP_START LocationId
(As per qualifier_attribute8, which says LocationId. LocationId is an alias for the above concatenated column list in the VO query. This is the text shown to the user in a Saved timecard)
EBS Oracle Time and Labor Development
32 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Id Column: pzc.ZIP_START Zip ( As per VO query)
Zip#String ( from qualifier_attribute9; meaning column name from query is Zip, which in this case is a Column Alias for pzc.zip_start and format is String)
Create Value set for Hidden Attributes
1. Create value set with validation type Table
2. Open Edit Information and key in values
EBS Oracle Time and Labor Development
33 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Note the following
Table Name field has all the tables in the VO query.
Value field has the column corresponding to Display Column of the LOV component.
Id field has the column corresponding to Id Column of the LOV component.
Where/Order By field has the complete WHERE clause of the VO query Now we have created the value set for the layout attribute Location LOV. Now let’s add the
columns for hidden attributes. These need to be added to Additional Columns. There are 3 hidden columns with these column names: State, County, City. These columns correspond to the following columns as per the VO query: pst.STATE_NAME pct.COUNTY_NAME pcy.CITY_NAME
Additional column to be created as a combination of VO select columns and column aliases as recognized by the layout
pst.state_name State, pct.county_name County, pcy.city_name City
3. Add information to the Value set definition as below.
EBS Oracle Time and Labor Development
34 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Value set is now ready to use.
Dependent LOVs
By definition, a dependent LOV is based on one or more previously selected Layout attributes. Such LOVs need special steps for use in Mobile.
To know if your layout has a dependent attribute and to gather details about the dependent LOVs follow either of these methods.
Method 1: Using Layout LDT files.
Key values to look for in the Layout LDT file, against the Layout component definition is Qualifier_attribute14 and qualifier_attribute15. Shown below is a sample from Seeded Projects Layout.
BEGIN HXC_LAYOUT_COMPONENTS "Projects Timecard Layout - Project" OWNER = "ORACLE12.2.0" COMPONENT_VALUE = "PROJECT" REGION_CODE = "HXC_CUI_TIMECARD" REGION_CODE_APP_SHORT_NAME = "HXC" ATTRIBUTE_CODE = "HXC_TIMECARD_PROJECT" ATTRIBUTE_CODE_APP_SHORT_NAME = "HXC" SEQUENCE = "210" COMPONENT_DEFINITION = "LOV" RENDER_TYPE = "WEB" PARENT_COMPONENT = "Projects Timecard Layout - Day Scope Building blocks for worker timecard matrix" LAST_UPDATE_DATE = "2004/05/24" BEGIN HXC_LAYOUT_COMP_QUALIFIERS "Projects Timecard Layout - Project" OWNER = "ORACLE12.2.0" QUALIFIER_ATTRIBUTE_CATEGORY = "LOV" QUALIFIER_ATTRIBUTE1 = "ProjectLOVVO" QUALIFIER_ATTRIBUTE2 = "N" QUALIFIER_ATTRIBUTE3 = "HXC_CUI_PROJECT_LOV"
EBS Oracle Time and Labor Development
35 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
QUALIFIER_ATTRIBUTE4 = "809" QUALIFIER_ATTRIBUTE5 = "12" QUALIFIER_ATTRIBUTE6 = "HxcCuiProjectNumber|PROJECT-DISPLAY|CRITERIA|N| HxcCuiProjectId|PROJECT|RESULT|N| HxcCuiProjectNumber|PROJECT-DISPLAY|RESULT|N" QUALIFIER_ATTRIBUTE8 = "ProjectNumber" QUALIFIER_ATTRIBUTE9 = "ProjectId#NUMBER" QUALIFIER_ATTRIBUTE10 = "oracle.apps.hxc.selfservice.timecard.server.ProjectLOVVO" QUALIFIER_ATTRIBUTE17 = "OraTableCellText" QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y" QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "PROJECTS" QUALIFIER_ATTRIBUTE27 = "Attribute1" QUALIFIER_ATTRIBUTE28 = "PROJECT" LAST_UPDATE_DATE = "2004/05/24" END HXC_LAYOUT_COMP_QUALIFIERS END HXC_LAYOUT_COMPONENTS BEGIN HXC_LAYOUT_COMPONENTS "Projects Timecard Layout - Task" OWNER = "ORACLE12.2.0" COMPONENT_VALUE = "TASK" REGION_CODE = "HXC_CUI_TIMECARD" REGION_CODE_APP_SHORT_NAME = "HXC" ATTRIBUTE_CODE = "HXC_TIMECARD_TASK" ATTRIBUTE_CODE_APP_SHORT_NAME = "HXC" SEQUENCE = "220" COMPONENT_DEFINITION = "LOV" RENDER_TYPE = "WEB" PARENT_COMPONENT = "Projects Timecard Layout - Day Scope Building blocks for worker timecard matrix" LAST_UPDATE_DATE = "2004/05/24" BEGIN HXC_LAYOUT_COMP_PROMPTS "HxcCuiTaskProjectId" "AK_PROMPT" OWNER = "ORACLE12.2.0" REGION_CODE = "HXC_CUI_TIMECARD" REGION_APP_SHORT_NAME = "HXC" ATTRIBUTE_CODE = "HXC_TIMECARD_PROJECT" ATTRIBUTE_APP_SHORT_NAME = "HXC" LAST_UPDATE_DATE = "2004/05/24" END HXC_LAYOUT_COMP_PROMPTS BEGIN HXC_LAYOUT_COMP_QUALIFIERS "Projects Timecard Layout - Task" OWNER = "ORACLE12.2.0" QUALIFIER_ATTRIBUTE_CATEGORY = "LOV" QUALIFIER_ATTRIBUTE1 = "TaskLOVVO" QUALIFIER_ATTRIBUTE2 = "N" QUALIFIER_ATTRIBUTE3 = "HXC_CUI_TASK_LOV" QUALIFIER_ATTRIBUTE4 = "809" QUALIFIER_ATTRIBUTE5 = "12" QUALIFIER_ATTRIBUTE6 = "HxcCuiTaskNumber|TASK-DISPLAY|CRITERIA|N| HxcCuiTaskProjectId|PROJECT|PASSIVE_CRITERIA|Y| HxcCuiTaskId|TASK|RESULT|N|HxcCuiTaskNumber|TASK-DISPLAY|RESULT|N" QUALIFIER_ATTRIBUTE8 = "TaskNumber" QUALIFIER_ATTRIBUTE9 = "TaskId#NUMBER" QUALIFIER_ATTRIBUTE10 = "oracle.apps.hxc.selfservice.timecard.server.TaskLOVVO" QUALIFIER_ATTRIBUTE14 = "HxcCuiTaskProjectId|PROJECT|Y#HxcCuiTaskProjectNumber|PROJECT-DISPLAY|Y" QUALIFIER_ATTRIBUTE15 = "project_id = ::HxcCuiTaskProjectId#upper(project_number) =
upper(::HxcCuiTaskProjectNumber)" QUALIFIER_ATTRIBUTE17 = "OraTableCellText" QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y" QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "PROJECTS" QUALIFIER_ATTRIBUTE27 = "Attribute2" QUALIFIER_ATTRIBUTE28 = "TASK" LAST_UPDATE_DATE = "2004/05/24" END HXC_LAYOUT_COMP_QUALIFIERS END HXC_LAYOUT_COMPONENTS
EBS Oracle Time and Labor Development
36 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
The above excerpt from Projects layout LDT, shows the definition of Project and Task components’ definition. In the Task component definition, qualifier_attribute14 and qualifier_attribute15 shows the details about the dependent component(shown in Red). Qualifier_attribute15 refers the WHERE clause to be attached to the query to bind the value from previous attribute where as qualifier_attribute14 mentions which layout attribute it has to refer to. PROJECT, shown in Green above points to the Project component’s qualifier_attribute28 in the previous definition (also shown in Green). Using this method, it is now clear that the LOV for Task is dependent on Project LOV’s selected value. Task LOV is the second layout attribute displayed on the Self Service layout and it is dependent on the project, which is the first attribute.
To implement this feature in mobile application, the Value set created should have an additional WHERE clause referring to the previous attribute. Qualifier_attribute15 has the WHERE clause to be used.
So, we proceed on to define the WHERE clause as “project_id = :attribute1” Below screenshot shows the value set created for Task LOV.
Method 2:
This method uses a query to point out dependent attributes in a given layout. select deps.component_name dependent_component_name, deps.layout_attribute dependent_attribute_num, comps.component_name source_component_name, comps.layout_attribute source_attribute_num, REGEXP_REPLACE( REGEXP_SUBSTR( lcq2.qualifier_attribute15,'[^#]+',1, RANK() OVER (partition by deps.layout_component_id
EBS Oracle Time and Labor Development
37 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
order by comps.layout_attribute ) ),'(::).*',':'||lower(comps.layout_attribute) ) WHERE_CLAUSE from hxc_mob_layout_comps_v comps, hxc_layout_comp_qualifiers lcq, hxc_layout_comp_qualifiers lcq2, (select layout_id, layout_component_id, component_name, parent_component_id, layout_attribute, VO_name, RTRIM(LTRIM(field,'|'),'|') field from hxc_mob_layout_comps_v unpivot (field for dep_column in (dependent_1, dependent_2, dependent_3, dependent_4) ) ) deps WHERE comps.parent_component_id = deps.parent_component_id AND lcq.layout_component_id = comps.layout_component_id AND lcq.qualifier_attribute28 = deps.field AND deps.layout_id = :LAYOUT AND lcq2.layout_component_id = deps.layout_component_id
Use layout_id as the bind variable. Let’s see the query outputs for a few layouts.
Projects Layout
First and second columns give the detail of the layout attribute which is dependent on a previous
one. Third and fourth columns give details of the layout component which the former component is dependent on. And WHERE Clause to be added to the Value set to be created is provided as the last column. The above query result means that the Task component is dependent on Project component and WHERE clause to be added is “project_id = :attribute1”
EBS Oracle Time and Labor Development
38 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Purchasing Layout
The following example demonstrates creation of Dependent LOV components dependent on more than one attributes. For this example, we are considering seeded Purchasing Layout. 5
Above query result shows two distinct components, Purchase Order Line and Price Type as
dependent attributes. They are dependent on one and two previous attributes respectively. Purchase order line is dependent on Purchase Order Number and WHERE clause to be added is
“POHeaderid = :attribute1”. Price Type is dependent on Purchase Order Number and Purchase Order Line( Two rows returned in
the query). Two conditions have to be added in the WHERE clause: “POHeaderId = :attribute1” and “POLineId = :attribute2”. They may be joined as “POHeaderId = :attribute1 AND POLineId = :attribute2”.
It may be noted here that the WHERE clause derived by the query above uses column aliases. Creation of the value set may require aliases to be replaced by actual column names. Eg. PO_HEADER_ID instead of POHeaderId.
Example
Sample Value Set creation with more than one dependent attributes Layout: Seeded Purchasing Layout6 Component: Price Type Excerpt from LDT file showing definition of Purchase Order Number, Line and Price Type.
5 Purchasing layout is used only as a sample layout for this case. This does not confirm all features of the
layout is allowed to be used in Timecards App. 6 Purchasing Layouts are not certified to work with Timecards App. Example shown here is for illustrative
purpose.
EBS Oracle Time and Labor Development
39 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
BEGIN HXC_LAYOUT_COMPONENTS "Purchasing Timecard Layout - Purchase Order Number" OWNER = "ORACLE12.2.0" COMPONENT_VALUE = "PONUMBER" REGION_CODE = "PO_CUI_TIMECARD" REGION_CODE_APP_SHORT_NAME = "PO" ATTRIBUTE_CODE = "PO_TIMECARD_PO_NUMBER" ATTRIBUTE_CODE_APP_SHORT_NAME = "PO" SEQUENCE = "220" COMPONENT_DEFINITION = "LOV" RENDER_TYPE = "WEB" PARENT_COMPONENT = "Purchasing Timecard Layout - Day Scope Building blocks for worker timecard matrix" LAST_UPDATE_DATE = "2004/05/13" BEGIN HXC_LAYOUT_COMP_QUALIFIERS "Purchasing Timecard Layout - Purchase Order Number" OWNER = "ORACLE12.2.0" QUALIFIER_ATTRIBUTE_CATEGORY = "LOV" QUALIFIER_ATTRIBUTE1 = "PurchaseOrderNumberLOVVO" QUALIFIER_ATTRIBUTE2 = "N" QUALIFIER_ATTRIBUTE3 = "PO_CUI_PO_NUMBER_LOV" QUALIFIER_ATTRIBUTE4 = "201" QUALIFIER_ATTRIBUTE5 = "5" QUALIFIER_ATTRIBUTE6 = "POCuiPONumber|PO_NUMBER-
DISPLAY|CRITERIA|N|POCuiPOHeaderId|PO_NUMBER|RESULT|N|POCuiPONumber|PO_NUMBER-DISPLAY|RESULT|N" QUALIFIER_ATTRIBUTE8 = "PONumber" QUALIFIER_ATTRIBUTE9 = "POHeaderId#NUMBER" QUALIFIER_ATTRIBUTE10 = "oracle.apps.po.selfservice.timecard.server.PurchaseOrderNumberLOVVO" QUALIFIER_ATTRIBUTE11 = "TIMECARD_BIND_START_DATE|TIMECARD_BIND_END_DATE|RESOURCE_IDENTIFIER_ID| TIMECARD_BIND_START_DATE|TIMECARD_BIND_END_DATE|TIMECARD_ID|TIMECARD_OVN" QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y" QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "PURCHASING" QUALIFIER_ATTRIBUTE27 = "Attribute8" QUALIFIER_ATTRIBUTE28 = "PO_NUMBER" LAST_UPDATE_DATE = "2004/05/13" END HXC_LAYOUT_COMP_QUALIFIERS END HXC_LAYOUT_COMPONENTS BEGIN HXC_LAYOUT_COMPONENTS "Purchasing Timecard Layout - Purchase Order Line" OWNER = "ORACLE12.2.0" COMPONENT_VALUE = "POLINE" REGION_CODE = "PO_CUI_TIMECARD" REGION_CODE_APP_SHORT_NAME = "PO" ATTRIBUTE_CODE = "PO_TIMECARD_PO_LINE" ATTRIBUTE_CODE_APP_SHORT_NAME = "PO" SEQUENCE = "230" COMPONENT_DEFINITION = "LOV" RENDER_TYPE = "WEB" PARENT_COMPONENT = "Purchasing Timecard Layout - Day Scope Building blocks for worker timecard matrix" LAST_UPDATE_DATE = "2004/05/13" BEGIN HXC_LAYOUT_COMP_QUALIFIERS "Purchasing Timecard Layout - Purchase Order Line" OWNER = "ORACLE12.2.0" QUALIFIER_ATTRIBUTE_CATEGORY = "LOV" QUALIFIER_ATTRIBUTE1 = "PurchaseOrderLineLOVVO" QUALIFIER_ATTRIBUTE2 = "N" QUALIFIER_ATTRIBUTE3 = "PO_CUI_PO_LINE_LOV" QUALIFIER_ATTRIBUTE4 = "201" QUALIFIER_ATTRIBUTE5 = "5" QUALIFIER_ATTRIBUTE6 = "POCuiPOLine|PO_LINE- DISPLAY|CRITERIA|N|POLineCuiPOHeaderId|PO_NUMBER|PASSIVE_CRITERIA|N| POCuiPOLineId|PO_LINE|RESULT|N|POCuiPOLine| PO_LINE-DISPLAY|RESULT|N" QUALIFIER_ATTRIBUTE8 = "POLine" QUALIFIER_ATTRIBUTE9 = "POLineId#NUMBER" QUALIFIER_ATTRIBUTE10 = "oracle.apps.po.selfservice.timecard.server.PurchaseOrderLineLOVVO" QUALIFIER_ATTRIBUTE11 = "TIMECARD_BIND_START_DATE|TIMECARD_BIND_END_DATE|RESOURCE_IDENTIFIER_ID| TIMECARD_BIND_START_DATE|TIMECARD_BIND_END_DATE|TIMECARD_ID|TIMECARD_OVN"
EBS Oracle Time and Labor Development
40 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
QUALIFIER_ATTRIBUTE14 = "POLineCuiPOHeaderId|PO_NUMBER|Y" QUALIFIER_ATTRIBUTE15 = "POHeaderId = ::POLineCuiPOHeaderId" QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y" QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "PURCHASING" QUALIFIER_ATTRIBUTE27 = "Attribute2" QUALIFIER_ATTRIBUTE28 = "PO_LINE" LAST_UPDATE_DATE = "2004/05/13" END HXC_LAYOUT_COMP_QUALIFIERS END HXC_LAYOUT_COMPONENTS BEGIN HXC_LAYOUT_COMPONENTS "Purchasing Timecard Layout - Price Type" OWNER = "ORACLE12.2.0" COMPONENT_VALUE = "PRICETYPE" REGION_CODE = "PO_CUI_TIMECARD" REGION_CODE_APP_SHORT_NAME = "PO" ATTRIBUTE_CODE = "PO_TIMECARD_PRICE_TYPE" ATTRIBUTE_CODE_APP_SHORT_NAME = "PO" SEQUENCE = "240" COMPONENT_DEFINITION = "LOV" RENDER_TYPE = "WEB" PARENT_COMPONENT = "Purchasing Timecard Layout - Day Scope Building blocks for worker timecard matrix" LAST_UPDATE_DATE = "2004/05/13" BEGIN HXC_LAYOUT_COMP_QUALIFIERS "Purchasing Timecard Layout - Price Type" OWNER = "ORACLE12.2.0" QUALIFIER_ATTRIBUTE_CATEGORY = "LOV" QUALIFIER_ATTRIBUTE1 = "PurchaseOrderPriceTypeLOVVO" QUALIFIER_ATTRIBUTE2 = "N" QUALIFIER_ATTRIBUTE3 = "PO_CUI_PRICE_TYPE_LOV" QUALIFIER_ATTRIBUTE4 = "201" QUALIFIER_ATTRIBUTE5 = "10" QUALIFIER_ATTRIBUTE6 = "POCuiPriceTypeDsp|PRICE_TYPE -DISPLAY|CRITERIA|N|POCuiPOHeaderId|PO_NUMBER|PASSIVE_CRITERIA|N| POCuiPOLineId|PO_LINE|PASSIVE_CRITERIA|N| POCuiPriceType|PRICE_TYPE|RESULT|N| POCuiPriceTypeDsp|PRICE_TYPE-DISPLAY|RESULT|N" QUALIFIER_ATTRIBUTE8 = "POPriceTypeDisplay" QUALIFIER_ATTRIBUTE9 = "POPriceType" QUALIFIER_ATTRIBUTE10 = "oracle.apps.po.selfservice.timecard.server.PurchaseOrderPriceTypeLOVVO" QUALIFIER_ATTRIBUTE11 = "TIMECARD_ID|TIMECARD_OVN" QUALIFIER_ATTRIBUTE14 = "POCuiPOHeaderId|PO_NUMBER|Y#POCuiPOLineId|PO_LINE|Y" QUALIFIER_ATTRIBUTE15 = "POHeaderId = ::POCuiPOHeaderId#POLineId = ::POCuiPOLineId" QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y" QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "PURCHASING" QUALIFIER_ATTRIBUTE27 = "Attribute3" QUALIFIER_ATTRIBUTE28 = "PRICE_TYPE" LAST_UPDATE_DATE = "2004/05/13" END HXC_LAYOUT_COMP_QUALIFIERS END HXC_LAYOUT_COMPONENTS
Qualifier_attribute1 mentions PurchaseOrderPriceTypeLOVVO as the VO attached to this layout attribute.
Query used in the VO is as follows. SELECT poh.segment1 PONumber , poh.po_header_id POHeaderId , TO_CHAR (pol.line_num) POLine , pol.po_line_id POLineId , flv.lookup_code POPriceType , flv.meaning POPriceTypeDisplay FROM po_headers_all poh , po_lines_all pol , fnd_lookups flv , (SELECT entity_id
EBS Oracle Time and Labor Development
41 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
, price_type , multiplier FROM po_price_differentials WHERE entity_type = 'PO LINE' AND enabled_flag = 'Y' UNION ALL SELECT po_line_id , 'STANDARD' , 1.0 FROM po_lines_all ) popd WHERE pol.po_header_id = poh.po_header_id AND flv.lookup_type = 'PRICE DIFFERENTIALS' AND flv.lookup_code = popd.price_type AND popd.entity_id = pol.po_line_id UNION SELECT poh.segment1 PONumber , poh.po_header_id POHeaderId , TO_CHAR (pol.line_num) POLine , pol.po_line_id POLineId , flv.lookup_code POPriceType , flv.meaning POPriceTypeDisplay FROM po_headers_all poh , po_lines_all pol , fnd_lookups flv , hxc_time_building_blocks day , hxc_time_building_blocks detail , hxc_time_attributes hta , hxc_time_attribute_usages htau WHERE poh.po_header_id = TO_NUMBER (hta.attribute8) AND pol.po_line_id = TO_NUMBER (hta.attribute2) AND flv.lookup_type = 'PRICE DIFFERENTIALS' AND flv.lookup_code = hta.attribute3 AND day.parent_building_block_id = :1 AND day.parent_building_block_ovn = :2 AND detail.parent_building_block_id = day.time_building_block_id AND detail.parent_building_block_ovn = day.object_version_number AND htau.time_building_block_id = detail.time_building_block_id AND htau.time_building_block_ovn = detail.object_version_number AND htau.time_attribute_id = hta.time_attribute_id AND hta.attribute_category = 'PURCHASING'
For ease of explanation, let’s consider only the first part of the UNION for now. Query is SELECT poh.segment1 PONumber , poh.po_header_id POHeaderId , TO_CHAR (pol.line_num) POLine , pol.po_line_id POLineId , flv.lookup_code POPriceType , flv.meaning POPriceTypeDisplay FROM po_headers_all poh , po_lines_all pol , fnd_lookups flv , (SELECT entity_id , price_type , multiplier FROM po_price_differentials WHERE entity_type = 'PO LINE' AND enabled_flag = 'Y' UNION ALL SELECT po_line_id , 'STANDARD' , 1.0
FROM po_lines_all ) popd WHERE pol.po_header_id = poh.po_header_id AND flv.lookup_type = 'PRICE DIFFERENTIALS' AND flv.lookup_code = popd.price_type AND popd.entity_id = pol.po_line_id
Let us first create a View out of the query so that it is easier to define the value set. This is not a mandatory step.
EBS Oracle Time and Labor Development
42 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
From Application Developer responsibility, Value Sets
In Edit Information page,
EBS Oracle Time and Labor Development
43 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
As per qualifier_attribute8 and qualifier_attribute9 created Value(Display Column) and Id (Id column). Qualifier_attribute14 and qualifier_attribute15 shows the below text
QUALIFIER_ATTRIBUTE14 = "POCuiPOHeaderId|PO_NUMBER|Y#POCuiPOLineId|PO_LINE|Y" QUALIFIER_ATTRIBUTE15 = "POHeaderId = ::POCuiPOHeaderId#POLineId = ::POCuiPOLineId"
Two layout attributes need reference in this value set, PO Number and PO line, first and second attribute respectively. WHERE clause to be build like
“POHeaderId = :attribute1 AND POLineId = :attribute2”
EBS Oracle Time and Labor Development
44 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
EBS Oracle Time and Labor Development
45 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Running OTL: Upgrade Layouts for Mobile Time Entry
Include the concurrent program in your request group.
Invoke the program from the Administrator responsibility
Choose No if the request is being run for the first time for the layout to be upgraded. If you are doing a rerun or a correction, choose Yes.
EBS Oracle Time and Labor Development
46 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Select the layout you want to upgrade
Program automatically populates the layout attributes attached to the layout.
Against each layout attribute, select the appropriate value set.
EBS Oracle Time and Labor Development
47 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Verify that the request completes successfully and look for the following message in the log file.
EBS Oracle Time and Labor Development
48 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Trouble Shooting
Mobile Foundation:
Issue #1 When entering user credentials and server URL in the configuration screen, no error message is
shown. However, the configuration screen is presented again. Probable Cause
User credentials are wrong and/or configuration server URL is wrong.
Resolution User should verify that they enter their valid E-Business Suite username and password. The
configuration server URL should be valid in the format of http(s)://<hostname>:<port>/OA_HTML/config
Issue #2
When entering valid user credentials and valid server URL in the configuration screen, no error message is shown. However, the configuration screen is presented again. Probable Cause
Application bundle id in the configuration service request sent to the E-Business Suite server from the mobile app does not match with server setup. Resolution
System administrator should verify that the application bundle id in the Mobile Application Manager matches the mobile app implementation guide.
Issue #3 Configuration server URL is not accessible when tested from a web browser. An HTTP 404 error
appears. Probable Cause
HTTP server configuration for configuration service is missing. Resolution
Verify that AutoConfig was run after applying the appropriate ATG consolidated patch for your Oracle E-Business Suite release.
Issue #4 Configuration server URL is not accessible when tested from a web browser. An HTTP 404 error
appears. Probable Cause
HTTP server configuration for configuration service is missing.
EBS Oracle Time and Labor Development
49 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Resolution Verify that AutoConfig was run after applying the appropriate ATG consolidated patch for your
Oracle E-Business Suite release.
Issue #5 Login server URL is not accessible when users attempt to login to app.
Probable Cause Issues with pre-requisite patch.
Resolution
The login service on the E-Business Suite server is automatically setup after patch application. Login service is of format
http(s)://<hostname>:<port>/OA_HTML/config Issues during patch application could result in login service in accessible. Contact Oracle
Support.
Timecards:
Issue #1 You do not have access to use this mobile application. Please contact your System Administrator. Probable Cause “Mobile Time Entry” Role has not been added to the responsibility (or) “Mobile Time Entry” Menu is missing. Resolution Add the “Mobile Time Entry” role to the responsibility and “Mobile Time Entry” menu as a submenu to the associated responsibility. Please refer to the white paper available in MOS Doc ID 1671889.1 for more details. Issue #2 Invalid Setup: <<LAYOUT_NAME>> is not upgraded. Probable Cause Employee has a timecard layout preference which is not upgraded for mobile time entry. Resolution Check the layout attached to self service timecard layout preference. If not upgraded, please execute the concurrent program “OTL: Upgrade Layouts for Mobile Time Entry”. If layout attached to preference is upgraded, check if the employee has already entered timecards with a layout that is not upgraded.
EBS Oracle Time and Labor Development
50 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Issue #3 Invalid Setup: Absence Integration is enabled Probable Cause OTL Absence Integration is enabled for this user via OTL Preferences. Resolution The V1 version of mobile Timecard app does support OTL Absence Integration. Please disable the Integration to use mobile timecard app. Issue #4 Invalid Setup: Audit Layout has been set. Probable Cause User has OTL preference set up with an Audit Layout. Resolution Change and Late Audit feature is not supported in V1 version. Remove the value in Audit Layout, (6th segment) in SS timecard layout preference. Issue #5 Invalid Setup: Audit Preferences has been enabled. Probable Cause SS timecard flow is set to Audit Resolution Set SS timecard flow to Standard. Audit flow is linked to CLA feature which is not enabled for mobile timecards. Issue #6 Invalid Setup: Audit Time Entry Rule has been set. Probable Cause Time Store Audit Requirements is set to a valid Time entry rule. Resolution Set Time Store Audit Requirements to NULL. Audit Time Entry Rule is linked to CLA feature which is not enabled for mobile timecards.
EBS Oracle Time and Labor Development
51 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Issue #7 You must use <<RESP>> responsibility for this action. Probable Cause This issue occurs when multiple responsibilities are enabled for mobile timecards. Timecard created with one responsibility cannot be modified using another responsibility. Resolution User must use the corresponding responsibility to modify the timecard. Navigate to Settings -> Responsibility and select the correct responsibility. Issue #8 You must enter <<FIELDS>>. Eg: You must enter Hours Type. Probable Cause Timecard has one or more mandatory fields and user has not entered all the required fields. Resolution User must key in all the required fields. <<FIELDS>> in the error message are the mandatory fields. Issue #9 Submit Failed. Timecard cannot be found. Save Failed. Timecard cannot be found. Delete Failed. Timecard cannot be found. Probable Cause The administrator or user has deleted the specific timecard in Timestore, there is a discrepancy in the data between the device and Timestore. Resolution Delete the timecard, logout and log in to continue working. Issue #10 Timecard period does not match your setup. You must delete this timecard. Probable Cause User's SS timecard period was changed after a timecard was pending in Draft status in the device. Resolution Delete the timecard in Draft status, logout and log in to continue working.
EBS Oracle Time and Labor Development
52 Oracle Mobile Timecards for Oracle E-Business Suite™: White Paper
Issue #11 You cannot create this Time Entry. Probable Cause SS status allowing edit preference does not allow user to create this timecard. Resolution Adjust Future Number of days and Past number of days preference values.