[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),'99999990.00') Q1_Tax_credits --13637469
,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),'99999990.00') Q1_Std_Rate --13637469
,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',
NULL, '2', --7710479
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)
-- + nvl(round(to_number(trim(pai_prsi.action_information22)),2),0) --14659262 --15843742
,'99999990.00') Q1_Total_Pay --13637469
-- bug 5435931
,to_char(nvl(round(to_number(substr(trim(pai.action_information28),1,instr(pai.action_information28,'|',1,1)-1)),2),0),'99999990.00') Q1_Previous_Emp_Pay --13637469
-- bug 5435931
,to_char(nvl(round(to_number(substr(trim(pai.action_information28),instr(pai.action_information28,'|',1,1)+1)),2),0)
-- + nvl(round(to_number(trim(pai_prsi.action_information22)),2),0) --14659262 --15843742
,'99999990.00') Q1_Present_pay --13637469
-- 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),'99999990.00') Q1_Total_Tax --13637469
-- bug 5435931
,to_char(nvl(round(to_number(substr(trim(pai.action_information29),1,instr(pai.action_information29,'|',1,1)-1)),2),0),'99999990.00') Q1_Previous_Emp_Tax --13637469
,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),'99999990.00') Q1_Present_tax --13637469
-- Modified for bug 5657992
,to_char(nvl(round(to_number(trim(pai_prsi.action_information11)),2),0),'99999990.00') Q1_EmployeePRSI --13637469
,to_char(nvl(round(to_number(nvl(trim(pai_prsi.action_information12),0)),2),0),'99999990.00') Q1_TotalPRSI --13637469
,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*/
/* 10277535 start */
--,pai_prsi.action_information26 asg_location_id --11674153
,nvl(to_number(substr(trim(pai_prsi.action_information26),1,instr(pai_prsi.action_information26,'|',1,1)-1)),0) asg_location_id --11674153
,nvl(to_number(substr(trim(pai_prsi.action_information26),instr(pai_prsi.action_information26,'|',1,1)+1)),0) asg_org_id --11674153
,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
,upper(substr(trim(pact_ade.action_information11),1,30)) Location_name -- 10277535
/* 10277535 end */
--11857084
,nvl(pai_prsi.action_information29,0) Q1_grosspay_usc
,nvl(pai_prsi.action_information30,0) Q1_usc_ded
--11857084
--12556436
,nvl(rtrim(pact_ade.action_information29),'') Q1_Employer_Email
,decode(sign(to_char(nvl(round(to_number(substr(trim(pai.action_information29),instr(pai.action_information29,'|',1,1)+1)),2),0),'99999990.00')) --13637469
,1,'D',-1,'R',NULL
) Q1_Refund_Deduction
--12556436
--14659262
--15843742
--,nvl(payef.USC_RATE_1,0) Q1_USC_Rate1
--,nvl(payef.USC_RATE_2,0) Q1_USC_Rate2
/*
,to_char(nvl(decode(papf.period_type,'Lunar Month',round((payef.USC_WKLY_CUTOFF_1*52),2),
decode(instr(papf.period_type,'Week'),0,round((payef.USC_MTHLY_CUTOFF_1*12),2),round((payef.USC_WKLY_CUTOFF_1*52),2))),0),'99999990.00') Q1_USC_Rate1
,to_char(nvl(decode(papf.period_type,'Lunar Month',round((payef.USC_WKLY_CUTOFF_2*52),2),
decode(instr(papf.period_type,'Week'),0,round((payef.USC_MTHLY_CUTOFF_2*12),2),round((payef.USC_WKLY_CUTOFF_2*52),2))),0),'99999990.00') Q1_USC_Rate2
*/
--16249683, 16315861
/*,nvl(payef.USC_YRLY_CUTOFF_1,0) Q1_USC_Rate1
,nvl(payef.USC_YRLY_CUTOFF_2,0) Q1_USC_Rate2*/
,to_char(nvl(NULL,to_char(nvl(decode(papf.period_type,'Lunar Month',round((payef.USC_WKLY_CUTOFF_1*52),2),
decode(instr(papf.period_type,'Week'),0,round((payef.USC_MTHLY_CUTOFF_1*12),2),round((payef.USC_WKLY_CUTOFF_1*52),2))),0),'99999990.00')),'99999990.00') Q1_USC_Rate1
,to_char(nvl(NULL,to_char(nvl(decode(papf.period_type,'Lunar Month',round((payef.USC_WKLY_CUTOFF_2*52),2),
decode(instr(papf.period_type,'Week'),0,round((payef.USC_MTHLY_CUTOFF_2*12),2),round((payef.USC_WKLY_CUTOFF_2*52),2))),0),'99999990.00')),'99999990.00') Q1_USC_Rate2
--16249683, 16315861
--15843742
,to_char(nvl(round(to_number(trim(pai_prsi.action_information22)),2),0),'99999990.00') Q1_Illness_Benefit
,to_char(nvl(round(to_number(substr(trim(pai_prsi.action_information20),1,instr(pai_prsi.action_information20,'|',1,1)-1)),2),0) +
nvl(round(to_number(substr(trim(pai_prsi.action_information20),instr(pai_prsi.action_information20,'|',1,1)+1)),2),0),'99999990.00') Q1_Total_Pay_USC
,to_char(nvl(round(to_number(substr(trim(pai_prsi.action_information20),1,instr(pai_prsi.action_information20,'|',1,1)-1)),2),0),'99999990.00') Q1_Previous_Emp_Pay_USC
,to_char(nvl(round(to_number(substr(trim(pai_prsi.action_information20),instr(pai_prsi.action_information20,'|',1,1)+1)),2),0),'99999990.00') Q1_Present_pay_USC
,to_char(nvl(round(to_number(substr(trim(pai_prsi.action_information21),1,instr(pai_prsi.action_information21,'|',1,1)-1)),2),0) +
nvl(round(to_number(substr(trim(pai_prsi.action_information21),instr(pai_prsi.action_information21,'|',1,1)+1)),2),0),'99999990.00') Q1_Total_USC
,to_char(nvl(round(to_number(substr(trim(pai_prsi.action_information21),1,instr(pai_prsi.action_information21,'|',1,1)-1)),2),0),'99999990.00') Q1_Previous_Emp_USC
,decode(sign(to_char(nvl(round(to_number(substr(trim(pai_prsi.action_information21),instr(pai_prsi.action_information21,'|',1,1)+1)),2),0),'99999990.00'))
,1,'D',-1,'R',NULL
) Q1_USC_Refund_Deduction
,to_char(nvl(round(to_number(substr(trim(pai_prsi.action_information21),instr(pai_prsi.action_information21,'|',1,1)+1)),2),0),'99999990.00') Q1_Present_USC
--14659262
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
-- Bug#9503612 Fix commented the following and added code using effective date
/* -- 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
and payef.effective_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
and paye.effective_start_date <= cp_end_date
and paye.effective_end_date >= cp_start_date
/* --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 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 NVL (TRIM (RPAD (NAME, 30)), ' ') asg_org_name
FROM
hr_all_organization_units
where
business_group_id = p_business_group_id
and organization_id = p_asg_org_id
and cp_effective_date between DATE_FROM and NVL(DATE_TO,to_date('31-12-4712','dd-mm-rrrr'));
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;