[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(global_budget_revision_id, budget_revision_id) global_revision_id
from PSB_BUDGET_REVISIONS
where budget_revision_id = RevID;
select budget_revision_id
from PSB_BUDGET_REVISIONS
where nvl(global_budget_revision_id, budget_revision_id) = GlobalRevID
and budget_group_id in
(select budget_group_id
from PSB_BUDGET_GROUPS
where budget_group_type = 'R'
start with budget_group_id = BudgetGroupID
connect by prior parent_budget_group_id = budget_group_id);
revision_value NUMBER, note_id NUMBER, delete_flag BOOLEAN ) ;
TYPE g_last_update_flag_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
g_last_update_flag_tbl g_last_update_flag_tbl_type;
SELECT minimum_accountable_unit,
precision
INTO l_minimum_accountable_unit,
l_precision
FROM fnd_currencies
WHERE currency_code = p_currency_code
AND enabled_flag = 'Y'
AND currency_flag = 'Y'
AND (start_date_active <= sysdate or start_date_active is null)
AND (end_date_active >= sysdate or end_date_active is null);
| PROCEDURE Delete_Row |
+==========================================================================*/
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_revision_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ROW';
SAVEPOINT DELETE_ROW;
delete FROM PSB_BUDGET_REVISIONS
WHERE BUDGET_REVISION_ID = P_BUDGET_REVISION_ID;
rollback to DELETE_ROW;
rollback to DELETE_ROW;
rollback to DELETE_ROW;
END Delete_Row;
select psb_budget_revisions_s.nextval budget_revision_id
from dual;
update PSB_BUDGET_REVISIONS
set justification = decode(p_justification, FND_API.G_MISS_CHAR, justification, p_justification),
from_gl_period_name = decode(p_from_gl_period_name,FND_API.G_MISS_CHAR, from_gl_period_name, p_from_gl_period_name),
to_gl_period_name = decode(p_to_gl_period_name, FND_API.G_MISS_CHAR, to_gl_period_name, p_to_gl_period_name),
currency_code = decode(p_currency_code, FND_API.G_MISS_CHAR, currency_code, p_currency_code),
effective_start_date = decode(p_effective_start_date, FND_API.G_MISS_DATE, effective_start_date, p_effective_start_date),
effective_end_date = decode(p_effective_end_date, FND_API.G_MISS_DATE, effective_end_date, p_effective_end_date),
hr_budget_id = decode(p_hr_budget_id, FND_API.G_MISS_NUM, hr_budget_id, p_hr_budget_id),
budget_revision_type = decode(p_budget_revision_type, FND_API.G_MISS_CHAR, budget_revision_type, p_budget_revision_type),
transaction_type = decode(p_transaction_type, FND_API.G_MISS_CHAR, transaction_type, p_transaction_type),
permanent_revision = decode(p_permanent_revision, FND_API.G_MISS_CHAR, permanent_revision, p_permanent_revision),
revise_by_position = decode(p_revise_by_position, FND_API.G_MISS_CHAR, revise_by_position, p_revise_by_position),
balance_type = decode(p_balance_type, FND_API.G_MISS_CHAR, balance_type, p_balance_type),
global_budget_revision = decode(p_global_budget_revision, FND_API.G_MISS_CHAR,global_budget_revision,p_global_budget_revision),
global_budget_revision_id = decode(p_global_budget_revision_id, FND_API.G_MISS_NUM,global_budget_revision_id,p_global_budget_revision_id),
parameter_set_id = decode(p_parameter_set_id, FND_API.G_MISS_NUM, parameter_set_id, p_parameter_set_id),
constraint_set_id = decode(p_constraint_set_id, FND_API.G_MISS_NUM, constraint_set_id, p_constraint_set_id),
submission_date = decode(p_submission_date, FND_API.G_MISS_DATE, submission_date, p_submission_date),
submission_status = decode(p_submission_status, FND_API.G_MISS_CHAR, submission_status, p_submission_status),
approval_orig_system = decode(p_approval_orig_system, FND_API.G_MISS_CHAR, approval_orig_system, p_approval_orig_system),
approval_override_by = decode(p_approval_override_by, FND_API.G_MISS_NUM, approval_override_by, p_approval_override_by),
freeze_flag = decode(p_freeze_flag, FND_API.G_MISS_CHAR, freeze_flag, p_freeze_flag),
request_id = decode(p_request_id, FND_API.G_MISS_NUM, request_id, p_request_id),
base_line_revision = decode(p_base_line_revision, FND_API.G_MISS_CHAR, base_line_revision, p_base_line_revision),
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID,
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),
attribute11 = decode(p_attribute11, FND_API.G_MISS_CHAR, attribute11, p_attribute11),
attribute12 = decode(p_attribute12, FND_API.G_MISS_CHAR, attribute12, p_attribute12),
attribute13 = decode(p_attribute13, FND_API.G_MISS_CHAR, attribute13, p_attribute13),
attribute14 = decode(p_attribute14, FND_API.G_MISS_CHAR, attribute14, p_attribute14),
attribute15 = decode(p_attribute15, FND_API.G_MISS_CHAR, attribute15, p_attribute15),
attribute16 = decode(p_attribute16, FND_API.G_MISS_CHAR, attribute16, p_attribute16),
attribute17 = decode(p_attribute17, FND_API.G_MISS_CHAR, attribute17, p_attribute17),
attribute18 = decode(p_attribute18, FND_API.G_MISS_CHAR, attribute18, p_attribute18),
attribute19 = decode(p_attribute19, FND_API.G_MISS_CHAR, attribute19, p_attribute19),
attribute20 = decode(p_attribute20, FND_API.G_MISS_CHAR, attribute20, p_attribute20),
attribute21 = decode(p_attribute21, FND_API.G_MISS_CHAR, attribute21, p_attribute21),
attribute22 = decode(p_attribute22, FND_API.G_MISS_CHAR, attribute22, p_attribute22),
attribute23 = decode(p_attribute23, FND_API.G_MISS_CHAR, attribute23, p_attribute23),
attribute24 = decode(p_attribute24, FND_API.G_MISS_CHAR, attribute24, p_attribute24),
attribute25 = decode(p_attribute25, FND_API.G_MISS_CHAR, attribute25, p_attribute25),
attribute26 = decode(p_attribute26, FND_API.G_MISS_CHAR, attribute26, p_attribute26),
attribute27 = decode(p_attribute27, FND_API.G_MISS_CHAR, attribute27, p_attribute27),
attribute28 = decode(p_attribute28, FND_API.G_MISS_CHAR, attribute28, p_attribute28),
attribute29 = decode(p_attribute29, FND_API.G_MISS_CHAR, attribute29, p_attribute29),
attribute30 = decode(p_attribute30, FND_API.G_MISS_CHAR, attribute30, p_attribute30),
context = decode(p_context,FND_API.G_MISS_CHAR, context, p_context)
where budget_revision_id = p_budget_revision_id;
INSERT INTO PSB_BUDGET_REVISIONS
(budget_revision_id,
justification,
budget_group_id,
gl_budget_set_id,
hr_budget_id,
from_gl_period_name,
to_gl_period_name,
currency_code,
effective_start_date,
effective_end_date,
budget_revision_type,
transaction_type,
permanent_revision,
revise_by_position,
balance_type,
requestor,
parameter_set_id,
constraint_set_id,
submission_date,
submission_status,
approval_orig_system,
approval_override_by,
freeze_flag,
request_id,
base_line_revision,
global_budget_revision,
global_budget_revision_id,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
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,
context)
values (l_budget_revision_id,
decode(p_justification,FND_API.G_MISS_CHAR,null,p_justification),
decode(p_budget_group_id,FND_API.G_MISS_NUM,null,p_budget_group_id),
decode(p_gl_budget_set_id,FND_API.G_MISS_NUM,null,p_gl_budget_set_id),
decode(p_hr_budget_id,FND_API.G_MISS_NUM,null,p_hr_budget_id),
decode(p_from_gl_period_name,FND_API.G_MISS_CHAR,null,p_from_gl_period_name),
decode(p_to_gl_period_name,FND_API.G_MISS_CHAR,null,p_to_gl_period_name),
decode(p_currency_code,FND_API.G_MISS_CHAR,null,p_currency_code),
decode(p_effective_start_date,FND_API.G_MISS_DATE,null,p_effective_start_date),
decode(p_effective_end_date,FND_API.G_MISS_DATE,null,p_effective_end_date),
decode(p_budget_revision_type,FND_API.G_MISS_CHAR,null,p_budget_revision_type),
decode(p_transaction_type,FND_API.G_MISS_CHAR,null,p_transaction_type),
decode(p_permanent_revision,FND_API.G_MISS_CHAR,null,p_permanent_revision),
decode(p_revise_by_position,FND_API.G_MISS_CHAR,null,p_revise_by_position),
decode(p_balance_type,FND_API.G_MISS_CHAR,'YTD',p_balance_type),
decode(p_requestor,FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, -1, FND_GLOBAL.USER_ID, p_requestor),
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_submission_date,FND_API.G_MISS_DATE,null,p_submission_date),
decode(p_submission_status,FND_API.G_MISS_CHAR,null,p_submission_status),
decode(p_approval_orig_system,FND_API.G_MISS_CHAR,null,p_approval_orig_system),
decode(p_approval_override_by,FND_API.G_MISS_NUM,null,p_approval_override_by),
decode(p_freeze_flag,FND_API.G_MISS_CHAR,null,p_freeze_flag),
decode(p_request_id,FND_API.G_MISS_NUM,null,p_request_id),
decode(p_base_line_revision,FND_API.G_MISS_CHAR,null,p_base_line_revision),
decode(p_global_budget_revision,FND_API.G_MISS_CHAR,null,p_global_budget_revision),
decode(p_global_budget_revision_id,FND_API.G_MISS_NUM,null,p_global_budget_revision_id),
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
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_attribute11,FND_API.G_MISS_CHAR,null,p_attribute11),
decode(p_attribute12,FND_API.G_MISS_CHAR,null,p_attribute12),
decode(p_attribute13,FND_API.G_MISS_CHAR,null,p_attribute13),
decode(p_attribute14,FND_API.G_MISS_CHAR,null,p_attribute14),
decode(p_attribute15,FND_API.G_MISS_CHAR,null,p_attribute15),
decode(p_attribute16,FND_API.G_MISS_CHAR,null,p_attribute16),
decode(p_attribute17,FND_API.G_MISS_CHAR,null,p_attribute17),
decode(p_attribute18,FND_API.G_MISS_CHAR,null,p_attribute18),
decode(p_attribute19,FND_API.G_MISS_CHAR,null,p_attribute19),
decode(p_attribute20,FND_API.G_MISS_CHAR,null,p_attribute20),
decode(p_attribute21,FND_API.G_MISS_CHAR,null,p_attribute21),
decode(p_attribute22,FND_API.G_MISS_CHAR,null,p_attribute22),
decode(p_attribute23,FND_API.G_MISS_CHAR,null,p_attribute23),
decode(p_attribute24,FND_API.G_MISS_CHAR,null,p_attribute24),
decode(p_attribute25,FND_API.G_MISS_CHAR,null,p_attribute25),
decode(p_attribute26,FND_API.G_MISS_CHAR,null,p_attribute26),
decode(p_attribute27,FND_API.G_MISS_CHAR,null,p_attribute27),
decode(p_attribute28,FND_API.G_MISS_CHAR,null,p_attribute28),
decode(p_attribute29,FND_API.G_MISS_CHAR,null,p_attribute29),
decode(p_attribute30,FND_API.G_MISS_CHAR,null,p_attribute30),
decode(p_context,FND_API.G_MISS_CHAR,null,p_context));
select budget_group_id,
budget_revision_type,
transaction_type,
permanent_revision,
nvl(revise_by_position,'N') revise_by_position,
balance_type,
parameter_set_id,
constraint_set_id,
gl_budget_set_id,
from_gl_period_name,
to_gl_period_name,
effective_start_date,
effective_end_date,
freeze_flag,
base_line_revision,
currency_code,
approval_orig_system,
approval_override_by,
nvl(global_budget_revision_id, budget_revision_id) global_budget_revision_id,
hr_budget_id
from PSB_BUDGET_REVISIONS
where budget_revision_id = p_budget_revision_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,
name
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 period_name, start_date,end_date
from gl_period_statuses
where application_id = 101
and set_of_books_id = g_set_of_books_id
and period_name in (g_from_gl_period_name, g_to_gl_period_name);
select min(start_date) start_date, max(end_date) end_date
from GL_PERIOD_STATUSES
where application_id = 101
and set_of_books_id = g_set_of_books_id
and period_name in
(select b.gl_period_name from psb_budget_revision_lines a, psb_budget_revision_accounts b
where a.budget_revision_id = p_budget_revision_id
and b.budget_revision_acct_line_id = a.budget_revision_acct_line_id);
select min(effective_start_date) start_date, max(effective_end_date) end_date
from PSB_BUDGET_REVISION_POSITIONS a, PSB_BUDGET_REVISION_POS_LINES b
where b.budget_revision_id = p_budget_revision_id
and a.budget_revision_pos_line_id = b.budget_revision_pos_line_id;
select 'Exists'
from dual
where exists
(select a.position_id, a.effective_start_date, a.effective_end_date, a.budget_group_id
from PSB_POSITIONS a,
(select budget_group_id from PSB_BUDGET_GROUPS
start with budget_group_id = g_budget_group_id
connect by prior budget_group_id = parent_budget_group_id) b
where a.data_extract_id = g_data_extract_id
and a.budget_group_id = b.budget_group_id);
select name, constraint_threshold
from PSB_CONSTRAINT_SETS_V
where constraint_set_id = g_constraint_set_id;
SELECT start_date, end_date
FROM gl_period_statuses
WHERE application_id = 101
AND set_of_books_id = g_set_of_books_id
AND period_name = p_gl_period_name;
'select pbra.budget_revision_acct_line_id '||
'from psb_budget_revision_accounts pbra, psb_budget_revision_lines pbrl ' ||
'where pbra.code_combination_id = :code_combination_id ' ||
'and pbra.gl_period_name = :gl_period_name ' ||
'and NVL(pbra.currency_code, :gmc1) = NVL(:currency_code, :gmc2) ' ||
'and NVL(pbra.gl_budget_version_id,:gmn1)=NVL(:budget_version_id,:gmn2) '||
'and NVL(pbra.position_id, :gmn3) = NVL(:position_id, :gmn4) ' ||
'and pbrl.budget_revision_id = :budget_revision_id ' ||
'and pbrl.budget_revision_acct_line_id = pbra.budget_revision_acct_line_id';
DELETE psb_budget_revision_lines
WHERE budget_revision_acct_line_id = l_budget_revision_acct_line_id;
DELETE psb_budget_revision_accounts
WHERE budget_revision_acct_line_id = l_budget_revision_acct_line_id;
sql_bra := 'select pbra.budget_revision_acct_line_id '||
'from psb_budget_revision_accounts pbra, psb_budget_revision_lines pbrl ' ||
'where pbra.code_combination_id = :code_combination_id ' ||
'and pbra.gl_period_name = :gl_period_name ' ||
'and nvl(pbra.currency_code, ''' || FND_API.G_MISS_CHAR ||
''') = nvl(:currency_code, ''' || FND_API.G_MISS_CHAR || ''') ' ||
-- Start bug # 3022417
'and nvl(pbra.gl_budget_version_id,-9999)=nvl(:budget_version_id,-9999) '||
'and nvl(pbra.position_id, -9999) = nvl(:position_id, -9999) ' ||
-- End bug # 3022417
'and pbrl.budget_revision_id = :budget_revision_id ' ||
'and pbrl.budget_revision_acct_line_id = pbra.budget_revision_acct_line_id';
GL_CODE_COMBINATIONS_PKG.Select_Columns
(X_code_combination_id => p_code_combination_id,
X_account_type => l_account_type,
X_template_id => l_template_id);
Insert into PSB_BUDGET_REVISION_ACCOUNTS (BUDGET_REVISION_ACCT_LINE_ID, CODE_COMBINATION_ID,
BUDGET_GROUP_ID, POSITION_ID, GL_PERIOD_NAME, GL_BUDGET_VERSION_ID, CURRENCY_CODE,
BUDGET_BALANCE, ACCOUNT_TYPE, REVISION_TYPE, REVISION_VALUE_TYPE,
REVISION_AMOUNT, functional_transaction,
FUNDS_CONTROL_STATUS_CODE, FUNDS_CONTROL_RESULTS_CODE,
NOTE_ID, FUNDS_CONTROL_TIMESTAMP, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE)
values (psb_budget_revision_accounts_s.nextval, p_code_combination_id,
p_budget_group_id, decode(p_position_id, FND_API.G_MISS_NUM, null, p_position_id), p_gl_period_name, l_budget_version_id, p_currency_code,
p_budget_balance, l_account_type, p_revision_type, p_revision_value_type,
decode(p_revision_value_type, 'A', Get_Rounded_Amount(p_currency_code, p_revision_amount), p_revision_amount), p_functional_transaction,
decode(p_funds_status_code, FND_API.G_MISS_CHAR, null, p_funds_status_code),
decode(p_funds_result_code, FND_API.G_MISS_CHAR, null, p_funds_result_code),
decode(p_note_id, FND_API.G_MISS_NUM, null, p_note_id), p_funds_control_timestamp,
sysdate, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID, FND_GLOBAL.USER_ID, sysdate)
returning budget_revision_acct_line_id into l_budget_revision_acct_line_id;
INSERT INTO PSB_BUDGET_REVISION_LINES
(BUDGET_REVISION_ACCT_LINE_ID, BUDGET_REVISION_ID, FREEZE_FLAG,
VIEW_LINE_FLAG, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY,
CREATION_DATE)
VALUES (l_budget_revision_acct_line_id, c_Distribute_Rev_Rec.budget_revision_id, p_freeze_flag,
p_view_line_flag, sysdate, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID, FND_GLOBAL.USER_ID,
sysdate);
Update PSB_BUDGET_REVISION_ACCOUNTS
set code_combination_id = p_code_combination_id,
budget_group_id = p_budget_group_id,
gl_period_name = p_gl_period_name,
budget_balance = p_budget_balance,
revision_type = p_revision_type,
revision_value_type = p_revision_value_type,
revision_amount = decode(p_revision_value_type, 'A', Get_Rounded_Amount(p_currency_code, p_revision_amount), p_revision_amount),
funds_control_status_code = decode(p_funds_status_code, FND_API.G_MISS_CHAR, funds_control_status_code, null, funds_control_status_code, p_funds_status_code),
funds_control_results_code = decode(p_funds_result_code, FND_API.G_MISS_CHAR, funds_control_results_code, null, funds_control_results_code, p_funds_result_code),
funds_control_timestamp = p_funds_control_timestamp,
note_id = decode(p_note_id, FND_API.G_MISS_NUM, note_id, null, note_id, p_note_id),
freeze_flag = p_freeze_flag,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID,
currency_code = p_currency_code -- Bug 3029168
where budget_revision_acct_line_id = l_budget_revision_acct_line_id;
select 'Exists'
from PSB_PARAMETER_ASSIGNMENTS_V a
where p_local_parameter = 'N'
and parameter_set_id = g_parameter_set_id
and parameter_id = p_parameter_id
and exists
(select 1
from PSB_SET_RELATIONS_V b,
PSB_BUDGET_ACCOUNTS c
where b.account_or_position_type = 'A'
and b.account_position_set_id = c.account_position_set_id
and b.parameter_id = p_parameter_id
and c.code_combination_id = p_ccid)
and a.parameter_type = 'ACCOUNT'
and (((a.effective_start_date <= nvl(p_ccid_end_period, p_period_end_date))
and (a.effective_end_date is null))
or ((a.effective_start_date between nvl(p_ccid_start_period, p_period_start_date) and nvl(p_ccid_end_period, p_period_end_date))
or (a.effective_end_date between nvl(p_ccid_start_period, p_period_start_date) and nvl(p_ccid_end_period, p_period_end_date))
or ((effective_start_date < nvl(p_ccid_start_period, p_period_start_date))
and (effective_end_date > nvl(p_ccid_end_period, p_period_end_date)))))
and (((a.effective_start_date <= p_period_end_date)
and (a.effective_end_date is null))
or ((a.effective_start_date between p_period_start_date and p_period_end_date)
or (a.effective_end_date between p_period_start_date and p_period_end_date)
or ((effective_start_date < p_period_start_date)
and (effective_end_date > p_period_end_date))))
UNION
select 'Exists'
from PSB_ENTITY a
where p_local_parameter = 'Y'
and entity_id = p_parameter_id
and exists
(select 1
from PSB_SET_RELATIONS_V b,
PSB_BUDGET_ACCOUNTS c
where b.account_or_position_type = 'A'
and b.account_position_set_id = c.account_position_set_id
and b.parameter_id = p_parameter_id
and c.code_combination_id = p_ccid)
and a.entity_subtype = 'ACCOUNT'
and (((a.effective_start_date <= nvl(p_ccid_end_period, p_period_end_date))
and (a.effective_end_date is null))
or ((a.effective_start_date between nvl(p_ccid_start_period, p_period_start_date) and nvl(p_ccid_end_period, p_period_end_date))
or (a.effective_end_date between nvl(p_ccid_start_period, p_period_start_date) and nvl(p_ccid_end_period, p_period_end_date))
or ((effective_start_date < nvl(p_ccid_start_period, p_period_start_date))
and (effective_end_date > nvl(p_ccid_end_period, p_period_end_date)))))
and (((a.effective_start_date <= p_period_end_date)
and (a.effective_end_date is null))
or ((a.effective_start_date between p_period_start_date and p_period_end_date)
or (a.effective_end_date between p_period_start_date and p_period_end_date)
or ((effective_start_date < p_period_start_date)
and (effective_end_date > p_period_end_date))));
select 'Exists'
from psb_budget_revision_accounts a, psb_budget_revision_lines b
where a.code_combination_id = p_ccid
and a.gl_period_name = p_period_name
and b.budget_revision_id = p_budget_revision_id
and b.budget_revision_acct_line_id = a.budget_revision_acct_line_id;
select step_number,
prefix_operator,
budget_year_type_id,
balance_type,
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,
currency_code,
nvl(amount, 0) amount,
postfix_operator
from PSB_PARAMETER_FORMULAS
where parameter_id = p_parameter_id
order by step_number;
select nvl(b.root_budget_group_id, b.budget_group_id) budget_group_id
from PSB_WORKSHEETS a, PSB_BUDGET_GROUPS b
where a.worksheet_id = p_worksheet_id
and b.budget_group_id = a.budget_group_id;
select budget_revision_id
from psb_budget_revisions
where budget_group_id = l_budget_group_id
and base_line_revision = 'Y'
AND ((currency_code = 'STAT' AND p_event_type = 'SW')
OR ((currency_code <> 'STAT' OR currency_code IS NULL) AND p_event_type = 'BP'));
select aeh.ae_header_id, aeh.budget_version_id, aeh.period_name,
ael.code_combination_id, ael.currency_code,
sum(nvl(ael.entered_dr, 0) - nvl(ael.entered_cr, 0)) budget_balance
from PSB_AE_LINES_ALL ael,
PSB_AE_HEADERS_ALL aeh
where ael.source_id = p_worksheet_id
and ael.source_table = 'PSB_WORKSHEETS'
and ael.actual_flag = 'B'
and aeh.ae_header_id = ael.ae_header_id
group by aeh.ae_header_id, aeh.budget_version_id, aeh.period_name, ael.code_combination_id, ael.currency_code;*/
SELECT pgi.worksheet_id,
pgi.budget_version_id,
pgi.period_name,
pgi.code_combination_id,
pgi.currency_code,
SUM(NVL(pgi.entered_dr, 0) - NVL(pgi.entered_cr, 0)) budget_balance
FROM psb_gl_interfaces pgi
WHERE pgi.worksheet_id = p_worksheet_id
AND pgi.actual_flag = 'B'
AND pgi.budget_source_type = p_event_type -- Bug 3029168
GROUP BY pgi.worksheet_id,
pgi.budget_version_id,
pgi.period_name,
pgi.code_combination_id,
pgi.currency_code;
GL_CODE_COMBINATIONS_PKG.Select_Columns
(X_code_combination_id => c_lines_rec.code_combination_id,
X_account_type => l_account_type,
X_template_id => l_template_id);
Select budget_balance
from psb_budget_revision_accounts pbra,
psb_budget_revision_lines pbrl,
psb_budget_revisions pbr
where pbra.code_combination_id = p_code_combination_id
and pbra.gl_period_name = p_gl_period
and pbra.gl_budget_version_id = p_gl_budget_version_id
and pbra.budget_revision_acct_line_id = pbrl.budget_revision_acct_line_id
and pbra.position_id is null
and pbrl.budget_revision_id = pbr.budget_revision_id
and pbr.budget_group_id = l_budget_group_id
and pbr.base_line_revision = 'Y'
and NVL(pbr.currency_code,p_currency_code) = p_currency_code
and NVL(pbra.currency_code,p_currency_code)
= NVL(pbr.currency_code,p_currency_code); -- Bug 3029168
Select sum(budget_balance) sum_budget_balance
from psb_budget_revision_accounts pbra,
psb_budget_revision_lines pbrl,
psb_budget_revisions pbr
where pbra.code_combination_id = p_code_combination_id
and pbra.gl_budget_version_id = p_gl_budget_version_id
and pbra.position_id is null
and pbra.budget_revision_acct_line_id = pbrl.budget_revision_acct_line_id
and pbrl.budget_revision_id = pbr.budget_revision_id
and pbr.budget_group_id = l_budget_group_id
and pbr.base_line_revision = 'Y'
and NVL(pbr.currency_code,p_currency_code) = p_currency_code
and NVL(pbra.currency_code,p_currency_code)
= NVL(pbr.currency_code,p_currency_code) -- Bug 3029168
and pbra.gl_period_name in
(select period_name
from gl_period_statuses
where application_id = 101
and set_of_books_id = p_set_of_books_id
and start_date between l_from_date and l_to_date
and end_date between l_from_date and l_to_date);
Select period_name, start_date,end_date
from gl_period_statuses
where application_id = 101
and set_of_books_id = p_set_of_books_id
and period_name in (p_gl_period, p_end_gl_period);
Select nvl(root_budget_group_id,budget_group_id) root_budget_group_id
from psb_budget_groups_v
where budget_group_id = p_budget_group_id;
(SELECT account_type
FROM gl_code_combinations
WHERE code_combination_id = p_code_combination_id)
LOOP
l_account_type := l_account_type_csr.account_type;
| PROCEDURE Insert_Into_GL_BCP |
+==========================================================================*/
PROCEDURE Insert_Into_GL_BCP
(x_return_status OUT NOCOPY VARCHAR2,
p_packet_id IN NUMBER,
p_budget_revision_id IN NUMBER,
p_code_combination_id IN Number_Tbl_Type,
p_account_type IN Char_Tbl_Type,
p_period_name IN Char_Tbl_Type,
p_period_year IN Number_Tbl_Type,
p_period_num IN Number_Tbl_Type,
p_quarter_num IN Char_Tbl_Type,
p_currency_code IN Char_Tbl_Type,
p_status_code IN Char_Tbl_Type,
p_budget_version_id IN Number_Tbl_Type,
p_entered_dr IN Number_Tbl_Type,
p_entered_cr IN Number_Tbl_Type,
p_accounted_dr IN Number_Tbl_Type,
p_accounted_cr IN Number_Tbl_Type,
p_reference1 IN Char_Tbl_Type
)
IS
l_session_id NUMBER(38);
SELECT s.sid, s.serial#
INTO l_session_id,
l_serial_id
FROM v$session s,v$process p
WHERE s.paddr = p.addr
AND audsid = USERENV('SESSIONID');
INSERT INTO GL_BC_PACKETS
(packet_id,
ledger_id,
je_source_name,
je_category_name,
code_combination_id,
account_type,
actual_flag,
period_name,
period_year,
period_num,
quarter_num,
currency_code,
status_code,
last_update_date,
last_updated_by,
budget_version_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
reference1,
reference2,
application_id, -- Bug 4589283 added the below columns
session_id,
serial_id
)
VALUES
(p_packet_id,
g_set_of_books_id,
g_gl_journal_source,
g_gl_journal_category,
p_code_combination_id(l_indx),
p_account_type(l_indx),
'B',
p_period_name(l_indx),
p_period_year(l_indx),
p_period_num(l_indx),
p_quarter_num(l_indx),
p_currency_code(l_indx),
p_status_code(l_indx),
SYSDATE,
FND_GLOBAL.USER_ID,
p_budget_version_id(l_indx),
p_entered_dr(l_indx),
p_entered_cr(l_indx),
p_accounted_dr(l_indx),
p_accounted_cr(l_indx),
p_reference1(l_indx),
p_budget_revision_id,
8401, --Bug 4589283 added the below columns
l_session_id,
l_serial_id
);
Select code_combination_id,
budget_version_id,
currency_code,
period_name,
result_code,
status_code,
reference1
from GL_BC_PACKETS
where packet_id = l_packet_id;
Select pbra.budget_revision_acct_line_id,
pbra.budget_revision_id,
pbra.code_combination_id,
pbra.gl_period_name,
gps.period_year,
gps.period_num,
gps.quarter_num,
pbra.gl_budget_version_id,
pbra.currency_code,
pbra.budget_balance,
pbra.revision_type,
pbra.revision_value_type,
pbra.revision_amount
from psb_budget_revision_accounts_v pbra,
GL_PERIOD_STATUSES gps
where budget_revision_id = p_budget_revision_id
and gps.application_id = 101
and gps.set_of_books_id = g_set_of_books_id
and gps.period_name = pbra.gl_period_name;
select gl_bc_packets_s.nextval seq
from dual;
GL_CODE_COMBINATIONS_PKG.Select_Columns
(X_code_combination_id => l_rev_lines_tab_inst(l_indx).code_combination_id,
X_account_type => l_account_type,
X_template_id => l_template_id
);
Insert_Into_GL_BCP
(x_return_status => l_return_status,
p_packet_id => l_packet_id,
p_budget_revision_id => p_budget_revision_id,
p_code_combination_id => l_code_combination_id_tab,
p_account_type => l_account_type_tab,
p_period_name => l_period_name_tab,
p_period_year => l_period_year_tab,
p_period_num => l_period_num_tab,
p_quarter_num => l_quarter_num_tab,
p_currency_code => l_currency_code_tab,
p_status_code => l_status_code_tab,
p_budget_version_id => l_budget_version_id_tab,
p_entered_dr => l_entered_dr_tab,
p_entered_cr => l_entered_cr_tab,
p_accounted_dr => l_accounted_dr_tab,
p_accounted_cr => l_accounted_cr_tab,
p_reference1 => l_reference1_tab
);
UPDATE PSB_BUDGET_REVISION_ACCOUNTS
SET budget_balance = l_budget_balance,
funds_control_timestamp = sysdate,
funds_control_status_code = c_Fund_Balances_Rec.status_code,
funds_control_results_code = c_Fund_Balances_Rec.result_code
WHERE budget_revision_acct_line_id = TO_NUMBER(c_Fund_Balances_Rec.reference1);
SELECT account_type
FROM gl_code_combinations
WHERE code_combination_id = p_code_combination_id;
Select data_extract_id
from psb_data_extracts pde,
psb_budget_groups pbg
where system_data_extract = 'Y'
and (((pde.budget_group_id = pbg.root_budget_group_id)
and (pbg.budget_group_id = p_budget_group_id))
or ((pde.budget_group_id = pbg.budget_group_id)
and (pbg.budget_group_id = p_budget_group_id)
and (pbg.root_budget_group_id is null)));
select parameter_id,
name,
priority,
parameter_autoinc_rule,
parameter_compound_annually,
currency_code,
effective_start_date,
effective_end_date
from PSB_PARAMETER_ASSIGNMENTS_V
where p_local_parameter = 'N'
and data_extract_id = g_data_extract_id
and parameter_type = 'ELEMENT'
and (((effective_start_date <= p_revision_end_date)
and (effective_end_date is null))
or ((effective_start_date between p_revision_start_date and p_revision_end_date)
or (effective_end_date between p_revision_start_date and p_revision_end_date)
or ((effective_start_date < p_revision_start_date)
and (effective_end_date > p_revision_end_date))))
and parameter_set_id = g_parameter_set_id
union
select parameter_id,
name,
0 priority,
parameter_autoinc_rule,
parameter_compound_annually,
currency_code,
effective_start_date,
effective_end_date
from PSB_PARAMETERS_V
where p_local_parameter = 'Y'
and data_extract_id = g_data_extract_id
and parameter_type = 'ELEMENT'
and (((effective_start_date <= p_revision_end_date)
and (effective_end_date is null))
or ((effective_start_date between p_revision_start_date and p_revision_end_date)
or (effective_end_date between p_revision_start_date and p_revision_end_date)
or ((effective_start_date < p_revision_start_date)
and (effective_end_date > p_revision_end_date))))
and parameter_id = p_parameter_id
order by effective_start_date, priority;
select parameter_id,
name,
priority,
parameter_compound_annually,
currency_code,
effective_start_date,
effective_end_date
from PSB_PARAMETER_ASSIGNMENTS_V
where p_local_parameter = 'N'
and parameter_autoinc_rule = 'N'
and data_extract_id = g_data_extract_id
and parameter_type = 'POSITION'
and (((effective_start_date <= p_revision_end_date)
and (effective_end_date is null))
or ((effective_start_date between p_revision_start_date and p_revision_end_date)
or (effective_end_date between p_revision_start_date and p_revision_end_date)
or ((effective_start_date < p_revision_start_date)
and (effective_end_date > p_revision_end_date))))
and parameter_set_id = g_parameter_set_id
union
select parameter_id,
name,
0 priority,
parameter_compound_annually,
currency_code,
effective_start_date,
effective_end_date
from PSB_PARAMETERS_V
where p_local_parameter = 'Y'
and parameter_autoinc_rule = 'N'
and data_extract_id = g_data_extract_id
and parameter_type = 'POSITION'
and (((effective_start_date <= p_revision_end_date)
and (effective_end_date is null))
or ((effective_start_date between p_revision_start_date and p_revision_end_date)
or (effective_end_date between p_revision_start_date and p_revision_end_date)
or ((effective_start_date < p_revision_start_date)
and (effective_end_date > p_revision_end_date))))
and parameter_id = p_parameter_id
order by effective_start_date, priority;
select parameter_id,
name,
priority,
parameter_compound_annually,
currency_code,
effective_start_date,
effective_end_date
from PSB_PARAMETER_ASSIGNMENTS_V
where p_local_parameter = 'N'
and parameter_autoinc_rule = 'Y'
and data_extract_id = g_data_extract_id
and parameter_type = 'POSITION'
and (((effective_start_date <= p_revision_end_date)
and (effective_end_date is null))
or ((effective_start_date between p_revision_start_date and p_revision_end_date)
or (effective_end_date between p_revision_start_date and p_revision_end_date)
or ((effective_start_date < p_revision_start_date)
and (effective_end_date > p_revision_end_date))))
and parameter_set_id = g_parameter_set_id
union
select parameter_id,
name,
0 priority,
parameter_compound_annually,
currency_code,
effective_start_date,
effective_end_date
from PSB_PARAMETERS_V
where p_local_parameter = 'Y'
and parameter_id = p_parameter_id
and parameter_autoinc_rule = 'Y'
and data_extract_id = g_data_extract_id
and parameter_type = 'POSITION'
and (((effective_start_date <= l_end_date)
and (effective_end_date is null))
or ((effective_start_date between p_revision_start_date and p_revision_end_date)
or (effective_end_date between p_revision_start_date and p_revision_end_date)
or ((effective_start_date < p_revision_start_date)
and (effective_end_date > p_revision_end_date))))
order by effective_start_date,
priority;
SELECT pbra.code_combination_id, pbra.gl_budget_version_id,
pbra.budget_group_id,
sum(decode(pbra.revision_type, 'D', -1 * pbra.revision_amount,
pbra.revision_amount)) sum_revision
FROM psb_budget_revision_lines pbrl, psb_budget_revision_accounts pbra
WHERE pbrl.budget_revision_id = p_budget_revision_id
AND pbra.budget_revision_acct_line_id = pbrl.budget_revision_acct_line_id
AND pbra.gl_period_name = p_gl_period_name
AND pbra.position_id IS NOT NULL
GROUP BY pbra.code_combination_id, pbra.gl_budget_version_id,
pbra.budget_group_id ;
Select period_name,
start_date,end_date
from gl_period_statuses
where application_id = 101
and set_of_books_id = g_set_of_books_id
and start_date between g_from_date and g_to_date
and end_date between g_from_date and g_to_date
and closing_status <> 'C'
/*Bug No. 4018446 Start*/
and adjustment_period_flag = 'N';
Select parameter_id,
name,
effective_start_date,
effective_end_date,
priority priority,
parameter_compound_annually,
currency_code
from PSB_PARAMETER_ASSIGNMENTS_V
where l_parameter_id is null
and parameter_set_id = g_parameter_set_id
and parameter_type = 'ACCOUNT'
and NVL(currency_code, g_currency_code) = g_currency_code
UNION
Select entity_id,
name,
effective_start_date,
effective_end_date,
0 priority,
parameter_compound_annually,
currency_code
from PSB_ENTITY
where entity_id = l_parameter_id
and entity_subtype = 'ACCOUNT'
and NVL(currency_code, g_currency_code) = g_currency_code
order by effective_start_date, priority;
select account_position_set_id, account_or_position_type, budget_group_id,
effective_start_date, effective_end_date
from PSB_SET_RELATIONS_V
where budget_group_id in
(select budget_group_id
from psb_budget_groups
where effective_start_date <= l_effective_start_date
and (effective_end_date is null or
effective_end_date >= l_effective_end_date)
start with budget_group_id = g_budget_group_id
connect by prior budget_group_id = parent_budget_group_id)
and account_or_position_type = 'A';
select a.position_id, a.effective_start_date, a.effective_end_date, a.budget_group_id
from PSB_POSITIONS a,
(select budget_group_id from PSB_BUDGET_GROUPS
start with budget_group_id = g_budget_group_id
connect by prior budget_group_id = parent_budget_group_id) b
where a.data_extract_id = p_data_extract_id
and a.budget_group_id = b.budget_group_id
and a.hr_position_id is not null;
select pbrp.position_id, pbrp.effective_start_date, pbrp.effective_end_date,
/* Bug No 1808330 Start */
pbrp.budget_revision_pos_line_id
/* Bug No 1808330 End */
from PSB_BUDGET_REVISION_POS_LINES pbrpl, PSB_BUDGET_REVISION_POSITIONS pbrp
where pbrpl.budget_revision_id = p_budget_revision_id
and pbrp.budget_revision_pos_line_id = pbrpl.budget_revision_pos_line_id;
Select period_name,
start_date,end_date
from gl_period_statuses
where application_id = 101
and set_of_books_id = g_set_of_books_id
and start_date between g_effective_start_date and g_effective_end_date
and end_date between g_effective_start_date and g_effective_end_date
and closing_status <> 'C'
/*Bug No. 4018446 Start*/
and adjustment_period_flag = 'N';
select period_name,
start_date,
end_date
from gl_period_statuses
where application_id = 101
and set_of_books_id = g_set_of_books_id
and startdate between start_date and end_date;
PROCEDURE Insert_Revision_Positions
( p_return_status OUT NOCOPY VARCHAR2,
p_budget_revision_pos_line_id OUT NOCOPY NUMBER,
p_budget_revision_id IN NUMBER,
p_position_id IN NUMBER,
p_budget_group_id IN NUMBER,
p_effective_start_date IN DATE,
p_effective_end_date IN DATE,
p_revision_type IN VARCHAR2,
p_revision_value_type IN VARCHAR2,
p_revision_value IN NUMBER,
p_note_id IN NUMBER,
p_freeze_flag IN VARCHAR2,
p_view_line_flag IN VARCHAR2
) IS
l_budget_revision_pos_line_id NUMBER;
select psb_budget_revision_pos_line_s.nextval seq
from dual;
INSERT INTO PSB_BUDGET_REVISION_POSITIONS
(budget_revision_pos_line_id, position_id, budget_group_id, effective_start_date,
effective_end_date, revision_type, revision_value_type, revision_value, note_id,
last_update_date, last_updated_by, last_update_login, created_by, creation_date)
VALUES (l_budget_revision_pos_line_id, p_position_id, p_budget_group_id, p_effective_start_date,
p_effective_end_date, p_revision_type, p_revision_value_type, p_revision_value, p_note_id,
sysdate, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID, FND_GLOBAL.USER_ID, sysdate);
INSERT INTO PSB_BUDGET_REVISION_POS_LINES (budget_revision_pos_line_id, budget_revision_id,
freeze_flag, view_line_flag, last_update_date, last_updated_by, last_update_login,
created_by, creation_date)
VALUES (l_budget_revision_pos_line_id, c_Distribute_Rev_Rec.budget_revision_id,
p_freeze_flag, p_view_line_flag, sysdate, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID, sysdate);
End Insert_Revision_Positions;
PROCEDURE Update_Revision_Positions
( p_return_status OUT NOCOPY VARCHAR2,
p_budget_revision_pos_line_id IN NUMBER,
p_budget_group_id IN NUMBER,
p_effective_start_date IN DATE := FND_API.G_MISS_DATE,
p_effective_end_date IN DATE := FND_API.G_MISS_DATE,
p_revision_type IN VARCHAR2,
p_revision_value_type IN VARCHAR2,
p_revision_value IN NUMBER,
p_note_id IN NUMBER
) IS
BEGIN
update PSB_BUDGET_REVISION_POSITIONS
set budget_group_id = p_budget_group_id,
effective_start_date = decode(p_effective_start_date, FND_API.G_MISS_DATE, effective_start_date, p_effective_start_date),
effective_end_date = decode(p_effective_end_date, FND_API.G_MISS_DATE, effective_end_date, p_effective_end_date),
revision_type = p_revision_type,
revision_value_type = p_revision_value_type,
revision_value = p_revision_value,
note_id = decode(p_note_id, null, note_id, p_note_id),
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE budget_revision_pos_line_id = p_budget_revision_pos_line_id;
End Update_Revision_Positions;
PROCEDURE Delete_Revision_Positions
( p_return_status OUT NOCOPY VARCHAR2,
p_budget_revision_pos_line_id IN NUMBER
) IS
BEGIN
delete from PSB_BUDGET_REVISION_POSITIONS
where budget_revision_pos_line_id = p_budget_revision_pos_line_id;
End Delete_Revision_Positions;
l_updated_record BOOLEAN;
select pbrp.*
from psb_budget_revision_positions pbrp,
psb_budget_revision_pos_lines pbrl
where pbrp.position_id = p_position_id
and ((((p_effective_end_date is not null)
and ((pbrp.effective_start_date <= p_effective_end_date)
and (pbrp.effective_end_date is null))
or ((pbrp.effective_start_date between p_effective_start_date and p_effective_end_date)
or (pbrp.effective_end_date between p_effective_start_date and p_effective_end_date)
or ((pbrp.effective_start_date < p_effective_start_date)
and (pbrp.effective_end_date > p_effective_end_date)))))
or ((p_effective_end_date is null)
and (nvl(pbrp.effective_end_date, p_effective_start_date) >= p_effective_start_date)))
and pbrl.budget_revision_id = p_budget_revision_id
and pbrp.budget_revision_pos_line_id = pbrl.budget_revision_pos_line_id;
update PSB_BUDGET_REVISION_POSITIONS brp
set budget_group_id = p_budget_group_id,
revision_type = p_revision_type,
revision_value_type = p_revision_value_type,
revision_value = p_revision_value,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where position_id = p_position_id
and effective_start_date = p_effective_start_date
and nvl(effective_end_date, FND_API.G_MISS_DATE) = nvl(p_effective_end_date, FND_API.G_MISS_DATE)
and exists
(select 1
from PSB_BUDGET_REVISION_POS_LINES brpl
where brpl.budget_revision_id = p_budget_revision_id
and brpl.budget_revision_pos_line_id = brp.budget_revision_pos_line_id);
g_revpos(l_init_index).delete_flag := null;
g_revpos(g_num_revpos).delete_flag := TRUE;
Insert_Revision_Positions
(p_return_status => l_return_status,
p_budget_revision_pos_line_id => l_budget_revision_pos_line_id,
p_budget_revision_id => p_budget_revision_id,
p_position_id => p_position_id,
p_budget_group_id => p_budget_group_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_revision_type => p_revision_type,
p_revision_value_type => p_revision_value_type,
p_revision_value => p_revision_value,
p_note_id => p_note_id,
p_freeze_flag => p_freeze_flag,
p_view_line_flag => p_view_line_flag);
l_updated_record := FALSE;
Update_Revision_Positions
(p_return_status => l_return_status,
p_budget_revision_pos_line_id => g_revpos(l_revpos_index).budget_revision_pos_line_id,
p_budget_group_id => p_budget_group_id,
p_effective_end_date => p_effective_end_date,
p_revision_type => g_revpos(l_revpos_index).revision_type,
p_revision_value_type => g_revpos(l_revpos_index).revision_value_type,
p_revision_value => g_revpos(l_revpos_index).revision_value,
p_note_id => g_revpos(l_revpos_index).note_id);
g_revpos(l_revpos_index).delete_flag := FALSE;
Update_Revision_Positions
(p_return_status => l_return_status,
p_budget_revision_pos_line_id => g_revpos(l_revpos_index).budget_revision_pos_line_id,
p_budget_group_id => p_budget_group_id,
p_effective_end_date => p_effective_start_date - 1,
p_revision_type => g_revpos(l_revpos_index).revision_type,
p_revision_value_type => g_revpos(l_revpos_index).revision_value_type,
p_revision_value => g_revpos(l_revpos_index).revision_value,
p_note_id => g_revpos(l_revpos_index).note_id);
l_updated_record := TRUE;
g_revpos(l_revpos_index).delete_flag := FALSE;
Update_Revision_Positions
(p_return_status => l_return_status,
p_budget_revision_pos_line_id => g_revpos(l_revpos_index).budget_revision_pos_line_id,
p_budget_group_id => p_budget_group_id,
p_effective_start_date => p_effective_end_date + 1,
p_revision_type => g_revpos(l_revpos_index).revision_type,
p_revision_value_type => g_revpos(l_revpos_index).revision_value_type,
p_revision_value => g_revpos(l_revpos_index).revision_value,
p_note_id => g_revpos(l_revpos_index).note_id);
l_updated_record := FALSE;
g_revpos(l_revpos_index).delete_flag := FALSE;
Insert_Revision_Positions
(p_return_status => l_return_status,
p_budget_revision_pos_line_id => l_budget_revision_pos_line_id,
p_budget_revision_id => p_budget_revision_id,
p_position_id => p_position_id,
p_budget_group_id => p_budget_group_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_revision_type => p_revision_type,
p_revision_value_type => p_revision_value_type,
p_revision_value => p_revision_value,
p_note_id => p_note_id,
p_freeze_flag => p_freeze_flag,
p_view_line_flag => p_view_line_flag);
if l_updated_record then
begin
Insert_Revision_Positions
(p_return_status => l_return_status,
p_budget_revision_pos_line_id => l_budget_revision_pos_line_id,
p_budget_revision_id => p_budget_revision_id,
p_position_id => p_position_id,
p_budget_group_id => p_budget_group_id,
p_effective_start_date => p_effective_end_date + 1,
p_effective_end_date => g_revpos(l_revpos_index).effective_end_date,
p_revision_type => p_revision_type,
p_revision_value_type => p_revision_value_type,
p_revision_value => p_revision_value,
p_note_id => p_note_id,
p_freeze_flag => p_freeze_flag,
p_view_line_flag => p_view_line_flag);
Update_Revision_Positions
(p_return_status => l_return_status,
p_budget_revision_pos_line_id => g_revpos(l_revpos_index).budget_revision_pos_line_id,
p_budget_group_id => p_budget_group_id,
p_effective_start_date => p_effective_end_date + 1,
p_effective_end_date => g_revpos(l_revpos_index).effective_end_date,
p_revision_type => g_revpos(l_revpos_index).revision_type,
p_revision_value_type => g_revpos(l_revpos_index).revision_value_type,
p_revision_value => g_revpos(l_revpos_index).revision_value,
p_note_id => g_revpos(l_revpos_index).note_id);
g_revpos(l_revpos_index).delete_flag := FALSE;
if g_revpos(l_revpos_index).delete_flag then
begin
Delete_Revision_Positions
(p_return_status => l_return_status,
p_budget_revision_pos_line_id => g_revpos(l_revpos_index).budget_revision_pos_line_id);
select pay_element_id,
name,
processing_type,
max_element_value_type,
max_element_value,
option_flag,
overwrite_flag,
salary_flag,
salary_type,
follow_salary,
period_type,
process_period_type
from PSB_PAY_ELEMENTS
where data_extract_id = g_data_extract_id
and business_group_id = g_business_group_id
and ((start_date >= p_start_date) and ((start_date <= p_end_date)
or (end_date is null))
or ((start_date between p_start_date and p_end_date))
or (p_start_date between start_date and nvl(end_date,p_end_date)))
order by salary_flag desc,
pay_element_id;
select code_combination_id,
distribution_percent,
effective_start_date,
effective_end_date
from PSB_POSITION_PAY_DISTRIBUTIONS
where position_id = p_position_id
and worksheet_id = p_budget_revision_id
and code_combination_id is not null
and chart_of_accounts_id = g_flex_code
and (((p_end_date is not null)
and (((effective_start_date <= p_end_date)
and (effective_end_date is null))
or ((effective_start_date between p_start_date and p_end_date)
or (effective_end_date between p_start_date and p_end_date)
or ((effective_start_date < p_start_date)
and (effective_end_date > p_end_date)))))
or ((p_end_date is null)
and (nvl(effective_end_date, p_start_date) >= p_start_date)))
order by distribution_percent desc;
select code_combination_id,
distribution_percent,
effective_start_date,
effective_end_date
from PSB_POSITION_PAY_DISTRIBUTIONS
where position_id = p_position_id
and worksheet_id is null
and code_combination_id is not null
and chart_of_accounts_id = g_flex_code
and (((p_end_date is not null)
and (((effective_start_date <= p_end_date)
and (effective_end_date is null))
or ((effective_start_date between p_start_date and p_end_date)
or (effective_end_date between p_start_date and p_end_date)
or ((effective_start_date < p_start_date)
and (effective_end_date > p_end_date)))))
or ((p_end_date is null)
and (nvl(effective_end_date, p_start_date) >= p_start_date)))
order by distribution_percent desc;
select a.budget_group_id,
b.num_proposed_years
from PSB_SET_RELATIONS a,
PSB_BUDGET_GROUPS b,
PSB_BUDGET_ACCOUNTS c
where a.budget_group_id = b.budget_group_id
and b.effective_start_date <= p_start_date
and (b.effective_end_date is null
or b.effective_end_date >= p_end_date)
and b.budget_group_type = 'R'
and ((b.budget_group_id = g_root_budget_group_id) or
(b.root_budget_group_id = g_root_budget_group_id))
and a.account_position_set_id = c.account_position_set_id
and c.code_combination_id = CCID;
| PROCEDURE Update_Baseline_Values |
+==========================================================================*/
PROCEDURE Update_Baseline_Values
( 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_revision_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Baseline_Values';
select position_fte_line_id, position_id, start_date,
end_date, fte
from psb_position_fte
where budget_revision_id = p_budget_revision_id;
select position_element_line_id, position_id, pay_element_id,
element_cost, start_date, end_date, currency_code
from PSB_POSITION_COSTS
where budget_revision_id = p_budget_revision_id;
select position_account_line_id, position_id, code_combination_id,
budget_group_id, amount, start_date, end_date, currency_code
from PSB_POSITION_ACCOUNTS
where budget_revision_id = p_budget_revision_id;
select *
from PSB_POSITION_ASSIGNMENTS
where worksheet_id = p_budget_revision_id
and assignment_type = 'ELEMENT';
select *
from PSB_POSITION_PAY_DISTRIBUTIONS
where worksheet_id = p_budget_revision_id;
select *
from PSB_POSITIONS
where position_id = l_position_id;
select *
from PSB_PAY_ELEMENT_RATES
where worksheet_id = p_budget_revision_id;
PSB_POSITIONS_PVT.UPDATE_ROW
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
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_position_id => l_position_id,
p_data_extract_id => c_positions_rec.data_extract_id,
p_position_definition_id => c_positions_rec.position_definition_id,
p_hr_position_id => c_positions_rec.hr_position_id,
p_hr_employee_id => c_positions_rec.hr_employee_id,
p_business_group_id => c_positions_rec.business_group_id,
p_budget_group_id => PSB_WS_POS1.g_salary_budget_group_id,
p_effective_start_DATE => c_positions_rec.effective_start_date,
p_effective_END_DATE => c_positions_rec.effective_end_date,
p_set_of_books_id => c_positions_rec.set_of_books_id,
p_vacant_position_flag => c_positions_rec.vacant_position_flag,
/*For Bug No : 1527423 Start*/
p_availability_status => c_positions_rec.availability_status,
/*For Bug No : 1527423 End*/
p_attribute1 => c_positions_rec.attribute1,
p_attribute2 => c_positions_rec.attribute2,
p_attribute3 => c_positions_rec.attribute3,
p_attribute4 => c_positions_rec.attribute4,
p_attribute5 => c_positions_rec.attribute5,
p_attribute6 => c_positions_rec.attribute6,
p_attribute7 => c_positions_rec.attribute7,
p_attribute8 => c_positions_rec.attribute8,
p_attribute9 => c_positions_rec.attribute9,
p_attribute10 => c_positions_rec.attribute10,
p_attribute11 => c_positions_rec.attribute11,
p_attribute12 => c_positions_rec.attribute12,
p_attribute13 => c_positions_rec.attribute13,
p_attribute14 => c_positions_rec.attribute14,
p_attribute15 => c_positions_rec.attribute15,
p_attribute16 => c_positions_rec.attribute16,
p_attribute17 => c_positions_rec.attribute17,
p_attribute18 => c_positions_rec.attribute18,
p_attribute19 => c_positions_rec.attribute19,
p_attribute20 => c_positions_rec.attribute20,
p_attribute_category => c_positions_rec.attribute_category,
p_name => c_positions_rec.name,
p_mode => 'R'
);
End Update_Baseline_Values;
SELECT base_line_version, position_fte_line_id, start_date, end_date, fte
FROM psb_position_fte
WHERE position_id = p_position_id
AND NVL (hr_budget_id, -1) = NVL (p_hr_budget_id, -1)
AND base_line_version IN ('O', 'C')
AND budget_revision_id IS NULL
AND (
start_date BETWEEN p_effective_start_date AND p_effective_end_date
OR end_date BETWEEN p_effective_start_date AND p_effective_end_date
OR (
start_date < p_effective_start_date
AND end_date > p_effective_end_date
)
);
SELECT position_fte_line_id, start_date, end_date, fte
FROM psb_position_fte
WHERE position_id = p_position_id
AND NVL (hr_budget_id, -1) = NVL (p_hr_budget_id, -1)
AND budget_revision_id = p_budget_revision_id
AND (
start_date BETWEEN p_effective_start_date AND p_effective_end_date
OR end_date BETWEEN p_effective_start_date AND p_effective_end_date
OR (
start_date < p_effective_start_date
AND end_date > p_effective_end_date
)
);
select period_name
from gl_period_statuses
where application_id = 101
and set_of_books_id = g_set_of_books_id
and p_effective_start_date between start_date and end_date;
select pbra.budget_revision_acct_line_id,
pbra.code_combination_id,
pbra.budget_group_id,
pbra.gl_period_name,
pbra.gl_budget_version_id,
pbra.currency_code,
pbra.budget_balance,
pbra.revision_type,
pbra.revision_value_type,
pbra.revision_amount,
pbrl.freeze_flag, pbrl.view_line_flag
from psb_budget_revision_accounts pbra, psb_budget_revision_lines pbrl
where pbrl.budget_revision_id = p_budget_revision_id
and pbra.budget_revision_acct_line_id = pbrl.budget_revision_acct_line_id
and position_id = p_position_id
and gl_period_name = l_gl_period;
select pbra.budget_revision_acct_line_id
from psb_budget_revision_accounts pbra, psb_budget_revision_lines pbrl
where pbrl.budget_revision_id = p_budget_revision_id
and pbra.budget_revision_acct_line_id = pbrl.budget_revision_acct_line_id
and pbra.revision_amount = 0;
Delete_Revision_Accounts
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_budget_revision_id => p_budget_revision_id,
p_budget_revision_acct_line_id => c_zero_accounts_rec.budget_revision_acct_line_id);
select a.segment1, a.segment2, a.segment3, a.segment4,
a.segment5, a.segment6, a.segment7, a.segment8,
a.segment9, a.segment10, a.segment11, a.segment12,
a.segment13, a.segment14, a.segment15, a.segment16,
a.segment17, a.segment18, a.segment19, a.segment20,
a.segment21, a.segment22, a.segment23, a.segment24,
a.segment25, a.segment26, a.segment27, a.segment28,
a.segment29, a.segment30, a.effective_start_date, a.effective_end_date
from PSB_PAY_ELEMENT_DISTRIBUTIONS a,
PSB_ELEMENT_POS_SET_GROUPS b,
PSB_SET_RELATIONS c,
PSB_BUDGET_POSITIONS d
where a.chart_of_accounts_id = g_flex_code
and (((a.effective_start_date <= p_revision_end_date)
and (a.effective_end_date is null))
or ((a.effective_start_date between p_revision_start_date and p_revision_end_date)
or (a.effective_end_date between p_revision_start_date and p_revision_end_date)
or ((a.effective_start_date < p_revision_start_date)
and (a.effective_end_date > p_revision_end_date))))
and a.position_set_group_id = b.position_set_group_id
and b.position_set_group_id = c.position_set_group_id
and b.pay_element_id = p_pay_element_id
and c.account_position_set_id = d.account_position_set_id
and d.data_extract_id = g_data_extract_id
and d.position_id = p_position_id;
select a.code_combination_id,
a.distribution_percent, a.effective_start_date, a.effective_end_date
from PSB_PAY_ELEMENT_DISTRIBUTIONS a,
PSB_ELEMENT_POS_SET_GROUPS b,
PSB_SET_RELATIONS c,
PSB_BUDGET_POSITIONS d
where a.chart_of_accounts_id = g_flex_code
and (((a.effective_start_date <= p_revision_end_date)
and (a.effective_end_date is null))
or ((a.effective_start_date between p_revision_start_date and p_revision_end_date)
or (a.effective_end_date between p_revision_start_date and p_revision_end_date)
or ((a.effective_start_date < p_revision_start_date)
and (a.effective_end_date > p_revision_end_date))))
and a.position_set_group_id = b.position_set_group_id
and b.position_set_group_id = c.position_set_group_id
and b.pay_element_id = p_pay_element_id
and c.account_position_set_id = d.account_position_set_id
and d.data_extract_id = g_data_extract_id
and d.position_id = p_position_id
order by a.distribution_percent desc;
| PROCEDURE Update_Position_Cost |
+===========================================================================*/
PROCEDURE Update_Position_Cost
( p_return_status OUT NOCOPY VARCHAR2
, p_mass_revision IN BOOLEAN
, p_position_id IN NUMBER
, p_hr_budget_id IN NUMBER
, p_budget_revision_id IN NUMBER
, p_revision_start_date IN DATE
, p_revision_end_date IN DATE
-- Added p_zero_revised_fte for bug 2896687
, p_zero_revised_fte IN BOOLEAN := FALSE
, p_parameter_id IN NUMBER -- Bug#4675858
)
IS
--
l_return_status VARCHAR2(1);
SELECT code_combination_id, start_date, end_date, amount
FROM psb_position_accounts
WHERE position_id = p_position_id
AND NVL (hr_budget_id, -1) = NVL (p_hr_budget_id, -1)
AND currency_code = g_func_currency
AND base_line_version = 'C'
AND (
start_date BETWEEN p_revision_start_date AND p_revision_end_date
OR end_date BETWEEN p_revision_start_date AND p_revision_end_date
OR (
start_date < p_revision_start_date
AND end_date > p_revision_end_date
)
);
SELECT period_name, start_date, end_date
FROM gl_period_statuses
WHERE application_id = 101
AND set_of_books_id = g_set_of_books_id
AND p_revision_start_date BETWEEN start_date AND end_date;
SELECT name
FROM psb_entity
WHERE entity_id = p_parameter_id ;
SELECT
budget_revision_pos_line_id
INTO
l_pos_line_id
FROM
psb_budget_revision_position_v
WHERE
budget_revision_id = p_budget_revision_id
AND position_id = p_position_id ;
End Update_Position_Cost;
select position_id,
name,
effective_start_date,
effective_end_date
from PSB_POSITIONS
where position_id = p_position_id;
select worksheet_id,
pay_element_id,
pay_element_option_id,
pay_basis,
element_value_type,
element_value,
effective_start_date,
effective_end_date
from PSB_POSITION_ASSIGNMENTS
where ((worksheet_id = g_global_budget_revision_id) or (worksheet_id is null))
and currency_code = g_func_currency
and assignment_type = 'ELEMENT'
and (((effective_start_date <= l_end_date)
and (effective_end_date is null))
or ((effective_start_date between l_start_date and l_end_date)
or (effective_end_date between l_start_date and l_end_date)
or ((effective_start_date < l_start_date)
and (effective_end_date > l_end_date))))
and position_id = p_position_id
order by effective_start_date, effective_end_date, element_value desc;
select a.worksheet_id,
a.pay_element_id,
a.pay_element_option_id,
a.pay_basis,
a.element_value_type,
a.element_value,
a.formula_id,
a.effective_start_date,
a.effective_end_date
from PSB_PAY_ELEMENT_RATES a,
PSB_PAY_ELEMENTS b
where (a.worksheet_id is null or a.worksheet_id = g_global_budget_revision_id)
and a.currency_code = g_func_currency
and exists
(select 1
from PSB_POSITION_ASSIGNMENTS c
where nvl(c.pay_element_option_id, FND_API.G_MISS_NUM) = nvl(a.pay_element_option_id, FND_API.G_MISS_NUM)
and ((c.worksheet_id = g_global_budget_revision_id) or (c.worksheet_id is null))
and c.currency_code = g_func_currency
and (((c.effective_start_date <= l_end_date)
and (c.effective_end_date is null))
or ((c.effective_start_date between l_start_date and l_end_date)
or (c.effective_end_date between l_start_date and l_end_date)
or ((c.effective_start_date < l_start_date)
and (c.effective_end_date > l_end_date))))
and c.pay_element_id = a.pay_element_id
and c.position_id = p_position_id)
and (((a.effective_start_date <= l_end_date)
and (a.effective_end_date is null))
or ((a.effective_start_date between l_start_date and l_end_date)
or (a.effective_end_date between l_start_date and l_end_date)
or ((a.effective_start_date < l_start_date)
and (a.effective_end_date > l_end_date))))
and a.pay_element_id = b.pay_element_id
and b.business_group_id = g_business_group_id
and b.data_extract_id = g_data_extract_id
order by a.worksheet_id, a.effective_start_date, a.effective_end_date, a.element_value desc;
select worksheet_id,
effective_start_date,
effective_end_date,
attribute_id,
-- Fixed bug # 3683644
FND_NUMBER.canonical_to_number(attribute_value) attribute_value,
attribute_value_id
from PSB_POSITION_ASSIGNMENTS
where attribute_id in (PSB_WS_POS1.g_default_wklyhrs_id, PSB_WS_POS1.g_fte_id)
and (( worksheet_id = g_global_budget_revision_id) or (worksheet_id is null))
and assignment_type = 'ATTRIBUTE'
and (((effective_start_date <= l_end_date)
and (effective_end_date is null))
or ((effective_start_date between l_start_date and l_end_date)
or (effective_end_date between l_start_date and l_end_date)
or ((effective_start_date < l_start_date)
and (effective_end_date > l_end_date))))
and position_id = p_position_id
order by worksheet_id,
effective_start_date,
effective_end_date,
FND_NUMBER.canonical_to_number(attribute_value) desc; -- Fixed bug # 3683644
select brp.revision_type, brp.revision_value_type, brp.revision_value, brp.effective_start_date, brp.effective_end_date
from PSB_BUDGET_REVISION_POSITIONS brp, PSB_BUDGET_REVISION_POS_LINES brpl
where brp.position_id = p_position_id
and ((effective_start_date between p_revision_start_date and p_revision_end_date)
or (effective_end_date between p_revision_start_date and p_revision_end_date)
or ((effective_start_date < p_revision_start_date)
and (effective_end_date > p_revision_end_date)))
and brpl.budget_revision_id = p_budget_revision_id
and brpl.budget_revision_pos_line_id = brp.budget_revision_pos_line_id
and brp.revision_value is not null;
select start_date, end_date, fte
from PSB_POSITION_FTE
where position_id = p_position_id
and nvl(hr_budget_id, -1) = nvl(g_hr_budget_id, -1)
and base_line_version = 'C'
and ((start_date between p_revision_start_date and p_revision_end_date)
or (end_date between p_revision_start_date and p_revision_end_date)
or ((start_date < p_revision_start_date)
and (end_date > p_revision_end_date)));
IF g_last_update_flag_tbl.exists (p_position_id) AND
g_last_update_flag_tbl (p_position_id) = 1 THEN
FOR l_acc_line_rec IN
(SELECT COUNT(1) acct_line_cnt
FROM psb_budget_revision_accounts
WHERE budgeT_revision_acct_line_id
IN (SELECT budget_revision_Acct_line_id
FROM psb_budget_revision_lines
WHERE budget_revision_id = p_budget_revision_id)
AND position_id = p_position_id)
LOOP
l_acct_line_cnt := l_acc_line_rec.acct_line_cnt;
g_last_update_flag_tbl(p_position_id) := 0;
Update_Position_Cost
( p_return_status => l_return_status
, p_mass_revision => p_mass_revision
, p_position_id => p_position_id
, p_hr_budget_id => g_hr_budget_id
, p_budget_revision_id => p_budget_revision_id
, p_revision_start_date => l_start_date
, p_revision_end_date => l_end_date
-- Added p_zero_revised_fte for bug 2896687
, p_zero_revised_fte => l_zero_revised_fte
, p_parameter_id => p_parameter_id -- Bug#4675858
) ;
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 Sum(decode(a.revision_type,'I',nvl(a.revision_amount, 0),'D',-nvl(a.revision_amount,0))) Sum_Acc
from PSB_BUDGET_REVISION_LINES b,
PSB_BUDGET_REVISION_ACCOUNTS a
where b.budget_revision_id = p_budget_revision_id
and a.budget_revision_acct_line_id = b.budget_revision_acct_line_id
and a.currency_code = p_currency_code
and a.position_id is null
and exists
(select 1
from psb_budget_accounts d,
psb_set_relations_v e
where d.account_position_set_id = e.account_position_set_id
and d.code_combination_id = a.code_combination_id
and e.account_or_position_type = 'A'
and e.constraint_id = p_constraint_id);
select Sum(decode(a.revision_type,'I',nvl(a.revision_amount, 0),'D',-nvl(a.revision_amount,0))) Sum_Acc
from PSB_BUDGET_REVISION_ACCOUNTS a,
PSB_BUDGET_REVISION_LINES b
where a.code_combination_id = CCID
and a.currency_code = p_currency_code
and a.position_id is null
and b.budget_revision_id = p_budget_revision_id
and a.budget_revision_acct_line_id = b.budget_revision_acct_line_id;
select sum(budget_balance) original_balance
from psb_budget_revision_accounts pbra,
psb_budget_revision_lines pbrl,
psb_budget_revisions pbr
where pbra.code_combination_id = CCID
and pbra.currency_code = Currency
and pbra.position_id is null
and pbra.budget_revision_acct_line_id = pbrl.budget_revision_acct_line_id
and pbrl.budget_revision_id = pbr.budget_revision_id
and pbr.budget_group_id = g_budget_group_id
and pbr.base_line_revision = 'Y'
and pbra.gl_period_name in
(select period_name
from gl_period_statuses
where application_id = 101
and set_of_books_id = g_set_of_books_id
and start_date between g_from_date and g_to_date
and end_date between g_from_date and g_to_date);
select sum(budget_balance) original_balance
from psb_budget_revision_accounts pbra,
psb_budget_revision_lines pbrl,
psb_budget_revisions pbr
where pbra.code_combination_id in
(select d.code_combination_id
from psb_budget_accounts d,
psb_set_relations_v e
where d.account_position_set_id = e.account_position_set_id
and d.code_combination_id = pbra.code_combination_id
and e.account_or_position_type = 'A'
and e.constraint_id = p_constraint_id)
and pbra.currency_code = Currency
and pbra.position_id is null
and pbra.budget_revision_acct_line_id = pbrl.budget_revision_acct_line_id
and pbrl.budget_revision_id = pbr.budget_revision_id
and pbr.budget_group_id = g_budget_group_id
and pbr.base_line_revision = 'Y'
and pbra.gl_period_name in
(select period_name
from gl_period_statuses
where application_id = 101
and set_of_books_id = g_set_of_books_id
and start_date between g_from_date and g_to_date
and end_date between g_from_date and g_to_date);
SELECT pbra.gl_budget_version_id,
pbra.gl_period_name -- Bug 5148786
FROM psb_budget_revision_accounts pbra,
psb_budget_revision_lines pbrl
WHERE pbra.code_combination_id = CCID
AND pbra.position_id is null
AND pbrl.budget_revision_id = p_budget_revision_id
AND pbrl.budget_revision_acct_line_id = pbra.budget_revision_acct_line_id;
SELECT pbra.code_combination_id,
pbra.gl_budget_version_id,
pbra.gl_period_name -- Bug 5148786
FROM psb_budget_revision_accounts pbra,
psb_budget_revision_lines pbrl
WHERE pbra.code_combination_id in
(select d.code_combination_id
from psb_budget_accounts d,
psb_set_relations_v e
where d.account_position_set_id = e.account_position_set_id
and d.code_combination_id = pbra.code_combination_id
and e.account_or_position_type = 'A'
and e.constraint_id = p_constraint_id)
and pbra.position_id is null
and pbrl.budget_revision_id = p_budget_revision_id
and pbra.budget_revision_acct_line_id = pbrl.budget_revision_acct_line_id;
FND_MSG_PUB.Delete_Msg;
insert into PSB_ERROR_MESSAGES
(Concurrent_Request_ID,
Process_ID,
Source_Process,
Description,
Creation_Date,
Created_By)
values (FND_GLOBAL.CONC_REQUEST_ID,
p_budget_revision_id,
'BUDGET_REVISION',
l_description,
sysdate,
FND_GLOBAL.USER_ID);
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.budget_group_id = d.budget_group_id
start with e.budget_group_id = g_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 constraint_set_id = g_constraint_set_id
and currency_code = g_currency_code -- Bug 3029168
order by severity_level desc;
select constraint_id,
name,
currency_code,
severity_level,
effective_start_date,
effective_end_date
from PSB_CONSTRAINT_ASSIGNMENTS_V
where constraint_type = 'ELEMENT'
and (((effective_start_date <= g_effective_end_date)
and (effective_end_date is null))
or ((effective_start_date between g_effective_start_date and g_effective_end_date)
or (effective_end_date between g_effective_start_date and g_effective_end_date)
or ((effective_start_date < g_effective_start_date)
and (effective_end_date > g_effective_end_date))))
and constraint_set_id = g_constraint_set_id;
select name grade_name,
grade_step
from PSB_PAY_ELEMENT_OPTIONS
where pay_element_option_id = p_pay_element_option_id;
select a.name position_name,
b.name,
b.grade_step
from PSB_POSITIONS a,
PSB_PAY_ELEMENT_OPTIONS b,
PSB_POSITION_ASSIGNMENTS c
where exists
(select 1
from PSB_BUDGET_POSITIONS d,
PSB_SET_RELATIONS e
where d.data_extract_id = g_data_extract_id
and d.position_id = c.position_id
and d.account_position_set_id = e.account_position_set_id
and e.constraint_id = p_constraint_id)
and a.position_id = c.position_id
and b.pay_element_option_id = c.pay_element_option_id
and c.pay_element_option_id <> p_pay_element_option_id
and ((c.worksheet_id is null) or (c.worksheet_id = p_budget_revision_id))
and c.pay_element_id = p_pay_element_id;
select a.name,
a.grade_step
from PSB_PAY_ELEMENT_OPTIONS a,
PSB_POSITION_ASSIGNMENTS b
where a.pay_element_option_id = b.pay_element_option_id
and b.pay_element_option_id <> p_pay_element_option_id
and b.pay_element_id = p_pay_element_id
and b.position_id = p_position_id;
select sum(nvl(a.element_cost, 0)) Sum_Elem
from PSB_POSITION_COSTS a
where exists
(select 1
from PSB_BUDGET_REVISION_POSITIONS c,
PSB_BUDGET_REVISION_POS_LINES d,
PSB_BUDGET_POSITIONS e,
PSB_SET_RELATIONS f
where d.budget_revision_id = a.budget_revision_id
and c.budget_revision_pos_line_id = d.budget_revision_pos_line_id
and d.budget_revision_id = p_budget_revision_id
and c.position_id = e.position_id
and e.data_extract_id = g_data_extract_id
and e.account_position_set_id = f.account_position_set_id
and f.constraint_id = p_constraint_id)
and a.currency_code = p_currency_code
and a.pay_element_id = p_pay_element_id
and a.budget_revision_id = p_budget_revision_id;
select sum(nvl(a.element_cost, 0)) Sum_Elem
from PSB_POSITION_COSTS a,
PSB_PAY_ELEMENTS c
where exists
(select 1
from PSB_BUDGET_REVISION_POSITIONS d,
PSB_BUDGET_REVISION_POS_LINES e,
PSB_BUDGET_POSITIONS f,
PSB_SET_RELATIONS g
where e.budget_revision_id = a.budget_revision_id
and d.budget_revision_pos_line_id = e.budget_revision_pos_line_id
and e.budget_revision_id = p_budget_revision_id
and d.position_id = f.position_id
and f.data_extract_id = g_data_extract_id
and f.account_position_set_id = g.account_position_set_id
and g.constraint_id = p_constraint_id)
and a.currency_code = p_currency_code
and a.pay_element_id = c.pay_element_id
and a.budget_revision_id = p_budget_revision_id
and c.processing_type = 'R'
and c.salary_flag = 'Y'
and c.business_group_id = g_business_group_id
and c.data_extract_id = g_data_extract_id;
select sum(nvl(a.element_cost, 0)) Sum_Elem
from PSB_POSITION_COSTS a
where a.currency_code = p_currency_code
and a.pay_element_id = p_pay_element_id
and a.position_id = p_position_id
and a.budget_revision_id = p_budget_revision_id;
select sum(nvl(a.element_cost, 0)) Sum_Elem
from PSB_POSITION_COSTS a,
PSB_PAY_ELEMENTS c
where a.currency_code = p_currency_code
and a.pay_element_id = c.pay_element_id
and a.position_id = p_position_id
and a.budget_revision_id = p_budget_revision_id
and c.processing_type = 'R'
and c.salary_flag = 'Y'
and c.business_group_id = g_business_group_id
and c.data_extract_id = g_data_extract_id;
FND_MSG_PUB.Delete_Msg;
insert into PSB_ERROR_MESSAGES
(Concurrent_Request_ID,
Process_ID,
Source_Process,
Description,
Creation_Date,
Created_By)
values (FND_GLOBAL.CONC_REQUEST_ID,
p_budget_revision_id,
'BUDGET_REVISION',
l_description,
sysdate,
FND_GLOBAL.USER_ID);
FND_MSG_PUB.Delete_Msg;
insert into PSB_ERROR_MESSAGES
(Concurrent_Request_ID,
Process_ID,
Source_Process,
Description,
Creation_Date,
Created_By)
values (FND_GLOBAL.CONC_REQUEST_ID,
p_budget_revision_id,
'BUDGET_REVISION',
l_description,
sysdate,
FND_GLOBAL.USER_ID);
FND_MSG_PUB.Delete_Msg;
insert into PSB_ERROR_MESSAGES
(Concurrent_Request_ID,
Process_ID,
Source_Process,
Description,
Creation_Date,
Created_By)
values (FND_GLOBAL.CONC_REQUEST_ID,
p_budget_revision_id,
'BUDGET_REVISION',
l_description,
sysdate,
FND_GLOBAL.USER_ID);
select pay_element_id,
pay_element_option_id,
prefix_operator,
nvl(currency_code, p_currency_code) currency_code,
element_value_type,
element_value
from PSB_CONSTRAINT_FORMULAS
where constraint_id = p_constraint_id
order by step_number;
select prefix_operator,
amount
from PSB_CONSTRAINT_FORMULAS
where constraint_id = p_constraint_id;
select sum(nvl(a.fte, 0)) Sum_FTE
from PSB_POSITION_FTE a
where exists
(select 1
from PSB_BUDGET_REVISION_POSITIONS c,
PSB_BUDGET_REVISION_POS_LINES d,
PSB_BUDGET_POSITIONS e,
PSB_SET_RELATIONS f
where c.budget_revision_pos_line_id = d.budget_revision_pos_line_id
and d.budget_revision_id = p_budget_revision_id
and c.position_id = e.position_id
and e.data_extract_id = g_data_extract_id
and e.account_position_set_id = f.account_position_set_id
and f.constraint_id = p_constraint_id)
and a.budget_revision_id = p_budget_revision_id;
select sum(nvl(fte, 0)) Sum_FTE
from PSB_POSITION_FTE
where position_id = p_position_id
and budget_revision_id = p_budget_revision_id;
FND_MSG_PUB.Delete_Msg;
insert into PSB_ERROR_MESSAGES
(Concurrent_Request_ID,
Process_ID,
Source_Process,
Description,
Creation_Date,
Created_By)
values (FND_GLOBAL.CONC_REQUEST_ID,
p_budget_revision_id,
'BUDGET_REVISION',
l_description,
sysdate,
FND_GLOBAL.USER_ID);
select d.position_id,
c.name
from PSB_BUDGET_REVISION_POSITIONS a,
PSB_BUDGET_REVISION_POS_LINES b,
PSB_POSITIONS c,
PSB_BUDGET_POSITIONS d,
PSB_SET_RELATIONS e
where a.budget_revision_pos_line_id = b.budget_revision_pos_line_id
and b.budget_revision_id = p_budget_revision_id
and a.position_id = c.position_id
and c.position_id = d.position_id
and d.data_extract_id = g_data_extract_id
and d.account_position_set_id = e.account_position_set_id
and e.constraint_id = p_constraint_id;
select constraint_id,
name,
currency_code,
severity_level,
fte_constraint,
effective_start_date,
effective_end_date,
constraint_detailed_flag
from PSB_CONSTRAINT_ASSIGNMENTS_V
where constraint_type = 'POSITION'
and constraint_set_id = g_constraint_set_id
order by severity_level desc;
delete from PSB_ERROR_MESSAGES
where source_process = 'BUDGET_REVISION'
and process_id = p_budget_revision_id;
Update PSB_BUDGET_REVISIONS
set constraint_set_id = g_constraint_set_id,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where budget_revision_id = p_budget_revision_id;
PROCEDURE Delete_Revision_Positions
( 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_revision_id IN NUMBER ,
p_budget_revision_pos_line_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Revision_Positions';
select pbr.global_budget_revision
from psb_budget_revisions pbr
where pbr.budget_revision_id = p_budget_revision_id;
select position_id, effective_start_date, effective_end_date
from psb_budget_revision_positions
where budget_revision_pos_line_id = p_budget_revision_pos_line_id;
delete from PSB_POSITION_ASSIGNMENTS pa
where pa.position_id = l_position_id
and pa.worksheet_id = p_budget_revision_id
and pa.data_extract_id = g_data_extract_id;
DELETE PSB_BUDGET_REVISION_POSITIONS
WHERE budget_revision_pos_line_id = p_budget_revision_pos_line_id;
DELETE PSB_BUDGET_REVISION_POS_LINES
WHERE budget_revision_pos_line_id = p_budget_revision_pos_line_id;
DELETE PSB_BUDGET_REVISION_POS_LINES
WHERE budget_revision_pos_line_id = p_budget_revision_pos_line_id
AND budget_revision_id = p_budget_revision_id;
End Delete_Revision_Positions;
PROCEDURE Delete_Revision_Accounts
( 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_revision_id IN NUMBER ,
p_budget_revision_acct_line_id IN NUMBER)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Revision_Accounts';
Select pbr.global_budget_revision
from psb_budget_revisions pbr
where pbr.budget_revision_id = p_budget_revision_id;
Select pbrl.budget_revision_acct_line_id
from psb_budget_revision_lines pbrl
where pbrl.budget_revision_id = p_budget_revision_id
and pbrl.budget_revision_acct_line_id = p_budget_revision_acct_line_id;
Delete PSB_BUDGET_REVISION_ACCOUNTS
where budget_revision_acct_line_id = p_budget_revision_acct_line_id;
Delete PSB_BUDGET_REVISION_LINES
where budget_revision_acct_line_id = p_budget_revision_acct_line_id;
Delete PSB_BUDGET_REVISION_LINES
where budget_revision_acct_line_id = p_budget_revision_acct_line_id
and budget_revision_id = p_budget_revision_id;
End Delete_Revision_Accounts;
| PROCEDURE Delete_Budget_Revision_Pvt ( Private ) |
+===========================================================================*/
--
-- This API deletes an official budget_revision by performing deletes on
-- psb_budget_revisions and matrix tables (psb_budget_revision_lines and
-- psb_budget_revision_pos_lines).
-- It also deletes budget_revision related data from other tables.
--
PROCEDURE Delete_Budget_Revision_Pvt
(
p_budget_revision_id IN NUMBER,
p_revise_by_position IN VARCHAR2,
p_budget_group_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2
)
IS
--
l_api_name CONSTANT VARCHAR2(30):= 'Delete_Budget_Revision_Pvt';
SELECT budget_revision_acct_line_id
FROM psb_budget_revision_lines
WHERE budget_revision_id = p_budget_revision_id;
SELECT budget_revision_pos_line_id
FROM psb_budget_revision_pos_lines
WHERE budget_revision_id = p_budget_revision_id;
SELECT distribution_id
FROM psb_ws_distributions
WHERE worksheet_id = p_budget_revision_id
AND distribution_option_flag = 'R';
SELECT position_assignment_id, pay_element_rate_id
FROM psb_position_assignments
WHERE worksheet_id = p_budget_revision_id
AND data_extract_id = l_data_extract_id
GROUP BY position_assignment_id, pay_element_rate_id;
SELECT position_id
FROM psb_positions pp
WHERE pp.data_extract_id = l_data_extract_id
AND nvl(pp.new_position_flag, 'N') = 'Y'
AND EXISTS (SELECT 1
FROM psb_budget_revision_positions brp,
psb_budget_revision_pos_lines brpl,
psb_budget_revisions br
WHERE br.budget_revision_id = p_budget_revision_id
AND br.budget_revision_id = brpl.budget_revision_id
AND brpl.budget_revision_pos_line_id = brp.budget_revision_pos_line_id
AND brp.position_id = pp.position_id
);
PSB_BUDGET_REVISIONS_PVT.Delete_Revision_Accounts
( p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE,
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_budget_revision_id => p_budget_revision_id,
p_budget_revision_acct_line_id => l_account_line_id);
DELETE psb_position_assignments
WHERE position_id = l_br_pos_csr_rec.position_id;
DELETE psb_positions
WHERE position_id = l_br_pos_csr_rec.position_id;
PSB_BUDGET_REVISIONS_PVT.Delete_Revision_Positions
( p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE,
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_budget_revision_id => p_budget_revision_id,
p_budget_revision_pos_line_id => l_position_line_id);
DELETE psb_ws_distribution_details
WHERE distribution_id = l_br_distribution_rec.distribution_id;
DELETE psb_ws_distributions
WHERE distribution_id = l_br_distribution_rec.distribution_id;
DELETE psb_workflow_processes
WHERE worksheet_id = p_budget_revision_id
AND document_type = 'BR' ;
DELETE psb_position_assignments
WHERE position_assignment_id = l_br_position_rec.position_assignment_id;
DELETE psb_pay_element_rates
WHERE pay_element_rate_id = l_br_position_rec.pay_element_rate_id;
DELETE psb_pay_element_rates
WHERE worksheet_id = p_budget_revision_id ;
DELETE psb_position_accounts
WHERE budget_revision_id = p_budget_revision_id ;
DELETE psb_position_fte
WHERE budget_revision_id = p_budget_revision_id ;
DELETE psb_position_costs
WHERE budget_revision_id = p_budget_revision_id ;
DELETE psb_ws_submit_comments
WHERE worksheet_id = p_budget_revision_id ;
fnd_attached_documents2_pkg.delete_attachments
(X_entity_name => 'PSB_BUDGET_REVISIONS',
X_pk1_value => p_budget_revision_id,
X_delete_document_flag => 'Y'
);
PSB_BUDGET_REVISIONS_PVT.Delete_Row
(p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE,
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_budget_revision_id => p_budget_revision_id);
END Delete_Budget_Revision_Pvt ;
| PROCEDURE Delete_Budget_Revision |
+===========================================================================*/
--
-- The API This API deletes a local or global budget revision.
--
PROCEDURE Delete_Budget_Revision
(
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_revision_id IN NUMBER
)
IS
--
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Budget_Revision';
SAVEPOINT Delete_Budget_Revision;
SELECT NVL( global_budget_revision, 'N') ,
NVL( revise_by_position, 'N'),
budget_group_id
INTO
l_global_budget_revision,
l_revise_by_position,
l_budget_group_id
FROM psb_budget_revisions
WHERE budget_revision_id = p_budget_revision_id ;
SELECT budget_revision_id
FROM psb_budget_revisions
WHERE global_budget_revision_id = p_budget_revision_id
AND NVL( global_budget_revision, 'N' ) = 'N'
)
LOOP
--
PSB_Create_BR_Pvt.Enforce_BR_Concurrency
(
p_api_version => 1.0 ,
p_init_msg_list => 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_budget_revision_id => l_budget_revision_rec.budget_revision_id,
p_parent_or_child_mode => 'CHILD',
p_maintenance_mode => 'MAINTENANCE'
);
SELECT budget_revision_id, revise_by_position, budget_group_id
FROM psb_budget_revisions
WHERE global_budget_revision_id = p_budget_revision_id
AND NVL( global_budget_revision, 'N' ) = 'N'
)
LOOP
--
Delete_Budget_Revision_Pvt
(
p_budget_revision_id => l_budget_revision_rec.budget_revision_id,
p_revise_by_position => l_budget_revision_rec.revise_by_position,
p_budget_group_id => l_budget_revision_rec.budget_group_id,
p_return_status => l_return_status
) ;
Delete_Budget_Revision_Pvt
(
p_budget_revision_id => p_budget_revision_id ,
p_revise_by_position => l_revise_by_position ,
p_budget_group_id => l_budget_group_id,
p_return_status => l_return_status
) ;
Delete_Budget_Revision_Pvt
(
p_budget_revision_id => l_budget_revisions_tab(i),
p_revise_by_position => l_revise_by_position,
p_budget_group_id => l_budget_group_id,
p_return_status => l_return_status
) ;
ROLLBACK TO Delete_Budget_Revision ;
ROLLBACK TO Delete_Budget_Revision ;
ROLLBACK TO Delete_Budget_Revision ;
END Delete_Budget_Revision ;
Select budget_group_id,currency_code -- Bug 3029168 added currency_code
from psb_budget_revisions
where budget_revision_id = p_budget_revision_id;
Select budget_group_id,currency_code -- Bug 3029168 added currency code
from psb_budget_revisions
where budget_revision_id = p_budget_revision_id;
| PROCEDURE Delete_Budget_Revision_CP |
+===========================================================================*/
--
-- This is the execution file for the concurrent program 'Maintain Budget
-- Account Codes'.
--
PROCEDURE Delete_Budget_Revision_CP
( errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_from_budget_revision_id IN NUMBER,
p_to_budget_revision_id IN NUMBER,
p_submission_status IN VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Budget_Revision_CP' ;
SELECT budget_revision_id
FROM psb_budget_revisions
WHERE budget_revision_id BETWEEN p_from_budget_revision_id
AND p_to_budget_revision_id
AND submission_status = p_submission_status;
SELECT budget_revision_id
FROM psb_budget_revisions
WHERE budget_revision_id BETWEEN p_from_budget_revision_id
AND p_to_budget_revision_id;
SAVEPOINT Delete_Budget_Revision_CP_Pvt ;
Delete_Budget_Revision
(
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_budget_revision_id => l_budget_revisions_rec.budget_revision_id
);
Delete_Budget_Revision
(
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_budget_revision_id => l_budget_revisions_rec.budget_revision_id
);
ROLLBACK TO Delete_Budget_Revision_CP_Pvt ;
ROLLBACK TO Delete_Budget_Revision_CP_Pvt ;
ROLLBACK TO Delete_Budget_Revision_CP_Pvt ;
END Delete_Budget_Revision_CP ;
SELECT 'exists' result FROM dual WHERE EXISTS
(
SELECT bra.code_combination_id
FROM PSB_BUDGET_REVISION_LINES brl,
PSB_BUDGET_REVISION_ACCOUNTS bra
WHERE brl.budget_revision_id = p_budget_revision_id
AND bra.budget_revision_acct_line_id = brl.budget_revision_acct_line_id
/*For Bug No : 2161125 Start*/
AND bra.position_id IS NULL
/*For Bug No : 2161125 End*/
AND EXISTS (SELECT 1
FROM PSB_BUDGET_ACCOUNTS ba,
PSB_SET_RELATIONS_V sr
WHERE ba.code_combination_id = bra.code_combination_id
AND ba.account_position_set_id = sr.account_position_set_id
AND sr.rule_id = l_rule_id
AND sr.account_or_position_type = 'A'
AND sr.apply_balance_flag = 'A'
)
AND EXISTS (SELECT 1
FROM PSB_ENTITY ent
WHERE ent.entity_id = l_rule_id
AND ( (ent.apply_account_set_flag = 'B')
/* Bug No 2144364 Start */
---- OR (ent.apply_account_set_flag = bra.revision_type)
OR (ent.apply_account_set_flag =
DECODE(SIGN(bra.revision_amount), -1,
DECODE(bra.revision_type, 'I', 'D', 'I'), bra.revision_type)
)
/* Bug No 2144364 End */
)
)
/* Bug No 2133484 Start */
AND EXISTS (SELECT 1
FROM PSB_RULE_TRANSACTION_TYPE rtt
WHERE rtt.rule_id = l_rule_id
AND rtt.transaction_type = l_transaction_type
-- Next 1 line added for Bug # 2123930
AND rtt.enable_flag = 'Y'
)
/* Bug No 2133484 End */
/* Bug No 2135165 Start */
AND EXISTS (SELECT 1 FROM PSB_ENTITY_ASSIGNMENT ea, gl_sets_of_books sob, gl_periods_v gp
WHERE ea.entity_set_id = g_brr_rule_set_id
AND ea.entity_id = l_rule_id
AND sob.set_of_books_id = g_brr_sob_id
AND gp.period_set_name = sob.period_set_name
AND gp.adjustment_period_flag = 'N'
AND gp.period_name = bra.gl_period_name
AND (((ea.effective_start_date <= gp.end_date)
AND (ea.effective_end_date IS NULL))
OR ((ea.effective_start_date BETWEEN gp.start_date AND gp.end_date)
OR (ea.effective_end_date BETWEEN gp.start_date AND gp.end_date)
OR ((ea.effective_start_date < gp.start_date)
AND (ea.effective_end_date > gp.end_date))))
)
/* Bug No 2135165 End */
);
SELECT rra.rule_set_id, rra.rule_id, rra.name, rra.rule_type,
rra.severity_level, rra.apply_account_set_flag,
rra.balance_account_set_flag
FROM PSB_REVISION_RULE_ASSIGNMENT_V rra
WHERE rra.rule_set_id IN (
SELECT rrs.rule_set_id
FROM PSB_REVISION_RULE_SETS_V rrs
WHERE rrs.enable_flag = 'Y'
and rrs.budget_group_id in (select budget_group_id
FROM PSB_BUDGET_GROUPS
WHERE budget_group_type = 'R'
START WITH budget_group_id = l_budget_group_id
CONNECT BY PRIOR parent_budget_group_id = budget_group_id));
SELECT budget_group_id, transaction_type
INTO l_budget_group_id, l_transaction_type
FROM psb_budget_revisions_v
WHERE budget_revision_id = p_budget_revision_id ;
SELECT chart_of_accounts_id,
/* Bug No 2135165 Start */
set_of_books_id
/* Bug No 2135165 End */
INTO l_chart_of_accounts_id,
/* Bug No 2135165 Start */
g_brr_sob_id
/* Bug No 2135165 End */
FROM gl_sets_of_books
WHERE set_of_books_id = (SELECT b.set_of_books_id FROM PSB_BUDGET_GROUPS a,
PSB_BUDGET_GROUPS b
WHERE a.budget_group_id = l_budget_group_id
and nvl(a.root_budget_group_id, a.budget_group_id) = b.budget_group_id);
DELETE FROM PSB_ERROR_MESSAGES
WHERE source_process = 'BUDGET_REVISION'
AND process_id = p_budget_revision_id;
SELECT name, constraint_threshold
INTO l_rule_set_name, l_constraint_threshold
FROM psb_revision_rule_sets_v
WHERE rule_set_id = c_Brrule_Rec.rule_set_id AND
/*For Bug No : 2125969 Start*/
--budget_group_id = l_budget_group_id AND
/*For Bug No : 2125969 End*/
enable_flag = 'Y';
SELECT count(*) into l_cnt
FROM PSB_RULE_WITHIN_SEGMENT
WHERE rule_id = c_Brrule_Rec.rule_id;
for c_rule_seg in (Select segment_name, application_column_name
from psb_rule_within_segment
where rule_id = c_Brrule_Rec.rule_id)
Loop
Apply_Detail_Revision_Rules
(
p_return_status => l_return_status,
p_rule_validation_status => l_rule_validation_status,
p_budget_revision_id => p_budget_revision_id,
p_rule_id => c_Brrule_Rec.rule_id,
p_rule_type => c_Brrule_Rec.rule_type,
p_apply_account_set_flag => c_Brrule_Rec.apply_account_set_flag,
p_balance_account_set_flag => c_Brrule_Rec.balance_account_set_flag,
p_segment_name => c_rule_seg.segment_name,
p_application_column_name => c_rule_seg.application_column_name,
p_chart_of_accounts_id => l_chart_of_accounts_id
);
Select count(*) into l_ctr
from psb_rule_transaction_type
where rule_id = c_Brrule_Rec.rule_id
and transaction_type = l_transaction_type
-- Following 1 line added for Bug # 2123930
and enable_flag = 'Y';
FND_MSG_PUB.Delete_Msg;
SELECT concatenated_segments INTO l_con_segments
FROM GL_CODE_COMBINATIONS_KFV
WHERE code_combination_id = g_ccid_rec(l_index).ccid;
FND_MSG_PUB.Delete_Msg;
insert into PSB_ERROR_MESSAGES
(Concurrent_Request_ID,
Process_ID,
Source_Process,
Description,
Creation_Date,
Created_By)
values (FND_GLOBAL.CONC_REQUEST_ID,
p_budget_revision_id,
'BUDGET_REVISION',
l_description,
sysdate,
FND_GLOBAL.USER_ID);
SELECT ba.code_combination_id, sr.apply_balance_flag
FROM PSB_BUDGET_ACCOUNTS ba, PSB_SET_RELATIONS_V sr
WHERE ba.account_position_set_id = sr.account_position_set_id
AND sr.account_or_position_type = 'A'
AND sr.rule_id = p_rule_id
/* Bug No 2135165 Start */
AND ba.code_combination_id in (SELECT bra.code_combination_id
FROM PSB_BUDGET_REVISION_LINES brl, PSB_BUDGET_REVISION_ACCOUNTS bra
WHERE brl.budget_revision_id = p_budget_revision_id
AND bra.budget_revision_acct_line_id = brl.budget_revision_acct_line_id
AND EXISTS (SELECT 1 FROM PSB_ENTITY_ASSIGNMENT ea, gl_sets_of_books sob, gl_periods_v gp
WHERE ea.entity_set_id = g_brr_rule_set_id
AND ea.entity_id = p_rule_id
AND sob.set_of_books_id = g_brr_sob_id
AND gp.period_set_name = sob.period_set_name
AND gp.adjustment_period_flag = 'N'
AND gp.period_name = bra.gl_period_name
AND (((ea.effective_start_date <= gp.end_date)
AND (ea.effective_end_date IS NULL))
OR ((ea.effective_start_date BETWEEN gp.start_date AND gp.end_date)
OR (ea.effective_end_date BETWEEN gp.start_date AND gp.end_date)
OR ((ea.effective_start_date < gp.start_date)
AND (ea.effective_end_date > gp.end_date))))
)
);
l_seg_sql := 'SELECT DISTINCT glcc.'||p_application_column_name||
' FROM gl_code_combinations glcc,'||
' (SELECT DISTINCT bra.code_combination_id '||
' FROM PSB_BUDGET_REVISION_LINES brl,'||
' PSB_BUDGET_REVISION_ACCOUNTS bra '||
' WHERE brl.budget_revision_id = '||to_char(p_budget_revision_id)||
' AND bra.budget_revision_acct_line_id = brl.budget_revision_acct_line_id'||
' ) rcc'||
' WHERE glcc.code_combination_id = rcc.code_combination_id';
l_acct_sql_temp := 'SELECT ba.code_combination_id, sr.apply_balance_flag '||
' FROM PSB_BUDGET_ACCOUNTS ba, PSB_SET_RELATIONS_V sr'||
' WHERE ba.account_position_set_id = sr.account_position_set_id'||
' AND sr.account_or_position_type = '||''''||'A'||''''||
' AND sr.rule_id = :b_rule_id'||
' AND ba.code_combination_id in (SELECT bra.code_combination_id '||
' FROM PSB_BUDGET_REVISION_LINES brl,'||
' PSB_BUDGET_REVISION_ACCOUNTS bra'||
' WHERE brl.budget_revision_id = :b_budget_revision_id'||
' AND bra.budget_revision_acct_line_id = brl.budget_revision_acct_line_id'||
' AND EXISTS (SELECT 1 '||
' FROM PSB_ENTITY_ASSIGNMENT ea, gl_sets_of_books sob, gl_periods_v gp'||
' WHERE ea.entity_set_id = :b_brr_rule_set_id'||
' AND ea.entity_id = :b_rule_id'||
' AND sob.set_of_books_id = :b_brr_sob_id'||
' AND gp.period_set_name = sob.period_set_name'||
' AND gp.adjustment_period_flag = '||''''||'N' ||''''||
' AND gp.period_name = bra.gl_period_name'||
' AND (((ea.effective_start_date <= gp.end_date)'||
' AND (ea.effective_end_date IS NULL))'||
' OR ((ea.effective_start_date BETWEEN gp.start_date AND gp.end_date)'||
' OR (ea.effective_end_date BETWEEN gp.start_date AND gp.end_date)'||
' OR ((ea.effective_start_date < gp.start_date)'||
' AND (ea.effective_end_date > gp.end_date))))'||
' ))'||
' AND EXISTS (SELECT 1 '||
' FROM PSB_BUDGET_REVISION_LINES brl,'||
' PSB_BUDGET_REVISION_ACCOUNTS bra'||
' WHERE brl.budget_revision_id = :b_budget_revision_id'||
' AND bra.budget_revision_acct_line_id = brl.budget_revision_acct_line_id'||
' AND bra.code_combination_id = ba.code_combination_id)'||
' AND EXISTS (SELECT 1'||
' FROM gl_code_combinations glcc'||
' WHERE glcc.code_combination_id = ba.code_combination_id';
/*select count(bra.code_combination_id) into l_cnt
from psb_budget_revision_accounts bra, psb_budget_revision_lines brl
where bra.budget_revision_acct_line_id = brl.budget_revision_acct_line_id
and brl.budget_revision_id = p_budget_revision_id
and bra.code_combination_id = c_CCID_Rec.code_combination_id;*/
SELECT a.revision_type, a.revision_value_type, a.revision_amount,
a.account_type, a.budget_balance
FROM PSB_BUDGET_REVISION_LINES b,
PSB_BUDGET_REVISION_ACCOUNTS a
WHERE b.budget_revision_id = p_budget_revision_id
AND a.budget_revision_acct_line_id = b.budget_revision_acct_line_id
AND a.code_combination_id = p_ccid
/*For Bug No : 2161125 Start*/
AND a.position_id IS NULL;
SELECT DECODE(permanent_revision, 'Y', 'PERMANENT', 'TEMPORARY') permanent_revision
FROM PSB_BUDGET_REVISIONS
WHERE budget_revision_id = p_budget_revision_id;
select note_id from PSB_BUDGET_REVISION_ACCOUNTS where budget_revision_acct_line_id = p_account_line_id;
SELECT pbrp.note_id,
pbra.code_combination_id
FROM PSB_BUDGET_REVISION_POSITIONS pbrp, PSB_BUDGET_REVISION_ACCOUNTS pbra
WHERE pbrp.budget_revision_pos_line_id = p_position_line_id
AND pbrp.position_id = pbra.position_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_BUDGET_REVISION_ACCOUNTS
set note_id = l_note_id
where budget_revision_acct_line_id = p_account_line_id;
update PSB_BUDGET_REVISION_POSITIONS
set note_id = l_note_id
where budget_revision_pos_line_id = p_position_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;
PROCEDURE set_position_update_flag
(
x_return_status OUT NOCOPY VARCHAR2, -- Bug#4460150
p_position_id IN NUMBER
)
IS
l_api_name VARCHAR2(30) := 'set_update_position_flag';
g_last_update_flag_tbl(p_position_id) := 1;
END set_position_update_flag;