[Home] [Help]
PACKAGE BODY: APPS.PA_FP_CONTROL_ITEMS_IMPACT_PKG
Source
1 PACKAGE BODY Pa_Fp_Control_Items_Impact_Pkg AS
2 /* $Header: PAFPCIIB.pls 120.4.12010000.3 2008/11/18 15:41:06 bnoorbha ship $ */
3 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4 PROCEDURE Maintain_Ctrl_Item_Version(
5 p_project_id IN Pa_Projects_All.Project_Id%TYPE,
6 p_ci_id IN NUMBER,
7 p_fp_pref_code IN VARCHAR2,
8 p_fin_plan_type_id_cost IN NUMBER,
9 p_fin_plan_type_id_rev IN NUMBER,
10 p_fin_plan_type_id_all IN NUMBER,
11 p_est_proj_raw_cost IN NUMBER,
12 p_est_proj_bd_cost IN NUMBER,
13 p_est_proj_revenue IN NUMBER,
14 p_est_qty IN NUMBER,
15 p_est_equip_qty IN NUMBER, -- FP.M
16 p_button_pressed_from_page IN VARCHAR2,
17 p_impacted_task_id IN NUMBER ,
18 p_agreement_id IN NUMBER DEFAULT NULL,
19 p_agreement_number IN VARCHAR2 DEFAULT NULL,
20 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
21 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
22 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
23 x_plan_version_id OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
24 ) IS
25 l_fin_plan_type_id NUMBER;
26 l_count NUMBER;
27 l_bv_id VARCHAR2(50);
28 l_bv_id_cost NUMBER;
29 l_bv_id_rev NUMBER;
30 l_bv_id_all NUMBER;
31 l_element_type pa_budget_versions.version_type%TYPE;
32 l_plan_version_id NUMBER;
33 l_proj_fp_option_id NUMBER;
34 l_proj_curr_code pa_projects_all.project_currency_code%TYPE;
35 l_projfunc_curr_code pa_projects_all.projfunc_currency_code%TYPE;
36 l_est_projfunc_raw_cost pa_budget_versions.est_projfunc_raw_cost%TYPE;
37 l_est_projfunc_bd_cost pa_budget_versions.est_projfunc_burdened_cost%TYPE;
38 l_est_projfunc_revenue pa_budget_versions.est_projfunc_revenue%TYPE;
39 l_no_of_app_plan_types NUMBER;
40 l_message_name fnd_new_messages.message_name%TYPE;
41 l_msg_data VARCHAR2(1000);
42 l_data VARCHAR2(1000);
43 l_msg_index_out NUMBER:=0;
44 l_msg_count NUMBER;
45 l_ci_impact_id NUMBER;
46 l_baseline_funding_flag pa_projects_all.baseline_funding_flag%TYPE;
47 l_agreement_id PA_AGREEMENTS_ALL.Agreement_Id%TYPE;
48 l_impact_type_code pa_ci_impacts.impact_type_code%TYPE;
49 l_est_proj_raw_cost pa_budget_versions.est_project_raw_cost%TYPE;
50 l_est_proj_bd_cost pa_budget_versions.est_project_burdened_cost%TYPE;
51 l_est_qty pa_budget_versions.est_quantity%TYPE;
52 l_est_equip_qty pa_budget_versions.est_equipment_quantity%TYPE;
53 l_est_proj_revenue pa_budget_versions.est_project_revenue%TYPE;
54
55 l_approved_cost_plan_type_flag Pa_Proj_Fp_Options.APPROVED_COST_PLAN_TYPE_FLAG%TYPE; -- Added for bug 4907408
56
57 CURSOR est_amt_csr
58 IS
59 SELECT est_project_raw_cost,
60 est_project_burdened_cost,
61 est_quantity,
62 est_equipment_quantity,
63 est_project_revenue,
64 version_type,
65 agreement_id
66 FROM pa_budget_versions
67 WHERE project_id=p_project_id
68 AND ci_id=p_ci_id;
69 est_amt_rec est_amt_csr%ROWTYPE;
70 est_amt_rec_tmp est_amt_csr%ROWTYPE;
71
72 --Bug 7497389
73 l_cost_ci_plan_version_id pa_budget_versions.budget_version_id%TYPE;
74 l_rev_ci_plan_version_id pa_budget_versions.budget_version_id%TYPE;
75 BEGIN
76 FND_MSG_PUB.initialize;
77 x_return_status := FND_API.G_RET_STS_SUCCESS;
78 l_agreement_id := p_agreement_id;
79 IF p_pa_debug_mode = 'Y' THEN
80 PA_DEBUG.init_err_stack('p_button_pressed_from_page '||p_button_pressed_from_page);
81 END IF;
82
83 IF p_pa_debug_mode = 'Y' THEN
84 pa_debug.g_err_stage:= 'Entering Maintain_Ctrl_Item_Version';
85 pa_debug.write('Maintain_Ctrl_Item_Version',pa_debUg.g_err_stage,3);
86
87 pa_debug.g_err_stage:= 'Project_id:'|| p_project_id;
88 pa_debug.write('Maintain_Ctrl_Item_Version',pa_debug.g_err_stage,3);
89
90 pa_debug.g_err_stage:= 'p_ci_id:'|| p_ci_id;
91 pa_debug.write('Maintain_Ctrl_Item_Version',pa_debug.g_err_stage,3);
92 pa_debug.g_err_stage:= 'p_agreement_id:' || p_agreement_id;
93 pa_debug.write('Maintain_Ctrl_Item_Version',pa_debug.g_err_stage,3);
94 pa_debug.g_err_stage:= 'p_agreement_number:'|| p_agreement_number;
95 pa_debug.write('Maintain_Ctrl_Item_Version',pa_debug.g_err_stage,3);
96 END IF;
97
98 SELECT COUNT(*) INTO l_no_of_app_plan_types FROM Pa_Proj_Fp_Options
99 WHERE
100 Project_Id = p_project_id AND
101 Fin_Plan_Option_Level_Code = 'PLAN_TYPE' AND
102 ( NVL(Approved_Cost_Plan_Type_Flag ,'N') = 'Y' OR
103 NVL(Approved_Rev_Plan_Type_Flag ,'N') = 'Y' ) ;
104
105
106 /* Added the if block for bug 4907408
107 To get plan type attached to the project*/
108 /* Bug 5210100: PQE 11i bug fix porting. Re-structuring the follwoing
109 * query to get whether an approved cost plan type is attached.
110 *
111 *IF l_no_of_app_plan_types = 1 THEN
112 * SELECT approved_cost_plan_type_flag
113 * INTO l_approved_cost_plan_type_flag
114 * FROM pa_proj_fp_options
115 * WHERE project_id = p_project_id
116 * AND fin_plan_option_level_code = 'PLAN_TYPE'
117 * AND ( NVL(approved_cost_plan_type_flag,'N') = 'Y'
118 * OR
119 * NVL(approved_rev_plan_type_flag,'N') = 'Y');
120 *END IF; -- bug 4907408
121 */
122 BEGIN
123 SELECT 'Y'
124 INTO l_approved_cost_plan_type_flag
125 FROM DUAL
126 WHERE EXISTS (SELECT 'X'
127 FROM pa_proj_fp_options
128 WHERE project_id = p_project_id
129 AND fin_plan_option_level_code = 'PLAN_TYPE'
130 AND approved_cost_plan_type_flag = 'Y');
131 EXCEPTION
132 WHEN NO_DATA_FOUND THEN
133 l_approved_cost_plan_type_flag := 'N';
134 END;
135 /* End of Addition for bug 5210100 */
136
137
138 IF l_no_of_app_plan_types = 0 THEN
139 IF p_pa_debug_mode = 'Y' THEN
140 PA_DEBUG.write_log (x_module =>
141 'pa.plsql.Pa_Fp_Control_Items_Impact_Pkg.Maintain_Ctrl_Item_Version.approved_plan_types'
142 ,x_msg => 'no of approved plan types is zero'
143 ,x_log_level => 5);
144 END IF;
145
146 x_return_status := FND_API.G_RET_STS_ERROR;
147 IF p_button_pressed_from_page = 'COST_EDIT' THEN
148 l_message_name := 'PA_FP_CI_NO_APP_COST_PLAN';
149 END IF;
150 IF p_button_pressed_from_page = 'REVENUE_EDIT' THEN
151 l_message_name := 'PA_FP_CI_NO_APP_REV_PLAN';
152 END IF;
153 IF p_button_pressed_from_page = 'FROM_ACTION_LIST' OR
154 p_button_pressed_from_page = 'ALL_EDIT' THEN
155 l_message_name := 'PA_FP_CI_NO_APP_PLAN_TYPE';
156 END IF;
157 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
158 p_msg_name => l_message_name );
159 x_msg_count := fnd_msg_pub.count_msg;
160 IF x_msg_count = 1 THEN
161 PA_INTERFACE_UTILS_PUB.Get_Messages (
162 p_encoded => FND_API.G_TRUE,
163 p_msg_index => 1,
164 p_msg_count => 1 ,
165 p_msg_data => l_msg_data ,
166 p_data => x_msg_data,
167 p_msg_index_out => l_msg_index_out );
168 -- x_msg_data := l_data;
169 END IF;
170 IF p_pa_debug_mode = 'Y' THEN
171 PA_DEBUG.Reset_Err_stack;
172 END IF;
173 RETURN;
174 END IF;
175
176 SELECT Project_Currency_Code,
177 Projfunc_Currency_Code,
178 NVL(Baseline_Funding_Flag,'N') INTO
179 l_proj_curr_code,
180 l_projfunc_curr_code,
181 l_baseline_Funding_flag
182 FROM Pa_Projects_All
183 WHERE
184 Project_Id = p_project_id;
185
186 /* Bug 3799500: rounding the quantities, if they are not null
187 */
188 IF p_est_qty IS NOT NULL THEN
189 l_est_qty := ROUND(p_est_qty, 5);
190 END IF;
191
192 IF p_est_equip_qty IS NOT NULL THEN
193 l_est_equip_qty := ROUND(p_est_equip_qty, 5);
194 END IF;
195
196 --Select the existing estimated amounts for the ci_id. If they are created/modified only then
197 --the maintain_plan_version/create ci impact apis should be called
198 OPEN est_amt_csr;
199 LOOP
200 FETCH est_amt_csr INTO est_amt_rec_tmp;
201 EXIT WHEN est_amt_csr%NOTFOUND;
202 IF est_amt_rec_tmp.version_type IN ('ALL','COST') THEN
203
204 est_amt_rec.est_project_raw_cost:=est_amt_rec_tmp.est_project_raw_cost;
205 est_amt_rec.est_project_burdened_cost:=est_amt_rec_tmp.est_project_burdened_cost;
206 est_amt_rec.est_quantity:=est_amt_rec_tmp.est_quantity;
207 est_amt_rec.est_equipment_quantity:=est_amt_rec_tmp.est_equipment_quantity;
208 END IF;
209 IF est_amt_rec_tmp.version_type IN ('ALL','REVENUE') THEN
210 est_amt_rec.est_project_revenue:= est_amt_rec_tmp.est_project_revenue;
211 est_amt_rec.agreement_id:= est_amt_rec_tmp.agreement_id;
212 END IF;
213
214 END LOOP;
215 CLOSE est_amt_csr;
216
217 IF p_button_pressed_from_page = 'COST_EDIT' OR
218 p_button_pressed_from_page = 'REVENUE_EDIT' OR
219 p_button_pressed_from_page = 'ALL_EDIT' OR
220 NVL(p_est_proj_raw_cost,0) <> NVL(est_amt_rec.est_project_raw_cost,0) OR
221 NVL(p_est_proj_bd_cost,0) <> NVL(est_amt_rec.est_project_burdened_cost,0) OR
222 NVL(l_est_qty,0) <> NVL(est_amt_rec.est_quantity,0) OR
223 NVL(l_est_equip_qty,0) <> NVL(est_amt_rec.est_equipment_quantity,0) OR
224 NVL(p_agreement_id,0) <> NVL(est_amt_rec.agreement_id,0) OR --Impact should be created even when only agreement is entered and saved
225 NVL(p_est_proj_revenue,0) <> NVL(est_amt_rec.est_project_revenue,0)THEN
226 IF p_pa_debug_mode = 'Y' THEN
227 PA_DEBUG.write_log (x_module =>
228 'pa.plsql.Pa_Fp_Control_Items_Impact_Pkg.Maintain_Ctrl_Item_Version.check_for_fp_impact'
229 ,x_msg => 'inside edit buttons check'
230 ,x_log_level => 5);
231 END IF;
232 /* FP.M -The following function call is used to get the impact information
233 */
234 l_impact_type_code := Pa_Fp_Control_Items_Utils.is_impact_exists(p_ci_id);
235 IF p_pa_debug_mode = 'Y' THEN
236 PA_DEBUG.write_log (x_module => 'pa.plsql.Pa_Fp_Control_Items_Impact_Pkg.Maintain_Ctrl_Item_Version.calling_ci_impact_pkg'
237 ,x_msg => 'calling create ci impact '
238 ,x_log_level => 5);
239 END IF;
240 /* FP.M - Apart from the record with Impact_type_code of FINPLAN
241 * one more record with Impact_type_code either finplan_cost or
242 * finplan_revenue or two records with these two values would be created
243 */
244
245 IF l_impact_type_code NOT IN ('COST','BOTH') AND
246 NVL(l_approved_cost_plan_type_flag,'N') = 'Y' AND -- Added for bug 5210100
247 (p_button_pressed_from_page = 'COST_EDIT' OR
248 p_button_pressed_from_page = 'ALL_EDIT' OR
249 NVL(p_est_proj_raw_cost,0) <> NVL(est_amt_rec.est_project_raw_cost,0) OR
250 NVL(p_est_proj_bd_cost,0) <> NVL(est_amt_rec.est_project_burdened_cost,0) OR
251 NVL(l_est_qty,0) <> NVL(est_amt_rec.est_quantity,0) OR
252 (NVL(p_agreement_id,0) <> NVL(est_amt_rec.agreement_id,0) AND l_no_of_app_plan_types = 1 -- Modified for bug 5210100
253 AND p_fp_pref_code = 'COST_AND_REV_SAME') OR -- Modified for bug 6119004
254 NVL(l_est_equip_qty,0) <> NVL(est_amt_rec.est_equipment_quantity,0)
255 )THEN
256 PA_CI_IMPACTS_pub.create_ci_impact(
257 p_ci_id => p_ci_id,
258 p_impact_type_code => 'FINPLAN_COST',
259 p_status_code => 'CI_IMPACT_PENDING',
260 p_commit => 'F',
261 p_validate_only => 'F',
262 p_description => NULL,
263 p_implementation_comment => NULL,
264 x_ci_impact_id => l_ci_impact_id,
265 x_return_status => x_return_status,
266 x_msg_count => x_msg_count,
267 x_msg_data =>x_msg_data
268 );
269 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
270 x_msg_count := fnd_msg_pub.count_msg;
271 IF x_msg_count = 1 THEN
272 PA_INTERFACE_UTILS_PUB.Get_Messages (
273 p_encoded => FND_API.G_TRUE,
274 p_msg_index => 1,
275 p_msg_count => 1 ,
276 p_msg_data => l_msg_data ,
277 p_data => x_msg_data,
278 p_msg_index_out => l_msg_index_out );
279 END IF;
280 IF p_pa_debug_mode = 'Y' THEN
281 PA_DEBUG.Reset_Err_stack;
282 END IF;
283 RETURN;
284 END IF;
285 END IF;
286 IF l_impact_type_code NOT IN ('REVENUE','BOTH') AND
287 (p_button_pressed_from_page = 'REVENUE_EDIT' OR
288 p_button_pressed_from_page = 'ALL_EDIT' OR
289 NVL(p_agreement_id,0) <> NVL(est_amt_rec.agreement_id,0) OR
290 NVL(p_est_proj_revenue,0) <> NVL(est_amt_rec.est_project_revenue,0)) THEN
291 PA_CI_IMPACTS_pub.create_ci_impact(
292 p_ci_id => p_ci_id,
293 p_impact_type_code => 'FINPLAN_REVENUE',
294 p_status_code => 'CI_IMPACT_PENDING',
295 p_commit => 'F',
296 p_validate_only => 'F',
297 p_description => NULL,
298 p_implementation_comment => NULL,
299 x_ci_impact_id => l_ci_impact_id,
300 x_return_status => x_return_status,
301 x_msg_count => x_msg_count,
302 x_msg_data =>x_msg_data
303 );
304 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
305 x_msg_count := fnd_msg_pub.count_msg;
306 IF x_msg_count = 1 THEN
307 PA_INTERFACE_UTILS_PUB.Get_Messages (
308 p_encoded => FND_API.G_TRUE,
309 p_msg_index => 1,
310 p_msg_count => 1 ,
311 p_msg_data => l_msg_data ,
312 p_data => x_msg_data,
313 p_msg_index_out => l_msg_index_out );
314 END IF;
315 IF p_pa_debug_mode = 'Y' THEN
316 PA_DEBUG.Reset_Err_stack;
317 END IF;
318 RETURN;
319 END IF;
320 END IF;
321 END IF;
322
323 IF l_no_of_app_plan_types = 1 AND
324 ( p_fp_pref_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY OR
325 p_fp_pref_code = PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY OR
326 p_fp_pref_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME ) AND
327 (p_button_pressed_from_page = 'REVENUE_EDIT' OR
328 p_button_pressed_from_page = 'ALL_EDIT' OR
329 p_button_pressed_from_page = 'COST_EDIT' OR
330 NVL(p_agreement_id,0) <> NVL(est_amt_rec.agreement_id,0) OR
331 NVL(p_est_proj_raw_cost,0) <> NVL(est_amt_rec.est_project_raw_cost,0) OR
332 NVL(p_est_proj_bd_cost,0) <> NVL(est_amt_rec.est_project_burdened_cost,0) OR
333 NVL(l_est_qty,0) <> NVL(est_amt_rec.est_quantity,0) OR
334 NVL(l_est_equip_qty,0) <> NVL(est_amt_rec.est_equipment_quantity,0) OR
335 NVL(p_est_proj_revenue,0) <> NVL(est_amt_rec.est_project_revenue,0))THEN
336
337
338 IF p_fp_pref_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY THEN
339 l_fin_plan_type_id := p_fin_plan_type_id_cost;
340 l_element_type := PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST;
341 ELSIF p_fp_pref_code = PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY THEN
342 l_fin_plan_type_id := p_fin_plan_type_id_rev;
343 l_element_type := PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE;
344 ELSIF p_fp_pref_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME THEN
345 l_fin_plan_type_id := p_fin_plan_type_id_all;
346 l_element_type := PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL;
347 END IF;
348 /* ELSIF p_fp_pref_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP THEN
349 l_fin_plan_type_id := p_fin_plan_type_id_all;
350 l_element_type := PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL; */
351
352 IF l_element_type IN (PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL) AND
353 p_agreement_id IS NULL THEN
354
355 Pa_Fp_Control_Items_Utils.IsValidAgreement(
356 p_project_id => p_project_id,
357 p_agreement_number => p_agreement_number,
358 x_agreement_id => l_agreement_id,
359 x_msg_count => x_msg_count,
360 x_msg_data => x_msg_data,
361 x_return_status => x_return_status );
362 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
363 x_msg_count := fnd_msg_pub.count_msg;
364 IF x_msg_count = 1 THEN
365 PA_INTERFACE_UTILS_PUB.Get_Messages (
366 p_encoded => FND_API.G_TRUE,
367 p_msg_index => 1,
368 p_msg_count => 1 ,
369 p_msg_data => l_msg_data ,
370 p_data => x_msg_data,
371 p_msg_index_out => l_msg_index_out );
372 END IF;
373 IF p_pa_debug_mode = 'Y' THEN
374 PA_DEBUG.Reset_Err_stack;
375 END IF;
376 RETURN;
377 END IF;
378 END IF;
379
380 Maintain_Plan_Version(
381 p_project_id => p_project_id,
382 p_ci_id => p_ci_id,
383 p_fp_pref_code => p_fp_pref_code,
384 p_fin_plan_type_id => l_fin_plan_type_id,
385 p_est_proj_raw_cost => p_est_proj_raw_cost,
386 p_est_proj_bd_cost => p_est_proj_bd_cost,
387 p_est_proj_revenue => p_est_proj_revenue,
388 p_est_qty => l_est_qty,
389 p_est_equip_qty => l_est_equip_qty,
390 x_return_status => x_return_status,
391 x_msg_count => x_msg_count,
392 x_msg_data => x_msg_data,
393 p_project_currency_Code => l_proj_curr_code,
394 p_projfunc_currency_code => l_projfunc_curr_code,
395 p_element_type => l_element_type,
396 x_plan_version_id => l_bv_id ,
397 p_impacted_task_id => p_impacted_task_id,
398 p_agreement_id => l_agreement_id,
399 p_agreement_number => p_agreement_number,
400 p_baseline_funding_flag => l_baseline_funding_flag);
401
402 x_plan_version_id := ltrim(rtrim(l_bv_id));
403
404 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
405 x_msg_count := fnd_msg_pub.count_msg;
406 IF x_msg_count = 1 THEN
407 PA_INTERFACE_UTILS_PUB.Get_Messages (
408 p_encoded => FND_API.G_TRUE,
409 p_msg_index => 1,
410 p_msg_count => 1 ,
411 p_msg_data => l_msg_data ,
412 p_data => x_msg_data,
413 p_msg_index_out => l_msg_index_out );
414 -- x_msg_data := l_data;
415 END IF;
416 IF p_pa_debug_mode = 'Y' THEN
417 PA_DEBUG.Reset_Err_stack;
418 END IF;
419 RETURN;
420 END IF;
421 ELSIF l_no_of_app_plan_types = 2 OR
422 p_fp_pref_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP THEN
423
424 /* Calling the create api with the proper version type */
425 IF p_button_pressed_from_page = 'COST_EDIT' OR
426 NVL(p_est_proj_raw_cost,0) <> NVL(est_amt_rec.est_project_raw_cost,0) OR
427 NVL(p_est_proj_bd_cost,0) <> NVL(est_amt_rec.est_project_burdened_cost,0) OR
428 NVL(l_est_qty,0) <> NVL(est_amt_rec.est_quantity,0) OR
429 NVL(l_est_equip_qty,0) <> NVL(est_amt_rec.est_equipment_quantity,0) THEN
430
431 IF l_no_of_app_plan_types=2 THEN
432 l_fin_plan_type_id := p_fin_plan_type_id_cost;
433 ELSE
434 l_fin_plan_type_id := p_fin_plan_type_id_all;
435 END IF;
436 l_element_type := PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST;
437
438 Maintain_Plan_Version(
439 p_project_id => p_project_id,
440 p_ci_id => p_ci_id,
441 p_fp_pref_code => p_fp_pref_code,
442 p_fin_plan_type_id => l_fin_plan_type_id,
443 p_est_proj_raw_cost => p_est_proj_raw_cost,
444 p_est_proj_bd_cost => p_est_proj_bd_cost,
445 p_est_proj_revenue => NULL,
446 p_est_qty => l_est_qty,
447 p_est_equip_qty => l_est_equip_qty,
448 x_return_status => x_return_status,
449 x_msg_count => x_msg_count,
450 x_msg_data => x_msg_data,
451 p_project_currency_Code => l_proj_curr_code,
452 p_projfunc_currency_code => l_projfunc_curr_code,
453 p_element_type => l_element_type,
454 x_plan_version_id => l_bv_id ,
455 p_impacted_task_id => p_impacted_task_id,
456 p_baseline_funding_flag => l_baseline_funding_flag);
457
458 x_plan_version_id := ltrim(rtrim(l_bv_id));
459 --Bug 7497389
460 l_cost_ci_plan_version_id:=x_plan_version_id;
461 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
462 x_msg_count := fnd_msg_pub.count_msg;
463 IF x_msg_count = 1 THEN
464 PA_INTERFACE_UTILS_PUB.Get_Messages (
465 p_encoded => FND_API.G_TRUE,
466 p_msg_index => 1,
467 p_msg_count => 1 ,
468 p_msg_data => l_msg_data ,
469 p_data => x_msg_data,
470 p_msg_index_out => l_msg_index_out );
471 -- x_msg_data := l_data;
472 END IF;
473 IF p_pa_debug_mode = 'Y' THEN
474 PA_DEBUG.Reset_Err_stack;
475 END IF;
476 RETURN;
477 END IF;
478
479 END IF;
480
481 IF p_button_pressed_from_page = 'REVENUE_EDIT' OR
482 NVL(p_agreement_id,0) <> NVL(est_amt_rec.agreement_id,0) OR
483 NVL(p_est_proj_revenue,0) <> NVL(est_amt_rec.est_project_revenue,0) THEN
484 IF l_no_of_app_plan_types=2 THEN
485 l_fin_plan_type_id := p_fin_plan_type_id_rev;
486 ELSE
487 l_fin_plan_type_id := p_fin_plan_type_id_all;
488 END IF;
489
490 l_element_type := PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE;
491
492 IF p_agreement_id IS NULL THEN
493
494 Pa_Fp_Control_Items_Utils.IsValidAgreement(
495 p_project_id => p_project_id,
496 p_agreement_number => p_agreement_number,
497 x_agreement_id => l_agreement_id,
498 x_msg_count => x_msg_count,
499 x_msg_data => x_msg_data,
500 x_return_status => x_return_status );
501 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
502 x_msg_count := fnd_msg_pub.count_msg;
503 IF x_msg_count = 1 THEN
504 PA_INTERFACE_UTILS_PUB.Get_Messages (
505 p_encoded => FND_API.G_TRUE,
506 p_msg_index => 1,
507 p_msg_count => 1 ,
508 p_msg_data => l_msg_data ,
509 p_data => x_msg_data,
510 p_msg_index_out => l_msg_index_out );
511 END IF;
512 IF p_pa_debug_mode = 'Y' THEN
513 PA_DEBUG.Reset_Err_stack;
514 END IF;
515 RETURN;
516 END IF;
517 END IF;
518
519 Maintain_Plan_Version(
520 p_project_id => p_project_id,
521 p_ci_id => p_ci_id,
522 p_fp_pref_code => p_fp_pref_code,
523 p_fin_plan_type_id => l_fin_plan_type_id,
524 p_est_proj_raw_cost => NULL,
525 p_est_proj_bd_cost => NULL,
526 p_est_proj_revenue => p_est_proj_revenue,
527 p_est_qty => NULL,
528 p_est_equip_qty => NULL,
529 x_return_status => x_return_status,
530 x_msg_count => x_msg_count,
531 x_msg_data => x_msg_data,
532 p_project_currency_Code => l_proj_curr_code,
533 p_projfunc_currency_code => l_projfunc_curr_code,
534 p_element_type => l_element_type,
535 x_plan_version_id => l_bv_id ,
536 p_impacted_task_id => p_impacted_task_id,
537 p_agreement_id => l_agreement_id,
538 p_agreement_number => p_agreement_number );
539
540 x_plan_version_id := ltrim(rtrim(l_bv_id));
541 --Bug 7497389
542 l_rev_ci_plan_version_id:=x_plan_version_id;
543 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
544 x_msg_count := fnd_msg_pub.count_msg;
545 IF x_msg_count = 1 THEN
546 PA_INTERFACE_UTILS_PUB.Get_Messages (
547 p_encoded => FND_API.G_TRUE,
548 p_msg_index => 1,
549 p_msg_count => 1 ,
550 p_msg_data => l_msg_data ,
551 p_data => x_msg_data,
552 p_msg_index_out => l_msg_index_out );
553 -- x_msg_data := l_data;
554 END IF;
555 IF p_pa_debug_mode = 'Y' THEN
556 PA_DEBUG.Reset_Err_stack;
557 END IF;
558 RETURN;
559 END IF;
560 END IF;
561
562 --Bug 7497389. This condition is put to take care of the case where the agreement info is
563 --entered for a change order and "Edit Planned Cost" button is clicked.
564
565 IF l_rev_ci_plan_version_id IS NOT NULL AND
566 l_cost_ci_plan_version_id IS NOT NULL THEN
567
568 IF p_button_pressed_from_page = 'REVENUE_EDIT' THEN
569
570 x_plan_version_id := l_rev_ci_plan_version_id;
571
572 ELSIF p_button_pressed_from_page = 'COST_EDIT' THEN
573
574 x_plan_version_id := l_cost_ci_plan_version_id;
575
576 END IF;
577
578 END IF;
579 END IF;
580
581 EXCEPTION
582 WHEN OTHERS THEN
583
584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
585 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_FP_CONTROL_ITEMS_IMPACT_PKG',
586 p_procedure_name => 'MAINTAIN_CTRL_ITEM_VERSION',
587 p_error_text => SUBSTRB(SQLERRM,1,240));
588
589 fnd_msg_pub.count_and_get(p_count => x_msg_count,
590 p_data => x_msg_data);
591 END Maintain_Ctrl_Item_Version;
592
593 PROCEDURE Maintain_Plan_Version(
594 p_project_id IN Pa_Projects_All.Project_Id%TYPE,
595 p_ci_id IN NUMBER,
596 p_fp_pref_code IN VARCHAR2,
597 p_fin_plan_type_id IN NUMBER,
598 p_est_proj_raw_cost IN NUMBER,
599 p_est_proj_bd_cost IN NUMBER,
600 p_est_proj_revenue IN NUMBER,
601 p_est_qty IN NUMBER,
602 p_est_equip_qty IN NUMBER, -- FP.M
603 p_project_currency_Code IN VARCHAR2,
604 p_projfunc_currency_code IN VARCHAR2,
605 p_element_type IN VARCHAR2 ,
606 p_impacted_task_id IN NUMBER ,
607 p_agreement_id IN NUMBER DEFAULT NULL,
608 p_agreement_number IN VARCHAR2 DEFAULT NULL,
609 p_baseline_funding_flag IN VARCHAR2 DEFAULT NULL,
610 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
611 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
612 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
613 x_plan_version_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
614 ) IS
615 l_est_projfunc_raw_cost pa_budget_versions.est_projfunc_raw_cost%TYPE;
616 l_est_projfunc_bd_cost pa_budget_versions.est_projfunc_burdened_cost%TYPE;
617 l_est_projfunc_revenue pa_budget_versions.est_projfunc_revenue%TYPE;
618 l_bv_id NUMBER;
619 l_plan_version_id NUMBER;
620 l_proj_fp_option_id NUMBER;
621 l_agreement_id PA_AGREEMENTS_ALL.Agreement_Id%TYPE;
622 l_create_ver_called_flag VARCHAR2(1);
623 l_last_updated_by NUMBER := FND_GLOBAL.user_id;
624 l_last_update_login NUMBER := FND_GLOBAL.login_id;
625 l_sysdate DATE := SYSDATE;
626 l_version_allowed_flag VARCHAR2(30);
627 l_funding_bl_tab pa_fp_auto_baseline_pkg.funding_bl_tab;
628 l_funding_level_code VARCHAR2(100);
629 l_err_code NUMBER := null;
630 l_err_stage varchar2(1000) := null;
631 l_err_stack varchar2(1000) := null;
632 lx_cur_work_bv_id NUMBER;
633 BEGIN
634 x_return_status := FND_API.G_RET_STS_SUCCESS;
635
636 IF p_pa_debug_mode = 'Y' THEN
637 pa_debug.g_err_stage:= 'Entering Maintain_Plan_Version';
638 pa_debug.write('Maintain_Plan_Version',pa_debUg.g_err_stage,3);
639
640 pa_debug.g_err_stage:= 'Project_id:'|| p_project_id;
641 pa_debug.write('Maintain_Plan_Version',pa_debug.g_err_stage,3);
642
643 pa_debug.g_err_stage:= 'p_ci_id:'|| p_ci_id;
644 pa_debug.write('Maintain_Plan_Version',pa_debug.g_err_stage,3);
645
646 pa_debug.g_err_stage:= 'p_agreement_id:' || p_agreement_id;
647 pa_debug.write('Maintain_Plan_Version',pa_debug.g_err_stage,3);
648
649 pa_debug.g_err_stage:= 'p_agreement_number:'|| p_agreement_number;
650 pa_debug.write('Maintain_Plan_Version',pa_debug.g_err_stage,3);
651 END IF;
652 l_create_ver_called_flag := 'N';
653
654 IF p_element_type = 'REVENUE' OR p_element_type = 'ALL' THEN
655 l_agreement_id := p_agreement_id;
656 ELSE
657 l_agreement_id := NULL;
658 END IF;
659 BEGIN
660 SELECT Budget_Version_Id INTO l_bv_id FROM Pa_Budget_Versions bv
661 WHERE
662 bv.Project_Id = p_project_id AND
663 NVL(bv.Fin_Plan_Type_id,-1) = p_fin_plan_type_id AND
664 NVL(bv.ci_id,-1) = p_ci_id AND
665 bv.Version_Type = p_element_type;
666 EXCEPTION
667 WHEN NO_DATA_FOUND THEN
668
669 /* Before creating CI version, there must be a current
670 working version for the approved budget plan type */
671
672 Pa_Fp_Control_Items_Utils.CHK_APRV_CUR_WORKING_BV_EXISTS
673 ( p_project_id => p_project_id
674 ,p_fin_plan_type_id => p_fin_plan_type_id
675 ,p_version_type => p_element_type
676 ,x_cur_work_bv_id => lx_cur_work_bv_id
677 ,x_return_status => x_return_status
678 ,x_msg_count => x_msg_count
679 ,x_msg_data => x_msg_data );
680
681 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
682 RETURN;
683 END IF;
684
685 /* the version name and description is not shown in the pages
686 for control item versions, so storing dummy value as CI */
687 /* version type and element type value is same here */
688
689 l_create_ver_called_flag := 'Y';
690 --For bug 3823016
691 --IF p_baseline_funding_flag = 'Y' THEN
692 IF ((p_baseline_funding_flag = 'Y') and (p_element_type<> 'COST')) THEN
693 /* the following API only creates the version for
694 the control item */
695
696 pa_billing_core.check_funding_level(
697 x_project_id => p_project_id,
698 x_funding_level => l_funding_level_code,
699 x_err_code => l_err_code,
700 x_err_stage => l_err_stage,
701 x_err_stack => l_err_stack );
702
703 IF (l_err_code <> 0) THEN
704 x_return_status := FND_API.G_RET_STS_ERROR;
705 RETURN;
706 END IF;
707
708 PA_FP_AUTO_BASELINE_PKG.CREATE_BASELINED_VERSION(
709 p_project_id => p_project_id
710 ,p_fin_plan_type_id => p_fin_plan_type_id
711 ,p_funding_level_code => l_funding_level_code
712 ,p_version_name => 'CI'
713 ,p_description => 'CI'
714 ,p_funding_bl_tab => l_funding_bl_tab
715 ,p_ci_id => p_ci_id
716 ,p_est_proj_raw_cost => p_est_proj_raw_cost
717 ,p_est_proj_bd_cost => p_est_proj_bd_cost
718 ,p_est_proj_revenue => p_est_proj_revenue
719 ,p_est_qty => p_est_qty
720 ,p_est_equip_qty => p_est_equip_qty
721 ,p_impacted_task_id => p_impacted_task_id
722 ,p_agreement_id => l_agreement_id
723 ,x_budget_version_id => l_plan_version_id
724 ,x_return_status => x_return_status
725 ,x_msg_count => x_msg_count
726 ,x_msg_data => x_msg_data
727 );
728 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
729 RETURN;
730 END IF;
731
732 l_bv_id := l_plan_version_id;
733 ELSE
734 Pa_Fp_Control_Items_Utils.Is_Create_CI_Version_Allowed
735 ( p_project_id => p_project_id
736 ,p_fin_plan_type_id => p_fin_plan_type_id
737 ,p_version_type => p_element_type
738 ,p_impacted_task_id => p_impacted_task_id
739 ,x_version_allowed_flag => l_version_allowed_flag
740 ,x_return_status => x_return_status
741 ,x_msg_count => x_msg_count
742 ,x_msg_data => x_msg_data );
743
744 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
745 RETURN;
746 END IF;
747
748
749 pa_fin_plan_pub.Create_Version(
750 p_project_id => p_project_id
751 ,p_fin_plan_type_id => p_fin_plan_type_id
752 ,p_element_type => p_element_type
753 ,p_version_name => 'CI'
754 ,p_description => 'CI'
755 ,px_budget_version_id => l_plan_version_id
756 ,x_proj_fp_option_id => l_proj_fp_option_id
757 ,x_return_status => x_return_status
758 ,x_msg_count => x_msg_count
759 ,x_msg_data => x_msg_data
760 ,p_ci_id => p_ci_id
761 ,p_est_proj_raw_cost => p_est_proj_raw_cost
762 ,p_est_proj_bd_cost => p_est_proj_bd_cost
763 ,p_est_proj_revenue => p_est_proj_revenue
764 ,p_est_qty => p_est_qty
765 ,p_est_equip_qty => p_est_equip_qty
766 ,p_impacted_task_id => p_impacted_task_id
767 ,p_agreement_id => l_agreement_id);
768
769 l_bv_id := l_plan_version_id;
770 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
771 RETURN;
772 END IF;
773 END IF;
774 END;
775
776 IF l_create_ver_called_flag = 'N' THEN
777 IF p_project_currency_Code = p_projfunc_currency_Code THEN
778 l_est_projfunc_raw_cost := p_est_proj_raw_cost;
779 l_est_projfunc_bd_cost := p_est_proj_bd_cost;
780 l_est_projfunc_revenue := p_est_proj_revenue;
781 ELSE
782 delete from pa_fp_rollup_tmp;
783 insert into pa_fp_rollup_tmp(
784 RESOURCE_ASSIGNMENT_ID,
785 START_DATE,
786 END_DATE,
787 TXN_CURRENCY_CODE,
788 PROJECT_CURRENCY_CODE,
789 PROJFUNC_CURRENCY_CODE,
790 TXN_RAW_COST,
791 TXN_BURDENED_COST,
792 TXN_REVENUE )
793 VALUES(
794 -1,
795 TRUNC(l_sysdate),
796 TRUNC(l_sysdate),
797 p_project_currency_Code,
798 p_project_currency_Code,
799 p_projfunc_currency_Code,
800 p_est_proj_raw_cost,
801 p_est_proj_bd_cost,
802 p_est_proj_revenue );
803
804 PA_FP_MULTI_CURRENCY_PKG.convert_txn_currency
805 ( p_budget_version_id => l_bv_id
806 ,p_entire_version => 'N'
807 ,x_return_status => x_return_status
808 ,x_msg_count => x_msg_count
809 ,x_msg_data => x_msg_data );
810 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
811 RETURN;
812 END IF;
813 SELECT PROJFUNC_RAW_COST,
814 PROJFUNC_BURDENED_COST,
815 PROJFUNC_REVENUE
816 INTO
817 l_est_projfunc_raw_cost ,
818 l_est_projfunc_bd_cost ,
819 l_est_projfunc_revenue
820 FROM Pa_Fp_Rollup_Tmp
821 WHERE RESOURCE_ASSIGNMENT_ID = -1;
822
823 END IF;
824 /* for proj curr code equal to projfunc curr code chk */
825 UPDATE Pa_Budget_Versions SET
826 est_project_raw_cost = p_est_proj_raw_cost,
827 est_project_burdened_cost = p_est_proj_bd_cost,
828 est_project_revenue = p_est_proj_revenue,
829 est_quantity = p_est_qty,
830 est_equipment_quantity = p_est_equip_qty,
831 agreement_id = l_agreement_id, -- Bug 3752125
832 est_projfunc_raw_cost = l_est_projfunc_raw_cost,
833 est_projfunc_burdened_cost = l_est_projfunc_bd_cost,
834 est_projfunc_revenue = l_est_projfunc_revenue,
835 last_update_date = l_sysdate,
836 last_updated_by = l_last_updated_by,
837 last_update_login = l_last_update_login
838 WHERE Budget_Version_Id = l_bv_id;
839 END IF;
840 /* for create ver called flag check */
841
842 x_plan_version_id := l_bv_id ;
843 EXCEPTION
844 WHEN OTHERS THEN
845
846 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
847 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_FP_CONTROL_ITEMS_IMPACT_PKG',
848 p_procedure_name => 'Maintain_Plan_Version',
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 END Maintain_Plan_Version;
854
855 PROCEDURE delete_ci_plan_versions
856 (
857 p_project_id IN NUMBER,
858 p_ci_id IN NUMBER,
859 p_init_msg_list IN VARCHAR2,
860 p_commit_flag IN VARCHAR2,
861 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
862 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
863 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
864 IS
865 l_bv_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
866 l_chk NUMBER := 0;
867 l_rv_number_tab PA_PLSQL_DATATYPES.NumTabTyp;
868 l_msg_data VARCHAR2(1000);
869 l_data VARCHAR2(1000);
870 l_msg_index_out NUMBER:=0;
871 l_msg_count NUMBER;
872 l_module VARCHAR2(255);
873 BEGIN
874 l_module := 'pa.plsql.Pa_Fp_Control_Items_Impact_Pkg.delete_ci_plan_versions';
875
876 IF p_pa_debug_mode = 'Y' THEN
877 PA_DEBUG.init_err_stack('Pa_Fp_Control_Items_Impact_Pkg.delete_ci_plan_versions');
878 PA_DEBUG.write_log (x_module => l_module
879 ,x_msg => 'inside del API call'
880 ,x_log_level => 5);
881 END IF;
882
883 x_return_status := FND_API.G_RET_STS_SUCCESS;
884
885 IF p_init_msg_list = 'Y' THEN
886 FND_MSG_PUB.initialize;
887 END IF;
888
889 l_bv_id_tab.delete;
890 l_rv_number_tab.delete;
891
892 BEGIN
893 SELECT budget_version_id ,
894 record_version_number
895 BULK COLLECT INTO
896 l_bv_id_tab,
897 l_rv_number_tab
898 FROM pa_budget_versions
899 WHERE
900 project_id = p_project_id AND
901 ci_id = p_ci_id;
902 EXCEPTION
903 WHEN NO_DATA_FOUND THEN
904 l_chk := 1;
905 END;
906
907 FOR I IN 1 .. l_bv_id_tab.COUNT LOOP
908 IF p_pa_debug_mode = 'Y' THEN
909 PA_DEBUG.write_log (x_module => l_module
910 ,x_msg => 'before calling dele version in finplan pub'
911 ,x_log_level => 5);
912 END IF;
913 pa_fin_plan_pub.Delete_Version(
914 p_project_id => p_project_id,
915 p_budget_version_id => l_bv_id_tab(i),
916 p_record_version_number => l_rv_number_tab(i),
917 x_return_status => x_return_status,
918 x_msg_count => x_msg_count,
919 x_msg_data => x_msg_data );
920
921 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
922 ROLLBACK;
923 x_msg_count := fnd_msg_pub.count_msg;
924 IF x_msg_count = 1 THEN
925 PA_INTERFACE_UTILS_PUB.Get_Messages (
926 p_encoded => FND_API.G_TRUE,
927 p_msg_index => 1,
928 p_msg_count => 1 ,
929 p_msg_data => l_msg_data ,
930 p_data => x_msg_data,
931 p_msg_index_out => l_msg_index_out );
932 END IF;
933 IF p_pa_debug_mode = 'Y' THEN
934 PA_DEBUG.Reset_Err_stack;
935 END IF;
936 RETURN;
937 END IF;
938
939 END LOOP;
940 /* the finplan impact record also should be deleted from
941 ci table */
942 DELETE FROM pa_ci_impacts
943 WHERE
944 ci_id = p_ci_id AND
945 impact_type_code IN ('FINPLAN','FINPLAN_COST','FINPLAN_REVENUE');
946
947 IF p_commit_flag = 'Y' THEN
948 COMMIT;
949 END IF;
950 IF p_pa_debug_mode = 'Y' THEN
951 PA_DEBUG.Reset_Err_stack;
952 END IF;
953 RETURN;
954 EXCEPTION
955 WHEN OTHERS THEN
956 ROLLBACK;
957 IF p_pa_debug_mode = 'Y' THEN
958 PA_DEBUG.Reset_Err_stack;
959 END IF;
960
961 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
962 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_FP_CONTROL_ITEMS_IMPACT_PKG',
963 p_procedure_name => 'DELETE_CI_PLAN_VERSIONS',
964 p_error_text => SUBSTRB(SQLERRM,1,240));
965
966 fnd_msg_pub.count_and_get(p_count => x_msg_count,
967 p_data => x_msg_data);
968
969
970
971 END delete_ci_plan_versions;
972
973 END Pa_Fp_Control_Items_Impact_Pkg;