1. Implementing Payroll for India
  2. Guidelines for Loading Calculation Cards

Guidelines for Loading Calculation Cards

Use HCM Data Loader for bulk-loading and maintaining payroll data.

You can navigate to the HCM Data Loader (HDL) pages directly from the Payroll Checklist page. On the Checklist page, select the Initiate Data Loader task to open the HCM Data Loader object status page.

Use the HCM Data Loader to load these payroll objects:

  • Calculation Cards

For more info on how to load these business objects, refer the Integrating with HCM guide.

Creating the Files

  1. Run the SQL statements in insert.sql. This generates the output with the required metadata for the HDL file along with the merge statements for each assignment for which the new statutory deductions card needs to be added.

    New card needs to be added for these assignments. Here's an example:

    METADATA|CalculationCard|EffectiveStartDate|CardSequence|AssignmentNumber|DirCardDefinitionName|LegislativeDataGroupName
METADATA|CardComponent|EffectiveStartDate|CardSequence|ComponentSequence|AssignmentNumber|DirCardDefinitionName|LegislativeDataGroupName|DirCardCompDefName|ParentComponentSequence|ParentDirCardCompDefName
METADATA|ComponentDetail|EffectiveStartDate|CardSequence|ComponentSequence|DirInformationCategory|LegislativeDataGroupName|AssignmentNumber|DirCardDefinitionName|DirCardCompDefName|FLEX:Deduction Developer DF|taxRegime(Deduction Developer DF=HRX_IN_DIR_INCOME_TAX)
 MERGE|CalculationCard|1990/01/01|1|E955160008175673|Statutory Deductions|ZHRX_IN_Vision_LDG_Two
 MERGE|CardComponent|1990/01/01|1|1|E955160008175673|Statutory Deductions|ZHRX_IN_Vision_LDG_Two|Income Tax||
 MERGE|CardComponent|1990/01/01|1|1|E955160008175673|Statutory Deductions|ZHRX_IN_Vision_LDG_Two|Aggregation Information||
MERGE|ComponentDetail|1990/01/01|1|1|HRX_IN_DIR_INCOME_TAX|ZHRX_IN_Vision_LDG_Two|E955160008175673|Statutory Deductions|Income Tax|HRX_IN_DIR_INCOME_TAX|Y

  2. For each assignment number picked up by the query, there are 4 rows as shown.

  3. Create a file CalculationCard.dat and copy the contents generated from step 1 into the file and save. Compress (.zip) the file using a file name of your choice (example: CalculationCard_Insert.zip).

  4. Execute the SQL statement provided in the Delete.sql file. This gives the list of records that need to be deleted. If there are more than one card attached to a single assignment, then there will be two rows with different CardSequence (example: 1,2)

    Here's an example:

    METADATA|CalculationCard|EffectiveStartDate|CardSequence|AssignmentNumber|DirCardDefinitionName|LegislativeDataGroupName
 DELETE|CalculationCard|1990/01/01|1|E955160008175673|Deduction Information|ZHRX_IN_Vision_LDG_Two

  5. Create a file CalculationCard.dat and copy the contents generated from step 4 and compress the file using a suitable file name (example: CalculationCard_Delete.zip)

    For each assignment number there are four rows in the output.

Insert SQL Query

Use the SQL statements in this Insert SQL query to create the output with the required metadata for the HDL file along with the merge statements for each assignment.

