[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Position_Cost
( p_return_status OUT NOCOPY VARCHAR2,
p_position_line_id IN NUMBER,
p_position_start_date IN DATE,
p_position_end_date IN DATE,
p_worksheet_id IN NUMBER,
p_flex_mapping_set_id IN NUMBER,
p_global_worksheet_id IN NUMBER,
p_func_currency IN VARCHAR2,
p_rounding_factor IN NUMBER,
p_service_package_id IN NUMBER,
p_stage_set_id IN NUMBER,
p_start_stage_seq IN NUMBER,
p_current_stage_seq IN NUMBER,
p_budget_year_id IN NUMBER,
p_budget_group_id IN NUMBER
);
select position_id,
name,
effective_start_date,
effective_end_date
from PSB_POSITIONS
where (((effective_start_date <= PSB_WS_ACCT1.g_end_est_date)
and (effective_end_date is null))
or ((effective_start_date between PSB_WS_ACCT1.g_startdate_cy and PSB_WS_ACCT1.g_end_est_date)
or (effective_end_date between PSB_WS_ACCT1.g_startdate_cy and PSB_WS_ACCT1.g_end_est_date)
or ((effective_start_date < PSB_WS_ACCT1.g_startdate_cy)
and (effective_end_date > PSB_WS_ACCT1.g_end_est_date))))
and business_group_id = p_business_group_id
and data_extract_id = p_data_extract_id;
select nvl(global_worksheet_id, worksheet_id) global_worksheet_id,
decode(local_copy_flag, 'Y', worksheet_id, nvl(global_worksheet_id, worksheet_id)) assign_worksheet_id,
stage_set_id,
nvl(data_extract_id, global_data_extract_id) data_extract_id,
budget_group_id,
budget_calendar_id,
num_proposed_years,
rounding_factor,
current_stage_seq
from PSB_WORKSHEETS_V
where worksheet_id = p_worksheet_id;
select service_package_id
from PSB_SERVICE_PACKAGES
where base_service_package = 'Y'
and global_worksheet_id = l_global_worksheet_id;
select nvl(root_budget_group_id, budget_group_id) root_budget_group_id,
nvl(business_group_id, root_business_group_id) business_group_id,
nvl(currency_code, root_currency_code) currency_code,
nvl(chart_of_accounts_id, root_chart_of_accounts_id) chart_of_accounts_id
from PSB_BUDGET_GROUPS_V
where budget_group_id = l_budget_group_id;
select a.position_id,
a.name,
a.effective_start_date,
a.effective_end_date
from PSB_POSITIONS a,
PSB_WS_POSITION_LINES b
where a.position_id = b.position_id
and b.position_line_id = p_position_line_id;
select worksheet_id,
pay_element_id,
pay_element_option_id,
pay_basis,
element_value_type,
element_value,
effective_start_date,
effective_end_date
from PSB_POSITION_ASSIGNMENTS
where (worksheet_id is null or worksheet_id = l_assign_worksheet_id)
and currency_code = l_func_currency
and assignment_type = 'ELEMENT'
and (((effective_start_date <= l_end_date)
and (effective_end_date is null))
or ((effective_start_date between l_start_date and l_end_date)
or (effective_end_date between l_start_date and l_end_date)
or ((effective_start_date < l_start_date)
and (effective_end_date > l_end_date))))
and position_id = l_position_id
order by effective_start_date,
effective_end_date,
element_value desc;*****/
select worksheet_id,
pay_element_id,
pay_element_option_id,
pay_basis,
element_value_type,
element_value,
effective_start_date,
effective_end_date
from PSB_POSITION_ASSIGNMENTS a
where currency_code = l_func_currency
and assignment_type = 'ELEMENT'
and position_id = l_position_id
and ((worksheet_id = l_assign_worksheet_id)
or (worksheet_id IS NULL
and not exists
(select 1
from psb_position_assignments c
where (
( nvl(c.effective_start_date, l_end_date + 1)
between nvl(a.effective_start_date, l_end_date)
and nvl(a.effective_end_date, nvl(l_end_date, c.effective_start_date)))
OR ( nvl(a.effective_start_date, l_end_date + 1)
between nvl(c.effective_start_date, l_end_date)
and nvl(c.effective_end_date, nvl(l_end_date, a.effective_start_date)))
)
and c.position_id = a.position_id
and c.pay_element_id = a.pay_element_id
and c.worksheet_id = l_assign_worksheet_id
)
))
order by effective_start_date,
effective_end_date,
element_value desc;
select a.worksheet_id,
a.pay_element_id,
a.pay_element_option_id,
a.pay_basis,
a.element_value_type,
a.element_value,
a.formula_id,
a.effective_start_date,
a.effective_end_date
from PSB_PAY_ELEMENT_RATES a,
PSB_PAY_ELEMENTS b
where (a.worksheet_id is null or a.worksheet_id = l_global_worksheet_id)
and a.currency_code = l_func_currency
and exists
(select 1
from PSB_POSITION_ASSIGNMENTS c
where nvl(c.pay_element_option_id, FND_API.G_MISS_NUM) = nvl(a.pay_element_option_id, FND_API.G_MISS_NUM)
and (c.worksheet_id is null or c.worksheet_id = l_assign_worksheet_id)
and c.currency_code = l_func_currency
and (((c.effective_start_date <= l_end_date)
and (c.effective_end_date is null))
or ((c.effective_start_date between l_start_date and l_end_date)
or (c.effective_end_date between l_start_date and l_end_date)
or ((c.effective_start_date < l_start_date)
and (c.effective_end_date > l_end_date))))
and c.pay_element_id = a.pay_element_id
and c.position_id = l_position_id)
and (((a.effective_start_date <= l_end_date)
and (a.effective_end_date is null))
or ((a.effective_start_date between l_start_date and l_end_date)
or (a.effective_end_date between l_start_date and l_end_date)
or ((a.effective_start_date < l_start_date)
and (a.effective_end_date > l_end_date))))
and a.pay_element_id = b.pay_element_id
and b.business_group_id = l_business_group_id
and b.data_extract_id = l_data_extract_id
order by a.worksheet_id,
a.effective_start_date,
a.effective_end_date,
a.element_value desc;
select worksheet_id,
effective_start_date,
effective_end_date,
attribute_id,
-- Fixed bug # 3683644
FND_NUMBER.canonical_to_number(attribute_value) attribute_value,
attribute_value_id
from PSB_POSITION_ASSIGNMENTS
where attribute_id in (PSB_WS_POS1.g_fte_id, PSB_WS_POS1.g_default_wklyhrs_id)
and (worksheet_id is null or worksheet_id = l_assign_worksheet_id)
and assignment_type = 'ATTRIBUTE'
and (((effective_start_date <= l_end_date)
and (effective_end_date is null))
or ((effective_start_date between l_start_date and l_end_date)
or (effective_end_date between l_start_date and l_end_date)
or ((effective_start_date < l_start_date)
and (effective_end_date > l_end_date))))
and position_id = l_position_id
order by worksheet_id,
effective_start_date,
effective_end_date,
FND_NUMBER.canonical_to_number(attribute_value) desc; -- Fixed bug # 3683644
Update_Position_Cost
(p_return_status => l_return_status,
p_position_line_id => p_position_line_id,
p_position_start_date => l_fte_start_date,
p_position_end_date => l_fte_end_date,
p_worksheet_id => p_worksheet_id,
p_flex_mapping_set_id => p_flex_mapping_set_id,
p_global_worksheet_id => l_global_worksheet_id,
p_func_currency => l_func_currency,
p_rounding_factor => l_rounding_factor,
p_service_package_id => l_service_package_id,
p_stage_set_id => l_stage_set_id,
p_start_stage_seq => l_start_stage_seq,
p_current_stage_seq => l_current_stage_seq,
p_budget_year_id => p_budget_year_id,
p_budget_group_id => PSB_WS_POS1.g_salary_budget_group_id);
Update_Position_Cost
(p_return_status => l_return_status,
p_position_line_id => p_position_line_id,
p_position_start_date => l_fte_start_date,
p_position_end_date => l_fte_end_date,
p_worksheet_id => p_worksheet_id,
p_flex_mapping_set_id => p_flex_mapping_set_id,
p_global_worksheet_id => l_global_worksheet_id,
p_func_currency => l_func_currency,
p_rounding_factor => l_rounding_factor,
p_service_package_id => l_service_package_id,
p_stage_set_id => l_stage_set_id,
p_start_stage_seq => l_start_stage_seq,
p_current_stage_seq => l_current_stage_seq,
p_budget_year_id => PSB_WS_ACCT1.g_budget_years(l_year_index).budget_year_id,
p_budget_group_id => PSB_WS_POS1.g_salary_budget_group_id);
SELECT MAX(FND_NUMBER.canonical_TO_NUMBER(attribute_value)) attribute_value
FROM psb_position_assignments
WHERE attribute_id = PSB_WS_POS1.g_default_wklyhrs_id
AND (worksheet_id IS NULL OR worksheet_id = g_weekly_hours_worksheet_id)
AND assignment_type = 'ATTRIBUTE'
AND position_id = p_position_id ;
SELECT MAX(FND_NUMBER.canonical_TO_NUMBER(vals.attribute_value)) attribute_value
FROM psb_position_assignments asgn ,
psb_attribute_values vals
WHERE asgn.attribute_id = PSB_WS_POS1.g_default_wklyhrs_id
AND ( asgn.worksheet_id IS NULL
OR
asgn.worksheet_id = g_weekly_hours_worksheet_id
)
AND asgn.assignment_type = 'ATTRIBUTE'
AND asgn.position_id = p_position_id
and vals.attribute_value_id = asgn.attribute_value_id ;
select b.entity_id,
a.percent
from PSB_ALLOCRULE_PERCENTS a,
PSB_DEFAULTS b
where a.number_of_periods = 12
and a.allocation_rule_id = b.entity_id
and ((nvl(b.global_default_flag, 'N') = 'N'
and exists
(select 1
from PSB_BUDGET_POSITIONS c,
PSB_SET_RELATIONS d
where c.position_id = p_position_id
and c.account_position_set_id = d.account_position_set_id
and d.default_rule_id = b.default_rule_id))
or b.global_default_flag = 'Y')
and b.business_group_id = p_business_group_id
and b.data_extract_id = p_data_extract_id
order by b.priority,b.entity_id,
a.period_num;
select b.entity_id,
a.percent
from PSB_ALLOCRULE_PERCENTS a,
PSB_DEFAULTS b
where a.number_of_periods = 4
and a.allocation_rule_id = b.entity_id
and ((nvl(b.global_default_flag, 'N') = 'N'
and exists
(select 1
from PSB_BUDGET_POSITIONS c,
PSB_SET_RELATIONS d
where c.position_id = p_position_id
and c.account_position_set_id = d.account_position_set_id
and d.default_rule_id = b.default_rule_id))
or b.global_default_flag = 'Y')
and b.business_group_id = p_business_group_id
and b.data_extract_id = p_data_extract_id
order by b.priority,b.entity_id,
a.period_num;
select b.entity_id,
a.percent
from PSB_ALLOCRULE_PERCENTS a,
PSB_DEFAULTS b
where a.number_of_periods = 2
and a.allocation_rule_id = b.entity_id
and ((nvl(b.global_default_flag, 'N') = 'N'
and exists
(select 1
from PSB_BUDGET_POSITIONS c,
PSB_SET_RELATIONS d
where c.position_id = p_position_id
and c.account_position_set_id = d.account_position_set_id
and d.default_rule_id = b.default_rule_id))
or b.global_default_flag = 'Y')
and b.business_group_id = p_business_group_id
and b.data_extract_id = p_data_extract_id
order by b.priority,b.entity_id,
a.period_num;
l_update_dist BOOLEAN;
l_update_dist := FALSE;
l_update_dist := TRUE;
if not (l_update_dist) then
begin
PSB_WS_POS1.g_num_pd_costs := PSB_WS_POS1.g_num_pd_costs + 1;
l_update_dist BOOLEAN;
select a.code_combination_id,
a.distribution_percent,
a.effective_start_date, a.effective_end_date
from PSB_PAY_ELEMENT_DISTRIBUTIONS a,
PSB_ELEMENT_POS_SET_GROUPS b,
PSB_SET_RELATIONS c,
PSB_BUDGET_POSITIONS d
where a.chart_of_accounts_id = p_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 = p_pay_element_id
and c.account_position_set_id = d.account_position_set_id
and d.data_extract_id = p_data_extract_id
and d.position_id = p_position_id
order by a.distribution_percent desc;
l_update_dist := FALSE;
l_update_dist := TRUE;
if not (l_update_dist) then
begin
PSB_WS_POS1.g_num_pd_costs := PSB_WS_POS1.g_num_pd_costs + 1;
PROCEDURE Update_Position_Cost
( p_return_status OUT NOCOPY VARCHAR2,
p_position_line_id IN NUMBER,
p_position_start_date IN DATE,
p_position_end_date IN DATE,
p_worksheet_id IN NUMBER,
p_flex_mapping_set_id IN NUMBER,
p_global_worksheet_id IN NUMBER,
p_func_currency IN VARCHAR2,
p_rounding_factor IN NUMBER,
p_service_package_id IN NUMBER,
p_stage_set_id IN NUMBER,
p_start_stage_seq IN NUMBER,
p_current_stage_seq IN NUMBER,
p_budget_year_id IN NUMBER,
p_budget_group_id IN NUMBER
) IS
l_start_date DATE;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Position_Cost';
select /*+ ORDERED INDEX(a PSB_WS_ACCOUNT_LINES_N5) */
account_line_id, code_combination_id,
service_package_id, element_set_id, budget_group_id
from PSB_WS_ACCOUNT_LINES a
where position_line_id = p_position_line_id
and budget_year_id = p_budget_year_id
and end_stage_seq is null;
select element_line_id, service_package_id, pay_element_id
from PSB_WS_ELEMENT_LINES
where position_line_id = p_position_line_id
and budget_year_id = p_budget_year_id
and end_stage_seq is null;
select /*+ ORDERED INDEX(a PSB_WS_FTE_LINES_N1) */
fte_line_id, annual_fte, service_package_id
from PSB_WS_FTE_LINES a
where position_line_id = p_position_line_id
and budget_year_id = p_budget_year_id
and end_stage_seq is null;
select service_package_id
from PSB_SERVICE_PACKAGES
where global_worksheet_id = p_global_worksheet_id;
PSB_WS_POS1.Update_Annual_FTE
(p_api_version => 1.0,
p_return_status => l_return_status,
p_worksheet_id => p_worksheet_id,
p_position_line_id => p_position_line_id,
p_budget_year_id => p_budget_year_id,
p_service_package_id => c_sp_rec.service_package_id,
p_stage_set_id => p_stage_set_id,
p_current_stage_seq => p_current_stage_seq,
p_budget_group_id => p_budget_group_id);
PSB_WS_POS1.Update_Annual_FTE
(p_api_version => 1.0,
p_return_status => l_return_status,
p_worksheet_id => p_worksheet_id,
p_position_line_id => p_position_line_id,
p_budget_year_id => p_budget_year_id,
p_service_package_id => c_sp_rec.service_package_id,
p_stage_set_id => p_stage_set_id,
p_current_stage_seq => p_current_stage_seq,
p_budget_group_id => p_budget_group_id);
END Update_Position_Cost;
select FND_NUMBER.canonical_to_number(attribute_value) attribute_value
from PSB_ATTRIBUTE_VALUES
where attribute_value_id = p_attribute_value_id;