[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select budget_calendar_id,
budget_group_id,
rounding_factor,
nvl(global_worksheet_id, worksheet_id) global_worksheet_id,
nvl(data_extract_id, global_data_extract_id) data_extract_id,
local_copy_flag
from PSB_WORKSHEETS_V
where worksheet_id = Worksheet;
select fte_line_id,
period1_fte, period2_fte, period3_fte, period4_fte,
period5_fte, period6_fte, period7_fte, period8_fte,
period9_fte, period10_fte, period11_fte, period12_fte,
period13_fte, period14_fte, period15_fte, period16_fte,
period17_fte, period18_fte, period19_fte, period20_fte,
period21_fte, period22_fte, period23_fte, period24_fte,
period25_fte, period26_fte, period27_fte, period28_fte,
period29_fte, period30_fte, period31_fte, period32_fte,
period33_fte, period34_fte, period35_fte, period36_fte,
period37_fte, period38_fte, period39_fte, period40_fte,
period41_fte, period42_fte, period43_fte, period44_fte,
period45_fte, period46_fte, period47_fte, period48_fte,
period49_fte, period50_fte, period51_fte, period52_fte,
period53_fte, period54_fte, period55_fte, period56_fte,
period57_fte, period58_fte, period59_fte, period60_fte,
annual_fte
from PSB_WS_FTE_LINES a
where CurSeq between start_stage_seq and current_stage_seq
and stage_set_id = StSet
and service_package_id = SvcPkg
and budget_year_id = BudYr
and position_line_id = PosLine;
select nvl(business_group_id, root_business_group_id) business_group_id
from PSB_BUDGET_GROUPS_V
where budget_group_id = BudGrp;
select psb_ws_fte_lines_s.nextval FteLineID
from dual;
select service_package_id
from PSB_SERVICE_PACKAGES
where base_service_package = 'Y'
and global_worksheet_id = GlobalWS;
select pay_element_id
from PSB_PAY_ELEMENTS
where processing_type = 'R'
and business_group_id = BusGrp
and data_extract_id = DataExt
/* For Bug No. 2250319 */
order by pay_element_id;
p_insert_from_base IN VARCHAR2 := FND_API.G_FALSE,
p_update_from_base IN VARCHAR2 := FND_API.G_FALSE,
p_worksheet_id IN NUMBER,
p_flex_mapping_set_id IN NUMBER := FND_API.G_MISS_NUM,
p_rounding_factor IN NUMBER,
p_position_line_id IN NUMBER,
p_pay_element_id IN NUMBER,
p_budget_year_id IN NUMBER,
p_base_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
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_group_id IN NUMBER,
/*For Bug No : 2811698 Start*/
p_period_fte IN PSB_WS_ACCT1.g_prdamt_tbl_type,
p_total_fte IN NUMBER,
p_num_budget_periods IN NUMBER
/*For Bug No : 2811698 End*/
);
select 'Valid'
from PSB_BUDGET_GROUPS
where budget_group_type = 'R'
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)
and budget_group_id = p_position_budget_group_id
start with budget_group_id = l_budget_group_id
connect by prior budget_group_id = parent_budget_group_id;
select pay_element_id,
name,
processing_type,
max_element_value_type,
max_element_value,
option_flag,
overwrite_flag,
salary_flag,
salary_type,
follow_salary,
period_type,
process_period_type
from PSB_PAY_ELEMENTS
where (((start_date <= PSB_WS_ACCT1.g_end_est_date)
and (end_date is null))
or ((start_date between PSB_WS_ACCT1.g_startdate_cy and PSB_WS_ACCT1.g_end_est_date)
or (end_date between PSB_WS_ACCT1.g_startdate_cy and PSB_WS_ACCT1.g_end_est_date)
or ((start_date < PSB_WS_ACCT1.g_startdate_cy)
and (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
order by salary_flag desc,
pay_element_id;
select attribute_id ,
system_attribute_type ,
NVL(value_table_flag, 'N') value_table_flag
from PSB_ATTRIBUTES
where system_attribute_type IN ('FTE', 'DEFAULT_WEEKLY_HOURS',
'HIREDATE', 'ADJUSTMENT_DATE')
and business_group_id = p_business_group_id;
select attribute_id,
attribute_value,
attribute_value_id
from PSB_POSITION_ASSIGNMENTS
where attribute_id in (g_hiredate_id, g_adjdate_id)
and worksheet_id is null
and assignment_type = 'ATTRIBUTE'
and position_id = p_position_id;*/
SELECT attribute_id,
attribute_value,
attribute_value_id
FROM PSB_POSITION_ASSIGNMENTS po1
WHERE attribute_id IN (g_hiredate_id, g_adjdate_id)
AND ( (worksheet_id = p_worksheet_id
AND (p_local_parameter_flag = 'Y'))
OR ( worksheet_id IS NULL
AND ( (NOT EXISTS ( SELECT 1
FROM psb_position_assignments po2
WHERE po1.position_id = po2.position_id
AND po1.attribute_id = po2.attribute_id
AND po2.worksheet_id = p_worksheet_id))
OR ( p_local_parameter_flag = 'N'))))
AND assignment_type = 'ATTRIBUTE'
AND position_id = p_position_id;
select attribute_value_id,
attribute_value
from PSB_ATTRIBUTE_VALUES
where attribute_value_id in (l_hiredate_value_id, l_adjdate_value_id);
select factor
from PSB_HRMS_FACTORS
where hrms_period_type = p_hrms_period_type
and budget_period_type = p_budget_period_type;
select code_combination_id,
distribution_percent,
effective_start_date,
effective_end_date
from PSB_POSITION_PAY_DISTRIBUTIONS a
where position_id = p_position_id
and chart_of_accounts_id = p_flex_code
and code_combination_id is not null
and ((worksheet_id = p_worksheet_id) or (worksheet_id is null
and not exists
(select 1
from psb_position_pay_distributions c
where (
( nvl(c.effective_start_date, p_end_date + 1)
between nvl(a.effective_start_date, p_end_date)
and nvl(a.effective_end_date, nvl(p_end_date, c.effective_start_date)))
OR ( nvl(a.effective_start_date, p_end_date + 1)
between nvl(c.effective_start_date, p_end_date)
and nvl(c.effective_end_date, nvl(p_end_date, a.effective_start_date)))
)
and c.position_id = a.position_id
and c.chart_of_accounts_id = p_flex_code
and c.code_combination_id is not null
and c.worksheet_id = p_worksheet_id
)))
-- commented for bug 3216145
-- if there exists worksheet specific records
-- pick up the data
/*
and (((p_end_date is not null)
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)))))
or ((p_end_date is null)
and (nvl(effective_end_date, p_start_date) >= p_start_date))) */
order by distribution_percent desc;
select code_combination_id,
distribution_percent,
effective_start_date,
effective_end_date
from PSB_POSITION_PAY_DISTRIBUTIONS a
where code_combination_id is not null
/* Bug No 2747205 Start */
and chart_of_accounts_id = p_flex_code
and (worksheet_id is null
and not exists
(select 1
from psb_position_pay_distributions c
where (
( nvl(c.effective_start_date, p_end_date + 1)
between nvl(a.effective_start_date, p_end_date)
and nvl(a.effective_end_date, nvl(p_end_date, c.effective_start_date)))
OR ( nvl(a.effective_start_date, p_end_date + 1)
between nvl(c.effective_start_date, p_end_date)
and nvl(c.effective_end_date, nvl(p_end_date, a.effective_start_date)))
)
and c.position_id = a.position_id
and c.chart_of_accounts_id = p_flex_code
and c.code_combination_id is null
and c.worksheet_id = p_worksheet_id
))
-- and worksheet_id is null
-- and chart_of_accounts_id = p_flex_code
-- and (((p_end_date is not null)
-- 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)))))
-- or ((p_end_date is null)
-- and (nvl(effective_end_date, p_start_date) >= p_start_date)))
/* Bug No 2747205 End */
and position_id = p_position_id
order by distribution_percent desc;
select a.budget_group_id,
b.num_proposed_years
from PSB_SET_RELATIONS a,
PSB_BUDGET_GROUPS b,
PSB_BUDGET_ACCOUNTS c
where a.budget_group_id = b.budget_group_id
and b.effective_start_date <= PSB_WS_ACCT1.g_startdate_pp
and (b.effective_end_date is null
or b.effective_end_date >= PSB_WS_ACCT1.g_enddate_cy)
and b.budget_group_type = 'R'
and ((b.budget_group_id = p_root_budget_group_id) or
(b.root_budget_group_id = p_root_budget_group_id))
and a.account_position_set_id = c.account_position_set_id
and c.code_combination_id = CCID;
select b.position_line_id,
b.budget_group_id
from PSB_WS_LINES_POSITIONS a,
PSB_WS_POSITION_LINES b
where a.position_line_id = b.position_line_id
and a.worksheet_id = g_global_worksheet_id
and b.position_id = p_position_id;
select psb_ws_position_lines_s.nextval PosLineID
from dual;
update PSB_WS_POSITION_LINES
set budget_group_id = p_budget_group_id,
copy_of_position_line_id = decode(p_copy_of_position_line_id, FND_API.G_MISS_NUM, null, p_copy_of_position_line_id),
last_update_date = sysdate,
last_updated_by = l_userid,
last_update_login = l_loginid
where position_line_id = l_poslineid;
delete from PSB_WS_LINES_POSITIONS
where position_line_id = l_poslineid;
insert into PSB_WS_POSITION_LINES
(position_line_id,
position_id,
budget_group_id,
copy_of_position_line_id,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
values (l_poslineid,
p_position_id,
p_budget_group_id,
decode(p_copy_of_position_line_id, FND_API.G_MISS_NUM, null, p_copy_of_position_line_id),
sysdate,
l_userid,
l_loginid,
l_userid,
sysdate);
update PSB_WS_LINES_POSITIONS
set freeze_flag = decode(p_freeze_flag, FND_API.G_FALSE, null, FND_API.G_TRUE, 'Y', p_freeze_flag),
view_line_flag = decode(p_view_line_flag, FND_API.G_TRUE, 'Y', FND_API.G_FALSE, null, p_view_line_flag),
last_update_date = sysdate,
last_updated_by = l_userid,
last_update_login = l_loginid
where position_line_id = p_position_line_id
and worksheet_id = p_worksheet_id;
insert into PSB_WS_LINES_POSITIONS
(worksheet_id,
position_line_id,
freeze_flag,
view_line_flag,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
values
(p_worksheet_id,
p_position_line_id,
decode(p_freeze_flag, FND_API.G_FALSE, null, FND_API.G_TRUE, 'Y', p_freeze_flag),
decode(p_view_line_flag, FND_API.G_TRUE, 'Y', FND_API.G_FALSE, null, p_view_line_flag),
sysdate,
l_userid,
l_loginid,
l_userid,
sysdate);
update PSB_WS_FTE_LINES
set period1_fte = l_period_fte(1), period2_fte = l_period_fte(2),
period3_fte = l_period_fte(3), period4_fte = l_period_fte(4),
period5_fte = l_period_fte(5), period6_fte = l_period_fte(6),
period7_fte = l_period_fte(7), period8_fte = l_period_fte(8),
period9_fte = l_period_fte(9), period10_fte = l_period_fte(10),
period11_fte = l_period_fte(11), period12_fte = l_period_fte(12),
period13_fte = l_period_fte(13), period14_fte = l_period_fte(14),
period15_fte = l_period_fte(15), period16_fte = l_period_fte(16),
period17_fte = l_period_fte(17), period18_fte = l_period_fte(18),
period19_fte = l_period_fte(19), period20_fte = l_period_fte(20),
period21_fte = l_period_fte(21), period22_fte = l_period_fte(22),
period23_fte = l_period_fte(23), period24_fte = l_period_fte(24),
period25_fte = l_period_fte(25), period26_fte = l_period_fte(26),
period27_fte = l_period_fte(27), period28_fte = l_period_fte(28),
period29_fte = l_period_fte(29), period30_fte = l_period_fte(30),
period31_fte = l_period_fte(31), period32_fte = l_period_fte(32),
period33_fte = l_period_fte(33), period34_fte = l_period_fte(34),
period35_fte = l_period_fte(35), period36_fte = l_period_fte(36),
period37_fte = l_period_fte(37), period38_fte = l_period_fte(38),
period39_fte = l_period_fte(39), period40_fte = l_period_fte(40),
period41_fte = l_period_fte(41), period42_fte = l_period_fte(42),
period43_fte = l_period_fte(43), period44_fte = l_period_fte(44),
period45_fte = l_period_fte(45), period46_fte = l_period_fte(46),
period47_fte = l_period_fte(47), period48_fte = l_period_fte(48),
period49_fte = l_period_fte(49), period50_fte = l_period_fte(50),
period51_fte = l_period_fte(51), period52_fte = l_period_fte(52),
period53_fte = l_period_fte(53), period54_fte = l_period_fte(54),
period55_fte = l_period_fte(55), period56_fte = l_period_fte(56),
period57_fte = l_period_fte(57), period58_fte = l_period_fte(58),
period59_fte = l_period_fte(59), period60_fte = l_period_fte(60),
end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
annual_fte = l_annual_fte,
last_update_date = sysdate,
last_updated_by = l_userid,
last_update_login = l_loginid
where fte_line_id = l_spflid;
update PSB_WS_FTE_LINES
set period1_fte = l_period_fte(1), period2_fte = l_period_fte(2),
period3_fte = l_period_fte(3), period4_fte = l_period_fte(4),
period5_fte = l_period_fte(5), period6_fte = l_period_fte(6),
period7_fte = l_period_fte(7), period8_fte = l_period_fte(8),
period9_fte = l_period_fte(9), period10_fte = l_period_fte(10),
period11_fte = l_period_fte(11), period12_fte = l_period_fte(12),
period13_fte = l_period_fte(13), period14_fte = l_period_fte(14),
period15_fte = l_period_fte(15), period16_fte = l_period_fte(16),
period17_fte = l_period_fte(17), period18_fte = l_period_fte(18),
period19_fte = l_period_fte(19), period20_fte = l_period_fte(20),
period21_fte = l_period_fte(21), period22_fte = l_period_fte(22),
period23_fte = l_period_fte(23), period24_fte = l_period_fte(24),
period25_fte = l_period_fte(25), period26_fte = l_period_fte(26),
period27_fte = l_period_fte(27), period28_fte = l_period_fte(28),
period29_fte = l_period_fte(29), period30_fte = l_period_fte(30),
period31_fte = l_period_fte(31), period32_fte = l_period_fte(32),
period33_fte = l_period_fte(33), period34_fte = l_period_fte(34),
period35_fte = l_period_fte(35), period36_fte = l_period_fte(36),
period37_fte = l_period_fte(37), period38_fte = l_period_fte(38),
period39_fte = l_period_fte(39), period40_fte = l_period_fte(40),
period41_fte = l_period_fte(41), period42_fte = l_period_fte(42),
period43_fte = l_period_fte(43), period44_fte = l_period_fte(44),
period45_fte = l_period_fte(45), period46_fte = l_period_fte(46),
period47_fte = l_period_fte(47), period48_fte = l_period_fte(48),
period49_fte = l_period_fte(49), period50_fte = l_period_fte(50),
period51_fte = l_period_fte(51), period52_fte = l_period_fte(52),
period53_fte = l_period_fte(53), period54_fte = l_period_fte(54),
period55_fte = l_period_fte(55), period56_fte = l_period_fte(56),
period57_fte = l_period_fte(57), period58_fte = l_period_fte(58),
period59_fte = l_period_fte(59), period60_fte = l_period_fte(60),
end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
annual_fte = l_annual_fte,
last_update_date = sysdate,
last_updated_by = l_userid,
last_update_login = l_loginid
where p_current_stage_seq between start_stage_seq and current_stage_seq
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;
insert into PSB_WS_FTE_LINES
(fte_line_id,
position_line_id,
budget_year_id,
service_package_id,
stage_set_id,
start_stage_seq,
current_stage_seq,
end_stage_seq,
period1_fte, period2_fte, period3_fte, period4_fte,
period5_fte, period6_fte, period7_fte, period8_fte,
period9_fte, period10_fte, period11_fte, period12_fte,
period13_fte, period14_fte, period15_fte, period16_fte,
period17_fte, period18_fte, period19_fte, period20_fte,
period21_fte, period22_fte, period23_fte, period24_fte,
period25_fte, period26_fte, period27_fte, period28_fte,
period29_fte, period30_fte, period31_fte, period32_fte,
period33_fte, period34_fte, period35_fte, period36_fte,
period37_fte, period38_fte, period39_fte, period40_fte,
period41_fte, period42_fte, period43_fte, period44_fte,
period45_fte, period46_fte, period47_fte, period48_fte,
period49_fte, period50_fte, period51_fte, period52_fte,
period53_fte, period54_fte, period55_fte, period56_fte,
period57_fte, period58_fte, period59_fte, period60_fte,
annual_fte,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
values (l_ftelineid,
p_position_line_id,
p_budget_year_id,
p_service_package_id,
p_stage_set_id,
l_start_stage_seq,
p_current_stage_seq,
decode(p_end_stage_seq, FND_API.G_MISS_NUM, null, p_end_stage_seq),
l_period_fte(1), l_period_fte(2), l_period_fte(3), l_period_fte(4),
l_period_fte(5), l_period_fte(6), l_period_fte(7), l_period_fte(8),
l_period_fte(9), l_period_fte(10), l_period_fte(11), l_period_fte(12),
l_period_fte(13), l_period_fte(14), l_period_fte(15), l_period_fte(16),
l_period_fte(17), l_period_fte(18), l_period_fte(19), l_period_fte(20),
l_period_fte(21), l_period_fte(22), l_period_fte(23), l_period_fte(24),
l_period_fte(25), l_period_fte(26), l_period_fte(27), l_period_fte(28),
l_period_fte(29), l_period_fte(30), l_period_fte(31), l_period_fte(32),
l_period_fte(33), l_period_fte(34), l_period_fte(35), l_period_fte(36),
l_period_fte(37), l_period_fte(38), l_period_fte(39), l_period_fte(40),
l_period_fte(41), l_period_fte(42), l_period_fte(43), l_period_fte(44),
l_period_fte(45), l_period_fte(46), l_period_fte(47), l_period_fte(48),
l_period_fte(49), l_period_fte(50), l_period_fte(51), l_period_fte(52),
l_period_fte(53), l_period_fte(54), l_period_fte(55), l_period_fte(56),
l_period_fte(57), l_period_fte(58), l_period_fte(59), l_period_fte(60),
l_annual_fte,
sysdate,
l_userid,
l_loginid,
l_userid,
sysdate);
p_insert_from_base => FND_API.G_TRUE,
p_worksheet_id => p_worksheet_id,
p_flex_mapping_set_id => p_flex_mapping_set_id,
p_rounding_factor => l_rounding_factor,
p_position_line_id => p_position_line_id,
p_pay_element_id => c_Elements_Rec.pay_element_id,
p_budget_year_id => p_budget_year_id,
p_base_service_package_id => l_base_spid,
p_service_package_id => p_service_package_id,
p_stage_set_id => p_stage_set_id,
p_start_stage_seq => l_start_stage_seq,
p_current_stage_seq => p_current_stage_seq,
p_budget_group_id => p_budget_group_id,
/*For Bug No : 2811698 Start*/
p_period_fte => p_period_fte,
p_total_fte => l_total_fte,
p_num_budget_periods => l_num_budget_periods
/*For Bug No : 2811698 End*/
);
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 => p_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);
l_update_from_base VARCHAR2(1) := FND_API.G_FALSE;
select position_line_id,
budget_year_id,
service_package_id,
stage_set_id,
start_stage_seq,
current_stage_seq,
end_stage_seq,
period1_fte, period2_fte, period3_fte, period4_fte,
period5_fte, period6_fte, period7_fte, period8_fte,
period9_fte, period10_fte, period11_fte, period12_fte,
period13_fte, period14_fte, period15_fte, period16_fte,
period17_fte, period18_fte, period19_fte, period20_fte,
period21_fte, period22_fte, period23_fte, period24_fte,
period25_fte, period26_fte, period27_fte, period28_fte,
period29_fte, period30_fte, period31_fte, period32_fte,
period33_fte, period34_fte, period35_fte, period36_fte,
period37_fte, period38_fte, period39_fte, period40_fte,
period41_fte, period42_fte, period43_fte, period44_fte,
period45_fte, period46_fte, period47_fte, period48_fte,
period49_fte, period50_fte, period51_fte, period52_fte,
period53_fte, period54_fte, period55_fte, period56_fte,
period57_fte, period58_fte, period59_fte, period60_fte,
annual_fte
from PSB_WS_FTE_LINES
where fte_line_id = p_fte_line_id;
select Max(sequence_number) sequence_number
from PSB_BUDGET_STAGES
where sequence_number < l_current_stage_seq
and budget_stage_set_id = l_stage_set_id;
PSB_WORKSHEET.Delete_WFL
(p_api_version => 1.0,
p_return_status => l_return_status,
p_fte_line_id => l_spflid);
sql_wfl := 'insert into PSB_WS_FTE_LINES ' ||
'(fte_line_id, ' ||
'position_line_id, ' ||
'budget_year_id, ' ||
'service_package_id, ' ||
'stage_set_id, ' ||
'start_stage_seq, ' ||
'current_stage_seq, ' ||
'end_stage_seq, ';
'last_update_date, ' ||
'last_updated_by, ' ||
'last_update_login, ' ||
'created_by, ' ||
'creation_date) ' ||
'select :b_fte_line_id, ' ||
'position_line_id, ' ||
'budget_year_id, ' ||
'service_package_id, ' ||
'stage_set_id, ' ||
'start_stage_seq, ' ||
':b_previous_stage_seq, ' ||
':b_previous_stage_seq, ';
':b_last_update_date, ' ||
':b_last_updated_by, ' ||
':b_last_update_login , ' ||
':b_created_by, ' ||
':b_creation_date ' ||
'from PSB_WS_FTE_LINES ' ||
'where fte_line_id = :b_fte_line_id';
sql_wfl := 'update PSB_WS_FTE_LINES ' ||
'set service_package_id = decode( :p_service_package_id1 , :gmn1,'||
'service_package_id, :p_service_package_id2 ), ';
'last_update_date = :b_last_update_date, ' ||
'last_updated_by = :b_last_updated_by, ' ||
'last_update_login = :b_last_update_login ' ||
'where fte_line_id = :b_fte_line_id' ;
p_update_from_base => l_update_from_base,
p_worksheet_id => p_worksheet_id,
p_rounding_factor => l_rounding_factor,
p_position_line_id => l_position_line_id,
p_pay_element_id => c_Elements_Rec.pay_element_id,
p_budget_year_id => l_budget_year_id,
p_base_service_package_id => l_base_spid,
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_group_id => p_budget_group_id,
/*For Bug No : 2811698 Start*/
p_period_fte => p_period_fte,
p_total_fte => l_total_fte,
p_num_budget_periods => l_num_budget_periods
/*For Bug No : 2811698 End*/
);
Update_Annual_FTE
(p_api_version => 1.0,
p_return_status => l_return_status,
p_worksheet_id => p_worksheet_id,
p_position_line_id => l_position_line_id,
p_budget_year_id => l_budget_year_id,
p_service_package_id => l_service_package_id,
p_stage_set_id => l_stage_set_id,
p_current_stage_seq => l_current_stage_seq,
p_budget_group_id => p_budget_group_id);
p_insert_from_base IN VARCHAR2 := FND_API.G_FALSE,
p_update_from_base IN VARCHAR2 := FND_API.G_FALSE,
p_worksheet_id IN NUMBER,
p_flex_mapping_set_id IN NUMBER := FND_API.G_MISS_NUM,
p_rounding_factor IN NUMBER,
p_position_line_id IN NUMBER,
p_pay_element_id IN NUMBER,
p_budget_year_id IN NUMBER,
p_base_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
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_group_id IN NUMBER,
/*For Bug No : 2811698 Start*/
p_period_fte IN PSB_WS_ACCT1.g_prdamt_tbl_type,
p_total_fte IN NUMBER,
p_num_budget_periods IN NUMBER
/*For Bug No : 2811698 End*/
) IS
l_account_line_id NUMBER;
select element_line_id,
element_set_id,
currency_code,
element_cost
from PSB_WS_ELEMENT_LINES a
where p_current_stage_seq between start_stage_seq and current_stage_seq
and pay_element_id = p_pay_element_id
and stage_set_id = p_stage_set_id
and service_package_id = p_base_service_package_id
and budget_year_id = p_budget_year_id
and position_line_id = p_position_line_id;
select a.account_line_id,
a.budget_group_id,
a.code_combination_id,
a.currency_code,
a.ytd_amount,
a.annual_fte,
a.element_set_id,
a.salary_account_line,
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
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_base_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.service_package_id = p_base_service_package_id
and b.pay_element_id = p_pay_element_id
and b.budget_year_id = p_budget_year_id
and b.position_line_id = p_position_line_id;
select element_line_id,
element_set_id,
currency_code,
element_cost
from PSB_WS_ELEMENT_LINES a
where p_current_stage_seq between start_stage_seq and current_stage_seq
and pay_element_id = p_pay_element_id
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 a.account_line_id,
a.budget_group_id,
a.code_combination_id,
a.currency_code,
a.ytd_amount,
a.annual_fte,
a.element_set_id,
a.salary_account_line,
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
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.service_package_id = p_service_package_id
and b.pay_element_id = p_pay_element_id
and b.budget_year_id = p_budget_year_id
and b.position_line_id = p_position_line_id;
SELECT 1
FROM PSB_WS_ELEMENT_LINES
WHERE position_line_id = p_position_line_id
AND budget_year_id = p_budget_year_id
AND element_set_id = element_set
AND pay_element_id < p_pay_element_id;
SELECT data_extract_id,
budget_calendar_id,
budget_group_id
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id;
SELECT nvl(root_budget_group_id, budget_group_id) root_budget_group_id,
nvl(set_of_books_id, root_set_of_books_id) set_of_books_id
FROM PSB_BUDGET_GROUPS_V
WHERE budget_group_id = l_budget_group_id;
SELECT chart_of_accounts_id
FROM GL_SETS_OF_BOOKS
WHERE set_of_books_id = l_set_of_books_id;
SELECT position_id
FROM psb_ws_position_lines
WHERE position_line_id = p_position_line_id;
SELECT annual_fte
FROM psb_ws_fte_lines
WHERE position_line_id = p_position_line_id
AND budget_year_id= p_budget_year_id;
SELECT pay_element_id,
distribution_percent,
code_combination_id
FROM psb_element_pos_set_groups pepsg ,
psb_pay_element_distributions pped
WHERE pepsg.position_set_group_id = pped.position_set_group_id
AND pepsg.pay_element_id = p_pay_element_id
AND code_combination_id = l_ccid;
if FND_API.to_Boolean(p_insert_from_base) then
begin
for c_Element_Dist_Rec in c_Base_Element_Dist loop
for l_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
l_period_amount(l_index) := null;
if FND_API.to_Boolean(p_update_from_base) then
begin
for c_Base_Element_Dist_Rec in c_Base_Element_Dist loop
if c_Base_Element_Dist_Rec.code_combination_id = c_Element_Dist_Rec.code_combination_id then
begin
/*For Bug No : 2811698 Start*/
--changed the logic to calculate the FTE proration here itself
--instead of from Prorate_FTE_Base. This will ensure that period costs
--will come properly.
if (nvl(c_Base_Element_Dist_Rec.annual_fte,0) <> 0) then
/* Bug 4379636 Start */
-- l_annual_fte_ratio := p_total_fte / (c_Base_Element_Dist_Rec.annual_fte * p_num_budget_periods);
if FND_API.to_Boolean(p_update_from_base) then
begin
for c_Base_Element_Cost_Rec in c_Base_Element_Cost loop
l_element_cost := c_Base_Element_Cost_Rec.element_cost * nvl(l_annual_fte_ratio, 0) + l_rounding_diff;
select psb_ws_element_lines_s.nextval ElmLineID
from dual;
select element_line_id,
element_cost
from PSB_WS_ELEMENT_LINES a
where p_current_stage_seq between start_stage_seq and current_stage_seq
and pay_element_id = p_pay_element_id
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;
update PSB_WS_ELEMENT_LINES
set element_cost = nvl(p_element_cost, 0),
element_set_id = p_element_set_id,
current_stage_seq = p_current_stage_seq,
end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
last_update_date = sysdate,
last_updated_by = l_userid,
last_update_login = l_loginid
where element_line_id = l_spelid;
update PSB_WS_ELEMENT_LINES a
set element_cost = nvl(p_element_cost, 0),
element_set_id = p_element_set_id,
current_stage_seq = p_current_stage_seq,
end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
last_update_date = sysdate,
last_updated_by = l_userid,
last_update_login = l_loginid
where start_stage_seq = p_start_stage_seq
and currency_code = p_currency_code
and pay_element_id = p_pay_element_id
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;
insert into PSB_WS_ELEMENT_LINES
(element_line_id,
position_line_id,
budget_year_id,
pay_element_id,
currency_code,
element_cost,
element_set_id,
service_package_id,
stage_set_id,
start_stage_seq,
current_stage_seq,
end_stage_seq,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
functional_transaction)
values (l_element_line_id,
p_position_line_id,
p_budget_year_id,
p_pay_element_id,
p_currency_code,
nvl(p_element_cost, 0),
p_element_set_id,
p_service_package_id,
p_stage_set_id,
l_start_stage_seq,
p_current_stage_seq,
decode(p_end_stage_seq, FND_API.G_MISS_NUM, null, p_end_stage_seq),
sysdate,
l_userid,
l_loginid,
l_userid,
sysdate,
p_functional_transaction);
select psb_ws_element_lines_s.nextval ElmLineID
from dual;
select Max(sequence_number) sequence_number
from PSB_BUDGET_STAGES
where sequence_number < l_current_stage_seq
and budget_stage_set_id = l_stage_set_id;
sql_wel := 'select position_line_id, budget_year_id, pay_element_id, currency_code, element_cost, ' ||
'element_set_id, service_package_id, stage_set_id, start_stage_seq, current_stage_seq, ' ||
'end_stage_seq ' ||
'from PSB_WS_ELEMENT_LINES ' ||
'where element_line_id = :ElemLineID';
sql_wel := 'select ' ||
'element_line_id, element_cost ' ||
'from PSB_WS_ELEMENT_LINES a ' ||
'where :current_stage_seq between start_stage_seq and current_stage_seq ' ||
'and pay_element_id = :pay_element_id ' ||
'and stage_set_id = :stage_set_id ' ||
'and service_package_id = :service_package_id ' ||
'and budget_year_id = :budget_year_id ' ||
'and position_line_id = :position_line_id';
PSB_WORKSHEET.Delete_WEL
(p_api_version => 1.0,
p_return_status => l_return_status,
p_element_line_id => l_spelid);
sql_wel := 'insert into PSB_WS_ELEMENT_LINES ' ||
'(element_line_id, ' ||
'position_line_id, ' ||
'budget_year_id, ' ||
'pay_element_id, ' ||
'currency_code, ' ||
'element_cost, ' ||
'element_set_id, ' ||
'service_package_id, ' ||
'stage_set_id, ' ||
'start_stage_seq, ' ||
'current_stage_seq, ' ||
'end_stage_seq, ' ||
'functional_transaction, ' ||
'last_update_date, ' ||
'last_updated_by, ' ||
'last_update_login, ' ||
'created_by, ' ||
'creation_date) ' ||
'select :b_element_line_id, ' ||
'position_line_id, ' ||
'budget_year_id, ' ||
'pay_element_id, ' ||
'currency_code, ' ||
'element_cost, ' ||
'element_set_id, ' ||
'service_package_id, ' ||
'stage_set_id, ' ||
'start_stage_seq, ' ||
':b_current_stage_seq, ' ||
':b_end_stage_seq, ' ||
'functional_transaction, ' ||
':b_last_update_date, ' ||
':b_last_updated_by, ' ||
':b_last_update_login, ' ||
':b_last_updated_by, ' ||
':b_creation_date ' ||
'from PSB_WS_ELEMENT_LINES ' ||
'where element_line_id = :b_element_line_id' ;
update PSB_WS_ELEMENT_LINES
set element_cost = decode(p_element_cost, FND_API.G_MISS_NUM, element_cost, null, 0, p_element_cost),
service_package_id = decode(p_service_package_id, FND_API.G_MISS_NUM, service_package_id, p_service_package_id),
start_stage_seq = l_current_stage_seq,
current_stage_seq = l_current_stage_seq,
last_update_date = sysdate,
last_updated_by = l_userid,
last_update_login = l_loginid
where element_line_id = p_element_line_id;
update PSB_WS_ELEMENT_LINES
set element_cost = decode(p_element_cost, FND_API.G_MISS_NUM, element_cost, null, 0, p_element_cost),
service_package_id = decode(p_service_package_id, FND_API.G_MISS_NUM, service_package_id, p_service_package_id),
current_stage_seq = decode(p_current_stage_seq, FND_API.G_MISS_NUM, current_stage_seq, p_current_stage_seq),
last_update_date = sysdate,
last_updated_by = l_userid,
last_update_login = l_loginid
where element_line_id = p_element_line_id;
PROCEDURE Update_Annual_FTE
( p_api_version IN NUMBER,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
p_return_status OUT NOCOPY VARCHAR2,
p_worksheet_id IN NUMBER,
p_position_line_id IN NUMBER,
p_budget_year_id IN NUMBER,
p_service_package_id IN NUMBER,
p_stage_set_id IN NUMBER,
p_current_stage_seq IN NUMBER,
p_budget_group_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Annual_FTE';
select annual_fte
from PSB_WS_FTE_LINES a
where p_current_stage_seq between start_stage_seq and current_stage_seq
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 a.account_line_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
where exists
(select 1
from PSB_PAY_ELEMENTS b,
PSB_WS_ELEMENT_LINES c
where b.processing_type = 'R'
and b.pay_element_id = c.pay_element_id
and c.element_set_id = a.element_set_id
and c.budget_year_id = p_budget_year_id
and c.position_line_id = p_position_line_id)
and a.salary_account_line = 'Y'
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;
END Update_Annual_FTE;
select a.segment1, a.segment2, a.segment3, a.segment4,
a.segment5, a.segment6, a.segment7, a.segment8,
a.segment9, a.segment10, a.segment11, a.segment12,
a.segment13, a.segment14, a.segment15, a.segment16,
a.segment17, a.segment18, a.segment19, a.segment20,
a.segment21, a.segment22, a.segment23, a.segment24,
a.segment25, a.segment26, a.segment27, a.segment28,
a.segment29, a.segment30,
a.effective_start_date, 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;