Timecard Layout Modification – Adding Lov Column

Timecard entry layout modification, how to add a new column to an existing standard Projects Template.
Sometimes we do get a business requirement to add a new column or remove an existing column. Here we look at how to add a new LOV column to Projects Timecard Entry page.
Oracle Provided Projects Timecard:

Process1 – Custom LOV & Standard Region

Step1: Identify the layout to modify and download the file from Oracle DB
Navigation: OTL Super Administrator -> Preferences -> Select your preferences(XXCUS OTL) -> Self Service -> XXCUS Timecard Layout

select layout_name from apps.HXC_LAYOUTS_VL where display_layout_name = 'Projects Timecard Layout';

Once we get the layout name we have to use the FNDLOAD commands to download the LDT files from Oracle server.

Note: Even though timecard entry page is OAF, most of the layout modifications will be done by LDT files or OTL preferences setup.

FNDLOAD apps/apps 0 Y DOWNLOAD $HXC_TOP/patch/115/import/hxclaytlayoutsld.lct xxcus_hxclaytlayoutsld.ldt HXC_LAYOUTS LAYOUT_NAME='PROJECTS-TIMECARD'

Step2: Create one DFF entry in OTL Information Types DFF.
Navigation: Application Developer > Application > Flexfield > Descriptive > Segments

We have created the our own DFF(XXCUS_PROJ_TYPE) entry to store the value in DataBase.

Step3: Create a view for the custom LOV.
According to the OTL standard/guide, we can create 10 custom LOV columns in the timecard page. The view name should be HXC_CUI_CUSTOM_LOVn_V, where n stands for 1 to 10 values.

The columns in the view must be aliased to lov1columnN.
The view definition must include a column called aliasdefinitionid. (if we don’t want this column we have to modify the CustomLov1VO)

CREATE OR REPLACE FORCE VIEW "APPS"."HXC_CUI_CUSTOM_LOV1_V"
(
   "LOV1COLUMN1",
   "LOV1COLUMN2",
   "LOV1COLUMN3",
   "LOV1COLUMN4",
   "LOV1COLUMN5",
   "LOV1COLUMN6",
   "LOV1COLUMN7",
   "LOV1COLUMN8",
   "LOV1COLUMN9",
   "LOV1COLUMN10"
)
AS
   (SELECT project_type,
           description,
           project_type_class_code,
           NULL,
           NULL,
           NULL,
           NULL,
           NULL,
           NULL,
           NULL
      FROM pa_project_types_all);

Once the view is created the view object name located in the blow path with the name as CustomnVO, where n stands for 1 to 10 values.
Path: oracle.apps.hxc.selfservice.timecard.server.CustomnVO
Ex: oracle.apps.hxc.selfservice.timecard.server.Custom1VO
Since we don’t have the aliasdefinitionid column in the view, we have to remove the where condition(where aliasdefinitionid = :1) from Custom1VO.
Go to the path oracle.apps.hxc.selfservice.timecard.server where you find the Custom1VO.xml and remove the where condition and save.

Step4: In order to display Project Details column on Timecard page, we have to create an attribute and create a region item in the timecard region(HXC_CUI_TIMECARD)
Navigation: AK Html Forms -> AK Attributes

Navigation: AK Html Forms -> AK Regions
Application ID/Name: HXC 809 Time and Labor Engine
Region Code: HXC_CUI_TIMECARD and click Go button to search.

click on region code HXC_CUI_TIMECARD hyper link and you see whole list item on this region. Click on Create Region Item button.

Step5: Modify the .ldt(xxcus_hxclaytlayoutsld.ldt) file which we had downlaoded at setp1.
Change the layout name from Projects Timecard Layout to XXCUS Projects Timecard Layout. You find this after entity definitions in the .ldt file.
Standard:

After Rename:

Note: This step is impornt other wise we will replace oracle standard layout(Projects Timecard Layout).

