[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fnd_date.canonical_to_date(pay_nl_taxoffice_archive.get_parameter(legislative_parameters,'REPORT_YEAR'))
,pay_nl_taxoffice_archive.get_parameter(legislative_parameters,'EMPLOYER_ID')
-- ,pay_nl_taxoffice_archive.get_parameter(legislative_parameters,'ORG_HIERARCHY')
,effective_date
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
|Description: This procedure returns a sql string to select a range of|
| assignments eligible for archival |
----------------------------------------------------------------------*/
Procedure RANGE_CODE (pactid IN NUMBER
,sqlstr OUT NOCOPY VARCHAR2) is
v_log_header VARCHAR2(255);
/*Return the SELECT Statement to select a range of assignments
eligible for archival */
sqlstr := 'SELECT DISTINCT person_id
FROM per_people_f ppf
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppa.business_group_id = ppf.business_group_id
ORDER BY ppf.person_id';
whose Employer matches the one selected in the SRS Request
and for which a Record has not already been archived.
*/
CURSOR Cur_EE_ATS_Archive(lp_business_group_id number,lp_employer_id number,
lp_Tax_Year_End_Date Date,
lp_Tax_Year_Start_Date Date,
-- lp_org_struct_version_id number,
lp_start_person_id number,
lp_end_person_id number
) IS
SELECT
paa.organization_id,
pap.person_id , paa.assignment_id, paa.assignment_number,
pap.last_name, pap.Date_of_Birth, pap.full_name
FROM
per_people_f pap
,per_assignments_f paa
,pay_all_payrolls_f ppf
-- per_all_people_f pap Performance fix 5042871
-- ,per_all_assignments_f paa
WHERE pap.business_group_id = lp_business_group_id
and pap.person_id = paa.person_id
and paa.person_id BETWEEN lp_start_person_id AND lp_end_person_id
and lp_Tax_Year_End_Date between pap.effective_start_date and pap.effective_end_date
and paa.effective_start_date =
(
SELECT MIN(asg.effective_start_date)
FROM per_all_assignments_f asg
WHERE asg.assignment_id = paa.assignment_id
and asg.payroll_id is not NULL
and asg.effective_start_date <= lp_Tax_Year_End_Date
and nvl(asg.effective_end_date, lp_Tax_Year_End_Date) >= lp_Tax_Year_Start_Date
)
and paa.payroll_id = ppf.payroll_id
and ppf.business_group_id = lp_business_group_id
and ppf.effective_start_date <= lp_Tax_Year_End_Date
and ppf.effective_end_date >= lp_Tax_Year_Start_Date
and ppf.prl_information_category = 'NL'
and lp_employer_id = ppf.prl_information1;
(select 1 from pay_action_information ee_ats
WHERE ee_ats.action_context_type='AAP'
AND ee_ats.action_information_category = 'NL ATS EMPLOYEE DETAILS'
AND ee_ats.action_information1 = lp_employer_id
AND ee_ats.action_information2 =pap.person_id
AND ee_ats.action_information3 =paa.assignment_id
AND ee_ats.effective_date =lp_Tax_Year_End_Date)
order by pap.person_id,paa.assignment_id;*/
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_asg_act_id
FROM dual;
SELECT 1 INTO l_active_asg_flag
FROM per_all_assignments_f asg, per_assignment_status_types past
WHERE asg.assignment_id = l_assignment_id
and past.assignment_status_type_id = asg.assignment_status_type_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and asg.effective_start_date <= l_Tax_Year_End_Date
and nvl(asg.effective_end_date, l_Tax_Year_End_Date) >= l_Tax_Year_Start_Date;
SELECT MAX(assignment_action_id)
from pay_assignment_actions paa
,pay_payroll_actions ppa
where paa.payroll_action_id =ppa.payroll_action_id
and paa.assignment_id = p_assignment_id
and ppa.date_earned between p_date_from and p_date_to
and ppa.action_type in ('R','B','Q','I','V');
SELECT context_id
FROM ff_contexts ff
WHERE ff.context_name = p_context_name;
select paa.assignment_action_id,ppa.date_earned
from pay_assignment_actions paa
,pay_payroll_actions ppa
where
paa.assignment_id = p_assignment_id and
ppa.payroll_action_id = paa.payroll_action_id and
ppa.date_earned between p_tax_year_start_date and p_tax_year_end_date
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C';
select prr.run_result_id
from pay_run_results prr
where
prr.element_type_id=lp_element_type_id and
prr.assignment_action_id=lp_assignment_action_id;
select ppa.date_earned
into l_eff_date
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = p_assgt_act_id
and ppa.payroll_action_id = paa.payroll_action_id;
SELECT peev.screen_entry_value
INTO l_comp_car
FROM pay_element_types_f pet
,pay_input_values_f piv
,pay_element_entries_f peef
,pay_element_entry_values_f peev
WHERE pet.element_name = 'Company Car Private Usage'
AND pet.element_type_id = piv.element_type_id
AND piv.name = 'Code Usage'
AND pet.legislation_code = 'NL'
AND piv.legislation_code = 'NL'
AND peef.assignment_id = p_assignment_id
AND peef.element_entry_id = peev.element_entry_id
AND peef.element_type_id = pet.element_type_id
AND peev.input_value_id = piv.input_value_id
AND l_eff_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND l_eff_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND l_eff_date BETWEEN peev.effective_start_date
AND peev.effective_end_date
AND l_eff_date BETWEEN peef.effective_start_date
AND peef.effective_end_date;
/* select decode(prrv.result_value,'NL_NONE','0','1') code,ppa.date_earned,paa.assignment_action_id,ptp.start_date --,prrv.result_value
from
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_element_types_f pet,
pay_input_values_f piv,
pay_run_results prr,
pay_run_result_values prrv,
per_time_periods ptp
where
pet.element_name='Standard Tax Deduction' and
pet.element_type_id=piv.element_type_id and
piv.name='Tax Reduction Flag' and
ppa.date_earned between p_tax_year_start_date and p_tax_year_end_date and
ppa.payroll_action_id=paa.payroll_action_id and
paa.assignment_id = p_assignment_id and
prrv.input_value_id=piv.input_value_id and
ppa.date_earned between pet.effective_start_date and pet.effective_end_date and
ppa.date_earned between piv.effective_start_date and piv.effective_end_date and
paa.assignment_action_id=prr.assignment_action_id and
prrv.run_result_id=prr.run_result_id and
ptp.time_period_id=ppa.time_period_id
order by date_earned,paa.assignment_action_id; */
select /*+ORDERED INDEX(ptp PER_TIME_PERIODS_PK) */ decode(prrv.result_value,'NL_NONE','0','1') code,ppa.date_earned,paa.assignment_action_id,ptp.start_date --,prrv.result_value
from
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_element_types_f pet,
pay_input_values_f piv,
pay_run_results prr,
pay_run_result_values prrv,
per_time_periods ptp
where
pet.element_name='Standard Tax Deduction' and
pet.element_type_id=piv.element_type_id and
piv.name='Tax Reduction Flag' and
ppa.date_earned between p_tax_year_start_date and p_tax_year_end_date and
ppa.payroll_action_id=paa.payroll_action_id and
paa.assignment_id = p_assignment_id and
-- ppa.business_group_id = p_bg_id and
prrv.input_value_id=piv.input_value_id and
ppa.date_earned between pet.effective_start_date and pet.effective_end_date and
ppa.date_earned between piv.effective_start_date and piv.effective_end_date and
paa.assignment_action_id=prr.assignment_action_id and
prrv.run_result_id=prr.run_result_id and
ptp.time_period_id=ppa.time_period_id and
ptp.payroll_id = ppa.payroll_id
order by date_earned,paa.assignment_action_id;
/* select count(prrv.result_value) counter,prrv.result_value
from
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_element_types_f pet,
pay_input_values_f piv,
pay_run_results prr,
pay_run_result_values prrv
where
pet.element_name='Standard Tax Deduction' and
pet.element_type_id=piv.element_type_id and
piv.name='Tax Code' and
ppa.date_earned between p_tax_year_start_date and p_tax_year_end_date and
ppa.payroll_action_id=paa.payroll_action_id and
paa.assignment_id = p_assignment_id and
prrv.input_value_id=piv.input_value_id
and
paa.assignment_action_id=prr.assignment_action_id and
ppa.date_earned between pet.effective_start_date and pet.effective_end_date and
ppa.date_earned between piv.effective_start_date and piv.effective_end_date and
prrv.run_result_id=prr.run_result_id
group by prrv.result_value
order by counter desc; */
select /*+ ORDERED */ count(prrv.result_value) counter,prrv.result_value
from
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_element_types_f pet,
pay_input_values_f piv,
pay_run_results prr,
pay_run_result_values prrv
where
pet.element_name='Standard Tax Deduction' and
pet.element_type_id=piv.element_type_id and
piv.name='Tax Code' and
--ppa.business_group_id = p_bg_id and
ppa.date_earned between p_tax_year_start_date and p_tax_year_end_date and
ppa.payroll_action_id=paa.payroll_action_id and
paa.assignment_id = p_assignment_id and
prrv.input_value_id=piv.input_value_id
and
paa.assignment_action_id=prr.assignment_action_id and
ppa.date_earned between pet.effective_start_date and pet.effective_end_date and
ppa.date_earned between piv.effective_start_date and piv.effective_end_date and
prrv.run_result_id=prr.run_result_id
group by prrv.result_value
order by counter desc;
select 1 from dual
where exists
(select /*+ USE_NL(paa, ppa, pet,prr) */ prr.run_result_id,ppa.date_earned from pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_run_results prr
,pay_element_types_f pet
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = p_assignment_id
and paa.assignment_action_id = paa.assignment_action_id
and ppa.date_earned between p_tax_year_start_date and p_tax_year_end_date
and ppa.action_type in ('R','Q','B','I','V')
and paa.assignment_action_id=prr.assignment_action_id
and pet.element_type_id=prr.element_type_id
and pet.element_name=lp_element_name
and ppa.date_earned between pet.effective_start_date and pet.effective_end_date);
select sck.segment8,SUM(decode(sign(p_tax_year_end_date - paa.effective_end_date),-1,p_tax_year_end_date,paa.effective_end_date)-decode(sign(paa.effective_start_date - p_tax_year_start_date),-1,p_tax_year_start_date,paa.effective_start_date)+1) Days
from per_all_assignments_f paa,hr_soft_coding_keyflex sck
where paa.assignment_id = p_assignment_id
and (paa.effective_start_date >= p_tax_year_start_date or p_tax_year_start_date between paa.effective_start_date and paa.effective_end_date)
and (paa.effective_end_date <= p_tax_year_end_date or paa.effective_start_date <= p_tax_year_end_date)
and sck.soft_coding_keyflex_id=paa.soft_coding_keyflex_id
group by sck.segment8
order by Days desc;
select
posv.org_structure_version_id
from
per_organization_structures pos,
per_org_structure_versions posv
where pos.organization_structure_id = posv.organization_structure_id
and to_char(pos.organization_structure_id) = p_org_struct_id
and p_tax_year_end_date between posv.date_from and nvl(posv.date_to,hr_general.End_of_time);
select pur.user_row_id
from pay_user_rows_f pur,
pay_user_tables put
where put.user_table_name='NL_ATS_USER_BALANCES'
and put.legislation_code='NL'
and pur.user_table_id=put.user_table_id
and p_effective_date between pur.effective_start_date and pur.effective_end_date;
vUserBalTable.DELETE;
SELECT puc.user_column_id
INTO vBalColId
FROM pay_user_columns puc,
pay_user_tables put
WHERE put.user_table_name='NL_ATS_USER_BALANCES'
and put.legislation_code='NL'
and put.user_table_id=puc.user_table_id
and puc.user_column_name='BAL_NAME';
SELECT puc.user_column_id
INTO vTagColId
FROM pay_user_columns puc,
pay_user_tables put
WHERE put.user_table_name='NL_ATS_USER_BALANCES'
and put.legislation_code='NL'
and put.user_table_id=puc.user_table_id
and puc.user_column_name='TAG_NAME';
SELECT puci.value
INTO vBalName
FROM pay_user_column_instances_f puci
WHERE puci.user_row_id=v_csr_get_rows.user_row_id
AND puci.user_column_id=vBalColId
AND p_effective_date between puci.effective_start_date and puci.effective_end_date;
SELECT puci.value
INTO vTagName
FROM pay_user_column_instances_f puci
WHERE puci.user_row_id=v_csr_get_rows.user_row_id
AND puci.user_column_id=vTagColId
AND p_effective_date between puci.effective_start_date and puci.effective_end_date;
SELECT u.creator_id
FROM ff_user_entities u,
ff_database_items d
WHERE d.user_name = p_user_name
AND u.user_entity_id = d.user_entity_id
AND (u.legislation_code is NULL )
AND (u.business_group_id = p_bg_id )
AND u.creator_type = 'B';