[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(SUM(allocated_amount),0)
INTO l_amount
FROM pa_project_fundings
WHERE project_id = p_project_id
AND NVL(task_id,-99) = NVL(p_task_id ,-99)
AND RTRIM(budget_type_code) = RTRIM(p_budget_type);
Select sum(total_unbaselined_amount) , sum(total_baselined_amount)
Into l_total_unbaselined_amount, l_total_baselined_amount
From Pa_summary_project_fundings
Where agreement_id = p_agreement_id;
select 1
into dummy
from dual
where exists ( select 1
from pa_project_fundings
where agreement_id = p_agreement_id);
Select sign(p_amount +
greatest(nvl(total_accrued_amount, 0),
nvl(total_billed_amount, 0),
nvl(total_baselined_amount, 0) +
nvl(total_unbaselined_amount, 0)))
From pa_summary_project_fundings
Where project_id = p_project_id
And agreement_id = p_agreement_id
And nvl(task_id, 0) = nvl(p_task_id, 0);
Select 1
From Pa_lookups lk, Pa_projects_all P , pa_project_types pt, pa_project_customers c
where (decode(p.template_flag, 'Y', 'Y', pa_security.allow_query(p.project_id)) = 'Y'
and decode(p.template_flag, 'Y', 'Y', pa_security.allow_update(p.project_id)) = 'Y' )
and pt.project_type = p.project_type and p.project_id = c.project_id
and pa_project_stus_utils.is_project_status_closed(p.project_status_code) = 'N'
and c.customer_id = p_customer_id and lk.lookup_type(+) = 'ALLOWABLE FUNDING LEVEL'
and lk.lookup_code(+) = pt.allowable_funding_level_code;
Cursor c1 is select 1 from pa_proj_fund_valid_v
where project_id = p_project_id
and customer_id = p_customer_id
AND project_type_class_code = 'CONTRACT'; /* Added for bug 3017733 */
SELECT count(*)
INTO l_count
FROM pa_agreements
WHERE agreement_id = p_agreement_id
AND advance_amount >0;
SELECT project_type
INTO l_proj_type
FROM pa_projects
WHERE project_id = p_project_id;
SELECT project_type
INTO l_proj_type1
FROM pa_project_types
WHERE project_type = l_proj_type
AND nvl(cc_prvdr_flag,'N') ='N';
SELECT f.project_funding_id
FROM PA_PROJECT_FUNDINGS f
WHERE f.pm_funding_reference = p_funding_reference;
Select 1
Into task_exists
From Dual
Where exists(
select task_name, task_number, task_id
from pa_tasks_top_v
where project_id = p_project_id
and task_id = p_task_id );
Select nvl(template_flag,'N')
into l_template_flag
From pa_projects_all
Where project_id = p_project_id;
Select ALLOWABLE_FUNDING_LEVEL_CODE
Into l_ALLOWABLE_FUNDING_LEVEL_CODE
From pa_project_types_all pt, pa_projects_all p
Where p.project_id=p_project_id
And p.project_type=pt.project_type
AND p.org_id = pt.org_id; /*Bug5374298 Removed NVL join on org_id*/
select 1
into proj_ev_exists
from pa_events
where project_id = p_project_id
and task_Id IS NULL
and rownum = 1;
select 'N' into v_valid_level_change from dual
where exists
(select T.project_id
from pa_expenditure_items E, pa_tasks T
where T.project_id = p_project_id
and E.task_id = T.task_id
and E.revenue_distributed_flag <> 'N');
select 'N' into v_valid_level_change from dual
where exists
(select E.project_id
from pa_expenditure_items E
where E.project_id = p_project_id
and E.revenue_distributed_flag <> 'N');
SELECT 'N'
INTO v_valid_level_change
FROM DUAL
WHERE EXISTS (
SELECT project_id FROM PA_EVENTS
WHERE project_id = p_project_id AND
revenue_distributed_flag ='Y');
FOR evt_rec IN( SELECT project_id, task_id, event_num, bill_amount
FROM pa_events
WHERE project_id = p_project_id AND
revenue_distributed_flag ='N' AND
bill_amount <> 0 ) LOOP
v_billed_flag := pa_events_pkg.Is_Event_Billed(
evt_rec.project_id,
evt_rec.task_id,
evt_rec.event_num,
evt_rec.bill_amount);
SELECT task_id INTO V_task_id
FROM pa_project_fundings
WHERE project_funding_id =
(SELECT max(project_funding_id)
FROM pa_project_fundings
WHERE project_id = p_project_id
AND agreement_id = p_agreement_id);
SELECT 'Y'
INTO Is_agr_fund_ok
FROM PA_PROJECTS_ALL P, PA_AGREEMENTS_ALL A
WHERE P.PROJECT_ID = p_project_id
AND A.AGREEMENT_ID = p_agreement_id
AND (P.multi_currency_billing_flag = 'Y'
OR (p.multi_currency_billing_flag = 'N'
AND p.projfunc_currency_code = a.agreement_currency_code))
AND not exists ( SELECT null
FROM PA_SUMMARY_PROJECT_FUNDINGS spf
WHERE spf.project_id = p.project_id
AND p.invproc_currency_type = 'FUNDING_CURRENCY'
AND spf.funding_currency_code <> a.agreement_currency_code
AND (spf.total_baselined_amount <> 0
OR spf.total_unbaselined_amount <> 0))
AND (nvl(p.template_flag,'N') = 'N'
OR ( p.template_flag = 'Y'
AND not exists ( select null
FROM PA_SUMMARY_PROJECT_FUNDINGS spf
where spf.project_id=p.project_id
and spf.agreement_id <> a.agreement_id))
)
AND (nvl(a.template_flag,'N') = 'N'
OR ( a.template_flag = 'Y'
AND not exists ( select null
FROM PA_SUMMARY_PROJECT_FUNDINGS spf
where spf.project_id <> p.project_id
and spf.agreement_id = a.agreement_id))
) ;
SELECT nvl(max(sum(nvl(allocated_amount,0))),0)
INTO dummy_amount
FROM PA_PROJECT_FUNDINGS P
WHERE P.PROJECT_ID = p_project_id
AND TASK_ID IS NOT NULL
AND BUDGET_TYPE_CODE IN ('BASELINE', 'DRAFT')
GROUP BY TASK_ID;
SELECT NVL(SUM(ALLOCATED_AMOUNT), 0)
INTO dummy_amount
FROM PA_PROJECT_FUNDINGS P
WHERE P.PROJECT_ID = p_project_id
AND P.TASK_ID IS NULL
AND P.BUDGET_TYPE_CODE IN ('BASELINE', 'DRAFT');
Select pt.project_type_class_code
Into l_class_code
From pa_projects_all p, pa_project_types_all pt
where p.project_type = pt.project_type
AND p.org_id = pt.org_id /*Bug5374298 Removed NVL join on org_id*/
and p.project_id = p_project_id;
Select pf.budget_type_code
Into l_budget_code
From pa_project_fundings pf
where pf.project_funding_id = p_funding_id;
p_Last_Update_Date IN DATE,
p_Last_Updated_By IN NUMBER,
p_Creation_Date IN DATE,
p_Created_By IN NUMBER,
p_Last_Update_Login IN NUMBER,
p_Agreement_Id IN NUMBER,
p_Project_Id IN NUMBER,
p_Task_id IN NUMBER,
p_Budget_Type_Code IN VARCHAR2,
p_Allocated_Amount IN NUMBER,
p_Date_Allocated IN DATE,
p_Control_Item_ID IN NUMBER DEFAULT NULL, -- FP_M changes
p_Attribute_Category IN VARCHAR2,
p_Attribute1 IN VARCHAR2,
p_Attribute2 IN VARCHAR2,
p_Attribute3 IN VARCHAR2,
p_Attribute4 IN VARCHAR2,
p_Attribute5 IN VARCHAR2,
p_Attribute6 IN VARCHAR2,
p_Attribute7 IN VARCHAR2,
p_Attribute8 IN VARCHAR2,
p_Attribute9 IN VARCHAR2,
p_Attribute10 IN VARCHAR2,
p_pm_funding_reference IN VARCHAR2,
p_pm_product_code IN VARCHAR2,
p_project_rate_type IN VARCHAR2 DEFAULT NULL,
p_project_rate_date IN DATE DEFAULT NULL,
p_project_exchange_rate IN NUMBER DEFAULT NULL,
p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL,
p_projfunc_rate_date IN DATE DEFAULT NULL,
p_projfunc_exchange_rate IN NUMBER DEFAULT NULL,
x_err_code OUT NOCOPY NUMBER,/*File.sql.39*/
x_err_msg OUT NOCOPY VARCHAR2,/*File.sql.39*/
p_funding_category IN VARCHAR2 /* Bug 2244796 */
)
IS
l_Project_Funding_Id NUMBER := p_Project_Funding_Id;
pa_project_fundings_pkg.insert_row(
x_rowid => p_rowid,
x_project_funding_id => p_project_funding_id,
x_last_update_date => p_last_update_date,
x_last_updated_by => p_last_updated_by,
x_creation_date => p_creation_date,
x_created_by => p_created_by,
x_last_update_login => p_last_update_login,
x_agreement_id => p_agreement_id,
x_project_id => p_project_id,
x_task_id => p_task_id,
x_budget_type_code => p_budget_type_code,
x_allocated_amount => l_allocated_amount,
x_date_allocated => p_date_allocated,
X_Control_Item_ID => p_Control_Item_ID, -- FP_M changes
x_attribute_category => p_attribute_category,
x_attribute1 => p_attribute1,
x_attribute2 => p_attribute2,
x_attribute3 => p_attribute3,
x_attribute4 => p_attribute4,
x_attribute5 => p_attribute5,
x_attribute6 => p_attribute6,
x_attribute7 => p_attribute7,
x_attribute8 => p_attribute8,
x_attribute9 => p_attribute9,
x_attribute10 => p_attribute10,
x_pm_funding_reference => p_pm_funding_reference,
x_pm_product_code => p_pm_product_code,
x_funding_currency_code => l_funding_currency_code,
x_project_currency_code => l_project_currency_code,
x_project_rate_type => l_project_rate_type,
x_project_rate_date => l_project_rate_date,
x_project_exchange_rate => l_project_exchange_rate,
x_project_allocated_amount => l_project_allocated_amount,
x_projfunc_currency_code => l_projfunc_currency_code,
x_projfunc_rate_type => l_projfunc_rate_type,
x_projfunc_rate_date => l_projfunc_rate_date,
x_projfunc_exchange_rate => l_projfunc_exchange_rate,
x_projfunc_allocated_amount => l_projfunc_allocated_amount,
x_invproc_currency_code => l_invproc_currency_code,
x_invproc_rate_type => l_invproc_rate_type,
x_invproc_rate_date => l_invproc_rate_date,
x_invproc_exchange_rate => l_invproc_exchange_rate,
x_invproc_allocated_amount => l_invproc_allocated_amount,
x_revproc_currency_code => l_revproc_currency_code,
x_revproc_rate_type => l_revproc_rate_type,
x_revproc_rate_date => l_revproc_rate_date,
x_revproc_exchange_rate => l_revproc_exchange_rate,
x_revproc_allocated_amount => l_revproc_allocated_amount,
x_funding_category => p_funding_category /* For Bug2244796 */
);
PROCEDURE Update_funding(
p_Project_Funding_Id IN NUMBER,
p_Last_Update_Date IN DATE,
p_Last_Updated_By IN NUMBER,
p_Last_Update_Login IN NUMBER,
p_Agreement_Id IN NUMBER,
p_Project_Id IN NUMBER,
p_Task_id IN NUMBER,
p_Budget_Type_Code IN VARCHAR2,
p_Allocated_Amount IN NUMBER,
p_Date_Allocated IN DATE,
p_Attribute_Category IN VARCHAR2,
p_Attribute1 IN VARCHAR2,
p_Attribute2 IN VARCHAR2,
p_Attribute3 IN VARCHAR2,
p_Attribute4 IN VARCHAR2,
p_Attribute5 IN VARCHAR2,
p_Attribute6 IN VARCHAR2,
p_Attribute7 IN VARCHAR2,
p_Attribute8 IN VARCHAR2,
p_Attribute9 IN VARCHAR2,
p_Attribute10 IN VARCHAR2,
p_pm_funding_reference IN VARCHAR2,
p_pm_product_code IN VARCHAR2,
p_project_rate_type IN VARCHAR2 DEFAULT NULL,
p_project_rate_date IN DATE DEFAULT NULL,
p_project_exchange_rate IN NUMBER DEFAULT NULL,
p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL,
p_projfunc_rate_date IN DATE DEFAULT NULL,
p_projfunc_exchange_rate IN NUMBER DEFAULT NULL,
x_err_code OUT NOCOPY NUMBER,/*File.sql.39*/
x_err_msg OUT NOCOPY VARCHAR2,/*File.sql.39*/
p_funding_category IN VARCHAR2 /* Bug 2244796 */
)
IS
CURSOR C IS
SELECT
rowid
FROM PA_PROJECT_FUNDINGS
WHERE project_funding_id = p_Project_Funding_Id;
pa_project_fundings_pkg.update_row(
x_rowid => fun_rec.rowid,
x_project_funding_id => p_project_funding_id,
x_last_update_date => p_last_update_date,
x_last_updated_by => p_last_updated_by,
x_last_update_login => p_last_update_login,
x_agreement_id => p_agreement_id,
x_project_id => p_project_id,
x_task_id => p_task_id,
x_budget_type_code => p_budget_type_code,
x_allocated_amount => l_allocated_amount,
x_date_allocated => p_date_allocated,
x_attribute_category => p_attribute_category,
x_attribute1 => p_attribute1,
x_attribute2 => p_attribute2,
x_attribute3 => p_attribute3,
x_attribute4 => p_attribute4,
x_attribute5 => p_attribute5,
x_attribute6 => p_attribute6,
x_attribute7 => p_attribute7,
x_attribute8 => p_attribute8,
x_attribute9 => p_attribute9,
x_attribute10 => p_attribute10,
x_pm_funding_reference => p_pm_funding_reference,
x_pm_product_code => p_pm_product_code,
x_funding_currency_code => l_funding_currency_code,
x_project_currency_code => l_project_currency_code,
x_project_rate_type => l_project_rate_type,
x_project_rate_date => l_project_rate_date,
x_project_exchange_rate => l_project_exchange_rate,
x_project_allocated_amount => l_project_allocated_amount,
x_projfunc_currency_code => l_projfunc_currency_code,
x_projfunc_rate_type => l_projfunc_rate_type,
x_projfunc_rate_date => l_projfunc_rate_date,
x_projfunc_exchange_rate => l_projfunc_exchange_rate,
x_projfunc_allocated_amount => l_projfunc_allocated_amount,
x_invproc_currency_code => l_invproc_currency_code,
x_invproc_rate_type => l_invproc_rate_type,
x_invproc_rate_date => l_invproc_rate_date,
x_invproc_exchange_rate => l_invproc_exchange_rate,
x_invproc_allocated_amount => l_invproc_allocated_amount,
x_revproc_currency_code => l_revproc_currency_code,
x_revproc_rate_type => l_revproc_rate_type,
x_revproc_rate_date => l_revproc_rate_date,
x_revproc_exchange_rate => l_revproc_exchange_rate,
x_revproc_allocated_amount => l_revproc_allocated_amount,
x_funding_category => p_funding_category /* Bug 2244796 */
);
END update_funding;
PROCEDURE Delete_funding(p_project_funding_id IN NUMBER)
is
CURSOR C IS
SELECT rowid
FROM PA_PROJECT_FUNDINGS
WHERE project_funding_id = p_project_funding_id;
pa_project_fundings_pkg.delete_row(fun_row_id);
END delete_funding;
SELECT rowid,
project_funding_id,
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,
pm_funding_reference,
pm_product_code,
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,
funding_category /* For Bug2244796 */
FROM PA_PROJECT_FUNDINGS
WHERE project_funding_id = p_Project_Funding_Id;
PROCEDURE summary_funding_insert_row
(p_agreement_id IN NUMBER
,p_project_id IN NUMBER
,p_task_id IN NUMBER
,p_login_id IN VARCHAR2
,p_user_id IN VARCHAR2
,p_budget_type_code IN VARCHAR2
)
IS
BEGIN
declare
cursor c1 is
select 1
from pa_summary_project_fundings
where project_id = p_project_id
and agreement_id = p_agreement_id
and nvl(task_id, 0) = nvl(p_task_id, 0);
pa_funding_core.summary_funding_update_row ( p_agreement_id
,p_project_id
,p_task_id
,p_login_id
,p_user_id
,p_budget_type_code
);
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,
NVL(SUM(DECODE(BUDGET_TYPE_CODE,'BASELINE',
NVL(ALLOCATED_AMOUNT,0))),0),
NVL(SUM(DECODE(BUDGET_TYPE_CODE,'DRAFT',
NVL(ALLOCATED_AMOUNT,0))),0),
0, 0, p_login_id, trunc(SYSDATE), p_user_id,
trunc(SYSDATE), p_user_id, FUNDING_CURRENCY_CODE,
PROJECT_CURRENCY_CODE,
NVL(SUM(DECODE(BUDGET_TYPE_CODE,'BASELINE',
NVL(PROJECT_ALLOCATED_AMOUNT,0))),0),
NVL(SUM(DECODE(BUDGET_TYPE_CODE,'DRAFT',
NVL(PROJECT_ALLOCATED_AMOUNT,0))),0),
0, 0,
PROJFUNC_CURRENCY_CODE,
NVL(SUM(DECODE(BUDGET_TYPE_CODE,'BASELINE',
NVL(PROJFUNC_ALLOCATED_AMOUNT,0))),0),
NVL(SUM(DECODE(BUDGET_TYPE_CODE,'DRAFT',
NVL(PROJFUNC_ALLOCATED_AMOUNT,0))),0),
0, 0,
INVPROC_CURRENCY_CODE,
NVL(SUM(DECODE(BUDGET_TYPE_CODE,'BASELINE',
NVL(INVPROC_ALLOCATED_AMOUNT,0))),0),
NVL(SUM(DECODE(BUDGET_TYPE_CODE,'DRAFT',
NVL(INVPROC_ALLOCATED_AMOUNT,0))),0),
0,
REVPROC_CURRENCY_CODE,
NVL(SUM(DECODE(BUDGET_TYPE_CODE,'BASELINE',
NVL(REVPROC_ALLOCATED_AMOUNT,0))),0),
NVL(SUM(DECODE(BUDGET_TYPE_CODE,'DRAFT',
NVL(REVPROC_ALLOCATED_AMOUNT,0))),0),
0
FROM PA_PROJECT_FUNDINGS
WHERE BUDGET_TYPE_CODE IN ('BASELINE','DRAFT')
AND PROJECT_ID = p_project_id
AND AGREEMENT_ID = p_agreement_id
AND NVL(TASK_ID,0) = NVL(p_task_id,0)
AND NOT EXISTS
(select NULL from PA_SUMMARY_PROJECT_FUNDINGS S
WHERE s.PROJECT_ID = p_project_id
AND s.AGREEMENT_ID = p_agreement_id
AND NVL(s.TASK_ID,0) = NVL(p_task_id,0))
GROUP BY AGREEMENT_ID,PROJECT_ID,TASK_ID,FUNDING_CURRENCY_CODE,
PROJECT_CURRENCY_CODE, PROJFUNC_CURRENCY_CODE,
INVPROC_CURRENCY_CODE, REVPROC_CURRENCY_CODE
;
pa_funding_core.summary_funding_update_row ( p_agreement_id
,p_project_id
,p_task_id
,p_login_id
,p_user_id
,p_budget_type_code
);
END summary_funding_insert_row;
PROCEDURE summary_funding_update_row
(p_agreement_id IN NUMBER
,p_project_id IN NUMBER
,p_task_id IN NUMBER
,p_login_id IN VARCHAR2
,p_user_id IN VARCHAR2
,p_budget_type_code IN VARCHAR2
)
IS
BEGIN
--dbms_output.put_line('Inside: PA_AGREEMENT_CORE.SUMMARY_FUNDING_UPDATE_ROW');
UPDATE PA_SUMMARY_PROJECT_FUNDINGS S
SET (S.TOTAL_UNBASELINED_AMOUNT, S.PROJECT_UNBASELINED_AMOUNT,
S.PROJFUNC_UNBASELINED_AMOUNT,
S.INVPROC_UNBASELINED_AMOUNT, S.REVPROC_UNBASELINED_AMOUNT) =
(SELECT SUM(DECODE(F.BUDGET_TYPE_CODE, 'BASELINE',0,
'DRAFT',nvl(F.ALLOCATED_AMOUNT,0))),
SUM(DECODE(F.BUDGET_TYPE_CODE, 'BASELINE',0,
'DRAFT',nvl(F.PROJECT_ALLOCATED_AMOUNT,0))),
SUM(DECODE(F.BUDGET_TYPE_CODE, 'BASELINE',0,
'DRAFT',nvl(F.PROJFUNC_ALLOCATED_AMOUNT,0))),
SUM(DECODE(F.BUDGET_TYPE_CODE, 'BASELINE',0,
'DRAFT',nvl(F.INVPROC_ALLOCATED_AMOUNT,0))),
SUM(DECODE(F.BUDGET_TYPE_CODE, 'BASELINE',0,
'DRAFT',nvl(F.REVPROC_ALLOCATED_AMOUNT,0)))
FROM PA_PROJECT_FUNDINGS F
WHERE F.PROJECT_ID = S.PROJECT_ID
AND F.AGREEMENT_ID = S.AGREEMENT_ID
AND NVL(F.TASK_ID,0) = NVL(S.TASK_ID,0)
GROUP BY F.AGREEMENT_ID, F.PROJECT_ID, F.TASK_ID)
WHERE S.AGREEMENT_ID = p_agreement_id
AND S.PROJECT_ID = p_project_id
AND NVL(S.TASK_ID,0) = NVL(p_task_id,0);
pa_funding_core.summary_funding_insert_row ( p_agreement_id
,p_project_id
,p_task_id
,p_login_id
,p_user_id
,p_budget_type_code
);
END summary_funding_update_row;
PROCEDURE summary_funding_delete_row
(p_agreement_id IN NUMBER
,p_project_id IN NUMBER
,p_task_id IN NUMBER
,p_login_id IN VARCHAR2
,p_user_id IN VARCHAR2
,p_budget_type_code IN VARCHAR2
)
IS
BEGIN
declare
dummy number;
select 1
from pa_project_fundings
where project_id = p_project_id
and nvl(task_id, 0) = nvl(p_task_id, 0)
and agreement_id = p_agreement_id;
pa_funding_core.summary_funding_update_row ( p_agreement_id
,p_project_id
,p_task_id
,p_login_id
,p_user_id
,p_budget_type_code
);
DELETE FROM PA_SUMMARY_PROJECT_FUNDINGS S
WHERE S.PROJECT_ID = p_project_id
AND S.AGREEMENT_ID = p_agreement_id
AND NVL(S.TASK_ID,0) = NVL(p_task_id,0);
END summary_funding_delete_row;
SELECT 'Y' INTO l_valid_rate_type
from pa_conversion_types_v
/* WHERE user_conversion_type = p_exchange_rate_type Commented for bug 5478703 */
WHERE conversion_type = p_exchange_rate_type /* Added for bug 5478703 */
AND ( (p_exchange_rate_type = 'User'
AND pa_multi_currency.is_user_rate_type_allowed(
p_funding_currency_code,
p_to_currency_code,
p_exchange_rate_date )= 'Y')
OR p_exchange_rate_type <> 'User');
SELECT 'Y' INTO l_valid_rate_type
from pa_conversion_types_v
WHERE user_conversion_type = p_exchange_rate_type;
SELECT conversion_type
INTO p_project_rate_type
FROM pa_conversion_types_v
WHERE user_conversion_type = p_project_rate_type
or conversion_type = p_project_rate_type;
SELECT conversion_type
INTO p_projfunc_rate_type
FROM pa_conversion_types_v
WHERE user_conversion_type = p_projfunc_rate_type
or conversion_type = p_projfunc_rate_type;
SELECT 'Y' into l_mult_funding_flag
FROM dual
WHERE exists ( select null
FROM PA_SUMMARY_PROJECT_FUNDINGS spf
WHERE spf.project_id = p_project_id
AND spf.funding_currency_code <> p_funding_currency_code
AND spf.total_baselined_amount <> 0
AND spf.total_unbaselined_amount <> 0);
p_Last_Update_Date IN DATE,
p_Last_Updated_By IN NUMBER,
p_Creation_Date IN DATE,
p_Created_By IN NUMBER,
p_Last_Update_Login IN NUMBER,
p_Agreement_Id IN NUMBER,
p_Project_Id IN NUMBER,
p_Task_id IN NUMBER,
p_Budget_Type_Code IN VARCHAR2,
p_Allocated_Amount IN NUMBER,
p_Date_Allocated IN DATE,
P_Funding_Currency_Code IN VARCHAR2, -- FP_M CI changes
p_Control_Item_ID IN NUMBER DEFAULT NULL, -- FP_M changes
p_Attribute_Category IN VARCHAR2,
p_Attribute1 IN VARCHAR2,
p_Attribute2 IN VARCHAR2,
p_Attribute3 IN VARCHAR2,
p_Attribute4 IN VARCHAR2,
p_Attribute5 IN VARCHAR2,
p_Attribute6 IN VARCHAR2,
p_Attribute7 IN VARCHAR2,
p_Attribute8 IN VARCHAR2,
p_Attribute9 IN VARCHAR2,
p_Attribute10 IN VARCHAR2,
p_pm_funding_reference IN VARCHAR2,
p_pm_product_code IN VARCHAR2,
p_Project_Allocated_Amount IN NUMBER DEFAULT 0, -- FP_M changes
p_project_rate_type IN VARCHAR2 DEFAULT NULL,
p_project_rate_date IN DATE DEFAULT NULL,
p_project_exchange_rate IN NUMBER DEFAULT NULL,
p_Projfunc_Allocated_Amount IN NUMBER DEFAULT 0, -- FP_M changes
p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL,
p_projfunc_rate_date IN DATE DEFAULT NULL,
p_projfunc_exchange_rate IN NUMBER DEFAULT NULL,
x_err_code OUT NOCOPY NUMBER,/*FILE.sql.39*/
x_err_msg OUT NOCOPY VARCHAR2,/*FILE.sql.39*/
p_funding_category IN VARCHAR2 /* Bug 2244796 */
)
IS
l_Project_Funding_Id NUMBER := p_Project_Funding_Id;
Select Invproc_Currency_Type, Project_Currency_Code, ProjFunc_Currency_Code
INTO l_Invproc_Currency_Type, l_Project_Currency_Code, l_ProjFunc_Currency_Code
FROM PA_Projects
Where Project_ID = P_Project_ID;
pa_project_fundings_pkg.insert_row(
x_rowid => p_rowid,
x_project_funding_id => p_project_funding_id,
x_last_update_date => p_last_update_date,
x_last_updated_by => p_last_updated_by,
x_creation_date => p_creation_date,
x_created_by => p_created_by,
x_last_update_login => p_last_update_login,
x_agreement_id => p_agreement_id,
x_project_id => p_project_id,
x_task_id => p_task_id,
x_budget_type_code => p_budget_type_code,
x_allocated_amount => p_allocated_amount,
x_date_allocated => p_date_allocated,
X_Control_Item_ID => p_Control_Item_ID, -- FP_M changes
x_attribute_category => p_attribute_category,
x_attribute1 => p_attribute1,
x_attribute2 => p_attribute2,
x_attribute3 => p_attribute3,
x_attribute4 => p_attribute4,
x_attribute5 => p_attribute5,
x_attribute6 => p_attribute6,
x_attribute7 => p_attribute7,
x_attribute8 => p_attribute8,
x_attribute9 => p_attribute9,
x_attribute10 => p_attribute10,
x_pm_funding_reference => p_pm_funding_reference,
x_pm_product_code => p_pm_product_code,
x_funding_currency_code => p_funding_currency_code,
x_project_currency_code => l_project_currency_code,
x_project_rate_type => p_project_rate_type,
x_project_rate_date => p_project_rate_date,
x_project_exchange_rate => p_project_exchange_rate,
x_project_allocated_amount => p_project_allocated_amount,
x_projfunc_currency_code => l_projfunc_currency_code,
x_projfunc_rate_type => p_projfunc_rate_type,
x_projfunc_rate_date => p_projfunc_rate_date,
x_projfunc_exchange_rate => p_projfunc_exchange_rate,
x_projfunc_allocated_amount => p_projfunc_allocated_amount,
x_invproc_currency_code => l_invproc_currency_code,
x_invproc_rate_type => l_invproc_rate_type,
x_invproc_rate_date => l_invproc_rate_date,
x_invproc_exchange_rate => l_invproc_exchange_rate,
x_invproc_allocated_amount => l_invproc_allocated_amount,
x_revproc_currency_code => l_projfunc_currency_code,
x_revproc_rate_type => p_projfunc_rate_type,
x_revproc_rate_date => p_projfunc_rate_date,
x_revproc_exchange_rate => p_projfunc_exchange_rate,
x_revproc_allocated_amount => p_projfunc_allocated_amount,
x_funding_category => p_funding_category /* For Bug2244796 */
);