[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select global_worksheet_flag,
budget_group_id,
nvl(data_extract_id, global_data_extract_id) data_extract_id,
budget_calendar_id,
nvl(parameter_set_id, global_parameter_set_id) parameter_set_id
from PSB_WORKSHEETS_V
where worksheet_id = p_worksheet_id;
select nvl(business_group_id, root_business_group_id) business_group_id,
nvl(currency_code, root_currency_code) currency_code
from PSB_BUDGET_GROUPS_V
where budget_group_id = l_budget_group_id;
select parameter_id,
name,
parameter_autoinc_rule,
parameter_compound_annually,
currency_code,
effective_start_date,
effective_end_date
from PSB_PARAMETER_ASSIGNMENTS_V
where data_extract_id = l_data_extract_id
and parameter_type = 'ELEMENT'
and (((effective_start_date <= Year_End_Date)
and (effective_end_date is null))
or ((effective_start_date between Year_Start_Date and Year_End_Date)
or (effective_end_date between Year_Start_Date and Year_End_Date)
or ((effective_start_date < Year_Start_Date)
and (effective_end_date > Year_End_Date))))
and parameter_set_id = l_parameter_set_id
order by effective_start_date,
priority;
select pp.position_id, pp.name
from PSB_POSITIONS pp
where pp.data_extract_id = l_data_extract_id
and exists
(select 1
from PSB_POSITION_ASSIGNMENTS pa,
PSB_PARAMETER_FORMULAS pf
where pa.position_id = pp.position_id
and pa.data_extract_id = pp.data_extract_id
and pa.assignment_type = 'ELEMENT'
and (pa.worksheet_id is null or pa.worksheet_id = p_worksheet_id)
and pf.parameter_id = p_parameter_id
and pa.pay_element_id = pf.pay_element_id)
and exists
(select 1
from PSB_WS_POSITION_LINES wpl,
PSB_WS_LINES_POSITIONS wlp
where wpl.position_line_id = wlp.position_line_id
and wlp.worksheet_id = p_worksheet_id
and wpl.position_id = pp.position_id);
select a.global_worksheet_flag,
nvl(a.global_worksheet_id, a.worksheet_id) global_worksheet_id,
nvl(a.data_extract_id, a.global_data_extract_id) data_extract_id,
a.budget_calendar_id,
nvl(b.business_group_id, b.root_business_group_id) business_group_id,
nvl(b.currency_code, b.root_currency_code) currency_code,
nvl(b.root_budget_group_id, b.budget_group_id) root_budget_group_id,
nvl(b.set_of_books_id, b.root_set_of_books_id) set_of_books_id
from PSB_WORKSHEETS_V a,
PSB_BUDGET_GROUPS_V b
where a.worksheet_id = p_worksheet_id
and b.budget_group_id = a.budget_group_id;
select name,
currency_code,
effective_start_date,
effective_end_date,
parameter_compound_annually,
parameter_autoinc_rule
from PSB_PARAMETERS_V
where parameter_id = p_parameter_id
and parameter_type = 'ELEMENT';
select chart_of_accounts_id
from GL_SETS_OF_BOOKS
where set_of_books_id = l_set_of_books_id;
select pay_element_id,
pay_element_option_id,
element_value_type,
element_value,
effective_start_date,
effective_end_date
from PSB_PARAMETER_FORMULAS
where parameter_id = p_parameter_id
order by step_number;
select pay_element_option_id
from PSB_PAY_ELEMENT_OPTIONS
where pay_element_id = p_pay_element_id;
select pay_element_option_id,
effective_start_date,
effective_end_date,
element_value_type,
element_value,
formula_id,
pay_basis
from PSB_PAY_ELEMENT_RATES
where worksheet_id is null
and currency_code = p_currency_code
and ((p_pay_element_option_id is null)
or (pay_element_option_id = p_pay_element_option_id))
and (((effective_start_date <= p_end_date)
and (effective_end_date is null))
or ((effective_start_date between p_start_date and p_end_date)
or (effective_end_date between p_start_date and p_end_date)
or ((effective_start_date < p_start_date)
and (effective_end_date > p_end_date))))
and pay_element_id = p_pay_element_id;
select increment_by,
increment_type
from PSB_PARAMETER_FORMULAS
where parameter_id = p_parameter_id;
select a.pay_element_id,
a.pay_element_option_id,
a.effective_start_date,
a.effective_end_date,
a.element_value_type,
a.element_value,
a.formula_id,
a.pay_basis,
a.maximum_value,
a.mid_value,
a.minimum_value
from PSB_PAY_ELEMENT_RATES a,
PSB_PAY_ELEMENTS b
where a.worksheet_id is null
and a.currency_code = p_currency_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.pay_element_id = b.pay_element_id
and b.salary_flag = 'Y'
and b.processing_type = 'R'
and b.business_group_id = p_business_group_id
and b.data_extract_id = p_data_extract_id;
select 'Exists'
from dual
where exists
(select 1 from PSB_WS_LINES_POSITIONS wlp, PSB_WS_POSITION_LINES wpl
where wlp.worksheet_id = p_worksheet_id
and wpl.position_line_id = wlp.position_line_id
and wpl.position_id = p_position_id);
select 'Exists'
from dual
where exists
(select 1 from PSB_BUDGET_REVISION_POS_LINES brpl, PSB_BUDGET_REVISION_POSITIONS brp
where brpl.budget_revision_id = p_worksheet_id
and brp.budget_revision_pos_line_id = brpl.budget_revision_pos_line_id
and brp.position_id = p_position_id);
select a.global_worksheet_flag,
nvl(a.global_worksheet_id, a.worksheet_id) global_worksheet_id,
nvl(a.data_extract_id, a.global_data_extract_id) data_extract_id,
a.budget_calendar_id,
nvl(b.business_group_id, b.root_business_group_id) business_group_id,
nvl(b.currency_code, b.root_currency_code) currency_code
from PSB_WORKSHEETS_V a,
PSB_BUDGET_GROUPS_V b
where a.worksheet_id = p_worksheet_id
and b.budget_group_id = a.budget_group_id;
select name,
currency_code,
effective_start_date,
effective_end_date,
parameter_compound_annually,
parameter_autoinc_rule
from PSB_PARAMETERS_V
where parameter_id = p_parameter_id
and parameter_type = 'POSITION';
select global_worksheet_flag,
budget_group_id,
nvl(data_extract_id, global_data_extract_id) data_extract_id,
budget_calendar_id,
nvl(parameter_set_id, global_parameter_set_id) parameter_set_id
from PSB_WORKSHEETS_V
where worksheet_id = p_worksheet_id;
select nvl(business_group_id, root_business_group_id) business_group_id,
nvl(currency_code, root_currency_code) currency_code
from PSB_BUDGET_GROUPS_V
where budget_group_id = l_budget_group_id;
select parameter_id,
name,
parameter_compound_annually,
currency_code,
effective_start_date,
effective_end_date
from PSB_PARAMETER_ASSIGNMENTS_V
where parameter_autoinc_rule = 'N'
and data_extract_id = l_data_extract_id
and parameter_type = 'POSITION'
and (((effective_start_date <= Year_End_Date)
and (effective_end_date is null))
or ((effective_start_date between Year_Start_Date and Year_End_Date)
or (effective_end_date between Year_Start_Date and Year_End_Date)
or ((effective_start_date < Year_Start_Date)
and (effective_end_date > Year_End_Date))))
and parameter_set_id = l_parameter_set_id
order by effective_start_date,
priority;
select parameter_id,
name,
parameter_compound_annually,
currency_code,
effective_start_date,
effective_end_date
from PSB_PARAMETER_ASSIGNMENTS_V
where parameter_autoinc_rule = 'Y'
and data_extract_id = l_data_extract_id
and parameter_type = 'POSITION'
and (((effective_start_date <= End_Date)
and (effective_end_date is null))
or ((effective_start_date between Start_Date and End_Date)
or (effective_end_date between Start_Date and End_Date)
or ((effective_start_date < Start_Date)
and (effective_end_date > End_Date))))
and parameter_set_id = l_parameter_set_id
order by effective_start_date,
priority;
select a.budget_revision_pos_line_id
from PSB_BUDGET_REVISION_POSITIONS a,
PSB_BUDGET_REVISION_POS_LINES b
where b.budget_revision_id = p_worksheet_id
and a.position_id = l_position_id
and a.budget_revision_pos_line_id = b.budget_revision_pos_line_id;
select a.position_id,
c.name
from PSB_BUDGET_POSITIONS a,
PSB_SET_RELATIONS b,
PSB_POSITIONS c
where a.data_extract_id = p_data_extract_id
and a.account_position_set_id = b.account_position_set_id
and b.parameter_id = p_parameter_id
and c.position_id = a.position_id;
select nvl(b.root_budget_group_id, b.budget_group_id) root_budget_group_id,
nvl(b.set_of_books_id, b.root_set_of_books_id) set_of_books_id
from PSB_WORKSHEETS_V a,
PSB_BUDGET_GROUPS_V b
where a.worksheet_id = p_worksheet_id
and b.budget_group_id = a.budget_group_id;
select chart_of_accounts_id
from GL_SETS_OF_BOOKS
where set_of_books_id = l_set_of_books_id;
select name from PSB_ENTITY where entity_id = p_parameter_id;
select assignment_type,
attribute_id,
attribute_value,
pay_element_id,
pay_element_option_id,
element_value_type,
element_value,
effective_start_date,
effective_end_date
from PSB_PARAMETER_FORMULAS
where parameter_id = p_parameter_id
order by step_number;
select pay_element_option_id,
effective_start_date,
effective_end_date,
element_value_type,
element_value,
pay_basis
from PSB_POSITION_ASSIGNMENTS
where worksheet_id is null
and currency_code = p_currency_code
and ((p_pay_element_option_id is null) or (pay_element_option_id = p_pay_element_option_id))
and (((effective_start_date <= p_end_date)
and (effective_end_date is null))
or ((effective_start_date between p_start_date and p_end_date)
or (effective_end_date between p_start_date and p_end_date)
or ((effective_start_date < p_start_date)
and (effective_end_date > p_end_date))))
and pay_element_id = p_pay_element_id
and position_id = p_position_id;
select pay_element_option_id,
effective_start_date,
effective_end_date,
element_value_type,
element_value,
formula_id,
pay_basis
from PSB_PAY_ELEMENT_RATES
where worksheet_id is null
and ((p_pay_element_option_id is null) or (pay_element_option_id = p_pay_element_option_id))
and (((effective_start_date <= EndDate)
and (effective_end_date is null))
or ((effective_start_date between StartDate and EndDate)
or (effective_end_date between StartDate and EndDate)
or ((effective_start_date < StartDate)
and (effective_end_date > EndDate))))
and pay_element_id = p_pay_element_id;
select hiredate_between_from,
hiredate_between_to,
adjdate_between_from,
adjdate_between_to,
increment_by,
increment_type
from PSB_PARAMETER_FORMULAS
where parameter_id = p_parameter_id;
(SELECT start_date,
end_date+1 end_date
FROM psb_budget_periods
WHERE budget_period_type = 'C'
AND l_start_date between start_date AND end_date
AND budget_calendar_id = g_budget_calendar_id
)
LOOP
l_cp_start_date := l_periods.start_date;
(SELECT start_date ,
end_date+1 end_date
FROM psb_budget_periods
WHERE budget_period_type = 'C'
AND l_start_date between start_date AND end_date
AND budget_calendar_id = g_budget_calendar_id
)
LOOP
l_cp_start_date := l_periods.start_date;
select a.pay_element_id,
a.pay_element_option_id,
a.effective_start_date,
a.effective_end_date,
a.element_value_type,
a.element_value,
a.pay_basis,
/* For Bug No. 2263220 : Start */
b.option_flag
/* For Bug No. 2263220 : End */
from PSB_POSITION_ASSIGNMENTS a,
PSB_PAY_ELEMENTS b
where a.worksheet_id is null
and a.currency_code = p_currency_code
and (((p_end_date is not null)
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)))))
or ((p_end_date is null)
and (nvl(a.effective_end_date, p_start_date) >= p_start_date)))
and a.pay_element_id = b.pay_element_id
and a.position_id = p_position_id
and b.salary_flag = 'Y'
and b.processing_type = 'R'
and b.business_group_id = p_business_group_id
and b.data_extract_id = p_data_extract_id;
select element_value_type,
element_value,
formula_id,
pay_basis,
effective_start_date,
effective_end_date
from PSB_PAY_ELEMENT_RATES
where worksheet_id is null
and ((ElemOptID is null) or (pay_element_option_id = ElemOptID))
and (((EndDate is not null)
and (((effective_start_date <= EndDate)
and (effective_end_date is null))
or ((effective_start_date between StartDate and EndDate)
or (effective_end_date between StartDate and EndDate)
or ((effective_start_date < StartDate)
and (effective_end_date > EndDate)))))
or ((EndDate is null)
and (nvl(effective_end_date, StartDate) >= StartDate)))
and pay_element_id = ElemID;
select name,
sequence_number
from PSB_PAY_ELEMENT_OPTIONS
where pay_element_option_id = ElemOptID;
select pay_element_option_id,
sequence_number
from PSB_PAY_ELEMENT_OPTIONS
where sequence_number =
(select min(sequence_number)
from PSB_PAY_ELEMENT_OPTIONS
where sequence_number > SeqNum
and name = OptionName
and pay_element_id = ElementID)
and name = OptionName
and pay_element_id = ElementID;
select budget_calendar_id,
flex_mapping_set_id,
rounding_factor,
nvl(data_extract_id, global_data_extract_id) data_extract_id,
budget_group_id,
current_stage_seq
from PSB_WORKSHEETS_V
where worksheet_id = p_worksheet_id;
select nvl(business_group_id, root_business_group_id) business_group_id,
nvl(chart_of_accounts_id, root_chart_of_accounts_id) chart_of_accounts_id,
nvl(currency_code, root_currency_code) currency_code
from PSB_BUDGET_GROUPS_V
where budget_group_id = l_budget_group_id;
select a.position_id,
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 start_date,
end_date
from PSB_BUDGET_PERIODS
where budget_period_id = p_budget_year_id;
select /*+ ORDERED INDEX(a PSB_WS_ACCOUNT_LINES_N5) */
code_combination_id,
budget_group_id,
ytd_amount
from PSB_WS_ACCOUNT_LINES a
where salary_account_line = 'Y'
and p_current_stage_seq between start_stage_seq and current_stage_seq
and currency_code = p_func_currency
and stage_set_id = p_stage_set_id
and service_package_id = p_service_package_id
and budget_year_id = p_budget_year_id
and position_line_id = p_position_line_id;
select /*+ ORDERED USE_NL(a b) INDEX(a PSB_WS_ELEMENT_LINES_N1) INDEX(b PSB_PAY_ELEMENTS_U1) */
a.pay_element_id,
a.element_set_id,
a.element_cost
from PSB_WS_ELEMENT_LINES a,
PSB_PAY_ELEMENTS b
where p_current_stage_seq between a.start_stage_seq and a.current_stage_seq
and a.currency_code = p_func_currency
and a.pay_element_id = b.pay_element_id
and a.stage_set_id = p_stage_set_id
and a.service_package_id = p_service_package_id
and a.budget_year_id = p_budget_year_id
and a.position_line_id = p_position_line_id
and b.follow_salary = 'Y'
and b.business_group_id = p_business_group_id
and b.data_extract_id = p_data_extract_id;
select /*+ ORDERED USE_NL(a b c) INDEX(a PSB_WS_ACCOUNT_LINES_N5) INDEX(b PSB_WS_ELEMENT_LINES_N1) INDEX(c PSB_PAY_ELEMENTS_U1) */
a.account_line_id,
a.code_combination_id,
a.ytd_amount,
a.period1_amount, a.period2_amount, a.period3_amount,
a.period4_amount, a.period5_amount, a.period6_amount,
a.period7_amount, a.period8_amount, a.period9_amount,
a.period10_amount, a.period11_amount, a.period12_amount,
a.period13_amount, a.period14_amount, a.period15_amount,
a.period16_amount, a.period17_amount, a.period18_amount,
a.period19_amount, a.period20_amount, a.period21_amount,
a.period22_amount, a.period23_amount, a.period24_amount,
a.period25_amount, a.period26_amount, a.period27_amount,
a.period28_amount, a.period29_amount, a.period30_amount,
a.period31_amount, a.period32_amount, a.period33_amount,
a.period34_amount, a.period35_amount, a.period36_amount,
a.period37_amount, a.period38_amount, a.period39_amount,
a.period40_amount, a.period41_amount, a.period42_amount,
a.period43_amount, a.period44_amount, a.period45_amount,
a.period46_amount, a.period47_amount, a.period48_amount,
a.period49_amount, a.period50_amount, a.period51_amount,
a.period52_amount, a.period53_amount, a.period54_amount,
a.period55_amount, a.period56_amount, a.period57_amount,
a.period58_amount, a.period59_amount, a.period60_amount
from PSB_WS_ACCOUNT_LINES a,
PSB_WS_ELEMENT_LINES b,
PSB_PAY_ELEMENTS c
where a.element_set_id = b.element_set_id
and p_current_stage_seq between a.start_stage_seq and a.current_stage_seq
and a.stage_set_id = p_stage_set_id
and a.service_package_id = p_service_package_id
and a.budget_year_id = p_budget_year_id
and a.position_line_id = p_position_line_id
and p_current_stage_seq between b.start_stage_seq and b.current_stage_seq
and b.currency_code = p_func_currency
and b.pay_element_id = c.pay_element_id
and b.stage_set_id = p_stage_set_id
and b.service_package_id = p_service_package_id
and b.budget_year_id = p_budget_year_id
and b.position_line_id = p_position_line_id
and c.follow_salary = 'Y'
and c.business_group_id = p_business_group_id
and c.data_extract_id = p_data_extract_id;
PSB_WORKSHEET.Delete_WAL
(p_api_version => 1.0,
p_return_status => l_return_status,
p_account_line_id => PSB_WS_POS1.g_element_dist(l_eldist_index).account_line_id);
PSB_WS_POS1.Update_Annual_FTE
(p_api_version => 1.0,
p_return_status => p_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 => p_service_package_id,
p_stage_set_id => p_stage_set_id,
p_current_stage_seq => p_current_stage_seq,
p_budget_group_id => PSB_WS_POS1.g_salary_budget_group_id);
select constraint_id,
name,
currency_code,
severity_level,
effective_start_date,
effective_end_date
from PSB_CONSTRAINT_ASSIGNMENTS_V
where constraint_type = 'ELEMENT'
and (((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 constraint_set_id = p_constraint_set_id;
select pay_element_id,
pay_element_option_id,
nvl(effective_start_date, p_start_date) effective_start_date,
nvl(effective_end_date, p_end_date) effective_end_date,
allow_modify
from PSB_CONSTRAINT_FORMULAS
where constraint_id = p_constraint_id
order by step_number;
select a.position_id
from PSB_BUDGET_POSITIONS a,
PSB_SET_RELATIONS b
where a.data_extract_id = p_data_extract_id
and a.account_position_set_id = b.account_position_set_id
and b.constraint_id = p_constraint_id;
select constraint_id,
name,
currency_code,
severity_level,
fte_constraint,
effective_start_date,
effective_end_date,
constraint_detailed_flag
from PSB_CONSTRAINT_ASSIGNMENTS_V
where constraint_type = 'POSITION'
and (((effective_start_date <= Year_End_Date)
and (effective_end_date is null))
or ((effective_start_date between Year_Start_Date and Year_End_Date)
or (effective_end_date between Year_Start_Date and Year_End_Date)
or ((effective_start_date < Year_Start_Date)
and (effective_end_date > Year_End_Date))))
and constraint_set_id = p_constraint_set_id
order by severity_level desc;
select 'x'
from dual
where exists
(select 'Service Package Exists'
from PSB_WS_SUBMIT_SERVICE_PACKAGES
where worksheet_id = p_worksheet_id);
select d.position_id,
c.name,
a.position_line_id
from PSB_WS_LINES_POSITIONS a,
PSB_WS_POSITION_LINES b,
PSB_POSITIONS c,
PSB_BUDGET_POSITIONS d,
PSB_SET_RELATIONS e
where a.position_line_id = b.position_line_id
and a.worksheet_id = p_worksheet_id
and b.position_id = c.position_id
and c.position_id = d.position_id
and d.data_extract_id = p_data_extract_id
and d.account_position_set_id = e.account_position_set_id
and e.constraint_id = p_constraint_id;
select pay_element_id,
pay_element_option_id,
prefix_operator,
nvl(currency_code, p_currency_code) currency_code,
element_value_type,
element_value
from PSB_CONSTRAINT_FORMULAS
where constraint_id = p_constraint_id
order by step_number;
select name grade_name,
grade_step
from PSB_PAY_ELEMENT_OPTIONS
where pay_element_option_id = p_pay_element_option_id;
select a.name position_name,
b.name,
b.grade_step
from PSB_POSITIONS a,
PSB_PAY_ELEMENT_OPTIONS b,
PSB_POSITION_ASSIGNMENTS c
where exists
(select 1
from PSB_BUDGET_POSITIONS d,
PSB_SET_RELATIONS e
where d.data_extract_id = p_data_extract_id
and d.position_id = c.position_id
and d.account_position_set_id = e.account_position_set_id
and e.constraint_id = p_constraint_id)
and a.position_id = c.position_id
and b.pay_element_option_id = c.pay_element_option_id
and c.pay_element_option_id <> p_pay_element_option_id
and ((c.worksheet_id is null) or (c.worksheet_id = p_worksheet_id))
and (((c.effective_start_date <= p_year_start_date)
and (c.effective_end_date is null))
or ((c.effective_start_date between p_year_start_date and p_year_end_date)
or (c.effective_end_date between p_year_start_date and p_year_end_date)
or ((c.effective_start_date < p_year_start_date)
and (c.effective_end_date > p_year_end_date))))
and c.pay_element_id = p_pay_element_id;
select a.name,
a.grade_step
from PSB_PAY_ELEMENT_OPTIONS a,
PSB_POSITION_ASSIGNMENTS b
where a.pay_element_option_id = b.pay_element_option_id
and b.pay_element_option_id <> p_pay_element_option_id
and (((b.effective_start_date <= p_year_start_date)
and (b.effective_end_date is null))
or ((b.effective_start_date between p_year_start_date and p_year_end_date)
or (b.effective_end_date between p_year_start_date and p_year_end_date)
or ((b.effective_start_date < p_year_start_date)
and (b.effective_end_date > p_year_end_date))))
and b.pay_element_id = p_pay_element_id
and b.position_id = p_position_id;
select sum(nvl(a.element_cost, 0)) Sum_Elem
from PSB_WS_ELEMENT_LINES a,
PSB_WORKSHEETS b
where exists
(select 1
from PSB_WS_LINES_POSITIONS c,
PSB_WS_POSITION_LINES d,
PSB_BUDGET_POSITIONS e,
PSB_SET_RELATIONS f
where c.position_line_id = a.position_line_id
and c.position_line_id = d.position_line_id
and c.worksheet_id = p_worksheet_id
and d.position_id = e.position_id
and e.data_extract_id = p_data_extract_id
and e.account_position_set_id = f.account_position_set_id
and f.constraint_id = p_constraint_id)
and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
and a.currency_code = p_currency_code
and a.stage_set_id = b.stage_set_id
and a.budget_year_id = p_budget_year_id
and a.pay_element_id = p_pay_element_id
and b.worksheet_id = p_worksheet_id;
select sum(nvl(a.element_cost, 0)) Sum_Elem
from PSB_WS_ELEMENT_LINES a,
PSB_WORKSHEETS b,
PSB_PAY_ELEMENTS c
where exists
(select 1
from PSB_WS_LINES_POSITIONS d,
PSB_WS_POSITION_LINES e,
PSB_BUDGET_POSITIONS f,
PSB_SET_RELATIONS g
where d.position_line_id = a.position_line_id
and d.position_line_id = e.position_line_id
and d.worksheet_id = p_worksheet_id
and e.position_id = f.position_id
and f.data_extract_id = p_data_extract_id
and f.account_position_set_id = g.account_position_set_id
and g.constraint_id = p_constraint_id)
and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
and a.currency_code = p_currency_code
and a.stage_set_id = b.stage_set_id
and a.budget_year_id = p_budget_year_id
and a.pay_element_id = c.pay_element_id
and b.worksheet_id = p_worksheet_id
and c.processing_type = 'R'
and c.salary_flag = 'Y'
and c.business_group_id = p_business_group_id
and c.data_extract_id = p_data_extract_id;
select sum(nvl(a.element_cost, 0)) Sum_Elem
from PSB_WS_ELEMENT_LINES a,
PSB_WORKSHEETS b
where exists
(select 1
from PSB_WS_LINES_POSITIONS c,
PSB_WS_POSITION_LINES d,
PSB_BUDGET_POSITIONS e,
PSB_SET_RELATIONS f
where c.position_line_id = a.position_line_id
and c.position_line_id = d.position_line_id
and c.worksheet_id = p_worksheet_id
and d.position_id = e.position_id
and e.data_extract_id = p_data_extract_id
and e.account_position_set_id = f.account_position_set_id
and f.constraint_id = p_constraint_id)
and exists
(select 1
from PSB_WS_SUBMIT_SERVICE_PACKAGES g
where g.service_package_id = a.service_package_id
and g.worksheet_id = p_worksheet_id)
and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
and a.currency_code = p_currency_code
and a.stage_set_id = b.stage_set_id
and a.budget_year_id = p_budget_year_id
and a.pay_element_id = p_pay_element_id
and b.worksheet_id = p_worksheet_id;
select sum(nvl(a.element_cost, 0)) Sum_Elem
from PSB_WS_ELEMENT_LINES a,
PSB_WORKSHEETS b,
PSB_PAY_ELEMENTS c
where exists
(select 1
from PSB_WS_LINES_POSITIONS d,
PSB_WS_POSITION_LINES e,
PSB_BUDGET_POSITIONS f,
PSB_SET_RELATIONS g
where d.position_line_id = a.position_line_id
and d.position_line_id = e.position_line_id
and d.worksheet_id = p_worksheet_id
and e.position_id = f.position_id
and f.data_extract_id = p_data_extract_id
and f.account_position_set_id = g.account_position_set_id
and g.constraint_id = p_constraint_id)
and exists
(select 1
from PSB_WS_SUBMIT_SERVICE_PACKAGES h
where h.service_package_id = a.service_package_id
and h.worksheet_id = p_worksheet_id)
and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
and a.currency_code = p_currency_code
and a.stage_set_id = b.stage_set_id
and a.budget_year_id = p_budget_year_id
and a.pay_element_id = c.pay_element_id
and b.worksheet_id = p_worksheet_id
and c.processing_type = 'R'
and c.salary_flag = 'Y'
and c.business_group_id = p_business_group_id
and c.data_extract_id = p_data_extract_id;
select /*+ ORDERED USE_NL(b a) INDEX(b PSB_WORKSHEETS_U1) INDEX(a PSB_WS_ELEMENT_LINES_N1) */
sum(nvl(a.element_cost, 0)) Sum_Elem
from PSB_WS_ELEMENT_LINES a,
PSB_WORKSHEETS b
where a.currency_code = p_currency_code
and a.stage_set_id = b.stage_set_id
and a.pay_element_id = p_pay_element_id
and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
and a.budget_year_id = p_budget_year_id
and a.position_line_id = p_position_line_id
and b.worksheet_id = p_worksheet_id;
select /*+ ORDERED INDEX(a PSB_WS_ELEMENT_LINES_N1) */
sum(nvl(a.element_cost, 0)) Sum_Elem
from PSB_WS_ELEMENT_LINES a,
PSB_WORKSHEETS b,
PSB_PAY_ELEMENTS c
where a.currency_code = p_currency_code
and a.stage_set_id = b.stage_set_id
and a.pay_element_id = c.pay_element_id
and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
and a.budget_year_id = p_budget_year_id
and a.position_line_id = p_position_line_id
and b.worksheet_id = p_worksheet_id
and c.processing_type = 'R'
and c.salary_flag = 'Y'
and c.business_group_id = p_business_group_id
and c.data_extract_id = p_data_extract_id;
select /*+ ORDERED USE_NL(b a) INDEX(b PSB_WORKSHEETS_U1) INDEX(a PSB_WS_ELEMENT_LINES_N1) */
sum(nvl(a.element_cost, 0)) Sum_Elem
from PSB_WS_ELEMENT_LINES a,
PSB_WORKSHEETS b
where exists
(select 1
from PSB_WS_SUBMIT_SERVICE_PACKAGES d
where d.service_package_id = a.service_package_id
and d.worksheet_id = p_worksheet_id)
and a.currency_code = p_currency_code
and a.stage_set_id = b.stage_set_id
and a.pay_element_id = p_pay_element_id
and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
and a.budget_year_id = p_budget_year_id
and a.position_line_id = p_position_line_id
and b.worksheet_id = p_worksheet_id;
select /*+ ORDERED INDEX(a PSB_WS_ELEMENT_LINES_N1) */
sum(nvl(a.element_cost, 0)) Sum_Elem
from PSB_WS_ELEMENT_LINES a,
PSB_WORKSHEETS b,
PSB_PAY_ELEMENTS c
where exists
(select 1
from PSB_WS_SUBMIT_SERVICE_PACKAGES d
where d.service_package_id = a.service_package_id
and d.worksheet_id = p_worksheet_id)
and a.currency_code = p_currency_code
and a.stage_set_id = b.stage_set_id
and a.pay_element_id = c.pay_element_id
and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
and a.budget_year_id = p_budget_year_id
and a.position_line_id = p_position_line_id
and b.worksheet_id = p_worksheet_id
and c.processing_type = 'R'
and c.salary_flag = 'Y'
and c.business_group_id = p_business_group_id
and c.data_extract_id = p_data_extract_id;
FND_MSG_PUB.Delete_Msg;
insert into PSB_ERROR_MESSAGES
(Concurrent_Request_ID,
Process_ID,
Source_Process,
Description,
Creation_Date,
Created_By)
values (l_reqid,
p_worksheet_id,
'WORKSHEET_CREATION',
l_description,
sysdate,
l_userid);
FND_MSG_PUB.Delete_Msg;
insert into PSB_ERROR_MESSAGES
(Concurrent_Request_ID,
Process_ID,
Source_Process,
Description,
Creation_Date,
Created_By)
values (l_reqid,
p_worksheet_id,
'WORKSHEET_CREATION',
l_description,
sysdate,
l_userid);
FND_MSG_PUB.Delete_Msg;
insert into PSB_ERROR_MESSAGES
(Concurrent_Request_ID,
Process_ID,
Source_Process,
Description,
Creation_Date,
Created_By)
values (l_reqid,
p_worksheet_id,
'WORKSHEET_CREATION',
l_description,
sysdate,
l_userid);
select prefix_operator,
amount
from PSB_CONSTRAINT_FORMULAS
where constraint_id = p_constraint_id;
select sum(nvl(a.annual_fte, 0)) Sum_FTE
from PSB_WS_FTE_LINES a,
PSB_WORKSHEETS b
where exists
(select 1
from PSB_WS_LINES_POSITIONS c,
PSB_WS_POSITION_LINES d,
PSB_BUDGET_POSITIONS e,
PSB_SET_RELATIONS f
where c.position_line_id = a.position_line_id
and c.position_line_id = d.position_line_id
and c.worksheet_id = p_worksheet_id
and d.position_id = e.position_id
and e.data_extract_id = p_data_extract_id
and e.account_position_set_id = f.account_position_set_id
and f.constraint_id = p_constraint_id)
and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
and a.stage_set_id = b.stage_set_id
and a.budget_year_id = p_budget_year_id
and b.worksheet_id = p_worksheet_id;
select sum(nvl(a.annual_fte, 0)) Sum_FTE
from PSB_WS_FTE_LINES a,
PSB_WORKSHEETS b
where exists
(select 1
from PSB_WS_LINES_POSITIONS c,
PSB_WS_POSITION_LINES d,
PSB_BUDGET_POSITIONS e,
PSB_SET_RELATIONS f
where c.position_line_id = a.position_line_id
and c.position_line_id = d.position_line_id
and c.worksheet_id = p_worksheet_id
and d.position_id = e.position_id
and e.data_extract_id = p_data_extract_id
and e.account_position_set_id = f.account_position_set_id
and f.constraint_id = p_constraint_id)
and exists
(select 1
from PSB_WS_SUBMIT_SERVICE_PACKAGES g
where g.service_package_id = a.service_package_id
and g.worksheet_id = p_worksheet_id)
and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
and a.stage_set_id = b.stage_set_id
and a.budget_year_id = p_budget_year_id
and b.worksheet_id = p_worksheet_id;
select /*+ ORDERED USE_NL(b a) INDEX(b PSB_WORKSHEETS_U1) INDEX(a PSB_WS_ELEMENT_LINES_N1) */
sum(nvl(a.annual_fte, 0)) Sum_FTE
from PSB_WS_FTE_LINES a,
PSB_WORKSHEETS b
where b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
and a.stage_set_id = b.stage_set_id
and a.budget_year_id = p_budget_year_id
and a.position_line_id = p_position_line_id
and b.worksheet_id = p_worksheet_id;
select /*+ ORDERED USE_NL(b a) INDEX(b PSB_WORKSHEETS_U1) INDEX(a PSB_WS_ELEMENT_LINES_N1) */
sum(nvl(a.annual_fte, 0)) Sum_FTE
from PSB_WS_FTE_LINES a,
PSB_WORKSHEETS b
where exists
(select 1
from PSB_WS_SUBMIT_SERVICE_PACKAGES d
where d.worksheet_id = p_worksheet_id
and d.service_package_id = a.service_package_id)
and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
and a.stage_set_id = b.stage_set_id
and a.budget_year_id = p_budget_year_id
and a.position_line_id = p_position_line_id
and b.worksheet_id = p_worksheet_id;
FND_MSG_PUB.Delete_Msg;
insert into PSB_ERROR_MESSAGES
(Concurrent_Request_ID,
Process_ID,
Source_Process,
Description,
Creation_Date,
Created_By)
values (l_reqid,
p_worksheet_id,
'WORKSHEET_CREATION',
l_description,
sysdate,
l_userid);