When a scheduled request is submitted, the details are stored in ess_request_history table. If the submitted request has no child requests, it will have only one row in the table.
The ParentId attribute in this case will be 0. However, if the parent request invokes multiple child jobs, the parentId attribute will have the requestid of previous request. In order to segregate the all child jobs submitted by a parent request, one need to use absParentId attribute in the table.
Below is the sample query:
SELECT REQUESTID,APPLICATION,NAME,ABSPARENTID,PARENTREQUESTID,INSTANCEPARENTID
FROM ess_request_history
WHERE ABSPARENTID = 2394562 --parent request id
ORDER BY requestid DESC
SELECT warf.ASSIGNMENT_RECORD_ID,
warf.EVENT_TYPE,
we.EVENT_TYPE we_event_type,
warf.EVENT_SUB_TYPE,
we.EVENT_SUB_TYPE we_EVENT_SUB_TYPE,
(SELECT person_number
FROM per_all_people_f
WHERE person_id = warf.EVENT_CREATED_BY_ID
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date) created_By,
we.CREATED_BY_ID "Assigned_By_Person_Id",
(SELECT person_number
FROM per_all_people_f
WHERE person_id = we.CREATED_BY_ID
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date) "Assigned_By",
warf.ATTRIBUTION_TYPE,
we.ATTRIBUTION_TYPE we_ATTRIBUTION_TYPE,
(SELECT person_number
FROM per_all_people_f
WHERE person_id = warf.ATTRIBUTION_ID
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date) attribution_id,
warf.ATTRIBUTION_ID warf_ATTRIBUTION_ID,
we.ATTRIBUTION_ID we_ATTRIBUTION_ID,
warf.learner_id,
warf.ATTRIBUTION_LOOKUP_CODE,
warf.EVENT_ASSIGNMENT_ID
from WLF_ASSIGNMENT_RECORDS_F warf,
WLF_EVENT_ASSIGNMENTS_F weaf,
WLF_EVENTS we
where warf.EVENT_ASSIGNMENT_ID = weaf.EVENT_ASSIGNMENT_ID
and trunc(sysdate) between warf.effective_start_date and warf.effective_end_date
and trunc(sysdate) between weaf.effective_start_date and weaf.effective_end_date
and we.event_id = weaf.event_id
In Oracle Learning cloud, Learning Admin can define the Default access at Course or offering level. Course.dat can be used to load this information.
In order to extract this information from DB in HDL format, please use below query:
SELECT 'MERGE' "METADATA"
,'CourseDefaultAccess' "CourseDefaultAccess"
,to_char(wapf.effective_start_date,'RRRR/MM/DD') "EffectiveStartDate"
,to_char(wapf.effective_end_date,'RRRR/MM/DD') "EffectiveEndDate"
,wapf.access_permission_number "DefaultAccessNumber"
,wlif.learning_item_number "CourseNumber"
,wapf.follow_spec "FollowSpecialization"
,wapf.assign_as_manager "MgrInitialStatus"
,wapf.assign_for_self "SelfInitialStatus"
,wlif.ss_view_mode "SelfViewMode"
FROM WLF_ACCESS_PERMISSIONS_F wapf
,WLF_LEARNING_ITEMS_F wlif
WHERE 1=1
AND wapf.access_permission_id= wlif.access_permission_id
--AND trunc(sysdate) between wapf.effective_start_date and wapf.effective_end_date
AND trunc(sysdate) between wlif.effective_start_date and wlif.effective_end_date
AND wlif.learning_item_number = 'OLC251051'
Also, learners can be assigned using Access Groups.
To extract this information below SQL can be used:
SELECT wlif.learning_item_id
,wlif.learning_item_number course_Number
,wlif_tl.name course_Name
,wlif_tl.description_short description
,wlif_ag.learning_item_number access_group_number
,wlif_tl_ag.name access_group_name
FROM wlf_learning_items_f wlif
,wlf_learning_items_f_tl wlif_tl
,wlf_learning_items_f wlif_ag
,wlf_learning_items_f_tl wlif_tl_ag
,wlf_li_ag_relations_f wlarf
WHERE 1=1
AND wlif_tl.learning_item_id = wlif.learning_item_id
AND wlif_tl.name = 'Test 1234'
AND wlif_ag.learning_item_type = 'ORA_ACCESS_GROUP'
AND wlif_tl_ag.learning_item_id = wlif_ag.learning_item_id
AND wlarf.access_learning_item_id = wlif_ag.learning_item_id
AND wlarf.catalog_learning_item_id = wlif.learning_item_id
AND TRUNC(SYSDATE) BETWEEN wlif_tl_ag.effective_start_date AND wlif_tl_ag.effective_end_date
AND TRUNC(SYSDATE) BETWEEN wlif_ag.effective_start_date AND wlif_ag.effective_end_date
AND TRUNC(SYSDATE) BETWEEN wlif_tl.effective_start_date AND wlif_tl.effective_end_date
AND TRUNC(SYSDATE) BETWEEN wlif.effective_start_date AND wlif.effective_end_date
Document of record attachments uploaded by workers in the Fusion HCM resides on UCM server. If there is a requirement to download or view the uploaded attachment on UCM, none of the standard roles like HR Analyst, HR Specialist have the privilege to view the DOR attachments on UCM. To achieve this, a custom role should be created. Please follow the below mentioned steps:
Create a new abstract role ‘XXX Read Attachments’ and add ‘AttachmentsRead’ standard role to it.
2. Once the role is ready, assign it to the required User and run ‘Import User and Role Application Security Data’ process.
3. On successful completion of the process, the user will be able to search any of the document of record attachment using content ID in UCM server and will be able to download the same.
When an activity in learning is created with a start time and end time, the time is stored in UTC format in the data base. When any reporting is done on this data, the output is incorrect as time is shown in UTC format which is different from user time format.
This is applicable for all transactions. Please use the below workaorund to get the correct date time in the SQL:
select wlif.start_date
,wlaf.time_zone
,TZ_OFFSET(wlaf.time_zone) time_zone_offset
,(FROM_TZ(CAST(wlif.start_date AS TIMESTAMP),(case when TZ_OFFSET(wlaf.time_zone) like '+%'
then '-'||substr(TZ_OFFSET(wlaf.time_zone),2)
else '+'||substr(TZ_OFFSET(wlaf.time_zone),2)
end
))) actual_start_date
,TO_CHAR(CAST(wlif.start_date AT TIME ZONE wlaf.time_zone AS DATE),'HH:Mi AM','nls_date_language=American') actual_start_time
from wlf_learning_items_f wlif
, wlf_li_activities_f wlaf
where learning_item_number= 'OLC245017'
and wlif.learning_item_id = wlaf.learning_item_id
and trunc(sysdate) between wlif.effective_start_date and wlif.effective_end_date
and trunc(sysdate) between wlaf.effective_start_date and wlaf.effective_end_date
SELECT 'MERGE' "METADATA"
,'AssignmentGradeSteps' "AssignmentGradeSteps"
,pagsf.EFFECTIVE_START_DATE "EffectiveStartDate"
,pagsf.EFFECTIVE_END_DATE "EffectiveEndDate"
,pagsf.ASSIGN_GRADE_STEP_ID "AssignGradeStepId"
,paam.assignment_number "AssignmentNumber"
,pav.ACTION_CODE "ActionCode"
,parv.ACTION_REASON_CODE "ReasonCode"
,pgsfv.name "GradeStepName"
,NULL "NewGradeStepName" --> to be supplied
FROM PER_ASSIGN_GRADE_STEPS_F pagsf,
PER_GRADE_STEPS_F_VL pgsfv,
PER_ALL_ASSIGNMENTS_M paam,
PER_ACTION_OCCURRENCES pao,
PER_ACTIONS_VL pav,
PER_ACTION_REASONS_VL parv
WHERE pagsf.GRADE_STEP_ID = pgsfv.GRADE_STEP_ID
AND TRUNC(SYSDATE) BETWEEN pagsf.effective_start_date AND pagsf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pgsfv.effective_start_date AND pgsfv.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND pagsf.assignment_id = paam.assignment_id
AND paam.assignment_type = 'E'
AND paam.effective_latest_change = 'Y'
AND pagsf.ACTION_OCCURRENCE_ID = pao.ACTION_OCCURRENCE_ID
AND pav.action_id = pao.action_id
AND parv.action_reason_id = pao.action_reason_id
For using workers in HRHD, they should be added as resources. However, there are certain cases post worker data load where the workers are not available in the resource directory to be added as resources.
Check the below post on adding workers as resources:
This is mainly due to the reason that post worker load, the workers are not loaded into hz_parties table. ESS Job Person Synchronization should be run post worker load and should be scheduled daily so that the worker data is in sync with hz_parties.
Please refer to the user guide attached in below MOS note:
Post Worker HDL load, the workers should be created as Resources in resource directory so that they can assigned various HR Help Desk roles like Service Admin, Agents etc.
Navigation to Resource Directory:
N -> Others -> Resource Directory
Click on view resources to view the already created resources or create a new resource:
Click on Add icon (+) under Search results to add a worker as resource.
You can either search by Person Name or Registry ID. Registry IDs you can find in hz_parties table.
Check below query to find the link between hz_parties and per_All_people_f table:
Manage Document Type security profile can be used to define a security profile with a list of various documents tagged to it. The documents can either be included or excluded in this security profile.
HDL can be used to mass create and update the document type security profiles.
Check the below post on how to refresh the business objects to download the latest template for DocumentTypeSecurityProfile.dat:
select distinct ic.candidate_number,
ppnfv.full_name,
irv.requisition_number,
ipv.name as phase,
isv.name as state
from irc_requisitions_vl irv,
irc_submissions isub,
irc_phases_vl ipv,
irc_states_vl isv,
per_person_names_f_v ppnfv,
irc_candidates ic
where 1=1
AND irv.REQUISITION_ID (+) = isub.REQUISITION_ID
AND ipv.PHASE_ID (+) = isub.CURRENT_PHASE_ID
AND isv.STATE_ID (+) = isub.CURRENT_STATE_ID
AND isub.PERSON_ID (+) = ic.PERSON_ID
AND ic.PERSON_ID = ppnfv.PERSON_ID
AND ppnfv.NAME_TYPE = 'GLOBAL'
AND TRUNC(SYSDATE) BETWEEN ppnfv.EFFECTIVE_START_DATE AND ppnfv.EFFECTIVE_END_DATE
ORDER BY ic.candidate_number
SELECT
to_char(hapf.Effective_Start_Date,'YYYY/MM/DD') "Effective_Start_Date",
to_char(hapf.Effective_End_Date,'YYYY/MM/DD') "Effective_End_Date",
(SELECT DISTINCT BU_NAME from FUN_ALL_BUSINESS_UNITS_V fun where fun.BU_ID= BUSINESS_UNIT_ID) "BU",
(select DISTINCT hapft.NAME
from HR_ALL_POSITIONS_F_TL hapft
where hapft.position_id=hapf.position_id
AND trunc(sysdate) between trunc(hapft.effective_start_date) and trunc(hapft.effective_end_date)
AND hapft.language = USERENV('LANG')
) "Pos_Name",
hapf.POSITION_CODE "PosCode",
hapf.ACTIVE_STATUS "Active_Inactive",
(select DISTINCT horg.NAME from HR_ALL_ORGANIZATION_UNITS_F_VL horg where horg.ORGANIZATION_ID=hapf.ORGANIZATION_ID
AND trunc(sysdate) between trunc(horg.effective_start_date) and trunc(horg.effective_end_date)) "Dept",
(select pjft.name
from per_jobs_f_tl pjft
WHERE pjft.job_id = hapf.job_id
AND trunc(SYSDATE) BETWEEN pjft.effective_start_date AND pjft.effective_end_date
AND language = USERENV('LANG')) "Job",
(select pj.job_code from per_jobs pj where pj.job_id=hapf.job_id
AND trunc(sysdate) between trunc(pj.effective_start_date) and trunc(pj.effective_end_date)) "JobCode",
(select hlaf.location_code from hr_locations_all_f_vl HLAF where HLAF.location_id=hapf.location_id
AND trunc(sysdate) between trunc(HLAF.effective_start_date) and trunc(HLAF.effective_end_date)
) "Location"
FROM HR_ALL_POSITIONS_F hapf
where TRUNC(SYSDATE) between trunc(hapf.effective_start_date) and trunc(hapf.effective_end_date)
User Interface Text is a tool provided with Oracle Fusion Applications which can be used to replace any words/sentences across the application. For example, you want to rename Worker to Employee, then you can make use of this tool.
To make use of this tool, you have to first create a sandbox and then add User Interface Text tool in it. Then test your changes in sandbox mode. Once you are satisfied, you can publish your changes.
Navigate to User Name -> Settings and Actions -> Administration -> Edit Pages
Click on Activate Sandbox:
Click on ‘Create Sandbox’. Give a name and choose ‘User Interface Text’ tool:
Click on ‘Create and Enter’ button.
Click on User Interface Text:
In the Find text box give the string you want to search and in Replace text box give the text which you want to display and click on Search button:
The result will show all the places where it finds the string:
Check the other tabs as well for results to see the texts which will be replaced:
Click on ‘Replace Strings’ button if you want to proceed ahead with the changes or click on ‘New Search’ to start a new search:
Please note that initial search button will not get enabled unless you supply both (search and replace) values.
Manage Person Name Styles is a task that can be used to configure additional Name attributes for a country if required.
Also, a name component can be made required as per requirement.
In multi country implementations, it is required to know the setup for technical developers as it is hard to check the setup for each country from the UI. In such cases, below SQL can be used to extract the information from backend tables:
SELECT pnsv.legislation_code
,pensv.display_sequence
,pensv.column_name
,pensv.prompt
,pensv.required_flag
FROM PER_EDIT_NAME_SETUP_VL pensv,
PER_NAME_STYLES_VL pnsv
WHERE pensv.name_style_id = pnsv.name_style_id
ORDER BY 1,2
There is a common requirement for Resignation approval workflows where the approval is triggered based on a condition by checking whether the worker has completed the probation period or not. The probation end date attribute is not exposed in BPM. So it becomes little tricky to get the probation end date.
In such cases, days between function can be used to calculate the probation end date based on probation period. The pre-requisite is that the probation period for all workers should be entered with UOM as ‘Days’.
Duration.days between(Task.payload.Worker’s Current Assignment.result.Work Relationship Start Date.toGregorianCalendar(),CurrentDate.date) <= Task.payload.Worker’s Current Assignment.result.Probation PeriodÂ
Use below sample queries to get details of loaded/created content items like – Languages, Degrees etc.
Query to get language details:
Select pr.person_id,
HCITL.NAME lang_name,
(select HRL.RATING_DESCRIPTION
from HRT_RATING_LEVELS_TL HRL
where HRL.RATING_LEVEL_ID = pi.RATING_LEVEL_ID1
and HRL.LANGUAGE = 'US') read_a,
(select HRL.RATING_DESCRIPTION
from HRT_RATING_LEVELS_TL HRL
where HRL.RATING_LEVEL_ID = pi.RATING_LEVEL_ID2
and HRL.LANGUAGE = 'US') write_a,
(select HRL.RATING_DESCRIPTION
from HRT_RATING_LEVELS_TL HRL
where HRL.RATING_LEVEL_ID = pi.RATING_LEVEL_ID3
and HRL.LANGUAGE = 'US') speak_a
from hrt_profiles_vl pr
,hrt_profile_items pi
,hrt_content_types_vl ct
,hrt_content_items_tl hcitl
where pi.profile_id=pr.profile_id
and pi.content_type_id=ct.content_type_id
and ct.content_type_name = 'Languages'
and hcitl.content_item_id = pi.content_item_id
Query to get Degree Details:
select hpb.person_id person_id,
hcitl.name degree,
item_text240_1 major,
est.name school,
item_decimal_1 gpa,
item_date_4 completion_date
from hrt_profiles_b hpb,hrt_profile_items hpi ,hrt_content_types_b hct,hrt_content_items_tl hcitl , hrt_establishments_vl est
where hpb.profile_id = hpi.profile_id
and hpi.content_type_id = hct.content_type_id
and hcitl.content_item_id = hpi.content_item_id
and est.establishment_id= hpi.item_number_9
and hct.context_name = 'DEGREE'
and hcitl.language ='US'
SELECT DISTINCT PAPF.PERSON_NUMBER,
TO_CHAR(HPI.DATE_FROM,'DD-MM-YYYY') DATE_FROM,
To_CHAR( HPI.DATE_TO,'DD-MM-YYYY') DATE_TO,
HRL.RATING_DESCRIPTION
FROM HRT_PROFILES_B HPB
INNER JOIN PER_ALL_PEOPLE_F PAPF ON HPB.PERSON_ID = PAPF.PERSON_ID
INNER JOIN HRT_PROFILE_ITEMS HPI ON HPI.PROFILE_ID = HPB.PROFILE_ID
INNER JOIN HRT_RATING_LEVELS_TL HRL ON HRL.RATING_LEVEL_ID = HPI.RATING_LEVEL_ID1
INNER JOIN HRT_CONTENT_TYPES_B HCT ON HPI.CONTENT_TYPE_ID = HCT.CONTENT_TYPE_ID
WHERE HPB.PROFILE_USAGE_CODE = 'P'
AND HCT.CONTEXT_NAME = 'PERFORMANCE_RATING'
AND HRL.LANGUAGE = 'US'
AND TRUNC(HPI.DATE_FROM) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND TRUNC(PAPF.EFFECTIVE_END_DATE)
ORDER BY PAPF.PERSON_NUMBER,DATE_FROM
