[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ass.position_id,
ass.assignment_number,
ass.organization_id,
ass.people_group_id,
ass.job_id,
ass.grade_id,
ass.payroll_id,
ass.default_code_comb_id,
ass.soft_coding_keyflex_id,
nvl(amd.per_system_status, st.per_system_status),
ass.assignment_status_type_id,
ass.primary_flag,
ass.special_ceiling_step_id,
pb.pay_basis
from per_assignments_f ass,
per_assignment_status_types st,
per_ass_status_type_amends amd,
per_pay_bases pb
where ass.rowid = P_ROW_ID
and ass.assignment_status_type_id =
amd.assignment_status_type_id (+)
and ass.assignment_status_type_id =
-- amd.assignment_status_type_id (+) bug 5378516
st.assignment_status_type_id (+)
and ass.business_group_id + 0 =
amd.business_group_id (+) + 0
and ass.pay_basis_id = pb.pay_basis_id (+);
select 1
from per_people_f p
where p.person_id = P_PER_ID
and P_SESS_DATE between p.effective_start_date
and p.effective_end_date;
select 1
from per_assignment_status_types a,
per_ass_status_type_amends b
where b.assignment_status_type_id (+) =
a.assignment_status_type_id
and a.assignment_status_type_id = P_ASS_ST_TYPE_ID
and b.business_group_id (+) + 0 = P_BG_ID
and nvl(a.business_group_id, P_BG_ID) = P_BG_ID
and nvl(a.legislation_code, P_LEG_CODE) = P_LEG_CODE
and nvl(b.active_flag, a.active_flag) = 'Y'
and nvl(b.per_system_status, a.per_system_status) =
P_PER_SYS_ST;
select
l1.meaning,
l2.meaning,
bg.people_group_structure
from
hr_lookups l1, hr_lookups l2,
per_business_groups bg
where l1.lookup_type = 'YES_NO'
and l2.lookup_type = 'YES_NO'
and l1.lookup_code = 'Y'
and l2.lookup_code = 'N'
and bg.business_group_id + 0 = P_BUS_GRP_ID;
select nvl(btl.user_status,atl.user_status),
a.assignment_status_type_id
from per_assignment_status_types_tl atl,
per_assignment_status_types a,
per_ass_status_type_amends_tl btl,
per_ass_status_type_amends b
where atl.assignment_status_type_id = a.assignment_status_type_id
and atl.language = userenv('LANG')
and btl.ass_status_type_amend_id (+) = b.ass_status_type_amend_id
and btl.language (+) = userenv('LANG')
and b.assignment_status_type_id (+) = a.assignment_status_type_id
and b.business_group_id (+) + 0 = P_BUS_GRP_ID
and nvl(a.business_group_id, P_BUS_GRP_ID) = P_BUS_GRP_ID
and nvl(a.legislation_code, P_LEG_CODE) = P_LEG_CODE
and nvl(b.active_flag, a.active_flag) = 'Y'
and nvl(b.default_flag, a.default_flag) = 'Y'
and nvl(b.per_system_status, a.per_system_status)
= 'ACTIVE_ASSIGN';
select 1
from per_valid_grades vg
where vg.business_group_id = P_BUS_GRP_ID
and vg.position_id is not null;
select 1
from per_valid_grades vg
where vg.business_group_id = P_BUS_GRP_ID
and vg.job_id is not null;
select p.date_start date_start, p.period_of_service_id
from per_periods_of_service p
where p.person_id = P_PERSON_ID
and P_SESS_DATE between
p.date_start and nvl(p.final_process_date, P_END_OF_TIME)
union
select pdp.date_start date_start, to_number(null)
from per_periods_of_placement pdp
where pdp.person_id = P_PERSON_ID
and P_SESS_DATE between
pdp.date_start and nvl(pdp.final_process_date, p_end_of_time)
order by date_start desc;
select 'Y'
from fnd_product_installations
where application_id = 200
and status = 'I';
select set_of_books_id
from financials_system_parameters
where business_group_id = p_bus_grp_id;
select chart_of_accounts_id, name
from gl_sets_of_books
where set_of_books_id = p_gl_set_of_books_id;
select rule_mode
from pay_legislation_rules
where legislation_code = P_LEG_CODE
and rule_type = 'S'
and exists
(select null
from fnd_segment_attribute_values
where id_flex_num = rule_mode
and application_id = 800
and id_flex_code = 'SCL'
and segment_attribute_type = 'ASSIGNMENT'
and attribute_value = 'Y')
and exists
(select null
from pay_legislation_rules
where legislation_code = P_LEG_CODE
and rule_type = 'SDL'
and rule_mode = 'A') ;
select rule_mode
from pay_legislation_rules
where legislation_code = P_LEG_CODE
and rule_type = 'CWK_S'
and exists
(select null
from fnd_segment_attribute_values
where id_flex_num = rule_mode
and application_id = 800
and id_flex_code = 'SCL'
and segment_attribute_type = 'ASSIGNMENT'
and attribute_value = 'Y')
and exists
(select null
from pay_legislation_rules
where legislation_code = P_LEG_CODE
and rule_type = 'CWK_SDL'
and rule_mode = 'A');
select id_flex_structure_name
from fnd_id_flex_structures_vl
where id_flex_num = P_SCL_ID_FLEX_NUM
and application_id = 800
and id_flex_code = 'SCL';
select rule_mode
from pay_legislative_field_info
where legislation_code = P_LEG_CODE
and rule_type = 'TERMS'
and rule_mode = 'Y';
select 1
from per_periods_of_service pos
where pos.period_of_service_id = P_PD_OS_ID
and exists (
select null
from per_all_assignments_f a
where a.assignment_id = P_ASS_ID
and a.effective_start_date = pos.date_start);
select 1
from per_all_assignments_f a
where a.assignment_id <> P_ASS_ID
and a.person_id = P_PER_ID
and a.assignment_type = 'E'
and exists (
select null
from per_periods_of_service pos
where pos.period_of_service_id = P_PD_OS_ID
and pos.date_start = a.effective_start_date);
per_assignments_f3_pkg.update_and_delete_bundle(
null,
p_val_st_date,
p_eff_st_date,
p_eff_end_date,
p_pd_os_id,
p_per_sys_st,
p_ass_id,
p_val_end_date,
null,
p_del_mode,
p_sess_date,
p_pay_id,
p_grd_id,
p_sp_ceil_st_id,
p_ceil_seq,
l_new_end_date,
l_warning,
l_re_entry_point,
'Y',
p_pay_basis_id);--fix for bug 4764140
procedure pre_update_bundle (
p_pos_id number,
p_org_id number,
p_ass_id number,
p_row_id varchar2,
p_eff_st_date date,
p_upd_mode varchar2,
p_per_sys_st varchar2,
p_s_pos_id IN OUT NOCOPY number,
p_s_ass_num IN OUT NOCOPY varchar2,
p_s_org_id IN OUT NOCOPY number,
p_s_pg_id IN OUT NOCOPY number,
p_s_job_id IN OUT NOCOPY number,
p_s_grd_id IN OUT NOCOPY number,
p_s_pay_id IN OUT NOCOPY number,
p_s_def_code_comb_id IN OUT NOCOPY number,
p_s_soft_code_kf_id IN OUT NOCOPY number,
p_s_per_sys_st IN OUT NOCOPY varchar2,
p_s_ass_st_type_id IN OUT NOCOPY number,
p_s_prim_flag IN OUT NOCOPY varchar2,
p_s_sp_ceil_step_id IN OUT NOCOPY number,
p_s_pay_bas IN OUT NOCOPY varchar2,
p_return_warning IN OUT NOCOPY varchar2,
p_sess_date date default null) is
--
l_dummy number;
select 1
from hr_all_positions p
where p.position_id = P_POS_ID
and p.organization_id = P_ORG_ID;
select 1
from per_assignments_f a
where a.assignment_id = P_ASS_ID
and a.rowid <> P_ROW_ID
and a.effective_start_date < P_EFF_ST_DATE;
select 1
from sys.dual
where L_EOT > (select max(effective_end_date)
from per_assignments_f
where assignment_id = P_ASS_ID);
select assignment_id
,effective_start_date
,effective_end_date
,business_group_id
,recruiter_id
,grade_id
,position_id
,job_id
,assignment_status_type_id
,payroll_id
,location_id
,person_referred_by_id
,supervisor_id
,special_ceiling_step_id
,person_id
,recruitment_activity_id
,source_organization_id
,organization_id
,people_group_id
,soft_coding_keyflex_id
,vacancy_id
,pay_basis_id
,assignment_sequence
,assignment_type
,primary_flag
,application_id
,assignment_number
,change_reason
,comment_id
,null
,date_probation_end
,default_code_comb_id
,employment_category
,frequency
,internal_address_line
,manager_flag
,normal_hours
,perf_review_period
,perf_review_period_frequency
,period_of_service_id
,probation_period
,probation_unit
,sal_review_period
,sal_review_period_frequency
,set_of_books_id
,source_type
,time_normal_finish
,time_normal_start
,bargaining_unit_code
,labour_union_member_flag
,hourly_salaried_code
,request_id
,program_application_id
,program_id
,program_update_date
,ass_attribute_category
,ass_attribute1
,ass_attribute2
,ass_attribute3
,ass_attribute4
,ass_attribute5
,ass_attribute6
,ass_attribute7
,ass_attribute8
,ass_attribute9
,ass_attribute10
,ass_attribute11
,ass_attribute12
,ass_attribute13
,ass_attribute14
,ass_attribute15
,ass_attribute16
,ass_attribute17
,ass_attribute18
,ass_attribute19
,ass_attribute20
,ass_attribute21
,ass_attribute22
,ass_attribute23
,ass_attribute24
,ass_attribute25
,ass_attribute26
,ass_attribute27
,ass_attribute28
,ass_attribute29
,ass_attribute30
,title
,object_version_number
,contract_id
,establishment_id
,collective_agreement_id
,cagr_grade_def_id
,cagr_id_flex_num
,notice_period
,notice_period_uom
,employee_category
,work_at_home
,job_post_source_name
,posting_content_id
,period_of_placement_date_start
,vendor_id
,vendor_employee_number
,vendor_assignment_number
,assignment_category
,project_title
,applicant_rank
,grade_ladder_pgm_id
,supervisor_assignment_id
,vendor_site_id
,po_header_id
,po_line_id
,projected_assignment_end
from per_all_assignments_f
where assignment_id = p_asg_id
and p_eff_date between effective_start_date
and effective_end_date;
l_proc varchar2(17) := 'pre_update_bundle';
if p_upd_mode <> 'UPDATE_OVERRIDE' then
--
-- Check for "This assignment has been ended in the future...
-- Continue?".
--
open ended_assgt;
end pre_update_bundle;
if p_del_mode in ('FUTURE_CHANGE', 'DELETE_NEXT_CHANGE') then
--
-- CHECK_TERM_BY_POS, upd_mode = null
--
l_re_entry_point := 999;
per_assignments_f3_pkg.update_and_delete_bundle(
p_del_mode,
p_val_st_date,
p_eff_st_date,
p_eff_end_date,
p_pd_os_id,
p_per_sys_st,
p_ass_id,
p_val_end_date,
null,
p_del_mode,
p_sess_date,
p_pay_id,
p_grd_id,
p_sp_ceil_st_id,
p_ceil_seq,
l_new_end_date,
l_returned_warning,
l_re_entry_point,
'Y',
p_pay_basis_id);--fix for bug 4764140
per_assignments_f3_pkg.update_and_delete_bundle(
p_del_mode,
p_val_st_date,
p_eff_st_date,
p_eff_end_date,
p_pd_os_id,
p_per_sys_st,
p_ass_id,
p_val_end_date,
null,
p_del_mode,
p_sess_date,
p_pay_id,
p_grd_id,
p_sp_ceil_st_id,
p_ceil_seq,
l_new_end_date,
l_returned_warning,
l_re_entry_point,
'Y',
p_pay_basis_id);--fix for bug 4764140
procedure pre_delete(
p_del_mode varchar2,
p_val_st_date date,
p_eff_st_date date,
p_eff_end_date date,
p_pd_os_id number,
p_per_sys_st varchar2,
p_ass_id number,
p_sess_date date,
p_new_end_date IN OUT NOCOPY date,
p_val_end_date date,
p_pay_id number,
p_grd_id number,
p_sp_ceil_st_id number,
p_ceil_seq number,
p_per_id number,
p_prim_flag varchar2,
p_prim_change_flag IN OUT NOCOPY varchar2,
p_new_prim_flag IN OUT NOCOPY varchar2,
p_re_entry_point IN OUT NOCOPY number,
p_returned_warning IN OUT NOCOPY varchar2,
p_cancel_atd IN OUT NOCOPY date,
p_cancel_lspd IN OUT NOCOPY date,
p_reterm_atd IN OUT NOCOPY date,
p_reterm_lspd IN OUT NOCOPY date,
p_prim_date_from IN OUT NOCOPY date,
p_new_prim_ass_id IN OUT NOCOPY number,
p_row_id varchar2,
p_s_pos_id IN OUT NOCOPY number,
p_s_ass_num IN OUT NOCOPY varchar2,
p_s_org_id IN OUT NOCOPY number,
p_s_pg_id IN OUT NOCOPY number,
p_s_job_id IN OUT NOCOPY number,
p_s_grd_id IN OUT NOCOPY number,
p_s_pay_id IN OUT NOCOPY number,
p_s_def_code_comb_id IN OUT NOCOPY number,
p_s_soft_code_kf_id IN OUT NOCOPY number,
p_s_per_sys_st IN OUT NOCOPY varchar2,
p_s_ass_st_type_id IN OUT NOCOPY number,
p_s_prim_flag IN OUT NOCOPY varchar2,
p_s_sp_ceil_step_id IN OUT NOCOPY number,
p_s_pay_bas IN OUT NOCOPY varchar2,
p_pay_basis_id number ) is --fix for bug 4764140
--
-- Start of Fix for Bug 2820230
--
-- Declare Cursor.
cursor csr_grade_step is
select spp.placement_id, spp.object_version_number ,step_id, spp.effective_end_date
from per_spinal_point_placements_f spp
where spp.assignment_id = p_ass_id
and p_val_st_date between spp.effective_start_date
and spp.effective_end_date;
select assignment_id
,effective_start_date
,effective_end_date
,business_group_id
,recruiter_id
,grade_id
,position_id
,job_id
,assignment_status_type_id
,payroll_id
,location_id
,person_referred_by_id
,supervisor_id
,special_ceiling_step_id
,person_id
,recruitment_activity_id
,source_organization_id
,organization_id
,people_group_id
,soft_coding_keyflex_id
,vacancy_id
,pay_basis_id
,assignment_sequence
,assignment_type
,primary_flag
,application_id
,assignment_number
,change_reason
,comment_id
,null
,date_probation_end
,default_code_comb_id
,employment_category
,frequency
,internal_address_line
,manager_flag
,normal_hours
,perf_review_period
,perf_review_period_frequency
,period_of_service_id
,probation_period
,probation_unit
,sal_review_period
,sal_review_period_frequency
,set_of_books_id
,source_type
,time_normal_finish
,time_normal_start
,bargaining_unit_code
,labour_union_member_flag
,hourly_salaried_code
,request_id
,program_application_id
,program_id
,program_update_date
,ass_attribute_category
,ass_attribute1
,ass_attribute2
,ass_attribute3
,ass_attribute4
,ass_attribute5
,ass_attribute6
,ass_attribute7
,ass_attribute8
,ass_attribute9
,ass_attribute10
,ass_attribute11
,ass_attribute12
,ass_attribute13
,ass_attribute14
,ass_attribute15
,ass_attribute16
,ass_attribute17
,ass_attribute18
,ass_attribute19
,ass_attribute20
,ass_attribute21
,ass_attribute22
,ass_attribute23
,ass_attribute24
,ass_attribute25
,ass_attribute26
,ass_attribute27
,ass_attribute28
,ass_attribute29
,ass_attribute30
,title
,object_version_number
,contract_id
,establishment_id
,collective_agreement_id
,cagr_grade_def_id
,cagr_id_flex_num
,notice_period
,notice_period_uom
,employee_category
,work_at_home
,job_post_source_name
,posting_content_id
,period_of_placement_date_start
,vendor_id
,vendor_employee_number
,vendor_assignment_number
,assignment_category
,project_title
,applicant_rank
,grade_ladder_pgm_id
,supervisor_assignment_id
,vendor_site_id
,po_header_id
,po_line_id
,projected_assignment_end
from per_all_assignments_f
where assignment_id = p_asg_id
and p_eff_date between effective_start_date
and effective_end_date;
hr_utility.set_location('per_assignments_f2_pkg.pre_delete',1);
if p_del_mode in ('FUTURE_CHANGE', 'DELETE_NEXT_CHANGE') then
--
-- Call bundle if there is a warning then simply return to
-- C-S calling routine with warning and code re-entry point.
-- If warning accepted then re-enter this proc with the
-- later re-entry point and continue checking further down
-- update_and_delete_bundle's code.
--
-- N.B. This is the only point in whuch the p_new_end_date is
-- passed as an IN OUT parameter. The value may change
-- and the new value is required here
--
hr_utility.set_location('per_assignments_f2_pkg.pre_delete',2);
per_assignments_f3_pkg.update_and_delete_bundle(
p_del_mode,
p_val_st_date,
p_eff_st_date,
p_eff_end_date,
p_pd_os_id,
p_per_sys_st,
p_ass_id,
p_val_end_date,
null,
p_del_mode,
p_sess_date,
p_pay_id,
p_grd_id,
p_sp_ceil_st_id,
p_ceil_seq,
p_new_end_date,
p_returned_warning,
p_re_entry_point,
'N' ,
p_pay_basis_id);--fix for bug 4764140
hr_utility.set_location('per_assignments_f2_pkg.pre_delete',20);
hr_utility.set_location('per_assignments_f2_pkg.pre_delete',40);
hr_utility.set_location('per_assignments_f2_pkg.pre_delete',50);
hr_utility.set_location('per_assignments_f2_pkg.pre_delete',60);
hr_utility.set_location(' leaving per_assignments_f2_pkg.pre_delete',70);
end pre_delete;