[Home] [Help]
PACKAGE BODY: APPS.PA_FP_CI_IMPLEMENT_PKG
Source
1 package body pa_fp_ci_implement_pkg as
2 /* $Header: PAFPCOMB.pls 120.3.12010000.3 2008/09/10 21:11:27 snizam ship $ */
3 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4
5 --3 new parameters are added as part of rounding changes.
6 ---->p_impl_txn_rev_amt : contain the amount in agreement currency for which funding lines should be created
7 ---->p_impl_pc_rev_amt : contain the amount in project currency for which funding lines should be created
8 ---->p_impl_pfc_rev_amt : contain the amount in project functional currency for which funding lines should be created
9 --The calling API should round these parameters before calling the APi
10 PROCEDURE create_ci_impact_fund_lines(
11 p_project_id IN NUMBER,
12 p_ci_id IN NUMBER,
13 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
14 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
15 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
16 p_update_agr_amount_flag IN VARCHAR2,
17 p_funding_category IN VARCHAR2 ,
18 p_partial_factor IN NUMBER,
19 p_impl_txn_rev_amt IN NUMBER,
20 p_impl_pc_rev_amt IN NUMBER,
21 p_impl_pfc_rev_amt IN NUMBER) IS
22 l_agreement_id pa_agreements_all.agreement_id%TYPE;
23 l_budget_version_id pa_budget_versions.budget_version_id%TYPE;
24 l_total_proj_revenue pa_budget_versions.total_project_revenue%TYPE;
25 l_total_projfunc_revenue pa_budget_versions.revenue%TYPE;
26 l_bv_id pa_budget_versions.budget_version_id%TYPE;
27
28 l_ci_ver_planning_level pa_proj_fp_options.all_fin_plan_level_code%TYPE;
29 l_funding_level VARCHAR2(100);
30 l_err_code NUMBER := null;
31 l_err_stage varchar2(1000) := null;
32 l_err_stack varchar2(1000) := null;
33 l_msg_data VARCHAR2(1000);
34 l_msg_index_out NUMBER:=0;
35 l_upd_agr_allowed VARCHAR2(30);
36 l_valid_funding_amt_flag VARCHAR2(30);
37 l_add_funding_ok_flag VARCHAR2(30);
38
39 l_customer_id pa_agreements_all.customer_id%TYPE;
40 l_agreement_type pa_agreements_all.agreement_type%TYPE;
41 l_term_id pa_agreements_all.term_id%TYPE;
42 l_template_flag pa_agreements_all.template_Flag%TYPE;
43 l_revenue_limit_flag pa_agreements_all.revenue_limit_flag%TYPE;
44 l_owned_by_person_id pa_agreements_all.owned_by_person_id%TYPE;
45 l_owning_org_id pa_agreements_all.owning_organization_id%TYPE;
46 l_agr_curr_code pa_agreements_all.agreement_currency_Code%TYPE;
47 l_invoice_limit_flag pa_agreements_all.invoice_limit_flag%TYPE;
48 l_agreement_num pa_agreements_all.agreement_num%TYPE;
49 l_expiration_Date pa_agreements_all.expiration_date%TYPE;
50 l_Attribute_Category pa_agreements_all.Attribute_Category%TYPE;
51 l_Attribute1 pa_agreements_all.Attribute1%TYPE;
52 l_Attribute2 pa_agreements_all.Attribute2%TYPE;
53 l_Attribute3 pa_agreements_all.Attribute3%TYPE;
54 l_Attribute4 pa_agreements_all.Attribute4%TYPE;
55 l_Attribute5 pa_agreements_all.Attribute5%TYPE;
56 l_Attribute6 pa_agreements_all.Attribute6%TYPE;
57 l_Attribute7 pa_agreements_all.Attribute7%TYPE;
58 l_Attribute8 pa_agreements_all.Attribute8%TYPE;
59 l_Attribute9 pa_agreements_all.Attribute9%TYPE;
60 l_Attribute10 pa_agreements_all.Attribute10%TYPE;
61 l_agr_amount pa_agreements_all.Amount%TYPE;
62
63 l_new_agr_amount NUMBER;
64
65 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
66 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
67 l_sysdate DATE := TRUNC(SYSDATE);
68 l_total_amount NUMBER;
69 l_rowid ROWID;
70 l_project_funding_id NUMBER;
71
72 l_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
73 l_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
74 --These tbls will hold the change order amounts in PC and PFC.
75 l_amount_tab_in_pc PA_PLSQL_DATATYPES.NumTabTyp;
76 l_amount_tab_in_pfc PA_PLSQL_DATATYPES.NumTabTyp;
77 l_proj_curr_code pa_projects_all.project_currency_code%TYPE;
78 l_projfunc_curr_code pa_projects_all.projfunc_currency_code%TYPE;
79 l_debug_mode VARCHAR2(30);
80 l_tmp_amount NUMBER;
81 l_rounded_agr_sum NUMBER;
82 l_rounded_pc_sum NUMBER;
83 l_rounded_pfc_sum NUMBER;
84 l_module_name VARCHAR2(100):='pa_fp_ci_implement_pkg.create_ci_impact_fund_lines';
85
86 l_budget_line_count NUMBER; --Bug 5509687
87 -- Bug 6772321
88 l_project_exchange_rate pa_project_fundings.project_exchange_rate%TYPE;
89 l_projfunc_exchange_rate pa_project_fundings.projfunc_exchange_rate%TYPE;
90 BEGIN
91
92 x_return_status := FND_API.G_RET_STS_SUCCESS;
93 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
94 l_debug_mode := NVL(l_debug_mode, 'Y');
95 /* the above default is set for testing purpose only
96 need to set to 'N' after testing */
97 IF l_debug_mode = 'Y' THEN
98 IF P_PA_DEBUG_MODE = 'Y' THEN
99 PA_DEBUG.init_err_stack('pa_fp_ci_implement_pkg.create_ci_impact_fund_lines');
100 END IF;
101 END IF;
102 IF p_ci_id IS NULL OR
103 p_partial_factor IS NULL OR
104 p_project_id IS NULL THEN
105
106 IF l_debug_mode = 'Y' THEN
107 pa_debug.g_err_stage:= 'p_ci_id IS '||p_ci_id;
108 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
109
110 pa_debug.g_err_stage:= 'p_partial_factor IS '||p_partial_factor;
111 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
112
113 pa_debug.g_err_stage:= 'p_project_id IS '||p_project_id;
114 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
115 END IF;
116 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
117 p_msg_name => 'PA_FP_INV_PARAM_PASSED',
118 p_token1 => 'PROCEDURENAME',
119 p_value1 => l_module_name);
120 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
121 END IF;
122
123
124
125 l_amount_tab.DELETE;
126 l_amount_tab_in_pc.DELETE;
127 l_amount_tab_in_pfc.DELETE;
128 l_task_id_tab.DELETE;
129
130 SELECT project_currency_Code,
131 projfunc_currency_code
132 INTO
133 l_proj_curr_code,
134 l_projfunc_curr_code
135 FROM pa_projects_all
136 WHERE
137 project_id = p_project_id;
138
139 SELECT budget_version_id,
140 agreement_id,
141 DECODE(bv.version_type,'REVENUE',revenue_fin_plan_level_code,
142 'ALL',all_fin_plan_level_code,null)
143 INTO
144 l_budget_version_id,
145 l_agreement_id,
146 l_ci_ver_planning_level
147 FROM PA_BUDGET_VERSIONS bv,
148 pa_proj_fp_options po WHERE
149 bv.project_id = p_project_id
150 AND bv.approved_rev_plan_type_flag = 'Y'
151 AND bv.version_type IN ('REVENUE','ALL')
152 AND po.project_id = bv.project_id
153 AND po.fin_plan_type_id = bv.fin_plan_type_id
154 AND po.fin_plan_version_id = bv.budget_version_id
155 AND po.fin_plan_option_level_code = 'PLAN_VERSION'
156 AND bv.ci_id = p_ci_id;
157
158 Select count(*)
159 into l_budget_line_count
160 from pa_budget_lines pbl
161 where pbl.budget_version_id = l_budget_version_id; --Bug 5509687
162
163 IF l_budget_line_count > 0 THEN --Bug 5509687: Prevent from creating funding lines if there are no impact lines
164
165 IF l_debug_mode = 'Y' THEN
166 PA_DEBUG.write_log (x_module =>
167 'pa.plsql.pa_fp_ci_implement_pkg.create_ci_impact_fund_lines'
168 ,x_msg => 'before getting CW REVENUE budget version id '
169 ,x_log_level => 5);
170 END IF;
171
172 BEGIN
173 SELECT budget_version_id INTO l_bv_id
174 FROM pa_budget_versions
175 WHERE
176 project_id = p_project_id AND
177 version_type IN ('REVENUE','ALL') AND
178 NVL(current_working_flag,'N' ) = 'Y' AND
179 NVL(Approved_Rev_Plan_Type_Flag,'N') = 'Y' AND
180 CI_ID IS NULL;
181 EXCEPTION
182 WHEN NO_DATA_FOUND THEN
183 x_return_status := FND_API.G_RET_STS_ERROR;
184 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
185 p_msg_name => 'PA_FP_CI_NO_CURR_WK_VERSION');
186 IF l_debug_mode = 'Y' THEN
187 PA_DEBUG.Reset_Err_Stack;
188 END IF;
189 RETURN;
190 END;
191
192 IF l_debug_mode = 'Y' THEN
193 PA_DEBUG.write_log (x_module =>
194 'pa.plsql.pa_fp_ci_implement_pkg.create_ci_impact_fund_lines'
195 ,x_msg => 'after getting CW REVENUE budget version id '||
196 TO_CHAR(l_bv_id)
197 ,x_log_level => 5);
198 END IF;
199
200 SELECT customer_id,
201 agreement_type,
202 term_id,
203 template_Flag,
204 revenue_limit_flag,
205 owned_by_person_id,
206 owning_organization_id,
207 agreement_currency_code,
208 invoice_limit_flag,
209 agreement_num,
210 expiration_Date,
211 Attribute_Category,
212 Attribute1,
213 Attribute2,
214 Attribute3,
215 Attribute4,
216 Attribute5,
217 Attribute6,
218 Attribute7,
219 Attribute8,
220 Attribute9,
221 Attribute10,
222 Amount
223 INTO
224 l_customer_id,
225 l_agreement_type,
226 l_term_id,
227 l_template_flag,
228 l_revenue_limit_flag,
229 l_owned_by_person_id,
230 l_owning_org_id,
231 l_agr_curr_code,
232 l_invoice_limit_flag,
233 l_agreement_num,
234 l_expiration_date,
235 l_Attribute_Category,
236 l_Attribute1,
237 l_Attribute2,
238 l_Attribute3,
239 l_Attribute4,
240 l_Attribute5,
241 l_Attribute6,
242 l_Attribute7,
243 l_Attribute8,
244 l_Attribute9,
245 l_Attribute10,
246 l_agr_amount
247 FROM pa_agreements_all WHERE
248 agreement_id = l_agreement_id;
249
250 IF l_debug_mode = 'Y' THEN
251 PA_DEBUG.write_log (x_module =>
252 'pa.plsql.pa_fp_ci_implement_pkg.create_ci_impact_fund_lines'
253 ,x_msg => 'fund level chk begin '||
254 'upd agr amt flag from page '||p_update_agr_amount_flag
255 ||' fund cate fr page '||p_funding_category
256 ,x_log_level => 5);
257 PA_DEBUG.write_log (x_module
258 => 'pa.plsql.pa_fp_ci_implement_pkg.create_ci_impact_fund_lines'
259 ,x_msg => 'prj id '||to_char(p_project_id) ||
260 'ci id '||to_char(p_ci_id)
261 ,x_log_level => 5);
262 END IF;
263
264 pa_billing_core.check_funding_level( x_project_id => p_project_id,
265 x_funding_level => l_funding_level,
266 x_err_code => l_err_code,
267 x_err_stage => l_err_stage,
268 x_err_stack => l_err_stack );
269
270 IF (l_err_code <> 0) THEN
271 x_return_status := FND_API.G_RET_STS_ERROR;
272 /* x_msg_count := FND_MSG_PUB.Count_Msg;
273 IF x_msg_count = 1 THEN
274 PA_INTERFACE_UTILS_PUB.get_messages
275 (p_encoded => FND_API.G_TRUE,
276 p_msg_index => 1,
277 p_msg_count => 1,
278 p_msg_data => l_msg_data ,
279 p_data => x_msg_data,
280 p_msg_index_out => l_msg_index_out);
281 END IF; */
282 IF l_debug_mode = 'Y' THEN
283 PA_DEBUG.Reset_Err_Stack;
284 END IF;
285
286 RETURN;
287 END IF;
288 IF l_debug_mode = 'Y' THEN
289 PA_DEBUG.write_log (x_module =>
290 'pa.plsql.pa_fp_ci_implement_pkg.create_ci_impact_fund_lines'
291 ,x_msg => 'funding level '||l_funding_level
292 ,x_log_level => 5);
293 END IF;
294
295 l_total_amount := p_impl_txn_rev_amt;
296 l_total_projfunc_revenue := p_impl_pfc_rev_amt;
297 l_total_proj_revenue := p_impl_pc_rev_amt;
298
299 /* check for agreement amount update allowed */
300 IF l_debug_mode = 'Y' THEN
301 PA_DEBUG.write_log (x_module =>
302 'pa.plsql.pa_fp_ci_implement_pkg.create_ci_impact_fund_lines'
303 ,x_msg => 'total fund amt '||ltrim(to_char(l_total_amount))
304 ,x_log_level => 5);
305 END IF;
306
307 IF p_update_agr_amount_flag = 'Y' THEN
308 l_upd_agr_allowed := pa_agreement_pvt.check_update_agreement_ok
309 (p_pm_agreement_reference => NULL
310 ,p_agreement_id => l_agreement_id
311 ,p_funding_id => NULL
312 ,p_customer_id => l_customer_id
313 ,p_agreement_type => l_agreement_type
314 ,p_term_id => l_term_id
315 ,p_template_flag => l_template_flag
316 ,p_revenue_limit_flag => l_revenue_limit_flag
317 ,p_owned_by_person_id => l_owned_by_person_id
318 ,p_owning_organization_id => l_owning_org_id
319 ,p_agreement_currency_code => l_agr_curr_code
320 ,p_invoice_limit_flag => l_invoice_limit_flag
321 ,p_start_date => PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE -- Bug 5522880
322 ,p_end_date => PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE -- Bug 5522880
323 ,p_advance_required => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR -- Bug 5522880
324 ,p_billing_sequence => PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM -- Bug 5522880
325 ,p_amount => PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM); -- Bug 5522880
326
327 IF l_debug_mode = 'Y' THEN
328 PA_DEBUG.write_log (x_module =>
329 'pa.plsql.pa_fp_ci_implement_pkg.create_ci_impact_fund_lines'
330 ,x_msg => 'upd agr allowed flag '||l_upd_agr_allowed
331 ,x_log_level => 5);
332 END IF;
333 IF l_upd_agr_allowed = 'N' THEN
334 x_return_status := FND_API.G_RET_STS_ERROR;
335 /* x_msg_count := FND_MSG_PUB.Count_Msg;
336 IF x_msg_count = 1 THEN
337 PA_INTERFACE_UTILS_PUB.get_messages
338 (p_encoded => FND_API.G_TRUE,
339 p_msg_index => 1,
340 p_msg_count => 1,
341 p_msg_data => l_msg_data ,
342 p_data => x_msg_data,
343 p_msg_index_out => l_msg_index_out);
344 END IF; */
345 IF l_debug_mode = 'Y' THEN
346 PA_DEBUG.Reset_Err_Stack;
347 END IF;
348 RETURN;
349 END IF;
350 /* calling update agreement API */
351 /* the update agreement API expects the existing amount plus
352 the new amount for the update bug 2671305 */
353
354 l_new_agr_amount := NVL(l_total_amount,0) + l_agr_amount;
355
356 IF l_debug_mode = 'Y' THEN
357 PA_DEBUG.write_log (x_module =>
358 'pa.plsql.pa_fp_ci_implement_pkg.create_ci_impact_fund_lines'
359 ,x_msg => 'new agr amt '||ltrim(to_char(l_new_agr_amount))
360 ||' Agr ID '||ltrim(to_char(l_agreement_id))
361 ,x_log_level => 5);
362 END IF;
363
364 pa_agreement_core.update_agreement(
365 p_Agreement_Id => l_agreement_id,
366 p_Customer_Id => l_customer_id,
367 p_Agreement_Num => l_agreement_num,
368 p_Agreement_Type => l_agreement_type,
369 p_Last_Update_Date => TRUNC(SYSDATE),
370 p_Last_Updated_By => l_last_updated_by,
371 p_Last_Update_Login => l_last_update_login,
372 p_Owned_By_Person_Id => l_owned_by_person_id,
373 p_Term_Id => l_term_id,
374 p_Revenue_Limit_Flag => l_revenue_limit_flag,
375 p_Amount => l_new_agr_amount,
376 p_Description => NULL,
377 p_Expiration_Date => l_expiration_date,
378 p_Attribute_Category => l_attribute_category,
379 p_Attribute1 => l_attribute1,
380 p_Attribute2 => l_attribute2,
381 p_Attribute3 => l_attribute3,
382 p_Attribute4 => l_attribute4,
383 p_Attribute5 => l_attribute5,
384 p_Attribute6 => l_attribute6,
385 p_Attribute7 => l_attribute7,
386 p_Attribute8 => l_attribute8,
387 p_Attribute9 => l_attribute9,
388 p_Attribute10 => l_attribute10,
389 p_Template_Flag => l_template_flag,
390 p_pm_agreement_reference => NULL,
391 p_pm_product_code => NULL,
392 p_agreement_currency_code => l_agr_curr_code,
393 p_owning_organization_id => l_owning_org_id,
394 p_invoice_limit_flag => l_invoice_limit_flag,
395 p_customer_order_number => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR, -- Bug 5522880
396 p_advance_required => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
397 p_start_date => PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
398 p_billing_sequence => PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
399 p_line_of_account => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
400 p_Attribute11 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
401 p_Attribute12 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
402 p_Attribute13 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
403 p_Attribute14 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
404 p_Attribute15 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
405 p_Attribute16 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
406 p_Attribute17 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
407 p_Attribute18 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
408 p_Attribute19 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
409 p_Attribute20 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
410 p_Attribute21 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
411 p_Attribute22 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
412 p_Attribute23 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
413 p_Attribute24 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
414 p_Attribute25 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR); -- Bug 5522880
415
416
417 END IF;
418
419 /* the following is only for testing a bug */
420 BEGIN
421 SELECT amount into l_tmp_amount
422 FROM pa_agreements_all WHERE
423 agreement_id = l_agreement_id;
424 EXCEPTION
425 WHEN OTHERS THEN
426 l_tmp_amount := 0;
427 END;
428 IF l_debug_mode = 'Y' THEN
429 PA_DEBUG.write_log (x_module =>
430 'pa.plsql.pa_fp_ci_implement_pkg.create_ci_impact_fund_lines'
431 ,x_msg => 'aft upd agr api'||ltrim(to_char(l_tmp_amount))
432 ,x_log_level => 5);
433 END IF;
434
435 /* the following is only for testing a bug */
436
437 /* check and call for agreement amount update */
438
439 /* check for validate funding amount */
440
441 l_valid_funding_amt_flag := Pa_agreement_pvt.validate_funding_amt(
442 p_funding_amt => l_total_amount,
443 p_agreement_id => l_agreement_id,
444 p_operation_flag => 'A',
445 p_funding_id => NULL,
446 p_pm_funding_reference => NULL );
447
448 IF l_debug_mode = 'Y' THEN
449 PA_DEBUG.write_log (x_module =>
450 'pa.plsql.pa_fp_ci_implement_pkg.create_ci_impact_fund_lines'
451 ,x_msg => 'aft valid fund amt api call '||l_valid_funding_amt_flag
452 ,x_log_level => 5);
453 END IF;
454
455 IF l_valid_funding_amt_flag <> 'Y' THEN
456 PA_UTILS.ADD_MESSAGE(
457 p_app_short_name => 'PA',
458 p_msg_name => 'PA_INVD_FUND_ALLOC_AMG' );
459
460 x_return_status := FND_API.G_RET_STS_ERROR;
461 /* x_msg_count := FND_MSG_PUB.Count_Msg;
462 IF x_msg_count = 1 THEN
463 PA_INTERFACE_UTILS_PUB.get_messages
464 (p_encoded => FND_API.G_TRUE,
465 p_msg_index => 1,
466 p_msg_count => 1,
467 p_msg_data => l_msg_data ,
468 p_data => x_msg_data,
469 p_msg_index_out => l_msg_index_out);
470 END IF; */
471 IF l_debug_mode = 'Y' THEN
472 PA_DEBUG.Reset_Err_Stack;
473 END IF;
474 RETURN;
475 END IF;
476
477 /* checking for project level funding */
478
479 IF ( l_ci_ver_planning_level = 'P' AND
480 l_funding_level = 'P' ) OR
481 ( l_ci_ver_planning_level = 'T' AND
482 l_funding_level = 'P' ) OR
483 ( l_ci_ver_planning_level = 'L' AND -- Bug 3755783: CI version Lowest level funding
484 l_funding_level = 'P' )THEN
485
486 l_amount_tab(1) := l_total_amount;
487 l_amount_tab_in_pfc(1) := l_total_projfunc_revenue;
488 l_amount_tab_in_pc(1) := l_total_proj_revenue;
489 l_task_id_tab(1) := NULL;
490 IF l_debug_mode = 'Y' THEN
491 PA_DEBUG.write_log (x_module =>
492 'pa.plsql.pa_fp_ci_implement_pkg.create_ci_impact_fund_lines'
493 ,x_msg => 'inside fund : ci level PP PT'
494 ,x_log_level => 5);
495 END IF;
496
497
498 ELSIF l_ci_ver_planning_level = 'T' AND
499 l_funding_level = 'T' THEN
500 IF l_debug_mode = 'Y' THEN
501 PA_DEBUG.write_log (x_module =>
502 'pa.plsql.pa_fp_ci_implement_pkg.create_ci_impact_fund_lines'
503 ,x_msg => 'inside fund : ci level TT'
504 ,x_log_level => 5);
505 END IF;
506 BEGIN
507 SELECT NVL( SUM(nvl(bl.txn_revenue,0)) , 0)*p_partial_factor,
508 NVL( SUM(nvl(bl.project_revenue,0)) , 0)*p_partial_factor,
509 NVL( SUM(nvl(bl.revenue,0)) , 0)*p_partial_factor,
510 ra.Task_id
511 BULK COLLECT INTO
512 l_amount_tab,
513 l_amount_tab_in_pc,
514 l_amount_tab_in_pfc,
515 l_task_id_tab
516 FROM pa_budget_lines bl,
517 pa_resource_assignments ra
518 WHERE
519 ra.project_id = p_project_id AND
520 ra.budget_version_id = l_budget_version_id AND
521 NVL(ra.resource_assignment_type,'USER_ENTERED') = 'USER_ENTERED' AND
522 ra.resource_assignment_id = bl.resource_Assignment_id AND
523 bl.budget_version_id = ra.budget_version_id AND
524 bl.cost_rejection_code IS NULL AND
525 bl.revenue_rejection_code IS NULL AND
526 bl.burden_rejection_code IS NULL AND
527 bl.other_rejection_code IS NULL AND
528 bl.pc_cur_conv_rejection_code IS NULL AND
529 bl.pfc_cur_conv_rejection_code IS NULL
530 GROUP BY ra.task_id HAVING NVL( SUM(nvl(bl.txn_revenue,0)) , 0) <> 0
531 ORDER BY PA_PROJ_ELEMENTS_UTILS.GET_DISPLAY_SEQUENCE(ra.task_id);
532 EXCEPTION
533 WHEN NO_DATA_FOUND THEN
534 NULL;
535 END;
536 ELSIF l_ci_ver_planning_level = 'P' AND
537 l_funding_level = 'T' THEN
538 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
539 p_msg_name => 'PA_FP_CI_FUNDING_LEVEL' );
540 x_return_status := FND_API.G_RET_STS_ERROR;
541 IF l_debug_mode = 'Y' THEN
542 PA_DEBUG.Reset_Err_Stack;
543 END IF;
544 RETURN;
545 ELSIF l_ci_ver_planning_level = 'L' AND -- Bug 3755783: FP.M change
546 l_funding_level = 'T' THEN
547 -- Rollup ci budget lines to appropriate top node and create funding lines
548 BEGIN
549 SELECT NVL( SUM(nvl(bl.txn_revenue,0)) , 0)*p_partial_factor,
550 NVL( SUM(nvl(bl.project_revenue,0)) , 0)*p_partial_factor,
551 NVL( SUM(nvl(bl.revenue,0)) , 0)*p_partial_factor,
552 pt.top_task_id
553 BULK COLLECT INTO
554 l_amount_tab,
555 l_amount_tab_in_pc,
556 l_amount_tab_in_pfc,
557 l_task_id_tab
558 FROM pa_budget_lines bl,
559 pa_resource_assignments ra,
560 pa_tasks pt
561 WHERE ra.project_id = p_project_id
562 AND ra.budget_version_id = l_budget_version_id
563 AND NVL(ra.resource_assignment_type,'USER_ENTERED') = 'USER_ENTERED'
564 AND ra.task_id = pt.task_id
565 AND ra.resource_assignment_id = bl.resource_Assignment_id
566 AND bl.budget_version_id = ra.budget_version_id
567 AND bl.cost_rejection_code IS NULL
568 AND bl.revenue_rejection_code IS NULL
569 AND bl.burden_rejection_code IS NULL
570 AND bl.other_rejection_code IS NULL
571 AND bl.pc_cur_conv_rejection_code IS NULL
572 AND bl.pfc_cur_conv_rejection_code IS NULL
573 GROUP BY pt.top_task_id HAVING NVL( SUM(nvl(bl.txn_revenue,0)) , 0) <> 0
574 ORDER BY PA_PROJ_ELEMENTS_UTILS.GET_DISPLAY_SEQUENCE(pt.top_task_id);
575 EXCEPTION
576 WHEN NO_DATA_FOUND THEN
577 NULL;
578 END;
579 END IF;
580
581 --In 3 for loops written below all the txn/pc/pfc amounts will be rounded. 3 For loops are written to take advantage
582 --of caching logic in Pa_currency.round_trans_currency_amt1
583 l_rounded_agr_sum :=0;
584 l_rounded_pc_sum :=0;
585 l_rounded_pfc_sum :=0;
586 -- Call the rounding api for all the agreement currency amounts
587 FOR i IN 1..l_task_id_tab.COUNT
588 LOOP
589 IF l_amount_tab(i) <> 0 THEN
590 l_amount_tab(i) :=
591 Pa_currency.round_trans_currency_amt1(l_amount_tab(i),
592 l_agr_curr_code);
593 END IF;
594 l_rounded_agr_sum := l_rounded_agr_sum + l_amount_tab(i);
595 END LOOP;
596
597 --Round PFC amounts
598 IF l_agr_curr_code = l_projfunc_curr_code THEN
599
600 l_amount_tab_in_pfc:=l_amount_tab;
601 l_rounded_pfc_sum := l_rounded_agr_sum;
602
603 ELSE
604
605 FOR i IN 1 .. l_task_id_tab.COUNT
606 LOOP
607 IF l_amount_tab_in_pfc(i) <> 0 THEN
608 l_amount_tab_in_pfc(i) :=
609 Pa_currency.round_trans_currency_amt1(l_amount_tab_in_pfc(i),
610 l_projfunc_curr_code);
611 l_rounded_pfc_sum := l_rounded_pfc_sum + l_amount_tab_in_pfc(i);
612 END IF;
613 END LOOP;
614
615 END IF;
616
617 --Round PC amounts
618 IF l_agr_curr_code = l_proj_curr_code THEN
619
620 l_amount_tab_in_pc:=l_amount_tab;
621 l_rounded_pc_sum:=l_rounded_agr_sum;
622
623 ELSIF l_projfunc_curr_code = l_proj_curr_code THEN
624
625 l_amount_tab_in_pc := l_amount_tab_in_pfc;
626 l_rounded_pc_sum:=l_rounded_pfc_sum;
627
628 ELSE
629
630 FOR i IN 1 .. l_task_id_tab.COUNT
631 LOOP
632 IF l_amount_tab_in_pc(i) <> 0 THEN
633 l_amount_tab_in_pc(i) :=
634 Pa_currency.round_trans_currency_amt1(l_amount_tab_in_pc(i),
635 l_proj_curr_code);
636 l_rounded_pc_sum:= l_rounded_pc_sum + l_amount_tab_in_pc(i);
637 END IF;
638 END LOOP;
639
640 END IF;
641
642 --Adjust the residual amount, if any, because of rounding into the last funding line
643 IF l_task_id_tab.COUNT >0 THEN
644
645 l_amount_tab(l_amount_tab.COUNT) := l_amount_tab(l_amount_tab.COUNT) + (l_total_amount-l_rounded_agr_sum);
646 l_amount_tab_in_pfc(l_amount_tab_in_pfc.COUNT) := l_amount_tab_in_pfc(l_amount_tab_in_pfc.COUNT)
647 +(l_total_projfunc_revenue-l_rounded_pfc_sum);
648 l_amount_tab_in_pc(l_amount_tab_in_pc.COUNT) := l_amount_tab_in_pc(l_amount_tab_in_pc.COUNT)
649 +(l_total_proj_revenue-l_rounded_pc_sum);
650
651 END IF;
652
653 FOR i IN 1 .. l_task_id_tab.COUNT LOOP
654 --Bug 6600563. Added the parameter p_calling_context to the below API call.
655 l_add_funding_ok_flag := pa_agreement_pvt.Check_add_funding_ok(
656 p_project_id => p_project_id,
657 p_task_id => l_task_id_tab(i),
658 p_agreement_id => l_agreement_id,
659 p_pm_funding_reference => NULL,
660 p_funding_amt => l_amount_tab(i),
661 p_customer_id => l_customer_id ,
662 p_calling_context => 'CI');
663
664 IF l_debug_mode = 'Y' THEN
665 PA_DEBUG.write_log (x_module =>
666 'pa.plsql.pa_fp_ci_implement_pkg.create_ci_impact_fund_lines'
667 ,x_msg => 'chk for add fund ok '||
668 ltrim(to_char(nvl(l_task_id_tab(i),-1)))
669 || ' amt '||to_char(l_amount_tab(i)) ||
670 ' flag '|| l_add_funding_ok_flag
671 ,x_log_level => 5);
672 END IF;
673
674 IF l_add_funding_ok_flag <> 'Y' THEN
675 x_return_status := FND_API.G_RET_STS_ERROR;
676 /* x_msg_count := FND_MSG_PUB.Count_Msg;
677 IF x_msg_count = 1 THEN
678 PA_INTERFACE_UTILS_PUB.get_messages
679 (p_encoded => FND_API.G_TRUE,
680 p_msg_index => 1,
681 p_msg_count => 1,
682 p_msg_data => l_msg_data ,
683 p_data => x_msg_data,
684 p_msg_index_out => l_msg_index_out);
685 END IF; */
686 IF l_debug_mode = 'Y' THEN
687 PA_DEBUG.Reset_Err_Stack;
688 END IF;
689 RETURN;
690 END IF;
691
692 IF l_debug_mode = 'Y' THEN
693 PA_DEBUG.write_log (x_module =>
694 'pa.plsql.pa_fp_ci_implement_pkg.create_ci_impact_fund_lines'
695 ,x_msg => 'bef create_funding api call'
696 ,x_log_level => 5);
697 END IF;
698
699 l_err_stage := NULL;
700 /* added for bug 2782095 */
701 l_rowid := NULL;
702 l_project_funding_id := NULL;
703 /* added for bug 2782095 */
704
705 -- Bug 6772321
706 IF l_amount_tab(i) <> 0 THEN
707 l_project_exchange_rate := l_amount_tab_in_pc(i)/l_amount_tab(i);
708 l_projfunc_exchange_rate := l_amount_tab_in_pfc(i)/l_amount_tab(i);
709 ELSE
710 l_project_exchange_rate := NULL;
711 l_projfunc_exchange_rate := NULL;
712 END IF;
713
714 pa_funding_core.create_funding_CO(
715 p_Rowid => l_rowid,
716 p_Project_Funding_Id => l_project_funding_id,
717 p_Last_Update_Date => l_sysdate,
718 p_Last_Updated_By => l_last_updated_by,
719 p_Creation_Date => l_sysdate,
720 p_Created_By => l_last_updated_by,
721 p_Last_Update_Login => l_last_update_login,
722 p_Agreement_Id => l_agreement_id,
723 p_Project_Id => p_project_id,
724 p_Task_id => l_task_id_tab(i),
725 p_Budget_Type_Code => 'DRAFT',
726 p_Allocated_Amount => l_amount_tab(i),
727 p_Date_Allocated => l_sysdate,
728 P_Funding_Currency_Code => l_agr_curr_code,
729 p_Control_Item_ID => p_ci_id,
730 p_Attribute_Category => NULL,
731 p_Attribute1 => NULL,
732 p_Attribute2 => NULL,
733 p_Attribute3 => NULL,
734 p_Attribute4 => NULL,
735 p_Attribute5 => NULL,
736 p_Attribute6 => NULL,
737 p_Attribute7 => NULL,
738 p_Attribute8 => NULL,
739 p_Attribute9 => NULL,
740 p_Attribute10 => NULL,
741 p_pm_funding_reference => NULL,
742 p_pm_product_code => NULL,
743 p_Project_Allocated_Amount => l_amount_tab_in_pc(i),
744 p_project_rate_type => 'User',
745 p_project_rate_date => NULL,
746 --p_project_exchange_rate => l_amount_tab_in_pc(i)/l_amount_tab(i),
747 p_project_exchange_rate => l_project_exchange_rate, --Bug 6772321
748 p_Projfunc_Allocated_Amount => l_amount_tab_in_pfc(i),
749 p_projfunc_rate_type => 'User',
750 p_projfunc_rate_date => NULL,
751 --p_projfunc_exchange_rate => l_amount_tab_in_pfc(i)/l_amount_tab(i),
752 p_projfunc_exchange_rate => l_projfunc_exchange_rate, --Bug 6772321
753 x_err_code => l_err_code,
754 x_err_msg => l_err_stage,
755 p_funding_category => p_funding_category );
756
757 IF l_debug_mode = 'Y' THEN
758 PA_DEBUG.write_log (x_module =>
759 'pa.plsql.pa_fp_ci_implement_pkg.create_ci_impact_fund_lines'
760 ,x_msg => 'aft create_funding api call ret code '||
761 to_char(l_err_code)
762 ,x_log_level => 5);
763 END IF;
764
765 IF (l_err_code <> 0) THEN
766 PA_UTILS.ADD_MESSAGE(
767 p_app_short_name => 'PA',
768 p_msg_name => l_err_stage );
769 x_return_status := FND_API.G_RET_STS_ERROR;
770 /* x_msg_count := FND_MSG_PUB.Count_Msg;
771 IF x_msg_count = 1 THEN
772 PA_INTERFACE_UTILS_PUB.get_messages
773 (p_encoded => FND_API.G_TRUE,
774 p_msg_index => 1,
775 p_msg_count => 1,
776 p_msg_data => l_msg_data ,
777 p_data => x_msg_data,
778 p_msg_index_out => l_msg_index_out);
779 END IF; */
780 IF l_debug_mode = 'Y' THEN
781 PA_DEBUG.Reset_Err_Stack;
782 END IF;
783 RETURN;
784 END IF;
785
786 pa_agreement_utils.summary_funding_insert_row(
787 p_agreement_id => l_agreement_id,
788 p_project_id => p_project_id,
789 p_task_id => l_task_id_tab(i),
790 p_login_id => LTRIM(RTRIM(TO_CHAR(l_last_update_login))),
791 p_user_id => LTRIM(RTRIM(TO_CHAR(l_last_updated_by)))
792 );
793 IF l_debug_mode = 'Y' THEN
794 PA_DEBUG.write_log (x_module =>
795 'pa.plsql.pa_fp_ci_implement_pkg.create_ci_impact_fund_lines'
796 ,x_msg => 'aft calling summary fund ins API '
797 ,x_log_level => 5);
798 END IF;
799 END LOOP;
800 /* FP.M- The following call to the api has been commented as this
801 * api spec has undergone changes and the calling api would not be
802 * called at all
803 */
804 /*PA_FP_CI_MERGE.FP_CI_LINK_CONTROL_ITEMS(
805 p_project_id => p_project_id,
806 p_s_fp_version_id => l_budget_version_id,
807 p_t_fp_version_id => l_bv_id,
808 p_inclusion_method => 'AUTOMATIC',
809 p_included_by => NULL,
810 x_return_status => x_return_status,
811 x_msg_count => x_msg_count,
812 x_msg_data => x_msg_data
813 );
814
815 IF l_debug_mode = 'Y' THEN
816 PA_DEBUG.write_log (x_module =>
817 'pa.plsql.pa_fp_ci_implement_pkg.create_ci_impact_fund_lines'
818 ,x_msg => 'aft calling link api : ret status '||
819 x_return_status
820 ,x_log_level => 5);
821 END IF;
822 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
823 IF l_debug_mode = 'Y' THEN
824 PA_DEBUG.Reset_Err_Stack;
825 END IF;
826 RETURN;
827 END IF;
828 /* checking for project level funding */
829 END IF; --Bug 5509687
830 /* PA_DEBUG.Reset_Err_Stack; */
831 IF l_debug_mode = 'Y' THEN
832 PA_DEBUG.Reset_Err_Stack;
833 END IF;
834 RETURN;
835 EXCEPTION
836 WHEN OTHERS THEN
837 ROLLBACK;
838
839 IF l_debug_mode = 'Y' THEN
840 PA_DEBUG.Reset_Err_Stack;
841 END IF;
842
843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
844 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_FP_CI_IMPLEMENT_PKG',
845 p_procedure_name => 'CREATE_CI_IMPACT_FUND_LINES',
846 p_error_text => SUBSTRB(SQLERRM,1,240));
847
848 fnd_msg_pub.count_and_get(p_count => x_msg_count,
849 p_data => x_msg_data);
850
851
852 END create_ci_impact_fund_lines;
853
854
855
856 /* bug 2735741 this API returns the appropriate error msg when the
857 budget version passed is in Submitted status or
858 locked by a different user. If the lock is held by
859 the current login user and the version is not in Submitted status,
860 then the implementation of the change order is allowed . */
861
862
863 PROCEDURE chk_plan_ver_for_merge
864 (
865 p_project_id IN NUMBER,
866 p_target_fp_version_id_tbl IN PA_PLSQL_DATATYPES.IdTabTyp,
867 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
868 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
869 x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
870 ) IS
871 l_budget_status_code pa_budget_versions.budget_status_code%TYPE;
872 l_locked_by_person_id pa_budget_versions.locked_by_person_id%TYPE;
873 l_version_type pa_budget_versions.version_type%TYPE;
874 l_meaning pa_lookups.meaning%TYPE;
875 l_person_id NUMBER;
876 l_resource_id NUMBER;
877 l_resource_name VARCHAR2(200);
878 l_user_id NUMBER;
879 l_chk_flag VARCHAR2(1);
880 /* l_chk_flag is used to display only one error for the
881 target version.either version in Submitted status or version locked by
882 another user. In this case, error for Submit status takes precedence
883 over the lock error. */
884 l_locked_by_name per_people_x.full_name%TYPE;
885 l_request_id NUMBER;
886 l_plan_proc_code pa_budget_versions.plan_processing_code%TYPE;
887 l_refresh_required_flag VARCHAR2(1);
888 l_request_id_v VARCHAR2(100);
889 l_url_text VARCHAR2(500);
890 l_return_status VARCHAR2(30);
891 l_wbs_update_flag VARCHAR2(1);
892 /* l_wbs_update_flag is used to display the error only one time,
893 if the Cost and Revenue amounts are planned separately and
894 both the target versions are undergoing WBS process update
895 changes. */
896 BEGIN
897 x_return_status := FND_API.G_RET_STS_SUCCESS;
898
899 l_user_id := FND_GLOBAL.USER_ID;
900 l_wbs_update_flag := 'Y';
901
902 PA_COMP_PROFILE_PUB.GET_USER_INFO
903 (p_user_id => l_user_id,
904 x_person_id => l_person_id,
905 x_resource_id => l_resource_id,
906 x_resource_name => l_resource_name);
907
908 FOR i IN 1 .. p_target_fp_version_id_tbl.COUNT LOOP
909 l_chk_flag := 'Y';
910 SELECT budget_status_code,
911 locked_by_person_id,
912 version_type,
913 NVL(request_id,0),
914 NVL(plan_processing_code,'ABC'),
915 NVL(process_update_wbs_flag,'N')
916 INTO
917 l_budget_status_code,
918 l_locked_by_person_id,
919 l_version_type,
920 l_request_id,
921 l_plan_proc_code,
922 l_refresh_required_flag
923 FROM pa_budget_versions WHERE
924 budget_version_id = p_target_fp_version_id_tbl(i);
925
926 /* code added for Patchset L */
927 /* We are not calling the API pa_fp_refresh_elements_pub.get_refresh_plan_ele_dtls
928 for getting the status details as we are already
929 getting information from pa_budget_versions table. */
930
931 IF l_plan_proc_code = 'WUP' AND l_wbs_update_flag = 'Y' THEN
932 x_return_status := FND_API.G_RET_STS_ERROR;
933 l_chk_flag := 'N';
934 l_wbs_update_flag := 'N';
935 IF l_request_id IS NOT NULL THEN
936 l_request_id_v := LTRIM(RTRIM(TO_CHAR(l_request_id)));
937 END IF;
938 l_url_text := 'OA.jsp?akRegionCode=FNDCPREQUESTVIEWREGION';
939 l_url_text := l_url_text || '&akRegionApplicationId=0';
940 l_url_text := l_url_text || '&progApplShortName=PA&progShortName=PAWPUWBS';
941 l_url_text := l_url_text || '&requestId=' || l_request_id_v;
942
943 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
944 p_msg_name => 'PA_FP_MERGE_WBS_UPD',
945 p_token1 => 'URLTXT',
946 p_value1 => l_url_text );
947 END IF;
948 /* code added for Patchset L */
949
950 /* checking for Submitted status */
951
952 IF l_budget_status_code = 'S' AND l_chk_flag = 'Y' THEN
953 l_chk_flag := 'N';
954 x_return_status := FND_API.G_RET_STS_ERROR;
955 IF l_version_type = 'ALL' THEN
956 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
957 p_msg_name => 'PA_FP_MERGE_ALL_SUBMIT');
958 ELSE
959 BEGIN
960 SELECT meaning
961 INTO l_meaning
962 FROM pa_lookups
963 WHERE lookup_type = 'FIN_PLAN_VER_TYPE'
964 AND lookup_code = l_version_type;
965 EXCEPTION
966 WHEN NO_DATA_FOUND THEN
967 l_meaning := NULL;
968 END;
969
970 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
971 p_msg_name => 'PA_FP_MERGE_SUBMIT',
972 p_token1 => 'VERTYPE',
973 p_value1 => l_meaning );
974 END IF;
975 END IF;
976
977 /* checking for lock. If the version is in Submitted status, the msg for
978 the Lock should not be displayed, even though the locked user id is
979 different. l_chk_flag is used to avoid the lock err msg in this case. */
980
981 l_locked_by_name := NULL;
982
983 IF l_locked_by_person_id IS NOT NULL AND
984 l_locked_by_person_id <> l_person_id AND
985 l_chk_flag = 'Y' THEN
986 l_locked_by_name := pa_fin_plan_utils.get_person_name(l_locked_by_person_id );
987 x_return_status := FND_API.G_RET_STS_ERROR;
988 IF l_version_type = 'ALL' THEN
989 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
990 p_msg_name => 'PA_FP_MERGE_ALL_LCK',
991 p_token1 => 'LOCKBY',
992 p_value1 => l_locked_by_name );
993 ELSE
994 BEGIN
995 SELECT meaning
996 INTO l_meaning
997 FROM pa_lookups
998 WHERE lookup_type = 'FIN_PLAN_VER_TYPE'
999 AND lookup_code = l_version_type;
1000 EXCEPTION
1001 WHEN NO_DATA_FOUND THEN
1002 l_meaning := NULL;
1003 END;
1004
1005 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1006 p_msg_name => 'PA_FP_MERGE_LCK',
1007 p_token1 => 'VERTYPE',
1008 p_value1 => l_meaning,
1009 p_token2 => 'LOCKBY',
1010 p_value2 => l_locked_by_name );
1011 END IF;
1012 END IF;
1013 END LOOP;
1014
1015
1016 EXCEPTION
1017 WHEN OTHERS THEN
1018
1019 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1020 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_FP_CI_IMPLEMENT_PKG',
1021 p_procedure_name => 'CHK_PLAN_VER_FOR_MERGE',
1022 p_error_text => SUBSTRB(SQLERRM,1,240));
1023
1024 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1025 p_data => x_msg_data);
1026
1027 END chk_plan_ver_for_merge;
1028
1029 END pa_fp_ci_implement_pkg;