The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT f.budget_type_code budget_type_code,
NVL(SUM(f.allocated_amount),0) tot_amt
FROM pa_project_fundings f
WHERE f.project_id = pl_project_id
AND f.agreement_id = pl_agreement_id
AND nvl(f.task_id,-99) = nvl(pl_task_id,-99)
GROUP BY f.budget_type_code;
SELECT NVL(SUM(dri.amount),0) dri_amount
INTO pl_accr_rev
FROM pa_draft_revenue_items dri,
pa_draft_revenues_all dr
WHERE dri.project_id = dr.project_id
AND dri.draft_revenue_num = dr.draft_revenue_num
AND ( NVL(p_task_id,0) = 0
OR dri.task_id = p_task_id )
AND dr.project_id = p_project_id
AND dr.agreement_id = p_agreement_id;
SELECT sum(dii.amount) dii_amount
INTO pl_billed
FROM pa_draft_invoice_items dii,
pa_draft_invoices_all di
WHERE dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND di.project_id = p_project_id
AND di.agreement_id = p_agreement_id
AND dii.invoice_line_type <> 'RETENTION';
SELECT round(sum(dii.amount * (1 -
( nvl(di.retention_percentage,0)/100 )) ),2) dii_amount
INTO pl_billed
FROM pa_draft_invoice_items dii,
pa_draft_invoices_all di
WHERE dii.project_id = di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND dii.task_id = p_task_id
AND dii.invoice_line_type <> 'RETENTION'
AND di.project_id = p_project_id
AND di.agreement_id = p_agreement_id;
| Procedure to update the adjusted amount in project funding table |
| and summary project funding efc table |
----------------------------------------------------------------------------*/
PROCEDURE Update_Adjusted_Amount (p_project_id IN NUMBER,
p_agreement_id IN NUMBER,
p_task_id IN NUMBER,
p_adjusted IN NUMBER) IS
-- get the latest funding id to update the adjusted amount
CURSOR cur_adj IS
SELECT f.project_funding_id project_funding_id
FROM pa_project_fundings f
WHERE f.project_id = p_project_id
AND f.agreement_id = p_agreement_id
AND NVL(f.task_id,-99)= NVL(p_task_id,-99)
AND f.budget_type_code = 'BASELINE'
ORDER BY creation_date DESC;
update_flag BOOLEAN:= FALSE;
-- update the Project Funding record
UPDATE pa_project_fundings pf
SET pf.allocated_amount=(pf.allocated_amount+p_adjusted)
WHERE pf.project_funding_id = rec_adj.project_funding_id;
-- update the Project Funding EFC record
UPDATE pa_project_fundings_efc pfefc
SET pfefc.adjusted_amount=p_adjusted
WHERE pfefc.project_funding_id = rec_adj.project_funding_id;
update_flag := TRUE;
EXIT WHEN (update_flag);
-- update the Summary Project Funding EFC record
UPDATE pa_summary_proj_fundings_efc
SET adjusted_amount = p_adjusted
WHERE project_id = p_project_id
AND NVL(task_id,-99) = NVL(p_task_id,-99)
AND agreement_id = p_agreement_id;
END Update_Adjusted_Amount ;
SELECT sum(nvl(rdl.amount,0))
INTO rdl_amt
FROM pa_mc_cust_rdl_all rdl
WHERE rdl.project_id = p_project_id
AND rdl.draft_revenue_num = p_draft_revenue_num
AND rdl.draft_revenue_item_line_num = p_draft_revenue_item_line_num
AND rdl.set_of_books_id = p_set_of_books_id;
SELECT sum(nvl(erdl.amount,0))
INTO erdl_amt
FROM pa_mc_cust_event_rdl_all erdl
WHERE erdl.project_id = p_project_id
AND erdl.draft_revenue_num = p_draft_revenue_num
AND erdl.draft_revenue_item_line_num = p_draft_revenue_item_line_num
AND erdl.set_of_books_id = p_set_of_books_id;
SELECT sum(nvl(rdl.amount,0))
INTO rdl_amt
FROM pa_mc_cust_rdl_all rdl,
pa_implementations imp
WHERE rdl.project_id = x_project_id
AND rdl.draft_revenue_num = x_draft_revenue_num
AND rdl.set_of_books_id = x_set_of_books_id;
SELECT sum(nvl(erdl.amount,0))
INTO erdl_amt
FROM pa_mc_cust_event_rdl_all erdl,
pa_implementations imp
WHERE erdl.project_id = x_project_id
AND erdl.draft_revenue_num = x_draft_revenue_num
AND erdl.set_of_books_id = x_set_of_books_id ;