[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT org_information10
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'Business Group Information';
select meaning
from hr_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code;
SELECT org_information1
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'KW_BG_DETAILS';
SELECT ORG_INFORMATION1
FROM HR_ORGANIZATION_INFORMATION
WHERE Organization_id = l_emp_id
AND org_information_context = 'KW_SI_DETAILS';
SELECT LPAD(org_information4,9,'0')
FROM hr_organization_information
WHERE organization_id = p_employer_id
AND org_information_context = 'KW_LEGAL_EMPLOYER_DETAILS';
SELECT name
FROM hr_organization_units
WHERE organization_id = p_employer_id;
SELECT u.creator_id
FROM ff_user_entities u,
ff_database_items d
WHERE d.user_name = p_user_name
AND u.user_entity_id = d.user_entity_id
AND u.legislation_code = 'KW'
AND u.business_group_id is null
AND u.creator_type = 'B';
SELECT distinct asg.person_id
,asg.assignment_id
,paa.assignment_action_id
FROM per_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_people_f ppf
WHERE asg.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND trunc(ppa.date_earned,'MM') = TRUNC(l_date, 'MM')
AND trunc(l_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(l_employer_id)
AND ppf.person_id = asg.person_id
AND trunc(l_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
AND ppf.nationality = l_nat;
select decode(trunc(ppa.date_earned,'YYYY'),trunc(l_date,'YYYY'),trunc(l_date,'YYYY'),ppa.date_earned) , paa.assignment_action_id
from pay_payroll_actions ppa, pay_assignment_actions paa
Where paa.assignment_id = l_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status = 'C'
order by ppa.date_earned asc;
select org_information3
from hr_organization_information
where org_information_context = 'ORG_CONTACT_DETAILS'
and organization_id = p_employer_id
and org_information1 = l_type;
SELECT pp.phone_number
FROM per_phones pp,per_people_f ppf
WHERE pp.parent_id = ppf.person_id
AND pp.phone_type = l_ph_type
AND ppf.person_id = l_person_id
AND l_effective_date between pp.date_from and nvl(pp.date_to,to_date('31-12-4712','DD-MM-YYYY'));
select location_id
from hr_organization_units
where organization_id = p_employer_id
and business_group_id = p_business_group_id;
select address_line_1 || decode(address_line_2,null,null,',') || address_line_2 , postal_code
from hr_locations
where location_id = l_location_id;
SELECT ppf.full_name
FROM per_people_f ppf
WHERE ppf.person_id = l_person_id
AND l_effective_date between ppf.effective_start_date and ppf.effective_end_date;
SELECT hsck.segment2,paf.job_id
FROM per_assignments_f paf,hr_soft_coding_keyflex hsck
WHERE paf.person_id = l_person_id
AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND hsck.segment1 = p_employer_id
AND l_effective_date between paf.effective_start_date and paf.effective_end_date;
SELECT pjb.name
FROM per_assignments_f paf,per_jobs pjb
WHERE paf.person_id = l_person_id
AND pjb.job_id = paf.job_id
AND l_effective_date between paf.effective_start_date and paf.effective_end_date;
select ppa.date_earned, paa.assignment_action_id
from pay_payroll_actions ppa, pay_assignment_actions paa
Where paa.assignment_id = l_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status = 'C'
and ppa.date_earned <= l_date
order by ppa.date_earned ASC;
select paa.assignment_action_id
from pay_payroll_actions ppa, pay_assignment_actions paa
Where paa.assignment_id = l_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status = 'C'
and trunc(ppa.date_earned,'MM') = trunc(l_date,'MM') ;
INSERT INTO fnd_sessions (session_id, effective_date)
VALUES (userenv('sessionid'), l_effective_date);
vXMLTable.DELETE;
SELECT org_information1
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'KW_BG_DETAILS';
SELECT LPAD(org_information4,9,'0')
FROM hr_organization_information
WHERE organization_id = p_employer_id
AND org_information_context = 'KW_LEGAL_EMPLOYER_DETAILS';
SELECT name
FROM hr_organization_units
WHERE organization_id = p_employer_id;
SELECT u.creator_id
FROM ff_user_entities u,
ff_database_items d
WHERE d.user_name = p_user_name
AND u.user_entity_id = d.user_entity_id
AND u.legislation_code = 'KW'
AND u.business_group_id is null
AND u.creator_type = 'B';
SELECT distinct asg.person_id
,asg.assignment_id
,paa.assignment_action_id
,ppa.date_earned
FROM per_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_people_f ppf
WHERE asg.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND trunc(ppa.date_earned,'MM') = TRUNC(l_date, 'MM')
AND trunc(l_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(l_employer_id)
AND ppf.person_id = asg.person_id
AND trunc(l_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
AND ppf.nationality = l_nat;
SELECT distinct asg.person_id
,asg.assignment_id
,paa.assignment_action_id
,ppa.date_earned
FROM per_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_people_f ppf
WHERE asg.assignment_id = p_assignment_id
AND asg.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND trunc(ppa.date_earned,'MM') = TRUNC(l_date, 'MM')
AND trunc(l_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(l_employer_id)
AND ppf.person_id = asg.person_id
AND trunc(l_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
AND ppf.nationality = l_nat;
SELECT decode(l_format,'L',PER_INFORMATION3,SUBSTR(first_name,1,60)) first_name ,
decode(l_format,'L',PER_INFORMATION4,SUBSTR(PER_INFORMATION1,1,60)) father_name ,
decode(l_format,'L',PER_INFORMATION5,SUBSTR(PER_INFORMATION2,1,60)) grandfather_name,
decode(l_format,'L',PER_INFORMATION6,SUBSTR(last_name,1,60)) last_name
FROM per_people_f ppf
WHERE person_id = p_person_id
AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
select ppa.date_earned, paa.assignment_action_id
from pay_payroll_actions ppa, pay_assignment_actions paa
Where paa.assignment_id = l_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status = 'C'
and ppa.date_earned <= l_date
order by ppa.date_earned DESC;
SELECT hsck.segment2
FROM per_assignments_f paf,hr_soft_coding_keyflex hsck
WHERE paf.person_id = l_person_id
AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND hsck.segment1 = p_employer_id
AND l_effective_date between paf.effective_start_date and paf.effective_end_date;
SELECT pos.actual_termination_date,pos.leaving_reason
FROM per_periods_of_service pos , per_assignments_f paf
WHERE paf.assignment_id = l_assignment_id
AND paf.period_of_service_id = pos.period_of_service_id
AND trunc(pos.actual_termination_date,'MM') = trunc(l_date,'MM')
AND trunc(l_date,'MM') between trunc(paf.effective_start_date,'MM') and paf.effective_end_date;
SELECT meaning
FROM hr_lookups
WHERE lookup_type = 'LEAV_REAS'
AND lookup_code = l_code
AND enabled_flag = 'Y';
SELECT pos.date_start
from per_periods_of_service pos, per_assignments_f paf
WHERE paf.assignment_id = l_assignment_id
AND paf.period_of_service_id = pos.period_of_service_id
AND trunc(pos.date_start,'MM') = trunc(l_date,'MM')
AND trunc(l_date,'MM') between trunc(paf.effective_start_date,'MM') and paf.effective_end_date;
INSERT INTO fnd_sessions (session_id, effective_date)
VALUES (userenv('sessionid'), l_effective_date);
vXMLTable.DELETE;
t_store_recs.DELETE;
SELECT org_information1
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'KW_BG_DETAILS';
SELECT ORG_INFORMATION1
FROM HR_ORGANIZATION_INFORMATION
WHERE Organization_id = l_emp_id
AND org_information_context = 'KW_SI_DETAILS';
SELECT LPAD(org_information4,9,'0')
FROM hr_organization_information
WHERE organization_id = p_employer_id
AND org_information_context = 'KW_LEGAL_EMPLOYER_DETAILS';
SELECT name
FROM hr_organization_units
WHERE organization_id = p_employer_id;
SELECT u.creator_id
FROM ff_user_entities u,
ff_database_items d
WHERE d.user_name = p_user_name
AND u.user_entity_id = d.user_entity_id
AND u.legislation_code = 'KW'
AND u.business_group_id is null
AND u.creator_type = 'B';
SELECT distinct asg.person_id
,asg.assignment_id
,paa.assignment_action_id
,ppa.date_earned
,decode(trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM'),TRUNC(l_date, 'MM'),'Y','N') term_flag
FROM per_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
,per_people_f ppf
WHERE asg.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pos.period_of_service_id = asg.period_of_service_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND trunc(ppa.date_earned,'MM') = TRUNC(l_date, 'MM')
AND ( trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM') = TRUNC(l_date, 'MM')
OR trunc(pos.date_start, 'MM') = trunc(l_date, 'MM') )
AND trunc(l_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(p_employer_id)
AND ppf.person_id = asg.person_id
AND trunc(l_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
AND ppf.nationality = l_nat;
SELECT decode(l_format,'L',PER_INFORMATION3,SUBSTR(first_name,1,60)),
decode(l_format,'L',PER_INFORMATION6,SUBSTR(last_name,1,60)),
decode(l_format,'L',PER_INFORMATION4,per_information1),
decode(l_format,'L',PER_INFORMATION5,per_information2),
sex,date_of_birth, pos.date_start , national_identifier , per_information9,per_information10,
fnd_date.canonical_to_date(per_information11)
FROM per_people_f ppf , per_periods_of_service pos
WHERE ppf.person_id = p_person_id
AND ppf.person_id = pos.person_id
AND l_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND trunc(l_effective_date,'MM') between trunc(pos.date_start,'MM') and nvl(pos.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY'));
SELECT hsck.segment2,job_id , fnd_date.canonical_to_date(hsck.segment3)
FROM per_assignments_f paf,hr_soft_coding_keyflex hsck
WHERE paf.person_id = l_person_id
AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND hsck.segment1 = p_employer_id
AND l_date between paf.effective_start_date and paf.effective_end_date;
SELECT hl.meaning
FROM hr_lookups hl
WHERE hl.lookup_type = 'SEX'
AND hl.lookup_code = l_sex
AND hl.enabled_flag = 'Y';
SELECT name
FROM per_jobs
WHERE job_id = l_job
AND l_date between date_from and nvl(date_to, to_date('31-12-4712','dd-mm-yyyy'));
SELECT substr(addr.ADDRESS_LINE1 || ' ' ||addr.address_line2,1,120)
FROM per_addresses addr
WHERE addr.person_id = l_person_id
AND l_date between addr.date_from and nvl(addr.date_to,to_date('31-12-4712','dd-mm-yyyy'))
AND addr.primary_flag = 'Y';
select ppa.date_earned, paa.assignment_action_id
from pay_payroll_actions ppa, pay_assignment_actions paa
Where paa.assignment_id = l_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status = 'C'
and ppa.date_earned <= l_date
order by ppa.date_earned DESC;
select ppa.date_earned, paa.assignment_action_id
from pay_payroll_actions ppa, pay_assignment_actions paa
Where paa.assignment_id = l_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status = 'C'
and ppa.date_earned <= l_date
order by ppa.date_earned ASC;
select paa.assignment_action_id
from pay_payroll_actions ppa, pay_assignment_actions paa
Where paa.assignment_id = l_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status = 'C'
and trunc(ppa.date_earned,'MM') = trunc(l_date,'MM') ;
SELECT rrv.RESULT_VALUE val,pee.entry_information1 ref,pee.entry_information2 authority,pee.entry_information3 type,
pee.entry_information4 debt,fnd_date.canonical_to_date(pee.entry_information5) start_d
,fnd_date.canonical_to_date(pee.entry_information6) end_d ,pet.element_type_id
FROM pay_element_types_f pet,
pay_element_entries_f pee,
pay_run_results prr,
pay_run_result_values rrv,
pay_input_values_f piv
WHERE rrv.RUN_RESULT_ID = prr.RUN_RESULT_ID
AND prr.assignment_action_id = l_assignment_action_id
AND prr.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
AND piv.element_type_id = pet.element_type_id
AND piv.name = 'Pay Value'
AND rrv.input_value_id = piv.input_value_id
AND pee.assignment_id = l_assignment_id
AND TRUNC(l_effective_date,'MM') between trunc(pee.effective_start_date,'MM') and nvl(pee.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
AND TRUNC(l_effective_date,'MM') between trunc(piv.effective_start_date,'MM') and nvl(piv.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
AND pee.element_type_id = pet.element_type_id
AND pee.entry_information3 is not null
AND rrv.result_value is not null
AND TRUNC(l_effective_date,'MM') between trunc(pet.effective_start_date,'MM') and nvl(pet.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'));
select distinct eev1.SCREEN_ENTRY_VALUE val, eev2.SCREEN_ENTRY_VALUE ref, eev3.SCREEN_ENTRY_VALUE authority, '72' type,
eev4.SCREEN_ENTRY_VALUE debt,eev1.effective_start_date start_d,eev1.effective_end_date end_d, pet.element_type_id
from pay_element_entry_values_f eev1,
pay_element_types_f pet,
pay_element_entries_f pee,
pay_input_values_f piv1,
pay_input_values_f piv2,
pay_input_values_f piv3,
pay_input_values_f piv4,
pay_element_entry_values_f eev2,
pay_element_entry_values_f eev3,
pay_element_entry_values_f eev4
where pet.element_name = 'Additional Social Insurance Information'
AND pet.element_type_id = pee.element_type_id
AND pee.assignment_id = l_assignment_id
AND pee.element_entry_id = eev1.element_entry_id
AND PAY_PAYWSMEE_PKG.PROCESSED(pee.element_entry_id,pee.original_entry_id , pet.processing_type , pee.entry_type, l_effective_date) = 'Y'
AND piv1.element_type_id = pet.element_type_id
AND piv1.name = 'Amount'
AND eev1.input_value_id = piv1.input_value_id
AND TRUNC(l_effective_date,'MM') between trunc(eev1.effective_start_date,'MM') and nvl(eev1.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
AND pee.element_entry_id = eev2.element_entry_id
AND piv2.element_type_id = pet.element_type_id
AND piv2.name = 'Reference Number'
AND eev2.input_value_id = piv2.input_value_id
AND TRUNC(l_effective_date,'MM') between trunc(eev2.effective_start_date,'MM') and nvl(eev2.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
AND pee.element_entry_id = eev3.element_entry_id
AND piv3.element_type_id = pet.element_type_id
AND piv3.name = 'Deduction Authority'
AND eev3.input_value_id = piv3.input_value_id
AND TRUNC(l_effective_date,'MM') between trunc(eev3.effective_start_date,'MM') and nvl(eev3.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
AND pee.element_entry_id = eev4.element_entry_id
AND piv4.element_type_id = pet.element_type_id
AND piv4.name = 'Total Deduction Amount'
AND eev4.input_value_id = piv4.input_value_id
AND TRUNC(l_effective_date,'MM') between trunc(eev4.effective_start_date,'MM') and nvl(eev4.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
AND TRUNC(l_effective_date,'MM') between trunc(pet.effective_start_date,'MM') and nvl(pet.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
AND TRUNC(l_effective_date,'MM') between trunc(pee.effective_start_date,'MM') and nvl(pee.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
AND TRUNC(l_effective_date,'MM') between trunc(piv1.effective_start_date,'MM') and nvl(piv1.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
AND TRUNC(l_effective_date,'MM') between trunc(piv2.effective_start_date,'MM') and nvl(piv2.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
AND TRUNC(l_effective_date,'MM') between trunc(piv3.effective_start_date,'MM') and nvl(piv3.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
AND TRUNC(l_effective_date,'MM') between trunc(piv4.effective_start_date,'MM') and nvl(piv4.effective_end_date,to_date('31-12-4712','DD-MM-YYYY')); */
SELECT pos.actual_termination_date , pos.leaving_reason
FROM per_periods_of_service pos
WHERE pos.person_id = l_person_id
AND trunc(l_date,'MM') between trunc(pos.date_start,'MM') and nvl(actual_termination_date,to_date('31-12-4712','dd-mm-yyyy'));
SELECT hl.meaning
FROM hr_lookups hl
WHERE hl.lookup_type = 'LEAV_REAS'
AND hl.lookup_code = l_code
AND hl.enabled_flag = 'Y';
SELECT employer_name
FROM per_previous_employers
WHERE person_id = l_person_id
ORDER by previous_employer_id DESC;
SELECT hl.meaning
FROM hr_lookups hl
WHERE hl.lookup_type = 'KW_DEDUCTION_CODES'
AND hl.lookup_code = l_type
AND hl.enabled_flag = 'Y';
SELECT ppf.person_id , paf.assignment_id , decode(trunc(pos.actual_termination_date,'MM'),trunc(l_date,'MM'),'Y','N') , decode(trunc(pos.date_start,'MM'),trunc(l_date,'MM'),'Y','N')
FROM per_people_f ppf, per_periods_of_service pos , per_assignments_f paf
WHERE paf.assignment_id = l_asg_id
AND paf.person_id = ppf.person_id
AND paf.period_of_service_id = pos.period_of_service_id
AND paf.person_id = pos.person_id
AND ppf.nationality = l_nat
AND trunc(l_date,'MM') between trunc(paf.effective_start_date,'MM') and paf.effective_end_date
AND trunc(l_date,'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
AND trunc(l_date,'MM') between trunc(pos.date_start,'MM') and nvl(pos.actual_termination_date,to_date('31-12-4712','dd-mm-yyyy'));
select paa.assignment_action_id , ppa.date_earned
from pay_payroll_actions ppa, pay_assignment_actions paa
Where paa.assignment_id = l_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status = 'C'
and trunc(ppa.date_earned,'MM') = trunc(l_date,'MM') ;
SELECT min(ppa.date_earned)
FROM pay_payroll_actions ppa, pay_assignment_actions paa
WHERE paa.assignment_id = l_assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status = 'C'
and ppa.date_earned > l_date;
INSERT INTO fnd_sessions (session_id, effective_date)
VALUES (userenv('sessionid'), l_effective_date);
vXMLTable.DELETE;
select userenv('LANGUAGE') into g_nls_db_char from dual;
Select file_data
Into p_pdf_blob
From fnd_lobs
Where file_id = (select max(file_id) from fnd_lobs where file_name like '%PAY_R55_ar_KW.pdf');
Select file_data
Into p_pdf_blob
From fnd_lobs
Where file_id = (select max(file_id) from fnd_lobs where file_name like '%PAY_R56_ar_KW.pdf');
Select file_data
Into p_pdf_blob
From fnd_lobs
Where file_id = (select max(file_id) from fnd_lobs where file_name like '%PAY_R103_ar_KW.pdf');
SELECT value
INTO l_audit_log_dir
FROM v$parameter
WHERE LOWER(name) = 'utl_file_dir';