ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4715451A9E05362FB09050512 select per.full_name, floor((:P_END_DATE-per.date_of_birth)/365) age, paa.assignment_id, paa.assignment_number, pcak.concatenated_segments cost_centre, &cp_sgc_rate_1 CF_sgc_rate, PAY_PYAUSGC_XMLP_PKG.CP_sgc_rate_1_p CP_sgc_rate_1, PAY_PYAUSGC_XMLP_PKG.cf_payausgc_pkbformula(paa.assignment_id,floor((:P_END_DATE-per.date_of_birth)/365)) CF_payausgc_pkb, PAY_PYAUSGC_XMLP_PKG.CP_start_date_p CP_start_date, PAY_PYAUSGC_XMLP_PKG.CP_end_date_p CP_end_date, PAY_PYAUSGC_XMLP_PKG.CP_super_sal_p CP_super_sal, PAY_PYAUSGC_XMLP_PKG.CP_compliance_mesg_p CP_compliance_mesg, PAY_PYAUSGC_XMLP_PKG.CP_warning_mesg_p CP_warning_mesg, PAY_PYAUSGC_XMLP_PKG.CP_sgc_contr_p CP_sgc_contr from per_people_f per, per_assignments_f paa, per_periods_of_service_v ppos, pay_cost_allocation_keyflex pcak, pay_cost_allocations_f pca where per.person_id=paa.person_id and paa.period_of_service_id = ppos.period_of_service_id and exists (select paa.assignment_id from pay_run_results prr, pay_assignment_actions paa1, pay_element_types_f pet, pay_payroll_actions ppa, per_time_periods ptp where prr.element_type_id=pet.element_type_id and paa1.assignment_action_id=prr.assignment_action_id and pet.element_name='Superannuation Contribution' and ppa.payroll_action_id=paa1.payroll_action_id and ppa.payroll_id = ptp.payroll_id and ppa.time_period_id = ptp.time_period_id and ppa.action_status='C' and ppa.action_type in ('R','Q') and ptp.end_date between :P_START_DATE and :P_END_DATE and ppa.effective_date between :P_START_DATE and :P_END_DATE and paa.assignment_id =paa1.assignment_id and paa1.tax_unit_id = :P_REGISTERED_EMPLOYER) and per.person_id=ppos.person_id and (ppos.actual_termination_date >= :P_START_DATE OR ppos.actual_termination_date is null) and nvl(ppos.actual_termination_date,:P_END_DATE) between per.effective_start_date and per.effective_end_Date and pcak.cost_allocation_keyflex_id(+)=pca.cost_allocation_keyflex_id and pca.assignment_id(+) = paa.assignment_id and nvl(ppos.actual_termination_date,:P_END_DATE) between paa.effective_start_date and paa.effective_end_date and (:P_START_DATE between pca.effective_start_date and pca.effective_end_date or pca.effective_start_date is null) and (:P_ALL_EXCEPTION = 'ALL' OR (:P_ALL_EXCEPTION <> 'ALL' and exists ( select 1 from dual where pay_au_sgc_pkg.compliance_mesg(paa.assignment_id, floor((sysdate-per.date_of_birth)/365),:P_END_DATE,:P_SGC_RATE,:P_BUSINESS_GROUP_ID,:P_REGISTERED_EMPLOYER,'AU') is not null ))) and (pcak.cost_allocation_keyflex_id =:p_cost_centre or :p_cost_centre is null) and (per.employee_number=:P_EMPLOYEE_NUMBER OR :P_EMPLOYEE_NUMBER is null) order by nvl(per.order_name,per.full_name) Y PAY PYAUSGC_XML US PAY Superannuation Contribution Imported from BI Publisher Description: Superannuation Contribution Report Application: Payroll Source: Superannuation Contribution Report (XML) Short Name: PYAUSGC_XML DB package: PAY_PYAUSGC_XMLP_PKG BI Publisher &cp_sgc_rate_1 :cp_compliance_mesg :cp_currency_code :cp_end_date :cp_sgc_contr :cp_sgc_rate :cp_sgc_rate_1 :cp_start_date :cp_super_sal :cp_warning_mesg :p_all_exception :p_business_group_id :p_conc_request_id :p_cost_centre :p_date :p_date1 :p_employee_number :p_end_date :p_mon_yyyy :p_registered_employer :p_sgc_rate :p_start_date 1 -20 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') Y US BUSINESS_GROUP_ID BUSINESS_GROUP_ID 2 10 :p_cost_centre LOV Oracle PY_AU_COST_CENTRES Y select cost_allocation_keyflex_id id, concatenated_segments value, null description from pay_cost_allocation_keyflex where id_flex_num in (select org_information7 from hr_organization_information_v where organization_id=fnd_global.per_business_group_id and org_information_context='Business Group Information') and concatenated_segments is not null order by concatenated_segments US Cost Centre COST_CENTRE 3 20 :p_date Date SELECT FND_DATE.DATE_TO_DISPLAYDATE(EFFECTIVE_DATE) FROM FND_SESSIONS WHERE SESSION_ID=USERENV('SESSIONID') Y US FND_STANDARD_DATE DATE 4 30 :p_registered_employer LOV Oracle HR_AU_LEG_EMP Y select le.organization_id id, substr(le.name,1,150) value, null description from hr_au_leg_emp_v le where le.business_group_id=fnd_global.per_business_group_id order by le.name Y US REGISTERED_EMPLOYER Legal Employer 5 40 :p_employee_number LOV Oracle PY_AU_SGC_EMPLOYEES Y select per.employee_number id, per.employee_number value, null description from per_people_f per,per_periods_of_service_v ppos,per_assignments_f paaf,hr_soft_coding_keyflex hsck where per.business_group_id=fnd_global.per_business_group_id and paaf.person_id=per.person_id and paaf.period_of_service_id=ppos.period_of_service_id and paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id and hsck.segment1=:$flex$.registered_employer and per.person_id=ppos.person_id and (ppos.actual_termination_date is null or (ppos.actual_termination_date between per.effective_start_date and per.effective_end_date and ppos.actual_termination_date>=to_date('1-'||to_char(to_date( :$flex$.fnd_standard_date,'YYYY/MM/DD HH24:MI:SS'),'MON-YYYY'),'DD-MON-YYYY'))) and nvl(ppos.actual_termination_date,last_day(to_date('1-'||to_char(to_date( :$flex$.fnd_standard_date,'YYYY/MM/DD HH24:MI:SS'),'MON-YYYY'),'DD-MON-YYYY'))) between per.effective_start_date and per.effective_end_date and nvl(ppos.actual_termination_date,last_day(to_date('1-'||to_char(to_date( :$flex$.fnd_standard_date,'YYYY/MM/DD HH24:MI:SS'),'MON-YYYY'),'DD-MON-YYYY'))) between paaf.effective_start_date and paaf.effective_end_date order by per.employee_number select per.employee_number from per_people_f per, per_periods_of_service_v ppos, pay_cost_allocations_f pca, pay_cost_allocation_keyflex pcak, per_assignments_f paa where per.business_group_id =fnd_global.PER_BUSINESS_GROUP_ID and per.person_id=ppos.person_id and paa.period_of_service_id = ppos.period_of_service_id and pca.cost_allocation_keyflex_id=pcak.cost_allocation_keyflex_id(+) and per.person_id=paa.person_id and paa.assignment_id=pca.assignment_id(+) and (ppos.actual_termination_date is null OR (ppos.actual_termination_date between per.effective_start_date and per.effective_end_date and ppos.actual_termination_date > to_date('1-'||to_char(to_date(:$FLEX$.FND_STANDARD_DATE,'YYYY/MM/DD HH24:MI:SS'),'MON-YYYY'),'DD-MON-YYYY') )) and to_date('1-'||to_char(to_date(:$FLEX$.FND_STANDARD_DATE,'YYYY/MM/DD HH24:MI:SS'),'MON-YYYY'),'DD-MON-YYYY') between per.effective_start_date and per.effective_end_date and to_date('1-'||to_char(to_date(:$FLEX$.FND_STANDARD_DATE,'YYYY/MM/DD HH24:MI:SS'),'MON-YYYY'),'DD-MON-YYYY') between paa.effective_start_date and paa.effective_end_date and pcak.cost_allocation_keyflex_id=:$flex$.cost_centre US Employee Number EMPLOYEE_NUMBER 6 50 :p_sgc_rate Number Y US SGC Rate SGC_RATE 7 60 :p_all_exception LOV Oracle PY_AU_SGC_COMPLIANCE Y select lookup_code id, meaning value, null description from hr_lookups where lookup_type='AU_SGC_COMPLIANCE' and enabled_flag='Y' order by meaning Y US All/Exception ALL/EXCEPTION :$FLEX$.FND_STANDARD_DATE FND_STANDARD_DATE Y Employee Number :$flex$.cost_centre Cost Centre Employee Number :$flex$.fnd_standard_date FND_STANDARD_DATE Y Employee Number :$flex$.registered_employer REGISTERED_EMPLOYER Employee Number