[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pay_ie_p30lock.get_parameter(legislative_parameters, p_token)
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
sqlstr := 'SELECT distinct asg.person_id
FROM per_periods_of_service pos,
per_assignments_f asg,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND pos.person_id = asg.person_id
AND pos.period_of_service_id = asg.period_of_service_id
AND pos.business_group_id = ppa.business_group_id
AND asg.business_group_id = ppa.business_group_id
ORDER BY asg.person_id';
select /*+ ORDERED USE_NL(pp1 asg)
INDEX(ppa PAY_PAYROLL_ACTIONS_PK)*/ paa.assignment_action_id,
paa.assignment_id,
paa.payroll_action_id,
ppa.date_earned,
paa_run.tax_unit_id
from per_assignments_f asg,
pay_payroll_actions pp1,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_interlocks pai_pre,
pay_assignment_actions paa_run,
pay_payroll_actions ppa_run,
pay_all_payrolls_f pap
,pay_action_information pai --12943478
where pp1.payroll_action_id = pactid
and asg.business_group_id = pp1.business_group_id
and asg.person_id between stperson and endperson
and asg.assignment_id = paa.assignment_id
and paa.source_action_id is null
and paa.payroll_action_id = ppa.payroll_action_id
and ppa_run.effective_date between asg.effective_start_date and asg.effective_end_date
and ppa_run.effective_date between to_date(substr(l_end_date,1,4)||'/01/01','YYYY/MM/DD')
and to_date(l_end_date,'YYYY/MM/DD')
and paa.action_status = 'C'
and ppa.action_type = 'X'
and ppa.report_type = 'IEPS'
and ppa.report_category = 'ARCHIVE'
and pai_pre.locking_action_id = paa.assignment_action_id
and pai_pre.locked_action_id = paa_run.assignment_action_id
and paa_run.action_status IN ('C','S') --10225372
and paa_run.payroll_action_id = ppa_run.payroll_action_id
and paa_run.source_action_id IS NULL
and ppa_run.action_type in ('Q','R')
--12943478
and pai.action_context_id=paa.assignment_action_id
and pai.action_information_category = 'IE EMPLOYEE DETAILS'
and pai.action_context_type='AAP'
--12943478
and not exists (select /*+ INDEX(payact PAY_PAYROLL_ACTIONS_PK) */ null
from pay_action_interlocks alock,
pay_assignment_actions assact,
pay_payroll_actions payact
where alock.locked_action_id = paa.assignment_action_id
and assact.assignment_action_id = alock.locking_action_id
and assact.payroll_action_id = payact.payroll_action_id
and payact.action_type = 'X'
and payact.report_type = 'IEP30_PRGLOCK'
and payact.report_category = 'ARCHIVE'
-- 4317512
/* Added to check whether the archiver is locked by a P30 datalock whose effective date is greater than effective date of payroll
run locked by archiver */
and to_date(substr(payact.legislative_parameters,instr(payact.legislative_parameters,'END_DATE=')+9,10),'YYYY/MM/DD') >= ppa_run.effective_date)
--Added for bug fix 3567562, to restrict assignments to the consoliation set selected.
and asg.payroll_id = pap.payroll_id
and ppa_run.effective_date between pap.effective_start_date and pap.effective_end_date
and (pap.consolidation_set_id = l_consolidation_set or l_consolidation_set is null)
ORDER BY paa.assignment_id,
paa.assignment_action_id;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
select paa.assignment_action_id,
paa.assignment_id,
paa.payroll_action_id,
ppa.date_earned,
paa.tax_unit_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
-- per_assignments_f asg -- Bug Fix 4260031
where ppa.payroll_action_id = l_pay_action_id
-- and asg.business_group_id = ppa.business_group_id -- Bug Fix 4260031
-- and asg.person_id between stperson and endperson
-- and asg.assignment_id = paa.assignment_id
and paa.source_action_id is null
and paa.payroll_action_id = ppa.payroll_action_id
-- and ppa.date_earned between asg.effective_start_date and asg.effective_end_date
and paa.assignment_id in (select asg.assignment_id -- Bug Fix 4260031
from per_assignments_f asg
where asg.business_group_id = ppa.business_group_id
and asg.person_id between stperson and endperson)
and ppa.action_status = 'C'
and ppa.action_type = 'X'
and ppa.report_type = 'IEP30_PRGLOCK'
and ppa.report_category = 'ARCHIVE'
-- bug fix 5371061, added ordered join to remove merge join cartesian.
and not exists (select /*+ ORDERED */ null
from pay_payroll_actions payact,
pay_assignment_actions assact,
pay_action_interlocks alock
where alock.locked_action_id = paa.assignment_action_id
and assact.assignment_action_id = alock.locking_action_id
and assact.payroll_action_id = payact.payroll_action_id
and payact.action_type = 'X'
and payact.report_type = 'IEP30_REPLOCK'
and payact.report_category = 'ARCHIVE')
ORDER BY paa.assignment_id;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT ppa_p30.payroll_action_id report_id,
paa_p30.assignment_id assignment_id,
pact_er.action_information5 employer_paye_number
FROM pay_payroll_actions ppa_p30,
pay_assignment_actions paa_p30,
pay_action_interlocks pai_p30,
pay_assignment_actions paa_arc,
pay_action_interlocks pai_arc,
pay_assignment_actions paa_prepay,
pay_action_interlocks pai_prepay,
pay_action_information pact_er,
pay_action_information pact_ee
-- pay_pre_payments ppp -- Bug Fix 3725003
WHERE ppa_p30.payroll_action_id = c_p30_data_lock_process
AND ppa_p30.payroll_action_id = paa_p30.payroll_action_id
AND paa_p30.assignment_action_id = pai_p30.locking_action_id
AND paa_arc.assignment_action_id = pai_p30.locked_action_id
AND paa_arc.assignment_action_id = pai_arc.locking_action_id
AND paa_prepay.assignment_action_id = pai_arc.locked_action_id
AND paa_prepay.assignment_action_id = pai_prepay.locking_action_id
AND pact_er.action_context_type = 'PA'
AND pact_er.action_information_category = 'EMEA PAYROLL INFO'
AND pact_er.action_context_id = paa_arc.payroll_action_id
AND pact_er.action_information1 = paa_prepay.payroll_action_id
AND pact_ee.action_information_category = 'EMPLOYEE DETAILS'
AND pact_ee.action_context_id = paa_arc.assignment_action_id
--Added for Bug fix : 3725003
-- AND ppp.assignment_action_id = pai_arc.locked_action_id
GROUP BY ppa_p30.payroll_action_id
,paa_p30.assignment_id
,pact_er.action_information5
ORDER BY ppa_p30.payroll_action_id;
SELECT max(ppa_arc.payroll_action_id) archive_pactid
FROM pay_assignment_actions paa_p30,
pay_action_interlocks pai_p30,
pay_assignment_actions paa_arc,
pay_payroll_actions ppa_arc
WHERE paa_p30.payroll_Action_id = c_p30_data_lock_process
AND paa_p30.assignment_action_id = pai_p30.locking_action_id
AND paa_arc.assignment_action_id = pai_p30.locked_action_id
AND ppa_arc.payroll_action_id = paa_arc.payroll_action_id;
SELECT to_char(MIN(ppa_arc.start_date),'DD/MM/RRRR') start_date
FROM pay_assignment_actions paa_p30,
pay_action_interlocks pai_p30,
pay_assignment_actions paa_arc,
pay_payroll_actions ppa_arc
WHERE paa_p30.payroll_Action_id = c_p30_data_lock_process
AND paa_p30.assignment_action_id = pai_p30.locking_action_id
AND paa_arc.assignment_action_id = pai_p30.locked_action_id
AND ppa_arc.payroll_action_id = paa_arc.payroll_action_id;
SELECT substr(pai.action_information5,1,30) employer_tax_addr1
,substr(pai.action_information6,1,30) employer_tax_addr2
,substr(pai.action_information7,1,30) employer_tax_addr3
,substr(pai.action_information26,1,30) employer_tax_contact
,substr(pai.action_information27,1,12) employer_tax_ref_phone
,substr(pai.action_information28,1,30) employer_tax_rep_name
FROM pay_action_information pai
WHERE pai.action_context_id = c_payroll_action_id
AND pai.action_context_type = 'PA'
AND pai.action_information_category = 'ADDRESS DETAILS'
AND pai.action_information14 = 'IE Employer Tax Address';
SELECT pact_ytdbal.action_information4 Balance_Value
FROM pay_assignment_actions paa_p30,
pay_action_interlocks pai_p30,
pay_assignment_actions paa_arc,
pay_action_information pact_ytdbal,
pay_defined_balances pdb_ytdbal,
pay_balance_types pbt_ytdbal,
pay_balance_dimensions pbd_ytdbal,
pay_assignment_actions paa_src,
pay_payroll_actions ppa_src
WHERE paa_p30.payroll_action_id = vp_Payroll_Action_Id
AND paa_p30.assignment_id = vp_Assignment_Id
AND paa_p30.assignment_action_id = pai_p30.locking_action_id
AND paa_arc.assignment_action_id = pai_p30.locked_action_id
AND pact_ytdbal.action_information_category= 'EMEA BALANCES'
AND pact_ytdbal.ACTION_CONTEXT_ID = vp_action_context_id --SR 17318286.6 rbhardwa
AND pact_ytdbal.ACTION_CONTEXT_ID = paa_arc.assignment_action_id
AND pact_ytdbal.ACTION_CONTEXT_TYPE = 'AAP'
AND pdb_ytdbal.defined_balance_id = pact_ytdbal.action_information1
AND pdb_ytdbal.balance_type_id = pbt_ytdbal.balance_type_id
AND pbt_ytdbal.balance_name = vp_Balance_Name
AND pbd_ytdbal.dimension_name = vp_Dimension_Name
AND pbd_ytdbal.balance_dimension_id = pdb_ytdbal.balance_dimension_id
AND pbt_ytdbal.legislation_code = 'IE'
AND pact_ytdbal.action_context_type = 'AAP'
AND paa_src.assignment_action_id = pact_ytdbal.source_id
AND paa_src.payroll_action_id = ppa_src.payroll_action_id
AND ppa_src.effective_date <= vp_effective_date
ORDER BY pact_ytdbal.effective_date DESC -- Fix Tar 4033038.994
,pact_ytdbal.ACTION_CONTEXT_ID DESC
,nvl(pact_ytdbal.action_information5,0) DESC;
SELECT ppa.payroll_action_id prev_data_lock_id,
to_date(substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'END_DATE=')+9,10),'YYYY/MM/DD')
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND ppa.report_type = 'IEP30_PRGLOCK'
AND paa.assignment_id = vp_assignment_id
AND paa.assignment_action_id = (
SELECT to_number(substr(max(lpad(paa2.action_sequence,15,'0')||
paa2.assignment_action_id),16))--Bug No 3318509
FROM pay_payroll_actions ppa2
,pay_assignment_actions paa2
WHERE ppa2.payroll_action_id = paa2.payroll_action_id
AND ppa2.report_type = 'IEP30_PRGLOCK'
AND paa2.assignment_id = vp_assignment_id
AND ppa2.payroll_action_id <> vp_payroll_action_id
AND to_date(substr(ppa2.legislative_parameters ,instr(ppa2.legislative_parameters,'END_DATE=')+9,10),'YYYY/MM/DD')
BETWEEN vp_tax_start_date AND vp_report_end_date)
ORDER BY ppa.payroll_action_id DESC;
SELECT to_date(substr(ppa_p30.legislative_parameters,instr(ppa_p30.legislative_parameters,'END_DATE=')+9,10),'YYYY/MM/DD') end_date
FROM pay_payroll_actions ppa_p30
WHERE ppa_p30.payroll_action_id=vp_payroll_action_id;
SELECT to_date(rule_mode || '/' || to_char(vp_report_end_date,'YYYY'),'dd/mm/yyyy')
FROM pay_legislation_rules
WHERE legislation_code = 'IE'
AND rule_type = 'L';
SELECT fnd_number.canonical_to_number(substr(max(lpad(paa_run.action_sequence,15,'0')||pact_ytdbal.ACTION_CONTEXT_ID),16))
FROM pay_assignment_actions paa_p30,
pay_action_interlocks pai_p30,
pay_assignment_actions paa_arc,
pay_action_information pact_ytdbal,
pay_action_interlocks pai_arc,
pay_assignment_actions paa_run,
pay_payroll_actions ppa_run
-- ,pay_pre_payments ppp --Bug Fix 4049831 Added join with pay_pre_payments table
WHERE paa_p30.payroll_action_id = vp_Payroll_Action_Id
AND paa_p30.assignment_id = vp_Assignment_Id
AND paa_p30.assignment_action_id = pai_p30.locking_action_id
AND paa_arc.assignment_action_id = pai_p30.locked_action_id
AND paa_arc.assignment_action_id = pai_arc.locking_action_id
AND pact_ytdbal.ACTION_CONTEXT_ID = paa_arc.assignment_action_id
AND paa_run.assignment_action_id = pai_arc.locked_action_id
AND pact_ytdbal.action_context_type = 'AAP'
AND paa_run.source_action_id IS NULL
AND paa_run.payroll_action_id = ppa_run.payroll_action_id
AND ppa_run.action_type in ('R','Q');