[Home] [Help]
PACKAGE BODY: APPS.PA_FP_MAINTAIN_ACTUAL_PUB
Source
1 PACKAGE body PA_FP_MAINTAIN_ACTUAL_PUB as
2 /* $Header: PAFPMAPB.pls 120.16 2007/04/13 16:17:29 rthumma noship $ */
3
4 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6 /**MAINTAIN_ACTUAL_AMT_WRP will get value from PA_PROG_ACT_BY_PERIOD_TEMP view*
7 *Populate init columns in PA_BUDGET_LINES. *
8 *Valid values for parameter P_CALLING_CONTEXT are:*
9 * WP_PROGRESS -- Work plan progress *
10 * WP_SUMMARIZED_ACTUAL -- Work plan summarized actual transactions *
11 * WP_APPLY_PROGRESS_TO_WORKING *
12 *Valid values for parameter P_EXTRACTION_TYPE are:*
13 * FULL -- DEFAULT, indicates full update of existing period *
14 * INCREMENTAL -- indicates increment the passed value of existing period *
15 **/
16 PROCEDURE MAINTAIN_ACTUAL_AMT_WRP
17 (P_PROJECT_ID_TAB IN SYSTEM.PA_NUM_TBL_TYPE,
18 P_WP_STR_VERSION_ID_TAB IN SYSTEM.PA_NUM_TBL_TYPE,
19 P_ACTUALS_THRU_DATE IN SYSTEM.PA_DATE_TBL_TYPE,
20 P_CALLING_CONTEXT IN VARCHAR2,
21 P_COMMIT_FLAG IN VARCHAR2,
22 P_INIT_MSG_FLAG IN VARCHAR2,
23 P_CALLING_MODE IN VARCHAR2,
24 P_EXTRACTION_TYPE IN VARCHAR2,
25 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
26 X_MSG_COUNT OUT NOCOPY NUMBER,
27 X_MSG_DATA OUT NOCOPY VARCHAR2)
28 IS
29 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_maintain_actual_pub.maintain_actual_amt_wrp';
30 l_count NUMBER;
31 l_msg_count NUMBER;
32 l_cnt NUMBER;
33 l_data VARCHAR2(2000);
34 l_msg_data VARCHAR2(2000);
35 l_msg_index_out NUMBER;
36
37 /* hidden res asg id recs are for the task level numbers
38 without resources. */
39 CURSOR distinct_ra_curr_cursor(c_project_id number,
40 c_STRUCTURE_VERSION_ID number ) IS
41 SELECT distinct vw.project_id,
42 bv.budget_version_id,
43 vw.STRUCTURE_VERSION_ID,
44 vw.RESOURCE_ASSIGNMENT_ID,
45 vw.TXN_CURRENCY_CODE
46 FROM PA_PROG_ACT_BY_PERIOD_TEMP vw,
47 PA_BUDGET_VERSIONS bv,
48 PA_RESOURCE_ASSIGNMENTS ra
49 WHERE bv.project_structure_version_id = vw.structure_version_id
50 AND nvl(bv.wp_version_flag,'N') = 'Y' AND
51 vw.RESOURCE_ASSIGNMENT_ID IS NOT NULL AND
52 ra.resource_assignment_id = vw.resource_assignment_id AND
53 ra.budget_version_id = bv.budget_version_id AND
54 vw.project_id = c_project_id AND
55 vw.structure_version_id = c_STRUCTURE_VERSION_ID
56 UNION
57 SELECT distinct vw.project_id,
58 bv.budget_version_id,
59 vw.STRUCTURE_VERSION_ID,
60 vw.HIDDEN_RES_ASSGN_ID,
61 vw.TXN_CURRENCY_CODE
62 FROM PA_PROG_ACT_BY_PERIOD_TEMP vw,
63 PA_BUDGET_VERSIONS bv,
64 PA_RESOURCE_ASSIGNMENTS ra
65 WHERE bv.project_structure_version_id = vw.structure_version_id
66 AND nvl(bv.wp_version_flag,'N') = 'Y' AND
67 vw.HIDDEN_RES_ASSGN_ID IS NOT NULL AND
68 ra.resource_assignment_id = vw.HIDDEN_RES_ASSGN_ID AND
69 ra.budget_version_id = bv.budget_version_id AND
70 vw.project_id = c_project_id AND
71 vw.structure_version_id = c_STRUCTURE_VERSION_ID;
72
73 /* Added start date and finish date in the SELECT stmt for bug 4408930 */
74
75 CURSOR budget_line_cursor(p_struct_ver_id NUMBER,
76 p_res_asg_id NUMBER,
77 p_txn_currency_code VARCHAR2) IS
78 SELECT period_name,
79 actual_effort,
80 actual_cost,
81 actual_cost_pc,
82 actual_cost_fc,
83 actual_rawcost,
84 actual_rawcost_pc,
85 actual_rawcost_fc,
86 start_date,
87 finish_date
88 FROM PA_PROG_ACT_BY_PERIOD_TEMP
89 WHERE structure_version_id = p_struct_ver_id
90 AND nvl(resource_assignment_id,HIDDEN_RES_ASSGN_ID) = p_res_asg_id
91 AND txn_currency_code = p_txn_currency_code;
92
93 l_project_id_tab pa_plsql_datatypes.IdTabTyp;
94 l_struct_ver_id_tab pa_plsql_datatypes.IdTabTyp;
95 l_budget_ver_id_tab pa_plsql_datatypes.IdTabTyp;
96 l_res_asg_id_tab pa_plsql_datatypes.IdTabTyp;
97 l_txn_currency_code_tab pa_plsql_datatypes.Char30TabTyp;
98
99 l_fp_cols_rec PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
100 l_time_phase VARCHAR2(10);
101 l_period_name_tab pa_plsql_datatypes.Char30TabTyp;
102 l_quantity_tab pa_plsql_datatypes.NumTabTyp;
103 l_txn_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
104 --l_txn_brdn_cost_tab pa_plsql_datatypes.NumTabTyp;
105 --l_txn_revenue_tab pa_plsql_datatypes.NumTabTyp;
106 l_proj_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
107 --l_proj_brdn_cost_tab pa_plsql_datatypes.NumTabTyp;
108 --l_proj_revenue_tab pa_plsql_datatypes.NumTabTyp;
109 l_pou_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
110 --l_pou_brdn_cost_tab pa_plsql_datatypes.NumTabTyp;
111 --l_pou_revenue_tab pa_plsql_datatypes.NumTabTyp;
112 l_start_date_tab pa_plsql_datatypes.DateTabTyp;
113 l_end_date_tab pa_plsql_datatypes.DateTabTyp;
114 l_start_date Date;
115 l_end_date Date;
116
117 l_amt_dtls_tbl pa_fp_maintain_actual_pub.l_amt_dtls_tbl_typ;
118
119 l_txn_bd_cost_tab pa_plsql_datatypes.NumTabTyp;
120 l_proj_bd_cost_tab pa_plsql_datatypes.NumTabTyp;
121 l_pou_bd_cost_tab pa_plsql_datatypes.NumTabTyp;
122
123 l_bv_id pa_budget_versions.budget_version_id%type;
124 l_bv_id_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
125 l_ra_id_upd_reprt_tab pa_plsql_datatypes.IdTabTyp;
126
127 /* Additional parameters for MAINTAIN_ACTUAL_AMT_RA */
128 l_open_pd_plan_amt_flag VARCHAR2(1);
129 -- End Date of P_ACTUALS_THRU_DATE period
130 l_open_pd_end_date DATE;
131
132 l_last_updated_by NUMBER := FND_GLOBAL.user_id;
133 l_last_update_login NUMBER := FND_GLOBAL.login_id;
134 l_sysdate DATE := SYSDATE;
135
136 -- IPM: Added table to store Distinct ra_ids for workplan resources
137 l_display_qty_ra_id_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
138
139 BEGIN
140 IF p_init_msg_flag = 'Y' THEN
141 FND_MSG_PUB.initialize;
142 END IF;
143
144 X_MSG_COUNT := 0;
145 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
146
147 IF p_pa_debug_mode = 'Y' AND p_init_msg_flag = 'Y' THEN
148 PA_DEBUG.init_err_stack('PA_FP_MAINTAIN_ACTUAL_PUB.MAINTAIN_ACTUAL_AMT_WRP');
149 ELSIF p_pa_debug_mode = 'Y' AND p_init_msg_flag = 'N' THEN
150 pa_debug.set_curr_function( p_function => 'MAINTAIN_ACTUAL_AMT_WRP'
151 ,p_debug_mode => p_pa_debug_mode);
152 END IF;
153
154 IF p_project_id_tab.count = 0 THEN
155 IF p_pa_debug_mode = 'Y' THEN
156 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
157 ( p_called_mode => p_calling_mode,
158 p_msg => 'Returning because P_PROJECT_ID_TAB has count = 0',
159 p_module_name => l_module_name,
160 p_log_level => 5 );
161 END IF;
162 IF p_pa_debug_mode = 'Y' AND p_init_msg_flag = 'Y' THEN
163 PA_DEBUG.reset_err_stack;
164 ELSIF p_pa_debug_mode = 'Y' AND p_init_msg_flag = 'N' THEN
165 PA_DEBUG.Reset_Curr_Function;
166 END IF;
167
168 RETURN;
169 END IF;
170
171 FOR ss1 IN 1 .. P_PROJECT_ID_TAB.COUNT LOOP
172 BEGIN
173 SELECT budget_version_id into l_bv_id
174 FROM PA_BUDGET_VERSIONS
175 WHERE project_id = P_PROJECT_ID_tab(ss1)
176 AND project_structure_version_id = P_WP_STR_VERSION_ID_TAB(ss1)
177 AND nvl(wp_version_flag,'N') = 'Y';
178 EXCEPTION
179 -- Bug 5336341: NO_DATA_FOUND can be encountered when there are
180 -- orphaned workplan structures in the system. In this case, raise
181 -- an informative error message.
182 -- Orphaned workplan structures are those for which workplan
183 -- publishing has failed. A workplan structure is created but a
184 -- corresponding budget version does not exist in the system in such case.
185 WHEN no_data_found THEN
186 PA_UTILS.ADD_MESSAGE
187 ( p_app_short_name => 'PA',
188 p_msg_name => 'PA_FP_ORPHANED_STRUCT_ERR',
189 p_token1 => 'PROJECT_NUMBER',
190 p_value1 => P_PROJECT_ID_tab(ss1) );
191 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
192 END;
193
194 --Bug 4091264
195 l_bv_id_tab.extend;
196 l_bv_id_tab(l_bv_id_tab.COUNT):=l_bv_id;
197
198 -- In the following case, we need to Null out the budget line INIT columns:
199 -- Calling context = 'WP_SUMMARIZED_ACTUAL' and extraction_type = 'FULL'
200
201 IF (P_EXTRACTION_TYPE = 'FULL' AND
202 P_CALLING_CONTEXT = 'WP_SUMMARIZED_ACTUAL') THEN
203 UPDATE pa_budget_lines
204 SET TXN_INIT_RAW_COST = decode(TXN_INIT_RAW_COST,null,null,0),
205 TXN_INIT_BURDENED_COST = decode(TXN_INIT_BURDENED_COST,null,null,0),
206 ---TXN_INIT_REVENUE = decode(TXN_INIT_REVENUE,null,null,0),
207 PROJECT_INIT_RAW_COST = decode(PROJECT_INIT_RAW_COST,null,null,0),
208 PROJECT_INIT_BURDENED_COST = decode(PROJECT_INIT_BURDENED_COST,null,null,0),
209 ---PROJECT_INIT_REVENUE = decode(PROJECT_INIT_REVENUE,null,null,0),
210 INIT_RAW_COST = decode(INIT_RAW_COST,null,null,0),
211 INIT_BURDENED_COST = decode(INIT_BURDENED_COST,null,null,0),
212 ---INIT_REVENUE = decode(INIT_REVENUE,null,null,0),
213 INIT_QUANTITY = decode(INIT_QUANTITY,null,null,0),
214 LAST_UPDATE_DATE = l_sysdate,
215 LAST_UPDATED_BY = l_last_updated_by,
216 LAST_UPDATE_LOGIN = l_last_update_login
217 WHERE budget_version_id = l_bv_id;
218 END IF;
219
220 IF p_pa_debug_mode = 'Y' THEN
221 pa_fp_gen_amount_utils.fp_debug
222 (p_called_mode => p_calling_mode,
223 p_msg => 'Before calling
224 pa_fp_gen_amount_utils.get_plan_version_dtls',
225 p_module_name => l_module_name,
226 p_log_level => 5);
227 END IF;
228 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS(
229 P_PROJECT_ID => p_project_id_tab(ss1),
230 P_BUDGET_VERSION_ID => l_bv_id,
231 X_FP_COLS_REC => l_fp_cols_rec,
232 X_RETURN_STATUS => x_return_status,
233 X_MSG_COUNT => x_msg_count,
234 X_MSG_DATA => x_msg_data);
235 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
236 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
237 END IF;
238 IF p_pa_debug_mode = 'Y' THEN
239 pa_fp_gen_amount_utils.fp_debug
240 (p_called_mode => p_calling_mode,
241 p_msg => 'Status after calling
242 pa_fp_gen_amount_utils.get_plan_version_dtls'
243 ||x_return_status,
244 p_module_name => l_module_name,
245 p_log_level => 5);
246 END IF;
247
248 l_time_phase := l_fp_cols_rec.x_time_phased_code;
249
250 l_ra_id_upd_reprt_tab.DELETE;
251
252 SELECT DISTINCT NVL(resource_assignment_id, hidden_res_assgn_id)
253 BULK COLLECT
254 INTO l_ra_id_upd_reprt_tab
255 FROM PA_PROG_ACT_BY_PERIOD_TEMP
256 WHERE project_id = P_PROJECT_ID_TAB(ss1)
257 AND structure_version_id = P_WP_STR_VERSION_ID_TAB(ss1)
258 AND NVL(resource_assignment_id, hidden_res_assgn_id) IS NOT NULL;
259
260 IF ( l_ra_id_upd_reprt_tab.count <> 0 )
261 THEN
262
263 IF p_pa_debug_mode = 'Y' THEN
264 pa_fp_gen_amount_utils.fp_debug
265 (p_called_mode => p_calling_mode,
266 p_msg => 'Before calling PA_FP_MAINTAIN_ACTUAL_PUB.'
267 ||'BLK_UPD_REPORTING_LINES_WRP',
268 p_module_name => l_module_name,
269 p_log_level => 5);
270 END IF;
271 /*============================================================================+
272 | Bug 4141131: Calling PA_FP_MAINTAIN_ACTUAL_PUB.BLK_UPD_REPORTING_LINES_WRP |
273 | only if P_EXTRACTION_TYPE is 'INCREMENTAL'. |
274 | Bug 4164532: Calling PA_FP_MAINTAIN_ACTUAL_PUB.BLK_UPD_REPORTING_LINES_WRP |
275 | only if NOT (P_CALLING_CONTEXT = 'WP_SUMMARIZED_ACTUAL' |
276 | AND P_EXTRACTION_TYPE = 'INCREMENTAL') |
277 +============================================================================*/
278 IF NOT ( P_CALLING_CONTEXT = 'WP_SUMMARIZED_ACTUAL' AND P_EXTRACTION_TYPE = 'FULL' )
279 THEN
280 PA_FP_MAINTAIN_ACTUAL_PUB.BLK_UPD_REPORTING_LINES_WRP (
281 P_BUDGET_VERSION_ID => l_bv_id,
282 P_ENTIRE_VERSION_FLAG => 'N',
283 P_RES_ASG_ID_TAB => l_ra_id_upd_reprt_tab,
284 P_ACTIVITY_CODE => 'DELETE',
285 X_RETURN_STATUS => x_return_status,
286 X_MSG_COUNT => x_msg_count,
287 X_MSG_DATA => x_msg_data);
288 IF p_pa_debug_mode = 'Y' THEN
289 pa_fp_gen_amount_utils.fp_debug
290 (p_called_mode => p_calling_mode,
291 p_msg => 'Status after calling PA_FP_MAINTAIN_ACTUAL_PUB.'
292 ||'BLK_UPD_REPORTING_LINES_WRP:'||x_return_status,
293 p_module_name => l_module_name,
294 p_log_level => 5);
295 END IF;
296 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
297 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
298 END IF;
299 END IF; --P_CALLING_CONTEXT = 'WP_SUMMARIZED_ACTUAL' AND P_EXTRACTION_TYPE = 'INCREMENTAL'
300
301
302 OPEN distinct_ra_curr_cursor(P_PROJECT_ID_TAB(ss1),
303 P_WP_STR_VERSION_ID_TAB(ss1));
304 FETCH distinct_ra_curr_cursor BULK COLLECT
305 INTO l_project_id_tab,
306 l_budget_ver_id_tab,
307 l_struct_ver_id_tab,
308 l_res_asg_id_tab,
309 l_txn_currency_code_tab;
310 CLOSE distinct_ra_curr_cursor;
311
312 /* Initialize open period variables */
313 l_open_pd_plan_amt_flag := 'N';
314 l_open_pd_end_date := NULL;
315
316 IF l_struct_ver_id_tab.count > 0 THEN
317 IF (l_time_phase = 'P') THEN
318 SELECT pd.end_date INTO l_open_pd_end_date
319 FROM pa_periods_all pd
320 WHERE pd.org_id = l_fp_cols_rec.x_org_id -- R12 MOAC 4447573: NVL(pd.org_id,-99)
321 AND p_actuals_thru_date(ss1) BETWEEN pd.start_date AND pd.end_date;
322 ELSIF ( l_time_phase = 'G') THEN
323 SELECT gl.end_date INTO l_open_pd_end_date
324 FROM gl_period_statuses gl,
325 pa_implementations_all imp
326 WHERE gl.application_id = PA_PERIOD_PROCESS_PKG.Application_id
327 AND gl.set_of_books_id = imp.set_of_books_id
328 AND gl.adjustment_period_flag = 'N'
329 AND imp.org_id = l_fp_cols_rec.x_org_id -- R12 MOAC 4447573: NVL(imp.org_id,-99)
330 AND p_actuals_thru_date(ss1) BETWEEN gl.start_date AND gl.end_date;
331 END IF;
332
333 IF p_actuals_thru_date(ss1) < l_open_pd_end_date THEN
334 l_open_pd_plan_amt_flag := 'Y';
335 END IF;
336 END IF;
337
338 FOR i IN 1..l_struct_ver_id_tab.count LOOP
339 OPEN budget_line_cursor(l_struct_ver_id_tab(i),
340 l_res_asg_id_tab(i),
341 l_txn_currency_code_tab(i));
342 l_period_name_tab.delete;
343 l_quantity_tab.delete;
344 l_txn_raw_cost_tab.delete;
345 l_proj_raw_cost_tab.delete;
346 l_pou_raw_cost_tab.delete;
347 l_txn_bd_cost_tab.delete;
348 l_proj_bd_cost_tab.delete;
349 l_pou_bd_cost_tab.delete;
350 l_start_date_tab.delete;
351 l_end_date_tab.delete;
352
353 FETCH budget_line_cursor
354 BULK COLLECT
355 INTO l_period_name_tab,
356 l_quantity_tab,
357 l_txn_bd_cost_tab,
358 l_proj_bd_cost_tab,
359 l_pou_bd_cost_tab,
360 l_txn_raw_cost_tab,
361 l_proj_raw_cost_tab,
362 l_pou_raw_cost_tab,
363 l_start_date_tab,
364 l_end_date_tab;
365 CLOSE budget_line_cursor;
366
367 l_amt_dtls_tbl.DELETE;
368
369 IF l_period_name_tab.count > 0 THEN
370 FOR j IN 1..l_period_name_tab.count LOOP
371 l_amt_dtls_tbl(j).period_name := l_period_name_tab(j);
372 l_amt_dtls_tbl(j).txn_raw_cost := l_txn_raw_cost_tab(j);
373 l_amt_dtls_tbl(j).project_raw_cost := l_proj_raw_cost_tab(j);
374 l_amt_dtls_tbl(j).project_func_raw_cost := l_pou_raw_cost_tab(j);
375 l_amt_dtls_tbl(j).txn_burdened_cost := l_txn_bd_cost_tab(j);
376 l_amt_dtls_tbl(j).project_burdened_cost := l_proj_bd_cost_tab(j);
377 l_amt_dtls_tbl(j).project_func_burdened_cost := l_pou_bd_cost_tab(j);
378 l_amt_dtls_tbl(j).txn_revenue := null;
379 l_amt_dtls_tbl(j).project_revenue := null;
380 l_amt_dtls_tbl(j).project_func_revenue := null;
381 l_amt_dtls_tbl(j).quantity := l_quantity_tab(j);
382
383 /* bug 4408930 */
384 l_amt_dtls_tbl(j).start_date := l_start_date_tab(j);
385 l_amt_dtls_tbl(j).end_date := l_end_date_tab(j);
386 /* bug 4408930 */
387
388 END LOOP;
389
390 IF (l_time_phase = 'P') THEN
391 FOR m IN 1..l_period_name_tab.count LOOP
392 SELECT pd.start_date ,pd.end_date into l_start_date, l_end_date
393 FROM pa_periods_all pd
394 WHERE pd.org_id = l_fp_cols_rec.x_org_id -- R12 MOAC 4447573: nvl(pd.org_id,-99)
395 AND pd.period_name = l_period_name_tab(m);
396 l_amt_dtls_tbl(m).start_date := l_start_date;
397 l_amt_dtls_tbl(m).end_date := l_end_date;
398 END LOOP;
399 ELSIF ( l_time_phase = 'G') THEN
400 FOR n IN l_period_name_tab.FIRST..l_period_name_tab.LAST LOOP
401 SELECT gl.start_date, gl.end_date INTO l_start_date,l_end_date
402 FROM gl_period_statuses gl,
403 pa_implementations_all imp
404 WHERE gl.application_id = PA_PERIOD_PROCESS_PKG.Application_id
405 AND gl.SET_OF_BOOKS_ID = imp.SET_OF_BOOKS_ID
406 AND gl.ADJUSTMENT_PERIOD_FLAG = 'N'
407 AND imp.org_id = l_fp_cols_rec.x_org_id -- R12 MOAC 4447573: nvl(imp.org_id,-99)
408 AND gl.period_name = l_period_name_tab(n);
409 l_amt_dtls_tbl(n).start_date := l_start_date;
410 l_amt_dtls_tbl(n).end_date := l_end_date;
411 END LOOP;
412 /* commented for bug 4408930
413 ELSIF ( l_time_phase = 'N') THEN
414 FOR m IN 1..l_period_name_tab.count LOOP
415 res asg id should be a valid id.
416 SELECT NVL(planning_start_date,trunc(sysdate)),
417 NVL(planning_end_date,trunc(sysdate) ) INTO
418 l_start_date,l_end_date
419 FROM pa_resource_assignments
420 WHERE resource_assignment_id = l_res_asg_id_tab(i);
421 l_amt_dtls_tbl(m).start_date := l_start_date;
422 l_amt_dtls_tbl(m).end_date := l_end_date;
423 END LOOP; */
424 END IF; /* end if for l_time_phase */
425 END IF; /* end if for l_period_name_tab.count > 0 */
426
427 IF p_pa_debug_mode = 'Y' THEN
428 pa_fp_gen_amount_utils.fp_debug
429 (p_called_mode => p_calling_mode,
430 p_msg => 'Before calling
431 pa_fp_maintain_actual_pub.maintain_actual_amt_ra',
432 p_module_name => l_module_name,
433 p_log_level => 5);
434 END IF;
435 PA_FP_MAINTAIN_ACTUAL_PUB.MAINTAIN_ACTUAL_AMT_RA (
436 P_PROJECT_ID => l_project_id_tab(i),
437 P_BUDGET_VERSION_ID => l_budget_ver_id_tab(i),
438 P_RESOURCE_ASSIGNMENT_ID => l_res_asg_id_tab(i),
439 P_TXN_CURRENCY_CODE => l_txn_currency_code_tab(i),
440 P_AMT_DTLS_REC_TAB => l_amt_dtls_tbl,
441 P_CALLING_CONTEXT => p_calling_context,
442 P_EXTRACTION_TYPE => p_extraction_type,
443 P_OPEN_PD_PLAN_AMT_FLAG => l_open_pd_plan_amt_flag,
444 P_OPEN_PD_END_DATE => l_open_pd_end_date,
445 X_RETURN_STATUS => x_return_Status,
446 X_MSG_COUNT => x_msg_count,
447 X_MSG_DATA => x_msg_data );
448 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
449 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
450 END IF;
451 IF p_pa_debug_mode = 'Y' THEN
452 pa_fp_gen_amount_utils.fp_debug
453 (p_called_mode => p_calling_mode,
454 p_msg => 'Status after calling
455 pa_fp_maintain_actual_pub.maintain_actual_amt_ra'
456 ||x_return_status,
457 p_module_name => l_module_name,
458 p_log_level => 5);
459 END IF;
460 END LOOP; /* end loop for l_struct_ver_id_tab.count */
461
462
463 -- IPM: New Entity and Display Quantity ERs --------------------
464
465 DELETE pa_resource_asgn_curr_tmp;
466
467 FORALL i IN 1..l_res_asg_id_tab.count
468 INSERT INTO pa_resource_asgn_curr_tmp (
469 resource_assignment_id,
470 txn_currency_code )
471 VALUES (
472 l_res_asg_id_tab(i),
473 l_txn_currency_code_tab(i) );
474
475 -- Bug 5042399: Copy any existing override rates to the tmp table
476 -- so that they will be carried over during the rollup.
477 UPDATE pa_resource_asgn_curr_tmp tmp
478 SET ( TXN_RAW_COST_RATE_OVERRIDE,
479 TXN_BURDEN_COST_RATE_OVERRIDE,
480 TXN_BILL_RATE_OVERRIDE ) =
481 ( SELECT rbc.TXN_RAW_COST_RATE_OVERRIDE,
482 rbc.TXN_BURDEN_COST_RATE_OVERRIDE,
483 rbc.TXN_BILL_RATE_OVERRIDE
484 FROM pa_resource_asgn_curr rbc
485 WHERE rbc.resource_assignment_id = tmp.resource_assignment_id
486 AND rbc.txn_currency_code = tmp.txn_currency_code );
487
488 -- Get distinct workplan ra_ids for later processing.
489 SELECT DISTINCT
490 resource_assignment_id
491 BULK COLLECT
492 INTO l_display_qty_ra_id_tab
493 FROM pa_resource_asgn_curr_tmp;
494
495 -- Populate the display quantity for processed workplan resources
496
497 IF p_pa_debug_mode = 'Y' THEN
498 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
499 P_MSG => 'Before calling PA_BUDGET_LINES_UTILS.' ||
500 'POPULATE_DISPLAY_QTY',
501 --P_CALLED_MODE => p_called_mode,
502 P_MODULE_NAME => l_module_name);
503 END IF;
504 PA_BUDGET_LINES_UTILS.POPULATE_DISPLAY_QTY
505 ( P_BUDGET_VERSION_ID => l_bv_id,
506 P_CONTEXT => 'WORKPLAN',
507 p_resource_assignment_id_tab => l_display_qty_ra_id_tab,
508 X_RETURN_STATUS => x_return_status );
509 IF p_pa_debug_mode = 'Y' THEN
510 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
511 P_MSG => 'After calling PA_BUDGET_LINES_UTILS.' ||
512 'POPULATE_DISPLAY_QTY: '||x_return_status,
513 --P_CALLED_MODE => p_called_mode,
514 P_MODULE_NAME => l_module_name);
515 END IF;
516 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
517 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
518 END IF;
519
520 -- Call the maintenance api in ROLLUP mode
521 IF p_pa_debug_mode = 'Y' THEN
522 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
523 P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.' ||
524 'MAINTAIN_DATA',
525 P_CALLED_MODE => p_calling_mode,
526 P_MODULE_NAME => l_module_name);
527 END IF;
528 PA_RES_ASG_CURRENCY_PUB.MAINTAIN_DATA
529 ( P_FP_COLS_REC => l_fp_cols_rec,
530 P_CALLING_MODULE => 'WORKPLAN',
531 P_ROLLUP_FLAG => 'Y',
532 P_VERSION_LEVEL_FLAG => 'N',
533 P_CALLED_MODE => p_calling_mode,
534 X_RETURN_STATUS => x_return_status,
535 X_MSG_COUNT => x_msg_count,
536 X_MSG_DATA => x_msg_data );
537 IF p_pa_debug_mode = 'Y' THEN
538 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
539 P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.' ||
540 'MAINTAIN_DATA: '||x_return_status,
541 P_CALLED_MODE => p_calling_mode,
542 P_MODULE_NAME => l_module_name);
543 END IF;
544 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
545 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
546 END IF;
547
548 -- END OF IPM: New Entity and Display Quantity ERs --------------------
549
550
551 IF p_pa_debug_mode = 'Y' THEN
552 pa_fp_gen_amount_utils.fp_debug
553 (p_called_mode => p_calling_mode,
554 p_msg => 'Before calling PA_FP_MAINTAIN_ACTUAL_PUB.'
555 ||'BLK_UPD_REPORTING_LINES_WRP',
556 p_module_name => l_module_name,
557 p_log_level => 5);
558 END IF;
559 /*============================================================================+
560 | Bug 4141131: Calling PA_FP_MAINTAIN_ACTUAL_PUB.BLK_UPD_REPORTING_LINES_WRP |
561 | only if P_EXTRACTION_TYPE is 'INCREMENTAL'. |
562 | Bug 4164532: Calling PA_FP_MAINTAIN_ACTUAL_PUB.BLK_UPD_REPORTING_LINES_WRP |
563 | only if NOT (P_CALLING_CONTEXT = 'WP_SUMMARIZED_ACTUAL' |
564 | AND P_EXTRACTION_TYPE = 'INCREMENTAL') |
565 +============================================================================*/
566 IF NOT ( P_CALLING_CONTEXT = 'WP_SUMMARIZED_ACTUAL' AND P_EXTRACTION_TYPE = 'FULL' )
567 THEN
568 PA_FP_MAINTAIN_ACTUAL_PUB.BLK_UPD_REPORTING_LINES_WRP (
569 P_BUDGET_VERSION_ID => l_bv_id,
570 P_ENTIRE_VERSION_FLAG => 'N',
571 P_RES_ASG_ID_TAB => l_ra_id_upd_reprt_tab,
572 P_ACTIVITY_CODE => 'UPDATE',
573 X_RETURN_STATUS => x_return_status,
574 X_MSG_COUNT => x_msg_count,
575 X_MSG_DATA => x_msg_data);
576 IF p_pa_debug_mode = 'Y' THEN
577 pa_fp_gen_amount_utils.fp_debug
578 (p_called_mode => p_calling_mode,
579 p_msg => 'Status after calling PA_FP_MAINTAIN_ACTUAL_PUB.'
580 ||'BLK_UPD_REPORTING_LINES_WRP:'||x_return_status,
581 p_module_name => l_module_name,
582 p_log_level => 5);
583 END IF;
584 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
585 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
586 END IF;
587 END IF; --P_CALLING_CONTEXT = 'WP_SUMMARIZED_ACTUAL' AND P_EXTRACTION_TYPE = 'INCREMENTAL'
588
589 END IF; /*end check for l_ra_id_upd_reprt_tab.count <> 0 */
590
591 END LOOP; /* end loop for p_project_id_tab.count */
592
593 FORALL kk IN 1..P_ACTUALS_THRU_DATE.count
594 UPDATE pa_budget_versions
595 SET etc_start_date = p_actuals_thru_date(kk)+1
596 WHERE budget_version_id = l_bv_id_tab(kk);
597
598 IF p_calling_context = 'WP_APPLY_PROGRESS_TO_WORKING' THEN
599 FORALL m IN 1..l_res_asg_id_tab.count
600 UPDATE pa_resource_assignments
601 SET unplanned_flag = 'N'
602 WHERE resource_assignment_id = l_res_asg_id_tab(m)
603 AND nvl(unplanned_flag,'N') = 'Y';
604 END IF;
605
606 FOR jj in 1..l_bv_id_tab.count LOOP
607 /* Calling the pa_fp_maintain_actual_pub.sync_up_planning_dates api */
608 IF p_pa_debug_mode = 'Y' THEN
609 pa_fp_gen_amount_utils.fp_debug
610 (p_called_mode => p_calling_mode,
611 p_msg => 'Before calling
612 pa_fp_maintain_actual_pub.sync_up_planning_dates',
613 p_module_name => l_module_name,
614 p_log_level => 5);
615 END IF;
616 PA_FP_MAINTAIN_ACTUAL_PUB.SYNC_UP_PLANNING_DATES
617 (P_BUDGET_VERSION_ID => l_bv_id_tab(jj),
618 P_CALLING_CONTEXT => 'SYNC_VERSION_LEVEL',
619 X_RETURN_STATUS => x_return_Status,
620 X_MSG_COUNT => x_msg_count,
621 X_MSG_DATA => x_msg_data );
622 IF p_pa_debug_mode = 'Y' THEN
623 pa_fp_gen_amount_utils.fp_debug
624 (p_called_mode => p_calling_mode,
625 p_msg => 'Status after calling
626 pa_fp_maintain_actual_pub.sync_up_planning_dates'
627 ||x_return_status,
628 p_module_name => l_module_name,
629 p_log_level => 5);
630 END IF;
631 END LOOP;
632
633 -- 5294838 : Added beloe code to delete temp table
634 FOR ss1 IN 1 .. P_PROJECT_ID_TAB.COUNT LOOP
635 delete from PA_PROG_ACT_BY_PERIOD_TEMP where project_id = P_PROJECT_ID_TAB(ss1) and structure_version_id = P_WP_STR_VERSION_ID_TAB(ss1);
636 end loop;
637
638 IF p_pa_debug_mode = 'Y' AND p_init_msg_flag = 'Y' THEN
639 PA_DEBUG.reset_err_stack;
640 ELSIF p_pa_debug_mode = 'Y' AND p_init_msg_flag = 'N' THEN
641 PA_DEBUG.Reset_Curr_Function;
642 END IF;
643
644 EXCEPTION
645 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
646 l_msg_count := FND_MSG_PUB.count_msg;
647 IF l_msg_count = 1 THEN
648 PA_INTERFACE_UTILS_PUB.get_messages
649 ( p_encoded => FND_API.G_TRUE,
650 p_msg_index => 1,
651 p_msg_count => l_msg_count,
652 p_msg_data => l_msg_data,
653 p_data => l_data,
654 p_msg_index_out => l_msg_index_out);
655 x_msg_data := l_data;
656 x_msg_count := l_msg_count;
657 ELSE
658 x_msg_count := l_msg_count;
659 END IF;
660 -- Bug 4621171: Removed ROLLBACK statement.
661
662 x_return_status := FND_API.G_RET_STS_ERROR;
663 IF P_PA_DEBUG_MODE = 'Y' THEN
664 pa_fp_gen_amount_utils.fp_debug
665 (p_called_mode => p_calling_mode,
666 p_msg => 'Invalid Arguments Passed',
667 p_module_name => l_module_name,
668 p_log_level => 5);
669 IF p_init_msg_flag = 'Y' THEN
670 PA_DEBUG.reset_err_stack;
671 ELSIF p_init_msg_flag = 'N' THEN
672 PA_DEBUG.Reset_Curr_Function;
673 END IF;
674 END IF;
675 -- Bug 4621171: Removed RAISE statement.
676 WHEN OTHERS THEN
677 -- Bug 4621171: Removed ROLLBACK statement.
678 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
679 x_msg_count := 1;
680 x_msg_data := substr(sqlerrm,1,240);
681 -- dbms_output.put_line('error msg :'||x_msg_data);
682 FND_MSG_PUB.add_exc_msg
683 ( p_pkg_name => 'PA_FP_MAINTAIN_ACTUAL_PUB',
684 p_procedure_name => 'MAINTAIN_ACTUAL_AMT_WRP',
685 p_error_text => substr(sqlerrm,1,240));
686 IF P_PA_DEBUG_MODE = 'Y' THEN
687 pa_fp_gen_amount_utils.fp_debug
688 (p_called_mode => p_calling_mode,
689 p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
690 p_module_name => l_module_name,
691 p_log_level => 5);
692 IF p_init_msg_flag = 'Y' THEN
693 PA_DEBUG.reset_err_stack;
694 ELSIF p_init_msg_flag = 'N' THEN
695 PA_DEBUG.Reset_Curr_Function;
696 END IF;
697 END IF;
698 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
699
700 END MAINTAIN_ACTUAL_AMT_WRP;
701
702
703 PROCEDURE UPD_REPORTING_LINES_WRP
704 (p_calling_module IN Varchar2
705 ,p_activity_code IN Varchar2
706 ,p_budget_version_id IN Number
707 ,p_resource_assignment_id IN Number
708 ,p_budget_line_id_tab IN pa_plsql_datatypes.IdTabTyp
709 ,p_calling_mode IN varchar2
710 ,x_msg_data OUT NOCOPY Varchar2
711 ,x_msg_count OUT NOCOPY Number
712 ,x_return_status OUT NOCOPY Varchar2) IS
713 l_module_name VARCHAR2(200) :=
714 'pa.plsql.pa_fp_maintain_actual_pub.upd_reporting_lines_wrp';
715 l_count NUMBER;
716 l_msg_count NUMBER;
717 l_cnt NUMBER;
718 l_data VARCHAR2(2000);
719 l_msg_data VARCHAR2(2000);
720 l_msg_index_out NUMBER;
721 BEGIN
722 X_MSG_COUNT := 0;
723 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
724
725 IF p_pa_debug_mode = 'Y' THEN
726 pa_debug.set_curr_function( p_function => 'UPD_REPORTING_LINES_WRP'
727 ,p_debug_mode => p_pa_debug_mode);
728 END IF;
729
730 IF p_budget_line_id_tab.count = 0 THEN
731 IF P_PA_DEBUG_MODE = 'Y' THEN
732 PA_DEBUG.Reset_Curr_Function;
733 END IF;
734 RETURN;
735 END IF;
736
737 FOR jj IN 1..p_budget_line_id_tab.count LOOP
738 IF p_pa_debug_mode = 'Y' THEN
739 pa_fp_gen_amount_utils.fp_debug
740 (p_called_mode => p_calling_mode,
741 p_msg => 'Before calling
742 pa_fp_pji_intg_pkg.update_reporting_lines_frombl',
743 p_module_name => l_module_name,
744 p_log_level => 5);
745 END IF;
746 IF p_pa_debug_mode = 'Y' THEN
747 pa_fp_gen_amount_utils.fp_debug
748 (p_called_mode => p_calling_mode,
749 p_msg => 'Value of budget_line_id b4
750 calling update_reporting_lines_frombl: '
751 ||p_budget_line_id_tab(jj),
752 p_module_name => l_module_name,
753 p_log_level => 5);
754 END IF;
755 PA_FP_PJI_INTG_PKG.UPDATE_REPORTING_LINES_FROMBL
756 (p_calling_module => p_calling_module
757 ,p_activity_code => p_activity_code
758 ,p_budget_version_id => p_budget_version_id
759 ,p_resource_assignment_id => p_resource_assignment_id
760 ,p_budget_line_id => p_budget_line_id_tab(jj)
761 ,x_msg_data => x_msg_data
762 ,x_msg_count => x_msg_count
763 ,x_return_status => x_return_status);
764 IF p_pa_debug_mode = 'Y' THEN
765 pa_fp_gen_amount_utils.fp_debug
766 (p_called_mode => p_calling_mode,
767 p_msg => 'Status after calling
768 pa_fp_pji_intg_pkg.update_reporting_lines_frombl'
769 ||x_return_status,
770 p_module_name => l_module_name,
771 p_log_level => 5);
772 END IF;
773 END LOOP;
774
775 IF P_PA_DEBUG_MODE = 'Y' THEN
776 PA_DEBUG.Reset_Curr_Function;
777 END IF;
778
779 EXCEPTION
780 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
781 l_msg_count := FND_MSG_PUB.count_msg;
782 IF l_msg_count = 1 THEN
783 PA_INTERFACE_UTILS_PUB.get_messages
784 ( p_encoded => FND_API.G_TRUE,
785 p_msg_index => 1,
786 p_msg_count => l_msg_count,
787 p_msg_data => l_msg_data,
788 p_data => l_data,
789 p_msg_index_out => l_msg_index_out);
790 x_msg_data := l_data;
791 x_msg_count := l_msg_count;
792 ELSE
793 x_msg_count := l_msg_count;
794 END IF;
795 -- Bug 4621171: Removed ROLLBACK statement.
796
797 x_return_status := FND_API.G_RET_STS_ERROR;
798 IF P_PA_DEBUG_MODE = 'Y' THEN
799 pa_fp_gen_amount_utils.fp_debug
800 (p_msg => 'Invalid Arguments Passed',
801 p_module_name => l_module_name,
802 p_log_level => 5);
803 PA_DEBUG.Reset_Curr_Function;
804 END IF;
805 -- Bug 4621171: Removed RAISE statement.
806 WHEN OTHERS THEN
807 -- Bug 4621171: Removed ROLLBACK statement.
808 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
809 x_msg_count := 1;
810 x_msg_data := substr(sqlerrm,1,240);
811 -- dbms_output.put_line('error msg :'||x_msg_data);
812 FND_MSG_PUB.add_exc_msg
813 ( p_pkg_name => 'PA_FP_MAINTAIN_ACTUAL_PUB',
814 p_procedure_name => 'UPD_REPORTING_LINES_WRP',
815 p_error_text => substr(sqlerrm,1,240));
816 IF P_PA_DEBUG_MODE = 'Y' THEN
817 pa_fp_gen_amount_utils.fp_debug
818 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
819 p_module_name => l_module_name,
820 p_log_level => 5);
821 PA_DEBUG.Reset_Curr_Function;
822 END IF;
823 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
824
825 END UPD_REPORTING_LINES_WRP;
826
827
828 /**Valid parameters*****
829 *P_CALLING_CONTEXT: WP_PROGRESS -- Work plan progress
830 * WP_SUMMARIZED_ACTUAL -- Work plan summarized actual transactions
831 * FP_GEN_FCST_COPY_ACTUAL -- For Budgeting & Forecasting module.
832 * WP_APPLY_PROGRESS_TO_WORKING
833 *P_TXN_AMT_TYPE_CODE: ACTUAL_TXN (default value) -- Populate Actual Amt to Init columns.
834 * PLANNING_TXN -- Populate Planning Amt to plan columns
835 *P_EXTRACTION_TYPE: FULL -- DEFAULT, indicates full update of existing period
836 * INCREMENTAL -- indicates increment the passed value of existing period
837 *P_OPEN_PD_PLAN_AMT_FLAG: Y -- Leave existing plan qty/amounts as-is for period with end date of
838 * P_OPEN_PD_END_DATE when Context is WP_APPLY_PROGRESS_TO_WORKING.
839 * N (default value) -- Set plan = actual.
840 * NOTE: If Target time phasing is None, the API will override this
841 * parameter with N.
842 *
843 *We currently expect the following Scenarios from the Workplan side:
844 *1. P_CALLING_CONTEXT = WP_APPLY_PROGRESS_TO_WORKING:
845 * -- P_EXTRACTION_TYPE always equals FULL
846 * -- No restriction on structure sharing type.
847 * -- IMPORTANT NOTE: If we start supporting extraction type of INCREMENT in this
848 * -- case, we will need to extend fixes made for Bug 4142150.
849 *2. P_CALLING_CONTEXT = WP_PROGRESS:
850 * -- P_EXTRACTION_TYPE always equals INCREMENTAL
851 * -- The structure cannot be fully shared in this case.
852 *3. P_CALLING_CONTEXT = WP_SUMMARIZED_ACTUAL:
853 * -- P_EXTRACTION_TYPE = FULL the 1st time this API is called (or after a refresh)
854 * -- P_EXTRACTION_TYPE = INCREMENTAL for subsequent calls
855 * -- The structure must be fully shared in this case.
856 *
857 **/
858 PROCEDURE MAINTAIN_ACTUAL_AMT_RA
859 (P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
860 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
861 P_RESOURCE_ASSIGNMENT_ID IN PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE,
862 P_TXN_CURRENCY_CODE IN PA_BUDGET_LINES.TXN_CURRENCY_CODE%TYPE,
863 P_AMT_DTLS_REC_TAB IN PA_FP_MAINTAIN_ACTUAL_PUB.l_amt_dtls_tbl_typ,
864 P_CALLING_CONTEXT IN VARCHAR2,
865 P_TXN_AMT_TYPE_CODE IN VARCHAR2,
866 P_CALLING_MODE IN VARCHAR2,
867 P_EXTRACTION_TYPE IN VARCHAR2,
868 P_OPEN_PD_PLAN_AMT_FLAG IN VARCHAR2,
869 P_OPEN_PD_END_DATE IN DATE,
870 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
871 X_MSG_COUNT OUT NOCOPY NUMBER,
872 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
873
874 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_maintain_actual_pub.maintain_actual_amt_ra';
875 l_period_name_tab PA_PLSQL_DATATYPES.Char30TabTyp;
876 l_start_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
877 l_end_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
878 l_txn_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
879 l_qty_tab PA_PLSQL_DATATYPES.NumTabTyp;
880 l_txn_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
881 l_txn_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
882 l_project_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
883 l_project_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
884 l_project_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
885 l_pfc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
886 l_pfc_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
887 l_pfc_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
888
889 l_ins_period_name_tab PA_PLSQL_DATATYPES.Char30TabTyp;
890 l_ins_start_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
891 l_ins_end_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
892 l_ins_txn_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
893 l_ins_txn_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
894 l_ins_txn_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
895 l_ins_project_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
896 l_ins_proj_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
897 l_ins_project_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
898 l_ins_pfc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
899 l_ins_pfc_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
900 l_ins_pfc_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
901 l_ins_qty_tab PA_PLSQL_DATATYPES.NumTabTyp;
902
903 l_upd_period_name_tab PA_PLSQL_DATATYPES.Char30TabTyp;
904 l_upd_start_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
905 l_upd_end_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
906 l_upd_txn_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
907 l_upd_txn_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
908 l_upd_txn_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
909 l_upd_project_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
910 l_upd_proj_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
911 l_upd_project_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
912 l_upd_pfc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
913 l_upd_pfc_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
914 l_upd_pfc_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
915 l_upd_qty_tab PA_PLSQL_DATATYPES.NumTabTyp;
916
917 l_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE;
918 l_projfunc_cost_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
919 l_projfunc_rev_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
920 l_project_cost_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
921 l_project_rev_rate_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
922
923 /* PL/SQL tables for rate overrides (Added for Bug 4162449) */
924 l_cost_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
925 l_bcost_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
926 l_bill_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
927 l_ins_cost_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
928 l_ins_bcost_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
929 l_ins_bill_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
930 l_upd_cost_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
931 l_upd_bcost_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
932 l_upd_bill_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
933
934 l_last_updated_by NUMBER := FND_GLOBAL.user_id;
935 l_last_update_login NUMBER := FND_GLOBAL.login_id;
936 l_sysdate DATE := SYSDATE;
937
938 l_bdgt_line_id PA_BUDGET_LINES.BUDGET_LINE_ID%TYPE;
939 l_bdgt_line_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
940
941 l_upd_ind NUMBER := 1;
942 l_ins_ind NUMBER := 1;
943
944 l_ins_flag VARCHAR2(1);
945
946 l_unplanned_res_flag PA_RESOURCE_ASSIGNMENTS.UNPLANNED_FLAG%TYPE;
947
948 l_pc_code pa_projects_all.PROJECT_CURRENCY_CODE%TYPE;
949 l_pfc_code pa_projects_all.PROJFUNC_CURRENCY_CODE%TYPE;
950
951 l_spread_curve_id PA_RESOURCE_ASSIGNMENTS.SPREAD_CURVE_ID%TYPE;
952 l_multi_bdgt_lines NUMBER;
953
954 -- Bug 4699248: Replaced l_spread_curve_name with l_spread_curve_code
955 -- throughout this procedure. Also, updated the type accordingly.
956 l_spread_curve_code PA_SPREAD_CURVES_B.SPREAD_CURVE_CODE%TYPE;
957
958 l_bl_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
959
960 l_time_phased_code PA_PROJ_FP_OPTIONS.COST_TIME_PHASED_CODE%TYPE;
961
962 /* Variables for Bug 4142150 */
963
964 l_open_pd_plan_amt_flag VARCHAR2(1);
965
966 -- Scalar variables to store planned amounts
967 l_txn_raw_cost NUMBER;
968 l_txn_burdened_cost NUMBER;
969 l_txn_revenue NUMBER;
970 l_project_raw_cost NUMBER;
971 l_project_burdened_cost NUMBER;
972 l_project_revenue NUMBER;
973 l_raw_cost NUMBER;
974 l_burdened_cost NUMBER;
975 l_revenue NUMBER;
976 l_quantity NUMBER;
977 l_txn_cost_rate_override NUMBER;
978 l_burden_cost_rate_override NUMBER;
979 l_txn_bill_rate_override NUMBER;
980
981 -- PL/SQL tables to stored planned amounts from existing lines
982 l_upd_plan_txn_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
983 l_upd_plan_txn_brdn_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
984 l_upd_plan_txn_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
985 l_upd_plan_proj_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
986 l_upd_plan_proj_brdn_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
987 l_upd_plan_proj_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
988 l_upd_plan_pfc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
989 l_upd_plan_pfc_brdn_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
990 l_upd_plan_pfc_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
991 l_upd_plan_qty_tab PA_PLSQL_DATATYPES.NumTabTyp;
992
993 l_ret_manual_line_flag PA_PROJ_FP_OPTIONS.GEN_COST_RET_MANUAL_LINE_FLAG%TYPE;
994
995 BEGIN
996 --Setting initial values
997 X_MSG_COUNT := 0;
998 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
999
1000 IF p_pa_debug_mode = 'Y' THEN
1001 pa_debug.set_curr_function( p_function => 'MAINTAIN_ACTUAL_AMT_RA'
1002 ,p_debug_mode => p_pa_debug_mode);
1003 END IF;
1004
1005 SELECT NVL(UNPLANNED_FLAG,'N')
1006 INTO l_unplanned_res_flag
1007 FROM pa_resource_assignments
1008 WHERE resource_assignment_id = p_resource_assignment_id;
1009
1010 SELECT project_currency_code,
1011 projfunc_currency_code INTO
1012 l_pc_code, l_pfc_code
1013 FROM pa_projects_all
1014 WHERE project_id = p_project_id;
1015
1016 IF P_CALLING_CONTEXT = 'FP_GEN_FCST_COPY_ACTUAL' THEN
1017
1018 -- ER 4376722: Reverted NVL around dtls_rec amounts for Generation context.
1019 -- This change avoids inserting 0 when NULL was intended. In particular,
1020 -- for non-billable tasks, revenue should be NULL instead of 0. The original
1021 -- reason for adding the NVL around dtls_rec amounts was to avoid nulling
1022 -- out amounts when updating a budget line with the sum of an existing amount
1023 -- and a NULL amount that is passed to this API. We will ensure that existing
1024 -- amounts are not nulled out during updates by adding logic to the UPDATE
1025 -- statements themselves when required.
1026
1027 FOR i in 1..p_amt_dtls_rec_tab.count LOOP
1028 l_period_name_tab(i) := p_amt_dtls_rec_tab(i).period_name;
1029 l_start_date_tab(i) := p_amt_dtls_rec_tab(i).start_date;
1030 l_end_date_tab(i) := p_amt_dtls_rec_tab(i).end_date;
1031 l_txn_raw_cost_tab(i) := p_amt_dtls_rec_tab(i).txn_raw_cost;
1032 l_txn_burdened_cost_tab(i) := p_amt_dtls_rec_tab(i).txn_burdened_cost;
1033 l_txn_revenue_tab(i) := p_amt_dtls_rec_tab(i).txn_revenue;
1034 l_project_raw_cost_tab(i) := p_amt_dtls_rec_tab(i).project_raw_cost;
1035 l_project_burdened_cost_tab(i) := p_amt_dtls_rec_tab(i).project_burdened_cost;
1036 l_project_revenue_tab(i) := p_amt_dtls_rec_tab(i).project_revenue;
1037 l_pfc_raw_cost_tab(i) := p_amt_dtls_rec_tab(i).project_func_raw_cost;
1038 l_pfc_burdened_cost_tab(i) := p_amt_dtls_rec_tab(i).project_func_burdened_cost;
1039 l_pfc_revenue_tab(i) := p_amt_dtls_rec_tab(i).project_func_revenue;
1040 l_qty_tab(i) := p_amt_dtls_rec_tab(i).quantity;
1041 END LOOP;
1042 ELSE -- p_calling_context is a Workplan context
1043 -- Added NVL around dtls_rec amounts during changes for Bug 4292083.
1044 FOR i in 1..p_amt_dtls_rec_tab.count LOOP
1045 l_period_name_tab(i) := p_amt_dtls_rec_tab(i).period_name;
1046 l_start_date_tab(i) := p_amt_dtls_rec_tab(i).start_date;
1047 l_end_date_tab(i) := p_amt_dtls_rec_tab(i).end_date;
1048 l_txn_raw_cost_tab(i) := nvl(p_amt_dtls_rec_tab(i).txn_raw_cost,0);
1049 l_txn_burdened_cost_tab(i) := nvl(p_amt_dtls_rec_tab(i).txn_burdened_cost,0);
1050 l_txn_revenue_tab(i) := nvl(p_amt_dtls_rec_tab(i).txn_revenue,0);
1051 l_project_raw_cost_tab(i) := nvl(p_amt_dtls_rec_tab(i).project_raw_cost,0);
1052 l_project_burdened_cost_tab(i) := nvl(p_amt_dtls_rec_tab(i).project_burdened_cost,0);
1053 l_project_revenue_tab(i) := nvl(p_amt_dtls_rec_tab(i).project_revenue,0);
1054 l_pfc_raw_cost_tab(i) := nvl(p_amt_dtls_rec_tab(i).project_func_raw_cost,0);
1055 l_pfc_burdened_cost_tab(i) := nvl(p_amt_dtls_rec_tab(i).project_func_burdened_cost,0);
1056 l_pfc_revenue_tab(i) := nvl(p_amt_dtls_rec_tab(i).project_func_revenue,0);
1057 l_qty_tab(i) := nvl(p_amt_dtls_rec_tab(i).quantity,0);
1058 END LOOP;
1059 END IF;
1060
1061 SELECT version_type INTO l_version_type
1062 FROM PA_BUDGET_VERSIONS
1063 WHERE budget_version_id = P_BUDGET_VERSION_ID;
1064
1065 SELECT decode(l_version_type,
1066 'COST', opt.cost_time_phased_code,
1067 'REVENUE',opt.revenue_time_phased_code,
1068 'ALL',opt.all_time_phased_code),
1069 decode(l_version_type,
1070 'COST', opt.gen_cost_ret_manual_line_flag,
1071 'REVENUE', opt.gen_rev_ret_manual_line_flag,
1072 'ALL', opt.gen_all_ret_manual_line_flag)
1073 INTO l_time_phased_code,
1074 l_ret_manual_line_flag
1075 FROM pa_proj_fp_options opt
1076 WHERE opt.fin_plan_version_id = p_budget_version_id;
1077
1078 /* Initialize l_open_pd_plan_amt_flag */
1079 IF l_time_phased_code IN ('P','G') THEN
1080 l_open_pd_plan_amt_flag := P_OPEN_PD_PLAN_AMT_FLAG;
1081 ELSE
1082 l_open_pd_plan_amt_flag := 'N';
1083 END IF;
1084
1085 -- Bug 4162449: When p_txn_amt_type_code = PLANNING_TXN (i.e. the
1086 -- context is Average of Actuals), we compute the appropriate
1087 -- override rates and populate them in the budget lines. We perform
1088 -- the computations once and assign the values to the insert and
1089 -- update pl/sql tables as needed.
1090
1091 FOR i in 1..l_period_name_tab.count LOOP
1092 l_cost_rate_override_tab(i) := NULL;
1093 l_bcost_rate_override_tab(i) := NULL;
1094 l_bill_rate_override_tab(i) := NULL;
1095 END LOOP;
1096
1097 IF l_version_type = 'COST' THEN
1098 FOR i in 1..l_period_name_tab.count LOOP
1099 l_projfunc_cost_rate_type_tab(i) := NULL ; --'User' /* Bug 4034089 */
1100 l_projfunc_rev_rate_type_tab(i) := NULL;
1101 l_project_cost_rate_type_tab(i) := NULL ; --'User' /* Bug 4034089 */
1102 l_project_rev_rate_type_tab(i) := NULL;
1103 IF l_qty_tab(i) <> 0 THEN
1104 l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i) / l_qty_tab(i);
1105 l_bcost_rate_override_tab(i) := l_txn_burdened_cost_tab(i) / l_qty_tab(i);
1106 END IF;
1107 END LOOP;
1108 ELSIF l_version_type = 'REVENUE' THEN
1109 FOR i in 1..l_period_name_tab.count LOOP
1110 l_projfunc_cost_rate_type_tab(i) := NULL;
1111 l_projfunc_rev_rate_type_tab(i) := NULL ; --'User' /* Bug 4034089 */
1112 l_project_cost_rate_type_tab(i) := NULL;
1113 l_project_rev_rate_type_tab(i) := NULL ; --'User' /* Bug 4034089 */
1114 IF l_qty_tab(i) <> 0 THEN
1115 l_bill_rate_override_tab(i) := l_txn_revenue_tab(i) / l_qty_tab(i);
1116 END IF;
1117 END LOOP;
1118 ELSIF l_version_type = 'ALL' THEN
1119 FOR i in 1..l_period_name_tab.count LOOP
1120 l_projfunc_cost_rate_type_tab(i) := NULL ; --'User' /* Bug 4034089 */
1121 l_projfunc_rev_rate_type_tab(i) := NULL ; --'User' /* Bug 4034089 */
1122 l_project_cost_rate_type_tab(i) := NULL ; --'User' /* Bug 4034089 */
1123 l_project_rev_rate_type_tab(i) := NULL ; --'User' /* Bug 4034089 */
1124 IF l_qty_tab(i) <> 0 THEN
1125 l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i) / l_qty_tab(i);
1126 l_bcost_rate_override_tab(i) := l_txn_burdened_cost_tab(i) / l_qty_tab(i);
1127 l_bill_rate_override_tab(i) := l_txn_revenue_tab(i) / l_qty_tab(i);
1128 END IF;
1129 END LOOP;
1130 END IF;
1131
1132 BEGIN
1133 SELECT 'N'
1134 INTO l_ins_flag
1135 FROM pa_budget_lines
1136 WHERE resource_assignment_id = p_resource_assignment_id
1137 AND txn_currency_code = p_txn_currency_code
1138 AND rownum < 2;
1139 EXCEPTION
1140 WHEN NO_DATA_FOUND THEN
1141 l_ins_flag := 'Y';
1142 END;
1143
1144 IF p_pa_debug_mode = 'Y' THEN
1145 pa_fp_gen_amount_utils.fp_debug
1146 (p_called_mode => p_calling_mode,
1147 p_msg => 'Value of l_ins_flag after sleecting from pa_budget_line: '||l_ins_flag,
1148 p_module_name => l_module_name,
1149 p_log_level => 5);
1150 END IF;
1151
1152 /* Bulk Insert in PA_BUDGET_LINES table */
1153
1154 -- Bug 4071198: When p_txn_amt_type_code = ACTUAL_TXN (i.e. the
1155 -- context is FP_GEN_FCST_COPY_ACTUAL ), we populate the appropriate
1156 -- override rates in the budget lines. Code changes are tagged with bug# 4071198
1157
1158 IF l_ins_flag = 'Y' THEN
1159 IF (p_txn_amt_type_code = 'ACTUAL_TXN' AND
1160 (p_calling_context = 'WP_APPLY_PROGRESS_TO_WORKING'
1161 OR p_calling_context = 'FP_GEN_FCST_COPY_ACTUAL')) THEN
1162 /* no matter unplanned res flag is Y or N,
1163 the actual values (init cols ) should be copied to
1164 the plan columns. */
1165 IF p_pa_debug_mode = 'Y' THEN
1166 pa_fp_gen_amount_utils.fp_debug
1167 (p_called_mode => p_calling_mode,
1168 p_msg => 'Before inserting into pa_bdgt_lines when l_ins_flag is Y,
1169 p_txn_amt_type_code is ACTUAL_TXN and l_unplanned_res_flag is Y',
1170 p_module_name => l_module_name,
1171 p_log_level => 5);
1172 END IF;
1173 l_bl_id_tab.delete;
1174
1175 -- Bug 4398799: Split original INSERT statement into 2 separate INSERT
1176 -- statements based on p_calling_context. When p_calling_context is
1177 -- 'FP_GEN_FCST_COPY_ACTUAL', we continue populating the rate override
1178 -- columns. In the ELSE case, the context is 'WP_APPLY_PROGRESS_TO_WORKING'
1179 -- so we populate the standard rate columns instead. Everything else about
1180 -- the INSERT statements is unchanged.
1181
1182 IF p_calling_context = 'FP_GEN_FCST_COPY_ACTUAL' THEN
1183 FORALL j in 1..l_period_name_tab.count
1184 INSERT INTO
1185 PA_BUDGET_LINES(BUDGET_VERSION_ID,
1186 RESOURCE_ASSIGNMENT_ID,
1187 PERIOD_NAME,
1188 START_DATE,
1189 END_DATE,
1190 TXN_CURRENCY_CODE,
1191 TXN_INIT_RAW_COST,
1192 TXN_INIT_BURDENED_COST,
1193 TXN_INIT_REVENUE,
1194 PROJECT_INIT_RAW_COST,
1195 PROJECT_INIT_BURDENED_COST,
1196 PROJECT_INIT_REVENUE,
1197 INIT_RAW_COST,
1198 INIT_BURDENED_COST,
1199 INIT_REVENUE,
1200 TXN_RAW_COST,
1201 TXN_BURDENED_COST,
1202 TXN_REVENUE,
1203 PROJECT_RAW_COST,
1204 PROJECT_BURDENED_COST,
1205 PROJECT_REVENUE,
1206 RAW_COST,
1207 BURDENED_COST,
1208 REVENUE,
1209 BUDGET_LINE_ID,
1210 LAST_UPDATE_DATE,
1211 LAST_UPDATED_BY,
1212 CREATION_DATE,
1213 CREATED_BY,
1214 LAST_UPDATE_LOGIN,
1215 QUANTITY,
1216 INIT_QUANTITY,
1217 project_currency_code,
1218 projfunc_currency_code,
1219 TXN_COST_RATE_OVERRIDE, /* Bug 4071198 start */
1220 BURDEN_COST_RATE_OVERRIDE,
1221 TXN_BILL_RATE_OVERRIDE ) /* Bug 4071198 start */
1222 VALUES(p_budget_version_id,
1223 p_resource_assignment_id,
1224 l_period_name_tab(j),
1225 l_start_date_tab(j),
1226 l_end_date_tab(j),
1227 p_txn_currency_code,
1228 l_txn_raw_cost_tab(j),
1229 l_txn_burdened_cost_tab(j),
1230 l_txn_revenue_tab(j),
1231 l_project_raw_cost_tab(j),
1232 l_project_burdened_cost_tab(j),
1233 l_project_revenue_tab(j),
1234 l_pfc_raw_cost_tab(j),
1235 l_pfc_burdened_cost_tab(j),
1236 l_pfc_revenue_tab(j),
1237 l_txn_raw_cost_tab(j),
1238 l_txn_burdened_cost_tab(j),
1239 l_txn_revenue_tab(j),
1240 l_project_raw_cost_tab(j),
1241 l_project_burdened_cost_tab(j),
1242 l_project_revenue_tab(j),
1243 l_pfc_raw_cost_tab(j),
1244 l_pfc_burdened_cost_tab(j),
1245 l_pfc_revenue_tab(j),
1246 PA_BUDGET_LINES_S.nextval,
1247 l_sysdate,
1248 l_last_updated_by,
1249 l_sysdate,
1250 l_last_updated_by,
1251 l_last_update_login,
1252 l_qty_tab(j),
1253 l_qty_tab(j),
1254 l_pc_code,
1255 l_pfc_code ,
1256 l_cost_rate_override_tab(j), /* bug 4071198 */
1257 l_bcost_rate_override_tab(j),
1258 l_bill_rate_override_tab(j)) /* bug 4071198 */
1259 RETURNING budget_line_id BULK COLLECT INTO l_bl_id_tab;
1260 ELSE -- 'WP_APPLY_PROGRESS_TO_WORKING'
1261 FORALL j in 1..l_period_name_tab.count
1262 INSERT INTO
1263 PA_BUDGET_LINES(BUDGET_VERSION_ID,
1264 RESOURCE_ASSIGNMENT_ID,
1265 PERIOD_NAME,
1266 START_DATE,
1267 END_DATE,
1268 TXN_CURRENCY_CODE,
1269 TXN_INIT_RAW_COST,
1270 TXN_INIT_BURDENED_COST,
1271 TXN_INIT_REVENUE,
1272 PROJECT_INIT_RAW_COST,
1273 PROJECT_INIT_BURDENED_COST,
1274 PROJECT_INIT_REVENUE,
1275 INIT_RAW_COST,
1276 INIT_BURDENED_COST,
1277 INIT_REVENUE,
1278 TXN_RAW_COST,
1279 TXN_BURDENED_COST,
1280 TXN_REVENUE,
1281 PROJECT_RAW_COST,
1282 PROJECT_BURDENED_COST,
1283 PROJECT_REVENUE,
1284 RAW_COST,
1285 BURDENED_COST,
1286 REVENUE,
1287 BUDGET_LINE_ID,
1288 LAST_UPDATE_DATE,
1289 LAST_UPDATED_BY,
1290 CREATION_DATE,
1291 CREATED_BY,
1292 LAST_UPDATE_LOGIN,
1293 QUANTITY,
1294 INIT_QUANTITY,
1295 project_currency_code,
1296 projfunc_currency_code,
1297 TXN_STANDARD_COST_RATE, /* Bug 4398799, 4071198 start */
1298 BURDEN_COST_RATE, /* Bug 4398799, 4071198 start */
1299 TXN_STANDARD_BILL_RATE ) /* Bug 4398799, 4071198 start */
1300 VALUES(p_budget_version_id,
1301 p_resource_assignment_id,
1302 l_period_name_tab(j),
1303 l_start_date_tab(j),
1304 l_end_date_tab(j),
1305 p_txn_currency_code,
1306 l_txn_raw_cost_tab(j),
1307 l_txn_burdened_cost_tab(j),
1308 l_txn_revenue_tab(j),
1309 l_project_raw_cost_tab(j),
1310 l_project_burdened_cost_tab(j),
1311 l_project_revenue_tab(j),
1312 l_pfc_raw_cost_tab(j),
1313 l_pfc_burdened_cost_tab(j),
1314 l_pfc_revenue_tab(j),
1315 l_txn_raw_cost_tab(j),
1316 l_txn_burdened_cost_tab(j),
1317 l_txn_revenue_tab(j),
1318 l_project_raw_cost_tab(j),
1319 l_project_burdened_cost_tab(j),
1320 l_project_revenue_tab(j),
1321 l_pfc_raw_cost_tab(j),
1322 l_pfc_burdened_cost_tab(j),
1323 l_pfc_revenue_tab(j),
1324 PA_BUDGET_LINES_S.nextval,
1325 l_sysdate,
1326 l_last_updated_by,
1327 l_sysdate,
1328 l_last_updated_by,
1329 l_last_update_login,
1330 l_qty_tab(j),
1331 l_qty_tab(j),
1332 l_pc_code,
1333 l_pfc_code ,
1334 l_cost_rate_override_tab(j), /* bug 4071198 */
1335 l_bcost_rate_override_tab(j),
1336 l_bill_rate_override_tab(j)) /* bug 4071198 */
1337 RETURNING budget_line_id BULK COLLECT INTO l_bl_id_tab;
1338 END IF; -- calling context check (End Bug 4398799)
1339
1340 IF p_pa_debug_mode = 'Y' THEN
1341 pa_fp_gen_amount_utils.fp_debug
1342 (p_called_mode => p_calling_mode,
1343 p_msg => 'After inserting into pa_bdgt_lines when l_ins_flag is Y,
1344 p_txn_amt_type_code is ACTUAL_TXN and l_unplanned_res_flag is Y',
1345 p_module_name => l_module_name,
1346 p_log_level => 5);
1347 END IF;
1348 ELSIF (p_calling_context = 'WP_PROGRESS' OR
1349 p_calling_context = 'WP_SUMMARIZED_ACTUAL') THEN
1350 --if unplanned res flag is N then
1351 --only the actual values (init cols ) should be populated.
1352 IF p_pa_debug_mode = 'Y' THEN
1353 pa_fp_gen_amount_utils.fp_debug
1354 (p_called_mode => p_calling_mode,
1355 p_msg => 'Before inserting into pa_bdgt_lines when l_ins_flag is Y,
1356 p_txn_amt_type_code is ACTUAL_TXN and l_unplanned_res_flag is N',
1357 p_module_name => l_module_name,
1358 p_log_level => 5);
1359 END IF;
1360
1361 l_bl_id_tab.delete;
1362 FORALL j in 1..l_period_name_tab.count
1363 INSERT INTO
1364 PA_BUDGET_LINES(BUDGET_VERSION_ID,
1365 RESOURCE_ASSIGNMENT_ID,
1366 PERIOD_NAME,
1367 START_DATE,
1368 END_DATE,
1369 TXN_CURRENCY_CODE,
1370 TXN_INIT_RAW_COST,
1371 TXN_INIT_BURDENED_COST,
1372 TXN_INIT_REVENUE,
1373 PROJECT_INIT_RAW_COST,
1374 PROJECT_INIT_BURDENED_COST,
1375 PROJECT_INIT_REVENUE,
1376 INIT_RAW_COST,
1377 INIT_BURDENED_COST,
1378 INIT_REVENUE,
1379 BUDGET_LINE_ID,
1380 LAST_UPDATE_DATE,
1381 LAST_UPDATED_BY,
1382 CREATION_DATE,
1383 CREATED_BY,
1384 LAST_UPDATE_LOGIN,
1385 INIT_QUANTITY,
1386 project_currency_code,
1387 projfunc_currency_code)
1388 VALUES(p_budget_version_id,
1389 p_resource_assignment_id,
1390 l_period_name_tab(j),
1391 l_start_date_tab(j),
1392 l_end_date_tab(j),
1393 p_txn_currency_code,
1394 l_txn_raw_cost_tab(j),
1395 l_txn_burdened_cost_tab(j),
1396 l_txn_revenue_tab(j),
1397 l_project_raw_cost_tab(j),
1398 l_project_burdened_cost_tab(j),
1399 l_project_revenue_tab(j),
1400 l_pfc_raw_cost_tab(j),
1401 l_pfc_burdened_cost_tab(j),
1402 l_pfc_revenue_tab(j),
1403 PA_BUDGET_LINES_S.nextval,
1404 l_sysdate,
1405 l_last_updated_by,
1406 l_sysdate,
1407 l_last_updated_by,
1408 l_last_update_login,
1409 l_qty_tab(j),
1410 l_pc_code,
1411 l_pfc_code );
1412 ELSIF (p_txn_amt_type_code = 'PLANNING_TXN') THEN
1413 IF p_pa_debug_mode = 'Y' THEN
1414 pa_fp_gen_amount_utils.fp_debug
1415 (p_called_mode => p_calling_mode,
1416 p_msg => 'Before inserting into pa_bdgt_lines when l_ins_flag is Y,
1417 p_txn_amt_type_code is PLANNING_TXN and
1418 l_version_type is COST or REVENUE or ALL',
1419 p_module_name => l_module_name,
1420 p_log_level => 5);
1421 END IF;
1422 l_bl_id_tab.delete;
1423 FORALL j2 in 1..l_period_name_tab.count
1424 INSERT INTO
1425 PA_BUDGET_LINES(BUDGET_VERSION_ID,
1426 RESOURCE_ASSIGNMENT_ID,
1427 PERIOD_NAME,
1428 START_DATE,
1429 END_DATE,
1430 TXN_CURRENCY_CODE,
1431 TXN_RAW_COST,
1432 TXN_BURDENED_COST,
1433 TXN_REVENUE,
1434 PROJECT_RAW_COST,
1435 PROJECT_BURDENED_COST,
1436 PROJECT_REVENUE,
1437 RAW_COST,
1438 BURDENED_COST,
1439 REVENUE,
1440 BUDGET_LINE_ID,
1441 LAST_UPDATE_DATE,
1442 LAST_UPDATED_BY,
1443 CREATION_DATE,
1444 CREATED_BY,
1445 LAST_UPDATE_LOGIN,
1446 QUANTITY,
1447 project_currency_code,
1448 projfunc_currency_code,
1449 PROJFUNC_COST_RATE_TYPE,
1450 PROJFUNC_REV_RATE_TYPE,
1451 PROJECT_COST_RATE_TYPE,
1452 PROJECT_REV_RATE_TYPE,
1453 TXN_COST_RATE_OVERRIDE,
1454 BURDEN_COST_RATE_OVERRIDE,
1455 TXN_BILL_RATE_OVERRIDE )
1456 VALUES(p_budget_version_id,
1457 p_resource_assignment_id,
1458 l_period_name_tab(j2),
1459 l_start_date_tab(j2),
1460 l_end_date_tab(j2),
1461 p_txn_currency_code,
1462 l_txn_raw_cost_tab(j2),
1463 l_txn_burdened_cost_tab(j2),
1464 l_txn_revenue_tab(j2),
1465 l_project_raw_cost_tab(j2),
1466 l_project_burdened_cost_tab(j2),
1467 l_project_revenue_tab(j2),
1468 l_pfc_raw_cost_tab(j2),
1469 l_pfc_burdened_cost_tab(j2),
1470 l_pfc_revenue_tab(j2),
1471 PA_BUDGET_LINES_S.nextval,
1472 l_sysdate,
1473 l_last_updated_by,
1474 l_sysdate,
1475 l_last_updated_by,
1476 l_last_update_login,
1477 l_qty_tab(j2),
1478 l_pc_code,
1479 l_pfc_code,
1480 l_projfunc_cost_rate_type_tab(j2),
1481 l_projfunc_rev_rate_type_tab(j2),
1482 l_project_cost_rate_type_tab(j2),
1483 l_project_rev_rate_type_tab(j2),
1484 l_cost_rate_override_tab(j2),
1485 l_bcost_rate_override_tab(j2),
1486 l_bill_rate_override_tab(j2))
1487 RETURNING budget_line_id
1488 BULK COLLECT INTO l_bl_id_tab;
1489 END IF;
1490 /* dbms_output.put_line('No. of rows inserted in
1491 bl table: '||sql%rowcount); */
1492 IF P_PA_DEBUG_MODE = 'Y' THEN
1493 PA_DEBUG.Reset_Curr_Function;
1494 END IF;
1495 RETURN;
1496 ELSIF l_ins_flag = 'N' THEN
1497 FOR k in 1..l_period_name_tab.count LOOP
1498 -- Initialize local variables for this loop iteration
1499 l_bdgt_line_id := null;
1500 l_txn_raw_cost := null;
1501 l_txn_burdened_cost := null;
1502 l_txn_revenue := null;
1503 l_project_raw_cost := null;
1504 l_project_burdened_cost := null;
1505 l_project_revenue := null;
1506 l_raw_cost := null;
1507 l_burdened_cost := null;
1508 l_revenue := null;
1509 l_quantity := null;
1510 l_txn_cost_rate_override := null;
1511 l_burden_cost_rate_override := null;
1512 l_txn_bill_rate_override := null;
1513
1514 BEGIN
1515 IF l_time_phased_code IN ('P','G') THEN
1516 IF l_open_pd_plan_amt_flag = 'Y' AND
1517 l_end_date_tab(k) = p_open_pd_end_date AND
1518 p_extraction_type = 'FULL' AND
1519 p_calling_context = 'WP_APPLY_PROGRESS_TO_WORKING' THEN
1520
1521 -- Bug 4398799: For Workplan flow, modified code to populate
1522 -- standard rate columns instead of rate override columns.
1523
1524 SELECT budget_line_id,
1525 TXN_RAW_COST,
1526 TXN_BURDENED_COST,
1527 TXN_REVENUE,
1528 PROJECT_RAW_COST,
1529 PROJECT_BURDENED_COST,
1530 PROJECT_REVENUE,
1531 RAW_COST,
1532 BURDENED_COST,
1533 REVENUE,
1534 QUANTITY,
1535 TXN_STANDARD_COST_RATE, /* Bug 4398799 */
1536 BURDEN_COST_RATE, /* Bug 4398799 */
1537 TXN_STANDARD_BILL_RATE /* Bug 4398799 */
1538 INTO l_bdgt_line_id,
1539 l_txn_raw_cost,
1540 l_txn_burdened_cost,
1541 l_txn_revenue,
1542 l_project_raw_cost,
1543 l_project_burdened_cost,
1544 l_project_revenue,
1545 l_raw_cost,
1546 l_burdened_cost,
1547 l_revenue,
1548 l_quantity,
1549 l_txn_cost_rate_override,
1550 l_burden_cost_rate_override,
1551 l_txn_bill_rate_override
1552 FROM pa_budget_lines
1553 WHERE resource_assignment_id = p_resource_assignment_id
1554 AND start_date = l_start_date_tab(k)
1555 AND txn_currency_code = p_txn_currency_code;
1556 ELSE
1557 SELECT budget_line_id
1558 INTO l_bdgt_line_id
1559 FROM pa_budget_lines
1560 WHERE resource_assignment_id = p_resource_assignment_id
1561 AND start_date = l_start_date_tab(k)
1562 AND txn_currency_code = p_txn_currency_code;
1563 END IF;
1564 ELSIF l_time_phased_code = 'N' THEN
1565 SELECT budget_line_id
1566 INTO l_bdgt_line_id
1567 FROM pa_budget_lines
1568 WHERE resource_assignment_id = p_resource_assignment_id
1569 AND txn_currency_code = p_txn_currency_code;
1570 END IF;
1571
1572 l_bdgt_line_id_tab(l_upd_ind) := l_bdgt_line_id;
1573
1574 l_upd_period_name_tab(l_upd_ind) := l_period_name_tab(k);
1575 l_upd_start_date_tab(l_upd_ind) := l_start_date_tab(k);
1576 l_upd_end_date_tab(l_upd_ind) := l_end_date_tab(k);
1577 l_upd_txn_raw_cost_tab(l_upd_ind) := l_txn_raw_cost_tab(k);
1578 l_upd_txn_burdened_cost_tab(l_upd_ind) := l_txn_burdened_cost_tab(k);
1579 l_upd_txn_revenue_tab(l_upd_ind) := l_txn_revenue_tab(k);
1580 l_upd_project_raw_cost_tab(l_upd_ind) := l_project_raw_cost_tab(k);
1581 l_upd_proj_burdened_cost_tab(l_upd_ind) := l_project_burdened_cost_tab(k);
1582 l_upd_project_revenue_tab(l_upd_ind) := l_project_revenue_tab(k);
1583 l_upd_pfc_raw_cost_tab(l_upd_ind) := l_pfc_raw_cost_tab(k);
1584 l_upd_pfc_burdened_cost_tab(l_upd_ind) := l_pfc_burdened_cost_tab(k);
1585 l_upd_pfc_revenue_tab(l_upd_ind) := l_pfc_revenue_tab(k);
1586 l_upd_qty_tab(l_upd_ind) := l_qty_tab(k);
1587
1588 -- Bug 4142150: If the following conditions are met, we apply the actuals
1589 -- but leave the Plan amounts as-is for the given period:
1590 -- 1. Calling Context is WP_APPLY_PROGRESS_TO_WORKING,
1591 -- 2. Extraction Type is FULL
1592 -- 3. Actual Through Date falls prior to the End Date of its period
1593 -- 4. Target time phase is PA or GL
1594 -- 5. Actual Quantity <= Plan Quantity
1595 -- 6. Given period = Actuals Through Date period (p_open_pd_end_date)
1596 -- NOTE: Currently, Condition 1 implies Condition 2. However, if the
1597 -- Workplan team starts to use Extraction Type = Incremental in conjunction
1598 -- with Condition 1, this bug fix will need to be extended to cover that case.
1599
1600 IF l_open_pd_plan_amt_flag = 'Y' AND
1601 p_extraction_type = 'FULL' AND
1602 p_calling_context = 'WP_APPLY_PROGRESS_TO_WORKING' THEN
1603 -- If Actual <= Plan, then we will update Actual amounts and leave
1604 -- the Plan amounts alone. If Actual > Plan, then we will update
1605 -- Actual amounts and set Plan = Actual.
1606 IF l_qty_tab(k) <= l_quantity AND
1607 l_open_pd_plan_amt_flag = 'Y' AND
1608 l_end_date_tab(k) = p_open_pd_end_date THEN
1609
1610 l_upd_plan_txn_raw_cost_tab(l_upd_ind) := l_txn_raw_cost;
1611 l_upd_plan_txn_brdn_cost_tab(l_upd_ind) := l_txn_burdened_cost;
1612 l_upd_plan_txn_revenue_tab(l_upd_ind) := l_txn_revenue;
1613 l_upd_plan_proj_raw_cost_tab(l_upd_ind) := l_project_raw_cost;
1614 l_upd_plan_proj_brdn_cost_tab(l_upd_ind) := l_project_burdened_cost;
1615 l_upd_plan_proj_revenue_tab(l_upd_ind) := l_project_revenue;
1616 l_upd_plan_pfc_raw_cost_tab(l_upd_ind) := l_raw_cost;
1617 l_upd_plan_pfc_brdn_cost_tab(l_upd_ind) := l_burdened_cost;
1618 l_upd_plan_pfc_revenue_tab(l_upd_ind) := l_revenue;
1619 l_upd_plan_qty_tab(l_upd_ind) := l_quantity;
1620
1621 l_upd_cost_rate_override_tab(l_upd_ind) := l_txn_cost_rate_override;
1622 l_upd_bcost_rate_override_tab(l_upd_ind) := l_burden_cost_rate_override;
1623 l_upd_bill_rate_override_tab(l_upd_ind) := l_txn_bill_rate_override;
1624 ELSE
1625 l_upd_plan_txn_raw_cost_tab(l_upd_ind) := l_txn_raw_cost_tab(k);
1626 l_upd_plan_txn_brdn_cost_tab(l_upd_ind) := l_txn_burdened_cost_tab(k);
1627 l_upd_plan_txn_revenue_tab(l_upd_ind) := l_txn_revenue_tab(k);
1628 l_upd_plan_proj_raw_cost_tab(l_upd_ind) := l_project_raw_cost_tab(k);
1629 l_upd_plan_proj_brdn_cost_tab(l_upd_ind) := l_project_burdened_cost_tab(k);
1630 l_upd_plan_proj_revenue_tab(l_upd_ind) := l_project_revenue_tab(k);
1631 l_upd_plan_pfc_raw_cost_tab(l_upd_ind) := l_pfc_raw_cost_tab(k);
1632 l_upd_plan_pfc_brdn_cost_tab(l_upd_ind) := l_pfc_burdened_cost_tab(k);
1633 l_upd_plan_pfc_revenue_tab(l_upd_ind) := l_pfc_revenue_tab(k);
1634 l_upd_plan_qty_tab(l_upd_ind) := l_qty_tab(k);
1635
1636 -- Assign pre-computed override rates to pl/sql update tables.
1637 l_upd_cost_rate_override_tab(l_upd_ind) := l_cost_rate_override_tab(k);
1638 l_upd_bcost_rate_override_tab(l_upd_ind) := l_bcost_rate_override_tab(k);
1639 l_upd_bill_rate_override_tab(l_upd_ind) := l_bill_rate_override_tab(k);
1640 END IF;
1641 ELSE
1642 -- Assign pre-computed override rates to pl/sql update tables.
1643 l_upd_cost_rate_override_tab(l_upd_ind) := l_cost_rate_override_tab(k);
1644 l_upd_bcost_rate_override_tab(l_upd_ind) := l_bcost_rate_override_tab(k);
1645 l_upd_bill_rate_override_tab(l_upd_ind) := l_bill_rate_override_tab(k);
1646 END IF;
1647
1648 l_upd_ind := l_upd_ind + 1;
1649 EXCEPTION
1650 WHEN no_data_found THEN
1651 l_ins_period_name_tab(l_ins_ind) := l_period_name_tab(k);
1652 l_ins_start_date_tab(l_ins_ind) := l_start_date_tab(k);
1653 l_ins_end_date_tab(l_ins_ind) := l_end_date_tab(k);
1654 l_ins_txn_raw_cost_tab(l_ins_ind) := l_txn_raw_cost_tab(k);
1655 l_ins_txn_burdened_cost_tab(l_ins_ind) := l_txn_burdened_cost_tab(k);
1656 l_ins_txn_revenue_tab(l_ins_ind) := l_txn_revenue_tab(k);
1657 l_ins_project_raw_cost_tab(l_ins_ind) := l_project_raw_cost_tab(k);
1658 l_ins_proj_burdened_cost_tab(l_ins_ind) := l_project_burdened_cost_tab(k);
1659 l_ins_project_revenue_tab(l_ins_ind) := l_project_revenue_tab(k);
1660 l_ins_pfc_raw_cost_tab(l_ins_ind) := l_pfc_raw_cost_tab(k);
1661 l_ins_pfc_burdened_cost_tab(l_ins_ind) := l_pfc_burdened_cost_tab(k);
1662 l_ins_pfc_revenue_tab(l_ins_ind) := l_pfc_revenue_tab(k);
1663 l_ins_qty_tab(l_ins_ind) := l_qty_tab(k);
1664
1665 -- Assign pre-computed override rates to pl/sql insert tables.
1666 l_ins_cost_rate_override_tab(l_ins_ind) := l_cost_rate_override_tab(k);
1667 l_ins_bcost_rate_override_tab(l_ins_ind) := l_bcost_rate_override_tab(k);
1668 l_ins_bill_rate_override_tab(l_ins_ind) := l_bill_rate_override_tab(k);
1669
1670 l_ins_ind := l_ins_ind + 1;
1671 END;
1672
1673 l_bdgt_line_id := null;
1674
1675 END LOOP;
1676
1677 /* dbms_output.put_line('Update count when the ins_flag is null:
1678 '|| l_upd_period_name_tab.count);*/
1679
1680 /* Bulk Update in PA_BUDGET_LINES table */
1681 IF l_upd_period_name_tab.count > 0 THEN
1682 IF (p_txn_amt_type_code = 'ACTUAL_TXN' AND
1683 (p_calling_context = 'WP_APPLY_PROGRESS_TO_WORKING'
1684 OR p_calling_context = 'FP_GEN_FCST_COPY_ACTUAL')) THEN
1685
1686 IF p_calling_context = 'WP_APPLY_PROGRESS_TO_WORKING'
1687 AND P_EXTRACTION_TYPE = 'INCREMENTAL' THEN
1688 FORALL m in 1..l_upd_period_name_tab.count
1689 UPDATE pa_budget_lines
1690 SET
1691 TXN_INIT_RAW_COST = NVL(TXN_INIT_RAW_COST,0) + l_upd_txn_raw_cost_tab(m),
1692 TXN_INIT_BURDENED_COST = NVL(TXN_INIT_BURDENED_COST,0) + l_upd_txn_burdened_cost_tab(m),
1693 TXN_INIT_REVENUE = NVL(TXN_INIT_REVENUE,0) + l_upd_txn_revenue_tab(m),
1694 PROJECT_INIT_RAW_COST = NVL(PROJECT_INIT_RAW_COST,0) + l_upd_project_raw_cost_tab(m),
1695 PROJECT_INIT_BURDENED_COST = NVL(PROJECT_INIT_BURDENED_COST,0) + l_upd_proj_burdened_cost_tab(m),
1696 PROJECT_INIT_REVENUE = NVL(PROJECT_INIT_REVENUE,0) + l_upd_project_revenue_tab(m),
1697 INIT_RAW_COST = NVL(INIT_RAW_COST,0) + l_upd_pfc_raw_cost_tab(m),
1698 INIT_BURDENED_COST = NVL(INIT_BURDENED_COST,0) + l_upd_pfc_burdened_cost_tab(m),
1699 INIT_REVENUE = NVL(INIT_REVENUE,0) + l_upd_pfc_revenue_tab(m),
1700 TXN_RAW_COST = NVL(TXN_RAW_COST,0) + l_upd_txn_raw_cost_tab(m),
1701 TXN_BURDENED_COST = NVL(TXN_BURDENED_COST,0) + l_upd_txn_burdened_cost_tab(m),
1702 TXN_REVENUE = NVL(TXN_REVENUE,0) + l_upd_txn_revenue_tab(m),
1703 PROJECT_RAW_COST = NVL(PROJECT_RAW_COST,0) + l_upd_project_raw_cost_tab(m),
1704 PROJECT_BURDENED_COST = NVL(PROJECT_BURDENED_COST,0) + l_upd_proj_burdened_cost_tab(m),
1705 PROJECT_REVENUE = NVL(PROJECT_REVENUE,0) + l_upd_project_revenue_tab(m),
1706 RAW_COST = NVL(RAW_COST,0) + l_upd_pfc_raw_cost_tab(m),
1707 BURDENED_COST = NVL(BURDENED_COST,0) + l_upd_pfc_burdened_cost_tab(m),
1708 REVENUE = NVL(REVENUE,0) + l_upd_pfc_revenue_tab(m),
1709 LAST_UPDATE_DATE = l_sysdate,
1710 LAST_UPDATED_BY = l_last_updated_by,
1711 CREATION_DATE = l_sysdate,
1712 CREATED_BY = l_last_updated_by,
1713 LAST_UPDATE_LOGIN = l_last_update_login,
1714 QUANTITY = NVL(QUANTITY,0) + l_upd_qty_tab(m),
1715 INIT_QUANTITY = NVL(INIT_QUANTITY,0) + l_upd_qty_tab(m)
1716 WHERE budget_line_id = l_bdgt_line_id_tab(m);
1717
1718 -- Bug 4398799: For Workplan flow, modified code to populate
1719 -- standard rate columns instead of rate override columns.
1720
1721 /* bug 4071198 start */
1722 /* bug 4398799 start */
1723 IF l_version_type = 'COST' THEN
1724
1725 FORALL m in 1..l_upd_period_name_tab.count
1726 UPDATE pa_budget_lines SET
1727 TXN_STANDARD_COST_RATE = decode (nvl(quantity,0), 0, NULL, txn_raw_cost / quantity),
1728 BURDEN_COST_RATE = decode (nvl(quantity,0), 0, NULL, txn_burdened_cost/quantity)
1729 WHERE budget_line_id = l_bdgt_line_id_tab(m);
1730
1731 ELSIF l_version_type = 'REVENUE' THEN
1732
1733 FORALL m in 1..l_upd_period_name_tab.count
1734 UPDATE pa_budget_lines SET
1735 TXN_STANDARD_BILL_RATE = decode (nvl(quantity,0), 0, NULL, txn_revenue/quantity)
1736 WHERE budget_line_id = l_bdgt_line_id_tab(m);
1737
1738 ELSIF l_version_type = 'ALL' THEN
1739
1740 FORALL m in 1..l_upd_period_name_tab.count
1741 UPDATE pa_budget_lines SET
1742 TXN_STANDARD_COST_RATE = decode (nvl(quantity,0), 0, NULL, txn_raw_cost / quantity),
1743 BURDEN_COST_RATE = decode (nvl(quantity,0), 0, NULL, txn_burdened_cost/quantity),
1744 TXN_STANDARD_BILL_RATE = decode (nvl(quantity,0), 0, NULL, txn_revenue/quantity)
1745 WHERE budget_line_id = l_bdgt_line_id_tab(m);
1746
1747 END IF;
1748 /* bug 4398799 end */
1749 /* bug 4071198 end */
1750
1751 -- Added this condition and Update as part of fix for Bug 4142150.
1752 -- Note that we do not need to check the Manual lines flag and
1753 -- time phase here, since l_open_pd_plan_amt_flag = 'Y' implies
1754 -- that time phase is PA or GL.
1755 ELSIF l_open_pd_plan_amt_flag = 'Y' AND
1756 p_calling_context = 'WP_APPLY_PROGRESS_TO_WORKING' AND
1757 P_EXTRACTION_TYPE = 'FULL' THEN
1758 FORALL m in 1..l_upd_period_name_tab.count
1759 UPDATE pa_budget_lines
1760 SET
1761 TXN_INIT_RAW_COST = l_upd_txn_raw_cost_tab(m),
1762 TXN_INIT_BURDENED_COST = l_upd_txn_burdened_cost_tab(m),
1763 TXN_INIT_REVENUE = l_upd_txn_revenue_tab(m),
1764 PROJECT_INIT_RAW_COST = l_upd_project_raw_cost_tab(m),
1765 PROJECT_INIT_BURDENED_COST = l_upd_proj_burdened_cost_tab(m),
1766 PROJECT_INIT_REVENUE = l_upd_project_revenue_tab(m),
1767 INIT_RAW_COST = l_upd_pfc_raw_cost_tab(m),
1768 INIT_BURDENED_COST = l_upd_pfc_burdened_cost_tab(m),
1769 INIT_REVENUE = l_upd_pfc_revenue_tab(m),
1770 TXN_RAW_COST = l_upd_plan_txn_raw_cost_tab(m),
1771 TXN_BURDENED_COST = l_upd_plan_txn_brdn_cost_tab(m),
1772 TXN_REVENUE = l_upd_plan_txn_revenue_tab(m),
1773 PROJECT_RAW_COST = l_upd_plan_proj_raw_cost_tab(m),
1774 PROJECT_BURDENED_COST = l_upd_plan_proj_brdn_cost_tab(m),
1775 PROJECT_REVENUE = l_upd_plan_proj_revenue_tab(m),
1776 RAW_COST = l_upd_plan_pfc_raw_cost_tab(m),
1777 BURDENED_COST = l_upd_plan_pfc_brdn_cost_tab(m),
1778 REVENUE = l_upd_plan_pfc_revenue_tab(m),
1779 LAST_UPDATE_DATE = l_sysdate,
1780 LAST_UPDATED_BY = l_last_updated_by,
1781 CREATION_DATE = l_sysdate,
1782 CREATED_BY = l_last_updated_by,
1783 LAST_UPDATE_LOGIN = l_last_update_login,
1784 QUANTITY = l_upd_plan_qty_tab(m),
1785 INIT_QUANTITY = l_upd_qty_tab(m),
1786 TXN_STANDARD_COST_RATE = l_upd_cost_rate_override_tab(m), /* Bug 4398799, 4071198 start */
1787 BURDEN_COST_RATE = l_upd_bcost_rate_override_tab(m),
1788 TXN_STANDARD_BILL_RATE = l_upd_bill_rate_override_tab(m) /* Bug 4398799, 4071198 end */
1789 WHERE budget_line_id = l_bdgt_line_id_tab(m);
1790 ELSE
1791 -- Bug 4232253 : Rev. forecast incorrect for NTP with retain Manually ordered line.
1792 If l_ret_manual_line_flag = 'Y' AND l_time_phased_code = 'N' then
1793
1794 -- Bug 4292083: When the Target timephase is None, update the plan
1795 -- columns with total amounts (Actual + Planning_Txn). Since we no
1796 -- longer call the UPDATE_TOTAL_PLAN_AMTS API in the Forecast Gen
1797 -- wrapper, we need to modify the update logic here.
1798
1799 -- ER 4376722: Split original UPDATE statement into 2 separate UPDATE
1800 -- statements based on p_calling_context. When p_calling_context is
1801 -- 'FP_GEN_FCST_COPY_ACTUAL', changed the update logic as follows:
1802 -- Before: Set amount = NVL(existing amount,0) + update amount.
1803 -- After: If existing amount is null, then set amount = update amount.
1804 -- If existing amount is not null, then
1805 -- set amount = existing amount + NVL(update amount, 0)
1806 -- The new logic preserves the non-null existing amounts.
1807 -- This change is necessary in case update revenue is Null. Using the
1808 -- old logic, we would set revenue to NVL(existing revenue,0) + Null,
1809 -- which is just Null. In other words, the existing revenue would be lost.
1810 -- Using the new logic, we would set revenue to existing revenue +
1811 -- NVL(NULL,0) = existing revenue. In this case, the existing amounts
1812 -- are manually added.
1813 -- In the ELSE case, the context is 'WP_APPLY_PROGRESS_TO_WORKING'
1814 -- so we use the same UPDATE statement as before to avoid changing
1815 -- Workplan behavior.
1816
1817 IF p_calling_context = 'FP_GEN_FCST_COPY_ACTUAL' THEN
1818 FORALL m in 1..l_upd_period_name_tab.count
1819 UPDATE pa_budget_lines
1820 SET -- Begin 4376722, 4292083 changes --
1821 TXN_RAW_COST =
1822 DECODE(TXN_RAW_COST, null, l_upd_txn_raw_cost_tab(m),
1823 TXN_RAW_COST + NVL(l_upd_txn_raw_cost_tab(m),0)),
1824 TXN_BURDENED_COST =
1825 DECODE(TXN_BURDENED_COST, null, l_upd_txn_burdened_cost_tab(m),
1826 TXN_BURDENED_COST + NVL(l_upd_txn_burdened_cost_tab(m),0)),
1827 TXN_REVENUE =
1828 DECODE(TXN_REVENUE, null, l_upd_txn_revenue_tab(m),
1829 TXN_REVENUE + NVL(l_upd_txn_revenue_tab(m),0)),
1830 PROJECT_RAW_COST =
1831 DECODE(PROJECT_RAW_COST, null, l_upd_project_raw_cost_tab(m),
1832 PROJECT_RAW_COST + NVL(l_upd_project_raw_cost_tab(m),0)),
1833 PROJECT_BURDENED_COST =
1834 DECODE(PROJECT_BURDENED_COST, null, l_upd_proj_burdened_cost_tab(m),
1835 PROJECT_BURDENED_COST + NVL(l_upd_proj_burdened_cost_tab(m),0)),
1836 PROJECT_REVENUE =
1837 DECODE(PROJECT_REVENUE, null, l_upd_project_revenue_tab(m),
1838 PROJECT_REVENUE + NVL(l_upd_project_revenue_tab(m),0)),
1839 RAW_COST =
1840 DECODE(RAW_COST, null, l_upd_pfc_raw_cost_tab(m),
1841 RAW_COST + NVL(l_upd_pfc_raw_cost_tab(m),0)),
1842 BURDENED_COST =
1843 DECODE(BURDENED_COST, null, l_upd_pfc_burdened_cost_tab(m),
1844 BURDENED_COST + NVL(l_upd_pfc_burdened_cost_tab(m),0)),
1845 REVENUE =
1846 DECODE(REVENUE, null, l_upd_pfc_revenue_tab(m),
1847 REVENUE + NVL(l_upd_pfc_revenue_tab(m),0)),
1848 QUANTITY =
1849 DECODE(QUANTITY, null, l_upd_qty_tab(m),
1850 QUANTITY + NVL(l_upd_qty_tab(m),0)),
1851 -- End 4376722, 4292083 changes --
1852 TXN_INIT_RAW_COST = l_upd_txn_raw_cost_tab(m),
1853 TXN_INIT_BURDENED_COST = l_upd_txn_burdened_cost_tab(m),
1854 TXN_INIT_REVENUE = l_upd_txn_revenue_tab(m),
1855 PROJECT_INIT_RAW_COST = l_upd_project_raw_cost_tab(m),
1856 PROJECT_INIT_BURDENED_COST = l_upd_proj_burdened_cost_tab(m),
1857 PROJECT_INIT_REVENUE = l_upd_project_revenue_tab(m),
1858 INIT_RAW_COST = l_upd_pfc_raw_cost_tab(m),
1859 INIT_BURDENED_COST = l_upd_pfc_burdened_cost_tab(m),
1860 INIT_REVENUE = l_upd_pfc_revenue_tab(m),
1861 LAST_UPDATE_DATE = l_sysdate,
1862 LAST_UPDATED_BY = l_last_updated_by,
1863 CREATION_DATE = l_sysdate,
1864 CREATED_BY = l_last_updated_by,
1865 LAST_UPDATE_LOGIN = l_last_update_login,
1866 INIT_QUANTITY = l_upd_qty_tab(m)
1867 WHERE budget_line_id = l_bdgt_line_id_tab(m);
1868 ELSE -- 'WP_APPLY_PROGRESS_TO_WORKING'
1869 FORALL m in 1..l_upd_period_name_tab.count
1870 UPDATE pa_budget_lines
1871 SET -- Begin 4292083 changes --
1872 TXN_RAW_COST = NVL(TXN_RAW_COST,0)
1873 + l_upd_txn_raw_cost_tab(m),
1874 TXN_BURDENED_COST = NVL(TXN_BURDENED_COST,0)
1875 + l_upd_txn_burdened_cost_tab(m),
1876 TXN_REVENUE = NVL(TXN_REVENUE,0)
1877 + l_upd_txn_revenue_tab(m),
1878 PROJECT_RAW_COST = NVL(PROJECT_RAW_COST,0)
1879 + l_upd_project_raw_cost_tab(m),
1880 PROJECT_BURDENED_COST = NVL(PROJECT_BURDENED_COST,0)
1881 + l_upd_proj_burdened_cost_tab(m),
1882 PROJECT_REVENUE = NVL(PROJECT_REVENUE,0)
1883 + l_upd_project_revenue_tab(m),
1884 RAW_COST = NVL(RAW_COST,0)
1885 + l_upd_pfc_raw_cost_tab(m),
1886 BURDENED_COST = NVL(BURDENED_COST,0)
1887 + l_upd_pfc_burdened_cost_tab(m),
1888 REVENUE = NVL(REVENUE,0)
1889 + l_upd_pfc_revenue_tab(m),
1890 QUANTITY = NVL(QUANTITY,0) + l_upd_qty_tab(m),
1891 -- End 4292083 changes --
1892 TXN_INIT_RAW_COST = l_upd_txn_raw_cost_tab(m),
1893 TXN_INIT_BURDENED_COST = l_upd_txn_burdened_cost_tab(m),
1894 TXN_INIT_REVENUE = l_upd_txn_revenue_tab(m),
1895 PROJECT_INIT_RAW_COST = l_upd_project_raw_cost_tab(m),
1896 PROJECT_INIT_BURDENED_COST = l_upd_proj_burdened_cost_tab(m),
1897 PROJECT_INIT_REVENUE = l_upd_project_revenue_tab(m),
1898 INIT_RAW_COST = l_upd_pfc_raw_cost_tab(m),
1899 INIT_BURDENED_COST = l_upd_pfc_burdened_cost_tab(m),
1900 INIT_REVENUE = l_upd_pfc_revenue_tab(m),
1901 LAST_UPDATE_DATE = l_sysdate,
1902 LAST_UPDATED_BY = l_last_updated_by,
1903 CREATION_DATE = l_sysdate,
1904 CREATED_BY = l_last_updated_by,
1905 LAST_UPDATE_LOGIN = l_last_update_login,
1906 INIT_QUANTITY = l_upd_qty_tab(m)
1907 WHERE budget_line_id = l_bdgt_line_id_tab(m);
1908 END IF; -- p_calling_context check for ER 4376722
1909
1910 ELSE -- l_ret_manual_line_flag <> 'Y' OR l_time_phased_code <> 'N'
1911
1912 -- Bug 4398799: Split original UPDATE statement into 2 separate UPDATE
1913 -- statements based on p_calling_context. When p_calling_context is
1914 -- 'FP_GEN_FCST_COPY_ACTUAL', we continue populating the rate override
1915 -- columns. In the ELSE case, the context is 'WP_APPLY_PROGRESS_TO_WORKING'
1916 -- so we populate the standard rate columns instead. Everything else about
1917 -- the UPDATE statements is unchanged.
1918
1919 IF p_calling_context = 'FP_GEN_FCST_COPY_ACTUAL' THEN
1920 FORALL m in 1..l_upd_period_name_tab.count
1921 UPDATE pa_budget_lines
1922 SET
1923 TXN_INIT_RAW_COST = l_upd_txn_raw_cost_tab(m),
1924 TXN_INIT_BURDENED_COST = l_upd_txn_burdened_cost_tab(m),
1925 TXN_INIT_REVENUE = l_upd_txn_revenue_tab(m),
1926 PROJECT_INIT_RAW_COST = l_upd_project_raw_cost_tab(m),
1927 PROJECT_INIT_BURDENED_COST = l_upd_proj_burdened_cost_tab(m),
1928 PROJECT_INIT_REVENUE = l_upd_project_revenue_tab(m),
1929 INIT_RAW_COST = l_upd_pfc_raw_cost_tab(m),
1930 INIT_BURDENED_COST = l_upd_pfc_burdened_cost_tab(m),
1931 INIT_REVENUE = l_upd_pfc_revenue_tab(m),
1932 TXN_RAW_COST = l_upd_txn_raw_cost_tab(m),
1933 TXN_BURDENED_COST = l_upd_txn_burdened_cost_tab(m),
1934 TXN_REVENUE = l_upd_txn_revenue_tab(m),
1935 PROJECT_RAW_COST = l_upd_project_raw_cost_tab(m),
1936 PROJECT_BURDENED_COST = l_upd_proj_burdened_cost_tab(m),
1937 PROJECT_REVENUE = l_upd_project_revenue_tab(m),
1938 RAW_COST = l_upd_pfc_raw_cost_tab(m),
1939 BURDENED_COST = l_upd_pfc_burdened_cost_tab(m),
1940 REVENUE = l_upd_pfc_revenue_tab(m),
1941 LAST_UPDATE_DATE = l_sysdate,
1942 LAST_UPDATED_BY = l_last_updated_by,
1943 CREATION_DATE = l_sysdate,
1944 CREATED_BY = l_last_updated_by,
1945 LAST_UPDATE_LOGIN = l_last_update_login,
1946 QUANTITY = l_upd_qty_tab(m),
1947 INIT_QUANTITY = l_upd_qty_tab(m),
1948 TXN_COST_RATE_OVERRIDE = l_upd_cost_rate_override_tab(m), /* Bug 4071198 start */
1949 BURDEN_COST_RATE_OVERRIDE = l_upd_bcost_rate_override_tab(m),
1950 TXN_BILL_RATE_OVERRIDE = l_upd_bill_rate_override_tab(m) /* Bug 4071198 end */
1951 WHERE budget_line_id = l_bdgt_line_id_tab(m);
1952 ELSE -- 'WP_APPLY_PROGRESS_TO_WORKING'
1953 FORALL m in 1..l_upd_period_name_tab.count
1954 UPDATE pa_budget_lines
1955 SET
1956 TXN_INIT_RAW_COST = l_upd_txn_raw_cost_tab(m),
1957 TXN_INIT_BURDENED_COST = l_upd_txn_burdened_cost_tab(m),
1958 TXN_INIT_REVENUE = l_upd_txn_revenue_tab(m),
1959 PROJECT_INIT_RAW_COST = l_upd_project_raw_cost_tab(m),
1960 PROJECT_INIT_BURDENED_COST = l_upd_proj_burdened_cost_tab(m),
1961 PROJECT_INIT_REVENUE = l_upd_project_revenue_tab(m),
1962 INIT_RAW_COST = l_upd_pfc_raw_cost_tab(m),
1963 INIT_BURDENED_COST = l_upd_pfc_burdened_cost_tab(m),
1964 INIT_REVENUE = l_upd_pfc_revenue_tab(m),
1965 TXN_RAW_COST = l_upd_txn_raw_cost_tab(m),
1966 TXN_BURDENED_COST = l_upd_txn_burdened_cost_tab(m),
1967 TXN_REVENUE = l_upd_txn_revenue_tab(m),
1968 PROJECT_RAW_COST = l_upd_project_raw_cost_tab(m),
1969 PROJECT_BURDENED_COST = l_upd_proj_burdened_cost_tab(m),
1970 PROJECT_REVENUE = l_upd_project_revenue_tab(m),
1971 RAW_COST = l_upd_pfc_raw_cost_tab(m),
1972 BURDENED_COST = l_upd_pfc_burdened_cost_tab(m),
1973 REVENUE = l_upd_pfc_revenue_tab(m),
1974 LAST_UPDATE_DATE = l_sysdate,
1975 LAST_UPDATED_BY = l_last_updated_by,
1976 CREATION_DATE = l_sysdate,
1977 CREATED_BY = l_last_updated_by,
1978 LAST_UPDATE_LOGIN = l_last_update_login,
1979 QUANTITY = l_upd_qty_tab(m),
1980 INIT_QUANTITY = l_upd_qty_tab(m),
1981 TXN_STANDARD_COST_RATE = l_upd_cost_rate_override_tab(m), /* Bug 4398799, 4071198 start */
1982 BURDEN_COST_RATE = l_upd_bcost_rate_override_tab(m),
1983 TXN_STANDARD_BILL_RATE = l_upd_bill_rate_override_tab(m) /* Bug 4398799, 4071198 end */
1984 WHERE budget_line_id = l_bdgt_line_id_tab(m);
1985 END IF; -- calling context check (End Bug 4398799)
1986
1987 End If; -- If l_time_phased_code = 'N'
1988 END IF;
1989
1990 ELSIF (p_calling_context = 'WP_PROGRESS' OR
1991 p_calling_context = 'WP_SUMMARIZED_ACTUAL') THEN
1992
1993 IF P_EXTRACTION_TYPE = 'INCREMENTAL' THEN
1994 FORALL m in 1..l_upd_period_name_tab.count
1995 UPDATE pa_budget_lines
1996 SET
1997 TXN_INIT_RAW_COST = NVL(TXN_INIT_RAW_COST,0) + l_upd_txn_raw_cost_tab(m),
1998 TXN_INIT_BURDENED_COST = NVL(TXN_INIT_BURDENED_COST,0) + l_upd_txn_burdened_cost_tab(m),
1999 TXN_INIT_REVENUE = NVL(TXN_INIT_REVENUE,0) + l_upd_txn_revenue_tab(m),
2000 PROJECT_INIT_RAW_COST = NVL(PROJECT_INIT_RAW_COST,0) + l_upd_project_raw_cost_tab(m),
2001 PROJECT_INIT_BURDENED_COST = NVL(PROJECT_INIT_BURDENED_COST,0) + l_upd_proj_burdened_cost_tab(m),
2002 PROJECT_INIT_REVENUE = NVL(PROJECT_INIT_REVENUE,0) + l_upd_project_revenue_tab(m),
2003 INIT_RAW_COST = NVL(INIT_RAW_COST,0) + l_upd_pfc_raw_cost_tab(m),
2004 INIT_BURDENED_COST = NVL(INIT_BURDENED_COST,0) + l_upd_pfc_burdened_cost_tab(m),
2005 INIT_REVENUE = NVL(INIT_REVENUE,0) + l_upd_pfc_revenue_tab(m),
2006 LAST_UPDATE_DATE = l_sysdate,
2007 LAST_UPDATED_BY = l_last_updated_by,
2008 CREATION_DATE = l_sysdate,
2009 CREATED_BY = l_last_updated_by,
2010 LAST_UPDATE_LOGIN = l_last_update_login,
2011 INIT_QUANTITY = NVL(INIT_QUANTITY,0) + l_upd_qty_tab(m)
2012 WHERE budget_line_id = l_bdgt_line_id_tab(m);
2013 ELSE
2014 FORALL m in 1..l_upd_period_name_tab.count
2015 UPDATE pa_budget_lines
2016 SET
2017 TXN_INIT_RAW_COST = l_upd_txn_raw_cost_tab(m),
2018 TXN_INIT_BURDENED_COST = l_upd_txn_burdened_cost_tab(m),
2019 TXN_INIT_REVENUE = l_upd_txn_revenue_tab(m),
2020 PROJECT_INIT_RAW_COST = l_upd_project_raw_cost_tab(m),
2021 PROJECT_INIT_BURDENED_COST = l_upd_proj_burdened_cost_tab(m),
2022 PROJECT_INIT_REVENUE = l_upd_project_revenue_tab(m),
2023 INIT_RAW_COST = l_upd_pfc_raw_cost_tab(m),
2024 INIT_BURDENED_COST = l_upd_pfc_burdened_cost_tab(m),
2025 INIT_REVENUE = l_upd_pfc_revenue_tab(m),
2026 LAST_UPDATE_DATE = l_sysdate,
2027 LAST_UPDATED_BY = l_last_updated_by,
2028 CREATION_DATE = l_sysdate,
2029 CREATED_BY = l_last_updated_by,
2030 LAST_UPDATE_LOGIN = l_last_update_login,
2031 INIT_QUANTITY = l_upd_qty_tab(m)
2032 WHERE budget_line_id = l_bdgt_line_id_tab(m);
2033 END IF;
2034 ELSIF p_txn_amt_type_code = 'PLANNING_TXN' THEN
2035
2036 -- Bug 4292083: When the Target timephase is None, update the plan
2037 -- columns with total amounts (Actual + Planning_Txn).
2038 -- Assumptions:
2039 -- 1) MAINTAIN_ACTUAL_AMT_RA is only called in the context of
2040 -- Forecast Generation
2041 -- 2) When the Retain Manually Added Plan Lines option is enabled,
2042 -- this API is called with p_txn_amt_type_code = 'PLANNING_TXN'
2043 -- only for resources that are not manually added.
2044
2045 IF l_time_phased_code = 'N' AND
2046 p_calling_context = 'FP_GEN_FCST_COPY_ACTUAL' THEN
2047
2048 -- ER 4376722: Changed the update logic as follows:
2049 -- Before: Set amount = NVL(actual amount,0) + update amount.
2050 -- After: If actual amount is null, then set amount = update amount.
2051 -- If actual amount is not null, then
2052 -- set amount = actual amount + NVL(update amount, 0)
2053 -- The new logic preserves the non-null actual amounts.
2054 -- This change is necessary in case update revenue is Null. Using the
2055 -- old logic, we would set revenue to NVL(actual revenue,0) + Null,
2056 -- which is just Null. In other words, the actual revenue would be lost.
2057 -- Using the new logic, we would set revenue to actual revenue +
2058 -- NVL(NULL,0) = actual revenue.
2059
2060 FORALL m2 in 1..l_upd_period_name_tab.count
2061 UPDATE pa_budget_lines
2062 SET
2063 TXN_RAW_COST =
2064 DECODE(TXN_INIT_RAW_COST, null, l_upd_txn_raw_cost_tab(m2),
2065 TXN_INIT_RAW_COST + NVL(l_upd_txn_raw_cost_tab(m2),0)),
2066 TXN_BURDENED_COST =
2067 DECODE(TXN_INIT_BURDENED_COST, null, l_upd_txn_burdened_cost_tab(m2),
2068 TXN_INIT_BURDENED_COST + NVL(l_upd_txn_burdened_cost_tab(m2),0)),
2069 TXN_REVENUE =
2070 DECODE(TXN_INIT_REVENUE, null, l_upd_txn_revenue_tab(m2),
2071 TXN_INIT_REVENUE + NVL(l_upd_txn_revenue_tab(m2),0)),
2072 PROJECT_RAW_COST =
2073 DECODE(PROJECT_INIT_RAW_COST, null, l_upd_project_raw_cost_tab(m2),
2074 PROJECT_INIT_RAW_COST + NVL(l_upd_project_raw_cost_tab(m2),0)),
2075 PROJECT_BURDENED_COST =
2076 DECODE(PROJECT_INIT_BURDENED_COST, null, l_upd_proj_burdened_cost_tab(m2),
2077 PROJECT_INIT_BURDENED_COST + NVL(l_upd_proj_burdened_cost_tab(m2),0)),
2078 PROJECT_REVENUE =
2079 DECODE(PROJECT_INIT_REVENUE, null, l_upd_project_revenue_tab(m2),
2080 PROJECT_INIT_REVENUE + NVL(l_upd_project_revenue_tab(m2),0)),
2081 RAW_COST =
2082 DECODE(INIT_RAW_COST, null, l_upd_pfc_raw_cost_tab(m2),
2083 INIT_RAW_COST + NVL(l_upd_pfc_raw_cost_tab(m2),0)),
2084 BURDENED_COST =
2085 DECODE(INIT_BURDENED_COST, null, l_upd_pfc_burdened_cost_tab(m2),
2086 INIT_BURDENED_COST + NVL(l_upd_pfc_burdened_cost_tab(m2),0)),
2087 REVENUE =
2088 DECODE(INIT_REVENUE, null, l_upd_pfc_revenue_tab(m2),
2089 INIT_REVENUE + NVL(l_upd_pfc_revenue_tab(m2),0)),
2090 LAST_UPDATE_DATE = l_sysdate,
2091 LAST_UPDATED_BY = l_last_updated_by,
2092 CREATION_DATE = l_sysdate,
2093 CREATED_BY = l_last_updated_by,
2094 LAST_UPDATE_LOGIN = l_last_update_login,
2095 QUANTITY =
2096 DECODE(INIT_QUANTITY, null, l_upd_qty_tab(m2),
2097 INIT_QUANTITY + NVL(l_upd_qty_tab(m2),0)),
2098 PROJFUNC_COST_RATE_TYPE = l_projfunc_cost_rate_type_tab(m2),
2099 PROJFUNC_REV_RATE_TYPE = l_projfunc_rev_rate_type_tab(m2),
2100 PROJECT_COST_RATE_TYPE = l_project_cost_rate_type_tab(m2),
2101 PROJECT_REV_RATE_TYPE = l_project_rev_rate_type_tab(m2),
2102 TXN_COST_RATE_OVERRIDE = l_upd_cost_rate_override_tab(m2),
2103 BURDEN_COST_RATE_OVERRIDE = l_upd_bcost_rate_override_tab(m2),
2104 TXN_BILL_RATE_OVERRIDE = l_upd_bill_rate_override_tab(m2)
2105 WHERE budget_line_id = l_bdgt_line_id_tab(m2);
2106 ELSE
2107 FORALL m2 in 1..l_upd_period_name_tab.count
2108 UPDATE pa_budget_lines
2109 SET
2110 TXN_RAW_COST = l_upd_txn_raw_cost_tab(m2),
2111 TXN_BURDENED_COST = l_upd_txn_burdened_cost_tab(m2),
2112 TXN_REVENUE = l_upd_txn_revenue_tab(m2),
2113 PROJECT_RAW_COST = l_upd_project_raw_cost_tab(m2),
2114 PROJECT_BURDENED_COST = l_upd_proj_burdened_cost_tab(m2),
2115 PROJECT_REVENUE = l_upd_project_revenue_tab(m2),
2116 RAW_COST = l_upd_pfc_raw_cost_tab(m2),
2117 BURDENED_COST = l_upd_pfc_burdened_cost_tab(m2),
2118 REVENUE = l_upd_pfc_revenue_tab(m2),
2119 LAST_UPDATE_DATE = l_sysdate,
2120 LAST_UPDATED_BY = l_last_updated_by,
2121 CREATION_DATE = l_sysdate,
2122 CREATED_BY = l_last_updated_by,
2123 LAST_UPDATE_LOGIN = l_last_update_login,
2124 QUANTITY = l_upd_qty_tab(m2),
2125 PROJFUNC_COST_RATE_TYPE = l_projfunc_cost_rate_type_tab(m2),
2126 PROJFUNC_REV_RATE_TYPE = l_projfunc_rev_rate_type_tab(m2),
2127 PROJECT_COST_RATE_TYPE = l_project_cost_rate_type_tab(m2),
2128 PROJECT_REV_RATE_TYPE = l_project_rev_rate_type_tab(m2),
2129 TXN_COST_RATE_OVERRIDE = l_upd_cost_rate_override_tab(m2),
2130 BURDEN_COST_RATE_OVERRIDE = l_upd_bcost_rate_override_tab(m2),
2131 TXN_BILL_RATE_OVERRIDE = l_upd_bill_rate_override_tab(m2)
2132 WHERE budget_line_id = l_bdgt_line_id_tab(m2);
2133 END IF; -- None timephase check
2134
2135 END IF;
2136 END IF;
2137
2138 /* dbms_output.put_line('Insert count when the ins_flag is null:
2139 '|| l_ins_period_name_tab.count);*/
2140 --Bulk Insert in PA_BUDGET_LINES table
2141
2142 IF l_ins_period_name_tab.count > 0 THEN
2143 IF (p_txn_amt_type_code = 'ACTUAL_TXN' AND
2144 (p_calling_context = 'WP_APPLY_PROGRESS_TO_WORKING'
2145 OR p_calling_context = 'FP_GEN_FCST_COPY_ACTUAL')) THEN
2146
2147 l_bl_id_tab.delete;
2148
2149 -- Bug 4398799: Split original INSERT statement into 2 separate INSERT
2150 -- statements based on p_calling_context. When p_calling_context is
2151 -- 'FP_GEN_FCST_COPY_ACTUAL', we continue populating the rate override
2152 -- columns. In the ELSE case, the context is 'WP_APPLY_PROGRESS_TO_WORKING'
2153 -- so we populate the standard rate columns instead. Everything else about
2154 -- the INSERT statements is unchanged.
2155
2156 IF p_calling_context = 'FP_GEN_FCST_COPY_ACTUAL' THEN
2157 FORALL n in 1..l_ins_period_name_tab.count
2158 INSERT INTO
2159 PA_BUDGET_LINES(BUDGET_VERSION_ID,
2160 RESOURCE_ASSIGNMENT_ID,
2161 PERIOD_NAME,
2162 START_DATE,
2163 END_DATE,
2164 TXN_CURRENCY_CODE,
2165 TXN_INIT_RAW_COST,
2166 TXN_INIT_BURDENED_COST,
2167 TXN_INIT_REVENUE,
2168 PROJECT_INIT_RAW_COST,
2169 PROJECT_INIT_BURDENED_COST,
2170 PROJECT_INIT_REVENUE,
2171 INIT_RAW_COST,
2172 INIT_BURDENED_COST,
2173 INIT_REVENUE,
2174 TXN_RAW_COST,
2175 TXN_BURDENED_COST,
2176 TXN_REVENUE,
2177 PROJECT_RAW_COST,
2178 PROJECT_BURDENED_COST,
2179 PROJECT_REVENUE,
2180 RAW_COST,
2181 BURDENED_COST,
2182 REVENUE,
2183 BUDGET_LINE_ID,
2184 LAST_UPDATE_DATE,
2185 LAST_UPDATED_BY,
2186 CREATION_DATE,
2187 CREATED_BY,
2188 LAST_UPDATE_LOGIN,
2189 INIT_QUANTITY,
2190 project_currency_code,
2191 projfunc_currency_code,
2192 QUANTITY,
2193 TXN_COST_RATE_OVERRIDE, /* Bug 4071198 */
2194 BURDEN_COST_RATE_OVERRIDE,
2195 TXN_BILL_RATE_OVERRIDE ) /* Bug 4071198 */
2196 VALUES(p_budget_version_id,
2197 p_resource_assignment_id,
2198 l_ins_period_name_tab(n),
2199 l_ins_start_date_tab(n),
2200 l_ins_end_date_tab(n),
2201 p_txn_currency_code,
2202 l_ins_txn_raw_cost_tab(n),
2203 l_ins_txn_burdened_cost_tab(n),
2204 l_ins_txn_revenue_tab(n),
2205 l_ins_project_raw_cost_tab(n),
2206 l_ins_proj_burdened_cost_tab(n),
2207 l_ins_project_revenue_tab(n),
2208 l_ins_pfc_raw_cost_tab(n),
2209 l_ins_pfc_burdened_cost_tab(n),
2210 l_ins_pfc_revenue_tab(n),
2211 l_ins_txn_raw_cost_tab(n),
2212 l_ins_txn_burdened_cost_tab(n),
2213 l_ins_txn_revenue_tab(n),
2214 l_ins_project_raw_cost_tab(n),
2215 l_ins_proj_burdened_cost_tab(n),
2216 l_ins_project_revenue_tab(n),
2217 l_ins_pfc_raw_cost_tab(n),
2218 l_ins_pfc_burdened_cost_tab(n),
2219 l_ins_pfc_revenue_tab(n),
2220 PA_BUDGET_LINES_S.nextval,
2221 l_sysdate,
2222 l_last_updated_by,
2223 l_sysdate,
2224 l_last_updated_by,
2225 l_last_update_login,
2226 l_ins_qty_tab(n),
2227 l_pc_code,
2228 l_pfc_code,
2229 l_ins_qty_tab(n) ,
2230 l_ins_cost_rate_override_tab(n), /* Bug 4071198 */
2231 l_ins_bcost_rate_override_tab(n), /* Bug 4071198 */
2232 l_ins_bill_rate_override_tab(n)) /* Bug 4071198 */
2233 RETURNING budget_line_id
2234 BULK COLLECT INTO l_bl_id_tab;
2235 ELSE -- 'WP_APPLY_PROGRESS_TO_WORKING'
2236 FORALL n in 1..l_ins_period_name_tab.count
2237 INSERT INTO
2238 PA_BUDGET_LINES(BUDGET_VERSION_ID,
2239 RESOURCE_ASSIGNMENT_ID,
2240 PERIOD_NAME,
2241 START_DATE,
2242 END_DATE,
2243 TXN_CURRENCY_CODE,
2244 TXN_INIT_RAW_COST,
2245 TXN_INIT_BURDENED_COST,
2246 TXN_INIT_REVENUE,
2247 PROJECT_INIT_RAW_COST,
2248 PROJECT_INIT_BURDENED_COST,
2249 PROJECT_INIT_REVENUE,
2250 INIT_RAW_COST,
2251 INIT_BURDENED_COST,
2252 INIT_REVENUE,
2253 TXN_RAW_COST,
2254 TXN_BURDENED_COST,
2255 TXN_REVENUE,
2256 PROJECT_RAW_COST,
2257 PROJECT_BURDENED_COST,
2258 PROJECT_REVENUE,
2259 RAW_COST,
2260 BURDENED_COST,
2261 REVENUE,
2262 BUDGET_LINE_ID,
2263 LAST_UPDATE_DATE,
2264 LAST_UPDATED_BY,
2265 CREATION_DATE,
2266 CREATED_BY,
2267 LAST_UPDATE_LOGIN,
2268 INIT_QUANTITY,
2269 project_currency_code,
2270 projfunc_currency_code,
2271 QUANTITY,
2272 TXN_STANDARD_COST_RATE, /* Bug 4398799, 4071198 */
2273 BURDEN_COST_RATE, /* Bug 4398799, 4071198 */
2274 TXN_STANDARD_BILL_RATE ) /* Bug 4398799, 4071198 */
2275 VALUES(p_budget_version_id,
2276 p_resource_assignment_id,
2277 l_ins_period_name_tab(n),
2278 l_ins_start_date_tab(n),
2279 l_ins_end_date_tab(n),
2280 p_txn_currency_code,
2281 l_ins_txn_raw_cost_tab(n),
2282 l_ins_txn_burdened_cost_tab(n),
2283 l_ins_txn_revenue_tab(n),
2284 l_ins_project_raw_cost_tab(n),
2285 l_ins_proj_burdened_cost_tab(n),
2286 l_ins_project_revenue_tab(n),
2287 l_ins_pfc_raw_cost_tab(n),
2288 l_ins_pfc_burdened_cost_tab(n),
2289 l_ins_pfc_revenue_tab(n),
2290 l_ins_txn_raw_cost_tab(n),
2291 l_ins_txn_burdened_cost_tab(n),
2292 l_ins_txn_revenue_tab(n),
2293 l_ins_project_raw_cost_tab(n),
2294 l_ins_proj_burdened_cost_tab(n),
2295 l_ins_project_revenue_tab(n),
2296 l_ins_pfc_raw_cost_tab(n),
2297 l_ins_pfc_burdened_cost_tab(n),
2298 l_ins_pfc_revenue_tab(n),
2299 PA_BUDGET_LINES_S.nextval,
2300 l_sysdate,
2301 l_last_updated_by,
2302 l_sysdate,
2303 l_last_updated_by,
2304 l_last_update_login,
2305 l_ins_qty_tab(n),
2306 l_pc_code,
2307 l_pfc_code,
2308 l_ins_qty_tab(n) ,
2309 l_ins_cost_rate_override_tab(n), /* Bug 4071198 */
2310 l_ins_bcost_rate_override_tab(n), /* Bug 4071198 */
2311 l_ins_bill_rate_override_tab(n)) /* Bug 4071198 */
2312 RETURNING budget_line_id
2313 BULK COLLECT INTO l_bl_id_tab;
2314 END IF; -- calling context check (End Bug 4398799)
2315
2316 ELSIF (p_calling_context = 'WP_PROGRESS' OR
2317 p_calling_context = 'WP_SUMMARIZED_ACTUAL') THEN
2318 l_bl_id_tab.delete;
2319 FORALL n in 1..l_ins_period_name_tab.count
2320 INSERT INTO
2321 PA_BUDGET_LINES(BUDGET_VERSION_ID,
2322 RESOURCE_ASSIGNMENT_ID,
2323 PERIOD_NAME,
2324 START_DATE,
2325 END_DATE,
2326 TXN_CURRENCY_CODE,
2327 TXN_INIT_RAW_COST,
2328 TXN_INIT_BURDENED_COST,
2329 TXN_INIT_REVENUE,
2330 PROJECT_INIT_RAW_COST,
2331 PROJECT_INIT_BURDENED_COST,
2332 PROJECT_INIT_REVENUE,
2333 INIT_RAW_COST,
2334 INIT_BURDENED_COST,
2335 INIT_REVENUE,
2336 BUDGET_LINE_ID,
2337 LAST_UPDATE_DATE,
2338 LAST_UPDATED_BY,
2339 CREATION_DATE,
2340 CREATED_BY,
2341 LAST_UPDATE_LOGIN,
2342 INIT_QUANTITY,
2343 project_currency_code,
2344 projfunc_currency_code)
2345 VALUES(p_budget_version_id,
2346 p_resource_assignment_id,
2347 l_ins_period_name_tab(n),
2348 l_ins_start_date_tab(n),
2349 l_ins_end_date_tab(n),
2350 p_txn_currency_code,
2351 l_ins_txn_raw_cost_tab(n),
2352 l_ins_txn_burdened_cost_tab(n),
2353 l_ins_txn_revenue_tab(n),
2354 l_ins_project_raw_cost_tab(n),
2355 l_ins_proj_burdened_cost_tab(n),
2356 l_ins_project_revenue_tab(n),
2357 l_ins_pfc_raw_cost_tab(n),
2358 l_ins_pfc_burdened_cost_tab(n),
2359 l_ins_pfc_revenue_tab(n),
2360 PA_BUDGET_LINES_S.nextval,
2361 l_sysdate,
2362 l_last_updated_by,
2363 l_sysdate,
2364 l_last_updated_by,
2365 l_last_update_login,
2366 l_ins_qty_tab(n),
2367 l_pc_code,
2368 l_pfc_code );
2369 ELSIF p_txn_amt_type_code = 'PLANNING_TXN' THEN
2370 l_bl_id_tab.delete;
2371 FORALL n2 in 1..l_ins_period_name_tab.count
2372 INSERT INTO
2373 PA_BUDGET_LINES(BUDGET_VERSION_ID,
2374 RESOURCE_ASSIGNMENT_ID,
2375 PERIOD_NAME,
2376 START_DATE,
2377 END_DATE,
2378 TXN_CURRENCY_CODE,
2379 TXN_RAW_COST,
2380 TXN_BURDENED_COST,
2381 TXN_REVENUE,
2382 PROJECT_RAW_COST,
2383 PROJECT_BURDENED_COST,
2384 PROJECT_REVENUE,
2385 RAW_COST,
2386 BURDENED_COST,
2387 REVENUE,
2388 BUDGET_LINE_ID,
2389 LAST_UPDATE_DATE,
2390 LAST_UPDATED_BY,
2391 CREATION_DATE,
2392 CREATED_BY,
2393 LAST_UPDATE_LOGIN,
2394 QUANTITY,
2395 project_currency_code,
2396 projfunc_currency_code,
2397 PROJFUNC_COST_RATE_TYPE,
2398 PROJFUNC_REV_RATE_TYPE,
2399 PROJECT_COST_RATE_TYPE,
2400 PROJECT_REV_RATE_TYPE,
2401 TXN_COST_RATE_OVERRIDE,
2402 BURDEN_COST_RATE_OVERRIDE,
2403 TXN_BILL_RATE_OVERRIDE )
2404 VALUES(p_budget_version_id,
2405 p_resource_assignment_id,
2406 l_ins_period_name_tab(n2),
2407 l_ins_start_date_tab(n2),
2408 l_ins_end_date_tab(n2),
2409 p_txn_currency_code,
2410 l_ins_txn_raw_cost_tab(n2),
2411 l_ins_txn_burdened_cost_tab(n2),
2412 l_ins_txn_revenue_tab(n2),
2413 l_ins_project_raw_cost_tab(n2),
2414 l_ins_proj_burdened_cost_tab(n2),
2415 l_ins_project_revenue_tab(n2),
2416 l_ins_pfc_raw_cost_tab(n2),
2417 l_ins_pfc_burdened_cost_tab(n2),
2418 l_ins_pfc_revenue_tab(n2),
2419 PA_BUDGET_LINES_S.nextval,
2420 l_sysdate,
2421 l_last_updated_by,
2422 l_sysdate,
2423 l_last_updated_by,
2424 l_last_update_login,
2425 l_ins_qty_tab(n2),
2426 l_pc_code,
2427 l_pfc_code,
2428 l_projfunc_cost_rate_type_tab(n2),
2429 l_projfunc_rev_rate_type_tab(n2),
2430 l_project_cost_rate_type_tab(n2),
2431 l_project_rev_rate_type_tab(n2),
2432 l_ins_cost_rate_override_tab(n2),
2433 l_ins_bcost_rate_override_tab(n2),
2434 l_ins_bill_rate_override_tab(n2))
2435 RETURNING budget_line_id
2436 BULK COLLECT INTO l_bl_id_tab;
2437 END IF;
2438 END IF;
2439 END IF;
2440
2441 /* if the spread curve is Fixed Date then there should be only one
2442 budget line for the planning resource for the txn currency and
2443 period name combination. If we are going to collect actuals for
2444 more than one period then the spread curve and the SP_fixed_date
2445 column should be nulled out. - msoundra */
2446 l_spread_curve_code := 'dummy';
2447 /* If the spread curve is Even then spread_curve_id
2448 and sp_fixed_date are nullified in res asg table */
2449 BEGIN
2450 -- Bug 4699248: Modified the SELECT statement below to use the
2451 -- 'pa_spread_curves_b' table instead of 'pa_spread_curves_tl'.
2452 -- As a result, the query fetches the spread curve code instead
2453 -- of the spread curve name for the given resource.
2454
2455 SELECT ra.spread_curve_id,t.spread_curve_code
2456 INTO l_spread_curve_id,l_spread_curve_code
2457 FROM pa_resource_assignments ra,pa_spread_curves_b t
2458 WHERE ra.resource_assignment_id = p_resource_assignment_id
2459 AND ra.spread_curve_id = t.spread_curve_id;
2460 EXCEPTION
2461 WHEN NO_DATA_FOUND THEN
2462 l_spread_curve_code := 'dummy';
2463 END;
2464
2465 -- Bug 4699248: Modified the IF statement below to check for
2466 -- the value 'FIXED_DATE' instead of 'Fixed Date'.
2467
2468 IF l_spread_curve_code = 'FIXED_DATE' THEN
2469 --Getting the number of budget lines for the given res_asg_id and txn_curr_code
2470 SELECT count(*)
2471 INTO l_multi_bdgt_lines
2472 FROM pa_budget_lines
2473 WHERE resource_assignment_id = p_resource_assignment_id
2474 AND txn_currency_code = p_txn_currency_code;
2475
2476 --Need to update res asg table if there are multiple budget lines
2477 IF l_multi_bdgt_lines > 1 THEN
2478 UPDATE pa_resource_assignments
2479 SET spread_curve_id = NULL,
2480 sp_fixed_date = NULL
2481 WHERE resource_assignment_id = p_resource_assignment_id;
2482 END IF;
2483 END IF;
2484
2485 IF p_pa_debug_mode = 'Y' THEN
2486 PA_DEBUG.Reset_Curr_Function;
2487 END IF;
2488
2489 EXCEPTION
2490 WHEN OTHERS THEN
2491 -- Bug 4621171: Removed ROLLBACK statement.
2492 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2493 x_msg_count := 1;
2494 x_msg_data := substr(sqlerrm,1,240);
2495 -- dbms_output.put_line('error msg :'||x_msg_data);
2496 FND_MSG_PUB.add_exc_msg
2497 ( p_pkg_name => 'PA_FP_MAINTAIN_ACTUAL_PUB',
2498 p_procedure_name => 'MAINTAIN_ACTUAL_AMT_RA',
2499 p_error_text => substr(sqlerrm,1,240));
2500 IF P_PA_DEBUG_MODE = 'Y' THEN
2501 pa_fp_gen_amount_utils.fp_debug
2502 (p_called_mode => p_calling_mode,
2503 p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
2504 p_module_name => l_module_name,
2505 p_log_level => 5);
2506 PA_DEBUG.Reset_Curr_Function;
2507 END IF;
2508 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2509
2510 END MAINTAIN_ACTUAL_AMT_RA;
2511
2512
2513 /**
2514 * This procedure synchs up planning dates for target resources in the
2515 * pa_resource_assignments table based upon the p_calling_context parameter.
2516 *
2517 * Valid P_CALLING_CONTEXT values are
2518 * 'SYNC_VERSION_LEVEL'
2519 * 'COPY_ACTUALS'
2520 * 'GEN_COMMITMENTS'
2521 * 'GEN_BILLING_EVENTS'
2522 *
2523 * Following are descriptions for how this procedure behaves in each context.
2524 *
2525 * SYNC_VERSION_LEVEL : This is the Default context. All target resources are
2526 * included in this operation. For a given resource, if
2527 * the minimum budget line start date is prior to the
2528 * planning start date period, set the planning start date
2529 * to the minimum budget line start date. If the maximum
2530 * budget line end date is after the planning end date
2531 * period, set the planning end date to the maximum budget
2532 * line end date.When the target is None timpehased, then
2533 * compare the min/max budget line dates directly with the
2534 * planning start/end dates (instead of their periods).
2535 *
2536 * COPY_ACTUALS : Has the same behavior as SYNC_VERSION_LEVEL, with the
2537 * exception that only target resources with actuals are
2538 * considered for synching. The pji_fm_xbs_accum_tmp1
2539 * table should be populated before calling this procedure.
2540 *
2541 * GEN_COMMITMENTS : Target resources with commitments are considered.
2542 * The temp table pa_res_list_map_tmp4 must be populated.
2543 * Please see the Technical Design for details:
2544 *
2545 * http://files.oraclecorp.com/content/MySharedFolders/
2546 * Projects%20Development%20-%20Projects/30.Family%20Pack
2547 * %20M/Budgeting%20and%20Forecasting/3.0%20Design/
2548 * Functional%20Design/B%26F%20Code%20Changes%20After
2549 * %20Oct%2031st/
2550 *
2551 * GEN_BILLING_EVENTS : Has the same behavior as GEN_COMMITMENTS, with the
2552 * exception that only target resources with billing
2553 * events are considered for synching.
2554 */
2555 PROCEDURE SYNC_UP_PLANNING_DATES
2556 (P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
2557 P_CALLING_CONTEXT IN VARCHAR2,
2558 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2559 X_MSG_COUNT OUT NOCOPY NUMBER,
2560 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
2561
2562 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_maintain_actual_pub.sync_up_planning_dates';
2563
2564 l_fp_cols_rec PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
2565
2566 /* Cursors for Version Level sync by target time phase */
2567
2568 CURSOR all_tgt_res_dates_pa_cursor IS
2569 SELECT bl.resource_assignment_id,
2570 ra.planning_start_date,
2571 ra.planning_end_date,
2572 start_pd.start_date,
2573 end_pd.end_date,
2574 min(bl.start_date),
2575 max(bl.end_date)
2576 FROM pa_resource_assignments ra,
2577 pa_budget_versions bv, -- Added for Perf Bug 4469690
2578 pa_budget_lines bl,
2579 pa_periods_all start_pd,
2580 pa_periods_all end_pd
2581 WHERE ra.resource_assignment_id = bl.resource_assignment_id
2582 AND ra.budget_version_id = bv.budget_version_id -- Added for Perf Bug 4469690
2583 AND ra.project_id = bv.project_id -- Added for Perf Bug 4469690
2584 AND bv.budget_version_id = p_budget_version_id -- Added for Perf Bug 4469690
2585 AND start_pd.org_id = l_fp_cols_rec.x_org_id
2586 AND end_pd.org_id = l_fp_cols_rec.x_org_id
2587 AND ra.planning_start_date between start_pd.start_date and start_pd.end_date
2588 AND ra.planning_end_date between end_pd.start_date and end_pd.end_date
2589 GROUP BY bl.resource_assignment_id,
2590 ra.planning_start_date,
2591 ra.planning_end_date,
2592 start_pd.start_date,
2593 end_pd.end_date;
2594
2595 CURSOR all_tgt_res_dates_gl_cursor IS
2596 SELECT bl.resource_assignment_id,
2597 ra.planning_start_date,
2598 ra.planning_end_date,
2599 start_pd.start_date,
2600 end_pd.end_date,
2601 min(bl.start_date),
2602 max(bl.end_date)
2603 FROM pa_resource_assignments ra,
2604 pa_budget_versions bv, -- Added for Perf Bug 4469690
2605 pa_budget_lines bl,
2606 gl_period_statuses start_pd,
2607 gl_period_statuses end_pd
2608 WHERE ra.resource_assignment_id = bl.resource_assignment_id
2609 AND ra.budget_version_id = bv.budget_version_id -- Added for Perf Bug 4469690
2610 AND ra.project_id = bv.project_id -- Added for Perf Bug 4469690
2611 AND bv.budget_version_id = p_budget_version_id -- Added for Perf Bug 4469690
2612 AND start_pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
2613 AND start_pd.set_of_books_id = l_fp_cols_rec.x_set_of_books_id
2614 AND start_pd.adjustment_period_flag = 'N'
2615 AND end_pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
2616 AND end_pd.set_of_books_id = l_fp_cols_rec.x_set_of_books_id
2617 AND end_pd.adjustment_period_flag = 'N'
2618 AND ra.planning_start_date between start_pd.start_date and start_pd.end_date
2619 AND ra.planning_end_date between end_pd.start_date and end_pd.end_date
2620 GROUP BY bl.resource_assignment_id,
2621 ra.planning_start_date,
2622 ra.planning_end_date,
2623 start_pd.start_date,
2624 end_pd.end_date;
2625
2626 CURSOR all_tgt_res_dates_none_cursor IS
2627 SELECT bl.resource_assignment_id,
2628 ra.planning_start_date,
2629 ra.planning_end_date,
2630 ra.planning_start_date,
2631 ra.planning_end_date,
2632 min(bl.start_date),
2633 max(bl.end_date)
2634 FROM pa_resource_assignments ra,
2635 pa_budget_versions bv, -- Added for Perf Bug 4469690
2636 pa_budget_lines bl
2637 WHERE ra.resource_assignment_id = bl.resource_assignment_id
2638 AND ra.budget_version_id = bv.budget_version_id -- Added for Perf Bug 4469690
2639 AND ra.project_id = bv.project_id -- Added for Perf Bug 4469690
2640 AND bv.budget_version_id = p_budget_version_id -- Added for Perf Bug 4469690
2641 GROUP BY bl.resource_assignment_id,
2642 ra.planning_start_date,
2643 ra.planning_end_date;
2644
2645 /* Added new cursor for work plan non time phased version
2646 planning dates sync up - bug 4408930 */
2647
2648 CURSOR all_tgt_res_dates_none_wp IS
2649 SELECT ra.resource_assignment_id,
2650 ra.planning_start_date,
2651 ra.planning_end_date,
2652 ra.planning_start_date,
2653 ra.planning_end_date,
2654 min(t.start_date),
2655 max(t.finish_date)
2656 FROM pa_resource_assignments ra,
2657 PA_PROG_ACT_BY_PERIOD_TEMP t
2658 WHERE ra.resource_assignment_id =
2659 nvl(t.resource_assignment_id,t.HIDDEN_RES_ASSGN_ID)
2660 AND ra.budget_version_id = p_budget_version_id
2661 GROUP BY ra.resource_assignment_id,
2662 ra.planning_start_date,
2663 ra.planning_end_date;
2664
2665
2666 /* Cursors for synching Actuals by target time phase */
2667
2668 CURSOR actuals_dates_pa_cursor IS
2669 SELECT bl.resource_assignment_id,
2670 ra.planning_start_date,
2671 ra.planning_end_date,
2672 start_pd.start_date,
2673 end_pd.end_date,
2674 min(bl.start_date),
2675 max(bl.end_date)
2676 FROM pa_resource_assignments ra,
2677 pa_budget_versions bv, -- Added for Perf Bug 4469690
2678 pa_budget_lines bl,
2679 pji_fm_xbs_accum_tmp1 pji_tmp,
2680 pa_periods_all start_pd,
2681 pa_periods_all end_pd
2682 WHERE ra.resource_assignment_id = bl.resource_assignment_id
2683 AND ra.budget_version_id = bv.budget_version_id -- Added for Perf Bug 4469690
2684 AND ra.project_id = bv.project_id -- Added for Perf Bug 4469690
2685 AND bv.budget_version_id = p_budget_version_id -- Added for Perf Bug 4469690
2686 AND ra.resource_assignment_id = pji_tmp.source_id
2687 AND start_pd.org_id = l_fp_cols_rec.x_org_id
2688 AND end_pd.org_id = l_fp_cols_rec.x_org_id
2689 AND ra.planning_start_date between start_pd.start_date and start_pd.end_date
2690 AND ra.planning_end_date between end_pd.start_date and end_pd.end_date
2691 GROUP BY bl.resource_assignment_id,
2692 ra.planning_start_date,
2693 ra.planning_end_date,
2694 start_pd.start_date,
2695 end_pd.end_date;
2696
2697 CURSOR actuals_dates_gl_cursor IS
2698 SELECT bl.resource_assignment_id,
2699 ra.planning_start_date,
2700 ra.planning_end_date,
2701 start_pd.start_date,
2702 end_pd.end_date,
2703 min(bl.start_date),
2704 max(bl.end_date)
2705 FROM pa_resource_assignments ra,
2706 pa_budget_versions bv, -- Added for Perf Bug 4469690
2707 pa_budget_lines bl,
2708 pji_fm_xbs_accum_tmp1 pji_tmp,
2709 gl_period_statuses start_pd,
2710 gl_period_statuses end_pd
2711 WHERE ra.resource_assignment_id = bl.resource_assignment_id
2712 AND ra.budget_version_id = bv.budget_version_id -- Added for Perf Bug 4469690
2713 AND ra.project_id = bv.project_id -- Added for Perf Bug 4469690
2714 AND bv.budget_version_id = p_budget_version_id -- Added for Perf Bug 4469690
2715 AND ra.resource_assignment_id = pji_tmp.source_id
2716 AND start_pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
2717 AND start_pd.set_of_books_id = l_fp_cols_rec.x_set_of_books_id
2718 AND start_pd.adjustment_period_flag = 'N'
2719 AND end_pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
2720 AND end_pd.set_of_books_id = l_fp_cols_rec.x_set_of_books_id
2721 AND end_pd.adjustment_period_flag = 'N'
2722 AND ra.planning_start_date between start_pd.start_date and start_pd.end_date
2723 AND ra.planning_end_date between end_pd.start_date and end_pd.end_date
2724 GROUP BY bl.resource_assignment_id,
2725 ra.planning_start_date,
2726 ra.planning_end_date,
2727 start_pd.start_date,
2728 end_pd.end_date;
2729
2730 CURSOR actuals_dates_none_cursor IS
2731 SELECT bl.resource_assignment_id,
2732 ra.planning_start_date,
2733 ra.planning_end_date,
2734 ra.planning_start_date,
2735 ra.planning_end_date,
2736 min(bl.start_date),
2737 max(bl.end_date)
2738 FROM pa_resource_assignments ra,
2739 pa_budget_versions bv, -- Added for Perf Bug 4469690
2740 pa_budget_lines bl,
2741 pji_fm_xbs_accum_tmp1 pji_tmp
2742 WHERE ra.resource_assignment_id = bl.resource_assignment_id
2743 AND ra.budget_version_id = bv.budget_version_id -- Added for Perf Bug 4469690
2744 AND ra.project_id = bv.project_id -- Added for Perf Bug 4469690
2745 AND bv.budget_version_id = p_budget_version_id -- Added for Perf Bug 4469690
2746 AND ra.resource_assignment_id = pji_tmp.source_id
2747 GROUP BY bl.resource_assignment_id,
2748 ra.planning_start_date,
2749 ra.planning_end_date;
2750
2751 /* Cursor for synching Commitments and Billing Events */
2752
2753 CURSOR cmt_bill_event_dates_cursor IS
2754 SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N2)*/
2755 ra.resource_assignment_id,
2756 ra.planning_start_date,
2757 ra.planning_end_date,
2758 MIN(tmp.txn_planning_start_date),
2759 MAX(tmp.txn_planning_end_date)
2760 FROM pa_res_list_map_tmp4 tmp,
2761 pa_resource_assignments ra
2762 WHERE ra.resource_assignment_id = tmp.txn_resource_assignment_id
2763 AND ra.budget_version_id = p_budget_version_id
2764 GROUP BY ra.resource_assignment_id,
2765 ra.planning_start_date,
2766 ra.planning_end_date;
2767
2768 l_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2769 l_plan_start_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2770 l_plan_end_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2771 l_plan_period_start_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2772 l_plan_period_end_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2773 l_min_start_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2774 l_max_end_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2775
2776 l_end_date_upd_val_tab PA_PLSQL_DATATYPES.DateTabTyp;
2777
2778 l_upd_flag VARCHAR2(1);
2779 l_start_date DATE;
2780 l_end_date DATE;
2781 l_count NUMBER := 1;
2782
2783 l_upd_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2784 l_upd_start_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2785 l_upd_end_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2786
2787 lc_SyncVersion CONSTANT VARCHAR2(30) := 'SYNC_VERSION_LEVEL';
2788 lc_CopyActuals CONSTANT VARCHAR2(30) := 'COPY_ACTUALS';
2789 lc_Commitments CONSTANT VARCHAR2(30) := 'GEN_COMMITMENTS';
2790 lc_BillingEvents CONSTANT VARCHAR2(30) := 'GEN_BILLING_EVENTS';
2791
2792 l_plan_class_code PA_FIN_PLAN_TYPES_B.PLAN_CLASS_CODE%TYPE;
2793 l_etc_start_date PA_BUDGET_VERSIONS.ETC_START_DATE%TYPE;
2794 l_etc_start_period_end_date DATE;
2795
2796 l_wp_version_flag pa_budget_versions.wp_version_flag%TYPE;
2797
2798 BEGIN
2799 /* Setting initial values */
2800 X_MSG_COUNT := 0;
2801 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2802
2803 IF p_pa_debug_mode = 'Y' THEN
2804 PA_DEBUG.SET_CURR_FUNCTION
2805 ( p_function => 'SYNC_UP_PLANNING_DATES',
2806 p_debug_mode => p_pa_debug_mode );
2807 END IF;
2808
2809 IF p_pa_debug_mode = 'Y' THEN
2810 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
2811 ( p_msg => 'Before calling ' ||
2812 'PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS',
2813 p_module_name => l_module_name,
2814 p_log_level => 5 );
2815 END IF;
2816 /* Calling UTIL API to get target financial plan info l_fp_cols_rec */
2817 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS
2818 ( P_BUDGET_VERSION_ID => p_budget_version_id,
2819 X_FP_COLS_REC => l_fp_cols_rec,
2820 X_RETURN_STATUS => x_return_status,
2821 X_MSG_COUNT => x_msg_count,
2822 X_MSG_DATA => x_msg_data );
2823 IF p_pa_debug_mode = 'Y' THEN
2824 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
2825 ( p_msg => 'Status after calling ' ||
2826 'PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS: '
2827 ||x_return_status,
2828 p_module_name => l_module_name,
2829 p_log_level => 5 );
2830 END IF;
2831 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2832 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2833 END IF;
2834
2835 SELECT NVL(wp_version_flag,'N') INTO l_wp_version_flag FROM
2836 pa_budget_versions WHERE budget_version_id = p_budget_version_id;
2837
2838
2839 /* Fetch the planning dates and min/max dates for planning resources
2840 * based on p_calling_context and target time phase. */
2841
2842 IF p_calling_context = lc_SyncVersion THEN
2843
2844 IF l_fp_cols_rec.x_time_phased_code = 'P' THEN
2845 OPEN all_tgt_res_dates_pa_cursor;
2846 FETCH all_tgt_res_dates_pa_cursor
2847 BULK COLLECT
2848 INTO l_res_asg_id_tab,
2849 l_plan_start_date_tab,
2850 l_plan_end_date_tab,
2851 l_plan_period_start_date_tab,
2852 l_plan_period_end_date_tab,
2853 l_min_start_date_tab,
2854 l_max_end_date_tab;
2855 CLOSE all_tgt_res_dates_pa_cursor;
2856 ELSIF l_fp_cols_rec.x_time_phased_code = 'G' THEN
2857 OPEN all_tgt_res_dates_gl_cursor;
2858 FETCH all_tgt_res_dates_gl_cursor
2859 BULK COLLECT
2860 INTO l_res_asg_id_tab,
2861 l_plan_start_date_tab,
2862 l_plan_end_date_tab,
2863 l_plan_period_start_date_tab,
2864 l_plan_period_end_date_tab,
2865 l_min_start_date_tab,
2866 l_max_end_date_tab;
2867 CLOSE all_tgt_res_dates_gl_cursor;
2868 ELSIF l_fp_cols_rec.x_time_phased_code = 'N' THEN
2869 IF l_wp_version_flag = 'N' THEN
2870 OPEN all_tgt_res_dates_none_cursor;
2871 FETCH all_tgt_res_dates_none_cursor
2872 BULK COLLECT
2873 INTO l_res_asg_id_tab,
2874 l_plan_start_date_tab,
2875 l_plan_end_date_tab,
2876 l_plan_period_start_date_tab,
2877 l_plan_period_end_date_tab,
2878 l_min_start_date_tab,
2879 l_max_end_date_tab;
2880 CLOSE all_tgt_res_dates_none_cursor;
2881 ELSE
2882 OPEN all_tgt_res_dates_none_wp;
2883 FETCH all_tgt_res_dates_none_wp
2884 BULK COLLECT
2885 INTO l_res_asg_id_tab,
2886 l_plan_start_date_tab,
2887 l_plan_end_date_tab,
2888 l_plan_period_start_date_tab,
2889 l_plan_period_end_date_tab,
2890 l_min_start_date_tab,
2891 l_max_end_date_tab;
2892 CLOSE all_tgt_res_dates_none_wp;
2893 END IF;
2894 END IF;
2895
2896 ELSIF p_calling_context = lc_CopyActuals THEN
2897
2898 IF l_fp_cols_rec.x_time_phased_code = 'P' THEN
2899 OPEN actuals_dates_pa_cursor;
2900 FETCH actuals_dates_pa_cursor
2901 BULK COLLECT
2902 INTO l_res_asg_id_tab,
2903 l_plan_start_date_tab,
2904 l_plan_end_date_tab,
2905 l_plan_period_start_date_tab,
2906 l_plan_period_end_date_tab,
2907 l_min_start_date_tab,
2908 l_max_end_date_tab;
2909 CLOSE actuals_dates_pa_cursor;
2910 ELSIF l_fp_cols_rec.x_time_phased_code = 'G' THEN
2911 OPEN actuals_dates_gl_cursor;
2912 FETCH actuals_dates_gl_cursor
2913 BULK COLLECT
2914 INTO l_res_asg_id_tab,
2915 l_plan_start_date_tab,
2916 l_plan_end_date_tab,
2917 l_plan_period_start_date_tab,
2918 l_plan_period_end_date_tab,
2919 l_min_start_date_tab,
2920 l_max_end_date_tab;
2921 CLOSE actuals_dates_gl_cursor;
2922 ELSIF l_fp_cols_rec.x_time_phased_code = 'N' THEN
2923 OPEN actuals_dates_none_cursor;
2924 FETCH actuals_dates_none_cursor
2925 BULK COLLECT
2926 INTO l_res_asg_id_tab,
2927 l_plan_start_date_tab,
2928 l_plan_end_date_tab,
2929 l_plan_period_start_date_tab,
2930 l_plan_period_end_date_tab,
2931 l_min_start_date_tab,
2932 l_max_end_date_tab;
2933 CLOSE actuals_dates_none_cursor;
2934 END IF;
2935
2936 ELSIF p_calling_context = lc_Commitments OR
2937 p_calling_context = lc_BillingEvents THEN
2938
2939 OPEN cmt_bill_event_dates_cursor;
2940 FETCH cmt_bill_event_dates_cursor
2941 BULK COLLECT
2942 INTO l_res_asg_id_tab,
2943 l_plan_start_date_tab,
2944 l_plan_end_date_tab,
2945 l_min_start_date_tab,
2946 l_max_end_date_tab;
2947 CLOSE cmt_bill_event_dates_cursor;
2948
2949 END IF; -- end of data fetching logic
2950
2951 IF l_res_asg_id_tab.count = 0 THEN
2952 IF p_pa_debug_mode = 'Y' THEN
2953 PA_DEBUG.Reset_Curr_Function;
2954 END IF;
2955 RETURN;
2956 END IF;
2957
2958 /* The following block determines which resource assignment dates
2959 * need to be synched and what the new planning date should be. */
2960
2961 IF p_calling_context = lc_SyncVersion OR
2962 p_calling_context = lc_CopyActuals THEN
2963
2964 FOR i IN 1..l_res_asg_id_tab.count LOOP
2965 l_upd_flag := 'N';
2966 l_start_date := l_plan_start_date_tab(i);
2967 l_end_date := l_plan_end_date_tab(i);
2968 IF l_min_start_date_tab(i) < l_plan_period_start_date_tab(i) THEN
2969 l_upd_flag := 'Y';
2970 l_start_date := l_min_start_date_tab(i);
2971 --Bug 5672100. Added this block to make sure that the planning dates and budget line
2972 --dates are one and same for non-time phased workplan versions
2973 ELSIF l_fp_cols_rec.x_time_phased_code = 'N' AND
2974 l_wp_version_flag = 'Y' AND
2975 l_min_start_date_tab(i) > l_plan_period_start_date_tab(i) THEN
2976 l_upd_flag := 'Y';
2977 l_start_date := l_plan_period_start_date_tab(i);
2978 END IF;
2979 IF l_max_end_date_tab(i) > l_plan_period_end_date_tab(i) THEN
2980 l_upd_flag := 'Y';
2981 l_end_date := l_max_end_date_tab(i);
2982 --Bug 5672100. Added this block to make sure that the planning dates and budget line
2983 --dates are one and same for non-time phased workplan versions
2984 ELSIF l_fp_cols_rec.x_time_phased_code = 'N' AND
2985 l_wp_version_flag = 'Y' AND
2986 l_max_end_date_tab(i) < l_plan_period_end_date_tab(i) THEN
2987 l_upd_flag := 'Y';
2988 l_end_date := l_plan_period_end_date_tab(i);
2989 END IF;
2990 IF l_upd_flag = 'Y' THEN
2991 l_upd_res_asg_id_tab(l_count) := l_res_asg_id_tab(i);
2992 l_upd_start_date_tab(l_count) := l_start_date;
2993 l_upd_end_date_tab(l_count) := l_end_date;
2994 l_count := l_count + 1;
2995 END IF;
2996 END LOOP;
2997
2998 ELSIF p_calling_context = lc_Commitments OR
2999 p_calling_context = lc_BillingEvents THEN
3000
3001 /* Default the values for l_end_date_upd_val_tab so that values
3002 * will be correct when the target is not a Forecast or when
3003 * the target is a Forecast but the max end date is less than
3004 * the ETC start date. */
3005 FOR i IN 1..l_res_asg_id_tab.count LOOP
3006 l_end_date_upd_val_tab(i) := l_max_end_date_tab(i);
3007 END LOOP;
3008
3009 l_plan_class_code := l_fp_cols_rec.x_plan_class_code;
3010
3011 IF l_plan_class_code = 'FORECAST' THEN
3012 l_etc_start_date := PA_FP_GEN_AMOUNT_UTILS.GET_ETC_START_DATE
3013 ( l_fp_cols_rec.x_budget_version_id );
3014 END IF;
3015
3016 IF l_plan_class_code = 'FORECAST' AND l_etc_start_date IS NOT NULL THEN
3017
3018 /* Get the periodic end date for the ETC start date period. */
3019 l_etc_start_period_end_date := l_etc_start_date;
3020 IF l_fp_cols_rec.x_time_phased_code = 'P' THEN
3021 SELECT pd.end_date
3022 INTO l_etc_start_period_end_date
3023 FROM pa_periods_all pd
3024 WHERE pd.org_id = l_fp_cols_rec.x_org_id
3025 AND l_etc_start_date between pd.start_date and pd.end_date;
3026 ELSIF l_fp_cols_rec.x_time_phased_code = 'G' THEN
3027 SELECT pd.end_date
3028 INTO l_etc_start_period_end_date
3029 FROM gl_period_statuses pd
3030 WHERE pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
3031 AND pd.set_of_books_id = l_fp_cols_rec.x_set_of_books_id
3032 AND pd.adjustment_period_flag = 'N'
3033 AND l_etc_start_date between pd.start_date and pd.end_date;
3034 END IF;
3035
3036 FOR i IN 1..l_res_asg_id_tab.count LOOP
3037 IF l_min_start_date_tab(i) < l_etc_start_date THEN
3038 l_min_start_date_tab(i) := l_etc_start_date;
3039 END IF;
3040 IF l_max_end_date_tab(i) < l_etc_start_date THEN
3041 l_max_end_date_tab(i) := l_etc_start_date;
3042 l_end_date_upd_val_tab(i) := l_etc_start_period_end_date;
3043 END IF;
3044 END LOOP;
3045 END IF;
3046
3047 FOR i IN 1..l_res_asg_id_tab.count LOOP
3048 l_upd_flag := 'N';
3049 l_start_date := l_plan_start_date_tab(i);
3050 l_end_date := l_plan_end_date_tab(i);
3051 IF l_min_start_date_tab(i) < l_plan_start_date_tab(i) THEN
3052 l_upd_flag := 'Y';
3053 l_start_date := l_min_start_date_tab(i);
3054 END IF;
3055 /* Although we compare using l_max_end_date_tab, we set the
3056 * end date based on the update value pl/sql table. This is done
3057 * to handle the Forecast case when both the planning end date
3058 * and max commitment / billing event date fall before the ETC
3059 * start date and we need to set the planning end date to the
3060 * last day of the ETC start date period. */
3061 IF l_max_end_date_tab(i) > l_plan_end_date_tab(i) THEN
3062 l_upd_flag := 'Y';
3063 l_end_date := l_end_date_upd_val_tab(i);
3064 END IF;
3065 IF l_upd_flag = 'Y' THEN
3066 l_upd_res_asg_id_tab(l_count) := l_res_asg_id_tab(i);
3067 l_upd_start_date_tab(l_count) := l_start_date;
3068 l_upd_end_date_tab(l_count) := l_end_date;
3069 l_count := l_count + 1;
3070 END IF;
3071 END LOOP;
3072
3073 END IF; -- end populating update pl/sql tables
3074
3075 /* Update synched planning dates in the db. */
3076 FORALL m IN 1..l_upd_res_asg_id_tab.count
3077 UPDATE pa_resource_assignments
3078 SET planning_start_date = l_upd_start_date_tab(m),
3079 planning_end_date = l_upd_end_date_tab(m)
3080 WHERE resource_assignment_id = l_upd_res_asg_id_tab(m);
3081
3082 /* bug 4408930 */
3083
3084 IF l_fp_cols_rec.x_time_phased_code = 'N' AND
3085 l_wp_version_flag = 'Y' THEN
3086 FORALL m IN 1..l_upd_res_asg_id_tab.count
3087 UPDATE pa_budget_lines
3088 SET start_date = l_upd_start_date_tab(m),
3089 end_date = l_upd_end_date_tab(m)
3090 WHERE resource_assignment_id = l_upd_res_asg_id_tab(m);
3091 END IF;
3092
3093 /* bug 4408930 */
3094
3095 IF p_pa_debug_mode = 'Y' THEN
3096 PA_DEBUG.Reset_Curr_Function;
3097 END IF;
3098
3099 EXCEPTION
3100 WHEN OTHERS THEN
3101 -- Bug 4621171: Removed ROLLBACK statement.
3102 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3103 x_msg_count := 1;
3104 x_msg_data := substr(sqlerrm,1,240);
3105 -- dbms_output.put_line('error msg :'||x_msg_data);
3106 FND_MSG_PUB.add_exc_msg
3107 ( p_pkg_name => 'PA_FP_MAINTAIN_ACTUAL_PUB',
3108 p_procedure_name => 'SYNC_UP_PLANNING_DATES',
3109 p_error_text => substr(sqlerrm,1,240));
3110 IF P_PA_DEBUG_MODE = 'Y' THEN
3111 pa_fp_gen_amount_utils.fp_debug
3112 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
3113 p_module_name => l_module_name,
3114 p_log_level => 5);
3115 PA_DEBUG.Reset_Curr_Function;
3116 END IF;
3117 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3118
3119 END SYNC_UP_PLANNING_DATES;
3120
3121 /** Valid values for parameter P_ENTIRE_VERSION_FLAG:
3122 * 'N': not for entire version, will update reporting lines only for the passes*
3123 * resource assignments. *
3124 * 'Y': for entire version, will update reporting lines for all resource *
3125 * assignments for the passed budget version id without looking into *
3126 * p_res_asg_id_tab. *
3127 * Valid values for parameter P_ACTIVITY_CODE: *
3128 * 'UPDATE': update reporting lines *
3129 * 'DELETE': delete reporting lines *
3130 **/
3131 PROCEDURE BLK_UPD_REPORTING_LINES_WRP
3132 (P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
3133 P_ENTIRE_VERSION_FLAG IN VARCHAR2,
3134 P_RES_ASG_ID_TAB IN PA_PLSQL_DATATYPES.IDTABTYP,
3135 P_ACTIVITY_CODE IN VARCHAR2,
3136 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3137 X_MSG_COUNT OUT NOCOPY NUMBER,
3138 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
3139
3140 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_maintain_actual_pub.blk_upd_reporting_lines_wrp';
3141
3142 l_rep_budget_line_id_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3143 l_rep_res_assignment_id_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3144 l_rep_start_date_tab SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
3145 l_rep_end_date_tab SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
3146 l_rep_period_name_tab SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
3147 l_rep_txn_curr_code_tab SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
3148 l_rep_quantity_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3149 l_rep_txn_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3150 l_rep_txn_burdened_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3151 l_rep_txn_revenue_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3152 l_rep_project_curr_code_tab SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
3153 l_rep_project_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3154 l_rep_project_burden_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3155 l_rep_project_revenue_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3156 l_rep_projfunc_curr_code_tab SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
3157 l_rep_projfunc_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3158 l_rep_projfunc_burden_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3159 l_rep_projfunc_revenue_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3160 /*
3161 * Following _act_ variables to hold Actual amounts.
3162 */
3163 l_rep_act_quantity_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3164 l_rep_txn_act_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3165 l_rep_txn_act_burd_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3166 l_rep_txn_act_rev_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3167 l_rep_prj_act_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3168 l_rep_prj_act_burd_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3169 l_rep_prj_act_rev_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3170 l_rep_pf_act_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3171 l_rep_pf_act_burd_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3172 l_rep_pf_act_rev_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3173
3174 l_msg_count NUMBER;
3175 l_data VARCHAR2(2000);
3176 l_msg_data VARCHAR2(2000);
3177 l_msg_index_out NUMBER;
3178
3179 BEGIN
3180 X_MSG_COUNT := 0;
3181 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3182
3183 IF p_pa_debug_mode = 'Y' THEN
3184 PA_DEBUG.SET_CURR_FUNCTION
3185 ( p_function => 'BLK_UPD_REPORTING_LINES_WRP',
3186 p_debug_mode => p_pa_debug_mode );
3187 END IF;
3188
3189 l_rep_budget_line_id_tab.delete;
3190 l_rep_res_assignment_id_tab.delete;
3191 l_rep_start_date_tab.delete;
3192 l_rep_end_date_tab.delete;
3193 l_rep_period_name_tab.delete;
3194 l_rep_txn_curr_code_tab.delete;
3195 l_rep_quantity_tab.delete;
3196 l_rep_txn_raw_cost_tab.delete;
3197 l_rep_txn_burdened_cost_tab.delete;
3198 l_rep_txn_revenue_tab.delete;
3199 l_rep_project_curr_code_tab.delete;
3200 l_rep_project_raw_cost_tab.delete;
3201 l_rep_project_burden_cost_tab.delete;
3202 l_rep_project_revenue_tab.delete;
3203 l_rep_projfunc_curr_code_tab.delete;
3204 l_rep_projfunc_raw_cost_tab.delete;
3205 l_rep_projfunc_burden_cost_tab.delete;
3206 l_rep_projfunc_revenue_tab.delete;
3207
3208 l_rep_act_quantity_tab.delete;
3209 l_rep_txn_act_raw_cost_tab.delete;
3210 l_rep_txn_act_burd_cost_tab.delete;
3211 l_rep_txn_act_rev_tab.delete;
3212 l_rep_prj_act_raw_cost_tab.delete;
3213 l_rep_prj_act_burd_cost_tab.delete;
3214 l_rep_prj_act_rev_tab.delete;
3215 l_rep_pf_act_raw_cost_tab.delete;
3216 l_rep_pf_act_burd_cost_tab.delete;
3217 l_rep_pf_act_rev_tab.delete;
3218
3219 IF P_ENTIRE_VERSION_FLAG = 'Y' THEN
3220 SELECT budget_line_id,
3221 resource_assignment_id,
3222 start_date,
3223 end_date,
3224 period_name,
3225 txn_currency_code,
3226 DECODE(P_ACTIVITY_CODE,
3227 'DELETE', (-1) * quantity,
3228 'UPDATE', quantity),
3229 DECODE(P_ACTIVITY_CODE,
3230 'DELETE', (-1) * txn_raw_cost,
3231 'UPDATE', txn_raw_cost),
3232 DECODE(P_ACTIVITY_CODE,
3233 'DELETE', (-1) * txn_burdened_cost,
3234 'UPDATE', txn_burdened_cost),
3235 DECODE(P_ACTIVITY_CODE,
3236 'DELETE', (-1) * txn_revenue,
3237 'UPDATE', txn_revenue),
3238 project_currency_code,
3239 DECODE(P_ACTIVITY_CODE,
3240 'DELETE', (-1) * project_raw_cost,
3241 'UPDATE', project_raw_cost),
3242 DECODE(P_ACTIVITY_CODE,
3243 'DELETE', (-1) * project_burdened_cost,
3244 'UPDATE', project_burdened_cost),
3245 DECODE(P_ACTIVITY_CODE,
3246 'DELETE', (-1) * project_revenue,
3247 'UPDATE', project_revenue),
3248 projfunc_currency_code,
3249 DECODE(P_ACTIVITY_CODE,
3250 'DELETE', (-1) * raw_cost,
3251 'UPDATE', raw_cost),
3252 DECODE(P_ACTIVITY_CODE,
3253 'DELETE', (-1) * burdened_cost,
3254 'UPDATE', burdened_cost),
3255 DECODE(P_ACTIVITY_CODE,
3256 'DELETE', (-1) * revenue,
3257 'UPDATE', revenue)
3258 ,DECODE(P_ACTIVITY_CODE,
3259 'DELETE', (-1) * init_quantity,
3260 'UPDATE', init_quantity)
3261 ,DECODE(P_ACTIVITY_CODE,
3262 'DELETE', (-1) * txn_init_raw_cost,
3263 'UPDATE', txn_init_raw_cost)
3264 ,DECODE(P_ACTIVITY_CODE,
3265 'DELETE', (-1) * txn_init_burdened_cost,
3266 'UPDATE', txn_init_burdened_cost)
3267 ,DECODE(P_ACTIVITY_CODE,
3268 'DELETE', (-1) * txn_init_revenue,
3269 'UPDATE', txn_init_revenue)
3270 ,DECODE(P_ACTIVITY_CODE,
3271 'DELETE', (-1) * project_init_raw_cost,
3272 'UPDATE', project_init_raw_cost)
3273 ,DECODE(P_ACTIVITY_CODE,
3274 'DELETE', (-1) * project_init_burdened_cost,
3275 'UPDATE', project_init_burdened_cost)
3276 ,DECODE(P_ACTIVITY_CODE,
3277 'DELETE', (-1) * project_init_revenue,
3278 'UPDATE', project_init_revenue)
3279 ,DECODE(P_ACTIVITY_CODE,
3280 'DELETE', (-1) * init_raw_cost,
3281 'UPDATE', init_raw_cost)
3282 ,DECODE(P_ACTIVITY_CODE,
3283 'DELETE', (-1) * init_burdened_cost,
3284 'UPDATE', init_burdened_cost)
3285 ,DECODE(P_ACTIVITY_CODE,
3286 'DELETE', (-1) * init_revenue,
3287 'UPDATE', init_revenue)
3288 BULK COLLECT INTO
3289 l_rep_budget_line_id_tab,
3290 l_rep_res_assignment_id_tab,
3291 l_rep_start_date_tab,
3292 l_rep_end_date_tab,
3293 l_rep_period_name_tab,
3294 l_rep_txn_curr_code_tab,
3295 l_rep_quantity_tab,
3296 l_rep_txn_raw_cost_tab,
3297 l_rep_txn_burdened_cost_tab,
3298 l_rep_txn_revenue_tab,
3299 l_rep_project_curr_code_tab,
3300 l_rep_project_raw_cost_tab,
3301 l_rep_project_burden_cost_tab,
3302 l_rep_project_revenue_tab,
3303 l_rep_projfunc_curr_code_tab,
3304 l_rep_projfunc_raw_cost_tab,
3305 l_rep_projfunc_burden_cost_tab,
3306 l_rep_projfunc_revenue_tab
3307 ,l_rep_act_quantity_tab
3308 ,l_rep_txn_act_raw_cost_tab
3309 ,l_rep_txn_act_burd_cost_tab
3310 ,l_rep_txn_act_rev_tab
3311 ,l_rep_prj_act_raw_cost_tab
3312 ,l_rep_prj_act_burd_cost_tab
3313 ,l_rep_prj_act_rev_tab
3314 ,l_rep_pf_act_raw_cost_tab
3315 ,l_rep_pf_act_burd_cost_tab
3316 ,l_rep_pf_act_rev_tab
3317 FROM pa_budget_lines
3318 WHERE budget_version_id = P_BUDGET_VERSION_ID;
3319 ELSE
3320 DELETE FROM pa_fp_calc_amt_tmp1;
3321 FORALL i IN 1..p_res_asg_id_tab.count
3322 INSERT INTO pa_fp_calc_amt_tmp1 (resource_assignment_id)
3323 VALUES (p_res_asg_id_tab(i));
3324
3325 SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP1_N2)*/
3326 bl.budget_line_id,
3327 bl.resource_assignment_id,
3328 bl.start_date,
3329 bl.end_date,
3330 bl.period_name,
3331 bl.txn_currency_code,
3332 DECODE(P_ACTIVITY_CODE,
3333 'DELETE', (-1) * bl.quantity,
3334 'UPDATE', bl.quantity),
3335 DECODE(P_ACTIVITY_CODE,
3336 'DELETE', (-1) * bl.txn_raw_cost,
3337 'UPDATE', bl.txn_raw_cost),
3338 DECODE(P_ACTIVITY_CODE,
3339 'DELETE', (-1) * bl.txn_burdened_cost,
3340 'UPDATE', bl.txn_burdened_cost),
3341 DECODE(P_ACTIVITY_CODE,
3342 'DELETE', (-1) * bl.txn_revenue,
3343 'UPDATE', bl.txn_revenue),
3344 project_currency_code,
3345 DECODE(P_ACTIVITY_CODE,
3346 'DELETE', (-1) * bl.project_raw_cost,
3347 'UPDATE', bl.project_raw_cost),
3348 DECODE(P_ACTIVITY_CODE,
3349 'DELETE', (-1) * bl.project_burdened_cost,
3350 'UPDATE', bl.project_burdened_cost),
3351 DECODE(P_ACTIVITY_CODE,
3352 'DELETE', (-1) * bl.project_revenue,
3353 'UPDATE', bl.project_revenue),
3354 bl.projfunc_currency_code,
3355 DECODE(P_ACTIVITY_CODE,
3356 'DELETE', (-1) * bl.raw_cost,
3357 'UPDATE', bl.raw_cost),
3358 DECODE(P_ACTIVITY_CODE,
3359 'DELETE', (-1) * bl.burdened_cost,
3360 'UPDATE', bl.burdened_cost),
3361 DECODE(P_ACTIVITY_CODE,
3362 'DELETE', (-1) * bl.revenue,
3363 'UPDATE', bl.revenue)
3364 ,DECODE(P_ACTIVITY_CODE,
3365 'DELETE', (-1) * bl.init_quantity,
3366 'UPDATE', bl.init_quantity)
3367 ,DECODE(P_ACTIVITY_CODE,
3368 'DELETE', (-1) * bl.txn_init_raw_cost,
3369 'UPDATE', bl.txn_init_raw_cost)
3370 ,DECODE(P_ACTIVITY_CODE,
3371 'DELETE', (-1) * bl.txn_init_burdened_cost,
3372 'UPDATE', bl.txn_init_burdened_cost)
3373 ,DECODE(P_ACTIVITY_CODE,
3374 'DELETE', (-1) * bl.txn_init_revenue,
3375 'UPDATE', bl.txn_init_revenue)
3376 ,DECODE(P_ACTIVITY_CODE,
3377 'DELETE', (-1) * bl.project_init_raw_cost,
3378 'UPDATE', bl.project_init_raw_cost)
3379 ,DECODE(P_ACTIVITY_CODE,
3380 'DELETE', (-1) * bl.project_init_burdened_cost,
3381 'UPDATE', bl.project_init_burdened_cost)
3382 ,DECODE(P_ACTIVITY_CODE,
3383 'DELETE', (-1) * bl.project_init_revenue,
3384 'UPDATE', bl.project_init_revenue)
3385 ,DECODE(P_ACTIVITY_CODE,
3386 'DELETE', (-1) * bl.init_raw_cost,
3387 'UPDATE', bl.init_raw_cost)
3388 ,DECODE(P_ACTIVITY_CODE,
3389 'DELETE', (-1) * bl.init_burdened_cost,
3390 'UPDATE', bl.init_burdened_cost)
3391 ,DECODE(P_ACTIVITY_CODE,
3392 'DELETE', (-1) * bl.init_revenue,
3393 'UPDATE', bl.init_revenue)
3394 BULK COLLECT INTO
3395 l_rep_budget_line_id_tab,
3396 l_rep_res_assignment_id_tab,
3397 l_rep_start_date_tab,
3398 l_rep_end_date_tab,
3399 l_rep_period_name_tab,
3400 l_rep_txn_curr_code_tab,
3401 l_rep_quantity_tab,
3402 l_rep_txn_raw_cost_tab,
3403 l_rep_txn_burdened_cost_tab,
3404 l_rep_txn_revenue_tab,
3405 l_rep_project_curr_code_tab,
3406 l_rep_project_raw_cost_tab,
3407 l_rep_project_burden_cost_tab,
3408 l_rep_project_revenue_tab,
3409 l_rep_projfunc_curr_code_tab,
3410 l_rep_projfunc_raw_cost_tab,
3411 l_rep_projfunc_burden_cost_tab,
3412 l_rep_projfunc_revenue_tab
3413 ,l_rep_act_quantity_tab
3414 ,l_rep_txn_act_raw_cost_tab
3415 ,l_rep_txn_act_burd_cost_tab
3416 ,l_rep_txn_act_rev_tab
3417 ,l_rep_prj_act_raw_cost_tab
3418 ,l_rep_prj_act_burd_cost_tab
3419 ,l_rep_prj_act_rev_tab
3420 ,l_rep_pf_act_raw_cost_tab
3421 ,l_rep_pf_act_burd_cost_tab
3422 ,l_rep_pf_act_rev_tab
3423 FROM pa_budget_lines bl, pa_fp_calc_amt_tmp1 tmp
3424 WHERE bl.budget_version_id = P_BUDGET_VERSION_ID
3425 AND bl.resource_assignment_id = tmp.resource_assignment_id;
3426 END IF;
3427
3428 IF l_rep_budget_line_id_tab.count = 0 THEN
3429 IF p_pa_debug_mode = 'Y' THEN
3430 PA_DEBUG.Reset_Curr_Function;
3431 END IF;
3432 RETURN;
3433 END IF;
3434
3435 IF p_pa_debug_mode = 'Y' THEN
3436 pa_fp_gen_amount_utils.fp_debug
3437 (p_msg => 'Before calling pa_fp_pji_intg_pkg.ublk_update_reporting_lines',
3438 p_module_name => l_module_name,
3439 p_log_level => 5);
3440 END IF;
3441 PA_FP_PJI_INTG_PKG.BLK_UPDATE_REPORTING_LINES
3442 (p_calling_module => 'MAINTAIN_ACTUAL_AMT_RA_API'
3443 ,p_activity_code => p_activity_code
3444 ,p_budget_version_id => p_budget_version_id
3445 ,p_rep_budget_line_id_tab => l_rep_budget_line_id_tab
3446 ,p_rep_res_assignment_id_tab => l_rep_res_assignment_id_tab
3447 ,p_rep_start_date_tab => l_rep_start_date_tab
3448 ,p_rep_end_date_tab => l_rep_end_date_tab
3449 ,p_rep_period_name_tab => l_rep_period_name_tab
3450 ,p_rep_txn_curr_code_tab => l_rep_txn_curr_code_tab
3451 ,p_rep_quantity_tab => l_rep_quantity_tab
3452 ,p_rep_txn_raw_cost_tab => l_rep_txn_raw_cost_tab
3453 ,p_rep_txn_burdened_cost_tab => l_rep_txn_burdened_cost_tab
3454 ,p_rep_txn_revenue_tab => l_rep_txn_revenue_tab
3455 ,p_rep_project_curr_code_tab => l_rep_project_curr_code_tab
3456 ,p_rep_project_raw_cost_tab => l_rep_project_raw_cost_tab
3457 ,p_rep_project_burden_cost_tab => l_rep_project_burden_cost_tab
3458 ,p_rep_project_revenue_tab => l_rep_project_revenue_tab
3459 ,p_rep_projfunc_curr_code_tab => l_rep_projfunc_curr_code_tab
3460 ,p_rep_projfunc_raw_cost_tab => l_rep_projfunc_raw_cost_tab
3461 ,p_rep_projfunc_burden_cost_tab => l_rep_projfunc_burden_cost_tab
3462 ,p_rep_projfunc_revenue_tab => l_rep_projfunc_revenue_tab
3463 ,p_rep_act_quantity_tab => l_rep_act_quantity_tab
3464 ,p_rep_txn_act_raw_cost_tab => l_rep_txn_act_raw_cost_tab
3465 ,p_rep_txn_act_burd_cost_tab => l_rep_txn_act_burd_cost_tab
3466 ,p_rep_txn_act_rev_tab => l_rep_txn_act_rev_tab
3467 ,p_rep_prj_act_raw_cost_tab => l_rep_prj_act_raw_cost_tab
3468 ,p_rep_prj_act_burd_cost_tab => l_rep_prj_act_burd_cost_tab
3469 ,p_rep_prj_act_rev_tab => l_rep_prj_act_rev_tab
3470 ,p_rep_pf_act_raw_cost_tab => l_rep_pf_act_raw_cost_tab
3471 ,p_rep_pf_act_burd_cost_tab => l_rep_pf_act_burd_cost_tab
3472 ,p_rep_pf_act_rev_tab => l_rep_pf_act_rev_tab
3473 ,x_msg_data => x_msg_data
3474 ,x_msg_count => x_msg_count
3475 ,x_return_status => x_return_status );
3476 IF p_pa_debug_mode = 'Y' THEN
3477 pa_fp_gen_amount_utils.fp_debug
3478 (p_msg => 'Status after calling
3479 pa_fp_pji_intg_pkg.blk_update_reporting_lines:'
3480 ||x_return_status,
3481 p_module_name => l_module_name,
3482 p_log_level => 5);
3483 END IF;
3484 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3485 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3486 END IF;
3487
3488 IF p_pa_debug_mode = 'Y' THEN
3489 PA_DEBUG.Reset_Curr_Function;
3490 END IF;
3491
3492 EXCEPTION
3493 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3494 l_msg_count := FND_MSG_PUB.count_msg;
3495 IF l_msg_count = 1 THEN
3496 PA_INTERFACE_UTILS_PUB.get_messages
3497 ( p_encoded => FND_API.G_TRUE,
3498 p_msg_index => 1,
3499 p_msg_count => l_msg_count,
3500 p_msg_data => l_msg_data,
3501 p_data => l_data,
3502 p_msg_index_out => l_msg_index_out);
3503 x_msg_data := l_data;
3504 x_msg_count := l_msg_count;
3505 ELSE
3506 x_msg_count := l_msg_count;
3507 END IF;
3508 -- Bug 4621171: Removed ROLLBACK statement.
3509
3510 x_return_status := FND_API.G_RET_STS_ERROR;
3511 IF P_PA_DEBUG_MODE = 'Y' THEN
3512 pa_fp_gen_amount_utils.fp_debug
3513 (p_msg => 'Invalid Arguments Passed',
3514 p_module_name => l_module_name,
3515 p_log_level => 5);
3516 PA_DEBUG.Reset_Curr_Function;
3517 END IF;
3518 -- Bug 4621171: Removed RAISE statement.
3519 WHEN OTHERS THEN
3520 -- Bug 4621171: Removed ROLLBACK statement.
3521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3522 x_msg_count := 1;
3523 x_msg_data := substr(sqlerrm,1,240);
3524 FND_MSG_PUB.add_exc_msg
3525 ( p_pkg_name => 'PA_FP_MAINTAIN_ACTUAL_PUB',
3526 p_procedure_name => 'BLK_UPD_REPORTING_LINES_WRP',
3527 p_error_text => substr(sqlerrm,1,240));
3528 IF P_PA_DEBUG_MODE = 'Y' THEN
3529 pa_fp_gen_amount_utils.fp_debug
3530 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
3531 p_module_name => l_module_name,
3532 p_log_level => 5);
3533 PA_DEBUG.Reset_Curr_Function;
3534 END IF;
3535 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3536
3537 END BLK_UPD_REPORTING_LINES_WRP;
3538
3539 PROCEDURE SYNC_UP_PLANNING_DATES_NONE_TP
3540 (P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
3541 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
3542 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3543 X_MSG_COUNT OUT NOCOPY NUMBER,
3544 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
3545
3546 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_maintain_actual_pub.SYNC_UP_PLANNING_DATES_NONE_TP';
3547
3548 l_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
3549 l_planning_start_date_tab pa_plsql_datatypes.DateTabTyp;
3550 l_planning_end_date_tab pa_plsql_datatypes.DateTabTyp;
3551 l_budget_line_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
3552 l_min_start_date DATE ;
3553 l_max_end_date DATE;
3554
3555 l_dummy NUMBER;
3556
3557 l_msg_count NUMBER;
3558 l_data VARCHAR2(2000);
3559 l_msg_data VARCHAR2(2000);
3560 l_msg_index_out NUMBER;
3561
3562 l_etc_start_date DATE;
3563 l_sum_init_quantity NUMBER;
3564 l_sum_plan_quantity NUMBER;
3565 l_etc_quantity NUMBER;
3566
3567 l_upd_index NUMBER;
3568 l_upd_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
3569 l_upd_planning_start_date_tab pa_plsql_datatypes.DateTabTyp;
3570 l_upd_planning_end_date_tab pa_plsql_datatypes.DateTabTyp;
3571 BEGIN
3572 X_MSG_COUNT := 0;
3573 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3574
3575 IF p_pa_debug_mode = 'Y' THEN
3576 PA_DEBUG.SET_CURR_FUNCTION
3577 ( p_function => 'SYNC_UP_PLANNING_DATES_NONE_TP',
3578 p_debug_mode => p_pa_debug_mode );
3579 END IF;
3580
3581 SELECT resource_assignment_id,
3582 planning_start_date,
3583 planning_end_date
3584 BULK COLLECT INTO
3585 l_res_asg_id_tab,
3586 l_planning_start_date_tab,
3587 l_planning_end_date_tab
3588 FROM pa_resource_assignments
3589 WHERE budget_version_id = P_BUDGET_VERSION_ID;
3590
3591 IF l_res_asg_id_tab.count = 0 THEN
3592 IF p_pa_debug_mode = 'Y' THEN
3593 PA_DEBUG.Reset_Curr_Function;
3594 END IF;
3595 RETURN;
3596 END IF;
3597
3598 IF p_fp_cols_rec.x_plan_class_code = 'FORECAST' THEN
3599 l_etc_start_date :=
3600 PA_FP_GEN_AMOUNT_UTILS.GET_ETC_START_DATE(p_budget_version_id);
3601 END IF;
3602
3603 l_upd_index := 0;
3604
3605 FOR i IN 1..l_res_asg_id_tab.count LOOP
3606 SELECT budget_line_id
3607 BULK COLLECT INTO
3608 l_budget_line_id_tab
3609 FROM pa_budget_lines
3610 WHERE resource_assignment_id = l_res_asg_id_tab(i);
3611
3612 IF l_budget_line_id_tab.count = 0 THEN
3613 l_dummy := 0;
3614 ELSE
3615 SELECT MIN(start_date),
3616 MAX(end_date),
3617 SUM(NVL(init_quantity,0)),
3618 SUM(NVL(quantity,0))
3619 INTO l_min_start_date,
3620 l_max_end_date,
3621 l_sum_init_quantity,
3622 l_sum_plan_quantity
3623 FROM pa_budget_lines
3624 WHERE resource_assignment_id = l_res_asg_id_tab(i);
3625
3626 -- Bug 4217917: If the Context is Forecast Generation and a resource
3627 -- has ETC but the Planning End Date falls prior to the ETC Start Date,
3628 -- then we should set the Planning End Date to the ETC Start Date.
3629
3630 IF p_fp_cols_rec.x_plan_class_code = 'FORECAST' THEN
3631 l_etc_quantity := l_sum_plan_quantity - l_sum_init_quantity;
3632 -- ER 5726773: Instead of requiring l_etc_quantity be positive,
3633 -- relax the condition to ensure it is non-zero.
3634 IF l_etc_quantity <> 0 AND l_max_end_date < l_etc_start_date THEN
3635 l_max_end_date := l_etc_start_date;
3636 END IF;
3637 END IF;
3638
3639 FORALL j IN 1..l_budget_line_id_tab.count
3640 UPDATE pa_budget_lines
3641 SET start_date = l_min_start_date,
3642 end_date = l_max_end_date
3643 WHERE budget_line_id = l_budget_line_id_tab(j);
3644
3645 IF l_planning_start_date_tab(i) <> l_min_start_date OR
3646 l_planning_end_date_tab(i) <> l_max_end_date THEN
3647 l_upd_index := l_upd_index + 1;
3648 l_upd_res_asg_id_tab(l_upd_index) := l_res_asg_id_tab(i);
3649 l_upd_planning_start_date_tab(l_upd_index) := l_min_start_date;
3650 l_upd_planning_end_date_tab(l_upd_index) := l_max_end_date;
3651 END IF;
3652 END IF;
3653 END LOOP;
3654
3655 FORALL i IN 1..l_upd_res_asg_id_tab.count
3656 UPDATE pa_resource_assignments
3657 SET planning_start_date = l_upd_planning_start_date_tab(i),
3658 planning_end_date = l_upd_planning_end_date_tab(i)
3659 WHERE resource_assignment_id = l_upd_res_asg_id_tab(i);
3660
3661 IF p_pa_debug_mode = 'Y' THEN
3662 PA_DEBUG.Reset_Curr_Function;
3663 END IF;
3664 EXCEPTION
3665 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3666 l_msg_count := FND_MSG_PUB.count_msg;
3667 IF l_msg_count = 1 THEN
3668 PA_INTERFACE_UTILS_PUB.get_messages
3669 ( p_encoded => FND_API.G_TRUE,
3670 p_msg_index => 1,
3671 p_msg_count => l_msg_count,
3672 p_msg_data => l_msg_data,
3673 p_data => l_data,
3674 p_msg_index_out => l_msg_index_out);
3675 x_msg_data := l_data;
3676 x_msg_count := l_msg_count;
3677 ELSE
3678 x_msg_count := l_msg_count;
3679 END IF;
3680 ROLLBACK;
3681
3682 x_return_status := FND_API.G_RET_STS_ERROR;
3683 IF P_PA_DEBUG_MODE = 'Y' THEN
3684 pa_fp_gen_amount_utils.fp_debug
3685 (p_msg => 'Invalid Arguments Passed',
3686 p_module_name => l_module_name,
3687 p_log_level => 5);
3688 PA_DEBUG.Reset_Curr_Function;
3689 END IF;
3690 RAISE;
3691 WHEN OTHERS THEN
3692 rollback;
3693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3694 x_msg_count := 1;
3695 x_msg_data := substr(sqlerrm,1,240);
3696 FND_MSG_PUB.add_exc_msg
3697 ( p_pkg_name => 'PA_FP_MAINTAIN_ACTUAL_PUB',
3698 p_procedure_name => 'SYNC_UP_PLANNING_DATES_NONE_TP',
3699 p_error_text => substr(sqlerrm,1,240));
3700 IF P_PA_DEBUG_MODE = 'Y' THEN
3701 pa_fp_gen_amount_utils.fp_debug
3702 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
3703 p_module_name => l_module_name,
3704 p_log_level => 5);
3705 PA_DEBUG.Reset_Curr_Function;
3706 END IF;
3707 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3708
3709 END SYNC_UP_PLANNING_DATES_NONE_TP;
3710
3711
3712 END PA_FP_MAINTAIN_ACTUAL_PUB;