[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nfp.process_method_code pm_code
,nfp.navigable_flag
FROM ghr_noa_fam_proc_methods nfp
,ghr_pa_data_fields pdf
WHERE pdf.pa_data_field_id = nfp.pa_data_field_id
AND pdf.form_block_name = p_form_block_name
AND pdf.form_field_name = p_form_field_name
AND nfp.noa_family_code = p_noa_family_code
AND nfp.enabled_flag = 'Y'
AND NVL(p_effective_date,TRUNC(sysdate))
BETWEEN NVL(nfp.start_date_active,NVL(p_effective_date,TRUNC(sysdate)))
AND NVL(nfp.end_date_active,NVL(p_effective_date,TRUNC(sysdate)));
SELECT pdf.name
FROM ghr_pa_data_fields pdf
WHERE pdf.form_block_name = p_form_block_name
AND pdf.form_field_name = p_form_field_name;
SELECT rpm.restricted_proc_method
FROM ghr_pa_data_fields pdf
,ghr_restricted_proc_methods rpm
WHERE pdf.pa_data_field_id = rpm.pa_data_field_id
AND rpm.restricted_form = p_restricted_form
AND pdf.form_block_name = p_form_block_name
AND pdf.form_field_name = p_form_field_name
AND rpm.enabled_flag = 'Y'
AND TRUNC(sysdate)
BETWEEN NVL(rpm.start_date_active,TRUNC(sysdate))
AND NVL(rpm.end_date_active,TRUNC(sysdate));
SELECT loc.meaning
FROM hr_lookups loc
WHERE loc.lookup_type = p_lookup_type
AND loc.lookup_code = p_lookup_code;
SELECT loc.description
FROM hr_lookups loc
WHERE loc.lookup_type = p_lookup_type
AND loc.lookup_code = p_lookup_code;
SELECT fam.name
FROM ghr_families fam
WHERE fam.noa_family_code = p_noa_family_code;
SELECT rgr.name
FROM ghr_routing_groups rgr
WHERE rgr.routing_group_id = p_routing_group_id;
SELECT per.last_name||','|| per.first_name||' '|| per.middle_names full_name
FROM per_people_f per
WHERE per.person_id = p_person_id
AND NVL(p_effective_date,TRUNC(sysdate)) between per.effective_start_date and per.effective_end_date;
SELECT per.last_name||','|| per.first_name||' '|| per.middle_names full_name
FROM per_all_people_f per
WHERE per.person_id = p_person_id
AND NVL(p_effective_date,TRUNC(sysdate)) BETWEEN per.effective_start_date AND per.effective_end_date;
SELECT noa.description
FROM ghr_nature_of_actions noa
WHERE noa.nature_of_action_id= p_nature_of_action_id;
SELECT rem.description
FROM ghr_remarks rem
WHERE rem.remark_id = p_remark_id;
SELECT pei.pei_information3 routing_group_id
,pei.pei_information4 initiator_flag
,pei.pei_information5 requester_flag
,pei.pei_information6 authorizer_flag
,pei.pei_information7 personnelist_flag
,pei.pei_information8 approver_flag
,pei.pei_information9 reviewer_flag
FROM per_people_extra_info pei
,fnd_user use
WHERE use.user_name = p_user_name
AND pei.person_id = use.employee_id
AND pei.information_type = 'GHR_US_PER_WF_ROUTING_GROUPS'
AND pei.pei_information10 = 'Y';
SELECT rli.routing_list_id
,rli.name
,prh.routing_seq_number
,prh.pa_routing_history_id
FROM ghr_routing_lists rli
,ghr_pa_routing_history prh
WHERE prh.pa_request_id = p_pa_request_id
AND prh.routing_list_id = rli.routing_list_id
ORDER BY prh.pa_routing_history_id DESC;
SELECT prh.pa_routing_history_id
FROM ghr_pa_routing_history prh
WHERE prh.pa_request_id = p_pa_request_id
ORDER BY prh.pa_routing_history_id DESC;
SELECT rlm.seq_number
,rlm.user_name
,rlm.groupbox_id
FROM ghr_routing_list_members rlm
WHERE rlm.routing_list_id = p_routing_list_id
AND rlm.seq_number > p_seq_number
ORDER BY rlm.seq_number asc;
select prh.groupbox_id
,prh.user_name
from ghr_pa_routing_history prh
where prh.pa_request_id = p_pa_request_id
order by prh.pa_routing_history_id desc;
select prh.groupbox_id
from ghr_pa_routing_history prh
where prh.pa_request_id = p_pa_request_id
and prh.user_name = l_user_name
and prh.groupbox_id is not NULL
and not exists (select 1
from ghr_pa_routing_history prh2
where prh2.pa_request_id = p_pa_request_id
and prh2.user_name <> l_user_name
and prh2.pa_routing_history_id > prh.pa_routing_history_id)
order by prh.pa_routing_history_id asc;
select pei.pei_information4 initiator_flag
,pei.pei_information5 requester_flag
,pei.pei_information6 authorizer_flag
,pei.pei_information7 personnelist_flag
,pei.pei_information8 approver_flag
,pei.pei_information9 reviewer_flag
from per_people_extra_info pei
,fnd_user usr
where usr.user_name = l_user_name
and pei.person_id = usr.employee_id
and pei.information_type = 'GHR_US_PER_WF_ROUTING_GROUPS'
and pei.pei_information3 = p_routing_group_id;
select gru.initiator_flag
,gru.requester_flag
,gru.authorizer_flag
,gru.personnelist_flag
,gru.approver_flag
,gru.reviewer_flag
from ghr_groupbox_users gru
where gru.groupbox_id = l_groupbox_id
and gru.user_name = p_user_name;
SELECT per.national_identifier
,per.date_of_birth
,per.last_name
,per.first_name
,per.middle_names
FROM per_people_f per
WHERE per.person_id = p_person_id
AND NVL(p_effective_date,TRUNC(sysdate)) between per.effective_start_date and per.effective_end_date;
SELECT dstv.duty_station_code
,dstv.duty_station_desc
FROM ghr_duty_stations_v dstv
WHERE dstv.duty_station_id = p_duty_station_id
AND NVL(p_effective_date,TRUNC(sysdate)) between dstv.effective_start_date and dstv.effective_end_date;
SELECT lei.lei_information3 duty_station_id
FROM hr_location_extra_info lei
WHERE lei.location_id = p_location_id
AND lei.information_type = 'GHR_US_LOC_INFORMATION';
SELECT adr.address_line1
,adr.address_line2
,adr.address_line3
,adr.town_or_city
,adr.region_2
,adr.postal_code
,adr.country
,ter.territory_short_name
FROM fnd_territories_vl ter
,per_addresses adr
WHERE adr.person_id = p_person_id
AND adr.primary_flag = 'Y'
AND NVL(p_effective_date, TRUNC(sysdate))
BETWEEN adr.date_from AND NVL(adr.date_to,NVL(p_effective_date,TRUNC(sysdate)))
AND adr.country = ter.territory_code;
SELECT assignment_id, person_id
FROM per_all_assignments_f
WHERE position_id = p_position_id
AND assignment_type <> 'B'
AND primary_flag = 'Y'
AND p_effective_date
between effective_start_date and effective_end_date;
SELECT pos.job_id
,pos.business_group_id
,pos.organization_id
,pos.location_id
FROM hr_all_positions_f pos -- Venkat -- Position DT
WHERE pos.position_id = p_position_id
and p_effective_date between pos.effective_start_date
and pos.effective_end_date ;
SELECT gdf.segment1 pay_plan
,gdf.segment2 grade_or_level
FROM per_grade_definitions gdf
,per_grades grd
WHERE grd.grade_id = p_grade_id
AND grd.grade_definition_id = gdf.grade_definition_id;
SELECT oi.org_information5 position_org_line1
,oi.org_information6 position_org_line2
,oi.org_information7 position_org_line3
,oi.org_information8 position_org_line4
,oi.org_information9 position_org_line5
,oi.org_information10 position_org_line6
FROM hr_organization_information oi
WHERE oi.organization_id = p_org_id
AND oi.org_information_context = 'GHR_US_ORG_REPORTING_INFO';
SELECT rei.rei_information8 org_id -- Bug 2681726 Changed information9 to 8 as we need to consider position's org
FROM ghr_pa_request_extra_info rei
WHERE pa_request_id = p_pa_request_id
AND rei.information_type = 'GHR_US_PAR_REALIGNMENT';
SELECT oi.org_information5 position_org_line1
,oi.org_information6 position_org_line2
,oi.org_information7 position_org_line3
,oi.org_information8 position_org_line4
,oi.org_information9 position_org_line5
,oi.org_information10 position_org_line6
FROM hr_organization_information oi
WHERE oi.organization_id = p_org_id
AND oi.org_information_context = 'GHR_US_ORG_REPORTING_INFO';
SELECT 1
FROM fnd_descr_flex_contexts dfc
WHERE dfc.application_id = 8301
AND dfc.descriptive_flexfield_name = p_flexfield_name
AND dfc.descriptive_flex_context_code = p_context_code
AND dfc.enabled_flag = 'Y'; --to avoid insertion prompts for diabled contexts 5766626
SELECT 1
FROM ghr_noac_remarks ncr
WHERE (ncr.nature_of_action_id = p_first_noa_id
OR ncr.nature_of_action_id = p_second_noa_id)
AND ncr.remark_id = p_remark_id
AND ncr.required_flag = 'Y'
AND NVL(p_effective_date,TRUNC(sysdate))
BETWEEN ncr.date_from AND NVL(ncr.date_to,NVL(p_effective_date,TRUNC(sysdate)));
SELECT use.employee_id
FROM fnd_user use
WHERE use.user_name = p_user_name;
SELECT noa.nature_of_action_id
,noa.code
,noa.description
FROM ghr_nature_of_actions noa
,ghr_noa_families naf
WHERE naf.noa_family_code = p_noa_family_code
AND naf.nature_of_action_id = noa.nature_of_action_id
AND naf.enabled_flag = 'Y'
AND NVL(p_effective_date,trunc(sysdate))
BETWEEN NVL(naf.start_date_active,NVL(p_effective_date,trunc(sysdate)))
AND NVL(naf.end_date_active,NVL(p_effective_date,trunc(sysdate))) ;
SELECT hrl.lookup_code
,hrl.description
FROM hr_lookups hrl
,ghr_noac_las nla
WHERE nla.nature_of_action_id = p_nature_of_action_id
AND nla.enabled_flag = 'Y'
AND nla.valid_first_lac_flag = 'Y'
AND NVL(p_effective_date,trunc(sysdate))
BETWEEN nla.date_from
AND NVL(nla.date_to,NVL(p_effective_date,trunc(sysdate)))
AND hrl.lookup_code = nla.lac_lookup_code
AND hrl.lookup_type = 'GHR_US_LEGAL_AUTHORITY'
AND hrl.enabled_flag = 'Y'
AND NVL(p_effective_date,trunc(sysdate))
BETWEEN NVL(hrl.start_date_active,NVL(p_effective_date,trunc(sysdate)))
AND NVL(hrl.end_date_active,NVL(p_effective_date,trunc(sysdate)));
SELECT pei.pei_information3 restricted_form
FROM per_people_extra_info pei
WHERE pei.information_type = 'GHR_US_PER_USER_INFO'
AND pei.person_id = p_person_id;
SELECT naf.noa_family_code
FROM ghr_families fam
,ghr_noa_families naf
WHERE fam.noa_family_code = naf.noa_family_code
AND naf.nature_of_action_id = p_nature_of_action_id
AND fam.proc_method_flag = 'Y';
SELECT naf.noa_family_code
FROM ghr_families fam
,ghr_noa_families naf
WHERE fam.noa_family_code = naf.noa_family_code
AND naf.nature_of_action_id = p_nature_of_action_id
AND fam.proc_method_flag = 'Y'
AND p_effective_date between NVL(naf.start_date_active,p_effective_date)
and NVL(naf.end_date_active,p_effective_date);
SELECT naf.noa_family_code
FROM ghr_families fam
,ghr_noa_families naf
,ghr_nature_of_actions noa
WHERE fam.noa_family_code = naf.noa_family_code
AND naf.nature_of_action_id = noa.nature_of_action_id
AND noa.code = p_noa_code
AND fam.proc_method_flag = 'Y';
SELECT 1
FROM per_all_assignments_f asg
WHERE asg.position_id = p_position_id
AND NVL(p_effective_date,TRUNC(sysdate))
BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.assignment_type NOT IN ('A', 'B');
SELECT cop.printer_name
FROM fnd_concurrent_programs cop
WHERE cop.application_id = 8301
AND cop.concurrent_program_name = p_concurrent_program_name;
SELECT 1
FROM ghr_pa_requests par
WHERE par.pa_request_id = p_pa_request_id
AND par.pa_notification_id IS NOT NULL;
SELECT 1
FROM per_person_types pet
,per_people_f per
WHERE nvl(p_effective_date,trunc(sysdate)) between per.effective_start_date and per.effective_end_date
AND pet.person_type_id = per.person_type_id
AND pet.system_person_type in ('APL','EX_EMP','EX_EMP_APL')
AND per.business_group_id = p_business_group_id
AND per.person_id <> p_user_person_id
AND per.person_id = p_person_id;
SELECT 1
FROM per_person_types pet
,per_people_f per
WHERE nvl(p_effective_date,trunc(sysdate)) between per.effective_start_date and per.effective_end_date
AND pet.person_type_id = per.person_type_id
AND (pet.system_person_type in ('EMP', 'EMP_APL')
OR (pet.system_person_type = 'EX_EMP' and p_noa_family_code = 'CONV_APP')
)
AND per.business_group_id = p_business_group_id
AND per.person_id <> p_user_person_id
AND per.person_id = p_person_id;
SELECT 1
FROM per_person_types pet
,per_people_f per
WHERE nvl(p_effective_date,trunc(sysdate)) between per.effective_start_date and per.effective_end_date
AND pet.person_type_id = per.person_type_id
AND pet.system_person_type in ('APL','EX_EMP')
AND per.business_group_id = p_business_group_id
AND per.person_id <> p_user_person_id
AND per.person_id = p_person_id;
SELECT 1
FROM per_person_types pet
,per_people_f per
WHERE nvl(p_effective_date,trunc(sysdate)) between per.effective_start_date and per.effective_end_date
AND pet.person_type_id = per.person_type_id
AND (pet.system_person_type in ('EMP', 'EMP_APL')
OR (pet.system_person_type = 'EX_EMP' and p_noa_family_code = 'CONV_APP')
)
AND per.business_group_id = p_business_group_id
AND per.person_id <> p_user_person_id
AND per.person_id = p_person_id;
SELECT asg.position_id
FROM Per_assignments_f asg
WHERE asg.person_id = p_person_id
AND trunc(nvl(p_effective_date,sysdate))
between asg.effective_start_date and asg.effective_end_date
AND asg.assignment_type <> 'B'
AND asg.primary_flag = 'Y';
SELECT per.first_name || decode(per.middle_names,null,null, ' ' ||substr(per.middle_names,1,1) || '.') || ' ' ||per.last_name full_name
FROM per_people_f per
WHERE per.person_id = p_person_id
AND trunc(nvl(p_effective_date,sysdate))between per.effective_start_date and per.effective_end_date;
l_update34_date DATE;
l_update34_date := ghr_pay_caps.update34_implemented_date(p_person_id);
hr_utility.set_location('l_update34_date ' || l_update34_date,1);
If l_update34_date is null then
hr_utility.set_location('update 34 is null',1);
elsif p_effective_date >= l_update34_date then
hr_utility.set_location('update 34 isnot null and effective_date is ',1);
PROCEDURE update34_implement_cancel (p_person_id IN NUMBER
,p_assignment_id IN NUMBER
,p_date IN DATE
,p_altered_pa_request_id in NUMBER)
IS
l_effective_date DATE;
l_update34_date DATE;
select par.effective_date effective_date,
par.pa_request_id
from ghr_pa_requests par
where par.person_id = p_person_id
and par.effective_date >= p_date
and par.pa_notification_id is not null
and par.first_noa_code <> '001'
and par.pa_request_id <> nvl(p_altered_pa_request_id,par.pa_request_id)
and nvl(par.first_noa_cancel_or_correct,hr_api.g_varchar2) <> 'CANCEL'
and ((par.second_noa_code is null)
or (par.second_noa_code is not null
and nvl(par.second_noa_cancel_or_correct,hr_api.g_varchar2) <> 'CANCEL'))
order by par.effective_date ,par.pa_request_id ;
SELECT par.effective_date effective_date
,par.altered_pa_request_id
FROM ghr_pa_routing_history prh
,ghr_pa_requests par
WHERE par.person_id = p_person_id
AND par.effective_date >= p_date
AND prh.pa_request_id = par.pa_request_id
AND prh.pa_routing_history_id = (SELECT MAX(prh2.pa_routing_history_id)
FROM ghr_pa_routing_history prh2
WHERE prh2.pa_request_id = par.pa_request_id)
AND prh.action_taken IN ('FUTURE_ACTION','UPDATE_HR_COMPLETE')
AND par.NOA_FAMILY_CODE != 'CANCEL'
AND ( ( par.second_noa_code IS NULL
AND NVL(par.first_noa_cancel_or_correct,'X') != 'CANCEL'
)
OR ( par.second_noa_code IS NOT NULL
AND par.NOA_FAMILY_CODE != 'CORRECT'
AND ( NVL(par.first_noa_cancel_or_correct,'X') != 'CANCEL'
OR NVL(par.second_noa_cancel_or_correct,'X') != 'CANCEL'
)
)
OR ( par.second_noa_code IS NOT NULL
AND par.NOA_FAMILY_CODE = 'CORRECT'
AND NVL(par.second_noa_cancel_or_correct,'X') != 'CANCEL'
)
)
ORDER BY par.effective_date, par.pa_request_id;
l_update34_date := ghr_pay_caps.update34_implemented_date(p_person_id);
If p_date = nvl(l_update34_date,hr_api.g_date) then
If not l_exists then
hr_utility.set_location('Not exists',1);
,p_information_type => 'GHR_US_PER_UPDATE34'
,p_date_effective => p_date
,p_per_ei_data => l_per_ei_data);
delete per_people_extra_info
where person_extra_info_id = l_person_extra_info_id;
delete from ghr_pa_history
where table_name = 'PER_PEOPLE_EXTRA_INFO'
and information1 = l_person_extra_info_id;
delete from ghr_pa_history
where table_name = 'PER_PEOPLE_EXTRA_INFO'
and to_number(information4) = p_person_id
and information5 = 'GHR_US_PER_UPDATE34'
and pa_request_id = l_altered_pa_request_id ;
ghr_person_extra_info_api.update_person_extra_info
(P_PERSON_EXTRA_INFO_ID => l_person_extra_info_id
,P_EFFECTIVE_DATE => l_effective_date
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_PEI_ATTRIBUTE_CATEGORY => 'GHR_US_PER_UPDATE34'
,p_pei_INFORMATION3 => fnd_date.date_to_canonical(l_effective_date)
,P_PEI_INFORMATION_CATEGORY => 'GHR_US_PER_UPDATE34'
);
END update34_implement_cancel;
SELECT first_noa_code,second_noa_code
FROM ghr_pa_requests
WHERE pa_request_id = p_pa_request_id;
SELECT rei_information3 temp_step
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_id
AND information_type = 'GHR_US_PAR_RG_TEMP_PROMO';
SELECT grd.name grade_name
FROM per_grades grd
WHERE grd.grade_id = TO_NUMBER(l_grade_id);
SELECT location_id
FROM hr_all_positions_f
WHERE position_id = p_position_id
and p_effective_date between
effective_start_date and effective_end_date;
select ds.duty_station_code
FROM hr_location_extra_info lei, ghr_duty_stations_v ds
WHERE lei.location_id = l_location_id
AND lei.information_type = 'GHR_US_LOC_INFORMATION'
and to_number(lei.lei_information3) = ds.duty_station_id
and p_effective_date between ds.effective_start_date
and ds.effective_end_date;
SELECT pos.job_id
FROM hr_all_positions_f pos
WHERE pos.position_id = p_position_id
AND p_effective_date BETWEEN pos.effective_start_date and pos.effective_end_date;
SELECT org.name from hr_positions_f pos, hr_organization_units org
WHERE pos.organization_id = org.organization_id
AND pos.business_group_id = p_business_group_id
AND p_effective_date between pos.EFFECTIVE_START_DATE and pos.EFFECTIVE_END_DATE
AND pos.position_id =p_position_id;
select system_type_cd
from per_shared_types_vl
where shared_type_id = p_availability_status_id;