Search for:
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
Nov 16 2024
HDL – Send notifications when loading participants in performance document

Whenever doing mass upload of participants in a performance document, there may be a need to send a notification message to the participants.

To load participants, you need to use PerfDocComplete -> Participant business object. However, if the data is loaded without using “Message” attribute in Participant business object, no message will be sent to the participants.

The notifications are determined by the setting at ‘Configure HCM Data Loader’ task under setup and maintenance. Under Business objects, choose “Performance Document” and you can see the process named – Send Notifications to Mass Loaded Participants.

But again to make this setting work, message attribute must be included in participant object.

Mandeep Gupta 0 Comments