[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
115.26 26-Jul-00 bbulusu Selecting all contacts for g_cache_con_rec.
Fix for WW Bug #1325440. Leapfrog based
on 115.22 - for Aera Production.
115.27 26-Jul-00 jcarpent Leapfrog based on 115.25 with change from
above.
115.28 28-Aug-00 stee Select all contacts where the effective
date is >= date_start instead of
> date_start.
115.29 14-Sep-00 mhoyes - Added delete calls to all asg and applicant
assignment caches.
115.30 03-Oct-00 mhoyes - Fixed semi-colon compliance violation.
115.31 05-Oct-00 gperry Added ord_id to person type cache.
This way we can get person type from person.
115.32 07-Nov-00 kmahendr - Fixed cache delete in the procedure set_allass_objecT
g_cache_last_allass_rec.delete in place of g_cache_last_appass_rec.
delete - WWWBug#1492522
115.33 05-Jan-01 kmahendr - Added parameter per_in_ler_id
115.34 24-Jan-02 kmahendr - Bug#2179708- Added cursor C2 to get full_time equivalent
of all the assignments
115.35 16-Mar-02 kmahendr - added dbdrv lines
115.36 29-Apr-02 pabodla - Bug 1631182 : support user created
person type. Added person_type_id
parameter.
115.37 03-May-02 pabodla - In set_typ_object for person_types do not get
person_type_id from per_all_peole_f
115.38 08-Jun-02 pabodla Do not select the contingent worker
assignment when assignment data is
fetched.
115.40 10-Oct-02 tmathers bug 2620818 set_typ_object(): cursor c1
mmudigon changed order by clause from col 4 to 5
115.41 13 Feb 03 mhoyes - Fixed PGA memory growth bug 2800680.
115.42 17 Feb 03 pabodla Added debug code
115.43 20 Apr 03 pbodla - FONM : where ever l_env dates are used
use fonm dates first.
115.44 14-Sep-07 rtagarra - Bug 6399423 changed cursor c1 in set_ass_object
-----------------------------------------------------------------------------
*/
--
g_package varchar2(30) := 'ben_person_object.';
select paf.*
from per_all_assignments_f paf
where paf.person_id = p_person_id
and paf.business_group_id = p_business_group_id
and paf.primary_flag = 'Y'
and paf.assignment_type = 'E'
and p_effective_date
between paf.effective_start_date
and paf.effective_end_date;
select paf.*
from per_all_assignments_f paf
,per_assignment_status_types ast
where paf.person_id = p_person_id
and paf.business_group_id = p_business_group_id
and paf.primary_flag = 'Y'
and paf.assignment_type = 'E'
and paf.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status <> 'TERM_ASSIGN'
and p_effective_date
between paf.effective_start_date
and paf.effective_end_date;
select paf.*
from per_all_assignments_f paf
where paf.person_id = p_person_id
and paf.business_group_id = p_business_group_id
and paf.primary_flag = 'Y'
and paf.assignment_type = 'B'
and p_effective_date
between paf.effective_start_date
and paf.effective_end_date;
select bnb.*
from ben_bnfts_bal_f bnb
where bnb.bnfts_bal_id = p_bnfts_bal_id
and bnb.business_group_id = p_business_group_id
and l_effective_date
between bnb.effective_start_date
and bnb.effective_end_date;
select pas.*
from per_assignment_status_types pas
where pas.assignment_status_type_id = p_assignment_status_type_id
and pas.active_flag = 'Y';
select pei.*
from per_assignment_extra_info pei
where pei.assignment_id = p_assignment_id
and pei.information_type = 'BEN_DERIVED';
select pil.*
from ben_per_in_ler pil
where pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd = 'STRTD'
-- added per_in_ler_id for unrestricted enhancement
and pil.per_in_ler_id = nvl(p_per_in_ler_id,pil.per_in_ler_id);
select con.*
from per_contact_relationships con,
per_all_people_f ppf
where con.person_id = p_person_id
and con.business_group_id = p_business_group_id
and nvl(con.date_start,hr_api.g_sot) <= p_effective_date
and ppf.person_id = con.contact_person_id
and ppf.business_group_id = con.business_group_id
and p_effective_date
between ppf.effective_start_date
and ppf.effective_end_date;
g_cache_last_con_rec.delete;
select paf.*
from per_all_assignments_f paf
where paf.person_id = p_person_id
and paf.business_group_id = p_business_group_id
and paf.assignment_type = 'A'
and p_effective_date
between paf.effective_start_date
and paf.effective_end_date
order by paf.effective_start_date;
g_cache_last_appass_rec.delete;
select paf.*
from per_all_assignments_f paf
where paf.person_id = p_person_id
and paf.assignment_type <> 'C'
and paf.business_group_id = p_business_group_id
and p_effective_date
between paf.effective_start_date
and paf.effective_end_date
order by paf.effective_start_date;
g_cache_last_allass_rec.delete;
select per.person_id,
ppt.person_type_id,
ppt.user_person_type,
ppt.system_person_type,
decode(ppt.system_person_type,'EMP',1,2)
from per_person_type_usages_f per,
per_person_types ppt
where per.person_id = p_person_id
and p_effective_date
between per.effective_start_date
and per.effective_end_date
and per.person_type_id = ppt.person_type_id
--
-- This person_type_id is the original person type id. It is possible that
-- this person type id may not exist in per_person_type_usages_f. So this
-- data should not be used for profiles evaluation.
--
/* union
select ppf.person_id,
ppt.person_type_id,
ppt.user_person_type,
ppt.system_person_type,
decode(ppt.system_person_type,'EMP',1,2)
from per_all_people_f ppf,
per_person_types ppt
where ppf.person_id = p_person_id
and p_effective_date
between ppf.effective_start_date
and ppf.effective_end_date
and ppf.person_type_id = ppt.person_type_id */
order by 5;
g_cache_last_typ_rec.delete;
select bal.*
from ben_per_bnfts_bal_f bal
where bal.person_id = p_person_id
and bal.business_group_id = p_business_group_id;
select pad.*
from per_addresses pad
where pad.person_id = p_person_id
and pad.business_group_id = p_business_group_id
and pad.primary_flag = 'Y'
and p_effective_date
between pad.date_from
and nvl(pad.date_to,p_effective_date);
select hsc.*
from hr_soft_coding_keyflex hsc
where hsc.soft_coding_keyflex_id = p_soft_coding_keyflex_id;
select ppf.*
from per_all_people_f ppf
where ppf.person_id = p_person_id
and ppf.business_group_id = p_business_group_id
and p_effective_date
between ppf.effective_start_date
and ppf.effective_end_date;
select pps.*
from per_periods_of_service pps
where pps.person_id = p_person_id
and pps.business_group_id = p_business_group_id
and p_effective_date >= pps.date_start
order by pps.date_start desc;
select min(ppf.effective_start_date)
from per_all_people_f ppf
where ppf.person_id = p_person_id
and ppf.business_group_id = p_business_group_id;
select min(paf.effective_start_date)
from per_all_assignments_f paf
where paf.person_id = p_person_id
and paf.assignment_type <> 'C'
and paf.business_group_id = p_business_group_id
and paf.primary_flag = 'Y';
select sum(pab.value)
from per_assignments_f paf,
per_assignment_budget_values_f pab
where paf.assignment_id = p_assignment_id
and paf.business_group_id = p_business_group_id
and paf.primary_flag = nvl(p_primary_flag,paf.primary_flag)
and p_effective_date
between paf.effective_start_date
and paf.effective_end_date
and pab.business_group_id = paf.business_group_id
and pab.assignment_id = paf.assignment_id
and pab.unit = 'FTE'
and p_effective_date
between pab.effective_start_date
and pab.effective_end_date;
select sum(pab.value)
from per_assignments_f paf,
per_assignment_budget_values_f pab,
per_assignments_f paf2
where paf.assignment_id = p_assignment_id
and paf.business_group_id = p_business_group_id
and p_effective_date
between paf.effective_start_date
and paf.effective_end_date
and paf.person_id = paf2.person_id
and pab.business_group_id = paf2.business_group_id
and pab.assignment_id = paf2.assignment_id
and pab.unit = 'FTE'
and p_effective_date
between pab.effective_start_date
and pab.effective_end_date
and p_effective_date
between paf2.effective_start_date
and paf2.effective_end_date;
g_cache_per_rec.delete;
g_cache_ass_rec.delete;
g_cache_benass_rec.delete;
g_cache_app_ass_rec.delete;
g_cache_appass_rec.delete;
g_cache_allass_rec.delete;
g_cache_all_ass_rec.delete;
g_cache_ast_rec.delete;
g_cache_aei_rec.delete;
g_cache_pps_rec.delete;
g_cache_pad_rec.delete;
g_cache_pil_rec.delete;
g_cache_bal_rec.delete;
g_cache_bnb_rec.delete;
g_cache_hsc_rec.delete;
g_cache_bal_per_rec.delete;
g_cache_con_rec.delete;
g_cache_con_per_rec.delete;
g_cache_typ_rec.delete;
g_cache_typ_per_rec.delete;
g_cache_date_rec.delete;
g_cache_fte_rec.delete;
g_cache_last_con_rec.delete;
g_cache_last_appass_rec.delete;
g_cache_last_allass_rec.delete;
g_cache_last_typ_rec.delete;