Load Benefits and Pension Card to Return Employees to Main Scheme from the 50/50 Section

This script anaylises which employees are in the 50/50 section at the time of run date.

It creates information with all detected employees and the change for field Member of 50/50 section with value 'No'. This information can be used to create a HCM Data Loader file for uploading and updating the Benefits and Pension calculation card.

You can use this following SQL query as it is or modify it for your own usage:

select 'METADATA|ComponentDetail|LegislativeDataGroupName|DirCardDefinitionName|EffectiveStartDate|DirCardCompDefName|AssignmentNumber|CardSequence|ComponentSequence|DirInformationCategory|FLEX:Deduction Developer DF|MemberOf5050Section(Deduction Developer DF=ORA_HRX_GB_LGPS_DETAILS)' hdldata
from dual
union
select 'MERGE|ComponentDetail|'||ldg.name||'|Benefits and Pensions|'||to_char(:rundate,'YYYY/MM/DD')||'|'||ccdf.component_name||'|'||prg.assignment_number||'|'||c.card_sequence||'|'||cc.component_sequence||'|ORA_HRX_GB_LGPS_DETAILS|ORA_HRX_GB_LGPS_DETAILS|'||'N' hdldata
  from pay_dir_comp_details_f cd
     , pay_dir_card_components_f cc
     , pay_dir_card_comp_defs_vl ccdf
     , pay_dir_cards_f c
     , pay_dir_rep_cards_f rc
     , pay_dir_rep_card_usages_f rcu
     , pay_pay_relationships_dn ppr
     , per_legislative_data_groups_vl ldg
     , pay_rel_groups_dn prg
     , pay_assigned_payrolls_dn pap
     , pay_all_payrolls_f papf
     , hr_org_details_by_class_v oc
     , hr_all_organization_units_vl ho
 where cd.dir_information_category = 'ORA_HRX_GB_LGPS_DETAILS'
   and cd.dir_information_char1 = 'Y'
   and cd.dir_card_comp_id = cc.dir_card_comp_id
   and cc.dir_card_comp_def_id = ccdf.dir_card_comp_def_id
   and ccdf.dir_card_definition_id = c.dir_card_definition_id
   and :rundate between ccdf.effective_start_date and ccdf.effective_end_date
   and cc.dir_card_id = c.dir_card_id
   and c.dir_card_definition_id = (select dir_card_definition_id from pay_dir_card_definitions_vl where base_display_name = 'Benefit and Pension Card')
   and c.payroll_relationship_id = ppr.payroll_relationship_id
   and :rundate between cd.effective_start_date and cd.effective_end_date
   and :rundate between cc.effective_start_date and cc.effective_end_date
   and :rundate between c.effective_start_date and c.effective_end_date
   and rc.dir_card_id = c.dir_card_id 
   and :rundate between rc.effective_start_date and rc.effective_end_date
   and :rundate between rcu.effective_start_date and rcu.effective_end_date
   and rcu.dir_rep_card_id = rc.dir_rep_card_id
   and rcu.dir_card_comp_id = cc.dir_card_comp_id
   and ppr.legislative_data_group_id = ldg.legislative_data_group_id
   and ldg.name = :ldg_name
   and prg.relationship_group_id = rcu.relationship_group_id
   and :rundate between prg.start_date and prg.end_date
   and prg.parent_rel_group_id = pap.payroll_term_id
   and papf.payroll_id = pap.payroll_id
   and papf.payroll_name = :payroll_name
   and :rundate between papf.effective_start_date and papf.effective_end_date
   and prg.legal_employer_id = ho.organization_id
   and :rundate between ho.effective_start_date and ho.effective_end_date
   and ho.organization_id = oc.organization_id
   and :rundate between oc.effective_start_date and oc.effective_end_date
   and oc.classification_code = 'HCM_LEMP'
   and oc.legislation_code = 'GB' 
   and oc.org_information_context = 'PER_LEMP_EMPLOYMENT_INFO'
   and ho.name = :legal_employer
order by 1 desc

View Data

1. Navigate to Tools > Report and Analytics.
2. Click Browse Catalog.
3. Navigate to Shared Folders -> Human Capital Management -> Payroll -> Regulatory and Tax Reporting -> UK -> Data Models.
4. To create a Data Model, click New and then Data Model.
5. On Diagram tab, click (+) and then SQL Query.
6. Enter a name for this query, the data source and the type of SQL.
7. Paste the SQL statements and click OK.
8. The script provides four parameters. Select all the parameters and click OK.
9. Change the first row (rundate) Data Type to 'Date' and enter a Display Label for this field.
10. Enter a Display Label also for the other rows (legal_employer, ldg_name, payroll_name).
11. On Data tab, click View Data (upper right corner) and you will get this information.
12. Enter a date for Run Date, the name of the Legal Employer, Legislative Data Group and Payroll.
    Note: The date will also be used as the Effective Start Darte when uploading the HCM Data Loader file for updating the Beneits and Pension calculation card, LGPS component details.
13. Click View.

For this example, the query will figure out the employees who are in the 50/50 section at the time of run date. It also creates the statements which can be used in a HCM Data Loader file, to update the LGPS information (Member of 50/50 Section = No).