[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert_error_message boolean;
SELECT nvl(BTC_COST_BASE_REV_CODE,'EXP_TRANS_CURR')
INTO l_mcb_cost_flag
FROM pa_projects_all
WHERE project_id =(select project_id from pa_expenditure_items_all
where expenditure_item_id=ei_id(1));
insert_error_message := FALSE;
insert_error_message := TRUE;
select BTC_COST_BASE_REV_CODE
into l_mcb_cost_flag
from pa_projects_all
where project_id =(select project_id from pa_expenditure_items_all where expenditure_item_id=ei_id(j));
insert_error_message := TRUE;
select BTC_COST_BASE_REV_CODE
into l_mcb_cost_flag
from pa_projects_all
where project_id =(select project_id from pa_expenditure_items_all where expenditure_item_id=ei_id(j));
IF ( insert_error_message ) THEN
IF (stage = 200) THEN
reason( j ) := 'NO_IND_RATE_SCH_REVISION';
SELECT
t.top_task_id TOP_TASK_ID,
max(dr.draft_revenue_num) DRAFT_REVENUE_NUM
FROM pa_tasks t,pa_draft_revenues_all dr
WHERE p_task_level_funding = 1 /* for task level funding projects only */
AND dr.project_id = p_project_id
AND t.project_id = dr.project_id
AND dr.request_id = p_request_id
AND EXISTS
( SELECT NULL
FROM pa_expenditure_items_all x,
pa_cust_rev_dist_lines_all rdl
WHERE x.request_id+0 = dr.request_id
AND x.task_id = t.task_id
AND x.revenue_distributed_flag||'' = 'A'
AND x.raw_revenue = x.accrued_revenue
AND x.raw_revenue is not NULL
AND x.accrued_revenue is not NULL
AND nvl(rdl.function_code,'*') not in ('LRL','LRB','URL','URB')
AND rdl.line_num_reversed+0 is null
AND nvl(rdl.reversed_flag, 'N' ) = 'N'
AND rdl.expenditure_item_id = x.expenditure_item_id+0
AND rdl.draft_revenue_num = dr.draft_revenue_num
AND rdl.project_id+0 = dr.project_id
AND rdl.request_id+0 = dr.request_id)
GROUP BY t.top_task_id
UNION ALL
SELECT max(to_number(NULL)) TOP_TASK_ID,
max(dr2.draft_revenue_num) DRAFT_REVENUE_NUM
FROM pa_draft_revenues_all dr2
WHERE p_task_level_funding = 0 /* for project level funding only */
AND dr2.project_id = p_project_id
AND dr2.request_id+0 = p_request_id
AND EXISTS
( SELECT NULL
FROM pa_expenditure_items_all ei2,
pa_cust_rev_dist_lines_all rdl2
WHERE ei2.request_id = rdl2.request_id
AND rdl2.expenditure_item_id = ei2.expenditure_item_id
AND ei2.raw_revenue is not NULL
AND ei2.accrued_revenue is not NULL
AND ei2.revenue_distributed_flag||'' = decode(dr2.project_id,NULL,'A','A')
AND ei2.raw_revenue = ei2.accrued_revenue
AND nvl(rdl2.function_code,'*') not in ('LRL','LRB','URL','URB')
AND rdl2.line_num_reversed+0 is null
AND nvl(rdl2.reversed_flag, 'N' ) = 'N'
AND rdl2.draft_revenue_num = dr2.draft_revenue_num
AND rdl2.project_id = dr2.project_id
AND rdl2.request_id+0 = dr2.request_id);
select ei.expenditure_item_id,
rdl.draft_revenue_item_line_num,
rdl.draft_revenue_num,
ei.accrued_revenue ,
ei.raw_revenue
from pa_cust_rev_dist_lines_all rdl,pa_expenditure_items_all ei,
pa_tasks t
where p_top_task_id is not NULL
AND ei.request_id+0 = p_request_id
AND ei.raw_revenue is not NULL
AND ei.accrued_revenue is not NULL
AND ei.revenue_distributed_flag||'' = 'A'
AND ei.expenditure_item_id = rdl.expenditure_item_id
AND ei.raw_revenue <> ei.accrued_revenue
AND rdl.request_id+0 = ei.request_id
AND rdl.project_id = t.project_id
AND nvl(rdl.function_code,'*') not in ('LRL','LRB','URL','URB')
AND rdl.line_num_reversed+0 is null
AND nvl(rdl.reversed_flag, 'N' ) = 'N'
AND t.project_id = p_project_id
AND t.task_id = ei.task_id
AND t.top_task_id = p_top_task_id
AND rdl.draft_revenue_num+0 = p_draft_revenue_num
UNION
select ei.expenditure_item_id,
rdl.draft_revenue_item_line_num,
rdl.draft_revenue_num,
ei.accrued_revenue ,
ei.raw_revenue
from pa_cust_rev_dist_lines_all rdl,pa_expenditure_items_all ei
where p_top_task_id is NULL
AND ei.request_id+0 = p_request_id
AND ei.raw_revenue is not NULL
AND ei.accrued_revenue is not NULL
AND ei.revenue_distributed_flag||'' = 'A'||''
AND ei.expenditure_item_id = rdl.expenditure_item_id
AND ei.raw_revenue <> ei.accrued_revenue
AND rdl.request_id+0 = ei.request_id
AND rdl.project_id = p_project_id
AND nvl(rdl.function_code,'*') not in ('LRL','LRB','URL','URB')
AND rdl.line_num_reversed+0 is null
AND nvl(rdl.reversed_flag, 'N' ) = 'N'
AND rdl.draft_revenue_num = p_draft_revenue_num;
select 'X'
into dummy_x
from pa_cust_rev_dist_lines_all rdl,pa_expenditure_items_all ei,
pa_tasks t
where ei.request_id+0 = p_request_id
AND ei.raw_revenue is not NULL
AND ei.accrued_revenue is not NULL
AND ei.revenue_distributed_flag||'' = 'A'
AND ei.expenditure_item_id = rdl.expenditure_item_id
AND ei.raw_revenue <> ei.accrued_revenue
AND rdl.request_id+0 = ei.request_id
AND rdl.project_id = t.project_id
AND nvl(rdl.function_code,'*') not in ('LRL','LRB','URL','URB')
AND rdl.line_num_reversed+0 is null
AND nvl(rdl.reversed_flag, 'N' ) = 'N'
AND t.project_id = rdl.project_id
AND rdl.project_id = p_project_id
AND t.task_id = ei.task_id
AND t.top_task_id = top_task_cur_rec.TOP_TASK_ID
AND rdl.draft_revenue_num = top_task_cur_rec.DRAFT_REVENUE_NUM
having sum(ei.accrued_revenue) = sum(ei.raw_revenue);
select 'X'
into dummy_x
from pa_cust_rev_dist_lines_all rdl,pa_expenditure_items_all ei
where ei.request_id+0 = p_request_id
AND ei.raw_revenue is not NULL
AND ei.accrued_revenue is not NULL
AND ei.revenue_distributed_flag||'' = 'A'||''
AND ei.expenditure_item_id = rdl.expenditure_item_id
AND ei.raw_revenue <> ei.accrued_revenue
AND rdl.request_id+0 = ei.request_id
AND rdl.project_id = p_project_id
AND nvl(rdl.function_code,'*') not in ('LRL','LRB','URL','URB')
AND rdl.line_num_reversed+0 is null
AND nvl(rdl.reversed_flag, 'N' ) = 'N'
AND rdl.draft_revenue_num = top_task_cur_rec.DRAFT_REVENUE_NUM
having sum(ei.accrued_revenue) = sum(ei.raw_revenue);
l_message_code := 'Error in update on pa_cut_rev_dist_lines_all';
UPDATE pa_cust_rev_dist_lines_all l
SET l.amount = PA_CURRENCY.ROUND_CURRENCY_AMT(l.amount -
DECODE(code_combination_id, -1, roundoff_amount,
-2, roundoff_amount,
-roundoff_amount)),
l.projfunc_revenue_amount = -- Below lines added for Bug 5042421
DECODE(l.revproc_currency_code, l.projfunc_currency_code,
PA_CURRENCY.ROUND_CURRENCY_AMT(l.amount -
DECODE(code_combination_id, -1, roundoff_amount,
-2, roundoff_amount,
-roundoff_amount)),
projfunc_revenue_amount),
l.project_revenue_amount =
DECODE(l.revproc_currency_code, l.project_currency_code,
PA_CURRENCY.ROUND_CURRENCY_AMT(l.amount -
DECODE(code_combination_id, -1, roundoff_amount,
-2, roundoff_amount,
-roundoff_amount)),
project_revenue_amount),
l.funding_revenue_amount =
DECODE(l.revproc_currency_code, l.funding_currency_code,
PA_CURRENCY.ROUND_CURRENCY_AMT(l.amount -
DECODE(code_combination_id, -1, roundoff_amount,
-2, roundoff_amount,
-roundoff_amount)),
funding_revenue_amount),
l.revtrans_amount =
DECODE(l.revproc_currency_code, l.revtrans_currency_code,
PA_CURRENCY.ROUND_CURRENCY_AMT(l.amount -
DECODE(code_combination_id, -1, roundoff_amount,
-2, roundoff_amount,
-roundoff_amount)),
revtrans_amount) -- End of Bug 5042421
WHERE l.expenditure_item_id =
exp_cur_rec.EXPENDITURE_ITEM_ID
AND l.draft_revenue_num =
exp_cur_rec.DRAFT_REVENUE_NUM
AND l.draft_revenue_item_line_num =
exp_cur_rec.DRAFT_REVENUE_ITEM_LINE_NUM;
l_message_code := 'Error in update on pa_draft_revenue_items';
UPDATE pa_draft_revenue_items i
SET i.amount = i.amount - roundoff_amount,
i.projfunc_revenue_amount = -- Below lines added for Bug 5042421
DECODE(i.revproc_currency_code, i.projfunc_currency_code,
i.amount - roundoff_amount,
i.projfunc_revenue_amount),
i.project_revenue_amount =
DECODE(i.revproc_currency_code, i.project_currency_code,
i.amount - roundoff_amount,
i.project_revenue_amount),
i.funding_revenue_amount =
DECODE(i.revproc_currency_code, i.funding_currency_code,
i.amount - roundoff_amount,
i.funding_revenue_amount),
i.revtrans_amount =
DECODE(i.revproc_currency_code, i.revtrans_currency_code,
i.amount - roundoff_amount,
i.revtrans_amount) -- End of Bug 5042421
WHERE i.project_id = p_project_id
AND i.draft_revenue_num =
exp_cur_rec.DRAFT_REVENUE_NUM
AND i.line_num =
exp_cur_rec.DRAFT_REVENUE_ITEM_LINE_NUM;
l_message_code := 'Error in update on pa_expenditure_items_all';
UPDATE pa_expenditure_items_all x
SET x.accrued_revenue
= x.accrued_revenue - roundoff_amount
WHERE x.expenditure_item_id =
exp_cur_rec.EXPENDITURE_ITEM_ID;
SELECT 'x'
INTO l_dummy
FROM gl_code_combinations
WHERE code_combination_id = P_Rec_ccid;
SELECT 'x'
INTO l_dummy
FROM gl_code_combinations
WHERE code_combination_id = P_Rev_ccid;
SELECT 'x'
INTO l_dummy
FROM gl_code_combinations
WHERE code_combination_id = P_rg_ccid;
SELECT 'x'
INTO l_dummy
FROM gl_code_combinations
WHERE code_combination_id = P_rl_ccid;