[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
SUBSTR(trim(pai.action_information18||','|| pai.action_information19),1,30) Q1_Employee
,substr(trim(pai.action_information21),1,30) Q1_Address1
,substr(trim(pai.action_information22),1,30) Q1_Address2
,rpad(substr(trim(pai.action_information23),1,30) ,30,' ') Q1_County
, to_char(cp_end_date,'YYYY') Q1_YEAR /*bug 3595646*/
,nvl(pai.action_information1,' ') Q1_PPSN
,to_char(nvl(decode(papf.period_type,'Lunar Month',round((payef.WEEKLY_TAX_CREDIT*52),2),
decode(instr(papf.period_type,'Week'),0,round((payef.MONTHLY_TAX_CREDIT*12),2),round((payef.WEEKLY_TAX_CREDIT*52),2))),0),'999990.00') Q1_Tax_credits
,to_char(nvl(decode(papf.period_type,'Lunar Month',round((payef.WEEKLY_STD_RATE_CUT_OFF*52),2),
decode(instr(papf.period_type,'Week'),0,round((payef.MONTHLY_STD_RATE_CUT_OFF*12),2),round((payef.WEEKLY_STD_RATE_CUT_OFF*52),2))),0),'999990.00') Q1_Std_Rate
,decode(payef.tax_basis,'IE_WEEK1_MONTH1','W', 'IE_EXEMPT_WEEK_MONTH', 'W') Q1_WM_Indicator
,decode(payef.TAX_BASIS,
'IE_WEEK1_MONTH1' , '1' ,
'IE_EXEMPT_WEEK_MONTH' , '1',
'IE_EMERGENCY','2',
decode(payef.INFO_SOURCE,'IE_P45','1')) Q1_TB_Indicator /* 6982274 */
-- ,decode(payef.TAX_BASIS,'IE_EMERGENCY','2',decode(payef.INFO_SOURCE,'IE_P45','1')) Q1_TB_Indicator
,decode(p_53_indicator,'Y','X') Q1_53_Indicator
,decode(prsif.director_flag,'Y','D') Q1_Director_Indicator
/*4130512 Total Pay must be sum of. ,nvl(round(to_number(trim(pai.action_information16)),2),0) Q1_Total_Pay*/
-- changes made for bug 5435931
,to_char(nvl(round(to_number(substr(trim(pai.action_information28),1,instr(pai.action_information28,'|',1,1)-1)),2),0) +
nvl(round(to_number(substr(trim(pai.action_information28),instr(pai.action_information28,'|',1,1)+1)),2),0),'999990.00') Q1_Total_Pay
-- bug 5435931
,to_char(nvl(round(to_number(substr(trim(pai.action_information28),1,instr(pai.action_information28,'|',1,1)-1)),2),0),'999990.00') Q1_Previous_Emp_Pay
-- bug 5435931
,to_char(nvl(round(to_number(substr(trim(pai.action_information28),instr(pai.action_information28,'|',1,1)+1)),2),0),'999990.00') Q1_Present_pay
-- bug 5435931
,to_char(nvl(round(to_number(substr(trim(pai.action_information29),1,instr(pai.action_information29,'|',1,1)-1)),2),0) +
nvl(round(to_number(substr(trim(pai.action_information29),instr(pai.action_information29,'|',1,1)+1)),2),0),'999990.00') Q1_Total_Tax
-- bug 5435931
,to_char(nvl(round(to_number(substr(trim(pai.action_information29),1,instr(pai.action_information29,'|',1,1)-1)),2),0),'999990.00') Q1_Previous_Emp_Tax
,pai.action_information30 Q1_PR_Indicator
-- bug 5435931
,to_char(nvl(round(to_number(substr(trim(pai.action_information29),instr(pai.action_information29,'|',1,1)+1)),2),0),'999990.00') Q1_Present_tax
-- Modified for bug 5657992
,to_char(nvl(round(to_number(trim(pai_prsi.action_information11)),2),0),'999990.00') Q1_EmployeePRSI
,to_char(nvl(round(to_number(nvl(trim(pai_prsi.action_information12),0)),2),0),'999990.00') Q1_TotalPRSI
,to_number(trim(pai_prsi.action_information13)) Q1_Total_Weeks_Insurable
,pai_prsi.action_information14 Q1_Initial_Contribution_Class
,rpad(pai_prsi.action_information15,2) Q1_Sub_Contribution_Class
,nvl(to_number(trim(pai_prsi.action_information16)),0) Q1_Weeks_In_Later_CC
-- end bug 5657992
,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'),'DD-MON-YYYY')) Q1_Date_Of_Hire
,nvl(rtrim(pact_ade.action_information26),'') Q1_Employer
,nvl(rtrim(pact_ade.action_information1),'') Q1_Employer_RegNo
--Bug No: 6474486 : Employer contact no. is added
,nvl(rtrim(pact_ade.action_information28),'') Q1_Employer_PhoneNo
,to_char(cp_effective_date,'DD-MON-RR') Q1_Report_date /* bug 3595646*/
,paf.assignment_number Q1_Assignment_Number
,paf.person_id Q1_Person_Id
,paf.assignment_id assignment_id /*6876894*/
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
,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 = 'C'
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
-- 6774415 Changed eff dates to cert dates
and payef.certificate_start_date(+) <= cp_end_date
and (payef.certificate_end_date IS NULL OR payef.certificate_end_date >= cp_start_date)
--
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
--6774415 Changed eff dates to cert dates
and paye.certificate_start_date <= 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 value into db_charset
from nls_database_parameters
where parameter = 'NLS_CHARACTERSET';
Select file_data Into p_rtf_blob
From fnd_lobs
Where file_id = p_template_id;