[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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) sort_index,
pdb.defined_balance_id defined_balance_id
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')
AND pbd.database_item_suffix = c_database_item_suffix
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;
g_balance_value_tab.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 pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
pay_core_utils.get_parameter('PACTID',legislative_parameters) pact_id,
pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
pay_core_utils.get_parameter('ASG',legislative_parameters) assignment_id,
pay_core_utils.get_parameter('SO1',legislative_parameters) sort_order_1,
pay_core_utils.get_parameter('SO2',legislative_parameters) sort_order_2,
pay_core_utils.get_parameter('SO3',legislative_parameters) sort_order_3,
pay_core_utils.get_parameter('SO4',legislative_parameters) sort_order_4,
to_date(pay_core_utils.get_parameter('PEDATE',legislative_parameters),'YYYY/MM/DD') period_end_date,
pay_core_utils.get_parameter('YTD_TOT',legislative_parameters) ytd_totals,
pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
decode(pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters),'C','Y','T','N','%') employee_type,
pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug# 4142159*/
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = c_payroll_action_id;
SELECT ppa.date_earned
FROM pay_payroll_actions ppa
WHERE
ppa.payroll_action_id = c_payroll_action_id;
hr_utility.set_location('g_parameters.delete_actions..........= '||g_parameters.delete_actions,30);/*Bug# 4142159*/
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
hr_organization_units hou,
per_periods_of_service pps
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = paaf.assignment_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
AND paa.action_status ='C' /*Bug 4099317*/
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and paa.source_action_id is null
and paaf.organization_id = hou.organization_id
and hou.business_group_id = c_business_group_id
and hou.organization_id = c_organization_id
and ppa1.effective_date between c_archive_start_date and c_archive_end_date
and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
From per_assignments_f iipaf
WHERE iipaf.assignment_id = paaf.assignment_id
and iipaf.effective_end_date >= c_archive_start_date
and iipaf.effective_start_date <= c_archive_end_date)
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
hr_organization_units hou,
per_periods_of_service pps
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = paaf.assignment_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
AND paa.action_status ='C' /*Bug 4099317*/
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and paa.source_action_id is null
and paaf.organization_id = hou.organization_id
and hou.business_group_id = c_business_group_id
and NVL(pap.current_employee_flag,'N') like c_employee_type
and hou.organization_id = c_organization_id
and ppa1.payroll_action_id = c_pact_id
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id,
paa.source_action_id master_action_id, /*Bug# 3935471*/
paa2.tax_unit_id master_tax_unit_id /*Bug# 3935471*/
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
pay_assignment_actions paa2, /*Bug# 3935471*/
per_periods_of_service pps
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = paaf.assignment_id /*Bug# 3935471*/
AND paa2.assignment_id = paa.assignment_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
and ppa1.payroll_action_id = paa2.payroll_action_id /*Bug# 3935471*/
AND paa2.action_status ='C' /*Bug# 3935471*/
AND paa.action_status ='C' /*Bug 4099317*/
AND paa2.assignment_action_id = nvl(paa.source_action_id, paa.assignment_action_id) /*Bug# 3935471*/
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and paa.tax_unit_id = c_legal_employer
and ppa1.effective_date between c_archive_start_date and c_archive_end_date
and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
From per_assignments_f iipaf
WHERE iipaf.assignment_id = paaf.assignment_id
and iipaf.effective_end_date >= c_archive_start_date
and iipaf.effective_start_date <= c_archive_end_date)
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id, paa.source_action_id, paa2.tax_unit_id; /*Bug# 3935471*/
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id,
paa.source_action_id master_action_id, /*Bug# 3935471*/
paa2.tax_unit_id master_tax_unit_id /*Bug# 3935471*/
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
pay_assignment_actions paa2, /*Bug# 3935471*/
per_periods_of_service pps
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = paaf.assignment_id
AND paa2.assignment_id = paa.assignment_id /*Bug# 3935471*/
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
AND ppa1.payroll_action_id = paa2.payroll_action_id /*Bug# 3935471*/
AND paa2.assignment_action_id = nvl(paa.source_action_id, paa.assignment_action_id) /*Bug# 3935471*/
AND paa.action_status ='C' /*Bug 4099317*/
AND paa2.action_status = 'C' /*Bug# 3935471*/
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and NVL(pap.current_employee_flag,'N') like c_employee_type
and paa.tax_unit_id = c_legal_employer
and ppa1.payroll_action_id = c_pact_id
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id, paa.source_action_id, paa2.tax_unit_id; /*Bug# 3935471*/
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
per_periods_of_service pps
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = paaf.assignment_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
AND paa.action_status ='C' /*Bug 4099317*/
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and paa.source_action_id is null
and ppa1.effective_date between c_archive_start_date and c_archive_end_date
AND paaf.payroll_id = c_payroll_id /*Bug 4040688*/
and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
From per_assignments_f iipaf
WHERE iipaf.assignment_id = paaf.assignment_id
and iipaf.effective_end_date >= c_archive_start_date
and iipaf.effective_start_date <= c_archive_end_date
AND iipaf.payroll_id IS NOT NULL) /*Bug#4688800*/
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
per_periods_of_service pps
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = paaf.assignment_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
AND paa.action_status ='C' /*Bug 4099317*/
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and paa.source_action_id is null
and NVL(pap.current_employee_flag,'N') like c_employee_type
and ppa1.payroll_id = c_payroll_id
and ppa1.payroll_action_id = c_pact_id
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
per_periods_of_service pps
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = paaf.assignment_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
AND paa.action_status ='C' /*Bug 4099317*/
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and paa.source_action_id is null
and paa.assignment_id = c_assignment_id
and ppa1.effective_date between c_archive_start_date and c_archive_end_date
and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
From per_assignments_f iipaf
WHERE iipaf.assignment_id = paaf.assignment_id
and iipaf.effective_end_date >= c_archive_start_date
and iipaf.effective_start_date <= c_archive_end_date)
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
per_periods_of_service pps
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = paaf.assignment_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
AND paa.action_status ='C' /*Bug 4099317*/
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and paa.source_action_id is null
and NVL(pap.current_employee_flag,'N') like c_employee_type
and paa.assignment_id = c_assignment_id
and ppa1.payroll_action_id = c_pact_id
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
per_periods_of_service pps
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = paaf.assignment_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
AND paa.action_status ='C' /*Bug 4099317*/
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and paa.source_action_id is null
and ppa1.effective_date between c_archive_start_date and c_archive_end_date
and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
From per_assignments_f iipaf
WHERE iipaf.assignment_id = paaf.assignment_id
and iipaf.effective_end_date >= c_archive_start_date
and iipaf.effective_start_date <= c_archive_end_date)
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
per_periods_of_service pps
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = paaf.assignment_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
AND paa.action_status ='C' /*Bug 4099317*/
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and paa.source_action_id is null
and NVL(pap.current_employee_flag,'N') like c_employee_type
and ppa1.payroll_action_id = c_pact_id
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
pay_core_utils.get_parameter('PACTID',legislative_parameters) pact_id,
pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
pay_core_utils.get_parameter('ASG',legislative_parameters) assignment_id,
pay_core_utils.get_parameter('SO1',legislative_parameters) sort_order_1,
pay_core_utils.get_parameter('SO2',legislative_parameters) sort_order_2,
pay_core_utils.get_parameter('SO3',legislative_parameters) sort_order_3,
pay_core_utils.get_parameter('SO4',legislative_parameters) sort_order_4,
to_date(pay_core_utils.get_parameter('PEDATE',legislative_parameters),'YYYY/MM/DD') period_end_date,
pay_core_utils.get_parameter('YTD_TOT',legislative_parameters) ytd_totals,
pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
decode(pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters),'C','Y','T','N','%') employee_type,
pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug# 4142159*/
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = c_payroll_action_id;
SELECT ppa.date_earned
FROM pay_payroll_actions ppa
WHERE
ppa.payroll_action_id = c_payroll_action_id;
select pay_assignment_actions_s.nextval
from dual;
hr_utility.set_location('g_parameters.delete_actions..........= '||g_parameters.delete_actions,30);/*Bug# 4142159*/
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,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
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,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
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,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
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,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
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,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
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,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
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,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
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,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
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,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
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,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
select pap.full_name,
paa.assignment_number,
paa.assignment_id,
paa.organization_id,
hou.NAME organization_name, /*Bug 4132525*/
-- paa.payroll_id, /*Bug 4688800*/
-- papf.payroll_name, /*Bug 4132525, Bug 4688800*/
hsc.segment1 tax_unit_id, /*Bug 4040688*/
hou1.NAME Legal_Employer /*Bug 4132525*/
from per_people_f pap,
per_assignments_f paa,
hr_soft_coding_keyflex hsc, /*Bug 4040688*/
hr_organization_units hou, /*Bug 4132525*/
hr_organization_units hou1 /*Bug 4132525*/
-- pay_payrolls_f papf /*Bug 4132525, Bug 4688800*/
where pap.person_id = paa.person_id
and paa.assignment_id = c_assignment_id
and paa.business_group_id = c_business_group_id
AND hsc.soft_coding_keyflex_id = paa.soft_coding_keyflex_id /*Bug 4040688*/
AND hou.organization_id = paa.organization_id /*Bug 4132525*/
AND hou1.organization_id = hsc.segment1 /*Bug 4132525*/
-- AND papf.payroll_id = paa.payroll_id /*Bug 4132525, Bug 4688800*/
-- AND c_end_date BETWEEN papf.effective_start_date AND papf.effective_end_date /*Bug 4132525, Bug 4688800*/
and paa.effective_end_date = ( select max(effective_end_date) /*Bug#3662449 sub query added*/
from per_assignments_f
WHERE assignment_id = c_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 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 pai.action_information1, pai.tax_unit_id, pai.assignment_id,pai.action_information3
from pay_action_information pai
where action_information_category = 'AU_ARCHIVE_ASG_DETAILS'
and pai.action_context_id = c_arc_ass_act_id;
select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
,max(paa.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 = nvl(c_tax_unit_id, paa.tax_unit_id);
select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
,max(paa.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.payroll_action_id = c_payroll_action_id
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 = nvl(c_tax_unit_id, paa.tax_unit_id);
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,
null,
l_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,
l_max_action_sequence);
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_information8,
action_information9,
action_information10,
assignment_id)
values(
pay_action_information_s.nextval,
g_arc_payroll_action_id,
'PA',
p_effective_date,
null,
l_tax_unit_id,
'AU_EMPLOYEE_RECON_DETAILS',
csr_rec.full_name,
csr_rec.assignment_number,
csr_rec.organization_name, /*Bug 4132525*/
csr_rec.Legal_Employer, /*Bug 4040688, Bug 4132525*/
l_payroll_name, /*Bug 4132525, Bug 4688800*/
l_assignment_id);
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,
l_tax_unit_id,
l_assignment_id,
'AU_BALANCE_RECON_DETAILS_RUN',
l_taxable_earnings,
l_NON_TAXABLE_EARNINGS,
l_DEDUCTIONS,
l_TAX,
l_NET_PAYMENT,
l_EMPLOYER_CHARGES,
l_GROSS_EARNINGS,
l_PRE_TAX_DEDUCTIONS,
l_DIRECT_PAYMENTS,
l_action_sequence);
SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
pay_core_utils.get_parameter('PACTID',legislative_parameters) pact_id,
pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
pay_core_utils.get_parameter('ASG',legislative_parameters) assignment_id,
pay_core_utils.get_parameter('SO1',legislative_parameters) sort_order_1,
pay_core_utils.get_parameter('SO2',legislative_parameters) sort_order_2,
pay_core_utils.get_parameter('SO3',legislative_parameters) sort_order_3,
pay_core_utils.get_parameter('SO4',legislative_parameters) sort_order_4,
to_date(pay_core_utils.get_parameter('PEDATE',legislative_parameters),'YYYY/MM/DD') period_end_date,
pay_core_utils.get_parameter('YTD_TOT',legislative_parameters) ytd_totals,
pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters) employee_type,
pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions, /*Bug# 4142159*/
pay_core_utils.get_parameter('OUTPUT_TYPE',legislative_parameters) output_type /*Bug# 5174524*/
FROM pay_payroll_actions ppa
WHERE ppa.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 action'||l_parameters.delete_actions,19); /*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
);