Total Pageviews

Monday, June 22, 2020

Element HDL Template Generator



Element HDL Template Generator

In most of the cases we create the elements manually in dev/Test instance those we need to move to production for that again, we must create it manually in Prod. In those case for migration we can use this Element HDL Template generator.
By this we can move the data in very less time and more accurately to production.

Parameters for this template are Element Name and LDG:


SQL:

SELECT HDL FROM (
select 'METADATA|PayrollElementDetails|ElementName|ReportingName|Description|LegislativeDataGroupName|PrimaryClassificationName|SecondaryClassificationName|Category|ElementStartDate' AS HDL,1 AS S from dual
union
select distinct 'MERGE|PayrollElementDetails|' || Templates.base_name ||'|'|| pet.reporting_name  ||'|'|| pet.description  ||'|'|| LegislativeDataGroup.name ||'|'|| ElementClassification.base_classification_name  ||'|'|| (select ElementClassification2.base_classification_name from pay_ele_classifications_vl ElementClassification2 where nvl(ElementClassification2.base_classification_id,ElementClassification2.classification_id) = pet.secondary_classification_id and LegislativeDataGroup.legislation_code = ElementClassification2.legislation_code ) ||'|'|| PETCU.Template_Category  ||'|'|| to_char(pet.effective_start_date,'YYYY/MM/DD') AS HDL,2 AS S
from pay_element_types_vl pet, pay_templates Templates, pay_ele_classifications_vl ElementClassification,per_legislative_data_groups_vl LegislativeDataGroup, pay_element_types_f petf, pay_template_core_objects ptco,
(Select E.Element_Type_Id,T1.Template_Id,Etcu.Template_Category AS Template_Category
From Pay_Element_Types_Vl E,Pay_Template_Core_Objects Co, Pay_Templates T, Pay_Templates T1,Pay_Ele_tmplt_class_usages_vl Etcu
Where E.Element_Type_Id = Co.Core_Object_Id
And Co.Template_Id = T1.Template_Id
And T1.Template_Name = T.Template_Name
And T.template_type = 'T'
And Etcu.Template_Id = T.Template_Id
And E.Classification_Id = Etcu.Classification_Id
And (E.Secondary_Classification_Id = Etcu.Secondary_Classification_Id
or (Etcu.Secondary_Classification_Id is null and E.Secondary_Classification_Id is null)
or (E.Secondary_Classification_Id is not null and Etcu.Secondary_Classification_Id is null and not exists (SELECT 1 FROM Pay_Ele_tmplt_class_usages_vl etcu_in
    WHERE etcu_in.CLASSIFICATION_ID= E.Classification_Id and  E.Secondary_Classification_Id = etcu_in.Secondary_Classification_Id)))) PETCU
where PETCU.Element_Type_Id (+) = pet.Element_Type_Id and
ElementClassification.base_classification_id = pet.classification_id  and ElementClassification.legislation_code = LegislativeDataGroup.legislation_code and
LegislativeDataGroup.legislative_data_group_id = pet.legislative_data_group_id
AND Templates.template_id = ptco.template_id and pet.base_element_name = :EN and pet.legislative_data_group_id = LegislativeDataGroup.legislative_data_group_id and pet.element_type_id = ptco.core_object_id
AND LegislativeDataGroup.name = :legislative_data_groups
union
select 'METADATA|PayrollElementQuestionnaire|ElementName|LegislativeDataGroupName|RuleCode|Rule|Response' AS HDL,2 AS S from dual
union
select HDL,S from
(
select 'MERGE|PayrollElementQuestionnaire' ||'|'|| Templates.base_name  ||'|'||   LegislativeDataGroup.name  ||'|'||  ptr.rule_name  ||'|'||  ptr.rule_txt  ||'|'||
pay_batch_ldr_pkg.ET_convert_value(Templates.legislative_data_group_id,(case when ptr.default_value like '<%' then ptr.default_value else ptr.rule_options_value end),ptrv.rule_value, 'Y') as HDL

,((5 * 1000000) + (case Rule_Groups.group_location when 'B' then 1 when 'D' then 2 else 3 end * 1000) + (rule_usage.group_sequence * 100) + rule_usage.rule_sequence)  AS S
from pay_tmplt_rule_values ptrv, pay_tmplt_rules_f_vl ptr,pay_templates Templates,pay_templates ptt,per_legislative_data_groups_vl LegislativeDataGroup,pay_tmplt_rule_gr_usages_f rule_usage, pay_tmplt_rule_groups_f Rule_Groups, pay_element_types_f petf, pay_template_core_objects ptco
where ptrv.template_id = Templates.template_id  and ptr.base_rule_id = ptrv.rule_id and LegislativeDataGroup.legislative_data_group_id = Templates.legislative_data_group_id
And Templates.Template_Name = ptt.Template_Name and ptt.template_type = 'T'
and rule_usage.template_id = ptt.template_id and rule_usage.rule_id = ptrv.rule_id and rule_usage.RULE_GROUP_ID = Rule_Groups.RULE_GROUP_ID
AND Templates.template_id = ptco.template_id and petf.base_element_name = :EN and petf.legislative_data_group_id = LegislativeDataGroup.legislative_data_group_id and petf.element_type_id = ptco.core_object_id
AND LegislativeDataGroup.name = :legislative_data_groups and sysdate between ptr.EFFECTIVE_START_DATE and ptr.EFFECTIVE_END_DATE
and sysdate between rule_usage.EFFECTIVE_START_DATE and rule_usage.EFFECTIVE_END_DATE
and
(

(
ptr.legislation_code is null and rule_usage.legislation_code is null
and NOT EXISTS(select 1 from PAY_TMPLT_RULES_F,PAY_TMPLT_RULE_GR_USAGES_F where pay_tmplt_rule_gr_usages_f.base_rule_group_usage_id  = rule_usage.base_rule_group_usage_id and PAY_TMPLT_RULES_F.BASE_RULE_ID = ptr.base_rule_id and pay_tmplt_rule_gr_usages_f.template_id = ptt.template_id
and pay_tmplt_rule_gr_usages_f.legislation_code = LegislativeDataGroup.legislation_code and PAY_TMPLT_RULES_F.legislation_code = LegislativeDataGroup.legislation_code)
and NOT EXISTS(select 1 from PAY_TMPLT_RULES_F,PAY_TMPLT_RULE_GR_USAGES_F where pay_tmplt_rule_gr_usages_f.base_rule_group_usage_id  = rule_usage.base_rule_group_usage_id and PAY_TMPLT_RULES_F.BASE_RULE_ID = ptr.base_rule_id and pay_tmplt_rule_gr_usages_f.template_id = ptt.template_id
and pay_tmplt_rule_gr_usages_f.legislation_code = LegislativeDataGroup.legislation_code and PAY_TMPLT_RULES_F.legislation_code is null)
and NOT EXISTS(select 1 from PAY_TMPLT_RULES_F,PAY_TMPLT_RULE_GR_USAGES_F where pay_tmplt_rule_gr_usages_f.base_rule_group_usage_id  = rule_usage.base_rule_group_usage_id and PAY_TMPLT_RULES_F.BASE_RULE_ID = ptr.base_rule_id and pay_tmplt_rule_gr_usages_f.template_id = ptt.template_id
and pay_tmplt_rule_gr_usages_f.legislation_code is null and PAY_TMPLT_RULES_F.legislation_code = LegislativeDataGroup.legislation_code)
)

or

(
rule_usage.legislation_code is null and ptr.legislation_code = LegislativeDataGroup.legislation_code
and NOT EXISTS(select 1 from PAY_TMPLT_RULES_F,PAY_TMPLT_RULE_GR_USAGES_F where pay_tmplt_rule_gr_usages_f.base_rule_group_usage_id  = rule_usage.base_rule_group_usage_id and PAY_TMPLT_RULES_F.BASE_RULE_ID = ptr.base_rule_id and pay_tmplt_rule_gr_usages_f.template_id = ptt.template_id
and pay_tmplt_rule_gr_usages_f.legislation_code = LegislativeDataGroup.legislation_code and PAY_TMPLT_RULES_F.legislation_code = LegislativeDataGroup.legislation_code)
)

or

(
rule_usage.legislation_code = LegislativeDataGroup.legislation_code and ptr.legislation_code is null
and NOT EXISTS(select 1 from PAY_TMPLT_RULES_F,PAY_TMPLT_RULE_GR_USAGES_F where pay_tmplt_rule_gr_usages_f.base_rule_group_usage_id  = rule_usage.base_rule_group_usage_id and PAY_TMPLT_RULES_F.BASE_RULE_ID = ptr.base_rule_id and pay_tmplt_rule_gr_usages_f.template_id = ptt.template_id
and pay_tmplt_rule_gr_usages_f.legislation_code = LegislativeDataGroup.legislation_code and PAY_TMPLT_RULES_F.legislation_code = LegislativeDataGroup.legislation_code)
)

or
(rule_usage.legislation_code = LegislativeDataGroup.legislation_code and ptr.legislation_code = LegislativeDataGroup.legislation_code)
)
order by Rule_Groups.group_location, rule_usage.group_sequence,  rule_usage.rule_sequence
) tmplt_rule_values ORDER BY S) T




2 comments:

  1. Thank You very much for sharing this query, Sainath!

    ReplyDelete
  2. Thanks for sharing this query. Much appreciated.

    ReplyDelete

Enable Advanced Edit in HCM Extracts- After 24A update

 Enable Advanced Edit in HCM Extracts- After 24A update After 24A update Oracle deprecated the advanced edit in HCM Extracts. To Enable the ...