Oracle HCM: February 2023

Tuesday, 28 February 2023

Element Input Value Required Flag Change

You can't update the required indicator for an input value that was saved.

If no element entries were created for the element, you can delete the input value and add it again with the correct setting.

Element Input Value UOM change

You can only update units of measure to another units type that's similar.

For example, you can change from hours in decimal format (1 decimal place) to hours in decimal format (3 decimal places), but you can't change from character format to date format or monetary format.

Thursday, 23 February 2023

Element Entries Sql

*****************Assigned Payroll***********************
select distinct
peo.person_number,
ppr.payroll_relationship_number,
asg.assignment_number,
ppp.payroll_name,
to_char(asg.RELATIONSHIP_GROUP_ID) as payroll_assignment_id,
to_char(pap.assigned_payroll_id) as assigned_payroll_id,
to_char(pap.start_date, 'YYYY/MM/DD') as ppr_start,
to_char(pap.end_date, 'YYYY/MM/DD') as ppr_end,
popm.ORG_PAYMENT_METHOD_NAME,
min(ppp.effective_start_date)||'~'||max(ppp.effective_end_date) payroll_range,
min(pou.effective_start_date)||'~'||max(pou.effective_end_date) usage_range,
min(popm.effective_start_date)||'~'||max(popm.effective_end_date) opm_range
from pay_pay_relationships_dn ppr, /* payroll relationship */
pay_dates rdt,
pay_time_definitions rtd,
per_all_people_f peo,
pay_rel_groups_dn asg,
PAY_ASSIGNED_PAYROLLS_DN pap,
PAY_ALL_PAYROLLS_F ppp,
PAY_ORG_PAY_METHOD_USAGES_F pou,
PAY_ORG_PAY_METHODS_vl popm
where peo.person_number = 'WK_1997'
and ppr.person_id = peo.person_id
and asg.PAYROLL_RELATIONSHIP_ID= ppr.PAYROLL_RELATIONSHIP_ID
and asg.GROUP_TYPE='A'
and asg.PARENT_REL_GROUP_ID = pap.PAYROLL_TERM_ID
and pap.payroll_id = ppp.payroll_id
and pou.payroll_id (+) = pap.payroll_id
and popm.ORG_PAYMENT_METHOD_ID (+) = pou.ORG_PAYMENT_METHOD_ID
--and pap.start_date between ppp.effective_start_date and ppp.effective_end_date
--and pap.start_date between pou.effective_start_date (+) and pou.effective_end_date (+)
--and pou.effective_start_date between popm.effective_start_date (+) and popm.effective_end_date (+)
and rdt.source_id (+) = asg.RELATIONSHIP_GROUP_ID
and rdt.source_type (+) = 'PA'
and rtd.time_definition_id (+) = rdt.time_definition_id
group by peo.person_number,
ppr.payroll_relationship_number,
asg.assignment_number,
ppp.payroll_name,
to_char(asg.RELATIONSHIP_GROUP_ID),
to_char(pap.assigned_payroll_id),
to_char(pap.start_date, 'YYYY/MM/DD'),
to_char(pap.end_date, 'YYYY/MM/DD'),
popm.ORG_PAYMENT_METHOD_NAME
order by peo.person_number,
ppr.payroll_relationship_number,
asg.assignment_number,
ppp.payroll_name,
to_char(asg.RELATIONSHIP_GROUP_ID),
to_char(pap.assigned_payroll_id),
to_char(pap.start_date, 'YYYY/MM/DD'),
to_char(pap.end_date, 'YYYY/MM/DD'),
popm.ORG_PAYMENT_METHOD_NAME

*****************Element setup***********************
select petf.element_type_id
,pettl.element_name
,petf.processing_type Recurring_NonRecurring
,petf.effective_start_date
,petf.effective_end_date
,petf.multiple_entries_allowed_flag
,pivf.base_name
from pay_element_types_f petf
,pay_element_types_tl pettl
,pay_input_values_f pivf
where petf.element_type_id = pettl.element_type_id
and pivf.element_type_id=petf.element_type_id
and pettl.element_name = 'WK_Bonus'
and pettl.language='US'
and pivf.user_enterable_flag='Y'
*****************Element Entries***********************

