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