[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* SELECT nvl(sum(nvl(a.burdened_cost,0)),0)
INTO new_cost
FROM pa_proj_ccrev_cost_v a
WHERE a.project_id = X2_project_id
AND a.task_id= nvl(X2_task_id,a.task_id)
AND TRUNC(nvl(X2_accrue_through_date,sysdate)) >= trunc(a.pa_start_date);/* BUG#3118592 */
/* Split select into two parts for bug 4251205 */
/* Commented for bug 4860032 Forward port of bug 4646775 */
/*IF X2_task_id is NULL THEN
SELECT nvl(sum(nvl(a.burdened_cost,0)),0)
INTO new_cost
FROM pa_proj_ccrev_cost_v a
WHERE a.project_id = X2_project_id
AND TRUNC(nvl(X2_accrue_through_date,sysdate)) >= trunc(a.pa_start_date);
SELECT nvl(sum(nvl(a.burdened_cost,0)),0)
INTO new_cost
FROM pa_proj_ccrev_cost_v a
WHERE a.project_id = X2_project_id
AND a.task_id= X2_task_id
AND TRUNC(nvl(X2_accrue_through_date,sysdate)) >= trunc(a.pa_start_date);
select sum(BURDENED_COST)
INTO new_cost
from (
SELECT sum(nvl(ta.tot_burdened_cost, nvl(ta.tot_raw_cost,0)) +
nvl(ta.i_tot_burdened_cost, nvl(i_tot_raw_cost,0))) BURDENED_COST
FROM
pa_txn_accum ta
WHERE ta.project_id = X2_project_id
AND EXISTS
(
select 1
from pa_periods pp
where nvl(X2_accrue_through_date,sysdate) >= trunc(pp.start_date) --Removed to_date
and pp.period_name = ta.pa_period
)
AND EXISTS (select 1 from pa_tasks t
where t.task_id = ta.task_id
and exists (select 1 from pa_tasks t1
where t1.task_id = t.top_task_id
and decode(l_calling_process,'Revenue',t1.ready_to_distribute_flag,
'Invoice',t1.ready_to_bill_flag,0) = 'Y')
) /* Exists clause added for bug 7299493 */
UNION ALL
SELECT sum(nvl((cdl.burdened_cost + nvl(project_burdened_change,0)), nvl(cdl.amount,0))) BURDENED_COST
FROM pa_cost_distribution_lines_all cdl
WHERE cdl.resource_accumulated_flag = 'N'
AND cdl.line_type = 'R'
AND cdl.project_id = X2_project_id
AND EXISTS
(
select 1
from pa_periods pp
where nvl(X2_accrue_through_date,sysdate) >= trunc(pp.start_date) --Removed to_date
and cdl.pa_date between pp.start_date and pp.end_date
)
AND EXISTS (select 1 from pa_tasks t
where t.task_id = cdl.task_id
and exists (select 1 from pa_tasks t1
where t1.task_id = t.top_task_id
and decode(l_calling_process,'Revenue',t1.ready_to_distribute_flag,
'Invoice',t1.ready_to_bill_flag,0) = 'Y')
) /* Exists clause added for bug 7299493 */
);
select sum(BURDENED_COST)
INTO new_cost
from (
SELECT sum(nvl(ta.tot_burdened_cost, nvl(ta.tot_raw_cost,0)) +
nvl(ta.i_tot_burdened_cost, nvl(i_tot_raw_cost,0))) BURDENED_COST
FROM
pa_txn_accum ta,
pa_tasks t
WHERE ta.task_id = t.task_id
AND t.top_task_id = X2_task_id
AND t.project_id = X2_project_id
AND ta.project_id = X2_project_id
AND ta.project_id = t.project_id
and exists (select 1 from pa_tasks t1
where t1.task_id = t.top_task_id
and decode(l_calling_process,'Revenue',t1.ready_to_distribute_flag,
'Invoice',t1.ready_to_bill_flag,0) = 'Y') /* Exists clause added for bug 7299493 */
AND EXISTS
(
select 1
from pa_periods pp
where nvl(X2_accrue_through_date,sysdate) >= trunc(pp.start_date) --Removed to_date
and pp.period_name = ta.pa_period
)
UNION ALL
SELECT sum(nvl((cdl.burdened_cost + nvl(project_burdened_change,0)), nvl(cdl.amount,0))) BURDENED_COST
FROM pa_cost_distribution_lines_all cdl /* Added _all for bug 5953670*/
/*pa_tasks commented for bug 6521198*/
WHERE EXISTS ( select 1 from pa_tasks t
WHERE cdl.project_id = t.project_id
AND cdl.task_id = t.task_id
AND t.project_id = X2_project_id
AND t.top_task_id = X2_task_id
and exists (select 1 from pa_tasks t1
where t1.task_id = t.top_task_id
and decode(l_calling_process,'Revenue',t1.ready_to_distribute_flag,
'Invoice',t1.ready_to_bill_flag,0) = 'Y') /* Exists clause added for bug 7294641 */
)
AND cdl.resource_accumulated_flag = 'N'
AND cdl.line_type = 'R'
AND cdl.project_id = X2_project_id
AND EXISTS
(
select 1
from pa_periods pp
where nvl(X2_accrue_through_date,sysdate) >= trunc(pp.start_date) --Removed to_date
AND cdl.pa_date between pp.start_date and pp.end_date
)
);
SELECT NVL(e.bill_trans_rev_amount,0) trans_rev_amount,e.bill_trans_currency_code,
e.projfunc_currency_code,e.projfunc_rate_type,e.projfunc_rate_date,e.projfunc_exchange_rate
FROM pa_events e,
pa_billing_assignments bea,
pa_billing_extensions be
where be.billing_extension_id = bea.billing_extension_id
and e.project_id = X2_project_id
and nvl(e.task_id,0) = decode(X2_task_id,NULL,nvl(e.task_id,0), X2_task_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(dri.projfunc_revenue_amount,0)) /* change this column from amount to projfunc_revenue_amount for MCB2 */
INTO accrued_ccrev
FROM pa_draft_revenue_items dri
WHERE dri.project_id = X2_project_id
AND nvl(dri.task_id,0) = decode(X2_task_id, NULL, nvl(dri.task_id,0), X2_task_id )
AND (EXISTS ( select '1'
from pa_cust_event_rev_dist_lines erdl,
pa_events e,
pa_billing_assignments bea,
pa_billing_extensions be
where be.billing_extension_id = bea.billing_extension_id
and bea.billing_assignment_id = e.billing_assignment_id
and e.project_id = erdl.project_id
and e.event_num = erdl.event_num
and nvl(e.task_id,0) = nvl(erdl.task_id, 0)
and erdl.project_id = dri.project_id
and erdl.draft_revenue_num = dri.draft_revenue_num
and erdl.draft_revenue_item_line_num = dri.line_num
and be.procedure_name = 'pa_billing.ccrev')
OR dri.revenue_source like 'Expenditure%');
/* SELECT sum(nvl(e.revenue_amount,0))
INTO pending_ccrev
FROM pa_events e,
pa_billing_assignments bea,
pa_billing_extensions be
where be.billing_extension_id = bea.billing_extension_id
and e.project_id = X2_project_id
and nvl(e.task_id,0) =
decode(X2_task_id,
NULL, nvl(e.task_id,0), X2_task_id )
and bea.billing_assignment_id = e.billing_assignment_id
and be.procedure_name = 'pa_billing.ccrev'
and e.revenue_distributed_flag||'' = 'N'; */
SELECT (DECODE(et.event_type_classification,
'WRITE OFF',-1 * NVL(e.bill_trans_rev_amount,0),
'RLZED_LOSSES',-1 * NVL(e.bill_trans_rev_amount,0),
NVL(e.bill_trans_rev_amount,0))) trans_rev_amount,
(DECODE(et.event_type_classification,'INVOICE REDUCTION', -1 * NVL(e.bill_trans_bill_amount,0),
NVL(e.bill_trans_bill_amount,0))) trans_bill_amount,
e.bill_trans_currency_code,e.projfunc_currency_code,e.projfunc_rate_type,
e.projfunc_rate_date,e.projfunc_exchange_rate
FROM pa_events e,
pa_event_types et
WHERE e.event_type = et.event_type
AND e.project_id = X2_project_id
AND nvl(e.task_id,0) = nvl(X2_task_id, nvl(e.task_id,0))
AND e.completion_date <= nvl(X2_accrue_through_date, sysdate)
AND NOT EXISTS ( select 'cost-cost event'
from pa_billing_assignments bea,
pa_billing_extensions be
where be.billing_extension_id = bea.billing_extension_id
and bea.billing_assignment_id = e.billing_assignment_id
and be.procedure_name = 'pa_billing.ccrev');
/* SELECT sum(decode(et.event_type_classification,
'WRITE OFF', -1 * nvl(revenue_amount,0),
nvl(revenue_amount,0))),
sum(decode(et.event_type_classification,
'INVOICE REDUCTION', -1 * nvl(bill_amount,0),
nvl(bill_amount,0)))
INTO X2_revenue_amount,
X2_invoice_amount
FROM pa_events e,
pa_event_types et
WHERE e.event_type = et.event_type
AND e.project_id = X2_project_id
AND nvl(e.task_id,0) = nvl(X2_task_id, nvl(e.task_id,0))
AND e.completion_date <= nvl(X2_accrue_through_date, sysdate)
AND NOT EXISTS ( select 'cost-cost event'
from pa_billing_assignments bea,
pa_billing_extensions be
where be.billing_extension_id = bea.billing_extension_id
and bea.billing_assignment_id = e.billing_assignment_id
and be.procedure_name = 'pa_billing.ccrev'); */
SELECT NVL(e.bill_trans_bill_amount,0) trans_bill_amount,e.bill_trans_currency_code,
e.projfunc_currency_code,
e.projfunc_rate_type,e.projfunc_rate_date,e.projfunc_exchange_rate
FROM pa_events e,
pa_billing_assignments bea,
pa_billing_extensions be
WHERE be.billing_extension_id = bea.billing_extension_id
AND bea.billing_assignment_id = e.billing_assignment_id
AND e.project_id = X2_project_id
AND be.procedure_name = 'pa_billing.ccrev'
AND nvl(e.task_id,0) = decode(X2_task_id,
NULL, nvl(e.task_id,0), X2_task_id)
AND NOT EXISTS
(select 'billed'
from pa_draft_invoice_items pdii
where pdii.project_id = e.project_id
and pdii.event_num = e.event_num
and nvl(pdii.task_id,0) = nvl(e.task_id,0));
SELECT sum(nvl(e.bill_amount,0))
INTO pending_ccinv
from pa_events e,
pa_billing_assignments bea,
pa_billing_extensions be
where be.billing_extension_id = bea.billing_extension_id
and bea.billing_assignment_id = e.billing_assignment_id
and e.project_id = X2_project_id
and be.procedure_name = 'pa_billing.ccrev'
and nvl(e.task_id,0) = decode(X2_task_id,
NULL, nvl(e.task_id,0), X2_task_id)
and NOT EXISTS
(select 'billed'
from pa_draft_invoice_items pdii
where pdii.project_id = e.project_id
and pdii.event_num = e.event_num
and nvl(pdii.task_id,0) = nvl(e.task_id,0)); */
SELECT sum(nvl(dii.projfunc_bill_amount,0))
INTO billed_ccinv
FROM pa_draft_invoice_items dii
WHERE dii.project_id = X2_project_id
AND (EXISTS (select '1'
from pa_events e,
pa_billing_assignments bea,
pa_billing_extensions be
where be.billing_extension_id = bea.billing_extension_id
and bea.billing_assignment_id = e.billing_assignment_id
and dii.project_id = e.project_id
and dii.event_num = e.event_num
and nvl(dii.event_task_id,0) = nvl(e.task_id,0)
and be.procedure_name = 'pa_billing.ccrev')
OR EXISTS ( select '1'
from pa_cust_rev_dist_lines erdl
where erdl.project_id = dii.project_id
and erdl.draft_invoice_num = dii.draft_invoice_num
and erdl.draft_invoice_item_line_num = dii.line_num));
SELECT sum(nvl(rdl.projfunc_bill_amount,0))
INTO task_billed_ccinv
FROM pa_cust_rev_dist_lines rdl,
pa_expenditure_items_all ei,
pa_tasks t
WHERE ei.task_id = t.task_id
AND ei.expenditure_item_id = rdl.expenditure_item_id
AND rdl.project_id = X2_project_id
AND t.top_task_id = X2_task_id
AND rdl.draft_invoice_num IS NOT NULL;
SELECT sum(nvl(pdii.projfunc_bill_amount,0))
INTO task_billed_ev_ccinv
FROM pa_draft_invoice_items pdii
WHERE pdii.event_task_id = X2_task_id
AND pdii.Project_ID = X2_Project_ID -- Perf Bug 2695243
AND EXISTS (select '1'
from pa_events e,
pa_billing_assignments bea,
pa_billing_extensions be
where be.billing_extension_id = bea.billing_extension_id
and bea.billing_assignment_id = e.billing_assignment_id
and pdii.project_id = e.project_id
and pdii.event_num = e.event_num
and pdii.event_task_id = e.task_id
and be.procedure_name = 'pa_billing.ccrev');
SELECT DECODE(e.revenue_distributed_flag,'N', NVL(e.bill_trans_rev_amount,0),0) trans_rev_amount,
DECODE(pdii.event_num,NULL, NVL(e.bill_trans_bill_amount,0),0) trans_bill_amount,
e.bill_trans_currency_code,
e.projfunc_currency_code,e.projfunc_rate_type,e.projfunc_rate_date,e.projfunc_exchange_rate
FROM pa_events e, pa_event_types et, pa_draft_invoice_items pdii
WHERE e.project_id = X2_project_id
AND pdii.project_id (+)= e.project_id
AND pdii.event_num (+)= e.event_num
AND nvl(pdii.event_task_id,0) = nvl(e.task_id,0)
AND nvl(e.task_id,0) = nvl(X2_task_id,0)
AND e.event_type = et.event_type
AND et.event_type_classification||'' = 'AUTOMATIC';
SELECT sum(decode(e.revenue_distributed_flag,
'N', nvl(e.revenue_amount,0),
0)),
sum(decode(pdii.event_num,
NULL, nvl(e.bill_amount,0),
0))
INTO current_event_revenue, current_event_invoice
FROM pa_events e, pa_event_types et, pa_draft_invoice_items pdii
WHERE e.project_id = X2_project_id
AND pdii.project_id (+)= e.project_id
and pdii.event_num (+)= e.event_num
and nvl(pdii.event_task_id,0) = nvl(e.task_id,0)
AND nvl(e.task_id,0) = nvl(X2_task_id,0)
and e.event_type = et.event_type
and et.event_type_classification||'' = 'AUTOMATIC';
/** Bug # 505759 , changed the select to use (100/pc.customer_bill_split)
rather than (pc.customer_bill_split * .01)
**/
/* MCB2: Change the name from total_accrued_amount to projfunc_accrued_amount,
total_billed_amount to projfunc_billed_amount, and total_baselined_amount to
projfunc_baselined_amount */
-- Following changes are made for FP_M : Top Task customer changes
l_Enable_Top_Task_Cust_Flag := PA_Billing_Pub.Get_Top_Task_Customer_Flag (
P_Project_ID => l_Project_ID );
SELECT sum(nvl(psf.projfunc_baselined_amount,0)
- nvl(psf.projfunc_accrued_amount,0)),
sum(nvl(psf.projfunc_baselined_amount,0)
- nvl(psf.projfunc_billed_amount,0))
INTO lowest_revenue_amount,
lowest_invoice_amount
FROM pa_summary_project_fundings psf,
pa_agreements_all a
WHERE a.agreement_id = psf.agreement_id
AND psf.project_id = X2_project_id
AND psf.task_id = X2_task_id
AND DECODE (X2_calling_process,'Revenue',
a.revenue_limit_flag||'','Invoice',
a.invoice_limit_flag||'') = 'Y' ;
SELECT min(sum(nvl(psf.projfunc_baselined_amount,0)
- nvl(psf.projfunc_accrued_amount,0))
* (100/nvl(pc.customer_bill_split,100)) ), /*Bug 5718115*/
min(sum(nvl(psf.projfunc_baselined_amount,0)
- nvl(psf.projfunc_billed_amount,0))
* (100/nvl(pc.customer_bill_split,100)) ) /* Bug 5718115*/
INTO lowest_revenue_amount,
lowest_invoice_amount
FROM pa_summary_project_fundings psf,
pa_agreements_all a, /* Changed table from pa_agreements to pa_agreements_all for MCB2 */
pa_projects p,
pa_project_customers pc
WHERE
a.agreement_id = psf.agreement_id
AND p.project_id = psf.project_id
AND a.customer_id = pc.customer_id
AND pc.project_id = p.project_id
AND nvl(psf.task_id,0) = nvl(X2_task_id,0)
AND psf.project_id = X2_project_id
AND DECODE (X2_calling_process,'Revenue',a.revenue_limit_flag||'','Invoice',a.invoice_limit_flag||'') = 'Y'
GROUP BY pc.customer_id, pc.customer_bill_split;
SELECT sum(decode(X_which, 'I', nvl(rdl.projfunc_bill_amount,0),
'R', NVL(rdl.projfunc_revenue_amount,0),NVL(rdl.projfunc_revenue_amount,0)))
INTO Ramount
FROM pa_cust_rev_dist_lines rdl
WHERE rdl.expenditure_item_id = X_eiid
AND (X_adj = 'ADJ'
AND (rdl.line_num_reversed IS NOT NULL
OR X_ei_adj = 'Y')
OR (X_adj = 'REG'
AND (rdl.line_num_reversed IS NULL
and rdl.reversed_flag IS NULL
and X_ei_adj = 'N')));
SELECT Decode(SUBSTR(distribution_rule,INSTR(distribution_rule,'/')+1),'COST','x','y') /* Added Decode for Bug 2389765 */
INTO l_dummy
FROM pa_projects_all
WHERE project_id = X_project_id
AND substr(distribution_rule,1,instr(distribution_rule,'/')-1)
IN ('COST','EVENT')
AND exists ( select 'x'
from pa_events e,
pa_event_types et
where e.project_id = X_project_id
and e.event_type = et.event_type
and et.event_type_classification = 'SCHEDULED PAYMENTS');
SELECT v.budget_version_id
INTO l_cost_budget_version_id
FROM pa_budget_versions v
WHERE v.project_id = X_project_id
AND v.current_flag = 'Y'
AND v.budget_status_code = 'B'
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 = X_project_id
AND budget_type_code = 'AC'
AND budget_status_code = 'B'
AND current_flag = 'Y';
SELECT v.budget_version_id
INTO l_rev_budget_version_id
FROM pa_budget_versions v
WHERE v.project_id = X_project_id
AND v.current_flag = 'Y'
AND v.budget_status_code = 'B'
AND v.version_type IN ('REVENUE','ALL')
AND v.approved_rev_plan_type_flag = 'Y' ; /* Added for bug 4059918 */
SELECT budget_version_id
INTO l_rev_budget_version_id
FROM pa_budget_versions pbv
WHERE project_id = X_project_id
AND budget_type_code = 'AR'
AND budget_status_code = 'B'
AND current_flag = 'Y';