PAY MPF Remittance

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: MPF Remittance Report
Application: Payroll
Source: MPF Remittance Report (XML)
Short Name: PAYHKMPF_XML
DB package: PAY_PAYHKMPF_XMLP_PKG
Run PAY MPF Remittance and other Oracle EBS reports with Blitz Report™ on our demo environment
select person_id,
       phmr.assignment_id,
       full_name,
       scheme_id,
       membership_number,
       to_char(date_start, 'YYYY-MM-DD') date_start,
       to_char(actual_termination_date, 'YYYY-MM-DD') actual_termination_date,
       min(decode(element_name, 'MPF Liability', to_char(period_start_date, 'YYYY-MM-DD'),
                               'Voluntary MPF Liability', to_char(period_start_date, 'YYYY-MM-DD'),
                                null))  er_start_date,
       max(decode(element_name, 'MPF Liability', to_char(period_end_date, 'YYYY-MM-DD'),
                                'Voluntary MPF Liability', to_char(period_end_date, 'YYYY-MM-DD'),
                                null))  er_end_date,
       min(decode(element_name, 'MPF Withheld', to_char(period_start_date, 'YYYY-MM-DD'),
                                'Voluntary MPF Withheld', to_char(period_start_date, 'YYYY-MM-DD'),
                                null))  ee_start_date,
       max(decode(element_name, 'MPF Withheld', to_char(period_end_date, 'YYYY-MM-DD'),
                    'Voluntary MPF Withheld', to_char(period_end_date, 'YYYY-MM-DD'),
                                null))  ee_end_date,
       max(decode(element_name, 'MPF Liability', assessed_ri,
                                    'Voluntary MPF Liability', assessed_ri,
                   'Voluntary MPF Withheld', assessed_ri,
                                0))     assessed_ri,
       sum(decode(element_name, 'MPF Liability', value,
                                0))  er_mandatory,
       sum(decode(element_name, 'Voluntary MPF Liability', value,
                                0))  er_voluntary,
       sum(decode(element_name, 'MPF Withheld', value,
                                0))  ee_mandatory,
       sum(decode(element_name, 'Voluntary MPF Withheld', value,
                                0))  ee_voluntary,
	PAY_PAYHKMPF_XMLP_PKG.cf_sequence_noformula() CF_sequence_no,
	PAY_PAYHKMPF_XMLP_PKG.CP_display_sequence_1_p CP_display_sequence_1,
	PAY_PAYHKMPF_XMLP_PKG.cf_total_mandatoryformula(sum(decode(element_name,'MPF Liability',value,0)),sum(decode(element_name,'MPF Withheld',value,0))) CF_total_mandatory,
	PAY_PAYHKMPF_XMLP_PKG.cf_total_voluntaryformula(sum(decode(element_name,'Voluntary MPF Liability',value,0)), sum(decode(element_name,'Voluntary MPF Withheld',value,0))) CF_total_voluntary
from pay_hk_mpf_remittance_v   phmr
where  element_name in ('MPF Withheld',  'Voluntary MPF Withheld',
                        'MPF Liability', 'Voluntary MPF Liability')
and NOT exists (select null
from pay_assignment_actions paa,
         pay_payroll_actions ppa,
         pay_element_types_f petf,
         pay_run_results prr,
         pay_input_values_f pivf,
         pay_run_result_values prrv
where prr.assignment_action_id = paa.assignment_action_id
and paa.payroll_action_id            = ppa.payroll_action_id
and paa.assignment_id                = phmr.assignment_id
and paa.tax_unit_id                     = phmr.tax_unit_id 
and paa.tax_unit_id                         = :p_legal_employer_id
and prr.run_result_id                       = prrv.run_result_id
and prrv.input_value_id                 = pivf.input_value_id
and pivf.name                                   = 'Source'
and pivf.element_type_id             = petf.element_type_id
and petf.element_type_id = prr.element_type_id
and petf.legislation_code = 'HK' 
and pivf.legislation_code = 'HK' 
and prrv.result_value = to_char(phmr.scheme_id) 
and prrv.result_value = to_char(:p_scheme_id)
and ppa.effective_date between
        pivf.effective_start_date and pivf.effective_end_date
and ppa.effective_date between
        petf.effective_start_date and petf.effective_end_date
and petf.element_name in
       ('MPF Withheld',  'Retro MPF Withheld',
        'MPF Liability',  'Retro MPF Liability', 'Retro MPF Liability for First Period', 'Retro MPF Liability for Second Period')
and ppa.effective_date < :p_contributions_start_date  )
&cp_where_current
group by person_id,
       phmr.assignment_id,
       full_name,
       scheme_id,
       membership_number,
       date_start,
       actual_termination_date
