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