[Home] [Help]
PACKAGE BODY: APPS.PA_FP_REFRESH_ELEMENTS_PUB
Source
1 PACKAGE BODY pa_fp_refresh_elements_pub AS
2 /* $Header: PAFPPERB.pls 120.3 2005/08/19 16:27:59 mwasowic noship $ */
3 p_pa_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4
5 PROCEDURE fp_write_log_ss_or_conc(
6 p_calling_context IN VARCHAR2,
7 p_msg IN VARCHAR2,
8 p_log_level IN NUMBER,
9 p_module IN VARCHAR2 ) IS
10 l_dummy NUMBER;
11 /* p_calling_context - SS when called from OA pages and
12 CP when called from concurrent program */
13 BEGIN
14 IF p_calling_context = 'SS' THEN
15 pa_debug.write( x_module => p_module,
16 x_msg => p_msg,
17 x_log_level => p_log_level);
18 ELSIF p_calling_context = 'CP' THEN
19 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
20 END IF;
21 END fp_write_log_ss_or_conc;
22
23 /* This API updates the budget version and proj fp options table
24 for plan processing code and process_update_wbs_flag columns based on the
25 p_return_status value. */
26 PROCEDURE update_process_status( p_fp_opt_tab IN PA_PLSQL_DATATYPES.IdTabTyp,
27 p_return_status IN VARCHAR2,
28 p_project_id IN NUMBER,
29 p_request_id IN NUMBER,
30 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
31 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
32 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
33 BEGIN
34 NULL;
35 END update_process_status;
36
37 /* This API internally calls update_process_status to update the
38 status. Exception : This API is having a autonomous COMMIT.
39 This API should be called only in case of unexpected errors and the process
40 is running thru the concurrent program. */
41
42 PROCEDURE update_process_status_auto(
43 p_fp_opt_tab IN PA_PLSQL_DATATYPES.IdTabTyp,
44 p_return_status IN VARCHAR2,
45 p_project_id IN NUMBER,
46 p_request_id IN NUMBER,
47 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
48 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
49 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
50 BEGIN
51 NULL;
52
53 END update_process_status_auto;
54
55
56
57 /* This procedure expects either p_budget_version_id or p_proj_fp_options_id
58 to be passed as NOT NULL. Based on the parameter values it returns the concurrent
59 request id , processing code and a flag which indicates whether the record
60 requires a planning elements refresh or not. */
61
62 PROCEDURE get_refresh_plan_ele_dtls(
63 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
64 DEFAULT NULL,
65 p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
66 DEFAULT NULL,
67 x_request_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
68 x_process_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
69 x_refresh_required_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
70 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
71 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
72 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
73 BEGIN
74 NULL;
75 END get_refresh_plan_ele_dtls;
76
77 /* This procedure updates the concurrent request id, process code and
78 refresh required flag in budget versions or fp options table based on the
79 parameter passed. If the p_proj_fp_options_id and p_budget_version_id is passed
80 as NULL, then the API updates all the appropriate records ( only Task level planning
81 records ) in both budget versions and proj fp options table.
82 */
83 PROCEDURE set_process_flag_opt(
84 p_project_id IN pa_projects_all.project_id%TYPE,
85 p_request_id IN pa_budget_versions.request_id%TYPE,
86 p_process_code IN pa_budget_versions.plan_processing_code%TYPE,
87 p_refresh_required_flag IN VARCHAR2,
88 p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
89 DEFAULT NULL,
90 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
91 DEFAULT NULL,
92 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
93 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
94 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
95 BEGIN
96 NULL;
97
98 END set_process_flag_opt;
99
100 /* This procedure updates the concurrent request id, process code and
101 refresh required flag in all the appropriate records ( only Task level planning
102 records ) in both budget versions and proj fp options table. This API internally
103 calls the API set_process_flag_opt.
104 */
105
106 PROCEDURE set_process_flag_proj(
107 p_project_id IN pa_projects_all.project_id%TYPE,
108 p_request_id IN pa_budget_versions.request_id%TYPE,
109 p_process_code IN pa_budget_versions.plan_processing_code%TYPE,
110 p_refresh_required_flag IN VARCHAR2,
111 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
112 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
113 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
114 BEGIN
115 NULL;
116 END set_process_flag_proj;
117
118 PROCEDURE refresh_planning_elements(
119 p_project_id IN pa_projects_all.project_id%TYPE,
120 p_request_id IN pa_budget_versions.request_id%TYPE,
121 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
122 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
123 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
124 l_impacted_tasks_rec PA_FP_ELEMENTS_PUB.l_wbs_refresh_tasks_tbl_typ;
125 i number;
126 l_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
127 l_top_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
128 l_parent_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
129 l_task_level_tab PA_PLSQL_DATATYPES.Char1TabTyp;
130 l_fp_opt_tbl PA_PLSQL_DATATYPES.IdTabTyp;
131 l_tname_tbl PA_PLSQL_DATATYPES.Char30TabTyp;
132 l_dummy NUMBER;
133 l_module VARCHAR2(255):='pa_fp_refresh_elements_pub.refresh_planning_elements';
134 l_calling_context VARCHAR2(10);
135 l_msg VARCHAR2(300);
136 l_ret_status VARCHAR2(100);
137 x_return_status_in VARCHAR2(100);
138 BEGIN
139 x_return_status := FND_API.G_RET_STS_SUCCESS;
140 l_task_id_tab.DELETE;
141 l_top_task_id_tab.DELETE;
142 l_fp_opt_tbl.DELETE;
143
144 IF p_request_id IS NOT NULL THEN
145 l_calling_context := 'CP';
146 ELSE
147 l_calling_context := 'SS';
148 END IF;
149
150 IF P_PA_DEBUG_MODE = 'Y' THEN
151 pa_debug.set_err_stack(l_module);
152 pa_debug.set_process('refresh_planning_elements: ' || 'PLSQL','LOG',p_pa_debug_mode);
153 END IF;
154
155 IF p_pa_debug_mode = 'Y' THEN
156 l_msg := 'Project Id:'||to_char(p_project_id) ||' Req Id :'||to_char(p_request_id);
157 pa_fp_refresh_elements_pub.fp_write_log_ss_or_conc(
158 p_calling_context => l_calling_context,
159 p_msg => l_msg,
160 p_log_level => 3,
161 p_module => l_module );
162 END IF;
163
164 BEGIN
165 IF p_request_id IS NOT NULL THEN
166 SELECT proj_fp_options_id,l_tname
167 BULK COLLECT INTO l_fp_opt_tbl,l_tname_tbl
168 FROM
169 (
170 SELECT proj_fp_options_id proj_fp_options_id,'OPT' l_tname
171 FROM pa_proj_fp_options
172 WHERE
173 project_id = p_project_id AND
174 fin_plan_option_level_code IN ( 'PROJECT',
175 'PLAN_TYPE' ) AND
176 ( nvl(all_fin_plan_level_code,'x' ) IN ( 'L','T','M' ) OR
177 nvl(cost_fin_plan_level_code,'x' ) IN ( 'L','T','M' ) OR
178 nvl(revenue_fin_plan_level_code,'x' ) IN ( 'L','T','M' ) ) AND
179 NVL(process_update_wbs_flag,'N') = 'Y' AND
180 p_request_id = request_id AND
181 plan_processing_code = 'WUP'
182 UNION ALL
183 SELECT proj_fp_options_id proj_fp_options_id, 'BV' l_tname FROM
184 pa_proj_fp_options opt,
185 pa_budget_versions bv
186 WHERE
187 opt.project_id = p_project_id AND
188 opt.fin_plan_option_level_code = 'PLAN_VERSION' AND
189 bv.budget_version_id = opt.fin_plan_version_id AND
190 ( nvl(opt.all_fin_plan_level_code,'x' ) IN ( 'L','T','M' ) OR
191 nvl(opt.cost_fin_plan_level_code,'x' ) IN ( 'L','T','M' ) OR
192 nvl(opt.revenue_fin_plan_level_code,'x' ) IN ( 'L','T','M' ) ) AND
193 NVL(bv.process_update_wbs_flag,'N') = 'Y' AND
194 p_request_id = bv.request_id AND
195 bv.plan_processing_code = 'WUP'
196 );
197 ELSE
198 SELECT proj_fp_options_id,l_tname
199 BULK COLLECT INTO l_fp_opt_tbl,l_tname_tbl
200 FROM
201 (
202 SELECT proj_fp_options_id proj_fp_options_id,'OPT' l_tname
203 FROM pa_proj_fp_options
204 WHERE
205 project_id = p_project_id AND
206 fin_plan_option_level_code IN ( 'PROJECT',
207 'PLAN_TYPE' ) AND
208 ( nvl(all_fin_plan_level_code,'x' ) IN ( 'L','T','M' ) OR
209 nvl(cost_fin_plan_level_code,'x' ) IN ( 'L','T','M' ) OR
210 nvl(revenue_fin_plan_level_code,'x' ) IN ( 'L','T','M' ) ) AND
211 NVL(process_update_wbs_flag,'N') = 'Y'
212 UNION ALL
213 SELECT proj_fp_options_id proj_fp_options_id, 'BV' l_tname FROM
214 pa_proj_fp_options opt,
215 pa_budget_versions bv
216 WHERE
217 opt.project_id = p_project_id AND
218 opt.fin_plan_option_level_code = 'PLAN_VERSION' AND
219 bv.locked_by_person_id IS NULL AND /* Bug 3091568 */
220 bv.budget_version_id = opt.fin_plan_version_id AND
221 ( nvl(opt.all_fin_plan_level_code,'x' ) IN ( 'L','T','M' ) OR
222 nvl(opt.cost_fin_plan_level_code,'x' ) IN ( 'L','T','M' ) OR
223 nvl(opt.revenue_fin_plan_level_code,'x' ) IN ( 'L','T','M' ) ) AND
224 NVL(bv.process_update_wbs_flag,'N') = 'Y'
225 );
226 END IF;
227 EXCEPTION
228 WHEN NO_DATA_FOUND THEN
229 l_dummy := 1;
230 END;
231
232 IF l_fp_opt_tbl.COUNT = 0 THEN
233 IF p_pa_debug_mode = 'Y' THEN
234 l_msg := 'l_fp_opt_tbl count is zero. Returning ';
235 pa_fp_refresh_elements_pub.fp_write_log_ss_or_conc(
236 p_calling_context => l_calling_context,
237 p_msg => l_msg,
238 p_log_level => 3,
239 p_module => l_module );
240 END IF;
241 RETURN;
242 END IF;
243
244
245 SELECT task_id,top_task_id,
246 parent_task_id,
247 decode(task_id,top_task_id,'T',
248 decode(pa_task_utils.check_child_exists(task_id),
249 1,'M','L' ))
250 BULK COLLECT INTO
251 l_task_id_tab,
252 l_top_task_id_tab,
253 l_parent_task_id_tab,
254 l_task_level_tab
255 FROM pa_tasks WHERE
256 project_id = p_project_id
257 ORDER BY PA_PROJ_ELEMENTS_UTILS.GET_DISPLAY_SEQUENCE(task_id);
258
259 IF l_task_id_tab.COUNT = 0 THEN
260 IF p_pa_debug_mode = 'Y' THEN
261 l_msg := 'task tbl count is zero. Returning ';
262 pa_fp_refresh_elements_pub.fp_write_log_ss_or_conc(
263 p_calling_context => l_calling_context,
264 p_msg => l_msg,
265 p_log_level => 3,
266 p_module => l_module );
267 END IF;
268 RETURN;
269 END IF;
270
271 FOR task_idx IN 1 .. l_task_id_tab.COUNT LOOP
272 l_impacted_tasks_rec(task_idx).task_id := l_task_id_tab(task_idx);
273 l_impacted_tasks_rec(task_idx).parent_task_id := l_parent_task_id_tab(task_idx);
274 l_impacted_tasks_rec(task_idx).top_task_id := l_top_task_id_tab(task_idx);
275 l_impacted_tasks_rec(task_idx).task_level := l_task_level_tab(task_idx);
276 END LOOP;
277
278 IF p_pa_debug_mode = 'Y' THEN
279 l_msg := 'before calling PA_FP_ELEMENTS_PUB.make_new_tasks_plannable';
280 pa_fp_refresh_elements_pub.fp_write_log_ss_or_conc(
281 p_calling_context => l_calling_context,
282 p_msg => l_msg,
283 p_log_level => 3,
284 p_module => l_module );
285 END IF;
286
287 PA_FP_ELEMENTS_PUB.make_new_tasks_plannable
288 (p_project_id => p_project_id,
289 p_tasks_tbl => l_impacted_tasks_rec,
290 P_refresh_fp_options_tbl => l_fp_opt_tbl,
291 x_return_status => x_return_status,
292 x_msg_count => x_msg_count,
293 x_msg_data => x_msg_data );
294
295 /* the return status should be retained to pass the value
296 back to the calling API. */
297 l_ret_status := x_return_status;
298
299 IF p_pa_debug_mode = 'Y' THEN
300 l_msg := 'after calling PA_FP_ELEMENTS_PUB.make_new_tasks_plannable'
301 || 'ret status:'||x_return_status;
302 pa_fp_refresh_elements_pub.fp_write_log_ss_or_conc(
303 p_calling_context => l_calling_context,
304 p_msg => l_msg,
305 p_log_level => 3,
306 p_module => l_module );
307 END IF;
308
309 IF p_request_id IS NULL OR
310 ( p_request_id IS NOT NULL AND
311 x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
312 IF p_pa_debug_mode = 'Y' THEN
313 l_msg := 'calling pa_fp_refresh_elements_pub.update_process_status';
314 pa_fp_refresh_elements_pub.fp_write_log_ss_or_conc(
315 p_calling_context => l_calling_context,
316 p_msg => l_msg,
317 p_log_level => 3,
318 p_module => l_module );
319 END IF;
320 x_return_status_in := x_return_status;
321 pa_fp_refresh_elements_pub.update_process_status(
322 p_fp_opt_tab => l_fp_opt_tbl,
323 p_return_status => x_return_status_in,
324 p_project_id => p_project_id,
325 p_request_id => p_request_id,
326 x_return_status => x_return_status,
327 x_msg_count => x_msg_count,
328 x_msg_data => x_msg_data );
329 ELSIF p_request_id IS NOT NULL AND
330 x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
331 IF p_pa_debug_mode = 'Y' THEN
332 l_msg := 'calling pa_fp_refresh_elements_pub.update_process_status_auto';
333 pa_fp_refresh_elements_pub.fp_write_log_ss_or_conc(
334 p_calling_context => l_calling_context,
335 p_msg => l_msg,
336 p_log_level => 3,
337 p_module => l_module );
338 END IF;
339 x_return_status_in := x_return_status;
340 pa_fp_refresh_elements_pub.update_process_status_auto(
341 p_fp_opt_tab => l_fp_opt_tbl,
342 p_return_status => x_return_status_in,
343 p_project_id => p_project_id,
344 p_request_id => p_request_id,
345 x_return_status => x_return_status,
346 x_msg_count => x_msg_count,
347 x_msg_data => x_msg_data );
348 END IF;
349 /* setting the return status from PA_FP_ELEMENTS_PUB.make_new_tasks_plannable */
350 x_return_status := l_ret_status;
351
352 IF p_pa_debug_mode = 'Y' THEN
353 PA_DEBUG.Reset_Err_stack;
354 END IF;
355 EXCEPTION
356 WHEN OTHERS THEN
357 IF p_pa_debug_mode = 'Y' THEN
358 PA_DEBUG.Reset_Err_stack;
359 END IF;
360 IF p_request_id IS NOT NULL THEN
361 x_return_status_in := x_return_status;
362 pa_fp_refresh_elements_pub.update_process_status_auto(
363 p_fp_opt_tab => l_fp_opt_tbl,
364 p_return_status => x_return_status_in,
365 p_project_id => p_project_id,
366 p_request_id => p_request_id,
367 x_return_status => x_return_status,
368 x_msg_count => x_msg_count,
369 x_msg_data => x_msg_data );
370 END IF;
371 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
372 fnd_msg_pub.add_exc_msg(p_pkg_name => 'pa_fp_refresh_elements_pub',
373 p_procedure_name => 'refresh_planning_elements',
374 p_error_text => SUBSTRB(SQLERRM,1,240));
375
376 fnd_msg_pub.count_and_get(p_count => x_msg_count,
377 p_data => x_msg_data);
378
379 END refresh_planning_elements;
380
381
382
383 END pa_fp_refresh_elements_pub;