[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Worksheet_Values
( p_return_status OUT NOCOPY VARCHAR2,
p_position_id in NUMBER ,
p_org_id in NUMBER);
SELECT organization_id,
decode(completion_status,'C','REFRESH','CREATE') extract_method
FROM psb_data_extract_orgs
WHERE data_extract_id = p_data_extract_id
AND (p_extract_by_org = 'N'
OR (p_extract_by_org = 'Y' AND select_flag = 'Y'));
SELECT effective_date
FROM FND_SESSIONS
WHERE session_id = USERENV('sessionid');
SELECT grade_spine_id
FROM per_spinal_point_steps
WHERE step_id = p_entry_step_id;
SELECT effective_start_date,
effective_end_date,
rate_id,
grade_or_spinal_point_id,
rate_type,
maximum,
mid_value,
minimum,
sequence,
value
FROM PAY_GRADE_RULES
WHERE business_group_id = p_business_group_id
AND grade_rule_id = p_entry_grade_rule_id;
SELECT parent_spine_id
FROM PER_SPINAL_POINTS
WHERE spinal_point_id = l_grade_or_spinal_point_id
AND business_group_id = p_business_group_id;
SELECT pay_basis
FROM PER_PAY_BASES
WHERE pay_basis_id = p_pay_basis_id;
SELECT name
FROM PER_PARENT_SPINES
WHERE parent_spine_id = l_rate_or_payscale_id
AND business_group_id = p_business_group_id;
SELECT name
FROM PAY_RATES
WHERE rate_id = l_rate_or_payscale_id;
select grade_structure
from per_business_groups
where business_group_id = p_business_group_id;
Select currency_code
from gl_sets_of_books
where set_of_books_id = p_set_of_books_id;
SELECT grade_id,name
FROM PER_GRADES
WHERE grade_id = p_entry_grade_id
AND business_group_id = p_business_group_id;
Select ppe.pay_element_id ,ppe.salary_type,
ppo.pay_element_option_id
FROM psb_pay_elements ppe,
psb_pay_element_options ppo
WHERE ppe.data_extract_id = p_data_extract_id
AND ppe.salary_type = 'STEP'
AND ppe.name = l_rate_or_payscale_name
AND ppe.pay_element_id = ppo.pay_element_id
AND ppo.name = l_grade_name
AND ppo.grade_step = l_grade_step
AND ppo.sequence_number = l_sequence;
Select ppe.pay_element_id ,ppe.salary_type,
ppo.pay_element_option_id
FROM psb_pay_elements ppe,
psb_pay_element_options ppo
WHERE ppe.data_extract_id = p_data_extract_id
AND ppe.salary_type = 'RATE'
AND ppe.name = l_rate_or_payscale_name
AND ppe.pay_element_id = ppo.pay_element_id
AND ppo.name = l_grade_name;
Select pay_element_rate_id,
effective_start_DATE,
effective_END_DATE,
element_value,
currency_code
FROM psb_pay_element_rates
WHERE pay_element_id = l_pay_element_id
AND pay_element_option_id = l_pay_element_option_id;
l_last_update_date DATE;
l_last_updated_BY number;
l_last_update_login number;
l_last_update_date := SYSDATE;
l_last_updated_BY := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
'SELECT pg.grade_id,pgv.concatenated_segments '||
' FROM PER_GRADES pg,PER_GRADE_DEFINITIONS_KFV pgv ' ||
' WHERE pg.grade_id = ' || p_entry_grade_id ||
' AND pg.business_group_id = ' || p_business_group_id ||
' AND pg.grade_definition_id = pgv.grade_definition_id ' ||
' AND pgv.id_flex_num = ' || l_grade_id_flex_num ;
Select psb_pay_elements_s.nextval INTO l_pay_element_id
FROM dual;
PSB_PAY_ELEMENTS_PVT.INSERT_ROW
( p_api_version => 1.0,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_row_id => l_rowid,
p_pay_element_id => l_pay_element_id,
p_business_group_id => p_business_group_id,
p_data_extract_id => p_data_extract_id,
p_name => l_rate_or_payscale_name,
p_description => NULL,
p_element_value_type => 'A',
p_formula_id => NULL,
p_overwrite_flag => 'Y',
p_required_flag => NULL,
p_follow_salary => NULL,
p_pay_basis => l_pay_basis,
p_start_date => p_date_effective,
p_end_date => NULL,
p_processing_type => 'R',
p_period_type => NULL,
p_process_period_type => NULL,
p_max_element_value_type => NULL,
p_max_element_value => NULL,
p_salary_flag => 'Y',
p_salary_type => l_salary_type,
p_option_flag => 'N',
p_hr_element_type_id => NULL,
p_attribute_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_last_update_date => l_last_update_date,
p_last_updated_by => l_last_updated_by,
p_last_update_login => l_last_update_login,
p_created_by => l_created_by,
p_creation_date => l_creation_date
);
/* l_last_update_date := sysdate;
l_last_updated_BY := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
Select psb_pay_element_options_s.nextval
INTO l_pay_element_option_id
FROM dual;
PSB_PAY_ELEMENT_OPTIONS_PVT.INSERT_ROW
( p_api_version => 1.0,
p_init_msg_list => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_pay_element_option_id => l_pay_element_option_id,
p_pay_element_id => l_pay_element_id,
p_name => l_grade_name,
p_grade_step => l_grade_step,
p_sequence_number => l_sequence,
p_last_update_date => l_last_update_date,
p_last_updated_by => l_last_updated_by,
p_last_update_login => l_last_update_login,
p_created_by => l_created_by,
p_creation_date => l_creation_date
);
Select psb_pay_element_rates_s.nextval
INTO l_pay_element_rate_id
FROM dual;
PSB_PAY_ELEMENT_RATES_PVT.INSERT_ROW
( p_api_version => 1.0,
p_init_msg_list => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_pay_element_rate_id => l_pay_element_rate_id,
p_pay_element_option_id => l_pay_element_option_id,
p_pay_element_id => l_pay_element_id,
p_effective_start_date => p_date_effective,
p_effective_END_DATE => p_date_end,
p_worksheet_id => NULL,
p_element_value_type => 'A',
p_element_value =>
fnd_number.canonical_to_number(C_Pay_Grade_Rec.value),
p_pay_basIS => l_pay_basis,
p_FORmula_id => NULL,
p_maximum_value =>
fnd_number.canonical_to_number(C_Pay_Grade_Rec.maximum),
p_mid_value =>
fnd_number.canonical_to_number(C_Pay_Grade_Rec.mid_value),
p_minimum_value =>
fnd_number.canonical_to_number(C_Pay_Grade_Rec.minimum),
p_currency_code => l_currency_code,
p_last_update_date => l_last_update_date,
p_last_updated_by => l_last_updated_by,
p_last_update_login => l_last_update_login,
p_created_by => l_created_by,
p_creation_date => l_creation_date
) ;
hr_utility.set_location(' ::: > before ASSinsert ' ,336);
PSB_POSITION_ASSIGNMENTS_PVT.INSERT_ROW
(
p_api_version => 1,
p_init_msg_list => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_position_assignment_id => l_position_assignment_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => NULL,
p_position_id => p_position_id,
p_assignment_type => 'ELEMENT',
p_attribute_id => NULL,
p_attribute_value_id => NULL,
p_attribute_value => NULL,
p_pay_element_id => l_pay_element_id,
p_pay_element_option_id => l_pay_element_option_id,
p_effective_start_date => p_date_effective,
p_effective_END_DATE => p_date_end,
p_element_value_type => 'A',
p_element_value => l_value,
p_currency_code => l_currency_code,
p_pay_basIS => l_pay_basis,
p_employee_id => NULL,
p_primary_employee_flag => NULL,
p_global_default_flag => NULL,
p_assignment_default_rule_id => NULL,
p_modify_flag => NULL,
p_mode => 'R'
);
hr_utility.set_location('fail insert assignments - stat is ' ||
l_return_status,983);
l_select_tab varchar2(30);
l_select_key varchar2(30);
l_last_update_date DATE;
l_last_updated_BY number;
l_last_update_login number;
Select attribute_id,name,definition_type,definition_structure,
definition_table, definition_column,system_attribute_type,
attribute_type_id,data_type,
nvl(value_table_flag,'N') value_table_flag
from psb_attributes_vl
where business_group_id = p_business_group_id
-- Added for Bug#2820825
and (system_attribute_type is null OR system_attribute_type <> 'HIREDATE');
Select name, select_table,
/*For Bug No : 2820825 Start*/
substr(select_table,1,instr(select_table,' ',1)-1) select_tab,
/*For Bug No : 2820825 End*/
select_column,select_key,
link_key,decode(link_type,'A','PER_ALL_ASSIGNMENTS','E',
'PER_ALL_PEOPLE','P', 'HR_ALL_POSITIONS','PER_ALL_ASSIGNMENTS')
link_type,link_type l_alias2,
select_where
From Psb_attribute_types
Where attribute_type = l_definition_type
and attribute_type_id = l_attribute_type_id;
Select job_structure
from per_business_groups
where business_group_id = p_business_group_id;
Select name
from per_jobs
where job_id = p_job_id;
Select name
from hr_all_organization_units
where organization_id = p_organization_id;
Select attribute_value_id
FROM psb_attribute_values
WHERE attribute_id = l_attribute_id
AND decode(l_definition_type, 'DFF',hr_value_id,
attribute_value) = lp_attribute_value
AND data_extract_id = p_data_extract_id;
Select application_id,id_flex_code,
application_table_name,
set_defining_column_name
from fnd_id_flexs
where id_flex_name = l_definition_structure;
SELECT fseg.application_column_name
FROM fnd_id_flex_structures_vl fstr,fnd_id_flex_segments_vl fseg
WHERE fstr.application_id = l_application_id
AND fstr.id_flex_code = l_id_flex_code
AND fstr.id_flex_structure_name = l_definition_table
AND fstr.id_flex_code = fseg.id_flex_code
AND fstr.id_flex_num = fseg.id_flex_num
AND fseg.segment_name = l_definition_column
AND fstr.application_id = fseg.application_id; -- bug #4924031
Select application_id,application_table_name,
context_column_name
from fnd_descriptive_flexs_vl
where descriptive_flexfield_name = l_definition_structure;
Select fcol.application_column_name
from fnd_descr_flex_contexts_vl fcon,fnd_descr_flex_column_usages fcol
where fcon.application_id = fcol.application_id
and fcon.descriptive_flexfield_name = l_definition_structure
and fcon.descriptive_flex_context_code = l_definition_table
and fcon.descriptive_flexfield_name = fcol.descriptive_flexfield_name
and fcon.descriptive_flex_context_code = fcol.descriptive_flex_context_code
and fcol.end_user_column_name = l_definition_column;
Select lookup_type
from per_common_lookup_types_v
where lookup_type_meaning = l_definition_table;
l_select_tab := NULL;
l_select_key := NULL;
l_sql_stmt := 'Select '||l_application_column_name||
' From Per_jobs,per_job_definitions '||
' Where per_jobs.job_id = '||p_job_id||
' and per_jobs.job_definition_id = '||
' per_job_definitions.job_definition_id';
l_select_tab := 'PQH_POSITION_TRANSACTIONS';
l_select_key := 'POSITION_TRANSACTION_ID';
l_select_tab := 'HR_ALL_POSITIONS';
l_select_key := 'POSITION_ID';
l_sql_stmt := 'Select '||l_application_column_name||
' From '||l_select_tab||','||'per_position_definitions '||
' Where '||l_select_tab||'.'||l_select_key||' = '||
' :v_param_value and '||
l_select_tab||'.'||'position_definition_id = '||
' per_position_definitions.position_definition_id';
l_sql_stmt := 'Select '||l_application_column_name||
' From Per_grades,per_grade_definitions '||
' Where per_grades.grade_id = '||p_entry_grade_id||
' and per_grades.grade_definition_id = '||
' per_grade_definitions.grade_definition_id';
and (Attr_Type_Rec.select_tab = 'HR_ALL_POSITIONS')) then
l_valid_attribute := 'Y' ;
l_select_tab := 'PQH_POSITION_TRANSACTIONS';
l_select_key := 'POSITION_TRANSACTION_ID';
l_select_tab := 'HR_ALL_POSITIONS';
l_select_key := 'POSITION_ID';
Select ltrim(rtrim(substr(Attr_type_rec.select_table,
instr(Attr_type_rec.select_table,' ',1),
length(Attr_type_rec.select_table)
- instr(Attr_type_rec.select_table,' ',1) + 1)))
into l_alias1
from dual;
d_sql_stmt := 'Select '||l_alias1||'.'
||l_application_column_name||
' From '||l_select_tab||' '||
l_alias1||' , '||
' Where '||l_alias1||'.'||
l_select_key||' = :v_param_value';
if (Attr_type_rec.select_where is not null) then
d_sql_stmt := d_sql_stmt||' and '||Attr_type_rec.select_where;
(Attr_Type_Rec.select_tab = 'HR_ALL_POSITIONS')) then
l_valid_attribute := 'Y' ;
l_select_tab := 'PQH_POSITION_TRANSACTIONS';
l_select_key := 'POSITION_TRANSACTION_ID';
l_select_tab := 'HR_ALL_POSITIONS';
l_select_key := 'POSITION_ID';
Select ltrim(rtrim(substr(Attr_type_rec.select_table,
instr(Attr_type_rec.select_table,' ',1),
length(Attr_type_rec.select_table)
- instr(Attr_type_rec.select_table,' ',1) + 1)))
into l_alias1
from dual;
q_sql_stmt := 'Select a.meaning '||
' From Fnd_Common_lookups a , '||
l_select_tab||' '||l_alias1||
' Where a.lookup_type = '||''''||
l_lookup_type||''''||
' and a.lookup_code = '||
l_alias1||'.'||Attr_type_rec.select_column||
' and '||l_alias1||'.'||l_select_key||
' = :v_param_value';
if (Attr_type_rec.select_where is not null) then
q_sql_stmt := q_sql_stmt||' and '||Attr_type_rec.select_where;
and (Attr_Type_Rec.select_tab = 'HR_ALL_POSITIONS')) then
l_valid_attribute := 'Y' ;
l_select_tab := 'PQH_POSITION_TRANSACTIONS';
l_select_key := 'POSITION_TRANSACTION_ID';
l_select_tab := 'HR_ALL_POSITIONS';
l_select_key := 'POSITION_ID';
Select ltrim(rtrim(substr(Attr_type_rec.select_table,
instr(Attr_type_rec.select_table,' ',1),
length(Attr_type_rec.select_table)
- instr(Attr_type_rec.select_table,' ',1) + 1))) into l_alias1
from dual;
o_sql_stmt := 'Select '||
Attr_type_rec.select_column||
' From '||l_select_tab||' '||l_alias1||
' Where '||l_alias1||'.'||l_select_key||
' = :v_param_value';
if (Attr_type_rec.select_where is not null) then
o_sql_stmt := o_sql_stmt||' and '||Attr_type_rec.select_where;
l_last_update_date := sysdate;
l_last_updated_BY := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
-- Insert the new value in PSB_ATTRIBUTE_VALUES
select psb_attribute_values_s.nextval into
l_attribute_value_id from dual;
PSB_ATTRIBUTE_VALUES_PVT.INSERT_ROW
( p_api_version => 1.0,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_attribute_value_id => l_attribute_value_id,
p_attribute_id => l_attribute_id,
p_attribute_value => lp_attribute_value,
p_hr_value_id => NULL,
p_description => NULL,
p_data_extract_id => p_data_extract_id,
p_context => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_attribute21 => NULL,
p_attribute22 => NULL,
p_attribute23 => NULL,
p_attribute24 => NULL,
p_attribute25 => NULL,
p_attribute26 => NULL,
p_attribute27 => NULL,
p_attribute28 => NULL,
p_attribute29 => NULL,
p_attribute30 => NULL,
p_last_upDATE_DATE => l_last_update_date,
p_last_upDATEd_BY => l_last_updated_BY,
p_last_upDATE_login => l_last_update_login,
p_created_BY => l_created_by,
p_creation_DATE => l_creation_date
) ;
PSB_POSITION_ASSIGNMENTS_PVT.INSERT_ROW
(
p_api_version => 1,
p_init_msg_list => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_position_assignment_id => l_position_assignment_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => NULL,
p_position_id => p_new_position_id,
p_assignment_type => 'ATTRIBUTE',
p_attribute_id => l_attribute_id,
p_attribute_value_id => l_attribute_value_id,
p_attribute_value => l_attribute_value,
p_pay_element_id => NULL,
p_pay_element_option_id => NULL,
p_effective_start_date => p_date_effective,
p_effective_end_date => p_date_end,
p_element_value_type => NULL,
p_element_value => NULL,
p_currency_code => NULL,
p_pay_basis => NULL,
p_employee_id => NULL,
p_primary_employee_flag => NULL,
p_global_default_flag => NULL,
p_assignment_default_rule_id => NULL,
p_modify_flag => NULL,
p_mode => 'R'
) ;
| PROCEDURE Insert_Position_Txn_Info |
+===========================================================================*/
PROCEDURE Insert_Position_Txn_Info
(
p_position_transaction_id in number ,
p_action_date in date ,
p_position_id in number ,
p_availability_status_id in number ,
p_business_group_id in number ,
p_entry_step_id in number ,
p_entry_grade_rule_id in number ,
p_job_id in number ,
p_location_id in number ,
p_organization_id in number ,
p_pay_freq_payroll_id in number ,
p_position_definition_id in number ,
p_prior_position_id in number ,
p_relief_position_id in number ,
p_entry_grade_id in number ,
p_successor_position_id in number ,
p_supervisor_position_id in number ,
p_amendment_date in date ,
p_amendment_recommendation in varchar2 ,
p_amendment_ref_number in varchar2 ,
p_avail_status_prop_end_date in date ,
p_bargaining_unit_cd in varchar2 ,
p_comments in long ,
p_country1 in varchar2 ,
p_country2 in varchar2 ,
p_country3 in varchar2 ,
p_current_job_prop_end_date in date ,
p_current_org_prop_end_date in date ,
p_date_effective in date ,
p_date_end in date ,
p_earliest_hire_date in date ,
p_fill_by_date in date ,
p_frequency in varchar2 ,
p_fte in number ,
p_location1 in varchar2 ,
p_location2 in varchar2 ,
p_location3 in varchar2 ,
p_max_persons in number ,
p_name in varchar2 ,
p_other_requirements in varchar2 ,
p_overlap_period in number ,
p_overlap_unit_cd in varchar2 ,
p_passport_required in varchar2 ,
p_pay_term_end_day_cd in varchar2 ,
p_pay_term_end_month_cd in varchar2 ,
p_permanent_temporary_flag in varchar2 ,
p_permit_recruitment_flag in varchar2 ,
p_position_type in varchar2 ,
p_posting_description in varchar2 ,
p_probation_period in number ,
p_probation_period_unit_cd in varchar2 ,
p_relocate_domestically in varchar2 ,
p_relocate_internationally in varchar2 ,
p_replacement_required_flag in varchar2 ,
p_review_flag in varchar2 ,
p_seasonal_flag in varchar2 ,
p_security_requirements in varchar2 ,
p_service_minimum in varchar2 ,
p_term_start_day_cd in varchar2 ,
p_term_start_month_cd in varchar2 ,
p_time_normal_finish in varchar2 ,
p_time_normal_start in varchar2 ,
p_transaction_status in varchar2 ,
p_travel_required in varchar2 ,
p_working_hours in number ,
p_works_council_approval_flag in varchar2 ,
p_work_any_country in varchar2 ,
p_work_any_location in varchar2 ,
p_work_period_type_cd in varchar2 ,
p_work_schedule in varchar2 ,
p_work_term_end_day_cd in varchar2 ,
p_work_term_end_month_cd in varchar2 ,
p_proposed_fte_for_layoff in number,
p_proposed_date_for_layoff in date,
p_information1 in varchar2 ,
p_information2 in varchar2 ,
p_information3 in varchar2 ,
p_information4 in varchar2 ,
p_information5 in varchar2 ,
p_information6 in varchar2 ,
p_information7 in varchar2 ,
p_information8 in varchar2 ,
p_information9 in varchar2 ,
p_information10 in varchar2 ,
p_information11 in varchar2 ,
p_information12 in varchar2 ,
p_information13 in varchar2 ,
p_information14 in varchar2 ,
p_information15 in varchar2 ,
p_information16 in varchar2 ,
p_information17 in varchar2 ,
p_information18 in varchar2 ,
p_information19 in varchar2 ,
p_information20 in varchar2 ,
p_information21 in varchar2 ,
p_information22 in varchar2 ,
p_information23 in varchar2 ,
p_information24 in varchar2 ,
p_information25 in varchar2 ,
p_information26 in varchar2 ,
p_information27 in varchar2 ,
p_information28 in varchar2 ,
p_information29 in varchar2 ,
p_information30 in varchar2 ,
p_information_category in varchar2 ,
p_attribute1 in varchar2 ,
p_attribute2 in varchar2 ,
p_attribute3 in varchar2 ,
p_attribute4 in varchar2 ,
p_attribute5 in varchar2 ,
p_attribute6 in varchar2 ,
p_attribute7 in varchar2 ,
p_attribute8 in varchar2 ,
p_attribute9 in varchar2 ,
p_attribute10 in varchar2 ,
p_attribute11 in varchar2 ,
p_attribute12 in varchar2 ,
p_attribute13 in varchar2 ,
p_attribute14 in varchar2 ,
p_attribute15 in varchar2 ,
p_attribute16 in varchar2 ,
p_attribute17 in varchar2 ,
p_attribute18 in varchar2 ,
p_attribute19 in varchar2 ,
p_attribute20 in varchar2 ,
p_attribute21 in varchar2 ,
p_attribute22 in varchar2 ,
p_attribute23 in varchar2 ,
p_attribute24 in varchar2 ,
p_attribute25 in varchar2 ,
p_attribute26 in varchar2 ,
p_attribute27 in varchar2 ,
p_attribute28 in varchar2 ,
p_attribute29 in varchar2 ,
p_attribute30 in varchar2 ,
p_attribute_category in varchar2 ,
p_object_version_number in number ,
p_effective_date in date ,
p_pay_basis_id in number ,
p_supervisor_id in number
)
IS
--
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Position_Txn_Info';
SELECT system_type_cd
/*For Bug No : 1527423 End*/
FROM per_shared_types
WHERE lookup_type = 'POSITION_AVAILABILITY_STATUS'
AND shared_type_id = p_availability_status_id;
SELECT business_group_id,
set_of_books_id,
position_id_flex_num,
req_data_as_of_date
FROM psb_data_extracts
WHERE data_extract_id = l_data_extract_id;
select position_structure
from per_business_groups
where business_group_id = p_business_group_id;
select application_column_name
from fnd_id_flex_segments_vl
where id_flex_code = 'POS'
and id_flex_num = l_pos_id_flex_num
and enabled_flag = 'Y'
order by segment_num;
hr_utility.set_location('>> insert position trans',777);
Select application_column_name
FROM fnd_id_flex_segments_vl
WHERE id_flex_code = 'BPS'
AND id_flex_num = l_position_id_flex_num
AND enabled_flag = 'Y'
ORDER BY segment_num
)
LOOP
l_pos_index := l_pos_index + 1;
SELECT psb_positions_s.nextval INTO l_position_id
FROM dual;
PSB_POSITIONS_PVT.INSERT_ROW
(
p_api_version => 1.0,
p_init_msg_list => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_position_id => l_position_id,
-- de by org
p_organization_id => p_organization_id,
p_data_extract_id => l_data_extract_id,
p_position_definition_id => l_ccid,
p_hr_position_id => p_position_id,
p_hr_employee_id => NULL,
p_business_group_id => p_business_group_id,
p_effective_start_date => p_date_effective,
p_effective_end_date => l_date_end,
p_set_of_books_id => l_set_of_books_id,
p_vacant_position_flag => 'Y',
p_availability_status => l_availability_status,
p_transaction_id => p_position_transaction_id,
p_transaction_status => p_transaction_status,
p_new_position_flag => 'Y',
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_attribute_category => NULL,
p_name => l_concat_pos_name,
p_mode => 'R'
);
FND_MESSAGE.SET_NAME('PSB', 'PSB_PQH_INSERT_FAILURE');
Update_Worksheet_Values ( p_return_status => l_return_status,
p_position_id => l_position_id,
p_org_id => p_organization_id
) ;
hr_utility.set_location(' fail to update ws value ',888);
hr_utility.set_location('>> END insert position trans',777);
END Insert_Position_Txn_Info;
| PROCEDURE Update_Position_Txn_Info |
+===========================================================================*/
PROCEDURE Update_Position_Txn_Info
(p_position_transaction_id in NUMBER,
p_action_date in DATE ,
p_position_id in NUMBER,
p_availability_status_id in NUMBER,
p_business_group_id in NUMBER,
p_entry_step_id in NUMBER,
p_entry_grade_rule_id in NUMBER,
p_job_id in NUMBER,
p_location_id in NUMBER,
p_organization_id in NUMBER,
p_pay_freq_payroll_id in NUMBER,
p_position_definition_id in NUMBER,
p_entry_grade_id in NUMBER,
p_bargaining_unit_cd in VARCHAR2,
p_date_effective in DATE,
p_date_end in DATE,
p_earliest_hire_date in DATE,
p_frequency in VARCHAR2,
p_fte in NUMBER,
p_name in VARCHAR2,
p_position_type in VARCHAR2,
p_transaction_status in VARCHAR2,
p_working_hours in NUMBER,
p_pay_basis_id_o in NUMBER,
p_object_version_number in NUMBER,
p_effective_date in DATE
)
IS
--
l_api_name CONSTANT VARCHAR2(30) := 'Update_Position_Txn_Info' ;
Select 'Position Transaction Exists in PSB'
from dual
where exists
(Select 1
from psb_positions
where transaction_id = p_position_transaction_id);
Select *
from psb_positions
where transaction_id = p_position_transaction_id;
select system_type_cd
/*For Bug No : 1527423 End*/
from per_shared_types
where lookup_type = 'POSITION_AVAILABILITY_STATUS'
and shared_type_id = p_availability_status_id;
SELECT req_data_as_of_date,
set_of_books_id,
position_id_flex_num,
business_group_id
FROM psb_data_extracts
WHERE data_extract_id = l_data_extract_id;
select wpl.position_line_id, wlp.worksheet_id
from psb_ws_position_lines wpl,
psb_ws_lines_positions wlp,
psb_worksheets ws
where wpl.position_id = l_psb_position_id
and wlp.position_line_id = wpl.position_line_id
and ws.worksheet_id = wlp.worksheet_id
and ws.global_worksheet_flag = 'Y';
Select brp.budget_revision_pos_line_id, brpl.budget_revision_id
from psb_budget_revision_positions brp, psb_budget_revision_pos_lines brpl
where brp.position_id = l_psb_position_id
and brpl.budget_revision_pos_line_id = brp.budget_revision_pos_line_id;
select position_structure
from per_business_groups
where business_group_id = p_business_group_id;
select application_column_name
from fnd_id_flex_segments_vl
where id_flex_code = 'POS'
and id_flex_num = l_pos_id_flex_num
and enabled_flag = 'Y'
order by segment_num;
hr_utility.set_location('>> update position trans',777);
Select application_column_name
FROM fnd_id_flex_segments_vl
WHERE id_flex_code = 'BPS'
AND id_flex_num = l_position_id_flex_num
AND enabled_flag = 'Y'
ORDER BY segment_num
)
LOOP
l_pos_index := l_pos_index + 1;
PSB_POSITIONS_PVT.UPDATE_ROW
(
p_api_version => 1.0,
p_init_msg_lISt => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_id => l_psb_position_id,
-- de by org
p_organization_id => l_organization_id,
p_data_extract_id => c_pos_trx_rec.data_extract_id,
p_position_definition_id => l_ccid,
p_hr_position_id => p_position_id,
p_hr_employee_id => c_pos_trx_rec.hr_employee_id,
p_business_group_id => p_business_group_id,
p_effective_start_DATE => p_effective_date,
p_effective_END_DATE => p_date_end,
p_set_of_books_id => c_pos_trx_rec.set_of_books_id,
p_vacant_position_flag => c_pos_trx_rec.vacant_position_flag,
p_availability_status => l_availability_status,
p_transaction_id => p_position_transaction_id,
p_transaction_status => p_transaction_status,
p_new_position_flag => c_pos_trx_rec.new_position_flag ,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_attribute_category => NULL,
p_name => l_concat_pos_name,
p_mode => 'R'
);
FND_MESSAGE.SET_NAME('PSB', 'PSB_PQH_UPDATE_FAILURE' );
Delete psb_position_assignments
where position_id = l_psb_position_id;
PSB_WORKSHEET.Delete_WPL
(p_api_version => 1.0,
p_return_status => l_return_status,
p_worksheet_id => c_ws_positions_rec.worksheet_id,
p_position_line_id => c_ws_positions_rec.position_line_id);
PSB_BUDGET_REVISIONS_PVT.Delete_Revision_Positions
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_budget_revision_id => c_br_positions_rec.budget_revision_id,
p_budget_revision_pos_line_id => c_br_positions_rec.budget_revision_pos_line_id);
delete from psb_position_assignments
where position_id = l_psb_position_id;
PSB_POSITIONS_PVT.DELETE_ROW
(
p_api_version => 1.0,
p_init_msg_liSt => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_id => l_psb_position_id);
hr_utility.set_location('>> pos delete row Error ' ,1777);
END Update_Position_Txn_Info;
| PROCEDURE Insert_Position_Info |
+===========================================================================*/
PROCEDURE Insert_Position_Info
(p_position_id in NUMBER ,
p_effective_start_date in DATE ,
p_effective_end_date in DATE ,
p_availability_status_id in NUMBER ,
p_business_group_id in NUMBER ,
p_entry_step_id in NUMBER ,
p_entry_grade_rule_id in NUMBER ,
p_job_id in NUMBER ,
p_location_id in NUMBER ,
p_organization_id in NUMBER ,
p_position_definition_id in NUMBER ,
p_position_transaction_id in NUMBER ,
p_entry_grade_id in NUMBER ,
p_bargaining_unit_cd in VARCHAR2 ,
p_date_effective in DATE ,
p_date_end in DATE ,
p_earliest_hire_date in DATE ,
p_fill_by_date in DATE ,
p_frequency in VARCHAR2 ,
p_working_hours in NUMBER ,
p_fte in NUMBER ,
p_name in VARCHAR2 ,
p_position_type in VARCHAR2 ,
p_pay_basis_id in NUMBER ,
p_object_version_number in NUMBER
)
IS
--
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Position_Info';
SELECT system_type_cd
/*For Bug No : 1527423 End*/
FROM per_shared_types
WHERE lookup_type = 'POSITION_AVAILABILITY_STATUS'
AND shared_type_id = p_availability_status_id;
SELECT business_group_id,
set_of_books_id,
position_id_flex_num,
req_data_as_of_date
FROM psb_data_extracts
WHERE data_extract_id = l_data_extract_id;
select position_structure
from per_business_groups
where business_group_id = p_business_group_id;
select application_column_name
from fnd_id_flex_segments_vl
where id_flex_code = 'POS'
and id_flex_num = l_pos_id_flex_num
and enabled_flag = 'Y'
order by segment_num;
hr_utility.set_location(' Start Insert Position',777);
Select application_column_name
FROM fnd_id_flex_segments_vl
WHERE id_flex_code = 'BPS'
AND id_flex_num = l_position_id_flex_num
AND enabled_flag = 'Y'
ORDER BY segment_num
)
LOOP
l_pos_index := l_pos_index + 1;
select psb_positions_s.nextval INTO l_position_id
from dual;
PSB_POSITIONS_PVT.INSERT_ROW
(
p_api_version => 1.0,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_position_id => l_position_id,
-- de by org
p_organization_id => p_organization_id,
p_data_extract_id => l_data_extract_id,
p_position_definition_id => l_ccid,
p_hr_position_id => p_position_id,
p_hr_employee_id => NULL,
p_business_group_id => p_business_group_id,
p_effective_start_DATE => p_date_effective,
p_effective_END_DATE => l_date_end,
p_set_of_books_id => l_set_of_books_id,
p_vacant_position_flag => 'Y',
p_availability_status => l_availability_status,
p_transaction_id => null,
p_transaction_status => 'SUCCESS',
p_new_position_flag => 'Y',
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_attribute_category => NULL,
p_name => l_concat_pos_name,
p_mode => 'R'
);
hr_utility.set_location('error in insert position',9877);
FND_MESSAGE.SET_NAME('PSB', 'PSB_POS_INSERT_FAILURE' );
Update_Worksheet_Values ( p_return_status => l_return_status,
p_position_id => l_position_id,
p_org_id => p_organization_id);
hr_utility.set_location(' fail to update ws value ',888);
hr_utility.set_location(' Insert Position Info Success',555);
END Insert_Position_Info;
| PROCEDURE Update_Position_Info |
+===========================================================================*/
PROCEDURE Update_Position_Info
(p_position_id in NUMBER ,
p_effective_start_date in DATE ,
p_effective_end_date in DATE ,
p_availability_status_id in NUMBER ,
p_business_group_id_o in NUMBER ,
p_entry_step_id in NUMBER ,
p_entry_grade_rule_id in NUMBER ,
p_job_id_o in NUMBER ,
p_location_id in NUMBER ,
p_organization_id_o in NUMBER ,
p_position_definition_id in NUMBER ,
p_position_transaction_id in NUMBER ,
p_entry_grade_id in NUMBER ,
p_bargaining_unit_cd in VARCHAR2 ,
p_date_effective in DATE ,
p_date_end in DATE ,
p_earliest_hire_date in DATE ,
p_fill_by_date in DATE ,
p_frequency in VARCHAR2 ,
p_working_hours in NUMBER,
p_fte in NUMBER ,
p_name in VARCHAR2 ,
p_position_type in VARCHAR2 ,
p_pay_basis_id in NUMBER ,
p_object_version_number in NUMBER ,
p_effective_date in DATE
)
IS
--
l_api_name CONSTANT VARCHAR2(30) := 'Update_Position_Info' ;
Select *
from psb_positions
where hr_position_id = p_position_id
and data_extract_id = l_data_extract_id;
select system_type_cd
/*For Bug No : 1527423 End*/
from per_shared_types
where lookup_type = 'POSITION_AVAILABILITY_STATUS'
and shared_type_id = p_availability_status_id;
SELECT business_group_id,
set_of_books_id,
position_id_flex_num,
req_data_as_of_date
FROM psb_data_extracts
WHERE data_extract_id = l_data_extract_id;
select position_structure
from per_business_groups
where business_group_id = p_business_group_id_o;
select application_column_name
from fnd_id_flex_segments_vl
where id_flex_code = 'POS'
and id_flex_num = l_pos_id_flex_num
and enabled_flag = 'Y'
order by segment_num;
hr_utility.set_location(' **** in update position info',777);
Select application_column_name
FROM fnd_id_flex_segments_vl
WHERE id_flex_code = 'BPS'
AND id_flex_num = l_position_id_flex_num
AND enabled_flag = 'Y'
ORDER BY segment_num
)
LOOP
l_pos_index := l_pos_index + 1;
hr_utility.set_location(' before insert ' ,881);
hr_utility.set_location(' before update position', 1887);
PSB_POSITIONS_PVT.UPDATE_ROW
(
p_api_version => 1.0,
p_init_msg_lISt => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_id => l_psb_position_id,
-- de by org
p_organization_id => p_organization_id_o,
p_data_extract_id => l_data_extract_id,
p_position_definition_id => l_ccid,
p_hr_position_id => p_position_id,
p_hr_employee_id => c_pos_trx_rec.hr_employee_id,
p_business_group_id => p_business_group_id_o,
p_effective_start_DATE => p_effective_date,
p_effective_END_DATE => p_date_end,
p_set_of_books_id => c_pos_trx_rec.set_of_books_id,
p_vacant_position_flag => c_pos_trx_rec.vacant_position_flag,
p_availability_status => l_availability_status,
p_transaction_id => c_pos_trx_rec.transaction_id,
p_transaction_status => c_pos_trx_rec.transaction_status,
p_new_position_flag => c_pos_trx_rec.new_position_flag ,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_attribute_category => NULL,
p_name => l_concat_pos_name,
p_mode => 'R'
);
hr_utility.set_location(' after update position', 1887);
hr_utility.set_location(' posb fail after update position', 1887);
FND_MESSAGE.SET_NAME('PSB', 'PSB_POS_UPDATE_FAILURE' );
Delete psb_position_assignments
where position_id = l_psb_position_id;
Update_Worksheet_Values ( p_return_status => l_return_status,
p_position_id => l_psb_position_id,
p_org_id => p_organization_id_o);
hr_utility.set_location(' fail to update ws value ',888);
END Update_Position_Info;
PROCEDURE Update_Worksheet_Values
( p_return_status OUT NOCOPY VARCHAR2,
p_position_id in NUMBER ,
p_org_id in NUMBER)
AS
l_position_line_id number;
select budget_group_id
from psb_budget_groups
where (organization_id = l_in_org_id or
business_group_id = l_in_org_id )
start with budget_group_id = l_in_bg_id
connect by prior budget_group_id = parent_budget_group_id;
hr_utility.set_location(' Inside update ws values',333);
PSB_WS_POSITION_CR_LINES_I_PVT.Insert_Row
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_worksheet_id => PSB_HR_POPULATE_DATA_PVT.get_global('G_PSB_WORKSHEET_ID'),
p_position_id => p_position_id,
p_budget_group_id => l_org_budget_group_id,
p_position_line_id => l_position_line_id);
hr_utility.set_location(' fail to insert row ',888);
FND_MESSAGE.SET_NAME('PSB', 'PSB_INCR_INSERT_POS_WS_FAIL');
FND_MESSAGE.SET_NAME('PSB', 'PSB_INCR_INSERT_POS_REV_FAIL');
SELECT a.budget_group_id, b.root_budget_group
FROM psb_budget_revisions a,
psb_budget_groups b
WHERE a.budget_revision_id = l_budget_revision_id
AND a.budget_group_id = b.budget_group_id) lOOP
l_budget_group_id := l_bud_group_csr.budget_group_id;
UPDATE psb_positions
SET budget_group_id = l_org_budget_group_id
WHERE position_id = p_position_id;
UPDATE psb_positions
SET budget_group_id = l_budget_group_id
WHERE position_id = p_position_id;
END Update_Worksheet_Values;
SELECT PP.position_id,
-- de by org
PP.organization_id,
PP.hr_employee_id,
PP.effective_start_DATE,
PP.effective_END_DATE
FROM PSB_POSITIONS PP,
PSB_POSITIONS_I PPI
WHERE PP.data_extract_id = p_data_extract_id
AND PP.data_extract_id = PPI.data_extract_id
AND PP.position_id > p_restart_position_id
AND PP.hr_position_id = PPI.hr_position_id
AND PP.hr_employee_id = PPI.hr_employee_id
AND ( PP.vacant_position_flag IS NULL OR PP.vacant_position_flag = 'N' )
ORDER BY position_id;
PSB_HR_EXTRACT_DATA_PVT.Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'PSB Costing',
p_restart_id => c_Positions_Rec.position_id
);
PSB_HR_EXTRACT_DATA_PVT.Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'PSB Costing',
p_restart_id => l_fin_position_id
);
SELECT a.costing_level,
a.proportion,
a.chart_of_accounts_id,
a.project_id,
a.task_id,
a.award_id,
a.expenditure_type,
a.expenditure_organization_id,
a.description,
a.cost_allocation_keyflex_id,
a.segment1, a.segment2, a.segment3, a.segment4, a.segment5,
a.segment6, a.segment7, a.segment8, a.segment9, a.segment10,
a.segment11, a.segment12, a.segment13, a.segment14, a.segment15,
a.segment16, a.segment17, a.segment18, a.segment19, a.segment20,
a.segment21, a.segment22, a.segment23, a.segment24, a.segment25,
a.segment26, a.segment27, a.segment28, a.segment29, a.segment30,
a.effective_start_date
FROM psb_cost_distributions_i a,
psb_employees_i b,
psb_employees be,
psb_positions c,
psb_position_assignments d
WHERE a.assignment_id = b.assignment_id
AND a.data_extract_id = p_data_extract_id
AND b.hr_position_id = c.hr_position_id
AND b.hr_employee_id = be.hr_employee_id
AND b.data_extract_id = p_data_extract_id
AND be.data_extract_id = p_data_extract_id
AND c.data_extract_id = p_data_extract_id
AND c.position_id = p_position_id
AND c.position_id = d.position_id
AND be.employee_id = d.employee_id
AND d.primary_employee_flag = 'Y'
AND d.assignment_type = 'EMPLOYEE'
ORDER BY a.chart_of_accounts_id;
lh_distribution.delete;
l_last_update_date DATE;
l_last_updated_by number;
l_last_update_login number;
l_update_flag varchar2(1):='N';
Select rate_or_payscale_name,grade_name
FROM psb_salary_i
WHERE rate_or_payscale_id = l_rate_or_payscale_id
AND grade_id = l_grade_id
AND grade_step = l_grade_step
AND sequence_number = l_sequence_number
AND data_extract_id = p_data_extract_id;
Select rate_or_payscale_name,grade_name
FROM psb_salary_i
WHERE rate_or_payscale_id = l_rate_or_payscale_id
AND grade_id = l_grade_id
AND data_extract_id = p_data_extract_id;
Select ppe.pay_element_id ,ppe.salary_type,
ppo.pay_element_option_id
FROM psb_pay_elements ppe,
psb_pay_element_options ppo
WHERE ppe.data_extract_id = p_data_extract_id
AND ppe.salary_type = 'STEP'
AND ppe.name = l_rate_or_payscale_name
AND ppe.pay_element_id = ppo.pay_element_id
AND ppo.name = l_grade_name
AND ppo.grade_step = l_grade_step
AND ppo.sequence_number = l_sequence_number;
Select ppe.pay_element_id ,ppe.salary_type,
ppo.pay_element_option_id
FROM psb_pay_elements ppe,
psb_pay_element_options ppo
WHERE ppe.data_extract_id = p_data_extract_id
AND ppe.salary_type = 'RATE'
AND ppe.name = l_rate_or_payscale_name
AND ppe.pay_element_id = ppo.pay_element_id
AND ppo.name = l_grade_name;
Select pay_element_rate_id,
effective_start_date,
effective_end_date,
element_value,
currency_code
FROM psb_pay_element_rates
WHERE pay_element_id = l_pay_element_id
AND pay_element_option_id = l_pay_element_option_id;
SELECT pas.position_assignment_id
FROM psb_position_assignments pas, -- bug #4886277: Used the table
-- instead of View.
psb_pay_elements pe
WHERE pas.pay_element_id = pe.pay_element_id (+)
AND pas.assignment_type = 'ELEMENT'
AND pas.data_extract_id = p_data_extract_id
AND pas.position_id = vc_position_id
AND NVL(pe.salary_flag, 'Y') = 'Y'
ORDER BY pe.PAY_ELEMENT_ID;
SELECT * FROM psb_positions_i
WHERE data_extract_id = p_data_extract_id
AND hr_position_id > l_restart_hr_position_id
ORDER BY hr_position_id,
hr_employee_id;
update_insert_flag VARCHAR2(1),
position_id NUMBER
);
update_insert_flag VARCHAR2(1),
organization_id NUMBER,
effective_start_date DATE,
effective_end_date DATE,
position_definition_id NUMBER,
transaction_id NUMBER,
transaction_status VARCHAR2(30),
name VARCHAR2(240)
);
l_last_update_date := sysdate;
l_last_updated_BY := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
l_position_cache.delete;
Select hr_position_id,
hr_employee_id,
position_id,
organization_id,
effective_start_date,
effective_end_date,
position_definition_id,
transaction_id,
transaction_status,
name
FROM psb_positions
WHERE data_extract_id = p_data_extract_id
AND hr_position_id = l_last_hr_position_id
ORDER BY hr_position_id,
hr_employee_id
)
LOOP
l_target_cache_count := nvl(l_target_cache_count,0) + 1;
AND l_position_cache(j).update_insert_flag IS NULL
AND l_position_i_cache(i).update_insert_flag IS NULL THEN
l_position_cache(j).update_insert_flag := 'U';
l_position_i_cache(i).update_insert_flag := 'U';
AND l_position_cache(j).update_insert_flag IS NULL
AND l_position_cache(j).position_id IS NOT NULL
AND l_position_i_cache(i).update_insert_flag IS NULL THEN
l_position_cache(j).update_insert_flag := 'U';
l_position_i_cache(i).update_insert_flag := 'U';
AND l_position_cache(j).update_insert_flag IS NULL
AND l_position_cache(j).position_id IS NOT NULL
AND l_position_i_cache(i).update_insert_flag IS NULL THEN
l_position_cache(j).update_insert_flag := 'U';
l_position_i_cache(i).update_insert_flag := 'U';
AND l_position_cache(j).update_insert_flag IS NULL
AND l_position_cache(j).position_id IS NOT NULL
AND l_position_i_cache(i).update_insert_flag IS NULL THEN
l_position_cache(j).update_insert_flag := 'U';
l_position_i_cache(i).update_insert_flag := 'U';
AND l_position_i_cache(i).update_insert_flag IS NULL THEN
l_position_i_cache(i).update_insert_flag := 'I';
still have update_insert_flag as null for the data in PSB,
then these are terminated records and we need to update the employee id
as null in PSB. Note that for shared positions, we would have created multiple
position records in PSB for the same position in HR,
if there are multiple assignments in HR. This scenario is only applicable during
REFRESH, but we do not need any additional condition to prevent, this for CREATE */
FOR j in 1..l_position_cache.count
LOOP
IF l_position_cache(j).update_insert_flag IS NULL THEN
-- Only thing we update here is the employee_id to NULL,
--rest of the data remains same.
PSB_POSITIONS_PVT.UPDATE_ROW
(
p_api_version => 1.0,
p_init_msg_lISt => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_id => l_position_cache(j).position_id,
-- de by org
p_organization_id => l_position_cache(j).organization_id,
p_data_extract_id => p_data_extract_id,
p_position_definition_id => l_position_cache(j).position_definition_id,
p_hr_position_id => l_position_cache(j).hr_position_id,
p_hr_employee_id => NULL,
p_business_group_id => p_business_group_id,
p_effective_start_date => l_position_cache(j).effective_start_date,
p_effective_end_date => l_position_cache(j).effective_end_date,
p_set_of_books_id => p_set_of_books_id,
p_vacant_position_flag => 'Y',
p_transaction_id => l_position_cache(j).transaction_id,
p_transaction_status => l_position_cache(j).transaction_status,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_attribute_category => NULL,
p_name => l_position_cache(j).name,
p_mode => 'R'
) ;
/* Do Update_Reentry only at the time of the first failure
with the last successful position or when the record
count reaches g_checkpoint_save with no failed positions */
if ((l_hr_position_ctr = PSB_WS_ACCT1.g_checkpoint_save) AND (l_status_flag <> 'I'))
OR
(l_last_success_flag = 'N')
then
PSB_HR_EXTRACT_DATA_PVT.Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'PSB Positions',
p_restart_id => prev_hr_position_id
) ;
Select application_column_name
FROM fnd_id_flex_segments_vl
WHERE id_flex_code = 'BPS'
AND id_flex_num = l_id_flex_num
AND enabled_flag = 'Y'
ORDER BY segment_num
)
LOOP
l_init_index := l_init_index + 1;
INSERT INTO PSB_ERROR_MESSAGES (
CONCURRENT_REQUEST_ID,
PROCESS_ID,
SOURCE_PROCESS,
SEQUENCE_NUMBER,
DESCRIPTION,
CREATION_DATE,
CREATED_BY)
VALUES (
-4712, -4712, 'XXX', null, l_msg, SYSDATE, -1);
IF (l_extract_method = 'REFRESH') or (l_update_flag = 'Y') THEN
-- Variable to determine whether 'REFRESH' occurred or NOT.
l_dummy := 0;
SELECT position_id,organization_id
FROM psb_positions
WHERE hr_position_id = position_rec.hr_position_id
AND ( NVL(hr_employee_id,-1)=NVL(position_rec.hr_employee_id,-1)
OR
( hr_employee_id IS NULL
AND
NOT EXISTS
( SELECT 1
FROM psb_positions
WHERE hr_position_id = position_rec.hr_position_id
AND hr_employee_id = position_rec.hr_employee_id
AND data_extract_id = p_data_extract_id
)
)
OR
( hr_employee_id IS NOT NULL
AND
NOT EXISTS
( SELECT 1
FROM psb_positions
WHERE hr_position_id = position_rec.hr_position_id
AND hr_employee_id IS NULL
AND data_extract_id = p_data_extract_id
)
)
)
AND data_extract_id = p_data_extract_id
AND business_group_id = p_business_group_id
)
LOOP
l_dummy := 1;
IF l_position_i_cache(i).update_insert_flag = 'U' THEN
/* Bug 3417868 End */
-- bug 4551061 added the in parameter p_availability_status
PSB_POSITIONS_PVT.UPDATE_ROW
(
p_api_version => 1.0,
p_init_msg_lISt => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_id => l_position_i_cache(i).position_id,
-- de by org
p_organization_id => l_position_i_cache(i).organization_id,
p_data_extract_id => p_data_extract_id,
p_position_definition_id => l_ccid,
p_hr_position_id => l_position_i_cache(i).hr_position_id,
p_hr_employee_id => l_position_i_cache(i).hr_employee_id,
p_business_group_id => p_business_group_id,
p_effective_start_date => l_position_i_cache(i).effective_start_date,
p_effective_end_date => l_position_i_cache(i).effective_end_date,
p_set_of_books_id => p_set_of_books_id,
p_vacant_position_flag => l_vacant_position_flag,
p_availability_status => l_position_i_cache(i).availability_status,
p_transaction_id => l_position_i_cache(i).transaction_id,
p_transaction_status => l_position_i_cache(i).transaction_status,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_attribute_category => NULL,
p_name => l_concat_pos_name,
p_mode => 'R'
) ;
(SELECT a.effective_start_date,
a.effective_end_date
FROM per_all_assignments_f a,
psb_data_extracts b
WHERE a.position_id =l_position_i_cache(i).hr_position_id
AND a.person_id = l_position_i_cache(i).hr_employee_id
AND b.data_extract_id = p_data_extract_id
AND b.req_data_as_of_date between
a.effective_start_date AND a.effective_end_date)
LOOP
l_assignment_exists := TRUE;
(SELECT a.effective_start_date,
a.effective_end_date
FROM hr_all_positions_f a,
psb_data_extracts b
WHERE a.position_id =l_position_i_cache(i).hr_position_id
AND b.data_extract_id = p_data_extract_id
AND b.req_data_as_of_date between
a.effective_start_date AND a.effective_end_date)
LOOP
l_eff_start_date := l_hr_position_rec.effective_start_date;
-- l_update_flag := 'N';
AND (l_update_flag <> 'Y')
*/
-- THEN /* For Bug No: 3066598 */
--
/* Bug 3417868 Start */
ELSIF l_position_i_cache(i).update_insert_flag = 'I' THEN
/* Bug 3417868 End */
SELECT psb_positions_s.nextval INTO l_position_id
FROM dual;
PSB_POSITIONS_PVT.INSERT_ROW
(
p_api_version => 1.0,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_position_id => l_position_id,
-- de by org
p_organization_id => l_position_i_cache(i).organization_id,
p_data_extract_id => p_data_extract_id,
p_position_definition_id => l_ccid,
p_hr_position_id => l_position_i_cache(i).hr_position_id,
p_hr_employee_id => l_position_i_cache(i).hr_employee_id,
p_business_group_id => p_business_group_id,
p_effective_start_date => l_position_i_cache(i).effective_start_date,
p_effective_END_date => l_position_i_cache(i).effective_END_date,
p_set_of_books_id => p_set_of_books_id,
p_vacant_position_flag => l_vacant_position_flag,
p_availability_status => l_position_i_cache(i).availability_status,
p_transaction_id => l_position_i_cache(i).transaction_id,
p_transaction_status => l_position_i_cache(i).transaction_status,
p_new_position_flag => 'N',
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_attribute_category => NULL,
p_name => l_concat_pos_name,
p_mode => 'R'
) ;
IF l_position_i_cache(i).update_insert_flag = 'I' OR
((l_elem_assign_dummy = 0) and (l_pay_element_id is not null)) THEN
--
/* if (((l_extract_method = 'CREATE') or (l_dummy = 0)) AND l_update_flag <> 'Y' )
or ((l_elem_assign_dummy = 0) and (l_pay_element_id is not null))
then */
--
PSB_POSITION_ASSIGNMENTS_PVT.INSERT_ROW
(
p_api_version => 1,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_position_assignment_id => l_position_assignment_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => NULL,
p_position_id => l_position_id,
p_assignment_type => 'ELEMENT',
p_attribute_id => NULL,
p_attribute_value_id => NULL,
p_attribute_value => NULL,
p_pay_element_id => l_pay_element_id,
p_pay_element_option_id => l_pay_element_option_id,
p_effective_start_date => l_position_i_cache(i).effective_start_date,
p_effective_END_date => l_position_i_cache(i).effective_end_date,
p_element_value_type => 'A',
p_element_value => l_position_i_cache(i).value,
p_currency_code => l_currency_code,
p_pay_basIS => l_position_i_cache(i).pay_basis,
p_employee_id => NULL,
p_primary_employee_flag => NULL,
p_global_default_flag => NULL,
p_assignment_default_rule_id => NULL,
p_modIFy_flag => NULL,
p_mode => 'R'
) ;
END IF; -- l_position_i_cache(i).update_insert_flag = 'U'
l_position_i_cache.delete;
PSB_HR_EXTRACT_DATA_PVT.Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'PSB Positions',
p_restart_id => prev_hr_position_id
);
l_last_update_date DATE;
l_last_upDATEd_BY number;
l_last_upDATE_login number;
Select pe.hr_employee_id,
pe.hr_position_id,
position_id,
-- de by org
organization_id,
assignment_id,first_name,
full_name, known_as,
last_name, middle_names, title,
pe.effective_start_DATE,
pe.change_DATE,
grade_id,grade_step,sequence_number,
pay_basIS,rate_id,
salary_type,rate_or_payscale_id,
element_value,proposed_salary,
employee_number,
pp.set_of_books_id
FROM psb_employees_i pe,
psb_positions pp
WHERE pe.data_extract_id = p_data_extract_id
AND pe.data_extract_id = pp.data_extract_id
AND pe.hr_position_id = pp.hr_position_id
AND pe.hr_employee_id = pp.hr_employee_id
AND pe.hr_position_id > l_restart_hr_position_id
ORDER BY pe.hr_position_id, proposed_salary;
SELECT distinct position_assignment_id,pp.position_id,pe.hr_employee_id
FROM psb_position_assignments ppa,psb_positions pp,psb_employees pe,
per_all_assignments_f paf
WHERE ppa.position_id=pp.position_id
AND ppa.data_extract_id=p_data_extract_id
AND pp.hr_position_id > l_restart_hr_position_id
AND paf.position_id=pp.hr_position_id
AND paf.person_id=pe.hr_employee_id
AND paf.person_id not in
(select person_id
from per_all_assignments_f paf2,fnd_sessions fs
where fs.effective_date between paf2.effective_start_date
and paf2.effective_end_date
and assignment_type='E' and position_id=paf.position_id
and fs.session_id=USERENV('sessionid'))
AND paf.assignment_type='E'
AND pp.hr_employee_id IS NULL
AND ppa.assignment_type='EMPLOYEE';
/* SELECT distinct position_assignment_id,pp.position_id,pe.hr_employee_id
FROM psb_position_assignments ppa,psb_positions pp,psb_employees pe,
per_all_assignments_f paf
WHERE ppa.position_id=pp.position_id
AND ppa.data_extract_id=p_data_extract_id
AND pp.hr_position_id > l_restart_hr_position_id
AND paf.position_id=pp.hr_position_id
AND paf.person_id=pe.hr_employee_id
AND not exists (select fs.session_id from fnd_sessions fs
where fs.effective_date between paf.effective_start_date
and paf.effective_end_date
and fs.session_id=USERENV('sessionid') )
AND paf.assignment_type='E'
AND pp.hr_employee_id IS NULL
AND ppa.assignment_type ='EMPLOYEE'; */
Select currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_of_books_id;
Select rate_or_payscale_name,grade_name
FROM psb_salary_i
WHERE rate_or_payscale_id = prev_rate_or_payscale_id
AND grade_id = prev_grade_id
AND grade_step = prev_grade_step
AND sequence_number = prev_sequence_number
AND data_extract_id = p_data_extract_id;
Select rate_or_payscale_name,grade_name
FROM psb_salary_i
WHERE rate_or_payscale_id = prev_rate_or_payscale_id
AND grade_id = prev_grade_id
AND data_extract_id = p_data_extract_id;
Select ppe.pay_element_id ,ppe.salary_type,
ppo.pay_element_option_id
FROM psb_pay_elements ppe,
psb_pay_element_options ppo
WHERE ppe.data_extract_id = p_data_extract_id
AND ppe.salary_type = 'STEP'
AND ppe.name = prev_rate_or_payscale_name
AND ppe.pay_element_id = ppo.pay_element_id
AND ppo.name = prev_grade_name
AND ppo.grade_step = prev_grade_step
AND ppo.sequence_number = prev_sequence_number;
Select ppe.pay_element_id ,ppe.salary_type,
ppo.pay_element_option_id
FROM psb_pay_elements ppe,
psb_pay_element_options ppo
WHERE ppe.data_extract_id = p_data_extract_id
AND ppe.salary_type = 'RATE'
AND ppe.name = prev_rate_or_payscale_name
AND ppe.pay_element_id = ppo.pay_element_id
AND ppo.name = prev_grade_name;
Select pay_element_option_id
FROM psb_pay_element_options
WHERE pay_element_id = l_ppay_element_id
AND name = prev_grade_name
AND grade_step = prev_grade_step
AND sequence_number = prev_sequence_number;
Select pay_element_option_id
FROM psb_pay_element_options
WHERE pay_element_id = l_ppay_element_id
AND name = prev_grade_name; */
Select pay_element_rate_id,
effective_start_DATE,
effective_END_DATE,
element_value,
currency_code
FROM psb_pay_element_rates
WHERE pay_element_id = l_ppay_element_id
AND pay_element_option_id = l_ppay_element_option_id;
Select pay_element_id
FROM psb_pay_elements
WHERE data_extract_id = p_data_extract_id
AND option_flag = 'N'
AND overwrite_flag = 'Y'
AND name = l_non_grade_salary_name;
l_last_upDATE_DATE := sysDATE;
l_last_upDATEd_BY := FND_GLOBAL.USER_ID;
l_last_upDATE_login := FND_GLOBAL.LOGIN_ID;
PSB_HR_EXTRACT_DATA_PVT.Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'PSB Employees',
p_restart_id => prev_hr_position_id
);
-- Note these assignments will be created/upDATEd later as part of
-- refreshing.
Begin
-- Delete 'ELEMENT' assignments.
Delete Psb_Position_Assignments
WHERE position_id = employee_rec.position_id
AND data_extract_id = p_data_extract_id
AND assignment_type = 'ELEMENT';
-- Delete 'ATTRIBUTE' assignments.
Delete Psb_Position_Assignments
WHERE position_id = employee_rec.position_id
AND data_extract_id = p_data_extract_id
AND assignment_type = 'ATTRIBUTE';
Select psb_pay_elements_s.nextval INTO ln_pay_element_id
FROM dual;
PSB_PAY_ELEMENTS_PVT.INSERT_ROW
( p_api_version => 1.0,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_row_id => l_rowid,
p_pay_element_id => ln_pay_element_id,
p_business_group_id => p_business_group_id,
p_data_extract_id => p_data_extract_id,
p_name => l_non_grade_salary_name,
p_description => NULL,
p_element_value_type => 'A',
p_FORmula_id => NULL,
p_overwrite_flag => 'Y',
p_required_flag => NULL,
p_follow_salary => NULL,
p_pay_basIS => prev_pay_basIS,
p_start_DATE => prev_change_DATE,
p_END_DATE => NULL,
p_processing_type => 'R',
p_period_type => NULL,
p_process_period_type => NULL,
p_max_element_value_type => NULL,
p_max_element_value => NULL,
p_salary_flag => 'Y',
p_salary_type => 'VALUE',
p_option_flag => 'N',
p_hr_element_type_id => NULL,
p_attribute_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_last_upDATE_DATE => l_last_update_date,
p_last_upDATEd_BY => l_last_upDATEd_BY,
p_last_upDATE_login => l_last_upDATE_login,
p_created_BY => l_created_BY,
p_creation_DATE => l_creation_DATE
);
-- UpDATE psb_employees table.
UpDATE Psb_Employees
set first_name = employee_rec.first_name,
full_name = employee_rec.full_name,
known_as = employee_rec.known_as,
last_name = employee_rec.last_name,
middle_names = employee_rec.middle_names,
title = employee_rec.title,
last_upDATE_DATE = l_last_update_date,
last_upDATEd_BY = l_last_upDATEd_BY,
last_upDATE_login = l_last_upDATE_login,
created_BY = l_created_BY,
creation_DATE = l_creation_DATE
WHERE hr_employee_id = employee_rec.hr_employee_id
AND data_extract_id = p_data_extract_id
AND business_group_id = p_business_group_id;
-- UpDATE position related assignment
-- (position_id = hr_employee_id + hr_position_id)
FOR l_pos_assignment_rec in
(
Select employee_id, position_assignment_id
FROM psb_position_assignments
WHERE data_extract_id = p_data_extract_id
AND assignment_type = 'EMPLOYEE'
AND position_id = employee_rec.position_id
)
LOOP
l_employee_dummy := 1;
END LOOP; -- END upDATE position related assignment.
Select position_assignment_id,
employee_id
FROM psb_position_assign_element_v
WHERE data_extract_id = p_data_extract_id
AND position_id = employee_rec.position_id
AND salary_flag = 'Y'
)
LOOP
l_element_dummy := 1;
PSB_POSITION_ASSIGNMENTS_PVT.UPDATE_ROW
(
p_api_version => 1,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_assignment_id =>
C_element_assignments_rec.position_assignment_id,
p_pay_element_id => l_ppay_element_id,
p_pay_element_option_id => l_ppay_element_option_id,
p_attribute_value_id => NULL,
p_attribute_value => NULL,
p_effective_END_DATE => NULL,
p_element_value_type => 'A',
p_element_value => l_proposed_salary,
p_pay_basIS => prev_pay_basIS,
p_employee_id => C_element_assignments_rec.employee_id,
p_primary_employee_flag => NULL,
p_global_default_flag => NULL,
p_assignment_default_rule_id => NULL,
p_modIFy_flag => NULL,
p_mode => 'R'
) ;
Select psb_employees_s.nextval INTO l_employee_id
FROM dual;
INSERT INTO PSB_EMPLOYEES
( EMPLOYEE_ID ,
HR_EMPLOYEE_ID ,
EMPLOYEE_NUMBER ,
FIRST_NAME ,
FULL_NAME ,
KNOWN_AS ,
LAST_NAME ,
MIDDLE_NAMES ,
TITLE ,
BUSINESS_GROUP_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_DATE ,
DATA_EXTRACT_ID )
VALUES
( l_employee_id,
employee_rec.hr_employee_id,
employee_rec.employee_number,
employee_rec.first_name,
employee_rec.full_name,
employee_rec.known_as,
employee_rec.last_name,
employee_rec.middle_names,
employee_rec.title,
p_business_group_id,
l_last_update_date,
l_last_upDATEd_BY ,
l_last_upDATE_login ,
l_created_BY,
l_creation_DATE,
p_data_extract_id
);
PSB_POSITION_ASSIGNMENTS_PVT.INSERT_ROW
(
p_api_version => 1,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_position_assignment_id => l_position_assignment_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => NULL,
p_position_id => employee_rec.position_id,
p_assignment_type => 'EMPLOYEE',
p_attribute_id => NULL,
p_attribute_value_id => NULL,
p_attribute_value => NULL,
p_pay_element_id => NULL,
p_pay_element_option_id => NULL,
p_effective_start_DATE => employee_rec.effective_start_DATE,
p_effective_END_DATE => NULL,
p_element_value_type => NULL,
p_element_value => NULL,
p_currency_code => NULL,
p_pay_basIS => NULL,
p_employee_id => l_employee_id,
p_primary_employee_flag => 'Y' ,
p_global_default_flag => NULL,
p_assignment_default_rule_id => NULL,
p_modIFy_flag => NULL,
p_mode => 'R'
) ;
PSB_POSITION_ASSIGNMENTS_PVT.INSERT_ROW
(
p_api_version => 1,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_position_assignment_id => l_position_assignment_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => NULL,
p_position_id => employee_rec.position_id,
p_assignment_type => 'ELEMENT',
p_attribute_id => NULL,
p_attribute_value_id => NULL,
p_attribute_value => NULL,
p_pay_element_id => l_ppay_element_id,
p_pay_element_option_id => l_ppay_element_option_id,
p_effective_start_DATE => prev_change_DATE,
p_effective_END_DATE => NULL,
p_element_value_type => 'A',
p_element_value => l_proposed_salary,
p_currency_code => l_currency_code,
p_pay_basIS => prev_pay_basIS,
p_employee_id => l_employee_id,
p_primary_employee_flag => NULL,
p_global_default_flag => NULL,
p_assignment_default_rule_id => NULL,
p_modIFy_flag => NULL,
p_mode => 'R'
) ;
PSB_POSITION_ASSIGNMENTS_PVT.Delete_Row
( p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_assignment_id =>terminated_rec.position_assignment_id);
delete from psb_employees
where hr_employee_id=terminated_rec.hr_employee_id
and data_extract_id=p_data_extract_id;
PSB_HR_EXTRACT_DATA_PVT.Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'PSB Employees',
p_restart_id => prev_hr_position_id
);
l_last_update_date DATE;
l_last_upDATEd_BY number;
l_last_upDATE_login number;
Select salary_type, rate_or_payscale_id,
rate_or_payscale_name, grade_id,
grade_name, grade_step, sequence_number,
minimum_value, maximum_value,
mid_value, element_value,pay_basIS,
element_type_id,
effective_start_DATE, effective_END_DATE
FROM psb_salary_i
WHERE data_extract_id = p_data_extract_id
AND rate_or_payscale_id > l_restart_salary_id
ORDER BY rate_or_payscale_id, salary_type;
Select rowid,pay_element_id, budget_set_id,period_type
FROM psb_pay_elements
WHERE data_extract_id = p_data_extract_id
AND name = lr_rate_or_payscale_name
AND salary_type = lr_salary_type;
Select currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_of_books_id;
Select rowid, pay_element_option_id
FROM PSB_PAY_ELEMENT_OPTIONS
WHERE pay_element_id = l_pay_element_id
AND name = lr_grade_name
AND grade_step = lr_grade_step
AND sequence_number = lr_sequence_number;
Select rowid, pay_element_option_id
FROM PSB_PAY_ELEMENT_OPTIONS
WHERE pay_element_id = l_pay_element_id
AND name = lr_grade_name;
l_last_update_date := sysDATE;
l_last_upDATEd_BY := FND_GLOBAL.USER_ID;
l_last_upDATE_login :=FND_GLOBAL.LOGIN_ID;
PSB_HR_EXTRACT_DATA_PVT.Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'PSB Elements',
p_restart_id => prev_rate_or_payscale_id
);
PSB_PAY_ELEMENTS_PVT.UPDATE_ROW
( p_api_version => 1.0,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_row_id => C_Refresh_Rec.rowid,
p_pay_element_id => C_Refresh_Rec.pay_element_id,
p_budget_set_id => C_Refresh_Rec.budget_set_id,
p_business_group_id => p_business_group_id,
p_data_extract_id => p_data_extract_id,
p_name => lr_rate_or_payscale_name,
p_description => NULL,
p_element_value_type => 'A',
p_FORmula_id => NULL,
p_overwrite_flag => NULL,
p_required_flag => NULL,
p_follow_salary => NULL,
p_pay_basIS => C_Element_Rec.pay_basIS,
p_start_DATE => C_Element_Rec.effective_start_DATE,
p_END_DATE => C_Element_Rec.effective_END_DATE,
p_processing_type => 'R',
/*
Pass the existing period_type istead of NULL to resolve the issue in Bug No: 2852998
We should not set/override the period_type to NULL in case of REFRESH mode,
since the period_type doesn't come from HR
*/
p_period_type => C_Refresh_Rec.period_type,
p_process_period_type => NULL,
p_max_element_value_type => NULL,
p_max_element_value => NULL,
p_salary_flag => 'Y',
p_salary_type => C_Element_Rec.salary_type,
p_option_flag => 'Y',
p_hr_element_type_id => C_Element_Rec.element_type_id,
p_attribute_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_last_upDATE_DATE => l_last_update_date,
p_last_upDATEd_BY => l_last_upDATEd_BY,
p_last_upDATE_login => l_last_upDATE_login
);
PSB_PAY_ELEMENT_OPTIONS_PVT.UPDATE_ROW
( p_api_version => 1.0,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_pay_element_option_id => C_Ref_Option_Rec.pay_element_option_id,
p_pay_element_id => C_Refresh_Rec.pay_element_id,
p_name => C_Element_Rec.grade_name,
p_grade_step => C_Element_Rec.grade_step,
p_sequence_number => C_Element_Rec.sequence_number,
p_last_upDATE_DATE => l_last_update_date,
p_last_upDATEd_BY => l_last_upDATEd_BY,
p_last_upDATE_login => l_last_upDATE_login
);
PSB_PAY_ELEMENT_OPTIONS_PVT.UPDATE_ROW
( p_api_version => 1.0,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_pay_element_option_id => C_Ref_Rate_Rec.pay_element_option_id,
p_pay_element_id => C_Refresh_Rec.pay_element_id,
p_name => C_Element_Rec.grade_name,
p_grade_step => C_Element_Rec.grade_step,
p_sequence_number => C_Element_Rec.sequence_number,
p_last_upDATE_DATE => l_last_update_date,
p_last_upDATEd_BY => l_last_upDATEd_BY,
p_last_upDATE_login => l_last_upDATE_login
);
Select psb_pay_element_options_s.nextval
INTO l_pay_element_option_id
FROM dual;
PSB_PAY_ELEMENT_OPTIONS_PVT.INSERT_ROW
( p_api_version => 1.0,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_pay_element_option_id => l_pay_element_option_id,
p_pay_element_id => l_pay_element_id,
p_name => C_Element_Rec.grade_name,
p_grade_step => C_Element_Rec.grade_step,
p_sequence_number => C_Element_Rec.sequence_number,
p_last_upDATE_DATE => l_last_update_date,
p_last_upDATEd_BY => l_last_upDATEd_BY,
p_last_upDATE_login => l_last_upDATE_login,
p_created_BY => l_created_BY,
p_creation_DATE => l_creation_DATE
);
Select psb_pay_elements_s.nextval
INTO l_pay_element_id
FROM dual;
PSB_PAY_ELEMENTS_PVT.INSERT_ROW
( p_api_version => 1.0,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_row_id => l_rowid,
p_pay_element_id => l_pay_element_id,
p_business_group_id => p_business_group_id,
p_data_extract_id => p_data_extract_id,
p_name => C_Element_Rec.rate_or_payscale_name,
p_description => NULL,
p_element_value_type => 'A',
p_FORmula_id => NULL,
p_overwrite_flag => NULL,
p_required_flag => NULL,
p_follow_salary => NULL,
p_pay_basIS => C_Element_Rec.pay_basIS,
p_start_DATE => C_Element_Rec.effective_start_DATE,
p_END_DATE => C_Element_Rec.effective_END_DATE,
p_processing_type => 'R',
p_period_type => NULL,
p_process_period_type => NULL,
p_max_element_value_type => NULL,
p_max_element_value => NULL,
p_salary_flag => 'Y',
p_salary_type => C_Element_Rec.salary_type,
p_option_flag => 'Y',
p_hr_element_type_id => C_Element_Rec.element_type_id,
p_attribute_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_last_upDATE_DATE => l_last_update_date,
p_last_upDATEd_BY => l_last_upDATEd_BY,
p_last_upDATE_login => l_last_upDATE_login,
p_created_BY => l_created_BY,
p_creation_DATE => l_creation_DATE
);
Select psb_pay_element_options_s.nextval
INTO l_pay_element_option_id
FROM dual;
PSB_PAY_ELEMENT_OPTIONS_PVT.INSERT_ROW
( p_api_version => 1.0,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_pay_element_option_id => l_pay_element_option_id,
p_pay_element_id => l_pay_element_id,
p_name => C_Element_Rec.grade_name,
p_grade_step => C_Element_Rec.grade_step,
p_sequence_number => C_Element_Rec.sequence_number,
p_last_upDATE_DATE => l_last_update_date,
p_last_upDATEd_BY => l_last_upDATEd_BY,
p_last_upDATE_login => l_last_upDATE_login,
p_created_BY => l_created_BY,
p_creation_DATE => l_creation_DATE
);
Select psb_pay_element_rates_s.nextval
INTO l_pay_element_rate_id
FROM dual;
PSB_PAY_ELEMENT_RATES_PVT.INSERT_ROW
( p_api_version => 1.0,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_pay_element_rate_id => l_pay_element_rate_id,
p_pay_element_option_id => l_pay_element_option_id,
p_pay_element_id => l_pay_element_id,
p_effective_start_DATE => C_Element_Rec.effective_start_DATE,
p_effective_END_DATE => C_Element_Rec.effective_END_DATE,
p_worksheet_id => NULL,
p_element_value_type => 'A',
p_element_value => C_Element_Rec.element_value,
p_pay_basIS => C_Element_Rec.pay_basIS,
p_FORmula_id => NULL,
p_maximum_value => C_Element_Rec.maximum_value,
p_mid_value => C_Element_Rec.mid_value,
p_minimum_value => C_Element_Rec.minimum_value,
p_currency_code => l_currency_code,
p_last_upDATE_DATE => l_last_update_date,
p_last_upDATEd_BY => l_last_upDATEd_BY,
p_last_upDATE_login => l_last_upDATE_login,
p_created_BY => l_created_BY,
p_creation_DATE => l_creation_DATE
) ;
PSB_HR_EXTRACT_DATA_PVT.Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'PSB Elements',
p_restart_id => prev_rate_or_payscale_id
);
Select count(*) INTO lcount
FROM psb_employees_i
WHERE hr_position_id = p_position_id
AND data_extract_id = p_data_extract_id;
l_last_update_date DATE;
l_last_upDATEd_BY number;
l_last_upDATE_login number;
Select b.attribute_value_id,b.attribute_id,
a.name, b.attribute_value,b.description,b.value_id
FROM psb_attribute_values_i b, psb_attributes_vl a
WHERE data_extract_id = p_data_extract_id
AND b.attribute_id = a.attribute_id
AND b.attribute_value_id > l_restart_attribute_value_id
order by b.attribute_value_id;
Select attribute_value_id
FROM psb_attribute_values
WHERE attribute_id = lr_attribute_id
AND attribute_value = lr_attribute_value
AND data_extract_id = p_data_extract_id;
l_last_update_date := sysDATE;
l_last_upDATEd_BY := FND_GLOBAL.USER_ID;
l_last_upDATE_login :=FND_GLOBAL.LOGIN_ID;
PSB_ATTRIBUTE_VALUES_PVT.UPDATE_ROW
( p_api_version => 1.0,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_attribute_value_id => C_ref_attr_rec.attribute_value_id,
p_attribute_id => C_Attribute_Rec.attribute_id,
p_attribute_value => C_Attribute_Rec.attribute_value,
p_hr_value_id => C_Attribute_Rec.value_id,
p_description => C_Attribute_Rec.description,
p_data_extract_id => p_data_extract_id,
p_context => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_attribute21 => NULL,
p_attribute22 => NULL,
p_attribute23 => NULL,
p_attribute24 => NULL,
p_attribute25 => NULL,
p_attribute26 => NULL,
p_attribute27 => NULL,
p_attribute28 => NULL,
p_attribute29 => NULL,
p_attribute30 => NULL,
p_last_upDATE_DATE => l_last_update_date,
p_last_upDATEd_BY => l_last_upDATEd_BY,
p_last_upDATE_login => l_last_upDATE_login
);
/* The following code(check for the attribute_value and update_row if
it exists else use insert_row. Insert_row was already existing)
has been added to fix the unique
constraint violation error as mentioned in bug 1735018.
Fixed by Siva Annamalai on 30 Apr 2001 */
l_attribute_value_id := NULL;
PSB_ATTRIBUTE_VALUES_PVT.UPDATE_ROW
( p_api_version => 1.0,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_attribute_value_id => l_attribute_value_id,
p_attribute_id => C_Attribute_Rec.attribute_id,
p_attribute_value => C_Attribute_Rec.attribute_value,
p_hr_value_id => C_Attribute_Rec.value_id,
p_description => C_Attribute_Rec.description,
p_data_extract_id => p_data_extract_id,
p_context => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_attribute21 => NULL,
p_attribute22 => NULL,
p_attribute23 => NULL,
p_attribute24 => NULL,
p_attribute25 => NULL,
p_attribute26 => NULL,
p_attribute27 => NULL,
p_attribute28 => NULL,
p_attribute29 => NULL,
p_attribute30 => NULL,
p_last_upDATE_DATE => l_last_update_date,
p_last_upDATEd_BY => l_last_upDATEd_BY,
p_last_upDATE_login => l_last_upDATE_login
);
PSB_ATTRIBUTE_VALUES_PVT.INSERT_ROW
( p_api_version => 1.0,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_attribute_value_id => C_Attribute_Rec.attribute_value_id,
p_attribute_id => C_Attribute_Rec.attribute_id,
p_attribute_value => C_Attribute_Rec.attribute_value,
p_hr_value_id => C_Attribute_Rec.value_id,
p_description => C_Attribute_Rec.description,
p_data_extract_id => p_data_extract_id,
p_context => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_attribute21 => NULL,
p_attribute22 => NULL,
p_attribute23 => NULL,
p_attribute24 => NULL,
p_attribute25 => NULL,
p_attribute26 => NULL,
p_attribute27 => NULL,
p_attribute28 => NULL,
p_attribute29 => NULL,
p_attribute30 => NULL,
p_last_upDATE_DATE => l_last_update_date,
p_last_upDATEd_BY => l_last_upDATEd_BY,
p_last_upDATE_login => l_last_upDATE_login,
p_created_BY => l_created_BY,
p_creation_DATE => l_creation_DATE
) ;
PSB_HR_EXTRACT_DATA_PVT.Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'PSB Attribute Values',
p_restart_id => C_Attribute_Rec.attribute_value_id
);
PSB_HR_EXTRACT_DATA_PVT.Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'PSB Attribute Values',
p_restart_id => l_fin_attribute_value_id
);
l_last_update_date DATE;
l_last_upDATEd_BY number;
l_last_upDATE_login number;
Select currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_of_books_id;
Select attribute_value_id
FROM psb_attribute_values
WHERE attribute_id = l_attribute_id
AND decode(l_definition_type, 'DFF',hr_value_id,
attribute_value) = lp_attribute_value
AND data_extract_id = p_data_extract_id;
l_last_update_date := sysDATE;
l_last_upDATEd_BY := FND_GLOBAL.USER_ID;
l_last_upDATE_login := FND_GLOBAL.LOGIN_ID;
/*Select pe.employee_id,
pea.hr_position_id,
pea.hr_employee_id,
pea.attribute_name,
pea.attribute_value,
pea.effective_start_DATE,
pea.effective_END_DATE
FROM psb_employee_assignments_i pea,
psb_employees pe
WHERE pea.data_extract_id = p_data_extract_id
AND pe.hr_employee_id(+) = pea.hr_employee_id
AND pe.data_extract_id(+) = p_data_extract_id
AND pea.hr_position_id > l_restart_hr_position_id
ORDER by pea.hr_position_id*/
SELECT ppa.employee_id,
-- de by org
pp.organization_id,
pea.hr_position_id,
pea.hr_employee_id,
pea.attribute_name,
pea.attribute_value,
pea.effective_start_DATE,
pea.effective_END_DATE
FROM psb_positions pp,
psb_position_assignments ppa,
psb_employee_assignments_i pea
WHERE pp.data_extract_id = p_data_extract_id
AND pp.position_id = ppa.position_id
AND ppa.assignment_type = 'EMPLOYEE'
AND ppa.employee_id IS NOT NULL
AND pp.hr_position_id = pea.hr_position_id
AND pp.hr_employee_id = pea.hr_employee_id
AND pea.data_extract_id = p_data_extract_id
AND pea.hr_position_id > l_restart_hr_position_id
UNION ALL
SELECT to_number(NULL),
-- de by org
pp.organization_id,
pea.hr_position_id,
pea.hr_employee_id,
pea.attribute_name,
pea.attribute_value,
pea.effective_start_DATE,
pea.effective_END_DATE
FROM psb_employee_assignments_i pea,
psb_positions pp
WHERE pea.data_extract_id = p_data_extract_id
AND pea.hr_position_id = pp.hr_position_id
AND pea.hr_employee_id IS NULL
AND pp.hr_employee_id IS NULL
AND pp.data_extract_id = p_data_extract_id
AND pea.hr_position_id > l_restart_hr_position_id
ORDER by 3
/*For Bug No : 2109120 End*/
)
LOOP
lp_hr_position_id := C_Assignment_rec.hr_position_id;
PSB_HR_EXTRACT_DATA_PVT.Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'PSB Position Assignments',
p_restart_id => C_Assignment_Rec.hr_position_id
);
Select attribute_id ,
nvl(value_table_flag ,'N') value_table_flag ,
definition_type
FROM psb_attributes_VL
WHERE business_group_id = p_business_group_id
AND name = lp_attribute_name
)
LOOP
l_attribute_id := C_pos_attr_rec.attribute_id;
Select position_id
FROM psb_positions
WHERE data_extract_id = p_data_extract_id
AND hr_position_id = C_Assignment_rec.hr_position_id
AND hr_employee_id is null
)
LOOP
lp_position_id := C_max_pos_rec.position_id;
Select position_id
FROM psb_positions
WHERE data_extract_id = p_data_extract_id
AND hr_position_id = C_Assignment_rec.hr_position_id
AND hr_employee_id = C_Assignment_rec.hr_employee_id
)
LOOP
lp_position_id := C_max_rec.position_id;
Select position_assignment_id
FROM psb_position_assignments
WHERE data_extract_id = p_data_extract_id
AND position_id = lr_position_id
AND attribute_id = lr_attribute_id
AND assignment_type = 'ATTRIBUTE'
)
LOOP
lr_max_dummy := 1;
PSB_POSITION_ASSIGNMENTS_PVT.INSERT_ROW
(
p_api_version => 1,
p_init_msg_lISt => NULL,
p_commit => NULL,
p_validation_level => NULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_position_assignment_id => l_position_assignment_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => NULL,
p_position_id => lp_position_id,
p_assignment_type => 'ATTRIBUTE',
p_attribute_id => l_attribute_id,
p_attribute_value_id => l_attribute_value_id,
p_attribute_value => l_attribute_value,
p_pay_element_id => NULL,
p_pay_element_option_id => NULL,
p_effective_start_DATE => C_Assignment_Rec.effective_start_DATE,
p_effective_END_DATE => C_Assignment_Rec.effective_END_DATE,
p_element_value_type => NULL,
p_element_value => NULL,
p_currency_code => NULL,
p_pay_basIS => NULL,
p_employee_id => NULL,
p_primary_employee_flag => NULL,
p_global_default_flag => NULL,
p_assignment_default_rule_id => NULL,
p_modIFy_flag => NULL,
p_mode => 'R'
) ;
PSB_HR_EXTRACT_DATA_PVT.Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'PSB Position Assignments',
p_restart_id => lp_hr_position_id
);
l_last_update_date DATE;
l_last_upDATEd_BY number;
l_last_upDATE_login number;
l_last_upDATE_DATE := sysDATE;
l_last_upDATEd_BY := FND_GLOBAL.USER_ID;
l_last_upDATE_login :=FND_GLOBAL.LOGIN_ID;
UpDATE PSB_DATA_EXTRACTS
set default_data_status = 'C'
WHERE data_extract_id = p_data_extract_id;