[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT TRIM(SUBSTR
(
legislative_parameters,
DECODE(INSTR
(
legislative_parameters,
p_token
),0,LENGTH(legislative_parameters),INSTR
(
legislative_parameters,
p_token
)) + (LENGTH(p_token) + 1),
DECODE(INSTR
(
legislative_parameters,
' ',
INSTR
(
legislative_parameters,
p_token
)),0,LENGTH(legislative_parameters),INSTR
(
legislative_parameters,
' ',
INSTR
(
legislative_parameters,
p_token
)))
-
(
INSTR
(
legislative_parameters,
p_token
) + LENGTH(p_token)
)
)),
TRIM(business_group_id)
FROM pay_payroll_actions
WHERE payroll_action_id = p_pact_id;
SELECT effective_date
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
select hoi.org_information2 regst_no
,hou.name employer_name
,hoi.org_information3 trade_name
,hla.address_line_1 addr1
,hla.address_line_2 addr2
,hla.address_line_3 addr3
,hoi.org_information4 contact_name
,hla.telephone_number_1 telphone_no
,hla.telephone_number_2 fax
from hr_organization_units hou
,hr_organization_information hoi
,hr_locations_all hla
where hoi.org_information_context='IE_EMPLOYER_INFO'
and hoi.organization_id=c_org_id
and hoi.organization_id=hou.organization_id
and hou.business_group_id= c_bg_id
and hou.location_id=hla.location_id(+);
sqlstr := ' select distinct p.person_id' ||
' from per_people_f p,' ||
' pay_payroll_actions pa' ||
' where pa.payroll_action_id = :payroll_action_id' ||
' and p.business_group_id = pa.business_group_id' ||
' order by p.person_id';
l_select_str VARCHAR2(3000);
SELECT nvl(peev.screen_entry_value,'N')
FROM pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev,
per_all_assignments_f paa
WHERE pet.element_name='IE P45P3_P46 Information'
AND piv.name='P45P3 Or P46 Processed'
AND pet.element_type_id=piv.element_type_id
AND paa.assignment_id=p_assg_id
AND pee.element_type_id=pet.element_type_id
AND pee.assignment_id=paa.assignment_id
AND pee.element_entry_id=peev.element_entry_id
AND piv.input_value_id=peev.input_value_id
--AND peev.effective_start_date between g_start_date and g_end_date
--AND pee.effective_start_date between g_start_date and g_end_date
Order by paa.assignment_id; */
select 'Y'
FROM
pay_payroll_actions ppa,
pay_assignment_actions paa,
PAY_ACTION_INFORMATION pai
WHERE
paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type = 'X'
AND ppa.business_group_id = p_bg_id
AND ppa.action_status = 'C'
AND ppa.report_type = 'IE_P45P3_P46'
AND ppa.report_qualifier = 'IE'
and pai.action_context_id = paa.assignment_action_id
and pai.action_information_category = 'IE_P45P3_P46_DETAILS'
AND paa.assignment_id = p_assg_id
AND pai.action_context_type = 'AAP';
SELECT peev.screen_entry_value P45P3_P46_Processed
FROM pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
WHERE pet.element_name = 'IE P45P3_P46 Information'
and piv.name =c_element_name
and pet.legislation_code = 'IE'
and piv.element_type_id=pet.element_type_id
and pee.element_type_id=pet.element_type_id
and pee.assignment_id =c_asg_id
--and pee.effective_start_date between g_start_date and g_end_date
and peev.element_entry_id=pee.element_entry_id
and peev.input_value_id=piv.input_value_id;
select 'X'
from per_all_assignments_f paa
where paa.assignment_id=c_assignment_id
and not exists
(select 1
from per_all_assignments_f paaf, pay_all_payrolls_f papf, hr_soft_coding_keyflex scl
where paaf.person_id=paa.person_id
and paaf.effective_start_date < paa.effective_start_date
and paaf.effective_end_date > g_end_date
and paaf.organization_id=paa.organization_id
and 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_employer_id)
); */
/*l_select_str := 'select distinct paaf.assignment_id, ppf.person_id from
per_all_assignments_f paaf,
per_all_people_f ppf,
pay_all_payrolls_f papf,
pay_payroll_actions ppa,
hr_soft_coding_keyflex scl
where paaf.business_group_id = '|| g_business_group_id
||' and paaf.effective_start_date between '||''''||g_start_date||''''||' and '
||''''||g_end_date||''''
||' and paaf.person_id = ppf.person_id '
||' and ppf.person_id between '|| stperson || ' AND ' || endperson
||g_where_clause1
||' and papf.business_group_id = paaf.business_group_id '
||' and ppa.payroll_action_id = '||pactid
||' and papf.payroll_id = paaf.payroll_id '
||' and papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id '
||' and scl.segment4 = to_char('||g_employer_id||') '
||g_where_clause
||' Order by paaf.assignment_id '; */
l_select_str := 'select distinct paaf.assignment_id asgid, ppf.person_id perid, pps.period_of_service_id, paaf.assignment_number
from
per_all_assignments_f paaf,
per_all_people_f ppf,
pay_all_payrolls_f papf,
pay_payroll_actions ppa,
hr_soft_coding_keyflex scl,
per_periods_of_service pps
where paaf.business_group_id = '|| g_business_group_id
||' and paaf.effective_start_date between '||''''||g_start_date||''''||' and '
||''''||g_end_date||''''
||' and paaf.person_id = ppf.person_id '
||' and ppf.person_id between '|| stperson || ' AND ' || endperson
||g_where_clause1
||' and papf.business_group_id = paaf.business_group_id '
||' and ppa.payroll_action_id = '||pactid
||' and papf.payroll_id = paaf.payroll_id '
||' and papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id '
||' and scl.segment4 = to_char('||g_employer_id||') '
||g_where_clause
||' and pps.person_id = ppf.person_id '
||' and pps.business_group_id = paaf.business_group_id '
||' and pps.period_of_service_id = paaf.period_of_service_id '
||' and pps.date_start between '||''''||g_start_date||''''||' and '||''''||g_end_date||''''
||' and paaf.effective_start_date between pps.date_start and '||''''||g_end_date||''''
||' Order by ppf.person_id, paaf.assignment_number, paaf.assignment_id ';
hr_utility.set_location('l_select_str'||l_select_str,225);
OPEN csr_get_asg FOR l_select_str; -- ref cursor
hr_utility.set_location('inserting into ASSIGNMENT_ACTIONS', 255);
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
OPEN csr_get_asg FOR l_select_str; -- ref cursor
hr_utility.set_location('inserting into ASSIGNMENT_ACTIONS', 255);
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
-- Insert assignment into PAY_ASSIGNMENT_ACTIONS TABLE
hr_nonrun_asact.insact(lockingactid => lockingactid
,assignid => l_assg_id
,pactid => pactid
,chunk => chunk
,greid => NULL);
SELECT effective_date
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
select hoi.org_information2 regst_no
,hou.name employer_name
,hoi.org_information3 trade_name
,hla.address_line_1 addr1
,hla.address_line_2 addr2
,hla.address_line_3 addr3
,hoi.org_information4 contact_name
,hla.telephone_number_1 telphone_no
,hla.telephone_number_2 fax
from hr_organization_units hou
,hr_organization_information hoi
,hr_locations_all hla
where hoi.org_information_context='IE_EMPLOYER_INFO'
and hoi.organization_id=c_org_id
and hoi.organization_id=hou.organization_id
and hou.business_group_id= c_bg_id
and hou.location_id=hla.location_id(+);
SELECT piv.name input_name,peev.screen_entry_value input_value
FROM pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev,
per_all_assignments_f paa,
pay_assignment_actions paac
WHERE pet.element_name in ('IE P45P3_P46 Information','IE P45 Information')
--AND piv.name='P45P3 Or P46 Processed'
AND pet.element_type_id=piv.element_type_id
AND paa.assignment_id=paac.assignment_id
AND pee.element_type_id=pet.element_type_id
AND pee.assignment_id=paa.assignment_id
AND pee.element_entry_id=peev.element_entry_id
AND piv.input_value_id=peev.input_value_id
AND paac.assignment_action_id=passactid;
SELECT ppf.national_identifier ppsn
,ppf.first_name firstname
,ppf.last_name surname
,ppf.effective_start_date emp_start_date
,pa.address_line1 addr1
,pa.address_line2 addr2
,pa.address_line3 addr3
,paa.assignment_number unit
--,pap.period_type frequency
,decode(pap.period_type,'Lunar Month','W',decode(instr(pap.period_type,'Week'),0,'M','W')) frequency
-- Bug# 7005067
,NULL addr4
,pa.TOWN_OR_CITY City
--,substr(pa.DERIVED_LOCALE,1,instr(pa.DERIVED_LOCALE,',',-1)-1) COUNTY
,flv.meaning COUNTY
,pc.NAME Country_Name
,pa.country
-- Bug# 7005067
FROM per_all_people_f ppf,
per_addresses pa,
per_all_assignments_f paa,
pay_all_payrolls_f pap,
pay_assignment_actions ppaa,
pa_country_v pc, -- Bug# 7005067
fnd_lookup_values flv -- Bug# 7005067
WHERE ppaa.assignment_action_id=passactid
AND ppaa.assignment_id=paa.assignment_id
AND paa.person_id=ppf.person_id
AND ppf.person_id=pa.person_id(+)
AND pap.payroll_id=paa.payroll_id
AND pap.business_group_id=paa.business_group_id
AND ppf.business_group_id=paa.business_group_id
AND pa.country = pc.country_code (+) -- Bug# 7005067
AND pa.style(+) LIKE 'IE%' --6817160
AND pa.primary_flag(+) = 'Y'
AND flv.lookup_type(+) = 'IE_COUNTY' -- Bug# 7005067
AND flv.language(+) = 'US' -- Bug# 7005067
AND flv.lookup_code(+) = pa.region_1 -- Bug# 7005067
AND ppf.effective_start_date between pa.date_from(+) and nvl(pa.date_to, p_effective_date)
AND paa.effective_start_date between ppf.effective_start_date and ppf.effective_end_date
AND paa.effective_start_date = (select min(paa1.effective_start_date)
from per_all_assignments_f paa1
where paa1.assignment_id = paa.assignment_id
and paa1.effective_start_date between g_start_date and g_end_date );
SELECT peev.screen_entry_value
from pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev,
per_all_assignments_f paa,
pay_assignment_actions paac
WHERE pet.element_name = 'IE P45P3_P46 Information'
AND piv.name='P45P3 Or P46 Processed'
AND pet.element_type_id=piv.element_type_id
AND paa.assignment_id=paac.assignment_id
AND pee.element_type_id=pet.element_type_id
AND pee.assignment_id=paa.assignment_id
AND pee.element_entry_id=peev.element_entry_id
AND piv.input_value_id=peev.input_value_id
AND paac.assignment_action_id=passactid
FOR UPDATE OF screen_entry_value;
pl_address.delete;
pl_address_final.delete;
UPDATE pay_element_entry_values_f set screen_entry_value='Y'
WHERE CURRENT OF csr_archive_processed;
SELECT
action_information6 form_type,
action_information7 ppsn,
action_information8 surname,
action_information9 firstname,
action_information10 addr1,
action_information11 addr2,
action_information12 addr3,
action_information13 addr4,
action_information14 emp_start_dt,
action_information15 unit,
action_information16 frequency,
action_information17 prv_emp_strt_dt,
action_information18 prv_emp_end_dt,
trim(to_char(fnd_number.canonical_to_number(nvl(action_information19,0)) ,'99999990.99')) paya,
trim(to_char(fnd_number.canonical_to_number(nvl(action_information20,0)) ,'99999990.99')) taxa,
trim(to_char(fnd_number.canonical_to_number(action_information21) ,'99999990.99')) payb,
trim(to_char(fnd_number.canonical_to_number(action_information22) ,'99999990.99')) taxb,
action_information23 refunded,
action_information24 paye_regst
FROM pay_action_information
WHERE
action_information_category = c_action_type
AND Action_context_id = c_asg_act_id;
SELECT
action_information6 regt_no,
action_information7 emplyr_name,
action_information8 trade_name,
action_information9 addr1,
action_information10 addr2,
action_information11 addr3,
action_information12 contact_name,
action_information13 phone,
action_information14 fax
FROM pay_action_information
WHERE action_context_id = c_pact_id
AND action_context_type = 'PA'
AND action_information_category ='IE P45P3 P46 EMPLOYER';