PAY MPF Remittance

Description
Categories: BI Publisher
Application: Payroll
Source: MPF Remittance Report (XML)
Short Name: PAYHKMPF_XML
DB package: PAY_PAYHKMPF_XMLP_PKG
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
Business Group Id
 
Number