[Home] [Help]
PACKAGE BODY: APPS.PA_RES_ASG_CURRENCY_PUB
Source
1 PACKAGE body PA_RES_ASG_CURRENCY_PUB as
2 /* $Header: PAFPRBCB.pls 120.7.12020000.5 2013/05/17 10:17:13 bpottipa ship $ */
3 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4
5 -- Private P_CALLING_MODULE values
6 G_PVT_MAINTAIN CONSTANT VARCHAR2(30) := 'MAINTAIN_DATA';
7 G_PVT_DELETE CONSTANT VARCHAR2(30) := 'DELETE_TABLE_RECORDS';
8 G_PVT_COPY CONSTANT VARCHAR2(30) := 'COPY_TABLE_RECORDS';
9 G_PVT_INSERT CONSTANT VARCHAR2(30) := 'INSERT_TABLE_RECORDS';
10 G_PVT_ROLLUP CONSTANT VARCHAR2(30) := 'ROLLUP_AMOUNTS';
11
12 --------------------------------------
13 --------- Local Functions -----------
14 --------------------------------------
15
16 FUNCTION IS_PUBLIC_CALLING_MODULE
17 ( P_CALLING_MODULE IN VARCHAR2 ) RETURN BOOLEAN
18 IS
19 BEGIN
20 RETURN p_calling_module IS NOT NULL AND
21 p_calling_module IN
22 ( G_BUDGET_GENERATION,
23 G_FORECAST_GENERATION,
24 G_CALCULATE_API,
25 G_UPDATE_PLAN_TRANSACTION,
26 G_WORKPLAN,
27 G_AMG_API,
28 G_WEBADI,
29 G_CHANGE_MGT,
30 G_COPY_PLAN,
31 G_UPGRADE );
32 END IS_PUBLIC_CALLING_MODULE;
33
34 FUNCTION IS_PRIVATE_CALLING_MODULE
35 ( P_CALLING_MODULE IN VARCHAR2 ) RETURN BOOLEAN
36 IS
37 BEGIN
38 RETURN p_calling_module IS NOT NULL AND
39 p_calling_module IN
40 ( G_PVT_MAINTAIN,
41 G_PVT_DELETE,
42 G_PVT_COPY,
43 G_PVT_INSERT,
44 G_PVT_ROLLUP );
45 END IS_PRIVATE_CALLING_MODULE;
46
47 FUNCTION IS_VALID_COPY_MODE
48 ( P_COPY_MODE IN VARCHAR2 ) RETURN BOOLEAN
49 IS
50 BEGIN
51 RETURN p_copy_mode IS NOT NULL AND
52 p_copy_mode IN
53 ( G_COPY_ALL,
54 G_COPY_OVERRIDES );
55 END IS_VALID_COPY_MODE;
56
57 FUNCTION IS_VALID_FLAG
58 ( P_FLAG IN VARCHAR2 ) RETURN BOOLEAN
59 IS
60 BEGIN
61 RETURN (p_flag IS NOT NULL) AND (p_flag IN ('Y','N'));
62 END IS_VALID_FLAG;
63
64 -----------------------------------------------------------------------
65 --------- Forward declarations for local/private procedures -----------
66 -----------------------------------------------------------------------
67
68 PROCEDURE PRINT_INPUT_PARAMS
69 ( P_CALLING_API IN VARCHAR2 DEFAULT NULL,
70 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
71 P_CALLING_MODULE IN VARCHAR2 DEFAULT NULL,
72 P_DELETE_FLAG IN VARCHAR2 DEFAULT NULL,
73 P_COPY_FLAG IN VARCHAR2 DEFAULT NULL,
74 P_SRC_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE DEFAULT NULL,
75 P_COPY_MODE IN VARCHAR2 DEFAULT NULL,
76 P_ROLLUP_FLAG IN VARCHAR2 DEFAULT NULL,
77 P_VERSION_LEVEL_FLAG IN VARCHAR2 DEFAULT NULL,
78 P_CALLED_MODE IN VARCHAR2 DEFAULT NULL );
79
80 PROCEDURE DELETE_TABLE_RECORDS
81 ( P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
85 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
82 P_CALLING_MODULE IN VARCHAR2,
83 P_VERSION_LEVEL_FLAG IN VARCHAR2 DEFAULT 'N',
84 P_CALLED_MODE IN VARCHAR2 DEFAULT 'SELF_SERVICE',
86 X_MSG_COUNT OUT NOCOPY NUMBER,
87 X_MSG_DATA OUT NOCOPY VARCHAR2);
88
89 PROCEDURE COPY_TABLE_RECORDS
90 ( P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
91 P_SRC_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE DEFAULT NULL,
92 P_COPY_MODE IN VARCHAR2 DEFAULT 'COPY_OVERRIDES',
93 P_CALLING_MODULE IN VARCHAR2,
94 P_VERSION_LEVEL_FLAG IN VARCHAR2 DEFAULT 'N',
95 P_CALLED_MODE IN VARCHAR2 DEFAULT 'SELF_SERVICE',
96 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
97 X_MSG_COUNT OUT NOCOPY NUMBER,
98 X_MSG_DATA OUT NOCOPY VARCHAR2);
99
100 PROCEDURE INSERT_TABLE_RECORDS
101 ( P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
102 P_CALLING_MODULE IN VARCHAR2,
103 P_VERSION_LEVEL_FLAG IN VARCHAR2 DEFAULT 'N',
104 P_CALLED_MODE IN VARCHAR2 DEFAULT 'SELF_SERVICE',
105 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
106 X_MSG_COUNT OUT NOCOPY NUMBER,
107 X_MSG_DATA OUT NOCOPY VARCHAR2);
108
109 PROCEDURE ROLLUP_AMOUNTS
110 ( P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
111 P_CALLING_MODULE IN VARCHAR2,
112 P_VERSION_LEVEL_FLAG IN VARCHAR2 DEFAULT 'N',
113 P_CALLED_MODE IN VARCHAR2 DEFAULT 'SELF_SERVICE',
114 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
115 X_MSG_COUNT OUT NOCOPY NUMBER,
116 X_MSG_DATA OUT NOCOPY VARCHAR2);
117
118 ------------------------------------------------------------------------------
119 --------- END OF Forward declarations for local/private procedures -----------
120 ------------------------------------------------------------------------------
121
122 ----------------------------------------
123 --------- Public Procedures ------------
124 ----------------------------------------
125
126 PROCEDURE MAINTAIN_DATA
127 ( P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
128 P_CALLING_MODULE IN VARCHAR2,
129 P_DELETE_FLAG IN VARCHAR2,
130 P_COPY_FLAG IN VARCHAR2,
131 P_SRC_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
132 P_COPY_MODE IN VARCHAR2,
133 P_ROLLUP_FLAG IN VARCHAR2,
134 P_VERSION_LEVEL_FLAG IN VARCHAR2,
135 P_CALLED_MODE IN VARCHAR2,
136 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
137 X_MSG_COUNT OUT NOCOPY NUMBER,
138 X_MSG_DATA OUT NOCOPY VARCHAR2 )
139 IS
140 l_module_name VARCHAR2(100) := 'pa.plsql.PA_RES_ASG_CURRENCY_PUB.MAINTAIN_DATA';
141 l_log_level NUMBER := 5;
142
143 l_msg_count NUMBER;
144 l_data VARCHAR2(2000);
145 l_msg_data VARCHAR2(2000);
146 l_msg_index_out NUMBER;
147
148 l_parameters_valid_flag VARCHAR2(1);
149
150 l_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
151 l_txn_currency_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
152 l_duplicate_count_tab PA_PLSQL_DATATYPES.IdTabTyp;
153 l_null_record_count NUMBER;
154
155 BEGIN
156 IF p_pa_debug_mode = 'Y' THEN
157 pa_debug.set_curr_function
158 ( p_function => 'MAINTAIN_DATA',
159 p_debug_mode => p_pa_debug_mode );
160 END IF;
161
162 PA_RES_ASG_CURRENCY_PUB.PRINT_INPUT_PARAMS
163 ( P_CALLING_API => G_PVT_MAINTAIN,
164 P_FP_COLS_REC => p_fp_cols_rec,
165 P_CALLING_MODULE => p_calling_module,
166 P_DELETE_FLAG => p_delete_flag,
167 P_COPY_FLAG => p_copy_flag,
168 P_SRC_VERSION_ID => p_src_version_id,
169 P_COPY_MODE => p_copy_mode,
170 P_ROLLUP_FLAG => p_rollup_flag,
171 P_VERSION_LEVEL_FLAG => p_version_level_flag,
172 P_CALLED_MODE => p_called_mode );
173
174 x_return_status := FND_API.G_RET_STS_SUCCESS;
175 x_msg_count := 0;
176
177 -- Step 0: Validate Input Parameters
178
179 l_parameters_valid_flag := 'Y';
180
181 IF p_fp_cols_rec.x_budget_version_id IS NULL THEN
182 IF p_pa_debug_mode = 'Y' THEN
183 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
184 ( P_MSG => 'P_fp_cols_rec.x_budget_version_id should not be null.',
185 P_CALLED_MODE => p_called_mode,
186 P_MODULE_NAME => l_module_name,
187 P_LOG_LEVEL => l_log_level );
188 END IF;
189 l_parameters_valid_flag := 'N';
190 END IF; -- minimum p_fp_cols_rec validation
191
195 ( P_MSG => 'Invalid p_calling_module value: '
192 IF NOT is_public_calling_module(p_calling_module) THEN
193 IF p_pa_debug_mode = 'Y' THEN
194 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
196 || p_calling_module,
197 P_CALLED_MODE => p_called_mode,
198 P_MODULE_NAME => l_module_name,
199 P_LOG_LEVEL => l_log_level );
200 END IF;
201 l_parameters_valid_flag := 'N';
202 END IF; -- p_calling_module validation
203
204 IF NOT is_valid_flag(p_delete_flag) THEN
205 IF p_pa_debug_mode = 'Y' THEN
206 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
207 ( P_MSG => 'Invalid p_delete_flag value: '
208 || p_delete_flag,
209 P_CALLED_MODE => p_called_mode,
210 P_MODULE_NAME => l_module_name,
211 P_LOG_LEVEL => l_log_level );
212 END IF;
213 l_parameters_valid_flag := 'N';
214 END IF; -- p_delete_flag validation
215
216 IF NOT is_valid_flag(p_copy_flag) THEN
217 IF p_pa_debug_mode = 'Y' THEN
218 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
219 ( P_MSG => 'Invalid p_copy_flag value: '
220 || p_copy_flag,
221 P_CALLED_MODE => p_called_mode,
222 P_MODULE_NAME => l_module_name,
223 P_LOG_LEVEL => l_log_level );
224 END IF;
225 l_parameters_valid_flag := 'N';
226 END IF; -- p_copy_flag validation
227
228 IF p_copy_flag = 'Y' AND
229 p_src_version_id IS NULL THEN
230 IF p_pa_debug_mode = 'Y' THEN
231 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
232 ( P_MSG => 'Since p_copy_flag is Y, p_src_version_id cannot be Null',
233 P_CALLED_MODE => p_called_mode,
234 P_MODULE_NAME => l_module_name,
235 P_LOG_LEVEL => l_log_level );
236 END IF;
237 l_parameters_valid_flag := 'N';
238 END IF; -- p_src_version_id validation
239
240 IF p_copy_flag = 'Y' AND
241 NOT IS_VALID_COPY_MODE(p_copy_mode) THEN
242 IF p_pa_debug_mode = 'Y' THEN
243 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
244 ( P_MSG => 'Since p_copy_flag is Y, invalid p_copy_mode value: '
245 || p_copy_mode,
246 P_CALLED_MODE => p_called_mode,
247 P_MODULE_NAME => l_module_name,
248 P_LOG_LEVEL => l_log_level );
249 END IF;
250 l_parameters_valid_flag := 'N';
251 END IF; -- p_copy_flag validation
252
253 IF NOT is_valid_flag(p_rollup_flag) THEN
254 IF p_pa_debug_mode = 'Y' THEN
255 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
256 ( P_MSG => 'Invalid p_rollup_flag value: '
257 || p_rollup_flag,
258 P_CALLED_MODE => p_called_mode,
259 P_MODULE_NAME => l_module_name,
260 P_LOG_LEVEL => l_log_level );
261 END IF;
262 l_parameters_valid_flag := 'N';
263 END IF; -- p_rollup_flag validation
264
265 IF NOT is_valid_flag(p_version_level_flag) THEN
266 IF p_pa_debug_mode = 'Y' THEN
267 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
268 ( P_MSG => 'Invalid p_version_level_flag value: '
269 || p_version_level_flag,
270 P_CALLED_MODE => p_called_mode,
271 P_MODULE_NAME => l_module_name,
272 P_LOG_LEVEL => l_log_level );
273 END IF;
274 l_parameters_valid_flag := 'N';
275 END IF; -- p_version_level_flag validation
276
277 -- Now that we have checked all of the input parameters,
278 -- raise an error if any of the parameters was invalid.
279 IF l_parameters_valid_flag = 'N' THEN
280 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
281 END IF;
282
283 -- Step 1: Handle Deletion
284 IF p_delete_flag = 'Y' THEN
285
286 IF p_pa_debug_mode = 'Y' THEN
287 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
288 P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.' ||
289 'DELETE_TABLE_RECORDS',
290 P_CALLED_MODE => p_called_mode,
291 P_MODULE_NAME => l_module_name);
292 END IF;
293 PA_RES_ASG_CURRENCY_PUB.DELETE_TABLE_RECORDS
294 ( P_FP_COLS_REC => p_fp_cols_rec,
295 P_CALLING_MODULE => p_calling_module,
296 P_VERSION_LEVEL_FLAG => p_version_level_flag,
297 P_CALLED_MODE => p_called_mode,
298 X_RETURN_STATUS => x_return_status,
299 X_MSG_COUNT => x_msg_count,
300 X_MSG_DATA => x_msg_data );
301 IF p_pa_debug_mode = 'Y' THEN
302 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
303 P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.' ||
304 'DELETE_TABLE_RECORDS: '||x_return_status,
305 P_CALLED_MODE => p_called_mode,
306 P_MODULE_NAME => l_module_name);
307 END IF;
308 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
309 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
313 PA_DEBUG.RESET_CURR_FUNCTION;
310 END IF;
311
312 IF P_PA_DEBUG_MODE = 'Y' THEN
314 END IF;
315
316 -- Return control to caller.
317 RETURN;
318 END IF; -- p_delete_flag = 'Y'
319
320 -- Step 0 (CONTD): Additional Temp Mode Validation
321 -- When processing non-delete operations in Temp Table Mode,
322 -- the following validation rules apply to temp table data:
323 -- 1. delete_flag should not be 'Y'.
324 -- Remove such records from the temp table.
325 -- 2A. Whenever copy_flag = 'Y'
326 -- (resource_assignment_id) must be unique.
327 -- Raise an error if duplicates are found.
328 -- 2B. Whenever copy_flag <> 'Y'
329 -- (resource_assignment_id, txn_currency_code) must be unique.
330 -- Raise an error if duplicates are found.
331 -- 3. (resource_assignment_id) should never be null.
332 -- Raise an error if any records with null resource_assignment_id are found.
333 -- 4. Whenever copy_flag <> 'Y',
334 -- txn_currency_code should not be null.
335 -- Raise an error if any records with null resource_assignment_id are found.
336
337
338 IF p_version_level_flag = 'N' THEN
339
340 -- 0.1. Remove records with delete_flag = 'Y' from temp table.
341 DELETE FROM pa_resource_asgn_curr_tmp
342 WHERE NVL(delete_flag,'N') = 'Y';
343
344 IF p_copy_flag = 'Y' THEN
345 -- 0.2A. Validate that no records in the temp table share the same
346 -- resource_assignment_id. When the context is Copy, currency
347 -- code is not populated in the temp table.
348 SELECT resource_assignment_id,
349 NULL,
350 count(*)
351 BULK COLLECT
352 INTO l_ra_id_tab,
353 l_txn_currency_code_tab,
354 l_duplicate_count_tab
355 FROM pa_resource_asgn_curr_tmp
356 GROUP BY resource_assignment_id
357 HAVING count(*) > 1;
358 ELSE
359 -- 0.2B. Validate that no records in the temp table share the same
360 -- (resource_assignment_id, txn_currency_code) combination.
361 SELECT resource_assignment_id,
362 txn_currency_code,
363 count(*)
364 BULK COLLECT
365 INTO l_ra_id_tab,
366 l_txn_currency_code_tab,
367 l_duplicate_count_tab
368 FROM pa_resource_asgn_curr_tmp
369 GROUP BY resource_assignment_id, txn_currency_code
370 HAVING count(*) > 1;
371 END IF; -- IF p_copy_flag = 'Y' THEN
372
373 -- Raise an error if duplicates are found.
374 IF l_ra_id_tab.count > 0 THEN
375 IF p_pa_debug_mode = 'Y' THEN
376 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
377 ( p_msg => 'Duplicate records found in '
378 || 'PA_RESOURCE_ASGN_CURR_TMP '
379 || '(count='||l_ra_id_tab.count||'):',
380 p_called_mode => p_called_mode,
381 p_module_name => l_module_name,
382 p_log_level => l_log_level );
383 FOR i IN 1..l_ra_id_tab.count LOOP
384 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
385 ( p_msg => 'Record'||i||': '
386 || 'resource_assignment_id:['||l_ra_id_tab(i)||'], '
387 || 'txn_currency_code:['||l_txn_currency_code_tab(i)||'], '
388 || 'number_of_duplicates:['||l_duplicate_count_tab(i)||'] ',
389 p_called_mode => p_called_mode,
390 p_module_name => l_module_name,
391 p_log_level => l_log_level );
392 END LOOP;
393 END IF; -- debug mode logic
394 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
395 END IF; -- duplicate record check
396
397 -- 0.3. Raise an error if any records with null resource_assignment_id are found.
398 SELECT count(*)
399 INTO l_null_record_count
400 FROM pa_resource_asgn_curr_tmp
401 WHERE resource_assignment_id IS NULL;
402
403 IF l_null_record_count > 0 THEN
404 IF p_pa_debug_mode = 'Y' THEN
405 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
406 ( p_msg => 'Records with null resource_assignment_id found in '
407 || 'PA_RESOURCE_ASGN_CURR_TMP '
408 || '(count='||l_null_record_count||'):',
409 p_called_mode => p_called_mode,
410 p_module_name => l_module_name,
411 p_log_level => l_log_level );
412 END IF;
413 --PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
414 -- p_msg_name => 'PA_RBC_RA_ID_NULL_ERR' );
415 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
416 END IF; -- IF l_null_record_count > 0 THEN
417
418 -- 0.4. Whenever copy_flag <> 'Y', txn_currency_code should not be null.
419 -- Raise an error if any records with null resource_assignment_id are found.
420 IF p_copy_flag <> 'Y' THEN
421 SELECT count(*)
422 INTO l_null_record_count
426 IF l_null_record_count > 0 THEN
423 FROM pa_resource_asgn_curr_tmp
424 WHERE txn_currency_code IS NULL;
425
427 IF p_pa_debug_mode = 'Y' THEN
428 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
429 ( p_msg => 'Records with null txn_currency_code found in '
430 || 'PA_RESOURCE_ASGN_CURR_TMP '
431 || '(count='||l_null_record_count||'):',
432 p_called_mode => p_called_mode,
433 p_module_name => l_module_name,
434 p_log_level => l_log_level );
435 END IF;
436 --PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
437 -- p_msg_name => 'PA_RBC_TXN_CUR_NULL_ERR' );
438 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
439 END IF; -- IF l_null_record_count > 0 THEN
440 END IF; --IF p_copy_flag = 'Y' THEN
441
442 END IF; -- additional Table Mode validation
443
444
445 -- Step 2: Handle Copy
446 IF p_copy_flag = 'Y' THEN
447
448 IF p_pa_debug_mode = 'Y' THEN
449 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
450 P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.' ||
451 'COPY_TABLE_RECORDS',
452 P_CALLED_MODE => p_called_mode,
453 P_MODULE_NAME => l_module_name);
454 END IF;
455 PA_RES_ASG_CURRENCY_PUB.COPY_TABLE_RECORDS
456 ( P_FP_COLS_REC => p_fp_cols_rec,
457 P_SRC_VERSION_ID => p_src_version_id,
458 P_COPY_MODE => p_copy_mode,
459 P_CALLING_MODULE => p_calling_module,
460 P_VERSION_LEVEL_FLAG => p_version_level_flag,
461 P_CALLED_MODE => p_called_mode,
462 X_RETURN_STATUS => x_return_status,
463 X_MSG_COUNT => x_msg_count,
464 X_MSG_DATA => x_msg_data );
465 IF p_pa_debug_mode = 'Y' THEN
466 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
467 P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.' ||
468 'COPY_TABLE_RECORDS: '||x_return_status,
469 P_CALLED_MODE => p_called_mode,
470 P_MODULE_NAME => l_module_name);
471 END IF;
472 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
473 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
474 END IF;
475
476 IF P_PA_DEBUG_MODE = 'Y' THEN
477 PA_DEBUG.RESET_CURR_FUNCTION;
478 END IF;
479
480 -- Return control to caller.
481 RETURN;
482 END IF; -- p_copy_flag = 'Y'
483
484
485 -- Step 3: Handle Insertion
486 IF p_rollup_flag = 'N' THEN
487
488 IF p_pa_debug_mode = 'Y' THEN
489 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
490 P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.' ||
491 'INSERT_TABLE_RECORDS',
492 P_CALLED_MODE => p_called_mode,
493 P_MODULE_NAME => l_module_name);
494 END IF;
495 PA_RES_ASG_CURRENCY_PUB.INSERT_TABLE_RECORDS
496 ( P_FP_COLS_REC => p_fp_cols_rec,
497 P_CALLING_MODULE => p_calling_module,
498 P_VERSION_LEVEL_FLAG => p_version_level_flag,
499 P_CALLED_MODE => p_called_mode,
500 X_RETURN_STATUS => x_return_status,
501 X_MSG_COUNT => x_msg_count,
502 X_MSG_DATA => x_msg_data );
503 IF p_pa_debug_mode = 'Y' THEN
504 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
505 P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.' ||
506 'INSERT_TABLE_RECORDS: '||x_return_status,
507 P_CALLED_MODE => p_called_mode,
508 P_MODULE_NAME => l_module_name);
509 END IF;
510 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
511 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
512 END IF;
513
514 -- Step 4: Handle Rollup of Amounts
515 ELSIF p_rollup_flag = 'Y' THEN
516
517 IF p_pa_debug_mode = 'Y' THEN
518 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
519 P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.' ||
520 'ROLLUP_AMOUNTS',
521 P_CALLED_MODE => p_called_mode,
522 P_MODULE_NAME => l_module_name);
523 END IF;
524
525 PA_RES_ASG_CURRENCY_PUB.ROLLUP_AMOUNTS
526 ( P_FP_COLS_REC => p_fp_cols_rec,
527 P_CALLING_MODULE => p_calling_module,
528 P_VERSION_LEVEL_FLAG => p_version_level_flag,
529 P_CALLED_MODE => p_called_mode,
530 X_RETURN_STATUS => x_return_status,
531 X_MSG_COUNT => x_msg_count,
532 X_MSG_DATA => x_msg_data );
533 IF p_pa_debug_mode = 'Y' THEN
534 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
535 P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.' ||
539 END IF;
536 'ROLLUP_AMOUNTS: '||x_return_status,
537 P_CALLED_MODE => p_called_mode,
538 P_MODULE_NAME => l_module_name);
540 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
541 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
542 END IF;
543
544 END IF; -- p_rollup_flag = 'Y'
545
546
547 IF P_PA_DEBUG_MODE = 'Y' THEN
548 PA_DEBUG.RESET_CURR_FUNCTION;
549 END IF;
550
551 EXCEPTION
552 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
553
554 l_msg_count := FND_MSG_PUB.count_msg;
555 IF l_msg_count = 1 THEN
556 PA_INTERFACE_UTILS_PUB.get_messages
557 ( p_encoded => FND_API.G_TRUE,
558 p_msg_index => 1,
559 p_msg_count => l_msg_count,
560 p_msg_data => l_msg_data,
561 p_data => l_data,
562 p_msg_index_out => l_msg_index_out);
563 x_msg_data := l_data;
564 x_msg_count := l_msg_count;
565 ELSE
566 x_msg_count := l_msg_count;
567 END IF;
568
569 -- Removed ROLLBACK statement.
570
571 x_return_status := FND_API.G_RET_STS_ERROR;
572
573 IF P_PA_DEBUG_MODE = 'Y' THEN
574 pa_fp_gen_amount_utils.fp_debug
575 ( p_msg => 'Invalid Arguments Passed',
576 p_called_mode => p_called_mode,
577 p_module_name => l_module_name,
578 p_log_level => l_log_level );
579 PA_DEBUG.RESET_CURR_FUNCTION;
580 END IF;
581
582 -- Removed RAISE statement.
583
584 WHEN OTHERS THEN
585 -- Removed ROLLBACK statement.
586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
587 x_msg_count := 1;
588 x_msg_data := substr(sqlerrm,1,240);
589 -- dbms_output.put_line('error msg :'||x_msg_data);
590 FND_MSG_PUB.add_exc_msg
591 ( p_pkg_name => 'PA_RES_ASG_CURRENCY_PUB',
592 p_procedure_name => 'MAINTAIN_DATA',
593 p_error_text => substr(sqlerrm,1,240));
594 IF P_PA_DEBUG_MODE = 'Y' THEN
595 pa_fp_gen_amount_utils.fp_debug
596 ( p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
597 p_called_mode => p_called_mode,
598 p_module_name => l_module_name,
599 p_log_level => l_log_level);
600 PA_DEBUG.RESET_CURR_FUNCTION;
601 END IF;
602 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
603
604 END MAINTAIN_DATA;
605
606
607 -----------------------------------------
608 --------- Private Procedures ------------
609 -----------------------------------------
610
611 PROCEDURE PRINT_INPUT_PARAMS
612 ( P_CALLING_API IN VARCHAR2,
613 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
614 P_CALLING_MODULE IN VARCHAR2,
615 P_DELETE_FLAG IN VARCHAR2,
616 P_COPY_FLAG IN VARCHAR2,
617 P_SRC_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
618 P_COPY_MODE IN VARCHAR2,
619 P_ROLLUP_FLAG IN VARCHAR2,
620 P_VERSION_LEVEL_FLAG IN VARCHAR2,
621 P_CALLED_MODE IN VARCHAR2 )
622 IS
623 l_module_name VARCHAR2(100) := 'pa.plsql.PA_RES_ASG_CURRENCY_PUB.PRINT_INPUT_PARAMS';
624 l_log_level NUMBER := 5;
625 BEGIN
626 IF p_pa_debug_mode = 'N' THEN
627 RETURN;
628 END IF; -- IF p_pa_debug_mode = 'N' THEN
629
630 -- Print values of Input Parameters based on p_calling_module
631 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
632 ( p_msg => 'Input Parameters to '
633 || p_calling_api || '():',
634 p_called_mode => p_called_mode,
635 p_module_name => l_module_name,
636 p_log_level => l_log_level );
637 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
638 ( p_msg => 'P_FP_COLS_REC.X_BUDGET_VERSION_ID:['
639 || P_FP_COLS_REC.X_BUDGET_VERSION_ID||']',
640 p_called_mode => p_called_mode,
641 p_module_name => l_module_name,
642 p_log_level => l_log_level );
643 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
644 ( p_msg => 'P_FP_COLS_REC.X_VERSION_TYPE:['
645 || P_FP_COLS_REC.X_VERSION_TYPE||']',
646 p_called_mode => p_called_mode,
647 p_module_name => l_module_name,
648 p_log_level => l_log_level );
649 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
650 ( p_msg => 'P_FP_COLS_REC.X_PLAN_CLASS_CODE:['
651 || P_FP_COLS_REC.X_PLAN_CLASS_CODE||']',
652 p_called_mode => p_called_mode,
653 p_module_name => l_module_name,
654 p_log_level => l_log_level );
655 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
656 ( p_msg => 'P_CALLING_MODULE:['||P_CALLING_MODULE||']',
657 p_called_mode => p_called_mode,
658 p_module_name => l_module_name,
659 p_log_level => l_log_level );
660
661 IF p_calling_api = G_PVT_MAINTAIN THEN
662 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
663 ( p_msg => 'P_DELETE_FLAG:['||P_DELETE_FLAG||']',
667 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
664 p_called_mode => p_called_mode,
665 p_module_name => l_module_name,
666 p_log_level => l_log_level );
668 ( p_msg => 'P_COPY_FLAG:['||P_COPY_FLAG||']',
669 p_called_mode => p_called_mode,
670 p_module_name => l_module_name,
671 p_log_level => l_log_level );
672 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
673 ( p_msg => 'P_ROLLUP_FLAG:['||P_ROLLUP_FLAG||']',
674 p_called_mode => p_called_mode,
675 p_module_name => l_module_name,
676 p_log_level => l_log_level );
677 END IF; -- IF p_calling_api = G_PVT_MAINTAIN THEN
678
679 IF p_calling_api IN ( G_PVT_MAINTAIN, G_PVT_COPY ) THEN
680 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
681 ( p_msg => 'P_SRC_VERSION_ID:['||P_SRC_VERSION_ID||']',
682 p_called_mode => p_called_mode,
683 p_module_name => l_module_name,
684 p_log_level => l_log_level );
685 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
686 ( p_msg => 'P_COPY_MODE:['||P_COPY_MODE||']',
687 p_called_mode => p_called_mode,
688 p_module_name => l_module_name,
689 p_log_level => l_log_level );
690 END IF; --IF p_calling_api IN ( G_PVT_MAINTAIN, G_PVT_COPY ) THEN
691
692 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
693 ( p_msg => 'P_VERSION_LEVEL_FLAG:['||P_VERSION_LEVEL_FLAG||']',
694 p_called_mode => p_called_mode,
695 p_module_name => l_module_name,
696 p_log_level => l_log_level );
697 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
698 ( p_msg => 'P_CALLED_MODE:['||P_CALLED_MODE||']',
699 p_called_mode => p_called_mode,
700 p_module_name => l_module_name,
701 p_log_level => l_log_level );
702
703
704 -- Print PA_RESOURCE_ASGN_CURR_TMP data
705 IF p_version_level_flag = 'N' THEN
706 -- Print values of PA_RESOURCE_ASGN_CURR_TMP data
707 DECLARE
708 l_dbg_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
709 l_dbg_txn_currency_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
710 l_dbg_rc_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
711 l_dbg_bc_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
712 l_dbg_bill_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
713 l_dbg_delete_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
714 BEGIN
715 SELECT resource_assignment_id,
716 txn_currency_code,
717 txn_raw_cost_rate_override,
718 txn_burden_cost_rate_override,
719 txn_bill_rate_override,
720 delete_flag
721 BULK COLLECT
722 INTO l_dbg_ra_id_tab,
723 l_dbg_txn_currency_code_tab,
724 l_dbg_rc_rate_override_tab,
725 l_dbg_bc_rate_override_tab,
726 l_dbg_bill_rate_override_tab,
727 l_dbg_delete_flag_tab
728 FROM pa_resource_asgn_curr_tmp
729 ORDER BY resource_assignment_id, txn_currency_code;
730
731 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
732 ( p_msg => 'Printing PA_RESOURCE_ASGN_CURR_TMP table data '
733 || '(count='||l_dbg_ra_id_tab.count||'):',
734 p_called_mode => p_called_mode,
735 p_module_name => l_module_name,
736 p_log_level => l_log_level );
737
738 FOR i IN 1..l_dbg_ra_id_tab.count LOOP
739 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
740 ( p_msg => 'Record'||i||': '
741 || 'resource_assignment_id:['||l_dbg_ra_id_tab(i)||'], '
742 || 'txn_currency_code:['||l_dbg_txn_currency_code_tab(i)||'], '
743 || 'txn_raw_cost_rate_override:['||l_dbg_rc_rate_override_tab(i)||'], '
744 || 'txn_burden_cost_rate_override:['||l_dbg_bc_rate_override_tab(i)||'], '
745 || 'txn_bill_rate_override:['||l_dbg_bill_rate_override_tab(i)||'], '
746 || 'delete_flag:['||l_dbg_delete_flag_tab(i)||']',
747 p_called_mode => p_called_mode,
748 p_module_name => l_module_name,
749 p_log_level => l_log_level );
750 END LOOP; -- FOR i IN 1..l_dbg_ra_id_tab.count LOOP
751 END; -- internal begin/end block
752 END IF; --IF p_version_level_flag = 'N' THEN
753
754 END PRINT_INPUT_PARAMS;
755
756
757 PROCEDURE DELETE_TABLE_RECORDS
758 ( P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
759 P_CALLING_MODULE IN VARCHAR2,
760 P_VERSION_LEVEL_FLAG IN VARCHAR2,
761 P_CALLED_MODE IN VARCHAR2,
762 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
763 X_MSG_COUNT OUT NOCOPY NUMBER,
764 X_MSG_DATA OUT NOCOPY VARCHAR2)
765 IS
766 l_module_name VARCHAR2(100) := 'pa.plsql.PA_RES_ASG_CURRENCY_PUB.DELETE_TABLE_RECORDS';
767 l_log_level NUMBER := 5;
768
769 l_msg_count NUMBER;
770 l_data VARCHAR2(2000);
771 l_msg_data VARCHAR2(2000);
772 l_msg_index_out NUMBER;
773
774 BEGIN
775 IF p_pa_debug_mode = 'Y' THEN
779 END IF;
776 PA_DEBUG.SET_CURR_FUNCTION
777 ( p_function => 'DELETE_TABLE_RECORDS',
778 p_debug_mode => p_pa_debug_mode );
780
781 PA_RES_ASG_CURRENCY_PUB.PRINT_INPUT_PARAMS
782 ( P_CALLING_API => G_PVT_DELETE,
783 P_FP_COLS_REC => p_fp_cols_rec,
784 P_CALLING_MODULE => p_calling_module,
785 P_VERSION_LEVEL_FLAG => p_version_level_flag,
786 P_CALLED_MODE => p_called_mode );
787
788 x_return_status := FND_API.G_RET_STS_SUCCESS;
789 x_msg_count := 0;
790
791 IF p_version_level_flag = 'Y' THEN
792
793 -- VERSION LEVEL Mode:
794 -- Delete records from pa_resource_asgn_curr for the version.
795
796 DELETE FROM pa_resource_asgn_curr rbc
797 WHERE rbc.budget_version_id = p_fp_cols_rec.x_budget_version_id;
798
799 ELSIF p_version_level_flag = 'N' THEN
800
801 IF is_public_calling_module(p_calling_module) THEN
802
803 -- TEMP TABLE Mode:
804 -- Delete records from pa_resource_asgn_curr for all the
805 -- planning resource + currency combinations specified in
806 -- pa_resource_asgn_curr_tmp with delete_flag = 'Y'.
807
808 DELETE FROM pa_resource_asgn_curr rbc
809 WHERE rbc.budget_version_id = p_fp_cols_rec.x_budget_version_id
810 AND EXISTS ( SELECT null
811 FROM pa_resource_asgn_curr_tmp tmp
812 WHERE NVL(tmp.delete_flag,'N') = 'Y'
813 AND rbc.resource_assignment_id = tmp.resource_assignment_id
814 AND rbc.txn_currency_code =
815 NVL(tmp.txn_currency_code,rbc.txn_currency_code) );
816
817 ELSIF is_private_calling_module(p_calling_module) THEN
818
819 -- TEMP TABLE Mode:
820 -- Delete records from pa_resource_asgn_curr for all the
821 -- planning resource + currency combinations specified in
822 -- pa_resource_asgn_curr_tmp without checking delete_flag.
823 /* replaced the delete statement with the one below for bug 14040849
824 DELETE FROM pa_resource_asgn_curr rbc
825 WHERE rbc.budget_version_id = p_fp_cols_rec.x_budget_version_id
826 AND EXISTS ( SELECT null
827 FROM pa_resource_asgn_curr_tmp tmp
828 WHERE rbc.resource_assignment_id = tmp.resource_assignment_id
829 AND rbc.txn_currency_code =
830 NVL(tmp.txn_currency_code,rbc.txn_currency_code) );
831 */
832 DELETE FROM PA_RESOURCE_ASGN_CURR RBC
833 where rbc.rowid in (
834 select /*+ leading(tmp) cardinality(tmp,1) INDEX(RBC1 PA_RESOURCE_ASGN_CURR_U2) */ rbc1.rowid
835 from PA_RESOURCE_ASGN_CURR RBC1,
836 PA_RESOURCE_ASGN_CURR_TMP TMP
837 where RBC1.BUDGET_VERSION_ID = p_fp_cols_rec.x_budget_version_id
838 AND RBC1.RESOURCE_ASSIGNMENT_ID = TMP.RESOURCE_ASSIGNMENT_ID
839 AND RBC1.TXN_CURRENCY_CODE = NVL(TMP.TXN_CURRENCY_CODE,RBC1.TXN_CURRENCY_CODE));
840 --end of 14040849
841 END IF; -- p_calling_module check
842
843 END IF; -- p_version_level_flag check
844
845 IF P_PA_DEBUG_MODE = 'Y' THEN
846 PA_DEBUG.RESET_CURR_FUNCTION;
847 END IF;
848 EXCEPTION
849 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
850
851 l_msg_count := FND_MSG_PUB.count_msg;
852 IF l_msg_count = 1 THEN
853 PA_INTERFACE_UTILS_PUB.get_messages
854 ( p_encoded => FND_API.G_TRUE,
855 p_msg_index => 1,
856 p_msg_count => l_msg_count,
857 p_msg_data => l_msg_data,
858 p_data => l_data,
859 p_msg_index_out => l_msg_index_out);
860 x_msg_data := l_data;
861 x_msg_count := l_msg_count;
862 ELSE
863 x_msg_count := l_msg_count;
864 END IF;
865
866 -- Removed ROLLBACK statement.
867
868 x_return_status := FND_API.G_RET_STS_ERROR;
869
870 IF P_PA_DEBUG_MODE = 'Y' THEN
871 pa_fp_gen_amount_utils.fp_debug
872 ( p_msg => 'Invalid Arguments Passed',
873 p_called_mode => p_called_mode,
874 p_module_name => l_module_name,
875 p_log_level => l_log_level );
876 PA_DEBUG.RESET_CURR_FUNCTION;
877 END IF;
878 -- Removed RAISE statement.
879
880 WHEN OTHERS THEN
881 -- Removed ROLLBACK statement.
882 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
883 x_msg_count := 1;
884 x_msg_data := substr(sqlerrm,1,240);
885 -- dbms_output.put_line('error msg :'||x_msg_data);
886 FND_MSG_PUB.add_exc_msg
887 ( p_pkg_name => 'PA_RES_ASG_CURRENCY_PUB',
888 p_procedure_name => 'DELETE_TABLE_RECORDS',
889 p_error_text => substr(sqlerrm,1,240));
890 IF P_PA_DEBUG_MODE = 'Y' THEN
891 pa_fp_gen_amount_utils.fp_debug
892 ( p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
893 p_called_mode => p_called_mode,
894 p_module_name => l_module_name,
895 p_log_level => l_log_level);
896 PA_DEBUG.RESET_CURR_FUNCTION;
897 END IF;
898 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
902
899
900 END DELETE_TABLE_RECORDS;
901
903 PROCEDURE COPY_TABLE_RECORDS
904 ( P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
905 P_SRC_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
906 P_COPY_MODE IN VARCHAR2,
907 P_CALLING_MODULE IN VARCHAR2,
908 P_VERSION_LEVEL_FLAG IN VARCHAR2,
909 P_CALLED_MODE IN VARCHAR2,
910 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
911 X_MSG_COUNT OUT NOCOPY NUMBER,
912 X_MSG_DATA OUT NOCOPY VARCHAR2)
913 IS
914 l_module_name VARCHAR2(100) := 'pa.plsql.PA_RES_ASG_CURRENCY_PUB.COPY_TABLE_RECORDS';
915 l_log_level NUMBER := 5;
916
917 l_msg_count NUMBER;
918 l_data VARCHAR2(2000);
919 l_msg_data VARCHAR2(2000);
920 l_msg_index_out NUMBER;
921
922 l_last_updated_by NUMBER := FND_GLOBAL.user_id;
923 l_last_update_login NUMBER := FND_GLOBAL.login_id;
924 l_sysdate DATE := SYSDATE;
925 l_record_version_number NUMBER := 1;
926
927 -- This cursor gets all the source pa_resource_asgn_curr records
928 -- for the entire version specified by c_src_version_id.
929 -- This cursor should be used when p_version_level_flag is 'Y'
930 -- and p_copy_mode is 'COPY_ALL'.
931
932 CURSOR ver_lvl_copy_all_csr
933 ( c_src_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE,
934 c_tgt_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE ) IS
935 SELECT tgt_ra.RESOURCE_ASSIGNMENT_ID,
936 src_rbc.TXN_CURRENCY_CODE,
937 src_rbc.TOTAL_QUANTITY,
938 src_rbc.TOTAL_INIT_QUANTITY,
939 src_rbc.TXN_RAW_COST_RATE_OVERRIDE,
940 src_rbc.TXN_BURDEN_COST_RATE_OVERRIDE,
941 src_rbc.TXN_BILL_RATE_OVERRIDE,
942 src_rbc.TXN_AVERAGE_RAW_COST_RATE,
943 src_rbc.TXN_AVERAGE_BURDEN_COST_RATE,
944 src_rbc.TXN_AVERAGE_BILL_RATE,
945 src_rbc.TXN_ETC_RAW_COST_RATE,
946 src_rbc.TXN_ETC_BURDEN_COST_RATE,
947 src_rbc.TXN_ETC_BILL_RATE,
948 src_rbc.TOTAL_TXN_RAW_COST,
949 src_rbc.TOTAL_TXN_BURDENED_COST,
950 src_rbc.TOTAL_TXN_REVENUE,
951 src_rbc.TOTAL_TXN_INIT_RAW_COST,
952 src_rbc.TOTAL_TXN_INIT_BURDENED_COST,
953 src_rbc.TOTAL_TXN_INIT_REVENUE,
954 src_rbc.TOTAL_PROJECT_RAW_COST,
955 src_rbc.TOTAL_PROJECT_BURDENED_COST,
956 src_rbc.TOTAL_PROJECT_REVENUE,
957 src_rbc.TOTAL_PROJECT_INIT_RAW_COST,
958 src_rbc.TOTAL_PROJECT_INIT_BD_COST,
959 src_rbc.TOTAL_PROJECT_INIT_REVENUE,
960 src_rbc.TOTAL_PROJFUNC_RAW_COST,
961 src_rbc.TOTAL_PROJFUNC_BURDENED_COST,
962 src_rbc.TOTAL_PROJFUNC_REVENUE,
963 src_rbc.TOTAL_PROJFUNC_INIT_RAW_COST,
964 src_rbc.TOTAL_PROJFUNC_INIT_BD_COST,
965 src_rbc.TOTAL_PROJFUNC_INIT_REVENUE,
966 src_rbc.TOTAL_DISPLAY_QUANTITY
967 FROM pa_resource_assignments tgt_ra,
968 pa_resource_assignments src_ra,
969 pa_resource_asgn_curr src_rbc
970 WHERE src_rbc.budget_version_id = c_src_version_id
971 AND src_ra.resource_assignment_id = src_rbc.resource_assignment_id
972 AND nvl(tgt_ra.task_id,0) = nvl(src_ra.task_id,0)
973 AND tgt_ra.resource_list_member_id = src_ra.resource_list_member_id
974 AND nvl(tgt_ra.cbs_element_id,-1) = nvl(src_ra.cbs_element_id,-1) --bug#16791711
975 AND tgt_ra.budget_version_id = c_tgt_version_id;
976
977 -- This cursor gets overrides from the source pa_resource_asgn_curr
978 -- records for the entire version specified by c_src_version_id.
979 -- This cursor should be used when p_version_level_flag is 'Y'
980 -- and p_copy_mode is 'COPY_OVERRIDES'.
981
982 CURSOR ver_lvl_copy_overrides_csr
983 ( c_src_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE,
984 c_tgt_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE ) IS
985 SELECT tgt_ra.RESOURCE_ASSIGNMENT_ID,
986 src_rbc.TXN_CURRENCY_CODE,
987 src_rbc.TXN_RAW_COST_RATE_OVERRIDE,
988 src_rbc.TXN_BURDEN_COST_RATE_OVERRIDE,
989 src_rbc.TXN_BILL_RATE_OVERRIDE
990 FROM pa_resource_assignments tgt_ra,
991 pa_resource_assignments src_ra,
992 pa_resource_asgn_curr src_rbc
993 WHERE src_rbc.budget_version_id = c_src_version_id
994 AND src_ra.resource_assignment_id = src_rbc.resource_assignment_id
995 AND nvl(tgt_ra.task_id,0) = nvl(src_ra.task_id,0)
996 AND tgt_ra.resource_list_member_id = src_ra.resource_list_member_id
997 AND nvl(tgt_ra.cbs_element_id,-1) = nvl(src_ra.cbs_element_id,-1) --bug#16791711
998 AND tgt_ra.budget_version_id = c_tgt_version_id;
999
1000 -- This cursor gets overrides from the source pa_resource_asgn_curr
1001 -- records for the resources specified in pa_resource_asgn_curr_tmp
1002 -- for the version specified by c_src_version_id.
1003 -- This cursor should be used when p_version_level_flag is 'N'
1004 -- and p_copy_mode is 'COPY_OVERRIDES'.
1005 -- Note: Ordered hint has been added to avoid a Merge Join Cartesian
1006 -- join order in the execution plan.
1007
1008 CURSOR tbl_mode_copy_overrides_csr
1009 ( c_src_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE,
1010 c_tgt_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE ) IS
1011 SELECT DISTINCT /*+ ORDERED */
1012 tmp.RESOURCE_ASSIGNMENT_ID,
1013 src_rbc.TXN_CURRENCY_CODE,
1014 src_rbc.TXN_RAW_COST_RATE_OVERRIDE,
1015 src_rbc.TXN_BURDEN_COST_RATE_OVERRIDE,
1016 src_rbc.TXN_BILL_RATE_OVERRIDE
1020 pa_resource_asgn_curr_tmp tmp
1017 FROM pa_resource_asgn_curr src_rbc,
1018 pa_resource_assignments src_ra,
1019 pa_resource_assignments tgt_ra,
1021 WHERE tgt_ra.budget_version_id = c_tgt_version_id
1022 AND src_ra.budget_version_id = c_src_version_id
1023 AND tgt_ra.resource_assignment_id = tmp.resource_assignment_id
1024 AND nvl(src_ra.task_id,0) = nvl(tgt_ra.task_id,0)
1025 AND src_ra.resource_list_member_id = tgt_ra.resource_list_member_id
1026 AND nvl(tgt_ra.cbs_element_id,-1) = nvl(src_ra.cbs_element_id,-1) --bug#16791711
1027 AND src_rbc.resource_assignment_id = src_ra.resource_assignment_id
1028 AND src_rbc.budget_version_id = src_ra.budget_version_id;
1029
1030
1031 -- PL/SQL tables for storing copied source rates and amount totals
1032 l_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1033 l_txn_currency_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
1034 l_total_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
1035 l_total_init_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
1036 l_raw_cost_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
1037 l_brdn_cost_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
1038 l_bill_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
1039 l_avg_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1040 l_avg_burden_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1041 l_avg_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1042 l_etc_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1043 l_etc_burden_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1044 l_etc_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1045 l_txn_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1046 l_txn_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1047 l_txn_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
1048 l_txn_init_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1049 l_txn_init_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1050 l_txn_init_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
1051 l_pc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1052 l_pc_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1053 l_pc_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
1054 l_pc_init_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1055 l_pc_init_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1056 l_pc_init_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
1057 l_pfc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1058 l_pfc_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1059 l_pfc_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
1060 l_pfc_init_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1061 l_pfc_init_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1062 l_pfc_init_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
1063 l_display_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
1064
1065 l_NULL_NumTabTyp PA_PLSQL_DATATYPES.NumTabTyp;
1066
1067 -- Indicates if the Target version is a Workplan.
1068 l_wp_version_flag PA_BUDGET_VERSIONS.WP_VERSION_FLAG%TYPE;
1069
1070 BEGIN
1071 IF p_pa_debug_mode = 'Y' THEN
1072 PA_DEBUG.SET_CURR_FUNCTION
1073 ( p_function => 'COPY_TABLE_RECORDS',
1074 p_debug_mode => p_pa_debug_mode );
1075 END IF;
1076
1077 PA_RES_ASG_CURRENCY_PUB.PRINT_INPUT_PARAMS
1078 ( P_CALLING_API => G_PVT_COPY,
1079 P_FP_COLS_REC => p_fp_cols_rec,
1080 P_CALLING_MODULE => p_calling_module,
1081 P_SRC_VERSION_ID => p_src_version_id,
1082 P_COPY_MODE => p_copy_mode,
1083 P_VERSION_LEVEL_FLAG => p_version_level_flag,
1084 P_CALLED_MODE => p_called_mode );
1085
1086 x_return_status := FND_API.G_RET_STS_SUCCESS;
1087 x_msg_count := 0;
1088
1089 BEGIN
1090 SELECT nvl(wp_version_flag,'N')
1091 INTO l_wp_version_flag
1092 FROM pa_budget_versions
1093 WHERE budget_version_id = p_fp_cols_rec.x_budget_version_id;
1094 EXCEPTION WHEN NO_DATA_FOUND THEN
1095 IF p_pa_debug_mode = 'Y' THEN
1096 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1097 ( P_MSG => 'Invalid p_fp_cols_rec.x_budget_version_id value: '
1098 || p_fp_cols_rec.x_budget_version_id
1099 || '. Budget version does not exist.',
1100 P_CALLED_MODE => p_called_mode,
1101 P_MODULE_NAME => l_module_name,
1102 P_LOG_LEVEL => l_log_level );
1103 END IF;
1104 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1105 END;
1106
1107 -- Step 1: Select rates and amounts from the appropriate cursor.
1108
1109 IF p_version_level_flag = 'Y' THEN
1110
1111 IF p_copy_mode = G_COPY_ALL THEN
1112 OPEN ver_lvl_copy_all_csr
1113 (p_src_version_id,
1114 p_fp_cols_rec.x_budget_version_id);
1115 FETCH ver_lvl_copy_all_csr
1116 BULK COLLECT
1117 INTO l_ra_id_tab,
1118 l_txn_currency_code_tab,
1119 l_total_quantity_tab,
1120 l_total_init_quantity_tab,
1121 l_raw_cost_rate_override_tab,
1122 l_brdn_cost_rate_override_tab,
1123 l_bill_rate_override_tab,
1124 l_avg_raw_cost_rate_tab,
1125 l_avg_burden_cost_rate_tab,
1126 l_avg_bill_rate_tab,
1127 l_etc_raw_cost_rate_tab,
1128 l_etc_burden_cost_rate_tab,
1129 l_etc_bill_rate_tab,
1130 l_txn_raw_cost_tab,
1131 l_txn_burdened_cost_tab,
1132 l_txn_revenue_tab,
1133 l_txn_init_raw_cost_tab,
1134 l_txn_init_burdened_cost_tab,
1135 l_txn_init_revenue_tab,
1136 l_pc_raw_cost_tab,
1137 l_pc_burdened_cost_tab,
1138 l_pc_revenue_tab,
1139 l_pc_init_raw_cost_tab,
1140 l_pc_init_burdened_cost_tab,
1141 l_pc_init_revenue_tab,
1142 l_pfc_raw_cost_tab,
1143 l_pfc_burdened_cost_tab,
1144 l_pfc_revenue_tab,
1145 l_pfc_init_raw_cost_tab,
1146 l_pfc_init_burdened_cost_tab,
1147 l_pfc_init_revenue_tab,
1148 l_display_quantity_tab;
1149 CLOSE ver_lvl_copy_all_csr;
1150 ELSIF p_copy_mode = G_COPY_OVERRIDES THEN
1151 OPEN ver_lvl_copy_overrides_csr
1152 (p_src_version_id,
1153 p_fp_cols_rec.x_budget_version_id);
1154 FETCH ver_lvl_copy_overrides_csr
1155 BULK COLLECT
1156 INTO l_ra_id_tab,
1157 l_txn_currency_code_tab,
1158 l_raw_cost_rate_override_tab,
1159 l_brdn_cost_rate_override_tab,
1160 l_bill_rate_override_tab;
1161 CLOSE ver_lvl_copy_overrides_csr;
1162 END IF; -- p_copy_mode check
1163
1164 ELSIF p_version_level_flag = 'N' THEN
1165
1166 IF p_copy_mode = G_COPY_ALL THEN
1167 -- This case is currently NOT supported.
1168 IF p_pa_debug_mode = 'Y' THEN
1169 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1170 ( P_MSG => 'The '''||G_COPY_ALL||''' copy mode is not supported '
1171 || 'when p_version_level_flag is '
1172 || p_version_level_flag,
1173 P_CALLED_MODE => p_called_mode,
1174 P_MODULE_NAME => l_module_name,
1175 P_LOG_LEVEL => l_log_level );
1176 END IF;
1177 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1178 ELSIF p_copy_mode = G_COPY_OVERRIDES THEN
1179 OPEN tbl_mode_copy_overrides_csr
1180 (p_src_version_id,
1181 p_fp_cols_rec.x_budget_version_id);
1182 FETCH tbl_mode_copy_overrides_csr
1183 BULK COLLECT
1184 INTO l_ra_id_tab,
1185 l_txn_currency_code_tab,
1186 l_raw_cost_rate_override_tab,
1187 l_brdn_cost_rate_override_tab,
1188 l_bill_rate_override_tab;
1189 CLOSE tbl_mode_copy_overrides_csr;
1190 END IF; -- p_copy_mode check
1191
1192 END IF; -- p_version_level_flag check
1193
1194 -- No further processing is required if there are no records to copy.
1195 IF l_ra_id_tab.count <= 0 THEN
1196 IF P_PA_DEBUG_MODE = 'Y' THEN
1197 PA_DEBUG.RESET_CURR_FUNCTION;
1198 END IF;
1199 RETURN;
1200 END IF;
1201
1202 -- Step 2: Process pl/sql tables as needed.
1203
1204 -- Initialize a pl/sql table of length l_ra_id_tab.count with nulls.
1205 -- We can use this table to null out entire tables during processing.
1206 -- This should perform better than nulling out records in a loop.
1207 l_null_NumTabTyp.delete;
1208 FOR i IN 1..l_ra_id_tab.count LOOP
1209 l_null_NumTabTyp(i) := null;
1210 END LOOP;
1211
1212 IF p_copy_mode = G_COPY_ALL THEN
1213 -- ETC Rate columns should Null for Budgets,
1214 -- but should be populated for Forecasts and Workplans.
1215 -- Additionally, Actuals columns should be nulled out.
1216 IF l_wp_version_flag = 'N' AND
1217 p_fp_cols_rec.x_plan_class_code = 'BUDGET' THEN
1218 l_etc_raw_cost_rate_tab := l_null_NumTabTyp;
1219 l_etc_burden_cost_rate_tab := l_null_NumTabTyp;
1220 l_etc_bill_rate_tab := l_null_NumTabTyp;
1221 l_total_init_quantity_tab := l_null_NumTabTyp;
1222 l_txn_init_raw_cost_tab := l_null_NumTabTyp;
1223 l_txn_init_burdened_cost_tab := l_null_NumTabTyp;
1224 l_txn_init_revenue_tab := l_null_NumTabTyp;
1225 l_pc_init_raw_cost_tab := l_null_NumTabTyp;
1226 l_pc_init_burdened_cost_tab := l_null_NumTabTyp;
1227 l_pc_init_revenue_tab := l_null_NumTabTyp;
1228 l_pfc_init_raw_cost_tab := l_null_NumTabTyp;
1229 l_pfc_init_burdened_cost_tab := l_null_NumTabTyp;
1230 l_pfc_init_revenue_tab := l_null_NumTabTyp;
1231 END IF; -- ETC Rate column logic
1232
1233 -- Only rates and totals relevant to the version type should be populated.
1234 -- Cost-only versions should not have revenue rates or totals.
1235 IF p_fp_cols_rec.x_version_type = 'COST' THEN
1236 l_bill_rate_override_tab := l_null_NumTabTyp;
1237 l_avg_bill_rate_tab := l_null_NumTabTyp;
1238 l_etc_bill_rate_tab := l_null_NumTabTyp;
1239 l_txn_revenue_tab := l_null_NumTabTyp;
1240 l_txn_init_revenue_tab := l_null_NumTabTyp;
1241 l_pc_revenue_tab := l_null_NumTabTyp;
1242 l_pc_init_revenue_tab := l_null_NumTabTyp;
1243 l_pfc_revenue_tab := l_null_NumTabTyp;
1244 l_pfc_init_revenue_tab := l_null_NumTabTyp;
1245 -- Revenue-only versions should not have cost rates or totals.
1246 ELSIF p_fp_cols_rec.x_version_type = 'REVENUE' THEN
1247 l_raw_cost_rate_override_tab := l_null_NumTabTyp;
1248 l_brdn_cost_rate_override_tab := l_null_NumTabTyp;
1249 l_avg_raw_cost_rate_tab := l_null_NumTabTyp;
1250 l_avg_burden_cost_rate_tab := l_null_NumTabTyp;
1251 l_etc_raw_cost_rate_tab := l_null_NumTabTyp;
1252 l_etc_burden_cost_rate_tab := l_null_NumTabTyp;
1253 l_txn_raw_cost_tab := l_null_NumTabTyp;
1254 l_txn_burdened_cost_tab := l_null_NumTabTyp;
1255 l_txn_init_raw_cost_tab := l_null_NumTabTyp;
1256 l_txn_init_burdened_cost_tab := l_null_NumTabTyp;
1257 l_pc_raw_cost_tab := l_null_NumTabTyp;
1258 l_pc_burdened_cost_tab := l_null_NumTabTyp;
1259 l_pc_init_raw_cost_tab := l_null_NumTabTyp;
1260 l_pc_init_burdened_cost_tab := l_null_NumTabTyp;
1261 l_pfc_raw_cost_tab := l_null_NumTabTyp;
1262 l_pfc_burdened_cost_tab := l_null_NumTabTyp;
1263 l_pfc_init_raw_cost_tab := l_null_NumTabTyp;
1264 l_pfc_init_burdened_cost_tab := l_null_NumTabTyp;
1265 END IF;
1266 ELSIF p_copy_mode = G_COPY_OVERRIDES THEN
1267 -- Only rates relevant to the version type should be populated.
1268 -- Cost-only versions should not have revenue rates.
1269 IF p_fp_cols_rec.x_version_type = 'COST' THEN
1270 l_bill_rate_override_tab := l_null_NumTabTyp;
1271 -- Revenue-only versions should not have cost rates.
1272 ELSIF p_fp_cols_rec.x_version_type = 'REVENUE' THEN
1273 l_raw_cost_rate_override_tab := l_null_NumTabTyp;
1274 l_brdn_cost_rate_override_tab := l_null_NumTabTyp;
1275 END IF;
1276 END IF; -- p_copy_mode check
1277
1278 -- Step 3: Delete target records from PA_RESOURCE_ASGN_CURR
1279 -- that are being copied over from source records.
1280
1281 -- Populate PA_RESOURCE_ASGN_CURR_TMP with the target records.
1282 -- Note that the DELETE_TABLE_RECORDS API does not require the
1283 -- DELETE_FLAG column be populated for internal p_calling_module
1284 -- values (though the flag is required for public p_calling_modules).
1285 -- See the is_public_calling_module() and is_private_calling_module()
1286 -- functions for details on private/public calling modules.
1287
1288 DELETE PA_RESOURCE_ASGN_CURR_TMP;
1289
1290 FORALL i IN 1..l_ra_id_tab.count
1291 INSERT INTO PA_RESOURCE_ASGN_CURR_TMP (
1292 RESOURCE_ASSIGNMENT_ID,
1293 TXN_CURRENCY_CODE )
1294 VALUES (
1295 l_ra_id_tab(i),
1296 l_txn_currency_code_tab(i) );
1297
1298 IF p_pa_debug_mode = 'Y' THEN
1299 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1300 ( P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.'
1301 || 'DELETE_TABLE_RECORDS',
1302 P_CALLED_MODE => p_called_mode,
1303 P_MODULE_NAME => l_module_name,
1304 P_LOG_LEVEL => l_log_level );
1305 END IF;
1306 PA_RES_ASG_CURRENCY_PUB.DELETE_TABLE_RECORDS
1307 ( P_FP_COLS_REC => p_fp_cols_rec,
1308 P_CALLING_MODULE => G_PVT_COPY,
1309 P_VERSION_LEVEL_FLAG => 'N',
1310 P_CALLED_MODE => p_called_mode,
1311 X_RETURN_STATUS => x_return_status,
1312 X_MSG_COUNT => x_msg_count,
1313 X_MSG_DATA => x_msg_data );
1314 IF p_pa_debug_mode = 'Y' THEN
1315 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1316 ( P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.'
1317 || 'DELETE_TABLE_RECORDS: ' || x_return_status,
1318 P_CALLED_MODE => p_called_mode,
1319 P_MODULE_NAME => l_module_name,
1320 P_LOG_LEVEL => l_log_level );
1321 END IF;
1322 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1323 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1324 END IF;
1325
1326 -- Step 4: Insert records into the PA_RESOURCE_ASGN_CURR table.
1327
1328 IF p_copy_mode = G_COPY_ALL THEN
1329 FORALL i IN 1..l_ra_id_tab.count
1330 INSERT INTO PA_RESOURCE_ASGN_CURR (
1331 RA_TXN_ID,
1332 BUDGET_VERSION_ID,
1333 RESOURCE_ASSIGNMENT_ID,
1334 TXN_CURRENCY_CODE,
1335 TOTAL_QUANTITY,
1336 TOTAL_INIT_QUANTITY,
1337 TXN_RAW_COST_RATE_OVERRIDE,
1338 TXN_BURDEN_COST_RATE_OVERRIDE,
1339 TXN_BILL_RATE_OVERRIDE,
1340 TXN_AVERAGE_RAW_COST_RATE,
1341 TXN_AVERAGE_BURDEN_COST_RATE,
1342 TXN_AVERAGE_BILL_RATE,
1343 TXN_ETC_RAW_COST_RATE,
1344 TXN_ETC_BURDEN_COST_RATE,
1345 TXN_ETC_BILL_RATE,
1346 TOTAL_TXN_RAW_COST,
1347 TOTAL_TXN_BURDENED_COST,
1348 TOTAL_TXN_REVENUE,
1349 TOTAL_TXN_INIT_RAW_COST,
1350 TOTAL_TXN_INIT_BURDENED_COST,
1351 TOTAL_TXN_INIT_REVENUE,
1352 TOTAL_PROJECT_RAW_COST,
1353 TOTAL_PROJECT_BURDENED_COST,
1354 TOTAL_PROJECT_REVENUE,
1355 TOTAL_PROJECT_INIT_RAW_COST,
1356 TOTAL_PROJECT_INIT_BD_COST,
1357 TOTAL_PROJECT_INIT_REVENUE,
1358 TOTAL_PROJFUNC_RAW_COST,
1359 TOTAL_PROJFUNC_BURDENED_COST,
1360 TOTAL_PROJFUNC_REVENUE,
1361 TOTAL_PROJFUNC_INIT_RAW_COST,
1362 TOTAL_PROJFUNC_INIT_BD_COST,
1363 TOTAL_PROJFUNC_INIT_REVENUE,
1364 TOTAL_DISPLAY_QUANTITY,
1365 CREATION_DATE,
1366 CREATED_BY,
1367 LAST_UPDATE_DATE,
1368 LAST_UPDATED_BY,
1369 LAST_UPDATE_LOGIN,
1370 RECORD_VERSION_NUMBER )
1371 VALUES (
1372 pa_resource_asgn_curr_s.nextval,
1373 p_fp_cols_rec.x_budget_version_id,
1374 l_ra_id_tab(i),
1375 l_txn_currency_code_tab(i),
1376 l_total_quantity_tab(i),
1377 l_total_init_quantity_tab(i),
1378 l_raw_cost_rate_override_tab(i),
1379 l_brdn_cost_rate_override_tab(i),
1380 l_bill_rate_override_tab(i),
1381 l_avg_raw_cost_rate_tab(i),
1382 l_avg_burden_cost_rate_tab(i),
1383 l_avg_bill_rate_tab(i),
1384 l_etc_raw_cost_rate_tab(i),
1385 l_etc_burden_cost_rate_tab(i),
1386 l_etc_bill_rate_tab(i),
1387 l_txn_raw_cost_tab(i),
1388 l_txn_burdened_cost_tab(i),
1389 l_txn_revenue_tab(i),
1390 l_txn_init_raw_cost_tab(i),
1391 l_txn_init_burdened_cost_tab(i),
1392 l_txn_init_revenue_tab(i),
1393 l_pc_raw_cost_tab(i),
1394 l_pc_burdened_cost_tab(i),
1395 l_pc_revenue_tab(i),
1396 l_pc_init_raw_cost_tab(i),
1397 l_pc_init_burdened_cost_tab(i),
1398 l_pc_init_revenue_tab(i),
1399 l_pfc_raw_cost_tab(i),
1400 l_pfc_burdened_cost_tab(i),
1401 l_pfc_revenue_tab(i),
1402 l_pfc_init_raw_cost_tab(i),
1403 l_pfc_init_burdened_cost_tab(i),
1404 l_pfc_init_revenue_tab(i),
1405 l_display_quantity_tab(i),
1406 l_sysdate,
1407 l_last_updated_by,
1408 l_sysdate,
1409 l_last_updated_by,
1410 l_last_update_login,
1411 l_record_version_number );
1412 ELSIF p_copy_mode = G_COPY_OVERRIDES THEN
1413 FORALL i IN 1..l_ra_id_tab.count
1414 INSERT INTO PA_RESOURCE_ASGN_CURR (
1415 RA_TXN_ID,
1416 BUDGET_VERSION_ID,
1417 RESOURCE_ASSIGNMENT_ID,
1418 TXN_CURRENCY_CODE,
1419 TXN_RAW_COST_RATE_OVERRIDE,
1420 TXN_BURDEN_COST_RATE_OVERRIDE,
1421 TXN_BILL_RATE_OVERRIDE,
1422 CREATION_DATE,
1423 CREATED_BY,
1424 LAST_UPDATE_DATE,
1425 LAST_UPDATED_BY,
1426 LAST_UPDATE_LOGIN,
1427 RECORD_VERSION_NUMBER )
1428 VALUES (
1429 pa_resource_asgn_curr_s.nextval,
1430 p_fp_cols_rec.x_budget_version_id,
1431 l_ra_id_tab(i),
1432 l_txn_currency_code_tab(i),
1433 l_raw_cost_rate_override_tab(i),
1434 l_brdn_cost_rate_override_tab(i),
1435 l_bill_rate_override_tab(i),
1436 l_sysdate,
1437 l_last_updated_by,
1438 l_sysdate,
1439 l_last_updated_by,
1440 l_last_update_login,
1441 l_record_version_number );
1442 END IF; -- p_copy_mode check
1443
1444 --dbms_output.put_line('Reached Copy Records');
1445
1446 IF P_PA_DEBUG_MODE = 'Y' THEN
1447 PA_DEBUG.RESET_CURR_FUNCTION;
1448 END IF;
1449 EXCEPTION
1450 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1451
1452 l_msg_count := FND_MSG_PUB.count_msg;
1453 IF l_msg_count = 1 THEN
1454 PA_INTERFACE_UTILS_PUB.get_messages
1455 ( p_encoded => FND_API.G_TRUE,
1456 p_msg_index => 1,
1457 p_msg_count => l_msg_count,
1458 p_msg_data => l_msg_data,
1459 p_data => l_data,
1460 p_msg_index_out => l_msg_index_out);
1461 x_msg_data := l_data;
1462 x_msg_count := l_msg_count;
1463 ELSE
1464 x_msg_count := l_msg_count;
1465 END IF;
1466
1467 -- Removed ROLLBACK statement.
1468
1469 x_return_status := FND_API.G_RET_STS_ERROR;
1470
1471 IF P_PA_DEBUG_MODE = 'Y' THEN
1472 pa_fp_gen_amount_utils.fp_debug
1473 ( p_msg => 'Invalid Arguments Passed',
1474 p_called_mode => p_called_mode,
1475 p_module_name => l_module_name,
1476 p_log_level => l_log_level );
1477 PA_DEBUG.RESET_CURR_FUNCTION;
1478 END IF;
1479 -- Removed RAISE statement.
1480
1481 WHEN OTHERS THEN
1485 x_msg_data := substr(sqlerrm,1,240);
1482 -- Removed ROLLBACK statement.
1483 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1484 x_msg_count := 1;
1486 -- dbms_output.put_line('error msg :'||x_msg_data);
1487 FND_MSG_PUB.add_exc_msg
1488 ( p_pkg_name => 'PA_RES_ASG_CURRENCY_PUB',
1489 p_procedure_name => 'COPY_TABLE_RECORDS',
1490 p_error_text => substr(sqlerrm,1,240));
1491 IF P_PA_DEBUG_MODE = 'Y' THEN
1492 pa_fp_gen_amount_utils.fp_debug
1493 ( p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1494 p_called_mode => p_called_mode,
1495 p_module_name => l_module_name,
1496 p_log_level => l_log_level);
1497 PA_DEBUG.RESET_CURR_FUNCTION;
1498 END IF;
1499 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1500
1501 END COPY_TABLE_RECORDS;
1502
1503
1504 PROCEDURE INSERT_TABLE_RECORDS
1505 ( P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
1506 P_CALLING_MODULE IN VARCHAR2,
1507 P_VERSION_LEVEL_FLAG IN VARCHAR2,
1508 P_CALLED_MODE IN VARCHAR2,
1509 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1510 X_MSG_COUNT OUT NOCOPY NUMBER,
1511 X_MSG_DATA OUT NOCOPY VARCHAR2)
1512 IS
1513 l_module_name VARCHAR2(100) := 'pa.plsql.PA_RES_ASG_CURRENCY_PUB.INSERT_TABLE_RECORDS';
1514 l_log_level NUMBER := 5;
1515
1516 l_msg_count NUMBER;
1517 l_data VARCHAR2(2000);
1518 l_msg_data VARCHAR2(2000);
1519 l_msg_index_out NUMBER;
1520
1521 l_last_updated_by NUMBER := FND_GLOBAL.user_id;
1522 l_last_update_login NUMBER := FND_GLOBAL.login_id;
1523 l_sysdate DATE := SYSDATE;
1524 l_record_version_number NUMBER := 1;
1525
1526 l_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1527 l_txn_currency_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
1528 l_rc_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
1529 l_bc_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
1530 l_bill_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
1531
1532 BEGIN
1533
1534 IF p_pa_debug_mode = 'Y' THEN
1535 PA_DEBUG.SET_CURR_FUNCTION
1536 ( p_function => 'INSERT_TABLE_RECORDS',
1537 p_debug_mode => p_pa_debug_mode );
1538 END IF;
1539
1540 PA_RES_ASG_CURRENCY_PUB.PRINT_INPUT_PARAMS
1541 ( P_CALLING_API => G_PVT_INSERT,
1542 P_FP_COLS_REC => p_fp_cols_rec,
1543 P_CALLING_MODULE => p_calling_module,
1544 P_VERSION_LEVEL_FLAG => p_version_level_flag,
1545 P_CALLED_MODE => p_called_mode );
1546
1547 x_return_status := FND_API.G_RET_STS_SUCCESS;
1548 x_msg_count := 0;
1549
1550
1551 IF p_version_level_flag = 'Y' THEN
1552
1553 -- VERSION LEVEL Mode:
1554 -- Insert records into PA_RESOURCE_ASGN_CURR for all planning
1555 -- resource + currency combinations for the given version that do
1556 -- not already exist in the table.
1557
1558 SELECT DISTINCT
1559 bl.resource_assignment_id,
1560 bl.txn_currency_code
1561 BULK COLLECT
1562 INTO l_ra_id_tab,
1563 l_txn_currency_code_tab
1564 FROM pa_budget_lines bl,
1565 pa_resource_assignments ra
1566 WHERE ra.budget_version_id = p_fp_cols_rec.x_budget_version_id
1567 AND bl.resource_assignment_id = ra.resource_assignment_id
1568 AND NOT EXISTS (SELECT null
1569 FROM pa_resource_asgn_curr rbc
1570 WHERE rbc.resource_assignment_id = bl.resource_assignment_id
1571 AND rbc.txn_currency_code = bl.txn_currency_code);
1572
1573 -- Insert records with values for the following columns:
1574 -- (ra_txn_id, budget_version_id, resource_assignment_id, txn_currency_code).
1575 -- All of the remaining columns, including the rate overrides,
1576 -- but excluding the who columns, will be Null in this case.
1577
1578 FORALL i IN 1..l_ra_id_tab.count
1579 INSERT INTO pa_resource_asgn_curr (
1580 RA_TXN_ID,
1581 BUDGET_VERSION_ID,
1582 RESOURCE_ASSIGNMENT_ID,
1583 TXN_CURRENCY_CODE,
1584 CREATION_DATE,
1585 CREATED_BY,
1586 LAST_UPDATE_DATE,
1587 LAST_UPDATED_BY,
1588 LAST_UPDATE_LOGIN,
1589 RECORD_VERSION_NUMBER )
1590 VALUES (
1591 pa_resource_asgn_curr_s.nextval,
1592 p_fp_cols_rec.x_budget_version_id,
1593 l_ra_id_tab(i),
1594 l_txn_currency_code_tab(i),
1595 l_sysdate,
1596 l_last_updated_by,
1597 l_sysdate,
1598 l_last_updated_by,
1599 l_last_update_login,
1600 l_record_version_number );
1601
1602 ELSIF p_version_level_flag = 'N' THEN
1603
1604 -- TEMP TABLE Mode:
1605 -- Delete and then Insert the records specified by the temp table
1606 -- into the PA_RESOURCE_ASGN_CURR table.
1607
1608 IF p_pa_debug_mode = 'Y' THEN
1609 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1610 ( P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.'
1611 || 'DELETE_TABLE_RECORDS',
1612 P_CALLED_MODE => p_called_mode,
1616 PA_RES_ASG_CURRENCY_PUB.DELETE_TABLE_RECORDS
1613 P_MODULE_NAME => l_module_name,
1614 P_LOG_LEVEL => l_log_level );
1615 END IF;
1617 ( P_FP_COLS_REC => p_fp_cols_rec,
1618 P_CALLING_MODULE => G_PVT_INSERT,
1619 P_VERSION_LEVEL_FLAG => p_version_level_flag,
1620 P_CALLED_MODE => p_called_mode,
1621 X_RETURN_STATUS => x_return_status,
1622 X_MSG_COUNT => x_msg_count,
1623 X_MSG_DATA => x_msg_data );
1624 IF p_pa_debug_mode = 'Y' THEN
1625 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1626 ( P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.'
1627 || 'DELETE_TABLE_RECORDS: ' || x_return_status,
1628 P_CALLED_MODE => p_called_mode,
1629 P_MODULE_NAME => l_module_name,
1630 P_LOG_LEVEL => l_log_level );
1631 END IF;
1632 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1633 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1634 END IF;
1635
1636 INSERT INTO pa_resource_asgn_curr
1637 ( RA_TXN_ID,
1638 BUDGET_VERSION_ID,
1639 RESOURCE_ASSIGNMENT_ID,
1640 TXN_CURRENCY_CODE,
1641 TXN_RAW_COST_RATE_OVERRIDE,
1642 TXN_BURDEN_COST_RATE_OVERRIDE,
1643 TXN_BILL_RATE_OVERRIDE,
1644 CREATION_DATE,
1645 CREATED_BY,
1646 LAST_UPDATE_DATE,
1647 LAST_UPDATED_BY,
1648 LAST_UPDATE_LOGIN,
1649 RECORD_VERSION_NUMBER,
1650 expenditure_type )--for EnC
1651 SELECT pa_resource_asgn_curr_s.nextval,
1652 p_fp_cols_rec.x_budget_version_id,
1653 tmp.resource_assignment_id,
1654 tmp.txn_currency_code,
1655 tmp.txn_raw_cost_rate_override,
1656 tmp.txn_burden_cost_rate_override,
1657 tmp.txn_bill_rate_override,
1658 l_sysdate,
1659 l_last_updated_by,
1660 l_sysdate,
1661 l_last_updated_by,
1662 l_last_update_login,
1663 l_record_version_number,
1664 expenditure_type --for EnC
1665 FROM pa_resource_asgn_curr_tmp tmp;
1666
1667 END IF; -- p_version_level_flag check
1668
1669 --dbms_output.put_line('Reached Insert Records');
1670
1671 IF P_PA_DEBUG_MODE = 'Y' THEN
1672 PA_DEBUG.RESET_CURR_FUNCTION;
1673 END IF;
1674 EXCEPTION
1675 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1676
1677 l_msg_count := FND_MSG_PUB.count_msg;
1678 IF l_msg_count = 1 THEN
1679 PA_INTERFACE_UTILS_PUB.get_messages
1680 ( p_encoded => FND_API.G_TRUE,
1681 p_msg_index => 1,
1682 p_msg_count => l_msg_count,
1683 p_msg_data => l_msg_data,
1684 p_data => l_data,
1685 p_msg_index_out => l_msg_index_out);
1686 x_msg_data := l_data;
1687 x_msg_count := l_msg_count;
1688 ELSE
1689 x_msg_count := l_msg_count;
1690 END IF;
1691
1692 -- Removed ROLLBACK statement.
1693
1694 x_return_status := FND_API.G_RET_STS_ERROR;
1695
1696 IF P_PA_DEBUG_MODE = 'Y' THEN
1697 pa_fp_gen_amount_utils.fp_debug
1698 ( p_msg => 'Invalid Arguments Passed',
1699 p_called_mode => p_called_mode,
1700 p_module_name => l_module_name,
1701 p_log_level => l_log_level );
1702 PA_DEBUG.RESET_CURR_FUNCTION;
1703 END IF;
1704 -- Removed RAISE statement.
1705
1706 WHEN OTHERS THEN
1707 -- Removed ROLLBACK statement.
1708 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1709 x_msg_count := 1;
1710 x_msg_data := substr(sqlerrm,1,240);
1711 -- dbms_output.put_line('error msg :'||x_msg_data);
1712 FND_MSG_PUB.add_exc_msg
1713 ( p_pkg_name => 'PA_RES_ASG_CURRENCY_PUB',
1714 p_procedure_name => 'INSERT_TABLE_RECORDS',
1715 p_error_text => substr(sqlerrm,1,240));
1716 IF P_PA_DEBUG_MODE = 'Y' THEN
1717 pa_fp_gen_amount_utils.fp_debug
1718 ( p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1719 p_called_mode => p_called_mode,
1720 p_module_name => l_module_name,
1721 p_log_level => l_log_level);
1722 PA_DEBUG.RESET_CURR_FUNCTION;
1723 END IF;
1724 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1725
1726 END INSERT_TABLE_RECORDS;
1727
1728
1729 PROCEDURE ROLLUP_AMOUNTS
1730 ( P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
1731 P_CALLING_MODULE IN VARCHAR2,
1732 P_VERSION_LEVEL_FLAG IN VARCHAR2,
1733 P_CALLED_MODE IN VARCHAR2,
1734 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1735 X_MSG_COUNT OUT NOCOPY NUMBER,
1736 X_MSG_DATA OUT NOCOPY VARCHAR2)
1737 IS
1738 l_module_name VARCHAR2(100) := 'pa.plsql.PA_RES_ASG_CURRENCY_PUB.ROLLUP_AMOUNTS';
1742 l_data VARCHAR2(2000);
1739 l_log_level NUMBER := 5;
1740
1741 l_msg_count NUMBER;
1743 l_msg_data VARCHAR2(2000);
1744 l_msg_index_out NUMBER;
1745
1746 -- This cursor computes ETC rates, average rates, and amount totals
1747 -- for the entire version specified by c_budget_version_id.
1748 -- This cursor should be used when p_version_level_flag is 'Y'.
1749 -- The cursor consists of two halves, connected by UNION ALL.
1750 -- The first half gets data for resources that have budget lines,
1751 -- but may or may not have records in PA_RESOURCE_ASGN_CURR.
1752 -- The second half gets data for resources that have records in
1753 -- PA_RESOURCE_ASGN_CURR but no budget lines.
1754
1755 CURSOR version_level_rollup_csr
1756 ( c_project_id PA_RESOURCE_ASSIGNMENTS.PROJECT_ID%TYPE,
1757 c_budget_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE ) IS
1758 SELECT bl.resource_assignment_id, --RESOURCE_ASSIGNMENT_ID
1759 bl.txn_currency_code, --TXN_CURRENCY_CODE
1760 case when sum(nvl(bl.quantity,0)) = 0 and sum(nvl(bl.init_quantity,0)) = 0 then null else sum(nvl(bl.quantity,0)) end, --TOTAL_QUANTITY
1761 decode(sum(nvl(bl.init_quantity,0)),
1762 0,null,sum(nvl(bl.init_quantity,0))), --TOTAL_INIT_QUANTITY
1763 rbc.txn_raw_cost_rate_override, --TXN_RAW_COST_RATE_OVERRIDE
1764 rbc.txn_burden_cost_rate_override, --TXN_BURDEN_COST_RATE_OVERRIDE
1765 rbc.txn_bill_rate_override, --TXN_BILL_RATE_OVERRIDE
1766 /* bug fix 5523038 : modified Avg/ETC rate calculation logic to check
1767 * for rejection codes instead of relying on override/standard rates */
1768 ( sum(decode(cost_rejection_code
1769 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1770 * nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)))
1771 + nvl(bl.txn_init_raw_cost,0)
1772 ,null))
1773 / DECODE(sum(decode(cost_rejection_code,null,nvl(bl.quantity,0),null))
1774 ,0,NULL
1775 ,sum(decode(cost_rejection_code,null,nvl(bl.quantity,0),null)))
1776 ) avg_cost_rate, --TXN_AVERAGE_RAW_COST_RATE
1777 ( sum(decode(burden_rejection_code
1778 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1779 * nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)))
1780 + nvl(bl.txn_init_burdened_cost,0)
1781 ,null))
1782 / DECODE(sum(decode(burden_rejection_code,null,nvl(bl.quantity,0),null))
1783 ,0,NULL
1784 ,sum(decode(burden_rejection_code,null,nvl(bl.quantity,0),null)))
1785 ) avg_burden_rate, --TXN_AVERAGE_BURDEN_COST_RATE
1786 ( sum(decode(revenue_rejection_code
1787 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1788 * nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)))
1789 + nvl(bl.txn_init_revenue,0)
1790 ,null))
1791 / DECODE(sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0),null))
1792 ,0,NULL
1793 ,sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0),null)))
1794 ) avg_bill_rate, --TXN_AVERAGE_BILL_RATE
1795 ( sum(decode(cost_rejection_code
1796 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1797 * nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)))
1798 ,null))
1799 / DECODE(sum(decode(cost_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null))
1800 ,0,NULL
1801 ,sum(decode(cost_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null)))
1802 ) etc_cost_rate, --TXN_ETC_RAW_COST_RATE
1803 ( sum(decode(burden_rejection_code
1804 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1805 * nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)))
1806 ,null))
1807 / DECODE(sum(decode(burden_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null))
1808 ,0,NULL
1809 ,sum(decode(burden_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null)))
1810 ) etc_burden_rate, --TXN_ETC_BURDEN_COST_RATE
1811 ( sum(decode(revenue_rejection_code
1812 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1813 * nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)))
1814 ,null))
1815 / DECODE(sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null))
1816 ,0,NULL
1817 ,sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null)))
1818 ) etc_bill_rate, --TXN_ETC_BILL_RATE
1819 /* end bug fix 5523038 */
1820 case when sum(nvl(bl.txn_raw_cost,0)) = 0 and sum(nvl(bl.txn_init_raw_cost,0)) = 0 then null else sum(nvl(bl.txn_raw_cost,0)) end, --TOTAL_TXN_RAW_COST
1821 case when sum(nvl(bl.txn_burdened_cost,0)) = 0 and sum(nvl(bl.txn_init_burdened_cost,0)) = 0 then null else sum(nvl(bl.txn_burdened_cost,0)) end, --TOTAL_TXN_BURDENED_COST
1822 /* decode(sum(nvl(bl.txn_revenue,0)),
1823 0,null,sum(nvl(bl.txn_revenue,0))) */
1827 decode(sum(nvl(bl.txn_init_burdened_cost,0)),
1824 case when sum(nvl(bl.txn_revenue,0)) = 0 and sum(nvl(bl.txn_init_revenue,0)) = 0 then null else sum(nvl(bl.txn_revenue,0)) end, --TOTAL_TXN_REVENUE
1825 decode(sum(nvl(bl.txn_init_raw_cost,0)),
1826 0,null,sum(nvl(bl.txn_init_raw_cost,0))), --TOTAL_TXN_INIT_RAW_COST
1828 0,null,sum(nvl(bl.txn_init_burdened_cost,0))), --TOTAL_TXN_INIT_BURDENED_COST
1829 decode(sum(nvl(bl.txn_init_revenue,0)),
1830 0,null,sum(nvl(bl.txn_init_revenue,0))), --TOTAL_TXN_INIT_REVENUE
1831 decode(sum(nvl(bl.project_raw_cost,0)),
1832 0,null,sum(nvl(bl.project_raw_cost,0))), --TOTAL_PROJECT_RAW_COST
1833 decode(sum(nvl(bl.project_burdened_cost,0)),
1834 0,null,sum(nvl(bl.project_burdened_cost,0))), --TOTAL_PROJECT_BURDENED_COST
1835 decode(sum(nvl(bl.project_revenue,0)),
1836 0,null,sum(nvl(bl.project_revenue,0))), --TOTAL_PROJECT_REVENUE
1837 decode(sum(nvl(bl.project_init_raw_cost,0)),
1838 0,null,sum(nvl(bl.project_init_raw_cost,0))), --TOTAL_PROJECT_INIT_RAW_COST
1839 decode(sum(nvl(bl.project_init_burdened_cost,0)),
1840 0,null,sum(nvl(bl.project_init_burdened_cost,0))), --TOTAL_PROJECT_INIT_BD_COST
1841 decode(sum(nvl(bl.project_init_revenue,0)),
1842 0,null,sum(nvl(bl.project_init_revenue,0))), --TOTAL_PROJECT_INIT_REVENUE
1843 decode(sum(nvl(bl.raw_cost,0)),
1844 0,null,sum(nvl(bl.raw_cost,0))), --TOTAL_PROJFUNC_RAW_COST
1845 decode(sum(nvl(bl.burdened_cost,0)),
1846 0,null,sum(nvl(bl.burdened_cost,0))), --TOTAL_PROJFUNC_BURDENED_COST
1847 decode(sum(nvl(bl.revenue,0)),
1848 0,null,sum(nvl(bl.revenue,0))), --TOTAL_PROJFUNC_REVENUE
1849 decode(sum(nvl(bl.init_raw_cost,0)),
1850 0,null,sum(nvl(bl.init_raw_cost,0))), --TOTAL_PROJFUNC_INIT_RAW_COST
1851 decode(sum(nvl(bl.init_burdened_cost,0)),
1852 0,null,sum(nvl(bl.init_burdened_cost,0))), --TOTAL_PROJFUNC_INIT_BD_COST
1853 decode(sum(nvl(bl.init_revenue,0)),
1854 0,null,sum(nvl(bl.init_revenue,0))), --TOTAL_PROJFUNC_INIT_REVENUE
1855 decode(sum(nvl(bl.display_quantity,0)),
1856 0,null,sum(nvl(bl.display_quantity,0))), --TOTAL_DISPLAY_QUANTITY
1857 rbc.expenditure_type --Expenditure type for Enc
1858 FROM pa_resource_assignments ra,
1859 pa_budget_lines bl,
1860 pa_resource_asgn_curr rbc
1861 WHERE bl.resource_assignment_id = rbc.resource_assignment_id (+)
1862 AND bl.txn_currency_code = rbc.txn_currency_code (+)
1863 AND ra.budget_version_id = c_budget_version_id
1864 AND ra.project_id = c_project_id
1865 AND bl.resource_assignment_id = ra.resource_assignment_id
1866 GROUP BY bl.resource_assignment_id,
1867 bl.txn_currency_code,
1868 rbc.txn_raw_cost_rate_override,
1869 rbc.txn_burden_cost_rate_override,
1870 rbc.txn_bill_rate_override,
1871 rbc.expenditure_type --cklee 6/16/2009
1872 UNION ALL
1873 SELECT rbc.resource_assignment_id, --RESOURCE_ASSIGNMENT_ID
1874 rbc.txn_currency_code, --TXN_CURRENCY_CODE
1875 null, --TOTAL_QUANTITY
1876 null, --TOTAL_INIT_QUANTITY
1877 rbc.txn_raw_cost_rate_override, --TXN_RAW_COST_RATE_OVERRIDE
1878 rbc.txn_burden_cost_rate_override, --TXN_BURDEN_COST_RATE_OVERRIDE
1879 rbc.txn_bill_rate_override, --TXN_BILL_RATE_OVERRIDE
1880 null, --TXN_AVERAGE_RAW_COST_RATE
1881 null, --TXN_AVERAGE_BURDEN_COST_RATE
1882 null, --TXN_AVERAGE_BILL_RATE
1883 null, --TXN_ETC_RAW_COST_RATE
1884 null, --TXN_ETC_BURDEN_COST_RATE
1885 null, --TXN_ETC_BILL_RATE
1886 null, --TOTAL_TXN_RAW_COST
1887 null, --TOTAL_TXN_BURDENED_COST
1888 null, --TOTAL_TXN_REVENUE
1889 null, --TOTAL_TXN_INIT_RAW_COST
1890 null, --TOTAL_TXN_INIT_BURDENED_COST
1891 null, --TOTAL_TXN_INIT_REVENUE
1892 null, --TOTAL_PROJECT_RAW_COST
1893 null, --TOTAL_PROJECT_BURDENED_COST
1894 null, --TOTAL_PROJECT_REVENUE
1895 null, --TOTAL_PROJECT_INIT_RAW_COST
1896 null, --TOTAL_PROJECT_INIT_BD_COST
1897 null, --TOTAL_PROJECT_INIT_REVENUE
1898 null, --TOTAL_PROJFUNC_RAW_COST
1899 null, --TOTAL_PROJFUNC_BURDENED_COST
1900 null, --TOTAL_PROJFUNC_REVENUE
1901 null, --TOTAL_PROJFUNC_INIT_RAW_COST
1902 null, --TOTAL_PROJFUNC_INIT_BD_COST
1903 null, --TOTAL_PROJFUNC_INIT_REVENUE
1904 null, --TOTAL_DISPLAY_QUANTITY
1905 rbc.expenditure_type --Expenditure type for Enc
1906 FROM pa_resource_asgn_curr rbc
1907 WHERE rbc.budget_version_id = c_budget_version_id
1911 AND bl.txn_currency_code = rbc.txn_currency_code );
1908 AND NOT EXISTS (SELECT null
1909 FROM pa_budget_lines bl
1910 WHERE bl.resource_assignment_id = rbc.resource_assignment_id
1912
1913
1914 -- This cursor computes ETC rates, average rates, and amount totals
1915 -- for the resources specified in PA_RESOURCE_ASGN_CURR_TMP.
1916 -- This cursor should be used when p_version_level_flag is 'N'.
1917 -- The cursor gets data for resources that have records in
1918 -- PA_RESOURCE_ASGN_CURR but may or may not have budget lines.
1919 -- NOTE: the cursor parameters c_project_id and c_budget_version_id
1920 -- are not necessary here, but included in case of future use.
1921
1922 CURSOR table_level_rollup_csr
1923 ( c_project_id PA_RESOURCE_ASSIGNMENTS.PROJECT_ID%TYPE,
1924 c_budget_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE ) IS
1925 SELECT /*+ leading(RBC) cardinality(RBC,1) */ -- added hint for bug 14040849
1926 rbc.resource_assignment_id, --RESOURCE_ASSIGNMENT_ID
1927 rbc.txn_currency_code, --TXN_CURRENCY_CODE
1928 case when sum(nvl(bl.quantity,0)) = 0 and sum(nvl(bl.init_quantity,0)) = 0 then null else sum(nvl(bl.quantity,0)) end, --TOTAL_QUANTITY
1929 decode(sum(nvl(bl.init_quantity,0)),
1930 0,null,sum(nvl(bl.init_quantity,0))), --TOTAL_INIT_QUANTITY
1931 rbc.txn_raw_cost_rate_override, --TXN_RAW_COST_RATE_OVERRIDE
1932 rbc.txn_burden_cost_rate_override, --TXN_BURDEN_COST_RATE_OVERRIDE
1933 rbc.txn_bill_rate_override, --TXN_BILL_RATE_OVERRIDE
1934 /* bug fix 5523038 : modified Avg/ETC rate calculation logic to check
1935 * for rejection codes instead of relying on override/standard rates */
1936 ( sum(decode(cost_rejection_code
1937 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1938 * nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)))
1939 + nvl(bl.txn_init_raw_cost,0)
1940 ,null))
1941 / DECODE(sum(decode(cost_rejection_code,null,nvl(bl.quantity,0),null))
1942 ,0,NULL
1943 ,sum(decode(cost_rejection_code,null,nvl(bl.quantity,0),null)))
1944 ) avg_cost_rate, --TXN_AVERAGE_RAW_COST_RATE
1945 ( sum(decode(burden_rejection_code
1946 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1947 * nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)))
1948 + nvl(bl.txn_init_burdened_cost,0)
1949 ,null))
1950 / DECODE(sum(decode(burden_rejection_code,null,nvl(bl.quantity,0),null))
1951 ,0,NULL
1952 ,sum(decode(burden_rejection_code,null,nvl(bl.quantity,0),null)))
1953 ) avg_burden_rate, --TXN_AVERAGE_BURDEN_COST_RATE
1954 ( sum(decode(revenue_rejection_code
1955 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1956 * nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)))
1957 + nvl(bl.txn_init_revenue,0)
1958 ,null))
1959 / DECODE(sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0),null))
1960 ,0,NULL
1961 ,sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0),null)))
1962 ) avg_bill_rate, --TXN_AVERAGE_BILL_RATE
1963
1964 ( sum(decode(cost_rejection_code
1965 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1966 * nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)))
1967 ,null))
1968 / DECODE(sum(decode(cost_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null))
1969 ,0,NULL
1970 ,sum(decode(cost_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null)))
1971 ) etc_cost_rate, --TXN_ETC_RAW_COST_RATE
1972 ( sum(decode(burden_rejection_code
1973 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1974 * nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)))
1975 ,null))
1976 / DECODE(sum(decode(burden_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null))
1977 ,0,NULL
1978 ,sum(decode(burden_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null)))
1979 ) etc_burden_rate, --TXN_ETC_BURDEN_COST_RATE
1980 ( sum(decode(revenue_rejection_code
1981 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1982 * nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)))
1983 ,null))
1984 / DECODE(sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null))
1985 ,0,NULL
1986 ,sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null)))
1987 ) etc_bill_rate, --TXN_ETC_BILL_RATE
1988 /* end bug fix 5523038 */
1989 case when sum(nvl(bl.txn_raw_cost,0)) = 0 and sum(nvl(bl.txn_init_raw_cost,0)) = 0 then null else sum(nvl(bl.txn_raw_cost,0)) end, --TOTAL_TXN_RAW_COST
1990 case when sum(nvl(bl.txn_burdened_cost,0)) = 0 and sum(nvl(bl.txn_init_burdened_cost,0)) = 0 then null else sum(nvl(bl.txn_burdened_cost,0)) end, --TOTAL_TXN_BURDENED_COST
1991 case when sum(nvl(bl.txn_revenue,0)) = 0 and sum(nvl(bl.txn_init_revenue,0)) = 0 then null else sum(nvl(bl.txn_revenue,0)) end, --TOTAL_TXN_REVENUE
1995 0,null,sum(nvl(bl.txn_init_burdened_cost,0))), --TOTAL_TXN_INIT_BURDENED_COST
1992 decode(sum(nvl(bl.txn_init_raw_cost,0)),
1993 0,null,sum(nvl(bl.txn_init_raw_cost,0))), --TOTAL_TXN_INIT_RAW_COST
1994 decode(sum(nvl(bl.txn_init_burdened_cost,0)),
1996 decode(sum(nvl(bl.txn_init_revenue,0)),
1997 0,null,sum(nvl(bl.txn_init_revenue,0))), --TOTAL_TXN_INIT_REVENUE
1998 decode(sum(nvl(bl.project_raw_cost,0)),
1999 0,null,sum(nvl(bl.project_raw_cost,0))), --TOTAL_PROJECT_RAW_COST
2000 decode(sum(nvl(bl.project_burdened_cost,0)),
2001 0,null,sum(nvl(bl.project_burdened_cost,0))), --TOTAL_PROJECT_BURDENED_COST
2002 decode(sum(nvl(bl.project_revenue,0)),
2003 0,null,sum(nvl(bl.project_revenue,0))), --TOTAL_PROJECT_REVENUE
2004 decode(sum(nvl(bl.project_init_raw_cost,0)),
2005 0,null,sum(nvl(bl.project_init_raw_cost,0))), --TOTAL_PROJECT_INIT_RAW_COST
2006 decode(sum(nvl(bl.project_init_burdened_cost,0)),
2007 0,null,sum(nvl(bl.project_init_burdened_cost,0))), --TOTAL_PROJECT_INIT_BD_COST
2008 decode(sum(nvl(bl.project_init_revenue,0)),
2009 0,null,sum(nvl(bl.project_init_revenue,0))), --TOTAL_PROJECT_INIT_REVENUE
2010 decode(sum(nvl(bl.raw_cost,0)),
2011 0,null,sum(nvl(bl.raw_cost,0))), --TOTAL_PROJFUNC_RAW_COST
2012 decode(sum(nvl(bl.burdened_cost,0)),
2013 0,null,sum(nvl(bl.burdened_cost,0))), --TOTAL_PROJFUNC_BURDENED_COST
2014 decode(sum(nvl(bl.revenue,0)),
2015 0,null,sum(nvl(bl.revenue,0))), --TOTAL_PROJFUNC_REVENUE
2016 decode(sum(nvl(bl.init_raw_cost,0)),
2017 0,null,sum(nvl(bl.init_raw_cost,0))), --TOTAL_PROJFUNC_INIT_RAW_COST
2018 decode(sum(nvl(bl.init_burdened_cost,0)),
2019 0,null,sum(nvl(bl.init_burdened_cost,0))), --TOTAL_PROJFUNC_INIT_BD_COST
2020 decode(sum(nvl(bl.init_revenue,0)),
2021 0,null,sum(nvl(bl.init_revenue,0))), --TOTAL_PROJFUNC_INIT_REVENUE
2022 decode(sum(nvl(bl.display_quantity,0)),
2023 0,null,sum(nvl(bl.display_quantity,0))), --TOTAL_DISPLAY_QUANTITY
2024 rbc.expenditure_type --Direct cost expenditure type for EnC
2025 FROM pa_budget_lines bl,
2026 pa_resource_asgn_curr_TMP rbc
2027 WHERE bl.resource_assignment_id (+) = rbc.resource_assignment_id
2028 AND bl.txn_currency_code (+) = rbc.txn_currency_code
2029 --Bug 6160759
2030 AND bl.budget_version_id(+)= p_fp_cols_rec.x_budget_version_id
2031 AND bl.budget_version_id(+)= p_fp_cols_rec.x_budget_version_id
2032 GROUP BY rbc.resource_assignment_id,
2033 rbc.txn_currency_code,
2034 rbc.txn_raw_cost_rate_override,
2035 rbc.txn_burden_cost_rate_override,
2036 rbc.txn_bill_rate_override,
2037 rbc.expenditure_type;
2038
2039 l_last_updated_by NUMBER := FND_GLOBAL.user_id;
2040 l_last_update_login NUMBER := FND_GLOBAL.login_id;
2041 l_sysdate DATE := SYSDATE;
2042 l_record_version_number NUMBER := 1;
2043
2044 -- PL/SQL tables for storing computed rates and rolled up amounts
2045 l_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2046 l_txn_currency_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
2047 l_direct_expenditure_type_tab PA_PLSQL_DATATYPES.Char30TabTyp; --For EnC
2048 l_total_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
2049 l_total_init_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
2050 l_raw_cost_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
2051 l_brdn_cost_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
2052 l_bill_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
2053 l_avg_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2054 l_avg_burden_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2055 l_avg_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2056 l_etc_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2057 l_etc_burden_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2058 l_etc_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2059 l_txn_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2060 l_txn_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2061 l_txn_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2062 l_txn_init_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2063 l_txn_init_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2064 l_txn_init_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2065 l_pc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2066 l_pc_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2067 l_pc_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2068 l_pc_init_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2069 l_pc_init_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2070 l_pc_init_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2071 l_pfc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2072 l_pfc_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2073 l_pfc_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2074 l_pfc_init_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2075 l_pfc_init_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2076 l_pfc_init_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2077 l_display_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
2078
2079 l_NULL_NumTabTyp PA_PLSQL_DATATYPES.NumTabTyp;
2080
2081 -- Indicates if the Target version is a Workplan.
2082 l_wp_version_flag PA_BUDGET_VERSIONS.WP_VERSION_FLAG%TYPE;
2083
2084 BEGIN
2085
2086 IF p_pa_debug_mode = 'Y' THEN
2087 PA_DEBUG.SET_CURR_FUNCTION
2088 ( p_function => 'ROLLUP_AMOUNTS',
2089 p_debug_mode => p_pa_debug_mode );
2090 END IF;
2091
2092 PA_RES_ASG_CURRENCY_PUB.PRINT_INPUT_PARAMS
2093 ( P_CALLING_API => G_PVT_ROLLUP,
2094 P_FP_COLS_REC => p_fp_cols_rec,
2095 P_CALLING_MODULE => p_calling_module,
2096 P_VERSION_LEVEL_FLAG => p_version_level_flag,
2097 P_CALLED_MODE => p_called_mode );
2098
2099 x_return_status := FND_API.G_RET_STS_SUCCESS;
2100 x_msg_count := 0;
2101
2102 BEGIN
2103 SELECT nvl(wp_version_flag,'N')
2104 INTO l_wp_version_flag
2105 FROM pa_budget_versions
2106 WHERE budget_version_id = p_fp_cols_rec.x_budget_version_id;
2107 EXCEPTION WHEN NO_DATA_FOUND THEN
2108 IF p_pa_debug_mode = 'Y' THEN
2109 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
2110 ( P_MSG => 'Invalid p_fp_cols_rec.x_budget_version_id value: '
2111 || p_fp_cols_rec.x_budget_version_id
2112 || '. Budget version does not exist.',
2113 P_CALLED_MODE => p_called_mode,
2114 P_MODULE_NAME => l_module_name,
2115 P_LOG_LEVEL => l_log_level );
2116 END IF;
2117 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2118 END;
2119
2120
2121 -- Step 1: Select rates and amounts from the appropriate cursor.
2122
2123 IF p_version_level_flag = 'Y' THEN
2124 OPEN version_level_rollup_csr
2125 (p_fp_cols_rec.x_project_id,
2126 p_fp_cols_rec.x_budget_version_id);
2127 FETCH version_level_rollup_csr
2128 BULK COLLECT
2129 INTO l_ra_id_tab,
2130 l_txn_currency_code_tab,
2131 l_total_quantity_tab,
2132 l_total_init_quantity_tab,
2133 l_raw_cost_rate_override_tab,
2134 l_brdn_cost_rate_override_tab,
2135 l_bill_rate_override_tab,
2136 l_avg_raw_cost_rate_tab,
2137 l_avg_burden_cost_rate_tab,
2138 l_avg_bill_rate_tab,
2139 l_etc_raw_cost_rate_tab,
2140 l_etc_burden_cost_rate_tab,
2141 l_etc_bill_rate_tab,
2142 l_txn_raw_cost_tab,
2143 l_txn_burdened_cost_tab,
2144 l_txn_revenue_tab,
2145 l_txn_init_raw_cost_tab,
2146 l_txn_init_burdened_cost_tab,
2147 l_txn_init_revenue_tab,
2148 l_pc_raw_cost_tab,
2149 l_pc_burdened_cost_tab,
2150 l_pc_revenue_tab,
2151 l_pc_init_raw_cost_tab,
2152 l_pc_init_burdened_cost_tab,
2153 l_pc_init_revenue_tab,
2154 l_pfc_raw_cost_tab,
2155 l_pfc_burdened_cost_tab,
2156 l_pfc_revenue_tab,
2157 l_pfc_init_raw_cost_tab,
2158 l_pfc_init_burdened_cost_tab,
2159 l_pfc_init_revenue_tab,
2160 l_display_quantity_tab,
2161 l_direct_expenditure_type_tab; -- added for Enc
2162 CLOSE version_level_rollup_csr;
2163 ELSIF p_version_level_flag = 'N' THEN
2164 OPEN table_level_rollup_csr
2165 (p_fp_cols_rec.x_project_id,
2166 p_fp_cols_rec.x_budget_version_id);
2167 FETCH table_level_rollup_csr
2168 BULK COLLECT
2169 INTO l_ra_id_tab,
2170 l_txn_currency_code_tab,
2171 l_total_quantity_tab,
2172 l_total_init_quantity_tab,
2173 l_raw_cost_rate_override_tab,
2174 l_brdn_cost_rate_override_tab,
2175 l_bill_rate_override_tab,
2176 l_avg_raw_cost_rate_tab,
2177 l_avg_burden_cost_rate_tab,
2178 l_avg_bill_rate_tab,
2179 l_etc_raw_cost_rate_tab,
2180 l_etc_burden_cost_rate_tab,
2181 l_etc_bill_rate_tab,
2182 l_txn_raw_cost_tab,
2183 l_txn_burdened_cost_tab,
2184 l_txn_revenue_tab,
2185 l_txn_init_raw_cost_tab,
2186 l_txn_init_burdened_cost_tab,
2187 l_txn_init_revenue_tab,
2188 l_pc_raw_cost_tab,
2189 l_pc_burdened_cost_tab,
2190 l_pc_revenue_tab,
2191 l_pc_init_raw_cost_tab,
2192 l_pc_init_burdened_cost_tab,
2193 l_pc_init_revenue_tab,
2194 l_pfc_raw_cost_tab,
2195 l_pfc_burdened_cost_tab,
2196 l_pfc_revenue_tab,
2197 l_pfc_init_raw_cost_tab,
2198 l_pfc_init_burdened_cost_tab,
2199 l_pfc_init_revenue_tab,
2200 l_display_quantity_tab,
2201 l_direct_expenditure_type_tab; --for EnC
2202 CLOSE table_level_rollup_csr;
2203 END IF; -- p_version_level_flag check
2204
2205 -- No further processing is required if there are no records to rollup.
2206 IF l_ra_id_tab.count <= 0 THEN
2207 IF P_PA_DEBUG_MODE = 'Y' THEN
2208 PA_DEBUG.RESET_CURR_FUNCTION;
2209 END IF;
2210 RETURN;
2211 END IF;
2212
2213
2214 -- Step 2: Process pl/sql tables as needed.
2215
2216 -- Initialize a pl/sql table of length l_ra_id_tab.count with nulls.
2217 -- We can use this table to null out entire tables during processing.
2218 -- This should perform better than nulling out records in a loop.
2219 l_null_NumTabTyp.delete;
2220 FOR i IN 1..l_ra_id_tab.count LOOP
2221 l_null_NumTabTyp(i) := null;
2222 END LOOP;
2223
2224 -- ETC Rate columns should Null for Budgets,
2225 -- but should be populated for Forecasts and Workplans.
2226 -- Additionally, Actuals columns should be nulled out.
2227 IF l_wp_version_flag = 'N' AND
2228 p_fp_cols_rec.x_plan_class_code = 'BUDGET' THEN
2229 l_etc_raw_cost_rate_tab := l_null_NumTabTyp;
2230 l_etc_burden_cost_rate_tab := l_null_NumTabTyp;
2231 l_etc_bill_rate_tab := l_null_NumTabTyp;
2232 l_total_init_quantity_tab := l_null_NumTabTyp;
2233 l_txn_init_raw_cost_tab := l_null_NumTabTyp;
2234 l_txn_init_burdened_cost_tab := l_null_NumTabTyp;
2235 l_txn_init_revenue_tab := l_null_NumTabTyp;
2236 l_pc_init_raw_cost_tab := l_null_NumTabTyp;
2237 l_pc_init_burdened_cost_tab := l_null_NumTabTyp;
2238 l_pc_init_revenue_tab := l_null_NumTabTyp;
2239 l_pfc_init_raw_cost_tab := l_null_NumTabTyp;
2240 l_pfc_init_burdened_cost_tab := l_null_NumTabTyp;
2241 l_pfc_init_revenue_tab := l_null_NumTabTyp;
2242 END IF; -- ETC Rate column logic
2243
2244 -- Only rates and totals relevant to the version type should be populated.
2245 -- Cost-only versions should not have revenue rates or totals.
2246 IF p_fp_cols_rec.x_version_type = 'COST' THEN
2247 l_bill_rate_override_tab := l_null_NumTabTyp;
2248 l_avg_bill_rate_tab := l_null_NumTabTyp;
2249 l_etc_bill_rate_tab := l_null_NumTabTyp;
2250 l_txn_revenue_tab := l_null_NumTabTyp;
2251 l_txn_init_revenue_tab := l_null_NumTabTyp;
2252 l_pc_revenue_tab := l_null_NumTabTyp;
2253 l_pc_init_revenue_tab := l_null_NumTabTyp;
2254 l_pfc_revenue_tab := l_null_NumTabTyp;
2255 l_pfc_init_revenue_tab := l_null_NumTabTyp;
2256 -- Revenue-only versions should not have cost rates or totals.
2257 ELSIF p_fp_cols_rec.x_version_type = 'REVENUE' THEN
2258 l_raw_cost_rate_override_tab := l_null_NumTabTyp;
2259 l_brdn_cost_rate_override_tab := l_null_NumTabTyp;
2260 l_avg_raw_cost_rate_tab := l_null_NumTabTyp;
2261 l_avg_burden_cost_rate_tab := l_null_NumTabTyp;
2262 l_etc_raw_cost_rate_tab := l_null_NumTabTyp;
2263 l_etc_burden_cost_rate_tab := l_null_NumTabTyp;
2264 l_txn_raw_cost_tab := l_null_NumTabTyp;
2265 l_txn_burdened_cost_tab := l_null_NumTabTyp;
2266 l_txn_init_raw_cost_tab := l_null_NumTabTyp;
2267 l_txn_init_burdened_cost_tab := l_null_NumTabTyp;
2268 l_pc_raw_cost_tab := l_null_NumTabTyp;
2269 l_pc_burdened_cost_tab := l_null_NumTabTyp;
2270 l_pc_init_raw_cost_tab := l_null_NumTabTyp;
2271 l_pc_init_burdened_cost_tab := l_null_NumTabTyp;
2272 l_pfc_raw_cost_tab := l_null_NumTabTyp;
2273 l_pfc_burdened_cost_tab := l_null_NumTabTyp;
2274 l_pfc_init_raw_cost_tab := l_null_NumTabTyp;
2275 l_pfc_init_burdened_cost_tab := l_null_NumTabTyp;
2276 END IF;
2277
2278 -- Step 3: Delete records from the PA_RESOURCE_ASGN_CURR table.
2279
2280 IF p_pa_debug_mode = 'Y' THEN
2281 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
2282 ( P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.'
2283 || 'DELETE_TABLE_RECORDS',
2284 P_CALLED_MODE => p_called_mode,
2285 P_MODULE_NAME => l_module_name,
2286 P_LOG_LEVEL => l_log_level );
2287 END IF;
2288 PA_RES_ASG_CURRENCY_PUB.DELETE_TABLE_RECORDS
2289 ( P_FP_COLS_REC => p_fp_cols_rec,
2290 P_CALLING_MODULE => G_PVT_ROLLUP,
2291 P_VERSION_LEVEL_FLAG => p_version_level_flag,
2292 P_CALLED_MODE => p_called_mode,
2293 X_RETURN_STATUS => x_return_status,
2294 X_MSG_COUNT => x_msg_count,
2295 X_MSG_DATA => x_msg_data );
2296 IF p_pa_debug_mode = 'Y' THEN
2297 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
2298 ( P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.'
2299 || 'DELETE_TABLE_RECORDS: ' || x_return_status,
2300 P_CALLED_MODE => p_called_mode,
2301 P_MODULE_NAME => l_module_name,
2302 P_LOG_LEVEL => l_log_level );
2303 END IF;
2304 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2308 -- Step 4: Insert records into the PA_RESOURCE_ASGN_CURR table.
2305 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2306 END IF;
2307
2309
2310 FORALL i IN 1..l_ra_id_tab.count
2311 INSERT INTO PA_RESOURCE_ASGN_CURR (
2312 RA_TXN_ID,
2313 BUDGET_VERSION_ID,
2314 RESOURCE_ASSIGNMENT_ID,
2315 TXN_CURRENCY_CODE,
2316 TOTAL_QUANTITY,
2317 TOTAL_INIT_QUANTITY,
2318 TXN_RAW_COST_RATE_OVERRIDE,
2319 TXN_BURDEN_COST_RATE_OVERRIDE,
2320 TXN_BILL_RATE_OVERRIDE,
2321 TXN_AVERAGE_RAW_COST_RATE,
2322 TXN_AVERAGE_BURDEN_COST_RATE,
2323 TXN_AVERAGE_BILL_RATE,
2324 TXN_ETC_RAW_COST_RATE,
2325 TXN_ETC_BURDEN_COST_RATE,
2326 TXN_ETC_BILL_RATE,
2327 TOTAL_TXN_RAW_COST,
2328 TOTAL_TXN_BURDENED_COST,
2329 TOTAL_TXN_REVENUE,
2330 TOTAL_TXN_INIT_RAW_COST,
2331 TOTAL_TXN_INIT_BURDENED_COST,
2332 TOTAL_TXN_INIT_REVENUE,
2333 TOTAL_PROJECT_RAW_COST,
2334 TOTAL_PROJECT_BURDENED_COST,
2335 TOTAL_PROJECT_REVENUE,
2336 TOTAL_PROJECT_INIT_RAW_COST,
2337 TOTAL_PROJECT_INIT_BD_COST,
2338 TOTAL_PROJECT_INIT_REVENUE,
2339 TOTAL_PROJFUNC_RAW_COST,
2340 TOTAL_PROJFUNC_BURDENED_COST,
2341 TOTAL_PROJFUNC_REVENUE,
2342 TOTAL_PROJFUNC_INIT_RAW_COST,
2343 TOTAL_PROJFUNC_INIT_BD_COST,
2344 TOTAL_PROJFUNC_INIT_REVENUE,
2345 TOTAL_DISPLAY_QUANTITY,
2346 CREATION_DATE,
2347 CREATED_BY,
2348 LAST_UPDATE_DATE,
2349 LAST_UPDATED_BY,
2350 LAST_UPDATE_LOGIN,
2351 RECORD_VERSION_NUMBER,
2352 expenditure_type )
2353 VALUES (
2354 pa_resource_asgn_curr_s.nextval,
2355 p_fp_cols_rec.x_budget_version_id,
2356 l_ra_id_tab(i),
2357 l_txn_currency_code_tab(i),
2358 l_total_quantity_tab(i),
2359 l_total_init_quantity_tab(i),
2360 l_raw_cost_rate_override_tab(i),
2361 l_brdn_cost_rate_override_tab(i),
2362 l_bill_rate_override_tab(i),
2363 l_avg_raw_cost_rate_tab(i),
2364 l_avg_burden_cost_rate_tab(i),
2365 l_avg_bill_rate_tab(i),
2366 l_etc_raw_cost_rate_tab(i),
2367 l_etc_burden_cost_rate_tab(i),
2368 l_etc_bill_rate_tab(i),
2369 l_txn_raw_cost_tab(i),
2370 l_txn_burdened_cost_tab(i),
2371 l_txn_revenue_tab(i),
2372 l_txn_init_raw_cost_tab(i),
2373 l_txn_init_burdened_cost_tab(i),
2374 l_txn_init_revenue_tab(i),
2375 l_pc_raw_cost_tab(i),
2376 l_pc_burdened_cost_tab(i),
2377 l_pc_revenue_tab(i),
2378 l_pc_init_raw_cost_tab(i),
2379 l_pc_init_burdened_cost_tab(i),
2380 l_pc_init_revenue_tab(i),
2381 l_pfc_raw_cost_tab(i),
2382 l_pfc_burdened_cost_tab(i),
2383 l_pfc_revenue_tab(i),
2384 l_pfc_init_raw_cost_tab(i),
2385 l_pfc_init_burdened_cost_tab(i),
2386 l_pfc_init_revenue_tab(i),
2387 l_display_quantity_tab(i),
2388 l_sysdate,
2389 l_last_updated_by,
2390 l_sysdate,
2391 l_last_updated_by,
2392 l_last_update_login,
2393 l_record_version_number,
2394 l_direct_expenditure_type_tab(i) --for EnC
2395 );
2396
2397 --bug#12865217
2398 FORALL j IN 1..l_ra_id_tab.count
2399 DELETE FROM pa_resource_asgn_curr prac
2400 WHERE EXISTS
2401 (SELECT 1
2402 FROM pa_resource_asgn_curr a
2403 where a.budget_version_id = p_fp_cols_rec.x_budget_version_id
2404 AND a.resource_assignment_id = l_ra_id_tab(j)
2405 AND prac.resource_assignment_id = a.resource_assignment_id
2406 AND ( a.total_quantity IS NOT NULL
2407 OR a.total_init_quantity IS NOT NULL
2408 OR a.txn_raw_cost_rate_override IS NOT NULL
2409 OR a.txn_burden_cost_rate_override IS NOT NULL
2410 OR a.txn_bill_rate_override IS NOT NULL
2411 OR a.txn_average_raw_cost_rate IS NOT NULL
2412 OR a.txn_average_burden_cost_rate IS NOT NULL
2413 OR a.txn_average_bill_rate IS NOT NULL
2414 OR a.txn_etc_raw_cost_rate IS NOT NULL
2415 OR a.txn_etc_burden_cost_rate IS NOT NULL
2416 OR a.txn_etc_bill_rate IS NOT NULL
2417 OR a.total_txn_raw_cost IS NOT NULL
2418 OR a.total_txn_burdened_cost IS NOT NULL
2419 OR a.total_txn_revenue IS NOT NULL
2420 OR a.total_txn_init_raw_cost IS NOT NULL
2421 OR a.total_txn_init_burdened_cost IS NOT NULL
2422 OR a.total_txn_init_revenue IS NOT NULL
2423 OR a.total_project_raw_cost IS NOT NULL
2424 OR a.total_project_burdened_cost IS NOT NULL
2425 OR a.total_project_revenue IS NOT NULL
2426 OR a.total_project_init_raw_cost IS NOT NULL
2427 OR a.total_project_init_bd_cost IS NOT NULL
2428 OR a.total_project_init_revenue IS NOT NULL
2429 OR a.total_projfunc_raw_cost IS NOT NULL
2430 OR a.total_projfunc_burdened_cost IS NOT NULL
2431 OR a.total_projfunc_revenue IS NOT NULL
2432 OR a.total_projfunc_init_raw_cost IS NOT NULL
2433 OR a.total_projfunc_init_bd_cost IS NOT NULL
2434 OR a.total_projfunc_init_revenue IS NOT NULL
2435 OR a.total_display_quantity IS NOT NULL ))
2436 AND prac.budget_version_id = p_fp_cols_rec.x_budget_version_id
2437 AND prac.resource_assignment_id = l_ra_id_tab(j)
2438 AND prac.total_quantity IS NULL
2439 AND prac.total_init_quantity IS NULL
2440 AND prac.txn_raw_cost_rate_override IS NULL
2441 AND prac.txn_burden_cost_rate_override IS NULL
2442 AND prac.txn_bill_rate_override IS NULL
2443 AND prac.txn_average_raw_cost_rate IS NULL
2444 AND prac.txn_average_burden_cost_rate IS NULL
2445 AND prac.txn_average_bill_rate IS NULL
2446 AND prac.txn_etc_raw_cost_rate IS NULL
2447 AND prac.txn_etc_burden_cost_rate IS NULL
2448 AND prac.txn_etc_bill_rate IS NULL
2449 AND prac.total_txn_raw_cost IS NULL
2450 AND prac.total_txn_burdened_cost IS NULL
2451 AND prac.total_txn_revenue IS NULL
2452 AND prac.total_txn_init_raw_cost IS NULL
2453 AND prac.total_txn_init_burdened_cost IS NULL
2454 AND prac.total_txn_init_revenue IS NULL
2455 AND prac.total_project_raw_cost IS NULL
2456 AND prac.total_project_burdened_cost IS NULL
2457 AND prac.total_project_revenue IS NULL
2458 AND prac.total_project_init_raw_cost IS NULL
2459 AND prac.total_project_init_bd_cost IS NULL
2460 AND prac.total_project_init_revenue IS NULL
2461 AND prac.total_projfunc_raw_cost IS NULL
2462 AND prac.total_projfunc_burdened_cost IS NULL
2463 AND prac.total_projfunc_revenue IS NULL
2464 AND prac.total_projfunc_init_raw_cost IS NULL
2465 AND prac.total_projfunc_init_bd_cost IS NULL
2466 AND prac.total_projfunc_init_revenue IS NULL
2467 AND prac.total_display_quantity IS NULL
2468 ;
2469 -- end of bug#12865217
2470
2471 IF P_PA_DEBUG_MODE = 'Y' THEN
2472 PA_DEBUG.RESET_CURR_FUNCTION;
2473 END IF;
2474 EXCEPTION
2475 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2476
2477 l_msg_count := FND_MSG_PUB.count_msg;
2478 IF l_msg_count = 1 THEN
2479 PA_INTERFACE_UTILS_PUB.get_messages
2480 ( p_encoded => FND_API.G_TRUE,
2481 p_msg_index => 1,
2482 p_msg_count => l_msg_count,
2483 p_msg_data => l_msg_data,
2484 p_data => l_data,
2485 p_msg_index_out => l_msg_index_out);
2486 x_msg_data := l_data;
2487 x_msg_count := l_msg_count;
2488 ELSE
2489 x_msg_count := l_msg_count;
2490 END IF;
2491
2492 -- Removed ROLLBACK statement.
2493
2494 x_return_status := FND_API.G_RET_STS_ERROR;
2495
2496 IF P_PA_DEBUG_MODE = 'Y' THEN
2497 pa_fp_gen_amount_utils.fp_debug
2498 ( p_msg => 'Invalid Arguments Passed',
2499 p_called_mode => p_called_mode,
2500 p_module_name => l_module_name,
2501 p_log_level => l_log_level );
2502 PA_DEBUG.RESET_CURR_FUNCTION;
2503 END IF;
2504 -- Removed RAISE statement.
2505
2506 WHEN OTHERS THEN
2507 -- Removed ROLLBACK statement.
2508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2509 x_msg_count := 1;
2510 x_msg_data := substr(sqlerrm,1,240);
2511 -- dbms_output.put_line('error msg :'||x_msg_data);
2512 FND_MSG_PUB.add_exc_msg
2513 ( p_pkg_name => 'PA_RES_ASG_CURRENCY_PUB',
2514 p_procedure_name => 'ROLLUP_AMOUNTS',
2515 p_error_text => substr(sqlerrm,1,240));
2516 IF P_PA_DEBUG_MODE = 'Y' THEN
2517 pa_fp_gen_amount_utils.fp_debug
2518 ( p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
2519 p_called_mode => p_called_mode,
2520 p_module_name => l_module_name,
2521 p_log_level => l_log_level);
2522 PA_DEBUG.RESET_CURR_FUNCTION;
2523 END IF;
2524 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2525
2526 END ROLLUP_AMOUNTS;
2527
2528
2529 END PA_RES_ASG_CURRENCY_PUB;