[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select PSB_ATTRIBUTES_S.NEXTVAL seq
from dual;
select PSB_ATTRIBUTE_VALUES_S.NEXTVAL seq
from dual;
select *
from PSB_ATTRIBUTES
where business_group_id = p_local_business_group_id;
select attribute_id
from PSB_ATTRIBUTES
where name = attrname
and business_group_id = g_global_business_group_id;
select *
from PSB_ATTRIBUTE_VALUES
where attribute_id = attrid
and data_extract_id = p_local_data_extract_id;
select attribute_value_id
from PSB_ATTRIBUTE_VALUES
where attribute_value = attrval
and attribute_id = attrid
and data_extract_id = g_global_data_extract_id;
PSB_POSITION_ATTRIBUTES_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_attribute_id => l_attribute_id,
p_business_group_id => g_global_business_group_id,
p_name => c_attr_rec.name,
p_display_in_worksheet => c_attr_rec.display_in_worksheet,
p_display_sequence => c_attr_rec.display_sequence,
p_display_prompt => c_attr_rec.display_prompt,
p_required_for_import_flag => c_attr_rec.required_for_import_flag,
p_required_for_positions_flag => c_attr_rec.required_for_positions_flag,
p_allow_in_position_set_flag => c_attr_rec.allow_in_position_set_flag,
p_value_table_flag => c_attr_rec.value_table_flag,
p_protected_flag => c_attr_rec.protected_flag,
p_definition_type => c_attr_rec.definition_type,
p_definition_structure => c_attr_rec.definition_structure,
p_definition_table => c_attr_rec.definition_table,
p_definition_column => c_attr_rec.definition_column,
p_attribute_type_id => c_attr_rec.attribute_type_id,
p_data_type => c_attr_rec.data_type,
p_application_id => c_attr_rec.application_id,
p_system_attribute_type => c_attr_rec.system_attribute_type,
p_last_update_date => sysdate,
p_last_updated_by => g_userid,
p_last_update_login => g_loginid,
p_created_by => g_userid,
p_creation_date => sysdate);
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 => l_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 => g_global_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 => g_userid,
p_last_update_login => g_loginid,
p_created_by => g_userid,
p_creation_date => sysdate);
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 => l_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 => g_global_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 => g_userid,
p_last_update_login => g_loginid,
p_created_by => g_userid,
p_creation_date => sysdate);
select *
from PSB_PAY_ELEMENTS
where data_extract_id = p_local_data_extract_id;
select pay_element_id
from PSB_PAY_ELEMENTS
where name = elemname
and data_extract_id = g_global_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 or worksheet_id = p_local_worksheet_id)
and pay_element_option_id = elemoptionid
and pay_element_id = elemid;
select *
from PSB_PAY_ELEMENT_RATES
where (worksheet_id is null or worksheet_id = p_local_worksheet_id)
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
from PSB_ACCOUNT_POSITION_SETS
where data_extract_id = g_global_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_id
from PSB_ATTRIBUTES a,
PSB_ATTRIBUTES b
where a.business_group_id = g_global_business_group_id
and a.name = b.name
and b.attribute_id = attrid;
select a.attribute_value_id
from PSB_ATTRIBUTE_VALUES a,
PSB_ATTRIBUTE_VALUES b
where a.data_extract_id = g_global_data_extract_id
and a.attribute_value = b.attribute_value
and a.attribute_id = b.attribute_id -- added this 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_pay_element_rates_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 => g_global_business_group_id,
p_data_extract_id => g_global_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 => g_userid,
p_last_update_login => g_loginid,
p_created_by => g_userid,
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 => g_userid,
p_last_update_login => g_loginid,
p_created_by => g_userid,
p_creation_date => sysdate);
PSB_PAY_ELEMENT_RATES_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_rate_id => l_pay_element_rate_id,
p_pay_element_option_id => l_pay_element_option_id,
p_pay_element_id => l_pay_element_id,
p_effective_start_date => c_elemrates_rec.effective_start_date,
p_effective_end_date => c_elemrates_rec.effective_end_date,
p_worksheet_id => l_worksheet_id,
p_element_value_type => c_elemrates_rec.element_value_type,
p_element_value => c_elemrates_rec.element_value,
p_pay_basis => c_elemrates_rec.pay_basis,
p_formula_id => c_elemrates_rec.formula_id,
p_maximum_value => c_elemrates_rec.maximum_value,
p_mid_value => c_elemrates_rec.mid_value,
p_minimum_value => c_elemrates_rec.minimum_value,
p_currency_code => c_elemrates_rec.currency_code,
p_last_update_date => sysdate,
p_last_updated_by => g_userid,
p_last_update_login => g_loginid,
p_created_by => g_userid,
p_creation_date => sysdate);
PSB_PAY_ELEMENT_RATES_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_rate_id => l_pay_element_rate_id,
p_pay_element_option_id => NULL,
p_pay_element_id => l_pay_element_id,
p_effective_start_date => c_elemrates_rec.effective_start_date,
p_effective_end_date => c_elemrates_rec.effective_end_date,
p_worksheet_id => l_worksheet_id,
p_element_value_type => c_elemrates_rec.element_value_type,
p_element_value => c_elemrates_rec.element_value,
p_pay_basis => c_elemrates_rec.pay_basis,
p_formula_id => c_elemrates_rec.formula_id,
p_maximum_value => c_elemrates_rec.maximum_value,
p_mid_value => c_elemrates_rec.mid_value,
p_minimum_value => c_elemrates_rec.minimum_value,
p_currency_code => c_elemrates_rec.currency_code,
p_last_update_date => sysdate,
p_last_updated_by => g_userid,
p_last_update_login => g_loginid,
p_created_by => g_userid,
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 => g_userid,
p_last_update_login => g_loginid,
p_created_by => g_userid,
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_use_in_budget_group_flag => c_posset_rec.use_in_budget_group_flag,
p_set_of_books_id => c_posset_rec.set_of_books_id,
p_data_extract_id => g_global_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 => g_userid,
p_last_update_login => g_loginid,
p_created_by => g_userid,
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 => g_global_business_group_id,
p_attribute_id => l_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 => g_userid,
p_last_update_login => g_loginid,
p_created_by => g_userid,
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 => g_userid,
p_last_update_login => g_loginid,
p_created_by => g_userid,
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 => g_global_business_group_id,
p_attribute_id => l_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 => g_userid,
p_last_update_login => g_loginid,
p_created_by => g_userid,
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 => g_userid,
p_last_update_login => g_loginid,
p_created_by => g_userid,
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 => g_userid,
p_last_update_login => g_loginid,
p_created_by => g_userid,
p_creation_date => sysdate);
-- 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 => 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_data_extract_id => g_global_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 => g_global_business_group_id,
p_last_update_date => sysdate,
p_last_updated_by => g_userid,
p_last_update_login => g_loginid);
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 => c_setrel_rec.effective_start_date,
p_effective_end_date => c_setrel_rec.effective_end_date,
p_last_update_date => sysdate,
p_last_updated_by => g_userid,
p_last_update_login => g_loginid,
p_created_by => g_userid,
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 => g_userid,
p_last_update_login => g_loginid,
p_created_by => g_userid,
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 => g_userid,
p_last_update_login => g_loginid,
p_created_by => g_userid,
p_creation_date => sysdate);
PSB_PAY_ELEMENT_RATES_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_rate_id => l_pay_element_rate_id,
p_pay_element_option_id => l_pay_element_option_id,
p_pay_element_id => l_pay_element_id,
p_effective_start_date => c_elemrates_rec.effective_start_date,
p_effective_end_date => c_elemrates_rec.effective_end_date,
p_worksheet_id => l_worksheet_id,
p_element_value_type => c_elemrates_rec.element_value_type,
p_element_value => c_elemrates_rec.element_value,
p_pay_basis => c_elemrates_rec.pay_basis,
p_formula_id => c_elemrates_rec.formula_id,
p_maximum_value => c_elemrates_rec.maximum_value,
p_mid_value => c_elemrates_rec.mid_value,
p_minimum_value => c_elemrates_rec.minimum_value,
p_currency_code => c_elemrates_rec.currency_code,
p_last_update_date => sysdate,
p_last_updated_by => g_userid,
p_last_update_login => g_loginid,
p_created_by => g_userid,
p_creation_date => sysdate);
select *
from PSB_EMPLOYEES
where business_group_id = p_local_business_group_id
and data_extract_id = p_local_data_extract_id;
select employee_id
from psb_employees
where employee_number = empno
and data_extract_id = g_global_data_extract_id;
select PSB_EMPLOYEES_S.NEXTVAL seq
from dual;
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 (l_employee_id, g_global_data_extract_id, g_global_business_group_id,
c_emp_rec.employee_number, c_emp_rec.hr_employee_id, c_emp_rec.first_name,
c_emp_rec.full_name, c_emp_rec.known_as, c_emp_rec.last_name,
c_emp_rec.middle_names, c_emp_rec.title,
sysdate, g_userid, sysdate,
g_userid, g_loginid);
select *
from PSB_POSITIONS
where data_extract_id = p_local_data_extract_id;
select position_id
from PSB_POSITIONS
where ((hrposid is null and (hrempid is null or hr_employee_id = hrempid) and name = posname)
or ((hr_position_id = hrposid) and (hrempid is null or hr_employee_id = hrempid)))
and data_extract_id = g_global_data_extract_id;
select *
from PSB_POSITION_ASSIGNMENTS
where (worksheet_id is null or worksheet_id = p_local_worksheet_id)
and assignment_type = 'ATTRIBUTE'
and position_id = positionid;
select a.attribute_id
from PSB_ATTRIBUTES a,
PSB_ATTRIBUTES b
where a.business_group_id = g_global_business_group_id
and a.name = b.name
and b.attribute_id = attrid;
select a.attribute_value_id
from PSB_ATTRIBUTE_VALUES a,
PSB_ATTRIBUTE_VALUES b
where a.data_extract_id = g_global_data_extract_id
and a.attribute_value = b.attribute_value
and a.attribute_id = b.attribute_id -- added this for Bug #4262388
and b.attribute_value_id = attrvalid;
select *
from PSB_POSITION_ASSIGNMENTS
where (worksheet_id is null or worksheet_id = p_local_worksheet_id)
and assignment_type = 'ELEMENT'
and position_id = positionid;
select a.pay_element_id
from PSB_PAY_ELEMENTS a,
PSB_PAY_ELEMENTS b
where a.name = b.name
and a.data_extract_id = g_global_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
where (worksheet_id is null or worksheet_id = p_local_worksheet_id)
and assignment_type = 'EMPLOYEE'
and position_id = positionid;
select a.employee_id
from PSB_EMPLOYEES a,
PSB_EMPLOYEES b
where a.employee_number = b.employee_number
and a.data_extract_id = g_global_data_extract_id
and b.employee_id = empid;
select *
from PSB_POSITION_PAY_DISTRIBUTIONS
where position_id = positionid
and (worksheet_id is null or worksheet_id = p_local_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_data_extract_id => g_global_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 => g_global_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_new_position_flag => c_positions_rec.new_position_flag,
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 => g_global_data_extract_id,
p_worksheet_id => l_worksheet_id,
p_position_id => l_position_id,
p_assignment_type => 'ATTRIBUTE',
p_attribute_id => l_attribute_id,
p_attribute_value_id => l_attribute_value_id,
p_attribute_value => c_posassign_attr_rec.attribute_value,
p_pay_element_id => null,
p_pay_element_option_id => null,
p_effective_start_date => c_posassign_attr_rec.effective_start_date,
p_effective_end_date => c_posassign_attr_rec.effective_end_date,
p_element_value_type => null,
p_element_value => null,
p_currency_code => null,
p_pay_basis => null,
p_employee_id => null,
p_primary_employee_flag => null,
p_global_default_flag => null,
p_assignment_default_rule_id => null,
p_modify_flag => null);
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 => g_global_data_extract_id,
p_worksheet_id => l_worksheet_id,
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 => g_global_data_extract_id,
p_worksheet_id => l_worksheet_id,
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_worksheet_id => l_worksheet_id,
p_position_id => l_position_id,
p_data_extract_id => g_global_data_extract_id,
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 => c_position_distr_rec.project_id,
p_task_id => c_position_distr_rec.task_id,
p_award_id => c_position_distr_rec.award_id,
p_expenditure_type => c_position_distr_rec.expenditure_type,
p_expenditure_organization_id => c_position_distr_rec.expenditure_organization_id,
p_description => c_position_distr_rec.description);
select *
from PSB_SERVICE_PACKAGES
where global_worksheet_id = p_local_worksheet_id;
select service_package_id
from PSB_SERVICE_PACKAGES
where short_name = name
and global_worksheet_id = g_global_worksheet_id;
select PSB_SERVICE_PACKAGES_S.NEXTVAL seq
from dual;
insert into PSB_SERVICE_PACKAGES
(service_package_id, global_worksheet_id, base_service_package, name,
short_name, description, priority, last_update_date, last_updated_by,
last_update_login, created_by, creation_date)
values (l_service_package_id, g_global_worksheet_id, c_sp_rec.base_service_package, c_sp_rec.name,
c_sp_rec.short_name, c_sp_rec.description, c_sp_rec.priority, sysdate, g_userid,
g_loginid, g_userid, sysdate);
select *
from PSB_WS_ACCOUNT_LINES a
where position_line_id is null
and exists
(select 1
from PSB_WS_LINES b
where b.account_line_id = a.account_line_id
and b.worksheet_id = p_local_worksheet_id)
order by a.code_combination_id, a.current_stage_seq;
select *
from PSB_WS_ACCOUNT_LINES
where position_line_id = poslineid
order by position_line_id, current_stage_seq;
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_local_worksheet_id);
select *
from PSB_WS_FTE_LINES
where position_line_id = poslineid
order by position_line_id, current_stage_seq;
select *
from PSB_WS_ELEMENT_LINES
where position_line_id = poslineid
order by position_line_id, current_stage_seq;
select a.pay_element_id
from PSB_PAY_ELEMENTS a,
PSB_PAY_ELEMENTS b
where a.name = b.name
and a.data_extract_id = g_global_data_extract_id
and b.pay_element_id = elemid;
select a.position_id
from PSB_POSITIONS a,
PSB_POSITIONS b
where (b.hr_employee_id is null or a.hr_employee_id = b.hr_employee_id)
and a.name = b.name
and a.data_extract_id = g_global_data_extract_id
and b.position_id = posid;
select 'Exists'
from dual
where exists
(select 1
from PSB_WS_POSITION_LINES a,
PSB_WS_LINES_POSITIONS b
where a.position_line_id = b.position_line_id
and a.position_id = posid
and b.worksheet_id = p_global_worksheet_id);
select a.service_package_id
from PSB_SERVICE_PACKAGES a,
PSB_SERVICE_PACKAGES b
where a.short_name = b.short_name
and a.global_worksheet_id = g_global_worksheet_id
and b.service_package_id = spid;
select b.data_extract_id,
a.business_group_id
from PSB_DATA_EXTRACTS a,
PSB_WORKSHEETS b
where a.data_extract_id = b.data_extract_id
and b.worksheet_id = p_global_worksheet_id;
select c.local_worksheet_id,
b.data_extract_id,
a.business_group_id
from PSB_DATA_EXTRACTS a,
PSB_WORKSHEETS b,
PSB_WS_CONSOLIDATION_DETAILS c
where a.data_extract_id = b.data_extract_id
and b.worksheet_id = c.local_worksheet_id
and c.global_worksheet_id = p_global_worksheet_id;
select local_worksheet_id
from PSB_WS_CONSOLIDATION_DETAILS
where global_worksheet_id = p_global_worksheet_id;
PSB_WORKSHEET.Update_Worksheet
(p_api_version => 1.0,
p_return_status => l_return_status,
p_worksheet_id => p_global_worksheet_id,
p_ws_creation_complete => 'N');
PSB_WORKSHEET.Update_Worksheet
(p_api_version => 1.0,
p_return_status => l_return_status,
p_worksheet_id => p_global_worksheet_id,
p_ws_creation_complete => 'Y');
select nvl(b.freeze_flag, 'N') freeze_flag,
b.stage_set_id,
b.current_stage_seq,
b.budget_calendar_id,
b.budget_by_position,
b.budget_group_id,
nvl(a.set_of_books_id, a.root_set_of_books_id) set_of_books_id
from PSB_BUDGET_GROUPS_V a,
PSB_WORKSHEETS b
where a.budget_group_id = b.budget_group_id
and b.worksheet_id = p_global_worksheet_id;
select b.name,
b.global_worksheet_flag,
b.stage_set_id,
b.current_stage_seq,
b.budget_calendar_id,
b.budget_by_position,
b.budget_group_id,
nvl(a.set_of_books_id, a.root_set_of_books_id) set_of_books_id
from PSB_BUDGET_GROUPS_V a,
PSB_WORKSHEETS b,
PSB_WS_CONSOLIDATION_DETAILS c
where a.budget_group_id = b.budget_group_id
and b.worksheet_id = c.local_worksheet_id
and c.global_worksheet_id = p_global_worksheet_id;
select budget_group_id
from PSB_BUDGET_GROUPS
where budget_group_type = 'R'
and budget_group_id = budgetgroup_id
and effective_start_date <= PSB_WS_ACCT1.g_startdate_pp
and (effective_end_date is null or effective_end_date >= PSB_WS_ACCT1.g_enddate_cy)
start with budget_group_id = l_budget_group_id
connect by prior budget_group_id = parent_budget_group_id;