Many a times when the reports are delivering the content to UCM server, we get below error:
Document delivery failed
[INSTANCE_ID=bip.bi_server1] [DELIVERY_ID=-1]Exception happened when delivery document to wcc
deliver API call throw ProcessingException
WCC delivery failed with Exception
WCC delivery failed with DeliveryException
oracle.xdo.service.delivery.DeliveryException: oracle.j2ee.ws.client.jaxws.JRFSOAPFaultException: Client received SOAP Fault from server : InvalidSecurity : error in processing the WS-Security security header
oracle.xdo.service.delivery.DeliveryException: oracle.xdo.service.delivery.DeliveryException: oracle.j2ee.ws.client.jaxws.JRFSOAPFaultException: Client received SOAP Fault from server : InvalidSecurity : error in processing the WS-Security security header
at oracle.xdo.service.delivery.impl.DeliveryServiceImpl.deliverToWCC(DeliveryServic
In this case look at the password for account used to connect to UCM server. If the password is reset make sure it is updated at the UCM connection as well.
In order to load person profile items, a person should have a profile code. There are two ways to create the profile code for a person record.
From UI :- When any business user clicks on Talent Profile for a worker, a profile code is automatically generated in the backend. Profile code is not visible in UI and is always stored in the backend.
Using HDL :- Profile codes can be loaded in bulk using TalentProfile.dat business object of HCM Data Loader (HDL).
It is advisable to load profile codes in bulk as part of data migration using HDL. But there are cases where a user will click on Talent Profile of a worker just after migrating the Core data for the worker. In this case, a profile code is generated in HRT_PROFILES_B table. So, when TalentProfile.dat is used, the profile record will fail for this particular worker.
Below SQL can be used to get list of all active workers who don’t have a talent profile code yet:
select *
from per_all_people_f papf
, per_periods_of_service ppos
where papf.person_id = ppos.person_id
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and ppos.actual_termination_date is NULL
and not exists (select 1 from HRT_PROFILES_B hpb where papf.person_id = hpb.person_id )
All recurring element entires are loaded/ created with an effective end date of 31-Dec-4712. But I have seen scenarios, where the business have requirements to end date an element entry as of a certain date. To achieve this in Bulk, one can use ElementEntry.dat HDL business object.
Let us take an example, where an employee has an element entry with an effective end date of 31-Dec-4712:
The effective end date is set to blank which is equivalent to 31-Dec-4712 in the backend table.
So, let us assume the business has requested to end date this particular element as of 31-Jan-2024 for all employees. To achieve, this we need to pull the existing data from element entries table for this particular element. Below SQL can be used to get the ID values and to verify the results before and after HDL load:
SELECT DISTINCT peevf.element_entry_value_id
,peef.element_entry_id
,petf.base_element_name
,peef.effective_start_date ele_sd
,peef.effective_end_date ele_ed
,peevf.effective_start_date
,peevf.effective_end_date
,paam.assignment_number
FROM per_all_assignments_m paam
,pay_element_types_f petf
,pay_element_entries_f peef
,pay_element_entry_values_f peevf
WHERE 1=1
AND paam.person_id = peef.person_id
AND peef.element_type_id = petf.element_type_id
AND peef.element_entry_id = peevf.element_entry_id
AND paam.ASSIGNMENT_TYPE in ('E')
AND paam.primary_assignment_flag = 'Y'
AND petf.base_element_name = 'Test XYZ Bonus'
AND paam.assignment_number = 'E2121212'
AND trunc(sysdate) between petf.effective_start_date and petf.effective_end_date
AND trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
A normal HDL file with new effective end date will just create a date split in the data. To avoid this a new attribute called ReplaceLastEffectiveEndDate should be added in the HDL file which will updated the effective end date from 31-Dec-4712 to 31-Jan-2024.
METADATA|ElementEntry|AssignmentNumber|ElementName|EffectiveStartDate|EffectiveEndDate|LegislativeDataGroupName|MultipleEntryCount|EntryType|ReplaceLastEffectiveEndDate
MERGE|ElementEntry|E2121212|Test XYZ Bonus|2012/01/31|2024/01/31|GB Legislative Data Group|1|E|Y
Once this HDL is run successfully, the effective end date will get updted.
You use “Manage Address Formats” task from Setup and Maintenance to define the address style for a particular country. Oracle provides a lot of address format for various countries out of the box. You have the capaibility to customize the address formats as per customer requirement.
For example, you can search for all available address style for United Kingdom using country filter:
You can see below details:
You can edit the address format to customize the address style. You can make a seeded attribute mandatory/ non-mandatory or can add a new address element and many more properties can be set:
You can save the changes. And the address format become active.
One of the main challenges, the data migration team and the integration teams face is the mapping of layout fields to actual backend table attribute in per_addresses_f table.
There is no direct way to find the UI attribute name and its corresponding attribute name in backend table. Below query has been developed to find the UI prompt with actual table attribute name:
SELECT ftt.TERRITORY_SHORT_NAME
,hsfl.TERRITORY_CODE
,hsflb.STYLE_FORMAT_CODE
,hsflb.VARIATION_NUMBER
,hsflt.PROMPT
,hsflb.ATTRIBUTE_CODE
,hsflb.LINE_NUMBER
,hsflb.MANDATORY_FLAG
,hsflb.USE_INITIAL_FLAG
,hsflb.UPPERCASE_FLAG
,hsflb.STATUS_FLAG
,hsflb.TRANSFORM_FUNCTION
,hsflb.DELIMITER_BEFORE
,hsflb.DELIMITER_AFTER
,hsflb.BLANK_LINES_BEFORE
,hsflb.BLANK_LINES_AFTER
,hsflb.START_DATE_ACTIVE
,hsflb.END_DATE_ACTIVE
,hsflb.DISPLAY_SIZE
FROM HZ_STYLE_FMT_LAYOUTS_B hsflb
,HZ_STYLE_FMT_LAYOUTS_TL hsflt
,HZ_STYLE_FMT_LOCALES hsfl
,FND_TERRITORIES_TL ftt
WHERE hsflb.STYLE_FORMAT_CODE = hsfl.STYLE_FORMAT_CODE
AND hsflb.STYLE_FMT_LAYOUT_ID = hsflt.STYLE_FMT_LAYOUT_ID
AND ftt.TERRITORY_CODE = hsfl.TERRITORY_CODE
AND ftt.LANGUAGE = 'US'
AND ftt.LANGUAGE = hsflt.LANGUAGE
AND ftt.TERRITORY_CODE = 'MX'
ORDER BY hsflb.STYLE_FORMAT_CODE, hsflb.LINE_NUMBER
There can multiple grades assigned as Valid grade at Position level or Job level. However, there is no direct way to end date the valid grades in bulk.
You can end date a valid grade from responsive UI. But it will be a lot of manual effort. You can search for Position and then navigate to Grades section. Then update the position and click on small delete icon next to Grade name (which you want to end date). This will end date the valid grade with an effective end date = date of position update – 1.
To do this in bulk using HDL, you can’t use DELETE command. If you use DELETE command, it will completely Purge the valid grade record from Position. To end date the valid grade, use “ReplaceLastEffectiveEndDate” attribute in the file.
Below is the sample file:
METADATA|PositionGrade|BusinessUnitName|PositionCode|EffectiveStartDate|EffectiveEndDate|GradeCode|GradeSetCode|ReplaceLastEffectiveEndDate
MERGE|PositionGrade|Progress US Business Unit|PRGUSPOS032|2018/12/31|2023/12/31|Hourly01|PRGUSGRADESET|Y
EffectiveStartDate – Earliest Grade Start Date
EffectiveEndDate – Date on which you want to end date the grade.
Once the file is loaded successfully, below is how the data will look in the backend:
Below SQL query can be used to extract valid grades data:
SELECT DISTINCT
TO_CHAR (pvgf.effective_start_date, 'DD/MON/YYYY') effective_start_date,
TO_CHAR (pvgf.effective_end_date, 'DD/MON/YYYY') effective_end_date,
pjfv.POSITION_CODE,
pjfv.name job_name,
pgfv.grade_code,
pgfv.name grade_name,
pvgf.valid_grade_id,
pgfv.grade_id,
pjfv.job_id
FROM per_valid_grades_f pvgf,
HR_ALL_POSITIONS_F_VL pjfv,
per_grades_f_vl pgfv
WHERE 1=1
AND pvgf.position_id = pjfv.position_id
AND pvgf.grade_id = pgfv.grade_id
AND pjfv.POSITION_CODE = 'PRGUSPOS032'
AND pvgf.effective_start_date BETWEEN pjfv.effective_start_date AND pjfv.effective_end_date
AND pvgf.effective_start_date BETWEEN pgfv.effective_start_date AND pgfv.effective_end_date
ORDER BY POSITION_CODE,grade_code
To bulk upload items catalogs in HCM profiles, you can use ContentItem.dat. Each of the template have certain mandatory attributes like Context Name, Value Set Name or Value Set Id:
S0, before you start preparing the file, you need to have below information handy:
Context Name – This is a mandatory attribute. If you don’t pass the value in your HDL file, you will get below error:
The values 3000122xxxxx aren’t valid for ContentItemValueSetId.
You can get the context name from HRT_CONTENT_TYPES_B table:
2. Content Item Value Set Name/Id: This is again a mandatory attribute. You can get the Content Value Set Name/ Id from HRT_CONTENT_TP_VALUESETS_TL table.
Once you have the details, you can prepare ContentItem.dat.
Below are the sample files for different item catalog templates:
For Establishments:
METADATA|ContentItem|Name|ContextName|ContentItemValueSetName|ContentItemValueSetId|ContentItemCode|DateFrom|DateTo|RatingModelCode|SourceSystemId|SourceSystemOwner
MERGE|ContentItem|Indian Institute of Technology, Bombay|EDUCATIONAL_ESTABLISHMENT|Establishment||IIT_B|1951/01/01|||IIT_B|HRC_SQLLOADER
MERGE|ContentItem|Indian Institute of Management, Ahemdabad|EDUCATIONAL_ESTABLISHMENT|Establishment||IIM_A|1951/01/01|||IIT_B|HRC_SQLLOADER
For Licenses and Certifications:
METADATA|ContentItem|Name|ContextName|ContentItemValueSetName|ContentItemValueSetId|ContentItemCode|DateFrom|DateTo|RatingModelCode|SourceSystemId|SourceSystemOwner
MERGE|ContentItem|Oracle Global Human Resources 2023|CERTIFICATION|Licenses and Certifications||O_GHR_2023|1951/01/01|||O_GHR_2023|HRC_SQLLOADER MERGE|ContentItem|Oracle Benefits 2023|CERTIFICATION|Licenses and Certifications||O_BEN_2023|1951/01/01|||O_BEN_2023|HRC_SQLLOADER
For Degrees:
METADATA|ContentItem|Name|ContextName|ContentItemValueSetName|ContentItemValueSetId|ContentItemCode|DateFrom|DateTo|RatingModelCode|SourceSystemId|SourceSystemOwner
MERGE|ContentItem|PhD|DEGREE|Degrees||XX_PhD|1951/01/01|||CI_XX_PhD|HRC_SQLLOADER
MERGE|ContentItem|Higher National Certificate|DEGREE|Degrees||XX_Higher National Certificate|1951/01/01|||CI_XX_Higher National Certificate|HRC_SQLLOADER
For Competencies:
METADATA|ContentItem|ContextName|ContentItemValueSetName|Name|ContentItemId|ContentItemCode|DateFrom|DateTo|ItemDescription|RatingModelId|RatingModelCode|CountryGeographyCode|CountryCountryCode|SourceSystemId|SourceSystemOwner
MERGE|ContentItem|COMPETENCY|Competencies|Accounting Standards and Principles||XX_ASAP|1951/01/01||To check knowledge on Accounting Standards and Principles.|5|PROFICIENCY|||XX_ASAP|HRC_SQLLOADER
MERGE|ContentItem|COMPETENCY|Competencies|Assessing Talent||XX_AT|1951/01/01||To check knowledge on Assessing Talent.|5|PROFICIENCY|||XX_AT|HRC_SQLLOADER
MERGE|ContentItem|COMPETENCY|Competencies|Assurance and Reporting||XX_AAR|1951/01/01||To check knowledge on Assurance and Reporting.|5|PROFICIENCY|||XX_AAR|HRC_SQLLOADER
Please note that Rating Mode Id is mandatory for loading competencies. You can find the rating model id and rating model code from hrt_rating_models_b table.
Once the data load is successful, you can run below queries to extract loaded data:
In this article, I will talk about affect of using SET PURGE_FUTURE_CHANGES in HCM Data Loader file. HCM use date tracked functionality for most of the objects in HCM, be it work structures, worker or payroll related objects.
I have seen many scenarios, where I had to go in past and make an update on the existing past date without touching the current existing record. There are numerous scenarios like these where HCM technical consultants are expected to make use of HDL to update the past dated records. Oracle has designed PURGE_FUTURE_CHANGES SET command specifically for these scenarios.
But before adding this command in your HDL (.dat) file, it is very important to understand the working of this command, otherwise it may unwantedly purge/ change data which is beyond recovery.
Let us start with an example. Below is existing employment data for a worker with Person Number – 120:
Later on, it has been found that Grade should have been changed Grade 6 from 16-Jun-2022 onwards but there is no data for this in the database currently..
So, in this case a new row with Grade update to Grade 6 should be inserted starting on 16-Jun-2022.
So, the technical consultant has created an an HDL with below format:
As you can see, EffectiveStartDate = 2022/06/16. So, the intention here is to insert a new row starting 2022/06/16.
But unfortuantely, running above HDL will replace all the existing future rows, i.e. the row with effective start date of 2023/01/01 will be purged from database. Below is how the new data will look once the above HDL load is done:
This happens because the default system setting for update mode is set to REPLACE. This can be verified using “Configure HCM Data Loader” task from Setup and Maintenance:
So, in order to preserve the future dated rows, you have to use SET command:
SET PURGE_FUTURE_CHANGES N
METADATA|WorkTerms|AssignmentId|PeriodOfServiceId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode
MERGE|WorkTerms|300000066966135|300000066966134|Y|1|2022/06/16|4712/12/31|300000066966135|FUSION|ASG_CHANGE
METADATA|Assignment|AssignmentId|WorkTermsAssignmentId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode|GradeCode
MERGE|Assignment|300000066966140|300000066966135|Y|1|2022/06/16|4712/12/31|300000066966140|FUSION|ASG_CHANGE|GRADE6
Executing this will preserve the future dated rows. But it will change the data in future rows as per current row data.
Now if the requirement is to keep the future dated rows and their data intact, you should pass #RETAIN in EffectiveEndDate attribute as shown in below example:
SET PURGE_FUTURE_CHANGES N
METADATA|WorkTerms|AssignmentId|PeriodOfServiceId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode
MERGE|WorkTerms|300000066966135|300000066966134|Y|1|2022/06/16|#RETAIN|300000066966135|FUSION|ASG_CHANGE
METADATA|Assignment|AssignmentId|WorkTermsAssignmentId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode|GradeCode
MERGE|Assignment|300000066966140|300000066966135|Y|1|2022/06/16|#RETAIN|300000066966140|FUSION|ASG_CHANGE|GRADE6
With #RETAIN value of Asg Attribute 1 is retained on 01-Jan-2023 row.
Absence reasons can be loaded in BULK using AbsenceReason.dat.
BaseName is a mandatory attribute in the file. If BaseName is not passed, user get below error:
An error occurred. To review details of the error run the HCM Data Loader Error Analysis Report diagnostic test. Message details: {MESSAGE}.
An error occurred. To review details of the error run the HCM Data Loader Error Analysis Report diagnostic test. Message details: Please check the stack trace for more details.
BaseName has to be provided in UPPER CASE concatenated with an underscore (_) and legislation code.
Example: TEST ABSENCE REASON_US
If the above format is not followed, HDL throws below error:
You can attach multiple adjustment reasons on accrual plan level. You can create any new custom adjustment reasons and add them in a lookup – ANC_ABS_PLAN_OTHER_REASONS.
Once the reason is added in the lookup, you can navigate to Absence Plans.
You can choose the newly added lookup code under “Enteries and Balances” tab.
Once the reasons are added, you can run below SQL query to extract the reasons added to the plan:
SELECT aapft.name
,aapf.legislation_code
,aapf.other_reasons
FROM ANC_ABSENCE_PLANS_F aapf
,ANC_ABSENCE_PLANS_F_TL aapft
WHERE aapf.absence_plan_id = aapft.absence_plan_id
AND aapft.language = 'US'
AND TRUNC(SYSDATE) BETWEEN aapf.effective_start_date AND aapf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN aapft.effective_start_date AND aapft.effective_end_date
AND aapf.other_reasons IS NOT NULL
AND aapf.other_reasons LIKE '%XX_MLA%'
ORDER BY 2,1
HCM Data Loader supports bulk uploading for Agents for Oracle recruiting cloud. Currently, there is no HDL support for loading Agencies. And idea is already submitted for same:
SELECT papf.person_number
,ppnf.full_name
,pao.action_type_code
,pav.action_name
,part.action_reason termination_reason
FROM per_all_people_f papf
,per_person_names_f ppnf
,per_periods_of_service ppos
,per_actions_vl pav
,per_action_reasons_tl part
,per_action_occurrences pao
WHERE papf.person_id =ppnf.person_id
AND papf.person_id =ppos.person_id
AND UPPER(ppnf.name_type)='GLOBAL'
--AND TO_CHAR(ppos.actual_termination_date,'dd-mm-yyyy')<TO_CHAR(SYSDATE,'dd-mm-yyyy')
AND ppos.action_occurrence_id = pao.action_occurrence_id
AND pao.action_reason_id = part.action_reason_id (+)
AND part.language (+) = USERENV('LANG')
AND TRUNC(SYSDATE) BETWEEN TRUNC(papf.effective_start_date) AND TRUNC(papf.effective_end_date)
AND TRUNC(SYSDATE) BETWEEN TRUNC(ppnf.effective_start_date) AND TRUNC(ppnf.effective_end_date)
AND papf.person_number = '123351'
AND pao.action_id = pav.action_id
order by papf.person_number
For requirements to add List of values to a DFF segments, one can create independent value set or a table based value set. For example, if you want LOV as your existing departments, then you can use a table based value set.
But sometimes, you need to display one hard coded value “All” along with list of departments. This is in order to specify that all departments are applicable for a DFF segement value.
In, this case you need to use UNION.
Below is how the query will look like:
SELECT substr(pd.name,1,125) dep_name
FROM per_departments pd
UNION
SELECT 'All Departments' dep_name
FROM DUAL
Value Set setup:
From Clause:
(SELECT substr(pd.name,1,125) dep_name FROM per_departments pd UNION SELECT ‘All Departments’ dep_name FROM DUAL) a
Learning admin can attachments on offering level for different roles – Admin/ Employee etc.
In case, you need to extract the attachment details for an offering, below BIP can be used:
SELECT wlf_learning_items_f.learning_item_id
,wlf_learning_items_f.learning_item_number
,wlf_li_relations_f.relation_id
,fad.pk1_value
,fad.attached_document_id
,fdt.document_id
,fdt.title
,fdt.file_name
,fad.creation_date
FROM fnd_attached_documents fad
,fnd_documents_tl fdt
,wlf_learning_items_f wlf_learning_items_f
,wlf_li_relations_f wlf_li_relations_f
WHERE fad.document_id = fdt.document_id
AND fdt.language = 'US'
AND fad.entity_name = 'WLF_LI_RELATIONS_F'
AND fad.pk1_value = wlf_li_relations_f.relation_id
AND wlf_learning_items_f.learning_item_id = wlf_li_relations_f.source_id
AND TRUNC(SYSDATE) BETWEEN wlf_learning_items_f.effective_start_date AND wlf_learning_items_f.effective_end_date
AND TRUNC(SYSDATE) BETWEEN wlf_li_relations_f.effective_start_date AND wlf_li_relations_f.effective_end_date
ORDER BY fad.creation_date DESC
Once a worker record is terminated, it is expected that the associated user account will get inactive. However, it depends upon the configuration on Enterprise Level and auto provisioning rules setup. If there is even on role assigned to user name, the user account will not get terminated in HCM.
To help the business to get a list of active user accounts even if the associated worker record is terminated, below query is created:
SELECT DISTINCT papf.person_number
,ppos.actual_termination_date
FROM PER_ALL_PEOPLE_F papf
,PER_PERIODS_OF_SERVICE ppos
,PER_USERS pu
,PER_USER_ROLES pur
WHERE papf.person_id = ppos.person_id
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = pu.person_id
AND pu.user_id = pur.user_id
AND ppos.actual_termination_date IS NOT NULL
AND ppos.actual_termination_date < TRUNC(SYSDATE) /*Check only for past dated terminations*/
AND NOT EXISTS (SELECT 1 FROM PER_PERIODS_OF_SERVICE ppos2
WHERE ppos2.person_id = ppos.person_id
AND ppos2.actual_termination_date IS NULL)
ORDER BY 1,2
There are scenarios when we want to delete a future dated row from an object. Please note that for objects like Positions/Locations etc which are date tracked, one can make use of HDL with SET command to delete the future dated row.
Let us take an example, where we have below data on Position:
Position Name – Test Position
Effective Start Date – 01- Jan-2023 – Record creation
Effective Start Date – 01-Oct-2023 – Record updated (let us say Standard working hours).
Now the requirement is to delete the row with effective start date – 01-Oct-2023.
In such cases, below HDL can be used:
SET PURGE_FUTURE_CHANGES Y
METADATA|Position|BusinessUnitName|PositionCode|EffectiveStartDate|EffectiveEndDate
MERGE|Position|BU Name|Pos Code|2023/01/01|4712/12/31
Any locations for which the value of the field will be selected as “Yes”, will be only available to be used in Manage Employment screen. This is a great feature to segregate locations which can be used on employee assignments vs locations which are used as training centers/ supplier locations etc.
By default, this field is hidden on Location Details quick action.
One needs to enable a sandbox and then from transaction design studio, make it visible.
From Quick actions under My Client Groups, choose Location Details:
If you still don’t see the attribute, create a new sandbox, add Experience Design Tool in it:
Choose Location Details as Action:
Click on Add,
under Page attributes, you see Employee Location is hidden OOTB:
P.S. – Please note this field can’t be used in classic locations page. Also, the default value is set to No.
There are cases where we need to use functions like days between to get difference between two dates. These functions can not be used from Transaction console. One has to login to BPM Worklist to access the functions.