############################################################################ 
# Project Details on the timecard matrix  - Start
############################################################################ 
BEGIN HXC_LAYOUT_COMPONENTS "Projects Timecard Layout - Project Details"
OWNER = "ORACLE"
COMPONENT_VALUE = "DETAILS"
SEQUENCE = "275" 
COMPONENT_DEFINITION = "LOV"
RENDER_TYPE = "WEB" 
PARENT_COMPONENT = "Projects Timecard Layout - Day Scope Building blocks for worker timecard matrix"
REGION_CODE = "HXC_CUI_TIMECARD"
REGION_CODE_APP_SHORT_NAME = "HXC"
ATTRIBUTE_CODE = "HXC_XXCUS_PJDET_LOV"
ATTRIBUTE_CODE_APP_SHORT_NAME = "HXC"
BEGIN HXC_LAYOUT_COMP_QUALIFIERS "Projects Timecard Layout - Project 
Details"
OWNER = "ORACLE"
QUALIFIER_ATTRIBUTE_CATEGORY = "LOV"
      QUALIFIER_ATTRIBUTE1 = "CustomLov1VO"
      QUALIFIER_ATTRIBUTE2 = "Y"
      QUALIFIER_ATTRIBUTE3 = "/oracle/apps/hxc/selfservice/configui/webui/CuiCustomLov1RN"
      QUALIFIER_ATTRIBUTE4 = "809"
      QUALIFIER_ATTRIBUTE5 = "12"
      QUALIFIER_ATTRIBUTE6 =
 "Lov1column1|DETAILS-DISPLAY|CRITERIA|N|Lov1column3|DETAILS|RESULT|N|Lov1column1|DETAILS-DISPLAY|RESULT|N"
      QUALIFIER_ATTRIBUTE8 = "Lovcolumn1"
      QUALIFIER_ATTRIBUTE9 = "Lovcolumn3"
      QUALIFIER_ATTRIBUTE10 =
 "oracle.apps.hxc.selfservice.timecard.server.CustomLov1VO"
      QUALIFIER_ATTRIBUTE17 = "OraTableCellText"
      QUALIFIER_ATTRIBUTE20 = "N"
      QUALIFIER_ATTRIBUTE21 = "Y"
      QUALIFIER_ATTRIBUTE22 = "L"
      QUALIFIER_ATTRIBUTE25 = "FLEX"
      QUALIFIER_ATTRIBUTE26 = "XXCUS_PROJ_TYPE"
      QUALIFIER_ATTRIBUTE27 = "Attribute1"
      QUALIFIER_ATTRIBUTE28 = "DETAILS"
END HXC_LAYOUT_COMP_QUALIFIERS 
END HXC_LAYOUT_COMPONENTS  
############################################################################ 
# Project Details on the timecard matrix  - End
############################################################################

ATTRIBUTE_CODE = “HXC_XXCUS_PJDET_LOV” Attribute code what we defined at step4
SEQUENCE = “275” Defind at step4 of create region item
COMPONENT_DEFINITION = “LOV” LOV field (If its choice list then value would be CHOICE_LIST)

QUALIFIER_ATTRIBUTE_CATEGORY = “LOV” LOV Field (If its choice list then value would be CHOICE_LIST)
QUALIFIER_ATTRIBUTE1 = “CustomLov1VO” VO name based on the OTL standards
QUALIFIER_ATTRIBUTE3 = “/oracle/apps/hxc/selfservice/configui/webui/CuiCustomLov1RN” Standard region for LOV, we can create our region also based on our requirement.
QUALIFIER_ATTRIBUTE5 = “12” Length of the field
QUALIFIER_ATTRIBUTE6 = “Lov1column1|DETAILS-DISPLAY|CRITERIA|N|Lov1column3|DETAILS|RESULT|N|Lov1column1|DETAILS-DISPLAY|RESULT|N”
It controls the data baded on the criteria and results for the list of values.
QUALIFIER_ATTRIBUTE8 = “Lovcolumn1” To dispaly value in the screen(like Descriptions)
QUALIFIER_ATTRIBUTE9 = “Lovcolumn3” Store the value in the table(like ID’s)
QUALIFIER_ATTRIBUTE10 = “oracle.apps.hxc.selfservice.timecard.server.CustomLov1VO” Path of the VO name

QUALIFIER_ATTRIBUTE25 = “FLEX” Flex field segment
QUALIFIER_ATTRIBUTE26 = “XXCUS_PROJ_TYPE” DDF Context Name, what we defined at step2
QUALIFIER_ATTRIBUTE27 = “Attribute1” Above DFF segment Name
QUALIFIER_ATTRIBUTE28 = “DETAILS” Alias Name

If you would like to know much more about the QUALIFIER ATTRIBUTES for LOV.
Navigation: Application Developer > Application > Flexfield > Descriptive > Segments
Application: Time and Labor Engine
Title: Self Service Configurable UI Flexfield and select LOV, if you want to see for CHOICE_LIST then select CHOICE_LIST.

LOV QUALIFIER ATTRIBUTES are 1 to 15 and rest others 16 to 30 are Global Data Elements (mostly common for all QUALIFIER/ATTRIBUTE CATEGORY)

Step6: Copy this above code into xxcus_hxclaytlayoutsld.ldt file after Expenditure Type and place this into the server and run LOAD command.

FNDLOAD apps/apps 0 Y UPLOAD $HXC_TOP/patch/115/import/hxclaytlayoutsld.lct xxcus_hxclaytlayoutsld.ldt

After the successful load and assign new template(XXCUS Projects Timecard Layout) to preference.

Step7: Attach it to preference and test the timecard
Navigation: OTL Super Administrator -> Preference

If we look at the LOV values region it has LOV1_COLUMN1 to LOV10_COLUMN10, if we want to display our own custom region instead of standard region we have to create our own region and pass it in .ldt file.

Process2 – Custom LOV & Custom Region

Step1: Create Attributes
We need to create attributes for columns to deispaly in LOV region and columns headings
Navigation: Ak Html Forms -> AK Attributes -> Click on create attribute
We have 3 columns(Project Type, Description and Project Type Class Code)

