[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE DELETE_DUPLICATE_ASG_RECORDS (P_COPY_ENTITY_RESULT_ID in NUMBER
,P_COPY_ENTITY_TXN_ID IN NUMBER
,P_RESULT_TYPE_CD in VARCHAR2
,P_INFORMATION2 IN VARCHAR2
,P_INFORMATION67 IN VARCHAR2)
IS
-- Cursors
CURSOR CSR_CHK_DUP_REC IS
SELECT COUNT(*)
FROM PQH_COPY_ENTITY_RESULTS
WHERE COPY_ENTITY_TXN_ID = P_COPY_ENTITY_TXN_ID
AND INFORMATION2 = P_INFORMATION2
AND COPY_ENTITY_RESULT_ID <> P_COPY_ENTITY_RESULT_ID
AND RESULT_TYPE_CD = P_RESULT_TYPE_CD;
SELECT ORG_INFORMATION9
FROM HR_ORGANIZATION_INFORMATION HOI,
PER_ALL_ASSIGNMENTS_F PAF
WHERE PAF.ASSIGNMENT_ID = P_INFORMATION2
AND FND_DATE.CANONICAL_TO_DATE(CSR_L_INFORMATION67) BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND HOI.ORGANIZATION_ID = PAF.BUSINESS_GROUP_ID
AND UPPER(HOI.ORG_INFORMATION_CONTEXT) = 'BUSINESS GROUP INFORMATION';
SELECT CONTEXT
FROM PQH_COPY_ENTITY_TXNS
WHERE COPY_ENTITY_TXN_ID = P_COPY_ENTITY_TXN_ID;
hr_utility.set_location('Leaving : pqh_fr_utility.delete_duplicate_asg_records' , 5);
if l_context = 'PQH_ASSIGNMENT_UPDATE' then
HR_UTILITY.SET_LOCATION('In pqh_assignment_update context', 20);
END DELETE_DUPLICATE_ASG_RECORDS;
SELECT id_flex_num
FROM fnd_id_flex_structures
WHERE id_flex_code = p_id_flex_code
AND id_flex_structure_code = p_structure_code;
SELECT MAX(fnd_date.canonical_to_date(pea.segment5))
FROM per_analysis_criteria pea,
per_person_analyses ppa
WHERE ppa.person_id = p_person_id
AND pea.id_flex_num = l_id_flex_num
AND pea.segment1 = p_award_type
AND pea.analysis_criteria_id = ppa.analysis_criteria_id;
SELECT MAX(fnd_date.canonical_to_date(pea.segment7))
FROM per_analysis_criteria pea,
per_person_analyses ppa
WHERE ppa.person_id = p_person_id
AND pea.id_flex_num = l_id_flex_num
AND pea.segment1 = p_award_type
AND pea.analysis_criteria_id = ppa.analysis_criteria_id;
SELECT pea.segment1
FROM per_analysis_criteria pea,
per_person_analyses ppa
WHERE ppa.person_id = p_person_id
AND pea.id_flex_num = l_id_flex_num
AND pea.analysis_criteria_id = ppa.analysis_criteria_id
AND pea.segment1 = p_award_type
AND fnd_date.canonical_to_date(pea.segment5) = l_max_date;
SELECT pea.segment1
FROM per_analysis_criteria pea,
per_person_analyses ppa
WHERE ppa.person_id = p_person_id
AND pea.id_flex_num = l_id_flex_num
AND pea.analysis_criteria_id = ppa.analysis_criteria_id
AND pea.segment1 = p_award_type
AND fnd_date.canonical_to_date(pea.segment7) = l_max_date;
SELECT MAX(fnd_date.canonical_to_date(pea.segment5))
FROM per_analysis_criteria pea,
per_person_analyses ppa
WHERE ppa.person_id = p_person_id
AND pea.id_flex_num = l_id_flex_num
AND pea.segment1 = p_award_type
AND pea.analysis_criteria_id = ppa.analysis_criteria_id;
SELECT MAX(fnd_date.canonical_to_date(pea.segment7))
FROM per_analysis_criteria pea,
per_person_analyses ppa
WHERE ppa.person_id = p_person_id
AND pea.id_flex_num = l_id_flex_num
AND pea.segment1 = p_award_type
AND pea.analysis_criteria_id = ppa.analysis_criteria_id;
SELECT pea.segment2
FROM per_analysis_criteria pea,
per_person_analyses ppa
WHERE ppa.person_id = p_person_id
AND pea.id_flex_num = l_id_flex_num
AND pea.analysis_criteria_id = ppa.analysis_criteria_id
AND pea.segment1 = p_award_type
AND fnd_date.canonical_to_date(pea.segment5) = l_max_date;
SELECT pea.segment4
FROM per_analysis_criteria pea,
per_person_analyses ppa
WHERE ppa.person_id = p_person_id
AND pea.id_flex_num = l_id_flex_num
AND pea.segment1 = p_award_type
AND pea.analysis_criteria_id = ppa.analysis_criteria_id
AND fnd_date.canonical_to_date(pea.segment7) = l_max_date;
SELECT information2
FROM per_shared_types
WHERE lookup_type = 'FR_PQH_ENTITLEMENT_SETUP'
AND system_type_cd = 'ACCOMMODATION'
AND (business_group_id = p_business_group_id OR business_group_id IS NULL);
SELECT information2
FROM per_shared_types
WHERE lookup_type = 'FR_PQH_ENTITLEMENT_SETUP'
AND system_type_cd = 'MINISTRY_AWARD'
AND (business_group_id = p_business_group_id OR business_group_id IS NULL);
SELECT information2
FROM per_shared_types
WHERE lookup_type = 'FR_PQH_ENTITLEMENT_SETUP'
AND system_type_cd = 'NATIONAL_AWARD'
AND (business_group_id = p_business_group_id OR business_group_id IS NULL);
l_update BOOLEAN;
l_update_override BOOLEAN;
l_update_change_insert BOOLEAN;
p_update => l_update,
p_update_override => l_update_override,
p_update_change_insert =>l_update_change_insert);
IF l_update_change_insert = TRUE THEN
p_datetrack_mode := 'UPDATE_CHANGE_INSERT';
ELSIF l_update = TRUE THEN
p_datetrack_mode := 'UPDATE';
SELECT 'Y'
FROM DUAL
WHERE EXISTS( Select NULL
from pqh_assign_accommodations_f
where accommodation_id = p_accommodation_id
and trunc(p_effective_date) between effective_start_date and effective_end_date
and accommodation_given ='Y');
Select shared_type_id, shared_type_name
From per_shared_types_vl
Where lookup_type = p_lookup_type
And system_type_cd = p_lookup_code
And business_group_id is null;
Select shared_type_id, shared_type_name
From per_shared_types_vl
Where lookup_type = p_lookup_type
And system_type_cd = p_lookup_code
And business_group_id = p_business_group_id;
Select max(effective_start_date)
from per_all_assignments_f
where person_id = p_person_id
and primary_flag ='Y';
Select PER_INFORMATION15
from per_all_people_f
where person_id = p_person_id
and to_date(p_effective_date,'RRRR-MM-DD') between effective_start_date and effective_end_date;
Select grade_ladder_pgm_id
from per_all_assignments_f
where person_id = p_person_id
and primary_flag ='Y'
and to_date(p_effective_date, 'RRRR-MM-DD') between effective_start_date and effective_end_date;
Select null
from per_all_people_f
where person_id = p_person_id
and to_date(p_effective_date,'RRRR-MM-DD') between effective_start_date and effective_end_date;
Select normal_hours
from per_all_assignments_f
where to_date(p_effective_date,'RRRR-MM-DD') between effective_start_date and effective_end_date
and person_id = p_person_id
and primary_flag ='Y';
Select sum(normal_hours)
from per_all_assignments_f
where person_id = p_person_id
and p_effective_date between effective_start_date and effective_end_date
and assignment_status_type_id = 1
and primary_flag = p_flag;
SELECT information1
FROM per_shared_types
WHERE lookup_type = 'FR_PQH_PHYSICAL_SHARE'
AND business_group_id = hr_general.get_business_group_id
AND system_type_cd = p_shard_type_cd;
SELECT information1
FROM per_shared_types
WHERE lookup_type = 'FR_PQH_PHYSICAL_SHARE'
AND business_group_id IS NULL
AND system_type_cd = p_shard_type_cd;
Select Reference
from per_contracts_f
where contract_id = p_contract_id
and p_effective_date between effective_start_date and effective_end_date;
SELECT assignment_id, object_version_number
FROM per_all_assignments_f
WHERE person_id = p_person_id
AND hr_general.effective_date between effective_start_date and effective_end_date
AND primary_flag = 'Y';
SELECT working_hours, frequency
FROM per_business_groups
WHERE business_group_id = p_bg_id;
Select statutory_situation_id
from pqh_fr_stat_situations_v sit , per_shared_types_vl sh
where sh.shared_type_id = type_of_ps
and sh.system_type_cd = nvl(pqh_fr_utility.GET_BG_TYPE_OF_PS,sh.system_type_cd)
and sit.business_group_id = hr_general.get_business_group_id
and sit.default_flag = 'Y'
and sit.situation_type = 'IA'
and sit.sub_type = 'IA_N'
and trunc(sysdate) between date_from and nvl(date_to,hr_general.end_of_time);
Select per.per_information15, pps.orig_hire_dt
from per_all_people_f per,
(SELECT min(PPS1.DATE_START) orig_hire_dt
FROM PER_PERIODS_OF_SERVICE PPS1
WHERE pps1.person_id = p_person_id) pps
where per.person_id =p_person_id
and trunc(sysdate) between per.effective_start_date and per.effective_end_date;
Select per_information15,original_date_of_hire
from per_all_people_f
where person_id =p_person_id
and trunc(sysdate) between effective_start_date and effective_end_date;
HR_ASSIGNMENT_API.update_emp_asg(
p_effective_date => hr_general.effective_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => l_asg_id
,p_object_version_number => l_asg_ovn
,p_normal_hours => fnd_number.canonical_to_number(l_bg_hours.working_hours)
,p_frequency => l_bg_hours.frequency
,p_segment9 => '100'
,p_segment2 => p_emp_type
,p_soft_coding_keyflex_id => l_scl_id
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_grade_segments
,p_concatenated_segments => l_conc_segments
,p_comment_id => l_comment_id
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_no_managers_warning => l_no_mgrs
,p_other_manager_warning => l_other_mgrs
,p_hourly_salaried_warning => l_hourly
,p_gsp_post_process_warning => l_gsp_warn);
Select position_id
from per_all_assignments_f
where assignment_id = p_admin_career_id
and p_effective_date between effective_start_date and effective_end_date;
SELECT psp.information1 "SCALE_TYPE",
ssp.information1 "GROSS_INDEX",
ssp.information2 "SALARY_RATE"
FROM per_spinal_point_steps_f sps,
per_parent_spines psp,
per_spinal_points ssp
WHERE sps.step_id = p_step_id
AND p_effective_date BETWEEN sps.effective_start_date AND sps.effective_end_date
AND sps.spinal_point_id = ssp.spinal_point_id
AND psp.parent_spine_id = ssp.parent_spine_id;
SELECT basic_salary_rate,
currency_code
FROM pqh_fr_global_indices_f
WHERE type_of_record = 'INM'
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT asg.person_id,
asg.grade_ladder_pgm_id "GRADE_LADDER_PGM_ID",
scl.segment9 "PHYSICAL_SHARE",
pqh_fr_utility.Get_Salary_Share(scl.segment9) "SALARY_SHARE",
scl.segment8 "EMP_STAT_SITUATION_ID",
sps.step_id "STEP_ID",
sps.information4 "PGI"
FROM per_all_assignments_f asg,
hr_soft_coding_keyflex scl,
per_spinal_point_placements_f sps
WHERE asg.assignment_id = p_assignment_id
AND p_effective_date BETWEEN asg.effective_start_date and asg.effective_end_date
AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND asg.assignment_id = sps.assignment_id
AND p_effective_date BETWEEN sps.effective_start_date AND sps.effective_end_date;
SELECT per_information15
FROM per_all_people_F
WHERE person_id = p_person_id
AND p_effective_date BETWEEN effective_start_date and effective_end_date;
SELECT sts.situation_type,
sts.sub_type,
sts.remuneration_paid,
sts.pay_share
FROM pqh_fr_stat_situations sts,
pqh_fr_emp_stat_situations ess
WHERE ess.emp_stat_situation_id = p_emp_stat_situation_id
AND ess.statutory_situation_id = sts.statutory_situation_id;
SELECT pgm_uom
FROM ben_pgm_f
WHERE pgm_id = p_gl_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
Select System_type_cd
from per_shared_types_vl sh , hr_organization_information O
where O.org_information_context = 'FR_PQH_GROUPING_UNIT_INFO'
and O.organization_id = hr_general.get_business_group_id
and sh.shared_type_id = o.org_information1;
SELECT nvl(scl.segment23,'-999') Identifier,
scl.segment24 Type,
scl.segment27 seg27,
scl.segment26 seg26,
assign.position_id Position,
scl.segment25 PercentAffected,
assign.normal_hours WorkingHours,
assign.frequency Frequency,
nvl(assign.supervisor_id,-999) Supervisor,
assign.effective_start_date,
assign.assignment_status_type_id
from per_all_assignments_f assign, hr_soft_coding_keyflex scl
WHERE assign.person_id = p_assignment_id
AND assign.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND assign.primary_flag ='N'
AND assign.assignment_status_type_id <> 3
and (assign.effective_start_date = p_start_date
or l_start_date between assign.effective_start_date and assign.effective_end_date)
ORDER by assign.effective_start_date;
SELECT asg.effective_start_date effective_start_date,
nvl(asg.establishment_id,-999) establishment_id,
nvl(asg.employment_category, '-999') category,
nvl(asg.normal_hours,-999) normal_hours,
nvl(asg.frequency,'-999') frequency,
nvl(scl.segment19,'-999') reason,
nvl(scl.segment9,'-999') share_part
FROM per_all_assignments_f asg,
hr_soft_coding_keyflex scl,
per_shared_types pst
WHERE asg.assignment_id = p_assignment_id
AND pst.shared_type_id(+) = scl.segment9
AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
and (effective_start_date = p_start_date
or l_start_date between effective_start_date and effective_end_date)
ORDER by effective_start_date;
SELECT asg.effective_start_date effective_start_date
,scl.segment10 employment_category
,NVL(asg.grade_ladder_pgm_id,-1) grade_ladder_pgm_id
,NVL(asg.grade_id,-1) grade_id
FROM per_all_assignments_f asg
,hr_soft_coding_keyflex scl
WHERE asg.assignment_id = p_assignment_id
AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
AND(asg.effective_start_date = p_start_date
OR l_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date)
ORDER BY asg.effective_start_date;
SELECT status, duration, duration_units,
extension_period, extension_period_units, number_of_extensions
FROM per_contracts_f
WHERE contract_id = NVL(p_contract_id,-1)
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT 'Y'
FROM per_all_assignments_f asg,
hr_soft_coding_keyflex scl,
per_assignment_status_types ast
WHERE scl.segment26 = to_char(p_primary_assign_id) --changed for bug 7211180
AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND ast.per_system_status = 'ACTIVE_ASSIGN'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND asg.primary_flag = 'N'
AND p_effective_date BETWEEN asg.effective_start_date AND NVL(asg.effective_end_date,HR_GENERAL.end_of_time)
AND asg.position_id NOT IN(SELECT position_id
FROM hr_all_positions_f
WHERE information_category = 'FR_PQH'
AND information10 =(SELECT TO_CHAR(corps_definition_id)
FROM pqh_corps_definitions
WHERE ben_pgm_id = TO_NUMBER(p_new_ben_pgm_id)
AND p_effective_date BETWEEN date_from
AND NVL(date_to,HR_GENERAL.end_of_time))
AND p_effective_date BETWEEN effective_start_date
AND NVL(effective_end_date,HR_GENERAL.end_of_time));
SELECT NVL(normal_hours,0), NVL(normal_hours_frequency,'X')
FROM pqh_corps_definitions
WHERE ben_pgm_id = p_ben_pgm_id
AND p_effective_date BETWEEN date_from AND NVL(date_to,HR_GENERAL.end_of_time);
SELECT FND_NUMBER.canonical_to_number(org_information4) hours, 'M' frequency
FROM hr_organization_information_v
WHERE org_information_context = 'FR_ESTAB_INFO'
AND organization_id = p_estab_id;
SELECT nvl(scl.segment23,'-999') Identifier,
scl.segment24 Type,
nvl(scl.segment27,'-999') seg27,
nvl(scl.segment26,'-999') seg26,
nvl(assign.position_id,-999) Position,
nvl(scl.segment25,-999) PercentAffected,
nvl(assign.normal_hours,-999) WorkingHours,
nvl(assign.frequency, '-999') Frequency,
nvl(assign.supervisor_id,-999) Supervisor,
assign.effective_start_date,
assign.assignment_status_type_id,
assign.effective_end_date
from per_all_assignments_f assign, hr_soft_coding_keyflex scl
WHERE assign.person_id = p_person_id
AND assign.assignment_id = l_assignment_id
AND assign.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND assign.primary_flag ='N'
AND assign.assignment_status_type_id <> 3
and (assign.effective_start_date = p_start_date
or effective_start_date > l_end_date)
ORDER by assign.effective_start_date;
SELECT asg.effective_end_date effective_end_date
FROM per_all_assignments_f asg
WHERE asg.assignment_id = l_assignment_id
and effective_start_date = p_start_date;
SELECT asg.effective_start_date effective_start_date,
nvl(asg.establishment_id,-999) establishment_id,
nvl(asg.employment_category, '-999') category,
nvl(asg.normal_hours,-999) normal_hours,
nvl(asg.frequency,'-999') frequency,
nvl(scl.segment19,'-999') reason,
nvl(scl.segment9,'-999') share_part,
asg.effective_end_date effective_end_date
FROM per_all_assignments_f asg,
hr_soft_coding_keyflex scl,
per_shared_types pst
WHERE asg.assignment_id = p_assignment_id
AND pst.shared_type_id(+) = scl.segment9
AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
and (effective_start_date = p_start_date
or effective_start_date > l_end_date )
ORDER by effective_start_date;
SELECT asg.effective_start_date effective_start_date
,scl.segment10 employment_category
,NVL(asg.grade_ladder_pgm_id,-1) grade_ladder_pgm_id
,NVL(asg.grade_id,-1) grade_id
,asg.effective_end_date effective_end_date
FROM per_all_assignments_f asg
,hr_soft_coding_keyflex scl
WHERE asg.assignment_id = p_assignment_id
AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
AND(asg.effective_start_date = p_start_date
OR asg.effective_start_date > l_end_date)
ORDER BY asg.effective_start_date;
SELECT information10 corps_id
FROM hr_positions_f
WHERE p_effective_date between effective_start_date and effective_end_date
AND position_id = p_pos_id;
Select Corps_Definition_Id
From Pqh_Corps_Definitions
where Ben_Pgm_Id in (select grade_ladder_pgm_id
from per_all_assignments_f
where assignment_id = p_primary_assign_id
and p_effective_date between effective_start_date and effective_end_date
and primary_flag = 'Y' );
SELECT information1
FROM per_shared_types
WHERE lookup_type = 'FR_PQH_ORG_CATEGORY'
AND shared_type_id =(SELECT org_information1
FROM hr_organization_information
WHERE org_information_context = 'FR_PQH_GROUPING_UNIT_INFO'
AND organization_id =(SELECT business_group_id
FROM per_all_people_f
WHERE person_id = p_person_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date));
SELECT shared_type_id, shared_type_name
FROM per_shared_types_vl
WHERE lookup_type = 'FR_PQH_ORG_CATEGORY'
AND system_type_cd = p_lookup_code
AND business_group_id IS NULL;
SELECT shared_type_id, shared_type_name
FROM per_shared_types_vl
WHERE lookup_type = 'FR_PQH_ORG_CATEGORY'
AND system_type_cd = p_lookup_code
AND business_group_id = HR_GENERAL.get_business_group_id;
SELECT t.description
FROM fnd_currencies a
,fnd_currencies_tl t
WHERE a.currency_code = p_currency_code
AND t.currency_code = a.currency_code
AND t.language = USERENV('LANG');
SELECT hru.name
FROM hr_all_organization_units hru
,hr_organization_information hri
WHERE hru.organization_id = p_org_id
AND hru.organization_id = hri.organization_id
AND hri.org_information_context = 'CLASS'
AND hri.org_information1 IN ('FR_ETABLISSEMENT','FR_SOCIETE','HR_BG')
AND p_effective_date BETWEEN hru.date_from AND NVL(hru.date_to,p_effective_date);
SELECT pettl.element_name
FROM pay_element_classifications pec
,pay_element_types_f pet
,pay_element_types_f_tl pettl
WHERE(pet.business_group_id = p_business_group_id OR
(pet.business_group_id IS NULL AND pet.legislation_code = 'FR'))
AND pet.element_type_id = p_payment_code
AND pet.element_type_id = pettl.element_type_id
AND pettl.language = USERENV('LANG')
AND pec.classification_id = pet.classification_id;