[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_sql := ' select distinct p.person_id' ||
' from per_people_f p,' ||
' pay_payroll_actions pa' ||
' where pa.payroll_action_id = :payroll_action_id' ||
' and p.business_group_id = pa.business_group_id' ||
' order by p.person_id';
SELECT decode(pbt.balance_name,'Earnings_Total',1,'Direct Payments',2,'Termination_Payments',3,
'Involuntary Deductions',4,'Pre Tax Deductions',5,'Termination Deductions',6,
'Voluntary Deductions',7,'Total_Tax_Deductions',8,'Earnings_Non_Taxable',9,
'Employer_Charges',10,
'Lump Sum A Payments',11,'Lump Sum B Payments',12,'Lump Sum C Payments',13,
'Lump Sum D Payments',14,'Lump Sum E Payments',15,'Invalidity Payments',16,'CDEP',17,
'Leave Payments Marginal',18,'Other Income',19,'Union Fees',20,
'Workplace Giving Deductions' ,21) sort_index, /*4085496 */
pdb.defined_balance_id defined_balance_id,
pbt.balance_name
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pbt.balance_name IN ('Earnings_Total','Direct Payments','Termination_Payments','Involuntary Deductions',
'Pre Tax Deductions','Termination Deductions','Voluntary Deductions','Total_Tax_Deductions',
'Earnings_Non_Taxable','Employer_Charges','Lump Sum A Payments','Lump Sum B Payments','Lump Sum C Payments',
'Lump Sum D Payments','Lump Sum E Payments','Invalidity Payments','CDEP','Leave Payments Marginal','Other Income','Union Fees', 'Workplace Giving Deductions') /*4085496 */
AND pbd.database_item_suffix = '_ASG_LE_YTD'
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbt.legislation_code = 'AU'
ORDER BY sort_index;
select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) business_group_id
,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) legal_employer
,decode(pay_core_utils.get_parameter('PAYROLL',legislative_parameters),null,'%',pay_core_utils.get_parameter('PAYROLL',legislative_parameters)) payroll_id
,decode(pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters),null,'%',pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters)) assignment_id
,decode(pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters),'C','Y','T','N','B','%') employee_type
,to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fin_year_state_date
,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') fin_year_end_date
,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_start_date
,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_end_date
,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_year_term
,pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug 4142159*/
,decode(pay_core_utils.get_parameter('REP_MODE',legislative_parameters),'SUMM','S','D') report_mode /*Bug 3891577*/
from pay_payroll_actions
where payroll_action_id = c_payroll_action_id;
select pdb.defined_balance_id
from pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pbt.balance_name ='Fringe Benefits'
and pbt.balance_type_id = pdb.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id /* Bug 2501105 */
and pbd.legislation_code ='AU'
and pbd.dimension_name ='_ASG_LE_FBT_YTD' --2610141
and pbd.legislation_code = pbt.legislation_code
and pbd.legislation_code = pdb.legislation_code;
g_balance_value_tab.delete;
hr_utility.set_location('g_parameters.delete_actions..........= '||g_parameters.delete_actions,30); /*Bug 4142159*/
select parameter_value
from pay_action_parameters
where parameter_name = 'RANGE_PERSON_ID';
SELECT par.parameter_value
FROM pay_report_format_parameters par,
pay_report_format_mappings_f map
WHERE map.report_format_mapping_id = par.report_format_mapping_id
AND map.report_type = c_report_type
AND map.report_format = c_report_type
AND map.report_qualifier = 'AU'
AND par.parameter_name = 'RANGE_PERSON_ID'; -- Bug fix 5567246
select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) business_group_id
,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) legal_employer
,decode(pay_core_utils.get_parameter('PAYROLL',legislative_parameters),null,'%',pay_core_utils.get_parameter('PAYROLL',legislative_parameters)) payroll_id
,decode(pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters),null,'%',pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters)) assignment_id
,decode(pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters),'C','Y','T','N','B','%') employee_type
,to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fin_year_state_date
,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') fin_year_end_date
,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_start_date
,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_end_date
,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_year_term
,pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug 4142159*/
,decode(pay_core_utils.get_parameter('REP_MODE',legislative_parameters),'SUMM','S','D') report_mode /*Bug 3891577*/
from pay_payroll_actions
where payroll_action_id = c_payroll_action_id;
SELECT /*+ INDEX(pap per_people_f_pk)
INDEX(paa per_assignments_f_fk1)
INDEX(paa per_assignments_f_N12)
INDEX(rppa pay_payroll_actions_pk)
INDEX(pps per_periods_of_service_n3)
*/ distinct paa.assignment_id
from per_people_f pap
,per_assignments_f paa
,pay_payroll_actions rppa
,per_periods_of_service pps
where rppa.payroll_action_id = c_payroll_action_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paa.person_id
and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_fin_year_end)),1,'Y','N')) LIKE c_employee_type
and pps.period_of_service_id = paa.period_of_service_id
and pap.person_id = pps.person_id
and rppa.business_group_id=paa.business_group_id
and nvl(pps.actual_termination_date, c_lst_year_start) >= c_lst_year_start
and c_fin_year_end between pap.effective_start_date and pap.effective_end_date
-- and least(nvl(pps.actual_termination_date,v_fin_year_end),v_fin_year_end) between a.effective_start_date and a.effective_end_date
and paa.effective_end_date = (select max(effective_end_date) /*4377367*/
From per_assignments_f iipaf
WHERE iipaf.assignment_id = paa.assignment_id
and iipaf.effective_end_date >= c_fbt_year_start
and iipaf.effective_start_date <= c_fin_year_end
AND iipaf.payroll_id IS NOT NULL) /*Bug 4688800*/
and paa.payroll_id like c_payroll_id
and paa.assignment_id like c_assignment_id
AND EXISTS (SELECT /*+ INDEX(rpac PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(rpac pay_assignment_actions_n1)
INDEX(rppa PAY_PAYROLL_ACTIONS_N51)
INDEX(rppa PAY_PAYROLL_ACTIONS_PK) */''
FROM per_assignments_f paaf
,pay_payroll_actions rppa
,pay_assignment_actions rpac
where (rppa.effective_date between c_fin_year_start and c_fin_year_end /*Bug3048962 */
or ( pps.actual_termination_date between c_lst_fbt_yr_start and c_fbt_year_end /*Bug3263659 */
and rppa.effective_date between c_fbt_year_start and c_fbt_year_end
and pay_balance_pkg.get_value(c_fbt_defined_balance_id, rpac.assignment_action_id
+ decode(rppa.payroll_id, 0, 0, 0),c_legal_employer,null,null,null,null) > to_number(g_fbt_threshold)) /* Bug 5708255 */
)
and rppa.action_type in ('R','Q','B','I')
and rpac.tax_unit_id = c_legal_employer
and rppa.payroll_action_id = rpac.payroll_action_id
and rpac.action_status = 'C'
and rpac.assignment_id = paaf.assignment_id
and paaf.assignment_id = paa.assignment_id
and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
and rppa.payroll_id = paaf.payroll_id );
SELECT /*+ INDEX(pap per_people_f_pk)
INDEX(paa per_assignments_f_fk1)
INDEX(paa per_assignments_f_N12)
INDEX(rppa pay_payroll_actions_pk)
INDEX(pps per_periods_of_service_n3)
*/ distinct paa.assignment_id
from per_people_f pap
,per_assignments_f paa
,pay_payroll_actions rppa
,per_periods_of_service pps
where rppa.payroll_action_id = c_payroll_action_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paa.person_id
and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_fin_year_end)),1,'Y','N')) LIKE c_employee_type
and pps.period_of_service_id = paa.period_of_service_id
and pap.person_id = pps.person_id
and rppa.business_group_id=paa.business_group_id
and nvl(pps.actual_termination_date, c_lst_year_start) >= c_lst_year_start
and c_fin_year_end between pap.effective_start_date and pap.effective_end_date
-- and least(nvl(pps.actual_termination_date,v_fin_year_end),v_fin_year_end) between a.effective_start_date and a.effective_end_date
and paa.effective_end_date = (select max(effective_end_date) /*4377367*/
From per_assignments_f iipaf
WHERE iipaf.assignment_id = paa.assignment_id
and iipaf.effective_end_date >= c_fbt_year_start
and iipaf.effective_start_date <= c_fin_year_end
AND iipaf.payroll_id IS NOT NULL) /*Bug 4688800*/
and paa.payroll_id like c_payroll_id
and paa.assignment_id = c_assignment_id
AND EXISTS (SELECT /*+ INDEX(rpac PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(rpac pay_assignment_actions_n1)
INDEX(rppa PAY_PAYROLL_ACTIONS_N51)
INDEX(rppa PAY_PAYROLL_ACTIONS_PK) */''
FROM per_assignments_f paaf
,pay_payroll_actions rppa
,pay_assignment_actions rpac
where (rppa.effective_date between c_fin_year_start and c_fin_year_end /*Bug3048962 */
or ( pps.actual_termination_date between c_lst_fbt_yr_start and c_fbt_year_end /*Bug3263659 */
and rppa.effective_date between c_fbt_year_start and c_fbt_year_end
and pay_balance_pkg.get_value(c_fbt_defined_balance_id, rpac.assignment_action_id
+ decode(rppa.payroll_id, 0, 0, 0),c_legal_employer,null,null,null,null) > to_number(g_fbt_threshold)) /* Bug 5708255 */
)
and rppa.action_type in ('R','Q','B','I')
and rpac.tax_unit_id = c_legal_employer
and rppa.payroll_action_id = rpac.payroll_action_id
and rpac.action_status = 'C'
and rpac.assignment_id = paaf.assignment_id
and paaf.assignment_id = paa.assignment_id
and rppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
and rppa.payroll_id = paaf.payroll_id );
SELECT /*+ INDEX(pap per_people_f_pk)
INDEX(rppa pay_payroll_actions_pk)
INDEX(ppr PAY_POPULATION_RANGES_N4)
INDEX(paa per_assignments_f_N12)
INDEX(pps per_periods_of_service_PK)
*/ paa.assignment_id
from per_people_f pap
,per_assignments_f paa
,pay_payroll_actions rppa
,per_periods_of_service pps
,pay_population_ranges ppr
WHERE rppa.payroll_action_id = c_payroll_action_id
AND rppa.payroll_action_id = ppr.payroll_action_id
AND ppr.chunk_number = c_chunk
AND pap.person_id = ppr.person_id
AND pap.person_id = paa.person_id
AND decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_fin_year_end)),1,'Y','N')) LIKE c_employee_type
AND pps.period_of_service_id = paa.period_of_service_id
AND pap.person_id = pps.person_id
AND rppa.business_group_id=paa.business_group_id
AND nvl(pps.actual_termination_date, c_lst_year_start) >= c_lst_year_start
AND c_fin_year_end between pap.effective_start_date AND pap.effective_end_date
AND paa.effective_end_date = (SELECT MAX(effective_end_date) /*4377367*/
FROM per_assignments_f iipaf
WHERE iipaf.assignment_id = paa.assignment_id
AND iipaf.effective_end_date >= c_fbt_year_start
AND iipaf.effective_start_date <= c_fin_year_end
AND iipaf.payroll_id IS NOT NULL) /*Bug 4688800*/
AND paa.payroll_id like c_payroll_id
AND paa.assignment_id like c_assignment_id
AND EXISTS (SELECT /*+ INDEX(rpac PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(rppa PAY_PAYROLL_ACTIONS_N51)
*/''
FROM per_assignments_f paaf
,pay_payroll_actions rppa
,pay_assignment_actions rpac
WHERE (rppa.effective_date between c_fin_year_start AND c_fin_year_end /*Bug3048962 */
or ( pps.actual_termination_date between c_lst_fbt_yr_start AND c_fbt_year_end /*Bug3263659 */
AND rppa.effective_date between c_fbt_year_start AND c_fbt_year_end
AND pay_balance_pkg.get_value(c_fbt_defined_balance_id, rpac.assignment_action_id
+ decode(rppa.payroll_id, 0, 0, 0),c_legal_employer,null,null,null,null) > to_number(g_fbt_threshold)) /* Bug 5708255 */
)
AND rppa.action_type in ('R','Q','B','I')
AND rpac.tax_unit_id = c_legal_employer
AND rppa.payroll_action_id = rpac.payroll_action_id
AND rpac.action_status = 'C'
AND rpac.assignment_id = paaf.assignment_id
AND paaf.assignment_id = paa.assignment_id
AND rppa.effective_date between paaf.effective_start_date AND paaf.effective_end_date
AND rppa.payroll_id = paaf.payroll_id );
select pdb.defined_balance_id
from pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pbt.balance_name ='Fringe Benefits'
and pbt.balance_type_id = pdb.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id /* Bug 2501105 */
and pbd.legislation_code ='AU'
and pbd.dimension_name ='_ASG_LE_FBT_YTD' --2610141
and pbd.legislation_code = pbt.legislation_code
and pbd.legislation_code = pdb.legislation_code;
select pay_assignment_actions_s.nextval
from dual;
SELECT global_value
FROM ff_globals_f
WHERE global_name = 'FBT_THRESHOLD'
AND legislation_code = 'AU'
AND c_year_end BETWEEN effective_start_date
AND effective_end_date ;
hr_utility.set_location('g_parameters.delete_actions..........= '||g_parameters.delete_actions,30); /*Bug 4142159*/
select pap.full_name,
paa.assignment_number,
paa.assignment_id,
to_number(pro.proposed_salary_n) actual_salary,
paa.normal_hours,
pps.actual_termination_date,
pps.date_start,
pgr.name grade,
paa.organization_id,
paa.payroll_id,
hsc.segment1 tax_unit_id,
hou.NAME organization_name,
hou1.name legal_employer
-- papf.payroll_name /*Bug 4688800*/
from per_people_f pap,
per_assignments_f paa,
per_grades_tl pgr,
per_periods_of_service pps,
per_pay_bases ppb,
per_pay_proposals pro,
per_assignment_status_types past,
hr_organization_units hou,
pay_assignment_actions paa1
,hr_soft_coding_keyflex hsc
,hr_organization_units hou1
-- ,pay_payrolls_f papf /*Bug 4688800*/
where pap.person_id = paa.person_id
and paa.assignment_id = paa1.assignment_id
and paa1.assignment_action_id = c_archive_assignment_action_id
and paa.business_group_id = c_business_group_id
and paa.grade_id = pgr.grade_id(+)
and pgr.language(+) = userenv('LANG')
and paa.pay_basis_id = ppb.pay_basis_id(+)
and paa.assignment_id = pro.assignment_id(+)
AND hou.organization_id = paa.organization_id
and hsc.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
and hou1.organization_id = hsc.segment1
-- and papf.payroll_id = paa.payroll_id /*Bug 4688800*/
-- and c_end_date between papf.effective_start_date and papf.effective_end_date /*Bug 4688800*/
and pps.period_of_service_id = paa.period_of_service_id
and paa.assignment_status_type_id = past.assignment_status_type_id
and paa.effective_end_date = ( select max(effective_end_date)
from per_assignments_f
WHERE assignment_id = paa.assignment_id
and effective_end_date >= c_start_date
and effective_start_date <= c_end_date)
and c_end_date between pap.effective_start_date and pap.effective_end_date
and pps.person_id = pap.person_id
and pro.change_date(+) <= c_end_date
and nvl(pro.approved,'Y') = 'Y'
and nvl(pro.change_date,to_date('4712/12/31','YYYY/MM/DD')) = (select nvl(max(pro1.change_date),to_date('4712/12/31','YYYY/MM/DD'))
from per_pay_proposals pro1
where pro1.assignment_id(+) = paa.assignment_id
and pro1.change_date(+) <= c_end_date
and nvl(pro1.approved,'Y')='Y');
SELECT paaf.payroll_id, pay.payroll_name
FROM per_all_assignments_f paaf,
pay_payrolls_f pay
WHERE paaf.assignment_id = c_assignment_id
and paaf.effective_end_date = (select max(effective_end_date)
From per_assignments_f iipaf
WHERE iipaf.assignment_id = c_assignment_id
and iipaf.effective_end_date >= c_start_date
and iipaf.effective_start_date <= c_end_date
AND iipaf.payroll_id IS NOT NULL)
AND pay.payroll_id = paaf.payroll_id
AND paaf.effective_end_date BETWEEN pay.effective_start_date AND pay.effective_end_date;
select pap.full_name,
paf.assignment_number,
paf.assignment_id,
pps.date_start,
pps.actual_termination_date
from per_people_f pap,
per_assignments_f paf,
per_periods_of_service pps,
pay_assignment_actions paa
where pap.person_id = paf.person_id
and paf.assignment_id = paa.assignment_id
and pps.person_id = pap.person_id
and pps.period_of_service_id = paf.period_of_service_id
and paf.business_group_id = c_business_group_id
and paa.assignment_action_id = c_archive_assignment_action_id
and paf.effective_end_date = ( select max(effective_end_date)
from per_assignments_f
WHERE assignment_id = paf.assignment_id
and effective_end_date >= c_start_date
and effective_start_date <= c_end_date)
and c_end_date between pap.effective_start_date and pap.effective_end_date;
select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
,max(paa.action_sequence) action_sequence
from pay_assignment_actions paa
, pay_payroll_actions ppa
, per_assignments_f paf
where paa.assignment_id = paf.assignment_id
and paf.assignment_id = c_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between c_start_date and c_end_date
and ppa.payroll_id = paf.payroll_id
and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
and paa.action_status='C'
AND paa.tax_unit_id = c_tax_unit_id;
select hr.meaning fin_year
from per_assignment_extra_info pae,
hr_lookups hr
where pae.aei_information_category = 'HR_PS_ISSUE_DATE_AU'
and pae.information_type = 'HR_PS_ISSUE_DATE_AU'
and pae.assignment_id = c_assignment_id
and pae.aei_information1 = to_char(c_fin_date,'YY')
and nvl(aei_information2,c_tax_unit_id) = decode(aei_information2,'-999',aei_information2,c_tax_unit_id)
and pae.aei_information1 = hr.lookup_code
and hr.lookup_type = 'AU_PS_FINANCIAL_YEAR';
insert into pay_action_information(
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
assignment_id)
values(
pay_action_information_s.nextval,
g_arc_payroll_action_id,
'PA',
p_effective_date,
null,
l_employee_details.tax_unit_id,
'AU_EMPLOYEE_RECON_DETAILS',
l_employee_details.full_name,
l_employee_details.assignment_number,
l_employee_details.actual_salary,
l_employee_details.grade,
l_employee_details.normal_hours,
l_employee_details.actual_termination_date,
l_fin_year,
l_employee_details.organization_id,
g_tax_unit_id,
l_employee_details.payroll_id,
l_employee_details.organization_name,
l_employee_details.legal_employer,
l_payroll_name, /*Bug 4688800*/
l_employee_details.assignment_id);
g_result_table.delete;
g_result_table.delete;
g_context_table.delete;
select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
,max(paa.action_sequence) action_sequence
from pay_assignment_actions paa
, pay_payroll_actions ppa
, per_assignments_f paf
where paa.assignment_id = paf.assignment_id
and paf.assignment_id = c_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between c_start_date and c_end_date
and ppa.payroll_id = paf.payroll_id
and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
and paa.action_status='C'
AND paa.tax_unit_id = c_tax_unit_id;
SELECT global_value
FROM ff_globals_f
WHERE global_name = c_name
AND legislation_code = 'AU'
AND c_year_end BETWEEN effective_start_date
AND effective_end_date ;
select element_name,label classification_name,sum(amount) payment,sum(hours) hours,rate,balance_type_id
from
(select distinct
nvl(pet.reporting_name, pet.element_name) element_name,
decode(instr(pec.classification_name, 'Earnings'), 0, null,
decode(pet.element_information1,'Y','Allowance',decode(pec2.classification_name, 'Non Taxable', 'Non Taxable Earnings','Taxable Earnings')))|| /* Bug 4179109, 5119734*/
decode(instr(pec.classification_name, 'Payments'), 0, null,
decode(instr(pec.classification_name, 'Direct'), 0, 'Taxable Earnings', 'Direct Payments')) ||
decode(instr(pec.classification_name, 'Deductions'), 0, null,
decode(pec.classification_name , 'Termination Deductions' , 'Tax Deductions'
, 'Involuntary Deductions' , 'Post Tax Deductions'
, 'Voluntary Deductions' , 'Post Tax Deductions'
, pec.classification_name )) ||
decode(instr(pec.classification_name, 'Employer Charges'), 0,null,'Employer Charges') label,
prrv1.result_value amount,
pay_au_rec_det_archive.get_element_payment_hours(prr.assignment_action_id,pet.element_type_id,prr.run_result_id,ppa.effective_date) hours, /*Bug 5603254 */
decode(pay_au_rec_det_archive.get_element_payment_rate(prr.assignment_action_id,pet.element_type_id,prr.run_result_id,ppa.effective_date), null,
(prrv1.result_value/pay_au_rec_det_archive.get_element_payment_hours(prr.assignment_action_id,pet.element_type_id,prr.run_result_id, ppa.effective_date)),
pay_au_rec_det_archive.get_element_payment_rate(prr.assignment_action_id,pet.element_type_id,prr.run_result_id,ppa.effective_date)) rate, /* 5599310 */
prr.run_result_id,
paa.source_action_id
,nvl(pet.element_information2,0) balance_type_id
from pay_element_types_f pet
,pay_input_values_f piv1
,pay_element_classifications pec
,pay_assignment_actions paa
,pay_payroll_actions ppa
,per_assignments_f paaf
,pay_run_results prr
,pay_run_result_values prrv1
,pay_element_classifications pec2
,pay_sub_classification_rules_f pscr
where pet.element_type_id = piv1.element_type_id
and pet.element_type_id = prr.element_type_id
and prr.assignment_action_id = paa.assignment_action_id
and paaf.assignment_id = paa.assignment_id
and paaf.business_group_id = c_business_group_id
and prr.run_result_id = prrv1.run_result_id
and prrv1.input_value_id = piv1.input_value_id
and pet.classification_id = pec.classification_id
and pec.legislation_code = 'AU'
and paaf.assignment_id = c_assignment_id
and paaf.payroll_id = ppa.payroll_id
and ppa.effective_date between c_start_date and c_end_date
and ppa.action_type in ('Q','R','I','B','V')
and paa.action_status = 'C'
and paa.tax_unit_id like c_tax_unit_id
and paa.payroll_action_id = ppa.payroll_action_id
and piv1.name = 'Pay Value'
and pet.classification_id = pec.classification_id
and (instr(pec.classification_name, 'Earnings') > 0
or instr(pec.classification_name, 'Payments') > 0
or instr(pec.classification_name, 'Deductions') > 0
or instr(pec.classification_name, 'Employer Charges' ) > 0 )
and pet.element_type_id = pscr.element_type_id (+)
and ppa.effective_date between nvl(pscr.effective_start_date, ppa.effective_date)
and nvl(pscr.effective_end_date, ppa.effective_date)
and pscr.classification_id = pec2.classification_id(+)
and ppa.date_earned between pet.effective_start_date and pet.effective_end_date
and ppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
and prr.status in ('P','PA')
and NOT EXISTS
(
select 1
from pay_run_results prr1,
pay_element_types_f pet1
where prr1.assignment_action_id = paa.assignment_action_id
and prr1.element_type_id = pet1.element_type_id
and pet1.element_name in ('Retropay GT 12 Mths Amount')
and prr1.source_id = prr.source_id
and prr.source_type='E' /*Bug 4363057 */
)
and NOT EXISTS
(
select 1
from pay_balance_feeds_f pbf,
pay_balance_types pbt
where pbt.balance_type_id = pbf.balance_type_id
and pbt.balance_name in ('Invalidity Payments','Lump Sum A Payments',
'Lump Sum B Payments','Lump Sum C Payments',
'Lump Sum D Payments','Lump Sum E Payments')/*Bug 5846278 */
and pbf.input_value_id = piv1.input_value_id
)
)
group by balance_type_id,element_name,label,rate;
g_balance_type_tab.delete;
insert into pay_action_information (
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
assignment_id)
values (
pay_action_information_s.nextval,
p_assignment_action_id,
'AAP',
p_effective_date,
null,
g_tax_unit_id,
'AU_ELEMENT_RECON_DETAILS',
csr_ele_det.element_name,
csr_ele_det.classification_name,
null,
csr_ele_det.hours,
csr_ele_det.rate,
csr_ele_det.payment,
p_assignment_id);
hr_utility.set_location('After Inserting Element Values ',500);
insert into pay_action_information (
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
assignment_id)
values (
pay_action_information_s.nextval,
p_assignment_action_id,
'AAP',
p_effective_date,
null,
g_tax_unit_id,
'AU_ELEMENT_RECON_DETAILS',
l_ele_name(i),
l_ele_classification_name(i),
null,
null,
null,
l_ele_pay_value(i),
p_assignment_id);
select sum(pay_balance_pkg.get_value(pdb.defined_balance_id,
c_assignment_action_id,
c_tax_unit_id,
null,null,null,null)) --2610141
FROM pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
WHERE pbt.legislation_code = 'AU'
AND pbt.balance_name = 'Lump Sum E Payments'
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbd.dimension_name = '_ASG_LE_PTD';
select max(paa.assignment_action_id) assignment_action_id
from per_assignments_f paf,
pay_payroll_Actions ppa,
pay_assignment_Actions paa,
per_time_periods ptp
where ppa.payroll_Action_id = paa.payroll_Action_id
and paa.assignment_id = c_assignment_id
and paf.assignment_id = paa.assignment_id
and paa.tax_unit_id = c_tax_unit_id
and action_type in ('Q','R','V')
AND (paa.source_action_id IS NULL
OR (paa.source_action_id IS NOT NULL AND ppa.run_type_id IS NULL)) /*Bug 4363057*/
and ppa.effective_date between c_year_start and c_year_end
AND ptp.time_period_id = ppa.time_period_id
and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
GROUP BY ptp.time_period_id;
select nvl(pbt.reporting_name,pbt.balance_name) balance_name, /* Bug 5743196 added nvl */
pdb.defined_balance_id
from pay_balance_types pbt
,pay_defined_balances pdb
,pay_balance_dimensions pbd
where pbt.balance_type_id = c_balance_type_id
and pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.dimension_name = '_ASG_LE_YTD'
and pbd.legislation_code ='AU';
allow_result_table.delete;
allow_context_table.delete;
insert into pay_action_information (
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10
)
values (
pay_action_information_s.nextval,
p_assignment_action_id,
'AAP',
p_effective_date,
null,
g_tax_unit_id,
p_assignment_id,
'AU_BALANCE_RECON_DETAILS_YTD',
l_YTD_TAXABLE_EARNINGS,
l_YTD_NON_TAXABLE_EARNINGS,
l_YTD_DEDUCTIONS,
l_YTD_TAX,
l_YTD_NET_PAYMENT,
l_YTD_EMPLOYER_CHARGES,
l_YTD_GROSS_EARNINGS,
l_YTD_PRE_TAX_DEDUCTIONS,
l_YTD_DIRECT_PAYMENTS,
p_run_action_sequence);
insert into pay_action_information (
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11
)
values (
pay_action_information_s.nextval,
p_assignment_action_id,
'AAP',
p_effective_date,
null,
g_tax_unit_id,
p_assignment_id,
'AU_PS_BALANCE_RECON_DETAILS',
l_YTD_PAYSUM_GROSS,
l_YTD_LUMPSUM_PAY,
l_YTD_ALLOWANCE,
l_YTD_RFB,
l_ETP_PAY,
l_ASSESSABLE_ETP,
l_YTD_CDEP,
l_YTD_OTHER_INCOME,
l_YTD_UNION_FEES,
p_run_action_sequence,
L_YTD_WORKPLACE_GIVING_DED); /* 4015082 */
select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) business_group_id
,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) legal_employer
,pay_core_utils.get_parameter('PAYROLL',legislative_parameters) payroll_id
,pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters) assignment_id
,pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters) employee_type
,to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fin_year_start_date
,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') fin_year_end_date
,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_start_date
,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_end_date
,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_year_term
,pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug 4142159*/
,decode(pay_core_utils.get_parameter('REP_MODE',legislative_parameters),'SUMM','S','D') report_mode /* Bug 3891577*/
,pay_core_utils.get_parameter('OUTPUT_TYPE',legislative_parameters)p_output_type
from pay_payroll_actions
where payroll_action_id = c_payroll_action_id;
SELECT printer,
print_style,
decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
,number_of_copies /* Bug 4116833 */
FROM pay_payroll_actions pact,
fnd_concurrent_requests fcr
WHERE fcr.request_id = pact.request_id
AND pact.payroll_action_id = p_payroll_action_id;
hr_utility.set_location('In Delete Actions'||l_parameters.delete_actions,919); /*Bug 4142159*/
'P_DELETE_ACTIONS='||l_parameters.delete_actions, /*Bug 4142159*/
'BLANKPAGES=NO',
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
);
SELECT DISTINCT pet.element_information2 balance_type_id
FROM pay_element_types_f pet
,per_all_assignments_f paf
,pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_run_results prr
WHERE ppa.effective_date BETWEEN c_start_date AND c_end_date
AND ppa.action_type in ('R','Q','B','V','I')
AND ppa.payroll_id = paf.payroll_id
AND paa.assignment_id = c_assignment_id
AND paa.assignment_id = paf.assignment_id
AND paa.assignment_action_id = prr.assignment_Action_id
AND prr.element_type_id = pet.element_type_id
AND prr.status in ('P','PA')
AND paa.tax_unit_id = c_tax_unit_id
AND paa.action_status ='C'
AND pet.element_information_category = 'AU_EARNINGS'
AND pet.element_information1 = 'Y'
AND paa.payroll_action_id = ppa.payroll_Action_id
AND ppa.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppa.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date;
g_balance_type_tab.delete;
select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) business_group_id
,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) legal_employer
,pay_core_utils.get_parameter('PAYROLL',legislative_parameters) payroll_id
,pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters) assignment_id
,pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters) employee_type
,to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fin_year_start_date
,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') fin_year_end_date
,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_start_date
,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') fbt_year_end_date
,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_year_term
,pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions
,decode(pay_core_utils.get_parameter('REP_MODE',legislative_parameters),'SUMM','S','D') report_mode
from pay_payroll_actions
where payroll_action_id = c_payroll_action_id;
SELECT printer,
print_style,
decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
,number_of_copies
FROM pay_payroll_actions pact,
fnd_concurrent_requests fcr
WHERE fcr.request_id = pact.request_id
AND pact.payroll_action_id = p_payroll_action_id;
hr_utility.set_location('In Delete Actions'||l_parameters.delete_actions,919);
'P_DELETE_ACTIONS='||l_parameters.delete_actions, /*Bug 4142159*/
'BLANKPAGES=NO',
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
);