[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_updated_date date;
CURSOR c_get_last_updated_date(p_pref_hierarchy_id IN number) IS
Select last_update_date
From hxc_pref_hierarchies
Where pref_hierarchy_id = p_pref_hierarchy_id;
Select employee_id from fnd_user
Where user_id = p_user_id;
SELECT hrr.pref_hierarchy_id, hrr.rule_evaluation_order
FROM hxc_resource_rules hrr,
per_all_assignments_f pa
WHERE pa.person_id = p_resource_id
AND nvl(hrr.business_group_id,pa.business_group_id) = pa.business_group_id
AND pa.primary_flag = 'Y'
and pa.assignment_type in ('E','C')
AND p_evaluation_date
BETWEEN pa.effective_start_date
AND Decode(pa.assignment_type , 'C',
Decode(cwk_final_process_date,pa.effective_END_date,pa.effective_END_date + l_num_of_days_to_add ,pa.effective_END_date),pa.effective_END_date)
AND p_evaluation_date between hrr.start_date and hrr.end_date
AND (( to_char(pa.assignment_id) = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'ASSIGNMENT')
OR ( to_char(pa.payroll_id) = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'PAYROLL')
OR ( to_char(pa.person_id) = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'PERSON')
OR ( to_char(pa.location_id) = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'LOCATION')
OR ( pa.EMPLOYEE_CATEGORY = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'EMP_CATEGORY')
OR ( pa.EMPLOYMENT_CATEGORY = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'ASGN_CATEGORY')
OR ( to_char(pa.organization_id) = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'ORGANIZATION')
OR (
hrr.eligibility_criteria_type = 'ALL_PEOPLE'));
SELECT /*+ USE_NL(PA HRR) */
hrr.pref_hierarchy_id, hrr.rule_evaluation_order
FROM hxc_resource_rules hrr,
per_all_assignments_f pa
WHERE pa.person_id = p_resource_id
AND nvl(hrr.business_group_id,pa.business_group_id) = pa.business_group_id
AND pa.primary_flag = 'Y'
and pa.assignment_type in ('E','C')
AND p_evaluation_date
BETWEEN pa.effective_start_date
AND Decode(pa.assignment_type , 'C',
Decode(cwk_final_process_date,pa.effective_END_date,pa.effective_END_date + l_num_of_days_to_add ,pa.effective_END_date),pa.effective_END_date)
AND p_evaluation_date between hrr.start_date and hrr.end_date
AND (
((HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,instr(hrr.eligibility_criteria_id,'-',1,1)+1)) in
(SELECT pose.organization_id_parent
FROM
per_org_structure_elements pose
start with organization_id_child = pa.organization_id
and pose.org_structure_version_id=
HXC_PREFERENCE_EVALUATION.return_version_id(HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,1,instr(hrr.eligibility_criteria_id,'-',1,1)-1)),
hrr.eligibility_criteria_type)
connect by prior organization_id_parent=organization_id_child
and pose.org_structure_version_id=
HXC_PREFERENCE_EVALUATION.return_version_id(HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,1,instr(hrr.eligibility_criteria_id,'-',1,1)-1)),
hrr.eligibility_criteria_type)
union
select organization_id
from hr_all_organization_units
where organization_id = pa.organization_id))
AND hrr.eligibility_criteria_type = 'ROLLUP' )
);
SELECT hrr.pref_hierarchy_id, hrr.rule_evaluation_order
FROM hxc_resource_rules hrr,
per_all_assignments_f pa
WHERE pa.person_id = p_resource_id
AND nvl(hrr.business_group_id,pa.business_group_id) = pa.business_group_id
AND pa.primary_flag = 'Y'
and pa.assignment_type in ('E','C')
AND p_evaluation_date
BETWEEN pa.effective_start_date
AND Decode(pa.assignment_type , 'C',
Decode(cwk_final_process_date,pa.effective_END_date,pa.effective_END_date + l_num_of_days_to_add ,pa.effective_END_date),pa.effective_END_date)
AND p_evaluation_date between hrr.start_date and hrr.end_date
AND ( (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 3 ),
'SCL', DECODE ( pa.soft_coding_keyflex_id, NULL, -1,
hxc_resource_rules_utils.chk_flex_valid ('SCL', pa.soft_coding_keyflex_id,
SUBSTR( hrr.eligibility_criteria_type, 5 ),
hrr.eligibility_criteria_id )), -1 ) = 1 )
OR
(DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 6 ),
'PEOPLE', DECODE ( pa.people_group_id, NULL, -1,
hxc_resource_rules_utils.chk_flex_valid ( 'PEOPLE', pa.people_group_id,
SUBSTR( hrr.eligibility_criteria_type, 8 ),
hrr.eligibility_criteria_id )), -1 ) = 1 )
OR
(DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 5 ),
'GRADE', DECODE ( pa.grade_id, NULL, -1,
hxc_resource_rules_utils.chk_flex_valid ( 'GRADE', pa.grade_id,
SUBSTR( hrr.eligibility_criteria_type, 7 ),
hrr.eligibility_criteria_id )), -1 ) = 1 )
OR
(DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 3 ),
'JOB', hrr.eligibility_criteria_id, -1 ) = to_char(pa.job_id))-- Issue 4
OR
(DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 8 ),
'POSITION', hrr.eligibility_criteria_id, -1 ) = to_char(pa.position_id)) -- Issue 4
);
SELECT hrr.pref_hierarchy_id , hrr.rule_evaluation_order
FROM hxc_resource_rules hrr
WHERE hrr.resource_type='PERSON'
AND hrr.eligibility_criteria_type = 'LOGIN'
AND hrr.eligibility_criteria_id = to_char(p_user_id) ; -- Issue 4
SELECT hrr.pref_hierarchy_id ,
hrr.rule_evaluation_order
FROM hxc_resource_rules hrr
WHERE hrr.resource_type='PERSON'
AND p_evaluation_date BETWEEN hrr.start_date
AND hrr.end_date
AND hrr.eligibility_criteria_type IN ('RESPONSIBILITY','PERST_RESPONSIBILITY')
AND hrr.eligibility_criteria_id = to_char(p_responsibility_id); -- Issue 4
SELECT hrr.pref_hierarchy_id ,
hrr.rule_evaluation_order
FROM hxc_resource_rules hrr
WHERE hrr.resource_type='PERSON'
AND p_evaluation_date BETWEEN hrr.start_date
AND hrr.end_date
AND hrr.eligibility_criteria_type = 'PERST_RESPONSIBILITY'
AND hrr.eligibility_criteria_id = to_char(p_responsibility_id);
SELECT hrr.pref_hierarchy_id , hrr.rule_evaluation_order
FROM hxc_resource_rules hrr,
per_person_types typ,
per_person_type_usages_f ptu
WHERE hrr.resource_type='PERSON'
AND p_evaluation_date between hrr.start_date and hrr.end_date
AND hrr.eligibility_criteria_type = 'PERSON_TYPE'
AND hrr.eligibility_criteria_id = to_char(ptu.person_type_id) -- Issue 4
AND ptu.person_id = p_resource_id
AND typ.system_person_type IN ('EMP','EX_EMP','EMP_APL','EX_EMP_APL','CWK','EX_CWK')
AND typ.person_type_id = ptu.person_type_id
AND p_evaluation_date between Ptu.effective_start_date
and Decode(typ.system_person_type , 'CWK',
Decode(cwk_final_process_date,Ptu.effective_end_date, Ptu.effective_end_date + l_num_of_days_to_add , Ptu.effective_end_date) ,Ptu.effective_end_date);
SELECT pref_hierarchy_id
,pref_definition_id preference_id
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,edit_allowed
,displayed
,name
,top_level_parent_id --Performance Fix
,code
FROM hxc_pref_hierarchies
WHERE top_level_parent_id = p_hierarchy_id;
SELECT
pref_definition_id, code
FROM hxc_pref_definitions;*/
Select NVL(final_process_date, hr_general.end_of_time)
from per_periods_of_placement
where person_id = p_person_id
and date_start <= p_evaluation_date
order by date_start desc;
p_pref_table.delete;
Open c_get_last_updated_date(l_hierarchy_list(l_hier).pref_hierarchy_id);
Fetch c_get_last_updated_date into l_last_updated_date;
Close c_get_last_updated_date;
if ( g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).caching_time >= l_last_updated_date) then
l_use_cache := TRUE;
-- Delete the Pref Values for this, since it has to be refreshed anyway.
g_pref_values_ct.delete(g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Start_Index,g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Stop_Index); -- table.delete(m,n)
g_pref_table.delete;
g_pref_table.delete;
l_last_updated_date date;
CURSOR c_get_last_updated_date(p_pref_hierarchy_id IN number) IS
Select last_update_date
From hxc_pref_hierarchies
Where pref_hierarchy_id = p_pref_hierarchy_id;
SELECT /*+ USE_NL(PA HRR) */
hrr.pref_hierarchy_id,
hrr.rule_evaluation_order,
hrr.start_date,
hrr.end_date,
decode(hrr.eligibility_criteria_type,
'ALL_PEOPLE', hr_general.start_of_time
, pa.effective_start_date) elig_start_date,
decode(hrr.eligibility_criteria_type,
'ALL_PEOPLE', hr_general.end_of_time
, pa.effective_END_date) elig_end_date
FROM hxc_resource_rules hrr,
per_all_assignments_f pa
WHERE pa.person_id = p_resource_id
AND nvl(hrr.business_group_id,pa.business_group_id) = pa.business_group_id
AND pa.primary_flag = 'Y'
and pa.assignment_type in ('E','C')
AND p_start_evaluation_date <= pa.effective_END_date
and pa.effective_start_date <= p_end_evaluation_date
and p_start_evaluation_date <= hrr.end_date
and hrr.start_date <= p_end_evaluation_date
and hrr.start_date <= pa.effective_end_date
and hrr.end_date >=pa.effective_start_date
AND (( to_char(pa.assignment_id) = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'ASSIGNMENT')
OR ( to_char(pa.payroll_id) = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'PAYROLL')
OR ( to_char(pa.person_id) = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'PERSON')
OR ( to_char(pa.location_id) = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'LOCATION')
OR ( pa.EMPLOYEE_CATEGORY = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'EMP_CATEGORY')
OR ( pa.EMPLOYMENT_CATEGORY = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'ASGN_CATEGORY')
OR ( to_char(pa.organization_id) = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'ORGANIZATION')
OR ((HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,instr(hrr.eligibility_criteria_id,'-',1,1)+1)) in
(SELECT pose.organization_id_parent
FROM
per_org_structure_elements pose
start with organization_id_child = pa.organization_id
and pose.org_structure_version_id=
HXC_PREFERENCE_EVALUATION.return_version_id(HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,1,instr(hrr.eligibility_criteria_id,'-',1,1)-1)),
hrr.eligibility_criteria_type)
connect by prior organization_id_parent=organization_id_child
and pose.org_structure_version_id=
HXC_PREFERENCE_EVALUATION.return_version_id(HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,1,instr(hrr.eligibility_criteria_id,'-',1,1)-1)),
hrr.eligibility_criteria_type)
union
select organization_id
from hr_all_organization_units
where organization_id = pa.organization_id))
AND hrr.eligibility_criteria_type = 'ROLLUP' )
OR (
hrr.eligibility_criteria_type = 'ALL_PEOPLE'));
SELECT /*+ USE_NL(PA HRR) */
hrr.pref_hierarchy_id,
hrr.rule_evaluation_order,
hrr.start_date,
hrr.end_date,
pa.effective_start_date elig_start_date,
pa.effective_END_date elig_end_date
FROM hxc_resource_rules hrr,
per_all_assignments_f pa
WHERE pa.person_id = p_resource_id
AND nvl(hrr.business_group_id,pa.business_group_id) = pa.business_group_id
AND pa.primary_flag = 'Y'
and pa.assignment_type in ('E','C')
AND p_start_evaluation_date <= pa.effective_END_date
and pa.effective_start_date <= p_end_evaluation_date
and p_start_evaluation_date <= hrr.end_date
and hrr.start_date <= p_end_evaluation_date
and hrr.start_date <= pa.effective_end_date
and hrr.end_date >=pa.effective_start_date
AND (( to_char(pa.assignment_id) = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'ASSIGNMENT')
OR ( to_char(pa.payroll_id) = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'PAYROLL')
OR ( to_char(pa.person_id) = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'PERSON')
OR ( to_char(pa.location_id) = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'LOCATION')
OR ( pa.EMPLOYEE_CATEGORY = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'EMP_CATEGORY')
OR ( pa.EMPLOYMENT_CATEGORY = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'ASGN_CATEGORY')
OR ( to_char(pa.organization_id) = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = 'ORGANIZATION')
OR ((HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,instr(hrr.eligibility_criteria_id,'-',1,1)+1)) in
(SELECT pose.organization_id_parent
FROM
per_org_structure_elements pose
start with organization_id_child = pa.organization_id
and pose.org_structure_version_id=
HXC_PREFERENCE_EVALUATION.return_version_id(HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,1,instr(hrr.eligibility_criteria_id,'-',1,1)-1)),
hrr.eligibility_criteria_type)
connect by prior organization_id_parent=organization_id_child
and pose.org_structure_version_id=
HXC_PREFERENCE_EVALUATION.return_version_id(HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,1,instr(hrr.eligibility_criteria_id,'-',1,1)-1)),
hrr.eligibility_criteria_type)
union
select organization_id
from hr_all_organization_units
where organization_id = pa.organization_id))
AND hrr.eligibility_criteria_type = 'ROLLUP' )
OR (
hrr.eligibility_criteria_type = 'ALL_PEOPLE'));
SELECT hrr.pref_hierarchy_id,
hrr.rule_evaluation_order,
hrr.start_date, hrr.end_date,
pa.effective_start_date elig_start_date,
pa.effective_END_date elig_end_date
FROM hxc_resource_rules hrr,
per_all_assignments_f pa
WHERE pa.person_id = p_resource_id
AND nvl(hrr.business_group_id,pa.business_group_id) = pa.business_group_id
AND pa.primary_flag = 'Y'
and pa.assignment_type in ('E','C')
AND p_start_evaluation_date <= pa.effective_END_date
and pa.effective_start_date <= p_end_evaluation_date
and p_start_evaluation_date <= hrr.end_date
and hrr.start_date <= p_end_evaluation_date
and hrr.start_date <= pa.effective_end_date
and hrr.end_date >=pa.effective_start_date
AND ( (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 3 ),
'SCL', DECODE ( pa.soft_coding_keyflex_id, NULL, -1,
hxc_resource_rules_utils.chk_flex_valid ( 'SCL', pa.soft_coding_keyflex_id,
SUBSTR( hrr.eligibility_criteria_type, 5 ), hrr.eligibility_criteria_id )), -1 ) = 1 )
OR
(DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 6 ),
'PEOPLE', DECODE ( pa.people_group_id, NULL, -1,
hxc_resource_rules_utils.chk_flex_valid ( 'PEOPLE', pa.people_group_id,
SUBSTR( hrr.eligibility_criteria_type, 8 ), hrr.eligibility_criteria_id )), -1 ) = 1 )
OR
(DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 5 ),
'GRADE', DECODE ( pa.grade_id, NULL, -1,
hxc_resource_rules_utils.chk_flex_valid ( 'GRADE', pa.grade_id,
SUBSTR( hrr.eligibility_criteria_type, 7 ), hrr.eligibility_criteria_id )), -1 ) = 1 )
OR
(DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 3 ),
'JOB', hrr.eligibility_criteria_id, -1 ) = to_char(pa.job_id)) -- Issue 4
OR
(DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 8 ),
'POSITION', hrr.eligibility_criteria_id, -1 ) = to_char(pa.position_id)) -- Issue 4
);
SELECT hrr.pref_hierarchy_id , hrr.rule_evaluation_order, hrr.start_date, hrr.end_date
FROM hxc_resource_rules hrr,
fnd_user fu
WHERE hrr.resource_type='PERSON'
AND hrr.eligibility_criteria_type = 'LOGIN'
AND hrr.eligibility_criteria_id = to_char(fu.user_id) -- Issue 4
AND fu.employee_id = p_resource_id;
SELECT hrr.pref_hierarchy_id , hrr.rule_evaluation_order, hrr.start_date, hrr.end_date,
fr.start_date elig_start_date,
fr.end_date elig_end_date
FROM hxc_resource_rules hrr,
fnd_responsibility fr
WHERE hrr.resource_type='PERSON'
and p_start_evaluation_date <= hrr.end_date
and hrr.start_date <= p_end_evaluation_date
AND hrr.eligibility_criteria_type IN ( 'RESPONSIBILITY',
'PERST_RESPONSIBILITY' )
AND hrr.eligibility_criteria_id = to_char(p_responsibility_id) -- Issue 4
and fr.responsibility_id = p_responsibility_id
and fr.application_id= p_resp_appl_id
and fr.responsibility_id = hrr.eligibility_criteria_id;
SELECT hrr.pref_hierarchy_id ,
hrr.rule_evaluation_order,
hrr.start_date,
hrr.end_date,
p_start_evaluation_date elig_start_date,
p_end_evaluation_date elig_end_date
FROM hxc_resource_rules hrr
WHERE hrr.resource_type='PERSON'
AND p_start_evaluation_date BETWEEN hrr.start_date
AND hrr.end_date
AND p_end_evaluation_date BETWEEN hrr.start_date
AND hrr.end_date
AND hrr.eligibility_criteria_type = 'PERST_RESPONSIBILITY'
AND hrr.eligibility_criteria_id = to_char(p_responsibility_id) ;
Select employee_id from fnd_user
Where user_id = p_user_id;
SELECT hrr.pref_hierarchy_id , hrr.rule_evaluation_order, hrr.start_date, hrr.end_date,
ptu.effective_start_date elig_start_date,
ptu.effective_end_date elig_end_date
FROM hxc_resource_rules hrr,
per_person_types typ,
per_person_type_usages_f ptu
WHERE hrr.resource_type='PERSON'
and p_start_evaluation_date <= hrr.end_date
and hrr.start_date <= p_end_evaluation_date
AND hrr.eligibility_criteria_type = 'PERSON_TYPE'
AND hrr.eligibility_criteria_id = to_char(ptu.person_type_id) -- Issue 4
AND ptu.person_id = p_resource_id
AND typ.system_person_type IN ('EMP','EX_EMP','EMP_APL','EX_EMP_APL','CWK','EX_CWK')
AND typ.person_type_id = ptu.person_type_id
AND p_start_evaluation_date <= ptu.effective_end_date
AND ptu.effective_start_date <= p_end_evaluation_date;
SELECT pref_hierarchy_id
,pref_definition_id preference_id
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,edit_allowed
,displayed
,name
,top_level_parent_id --Performance Fix
,code
FROM hxc_pref_hierarchies
WHERE top_level_parent_id = p_hierarchy_id;
SELECT
pref_definition_id, code
FROM hxc_pref_definitions;
p_pref_table.delete;
-- was updated by another person, not the employee
-- himself, meaning we dont have to consider persistent
-- responsibilities at all.
IF l_resptab(i).resp_id <> -1
THEN
BEGIN
-- Run the cursor for all the responsibilities and
-- timecards and keep recording the hierarchy rules.
FOR hier_rec IN c_perst_resp_hierarchies(l_resptab(i).resp_id,
TRUNC(l_resptab(i).start_date),
TRUNC(l_resptab(i).stop_date))
LOOP
l_hierarchy_list(l_hier_count).pref_hierarchy_id := hier_rec.pref_hierarchy_id;
Open c_get_last_updated_date(l_hierarchy_list(l_hier).pref_hierarchy_id);
Fetch c_get_last_updated_date into l_last_updated_date;
Close c_get_last_updated_date;
if ( g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).caching_time >= l_last_updated_date) then
l_use_cache := TRUE;
-- Delete the Pref Values for this, since it has to be refreshed anyway.
g_pref_values_ct.delete(g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Start_Index,g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Stop_Index); -- table.delete(m,n)
END LOOP; --3 update of existing dated prefrences
SELECT count(*)
FROM hxc_pref_hierarchies hph,
hxc_pref_definitions hpd
WHERE hph.pref_definition_id = hpd.pref_definition_id
AND hpd.code = p_preference_code
AND decode (p_attributen, 1, hph.attribute1, 2, hph.attribute2, 3, hph.attribute3,
4, hph.attribute4, 5, hph.attribute5, 6, hph.attribute6,
7, hph.attribute7, 8, hph.attribute8, 9, hph.attribute9,
10, hph.attribute10,11, hph.attribute11,12, hph.attribute12,
13, hph.attribute13,14, hph.attribute14,15, hph.attribute15,
16, hph.attribute16,17, hph.attribute17,18, hph.attribute18,
19, hph.attribute19,20, hph.attribute20,21, hph.attribute21,
22, hph.attribute22,23, hph.attribute23,24, hph.attribute24,
25, hph.attribute25,26, hph.attribute26,27, hph.attribute27,
28, hph.attribute28,29, hph.attribute29,30, hph.attribute30) = p_value;
g_sort_pref_table.DELETE;
l_prefs_filter.DELETE;
g_sort_pref_table.DELETE;
l_tmp_pref_table.DELETE;
g_sort_pref_table.DELETE;
SELECT 1
FROM per_all_assignments_f paaf, per_assignment_status_types past
WHERE paaf.person_id = p_person_id
AND p_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.assignment_type IN ( 'E','C')
AND past.assignment_status_type_id = paaf.assignment_status_type_id
AND past.per_system_status IN ('ACTIVE_ASSIGN','ACTIVE_CWK');
SELECT MAX (paaf.effective_end_date)
FROM per_all_assignments_f paaf, per_assignment_status_types past
WHERE paaf.person_id = p_person_id
AND paaf.effective_end_date <= p_effective_date
AND paaf.assignment_type IN ( 'E', 'C')
AND past.assignment_status_type_id = paaf.assignment_status_type_id
AND past.per_system_status IN ( 'ACTIVE_ASSIGN','ACTIVE_CWK');
l_sql_statement := ' SELECT /*+ USE_NL(PA HRR) */
pa.person_id as criteria_id,
hrr.pref_hierarchy_id,
hrr.rule_evaluation_order
FROM hxc_resource_rules hrr,
per_all_assignments_f pa
WHERE pa.person_id '||p_resource_sql||'
AND nvl(hrr.business_group_id,pa.business_group_id) = pa.business_group_id
AND pa.primary_flag =''Y''
AND pa.assignment_type in (''E'',''C'')
AND :evaluation_date
BETWEEN pa.effective_start_date AND pa.effective_END_date
AND :evaluation_date between hrr.start_date and hrr.end_date
AND (( to_char(pa.assignment_id) = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = ''ASSIGNMENT'')
OR ( to_char(pa.payroll_id) = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = ''PAYROLL'')
OR ( to_char(pa.person_id) = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = ''PERSON'')
OR ( pa.EMPLOYEE_CATEGORY = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = ''EMP_CATEGORY'')
OR ( pa.EMPLOYMENT_CATEGORY = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = ''ASGN_CATEGORY'')
OR ( to_char(pa.location_id) = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = ''LOCATION'')
OR ( to_char(pa.organization_id) = hrr.eligibility_criteria_id
AND hrr.eligibility_criteria_type = ''ORGANIZATION'')
OR ((HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,instr(hrr.eligibility_criteria_id,''-'',1,1)+1)) in
(SELECT pose.organization_id_parent
FROM
per_org_structure_elements pose
start with organization_id_child = pa.organization_id
and pose.org_structure_version_id=
HXC_PREFERENCE_EVALUATION.return_version_id(HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,1,instr(hrr.eligibility_criteria_id,''-'',1,1)-1)),
hrr.eligibility_criteria_type)
connect by prior organization_id_parent=organization_id_child
and pose.org_structure_version_id=
HXC_PREFERENCE_EVALUATION.return_version_id(HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,1,instr(hrr.eligibility_criteria_id,''-'',1,1)-1)),
hrr.eligibility_criteria_type)
union
select organization_id
from hr_all_organization_units
where organization_id = pa.organization_id))
AND hrr.eligibility_criteria_type = ''ROLLUP'' )
OR ( HXC_PREFERENCE_EVALUATION.check_number(hrr.eligibility_criteria_id)
in ( SELECT typ.person_type_id
FROM per_person_types typ
,per_person_type_usages_f ptu
WHERE typ.system_person_type IN (''EMP'',''EX_EMP'',''EMP_APL'',''EX_EMP_APL'',''CWK'',''EX_CWK'')
AND typ.person_type_id = ptu.person_type_id
AND :evaluation_date BETWEEN ptu.effective_start_date AND ptu.effective_end_date
AND pa.effective_start_date <= ptu.effective_end_date
AND pa.effective_end_date >=ptu.effective_start_date
AND ptu.person_id = pa.person_id)
AND hrr.eligibility_criteria_type = ''PERSON_TYPE'')
OR (
hrr.eligibility_criteria_type = ''ALL_PEOPLE''))'|| 'union '||
' SELECT pa.person_id as criteria_id,hrr.pref_hierarchy_id, hrr.rule_evaluation_order
FROM hxc_resource_rules hrr,
per_all_assignments_f pa
WHERE pa.person_id '||p_resource_sql||'
AND nvl(hrr.business_group_id,pa.business_group_id) = pa.business_group_id
AND pa.primary_flag = ''Y''
AND pa.assignment_type in (''E'',''C'')
AND :evaluation_date
BETWEEN pa.effective_start_date AND pa.effective_END_date
AND :evaluation_date between hrr.start_date and hrr.end_date
AND ( (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 3 ),
''SCL'', DECODE ( pa.soft_coding_keyflex_id, NULL, -1,
hxc_resource_rules_utils.chk_flex_valid (''SCL'', pa.soft_coding_keyflex_id,
SUBSTR( hrr.eligibility_criteria_type, 5 ),
hrr.eligibility_criteria_id )), -1 ) = 1 )
OR (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 6 ),
''PEOPLE'', DECODE ( pa.people_group_id, NULL, -1,
hxc_resource_rules_utils.chk_flex_valid ( ''PEOPLE'', pa.people_group_id,
SUBSTR( hrr.eligibility_criteria_type, 8 ),
hrr.eligibility_criteria_id )), -1 ) = 1 )
OR (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 5 ),
''GRADE'', DECODE ( pa.grade_id, NULL, -1,
hxc_resource_rules_utils.chk_flex_valid ( ''GRADE'', pa.grade_id,
SUBSTR( hrr.eligibility_criteria_type, 7 ),
hrr.eligibility_criteria_id )), -1 ) = 1 )
OR (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 3 ),
''JOB'', hrr.eligibility_criteria_id, -1 ) = to_char(pa.job_id))-- Issue 4
OR (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 8 ),
''POSITION'', hrr.eligibility_criteria_id, -1 ) = to_char(pa.position_id)) -- Issue 4
)
order by criteria_id';
-- Update the Out table p_resource_pref_table
p_resource_pref_table(l_current_resource_id).start_index := l_pref_sets_index_table(l_pref_sets_index_table_idx).result_start;
-- 3. Update the sets_index table
l_pref_sets_index_table_idx := l_pref_sets_index_table.last + 1;
-- 4. Update the Out table p_resource_pref_table
p_resource_pref_table(l_current_resource_id).start_index := l_result_start;
-- The current resource has been processed. So delete the l_flat_table for fresh data to be populated.
l_flat_table.delete;
select org_structure_version_id
from per_org_structure_versions
where organization_structure_id = p_number
and trunc(sysdate) between nvl(date_from,trunc(sysdate)) and
nvl(date_to,sysdate);
SELECT ta.attribute4 , ta.attribute3
from HXC_TIME_ATTRIBUTES ta, HXC_TIME_ATTRIBUTE_USAGES tau, hxc_latest_details ld, hxc_timecard_summary tbd
where ta.time_attribute_id = tau.time_attribute_id
and tau.time_building_block_id = ld.time_building_block_id
and tau.time_building_block_ovn = ld.object_version_number
and ld.resource_id = p_resource_id
and p_evaluation_date between trunc(tbd.START_TIME) and trunc(tbd.STOP_TIME)
and tbd.resource_id = p_resource_id
and trunc(ld.start_time) <= trunc(tbd.STOP_TIME)
and trunc(ld.stop_time) >= trunc(tbd.start_time)
and ta.attribute_category = 'SECURITY'
--and tbd.scope = 'TIMECARD'
order by ld.last_update_date DESC;
Select employee_id from fnd_user
Where user_id = p_user_id;
SELECT ld.time_building_block_id, ld.object_version_number
from hxc_latest_details ld
WHERE ld.resource_id = p_resource_id
and trunc(ld.start_time) <= trunc(p_end_evaluation_date)
and trunc(ld.stop_time) >= trunc(p_start_evaluation_date)
order by ld.last_update_date DESC;
SELECT ta.attribute4, ta.attribute5, ta.attribute3
FROM HXC_TIME_ATTRIBUTES ta, HXC_TIME_ATTRIBUTE_USAGES tau
WHERE ta.attribute_category = 'SECURITY'
and ta.time_attribute_id = tau.time_attribute_id
and tau.time_building_block_id = detail_building_block_id
and tau.time_building_block_ovn = detail_builiding_block_ovn;
Select employee_id from fnd_user
Where user_id = p_user_id;
select START_TIME, STOP_TIME, DATE_TO from hxc_time_building_blocks t
where scope = 'TIMECARD' AND
resource_id =p_resource_id
connect by prior parent_building_block_id = time_building_block_id
and prior parent_building_block_ovn = object_version_number
start with time_building_block_id = l_detail_building_block_id
and object_version_number = detail_builiding_block_ovn
order by time_building_block_id asc, object_version_number desc;
IS SELECT timecard_id,
timecard_ovn,
start_time,
stop_time,
date_to
FROM ( SELECT time_building_block_id timecard_id,
object_version_number timecard_ovn,
start_time,
stop_time,
date_to,
RANK() OVER ( PARTITION BY resource_id,
start_time,
stop_time
ORDER BY date_to DESC,
time_building_block_id DESC,
object_version_number DESC ) rank
FROM hxc_time_building_blocks
WHERE resource_id = p_resource_id
AND scope = 'TIMECARD'
AND TRUNC(start_time) BETWEEN p_start_evaluation_date
AND p_end_evaluation_date
AND TRUNC(stop_time) BETWEEN p_start_evaluation_date
AND p_end_evaluation_date
)
WHERE rank = 1;
IS SELECT ha.attribute4,
ha.attribute5,
ha.attribute3
FROM hxc_time_attribute_usages hau,
hxc_time_attributes ha
WHERE hau.time_building_block_id = p_timecard_id
AND hau.time_building_block_ovn = p_timecard_ovn
AND ha.time_attribute_id = hau.time_attribute_id
AND attribute_category = 'SECURITY' ;
IS SELECT /*+ LEADING(hld)
INDEX(hld hxc_lastest_details_n3) */
ha.attribute4,
ha.attribute5,
ha.attribute3
FROM hxc_time_attribute_usages hau,
hxc_time_attributes ha,
hxc_latest_details hld
WHERE hld.resource_id = p_resource_id
AND TRUNC(hld.start_time) >= p_start_date
AND TRUNC(hld.stop_time) <= TRUNC(p_stop_date)
AND hau.time_building_block_id = hld.time_building_block_id
AND hau.time_building_block_ovn = hld.object_version_number
AND hau.time_attribute_id = ha.time_attribute_id
AND attribute_category = 'SECURITY'
ORDER BY hld.last_update_date DESC ;
IS SELECT employee_id
FROM fnd_user
WHERE user_id = p_user_id;
-- If the last touched upon timecard record is deleted, there
-- is no point in looking at latest details, rather look
-- into the timecard record's SECURITY attributes.
-- If the last touched upon timecard is still live look at
-- HXC_LATEST_DETAILS table to find out who touched the
-- timecard last and get the SECURITY attribute.
-- Get the resource_id attached to the user.
-- If its the same resource_id as the parameter of this procedure
-- it means its the employee himself, and we need his
-- persistent responsibility preferences. Record the fetched
-- responsibility ids and periods into the data structure.
-- If its somebody else, it is a Time Keeper or a Line Manager
-- or an Authorized Delegate, never mind his preferences;