[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select bv.budget_version_id
into x_budget_version_id
from pa_budget_versions bv , pa_budget_types bt
where bv.project_id = x_project_id
and bv.budget_type_code = x_budget_type_code
and bv.budget_status_code in ('W', 'S')
and bv.budget_type_code = bt.budget_type_code
and nvl(bt.plan_type,'BUDGET') = 'BUDGET';
select bv.budget_version_id
into x_budget_version_id
from pa_budget_versions bv, pa_budget_types bt
where bv.project_id = x_project_id
and bv.budget_type_code = x_budget_type_code
and bv.current_flag = 'Y'
and bv.budget_type_code = bt.budget_type_code
and nvl(bt.plan_type,'BUDGET') = 'BUDGET';
select bv.budget_version_id
into x_budget_version_id
from pa_budget_versions bv, pa_budget_types bt
where bv.project_id = x_project_id
and bv.budget_type_code = x_budget_type_code
and bv.current_original_flag = 'Y'
and bv.budget_type_code = bt.budget_type_code
and nvl(bt.plan_type,'BUDGET') = 'BUDGET';
select resource_list_id
into x_resource_list_id
from pa_budget_versions
where budget_version_id = x_baselined_version_id;
select budget_amount_code
into x_budget_amount_code
from pa_budget_types
where budget_type_code = x_budget_type_code;
select t.allow_cost_budget_entry_flag,
t.cost_budget_resource_list_id
into x_allow_budget_entry_flag,
x_resource_list_id
from pa_project_types t,
pa_projects p
where p.project_id = x_project_id
and p.project_type = t.project_type;
select t.allow_rev_budget_entry_flag,
t.rev_budget_resource_list_id
into x_allow_budget_entry_flag,
x_resource_list_id
from pa_project_types t,
pa_projects p
where p.project_id = x_project_id
and p.project_type = t.project_type;
select budget_amount_code
into x_budget_amount_code
from pa_budget_types
where budget_type_code = x_budget_type_code;
select t.allow_cost_budget_entry_flag,
t.cost_budget_entry_method_code
into x_allow_budget_entry_flag,
x_budget_entry_method_code
from pa_project_types t,
pa_projects p
where p.project_id = x_project_id
and p.project_type = t.project_type;
select t.allow_rev_budget_entry_flag,
t.rev_budget_entry_method_code
into x_allow_budget_entry_flag,
x_budget_entry_method_code
from pa_project_types t,
pa_projects p
where p.project_id = x_project_id
and p.project_type = t.project_type;
select budget_type_code
into x_budget_type_code
from pa_budget_types
where budget_type = x_budget_type;
select budget_entry_method_code
into x_budget_entry_method_code
from pa_budget_entry_methods
where budget_entry_method = x_budget_entry_method;
select lookup_code
into x_change_reason_code
from pa_lookups
where lookup_type = 'BUDGET CHANGE REASON'
and meaning = x_meaning;
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv, pa_budget_types bt
where bv.project_id = x_project_id
and bv.budget_type_code is NOT NULL -- This must be specified for r11.5.7 Budgets Model
and bv.budget_type_code = bt.budget_type_code
and nvl(bt.plan_type,'BUDGET') = 'BUDGET');
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
where bv.project_id = x_project_id
and bv.fin_plan_type_id is NOT NULL -- Specified for FP Model
and nvl(bv.wp_version_flag,'N') = 'N' -- (Added for Patchset M,Tracking Bug No - 3354518)
);
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv, pa_budget_types bt
where bv.project_id = x_project_id
and bv.budget_type_code = x_budget_type_code
and bv.budget_type_code = bt.budget_type_code
and nvl(bt.plan_type,'BUDGET') = 'BUDGET');
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
where bv.project_id = x_project_id
and bv.fin_plan_type_id = x_fin_plan_type_id
and bv.version_type = nvl(x_version_type, bv.version_type)
);
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv, pa_budget_types bt
where bv.project_id = x_project_id
and bv.budget_type_code = x_budget_type_code
and bv.budget_type_code = bt.budget_type_code
and nvl(bt.plan_type,'BUDGET') = 'BUDGET');
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
where bv.project_id = x_project_id
and bv.fin_plan_type_id = x_fin_plan_type_id
and bv.version_type = nvl(x_version_type, bv.version_type)
);
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
where bv.project_id = x_project_id
and bv.approved_cost_plan_type_flag = 'Y'
and bv.current_flag = 'Y'
);
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
where bv.project_id = x_project_id
and bv.approved_rev_plan_type_flag = 'Y'
and bv.current_flag = 'Y'
);
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
where bv.project_id = x_project_id
and bv.fin_plan_type_id = x_fin_plan_type_id
and bv.version_type = nvl(x_version_type, bv.version_type)
and bv.approved_cost_plan_type_flag = 'Y'
and bV.current_flag = 'Y'
);
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
where bv.project_id = x_project_id
and bv.fin_plan_type_id = x_fin_plan_type_id
and bv.version_type = nvl(x_version_type, bv.version_type)
and bv.approved_rev_plan_type_flag = 'Y'
and bV.current_flag = 'Y'
);
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv, pa_budget_types bt
where bv.project_id = x_project_id
and bv.budget_type_code = x_budget_type_code
and bv.budget_type_code = bt.budget_type_code
and nvl(bt.plan_type,'BUDGET') = 'BUDGET'
and bV.current_flag = 'Y'
);
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
where bv.project_id = x_project_id
and bv.fin_plan_type_id = x_fin_plan_type_id
and bv.version_type = nvl(x_version_type, bv.version_type)
and bV.current_flag = 'Y'
);
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
, pa_budget_types bt
, pa_resource_assignments a
where a.task_id = x_task_id
and bv.budget_version_id = a.budget_version_id
and bv.budget_type_code is NOT NULL -- This must be specified for r11.5.7 Budgets Model
and bv.budget_type_code = bt.budget_type_code
and nvl(bt.plan_type,'BUDGET') = 'BUDGET'
);
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
, pa_resource_assignments a
where a.task_id = x_task_id
and bv.budget_version_id = a.budget_version_id
and bv.fin_plan_type_id is NOT NULL -- Specified for FP Model
and nvl(bv.wp_version_flag,'N') = 'N' -- (Added for Patchset M,Tracking Bug No - 3354518)
);
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
, pa_budget_types bt
, pa_resource_assignments a
where a.task_id = x_task_id
and bv.budget_version_id = a.budget_version_id
and bv.budget_type_code = x_budget_type_code
and bv.budget_type_code = bt.budget_type_code
and nvl(bt.plan_type,'BUDGET') = 'BUDGET'
);
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
, pa_resource_assignments a
where a.task_id = x_task_id
and bv.budget_version_id = a.budget_version_id
and bv.fin_plan_type_id = x_fin_plan_type_id
and bv.version_type = nvl(x_version_type, bv.version_type)
);
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
, pa_budget_types bt
, pa_resource_assignments a
where a.task_id = x_task_id
and bv.budget_version_id = a.budget_version_id
and bv.budget_type_code = x_budget_type_code
and bv.budget_type_code = bt.budget_type_code
and nvl(bt.plan_type,'BUDGET') = 'BUDGET'
);
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
, pa_resource_assignments a
where a.task_id = x_task_id
and bv.budget_version_id = a.budget_version_id
and bv.fin_plan_type_id = x_fin_plan_type_id
and bv.version_type = nvl(x_version_type, bv.version_type)
);
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
, pa_tasks t
, pa_resource_assignments a
where a.budget_version_id = bv.budget_version_id
and a.task_id = t.task_id
and t.top_task_id = x_task_id
and bv.approved_cost_plan_type_flag = 'Y'
and bv.current_flag = 'Y'
);
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
, pa_tasks t
, pa_resource_assignments a
where a.budget_version_id = bv.budget_version_id
and a.task_id = t.task_id
and t.top_task_id = x_task_id
and bv.approved_rev_plan_type_flag = 'Y'
and bv.current_flag = 'Y'
);
* the following select to avoid full index scan on PA_PROJ_ELEM_VER_SCHEDULE_N1
*/
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
,pa_tasks t --Bug 4176059: Performance Fix: FP.M -B12: re-used pa_tasks
-- , pa_struct_task_wbs_v t -- Adding for FP.M, Tracking Bug No - 3354518.
,pa_resource_assignments a
where a.budget_version_id = bv.budget_version_id
and a.task_id = t.task_id
and t.top_task_id = x_task_id
and bv.fin_plan_type_id = x_fin_plan_type_id
and bv.version_type = nvl(x_version_type, bv.version_type)
and bv.approved_cost_plan_type_flag = 'Y'
and bV.current_flag = 'Y'
);
* the following select to avoid full index scan on PA_PROJ_ELEM_VER_SCHEDULE_N1
*/
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
,pa_tasks t -- Bug 4176059: Performance Fix: FP.M -B12: re-used pa_tasks
--, pa_struct_task_wbs_v t -- Adding for FP.M, Tracking Bug No - 3354518.
,pa_resource_assignments a
where a.budget_version_id = bv.budget_version_id
and a.task_id = t.task_id
and t.top_task_id = x_task_id
and bv.fin_plan_type_id = x_fin_plan_type_id
and bv.version_type = nvl(x_version_type, bv.version_type)
and bv.approved_rev_plan_type_flag = 'Y'
and bV.current_flag = 'Y'
);
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
, pa_budget_types bt
, pa_tasks t
, pa_resource_assignments a
where a.budget_version_id = bv.budget_version_id
and a.task_id = t.task_id
and t.top_task_id = x_task_id
and bv.budget_type_code = x_budget_type_code
and bv.budget_type_code = bt.budget_type_code
and nvl(bt.plan_type,'BUDGET') = 'BUDGET'
and bv.current_flag = 'Y'
);
* the following select to avoid full index scan on PA_PROJ_ELEM_VER_SCHEDULE_N1
*/
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
,pa_tasks t -- Bug 4176059: Performance Fix: FP.M -B12: re-used pa_tasks
--, pa_struct_task_wbs_v t -- Adding for FP.M, Tracking Bug No - 3354518.
,pa_resource_assignments a
where a.budget_version_id = bv.budget_version_id
and a.task_id = t.task_id
and t.top_task_id = x_task_id
and bv.fin_plan_type_id = x_fin_plan_type_id
and bv.version_type = nvl(x_version_type, bv.version_type)
and bv.current_flag = 'Y'
);
Select migration_code
into l_migration_code
from pa_resource_list_members
where resource_list_member_id = x_resource_list_member_id;
select 1
into dummy
from sys.dual
where exists
(select 1
from pa_resource_list_members m,
pa_resource_assignments a
where m.parent_member_id = x_resource_list_member_id
and m.resource_list_member_id = a.resource_list_member_id
and a.budget_version_id = x_budget_version_id
and a.task_id = x_task_id);
select 1
into dummy
from sys.dual
where exists
(select 1
from pa_resource_assignments a
where a.budget_version_id = x_budget_version_id
and a.task_id = x_task_id
and a.resource_list_member_id = x_parent_member_id);
SELECT nvl(SUM(nvl(b.raw_cost,0)), 0),
nvl(SUM(nvl(b.burdened_cost,0)), 0),
nvl(SUM(nvl(b.labor_quantity,0)), 0),
nvl(SUM(nvl(b.revenue,0)), 0)
INTO raw_cost,
burdened_cost,
labor_qty,
revenue_amount
FROM pa_budget_versions b
WHERE b.project_id = x_project_id
AND b.budget_type_code = x_budget_type
AND b.budget_status_code = decode(budget_status, 'B', 'B',
b.budget_status_code)
AND NOT (budget_status = 'O' and b.budget_status_code = 'B')
AND b.current_flag||'' = nvl(current_flag, b.current_flag)
AND b.current_original_flag =
nvl(original_flag, b.current_original_flag);
SELECT nvl(SUM(nvl(l.raw_cost,0)), 0),
nvl(SUM(nvl(l.burdened_cost,0)), 0),
nvl(SUM(decode(a.track_as_labor_flag,'Y',nvl(l.quantity,0),0)), 0),
nvl(SUM(nvl(l.revenue,0)), 0)
INTO raw_cost,
burdened_cost,
labor_qty,
revenue_amount
FROM pa_budget_lines l,
pa_resource_assignments a,
pa_tasks t,
pa_budget_versions v
WHERE v.project_id = x_project_id
AND v.budget_type_code = x_budget_type
AND v.budget_status_code = decode(budget_status, 'B', 'B',
v.budget_status_code)
and NOT (budget_status = 'O' and v.budget_status_code = 'B')
and v.current_flag||'' = nvl(current_flag, v.current_flag)
and a.budget_version_id = v.budget_version_id
and a.project_id = v.project_id
and t.project_id = x_project_id
and t.task_id = a.task_id
and x_task_id in (t.top_task_id, t.task_id)
and v.current_original_flag =
nvl(original_flag, v.current_original_flag)
AND l.resource_assignment_id = a.resource_assignment_id;
procedure delete_draft (x_budget_version_id in number,
x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
is
old_stack varchar2(630);
x_err_stack := x_err_stack || '->delete_draft';
select v.project_id,
v.budget_type_code,
la.resource_list_assignment_id
into x_project_id,
x_budget_type_code,
x_resource_list_assgmt_id
from pa_resource_list_assignments la,
pa_budget_versions v
where v.budget_version_id = x_budget_version_id
and v.project_id = la.project_id
and v.resource_list_id = la.resource_list_id;
/* Never delete resource list assignment if it is baselined
-- if there is a baselined version, then do not delete resource assignment
pa_budget_utils.get_baselined_version_id(x_project_id,
x_budget_type_code,
x_baselined_version_id,
x_err_code,
x_err_stage,
x_err_stack);
x_err_stage := 'delete resource assignment <'
|| to_char(x_resource_list_assgmt_id) || '><'
|| x_budget_type_code || '>';
pa_res_list_assignments.delete_rl_uses(x_resource_list_assgmt_id,
x_budget_type_code,
x_err_code,
x_err_stage,
x_err_stack);
x_err_stage := 'delete budget lines <' || to_char(x_budget_version_id)
|| '>';
for bl_rec in (select rowid
from pa_budget_lines
where resource_assignment_id in
(select resource_assignment_id
from pa_resource_assignments
where budget_version_id = x_budget_version_id))
loop
pa_budget_lines_v_pkg.delete_row(x_rowid => bl_rec.rowid);
x_err_stage := 'delete budget version <' || to_char(x_budget_version_id)
|| '>';
delete pa_budget_versions
where budget_version_id = x_budget_version_id;
fnd_attached_documents2_pkg.delete_attachments('PA_BUDGET_VERSIONS',
x_budget_version_id,
null, null, null, null,
'Y') ;
end delete_draft;
x_last_update_login number;
x_last_update_login := FND_GLOBAL.LOGIN_ID;
select resource_list_id
into x_baselined_resource_list_id
from pa_budget_versions
where budget_version_id = x_baselined_version_id;
pa_debug.g_err_stage:= 'Calling delete_draft';
pa_budget_utils.delete_draft(old_draft_version_id,
x_err_code,
x_err_stage,
x_err_stack);
pa_debug.g_err_stage:= 'After call to delete_draft';
SELECT pa_budget_versions_s.nextval
INTO x_budget_version_id
FROM dual;
insert into pa_budget_versions(
budget_version_id,
project_id,
budget_type_code,
version_number,
budget_status_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
current_flag,
original_flag,
current_original_flag,
resource_accumulated_flag,
resource_list_id,
version_name,
budget_entry_method_code,
baselined_by_person_id,
baselined_date,
change_reason_code,
labor_quantity,
labor_unit_of_measure,
raw_cost,
burdened_cost,
revenue,
description,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
pm_product_code,
pm_budget_reference,
wf_status_code,
approved_cost_plan_type_flag,
approved_rev_plan_type_flag
)
select
x_budget_version_id,
x_project_id,
x_budget_type_code,
1,
'W',
SYSDATE,
x_created_by,
SYSDATE,
x_created_by,
x_last_update_login,
'N',
'N',
'N',
'N',
x_resource_list_id,
x_version_name,
x_budget_entry_method_code,
NULL,
NULL,
x_change_reason_code,
NULL,
NULL,
NULL,
NULL,
NULL,
x_description,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
x_pm_product_code,
x_pm_budget_reference,
NULL,
decode(x_budget_type_code,'AC','Y','N'),
decode(x_budget_type_code,'AR','Y','N')
from sys.dual;
x_last_update_login number;
select budget_type_code
from pa_budget_versions
where budget_version_id = x_budget_version_id;
x_last_update_login := FND_GLOBAL.LOGIN_ID;
select resource_assignment_id
into x_resource_assignment_id
from pa_resource_assignments
where budget_version_id = x_budget_version_id
and project_id = x_project_id
and NVL(task_id, 0) = NVL(x_task_id, 0)
and resource_list_member_id = x_resource_list_member_id
AND NVL(CBS_ELEMENT_ID,-1)=NVL(X_CBS_ELEMENT_ID,-1); --Bug 16604257
select pa_resource_assignments_s.nextval
into x_resource_assignment_id
from sys.dual;
insert into pa_resource_assignments
(resource_assignment_id,
budget_version_id,
project_id,
task_id,
resource_list_member_id,
CBS_ELEMENT_ID,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
unit_of_measure,
track_as_labor_flag,
project_assignment_id, --added the cloumn for bug 2446041
RESOURCE_ASSIGNMENT_TYPE)
values ( x_resource_assignment_id,
x_budget_version_id,
x_project_id,
x_task_id,
x_resource_list_member_id,
X_CBS_ELEMENT_ID,--Bug 16604257
SYSDATE,
x_created_by,
SYSDATE,
x_created_by,
x_last_update_login,
x_unit_of_measure,
x_track_as_labor_flag,
-1, --added the cloumn for bug 2446041
'USER_ENTERED');
SELECT pa_budget_lines_s.nextval
INTO l_budget_line_id
FROM DUAL;
insert into pa_budget_lines
(budget_line_id, /* FPB2 */
budget_version_id, /* FPB2 */
resource_assignment_id,
start_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
end_date,
period_name,
quantity,
raw_cost,
burdened_cost,
revenue,
change_reason_code,
description,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
pm_product_code,
pm_budget_line_reference,
quantity_source,
raw_cost_source,
burdened_cost_source,
revenue_source,
projfunc_currency_code,
project_currency_code,
txn_currency_code
)
values (
l_budget_line_id, /* FPB2 */
x_budget_version_id, /* FPB2 */
x_resource_assignment_id,
x_start_date,
SYSDATE,
x_created_by,
SYSDATE,
x_created_by,
x_last_update_login,
x_end_date,
x_period_name,
x_quantity,
pa_currency.round_currency_amt(x_raw_cost),
pa_currency.round_currency_amt(x_burdened_cost),
pa_currency.round_currency_amt(x_revenue),
x_change_reason_code,
x_description,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
x_pm_product_code,
x_pm_budget_line_reference,
x_quantity_source,
x_raw_cost_source,
x_burdened_cost_source,
x_revenue_source,
l_Projfunc_currency_code,
l_Project_currency_code,
l_txn_currency_code
);
p_action => PA_MRC_FINPLAN.G_ACTION_INSERT,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
x_last_update_login number;
x_last_update_login := FND_GLOBAL.LOGIN_ID;
update pa_budget_versions v
set (labor_quantity,
labor_unit_of_measure,
raw_cost,
burdened_cost,
revenue,
last_update_date,
last_updated_by,
last_update_login
)
=
(select sum(nvl(to_number(decode(a.track_as_labor_flag,
'Y', l.quantity, NULL)),0)),
-- decode(a.track_as_labor_flag, 'Y', a.unit_of_measure, NULL),
'HOURS', -- V4 uses HOURS as the only labor unit
pa_currency.round_currency_amt(sum(nvl(l.raw_cost, 0))),
pa_currency.round_currency_amt(sum(nvl(l.burdened_cost, 0))),
pa_currency.round_currency_amt(sum(nvl(l.revenue, 0))),
SYSDATE,
x_created_by,
x_last_update_login
from pa_resource_assignments a,
pa_budget_lines l
where a.budget_version_id = x_budget_version_id /*Bug 4198840: Perf:Included this join*/
and a.budget_version_id = v.budget_version_id
and a.resource_assignment_id = l.resource_assignment_id
)
where budget_version_id = x_budget_version_id;
pa_debug.g_err_stage:= 'After update';
select 1
into l_dummy
from sys.dual
where exists
(select 1
from pa_resource_assignments
where budget_version_id = p_draft_version_id);
select 'T'
into l_entry_level_code
from sys.dual
where exists
(select 1
from pa_resource_assignments
where budget_version_id = p_draft_version_id
-- and task_id is not null);
select nvl(revenue,0) -- Bug 3142016
into l_budget_total
from pa_budget_versions
where budget_version_id = p_draft_version_id;
select 'Y'
into l_dual_bdgt_cntrl_flag
from dual
where exists
(select 1
from pa_budgetary_control_options a
where project_id = p_project_id
and external_budget_code = 'CC'
and bdgt_cntrl_flag = 'Y')
and exists
(select 1
from pa_budgetary_control_options b
where project_id = p_project_id
and external_budget_code = 'GL'
and bdgt_cntrl_flag = 'Y');
SELECT budget_version_id
INTO l_CC_Budget_Version_id
FROM pa_budget_versions bv
WHERE project_id = p_project_id
AND BUDGET_STATUS_CODE = 'S'
AND budget_type_code =
(SELECT budget_type_code
FROM pa_budgetary_control_options pbco
WHERE pbco.budget_type_code = bv.budget_type_code
AND pbco.project_id = bv.project_id
AND pbco.bdgt_cntrl_flag = 'Y'
AND pbco.external_budget_code = 'CC');
SELECT budget_type_code
INTO l_budget_type_code
FROM pa_budget_versions
WHERE budget_version_id = p_draft_version_id
AND budget_status_code = 'S';
SELECT budget_version_id, version_number
INTo l_baseline_version_id, l_baseline_version_number
FROM pa_budget_versions
WHERE budget_type_code = l_budget_type_code
AND project_id = p_project_id
AND budget_status_code = 'B'
AND current_flag = 'Y';
SELECT budget_version_id
INTo l_pre_baseline_version_id
FROM pa_budget_versions pb
WHERE pb.budget_type_code = l_budget_type_code
AND pb.project_id = p_project_id
AND pb.budget_status_code='B'
AND pb.version_number = (l_baseline_version_number - 1);
PA_CLIENT_EXT_FV_BUDGET_INT.INSERT_BUDGET_LINES
(p_project_id => p_project_id
,p_pre_baselined_version_id => l_pre_baseline_version_id
,p_baselined_budget_version_id => l_baseline_version_id
,x_rejection_code => l_rejection_code
,x_interface_status => l_interface_status);
Select budget_type_code
into l_budget_type_code
from pa_budget_versions
where budget_version_id = p_draft_version_id;
SELECT budget_version_id, version_number
INTo l_baseline_version_id, l_baseline_version_number
FROM pa_budget_versions
WHERE budget_type_code = l_budget_type_code
AND project_id = p_project_id
AND budget_status_code='B'
AND current_flag = 'Y';
SELECT budget_version_id
INTo l_pre_baseline_version_id
FROM pa_budget_versions pb
WHERE pb.budget_type_code = l_budget_type_code
AND pb.project_id = p_project_id
AND pb.budget_status_code = 'B'
AND pb.version_number = (l_baseline_version_number - 1);
PA_CLIENT_EXT_FV_BUDGET_INT.INSERT_BUDGET_LINES
(p_project_id => p_project_id
,p_pre_baselined_version_id => l_pre_baseline_version_id
,p_baselined_budget_version_id => l_baseline_version_id
,x_rejection_code => l_rejection_code
,x_interface_status => l_interface_status) ;
Select meaning
into l_rejection_reason
from pa_lookups
where lookup_code = l_rejection_code
and lookup_type = 'PA_BUD_INTERFACE_REJ_CODE';
select 'P'
from dual
where x_task_id is null
union
select 'T'
from pa_tasks
where x_task_id is not null
and task_id = x_task_id
and parent_task_id is null
union
select 'M'
from pa_tasks
where x_task_id is not null
and task_id = x_task_id
and parent_task_id is not null
and exists (select 'X'
from pa_tasks
where parent_task_id = x_task_id)
union
select 'L'
from dual
where x_task_id is not null
and not exists (select 'X'
from pa_tasks
where parent_task_id = x_task_id);
select labor_quantity,
raw_cost,
burdened_cost,
revenue
from pa_budget_versions
where v_rollup_flag = 'P' -- Project Level
and budget_version_id = x_budget_version_id;
select SUM(DECODE(a.TRACK_AS_LABOR_FLAG,'Y',NVL(l.QUANTITY,0),0)),
SUM(NVL(l.RAW_COST,0)),
SUM(NVL(l.BURDENED_COST,0)),
SUM(NVL(l.REVENUE,0))
from pa_tasks t,
pa_budget_lines l ,
pa_resource_assignments a
where v_rollup_flag = 'T' -- Top Task Level
and a.budget_version_id = x_budget_version_id
and a.task_id = t.task_id
and t.top_task_id = x_task_id
and a.resource_assignment_id = l.resource_assignment_id
and NVL(a.RESOURCE_ASSIGNMENT_TYPE,'USER_ENTERED') = 'USER_ENTERED';
select SUM(DECODE(a.TRACK_AS_LABOR_FLAG,'Y',NVL(l.QUANTITY,0),0)),
SUM(NVL(l.RAW_COST,0)),
SUM(NVL(l.BURDENED_COST,0)),
SUM(NVL(l.REVENUE,0))
from pa_budget_lines l,
pa_resource_assignments a
where v_rollup_flag = 'M' -- Middle Task Level
and a.budget_version_id = x_budget_version_id
and a.task_id in (select task_id
from pa_tasks
start with task_id = x_task_id
connect by prior task_id = parent_task_id)
and a.resource_assignment_id = l.resource_assignment_id
and NVL(a.RESOURCE_ASSIGNMENT_TYPE,'USER_ENTERED') = 'USER_ENTERED';
select SUM(DECODE(a.TRACK_AS_LABOR_FLAG,'Y',NVL(l.QUANTITY,0),0)),
SUM(NVL(l.RAW_COST,0)),
SUM(NVL(l.BURDENED_COST,0)),
SUM(NVL(l.REVENUE,0))
from pa_budget_lines l,
pa_resource_assignments a
where v_rollup_flag = 'L' -- Lowest Task Level
and a.budget_version_id = x_budget_version_id
and a.task_id = x_task_id
and a.resource_assignment_id = l.resource_assignment_id
and NVL(a.RESOURCE_ASSIGNMENT_TYPE, 'USER_ENTERED') = 'USER_ENTERED';
select currency_uom_flag
from pa_currency_uom_v
where uom_code = x_uom_code;
select budget_amount_code
from pa_budget_types
where budget_type_code = x_budget_type_code;
SELECT b.approved_cost_plan_type_flag, b.approved_rev_plan_type_flag
INTO l_cost_flag, l_rev_flag
FROM pa_budget_versions b
WHERE b.budget_version_id = p_budget_version_id;
SELECT projfunc_currency_code
, project_currency_code
, projfunc_currency_code
INTO pa_budget_utils.G_projfunc_currency_code
, pa_budget_utils.G_project_currency_code
, pa_budget_utils.G_txn_currency_code
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT 'Y'
INTO l_dummy
FROM dual
WHERE EXISTS (select '1'
from pa_budget_versions v
where v.project_id = p_project_id
and v.budget_type_code IN ('AC','AR')
);
SELECT o.fin_plan_type_id, v.version_type
INTO l_ac_plan_type_id, l_ac_version_type
FROM pa_proj_fp_options o
, pa_budget_versions v
WHERE o.fin_plan_version_id = v.budget_version_id
AND v.approved_cost_plan_type_flag = 'Y'
AND v.current_flag = 'Y'
AND v.project_id = p_project_id;
SELECT o.fin_plan_type_id, v.version_type
INTO l_ar_plan_type_id, l_ar_version_type
FROM pa_proj_fp_options o
, pa_budget_versions v
WHERE o.fin_plan_version_id = v.budget_version_id
AND v.approved_rev_plan_type_flag = 'Y'
AND v.current_flag = 'Y'
AND v.project_id = p_project_id;
SELECT 1
INTO dummy
FROM dual
WHERE EXISTS( SELECT 1
FROM pa_budget_versions pbv,
pa_resource_lists prl
WHERE (pbv.budget_type_code = 'AR' OR -- old model
pbv.budget_type_code IS NULL AND
approved_rev_plan_type_flag = 'Y') -- new model
AND pbv.ci_id is null -- filter change order versions
AND pbv.resource_list_id = prl.resource_list_id
AND prl.uncategorized_flag <> 'Y'
AND pbv.project_id = x_project_id );
SELECT org_id
INTO l_org_id
FROM pa_projects_all
WHERE project_id = p_project_id;