[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.1.12010000.2 2008/12/05 10:13:01 rthumma 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,
82 P_CALLING_MODULE IN VARCHAR2,
83 P_VERSION_LEVEL_FLAG IN VARCHAR2 DEFAULT 'N',
84 P_CALLED_MODE IN VARCHAR2 DEFAULT 'SELF_SERVICE',
85 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
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
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
195 ( P_MSG => 'Invalid p_calling_module value: '
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
284 -- Step 1: Handle Deletion
285 IF p_delete_flag = 'Y' THEN
286
287 IF p_pa_debug_mode = 'Y' THEN
288 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
289 P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.' ||
290 'DELETE_TABLE_RECORDS',
291 P_CALLED_MODE => p_called_mode,
292 P_MODULE_NAME => l_module_name);
293 END IF;
294 PA_RES_ASG_CURRENCY_PUB.DELETE_TABLE_RECORDS
295 ( P_FP_COLS_REC => p_fp_cols_rec,
296 P_CALLING_MODULE => p_calling_module,
297 P_VERSION_LEVEL_FLAG => p_version_level_flag,
298 P_CALLED_MODE => p_called_mode,
299 X_RETURN_STATUS => x_return_status,
300 X_MSG_COUNT => x_msg_count,
301 X_MSG_DATA => x_msg_data );
302 IF p_pa_debug_mode = 'Y' THEN
303 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
304 P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.' ||
305 'DELETE_TABLE_RECORDS: '||x_return_status,
306 P_CALLED_MODE => p_called_mode,
307 P_MODULE_NAME => l_module_name);
308 END IF;
309 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
310 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
311 END IF;
312
313 IF P_PA_DEBUG_MODE = 'Y' THEN
314 PA_DEBUG.RESET_CURR_FUNCTION;
315 END IF;
316
317 -- Return control to caller.
318 RETURN;
319 END IF; -- p_delete_flag = 'Y'
320
321 -- Step 0 (CONTD): Additional Temp Mode Validation
322 -- When processing non-delete operations in Temp Table Mode,
323 -- the following validation rules apply to temp table data:
324 -- 1. delete_flag should not be 'Y'.
325 -- Remove such records from the temp table.
326 -- 2A. Whenever copy_flag = 'Y'
327 -- (resource_assignment_id) must be unique.
328 -- Raise an error if duplicates are found.
329 -- 2B. Whenever copy_flag <> 'Y'
330 -- (resource_assignment_id, txn_currency_code) must be unique.
331 -- Raise an error if duplicates are found.
332 -- 3. (resource_assignment_id) should never be null.
333 -- Raise an error if any records with null resource_assignment_id are found.
334 -- 4. Whenever copy_flag <> 'Y',
335 -- txn_currency_code should not be null.
336 -- Raise an error if any records with null resource_assignment_id are found.
337
338
339 IF p_version_level_flag = 'N' THEN
340
341 -- 0.1. Remove records with delete_flag = 'Y' from temp table.
342 DELETE FROM pa_resource_asgn_curr_tmp
343 WHERE NVL(delete_flag,'N') = 'Y';
344
345 IF p_copy_flag = 'Y' THEN
346 -- 0.2A. Validate that no records in the temp table share the same
347 -- resource_assignment_id. When the context is Copy, currency
348 -- code is not populated in the temp table.
349 SELECT resource_assignment_id,
350 NULL,
351 count(*)
352 BULK COLLECT
353 INTO l_ra_id_tab,
354 l_txn_currency_code_tab,
355 l_duplicate_count_tab
356 FROM pa_resource_asgn_curr_tmp
357 GROUP BY resource_assignment_id
358 HAVING count(*) > 1;
359 ELSE
360 -- 0.2B. Validate that no records in the temp table share the same
361 -- (resource_assignment_id, txn_currency_code) combination.
362 SELECT resource_assignment_id,
363 txn_currency_code,
364 count(*)
365 BULK COLLECT
366 INTO l_ra_id_tab,
367 l_txn_currency_code_tab,
368 l_duplicate_count_tab
369 FROM pa_resource_asgn_curr_tmp
370 GROUP BY resource_assignment_id, txn_currency_code
371 HAVING count(*) > 1;
372 END IF; -- IF p_copy_flag = 'Y' THEN
373
374 -- Raise an error if duplicates are found.
375 IF l_ra_id_tab.count > 0 THEN
376 IF p_pa_debug_mode = 'Y' THEN
377 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
378 ( p_msg => 'Duplicate records found in '
379 || 'PA_RESOURCE_ASGN_CURR_TMP '
380 || '(count='||l_ra_id_tab.count||'):',
381 p_called_mode => p_called_mode,
382 p_module_name => l_module_name,
383 p_log_level => l_log_level );
384 FOR i IN 1..l_ra_id_tab.count LOOP
385 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
386 ( p_msg => 'Record'||i||': '
387 || 'resource_assignment_id:['||l_ra_id_tab(i)||'], '
388 || 'txn_currency_code:['||l_txn_currency_code_tab(i)||'], '
389 || 'number_of_duplicates:['||l_duplicate_count_tab(i)||'] ',
390 p_called_mode => p_called_mode,
391 p_module_name => l_module_name,
392 p_log_level => l_log_level );
393 END LOOP;
394 END IF; -- debug mode logic
395 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
396 END IF; -- duplicate record check
397
398 -- 0.3. Raise an error if any records with null resource_assignment_id are found.
399 SELECT count(*)
400 INTO l_null_record_count
401 FROM pa_resource_asgn_curr_tmp
402 WHERE resource_assignment_id IS NULL;
403
404 IF l_null_record_count > 0 THEN
405 IF p_pa_debug_mode = 'Y' THEN
406 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
407 ( p_msg => 'Records with null resource_assignment_id found in '
408 || 'PA_RESOURCE_ASGN_CURR_TMP '
409 || '(count='||l_null_record_count||'):',
410 p_called_mode => p_called_mode,
411 p_module_name => l_module_name,
412 p_log_level => l_log_level );
413 END IF;
414 --PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
415 -- p_msg_name => 'PA_RBC_RA_ID_NULL_ERR' );
416 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
417 END IF; -- IF l_null_record_count > 0 THEN
418
419 -- 0.4. Whenever copy_flag <> 'Y', txn_currency_code should not be null.
420 -- Raise an error if any records with null resource_assignment_id are found.
421 IF p_copy_flag <> 'Y' THEN
422 SELECT count(*)
423 INTO l_null_record_count
424 FROM pa_resource_asgn_curr_tmp
425 WHERE txn_currency_code IS NULL;
426
427 IF l_null_record_count > 0 THEN
428 IF p_pa_debug_mode = 'Y' THEN
429 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
430 ( p_msg => 'Records with null txn_currency_code found in '
431 || 'PA_RESOURCE_ASGN_CURR_TMP '
432 || '(count='||l_null_record_count||'):',
433 p_called_mode => p_called_mode,
434 p_module_name => l_module_name,
435 p_log_level => l_log_level );
436 END IF;
437 --PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
438 -- p_msg_name => 'PA_RBC_TXN_CUR_NULL_ERR' );
439 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
440 END IF; -- IF l_null_record_count > 0 THEN
441 END IF; --IF p_copy_flag = 'Y' THEN
442
443 END IF; -- additional Table Mode validation
444
445
446 -- Step 2: Handle Copy
447 IF p_copy_flag = 'Y' THEN
448
449 IF p_pa_debug_mode = 'Y' THEN
450 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
451 P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.' ||
452 'COPY_TABLE_RECORDS',
453 P_CALLED_MODE => p_called_mode,
454 P_MODULE_NAME => l_module_name);
455 END IF;
456 PA_RES_ASG_CURRENCY_PUB.COPY_TABLE_RECORDS
457 ( P_FP_COLS_REC => p_fp_cols_rec,
458 P_SRC_VERSION_ID => p_src_version_id,
459 P_COPY_MODE => p_copy_mode,
460 P_CALLING_MODULE => p_calling_module,
461 P_VERSION_LEVEL_FLAG => p_version_level_flag,
462 P_CALLED_MODE => p_called_mode,
463 X_RETURN_STATUS => x_return_status,
464 X_MSG_COUNT => x_msg_count,
465 X_MSG_DATA => x_msg_data );
466 IF p_pa_debug_mode = 'Y' THEN
467 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
468 P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.' ||
469 'COPY_TABLE_RECORDS: '||x_return_status,
470 P_CALLED_MODE => p_called_mode,
471 P_MODULE_NAME => l_module_name);
472 END IF;
473 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
474 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
475 END IF;
476
477 IF P_PA_DEBUG_MODE = 'Y' THEN
478 PA_DEBUG.RESET_CURR_FUNCTION;
479 END IF;
480
481 -- Return control to caller.
482 RETURN;
483 END IF; -- p_copy_flag = 'Y'
484
485
486 -- Step 3: Handle Insertion
487 IF p_rollup_flag = 'N' THEN
488
489 IF p_pa_debug_mode = 'Y' THEN
490 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
491 P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.' ||
492 'INSERT_TABLE_RECORDS',
493 P_CALLED_MODE => p_called_mode,
494 P_MODULE_NAME => l_module_name);
495 END IF;
496 PA_RES_ASG_CURRENCY_PUB.INSERT_TABLE_RECORDS
497 ( P_FP_COLS_REC => p_fp_cols_rec,
498 P_CALLING_MODULE => p_calling_module,
499 P_VERSION_LEVEL_FLAG => p_version_level_flag,
500 P_CALLED_MODE => p_called_mode,
501 X_RETURN_STATUS => x_return_status,
502 X_MSG_COUNT => x_msg_count,
503 X_MSG_DATA => x_msg_data );
504 IF p_pa_debug_mode = 'Y' THEN
505 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
506 P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.' ||
507 'INSERT_TABLE_RECORDS: '||x_return_status,
508 P_CALLED_MODE => p_called_mode,
509 P_MODULE_NAME => l_module_name);
510 END IF;
511 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
512 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
513 END IF;
514
515 -- Step 4: Handle Rollup of Amounts
516 ELSIF p_rollup_flag = 'Y' THEN
517
518 IF p_pa_debug_mode = 'Y' THEN
519 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
520 P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.' ||
521 'ROLLUP_AMOUNTS',
522 P_CALLED_MODE => p_called_mode,
523 P_MODULE_NAME => l_module_name);
524 END IF;
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.' ||
536 'ROLLUP_AMOUNTS: '||x_return_status,
537 P_CALLED_MODE => p_called_mode,
538 P_MODULE_NAME => l_module_name);
539 END IF;
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||']',
664 p_called_mode => p_called_mode,
665 p_module_name => l_module_name,
666 p_log_level => l_log_level );
667 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
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
776 PA_DEBUG.SET_CURR_FUNCTION
777 ( p_function => 'DELETE_TABLE_RECORDS',
778 p_debug_mode => p_pa_debug_mode );
779 END IF;
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
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 END IF; -- p_calling_module check
833
834 END IF; -- p_version_level_flag check
835
836 IF P_PA_DEBUG_MODE = 'Y' THEN
837 PA_DEBUG.RESET_CURR_FUNCTION;
838 END IF;
839 EXCEPTION
840 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
841
842 l_msg_count := FND_MSG_PUB.count_msg;
843 IF l_msg_count = 1 THEN
844 PA_INTERFACE_UTILS_PUB.get_messages
845 ( p_encoded => FND_API.G_TRUE,
846 p_msg_index => 1,
847 p_msg_count => l_msg_count,
848 p_msg_data => l_msg_data,
849 p_data => l_data,
850 p_msg_index_out => l_msg_index_out);
851 x_msg_data := l_data;
852 x_msg_count := l_msg_count;
853 ELSE
854 x_msg_count := l_msg_count;
855 END IF;
856
857 -- Removed ROLLBACK statement.
858
859 x_return_status := FND_API.G_RET_STS_ERROR;
860
861 IF P_PA_DEBUG_MODE = 'Y' THEN
862 pa_fp_gen_amount_utils.fp_debug
863 ( p_msg => 'Invalid Arguments Passed',
864 p_called_mode => p_called_mode,
865 p_module_name => l_module_name,
866 p_log_level => l_log_level );
867 PA_DEBUG.RESET_CURR_FUNCTION;
868 END IF;
869 -- Removed RAISE statement.
870
871 WHEN OTHERS THEN
872 -- Removed ROLLBACK statement.
873 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
874 x_msg_count := 1;
875 x_msg_data := substr(sqlerrm,1,240);
876 -- dbms_output.put_line('error msg :'||x_msg_data);
877 FND_MSG_PUB.add_exc_msg
878 ( p_pkg_name => 'PA_RES_ASG_CURRENCY_PUB',
879 p_procedure_name => 'DELETE_TABLE_RECORDS',
880 p_error_text => substr(sqlerrm,1,240));
881 IF P_PA_DEBUG_MODE = 'Y' THEN
882 pa_fp_gen_amount_utils.fp_debug
883 ( p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
884 p_called_mode => p_called_mode,
885 p_module_name => l_module_name,
886 p_log_level => l_log_level);
887 PA_DEBUG.RESET_CURR_FUNCTION;
888 END IF;
889 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
890
891 END DELETE_TABLE_RECORDS;
892
893
894 PROCEDURE COPY_TABLE_RECORDS
895 ( P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
896 P_SRC_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
897 P_COPY_MODE IN VARCHAR2,
898 P_CALLING_MODULE IN VARCHAR2,
899 P_VERSION_LEVEL_FLAG IN VARCHAR2,
900 P_CALLED_MODE IN VARCHAR2,
901 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
902 X_MSG_COUNT OUT NOCOPY NUMBER,
903 X_MSG_DATA OUT NOCOPY VARCHAR2)
904 IS
905 l_module_name VARCHAR2(100) := 'pa.plsql.PA_RES_ASG_CURRENCY_PUB.COPY_TABLE_RECORDS';
906 l_log_level NUMBER := 5;
907
908 l_msg_count NUMBER;
909 l_data VARCHAR2(2000);
910 l_msg_data VARCHAR2(2000);
911 l_msg_index_out NUMBER;
912
913 l_last_updated_by NUMBER := FND_GLOBAL.user_id;
914 l_last_update_login NUMBER := FND_GLOBAL.login_id;
915 l_sysdate DATE := SYSDATE;
916 l_record_version_number NUMBER := 1;
917
918 -- This cursor gets all the source pa_resource_asgn_curr records
919 -- for the entire version specified by c_src_version_id.
920 -- This cursor should be used when p_version_level_flag is 'Y'
921 -- and p_copy_mode is 'COPY_ALL'.
922
923 CURSOR ver_lvl_copy_all_csr
924 ( c_src_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE,
925 c_tgt_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE ) IS
926 SELECT tgt_ra.RESOURCE_ASSIGNMENT_ID,
927 src_rbc.TXN_CURRENCY_CODE,
928 src_rbc.TOTAL_QUANTITY,
929 src_rbc.TOTAL_INIT_QUANTITY,
930 src_rbc.TXN_RAW_COST_RATE_OVERRIDE,
931 src_rbc.TXN_BURDEN_COST_RATE_OVERRIDE,
932 src_rbc.TXN_BILL_RATE_OVERRIDE,
933 src_rbc.TXN_AVERAGE_RAW_COST_RATE,
934 src_rbc.TXN_AVERAGE_BURDEN_COST_RATE,
935 src_rbc.TXN_AVERAGE_BILL_RATE,
936 src_rbc.TXN_ETC_RAW_COST_RATE,
937 src_rbc.TXN_ETC_BURDEN_COST_RATE,
938 src_rbc.TXN_ETC_BILL_RATE,
939 src_rbc.TOTAL_TXN_RAW_COST,
940 src_rbc.TOTAL_TXN_BURDENED_COST,
941 src_rbc.TOTAL_TXN_REVENUE,
942 src_rbc.TOTAL_TXN_INIT_RAW_COST,
943 src_rbc.TOTAL_TXN_INIT_BURDENED_COST,
944 src_rbc.TOTAL_TXN_INIT_REVENUE,
945 src_rbc.TOTAL_PROJECT_RAW_COST,
946 src_rbc.TOTAL_PROJECT_BURDENED_COST,
947 src_rbc.TOTAL_PROJECT_REVENUE,
948 src_rbc.TOTAL_PROJECT_INIT_RAW_COST,
949 src_rbc.TOTAL_PROJECT_INIT_BD_COST,
950 src_rbc.TOTAL_PROJECT_INIT_REVENUE,
951 src_rbc.TOTAL_PROJFUNC_RAW_COST,
952 src_rbc.TOTAL_PROJFUNC_BURDENED_COST,
953 src_rbc.TOTAL_PROJFUNC_REVENUE,
954 src_rbc.TOTAL_PROJFUNC_INIT_RAW_COST,
955 src_rbc.TOTAL_PROJFUNC_INIT_BD_COST,
956 src_rbc.TOTAL_PROJFUNC_INIT_REVENUE,
957 src_rbc.TOTAL_DISPLAY_QUANTITY
958 FROM pa_resource_assignments tgt_ra,
959 pa_resource_assignments src_ra,
960 pa_resource_asgn_curr src_rbc
961 WHERE src_rbc.budget_version_id = c_src_version_id
962 AND src_ra.resource_assignment_id = src_rbc.resource_assignment_id
963 AND nvl(tgt_ra.task_id,0) = nvl(src_ra.task_id,0)
964 AND tgt_ra.resource_list_member_id = src_ra.resource_list_member_id
965 AND tgt_ra.budget_version_id = c_tgt_version_id;
966
967 -- This cursor gets overrides from the source pa_resource_asgn_curr
968 -- records for the entire version specified by c_src_version_id.
969 -- This cursor should be used when p_version_level_flag is 'Y'
970 -- and p_copy_mode is 'COPY_OVERRIDES'.
971
972 CURSOR ver_lvl_copy_overrides_csr
973 ( c_src_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE,
974 c_tgt_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE ) IS
975 SELECT tgt_ra.RESOURCE_ASSIGNMENT_ID,
976 src_rbc.TXN_CURRENCY_CODE,
977 src_rbc.TXN_RAW_COST_RATE_OVERRIDE,
978 src_rbc.TXN_BURDEN_COST_RATE_OVERRIDE,
979 src_rbc.TXN_BILL_RATE_OVERRIDE
980 FROM pa_resource_assignments tgt_ra,
981 pa_resource_assignments src_ra,
982 pa_resource_asgn_curr src_rbc
983 WHERE src_rbc.budget_version_id = c_src_version_id
984 AND src_ra.resource_assignment_id = src_rbc.resource_assignment_id
985 AND nvl(tgt_ra.task_id,0) = nvl(src_ra.task_id,0)
986 AND tgt_ra.resource_list_member_id = src_ra.resource_list_member_id
987 AND tgt_ra.budget_version_id = c_tgt_version_id;
988
989 -- This cursor gets overrides from the source pa_resource_asgn_curr
990 -- records for the resources specified in pa_resource_asgn_curr_tmp
991 -- for the version specified by c_src_version_id.
992 -- This cursor should be used when p_version_level_flag is 'N'
993 -- and p_copy_mode is 'COPY_OVERRIDES'.
994 -- Note: Ordered hint has been added to avoid a Merge Join Cartesian
995 -- join order in the execution plan.
996
997 CURSOR tbl_mode_copy_overrides_csr
998 ( c_src_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE,
999 c_tgt_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE ) IS
1000 SELECT /*+ ORDERED */
1001 tmp.RESOURCE_ASSIGNMENT_ID,
1002 src_rbc.TXN_CURRENCY_CODE,
1003 src_rbc.TXN_RAW_COST_RATE_OVERRIDE,
1004 src_rbc.TXN_BURDEN_COST_RATE_OVERRIDE,
1005 src_rbc.TXN_BILL_RATE_OVERRIDE
1006 FROM pa_resource_asgn_curr src_rbc,
1007 pa_resource_assignments src_ra,
1008 pa_resource_assignments tgt_ra,
1009 pa_resource_asgn_curr_tmp tmp
1010 WHERE tgt_ra.budget_version_id = c_tgt_version_id
1011 AND src_ra.budget_version_id = c_src_version_id
1012 AND tgt_ra.resource_assignment_id = tmp.resource_assignment_id
1013 AND nvl(src_ra.task_id,0) = nvl(tgt_ra.task_id,0)
1014 AND src_ra.resource_list_member_id = tgt_ra.resource_list_member_id
1015 AND src_rbc.resource_assignment_id = src_ra.resource_assignment_id
1016 AND src_rbc.budget_version_id = src_ra.budget_version_id;
1017
1018
1019 -- PL/SQL tables for storing copied source rates and amount totals
1020 l_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1021 l_txn_currency_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
1022 l_total_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
1023 l_total_init_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
1024 l_raw_cost_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
1025 l_brdn_cost_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
1026 l_bill_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
1027 l_avg_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1028 l_avg_burden_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1029 l_avg_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1030 l_etc_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1031 l_etc_burden_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1032 l_etc_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1033 l_txn_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1034 l_txn_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1035 l_txn_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
1036 l_txn_init_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1037 l_txn_init_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1038 l_txn_init_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
1039 l_pc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1040 l_pc_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1041 l_pc_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
1042 l_pc_init_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1043 l_pc_init_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1044 l_pc_init_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
1045 l_pfc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1046 l_pfc_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1047 l_pfc_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
1048 l_pfc_init_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1049 l_pfc_init_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1050 l_pfc_init_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
1051 l_display_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
1052
1053 l_NULL_NumTabTyp PA_PLSQL_DATATYPES.NumTabTyp;
1054
1055 -- Indicates if the Target version is a Workplan.
1056 l_wp_version_flag PA_BUDGET_VERSIONS.WP_VERSION_FLAG%TYPE;
1057
1058 BEGIN
1059 IF p_pa_debug_mode = 'Y' THEN
1060 PA_DEBUG.SET_CURR_FUNCTION
1061 ( p_function => 'COPY_TABLE_RECORDS',
1062 p_debug_mode => p_pa_debug_mode );
1063 END IF;
1064
1065 PA_RES_ASG_CURRENCY_PUB.PRINT_INPUT_PARAMS
1066 ( P_CALLING_API => G_PVT_COPY,
1067 P_FP_COLS_REC => p_fp_cols_rec,
1068 P_CALLING_MODULE => p_calling_module,
1069 P_SRC_VERSION_ID => p_src_version_id,
1070 P_COPY_MODE => p_copy_mode,
1071 P_VERSION_LEVEL_FLAG => p_version_level_flag,
1072 P_CALLED_MODE => p_called_mode );
1073
1074 x_return_status := FND_API.G_RET_STS_SUCCESS;
1075 x_msg_count := 0;
1076
1077 BEGIN
1078 SELECT nvl(wp_version_flag,'N')
1079 INTO l_wp_version_flag
1080 FROM pa_budget_versions
1081 WHERE budget_version_id = p_fp_cols_rec.x_budget_version_id;
1082 EXCEPTION WHEN NO_DATA_FOUND THEN
1083 IF p_pa_debug_mode = 'Y' THEN
1084 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1085 ( P_MSG => 'Invalid p_fp_cols_rec.x_budget_version_id value: '
1086 || p_fp_cols_rec.x_budget_version_id
1087 || '. Budget version does not exist.',
1088 P_CALLED_MODE => p_called_mode,
1089 P_MODULE_NAME => l_module_name,
1090 P_LOG_LEVEL => l_log_level );
1091 END IF;
1092 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1093 END;
1094
1095 -- Step 1: Select rates and amounts from the appropriate cursor.
1096
1097 IF p_version_level_flag = 'Y' THEN
1098
1099 IF p_copy_mode = G_COPY_ALL THEN
1100 OPEN ver_lvl_copy_all_csr
1101 (p_src_version_id,
1102 p_fp_cols_rec.x_budget_version_id);
1103 FETCH ver_lvl_copy_all_csr
1104 BULK COLLECT
1105 INTO l_ra_id_tab,
1106 l_txn_currency_code_tab,
1107 l_total_quantity_tab,
1108 l_total_init_quantity_tab,
1109 l_raw_cost_rate_override_tab,
1110 l_brdn_cost_rate_override_tab,
1111 l_bill_rate_override_tab,
1112 l_avg_raw_cost_rate_tab,
1113 l_avg_burden_cost_rate_tab,
1114 l_avg_bill_rate_tab,
1115 l_etc_raw_cost_rate_tab,
1116 l_etc_burden_cost_rate_tab,
1117 l_etc_bill_rate_tab,
1118 l_txn_raw_cost_tab,
1119 l_txn_burdened_cost_tab,
1120 l_txn_revenue_tab,
1121 l_txn_init_raw_cost_tab,
1122 l_txn_init_burdened_cost_tab,
1123 l_txn_init_revenue_tab,
1124 l_pc_raw_cost_tab,
1125 l_pc_burdened_cost_tab,
1126 l_pc_revenue_tab,
1127 l_pc_init_raw_cost_tab,
1128 l_pc_init_burdened_cost_tab,
1129 l_pc_init_revenue_tab,
1130 l_pfc_raw_cost_tab,
1131 l_pfc_burdened_cost_tab,
1132 l_pfc_revenue_tab,
1133 l_pfc_init_raw_cost_tab,
1134 l_pfc_init_burdened_cost_tab,
1135 l_pfc_init_revenue_tab,
1136 l_display_quantity_tab;
1137 CLOSE ver_lvl_copy_all_csr;
1138 ELSIF p_copy_mode = G_COPY_OVERRIDES THEN
1139 OPEN ver_lvl_copy_overrides_csr
1140 (p_src_version_id,
1141 p_fp_cols_rec.x_budget_version_id);
1142 FETCH ver_lvl_copy_overrides_csr
1143 BULK COLLECT
1144 INTO l_ra_id_tab,
1145 l_txn_currency_code_tab,
1146 l_raw_cost_rate_override_tab,
1147 l_brdn_cost_rate_override_tab,
1148 l_bill_rate_override_tab;
1149 CLOSE ver_lvl_copy_overrides_csr;
1150 END IF; -- p_copy_mode check
1151
1152 ELSIF p_version_level_flag = 'N' THEN
1153
1154 IF p_copy_mode = G_COPY_ALL THEN
1155 -- This case is currently NOT supported.
1156 IF p_pa_debug_mode = 'Y' THEN
1157 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1158 ( P_MSG => 'The '''||G_COPY_ALL||''' copy mode is not supported '
1159 || 'when p_version_level_flag is '
1160 || p_version_level_flag,
1161 P_CALLED_MODE => p_called_mode,
1162 P_MODULE_NAME => l_module_name,
1163 P_LOG_LEVEL => l_log_level );
1164 END IF;
1165 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1166 ELSIF p_copy_mode = G_COPY_OVERRIDES THEN
1167 OPEN tbl_mode_copy_overrides_csr
1168 (p_src_version_id,
1169 p_fp_cols_rec.x_budget_version_id);
1170 FETCH tbl_mode_copy_overrides_csr
1171 BULK COLLECT
1172 INTO l_ra_id_tab,
1173 l_txn_currency_code_tab,
1174 l_raw_cost_rate_override_tab,
1175 l_brdn_cost_rate_override_tab,
1176 l_bill_rate_override_tab;
1177 CLOSE tbl_mode_copy_overrides_csr;
1178 END IF; -- p_copy_mode check
1179
1180 END IF; -- p_version_level_flag check
1181
1182 -- No further processing is required if there are no records to copy.
1183 IF l_ra_id_tab.count <= 0 THEN
1184 IF P_PA_DEBUG_MODE = 'Y' THEN
1185 PA_DEBUG.RESET_CURR_FUNCTION;
1186 END IF;
1187 RETURN;
1188 END IF;
1189
1190 -- Step 2: Process pl/sql tables as needed.
1191
1192 -- Initialize a pl/sql table of length l_ra_id_tab.count with nulls.
1193 -- We can use this table to null out entire tables during processing.
1194 -- This should perform better than nulling out records in a loop.
1195 l_null_NumTabTyp.delete;
1196 FOR i IN 1..l_ra_id_tab.count LOOP
1197 l_null_NumTabTyp(i) := null;
1198 END LOOP;
1199
1200 IF p_copy_mode = G_COPY_ALL THEN
1201 -- ETC Rate columns should Null for Budgets,
1202 -- but should be populated for Forecasts and Workplans.
1203 -- Additionally, Actuals columns should be nulled out.
1204 IF l_wp_version_flag = 'N' AND
1205 p_fp_cols_rec.x_plan_class_code = 'BUDGET' THEN
1206 l_etc_raw_cost_rate_tab := l_null_NumTabTyp;
1207 l_etc_burden_cost_rate_tab := l_null_NumTabTyp;
1208 l_etc_bill_rate_tab := l_null_NumTabTyp;
1209 l_total_init_quantity_tab := l_null_NumTabTyp;
1210 l_txn_init_raw_cost_tab := l_null_NumTabTyp;
1211 l_txn_init_burdened_cost_tab := l_null_NumTabTyp;
1212 l_txn_init_revenue_tab := l_null_NumTabTyp;
1213 l_pc_init_raw_cost_tab := l_null_NumTabTyp;
1214 l_pc_init_burdened_cost_tab := l_null_NumTabTyp;
1215 l_pc_init_revenue_tab := l_null_NumTabTyp;
1216 l_pfc_init_raw_cost_tab := l_null_NumTabTyp;
1217 l_pfc_init_burdened_cost_tab := l_null_NumTabTyp;
1218 l_pfc_init_revenue_tab := l_null_NumTabTyp;
1219 END IF; -- ETC Rate column logic
1220
1221 -- Only rates and totals relevant to the version type should be populated.
1222 -- Cost-only versions should not have revenue rates or totals.
1223 IF p_fp_cols_rec.x_version_type = 'COST' THEN
1224 l_bill_rate_override_tab := l_null_NumTabTyp;
1225 l_avg_bill_rate_tab := l_null_NumTabTyp;
1226 l_etc_bill_rate_tab := l_null_NumTabTyp;
1227 l_txn_revenue_tab := l_null_NumTabTyp;
1228 l_txn_init_revenue_tab := l_null_NumTabTyp;
1229 l_pc_revenue_tab := l_null_NumTabTyp;
1230 l_pc_init_revenue_tab := l_null_NumTabTyp;
1231 l_pfc_revenue_tab := l_null_NumTabTyp;
1232 l_pfc_init_revenue_tab := l_null_NumTabTyp;
1233 -- Revenue-only versions should not have cost rates or totals.
1234 ELSIF p_fp_cols_rec.x_version_type = 'REVENUE' THEN
1235 l_raw_cost_rate_override_tab := l_null_NumTabTyp;
1236 l_brdn_cost_rate_override_tab := l_null_NumTabTyp;
1237 l_avg_raw_cost_rate_tab := l_null_NumTabTyp;
1238 l_avg_burden_cost_rate_tab := l_null_NumTabTyp;
1239 l_etc_raw_cost_rate_tab := l_null_NumTabTyp;
1240 l_etc_burden_cost_rate_tab := l_null_NumTabTyp;
1241 l_txn_raw_cost_tab := l_null_NumTabTyp;
1242 l_txn_burdened_cost_tab := l_null_NumTabTyp;
1243 l_txn_init_raw_cost_tab := l_null_NumTabTyp;
1244 l_txn_init_burdened_cost_tab := l_null_NumTabTyp;
1245 l_pc_raw_cost_tab := l_null_NumTabTyp;
1246 l_pc_burdened_cost_tab := l_null_NumTabTyp;
1247 l_pc_init_raw_cost_tab := l_null_NumTabTyp;
1248 l_pc_init_burdened_cost_tab := l_null_NumTabTyp;
1249 l_pfc_raw_cost_tab := l_null_NumTabTyp;
1250 l_pfc_burdened_cost_tab := l_null_NumTabTyp;
1251 l_pfc_init_raw_cost_tab := l_null_NumTabTyp;
1252 l_pfc_init_burdened_cost_tab := l_null_NumTabTyp;
1253 END IF;
1254 ELSIF p_copy_mode = G_COPY_OVERRIDES THEN
1255 -- Only rates relevant to the version type should be populated.
1256 -- Cost-only versions should not have revenue rates.
1257 IF p_fp_cols_rec.x_version_type = 'COST' THEN
1258 l_bill_rate_override_tab := l_null_NumTabTyp;
1259 -- Revenue-only versions should not have cost rates.
1260 ELSIF p_fp_cols_rec.x_version_type = 'REVENUE' THEN
1261 l_raw_cost_rate_override_tab := l_null_NumTabTyp;
1262 l_brdn_cost_rate_override_tab := l_null_NumTabTyp;
1263 END IF;
1264 END IF; -- p_copy_mode check
1265
1266 -- Step 3: Delete target records from PA_RESOURCE_ASGN_CURR
1267 -- that are being copied over from source records.
1268
1269 -- Populate PA_RESOURCE_ASGN_CURR_TMP with the target records.
1270 -- Note that the DELETE_TABLE_RECORDS API does not require the
1271 -- DELETE_FLAG column be populated for internal p_calling_module
1272 -- values (though the flag is required for public p_calling_modules).
1273 -- See the is_public_calling_module() and is_private_calling_module()
1274 -- functions for details on private/public calling modules.
1275
1276 DELETE PA_RESOURCE_ASGN_CURR_TMP;
1277
1278 FORALL i IN 1..l_ra_id_tab.count
1279 INSERT INTO PA_RESOURCE_ASGN_CURR_TMP (
1280 RESOURCE_ASSIGNMENT_ID,
1281 TXN_CURRENCY_CODE )
1282 VALUES (
1283 l_ra_id_tab(i),
1284 l_txn_currency_code_tab(i) );
1285
1286 IF p_pa_debug_mode = 'Y' THEN
1287 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1288 ( P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.'
1289 || 'DELETE_TABLE_RECORDS',
1290 P_CALLED_MODE => p_called_mode,
1291 P_MODULE_NAME => l_module_name,
1292 P_LOG_LEVEL => l_log_level );
1293 END IF;
1294 PA_RES_ASG_CURRENCY_PUB.DELETE_TABLE_RECORDS
1295 ( P_FP_COLS_REC => p_fp_cols_rec,
1296 P_CALLING_MODULE => G_PVT_COPY,
1297 P_VERSION_LEVEL_FLAG => 'N',
1298 P_CALLED_MODE => p_called_mode,
1299 X_RETURN_STATUS => x_return_status,
1300 X_MSG_COUNT => x_msg_count,
1301 X_MSG_DATA => x_msg_data );
1302 IF p_pa_debug_mode = 'Y' THEN
1303 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1304 ( P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.'
1305 || 'DELETE_TABLE_RECORDS: ' || x_return_status,
1306 P_CALLED_MODE => p_called_mode,
1307 P_MODULE_NAME => l_module_name,
1308 P_LOG_LEVEL => l_log_level );
1309 END IF;
1310 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1311 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1312 END IF;
1313
1314 -- Step 4: Insert records into the PA_RESOURCE_ASGN_CURR table.
1315
1316 IF p_copy_mode = G_COPY_ALL THEN
1317 FORALL i IN 1..l_ra_id_tab.count
1318 INSERT INTO PA_RESOURCE_ASGN_CURR (
1319 RA_TXN_ID,
1320 BUDGET_VERSION_ID,
1321 RESOURCE_ASSIGNMENT_ID,
1322 TXN_CURRENCY_CODE,
1323 TOTAL_QUANTITY,
1324 TOTAL_INIT_QUANTITY,
1325 TXN_RAW_COST_RATE_OVERRIDE,
1326 TXN_BURDEN_COST_RATE_OVERRIDE,
1327 TXN_BILL_RATE_OVERRIDE,
1328 TXN_AVERAGE_RAW_COST_RATE,
1329 TXN_AVERAGE_BURDEN_COST_RATE,
1330 TXN_AVERAGE_BILL_RATE,
1331 TXN_ETC_RAW_COST_RATE,
1332 TXN_ETC_BURDEN_COST_RATE,
1333 TXN_ETC_BILL_RATE,
1334 TOTAL_TXN_RAW_COST,
1335 TOTAL_TXN_BURDENED_COST,
1336 TOTAL_TXN_REVENUE,
1337 TOTAL_TXN_INIT_RAW_COST,
1338 TOTAL_TXN_INIT_BURDENED_COST,
1339 TOTAL_TXN_INIT_REVENUE,
1340 TOTAL_PROJECT_RAW_COST,
1341 TOTAL_PROJECT_BURDENED_COST,
1342 TOTAL_PROJECT_REVENUE,
1343 TOTAL_PROJECT_INIT_RAW_COST,
1344 TOTAL_PROJECT_INIT_BD_COST,
1345 TOTAL_PROJECT_INIT_REVENUE,
1346 TOTAL_PROJFUNC_RAW_COST,
1347 TOTAL_PROJFUNC_BURDENED_COST,
1348 TOTAL_PROJFUNC_REVENUE,
1349 TOTAL_PROJFUNC_INIT_RAW_COST,
1350 TOTAL_PROJFUNC_INIT_BD_COST,
1351 TOTAL_PROJFUNC_INIT_REVENUE,
1352 TOTAL_DISPLAY_QUANTITY,
1353 CREATION_DATE,
1354 CREATED_BY,
1355 LAST_UPDATE_DATE,
1356 LAST_UPDATED_BY,
1357 LAST_UPDATE_LOGIN,
1358 RECORD_VERSION_NUMBER )
1359 VALUES (
1360 pa_resource_asgn_curr_s.nextval,
1361 p_fp_cols_rec.x_budget_version_id,
1362 l_ra_id_tab(i),
1363 l_txn_currency_code_tab(i),
1364 l_total_quantity_tab(i),
1365 l_total_init_quantity_tab(i),
1366 l_raw_cost_rate_override_tab(i),
1367 l_brdn_cost_rate_override_tab(i),
1368 l_bill_rate_override_tab(i),
1369 l_avg_raw_cost_rate_tab(i),
1370 l_avg_burden_cost_rate_tab(i),
1371 l_avg_bill_rate_tab(i),
1372 l_etc_raw_cost_rate_tab(i),
1373 l_etc_burden_cost_rate_tab(i),
1374 l_etc_bill_rate_tab(i),
1375 l_txn_raw_cost_tab(i),
1376 l_txn_burdened_cost_tab(i),
1377 l_txn_revenue_tab(i),
1378 l_txn_init_raw_cost_tab(i),
1379 l_txn_init_burdened_cost_tab(i),
1380 l_txn_init_revenue_tab(i),
1381 l_pc_raw_cost_tab(i),
1382 l_pc_burdened_cost_tab(i),
1383 l_pc_revenue_tab(i),
1384 l_pc_init_raw_cost_tab(i),
1385 l_pc_init_burdened_cost_tab(i),
1386 l_pc_init_revenue_tab(i),
1387 l_pfc_raw_cost_tab(i),
1388 l_pfc_burdened_cost_tab(i),
1389 l_pfc_revenue_tab(i),
1390 l_pfc_init_raw_cost_tab(i),
1391 l_pfc_init_burdened_cost_tab(i),
1392 l_pfc_init_revenue_tab(i),
1393 l_display_quantity_tab(i),
1394 l_sysdate,
1395 l_last_updated_by,
1396 l_sysdate,
1397 l_last_updated_by,
1398 l_last_update_login,
1399 l_record_version_number );
1400 ELSIF p_copy_mode = G_COPY_OVERRIDES THEN
1401 FORALL i IN 1..l_ra_id_tab.count
1402 INSERT INTO PA_RESOURCE_ASGN_CURR (
1403 RA_TXN_ID,
1404 BUDGET_VERSION_ID,
1405 RESOURCE_ASSIGNMENT_ID,
1406 TXN_CURRENCY_CODE,
1407 TXN_RAW_COST_RATE_OVERRIDE,
1408 TXN_BURDEN_COST_RATE_OVERRIDE,
1409 TXN_BILL_RATE_OVERRIDE,
1410 CREATION_DATE,
1411 CREATED_BY,
1412 LAST_UPDATE_DATE,
1413 LAST_UPDATED_BY,
1414 LAST_UPDATE_LOGIN,
1415 RECORD_VERSION_NUMBER )
1416 VALUES (
1417 pa_resource_asgn_curr_s.nextval,
1418 p_fp_cols_rec.x_budget_version_id,
1419 l_ra_id_tab(i),
1420 l_txn_currency_code_tab(i),
1421 l_raw_cost_rate_override_tab(i),
1422 l_brdn_cost_rate_override_tab(i),
1423 l_bill_rate_override_tab(i),
1424 l_sysdate,
1425 l_last_updated_by,
1426 l_sysdate,
1427 l_last_updated_by,
1428 l_last_update_login,
1429 l_record_version_number );
1430 END IF; -- p_copy_mode check
1431
1432 --dbms_output.put_line('Reached Copy Records');
1433
1434 IF P_PA_DEBUG_MODE = 'Y' THEN
1435 PA_DEBUG.RESET_CURR_FUNCTION;
1436 END IF;
1437 EXCEPTION
1438 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1439
1440 l_msg_count := FND_MSG_PUB.count_msg;
1441 IF l_msg_count = 1 THEN
1442 PA_INTERFACE_UTILS_PUB.get_messages
1443 ( p_encoded => FND_API.G_TRUE,
1444 p_msg_index => 1,
1445 p_msg_count => l_msg_count,
1446 p_msg_data => l_msg_data,
1447 p_data => l_data,
1448 p_msg_index_out => l_msg_index_out);
1449 x_msg_data := l_data;
1450 x_msg_count := l_msg_count;
1451 ELSE
1452 x_msg_count := l_msg_count;
1453 END IF;
1454
1455 -- Removed ROLLBACK statement.
1456
1457 x_return_status := FND_API.G_RET_STS_ERROR;
1458
1459 IF P_PA_DEBUG_MODE = 'Y' THEN
1460 pa_fp_gen_amount_utils.fp_debug
1461 ( p_msg => 'Invalid Arguments Passed',
1462 p_called_mode => p_called_mode,
1463 p_module_name => l_module_name,
1464 p_log_level => l_log_level );
1465 PA_DEBUG.RESET_CURR_FUNCTION;
1466 END IF;
1467 -- Removed RAISE statement.
1468
1469 WHEN OTHERS THEN
1470 -- Removed ROLLBACK statement.
1471 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1472 x_msg_count := 1;
1473 x_msg_data := substr(sqlerrm,1,240);
1474 -- dbms_output.put_line('error msg :'||x_msg_data);
1475 FND_MSG_PUB.add_exc_msg
1476 ( p_pkg_name => 'PA_RES_ASG_CURRENCY_PUB',
1477 p_procedure_name => 'COPY_TABLE_RECORDS',
1478 p_error_text => substr(sqlerrm,1,240));
1479 IF P_PA_DEBUG_MODE = 'Y' THEN
1480 pa_fp_gen_amount_utils.fp_debug
1481 ( p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1482 p_called_mode => p_called_mode,
1483 p_module_name => l_module_name,
1484 p_log_level => l_log_level);
1485 PA_DEBUG.RESET_CURR_FUNCTION;
1486 END IF;
1487 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1488
1489 END COPY_TABLE_RECORDS;
1490
1491
1492 PROCEDURE INSERT_TABLE_RECORDS
1493 ( P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
1494 P_CALLING_MODULE IN VARCHAR2,
1495 P_VERSION_LEVEL_FLAG IN VARCHAR2,
1496 P_CALLED_MODE IN VARCHAR2,
1497 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1498 X_MSG_COUNT OUT NOCOPY NUMBER,
1499 X_MSG_DATA OUT NOCOPY VARCHAR2)
1500 IS
1501 l_module_name VARCHAR2(100) := 'pa.plsql.PA_RES_ASG_CURRENCY_PUB.INSERT_TABLE_RECORDS';
1502 l_log_level NUMBER := 5;
1503
1504 l_msg_count NUMBER;
1505 l_data VARCHAR2(2000);
1506 l_msg_data VARCHAR2(2000);
1507 l_msg_index_out NUMBER;
1508
1509 l_last_updated_by NUMBER := FND_GLOBAL.user_id;
1510 l_last_update_login NUMBER := FND_GLOBAL.login_id;
1511 l_sysdate DATE := SYSDATE;
1512 l_record_version_number NUMBER := 1;
1513
1514 l_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1515 l_txn_currency_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
1516 l_rc_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
1517 l_bc_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
1518 l_bill_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
1519
1520 BEGIN
1521
1522 IF p_pa_debug_mode = 'Y' THEN
1523 PA_DEBUG.SET_CURR_FUNCTION
1524 ( p_function => 'INSERT_TABLE_RECORDS',
1525 p_debug_mode => p_pa_debug_mode );
1526 END IF;
1527
1528 PA_RES_ASG_CURRENCY_PUB.PRINT_INPUT_PARAMS
1529 ( P_CALLING_API => G_PVT_INSERT,
1530 P_FP_COLS_REC => p_fp_cols_rec,
1531 P_CALLING_MODULE => p_calling_module,
1532 P_VERSION_LEVEL_FLAG => p_version_level_flag,
1533 P_CALLED_MODE => p_called_mode );
1534
1535 x_return_status := FND_API.G_RET_STS_SUCCESS;
1536 x_msg_count := 0;
1537
1538
1539 IF p_version_level_flag = 'Y' THEN
1540
1541 -- VERSION LEVEL Mode:
1542 -- Insert records into PA_RESOURCE_ASGN_CURR for all planning
1543 -- resource + currency combinations for the given version that do
1544 -- not already exist in the table.
1545
1546 SELECT DISTINCT
1547 bl.resource_assignment_id,
1548 bl.txn_currency_code
1549 BULK COLLECT
1550 INTO l_ra_id_tab,
1551 l_txn_currency_code_tab
1552 FROM pa_budget_lines bl,
1553 pa_resource_assignments ra
1554 WHERE ra.budget_version_id = p_fp_cols_rec.x_budget_version_id
1555 AND bl.resource_assignment_id = ra.resource_assignment_id
1556 AND NOT EXISTS (SELECT null
1557 FROM pa_resource_asgn_curr rbc
1558 WHERE rbc.resource_assignment_id = bl.resource_assignment_id
1559 AND rbc.txn_currency_code = bl.txn_currency_code);
1560
1561 -- Insert records with values for the following columns:
1562 -- (ra_txn_id, budget_version_id, resource_assignment_id, txn_currency_code).
1563 -- All of the remaining columns, including the rate overrides,
1564 -- but excluding the who columns, will be Null in this case.
1565
1566 FORALL i IN 1..l_ra_id_tab.count
1567 INSERT INTO pa_resource_asgn_curr (
1568 RA_TXN_ID,
1569 BUDGET_VERSION_ID,
1570 RESOURCE_ASSIGNMENT_ID,
1571 TXN_CURRENCY_CODE,
1572 CREATION_DATE,
1573 CREATED_BY,
1574 LAST_UPDATE_DATE,
1575 LAST_UPDATED_BY,
1576 LAST_UPDATE_LOGIN,
1577 RECORD_VERSION_NUMBER )
1578 VALUES (
1579 pa_resource_asgn_curr_s.nextval,
1580 p_fp_cols_rec.x_budget_version_id,
1581 l_ra_id_tab(i),
1582 l_txn_currency_code_tab(i),
1583 l_sysdate,
1584 l_last_updated_by,
1585 l_sysdate,
1586 l_last_updated_by,
1587 l_last_update_login,
1588 l_record_version_number );
1589
1590 ELSIF p_version_level_flag = 'N' THEN
1591
1592 -- TEMP TABLE Mode:
1593 -- Delete and then Insert the records specified by the temp table
1594 -- into the PA_RESOURCE_ASGN_CURR table.
1595
1596 IF p_pa_debug_mode = 'Y' THEN
1597 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1598 ( P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.'
1599 || 'DELETE_TABLE_RECORDS',
1600 P_CALLED_MODE => p_called_mode,
1601 P_MODULE_NAME => l_module_name,
1602 P_LOG_LEVEL => l_log_level );
1603 END IF;
1604 PA_RES_ASG_CURRENCY_PUB.DELETE_TABLE_RECORDS
1605 ( P_FP_COLS_REC => p_fp_cols_rec,
1606 P_CALLING_MODULE => G_PVT_INSERT,
1607 P_VERSION_LEVEL_FLAG => p_version_level_flag,
1608 P_CALLED_MODE => p_called_mode,
1609 X_RETURN_STATUS => x_return_status,
1610 X_MSG_COUNT => x_msg_count,
1611 X_MSG_DATA => x_msg_data );
1612 IF p_pa_debug_mode = 'Y' THEN
1613 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1614 ( P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.'
1615 || 'DELETE_TABLE_RECORDS: ' || x_return_status,
1616 P_CALLED_MODE => p_called_mode,
1617 P_MODULE_NAME => l_module_name,
1618 P_LOG_LEVEL => l_log_level );
1619 END IF;
1620 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1621 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1622 END IF;
1623
1624 INSERT INTO pa_resource_asgn_curr
1625 ( RA_TXN_ID,
1626 BUDGET_VERSION_ID,
1627 RESOURCE_ASSIGNMENT_ID,
1628 TXN_CURRENCY_CODE,
1629 TXN_RAW_COST_RATE_OVERRIDE,
1630 TXN_BURDEN_COST_RATE_OVERRIDE,
1631 TXN_BILL_RATE_OVERRIDE,
1632 CREATION_DATE,
1633 CREATED_BY,
1634 LAST_UPDATE_DATE,
1635 LAST_UPDATED_BY,
1636 LAST_UPDATE_LOGIN,
1637 RECORD_VERSION_NUMBER )
1638 SELECT pa_resource_asgn_curr_s.nextval,
1639 p_fp_cols_rec.x_budget_version_id,
1640 tmp.resource_assignment_id,
1641 tmp.txn_currency_code,
1642 tmp.txn_raw_cost_rate_override,
1643 tmp.txn_burden_cost_rate_override,
1644 tmp.txn_bill_rate_override,
1645 l_sysdate,
1646 l_last_updated_by,
1647 l_sysdate,
1648 l_last_updated_by,
1649 l_last_update_login,
1650 l_record_version_number
1651 FROM pa_resource_asgn_curr_tmp tmp;
1652
1653 END IF; -- p_version_level_flag check
1654
1655 --dbms_output.put_line('Reached Insert Records');
1656
1657 IF P_PA_DEBUG_MODE = 'Y' THEN
1658 PA_DEBUG.RESET_CURR_FUNCTION;
1659 END IF;
1660 EXCEPTION
1661 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1662
1663 l_msg_count := FND_MSG_PUB.count_msg;
1664 IF l_msg_count = 1 THEN
1665 PA_INTERFACE_UTILS_PUB.get_messages
1666 ( p_encoded => FND_API.G_TRUE,
1667 p_msg_index => 1,
1668 p_msg_count => l_msg_count,
1669 p_msg_data => l_msg_data,
1670 p_data => l_data,
1671 p_msg_index_out => l_msg_index_out);
1672 x_msg_data := l_data;
1673 x_msg_count := l_msg_count;
1674 ELSE
1675 x_msg_count := l_msg_count;
1676 END IF;
1677
1678 -- Removed ROLLBACK statement.
1679
1680 x_return_status := FND_API.G_RET_STS_ERROR;
1681
1682 IF P_PA_DEBUG_MODE = 'Y' THEN
1683 pa_fp_gen_amount_utils.fp_debug
1684 ( p_msg => 'Invalid Arguments Passed',
1685 p_called_mode => p_called_mode,
1686 p_module_name => l_module_name,
1687 p_log_level => l_log_level );
1688 PA_DEBUG.RESET_CURR_FUNCTION;
1689 END IF;
1690 -- Removed RAISE statement.
1691
1692 WHEN OTHERS THEN
1693 -- Removed ROLLBACK statement.
1694 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1695 x_msg_count := 1;
1696 x_msg_data := substr(sqlerrm,1,240);
1697 -- dbms_output.put_line('error msg :'||x_msg_data);
1698 FND_MSG_PUB.add_exc_msg
1699 ( p_pkg_name => 'PA_RES_ASG_CURRENCY_PUB',
1700 p_procedure_name => 'INSERT_TABLE_RECORDS',
1701 p_error_text => substr(sqlerrm,1,240));
1702 IF P_PA_DEBUG_MODE = 'Y' THEN
1703 pa_fp_gen_amount_utils.fp_debug
1704 ( p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1705 p_called_mode => p_called_mode,
1706 p_module_name => l_module_name,
1707 p_log_level => l_log_level);
1708 PA_DEBUG.RESET_CURR_FUNCTION;
1709 END IF;
1710 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1711
1712 END INSERT_TABLE_RECORDS;
1713
1714
1715 PROCEDURE ROLLUP_AMOUNTS
1716 ( P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
1717 P_CALLING_MODULE IN VARCHAR2,
1718 P_VERSION_LEVEL_FLAG IN VARCHAR2,
1719 P_CALLED_MODE IN VARCHAR2,
1720 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1721 X_MSG_COUNT OUT NOCOPY NUMBER,
1722 X_MSG_DATA OUT NOCOPY VARCHAR2)
1723 IS
1724 l_module_name VARCHAR2(100) := 'pa.plsql.PA_RES_ASG_CURRENCY_PUB.ROLLUP_AMOUNTS';
1725 l_log_level NUMBER := 5;
1726
1727 l_msg_count NUMBER;
1728 l_data VARCHAR2(2000);
1729 l_msg_data VARCHAR2(2000);
1730 l_msg_index_out NUMBER;
1731
1732 -- This cursor computes ETC rates, average rates, and amount totals
1733 -- for the entire version specified by c_budget_version_id.
1734 -- This cursor should be used when p_version_level_flag is 'Y'.
1735 -- The cursor consists of two halves, connected by UNION ALL.
1736 -- The first half gets data for resources that have budget lines,
1737 -- but may or may not have records in PA_RESOURCE_ASGN_CURR.
1738 -- The second half gets data for resources that have records in
1739 -- PA_RESOURCE_ASGN_CURR but no budget lines.
1740
1741 CURSOR version_level_rollup_csr
1742 ( c_project_id PA_RESOURCE_ASSIGNMENTS.PROJECT_ID%TYPE,
1743 c_budget_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE ) IS
1744 SELECT bl.resource_assignment_id, --RESOURCE_ASSIGNMENT_ID
1745 bl.txn_currency_code, --TXN_CURRENCY_CODE
1746 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
1747 decode(sum(nvl(bl.init_quantity,0)),
1748 0,null,sum(nvl(bl.init_quantity,0))), --TOTAL_INIT_QUANTITY
1749 rbc.txn_raw_cost_rate_override, --TXN_RAW_COST_RATE_OVERRIDE
1750 rbc.txn_burden_cost_rate_override, --TXN_BURDEN_COST_RATE_OVERRIDE
1751 rbc.txn_bill_rate_override, --TXN_BILL_RATE_OVERRIDE
1752 /* bug fix 5726773 : modified Avg/ETC rate calculation logic to check
1753 * for rejection codes instead of relying on override/standard rates */
1754 ( sum(decode(cost_rejection_code
1755 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1756 * nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)))
1757 + nvl(bl.txn_init_raw_cost,0)
1758 ,null))
1759 / DECODE(sum(decode(cost_rejection_code,null,nvl(bl.quantity,0),null))
1760 ,0,NULL
1761 ,sum(decode(cost_rejection_code,null,nvl(bl.quantity,0),null)))
1762 ) avg_cost_rate, --TXN_AVERAGE_RAW_COST_RATE
1763 ( sum(decode(burden_rejection_code
1764 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1765 * nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)))
1766 + nvl(bl.txn_init_burdened_cost,0)
1767 ,null))
1768 / DECODE(sum(decode(burden_rejection_code,null,nvl(bl.quantity,0),null))
1769 ,0,NULL
1770 ,sum(decode(burden_rejection_code,null,nvl(bl.quantity,0),null)))
1771 ) avg_burden_rate, --TXN_AVERAGE_BURDEN_COST_RATE
1772 ( sum(decode(revenue_rejection_code
1773 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1774 * nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)))
1775 + nvl(bl.txn_init_revenue,0)
1776 ,null))
1777 / DECODE(sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0),null))
1778 ,0,NULL
1779 ,sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0),null)))
1780 ) avg_bill_rate, --TXN_AVERAGE_BILL_RATE
1781 ( sum(decode(cost_rejection_code
1782 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1783 * nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)))
1784 ,null))
1785 / DECODE(sum(decode(cost_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null))
1786 ,0,NULL
1787 ,sum(decode(cost_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null)))
1788 ) etc_cost_rate, --TXN_ETC_RAW_COST_RATE
1789 ( sum(decode(burden_rejection_code
1790 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1791 * nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)))
1792 ,null))
1793 / DECODE(sum(decode(burden_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null))
1794 ,0,NULL
1795 ,sum(decode(burden_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null)))
1796 ) etc_burden_rate, --TXN_ETC_BURDEN_COST_RATE
1797 ( sum(decode(revenue_rejection_code
1798 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1799 * nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)))
1800 ,null))
1801 / DECODE(sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null))
1802 ,0,NULL
1803 ,sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null)))
1804 ) etc_bill_rate, --TXN_ETC_BILL_RATE
1805 /* end bug fix 5726773 */
1806 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
1807 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
1808 /* decode(sum(nvl(bl.txn_revenue,0)),
1809 0,null,sum(nvl(bl.txn_revenue,0))) */
1810 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
1811 decode(sum(nvl(bl.txn_init_raw_cost,0)),
1812 0,null,sum(nvl(bl.txn_init_raw_cost,0))), --TOTAL_TXN_INIT_RAW_COST
1813 decode(sum(nvl(bl.txn_init_burdened_cost,0)),
1814 0,null,sum(nvl(bl.txn_init_burdened_cost,0))), --TOTAL_TXN_INIT_BURDENED_COST
1815 decode(sum(nvl(bl.txn_init_revenue,0)),
1816 0,null,sum(nvl(bl.txn_init_revenue,0))), --TOTAL_TXN_INIT_REVENUE
1817 decode(sum(nvl(bl.project_raw_cost,0)),
1818 0,null,sum(nvl(bl.project_raw_cost,0))), --TOTAL_PROJECT_RAW_COST
1819 decode(sum(nvl(bl.project_burdened_cost,0)),
1820 0,null,sum(nvl(bl.project_burdened_cost,0))), --TOTAL_PROJECT_BURDENED_COST
1821 decode(sum(nvl(bl.project_revenue,0)),
1822 0,null,sum(nvl(bl.project_revenue,0))), --TOTAL_PROJECT_REVENUE
1823 decode(sum(nvl(bl.project_init_raw_cost,0)),
1824 0,null,sum(nvl(bl.project_init_raw_cost,0))), --TOTAL_PROJECT_INIT_RAW_COST
1825 decode(sum(nvl(bl.project_init_burdened_cost,0)),
1826 0,null,sum(nvl(bl.project_init_burdened_cost,0))), --TOTAL_PROJECT_INIT_BD_COST
1827 decode(sum(nvl(bl.project_init_revenue,0)),
1828 0,null,sum(nvl(bl.project_init_revenue,0))), --TOTAL_PROJECT_INIT_REVENUE
1829 decode(sum(nvl(bl.raw_cost,0)),
1830 0,null,sum(nvl(bl.raw_cost,0))), --TOTAL_PROJFUNC_RAW_COST
1831 decode(sum(nvl(bl.burdened_cost,0)),
1832 0,null,sum(nvl(bl.burdened_cost,0))), --TOTAL_PROJFUNC_BURDENED_COST
1833 decode(sum(nvl(bl.revenue,0)),
1834 0,null,sum(nvl(bl.revenue,0))), --TOTAL_PROJFUNC_REVENUE
1835 decode(sum(nvl(bl.init_raw_cost,0)),
1836 0,null,sum(nvl(bl.init_raw_cost,0))), --TOTAL_PROJFUNC_INIT_RAW_COST
1837 decode(sum(nvl(bl.init_burdened_cost,0)),
1838 0,null,sum(nvl(bl.init_burdened_cost,0))), --TOTAL_PROJFUNC_INIT_BD_COST
1839 decode(sum(nvl(bl.init_revenue,0)),
1840 0,null,sum(nvl(bl.init_revenue,0))), --TOTAL_PROJFUNC_INIT_REVENUE
1841 decode(sum(nvl(bl.display_quantity,0)),
1842 0,null,sum(nvl(bl.display_quantity,0))) --TOTAL_DISPLAY_QUANTITY
1843 FROM pa_resource_assignments ra,
1844 pa_budget_lines bl,
1845 pa_resource_asgn_curr rbc
1846 WHERE bl.resource_assignment_id = rbc.resource_assignment_id (+)
1847 AND bl.txn_currency_code = rbc.txn_currency_code (+)
1848 AND ra.budget_version_id = c_budget_version_id
1849 AND ra.project_id = c_project_id
1850 AND bl.resource_assignment_id = ra.resource_assignment_id
1851 GROUP BY bl.resource_assignment_id,
1852 bl.txn_currency_code,
1853 rbc.txn_raw_cost_rate_override,
1854 rbc.txn_burden_cost_rate_override,
1855 rbc.txn_bill_rate_override
1856 UNION ALL
1857 SELECT rbc.resource_assignment_id, --RESOURCE_ASSIGNMENT_ID
1858 rbc.txn_currency_code, --TXN_CURRENCY_CODE
1859 null, --TOTAL_QUANTITY
1860 null, --TOTAL_INIT_QUANTITY
1861 rbc.txn_raw_cost_rate_override, --TXN_RAW_COST_RATE_OVERRIDE
1862 rbc.txn_burden_cost_rate_override, --TXN_BURDEN_COST_RATE_OVERRIDE
1863 rbc.txn_bill_rate_override, --TXN_BILL_RATE_OVERRIDE
1864 null, --TXN_AVERAGE_RAW_COST_RATE
1865 null, --TXN_AVERAGE_BURDEN_COST_RATE
1866 null, --TXN_AVERAGE_BILL_RATE
1867 null, --TXN_ETC_RAW_COST_RATE
1868 null, --TXN_ETC_BURDEN_COST_RATE
1869 null, --TXN_ETC_BILL_RATE
1870 null, --TOTAL_TXN_RAW_COST
1871 null, --TOTAL_TXN_BURDENED_COST
1872 null, --TOTAL_TXN_REVENUE
1873 null, --TOTAL_TXN_INIT_RAW_COST
1874 null, --TOTAL_TXN_INIT_BURDENED_COST
1875 null, --TOTAL_TXN_INIT_REVENUE
1876 null, --TOTAL_PROJECT_RAW_COST
1877 null, --TOTAL_PROJECT_BURDENED_COST
1878 null, --TOTAL_PROJECT_REVENUE
1879 null, --TOTAL_PROJECT_INIT_RAW_COST
1880 null, --TOTAL_PROJECT_INIT_BD_COST
1881 null, --TOTAL_PROJECT_INIT_REVENUE
1882 null, --TOTAL_PROJFUNC_RAW_COST
1883 null, --TOTAL_PROJFUNC_BURDENED_COST
1884 null, --TOTAL_PROJFUNC_REVENUE
1885 null, --TOTAL_PROJFUNC_INIT_RAW_COST
1886 null, --TOTAL_PROJFUNC_INIT_BD_COST
1887 null, --TOTAL_PROJFUNC_INIT_REVENUE
1888 null --TOTAL_DISPLAY_QUANTITY
1889 FROM pa_resource_asgn_curr rbc
1890 WHERE rbc.budget_version_id = c_budget_version_id
1891 AND NOT EXISTS (SELECT null
1892 FROM pa_budget_lines bl
1893 WHERE bl.resource_assignment_id = rbc.resource_assignment_id
1894 AND bl.txn_currency_code = rbc.txn_currency_code );
1895
1896
1897 -- This cursor computes ETC rates, average rates, and amount totals
1898 -- for the resources specified in PA_RESOURCE_ASGN_CURR_TMP.
1899 -- This cursor should be used when p_version_level_flag is 'N'.
1900 -- The cursor gets data for resources that have records in
1901 -- PA_RESOURCE_ASGN_CURR but may or may not have budget lines.
1902 -- NOTE: the cursor parameters c_project_id and c_budget_version_id
1903 -- are not necessary here, but included in case of future use.
1904
1905 CURSOR table_level_rollup_csr
1906 ( c_project_id PA_RESOURCE_ASSIGNMENTS.PROJECT_ID%TYPE,
1907 c_budget_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE ) IS
1908 SELECT rbc.resource_assignment_id, --RESOURCE_ASSIGNMENT_ID
1909 rbc.txn_currency_code, --TXN_CURRENCY_CODE
1910 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
1911 decode(sum(nvl(bl.init_quantity,0)),
1912 0,null,sum(nvl(bl.init_quantity,0))), --TOTAL_INIT_QUANTITY
1913 rbc.txn_raw_cost_rate_override, --TXN_RAW_COST_RATE_OVERRIDE
1914 rbc.txn_burden_cost_rate_override, --TXN_BURDEN_COST_RATE_OVERRIDE
1915 rbc.txn_bill_rate_override, --TXN_BILL_RATE_OVERRIDE
1916 /* bug fix 5726773 : modified Avg/ETC rate calculation logic to check
1917 * for rejection codes instead of relying on override/standard rates */
1918 ( sum(decode(cost_rejection_code
1919 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1920 * nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)))
1921 + nvl(bl.txn_init_raw_cost,0)
1922 ,null))
1923 / DECODE(sum(decode(cost_rejection_code,null,nvl(bl.quantity,0),null))
1924 ,0,NULL
1925 ,sum(decode(cost_rejection_code,null,nvl(bl.quantity,0),null)))
1926 ) avg_cost_rate, --TXN_AVERAGE_RAW_COST_RATE
1927 ( sum(decode(burden_rejection_code
1928 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1929 * nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)))
1930 + nvl(bl.txn_init_burdened_cost,0)
1931 ,null))
1932 / DECODE(sum(decode(burden_rejection_code,null,nvl(bl.quantity,0),null))
1933 ,0,NULL
1934 ,sum(decode(burden_rejection_code,null,nvl(bl.quantity,0),null)))
1935 ) avg_burden_rate, --TXN_AVERAGE_BURDEN_COST_RATE
1936 ( sum(decode(revenue_rejection_code
1937 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1938 * nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)))
1939 + nvl(bl.txn_init_revenue,0)
1940 ,null))
1941 / DECODE(sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0),null))
1942 ,0,NULL
1943 ,sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0),null)))
1944 ) avg_bill_rate, --TXN_AVERAGE_BILL_RATE
1945 ( sum(decode(cost_rejection_code
1946 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1947 * nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)))
1948 ,null))
1949 / DECODE(sum(decode(cost_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null))
1950 ,0,NULL
1951 ,sum(decode(cost_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null)))
1952 ) etc_cost_rate, --TXN_ETC_RAW_COST_RATE
1953 ( sum(decode(burden_rejection_code
1954 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1955 * nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)))
1956 ,null))
1957 / DECODE(sum(decode(burden_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null))
1958 ,0,NULL
1959 ,sum(decode(burden_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null)))
1960 ) etc_burden_rate, --TXN_ETC_BURDEN_COST_RATE
1961 ( sum(decode(revenue_rejection_code
1962 ,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
1963 * nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)))
1964 ,null))
1965 / DECODE(sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null))
1966 ,0,NULL
1967 ,sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null)))
1968 ) etc_bill_rate, --TXN_ETC_BILL_RATE
1969 /* end bug fix 5726773 */
1970 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
1971 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
1972 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
1973 decode(sum(nvl(bl.txn_init_raw_cost,0)),
1974 0,null,sum(nvl(bl.txn_init_raw_cost,0))), --TOTAL_TXN_INIT_RAW_COST
1975 decode(sum(nvl(bl.txn_init_burdened_cost,0)),
1976 0,null,sum(nvl(bl.txn_init_burdened_cost,0))), --TOTAL_TXN_INIT_BURDENED_COST
1977 decode(sum(nvl(bl.txn_init_revenue,0)),
1978 0,null,sum(nvl(bl.txn_init_revenue,0))), --TOTAL_TXN_INIT_REVENUE
1979 decode(sum(nvl(bl.project_raw_cost,0)),
1980 0,null,sum(nvl(bl.project_raw_cost,0))), --TOTAL_PROJECT_RAW_COST
1981 decode(sum(nvl(bl.project_burdened_cost,0)),
1982 0,null,sum(nvl(bl.project_burdened_cost,0))), --TOTAL_PROJECT_BURDENED_COST
1983 decode(sum(nvl(bl.project_revenue,0)),
1984 0,null,sum(nvl(bl.project_revenue,0))), --TOTAL_PROJECT_REVENUE
1985 decode(sum(nvl(bl.project_init_raw_cost,0)),
1986 0,null,sum(nvl(bl.project_init_raw_cost,0))), --TOTAL_PROJECT_INIT_RAW_COST
1987 decode(sum(nvl(bl.project_init_burdened_cost,0)),
1988 0,null,sum(nvl(bl.project_init_burdened_cost,0))), --TOTAL_PROJECT_INIT_BD_COST
1989 decode(sum(nvl(bl.project_init_revenue,0)),
1990 0,null,sum(nvl(bl.project_init_revenue,0))), --TOTAL_PROJECT_INIT_REVENUE
1991 decode(sum(nvl(bl.raw_cost,0)),
1992 0,null,sum(nvl(bl.raw_cost,0))), --TOTAL_PROJFUNC_RAW_COST
1993 decode(sum(nvl(bl.burdened_cost,0)),
1994 0,null,sum(nvl(bl.burdened_cost,0))), --TOTAL_PROJFUNC_BURDENED_COST
1995 decode(sum(nvl(bl.revenue,0)),
1996 0,null,sum(nvl(bl.revenue,0))), --TOTAL_PROJFUNC_REVENUE
1997 decode(sum(nvl(bl.init_raw_cost,0)),
1998 0,null,sum(nvl(bl.init_raw_cost,0))), --TOTAL_PROJFUNC_INIT_RAW_COST
1999 decode(sum(nvl(bl.init_burdened_cost,0)),
2000 0,null,sum(nvl(bl.init_burdened_cost,0))), --TOTAL_PROJFUNC_INIT_BD_COST
2001 decode(sum(nvl(bl.init_revenue,0)),
2002 0,null,sum(nvl(bl.init_revenue,0))), --TOTAL_PROJFUNC_INIT_REVENUE
2003 decode(sum(nvl(bl.display_quantity,0)),
2004 0,null,sum(nvl(bl.display_quantity,0))) --TOTAL_DISPLAY_QUANTITY
2005 FROM pa_budget_lines bl,
2006 pa_resource_asgn_curr_TMP rbc
2007 WHERE bl.resource_assignment_id (+) = rbc.resource_assignment_id
2008 AND bl.txn_currency_code (+) = rbc.txn_currency_code
2009 GROUP BY rbc.resource_assignment_id,
2010 rbc.txn_currency_code,
2011 rbc.txn_raw_cost_rate_override,
2012 rbc.txn_burden_cost_rate_override,
2013 rbc.txn_bill_rate_override;
2014
2015 l_last_updated_by NUMBER := FND_GLOBAL.user_id;
2016 l_last_update_login NUMBER := FND_GLOBAL.login_id;
2017 l_sysdate DATE := SYSDATE;
2018 l_record_version_number NUMBER := 1;
2019
2020 -- PL/SQL tables for storing computed rates and rolled up amounts
2021 l_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2022 l_txn_currency_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
2023 l_total_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
2024 l_total_init_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
2025 l_raw_cost_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
2026 l_brdn_cost_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
2027 l_bill_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
2028 l_avg_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2029 l_avg_burden_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2030 l_avg_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2031 l_etc_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2032 l_etc_burden_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2033 l_etc_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2034 l_txn_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2035 l_txn_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2036 l_txn_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2037 l_txn_init_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2038 l_txn_init_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2039 l_txn_init_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2040 l_pc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2041 l_pc_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2042 l_pc_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2043 l_pc_init_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2044 l_pc_init_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2045 l_pc_init_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2046 l_pfc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2047 l_pfc_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2048 l_pfc_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2049 l_pfc_init_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2050 l_pfc_init_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2051 l_pfc_init_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2052 l_display_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
2053
2054 l_NULL_NumTabTyp PA_PLSQL_DATATYPES.NumTabTyp;
2055
2056 -- Indicates if the Target version is a Workplan.
2057 l_wp_version_flag PA_BUDGET_VERSIONS.WP_VERSION_FLAG%TYPE;
2058
2059 BEGIN
2060
2061 IF p_pa_debug_mode = 'Y' THEN
2062 PA_DEBUG.SET_CURR_FUNCTION
2063 ( p_function => 'ROLLUP_AMOUNTS',
2064 p_debug_mode => p_pa_debug_mode );
2065 END IF;
2066
2067 PA_RES_ASG_CURRENCY_PUB.PRINT_INPUT_PARAMS
2068 ( P_CALLING_API => G_PVT_ROLLUP,
2069 P_FP_COLS_REC => p_fp_cols_rec,
2070 P_CALLING_MODULE => p_calling_module,
2071 P_VERSION_LEVEL_FLAG => p_version_level_flag,
2072 P_CALLED_MODE => p_called_mode );
2073
2074 x_return_status := FND_API.G_RET_STS_SUCCESS;
2075 x_msg_count := 0;
2076
2077 BEGIN
2078 SELECT nvl(wp_version_flag,'N')
2079 INTO l_wp_version_flag
2080 FROM pa_budget_versions
2081 WHERE budget_version_id = p_fp_cols_rec.x_budget_version_id;
2082 EXCEPTION WHEN NO_DATA_FOUND THEN
2083 IF p_pa_debug_mode = 'Y' THEN
2084 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
2085 ( P_MSG => 'Invalid p_fp_cols_rec.x_budget_version_id value: '
2086 || p_fp_cols_rec.x_budget_version_id
2087 || '. Budget version does not exist.',
2088 P_CALLED_MODE => p_called_mode,
2089 P_MODULE_NAME => l_module_name,
2090 P_LOG_LEVEL => l_log_level );
2091 END IF;
2092 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2093 END;
2094
2095
2096 -- Step 1: Select rates and amounts from the appropriate cursor.
2097
2098 IF p_version_level_flag = 'Y' THEN
2099 OPEN version_level_rollup_csr
2100 (p_fp_cols_rec.x_project_id,
2101 p_fp_cols_rec.x_budget_version_id);
2102 FETCH version_level_rollup_csr
2103 BULK COLLECT
2104 INTO l_ra_id_tab,
2105 l_txn_currency_code_tab,
2106 l_total_quantity_tab,
2107 l_total_init_quantity_tab,
2108 l_raw_cost_rate_override_tab,
2109 l_brdn_cost_rate_override_tab,
2110 l_bill_rate_override_tab,
2111 l_avg_raw_cost_rate_tab,
2112 l_avg_burden_cost_rate_tab,
2113 l_avg_bill_rate_tab,
2114 l_etc_raw_cost_rate_tab,
2115 l_etc_burden_cost_rate_tab,
2116 l_etc_bill_rate_tab,
2117 l_txn_raw_cost_tab,
2118 l_txn_burdened_cost_tab,
2119 l_txn_revenue_tab,
2120 l_txn_init_raw_cost_tab,
2121 l_txn_init_burdened_cost_tab,
2122 l_txn_init_revenue_tab,
2123 l_pc_raw_cost_tab,
2124 l_pc_burdened_cost_tab,
2125 l_pc_revenue_tab,
2126 l_pc_init_raw_cost_tab,
2127 l_pc_init_burdened_cost_tab,
2128 l_pc_init_revenue_tab,
2129 l_pfc_raw_cost_tab,
2130 l_pfc_burdened_cost_tab,
2131 l_pfc_revenue_tab,
2132 l_pfc_init_raw_cost_tab,
2133 l_pfc_init_burdened_cost_tab,
2134 l_pfc_init_revenue_tab,
2135 l_display_quantity_tab;
2136 CLOSE version_level_rollup_csr;
2137 ELSIF p_version_level_flag = 'N' THEN
2138 OPEN table_level_rollup_csr
2139 (p_fp_cols_rec.x_project_id,
2140 p_fp_cols_rec.x_budget_version_id);
2141 FETCH table_level_rollup_csr
2142 BULK COLLECT
2143 INTO l_ra_id_tab,
2144 l_txn_currency_code_tab,
2145 l_total_quantity_tab,
2146 l_total_init_quantity_tab,
2147 l_raw_cost_rate_override_tab,
2148 l_brdn_cost_rate_override_tab,
2149 l_bill_rate_override_tab,
2150 l_avg_raw_cost_rate_tab,
2151 l_avg_burden_cost_rate_tab,
2152 l_avg_bill_rate_tab,
2153 l_etc_raw_cost_rate_tab,
2154 l_etc_burden_cost_rate_tab,
2155 l_etc_bill_rate_tab,
2156 l_txn_raw_cost_tab,
2157 l_txn_burdened_cost_tab,
2158 l_txn_revenue_tab,
2159 l_txn_init_raw_cost_tab,
2160 l_txn_init_burdened_cost_tab,
2161 l_txn_init_revenue_tab,
2162 l_pc_raw_cost_tab,
2163 l_pc_burdened_cost_tab,
2164 l_pc_revenue_tab,
2165 l_pc_init_raw_cost_tab,
2166 l_pc_init_burdened_cost_tab,
2167 l_pc_init_revenue_tab,
2168 l_pfc_raw_cost_tab,
2169 l_pfc_burdened_cost_tab,
2170 l_pfc_revenue_tab,
2171 l_pfc_init_raw_cost_tab,
2172 l_pfc_init_burdened_cost_tab,
2173 l_pfc_init_revenue_tab,
2174 l_display_quantity_tab;
2175 CLOSE table_level_rollup_csr;
2176 END IF; -- p_version_level_flag check
2177
2178 -- No further processing is required if there are no records to rollup.
2179 IF l_ra_id_tab.count <= 0 THEN
2180 IF P_PA_DEBUG_MODE = 'Y' THEN
2181 PA_DEBUG.RESET_CURR_FUNCTION;
2182 END IF;
2183 RETURN;
2184 END IF;
2185
2186
2187 -- Step 2: Process pl/sql tables as needed.
2188
2189 -- Initialize a pl/sql table of length l_ra_id_tab.count with nulls.
2190 -- We can use this table to null out entire tables during processing.
2191 -- This should perform better than nulling out records in a loop.
2192 l_null_NumTabTyp.delete;
2193 FOR i IN 1..l_ra_id_tab.count LOOP
2194 l_null_NumTabTyp(i) := null;
2195 END LOOP;
2196
2197 -- ETC Rate columns should Null for Budgets,
2198 -- but should be populated for Forecasts and Workplans.
2199 -- Additionally, Actuals columns should be nulled out.
2200 IF l_wp_version_flag = 'N' AND
2201 p_fp_cols_rec.x_plan_class_code = 'BUDGET' THEN
2202 l_etc_raw_cost_rate_tab := l_null_NumTabTyp;
2203 l_etc_burden_cost_rate_tab := l_null_NumTabTyp;
2204 l_etc_bill_rate_tab := l_null_NumTabTyp;
2205 l_total_init_quantity_tab := l_null_NumTabTyp;
2206 l_txn_init_raw_cost_tab := l_null_NumTabTyp;
2207 l_txn_init_burdened_cost_tab := l_null_NumTabTyp;
2208 l_txn_init_revenue_tab := l_null_NumTabTyp;
2209 l_pc_init_raw_cost_tab := l_null_NumTabTyp;
2210 l_pc_init_burdened_cost_tab := l_null_NumTabTyp;
2211 l_pc_init_revenue_tab := l_null_NumTabTyp;
2212 l_pfc_init_raw_cost_tab := l_null_NumTabTyp;
2213 l_pfc_init_burdened_cost_tab := l_null_NumTabTyp;
2214 l_pfc_init_revenue_tab := l_null_NumTabTyp;
2215 END IF; -- ETC Rate column logic
2216
2217 -- Only rates and totals relevant to the version type should be populated.
2218 -- Cost-only versions should not have revenue rates or totals.
2219 IF p_fp_cols_rec.x_version_type = 'COST' THEN
2220 l_bill_rate_override_tab := l_null_NumTabTyp;
2221 l_avg_bill_rate_tab := l_null_NumTabTyp;
2222 l_etc_bill_rate_tab := l_null_NumTabTyp;
2223 l_txn_revenue_tab := l_null_NumTabTyp;
2224 l_txn_init_revenue_tab := l_null_NumTabTyp;
2225 l_pc_revenue_tab := l_null_NumTabTyp;
2226 l_pc_init_revenue_tab := l_null_NumTabTyp;
2227 l_pfc_revenue_tab := l_null_NumTabTyp;
2228 l_pfc_init_revenue_tab := l_null_NumTabTyp;
2229 -- Revenue-only versions should not have cost rates or totals.
2230 ELSIF p_fp_cols_rec.x_version_type = 'REVENUE' THEN
2231 l_raw_cost_rate_override_tab := l_null_NumTabTyp;
2232 l_brdn_cost_rate_override_tab := l_null_NumTabTyp;
2233 l_avg_raw_cost_rate_tab := l_null_NumTabTyp;
2234 l_avg_burden_cost_rate_tab := l_null_NumTabTyp;
2235 l_etc_raw_cost_rate_tab := l_null_NumTabTyp;
2236 l_etc_burden_cost_rate_tab := l_null_NumTabTyp;
2237 l_txn_raw_cost_tab := l_null_NumTabTyp;
2238 l_txn_burdened_cost_tab := l_null_NumTabTyp;
2239 l_txn_init_raw_cost_tab := l_null_NumTabTyp;
2240 l_txn_init_burdened_cost_tab := l_null_NumTabTyp;
2241 l_pc_raw_cost_tab := l_null_NumTabTyp;
2242 l_pc_burdened_cost_tab := l_null_NumTabTyp;
2243 l_pc_init_raw_cost_tab := l_null_NumTabTyp;
2244 l_pc_init_burdened_cost_tab := l_null_NumTabTyp;
2245 l_pfc_raw_cost_tab := l_null_NumTabTyp;
2246 l_pfc_burdened_cost_tab := l_null_NumTabTyp;
2247 l_pfc_init_raw_cost_tab := l_null_NumTabTyp;
2248 l_pfc_init_burdened_cost_tab := l_null_NumTabTyp;
2249 END IF;
2250
2251 -- Step 3: Delete records from the PA_RESOURCE_ASGN_CURR table.
2252
2253 IF p_pa_debug_mode = 'Y' THEN
2254 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
2255 ( P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.'
2256 || 'DELETE_TABLE_RECORDS',
2257 P_CALLED_MODE => p_called_mode,
2258 P_MODULE_NAME => l_module_name,
2259 P_LOG_LEVEL => l_log_level );
2260 END IF;
2261 PA_RES_ASG_CURRENCY_PUB.DELETE_TABLE_RECORDS
2262 ( P_FP_COLS_REC => p_fp_cols_rec,
2263 P_CALLING_MODULE => G_PVT_ROLLUP,
2264 P_VERSION_LEVEL_FLAG => p_version_level_flag,
2265 P_CALLED_MODE => p_called_mode,
2266 X_RETURN_STATUS => x_return_status,
2267 X_MSG_COUNT => x_msg_count,
2268 X_MSG_DATA => x_msg_data );
2269 IF p_pa_debug_mode = 'Y' THEN
2270 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
2271 ( P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.'
2272 || 'DELETE_TABLE_RECORDS: ' || x_return_status,
2273 P_CALLED_MODE => p_called_mode,
2274 P_MODULE_NAME => l_module_name,
2275 P_LOG_LEVEL => l_log_level );
2276 END IF;
2277 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2278 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2279 END IF;
2280
2281 -- Step 4: Insert records into the PA_RESOURCE_ASGN_CURR table.
2282
2283 FORALL i IN 1..l_ra_id_tab.count
2284 INSERT INTO PA_RESOURCE_ASGN_CURR (
2285 RA_TXN_ID,
2286 BUDGET_VERSION_ID,
2287 RESOURCE_ASSIGNMENT_ID,
2288 TXN_CURRENCY_CODE,
2289 TOTAL_QUANTITY,
2290 TOTAL_INIT_QUANTITY,
2291 TXN_RAW_COST_RATE_OVERRIDE,
2292 TXN_BURDEN_COST_RATE_OVERRIDE,
2293 TXN_BILL_RATE_OVERRIDE,
2294 TXN_AVERAGE_RAW_COST_RATE,
2295 TXN_AVERAGE_BURDEN_COST_RATE,
2296 TXN_AVERAGE_BILL_RATE,
2297 TXN_ETC_RAW_COST_RATE,
2298 TXN_ETC_BURDEN_COST_RATE,
2299 TXN_ETC_BILL_RATE,
2300 TOTAL_TXN_RAW_COST,
2301 TOTAL_TXN_BURDENED_COST,
2302 TOTAL_TXN_REVENUE,
2303 TOTAL_TXN_INIT_RAW_COST,
2304 TOTAL_TXN_INIT_BURDENED_COST,
2305 TOTAL_TXN_INIT_REVENUE,
2306 TOTAL_PROJECT_RAW_COST,
2307 TOTAL_PROJECT_BURDENED_COST,
2308 TOTAL_PROJECT_REVENUE,
2309 TOTAL_PROJECT_INIT_RAW_COST,
2310 TOTAL_PROJECT_INIT_BD_COST,
2311 TOTAL_PROJECT_INIT_REVENUE,
2312 TOTAL_PROJFUNC_RAW_COST,
2313 TOTAL_PROJFUNC_BURDENED_COST,
2314 TOTAL_PROJFUNC_REVENUE,
2315 TOTAL_PROJFUNC_INIT_RAW_COST,
2316 TOTAL_PROJFUNC_INIT_BD_COST,
2317 TOTAL_PROJFUNC_INIT_REVENUE,
2318 TOTAL_DISPLAY_QUANTITY,
2319 CREATION_DATE,
2320 CREATED_BY,
2321 LAST_UPDATE_DATE,
2322 LAST_UPDATED_BY,
2323 LAST_UPDATE_LOGIN,
2324 RECORD_VERSION_NUMBER )
2325 VALUES (
2326 pa_resource_asgn_curr_s.nextval,
2327 p_fp_cols_rec.x_budget_version_id,
2328 l_ra_id_tab(i),
2329 l_txn_currency_code_tab(i),
2330 l_total_quantity_tab(i),
2331 l_total_init_quantity_tab(i),
2332 l_raw_cost_rate_override_tab(i),
2333 l_brdn_cost_rate_override_tab(i),
2334 l_bill_rate_override_tab(i),
2335 l_avg_raw_cost_rate_tab(i),
2336 l_avg_burden_cost_rate_tab(i),
2337 l_avg_bill_rate_tab(i),
2338 l_etc_raw_cost_rate_tab(i),
2339 l_etc_burden_cost_rate_tab(i),
2340 l_etc_bill_rate_tab(i),
2341 l_txn_raw_cost_tab(i),
2342 l_txn_burdened_cost_tab(i),
2343 l_txn_revenue_tab(i),
2344 l_txn_init_raw_cost_tab(i),
2345 l_txn_init_burdened_cost_tab(i),
2346 l_txn_init_revenue_tab(i),
2347 l_pc_raw_cost_tab(i),
2348 l_pc_burdened_cost_tab(i),
2349 l_pc_revenue_tab(i),
2350 l_pc_init_raw_cost_tab(i),
2351 l_pc_init_burdened_cost_tab(i),
2352 l_pc_init_revenue_tab(i),
2353 l_pfc_raw_cost_tab(i),
2354 l_pfc_burdened_cost_tab(i),
2355 l_pfc_revenue_tab(i),
2356 l_pfc_init_raw_cost_tab(i),
2357 l_pfc_init_burdened_cost_tab(i),
2358 l_pfc_init_revenue_tab(i),
2359 l_display_quantity_tab(i),
2360 l_sysdate,
2361 l_last_updated_by,
2362 l_sysdate,
2363 l_last_updated_by,
2364 l_last_update_login,
2365 l_record_version_number );
2366
2367 IF P_PA_DEBUG_MODE = 'Y' THEN
2368 PA_DEBUG.RESET_CURR_FUNCTION;
2369 END IF;
2370 EXCEPTION
2371 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2372
2373 l_msg_count := FND_MSG_PUB.count_msg;
2374 IF l_msg_count = 1 THEN
2375 PA_INTERFACE_UTILS_PUB.get_messages
2376 ( p_encoded => FND_API.G_TRUE,
2377 p_msg_index => 1,
2378 p_msg_count => l_msg_count,
2379 p_msg_data => l_msg_data,
2380 p_data => l_data,
2381 p_msg_index_out => l_msg_index_out);
2382 x_msg_data := l_data;
2383 x_msg_count := l_msg_count;
2384 ELSE
2385 x_msg_count := l_msg_count;
2386 END IF;
2387
2388 -- Removed ROLLBACK statement.
2389
2390 x_return_status := FND_API.G_RET_STS_ERROR;
2391
2392 IF P_PA_DEBUG_MODE = 'Y' THEN
2393 pa_fp_gen_amount_utils.fp_debug
2394 ( p_msg => 'Invalid Arguments Passed',
2395 p_called_mode => p_called_mode,
2396 p_module_name => l_module_name,
2397 p_log_level => l_log_level );
2398 PA_DEBUG.RESET_CURR_FUNCTION;
2399 END IF;
2400 -- Removed RAISE statement.
2401
2402 WHEN OTHERS THEN
2403 -- Removed ROLLBACK statement.
2404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2405 x_msg_count := 1;
2406 x_msg_data := substr(sqlerrm,1,240);
2407 -- dbms_output.put_line('error msg :'||x_msg_data);
2408 FND_MSG_PUB.add_exc_msg
2409 ( p_pkg_name => 'PA_RES_ASG_CURRENCY_PUB',
2410 p_procedure_name => 'ROLLUP_AMOUNTS',
2411 p_error_text => substr(sqlerrm,1,240));
2412 IF P_PA_DEBUG_MODE = 'Y' THEN
2413 pa_fp_gen_amount_utils.fp_debug
2414 ( p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
2415 p_called_mode => p_called_mode,
2416 p_module_name => l_module_name,
2417 p_log_level => l_log_level);
2418 PA_DEBUG.RESET_CURR_FUNCTION;
2419 END IF;
2420 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2421
2422 END ROLLUP_AMOUNTS;
2423
2424
2425 END PA_RES_ASG_CURRENCY_PUB;