Project Type

Description

Project Type Class Code

Step2: Create Region and Assign Attributes to Region
Navigation: Ak Html Forms -> AK Region -> Click on create region

AM Path: oracle.apps.hxc.selfservice.configui.server.LovAM(We have to give the OTL standard LOV AM path) OR oracle.apps.hxc.selfservice.configui.server.TimecardAM
CO Path: oracle.apps.hxc.selfservice.configui.server.LovCO(We have to give the OTL standard LOV CO path)
Search for newly created custom region(XXCUS_PROJ_LOV) and click hyperlink of XXCUS_PROJ_LOV

Now assign attributes that we created at step1 to this region(XXCUS_PROJ_LOV). Click on create region item

XXCUS_PROJ_TYPE – XXCUSProjType – Project Type

XXCUS_PROJ_DESC – XXCUSProjDesc – Description

XXCUS_PROJ_CLASS_CODE – XXCUSProjClassCode – Project Type Class Code

Step3: Assign custom region to .ldt(xxcus_hxclaytlayoutsld.ldt) file from the PROCESS1

############################################################################ 
# Project Details on the timecard matrix  - Start
############################################################################ 
BEGIN HXC_LAYOUT_COMPONENTS "Projects Timecard Layout - Project Details"
OWNER = "ORACLE"
COMPONENT_VALUE = "DETAILS"
SEQUENCE = "275" 
COMPONENT_DEFINITION = "LOV"
RENDER_TYPE = "WEB" 
PARENT_COMPONENT = "Projects Timecard Layout - Day Scope Building blocks for worker timecard matrix"
REGION_CODE = "HXC_CUI_TIMECARD"
REGION_CODE_APP_SHORT_NAME = "HXC"
ATTRIBUTE_CODE = "HXC_XXCUS_PJDET_LOV"
ATTRIBUTE_CODE_APP_SHORT_NAME = "HXC"
BEGIN HXC_LAYOUT_COMP_QUALIFIERS "Projects Timecard Layout - Project 
Details"
OWNER = "ORACLE"
QUALIFIER_ATTRIBUTE_CATEGORY = "LOV"
      QUALIFIER_ATTRIBUTE1 = "CustomLov1VO"
      QUALIFIER_ATTRIBUTE2 = "N"
      QUALIFIER_ATTRIBUTE3 = "XXCUS_PROJ_LOV"
      QUALIFIER_ATTRIBUTE4 = "809"
      QUALIFIER_ATTRIBUTE5 = "12"
      QUALIFIER_ATTRIBUTE6 =
 "XXCUSProjType|DETAILS-DISPLAY|CRITERIA|N|XXCUSProjClassCode|DETAILS|RESULT|N|XXCUSProjType|DETAILS-DISPLAY|RESULT|N"
      QUALIFIER_ATTRIBUTE8 = "XXCUSProjType"
      QUALIFIER_ATTRIBUTE9 = "XXCUSProjClassCode"
      QUALIFIER_ATTRIBUTE10 =
 "oracle.apps.hxc.selfservice.timecard.server.CustomLov1VO"
      QUALIFIER_ATTRIBUTE17 = "OraTableCellText"
      QUALIFIER_ATTRIBUTE20 = "N"
      QUALIFIER_ATTRIBUTE21 = "Y"
      QUALIFIER_ATTRIBUTE22 = "L"
      QUALIFIER_ATTRIBUTE25 = "FLEX"
      QUALIFIER_ATTRIBUTE26 = "XXCUS_PROJ_TYPE"
      QUALIFIER_ATTRIBUTE27 = "Attribute1"
      QUALIFIER_ATTRIBUTE28 = "DETAILS"
END HXC_LAYOUT_COMP_QUALIFIERS 
END HXC_LAYOUT_COMPONENTS  
############################################################################ 
# Project Details on the timecard matrix  - End
############################################################################

Step4:Migrate the above-created custom region to Database using below API.


BEGIN
   hxc_lov_migration.migrate_lov_region (
      p_region_code             => 'XXCUS_PROJ_LOV',
      p_region_app_short_name   => 'HXC',
      p_force                   => 'Y');
END;

COMMIT;

-- You can check using below SQL/Table
select * 
 from JDR_ATTRIBUTES 
 where att_comp_docid =
 (select comp_docid 
 from JDR_COMPONENTS  where comp_ref = '/oracle/apps/hxc/selfservice/configui/webui/XXCUS_PROJ_LOV')+1;

Step5: Upload .ldt(xxcus_hxclaytlayoutsld.ldt) to Oracle server and Test the Timecard Page.

FNDLOAD apps/apps 0 Y UPLOAD $HXC_TOP/patch/115/import/hxclaytlayoutsld.lct xxcus_hxclaytlayoutsld.ldt

Move on to the oracle timecard page and click on Project LOV(Custom LOV)

Oracle Apps Info

Oracle Applications Knowledge Sharing

Timecard Layout Modification – Adding Lov Column