[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_update_agr_amount_flag IN VARCHAR2,
p_funding_category IN VARCHAR2 ,
p_partial_factor IN NUMBER,
p_impl_txn_rev_amt IN NUMBER,
p_impl_pc_rev_amt IN NUMBER,
p_impl_pfc_rev_amt IN NUMBER) IS
l_agreement_id pa_agreements_all.agreement_id%TYPE;
l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
l_amount_tab.DELETE;
l_amount_tab_in_pc.DELETE;
l_amount_tab_in_pfc.DELETE;
l_task_id_tab.DELETE;
SELECT project_currency_Code,
projfunc_currency_code
INTO
l_proj_curr_code,
l_projfunc_curr_code
FROM pa_projects_all
WHERE
project_id = p_project_id;
SELECT budget_version_id,
agreement_id,
DECODE(bv.version_type,'REVENUE',revenue_fin_plan_level_code,
'ALL',all_fin_plan_level_code,null)
INTO
l_budget_version_id,
l_agreement_id,
l_ci_ver_planning_level
FROM PA_BUDGET_VERSIONS bv,
pa_proj_fp_options po WHERE
bv.project_id = p_project_id
AND bv.approved_rev_plan_type_flag = 'Y'
AND bv.version_type IN ('REVENUE','ALL')
AND po.project_id = bv.project_id
AND po.fin_plan_type_id = bv.fin_plan_type_id
AND po.fin_plan_version_id = bv.budget_version_id
AND po.fin_plan_option_level_code = 'PLAN_VERSION'
AND bv.ci_id = p_ci_id;
Select count(*)
into l_budget_line_count
from pa_budget_lines pbl
where pbl.budget_version_id = l_budget_version_id; --Bug 5509687
SELECT budget_version_id INTO l_bv_id
FROM pa_budget_versions
WHERE
project_id = p_project_id AND
version_type IN ('REVENUE','ALL') AND
NVL(current_working_flag,'N' ) = 'Y' AND
NVL(Approved_Rev_Plan_Type_Flag,'N') = 'Y' AND
CI_ID IS NULL;
SELECT customer_id,
agreement_type,
term_id,
template_Flag,
revenue_limit_flag,
owned_by_person_id,
owning_organization_id,
agreement_currency_code,
invoice_limit_flag,
agreement_num,
expiration_Date,
Attribute_Category,
Attribute1,
Attribute2,
Attribute3,
Attribute4,
Attribute5,
Attribute6,
Attribute7,
Attribute8,
Attribute9,
Attribute10,
Amount
INTO
l_customer_id,
l_agreement_type,
l_term_id,
l_template_flag,
l_revenue_limit_flag,
l_owned_by_person_id,
l_owning_org_id,
l_agr_curr_code,
l_invoice_limit_flag,
l_agreement_num,
l_expiration_date,
l_Attribute_Category,
l_Attribute1,
l_Attribute2,
l_Attribute3,
l_Attribute4,
l_Attribute5,
l_Attribute6,
l_Attribute7,
l_Attribute8,
l_Attribute9,
l_Attribute10,
l_agr_amount
FROM pa_agreements_all WHERE
agreement_id = l_agreement_id;
'upd agr amt flag from page '||p_update_agr_amount_flag
||' fund cate fr page '||p_funding_category
,x_log_level => 5);
/* check for agreement amount update allowed */
IF l_debug_mode = 'Y' THEN
PA_DEBUG.write_log (x_module =>
'pa.plsql.pa_fp_ci_implement_pkg.create_ci_impact_fund_lines'
,x_msg => 'total fund amt '||ltrim(to_char(l_total_amount))
,x_log_level => 5);
IF p_update_agr_amount_flag = 'Y' THEN
l_upd_agr_allowed := pa_agreement_pvt.check_update_agreement_ok
(p_pm_agreement_reference => NULL
,p_agreement_id => l_agreement_id
,p_funding_id => NULL
,p_customer_id => l_customer_id
,p_agreement_type => l_agreement_type
,p_term_id => l_term_id
,p_template_flag => l_template_flag
,p_revenue_limit_flag => l_revenue_limit_flag
,p_owned_by_person_id => l_owned_by_person_id
,p_owning_organization_id => l_owning_org_id
,p_agreement_currency_code => l_agr_curr_code
,p_invoice_limit_flag => l_invoice_limit_flag
,p_start_date => PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE -- Bug 5522880
,p_end_date => PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE -- Bug 5522880
,p_advance_required => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR -- Bug 5522880
,p_billing_sequence => PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM -- Bug 5522880
,p_amount => PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM); -- Bug 5522880
/* calling update agreement API */
/* the update agreement API expects the existing amount plus
the new amount for the update bug 2671305 */
l_new_agr_amount := NVL(l_total_amount,0) + l_agr_amount;
pa_agreement_core.update_agreement(
p_Agreement_Id => l_agreement_id,
p_Customer_Id => l_customer_id,
p_Agreement_Num => l_agreement_num,
p_Agreement_Type => l_agreement_type,
p_Last_Update_Date => TRUNC(SYSDATE),
p_Last_Updated_By => l_last_updated_by,
p_Last_Update_Login => l_last_update_login,
p_Owned_By_Person_Id => l_owned_by_person_id,
p_Term_Id => l_term_id,
p_Revenue_Limit_Flag => l_revenue_limit_flag,
p_Amount => l_new_agr_amount,
p_Description => NULL,
p_Expiration_Date => l_expiration_date,
p_Attribute_Category => l_attribute_category,
p_Attribute1 => l_attribute1,
p_Attribute2 => l_attribute2,
p_Attribute3 => l_attribute3,
p_Attribute4 => l_attribute4,
p_Attribute5 => l_attribute5,
p_Attribute6 => l_attribute6,
p_Attribute7 => l_attribute7,
p_Attribute8 => l_attribute8,
p_Attribute9 => l_attribute9,
p_Attribute10 => l_attribute10,
p_Template_Flag => l_template_flag,
p_pm_agreement_reference => NULL,
p_pm_product_code => NULL,
p_agreement_currency_code => l_agr_curr_code,
p_owning_organization_id => l_owning_org_id,
p_invoice_limit_flag => l_invoice_limit_flag,
p_customer_order_number => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR, -- Bug 5522880
p_advance_required => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_start_date => PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_billing_sequence => PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_line_of_account => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_Attribute11 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_Attribute12 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_Attribute13 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_Attribute14 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_Attribute15 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_Attribute16 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_Attribute17 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_Attribute18 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_Attribute19 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_Attribute20 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_Attribute21 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_Attribute22 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_Attribute23 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_Attribute24 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_Attribute25 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR); -- Bug 5522880
SELECT amount into l_tmp_amount
FROM pa_agreements_all WHERE
agreement_id = l_agreement_id;
/* check and call for agreement amount update */
/* check for validate funding amount */
l_valid_funding_amt_flag := Pa_agreement_pvt.validate_funding_amt(
p_funding_amt => l_total_amount,
p_agreement_id => l_agreement_id,
p_operation_flag => 'A',
p_funding_id => NULL,
p_pm_funding_reference => NULL );
SELECT NVL( SUM(nvl(bl.txn_revenue,0)) , 0)*p_partial_factor,
NVL( SUM(nvl(bl.project_revenue,0)) , 0)*p_partial_factor,
NVL( SUM(nvl(bl.revenue,0)) , 0)*p_partial_factor,
ra.Task_id
BULK COLLECT INTO
l_amount_tab,
l_amount_tab_in_pc,
l_amount_tab_in_pfc,
l_task_id_tab
FROM pa_budget_lines bl,
pa_resource_assignments ra
WHERE
ra.project_id = p_project_id AND
ra.budget_version_id = l_budget_version_id AND
NVL(ra.resource_assignment_type,'USER_ENTERED') = 'USER_ENTERED' AND
ra.resource_assignment_id = bl.resource_Assignment_id AND
bl.budget_version_id = ra.budget_version_id AND
bl.cost_rejection_code IS NULL AND
bl.revenue_rejection_code IS NULL AND
bl.burden_rejection_code IS NULL AND
bl.other_rejection_code IS NULL AND
bl.pc_cur_conv_rejection_code IS NULL AND
bl.pfc_cur_conv_rejection_code IS NULL
GROUP BY ra.task_id HAVING NVL( SUM(nvl(bl.txn_revenue,0)) , 0) <> 0
ORDER BY PA_PROJ_ELEMENTS_UTILS.GET_DISPLAY_SEQUENCE(ra.task_id);
SELECT NVL( SUM(nvl(bl.txn_revenue,0)) , 0)*p_partial_factor,
NVL( SUM(nvl(bl.project_revenue,0)) , 0)*p_partial_factor,
NVL( SUM(nvl(bl.revenue,0)) , 0)*p_partial_factor,
pt.top_task_id
BULK COLLECT INTO
l_amount_tab,
l_amount_tab_in_pc,
l_amount_tab_in_pfc,
l_task_id_tab
FROM pa_budget_lines bl,
pa_resource_assignments ra,
pa_tasks pt
WHERE ra.project_id = p_project_id
AND ra.budget_version_id = l_budget_version_id
AND NVL(ra.resource_assignment_type,'USER_ENTERED') = 'USER_ENTERED'
AND ra.task_id = pt.task_id
AND ra.resource_assignment_id = bl.resource_Assignment_id
AND bl.budget_version_id = ra.budget_version_id
AND bl.cost_rejection_code IS NULL
AND bl.revenue_rejection_code IS NULL
AND bl.burden_rejection_code IS NULL
AND bl.other_rejection_code IS NULL
AND bl.pc_cur_conv_rejection_code IS NULL
AND bl.pfc_cur_conv_rejection_code IS NULL
GROUP BY pt.top_task_id HAVING NVL( SUM(nvl(bl.txn_revenue,0)) , 0) <> 0
ORDER BY PA_PROJ_ELEMENTS_UTILS.GET_DISPLAY_SEQUENCE(pt.top_task_id);
p_Last_Update_Date => l_sysdate,
p_Last_Updated_By => l_last_updated_by,
p_Creation_Date => l_sysdate,
p_Created_By => l_last_updated_by,
p_Last_Update_Login => l_last_update_login,
p_Agreement_Id => l_agreement_id,
p_Project_Id => p_project_id,
p_Task_id => l_task_id_tab(i),
p_Budget_Type_Code => 'DRAFT',
p_Allocated_Amount => l_amount_tab(i),
p_Date_Allocated => l_sysdate,
P_Funding_Currency_Code => l_agr_curr_code,
p_Control_Item_ID => p_ci_id,
p_Attribute_Category => NULL,
p_Attribute1 => NULL,
p_Attribute2 => NULL,
p_Attribute3 => NULL,
p_Attribute4 => NULL,
p_Attribute5 => NULL,
p_Attribute6 => NULL,
p_Attribute7 => NULL,
p_Attribute8 => NULL,
p_Attribute9 => NULL,
p_Attribute10 => NULL,
p_pm_funding_reference => NULL,
p_pm_product_code => NULL,
p_Project_Allocated_Amount => l_amount_tab_in_pc(i),
p_project_rate_type => 'User',
p_project_rate_date => NULL,
--p_project_exchange_rate => l_amount_tab_in_pc(i)/l_amount_tab(i),
p_project_exchange_rate => l_project_exchange_rate, --Bug 6772321
p_Projfunc_Allocated_Amount => l_amount_tab_in_pfc(i),
p_projfunc_rate_type => 'User',
p_projfunc_rate_date => NULL,
--p_projfunc_exchange_rate => l_amount_tab_in_pfc(i)/l_amount_tab(i),
p_projfunc_exchange_rate => l_projfunc_exchange_rate, --Bug 6772321
x_err_code => l_err_code,
x_err_msg => l_err_stage,
p_funding_category => p_funding_category );
pa_agreement_utils.summary_funding_insert_row(
p_agreement_id => l_agreement_id,
p_project_id => p_project_id,
p_task_id => l_task_id_tab(i),
p_login_id => LTRIM(RTRIM(TO_CHAR(l_last_update_login))),
p_user_id => LTRIM(RTRIM(TO_CHAR(l_last_updated_by)))
);
l_wbs_update_flag VARCHAR2(1);
/* l_wbs_update_flag is used to display the error only one time,
if the Cost and Revenue amounts are planned separately and
both the target versions are undergoing WBS process update
changes. */
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
l_wbs_update_flag := 'Y';
SELECT budget_status_code,
locked_by_person_id,
version_type,
NVL(request_id,0),
NVL(plan_processing_code,'ABC'),
NVL(process_update_wbs_flag,'N')
INTO
l_budget_status_code,
l_locked_by_person_id,
l_version_type,
l_request_id,
l_plan_proc_code,
l_refresh_required_flag
FROM pa_budget_versions WHERE
budget_version_id = p_target_fp_version_id_tbl(i);
IF l_plan_proc_code = 'WUP' AND l_wbs_update_flag = 'Y' THEN
x_return_status := FND_API.G_RET_STS_ERROR;
l_wbs_update_flag := 'N';
SELECT meaning
INTO l_meaning
FROM pa_lookups
WHERE lookup_type = 'FIN_PLAN_VER_TYPE'
AND lookup_code = l_version_type;
SELECT meaning
INTO l_meaning
FROM pa_lookups
WHERE lookup_type = 'FIN_PLAN_VER_TYPE'
AND lookup_code = l_version_type;