1 PACKAGE BODY PA_PLANNING_TRANSACTION_UTILS AS
2 /* $Header: PAFPPTUB.pls 120.5.12010000.2 2008/08/21 22:04:59 skkoppul ship $ */
3
4 -----------------------------------------------
5 -- Declaring global datatypes and variables
6 -----------------------------------------------
7 g_module_name VARCHAR2(100) := 'pa.plsql.PA_PLANNING_TRANSACTION_UTILS';
8
9 --This record type will contain key and a value. A pl/sql tbl of this record type can be declared and it can be
10 --used for different purposes. One such case is : if its required to get the start date for a task id at many
11 --places in the code then instead of firing a select each time we can fetch it and store in this record. The key
12 --will be the task id and the value will be top task id.
13 --Created for bug 3678314
14 TYPE key_value_rec IS RECORD
15 (key NUMBER
16 ,value DATE);
17
18 TYPE key_value_rec_tbl_type IS TABLE OF key_value_rec
19 INDEX BY BINARY_INTEGER;
20
21 /*=====================================================================
22 Function Name: GET_WP_BUDGET_VERSION_ID
23
24 Purpose: This is a public API in the package. This function
25 will return the budget_version_id for the passed
26 project_structure_version_id.
27 This is called by/from
28 - Add/Update Planning Transactions API.
29
30 Note: This api is called only for workplan.
31
32 Parameters:
33 IN 1) p_struct_elem_version_id
34 - project_structure_version_id
35 =======================================================================*/
36
37 FUNCTION Get_Wp_Budget_Version_Id (
38 p_struct_elem_version_id IN pa_budget_versions.project_structure_version_id%TYPE
39 )
40 RETURN NUMBER
41 IS
42 l_budget_version_id NUMBER;
43
44 BEGIN
45 --------------------------------------------------------------------
46 -- Parameter Validations -
47 -- return null if p_struct_elem_version_id is passed as NULL
48 --------------------------------------------------------------------
49 IF p_struct_elem_version_id IS NULL THEN
50 return NULL;
51 END IF;
52
53 --------------------------------------------------------------------
54 -- Fetching budget_version_id.
55 -- Please note that this API is only called for getting the
56 -- WorkPlan Budget Version Id for the Structure Version Id passed.
57 --------------------------------------------------------------------
58 BEGIN
59 SELECT budget_version_id
60 INTO l_budget_version_id
61 FROM pa_budget_versions
62 WHERE project_structure_version_id = p_struct_elem_version_id
63 AND nvl(wp_version_flag,'N') = 'Y';
64
65 RETURN l_budget_version_id;
66 EXCEPTION
67 WHEN NO_DATA_FOUND THEN
68 RETURN NULL;
69 END;
70
71 END Get_Wp_Budget_Version_Id;
72
73
74 /*=====================================================================
75 Procedure Name: GET_RES_CLASS_RLM_IDS
76
77 Purpose: This is a public api in the package. This procedure
78 will return the rlm ids of the resource class rlm
79 ids given the resoure list id.
80 This program is called by/from:
81 - Add/Update Planning Transactions API
82
83 =======================================================================*/
84 PROCEDURE Get_Res_Class_Rlm_Ids
85 (p_project_id IN pa_projects_all.project_id%TYPE,
86 p_resource_list_id IN pa_resource_lists_all_bg.resource_list_id%TYPE,
87 x_people_res_class_rlm_id OUT NOCOPY pa_resource_list_members.resource_list_member_id%TYPE, --File.Sql.39 bug 4440895
88 x_equip_res_class_rlm_id OUT NOCOPY pa_resource_list_members.resource_list_member_id%TYPE, --File.Sql.39 bug 4440895
89 x_fin_res_class_rlm_id OUT NOCOPY pa_resource_list_members.resource_list_member_id%TYPE, --File.Sql.39 bug 4440895
90 x_mat_res_class_rlm_id OUT NOCOPY pa_resource_list_members.resource_list_member_id%TYPE, --File.Sql.39 bug 4440895
91 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
92 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
93 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
94 IS
95
96 --Start of variables used for debugging
97 l_msg_count NUMBER :=0;
98 l_data VARCHAR2(2000);
99 l_msg_data VARCHAR2(2000);
100 l_error_msg_code VARCHAR2(30);
101 l_msg_index_out NUMBER;
102 l_return_status VARCHAR2(2000);
103 l_debug_mode VARCHAR2(30);
104 --End of variables used for debugging
105
106 CURSOR c_rlm_ids IS
107 SELECT resource_list_member_id, resource_class_code
108 FROM pa_resource_list_members,
109 (SELECT control_flag
110 FROM pa_resource_lists_all_bg
111 WHERE resource_list_id = p_resource_list_id) rl_control_flag
112 WHERE resource_list_id = p_resource_list_id
113 AND ((rl_control_flag.control_flag = 'N' AND
114 object_type = 'PROJECT' AND
115 object_id = p_project_id)
116 OR
117 (rl_control_flag.control_flag = 'Y' AND
118 object_type = 'RESOURCE_LIST' AND
119 object_id = p_resource_list_id))
120 AND nvl(resource_class_flag,'N') = 'Y';
121
122 BEGIN
123
124
125 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
126 l_debug_mode := NVL(l_debug_mode, 'N');
127 x_msg_count := 0;
128 x_return_status := FND_API.G_RET_STS_SUCCESS;
129 IF l_debug_mode = 'Y' THEN
130 PA_DEBUG.Set_Curr_Function( p_function => 'PA_PLAN_TXN_UTILS.Get_Res_Class_Rlm_Ids',
131 p_debug_mode => l_debug_mode );
132 END IF;
133 ---------------------------------------------------------------
134 -- validating input parameter p_resource_list_id.
135 -- p_resource_list_id cannot be passed as null.
136 ---------------------------------------------------------------
137
138 IF l_debug_mode = 'Y' THEN
139 pa_debug.g_err_stage:='Validating input parameters';
140 pa_debug.write('PA_PLANNING_TRANSACTION_UTILS.Get_Res_Class_Rlm_Ids: ' || g_module_name,pa_debug.g_err_stage,3);
141 END IF;
142
143 IF (p_resource_list_id IS NULL) THEN
144
145 IF l_debug_mode = 'Y' THEN
146 pa_debug.g_err_stage:='p_resource_list_id is null';
147 pa_debug.write('PA_PLANNING_TRANSACTION_UTILS.Get_Res_Class_Rlm_Id: ' || g_module_name,pa_debug.g_err_stage,5);
148 END IF;
149 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
150 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
151
152 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
153
154 END IF;
155
156
157 -------------------------------------------------------------------
158 -- Fetching rlm ids from the cursor c_rlm_ids defined above
159 -- For Class Code Fetch Into
160 -- -------------- -----------------
161 -- EQUIPMENT x_equip_res_class_rlm_id
162 -- FINANCIAL_ELEMENT x_fin_res_class_rlm_id
163 -- MATERIAL x_mat_res_class_rlm_id
164 -- PEOPLE x_people_res_class_rlm_id
165 -------------------------------------------------------------------
166
167
168 IF l_debug_mode = 'Y' THEN
169 pa_debug.g_err_stage:='Fetching rlm ids';
170 pa_debug.write('PA_PLANNING_TRANSACTION_UTILS.Get_Res_Class_Rlm_Ids: ' || g_module_name,pa_debug.g_err_stage,3);
171 END IF;
172
173 FOR c1 IN c_rlm_ids LOOP -- LoopA starts
174
175 IF c1.resource_class_code = PA_FP_CONSTANTS_PKG.G_RESOURCE_CLASS_CODE_EQUIP THEN
176 x_equip_res_class_rlm_id := c1.resource_list_member_id;
177
178 ELSIF c1.resource_class_code = PA_FP_CONSTANTS_PKG.G_RESOURCE_CLASS_CODE_FIN THEN
179 x_fin_res_class_rlm_id := c1.resource_list_member_id;
180
181 ELSIF c1.resource_class_code = PA_FP_CONSTANTS_PKG.G_RESOURCE_CLASS_CODE_MAT THEN
182 x_mat_res_class_rlm_id := c1.resource_list_member_id;
183
184 ELSIF c1.resource_class_code = PA_FP_CONSTANTS_PKG.G_RESOURCE_CLASS_CODE_PPL THEN
185 x_people_res_class_rlm_id := c1.resource_list_member_id;
186
187 END IF;
188 END LOOP; -- LoopA Ends
189 IF l_debug_mode = 'Y' THEN
190 pa_debug.reset_curr_function;
191 END IF;
192 EXCEPTION
193
194 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
195 l_msg_count := FND_MSG_PUB.count_msg;
196 IF l_msg_count = 1 THEN
197 PA_INTERFACE_UTILS_PUB.get_messages
198 (p_encoded => FND_API.G_TRUE
199 ,p_msg_index => 1
200 ,p_msg_count => l_msg_count
201 ,p_msg_data => l_msg_data
202 ,p_data => l_data
203 ,p_msg_index_out => l_msg_index_out);
204 x_msg_data := l_data;
205 x_msg_count := l_msg_count;
206 ELSE
207 x_msg_count := l_msg_count;
208 END IF;
209
210 x_return_status := FND_API.G_RET_STS_ERROR;
211
212 IF l_debug_mode = 'Y' THEN
213 pa_debug.g_err_stage:='Invalid Arguments Passed';
214 pa_debug.write('PA_PLANNING_TRANSACTION_UTILS.Get_Res_Class_Rlm_Ids : ' || g_module_name,pa_debug.g_err_stage,5);
215 pa_debug.reset_curr_function;
216 END IF;
217 WHEN Others THEN
218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
219 x_msg_count := 1;
220 x_msg_data := SQLERRM;
221 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PLANNING_TRANSACTION_UTILS'
222 ,p_procedure_name => 'PA_PLANNING_TRANSACTION_UTILS.Get_res_class_rlm_ids');
223
224 IF l_debug_mode = 'Y' THEN
225 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
226 pa_debug.write('PA_PLANNING_TRANSACTION_UTILS.Get_res_class_rlm_ids: ' || g_module_name,pa_debug.g_err_stage,5);
227 pa_debug.reset_curr_function;
228 END IF;
229 RAISE;
230
231
232 END Get_Res_Class_Rlm_Ids;
233
234 -- This API will return the default planning start and end dates based on the element version
235 -- Depending on the existence either the txn, actual, estimated or scheduled dates of the task in the priority of
236 -- the order mentioned will be returned.If none of the them are there then the dates of the parent structure
237 -- version id will be passed. If the dates are not there for the parent version also then sysdate will be returned
238
239 -- The output tables x_planning_start_date_tbl and x_planning_end_date_tbl will have the same no of records as
240 -- in the table p_element_version_id_tbl. Duplicates are allowed in input and the derivation will be done for the duplicate
241 -- tasks also
242
243 -- Included p_project_id as parameter. For elem vers id as 0, project start and end dates are used.
244
245 -- Added New I/p params p_planning_start_date_tbl and x_planning_end_date_tbl -- 3793623
246 -- Dates will not be defaulted if they are passed to the API at a particular index.
247
248 PROCEDURE get_default_planning_dates
249 ( p_project_id IN pa_projects_all.project_id%TYPE
250 ,p_element_version_id_tbl IN SYSTEM.pa_num_tbl_type
251 ,p_project_structure_version_id IN pa_budget_versions.project_structure_version_id%TYPE
252 ,p_planning_start_date_tbl IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.PA_DATE_TBL_TYPE()
253 ,p_planning_end_date_tbl IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.PA_DATE_TBL_TYPE()
254 ,x_planning_start_date_tbl OUT NOCOPY SYSTEM.pa_date_tbl_type --File.Sql.39 bug 4440895
255 ,x_planning_end_date_tbl OUT NOCOPY SYSTEM.pa_date_tbl_type --File.Sql.39 bug 4440895
256 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
257 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
258 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
259 ) IS
260
261 l_msg_count NUMBER :=0;
262 l_data VARCHAR2(2000);
263 l_msg_data VARCHAR2(2000);
264 l_error_msg_code VARCHAR2(30);
265 l_msg_index_out NUMBER;
266 l_return_status VARCHAR2(2000);
267 l_debug_mode VARCHAR2(30);
268 l_module_name VARCHAR2(100):='pafpptub.get_def_planning_dates';
269
270 --These pl/sql tables will store the already derived st and end dates for the tasks so that
271 --the process of fetching st and end dates can be avoided if duplicate tasks exist in the input
272 --Changed the type of tbls for bug 3678314
273 l_cached_elem_ver_st_dt_tbl key_value_rec_tbl_type;
274 l_cached_elem_ver_end_dt_tbl key_value_rec_tbl_type;
275 l_temp NUMBER;
276
277 --Variables for the start and end dates of parent structure version id
278 l_parent_struct_st_dt DATE ;
279 l_parent_struct_end_dt DATE ;
280 BEGIN
281
282 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
283 l_debug_mode := NVL(l_debug_mode, 'Y');
284 x_msg_count := 0;
285 x_return_status := FND_API.G_RET_STS_SUCCESS;
286 IF l_debug_mode = 'Y' THEN
287 PA_DEBUG.Set_Curr_Function( p_function => 'PA_FP_PLAN_TXN_UTILS.get_def_planning_dates',
288 p_debug_mode => l_debug_mode );
289 END IF;
290 --If no records are found in the input element version id table then return
291 IF p_element_version_id_tbl.COUNT=0 THEN
292
293 IF l_debug_mode = 'Y' THEN
294 pa_debug.g_err_stage:='p_element_version_id_tbl is empty. Returning';
295 pa_debug.write(l_module_name ,pa_debug.g_err_stage,3);
296 pa_debug.reset_curr_function;
297 END IF;
298 RETURN;
299
300 END IF;
301
302 IF p_project_structure_version_id IS NULL OR p_project_id IS NULL THEN
303
304 PA_UTILS.ADD_MESSAGE
305 (p_app_short_name => 'PA',
306 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
307
308 IF l_debug_mode = 'Y' THEN
309
310 pa_debug.g_err_stage:= 'p_project_structure_version_id passed is '||p_project_structure_version_id;
311 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
312
313 pa_debug.g_err_stage:= 'p_project_id passed is '|| p_project_id;
314 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
315
316 END IF;
317
318 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
319
320 END IF;
321
322
323 IF l_debug_mode = 'Y' THEN
324 pa_debug.g_err_stage:='Looping thru the element version id tbl to derive the dates';
325 pa_debug.write(l_module_name ,pa_debug.g_err_stage,3);
326 END IF;
327
328 x_planning_start_date_tbl := SYSTEM.pa_date_tbl_type();
329 x_planning_end_date_tbl := SYSTEM.pa_date_tbl_type();
330 x_planning_start_date_tbl.extend(p_element_version_id_tbl.LAST);
331 x_planning_end_date_tbl.extend(p_element_version_id_tbl.LAST);
332 --Loop thru the input table and derive the start and end dates
333 FOR i IN p_element_version_id_tbl.FIRST..p_element_version_id_tbl.LAST LOOP
334 -- Validations for p_planning_start_date_tbl and p_planning_end_date_tbl
335 -- Bug 3793623
336 -- 1. If Start Date is passed End Date Also has to be passed.
337 -- 2. It Start Date is passed as NOT NULL, End Date will also have to be passed Not Null and vice-versa.
338 IF (((p_planning_start_date_tbl.EXISTS(i) AND p_planning_start_date_tbl(i) IS NOT NULL) AND
339 (p_planning_end_date_tbl.EXISTS(i) AND p_planning_end_date_tbl(i) IS NULL))
340 OR ((p_planning_start_date_tbl.EXISTS(i) AND p_planning_start_date_tbl(i) IS NULL) AND
341 (p_planning_end_date_tbl.EXISTS(i) AND p_planning_end_date_tbl(i) IS NOT NULL))
342 OR ((p_planning_start_date_tbl.EXISTS(i)) AND NOT(p_planning_end_date_tbl.EXISTS(i)))
343 OR (NOT(p_planning_start_date_tbl.EXISTS(i)) AND (p_planning_end_date_tbl.EXISTS(i)))) THEN
344
345 IF l_debug_mode = 'Y' THEN
346 IF NOT(p_planning_start_date_tbl.EXISTS(i)) THEN
347 pa_debug.g_err_stage:='p_planning_start_date_tbl NOT Exists :'||i;
348 pa_debug.write('PA_FP_PLANNING_TRANSACTION_UTILS.get_default_planning_dates: ' || l_module_name,pa_debug.g_err_stage,3);
349 END IF;
350 IF NOT(p_planning_end_date_tbl.EXISTS(i)) THEN
351 pa_debug.g_err_stage:='p_planning_end_date_tbl NOT Exists :'||i;
352 pa_debug.write('PA_FP_PLANNING_TRANSACTION_UTILS.get_default_planning_dates: ' || l_module_name,pa_debug.g_err_stage,3);
353 END IF;
354 pa_debug.g_err_stage:='InCorrect Dates Passed p_planning_start_date_tbl :'||p_planning_start_date_tbl(i);
355 pa_debug.write('PA_FP_PLANNING_TRANSACTION_UTILS.get_default_planning_dates: ' || l_module_name,pa_debug.g_err_stage,3);
356
357 pa_debug.g_err_stage:='InCorrect Dates Passed p_planning_end_date_tbl :'||p_planning_end_date_tbl(i);
358 pa_debug.write('PA_FP_PLANNING_TRANSACTION_UTILS.get_default_planning_dates: ' || l_module_name,pa_debug.g_err_stage,3);
359 END IF;
360 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
361 p_msg_name => 'PA_FP_INV_PARAM_PASSED',
362 p_token1 => 'PROCEDURENAME',
363 p_value1 => 'PAFPPTUB.get_default_planning_dates',
364 p_token2 => 'STAGE',
365 p_value2 => 'InCorrect Dates Passed');
366 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
367
368 -- Bug 3793623
369 -- If Both Start Date and End Dates are passed, the Input Values are honoured and
370 -- No Data Fetch has to occur for Defaulting start and End dates.
371 ELSIF ((p_planning_start_date_tbl.EXISTS(i) AND p_planning_start_date_tbl(i) IS NOT NULL) AND
372 (p_planning_end_date_tbl.EXISTS(i) AND p_planning_end_date_tbl(i) IS NOT NULL)) THEN
373
374 x_planning_start_date_tbl(i):= p_planning_start_date_tbl(i);
375 x_planning_end_date_tbl(i) := p_planning_end_date_tbl(i);
376
377 ELSE
378 --Check if the start date of the task is already retrieved and cached. Bug 3678314
379 l_temp:=NULL;
380 --For bug 3938549 changed from tbl.last to tbl.count
381 FOR kk IN 1..l_cached_elem_ver_st_dt_tbl.COUNT LOOP
382
383 IF l_cached_elem_ver_st_dt_tbl(kk).key = p_element_version_id_tbl(i) THEN
384
385 l_temp:=kk;
386 EXIT;
387
388 END IF;
389
390 END LOOP;
391
392 IF l_temp IS NOT NULL THEN
393
394 x_planning_start_date_tbl(i):= l_cached_elem_ver_st_dt_tbl(l_temp).value;
395 x_planning_end_date_tbl(i) := l_cached_elem_ver_end_dt_tbl(l_temp).value;
396
397 ELSE
398 --The element version id will be 0 for the project level record. The Dates for this ID will be derived
399 --in the next select.
400 IF p_element_version_id_tbl(i) <>0 THEN
401
402 --Bug 6449970 skkoppul - Added to_date conditions to all the null values in the decode
403 --statement. Null value will force the date become char. By default the nls
404 --session will have the date format as DD/MMM/RR format. So the year in the
405 --format will loose the first 2 digits. But it will converted back to date because it
406 -- selected into the date variable. So any date greater than 2050 will be wrapped
407 -- to date between 1950 and 2049.
408 SELECT nvl(pt.transaction_start_date, nvl(pt.actual_start_date, nvl(pt.estimated_start_date, pt.start_date))),
409 decode(pt.transaction_start_date,
410 to_date(null),decode(pt.actual_start_date,
411 to_date(null),decode(pt.estimated_start_date,
412 to_date(null),decode(pt.start_date,
413 to_date(null),to_date(null),
414 pt.completion_date),
415 pt.estimated_finish_date),
416 pt.actual_finish_date),
417 pt.transaction_completion_date)
418 INTO x_planning_start_date_tbl(i)
419 ,x_planning_end_date_tbl(i)
420 FROM pa_struct_task_wbs_v pt
421 WHERE pt.element_Version_id=p_element_version_id_tbl(i)
422 AND pt.parent_structure_version_id=p_project_structure_version_id;
423
424 END IF;
425
426 IF x_planning_start_date_tbl(i) IS NULL AND
427 x_planning_end_date_tbl(i) IS NULL THEN
428
429 IF l_parent_struct_st_dt IS NULL THEN
430
431 --Derive the st and end dates for the parent version
432 /* After the mails from Sakthi, looks like there wouldnt be a record in the
433 * below table if the project is enabled only for Financial...
434 * Since the below select is returning no data found, fixingit to read the
435 * project start and end date and commenting the below
436 SELECT nvl(pelm.actual_start_date, nvl(pelm.estimated_start_date, pelm.scheduled_start_date)),
437 decode(pelm.actual_start_date,
438 null,decode(pelm.estimated_start_date,
439 null,decode(pelm.scheduled_start_date,
440 null,null,
441 pelm.scheduled_finish_date),
442 pelm.estimated_finish_date),
443 pelm.actual_finish_date)
444 INTO l_parent_struct_st_dt
445 ,l_parent_struct_end_dt
446 FROM pa_proj_elem_ver_schedule pelm
447 WHERE pelm.element_version_id=p_project_structure_version_id;
448
449 */
450 SELECT start_date,decode(start_date, null, to_Date(null), completion_date)
451 INTO l_parent_struct_st_dt ,l_parent_struct_end_dt
452 FROM pa_projects_all
453 where project_id = p_project_id;
454
455 IF l_parent_struct_st_dt IS NULL AND l_parent_struct_end_dt IS NULL THEN
456
457 l_parent_Struct_st_dt := trunc(sysdate);
458 l_parent_Struct_end_dt := trunc(sysdate);
459
460 ELSIF l_parent_struct_end_dt IS NULL THEN
461
462 l_parent_Struct_end_dt := l_parent_Struct_st_dt;
463
464 END IF;
465
466 END IF;
467
468 x_planning_start_date_tbl(i):=l_parent_struct_st_dt;
469 x_planning_end_date_tbl(i):=l_parent_struct_end_dt;
470
471 ELSIF x_planning_end_date_tbl(i) IS NULL THEN
472
473 x_planning_end_date_tbl(i):= x_planning_start_date_tbl(i);
474
475 END IF;
476
477 l_temp := l_cached_elem_ver_st_dt_tbl.COUNT +1;
478 l_cached_elem_ver_st_dt_tbl(l_temp).key:= p_element_version_id_tbl(i);
479 l_cached_elem_ver_st_dt_tbl(l_temp).value:= x_planning_start_date_tbl(i);
480 l_cached_elem_ver_end_dt_tbl(l_temp).key:= p_element_version_id_tbl(i);
481 l_cached_elem_ver_end_dt_tbl(l_temp).value:= x_planning_end_date_tbl(i);
482
483 END IF;
484 END IF;
485 END LOOP;
486
487 IF l_debug_mode = 'Y' THEN
488 pa_debug.g_err_stage:='Leaving get_default_planning_dates';
489 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
490 pa_debug.reset_curr_function;
491 END IF;
492 EXCEPTION
493
494 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
495 l_msg_count := FND_MSG_PUB.count_msg;
496 IF l_msg_count = 1 THEN
497 PA_INTERFACE_UTILS_PUB.get_messages
498 (p_encoded => FND_API.G_TRUE
499 ,p_msg_index => 1
500 ,p_msg_count => l_msg_count
501 ,p_msg_data => l_msg_data
502 ,p_data => l_data
503 ,p_msg_index_out => l_msg_index_out);
504 x_msg_data := l_data;
505 x_msg_count := l_msg_count;
506
507 ELSE
508
509 x_msg_count := l_msg_count;
510 END IF;
511 x_return_status := FND_API.G_RET_STS_ERROR;
512 IF l_debug_mode = 'Y' THEN
513 pa_debug.reset_curr_function;
514 END IF;
515 WHEN OTHERS THEN
516
517 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
518 x_msg_count := 1;
519 x_msg_data := SQLERRM;
520 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_PLANNING_TRANSACTION_UTILS'
521 ,p_procedure_name => 'get_default_planning_dates');
522
523 IF l_debug_mode = 'Y' THEN
524 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
525 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
526 pa_debug.reset_curr_function;
527 END IF;
528 RAISE;
529
530 END get_default_planning_dates;
531 --This procedure populates the tmp table PJI_FM_EXTR_PLAN_LINES and calls the API
532 --PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE . The valid values for p_source are
533 -- 1. PA_RBS_PLANS_OUT_TMP (This is the tmp table which contains the mapped rbs elemend ids ). The PJI API will
534 -- be called for the rbs element ids availabe in the PA_RBS_PLANS_OUT_TMP, if the new rbs element id is different
535 -- from the already existing rbs element id in pa_resource_assignments. If the rbs element id is different then
536 ---------1.Reporting lines API will be called with negative amounts for the budget lines with
537 -- start_date <= etc_start_date with old rbs element id. The same API will be called for the same set of
538 -- budget lines with +ve amounts and new rbs element id again.
539 ---------2.Reporting lines API will NOT be called for the budget lines with start_date > etc_start_date. The
540 -- calling API should take care of these budget lines ( Calling this API with context 'DEL_FLAG_Y'
541 -- delete the reporting lines for budget lines with start_Date > etc_start_date)
542 -- 2. PA_FP_RA_MAP_TMP (This is the global temporary table which contains the resouce assignments in the source that
543 -- should copied. This is used for copying a version fully or some of the assignments in it ). This table is used
544 -- as the reference for deciding the budget lines for which reporting lines should be created
545 -- 3. PL-SQL : The source will be pl/sql if the pl/sql tables are populated. These pl/sql tables will be used in
546 -- populated the tmp table for calling the PJI Update API. This will be used in delete_planning_transactions
547 -- 4. DEL_FLAG_Y : This context is used in update_planning_transactions API. The API will be called with this
548 -- context when the the budget lines for a resource assignment should be deleted. This will delete all the
549 -- budget lines for a RA with start_date > etc_start_date of the version.
550 -- 5. PROCESS_RES_CHG_DERV_CALC_PRMS: Combination of 1 and 4.
551 --6 POPULATE_PJI_TABLE - This has been introduced for the bug 4543744 . This is called when we have to insert negative and
552 -- positive amounts in the pji table , called during the change in RBS the negative amounts are of the old rbs version id
553 -- existing in the pji tables and the positive amounts are of the new rbs version that is changed.
554
555 PROCEDURE call_update_rep_lines_api
556 (
557 p_source IN VARCHAR2
558 ,p_budget_version_id IN pa_budget_versions.budget_Version_id%TYPE
559 ,p_resource_assignment_id_tbl IN SYSTEM.pa_num_tbl_type
560 ,p_period_name_tbl IN SYSTEM.pa_varchar2_30_tbl_type
561 ,p_start_date_tbl IN SYSTEM.pa_date_tbl_type
562 ,p_end_date_tbl IN SYSTEM.pa_date_tbl_type
563 ,p_txn_currency_code_tbl IN SYSTEM.pa_varchar2_15_tbl_type
564 ,p_txn_raw_cost_tbl IN SYSTEM.pa_num_tbl_type
565 ,p_txn_burdened_cost_tbl IN SYSTEM.pa_num_tbl_type
566 ,p_txn_revenue_tbl IN SYSTEM.pa_num_tbl_type
567 ,p_project_raw_cost_tbl IN SYSTEM.pa_num_tbl_type
568 ,p_project_burdened_cost_tbl IN SYSTEM.pa_num_tbl_type
569 ,p_project_revenue_tbl IN SYSTEM.pa_num_tbl_type
570 ,p_raw_cost_tbl IN SYSTEM.pa_num_tbl_type
571 ,p_burdened_cost_tbl IN SYSTEM.pa_num_tbl_type
572 ,p_revenue_tbl IN SYSTEM.pa_num_tbl_type
573 ,p_cost_rejection_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
574 ,p_revenue_rejection_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
575 ,p_burden_rejection_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
576 ,p_other_rejection_code IN SYSTEM.pa_varchar2_30_tbl_type
577 ,p_pc_cur_conv_rej_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
578 ,p_pfc_cur_conv_rej_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
579 ,p_quantity_tbl IN SYSTEM.pa_num_tbl_type
580 ,p_rbs_element_id_tbl IN SYSTEM.pa_num_tbl_type
581 ,p_task_id_tbl IN SYSTEM.pa_num_tbl_type
582 ,p_res_class_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
583 ,p_rate_based_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type
584 ,p_qty_sign IN NUMBER -- for bug 4543744
585 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
586 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
587 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
588 IS
589 --Start of variables used for debugging
590 l_msg_count NUMBER :=0;
591 l_data VARCHAR2(2000);
592 l_msg_data VARCHAR2(2000);
593 l_error_msg_code VARCHAR2(30);
594 l_msg_index_out NUMBER;
595 l_return_status VARCHAR2(2000);
596 l_debug_mode VARCHAR2(30);
597 l_module_name VARCHAR2(100):='PAFPPTUB.call_update_rep_lines_api';
598 --End of variables used for debugging
599 l_rows_inserted NUMBER:=0;
600 l_msg_code VARCHAR2(2000);
601
602 l_project_id pa_budget_versions.project_id%TYPE;
603 l_fin_structure_ver_id pa_budget_versions.project_structure_version_id%Type;
604 BEGIN
605 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
606 l_debug_mode := NVL(l_debug_mode, 'N');
607 x_msg_count := 0;
608 x_return_status := FND_API.G_RET_STS_SUCCESS;
609 IF l_debug_mode = 'Y' THEN
610 PA_DEBUG.Set_Curr_Function( p_function => 'pafpptub.call_update_rep_lines_api',
611 p_debug_mode => l_debug_mode );
612 pa_debug.g_err_stage:='Validating input parameters';
613 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
614 END IF;
615
616 IF p_source IS NULL THEN
617
618 IF l_debug_mode = 'Y' THEN
619 pa_debug.g_err_stage:='p_source is '||p_source;
620 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
621 END IF;
622 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
623 p_msg_name => 'PA_FP_INV_PARAM_PASSED',
624 p_token1 => 'PROCEDURENAME',
625 p_value1 => l_module_name );
626 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
627
628 END IF;
629
630 IF p_budget_version_id IS NULL THEN
631
632 IF l_debug_mode = 'Y' THEN
633 pa_debug.g_err_stage:='p_budget_version_id is '||p_budget_version_id;
634 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
635 END IF;
636 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
637 p_msg_name => 'PA_FP_INV_PARAM_PASSED',
638 p_token1 => 'PROCEDURENAME',
639 p_value1 => l_module_name );
640 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
641
642 END IF;
643
644 IF p_source ='PL-SQL' AND
645 p_resource_assignment_id_tbl.COUNT = 0 THEN
646
647 IF l_debug_mode = 'Y' THEN
648 pa_debug.g_err_stage:='Resource assignment id table is empty. Returning';
649 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
650 pa_debug.reset_curr_function;
651 END IF;
652 RETURN;
653
654 END IF;
655
656
657 IF l_debug_mode = 'Y' THEN
658 pa_debug.g_err_stage:='Emptying the PJI_FM_EXTR_PLAN_LINES ';
659 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
660 END IF;
661
662 --Getting the project id to call the function PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID
663 SELECT project_id INTO l_project_id
664 FROM pa_budget_versions
665 WHERE budget_version_id=p_budget_version_id;
666
667 l_fin_structure_ver_id := PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(l_project_id);
668
669
670
671 IF p_source = 'PA_RBS_PLANS_OUT_TMP' OR
672 p_source = 'PROCESS_RES_CHG_DERV_CALC_PRMS'THEN
673
674 IF l_debug_mode = 'Y' THEN
675 pa_debug.g_err_stage:='Source is PJI_FM_EXTR_PLAN_LINES. Populating the tmp table';
676 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
677 END IF;
678
679 INSERT INTO PJI_FM_EXTR_PLAN_LINES
680 ( PROJECT_ID
681 ,PROJECT_ORG_ID
682 ,PROJECT_ELEMENT_ID
683 ,STRUCT_VER_ID
684 ,PERIOD_NAME
685 ,CALENDAR_TYPE
686 ,START_DATE
687 ,END_DATE
688 ,RBS_ELEMENT_ID
689 ,RBS_VERSION_ID
690 ,PLAN_VERSION_ID
691 ,PLAN_TYPE_ID
692 ,WP_VERSION_FLAG
693 ,ROLLUP_TYPE
694 ,TXN_CURRENCY_CODE
695 ,TXN_RAW_COST
696 ,TXN_BURDENED_COST
697 ,TXN_REVENUE
698 ,PRJ_CURRENCY_CODE
699 ,PRJ_RAW_COST
700 ,PRJ_BURDENED_COST
701 ,PRJ_REVENUE
702 ,PFC_CURRENCY_CODE
703 ,PFC_RAW_COST
704 ,PFC_BURDENED_COST
705 ,PFC_REVENUE
706 ,QUANTITY
707 ,RESOURCE_CLASS_CODE
708 ,RATE_BASED_FLAG)
709 SELECT
710 p.project_id
711 ,p.org_id
712 ,pra.task_id
713 ,decode(pbv.wp_version_flag,'Y',pbv.project_structure_version_id,l_fin_structure_ver_id)
714 ,pbl.period_name
715 ,nvl(pfo.cost_time_phased_code,nvl(revenue_time_phased_code,all_time_phased_code))
716 ,pbl.start_date
717 ,pbl.end_date
718 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , pra.rbs_element_id, 'NEW', tmp.rbs_element_id)
719 ,pfo.rbs_version_id
720 ,pbv.budget_version_id
721 ,pfo.fin_plan_type_id
722 ,pbv.wp_version_flag
723 ,'W'
724 ,pbl.txn_currency_code
725 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.txn_raw_cost, 'NEW', pbl.txn_raw_cost)
726 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.txn_burdened_cost, 'NEW', pbl.txn_burdened_cost)
727 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.txn_revenue, 'NEW', pbl.txn_revenue)
728 ,p.project_currency_code
729 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.project_raw_cost, 'NEW', pbl.project_raw_cost)
730 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.project_burdened_cost, 'NEW', pbl.project_burdened_cost)
731 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.project_revenue, 'NEW', pbl.project_revenue)
732 ,p.projfunc_currency_code
733 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.raw_cost, 'NEW', pbl.raw_cost)
734 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.burdened_cost, 'NEW', pbl.burdened_cost)
735 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.revenue, 'NEW', pbl.revenue)
736 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.quantity, 'NEW', pbl.quantity)
737 ,pra.resource_class_code
738 ,pra.rate_based_flag
739 FROM pa_projects_all p
740 ,pa_resource_assignments pra
741 ,pa_budget_versions pbv
742 ,pa_proj_fp_options pfo
743 ,pa_rbs_plans_out_tmp tmp
744 ,pa_budget_lines pbl
745 ,(SELECT 'OLD' as rbs_elem_id
746 FROM DUAL
747 UNION ALL
748 SELECT 'NEW' as rbs_elem_id
749 FROM DUAL) rbs_dummy
750 WHERE p.project_id=pbv.project_id
751 AND pbv.budget_version_id=p_budget_Version_id
752 AND pra.resource_assignment_id=tmp.source_id
753 AND pbv.budget_version_id=pra.budget_version_id
754 AND pfo.fin_plan_version_id=pbv.budget_Version_id
755 AND pra.rbs_element_id <> tmp.rbs_element_id
756 AND pbl.resource_assignment_id=pra.resource_assignment_id
757 AND pbl.cost_rejection_code IS NULL
758 AND pbl.revenue_rejection_code IS NULL
759 AND pbl.burden_rejection_code IS NULL
760 AND pbl.other_rejection_code IS NULL
761 AND pbl.pc_cur_conv_rejection_code IS NULL
762 AND pbl.pfc_cur_conv_rejection_code IS NULL
763 AND pbl.start_date <= nvl(pbv.etc_start_date, pbl.start_date+1);
764
765 l_rows_inserted := SQL%ROWCOUNT;
766 IF l_debug_mode = 'Y' THEN
767 pa_debug.g_err_stage:='No of rows inserted = '||l_rows_inserted;
768 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
769 END IF;
770
771 END IF;
772
773 IF p_source ='PA_FP_RA_MAP_TMP' THEN
774
775 IF l_debug_mode = 'Y' THEN
776 pa_debug.g_err_stage:='Selectiong from PA_FP_RA_MAP_TMP ';
777 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
778 END IF;
779
780 INSERT INTO PJI_FM_EXTR_PLAN_LINES
781 ( PROJECT_ID
782 ,PROJECT_ORG_ID
783 ,PROJECT_ELEMENT_ID
784 ,STRUCT_VER_ID
785 ,PERIOD_NAME
786 ,CALENDAR_TYPE
787 ,START_DATE
788 ,END_DATE
789 ,RBS_ELEMENT_ID
790 ,RBS_VERSION_ID
791 ,PLAN_VERSION_ID
792 ,PLAN_TYPE_ID
793 ,WP_VERSION_FLAG
794 ,ROLLUP_TYPE
795 ,TXN_CURRENCY_CODE
796 ,TXN_RAW_COST
797 ,TXN_BURDENED_COST
798 ,TXN_REVENUE
799 ,PRJ_CURRENCY_CODE
800 ,PRJ_RAW_COST
801 ,PRJ_BURDENED_COST
802 ,PRJ_REVENUE
803 ,PFC_CURRENCY_CODE
804 ,PFC_RAW_COST
805 ,PFC_BURDENED_COST
806 ,PFC_REVENUE
807 ,QUANTITY
808 ,RESOURCE_CLASS_CODE
809 ,RATE_BASED_FLAG)
810 SELECT
811 p.project_id
812 ,p.org_id
813 ,pra.task_id
814 ,decode(pbv.wp_version_flag,'Y',pbv.project_structure_version_id,l_fin_structure_ver_id)
815 ,pbl.period_name
816 ,nvl(pfo.cost_time_phased_code,nvl(revenue_time_phased_code,all_time_phased_code))
817 ,pbl.start_date
818 ,pbl.end_date
819 ,pra.rbs_element_id
820 ,pfo.rbs_version_id
821 ,pbv.budget_version_id
822 ,pfo.fin_plan_type_id
823 ,pbv.wp_version_flag
824 ,'W'
825 ,pbl.txn_currency_code
826 ,pbl.txn_raw_cost
827 ,pbl.txn_burdened_cost
828 ,pbl.txn_revenue
829 ,p.project_currency_code
830 ,pbl.project_raw_cost
831 ,pbl.project_burdened_cost
832 ,pbl.project_revenue
833 ,p.projfunc_currency_code
834 ,pbl.raw_cost
835 ,pbl.burdened_cost
836 ,pbl.revenue
837 ,pbl.quantity
838 ,pra.resource_class_code
839 ,pra.rate_based_flag
840 FROM pa_projects_all p
841 ,pa_resource_assignments pra
842 ,pa_budget_versions pbv
843 ,pa_proj_fp_options pfo
844 ,pa_fp_ra_map_tmp tmp
845 ,pa_budget_lines pbl
846 WHERE p.project_id=pbv.project_id
847 AND pbv.budget_version_id=p_budget_version_id
848 AND pra.resource_assignment_id=tmp.target_res_assignment_id
849 AND pbv.budget_version_id=pra.budget_version_id
850 AND pfo.fin_plan_version_id=pbv.budget_Version_id
851 AND pbl.resource_assignment_id=pra.resource_assignment_id
852 AND pbl.cost_rejection_code IS NULL
853 AND pbl.revenue_rejection_code IS NULL
854 AND pbl.burden_rejection_code IS NULL
855 AND pbl.other_rejection_code IS NULL
856 AND pbl.pc_cur_conv_rejection_code IS NULL
857 AND pbl.pfc_cur_conv_rejection_code IS NULL ;
858
859 l_rows_inserted := SQL%ROWCOUNT;
860 IF l_debug_mode = 'Y' THEN
861 pa_debug.g_err_stage:='No of rows inserted = '||l_rows_inserted;
862 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
863 END IF;
864
865 END IF;
866
867 IF p_source ='PL-SQL' THEN
868
869 IF l_debug_mode = 'Y' THEN
870 pa_debug.g_err_stage:='Selectiong from PL-SQL ';
871 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
872 END IF;
873
874 FORALL i IN p_resource_assignment_id_tbl.FIRST..p_resource_assignment_id_tbl.LAST
875 INSERT INTO PJI_FM_EXTR_PLAN_LINES
876 ( PROJECT_ID
877 ,PROJECT_ORG_ID
878 ,PROJECT_ELEMENT_ID
879 ,STRUCT_VER_ID
880 ,PERIOD_NAME
881 ,CALENDAR_TYPE
882 ,START_DATE
883 ,END_DATE
884 ,RBS_ELEMENT_ID
885 ,RBS_VERSION_ID
886 ,PLAN_VERSION_ID
887 ,PLAN_TYPE_ID
888 ,WP_VERSION_FLAG
889 ,ROLLUP_TYPE
890 ,TXN_CURRENCY_CODE
891 ,TXN_RAW_COST
892 ,TXN_BURDENED_COST
893 ,TXN_REVENUE
894 ,PRJ_CURRENCY_CODE
895 ,PRJ_RAW_COST
896 ,PRJ_BURDENED_COST
897 ,PRJ_REVENUE
898 ,PFC_CURRENCY_CODE
899 ,PFC_RAW_COST
900 ,PFC_BURDENED_COST
901 ,PFC_REVENUE
902 ,QUANTITY
903 ,RESOURCE_CLASS_CODE
904 ,RATE_BASED_FLAG)
905 SELECT
906 p.project_id
907 ,p.org_id
908 ,p_task_id_tbl(i)
909 ,decode(pbv.wp_version_flag,'Y',pbv.project_structure_version_id,l_fin_structure_ver_id)
910 ,p_period_name_tbl(i)
911 ,nvl(pfo.cost_time_phased_code,nvl(revenue_time_phased_code,all_time_phased_code))
912 ,p_start_date_tbl(i)
913 ,p_end_date_tbl(i)
914 ,p_rbs_element_id_tbl(i)
915 ,pfo.rbs_version_id
916 ,pbv.budget_version_id
917 ,pfo.fin_plan_type_id
918 ,pbv.wp_version_flag
919 ,'W'
920 ,p_txn_currency_code_tbl(i)
921 ,p_txn_raw_cost_tbl(i)
922 ,p_txn_burdened_cost_tbl(i)
923 ,p_txn_revenue_tbl(i)
924 ,p.project_currency_code
925 ,p_project_raw_cost_tbl(i)
926 ,p_project_burdened_cost_tbl(i)
927 ,p_project_revenue_tbl(i)
928 ,p.projfunc_currency_code
929 ,p_raw_cost_tbl(i)
930 ,p_burdened_cost_tbl(i)
931 ,p_revenue_tbl(i)
932 ,p_quantity_tbl(i)
933 ,p_res_class_code_tbl(i)
934 ,p_rate_based_flag_tbl(i)
935 FROM pa_projects_all p,
936 pa_proj_fp_options pfo,
937 pa_budget_versions pbv
938 WHERE p.project_id=pbv.project_id
939 AND pbv.budget_version_id=p_budget_version_id
940 AND pfo.fin_plan_version_id=p_budget_version_id
941 AND p_cost_rejection_code_tbl(i) IS NULL
942 AND p_revenue_rejection_code_tbl(i) IS NULL
943 AND p_burden_rejection_code_tbl(i) IS NULL
944 AND p_other_rejection_code(i) IS NULL
945 AND p_pc_cur_conv_rej_code_tbl(i) IS NULL
946 AND p_pfc_cur_conv_rej_code_tbl(i) IS NULL ;
947
948 l_rows_inserted := SQL%ROWCOUNT;
949 IF l_debug_mode = 'Y' THEN
950 pa_debug.g_err_stage:='No of rows inserted = '||l_rows_inserted;
951 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
952 END IF;
953
954 END IF;
955
956 IF p_source = 'REFRESH_WP_SETTINGS' THEN --Bug 5073350. Changed the source from POPULATE_PJI_TABLE to REFRESH_WP_SETTINGS.
957
958 IF l_debug_mode = 'Y' THEN
959 pa_debug.g_err_stage:='In If p_source = POPULATE_PJI_TAB ';
960 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
961 pa_debug.g_err_stage:='p_budget_version is ' || p_budget_version_id;
962 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
963 END IF;
964
965 INSERT INTO PJI_FM_EXTR_PLAN_LINES
966 ( PROJECT_ID
967 ,PROJECT_ORG_ID
968 ,PROJECT_ELEMENT_ID
969 ,STRUCT_VER_ID
970 ,PERIOD_NAME
971 ,CALENDAR_TYPE
972 ,START_DATE
973 ,END_DATE
974 ,RBS_ELEMENT_ID
975 ,RBS_VERSION_ID
976 ,PLAN_VERSION_ID
977 ,PLAN_TYPE_ID
978 ,WP_VERSION_FLAG
979 ,ROLLUP_TYPE
980 ,TXN_CURRENCY_CODE
981 ,TXN_RAW_COST
982 ,TXN_BURDENED_COST
983 ,TXN_REVENUE
984 ,PRJ_CURRENCY_CODE
985 ,PRJ_RAW_COST
986 ,PRJ_BURDENED_COST
987 ,PRJ_REVENUE
988 ,PFC_CURRENCY_CODE
989 ,PFC_RAW_COST
990 ,PFC_BURDENED_COST
991 ,PFC_REVENUE
992 ,QUANTITY
993 ,RESOURCE_CLASS_CODE
994 ,RATE_BASED_FLAG)
995 SELECT
996 p.project_id
997 ,p.org_id
998 ,pra.task_id
999 ,decode(pbv.wp_version_flag,'Y',pbv.project_structure_version_id,l_fin_structure_ver_id)
1000 ,pbl.period_name
1001 ,nvl(pfo.cost_time_phased_code,nvl(revenue_time_phased_code,all_time_phased_code))
1002 ,pbl.start_date
1003 ,pbl.end_date
1004 ,pra.rbs_element_id
1005 ,pfo.rbs_version_id
1006 ,pbv.budget_version_id
1007 ,pfo.fin_plan_type_id
1008 ,pbv.wp_version_flag
1009 ,'W'
1010 ,pbl.txn_currency_code
1011 ,pbl.txn_raw_cost * p_qty_sign
1012 ,pbl.txn_burdened_cost * p_qty_sign
1013 ,pbl.txn_revenue * p_qty_sign
1014 ,p.project_currency_code
1015 ,pbl.project_raw_cost * p_qty_sign
1016 ,pbl.project_burdened_cost * p_qty_sign
1017 ,pbl.project_revenue * p_qty_sign
1018 ,p.projfunc_currency_code
1019 ,pbl.raw_cost * p_qty_sign
1020 ,pbl.burdened_cost * p_qty_sign
1021 ,pbl.revenue * p_qty_sign
1022 ,pbl.quantity * p_qty_sign
1023 ,pra.resource_class_code
1024 ,pra.rate_based_flag
1025 FROM pa_projects_all p,
1026 pa_proj_fp_options pfo,
1027 pa_budget_versions pbv,
1028 pa_budget_lines pbl,
1029 pa_resource_assignments pra
1030 WHERE p.project_id=pbv.project_id
1031 AND pbv.budget_version_id=p_budget_version_id
1032 AND pfo.fin_plan_version_id=p_budget_version_id
1033 AND pbl.resource_assignment_id= pra.resource_assignment_id
1034 AND pbv.budget_version_id= pra.budget_version_id
1035 AND pbl.cost_rejection_code IS NULL
1036 AND pbl.revenue_rejection_code IS NULL
1037 AND pbl.burden_rejection_code IS NULL
1038 AND pbl.other_rejection_code IS NULL
1039 AND pbl.pc_cur_conv_rejection_code IS NULL
1040 AND pbl.pfc_cur_conv_rejection_code IS NULL ;
1041
1042 l_rows_inserted := SQL%ROWCOUNT;
1043
1044 IF l_debug_mode = 'Y' THEN
1045 pa_debug.g_err_stage:='No of rows inserted = '||l_rows_inserted;
1046 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
1047 END IF;
1048
1049 END IF;
1050
1051 IF nvl(l_rows_inserted,0) >0 THEN
1052
1053 IF l_debug_mode = 'Y' THEN
1054 pa_debug.g_err_stage:='Calling the PJI API';
1055 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
1056 pa_debug.write('xxxxxxx','x_return_status before plan update '||x_return_status,5);
1057
1058 END IF;
1059
1060 /*Bug 5073350. Commented out this IF condition as the plan_update api
1061 has to be called for each plan version.*/
1062 --IF p_source <> 'POPULATE_PJI_TABLE' THEN
1063
1064 PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE
1065 (p_plan_version_id => p_budget_version_id -- Added for bug 4218331
1066 ,x_msg_code =>l_msg_code
1067 ,x_return_status =>x_return_status);
1068
1069 IF l_debug_mode = 'Y' THEN
1070 pa_debug.write('xxxxxxx','x_return_status from plan update '||x_return_status,5);
1071 END IF;
1072 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1073
1074 IF l_debug_mode = 'Y' THEN
1075 pa_debug.g_err_stage:='Called API PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE returned error';
1076 pa_debug.write(l_module_name,pa_debug.g_err_stage, 5);
1077 END IF;
1078 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1079
1080 END IF;
1081 -- END IF;
1082
1083 END IF;
1084
1085 IF l_debug_mode = 'Y' THEN
1086 pa_debug.g_err_stage:='Leaving call_update_rep_lines_api';
1087 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
1088 pa_debug.reset_curr_function;
1089 END IF;
1090 EXCEPTION
1091 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1092 l_msg_count := FND_MSG_PUB.count_msg;
1093 IF l_msg_count = 1 THEN
1094 PA_INTERFACE_UTILS_PUB.get_messages
1095 (p_encoded => FND_API.G_TRUE
1096 ,p_msg_index => 1
1097 ,p_msg_count => l_msg_count
1098 ,p_msg_data => l_msg_data
1099 ,p_data => l_data
1100 ,p_msg_index_out => l_msg_index_out);
1101 x_msg_data := l_data;
1102 x_msg_count := l_msg_count;
1103 ELSE
1104 x_msg_count := l_msg_count;
1105 END IF;
1106 x_return_status := FND_API.G_RET_STS_ERROR;
1107 IF l_debug_mode = 'Y' THEN
1108 pa_debug.reset_curr_function;
1109 END IF;
1110 WHEN OTHERS THEN
1111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1112 x_msg_count := 1;
1113 x_msg_data := SQLERRM;
1114 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_PLANNING_TRANSACTION_UTILS'
1115 ,p_procedure_name => 'call_update_rep_lines_api');
1116
1117 IF l_debug_mode = 'Y' THEN
1118 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
1119 pa_debug.write( l_module_name,pa_debug.g_err_stage,5);
1120 pa_debug.reset_curr_function;
1121 END IF;
1122 RAISE;
1123
1124 END call_update_rep_lines_api;
1125
1126 END PA_PLANNING_TRANSACTION_UTILS;
[Home] [Help]