[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT formula_id
FROM hr_assignment_sets ags
WHERE assignment_set_id = c_asg_set_id
AND EXISTS(SELECT 1
FROM hr_assignment_set_criteria agsc
WHERE agsc.assignment_set_id = ags.assignment_set_id);
SELECT assignment_id, NVL(include_or_exclude
,'I') include_or_exclude
FROM hr_assignment_set_amendments
WHERE assignment_set_id = c_asg_set_id;
select
/* 9081004 */
upper(SUBSTR(trim(pai.action_information18),1,30)) surname
,upper(SUBSTR(trim(pai.action_information19),1,30)) first_name
,upper(pai.action_information1) ppsn
,lpad(upper(pai.action_information2), 9, ' ') works_num
,decode(sign(to_date(pai.action_information24,'DD-MM-YYYY')- cp_start_date),-1,Null,to_char(to_date(pai.action_information24,'DD-MM-YYYY'),'DDMMRR')) hire_date
,nvl(pai_prsi.action_information24,0) total_gross_pay
,nvl(pai_prsi.action_information25,0) total_income_levy
,upper(nvl(rtrim(pact_ade.action_information26),'')) Employer_name
,upper(substr(trim(pact_ade.action_information5),1,30)) address_line1
,upper(substr(trim(pact_ade.action_information6),1,30)) address_line2
,upper(substr(trim(pact_ade.action_information7),1,30)) address_line3
,lpad(translate(pact_ade.action_information28,'1()-', '1'), 11, ' ') Phone_number
,lpad(upper(nvl(rtrim(pact_ade.action_information1),'')), 8, ' ') Employer_number
,upper(substr(trim(pact_ade.action_information11),1,30)) Location_name -- 10277535
,paf.assignment_number assignment_number
,paf.person_id Person_Id
,paf.assignment_id assignment_id /*6876894*/
,substr(trim(pai.action_information21),1,30) emp_Address1 /* 9160076 */
,substr(trim(pai.action_information22),1,30) emp_Address2
,rpad(substr(trim(pai.action_information23),1,30) ,30,' ') emp_County
,pai_prsi.action_information26 asg_location_id -- 10277535
FROM pay_action_information pai /*Employee Details Info*/
,pay_action_information pai_prsi /* prsi Details 5657992 */
,pay_action_information pact_ade /*Address Details - for Employer Name -IE Employer Tax Address*/
,pay_payroll_actions ppa35
,pay_assignment_actions paa
,per_assignments_f paf
,per_periods_of_service pps
,pay_ie_paye_details_f payef -- 10277535
,pay_ie_prsi_details_f prsif
,pay_all_payrolls_f PAPF
WHERE
NVl('N','N') = 'N'
and to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'END_DATE'),'YYYY/MM/DD') between cp_start_date and cp_end_date
-- and cp_start_date <= to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'END_DATE'),'YYYY/MM/DD') /*4641756*/
and ppa35.report_type = 'IEP35'
and ppa35.business_group_id = p_business_group_id /* p_business_group_id */
and paa.payroll_action_id = ppa35.payroll_action_id
and paa.assignment_id = paf.assignment_id
and paa.action_status IN ('C','S') --10225372
and paa.assignment_action_id = pai.action_context_id
and paf.period_of_service_id = pps.period_of_service_id
and paf.person_id= pps.person_id
and paf.business_group_id + 0 = p_business_group_id /*4483028*/
-- Bug 3446744 Checking if the employee has been terminated before issuing the P60
and (pps.actual_termination_date is null or pps.actual_termination_date > cp_end_date)
and paf.effective_start_date = (select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = paf.assignment_id
and asg2.effective_start_date <= cp_end_date
and nvl(asg2.effective_end_date, to_date('31-12-4712','DD-MM-RRRR')) >= cp_start_date)
/*bug 3595646*/
and payef.assignment_id(+)= paa.assignment_id
-- For SR 5108858.993
-- Bug#9503612 Fix commented the following and added code using effective date 10277535
-- 6774415 Changed eff dates to cert dates
and (payef.certificate_start_date is null or payef.certificate_start_date <= cp_end_date) --8229764
and (payef.certificate_end_date IS NULL OR payef.certificate_end_date >= cp_start_date)
--
and payef.effective_start_date(+) <= cp_end_date -- 10277535
and payef.effective_end_date(+) >= cp_start_date -- 10277535
and (payef.effective_end_date = (select max(paye.effective_end_date)
from pay_ie_paye_details_f paye
where paye.assignment_id = paa.assignment_id
and paye.effective_start_date <= cp_end_date -- Bug#9503612 10277535
and paye.effective_end_date >= cp_start_date -- Bug#9503612 10277535
/* --6774415 Changed eff dates to cert dates, nvl for 8229764
and nvl(paye.certificate_start_date, to_date('01/01/0001','DD/MM/YYYY')) <= cp_end_date
and nvl(paye.certificate_end_date,to_date('31/12/4712','DD/MM/YYYY')) >= cp_start_date */
)
or
payef.effective_end_date IS NULL
)
and prsif.assignment_id(+)= paa.assignment_id
-- For SR - 5108858.993, similar changes were made to PRSI as
-- made for PAYE
and prsif.effective_start_date(+) <= cp_end_date /*to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'END_DATE'),'YYYY/MM/DD')*/
and prsif.effective_end_date(+) >= cp_start_date /*to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'START_DATE'),'YYYY/MM/DD')*/
--
and (prsif.effective_end_date = (select max(prsi.effective_end_date)
from pay_ie_prsi_details_f prsi
where prsi.assignment_id = paa.assignment_id
and prsi.effective_start_date <= cp_end_date /*to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'END_DATE'),'YYYY/MM/DD')*/
and prsi.effective_end_date >= cp_start_date /*to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'START_DATE'),'YYYY/MM/DD')*/
)
or
prsif.effective_end_date IS NULL
)
-- Bug 3446744 Removed the check of a P45 existence
/* and not exists (select 1 from pay_assignment_actions paax
,pay_payroll_actions ppax
WHERE
paax.assignment_id = paa.assignment_id
and ppax.payroll_action_id = paax.payroll_action_id
and ppax.report_type = 'P45'
and ppax.business_group_id = ppa35.business_group_id
and ppax.action_status = 'C') */
/*6876894*/
/* removing the check with the assignment set ammendments and checking later for both ammendment set criteria
and ammendments for a particular assignment set id*/
/* AND (p_assignment_set_id IS NULL OR EXISTS (SELECT ' '
FROM HR_ASSIGNMENT_SET_AMENDMENTS HR_ASG
WHERE HR_ASG.ASSIGNMENT_SET_ID=NVL(p_assignment_set_id, HR_ASG.ASSIGNMENT_SET_ID)
AND HR_ASG.ASSIGNMENT_ID=PAA.ASSIGNMENT_ID ))
*/
and PAPF.payroll_id = paf.payroll_id
and PAPF.business_group_id + 0 = p_business_group_id /*4483028*/
and PAPF.payroll_id = nvl(p_payroll_id,papf.payroll_id)
and papf.consolidation_set_id =nvl(p_consolidation_set_id,PAPF.consolidation_set_id)
and PAPF.effective_end_date = (select max(PAPF1.effective_end_date)
from pay_all_payrolls_f PAPF1
where PAPF1.payroll_id = PAPF.payroll_id
and PAPF1.effective_start_date <= cp_end_date --to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'END_DATE'),'YYYY/MM/DD')
and PAPF1.effective_end_date >= cp_start_date --to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'START_DATE'),'YYYY/MM/DD')
)
AND pact_ade.action_information_category = 'ADDRESS DETAILS'
AND pact_ade.action_context_type = 'PA'
AND pai.action_information_category = 'IE P35 DETAIL'
-- added for PRSI section changes 5657992
AND pai_prsi.action_information_category = 'IE P35 ADDITIONAL DETAILS'
AND pai.action_context_id = pai_prsi.action_context_id
-- end 5657992
AND pact_ade.ACTION_CONTEXT_ID = paa.payroll_action_id
and paf.period_of_service_id = pps.period_of_service_id
and paf.person_id= pps.person_id
order by decode(p_sort_order,'Last Name',SUBSTR(trim(pai.action_information18||','|| pai.action_information19),1,30),
'Address Line1',substr(trim(pai.action_information21),1,30),
'Address Line2',substr(trim(pai.action_information22),1,30),
'County',rpad(substr(trim(pai.action_information23),1,30) ,30,' '),
'Assignment Number',paf.assignment_number,
'National Identifier',nvl(pai.action_information1,' '),
SUBSTR(trim(pai.action_information18||','|| pai.action_information19),1,30));
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
WHERE ((c_ppsn is null and paa.assignment_id=c_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 = c_person_id
and paaf.assignment_id=paei.assignment_id
and paei.information_type = 'IE_ASG_OVERRIDE'
and paei.aei_information1 = c_ppsn --'314678745T'
)))
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'||'/'||to_char(cp_end_date,'yyyy'),'dd/mm/yyyy');
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
WHERE paa.assignment_id=c_assignment_id
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'||'/'||to_char(cp_end_date,'yyyy'),'dd/mm/yyyy');
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
WHERE paa.assignment_id=c_assignment_id
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 between cp_start_date and cp_end_date;
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 scl.segment4 paye_ref
FROM per_all_assignments_f paaf,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex scl
WHERE paaf.person_id = c_person_id
AND paaf.assignment_id=c_assignment_id
AND paaf.payroll_id = papf.payroll_id
/* 9255733 */
AND papf.effective_end_date = (select max(PAPF1.effective_end_date)
from pay_all_payrolls_f PAPF1
where PAPF1.payroll_id = papf.payroll_id
and PAPF1.effective_start_date <= cp_end_date
and PAPF1.effective_end_date >= cp_start_date
)
AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_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
org_info1.org_information3 email /* knadhan */
FROM hr_organization_information org_info1
WHERE
org_info1.org_information_context = 'ORG_CONTACT_DETAILS'
AND org_info1.org_information1 ='EMAIL'
AND org_info1.organization_id = l_paye_ref
;
SELECT max(pps.period_of_service_id)
FROM per_periods_of_service pps
,per_assignments_f asg
,pay_all_payrolls_f pay
,hr_soft_coding_keyflex flex
WHERE pps.person_id = v_person_id
AND pps.person_id = asg.person_id
AND asg.period_of_service_id <> pps.period_of_service_id
AND asg.assignment_id = v_assignment_id
AND asg.payroll_id = pay.payroll_id
AND pay.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment4 = v_paye_ref
AND actual_termination_date IS NOT NULL
AND actual_termination_date BETWEEN cp_start_date
AND cp_end_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
WHERE paa.assignment_id in (SELECT assignment_id
FROM per_assignments_f
WHERE period_of_service_id = p_pds_id
and person_id=c_person_id)
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN ('R','Q','I','B','V')
AND paa.action_status IN ('C','S') --10225372
AND paa.source_action_id is null
AND ppa.effective_date<= to_date('30/04'||'/'||to_char(cp_end_date,'yyyy'),'dd/mm/yyyy');
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
WHERE paa.assignment_id in (SELECT paf.assignment_id
FROM per_assignments_f paf, per_assignment_extra_info paei
WHERE paf.period_of_service_id = p_pds_id
AND paf.person_id=c_person_id
AND paf.assignment_id=paei.assignment_id
AND paei.information_type = 'IE_ASG_OVERRIDE'
AND paei.aei_information1 = c_ppsn
)
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN ('R','Q','I','B','V')
AND paa.action_status IN ('C','S') --10225372
AND paa.source_action_id is null
AND ppa.effective_date<= to_date('30/04'||'/'||to_char(cp_end_date,'yyyy'),'dd/mm/yyyy');
SELECT NVL (TRIM (RPAD (hl.ADDRESS_LINE_1, 30)), ' ') address_line1,
NVL (TRIM (RPAD (hl.ADDRESS_LINE_2, 30)), ' ') address_line2,
NVL (TRIM (RPAD (hl.ADDRESS_LINE_3, 30)), ' ') address_line3,
NVL (TRIM (RPAD (hl.LOCATION_CODE, 30)), ' ') Location_name
FROM hr_locations hl
WHERE hl.location_id =c_location_id;
select value into db_charset
from nls_database_parameters
where parameter = 'NLS_CHARACTERSET';