[Home] [Help]
PACKAGE BODY: APPS.PA_FP_PJI_INTG_PKG
Source
1 PACKAGE BODY PA_FP_PJI_INTG_PKG AS
2 --$Header: PAFPUT4B.pls 120.4.12020000.2 2013/03/08 18:55:56 sachandr ship $
3
4 /* Declare global variables*/
5 g_debug_flag Varchar2(10) ;
6
7 /**
8 procedure calc_log(p_msg varchar2) IS
9
10 pragma autonomous_transaction ;
11 BEGIN
12 --dbms_output.put_line(p_msg);
13 --IF P_PA_DEBUG_MODE = 'Y' Then
14 NULL;
15 INSERT INTO PA_FP_CALCULATE_LOG
16 (SESSIONID
17 ,SEQ_NUMBER
18 ,LOG_MESSAGE)
19 VALUES
20 (userenv('sessionid')
21 ,HR.PAY_US_GARN_FEE_RULES_S.nextval
22 ,substr(P_MSG,1,240)
23 );
24 --END IF;
25 COMMIT;
26
27 end calc_log;
28 **/
29 PROCEDURE PRINT_MSG(p_msg varchar2
30 ,p_debug_flag varchar2 default NULL) IS
31
32 BEGIN
33 --calc_log(p_msg);
34 If (NVL(p_debug_flag,'N') = 'Y' OR g_debug_flag = 'Y') Then
35 pa_debug.g_err_stage := substr('LOG:'||p_msg,1,240);
36 PA_DEBUG.write
37 (x_Module => 'PA_FP_PJI_INTG_PKG'
38 ,x_Msg => pa_debug.g_err_stage
39 ,x_Log_Level => 3);
40 End If;
41 END;
42
43 /* This is the main api called from calculate, budget generation process to update the
44 * reporting PJI data when budget lines are created,updated or deleted.
45 * The following params values must be passed
46 * p_activity_code 'UPDATE',/'DELETE'
47 * p_calling_module name of API, for calculate 'CALCULATE_API'
48 * p_start_date BudgetLine StartDate
49 * p_end_date BudgetLine Enddate
50 * If activity = 'UPDATE' then all the amounts and currency columns must be passed
51 * if activity = 'DELETE' then -ve budgetLine amounts will be selected from DB and passed in params will be ignored
52 * NOTE: BEFORE CALLING THIS API, a record must exists in pa_resource_assignments for the p_resource_assignment_id
53 * AND CALL THIS API ONLY IF THERE ARE NO REJECTION CODES STAMPED ON THE BUDGET LINES
54 * NOTE: As of IPM, we ignore rejection codes stamped on budget lines for the purpose of updating PJI data.
55 */
56 PROCEDURE update_reporting_lines
57 (p_calling_module IN Varchar2 Default 'CALCULATE_API'
58 ,p_activity_code IN Varchar2 Default 'UPDATE'
59 ,p_budget_version_id IN Number
60 ,p_budget_line_id IN Number
61 ,p_resource_assignment_id IN Number
62 ,p_start_date IN Date
63 ,p_end_date IN Date
64 ,p_period_name IN Varchar2
65 ,p_txn_currency_code IN Varchar2
66 ,p_quantity IN Number
67 ,p_txn_raw_cost IN Number
68 ,p_txn_burdened_cost IN Number
69 ,p_txn_revenue IN Number
70 ,p_project_currency_code IN Varchar2
71 ,p_project_raw_cost IN Number
72 ,p_project_burdened_cost IN Number
73 ,p_project_revenue IN Number
74 ,p_projfunc_currency_code IN Varchar2
75 ,p_projfunc_raw_cost IN Number
76 ,p_projfunc_burdened_cost IN Number
77 ,p_projfunc_revenue IN Number
78 ,x_msg_data OUT NOCOPY Varchar2
79 ,x_msg_count OUT NOCOPY Number
80 ,x_return_status OUT NOCOPY Varchar2
81 ) IS
82
83 l_msg_count Number := 0;
84 l_msg_data Varchar2(1000) := Null;
85 l_return_status Varchar2(10);
86 l_debug_flag Varchar2(10);
87 l_project_structure_id Number;
88 PJI_EXCEPTION EXCEPTION;
89
90 CURSOR strVer IS
91 SELECT DECODE(nvl(pbv.wp_version_flag,'N'),'Y',pbv.project_structure_version_id,
92 PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(pbv.project_id)) project_structure_version_id
93 FROM pa_budget_versions pbv
94 WHERE pbv.budget_version_id = p_budget_version_id;
95
96 CURSOR cur_pjiDetails IS
97 SELECT pbv.budget_version_id
98 ,ppa.org_id
99 ,ppfo.rbs_version_id
100 ,pbv.fin_plan_type_id
101 /* Bug fix :3839761 ,nvl(pbv.project_structure_version_id,
102 --PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(pbv.project_id)) project_structure_version_id
103 */
104 ,pbv.wp_version_flag
105 ,decode(pbv.version_type, 'COST',ppfo.cost_time_phased_code,
106 'REVENUE',ppfo.revenue_time_phased_code,
107 ppfo.all_time_phased_code) time_phase_code
108 ,ra.project_id
109 ,ra.task_id
110 ,ra.rbs_element_id
111 ,ra.cbs_element_id --bug#16200605
112 ,ra.resource_class_code
113 ,ra.rate_based_flag
114 FROM pa_projects_all ppa
115 ,pa_budget_versions pbv
116 ,pa_proj_fp_options ppfo
117 ,pa_resource_assignments ra
118 WHERE ppa.project_id = pbv.project_id
119 AND pbv.budget_version_id = ppfo.fin_plan_version_id
120 AND pbv.budget_version_id = p_budget_version_id
121 AND ra.resource_assignment_id = p_resource_assignment_id
122 AND ra.budget_version_id = pbv.budget_version_id;
123
124 pji_rec cur_pjiDetails%ROWTYPE;
125 l_pji_call_flag Varchar2(10);
126 l_start_date Date;
127 l_end_date Date;
128 l_period_name Varchar2(100);
129 l_txn_currency_code Varchar2(100);
130 l_txn_raw_cost Number;
131 l_txn_burdened_cost Number;
132 l_txn_revenue Number;
133 l_project_currency_code Varchar2(100);
134 l_project_raw_cost Number;
135 l_project_burdened_cost Number;
136 l_project_revenue Number;
137 l_projfunc_currency_code Varchar2(100);
138 l_projfunc_raw_cost Number;
139 l_projfunc_burdened_cost Number;
140 l_projfunc_revenue Number;
141 l_quantity Number;
142 l_budget_line_id Number;
143 l_num_rows_inserted Number;
144 INVALID_PARAMS EXCEPTION;
145 l_stage Varchar2(100);
146
147
148 BEGIN
149 l_return_status := 'S';
150 x_return_status := 'S';
151 x_msg_count := 0;
152 x_msg_data := Null;
153
154 l_stage := 10;
155 /* Initialize the error stack */
156 l_debug_flag := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
157 g_debug_flag := l_debug_flag;
158 If g_debug_flag = 'Y' Then
159 pa_debug.init_err_stack('PA_FP_PJI_INTG_PKG.update_reporting_lines');
160 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
161 ,x_write_file => 'LOG'
162 ,x_debug_mode => l_debug_flag
163 );
164 End If;
165 print_msg('Inside Update reporting Lines api');
166 /* assign the IN params to local variables*/
167 l_budget_line_id := p_budget_line_id;
168 l_start_date := p_start_date;
169 l_end_date := p_end_date;
170 l_period_name := p_period_name;
171 l_txn_currency_code := p_txn_currency_code;
172 l_txn_raw_cost := p_txn_raw_cost;
173 l_txn_burdened_cost := p_txn_burdened_cost ;
174 l_txn_revenue := p_txn_revenue;
175 l_project_currency_code := p_project_currency_code ;
176 l_project_raw_cost := p_project_raw_cost;
177 l_project_burdened_cost := p_project_burdened_cost;
178 l_project_revenue := p_project_revenue;
179 l_projfunc_currency_code := p_projfunc_currency_code;
180 l_projfunc_raw_cost := p_projfunc_raw_cost;
181 l_projfunc_burdened_cost := p_projfunc_burdened_cost;
182 l_projfunc_revenue := p_projfunc_revenue;
183 l_quantity := p_quantity;
184
185 IF p_activity_code = 'DELETE' AND p_budget_line_id is NULL Then
186 l_stage := 20;
187 raise invalid_params;
188 End IF;
192 OPEN strVer;
189 /* bug fix: 3839761 */
190 IF p_budget_version_id is NOT NULL Then
191 l_project_structure_id := NULL;
193 FETCH strVer INTO l_project_structure_id;
194 CLOSE strVer;
195 End If;
196
197 If p_activity_code in ('UPDATE','DELETE') Then
198 l_stage := 30;
199
200 If p_budget_version_id is NOT NULL and p_resource_assignment_id is NOT NULL Then
201 l_pji_call_flag := 'Y';
202 pji_rec := NULL;
203 OPEN cur_pjiDetails;
204 FETCH cur_pjiDetails INTO pji_rec;
205 IF cur_pjiDetails%NOTFOUND Then
206 l_pji_call_flag := 'N';
207 End If;
208 CLOSE cur_pjiDetails;
209 l_stage := 40;
210 print_msg('l_pji_call_flag['||l_pji_call_flag||']');
211 IF l_pji_call_flag = 'Y' Then --{
212
213 IF (p_calling_module = 'BUDGET_LINE' AND p_budget_line_id is NOT NULL) Then
214 BEGIN
215 l_stage := 50;
216 -- IPM: Removed check for budget line rejection codes.
217 SELECT bl.start_date
218 ,bl.end_date
219 ,bl.period_name
220 ,bl.txn_currency_code
221 ,decode(p_activity_code,'DELETE',bl.txn_raw_cost * -1,bl.txn_raw_cost)
222 ,decode(p_activity_code,'DELETE',bl.txn_burdened_cost *-1 , bl.txn_burdened_cost)
223 ,decode(p_activity_code,'DELETE',bl.txn_revenue * -1 ,bl.txn_revenue)
224 ,bl.project_currency_code
225 ,decode(p_activity_code,'DELETE',bl.project_raw_cost * -1 ,bl.project_raw_cost)
226 ,decode(p_activity_code,'DELETE',bl.project_burdened_cost * -1 ,bl.project_burdened_cost)
227 ,decode(p_activity_code,'DELETE',bl.project_revenue * -1 ,bl.project_revenue)
228 ,bl.projfunc_currency_code
229 ,decode(p_activity_code,'DELETE',bl.raw_cost * -1 ,bl.raw_cost)
230 ,decode(p_activity_code,'DELETE',bl.burdened_cost * -1 ,bl.burdened_cost)
231 ,decode(p_activity_code,'DELETE',bl.revenue * -1 ,bl.revenue)
232 ,decode(p_activity_code,'DELETE',bl.quantity * -1 ,bl.quantity)
233 INTO
234 l_start_date
235 ,l_end_date
236 ,l_period_name
237 ,l_txn_currency_code
238 ,l_txn_raw_cost
239 ,l_txn_burdened_cost
240 ,l_txn_revenue
241 ,l_project_currency_code
242 ,l_project_raw_cost
243 ,l_project_burdened_cost
244 ,l_project_revenue
245 ,l_projfunc_currency_code
246 ,l_projfunc_raw_cost
247 ,l_projfunc_burdened_cost
248 ,l_projfunc_revenue
249 ,l_quantity
250 FROM pa_budget_lines bl
251 WHERE bl.budget_line_id = p_budget_line_id;
252 l_stage := 50;
253 EXCEPTION
254 WHEN NO_DATA_FOUND Then
255 -- set the following columns to null so that calling pji api is bypassed
256 l_stage := 60;
257 l_quantity := NULL;
258 l_txn_raw_cost := NULL;
259 l_txn_burdened_cost := NULL;
260 l_txn_revenue := NULL;
261 END ;
262
263
264 END IF;
265 print_msg('Calling PJI_FM_XBS_ACCUM_MAINT.plan_update api bdgtLineId['||l_budget_line_id||']');
266 print_msg('AmtPassing to planUpdateAPI l_txn_currency_code['||l_txn_currency_code||']TxnRaw['||l_txn_raw_cost||']');
267 print_msg('txnBd['||l_txn_burdened_cost||']TxnRev['||l_txn_revenue||']PrjCur['||l_project_currency_code||']');
268 print_msg('prjRaw['||l_project_raw_cost||']prjBd['||l_project_burdened_cost||']prjrev['||l_project_revenue||']');
269 print_msg('pfcur['||l_projfunc_currency_code||']pfcraw['||l_projfunc_raw_cost||']pfcBd['||l_projfunc_burdened_cost||']');
270 print_msg('pfc_rev['||l_projfunc_revenue||']QTY['||l_quantity||']RbsElemt['||pji_rec.rbs_element_id||']');
271
272 IF (l_quantity is NULL
273 and l_txn_raw_cost is NULL
274 and l_txn_burdened_cost is NULL
275 and l_txn_revenue is NULL ) THEN
276
277 print_msg('This is newly created budgetline with NULL amts and qty from spread api no need to call pji');
278 l_stage := 70;
279 l_num_rows_inserted := 0;
280 NUll;
281 ELSE
282 print_msg('Calling PJI_FM_XBS_ACCUM_MAINT.plan_update api bdgtLineId['||l_budget_line_id||']');
283 /* clean up the tmp table before inserting*/
284 l_num_rows_inserted := 0;
285 /* since this is not a tmp table, deleteing will delete all the
286 * pending transactions inserted from other sessions
287 * so commenting out the code
288 * Bug fix:3803569 --DELETE FROM PJI_FM_EXTR_PLAN_LINES;
289 */
290
291 l_stage := 80;
292 INSERT INTO PJI_FM_EXTR_PLAN_LINES
293 ( PROJECT_ID
294 ,PROJECT_ORG_ID
295 ,PROJECT_ELEMENT_ID
296 ,STRUCT_VER_ID
297 ,CALENDAR_TYPE
298 ,RBS_ELEMENT_ID
299 ,CBS_ELEMENT_ID --bug#16200605
300 ,RBS_VERSION_ID
301 ,PLAN_VERSION_ID
302 ,PLAN_TYPE_ID
303 ,WP_VERSION_FLAG
307 ,START_DATE
304 ,RESOURCE_CLASS_CODE
305 ,RATE_BASED_FLAG
306 ,ROLLUP_TYPE
308 ,END_DATE
309 ,PERIOD_NAME
310 ,TXN_CURRENCY_CODE
311 ,TXN_RAW_COST
312 ,TXN_BURDENED_COST
313 ,TXN_REVENUE
314 ,PRJ_CURRENCY_CODE
315 ,PRJ_RAW_COST
316 ,PRJ_BURDENED_COST
317 ,PRJ_REVENUE
318 ,PFC_CURRENCY_CODE
319 ,PFC_RAW_COST
320 ,PFC_BURDENED_COST
321 ,PFC_REVENUE
322 ,QUANTITY
323 )
324 VALUES (
325 pji_rec.project_id
326 ,pji_rec.org_id
327 ,pji_rec.task_id
328 ,l_project_structure_id --pji_rec.project_structure_version_id
329 ,pji_rec.time_phase_code
330 ,pji_rec.rbs_element_id
331 ,pji_rec.cbs_element_id --bug#16200605
332 ,pji_rec.rbs_version_id
333 ,pji_rec.budget_version_id
334 ,pji_rec.fin_plan_type_id
335 ,pji_rec.wp_version_flag
336 ,pji_rec.resource_class_code
337 ,pji_rec.rate_based_flag
338 ,'W'
339 ,l_start_date
340 ,l_end_date
341 ,l_period_name
342 ,l_txn_currency_code
343 ,l_txn_raw_cost
344 ,l_txn_burdened_cost
345 ,l_txn_revenue
346 ,l_project_currency_code
347 ,l_project_raw_cost
348 ,l_project_burdened_cost
349 ,l_project_revenue
350 ,l_projfunc_currency_code
351 ,l_projfunc_raw_cost
352 ,l_projfunc_burdened_cost
353 ,l_projfunc_revenue
354 ,l_quantity
355 );
356 l_num_rows_inserted := sql%rowcount;
357
358 l_stage := 90;
359 END IF;
360
361 If l_num_rows_inserted > 0 Then
362 l_stage := 100;
363 /* added this as per PJIs request ( virangan) */
364 IF p_budget_version_id IS NOT NULL THEN
365 PJI_FM_XBS_ACCUM_MAINT.plan_update
366 (p_plan_version_id => p_budget_version_id
367 , x_msg_code => l_msg_data
368 ,x_return_status => l_return_status
369 );
370 ELSE
371 PJI_FM_XBS_ACCUM_MAINT.plan_update
372 ( x_msg_code => l_msg_data
373 ,x_return_status => l_return_status
374 );
375 END IF;
376 l_stage := 110;
377 Print_msg('End of PJI_FM_XBS_ACCUM_MAINT.plan_update retSts['||l_return_status||']msgdata['||l_msg_data||']');
378 If l_return_status <> 'S' Then
379 l_stage := 120;
380 x_msg_data := l_msg_data;
381 Raise pji_exception;
382 End If;
383 End If;
384 End If; --} end of l_pji_call_flag
385 End If;
386 End If; -- end of p_activity
387 l_stage := 200;
388 x_return_status := l_return_status;
389 print_msg('End of updateReportingLines api retSts['||x_return_status||']');
390 --reset the error stack;
391 If g_debug_flag = 'Y' Then
392 pa_debug.reset_err_stack;
393 End If;
394
395 EXCEPTION
396 WHEN INVALID_PARAMS THEN
397 x_return_status := 'E';
398 x_msg_count := 1;
399 x_msg_data := 'PA_FP_INVALID_PARAMS';
400 print_msg(l_stage||x_msg_data,'Y');
401 WHEN PJI_EXCEPTION THEN
402 x_return_status := 'U';
403 x_msg_count := 1;
404 fnd_msg_pub.add_exc_msg
405 ( p_pkg_name => 'PA_FP_PJI_INTG_PKG'
406 ,p_procedure_name => 'update_reporting_lines:Error Occured in plan_update' );
407 print_msg(l_stage||'Error occured in update_reporting_lines:Error Occured in plan_update ['||x_msg_data||']','Y');
408 If g_debug_flag = 'Y' Then
409 pa_debug.reset_err_stack;
410 End If;
411 RAISE;
412
413 WHEN OTHERS THEN
414 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
415 x_msg_data := SQLCODE||SQLERRM;
416 x_msg_count := 1;
417 fnd_msg_pub.add_exc_msg
418 ( p_pkg_name => 'PA_FP_PJI_INTG_PKG'
419 ,p_procedure_name => 'update_reporting_lines' );
420 print_msg(l_stage||'Error occured in update_reporting_lines['|| substr(SQLERRM,1,240),'Y');
421 If g_debug_flag = 'Y' Then
422 pa_debug.reset_err_stack;
423 End If;
424 RAISE;
425
426 END update_reporting_lines;
427
428 /* This is an wrapper api, which in turn calls update_reporting_lines and passes
429 * each budget line to reporting api
430 *This is the main api called from calculate, budget generation process to update the
431 * reporting PJI data when budget lines are created,updated or deleted.
432 * The following params values must be passed
433 * p_activity_code 'UPDATE',/'DELETE'
437 * NOTE: BEFORE CALLING THIS API, a record must exists in pa_resource_assignments for the p_resource_assignment_id
434 * p_calling_module name of API, for ex: 'CALCULATE_API'
435 * If activity = 'UPDATE' then +ve budgetLine amounts will be selected from DB
436 * if activity = 'DELETE' then -ve budgetLine amounts will be selected from DB
438 * AND a budget line must exists for the given p_budget_line_id
439 */
440 PROCEDURE update_reporting_lines_frombl
441 (p_calling_module IN Varchar2 Default 'CALCULATE_API'
442 ,p_activity_code IN Varchar2 Default 'UPDATE'
443 ,p_budget_version_id IN Number
444 ,p_resource_assignment_id IN Number
445 ,p_budget_line_id IN Number
446 ,x_msg_data OUT NOCOPY Varchar2
447 ,x_msg_count OUT NOCOPY Number
448 ,x_return_status OUT NOCOPY Varchar2
449 ) IS
450
451 INVALID_PARAMS EXCEPTION;
452 l_debug_flag Varchar2(10);
453 l_return_status Varchar2(10);
454 l_stage varchar2(100);
455 BEGIN
456 l_return_status := 'S';
457 x_return_status := 'S';
458 x_msg_count := 0;
459 x_msg_data := Null;
460
461 /* Initialize the error stack */
462 l_stage := 10;
463 l_debug_flag := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
464 g_debug_flag := l_debug_flag;
465 If g_debug_flag = 'Y' Then
466 pa_debug.init_err_stack('PA_FP_PJI_INTG_PKG.update_reporting_lines_frombl');
467 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
468 ,x_write_file => 'LOG'
469 ,x_debug_mode => l_debug_flag
470 );
471 End If;
472 print_msg('Entered update_reporting_lines_frombl module['||p_calling_module||']Activity['||p_activity_code||']bdgtver['||p_budget_version_id||']');
473 print_msg('bdgtLineId['||p_budget_line_id||']ResAssId['||p_resource_assignment_id||']');
474
475 If p_budget_line_id is NULL OR p_resource_assignment_id is NULL OR
476 p_activity_code NOT IN ('UPDATE','DELETE') Then
477 l_stage := 20;
478 print_msg('Invalid params passed to update_reporting_lines_frombl');
479 raise invalid_params;
480 End If;
481
482 IF p_budget_line_id is NOT NULL AND p_resource_assignment_id is NOT NULL Then
483
484 l_stage := 30;
485 update_reporting_lines
486 (p_calling_module => 'BUDGET_LINE'
487 ,p_activity_code => p_activity_code
488 ,p_budget_version_id => p_budget_version_id
489 ,p_budget_line_id => p_budget_line_id
490 ,p_resource_assignment_id => p_resource_assignment_id
491 ,p_start_date => null
492 ,p_end_date => null
493 ,p_period_name => null
494 ,p_txn_currency_code => null
495 ,p_quantity => null
496 ,p_txn_raw_cost => null
497 ,p_txn_burdened_cost => null
498 ,p_txn_revenue => null
499 ,p_project_currency_code => null
500 ,p_project_raw_cost => null
501 ,p_project_burdened_cost => null
502 ,p_project_revenue => null
503 ,p_projfunc_currency_code => null
504 ,p_projfunc_raw_cost => null
505 ,p_projfunc_burdened_cost => null
506 ,p_projfunc_revenue => null
507 ,x_msg_data => x_msg_data
508 ,x_msg_count => x_msg_count
509 ,x_return_status => x_return_status
510 ) ;
511 l_stage := 40;
512 End IF;
513
514 x_return_status := l_return_status;
515 print_msg('End of updateReportingLines_frombl api retSts['||x_return_status||']');
516 --reset the error stack;
517 If g_debug_flag = 'Y' Then
518 pa_debug.reset_err_stack;
519 End If;
520 EXCEPTION
521 WHEN INVALID_PARAMS THEN
522 x_return_status := 'E';
523 x_msg_count := 1;
524 x_msg_data := 'Invalid params passed to update_reporting_lines_frombl';
525 print_msg(l_stage||x_msg_data,'Y');
526 WHEN OTHERS THEN
527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
528 x_msg_data := SQLCODE||SQLERRM;
529 x_msg_count := 1;
530 fnd_msg_pub.add_exc_msg
531 ( p_pkg_name => 'PA_FP_PJI_INTG_PKG'
532 ,p_procedure_name => 'update_reporting_lines_from_bl' );
533 print_msg(l_stage||'Error occured in update_reporting_lines_frombl['|| substr(SQLERRM,1,240),'Y');
534 If g_debug_flag = 'Y' Then
535 pa_debug.reset_err_stack;
536 End If;
537 RAISE;
538 END update_reporting_lines_frombl;
539
540 /* This is the main api called from calculate, budget generation process to update the
541 * reporting PJI data when budget lines are created,updated or deleted.
542 * The following params values must be passed
543 * p_activity_code 'UPDATE',/'DELETE'
544 * p_calling_module name of API, for calculate 'CALCULATE_API'
545 * p_start_date BudgetLine StartDate
546 * p_end_date BudgetLine Enddate
550 * AND CALL THIS API ONLY IF THERE ARE NO REJECTION CODES STAMPED ON THE BUDGET LINES
547 * If activity = 'UPDATE' then all the amounts and currency columns must be passed
548 * if activity = 'DELETE' then -ve budgetLine amounts will be selected from DB and passed in params will be ignored
549 * NOTE: BEFORE CALLING THIS API, a record must exists in pa_resource_assignments for the p_resource_assignment_id
551 * NOTE: As of IPM, we ignore rejection codes stamped on budget lines for the purpose of updating PJI data.
552 * THIS API IS CREATED FOR BULK PROCESS OF DATA.
553 * NOTE: ALL PARAMS MUST BE PASSED , passing Null or incomplete params will error out
554 * the calling API must initialize all params and pass it
555 */
556 PROCEDURE blk_update_reporting_lines
557 (p_calling_module IN Varchar2 Default 'CALCULATE_API'
558 ,p_activity_code IN Varchar2 Default 'UPDATE'
559 ,p_budget_version_id IN Number
560 ,p_rep_budget_line_id_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
561 ,p_rep_res_assignment_id_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
562 ,p_rep_start_date_tab IN SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type()
563 ,p_rep_end_date_tab IN SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type()
564 ,p_rep_period_name_tab IN SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type()
565 ,p_rep_txn_curr_code_tab IN SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type()
566 ,p_rep_quantity_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
567 ,p_rep_txn_raw_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
568 ,p_rep_txn_burdened_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
569 ,p_rep_txn_revenue_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
570 ,p_rep_project_curr_code_tab IN SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type()
571 ,p_rep_project_raw_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
572 ,p_rep_project_burden_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
573 ,p_rep_project_revenue_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
574 ,p_rep_projfunc_curr_code_tab IN SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type()
575 ,p_rep_projfunc_raw_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
576 ,p_rep_projfunc_burden_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
577 ,p_rep_projfunc_revenue_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
578 /*
579 * The following _act_ parameters contain actual amounts.
580 */
581 ,p_rep_act_quantity_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
582 ,p_rep_txn_act_raw_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
583 ,p_rep_txn_act_burd_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
584 ,p_rep_txn_act_rev_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
585 ,p_rep_prj_act_raw_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
586 ,p_rep_prj_act_burd_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
587 ,p_rep_prj_act_rev_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
588 ,p_rep_pf_act_raw_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
589 ,p_rep_pf_act_burd_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
590 ,p_rep_pf_act_rev_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
591 /* bug fix:5116157 */
592 ,p_rep_line_mode_tab IN SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type()
593 ,p_rep_rate_base_flag_tab IN SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type()
594 ,x_msg_data OUT NOCOPY Varchar2
595 ,x_msg_count OUT NOCOPY Number
596 ,x_return_status OUT NOCOPY Varchar2
597 ) IS
598
599 l_msg_count Number := 0;
600 l_msg_data Varchar2(1000) := Null;
601 l_return_status Varchar2(10);
602 l_debug_flag Varchar2(10);
603 PJI_EXCEPTION EXCEPTION;
604
605 l_rep_budget_line_id_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
606 l_rep_res_assignment_id_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
607 l_rep_start_date_tab SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
608 l_rep_end_date_tab SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
609 l_rep_period_name_tab SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
610 l_rep_txn_curr_code_tab SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
611 l_rep_quantity_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
612 l_rep_txn_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
613 l_rep_txn_burdened_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
614 l_rep_txn_revenue_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
615 l_rep_project_curr_code_tab SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
616 l_rep_project_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
617 l_rep_project_burden_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
618 l_rep_project_revenue_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
619 l_rep_projfunc_curr_code_tab SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
620 l_rep_projfunc_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
621 l_rep_projfunc_burden_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
622 l_rep_projfunc_revenue_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
623
624 /*
625 * The following _act_ tables are to hold Actual amounts.
626 */
627 l_rep_act_quantity_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
628 l_rep_txn_act_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
629 l_rep_txn_act_burd_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
630 l_rep_txn_act_rev_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
631 l_rep_prj_act_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
632 l_rep_prj_act_burd_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
633 l_rep_prj_act_rev_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
634 l_rep_pf_act_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
635 l_rep_pf_act_burd_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
636 l_rep_pf_act_rev_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
637
638 l_rep_org_id_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
639 l_rep_rbs_version_id_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
640 l_rep_finplan_type_id_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
641 l_rep_proj_structure_id_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
642 l_rep_wp_version_flag_tab SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
643 l_rep_time_phase_code_tab SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
644 l_rep_project_id_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
645 l_rep_task_id_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
646 l_rep_rbs_element_id_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
647 l_rep_cbs_element_id_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(); --bug#16200605
648 l_rep_resclass_code_tab SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
649 l_rep_rate_base_flag_tab SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
650 l_rep_line_mode_tab SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
651
652 l_pji_call_flag Varchar2(10);
653 l_resAssId Number;
654 l_start_date Date;
655 l_end_date Date;
656 l_period_name Varchar2(100);
657 l_txn_currency_code Varchar2(100);
658 l_txn_raw_cost Number;
659 l_txn_burdened_cost Number;
660 l_txn_revenue Number;
661 l_project_currency_code Varchar2(100);
662 l_project_raw_cost Number;
663 l_project_burdened_cost Number;
664 l_project_revenue Number;
665 l_projfunc_currency_code Varchar2(100);
666 l_projfunc_raw_cost Number;
667 l_projfunc_burdened_cost Number;
668 l_projfunc_revenue Number;
669 l_quantity Number;
670
671 l_budget_line_id Number;
672 l_num_rows_inserted Number;
673 INVALID_PARAMS EXCEPTION;
674 l_stage Varchar2(100);
675
676 l_project_id Number;
677 l_task_id Number;
678 l_rbs_element_id Number;
679 l_cbs_element_id Number; --bug#16200605
680 l_res_class_code Varchar2(80);
681 l_rate_base_flag Varchar2(80);
682 l_org_id Number;
683 l_rbs_version_id Number;
684 l_fin_plan_type_id Number;
685 l_project_structure_id Number;
686 l_wp_version_flag Varchar2(80);
687 l_time_phase_code Varchar2(80);
688
689 CURSOR strVer IS
690 SELECT DECODE(nvl(pbv.wp_version_flag,'N'),'Y',pbv.project_structure_version_id,
691 PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(pbv.project_id)) project_structure_version_id
692 FROM pa_budget_versions pbv
693 WHERE pbv.budget_version_id = p_budget_version_id;
694
695
696 BEGIN
697 l_return_status := 'S';
698 x_return_status := 'S';
699 x_msg_count := 0;
700 x_msg_data := Null;
701
702 l_stage := 10;
703 /* Initialize the error stack */
704 l_debug_flag := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
705 g_debug_flag := l_debug_flag;
706 print_msg('Entered PA_FP_PJI_INTG_PKG.blk_update_reporting_lines api: Num of Trxns['||p_rep_res_assignment_id_tab.count||']','Y');
707 If g_debug_flag = 'Y' Then
708 pa_debug.init_err_stack('PA_FP_PJI_INTG_PKG.blk_update_reporting_lines');
709 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
710 ,x_write_file => 'LOG'
711 ,x_debug_mode => l_debug_flag
712 );
713 End If;
714 /* Assign the in params to local tables*/
715 l_rep_budget_line_id_tab := p_rep_budget_line_id_tab;
716 l_rep_res_assignment_id_tab := p_rep_res_assignment_id_tab;
717 l_rep_start_date_tab := p_rep_start_date_tab;
718 l_rep_end_date_tab := p_rep_end_date_tab;
719 l_rep_period_name_tab := p_rep_period_name_tab;
720 l_rep_txn_curr_code_tab := p_rep_txn_curr_code_tab;
721 l_rep_quantity_tab := p_rep_quantity_tab;
722 l_rep_txn_raw_cost_tab := p_rep_txn_raw_cost_tab;
723 l_rep_txn_burdened_cost_tab := p_rep_txn_burdened_cost_tab;
724 l_rep_txn_revenue_tab := p_rep_txn_revenue_tab;
725 l_rep_project_curr_code_tab := p_rep_project_curr_code_tab;
726 l_rep_project_raw_cost_tab := p_rep_project_raw_cost_tab;
727 l_rep_project_burden_cost_tab := p_rep_project_burden_cost_tab;
728 l_rep_project_revenue_tab := p_rep_project_revenue_tab;
729 l_rep_projfunc_curr_code_tab := p_rep_projfunc_curr_code_tab;
730 l_rep_projfunc_raw_cost_tab := p_rep_projfunc_raw_cost_tab;
731 l_rep_projfunc_burden_cost_tab := p_rep_projfunc_burden_cost_tab;
732 l_rep_projfunc_revenue_tab := p_rep_projfunc_revenue_tab;
733 l_rep_act_quantity_tab := p_rep_act_quantity_tab;
734 l_rep_txn_act_raw_cost_tab := p_rep_txn_act_raw_cost_tab;
735 l_rep_txn_act_burd_cost_tab := p_rep_txn_act_burd_cost_tab;
736 l_rep_txn_act_rev_tab := p_rep_txn_act_rev_tab;
737 l_rep_prj_act_raw_cost_tab := p_rep_prj_act_raw_cost_tab;
738 l_rep_prj_act_burd_cost_tab := p_rep_prj_act_burd_cost_tab;
739 l_rep_prj_act_rev_tab := p_rep_prj_act_rev_tab;
740 l_rep_pf_act_raw_cost_tab := p_rep_pf_act_raw_cost_tab;
741 l_rep_pf_act_burd_cost_tab := p_rep_pf_act_burd_cost_tab;
742 l_rep_pf_act_rev_tab := p_rep_pf_act_rev_tab;
743 l_rep_rate_base_flag_tab := p_rep_rate_base_flag_tab;
744 l_rep_line_mode_tab := p_rep_line_mode_tab;
745
746 /*=================================================================+
747 | Taking care of input tables that were not passed by the caller. |
748 | This is being done only for Actual amounts assuming plan |
749 | amounts will always be sent. |
750 +=================================================================*/
751 FOR i IN l_rep_quantity_tab.FIRST .. l_rep_quantity_tab.LAST
752 LOOP
753 IF NOT l_rep_act_quantity_tab.EXISTS(i)
754 THEN
755 l_rep_act_quantity_tab.EXTEND;
756 l_rep_act_quantity_tab(i) := NULL;
757 ELSIF l_rep_act_quantity_tab(i) = fnd_api.g_miss_num
758 THEN
759 l_rep_act_quantity_tab(i) := NULL;
760 END IF;
761
762 IF NOT l_rep_txn_act_raw_cost_tab.EXISTS(i)
763 THEN
764 l_rep_txn_act_raw_cost_tab.EXTEND;
765 l_rep_txn_act_raw_cost_tab(i) := NULL;
766 ELSIF l_rep_txn_act_raw_cost_tab(i) = fnd_api.g_miss_num
767 THEN
768 l_rep_txn_act_raw_cost_tab(i) := NULL;
769 END IF;
770
771 IF NOT l_rep_txn_act_burd_cost_tab.EXISTS(i)
772 THEN
773 l_rep_txn_act_burd_cost_tab.EXTEND;
774 l_rep_txn_act_burd_cost_tab(i) := NULL;
775 ELSIF l_rep_txn_act_burd_cost_tab(i) = fnd_api.g_miss_num
776 THEN
777 l_rep_txn_act_burd_cost_tab(i) := NULL;
778 END IF;
779
780 IF NOT l_rep_txn_act_rev_tab.EXISTS(i)
781 THEN
782 l_rep_txn_act_rev_tab.EXTEND;
783 l_rep_txn_act_rev_tab(i) := NULL;
784 ELSIF l_rep_txn_act_rev_tab(i) = fnd_api.g_miss_num
785 THEN
786 l_rep_txn_act_rev_tab(i) := NULL;
787 END IF;
788
789 IF NOT l_rep_prj_act_raw_cost_tab.EXISTS(i)
790 THEN
791 l_rep_prj_act_raw_cost_tab.EXTEND;
792 l_rep_prj_act_raw_cost_tab(i) := NULL;
793 ELSIF l_rep_prj_act_raw_cost_tab(i) = fnd_api.g_miss_num
794 THEN
795 l_rep_prj_act_raw_cost_tab(i) := NULL;
796 END IF;
797 IF NOT l_rep_prj_act_burd_cost_tab.EXISTS(i)
798 THEN
799 l_rep_prj_act_burd_cost_tab.EXTEND;
800 l_rep_prj_act_burd_cost_tab(i) := NULL;
801 ELSIF l_rep_prj_act_burd_cost_tab(i) = fnd_api.g_miss_num
802 THEN
803 l_rep_prj_act_burd_cost_tab(i) := NULL;
804 END IF;
805 IF NOT l_rep_prj_act_rev_tab.EXISTS(i)
806 THEN
810 THEN
807 l_rep_prj_act_rev_tab.EXTEND;
808 l_rep_prj_act_rev_tab(i) := NULL;
809 ELSIF l_rep_prj_act_rev_tab(i) = fnd_api.g_miss_num
811 l_rep_prj_act_rev_tab(i) := NULL;
812 END IF;
813 IF NOT l_rep_pf_act_raw_cost_tab.EXISTS(i)
814 THEN
815 l_rep_pf_act_raw_cost_tab.EXTEND;
816 l_rep_pf_act_raw_cost_tab(i) := NULL;
817 ELSIF l_rep_pf_act_raw_cost_tab(i) = fnd_api.g_miss_num
818 THEN
819 l_rep_pf_act_raw_cost_tab(i) := NULL;
820 END IF;
821 IF NOT l_rep_pf_act_burd_cost_tab.EXISTS(i)
822 THEN
823 l_rep_pf_act_burd_cost_tab.EXTEND;
824 l_rep_pf_act_burd_cost_tab(i) := NULL;
825 ELSIF l_rep_pf_act_burd_cost_tab(i) = fnd_api.g_miss_num
826 THEN
827 l_rep_pf_act_burd_cost_tab(i) := NULL;
828 END IF;
829 IF NOT l_rep_pf_act_rev_tab.EXISTS(i)
830 THEN
831 l_rep_pf_act_rev_tab.EXTEND;
832 l_rep_pf_act_rev_tab(i) := NULL;
833 ELSIF l_rep_pf_act_rev_tab(i) = fnd_api.g_miss_num
834 THEN
835 l_rep_pf_act_rev_tab(i) := NULL;
836 END IF;
837
838 IF NOT l_rep_rate_base_flag_tab.EXISTS(i) Then
839 l_rep_rate_base_flag_tab.EXTEND;
840 l_rep_rate_base_flag_tab(i) := NULL;
841 END IF;
842
843 IF NOT l_rep_line_mode_tab.EXISTS(i) then
844 l_rep_line_mode_tab.EXTEND;
845 l_rep_line_mode_tab(i) := NULL;
846 END If;
847
848 END LOOP; -- g_TXN_SOURCE_ID_sysTab.FIRST .. g_TXN_SOURCE_ID_sysTab.LAST
849
850 IF p_activity_code = 'DELETE' AND l_rep_budget_line_id_tab.COUNT = 0 Then
851 l_stage := 20;
852 raise invalid_params;
853 End IF;
854
855 /* bug fix: 3839761 */
856 IF p_budget_version_id is NOT NULL Then
857 l_project_structure_id := null;
858 OPEN strVer;
859 FETCH strVer INTO l_project_structure_id;
860 CLOSE strVer;
861 End If;
862
863 If p_activity_code in ('UPDATE','DELETE') Then
864 l_stage := 30;
865 FOR i IN l_rep_budget_line_id_tab.FIRST .. l_rep_budget_line_id_tab.LAST LOOP --{
866 IF (p_calling_module = 'BUDGET_LINE' AND l_rep_budget_line_id_tab(i) is NOT NULL) Then
867 BEGIN
868 l_stage := 50;
869 If g_debug_flag = 'Y' Then
870 print_msg('Executing sql to get -ve amts for budget line Id['||l_rep_budget_line_id_tab(i)||']');
871 End If;
872 -- IPM: Removed check for budget line rejection codes.
873 SELECT bl.start_date
874 ,bl.end_date
875 ,bl.period_name
876 ,bl.txn_currency_code
877 ,decode(p_activity_code,'DELETE',bl.txn_raw_cost * -1,bl.txn_raw_cost)
878 ,decode(p_activity_code,'DELETE',bl.txn_burdened_cost *-1 , bl.txn_burdened_cost)
879 ,decode(p_activity_code,'DELETE',bl.txn_revenue * -1 ,bl.txn_revenue)
880 ,bl.project_currency_code
881 ,decode(p_activity_code,'DELETE',bl.project_raw_cost * -1 ,bl.project_raw_cost)
882 ,decode(p_activity_code,'DELETE',bl.project_burdened_cost * -1 ,bl.project_burdened_cost)
883 ,decode(p_activity_code,'DELETE',bl.project_revenue * -1 ,bl.project_revenue)
884 ,bl.projfunc_currency_code
885 ,decode(p_activity_code,'DELETE',bl.raw_cost * -1 ,bl.raw_cost)
886 ,decode(p_activity_code,'DELETE',bl.burdened_cost * -1 ,bl.burdened_cost)
887 ,decode(p_activity_code,'DELETE',bl.revenue * -1 ,bl.revenue)
888 ,decode(p_activity_code,'DELETE',bl.quantity * -1 ,bl.quantity)
889 ,decode(p_activity_code,'DELETE',bl.txn_init_raw_cost * -1,bl.txn_init_raw_cost)
890 ,decode(p_activity_code,'DELETE',bl.txn_init_burdened_cost *-1 , bl.txn_init_burdened_cost)
891 ,decode(p_activity_code,'DELETE',bl.txn_init_revenue * -1 ,bl.txn_init_revenue)
892 ,decode(p_activity_code,'DELETE',bl.project_init_raw_cost * -1 ,bl.project_init_raw_cost)
893 ,decode(p_activity_code,'DELETE',bl.project_init_burdened_cost * -1 ,bl.project_init_burdened_cost)
894 ,decode(p_activity_code,'DELETE',bl.project_init_revenue * -1 ,bl.project_init_revenue)
895 ,decode(p_activity_code,'DELETE',bl.init_raw_cost * -1 ,bl.init_raw_cost)
896 ,decode(p_activity_code,'DELETE',bl.init_burdened_cost * -1 ,bl.init_burdened_cost)
897 ,decode(p_activity_code,'DELETE',bl.init_revenue * -1 ,bl.init_revenue)
898 ,decode(p_activity_code,'DELETE',bl.init_quantity * -1 ,bl.init_quantity)
899 INTO
900 l_rep_start_date_tab(i)
901 ,l_rep_end_date_tab(i)
902 ,l_rep_period_name_tab(i)
903 ,l_rep_txn_curr_code_tab(i)
904 ,l_rep_txn_raw_cost_tab(i)
905 ,l_rep_txn_burdened_cost_tab(i)
906 ,l_rep_txn_revenue_tab(i)
907 ,l_rep_project_curr_code_tab(i)
908 ,l_rep_project_raw_cost_tab(i)
909 ,l_rep_project_burden_cost_tab(i)
910 ,l_rep_project_revenue_tab(i)
911 ,l_rep_projfunc_curr_code_tab(i)
915 ,l_rep_quantity_tab(i)
912 ,l_rep_projfunc_raw_cost_tab(i)
913 ,l_rep_projfunc_burden_cost_tab(i)
914 ,l_rep_projfunc_revenue_tab(i)
916 ,l_rep_txn_act_raw_cost_tab(i)
917 ,l_rep_txn_act_burd_cost_tab(i)
918 ,l_rep_txn_act_rev_tab(i)
919 ,l_rep_prj_act_raw_cost_tab(i)
920 ,l_rep_prj_act_burd_cost_tab(i)
921 ,l_rep_prj_act_rev_tab(i)
922 ,l_rep_pf_act_raw_cost_tab(i)
923 ,l_rep_pf_act_burd_cost_tab(i)
924 ,l_rep_pf_act_rev_tab(i)
925 ,l_rep_act_quantity_tab(i)
926 FROM pa_budget_lines bl
927 WHERE bl.budget_line_id = l_rep_budget_line_id_tab(i);
928 If g_debug_flag = 'Y' Then
929 print_msg('Number of rows fetched:['||sql%rowcount||']');
930 End If;
931 l_stage := 50;
932 EXCEPTION
933 WHEN NO_DATA_FOUND Then
934 -- set the following columns to null so that calling pji api is bypassed
935 l_stage := 60;
936 l_rep_quantity_tab(i) := NULL;
937 l_rep_txn_raw_cost_tab(i) := NULL;
938 l_rep_txn_burdened_cost_tab(i) := NULL;
939 l_rep_txn_revenue_tab(i) := NULL;
940 l_rep_act_quantity_tab(i) := NULL;
941 l_rep_txn_act_raw_cost_tab(i) := NULL;
942 l_rep_txn_act_burd_cost_tab(i) := NULL;
943 l_rep_txn_act_rev_tab(i) := NULL;
944 END ;
945 END IF;
946 END LOOP; --}
947 l_stage := 60;
948 /* Insert the records into pji tmp table*/
949 FOR i IN l_rep_res_assignment_id_tab.FIRST .. l_rep_res_assignment_id_tab.LAST LOOP --{
950 l_rep_org_id_tab.extend;
951 l_rep_rbs_version_id_tab.extend;
952 l_rep_finplan_type_id_tab.extend;
953 l_rep_proj_structure_id_tab.extend;
954 l_rep_wp_version_flag_tab.extend;
955 l_rep_time_phase_code_tab.extend;
956 l_rep_project_id_tab.extend;
957 l_rep_task_id_tab.extend;
958 l_rep_rbs_element_id_tab.extend;
959 l_rep_cbs_element_id_tab.extend; --bug#16200605
960 l_rep_resclass_code_tab.extend;
961 -- bug fix: 5116157 l_rep_rate_base_flag_tab.extend;
962 If (l_resAssId is NULL or l_resAssId <> l_rep_res_assignment_id_tab(i) ) Then
963 If g_debug_flag = 'Y' Then
964 print_msg('Fetching Resource details for AssignmentId['||l_rep_res_assignment_id_tab(i)||']');
965 End If;
966 SELECT ra.project_id
967 ,ra.task_id
968 ,ra.rbs_element_id
969 ,ra.cbs_element_id --bug#16200605
970 ,ra.resource_class_code
971 ,ra.rate_based_flag
972 ,ppa.org_id
973 ,ppfo.rbs_version_id
974 ,pbv.fin_plan_type_id
975 /* Bug fix: 3839761 --nvl(pbv.project_structure_version_id,
976 --PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(pbv.project_id)) project_structure_version_id
977 */
978 ,pbv.wp_version_flag
979 ,decode(pbv.version_type, 'COST',ppfo.cost_time_phased_code,
980 'REVENUE',ppfo.revenue_time_phased_code,
981 ppfo.all_time_phased_code) time_phase_code
982 INTO l_project_id
983 ,l_task_id
984 ,l_rbs_element_id
985 ,l_cbs_element_id --bug#16200605
986 ,l_res_class_code
987 ,l_rate_base_flag
988 ,l_org_id
989 ,l_rbs_version_id
990 ,l_fin_plan_type_id
991 /* bug fix: 3839761--,l_project_structure_id */
992 ,l_wp_version_flag
993 ,l_time_phase_code
994 FROM pa_projects_all ppa
995 ,pa_budget_versions pbv
996 ,pa_proj_fp_options ppfo
997 ,pa_resource_assignments ra
998 WHERE ppa.project_id = pbv.project_id
999 AND pbv.budget_version_id = ppfo.fin_plan_version_id
1000 AND pbv.budget_version_id = p_budget_version_id
1001 AND ra.resource_assignment_id = l_rep_res_assignment_id_tab(i)
1002 AND ra.budget_version_id = pbv.budget_version_id;
1003
1004 l_resAssId := l_rep_res_assignment_id_tab(i);
1005 l_rep_project_id_tab(i) := l_project_id;
1006 l_rep_task_id_tab(i) := l_task_id;
1007 l_rep_rbs_element_id_tab(i) := l_rbs_element_id;
1008 l_rep_cbs_element_id_tab(i) := l_cbs_element_id; --bug#16200605
1009 l_rep_resclass_code_tab(i) := l_res_class_code;
1010 IF NVL(l_rep_line_mode_tab(i),'XXX') = 'REVERSAL' Then
1011 l_rep_rate_base_flag_tab(i) := NVL(l_rep_rate_base_flag_tab(i),l_rate_base_flag);
1012 Else
1013 l_rep_rate_base_flag_tab(i) := l_rate_base_flag;
1014 End If;
1015 l_rep_org_id_tab(i) := l_org_id;
1016 l_rep_rbs_version_id_tab(i) := l_rbs_version_id;
1017 l_rep_finplan_type_id_tab(i) := l_fin_plan_type_id;
1018 l_rep_proj_structure_id_tab(i) := l_project_structure_id;
1019 l_rep_wp_version_flag_tab(i) := l_wp_version_flag;
1020 l_rep_time_phase_code_tab(i) := l_time_phase_code;
1021 Else
1022 -- retrieve from cache
1023 l_resAssId := l_rep_res_assignment_id_tab(i);
1024 l_rep_project_id_tab(i) := l_project_id;
1025 l_rep_task_id_tab(i) := l_task_id;
1026 l_rep_rbs_element_id_tab(i) := l_rbs_element_id;
1030 l_rep_rate_base_flag_tab(i) := NVL(l_rep_rate_base_flag_tab(i),l_rate_base_flag);
1027 l_rep_cbs_element_id_tab(i) := l_cbs_element_id; --bug#16200605
1028 l_rep_resclass_code_tab(i) := l_res_class_code;
1029 IF NVL(l_rep_line_mode_tab(i),'XXX') = 'REVERSAL' Then
1031 Else
1032 l_rep_rate_base_flag_tab(i) := l_rate_base_flag;
1033 End If;
1034 l_rep_org_id_tab(i) := l_org_id;
1035 l_rep_rbs_version_id_tab(i) := l_rbs_version_id;
1036 l_rep_finplan_type_id_tab(i) := l_fin_plan_type_id;
1037 l_rep_proj_structure_id_tab(i) := l_project_structure_id;
1038 l_rep_wp_version_flag_tab(i) := l_wp_version_flag;
1039 l_rep_time_phase_code_tab(i) := l_time_phase_code;
1040 End If;
1041 END LOOP; --}
1042
1043 /*Bulk insert into reporting table */
1044 IF l_rep_res_assignment_id_tab.COUNT > 0 Then --{
1045 l_num_rows_inserted := 0;
1046 l_stage := 80;
1047 If g_debug_flag = 'Y' Then
1048 print_msg(l_stage||': Inserting records into PJI_FM_EXTR_PLAN_LINES tmp table');
1049 End If;
1050 FORALL i IN l_rep_res_assignment_id_tab.FIRST .. l_rep_res_assignment_id_tab.LAST
1051 INSERT INTO PJI_FM_EXTR_PLAN_LINES
1052 ( PROJECT_ID
1053 ,PROJECT_ORG_ID
1054 ,PROJECT_ELEMENT_ID
1055 ,STRUCT_VER_ID
1056 ,CALENDAR_TYPE
1057 ,RBS_ELEMENT_ID
1058 ,CBS_ELEMENT_ID--bug#16200605
1059 ,RBS_VERSION_ID
1060 ,PLAN_VERSION_ID
1061 ,PLAN_TYPE_ID
1062 ,WP_VERSION_FLAG
1063 ,RESOURCE_CLASS_CODE
1064 ,RATE_BASED_FLAG
1065 ,ROLLUP_TYPE
1066 ,START_DATE
1067 ,END_DATE
1068 ,PERIOD_NAME
1069 ,TXN_CURRENCY_CODE
1070 ,TXN_RAW_COST
1071 ,TXN_BURDENED_COST
1072 ,TXN_REVENUE
1073 ,PRJ_CURRENCY_CODE
1074 ,PRJ_RAW_COST
1075 ,PRJ_BURDENED_COST
1076 ,PRJ_REVENUE
1077 ,PFC_CURRENCY_CODE
1078 ,PFC_RAW_COST
1079 ,PFC_BURDENED_COST
1080 ,PFC_REVENUE
1081 ,QUANTITY
1082 ,ACT_TXN_RAW_COST
1083 ,ACT_TXN_BURDENED_COST
1084 ,ACT_TXN_REVENUE
1085 ,ACT_PRJ_RAW_COST
1086 ,ACT_PRJ_BURDENED_COST
1087 ,ACT_PRJ_REVENUE
1088 ,ACT_PFC_RAW_COST
1089 ,ACT_PFC_BURDENED_COST
1090 ,ACT_PFC_REVENUE
1091 ,ACT_QUANTITY
1092 )
1093 SELECT
1094 l_rep_project_id_tab(i)
1095 ,l_rep_org_id_tab(i)
1096 ,l_rep_task_id_tab(i)
1097 ,l_rep_proj_structure_id_tab(i)
1098 ,l_rep_time_phase_code_tab(i)
1099 ,l_rep_rbs_element_id_tab(i)
1100 ,l_rep_cbs_element_id_tab(i) --bug#16200605
1101 ,l_rep_rbs_version_id_tab(i)
1102 ,p_budget_version_id
1103 ,l_rep_finplan_type_id_tab(i)
1104 ,l_rep_wp_version_flag_tab(i)
1105 ,l_rep_resclass_code_tab(i)
1106 ,l_rep_rate_base_flag_tab(i)
1107 ,'W'
1108 ,l_rep_start_date_tab(i)
1109 ,l_rep_end_date_tab(i)
1110 ,l_rep_period_name_tab(i)
1111 ,l_rep_txn_curr_code_tab(i)
1112 ,l_rep_txn_raw_cost_tab(i)
1113 ,l_rep_txn_burdened_cost_tab(i)
1114 ,l_rep_txn_revenue_tab(i)
1115 ,l_rep_project_curr_code_tab(i)
1116 ,l_rep_project_raw_cost_tab(i)
1117 ,l_rep_project_burden_cost_tab(i)
1118 ,l_rep_project_revenue_tab(i)
1119 ,l_rep_projfunc_curr_code_tab(i)
1120 ,l_rep_projfunc_raw_cost_tab(i)
1121 ,l_rep_projfunc_burden_cost_tab(i)
1122 ,l_rep_projfunc_revenue_tab(i)
1123 ,l_rep_quantity_tab(i)
1124 ,l_rep_txn_act_raw_cost_tab(i)
1125 ,l_rep_txn_act_burd_cost_tab(i)
1126 ,l_rep_txn_act_rev_tab(i)
1127 ,l_rep_prj_act_raw_cost_tab(i)
1128 ,l_rep_prj_act_burd_cost_tab(i)
1129 ,l_rep_prj_act_rev_tab(i)
1130 ,l_rep_pf_act_raw_cost_tab(i)
1131 ,l_rep_pf_act_burd_cost_tab(i)
1132 ,l_rep_pf_act_rev_tab(i)
1133 ,l_rep_act_quantity_tab(i)
1134 FROM DUAL
1135 WHERE ( l_rep_quantity_tab(i) is NOT NULL
1136 OR l_rep_txn_raw_cost_tab(i) is NOT NULL
1137 OR l_rep_txn_burdened_cost_tab(i) is NOT NULL
1138 OR l_rep_txn_revenue_tab(i) is NOT NULL
1139 OR l_rep_act_quantity_tab(i) is NOT NULL
1140 OR l_rep_txn_act_raw_cost_tab(i) is NOT NULL
1141 OR l_rep_txn_act_burd_cost_tab(i) is NOT NULL
1142 OR l_rep_txn_act_rev_tab(i) is NOT NULL ) ;
1143 l_num_rows_inserted := sql%rowcount;
1144 If g_debug_flag = 'Y' Then
1145 print_msg('Number of records inserted ['||l_num_rows_inserted||']');
1146 End If;
1147 /* end of Bulk insert */
1148
1149 /* Start for dubug message*/
1150 /* Bug fix: 4159553 enclose the the following cursor in a debug flag */
1151 IF NVL(g_debug_flag,'N') = 'Y' Then
1152 for j in (select pj.RBS_ELEMENT_ID RBS_ELEMENT_ID
1153 ,pj.TXN_CURRENCY_CODE TXN_CURRENCY_CODE
1154 ,pj.RATE_BASED_FLAG RATE_BASED_FLAG
1155 ,sum(pj.QUANTITY) QUANTITY
1156 ,sum(pj.TXN_RAW_COST) TXN_RAW_COST
1157 ,sum(pj.TXN_BURDENED_COST) TXN_BURDENED_COST
1158 ,sum(pj.TXN_REVENUE) TXN_REVENUE
1159 ,sum(pj.ACT_QUANTITY) ACT_QUANTITY
1160 ,sum(pj.ACT_TXN_RAW_COST) ACT_TXN_RAW_COST
1161 ,sum(pj.ACT_TXN_BURDENED_COST) ACT_TXN_BURDENED_COST
1162 ,sum(pj.ACT_TXN_REVENUE) ACT_TXN_REVENUE
1163 from PJI_FM_EXTR_PLAN_LINES pj
1164 where pj.plan_version_id = p_budget_version_id
1165 Group by pj.RBS_VERSION_ID
1166 ,pj.RBS_ELEMENT_ID
1167 ,pj.TXN_CURRENCY_CODE
1168 ,pj.RATE_BASED_FLAG ) LOOP
1169 null;
1170 print_msg('RbsElemnt['||j.RBS_ELEMENT_ID||']TxnCur['||j.TXN_CURRENCY_CODE||
1171 ']TxnQty['||j.QUANTITY||']TxnRaw['||j.TXN_RAW_COST||
1172 ']TxnBurd['||j.TXN_BURDENED_COST||']TxnReve['||j.TXN_REVENUE||
1173 ']ActTxnQty['||j.ACT_QUANTITY||']ActTxnRaw['||j.ACT_TXN_RAW_COST||
1174 ']ActTxnBurd['||j.ACT_TXN_BURDENED_COST||']ActTxnReve['||j.ACT_TXN_REVENUE||
1175 ']RateBaseFlag['||j.RATE_BASED_FLAG||']');
1176 End Loop;
1177 END IF;
1178 /* end of dbug message*/
1179
1180 IF l_num_rows_inserted > 0 Then
1181 l_stage := 100;
1182 /* added this as per PJIs request ( virangan) */
1183 print_msg('Start of PJI_plan_update:['||dbms_utility.get_time||']');
1184 IF p_budget_version_id IS NOT NULL THEN
1185 If g_debug_flag = 'Y' Then
1186 print_msg('l_stage: Calling PJI_FM_XBS_ACCUM_MAINT.plan_update for budget version');
1187 End If;
1188 PJI_FM_XBS_ACCUM_MAINT.plan_update
1189 (p_plan_version_id => p_budget_version_id
1190 , x_msg_code => l_msg_data
1191 ,x_return_status => l_return_status
1192 );
1193 ELSE
1194 If g_debug_flag = 'Y' Then
1195 print_msg('l_stage: Calling PJI_FM_XBS_ACCUM_MAINT.plan_update without version');
1196 End If;
1197 PJI_FM_XBS_ACCUM_MAINT.plan_update
1198 ( x_msg_code => l_msg_data
1199 ,x_return_status => l_return_status
1200 );
1201 END IF;
1202 print_msg('End of PJI_plan_update:['||dbms_utility.get_time||']');
1203 l_stage := 110;
1204 If g_debug_flag = 'Y' Then
1205 Print_msg('End of PJI_FM_XBS_ACCUM_MAINT.plan_update retSts['||l_return_status||']msgdata['||l_msg_data||']');
1206 End If;
1207 If l_return_status <> 'S' Then
1208 l_stage := 120;
1209 x_msg_data := l_msg_data;
1210 Raise pji_exception;
1211 End If;
1212 END IF;
1213
1214 END IF; --} end of restab count > 0
1215 End If; -- end of p_activity
1216 l_stage := 200;
1217 x_return_status := l_return_status;
1218 If g_debug_flag = 'Y' Then
1219 print_msg('End of blk_update_reporting_lines api retSts['||x_return_status||']');
1220 End If;
1221 --reset the error stack;
1222 If g_debug_flag = 'Y' Then
1223 pa_debug.reset_err_stack;
1224 End If;
1225
1226 EXCEPTION
1227 WHEN INVALID_PARAMS THEN
1228 x_return_status := 'E';
1229 x_msg_count := 1;
1230 x_msg_data := 'PA_FP_INVALID_PARAMS';
1231 print_msg(l_stage||x_msg_data,'Y');
1232 WHEN PJI_EXCEPTION THEN
1233 x_return_status := 'U';
1234 x_msg_count := 1;
1235 fnd_msg_pub.add_exc_msg
1236 ( p_pkg_name => 'PA_FP_PJI_INTG_PKG'
1237 ,p_procedure_name => 'blk_update_reporting_lines:Error Occured in plan_update' );
1238 print_msg(l_stage||'Error occured in blk_update_reporting_lines:Error Occured in plan_update ['||x_msg_data||']','Y');
1239 If g_debug_flag = 'Y' Then
1240 pa_debug.reset_err_stack;
1241 End If;
1242 -- Bug 4621171: Removed RAISE statement.
1243
1244 WHEN OTHERS THEN
1245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1246 x_msg_data := SQLCODE||SQLERRM;
1247 x_msg_count := 1;
1248 fnd_msg_pub.add_exc_msg
1249 ( p_pkg_name => 'PA_FP_PJI_INTG_PKG'
1250 ,p_procedure_name => 'update_reporting_lines' );
1251 print_msg(l_stage||'Error occured in blk_update_reporting_lines['|| substr(SQLERRM,1,240),'Y');
1252 If g_debug_flag = 'Y' Then
1253 pa_debug.reset_err_stack;
1254 End If;
1255 RAISE;
1256
1257 END blk_update_reporting_lines;
1258
1259 END PA_FP_PJI_INTG_PKG;