select stmt from
(select 'METADATA|CalculationCard|EffectiveStartDate|CardSequence|AssignmentNumber|DirCardDefinitionName|LegislativeDataGroupName' stmt, '0' a_num,0.1 row_name from dual
union
select 'METADATA|CardComponent|EffectiveStartDate|CardSequence|ComponentSequence|AssignmentNumber|DirCardDefinitionName|LegislativeDataGroupName|DirCardCompDefName|ParentComponentSequence|ParentDirCardCompDefName' stmt, '0' a_num,0.2 row_name from dual
union
select 'METADATA|ComponentDetail|EffectiveStartDate|CardSequence|ComponentSequence|DirInformationCategory|LegislativeDataGroupName|AssignmentNumber|DirCardDefinitionName|DirCardCompDefName|FLEX:Deduction Developer DF|taxRegime(Deduction Developer DF=HRX_IN_DIR_INCOME_TAX)' stmt, '0' a_num,0.3 row_name from dual
union
select distinct 'MERGE|CalculationCard'||'|'||to_char(card.effective_start_date,'YYYY/MM/DD')||'|'||'1'||'|'||ASSIGNMENT_NUMBER||'|'||
(select display_name from fusion.pay_dir_card_definitions_vl where BASE_DISPLAY_NAME = 'ORA_HRX_IN_RESP_DEDUCTION_CARD'
and legislation_code='IN')||'|'||ldg.name stmt,ASSIGNMENT_NUMBER a_num,1 row_name
from fusion.pay_pay_relationships_dn rel, 
     fusion.per_legislative_data_groups_vl ldg,
     fusion.pay_rel_groups_dn relg,
     fusion.pay_dir_cards_f card, 
     fusion.pay_dir_card_definitions_vl def,
     fusion.per_periods_of_service ppos
where rel.LEGISLATIVE_DATA_GROUP_ID = ldg.LEGISLATIVE_DATA_GROUP_ID
and ldg.LEGISLATION_CODE = 'IN'
and rel.PAYROLL_RELATIONSHIP_ID = relg.PAYROLL_RELATIONSHIP_ID
and relg.GROUP_TYPE = 'A'
and rel.payroll_relationship_id = card.PAYROLL_RELATIONSHIP_ID
and card.DIR_CARD_DEFINITION_ID = def.DIR_CARD_DEFINITION_ID
and def.BASE_DISPLAY_NAME = 'IN_DEDUCTION_CARD'
and not exists ( select 1 from fusion.pay_payroll_rel_actions act 
where act.PAYROLL_RELATIONSHIP_ID = rel.PAYROLL_RELATIONSHIP_ID)
and card.effective_start_date =
(select max(effective_start_date) from fusion.pay_dir_cards_f where PAYROLL_RELATIONSHIP_ID= rel.PAYROLL_RELATIONSHIP_ID)
and ppos.person_id=rel.person_id
and ppos.legislation_code='IN'
and nvl(ppos.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')) > trunc(sysdate)
AND  ppos.period_of_service_id = ( SELECT MAX(period_of_service_id) FROM fusion.per_periods_of_service WHERE person_id = rel.person_id)
union
select distinct 'MERGE|CardComponent'||'|'||to_char(card.effective_start_date,'YYYY/MM/DD')||'|'||'1|1'||'|'||ASSIGNMENT_NUMBER||'|'||
(select display_name from fusion.pay_dir_card_definitions_vl where BASE_DISPLAY_NAME = 'ORA_HRX_IN_RESP_DEDUCTION_CARD'
and legislation_code='IN')||'|'||ldg.name||'|'||'Income Tax||' stmt,ASSIGNMENT_NUMBER a_num,2 row_name
from fusion.pay_pay_relationships_dn rel, 
     fusion.per_legislative_data_groups_vl ldg,
     fusion.pay_rel_groups_dn relg,
     fusion.pay_dir_cards_f card, 
     fusion.pay_dir_card_definitions_vl def,
     fusion.per_periods_of_service ppos
where rel.LEGISLATIVE_DATA_GROUP_ID = ldg.LEGISLATIVE_DATA_GROUP_ID
and ldg.LEGISLATION_CODE = 'IN'
and rel.PAYROLL_RELATIONSHIP_ID = relg.PAYROLL_RELATIONSHIP_ID
and relg.GROUP_TYPE = 'A'
and rel.payroll_relationship_id = card.PAYROLL_RELATIONSHIP_ID
and card.DIR_CARD_DEFINITION_ID = def.DIR_CARD_DEFINITION_ID
and def.BASE_DISPLAY_NAME = 'IN_DEDUCTION_CARD'
and not exists ( select 1 from fusion.pay_payroll_rel_actions act 
where act.PAYROLL_RELATIONSHIP_ID = rel.PAYROLL_RELATIONSHIP_ID)
and card.effective_start_date =
(select max(effective_start_date) from fusion.pay_dir_cards_f where PAYROLL_RELATIONSHIP_ID= rel.PAYROLL_RELATIONSHIP_ID)
and ppos.person_id=rel.person_id
and ppos.legislation_code='IN'
and nvl(ppos.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')) > trunc(sysdate)
AND  ppos.period_of_service_id = ( SELECT MAX(period_of_service_id) FROM fusion.per_periods_of_service WHERE person_id = rel.person_id)
union
select distinct 'MERGE|CardComponent'||'|'||to_char(card.effective_start_date,'YYYY/MM/DD')||'|'||'1|1'||'|'||ASSIGNMENT_NUMBER||'|'||
(select display_name from fusion.pay_dir_card_definitions_vl where BASE_DISPLAY_NAME = 'ORA_HRX_IN_RESP_DEDUCTION_CARD'
and legislation_code='IN')||'|'||ldg.name||'|'||'Aggregation Information||' stmt,ASSIGNMENT_NUMBER a_num,3 row_name
from fusion.pay_pay_relationships_dn rel, 
     fusion.per_legislative_data_groups_vl ldg,
     fusion.pay_rel_groups_dn relg,
     fusion.pay_dir_cards_f card, 
     fusion.pay_dir_card_definitions_vl def,
     fusion.per_periods_of_service ppos
where rel.LEGISLATIVE_DATA_GROUP_ID = ldg.LEGISLATIVE_DATA_GROUP_ID
and ldg.LEGISLATION_CODE = 'IN'
and rel.PAYROLL_RELATIONSHIP_ID = relg.PAYROLL_RELATIONSHIP_ID
and relg.GROUP_TYPE = 'A'
and rel.payroll_relationship_id = card.PAYROLL_RELATIONSHIP_ID
and card.DIR_CARD_DEFINITION_ID = def.DIR_CARD_DEFINITION_ID
and def.BASE_DISPLAY_NAME = 'IN_DEDUCTION_CARD'
and not exists ( select 1 from fusion.pay_payroll_rel_actions act 
where act.PAYROLL_RELATIONSHIP_ID = rel.PAYROLL_RELATIONSHIP_ID)
and card.effective_start_date =
(select max(effective_start_date) from fusion.pay_dir_cards_f where PAYROLL_RELATIONSHIP_ID= rel.PAYROLL_RELATIONSHIP_ID)
and ppos.person_id=rel.person_id
and ppos.legislation_code='IN'
and nvl(ppos.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')) > trunc(sysdate)
AND  ppos.period_of_service_id = ( SELECT MAX(period_of_service_id) FROM fusion.per_periods_of_service WHERE person_id = rel.person_id)
union
select distinct 'MERGE|ComponentDetail'||'|'||to_char(card.effective_start_date,'YYYY/MM/DD')||'|'||'1|1'||'|'
||'HRX_IN_DIR_INCOME_TAX|'||ldg.name||'|'||ASSIGNMENT_NUMBER||'|'||
(select display_name from fusion.pay_dir_card_definitions_vl where BASE_DISPLAY_NAME = 'ORA_HRX_IN_RESP_DEDUCTION_CARD'
and legislation_code='IN')||'|Income Tax|HRX_IN_DIR_INCOME_TAX|Y' stmt,ASSIGNMENT_NUMBER a_num,4 row_name
from fusion.pay_pay_relationships_dn rel, 
     fusion.per_legislative_data_groups_vl ldg,
     fusion.pay_rel_groups_dn relg,
     fusion.pay_dir_cards_f card, 
     fusion.pay_dir_card_definitions_vl def,
     fusion.per_periods_of_service ppos
where rel.LEGISLATIVE_DATA_GROUP_ID = ldg.LEGISLATIVE_DATA_GROUP_ID
and ldg.LEGISLATION_CODE = 'IN'
and rel.PAYROLL_RELATIONSHIP_ID = relg.PAYROLL_RELATIONSHIP_ID
and relg.GROUP_TYPE = 'A'
and rel.payroll_relationship_id = card.PAYROLL_RELATIONSHIP_ID
and card.DIR_CARD_DEFINITION_ID = def.DIR_CARD_DEFINITION_ID
and def.BASE_DISPLAY_NAME = 'IN_DEDUCTION_CARD'
and not exists ( select 1 from fusion.pay_payroll_rel_actions act 
where act.PAYROLL_RELATIONSHIP_ID = rel.PAYROLL_RELATIONSHIP_ID)
and card.effective_start_date =
(select max(effective_start_date) from fusion.pay_dir_cards_f where PAYROLL_RELATIONSHIP_ID= rel.PAYROLL_RELATIONSHIP_ID)
and ppos.person_id=rel.person_id
and ppos.legislation_code='IN'
and nvl(ppos.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')) > trunc(sysdate)
AND  ppos.period_of_service_id = ( SELECT MAX(period_of_service_id) FROM fusion.per_periods_of_service WHERE person_id = rel.person_id)
)

order by a_num,row_name

Delete SQL Query

Use the SQL statements in this Delete SQL query to generate the list of records that need to be deleted.

select stmt from
(select 'METADATA|CalculationCard|EffectiveStartDate|CardSequence|AssignmentNumber|DirCardDefinitionName|LegislativeDataGroupName' stmt, 1 row_num from dual
union
select 'DELETE|CalculationCard|'||to_char(rel.start_date,'YYYY/MM/DD')||'|'||CARD_SEQUENCE||'|'||ASSIGNMENT_NUMBER||'|'||DISPLAY_NAME||'|'||ldg.name stmt, 2 row_num
from fusion.pay_pay_relationships_dn rel, 
     fusion.per_legislative_data_groups_vl ldg,
     fusion.pay_rel_groups_dn relg,
     fusion.pay_dir_cards_f card, 
     fusion.pay_dir_card_definitions_vl def,
     fusion.per_periods_of_service ppos
where rel.LEGISLATIVE_DATA_GROUP_ID = ldg.LEGISLATIVE_DATA_GROUP_ID
and ldg.LEGISLATION_CODE = 'IN'
and rel.PAYROLL_RELATIONSHIP_ID = relg.PAYROLL_RELATIONSHIP_ID
and relg.GROUP_TYPE = 'A'
and rel.payroll_relationship_id = card.PAYROLL_RELATIONSHIP_ID
and card.DIR_CARD_DEFINITION_ID = def.DIR_CARD_DEFINITION_ID
and def.BASE_DISPLAY_NAME = 'IN_DEDUCTION_CARD'
and not exists ( select 1 from fusion.pay_payroll_rel_actions act where act.PAYROLL_RELATIONSHIP_ID = rel.PAYROLL_RELATIONSHIP_ID)
and ppos.person_id=rel.person_id
and ppos.legislation_code='IN'
and nvl(ppos.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')) > trunc(sysdate)
AND  ppos.period_of_service_id = ( SELECT MAX(period_of_service_id) FROM fusion.per_periods_of_service WHERE person_id = rel.person_id)
)
order by row_num

Importing and Loading the Files

  1. From the home page, click My Client Groups > Data Exchange.

  2. On the Data Exchange page, click Import and Load Data.

  3. Click Import File on the page header.

  4. In the File Explorer, drag the .zip file created in step 5 of Creating the Files task (CalculationCard_Delete.zip) to add the file.

    Or, click Choose File to search and select your file.

    Note: You need to load the CalculationCard_Delete.zip before loading CalculationCard_Insert.zip

  5. Click Submit.

  6. Click OK on the confirmation page.

  7. When the import and load process is complete, the old card Deduction Information needs to be deleted from the list of assignment number in the file.

  8. Repeat the same set of steps to load the file created in Step 3 of Creating the Files. Example: CalculationCard_Insert.zip

  9. When the import and load process is complete, the new card Statutory Deductions needs to be created for the list of assignment number in the file.

  10. Validate that the new card Statutory Deductions is created for the employee.

  11. Query for the employee using the Person Management screen.

  12. Navigate to Payroll > Calculation Cards.

    The calculation card displays as Statutory Deductions with the description Statutory deduction information for India.