union all
select person_id,
       phmr.assignment_id,
       full_name,
       scheme_id,
       membership_number,
       to_char(date_start, 'YYYY-MM-DD') date_start,
       to_char(actual_termination_date, 'YYYY-MM-DD') actual_termination_date,
       min(decode(element_name, 'Retro MPF Liability for First Period', to_char(period_start_date, 'YYYY-MM-DD'),
                     'Retro MPF Liability for Second Period', to_char(period_start_date, 'YYYY-MM-DD'),
                                null))  er_start_date,
       max(decode(element_name, 'Retro MPF Liability for First Period', to_char(period_end_date, 'YYYY-MM-DD'),
                'Retro MPF Liability for Second Period', to_char(period_end_date, 'YYYY-MM-DD'),
                                null))  er_end_date,
       min(decode(element_name, 'Retro MPF Withheld', to_char(period_start_date, 'YYYY-MM-DD'),
                                null))  ee_start_date,
       max(decode(element_name, 'Retro MPF Withheld', to_char(period_end_date, 'YYYY-MM-DD'),
                                null))  ee_end_date,
       sum(decode(element_name, 'Retro MPF Liability for First Period', assessed_ri,
                       'Retro MPF Liability for Second Period', assessed_ri,
                                0))     assessed_ri,
       sum(decode(element_name, 'Retro MPF Liability for First Period', phmr.value,
                       'Retro MPF Liability for Second Period', phmr.value,
                                0))  er_mandatory,
       0                             er_voluntary,
       sum(decode(element_name, 'Retro MPF Withheld', phmr.value,
                                0))  ee_mandatory,
       0                             ee_voluntary,
	PAY_PAYHKMPF_XMLP_PKG.cf_sequence_noformula() CF_sequence_no,
	PAY_PAYHKMPF_XMLP_PKG.CP_display_sequence_1_p CP_display_sequence_1,
	PAY_PAYHKMPF_XMLP_PKG.cf_total_mandatoryformula(sum(decode(element_name, 'Retro MPF Liability for First Period', phmr.value, 'Retro MPF Liability for Second Period', phmr.value,0)),sum(decode(element_name,'Retro MPF Withheld',value,0))) CF_total_mandatory,
	PAY_PAYHKMPF_XMLP_PKG.cf_total_voluntaryformula(0,0) CF_total_voluntary
from pay_hk_mpf_remittance_v  phmr
where  element_name in ('Retro MPF Withheld', 'Retro MPF Liability for First Period', 'Retro MPF Liability for Second Period')
and NOT exists (select null
from pay_assignment_actions paa,
         pay_payroll_actions ppa,
         pay_element_types_f petf,
         pay_run_results prr,
         pay_input_values_f pivf,
         pay_run_result_values prrv
where prr.assignment_action_id = paa.assignment_action_id
and paa.payroll_action_id            = ppa.payroll_action_id
and paa.assignment_id                 = phmr.assignment_id
and paa.tax_unit_id                     =phmr.tax_unit_id 
and paa.tax_unit_id                         = :p_legal_employer_id
and prr.run_result_id                       = prrv.run_result_id
and prrv.input_value_id                 = pivf.input_value_id
and pivf.name                                   = 'Source'
and pivf.element_type_id             = petf.element_type_id
and petf.element_type_id = prr.element_type_id 
and  petf.legislation_code = 'HK'
and pivf.legislation_code = 'HK'
and prrv.result_value = to_char(phmr.scheme_id)
  and prrv.result_value = to_char(:p_scheme_id)
and ppa.effective_date between
        pivf.effective_start_date and pivf.effective_end_date
and ppa.effective_date between
        petf.effective_start_date and petf.effective_end_date
and petf.element_name in
       ('MPF Withheld',  'Retro MPF Withheld',
        'MPF Liability', 'Retro MPF Liability','Retro MPF Liability for First Period', 'Retro MPF Liability for Second Period')
and ppa.effective_date < :p_contributions_start_date  )
&cp_where_current
group by person_id,
       phmr.assignment_id,
       full_name,
       scheme_id,
       membership_number,
       date_start,
       actual_termination_date
order by full_name,
                 er_start_date
Parameter Name SQL text Validation
MPF Scheme Id 
 
 LOV Oracle
Legal Employer Id 
 
 LOV Oracle
Contributions Start Date 
 
 Date
Contributions End Date 
 
 Date
Surcharge - Part I Mandatory 
 
 Number
Surcharge - Part I Voluntary 
 
 Number
Surcharge - Part II Mandatory 
 
 Number
Surcharge - Part II Voluntary 
 
 Number
Download
Related Blitz Reports
PAY Tax Payments Listing PAY Gross To Net Summary Report (GB) PAY Payroll Reconciliation Detail RKD Employee Element History PAY Employee Payroll History SSP ShPP Adoption Element Results SSP ShPP Birth Element Results PAY Employee Run Results PAY Employee Run Results Report (CA)
Blitz Report™

Blitz Report™ provides multiple benefits: