[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
INTO l_count
from pa_events e
WHERE e.project_id = X_project_id
and nvl(e.task_id,0) =
decode(X_task_id,
NULL, nvl(e.task_id,0), X_task_id )
and e.attribute10 LIKE 'CLOSE%'
and NOT EXISTS
( SELECT 'x'
from pa_events pe
WHERE pe.project_id = X_project_id
and nvl(pe.task_id,0) =
decode(X_task_id,
NULL, nvl(pe.task_id,0), X_task_id )
and pe.attribute10 LIKE 'REV%'
and pe.event_num_reversed = e.event_num
)
;
SELECT sum(nvl(dri.projfunc_revenue_amount,0))
INTO accrued_rev
FROM pa_draft_revenue_items dri
WHERE dri.project_id = X_project_id
AND nvl(dri.task_id,0) =
decode(X_task_id, NULL, nvl(dri.task_id,0), X_task_id);
SELECT sum(nvl(e.bill_trans_rev_amount,0))
INTO cost_accrued
FROM pa_events e
where e.project_id = X_project_id
and nvl(e.task_id,0) =
decode(X_task_id,
NULL, nvl(e.task_id,0), X_task_id )
and e.event_type = g_ca_event_type
;
SELECT sum(nvl(e.bill_trans_rev_amount,0))
INTO cost_accrued
FROM pa_events e
where e.project_id = X_project_id
and nvl(e.task_id,0) =
decode(X_task_id,
NULL, nvl(e.task_id,0), X_task_id )
and e.event_type = g_ca_contra_event_type
;
SELECT sum(nvl(e.bill_trans_rev_amount,0))
INTO cost_accrued
FROM pa_events e
where e.project_id = X_project_id
and nvl(e.task_id,0) =
decode(X_task_id,
NULL, nvl(e.task_id,0), X_task_id )
and e.event_type = g_ca_wip_event_type
;
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),
DECODE(P_cost_plan_type_id,NULL,default_cost_plan_type_id,
P_cost_plan_type_id), /* Added for Fin plan impact */
DECODE(P_rev_plan_type_id,NULL,default_rev_plan_type_id,
P_rev_plan_type_id) /* Added for Fin plan impact */
INTO l_cost_budget_type_code,
l_rev_budget_type_code,
l_cost_plan_type_id,
l_rev_plan_type_id
FROM pa_billing_extensions
WHERE billing_extension_id=pa_billing.GetBillingExtensionId;
/* Added this select for Fin plan Impact */
BEGIN
SELECT 'x'
INTO dummy
FROM dual
WHERE EXISTS( SELECT *
FROM pa_fin_plan_types_b f
WHERE f.fin_plan_type_id=l_cost_plan_type_id );
SELECT 'x'
INTO dummy
FROM pa_budget_types
WHERE budget_type_code = l_cost_budget_type_code
AND budget_amount_code = 'C';
/* Added this select for Fin plan Impact */
BEGIN
SELECT 'x'
INTO dummy
FROM dual
WHERE EXISTS( SELECT *
FROM pa_fin_plan_types_b f
WHERE f.fin_plan_type_id=l_rev_plan_type_id );
SELECT 'x'
INTO dummy
FROM pa_budget_types
WHERE budget_type_code = l_rev_budget_type_code
AND budget_amount_code = 'R';
/* Added this select for Fin plan Impact */
BEGIN
SELECT v.budget_version_id
INTO l_cost_plan_version_id
FROM pa_budget_versions v
WHERE v.project_id = X2_project_id
AND v.current_flag = 'Y'
AND v.budget_status_code = 'B'
AND v.fin_plan_type_id = l_cost_plan_type_id
AND v.version_type IN ('COST','ALL');
SELECT budget_version_id
INTO l_cost_budget_version_id
FROM pa_budget_versions pbv
WHERE project_id = X2_project_id
AND budget_type_code = l_cost_budget_type_code
AND budget_status_code = 'B'
AND current_flag = 'Y';
/* Added this select for Fin plan Impact */
BEGIN
SELECT v.budget_version_id
INTO l_rev_plan_version_id
FROM pa_budget_versions v
WHERE v.project_id = X2_project_id
AND v.current_flag = 'Y'
AND v.budget_status_code = 'B'
AND v.fin_plan_type_id = l_rev_plan_type_id
AND v.version_type IN ('REVENUE','ALL');
SELECT budget_version_id
INTO l_rev_budget_version_id
FROM pa_budget_versions pbv
WHERE project_id = X2_project_id
AND budget_type_code = l_rev_budget_type_code
AND budget_status_code = 'B'
AND current_flag = 'Y';
pa_billing_pub.insert_message
(X_inserting_procedure_name =>'pa_rev_ca.get_budget_amount',
X_attribute1 => l_cost_budget_type_code,
X_attribute2 => l_rev_budget_type_code,
X_message => status,
X_error_message=>err_msg,
X_status=>err_status);
SELECT *
from pa_events e
WHERE e.project_id = X_project_id
and nvl(e.task_id,0) =
decode(X_task_id,
NULL, nvl(e.task_id,0), X_task_id )
and e.attribute10 LIKE 'CLOSE%'
and NOT EXISTS
( SELECT 'x'
from pa_events pe
WHERE pe.project_id = X_project_id
and nvl(pe.task_id,0) =
decode(X_task_id,
NULL, nvl(pe.task_id,0), X_task_id )
and pe.attribute10 LIKE 'REV%'
and pe.event_num_reversed = e.event_num
)
)
LOOP
-- Event description will show the event number reversed by this event
--
Event_Description := 'reversing event num = ' || r_rec.event_num;
pa_billing_pub.insert_event (
X_rev_amt => (-1) * r_rec.revenue_amount,
X_bill_amt => 0,
X_event_type =>r_rec.event_type ,
X_event_description => event_description,
X_event_num_reversed => r_rec.event_num,
X_attribute10 => l_event_set_id,
X_audit_amount1 => r_rec.audit_amount1,
X_audit_amount2 => r_rec.audit_amount2,
X_audit_amount3 => r_rec.audit_amount3,
X_audit_amount4 => r_rec.audit_amount4,
X_audit_cost_budget_type_code => r_rec.audit_cost_budget_type_code,
X_audit_rev_budget_type_code => r_rec.audit_rev_budget_type_code,
X_error_message =>l_error_message,
X_status => l_status);
/** public api to insert event **/
pa_billing_pub.insert_event (
X_rev_amt => cost_accrual,
X_bill_amt => 0,
X_event_type =>g_ca_contra_event_type ,
X_event_description => event_description,
X_attribute10 => l_event_set_id,
X_audit_amount1 => X_revenue_amount,
X_audit_amount2 => X_budget_revenue,
X_audit_amount3 => X_budget_cost,
X_audit_amount4 => X_cost_accrued,
X_audit_cost_budget_type_code => X_audit_cost_budget_type_code,
X_audit_rev_budget_type_code => X_audit_rev_budget_type_code,
X_audit_cost_plan_type_id => X_cost_plan_type_id, /* Added for fin plan impact */
X_audit_rev_plan_type_id => X_rev_plan_type_id, /* Added for fin plan impact */
X_error_message =>l_error_message,
X_status => l_status);
pa_billing_pub.insert_event (
X_rev_amt => (-1) * cost_accrual,
X_bill_amt => 0,
X_event_description => event_description,
X_event_type => g_ca_event_type ,
X_attribute10 => l_event_set_id,
X_audit_amount1 => X_revenue_amount,
X_audit_amount2 => X_budget_revenue,
X_audit_amount3 => X_budget_cost,
X_audit_amount4 => X_cost_accrued,
X_audit_cost_budget_type_code =>x_audit_cost_budget_type_code,
X_audit_rev_budget_type_code => x_audit_rev_budget_type_code,
X_audit_cost_plan_type_id => X_cost_plan_type_id, /* Added for fin plan impact */
X_audit_rev_plan_type_id => X_rev_plan_type_id, /* Added for fin plan impact */
X_error_message =>l_error_message,
X_status => l_status
);
SELECT sum(decode(g_ca_budget_type,'R',nvl(cdl.amount,0),
(nvl(cdl.burdened_cost,0)+nvl(cdl.project_burdened_change,0))))
INTO cost_WIP
FROM pa_cost_distribution_lines_all cdl,
pa_expenditure_items_all ei,
pa_tasks t
WHERE t.project_id = X_project_id
AND (t.top_task_id = X_top_task_id
OR X_top_task_id IS NULL)
AND ei.task_id = t.task_id
AND ei.Project_ID = X_project_id -- Perf Bug 2695266
AND cdl.expenditure_item_id = ei.expenditure_item_id
AND cdl.line_type = 'R'
;
/** public api to insert event **/
IF cost_WIP <> 0 THEN /* Added for bug 3788835: if project doesnot have EIs then this would be 0 */
pa_billing_pub.insert_event (
X_rev_amt => cost_WIP,
X_bill_amt => 0,
X_event_type => g_ca_wip_event_type ,
X_event_description => event_description,
X_attribute10 => l_event_set_id,
X_audit_amount1 => X_revenue_amount,
X_audit_amount2 => X_budget_revenue,
X_audit_amount3 => X_budget_cost,
X_audit_amount4 => X_cost_accrued,
X_audit_cost_budget_type_code => X_audit_cost_budget_type_code,
X_audit_rev_budget_type_code => X_audit_rev_budget_type_code,
X_audit_cost_plan_type_id => X_cost_plan_type_id, /* Added for fin plan impact */
X_audit_rev_plan_type_id => X_rev_plan_type_id, /* Added for fin plan impact */
X_error_message =>l_error_message,
X_status => l_status);
pa_billing_pub.insert_event (
X_rev_amt => cost_accrual_contra,
X_bill_amt => 0,
X_event_description => event_description,
X_event_type => g_ca_contra_event_type ,
X_attribute10 => l_event_set_id,
X_audit_amount1 => X_revenue_amount,
X_audit_amount2 => X_budget_revenue,
X_audit_amount3 => X_budget_cost,
X_audit_amount4 => X_cost_accrued,
X_audit_cost_budget_type_code =>x_audit_cost_budget_type_code,
X_audit_rev_budget_type_code => x_audit_rev_budget_type_code,
X_audit_cost_plan_type_id => X_cost_plan_type_id, /* Added for fin plan impact */
X_audit_rev_plan_type_id => X_rev_plan_type_id, /* Added for fin plan impact */
X_error_message =>l_error_message,
X_status => l_status
);
pa_billing_pub.insert_event (
X_rev_amt => cost_accrual,
X_bill_amt => 0,
X_event_description => event_description,
X_event_type => g_ca_event_type,
X_attribute10 => l_event_set_id,
X_audit_amount1 => X_revenue_amount,
X_audit_amount2 => X_budget_revenue,
X_audit_amount3 => X_budget_cost,
X_audit_amount4 => X_cost_accrued,
X_audit_cost_budget_type_code =>x_audit_cost_budget_type_code,
X_audit_rev_budget_type_code => x_audit_rev_budget_type_code,
X_audit_cost_plan_type_id => X_cost_plan_type_id, /* Added for fin plan impact */
X_audit_rev_plan_type_id => X_rev_plan_type_id, /* Added for fin plan impact */
X_error_message =>l_error_message,
X_status => l_status
);
SELECT sum(decode(g_ca_budget_type,'R',nvl(cdl.amount,0),
(nvl(cdl.burdened_cost,0)+nvl(cdl.project_burdened_change,0))))
INTO l_cost_wip_amount
FROM pa_cost_distribution_lines_all cdl,
pa_expenditure_items_all ei,
pa_tasks t
WHERE t.project_id = p_project_id
AND (t.top_task_id = p_task_id
OR p_task_id IS NULL)
AND ei.task_id = t.task_id
AND ei.Project_ID = P_project_id -- Perf Bug 2695266
AND cdl.expenditure_item_id = ei.expenditure_item_id
AND cdl.line_type = 'R'
;
SELECT attribute12 , attribute13 , attribute14 , attribute15
INTO g_ca_event_type ,g_ca_contra_event_type,g_ca_wip_event_type,g_ca_budget_type
FROM pa_billing_extensions
WHERE billing_extension_id = X_billing_extension_id;
PA_MCB_INVOICE_PKG.log_message('Before pa_billing_extn_params_v select pa_rev_ca.calc_ca_amt :');
SELECT default_cost_plan_type_id,default_rev_plan_type_id
INTO l_cost_plan_type_id,l_rev_plan_type_id
FROM pa_billing_extn_params_v;
SELECT pps.project_system_status_code
INTO l_project_status
FROM pa_projects_all ppa , pa_project_statuses pps
WHERE ppa.project_id = x_project_id
AND ppa.project_status_code = pps.project_status_code
AND pps.status_type = 'PROJECT';
SELECT sum(decode(l_ca_budget_type,'R',nvl(cdl.amount,0),
(nvl(burdened_cost,0)+nvl(cdl.project_burdened_change,0))))
INTO l_raw_cost_itd
FROM pa_cost_distribution_lines_all cdl,
pa_expenditure_items_all ei,
pa_tasks t
WHERE t.project_id = x_project_id
AND (nvl(t.top_task_id,0) =
decode(x_status_view , 'TASKS', x_task_id , nvl(t.top_task_id,0)))
AND ei.task_id = t.task_id
AND ei.Project_ID = X_project_id -- Perf Bug 2695266
AND cdl.expenditure_item_id = ei.expenditure_item_id
AND cdl.line_type = 'R'
AND cdl.resource_accumulated_flag = 'Y'
;
SELECT sum(decode(l_ca_budget_type,'R',nvl(cdl.amount,0),
(nvl(burdened_cost,0)+nvl(cdl.project_burdened_change,0))))
INTO l_raw_cost_ptd
FROM pa_cost_distribution_lines_all cdl,
pa_expenditure_items_all ei,
pa_tasks t,
pa_periods pp
WHERE pp.current_pa_period_flag = 'Y'
AND TRUNC(cdl.pa_date) BETWEEN pp.start_date AND pp.end_date
AND t.project_id = x_project_id
AND (nvl(t.top_task_id,0) =
decode(x_status_view , 'TASKS', x_task_id , nvl(t.top_task_id,0)))
AND ei.task_id = t.task_id
AND ei.Project_ID = X_project_id -- Perf Bug 2695266
AND cdl.expenditure_item_id = ei.expenditure_item_id
AND cdl.line_type = 'R'
AND cdl.resource_accumulated_flag = 'Y'
;
SELECT sum(decode(pe.event_type,l_ca_wip_event_type,
nvl(erdl.projfunc_revenue_amount,0),0)),
sum(decode(pe.event_type,l_ca_event_type,
nvl(erdl.projfunc_revenue_amount,0),0))
INTO l_accounted_cost_WIP_itd,
l_cost_accrual_itd
FROM pa_events pe, pa_cust_event_rdl_all erdl,
pa_draft_revenues_all dr
WHERE pe.event_num = erdl.event_num
AND pe.project_id = erdl.project_id
AND nvl(pe.task_id,0) = nvl(erdl.task_id,0)
AND nvl(pe.task_id,0) =
decode(x_status_view , 'TASKS', x_task_id , nvl(pe.task_id,0))
AND pe.project_id = x_project_id
AND erdl.draft_revenue_num = dr.draft_revenue_num
AND erdl.project_id = dr.project_id
AND pe.event_type IN (l_ca_wip_event_type , l_ca_event_type)
AND dr.resource_accumulated_flag = 'Y'
;
SELECT sum(decode(pe.event_type,l_ca_wip_event_type,
nvl(erdl.projfunc_revenue_amount,0),0)),
sum(decode(pe.event_type,l_ca_event_type,
nvl(erdl.projfunc_revenue_amount,0),0))
INTO l_accounted_cost_WIP_ptd,
l_cost_accrual_ptd
FROM pa_events pe, pa_cust_event_rdl_all erdl,
pa_draft_revenues_all dr , pa_periods pp /* Bug# 2197991 */
WHERE pp.current_pa_period_flag = 'Y'
-- AND TRUNC(dr.pa_date) = pp.end_date
AND TRUNC(dr.pa_date) BETWEEN pp.start_date AND pp.end_date -- Modified for PA/GL period enhancements
AND pe.event_num = erdl.event_num
AND pe.project_id = erdl.project_id
AND nvl(pe.task_id,0) = nvl(erdl.task_id,0)
AND nvl(pe.task_id,0) =
decode(x_status_view , 'TASKS', x_task_id , nvl(pe.task_id,0))
AND pe.project_id = x_project_id
AND erdl.draft_revenue_num = dr.draft_revenue_num
AND erdl.project_id = dr.project_id
AND pe.event_type IN (l_ca_wip_event_type , l_ca_event_type)
AND dr.resource_accumulated_flag = 'Y'
;
SELECT sum(nvl(dri.projfunc_revenue_amount,0))
INTO l_revenue_ptd
FROM pa_draft_revenue_items dri,
pa_draft_revenues_all dr,
pa_periods pp /* Bug# 2197991 */
WHERE dri.project_id = x_project_id
AND nvl(dri.task_id,0) =
decode(x_status_view,
'TASKS', x_task_id , nvl(dri.task_id,0))
AND dri.draft_revenue_num = dr.draft_revenue_num
AND dri.project_id = dr.project_id
-- AND TRUNC(dr.pa_date) = pp.end_date
AND TRUNC(dr.pa_date) BETWEEN pp.start_date AND pp.end_date -- Modified for PA/GL period enhancements
AND dr.resource_accumulated_flag = 'Y'
AND pp.current_pa_period_flag = 'Y';
select project_system_status_code
into l_new_system_status_code
from pa_project_statuses
where project_status_code = x_new_proj_status_code
and status_type = 'PROJECT';
select project_system_status_code
into l_old_system_status_code
from pa_project_statuses
where project_status_code = x_old_proj_status_code
and status_type = 'PROJECT';
select 'Y',
nvl(p.project_level_funding_flag ,'X') ,
be.attribute12,
be.attribute13,
be.attribute14,
be.attribute15
INTO l_cost_accrual_flag,
l_funding_flag,
l_ca_event_type,
l_ca_contra_event_type,
l_ca_wip_event_type,
l_ca_budget_type
from pa_billing_extensions be,
pa_billing_assignments_all bea,
pa_projects_all p
where p.project_id = p_project_id
and bea.active_flag = 'Y'
and bea.billing_extension_id = be.billing_extension_id
and be.attribute11 = 'COST-ACCRUAL'
and bea.project_id = p_project_id
order by be.processing_order, bea.billing_assignment_id;
Select Project_type, Org_ID , NVL(PROJECT_LEVEL_FUNDING_FLAG,'X')
INTO l_Project_Type, l_Org_ID, l_funding_flag1
from PA_PROJECTS_ALL
where Project_ID = P_Project_ID;
select 'Y',
l_funding_flag1,
be.attribute12,
be.attribute13,
be.attribute14,
be.attribute15
INTO l_cost_accrual_flag,
l_funding_flag,
l_ca_event_type,
l_ca_contra_event_type,
l_ca_wip_event_type,
l_ca_budget_type
from pa_billing_extensions be,
pa_billing_assignments_all bea
where bea.active_flag = 'Y'
and bea.billing_extension_id = be.billing_extension_id
and be.attribute11 = 'COST-ACCRUAL'
and bea.project_type = l_Project_Type
and bea.org_id = l_Org_ID
order by be.processing_order, bea.billing_assignment_id;