[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select s.full_name, s.person_id
From per_all_people_f s, per_all_assignments_f paf
Where paf.person_id = p_id
and paf.supervisor_id = s.person_id
and paf.primary_flag = 'Y'
and s.current_employee_flag = 'Y'
and paf.assignment_type = 'E'
and trunc(sysdate) between s.effective_start_date and s.effective_end_date
and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date;
Select haotl.name organization_name,
pap.full_name manager_name,
fnd_date.canonical_to_date(cm.ORG_INFORMATION3) start_Date,
fnd_date.canonical_to_date(cm.ORG_INFORMATION4) end_Date,
decode((decode(decode(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE', HR_SECURITY.SHOW_RECORD('HR_ALL_ORGANIZATION_UNITS', HAO.ORGANIZATION_ID)
),'TRUE',0,1)+ decode(decode(hr_general.get_xbg_profile,'Y', hao.business_group_id , hr_general.get_business_group_id),hao.business_group_id,0,1)
+ decode(decode(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE', HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F', PAP.PERSON_ID, PAP.PERSON_TYPE_ID, PAP.EMPLOYEE_NUMBER,PAP.APPLICANT_NUMBER)
),'TRUE',0,1) + decode(decode(hr_general.get_xbg_profile,'Y',pap.business_group_id , hr_general.get_business_group_id),pap.business_group_id,0,1)),0,'Y','N') hasUpdateAccess
from hr_organization_information cm,per_all_people_f pap
,hr_all_organization_units hao ,hr_all_organization_units_tl haotl
where cm.org_information_context = 'Organization Name Alias'
and cm.ORG_INFORMATION2 = p_id
and cm.ORG_INFORMATION2 = to_char(pap.person_id)
and pap.current_employee_flag = 'Y'
and hao.organization_id = cm.organization_id
and pap.person_id=p_id
and trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
and trunc(sysdate) between hao.date_from and nvl(hao.date_to,trunc(sysdate))
and HAO.ORGANIZATION_ID = HAOTL.ORGANIZATION_ID
and HAOTL.LANGUAGE = USERENV('LANG')
and trunc(sysdate) between hao.date_from and nvl(hao.date_to,trunc(sysdate))
and exists (select 'e' from hr_organization_information class,
hr_org_info_types_by_class ctype
where ctype.org_information_type = 'Organization Name Alias'
and ctype.org_classification = class.org_information1
and class.org_information_context = 'CLASS'
and class.org_information2 = 'Y'
and class.organization_id = cm.organization_id)
/* Excluding pending approval */
and not exists (select 'e' from hr_api_transaction_steps s, hr_api_transactions t,
hr_api_transaction_values v
where s.api_name = 'HR_CCMGR_SS.PROCESS_API'
and s.transaction_id = t.transaction_id and status = 'Y'
and s.transaction_step_id = v.transaction_step_id
and v.name = 'P_ORGANIZATION_ID'
and v.number_value = hao.organization_id
and rownum < 2)
and (nvl(fnd_date.canonical_to_date(cm.ORG_INFORMATION4),sysdate) >= sysdate
Or (fnd_date.canonical_to_date(cm.ORG_INFORMATION4) <= sysdate
and fnd_date.canonical_to_date(cm.ORG_INFORMATION3)
= (select max(fnd_date.canonical_to_date(oi.ORG_INFORMATION3))
from hr_organization_information oi
where oi.org_information_context = 'Organization Name Alias'
and oi.organization_id = cm.organization_id)));
If I.hasUpdateAccess = 'N' Then
WF_NOTIFICATION.WriteToClob(document, I.organization_name||'
');
procedure delete_trans_steps(itemtype IN Varchar2
,itemkey IN Varchar2
,actid IN Number) IS
l_proc Varchar2(200) := g_package || 'delete_trans_steps';
Delete From hr_api_transaction_values tv
Where tv.transaction_step_id in (Select ts.transaction_step_id
From hr_api_transaction_steps ts
Where ts.item_key = itemkey
And ts.item_type = itemtype
And ts.activity_id = actid
And ts.api_name = 'HR_CCMGR_SS.PROCESS_API');
Delete From hr_api_transaction_steps
Where item_key = itemkey
And item_type = itemtype
And activity_id = actid
And api_name = 'HR_CCMGR_SS.PROCESS_API';
End delete_trans_steps;
l_mode := 'UPDATE';
hr_organization_api.update_org_manager
(p_validate => false
,p_effective_date => p_eff_date
,p_organization_id => p_ccmgr_rec.organization_id
,p_org_information_id => p_ccmgr_rec.org_information_id
,p_org_info_type_code => 'Organization Name Alias'
,p_org_information1 => p_ccmgr_rec.org_information1
,p_org_information2 => l_manager_id
,p_org_information3 => fnd_date.date_to_canonical(l_start_date)
,p_org_information4 => fnd_date.date_to_canonical(l_end_date)
,p_org_information5 => Null
,p_org_information6 => Null
,p_org_information7 => Null
,p_org_information8 => Null
,p_org_information9 => Null
,p_org_information10 => Null
,p_org_information11 => Null
,p_org_information12 => Null
,p_org_information13 => Null
,p_org_information14 => Null
,p_org_information15 => Null
,p_org_information16 => Null
,p_org_information17 => Null
,p_org_information18 => Null
,p_org_information19 => Null
,p_org_information20 => Null
,p_object_version_number => x_obj_number
,p_warning => p_warning);
Procedure update_ccmgr_recs(p_item_key IN Varchar2
,p_item_type IN Varchar2
,p_activity_id IN Number
,p_login_person_id IN OUT NOCOPY Number
,p_ccmgr_tbl IN OUT NOCOPY HR_CCMGR_TABLE
,p_mode IN Varchar2 Default '#'
,p_error_message OUT NOCOPY Long
,p_status OUT NOCOPY Varchar2) IS
l_proc Varchar2(200) := g_package || 'update_ccmgr_recs';
Select person_id into p_ccmgr_tbl(I).manager_id
From per_all_people_f
where full_name = p_ccmgr_tbl(I).manager
and current_employee_flag = 'Y'
and trunc(sysdate) between effective_start_date and effective_end_date;
Delete From hr_api_transaction_values where transaction_step_id = p_ccmgr_tbl(I).trans_step_id;
Delete From hr_api_transaction_steps where transaction_step_id = p_ccmgr_tbl(I).trans_step_id;
End update_ccmgr_recs;