ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4713A51A9E05362FB09050512Y 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 PAY PAYHKMPF_XML US PAY MPF Remittance 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 ZHS PAY MPF 汇款报表 Imported from BI Publisher Description: MPF 汇款报表 Application: 工资单 Source: MPF 汇款报表 (XML) Short Name: PAYHKMPF_XML DB package: PAY_PAYHKMPF_XMLP_PKG BI Publisher &cp_where_current :cp_address :cp_contact :cp_currency_format_mask_12 :cp_currency_format_mask_14 :cp_display_sequence_1 :cp_display_sequence_2 :cp_employer_name :cp_participation_no :cp_scheme_name :cp_scheme_reg_number :cp_telephone_no :cp_where_current :p_business_group_id :p_conc_request_id :p_contributions_end_date :p_contributions_start_date :p_global_sequence_no :p_legal_employer_id :p_scheme_id :p_surcharge_parti_mand :p_surcharge_parti_mand_t :p_surcharge_parti_vol :p_surcharge_parti_vol_t :p_surcharge_partii_mand :p_surcharge_partii_mand_t :p_surcharge_partii_vol :p_surcharge_partii_vol_t 1 -20 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') Y US Business Group Id ZHS 业务组标识 2 10 :p_scheme_id LOV Oracle HR_HK_SCHEME_ID Y select c.org_information20 id, c.org_information2 || ' (' || c.org_information20 || ')' value, null description from hr_organization_information c,hr_organization_units b where b.business_group_id= fnd_global.per_business_group_id and b.organization_id=c.organization_id and c.org_information_context='HK_MPF_SCHEMES' order by value,description Y US MPF Scheme Id ZHS MPF 计划标识 3 20 :p_legal_employer_id LOV Oracle HR_HK_LEGAL_EMPLOYER Y select le.gre_id id, substr(le.name,1,150) value, null description from hr_hk_legal_employer_v le where le.business_group_id=fnd_global.per_business_group_id order by value,description Y US Legal Employer Id ZHS 合法雇主标识 4 30 :p_contributions_start_date Date Y US Contributions Start Date ZHS 缴费起始日期 5 40 :p_contributions_end_date Date Y US Contributions End Date ZHS 缴费终止日期 6 50 :p_surcharge_parti_mand Number US Surcharge - Part I Mandatory ZHS 附加费 - 第 I 部分（强制性） 7 60 :p_surcharge_parti_vol Number US Surcharge - Part I Voluntary ZHS 附加费 - 第 I 部分（自愿） 8 70 :p_surcharge_partii_mand Number US Surcharge - Part II Mandatory ZHS 附加费 - 第 II 部分（强制性） 9 80 :p_surcharge_partii_vol Number US Surcharge - Part II Voluntary ZHS 附加费 - 第 II 部分（自愿）