[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1
from pa_projects_all
where project_id = x_project_id
and substr(distribution_rule, 1, 5) = 'COST/';
select 1
from sys.dual
where not exists
(select 1
from pa_agreements_all a,
pa_summary_project_fundings f
where f.project_id = x_project_id
and nvl(f.task_id, 0) = x_task_id
and ( ( nvl(f.total_baselined_amount, 0)
+ f.total_unbaselined_amount
)
< greatest(nvl(f.total_accrued_amount, 0),
nvl(f.total_billed_amount, 0))
)
and f.agreement_id = a.agreement_id
and a.revenue_limit_flag = 'Y');
select 1
from sys.dual
where not exists
(select 1
from pa_agreements_all a,
pa_summary_project_fundings f
where f.project_id = x_project_id
and nvl(f.task_id, 0) = x_task_id
and ( ( nvl(f.projfunc_baselined_amount, 0)
+ f.projfunc_unbaselined_amount
)
< nvl(f.projfunc_accrued_amount, 0)
)
and f.agreement_id = a.agreement_id
and a.revenue_limit_flag = 'Y')
and not exists
(select 1
from pa_agreements_all a,
pa_summary_project_fundings f
where f.project_id = x_project_id
and nvl(f.task_id, 0) = x_task_id
and ( ( nvl(f.invproc_baselined_amount, 0)
+ f.invproc_unbaselined_amount
)
< nvl(f.invproc_billed_amount, 0)
)
and f.agreement_id = a.agreement_id
and a.invoice_limit_flag = 'Y');
select 1
from pa_events
where project_id = x_project_id
and task_id is null;
SELECT t.top_task_id , sum(l.revenue) revenue
FROM pa_budget_lines l,
pa_resource_assignments a,
pa_tasks t,
pa_budget_versions v
WHERE v.project_id = x_project_id
AND (v.budget_type_code = 'AR'
OR v.approved_rev_plan_type_flag ='Y')
AND v.budget_status_code IN ('S','W') /* Fix for Bug # 1206240*/
AND decode(v.budget_type_code,null,v.current_working_flag,'Y')='Y' /* Added for bug 2834104 */
and a.budget_version_id = v.budget_version_id
and a.project_id = v.project_id
and t.project_id = v.project_id
and t.task_id = a.task_id
and a.task_id is not null
AND l.resource_assignment_id = a.resource_assignment_id
group by t.top_task_id
having nvl(sum(l.revenue),0) <> 0 /* Fix for Bug 4735399 */
order by t.top_task_id;
select task_id, sum(nvl(allocated_amount,0)) funding_total
*/
-- Following cursor is modified for bux fix 3763133
cursor funding_task is
select task_id, sum(nvl(projfunc_allocated_amount,0)) funding_total
from pa_project_fundings
where project_id = x_project_id
AND ( (budget_type_code IN ('DRAFT', 'BASELINE') AND PA_FUND_REVAL_PVT.G_REVAL_FLAG ='N'
AND PA_Funding_Core.G_Fund_Baseline_Flag = 'N')
OR (PA_FUND_REVAL_PVT.G_REVAL_FLAG ='Y' AND (
( (budget_type_code ='BASELINE') OR
(budget_type_code ='DRAFT' AND funding_category=
'REVALUATION') )))
OR (PA_Funding_Core.G_Fund_Baseline_Flag = 'Y' AND
( Budget_Type_Code = 'BASELINE' ) OR -- Modified for bug 4057927
( Budget_Type_Code = 'DRAFT' AND NVL(Submit_Baseline_Flag,'N') = 'Y') )
)
and task_id is not null
group by task_id
having nvl(sum(nvl(projfunc_allocated_amount,0)),0) <> 0 /* Fix for Bug 4710749 */
order by task_id;
select 1 from dual where not exists
(
select PPC.customer_id from pa_project_customers PPC
where PPC.project_id= x_project_id
and PPC.customer_bill_split > 0 /* Added for Bug2453912 */
and not exists
(
select 1 from pa_summary_project_fundings PSPF,
pa_agreements_all PAA
where PPC.customer_id = PAA.customer_id
and PAA.agreement_id = PSPF.agreement_id
and PPC.project_id= PSPF.project_id
)
);
select sum(nvl(allocated_amount,0))
*/
-- Following select is modified for bux fix 3763133
select sum(nvl(projfunc_allocated_amount,0))
into funding_total
from pa_project_fundings
where project_id = x_project_id
AND ( (budget_type_code IN ('DRAFT', 'BASELINE') AND PA_FUND_REVAL_PVT.G_REVAL_FLAG ='N'
AND PA_Funding_Core.G_Fund_Baseline_Flag = 'N')
OR (PA_FUND_REVAL_PVT.G_REVAL_FLAG ='Y' AND (
( (budget_type_code ='BASELINE') OR
(budget_type_code ='DRAFT' AND funding_category=
'REVALUATION') )))
OR (PA_Funding_Core.G_Fund_Baseline_Flag = 'Y' AND
( Budget_Type_Code = 'BASELINE') OR -- Modified for bug 4057927
( Budget_Type_Code = 'DRAFT' AND NVL(Submit_Baseline_Flag,'N') = 'Y') )
);
select fin_plan_type_id,
version_type
into l_fin_plan_type_id,l_version_type
from pa_budget_versions
where budget_version_id = x_draft_version_id;
for fund_rec in (select task_id,
sum(nvl(allocated_amount,0)) funding_total
from pa_project_fundings
where project_id = x_project_id
and budget_type_code in ('BASELINE', 'DRAFT')
group by task_id) loop
x_revenue :=0;
select fin_plan_type_id,
version_type
into l_fin_plan_type_id,
l_version_type
from pa_budget_versions
where budget_version_id = x_draft_version_id;
-- Comment out call to update_funding to use as verify only.
-- (ckh 09/04/97)
--
-- change DRAFT funding to BASELINE
-- update_funding( x_project_id,
-- x_funding_level,
-- x_err_code,
-- x_err_stage,
-- x_err_stack);
procedure update_funding(
x_project_id in number,
x_funding_level in out NOCOPY varchar2, --File.Sql.39 bug 4440895
x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
is
-- Standard who
x_created_by number(15);
x_last_update_login number(15);
x_err_stack := 'pa_billing_core->update_funding';
x_last_update_login := FND_GLOBAL.LOGIN_ID;
-- update the all currency columns
-- elseif action is from revaluation process
-- baseline only the revaluation funding line amount
-- update only the revaluation funding line amount
-- update only the projfunc, revproc and invproc columns
-- End if
--------------------------------------------------------*/
IF PA_FUND_REVAL_PVT.G_REVAL_FLAG='N' THEN
x_err_stage := 'Not Revaluation: change draft to baseline <' || to_char(x_project_id)
|| '>';
update pa_project_fundings
set /* PJI_SUMMARIZED_FLAG = 'N' -- For Bug 2244796 and bug 2440676 */
PJI_SUMMARIZED_FLAG = decode(budget_type_code, 'BASELINE', pji_summarized_flag, 'N'), -- For Bug 3821126
budget_type_code = 'BASELINE',
last_update_date = SYSDATE,
last_updated_by = x_created_by,
last_update_login = x_last_update_login
where project_id = x_project_id
and budget_type_code IN('DRAFT','BASELINE');
-- update summary funding
x_err_stage := 'update summary funding <' || to_char(x_project_id)
|| '>';
update pa_summary_project_fundings
set total_baselined_amount = total_unbaselined_amount +
nvl(total_baselined_amount, 0),
total_unbaselined_amount = 0,
/* MCB2 code begins */
project_baselined_amount = project_unbaselined_amount +
nvl(project_baselined_amount, 0),
project_unbaselined_amount = 0,
projfunc_baselined_amount = projfunc_unbaselined_amount +
nvl(projfunc_baselined_amount, 0),
projfunc_unbaselined_amount = 0,
invproc_baselined_amount = invproc_unbaselined_amount +
nvl(invproc_baselined_amount, 0),
invproc_unbaselined_amount = 0,
revproc_baselined_amount = revproc_unbaselined_amount +
nvl(revproc_baselined_amount, 0),
revproc_unbaselined_amount = 0,
/* MCB2 code ends */
last_update_date = SYSDATE,
last_updated_by = x_created_by,
last_update_login = x_last_update_login
where project_id = x_project_id;
-- update summary funding
x_err_stage := 'update summary funding <' || to_char(x_project_id)
|| '>';
FOR reval_rec IN (SELECT project_funding_id, agreement_id, project_id,task_id, projfunc_allocated_amount,
invproc_allocated_amount,revproc_allocated_amount
FROM pa_project_fundings
WHERE project_id = x_project_id
AND budget_type_code ='DRAFT'
AND funding_category ='REVALUATION') LOOP
/* Bug 2670854 Since pa_mc_sum_proj_fundings updates its baselined, unbaselined amount
based on the budget_type_code, before updating summary project fundings in primary
this is being done as trigger on summary project fundings will update mc summary project
fundings based on this col value */
UPDATE pa_project_fundings
set /* PJI_SUMMARIZED_FLAG = 'N' -- For Bug 2244796 and bug 2440676 */
PJI_SUMMARIZED_FLAG = decode(budget_type_code, 'BASELINE', pji_summarized_flag, 'N'), -- For Bug 3821126
budget_type_code = 'BASELINE',
last_update_date = SYSDATE,
last_updated_by = x_created_by,
last_update_login = x_last_update_login
WHERE project_funding_id = reval_rec.project_funding_id;
UPDATE pa_summary_project_fundings
SET projfunc_baselined_amount = projfunc_baselined_amount +
NVL(reval_rec.projfunc_allocated_amount, 0),
projfunc_unbaselined_amount = projfunc_unbaselined_amount -
NVL(reval_rec.projfunc_allocated_amount,0),
invproc_baselined_amount = invproc_baselined_amount +
NVL(reval_rec.invproc_allocated_amount, 0),
invproc_unbaselined_amount = invproc_unbaselined_amount -
NVL(reval_rec.invproc_allocated_amount,0),
revproc_baselined_amount = revproc_baselined_amount +
NVL(reval_rec.revproc_allocated_amount, 0),
revproc_unbaselined_amount = revproc_unbaselined_amount -
NVL(reval_rec.revproc_allocated_amount,0),
last_update_date = SYSDATE,
last_updated_by = x_created_by,
last_update_login = x_last_update_login
WHERE project_id = x_project_id
AND agreement_id = reval_rec.agreement_id
AND nvl(task_id,0) = nvl(reval_rec.task_id,0);
UPDATE pa_project_fundings
SET budget_type_code = 'BASELINE',
last_update_date = SYSDATE,
last_updated_by = x_created_by,
last_update_login = x_last_update_login,
pji_summarized_flag = 'N' -- For Bug 2244796 and bug 2440676
WHERE project_id = x_project_id
AND budget_type_code = 'DRAFT'
AND funding_category='REVALUATION';
-- update summary funding
x_err_stage := 'update summary funding <' || to_char(x_project_id) || '>';
FOR Agreement_rec IN ( SELECT project_funding_id, agreement_id,
project_id,task_id, projfunc_allocated_amount,
invproc_allocated_amount, revproc_allocated_amount
FROM pa_project_fundings
WHERE project_id = x_project_id
AND budget_type_code ='DRAFT'
AND NVL(Submit_Baseline_Flag,'N') = 'Y' )
LOOP
-- Update the Project fundings for the baselined lines
UPDATE pa_project_fundings
SET budget_type_code = 'BASELINE',
last_update_date = SYSDATE,
last_updated_by = x_created_by,
last_update_login = x_last_update_login,
pji_summarized_flag = 'N', -- For Bug 2244796 and bug 2440676
Submit_Baseline_Flag = 'N'
WHERE project_funding_id = Agreement_rec.project_funding_id;
-- Update the Project summary fundings of PFC, Invoice and revenue
-- baselined and unbaselined amounts
UPDATE pa_summary_project_fundings
SET projfunc_baselined_amount = projfunc_baselined_amount +
NVL(Agreement_rec.projfunc_allocated_amount, 0),
projfunc_unbaselined_amount = projfunc_unbaselined_amount -
NVL(Agreement_rec.projfunc_allocated_amount,0),
invproc_baselined_amount = invproc_baselined_amount +
NVL(Agreement_rec.invproc_allocated_amount, 0),
invproc_unbaselined_amount = invproc_unbaselined_amount -
NVL(Agreement_rec.invproc_allocated_amount,0),
revproc_baselined_amount = revproc_baselined_amount +
NVL(Agreement_rec.revproc_allocated_amount, 0),
revproc_unbaselined_amount = revproc_unbaselined_amount -
NVL(Agreement_rec.revproc_allocated_amount,0),
last_update_date = SYSDATE,
last_updated_by = x_created_by,
last_update_login = x_last_update_login
WHERE project_id = x_project_id
AND agreement_id = Agreement_rec.agreement_id
AND nvl(task_id,0) = nvl(Agreement_rec.task_id,0);
UPDATE pa_events evt
SET evt.revenue_hold_flag ='N'
WHERE evt.project_id = x_project_id
AND evt.project_funding_id IS NOT NULL;
update pa_projects_all
set project_level_funding_flag = decode(x_funding_level,'P','Y','N'),
last_update_date = SYSDATE,
last_updated_by = x_created_by,
last_update_login = x_last_update_login
where project_id = x_project_id;
end update_funding;
select 1
from pa_summary_project_fundings
where project_id = x_project_id
and task_id is null
and nvl(total_baselined_amount, 0) + total_unbaselined_amount > 0;
select 1
from pa_summary_project_fundings
where project_id = x_project_id
and task_id is not null
and nvl(total_baselined_amount, 0) + total_unbaselined_amount > 0;
select 1 from pa_project_fundings
where project_id = x_project_id
and task_id is null
having sum(allocated_amount) = 0;
select 1 from pa_project_fundings
where project_id = x_project_id
and task_id is not null
having sum(allocated_amount) = 0;
select 1 from pa_summary_project_fundings
where project_id = x_project_id
and task_id is null
and total_unbaselined_amount = nvl(total_baselined_amount,0)*(-1)
and total_unbaselined_amount <> 0;
select 1 from pa_summary_project_fundings
where project_id = x_project_id
and task_id is not null
and total_unbaselined_amount = nvl(total_baselined_amount,0)*(-1)
and total_unbaselined_amount <> 0;
Select 'T' INTO x_funding_level
FROM PA_Projects_All
Where Project_ID = X_Project_ID
AND (ENABLE_TOP_TASK_CUSTOMER_FLAG = 'Y' OR
ENABLE_TOP_TASK_INV_MTH_FLAG = 'Y' );
select 'Y' into l_fund_exists
from pa_summary_project_fundings
where project_id = x_orig_project_id
and nvl(total_baselined_amount, 0) + total_unbaselined_amount > 0;
SELECT count(*)
INTO l_funding_count
FROM pa_project_fundings
WHERE project_id = x_orig_project_id;
select pa_agreements_s.nextval
into x_agreement_id
from dual;
x_err_stage := 'Insert into pa_agreements.';
INSERT INTO PA_AGREEMENTS_ALL(
agreement_id,
customer_id,
agreement_num,
agreement_type,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
owned_by_person_id,
term_id,
revenue_limit_flag,
amount,
description,
expiration_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
template_flag,
agreement_currency_code, /* MCB2 column begins */
owning_organization_id,
invoice_limit_flag, /* MCB2 column ends */
org_id)
SELECT x_agreement_id,
nvl(x_customer_id, a.customer_id),
p.segment1,
a.agreement_type,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
a.owned_by_person_id,
a.term_id,
a.revenue_limit_flag,
nvl(l_amount, a.amount), /* MCB2 change */
a.description,
decode(a.expiration_date, null, null,
a.expiration_date + nvl(x_delta, 0)),
a.attribute_category,
a.attribute1,
a.attribute2,
a.attribute3,
a.attribute4,
a.attribute5,
a.attribute6,
a.attribute7,
a.attribute8,
a.attribute9,
a.attribute10,
x_template_flag,
/* MCB2 columns begin */
nvl(x_agreement_currency_code,a.agreement_currency_code),
nvl(x_owning_organization_id,a.owning_organization_id),
a.invoice_limit_flag,
mo_global.get_current_org_id
/* MCB2 columns end */
FROM pa_agreements_all a, pa_projects_all p
WHERE p.project_id = x_new_project_id
/* Bug 727421 Performance Issue
AND exists
(select null
from pa_project_customers c2, pa_project_fundings f
where f.project_id = x_orig_project_id
and f.agreement_id = a.agreement_id
and c2.project_id = x_orig_project_id
and c2.customer_bill_split = 100
and c2.customer_id = a.customer_id);
(SELECT F.agreement_id from pa_summary_project_fundings F
WHERE F.project_id = x_orig_project_id)
AND exists
(select null
from pa_project_customers c2
where c2.project_id = x_orig_project_id
-- and nvl(c2.customer_bill_split,0) = 100
-- FP_M changes
-- If the project is implemented with Top Task Customer then
-- assume the bill split is 100%
and nvl(c2.customer_bill_split,0) =
Decode(c2.Default_Top_Task_Cust_Flag, 'Y', 0, 100)
and c2.customer_id = A.customer_id);
select 1
from pa_project_fundings
where project_id = x_orig_project_id
and task_id is null;*/
SELECT pa_project_fundings_s.nextval project_funding_id ,
sysdate last_update_date, fnd_global.user_id last_updated_by,
sysdate creation_date, fnd_global.user_id created_by,
fnd_global.login_id last_update_login,
x_agreement_id agreement_id, x_new_project_id project_id,
NULL task_id, 'DRAFT' budget_type_code,
/* l_allocated_amount allocated_amount, Commented code for bug 2793120 */
NVL(r_amount,f.allocated_amount) allocated_amount, /* Added for bug 2793120 */ /* Modified for bug 2913524 */
f.date_allocated + nvl(x_delta, 0) date_allocated,
f.attribute_category attribute_category,
f.attribute1 attribute1,
f.attribute2 attribute2,
f.attribute3 attribute3,
f.attribute4 attribute4,
f.attribute5 attribute5,
f.attribute6 attribute6,
f.attribute7 attribute7,
f.attribute8 attribute8,
f.attribute9 attribute9,
f.attribute10 attribute10,
l_funding_currency_code funding_currency_code,
f.funding_category /* For Bug 2244796 */
FROM pa_project_fundings f
WHERE f.project_id = x_orig_project_id
AND f.task_id is null ;/*Added for bug 5140179*/
select null
from pa_agreements
where agreement_id = x_agreement_id);
SELECT pa_project_fundings_s.nextval project_funding_id ,
sysdate last_update_date, fnd_global.user_id last_updated_by,
sysdate creation_date, fnd_global.user_id created_by,
fnd_global.login_id last_update_login,
x_agreement_id agreement_id, x_new_project_id project_id,
t2.task_id task_id, 'DRAFT' budget_type_code,
f.allocated_amount allocated_amount,
f.date_allocated + nvl(x_delta, 0) date_allocated,
f.attribute_category attribute_category,
f.attribute1 attribute1,
f.attribute2 attribute2,
f.attribute3 attribute3,
f.attribute4 attribute4,
f.attribute5 attribute5,
f.attribute6 attribute6,
f.attribute7 attribute7,
f.attribute8 attribute8,
f.attribute9 attribute9,
f.attribute10 attribute10,
l_funding_currency_code funding_currency_code,
f.funding_category /* For Bug 2244796 */
FROM pa_tasks t2, pa_tasks t, pa_project_fundings f
WHERE f.project_id = x_orig_project_id
AND t.project_id = f.project_id
AND t.task_id = f.task_id
AND t2.task_number = t.task_number
AND t2.project_id = x_new_project_id;
select null
from pa_agreements
where agreement_id = x_agreement_id);
select agreement_currency_code, amount
into l_funding_currency_code, l_allocated_amount
from pa_agreements_all
where agreement_id = x_agreement_id;
x_err_stage := 'Insert into pa_project_fundings';
INSERT INTO pa_project_fundings(
project_funding_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
agreement_id,
project_id,
task_id,
budget_type_code,
allocated_amount,
date_allocated,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
funding_currency_code,
project_currency_code,
project_rate_type,
project_rate_date,
project_exchange_rate,
project_allocated_amount,
projfunc_currency_code,
projfunc_rate_type,
projfunc_rate_date,
projfunc_exchange_rate,
projfunc_allocated_amount,
invproc_currency_code,
invproc_rate_type,
invproc_rate_date,
invproc_exchange_rate,
invproc_allocated_amount,
revproc_currency_code,
revproc_rate_type,
revproc_rate_date,
revproc_exchange_rate,
revproc_allocated_amount,
funding_category /* For Bug2244796 */
)
VALUES
( proj_rec.project_funding_id,
proj_rec.last_update_date,
proj_rec.last_updated_by,
proj_rec.creation_date,
proj_rec.created_by,
proj_rec.last_update_login,
proj_rec.agreement_id,
proj_rec.project_id,
proj_rec.task_id,
proj_rec.budget_type_code,
proj_rec.allocated_amount,
proj_rec.date_allocated,
proj_rec.attribute_category,
proj_rec.attribute1,
proj_rec.attribute2,
proj_rec.attribute3,
proj_rec.attribute4,
proj_rec.attribute5,
proj_rec.attribute6,
proj_rec.attribute7,
proj_rec.attribute8,
proj_rec.attribute9,
proj_rec.attribute10,
proj_rec.funding_currency_code,
l_project_currency_code,
l_project_rate_type,
l_project_rate_date,
l_project_exchange_rate,
l_project_allocated_amount,
l_projfunc_currency_code,
l_projfunc_rate_type,
l_projfunc_rate_date,
l_projfunc_exchange_rate,
l_projfunc_allocated_amount,
l_invproc_currency_code,
l_invproc_rate_type,
l_invproc_rate_date,
l_invproc_exchange_rate,
l_invproc_allocated_amount,
l_revproc_currency_code,
l_revproc_rate_type,
l_revproc_rate_date,
l_revproc_exchange_rate,
l_revproc_allocated_amount,
proj_rec.funding_category /* For Bug 2244796 */
);
x_err_stage := 'Insert into pa_project_fundings';
INSERT INTO pa_project_fundings(
project_funding_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
agreement_id,
project_id,
task_id,
budget_type_code,
allocated_amount,
date_allocated,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
funding_currency_code,
project_currency_code,
project_rate_type,
project_rate_date,
project_exchange_rate,
project_allocated_amount,
projfunc_currency_code,
projfunc_rate_type,
projfunc_rate_date,
projfunc_exchange_rate,
projfunc_allocated_amount,
invproc_currency_code,
invproc_rate_type,
invproc_rate_date,
invproc_exchange_rate,
invproc_allocated_amount,
revproc_currency_code,
revproc_rate_type,
revproc_rate_date,
revproc_exchange_rate,
revproc_allocated_amount,
funding_category /* For Bug2244796 */
)
VALUES
( task_rec.project_funding_id,
task_rec.last_update_date,
task_rec.last_updated_by,
task_rec.creation_date,
task_rec.created_by,
task_rec.last_update_login,
task_rec.agreement_id,
task_rec.project_id,
task_rec.task_id,
task_rec.budget_type_code,
task_rec.allocated_amount,
task_rec.date_allocated,
task_rec.attribute_category,
task_rec.attribute1,
task_rec.attribute2,
task_rec.attribute3,
task_rec.attribute4,
task_rec.attribute5,
task_rec.attribute6,
task_rec.attribute7,
task_rec.attribute8,
task_rec.attribute9,
task_rec.attribute10,
task_rec.funding_currency_code,
l_project_currency_code,
l_project_rate_type,
l_project_rate_date,
l_project_exchange_rate,
l_project_allocated_amount,
l_projfunc_currency_code,
l_projfunc_rate_type,
l_projfunc_rate_date,
l_projfunc_exchange_rate,
l_projfunc_allocated_amount,
l_invproc_currency_code,
l_invproc_rate_type,
l_invproc_rate_date,
l_invproc_exchange_rate,
l_invproc_allocated_amount,
l_revproc_currency_code,
l_revproc_rate_type,
l_revproc_rate_date,
l_revproc_exchange_rate,
l_revproc_allocated_amount,
task_rec.funding_category /* For Bug 2244796 */
);
x_err_stage := 'Insert or update pa_summary_project_fundings';
INSERT INTO pa_summary_project_fundings(
agreement_id,
project_id,
task_id,
total_baselined_amount,
total_unbaselined_amount,
total_accrued_amount,
total_billed_amount,
last_update_login,
last_update_date,
last_updated_by,
creation_date,
created_by,
funding_currency_code,
project_currency_code, project_baselined_amount,
project_unbaselined_amount, project_accrued_amount,
project_billed_amount,
projfunc_currency_code, projfunc_baselined_amount,
projfunc_unbaselined_amount, projfunc_accrued_amount,
projfunc_billed_amount,
invproc_currency_code, invproc_baselined_amount,
invproc_unbaselined_amount,
invproc_billed_amount,
revproc_currency_code, revproc_baselined_amount,
revproc_unbaselined_amount, revproc_accrued_amount)
SELECT agreement_id,
project_id,
task_id,
0,
nvl(sum(nvl(allocated_amount, 0)), 0),
0, 0,
fnd_global.login_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
funding_currency_code,
project_currency_code,
0,
nvl(sum(nvl(project_allocated_amount, 0)), 0),
0, 0,
projfunc_currency_code,
0,
nvl(sum(nvl(projfunc_allocated_amount, 0)), 0),
0, 0,
invproc_currency_code,
0,
nvl(sum(nvl(invproc_allocated_amount, 0)), 0),
0,
revproc_currency_code,
0,
nvl(sum(nvl(revproc_allocated_amount, 0)), 0),
0
FROM pa_project_fundings
WHERE project_id = x_new_project_id
GROUP BY agreement_id, project_id, task_id, funding_currency_code,
project_currency_code, projfunc_currency_code,
invproc_currency_code, revproc_currency_code ;
select 'Y' into l_fund_exists
from dual
where exists(select null
from pa_summary_project_fundings spf
where spf.project_id = x_project_id);
Function Update_Top_Task_Cust_Flag (
P_Project_ID IN Number
) Return Varchar2 IS
l_Exist_Flag VARCHAR2(1);
Select Project_Level_Funding_Flag
INTO l_Funding_Level_Flag
FROM PA_Projects_All
Where Project_ID = P_Project_ID;
Select 'N'
Into l_Exist_Flag
from dual
where exists ( select null
From PA_Project_Fundings
Where Project_ID = P_Project_ID
and budget_type_code = 'DRAFT');
Select 'N'
Into l_Exist_Flag
From PA_summary_Project_Fundings
Where Project_ID = P_Project_ID
HAVING ( sum(nvl(Total_Baselined_Amount,0)) > 0 OR sum(nvl(Total_Unbaselined_Amount,0)) > 0 );
END Update_Top_Task_Cust_Flag;
Function Update_Top_Task_Inv_Mthd_Flag (
P_Project_ID IN Number
) Return Varchar2
IS
l_Exist_Flag VARCHAR2(1);
Select Project_Level_Funding_Flag
INTO l_Funding_Level_Flag
FROM PA_Projects_All
Where Project_ID = P_Project_ID;
Select 'N'
Into l_Exist_Flag
from dual
where exists ( select null
From PA_Project_Fundings
Where Project_ID = P_Project_ID
and Task_ID IS NULL
and budget_type_code = 'DRAFT');
Select 'N'
Into l_Exist_Flag
From PA_Summary_Project_Fundings
Where Project_ID = P_Project_ID
and Task_ID IS NULL
HAVING ( sum(Total_Baselined_Amount) > 0 OR sum(Total_Unbaselined_Amount) > 0 );
Select 'N'
Into l_Exist_Flag
From PA_Summary_Project_Fundings
Where Project_ID = P_Project_ID
and Task_ID IS NOT NULL
HAVING sum(Total_Billed_Amount) > 0;
END Update_Top_Task_Inv_Mthd_Flag;
/* Select 'N'
Into l_Exist_Flag
From PA_Summary_Project_Fundings
Where Project_ID = P_Project_ID
HAVING ( sum(Total_Billed_Amount) > 0 OR
sum(Total_Accrued_Amount) > 0 ); --Added for Bug3729634 */
Select 'N'
Into l_Exist_Flag
From dual
Where exists
( select 1 from pa_draft_revenue_items
where project_id = P_Project_ID
group by nvl(task_id,-99)
having sum(Amount) <> 0
Union all
select 1 from pa_draft_invoice_items
where project_id = P_Project_ID
group by nvl(task_id,-99)
having sum(Amount) <> 0 );
Function Update_Top_Task_Customer (
P_Project_ID IN Number,
P_Task_ID IN Number
) Return Varchar2
IS
l_Exist_Flag varchar2(1);
Select 'N'
Into l_Exist_Flag
from dual
where exists ( select null
From PA_Project_Fundings
Where Project_ID = P_Project_ID
AND Task_ID = P_Task_ID
and budget_type_code = 'DRAFT');
Select 'N'
Into l_Exist_Flag
From PA_Summary_Project_Fundings
Where Project_ID = P_Project_ID
AND Task_ID = P_Task_ID
HAVING (sum(Total_Baselined_Amount) <> 0
OR sum(Total_UnBaselined_Amount) <> 0
OR sum(Total_Accrued_Amount)<>0 /* added for bug 7291160 */
OR sum(Total_Billed_Amount)<>0 /* added for bug 7291160 */
)
;
select 'N'
into l_Exist_Flag
from dual
where exists (select 1
from pa_expenditure_items_all ei, pa_tasks pt
where ei.task_id = pt.task_id
and pt.top_task_id = P_Task_ID
and pt.project_id = P_Project_ID
and (NVL(accrued_revenue,0) <> 0 or NVL(bill_amount,0) <> 0)
and ei.net_zero_adjustment_flag = 'N');
select 'N'
into l_Exist_Flag
from dual where exists (select 1
from pa_expenditure_items_all ei, pa_tasks pt
where ei.task_id = pt.task_id
and pt.top_task_id = P_Task_ID
and pt.project_id = P_Project_ID
and (( NVL(ei.accrued_revenue,0) + NVL((select ei1.accrued_revenue
from pa_expenditure_items_all ei1 where ei1.expenditure_item_id = ei.adjusted_expenditure_item_id),0) <> 0)
or
( NVL(ei.bill_amount,0) + NVL((select ei2.bill_amount
from pa_expenditure_items_all ei2 where ei2.expenditure_item_id = ei.adjusted_expenditure_item_id),0) <> 0))
and ei.adjusted_expenditure_item_id IS NOT NULL
and ei.net_zero_adjustment_flag = 'Y');
END Update_Top_Task_Customer;
Function Update_Top_Task_Invoice_Method (
P_Project_ID IN Number,
P_Task_ID IN Number
) Return Varchar2
IS
l_Exist_Flag varchar2(1);
Select 'N'
Into l_Exist_Flag
From PA_Summary_Project_Fundings
Where Project_ID = P_Project_ID
AND Task_ID = P_Task_ID
HAVING sum(Total_Billed_Amount) > 0;
END Update_Top_Task_Invoice_Method;