[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT decode(P_cost_budget_type_code,NULL,default_cost_budget_type_code, P_cost_budget_type_code),
decode(P_rev_budget_type_code,NULL,default_rev_budget_type_code,
P_rev_budget_type_code)
INTO l_cost_budget_type_code,
l_rev_budget_type_code
FROM pa_billing_extensions
WHERE billing_extension_id= p_billing_Extension_Id;
SELECT 'x'
INTO l_check_code
FROM pa_budget_types
WHERE budget_type_code = l_cost_budget_type_code
AND budget_amount_code = 'C';
SELECT 'x'
INTO l_check_code
FROM pa_budget_types
WHERE budget_type_code = l_rev_budget_type_code
AND budget_amount_code = 'R';
SELECT budget_version_id
INTO l_cost_budget_version_id
FROM pa_budget_versions pbv
WHERE project_id = p_project_id
AND budget_type_code = l_cost_budget_type_code
AND budget_status_code = 'B'
AND current_flag = 'Y';
SELECT budget_version_id
INTO l_rev_budget_version_id
FROM pa_budget_versions pbv
WHERE project_id = p_project_id
AND budget_type_code = l_rev_budget_type_code
AND budget_status_code = 'B'
AND current_flag = 'Y';
SELECT 'P'
FROM dual
WHERE p_task_id is null
UNION
SELECT 'T'
FROM pa_tasks
WHERE p_task_id is not null
AND task_id = p_task_id
AND parent_task_id is null
UNION
SELECT 'M'
FROM pa_tasks
WHERE p_task_id is not null
AND task_id = p_task_id
AND parent_task_id is not null
AND exists (select 'X'
from pa_tasks
where parent_task_id = p_task_id)
UNION
SELECt 'L'
FROM dual
WHERE p_task_id is not null
AND not exists (select 'X'
from pa_tasks
where parent_task_id = p_task_id);
different SELECT for get the amount.
------------------------------------------------------------------------ */
/* Project Level Task */
IF (l_rollup_level = 'P') THEN
NULL;
SELECT SUM(NVL(mcbl.raw_cost,0)),
SUM(NVL(mcbl.burdened_cost,0)),
SUM(NVL(mcbl.revenue,0))
INTO l_raw_cost_total,
l_burdened_cost_total,
l_revenue_total
FROM pa_mc_budget_lines mcbl, pa_budget_lines bl,
pa_resource_assignments a
WHERE bl.budget_line_id = mcbl.budget_line_id
AND a.budget_version_id = p_budget_version_id
AND a.project_id = p_project_id
AND a.resource_assignment_id = bl.resource_assignment_id
AND mcbl.set_of_books_id = p_rsob_id
;
SELECT SUM(NVL(mcbl.raw_cost,0)),
SUM(NVL(mcbl.burdened_cosT,0)),
SUM(NVL(mcbl.revenue,0))
INTO l_raw_cost_total,
l_burdened_cost_total,
l_revenue_total
FROM pa_tasks t, pa_mc_budget_lines mcbl , pa_budget_lines bl,
pa_resource_assignments a
WHERE bl.budget_line_id = mcbl.budget_line_id
AND a.budget_version_id = p_budget_version_id
AND a.task_id = t.task_id
AND t.top_task_id = p_task_id
AND a.resource_assignment_id = bl.resource_assignment_id
AND mcbl.set_of_books_id = p_rsob_id
;
SELECT SUM(NVL(mcbl.raw_cost,0)),
SUM(NVL(mcbl.burdened_cost,0)),
SUM(NVL(mcbl.revenue,0))
INTO l_raw_cost_total,
l_burdened_cost_total,
l_revenue_total
FROM pa_mc_budget_lines mcbl, pa_budget_lines bl,
pa_resource_assignments a
WHERE bl.budget_line_id = mcbl.budget_line_id
AND a.budget_version_id = p_budget_version_id
AND a.task_id in (SELECT task_id
FROM pa_tasks
START with task_id = p_task_id
CONNECT by prior task_id = parent_task_id)
AND a.resource_assignment_id = bl.resource_assignment_id
AND mcbl.set_of_books_id = p_rsob_id
;
SELECT SUM(NVL(mcbl.raw_cost,0)),
SUM(NVL(mcbl.burdened_cost,0)),
SUM(NVL(mcbl.revenue,0))
INTO l_raw_cost_total,
l_burdened_cost_total,
l_revenue_total
FROM pa_mc_budget_lines mcbl, pa_budget_lines bl,
pa_resource_assignments a
WHERE bl.budget_line_id = mcbl.budget_line_id
AND a.budget_version_id = p_budget_version_id
AND a.task_id = p_task_id
AND a.resource_assignment_id = bl.resource_assignment_id
AND mcbl.set_of_books_id = p_rsob_id
;
SELECT SUM(NVL(mccdl.burdened_cost, NVL(mccdl.amount,0)))
INTO l_cost_amount
FROM pa_cost_distribution_lines_all cdl,
pa_mc_cost_dist_lines_all mccdl,
pa_tasks t, pa_periods pp
WHERE cdl.project_id = t.project_id
AND t.project_id = p_project_id
AND nvl(cdl.task_id, -1) = nvl(t.task_id, -1)
AND nvl(t.task_id, -1) = nvl(p_task_id, nvl(t.task_id, -1))
AND mccdl.expenditure_item_id = cdl.expenditure_item_id
AND mccdl.line_num = cdl.line_num
AND ( cdl.pa_date BETWEEN pp.start_date AND pp.end_date)
AND (trunc(NVL(p_accrue_through_date, SYSDATE)) >= TRUNC(pp.start_date)) -- BUG#3118592
AND cdl.line_type = 'R'
AND mccdl.set_of_books_id = p_rsob_id ;
SELECT SUM((DECODE(et.event_type_classification, 'WRITE OFF',-1 * NVL(mcevt.revenue_amount,0),
NVL(mcevt.revenue_amount,0))))
INTO l_mc_revenue_amount
FROM pa_events e,
pa_mc_events mcevt,
pa_event_types et
WHERE e.event_type = et.event_type
AND e.project_id = p_project_id
AND nvl(e.task_id,-1) = nvl(p_task_id, nvl(e.task_id,-1))
AND e.event_id = mcevt.event_id
AND e.event_id <> NVL(p_event_id, -1)
AND mcevt.set_of_books_id = p_rsob_id
AND TRUNC(e.completion_date) <= TRUNC(nvl(p_accrue_through_date, sysdate))
*/
/* -------------------------------------------------------
Copy the value into the OUTPUT parameter
------------------------------------------------------- */
x_event_amount := l_mc_revenue_amount ;
SELECT SUM(DECODE(e.revenue_distributed_flag,'N', NVL(mcevt.revenue_amount,0),0)) revenue_amount
INTO l_mc_current_revenue
FROM pa_events e, pa_mc_events mcevt, pa_event_types et
WHERE e.project_id = p_project_id
AND nvl(e.task_id,-1) = nvl(p_task_id, nvl(e.task_id,-1))
AND e.event_id = mcevt.event_id
AND e.event_id <> NVL(p_event_id, -1)
AND mcevt.set_of_books_id = p_rsob_id
AND e.event_type = et.event_type
AND et.event_type_classification||'' = 'AUTOMATIC';
SELECT SUM(NVL(mcspf.total_baselined_amount,0) -
NVL(mcspf.total_accrued_amount,0))
-- INTO x_Funding_Amount
INTO l_lowest_revenue_amount
FROM pa_summary_project_fundings spf,
pa_mc_sum_proj_fundings mcspf,
pa_agreements_all a
WHERE a.agreement_id = spf.agreement_id
AND spf.task_id = p_task_id
AND spf.project_id = p_project_id
AND a.revenue_limit_flag = 'Y'
AND mcspf.set_of_books_id = p_rsob_id;
SELECT MIN(SUM(NVL(mcspf.total_baselined_amount,0)
- NVL(mcspf.total_accrued_amount,0))
* (100/pc.customer_bill_split) )
INTO l_lowest_revenue_amount
FROM pa_summary_project_fundings spf,
pa_mc_sum_proj_fundings mcspf,
pa_agreements_all a,
pa_projects p,
pa_project_customers pc
WHERE a.agreement_id = spf.agreement_id
AND p.project_id = spf.project_id
AND a.customer_id = pc.customer_id
AND pc.project_id = p.project_id
AND nvl(spf.task_id,-1) = nvl(p_task_id,-1)
AND spf.project_id = p_project_id
AND a.revenue_limit_flag = 'Y'
AND mcspf.project_id = spf.project_id
AND nvl(mcspf.task_id,-1) = nvl(spf.task_id,-1)
AND mcspf.agreement_id = spf.agreement_id
AND mcspf.set_of_books_id = p_rsob_id
GROUP BY pc.customer_id, pc.customer_bill_split;
SELECT SUM(NVL(mcevt.revenue_amount,0)) revenue_amount
INTO l_mc_revenue_amount
FROM pa_events e, pa_mc_events mcevt,
pa_billing_assignments bea,
pa_billing_extensions be
WHERE be.billing_extension_id = bea.billing_extension_id
AND e.project_id = p_project_id
AND nvl(e.task_id,-1) = nvl(p_task_id, nvl(e.task_id, -1))
AND e.event_id = mcevt.event_id
AND e.event_id <> nvl(p_event_id, -1)
AND mcevt.set_of_books_id = p_rsob_id
AND bea.billing_assignment_id = e.billing_assignment_id
AND be.procedure_name = 'pa_billing.ccrev'
AND e.revenue_distributed_flag||'' = 'N';
SELECT sum(nvl(mcerdl.amount,0))
INTO l_erdl_accrued_amount
FROM pa_draft_revenue_items dri, pa_mc_cust_event_rdl_all mcerdl,
pa_events e, pa_billing_assignments bea,
pa_billing_extensions be
WHERE dri.project_id = p_project_id
AND NVL(dri.task_id,-1) = NVL(p_task_id, nvl(dri.task_id, -1))
AND mcerdl.project_id = dri.project_id
AND NVL(mcerdl.task_id, -1) = NVL(dri.task_id, -1)
AND mcerdl.draft_revenue_num = dri.draft_revenue_num
AND mcerdl.line_num = dri.line_num
AND mcerdl.set_of_books_id = p_rsob_id
AND e.project_id = mcerdl.project_id
AND nvl(e.task_id,-1) = nvl(mcerdl.task_id, -1)
AND e.event_num = mcerdl.event_num
AND be.billing_extension_id = bea.billing_extension_id
AND bea.billing_assignment_id = e.billing_assignment_id
AND be.procedure_name = 'pa_billing.ccrev'; */ /* Check with SS for this ccrev join */
SELECT sum(nvl(mcrdl.amount,0))
INTO l_rdl_accrued_amount
FROM pa_draft_revenue_items dri, pa_mc_cust_rdl_all mcrdl
WHERE dri.project_id = p_project_id
AND NVL(dri.task_id,-1) = nvl(p_task_id, nvl(dri.task_id, -1))
AND mcrdl.project_id = dri.project_id
AND mcrdl.draft_revenue_num = dri.draft_revenue_num
AND mcrdl.line_num = dri.line_num
AND mcrdl.set_of_books_id = p_rsob_id
AND dri.revenue_source like 'Expenditure%' ; */