select aaa.person_number,aaa.element_name,aaa.creator_type,aaa.created_by,aaa.creation_Date,aaa.entry_range,
(select min(date_from)||'~'||max(date_to) from pay_entry_usages where element_entry_id=aaa.element_entry_id) usage_range,
aaa.element_entry_id,
(select --LISTAGG(piv.name, '; ') WITHIN GROUP (ORDER BY piv.display_sequence)
rtrim(xmlserialize(content extract(xmlagg(xmlelement("e", piv.name||'; ') order by piv.display_sequence), '//text()')), ',' )
from PAY_INPUT_VALUES_VL piv
where piv.element_type_id = aaa.element_type_id and aaa.start_Date between piv.effective_start_date and piv.EFFECTIVE_END_DATE) inputs,
(select --LISTAGG(piv.name||'='||peev.screen_entry_value, '; ') WITHIN GROUP (ORDER BY piv.display_sequence)
rtrim(xmlserialize(content extract(xmlagg(xmlelement("e", piv.name||'='||peev.screen_entry_value||'; ') order by piv.display_sequence), '//text()')), ',' )
from PAY_INPUT_VALUES_VL piv,pay_element_entry_values_f peev
where peev.element_entry_id = aaa.element_entry_id and peev.input_value_id = piv.input_value_id
and piv.element_type_id = aaa.element_type_id and aaa.start_Date between piv.effective_start_date and piv.EFFECTIVE_END_DATE
and aaa.start_Date between peev.effective_start_date and peev.EFFECTIVE_END_DATE) input_values,
(select max('PERL:'||prl.payroll_relationship_number||'('||prl.payroll_relationship_id||'); '||'TERM:'||trm.assignment_number||'('||trm.relationship_group_id||'); '||'ASG:'||asg.assignment_number||'('||asg.relationship_group_id||'); ')
from pay_entry_usages peu,
pay_rel_groups_dn asg,
pay_rel_groups_dn trm,
PAY_PAY_RELATIONSHIPS_DN prl
where peu.element_entry_id=aaa.element_entry_id
and peu.payroll_assignment_id=asg.relationship_group_id (+)
and peu.payroll_term_id=trm.relationship_group_id (+)
and peu.payroll_relationship_id = prl.payroll_relationship_id
) Payroll_Employment,
(select max('TERM:'||htrm.assignment_number||'('||htrm.assignment_id||'); '||'ASG:'||hasg.assignment_number||'('||hasg.assignment_id||'); ')
from pay_entry_usages peu,
pay_rel_groups_dn asg,
pay_rel_groups_dn trm,
per_all_assignments_m hasg,
per_all_assignments_m htrm
where peu.element_entry_id=aaa.element_entry_id
and peu.payroll_assignment_id=asg.relationship_group_id (+)
and peu.payroll_assignment_id=trm.relationship_group_id (+)
and hasg.assignment_id (+) = asg.assignment_id
and htrm.assignment_id (+) = trm.term_id
) HR_Employment
from (select peo.person_number,
pet.element_name,
pee.creator_type,
pee.created_by,
pee.creation_Date,
pee.element_entry_id,
pee.element_type_id,
min(pee.effective_start_date) as start_Date,
min(pee.effective_start_date)||'~'||max(pee.effective_end_date) entry_range
from per_all_people_f peo,
pay_element_entries_f pee,
pay_element_types_vl pet
where peo.person_number = 'WK_1997'
and pee.person_id = peo.person_id
and pee.element_type_id = pet.element_type_id
group by peo.person_number,
pet.element_name,
pee.element_type_id,
pee.creator_type,
pee.created_by,
pee.creation_Date,
pee.element_entry_id) aaa
order by aaa.person_number,
aaa.element_name,
aaa.creator_type,
aaa.created_by,
aaa.creation_Date,
aaa.element_entry_id

Elements HDL, Element Input Values HDL, Element Eligibility HDL

