[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_salary_proposal(p_assignment_id number
, p_effective_date date) IS
l_pay_proposal_id per_pay_proposals.pay_proposal_id%TYPE;
select pay_proposal_id, object_version_number, change_date
,PROPOSED_SALARY_N
from per_pay_proposals
where assignment_id = p_assignment_id
order by change_date DESC;
END update_salary_proposal;
select name
from hr_organization_units
where organization_id = p_id;
select name
from per_jobs_vl
where job_id = p_id;
select name
from hr_positions_f
where position_id = p_id
and to_date(p_effective_date,g_date_format)
between effective_start_date
and effective_end_date;
select name
from per_grades_vl
where grade_id = p_id;
select vendor_name
from po_vendors
where vendor_id = p_id;
SELECT poh.segment1 po_number
FROM
po_headers_all poh
WHERE
poh.po_header_id = p_id
AND poh.type_lookup_code = 'STANDARD'
AND poh.authorization_status IN ('APPROVED', 'PRE-APPROVED')
AND poh.approved_flag = 'Y'
AND poh.enabled_flag = 'Y'
AND NVL(poh.cancel_flag, 'N') <> 'Y'
AND NVL(poh.frozen_flag, 'N') <> 'Y'
AND poh.org_id IS NOT NULL
AND EXISTS
(
SELECT
NULL
FROM po_lines_all pol ,
po_line_types_b polt
WHERE pol.po_header_id = poh.po_header_id
AND NVL(pol.cancel_flag, 'N') <> 'Y'
AND pol.line_type_id = polt.line_type_id
AND polt.purchase_basis = 'TEMP LABOR'
);
select line_number
from po_temp_labor_lines_v
where po_line_id = p_id;
select vendor_site_code
from po_vendor_sites_all
where vendor_site_id = p_id;
l_datetrack_update_mode varchar2(30);
ln_gsp_update_mode varchar2(10);
CURSOR lc_sal_updateable_grade_ladder
(p_grade_ladder_id in per_all_assignments_f.grade_ladder_pgm_id%TYPE ,
p_effective_date in date
) IS
select pgm_id, update_salary_cd from ben_pgm_f
where
-- grade ladder does not allow update of salary
--(update_salary_cd is null or update_salary_cd = 'NO_UPDATE')
-- salary updated by the progression system should not be manually overidden
--and (gsp_allow_override_flag is null or gsp_allow_override_flag = 'N')
--and
pgm_id = p_grade_ladder_id
and p_effective_date between effective_start_date and effective_end_date;
select object_version_number
from per_all_people_f
where person_id = p_appl_person_id
and p_appl_effective_date between effective_start_date
and effective_end_date;
select object_version_number,
assignment_status_type_id
from per_all_assignments_f
where assignment_id = p_appl_assign_id
and p_appl_effective_date between effective_start_date
and effective_end_date;
select max(asf.effective_start_date)
from per_all_assignments_f asf,
per_assignment_status_types past
where asf.assignment_id=l_assignment_id
and asf.ASSIGNMENT_STATUS_TYPE_ID = past.ASSIGNMENT_STATUS_TYPE_ID;
select null
from per_all_assignments_f asf,
per_assignment_status_types past
where asf.assignment_id = l_assignment_id
and asf.assignment_status_type_id = past.assignment_status_type_id
and asf.effective_start_date = l_effective_date;
select name
from hr_organization_units
where organization_id = p_id;
select name
from per_jobs_vl
where job_id = p_id;
select name
from hr_positions_f
where position_id = p_id
and to_date(p_effective_date,g_date_format)
between effective_start_date
and effective_end_date;
select name
from per_grades_vl
where grade_id = p_id;
select assignment_id
from per_all_assignments_f
where assignment_id = p_assignment_id;
select processing_order from hr_api_transaction_steps where
item_type=l_item_type and item_key=l_item_key and api_name='HR_SUPERVISOR_SS.PROCESS_API';
select transaction_step_id,activity_id from hr_api_transaction_steps where
item_type=l_item_type and item_key=l_item_key and api_name='HR_PAY_RATE_SS.PROCESS_API';
select null from hr_api_transaction_steps where
item_type=l_item_type and item_key=l_item_key and api_name='HR_PROCESS_ASSIGNMENT_STEP_SS.PROCESS_API';
select * from pay_people_groups
where people_group_id = p_people_group_id;
select * from hr_soft_coding_keyflex
where soft_coding_keyflex_id = p_soft_coding_keyflex_id;
select transaction_step_id from hr_api_transaction_steps
where item_type=l_item_type and item_key=l_item_key
and api_name='HR_PROCESS_PERSON_SS.PROCESS_API';
select *
from per_all_assignments_f
where person_id = l_person_id and primary_flag = 'Y' and assignment_type = 'E'
and l_effective_date between effective_start_date and effective_end_date;
select object_version_number into l_object_version_number from per_all_assignments_f where assignment_id = l_assignment_id;
l_datetrack_update_mode:='UPDATE';
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode:='CORRECTION';
l_datetrack_update_mode:='UPDATE_CHANGE_INSERT';
/*l_datetrack_update_mode:=
hr_transaction_api.get_varchar2_value(
p_transaction_step_id => l_transaction_step_id,
p_name => 'P_DATETRACK_UPDATE_MODE'
);*/
l_datetrack_update_mode:='CORRECTION';
hr_new_user_reg_ss.process_selected_transaction(p_item_type => l_item_type,
p_item_key => l_item_key
,p_api_name => 'HR_PROCESS_PERSON_SS.PROCESS_API');
hr_new_user_reg_ss.process_selected_transaction(p_item_type => l_item_type,
p_item_key => l_item_key
,p_api_name => 'HR_PROCESS_ADDRESS_SS.PROCESS_API');
l_datetrack_update_mode := 'CORRECTION';
open lc_sal_updateable_grade_ladder(p_grade_ladder_id =>
l_assignment_rec.grade_ladder_pgm_id,
p_effective_date => l_effective_date
);
fetch lc_sal_updateable_grade_ladder into lc_temp_grade_ladder_id, lc_temp_upd_sal_cd;
if (lc_sal_updateable_grade_ladder%FOUND) THEN
-- initializing local salary data table type to pass to store into txn
ltt_salary_data := sshr_sal_prop_tab_typ(sshr_sal_prop_obj_typ(
null,-- pay_proposal_id NUMBER,
l_assignment_id,-- assignment_id NUMBER,
l_assignment_rec.business_group_id,--business_group_id NUMBER,
l_effective_date,--effective_date DATE,
null,--comments VARCHAR2(2000),
null,--next_sal_review_date DATE,
null,--salary_change_amount NUMBER ,
null,--salary_change_percent NUMBER ,
null,--annual_change NUMBER ,
null,--proposed_salary NUMBER ,
null,--proposed_percent NUMBER ,
null,--proposal_reason VARCHAR2(30),
null,--ranking NUMBER,
null,--current_salary NUMBER,
null,--performance_review_id NUMBER,
null,--multiple_components VARCHAR2(1),
null,--element_entry_id NUMBER ,
null,--selection_mode VARCHAR2(1),
null,--ovn NUMBER,
null,--currency VARCHAR2(15),
null,--pay_basis_name VARCHAR2(80),
null,--annual_equivalent NUMBER ,
null,--total_percent NUMBER ,
null,--quartile NUMBER ,
null,--comparatio NUMBER ,
null,--lv_selection_mode VARCHAR2(1),
null,--attribute_category VARCHAR2(150),
null,--attribute1 VARCHAR2(150),
null,--attribute2 VARCHAR2(150),
null,--attribute3 VARCHAR2(150),
null,--attribute4 VARCHAR2(150),
null,--attribute5 VARCHAR2(150),
null,--attribute6 VARCHAR2(150),
null,--attribute7 VARCHAR2(150),
null,--attribute8 VARCHAR2(150),
null,--attribute9 VARCHAR2(150),
null,--attribute10 VARCHAR2(150),
null,--attribute11 VARCHAR2(150),
null,--attribute12 VARCHAR2(150),
null,--attribute13 VARCHAR2(150),
null,--attribute14 VARCHAR2(150),
null,--attribute15 VARCHAR2(150),
null,--attribute16 VARCHAR2(150),
null,--attribute17 VARCHAR2(150),
null,--attribute18 VARCHAR2(150),
null,--attribute19 VARCHAR2(150),
null,--attribute20 VARCHAR2(150),
null, --no_of_components NUMBER,
-- 04/12/02 Salary Basis Enhancement Begins
null, -- salary_basis_change_type varchar2(30)
null, -- default_date date
null, -- default_bg_id number
null, -- default_currency VARCHAR2(15)
null, -- default_format_string VARCHAR2(40)
null, -- default_salary_basis_name varchar2(30)
null, -- default_pay_basis_name varchar2(80)
null, -- default_pay_basis varchar2(30)
null, -- default_pay_annual_factor number
null, -- default_grade VARCHAR2(240)
null, -- default_grade_annual_factor number
null, -- default_minimum_salary number
null, -- default_maximum_salary number
null, -- default_midpoint_salary number
null, -- default_prev_salary number
null, -- default_last_change_date date
null, -- default_element_entry_id number
null, -- default_basis_changed number
null, -- default_uom VARCHAR2(30)
null, -- default_grade_uom VARCHAR2(30)
null, -- default_change_amount number
null, -- default_change_percent number
null, -- default_quartile number
null, -- default_comparatio number
null, -- default_last_pay_change varchar2(200)
null, -- default_flsa_status varchar2(80)
null, -- default_currency_symbol varchar2(4)
null, -- default_precision number
-- 04/12/02 Salary Basis Enhancement Ends
-- GSP
null, -- salary_effective_date date
null, -- gsp_dummy_txn varchar2(30)
-- End of GSP
null,
null,
null,
null,
null
));
close lc_sal_updateable_grade_ladder;
update_assignment
(p_validate => true
,p_login_person_id => p_login_person_id
,p_new_hire_appl_hire => l_new_hire_appl_hire
,p_assignment_id => l_assignment_id
,p_object_version_number => l_object_version_number
,p_effective_date => l_effective_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_organization_id => l_assignment_rec.organization_id
,p_position_id => l_assignment_rec.position_id
,p_job_id => l_assignment_rec.job_id
,p_grade_id => l_assignment_rec.grade_id
,p_location_id => l_assignment_rec.location_id
,p_employment_category => l_assignment_rec.employment_category
,p_supervisor_id => l_assignment_rec.supervisor_id
,p_manager_flag => l_assignment_rec.manager_flag
,p_frequency => l_assignment_rec.frequency
,p_normal_hours => l_assignment_rec.normal_hours
,p_time_normal_finish => l_assignment_rec.time_normal_finish
,p_time_normal_start => l_assignment_rec.time_normal_start
,p_bargaining_unit_code => l_assignment_rec.bargaining_unit_code
,p_labour_union_member_flag => l_assignment_rec.labour_union_member_flag
,p_assignment_status_type_id=> l_assignment_rec.assignment_status_type_id
,p_change_reason => l_assignment_rec.change_reason
,p_special_ceiling_step_id => l_assignment_rec.special_ceiling_step_id
,p_ass_attribute_category => l_assignment_rec.ass_attribute_category
,p_ass_attribute1 => l_assignment_rec.ass_attribute1
,p_ass_attribute2 => l_assignment_rec.ass_attribute2
,p_ass_attribute3 => l_assignment_rec.ass_attribute3
,p_ass_attribute4 => l_assignment_rec.ass_attribute4
,p_ass_attribute5 => l_assignment_rec.ass_attribute5
,p_ass_attribute6 => l_assignment_rec.ass_attribute6
,p_ass_attribute7 => l_assignment_rec.ass_attribute7
,p_ass_attribute8 => l_assignment_rec.ass_attribute8
,p_ass_attribute9 => l_assignment_rec.ass_attribute9
,p_ass_attribute10 => l_assignment_rec.ass_attribute10
,p_ass_attribute11 => l_assignment_rec.ass_attribute11
,p_ass_attribute12 => l_assignment_rec.ass_attribute12
,p_ass_attribute13 => l_assignment_rec.ass_attribute13
,p_ass_attribute14 => l_assignment_rec.ass_attribute14
,p_ass_attribute15 => l_assignment_rec.ass_attribute15
,p_ass_attribute16 => l_assignment_rec.ass_attribute16
,p_ass_attribute17 => l_assignment_rec.ass_attribute17
,p_ass_attribute18 => l_assignment_rec.ass_attribute18
,p_ass_attribute19 => l_assignment_rec.ass_attribute19
,p_ass_attribute20 => l_assignment_rec.ass_attribute20
,p_ass_attribute21 => l_assignment_rec.ass_attribute21
,p_ass_attribute22 => l_assignment_rec.ass_attribute22
,p_ass_attribute23 => l_assignment_rec.ass_attribute23
,p_ass_attribute24 => l_assignment_rec.ass_attribute24
,p_ass_attribute25 => l_assignment_rec.ass_attribute25
,p_ass_attribute26 => l_assignment_rec.ass_attribute26
,p_ass_attribute27 => l_assignment_rec.ass_attribute27
,p_ass_attribute28 => l_assignment_rec.ass_attribute28
,p_ass_attribute29 => l_assignment_rec.ass_attribute29
,p_ass_attribute30 => l_assignment_rec.ass_attribute30
,p_soft_coding_keyflex_id => l_assignment_rec.soft_coding_keyflex_id
,p_people_group_id => l_assignment_rec.people_group_id
,p_contract_id => l_assignment_rec.contract_id
,p_establishment_id => l_assignment_rec.establishment_id
,p_cagr_grade_def_id => l_assignment_rec.cagr_grade_def_id
,p_collective_agreement_id => l_assignment_rec.collective_agreement_id
,p_cagr_id_flex_num => l_assignment_rec.cagr_id_flex_num
,p_payroll_id => l_assignment_rec.payroll_id
,p_pay_basis_id => l_assignment_rec.pay_basis_id
,p_sal_review_period => l_assignment_rec.sal_review_period
,p_sal_review_period_frequency => l_assignment_rec.sal_review_period_frequency
,p_date_probation_end => l_assignment_rec.date_probation_end
,p_probation_period => l_assignment_rec.probation_period
,p_probation_unit => l_assignment_rec.probation_unit
,p_notice_period => l_assignment_rec.notice_period
,p_notice_period_uom => l_assignment_rec.notice_period_uom
,p_employee_category => l_assignment_rec.employee_category
,p_work_at_home => l_assignment_rec.work_at_home
,p_job_post_source_name => l_assignment_rec.job_post_source_name
,p_perf_review_period => l_assignment_rec.perf_review_period
,p_perf_review_period_frequency => l_assignment_rec.perf_review_period_frequency
,p_internal_address_line => l_assignment_rec.internal_address_line
,p_business_group_id => l_assignment_rec.business_group_id
-- GSP change
,p_grade_ladder_pgm_id => l_assignment_rec.grade_ladder_pgm_id
-- End of GSP change
,p_assignment_type => l_assignment_rec.assignment_type
--,p_supervisor_assignment_id => l_assignment_rec.supervisor_assignment_id
,p_vacancy_id => l_assignment_rec.vacancy_id
,p_primary_flag => l_assignment_rec.primary_flag
,p_person_id => l_assignment_rec.person_id
,p_default_code_comb_id => l_assignment_rec.default_code_comb_id
,p_project_title => l_assignment_rec.project_title
,p_set_of_books_id => l_assignment_rec.set_of_books_id
,p_source_type => l_assignment_rec.source_type
,p_title => l_assignment_rec.title
,p_vendor_assignment_number => l_assignment_rec.vendor_assignment_number
,p_vendor_employee_number => l_assignment_rec.vendor_employee_number
,p_vendor_id => l_assignment_rec.vendor_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_element_warning => l_element_warning
,p_element_changed => l_element_changed
,p_email_id => l_email_id
,p_page_error => p_page_error
,p_page_error_msg => p_page_error_msg
,p_page_warning => p_page_warning
,p_page_warning_msg => p_page_warning_msg
,p_organization_error => p_organization_error
,p_organization_error_msg => p_organization_error_msg
,p_job_error => p_job_error
,p_job_error_msg => p_job_error_msg
,p_position_error => p_position_error
,p_position_error_msg => p_position_error_msg
,p_grade_error => p_grade_error
,p_grade_error_msg => p_grade_error_msg
,p_supervisor_error => p_supervisor_error
,p_supervisor_error_msg => p_supervisor_error_msg
,p_location_error => p_location_error
,p_location_error_msg => p_location_error_msg
-- GSP change
,p_ltt_salary_data => ltt_salary_data
,p_gsp_post_process_warning => p_gsp_post_process_warning
-- End of GSP change
,p_po_header_id => p_po_header_id
,p_po_line_id => p_po_line_id
,p_vendor_site_id => p_vendor_site_id
,p_projected_asgn_end => p_proj_asgn_end
,p_j_changed => j_changed
);
update_apl_assignment(
p_assignment_rec => l_assignment_rec
,p_validate => true
,p_effective_date => l_effective_date
,p_person_id => l_assignment_rec.person_id
,p_appl_assignment_id => l_assignment_id
,p_person_type_id => l_person_type_id
,p_overwrite_primary => l_asgn_change_mode
,p_ovn => l_per_object_version_number);
l_datetrack_update_mode := 'CORRECTION';
l_trans_tbl(l_count).param_name := 'P_DATETRACK_UPDATE_MODE';
l_trans_tbl(l_count).param_value := l_datetrack_update_mode;
l_trans_tbl(l_count).param_original_value := l_datetrack_update_mode; --ns
hr_transaction_ss.delete_transaction_step
(p_transaction_step_id => l_pay_step_id
,p_login_person_id => p_login_person_id);
open lc_sal_updateable_grade_ladder(p_grade_ladder_id =>
l_assignment_rec.grade_ladder_pgm_id,
p_effective_date => l_effective_date
);
fetch lc_sal_updateable_grade_ladder into lc_temp_grade_ladder_id, lc_temp_upd_sal_cd;
if ((lc_sal_updateable_grade_ladder%FOUND) and
( (ltt_salary_data(1).salary_change_amount < 0)
or (ltt_salary_data(1).salary_change_amount > 0))) THEN
lv_gsp_review_proc_call := 'HrPayRate';
p_update_mode => ln_gsp_update_mode,
p_effective_date_option => p_effective_date_option);
hr_transaction_ss.delete_transaction_step
(p_transaction_step_id => ln_gsp_step_id
,p_login_person_id => p_login_person_id);
delete from per_pay_transactions where TRANSACTION_STEP_ID=ln_gsp_step_id;
close lc_sal_updateable_grade_ladder;
hr_transaction_ss.delete_transaction_step
(p_transaction_step_id => l_transaction_step_id
,p_login_person_id => p_login_person_id);
hr_transaction_ss.delete_transaction_step
(p_transaction_step_id => ln_gsp_step_id
,p_login_person_id => p_login_person_id);
delete from per_pay_transactions where TRANSACTION_STEP_ID=ln_gsp_step_id;
procedure update_object_version
(p_transaction_step_id in number
,p_login_person_id in number) is
cursor csr_new_object_number(p_asg_id in number) is
select object_version_number
from per_all_assignments_f
where assignment_id = p_asg_id
and assignment_type = 'E'
order by object_version_number desc;
l_proc varchar2(72) := g_package||'update_object_version';
end update_object_version;
l_datetrack_update_mode varchar2(30);
l_spp_delete_warning boolean;
select object_version_number,
assignment_type,
assignment_status_type_id
from per_all_assignments_f
where assignment_id = p_appl_assign_id
and p_appl_effective_date between effective_start_date
and effective_end_date;
SELECT item_type,item_key
FROM hr_api_transaction_steps
WHERE transaction_step_id = c_txnStepId;
select transaction_step_id from hr_api_transaction_steps
where item_type=l_item_type and item_key=l_item_key
and api_name='HR_PROCESS_PERSON_SS.PROCESS_API';
select object_version_number
from per_all_people_f
where person_id = p_appl_person_id
and p_appl_effective_date between effective_start_date
and effective_end_date;
l_datetrack_update_mode:=
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_DATETRACK_UPDATE_MODE');
l_datetrack_update_mode := 'CORRECTION';
null,--selection_mode VARCHAR2(1),
null,--ovn NUMBER,
null,--currency VARCHAR2(15),
null,--pay_basis_name VARCHAR2(80),
null,--annual_equivalent NUMBER ,
null,--total_percent NUMBER ,
null,--quartile NUMBER ,
null,--comparatio NUMBER ,
null,--lv_selection_mode VARCHAR2(1),
null,--attribute_category VARCHAR2(150),
null,--attribute1 VARCHAR2(150),
null,--attribute2 VARCHAR2(150),
null,--attribute3 VARCHAR2(150),
null,--attribute4 VARCHAR2(150),
null,--attribute5 VARCHAR2(150),
null,--attribute6 VARCHAR2(150),
null,--attribute7 VARCHAR2(150),
null,--attribute8 VARCHAR2(150),
null,--attribute9 VARCHAR2(150),
null,--attribute10 VARCHAR2(150),
null,--attribute11 VARCHAR2(150),
null,--attribute12 VARCHAR2(150),
null,--attribute13 VARCHAR2(150),
null,--attribute14 VARCHAR2(150),
null,--attribute15 VARCHAR2(150),
null,--attribute16 VARCHAR2(150),
null,--attribute17 VARCHAR2(150),
null,--attribute18 VARCHAR2(150),
null,--attribute19 VARCHAR2(150),
null,--attribute20 VARCHAR2(150),
null, --no_of_components NUMBER,
-- 04/12/02 Salary Basis Enhancement Begins
null, -- salary_basis_change_type varchar2(30)
null, -- default_date date
null, -- default_bg_id number
null, -- default_currency VARCHAR2(15)
null, -- default_format_string VARCHAR2(40)
null, -- default_salary_basis_name varchar2(30)
null, -- default_pay_basis_name varchar2(80)
null, -- default_pay_basis varchar2(30)
null, -- default_pay_annual_factor number
null, -- default_grade VARCHAR2(240)
null, -- default_grade_annual_factor number
null, -- default_minimum_salary number
null, -- default_maximum_salary number
null, -- default_midpoint_salary number
null, -- default_prev_salary number
null, -- default_last_change_date date
null, -- default_element_entry_id number
null, -- default_basis_changed number
null, -- default_uom VARCHAR2(30)
null, -- default_grade_uom VARCHAR2(30)
null, -- default_change_amount number
null, -- default_change_percent number
null, -- default_quartile number
null, -- default_comparatio number
null, -- default_last_pay_change varchar2(200)
null, -- default_flsa_status varchar2(80)
null, -- default_currency_symbol varchar2(4)
null, -- default_precision number
-- 04/12/02 Salary Basis Enhancement Ends
-- GSP
null, -- salary_effective_date date
null, -- gsp_dummy_txn varchar2(30)
-- End of GSP
null,
null,
null,
null,
null
));
update_assignment(
p_validate => p_validate
,p_login_person_id => l_login_person_id
,p_new_hire_appl_hire => l_new_hire_appl_hire
,p_assignment_id => l_assignment_rec.assignment_id
,p_object_version_number => l_assignment_rec.object_version_number
,p_effective_date => l_effective_date
,p_datetrack_update_mode => l_datetrack_update_mode
,p_organization_id => l_assignment_rec.organization_id
,p_position_id => l_assignment_rec.position_id
,p_job_id => l_assignment_rec.job_id
,p_grade_id => l_assignment_rec.grade_id
,p_location_id => l_assignment_rec.location_id
,p_employment_category => l_assignment_rec.employment_category
,p_supervisor_id => l_assignment_rec.supervisor_id
,p_manager_flag => l_assignment_rec.manager_flag
,p_normal_hours => l_assignment_rec.normal_hours
,p_frequency => l_assignment_rec.frequency
,p_time_normal_finish => l_assignment_rec.time_normal_finish
,p_time_normal_start => l_assignment_rec.time_normal_start
,p_bargaining_unit_code => l_assignment_rec.bargaining_unit_code
,p_labour_union_member_flag => l_assignment_rec.labour_union_member_flag
,p_assignment_status_type_id=> l_assignment_rec.assignment_status_type_id
,p_change_reason => l_assignment_rec.change_reason
,p_ass_attribute_category => l_assignment_rec.ass_attribute_category
,p_ass_attribute1 => l_assignment_rec.ass_attribute1
,p_ass_attribute2 => l_assignment_rec.ass_attribute2
,p_ass_attribute3 => l_assignment_rec.ass_attribute3
,p_ass_attribute4 => l_assignment_rec.ass_attribute4
,p_ass_attribute5 => l_assignment_rec.ass_attribute5
,p_ass_attribute6 => l_assignment_rec.ass_attribute6
,p_ass_attribute7 => l_assignment_rec.ass_attribute7
,p_ass_attribute8 => l_assignment_rec.ass_attribute8
,p_ass_attribute9 => l_assignment_rec.ass_attribute9
,p_ass_attribute10 => l_assignment_rec.ass_attribute10
,p_ass_attribute11 => l_assignment_rec.ass_attribute11
,p_ass_attribute12 => l_assignment_rec.ass_attribute12
,p_ass_attribute13 => l_assignment_rec.ass_attribute13
,p_ass_attribute14 => l_assignment_rec.ass_attribute14
,p_ass_attribute15 => l_assignment_rec.ass_attribute15
,p_ass_attribute16 => l_assignment_rec.ass_attribute16
,p_ass_attribute17 => l_assignment_rec.ass_attribute17
,p_ass_attribute18 => l_assignment_rec.ass_attribute18
,p_ass_attribute19 => l_assignment_rec.ass_attribute19
,p_ass_attribute20 => l_assignment_rec.ass_attribute20
,p_ass_attribute21 => l_assignment_rec.ass_attribute21
,p_ass_attribute22 => l_assignment_rec.ass_attribute22
,p_ass_attribute23 => l_assignment_rec.ass_attribute23
,p_ass_attribute24 => l_assignment_rec.ass_attribute24
,p_ass_attribute25 => l_assignment_rec.ass_attribute25
,p_ass_attribute26 => l_assignment_rec.ass_attribute26
,p_ass_attribute27 => l_assignment_rec.ass_attribute27
,p_ass_attribute28 => l_assignment_rec.ass_attribute28
,p_ass_attribute29 => l_assignment_rec.ass_attribute29
,p_ass_attribute30 => l_assignment_rec.ass_attribute30
,p_soft_coding_keyflex_id => l_assignment_rec.soft_coding_keyflex_id
,p_people_group_id => l_assignment_rec.people_group_id
,p_payroll_id => l_assignment_rec.payroll_id
,p_pay_basis_id => l_assignment_rec.pay_basis_id
,p_sal_review_period => l_assignment_rec.sal_review_period
,p_sal_review_period_frequency => l_assignment_rec.sal_review_period_frequency
,p_date_probation_end => l_assignment_rec.date_probation_end
,p_probation_period => l_assignment_rec.probation_period
,p_probation_unit => l_assignment_rec.probation_unit
,p_notice_period => l_assignment_rec.notice_period
,p_notice_period_uom => l_assignment_rec.notice_period_uom
,p_employee_category => l_assignment_rec.employee_category
,p_work_at_home => l_assignment_rec.work_at_home
,p_job_post_source_name => l_assignment_rec.job_post_source_name
,p_perf_review_period => l_assignment_rec.perf_review_period
,p_perf_review_period_frequency =>
l_assignment_rec.perf_review_period_frequency
,p_internal_address_line => l_assignment_rec.internal_address_line
,p_contract_id => l_assignment_rec.contract_id
,p_establishment_id => l_assignment_rec.establishment_id
,p_cagr_grade_def_id => l_assignment_rec.cagr_grade_def_id
,p_collective_agreement_id => l_assignment_rec.collective_agreement_id
,p_cagr_id_flex_num => l_assignment_rec.cagr_id_flex_num
,p_business_group_id => l_assignment_rec.business_group_id
,p_grade_ladder_pgm_id => l_assignment_rec.grade_ladder_pgm_id
,p_assignment_type => l_assignment_rec.assignment_type
--,p_supervisor_assignment_id => l_assignment_rec.supervisor_assignment_id
,p_vacancy_id => l_assignment_rec.vacancy_id
,p_special_ceiling_step_id => l_special_ceiling_step_id
,p_primary_flag => l_assignment_rec.primary_flag
,p_person_id => l_assignment_rec.person_id
,p_default_code_comb_id => l_assignment_rec.default_code_comb_id
,p_project_title => l_assignment_rec.project_title
,p_set_of_books_id => l_assignment_rec.set_of_books_id
,p_source_type => l_assignment_rec.source_type
,p_title => l_assignment_rec.title
,p_vendor_assignment_number => l_assignment_rec.vendor_assignment_number
,p_vendor_employee_number => l_assignment_rec.vendor_employee_number
,p_vendor_id => l_assignment_rec.vendor_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_element_warning => TRUE
,p_element_changed => l_element_changed
,p_page_error => l_page_error
,p_page_error_msg => l_page_error_msg
,p_page_warning => l_page_warning
,p_page_warning_msg => l_page_warning_msg
,p_organization_error => l_organization_error
,p_organization_error_msg => l_organization_error_msg
,p_job_error => l_job_error
,p_job_error_msg => l_job_error_msg
,p_position_error => l_position_error
,p_position_error_msg => l_position_error_msg
,p_grade_error => l_grade_error
,p_grade_error_msg => l_grade_error_msg
,p_supervisor_error => l_supervisor_error
,p_supervisor_error_msg => l_supervisor_error_msg
,p_location_error => l_location_error
,p_location_error_msg => l_location_error_msg
-- GSP change
,p_ltt_salary_data => ltt_salary_data
,p_gsp_post_process_warning => lv_gsp_post_process_warning
-- End of GSP change
,p_po_header_id => l_assignment_rec.po_header_id
,p_po_line_id => l_assignment_rec.po_line_id
,p_vendor_site_id => l_assignment_rec.vendor_site_id
,p_projected_asgn_end => l_assignment_rec.projected_assignment_end
,p_j_changed => l_j_changed
);
update_apl_assignment(
p_assignment_rec => l_assignment_rec
,p_validate => p_validate
,p_effective_date => l_effective_date
,p_person_id => l_assignment_rec.person_id
,p_appl_assignment_id => l_assignment_rec.assignment_id
,p_person_type_id => l_person_type_id
,p_overwrite_primary => l_asgn_change_mode
,p_ovn => l_per_object_version_number);
update_salary_proposal(l_assignment_rec.assignment_id,l_effective_date);
procedure update_apl_assignment
(p_validate in boolean default false,
p_assignment_rec in out nocopy per_all_assignments_f%rowtype,
p_effective_date in date,
p_person_id in number,
p_appl_assignment_id in number,
p_person_type_id in number,
p_overwrite_primary in varchar2,
p_ovn in number
) is
l_per_effective_start_date date;
select assignment_id from per_all_assignments_f where
person_id = p_person_id and primary_flag='Y' and assignment_type='E' and
p_effective_date between effective_start_date and effective_end_date;
select supervisor_assignment_id from per_all_assignments_f
where assignment_id = p_assignment_id;
procedure update_assignment
(p_validate in boolean default false
,p_login_person_id in number default null
,p_new_hire_appl_hire in varchar2 default 'N'
,p_assignment_id in number
,p_object_version_number in number
,p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_organization_id in number
,p_position_id in number default null
,p_job_id in number default null
,p_grade_id in number default null
,p_location_id in number default null
,p_employment_category in varchar2 default null
,p_supervisor_id in number default null
,p_manager_flag in varchar2 default null
,p_normal_hours in number default null
,p_frequency in varchar2 default null
,p_time_normal_finish in varchar2 default null
,p_time_normal_start in varchar2 default null
,p_bargaining_unit_code in varchar2 default null
,p_labour_union_member_flag in varchar2 default null
,p_assignment_status_type_id in number default null
,p_change_reason in varchar2 default null
,p_ass_attribute_category in varchar2 default null
,p_ass_attribute1 in varchar2 default null
,p_ass_attribute2 in varchar2 default null
,p_ass_attribute3 in varchar2 default null
,p_ass_attribute4 in varchar2 default null
,p_ass_attribute5 in varchar2 default null
,p_ass_attribute6 in varchar2 default null
,p_ass_attribute7 in varchar2 default null
,p_ass_attribute8 in varchar2 default null
,p_ass_attribute9 in varchar2 default null
,p_ass_attribute10 in varchar2 default null
,p_ass_attribute11 in varchar2 default null
,p_ass_attribute12 in varchar2 default null
,p_ass_attribute13 in varchar2 default null
,p_ass_attribute14 in varchar2 default null
,p_ass_attribute15 in varchar2 default null
,p_ass_attribute16 in varchar2 default null
,p_ass_attribute17 in varchar2 default null
,p_ass_attribute18 in varchar2 default null
,p_ass_attribute19 in varchar2 default null
,p_ass_attribute20 in varchar2 default null
,p_ass_attribute21 in varchar2 default null
,p_ass_attribute22 in varchar2 default null
,p_ass_attribute23 in varchar2 default null
,p_ass_attribute24 in varchar2 default null
,p_ass_attribute25 in varchar2 default null
,p_ass_attribute26 in varchar2 default null
,p_ass_attribute27 in varchar2 default null
,p_ass_attribute28 in varchar2 default null
,p_ass_attribute29 in varchar2 default null
,p_ass_attribute30 in varchar2 default null
,p_soft_coding_keyflex_id in out nocopy number
,p_people_group_id in number default null
,p_contract_id in number default null
,p_establishment_id in number default null
,p_cagr_grade_def_id in number default null
,p_collective_agreement_id in number default null
,p_cagr_id_flex_num in number default null
,p_payroll_id in number default null
,p_pay_basis_id in number default null
,p_sal_review_period in number default null
,p_sal_review_period_frequency in varchar2 default null
,p_date_probation_end in date default null
,p_probation_period in number default null
,p_probation_unit in varchar2 default null
,p_notice_period in number default null
,p_notice_period_uom in varchar2 default null
,p_employee_category in varchar2 default null
,p_work_at_home in varchar2 default null
,p_job_post_source_name in varchar2 default null
,p_perf_review_period in number default null
,p_perf_review_period_frequency in varchar2 default null
,p_internal_address_line in varchar2 default null
,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
-- GSP change
,p_grade_ladder_pgm_id in per_all_assignments_f.grade_ladder_pgm_id%TYPE
-- GSP change
,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
--,p_supervisor_assignment_id in per_all_assignments_f.supervisor_assignment_id%TYPE
,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
,p_special_ceiling_step_id in out nocopy per_all_assignments_f.special_ceiling_step_id%TYPE
,p_primary_flag in per_all_assignments_f.primary_flag%TYPE
,p_person_id in per_all_assignments_f.person_id%TYPE
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_element_warning in boolean
,p_element_changed in out nocopy varchar2
,p_email_id in varchar2 default null
,p_page_error in out nocopy varchar2
,p_page_error_msg in out nocopy varchar2
,p_page_warning in out nocopy varchar2
,p_page_warning_msg in out nocopy varchar2
,p_organization_error in out nocopy varchar2
,p_organization_error_msg in out nocopy varchar2
,p_job_error in out nocopy varchar2
,p_job_error_msg in out nocopy varchar2
,p_position_error in out nocopy varchar2
,p_position_error_msg in out nocopy varchar2
,p_grade_error in out nocopy varchar2
,p_grade_error_msg in out nocopy varchar2
,p_supervisor_error in out nocopy varchar2
,p_supervisor_error_msg in out nocopy varchar2
,p_location_error in out nocopy varchar2
,p_location_error_msg in out nocopy varchar2
,p_title in varchar2 default null
,p_default_code_comb_id in number default null
,p_set_of_books_id in number default null
,p_source_type in varchar2 default null
,p_project_title in varchar2 default null
,p_vendor_assignment_number in varchar2 default null
,p_vendor_employee_number in varchar2 default null
,p_vendor_id in number default null
--GSP populates salary information from Grade Ladder assignment
,p_ltt_salary_data IN OUT NOCOPY sshr_sal_prop_tab_typ
,p_gsp_post_process_warning out nocopy varchar2
-- End of GSP
,p_po_header_id in number default null
,p_po_line_id in number default null
,p_vendor_site_id in number default null
,p_projected_asgn_end in date default null
,p_j_changed in varchar2 default 'Y'
) is
--
l_effective_date date;
l_spp_delete_warning boolean;
l_proc varchar2(72) := g_package||'update_assignment';
select job_id
from per_all_assignments_f
where assignment_id=p_assignment_id
and l_effective_date between effective_start_date and effective_end_date;
select jwc.wc_code
from pay_job_wc_code_usages jwc,
hr_locations_all hl
where jwc.job_id = pc_job_id
and hl.location_id = p_location_id
and jwc.state_code = hl.region_2;
select per_system_status
from per_assignment_status_types
where assignment_status_type_id=p_assignment_status_type_id;
select *
from pay_people_groups
where people_group_id = p_people_group_id;
select *
from hr_soft_coding_keyflex
where soft_coding_keyflex_id = p_soft_coding_keyflex_id;
select *
from per_cagr_grades_def
where cagr_grade_def_id = p_cagr_grade_def_id;
select to_char(user_column_id)
from pay_user_columns puc, hr_organization_information hoi
WHERE (HOI.org_information1 = to_char(PUC.user_table_id)
OR HOI.org_information1 is null )
AND HOI.org_information_context = 'Work Schedule'
AND HOI.organization_id = p_organization_id
AND (puc.business_group_id is null or
puc.business_group_id = p_business_group_id)
AND (puc.legislation_code is null or
puc.legislation_code = leg_code)
and puc.user_column_name = hoi.org_information2;
select to_char(user_column_id)
from pay_user_columns puc, hr_organization_information hoi
WHERE (HOI.org_information1 = to_char(PUC.user_table_id)
OR HOI.org_information1 is null )
AND HOI.org_information_context = 'Work Schedule'
AND HOI.organization_id = p_organization_id
AND (puc.business_group_id is null or
puc.business_group_id = p_business_group_id)
AND (puc.legislation_code is null or
puc.legislation_code = leg_code)
and puc.user_column_id = segment4;*/
select scl.segment1
from hr_soft_coding_keyflex scl,
per_all_assignments_f asg
where asg.person_id = p_login_person_id
and asg.business_group_id = p_business_group_id --the new hire business group
and asg.primary_flag = 'Y'
and trunc(sysdate) between asg.effective_start_date
and asg.effective_end_date
and asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
select rule_mode
from pay_legislation_rules plr
where plr.legislation_code = leg_code
and plr.rule_type = 'CWK_S'
and exists
(select null
from fnd_segment_attribute_values fsav
where fsav.id_flex_num = plr.rule_mode
and fsav.application_id = 800
and fsav.id_flex_code = 'SCL'
and fsav.segment_attribute_type = 'ASSIGNMENT'
and fsav.attribute_value = 'Y')
and exists
(select null
from pay_legislation_rules plr2
where plr2.legislation_code = plr.legislation_code
and plr2.rule_type = 'CWK_SDL'
and plr2.rule_mode = 'A') ;
select rule_mode
from pay_legislation_rules
where legislation_code = 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 = leg_code
and rule_type = 'SDL'
and rule_mode = 'A');
select asg.assignment_id,
asg.payroll_id,
asg.soft_coding_keyflex_id
from per_all_assignments_f asg
where asg.person_id = p_person_id
and l_effective_date between asg.effective_start_date
and asg.effective_end_date
and asg.assignment_type = 'E'
and assignment_id <> p_assignment_id
and asg.payroll_id is not null
and asg.soft_coding_keyflex_id is null
order by asg.assignment_id;
But while calling hr_assignment_api.update_emp_asg_criteria we have to pass a proper
scl_id so that no error appears.*/
hr_kflex_utility.upd_or_sel_keyflex_comb
(p_appl_short_name => 'PER'
,p_flex_code => 'SCL'
,p_flex_num => l_flex_num
,p_segment1 => l_soft_coding_keyflex.segment1
,p_segment2 => l_soft_coding_keyflex.segment2
,p_segment3 => l_soft_coding_keyflex.segment3
,p_segment4 => l_soft_coding_keyflex.segment4
,p_segment5 => l_soft_coding_keyflex.segment5
,p_segment6 => l_soft_coding_keyflex.segment6
,p_segment7 => l_soft_coding_keyflex.segment7
,p_segment8 => l_soft_coding_keyflex.segment8
,p_segment9 => l_soft_coding_keyflex.segment9
,p_segment10 => l_soft_coding_keyflex.segment10
,p_segment11 => l_soft_coding_keyflex.segment11
,p_segment12 => l_soft_coding_keyflex.segment12
,p_segment13 => l_soft_coding_keyflex.segment13
,p_segment14 => l_soft_coding_keyflex.segment14
,p_segment15 => l_soft_coding_keyflex.segment15
,p_segment16 => l_soft_coding_keyflex.segment16
,p_segment17 => l_soft_coding_keyflex.segment17
,p_segment18 => l_soft_coding_keyflex.segment18
,p_segment19 => l_soft_coding_keyflex.segment19
,p_segment20 => l_soft_coding_keyflex.segment20
,p_segment21 => l_soft_coding_keyflex.segment21
,p_segment22 => l_soft_coding_keyflex.segment22
,p_segment23 => l_soft_coding_keyflex.segment23
,p_segment24 => l_soft_coding_keyflex.segment24
,p_segment25 => l_soft_coding_keyflex.segment25
,p_segment26 => l_soft_coding_keyflex.segment26
,p_segment27 => l_soft_coding_keyflex.segment27
,p_segment28 => l_soft_coding_keyflex.segment28
,p_segment29 => l_soft_coding_keyflex.segment29
,p_segment30 => l_soft_coding_keyflex.segment30
,p_concat_segments_in => l_old_conc_segments
,p_ccid => l_soft_coding_keyflex_id
,p_concat_segments_out => l_concatenated_segments
);
update per_all_assignments_f set soft_coding_keyflex_id = p_soft_coding_keyflex_id
where assignment_id = c.assignment_id and l_effective_date between
effective_start_date and effective_end_date;
hr_assignment_api.update_cwk_asg_criteria
(p_effective_date => l_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
,p_grade_id => p_grade_id
,p_position_id => p_position_id
,p_job_id => p_job_id
,p_location_id => p_location_id
,p_organization_id => p_organization_id
,p_pay_basis_id => p_pay_basis_id
-- Fix for Bug #3083706 for updating people grp for CWK
,p_segment1 => l_people_groups.segment1
,p_segment2 => l_people_groups.segment2
,p_segment3 => l_people_groups.segment3
,p_segment4 => l_people_groups.segment4
,p_segment5 => l_people_groups.segment5
,p_segment6 => l_people_groups.segment6
,p_segment7 => l_people_groups.segment7
,p_segment8 => l_people_groups.segment8
,p_segment9 => l_people_groups.segment9
,p_segment10 => l_people_groups.segment10
,p_segment11 => l_people_groups.segment11
,p_segment12 => l_people_groups.segment12
,p_segment13 => l_people_groups.segment13
,p_segment14 => l_people_groups.segment14
,p_segment15 => l_people_groups.segment15
,p_segment16 => l_people_groups.segment16
,p_segment17 => l_people_groups.segment17
,p_segment18 => l_people_groups.segment18
,p_segment19 => l_people_groups.segment19
,p_segment20 => l_people_groups.segment20
,p_segment21 => l_people_groups.segment21
,p_segment22 => l_people_groups.segment22
,p_segment23 => l_people_groups.segment23
,p_segment24 => l_people_groups.segment24
,p_segment25 => l_people_groups.segment25
,p_segment26 => l_people_groups.segment26
,p_segment27 => l_people_groups.segment27
,p_segment28 => l_people_groups.segment28
,p_segment29 => l_people_groups.segment29
,p_segment30 => l_people_groups.segment30
-- end of the bug Fix for #3083706
,p_people_group_name => l_group_name
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_people_group_id => l_people_group_id
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning
);
hr_assignment_api.update_cwk_asg
(p_effective_date => l_effective_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
-- Fix for updating assignment_category for CWK , fix for Bug #3083776
,p_assignment_category => p_employment_category
,p_change_reason => p_change_reason
--,p_comments => p_comments
,p_default_code_comb_id => p_default_code_comb_id
,p_establishment_id => p_establishment_id
,p_frequency => p_frequency
,p_internal_address_line => p_internal_address_line
,p_labour_union_member_flag => p_labour_union_member_flag
,p_manager_flag => p_manager_flag
,p_normal_hours => p_normal_hours
,p_project_title => p_project_title
,p_set_of_books_id => p_set_of_books_id
,p_source_type => p_source_type
,p_supervisor_id => p_supervisor_id
,p_time_normal_finish => p_time_normal_finish
,p_time_normal_start => p_time_normal_start
,p_title => p_title
,p_vendor_assignment_number => p_vendor_assignment_number
,p_vendor_employee_number => p_vendor_employee_number
,p_vendor_id => p_vendor_id
,p_po_header_id => p_po_header_id
,p_po_line_id => p_po_line_id
,p_vendor_site_id => p_vendor_site_id
,p_projected_assignment_end => p_projected_asgn_end
--,p_assignment_status_type_id => p_assignment_status_type_id --3262804
,p_attribute_category => p_ass_attribute_category
,p_attribute1 => p_ass_attribute1
,p_attribute2 => p_ass_attribute2
,p_attribute3 => p_ass_attribute3
,p_attribute4 => p_ass_attribute4
,p_attribute5 => p_ass_attribute5
,p_attribute6 => p_ass_attribute6
,p_attribute7 => p_ass_attribute7
,p_attribute8 => p_ass_attribute8
,p_attribute9 => p_ass_attribute9
,p_attribute10 => p_ass_attribute10
,p_attribute11 => p_ass_attribute11
,p_attribute12 => p_ass_attribute12
,p_attribute13 => p_ass_attribute13
,p_attribute14 => p_ass_attribute14
,p_attribute15 => p_ass_attribute15
,p_attribute16 => p_ass_attribute16
,p_attribute17 => p_ass_attribute17
,p_attribute18 => p_ass_attribute18
,p_attribute19 => p_ass_attribute19
,p_attribute20 => p_ass_attribute20
,p_attribute21 => p_ass_attribute21
,p_attribute22 => p_ass_attribute22
,p_attribute23 => p_ass_attribute23
,p_attribute24 => p_ass_attribute24
,p_attribute25 => p_ass_attribute25
,p_attribute26 => p_ass_attribute26
,p_attribute27 => p_ass_attribute27
,p_attribute28 => p_ass_attribute28
,p_attribute29 => p_ass_attribute29
,p_attribute30 => p_ass_attribute30
,p_scl_segment1 => l_soft_coding_keyflex.segment1
,p_scl_segment2 => l_soft_coding_keyflex.segment2
,p_scl_segment3 => l_soft_coding_keyflex.segment3
,p_scl_segment4 => l_soft_coding_keyflex.segment4
,p_scl_segment5 => l_soft_coding_keyflex.segment5
,p_scl_segment6 => l_soft_coding_keyflex.segment6
,p_scl_segment7 => l_soft_coding_keyflex.segment7
,p_scl_segment8 => l_soft_coding_keyflex.segment8
,p_scl_segment9 => l_soft_coding_keyflex.segment9
,p_scl_segment10 => l_soft_coding_keyflex.segment10
,p_scl_segment11 => l_soft_coding_keyflex.segment11
,p_scl_segment12 => l_soft_coding_keyflex.segment12
,p_scl_segment13 => l_soft_coding_keyflex.segment13
,p_scl_segment14 => l_soft_coding_keyflex.segment14
,p_scl_segment15 => l_soft_coding_keyflex.segment15
,p_scl_segment16 => l_soft_coding_keyflex.segment16
,p_scl_segment17 => l_soft_coding_keyflex.segment17
,p_scl_segment18 => l_soft_coding_keyflex.segment18
,p_scl_segment19 => l_soft_coding_keyflex.segment19
,p_scl_segment20 => l_soft_coding_keyflex.segment20
,p_scl_segment21 => l_soft_coding_keyflex.segment21
,p_scl_segment22 => l_soft_coding_keyflex.segment22
,p_scl_segment23 => l_soft_coding_keyflex.segment23
,p_scl_segment24 => l_soft_coding_keyflex.segment24
,p_scl_segment25 => l_soft_coding_keyflex.segment25
,p_scl_segment26 => l_soft_coding_keyflex.segment26
,p_scl_segment27 => l_soft_coding_keyflex.segment27
,p_scl_segment28 => l_soft_coding_keyflex.segment28
,p_scl_segment29 => l_soft_coding_keyflex.segment29
,p_scl_segment30 => l_soft_coding_keyflex.segment30
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_comment_id => l_comment_id
,p_no_managers_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_concatenated_segments => l_concatenated_segments
,p_hourly_salaried_warning => l_hourly_salaried_warning
);
hr_assignment_api.update_emp_asg_criteria
(p_effective_date => l_effective_date
,p_datetrack_update_mode => p_datetrack_update_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
,p_grade_id => p_grade_id
,p_position_id => p_position_id
,p_job_id => p_job_id
,p_location_id => p_location_id
,p_special_ceiling_step_id => p_special_ceiling_step_id
,p_organization_id => p_organization_id
,p_employment_category => p_employment_category
,p_payroll_id => p_payroll_id
,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
--,p_supervisor_assignment_id => p_supervisor_assignment_id
,p_pay_basis_id => p_pay_basis_id
,p_segment1 => l_people_groups.segment1
,p_segment2 => l_people_groups.segment2
,p_segment3 => l_people_groups.segment3
,p_segment4 => l_people_groups.segment4
,p_segment5 => l_people_groups.segment5
,p_segment6 => l_people_groups.segment6
,p_segment7 => l_people_groups.segment7
,p_segment8 => l_people_groups.segment8
,p_segment9 => l_people_groups.segment9
,p_segment10 => l_people_groups.segment10
,p_segment11 => l_people_groups.segment11
,p_segment12 => l_people_groups.segment12
,p_segment13 => l_people_groups.segment13
,p_segment14 => l_people_groups.segment14
,p_segment15 => l_people_groups.segment15
,p_segment16 => l_people_groups.segment16
,p_segment17 => l_people_groups.segment17
,p_segment18 => l_people_groups.segment18
,p_segment19 => l_people_groups.segment19
,p_segment20 => l_people_groups.segment20
,p_segment21 => l_people_groups.segment21
,p_segment22 => l_people_groups.segment22
,p_segment23 => l_people_groups.segment23
,p_segment24 => l_people_groups.segment24
,p_segment25 => l_people_groups.segment25
,p_segment26 => l_people_groups.segment26
,p_segment27 => l_people_groups.segment27
,p_segment28 => l_people_groups.segment28
,p_segment29 => l_people_groups.segment29
,p_segment30 => l_people_groups.segment30
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_concatenated_segments => l_concatenated_segments
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_people_group_id => l_people_group_id
,p_group_name => l_group_name
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning
,p_gsp_post_process_warning => p_gsp_post_process_warning
);
select PAYROLL_ID into l_old_payroll_id
from per_all_assignments_f
where assignment_id = p_assignment_id and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
,p_dt_mode => p_datetrack_update_mode ---check??
,p_validation_start_date => l_validate_start_date
,p_validation_end_date => l_validate_end_date
,p_entries_changed => l_entries_changed
,p_old_people_group_id => -1
,p_new_people_group_id => p_people_group_id
);
hr_assignment_api.update_emp_asg
(p_effective_date => l_effective_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
,p_supervisor_id => p_supervisor_id
,p_manager_flag => p_manager_flag
,p_normal_hours => p_normal_hours
,p_frequency => p_frequency
,p_time_normal_finish => p_time_normal_finish
,p_time_normal_start => p_time_normal_start
,p_bargaining_unit_code => p_bargaining_unit_code
,p_labour_union_member_flag => p_labour_union_member_flag
,p_sal_review_period => p_sal_review_period
,p_sal_review_period_frequency => p_sal_review_period_frequency
,p_date_probation_end => p_date_probation_end
,p_probation_period => p_probation_period
,p_probation_unit => p_probation_unit
,p_notice_period => p_notice_period
,p_notice_period_uom => p_notice_period_uom
,p_employee_category => p_employee_category
,p_work_at_home => p_work_at_home
,p_job_post_source_name => p_job_post_source_name
,p_perf_review_period => p_perf_review_period
,p_perf_review_period_frequency => p_perf_review_period_frequency
,p_internal_address_line => p_internal_address_line
,p_ass_attribute_category => p_ass_attribute_category
,p_ass_attribute1 => p_ass_attribute1
,p_ass_attribute2 => p_ass_attribute2
,p_ass_attribute3 => p_ass_attribute3
,p_ass_attribute4 => p_ass_attribute4
,p_ass_attribute5 => p_ass_attribute5
,p_ass_attribute6 => p_ass_attribute6
,p_ass_attribute7 => p_ass_attribute7
,p_ass_attribute8 => p_ass_attribute8
,p_ass_attribute9 => p_ass_attribute9
,p_ass_attribute10 => p_ass_attribute10
,p_ass_attribute11 => p_ass_attribute11
,p_ass_attribute12 => p_ass_attribute12
,p_ass_attribute13 => p_ass_attribute13
,p_ass_attribute14 => p_ass_attribute14
,p_ass_attribute15 => p_ass_attribute15
,p_ass_attribute16 => p_ass_attribute16
,p_ass_attribute17 => p_ass_attribute17
,p_ass_attribute18 => p_ass_attribute18
,p_ass_attribute19 => p_ass_attribute19
,p_ass_attribute20 => p_ass_attribute20
,p_ass_attribute21 => p_ass_attribute21
,p_ass_attribute22 => p_ass_attribute22
,p_ass_attribute23 => p_ass_attribute23
,p_ass_attribute24 => p_ass_attribute24
,p_ass_attribute25 => p_ass_attribute25
,p_ass_attribute26 => p_ass_attribute26
,p_ass_attribute27 => p_ass_attribute27
,p_ass_attribute28 => p_ass_attribute28
,p_ass_attribute29 => p_ass_attribute29
,p_ass_attribute30 => p_ass_attribute30
,p_segment1 => l_SOFt_coding_keyflex.segment1
,p_segment2 => l_soft_coding_keyflex.segment2
,p_segment3 => l_soft_coding_keyflex.segment3
,p_segment4 => l_soft_coding_keyflex.segment4
,p_segment5 => l_soft_coding_keyflex.segment5
,p_segment6 => l_soft_coding_keyflex.segment6
,p_segment7 => l_soft_coding_keyflex.segment7
,p_segment8 => l_soft_coding_keyflex.segment8
,p_segment9 => l_soft_coding_keyflex.segment9
,p_segment10 => l_soft_coding_keyflex.segment10
,p_segment11 => l_soft_coding_keyflex.segment11
,p_segment12 => l_soft_coding_keyflex.segment12
,p_segment13 => l_soft_coding_keyflex.segment13
,p_segment14 => l_soft_coding_keyflex.segment14
,p_segment15 => l_soft_coding_keyflex.segment15
,p_segment16 => l_soft_coding_keyflex.segment16
,p_segment17 => l_soft_coding_keyflex.segment17
,p_segment18 => l_soft_coding_keyflex.segment18
,p_segment19 => l_soft_coding_keyflex.segment19
,p_segment20 => l_soft_coding_keyflex.segment20
,p_segment21 => l_soft_coding_keyflex.segment21
,p_segment22 => l_soft_coding_keyflex.segment22
,p_segment23 => l_soft_coding_keyflex.segment23
,p_segment24 => l_soft_coding_keyflex.segment24
,p_segment25 => l_soft_coding_keyflex.segment25
,p_segment26 => l_soft_coding_keyflex.segment26
,p_segment27 => l_soft_coding_keyflex.segment27
,p_segment28 => l_soft_coding_keyflex.segment28
,p_segment29 => l_soft_coding_keyflex.segment29
,p_segment30 => l_soft_coding_keyflex.segment30
,p_cag_segment1 => l_cagr_grades_def.segment1
,p_cag_segment2 => l_cagr_grades_def.segment2
,p_cag_segment3 => l_cagr_grades_def.segment3
,p_cag_segment4 => l_cagr_grades_def.segment4
,p_cag_segment5 => l_cagr_grades_def.segment5
,p_cag_segment6 => l_cagr_grades_def.segment6
,p_cag_segment7 => l_cagr_grades_def.segment7
,p_cag_segment8 => l_cagr_grades_def.segment8
,p_cag_segment9 => l_cagr_grades_def.segment9
,p_cag_segment10 => l_cagr_grades_def.segment10
,p_cag_segment11 => l_cagr_grades_def.segment11
,p_cag_segment12 => l_cagr_grades_def.segment12
,p_cag_segment13 => l_cagr_grades_def.segment13
,p_cag_segment14 => l_cagr_grades_def.segment14
,p_cag_segment15 => l_cagr_grades_def.segment15
,p_cag_segment16 => l_cagr_grades_def.segment16
,p_cag_segment17 => l_cagr_grades_def.segment17
,p_cag_segment18 => l_cagr_grades_def.segment18
,p_cag_segment19 => l_cagr_grades_def.segment19
,p_cag_segment20 => l_cagr_grades_def.segment20
--,p_contract_id => p_contract_id
--,p_establishment_id => p_establishment_id
,p_collective_agreement_id => p_collective_agreement_id
,p_cagr_id_flex_num => p_cagr_id_flex_num
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_concatenated_segments => l_concatenated_segments
,p_no_managers_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_title => p_title
);
/*hr_assignment_api.update_emp_asg_criteria
(p_effective_date => l_effective_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
,p_grade_id => p_grade_id
,p_special_ceiling_step_id => p_special_ceiling_step_id
,p_payroll_id => p_payroll_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_concatenated_segments => l_concatenated_segments
,p_people_group_id => l_people_group_id
,p_group_name => l_group_name
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning
,p_gsp_post_process_warning => p_gsp_post_process_warning
);*/
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_change_reason => p_change_reason
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_change_reason => p_change_reason
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_change_reason => p_change_reason
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
,p_assignment_status_type_id => p_assignment_status_type_id
,p_change_reason => p_change_reason
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
update_salary_proposal(p_assignment_id,l_effective_date);
end update_assignment;
select primary_flag into l_primary_flag from per_all_assignments_f
where assignment_id = l_assignment_id and trunc(l_effective_date)
between effective_start_date and effective_end_date;
SELECT
asgRec.number_value(1) assignment_id,
asgRec.number_value(8) business_group_id,
asgRec.number_value(7) organization_id,
org.name organization_name,
asgRec.number_value(2) object_version_number,
asgRec.number_value(12) position_id,
pos.name position_name,
asgRec.number_value(14) job_id,
job.name job_name,
asgRec.number_value(16) grade_id,
grade.name grade_name,
asgRec.number_value(18) location_id,
asgRec.varchar2_value(19) employment_category,
asgRec.number_value(20) supervisor_id,
asgRec.varchar2_value(21) manager_flag,
asgRec.number_value(22) normal_hours,
asgRec.varchar2_value(23) frequency,
asgRec.varchar2_value(24) time_normal_finish,
asgRec.varchar2_value(25) time_normal_start,
asgRec.number_value(28) special_ceiling_step_id,
asgRec.number_value(29) assignment_status_type_id,
asgRec.varchar2_value(30) change_reason,
asgRec.varchar2_value(31) ass_attribute_category,
asgRec.varchar2_value(32) ass_attribute1,
asgRec.varchar2_value(33) ass_attribute2,
asgRec.varchar2_value(34) ass_attribute3,
asgRec.varchar2_value(35) ass_attribute4,
asgRec.varchar2_value(36) ass_attribute5,
asgRec.varchar2_value(37) ass_attribute6,
asgRec.varchar2_value(38) ass_attribute7,
asgRec.varchar2_value(39) ass_attribute8,
asgRec.varchar2_value(40) ass_attribute9,
asgRec.varchar2_value(41) ass_attribute10,
asgRec.varchar2_value(42) ass_attribute11,
asgRec.varchar2_value(43) ass_attribute12,
asgRec.varchar2_value(44) ass_attribute13,
asgRec.varchar2_value(45) ass_attribute14,
asgRec.varchar2_value(46) ass_attribute15,
asgRec.varchar2_value(47) ass_attribute16,
asgRec.varchar2_value(48) ass_attribute17,
asgRec.varchar2_value(49) ass_attribute18,
asgRec.varchar2_value(50) ass_attribute19,
asgRec.varchar2_value(51) ass_attribute20,
asgRec.varchar2_value(52) ass_attribute21,
asgRec.varchar2_value(53) ass_attribute22,
asgRec.varchar2_value(54) ass_attribute23,
asgRec.varchar2_value(55) ass_attribute24,
asgRec.varchar2_value(56) ass_attribute25,
asgRec.varchar2_value(57) ass_attribute26,
asgRec.varchar2_value(58) ass_attribute27,
asgRec.varchar2_value(59) ass_attribute28,
asgRec.varchar2_value(60) ass_attribute29,
asgRec.varchar2_value(61) ass_attribute30,
asgRec.number_value(62) people_group_id,
asgRec.number_value(63) soft_coding_keyflex_id,
asgRec.number_value(66) sal_review_period,
asgRec.varchar2_value(67) sal_review_period_frequency,
asgRec.number_value(71) notice_period,
asgRec.varchar2_value(73) employee_category,
asgRec.varchar2_value(74) work_at_home,
asgRec.varchar2_value(75) job_post_source_name,
asgRec.number_value(76) perf_review_period,
asgRec.varchar2_value(77) perf_review_period_frequency,
asgRec.number_value(64) payroll_id,
asgRec.number_value(65) pay_basis_id,
asgRec.number_value(80) establishment_id,
asgRec.varchar2_value(88) title,
asgRec.varchar2_value(89) project_title,
asgRec.varchar2_value(90) source_type,
asgRec.varchar2_value(91) vendor_assignment_number,
asgRec.varchar2_value(92) vendor_employee_number,
asgRec.number_value(84) default_code_comb_id,
asgRec.number_value(85) set_of_books_id,
asgRec.number_value(86) vendor_id,
vendor.vendor_name vendor_name,
asgRec.varchar2_value(87) assignment_type,
asgRec.number_value(104) grade_ladder_id,
pgm.name grade_ladder_name,
asgRec.number_value(105) po_header_id,
asgRec.number_value(106) po_line_id,
asgRec.number_value(107) vendor_site_id,
po_heads.segment1 po_number,
po_lines.line_num po_line_number,
vend_sits.vendor_site_code vendor_site_name,
asgRec.DATE_VALUE(108) projected_assignment_end,
--- Position Defaulting changes
asgRec.number_value(69) probation_period,
asgRec.varchar2_value(70) probation_unit,
asgRec.DATE_VALUE(68) date_probation_end,
asgRec.varchar2_value(72) notice_period_uom,
asgRec.varchar2_value(26) bargaining_unit_code,
hl_bargaining_unit.meaning bargaining_unit_name,
asgRec.varchar2_value(27) labour_union_member_flag,
l_old_mgr_flag old_manager_flag,
l_old_labour_union_flag old_labour_union_flag,
l_old_organization_id old_organization_id,
l_old_pay_basis_id old_pay_basis_id,
l_old_grade_id old_grade_id,
l_primary_flag primary_flag
from
hr_api_transaction_steps ts
,hr_api_transaction_values otv, hr_all_organization_units_tl org
,hr_api_transaction_values jtv, per_jobs_tl job
,hr_api_transaction_values ptv, hr_all_positions_f_tl pos
,hr_api_transaction_values gtv, per_grades_tl grade
,hr_api_transaction_values vtv, po_vendors vendor
,hr_api_transaction_values gltv, ben_pgm_f pgm
,hr_api_transaction_values htv, po_headers_all po_heads
,hr_api_transaction_values ltv, po_lines_all po_lines
,hr_api_transaction_values stv, po_vendor_sites_all vend_sits
,hr_api_transaction_values btv, hr_lookups hl_bargaining_unit
where
ts.transaction_step_id = p_transaction_step_id
and otv.transaction_step_id(+) = ts.transaction_step_id
and otv.name(+) = 'P_ORGANIZATION_ID'
and otv.number_value = org.organization_id(+)
and org.language(+) = userenv('LANG')
and jtv.transaction_step_id(+) = ts.transaction_step_id
and jtv.name(+) = 'P_JOB_ID'
and jtv.number_value = job.job_id(+)
and job.language(+) = userenv('LANG')
and ptv.transaction_step_id(+) = ts.transaction_step_id
and ptv.name(+) = 'P_POSITION_ID'
and ptv.number_value = pos.position_id(+)
and pos.language(+) = userenv('LANG')
and gtv.transaction_step_id(+) = ts.transaction_step_id
and gtv.name(+) = 'P_GRADE_ID'
and gtv.number_value = grade.grade_id(+)
and grade.language(+) = userenv('LANG')
and vtv.transaction_step_id(+) = ts.transaction_step_id
and vtv.name(+) = 'P_VENDOR_ID'
and vtv.number_value = vendor.vendor_id(+)
and gltv.transaction_step_id(+) = ts.transaction_step_id
and gltv.name(+) = 'P_GRADE_LADDER_PGM_ID'
and gltv.number_value = pgm.pgm_id(+)
and htv.transaction_step_id(+) = ts.transaction_step_id
and htv.name(+) = 'P_PO_HEADER_ID'
and htv.number_value = po_heads.po_header_id(+)
and ltv.transaction_step_id(+) = ts.transaction_step_id
and ltv.name(+) = 'P_PO_LINE_ID'
and ltv.number_value = po_lines.po_line_id(+)
and stv.transaction_step_id(+) = ts.transaction_step_id
and stv.name(+) = 'P_VENDOR_SITE_ID'
and stv.number_value = vend_sits.vendor_site_id(+)
and btv.transaction_step_id(+) = ts.transaction_step_id
and btv.name(+) = 'P_BARGAINING_UNIT_CODE'
and btv.varchar2_value = hl_bargaining_unit.lookup_code(+) -- Bug 7603539
and hl_bargaining_unit.lookup_type(+) = 'BARGAINING_UNIT_CODE'
and hl_bargaining_unit.enabled_flag(+) = 'Y'
and (trunc(sysdate) between nvl(hl_bargaining_unit.start_date_active(+), trunc(sysdate))
and nvl(hl_bargaining_unit.end_date_active(+), trunc(sysdate)));
SELECT poh.segment1 po_number
FROM
po_headers_all poh
WHERE
poh.po_header_id = p_id
AND poh.type_lookup_code = 'STANDARD'
AND poh.authorization_status IN ('APPROVED', 'PRE-APPROVED')
AND poh.approved_flag = 'Y'
AND poh.enabled_flag = 'Y'
AND NVL(poh.cancel_flag, 'N') <> 'Y'
AND NVL(poh.frozen_flag, 'N') <> 'Y'
AND poh.org_id IS NOT NULL
AND EXISTS
(
SELECT
NULL
FROM po_lines_all pol ,
po_line_types_b polt
WHERE pol.po_header_id = poh.po_header_id
AND NVL(pol.cancel_flag, 'N') <> 'Y'
AND pol.line_type_id = polt.line_type_id
AND polt.purchase_basis = 'TEMP LABOR'
);
select line_number
from po_temp_labor_lines_v
where po_line_id = p_id;
select vendor_site_code
from po_vendor_sites_all
where vendor_site_id = p_id;