[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select budget_group_id,
name,
parent_budget_group_id,
set_of_books_id,
business_group_id,
budget_group_category_set_id,
ps_account_position_set_id,
nps_account_position_set_id,
effective_start_date,
effective_end_date
from PSB_BUDGET_GROUPS
where budget_group_type = 'R'
start with budget_group_id = RootBudgetGroup_ID
connect by prior budget_group_id = parent_budget_group_id;
select account_position_set_id,
name,
effective_start_date,
effective_end_date
from psb_set_relations_v
where account_or_position_type = 'A'
and budget_group_id = BudgetGroup_ID;
select a.code_combination_id
from psb_budget_accounts a,
psb_set_relations c
where a.account_position_set_id = c.account_position_set_id
and ((((End_Date is not null)
and ((c.effective_start_date <= End_Date)
and (c.effective_end_date is null))
or ((c.effective_start_date between Start_Date and End_Date)
or (c.effective_end_date between Start_Date and End_Date)
or ((c.effective_start_date < Start_Date)
and (c.effective_end_date > End_Date)))))
or ((End_Date is null)
and (nvl(c.effective_end_date, Start_Date) >= Start_Date)))
/* for bug no 3824989 */
-- and c.account_position_set_id <> AccSet_ID
and c.budget_group_id <> BudgetGroup_ID
/*For Bug No : 2255402 Start*/
and exists
(select 1
from psb_budget_groups
where budget_group_id = c.budget_group_id
and (budget_group_id = RootBudgetGroup_ID
or root_budget_group_id = RootBudgetGroup_ID))
/*
and c.budget_group_id in
(select budget_group_id
from psb_budget_groups
where (budget_group_id = RootBudgetGroup_ID
or root_budget_group_id = RootBudgetGroup_ID))
*/
/*For Bug No : 2255402 End*/
and exists
(select 1
from psb_budget_accounts b
where a.code_combination_id = b.code_combination_id
and b.account_position_set_id = AccSet_ID);
select 'ACCOUNT RANGE OVERLAP'
from psb_account_position_set_lines cmp,
psb_account_position_set_lines lst,
psb_set_relations a
where (
NVL(cmp.SEGMENT30_LOW,Def_Seg) <= NVL(lst.SEGMENT30_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT30_HIGH,Def_Seg) >= NVL(lst.SEGMENT30_LOW,Def_Seg)
AND NVL(cmp.SEGMENT29_LOW,Def_Seg) <= NVL(lst.SEGMENT29_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT29_HIGH,Def_Seg) >= NVL(lst.SEGMENT29_LOW,Def_Seg)
AND NVL(cmp.SEGMENT28_LOW,Def_Seg) <= NVL(lst.SEGMENT28_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT28_HIGH,Def_Seg) >= NVL(lst.SEGMENT28_LOW,Def_Seg)
AND NVL(cmp.SEGMENT27_LOW,Def_Seg) <= NVL(lst.SEGMENT27_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT27_HIGH,Def_Seg) >= NVL(lst.SEGMENT27_LOW,Def_Seg)
AND NVL(cmp.SEGMENT26_LOW,Def_Seg) <= NVL(lst.SEGMENT26_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT26_HIGH,Def_Seg) >= NVL(lst.SEGMENT26_LOW,Def_Seg)
AND NVL(cmp.SEGMENT25_LOW,Def_Seg) <= NVL(lst.SEGMENT25_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT25_HIGH,Def_Seg) >= NVL(lst.SEGMENT25_LOW,Def_Seg)
AND NVL(cmp.SEGMENT24_LOW,Def_Seg) <= NVL(lst.SEGMENT24_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT24_HIGH,Def_Seg) >= NVL(lst.SEGMENT24_LOW,Def_Seg)
AND NVL(cmp.SEGMENT23_LOW,Def_Seg) <= NVL(lst.SEGMENT23_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT23_HIGH,Def_Seg) >= NVL(lst.SEGMENT23_LOW,Def_Seg)
AND NVL(cmp.SEGMENT22_LOW,Def_Seg) <= NVL(lst.SEGMENT22_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT22_HIGH,Def_Seg) >= NVL(lst.SEGMENT22_LOW,Def_Seg)
AND NVL(cmp.SEGMENT21_LOW,Def_Seg) <= NVL(lst.SEGMENT21_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT21_HIGH,Def_Seg) >= NVL(lst.SEGMENT21_LOW,Def_Seg)
AND NVL(cmp.SEGMENT20_LOW,Def_Seg) <= NVL(lst.SEGMENT20_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT20_HIGH,Def_Seg) >= NVL(lst.SEGMENT20_LOW,Def_Seg)
AND NVL(cmp.SEGMENT19_LOW,Def_Seg) <= NVL(lst.SEGMENT19_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT19_HIGH,Def_Seg) >= NVL(lst.SEGMENT19_LOW,Def_Seg)
AND NVL(cmp.SEGMENT18_LOW,Def_Seg) <= NVL(lst.SEGMENT18_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT18_HIGH,Def_Seg) >= NVL(lst.SEGMENT18_LOW,Def_Seg)
AND NVL(cmp.SEGMENT17_LOW,Def_Seg) <= NVL(lst.SEGMENT17_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT17_HIGH,Def_Seg) >= NVL(lst.SEGMENT17_LOW,Def_Seg)
AND NVL(cmp.SEGMENT16_LOW,Def_Seg) <= NVL(lst.SEGMENT16_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT16_HIGH,Def_Seg) >= NVL(lst.SEGMENT16_LOW,Def_Seg)
AND NVL(cmp.SEGMENT15_LOW,Def_Seg) <= NVL(lst.SEGMENT15_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT15_HIGH,Def_Seg) >= NVL(lst.SEGMENT15_LOW,Def_Seg)
AND NVL(cmp.SEGMENT14_LOW,Def_Seg) <= NVL(lst.SEGMENT14_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT14_HIGH,Def_Seg) >= NVL(lst.SEGMENT14_LOW,Def_Seg)
AND NVL(cmp.SEGMENT13_LOW,Def_Seg) <= NVL(lst.SEGMENT13_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT13_HIGH,Def_Seg) >= NVL(lst.SEGMENT13_LOW,Def_Seg)
AND NVL(cmp.SEGMENT12_LOW,Def_Seg) <= NVL(lst.SEGMENT12_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT12_HIGH,Def_Seg) >= NVL(lst.SEGMENT12_LOW,Def_Seg)
AND NVL(cmp.SEGMENT11_LOW,Def_Seg) <= NVL(lst.SEGMENT11_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT11_HIGH,Def_Seg) >= NVL(lst.SEGMENT11_LOW,Def_Seg)
AND NVL(cmp.SEGMENT10_LOW,Def_Seg) <= NVL(lst.SEGMENT10_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT10_HIGH,Def_Seg) >= NVL(lst.SEGMENT10_LOW,Def_Seg)
AND NVL(cmp.SEGMENT9_LOW,Def_Seg) <= NVL(lst.SEGMENT9_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT9_HIGH,Def_Seg) >= NVL(lst.SEGMENT9_LOW,Def_Seg)
AND NVL(cmp.SEGMENT8_LOW,Def_Seg) <= NVL(lst.SEGMENT8_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT8_HIGH,Def_Seg) >= NVL(lst.SEGMENT8_LOW,Def_Seg)
AND NVL(cmp.SEGMENT7_LOW,Def_Seg) <= NVL(lst.SEGMENT7_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT7_HIGH,Def_Seg) >= NVL(lst.SEGMENT7_LOW,Def_Seg)
AND NVL(cmp.SEGMENT6_LOW,Def_Seg) <= NVL(lst.SEGMENT6_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT6_HIGH,Def_Seg) >= NVL(lst.SEGMENT6_LOW,Def_Seg)
AND NVL(cmp.SEGMENT5_LOW,Def_Seg) <= NVL(lst.SEGMENT5_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT5_HIGH,Def_Seg) >= NVL(lst.SEGMENT5_LOW,Def_Seg)
AND NVL(cmp.SEGMENT4_LOW,Def_Seg) <= NVL(lst.SEGMENT4_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT4_HIGH,Def_Seg) >= NVL(lst.SEGMENT4_LOW,Def_Seg)
AND NVL(cmp.SEGMENT3_LOW,Def_Seg) <= NVL(lst.SEGMENT3_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT3_HIGH,Def_Seg) >= NVL(lst.SEGMENT3_LOW,Def_Seg)
AND NVL(cmp.SEGMENT2_LOW,Def_Seg) <= NVL(lst.SEGMENT2_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT2_HIGH,Def_Seg) >= NVL(lst.SEGMENT2_LOW,Def_Seg)
AND NVL(cmp.SEGMENT1_LOW,Def_Seg) <= NVL(lst.SEGMENT1_HIGH,Def_Seg)
AND NVL(cmp.SEGMENT1_HIGH,Def_Seg) >= NVL(lst.SEGMENT1_LOW,Def_Seg)
)
and lst.account_position_set_id = AccSet_ID
and lst.include_or_exclude_type = Inc_Type
and cmp.account_position_set_id = a.account_position_set_id
and cmp.include_or_exclude_type = Inc_Type
and ((((End_Date is not null)
and ((a.effective_start_date <= End_Date)
and (a.effective_end_date is null))
or ((a.effective_start_date between Start_Date and End_Date)
or (a.effective_end_date between Start_Date and End_Date)
or ((a.effective_start_date < Start_Date)
and (a.effective_end_date > End_Date)))))
or ((End_Date is null)
and (nvl(a.effective_end_date, Start_Date) >= Start_Date)))
and a.account_position_set_id <> AccSet_ID
and exists
(select 1
from psb_budget_groups
where budget_group_id = a.budget_group_id
and (budget_group_id = RootBudgetGroup_ID
or root_budget_group_id = RootBudgetGroup_ID));
(select budget_group_id
d from psb_budget_groups
where (budget_group_id = RootBudgetGroup_ID
or root_budget_group_id = RootBudgetGroup_ID));
select 'Personnel Services and Non-Personnel Services Account Sets Overlap'
from dual
where exists
(select 1
from psb_budget_accounts a,
psb_budget_accounts b
where a.code_combination_id = b.code_combination_id
and a.account_position_set_id = nps_AccSet
and b.account_position_set_id = ps_AccSet);
select 'Personnel Services and Non-Personnel Services Account Sets Overlap'
from psb_account_position_set_lines ps,
psb_account_position_set_lines nps
where (
NVL(ps.SEGMENT30_LOW,'X') <= NVL(nps.SEGMENT30_HIGH,'X')
AND NVL(ps.SEGMENT30_HIGH,'X') >= NVL(nps.SEGMENT30_LOW,'X')
AND NVL(ps.SEGMENT29_LOW,'X') <= NVL(nps.SEGMENT29_HIGH,'X')
AND NVL(ps.SEGMENT29_HIGH,'X') >= NVL(nps.SEGMENT29_LOW,'X')
AND NVL(ps.SEGMENT28_LOW,'X') <= NVL(nps.SEGMENT28_HIGH,'X')
AND NVL(ps.SEGMENT28_HIGH,'X') >= NVL(nps.SEGMENT28_LOW,'X')
AND NVL(ps.SEGMENT27_LOW,'X') <= NVL(nps.SEGMENT27_HIGH,'X')
AND NVL(ps.SEGMENT27_HIGH,'X') >= NVL(nps.SEGMENT27_LOW,'X')
AND NVL(ps.SEGMENT26_LOW,'X') <= NVL(nps.SEGMENT26_HIGH,'X')
AND NVL(ps.SEGMENT26_HIGH,'X') >= NVL(nps.SEGMENT26_LOW,'X')
AND NVL(ps.SEGMENT25_LOW,'X') <= NVL(nps.SEGMENT25_HIGH,'X')
AND NVL(ps.SEGMENT25_HIGH,'X') >= NVL(nps.SEGMENT25_LOW,'X')
AND NVL(ps.SEGMENT24_LOW,'X') <= NVL(nps.SEGMENT24_HIGH,'X')
AND NVL(ps.SEGMENT24_HIGH,'X') >= NVL(nps.SEGMENT24_LOW,'X')
AND NVL(ps.SEGMENT23_LOW,'X') <= NVL(nps.SEGMENT23_HIGH,'X')
AND NVL(ps.SEGMENT23_HIGH,'X') >= NVL(nps.SEGMENT23_LOW,'X')
AND NVL(ps.SEGMENT22_LOW,'X') <= NVL(nps.SEGMENT22_HIGH,'X')
AND NVL(ps.SEGMENT22_HIGH,'X') >= NVL(nps.SEGMENT22_LOW,'X')
AND NVL(ps.SEGMENT21_LOW,'X') <= NVL(nps.SEGMENT21_HIGH,'X')
AND NVL(ps.SEGMENT21_HIGH,'X') >= NVL(nps.SEGMENT21_LOW,'X')
AND NVL(ps.SEGMENT20_LOW,'X') <= NVL(nps.SEGMENT20_HIGH,'X')
AND NVL(ps.SEGMENT20_HIGH,'X') >= NVL(nps.SEGMENT20_LOW,'X')
AND NVL(ps.SEGMENT19_LOW,'X') <= NVL(nps.SEGMENT19_HIGH,'X')
AND NVL(ps.SEGMENT19_HIGH,'X') >= NVL(nps.SEGMENT19_LOW,'X')
AND NVL(ps.SEGMENT18_LOW,'X') <= NVL(nps.SEGMENT18_HIGH,'X')
AND NVL(ps.SEGMENT18_HIGH,'X') >= NVL(nps.SEGMENT18_LOW,'X')
AND NVL(ps.SEGMENT17_LOW,'X') <= NVL(nps.SEGMENT17_HIGH,'X')
AND NVL(ps.SEGMENT17_HIGH,'X') >= NVL(nps.SEGMENT17_LOW,'X')
AND NVL(ps.SEGMENT16_LOW,'X') <= NVL(nps.SEGMENT16_HIGH,'X')
AND NVL(ps.SEGMENT16_HIGH,'X') >= NVL(nps.SEGMENT16_LOW,'X')
AND NVL(ps.SEGMENT15_LOW,'X') <= NVL(nps.SEGMENT15_HIGH,'X')
AND NVL(ps.SEGMENT15_HIGH,'X') >= NVL(nps.SEGMENT15_LOW,'X')
AND NVL(ps.SEGMENT14_LOW,'X') <= NVL(nps.SEGMENT14_HIGH,'X')
AND NVL(ps.SEGMENT14_HIGH,'X') >= NVL(nps.SEGMENT14_LOW,'X')
AND NVL(ps.SEGMENT13_LOW,'X') <= NVL(nps.SEGMENT13_HIGH,'X')
AND NVL(ps.SEGMENT13_HIGH,'X') >= NVL(nps.SEGMENT13_LOW,'X')
AND NVL(ps.SEGMENT12_LOW,'X') <= NVL(nps.SEGMENT12_HIGH,'X')
AND NVL(ps.SEGMENT12_HIGH,'X') >= NVL(nps.SEGMENT12_LOW,'X')
AND NVL(ps.SEGMENT11_LOW,'X') <= NVL(nps.SEGMENT11_HIGH,'X')
AND NVL(ps.SEGMENT11_HIGH,'X') >= NVL(nps.SEGMENT11_LOW,'X')
AND NVL(ps.SEGMENT10_LOW,'X') <= NVL(nps.SEGMENT10_HIGH,'X')
AND NVL(ps.SEGMENT10_HIGH,'X') >= NVL(nps.SEGMENT10_LOW,'X')
AND NVL(ps.SEGMENT9_LOW,'X') <= NVL(nps.SEGMENT9_HIGH,'X')
AND NVL(ps.SEGMENT9_HIGH,'X') >= NVL(nps.SEGMENT9_LOW,'X')
AND NVL(ps.SEGMENT8_LOW,'X') <= NVL(nps.SEGMENT8_HIGH,'X')
AND NVL(ps.SEGMENT8_HIGH,'X') >= NVL(nps.SEGMENT18_LOW,'X')
AND NVL(ps.SEGMENT7_LOW,'X') <= NVL(nps.SEGMENT7_HIGH,'X')
AND NVL(ps.SEGMENT7_HIGH,'X') >= NVL(nps.SEGMENT7_LOW,'X')
AND NVL(ps.SEGMENT6_LOW,'X') <= NVL(nps.SEGMENT6_HIGH,'X')
AND NVL(ps.SEGMENT6_HIGH,'X') >= NVL(nps.SEGMENT6_LOW,'X')
AND NVL(ps.SEGMENT5_LOW,'X') <= NVL(nps.SEGMENT5_HIGH,'X')
AND NVL(ps.SEGMENT5_HIGH,'X') >= NVL(nps.SEGMENT5_LOW,'X')
AND NVL(ps.SEGMENT4_LOW,'X') <= NVL(nps.SEGMENT4_HIGH,'X')
AND NVL(ps.SEGMENT4_HIGH,'X') >= NVL(nps.SEGMENT4_LOW,'X')
AND NVL(ps.SEGMENT3_LOW,'X') <= NVL(nps.SEGMENT3_HIGH,'X')
AND NVL(ps.SEGMENT3_HIGH,'X') >= NVL(nps.SEGMENT3_LOW,'X')
AND NVL(ps.SEGMENT2_LOW,'X') <= NVL(nps.SEGMENT2_HIGH,'X')
AND NVL(ps.SEGMENT2_HIGH,'X') >= NVL(nps.SEGMENT2_LOW,'X')
AND NVL(ps.SEGMENT1_LOW,'X') <= NVL(nps.SEGMENT1_HIGH,'X')
AND NVL(ps.SEGMENT1_HIGH,'X') >= NVL(nps.SEGMENT1_LOW,'X')
)
and ps.account_position_set_id = l_ps_AccSet
and ps.include_or_exclude_type = 'I'
and nps.account_position_set_id = l_nps_AccSet
and nps.include_or_exclude_type = 'I';
select nvl(freeze_hierarchy_flag, root_freeze_hierarchy_flag) freeze_hierarchy_flag
from PSB_BUDGET_GROUPS_V
where budget_group_id = p_budget_group_id;
select 'Account Sets Overlap'
from psb_budget_accounts a,
psb_budget_accounts b,
psb_set_relations_v c
where a.code_combination_id = b.code_combination_id
and b.account_position_set_id = AccSet_ID
and a.account_position_set_id = c.account_position_set_id
and ((((End_Date is not null)
and ((c.effective_start_date <= End_Date)
and (c.effective_end_date is null))
or ((c.effective_start_date between Start_Date and End_Date)
or (c.effective_end_date between Start_Date and End_Date)
or ((c.effective_start_date < Start_Date)
and (c.effective_end_date > End_Date)))))
or ((End_Date is null)
and (nvl(c.effective_end_date, Start_Date) >= Start_Date)))
and c.account_position_set_id <> AccSet_ID
and c.account_or_position_type = 'A'
and c.budget_group_id = p_budget_group_id;
select nvl(chart_of_accounts_id, root_chart_of_accounts_id) flex_code
from PSB_BUDGET_GROUPS_V
where budget_group_id = p_budget_group_id;
update PSB_BUDGET_GROUPS
set freeze_hierarchy_flag = 'Y'
where budget_group_id = p_budget_group_id;
delete from PSB_ERROR_MESSAGES
where source_process = 'VALIDATE_BUDGET_HIERARCHY'
and process_id = p_budget_group_id;
PROCEDURE INSERT_ROW (
p_api_version in number,
p_init_msg_list in varchar2 := fnd_api.g_false,
p_commit in varchar2 := fnd_api.g_false,
p_validation_level in number := fnd_api.g_valid_level_full,
p_return_status OUT NOCOPY varchar2,
p_msg_count OUT NOCOPY number,
p_msg_data OUT NOCOPY varchar2,
p_rowid in OUT NOCOPY varchar2,
p_budget_group_id in number,
p_name in varchar2,
p_short_name in varchar2,
p_root_budget_group in varchar2,
p_parent_budget_group_id in number,
p_root_budget_group_id in number,
p_ps_account_position_set_id in number,
p_nps_account_position_set_id in number,
p_budget_group_category_set_id in number,
p_effective_start_date in date,
p_effective_end_date in date,
p_freeze_hierarchy_flag in varchar2,
p_description in varchar2,
p_set_of_books_id in number,
p_business_group_id in number,
p_num_proposed_years in number,
p_narrative_description in varchar2,
p_budget_group_type in varchar2,
p_organization_id in number ,
p_request_id in number,
p_segment1_type in number,
p_segment2_type in number,
p_segment3_type in number,
p_segment4_type in number,
p_segment5_type in number,
p_segment6_type in number,
p_segment7_type in number,
p_segment8_type in number,
p_segment9_type in number,
p_segment10_type in number,
p_segment11_type in number,
p_segment12_type in number,
p_segment13_type in number,
p_segment14_type in number,
p_segment15_type in number,
p_segment16_type in number,
p_segment17_type in number,
p_segment18_type in number,
p_segment19_type in number,
p_segment20_type in number,
p_segment21_type in number,
p_segment22_type in number,
p_segment23_type in number,
p_segment24_type in number,
p_segment25_type in number,
p_segment26_type in number,
p_segment27_type in number,
p_segment28_type in number,
p_segment29_type in number,
p_segment30_type in number,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_context in varchar2,
p_mode in varchar2 := 'R'
) AS
cursor C is select ROWID from PSB_BUDGET_GROUPS
where BUDGET_GROUP_ID = P_BUDGET_GROUP_ID;
P_LAST_UPDATE_DATE DATE;
P_LAST_UPDATED_BY NUMBER;
P_LAST_UPDATE_LOGIN NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row' ;
SAVEPOINT Insert_Row ;
P_LAST_UPDATE_DATE := SYSDATE;
P_LAST_UPDATED_BY := 1;
P_LAST_UPDATE_LOGIN := 0;
P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
if P_LAST_UPDATED_BY is NULL then
P_LAST_UPDATED_BY := -1;
P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
if P_LAST_UPDATE_LOGIN is NULL then
P_LAST_UPDATE_LOGIN := -1;
insert into PSB_BUDGET_GROUPS (
budget_group_id,
name,
short_name,
root_budget_group,
parent_budget_group_id,
root_budget_group_id,
ps_account_position_set_id,
nps_account_position_set_id,
budget_group_category_set_id,
effective_start_date,
effective_end_date,
freeze_hierarchy_flag,
description,
set_of_books_id,
business_group_id,
num_proposed_years,
narrative_description,
budget_group_type,
organization_id,
request_id,
segment1_type,
segment2_type,
segment3_type,
segment4_type,
segment5_type,
segment6_type,
segment7_type,
segment8_type,
segment9_type,
segment10_type,
segment11_type,
segment12_type,
segment13_type,
segment14_type,
segment15_type,
segment16_type,
segment17_type,
segment18_type,
segment19_type,
segment20_type,
segment21_type,
segment22_type,
segment23_type,
segment24_type,
segment25_type,
segment26_type,
segment27_type,
segment28_type,
segment29_type,
segment30_type,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
context,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
) values (
p_budget_group_id,
p_name,
p_short_name,
p_root_budget_group,
p_parent_budget_group_id,
p_root_budget_group_id,
p_ps_account_position_set_id,
p_nps_account_position_set_id,
p_budget_group_category_set_id,
p_effective_start_date,
p_effective_end_date,
p_freeze_hierarchy_flag,
p_description,
p_set_of_books_id,
p_business_group_id,
p_num_proposed_years,
p_narrative_description,
p_budget_group_type,
p_organization_id,
p_request_id,
p_segment1_type,
p_segment2_type,
p_segment3_type,
p_segment4_type,
p_segment5_type,
p_segment6_type,
p_segment7_type,
p_segment8_type,
p_segment9_type,
p_segment10_type,
p_segment11_type,
p_segment12_type,
p_segment13_type,
p_segment14_type,
p_segment15_type,
p_segment16_type,
p_segment17_type,
p_segment18_type,
p_segment19_type,
p_segment20_type,
p_segment21_type,
p_segment22_type,
p_segment23_type,
p_segment24_type,
p_segment25_type,
p_segment26_type,
p_segment27_type,
p_segment28_type,
p_segment29_type,
p_segment30_type,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_context,
p_last_update_date,
p_last_updated_by,
p_last_update_date,
p_last_updated_by,
p_last_update_login
);
rollback to INSERT_ROW ;
rollback to INSERT_ROW ;
rollback to INSERT_ROW ;
END INSERT_ROW;
cursor c1 is select
name,
short_name,
root_budget_group,
parent_budget_group_id,
root_budget_group_id,
ps_account_position_set_id,
nps_account_position_set_id,
budget_group_category_set_id,
effective_start_date,
effective_end_date,
freeze_hierarchy_flag,
description,
set_of_books_id,
business_group_id,
num_proposed_years,
narrative_description,
budget_group_type,
organization_id,
request_id,
segment1_type,
segment2_type,
segment3_type,
segment4_type,
segment5_type,
segment6_type,
segment7_type,
segment8_type,
segment9_type,
segment10_type,
segment11_type,
segment12_type,
segment13_type,
segment14_type,
segment15_type,
segment16_type,
segment17_type,
segment18_type,
segment19_type,
segment20_type,
segment21_type,
segment22_type,
segment23_type,
segment24_type,
segment25_type,
segment26_type,
segment27_type,
segment28_type,
segment29_type,
segment30_type,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
context
from PSB_BUDGET_GROUPS
where BUDGET_GROUP_ID = P_BUDGET_GROUP_ID
for update of BUDGET_GROUP_ID nowait;
PROCEDURE UPDATE_ROW (
p_api_version in number,
p_init_msg_list in varchar2 := fnd_api.g_false,
p_commit in varchar2 := fnd_api.g_false,
p_validation_level in number := fnd_api.g_valid_level_full,
p_return_status OUT NOCOPY varchar2,
p_msg_count OUT NOCOPY number,
p_msg_data OUT NOCOPY varchar2,
p_budget_group_id in number,
p_name in varchar2,
p_short_name in varchar2,
p_root_budget_group in varchar2,
p_parent_budget_group_id in number,
p_root_budget_group_id in number,
p_ps_account_position_set_id in number,
p_nps_account_position_set_id in number,
p_budget_group_category_set_id in number,
p_effective_start_date in date,
p_effective_end_date in date,
p_freeze_hierarchy_flag in varchar2,
p_description in varchar2,
p_set_of_books_id in number,
p_business_group_id in number,
p_num_proposed_years in number,
p_narrative_description in varchar2,
p_budget_group_type in varchar2,
p_organization_id in number ,
p_request_id in number,
p_segment1_type in number,
p_segment2_type in number,
p_segment3_type in number,
p_segment4_type in number,
p_segment5_type in number,
p_segment6_type in number,
p_segment7_type in number,
p_segment8_type in number,
p_segment9_type in number,
p_segment10_type in number,
p_segment11_type in number,
p_segment12_type in number,
p_segment13_type in number,
p_segment14_type in number,
p_segment15_type in number,
p_segment16_type in number,
p_segment17_type in number,
p_segment18_type in number,
p_segment19_type in number,
p_segment20_type in number,
p_segment21_type in number,
p_segment22_type in number,
p_segment23_type in number,
p_segment24_type in number,
p_segment25_type in number,
p_segment26_type in number,
p_segment27_type in number,
p_segment28_type in number,
p_segment29_type in number,
p_segment30_type in number,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_context in varchar2,
p_mode in varchar2 := 'R'
) AS
P_LAST_UPDATE_DATE DATE;
P_LAST_UPDATED_BY NUMBER;
P_LAST_UPDATE_LOGIN NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'Update Row';
SAVEPOINT Update_Row ;
P_LAST_UPDATE_DATE := SYSDATE;
P_LAST_UPDATED_BY := 1;
P_LAST_UPDATE_LOGIN := 0;
P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
if P_LAST_UPDATED_BY is NULL then
P_LAST_UPDATED_BY := -1;
P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
if P_LAST_UPDATE_LOGIN is NULL then
P_LAST_UPDATE_LOGIN := -1;
update PSB_BUDGET_GROUPS set
name = p_name,
short_name = p_short_name,
root_budget_group = p_root_budget_group,
parent_budget_group_id = p_parent_budget_group_id,
root_budget_group_id = p_root_budget_group_id,
ps_account_position_set_id = p_ps_account_position_set_id,
nps_account_position_set_id = p_nps_account_position_set_id,
budget_group_category_set_id = p_budget_group_category_set_id,
effective_start_date = p_effective_start_date,
effective_end_date = p_effective_end_date,
freeze_hierarchy_flag = p_freeze_hierarchy_flag,
description = p_description,
set_of_books_id = p_set_of_books_id,
business_group_id = p_business_group_id,
num_proposed_years = p_num_proposed_years,
narrative_description = p_narrative_description,
budget_group_type = p_budget_group_type,
organization_id = p_organization_id ,
request_id = p_request_id,
segment1_type = p_segment1_type,
segment2_type = p_segment2_type,
segment3_type = p_segment3_type,
segment4_type = p_segment4_type,
segment5_type = p_segment5_type,
segment6_type = p_segment6_type,
segment7_type = p_segment7_type,
segment8_type = p_segment8_type,
segment9_type = p_segment9_type,
segment10_type = p_segment10_type,
segment11_type = p_segment11_type,
segment12_type = p_segment12_type,
segment13_type = p_segment13_type,
segment14_type = p_segment14_type,
segment15_type = p_segment15_type,
segment16_type = p_segment16_type,
segment17_type = p_segment17_type,
segment18_type = p_segment18_type,
segment19_type = p_segment19_type,
segment20_type = p_segment20_type,
segment21_type = p_segment21_type,
segment22_type = p_segment22_type,
segment23_type = p_segment23_type,
segment24_type = p_segment24_type,
segment25_type = p_segment25_type,
segment26_type = p_segment26_type,
segment27_type = p_segment27_type,
segment28_type = p_segment28_type,
segment29_type = p_segment29_type,
segment30_type = p_segment30_type,
attribute1 = p_attribute1,
attribute2 = p_attribute2,
attribute3 = p_attribute3,
attribute4 = p_attribute4,
attribute5 = p_attribute5,
attribute6 = p_attribute6,
attribute7 = p_attribute7,
attribute8 = p_attribute8,
attribute9 = p_attribute9,
attribute10 = p_attribute10,
context = p_context,
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login
where BUDGET_GROUP_ID = P_BUDGET_GROUP_ID
;
rollback to UPDATE_ROW ;
rollback to UPDATE_ROW ;
rollback to UPDATE_ROW ;
END UPDATE_ROW;
cursor c1 is select rowid from PSB_BUDGET_GROUPS
where BUDGET_GROUP_ID = P_BUDGET_GROUP_ID
;
INSERT_ROW (
p_api_version,
p_init_msg_list,
p_commit,
p_validation_level,
p_return_status,
p_msg_count,
p_msg_data,
p_rowid,
p_budget_group_id,
p_name,
p_short_name,
p_root_budget_group,
p_parent_budget_group_id,
p_root_budget_group_id,
p_ps_account_position_set_id,
p_nps_account_position_set_id,
p_budget_group_category_set_id,
p_effective_start_date,
p_effective_end_date,
p_freeze_hierarchy_flag,
p_description,
p_set_of_books_id,
p_business_group_id,
p_num_proposed_years,
p_narrative_description,
p_budget_group_type,
p_organization_id,
p_request_id,
p_segment1_type,
p_segment2_type,
p_segment3_type,
p_segment4_type,
p_segment5_type,
p_segment6_type,
p_segment7_type,
p_segment8_type,
p_segment9_type,
p_segment10_type,
p_segment11_type,
p_segment12_type,
p_segment13_type,
p_segment14_type,
p_segment15_type,
p_segment16_type,
p_segment17_type,
p_segment18_type,
p_segment19_type,
p_segment20_type,
p_segment21_type,
p_segment22_type,
p_segment23_type,
p_segment24_type,
p_segment25_type,
p_segment26_type,
p_segment27_type,
p_segment28_type,
p_segment29_type,
p_segment30_type,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_context,
p_mode);
UPDATE_ROW (
p_api_version,
p_init_msg_list,
p_commit,
p_validation_level,
p_return_status,
p_msg_count,
p_msg_data,
p_budget_group_id,
p_name,
p_short_name,
p_root_budget_group,
p_parent_budget_group_id,
p_root_budget_group_id,
p_ps_account_position_set_id,
p_nps_account_position_set_id,
p_budget_group_category_set_id,
p_effective_start_date,
p_effective_end_date,
p_freeze_hierarchy_flag,
p_description,
p_set_of_books_id,
p_business_group_id,
p_num_proposed_years,
p_narrative_description,
p_budget_group_type,
p_organization_id,
p_request_id,
p_segment1_type,
p_segment2_type,
p_segment3_type,
p_segment4_type,
p_segment5_type,
p_segment6_type,
p_segment7_type,
p_segment8_type,
p_segment9_type,
p_segment10_type,
p_segment11_type,
p_segment12_type,
p_segment13_type,
p_segment14_type,
p_segment15_type,
p_segment16_type,
p_segment17_type,
p_segment18_type,
p_segment19_type,
p_segment20_type,
p_segment21_type,
p_segment22_type,
p_segment23_type,
p_segment24_type,
p_segment25_type,
p_segment26_type,
p_segment27_type,
p_segment28_type,
p_segment29_type,
p_segment30_type,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_context,
p_mode);
PROCEDURE DELETE_ROW (
p_api_version in number,
p_init_msg_list in varchar2 := fnd_api.g_false,
p_commit in varchar2 := fnd_api.g_false,
p_validation_level in number := fnd_api.g_valid_level_full,
p_return_status OUT NOCOPY varchar2,
p_msg_count OUT NOCOPY number,
p_msg_data OUT NOCOPY varchar2,
p_budget_group_id in number,
p_delete OUT NOCOPY varchar2
) AS
cursor C1 is
select budget_group_id,
short_name
from psb_budget_groups
where budget_group_type = 'R'
start with budget_group_id = p_budget_group_id
connect by prior budget_group_id = parent_budget_group_id;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
SELECT 'PSB_POSITION_ACCOUNTS'
FROM dual
WHERE exists
(SELECT 1
FROM PSB_POSITION_ACCOUNTS
WHERE budget_group_id = l_budget_group_id
);
SELECT 'PSB_ACCOUNT_POSITION_SETS'
FROM dual
WHERE exists
(SELECT 1
FROM PSB_ACCOUNT_POSITION_SETS
WHERE budget_group_id = l_budget_group_id
);
SELECT 'PSB_BUDGET_REVISIONS'
FROM dual
WHERE exists
(SELECT 1
FROM PSB_BUDGET_REVISIONS
WHERE budget_group_id = l_budget_group_id
);
SELECT 'PSB_BUDGET_REVISION_POSITIONS'
FROM dual
WHERE exists
(SELECT 1
FROM PSB_BUDGET_REVISION_POSITIONS
WHERE budget_group_id = l_budget_group_id
);
SELECT 'PSB_BUDGET_REVISION_ACCOUNTS'
FROM dual
WHERE exists
(SELECT 1
FROM PSB_BUDGET_REVISION_ACCOUNTS
WHERE budget_group_id = l_budget_group_id
);
SELECT 'PSB_BUDGET_WORKFLOW_RULES'
FROM dual
WHERE exists
(SELECT 1
FROM PSB_BUDGET_WORKFLOW_RULES
WHERE budget_group_id = l_budget_group_id
);
SELECT 'PSB_DATA_EXTRACTS'
FROM dual
WHERE exists
(SELECT 1
FROM PSB_DATA_EXTRACTS
WHERE budget_group_id = l_budget_group_id
);
SELECT 'PSB_WS_POSITION_LINES'
FROM dual
WHERE exists
(SELECT 1
FROM PSB_WS_POSITION_LINES
WHERE budget_group_id = l_budget_group_id
);
SELECT 'PSB_POSITIONS'
FROM dual
WHERE exists
(SELECT 1
FROM PSB_POSITIONS
WHERE budget_group_id = l_budget_group_id
);
SELECT 'PSB_ENTITY_SET'
FROM dual
WHERE exists
(SELECT 1
FROM PSB_ENTITY_SET
WHERE budget_group_id = l_budget_group_id
);
SELECT 'PSB_ENTITY'
FROM dual
WHERE exists
(SELECT 1
FROM PSB_ENTITY
WHERE budget_group_id = l_budget_group_id
);
SELECT 'PSB_WS_DISTRIBUTION_RULE_LINES'
FROM dual
WHERE exists
(SELECT 1
FROM PSB_WS_DISTRIBUTION_RULE_LINES
WHERE budget_group_id = l_budget_group_id
);
SELECT 'PSB_WS_DISTRIBUTION_RULES'
FROM dual
WHERE exists
(SELECT 1
FROM PSB_WS_DISTRIBUTION_RULES
WHERE budget_group_id = l_budget_group_id
);
SELECT 'PSB_WS_LINE_BALANCES_I'
FROM dual
WHERE exists
(SELECT 1
FROM PSB_WS_LINE_BALANCES_I
WHERE budget_group_id = l_budget_group_id
);
SELECT 'PSB_WORKSHEETS'
FROM dual
WHERE exists
(SELECT 1
FROM PSB_WORKSHEETS
WHERE budget_group_id = l_budget_group_id
);
SELECT 'PSB_WS_ACCOUNT_LINES'
FROM dual
WHERE exists
(SELECT 1
FROM PSB_WS_ACCOUNT_LINES
WHERE budget_group_id = l_budget_group_id
);
SAVEPOINT Delete_Row;
rollback to Delete_Row;
add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
p_delete := 'NO_DELETE';
rollback to Delete_Row;
add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
p_delete := 'NO_DELETE';
rollback to Delete_Row;
add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
p_delete := 'NO_DELETE';
rollback to Delete_Row;
add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
p_delete := 'NO_DELETE';
rollback to Delete_Row;
add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
p_delete := 'NO_DELETE';
rollback to Delete_Row;
add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
p_delete := 'NO_DELETE';
rollback to Delete_Row;
add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
p_delete := 'NO_DELETE';
rollback to Delete_Row;
add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
p_delete := 'NO_DELETE';
rollback to Delete_Row;
add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
p_delete := 'NO_DELETE';
rollback to Delete_Row;
add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
p_delete := 'NO_DELETE';
rollback to Delete_Row;
add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
p_delete := 'NO_DELETE';
rollback to Delete_Row;
add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
p_delete := 'NO_DELETE';
rollback to Delete_Row;
add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
p_delete := 'NO_DELETE';
rollback to Delete_Row;
add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
p_delete := 'NO_DELETE';
rollback to Delete_Row;
add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
p_delete := 'NO_DELETE';
rollback to Delete_Row;
add_message('PSB', 'PSB_BG_CANNOT_BE_DELETED');
p_delete := 'NO_DELETE';
delete psb_budget_groups where budget_group_id = c1rec.budget_group_id;
delete psb_budget_group_resp where budget_group_id = c1rec.budget_group_id;
delete psb_set_relations where budget_group_id = c1rec.budget_group_id;
delete psb_budget_group_categories where budget_group_id = c1rec.budget_group_id;
delete psb_budget_groups where budget_group_id = p_budget_group_id;
delete psb_budget_group_resp where budget_group_id = p_budget_group_id;
delete psb_set_relations where budget_group_id = p_budget_group_id;
delete psb_budget_group_categories where budget_group_id = p_budget_group_id;
p_delete := 'DELETE';
rollback to Delete_Row;
rollback to Delete_Row;
rollback to Delete_Row;
END Delete_Row;
PROCEDURE Delete_Review_Group (
p_api_version in number,
p_init_msg_list in varchar2 := fnd_api.g_false,
p_commit in varchar2 := fnd_api.g_false,
p_validation_level in number := fnd_api.g_valid_level_full,
p_return_status OUT NOCOPY varchar2,
p_msg_count OUT NOCOPY number,
p_msg_data OUT NOCOPY varchar2,
p_budget_group_id in number
) AS
--
l_api_name CONSTANT VARCHAR2(30) := 'Delete Review Group' ;
SAVEPOINT Delete_Review_Group ;
delete from PSB_BUDGET_GROUPS
where BUDGET_GROUP_ID = P_BUDGET_GROUP_ID;
delete psb_budget_group_resp where budget_group_id = P_BUDGET_GROUP_ID;
rollback to Delete_Review_Group ;
rollback to Delete_Review_Group ;
rollback to DELETE_ROW ;
END Delete_Review_Group;
select short_name
from psb_budget_groups
where budget_group_id = p_curr_budget_group_id ;
select responsibility_id,responsibility_type
from psb_budget_group_resp
where budget_group_id = p_src_budget_group_id
and responsibility_type = 'R';
select wf_role_name,wf_role_orig_system,wf_role_orig_system_id,
responsibility_type from
psb_budget_group_resp
where budget_group_id = p_src_budget_group_id
and responsibility_type = 'N';
select stage_id
from psb_budget_group_categories
where budget_group_id = p_src_budget_group_id;
select name,
set_relation_id,
set_of_books_id,
data_extract_id,
global_or_local_type,
account_or_position_type ,
business_group_id ,
effective_start_date,
effective_end_date,
account_position_set_id,
attribute_selection_type,
use_in_budget_group_flag
from psb_set_relations_v
where budget_group_id = p_src_budget_group_id;
select
LINE_SEQUENCE_ID,
ACCOUNT_POSITION_SET_ID,
DESCRIPTION,
BUSINESS_GROUP_ID,
ATTRIBUTE_ID,
INCLUDE_OR_EXCLUDE_TYPE,
SEGMENT1_LOW ,
SEGMENT2_LOW ,
SEGMENT3_LOW ,
SEGMENT4_LOW ,
SEGMENT5_LOW ,
SEGMENT6_LOW ,
SEGMENT7_LOW ,
SEGMENT8_LOW ,
SEGMENT9_LOW ,
SEGMENT10_LOW ,
SEGMENT11_LOW ,
SEGMENT12_LOW ,
SEGMENT13_LOW ,
SEGMENT14_LOW ,
SEGMENT15_LOW ,
SEGMENT16_LOW ,
SEGMENT17_LOW ,
SEGMENT18_LOW ,
SEGMENT19_LOW ,
SEGMENT20_LOW ,
SEGMENT21_LOW ,
SEGMENT22_LOW ,
SEGMENT23_LOW ,
SEGMENT24_LOW ,
SEGMENT25_LOW ,
SEGMENT26_LOW ,
SEGMENT27_LOW ,
SEGMENT28_LOW ,
SEGMENT29_LOW ,
SEGMENT30_LOW ,
SEGMENT1_HIGH ,
SEGMENT2_HIGH ,
SEGMENT3_HIGH ,
SEGMENT4_HIGH ,
SEGMENT5_HIGH ,
SEGMENT6_HIGH ,
SEGMENT7_HIGH ,
SEGMENT8_HIGH ,
SEGMENT9_HIGH ,
SEGMENT10_HIGH ,
SEGMENT11_HIGH ,
SEGMENT12_HIGH ,
SEGMENT13_HIGH ,
SEGMENT14_HIGH ,
SEGMENT15_HIGH ,
SEGMENT16_HIGH ,
SEGMENT17_HIGH ,
SEGMENT18_HIGH ,
SEGMENT19_HIGH ,
SEGMENT20_HIGH ,
SEGMENT21_HIGH ,
SEGMENT22_HIGH ,
SEGMENT23_HIGH ,
SEGMENT24_HIGH ,
SEGMENT25_HIGH ,
SEGMENT26_HIGH ,
SEGMENT27_HIGH ,
SEGMENT28_HIGH ,
SEGMENT29_HIGH ,
SEGMENT30_HIGH ,
CONTEXT ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10
from PSB_ACCT_POSITION_SET_LINES_V
where account_position_set_id = Bgsr_Rec.account_position_set_id;
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login :=FND_GLOBAL.LOGIN_ID;
select PSB_BUDGET_GROUP_RESP_S.NEXTVAL
Into l_budget_group_resp_id from DUAL ;
Insert into psb_budget_group_resp
(budget_group_resp_id,
budget_group_id,
responsibility_id,
responsibility_type,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
values
(
l_budget_group_resp_id,
p_curr_budget_group_id,
Bgr_Rec.responsibility_id,
Bgr_Rec.responsibility_type,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_created_by,
l_creation_date);
select PSB_BUDGET_GROUP_RESP_S.NEXTVAL
Into l_budget_group_resp_id from DUAL ;
Insert into psb_budget_group_resp
(budget_group_resp_id,
budget_group_id,
wf_role_name,
wf_role_orig_system,
wf_role_orig_system_id,
responsibility_type,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
values
(
l_budget_group_resp_id,
p_curr_budget_group_id,
Bgw_Rec.wf_role_name,
Bgw_Rec.wf_role_orig_system,
Bgw_Rec.wf_role_orig_system_id,
Bgw_Rec.responsibility_type,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_created_by,
l_creation_date);
select psb_budget_group_categories_s.NEXTVAL
Into l_budget_group_category_id from DUAL ;
Insert into psb_budget_group_categories
( budget_group_category_id ,
budget_group_id ,
stage_id ,
last_updated_date ,
last_updated_by ,
last_update_login ,
created_by ,
created_date )
values
(l_budget_group_category_id,
p_curr_budget_group_id,
Bgwf_Rec.stage_id,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_created_by,
l_creation_date);
select count(*) INTO l_count
from psb_account_position_sets
where name = l_name
and account_or_position_type = 'A'
and global_or_local_type = 'G' ;
PSB_Account_Position_Set_PVT.Insert_Row
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_row_id => l_row_id,
p_account_position_set_id => l_account_position_set_id,
p_name => l_name,
p_set_of_books_id => Bgsr_Rec.set_of_books_id,
p_data_extract_id => Bgsr_Rec.data_extract_id,
p_global_or_local_type => Bgsr_Rec.Global_or_Local_Type,
p_account_or_position_type => Bgsr_Rec.account_or_position_type,
p_attribute_selection_type => Bgsr_Rec.attribute_selection_type,
p_business_group_id => Bgsr_Rec.business_group_id,
p_last_update_date => l_last_update_date,
p_last_updated_by => l_last_updated_by,
p_last_update_login => l_last_update_login,
p_created_by => l_created_by,
p_creation_date => l_creation_date,
p_use_in_budget_group_flag => Bgsr_Rec.use_in_budget_group_flag
);
PSB_Set_Relation_PVT.Insert_Row
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_return_status => l_return_status1,
p_msg_count => l_msg_count1,
p_msg_data => l_msg_data1,
p_Row_Id => l_row_id1,
p_Set_Relation_Id => l_set_relation_id,
p_Account_Position_Set_Id => l_account_position_set_id,
p_Allocation_Rule_Id => null,
p_Budget_Group_Id => p_curr_budget_group_id,
p_Budget_Workflow_Rule_Id => null,
p_Constraint_Id => null,
p_Default_Rule_Id => null,
p_Parameter_Id => null,
p_Position_Set_Group_Id => null,
/* Budget Revision Rules Enhancement Start */
p_rule_id => null,
p_apply_balance_flag => null,
/* Budget Revision Rules Enhancement End */
p_Effective_Start_Date => Bgsr_Rec.effective_start_date,
p_Effective_End_Date => BGsr_Rec.effective_end_date,
p_last_update_date => l_last_update_date,
p_last_updated_by => l_last_updated_by,
p_last_update_login => l_last_update_login,
p_created_by => l_created_by,
p_creation_date => l_creation_date
);
PSB_Acct_Pos_Set_Line_I_PVT.Insert_Row
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_return_status => l_return_status2,
p_msg_count => l_msg_count2,
p_msg_data => l_msg_data2,
p_Row_Id => l_row_id2,
p_line_sequence_id => l_line_sequence_id,
p_account_position_set_id => l_account_position_set_id,
p_description => Bgal_Rec.description,
p_business_group_id => Bgal_Rec.business_group_id,
p_attribute_id => Bgal_Rec.attribute_id,
p_include_or_exclude_type => Bgal_Rec.include_or_exclude_type,
p_segment1_low => Bgal_Rec.segment1_low,
p_segment2_low => Bgal_Rec.segment2_low,
p_segment3_low => Bgal_Rec.segment3_low,
p_segment4_low => Bgal_Rec.segment4_low,
p_segment5_low => Bgal_Rec.segment5_low ,
p_segment6_low => Bgal_Rec.segment6_low,
p_segment7_low => Bgal_Rec.segment7_low,
p_segment8_low => Bgal_Rec.segment8_low,
p_segment9_low => Bgal_Rec.segment9_low,
p_segment10_low => Bgal_Rec.segment10_low,
p_segment11_low => Bgal_Rec.segment11_low,
p_segment12_low => Bgal_Rec.segment12_low,
p_segment13_low => Bgal_Rec.segment13_low,
p_segment14_low => Bgal_Rec.segment14_low,
p_segment15_low => Bgal_Rec.segment15_low,
p_segment16_low => Bgal_Rec.segment16_low,
p_segment17_low => Bgal_Rec.segment17_low,
p_segment18_low => Bgal_Rec.segment18_low,
p_segment19_low => Bgal_Rec.segment19_low,
p_segment20_low => Bgal_Rec.segment20_low,
p_segment21_low => Bgal_Rec.segment21_low,
p_segment22_low => Bgal_Rec.segment22_low,
p_segment23_low => Bgal_Rec.segment23_low,
p_segment24_low => Bgal_Rec.segment24_low,
p_segment25_low => Bgal_Rec.segment25_low,
p_segment26_low => Bgal_Rec.segment26_low,
p_segment27_low => Bgal_Rec.segment27_low,
p_segment28_low => Bgal_Rec.segment28_low,
p_segment29_low => Bgal_Rec.segment29_low,
p_segment30_low => Bgal_Rec.segment30_low,
p_segment1_high => Bgal_Rec.segment1_high,
p_segment2_high => Bgal_Rec.segment2_high,
p_segment3_high => Bgal_Rec.segment3_high,
p_segment4_high => Bgal_Rec.segment4_high,
p_segment5_high => Bgal_Rec.segment5_high,
p_segment6_high => Bgal_Rec.segment6_high,
p_segment7_high => Bgal_Rec.segment7_high,
p_segment8_high => Bgal_Rec.segment8_high,
p_segment9_high => Bgal_Rec.segment9_high,
p_segment10_high => Bgal_Rec.segment10_high,
p_segment11_high => Bgal_Rec.segment11_high,
p_segment12_high => Bgal_Rec.segment12_high,
p_segment13_high => Bgal_Rec.segment13_high,
p_segment14_high => Bgal_Rec.segment14_high,
p_segment15_high => Bgal_Rec.segment15_high,
p_segment16_high => Bgal_Rec.segment16_high,
p_segment17_high => Bgal_Rec.segment17_high,
p_segment18_high => Bgal_Rec.segment18_high,
p_segment19_high => Bgal_Rec.segment19_high,
p_segment20_high => Bgal_Rec.segment20_high,
p_segment21_high => Bgal_Rec.segment21_high,
p_segment22_high => Bgal_Rec.segment22_high,
p_segment23_high => Bgal_Rec.segment23_high,
p_segment24_high => Bgal_Rec.segment24_high,
p_segment25_high => Bgal_Rec.segment25_high,
p_segment26_high => Bgal_Rec.segment26_high,
p_segment27_high => Bgal_Rec.segment27_high,
p_segment28_high => Bgal_Rec.segment28_high,
p_segment29_high => Bgal_Rec.segment29_high,
p_segment30_high => Bgal_Rec.segment30_high,
p_context => Bgal_Rec.context,
p_attribute1 => Bgal_Rec.attribute1,
p_attribute2 => Bgal_Rec.attribute2,
p_attribute3 => Bgal_Rec.attribute3,
p_attribute4 => Bgal_Rec.attribute4,
p_attribute5 => Bgal_Rec.attribute5,
p_attribute6 => Bgal_Rec.attribute6,
p_attribute7 => Bgal_Rec.attribute7,
p_attribute8 => Bgal_Rec.attribute8,
p_attribute9 => Bgal_Rec.attribute9,
p_attribute10 => Bgal_Rec.attribute10,
p_last_update_date => l_last_update_date,
p_last_updated_by => l_last_updated_by,
p_last_update_login => l_last_update_login,
p_created_by => l_created_by,
p_creation_date => l_creation_date
);
SELECT budget_group_id,PS_ACCOUNT_POSITION_SET_ID,
NPS_ACCOUNT_POSITION_SET_ID,name,chart_of_accounts_id
FROM psb_budget_groups_v
WHERE root_budget_group = 'Y'
AND (((p_budget_group_id is not null) and
(budget_group_id = p_budget_group_id))
or (p_budget_group_id is null));
select gcc.code_combination_id
from gl_code_combinations gcc
where gcc.chart_of_accounts_id = p_flex_code
and gcc.detail_budgeting_allowed_flag = 'Y'
and gcc.enabled_flag = 'Y'
/*For Bug No : 2359795 Start*/
and gcc.summary_flag = 'N'
and gcc.template_id is null
/*For Bug No : 2359795 End*/
and not exists(select 1
from PSB_BUDGET_ACCOUNTS b,
PSB_SET_RELATIONS_V c,
PSB_BUDGET_GROUPS d
where b.code_combination_id = gcc.code_combination_id
and b.account_position_set_id = c.account_position_set_id
and c.budget_group_id = d.budget_group_id
and (d.budget_group_id = p_top_budget_group_id or
d.root_budget_group_id = p_top_budget_group_id));
DELETE PSB_ERROR_MESSAGES
WHERE SOURCE_PROCESS = 'VALIDATE_BUDGET_HIERARCHY'
AND process_id = p_top_budget_group_id;
l_missing_accts.delete;
PSB_MESSAGE_S.BATCH_INSERT_ERROR('VALIDATE_BUDGET_HIERARCHY',
p_top_budget_group_id);
SELECT a.code_combination_id ccid,b.name bg_name ,s.name set_name
FROM psb_budget_accounts a,
psb_set_relations_v s,
psb_budget_groups_v b
WHERE b.budget_group_id = s.budget_group_id
AND b.budget_group_type = 'R'
AND nvl(b.root_budget_group_id, b.budget_group_id) = p_top_budget_group_id
AND s.account_position_set_id = a.account_position_set_id
AND NOT EXISTS
(SELECT z.code_combination_id from psb_budget_accounts z
WHERE z.account_position_set_id in (p_ps_account_set_id, p_nps_account_set_id)
AND a.code_combination_id = z.code_combination_id);
SELECT budget_group_id ,short_name
FROM psb_budget_groups_v
WHERE budget_group_type = 'R'
AND nvl(root_budget_group_id, budget_group_id) =
p_top_budget_group_id
AND organization_id is null;
SELECT budget_group_id, short_name
FROM psb_budget_groups_v
WHERE budget_group_type = 'R'
AND budget_group_id = p_top_budget_group_id
AND business_group_id is null;
SELECT budget_group_id, bg.short_name
FROM psb_budget_groups_v bg
WHERE budget_group_type = 'R'
AND root_budget_group_id = p_top_budget_group_id
AND nvl(root_budget_group,'N') = 'N'
and not exists
(select 'exists' from per_organization_units
where organization_id = bg.organization_id and
business_group_id = bg.root_business_group_id);
delete from PSB_ERROR_MESSAGES
where source_process = 'VALIDATE_BUDGET_HIERARCHY'
and process_id = p_budget_group_id;
PSB_MESSAGE_S.Insert_Error ( p_source_process => 'VALIDATE_BUDGET_HIERARCHY',
p_process_id => p_budget_group_id,
p_msg_count => l_msg_count,
p_msg_data => l_msg_buf,
p_desc_sequence => FND_API.G_TRUE) ;
PSB_MESSAGE_S.BATCH_INSERT_ERROR ( p_source_process => 'VALIDATE_BUDGET_HIERARCHY',
p_process_id => p_budget_group_id);
SELECT max(sequence_number) + 1 max_seq
FROM psb_error_messages
WHERE process_id = p_budget_group_id
AND source_process = 'VALIDATE_BUDGET_HIERARCHY')
LOOP
l_max_sequence_number := l_max_seq_rec.max_seq;
UPDATE psb_error_messages
SET sequence_number = (l_max_sequence_number - sequence_number)
WHERE process_id = p_budget_group_id
AND source_process = 'VALIDATE_BUDGET_HIERARCHY';
| PROCEDURE Delete Row CP |
+===========================================================================*/
--
-- This is the execution file for the concurrent program Create Budget Journal
-- through Standard Report Submissions.
--
PROCEDURE DELETE_ROW_CP
(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
--
p_budget_group_id IN NUMBER
)
IS
--
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ROW_CP';
l_delete VARCHAR(20) ;
PSB_BUDGET_GROUPS_PVT.DELETE_ROW
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_budget_group_id => p_budget_group_id,
p_delete => l_delete);
if l_delete <> 'DELETE' THEN
FND_FILE.put_line(FND_FILE.LOG,'The Budget Group Cannot Be Deleted');
END Delete_Row_CP ;