PayrollElementDetails.dat

METADATA|PayrollElementDetails|ElementName|ReportingName|Description|LegislativeDataGroupName|PrimaryClassificationName|SecondaryClassificationName|Category|ElementStartDate
METADATA|PayrollElementQuestionnaire|ElementName|LegislativeDataGroupName|RuleCode|Rule|Response
MERGE|PayrollElementDetails|AL HDL Test1|AL HDL Test1|AL HDL Test1|IN Legislative Data Group|Standard Earnings|||1951/01/01
MERGE|PayrollElementQuestionnaire|AL HDL Test1|IN Legislative Data Group|SpecCurrency|Input Currency|Indian Rupee
MERGE|PayrollElementQuestionnaire|AL HDL Test1|IN Legislative Data Group|Availability Rule|Should every person eligible for the element automatically receive it?|No
MERGE|PayrollElementQuestionnaire|AL HDL Test1|IN Legislative Data Group|Hire Process|What is the earliest entry date for this element?|First Standard Earning Date
MERGE|PayrollElementQuestionnaire|AL HDL Test1|IN Legislative Data Group|Terminate Process|What is the latest entry date for this element?|Last Standard Earning Date
MERGE|PayrollElementQuestionnaire|AL HDL Test1|IN Legislative Data Group|Processing Level|At which employment level should this element be attached?|Assignment level
MERGE|PayrollElementQuestionnaire|AL HDL Test1|IN Legislative Data Group|Processing Asg Level|Do you want the element to be processed at Payroll Assignment level?|Yes
MERGE|PayrollElementQuestionnaire|AL HDL Test1|IN Legislative Data Group|Recurring Or NonRecurring|Does this element recur each payroll period, or does it require explicit entry?|Nonrecurring
MERGE|PayrollElementQuestionnaire|AL HDL Test1|IN Legislative Data Group|Multiple Entries|Can a person have more than one entry of this element in a payroll period?|No
MERGE|PayrollElementQuestionnaire|AL HDL Test1|IN Legislative Data Group|CalculationRuleBasic|What is the calculation rule?|Flat amount

PayrollElementDefinition.dat

MERGE|PayrollElementDefinition|AL HDL Test1|1951/01/01||IN Legislative Data Group|AL HDL Test1|AL HDL Test1

MERGE|InputValue|1951/01/01||Test Input Value|Test Input Value|Y|Y|N|Y|Y|AL HDL Test1|100|IN Legislative Data Group|N

MERGE|ElementEligibility|1951/01/01||AL HDL Test1 Elig|N|AL HDL Test1|IN Legislative Data Group|LE_NAME

FTE update HDL

The SET command is required to update the FTE in work measures table when changing the working hours of an employee. IF you change it without the SET command the hours get updated but not the FTE.

SET CALCULATE_FTE Y
METADATA|WorkTerms|AssignmentNumber|PersonNumber|LegalEmployerName|DateStart|WorkerType|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|ActionCode|NormalHours
MERGE|WorkTerms|ET29611|29611|LE_NAME|2009/01/19|E|2022/08/01||1|Y|ASG_CHANGE|20
METADATA|Assignment|WorkTermsNumber|PersonNumber|AssignmentNumber|LegalEmployerName|DateStart|WorkerType|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|ActionCode|EffectiveLatestChange|NormalHours
MERGE|Assignment|ET29611|29611|E29611|LE_NAME|2009/01/19|E|2022/08/01||1|ASG_CHANGE|Y|20

Element HDL Template Generator Report

Hi Everyone,

I was trying to create an element using HDL but it was not created as all the Questions in the element questionaire were not being passed. There is a wonderful post on customer connect to generate the HDL file from a sample element created in the application which has resolved my issue. Thanks.

https://community.oracle.com/customerconnect/discussion/505001/element-hdl-template-generator-report

The value CalculationRule isn't valid for the attribute RuleName.

Hi Everyone,

I was trying to create an element using but I was facing the below issue.

The value CalculationRule isn't valid for the attribute RuleName.

The reason I was facing this is I missed a Question in the questionnare part of the HDL.

