Nov 16 2024
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.
Please upload data in DEV environment, so it can be validated before upload into Production.
*/
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

Mandeep Gupta 0 Comments