[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_updated_by ben_per_in_ler.last_updated_by%type;
select created_by, creation_date, group_pl_id, lf_evt_ocrd_dt
into l_created_by, l_creation_date, l_group_pl_id, l_lf_evt_ocrd_dt
from ben_per_in_ler
where per_in_ler_id = p_per_in_ler_id;
select last_updated_by, group_pl_id, lf_evt_ocrd_dt,creation_date
into l_last_updated_by, l_group_pl_id, l_lf_evt_ocrd_dt, l_creation_date
from ben_per_in_ler
where per_in_ler_id = p_per_in_ler_id;
select employee_id into l_person_id
from fnd_user
where user_id = l_last_updated_by;
procedure update_per_record
(p_per_in_ler_id in ben_per_in_ler.per_in_ler_id%type
) is
--
begin
update_per_record(p_per_in_ler_id => p_per_in_ler_id
,p_old_val => null
,p_audit_type_cd => null
);
procedure update_per_record2
(p_group_per_in_ler_id in number
) is
--
l_cwb_audit_id ben_cwb_audit.cwb_audit_id%type;
l_proc varchar2(72) := g_package||'update_per_record';
select *
into l_per_record_new
from ben_per_in_ler
where per_in_ler_id = p_group_per_in_ler_id;
select employee_id into l_person_id
from fnd_user
where user_id = l_per_record_new.last_updated_by;
select DECODE (ben_cwb_utils.get_profile ('BEN_DISPLAY_EMPLOYEE_NAME'),
'BN', empinfo.brief_name,
'CN', empinfo.custom_name,
empinfo.full_name
)
into l_old_ws_mgr_name
from ben_cwb_person_info empinfo
where empinfo.group_per_in_ler_id = p_per_record_old.ws_mgr_id;
select DECODE (ben_cwb_utils.get_profile ('BEN_DISPLAY_EMPLOYEE_NAME'),
'BN', empinfo.brief_name,
'CN', empinfo.custom_name,
empinfo.full_name
)
into l_new_ws_mgr_name
from ben_cwb_person_info empinfo
where empinfo.group_per_in_ler_id = l_per_record_new.ws_mgr_id;
end update_per_record2;
procedure update_per_record
(p_per_in_ler_id in number
,p_old_val in varchar2
,p_audit_type_cd in varchar2
) is
--
l_proc varchar2(72) := g_package||'update_per_record';
update_per_record2(p_group_per_in_ler_id => p_per_in_ler_id);
select *
into l_per_record_new
from ben_per_in_ler
where per_in_ler_id = p_per_in_ler_id;
select employee_id into l_person_id
from fnd_user
where user_id = l_per_record_new.last_updated_by;
select distinct ppf.person_id, DECODE
(ben_cwb_utils.get_profile ('BEN_DISPLAY_EMPLOYEE_NAME'),
'BN', trim(ppf.first_name ||' '||ppf.last_name||' '||ppf.suffix),
'CN', nvl(ben_cwb_custom_person_pkg.get_custom_name
(ppf.person_id
,pil.assignment_id
,bg.legislation_code
,pil.group_pl_id
,pil.lf_evt_ocrd_dt
,sysdate),
ppf.full_name),
ppf.full_name)
into l_personid, l_old_ws_mgr_name
from per_all_people_f ppf
,ben_per_in_ler pil
,per_all_assignments_f paf
,per_business_groups bg
where ppf.person_id = l_ws_mgr_id
and sysdate between ppf.effective_start_date and
ppf.effective_end_date
and paf.assignment_id = pil.assignment_id
and sysdate between paf.effective_start_date and
paf.effective_end_date
and paf.person_id = ppf.person_id
and bg.business_group_id = paf.business_group_id;
select distinct ppf.person_id, DECODE
(ben_cwb_utils.get_profile ('BEN_DISPLAY_EMPLOYEE_NAME'),
'BN', trim(ppf.first_name ||' '||ppf.last_name||' '||ppf.suffix),
'CN', nvl(ben_cwb_custom_person_pkg.get_custom_name
(ppf.person_id
,pil.assignment_id
,bg.legislation_code
,pil.group_pl_id
,pil.lf_evt_ocrd_dt
,sysdate),
ppf.full_name),
ppf.full_name)
into l_personid, l_new_ws_mgr_name
from per_all_people_f ppf
,ben_per_in_ler pil
,per_all_assignments_f paf
,per_business_groups bg
where ppf.person_id = l_per_record_new.ws_mgr_id
and sysdate between ppf.effective_start_date and
ppf.effective_end_date
and paf.assignment_id = pil.assignment_id
and sysdate between paf.effective_start_date and
paf.effective_end_date
and paf.person_id = ppf.person_id
and bg.business_group_id = paf.business_group_id;
end update_per_record;
select person_id into l_change_made_by_person_id
from fnd_user fnduser, per_all_people_f per
where p_change_made_by_person_id=fnduser.user_id (+)
and fnduser.person_party_id = per.party_id (+)
and ((nvl(p_change_made_by_person_id, -1) = -1) or
(p_date_stamp between per.effective_start_date and per.effective_end_date));
procedure update_audit_entry
(p_validate in boolean default false
,p_cwb_audit_id in number
,p_group_per_in_ler_id in number
,p_group_pl_id in number
,p_lf_evt_ocrd_dt in date
,p_pl_id in number
,p_group_oipl_id in number default hr_api.g_number
,p_audit_type_cd in varchar2
,p_old_val_varchar in varchar2 default hr_api.g_varchar2
,p_new_val_varchar in varchar2 default hr_api.g_varchar2
,p_old_val_number in number default hr_api.g_number
,p_new_val_number in number default hr_api.g_number
,p_old_val_date in date default hr_api.g_date
,p_new_val_date in date default hr_api.g_date
,p_date_stamp in date default hr_api.g_date
,p_change_made_by_person_id in number default hr_api.g_number
,p_supporting_information in varchar2 default hr_api.g_varchar2
,p_request_id in number default hr_api.g_number
,p_object_version_number in out nocopy number
) is
--
-- Declare cursors and local variables
--
l_object_version_number number;
l_proc varchar2(72) := g_package||'update_group_budget';
select person_id into l_change_made_by_person_id
from fnd_user fnduser, per_all_people_f per
where p_change_made_by_person_id=fnduser.user_id (+)
and fnduser.person_party_id = per.party_id (+)
and ((nvl(p_change_made_by_person_id, -1) = -1) or
(p_date_stamp between per.effective_start_date and per.effective_end_date));
savepoint update_audit_entry;
ben_cwb_audit_bk2.update_audit_entry_b
(p_cwb_audit_id => p_cwb_audit_id
,p_group_per_in_ler_id => p_group_per_in_ler_id
,p_group_pl_id => p_group_pl_id
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
,p_pl_id => p_pl_id
,p_group_oipl_id => p_group_oipl_id
,p_audit_type_cd => p_audit_type_cd
,p_old_val_varchar => p_old_val_varchar
,p_new_val_varchar => p_new_val_varchar
,p_old_val_number => p_old_val_number
,p_new_val_number => p_new_val_number
,p_old_val_date => p_old_val_date
,p_new_val_date => p_new_val_date
,p_date_stamp => p_date_stamp
,p_change_made_by_person_id => p_change_made_by_person_id
,p_supporting_information => p_supporting_information
,p_request_id => p_request_id
,p_object_version_number => l_object_version_number
);
(p_module_name => 'update_audit_entry'
,p_hook_type => 'BP'
);
ben_cwb_audit_bk2.update_audit_entry_a
(p_cwb_audit_id => p_cwb_audit_id
,p_group_per_in_ler_id => p_group_per_in_ler_id
,p_group_pl_id => p_group_pl_id
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
,p_pl_id => p_pl_id
,p_group_oipl_id => p_group_oipl_id
,p_audit_type_cd => p_audit_type_cd
,p_old_val_varchar => p_old_val_varchar
,p_new_val_varchar => p_new_val_varchar
,p_old_val_number => p_old_val_number
,p_new_val_number => p_new_val_number
,p_old_val_date => p_old_val_date
,p_new_val_date => p_new_val_date
,p_date_stamp => p_date_stamp
,p_change_made_by_person_id => p_change_made_by_person_id
,p_supporting_information => p_supporting_information
,p_request_id => p_request_id
,p_object_version_number => l_object_version_number
);
(p_module_name => 'update_audit_entry'
,p_hook_type => 'AP'
);
rollback to update_audit_entry;
rollback to update_audit_entry;
end update_audit_entry;
procedure delete_audit_entry
(p_validate in boolean default false
,p_cwb_audit_id in number
,p_object_version_number in out nocopy number
) is
--
-- Declare cursors and local variables
--
l_object_version_number number;
l_proc varchar2(72) := g_package||'delete_audit_entry';
savepoint delete_audit_entry;
BEN_CWB_AUDIT_BK3.delete_audit_entry_b
(p_cwb_audit_id => p_cwb_audit_id
,p_object_version_number => l_object_version_number
);
(p_module_name => 'delete_audit_entry'
,p_hook_type => 'BP'
);
ben_cwb_audit_bk3.delete_audit_entry_a
(p_cwb_audit_id => p_cwb_audit_id
,p_object_version_number => l_object_version_number
);
(p_module_name => 'delete_audit_entry'
,p_hook_type => 'AP'
);
rollback to delete_audit_entry;
rollback to delete_audit_entry;
end delete_audit_entry;
select decode(p_lookup_code,'BO',1
,'BG',1
,'EN',1
,'MG',1
,'RF',1
,'AD',3
,'AS',1
,'BS',2
,'BAD',2
,'BAA',2
,'BPA',2
,'BPD',2
,'BP',1
,'CF1',1
,'CF2',1
,'CF3',1
,'CF4',1
,'CF5',1
,'CF6',1
,'CF7',1
,'CF8',1
,'CF9',1
,'CF10',1
,'CF11',1
,'CF12',1
,'CF13',1
,'CF14',1
,'CF15',1
,'CF16',1
,'CF17',1
,'CF18',1
,'CF19',1
,'CF20',1
,'CF21',1
,'CF22',1
,'CF23',1
,'CF24',1
,'CF25',1
,'CF26',1
,'CF27',1
,'CF28',1
,'CF29',1
,'CF30',1
,'CR',1
,'CA',2
,'CU1',1
,'CU2',1
,'CU3',1
,'CU4',1
,'CU5',1
,'CU6',1
,'CU7',1
,'CU8',1
,'CU9',1
,'CU10',1
,'CU11',2
,'CU12',2
,'CU13',2
,'CU14',2
,'CU15',2
,'CU16',2
,'CU17',2
,'CU18',2
,'CU19',2
,'CU20',2
,'EL',1
,'ES',2
,'CM',1
,'ER',1
,'AC',1
,'M1',2
,'M2',2
,'M3',2
,'OC',2
,'PR',1
,'DD',3
,'AF1',1
,'AF10',1
,'AF11',1
,'AF12',1
,'AF13',1
,'AF14',1
,'AF15',1
,'AF16',1
,'AF17',1
,'AF18',1
,'AF19',1
,'AF20',1
,'AF21',1
,'AF22',1
,'AF23',1
,'AF24',1
,'AF25',1
,'AF26',1
,'AF27',1
,'AF28',1
,'AF29',1
,'AF30',1
,'AF2',1
,'AF3',1
,'AF4',1
,'AF5',1
,'AF6',1
,'AF7',1
,'AF8',1
,'AF9',1
,'GR',1
,'PG',1
,'JO',1
,'PO',1
,'SC',1
,'RA',2
,'RX',2
,'RN',2
,'RS',2
,'SS',2
,'SU',1
,'SD',3
,'TC',2
,'WX',2
,'WN',2
,1) into p_code from dual;
select * into l_lookup
from hr_lookups
where lookup_type='BEN_CWB_AUDIT_TYPE'
and lookup_code = p_lookup_code;