HDL – Loading and Extracting ‘Reporting Information’ calculation card
For US legislation, Reporting Information calculation card is mandatory calculation card.
CalculationCard.dat HDL is used to upload the Calculation Component Details:
In this example, we will prepare a sample HDL file to upload “Corporate Officer” component value and set it to “Not a Corporate Officer”.
To create a calculation component details, below three Metadata’s should be used:
- CalculationCard
- CardComponent
- ComponentDetail
Below is the worked out example for same:
METADATA|CalculationCard|EffectiveStartDate|LegislativeDataGroupName|DirCardDefinitionName|CardSequence|AssignmentNumber
MERGE|CalculationCard|2024/11/01|United States|Reporting Information|1|E303510
METADATA|CardComponent|CardSequence|ComponentSequence|AssignmentNumber|EffectiveStartDate|EffectiveEndDate|DirCardDefinitionName|LegislativeDataGroupName|DirCardCompDefName
MERGE|CardComponent|1|1|E303510|2024/11/01|4712/12/31|Reporting Information|United States|Reporting Information
METADATA|ComponentDetail|AssignmentNumber|ComponentSequence|CardSequence|DirCardCompDefName|DirCardDefinitionName|DirInformationCategory|PayrollRelationshipNumber|EffectiveStartDate|EffectiveEndDate|DirCompFlexId|FLEX:Deduction Developer DF|_CORPORATE_OFFICER(Deduction Developer DF=HRX_US_REP_REL)|LegislativeDataGroupName
MERGE|ComponentDetail|E303510|1|1|Reporting Information|Reporting Information|HRX_US_REP_REL|303510|2024/11/01|4712/12/31|300000000630850|HRX_US_REP_REL|Y|United States
Save the data as CalculationCard.dat, zip the file and upload it. Calculation card component will be successfully created.
However, there are a few attributes for which you need to know the values in advance before preparing the HDL. One such attribute is PayrollRelationshipNumber. You can run below SQL to get the PayrollRelationshipNumber:
SELECT DISTINCT papf.person_number
,paam.assignment_status_type
,paam.assignment_number
,pprd.payroll_relationship_number ,to_char(GREATEST(to_date('2024/11/01','RRRR/MM/DD'),ppos.date_start),'RRRR/MM/DD') effective_start_date
FROM per_all_people_f papf
,per_all_assignments_m paam
,per_periods_of_service ppos
,pay_payroll_assignments ppa
,pay_pay_relationships_dn pprd
WHERE ppos.person_id = paam.person_id
AND ppos.period_of_service_id = paam.period_of_service_id
AND paam.effective_latest_change = 'Y'
AND paam.assignment_type in ( 'E', 'C')
--AND paam.assignment_status_type <> 'INACTIVE'
AND paam.person_id = papf.person_id
AND ppa.person_id (+) = papf.person_id
AND ppa.hr_assignment_id (+) = paam.assignment_id
AND ppos.date_start = (SELECT max(ppos2.date_start)
FROM per_periods_of_service ppos2
WHERE ppos.person_id = ppos2.person_id
AND period_type IN ('E','C')
)
AND pprd.payroll_relationship_id (+) = ppa.payroll_relationship_id
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND (GREATEST(to_date('2024/11/01','RRRR/MM/DD'),ppos.date_start) BETWEEN paam.effective_start_date AND paam.effective_end_date
)
AND Papf.person_number IN ('303510')Once the data is loaded successfully, you can run the below to extract card component details:
/*
Officer Code ?Calculation Cards?Reporting information (federal)?Calculation Component Details?Reporting Information?Officer Code.
*/
SELECT papf.person_number
,paam.assignment_status_type
,'Corporate Officer' Component_Information_Segment
,pdcdf.dir_information_char2 component_value_code
,(SELECT meaning
FROM fnd_lookup_values flv
WHERE lookup_code = pdcdf.dir_information_char2
AND flv.lookup_type = 'HRX_US_CORPORATE_OFFICER_CODES'
AND flv.language = 'US') component_value
,pdcdf.DIR_COMP_DETAIL_ID "DirCompDetailId"
,pdcf.dir_card_id "DirCardId"
,pdcdf.DIR_CARD_COMP_ID "DirCardCompId"
,paam.assignment_number "AssignmentNumber"
,pdccf.component_sequence "ComponentSequence"
,pdcf.card_sequence "CardSequence"
,pdccdv.component_name "DirCardCompDefName"
,pdcdv.display_name "DirCardDefinitionName"
,'HRX_US_REP_REL' "DirInformationCategory"
,pprd.payroll_relationship_number "PayrollRelationshipNumber"
,to_char(pdcdf.effective_start_date,'RRRR/MM/DD') "EffectiveStartDate"
,to_char(pdcdf.effective_end_date,'RRRR/MM/DD') "EffectiveEndDate"
,'300000000630850' "DirCompFlexId"
,'HRX_US_REP_REL' "FLEX:Deduction Developer DF"
,hikm.source_system_id
,hikm.source_system_owner
FROM per_all_people_f papf
,per_all_assignments_m paam
,per_periods_of_service ppos
,pay_payroll_assignments ppa
,pay_pay_relationships_dn pprd
,pay_dir_card_definitions_vl pdcdv
,pay_dir_card_comp_defs_vl pdccdv
,pay_dir_cards_f pdcf
,pay_dir_card_components_f pdccf
,pay_dir_comp_details_f pdcdf
,hrc_integration_key_map hikm
WHERE ppos.person_id = paam.person_id
AND ppos.period_of_service_id = paam.period_of_service_id
AND paam.effective_latest_change = 'Y'
AND paam.assignment_type in ( 'E', 'C')
--AND paam.assignment_status_type <> 'INACTIVE'
AND paam.person_id = papf.person_id
AND pdcdv.display_name = 'Reporting Information'
AND pdccdv.component_name = 'Reporting Information'
AND ppa.person_id = papf.person_id
AND pprd.payroll_relationship_id = ppa.payroll_relationship_id
AND pdcf.payroll_relationship_id = ppa.payroll_relationship_id
AND pdcf.dir_card_definition_id = pdccdv.dir_card_definition_id
AND pdcdv.dir_card_definition_id = pdccdv.dir_card_definition_id
AND pdccdv.dir_card_comp_def_id = pdccf.dir_card_comp_def_id
AND pdccf.dir_card_id = pdcf.dir_card_id
AND pdcdf.dir_card_comp_id = pdccf.dir_card_comp_id
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND to_date('2024/11/01','RRRR/MM/DD') BETWEEN paam.effective_start_date AND paam.effective_end_date
AND to_date('2024/11/01','RRRR/MM/DD') BETWEEN pdccdv.effective_start_date AND pdccdv.effective_end_date
AND to_date('2024/11/01','RRRR/MM/DD') BETWEEN pdcf.effective_start_date AND pdcf.effective_end_date
AND to_date('2024/11/01','RRRR/MM/DD') BETWEEN pdccf.effective_start_date AND pdccf.effective_end_date
AND to_date('2024/11/01','RRRR/MM/DD') BETWEEN pdcdf.effective_start_date AND pdcdf.effective_end_date
AND Papf.person_number = '303510'
AND hikm.surrogate_id = pdcdf.dir_comp_detail_id