[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select budget_version_id
into x_budget_version_id
from gms_budget_versions
where project_id = x_project_id
and award_id = x_award_id
and budget_type_code = x_budget_type_code
and budget_status_code in ('W', 'S');
select budget_version_id
into x_budget_version_id
from gms_budget_versions
where project_id = x_project_id
and award_id = x_award_id
and budget_type_code = x_budget_type_code
and current_flag = 'Y';
select budget_version_id
into x_budget_version_id
from gms_budget_versions
where project_id = x_project_id
and award_id = x_award_id
and budget_type_code = x_budget_type_code
and current_original_flag = 'Y';
select resource_list_id
into x_resource_list_id
from gms_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 sys.dual
where exists
(select 1
from gms_budget_versions
where project_id = x_project_id
and award_id = x_award_id
and budget_type_code =
nvl(x_budget_type_code, budget_type_code));
select 1
into dummy
from sys.dual
where exists
(select 1
from gms_budget_versions
where project_id = x_project_id
and award_id = x_award_id
and budget_type_code =
nvl(x_budget_type_code, budget_type_code)
and budget_status_code = 'B');
select 1
into dummy
from sys.dual
where exists
(select 1
from gms_budget_versions v,
gms_resource_assignments a
where a.task_id = x_task_id
and v.award_id = x_award_id
and v.budget_version_id = a.budget_version_id
and v.budget_type_code =
nvl(x_budget_type_code, v.budget_type_code));
select 1
into dummy
from sys.dual
where exists
(select 1
from gms_budget_versions v,
pa_tasks t,
gms_resource_assignments a
where a.budget_version_id = v.budget_version_id
and v.budget_status_code = 'B'
and a.task_id = t.task_id
and t.top_task_id = x_task_id
and v.award_id = x_award_id
and v.budget_type_code =
nvl(x_budget_type_code, v.budget_type_code));
select 1
into dummy
from sys.dual
where exists
(select 1
from pa_resource_list_members m,
gms_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 gms_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 a.resource_name
from gms_budget_lines_v a, gms_budget_lines_v b
where a.budget_version_id = x_budget_version_id
and b.budget_version_id = x_budget_version_id
and a.task_id||null = b.task_id||null
-- and a.resource_list_member_id = b.resource_list_member_id Bug 2601648
and a.row_id <> b.row_id
and ((a.start_date
between b.start_date + 1 -- Bug 2601648 Added + 1
and nvl(b.end_date,a.start_date +1))
or (a.end_date
between b.start_date
and nvl(b.end_date - 1,b.end_date+1)) -- Bug 2601648 Added - 1
or (b.start_date
between a.start_date + 1 -- Bug 2601648 Added + 1
and nvl(a.end_date,b.start_date+1))
);
select a1.resource_list_member_id
from gms_resource_assignments a1,
gms_budget_lines a2,
gms_resource_assignments b1,
gms_budget_lines b2
where a1.resource_assignment_id = a2.resource_assignment_id
and b1.resource_assignment_id = b2.resource_assignment_id
and a1.budget_version_id = b1.budget_version_id
and a1.budget_version_id = x_budget_version_id
and not (a1.rowid = b1.rowid and a2.rowid = b2.rowid)
and b2.end_date >= a2.start_date
and b2.start_date <= a2.end_date
and not (a2.start_date = b2.start_date and a2.end_date = b2.end_date);
select alias
into x_resource_name
from pa_resource_list_members
where resource_list_member_id = l_resource_list_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 gms_budget_versions b
WHERE b.project_id = x_project_id
AND b.award_id = x_award_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 gms_budget_lines l,
gms_resource_assignments a,
pa_tasks t,
gms_budget_versions v
WHERE v.project_id = x_project_id
AND v.award_id = x_award_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;
select 1
into l_dummy
from sys.dual
where exists
(select 1
from gms_resource_assignments
where budget_version_id = p_draft_version_id);
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 gms_budget_versions
where v_rollup_flag = 'P' -- Project Level
and budget_version_id = x_budget_version_id
union
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,
gms_budget_lines l ,
gms_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
union
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 gms_budget_lines l,
gms_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
union
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 gms_budget_lines l,
gms_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;
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 period_start_date
, period_end_date
FROM pa_budget_periods_v
WHERE period_name = p_period_name
AND period_type_code = p_period_type_code;
SELECT period_name
FROM pa_budget_periods_v
WHERE period_type_code = p_period_type_code
AND period_start_date = p_start_date
AND period_end_date = p_end_date;
SELECT start_date
, completion_date
FROM pa_projects
WHERE project_id = p_project_id;
SELECT start_date
, completion_date
FROM pa_tasks
WHERE task_id = p_task_id;
SELECT nvl(preaward_date,start_date_active), -- Added preaward_date for Bug:2266731
end_date_active
FROM gms_awards
WHERE award_id = p_award_id;
SELECT cost_quantity_flag
, raw_cost_flag
, burdened_cost_flag
, rev_quantity_flag
FROM pa_budget_entry_methods
WHERE budget_entry_method_code = p_budget_entry_method_code
AND trunc(sysdate) BETWEEN trunc(start_date_active) and trunc(nvl(end_date_active,sysdate));
SELECT org_id
INTO l_org_id
FROM gms_awards_all
WHERE award_id = p_award_id;
SELECT task_name
FROM pa_tasks
WHERE task_id = P_task_Id;