[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select a.budget_period_id,
a.budget_year_type_id,
b.year_category_type,
period_distribution_type,
calculation_period_type,
a.name,
a.start_date,
a.end_date
from PSB_BUDGET_YEAR_TYPES b,
PSB_BUDGET_PERIODS a
where b.budget_year_type_id = a.budget_year_type_id
and a.budget_period_type = 'Y'
and a.budget_calendar_id = p_budget_calendar_id
order by a.start_date;
select budget_period_id,
start_date,
end_date
from PSB_BUDGET_PERIODS
where budget_period_type = 'P'
and parent_budget_period_id = budyr_id
and budget_calendar_id = p_budget_calendar_id
order by start_date;
select budget_period_id,
start_date,
end_date
from PSB_BUDGET_PERIODS
where end_date <= enddate
and start_date >= startdate
and budget_period_type = 'C'
and parent_budget_period_id = budyr_id
and budget_calendar_id = p_budget_calendar_id
order by start_date;
select root_budget_group_id,
root_budget_group,
ps_account_position_set_id psid,
nps_account_position_set_id npsid
from PSB_BUDGET_GROUPS
where budget_group_id = p_budget_group_id;
select ps_account_position_set_id psid,
nps_account_position_set_id npsid
from PSB_BUDGET_GROUPS
where budget_group_id = BudgetGroupID;
select 1
from PSB_BUDGET_ACCOUNTS
where account_position_set_id = AccSet_ID
and code_combination_id = CCID;
SELECT root_budget_group_id,
root_budget_group,
ps_account_position_set_id,
nps_account_position_set_id
INTO
l_root_budget_group_id,
l_root_budget_group,
l_ps_acct_pos_set_id,
l_nps_acct_pos_set_id
FROM PSB_BUDGET_GROUPS
WHERE budget_group_id = p_budget_group_id;
SELECT ps_account_position_set_id,
nps_account_position_set_id
INTO
l_ps_acct_pos_set_id,
l_nps_acct_pos_set_id
FROM PSB_BUDGET_GROUPS
WHERE budget_group_id = l_root_budget_group_id;
select a.code_combination_id,
b.start_date_active,
b.end_date_active
from GL_CODE_COMBINATIONS b,
PSB_BUDGET_ACCOUNTS a
where b.enabled_flag = 'Y'
/* Bug 3692601 Start */
AND b.detail_budgeting_allowed_flag = 'Y'
/* Bug 3692601 End */
and b.code_combination_id = a.code_combination_id
and a.account_position_set_id = p_account_set_id;
p_update_cy_estimate IN VARCHAR2 := 'N'
/* bug end 3996052 */
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Create_Account_Dist';
select gl_cutoff_period,
nvl(global_worksheet_id, worksheet_id) global_worksheet_id,
nvl(allocrule_set_id, global_allocrule_set_id) allocrule_set_id,
budget_calendar_id,
rounding_factor,
stage_set_id,
flex_mapping_set_id,
current_stage_seq,
local_copy_flag
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 start_date_active,
end_date_active
from GL_CODE_COMBINATIONS
where code_combination_id = l_ccid;
select psb_service_packages_s.nextval ServicePackageID
from dual;
select account_line_id, budget_group_id, request_id, annual_fte, ytd_amount,
period1_amount, period2_amount, period3_amount, period4_amount,
period5_amount, period6_amount, period7_amount, period8_amount,
period9_amount, period10_amount, period11_amount, period12_amount,
period13_amount, period14_amount, period15_amount, period16_amount,
period17_amount, period18_amount, period19_amount, period20_amount,
period21_amount, period22_amount, period23_amount, period24_amount,
period25_amount, period26_amount, period27_amount, period28_amount,
period29_amount, period30_amount, period31_amount, period32_amount,
period33_amount, period34_amount, period35_amount, period36_amount,
period37_amount, period38_amount, period39_amount, period40_amount,
period41_amount, period42_amount, period43_amount, period44_amount,
period45_amount, period46_amount, period47_amount, period48_amount,
period49_amount, period50_amount, period51_amount, period52_amount,
period53_amount, period54_amount, period55_amount, period56_amount,
period57_amount, period58_amount, period59_amount, period60_amount
from PSB_WS_ACCOUNT_LINES a
where currency_code = p_currency_code
and l_current_stage_seq between start_stage_seq and current_stage_seq
and balance_type = p_balance_type
and template_id = p_template_id
and 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 = decode(nvl(l_local_copy_flag, 'N'), 'Y', p_worksheet_id, l_global_worksheet_id))
and stage_set_id = l_stage_set_id
and service_package_id = l_service_package_id
and budget_year_id = p_budget_year_id
and code_combination_id = l_ccid;
select account_line_id, budget_group_id, request_id, annual_fte, ytd_amount,
period1_amount, period2_amount, period3_amount, period4_amount,
period5_amount, period6_amount, period7_amount, period8_amount,
period9_amount, period10_amount, period11_amount, period12_amount,
period13_amount, period14_amount, period15_amount, period16_amount,
period17_amount, period18_amount, period19_amount, period20_amount,
period21_amount, period22_amount, period23_amount, period24_amount,
period25_amount, period26_amount, period27_amount, period28_amount,
period29_amount, period30_amount, period31_amount, period32_amount,
period33_amount, period34_amount, period35_amount, period36_amount,
period37_amount, period38_amount, period39_amount, period40_amount,
period41_amount, period42_amount, period43_amount, period44_amount,
period45_amount, period46_amount, period47_amount, period48_amount,
period49_amount, period50_amount, period51_amount, period52_amount,
period53_amount, period54_amount, period55_amount, period56_amount,
period57_amount, period58_amount, period59_amount, period60_amount
from PSB_WS_ACCOUNT_LINES a
where currency_code = p_currency_code
and l_current_stage_seq between start_stage_seq and current_stage_seq
and balance_type = p_balance_type
and template_id is null
and position_line_id = p_position_line_id
and element_set_id = p_element_set_id
and stage_set_id = l_stage_set_id
and service_package_id = l_service_package_id
and budget_year_id = p_budget_year_id
and code_combination_id = l_ccid;
select account_line_id, budget_group_id, request_id, annual_fte, ytd_amount,
period1_amount, period2_amount, period3_amount, period4_amount,
period5_amount, period6_amount, period7_amount, period8_amount,
period9_amount, period10_amount, period11_amount, period12_amount,
period13_amount, period14_amount, period15_amount, period16_amount,
period17_amount, period18_amount, period19_amount, period20_amount,
period21_amount, period22_amount, period23_amount, period24_amount,
period25_amount, period26_amount, period27_amount, period28_amount,
period29_amount, period30_amount, period31_amount, period32_amount,
period33_amount, period34_amount, period35_amount, period36_amount,
period37_amount, period38_amount, period39_amount, period40_amount,
period41_amount, period42_amount, period43_amount, period44_amount,
period45_amount, period46_amount, period47_amount, period48_amount,
period49_amount, period50_amount, period51_amount, period52_amount,
period53_amount, period54_amount, period55_amount, period56_amount,
period57_amount, period58_amount, period59_amount, period60_amount
from PSB_WS_ACCOUNT_LINES a
where currency_code = p_currency_code
and l_current_stage_seq between start_stage_seq and current_stage_seq
and balance_type = p_balance_type
and template_id is null
and 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 = decode(nvl(l_local_copy_flag, 'N'), 'Y', p_worksheet_id, l_global_worksheet_id))
and stage_set_id = l_stage_set_id
and service_package_id = l_service_package_id
and budget_year_id = p_budget_year_id
and code_combination_id = l_ccid;
SELECT DECODE(global_worksheet_flag, 'Y', worksheet_id,
global_worksheet_id) global_worksheet_id,
local_copy_flag,
/* start bug 3871839 */
current_stage_seq
/* End bug 3871839 */
INTO
l_global_worksheet_id,
l_local_copy_flag,
/* start bug 3871839 */
l_current_stage_seq
/* end bug 3871839 */
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id;
SELECT gl_cutoff_period,
allocrule_set_id allocrule_set_id,
budget_calendar_id,
rounding_factor,
stage_set_id,
flex_mapping_set_id
/* Bug no :3871839 commented out stage sequence*/
/* current_stage_seq */
INTO
l_gl_cutoff_period,
l_allocrule_set_id,
l_budget_calendar_id,
l_rounding_factor,
l_stage_set_id,
l_flex_mapping_set_id
/* Bug No :3871839 Commented out l_current_stage_sequence */
/*l_current_stage_seq*/
FROM psb_worksheets
WHERE worksheet_id = l_global_worksheet_id;
sql_wal := 'select account_line_id, ' ||
'budget_group_id, ' ||
'ytd_amount, ' ||
/* Bug 3347507 start */
'start_stage_seq ' ||
/* Bug 3347507 end */
'from PSB_WS_ACCOUNT_LINES a ' ||
'where ' || l_current_stage_seq || ' between start_stage_seq and current_stage_seq ';
'(select 1 ' ||
'from PSB_WS_LINES b ' ||
'where b.account_line_id = a.account_line_id ' ||
'and b.worksheet_id = ' || p_worksheet_id || ') ';
IF NVL(p_update_cy_estimate, 'N') = 'Y' THEN
l_ytd_amount := nvl(p_ytd_amount, 0);
IF NVL(p_update_cy_estimate, 'N') = 'Y' THEN
l_rounded_ytd_amount := nvl(p_ytd_amount, 0);
IF NVL(p_update_cy_estimate, 'N') = 'Y' THEN
l_rounded_ytd_amount := l_rounded_ytd_amount;
p_update_cy_estimate => NVL(p_update_cy_estimate, 'N')
/* end bug 4128196 */
);
update PSB_WS_ACCOUNT_LINES a
set budget_group_id = p_budget_group_id,
current_stage_seq = l_current_stage_seq,
end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, nvl(p_annual_fte, FND_API.G_MISS_NUM)),
copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
period1_amount = l_period_amounts(1), period2_amount = l_period_amounts(2),
period3_amount = l_period_amounts(3), period4_amount = l_period_amounts(4),
period5_amount = l_period_amounts(5), period6_amount = l_period_amounts(6),
period7_amount = l_period_amounts(7), period8_amount = l_period_amounts(8),
period9_amount = l_period_amounts(9), period10_amount = l_period_amounts(10),
period11_amount = l_period_amounts(11), period12_amount = l_period_amounts(12),
period13_amount = l_period_amounts(13), period14_amount = l_period_amounts(14),
period15_amount = l_period_amounts(15), period16_amount = l_period_amounts(16),
period17_amount = l_period_amounts(17), period18_amount = l_period_amounts(18),
period19_amount = l_period_amounts(19), period20_amount = l_period_amounts(20),
period21_amount = l_period_amounts(21), period22_amount = l_period_amounts(22),
period23_amount = l_period_amounts(23), period24_amount = l_period_amounts(24),
period25_amount = l_period_amounts(25), period26_amount = l_period_amounts(26),
period27_amount = l_period_amounts(27), period28_amount = l_period_amounts(28),
period29_amount = l_period_amounts(29), period30_amount = l_period_amounts(30),
period31_amount = l_period_amounts(31), period32_amount = l_period_amounts(32),
period33_amount = l_period_amounts(33), period34_amount = l_period_amounts(34),
period35_amount = l_period_amounts(35), period36_amount = l_period_amounts(36),
period37_amount = l_period_amounts(37), period38_amount = l_period_amounts(38),
period39_amount = l_period_amounts(39), period40_amount = l_period_amounts(40),
period41_amount = l_period_amounts(41), period42_amount = l_period_amounts(42),
period43_amount = l_period_amounts(43), period44_amount = l_period_amounts(44),
period45_amount = l_period_amounts(45), period46_amount = l_period_amounts(46),
period47_amount = l_period_amounts(47), period48_amount = l_period_amounts(48),
period49_amount = l_period_amounts(49), period50_amount = l_period_amounts(50),
period51_amount = l_period_amounts(51), period52_amount = l_period_amounts(52),
period53_amount = l_period_amounts(53), period54_amount = l_period_amounts(54),
period55_amount = l_period_amounts(55), period56_amount = l_period_amounts(56),
period57_amount = l_period_amounts(57), period58_amount = l_period_amounts(58),
period59_amount = l_period_amounts(59), period60_amount = l_period_amounts(60),
ytd_amount = l_rounded_ytd_amount,
last_update_date = sysdate,
last_updated_by = l_userid,
last_update_login = l_loginid
where account_line_id = l_spal_id;
update PSB_WS_ACCOUNT_LINES
set budget_group_id = p_budget_group_id,
current_stage_seq = l_current_stage_seq,
end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte + nvl(l_current_fte, 0)),
copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
period1_amount = l_period_amounts(1) + l_current_prdamt(1),
period2_amount = l_period_amounts(2) + l_current_prdamt(2),
period3_amount = l_period_amounts(3) + l_current_prdamt(3),
period4_amount = l_period_amounts(4) + l_current_prdamt(4),
period5_amount = l_period_amounts(5) + l_current_prdamt(5),
period6_amount = l_period_amounts(6) + l_current_prdamt(6),
period7_amount = l_period_amounts(7) + l_current_prdamt(7),
period8_amount = l_period_amounts(8) + l_current_prdamt(8),
period9_amount = l_period_amounts(9) + l_current_prdamt(9),
period10_amount = l_period_amounts(10) + l_current_prdamt(10),
period11_amount = l_period_amounts(11) + l_current_prdamt(11),
period12_amount = l_period_amounts(12) + l_current_prdamt(12),
period13_amount = l_period_amounts(13) + l_current_prdamt(13),
period14_amount = l_period_amounts(14) + l_current_prdamt(14),
period15_amount = l_period_amounts(15) + l_current_prdamt(15),
period16_amount = l_period_amounts(16) + l_current_prdamt(16),
period17_amount = l_period_amounts(17) + l_current_prdamt(17),
period18_amount = l_period_amounts(18) + l_current_prdamt(18),
period19_amount = l_period_amounts(19) + l_current_prdamt(19),
period20_amount = l_period_amounts(20) + l_current_prdamt(20),
period21_amount = l_period_amounts(21) + l_current_prdamt(21),
period22_amount = l_period_amounts(22) + l_current_prdamt(22),
period23_amount = l_period_amounts(23) + l_current_prdamt(23),
period24_amount = l_period_amounts(24) + l_current_prdamt(24),
period25_amount = l_period_amounts(25) + l_current_prdamt(25),
period26_amount = l_period_amounts(26) + l_current_prdamt(26),
period27_amount = l_period_amounts(27) + l_current_prdamt(27),
period28_amount = l_period_amounts(28) + l_current_prdamt(28),
period29_amount = l_period_amounts(29) + l_current_prdamt(29),
period30_amount = l_period_amounts(30) + l_current_prdamt(30),
period31_amount = l_period_amounts(31) + l_current_prdamt(31),
period32_amount = l_period_amounts(32) + l_current_prdamt(32),
period33_amount = l_period_amounts(33) + l_current_prdamt(33),
period34_amount = l_period_amounts(34) + l_current_prdamt(34),
period35_amount = l_period_amounts(35) + l_current_prdamt(35),
period36_amount = l_period_amounts(36) + l_current_prdamt(36),
period37_amount = l_period_amounts(37) + l_current_prdamt(37),
period38_amount = l_period_amounts(38) + l_current_prdamt(38),
period39_amount = l_period_amounts(39) + l_current_prdamt(39),
period40_amount = l_period_amounts(40) + l_current_prdamt(40),
period41_amount = l_period_amounts(41) + l_current_prdamt(41),
period42_amount = l_period_amounts(42) + l_current_prdamt(42),
period43_amount = l_period_amounts(43) + l_current_prdamt(43),
period44_amount = l_period_amounts(44) + l_current_prdamt(44),
period45_amount = l_period_amounts(45) + l_current_prdamt(45),
period46_amount = l_period_amounts(46) + l_current_prdamt(46),
period47_amount = l_period_amounts(47) + l_current_prdamt(47),
period48_amount = l_period_amounts(48) + l_current_prdamt(48),
period49_amount = l_period_amounts(49) + l_current_prdamt(49),
period50_amount = l_period_amounts(50) + l_current_prdamt(50),
period51_amount = l_period_amounts(51) + l_current_prdamt(51),
period52_amount = l_period_amounts(52) + l_current_prdamt(52),
period53_amount = l_period_amounts(53) + l_current_prdamt(53),
period54_amount = l_period_amounts(54) + l_current_prdamt(54),
period55_amount = l_period_amounts(55) + l_current_prdamt(55),
period56_amount = l_period_amounts(56) + l_current_prdamt(56),
period57_amount = l_period_amounts(57) + l_current_prdamt(57),
period58_amount = l_period_amounts(58) + l_current_prdamt(58),
period59_amount = l_period_amounts(59) + l_current_prdamt(59),
period60_amount = l_period_amounts(60) + l_current_prdamt(60),
ytd_amount = l_rounded_ytd_amount + l_current_ytdamt,
last_update_date = sysdate,
last_updated_by = l_userid,
last_update_login = l_loginid,
request_id = l_requestid
where account_line_id = l_account_line_id;
update PSB_WS_ACCOUNT_LINES
set budget_group_id = p_budget_group_id,
current_stage_seq = l_current_stage_seq,
end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte),
copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
period1_amount = l_period_amounts(1), period2_amount = l_period_amounts(2),
period3_amount = l_period_amounts(3), period4_amount = l_period_amounts(4),
period5_amount = l_period_amounts(5), period6_amount = l_period_amounts(6),
period7_amount = l_period_amounts(7), period8_amount = l_period_amounts(8),
period9_amount = l_period_amounts(9), period10_amount = l_period_amounts(10),
period11_amount = l_period_amounts(11), period12_amount = l_period_amounts(12),
period13_amount = l_period_amounts(13), period14_amount = l_period_amounts(14),
period15_amount = l_period_amounts(15), period16_amount = l_period_amounts(16),
period17_amount = l_period_amounts(17), period18_amount = l_period_amounts(18),
period19_amount = l_period_amounts(19), period20_amount = l_period_amounts(20),
period21_amount = l_period_amounts(21), period22_amount = l_period_amounts(22),
period23_amount = l_period_amounts(23), period24_amount = l_period_amounts(24),
period25_amount = l_period_amounts(25), period26_amount = l_period_amounts(26),
period27_amount = l_period_amounts(27), period28_amount = l_period_amounts(28),
period29_amount = l_period_amounts(29), period30_amount = l_period_amounts(30),
period31_amount = l_period_amounts(31), period32_amount = l_period_amounts(32),
period33_amount = l_period_amounts(33), period34_amount = l_period_amounts(34),
period35_amount = l_period_amounts(35), period36_amount = l_period_amounts(36),
period37_amount = l_period_amounts(37), period38_amount = l_period_amounts(38),
period39_amount = l_period_amounts(39), period40_amount = l_period_amounts(40),
period41_amount = l_period_amounts(41), period42_amount = l_period_amounts(42),
period43_amount = l_period_amounts(43), period44_amount = l_period_amounts(44),
period45_amount = l_period_amounts(45), period46_amount = l_period_amounts(46),
period47_amount = l_period_amounts(47), period48_amount = l_period_amounts(48),
period49_amount = l_period_amounts(49), period50_amount = l_period_amounts(50),
period51_amount = l_period_amounts(51), period52_amount = l_period_amounts(52),
period53_amount = l_period_amounts(53), period54_amount = l_period_amounts(54),
period55_amount = l_period_amounts(55), period56_amount = l_period_amounts(56),
period57_amount = l_period_amounts(57), period58_amount = l_period_amounts(58),
period59_amount = l_period_amounts(59), period60_amount = l_period_amounts(60),
ytd_amount = l_rounded_ytd_amount,
last_update_date = sysdate,
last_updated_by = l_userid,
last_update_login = l_loginid,
request_id = l_requestid
where account_line_id = l_account_line_id;
update PSB_WS_ACCOUNT_LINES
set budget_group_id = p_budget_group_id,
current_stage_seq = l_current_stage_seq,
end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte + nvl(l_current_fte, 0)),
copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
period1_amount = l_period_amounts(1) + l_current_prdamt(1),
period2_amount = l_period_amounts(2) + l_current_prdamt(2),
period3_amount = l_period_amounts(3) + l_current_prdamt(3),
period4_amount = l_period_amounts(4) + l_current_prdamt(4),
period5_amount = l_period_amounts(5) + l_current_prdamt(5),
period6_amount = l_period_amounts(6) + l_current_prdamt(6),
period7_amount = l_period_amounts(7) + l_current_prdamt(7),
period8_amount = l_period_amounts(8) + l_current_prdamt(8),
period9_amount = l_period_amounts(9) + l_current_prdamt(9),
period10_amount = l_period_amounts(10) + l_current_prdamt(10),
period11_amount = l_period_amounts(11) + l_current_prdamt(11),
period12_amount = l_period_amounts(12) + l_current_prdamt(12),
period13_amount = l_period_amounts(13) + l_current_prdamt(13),
period14_amount = l_period_amounts(14) + l_current_prdamt(14),
period15_amount = l_period_amounts(15) + l_current_prdamt(15),
period16_amount = l_period_amounts(16) + l_current_prdamt(16),
period17_amount = l_period_amounts(17) + l_current_prdamt(17),
period18_amount = l_period_amounts(18) + l_current_prdamt(18),
period19_amount = l_period_amounts(19) + l_current_prdamt(19),
period20_amount = l_period_amounts(20) + l_current_prdamt(20),
period21_amount = l_period_amounts(21) + l_current_prdamt(21),
period22_amount = l_period_amounts(22) + l_current_prdamt(22),
period23_amount = l_period_amounts(23) + l_current_prdamt(23),
period24_amount = l_period_amounts(24) + l_current_prdamt(24),
period25_amount = l_period_amounts(25) + l_current_prdamt(25),
period26_amount = l_period_amounts(26) + l_current_prdamt(26),
period27_amount = l_period_amounts(27) + l_current_prdamt(27),
period28_amount = l_period_amounts(28) + l_current_prdamt(28),
period29_amount = l_period_amounts(29) + l_current_prdamt(29),
period30_amount = l_period_amounts(30) + l_current_prdamt(30),
period31_amount = l_period_amounts(31) + l_current_prdamt(31),
period32_amount = l_period_amounts(32) + l_current_prdamt(32),
period33_amount = l_period_amounts(33) + l_current_prdamt(33),
period34_amount = l_period_amounts(34) + l_current_prdamt(34),
period35_amount = l_period_amounts(35) + l_current_prdamt(35),
period36_amount = l_period_amounts(36) + l_current_prdamt(36),
period37_amount = l_period_amounts(37) + l_current_prdamt(37),
period38_amount = l_period_amounts(38) + l_current_prdamt(38),
period39_amount = l_period_amounts(39) + l_current_prdamt(39),
period40_amount = l_period_amounts(40) + l_current_prdamt(40),
period41_amount = l_period_amounts(41) + l_current_prdamt(41),
period42_amount = l_period_amounts(42) + l_current_prdamt(42),
period43_amount = l_period_amounts(43) + l_current_prdamt(43),
period44_amount = l_period_amounts(44) + l_current_prdamt(44),
period45_amount = l_period_amounts(45) + l_current_prdamt(45),
period46_amount = l_period_amounts(46) + l_current_prdamt(46),
period47_amount = l_period_amounts(47) + l_current_prdamt(47),
period48_amount = l_period_amounts(48) + l_current_prdamt(48),
period49_amount = l_period_amounts(49) + l_current_prdamt(49),
period50_amount = l_period_amounts(50) + l_current_prdamt(50),
period51_amount = l_period_amounts(51) + l_current_prdamt(51),
period52_amount = l_period_amounts(52) + l_current_prdamt(52),
period53_amount = l_period_amounts(53) + l_current_prdamt(53),
period54_amount = l_period_amounts(54) + l_current_prdamt(54),
period55_amount = l_period_amounts(55) + l_current_prdamt(55),
period56_amount = l_period_amounts(56) + l_current_prdamt(56),
period57_amount = l_period_amounts(57) + l_current_prdamt(57),
period58_amount = l_period_amounts(58) + l_current_prdamt(58),
period59_amount = l_period_amounts(59) + l_current_prdamt(59),
period60_amount = l_period_amounts(60) + l_current_prdamt(60),
ytd_amount = l_rounded_ytd_amount + l_current_ytdamt,
last_update_date = sysdate,
last_updated_by = l_userid,
last_update_login = l_loginid,
request_id = l_requestid
where account_line_id = l_account_line_id;
update PSB_WS_ACCOUNT_LINES
set budget_group_id = p_budget_group_id,
current_stage_seq = l_current_stage_seq,
end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte),
copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
period1_amount = l_period_amounts(1), period2_amount = l_period_amounts(2),
period3_amount = l_period_amounts(3), period4_amount = l_period_amounts(4),
period5_amount = l_period_amounts(5), period6_amount = l_period_amounts(6),
period7_amount = l_period_amounts(7), period8_amount = l_period_amounts(8),
period9_amount = l_period_amounts(9), period10_amount = l_period_amounts(10),
period11_amount = l_period_amounts(11), period12_amount = l_period_amounts(12),
period13_amount = l_period_amounts(13), period14_amount = l_period_amounts(14),
period15_amount = l_period_amounts(15), period16_amount = l_period_amounts(16),
period17_amount = l_period_amounts(17), period18_amount = l_period_amounts(18),
period19_amount = l_period_amounts(19), period20_amount = l_period_amounts(20),
period21_amount = l_period_amounts(21), period22_amount = l_period_amounts(22),
period23_amount = l_period_amounts(23), period24_amount = l_period_amounts(24),
period25_amount = l_period_amounts(25), period26_amount = l_period_amounts(26),
period27_amount = l_period_amounts(27), period28_amount = l_period_amounts(28),
period29_amount = l_period_amounts(29), period30_amount = l_period_amounts(30),
period31_amount = l_period_amounts(31), period32_amount = l_period_amounts(32),
period33_amount = l_period_amounts(33), period34_amount = l_period_amounts(34),
period35_amount = l_period_amounts(35), period36_amount = l_period_amounts(36),
period37_amount = l_period_amounts(37), period38_amount = l_period_amounts(38),
period39_amount = l_period_amounts(39), period40_amount = l_period_amounts(40),
period41_amount = l_period_amounts(41), period42_amount = l_period_amounts(42),
period43_amount = l_period_amounts(43), period44_amount = l_period_amounts(44),
period45_amount = l_period_amounts(45), period46_amount = l_period_amounts(46),
period47_amount = l_period_amounts(47), period48_amount = l_period_amounts(48),
period49_amount = l_period_amounts(49), period50_amount = l_period_amounts(50),
period51_amount = l_period_amounts(51), period52_amount = l_period_amounts(52),
period53_amount = l_period_amounts(53), period54_amount = l_period_amounts(54),
period55_amount = l_period_amounts(55), period56_amount = l_period_amounts(56),
period57_amount = l_period_amounts(57), period58_amount = l_period_amounts(58),
period59_amount = l_period_amounts(59), period60_amount = l_period_amounts(60),
ytd_amount = l_rounded_ytd_amount,
last_update_date = sysdate,
last_updated_by = l_userid,
last_update_login = l_loginid,
request_id = l_requestid
where account_line_id = l_account_line_id;
update PSB_WS_ACCOUNT_LINES
set budget_group_id = p_budget_group_id,
current_stage_seq = l_current_stage_seq,
end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte + nvl(l_current_fte, 0)),
copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
period1_amount = l_period_amounts(1) + l_current_prdamt(1),
period2_amount = l_period_amounts(2) + l_current_prdamt(2),
period3_amount = l_period_amounts(3) + l_current_prdamt(3),
period4_amount = l_period_amounts(4) + l_current_prdamt(4),
period5_amount = l_period_amounts(5) + l_current_prdamt(5),
period6_amount = l_period_amounts(6) + l_current_prdamt(6),
period7_amount = l_period_amounts(7) + l_current_prdamt(7),
period8_amount = l_period_amounts(8) + l_current_prdamt(8),
period9_amount = l_period_amounts(9) + l_current_prdamt(9),
period10_amount = l_period_amounts(10) + l_current_prdamt(10),
period11_amount = l_period_amounts(11) + l_current_prdamt(11),
period12_amount = l_period_amounts(12) + l_current_prdamt(12),
period13_amount = l_period_amounts(13) + l_current_prdamt(13),
period14_amount = l_period_amounts(14) + l_current_prdamt(14),
period15_amount = l_period_amounts(15) + l_current_prdamt(15),
period16_amount = l_period_amounts(16) + l_current_prdamt(16),
period17_amount = l_period_amounts(17) + l_current_prdamt(17),
period18_amount = l_period_amounts(18) + l_current_prdamt(18),
period19_amount = l_period_amounts(19) + l_current_prdamt(19),
period20_amount = l_period_amounts(20) + l_current_prdamt(20),
period21_amount = l_period_amounts(21) + l_current_prdamt(21),
period22_amount = l_period_amounts(22) + l_current_prdamt(22),
period23_amount = l_period_amounts(23) + l_current_prdamt(23),
period24_amount = l_period_amounts(24) + l_current_prdamt(24),
period25_amount = l_period_amounts(25) + l_current_prdamt(25),
period26_amount = l_period_amounts(26) + l_current_prdamt(26),
period27_amount = l_period_amounts(27) + l_current_prdamt(27),
period28_amount = l_period_amounts(28) + l_current_prdamt(28),
period29_amount = l_period_amounts(29) + l_current_prdamt(29),
period30_amount = l_period_amounts(30) + l_current_prdamt(30),
period31_amount = l_period_amounts(31) + l_current_prdamt(31),
period32_amount = l_period_amounts(32) + l_current_prdamt(32),
period33_amount = l_period_amounts(33) + l_current_prdamt(33),
period34_amount = l_period_amounts(34) + l_current_prdamt(34),
period35_amount = l_period_amounts(35) + l_current_prdamt(35),
period36_amount = l_period_amounts(36) + l_current_prdamt(36),
period37_amount = l_period_amounts(37) + l_current_prdamt(37),
period38_amount = l_period_amounts(38) + l_current_prdamt(38),
period39_amount = l_period_amounts(39) + l_current_prdamt(39),
period40_amount = l_period_amounts(40) + l_current_prdamt(40),
period41_amount = l_period_amounts(41) + l_current_prdamt(41),
period42_amount = l_period_amounts(42) + l_current_prdamt(42),
period43_amount = l_period_amounts(43) + l_current_prdamt(43),
period44_amount = l_period_amounts(44) + l_current_prdamt(44),
period45_amount = l_period_amounts(45) + l_current_prdamt(45),
period46_amount = l_period_amounts(46) + l_current_prdamt(46),
period47_amount = l_period_amounts(47) + l_current_prdamt(47),
period48_amount = l_period_amounts(48) + l_current_prdamt(48),
period49_amount = l_period_amounts(49) + l_current_prdamt(49),
period50_amount = l_period_amounts(50) + l_current_prdamt(50),
period51_amount = l_period_amounts(51) + l_current_prdamt(51),
period52_amount = l_period_amounts(52) + l_current_prdamt(52),
period53_amount = l_period_amounts(53) + l_current_prdamt(53),
period54_amount = l_period_amounts(54) + l_current_prdamt(54),
period55_amount = l_period_amounts(55) + l_current_prdamt(55),
period56_amount = l_period_amounts(56) + l_current_prdamt(56),
period57_amount = l_period_amounts(57) + l_current_prdamt(57),
period58_amount = l_period_amounts(58) + l_current_prdamt(58),
period59_amount = l_period_amounts(59) + l_current_prdamt(59),
period60_amount = l_period_amounts(60) + l_current_prdamt(60),
ytd_amount = l_rounded_ytd_amount + l_current_ytdamt,
last_update_date = sysdate,
last_updated_by = l_userid,
last_update_login = l_loginid,
request_id = l_requestid
where account_line_id = l_account_line_id;
update PSB_WS_ACCOUNT_LINES
set budget_group_id = p_budget_group_id,
current_stage_seq = l_current_stage_seq,
end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte),
copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
period1_amount = l_period_amounts(1), period2_amount = l_period_amounts(2),
period3_amount = l_period_amounts(3), period4_amount = l_period_amounts(4),
period5_amount = l_period_amounts(5), period6_amount = l_period_amounts(6),
period7_amount = l_period_amounts(7), period8_amount = l_period_amounts(8),
period9_amount = l_period_amounts(9), period10_amount = l_period_amounts(10),
period11_amount = l_period_amounts(11), period12_amount = l_period_amounts(12),
period13_amount = l_period_amounts(13), period14_amount = l_period_amounts(14),
period15_amount = l_period_amounts(15), period16_amount = l_period_amounts(16),
period17_amount = l_period_amounts(17), period18_amount = l_period_amounts(18),
period19_amount = l_period_amounts(19), period20_amount = l_period_amounts(20),
period21_amount = l_period_amounts(21), period22_amount = l_period_amounts(22),
period23_amount = l_period_amounts(23), period24_amount = l_period_amounts(24),
period25_amount = l_period_amounts(25), period26_amount = l_period_amounts(26),
period27_amount = l_period_amounts(27), period28_amount = l_period_amounts(28),
period29_amount = l_period_amounts(29), period30_amount = l_period_amounts(30),
period31_amount = l_period_amounts(31), period32_amount = l_period_amounts(32),
period33_amount = l_period_amounts(33), period34_amount = l_period_amounts(34),
period35_amount = l_period_amounts(35), period36_amount = l_period_amounts(36),
period37_amount = l_period_amounts(37), period38_amount = l_period_amounts(38),
period39_amount = l_period_amounts(39), period40_amount = l_period_amounts(40),
period41_amount = l_period_amounts(41), period42_amount = l_period_amounts(42),
period43_amount = l_period_amounts(43), period44_amount = l_period_amounts(44),
period45_amount = l_period_amounts(45), period46_amount = l_period_amounts(46),
period47_amount = l_period_amounts(47), period48_amount = l_period_amounts(48),
period49_amount = l_period_amounts(49), period50_amount = l_period_amounts(50),
period51_amount = l_period_amounts(51), period52_amount = l_period_amounts(52),
period53_amount = l_period_amounts(53), period54_amount = l_period_amounts(54),
period55_amount = l_period_amounts(55), period56_amount = l_period_amounts(56),
period57_amount = l_period_amounts(57), period58_amount = l_period_amounts(58),
period59_amount = l_period_amounts(59), period60_amount = l_period_amounts(60),
ytd_amount = l_rounded_ytd_amount,
last_update_date = sysdate,
last_updated_by = l_userid,
last_update_login = l_loginid,
request_id = l_requestid
where account_line_id = l_account_line_id;
GL_CODE_COMBINATIONS_PKG.Select_Columns
(X_code_combination_id => l_ccid,
X_account_type => l_account_type,
X_template_id => l_template_id);
insert into PSB_WS_ACCOUNT_LINES
(account_line_id, code_combination_id, position_line_id, service_package_id, budget_group_id,
element_set_id, salary_account_line, stage_set_id, start_stage_seq, current_stage_seq,
end_stage_seq, copy_of_account_line_id, last_update_date, last_updated_by,
last_update_login, created_by, creation_date, template_id, budget_year_id, annual_fte,
currency_code, account_type, balance_type,
period1_amount, period2_amount, period3_amount, period4_amount, period5_amount, period6_amount,
period7_amount, period8_amount, period9_amount, period10_amount, period11_amount, period12_amount,
period13_amount, period14_amount, period15_amount, period16_amount, period17_amount, period18_amount,
period19_amount, period20_amount, period21_amount, period22_amount, period23_amount, period24_amount,
period25_amount, period26_amount, period27_amount, period28_amount, period29_amount, period30_amount,
period31_amount, period32_amount, period33_amount, period34_amount, period35_amount, period36_amount,
period37_amount, period38_amount, period39_amount, period40_amount, period41_amount, period42_amount,
period43_amount, period44_amount, period45_amount, period46_amount, period47_amount, period48_amount,
period49_amount, period50_amount, period51_amount, period52_amount, period53_amount, period54_amount,
period55_amount, period56_amount, period57_amount, period58_amount, period59_amount, period60_amount,
ytd_amount, request_id, functional_transaction)
values (psb_ws_account_lines_s.nextval,
l_ccid,
decode(p_position_line_id, FND_API.G_MISS_NUM, null, p_position_line_id),
l_service_package_id,
p_budget_group_id,
decode(p_element_set_id, FND_API.G_MISS_NUM, null, p_element_set_id),
decode(p_salary_account_line, FND_API.G_FALSE, null, 'Y'),
l_stage_set_id,
decode(p_start_stage_seq, FND_API.G_MISS_NUM, l_start_stage_seq, p_start_stage_seq),
l_current_stage_seq,
decode(p_end_stage_seq, FND_API.G_MISS_NUM, null, p_end_stage_seq),
decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, null, p_copy_of_account_line_id),
sysdate, l_userid, l_loginid, l_userid, sysdate,
decode(p_template_id, FND_API.G_MISS_NUM, null, p_template_id),
p_budget_year_id,
decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, null, nvl(p_annual_fte, FND_API.G_MISS_NUM)),
p_currency_code, l_account_type, p_balance_type,
l_period_amounts(1), l_period_amounts(2), l_period_amounts(3), l_period_amounts(4), l_period_amounts(5),
l_period_amounts(6), l_period_amounts(7), l_period_amounts(8), l_period_amounts(9), l_period_amounts(10),
l_period_amounts(11), l_period_amounts(12), l_period_amounts(13), l_period_amounts(14), l_period_amounts(15),
l_period_amounts(16), l_period_amounts(17), l_period_amounts(18), l_period_amounts(19), l_period_amounts(20),
l_period_amounts(21), l_period_amounts(22), l_period_amounts(23), l_period_amounts(24), l_period_amounts(25),
l_period_amounts(26), l_period_amounts(27), l_period_amounts(28), l_period_amounts(29), l_period_amounts(30),
l_period_amounts(31), l_period_amounts(32), l_period_amounts(33), l_period_amounts(34), l_period_amounts(35),
l_period_amounts(36), l_period_amounts(37), l_period_amounts(38), l_period_amounts(39), l_period_amounts(40),
l_period_amounts(41), l_period_amounts(42), l_period_amounts(43), l_period_amounts(44), l_period_amounts(45),
l_period_amounts(46), l_period_amounts(47), l_period_amounts(48), l_period_amounts(49), l_period_amounts(50),
l_period_amounts(51), l_period_amounts(52), l_period_amounts(53), l_period_amounts(54), l_period_amounts(55),
l_period_amounts(56), l_period_amounts(57), l_period_amounts(58), l_period_amounts(59), l_period_amounts(60),
l_rounded_ytd_amount, l_requestid, p_functional_transaction)
returning account_line_id into l_acclineid;
insert into PSB_WS_LINES
(worksheet_id, account_line_id, freeze_flag,
view_line_flag, last_update_date, last_updated_by,
last_update_login, created_by, creation_date)
select worksheet_id, l_acclineid, freeze_flag,
view_line_flag, sysdate, l_userid,
l_loginid, l_userid, sysdate
from PSB_WS_LINES_POSITIONS
where position_line_id = p_position_line_id;
insert into PSB_WS_LINES
(worksheet_id, account_line_id, freeze_flag,
view_line_flag, last_update_date, last_updated_by,
last_update_login, created_by, creation_date)
values (p_worksheet_id, l_acclineid, null,
'Y', sysdate, l_userid,
l_loginid, l_userid, sysdate);
insert into PSB_WS_LINES
(worksheet_id, account_line_id, freeze_flag,
view_line_flag, last_update_date, last_updated_by,
last_update_login, created_by, creation_date)
values (c_Distribute_WS_Rec.worksheet_id, l_acclineid, null,
'Y', sysdate, l_userid,
l_loginid, l_userid, sysdate);
delete from psb_ws_lines
where account_line_id = l_account_line_id;
insert into PSB_WS_LINES
(worksheet_id, account_line_id, freeze_flag,
view_line_flag, last_update_date, last_updated_by,
last_update_login, created_by, creation_date)
select worksheet_id, l_account_line_id, freeze_flag,
view_line_flag, sysdate, l_userid,
l_loginid, l_userid, sysdate
from PSB_WS_LINES_POSITIONS
where position_line_id = p_position_line_id;
insert into PSB_WS_LINES
(worksheet_id, account_line_id, freeze_flag,
view_line_flag, last_update_date, last_updated_by,
last_update_login, created_by, creation_date)
values (p_worksheet_id, l_account_line_id, null,
'Y', sysdate, l_userid,
l_loginid, l_userid, sysdate);
insert into PSB_WS_LINES
(worksheet_id, account_line_id, freeze_flag,
view_line_flag, last_update_date, last_updated_by,
last_update_login, created_by, creation_date)
values (c_Distribute_WS_Rec.worksheet_id, l_account_line_id, null,
'Y', sysdate, l_userid,
l_loginid, l_userid, sysdate);
p_update_cy_estimate IN VARCHAR2 := 'N'
/* end bug 4128196 */
)
IS
--
l_api_name CONSTANT VARCHAR2(30) := 'Create_Account_Dist';
select a.stage_set_id,
a.rounding_factor,
nvl(a.allocrule_set_id, a.global_allocrule_set_id) allocrule_set_id,
nvl(a.global_worksheet_id, a.worksheet_id) global_worksheet_id,
a.budget_calendar_id,
a.flex_mapping_set_id,
a.gl_cutoff_period,
nvl(b.root_budget_group_id, b.budget_group_id) root_budget_group_id,
local_copy_flag
from PSB_WORKSHEETS_V a, PSB_BUDGET_GROUPS b
where a.worksheet_id = p_worksheet_id
and b.budget_group_id = a.budget_group_id;
select start_date_active,
end_date_active
from GL_CODE_COMBINATIONS
where code_combination_id = l_ccid;
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;
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 <= l_end_date)
and (b.effective_end_date is null))
or ((b.effective_start_date between l_start_date and l_end_date)
or (b.effective_end_date between l_start_date and l_end_date)
or ((b.effective_start_date < l_start_date)
and (b.effective_end_date > l_end_date))))
and b.budget_group_type = 'R'
and ((b.budget_group_id = l_root_budget_group_id) or
(b.root_budget_group_id = l_root_budget_group_id))
and a.account_position_set_id = c.account_position_set_id
and c.code_combination_id = l_ccid;
select PSB_WS_ACCOUNT_LINES_S.NEXTVAL seq
from dual;
select period1_amount, period2_amount, period3_amount, period4_amount,
period5_amount, period6_amount, period7_amount, period8_amount,
period9_amount, period10_amount, period11_amount, period12_amount,
period13_amount, period14_amount, period15_amount, period16_amount,
period17_amount, period18_amount, period19_amount, period20_amount,
period21_amount, period22_amount, period23_amount, period24_amount,
period25_amount, period26_amount, period27_amount, period28_amount,
period29_amount, period30_amount, period31_amount, period32_amount,
period33_amount, period34_amount, period35_amount, period36_amount,
period37_amount, period38_amount, period39_amount, period40_amount,
period41_amount, period42_amount, period43_amount, period44_amount,
period45_amount, period46_amount, period47_amount, period48_amount,
period49_amount, period50_amount, period51_amount, period52_amount,
period53_amount, period54_amount, period55_amount, period56_amount,
period57_amount, period58_amount, period59_amount, period60_amount,
budget_year_id
from PSB_WS_ACCOUNT_LINES
where account_line_id = p_account_line_id
and position_line_id is null;
sql_wsacc := 'select code_combination_id, budget_group_id, template_id, position_line_id, ' ||
'element_set_id, budget_year_id, currency_code, stage_set_id, start_stage_seq, ' ||
'current_stage_seq, service_package_id, balance_type, ' ||
'period1_amount, period2_amount, period3_amount, period4_amount, ' ||
'period5_amount, period6_amount, period7_amount, period8_amount, ' ||
'period9_amount, period10_amount, period11_amount, period12_amount, ' ||
'period13_amount, period14_amount, period15_amount, period16_amount, ' ||
'period17_amount, period18_amount, period19_amount, period20_amount, ' ||
'period21_amount, period22_amount, period23_amount, period24_amount, ' ||
'period25_amount, period26_amount, period27_amount, period28_amount, ' ||
'period29_amount, period30_amount, period31_amount, period32_amount, ' ||
'period33_amount, period34_amount, period35_amount, period36_amount, ' ||
'period37_amount, period38_amount, period39_amount, period40_amount, ' ||
'period41_amount, period42_amount, period43_amount, period44_amount, ' ||
'period45_amount, period46_amount, period47_amount, period48_amount, ' ||
'period49_amount, period50_amount, period51_amount, period52_amount, ' ||
'period53_amount, period54_amount, period55_amount, period56_amount, ' ||
'period57_amount, period58_amount, period59_amount, period60_amount, ' ||
'ytd_amount ' ||
'from PSB_WS_ACCOUNT_LINES ' ||
'where account_line_id = :AccLineID';
sql_wal := 'select account_line_id, ' ||
'ytd_amount ' ||
'from PSB_WS_ACCOUNT_LINES a ' ||
'where currency_code = ''' || l_currency_code || ''' ' ||
'and ' || l_current_stage_seq || ' between start_stage_seq and current_stage_seq ' ||
'and balance_type = ''' || l_balance_type || ''' ';
'(select 1 ' ||
'from PSB_WS_LINES b ' ||
'where b.account_line_id = a.account_line_id ' ||
'and b.worksheet_id = ' || p_worksheet_id || ') ';
insert into PSB_WS_ACCOUNT_LINES
(account_line_id, code_combination_id, position_line_id, service_package_id, budget_group_id,
element_set_id, salary_account_line, stage_set_id, start_stage_seq, current_stage_seq,
end_stage_seq, copy_of_account_line_id, last_update_date, last_updated_by,
last_update_login, created_by, creation_date, template_id, budget_year_id, annual_fte,
currency_code, account_type, balance_type,
period1_amount, period2_amount, period3_amount, period4_amount, period5_amount, period6_amount,
period7_amount, period8_amount, period9_amount, period10_amount, period11_amount, period12_amount,
period13_amount, period14_amount, period15_amount, period16_amount, period17_amount, period18_amount,
period19_amount, period20_amount, period21_amount, period22_amount, period23_amount, period24_amount,
period25_amount, period26_amount, period27_amount, period28_amount, period29_amount, period30_amount,
period31_amount, period32_amount, period33_amount, period34_amount, period35_amount, period36_amount,
period37_amount, period38_amount, period39_amount, period40_amount, period41_amount, period42_amount,
period43_amount, period44_amount, period45_amount, period46_amount, period47_amount, period48_amount,
period49_amount, period50_amount, period51_amount, period52_amount, period53_amount, period54_amount,
period55_amount, period56_amount, period57_amount, period58_amount, period59_amount, period60_amount,
ytd_amount, functional_transaction)
select l_acclineid,
code_combination_id, position_line_id, service_package_id, budget_group_id,
element_set_id, salary_account_line, stage_set_id, start_stage_seq,
l_previous_stage,
l_previous_stage,
copy_of_account_line_id, sysdate,
l_userid, l_loginid, l_userid,
sysdate, template_id, budget_year_id, annual_fte, currency_code, account_type, balance_type,
period1_amount, period2_amount, period3_amount, period4_amount, period5_amount, period6_amount,
period7_amount, period8_amount, period9_amount, period10_amount, period11_amount, period12_amount,
period13_amount, period14_amount, period15_amount, period16_amount, period17_amount, period18_amount,
period19_amount, period20_amount, period21_amount, period22_amount, period23_amount, period24_amount,
period25_amount, period26_amount, period27_amount, period28_amount, period29_amount, period30_amount,
period31_amount, period32_amount, period33_amount, period34_amount, period35_amount, period36_amount,
period37_amount, period38_amount, period39_amount, period40_amount, period41_amount, period42_amount,
period43_amount, period44_amount, period45_amount, period46_amount, period47_amount, period48_amount,
period49_amount, period50_amount, period51_amount, period52_amount, period53_amount, period54_amount,
period55_amount, period56_amount, period57_amount, period58_amount, period59_amount, period60_amount,
ytd_amount, functional_transaction
from PSB_WS_ACCOUNT_LINES
where account_line_id = p_account_line_id;
insert into PSB_WS_LINES
(worksheet_id, account_line_id, freeze_flag,
view_line_flag, last_update_date, last_updated_by,
last_update_login, created_by, creation_date)
select worksheet_id, l_acclineid, freeze_flag,
view_line_flag, sysdate, l_userid,
l_loginid, l_userid, sysdate
from PSB_WS_LINES
where account_line_id = p_account_line_id;
PSB_WORKSHEET.Delete_WAL
(p_api_version => 1.0,
p_return_status => l_return_status,
p_account_line_id => l_spal_id);
'update PSB_WS_ACCOUNT_LINES ' ||
'set service_package_id = decode(:p_service_package_id1, :gmn1, service_package_id, :p_service_package_id2), ' ||
'budget_group_id = :b_budget_group_id, ' ||
'copy_of_account_line_id = decode(:p_copy_of_account_line_id1, :gmn2 , copy_of_account_line_id, :p_copy_of_account_line_id2), annual_fte = decode(' ||
'nvl(:p_annual_fte1, :gmn3), :gmn4, annual_fte, nvl(:p_annual_fte2, :gmn5) ), ' ;
IF NVL(p_update_cy_estimate, 'N') = 'Y' THEN
l_rounding_difference := 0;
IF NVL(p_update_cy_estimate, 'N') = 'Y' THEN
l_running_total := NVL(l_running_total, 0) + NVL(l_period_amount, 0);
IF NVL(p_update_cy_estimate, 'N') = 'Y' THEN
l_prdamt_tbl(l_index) := l_period_amount +
(l_new_ytd_amount - l_running_total) +
NVL(l_rounding_difference, 0);
IF NVL(p_update_cy_estimate, 'N') = 'Y' THEN
l_running_total := NVL(l_running_total, 0) + NVL(l_period_amount, 0);
IF NVL(p_update_cy_estimate, 'N') = 'Y' THEN
l_prdamt_tbl(l_index) := l_period_amount +
(l_new_ytd_amount - l_running_total) +
NVL(l_rounding_difference, 0);
'last_update_date = :b_last_update_date, ' ||
'last_updated_by = :b_last_updated_by, ' ||
'last_update_login = :b_last_update_login, ';
DELETE FROM psb_ws_lines
WHERE account_line_id = p_account_line_id;
INSERT INTO PSB_WS_LINES
(worksheet_id, account_line_id, freeze_flag, view_line_flag,
last_update_date, last_updated_by, last_update_login, created_by,
creation_date)
SELECT worksheet_id, p_account_line_id, freeze_flag, view_line_flag,
sysdate, l_userid,l_loginid, l_userid, sysdate
FROM PSB_WS_LINES_POSITIONS
WHERE position_line_id = l_position_line_id;
UPDATE psb_ws_account_lines SET budget_group_changed = 'Y'
WHERE account_line_id = p_account_line_id;
INSERT INTO PSB_WS_LINES
(worksheet_id, account_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_account_line_id, null, 'Y', sysdate,
l_userid, l_loginid, l_userid, sysdate);
INSERT INTO PSB_WS_LINES
(worksheet_id, account_line_id, freeze_flag, view_line_flag,
last_update_date, last_updated_by, last_update_login,
created_by, creation_date)
VALUES (c_Distribute_WS_Rec.worksheet_id, p_account_line_id,
null,'Y', sysdate, l_userid, l_loginid, l_userid, sysdate);
select ytd_amount,
period1_amount, period2_amount, period3_amount, period4_amount,
period5_amount, period6_amount, period7_amount, period8_amount,
period9_amount, period10_amount, period11_amount, period12_amount,
period13_amount, period14_amount, period15_amount, period16_amount,
period17_amount, period18_amount, period19_amount, period20_amount,
period21_amount, period22_amount, period23_amount, period24_amount,
period25_amount, period26_amount, period27_amount, period28_amount,
period29_amount, period30_amount, period31_amount, period32_amount,
period33_amount, period34_amount, period35_amount, period36_amount,
period37_amount, period38_amount, period39_amount, period40_amount,
period41_amount, period42_amount, period43_amount, period44_amount,
period45_amount, period46_amount, period47_amount, period48_amount,
period49_amount, period50_amount, period51_amount, period52_amount,
period53_amount, period54_amount, period55_amount, period56_amount,
period57_amount, period58_amount, period59_amount, period60_amount
from PSB_WS_ACCOUNT_LINES a
where template_id is null
and position_line_id is null
and currency_code = p_currency_code
and p_start_stage_seq between start_stage_seq and current_stage_seq
and balance_type = 'A'
and exists
(select 1
from PSB_WS_LINES b
where b.account_line_id = a.account_line_id
and b.worksheet_id = p_worksheet_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 budget_group_id = p_budget_group_id
and code_combination_id = p_ccid;
select ytd_amount,
period1_amount, period2_amount, period3_amount, period4_amount,
period5_amount, period6_amount, period7_amount, period8_amount,
period9_amount, period10_amount, period11_amount, period12_amount,
period13_amount, period14_amount, period15_amount, period16_amount,
period17_amount, period18_amount, period19_amount, period20_amount,
period21_amount, period22_amount, period23_amount, period24_amount,
period25_amount, period26_amount, period27_amount, period28_amount,
period29_amount, period30_amount, period31_amount, period32_amount,
period33_amount, period34_amount, period35_amount, period36_amount,
period37_amount, period38_amount, period39_amount, period40_amount,
period41_amount, period42_amount, period43_amount, period44_amount,
period45_amount, period46_amount, period47_amount, period48_amount,
period49_amount, period50_amount, period51_amount, period52_amount,
period53_amount, period54_amount, period55_amount, period56_amount,
period57_amount, period58_amount, period59_amount, period60_amount
from PSB_WS_ACCOUNT_LINES a
where template_id is null
and position_line_id is null
and currency_code = p_currency_code
and p_start_stage_seq between start_stage_seq and current_stage_seq
and balance_type = 'E'
and exists
(select 1
from PSB_WS_LINES b
where b.account_line_id = a.account_line_id
and b.worksheet_id = p_worksheet_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 budget_group_id = p_budget_group_id
and code_combination_id = p_ccid;
PROCEDURE Update_YTD_Amount
( p_api_version IN NUMBER,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
p_return_status OUT NOCOPY VARCHAR2,
p_account_line_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_YTD_Amount';
update PSB_WS_ACCOUNT_LINES
set ytd_amount = nvl(period1_amount, 0) + nvl(period2_amount, 0) + nvl(period3_amount, 0) +
nvl(period4_amount, 0) + nvl(period5_amount, 0) + nvl(period6_amount, 0) +
nvl(period7_amount, 0) + nvl(period8_amount, 0) + nvl(period9_amount, 0) +
nvl(period10_amount, 0) + nvl(period11_amount, 0) + nvl(period12_amount, 0) +
nvl(period13_amount, 0) + nvl(period14_amount, 0) + nvl(period15_amount, 0) +
nvl(period16_amount, 0) + nvl(period17_amount, 0) + nvl(period18_amount, 0) +
nvl(period19_amount, 0) + nvl(period20_amount, 0) + nvl(period21_amount, 0) +
nvl(period22_amount, 0) + nvl(period23_amount, 0) + nvl(period24_amount, 0) +
nvl(period25_amount, 0) + nvl(period26_amount, 0) + nvl(period27_amount, 0) +
nvl(period28_amount, 0) + nvl(period29_amount, 0) + nvl(period30_amount, 0) +
nvl(period31_amount, 0) + nvl(period32_amount, 0) + nvl(period33_amount, 0) +
nvl(period34_amount, 0) + nvl(period35_amount, 0) + nvl(period36_amount, 0) +
nvl(period37_amount, 0) + nvl(period38_amount, 0) + nvl(period39_amount, 0) +
nvl(period40_amount, 0) + nvl(period41_amount, 0) + nvl(period42_amount, 0) +
nvl(period43_amount, 0) + nvl(period44_amount, 0) + nvl(period45_amount, 0) +
nvl(period46_amount, 0) + nvl(period47_amount, 0) + nvl(period48_amount, 0) +
nvl(period49_amount, 0) + nvl(period50_amount, 0) + nvl(period51_amount, 0) +
nvl(period52_amount, 0) + nvl(period53_amount, 0) + nvl(period54_amount, 0) +
nvl(period55_amount, 0) + nvl(period56_amount, 0) + nvl(period57_amount, 0) +
nvl(period58_amount, 0) + nvl(period59_amount, 0) + nvl(period60_amount, 0)
where account_line_id = p_account_line_id;
END Update_YTD_Amount;
select application_column_name
from fnd_id_flex_segments
where application_id = 101
and id_flex_code = 'GL#'
and id_flex_num = p_flex_code
and enabled_flag = 'Y'
order by segment_num;
sql_budget_balance := 'select gs.start_date, gs.end_date, ' ||
'decode(:ACCOUNT_TYPE, ''A'', 1, ''E'', 1, ''D'', 1, ''L'', -1, ''O'', -1, ''R'', -1, ''C'', -1 ) * ' ||
'(nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0)), ' ||
'(nvl(gb.BEGIN_BALANCE_DR, 0) - nvl(gb.BEGIN_BALANCE_CR,0)) '||
'from GL_BALANCES gb, ' ||
'GL_PERIOD_STATUSES gs ';
sql_actual_balance := 'select gs.start_date, gs.end_date, ' ||
'decode(:ACCOUNT_TYPE, ''A'', 1, ''E'', 1, ''D'', 1, ''L'', -1, ''O'', -1, ''R'', -1, ''C'', -1 ) * ' ||
'(nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0)) ' ||
'from GL_BALANCES gb, ' ||
'GL_PERIOD_STATUSES gs ' ||
'where gb.ledger_id = :B_SET_OF_BOOKS_ID ' ||
'and gb.currency_code = :CURRENCY_CODE ' ||
'and gb.code_combination_id = :CCID ' ||
'and ((gb.translated_flag is null) or (gb.translated_flag = ''Y'')) ' ||
'and gb.actual_flag = ''A'' ' ||
'and gb.period_name = gs.period_name ' ||
'and gb.period_type = gs.period_type ' ||
'and gb.period_year = gs.period_year ' ||
'and gb.period_num = gs.period_num ' ||
'and gs.set_of_books_id = :B_SET_OF_BOOKS_ID ' ||
'and gs.application_id = 101 ';
select purch_encumbrance_type_id, req_encumbrance_type_id
from financials_system_parameters; */
sql_encum_balance := 'select gs.start_date, gs.end_date, ' ||
'decode(:ACCOUNT_TYPE, ''A'', 1, ''E'', 1, ''D'', 1, ''L'', -1, ''O'', -1, ''R'', -1, ''C'', -1 ) * ' ||
'(nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0)), ' ||
'(nvl(gb.BEGIN_BALANCE_DR, 0) - nvl(gb.BEGIN_BALANCE_CR, 0)) '||
'from GL_BALANCES gb, ' ||
'GL_PERIOD_STATUSES gs ' ||
'where gb.ledger_id = :B_SET_OF_BOOKS_ID ' ||
'and gb.currency_code = :CURRENCY_CODE ' ||
'and gb.code_combination_id = :CCID ' ||
'and ((gb.translated_flag is null) or (gb.translated_flag = ''Y'')) ' ||
'and gb.actual_flag = ''E'' ' ||
'and gb.period_name = gs.period_name ' ||
'and gb.period_type = gs.period_type ' ||
'and gb.period_year = gs.period_year ' ||
'and gb.period_num = gs.period_num ' ||
'and gs.set_of_books_id = :B_SET_OF_BOOKS_ID ' ||
'and gs.application_id = 101 ';
select nvl(a.ytd_amount, 0) YTD_Amount
from PSB_WS_ACCOUNT_LINES a,
PSB_WORKSHEETS b,
PSB_BUDGET_PERIODS c
where a.code_combination_id = l_mapped_ccid
and a.balance_type = p_balance_type
and a.currency_code = p_currency_code
and exists
(select 1
from PSB_WORKSHEETS b,
PSB_BUDGET_PERIODS c
where b.worksheet_id = p_worksheet_id
and a.stage_set_id = b.stage_set_id
and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
and c.budget_calendar_id = p_budget_calendar_id
and a.budget_year_id = c.budget_period_id
and c.budget_period_type = 'Y'
and c.budget_year_type_id = p_budget_year_type_id)
and exists
(select 1
from PSB_WS_LINES
where account_line_id = a.account_line_id
and worksheet_id = p_worksheet_id);
select sum(nvl(a.ytd_amount, 0)) YTD_Amount
from PSB_WORKSHEETS b,
PSB_WS_LINES d,
PSB_WS_ACCOUNT_LINES a,
PSB_BUDGET_PERIODS c
where b.worksheet_id = p_worksheet_id
and d.worksheet_id = b.worksheet_id
and d.account_line_id = a.account_line_id
and a.code_combination_id = l_mapped_ccid
and a.balance_type = p_balance_type
and a.currency_code = p_currency_code
and a.stage_set_id = b.stage_set_id
and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
and c.budget_calendar_id = p_budget_calendar_id
and a.budget_year_id = c.budget_period_id
and c.budget_period_type = 'Y'
and c.budget_year_type_id = p_budget_year_type_id; */
select nvl(a.ytd_amount, 0) YTD_Amount
from PSB_WS_ACCOUNT_LINES a,
PSB_WORKSHEETS b,
PSB_BUDGET_PERIODS c
where a.code_combination_id = l_mapped_ccid
and a.balance_type = p_balance_type
and a.currency_code = p_currency_code
and exists
(select 1
from PSB_WORKSHEETS b,
PSB_BUDGET_PERIODS c
where b.worksheet_id = p_worksheet_id
and a.stage_set_id = b.stage_set_id
and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
and c.budget_calendar_id = p_budget_calendar_id
and a.budget_year_id = c.budget_period_id
and c.budget_period_type = 'Y'
and c.budget_year_type_id = p_budget_year_type_id)
and exists
(select 1
from PSB_WS_LINES
where account_line_id = a.account_line_id
and worksheet_id = p_worksheet_id)
and 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);
select sum(nvl(a.ytd_amount, 0)) YTD_Amount
from PSB_WORKSHEETS b,
PSB_WS_LINES d,
PSB_WS_ACCOUNT_LINES a,
PSB_BUDGET_PERIODS c
where b.worksheet_id = p_worksheet_id
and d.worksheet_id = b.worksheet_id
and d.account_line_id = a.account_line_id
and a.code_combination_id = l_mapped_ccid
and a.balance_type = p_balance_type
and a.currency_code = p_currency_code
and a.stage_set_id = b.stage_set_id
and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
and c.budget_calendar_id = p_budget_calendar_id
and a.budget_year_id = c.budget_period_id
and c.budget_period_type = 'Y'
and c.budget_year_type_id = p_budget_year_type_id
and exists
(select 1
from PSB_WS_SUBMIT_SERVICE_PACKAGES e
where e.service_package_id = a.service_package_id
and e.worksheet_id = p_worksheet_id); */
FOR l_ytd_rec IN (SELECT NVL(A.YTD_AMOUNT, 0) YTD_AMOUNT
FROM PSB_WS_LINES WSL,
PSB_WS_ACCOUNT_LINES A
WHERE A.CODE_COMBINATION_ID = l_mapped_ccid
AND A.BUDGET_YEAR_ID = p_budget_period_id
AND A.BALANCE_TYPE = p_balance_type
AND A.CURRENCY_CODE = p_currency_code
AND A.STAGE_SET_ID = p_stage_set_id
AND p_current_stage_seq
BETWEEN A.START_STAGE_SEQ AND A.CURRENT_STAGE_SEQ
AND WSL.WORKSHEET_ID = p_worksheet_id
AND WSL.ACCOUNT_LINE_ID = A.ACCOUNT_LINE_ID
AND EXISTS( SELECT 1
FROM PSB_WS_SUBMIT_SERVICE_PACKAGES S
WHERE S.SERVICE_PACKAGE_ID = A.SERVICE_PACKAGE_ID
AND S.WORKSHEET_ID = p_worksheet_id))
LOOP
l_ytd_amount := l_ytd_amount + l_ytd_rec.ytd_amount;
FOR l_ytd_rec IN (SELECT NVL(A.YTD_AMOUNT, 0) YTD_AMOUNT
FROM PSB_WS_LINES WSL,
PSB_WS_ACCOUNT_LINES A
WHERE A.CODE_COMBINATION_ID = l_mapped_ccid
AND A.BUDGET_YEAR_ID = p_budget_period_id
AND A.BALANCE_TYPE = p_balance_type
AND A.CURRENCY_CODE = p_currency_code
AND A.STAGE_SET_ID = p_stage_set_id
AND p_current_stage_seq
BETWEEN A.START_STAGE_SEQ AND A.CURRENT_STAGE_SEQ
AND WSL.WORKSHEET_ID = p_worksheet_id
AND WSL.ACCOUNT_LINE_ID = A.ACCOUNT_LINE_ID)
LOOP
l_ytd_amount := l_ytd_amount + l_ytd_rec.ytd_amount;
select sum(nvl(a.ytd_amount,0)) Sum_Acc
from PSB_WS_ACCOUNT_LINES a,
PSB_WORKSHEETS b
where exists
(select 1
from PSB_WS_LINES
where account_line_id = a.account_line_id
and worksheet_id = p_worksheet_id)
and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
and a.balance_type = 'E'
and a.stage_set_id = b.stage_set_id
and a.currency_code = p_currency_code
and a.budget_year_id = p_budget_year_id
and a.code_combination_id = l_mapped_ccid
and b.worksheet_id = p_worksheet_id;
select sum(nvl(a.ytd_amount,0)) Sum_Acc
from PSB_WS_ACCOUNT_LINES a,
PSB_WORKSHEETS b
where exists
(select 1
from PSB_WS_SUBMIT_SERVICE_PACKAGES c
where c.service_package_id = a.service_package_id
and c.worksheet_id = p_worksheet_id)
and exists
(select 1
from PSB_WS_LINES
where account_line_id = a.account_line_id
and worksheet_id = p_worksheet_id)
and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
and a.balance_type = 'E'
and a.stage_set_id = b.stage_set_id
and a.currency_code = p_currency_code
and a.budget_year_id = p_budget_year_id
and a.code_combination_id = l_mapped_ccid
and b.worksheet_id = p_worksheet_id;
select distinct f.account_type
from psb_budget_accounts d,
psb_set_relations_v e,
psb_ws_account_lines f,
psb_ws_lines g
where d.account_position_set_id = e.account_position_set_id
and e.account_or_position_type = 'A'
and e.constraint_id = p_constraint_id
and d.code_combination_id = f.code_combination_id
and f.account_line_id = g.account_line_id
and g.worksheet_id = p_worksheet_id;
select distinct account_type
from gl_code_combinations
where code_combination_id = p_ccid;
SELECT budget_group_id
FROM psb_budget_groups
WHERE budget_group_type = 'R'
START WITH budget_group_id = c_budgetgroup_id
CONNECT BY prior budget_group_id = parent_budget_group_id;
SELECT d.code_combination_id, e.account_position_set_id
FROM psb_budget_accounts d,
psb_set_relations e
WHERE d.account_position_set_id = e.account_position_set_id
AND e.constraint_id = p_constraint_id
AND EXISTS
(SELECT 1
FROM psb_budget_accounts a,
psb_set_relations b
WHERE a.account_position_set_id = b.account_position_set_id
AND b.budget_group_id = l_budget_group_id
AND a.code_combination_id = d.code_combination_id);
SELECT d.code_combination_id, e.account_position_set_id
FROM psb_budget_accounts d,
psb_set_relations e
WHERE d.account_position_set_id = e.account_position_set_id
--AND e.account_or_position_type = 'A'
AND e.constraint_id = p_constraint_id;
SELECT global_worksheet_flag
INTO l_global_ws_flag
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id;
select step_number,
prefix_operator,
budget_year_type_id,
balance_type,
currency_code,
nvl(amount, 0) amount,
postfix_operator,
segment1, segment2, segment3,
segment4, segment5, segment6,
segment7, segment8, segment9,
segment10, segment11, segment12,
segment13, segment14, segment15,
segment16, segment17, segment18,
segment19, segment20, segment21,
segment22, segment23, segment24,
segment25, segment26, segment27,
segment28, segment29, segment30
from PSB_CONSTRAINT_FORMULAS
where constraint_id = p_constraint_id
order by step_number;
SELECT d.code_combination_id
FROM psb_budget_accounts d,
psb_set_relations e
WHERE d.account_position_set_id = e.account_position_set_id
AND e.constraint_id = p_constraint_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);
select a.code_combination_id ccid
from PSB_BUDGET_ACCOUNTS a,
PSB_SET_RELATIONS_V b
where exists
(select 1
from PSB_BUDGET_ACCOUNTS c,
PSB_SET_RELATIONS_V d
where c.account_position_set_id = d.account_position_set_id
and c.code_combination_id = a.code_combination_id
and d.account_or_position_type = 'A'
and exists
(select 1
from psb_budget_groups e
where e.budget_group_type = 'R'
and e.effective_start_date <= g_startdate_pp
and (e.effective_end_date is null or e.effective_end_date >= g_enddate_cy)
and e.budget_group_id = d.budget_group_id
start with e.budget_group_id = p_budget_group_id
connect by prior e.budget_group_id = e.parent_budget_group_id))
and a.account_position_set_id = b.account_position_set_id
and b.account_or_position_type = 'A'
and b.constraint_id = p_constraint_id;
select constraint_id,
name,
currency_code,
severity_level,
effective_start_date,
effective_end_date,
constraint_detailed_flag
from PSB_CONSTRAINT_ASSIGNMENTS_V
where constraint_type = 'ACCOUNT'
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,
effective_start_date,
effective_end_date;
select 'x'
from dual
where exists
(select 'Service Package Exists'
from PSB_WS_SUBMIT_SERVICE_PACKAGES
where worksheet_id = p_worksheet_id);
FOR l_stage_rec IN ( SELECT stage_set_id, current_stage_seq
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id)
LOOP
l_stage_set_id := l_stage_rec.stage_set_id;
FOR l_budget_period_rec IN (select budget_period_id, budget_year_type_id
from psb_budget_periods
where budget_calendar_id = p_budget_calendar_id
and budget_period_type = 'Y'
and parent_budget_period_id is null
)
LOOP
l_budget_period_tbl(l_budget_period_rec.budget_year_type_id) :=
l_budget_period_rec.budget_period_id;
select note_id
from PSB_WS_ACCOUNT_LINES
where account_line_id = p_account_line_id;
Insert into PSB_WS_ACCOUNT_LINE_NOTES
(note_id, note, last_update_date, last_updated_by, last_update_login, created_by, creation_date)
values (psb_ws_account_line_notes_s.nextval, p_note, sysdate, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID, FND_GLOBAL.USER_ID, sysdate)
returning note_id into l_note_id;
update PSB_WS_ACCOUNT_LINES
set note_id = l_note_id
where account_line_id = p_account_line_id;
UPDATE PSB_WS_ACCOUNT_LINE_NOTES
SET note = note || FND_GLOBAL.NewLine || p_note,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID,
created_by = FND_GLOBAL.USER_ID,
creation_date = sysdate
WHERE note_id = l_note_id;