The below 2 lines need to be added instead of just the first line which is visible in the UI on the summary page when we are submitting the element for creation. Only the first one was visible, but HDL needed the second line also to be passed along with the first line.

Sample HDL

MERGE|PayrollElementDetails|AL HDL Test1|AL HDL Test1|AL HDL Test1|IN Legislative Data Group|Standard Earnings|||1951/01/01

The values Earnings aren't valid for the attribute PrimaryClassificationId.

Hi Everyone,
I was facing an issue trying to load the elements using HDL. I was getting below error.
"The values Earnings aren't valid for the attribute PrimaryClassificationId."
The issue was being caused as the base classification name has to be passed instead of the classification name in the HDL. In my case I was passing Earnings instead of Standard Earnings.

Sample HDL below.
METADATA|PayrollElementDetails|ElementName|ReportingName|Description|LegislativeDataGroupName|PrimaryClassificationName|SecondaryClassificationName|Category|ElementStartDate
METADATA|PayrollElementQuestionnaire|ElementName|LegislativeDataGroupName|RuleCode|Rule|Response
MERGE|PayrollElementDetails|AL HDL Test1|AL HDL Test1|AL HDL Test1|IN Legislative Data Group|Standard Earnings|||1951/01/01
MERGE|PayrollElementQuestionnaire|AL HDL Test1|IN Legislative Data Group|SpecCurrency|Input Currency|Indian Rupee
MERGE|PayrollElementQuestionnaire|AL HDL Test1|IN Legislative Data Group|Availability Rule|Should every person eligible for the element automatically receive it?|No
MERGE|PayrollElementQuestionnaire|AL HDL Test1|IN Legislative Data Group|Hire Process|What is the earliest entry date for this element?|First Standard Earning Date
MERGE|PayrollElementQuestionnaire|AL HDL Test1|IN Legislative Data Group|Terminate Process|What is the latest entry date for this element?|Last Standard Earning Date
MERGE|PayrollElementQuestionnaire|AL HDL Test1|IN Legislative Data Group|Processing Level|At which employment level should this element be attached?|Assignment level
MERGE|PayrollElementQuestionnaire|AL HDL Test1|IN Legislative Data Group|Processing Asg Level|Do you want the element to be processed at Payroll Assignment level?|Yes
MERGE|PayrollElementQuestionnaire|AL HDL Test1|IN Legislative Data Group|Recurring Or NonRecurring|Does this element recur each payroll period, or does it require explicit entry?|Nonrecurring
MERGE|PayrollElementQuestionnaire|AL HDL Test1|IN Legislative Data Group|Multiple Entries|Can a person have more than one entry of this element in a payroll period?|No
MERGE|PayrollElementQuestionnaire|AL HDL Test1|IN Legislative Data Group|CalculationRuleBasic|What is the calculation rule?|Flat amount

Wednesday, 8 February 2023

BI Publisher- Excel Template - Print rows conditionally

When using excel template in BI Publisher, there are times when we want to print certain rows in output file based on the value of a column. Instead of the normal group tag, add the group tag as below in XDO_METADATA sheet.

Sample XML Data:
<DATA_DS>
<G_1>
<emp_no>101</emp_no>
<emp_name>Arjun</emp_name>
<ENROLLED>yes</ENROLLED>
</G_1>
<G_1>
<emp_no>102</emp_no>
<emp_name>Bharat</emp_name>
<ENROLLED>no</ENROLLED>
</G_1>
<G_1>
<emp_no>103</emp_no>
<emp_name>Charan</emp_name>
<ENROLLED>no</ENROLLED>
</G_1>
<G_1>
<emp_no>104</emp_no>
<emp_name>Dinesh</emp_name>
<ENROLLED>yes</ENROLLED>
</G_1>
</DATA_DS>

XML- XSLT transformation online

Below website is very useful to check if your XSL template is transforming the XML as required.

https://www.freeformatter.com/xsl-transformer.html

Tuesday, 28 February 2023

Thursday, 23 February 2023

Wednesday, 8 February 2023

Pageviews last month