[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUBSTR(legislative_parameters,
INSTR(legislative_parameters,p_token)+(LENGTH(p_token)+1),
INSTR(legislative_parameters,' ',
INSTR(legislative_parameters,p_token))
- (INSTR(legislative_parameters,p_token)+LENGTH(p_token))),
business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_pact_id;
select decode(ppos.leaving_reason, 'D','Y','N'),
ppos.actual_termination_date
from per_periods_of_service ppos
where ppos.person_id = p_person_id
and ppos.period_of_service_id = (select max(paf.period_of_service_id)
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locking_action_id = p_action_context_id
and pai.locked_action_id = paa.assignment_action_id
and paa.action_status = 'C'
and paa.assignment_id = paf.assignment_id
);
SELECT max(paaf.effective_end_date)
FROM per_all_assignments_f paaf,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex scl
WHERE paaf.person_id = p_person_id
AND paaf.payroll_id = papf.payroll_id
AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment4 = to_char(g_paye_ref)
AND paaf.assignment_status_type_id in
(SELECT ast.assignment_status_type_id
FROM per_assignment_status_types ast
WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
)
AND paaf.effective_end_date between g_archive_start_date and g_archive_end_date;
SELECT max(effective_end_date)
FROM per_all_assignments_f paaf
WHERE paaf.assignment_id = p_assignment_id
AND paaf.assignment_status_type_id in
(SELECT ast.assignment_status_type_id
FROM per_assignment_status_types ast
WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
);
select fnd_date.canonical_to_date(act_inf.action_information3)
from pay_assignment_actions paa_run,
pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_information act_inf
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.report_type = 'IE_CESSATION'
and ppa.report_qualifier = 'IE'
and ppa.action_type = 'X'
and paa.assignment_action_id = act_inf.action_context_id
and act_inf.action_information_category = 'IE CESS INFORMATION'
and act_inf.action_context_type = 'AAP'
and ppa.payroll_action_id <> g_archive_pact
and paa.assignment_action_id = pai.locking_action_id
and paa.source_action_id is NULL
and pai.locked_action_id = paa_run.assignment_action_id
and paa_run.assignment_id = p_assignment_id
and paa_run.action_status = 'C'
and paa.action_status = 'C';
select pdb.defined_balance_id
from pay_defined_balances pdb
,pay_balance_dimensions pbd
,pay_balance_types pbt
WHERE pbt.balance_name=c_balance_name
AND pbt.balance_type_id=pdb.balance_type_id
and pbd.database_item_suffix=c_dimension_name
and pbd.balance_dimension_id=pdb.balance_dimension_id
and pbt.legislation_code='IE'
and pdb.legislation_code='IE';
select source_id from
pay_action_information pai,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_assignment_actions paa1
where paa.assignment_action_id = p_last_cess_action
and ppa.payroll_action_id=paa.payroll_action_id
and ppa.payroll_action_id=paa1.payroll_action_id
and paa1.assignment_id=p_assignment_id
and paa1.assignment_action_id = pai.action_context_id
and pai.action_information_category='IE CESS INFORMATION'
--order by source_id desc
;
select source_id from
pay_action_information pai,
pay_assignment_actions paa
where paa.assignment_action_id = p_last_cess_action
and paa.assignment_action_id = pai.action_context_id
and pai.action_information_category='IE CESS INFORMATION'
and pai.action_context_type = 'AAP'
;
select source_id , ppa.effective_date from
pay_action_information pai,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_assignment_actions paa1
where paa.assignment_action_id = p_last_cess_action
and ppa.payroll_action_id=paa.payroll_action_id
and ppa.payroll_action_id=paa1.payroll_action_id
and paa1.assignment_id=p_assignment_id
and paa1.assignment_action_id = pai.action_context_id
and pai.action_information_category='IE CESS INFORMATION';
select source_id , ppa.effective_date from
pay_action_information pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_action_id = p_last_cess_action
and ppa.payroll_action_id=paa.payroll_action_id
and paa.assignment_action_id = pai.action_context_id
and pai.action_information_category='IE CESS INFORMATION'
and pai.action_context_type = 'AAP';
select ppa.effective_date
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_action_id=c_assignment_action_id
and ppa.payroll_action_id=paa.payroll_action_id;
select ppf.last_name surname,
ppf.first_name first_name,
ppf.national_identifier PPSN,
paf.assignment_number works_no,
pps.date_start hire_date
from per_assignments_f paf,
per_all_people_f ppf,
per_periods_of_service pps
where paf.person_id = ppf.person_id
and paf.assignment_id = cp_assignment_id
and cp_curr_eff_date between paf.effective_start_date
and paf.effective_end_date
and cp_curr_eff_date between ppf.effective_start_date
and ppf.effective_end_date
and pps.person_id = ppf.person_id
and pps.date_start = (select max(pps1.date_start)
from per_periods_of_service pps1
where pps1.person_id = paf.person_id
and pps1.date_start <= cp_curr_eff_date);
SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
-- ,pay_assignment_actions paa1
WHERE ((c_ppsn is null and paa.assignment_id=p_assignment_id) OR(c_ppsn is not null
and paa.assignment_id in (select paaf.assignment_id
from per_all_assignments_f paaf, per_assignment_extra_info paei
where paaf.person_id = p_person_id
and paaf.assignment_id=paei.assignment_id
and paei.information_type = 'IE_ASG_OVERRIDE'
and paei.aei_information1 = c_ppsn --'314678745T'
))) /* knadhan12 */
AND paa.payroll_action_id=ppa.payroll_action_id
AND ppa.action_type in ('Q','B','R','I','V')
AND ppa.action_status ='C'
AND paa.source_action_id is null
AND ppa.effective_date<= to_date('30/04/2009','dd/mm/yyyy');
SELECT
hrl.address_line_1 employer_tax_addr1,
hrl.address_line_2 employer_tax_addr2,
hrl.address_line_3 employer_tax_addr3,
org_info.org_information2 employer_no,
hrl.telephone_number_1 employer_tax_ref_phone,
org_all.name employer_tax_rep_name,
org_info1.org_information3 email /* knadhan */
FROM hr_all_organization_units org_all
,hr_organization_information org_info
,hr_locations_all hrl
,hr_organization_information org_info1
WHERE org_info.organization_id = org_all.organization_id
AND org_info.org_information_context = 'IE_EMPLOYER_INFO' --for migration changes 4369280
AND org_all.location_id = hrl.location_id (+)
AND org_info1.org_information_context (+) = 'ORG_CONTACT_DETAILS'
AND org_info1.org_information1 (+) ='EMAIL'
AND org_info.organization_id = g_paye_ref
AND org_all.organization_id = org_info1.organization_id (+)
;
select aei_information1 PPSN_OVERRIDE
from per_assignment_extra_info
where assignment_id = p_asg_id
and aei_information_category = 'IE_ASG_OVERRIDE';
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 +0= ppf.business_group_id
ORDER BY ppf.person_id';
sqlstr:='select 1 from dual where to_char(:payroll_action_id) = dummy';
SELECT as1.person_id person_id,
act.assignment_id assignment_id,
act.assignment_action_id run_action_id,
act1.assignment_action_id prepaid_action_id,
as1.assignment_number works_number,
as1.period_of_service_id period_of_service_id
FROM --per_periods_of_service ppos,
per_all_assignments_f as1,
pay_assignment_actions act,
pay_payroll_actions appa,
pay_action_interlocks pai,
pay_assignment_actions act1,
pay_payroll_actions appa2
WHERE /*appa.consolidation_set_id = p_consolidation_id*/
act.tax_unit_id = p_paye_ref
AND appa.effective_date BETWEEN g_archive_start_date AND g_archive_end_date
AND as1.person_id BETWEEN stperson AND endperson
AND as1.effective_end_date between g_archive_start_date AND g_archive_end_date
AND (as1.effective_end_date = (select max(effective_end_date)
from per_all_assignments_f paf1
where paf1.assignment_id = as1.assignment_id
and paf1.assignment_status_type_id in
(SELECT ast.assignment_status_type_id
FROM per_assignment_status_types ast
WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
)
)
AND as1.effective_end_date <> to_date('31-12-4712','DD-MM-YYYY')
)
AND (as1.payroll_id in (select b.payroll_id
from per_assignments_f a,per_assignments_f b
where a.payroll_id = l_payroll_id
and a.person_id = b.person_id
and a.period_of_Service_id = b.period_of_Service_id
and a.period_of_Service_id = as1.period_of_Service_id
and a.person_id = as1.person_id
and a.effective_start_date <= g_archive_end_date
and a.effective_end_date = (select max(effective_end_date)
from per_all_assignments_f paf1
where paf1.assignment_id = a.assignment_id
and paf1.assignment_status_type_id in
(SELECT ast.assignment_status_type_id
FROM per_assignment_status_types ast
WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
)
)
)
OR l_payroll_id is null)
--
AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
AND act.payroll_action_id = appa.payroll_action_id
AND act.source_action_id IS NULL
AND as1.assignment_id = act.assignment_id
AND act.action_status = 'C'
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C'
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
AND appa2.payroll_action_id = (SELECT /*+ USE_NL(ACT2 APPA4)*/
max(appa4.payroll_action_id)
FROM /*pay_pre_payments ppp, --Bug 4193738 --Bug 4468864*/
pay_assignment_actions act2,
pay_payroll_actions appa4
WHERE /*ppp.assignment_action_id=act2.assignment_action_id
AND*/ act2.assignment_id = act.assignment_id
AND act2.action_status = 'C'
AND appa4.payroll_action_id = act2.payroll_action_id
AND appa4.action_type in ('P','U')
AND appa4.effective_date BETWEEN g_archive_start_date AND g_archive_end_date)
-- bug 5597735, change the not exists clause.
-- refer bug 5233518 for more details.
AND NOT EXISTS (SELECT /*+ ORDERED use_nl(appa3)*/ null
from pay_assignment_actions act3,
pay_payroll_actions appa3,
pay_action_interlocks pai, --bug 4208273
pay_assignment_actions act2, --bug 4208273
pay_payroll_actions appa4 --bug 4208273
where pai.locked_action_id= act3.assignment_action_id
and pai.locking_action_id=act2.assignment_action_id
and act3.action_sequence >= act1.action_sequence --bug 4193738
and act3.assignment_id in (select distinct paaf.assignment_id
from per_all_assignments_f paaf
where paaf.person_id = as1.person_id
)
and act3.tax_unit_id = act1.tax_unit_id
and act3.action_status = 'C'
and act2.action_status = 'C'
and act3.payroll_action_id=appa4.payroll_action_id
and appa4.action_type in ('P','U')
and act2.payroll_action_id = appa3.payroll_action_id
and appa3.action_type = 'X'
and appa3.report_type = 'IE_CESSATION')
/* check person does not hold employment with the employer between start of year and archive end date */
AND NOT EXISTS (
SELECT MIN(paf.effective_start_date),MAX(paf.effective_end_date)
FROM per_all_assignments_f paf,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex scl
WHERE paf.person_id = as1.person_id
AND paf.payroll_id = papf.payroll_id
/* changed the cursor to handle case where 2 user defined assignment status exist mapping to
same per_system_status (5073577) */
AND paf.assignment_status_type_id in
(SELECT ast.assignment_status_type_id
FROM per_assignment_status_types ast
WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
)
AND g_archive_end_date between papf.effective_start_date and papf.effective_end_date
AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment4 = to_char(p_paye_ref)
group by paf.assignment_id
having min(paf.effective_start_date) <= g_archive_end_date
and max(paf.effective_end_date) > g_archive_end_date
)
AND as1.person_id =nvl(l_person_id,as1.person_id) /* knadhan */
ORDER BY as1.person_id,as1.assignment_number,act.assignment_id
FOR UPDATE OF as1.assignment_id;
select aei_information1 PPSN_OVERRIDE
from per_assignment_extra_info
where assignment_id = p_asg_id
and aei_information_category = 'IE_ASG_OVERRIDE';
select MIN(paei.assignment_id) ovrride_asg
from per_assignment_extra_info paei
where paei.information_type = 'IE_ASG_OVERRIDE'
and paei.aei_information1 = p_ppsn_override
and exists
(select 1 from per_all_assignments_f paaf
where paaf.assignment_id = paei.assignment_id
and paaf.person_id = p_person_id)
GROUP BY paei.aei_information1; */
select MIN(paei.assignment_id) ovrride_asg
from per_assignment_extra_info paei,per_all_assignments_f paaf
where paei.information_type = 'IE_ASG_OVERRIDE'
and paei.aei_information1 = p_ppsn_override
and paaf.assignment_id = paei.assignment_id
and paaf.person_id = p_person_id
and paaf.period_of_service_id=c_period_of_service_id
GROUP BY paei.aei_information1;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT effective_date
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
SELECT pet.element_type_id,
piv.input_value_id
FROM pay_input_values_f piv,
pay_element_types_f pet
WHERE piv.element_type_id = pet.element_type_id
AND pet.legislation_code = 'IE'
AND pet.element_name = p_element_name
AND piv.name = p_value_name;
SELECT pre.locked_action_id pre_assignment_action_id,
pay.locked_action_id master_assignment_action_id,
assact.assignment_id assignment_id,
assact.payroll_action_id pay_payroll_action_id,
paa.effective_date effective_date,
ppaa.effective_date pre_effective_date,
paa.date_earned date_earned,
ptp.time_period_id time_period_id
FROM pay_action_interlocks pre,
pay_action_interlocks pay,
pay_payroll_actions paa,
pay_payroll_actions ppaa,
pay_assignment_actions assact,
pay_assignment_actions passact,
per_time_periods ptp -- Added to retrieve correct time_period_id 4906850
WHERE pre.locked_action_id = pay.locking_action_id
AND pre.locking_action_id = p_locking_action_id
AND pre.locked_action_id = passact.assignment_action_id
AND passact.payroll_action_id = ppaa.payroll_action_id
AND ppaa.action_type IN ('P','U')
AND pay.locked_action_id = assact.assignment_action_id
AND assact.payroll_action_id = paa.payroll_action_id
AND assact.source_action_id IS NULL
AND ptp.payroll_id = paa.payroll_id
AND paa.date_earned between ptp.start_date and ptp.end_date
and paa.date_earned >= to_date('01/01/2009','dd/mm/yyyy')
--
ORDER BY pay.locked_action_id DESC;
SELECT /*+ USE_NL(paa, ppa) */
fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id in (select assignment_id
from per_all_assignments_f
where person_id = p_person_id
)
AND paa.tax_unit_id = g_paye_ref
AND (paa.source_action_id is not null or ppa.action_type in ('I','V','B'))
AND ppa.effective_date between trunc(p_effective_date,'Y') and p_effective_date
AND paa.action_sequence > p_lat_act_seq
AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
AND paa.action_status = 'C';
SELECT max(lpad(paa_src.action_sequence,15,'0')|| paa_src.assignment_action_id)
FROM pay_payroll_actions ppa_cess,
pay_assignment_actions cess_src,
pay_action_information pai_cess,
pay_assignment_actions paa_src
WHERE ppa_cess.action_type = 'X'
AND ppa_cess.report_type = 'IE_CESSATION'
AND ppa_cess.report_qualifier = 'IE'
AND ppa_cess.payroll_action_id <> p_pact_id
AND ppa_cess.payroll_action_id = cess_src.payroll_action_id
AND cess_src.assignment_action_id = pai_cess.action_context_id
AND pai_cess.action_context_type = 'AAP'
AND pai_cess.action_information_category = 'IE CESS INFORMATION'
AND pai_cess.source_id = paa_src.assignment_action_id
AND cess_src.action_status = 'C'
AND paa_src.tax_unit_id = g_paye_ref
AND cess_src.tax_unit_id = g_paye_ref
AND pai_cess.action_information8 = to_char(p_person_id)
AND ((c_ppsn is not null and pai_cess.action_information22=c_ppsn) or (c_ppsn is null)) /* 8615992 */
;
SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_information pai
WHERE paa.assignment_action_id = pai.action_context_id
AND pai.action_information_category = 'IE CESS INFORMATION'
AND pai.action_context_type = 'AAP'
AND paa.tax_unit_id = g_paye_ref
AND fnd_date.canonical_to_date(pai.action_information3) between trunc(p_termination_date,'Y') and p_termination_date
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.report_type = 'IE_CESSATION'
AND ppa.report_category = 'ARCHIVE'
AND ppa.report_qualifier = 'IE'
AND ppa.effective_date between trunc(g_archive_end_date,'Y') and g_archive_end_date
AND paa.payroll_action_id <> p_pact
AND paa.action_status = 'C'
AND pai.action_information8 = to_char(p_person_id)
AND ((c_ppsn is not null and pai.action_information22=c_ppsn) or (c_ppsn is null)) /* 8615992 */
;
SELECT paa.payroll_action_id
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = p_cess_aact;
SELECT payroll_id,person_id,period_of_service_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_date_earned
BETWEEN effective_start_date AND effective_end_date;
select aei_information1 PPSN_OVERRIDE
from per_assignment_extra_info
where assignment_id = p_asg_id
and aei_information_category = 'IE_ASG_OVERRIDE';
SELECT /*+ USE_NL(paa, ppa) */
fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id in (select paaf.assignment_id
from per_all_assignments_f paaf, per_assignment_extra_info paei
where paaf.person_id = p_person_id
and paaf.assignment_id=paei.assignment_id
and paei.information_type = 'IE_ASG_OVERRIDE'
and paei.aei_information1 = c_ppsn_override --'314678745T'
)
AND paa.tax_unit_id = g_paye_ref
AND (paa.source_action_id is not null or ppa.action_type in ('I','V','B'))
AND ppa.effective_date between trunc(p_effective_date,'Y') and p_effective_date
AND paa.action_sequence > p_lat_act_seq
AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
AND paa.action_status = 'C';
SELECT paa.chunk_number,paa.assignment_id
INTO l_chunk_number,l_assignment_id
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = p_assactid;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
select assignment_id
from pay_assignment_actions
where assignment_action_id=c_assignment_action_id;
select PAI_IEcess.ACTION_INFORMATION20 last_name,
PAI_IEcess.ACTION_INFORMATION21 first_name,
PAI_IEcess.ACTION_INFORMATION22 pps_no,
PAI_IEcess.ACTION_INFORMATION23 works_no,
PAI_IEcess.ACTION_INFORMATION24 date_of_commencement,
PAI_IEcess.ACTION_INFORMATION2 supplementary_flag,
PAI_IEcess.ACTION_INFORMATION3 date_of_leaving,
PAI_IEcess.ACTION_INFORMATION29 gross_pay_total_frm_may09,
PAI_IEcess.ACTION_INFORMATION30 income_levy_frm_may09,
PAI_IEcess.ACTION_INFORMATION27 gross_pay_total_apr_09,
PAI_IEcess.ACTION_INFORMATION28 income_levy_apr_09,
PAI_IEcess.ACTION_INFORMATION25 gross_pay_total_final, /* 8615992 */
PAI_IEcess.ACTION_INFORMATION26 income_levy_final,
PAI_IEcess.ACTION_INFORMATION10 employer_tax_rep_name,
PAI_IEcess.ACTION_INFORMATION11 employer_tax_addr1,
PAI_IEcess.ACTION_INFORMATION12 employer_tax_addr2,
PAI_IEcess.ACTION_INFORMATION13 employer_tax_addr3,
PAI_IEcess.ACTION_INFORMATION14 employer_no,
PAI_IEcess.ACTION_INFORMATION15 employer_tax_ref_phone,
PAI_IEcess.ACTION_INFORMATION16 email,
PAI_IEcess.ACTION_INFORMATION9 date_paid, /* 9337590 */
PAI_IEcess.ACTION_INFORMATION17 usc,
PAI_IEcess.ACTION_INFORMATION18 gross_uscable
from pay_action_information PAI_IEcess
where PAI_IEcess.action_context_id=c_assignment_action_id
AND PAI_IEcess.ACTION_INFORMATION_CATEGORY = 'IE CESS INFORMATION';