1 PACKAGE BODY PA_PLANNING_TRANSACTION_UTILS AS
2 /* $Header: PAFPPTUB.pls 120.8.12020000.4 2013/04/29 11:41:42 bpottipa 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_cbs_element_id_tbl IN SYSTEM.pa_num_tbl_type -- cbs enhancement bug#16688443
561 ,p_period_name_tbl IN SYSTEM.pa_varchar2_30_tbl_type
562 ,p_start_date_tbl IN SYSTEM.pa_date_tbl_type
563 ,p_end_date_tbl IN SYSTEM.pa_date_tbl_type
564 ,p_txn_currency_code_tbl IN SYSTEM.pa_varchar2_15_tbl_type
565 ,p_txn_raw_cost_tbl IN SYSTEM.pa_num_tbl_type
566 ,p_txn_burdened_cost_tbl IN SYSTEM.pa_num_tbl_type
567 ,p_txn_revenue_tbl IN SYSTEM.pa_num_tbl_type
568 ,p_project_raw_cost_tbl IN SYSTEM.pa_num_tbl_type
569 ,p_project_burdened_cost_tbl IN SYSTEM.pa_num_tbl_type
570 ,p_project_revenue_tbl IN SYSTEM.pa_num_tbl_type
571 ,p_raw_cost_tbl IN SYSTEM.pa_num_tbl_type
572 ,p_burdened_cost_tbl IN SYSTEM.pa_num_tbl_type
573 ,p_revenue_tbl IN SYSTEM.pa_num_tbl_type
574 ,p_cost_rejection_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
575 ,p_revenue_rejection_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
576 ,p_burden_rejection_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
577 ,p_other_rejection_code IN SYSTEM.pa_varchar2_30_tbl_type
578 ,p_pc_cur_conv_rej_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
579 ,p_pfc_cur_conv_rej_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
580 ,p_quantity_tbl IN SYSTEM.pa_num_tbl_type
581 ,p_rbs_element_id_tbl IN SYSTEM.pa_num_tbl_type
582 ,p_task_id_tbl IN SYSTEM.pa_num_tbl_type
583 ,p_res_class_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
584 ,p_rate_based_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type
585 ,p_qty_sign IN NUMBER -- for bug 4543744
586 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
587 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
588 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
589 IS
590 --Start of variables used for debugging
591 l_msg_count NUMBER :=0;
592 l_data VARCHAR2(2000);
593 l_msg_data VARCHAR2(2000);
594 l_error_msg_code VARCHAR2(30);
595 l_msg_index_out NUMBER;
596 l_return_status VARCHAR2(2000);
597 l_debug_mode VARCHAR2(30);
598 l_module_name VARCHAR2(100):='PAFPPTUB.call_update_rep_lines_api';
599 --End of variables used for debugging
600 l_rows_inserted NUMBER:=0;
601 l_msg_code VARCHAR2(2000);
602
603 l_project_id pa_budget_versions.project_id%TYPE;
604 l_fin_structure_ver_id pa_budget_versions.project_structure_version_id%Type;
605 BEGIN
606 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
607 l_debug_mode := NVL(l_debug_mode, 'N');
608 x_msg_count := 0;
609 x_return_status := FND_API.G_RET_STS_SUCCESS;
610 IF l_debug_mode = 'Y' THEN
611 PA_DEBUG.Set_Curr_Function( p_function => 'pafpptub.call_update_rep_lines_api',
612 p_debug_mode => l_debug_mode );
613 pa_debug.g_err_stage:='Validating input parameters';
614 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
615 END IF;
616
617 IF p_source IS NULL THEN
618
619 IF l_debug_mode = 'Y' THEN
620 pa_debug.g_err_stage:='p_source is '||p_source;
621 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
622 END IF;
623 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
624 p_msg_name => 'PA_FP_INV_PARAM_PASSED',
625 p_token1 => 'PROCEDURENAME',
626 p_value1 => l_module_name );
627 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
628
629 END IF;
630
631 IF p_budget_version_id IS NULL THEN
632
633 IF l_debug_mode = 'Y' THEN
634 pa_debug.g_err_stage:='p_budget_version_id is '||p_budget_version_id;
635 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
636 END IF;
637 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
638 p_msg_name => 'PA_FP_INV_PARAM_PASSED',
639 p_token1 => 'PROCEDURENAME',
640 p_value1 => l_module_name );
641 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
642
643 END IF;
644
645 IF p_source ='PL-SQL' AND
646 p_resource_assignment_id_tbl.COUNT = 0 THEN
647
648 IF l_debug_mode = 'Y' THEN
649 pa_debug.g_err_stage:='Resource assignment id table is empty. Returning';
650 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
651 pa_debug.reset_curr_function;
652 END IF;
653 RETURN;
654
655 END IF;
656
657
658 IF l_debug_mode = 'Y' THEN
659 pa_debug.g_err_stage:='Emptying the PJI_FM_EXTR_PLAN_LINES ';
660 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
661 END IF;
662
663 --Getting the project id to call the function PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID
664 SELECT project_id INTO l_project_id
665 FROM pa_budget_versions
666 WHERE budget_version_id=p_budget_version_id;
667
668 l_fin_structure_ver_id := PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(l_project_id);
669
670
671
672 IF p_source = 'PA_RBS_PLANS_OUT_TMP' OR
673 p_source = 'PROCESS_RES_CHG_DERV_CALC_PRMS'THEN
674
675 IF l_debug_mode = 'Y' THEN
676 pa_debug.g_err_stage:='Source is PJI_FM_EXTR_PLAN_LINES. Populating the tmp table';
677 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
678 END IF;
679
680 INSERT INTO PJI_FM_EXTR_PLAN_LINES
681 ( PROJECT_ID
682 ,PROJECT_ORG_ID
683 ,PROJECT_ELEMENT_ID
684 ,STRUCT_VER_ID
685 ,PERIOD_NAME
686 ,CALENDAR_TYPE
687 ,START_DATE
688 ,END_DATE
689 ,RBS_ELEMENT_ID
690 ,RBS_VERSION_ID
691 ,PLAN_VERSION_ID
692 ,PLAN_TYPE_ID
693 ,WP_VERSION_FLAG
694 ,ROLLUP_TYPE
695 ,TXN_CURRENCY_CODE
696 ,TXN_RAW_COST
697 ,TXN_BURDENED_COST
698 ,TXN_REVENUE
699 ,PRJ_CURRENCY_CODE
700 ,PRJ_RAW_COST
701 ,PRJ_BURDENED_COST
702 ,PRJ_REVENUE
703 ,PFC_CURRENCY_CODE
704 ,PFC_RAW_COST
705 ,PFC_BURDENED_COST
706 ,PFC_REVENUE
707 ,QUANTITY
708 ,RESOURCE_CLASS_CODE
709 ,RATE_BASED_FLAG
710 ,CBS_ELEMENT_ID)
711 SELECT
712 p.project_id
713 ,p.org_id
714 ,pra.task_id
715 ,decode(pbv.wp_version_flag,'Y',pbv.project_structure_version_id,l_fin_structure_ver_id)
716 ,pbl.period_name
717 ,nvl(pfo.cost_time_phased_code,nvl(revenue_time_phased_code,all_time_phased_code))
718 ,pbl.start_date
719 ,pbl.end_date
720 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , pra.rbs_element_id, 'NEW', tmp.rbs_element_id)
721 ,pfo.rbs_version_id
722 ,pbv.budget_version_id
723 ,pfo.fin_plan_type_id
724 ,pbv.wp_version_flag
725 ,'W'
726 ,pbl.txn_currency_code
727 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.txn_raw_cost, 'NEW', pbl.txn_raw_cost)
728 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.txn_burdened_cost, 'NEW', pbl.txn_burdened_cost)
729 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.txn_revenue, 'NEW', pbl.txn_revenue)
730 ,p.project_currency_code
731 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.project_raw_cost, 'NEW', pbl.project_raw_cost)
732 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.project_burdened_cost, 'NEW', pbl.project_burdened_cost)
733 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.project_revenue, 'NEW', pbl.project_revenue)
734 ,p.projfunc_currency_code
735 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.raw_cost, 'NEW', pbl.raw_cost)
736 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.burdened_cost, 'NEW', pbl.burdened_cost)
737 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.revenue, 'NEW', pbl.revenue)
738 ,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.quantity, 'NEW', pbl.quantity)
739 ,pra.resource_class_code
740 ,pra.rate_based_flag
741 ,pra.CBS_ELEMENT_ID
742 FROM pa_projects_all p
743 ,pa_resource_assignments pra
744 ,pa_budget_versions pbv
745 ,pa_proj_fp_options pfo
746 ,pa_rbs_plans_out_tmp tmp
747 ,pa_budget_lines pbl
748 ,(SELECT 'OLD' as rbs_elem_id
749 FROM DUAL
750 UNION ALL
751 SELECT 'NEW' as rbs_elem_id
752 FROM DUAL) rbs_dummy
753 WHERE p.project_id=pbv.project_id
754 AND pbv.budget_version_id=p_budget_Version_id
755 AND pra.resource_assignment_id=tmp.source_id
756 AND pbv.budget_version_id=pra.budget_version_id
757 AND pfo.fin_plan_version_id=pbv.budget_Version_id
758 AND pra.rbs_element_id <> tmp.rbs_element_id
759 AND pbl.resource_assignment_id=pra.resource_assignment_id
760 AND pbl.cost_rejection_code IS NULL
761 AND pbl.revenue_rejection_code IS NULL
762 AND pbl.burden_rejection_code IS NULL
763 AND pbl.other_rejection_code IS NULL
764 AND pbl.pc_cur_conv_rejection_code IS NULL
765 AND pbl.pfc_cur_conv_rejection_code IS NULL
766 AND pbl.start_date <= nvl(pbv.etc_start_date, pbl.start_date+1);
767
768 l_rows_inserted := SQL%ROWCOUNT;
769 IF l_debug_mode = 'Y' THEN
770 pa_debug.g_err_stage:='No of rows inserted = '||l_rows_inserted;
771 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
772 END IF;
773
774 END IF;
775
776 IF p_source ='PA_FP_RA_MAP_TMP' THEN
777
778 IF l_debug_mode = 'Y' THEN
779 pa_debug.g_err_stage:='Selectiong from PA_FP_RA_MAP_TMP ';
780 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
781 END IF;
782
783 INSERT INTO PJI_FM_EXTR_PLAN_LINES
784 ( PROJECT_ID
785 ,PROJECT_ORG_ID
786 ,PROJECT_ELEMENT_ID
787 ,STRUCT_VER_ID
788 ,PERIOD_NAME
789 ,CALENDAR_TYPE
790 ,START_DATE
791 ,END_DATE
792 ,RBS_ELEMENT_ID
793 ,RBS_VERSION_ID
794 ,PLAN_VERSION_ID
795 ,PLAN_TYPE_ID
796 ,WP_VERSION_FLAG
797 ,ROLLUP_TYPE
798 ,TXN_CURRENCY_CODE
799 ,TXN_RAW_COST
800 ,TXN_BURDENED_COST
801 ,TXN_REVENUE
802 ,PRJ_CURRENCY_CODE
803 ,PRJ_RAW_COST
804 ,PRJ_BURDENED_COST
805 ,PRJ_REVENUE
806 ,PFC_CURRENCY_CODE
807 ,PFC_RAW_COST
808 ,PFC_BURDENED_COST
809 ,PFC_REVENUE
810 ,QUANTITY
811 ,RESOURCE_CLASS_CODE
812 ,RATE_BASED_FLAG
813 ,CBS_ELEMENT_ID)
814 SELECT
815 p.project_id
816 ,p.org_id
817 ,pra.task_id
818 ,decode(pbv.wp_version_flag,'Y',pbv.project_structure_version_id,l_fin_structure_ver_id)
819 ,pbl.period_name
820 ,nvl(pfo.cost_time_phased_code,nvl(revenue_time_phased_code,all_time_phased_code))
821 ,pbl.start_date
822 ,pbl.end_date
823 ,pra.rbs_element_id
824 ,pfo.rbs_version_id
825 ,pbv.budget_version_id
826 ,pfo.fin_plan_type_id
827 ,pbv.wp_version_flag
828 ,'W'
829 ,pbl.txn_currency_code
830 ,pbl.txn_raw_cost
831 ,pbl.txn_burdened_cost
832 ,pbl.txn_revenue
833 ,p.project_currency_code
834 ,pbl.project_raw_cost
835 ,pbl.project_burdened_cost
836 ,pbl.project_revenue
837 ,p.projfunc_currency_code
838 ,pbl.raw_cost
839 ,pbl.burdened_cost
840 ,pbl.revenue
841 ,pbl.quantity
842 ,pra.resource_class_code
843 ,pra.rate_based_flag
844 ,pra.CBS_ELEMENT_ID
845 FROM pa_projects_all p
846 ,pa_resource_assignments pra
847 ,pa_budget_versions pbv
848 ,pa_proj_fp_options pfo
849 ,pa_fp_ra_map_tmp tmp
850 ,pa_budget_lines pbl
851 WHERE p.project_id=pbv.project_id
852 AND pbv.budget_version_id=p_budget_version_id
853 AND pra.resource_assignment_id=tmp.target_res_assignment_id
854 AND pbv.budget_version_id=pra.budget_version_id
855 AND pfo.fin_plan_version_id=pbv.budget_Version_id
856 AND pbl.resource_assignment_id=pra.resource_assignment_id
857 AND pbl.cost_rejection_code IS NULL
858 AND pbl.revenue_rejection_code IS NULL
859 AND pbl.burden_rejection_code IS NULL
860 AND pbl.other_rejection_code IS NULL
861 AND pbl.pc_cur_conv_rejection_code IS NULL
862 AND pbl.pfc_cur_conv_rejection_code IS NULL ;
863
864 l_rows_inserted := SQL%ROWCOUNT;
865 IF l_debug_mode = 'Y' THEN
866 pa_debug.g_err_stage:='No of rows inserted = '||l_rows_inserted;
867 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
868 END IF;
869
870 END IF;
871 /*
872 DEBUG_NS('p_resource_assignment_id_tbl.count = ' || p_resource_assignment_id_tbl.count);
873 DEBUG_NS('p_budget_version_id = ' || p_budget_version_id);
874 DEBUG_NS('l_fin_structure_ver_id = ' || l_fin_structure_ver_id);
875
876 FOR i IN p_resource_assignment_id_tbl.first..p_resource_assignment_id_tbl.last LOOP
877 DEBUG_NS('p_resource_assignment_id_tbl(i) = ' || p_resource_assignment_id_tbl(i));
878 DEBUG_NS('p_task_id_tbl(i) = ' || p_task_id_tbl(i));
879 DEBUG_NS('p_period_name_tbl(i) = ' || p_period_name_tbl(i));
880 DEBUG_NS('p_start_date_tbl(i) = ' || p_start_date_tbl(i));
881 DEBUG_NS('p_end_date_tbl(i) = ' || p_end_date_tbl(i));
882 DEBUG_NS('p_rbs_element_id_tbl(i) = ' || p_rbs_element_id_tbl(i));
883 DEBUG_NS('p_txn_currency_code_tbl(i) = ' || p_txn_currency_code_tbl(i));
884 DEBUG_NS('p_txn_raw_cost_tbl(i) = ' || p_txn_raw_cost_tbl(i));
885 DEBUG_NS('p_txn_burdened_cost_tbl(i) = ' || p_txn_burdened_cost_tbl(i));
886 DEBUG_NS('p_txn_revenue_tbl(i) = ' || p_txn_revenue_tbl(i));
887 DEBUG_NS('p_project_raw_cost_tbl(i) = ' || p_project_raw_cost_tbl(i));
888 DEBUG_NS('p_project_burdened_cost_tbl(i) = ' || p_project_burdened_cost_tbl(i));
889 DEBUG_NS('p_project_revenue_tbl(i) = ' || p_project_revenue_tbl(i));
890 DEBUG_NS('p_raw_cost_tbl(i) = ' || p_raw_cost_tbl(i));
891 DEBUG_NS('p_burdened_cost_tbl(i) = ' || p_burdened_cost_tbl(i));
892 DEBUG_NS('p_revenue_tbl(i) = ' || p_revenue_tbl(i));
893 DEBUG_NS('p_quantity_tbl(i) = ' || p_quantity_tbl(i));
894 DEBUG_NS('p_res_class_code_tbl(i) = ' || p_res_class_code_tbl(i));
895 DEBUG_NS('p_rate_based_flag_tbl(i) = ' || p_rate_based_flag_tbl(i));
896 DEBUG_NS('p_cost_rejection_code_tbl(i) = ' || p_cost_rejection_code_tbl(i));
897 DEBUG_NS('p_revenue_rejection_code_tbl(i) = ' || p_revenue_rejection_code_tbl(i));
898 DEBUG_NS('p_burden_rejection_code_tbl(i) = ' || p_burden_rejection_code_tbl(i));
899 DEBUG_NS('p_other_rejection_code(i) = ' || p_other_rejection_code(i));
900 DEBUG_NS('p_pc_cur_conv_rej_code_tbl(i) = ' || p_pc_cur_conv_rej_code_tbl(i));
901 DEBUG_NS('p_pfc_cur_conv_rej_code_tbl(i) = ' || p_pfc_cur_conv_rej_code_tbl(i));
902
903 END LOOP;
904 */
905 IF p_source ='PL-SQL' THEN
906
907 IF l_debug_mode = 'Y' THEN
908 pa_debug.g_err_stage:='Selectiong from PL-SQL ';
909 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
910 END IF;
911
912 FORALL i IN p_resource_assignment_id_tbl.FIRST..p_resource_assignment_id_tbl.LAST
913 INSERT INTO PJI_FM_EXTR_PLAN_LINES
914 ( PROJECT_ID
915 ,PROJECT_ORG_ID
916 ,PROJECT_ELEMENT_ID
917 ,STRUCT_VER_ID
918 ,PERIOD_NAME
919 ,CALENDAR_TYPE
920 ,START_DATE
921 ,END_DATE
922 ,RBS_ELEMENT_ID
923 ,RBS_VERSION_ID
924 ,PLAN_VERSION_ID
925 ,PLAN_TYPE_ID
926 ,WP_VERSION_FLAG
927 ,ROLLUP_TYPE
928 ,TXN_CURRENCY_CODE
929 ,TXN_RAW_COST
930 ,TXN_BURDENED_COST
931 ,TXN_REVENUE
932 ,PRJ_CURRENCY_CODE
933 ,PRJ_RAW_COST
934 ,PRJ_BURDENED_COST
935 ,PRJ_REVENUE
936 ,PFC_CURRENCY_CODE
937 ,PFC_RAW_COST
938 ,PFC_BURDENED_COST
939 ,PFC_REVENUE
940 ,QUANTITY
941 ,RESOURCE_CLASS_CODE
942 ,RATE_BASED_FLAG
943 ,CBS_ELEMENT_ID)
944 SELECT
945 p.project_id
946 ,p.org_id
947 ,p_task_id_tbl(i)
948 ,decode(pbv.wp_version_flag,'Y',pbv.project_structure_version_id,l_fin_structure_ver_id)
949 ,p_period_name_tbl(i)
950 ,nvl(pfo.cost_time_phased_code,nvl(revenue_time_phased_code,all_time_phased_code))
951 ,p_start_date_tbl(i)
952 ,p_end_date_tbl(i)
953 ,p_rbs_element_id_tbl(i)
954 ,pfo.rbs_version_id
955 ,pbv.budget_version_id
956 ,pfo.fin_plan_type_id
957 ,pbv.wp_version_flag
958 ,'W'
959 ,p_txn_currency_code_tbl(i)
960 ,p_txn_raw_cost_tbl(i)
961 ,p_txn_burdened_cost_tbl(i)
962 ,p_txn_revenue_tbl(i)
963 ,p.project_currency_code
964 ,p_project_raw_cost_tbl(i)
965 ,p_project_burdened_cost_tbl(i)
966 ,p_project_revenue_tbl(i)
967 ,p.projfunc_currency_code
968 ,p_raw_cost_tbl(i)
969 ,p_burdened_cost_tbl(i)
970 ,p_revenue_tbl(i)
971 ,p_quantity_tbl(i)
972 ,p_res_class_code_tbl(i)
973 ,p_rate_based_flag_tbl(i)
974 ,p_cbs_element_id_tbl(i)
975 FROM pa_projects_all p,
976 pa_proj_fp_options pfo,
977 pa_budget_versions pbv
978 WHERE p.project_id=pbv.project_id
979 AND pbv.budget_version_id=p_budget_version_id
980 AND pfo.fin_plan_version_id=p_budget_version_id
981 AND p_cost_rejection_code_tbl(i) IS NULL
982 AND p_revenue_rejection_code_tbl(i) IS NULL
983 AND p_burden_rejection_code_tbl(i) IS NULL
984 AND p_other_rejection_code(i) IS NULL
985 AND p_pc_cur_conv_rej_code_tbl(i) IS NULL
986 AND p_pfc_cur_conv_rej_code_tbl(i) IS NULL ;
987
988 l_rows_inserted := SQL%ROWCOUNT;
989 --DEBUG_NS('No of rows inserted = ' || l_rows_inserted);
990 IF l_debug_mode = 'Y' THEN
991 pa_debug.g_err_stage:='No of rows inserted = '||l_rows_inserted;
992 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
993 END IF;
994
995 END IF;
996
997 IF p_source = 'REFRESH_WP_SETTINGS' THEN --Bug 5073350. Changed the source from POPULATE_PJI_TABLE to REFRESH_WP_SETTINGS.
998
999 IF l_debug_mode = 'Y' THEN
1000 pa_debug.g_err_stage:='In If p_source = POPULATE_PJI_TAB ';
1001 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
1002 pa_debug.g_err_stage:='p_budget_version is ' || p_budget_version_id;
1003 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
1004 END IF;
1005
1006 INSERT INTO PJI_FM_EXTR_PLAN_LINES
1007 ( PROJECT_ID
1008 ,PROJECT_ORG_ID
1009 ,PROJECT_ELEMENT_ID
1010 ,STRUCT_VER_ID
1011 ,PERIOD_NAME
1012 ,CALENDAR_TYPE
1013 ,START_DATE
1014 ,END_DATE
1015 ,RBS_ELEMENT_ID
1016 ,RBS_VERSION_ID
1017 ,PLAN_VERSION_ID
1018 ,PLAN_TYPE_ID
1019 ,WP_VERSION_FLAG
1020 ,ROLLUP_TYPE
1021 ,TXN_CURRENCY_CODE
1022 ,TXN_RAW_COST
1023 ,TXN_BURDENED_COST
1024 ,TXN_REVENUE
1025 ,PRJ_CURRENCY_CODE
1026 ,PRJ_RAW_COST
1027 ,PRJ_BURDENED_COST
1028 ,PRJ_REVENUE
1029 ,PFC_CURRENCY_CODE
1030 ,PFC_RAW_COST
1031 ,PFC_BURDENED_COST
1032 ,PFC_REVENUE
1033 ,QUANTITY
1034 ,RESOURCE_CLASS_CODE
1035 ,RATE_BASED_FLAG
1036 ,CBS_ELEMENT_ID)
1037 SELECT
1038 p.project_id
1039 ,p.org_id
1040 ,pra.task_id
1041 ,decode(pbv.wp_version_flag,'Y',pbv.project_structure_version_id,l_fin_structure_ver_id)
1042 ,pbl.period_name
1043 ,nvl(pfo.cost_time_phased_code,nvl(revenue_time_phased_code,all_time_phased_code))
1044 ,pbl.start_date
1045 ,pbl.end_date
1046 ,pra.rbs_element_id
1047 ,pfo.rbs_version_id
1048 ,pbv.budget_version_id
1049 ,pfo.fin_plan_type_id
1050 ,pbv.wp_version_flag
1051 ,'W'
1052 ,pbl.txn_currency_code
1053 ,pbl.txn_raw_cost * p_qty_sign
1054 ,pbl.txn_burdened_cost * p_qty_sign
1055 ,pbl.txn_revenue * p_qty_sign
1056 ,p.project_currency_code
1057 ,pbl.project_raw_cost * p_qty_sign
1058 ,pbl.project_burdened_cost * p_qty_sign
1059 ,pbl.project_revenue * p_qty_sign
1060 ,p.projfunc_currency_code
1061 ,pbl.raw_cost * p_qty_sign
1062 ,pbl.burdened_cost * p_qty_sign
1063 ,pbl.revenue * p_qty_sign
1064 ,pbl.quantity * p_qty_sign
1065 ,pra.resource_class_code
1066 ,pra.rate_based_flag
1067 ,pra.CBS_ELEMENT_ID
1068 FROM pa_projects_all p,
1069 pa_proj_fp_options pfo,
1070 pa_budget_versions pbv,
1071 pa_budget_lines pbl,
1072 pa_resource_assignments pra
1073 WHERE p.project_id=pbv.project_id
1074 AND pbv.budget_version_id=p_budget_version_id
1075 AND pfo.fin_plan_version_id=p_budget_version_id
1076 AND pbl.resource_assignment_id= pra.resource_assignment_id
1077 AND pbv.budget_version_id= pra.budget_version_id ;
1078 /* AND pbl.cost_rejection_code IS NULL
1079 AND pbl.revenue_rejection_code IS NULL
1080 AND pbl.burden_rejection_code IS NULL
1081 AND pbl.other_rejection_code IS NULL
1082 AND pbl.pc_cur_conv_rejection_code IS NULL
1083 AND pbl.pfc_cur_conv_rejection_code IS NULL */ -- Commented as part of 15916083
1084
1085 l_rows_inserted := SQL%ROWCOUNT;
1086
1087 IF l_debug_mode = 'Y' THEN
1088 pa_debug.g_err_stage:='No of rows inserted = '||l_rows_inserted;
1089 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
1090 END IF;
1091
1092 END IF;
1093
1094
1095 --delete from DEBUG_PJI_FM_EXTR_PLAN_LINES;
1096 /*
1097 DEBUG_NS('Before insert = ' || l_rows_inserted);
1098 insert into DEBUG_PJI_FM_EXTR_PLAN_LINES (select * from PJI_FM_EXTR_PLAN_LINES) ;
1099 commit;
1100 DEBUG_NS('After insert = ' || l_rows_inserted);
1101 */
1102
1103 IF nvl(l_rows_inserted,0) >0 THEN
1104
1105 IF l_debug_mode = 'Y' THEN
1106 pa_debug.g_err_stage:='Calling the PJI API';
1107 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
1108 pa_debug.write('xxxxxxx','x_return_status before plan update '||x_return_status,5);
1109
1110 END IF;
1111
1112 /*Bug 5073350. Commented out this IF condition as the plan_update api
1113 has to be called for each plan version.*/
1114 --IF p_source <> 'POPULATE_PJI_TABLE' THEN
1115
1116 PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE
1117 (p_plan_version_id => p_budget_version_id -- Added for bug 4218331
1118 ,x_msg_code =>l_msg_code
1119 ,x_return_status =>x_return_status);
1120
1121 IF l_debug_mode = 'Y' THEN
1122 pa_debug.write('xxxxxxx','x_return_status from plan update '||x_return_status,5);
1123 END IF;
1124 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1125
1126 IF l_debug_mode = 'Y' THEN
1127 pa_debug.g_err_stage:='Called API PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE returned error';
1128 pa_debug.write(l_module_name,pa_debug.g_err_stage, 5);
1129 END IF;
1130 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1131
1132 END IF;
1133 -- END IF;
1134
1135 END IF;
1136
1137 IF l_debug_mode = 'Y' THEN
1138 pa_debug.g_err_stage:='Leaving call_update_rep_lines_api';
1139 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
1140 pa_debug.reset_curr_function;
1141 END IF;
1142 EXCEPTION
1143 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1144 l_msg_count := FND_MSG_PUB.count_msg;
1145 IF l_msg_count = 1 THEN
1146 PA_INTERFACE_UTILS_PUB.get_messages
1147 (p_encoded => FND_API.G_TRUE
1148 ,p_msg_index => 1
1149 ,p_msg_count => l_msg_count
1150 ,p_msg_data => l_msg_data
1151 ,p_data => l_data
1152 ,p_msg_index_out => l_msg_index_out);
1153 x_msg_data := l_data;
1154 x_msg_count := l_msg_count;
1155 ELSE
1156 x_msg_count := l_msg_count;
1157 END IF;
1158 x_return_status := FND_API.G_RET_STS_ERROR;
1159 IF l_debug_mode = 'Y' THEN
1160 pa_debug.reset_curr_function;
1161 END IF;
1162 WHEN OTHERS THEN
1163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1164 x_msg_count := 1;
1165 x_msg_data := SQLERRM;
1166 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_PLANNING_TRANSACTION_UTILS'
1167 ,p_procedure_name => 'call_update_rep_lines_api');
1168
1169 IF l_debug_mode = 'Y' THEN
1170 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
1171 pa_debug.write( l_module_name,pa_debug.g_err_stage,5);
1172 pa_debug.reset_curr_function;
1173 END IF;
1174 RAISE;
1175
1176 END call_update_rep_lines_api;
1177
1178 END PA_PLANNING_TRANSACTION_UTILS;
[Home] [Help]