Enable Advanced Edit in HCM Extracts- After 24A update
After 24A update Oracle deprecated the advanced edit in HCM Extracts. To Enable the advanced edit we have to do the below steps:
1. Go to Setup and Maintenance
2. open the task Manage Common Lookups
3. Search lookup type 'ORA_PER_EXT_CONFIG'
4. Add lookup code 'EXT_PUI_ENABLE' and Give meaning 'YES'
This is not a permanent solution. After few updates this also going to deprecate.
Thanks
Sainath
SQL Hints for dashboard
#{securityContext.userInRole['']}
/xmlpserver/Custom/RPT.xdo?&_xpt=0&_xmode=2
SQL function to get the employee schedule details
To get the employees availability as per their schedule using the below function:
TABLE(per_availability_details.get_schedule_details(p_resource_type => 'ASSIGN', p_resource_id => <assignment_id> ,p_period_start => <Start_date> ,p_period_end => <End_date> ))
Example:
SELECT
*
FROM
TABLE(per_availability_details.get_schedule_details(p_resource_type => 'ASSIGN', p_resource_id => 300000008682319 ,p_period_start => to_date('17-10-2021','DD-MM-YYYY') ,p_period_end => to_date('23-10-2021','DD-MM-YYYY') ))
WHERE
availability_code = 'AVL'
Thanks
Sainath
Global Transfer - HDL
Termination of existing work relationship within a legal
entity and creation of a new work relationship with a new legal entity is called
as Global Transfer.
To do Global Transfer we need below 3 METADATAs:
1. Work Relationship
2. Work Terms (In New work relationship)
3. Assignment (In New
work relationship)
We should include GlobalTransferFlag
as Y and an action code from the EMPL_GLB_TRANSFER action type in the new work relationship.
Here we don’t need to include the old WorkRelationship for
termination, it will automatically be terminated. The termination date of the
existing work relationship is one day before the start date of the new work
relationship.
Note: (Need to check the Data consistency)
1.
Active an employee can’t be with a non-primary Work relationship.
2.
We
can’t do the Global Transfer on the primary work relationship if the employee has
two active work relationships. (Need to make the primary work relationship
non-primary before doing the global transfer)
Hire Records:
METADATA|Worker|PersonNumber|StartDate|EffectiveStartDate|DateOfBirth|ActionCode
MERGE|Worker|Employee1|2018/01/01|2018/01/01|1992/06/17|HIRE
METADATA|PersonName|PersonNumber|EffectiveStartDate|LegislationCode|NameType|FirstName|MiddleNames|LastName
MERGE|PersonName|Employee1|2018/01/01|US|GLOBAL|S|N|R
METADATA|WorkRelationship|PersonNumber|DateStart|PrimaryFlag|LegalEmployerName|WorkerType
MERGE|WorkRelationship|Employee1|2018/01/01|Y|Legal_employer|E
METADATA|WorkTerms|AssignmentNumber|PersonNumber|LegalEmployerName|DateStart|EffectiveStartDate|EffectiveSequence|EffectiveLatestChange|ActionCode|PersonTypeCode|JobCode|WorkerType|BusinessUnitShortCode|SystemPersonType|AssignmentName|AssignmentType|AssignmentStatusTypeCode
MERGE|WorkTerms|ETEmployee1|Employee1|Legal_employer|2018/01/01|2018/01/01|1|Y|HIRE|Employee||E|Business_unit|EMP|Work_terms_Assign_name|ET|ACTIVE_PROCESS
METADATA|Assignment|AssignmentNumber|WorkTermsNumber|PersonNumber|LegalEmployerName|DateStart|EffectiveStartDate|EffectiveSequence|EffectiveLatestChange|ActionCode|PersonTypeCode|JobCode|BusinessUnitShortCode|WorkerType|SystemPersonType|AssignmentName|AssignmentType|AssignmentStatusTypeCode
MERGE|Assignment|EEmployee1|ETEmployee1|Employee1|Legal_employer|2018/01/01|2018/01/01|1|Y|HIRE|Employee||Business_unit|E|EMP|assignment_name|E|ACTIVE_PROCESS
Global Transfer records:
METADATA|WorkRelationship|PersonNumber|DateStart|PrimaryFlag|LegalEmployerName|WorkerType|GlobalTransferFlag
MERGE|WorkRelationship|Employee1|2019/05/10|Y|New_Legal_Employer|E|Y
METADATA|WorkTerms|AssignmentNumber|PersonNumber|LegalEmployerName|DateStart|EffectiveStartDate|EffectiveSequence|EffectiveLatestChange|ActionCode|PersonTypeCode|JobCode|WorkerType|BusinessUnitShortCode|SystemPersonType|AssignmentName|AssignmentType|AssignmentStatusTypeCode
MERGE|WorkTerms|ETEmployee1|Employee1|Legal_employer|2018/01/01|2019/05/10|1|Y|GLB_TRANSFER|Employee||E|Business_unit|EMP|Work_terms_Assign_name|ET|ACTIVE_PROCESS
METADATA|Assignment|AssignmentNumber|WorkTermsNumber|PersonNumber|LegalEmployerName|DateStart|EffectiveStartDate|EffectiveSequence|EffectiveLatestChange|ActionCode|PersonTypeCode|JobCode|BusinessUnitShortCode|WorkerType|SystemPersonType|AssignmentName|AssignmentType|AssignmentStatusTypeCode
MERGE|Assignment|EEmployee1|ETEmployee1|Employee1|Legal_employer|2018/01/01|2019/05/10|1|Y|GLB_TRANSFER|Employee||Business_unit|E|EMP|assignment_name|E|ACTIVE_PROCESS
HDL File for Compensatory plan Balances Load
To load the Compensatory plan balances use the below HDL.
The accrual Type for compensatory plans: ORA_ANC_COMPTME_DL
HDL: (PersonAccrualDetail.dat)
METADATA|PersonAccrualDetail|PersonNumber|WorkTermsNumber|PlanName|ProcdDate|AccrualType|Value
MERGE|PersonAccrualDetail|2345|ET2345|PlanName|2020/05/08|ORA_ANC_COMPTME_DL|1
Thanks
Sainath
Error - Employment level entered is not eligible for the element Statutory Deductions
When hiring an employee if the below error occurs. Then we have to follow a few steps to avoid this error.
ERROR:
The message is ORA-20001: The employment level entered is not eligible for the element Statutory Deductions on YYYY-MM-DD. Details: You cannot grant the element to the payroll relationship, terms or assignment unless it meets the element eligibility criteria and has an element costing record. Ensure that a suitable element costing rule exists for the element, and that the eligibility criteria for a person matches those defined on the costing rule. ORA-06512: at "FUSION.PAY_ELEMENT_ENTRY_API", line 468 ORA-06512: at line 53 .
To avoid this error we have to follow the below steps:
1. Set up and maintenance --> Elements
2. Search for the "Statutory Deductions" Salary Element
3. Click on Statutory Deductions
4. Please make sure the effective as-of date is correct
4. Select "Element Eligibility" on the left-hand side and click on the Actions dropdown
5. Click on Create Element Eligibility
6. Fill in all the required information and submit.
Thanks
Sainath
Credential sent Process to employees/Users in oracle cloud HCM
To send the credentials email notification or resend the welcome mail, need to follow the below steps:
1. Use the CredentialsEmailSent flag to No in User.dat HDL for the employees we want to send notifications to.
User.dat
METADATA|User|PersonNumber|CredentialsEmailSent
MERGE|User|1234|N
2. Run the schedule process "Send User Name and Password E-Mail Notifications"
To Stop credential email notification or sending welcome mail for the employees, need to follow the below steps:
1. Use the CredentialsEmailSent flag to Yes in User.dat HDL for the employees we want to stop notifications.
User.dat
METADATA|User|PersonNumber|CredentialsEmailSent
MERGE|User|1234|Y
2. Run the schedule process "Send User Name and Password E-Mail Notifications" which will send a notification to only Employees with CredentialsEmailSent set to N
Thanks
Sainath
BIP Refresh Process
Login with a BI Administrator user and execute the following steps in order to clear the BI Cache:
1. Clear BI Publisher Cache:
Administration --> BI Publisher --> Manage BI Publisher --> System Maintenance --> Manage Cache --> Clear Object Cache
2. Clear cache for the integration between BI Publisher and OTBI
Administration-- > BI Publisher --> Manage BI Publisher --> Integration --> Oracle BI Presentation Services --> Clear the BI Subject Area Metadata Cache
3. Clear OTBI Cache:
Administration-- > Issue SQL --> type : "Call SAPurgeAllCache();" --> Click "Issue SQL" (you should see a message like "Operation SAPurgeAllCache succeeded!")
4. OTBI Reload Files and Metadata:
Administration-- > Maintenance and Troubleshooting --> Reload Files and Metadata
5. Logout --> Clear browser cache --> Restart browser
6. Login and retest
Thanks
Sainath
Current User Person ID - SQL
Below is the SQL to get the login / Session user person Id in oracle cloud HCM :
HRC_SESSION_UTIL.GET_USER_PERSONID
Ex:
SELECT HRC_SESSION_UTIL.GET_USER_PERSONID FROM DUAL
Thanks
Sainath
SQL to get the UDT value
select
fuci.value
from
ff_user_tables fut
,ff_user_column_instances_f fuci
,ff_user_columns fuc
,ff_user_columns_tl fuct
,ff_user_rows_f furf
where 1=1
and fut.user_table_id = fuc.user_table_id
and fuc.user_column_id = fuct.user_column_id
and fut.user_table_id = furf.user_table_id
and fuci.user_row_id = furf.user_row_id
and fuci.user_column_id = fuct.user_column_id
and fut.BASE_USER_TABLE_NAME ='TABLE_NAME'
and fuct.USER_COLUMN_NAME IN ('COLUMN_NAME')
and furf.ROW_LOW_RANGE_OR_NAME IN ('ROW_NAMES')
--and to_number(:{PARAMETER.value}) between (cast(furf.ROW_LOW_RANGE_OR_NAME as float)) and furf.ROW_HIGH_RANGE
--and to_number(:{PARAMETER.value}) between furf.ROW_LOW_RANGE_OR_NAME and furf.ROW_HIGH_RANGE
order by furf.DISPLAY_SEQUENCE
Thanks
Sainath
UDT Table:
SELECT
fut.user_table_name
,fut.base_user_table_name
,fut.range_or_match
,fut.user_key_units
,fut.user_table_id
p_user_table_id
,ldg.name ldg_name
,'UserDefinedTable'
metadata
FROM
ff_user_tables_vl fut
,per_legislative_data_groups_vl
ldg
WHERE 1=1
--AND
fut.legislative_data_group_id = ldg.legislative_data_group_id
AND fut.user_table_id =
:P_UDT
UDT Column:
SELECT
fuc.user_column_name
,fuc.base_user_column_name
,fuc.data_type
,fuc.formula_id
,'UserDefinedTableColumn'
metadata
FROM
ff_user_columns_vl fuc
WHERE 1=1
AND fuc.user_table_id =
:P_USER_TABLE_ID
UDT Rows:
SELECT
fur.row_name
,fur.display_sequence
,TO_CHAR(fur.effective_start_date,'YYYY/MM/DD')
row_effective_start_date
,TO_CHAR(fur.effective_end_date,'YYYY/MM/DD')
row_effective_end_date
-- ,fur.row_high_range
--
,fur.row_low_range_or_name
,DECODE(fut.range_or_match,'M','',fur.row_high_range) row_high_range
,DECODE(fut.range_or_match,'M',fur.row_name,fur.row_low_range_or_name) row_low_range_or_name
,'UserDefinedTableRow'
metadata
FROM
ff_user_tables_vl fut
,ff_user_rows_vl fur
WHERE 1=1
AND fur.user_table_id =
:P_USER_TABLE_ID
AND fut.user_table_id =
fur.user_table_id
Column Instance:
SELECT
'UserDefinedTableColumnInstance'
metadata
,fut.user_table_name
,fut.base_user_table_name
,fut.range_or_match
,fut.user_key_units
,display_sequence
,fur.row_name
,TO_CHAR(fur.effective_start_date,'YYYY/MM/DD')
row_effective_start_date
,TO_CHAR(fur.effective_end_date,'YYYY/MM/DD')
row_effective_end_date
,fur.row_high_range
,fur.row_low_range_or_name
,fuc.user_column_name
,fuc.base_user_column_name
,fuc.data_type
,fuc.formula_id
,fuci.value udt_value
,TO_CHAR(fuci.effective_start_date,'YYYY/MM/DD')
col_inst_eff_start_date
,TO_CHAR(fuci.effective_end_date,'YYYY/MM/DD')
col_inst_eff_end_date
FROM
fusion.ff_user_tables_vl fut
,fusion.ff_user_columns_vl
fuc
,fusion.ff_user_rows_vl
fur
,fusion.ff_user_column_instances_f
fuci
WHERE 1=1
--AND
(fut.legislative_data_group_id = :P_LDG OR :P_LDG IS NULL)
AND fut.user_table_id =
:P_USER_TABLE_ID
AND fut.user_table_id =
fuc.user_table_id
AND fut.user_table_id =
fur.user_table_id
AND TRUNC(SYSDATE) BETWEEN
fur.effective_start_date AND fur.effective_end_date
AND fuc.user_column_id =
fuci.user_column_id
AND fur.user_row_id =
fuci.user_row_id
AND TRUNC(SYSDATE) BETWEEN
fuci.effective_start_date AND fuci.effective_end_date
Parameter(P_USER_TABLE_ID) LOV
SQL for above SQL:
SELECT
user_table_name
,user_table_id
FROM
ff_user_tables_vl
UDT HDL Format Report:
https://drive.google.com/file/d/1gnkepEqUgAQj657U0K20Owl-l_Ui4BZV/view?usp=sharing
Thanks
Sainath
