[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_data_pump_upd_user constant varchar2(30) := 'hrdpp_update_user_acct';
|| Selected employees are written to hr_pump_batch_lines table.
||
|| Post Failure:
|| Raise exception.
||
|| Access Status:
|| Public
||
||=============================================================================
*/
PROCEDURE run_process (
errbuf out nocopy varchar2
,retcode out nocopy number
,p_batch_name in hr_pump_batch_headers.batch_name%TYPE
,p_date_from in varchar2 default null
,p_date_to in varchar2 default null
,p_business_group_id in per_all_people_f.business_group_id%type
,p_single_org_id in per_organization_units.organization_id%type
default null
,p_organization_structure_id in
per_organization_structures.organization_structure_id%type
default null
,p_org_structure_version_id in
per_org_structure_versions.org_structure_version_id%type
default null
,p_parent_org_id in per_organization_units.organization_id%type
default null
,p_run_type in varchar2
)
IS
--
--
CURSOR lc_check_if_batch_name_used
IS
SELECT 'Y'
FROM hr_pump_batch_headers
WHERE upper(batch_name) = upper(p_batch_name);
SELECT name
FROM hr_all_organization_units
WHERE business_group_id = p_business_group_id
AND organization_id = p_business_group_id;
SELECT organization_id_child
FROM per_org_structure_elements
CONNECT BY organization_id_parent = prior organization_id_child
AND org_structure_version_id = prior org_structure_version_id
START WITH organization_id_parent = p_parent_org_id
AND org_structure_version_id = p_org_structure_version_id
UNION
SELECT p_parent_org_id
FROM SYS.DUAL;
SELECT person_type_id
FROM per_person_types
WHERE business_group_id = p_business_group_id
AND SYSTEM_PERSON_TYPE IN ( 'EMP','EMP_APL')
AND active_flag = 'Y';
SELECT person_type_id
FROM per_person_types
WHERE business_group_id = p_business_group_id
AND (SYSTEM_PERSON_TYPE = 'EMP'
OR
SYSTEM_PERSON_TYPE = 'EMP_APL')
AND active_flag = 'Y';
SELECT ast.assignment_status_type_id
FROM per_assignment_status_types ast
WHERE nvl(ast.business_group_id, p_business_group_id)
= p_business_group_id
AND ast.active_flag = 'Y'
AND ast.per_system_status = p_per_sys_status;
'SELECT DISTINCT ppf.person_id
,ppf.effective_start_date
,ppf.effective_end_date
,paf.assignment_id
,paf.effective_start_date
,paf.effective_end_date
,ppos.date_start hire_date
FROM per_periods_of_service ppos
,per_people_f ppf
,per_assignments_f paf
WHERE ppf.person_id = paf.person_id
and paf.primary_flag = ''Y''
AND ppf.business_group_id + 0 = ' ||
to_char(p_business_group_id);
'SELECT DISTINCT ppf.person_id
,ppf.effective_start_date
,ppf.effective_end_date
,paf.assignment_id
,paf.effective_start_date
,paf.effective_end_date
,ppos.date_start hire_date
FROM per_periods_of_service ppos
,per_people_f ppf
,per_assignments_f paf
WHERE ppf.person_id = paf.person_id
and paf.primary_flag = ''Y''
and paf.assignment_type=''E''
AND ppf.business_group_id + 0 = ' ||
to_char(p_business_group_id);
'SELECT ppf.person_id
,ppf.effective_start_date
,ppf.effective_end_date
,paf.assignment_id
,paf.effective_start_date
,paf.effective_end_date
,MAX(ppos.actual_termination_date) term_date
FROM per_periods_of_service ppos
,per_people_f ppf
,per_assignments_f paf
WHERE ppf.person_id = paf.person_id
AND paf.person_id = ppos.person_id
AND ppf.business_group_id + 0 = ' ||
to_char(p_business_group_id) ||
' AND ppf.effective_end_date BETWEEN to_date(''' ||
l_date_from_char || ''', ''' || fnd_date.canonical_mask
|| ''')'||
' AND to_date(''' ||
l_date_to_char || ''', ''' || fnd_date.canonical_mask
|| ''')' ||
' AND paf.primary_flag = ''Y''';
' (select max(actual_termination_date) from '||
' per_periods_of_service b '||
' where b.person_id=ppf.person_id '||
' and business_group_id + 0 = ' || to_char(p_business_group_id) ||
' and ppos.person_id= paf.person_id '||
' and b.actual_termination_date BETWEEN to_date(''' ||
l_date_from_char || ''', ''' || fnd_date.canonical_mask
|| ''')'||
' AND to_date(''' ||
l_date_to_char || ''', ''' || fnd_date.canonical_mask
|| ''')' ||
' ) AND ppf.person_id not in( '||
' select a.person_id from per_all_people_f a,'||
' per_periods_of_service b'||
' where a.effective_start_date= b.date_start'||
' and a.person_id=b.person_id'||
' and a.business_group_id = b.business_group_id'||
' and b.actual_termination_date IS NULL '||
' and a.person_type_id in('||
' SELECT person_type_id'||
' FROM per_person_types'||
' WHERE business_group_id = ' || to_char(p_business_group_id) ||
' AND system_person_type IN (''EMP'',''EMP_APL'' )' ||
' AND active_flag = ''Y'' ))' ;
hrdpp_create_user_acct.insert_batch_lines
(p_batch_id => l_batch_id
,p_user_sequence => null
,p_link_value => null
,p_person_user_key => l_unique_str
,p_date_from => l_date_from
,p_date_to => l_date_to
,p_org_structure_id => p_organization_structure_id
,p_org_structure_vers_id => p_org_structure_version_id
,p_parent_org_id => p_parent_org_id
,p_single_org_id => p_single_org_id
,p_run_type => p_run_type
,p_per_effective_start_date => l_effective_start_date
,p_per_effective_end_date => l_effective_end_date
,p_assignment_id => l_asg_id
,p_asg_effective_start_date => l_asg_eff_start_date
,p_asg_effective_end_date => l_asg_eff_end_date
,p_hire_date => l_hire_date);
goto update_next;
hrdpp_update_user_acct.insert_batch_lines
(p_batch_id => l_batch_id
,p_user_sequence => null
,p_link_value => null
,p_person_user_key => l_unique_str
,p_date_from => l_date_from
,p_date_to => l_date_to
,p_org_structure_id => p_organization_structure_id
,p_org_structure_vers_id => p_org_structure_version_id
,p_parent_org_id => p_parent_org_id
,p_single_org_id => p_single_org_id
,p_run_type => p_run_type
,p_per_effective_start_date => l_effective_start_date
,p_per_effective_end_date => l_effective_end_date
,p_assignment_id => l_asg_id
,p_asg_effective_start_date => l_asg_eff_start_date
,p_asg_effective_end_date => l_asg_eff_end_date
,p_inactivate_date => l_term_date);
<>
null;