[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
delete_flag BOOLEAN);
delete_flag BOOLEAN);
delete_flag BOOLEAN);
SELECT ppf.attribute_Value
FROM psb_position_assignments ppf
WHERE ppf.data_extract_id = c_data_extract_id
AND ppf.position_id = c_position_id
AND nvl(worksheet_id, -1) = nvl(c_worksheet_id, -1)
AND ppf.attribute_id = c_attribute_Id
AND c_budget_year_end_date BETWEEN effective_start_date AND
nvl(effective_end_date, c_budget_year_end_date);
select PSB_ATTRIBUTE_VALUES_S.NEXTVAL seq
from dual;
select *
from PSB_ATTRIBUTE_VALUES a
where data_extract_id = p_source_data_extract_id
and exists
(select 1 from PSB_ATTRIBUTES_VL b
where b.attribute_id = a.attribute_id
and b.business_group_id = p_source_business_group_id)
and attribute_value is not null;
select attribute_value_id
from PSB_ATTRIBUTE_VALUES
where attribute_value = attrval
and attribute_id = attrid
and data_extract_id = p_target_data_extract_id;
PSB_ATTRIBUTE_VALUES_PVT.Insert_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_rowid => l_rowid,
p_attribute_value_id => l_attribute_value_id,
p_attribute_id => c_attrval_rec.attribute_id,
p_attribute_value => c_attrval_rec.attribute_value,
p_description => c_attrval_rec.description,
p_hr_value_id => c_attrval_rec.hr_value_id,
p_data_extract_id => p_target_data_extract_id,
p_attribute1 => c_attrval_rec.attribute1,
p_attribute2 => c_attrval_rec.attribute2,
p_attribute3 => c_attrval_rec.attribute3,
p_attribute4 => c_attrval_rec.attribute4,
p_attribute5 => c_attrval_rec.attribute5,
p_attribute6 => c_attrval_rec.attribute6,
p_attribute7 => c_attrval_rec.attribute7,
p_attribute8 => c_attrval_rec.attribute8,
p_attribute9 => c_attrval_rec.attribute9,
p_attribute10 => c_attrval_rec.attribute10,
p_attribute11 => c_attrval_rec.attribute11,
p_attribute12 => c_attrval_rec.attribute12,
p_attribute13 => c_attrval_rec.attribute13,
p_attribute14 => c_attrval_rec.attribute14,
p_attribute15 => c_attrval_rec.attribute15,
p_attribute16 => c_attrval_rec.attribute16,
p_attribute17 => c_attrval_rec.attribute17,
p_attribute18 => c_attrval_rec.attribute18,
p_attribute19 => c_attrval_rec.attribute19,
p_attribute20 => c_attrval_rec.attribute20,
p_attribute21 => c_attrval_rec.attribute21,
p_attribute22 => c_attrval_rec.attribute22,
p_attribute23 => c_attrval_rec.attribute23,
p_attribute24 => c_attrval_rec.attribute24,
p_attribute25 => c_attrval_rec.attribute25,
p_attribute26 => c_attrval_rec.attribute26,
p_attribute27 => c_attrval_rec.attribute27,
p_attribute28 => c_attrval_rec.attribute28,
p_attribute29 => c_attrval_rec.attribute29,
p_attribute30 => c_attrval_rec.attribute30,
p_context => c_attrval_rec.context,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
then update the with the current value. Changes done by Siva */
begin
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_attrval_rec.attribute_id,
p_attribute_value => c_attrval_rec.attribute_value,
p_description => c_attrval_rec.description,
p_hr_value_id => c_attrval_rec.hr_value_id,
p_data_extract_id => p_target_data_extract_id,
p_attribute1 => c_attrval_rec.attribute1,
p_attribute2 => c_attrval_rec.attribute2,
p_attribute3 => c_attrval_rec.attribute3,
p_attribute4 => c_attrval_rec.attribute4,
p_attribute5 => c_attrval_rec.attribute5,
p_attribute6 => c_attrval_rec.attribute6,
p_attribute7 => c_attrval_rec.attribute7,
p_attribute8 => c_attrval_rec.attribute8,
p_attribute9 => c_attrval_rec.attribute9,
p_attribute10 => c_attrval_rec.attribute10,
p_attribute11 => c_attrval_rec.attribute11,
p_attribute12 => c_attrval_rec.attribute12,
p_attribute13 => c_attrval_rec.attribute13,
p_attribute14 => c_attrval_rec.attribute14,
p_attribute15 => c_attrval_rec.attribute15,
p_attribute16 => c_attrval_rec.attribute16,
p_attribute17 => c_attrval_rec.attribute17,
p_attribute18 => c_attrval_rec.attribute18,
p_attribute19 => c_attrval_rec.attribute19,
p_attribute20 => c_attrval_rec.attribute20,
p_attribute21 => c_attrval_rec.attribute21,
p_attribute22 => c_attrval_rec.attribute22,
p_attribute23 => c_attrval_rec.attribute23,
p_attribute24 => c_attrval_rec.attribute24,
p_attribute25 => c_attrval_rec.attribute25,
p_attribute26 => c_attrval_rec.attribute26,
p_attribute27 => c_attrval_rec.attribute27,
p_attribute28 => c_attrval_rec.attribute28,
p_attribute29 => c_attrval_rec.attribute29,
p_attribute30 => c_attrval_rec.attribute30,
p_context => c_attrval_rec.context,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID);
select business_group_id
from PSB_DATA_EXTRACTS
where data_extract_id = p_source_data_extract_id;
select *
from PSB_PAY_ELEMENTS
where pay_element_id = p_pay_element_id;
select pay_element_id, rowid
from PSB_PAY_ELEMENTS
where name = elemname
and data_extract_id = p_target_data_extract_id;
select *
from PSB_PAY_ELEMENT_OPTIONS
where pay_element_id = elemid;
select pay_element_option_id
from PSB_PAY_ELEMENT_OPTIONS
where name = optname
and pay_element_id = elemid;
select *
from PSB_PAY_ELEMENT_RATES
where worksheet_id is null
and pay_element_option_id = elemoptionid
and pay_element_id = elemid;
select *
from PSB_PAY_ELEMENT_RATES
where worksheet_id is null
and pay_element_id = elemid;
select *
from PSB_ELEMENT_POS_SET_GROUPS
where pay_element_id = elemid;
select *
from PSB_SET_RELATIONS
where position_set_group_id = possetgrpid;
select *
from PSB_ACCOUNT_POSITION_SETS
where account_position_set_id = possetid;
select *
from PSB_ACCOUNT_POSITION_SET_LINES
where account_position_set_id = possetid;
select *
from PSB_POSITION_SET_LINE_VALUES
where line_sequence_id = lineseqid;
select account_position_set_id, rowid
from PSB_ACCOUNT_POSITION_SETS
where data_extract_id = p_target_data_extract_id
and account_or_position_type = 'P'
and name = possetname;
select value_sequence_id
from PSB_POSITION_SET_LINE_VALUES
where attribute_value = attrval
and line_sequence_id = lineseqid;
select a.attribute_value_id
from PSB_ATTRIBUTE_VALUES a,
PSB_ATTRIBUTE_VALUES b
where a.data_extract_id = p_target_data_extract_id
and a.attribute_value = b.attribute_value
and a.attribute_id = b.attribute_id -- added for Bug#4262388
and b.attribute_value_id = attrvalid;
select line_sequence_id
from PSB_ACCOUNT_POSITION_SET_LINES
where attribute_id = attrid
and account_position_set_id = possetid;
select *
from PSB_PAY_ELEMENT_DISTRIBUTIONS
where position_set_group_id = possetgrpid;
select psb_pay_elements_s.nextval seq
from dual;
select psb_pay_element_options_s.nextval seq
from dual;
select PSB_ACCOUNT_POSITION_SETS_S.NEXTVAL seq
from dual;
select PSB_ACCT_POSITION_SET_LINES_S.NEXTVAL seq
from dual;
select PSB_POSITION_SET_LINE_VALUES_S.NEXTVAL seq
from dual;
select PSB_SET_RELATIONS_S.NEXTVAL seq
from dual;
select PSB_ELEMENT_POS_SET_GROUPS_S.NEXTVAL seq
from dual;
select PSB_PAY_ELEMENT_DISTRIBUTION_S.NEXTVAL seq
from dual;
PSB_PAY_ELEMENTS_PVT.INSERT_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_row_id => l_rowid,
p_pay_element_id => l_pay_element_id,
p_budget_set_id => c_elem_rec.budget_set_id,
p_business_group_id => c_elem_rec.business_group_id,
p_data_extract_id => p_target_data_extract_id,
p_name => c_elem_rec.name,
p_description => c_elem_rec.description,
p_element_value_type => c_elem_rec.element_value_type,
p_formula_id => c_elem_rec.formula_id,
p_overwrite_flag => c_elem_rec.overwrite_flag,
p_required_flag => c_elem_rec.required_flag,
p_follow_salary => c_elem_rec.follow_salary,
p_pay_basis => c_elem_rec.pay_basis,
p_start_date => c_elem_rec.start_date,
p_end_date => c_elem_rec.end_date,
p_processing_type => c_elem_rec.processing_type,
p_period_type => c_elem_rec.period_type,
p_process_period_type => c_elem_rec.process_period_type,
p_max_element_value_type => c_elem_rec.max_element_value_type,
p_max_element_value => c_elem_rec.max_element_value,
p_salary_flag => c_elem_rec.salary_flag,
p_salary_type => c_elem_rec.salary_type,
p_option_flag => c_elem_rec.option_flag,
p_hr_element_type_id => c_elem_rec.hr_element_type_id,
p_attribute_category => c_elem_rec.attribute_category,
p_attribute1 => c_elem_rec.attribute1,
p_attribute2 => c_elem_rec.attribute2,
p_attribute3 => c_elem_rec.attribute3,
p_attribute4 => c_elem_rec.attribute4,
p_attribute5 => c_elem_rec.attribute5,
p_attribute6 => c_elem_rec.attribute6,
p_attribute7 => c_elem_rec.attribute7,
p_attribute8 => c_elem_rec.attribute8,
p_attribute9 => c_elem_rec.attribute9,
p_attribute10 => c_elem_rec.attribute10,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
PSB_PAY_ELEMENT_OPTIONS_PVT.INSERT_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_pay_element_option_id => l_pay_element_option_id,
p_pay_element_id => l_pay_element_id,
p_name => c_elemoptions_rec.name,
p_grade_step => c_elemoptions_rec.grade_step,
p_sequence_number => c_elemoptions_rec.sequence_number,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
PSB_ELEMENT_POS_SET_GROUPS_PVT.Insert_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_set_group_id => l_position_set_group_id,
p_pay_element_id => l_pay_element_id,
p_name => c_possetgrp_rec.name,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
PSB_ACCOUNT_POSITION_SET_PVT.Insert_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_row_id => l_rowid,
p_account_position_set_id => l_position_set_id,
p_name => c_posset_rec.name,
p_set_of_books_id => c_posset_rec.set_of_books_id,
p_use_in_budget_group_flag => c_posset_rec.use_in_budget_group_flag,
p_data_extract_id => p_target_data_extract_id,
p_global_or_local_type => c_posset_rec.global_or_local_type,
p_account_or_position_type =>c_posset_rec.account_or_position_type,
p_attribute_selection_type => c_posset_rec.attribute_selection_type,
p_business_group_id => c_posset_rec.business_group_id,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
PSB_ACCT_POSITION_SET_LINE_PVT.Insert_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_row_id => l_rowid,
p_line_sequence_id => l_line_sequence_id,
p_account_position_set_id => l_position_set_id,
p_description => c_possetline_rec.description,
p_business_group_id => c_possetline_rec.business_group_id,
p_attribute_id => c_possetline_rec.attribute_id,
p_include_or_exclude_type => c_possetline_rec.include_or_exclude_type,
p_segment1_low => c_possetline_rec.segment1_low,
p_segment2_low => c_possetline_rec.segment2_low,
p_segment3_low => c_possetline_rec.segment3_low,
p_segment4_low => c_possetline_rec.segment4_low,
p_segment5_low => c_possetline_rec.segment5_low,
p_segment6_low => c_possetline_rec.segment6_low,
p_segment7_low => c_possetline_rec.segment7_low,
p_segment8_low => c_possetline_rec.segment8_low,
p_segment9_low => c_possetline_rec.segment9_low,
p_segment10_low => c_possetline_rec.segment10_low,
p_segment11_low => c_possetline_rec.segment11_low,
p_segment12_low => c_possetline_rec.segment12_low,
p_segment13_low => c_possetline_rec.segment13_low,
p_segment14_low => c_possetline_rec.segment14_low,
p_segment15_low => c_possetline_rec.segment15_low,
p_segment16_low => c_possetline_rec.segment16_low,
p_segment17_low => c_possetline_rec.segment17_low,
p_segment18_low => c_possetline_rec.segment18_low,
p_segment19_low => c_possetline_rec.segment19_low,
p_segment20_low => c_possetline_rec.segment20_low,
p_segment21_low => c_possetline_rec.segment21_low,
p_segment22_low => c_possetline_rec.segment22_low,
p_segment23_low => c_possetline_rec.segment23_low,
p_segment24_low => c_possetline_rec.segment24_low,
p_segment25_low => c_possetline_rec.segment25_low,
p_segment26_low => c_possetline_rec.segment26_low,
p_segment27_low => c_possetline_rec.segment27_low,
p_segment28_low => c_possetline_rec.segment28_low,
p_segment29_low => c_possetline_rec.segment29_low,
p_segment30_low => c_possetline_rec.segment30_low,
p_segment1_high => c_possetline_rec.segment1_high,
p_segment2_high => c_possetline_rec.segment2_high,
p_segment3_high => c_possetline_rec.segment3_high,
p_segment4_high => c_possetline_rec.segment4_high,
p_segment5_high => c_possetline_rec.segment5_high,
p_segment6_high => c_possetline_rec.segment6_high,
p_segment7_high => c_possetline_rec.segment7_high,
p_segment8_high => c_possetline_rec.segment8_high,
p_segment9_high => c_possetline_rec.segment9_high,
p_segment10_high => c_possetline_rec.segment10_high,
p_segment11_high => c_possetline_rec.segment11_high,
p_segment12_high => c_possetline_rec.segment12_high,
p_segment13_high => c_possetline_rec.segment13_high,
p_segment14_high => c_possetline_rec.segment14_high,
p_segment15_high => c_possetline_rec.segment15_high,
p_segment16_high => c_possetline_rec.segment16_high,
p_segment17_high => c_possetline_rec.segment17_high,
p_segment18_high => c_possetline_rec.segment18_high,
p_segment19_high => c_possetline_rec.segment19_high,
p_segment20_high => c_possetline_rec.segment20_high,
p_segment21_high => c_possetline_rec.segment21_high,
p_segment22_high => c_possetline_rec.segment22_high,
p_segment23_high => c_possetline_rec.segment23_high,
p_segment24_high => c_possetline_rec.segment24_high,
p_segment25_high => c_possetline_rec.segment25_high,
p_segment26_high => c_possetline_rec.segment26_high,
p_segment27_high => c_possetline_rec.segment27_high,
p_segment28_high => c_possetline_rec.segment28_high,
p_segment29_high => c_possetline_rec.segment29_high,
p_segment30_high => c_possetline_rec.segment30_high,
p_context => c_possetline_rec.context,
p_attribute1 => c_possetline_rec.attribute1,
p_attribute2 => c_possetline_rec.attribute2,
p_attribute3 => c_possetline_rec.attribute3,
p_attribute4 => c_possetline_rec.attribute4,
p_attribute5 => c_possetline_rec.attribute5,
p_attribute6 => c_possetline_rec.attribute6,
p_attribute7 => c_possetline_rec.attribute7,
p_attribute8 => c_possetline_rec.attribute8,
p_attribute9 => c_possetline_rec.attribute9,
p_attribute10 => c_possetline_rec.attribute10,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
PSB_POS_SET_LINE_VALUES_PVT.Insert_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_row_id => l_rowid,
p_value_sequence_id => l_value_sequence_id,
p_line_sequence_id => l_line_sequence_id,
p_attribute_value_id => l_attribute_value_id,
p_attribute_value => c_possetlineval_rec.attribute_value,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
PSB_ACCT_POSITION_SET_LINE_PVT.Insert_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_row_id => l_rowid,
p_line_sequence_id => l_line_sequence_id,
p_account_position_set_id => l_position_set_id,
p_description => c_possetline_rec.description,
p_business_group_id => c_possetline_rec.business_group_id,
p_attribute_id => c_possetline_rec.attribute_id,
p_include_or_exclude_type => c_possetline_rec.include_or_exclude_type,
p_segment1_low => c_possetline_rec.segment1_low,
p_segment2_low => c_possetline_rec.segment2_low,
p_segment3_low => c_possetline_rec.segment3_low,
p_segment4_low => c_possetline_rec.segment4_low,
p_segment5_low => c_possetline_rec.segment5_low,
p_segment6_low => c_possetline_rec.segment6_low,
p_segment7_low => c_possetline_rec.segment7_low,
p_segment8_low => c_possetline_rec.segment8_low,
p_segment9_low => c_possetline_rec.segment9_low,
p_segment10_low => c_possetline_rec.segment10_low,
p_segment11_low => c_possetline_rec.segment11_low,
p_segment12_low => c_possetline_rec.segment12_low,
p_segment13_low => c_possetline_rec.segment13_low,
p_segment14_low => c_possetline_rec.segment14_low,
p_segment15_low => c_possetline_rec.segment15_low,
p_segment16_low => c_possetline_rec.segment16_low,
p_segment17_low => c_possetline_rec.segment17_low,
p_segment18_low => c_possetline_rec.segment18_low,
p_segment19_low => c_possetline_rec.segment19_low,
p_segment20_low => c_possetline_rec.segment20_low,
p_segment21_low => c_possetline_rec.segment21_low,
p_segment22_low => c_possetline_rec.segment22_low,
p_segment23_low => c_possetline_rec.segment23_low,
p_segment24_low => c_possetline_rec.segment24_low,
p_segment25_low => c_possetline_rec.segment25_low,
p_segment26_low => c_possetline_rec.segment26_low,
p_segment27_low => c_possetline_rec.segment27_low,
p_segment28_low => c_possetline_rec.segment28_low,
p_segment29_low => c_possetline_rec.segment29_low,
p_segment30_low => c_possetline_rec.segment30_low,
p_segment1_high => c_possetline_rec.segment1_high,
p_segment2_high => c_possetline_rec.segment2_high,
p_segment3_high => c_possetline_rec.segment3_high,
p_segment4_high => c_possetline_rec.segment4_high,
p_segment5_high => c_possetline_rec.segment5_high,
p_segment6_high => c_possetline_rec.segment6_high,
p_segment7_high => c_possetline_rec.segment7_high,
p_segment8_high => c_possetline_rec.segment8_high,
p_segment9_high => c_possetline_rec.segment9_high,
p_segment10_high => c_possetline_rec.segment10_high,
p_segment11_high => c_possetline_rec.segment11_high,
p_segment12_high => c_possetline_rec.segment12_high,
p_segment13_high => c_possetline_rec.segment13_high,
p_segment14_high => c_possetline_rec.segment14_high,
p_segment15_high => c_possetline_rec.segment15_high,
p_segment16_high => c_possetline_rec.segment16_high,
p_segment17_high => c_possetline_rec.segment17_high,
p_segment18_high => c_possetline_rec.segment18_high,
p_segment19_high => c_possetline_rec.segment19_high,
p_segment20_high => c_possetline_rec.segment20_high,
p_segment21_high => c_possetline_rec.segment21_high,
p_segment22_high => c_possetline_rec.segment22_high,
p_segment23_high => c_possetline_rec.segment23_high,
p_segment24_high => c_possetline_rec.segment24_high,
p_segment25_high => c_possetline_rec.segment25_high,
p_segment26_high => c_possetline_rec.segment26_high,
p_segment27_high => c_possetline_rec.segment27_high,
p_segment28_high => c_possetline_rec.segment28_high,
p_segment29_high => c_possetline_rec.segment29_high,
p_segment30_high => c_possetline_rec.segment30_high,
p_context => c_possetline_rec.context,
p_attribute1 => c_possetline_rec.attribute1,
p_attribute2 => c_possetline_rec.attribute2,
p_attribute3 => c_possetline_rec.attribute3,
p_attribute4 => c_possetline_rec.attribute4,
p_attribute5 => c_possetline_rec.attribute5,
p_attribute6 => c_possetline_rec.attribute6,
p_attribute7 => c_possetline_rec.attribute7,
p_attribute8 => c_possetline_rec.attribute8,
p_attribute9 => c_possetline_rec.attribute9,
p_attribute10 => c_possetline_rec.attribute10,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
PSB_POS_SET_LINE_VALUES_PVT.Insert_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_row_id => l_rowid,
p_value_sequence_id => l_value_sequence_id,
p_line_sequence_id => l_line_sequence_id,
p_attribute_value_id => l_attribute_value_id,
p_attribute_value => c_possetlineval_rec.attribute_value,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
PSB_POS_SET_LINE_VALUES_PVT.Insert_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_row_id => l_rowid,
p_value_sequence_id => l_value_sequence_id,
p_line_sequence_id => l_line_sequence_id,
p_attribute_value_id => l_attribute_value_id,
p_attribute_value => c_possetlineval_rec.attribute_value,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
--This has been commented because of the attribute selection type
--should be same as the old Position Set.
--This has been taken care during the build for Bug No : 1822364
/*
-- Since we're adding attribute values set the attribute selection type to 'O'
if c_posset_rec.attribute_selection_type <> 'O' then
begin
PSB_ACCOUNT_POSITION_SET_PVT.Update_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_row_id => lp_rowid,
p_account_position_set_id => l_position_set_id,
p_name => c_posset_rec.name,
p_set_of_books_id => c_posset_rec.set_of_books_id,
p_data_extract_id => p_target_data_extract_id,
p_global_or_local_type => c_posset_rec.global_or_local_type,
p_account_or_position_type => c_posset_rec.account_or_position_type,
p_attribute_selection_type => 'O',
p_business_group_id => c_posset_rec.business_group_id,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID);
PSB_SET_RELATION_PVT.Insert_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_row_id => l_rowid,
p_set_relation_id => l_set_relation_id,
p_account_position_set_id => l_position_set_id,
p_allocation_rule_id => null,
p_budget_group_id => null,
p_budget_workflow_rule_id => null,
p_constraint_id => null,
p_default_rule_id => null,
p_parameter_id => null,
p_position_set_group_id => l_position_set_group_id,
/* Budget Revision Rules Enhancement Start */
p_rule_id => null,
p_apply_balance_flag => null,
/* Budget Revision Rules Enhancement End */
p_effective_start_date => null,
p_effective_end_date => null,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
PSB_ELEMENT_DISTRIBUTIONS_PVT.Insert_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_distribution_id => l_distribution_id,
p_position_set_group_id => l_position_set_group_id,
p_chart_of_accounts_id => c_elemdist_rec.chart_of_accounts_id,
p_effective_start_date => c_elemdist_rec.effective_start_date,
p_effective_end_date => c_elemdist_rec.effective_end_date,
p_distribution_percent => c_elemdist_rec.distribution_percent,
p_concatenated_segments => c_elemdist_rec.concatenated_segments,
p_code_combination_id => c_elemdist_rec.code_combination_id,
p_distribution_set_id => c_elemdist_rec.distribution_set_id,
p_segment1 => c_elemdist_rec.segment1,
p_segment2 => c_elemdist_rec.segment2,
p_segment3 => c_elemdist_rec.segment3,
p_segment4 => c_elemdist_rec.segment4,
p_segment5 => c_elemdist_rec.segment5,
p_segment6 => c_elemdist_rec.segment6,
p_segment7 => c_elemdist_rec.segment7,
p_segment8 => c_elemdist_rec.segment8,
p_segment9 => c_elemdist_rec.segment9,
p_segment10 => c_elemdist_rec.segment10,
p_segment11 => c_elemdist_rec.segment11,
p_segment12 => c_elemdist_rec.segment12,
p_segment13 => c_elemdist_rec.segment13,
p_segment14 => c_elemdist_rec.segment14,
p_segment15 => c_elemdist_rec.segment15,
p_segment16 => c_elemdist_rec.segment16,
p_segment17 => c_elemdist_rec.segment17,
p_segment18 => c_elemdist_rec.segment18,
p_segment19 => c_elemdist_rec.segment19,
p_segment20 => c_elemdist_rec.segment20,
p_segment21 => c_elemdist_rec.segment21,
p_segment22 => c_elemdist_rec.segment22,
p_segment23 => c_elemdist_rec.segment23,
p_segment24 => c_elemdist_rec.segment24,
p_segment25 => c_elemdist_rec.segment25,
p_segment26 => c_elemdist_rec.segment26,
p_segment27 => c_elemdist_rec.segment27,
p_segment28 => c_elemdist_rec.segment28,
p_segment29 => c_elemdist_rec.segment29,
p_segment30 => c_elemdist_rec.segment30,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
PSB_PAY_ELEMENTS_PVT.UPDATE_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_ROW_ID => l_rowid,
P_PAY_ELEMENT_ID => l_pay_element_id,
P_BUSINESS_GROUP_ID => c_elem_rec.business_group_id,
P_DATA_EXTRACT_ID => p_target_data_extract_id,
P_BUDGET_SET_ID => c_elem_rec.budget_set_id,
P_NAME => c_elem_rec.name,
P_DESCRIPTION => c_elem_rec.description,
P_ELEMENT_VALUE_TYPE => c_elem_rec.element_value_type,
P_FORMULA_ID => c_elem_rec.formula_id,
P_OVERWRITE_FLAG => c_elem_rec.overwrite_flag,
P_REQUIRED_FLAG => c_elem_rec.required_flag,
P_FOLLOW_SALARY => c_elem_rec.follow_salary,
P_PAY_BASIS => c_elem_rec.pay_basis,
P_START_DATE => c_elem_rec.start_date,
P_END_DATE => c_elem_rec.end_date,
P_PROCESSING_TYPE => c_elem_rec.processing_type,
P_PERIOD_TYPE => c_elem_rec.period_type,
P_PROCESS_PERIOD_TYPE => c_elem_rec.process_period_type,
P_MAX_ELEMENT_VALUE_TYPE => c_elem_rec.max_element_value_type,
P_MAX_ELEMENT_VALUE => c_elem_rec.max_element_value,
P_SALARY_FLAG => c_elem_rec.salary_flag,
P_SALARY_TYPE => c_elem_rec.salary_type,
P_OPTION_FLAG => c_elem_rec.option_flag,
P_HR_ELEMENT_TYPE_ID => c_elem_rec.hr_element_type_id,
P_ATTRIBUTE_CATEGORY => c_elem_rec.attribute_category,
P_ATTRIBUTE1 => c_elem_rec.attribute1,
P_ATTRIBUTE2 => c_elem_rec.attribute2,
P_ATTRIBUTE3 => c_elem_rec.attribute3,
P_ATTRIBUTE4 => c_elem_rec.attribute4,
P_ATTRIBUTE5 => c_elem_rec.attribute5,
P_ATTRIBUTE6 => c_elem_rec.attribute6,
P_ATTRIBUTE7 => c_elem_rec.attribute7,
P_ATTRIBUTE8 => c_elem_rec.attribute8,
P_ATTRIBUTE9 => c_elem_rec.attribute9,
P_ATTRIBUTE10 => c_elem_rec.attribute10,
P_LAST_UPDATE_DATE => sysdate,
P_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
P_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
PSB_PAY_ELEMENT_OPTIONS_PVT.INSERT_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_pay_element_option_id => l_pay_element_option_id,
p_pay_element_id => l_pay_element_id,
p_name => c_elemoptions_rec.name,
p_grade_step => c_elemoptions_rec.grade_step,
p_sequence_number => c_elemoptions_rec.sequence_number,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
select *
from PSB_PAY_ELEMENTS
where data_extract_id = p_source_data_extract_id;
select pay_element_id, rowid
from PSB_PAY_ELEMENTS
where name = elemname
and data_extract_id = p_target_data_extract_id;
select *
from PSB_PAY_ELEMENT_OPTIONS
where pay_element_id = elemid;
select pay_element_option_id
from PSB_PAY_ELEMENT_OPTIONS
where name = optname
and pay_element_id = elemid;
select *
from PSB_PAY_ELEMENT_RATES a
where a.pay_element_id = elemid
and a.pay_element_option_id = elemoptionid
and ((a.worksheet_id = p_worksheet_id)
or (a.worksheet_id is null
and not exists
(select 1 from PSB_PAY_ELEMENT_RATES b
where b.pay_element_id = a.pay_element_id
and b.pay_element_option_id = a.pay_element_option_id
and b.worksheet_id = p_worksheet_id)));
select *
from PSB_PAY_ELEMENT_RATES a
where a.pay_element_id = elemid
and ((a.worksheet_id = p_worksheet_id)
or (a.worksheet_id is null
and not exists
(select 1 from PSB_PAY_ELEMENT_RATES b
where b.pay_element_id = a.pay_element_id
and b.worksheet_id = p_worksheet_id)));
select psb_pay_elements_s.nextval seq
from dual;
select psb_pay_element_options_s.nextval seq
from dual;
PSB_PAY_ELEMENTS_PVT.INSERT_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_row_id => l_rowid,
p_pay_element_id => l_pay_element_id,
p_budget_set_id => c_elem_rec.budget_set_id,
p_business_group_id => c_elem_rec.business_group_id,
p_data_extract_id => p_target_data_extract_id,
p_name => c_elem_rec.name,
p_description => c_elem_rec.description,
p_element_value_type => c_elem_rec.element_value_type,
p_formula_id => c_elem_rec.formula_id,
p_overwrite_flag => c_elem_rec.overwrite_flag,
p_required_flag => c_elem_rec.required_flag,
p_follow_salary => c_elem_rec.follow_salary,
p_pay_basis => c_elem_rec.pay_basis,
p_start_date => c_elem_rec.start_date,
p_end_date => c_elem_rec.end_date,
p_processing_type => c_elem_rec.processing_type,
p_period_type => c_elem_rec.period_type,
p_process_period_type => c_elem_rec.process_period_type,
p_max_element_value_type => c_elem_rec.max_element_value_type,
p_max_element_value => c_elem_rec.max_element_value,
p_salary_flag => c_elem_rec.salary_flag,
p_salary_type => c_elem_rec.salary_type,
p_option_flag => c_elem_rec.option_flag,
p_hr_element_type_id => c_elem_rec.hr_element_type_id,
p_attribute_category => c_elem_rec.attribute_category,
p_attribute1 => c_elem_rec.attribute1,
p_attribute2 => c_elem_rec.attribute2,
p_attribute3 => c_elem_rec.attribute3,
p_attribute4 => c_elem_rec.attribute4,
p_attribute5 => c_elem_rec.attribute5,
p_attribute6 => c_elem_rec.attribute6,
p_attribute7 => c_elem_rec.attribute7,
p_attribute8 => c_elem_rec.attribute8,
p_attribute9 => c_elem_rec.attribute9,
p_attribute10 => c_elem_rec.attribute10,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
PSB_PAY_ELEMENT_OPTIONS_PVT.INSERT_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_pay_element_option_id => l_pay_element_option_id,
p_pay_element_id => l_pay_element_id,
p_name => c_elemoptions_rec.name,
p_grade_step => c_elemoptions_rec.grade_step,
p_sequence_number => c_elemoptions_rec.sequence_number,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
PSB_PAY_ELEMENTS_PVT.UPDATE_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_ROW_ID => l_rowid,
P_PAY_ELEMENT_ID => l_pay_element_id,
P_BUSINESS_GROUP_ID => c_elem_rec.business_group_id,
P_DATA_EXTRACT_ID => p_target_data_extract_id,
P_BUDGET_SET_ID => c_elem_rec.budget_set_id,
P_NAME => c_elem_rec.name,
P_DESCRIPTION => c_elem_rec.description,
P_ELEMENT_VALUE_TYPE => c_elem_rec.element_value_type,
P_FORMULA_ID => c_elem_rec.formula_id,
P_OVERWRITE_FLAG => c_elem_rec.overwrite_flag,
P_REQUIRED_FLAG => c_elem_rec.required_flag,
P_FOLLOW_SALARY => c_elem_rec.follow_salary,
P_PAY_BASIS => c_elem_rec.pay_basis,
P_START_DATE => c_elem_rec.start_date,
P_END_DATE => c_elem_rec.end_date,
P_PROCESSING_TYPE => c_elem_rec.processing_type,
P_PERIOD_TYPE => c_elem_rec.period_type,
P_PROCESS_PERIOD_TYPE => c_elem_rec.process_period_type,
P_MAX_ELEMENT_VALUE_TYPE => c_elem_rec.max_element_value_type,
P_MAX_ELEMENT_VALUE => c_elem_rec.max_element_value,
P_SALARY_FLAG => c_elem_rec.salary_flag,
P_SALARY_TYPE => c_elem_rec.salary_type,
P_OPTION_FLAG => c_elem_rec.option_flag,
P_HR_ELEMENT_TYPE_ID => c_elem_rec.hr_element_type_id,
P_ATTRIBUTE_CATEGORY => c_elem_rec.attribute_category,
P_ATTRIBUTE1 => c_elem_rec.attribute1,
P_ATTRIBUTE2 => c_elem_rec.attribute2,
P_ATTRIBUTE3 => c_elem_rec.attribute3,
P_ATTRIBUTE4 => c_elem_rec.attribute4,
P_ATTRIBUTE5 => c_elem_rec.attribute5,
P_ATTRIBUTE6 => c_elem_rec.attribute6,
P_ATTRIBUTE7 => c_elem_rec.attribute7,
P_ATTRIBUTE8 => c_elem_rec.attribute8,
P_ATTRIBUTE9 => c_elem_rec.attribute9,
P_ATTRIBUTE10 => c_elem_rec.attribute10,
P_LAST_UPDATE_DATE => sysdate,
P_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
P_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
PSB_PAY_ELEMENT_OPTIONS_PVT.INSERT_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_pay_element_option_id => l_pay_element_option_id,
p_pay_element_id => l_pay_element_id,
p_name => c_elemoptions_rec.name,
p_grade_step => c_elemoptions_rec.grade_step,
p_sequence_number => c_elemoptions_rec.sequence_number,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
SELECT EMPLOYEE_NUMBER ,
HR_EMPLOYEE_ID ,
FIRST_NAME ,
FULL_NAME ,
KNOWN_AS ,
LAST_NAME ,
MIDDLE_NAMES ,
TITLE
FROM PSB_EMPLOYEES emp
WHERE emp.data_extract_id = p_source_data_extract_id
AND emp.business_group_id = p_source_business_group_id
AND NOT EXISTS (
SELECT 1
FROM PSB_EMPLOYEES
WHERE hr_employee_id = emp.hr_employee_id
AND data_extract_id = p_target_data_extract_id
);
insert into PSB_EMPLOYEES
(employee_id,
data_extract_id,
business_group_id,
employee_number,
hr_employee_id,
first_name,
full_name,
known_as,
last_name,
middle_names,
title,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
values (PSB_EMPLOYEES_S.NEXTVAL,
p_target_data_extract_id,
p_source_business_group_id,
l_emp_num(i),
l_hremp_id(i),
l_first_name(i),
l_full_name(i),
l_known_as(i),
l_last_name(i),
l_middle_names(i),
l_title(i),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID);
SELECT distribution_id, position_id, data_extract_id,
worksheet_id, effective_start_date, effective_end_date,
chart_of_accounts_id, code_combination_id, distribution_percent,
global_default_flag, distribution_default_rule_id, project_id,
task_id, award_id, expenditure_type, expenditure_organization_id,
cost_allocation_key_flex_id, description
FROM psb_position_pay_distributions b
WHERE (((l_year_end_date IS NOT NULL)
AND (((b.effective_start_date <= l_year_end_date)
AND (b.effective_end_date IS NULL))
OR ((b.effective_start_date
BETWEEN l_year_start_date AND l_year_end_date)
OR (b.effective_end_date
BETWEEN l_year_start_date AND l_year_end_date)
OR ((b.effective_start_date < l_year_start_date)
AND (b.effective_end_date > l_year_end_date)))))
OR ((l_year_end_date IS NULL)
AND (nvl(b.effective_end_date, l_year_start_date)
>= l_year_start_date)))
AND position_id = p_position_id
AND worksheet_id IS NULL
AND data_extract_id = p_target_data_extract_id;
SELECT DISTINCT
pos_distr.distribution_id, pos_distr.effective_start_date,
pos_distr.effective_end_date, pos_distr.chart_of_accounts_id,
pos_distr.code_combination_id, pos_distr.distribution_percent
FROM
PSB_POSITION_PAY_DISTRIBUTIONS pos_distr
WHERE EXISTS
(
SELECT 1
FROM psb_ws_account_lines ws_lines
WHERE pos_distr.code_combination_id = ws_lines.code_combination_id
AND ws_lines.salary_account_line = 'Y'
AND ws_lines.ytd_amount <> 0
AND ws_lines.budget_year_id BETWEEN p_from_budget_year_id
AND p_to_budget_year_id
AND ws_lines.position_line_id = pos_line_id
)
AND pos_distr.position_id = pos_id
AND (
(pos_distr.worksheet_id = p_worksheet_id)
OR (pos_distr.worksheet_id IS NULL
AND NOT EXISTS
(SELECT 1
FROM PSB_POSITION_PAY_DISTRIBUTIONS pay_distr
WHERE pay_distr.position_id = pos_distr.position_id
AND pay_distr.worksheet_id = p_worksheet_id
)
)
)
-- AND b.chart_of_accounts_id = p_gl_flex_code
AND pos_distr.code_combination_id IS NOT NULL
AND (((end_date IS NOT NULL)
AND (((pos_distr.effective_start_date <= end_date)
AND (pos_distr.effective_end_date IS NULL))
OR ((pos_distr.effective_start_date BETWEEN start_date and end_date)
OR (pos_distr.effective_end_date BETWEEN start_date and end_date)
OR ((pos_distr.effective_start_date < start_date)
AND (pos_distr.effective_end_date > end_date)))))
OR ((end_date is null)
AND (nvl(pos_distr.effective_end_date, start_date) >= start_date)))
ORDER BY pos_distr.effective_start_date;
SELECT PSB_POSITION_PAY_DISTR_S.NEXTVAL
INTO l_distribution_id
FROM dual;
/*SELECT PSB_POSITION_PAY_DISTR_S.NEXTVAL
INTO l_distribution_id
FROM dual;
SELECT PSB_POSITION_PAY_DISTR_S.NEXTVAL
INTO l_distribution_id
FROM dual;
SELECT PSB_POSITION_PAY_DISTR_S.NEXTVAL
INTO l_distribution_id
FROM dual;
SELECT PSB_POSITION_PAY_DISTR_S.NEXTVAL
INTO l_distribution_id
FROM dual;
DELETE FROM psb_position_pay_distributions
WHERE distribution_id = rec_distribution_id(i);
INSERT INTO psb_position_pay_distributions(
distribution_id, position_id, data_extract_id,
worksheet_id, effective_start_date, effective_end_date,
chart_of_accounts_id, code_combination_id, distribution_percent,
global_default_flag, distribution_default_rule_id, project_id,
task_id, award_id, expenditure_type, expenditure_organization_id,
cost_allocation_key_flex_id, description, last_update_date,
last_updated_by, last_update_login, created_by, creation_date)
VALUES(
ins_distribution_id(i), ins_position_id(i), ins_data_extract_id(i),
ins_worksheet_id(i), ins_start_date(i), ins_end_date(i),
ins_chart_of_accounts_id(i), ins_code_combination_id(i),
ins_distribution_percent(i), ins_global_default_flag(i),
ins_dist_default_rule_id(i), ins_project_id(i), ins_task_id(i),
ins_award_id(i), ins_exp_type(i), ins_exp_org_id(i),
ins_cost_alloc_key_flex_id(i), ins_description(i),
SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID, SYSDATE
);
l_debug_info := ' Bulk Insertion of the manipulated disr completed.';
rec_distribution_id.DELETE;
rec_position_id.DELETE;
rec_data_extract_id.DELETE;
rec_worksheet_id.DELETE;
rec_start_date.DELETE;
rec_end_date.DELETE;
rec_chart_of_accounts_id.DELETE;
rec_code_combination_id.DELETE;
rec_distribution_percent.DELETE;
rec_global_default_flag.DELETE;
rec_dist_default_rule_id.DELETE;
rec_project_id.DELETE;
rec_task_id.DELETE;
rec_award_id.DELETE;
rec_exp_type.DELETE;
rec_exp_org_id.DELETE;
rec_cost_alloc_key_flex_id.DELETE;
rec_description.DELETE;
ins_distribution_id.DELETE;
ins_position_id.DELETE;
ins_data_extract_id.DELETE;
ins_worksheet_id.DELETE;
ins_start_date.DELETE;
ins_end_date.DELETE;
ins_chart_of_accounts_id.DELETE;
ins_code_combination_id.DELETE;
ins_distribution_percent.DELETE;
ins_global_default_flag.DELETE;
ins_dist_default_rule_id.DELETE;
ins_project_id.DELETE;
ins_task_id.DELETE;
ins_award_id.DELETE;
ins_exp_type.DELETE;
ins_exp_org_id.DELETE;
ins_cost_alloc_key_flex_id.DELETE;
ins_description.DELETE;
SELECT effective_start_date INTO l_pos_start_date
FROM psb_positions psb_pos
WHERE psb_pos.position_id = p_source_DE_position_id;
SELECT psb_pos_lines.position_line_id INTO l_pos_line_id
FROM psb_ws_position_lines psb_pos_lines,
psb_ws_lines_positions psb_lines_pos
WHERE psb_pos_lines.position_line_id = psb_lines_pos.position_line_id
AND psb_pos_lines.position_id = p_source_DE_position_id
AND psb_lines_pos.worksheet_id = p_worksheet_id;
SELECT PSB_POSITION_PAY_DISTR_S.NEXTVAL
INTO l_distribution_id
FROM dual;
SELECT PSB_POSITION_PAY_DISTR_S.NEXTVAL
INTO l_distribution_id
FROM dual;
SELECT PSB_POSITION_PAY_DISTR_S.NEXTVAL
INTO l_distribution_id
FROM dual;
SELECT PSB_POSITION_PAY_DISTR_S.NEXTVAL
INTO l_distribution_id
FROM dual;
SELECT PSB_POSITION_PAY_DISTR_S.NEXTVAL
INTO l_distribution_id
FROM dual;
select pos.position_id, pos.position_definition_id, pos.hr_position_id, pos.hr_employee_id,pos.organization_id,
pos.business_group_id, pos.effective_start_date, pos.effective_end_date,
pos.set_of_books_id, pos.vacant_position_flag, pos.availability_status,
pos.transaction_id, pos.transaction_status, pos.new_position_flag,
pos.attribute1, pos.attribute2, pos.attribute3, pos.attribute4, pos.attribute5,
pos.attribute6, pos.attribute7, pos.attribute8, pos.attribute9, pos.attribute10,
pos.attribute11, pos.attribute12, pos.attribute13, pos.attribute14, pos.attribute15,
pos.attribute16, pos.attribute17, pos.attribute18, pos.attribute19, pos.attribute20,
pos.attribute_category, pos.name, wpl.budget_group_id
from PSB_POSITIONS pos,
PSB_WS_POSITION_LINES wpl,
PSB_WS_LINES_POSITIONS wlp
where data_extract_id = p_source_data_extract_id
and wpl.position_id = pos.position_id
and wlp.worksheet_id = p_worksheet_id
and wlp.position_line_id = wpl.position_line_id;
select position_id
from PSB_POSITIONS
where name = posname
and data_extract_id = p_target_data_extract_id
and hr_position_id = hrposid
-- For Bug number 2931727
-- and (hrempid is null or hr_employee_id = hrempid);
(select 1 from psb_positions
where hr_position_id = hrposid
and hr_employee_id = hrempid
and data_extract_id = p_target_data_extract_id))))
or ((hrempid is null) and (hr_employee_id is null)));
select pas.attribute_id,
pas.attribute_value_id,
decode(pas.attribute_value_id,null,pas.attribute_value,patv.attribute_value) attribute_value,
pas.effective_start_date,
pas.effective_end_date
from PSB_POSITION_ASSIGNMENTS pas,
PSB_ATTRIBUTES pat,
PSB_ATTRIBUTE_VALUES patv
where pas.position_id = positionid
and pas.assignment_type = 'ATTRIBUTE'
and ((pas.worksheet_id = p_worksheet_id)
or (worksheet_id is null
and not exists
(select 1 from psb_position_assignments c
where c.position_id = positionid
and c.attribute_id = pas.attribute_id
and c.worksheet_id = p_worksheet_id)))
and pas.attribute_id = pat.attribute_id
and pas.attribute_value_id = patv.attribute_value_id(+);
select a.attribute_value_id
from PSB_ATTRIBUTE_VALUES a,
PSB_ATTRIBUTE_VALUES b
where a.data_extract_id = p_target_data_extract_id
and a.attribute_value = b.attribute_value
and a.attribute_id = b.attribute_id -- added for Bug#4262388
and b.attribute_value_id = attrvalid;
select pas.pay_element_id,
pas.pay_element_option_id,
pas.pay_element_rate_id,
pas.effective_start_date,
pas.effective_end_date,
pas.element_value,
pas.element_value_type,
pas.currency_code,
pas.pay_basis
from PSB_POSITION_ASSIGNMENTS pas,
PSB_PAY_ELEMENTS pe
where pas.position_id = positionid
and pas.assignment_type = 'ELEMENT'
and ((pas.worksheet_id = p_worksheet_id)
or (worksheet_id is null
and not exists
(select 1 from PSB_POSITION_ASSIGNMENTS c
where c.position_id = positionid
and c.pay_element_id = pas.pay_element_id
and c.worksheet_id = p_worksheet_id)))
and pas.pay_element_id = pe.pay_element_id;
select a.pay_element_id
from PSB_PAY_ELEMENTS a,
PSB_PAY_ELEMENTS b
where a.name = b.name
and a.data_extract_id = p_target_data_extract_id
and b.pay_element_id = elemid;
select a.pay_element_option_id
from PSB_PAY_ELEMENT_OPTIONS a,
PSB_PAY_ELEMENT_OPTIONS b
where a.name = b.name
and a.pay_element_id = elemid
and b.pay_element_option_id = elemoptid
and nvl(a.sequence_number, -1) = nvl(b.sequence_number, -1);
select *
from PSB_POSITION_ASSIGNMENTS pas
where pas.position_id = positionid
and pas.assignment_type = 'EMPLOYEE'
and ((pas.worksheet_id = p_worksheet_id)
or (pas.worksheet_id is null
and not exists
(select 1 from PSB_POSITION_ASSIGNMENTS c
where c.position_id = positionid
and c.primary_employee_flag = 'Y'
and c.worksheet_id = p_worksheet_id)));
select a.employee_id
from PSB_EMPLOYEES a,
PSB_EMPLOYEES b
/*For Bug No : 2434152 Start*/
--where a.employee_number = b.employee_number
where a.hr_employee_id = b.hr_employee_id
/*For Bug No : 2434152 End*/
and a.data_extract_id = p_target_data_extract_id
and b.employee_id = empid;
select *
from PSB_POSITION_PAY_DISTRIBUTIONS a
where a.position_id = positionid
and ((a.worksheet_id = p_worksheet_id)
or (a.worksheet_id is null
and not exists
(select 1 from PSB_POSITION_PAY_DISTRIBUTIONS c
where c.position_id = positionid
and c.worksheet_id = p_worksheet_id)));
select PSB_POSITIONS_S.NEXTVAL seq
from dual;
select PSB_POSITION_ASSIGNMENTS_S.NEXTVAL seq
from dual;
select PSB_POSITION_PAY_DISTR_S.NEXTVAL seq
from dual;
PSB_POSITIONS_PVT.Insert_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_rowid => l_rowid,
p_position_id => l_position_id,
p_budget_group_id => c_positions_rec.budget_group_id,
p_data_extract_id => p_target_data_extract_id,
p_position_definition_id => c_positions_rec.position_definition_id,
p_hr_position_id => c_positions_rec.hr_position_id,
p_hr_employee_id => c_positions_rec.hr_employee_id,
p_business_group_id => c_positions_rec.business_group_id,
-- de by org
p_organization_id => c_positions_rec.organization_id,
p_effective_start_date => c_positions_rec.effective_start_date,
p_effective_end_date => c_positions_rec.effective_end_date,
p_set_of_books_id => c_positions_rec.set_of_books_id,
p_vacant_position_flag => c_positions_rec.vacant_position_flag,
p_availability_status => c_positions_rec.availability_status,
p_transaction_id => c_positions_rec.transaction_id,
p_transaction_status => c_positions_rec.transaction_status,
p_attribute1 => c_positions_rec.attribute1,
p_attribute2 => c_positions_rec.attribute2,
p_attribute3 => c_positions_rec.attribute3,
p_attribute4 => c_positions_rec.attribute4,
p_attribute5 => c_positions_rec.attribute5,
p_attribute6 => c_positions_rec.attribute6,
p_attribute7 => c_positions_rec.attribute7,
p_attribute8 => c_positions_rec.attribute8,
p_attribute9 => c_positions_rec.attribute9,
p_attribute10 => c_positions_rec.attribute10,
p_attribute11 => c_positions_rec.attribute11,
p_attribute12 => c_positions_rec.attribute12,
p_attribute13 => c_positions_rec.attribute13,
p_attribute14 => c_positions_rec.attribute14,
p_attribute15 => c_positions_rec.attribute15,
p_attribute16 => c_positions_rec.attribute16,
p_attribute17 => c_positions_rec.attribute17,
p_attribute18 => c_positions_rec.attribute18,
p_attribute19 => c_positions_rec.attribute19,
p_attribute20 => c_positions_rec.attribute20,
p_attribute_category => c_positions_rec.attribute_category,
p_name => c_positions_rec.name);
PSB_POSITION_ASSIGNMENTS_PVT.Insert_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_rowid => l_rowid,
p_position_assignment_id => l_position_assignment_id,
p_data_extract_id => p_target_data_extract_id,
p_worksheet_id => null,
p_position_id => l_position_id,
p_assignment_type => 'ATTRIBUTE',
p_attribute_id => l_attr_val.attribute_id(l_attr_index),
p_attribute_value_id => l_attribute_value_id,
p_attribute_value => l_attr_val.attribute_value(l_attr_index),
p_pay_element_id => null,
p_pay_element_option_id => null,
p_effective_start_date => l_attr_val.effective_start_date(l_attr_index),
p_effective_end_date => l_attr_val.effective_end_date(l_attr_index),
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);
PSB_POSITION_ASSIGNMENTS_PVT.Insert_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_rowid => l_rowid,
p_position_assignment_id => l_position_assignment_id,
p_data_extract_id => p_target_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 => c_posassign_elem_rec.effective_start_date,
p_effective_end_date => c_posassign_elem_rec.effective_end_date,
p_element_value_type => c_posassign_elem_rec.element_value_type,
p_element_value => c_posassign_elem_rec.element_value,
p_currency_code => c_posassign_elem_rec.currency_code,
p_pay_basis => c_posassign_elem_rec.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);
PSB_POSITION_ASSIGNMENTS_PVT.Insert_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_rowid => l_rowid,
p_position_assignment_id => l_position_assignment_id,
p_data_extract_id => p_target_data_extract_id,
p_worksheet_id => null,
p_position_id => l_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 => c_posassign_emp_rec.effective_start_date,
p_effective_end_date => c_posassign_emp_rec.effective_end_date,
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 => c_posassign_emp_rec.primary_employee_flag,
p_global_default_flag => null,
p_assignment_default_rule_id => null,
p_modify_flag => null);
PSB_POSITION_PAY_DISTR_PVT.Insert_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_rowid => l_rowid,
p_distribution_id => l_distribution_id,
p_position_id => l_position_id,
p_data_extract_id => p_target_data_extract_id,
p_worksheet_id => null,
p_effective_start_date => l_year_start_date,
p_effective_end_date => l_year_end_date,
p_chart_of_accounts_id => c_position_distr_rec.chart_of_accounts_id,
p_code_combination_id => l_mapped_ccid,
p_distribution_percent => c_position_distr_rec.distribution_percent,
p_global_default_flag => null,
p_distribution_default_rule_id => null,
p_project_id => null,
p_task_id => null,
p_award_id => null,
p_expenditure_type => null,
p_expenditure_organization_id => null,
p_description => null);
PSB_POSITION_PAY_DISTR_PVT.Insert_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_rowid => l_rowid,
p_distribution_id => l_distribution_id,
p_position_id => l_position_id,
p_data_extract_id => p_target_data_extract_id,
p_worksheet_id => null,
p_effective_start_date => c_position_distr_rec.effective_start_date,
p_effective_end_date => c_position_distr_rec.effective_end_date,
p_chart_of_accounts_id => c_position_distr_rec.chart_of_accounts_id,
p_code_combination_id => c_position_distr_rec.code_combination_id,
p_distribution_percent => c_position_distr_rec.distribution_percent,
p_global_default_flag => null,
p_distribution_default_rule_id => null,
p_project_id => null,
p_task_id => null,
p_award_id => null,
p_expenditure_type => null,
p_expenditure_organization_id => null,
p_description => null);
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_id,
p_data_extract_id => p_target_data_extract_id,
p_budget_group_id => c_positions_rec.budget_group_id,
p_position_definition_id => c_positions_rec.position_definition_id,
p_hr_position_id => c_positions_rec.hr_position_id,
p_hr_employee_id => c_positions_rec.hr_employee_id,
p_business_group_id => c_positions_rec.business_group_id,
p_effective_start_date => c_positions_rec.effective_start_date,
p_effective_end_date => c_positions_rec.effective_end_date,
p_set_of_books_id => c_positions_rec.set_of_books_id,
p_vacant_position_flag => c_positions_rec.vacant_position_flag,
p_availability_status => c_positions_rec.availability_status,
p_transaction_id => c_positions_rec.transaction_id,
p_transaction_status => c_positions_rec.transaction_status,
p_attribute1 => c_positions_rec.attribute1,
p_attribute2 => c_positions_rec.attribute2,
p_attribute3 => c_positions_rec.attribute3,
p_attribute4 => c_positions_rec.attribute4,
p_attribute5 => c_positions_rec.attribute5,
p_attribute6 => c_positions_rec.attribute6,
p_attribute7 => c_positions_rec.attribute7,
p_attribute8 => c_positions_rec.attribute8,
p_attribute9 => c_positions_rec.attribute9,
p_attribute10 => c_positions_rec.attribute10,
p_attribute11 => c_positions_rec.attribute11,
p_attribute12 => c_positions_rec.attribute12,
p_attribute13 => c_positions_rec.attribute13,
p_attribute14 => c_positions_rec.attribute14,
p_attribute15 => c_positions_rec.attribute15,
p_attribute16 => c_positions_rec.attribute16,
p_attribute17 => c_positions_rec.attribute17,
p_attribute18 => c_positions_rec.attribute18,
p_attribute19 => c_positions_rec.attribute19,
p_attribute20 => c_positions_rec.attribute20,
p_attribute_category => c_positions_rec.attribute_category,
p_name => c_positions_rec.name);
TYPE l_update_tbl IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
l_fte_update l_update_tbl;
l_cost_update l_update_tbl;
l_account_update l_update_tbl;
/*select *
from PSB_WS_POSITION_LINES a
where exists
(select 1
from PSB_WS_LINES_POSITIONS b
where b.position_line_id = a.position_line_id
and b.worksheet_id = p_worksheet_id);*/
select a.position_id, a.position_line_id
from PSB_WS_POSITION_LINES a,
PSB_WS_LINES_POSITIONS b
where a.position_line_id = b.position_line_id
and b.worksheet_id = p_worksheet_id;
select code_combination_id, currency_code, sum(nvl(ytd_amount, 0)) annual_amount
from PSB_WS_ACCOUNT_LINES
where position_line_id = poslineid
and budget_year_id = budgetyearid
and end_stage_seq is null
group by code_combination_id, currency_code;
select currency_code, element_set_id,
sum(nvl(ytd_amount,0)) annual_amount,
sum(nvl(period1_amount,0)) period1_amount,
sum(nvl(period2_amount,0)) period2_amount,
sum(nvl(period3_amount,0)) period3_amount,
sum(nvl(period4_amount,0)) period4_amount,
sum(nvl(period5_amount,0)) period5_amount,
sum(nvl(period6_amount,0)) period6_amount,
sum(nvl(period7_amount,0)) period7_amount,
sum(nvl(period8_amount,0)) period8_amount,
sum(nvl(period9_amount,0)) period9_amount,
sum(nvl(period10_amount,0)) period10_amount,
sum(nvl(period11_amount,0)) period11_amount,
sum(nvl(period12_amount,0)) period12_amount
from psb_ws_account_lines
where position_line_id = poslineid
and budget_year_id = budgetyearid
and end_stage_seq is null
group by currency_code, element_set_id;
select sum(nvl(period1_fte, 0)) period1_fte, sum(nvl(period2_fte, 0)) period2_fte,
sum(nvl(period3_fte, 0)) period3_fte, sum(nvl(period4_fte, 0)) period4_fte,
sum(nvl(period5_fte, 0)) period5_fte, sum(nvl(period6_fte, 0)) period6_fte,
sum(nvl(period7_fte, 0)) period7_fte, sum(nvl(period8_fte, 0)) period8_fte,
sum(nvl(period9_fte, 0)) period9_fte, sum(nvl(period10_fte, 0)) period10_fte,
sum(nvl(period11_fte, 0)) period11_fte, sum(nvl(period12_fte, 0)) period12_fte
from PSB_WS_FTE_LINES
where position_line_id = poslineid
and budget_year_id = budgetyearid
and end_stage_seq is null;
select pay_element_id, sum(nvl(element_cost, 0)) element_cost
from PSB_WS_ELEMENT_LINES
where position_line_id = poslineid
and budget_year_id = budgetyearid
and element_set_id = elemsetid
and currency_code = currency
and end_stage_seq is null
group by pay_element_id;
select a.pay_element_id
from PSB_PAY_ELEMENTS a,
PSB_PAY_ELEMENTS b
where a.name = b.name
and a.data_extract_id = p_target_data_extract_id
and b.pay_element_id = elemid;
select a.position_id, a.budget_group_id
from PSB_POSITIONS a,
PSB_POSITIONS b
where (((b.hr_employee_id is null) and
(a.hr_employee_id is null))
or ((b.hr_employee_id is not null )
and (a.hr_employee_id = b.hr_employee_id)))
/* Start bug 3625364 */
/*and a.name = b.name*/
and a.hr_position_id = b.hr_position_id
/* End bug 3625364 */
and a.data_extract_id = p_target_data_extract_id
and b.position_id = posid;
l_position_rec.position_id.delete;
l_position_rec.position_line_id.delete;
UPDATE PSB_POSITION_FTE
SET fte =l_fte_fte(i),
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE position_id = l_fte_position_id(i)
AND nvl(hr_budget_id,-1) = nvl(p_hr_budget_id,-1)
AND budget_revision_id is null
AND base_line_version = l_base_line_version
AND start_date = l_fte_start_date(i)
AND end_date = l_fte_end_date(i);
l_fte_update(i) := 'Y';
l_fte_update(i) := 'N';
IF l_fte_update(i) = 'N' THEN
Modify_Position_FTE
(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_id => l_fte_position_id(i),
p_hr_budget_id => p_hr_budget_id,
p_budget_revision_id => null,
p_base_line_version => l_base_line_version,
p_fte => l_fte_fte(i),
p_start_date => l_fte_start_date(i),
p_end_date => l_fte_end_date(i));
l_fte_position_id.delete;
l_fte_start_date.delete;
l_fte_end_date.delete;
l_fte_fte.delete;
l_fte_update.delete;
UPDATE PSB_POSITION_COSTS
SET element_cost = l_cost_element_cost(i),
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE position_id = l_cost_position_id(i)
AND nvl(hr_budget_id, -1) = nvl(p_hr_budget_id, -1)
AND pay_element_id = l_cost_element_id(i)
AND currency_code = l_cost_currency_code(i)
AND budget_revision_id is null
AND base_line_version = l_base_line_version
AND start_date = l_cost_start_date(i)
AND end_date = l_cost_end_date(i);
l_cost_update(i) := 'Y';
l_cost_update(i) := 'N';
IF l_cost_update(i) = 'N' THEN
Modify_Position_Costs
(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_id => l_cost_position_id(i),
p_hr_budget_id => p_hr_budget_id,
p_pay_element_id => l_cost_element_id(i),
p_budget_revision_id => null,
p_base_line_version => l_base_line_version,
p_start_date => l_cost_start_date(i),
p_end_date => l_cost_end_date(i),
p_currency_code => l_cost_currency_code(i),
p_element_cost => l_cost_element_cost(i));
l_cost_position_id.delete;
l_cost_start_date.delete;
l_cost_end_date.delete;
l_cost_element_id.delete;
l_cost_currency_code.delete;
l_cost_element_cost.delete;
l_cost_update.delete;
UPDATE PSB_POSITION_ACCOUNTS
SET amount = l_account_amount(i),
budget_group_id = l_account_bg_id(i),
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE position_id = l_account_position_id(i)
AND nvl(hr_budget_id, -1) = nvl(p_hr_budget_id, -1)
AND code_combination_id = l_account_cc_id(i)
AND currency_code = l_account_currency_code(i)
AND budget_revision_id is null
AND base_line_version = l_base_line_version
AND start_date = l_account_start_date(i)
AND end_date = l_account_end_date(i);
l_account_update(i) := 'Y';
l_account_update(i) := 'N';
IF l_account_update(i) = 'N' THEN
Modify_Position_Accounts
(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_id => l_account_position_id(i),
p_hr_budget_id => p_hr_budget_id,
p_budget_revision_id => null,
p_budget_group_id => l_account_bg_id(i),
p_base_line_version => l_base_line_version,
p_start_date => l_account_start_date(i),
p_end_date => l_account_end_date(i),
p_code_combination_id => l_account_cc_id(i),
p_currency_code => l_account_currency_code(i),
p_amount => l_account_amount(i));
l_account_position_id.delete;
l_account_start_date.delete;
l_account_end_date.delete;
l_account_cc_id.delete;
l_account_currency_code.delete;
l_account_amount.delete;
l_account_bg_id.delete;
l_account_update.delete;
UPDATE PSB_POSITION_FTE
SET fte = l_fte_fte(i),
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE position_id = l_fte_position_id(i)
AND nvl(hr_budget_id,-1) = nvl(p_hr_budget_id,-1)
AND budget_revision_id is null
AND base_line_version = l_base_line_version
AND start_date = l_fte_start_date(i)
AND end_date = l_fte_end_date(i);
l_fte_update(i) := 'Y';
l_fte_update(i) := 'N';
IF l_fte_update(i) = 'N' THEN
Modify_Position_FTE
(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_id => l_fte_position_id(i),
p_hr_budget_id => p_hr_budget_id,
p_budget_revision_id => null,
p_base_line_version => l_base_line_version,
p_fte => l_fte_fte(i),
p_start_date => l_fte_start_date(i),
p_end_date => l_fte_end_date(i));
l_fte_position_id.delete;
l_fte_start_date.delete;
l_fte_end_date.delete;
l_fte_fte.delete;
l_fte_update.delete;
UPDATE PSB_POSITION_COSTS
SET element_cost = l_cost_element_cost(i),
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE position_id = l_cost_position_id(i)
AND nvl(hr_budget_id, -1) = nvl(p_hr_budget_id, -1)
AND pay_element_id = l_cost_element_id(i)
AND currency_code = l_cost_currency_code(i)
AND budget_revision_id is null
AND base_line_version = l_base_line_version
AND start_date = l_cost_start_date(i)
AND end_date = l_cost_end_date(i);
l_cost_update(i) := 'Y';
l_cost_update(i) := 'N';
IF l_cost_update(i) = 'N' THEN
Modify_Position_Costs
(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_id => l_cost_position_id(i),
p_hr_budget_id => p_hr_budget_id,
p_pay_element_id => l_cost_element_id(i),
p_budget_revision_id => null,
p_base_line_version => l_base_line_version,
p_start_date => l_cost_start_date(i),
p_end_date => l_cost_end_date(i),
p_currency_code => l_cost_currency_code(i),
p_element_cost => l_cost_element_cost(i));
l_cost_position_id.delete;
l_cost_start_date.delete;
l_cost_end_date.delete;
l_cost_element_id.delete;
l_cost_currency_code.delete;
l_cost_element_cost.delete;
l_cost_update.delete;
UPDATE PSB_POSITION_ACCOUNTS
SET amount = l_account_amount(i),
budget_group_id = l_account_bg_id(i),
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE position_id = l_account_position_id(i)
AND nvl(hr_budget_id, -1) = nvl(p_hr_budget_id, -1)
AND code_combination_id = l_account_cc_id(i)
AND currency_code = l_account_currency_code(i)
AND budget_revision_id is null
AND base_line_version = l_base_line_version
AND start_date = l_account_start_date(i)
AND end_date = l_account_end_date(i);
l_account_update(i) := 'Y';
l_account_update(i) := 'N';
IF l_account_update(i) = 'N' THEN
Modify_Position_Accounts
(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_id => l_account_position_id(i),
p_hr_budget_id => p_hr_budget_id,
p_budget_revision_id => null,
p_budget_group_id => l_account_bg_id(i),
p_base_line_version => l_base_line_version,
p_start_date => l_account_start_date(i),
p_end_date => l_account_end_date(i),
p_code_combination_id => l_account_cc_id(i),
p_currency_code => l_account_currency_code(i),
p_amount => l_account_amount(i));
l_account_position_id.delete;
l_account_start_date.delete;
l_account_end_date.delete;
l_account_cc_id.delete;
l_account_currency_code.delete;
l_account_amount.delete;
l_account_bg_id.delete;
l_account_update.delete;
update PSB_POSITION_EVENTS_ALL
set org_id = FND_PROFILE.VALUE('ORG_ID'),
transfer_to_interface = p_transfer_to_interface,
transfer_to_hrms = p_transfer_to_hrms,
interface_last_update_date = sysdate,
interface_last_updated_by = FND_GLOBAL.USER_ID,
interface_last_update_login = FND_GLOBAL.LOGIN_ID,
hrms_last_update_date = sysdate,
hrms_last_updated_by = FND_GLOBAL.USER_ID,
hrms_last_update_login = FND_GLOBAL.LOGIN_ID
where event_type = p_event_type
and source_id = p_source_id
and from_budget_year_id = p_from_budget_year_id
and to_budget_year_id = p_to_budget_year_id
and hr_budget_id is null;
update PSB_POSITION_EVENTS_ALL
set org_id = FND_PROFILE.VALUE('ORG_ID'),
transfer_to_interface = p_transfer_to_interface,
transfer_to_hrms = p_transfer_to_hrms,
interface_last_update_date = sysdate,
interface_last_updated_by = FND_GLOBAL.USER_ID,
interface_last_update_login = FND_GLOBAL.LOGIN_ID,
hrms_last_update_date = sysdate,
hrms_last_updated_by = FND_GLOBAL.USER_ID,
hrms_last_update_login = FND_GLOBAL.LOGIN_ID
where event_type = p_event_type
and source_id = p_source_id
and from_budget_year_id = p_from_budget_year_id
and to_budget_year_id = p_to_budget_year_id
and hr_budget_id = p_hr_budget_id;
INSERT INTO PSB_POSITION_EVENTS_ALL
(position_event_id,
event_type,
source_id,
org_id,
hr_budget_id,
from_budget_year_id,
to_budget_year_id,
transfer_to_interface,
transfer_to_hrms,
interface_created_by,
interface_creation_date,
interface_last_update_date,
interface_last_updated_by,
interface_last_update_login,
hrms_created_by,
hrms_creation_date,
hrms_last_update_date,
hrms_last_updated_by,
hrms_last_update_login)
VALUES (PSB_POSITION_EVENTS_ALL_S.NEXTVAL,
p_event_type,
p_source_id,
FND_PROFILE.VALUE('ORG_ID'),
p_hr_budget_id,
p_from_budget_year_id,
p_to_budget_year_id,
p_transfer_to_interface,
p_transfer_to_hrms,
FND_GLOBAL.USER_ID,
sysdate,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
sysdate,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID) RETURNING position_event_id into l_position_event_id;
select 'Exists'
from dual
where exists
(select 'Exists'
from psb_ws_position_lines wpl, psb_ws_lines_positions wlp
where wpl.position_id = p_position_id
and wlp.position_line_id = wpl.position_line_id
and wlp.worksheet_id = p_source_id);
select 'Exists'
from dual
where exists
(select 'Exists'
from psb_budget_revision_positions brp, psb_budget_revision_pos_lines brpl
where brp.position_id = p_position_id
and brpl.budget_revision_pos_line_id = brp.budget_revision_pos_line_id
and brpl.budget_revision_id = p_source_id);
select 'Exists'
from dual
where exists
(select 'Exists'
from psb_ws_position_lines wpl, psb_ws_lines_positions wlp, psb_ws_account_lines wal
where wpl.position_id = p_position_id
and wlp.position_line_id = wpl.position_line_id
and wlp.worksheet_id = p_source_id
and wal.position_line_id = wpl.position_line_id);
select 'Exists'
from dual
where exists
(select 'Exists'
from psb_position_accounts
where budget_revision_id = p_source_id
and position_id = p_position_id);
select data_extract_id from psb_worksheets where worksheet_id = p_source_id;
select budget_group_id from psb_budget_revisions where budget_revision_id = p_source_id;
select position_id, name, hr_position_id, hr_employee_id
from psb_positions
where data_extract_id = l_data_extract_id;
select name, transaction_id
from psb_positions
where position_id = positionid
and hr_position_id is null;
select a.data_extract_id,
b.business_group_id,
b.position_id_flex_num,
a.budget_calendar_id,
a.budget_group_id,
a.flex_mapping_set_id
from PSB_WORKSHEETS a,
PSB_DATA_EXTRACTS b
where a.worksheet_id = p_worksheet_id
and b.data_extract_id = a.data_extract_id;
Select nvl(root_budget_group_id, budget_group_id) budget_group_id,
nvl(root_set_of_books_id, set_of_books_id) set_of_books_id,
nvl(root_short_name, short_name) short_name,
business_group_id
from psb_budget_groups_v
where budget_group_id = l_budget_group_id;
INSERT INTO PSB_DATA_EXTRACTS
(DATA_EXTRACT_ID, DATA_EXTRACT_NAME, DATA_EXTRACT_METHOD,
SET_OF_BOOKS_ID, BUSINESS_GROUP_ID, BUDGET_GROUP_ID,
POSITION_ID_FLEX_NUM, BASE_SALARY_DATE, REQ_DATA_AS_OF_DATE,
LAST_EXTRACT_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, SYSTEM_DATA_EXTRACT)
VALUES (PSB_DATA_EXTRACTS_S.NEXTVAL, l_data_extract_name, 'CREATE',
l_root_set_of_books_id, l_business_group_id, l_root_budget_group_id,
l_position_id_flex_num, null, sysdate,
sysdate, sysdate, FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID, FND_GLOBAL.USER_ID, sysdate, 'Y') RETURNING data_extract_id into l_system_data_extract_id;
FOR l_data_extract_rec IN (SELECT data_extract_id
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id)
LOOP
l_data_extract_Id := l_data_extract_rec.data_extract_id;
FOR l_att_fte_rec IN (SELECT attribute_id FROM psb_attributes a,
psb_data_extracts b
WHERE a.business_group_id = b.business_group_id
AND a.system_attribute_type = 'FTE'
AND b.data_extract_id = l_data_extract_Id)
LOOP
g_fte_attribute_Id := l_att_fte_rec.attribute_id;
FOR li_csr IN ( SELECT 1 new_bud
FROM dual
WHERE EXISTS (SELECT 1
FROM psb_position_events_all
WHERE event_type = 'BP'
AND hr_budget_id = p_hr_budget_id)) LOOP
lp_num_var := li_csr.new_bud;
PROCEDURE Insert_Position_Accounts
( p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_position_id IN NUMBER,
p_hr_budget_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_code_combination_id IN NUMBER,
p_budget_group_id IN NUMBER,
p_currency_code IN VARCHAR2,
p_amount IN NUMBER,
p_budget_revision_id IN NUMBER,
p_base_line_version IN VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Position_Accounts';
insert into PSB_POSITION_ACCOUNTS
(position_account_line_id, position_id, hr_budget_id, budget_revision_id, budget_group_id,
base_line_version, start_date, end_date, code_combination_id, currency_code,
amount, last_update_date, last_updated_by, last_update_login, created_by, creation_date)
values (PSB_POSITION_ACCOUNTS_S.NEXTVAL, p_position_id, p_hr_budget_id, p_budget_revision_id, p_budget_group_id,
p_base_line_version, p_start_date, p_end_date, p_code_combination_id, p_currency_code,
p_amount, sysdate, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID, FND_GLOBAL.USER_ID, sysdate)
RETURNING position_account_line_id into l_position_account_line_id;
END Insert_Position_Accounts;
PROCEDURE Update_Position_Accounts
( p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_position_account_line_id IN NUMBER,
p_budget_group_id IN NUMBER,
p_start_date IN DATE := FND_API.G_MISS_DATE,
p_end_date IN DATE := FND_API.G_MISS_DATE,
p_amount IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Position_Accounts';
update PSB_POSITION_ACCOUNTS
set amount = p_amount,
budget_group_id = p_budget_group_id,
start_date = decode(p_start_date, FND_API.G_MISS_DATE, start_date, p_start_date),
end_date = decode(p_end_date, FND_API.G_MISS_DATE, end_date, p_end_date),
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where position_account_line_id = p_position_account_line_id;
END Update_Position_Accounts;
PROCEDURE Delete_Position_Accounts
( p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_position_account_line_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Position_Accounts';
delete from PSB_POSITION_ACCOUNTS
where position_account_line_id = p_position_account_line_id;
END Delete_Position_Accounts;
l_updated_record BOOLEAN;
l_update_flag BOOLEAN := TRUE;
select *
from PSB_POSITION_ACCOUNTS
where position_id = p_position_id
and nvl(hr_budget_id, -1) = nvl(p_hr_budget_id, -1)
and code_combination_id = p_code_combination_id
and currency_code = p_currency_code
and nvl(budget_revision_id, -1) = nvl(p_budget_revision_id, -1)
and nvl(base_line_version, FND_API.G_MISS_CHAR) = nvl(p_base_line_version, FND_API.G_MISS_CHAR)
and ((((p_end_date is not null)
and ((start_date <= p_end_date)
and (end_date is null))
or ((start_date between p_start_date and p_end_date)
or (end_date between p_start_date and p_end_date)
or ((start_date < p_start_date)
and (end_date > p_end_date)))))
or ((p_end_date is null)
and (nvl(end_date, p_start_date) >= p_start_date)));
update PSB_POSITION_ACCOUNTS
set amount = p_amount,
budget_group_id = p_budget_group_id,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where position_id = p_position_id
and nvl(hr_budget_id, -1) = nvl(p_hr_budget_id, -1)
and code_combination_id = p_code_combination_id
and currency_code = p_currency_code
and nvl(budget_revision_id, -1) = nvl(p_budget_revision_id, -1)
and nvl(base_line_version, FND_API.G_MISS_CHAR) = nvl(p_base_line_version, FND_API.G_MISS_CHAR)
and start_date = p_start_date
and nvl(end_date, FND_API.G_MISS_DATE) = nvl(p_end_date, FND_API.G_MISS_DATE);
l_update_flag := FALSE;
l_update_flag := FALSE;
if not l_update_flag then --SQL%NOTFOUND then
/*For Bug No : 1808322 End*/
begin
for l_init_index in 1..g_accounts.Count loop
g_accounts(l_init_index).position_account_line_id := null;
g_accounts(l_init_index).delete_flag := null;
g_accounts(g_num_accounts).delete_flag := TRUE;
Insert_Position_Accounts
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_id => p_position_id,
p_hr_budget_id => p_hr_budget_id,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_code_combination_id => p_code_combination_id,
p_budget_group_id => p_budget_group_id,
p_currency_code => p_currency_code,
p_amount => p_amount,
p_budget_revision_id => p_budget_revision_id,
p_base_line_version => p_base_line_version);
l_updated_record := FALSE;
Update_Position_Accounts
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_account_line_id => g_accounts(l_account_index).position_account_line_id,
p_budget_group_id => p_budget_group_id,
p_end_date => p_end_date,
p_amount => p_amount);
g_accounts(l_account_index).delete_flag := FALSE;
Update_Position_Accounts
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_account_line_id => g_accounts(l_account_index).position_account_line_id,
p_budget_group_id => p_budget_group_id,
p_end_date => p_start_date - 1,
p_amount => Prorate (p_original_amount => g_accounts(l_account_index).amount,
p_original_start_date => g_accounts(l_account_index).start_date,
p_original_end_date => g_accounts(l_account_index).end_date,
p_new_start_date => g_accounts(l_account_index).start_date,
p_new_end_date => p_start_date - 1));
l_updated_record := TRUE;
g_accounts(l_account_index).delete_flag := FALSE;
Update_Position_Accounts
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_account_line_id => g_accounts(l_account_index).position_account_line_id,
p_budget_group_id => p_budget_group_id,
p_start_date => p_end_date + 1,
p_amount => Prorate (p_original_amount => g_accounts(l_account_index).amount,
p_original_start_date => g_accounts(l_account_index).start_date,
p_original_end_date => g_accounts(l_account_index).end_date,
p_new_start_date => p_end_date + 1,
p_new_end_date => g_accounts(l_account_index).end_date));
l_updated_record := FALSE;
g_accounts(l_account_index).delete_flag := FALSE;
Insert_Position_Accounts
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_id => p_position_id,
p_hr_budget_id => p_hr_budget_id,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_code_combination_id => p_code_combination_id,
p_budget_group_id => p_budget_group_id,
p_currency_code => p_currency_code,
p_amount => p_amount,
p_budget_revision_id => p_budget_revision_id,
p_base_line_version => p_base_line_version);
if l_updated_record then
begin
Insert_Position_Accounts
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_id => g_accounts(l_account_index).position_id,
p_hr_budget_id => p_hr_budget_id,
p_start_date => p_end_date + 1,
p_end_date => g_accounts(l_account_index).end_date,
p_code_combination_id => g_accounts(l_account_index).code_combination_id,
p_budget_group_id => g_accounts(l_account_index).budget_group_id,
p_currency_code => g_accounts(l_account_index).currency_code,
p_amount => Prorate (p_original_amount => g_accounts(l_account_index).amount,
p_original_start_date => g_accounts(l_account_index).start_date,
p_original_end_date => g_accounts(l_account_index).end_date,
p_new_start_date => p_end_date + 1,
p_new_end_date => g_accounts(l_account_index).end_date),
p_budget_revision_id => g_accounts(l_account_index).budget_revision_id,
p_base_line_version => g_accounts(l_account_index).base_line_version);
Update_Position_Accounts
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_account_line_id => g_accounts(l_account_index).position_account_line_id,
p_budget_group_id => p_budget_group_id,
p_start_date => p_end_date + 1,
p_end_date => g_accounts(l_account_index).end_date,
p_amount => Prorate (p_original_amount => g_accounts(l_account_index).amount,
p_original_start_date => g_accounts(l_account_index).start_date,
p_original_end_date => g_accounts(l_account_index).end_date,
p_new_start_date => p_end_date + 1,
p_new_end_date => g_accounts(l_account_index).end_date));
g_accounts(l_account_index).delete_flag := FALSE;
if g_accounts(l_account_index).delete_flag then
begin
Delete_Position_Accounts
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_account_line_id => g_accounts(l_account_index).position_account_line_id);
PROCEDURE Insert_Position_Costs
( p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_position_id IN NUMBER,
p_hr_budget_id IN NUMBER,
p_pay_element_id IN NUMBER,
p_budget_revision_id IN NUMBER,
p_base_line_version IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
p_currency_code IN VARCHAR2,
p_element_cost IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Position_Costs';
insert into PSB_POSITION_COSTS
(position_element_line_id, position_id, hr_budget_id, pay_element_id, budget_revision_id,
base_line_version, currency_code, start_date, end_date,
element_cost, last_update_date, last_updated_by, last_update_login, created_by, creation_date)
values (PSB_POSITION_COSTS_S.NEXTVAL, p_position_id, p_hr_budget_id, p_pay_element_id, p_budget_revision_id,
p_base_line_version, p_currency_code, p_start_date, p_end_date,
p_element_cost, sysdate, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID, FND_GLOBAL.USER_ID, sysdate)
RETURNING position_element_line_id INTO l_position_element_line_id;
END Insert_Position_Costs;
PROCEDURE Update_Position_Costs
( p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_position_element_line_id IN NUMBER,
p_start_date IN DATE := FND_API.G_MISS_DATE,
p_end_date IN DATE := FND_API.G_MISS_DATE,
p_element_cost IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Position_Costs';
update PSB_POSITION_COSTS
set element_cost = p_element_cost,
start_date = decode(p_start_date, FND_API.G_MISS_DATE, start_date, p_start_date),
end_date = decode(p_end_date, FND_API.G_MISS_DATE, end_date, p_end_date),
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where position_element_line_id = p_position_element_line_id;
END Update_Position_Costs;
PROCEDURE Delete_Position_Costs
( p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_position_element_line_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Position_Costs';
delete from PSB_POSITION_COSTS
where position_element_line_id = p_position_element_line_id;
END Delete_Position_Costs;
l_updated_record BOOLEAN;
l_update_flag BOOLEAN := TRUE;
select *
from PSB_POSITION_COSTS
where position_id = p_position_id
and nvl(hr_budget_id, -1) = nvl(p_hr_budget_id, -1)
and pay_element_id = p_pay_element_id
and currency_code = p_currency_code
and nvl(budget_revision_id, -1) = nvl(p_budget_revision_id, -1)
and nvl(base_line_version, FND_API.G_MISS_CHAR) = nvl(p_base_line_version, FND_API.G_MISS_CHAR)
and ((((p_end_date is not null)
and ((start_date <= p_end_date)
and (end_date is null))
or ((start_date between p_start_date and p_end_date)
or (end_date between p_start_date and p_end_date)
or ((start_date < p_start_date)
and (end_date > p_end_date)))))
or ((p_end_date is null)
and (nvl(end_date, p_start_date) >= p_start_date)));
update PSB_POSITION_COSTS
set element_cost = p_element_cost,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where position_id = p_position_id
and nvl(hr_budget_id, -1) = nvl(p_hr_budget_id, -1)
and pay_element_id = p_pay_element_id
and currency_code = p_currency_code
and nvl(budget_revision_id, -1) = nvl(p_budget_revision_id, -1)
and nvl(base_line_version, FND_API.G_MISS_CHAR) = nvl(p_base_line_version, FND_API.G_MISS_CHAR)
and start_date = p_start_date
and nvl(end_date, FND_API.G_MISS_DATE) = nvl(p_end_date, FND_API.G_MISS_DATE);
l_update_flag := FALSE;
l_update_flag := FALSE;
if not l_update_flag then --if SQL%NOTFOUND then
/*For Bug No : 1808322 End*/
begin
for l_init_index in 1..g_costs.Count loop
g_costs(l_init_index).position_element_line_id := null;
g_costs(l_init_index).delete_flag := null;
g_costs(g_num_costs).delete_flag := TRUE;
Insert_Position_Costs
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_id => p_position_id,
p_hr_budget_id => p_hr_budget_id,
p_pay_element_id => p_pay_element_id,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_currency_code => p_currency_code,
p_element_cost => p_element_cost,
p_budget_revision_id => p_budget_revision_id,
p_base_line_version => p_base_line_version);
l_updated_record := FALSE;
Update_Position_Costs
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_element_line_id => g_costs(l_cost_index).position_element_line_id,
p_end_date => p_end_date,
p_element_cost => p_element_cost);
g_costs(l_cost_index).delete_flag := FALSE;
Update_Position_Costs
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_element_line_id => g_costs(l_cost_index).position_element_line_id,
p_end_date => p_start_date - 1,
p_element_cost => Prorate (p_original_amount => g_costs(l_cost_index).element_cost,
p_original_start_date => g_costs(l_cost_index).start_date,
p_original_end_date => g_costs(l_cost_index).end_date,
p_new_start_date => g_costs(l_cost_index).start_date,
p_new_end_date => p_start_date - 1));
l_updated_record := TRUE;
g_costs(l_cost_index).delete_flag := FALSE;
Update_Position_Costs
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_element_line_id => g_costs(l_cost_index).position_element_line_id,
p_start_date => p_end_date + 1,
p_element_cost => Prorate (p_original_amount => g_costs(l_cost_index).element_cost,
p_original_start_date => g_costs(l_cost_index).start_date,
p_original_end_date => g_costs(l_cost_index).end_date,
p_new_start_date => p_end_date + 1,
p_new_end_date => g_costs(l_cost_index).end_date));
l_updated_record := FALSE;
g_costs(l_cost_index).delete_flag := FALSE;
Insert_Position_Costs
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_id => p_position_id,
p_hr_budget_id => p_hr_budget_id,
p_pay_element_id => p_pay_element_id,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_currency_code => p_currency_code,
p_element_cost => p_element_cost,
p_budget_revision_id => p_budget_revision_id,
p_base_line_version => p_base_line_version);
if l_updated_record then
begin
Insert_Position_Costs
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_id => g_costs(l_cost_index).position_id,
p_hr_budget_id => p_hr_budget_id,
p_pay_element_id => g_costs(l_cost_index).pay_element_id,
p_start_date => p_end_date + 1,
p_end_date => g_costs(l_cost_index).end_date,
p_currency_code => g_costs(l_cost_index).currency_code,
p_element_cost => Prorate (p_original_amount => g_costs(l_cost_index).element_cost,
p_original_start_date => g_costs(l_cost_index).start_date,
p_original_end_date => g_costs(l_cost_index).end_date,
p_new_start_date => p_end_date + 1,
p_new_end_date => g_costs(l_cost_index).end_date),
p_budget_revision_id => g_costs(l_cost_index).budget_revision_id,
p_base_line_version => g_costs(l_cost_index).base_line_version);
Update_Position_Costs
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_element_line_id => g_costs(l_cost_index).position_element_line_id,
p_start_date => p_end_date + 1,
p_end_date => g_costs(l_cost_index).end_date,
p_element_cost => Prorate (p_original_amount => g_costs(l_cost_index).element_cost,
p_original_start_date => g_costs(l_cost_index).start_date,
p_original_end_date => g_costs(l_cost_index).end_date,
p_new_start_date => p_end_date + 1,
p_new_end_date => g_costs(l_cost_index).end_date));
g_costs(l_cost_index).delete_flag := FALSE;
if g_costs(l_cost_index).delete_flag then
begin
Delete_Position_Costs
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_element_line_id => g_costs(l_cost_index).position_element_line_id);
PROCEDURE Insert_Position_FTE
( p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_position_id IN NUMBER,
p_hr_budget_id IN NUMBER,
p_budget_revision_id IN NUMBER,
p_base_line_version IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
p_fte IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Position_FTE';
insert into PSB_POSITION_FTE
(position_fte_line_id, position_id, hr_budget_id, budget_revision_id, base_line_version,
start_date, end_date, fte, last_update_date, last_updated_by, last_update_login,
created_by, creation_date)
VALUES (PSB_POSITION_FTE_S.NEXTVAL, p_position_id, p_hr_budget_id, p_budget_revision_id, p_base_line_version,
p_start_date, p_end_date, nvl(p_fte, 0), sysdate, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID, sysdate) RETURNING position_fte_line_id INTO l_position_fte_line_id;
END Insert_Position_FTE;
PROCEDURE Update_Position_FTE
( p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_position_fte_line_id IN NUMBER,
p_start_date IN DATE := FND_API.G_MISS_DATE,
p_end_date IN DATE := FND_API.G_MISS_DATE,
p_fte IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Position_FTE';
update PSB_POSITION_FTE
set fte = nvl(p_fte, 0),
start_date = decode(p_start_date, FND_API.G_MISS_DATE, start_date, p_start_date),
end_date = decode(p_end_date, FND_API.G_MISS_DATE, end_date, p_end_date),
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where position_fte_line_id = p_position_fte_line_id;
END Update_Position_FTE;
PROCEDURE Delete_Position_FTE
( p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_position_fte_line_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Position_FTE';
delete from PSB_POSITION_FTE
where position_fte_line_id = p_position_fte_line_id;
END Delete_Position_FTE;
l_updated_record BOOLEAN;
l_update_flag BOOLEAN := TRUE;
select *
from PSB_POSITION_FTE
where position_id = p_position_id
and nvl(hr_budget_id, -1) = nvl(p_hr_budget_id, -1)
and nvl(budget_revision_id, -1) = nvl(p_budget_revision_id, -1)
and nvl(base_line_version, FND_API.G_MISS_CHAR) = nvl(p_base_line_version, FND_API.G_MISS_CHAR)
and ((((p_end_date is not null)
and ((start_date <= p_end_date)
and (end_date is null))
or ((start_date between p_start_date and p_end_date)
or (end_date between p_start_date and p_end_date)
or ((start_date < p_start_date)
and (end_date > p_end_date)))))
or ((p_end_date is null)
and (nvl(end_date, p_start_date) >= p_start_date)));
update PSB_POSITION_FTE
set fte = nvl(p_fte, 0),
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where position_id = p_position_id
and nvl(hr_budget_id, -1) = nvl(p_hr_budget_id, -1)
and nvl(budget_revision_id, -1) = nvl(p_budget_revision_id, -1)
and nvl(base_line_version, FND_API.G_MISS_CHAR) = nvl(p_base_line_version, FND_API.G_MISS_CHAR)
and start_date = p_start_date
and nvl(end_date, FND_API.G_MISS_DATE) = nvl(p_end_date, FND_API.G_MISS_DATE);
l_update_flag := FALSE;
l_update_flag := FALSE;
if not l_update_flag then --if SQL%NOTFOUND then
/*For Bug No : 1808322 End*/
begin
for l_init_index in 1..g_fte.Count loop
g_fte(l_init_index).position_fte_line_id := null;
g_fte(l_init_index).delete_flag := null;
g_fte(g_num_fte).delete_flag := TRUE;
Insert_Position_FTE
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_id => p_position_id,
p_hr_budget_id => p_hr_budget_id,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_fte => p_fte,
p_budget_revision_id => p_budget_revision_id,
p_base_line_version => p_base_line_version);
l_updated_record := FALSE;
Update_Position_FTE
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_fte_line_id => g_fte(l_fte_index).position_fte_line_id,
p_end_date => p_end_date,
p_fte => p_fte);
g_fte(l_fte_index).delete_flag := FALSE;
Update_Position_FTE
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_fte_line_id => g_fte(l_fte_index).position_fte_line_id,
p_end_date => p_start_date - 1,
p_fte => g_fte(l_fte_index).fte);
l_updated_record := TRUE;
g_fte(l_fte_index).delete_flag := FALSE;
Update_Position_FTE
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_fte_line_id => g_fte(l_fte_index).position_fte_line_id,
p_start_date => p_end_date + 1,
p_fte => g_fte(l_fte_index).fte);
l_updated_record := FALSE;
g_fte(l_fte_index).delete_flag := FALSE;
Insert_Position_FTE
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_id => p_position_id,
p_hr_budget_id => p_hr_budget_id,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_fte => p_fte,
p_budget_revision_id => p_budget_revision_id,
p_base_line_version => p_base_line_version);
if l_updated_record then
begin
Insert_Position_FTE
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_id => g_fte(l_fte_index).position_id,
p_hr_budget_id => p_hr_budget_id,
p_start_date => p_end_date + 1,
p_end_date => g_fte(l_fte_index).end_date,
p_fte => g_fte(l_fte_index).fte,
p_budget_revision_id => g_fte(l_fte_index).budget_revision_id,
p_base_line_version => g_fte(l_fte_index).base_line_version);
Update_Position_FTE
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_fte_line_id => g_fte(l_fte_index).position_fte_line_id,
p_start_date => p_end_date + 1,
p_end_date => g_fte(l_fte_index).end_date,
p_fte => g_fte(l_fte_index).fte);
g_fte(l_fte_index).delete_flag := FALSE;
if g_fte(l_fte_index).delete_flag then
begin
Delete_Position_FTE
(p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_fte_line_id => g_fte(l_fte_index).position_fte_line_id);
select short_name, nvl(organization_id, business_group_id) organization_id,
effective_start_date, effective_end_date
from PSB_BUDGET_GROUPS
where budget_group_type = 'R'
start with budget_group_id = p_budget_group_id
connect by prior budget_group_id = parent_budget_group_id;
select pc.proc_period_type
from pqh_psb_budgets pb, pay_calendars pc
where pb.budget_id = p_hr_budget_id
and pc.period_set_name = pb.period_set_name;
select min(period_distribution_type) psb_period_type
from psb_budget_periods
where budget_period_id between p_from_budget_year_id and p_to_budget_year_id
and budget_period_type = 'Y';
select meaning from fnd_lookups
where lookup_type = 'PSB_PERIOD_DISTRIBUTION_TYPES'
and lookup_code = l_psb_period_type;
select tpt.display_period_type
from per_time_period_types tpt, per_time_period_rules tpr
where tpr.number_per_fiscal_year = tpt.number_per_fiscal_year
and tpr.proc_period_type = l_hrms_period_type;
select b.pay_element_id
from PSB_PAY_ELEMENTS a,
PSB_PAY_ELEMENTS b
where a.pay_element_id = p_element_id
and b.data_extract_id = p_data_extract_id
and b.name = a.name;
select 'Exists'
from dual
where exists
(select 'Exists'
from psb_ws_lines_positions wlp, psb_ws_position_lines wpl, psb_position_assignments ppa
where wlp.worksheet_id = p_source_id
and wpl.position_line_id = wlp.position_line_id
and ppa.position_id = wpl.position_id
and ppa.pay_element_id = l_element_id
/* For Bug No. 2599262 : Start */
and (ppa.worksheet_id = p_source_id OR ppa.worksheet_id IS NULL)
/* For Bug No. 2599262 : End */
);
select 'Exists'
from dual
where exists
(select 'Exists'
from psb_budget_revision_pos_lines brpl, psb_budget_revision_positions brp, psb_position_assignments ppa
where brpl.budget_revision_id = p_source_id
and brp.budget_revision_pos_line_id = brpl.budget_revision_pos_line_id
and ppa.position_id = brp.position_id
and ppa.pay_element_id = p_element_id
/* For Bug No. 2599262 : Start */
and (ppa.worksheet_id = p_source_id OR ppa.worksheet_id IS NULL)
/* For Bug No. 2599262 : End */
);
select pay_freq_payroll_id
from hr_all_positions_f
where position_id = p_hr_position_id
/*For Bug No : 2292003 Start*/
--and p_effective_start_date between effective_start_date and effective_end_date
and ((p_effective_start_date between effective_start_date and effective_end_date) OR
(p_effective_end_date between effective_start_date and effective_end_date)
);
select payroll_id
from per_all_assignments_f
where position_id = p_hr_position_id
and person_id in
(select hr_employee_id from PSB_POSITIONS
where hr_position_id = p_hr_position_id
and data_extract_id = p_data_extract_id)
/*For Bug No : 2292003 Start*/
--and p_effective_start_date between effective_start_date and effective_end_date
and ((p_effective_start_date between effective_start_date and effective_end_date) OR
(p_effective_end_date between effective_start_date and effective_end_date)
)
/*For Bug No : 2292003 End*/
and assignment_type = 'E';
select position_id, name, hr_position_id, hr_employee_id, effective_start_date
from psb_positions
where data_extract_id = p_data_extract_id;
select position_id, name, hr_position_id, hr_employee_id, effective_start_date
from psb_positions
where data_extract_id = p_system_data_extract_id;
select name, transaction_id
from psb_positions
where position_id = positionid
and hr_position_id is null;
select pay_element_id, name
from psb_pay_elements
where data_extract_id = p_system_data_extract_id
and budget_set_id is null;
select ppe.pay_element_id, ppe.budget_set_id, sum(nvl(ppc.element_cost,0)) element_cost
from psb_pay_elements ppe,
psb_position_costs ppc,
psb_positions pp
where ppe.data_extract_id = p_data_extract_id
and ppc.pay_element_id = ppe.pay_element_id
and pp.data_extract_id = p_data_extract_id
and pp.hr_position_id = p_hr_position_id
and ppc.position_id = pp.position_id
and nvl(ppc.hr_budget_id, -1) = nvl(p_hr_budget_id, -1)
and ((p_event_type = 'BP' and ppc.base_line_version = 'C')
or (p_event_type = 'BR' and ppc.budget_revision_id = p_source_id))
and ppc.currency_code = p_currency_code
and ((ppc.start_date between p_start_date and p_end_date)
or (ppc.end_date between p_start_date and p_end_date)
or ((ppc.start_date < p_start_date) and (ppc.end_date > p_end_date)))
group by ppe.budget_set_id, ppe.pay_element_id;
select distinct c.pay_element_id, b.code_combination_id,
b.distribution_percent
from PSB_PAY_ELEMENTS c, PSB_POSITION_PAY_DISTRIBUTIONS b
where c.data_extract_id = p_data_extract_id
and c.salary_flag = 'Y'
and b.position_id in
(select a.position_id from PSB_POSITIONS a
where a.hr_position_id = p_hr_position_id
and a.data_extract_id = p_data_extract_id)
and exists
(select 1 from PSB_POSITION_ASSIGNMENTS d
where d.position_id = b.position_id and d.pay_element_id = c.pay_element_id)
and ((b.worksheet_id = p_source_id)
or (b.worksheet_id is null
and not exists
(select 1 from PSB_POSITION_PAY_DISTRIBUTIONS c
where c.position_id = b.position_id
and c.worksheet_id = p_source_id)))
and b.chart_of_accounts_id = p_gl_flex_code
and b.code_combination_id is not null
and (((p_end_date is not null)
and (((b.effective_start_date <= p_end_date)
and (b.effective_end_date is null))
or ((b.effective_start_date between p_start_date and p_end_date)
or (b.effective_end_date between p_start_date and p_end_date)
or ((b.effective_start_date < p_start_date)
and (b.effective_end_date > p_end_date)))))
or ((p_end_date is null)
and (nvl(b.effective_end_date, p_start_date) >= p_start_date)))
order by b.distribution_percent desc;
select distinct 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.code_combination_id, a.distribution_percent, e.pay_element_id,
e.follow_salary, /* bug No: 3666828 */ e.name
from PSB_PAY_ELEMENTS e, PSB_PAY_ELEMENT_DISTRIBUTIONS a,
PSB_ELEMENT_POS_SET_GROUPS b,
PSB_SET_RELATIONS c,
PSB_BUDGET_POSITIONS d
where e.data_extract_id = p_data_extract_id
and e.salary_flag <> 'Y'
and a.chart_of_accounts_id = p_gl_flex_code
and (((a.effective_start_date <= p_end_date)
and (a.effective_end_date is null))
or ((a.effective_start_date between p_start_date and p_end_date)
or (a.effective_end_date between p_start_date and p_end_date)
or ((a.effective_start_date < p_start_date)
and (a.effective_end_date > p_end_date))))
and a.position_set_group_id = b.position_set_group_id
and b.position_set_group_id = c.position_set_group_id
and b.pay_element_id = e.pay_element_id
and c.account_position_set_id = d.account_position_set_id
and exists
(select 1 from PSB_POSITION_ASSIGNMENTS g
where g.position_id = d.position_id
and g.pay_element_id = e.pay_element_id)
and d.position_id in
(select f.position_id from PSB_POSITIONS f
where f.hr_position_id = p_hr_position_id
and f.data_extract_id = p_data_extract_id);
select version_number, budget_version_id, object_version_number, date_from, date_to,
budget_unit1_value, budget_unit2_value, budget_unit3_value
from pqh_psb_budget_versions
where budget_id = p_hr_budget_id
and version_number =
(select max(version_number) from pqh_psb_budget_versions
where budget_id = p_hr_budget_id);
select version_number, budget_version_id, object_version_number
from pqh_psb_budget_versions
where budget_id = p_hr_budget_id
and date_from = p_start_date
and date_to = p_end_date;
select sum(ppc.element_cost) total_cost
from psb_positions pp, psb_position_costs ppc
where pp.data_extract_id = p_system_data_extract_id
and exists
(select 'Exists'
from psb_positions pp1, psb_ws_position_lines wpl, psb_ws_lines_positions wlp
where pp1.data_extract_id = p_data_extract_id
and pp1.hr_position_id = pp.hr_position_id
and wpl.position_id = pp1.position_id
and wlp.position_line_id = wpl.position_line_id
and wlp.worksheet_id = p_source_id)
and ppc.position_id = pp.position_id
and ppc.hr_budget_id = p_hr_budget_id
and ppc.base_line_version = 'O'
and ppc.currency_code = p_currency_code
and ppc.budget_revision_id is null
and ((ppc.start_date between p_start_date and p_end_date)
or (ppc.end_date between p_start_date and p_end_date)
or ((ppc.start_date < p_start_date) and (ppc.end_date > p_end_date)));
select sum(decode(b.revision_type, 'I', b.revision_amount,
'D', -b.revision_amount)) revision_amount
from psb_budget_revision_lines a,
psb_budget_revision_accounts b
where a.budget_revision_id = p_source_id
and b.budget_revision_acct_line_id = a.budget_revision_acct_line_id
and b.position_id is not null;
select sum(avg(ppf.fte)) total_fte
from psb_positions pp, psb_position_fte ppf
where pp.data_extract_id = p_system_data_extract_id
and exists
(select 'Exists'
from psb_positions pp1, psb_ws_position_lines wpl, psb_ws_lines_positions wlp
where pp1.data_extract_id = p_data_extract_id
and pp1.hr_position_id = pp.hr_position_id
and wpl.position_id = pp1.position_id
and wlp.position_line_id = wpl.position_line_id
and wlp.worksheet_id = p_source_id)
and ppf.position_id = pp.position_id
and ppf.hr_budget_id = p_hr_budget_id
and ppf.base_line_version = 'O'
and ppf.budget_revision_id is null
and ((ppf.start_date between p_start_date and p_end_date)
or (ppf.end_date between p_start_date and p_end_date)
or ((ppf.start_date < p_start_date) and (ppf.end_date > p_end_date)))
group by ppf.position_id;
select brp.position_id, brp.effective_start_date, brp.effective_end_date,
brp.revision_type, brp.revision_value_type, brp.revision_value
from psb_budget_revision_pos_lines brpl,
psb_budget_revision_positions brp
where brpl.budget_revision_id = p_source_id
and brp.budget_revision_pos_line_id = brpl.budget_revision_pos_line_id;
SELECT position_id
FROM psb_ws_position_lines a, psb_ws_lines_positions b
WHERE worksheet_id = p_source_id
AND a.position_line_id = b.position_line_id;
l_position_id_tbl.DELETE;
pqh_psb_interface_api.update_budget_version
(p_validate => false,
p_budget_id => p_hr_budget_id,
p_budget_version_id => l_budget_version_id,
p_version_number => l_version_number,
p_date_from => l_date_from,
p_date_to => l_date_to,
p_transfered_to_gl_flag => 'N',
p_xfer_to_other_apps_cd => 'N',
p_object_version_number => l_object_version_number,
p_budget_unit1_value => l_budget_unit1_value,
p_budget_unit2_value => l_budget_unit2_value,
p_budget_unit3_value => l_budget_unit3_value,
p_effective_date => sysdate);
add_message('PSB', 'PSB_PQH_UPDATE_BUDGET_VERSION');
select dflt_budget_element_id,
element_type_id,
dflt_dist_percentage
from pqh_psb_dflt_budget_elements
where dflt_budget_set_id = p_dflt_budget_set_id;
select *
from pqh_psb_budget_elements
where budget_set_id = p_pqh_budget_set_id
and element_type_id = elemtypeid;
select *
from pqh_psb_budget_fund_srcs
where budget_element_id = l_pqh_budget_element_id
and cost_allocation_keyflex_id = l_cost_keyflex_id;
pqh_psb_interface_api.update_budget_element
(p_validate => false,
p_budget_element_id => c_pqh_budget_elements_rec.budget_element_id,
p_budget_set_id => p_pqh_budget_set_id,
p_element_type_id => c_dflt_budget_elem_rec.element_type_id,
p_distribution_percentage => c_dflt_budget_elem_rec.dflt_dist_percentage,
p_object_version_number => c_pqh_budget_elements_rec.object_version_number);
add_message('PSB', 'PSB_PQH_UPDATE_BUDGET_ELEMENT');
pqh_psb_interface_api.update_budget_fund_src
(p_validate => false,
p_budget_fund_src_id => c_pqh_fund_srcs_rec.budget_fund_src_id,
p_budget_element_id => l_pqh_budget_element_id,
p_cost_allocation_keyflex_id => l_cost_keyflex_id,
p_distribution_percentage => g_budgetset_dists(l_dist_index).percent,
p_object_version_number => c_pqh_fund_srcs_rec.object_version_number);
add_message('PSB', 'PSB_PQH_UPDATE_BUDGET_FUND_SRC');
select a.start_date, a.end_date, a.fte, b.cost
from
(
select ppf.start_date,
ppf.end_date,
sum(ppf.fte) fte
from psb_position_fte ppf,
psb_positions pp
where pp.data_extract_id = p_system_data_extract_id
and pp.hr_position_id = p_hr_position_id
and ppf.position_id = pp.position_id
and ppf.hr_budget_id = p_hr_budget_id
and ((p_event_type = 'BP' and ppf.base_line_version = 'C')
or (p_event_type = 'BR' and ppf.budget_revision_id = p_source_id))
and ((ppf.start_date between p_start_date and p_end_date)
or (ppf.end_date between p_start_date and p_end_date)
or ((ppf.start_date < p_start_date) and (ppf.end_date > p_end_date)))
group by ppf.start_date, ppf.end_date
) a,
(select ppc.start_date, ppc.end_date, sum(ppc.element_cost) cost
from psb_position_costs ppc, psb_positions pp
where pp.data_extract_id = p_system_data_extract_id
and pp.hr_position_id = p_hr_position_id
and ppc.position_id = pp.position_id
and ppc.hr_budget_id = p_hr_budget_id
and ((p_event_type = 'BP' and ppc.base_line_version = 'C')
or (p_event_type = 'BR' and ppc.budget_revision_id = p_source_id))
and ppc.currency_code = p_currency_code
and ((ppc.start_date between p_start_date and p_end_date)
or (ppc.end_date between p_start_date and p_end_date)
or ((ppc.start_date < p_start_date) and (ppc.end_date > p_end_date)))
group by ppc.start_date, ppc.end_date
) b
where b.start_date between a.start_date and a.end_date
or b.end_date between a.start_date and a.end_date
or ((b.start_date < a.start_date) and (b.end_date > a.end_date));
select *
from pqh_psb_budget_periods
where budget_detail_id = p_pqh_budget_detail_id
and start_time_period_id = start_period_id
and end_time_period_id = end_period_id;
select pt.time_period_id, pt.start_date, pt.end_date
from per_time_periods pt, pqh_psb_budgets pb
where pb.budget_id = p_hr_budget_id
and pt.period_set_name = pb.period_set_name
and startdate between pt.start_date and pt.end_date;
select pt.time_period_id, pt.start_date, pt.end_date
from per_time_periods pt, pqh_psb_budgets pb
where pb.budget_id = p_hr_budget_id
and pt.period_set_name = pb.period_set_name
and ((pt.start_date between startdate and enddate)
or (pt.end_date between startdate and enddate)
or ((pt.start_date < startdate) and (pt.end_date > enddate)));
select ppe.budget_set_id hr_budget_set_id,
sum(nvl(ppc.element_cost,0)) budget_set_cost
from psb_pay_elements ppe,
psb_position_costs ppc,
psb_positions pp
where ppe.data_extract_id = p_system_data_extract_id
and pp.hr_position_id = p_hr_position_id
and pp.data_extract_id = p_system_data_extract_id
and ppc.position_id = pp.position_id
and ppc.pay_element_id = ppe.pay_element_id
and ppc.hr_budget_id = p_hr_budget_id
and ((p_event_type = 'BP' and ppc.base_line_version = 'C')
or (p_event_type = 'BR' and ppc.budget_revision_id = p_source_id))
and ppc.currency_code = p_currency_code
and ((ppc.start_date between startdate and enddate)
or (ppc.end_date between startdate and enddate)
or ((ppc.start_date < startdate) and (ppc.end_date > enddate)))
group by ppe.budget_set_id;
select *
from pqh_psb_budget_sets
where budget_period_id = l_pqh_budget_period_id
and dflt_budget_set_id = hrbudgetsetid;
pqh_psb_interface_api.update_budget_period
(p_validate => false,
p_budget_period_id => c_pqh_budget_periods_rec.budget_period_id,
p_budget_detail_id => c_pqh_budget_periods_rec.budget_detail_id,
p_start_time_period_id => c_pqh_budget_periods_rec.start_time_period_id,
p_end_time_period_id => c_pqh_budget_periods_rec.end_time_period_id,
p_budget_unit1_value_type_cd => 'V',
p_budget_unit1_value => l_budget_unit1_value,
p_budget_unit2_value_type_cd => 'V',
p_budget_unit2_value => l_budget_unit2_value,
p_budget_unit3_value_type_cd => 'V',
p_budget_unit3_value => l_budget_unit3_value,
p_object_version_number => c_pqh_budget_periods_rec.object_version_number);
add_message('PSB', 'PSB_PQH_UPDATE_BUDGET_PERIOD');
pqh_psb_interface_api.update_budget_set
(p_validate => false,
p_budget_set_id => c_pqh_budget_sets_rec.budget_set_id,
p_dflt_budget_set_id => l_bs_cost.hr_budget_set_id(l_bs_index),
p_budget_period_id => l_pqh_budget_period_id,
p_budget_unit1_value_type_cd => 'V',
p_budget_unit1_value => l_budget_unit1_value,
p_budget_unit2_value_type_cd => 'V',
p_budget_unit2_value => l_budget_unit2_value,
p_budget_unit3_value_type_cd => 'V',
p_budget_unit3_value => l_budget_unit3_value,
p_object_version_number => c_pqh_budget_sets_rec.object_version_number,
p_effective_date => sysdate);
add_message('PSB', 'PSB_PQH_UPDATE_BUDGET_SET');
select *
from pay_payroll_gl_flex_maps
where payroll_id = p_payroll_id
and gl_set_of_books_id = p_sob_id;
select payroll_name from pay_all_payrolls_f where payroll_id = p_payroll_id;
g_map_str := 'SELECT cost_allocation_keyflex_id '||
' FROM pay_cost_allocation_keyflex cakf, gl_code_combinations glcc '||
' WHERE cakf.id_flex_num = :b1 '||
' AND cakf.'||c_map_segments_rec.payroll_cost_segment ||' = glcc.'||c_map_segments_rec.gl_account_segment ;
select a.fte_position_id, a.fte, b.cost
from
(
select pp.hr_position_id fte_position_id, sum(avg_fte) fte
from
(select pp.position_id, avg(ppf.fte) avg_fte
from psb_positions pp, psb_position_fte ppf
where ppf.position_id = pp.position_id
and ppf.base_line_version = 'C'
and ppf.hr_budget_id = p_hr_budget_id
and ((ppf.start_date between p_start_date and p_end_date)
or (ppf.end_date between p_start_date and p_end_date)
or ((ppf.start_date < p_start_date) and (ppf.end_date > p_end_date)))
group by pp.position_id) pf, psb_positions pp
where pp.data_extract_id = p_system_data_extract_id
and pp.position_id = pf.position_id
group by pp.hr_position_id
) a,
(
select pp.hr_position_id cost_position_id, sum(ppc.element_cost) cost
from psb_position_costs ppc, psb_positions pp
where pp.data_extract_id = p_system_data_extract_id
and ppc.hr_budget_id = p_hr_budget_id
and ppc.position_id = pp.position_id
and ppc.base_line_version = 'C'
and ppc.currency_code = p_currency_code
and ((ppc.start_date between p_start_date and p_end_date)
or (ppc.end_date between p_start_date and p_end_date)
or ((ppc.start_date < p_start_date) and (ppc.end_date > p_end_date)))
group by pp.hr_position_id
) b
where a.fte_position_id = b.cost_position_id;
select position_id, hr_employee_id, name, effective_start_date
from psb_positions
where data_extract_id = decode(p_event_type, 'BP', p_data_extract_id, p_system_data_extract_id)
and hr_position_id = positionid
and rownum < 2;
select position_id
from psb_positions
where data_extract_id = p_system_data_extract_id
and hr_position_id = positionid
and rownum < 2;
select *
from pqh_psb_budget_details
where budget_version_id = p_pqh_budget_version_id
and position_id = positionid;
select *
from pqh_psb_budget_details
where budget_version_id = p_pqh_budget_version_id
and budget_detail_id = l_pqh_budget_detail_id;
select sum(decode(b.revision_type, 'I', b.revision_amount,
'D', -b.revision_amount)) revision_amount
from psb_budget_revision_lines a,
psb_budget_revision_accounts b,
psb_positions pp
where a.budget_revision_id = p_source_id
and b.budget_revision_acct_line_id = a.budget_revision_acct_line_id
and pp.position_id = b.position_id
and pp.data_extract_id = p_system_data_extract_id
and pp.hr_position_id = positionid;
select a.attribute_id, a.attribute_value_id, c.attribute_value , hr_value_id
from psb_position_assignments a,
psb_attributes b,
psb_attribute_values c
where a.position_id = positionid
and a.data_extract_id = p_system_data_extract_id -- system_data_extract_id
and a.attribute_id = b.attribute_id
and b.system_attribute_type = 'JOB_CLASS'
and c.attribute_value_id = a.attribute_value_id
and c.data_extract_id = p_system_data_extract_id;
select a.attribute_id, a.attribute_value_id, c.attribute_value , hr_value_id
from psb_position_assignments a,
psb_attributes b,
psb_attribute_values c
where a.position_id = positionid
and a.data_extract_id = p_system_data_extract_id -- system_data_extract_id
and a.attribute_id = b.attribute_id
and b.system_attribute_type = 'ORG'
and c.attribute_value_id = a.attribute_value_id
and c.data_extract_id = p_system_data_extract_id;
select *
from pqh_psb_budget_versions
where budget_version_id = p_pqh_budget_version_id;
select set_of_books_id
from psb_data_extracts
where data_extract_id = p_system_data_extract_id;
pqh_psb_interface_api.update_budget_detail
(p_validate => false,
p_budget_detail_id => c_pqh_budget_details_rec.budget_detail_id,
p_position_id => l_positions.hr_position_id(l_position_index),
p_job_id => l_job_id,
p_organization_id => l_org_id,
p_budget_version_id => c_pqh_budget_details_rec.budget_version_id,
p_budget_unit1_value_type_cd => 'V',
p_budget_unit1_value => l_budget_unit1_value,
p_budget_unit2_value_type_cd => 'V',
p_budget_unit2_value => l_budget_unit2_value,
p_budget_unit3_value_type_cd => 'V',
p_budget_unit3_value => l_budget_unit3_value,
p_object_version_number => c_pqh_budget_details_rec.object_version_number);
add_message('PSB', 'PSB_PQH_UPDATE_BUDGET_DETAIL');
(select avg(budget_unit1_value) period_avg_value
from pqh_psb_budget_periods
where budget_detail_id = l_pqh_budget_detail_id) loop
l_period_avg_value := c_period_avg_rec.period_avg_value;
pqh_psb_interface_api.update_budget_detail
(p_validate => false,
p_budget_detail_id => l_pqh_budget_detail_id,
p_position_id => l_positions.hr_position_id(l_position_index),
p_budget_version_id => p_pqh_budget_version_id,
p_budget_unit1_value_type_cd => 'V',
p_budget_unit1_value => l_period_avg_value,
p_object_version_number => l_new_object_version_number);
add_message('PSB', 'PSB_PQH_UPDATE_BUDGET_DETAIL');
(select avg(budget_unit2_value) period_avg_value
from pqh_psb_budget_periods
where budget_detail_id = l_pqh_budget_detail_id) loop
l_period_avg_value := c_period_avg_rec.period_avg_value;
pqh_psb_interface_api.update_budget_detail
(p_validate => false,
p_budget_detail_id => l_pqh_budget_detail_id,
p_position_id => l_positions.hr_position_id(l_position_index),
p_budget_version_id => p_pqh_budget_version_id,
p_budget_unit2_value_type_cd => 'V',
p_budget_unit2_value => l_period_avg_value,
p_object_version_number => l_new_object_version_number);
add_message('PSB', 'PSB_PQH_UPDATE_BUDGET_DETAIL');
(select avg(budget_unit3_value) period_avg_value
from pqh_psb_budget_periods
where budget_detail_id = l_pqh_budget_detail_id) loop
l_period_avg_value := c_period_avg_rec.period_avg_value;
pqh_psb_interface_api.update_budget_detail
(p_validate => false,
p_budget_detail_id => l_pqh_budget_detail_id,
p_position_id => l_positions.hr_position_id(l_position_index),
p_budget_version_id => p_pqh_budget_version_id,
p_budget_unit3_value_type_cd => 'V',
p_budget_unit3_value => l_period_avg_value,
p_object_version_number => l_new_object_version_number);
add_message('PSB', 'PSB_PQH_UPDATE_BUDGET_DETAIL');
pqh_psb_interface_api.update_budget_version
(p_validate => false,
p_budget_id => p_hr_budget_id,
p_budget_version_id => p_pqh_budget_version_id,
p_version_number => l_version_number,
p_object_version_number => l_ver_object_version_number,
p_date_from => l_start_date,
p_date_to => l_end_date,
p_transfered_to_gl_flag => 'N',
p_xfer_to_other_apps_cd => 'N',
p_budget_unit1_value => l_orig_budget_unit1_value,
p_budget_unit2_value => l_orig_budget_unit2_value,
p_budget_unit3_value => l_orig_budget_unit3_value,
p_effective_date => sysdate);
add_message('PSB', 'PSB_PQH_UPDATE_BUDGET_VERSION');
select shared_type_id, system_type_cd
from per_shared_types_vl
where system_type_cd in ('FTE', 'MONEY');
select budget_name, currency_code, business_group_id, budget_unit1_id, budget_unit2_id,
budget_unit3_id, budget_unit1_aggregate, budget_unit2_aggregate, budget_unit3_aggregate
from pqh_psb_budgets
where budget_id = p_hr_budget_id;
select currency_code, to_number(cost_allocation_structure) id_flex_num
from per_business_groups_perf
where business_group_id = l_business_group_id;
select nvl(chart_of_accounts_id, root_chart_of_accounts_id) flex_code,
nvl(root_budget_group_id, budget_group_id) root_budget_group_id
from PSB_BUDGET_GROUPS_V
where budget_group_id = l_budget_group_id;
select budget_calendar_id, budget_group_id, data_extract_id
from PSB_WORKSHEETS
where worksheet_id = p_source_id;
select budget_group_id
from psb_budget_revisions
where budget_revision_id = p_source_id;
select min(start_date) start_date, max(end_date) end_date
from psb_budget_periods
where budget_period_id between p_from_budget_year_id and p_to_budget_year_id
and budget_calendar_id = l_budget_calendar_id
and budget_period_type = 'Y';
select min(start_date) start_date, max(end_date) end_date
from psb_position_fte
where budget_revision_id = p_source_id
and hr_budget_id = p_hr_budget_id;
select attribute_id
from psb_attributes_vl
where business_group_id = p_business_group_id
and system_attribute_type = 'ORG';
select count(*)
into l_syscount
from psb_attribute_values
where attribute_id = l_sysorg_id;
select count(*)
into l_usrcount
from psb_attribute_values
where attribute_id = p_attribute_id;
Update psb_attribute_values
set attribute_id = l_sysorg_id
where attribute_id = p_attribute_id;
Update psb_attribute_values_i
set attribute_id = l_sysorg_id
where attribute_id = p_attribute_id;
update psb_account_position_set_lines
set attribute_id = l_sysorg_id
where attribute_id = p_attribute_id;
Update psb_parameter_formulas
set attribute_id = l_sysorg_id
where attribute_id = p_attribute_id;
Update psb_default_assignments
set attribute_id = l_sysorg_id
where attribute_id = p_attribute_id;
Update psb_position_assignments
set attribute_id = l_sysorg_id
where attribute_id = p_attribute_id;
Delete from psb_attributes_tl
where attribute_id = p_attribute_id;
Delete from psb_attributes
where attribute_id = p_attribute_id;
Update psb_attribute_values
set attribute_id = p_attribute_id
where attribute_id = l_sysorg_id;
Update psb_attribute_values_i
set attribute_id = p_attribute_id
where attribute_id = l_sysorg_id;
update psb_account_position_set_lines
set attribute_id = p_attribute_id
where attribute_id = l_sysorg_id;
Update psb_parameter_formulas
set attribute_id = p_attribute_id
where attribute_id = l_sysorg_id;
Update psb_default_assignments
set attribute_id = p_attribute_id
where attribute_id = l_sysorg_id;
Update psb_position_assignments
set attribute_id = p_attribute_id
where attribute_id = l_sysorg_id;
Delete from psb_attributes_tl
where attribute_id = p_attribute_id;
Update psb_attributes_tl
set attribute_id = p_attribute_id
where attribute_id = l_sysorg_id;
Delete from psb_attributes
where attribute_id = p_attribute_id;
Update psb_attributes
set attribute_id = p_attribute_id
where attribute_id = l_sysorg_id;
select PSB_ELEMENT_POS_SET_GROUPS_S.NEXTVAL seq
from dual;
select * from PSB_ELEMENT_POS_SET_GROUPS
where pay_element_id = elemid;
select position_set_group_id
from PSB_ELEMENT_POS_SET_GROUPS
where pay_element_id = p_new_pay_element_id
and name = psg_name;
--Insert the position set group for new element
for c_elempossetgrp_seq_rec in c_elempossetgrp_seq loop
l_possetgrp_id := c_elempossetgrp_seq_rec.seq;
PSB_ELEMENT_POS_SET_GROUPS_PVT.Insert_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_set_group_id => l_possetgrp_id,
p_pay_element_id => p_new_pay_element_id,
p_name => c_possetgrp_rec.name,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
l_attribute_selection_type VARCHAR2(1);
select *
from PSB_SET_RELATIONS
where position_set_group_id = possetgrpid;
select set_relation_id
from PSB_SET_RELATIONS
where position_set_group_id = possetgrpid
and account_position_set_id = pos_set_id;
select *
from PSB_ACCOUNT_POSITION_SETS
where account_position_set_id = possetid;
select account_position_set_id , attribute_selection_type, rowid
from PSB_ACCOUNT_POSITION_SETS
where data_extract_id = p_target_data_extract_id
and account_or_position_type = 'P'
and name = possetname;
select PSB_ACCOUNT_POSITION_SETS_S.NEXTVAL seq
from dual;
select PSB_SET_RELATIONS_S.NEXTVAL seq
from dual;
l_attribute_selection_type := c_posset_rec.attribute_selection_type;
--Insert the position set
for c_posset_seq_rec in c_posset_seq loop
l_position_set_id := c_posset_seq_rec.seq;
PSB_ACCOUNT_POSITION_SET_PVT.Insert_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_row_id => l_rowid,
p_account_position_set_id => l_position_set_id,
p_name => c_posset_rec.name,
p_set_of_books_id => c_posset_rec.set_of_books_id,
p_use_in_budget_group_flag => c_posset_rec.use_in_budget_group_flag,
p_data_extract_id => p_target_data_extract_id,
p_budget_group_id => p_budget_group_id,
p_global_or_local_type => c_posset_rec.global_or_local_type,
p_account_or_position_type =>c_posset_rec.account_or_position_type,
p_attribute_selection_type => c_posset_rec.attribute_selection_type,
p_business_group_id => c_posset_rec.business_group_id,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
--Position set is inserted
end;
elsif c_posset_rec.attribute_selection_type <> l_attribute_selection_type then
begin
--Update the Position Set with the new selection type
PSB_ACCOUNT_POSITION_SET_PVT.Update_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_row_id => lp_rowid,
p_account_position_set_id => l_position_set_id,
p_name => c_posset_rec.name,
p_set_of_books_id => c_posset_rec.set_of_books_id,
p_data_extract_id => p_target_data_extract_id,
p_global_or_local_type => c_posset_rec.global_or_local_type,
p_account_or_position_type => c_posset_rec.account_or_position_type,
p_attribute_selection_type =>c_posset_rec.attribute_selection_type,
p_business_group_id => c_posset_rec.business_group_id,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID);
--Position set is updated
end;
PSB_SET_RELATION_PVT.Insert_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_row_id => l_rowid,
p_set_relation_id => l_set_relation_id,
p_account_position_set_id => l_position_set_id,
p_allocation_rule_id => null,
p_budget_group_id => null,
p_budget_workflow_rule_id => null,
p_constraint_id => null,
p_default_rule_id => null,
p_parameter_id => null,
p_position_set_group_id => p_new_psg_id,
/* Budget Revision Rules Enhancement Start */
p_rule_id => null,
p_apply_balance_flag => null,
/* Budget Revision Rules Enhancement End */
p_effective_start_date => null,
p_effective_end_date => null,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
l_attribute_selection_type VARCHAR2(1);
select *
from PSB_ACCOUNT_POSITION_SET_LINES
where account_position_set_id = possetid;
select *
from PSB_POSITION_SET_LINE_VALUES
where line_sequence_id = lineseqid;
select a.attribute_value_id
from PSB_ATTRIBUTE_VALUES a,
PSB_ATTRIBUTE_VALUES b
where a.data_extract_id = p_target_data_extract_id
and a.attribute_value = b.attribute_value
and a.attribute_id = b.attribute_id -- added for Bug#4262388
and b.attribute_value_id = attrvalid;
select line_sequence_id
from PSB_ACCOUNT_POSITION_SET_LINES
where attribute_id = attrid
and account_position_set_id = possetid;
select attribute_value_id
from PSB_ATTRIBUTE_VALUES
where attribute_value_id = attr_val_id
and attribute_value in
(select a.attribute_value
from PSB_ATTRIBUTE_VALUES a,
PSB_POSITION_SET_LINE_VALUES b
where a.attribute_value_id = b.attribute_value_id
and b.line_sequence_id = lineseqid);
select PSB_ACCT_POSITION_SET_LINES_S.NEXTVAL seq
from dual;
select PSB_POSITION_SET_LINE_VALUES_S.NEXTVAL seq
from dual;
PSB_ACCT_POSITION_SET_LINE_PVT.Insert_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_row_id => l_rowid,
p_line_sequence_id => l_line_sequence_id,
p_account_position_set_id => p_new_posset_id,
p_description => c_possetline_rec.description,
p_business_group_id => c_possetline_rec.business_group_id,
p_attribute_id => c_possetline_rec.attribute_id,
p_include_or_exclude_type => c_possetline_rec.include_or_exclude_type,
p_segment1_low => c_possetline_rec.segment1_low,
p_segment2_low => c_possetline_rec.segment2_low,
p_segment3_low => c_possetline_rec.segment3_low,
p_segment4_low => c_possetline_rec.segment4_low,
p_segment5_low => c_possetline_rec.segment5_low,
p_segment6_low => c_possetline_rec.segment6_low,
p_segment7_low => c_possetline_rec.segment7_low,
p_segment8_low => c_possetline_rec.segment8_low,
p_segment9_low => c_possetline_rec.segment9_low,
p_segment10_low => c_possetline_rec.segment10_low,
p_segment11_low => c_possetline_rec.segment11_low,
p_segment12_low => c_possetline_rec.segment12_low,
p_segment13_low => c_possetline_rec.segment13_low,
p_segment14_low => c_possetline_rec.segment14_low,
p_segment15_low => c_possetline_rec.segment15_low,
p_segment16_low => c_possetline_rec.segment16_low,
p_segment17_low => c_possetline_rec.segment17_low,
p_segment18_low => c_possetline_rec.segment18_low,
p_segment19_low => c_possetline_rec.segment19_low,
p_segment20_low => c_possetline_rec.segment20_low,
p_segment21_low => c_possetline_rec.segment21_low,
p_segment22_low => c_possetline_rec.segment22_low,
p_segment23_low => c_possetline_rec.segment23_low,
p_segment24_low => c_possetline_rec.segment24_low,
p_segment25_low => c_possetline_rec.segment25_low,
p_segment26_low => c_possetline_rec.segment26_low,
p_segment27_low => c_possetline_rec.segment27_low,
p_segment28_low => c_possetline_rec.segment28_low,
p_segment29_low => c_possetline_rec.segment29_low,
p_segment30_low => c_possetline_rec.segment30_low,
p_segment1_high => c_possetline_rec.segment1_high,
p_segment2_high => c_possetline_rec.segment2_high,
p_segment3_high => c_possetline_rec.segment3_high,
p_segment4_high => c_possetline_rec.segment4_high,
p_segment5_high => c_possetline_rec.segment5_high,
p_segment6_high => c_possetline_rec.segment6_high,
p_segment7_high => c_possetline_rec.segment7_high,
p_segment8_high => c_possetline_rec.segment8_high,
p_segment9_high => c_possetline_rec.segment9_high,
p_segment10_high => c_possetline_rec.segment10_high,
p_segment11_high => c_possetline_rec.segment11_high,
p_segment12_high => c_possetline_rec.segment12_high,
p_segment13_high => c_possetline_rec.segment13_high,
p_segment14_high => c_possetline_rec.segment14_high,
p_segment15_high => c_possetline_rec.segment15_high,
p_segment16_high => c_possetline_rec.segment16_high,
p_segment17_high => c_possetline_rec.segment17_high,
p_segment18_high => c_possetline_rec.segment18_high,
p_segment19_high => c_possetline_rec.segment19_high,
p_segment20_high => c_possetline_rec.segment20_high,
p_segment21_high => c_possetline_rec.segment21_high,
p_segment22_high => c_possetline_rec.segment22_high,
p_segment23_high => c_possetline_rec.segment23_high,
p_segment24_high => c_possetline_rec.segment24_high,
p_segment25_high => c_possetline_rec.segment25_high,
p_segment26_high => c_possetline_rec.segment26_high,
p_segment27_high => c_possetline_rec.segment27_high,
p_segment28_high => c_possetline_rec.segment28_high,
p_segment29_high => c_possetline_rec.segment29_high,
p_segment30_high => c_possetline_rec.segment30_high,
p_context => c_possetline_rec.context,
p_attribute1 => c_possetline_rec.attribute1,
p_attribute2 => c_possetline_rec.attribute2,
p_attribute3 => c_possetline_rec.attribute3,
p_attribute4 => c_possetline_rec.attribute4,
p_attribute5 => c_possetline_rec.attribute5,
p_attribute6 => c_possetline_rec.attribute6,
p_attribute7 => c_possetline_rec.attribute7,
p_attribute8 => c_possetline_rec.attribute8,
p_attribute9 => c_possetline_rec.attribute9,
p_attribute10 => c_possetline_rec.attribute10,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
PSB_POS_SET_LINE_VALUES_PVT.Insert_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_row_id => l_rowid,
p_value_sequence_id => l_value_sequence_id,
p_line_sequence_id => l_line_sequence_id,
p_attribute_value_id => l_attribute_value_id,
p_attribute_value => c_possetlineval_rec.attribute_value,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
select *
from PSB_PAY_ELEMENT_DISTRIBUTIONS
where position_set_group_id = possetgrpid;
select PSB_PAY_ELEMENT_DISTRIBUTION_S.NEXTVAL seq
from dual;
select distribution_id, distribution_percent
from PSB_PAY_ELEMENT_DISTRIBUTIONS
where position_set_group_id = psg_id
and ((cc_id is not null and code_combination_id = cc_id) or (concatenated_segments = con_segments));
--Check whether the line is already exists or not, if exists update the line o/w insert new one
l_distribution_exists := FALSE;
--Insert the line
for c_elemdist_seq_rec in c_elemdist_seq loop
l_distribution_id := c_elemdist_seq_rec.seq;
PSB_ELEMENT_DISTRIBUTIONS_PVT.Insert_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_distribution_id => l_distribution_id,
p_position_set_group_id => p_new_psg_id,
p_chart_of_accounts_id => c_elemdist_rec.chart_of_accounts_id,
p_effective_start_date => l_year_start_date,
p_effective_end_date => l_year_end_date,
p_distribution_percent => c_elemdist_rec.distribution_percent,
p_concatenated_segments => null,
p_code_combination_id => l_mapped_ccid,
p_distribution_set_id => c_elemdist_rec.distribution_set_id,
p_segment1 => null,
p_segment2 => null,
p_segment3 => null,
p_segment4 => null,
p_segment5 => null,
p_segment6 => null,
p_segment7 => null,
p_segment8 => null,
p_segment9 => null,
p_segment10 => null,
p_segment11 => null,
p_segment12 => null,
p_segment13 => null,
p_segment14 => null,
p_segment15 => null,
p_segment16 => null,
p_segment17 => null,
p_segment18 => null,
p_segment19 => null,
p_segment20 => null,
p_segment21 => null,
p_segment22 => null,
p_segment23 => null,
p_segment24 => null,
p_segment25 => null,
p_segment26 => null,
p_segment27 => null,
p_segment28 => null,
p_segment29 => null,
p_segment30 => null,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
end; --End of insert line
begin --Update line
PSB_ELEMENT_DISTRIBUTIONS_PVT.Update_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_distribution_id => l_distribution_id,
p_position_set_group_id => p_new_psg_id,
p_chart_of_accounts_id => c_elemdist_rec.chart_of_accounts_id,
p_effective_start_date => l_year_start_date,
p_effective_end_date => l_year_end_date,
p_distribution_percent => c_elemdist_rec.distribution_percent,
p_concatenated_segments => null,
p_code_combination_id => l_mapped_ccid,
p_distribution_set_id => c_elemdist_rec.distribution_set_id,
p_segment1 => null,
p_segment2 => null,
p_segment3 => null,
p_segment4 => null,
p_segment5 => null,
p_segment6 => null,
p_segment7 => null,
p_segment8 => null,
p_segment9 => null,
p_segment10 => null,
p_segment11 => null,
p_segment12 => null,
p_segment13 => null,
p_segment14 => null,
p_segment15 => null,
p_segment16 => null,
p_segment17 => null,
p_segment18 => null,
p_segment19 => null,
p_segment20 => null,
p_segment21 => null,
p_segment22 => null,
p_segment23 => null,
p_segment24 => null,
p_segment25 => null,
p_segment26 => null,
p_segment27 => null,
p_segment28 => null,
p_segment29 => null,
p_segment30 => null,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID);
end if; --End of update line
begin --Insert the line
--
for c_elemdist_seq_rec in c_elemdist_seq loop
l_distribution_id := c_elemdist_seq_rec.seq;
PSB_ELEMENT_DISTRIBUTIONS_PVT.Insert_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_distribution_id => l_distribution_id,
p_position_set_group_id => p_new_psg_id,
p_chart_of_accounts_id => c_elemdist_rec.chart_of_accounts_id,
p_effective_start_date => c_elemdist_rec.effective_start_date,
p_effective_end_date => c_elemdist_rec.effective_end_date,
p_distribution_percent => c_elemdist_rec.distribution_percent,
p_concatenated_segments => c_elemdist_rec.concatenated_segments,
p_code_combination_id => c_elemdist_rec.code_combination_id,
p_distribution_set_id => c_elemdist_rec.distribution_set_id,
p_segment1 => c_elemdist_rec.segment1,
p_segment2 => c_elemdist_rec.segment2,
p_segment3 => c_elemdist_rec.segment3,
p_segment4 => c_elemdist_rec.segment4,
p_segment5 => c_elemdist_rec.segment5,
p_segment6 => c_elemdist_rec.segment6,
p_segment7 => c_elemdist_rec.segment7,
p_segment8 => c_elemdist_rec.segment8,
p_segment9 => c_elemdist_rec.segment9,
p_segment10 => c_elemdist_rec.segment10,
p_segment11 => c_elemdist_rec.segment11,
p_segment12 => c_elemdist_rec.segment12,
p_segment13 => c_elemdist_rec.segment13,
p_segment14 => c_elemdist_rec.segment14,
p_segment15 => c_elemdist_rec.segment15,
p_segment16 => c_elemdist_rec.segment16,
p_segment17 => c_elemdist_rec.segment17,
p_segment18 => c_elemdist_rec.segment18,
p_segment19 => c_elemdist_rec.segment19,
p_segment20 => c_elemdist_rec.segment20,
p_segment21 => c_elemdist_rec.segment21,
p_segment22 => c_elemdist_rec.segment22,
p_segment23 => c_elemdist_rec.segment23,
p_segment24 => c_elemdist_rec.segment24,
p_segment25 => c_elemdist_rec.segment25,
p_segment26 => c_elemdist_rec.segment26,
p_segment27 => c_elemdist_rec.segment27,
p_segment28 => c_elemdist_rec.segment28,
p_segment29 => c_elemdist_rec.segment29,
p_segment30 => c_elemdist_rec.segment30,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => sysdate);
end; --End of Insert line where flex mapping doesn't exist
begin --Update the line
--
PSB_ELEMENT_DISTRIBUTIONS_PVT.Update_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_distribution_id => l_distribution_id,
p_position_set_group_id => p_new_psg_id,
p_chart_of_accounts_id => c_elemdist_rec.chart_of_accounts_id,
p_effective_start_date => c_elemdist_rec.effective_start_date,
p_effective_end_date => c_elemdist_rec.effective_end_date,
p_distribution_percent => c_elemdist_rec.distribution_percent,
p_concatenated_segments => c_elemdist_rec.concatenated_segments,
p_code_combination_id => c_elemdist_rec.code_combination_id,
p_distribution_set_id => c_elemdist_rec.distribution_set_id,
p_segment1 => c_elemdist_rec.segment1,
p_segment2 => c_elemdist_rec.segment2,
p_segment3 => c_elemdist_rec.segment3,
p_segment4 => c_elemdist_rec.segment4,
p_segment5 => c_elemdist_rec.segment5,
p_segment6 => c_elemdist_rec.segment6,
p_segment7 => c_elemdist_rec.segment7,
p_segment8 => c_elemdist_rec.segment8,
p_segment9 => c_elemdist_rec.segment9,
p_segment10 => c_elemdist_rec.segment10,
p_segment11 => c_elemdist_rec.segment11,
p_segment12 => c_elemdist_rec.segment12,
p_segment13 => c_elemdist_rec.segment13,
p_segment14 => c_elemdist_rec.segment14,
p_segment15 => c_elemdist_rec.segment15,
p_segment16 => c_elemdist_rec.segment16,
p_segment17 => c_elemdist_rec.segment17,
p_segment18 => c_elemdist_rec.segment18,
p_segment19 => c_elemdist_rec.segment19,
p_segment20 => c_elemdist_rec.segment20,
p_segment21 => c_elemdist_rec.segment21,
p_segment22 => c_elemdist_rec.segment22,
p_segment23 => c_elemdist_rec.segment23,
p_segment24 => c_elemdist_rec.segment24,
p_segment25 => c_elemdist_rec.segment25,
p_segment26 => c_elemdist_rec.segment26,
p_segment27 => c_elemdist_rec.segment27,
p_segment28 => c_elemdist_rec.segment28,
p_segment29 => c_elemdist_rec.segment29,
p_segment30 => c_elemdist_rec.segment30,
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID);
end if; --End of Insert line where flex mapping doesn't exist