Use the below query to extract list of “Extract Rule Type” fast formulas used in Oracle HCM Extracts:
select pedv.definition_name
,ffv.formula_name
from per_ext_data_elements_vl pedev
,pay_report_records_f prrf
,pay_report_blocks prb
,per_ext_definitions_vl pedv
,ff_formulas_vl ffv
where pedev.report_record_id = prrf.report_record_id
and prrf.report_block_id = prb.report_block_id
and prb.ext_definition_id = pedv.ext_definition_id
and pedv.definition_name LIKE 'Dec%Absence%'
and ffv.formula_id = pedev.rule_id
Sometime, while configuring the SFTP server, the user gets below error:
The cause of this issue is incorrect SFTP details entered. Make sure all the details are correctly entered. Once the SFTP details are entered correctly, the error will fade away.
In this article, we will see how to setup an SFTP server which will use private key for authentication. First step is to obtain the private key. Once you have the key, login into Oracle Fusion Application Analytics and click on Administration link on the right top side:
Click ‘Manage BI Publisher’ under BI Publisher on Administration Page:
Click on ‘Upload Center’ under ‘System Maintenance’:
Upload the key file with File Type as SSH Private Key:
Once the key is uploaded successfully. Return back.
Click on FTP under Delivery and click on Add Server:
Excel output in Fusion BIP can be generated using multiple ways i.e. either using excel template or RTF templates. When you use RTF templates to generate excel output, by default the data is generated in sheet with name “Sheet1:
If you want to rename Sheet1 to user defined value, then use the below expression in your RTF template:
<?spreadsheet-sheet-name: {‘Test’}?>
If you want it be dynamic, use below expression:
<?spreadsheet-sheet-name: {.//NAME}?>
Make the changes in RTF template and upload. The output file generated will have sheet name as Test.
Many a time there is a requirement to update/change the existing actions and actions reasons usages. Doing this manually in UI will become a cumbersome process for multiple actions and may lead to human errors as well.
For this, HCM Data Loader can used to update the changes using Actions.dat file.
Use the below SQL queries to extract Actions and Action Reason Usages data from your Pod in HDL format:
Actions:
Select 'MERGE|Actions|'
||
actb.action_code
||'|'||
actt.action_name
||'|'||
actb.action_type_code
||'|'||
to_char(actb.start_date, 'yyyy/mm/dd')
||'|'||
to_char(actb.end_date, 'yyyy/mm/dd')
||'|'||
map.source_system_id
||'|'||
map.source_system_owner ACTIONS_HDL
from
PER_ACTIONS_B actb,
PER_ACTIONS_TL actt,
hrc_integration_key_map map
where 1=1
and actb.action_id = actt.action_id
and actt.language = USERENV('LANG')
and actb.action_id = map.surrogate_id
Action Reason Usage:
Select 'MERGE|ActionReasonUsage|'
||
aru.action_code
||'|'||
aru.action_reason_code
||'|'||
to_char(aru.start_date, 'yyyy/mm/dd')
||'|'||
to_char(aru.end_date, 'yyyy/mm/dd')
||'|'||
km.source_system_id
||'|'||
km.source_system_owner ARC_HDL
from
hrc_integration_key_map km,
PER_ACTION_REASON_USAGES aru
where 1=1
and aru.ACTION_REASON_USAGE_ID = km.surrogate_id
Copy and save the data as Actions.dat and do the required changes.
When designing BIP reports, sometimes the developer is encountered with “Report Cannot Be Rendered Error”. There can be various reasons of this error. The main reason behind this error is incorrect configuration.
To resolve this issue for excel templates, make sure that there are no invalid references under Name Manager.
Navigate to Formulas -> Name Manager and make sure there are no fields with invalid references. If there exist a field with invalid reference, delete the reference, save your template and upload again.
In the below example, FIELD1 has invalid reference:
You can test if the template is rendering proper data or not by uploading the sample xml data before actually uploading the file to BIP.
With incorrect reference, you will get below error:
Third party payslip data can be loaded into Payroll Inbound Records in Fusion HCM. Use below query to extract the data:
select distinct hpir.inbd_record_id
,to_char(hpir.start_date,'RRRR/MM/DD', 'nls_date_language=American') startDate
,hpir.entity_identifier employeeNumber
,hpir.payroll_id
,hpir.time_period_id
,hpir.person_id
,(select pldft.LOCATION_NAME
from per_location_details_f pldf,PER_LOCATION_DETAILS_F_TL pldft
where pldf.location_id = paam.location_id
and pldft.location_details_id = pldf.location_details_id
and trunc(SYSDATE) BETWEEN pldf.effective_start_date AND pldf.effective_end_date
and trunc(SYSDATE) BETWEEN pldft.effective_start_date AND pldft.effective_end_date) location_name
,(select hauft.NAME
from HR_ORG_UNIT_CLASSIFICATIONS_F houcf,
HR_ALL_ORGANIZATION_UNITS_F haouf,
HR_ORGANIZATION_UNITS_F_TL hauft
where hauft.organization_id = paam.legal_entity_id
AND haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID
AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE
AND hauft.LANGUAGE = 'US'
AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE
AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'HCM_LEMP'
AND trunc(SYSDATE) BETWEEN hauft.effective_start_date AND hauft.effective_end_date) le
,(select TO_CHAR(ptp.end_date,'DD-MON-RRRR', 'nls_date_language=American') from pay_time_periods ptp
where ptp.time_period_id = hpir.time_period_id) periodEndDate
,ppnf.first_name||' '||ppnf.last_name employeeName
,(select meaning from fnd_lookup_values
where lookup_type = 'EMP_CAT'
and lookup_code = paam.employment_category) catgr
,pp.attribute1
,pp.attribute2
,pni.national_identifier_number
from hry_pi_inbd_records hpir,
per_all_assignments_m paam,
per_person_names_f ppnf,
per_persons pp,
per_national_identifiers pni
where hpir.person_id = paam.person_id
and hpir.person_id = ppnf.person_id
and hpir.person_id = pp.person_id
and hpir.person_id = pni.person_id
and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
and assignment_type not like 'ET%'
and paam.ACTION_CODE <> 'TERMINATION'
/* and hpir.person_id = (select pu.person_id
from per_users pu
where pu.user_guid=fnd_global.user_guid)*/
Select DISTINCT papf_emp.person_number employee_number,
ppnf.first_name||' '||ppnf.last_name employee_name,
(select hapft.name
from hr_all_positions_f hapf, HR_ALL_POSITIONS_F_TL hapft
WHERE hapf.position_id = paam.position_id
AND hapf.position_id = hapft.position_id
AND trunc(SYSDATE) BETWEEN hapft.effective_start_date AND hapft.effective_end_date
AND trunc(SYSDATE) BETWEEN hapf.effective_start_date AND hapf.effective_end_date) employee_position,
(select hapft.name
from hr_all_positions_f hapf, hr_all_positions_f_tl hapft,per_position_hierarchy_f pphf
WHERE hapf.position_id = pphf.parent_position_id
AND pphf.position_id=paam.position_id
AND hapf.position_id = hapft.position_id
AND trunc(SYSDATE) BETWEEN hapft.effective_start_date AND hapft.effective_end_date
AND trunc(SYSDATE) BETWEEN pphf.effective_start_date AND pphf.effective_end_date
AND trunc(SYSDATE) BETWEEN hapf.effective_start_date AND hapf.effective_end_date) employee_position_parent
from per_all_assignments_m paam,
per_all_people_f papf_emp,
per_person_names_f ppnf
where papf_emp.person_id = paam.person_id
and papf_emp.person_id = ppnf.person_id
and trunc(sysdate) between papf_emp.effective_start_date and papf_emp.effective_end_date
and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
and ppnf.name_type ='GLOBAL'
and paam.assignment_type ='E'
and paam.assignment_status_type = 'ACTIVE'
--and paam.position_id = 300000009196111
order by papf_emp.person_number
SELECT csb.name
,csb.salary_basis_code
,csb.salary_annualization_factor
,pldgt.name legislative_dg
,petft.element_name
,pivt.name input_val_name
,prft.name rate_name
FROM cmp_salary_bases csb
,per_legislative_data_groups_tl pldgt
,pay_element_types_tl petft
,pay_input_values_f pivf
,pay_input_values_tl pivt
,per_rates_f_tl prft
WHERE csb.legislative_data_group_id = pldgt.legislative_data_group_id
AND pldgt.language = USERENV('LANG')
AND csb.element_type_id = petft.element_type_id
AND petft.language = USERENV('LANG')
AND csb.element_type_id = pivf.element_type_id
AND csb.input_value_id = pivf.input_value_id
AND trunc(sysdate) between pivf.effective_start_date and pivf.effective_end_date
AND pivf.input_value_id = pivt.input_value_id
AND pivt.language = USERENV('LANG')
AND csb.grade_rate_id = prft.rate_id(+)
AND NVL(prft.language,USERENV('LANG')) = USERENV('LANG')
Once a worker is terminated in Oracle HCM Cloud, one would expect that associated User account to be inactive as well. For this to happen automatically, autoprovisioning rules should be setup which will remove the associated roles to the terminated worker’s user. In case, there is even a single role attached to the user, the user will show as active in Security Console.
From the backend, Suspended attribute in PER_USERS table is mapped to active checkbox in UI. If the user is active, the suspended flag will hold N value. For inactive users, the value of this attribute will be Y.
You can use below query to get the list of terminated workers for whom the user is still active:
SELECT papf.person_number,pu.username
FROM per_all_people_f papf
,per_all_assignments_m paam
,per_users pu
WHERE papf.person_id = paam.person_id
AND paam.effective_latest_change = 'Y'
AND paam.effective_sequence = 1
AND paam.assignment_status_type like 'INACTIVE%'
AND paam.assignment_type NOT LIKE '%T'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND papf.person_id = pu.person_id
AND pu.suspended = 'N'
SELECT paam.assignment_number
,paam.labour_union_member_flag
,houft.name
FROM per_all_assignments_m paam
,hr_organization_units_f_tl houft
WHERE paam.assignment_type ='E'
AND paam.union_id = houft.organization_id
AND houft.language = 'US'
AND paam.effective_latest_change = 'Y'
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND TRUNC(SYSDATE) BETWEEN houft.effective_start_date AND houft.effective_end_date
select papf.person_number,
ppnf.full_name
from per_All_people_f papf
,per_person_names_f ppnf
where papf.person_id =ppnf.person_id
and ppnf.name_type= 'GLOBAL'
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
and papf.person_id = HRC_SESSION_UTIL.GET_USER_PERSONID
SELECT 'METADATA'
,'ContentItem'
,hctb.content_type_id
,hctb.context_name
,hcibt.name
,hctvt.value_set_name
,hg.geography_name
,hcibt.item_description
,to_char(hcib.date_from,'yyyy/mm/dd')
,hg.geography_code
,hcib.content_item_code
,to_char(hcib.date_to,'yyyy/mm/dd')
,hrmb.rating_model_code
,hrmb.rating_model_id
,hikm.source_system_id
,hikm.source_system_owner
FROM HRT_CONTENT_ITEMS_B hcib
,HRT_CONTENT_ITEMS_TL hcibt
,HRT_CONTENT_TYPES_B hctb
,HZ_GEOGRAPHIES hg
,HRT_RATING_MODELS_B hrmb
,HRT_CONTENT_TP_VALUESETS_TL hctvt
,HRC_INTEGRATION_KEY_MAP hikm
where hcib.CONTENT_ITEM_ID=hcibt.CONTENT_ITEM_ID
AND hcibt.LANGUAGE=userenv('LANG')
AND hcib.CONTENT_TYPE_ID=hctb.CONTENT_TYPE_ID(+)
AND hikm.surrogate_id = hcib.CONTENT_ITEM_ID
AND hcib.COUNTRY_ID=hg.GEOGRAPHY_ID(+)
AND hg.GEOGRAPHY_TYPE(+)='COUNTRY'
AND trunc(hcib.DATE_FROM) between hg.START_DATE(+) and nvl(hg.END_DATE(+),to_date('4712/12/31','YYYY/MM/DD'))
AND hcib.RATING_MODEL_ID=hrmb.RATING_MODEL_ID(+)
AND trunc(hcib.DATE_FROM) between hrmb.DATE_FROM(+) and nvl(hrmb.DATE_TO(+),to_date('4712/12/31','YYYY/MM/DD'))
AND hcib.CONTENT_VALUE_SET_ID=hctvt.CONTENT_VALUE_SET_ID(+)
AND hctvt.LANGUAGE(+)=userenv('LANG')
AND hctb.CONTEXT_NAME = 'LANGUAGE'
There is one common requirement where Employees should access reports directly from self-service instead of navigating to analytics. In such cases, a report link can be created and added as a static link on Navigator. This will enable the employee to access report directly. The report can be then viewed in different views using different parameters in report link.
Steps to enable report link on self-service:
Derive the report link. Navigate to analytics and open the report:
Each person can publish public info in HCM Cloud by navigating to Me -> Quick Actions -> Public Info. There are various sections under Public Info like Public Message, Contact Info, About Me etc.
You can use the below SQL queries to extract the data for relevant sections.
Public Message:
SQL Query:
SELECT papf.person_number
,ppnf.full_name
,ppmv.PORTRAIT_MESSAGE_CONTENT
,ppmv.TIME_FROM
,ppmv.TIME_TO
FROM per_all_people_f papf
,per_person_names_f ppnf
,per_portrait_messages_vl ppmv
WHERE papf.person_id = ppnf.person_id
AND ppnf.name_type = 'GLOBAL'
AND papf.person_id = ppmv.target_person_id
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
About Me:
SQL Query:
select papf.person_number
,ppnf.full_name
,hpv.summary about_me
,hpkaoe.keywords area_of_expertise
,regexp_replace(regexp_replace(regexp_replace(hpkaoe.KEYWORDS, '</p><br/>'), '</p>'),'<p>') as Area_Of_Expert
ise_wo_html
,hpkaoi.keywords area_of_interest
,regexp_replace(regexp_replace(regexp_replace(hpkaoi.KEYWORDS, '</p><br/>'), '</p>'),'<p>') as area_of_interest_wo_html
from PER_ALL_PEOPLE_F papf,
PER_PERSON_NAMES_F ppnf,
HRT_PROFILES_VL hpv,
HRT_PROFILE_KEYWORDS hpkaoe,
HRT_PROFILE_KEYWORDS hpkaoi
where hpv.profile_id = hpkaoe.profile_id
and hpkaoe.profile_id = hpkaoi.profile_id
and hpkaoe.keyword_type = 'AOE'
and hpkaoi. keyword_type = 'AOI'
and papf.person_id = hpv.person_id
and papf.person_id = ppnf.person_id
and ppnf.name_type = 'GLOBAL'
and TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
and TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
