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