[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select to_number(attribute1) count_ps1,
to_date(attribute2, 'YYYY/MM/DD HH24:MI:SS') lud_ps1,
to_number(attribute3) count_ps2,
to_date(attribute4, 'YYYY/MM/DD HH24:MI:SS') lud_ps2,
to_number(attribute5) count_cs1,
to_date(attribute6, 'YYYY/MM/DD HH24:MI:SS') lud_cs1,
to_number(attribute7) count_cs2,
to_date(attribute8, 'YYYY/MM/DD HH24:MI:SS') lud_cs2,
to_number(attribute9) count_ar1,
to_date(attribute10, 'YYYY/MM/DD HH24:MI:SS') lud_ar1,
to_number(attribute11) count_ar2,
to_date(attribute12, 'YYYY/MM/DD HH24:MI:SS') lud_ar2,
to_number(attribute13) count_bc,
to_date(attribute14, 'YYYY/MM/DD HH24:MI:SS') lud_bc,
to_number(attribute15) count_bg1,
to_date(attribute16, 'YYYY/MM/DD HH24:MI:SS') lud_bg1,
to_number(attribute17) count_bg2,
to_date(attribute18, 'YYYY/MM/DD HH24:MI:SS') lud_bg2,
to_date(attribute19, 'YYYY/MM/DD HH24:MI:SS') lud_de,
to_number(attribute20) count_assign,
to_date(attribute21, 'YYYY/MM/DD HH24:MI:SS') lud_assign,
to_number(attribute22) count_rates,
to_date(attribute23, 'YYYY/MM/DD HH24:MI:SS') lud_rates,
to_number(attribute24) count_dist,
to_date(attribute25, 'YYYY/MM/DD HH24:MI:SS') lud_dist,
to_number(attribute26) count_glset1,
to_date(attribute27, 'YYYY/MM/DD HH24:MI:SS') lud_glset1,
to_number(attribute28) count_glset2,
to_date(attribute29, 'YYYY/MM/DD HH24:MI:SS') lud_glset2,
TO_DATE(attribute30, 'YYYY/MM/DD HH24:MI:SS') gcd_ws,
sp1_status,
sp2_status,
sp3_status,
sp4_status
from PSB_REENTRANT_PROCESS_STATUS
where process_type = 'WORKSHEET_CREATION'
and process_uid = p_worksheet_id;
select Count(*) count_glset1,
Max(last_update_date) lud_glset1
from PSB_GL_BUDGETS
where gl_budget_set_id = p_gl_budget_set_id;
select Count(*) count_glset2,
Max(last_update_date) lud_glset2
from PSB_SET_RELATIONS
where gl_budget_id in
(select gl_budget_id
from PSB_GL_BUDGETS
where gl_budget_set_id = p_gl_budget_set_id);
select Count(*) count_ps1,
Max(last_update_date) lud_ps1
from PSB_PARAMETER_ASSIGNMENTS_V
where parameter_set_id = p_parameter_set_id;
select Count(*) count_ps2,
Max(last_update_date) lud_ps2
from PSB_SET_RELATIONS
where parameter_id in
(select parameter_id
from PSB_PARAMETER_ASSIGNMENTS_V
where parameter_set_id = p_parameter_set_id);
select Count(*) count_cs1,
Max(last_update_date) lud_cs1
from PSB_CONSTRAINT_ASSIGNMENTS_V
where constraint_set_id = p_constraint_set_id;
select Count(*) count_cs2,
Max(last_update_date) lud_cs2
from PSB_SET_RELATIONS
where constraint_id in
(select constraint_id
from PSB_CONSTRAINT_ASSIGNMENTS_V
where constraint_set_id = p_constraint_set_id);
select Count(*) count_ar1,
Max(last_update_date) lud_ar1
from PSB_ALLOCRULE_ASSIGNMENTS_V
where allocrule_set_id = p_allocrule_set_id;
select Count(*) count_ar2,
Max(last_update_date) lud_ar2
from PSB_SET_RELATIONS
where allocation_rule_id in
(select allocrule_id
from PSB_ALLOCRULE_ASSIGNMENTS_V
where allocrule_set_id = p_allocrule_set_id);
select Count(*) count_bc,
Max(last_update_date) lud_bc
from PSB_BUDGET_PERIODS
where budget_period_type = 'Y'
and budget_calendar_id = p_budget_calendar_id;
select Count(*) count_bg1,
Max(last_update_date) lud_bg1
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)
start with budget_group_id = p_budget_group_id
connect by prior budget_group_id = parent_budget_group_id;
select Count(*) count_bg2,
Max(last_update_date) lud_bg2
from PSB_SET_RELATIONS
where budget_group_id in
(select budget_group_id
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)
start with budget_group_id = p_budget_group_id
connect by prior budget_group_id = parent_budget_group_id);
select last_extract_date lud_de
from PSB_DATA_EXTRACTS
where data_extract_id = p_data_extract_id;
select count(*) count_assign,
Max(last_update_date) lud_assign
from PSB_POSITION_ASSIGNMENTS
where data_extract_id = p_data_extract_id;
select count(*) count_rates,
Max(last_update_date) lud_rates
from PSB_PAY_ELEMENT_RATES
where pay_element_id in
(select pay_element_id
from PSB_PAY_ELEMENTS
where data_extract_id = p_data_extract_id);
select count(*) count_dist,
Max(last_update_date) lud_dist
from PSB_POSITION_PAY_DISTRIBUTIONS
where data_extract_id = p_data_extract_id;
insert into PSB_REENTRANT_PROCESS_STATUS
(process_type, process_uid,
attribute1, attribute2,
attribute3, attribute4,
attribute5, attribute6,
attribute7, attribute8,
attribute9, attribute10,
attribute11, attribute12,
attribute13, attribute14,
attribute15, attribute16,
attribute17, attribute18,
attribute19, attribute20,
attribute21, attribute22,
attribute23, attribute24,
attribute25, attribute26,
attribute27, attribute28,
attribute29, attribute30,
sp1_status, sp2_status,
sp3_status, sp4_status,
sp5_status, sp6_status,
sp7_status, sp8_status,
sp9_status, sp10_status,
sp11_status, sp12_status,
sp13_status, sp14_status,
sp15_status, sp16_status,
sp17_status, sp18_status,
sp19_status, sp20_status,
sp21_status, sp22_status,
sp23_status, sp24_status,
sp25_status, sp26_status,
sp27_status, sp28_status,
sp29_status, sp30_status)
values ('WORKSHEET_CREATION', p_worksheet_id,
to_char(l_count_ps1), to_char(l_lud_ps1, 'YYYY/MM/DD HH24:MI:SS'),
to_char(l_count_ps2), to_char(l_lud_ps2, 'YYYY/MM/DD HH24:MI:SS'),
to_char(l_count_cs1), to_char(l_lud_cs1, 'YYYY/MM/DD HH24:MI:SS'),
to_char(l_count_cs2), to_char(l_lud_cs2, 'YYYY/MM/DD HH24:MI:SS'),
to_char(l_count_ar1), to_char(l_lud_ar1, 'YYYY/MM/DD HH24:MI:SS'),
to_char(l_count_ar2), to_char(l_lud_ar2, 'YYYY/MM/DD HH24:MI:SS'),
to_char(l_count_bc), to_char(l_lud_bc, 'YYYY/MM/DD HH24:MI:SS'),
to_char(l_count_bg1), to_char(l_lud_bg1, 'YYYY/MM/DD HH24:MI:SS'),
to_char(l_count_bg2), to_char(l_lud_bg2, 'YYYY/MM/DD HH24:MI:SS'),
to_char(l_lud_de, 'YYYY/MM/DD HH24:MI:SS'),
to_char(l_count_assign), to_char(l_lud_assign, 'YYYY/MM/DD HH24:MI:SS'),
to_char(l_count_rates), to_char(l_lud_rates, 'YYYY/MM/DD HH24:MI:SS'),
to_char(l_count_dist), to_char(l_lud_dist, 'YYYY/MM/DD HH24:MI:SS'),
to_char(l_count_glset1), to_char(l_lud_glset1, 'YYYY/MM/DD HH24:MI:SS'),
to_char(l_count_glset2), to_char(l_lud_glset2, 'YYYY/MM/DD HH24:MI:SS'),
/* Bug 3525832 Start */
TO_CHAR(g_gl_cutoff_period, 'YYYY/MM/DD HH24:MI:SS'),
/* Bug 3525832 End */
'I', 'I',
'I', 'I',
null, null,
null, null,
null, null,
null, null,
null, null,
null, null,
null, null,
null, null,
null, null,
null, null,
null, null,
null, null,
null, null);
update PSB_REENTRANT_PROCESS_STATUS
set sp1_status = g_sp1_status,
sp2_status = g_sp2_status,
sp3_status = g_sp3_status,
sp4_status = g_sp4_status
where process_type = 'WORKSHEET_CREATION'
and process_uid = p_worksheet_id;
update PSB_REENTRANT_PROCESS_STATUS
set attribute1 = to_char(l_count_ps1),
attribute2 = to_char(l_lud_ps1, 'YYYY/MM/DD HH24:MI:SS'),
attribute3 = to_char(l_count_ps2),
attribute4 = to_char(l_lud_ps2, 'YYYY/MM/DD HH24:MI:SS'),
attribute5 = to_char(l_count_cs1),
attribute6 = to_char(l_lud_cs1, 'YYYY/MM/DD HH24:MI:SS'),
attribute7 = to_char(l_count_cs2),
attribute8 = to_char(l_lud_cs2, 'YYYY/MM/DD HH24:MI:SS'),
attribute9 = to_char(l_count_ar1),
attribute10 = to_char(l_lud_ar1, 'YYYY/MM/DD HH24:MI:SS'),
attribute11 = to_char(l_count_ar2),
attribute12 = to_char(l_lud_ar2, 'YYYY/MM/DD HH24:MI:SS'),
attribute13 = to_char(l_count_bc),
attribute14 = to_char(l_lud_bc, 'YYYY/MM/DD HH24:MI:SS'),
attribute15 = to_char(l_count_bg1),
attribute16 = to_char(l_lud_bg1, 'YYYY/MM/DD HH24:MI:SS'),
attribute17 = to_char(l_count_bg2),
attribute18 = to_char(l_lud_bg2, 'YYYY/MM/DD HH24:MI:SS'),
attribute19 = to_char(l_lud_de, 'YYYY/MM/DD HH24:MI:SS'),
attribute20 = to_char(l_count_assign),
attribute21 = to_char(l_lud_assign, 'YYYY/MM/DD HH24:MI:SS'),
attribute22 = to_char(l_count_rates),
attribute23 = to_char(l_lud_rates, 'YYYY/MM/DD HH24:MI:SS'),
attribute24 = to_char(l_count_dist),
attribute25 = to_char(l_lud_dist, 'YYYY/MM/DD HH24:MI:SS'),
attribute26 = to_char(l_count_glset1),
attribute27 = to_char(l_lud_glset1, 'YYYY/MM/DD HH24:MI:SS'),
attribute28 = to_char(l_count_glset2),
attribute29 = to_char(l_lud_glset2, 'YYYY/MM/DD HH24:MI:SS'),
/* Bug 3525832 Start */
attribute30 = TO_CHAR(g_gl_cutoff_period, 'YYYY/MM/DD HH24:MI:SS'),
/* Bug 3525832 End */
sp1_status = g_sp1_status,
sp2_status = g_sp2_status,
sp3_status = g_sp3_status,
sp4_status = g_sp4_status
where process_type = 'WORKSHEET_CREATION'
and process_uid = p_worksheet_id;
PSB_WORKSHEET.Update_Worksheet
(p_api_version => 1.0,
p_return_status => l_return_status,
p_worksheet_id => p_worksheet_id,
p_ws_creation_complete => 'N');
update PSB_REENTRANT_PROCESS_STATUS
set sp1_status = 'C'
where process_type = 'WORKSHEET_CREATION'
and process_uid = p_worksheet_id;
update PSB_REENTRANT_PROCESS_STATUS
set sp2_status = 'C'
where process_type = 'WORKSHEET_CREATION'
and process_uid = p_worksheet_id;
update PSB_REENTRANT_PROCESS_STATUS
set sp3_status = 'C'
where process_type = 'WORKSHEET_CREATION'
and process_uid = p_worksheet_id;
update PSB_REENTRANT_PROCESS_STATUS
set sp4_status = 'C'
where process_type = 'WORKSHEET_CREATION'
and process_uid = p_worksheet_id;
PSB_WORKSHEET.Update_Worksheet
(p_api_version => 1.0,
p_return_status => l_return_status,
p_worksheet_id => p_worksheet_id,
p_ws_creation_complete => 'Y');
select budget_group_id,
nvl(global_worksheet_id, worksheet_id) global_worksheet_id,
nvl(constraint_set_id, global_constraint_set_id) constraint_set_id,
budget_calendar_id,
nvl(data_extract_id, global_data_extract_id) data_extract_id,
budget_by_position
from PSB_WORKSHEETS_V
where worksheet_id = p_worksheet_id;
select nvl(chart_of_accounts_id, root_chart_of_accounts_id) chart_of_accounts_id,
nvl(currency_code, root_currency_code) currency_code,
nvl(business_group_id, root_business_group_id) business_group_id
from PSB_BUDGET_GROUPS_V
where budget_group_id = l_budget_group_id;
select name,
constraint_threshold
from PSB_CONSTRAINT_SETS_V
where constraint_set_id = l_constraint_set_id;
delete from PSB_ERROR_MESSAGES
where source_process = 'WORKSHEET_CREATION'
and process_id = p_worksheet_id;
select 'Exists'
from PSB_ENTITY_SET
where (p_data_extract_id is null or data_extract_id = p_data_extract_id)
and entity_set_id = p_parameter_set_id;
select 'Exists'
from PSB_ENTITY_SET
where (p_data_extract_id is null or data_extract_id = p_data_extract_id)
and entity_set_id = p_constraint_set_id;
delete from PSB_ERROR_MESSAGES
where source_process = 'WORKSHEET_CREATION'
and process_id = p_worksheet_id;
PSB_WORKSHEET.Update_Worksheet
(p_api_version => 1.0,
p_return_status => l_return_status,
p_worksheet_id => p_worksheet_id,
p_ws_creation_complete => 'N');
SELECT 1
FROM PSB_REENTRANT_PROCESS_STATUS
WHERE PROCESS_UID = p_worksheet_id AND
PROCESS_TYPE = 'WORKSHEET_CREATION'
)
LOOP
g_ws_first_time_creation_flag := FALSE;
update PSB_REENTRANT_PROCESS_STATUS
set sp1_status = 'C'
where process_type = 'WORKSHEET_CREATION'
and process_uid = p_worksheet_id;
update PSB_REENTRANT_PROCESS_STATUS
set sp2_status = 'C'
where process_type = 'WORKSHEET_CREATION'
and process_uid = p_worksheet_id;
select budget_group_id,
nvl(global_worksheet_id, worksheet_id) global_worksheet_id,
nvl(constraint_set_id, global_constraint_set_id) constraint_set_id,
budget_calendar_id,
nvl(data_extract_id, global_data_extract_id) data_extract_id,
budget_by_position
from PSB_WORKSHEETS_V
where worksheet_id = p_worksheet_id;
select nvl(chart_of_accounts_id, root_chart_of_accounts_id) chart_of_accounts_id,
nvl(currency_code, root_currency_code) currency_code,
nvl(business_group_id, root_business_group_id) business_group_id
from PSB_BUDGET_GROUPS_V
where budget_group_id = l_budget_group_id;
select name,
constraint_threshold
from PSB_CONSTRAINT_SETS_V
where constraint_set_id = l_constraint_set_id;
select budget_group_id,
nvl(global_worksheet_id, worksheet_id) global_worksheet_id,
nvl(constraint_set_id, global_constraint_set_id) constraint_set_id,
budget_calendar_id,
nvl(data_extract_id, global_data_extract_id) data_extract_id,
budget_by_position
from PSB_WORKSHEETS_V
where worksheet_id = p_worksheet_id;
select nvl(chart_of_accounts_id, root_chart_of_accounts_id) chart_of_accounts_id,
nvl(currency_code, root_currency_code) currency_code,
nvl(business_group_id, root_business_group_id) business_group_id
from PSB_BUDGET_GROUPS_V
where budget_group_id = l_budget_group_id;
select name,
constraint_threshold
from PSB_CONSTRAINT_SETS_V
where constraint_set_id = l_constraint_set_id;
select budget_group_id,
nvl(global_worksheet_id, worksheet_id) global_worksheet_id,
nvl(constraint_set_id, global_constraint_set_id) constraint_set_id,
budget_calendar_id,
nvl(data_extract_id, global_data_extract_id) data_extract_id,
budget_by_position
from PSB_WORKSHEETS_V
where worksheet_id = p_worksheet_id;
select nvl(chart_of_accounts_id, root_chart_of_accounts_id) chart_of_accounts_id,
nvl(currency_code, root_currency_code) currency_code,
nvl(business_group_id, root_business_group_id) business_group_id
from PSB_BUDGET_GROUPS_V
where budget_group_id = l_budget_group_id;
select name,
constraint_threshold
from PSB_CONSTRAINT_SETS_V
where constraint_set_id = l_constraint_set_id;
PSB_WORKSHEET.Update_Worksheet
(p_api_version => 1.0,
p_return_status => l_return_status,
p_worksheet_id => p_worksheet_id,
p_ws_creation_complete => 'Y');
PROCEDURE Delete_WS_Line_Items
( 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_global_worksheet IN VARCHAR2 := FND_API.G_TRUE
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_WS_Line_Items';
delete from PSB_POSITION_ASSIGNMENTS
where worksheet_id = p_worksheet_id;
delete from PSB_PAY_ELEMENT_RATES
where worksheet_id = p_worksheet_id;
delete from PSB_WS_POSITION_LINES
where position_line_id in
(select position_line_id
from PSB_WS_LINES_POSITIONS
where worksheet_id = p_worksheet_id);
delete from PSB_WS_FTE_LINES
where position_line_id in
(select position_line_id
from PSB_WS_LINES_POSITIONS
where worksheet_id = p_worksheet_id);
delete from PSB_WS_ELEMENT_LINES
where position_line_id in
(select position_line_id
from PSB_WS_LINES_POSITIONS
where worksheet_id = p_worksheet_id);
delete from PSB_WS_LINES_POSITIONS
where worksheet_id = p_worksheet_id;
delete from PSB_WS_ACCOUNT_LINES
where account_line_id in
(select account_line_id
from PSB_WS_LINES
where worksheet_id = p_worksheet_id);
delete from PSB_WS_LINES
where worksheet_id = p_worksheet_id;
delete from PSB_REENTRANT_PROCESS_STATUS
where process_type = 'WORKSHEET_CREATION'
and process_uid = p_worksheet_id;
END Delete_WS_Line_Items;
select min(sequence_number) sequence_number
from psb_budget_stages
where budget_stage_set_id = p_stage_set_id;
select psb_worksheets_s.nextval worksheet_id
from dual;
select short_name
from psb_budget_groups
where budget_group_id = p_budget_group_id;
insert into PSB_WORKSHEETS
(worksheet_id,
budget_group_id,
budget_calendar_id,
worksheet_type,
name,
description,
ws_creation_complete,
stage_set_id,
current_stage_seq,
global_worksheet_id,
global_worksheet_flag,
global_worksheet_option,
local_copy_flag,
copy_of_worksheet_id,
freeze_flag,
budget_by_position,
use_revised_element_rates,
num_proposed_years,
num_years_to_allocate,
rounding_factor,
gl_cutoff_period,
budget_version_id,
gl_budget_set_id,
include_stat_balance,
include_translated_balance,
include_adjustment_periods,
data_extract_id,
parameter_set_id,
constraint_set_id,
allocrule_set_id,
date_submitted,
submitted_by,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
context,
create_non_pos_line_items,
apply_element_parameters,
apply_position_parameters,
create_positions,
create_summary_totals,
apply_constraints,
flex_mapping_set_id,
include_gl_commit_balance,
include_gl_oblig_balance,
include_gl_other_balance,
include_cbc_commit_balance,
include_cbc_oblig_balance,
include_cbc_budget_balance,
/* For Bug 3157960, added the federal ws flag */
federal_ws_flag,
/* bug no 4725091 */
include_gl_forward_balance)
values (l_worksheet_id,
p_budget_group_id,
p_budget_calendar_id,
p_worksheet_type,
decode(p_name, FND_API.G_MISS_CHAR, l_worksheet_name, null, l_worksheet_name, p_name),
p_description,
decode(p_ws_creation_complete, FND_API.G_MISS_CHAR, null, p_ws_creation_complete),
p_stage_set_id,
decode(p_current_stage_seq, FND_API.G_MISS_NUM, l_start_stage_seq, null, l_start_stage_seq, p_current_stage_seq),
decode(p_global_worksheet_id, FND_API.G_MISS_NUM, null, p_global_worksheet_id),
decode(p_global_worksheet_flag, FND_API.G_MISS_CHAR, null, p_global_worksheet_flag),
decode(p_global_worksheet_option, FND_API.G_MISS_CHAR, null, p_global_worksheet_option),
decode(p_local_copy_flag, FND_API.G_MISS_CHAR, null, p_local_copy_flag),
decode(p_copy_of_worksheet_id, FND_API.G_MISS_NUM, null, p_copy_of_worksheet_id),
decode(p_freeze_flag, FND_API.G_MISS_CHAR, null, p_freeze_flag),
decode(p_budget_by_position, FND_API.G_MISS_CHAR, null, p_budget_by_position),
decode(p_use_revised_element_rates, FND_API.G_MISS_CHAR, null, p_use_revised_element_rates),
decode(p_num_proposed_years, FND_API.G_MISS_NUM, null, p_num_proposed_years),
decode(p_num_years_to_allocate, FND_API.G_MISS_NUM, null, p_num_years_to_allocate),
decode(p_rounding_factor, FND_API.G_MISS_NUM, null, p_rounding_factor),
decode(p_gl_cutoff_period, FND_API.G_MISS_DATE, null, p_gl_cutoff_period),
decode(p_budget_version_id, FND_API.G_MISS_NUM, null, p_budget_version_id),
decode(p_gl_budget_set_id, FND_API.G_MISS_NUM, null, p_gl_budget_set_id),
decode(p_include_stat_balance, FND_API.G_MISS_CHAR, null, p_include_stat_balance),
decode(p_include_trans_balance, FND_API.G_MISS_CHAR, null, p_include_trans_balance),
decode(p_include_adj_period, FND_API.G_MISS_CHAR, null, p_include_adj_period),
decode(p_data_extract_id, FND_API.G_MISS_NUM, null, p_data_extract_id),
decode(p_parameter_set_id, FND_API.G_MISS_NUM, null, p_parameter_set_id),
decode(p_constraint_set_id, FND_API.G_MISS_NUM, null, p_constraint_set_id),
decode(p_allocrule_set_id, FND_API.G_MISS_NUM, null, p_allocrule_set_id),
decode(p_date_submitted, FND_API.G_MISS_DATE, null, p_date_submitted),
decode(p_submitted_by, FND_API.G_MISS_NUM, null, p_submitted_by),
sysdate,
l_userid,
l_loginid,
l_userid,
sysdate,
decode(p_attribute1, FND_API.G_MISS_CHAR, null, p_attribute1),
decode(p_attribute2, FND_API.G_MISS_CHAR, null, p_attribute2),
decode(p_attribute3, FND_API.G_MISS_CHAR, null, p_attribute3),
decode(p_attribute4, FND_API.G_MISS_CHAR, null, p_attribute4),
decode(p_attribute5, FND_API.G_MISS_CHAR, null, p_attribute5),
decode(p_attribute6, FND_API.G_MISS_CHAR, null, p_attribute6),
decode(p_attribute7, FND_API.G_MISS_CHAR, null, p_attribute7),
decode(p_attribute8, FND_API.G_MISS_CHAR, null, p_attribute8),
decode(p_attribute9, FND_API.G_MISS_CHAR, null, p_attribute9),
decode(p_attribute10, FND_API.G_MISS_CHAR, null, p_attribute10),
decode(p_context, FND_API.G_MISS_CHAR, null, p_context),
decode(p_create_non_pos_line_items, FND_API.G_MISS_CHAR, null, p_create_non_pos_line_items),
decode(p_apply_element_parameters, FND_API.G_MISS_CHAR, null, p_apply_element_parameters),
decode(p_apply_position_parameters, FND_API.G_MISS_CHAR, null, p_apply_position_parameters),
decode(p_create_positions, FND_API.G_MISS_CHAR, null, p_create_positions),
decode(p_create_summary_totals, FND_API.G_MISS_CHAR, null, p_create_summary_totals),
decode(p_apply_constraints, FND_API.G_MISS_CHAR, null, p_apply_constraints),
decode(p_flex_mapping_set_id, FND_API.G_MISS_NUM, null, p_flex_mapping_set_id),
decode(p_include_gl_commit_balance, FND_API.G_MISS_CHAR, null, p_include_gl_commit_balance),
decode(p_include_gl_oblig_balance, FND_API.G_MISS_CHAR, null, p_include_gl_oblig_balance),
decode(p_include_gl_other_balance, FND_API.G_MISS_CHAR, null, p_include_gl_other_balance),
decode(p_include_cbc_commit_balance, FND_API.G_MISS_CHAR, null, p_include_cbc_commit_balance),
decode(p_include_cbc_oblig_balance, FND_API.G_MISS_CHAR, null, p_include_cbc_oblig_balance),
decode(p_include_cbc_budget_balance, FND_API.G_MISS_CHAR, null, p_include_cbc_oblig_balance),
decode(p_federal_ws_flag,FND_API.G_MISS_CHAR,null,p_federal_ws_flag),
/* bug no 4725091 */
decode(p_include_gl_forwd_balance,FND_API.G_MISS_CHAR, null, p_include_gl_forwd_balance)
);
PROCEDURE Update_Worksheet
( 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 := FND_API.G_MISS_NUM,
p_worksheet_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ws_creation_complete IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_global_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
p_local_copy_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_copy_of_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
p_freeze_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_use_revised_element_rates IN VARCHAR2 := FND_API.G_MISS_CHAR,
/* Bug # 3083970 */
p_num_proposed_years IN NUMBER := FND_API.G_MISS_NUM,
p_rounding_factor IN NUMBER := FND_API.G_MISS_NUM,
/* End bug */
p_date_submitted IN DATE := FND_API.G_MISS_DATE,
p_submitted_by IN NUMBER := FND_API.G_MISS_NUM,
p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_context IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_create_non_pos_line_items IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_apply_element_parameters IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_apply_position_parameters IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_create_positions IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_create_summary_totals IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_apply_constraints IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_include_gl_commit_balance IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_include_gl_oblig_balance IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_include_gl_other_balance IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_include_cbc_commit_balance IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_include_cbc_oblig_balance IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_include_cbc_budget_balance IN VARCHAR2 := FND_API.G_MISS_CHAR,
/* For Bug No. 2312657 : Start */
p_gl_cutoff_period IN DATE := NULL,
p_gl_budget_set_id IN NUMBER := NULL,
/* For Bug No. 2312657 : End */
p_federal_ws_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
/* bug no 4725091 */
p_include_gl_forwd_balance IN VARCHAR2 := FND_API.G_MISS_CHAR
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Worksheet';
select gl_cutoff_period
into l_gl_cutoff_period
from psb_worksheets
where worksheet_id = p_worksheet_id;
update PSB_WORKSHEETS
set worksheet_type = decode(p_worksheet_type, FND_API.G_MISS_CHAR, worksheet_type, p_worksheet_type),
description = decode(p_description, FND_API.G_MISS_CHAR, description, p_description),
ws_creation_complete = decode(p_ws_creation_complete, FND_API.G_MISS_CHAR, ws_creation_complete, p_ws_creation_complete),
global_worksheet_id = decode(p_global_worksheet_id, FND_API.G_MISS_NUM, global_worksheet_id, p_global_worksheet_id),
current_stage_seq = decode(p_current_stage_seq, FND_API.G_MISS_NUM, current_stage_seq, p_current_stage_seq),
local_copy_flag = decode(p_local_copy_flag, FND_API.G_MISS_CHAR, local_copy_flag, p_local_copy_flag),
copy_of_worksheet_id = decode(p_copy_of_worksheet_id, FND_API.G_MISS_NUM, copy_of_worksheet_id, p_copy_of_worksheet_id),
freeze_flag = decode(p_freeze_flag, FND_API.G_MISS_CHAR, freeze_flag, p_freeze_flag),
use_revised_element_rates = decode(p_use_revised_element_rates, FND_API.G_MISS_CHAR, use_revised_element_rates, p_use_revised_element_rates),
/* Bug # 3083970 */
num_proposed_years = decode(p_num_proposed_years, FND_API.G_MISS_NUM, num_proposed_years, p_num_proposed_years),
rounding_factor = decode(p_rounding_factor, FND_API.G_MISS_NUM, rounding_factor, p_rounding_factor),
/* End Bug # 3083970 */
date_submitted = decode(p_date_submitted, FND_API.G_MISS_DATE, date_submitted, p_date_submitted),
submitted_by = decode(p_submitted_by, FND_API.G_MISS_NUM, submitted_by, p_submitted_by),
last_update_date = sysdate,
last_updated_by = l_userid,
last_update_login = l_loginid,
attribute1 = decode(p_attribute1, FND_API.G_MISS_CHAR, attribute1, p_attribute1),
attribute2 = decode(p_attribute2, FND_API.G_MISS_CHAR, attribute2, p_attribute2),
attribute3 = decode(p_attribute3, FND_API.G_MISS_CHAR, attribute3, p_attribute3),
attribute4 = decode(p_attribute4, FND_API.G_MISS_CHAR, attribute4, p_attribute4),
attribute5 = decode(p_attribute5, FND_API.G_MISS_CHAR, attribute5, p_attribute5),
attribute6 = decode(p_attribute6, FND_API.G_MISS_CHAR, attribute6, p_attribute6),
attribute7 = decode(p_attribute7, FND_API.G_MISS_CHAR, attribute7, p_attribute7),
attribute8 = decode(p_attribute8, FND_API.G_MISS_CHAR, attribute8, p_attribute8),
attribute9 = decode(p_attribute9, FND_API.G_MISS_CHAR, attribute9, p_attribute9),
attribute10 = decode(p_attribute10, FND_API.G_MISS_CHAR, attribute10, p_attribute10),
context = decode(p_context, FND_API.G_MISS_CHAR, context, p_context),
create_non_pos_line_items = decode(p_create_non_pos_line_items, FND_API.G_MISS_CHAR, create_non_pos_line_items, p_create_non_pos_line_items),
apply_element_parameters = decode(p_apply_element_parameters, FND_API.G_MISS_CHAR, apply_element_parameters, p_apply_element_parameters),
apply_position_parameters = decode(p_apply_position_parameters, FND_API.G_MISS_CHAR, apply_position_parameters, p_apply_position_parameters),
create_positions = decode(p_create_positions, FND_API.G_MISS_CHAR, create_positions, p_create_positions),
create_summary_totals = decode(p_create_summary_totals, FND_API.G_MISS_CHAR, create_summary_totals, p_create_summary_totals),
apply_constraints = decode(p_apply_constraints, FND_API.G_MISS_CHAR, apply_constraints, p_apply_constraints),
include_gl_commit_balance = decode(p_include_gl_commit_balance, FND_API.G_MISS_CHAR, include_gl_commit_balance, p_include_gl_commit_balance),
include_gl_oblig_balance = decode(p_include_gl_oblig_balance, FND_API.G_MISS_CHAR, include_gl_oblig_balance, p_include_gl_oblig_balance),
include_gl_other_balance = decode(p_include_gl_other_balance, FND_API.G_MISS_CHAR, include_gl_other_balance, p_include_gl_other_balance),
include_cbc_commit_balance = decode(p_include_cbc_commit_balance, FND_API.G_MISS_CHAR, include_cbc_commit_balance, p_include_cbc_commit_balance),
include_cbc_oblig_balance = decode(p_include_cbc_oblig_balance, FND_API.G_MISS_CHAR, include_cbc_oblig_balance, p_include_cbc_oblig_balance),
include_cbc_budget_balance = decode(p_include_cbc_budget_balance, FND_API.G_MISS_CHAR, include_cbc_budget_balance, p_include_cbc_budget_balance),
/* For Bug No. 2312657 : Start */
gl_cutoff_period = decode(p_gl_cutoff_period, NULL, gl_cutoff_period, p_gl_cutoff_period),
gl_budget_set_id = decode(p_gl_budget_set_id, NULL, gl_budget_set_id, p_gl_budget_set_id),
/* For Bug No. 2312657 : End */
/* For Bug 3157960, added the federal ws flag */
federal_Ws_flag = decode(p_federal_ws_flag,FND_API.G_MISS_CHAR,federal_Ws_flag,p_federal_ws_flag),
/* bug no 4725091 */
include_gl_forward_balance = decode(p_include_gl_forwd_balance, FND_API.G_MISS_CHAR, include_gl_forward_balance, p_include_gl_forwd_balance)
where worksheet_id = p_worksheet_id;
update psb_worksheets
set gl_cutoff_period = p_gl_cutoff_period
where global_worksheet_id = p_worksheet_id;
END Update_Worksheet;
PROCEDURE Delete_Worksheet
( 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
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Worksheet';
delete from PSB_WORKSHEETS
where worksheet_id = p_worksheet_id;
END Delete_Worksheet;
PROCEDURE Delete_WAL
( 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) := 'Delete_WAL';
delete from PSB_WS_ACCOUNT_LINES
where account_line_id = p_account_line_id;
delete from PSB_WS_LINES
where account_line_id = p_account_line_id;
END Delete_WAL;
PROCEDURE Delete_WPL
( 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
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_WPL';
delete from PSB_WS_FTE_LINES
where position_line_id = p_position_line_id;
delete from PSB_WS_ELEMENT_LINES
where position_line_id = p_position_line_id;
delete from PSB_WS_LINES
where account_line_id in
(select account_line_id
from PSB_WS_ACCOUNT_LINES
where element_set_id is not null
and position_line_id = p_position_line_id);
delete from PSB_WS_ACCOUNT_LINES
where position_line_id = p_position_line_id;
delete from PSB_WS_LINES_POSITIONS
where position_line_id = p_position_line_id;
delete from PSB_POSITION_ASSIGNMENTS
where position_id =
(select position_id
from PSB_WS_POSITION_LINES
where position_line_id = p_position_line_id)
and worksheet_id =
(select nvl(global_worksheet_id, worksheet_id)
from PSB_WORKSHEETS
where worksheet_id = p_worksheet_id);
delete from PSB_WS_POSITION_LINES
where position_line_id = p_position_line_id;
END Delete_WPL;
PROCEDURE Delete_WFL
( p_api_version IN NUMBER,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
p_return_status OUT NOCOPY VARCHAR2,
p_fte_line_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_WFL';
delete from PSB_WS_FTE_LINES
where fte_line_id = p_fte_line_id;
END Delete_WFL;
PROCEDURE Delete_WEL
( p_api_version IN NUMBER,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
p_return_status OUT NOCOPY VARCHAR2,
p_element_line_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_WEL';
delete from PSB_WS_ELEMENT_LINES
where element_line_id = p_element_line_id;
END Delete_WEL;
PROCEDURE Delete_Summary_Lines
( 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
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Summary_Lines';
delete from PSB_WS_ACCOUNT_LINES
where template_id is not null
and account_line_id in
(select account_line_id
from PSB_WS_LINES
where worksheet_id = p_worksheet_id);
delete from PSB_WS_LINES a
where a.worksheet_id = p_worksheet_id
and not exists
(select 1
from PSB_WS_ACCOUNT_LINES b
where b.account_line_id = a.account_line_id);
END Delete_Summary_Lines;
select budget_group_id,
budget_calendar_id,
nvl(parameter_set_id, global_parameter_set_id) parameter_set_id,
nvl(constraint_set_id, global_constraint_set_id) constraint_set_id,
nvl(allocrule_set_id, global_allocrule_set_id) allocrule_set_id,
nvl(data_extract_id, global_data_extract_id) data_extract_id,
data_extract_name,
nvl(global_worksheet_id, worksheet_id) global_worksheet_id,
local_copy_flag,
global_worksheet_flag,
global_worksheet_option,
rounding_factor,
budget_version_id,
gl_budget_set_id,
gl_cutoff_period,
budget_by_position,
use_revised_element_rates,
num_proposed_years,
num_years_to_allocate,
stage_set_id,
current_stage_seq,
include_stat_balance,
include_translated_balance,
include_adjustment_periods,
create_non_pos_line_items,
apply_element_parameters,
apply_position_parameters,
create_positions,
create_summary_totals,
apply_constraints,
flex_mapping_set_id,
include_gl_commit_balance,
include_gl_oblig_balance,
include_gl_other_balance,
include_cbc_commit_balance,
include_cbc_oblig_balance,
include_cbc_budget_balance,
/* Bug No 4725091 */
include_gl_forward_balance
from PSB_WORKSHEETS_V
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,
nvl(business_group_id, root_business_group_id) business_group_id,
nvl(name, root_name) name,
root_budget_group,
ps_account_position_set_id psapsid,
nps_account_position_set_id npsapsid
from PSB_BUDGET_GROUPS_V
where budget_group_id = g_budget_group_id;
select currency_code,
chart_of_accounts_id,
name,
enable_budgetary_control_flag
from GL_SETS_OF_BOOKS
where set_of_books_id = g_set_of_books_id;
select service_package_id
from PSB_SERVICE_PACKAGES
where base_service_package = 'Y'
and global_worksheet_id = g_global_worksheet_id;
select psb_service_packages_s.nextval ServicePackageID
from dual;
select Min(sequence_number) sequence_number
from PSB_BUDGET_STAGES
where budget_stage_set_id = g_stage_set_id;
select name,
constraint_threshold
from PSB_CONSTRAINT_SETS_V
where constraint_set_id = g_constraint_set_id;
SELECT ps_account_position_set_id,
nps_account_position_set_id
INTO
g_ps_acct_pos_set_id,
g_nps_acct_pos_set_id
FROM PSB_BUDGET_GROUPS
WHERE budget_group_id = g_root_budget_group_id;
FND_MSG_PUB.Delete_Msg;
insert into PSB_SERVICE_PACKAGES
(service_package_id, global_worksheet_id,
base_service_package, name,
short_name, description, priority,
last_update_date, last_updated_by,
last_update_login, created_by, creation_date)
values (g_service_package_id, p_worksheet_id,
'Y', substr(l_name, 1, 30),
substr(l_name, 1, 15), l_name, null,
sysdate, l_userid,
l_loginid, l_userid, sysdate);
select data_extract_status
from PSB_DATA_EXTRACTS
where data_extract_id = p_data_extract_id;