[Home] [Help]
PACKAGE BODY: APPS.PA_PLAN_MATRIX
Source
1 PACKAGE BODY Pa_Plan_Matrix AS
2 /* $Header: PARPLMXB.pls 120.2 2005/09/27 12:41:29 rnamburi noship $ */
3
4 -- Bug Fix: 4569365. Removed MRC code.
5 -- g_mrc_exception EXCEPTION; /* FPB2 */
6
7 -- NEW SEPARATE API FOR CALCULATION OF PERIOD NAME, START DATE
8 -- AND END DATE FOR PRECEDING AND SUCCEEDING PERIODS
9 -- This API needs to be separate because it may be called by
10 -- other APIs or WEB ADI
11
12 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
13
14 PROCEDURE Get_Period_Info(
15 p_bucketing_period_code IN VARCHAR2,
16 p_st_dt_4_st_pd IN DATE,
17 p_st_dt_4_end_pd IN DATE,
18 p_plan_period_type IN VARCHAR2,
19 p_project_id IN NUMBER,
20 p_budget_version_id IN NUMBER,
21 p_resource_assignment_id IN NUMBER,
22 p_transaction_currency_code IN VARCHAR2,
23 x_start_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
24 x_end_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
25 x_period_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
26 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
27 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
28 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
29 )
30 IS
31 -- Local Variable Declaration
32 l_max_pa_bdgt_st_dt DATE;
33 l_min_pa_bdgt_st_dt DATE;
34 l_plan_period_type VARCHAR2(30);
35 l_budget_version_id NUMBER;
36 l_resource_assignment_id NUMBER;
37 l_project_id NUMBER;
38 l_st_dt_4_st_pd DATE;
39 l_st_dt_4_end_pd DATE;
40 l_bucketing_period_code VARCHAR2(30);
41 l_transaction_currency_code VARCHAR2(30);
42 l_debug_mode VARCHAR2(30);
43
44 BEGIN
45 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
46 l_debug_mode := NVL(l_debug_mode, 'Y');
47 PA_DEBUG.Set_Curr_Function( p_function => 'Get_Period_Info',
48 p_debug_mode => l_debug_mode );
49 IF P_PA_DEBUG_MODE = 'Y' THEN
50 PA_DEBUG.g_err_stage := 'Entering Get_Period_Info and selecting ' ||
51 'min dates from budget lines';
52 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
53 END IF;
54 --Assigning values to variables
55 l_max_pa_bdgt_st_dt := NULL;
56 l_min_pa_bdgt_st_dt := NULL;
57 l_plan_period_type := p_plan_period_type;
58 l_budget_version_id := p_budget_version_id;
59 l_resource_assignment_id := p_resource_assignment_id;
60 l_project_id := p_project_id;
61 l_st_dt_4_st_pd := p_st_dt_4_st_pd;
62 l_st_dt_4_end_pd := p_st_dt_4_end_pd;
63 l_bucketing_period_code := p_bucketing_period_code;
64 l_transaction_currency_code := p_transaction_currency_code;
65
66 x_return_status := FND_API.G_RET_STS_SUCCESS;
67
68 -- Get the minimum of start date and maximum of end date for this
69 -- resource assignment id from the budget line table:
70
71 SELECT min(pa_bdgt.start_date),
72 max(pa_bdgt.start_date)
73 INTO l_min_pa_bdgt_st_dt,
74 l_max_pa_bdgt_st_dt
75 FROM pa_budget_lines pa_bdgt
76 WHERE pa_bdgt.resource_assignment_id = l_resource_assignment_id
77 AND pa_bdgt.TXN_CURRENCY_CODE = l_transaction_currency_code
78 AND pa_bdgt.bucketing_period_code IS NULL;
79
80 -- Getting the Preceding period start date, end date and period name
81 IF (l_bucketing_period_code = 'PD') THEN
82 IF ( l_plan_period_type = 'GL') THEN
83 BEGIN
84 SELECT inr1.period_name,
85 inr1.start_date,
86 inr1.end_date
87 INTO x_period_name,
88 x_start_date,
89 x_end_date
90 FROM
91 (
92 SELECT G.period_name,
93 G.start_date,
94 G.end_date
95 FROM
96 Gl_Periods G,
97 pa_implementations_all imp ,
98 pa_projects_all p,
99 gl_sets_of_books sob
100 WHERE
101 G.start_date < LEAST (NVL(l_min_pa_bdgt_st_dt, l_st_dt_4_st_pd), l_st_dt_4_st_pd ) AND
102 p.project_id = l_project_id AND
103 nvl(p.org_id,-99) = nvl(imp.org_id,-99) AND
104 imp.set_of_books_id = sob.set_of_books_id AND
105 G.period_set_name = imp.period_set_name AND
106 G.period_type = sob.accounted_period_type AND
107 ADJUSTMENT_PERIOD_FLAG = 'N'
108 ORDER BY G.Start_Date desc
109 ) inr1
110 WHERE Rownum < 2;
111 PA_DEBUG.Reset_Curr_Function;
112 RETURN;
113 EXCEPTION
114 WHEN NO_DATA_FOUND THEN
115 FND_MSG_PUB.add_exc_msg
116 ( p_pkg_name => 'PA_PLAN_MATRIX.Get_Period_Info'
117 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
118 IF P_PA_DEBUG_MODE = 'Y' THEN
119 PA_DEBUG.g_err_stage := 'No data found while trying to retrieve ' ||
120 'start date, end date and period name from GL_periods for PD-GL';
121 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
122 END IF;
123 x_return_status := FND_API.G_RET_STS_ERROR;
124 x_msg_data := 'PA_FP_INVALID_PROJECT_ID';
125 PA_DEBUG.Reset_Curr_Function;
126 RAISE;
127 END;
128 ELSIF ( l_plan_period_type = 'PA') THEN
129 BEGIN
130 SELECT inr1.period_name,
131 inr1.start_date,
132 inr1.end_date
133 INTO x_period_name,
134 x_start_date,
135 x_end_date
136 FROM
137 (
138 SELECT G.period_name,
139 G.start_date,
140 G.end_date
141 FROM
142 Gl_Periods G,
143 pa_implementations_all imp ,
144 pa_projects_all p
145 WHERE
146 G.start_date < LEAST (NVL(l_min_pa_bdgt_st_dt, l_st_dt_4_st_pd), l_st_dt_4_st_pd ) AND
147 p.project_id = l_project_id AND
148 nvl(p.org_id,-99) = nvl(imp.org_id,-99) AND
149 G.period_set_name = imp.period_set_name AND
150 G.period_type = imp.pa_period_type AND
151 ADJUSTMENT_PERIOD_FLAG = 'N'
152 ORDER BY G.Start_Date desc
153 ) inr1
154 WHERE Rownum < 2;
155 PA_DEBUG.Reset_Curr_Function;
156 RETURN;
157 EXCEPTION
158 WHEN NO_DATA_FOUND THEN
159 FND_MSG_PUB.add_exc_msg
160 ( p_pkg_name => 'PA_PLAN_MATRIX.Get_Period_Info'
161 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
162 IF P_PA_DEBUG_MODE = 'Y' THEN
163 PA_DEBUG.g_err_stage := 'No data found while trying to retrieve ' ||
164 'start date, end date and period name from GL_periods for PD-PA';
165 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
166 END IF;
167 x_return_status := FND_API.G_RET_STS_ERROR;
168 x_msg_data := 'PA_FP_INVALID_PROJECT_ID';
169 PA_DEBUG.Reset_Curr_Function;
170 RAISE;
171 END;
172 END IF;
173 ELSIF (l_bucketing_period_code = 'SD') THEN
174 IF ( l_plan_period_type = 'GL') THEN
175 BEGIN
176 SELECT G.period_name,
177 G.start_date,
178 G.end_date
179 INTO x_period_name,
180 x_start_date,
181 x_end_date
182 FROM
183 Gl_Periods G,
184 pa_implementations_all imp ,
185 pa_projects_all p ,
186 gl_sets_of_books sob
187 WHERE
188 G.start_date > GREATEST (NVL(l_max_pa_bdgt_st_dt, l_st_dt_4_end_pd) , l_st_dt_4_end_pd ) AND
189 p.project_id = l_project_id AND
190 nvl(p.org_id,-99) = nvl(imp.org_id,-99) AND
191 imp.set_of_books_id = sob.set_of_books_id AND
192 G.period_set_name = imp.period_set_name AND
193 G.period_type = sob.accounted_period_type AND
194 ADJUSTMENT_PERIOD_FLAG = 'N' AND
195 Rownum < 2
196 ORDER BY G.Start_Date;
197 PA_DEBUG.Reset_Curr_Function;
198 RETURN;
199 EXCEPTION
200 WHEN NO_DATA_FOUND THEN
201 FND_MSG_PUB.add_exc_msg
202 ( p_pkg_name => 'PA_PLAN_MATRIX.Get_Period_Info'
203 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
204 IF P_PA_DEBUG_MODE = 'Y' THEN
205 PA_DEBUG.g_err_stage := 'No data found while trying to retrieve ' ||
206 'start date, end date and period name from GL_periods for SD-GL';
207 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
208 END IF;
209 x_return_status := FND_API.G_RET_STS_ERROR;
210 x_msg_data := 'PA_FP_INVALID_PROJECT_ID';
211 PA_DEBUG.Reset_Curr_Function;
212 RAISE;
213 END;
214 ELSIF ( l_plan_period_type = 'PA') THEN
215 BEGIN
216 SELECT G.period_name,
217 G.start_date,
218 G.end_date
219 INTO x_period_name,
220 x_start_date,
221 x_end_date
222 FROM
223 Gl_Periods G,
224 pa_implementations_all imp ,
225 pa_projects_all p
226 WHERE
227 G.start_date > GREATEST (NVL(l_max_pa_bdgt_st_dt, l_st_dt_4_end_pd) , l_st_dt_4_end_pd ) AND
228 p.project_id = l_project_id AND
229 nvl(p.org_id,-99) = nvl(imp.org_id,-99) AND
230 G.period_set_name = imp.period_set_name AND
231 G.period_type = imp.pa_period_type AND
232 ADJUSTMENT_PERIOD_FLAG = 'N' AND
233 Rownum < 2
234 ORDER BY G.Start_Date;
235 PA_DEBUG.Reset_Curr_Function;
236 RETURN;
237 EXCEPTION
238 WHEN NO_DATA_FOUND THEN
239 FND_MSG_PUB.add_exc_msg
240 ( p_pkg_name => 'PA_PLAN_MATRIX.Get_Period_Info'
241 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
242 IF P_PA_DEBUG_MODE = 'Y' THEN
243 PA_DEBUG.g_err_stage := 'No data found while trying to retrieve ' ||
244 'start date, end date and period name from GL_periods for SD-PA';
245 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
246 END IF;
247 x_return_status := FND_API.G_RET_STS_ERROR;
248 x_msg_data := 'PA_FP_INVALID_PROJECT_ID';
249 PA_DEBUG.Reset_Curr_Function;
250 RAISE;
251 END;
252 END IF;
253 END IF;
254 EXCEPTION
255 WHEN OTHERS THEN
256 FND_MSG_PUB.add_exc_msg
257 ( p_pkg_name => 'PA_PLAN_MATRIX.Get_Period_Info'
258 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
259 IF P_PA_DEBUG_MODE = 'Y' THEN
260 PA_DEBUG.g_err_stage := 'Unexpected error in Get_Period_Info ';
261 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
262 END IF;
263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
264 PA_DEBUG.Reset_Curr_Function;
265 RAISE;
266 END Get_Period_Info;
267
268
269 -- NEW API FOR population of Budget Lines - Added by Vijay S Gautam
270 PROCEDURE Populate_Budget_Lines
271 (
272 p_bucketing_period_code IN VARCHAR2,
273 p_st_dt_4_st_pd IN DATE,
274 p_st_dt_4_end_pd IN DATE,
275 p_plan_period_type IN VARCHAR2,
276 p_project_id IN NUMBER,
277 p_budget_version_id IN NUMBER,
278 p_project_currency_code IN VARCHAR2,
279 p_projfunc_currency_code IN VARCHAR2,
280 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
281 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
282 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
283 )
284 IS
285 --Local Variable Declarations
286 --Added By Vijay Gautam
287 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
288 l_created_by NUMBER := FND_GLOBAL.USER_ID;
289 l_creation_date DATE := SYSDATE;
290 l_last_update_date DATE := l_creation_date;
291 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
292
293 l_plan_period_type VARCHAR2(30);
294 l_project_id NUMBER;
295 l_resource_assignment_id NUMBER;
296 l_transaction_currency_code VARCHAR2(30);
297 l_budget_version_id NUMBER;
298 l_bdgt_prec_per_name VARCHAR2(30);
299 l_bdgt_prec_per_st_dt DATE;
300 l_bdgt_prec_per_end_dt DATE;
301 l_bdgt_succ_per_name VARCHAR2(30);
302 l_bdgt_succ_per_st_dt DATE;
303 l_bdgt_succ_per_end_dt DATE;
304 l_st_dt_4_st_pd DATE;
305 l_st_dt_4_end_pd DATE;
306 l_bucketing_period_code VARCHAR2(30);
307 l_prec_func_raw_cost NUMBER;
308 l_prec_func_burdened_cost NUMBER;
309 l_prec_func_revenue NUMBER;
310 l_prec_func_curr_code VARCHAR2(30);
311 l_prec_txn_quantity NUMBER;
312 l_prec_txn_raw_cost NUMBER;
313 l_prec_txn_burdened_cost NUMBER;
314 l_prec_txn_revenue NUMBER;
315 l_prec_txn_curr_code VARCHAR2(30);
316 l_prec_proj_raw_cost NUMBER;
317 l_prec_proj_burdened_cost NUMBER;
318 l_prec_proj_revenue NUMBER;
319 l_prec_proj_curr_code VARCHAR2(30);
320 l_succ_func_raw_cost NUMBER;
321 l_succ_func_burdened_cost NUMBER;
322 l_succ_func_revenue NUMBER;
323 l_succ_func_curr_code VARCHAR2(30);
324 l_succ_txn_quantity NUMBER;
325 l_succ_txn_raw_cost NUMBER;
326 l_succ_txn_burdened_cost NUMBER;
327 l_succ_txn_revenue NUMBER;
328 l_succ_txn_curr_code VARCHAR2(30);
329 l_succ_proj_raw_cost NUMBER;
330 l_succ_proj_burdened_cost NUMBER;
331 l_succ_proj_revenue NUMBER;
332 l_succ_proj_curr_code VARCHAR2(30);
333 l_debug_mode VARCHAR2(30);
334
335 l_budget_line_id PA_BUDGET_LINES.BUDGET_LINE_ID%type; /* FPB2: MRC */
336 l_version_type pa_budget_versions.version_type%TYPE;
337 l_raw_cost_source pa_budget_lines.RAW_COST_SOURCE%TYPE;
338 l_bd_cost_source pa_budget_lines.RAW_COST_SOURCE%TYPE;
342 SELECT DISTINCT Resource_Assignment_Id,
339 l_rev_source pa_budget_lines.RAW_COST_SOURCE%TYPE;
340 l_qty_source pa_budget_lines.RAW_COST_SOURCE%TYPE;
341 CURSOR Main_Tmp_Cur IS
343 Source_Txn_Currency_Code
344 FROM Pa_Fin_Plan_Lines_Tmp;
345 BEGIN
346 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
347 l_debug_mode := NVL(l_debug_mode, 'Y');
348 PA_DEBUG.Set_Curr_Function( p_function => 'Populate_Budget_Lines',
349 p_debug_mode => l_debug_mode );
350
351 IF P_PA_DEBUG_MODE = 'Y' THEN
352 PA_DEBUG.g_err_stage := 'Entering Populate_Budget_Lines and selecting ' ||
353 'cost/revenue values from budget lines';
354 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
355 END IF;
356 --Local Variable Initialization
357 l_plan_period_type := p_plan_period_type;
358 l_project_id := p_project_id;
359 l_budget_version_id := p_budget_version_id;
360 l_resource_assignment_id := NULL;
361 l_transaction_currency_code := NULL;
362 l_bdgt_prec_per_name := NULL;
363 l_bdgt_prec_per_st_dt := NULL;
364 l_bdgt_prec_per_end_dt := NULL;
365 l_bdgt_succ_per_name := NULL;
366 l_bdgt_succ_per_st_dt := NULL;
367 l_bdgt_succ_per_end_dt := NULL;
368 l_st_dt_4_st_pd := p_st_dt_4_st_pd;
369 l_st_dt_4_end_pd := p_st_dt_4_end_pd;
370 l_bucketing_period_code := p_bucketing_period_code;
371 l_prec_func_raw_cost := NULL;
372 l_prec_func_burdened_cost := NULL;
373 l_prec_func_revenue := NULL;
374 l_prec_func_curr_code := NULL;
375 l_prec_txn_quantity := NULL;
376 l_prec_txn_raw_cost := NULL;
377 l_prec_txn_burdened_cost := NULL;
378 l_prec_txn_revenue := NULL;
379 l_prec_txn_curr_code := NULL;
380 l_prec_proj_raw_cost := NULL;
381 l_prec_proj_burdened_cost := NULL;
382 l_prec_proj_revenue := NULL;
383 l_prec_proj_curr_code := NULL;
384 l_succ_func_raw_cost := NULL;
385 l_succ_func_burdened_cost := NULL;
386 l_succ_func_revenue := NULL;
387 l_succ_func_curr_code := NULL;
388 l_succ_txn_quantity := NULL;
389 l_succ_txn_raw_cost := NULL;
390 l_succ_txn_burdened_cost := NULL;
391 l_succ_txn_revenue := NULL;
392 l_succ_txn_curr_code := NULL;
393 l_succ_proj_raw_cost := NULL;
394 l_succ_proj_burdened_cost := NULL;
395 l_succ_proj_revenue := NULL;
396 l_succ_proj_curr_code := NULL;
397
398 x_return_status := FND_API.G_RET_STS_SUCCESS;
399
400 SELECT NVL(version_type,'ALL') INTO
401 l_version_type FROM pa_budget_versions
402 where budget_version_id = p_budget_version_id;
403 l_qty_source := 'M';
404 l_raw_cost_source := NULL;
405 l_bd_cost_source := NULL;
406 l_rev_source := NULL;
407
408 IF l_version_type = 'ALL' THEN
409 l_raw_cost_source := 'M';
410 l_bd_cost_source := 'M';
411 l_rev_source := 'M';
412 ELSIF l_version_type = 'COST' THEN
413 l_raw_cost_source := 'M';
414 l_bd_cost_source := 'M';
415 ELSIF l_version_type = 'REVENUE' THEN
416 l_rev_source := 'M';
417 END IF;
418 FOR main_cur_rec IN MAIN_TMP_CUR
419 LOOP
420 -- Get the minimum of start date and maximum of end date for this
421 -- period profile id from the period profile table:
422
423 -- We already have it in the API as the parameter assigned to
424 -- these local variables
425
426 -- Start Date - l_st_dt_4_st_pd
427 -- End Date - l_st_dt_4_end_pd
428
429 -- Get The Period Name, Start Date and End Date from the GL_periods
430 -- Table for the minimum of start date and end date derived from the
431 -- pa_budget_lines table and Pa_Proj_Period_Profiles table.
432
433 -- Assigning values from cursor to the local variable
434 l_resource_assignment_id := main_cur_rec.resource_assignment_id;
435 l_transaction_currency_code := main_cur_rec.source_txn_currency_code;
436 IF (l_bucketing_period_code = 'PD') THEN
437
438 -- PE values need to be set to null to make sure that
439 -- new updates for PE values in budget_lines table go
440 -- smoothly depending on the new data in the temporary table
441
442 /* FPB2: MRC No changes done as no amount columns are being updated.
443 DO NOT ADD ANY AMOUNT COLUMNS TO THIS UPDATE. ELSE MAKE CALL TO MRC !!!!
444 */
445 UPDATE Pa_Budget_Lines
446 SET Bucketing_Period_Code = NULL
447 WHERE Pa_Budget_Lines.resource_assignment_id = main_cur_rec.resource_assignment_id
448 AND Pa_Budget_Lines.TXN_CURRENCY_CODE = main_cur_rec.source_txn_currency_code
449 AND ( Pa_Budget_Lines.Bucketing_period_code = 'PE' -- Bug 2810094. update the SE records where
450 OR -- start_date < period profile start period start date
451 (Pa_Budget_Lines.Bucketing_period_code = 'SE' AND -- with bucketing period code as null
452 Pa_Budget_Lines.start_date < p_st_dt_4_st_pd
453 )
454 );
455 -- Getting the Preceding period start date, end date and period name
456
457 Get_Period_Info
458 (
462 p_plan_period_type => l_plan_period_type,
459 p_bucketing_period_code => l_bucketing_period_code,
460 p_st_dt_4_st_pd => l_st_dt_4_st_pd,
461 p_st_dt_4_end_pd => l_st_dt_4_end_pd,
463 p_project_id => l_project_id,
464 p_budget_version_id => l_budget_version_id,
465 p_resource_assignment_id => l_resource_assignment_id,
466 p_transaction_currency_code => l_transaction_currency_code,
467 x_start_date => l_bdgt_prec_per_st_dt,
468 x_end_date => l_bdgt_prec_per_end_dt,
469 x_period_name => l_bdgt_prec_per_name,
470 x_return_status =>x_return_status,
471 x_msg_count => x_msg_count,
472 x_msg_data => x_msg_data
473 );
474
475 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
476 --DBMS_OUTPUT.PUT_LINE('Error in call to Get Period Info');
477 RETURN;
478 END IF;
479 -- Selecting the revenue and cost values from the temporary
480 -- table for preceding period
481
482 -- Selecting for Transaction Currency
483 BEGIN
484 SELECT raw_cost,
485 burdened_cost,
486 revenue,
487 currency_code,
488 quantity
489 INTO l_prec_txn_raw_cost,
490 l_prec_txn_burdened_cost,
491 l_prec_txn_revenue,
492 l_prec_txn_curr_code,
493 l_prec_txn_quantity
494 FROM PA_FIN_PLAN_LINES_TMP
495 WHERE bucketing_period_code = l_bucketing_period_code
496 AND resource_assignment_id = main_cur_rec.resource_assignment_id
497 AND currency_type = 'TRANSACTION'
498 AND source_txn_currency_code = main_cur_rec.source_txn_currency_code;
499 EXCEPTION
500 WHEN NO_DATA_FOUND THEN
501 l_prec_txn_raw_cost := NULL;
502 l_prec_txn_burdened_cost := NULL;
503 l_prec_txn_revenue := NULL;
504 l_prec_txn_curr_code := main_cur_rec.source_txn_currency_code;
505 l_prec_txn_quantity := NULL;
506 END;
507
508 -- Selecting for Project Currency
509 BEGIN
510 SELECT raw_cost,
511 burdened_cost,
512 revenue,
513 currency_code
514 INTO l_prec_proj_raw_cost,
515 l_prec_proj_burdened_cost,
516 l_prec_proj_revenue,
517 l_prec_proj_curr_code
518 FROM PA_FIN_PLAN_LINES_TMP
519 WHERE bucketing_period_code = l_bucketing_period_code
520 AND resource_assignment_id = main_cur_rec.resource_assignment_id
521 AND currency_type = 'PROJECT'
522 AND source_txn_currency_code = main_cur_rec.source_txn_currency_code ;
523 EXCEPTION
524 WHEN NO_DATA_FOUND THEN
525 l_prec_proj_raw_cost := NULL;
526 l_prec_proj_burdened_cost := NULL;
527 l_prec_proj_revenue := NULL;
528 l_prec_proj_curr_code := p_project_currency_code;
529 END;
530
531 -- Selecting for Project Functional Currency
532 BEGIN
533 SELECT raw_cost,
534 burdened_cost,
535 revenue,
536 currency_code
537 INTO l_prec_func_raw_cost,
538 l_prec_func_burdened_cost,
539 l_prec_func_revenue,
540 l_prec_func_curr_code
541 FROM PA_FIN_PLAN_LINES_TMP
542 WHERE bucketing_period_code = l_bucketing_period_code
543 AND resource_assignment_id = main_cur_rec.resource_assignment_id
544 AND currency_type = 'PROJ_FUNCTIONAL'
545 AND source_txn_currency_code = main_cur_rec.source_txn_currency_code;
546 EXCEPTION
547 WHEN NO_DATA_FOUND THEN
548 l_prec_func_raw_cost := NULL;
549 l_prec_func_burdened_cost := NULL;
550 l_prec_func_revenue := NULL;
551 l_prec_func_curr_code := p_projfunc_currency_code;
552 END;
553 -- Updating the budget line table to store the values of preceding buckets
554 BEGIN
555
556 l_budget_line_id := Null; /* FPB2 */
557 -- updation of amount has been commented for bug#2817407
558 UPDATE Pa_Budget_Lines
559 SET
560 Period_Name = l_bdgt_prec_per_name,
561 Start_Date = l_bdgt_prec_per_st_dt,
562 End_Date = l_bdgt_prec_per_end_dt,
563 -- Quantity = l_prec_txn_quantity,
564 -- Raw_cost = l_prec_func_raw_cost,
565 -- Burdened_cost = l_prec_func_burdened_cost,
566 -- Revenue = l_prec_func_revenue,
567 -- Txn_Raw_cost = l_prec_txn_raw_cost,
568 -- Txn_Burdened_cost = l_prec_txn_burdened_cost,
569 -- Txn_Revenue = l_prec_txn_revenue,
570 -- Project_Raw_cost = l_prec_proj_raw_cost,
574 LAST_UPDATED_BY = l_last_updated_by,
571 -- Project_Burdened_cost = l_prec_proj_burdened_cost,
572 -- Project_Revenue = l_prec_proj_revenue,
573 LAST_UPDATE_LOGIN = l_last_update_login,
575 LAST_UPDATE_DATE = l_last_update_date
576 WHERE resource_assignment_id = main_cur_rec.resource_assignment_id
577 AND Pa_Budget_Lines.TXN_CURRENCY_CODE = main_cur_rec.source_txn_currency_code
578 AND Pa_Budget_Lines.bucketing_period_code = l_bucketing_period_code
579 AND budget_version_id = l_budget_version_id
580 returning budget_line_id into l_budget_line_id; /* FPB2: MRC */
581
582 IF SQL%ROWCOUNT = 0 THEN
583
584 select pa_budget_lines_s.nextval
585 into l_budget_line_id
586 from dual;
587
588 INSERT INTO PA_BUDGET_LINES
589 (budget_line_id, /* FPB2 */
590 budget_version_id, /* FPB2 */
591 Resource_Assignment_Id,
592 Start_Date,
593 End_Date,
594 Period_Name,
595 Quantity,
596 Raw_cost,
597 Burdened_cost ,
598 Revenue,
599 projfunc_currency_code,
600 Txn_Raw_cost,
601 Txn_Burdened_cost,
602 Txn_Revenue,
603 txn_currency_code,
604 Project_Raw_cost,
605 Project_Burdened_cost,
606 Project_Revenue,
607 project_currency_code,
608 bucketing_period_code,
609 CREATION_DATE ,
610 CREATED_BY ,
611 LAST_UPDATE_LOGIN ,
612 LAST_UPDATED_BY ,
613 LAST_UPDATE_DATE,
614 RAW_COST_SOURCE,
615 BURDENED_COST_SOURCE,
616 QUANTITY_SOURCE,
617 REVENUE_SOURCE)
618 VALUES (l_budget_line_id, /* FPB2 */
619 l_budget_version_id, /* FPB2 */
620 main_cur_rec.resource_assignment_id,
621 l_bdgt_prec_per_st_dt,
622 l_bdgt_prec_per_end_dt,
623 l_bdgt_prec_per_name,
624 l_prec_txn_quantity,
625 l_prec_func_raw_cost ,
626 l_prec_func_burdened_cost ,
627 l_prec_func_revenue,
628 l_prec_func_curr_code,
629 l_prec_txn_raw_cost,
630 l_prec_txn_burdened_cost ,
631 l_prec_txn_revenue ,
632 l_prec_txn_curr_code,
633 l_prec_proj_raw_cost ,
634 l_prec_proj_burdened_cost,
635 l_prec_proj_revenue ,
636 l_prec_proj_curr_code,
637 l_bucketing_period_code,
638 l_creation_date ,
639 l_created_by ,
640 l_last_update_login ,
641 l_last_updated_by ,
642 l_last_update_date,
643 l_raw_cost_source,
644 l_bd_cost_source,
645 l_qty_source,
646 l_rev_source );
647
648 -- Bug Fix: 4569365. Removed MRC code.
649 /* FPB2: MRC */
650 /*
651 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS IS NULL THEN
652 PA_MRC_FINPLAN.CHECK_MRC_INSTALL
653 (x_return_status => x_return_status,
654 x_msg_count => x_msg_count,
655 x_msg_data => x_msg_data);
656 END IF;
657
658 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS AND
659 PA_MRC_FINPLAN.G_FINPLAN_MRC_OPTION_CODE = 'A' THEN
660 PA_MRC_FINPLAN.MAINTAIN_ONE_MC_BUDGET_LINE
661 (p_budget_line_id => l_budget_line_id,
662 p_budget_version_id => l_budget_version_id,
663 p_action => PA_MRC_FINPLAN.G_ACTION_INSERT,
664 x_return_status => x_return_status,
665 x_msg_count => x_msg_count,
666 x_msg_data => x_msg_data);
667 END IF;
668
669 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
670 RAISE g_mrc_exception;
671 END IF;
675 EXCEPTION
672 */
673
674 END IF;
676 WHEN OTHERS THEN
677 FND_MSG_PUB.add_exc_msg
678 ( p_pkg_name => 'PA_PLAN_MATRIX.Populate_Budget_Lines'
679 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
680 IF P_PA_DEBUG_MODE = 'Y' THEN
681 PA_DEBUG.g_err_stage := 'EXCEPTION while trying to insert ' ||
682 'PD data in budget lines table';
683 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
684 END IF;
685 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
686 p_msg_name => 'PA_FP_UNEX_ERR_INS_BDGT_LNS');
687 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
688 x_msg_data := 'PA_FP_UNEX_ERR_INS_BDGT_LNS';
689 PA_DEBUG.Reset_Curr_Function;
690 RAISE;
691 END;
692 -- Updating the Budget Line Tables to store the Values for
693 -- Preceding Entered l_st_dt_4_st_pd is the start date of
694 -- the start period from the period profile table
695
696 /* FPB2 : MRC DO NOT ADD AMOUNT COLUMNS TO THE UPDATE OR CONSIDER MRC IMPACT !!! */
697
698 UPDATE Pa_Budget_Lines
699 SET Bucketing_Period_Code = 'PE'
700 WHERE Pa_Budget_Lines.START_DATE < l_st_dt_4_st_pd
701 AND Pa_Budget_Lines.resource_assignment_id = main_cur_rec.resource_assignment_id
702 AND Pa_Budget_Lines.TXN_CURRENCY_CODE = main_cur_rec.source_txn_currency_code
703 AND Pa_Budget_Lines.Bucketing_period_code IS NULL;
704
705 END IF; -- End of if for bucketing period code PD
706
707 IF (l_bucketing_period_code = 'SD') THEN
708
709 -- SE values need to be set to null to make sure that
710 -- new updates for SE values in budget_lines table go
711 -- smoothly depending on the new data in the temporary table
712
713 /* FPB2 : Please note that if the following update is modified to udpate
714 amount columns MRC api call needs to be made appropirately */
715
716 UPDATE Pa_Budget_Lines
717 SET Bucketing_Period_Code = NULL
718 WHERE Pa_Budget_Lines.resource_assignment_id = main_cur_rec.resource_assignment_id
719 AND Pa_Budget_Lines.TXN_CURRENCY_CODE = main_cur_rec.source_txn_currency_code
720 AND ( Pa_Budget_Lines.Bucketing_period_code = 'SE' -- Bug 2810094. update the PE records where
721 OR -- start_date > period profile end period start date
722 ( -- with bucketing period code as null
723 Pa_Budget_Lines.Bucketing_period_code = 'PE'
724 AND Pa_Budget_Lines.Start_Date > l_st_dt_4_end_pd
725 )
726 );
727 -- Getting the Succeeding period start date, end date and period name
728
729 Get_Period_Info
730 (
731 p_bucketing_period_code => l_bucketing_period_code,
732 p_st_dt_4_st_pd => l_st_dt_4_st_pd,
733 p_st_dt_4_end_pd => l_st_dt_4_end_pd,
734 p_plan_period_type => l_plan_period_type,
735 p_project_id => l_project_id,
736 p_budget_version_id => l_budget_version_id,
737 p_resource_assignment_id => l_resource_assignment_id,
738 p_transaction_currency_code => l_transaction_currency_code,
739 x_start_date => l_bdgt_succ_per_st_dt,
740 x_end_date => l_bdgt_succ_per_end_dt,
741 x_period_name => l_bdgt_succ_per_name,
742 x_return_status =>x_return_status,
743 x_msg_count => x_msg_count,
744 x_msg_data => x_msg_data
745 );
746
747 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
748 --DBMS_OUTPUT.PUT_LINE('Error in call to Get Period Info');
749 PA_DEBUG.Reset_Curr_Function;
750 RETURN;
751 END IF;
752 -- Selecting the revenue and cost values from the temporary
753 -- table for succeeding period
754
755 -- Selecting for Transaction Currency
756 BEGIN
757 SELECT raw_cost,
758 burdened_cost,
759 revenue,
760 currency_code,
761 quantity
762 INTO l_succ_txn_raw_cost,
763 l_succ_txn_burdened_cost,
764 l_succ_txn_revenue,
765 l_succ_txn_curr_code,
766 l_succ_txn_quantity
767 FROM PA_FIN_PLAN_LINES_TMP
768 WHERE bucketing_period_code = l_bucketing_period_code
769 AND resource_assignment_id = main_cur_rec.resource_assignment_id
770 AND currency_type = 'TRANSACTION'
771 AND source_txn_currency_code = main_cur_rec.source_txn_currency_code;
772 EXCEPTION
773 WHEN NO_DATA_FOUND THEN
774 l_succ_txn_raw_cost := NULL;
775 l_succ_txn_burdened_cost := NULL;
776 l_succ_txn_revenue := NULL;
780
777 l_succ_txn_curr_code := main_cur_rec.source_txn_currency_code;
778 l_succ_txn_quantity := NULL;
779 END;
781 -- Selecting for Project Currency
782 BEGIN
783 SELECT raw_cost,
784 burdened_cost,
785 revenue,
786 currency_code
787 INTO l_succ_proj_raw_cost,
788 l_succ_proj_burdened_cost,
789 l_succ_proj_revenue,
790 l_succ_proj_curr_code
791 FROM PA_FIN_PLAN_LINES_TMP
792 WHERE bucketing_period_code = l_bucketing_period_code
793 AND resource_assignment_id = main_cur_rec.resource_assignment_id
794 AND currency_type = 'PROJECT'
795 AND source_txn_currency_code = main_cur_rec.source_txn_currency_code;
796 EXCEPTION
797 WHEN NO_DATA_FOUND THEN
798 l_succ_proj_raw_cost := NULL;
799 l_succ_proj_burdened_cost := NULL;
800 l_succ_proj_revenue := NULL;
801 l_succ_proj_curr_code := p_project_currency_code;
802 END;
803
804 -- Selecting for Project Functional Currency
805 BEGIN
806 SELECT raw_cost,
807 burdened_cost,
808 revenue,
809 currency_code
810 INTO l_succ_func_raw_cost,
811 l_succ_func_burdened_cost,
812 l_succ_func_revenue,
813 l_succ_func_curr_code
814 FROM PA_FIN_PLAN_LINES_TMP
815 WHERE bucketing_period_code = l_bucketing_period_code
816 AND resource_assignment_id = main_cur_rec.resource_assignment_id
817 AND currency_type = 'PROJ_FUNCTIONAL'
818 AND source_txn_currency_code = main_cur_rec.source_txn_currency_code;
819 EXCEPTION
820 WHEN NO_DATA_FOUND THEN
821 l_succ_func_raw_cost := NULL;
822 l_succ_func_burdened_cost := NULL;
823 l_succ_func_revenue := NULL;
824 l_succ_func_curr_code := p_projfunc_currency_code;
825 END;
826 -- Updating the budget line table to store the values of succeeding buckets
827 BEGIN
828
829 l_budget_line_id := Null; /* FPB2 */
830 -- updation of amount has been commented for bug#2817407
831 UPDATE Pa_Budget_Lines
832 SET
833 Period_Name = l_bdgt_succ_per_name,
834 Start_Date = l_bdgt_succ_per_st_dt,
835 End_Date = l_bdgt_succ_per_end_dt,
836 -- Quantity = l_succ_txn_quantity,
837 -- Raw_cost = l_succ_func_raw_cost,
838 -- Burdened_cost = l_succ_func_burdened_cost,
839 -- Revenue = l_succ_func_revenue,
840 -- Txn_Raw_cost = l_succ_txn_raw_cost,
841 -- Txn_Burdened_cost = l_succ_txn_burdened_cost,
842 -- Txn_Revenue = l_succ_txn_revenue,
843 -- Project_Raw_cost = l_succ_proj_raw_cost,
844 -- Project_Burdened_cost = l_succ_proj_burdened_cost,
845 -- Project_Revenue = l_succ_proj_revenue,
846 LAST_UPDATE_LOGIN = l_last_update_login,
847 LAST_UPDATED_BY = l_last_updated_by,
848 LAST_UPDATE_DATE = l_last_update_date
849 WHERE resource_assignment_id = main_cur_rec.resource_assignment_id
850 AND Pa_Budget_Lines.TXN_CURRENCY_CODE = main_cur_rec.source_txn_currency_code
851 AND Pa_Budget_Lines.bucketing_period_code = l_bucketing_period_code
852 AND budget_version_id = l_budget_version_id
853 returning budget_line_id into l_budget_line_id;
854
855 -- Bug Fix: 4569365. Removed MRC code.
856 /* FPB2: MRC */
857 IF SQL%ROWCOUNT <> 0 THEN
858 NULL;
859 /*
860 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS IS NULL THEN
861 PA_MRC_FINPLAN.CHECK_MRC_INSTALL
862 (x_return_status => x_return_status,
863 x_msg_count => x_msg_count,
864 x_msg_data => x_msg_data);
865 END IF;
866
867 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS AND
868 PA_MRC_FINPLAN.G_FINPLAN_MRC_OPTION_CODE = 'A' THEN
869 PA_MRC_FINPLAN.MAINTAIN_ONE_MC_BUDGET_LINE
870 (p_budget_line_id => l_budget_line_id,
871 p_budget_version_id => l_budget_version_id,
872 p_action => PA_MRC_FINPLAN.G_ACTION_UPDATE,
873 x_return_status => x_return_status,
874 x_msg_count => x_msg_count,
875 x_msg_data => x_msg_data);
876 END IF;
877
878 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
879 RAISE g_mrc_exception;
880 END IF;
881 */
885 into l_budget_line_id
882 ELSE
883 /*IF SQL%ROWCOUNT = 0 THEN*/
884 select pa_budget_lines_s.nextval
886 from dual;
887
888 INSERT INTO PA_BUDGET_LINES
889 (Budget_Line_id, /* FPB2 */
890 Budget_Version_id, /* FPB2 */
891 Resource_Assignment_Id,
892 Start_Date,
893 End_Date,
894 Period_Name,
895 Quantity,
896 Raw_cost,
897 Burdened_cost ,
898 Revenue,
899 projfunc_currency_code,
900 Txn_Raw_cost,
901 Txn_Burdened_cost,
902 Txn_Revenue,
903 txn_currency_code,
904 Project_Raw_cost,
905 Project_Burdened_cost,
906 Project_Revenue,
907 project_currency_code,
908 bucketing_period_code,
909 CREATION_DATE ,
910 CREATED_BY ,
911 LAST_UPDATE_LOGIN ,
912 LAST_UPDATED_BY ,
913 LAST_UPDATE_DATE,
914 RAW_COST_SOURCE,
915 BURDENED_COST_SOURCE,
916 QUANTITY_SOURCE,
917 REVENUE_SOURCE)
918 VALUES (l_budget_line_id, /* FPB2 */
919 l_budget_version_id, /* FPB2 */
920 main_cur_rec.resource_assignment_id,
921 l_bdgt_succ_per_st_dt,
922 l_bdgt_succ_per_end_dt,
923 l_bdgt_succ_per_name,
924 l_succ_txn_quantity,
925 l_succ_func_raw_cost ,
926 l_succ_func_burdened_cost ,
927 l_succ_func_revenue,
928 l_succ_func_curr_code,
929 l_succ_txn_raw_cost,
930 l_succ_txn_burdened_cost ,
931 l_succ_txn_revenue ,
932 l_succ_txn_curr_code,
933 l_succ_proj_raw_cost ,
934 l_succ_proj_burdened_cost,
935 l_succ_proj_revenue ,
936 l_succ_proj_curr_code,
937 l_bucketing_period_code,
938 l_creation_date ,
939 l_created_by ,
940 l_last_update_login ,
941 l_last_updated_by ,
942 l_last_update_date,
943 l_raw_cost_source,
944 l_bd_cost_source,
945 l_qty_source,
946 l_rev_source );
947
948 -- Bug Fix: 4569365. Removed MRC code.
949 /* FPB2: MRC */
950 /*
951 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS IS NULL THEN
952 PA_MRC_FINPLAN.CHECK_MRC_INSTALL
953 (x_return_status => x_return_status,
954 x_msg_count => x_msg_count,
955 x_msg_data => x_msg_data);
956 END IF;
957
958 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS AND
959 PA_MRC_FINPLAN.G_FINPLAN_MRC_OPTION_CODE = 'A' THEN
960 PA_MRC_FINPLAN.MAINTAIN_ONE_MC_BUDGET_LINE
961 (p_budget_line_id => l_budget_line_id,
962 p_budget_version_id => l_budget_version_id,
963 p_action => PA_MRC_FINPLAN.G_ACTION_INSERT,
964 x_return_status => x_return_status,
965 x_msg_count => x_msg_count,
966 x_msg_data => x_msg_data);
967 END IF;
968
969 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
970 RAISE g_mrc_exception;
971 END IF;
972 */
973
974 END IF;
975 EXCEPTION
976 WHEN OTHERS THEN
977 FND_MSG_PUB.add_exc_msg
978 ( p_pkg_name => 'PA_PLAN_MATRIX.Populate_Budget_Lines'
979 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
980 IF P_PA_DEBUG_MODE = 'Y' THEN
981 PA_DEBUG.g_err_stage := 'Exception while trying to insert ' ||
982 'SD data in budget lines table';
983 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
984 END IF;
985 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
986 p_msg_name => 'PA_FP_UNEX_ERR_INS_BDGT_LNS');
987 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
988 x_msg_data := 'PA_FP_UNEX_ERR_INS_BDGT_LNS';
989 PA_DEBUG.Reset_Curr_Function;
990 RAISE;
991 END;
992
996
993 -- Updating the Budget Line Tables to store the Values for
994 -- Succeeding Entered l_st_dt_4_end_pd is the start date of
995 -- the end period from the period profile table
997 UPDATE Pa_Budget_Lines
998 SET Bucketing_Period_Code = 'SE'
999 WHERE Pa_Budget_Lines.START_DATE > l_st_dt_4_end_pd
1000 AND Pa_Budget_Lines.resource_assignment_id = main_cur_rec.resource_assignment_id
1001 AND Pa_Budget_Lines.TXN_CURRENCY_CODE = main_cur_rec.source_txn_currency_code
1002 AND Pa_Budget_Lines.Bucketing_period_code IS NULL;
1003 END IF; -- End of if for bucketing period code SD
1004 END LOOP;
1005 EXCEPTION
1006 WHEN OTHERS THEN
1007 FND_MSG_PUB.add_exc_msg
1008 ( p_pkg_name => 'PA_PLAN_MATRIX.Populate_Budget_Lines'
1009 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
1010 IF P_PA_DEBUG_MODE = 'Y' THEN
1011 PA_DEBUG.g_err_stage := 'Unexpected error in Populate_Budget_Lines ';
1012 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1013 END IF;
1014 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1015 PA_DEBUG.Reset_Curr_Function;
1016 RAISE;
1017 END Populate_Budget_Lines;
1018
1019 PROCEDURE Maintain_Plan_Matrix(
1020 p_amount_type_tab IN pa_plan_matrix.amount_type_tabtyp,
1021 p_period_profile_id IN NUMBER,
1022 p_prior_period_flag IN VARCHAR2,
1023 p_commit_flag IN VARCHAR2,
1024 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1025 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1026 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1027 p_budget_version_id IN NUMBER,
1028 p_project_id IN NUMBER,
1029 p_debug_mode IN VARCHAR2,
1030 p_add_msg_in_stack IN VARCHAR2,
1031 p_calling_module IN VARCHAR2) IS
1032
1033 l_start_period_name PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1034 l_st_dt_4_st_pd DATE;
1035 l_end_period_name PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1036 l_st_dt_4_end_pd DATE;
1037 l_period_name1 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1038 l_period_name2 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1039 l_period_name3 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1040 l_period_name4 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1041 l_period_name5 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1042 l_period_name6 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1043 l_period_name7 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1044 l_period_name8 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1045 l_period_name9 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1046 l_period_name10 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1047 l_period_name11 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1048 l_period_name12 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1049 l_period_name13 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1050 l_period_name14 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1051 l_period_name15 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1052 l_period_name16 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1053 l_period_name17 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1054 l_period_name18 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1055 l_period_name19 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1056 l_period_name20 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1057 l_period_name21 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1058 l_period_name22 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1059 l_period_name23 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1060 l_period_name24 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1061 l_period_name25 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1062 l_period_name26 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1063 l_period_name27 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1064 l_period_name28 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1065 l_period_name29 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1066 l_period_name30 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1067 l_period_name31 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1068 l_period_name32 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1069 l_period_name33 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1070 l_period_name34 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1071 l_period_name35 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1072 l_period_name36 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1073 l_period_name37 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1074 l_period_name38 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1075 l_period_name39 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1076 l_period_name40 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1077 l_period_name41 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1078 l_period_name42 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1079 l_period_name43 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1080 l_period_name44 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1081 l_period_name45 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1082 l_period_name46 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1083 l_period_name47 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1084 l_period_name48 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1085 l_period_name49 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1086 l_period_name50 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1087 l_period_name51 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1088 l_period_name52 PA_BUDGET_LINES.PERIOD_NAME%TYPE;
1089 l_period1_start_date DATE;
1090 l_period2_start_date DATE;
1091 l_period3_start_date DATE;
1092 l_period4_start_date DATE;
1093 l_period5_start_date DATE;
1094 l_period6_start_date DATE;
1098 l_period10_start_date DATE;
1095 l_period7_start_date DATE;
1096 l_period8_start_date DATE;
1097 l_period9_start_date DATE;
1099 l_period11_start_date DATE;
1100 l_period12_start_date DATE;
1101 l_period13_start_date DATE;
1102 l_period14_start_date DATE;
1103 l_period15_start_date DATE;
1104 l_period16_start_date DATE;
1105 l_period17_start_date DATE;
1106 l_period18_start_date DATE;
1107 l_period19_start_date DATE;
1108 l_period20_start_date DATE;
1109 l_period21_start_date DATE;
1110 l_period22_start_date DATE;
1111 l_period23_start_date DATE;
1112 l_period24_start_date DATE;
1113 l_period25_start_date DATE;
1114 l_period26_start_date DATE;
1115 l_period27_start_date DATE;
1116 l_period28_start_date DATE;
1117 l_period29_start_date DATE;
1118 l_period30_start_date DATE;
1119 l_period31_start_date DATE;
1120 l_period32_start_date DATE;
1121 l_period33_start_date DATE;
1122 l_period34_start_date DATE;
1123 l_period35_start_date DATE;
1124 l_period36_start_date DATE;
1125 l_period37_start_date DATE;
1126 l_period38_start_date DATE;
1127 l_period39_start_date DATE;
1128 l_period40_start_date DATE;
1129 l_period41_start_date DATE;
1130 l_period42_start_date DATE;
1131 l_period43_start_date DATE;
1132 l_period44_start_date DATE;
1133 l_period45_start_date DATE;
1134 l_period46_start_date DATE;
1135 l_period47_start_date DATE;
1136 l_period48_start_date DATE;
1137 l_period49_start_date DATE;
1138 l_period50_start_date DATE;
1139 l_period51_start_date DATE;
1140 l_period52_start_date DATE;
1141
1142
1143 l_amount_tab1 PA_PLSQL_DATATYPES.NumTabTyp;
1144 l_amount_tab2 PA_PLSQL_DATATYPES.NumTabTyp;
1145 l_amount_tab3 PA_PLSQL_DATATYPES.NumTabTyp;
1146 l_amount_tab4 PA_PLSQL_DATATYPES.NumTabTyp;
1147 l_amount_tab5 PA_PLSQL_DATATYPES.NumTabTyp;
1148 l_amount_tab6 PA_PLSQL_DATATYPES.NumTabTyp;
1149 l_amount_tab7 PA_PLSQL_DATATYPES.NumTabTyp;
1150 l_amount_tab8 PA_PLSQL_DATATYPES.NumTabTyp;
1151 l_amount_tab9 PA_PLSQL_DATATYPES.NumTabTyp;
1152 l_amount_tab10 PA_PLSQL_DATATYPES.NumTabTyp;
1153
1154 l_amount_tab11 PA_PLSQL_DATATYPES.NumTabTyp;
1155 l_amount_tab12 PA_PLSQL_DATATYPES.NumTabTyp;
1156 l_amount_tab13 PA_PLSQL_DATATYPES.NumTabTyp;
1157 l_amount_tab14 PA_PLSQL_DATATYPES.NumTabTyp;
1158 l_amount_tab15 PA_PLSQL_DATATYPES.NumTabTyp;
1159 l_amount_tab16 PA_PLSQL_DATATYPES.NumTabTyp;
1160 l_amount_tab17 PA_PLSQL_DATATYPES.NumTabTyp;
1161 l_amount_tab18 PA_PLSQL_DATATYPES.NumTabTyp;
1162 l_amount_tab19 PA_PLSQL_DATATYPES.NumTabTyp;
1163 l_amount_tab20 PA_PLSQL_DATATYPES.NumTabTyp;
1164
1165
1166 l_amount_tab21 PA_PLSQL_DATATYPES.NumTabTyp;
1167 l_amount_tab22 PA_PLSQL_DATATYPES.NumTabTyp;
1168 l_amount_tab23 PA_PLSQL_DATATYPES.NumTabTyp;
1169 l_amount_tab24 PA_PLSQL_DATATYPES.NumTabTyp;
1170 l_amount_tab25 PA_PLSQL_DATATYPES.NumTabTyp;
1171
1172 l_amount_tab26 PA_PLSQL_DATATYPES.NumTabTyp;
1173 l_amount_tab27 PA_PLSQL_DATATYPES.NumTabTyp;
1174 l_amount_tab28 PA_PLSQL_DATATYPES.NumTabTyp;
1175 l_amount_tab29 PA_PLSQL_DATATYPES.NumTabTyp;
1176 l_amount_tab30 PA_PLSQL_DATATYPES.NumTabTyp;
1177
1178 l_amount_tab31 PA_PLSQL_DATATYPES.NumTabTyp;
1179 l_amount_tab32 PA_PLSQL_DATATYPES.NumTabTyp;
1180 l_amount_tab33 PA_PLSQL_DATATYPES.NumTabTyp;
1181 l_amount_tab34 PA_PLSQL_DATATYPES.NumTabTyp;
1182 l_amount_tab35 PA_PLSQL_DATATYPES.NumTabTyp;
1183 l_amount_tab36 PA_PLSQL_DATATYPES.NumTabTyp;
1184 l_amount_tab37 PA_PLSQL_DATATYPES.NumTabTyp;
1185 l_amount_tab38 PA_PLSQL_DATATYPES.NumTabTyp;
1186 l_amount_tab39 PA_PLSQL_DATATYPES.NumTabTyp;
1187 l_amount_tab40 PA_PLSQL_DATATYPES.NumTabTyp;
1188
1189
1190 l_amount_tab41 PA_PLSQL_DATATYPES.NumTabTyp;
1191 l_amount_tab42 PA_PLSQL_DATATYPES.NumTabTyp;
1192 l_amount_tab43 PA_PLSQL_DATATYPES.NumTabTyp;
1193 l_amount_tab44 PA_PLSQL_DATATYPES.NumTabTyp;
1194 l_amount_tab45 PA_PLSQL_DATATYPES.NumTabTyp;
1195 l_amount_tab46 PA_PLSQL_DATATYPES.NumTabTyp;
1196 l_amount_tab47 PA_PLSQL_DATATYPES.NumTabTyp;
1197 l_amount_tab48 PA_PLSQL_DATATYPES.NumTabTyp;
1198 l_amount_tab49 PA_PLSQL_DATATYPES.NumTabTyp;
1199 l_amount_tab50 PA_PLSQL_DATATYPES.NumTabTyp;
1200
1201 l_amount_tab51 PA_PLSQL_DATATYPES.NumTabTyp;
1202 l_amount_tab52 PA_PLSQL_DATATYPES.NumTabTyp;
1203
1204 l_pd_name_map_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1205 l_st_date_map_tab PA_PLSQL_DATATYPES.DateTabTyp;
1206
1207 l_res_asg_id_tab PA_PLSQL_DATATYPES.NumTabTyp;
1208 l_obj_id_tab PA_PLSQL_DATATYPES.NumTabTyp;
1209 l_obj_type_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1210 l_amt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1211 l_amt_subtype_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1212 l_amt_type_id_tab PA_PLSQL_DATATYPES.NumTabTyp;
1213 l_amt_subtype_id_tab PA_PLSQL_DATATYPES.NumTabTyp;
1214 l_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1215 l_currency_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1216 l_parent_assignment_id_tab PA_PLSQL_DATATYPES.NumTabTyp;
1217
1218 l_res_asg_id NUMBER(15);
1219 l_obj_id NUMBER(15);
1220 l_obj_type_code VARCHAR2(30);
1221 l_amt_type_code VARCHAR2(30);
1222 l_amt_subtype_code VARCHAR2(30);
1223 l_amt_type_id NUMBER(15);
1227 l_prev_amt NUMBER;
1224 l_amt_subtype_id NUMBER(15);
1225 l_currency_code VARCHAR2(30);
1226 l_currency_type VARCHAR2(30);
1228 l_next_amt NUMBER;
1229 l_prior_amt NUMBER;
1230
1231 l_old_qty_fin_plan_tmp NUMBER;
1232 l_old_raw_cost_fin_plan_tmp NUMBER;
1233 l_old_brd_cost_fin_plan_tmp NUMBER;
1234 l_old_revenue_fin_plan_tmp NUMBER;
1235
1236 l_pd_amt1 NUMBER;
1237 l_pd_amt2 NUMBER;
1238 l_pd_amt3 NUMBER;
1239 l_pd_amt4 NUMBER;
1240 l_pd_amt5 NUMBER;
1241 l_pd_amt6 NUMBER;
1242 l_pd_amt7 NUMBER;
1243 l_pd_amt8 NUMBER;
1244 l_pd_amt9 NUMBER;
1245 l_pd_amt10 NUMBER;
1246 l_pd_amt11 NUMBER;
1247 l_pd_amt12 NUMBER;
1248 l_pd_amt13 NUMBER;
1249 l_pd_amt14 NUMBER;
1250 l_pd_amt15 NUMBER;
1251 l_pd_amt16 NUMBER;
1252 l_pd_amt17 NUMBER;
1253 l_pd_amt18 NUMBER;
1254 l_pd_amt19 NUMBER;
1255 l_pd_amt20 NUMBER;
1256 l_pd_amt21 NUMBER;
1257 l_pd_amt22 NUMBER;
1258 l_pd_amt23 NUMBER;
1259 l_pd_amt24 NUMBER;
1260 l_pd_amt25 NUMBER;
1261 l_pd_amt26 NUMBER;
1262 l_pd_amt27 NUMBER;
1263 l_pd_amt28 NUMBER;
1264 l_pd_amt29 NUMBER;
1265 l_pd_amt30 NUMBER;
1266 l_pd_amt31 NUMBER;
1267 l_pd_amt32 NUMBER;
1268 l_pd_amt33 NUMBER;
1269 l_pd_amt34 NUMBER;
1270 l_pd_amt35 NUMBER;
1271 l_pd_amt36 NUMBER;
1272 l_pd_amt37 NUMBER;
1273 l_pd_amt38 NUMBER;
1274 l_pd_amt39 NUMBER;
1275 l_pd_amt40 NUMBER;
1276 l_pd_amt41 NUMBER;
1277 l_pd_amt42 NUMBER;
1278 l_pd_amt43 NUMBER;
1279 l_pd_amt44 NUMBER;
1280 l_pd_amt45 NUMBER;
1281 l_pd_amt46 NUMBER;
1282 l_pd_amt47 NUMBER;
1283 l_pd_amt48 NUMBER;
1284 l_pd_amt49 NUMBER;
1285 l_pd_amt50 NUMBER;
1286 l_pd_amt51 NUMBER;
1287 l_pd_amt52 NUMBER;
1288
1289 --Added By Vijay Gautam
1290 l_parent_assignment_id NUMBER; --to hold the value from denorm table
1291 l_parent_assign_id NUMBER; -- to hold the value from PL/SQL (fin_plan_lines_tmp) table
1292 l_count_for_pop_call NUMBER;
1293 l_parent_assign_id_local NUMBER;
1294 l_quantity_filter_flag VARCHAR2(1); -- to filter and not insert/update anything for quantity and
1295 -- currency type project or proj_functional
1296 l_min_pa_fp_ln_tmp_st_dt DATE;
1297 l_max_pa_fp_ln_tmp_st_dt DATE;
1298
1299 l_project_currency_code VARCHAR2(30);
1300 l_projfunc_currency_code VARCHAR2(30);
1301 --
1302
1303 l_cnt NUMBER(5);
1304 l_total_pds NUMBER(5);
1305
1306 l_prev_raw_cost NUMBER;
1307 l_prev_burd_cost NUMBER;
1308 l_prev_revenue NUMBER;
1309 l_prev_quantity NUMBER;
1310 l_prev_borr_revenue NUMBER;
1311 l_prev_cc_rev_in NUMBER;
1312 l_prev_cc_rev_out NUMBER;
1313 l_prev_rev_adj NUMBER;
1314 l_prev_lent_res_cost NUMBER;
1315 l_prev_cc_cost_in NUMBER;
1316 l_prev_cc_cost_out NUMBER;
1317 l_prev_cost_adj NUMBER;
1318 l_prev_unasg_time_cost NUMBER;
1319 l_prev_util_per NUMBER;
1320 l_prev_util_adj NUMBER;
1321 l_prev_util_hrs NUMBER;
1322 l_prev_capacity NUMBER;
1323 l_prev_head_count NUMBER;
1324 l_prev_head_count_adj NUMBER;
1325 l_prev_margin NUMBER;
1326 l_prev_margin_perc NUMBER;
1327 l_prev_txn_raw_cost NUMBER;
1328 l_prev_txn_burd_cost NUMBER;
1329 l_prev_txn_revenue NUMBER;
1330 l_prev_proj_raw_cost NUMBER;
1331 l_prev_proj_burd_cost NUMBER;
1332 l_prev_proj_revenue NUMBER;
1333
1334 l_next_raw_cost NUMBER;
1335 l_next_burd_cost NUMBER;
1336 l_next_revenue NUMBER;
1337 l_next_quantity NUMBER;
1338 l_next_borr_revenue NUMBER;
1339 l_next_cc_rev_in NUMBER;
1340 l_next_cc_rev_out NUMBER;
1341 l_next_rev_adj NUMBER;
1342 l_next_lent_res_cost NUMBER;
1343 l_next_cc_cost_in NUMBER;
1344 l_next_cc_cost_out NUMBER;
1345 l_next_cost_adj NUMBER;
1346 l_next_unasg_time_cost NUMBER;
1347 l_next_util_per NUMBER;
1348 l_next_util_adj NUMBER;
1349 l_next_util_hrs NUMBER;
1350 l_next_capacity NUMBER;
1351 l_next_head_count NUMBER;
1352 l_next_head_count_adj NUMBER;
1353 l_next_margin NUMBER;
1354 l_next_margin_perc NUMBER;
1355 l_next_txn_raw_cost NUMBER;
1356 l_next_txn_burd_cost NUMBER;
1357 l_next_txn_revenue NUMBER;
1358 l_next_proj_raw_cost NUMBER;
1359 l_next_proj_burd_cost NUMBER;
1360 l_next_proj_revenue NUMBER;
1361
1362 l_valid_amount_flag varchar2(1);
1363 l_start_date DATE;
1364 l_fcst_amt NUMBER;
1365 l_old_fcst_amt NUMBER;
1366
1367 --Added By Vijay Gautam
1368 l_period_set_name VARCHAR2(30);
1369 l_period_type VARCHAR2(30);
1370 l_plan_period_type VARCHAR2(30);
1371 l_project_id NUMBER;
1372 l_period_profile_id NUMBER;
1373 l_budget_version_id NUMBER;
1374
1375 --
1376
1377
1378 CURSOR Main_Cur IS
1379 SELECT DISTINCT Resource_Assignment_Id,
1380 Object_Id,
1381 Object_Type_Code,
1382 Currency_Type,
1383 Currency_Code,
1384 Source_Txn_Currency_Code
1385 FROM
1386 Pa_Fin_Plan_Lines_Tmp;
1387
1388
1392 c_currency_type VARCHAR2,
1389 CURSOR Bl_Cur(c_resource_assignment_id NUMBER,
1390 c_object_id NUMBER,
1391 c_object_type_code VARCHAR2,
1393 c_currency_code VARCHAR2,
1394 c_source_txn_currency_code VARCHAR2,
1395 c_start_date DATE,
1396 c_end_date DATE) IS
1397 SELECT Period_Name,
1398 Start_Date,
1399 Quantity,
1400 Raw_Cost,
1401 Burdened_Cost,
1402 Revenue,
1403 Old_Quantity,
1404 Old_Raw_Cost,
1405 Old_Burdened_Cost,
1406 Old_Revenue,
1407 Borrowed_Revenue,
1408 Tp_Revenue_In,
1409 Tp_Revenue_Out,
1410 Revenue_Adj,
1411 Lent_Resource_Cost,
1412 Tp_Cost_In,
1413 Tp_Cost_Out,
1414 Cost_Adj,
1415 Unassigned_Time_Cost,
1416 Utilization_Percent,
1417 Utilization_Adj,
1418 Utilization_Hours,
1419 Capacity,
1420 Head_Count,
1421 Head_Count_Adj,
1422 Margin,
1423 Margin_Percentage,
1424 Bucketing_Period_Code, -- added this column in the cursor
1425 Parent_Assignment_Id, -- added this column in the cursor
1426 NVL(Delete_Flag,'N') -- added this column in the cursor
1427 FROM Pa_Fin_Plan_Lines_Tmp
1428 WHERE Resource_Assignment_Id = c_resource_assignment_id AND
1429 Object_Id = c_object_id AND
1430 Object_Type_Code = c_object_type_code AND
1431 Currency_Type = c_currency_type AND
1432 Currency_Code = c_currency_code AND
1433 Source_Txn_Currency_Code = c_source_txn_currency_code AND
1434 Start_Date BETWEEN c_start_date AND
1435 c_end_date AND
1436 (Bucketing_Period_Code IS NULL OR
1437 (p_calling_module = 'FINANCIAL_PLANNING' AND
1438 Bucketing_Period_Code IN ('PE','SE'))); -- Bug 2789114
1439
1440
1441 /* bug 2772683 bucketing period code NULL check added. */
1442
1443 l_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1444 l_burd_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1445 l_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
1446 l_qty_tab PA_PLSQL_DATATYPES.NumTabTyp;
1447 l_old_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1448 l_old_burd_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1449 l_old_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
1450 l_old_qty_tab PA_PLSQL_DATATYPES.NumTabTyp;
1451
1452 l_borr_rev_tab PA_PLSQL_DATATYPES.NumTabTyp;
1453 l_cc_rev_in_tab PA_PLSQL_DATATYPES.NumTabTyp;
1454 l_cc_rev_out_tab PA_PLSQL_DATATYPES.NumTabTyp;
1455 l_rev_adj_tab PA_PLSQL_DATATYPES.NumTabTyp;
1456 l_lent_res_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1457 l_cc_cost_in_tab PA_PLSQL_DATATYPES.NumTabTyp;
1458 l_cc_cost_out_tab PA_PLSQL_DATATYPES.NumTabTyp;
1459 l_cost_adj_tab PA_PLSQL_DATATYPES.NumTabTyp;
1460 l_unasg_time_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1461 l_util_per_tab PA_PLSQL_DATATYPES.NumTabTyp;
1462 l_util_adj_tab PA_PLSQL_DATATYPES.NumTabTyp;
1463 l_util_hrs_tab PA_PLSQL_DATATYPES.NumTabTyp;
1464 l_capacity_tab PA_PLSQL_DATATYPES.NumTabTyp;
1465 l_head_count_tab PA_PLSQL_DATATYPES.NumTabTyp;
1466 l_head_count_adj_tab PA_PLSQL_DATATYPES.NumTabTyp;
1467 l_margin_tab PA_PLSQL_DATATYPES.NumTabTyp;
1468 l_margin_perc_tab PA_PLSQL_DATATYPES.NumTabTyp;
1469
1470 l_prev_amt_tab PA_PLSQL_DATATYPES.NumTabTyp;
1471 l_next_amt_tab PA_PLSQL_DATATYPES.NumTabTyp;
1472 l_prior_amt_tab PA_PLSQL_DATATYPES.NumTabTyp;
1473 l_period_name_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1474 l_fcst_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
1475 l_fcst_old_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
1476 l_start_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1477
1478 l_temp NUMBER(5);
1479 l_matrix_counter NUMBER ;
1480 l_number_of_periods NUMBER;
1481 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
1482 l_created_by NUMBER := FND_GLOBAL.USER_ID;
1483 l_creation_date DATE := SYSDATE;
1484 l_last_update_date DATE := l_creation_date;
1485 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
1486 l_program_application_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
1487 l_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
1488 l_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
1489
1490 --Added By Vijay Gautam
1491 l_bucketing_period_code_tab PA_PLSQL_DATATYPES.Char30TabTyp; --added this table
1492 l_parent_assign_id_tab PA_PLSQL_DATATYPES.NumTabTyp; --added this Table
1493 l_delete_flag_tab PA_PLSQL_DATATYPES.Char30TabTyp; --added this Table
1494 --
1495
1496 BEGIN
1497 PA_DEBUG.Set_Curr_Function( p_function => 'Maintain_Plan_Matrix',
1498 p_debug_mode => p_debug_mode );
1499 l_matrix_counter := 1;
1500 /* the following logic can be easily coded by using Dynamic SQL. But for checking 52 columns
1501 , it will be 52 DB hits. And also if this process is called multiple times from
1502 conc mgr process for a range of projects, there will be more DB hits.
1506 IF P_PA_DEBUG_MODE = 'Y' THEN
1503 So the logic is coded as a single select and using multiple IFs - SManivannan */
1504 x_return_status := FND_API.G_RET_STS_SUCCESS;
1505
1507 PA_DEBUG.g_err_stage := 'Entering Main Plan Matrix and selecting prj profile';
1508 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1509 END IF;
1510 --DBMS_OUTPUT.PUT_LINE('11');
1511 BEGIN
1512 SELECT
1513 number_of_periods,
1514 period_name1,
1515 period_name2,
1516 period_name3,
1517 period_name4,
1518 period_name5,
1519 period_name6,
1520 period_name7,
1521 period_name8,
1522 period_name9,
1523 period_name10,
1524 period_name11,
1525 period_name12,
1526 period_name13,
1527 period_name14,
1528 period_name15,
1529 period_name16,
1530 period_name17,
1531 period_name18,
1532 period_name19,
1533 period_name20,
1534 period_name21,
1535 period_name22,
1536 period_name23,
1537 period_name24,
1538 period_name25,
1539 period_name26,
1540 period_name27,
1541 period_name28,
1542 period_name29,
1543 period_name30,
1544 period_name31,
1545 period_name32,
1546 period_name33,
1547 period_name34,
1548 period_name35,
1549 period_name36,
1550 period_name37,
1551 period_name38,
1552 period_name39,
1553 period_name40,
1554 period_name41,
1555 period_name42,
1556 period_name43,
1557 period_name44,
1558 period_name45,
1559 period_name46,
1560 period_name47,
1561 period_name48,
1562 period_name49,
1563 period_name50,
1564 period_name51,
1565 period_name52,
1566 period1_start_date,
1567 period2_start_date,
1568 period3_start_date,
1569 period4_start_date,
1570 period5_start_date,
1571 period6_start_date,
1572 period7_start_date,
1573 period8_start_date,
1574 period9_start_date,
1575 period10_start_date,
1576 period11_start_date,
1577 period12_start_date,
1578 period13_start_date,
1579 period14_start_date,
1580 period15_start_date,
1581 period16_start_date,
1582 period17_start_date,
1583 period18_start_date,
1584 period19_start_date,
1585 period20_start_date,
1586 period21_start_date,
1587 period22_start_date,
1588 period23_start_date,
1589 period24_start_date,
1590 period25_start_date,
1591 period26_start_date,
1592 period27_start_date,
1593 period28_start_date,
1594 period29_start_date,
1595 period30_start_date,
1596 period31_start_date,
1597 period32_start_date,
1598 period33_start_date,
1599 period34_start_date,
1600 period35_start_date,
1601 period36_start_date,
1602 period37_start_date,
1603 period38_start_date,
1604 period39_start_date,
1605 period40_start_date,
1606 period41_start_date,
1607 period42_start_date,
1608 period43_start_date,
1609 period44_start_date,
1610 period45_start_date,
1611 period46_start_date,
1612 period47_start_date,
1613 period48_start_date,
1614 period49_start_date,
1615 period50_start_date,
1616 period51_start_date,
1617 period52_start_date INTO
1618 l_number_of_periods,
1619 l_period_name1,
1620 l_period_name2,
1621 l_period_name3,
1622 l_period_name4,
1623 l_period_name5,
1624 l_period_name6,
1625 l_period_name7,
1626 l_period_name8,
1627 l_period_name9,
1628 l_period_name10,
1629 l_period_name11,
1630 l_period_name12,
1631 l_period_name13,
1632 l_period_name14,
1633 l_period_name15,
1634 l_period_name16,
1635 l_period_name17,
1636 l_period_name18,
1637 l_period_name19,
1638 l_period_name20,
1639 l_period_name21,
1640 l_period_name22,
1641 l_period_name23,
1642 l_period_name24,
1643 l_period_name25,
1644 l_period_name26,
1645 l_period_name27,
1646 l_period_name28,
1647 l_period_name29,
1648 l_period_name30,
1649 l_period_name31,
1650 l_period_name32,
1651 l_period_name33,
1652 l_period_name34,
1653 l_period_name35,
1654 l_period_name36,
1658 l_period_name40,
1655 l_period_name37,
1656 l_period_name38,
1657 l_period_name39,
1659 l_period_name41,
1660 l_period_name42,
1661 l_period_name43,
1662 l_period_name44,
1663 l_period_name45,
1664 l_period_name46,
1665 l_period_name47,
1666 l_period_name48,
1667 l_period_name49,
1668 l_period_name50,
1669 l_period_name51,
1670 l_period_name52,
1671 l_period1_start_date,
1672 l_period2_start_date,
1673 l_period3_start_date,
1674 l_period4_start_date,
1675 l_period5_start_date,
1676 l_period6_start_date,
1677 l_period7_start_date,
1678 l_period8_start_date,
1679 l_period9_start_date,
1680 l_period10_start_date,
1681 l_period11_start_date,
1682 l_period12_start_date,
1683 l_period13_start_date,
1684 l_period14_start_date,
1685 l_period15_start_date,
1686 l_period16_start_date,
1687 l_period17_start_date,
1688 l_period18_start_date,
1689 l_period19_start_date,
1690 l_period20_start_date,
1691 l_period21_start_date,
1692 l_period22_start_date,
1693 l_period23_start_date,
1694 l_period24_start_date,
1695 l_period25_start_date,
1696 l_period26_start_date,
1697 l_period27_start_date,
1698 l_period28_start_date,
1699 l_period29_start_date,
1700 l_period30_start_date,
1701 l_period31_start_date,
1702 l_period32_start_date,
1703 l_period33_start_date,
1704 l_period34_start_date,
1705 l_period35_start_date,
1706 l_period36_start_date,
1707 l_period37_start_date,
1708 l_period38_start_date,
1709 l_period39_start_date,
1710 l_period40_start_date,
1711 l_period41_start_date,
1712 l_period42_start_date,
1713 l_period43_start_date,
1714 l_period44_start_date,
1715 l_period45_start_date,
1716 l_period46_start_date,
1717 l_period47_start_date,
1718 l_period48_start_date,
1719 l_period49_start_date,
1720 l_period50_start_date,
1721 l_period51_start_date,
1722 l_period52_start_date
1723 from
1724 pa_proj_period_profiles where
1725 period_profile_id = p_period_profile_id;
1726 EXCEPTION
1727 WHEN NO_DATA_FOUND THEN
1728 IF P_PA_DEBUG_MODE = 'Y' THEN
1729 PA_DEBUG.g_err_stage := 'Prj profile not found returning';
1730 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1731 END IF;
1732 x_return_status := FND_API.G_RET_STS_ERROR;
1733 IF p_add_msg_in_stack = 'Y' THEN
1734 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1735 p_msg_name => 'PA_FP_INVALID_PRJ_PROFILE');
1736 ELSE
1737 x_msg_data := 'PA_FP_INVALID_PRJ_PROFILE';
1738 END IF;
1739 PA_DEBUG.Reset_Curr_Function;
1740 END;
1741
1742 IF P_PA_DEBUG_MODE = 'Y' THEN
1743 PA_DEBUG.g_err_stage := 'After selecting prj profile';
1744 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1745 END IF;
1746
1747
1748 l_start_period_name := l_period_name1;
1749 l_st_dt_4_st_pd := l_period1_start_date;
1750 l_total_pds := 1;
1751
1752 -- bug 2858293, if the duration of period profile can beonly one period
1753 -- then end period is same as period1
1754
1755 l_end_period_name := l_period_name1;
1756 l_st_dt_4_end_pd := l_period1_start_date;
1757
1758 l_pd_name_map_tab.delete;
1759 l_st_date_map_tab.delete;
1760
1761 l_pd_name_map_tab(1) := l_period_name1;
1762 l_st_date_map_tab(1) := l_period1_start_date;
1763 l_cnt := 1;
1764 /* incremented inside IFs for not null values */
1765
1766
1767 IF l_period2_start_date IS NOT NULL THEN
1768 l_cnt := l_cnt + 1;
1769 l_end_period_name := l_period_name2;
1770 l_st_dt_4_end_pd := l_period2_start_date;
1771 l_total_pds := 2;
1772 l_pd_name_map_tab(l_cnt) := l_period_name2;
1773 l_st_date_map_tab(l_cnt) := l_period2_start_date;
1774 END IF;
1775 IF l_period3_start_date IS NOT NULL THEN
1776 l_cnt := l_cnt + 1;
1777 l_end_period_name := l_period_name3;
1778 l_st_dt_4_end_pd := l_period3_start_date;
1779 l_total_pds := 3;
1780 l_pd_name_map_tab(l_cnt) := l_period_name3;
1781 l_st_date_map_tab(l_cnt) := l_period3_start_date;
1782 END IF;
1783 IF l_period4_start_date IS NOT NULL THEN
1784 l_cnt := l_cnt + 1;
1785 l_end_period_name := l_period_name4;
1786 l_st_dt_4_end_pd := l_period4_start_date;
1787 l_total_pds := 4;
1788 l_pd_name_map_tab(l_cnt) := l_period_name4;
1789 l_st_date_map_tab(l_cnt) := l_period4_start_date;
1790 END IF;
1791 IF l_period5_start_date IS NOT NULL THEN
1792 l_cnt := l_cnt + 1;
1793 l_end_period_name := l_period_name5;
1797 l_st_date_map_tab(l_cnt) := l_period5_start_date;
1794 l_st_dt_4_end_pd := l_period5_start_date;
1795 l_total_pds := 5;
1796 l_pd_name_map_tab(l_cnt) := l_period_name5;
1798 END IF;
1799 IF l_period6_start_date IS NOT NULL THEN
1800 l_cnt := l_cnt + 1;
1801 l_end_period_name := l_period_name6;
1802 l_st_dt_4_end_pd := l_period6_start_date;
1803 l_total_pds := 6;
1804 l_pd_name_map_tab(l_cnt) := l_period_name6;
1805 l_st_date_map_tab(l_cnt) := l_period6_start_date;
1806 END IF;
1807 IF l_period7_start_date IS NOT NULL THEN
1808 l_cnt := l_cnt + 1;
1809 l_end_period_name := l_period_name7;
1810 l_st_dt_4_end_pd := l_period7_start_date;
1811 l_total_pds := 7;
1812 l_pd_name_map_tab(l_cnt) := l_period_name7;
1813 l_st_date_map_tab(l_cnt) := l_period7_start_date;
1814 END IF;
1815 IF l_period8_start_date IS NOT NULL THEN
1816 l_cnt := l_cnt + 1;
1817 l_end_period_name := l_period_name8;
1818 l_st_dt_4_end_pd := l_period8_start_date;
1819 l_total_pds := 8;
1820 l_pd_name_map_tab(l_cnt) := l_period_name8;
1821 l_st_date_map_tab(l_cnt) := l_period8_start_date;
1822 END IF;
1823 IF l_period9_start_date IS NOT NULL THEN
1824 l_cnt := l_cnt + 1;
1825 l_end_period_name := l_period_name9;
1826 l_st_dt_4_end_pd := l_period9_start_date;
1827 l_total_pds := 9;
1828 l_pd_name_map_tab(l_cnt) := l_period_name9;
1829 l_st_date_map_tab(l_cnt) := l_period9_start_date;
1830 END IF;
1831 IF l_period10_start_date IS NOT NULL THEN
1832 l_cnt := l_cnt + 1;
1833 l_end_period_name := l_period_name10;
1834 l_st_dt_4_end_pd := l_period10_start_date;
1835 l_total_pds := 10;
1836 l_pd_name_map_tab(l_cnt) := l_period_name10;
1837 l_st_date_map_tab(l_cnt) := l_period10_start_date;
1838 END IF;
1839 IF l_period11_start_date IS NOT NULL THEN
1840 l_cnt := l_cnt + 1;
1841 l_end_period_name := l_period_name11;
1842 l_st_dt_4_end_pd := l_period11_start_date;
1843 l_total_pds := 11;
1844 l_pd_name_map_tab(l_cnt) := l_period_name11;
1845 l_st_date_map_tab(l_cnt) := l_period11_start_date;
1846 END IF;
1847 IF l_period12_start_date IS NOT NULL THEN
1848 l_cnt := l_cnt + 1;
1849 l_end_period_name := l_period_name12;
1850 l_st_dt_4_end_pd := l_period12_start_date;
1851 l_total_pds := 12;
1852 l_pd_name_map_tab(l_cnt) := l_period_name12;
1853 l_st_date_map_tab(l_cnt) := l_period12_start_date;
1854 END IF;
1855 IF l_period13_start_date IS NOT NULL THEN
1856 l_cnt := l_cnt + 1;
1857 l_end_period_name := l_period_name13;
1858 l_st_dt_4_end_pd := l_period13_start_date;
1859 l_total_pds := 13;
1860 l_pd_name_map_tab(l_cnt) := l_period_name13;
1861 l_st_date_map_tab(l_cnt) := l_period13_start_date;
1862 END IF;
1863 IF l_period14_start_date IS NOT NULL THEN
1864 l_cnt := l_cnt + 1;
1865 l_end_period_name := l_period_name14;
1866 l_st_dt_4_end_pd := l_period14_start_date;
1867 l_total_pds := 14;
1868 l_pd_name_map_tab(l_cnt) := l_period_name14;
1869 l_st_date_map_tab(l_cnt) := l_period14_start_date;
1870 END IF;
1871 IF l_period15_start_date IS NOT NULL THEN
1872 l_cnt := l_cnt + 1;
1873 l_end_period_name := l_period_name15;
1874 l_st_dt_4_end_pd := l_period15_start_date;
1875 l_total_pds := 15;
1876 l_pd_name_map_tab(l_cnt) := l_period_name15;
1877 l_st_date_map_tab(l_cnt) := l_period15_start_date;
1878 END IF;
1879 IF l_period16_start_date IS NOT NULL THEN
1880 l_cnt := l_cnt + 1;
1881 l_end_period_name := l_period_name16;
1882 l_st_dt_4_end_pd := l_period16_start_date;
1883 l_total_pds := 16;
1884 l_pd_name_map_tab(l_cnt) := l_period_name16;
1885 l_st_date_map_tab(l_cnt) := l_period16_start_date;
1886 END IF;
1887 IF l_period17_start_date IS NOT NULL THEN
1888 l_cnt := l_cnt + 1;
1889 l_end_period_name := l_period_name17;
1890 l_st_dt_4_end_pd := l_period17_start_date;
1891 l_total_pds := 17;
1892 l_pd_name_map_tab(l_cnt) := l_period_name17;
1893 l_st_date_map_tab(l_cnt) := l_period17_start_date;
1894 END IF;
1895 IF l_period18_start_date IS NOT NULL THEN
1896 l_cnt := l_cnt + 1;
1897 l_end_period_name := l_period_name18;
1898 l_st_dt_4_end_pd := l_period18_start_date;
1899 l_total_pds := 18;
1900 l_pd_name_map_tab(l_cnt) := l_period_name18;
1901 l_st_date_map_tab(l_cnt) := l_period18_start_date;
1902 END IF;
1903 IF l_period19_start_date IS NOT NULL THEN
1904 l_cnt := l_cnt + 1;
1905 l_end_period_name := l_period_name19;
1906 l_st_dt_4_end_pd := l_period19_start_date;
1907 l_total_pds := 19;
1908 l_pd_name_map_tab(l_cnt) := l_period_name19;
1909 l_st_date_map_tab(l_cnt) := l_period19_start_date;
1910 END IF;
1911 IF l_period20_start_date IS NOT NULL THEN
1912 l_cnt := l_cnt + 1;
1913 l_end_period_name := l_period_name20;
1914 l_st_dt_4_end_pd := l_period20_start_date;
1915 l_total_pds := 20;
1916 l_pd_name_map_tab(l_cnt) := l_period_name20;
1917 l_st_date_map_tab(l_cnt) := l_period20_start_date;
1918 END IF;
1919 IF l_period21_start_date IS NOT NULL THEN
1920 l_cnt := l_cnt + 1;
1921 l_end_period_name := l_period_name21;
1922 l_st_dt_4_end_pd := l_period21_start_date;
1923 l_total_pds := 21;
1927 IF l_period22_start_date IS NOT NULL THEN
1924 l_pd_name_map_tab(l_cnt) := l_period_name21;
1925 l_st_date_map_tab(l_cnt) := l_period21_start_date;
1926 END IF;
1928 l_cnt := l_cnt + 1;
1929 l_end_period_name := l_period_name22;
1930 l_st_dt_4_end_pd := l_period22_start_date;
1931 l_total_pds := 22;
1932 l_pd_name_map_tab(l_cnt) := l_period_name22;
1933 l_st_date_map_tab(l_cnt) := l_period22_start_date;
1934 END IF;
1935 IF l_period23_start_date IS NOT NULL THEN
1936 l_cnt := l_cnt + 1;
1937 l_end_period_name := l_period_name23;
1938 l_st_dt_4_end_pd := l_period23_start_date;
1939 l_total_pds := 23;
1940 l_pd_name_map_tab(l_cnt) := l_period_name23;
1941 l_st_date_map_tab(l_cnt) := l_period23_start_date;
1942 END IF;
1943 IF l_period24_start_date IS NOT NULL THEN
1944 l_cnt := l_cnt + 1;
1945 l_end_period_name := l_period_name24;
1946 l_st_dt_4_end_pd := l_period24_start_date;
1947 l_total_pds := 24;
1948 l_pd_name_map_tab(l_cnt) := l_period_name24;
1949 l_st_date_map_tab(l_cnt) := l_period24_start_date;
1950 END IF;
1951 IF l_period25_start_date IS NOT NULL THEN
1952 l_cnt := l_cnt + 1;
1953 l_end_period_name := l_period_name25;
1954 l_st_dt_4_end_pd := l_period25_start_date;
1955 l_total_pds := 25;
1956 l_pd_name_map_tab(l_cnt) := l_period_name25;
1957 l_st_date_map_tab(l_cnt) := l_period25_start_date;
1958 END IF;
1959 IF l_period26_start_date IS NOT NULL THEN
1960 l_cnt := l_cnt + 1;
1961 l_end_period_name := l_period_name26;
1962 l_st_dt_4_end_pd := l_period26_start_date;
1963 l_total_pds := 26;
1964 l_pd_name_map_tab(l_cnt) := l_period_name26;
1965 l_st_date_map_tab(l_cnt) := l_period26_start_date;
1966 END IF;
1967 IF l_period27_start_date IS NOT NULL THEN
1968 l_cnt := l_cnt + 1;
1969 l_end_period_name := l_period_name27;
1970 l_st_dt_4_end_pd := l_period27_start_date;
1971 l_total_pds := 27;
1972 l_pd_name_map_tab(l_cnt) := l_period_name27;
1973 l_st_date_map_tab(l_cnt) := l_period27_start_date;
1974 END IF;
1975 IF l_period28_start_date IS NOT NULL THEN
1976 l_cnt := l_cnt + 1;
1977 l_end_period_name := l_period_name28;
1978 l_st_dt_4_end_pd := l_period28_start_date;
1979 l_total_pds := 28;
1980 l_pd_name_map_tab(l_cnt) := l_period_name28;
1981 l_st_date_map_tab(l_cnt) := l_period28_start_date;
1982 END IF;
1983 IF l_period29_start_date IS NOT NULL THEN
1984 l_cnt := l_cnt + 1;
1985 l_end_period_name := l_period_name29;
1986 l_st_dt_4_end_pd := l_period29_start_date;
1987 l_total_pds := 29;
1988 l_pd_name_map_tab(l_cnt) := l_period_name29;
1989 l_st_date_map_tab(l_cnt) := l_period29_start_date;
1990 END IF;
1991 IF l_period30_start_date IS NOT NULL THEN
1992 l_cnt := l_cnt + 1;
1993 l_end_period_name := l_period_name30;
1994 l_st_dt_4_end_pd := l_period30_start_date;
1995 l_total_pds := 30;
1996 l_pd_name_map_tab(l_cnt) := l_period_name30;
1997 l_st_date_map_tab(l_cnt) := l_period30_start_date;
1998 END IF;
1999 IF l_period31_start_date IS NOT NULL THEN
2000 l_cnt := l_cnt + 1;
2001 l_end_period_name := l_period_name31;
2002 l_st_dt_4_end_pd := l_period31_start_date;
2003 l_total_pds := 31;
2004 l_pd_name_map_tab(l_cnt) := l_period_name31;
2005 l_st_date_map_tab(l_cnt) := l_period31_start_date;
2006 END IF;
2007 IF l_period32_start_date IS NOT NULL THEN
2008 l_cnt := l_cnt + 1;
2009 l_end_period_name := l_period_name32;
2010 l_st_dt_4_end_pd := l_period32_start_date;
2011 l_total_pds := 32;
2012 l_pd_name_map_tab(l_cnt) := l_period_name32;
2013 l_st_date_map_tab(l_cnt) := l_period32_start_date;
2014 END IF;
2015 IF l_period33_start_date IS NOT NULL THEN
2016 l_cnt := l_cnt + 1;
2017 l_end_period_name := l_period_name33;
2018 l_st_dt_4_end_pd := l_period33_start_date;
2019 l_total_pds := 33;
2020 l_pd_name_map_tab(l_cnt) := l_period_name33;
2021 l_st_date_map_tab(l_cnt) := l_period33_start_date;
2022 END IF;
2023 IF l_period34_start_date IS NOT NULL THEN
2024 l_cnt := l_cnt + 1;
2025 l_end_period_name := l_period_name34;
2026 l_st_dt_4_end_pd := l_period34_start_date;
2027 l_total_pds := 34;
2028 l_pd_name_map_tab(l_cnt) := l_period_name34;
2029 l_st_date_map_tab(l_cnt) := l_period34_start_date;
2030 END IF;
2031 IF l_period35_start_date IS NOT NULL THEN
2032 l_cnt := l_cnt + 1;
2033 l_end_period_name := l_period_name35;
2034 l_st_dt_4_end_pd := l_period35_start_date;
2035 l_total_pds := 35;
2036 l_pd_name_map_tab(l_cnt) := l_period_name35;
2037 l_st_date_map_tab(l_cnt) := l_period35_start_date;
2038 END IF;
2039 IF l_period36_start_date IS NOT NULL THEN
2040 l_cnt := l_cnt + 1;
2041 l_end_period_name := l_period_name36;
2042 l_st_dt_4_end_pd := l_period36_start_date;
2043 l_total_pds := 36;
2044 l_pd_name_map_tab(l_cnt) := l_period_name36;
2045 l_st_date_map_tab(l_cnt) := l_period36_start_date;
2046 END IF;
2047 IF l_period37_start_date IS NOT NULL THEN
2048 l_cnt := l_cnt + 1;
2049 l_end_period_name := l_period_name37;
2053 l_st_date_map_tab(l_cnt) := l_period37_start_date;
2050 l_st_dt_4_end_pd := l_period37_start_date;
2051 l_total_pds := 37;
2052 l_pd_name_map_tab(l_cnt) := l_period_name37;
2054 END IF;
2055 IF l_period38_start_date IS NOT NULL THEN
2056 l_cnt := l_cnt + 1;
2057 l_end_period_name := l_period_name38;
2058 l_st_dt_4_end_pd := l_period38_start_date;
2059 l_total_pds := 38;
2060 l_pd_name_map_tab(l_cnt) := l_period_name38;
2061 l_st_date_map_tab(l_cnt) := l_period38_start_date;
2062 END IF;
2063 IF l_period39_start_date IS NOT NULL THEN
2064 l_cnt := l_cnt + 1;
2065 l_end_period_name := l_period_name39;
2066 l_st_dt_4_end_pd := l_period39_start_date;
2067 l_total_pds := 39;
2068 l_pd_name_map_tab(l_cnt) := l_period_name39;
2069 l_st_date_map_tab(l_cnt) := l_period39_start_date;
2070 END IF;
2071 IF l_period40_start_date IS NOT NULL THEN
2072 l_cnt := l_cnt + 1;
2073 l_end_period_name := l_period_name40;
2074 l_st_dt_4_end_pd := l_period40_start_date;
2075 l_total_pds := 40;
2076 l_pd_name_map_tab(l_cnt) := l_period_name40;
2077 l_st_date_map_tab(l_cnt) := l_period40_start_date;
2078 END IF;
2079 IF l_period41_start_date IS NOT NULL THEN
2080 l_cnt := l_cnt + 1;
2081 l_end_period_name := l_period_name41;
2082 l_st_dt_4_end_pd := l_period41_start_date;
2083 l_total_pds := 41;
2084 l_pd_name_map_tab(l_cnt) := l_period_name41;
2085 l_st_date_map_tab(l_cnt) := l_period41_start_date;
2086 END IF;
2087 IF l_period42_start_date IS NOT NULL THEN
2088 l_cnt := l_cnt + 1;
2089 l_end_period_name := l_period_name42;
2090 l_st_dt_4_end_pd := l_period42_start_date;
2091 l_total_pds := 42;
2092 l_pd_name_map_tab(l_cnt) := l_period_name42;
2093 l_st_date_map_tab(l_cnt) := l_period42_start_date;
2094 END IF;
2095 IF l_period43_start_date IS NOT NULL THEN
2096 l_cnt := l_cnt + 1;
2097 l_end_period_name := l_period_name43;
2098 l_st_dt_4_end_pd := l_period43_start_date;
2099 l_total_pds := 43;
2100 l_pd_name_map_tab(l_cnt) := l_period_name43;
2101 l_st_date_map_tab(l_cnt) := l_period43_start_date;
2102 END IF;
2103 IF l_period44_start_date IS NOT NULL THEN
2104 l_cnt := l_cnt + 1;
2105 l_end_period_name := l_period_name44;
2106 l_st_dt_4_end_pd := l_period44_start_date;
2107 l_total_pds := 44;
2108 l_pd_name_map_tab(l_cnt) := l_period_name44;
2109 l_st_date_map_tab(l_cnt) := l_period44_start_date;
2110 END IF;
2111 IF l_period45_start_date IS NOT NULL THEN
2112 l_cnt := l_cnt + 1;
2113 l_end_period_name := l_period_name45;
2114 l_st_dt_4_end_pd := l_period45_start_date;
2115 l_total_pds := 45;
2116 l_pd_name_map_tab(l_cnt) := l_period_name45;
2117 l_st_date_map_tab(l_cnt) := l_period45_start_date;
2118 END IF;
2119 IF l_period46_start_date IS NOT NULL THEN
2120 l_cnt := l_cnt + 1;
2121 l_end_period_name := l_period_name46;
2122 l_st_dt_4_end_pd := l_period46_start_date;
2123 l_total_pds := 46;
2124 l_pd_name_map_tab(l_cnt) := l_period_name46;
2125 l_st_date_map_tab(l_cnt) := l_period46_start_date;
2126 END IF;
2127 IF l_period47_start_date IS NOT NULL THEN
2128 l_cnt := l_cnt + 1;
2129 l_end_period_name := l_period_name47;
2130 l_st_dt_4_end_pd := l_period47_start_date;
2131 l_total_pds := 47;
2132 l_pd_name_map_tab(l_cnt) := l_period_name47;
2133 l_st_date_map_tab(l_cnt) := l_period47_start_date;
2134 END IF;
2135 IF l_period48_start_date IS NOT NULL THEN
2136 l_cnt := l_cnt + 1;
2137 l_end_period_name := l_period_name48;
2138 l_st_dt_4_end_pd := l_period48_start_date;
2139 l_total_pds := 48;
2140 l_pd_name_map_tab(l_cnt) := l_period_name48;
2141 l_st_date_map_tab(l_cnt) := l_period48_start_date;
2142 END IF;
2143 IF l_period49_start_date IS NOT NULL THEN
2144 l_cnt := l_cnt + 1;
2145 l_end_period_name := l_period_name49;
2146 l_st_dt_4_end_pd := l_period49_start_date;
2147 l_total_pds := 49;
2148 l_pd_name_map_tab(l_cnt) := l_period_name49;
2149 l_st_date_map_tab(l_cnt) := l_period49_start_date;
2150 END IF;
2151 IF l_period50_start_date IS NOT NULL THEN
2152 l_cnt := l_cnt + 1;
2153 l_end_period_name := l_period_name50;
2154 l_st_dt_4_end_pd := l_period50_start_date;
2155 l_total_pds := 50;
2156 l_pd_name_map_tab(l_cnt) := l_period_name50;
2157 l_st_date_map_tab(l_cnt) := l_period50_start_date;
2158 END IF;
2159 IF l_period51_start_date IS NOT NULL THEN
2160 l_cnt := l_cnt + 1;
2161 l_end_period_name := l_period_name51;
2162 l_st_dt_4_end_pd := l_period51_start_date;
2163 l_total_pds := 51;
2164 l_pd_name_map_tab(l_cnt) := l_period_name51;
2165 l_st_date_map_tab(l_cnt) := l_period51_start_date;
2166 END IF;
2167 IF l_period52_start_date IS NOT NULL THEN
2168 l_cnt := l_cnt + 1;
2169 l_end_period_name := l_period_name52;
2170 l_st_dt_4_end_pd := l_period52_start_date;
2171 l_total_pds := 52;
2172 l_pd_name_map_tab(l_cnt) := l_period_name52;
2173 l_st_date_map_tab(l_cnt) := l_period52_start_date;
2174 END IF;
2175
2179 -- Getting the period_set_name and gl_period_type from period profile table
2176 -- This step is only for financial planning module
2177 IF (p_calling_module = 'FINANCIAL_PLANNING') THEN
2178
2180
2181 SELECT pa_prof.Plan_Period_Type
2182 INTO l_plan_period_type
2183 FROM Pa_Proj_Period_Profiles pa_prof
2184 WHERE pa_prof.period_profile_id = p_period_profile_id;
2185
2186 -- Calling the API to populate the budget lines table
2187 l_project_id := p_project_id;
2188 l_budget_version_id := p_budget_version_id;
2189 l_count_for_pop_call := 0;
2190
2191 /* Change for Bug 2641475 Starts */
2192
2193 -- Get the minimum of start date and maximum of start date for this
2194 -- resource assignment id from the fin plan lines table table:
2195
2196 SELECT min(pfpltmp.start_date),
2197 max(pfpltmp.start_date)
2198 INTO l_min_pa_fp_ln_tmp_st_dt,
2199 l_max_pa_fp_ln_tmp_st_dt
2200 FROM pa_fin_plan_lines_tmp pfpltmp;
2201
2202 -- Get the projfunc and project currency code for this project id
2203 SELECT project_currency_code,
2204 projfunc_currency_code
2205 INTO l_project_currency_code,
2206 l_projfunc_currency_code
2207 FROM pa_projects_all
2208 WHERE project_id = l_project_id;
2209
2210 /* Change for Bug 2641475 Ends */
2211
2212 SELECT count(*) into l_count_for_pop_call FROM PA_FIN_PLAN_LINES_TMP
2213 WHERE bucketing_period_code = 'PD';
2214 IF (l_count_for_pop_call <> 0) THEN
2215 Populate_Budget_Lines
2216 (
2217 p_bucketing_period_code => 'PD',
2218 p_st_dt_4_st_pd => l_st_dt_4_st_pd,
2219 p_st_dt_4_end_pd => l_st_dt_4_end_pd,
2220 p_plan_period_type => l_plan_period_type,
2221 p_project_id => l_project_id,
2222 p_budget_version_id => l_budget_version_id,
2223 p_project_currency_code => l_project_currency_code,
2224 p_projfunc_currency_code => l_projfunc_currency_code,
2225 x_return_status => x_return_status,
2226 x_msg_count => x_msg_count ,
2227 x_msg_data => x_msg_data
2228 );
2229
2230 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2231 --DBMS_OUTPUT.PUT_LINE('Error in call to Populate Budget Lines');
2232 RETURN;
2233 END IF;
2234 ELSE
2235 /* Change for Bug 2641475 Starts */
2236 -- Will come here only in case of entire budget version refresh
2237 -- or upgrade or other cases (i.e., whenever no PD records are
2238 -- populated in fin plan lines tmp)
2239
2240 -- Check the start dates in fin plan lines tmp and period profiles
2241 -- If there is a date in fin plan lines tmp that is lower than
2242 -- period profile date then call populate budget lines
2243 IF ( NVL(l_min_pa_fp_ln_tmp_st_dt,l_st_dt_4_st_pd) < l_st_dt_4_st_pd) THEN
2244 -- Call populate budget lines with bucketing period code PD
2245 Populate_Budget_Lines
2246 (
2247 p_bucketing_period_code => 'PD',
2248 p_st_dt_4_st_pd => l_st_dt_4_st_pd,
2249 p_st_dt_4_end_pd => l_st_dt_4_end_pd,
2250 p_plan_period_type => l_plan_period_type,
2251 p_project_id => l_project_id,
2252 p_budget_version_id => l_budget_version_id,
2253 p_project_currency_code => l_project_currency_code,
2254 p_projfunc_currency_code => l_projfunc_currency_code,
2255 x_return_status => x_return_status,
2256 x_msg_count => x_msg_count ,
2257 x_msg_data => x_msg_data
2258 );
2259
2260 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2261 --DBMS_OUTPUT.PUT_LINE('Error in call to Populate Budget Lines');
2262 PA_DEBUG.Reset_Curr_Function;
2263 RETURN;
2264 END IF;
2265 END IF;
2266 /* Change for Bug 2641475 ends */
2267 END IF;
2268
2269 SELECT count(*) into l_count_for_pop_call FROM PA_FIN_PLAN_LINES_TMP
2270 WHERE bucketing_period_code = 'SD';
2271 IF (l_count_for_pop_call <> 0) THEN
2272 Populate_Budget_Lines
2273 (
2274 p_bucketing_period_code => 'SD',
2275 p_st_dt_4_st_pd => l_st_dt_4_st_pd,
2276 p_st_dt_4_end_pd => l_st_dt_4_end_pd,
2277 p_plan_period_type => l_plan_period_type,
2278 p_project_id => l_project_id,
2279 p_budget_version_id => l_budget_version_id,
2280 p_project_currency_code => l_project_currency_code,
2281 p_projfunc_currency_code => l_projfunc_currency_code,
2282 x_return_status => x_return_status,
2286 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2283 x_msg_count => x_msg_count ,
2284 x_msg_data => x_msg_data
2285 );
2287 --DBMS_OUTPUT.PUT_LINE('Error in call to Populate Budget Lines');
2288 PA_DEBUG.Reset_Curr_Function;
2289 RETURN;
2290 END IF;
2291 ELSE
2292 /* Change for Bug 2641475 Starts */
2293 -- Will come here only in case of entire budget version refresh
2294 -- or upgrade or other cases (i.e., whenever no SD records are
2295 -- populated in fin plan lines tmp)
2296
2297 -- Check the start dates in fin plan lines tmp and period profiles
2298 -- If there is a date in fin plan lines tmp that is higher than
2299 -- period profile date then call populate budget lines
2300 IF ( NVL(l_max_pa_fp_ln_tmp_st_dt,l_st_dt_4_end_pd) > l_st_dt_4_end_pd) THEN
2301 -- Call populate budget lines with bucketing period code SD
2302 Populate_Budget_Lines
2303 (
2304 p_bucketing_period_code => 'SD',
2305 p_st_dt_4_st_pd => l_st_dt_4_st_pd,
2306 p_st_dt_4_end_pd => l_st_dt_4_end_pd,
2307 p_plan_period_type => l_plan_period_type,
2308 p_project_id => l_project_id,
2309 p_budget_version_id => l_budget_version_id,
2310 p_project_currency_code => l_project_currency_code,
2311 p_projfunc_currency_code => l_projfunc_currency_code,
2312 x_return_status => x_return_status,
2313 x_msg_count => x_msg_count ,
2314 x_msg_data => x_msg_data
2315 );
2316
2317 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2318 --DBMS_OUTPUT.PUT_LINE('Error in call to Populate Budget Lines');
2319 PA_DEBUG.Reset_Curr_Function;
2320 RETURN;
2321 END IF;
2322 END IF;
2323 /* Change for Bug 2641475 ends */
2324 END IF;
2325
2326 END IF; --For call of financial planning
2327
2328 FOR main_cur_rec IN MAIN_CUR LOOP
2329 l_temp := 1;
2330
2331 OPEN BL_CUR(main_cur_rec.resource_assignment_id,
2332 main_cur_rec.object_id,
2333 main_cur_rec.object_type_code,
2334 main_cur_rec.currency_type,
2335 main_cur_rec.currency_code,
2336 main_cur_rec.source_txn_currency_code,
2337 l_st_dt_4_st_pd,
2338 l_st_dt_4_end_pd );
2339 FETCH BL_CUR BULK COLLECT INTO
2340 l_period_name_tab,
2341 l_start_date_tab,
2342 l_qty_tab,
2343 l_raw_cost_tab,
2344 l_burd_cost_tab,
2345 l_revenue_tab,
2346 l_old_qty_tab,
2347 l_old_raw_cost_tab,
2348 l_old_burd_cost_tab,
2349 l_old_revenue_tab,
2350 l_borr_rev_tab,
2351 l_cc_rev_in_tab,
2352 l_cc_rev_out_tab,
2353 l_rev_adj_tab,
2354 l_lent_res_cost_tab,
2355 l_cc_cost_in_tab,
2356 l_cc_cost_out_tab,
2357 l_cost_adj_tab,
2358 l_unasg_time_cost_tab,
2359 l_util_per_tab,
2360 l_util_adj_tab,
2361 l_util_hrs_tab,
2362 l_capacity_tab,
2363 l_head_count_tab,
2364 l_head_count_adj_tab,
2365 l_margin_tab,
2366 l_margin_perc_tab,
2367 l_bucketing_period_code_tab, --added this column
2368 l_parent_assign_id_tab, --added this column
2369 l_delete_flag_tab; --added this column
2370 CLOSE BL_CUR;
2371
2372 l_prev_raw_cost := NULL;
2373 l_prev_burd_cost := NULL;
2374 l_prev_revenue := NULL;
2375 l_prev_quantity := NULL;
2376 l_prev_borr_revenue := NULL;
2377 l_prev_cc_rev_in := NULL;
2378 l_prev_cc_rev_out := NULL;
2379 l_prev_rev_adj := NULL;
2380 l_prev_lent_res_cost := NULL;
2381 l_prev_cc_cost_in := NULL;
2382 l_prev_cc_cost_out := NULL;
2383 l_prev_cost_adj := NULL;
2384 l_prev_unasg_time_cost := NULL;
2385 l_prev_util_per := NULL;
2386 l_prev_util_adj := NULL;
2387 l_prev_util_hrs := NULL;
2388 l_prev_capacity := NULL;
2389 l_prev_head_count := NULL;
2390 l_prev_head_count_adj := NULL;
2391 l_prev_margin := NULL;
2392 l_prev_margin_perc := NULL;
2393 l_prev_txn_raw_cost := NULL;
2394 l_prev_txn_burd_cost := NULL;
2395 l_prev_txn_revenue := NULL;
2396 l_prev_proj_raw_cost := NULL;
2397 l_prev_proj_burd_cost := NULL;
2398 l_prev_proj_revenue := NULL;
2399
2400 l_next_raw_cost := NULL;
2401 l_next_burd_cost := NULL;
2402 l_next_revenue := NULL;
2403 l_next_quantity := NULL;
2404 l_next_borr_revenue := NULL;
2405 l_next_cc_rev_in := NULL;
2406 l_next_cc_rev_out := NULL;
2407 l_next_rev_adj := NULL;
2408 l_next_lent_res_cost := NULL;
2409 l_next_cc_cost_in := NULL;
2413 l_next_util_per := NULL;
2410 l_next_cc_cost_out := NULL;
2411 l_next_cost_adj := NULL;
2412 l_next_unasg_time_cost := NULL;
2414 l_next_util_adj := NULL;
2415 l_next_util_hrs := NULL;
2416 l_next_capacity := NULL;
2417 l_next_head_count := NULL;
2418 l_next_head_count_adj := NULL;
2419 l_next_margin := NULL;
2420 l_next_margin_perc := NULL;
2421 l_next_txn_raw_cost := NULL;
2422 l_next_txn_burd_cost := NULL;
2423 l_next_txn_revenue := NULL;
2424 l_next_proj_raw_cost := NULL;
2425 l_next_proj_burd_cost := NULL;
2426 l_next_proj_revenue := NULL;
2427
2428 SELECT SUM(NVL(bl.Raw_Cost,0)),
2429 SUM(NVL(bl.Burdened_Cost,0)),
2430 SUM(NVL(bl.Revenue,0)),
2431 SUM(NVL(bl.Quantity,0)),
2432 SUM(NVL(bl.Borrowed_Revenue,0)),
2433 SUM(NVL(bl.Tp_Revenue_In,0)),
2434 SUM(NVL(bl.Tp_Revenue_Out,0)),
2435 SUM(NVL(bl.Revenue_Adj,0)),
2436 SUM(NVL(bl.Lent_Resource_Cost,0)),
2437 SUM(NVL(bl.Tp_Cost_In,0)),
2438 SUM(NVL(bl.Tp_Cost_Out,0)),
2439 SUM(NVL(bl.Cost_Adj,0)),
2440 SUM(NVL(bl.Unassigned_Time_Cost,0)),
2441 SUM(NVL(bl.Utilization_Percent,0)),
2442 SUM(NVL(bl.Utilization_Adj,0)),
2443 SUM(NVL(bl.Utilization_Hours,0)),
2444 SUM(NVL(bl.Capacity,0)),
2445 SUM(NVL(bl.Head_Count,0)),
2446 SUM(NVL(bl.Head_Count_Adj,0)),
2447 SUM(NVL(bl.Margin,0)),
2448 SUM(NVL(bl.Margin_Percentage,0)) INTO
2449 l_prev_raw_cost,
2450 l_prev_burd_cost,
2451 l_prev_revenue,
2452 l_prev_quantity,
2453 l_prev_borr_revenue,
2454 l_prev_cc_rev_in,
2455 l_prev_cc_rev_out,
2456 l_prev_rev_adj,
2457 l_prev_lent_res_cost,
2458 l_prev_cc_cost_in,
2459 l_prev_cc_cost_out,
2460 l_prev_cost_adj,
2461 l_prev_unasg_time_cost,
2462 l_prev_util_per,
2463 l_prev_util_adj,
2464 l_prev_util_hrs,
2465 l_prev_capacity,
2466 l_prev_head_count,
2467 l_prev_head_count_adj,
2468 l_prev_margin,
2469 l_prev_margin_perc
2470 FROM
2471 Pa_Fin_Plan_Lines_Tmp bl WHERE
2472 bl.Resource_Assignment_Id = main_cur_rec.resource_assignment_id AND
2473 bl.Object_Type_Code = main_cur_rec.object_type_code AND
2474 bl.Object_Id = main_cur_rec.object_id AND
2475 bl.Currency_Type = main_cur_rec.currency_type AND
2476 bl.Currency_Code = main_cur_rec.currency_code AND
2477 bl.start_date < l_st_dt_4_st_pd;
2478
2479 SELECT SUM(NVL(bl.Raw_Cost,0)),
2480 SUM(NVL(bl.Burdened_Cost,0)),
2481 SUM(NVL(bl.Revenue,0)),
2482 SUM(NVL(bl.Quantity,0)),
2483 SUM(NVL(bl.Borrowed_Revenue,0)),
2484 SUM(NVL(bl.Tp_Revenue_In,0)),
2485 SUM(NVL(bl.Tp_Revenue_Out,0)),
2486 SUM(NVL(bl.Revenue_Adj,0)),
2487 SUM(NVL(bl.Lent_Resource_Cost,0)),
2488 SUM(NVL(bl.Tp_Cost_In,0)),
2489 SUM(NVL(bl.Tp_Cost_Out,0)),
2490 SUM(NVL(bl.Cost_Adj,0)),
2491 SUM(NVL(bl.Unassigned_Time_Cost,0)),
2492 SUM(NVL(bl.Utilization_Percent,0)),
2493 SUM(NVL(bl.Utilization_Adj,0)),
2494 SUM(NVL(bl.Utilization_Hours,0)),
2495 SUM(NVL(bl.Capacity,0)),
2496 SUM(NVL(bl.Head_Count,0)),
2497 SUM(NVL(bl.Head_Count_Adj,0)),
2498 SUM(NVL(bl.Margin,0)),
2499 SUM(NVL(bl.Margin_Percentage,0)) INTO
2500 l_next_raw_cost,
2501 l_next_burd_cost,
2502 l_next_revenue,
2503 l_next_quantity,
2504 l_next_borr_revenue,
2505 l_next_cc_rev_in,
2506 l_next_cc_rev_out,
2507 l_next_rev_adj,
2508 l_next_lent_res_cost,
2509 l_next_cc_cost_in,
2510 l_next_cc_cost_out,
2511 l_next_cost_adj,
2512 l_next_unasg_time_cost,
2513 l_next_util_per,
2514 l_next_util_adj,
2515 l_next_util_hrs,
2516 l_next_capacity,
2517 l_next_head_count,
2518 l_next_head_count_adj,
2519 l_prev_margin,
2520 l_prev_margin_perc FROM
2521 Pa_Fin_Plan_Lines_Tmp bl WHERE
2522 bl.Resource_Assignment_Id = main_cur_rec.resource_assignment_id AND
2523 bl.Object_Type_Code = main_cur_rec.object_type_code AND
2524 bl.Object_Id = main_cur_rec.object_id AND
2525 bl.Currency_Type = main_cur_rec.currency_type AND
2526 bl.Currency_Code = main_cur_rec.currency_code AND
2527 bl.Start_Date > l_st_dt_4_end_pd;
2528
2532
2529 -- This step is only for financial planning module
2530 IF (p_calling_module = 'FINANCIAL_PLANNING') THEN
2531 -- Selecting for transaction currency
2533 SELECT SUM(NVL(bl.Txn_Raw_Cost,0)),
2534 SUM(NVL(bl.Txn_Burdened_Cost,0)),
2535 SUM(NVL(bl.Txn_Revenue,0)),
2536 SUM(NVL(bl.Quantity,0))
2537 INTO
2538 l_prev_txn_raw_cost,
2539 l_prev_txn_burd_cost,
2540 l_prev_txn_revenue,
2541 l_prev_quantity
2542 FROM
2543 Pa_Budget_Lines bl WHERE
2544 bl.Resource_Assignment_Id = main_cur_rec.resource_assignment_id AND
2545 bl.Txn_Currency_Code = main_cur_rec.source_txn_currency_code AND
2546 bl.bucketing_period_code in ('PE','PD')
2547 AND budget_version_id = p_budget_version_id;
2548
2549
2550 -- Selecting for project currency and proj functional currency
2551
2552 SELECT SUM(NVL(bl.Raw_Cost,0)),
2553 SUM(NVL(bl.Burdened_Cost,0)),
2554 SUM(NVL(bl.Revenue,0)),
2555 SUM(NVL(bl.Project_Raw_Cost,0)),
2556 SUM(NVL(bl.Project_Burdened_Cost,0)),
2557 SUM(NVL(bl.Project_Revenue,0))
2558 INTO
2559 l_prev_raw_cost,
2560 l_prev_burd_cost,
2561 l_prev_revenue,
2562 l_prev_proj_raw_cost,
2563 l_prev_proj_burd_cost,
2564 l_prev_proj_revenue
2565 FROM
2566 Pa_Budget_Lines bl WHERE
2567 bl.Resource_Assignment_Id = main_cur_rec.resource_assignment_id AND
2568 bl.bucketing_period_code in ('PE','PD')
2569 AND budget_version_id = p_budget_version_id;
2570
2571 -- Selecting for transaction currency
2572
2573 SELECT SUM(NVL(bl.Txn_Raw_Cost,0)),
2574 SUM(NVL(bl.Txn_Burdened_Cost,0)),
2575 SUM(NVL(bl.Txn_Revenue,0)),
2576 SUM(NVL(bl.Quantity,0))
2577 INTO
2578 l_next_txn_raw_cost,
2579 l_next_txn_burd_cost,
2580 l_next_txn_revenue,
2581 l_next_quantity
2582 FROM
2583 Pa_Budget_Lines bl WHERE
2584 bl.Resource_Assignment_Id = main_cur_rec.resource_assignment_id AND
2585 bl.Txn_Currency_Code = main_cur_rec.source_txn_currency_code AND
2586 bl.bucketing_period_code in ('SE','SD')
2587 AND budget_version_id = p_budget_version_id;
2588
2589 -- Selecting for project currency and proj functional currency
2590
2591 SELECT SUM(NVL(bl.Raw_Cost,0)),
2592 SUM(NVL(bl.Burdened_Cost,0)),
2593 SUM(NVL(bl.Revenue,0)),
2594 SUM(NVL(bl.Project_Raw_Cost,0)),
2595 SUM(NVL(bl.Project_Burdened_Cost,0)),
2596 SUM(NVL(bl.Project_Revenue,0))
2597 INTO
2598 l_next_raw_cost,
2599 l_next_burd_cost,
2600 l_next_revenue,
2601 l_next_proj_raw_cost,
2602 l_next_proj_burd_cost,
2603 l_next_proj_revenue
2604 FROM
2605 Pa_Budget_Lines bl WHERE
2606 bl.Resource_Assignment_Id = main_cur_rec.resource_assignment_id AND
2607 bl.bucketing_period_code in ('SE','SD')
2608 AND budget_version_id = p_budget_version_id;
2609 END IF; --For Financial Planning call
2610
2611 FOR l_plsql_cnt IN 1 .. p_amount_type_tab.count
2612 LOOP
2613
2614 -- PA_DEBUG.g_err_stage := 'Processing amt subtype code:'||
2615 -- p_amount_type_tab(l_plsql_cnt).amount_subtype_code;
2616 -- PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2617 -- For Org Forecasting, Burdened Cost maps to Own Project Cost
2618 l_valid_amount_flag := 'Y';
2619 l_quantity_filter_flag := 'Y';
2620 IF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'RAW_COST' THEN
2621 IF (main_cur_rec.currency_type = 'TRANSACTION') THEN
2622 l_fcst_amount_tab := l_raw_cost_tab;
2623 l_fcst_old_amount_tab := l_old_raw_cost_tab;
2624 l_prev_amt := l_prev_txn_raw_cost;
2625 l_next_amt := l_next_txn_raw_cost;
2626 ELSIF (main_cur_rec.currency_type = 'PROJ_FUNCTIONAL') THEN
2627 l_fcst_amount_tab := l_raw_cost_tab;
2628 l_fcst_old_amount_tab := l_old_raw_cost_tab;
2629 l_prev_amt := l_prev_raw_cost;
2630 l_next_amt := l_next_raw_cost;
2631 ELSIF (main_cur_rec.currency_type = 'PROJECT') THEN
2632 l_fcst_amount_tab := l_raw_cost_tab;
2633 l_fcst_old_amount_tab := l_old_raw_cost_tab;
2634 l_prev_amt := l_prev_proj_raw_cost;
2635 l_next_amt := l_next_proj_raw_cost;
2636 END IF;
2637 ELSIF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'OWN_PROJECT_COST' THEN
2638 l_fcst_amount_tab := l_burd_cost_tab;
2642 IF (main_cur_rec.currency_type = 'TRANSACTION') THEN
2639 l_prev_amt := l_prev_burd_cost;
2640 l_next_amt := l_next_burd_cost;
2641 ELSIF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'BURDENED_COST' THEN
2643 l_fcst_amount_tab := l_burd_cost_tab;
2644 l_fcst_old_amount_tab := l_old_burd_cost_tab;
2645 l_prev_amt := l_prev_txn_burd_cost;
2646 l_next_amt := l_next_txn_burd_cost;
2647 ELSIF (main_cur_rec.currency_type = 'PROJ_FUNCTIONAL') THEN
2648 l_fcst_amount_tab := l_burd_cost_tab;
2649 l_fcst_old_amount_tab := l_old_burd_cost_tab;
2650 l_prev_amt := l_prev_burd_cost;
2651 l_next_amt := l_next_burd_cost;
2652 ELSIF (main_cur_rec.currency_type = 'PROJECT') THEN
2653 l_fcst_amount_tab := l_burd_cost_tab;
2654 l_fcst_old_amount_tab := l_old_burd_cost_tab;
2655 l_prev_amt := l_prev_proj_burd_cost;
2656 l_next_amt := l_next_proj_burd_cost;
2657 END IF;
2658 ELSIF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'LENT_RESOURCE_COST' THEN
2659 l_fcst_amount_tab := l_lent_res_cost_tab;
2660 l_prev_amt := l_prev_lent_res_cost;
2661 l_next_amt := l_next_lent_res_cost;
2662 ELSIF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'TP_COST_IN' THEN
2663 l_fcst_amount_tab := l_cc_cost_in_tab;
2664 l_prev_amt := l_prev_cc_cost_in;
2665 l_next_amt := l_next_cc_cost_in;
2666 ELSIF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'TP_COST_OUT' THEN
2667 l_fcst_amount_tab := l_cc_cost_out_tab;
2668 l_prev_amt := l_prev_cc_cost_out;
2669 l_next_amt := l_next_cc_cost_out;
2670 ELSIF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'COST_ADJUSTMENTS' THEN
2671 l_fcst_amount_tab := l_cost_adj_tab;
2672 l_prev_amt := l_prev_cost_adj;
2673 l_next_amt := l_next_cost_adj;
2674 ELSIF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'UNASSIGNED_TIME_COST' THEN
2675 l_fcst_amount_tab := l_unasg_time_cost_tab;
2676 l_prev_amt := l_prev_unasg_time_cost;
2677 l_next_amt := l_next_unasg_time_cost;
2678 ELSIF (p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'OWN_REVENUE' OR
2679 p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'REVENUE') THEN
2680 IF (main_cur_rec.currency_type = 'TRANSACTION') THEN
2681 l_fcst_amount_tab := l_revenue_tab;
2682 l_fcst_old_amount_tab := l_old_revenue_tab;
2683 l_prev_amt := l_prev_txn_revenue;
2684 l_next_amt := l_next_txn_revenue;
2685 ELSIF (main_cur_rec.currency_type = 'PROJ_FUNCTIONAL') THEN
2686 l_fcst_amount_tab := l_revenue_tab;
2687 l_fcst_old_amount_tab := l_old_revenue_tab;
2688 l_prev_amt := l_prev_revenue;
2689 l_next_amt := l_next_revenue;
2690 ELSIF (main_cur_rec.currency_type = 'PROJECT') THEN
2691 l_fcst_amount_tab := l_revenue_tab;
2692 l_fcst_old_amount_tab := l_old_revenue_tab;
2693 l_prev_amt := l_prev_proj_revenue;
2694 l_next_amt := l_next_proj_revenue;
2695 END IF;
2696 ELSIF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'BORROWED_REVENUE' THEN
2697 l_fcst_amount_tab := l_borr_rev_tab;
2698 l_prev_amt := l_prev_borr_revenue;
2699 l_next_amt := l_next_borr_revenue;
2700 ELSIF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'TP_REVENUE_IN' THEN
2701 l_fcst_amount_tab := l_cc_rev_in_tab;
2702 l_prev_amt := l_prev_cc_rev_in;
2703 l_next_amt := l_next_cc_rev_in;
2704 ELSIF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'TP_REVENUE_OUT' THEN
2705 l_fcst_amount_tab := l_cc_rev_out_tab;
2706 l_prev_amt := l_prev_cc_rev_out;
2707 l_next_amt := l_next_cc_rev_out;
2708 ELSIF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'REVENUE_ADJUSTMENTS' THEN
2709 l_fcst_amount_tab := l_rev_adj_tab;
2710 l_prev_amt := l_prev_rev_adj;
2711 l_next_amt := l_next_rev_adj;
2712 ELSIF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'QUANTITY' THEN
2713 IF (main_cur_rec.currency_type = 'TRANSACTION') THEN
2714 l_fcst_amount_tab := l_qty_tab;
2715 l_fcst_old_amount_tab := l_old_qty_tab;
2716 l_prev_amt := l_prev_quantity;
2717 l_next_amt := l_next_quantity;
2718 ELSIF (p_calling_module = 'ORG_FORECAST') THEN
2719 l_fcst_amount_tab := l_qty_tab;
2720 l_prev_amt := l_prev_quantity;
2721 l_next_amt := l_next_quantity;
2722 ELSE
2723 l_quantity_filter_flag := 'N';
2724 END IF;
2725 ELSIF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'UTILIZATION_PERCENT' THEN
2726 l_fcst_amount_tab := l_util_per_tab;
2727 l_prev_amt := l_prev_util_per;
2728 l_next_amt := l_next_util_per;
2729 ELSIF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'UTILIZATION_ADJUSTMENTS' THEN
2730 l_fcst_amount_tab := l_util_adj_tab;
2731 l_prev_amt := l_prev_util_adj;
2732 l_next_amt := l_next_util_adj;
2736 l_next_amt := l_next_util_hrs;
2733 ELSIF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'UTILIZATION_HOURS' THEN
2734 l_fcst_amount_tab := l_util_hrs_tab;
2735 l_prev_amt := l_prev_util_hrs;
2737 ELSIF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'CAPACITY' THEN
2738 l_fcst_amount_tab := l_capacity_tab;
2739 l_prev_amt := l_prev_capacity;
2740 l_next_amt := l_next_capacity;
2741 ELSIF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'BEGIN_HEADCOUNT' THEN
2742 l_fcst_amount_tab := l_head_count_tab;
2743 l_prev_amt := l_prev_head_count;
2744 l_next_amt := l_next_head_count;
2745 ELSIF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'HEADCOUNT_ADJUSTMENTS' THEN
2746 l_fcst_amount_tab := l_head_count_adj_tab;
2747 l_prev_amt := l_prev_head_count_adj;
2748 l_next_amt := l_next_head_count_adj;
2749 ELSIF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'MARGIN' THEN
2750 l_fcst_amount_tab := l_margin_tab;
2751 l_prev_amt := l_prev_margin;
2752 l_next_amt := l_next_margin;
2753 ELSIF p_amount_type_tab(l_plsql_cnt).amount_subtype_code = 'MARGIN_PERCENT' THEN
2754 l_fcst_amount_tab := l_margin_perc_tab;
2755 l_prev_amt := l_prev_margin_perc;
2756 l_next_amt := l_next_margin_perc;
2757 ELSE
2758 l_valid_amount_flag := 'N';
2759 END IF;
2760 l_amt_type_code := p_amount_type_tab(l_plsql_cnt).amount_type_code;
2761 l_amt_subtype_code := p_amount_type_tab(l_plsql_cnt).amount_subtype_code;
2762 l_amt_type_id := p_amount_type_tab(l_plsql_cnt).amount_type_id;
2763 l_amt_subtype_id := p_amount_type_tab(l_plsql_cnt).amount_subtype_id;
2764 IF l_valid_amount_flag = 'N' THEN
2765 IF P_PA_DEBUG_MODE = 'Y' THEN
2766 PA_DEBUG.g_err_stage := 'Invalid Amt Type:'||l_amt_type_code;
2767 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2768 PA_DEBUG.g_err_stage := 'Invalid Amt Sub Type:'||l_amt_subtype_code;
2769 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2770 END IF;
2771 x_return_status := FND_API.G_RET_STS_ERROR;
2772 IF p_add_msg_in_stack = 'Y' THEN
2773 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2774 p_msg_name => 'PA_FP_INVALID_AMT_TYPE');
2775 ELSE
2776 x_msg_data := 'PA_FP_INVALID_AMT_TYPE';
2777 END IF;
2778 PA_DEBUG.Reset_Curr_Function;
2779 RETURN;
2780 END IF;
2781 l_res_asg_id := main_cur_rec.resource_assignment_id;
2782 l_obj_id := main_cur_rec.object_id;
2783 l_obj_type_code := main_cur_rec.object_type_code;
2784 l_currency_type := main_cur_rec.currency_type;
2785 l_currency_code := main_cur_rec.currency_code;
2786
2787 IF l_valid_amount_flag = 'Y' THEN
2788 IF p_prior_period_flag = 'Y' THEN
2789 BEGIN
2790 SELECT P.Prior_Period_Amount
2791 INTO
2792 l_prior_amt FROM
2793 Pa_Fp_Prior_Periods_Tmp P
2794 WHERE
2795 Resource_Assignment_Id = main_cur_rec.resource_assignment_id AND
2796 Object_Id = main_cur_rec.object_id AND
2797 Object_Type_Code = main_cur_rec.object_type_code AND
2798 Amount_Type_Code = l_amt_type_code AND
2799 Amount_Subtype_Code = l_amt_subtype_code AND
2800 Currency_Type = l_currency_type AND
2801 Currency_Code = l_currency_code;
2802 EXCEPTION
2803 WHEN NO_DATA_FOUND THEN
2804 l_prior_amt := NULL;
2805 END;
2806 END IF;
2807 /* these variables needs to be set for each amount type and
2808 currency type, otherwise it will lead to element at
2809 index [X] does not exist */
2810 IF (l_quantity_filter_flag = 'Y') THEN
2811 -- POPULATION OF PA_PROJ_PERIODS_DENORM
2812 -- Storing current amount values from denorm table
2813 IF (main_cur_rec.currency_type = 'TRANSACTION' OR p_calling_module = 'ORG_FORECAST') THEN
2814 BEGIN
2815 SELECT Period_Amount1,
2816 Period_Amount2,
2817 Period_Amount3,
2818 Period_Amount4,
2819 Period_Amount5,
2820 Period_Amount6,
2821 Period_Amount7,
2822 Period_Amount8,
2823 Period_Amount9,
2824 Period_Amount10,
2825 Period_Amount11,
2826 Period_Amount12,
2827 Period_Amount13,
2828 Period_Amount14,
2829 Period_Amount15,
2830 Period_Amount16,
2831 Period_Amount17,
2832 Period_Amount18,
2833 Period_Amount19,
2834 Period_Amount20,
2835 Period_Amount21,
2836 Period_Amount22,
2837 Period_Amount23,
2838 Period_Amount24,
2839 Period_Amount25,
2840 Period_Amount26,
2841 Period_Amount27,
2842 Period_Amount28,
2843 Period_Amount29,
2844 Period_Amount30,
2845 Period_Amount31,
2846 Period_Amount32,
2847 Period_Amount33,
2848 Period_Amount34,
2849 Period_Amount35,
2853 Period_Amount39,
2850 Period_Amount36,
2851 Period_Amount37,
2852 Period_Amount38,
2854 Period_Amount40,
2855 Period_Amount41,
2856 Period_Amount42,
2857 Period_Amount43,
2858 Period_Amount44,
2859 Period_Amount45,
2860 Period_Amount46,
2861 Period_Amount47,
2862 Period_Amount48,
2863 Period_Amount49,
2864 Period_Amount50,
2865 Period_Amount51,
2866 Period_Amount52,
2867 Parent_Assignment_id
2868 INTO l_pd_amt1 ,
2869 l_pd_amt2 ,
2870 l_pd_amt3 ,
2871 l_pd_amt4 ,
2872 l_pd_amt5 ,
2873 l_pd_amt6 ,
2874 l_pd_amt7 ,
2875 l_pd_amt8 ,
2876 l_pd_amt9 ,
2877 l_pd_amt10 ,
2878 l_pd_amt11 ,
2879 l_pd_amt12 ,
2880 l_pd_amt13 ,
2881 l_pd_amt14 ,
2882 l_pd_amt15 ,
2883 l_pd_amt16 ,
2884 l_pd_amt17 ,
2885 l_pd_amt18 ,
2886 l_pd_amt19 ,
2887 l_pd_amt20 ,
2888 l_pd_amt21 ,
2889 l_pd_amt22 ,
2890 l_pd_amt23 ,
2891 l_pd_amt24 ,
2892 l_pd_amt25 ,
2893 l_pd_amt26 ,
2894 l_pd_amt27 ,
2895 l_pd_amt28 ,
2896 l_pd_amt29 ,
2897 l_pd_amt30 ,
2898 l_pd_amt31 ,
2899 l_pd_amt32 ,
2900 l_pd_amt33 ,
2901 l_pd_amt34 ,
2902 l_pd_amt35 ,
2903 l_pd_amt36 ,
2904 l_pd_amt37 ,
2905 l_pd_amt38 ,
2906 l_pd_amt39 ,
2907 l_pd_amt40 ,
2908 l_pd_amt41 ,
2909 l_pd_amt42 ,
2910 l_pd_amt43 ,
2911 l_pd_amt44 ,
2912 l_pd_amt45 ,
2913 l_pd_amt46 ,
2914 l_pd_amt47 ,
2915 l_pd_amt48 ,
2916 l_pd_amt49 ,
2917 l_pd_amt50 ,
2918 l_pd_amt51 ,
2919 l_pd_amt52,
2920 l_parent_assignment_id
2921 FROM pa_proj_periods_denorm
2922 WHERE period_profile_id = p_period_profile_id AND
2923 Budget_Version_Id = p_budget_version_id AND
2924 project_id = p_project_id AND
2925 Resource_Assignment_Id = l_res_asg_id AND
2926 Object_Id = l_obj_id AND
2927 Object_Type_Code = l_obj_type_code AND
2928 Amount_Type_Code = l_amt_type_code AND
2929 Amount_Subtype_Code = l_amt_subtype_code AND
2930 Currency_Type = l_currency_type AND
2931 Currency_Code = l_currency_code;
2932 -- IF the SELECT FAILS with no data found exception
2933 -- Initializing the local variables for the amount types to NULL
2934 EXCEPTION
2935 WHEN NO_DATA_FOUND THEN
2936 l_pd_amt1 := NULL;
2937 l_pd_amt2 := NULL;
2938 l_pd_amt3 := NULL;
2939 l_pd_amt4 := NULL;
2940 l_pd_amt5 := NULL;
2941 l_pd_amt6 := NULL;
2942 l_pd_amt7 := NULL;
2943 l_pd_amt8 := NULL;
2944 l_pd_amt9 := NULL;
2945 l_pd_amt10 := NULL;
2946 l_pd_amt11 := NULL;
2947 l_pd_amt12 := NULL;
2948 l_pd_amt13 := NULL;
2949 l_pd_amt14 := NULL;
2950 l_pd_amt15 := NULL;
2951 l_pd_amt16 := NULL;
2952 l_pd_amt17 := NULL;
2953 l_pd_amt18 := NULL;
2954 l_pd_amt19 := NULL;
2955 l_pd_amt20 := NULL;
2956 l_pd_amt21 := NULL;
2957 l_pd_amt22 := NULL;
2958 l_pd_amt23 := NULL;
2959 l_pd_amt24 := NULL;
2960 l_pd_amt25 := NULL;
2961 l_pd_amt26 := NULL;
2962 l_pd_amt27 := NULL;
2963 l_pd_amt28 := NULL;
2964 l_pd_amt29 := NULL;
2965 l_pd_amt30 := NULL;
2966 l_pd_amt31 := NULL;
2967 l_pd_amt32 := NULL;
2968 l_pd_amt33 := NULL;
2969 l_pd_amt34 := NULL;
2970 l_pd_amt35 := NULL;
2971 l_pd_amt36 := NULL;
2972 l_pd_amt37 := NULL;
2973 l_pd_amt38 := NULL;
2974 l_pd_amt39 := NULL;
2975 l_pd_amt40 := NULL;
2976 l_pd_amt41 := NULL;
2977 l_pd_amt42 := NULL;
2978 l_pd_amt43 := NULL;
2979 l_pd_amt44 := NULL;
2980 l_pd_amt45 := NULL;
2981 l_pd_amt46 := NULL;
2982 l_pd_amt47 := NULL;
2983 l_pd_amt48 := NULL;
2984 l_pd_amt49 := NULL;
2985 l_pd_amt50 := NULL;
2986 l_pd_amt51 := NULL;
2987 l_pd_amt52 := NULL;
2988 l_parent_assignment_id := NULL;
2989 END;
2990 ELSE
2991 BEGIN
2992 SELECT 0,
2993 0,
2994 0,
2995 0,
2996 0,
2997 0,
2998 0,
2999 0,
3000 0,
3001 0,
3002 0,
3003 0,
3004 0,
3005 0,
3006 0,
3007 0,
3008 0,
3012 0,
3009 0,
3010 0,
3011 0,
3013 0,
3014 0,
3015 0,
3016 0,
3017 0,
3018 0,
3019 0,
3020 0,
3021 0,
3022 0,
3023 0,
3024 0,
3025 0,
3026 0,
3027 0,
3028 0,
3029 0,
3030 0,
3031 0,
3032 0,
3033 0,
3034 0,
3035 0,
3036 0,
3037 0,
3038 0,
3039 0,
3040 0,
3041 0,
3042 0,
3043 0,
3044 Parent_Assignment_id
3045 INTO l_pd_amt1 ,
3046 l_pd_amt2 ,
3047 l_pd_amt3 ,
3048 l_pd_amt4 ,
3049 l_pd_amt5 ,
3050 l_pd_amt6 ,
3051 l_pd_amt7 ,
3052 l_pd_amt8 ,
3053 l_pd_amt9 ,
3054 l_pd_amt10 ,
3055 l_pd_amt11 ,
3056 l_pd_amt12 ,
3057 l_pd_amt13 ,
3058 l_pd_amt14 ,
3059 l_pd_amt15 ,
3060 l_pd_amt16 ,
3061 l_pd_amt17 ,
3062 l_pd_amt18 ,
3063 l_pd_amt19 ,
3064 l_pd_amt20 ,
3065 l_pd_amt21 ,
3066 l_pd_amt22 ,
3067 l_pd_amt23 ,
3068 l_pd_amt24 ,
3069 l_pd_amt25 ,
3070 l_pd_amt26 ,
3071 l_pd_amt27 ,
3072 l_pd_amt28 ,
3073 l_pd_amt29 ,
3074 l_pd_amt30 ,
3075 l_pd_amt31 ,
3076 l_pd_amt32 ,
3077 l_pd_amt33 ,
3078 l_pd_amt34 ,
3079 l_pd_amt35 ,
3080 l_pd_amt36 ,
3081 l_pd_amt37 ,
3082 l_pd_amt38 ,
3083 l_pd_amt39 ,
3084 l_pd_amt40 ,
3085 l_pd_amt41 ,
3086 l_pd_amt42 ,
3087 l_pd_amt43 ,
3088 l_pd_amt44 ,
3089 l_pd_amt45 ,
3090 l_pd_amt46 ,
3091 l_pd_amt47 ,
3092 l_pd_amt48 ,
3093 l_pd_amt49 ,
3094 l_pd_amt50 ,
3095 l_pd_amt51 ,
3096 l_pd_amt52,
3097 l_parent_assignment_id
3098 FROM pa_proj_periods_denorm
3099 WHERE period_profile_id = p_period_profile_id AND
3100 Budget_Version_Id = p_budget_version_id AND
3101 project_id = p_project_id AND
3102 Resource_Assignment_Id = l_res_asg_id AND
3103 Object_Id = l_obj_id AND
3104 Object_Type_Code = l_obj_type_code AND
3105 Amount_Type_Code = l_amt_type_code AND
3106 Amount_Subtype_Code = l_amt_subtype_code AND
3107 Currency_Type = l_currency_type AND
3108 Currency_Code = l_currency_code;
3109 -- IF the SELECT FAILS with no data found exception
3110 -- Initializing the local variables for the amount types to NULL
3111 EXCEPTION
3112 WHEN NO_DATA_FOUND THEN
3113 l_pd_amt1 := NULL;
3114 l_pd_amt2 := NULL;
3115 l_pd_amt3 := NULL;
3116 l_pd_amt4 := NULL;
3117 l_pd_amt5 := NULL;
3118 l_pd_amt6 := NULL;
3119 l_pd_amt7 := NULL;
3120 l_pd_amt8 := NULL;
3121 l_pd_amt9 := NULL;
3122 l_pd_amt10 := NULL;
3123 l_pd_amt11 := NULL;
3124 l_pd_amt12 := NULL;
3125 l_pd_amt13 := NULL;
3126 l_pd_amt14 := NULL;
3127 l_pd_amt15 := NULL;
3128 l_pd_amt16 := NULL;
3129 l_pd_amt17 := NULL;
3130 l_pd_amt18 := NULL;
3131 l_pd_amt19 := NULL;
3132 l_pd_amt20 := NULL;
3133 l_pd_amt21 := NULL;
3134 l_pd_amt22 := NULL;
3135 l_pd_amt23 := NULL;
3136 l_pd_amt24 := NULL;
3137 l_pd_amt25 := NULL;
3138 l_pd_amt26 := NULL;
3139 l_pd_amt27 := NULL;
3140 l_pd_amt28 := NULL;
3141 l_pd_amt29 := NULL;
3142 l_pd_amt30 := NULL;
3143 l_pd_amt31 := NULL;
3144 l_pd_amt32 := NULL;
3145 l_pd_amt33 := NULL;
3146 l_pd_amt34 := NULL;
3147 l_pd_amt35 := NULL;
3148 l_pd_amt36 := NULL;
3149 l_pd_amt37 := NULL;
3150 l_pd_amt38 := NULL;
3151 l_pd_amt39 := NULL;
3152 l_pd_amt40 := NULL;
3153 l_pd_amt41 := NULL;
3154 l_pd_amt42 := NULL;
3155 l_pd_amt43 := NULL;
3156 l_pd_amt44 := NULL;
3157 l_pd_amt45 := NULL;
3158 l_pd_amt46 := NULL;
3159 l_pd_amt47 := NULL;
3160 l_pd_amt48 := NULL;
3161 l_pd_amt49 := NULL;
3162 l_pd_amt50 := NULL;
3163 l_pd_amt51 := NULL;
3164 l_pd_amt52 := NULL;
3165 l_parent_assignment_id := NULL;
3166 END;
3167 END IF;
3168 /* For select from proj_period_denorm based on currency type */
3169
3170 /* Reason for checking Period Start Date to map Denorm table columns.
3174 This model will be eventually used for existing Budget model also and
3171 Currently this denorm table is designed for Financial Planning and
3172 Organization Forecasting.
3173
3175 if the budget is not based on a period ( PA or GL ) then the Period Name
3176 will be NULL in Budget Lines Table. So checking with Period Start Date.
3177 SManivannan */
3178
3179 --Added by Vijay S Gautam
3180 FOR l_dummy IN 1 .. l_period_name_tab.count
3181 LOOP
3182 IF (p_calling_module = 'FINANCIAL_PLANNING') THEN
3183 l_start_date := l_start_date_tab(l_dummy);
3184 l_old_fcst_amt := NVL(l_fcst_old_amount_tab(l_dummy),0);
3185 IF (main_cur_rec.currency_type <> 'TRANSACTION') THEN
3186 IF (l_delete_flag_tab(l_dummy) = 'Y') THEN
3187 l_fcst_amt := NVL(-l_old_fcst_amt,0);
3188 ELSE
3189 l_fcst_amt := NVL(l_fcst_amount_tab(l_dummy),0) - NVL(l_old_fcst_amt,0);
3190 END IF;
3191 ELSE
3192 IF (l_delete_flag_tab(l_dummy) = 'Y') THEN
3193 l_fcst_amt := NULL;
3194 ELSE
3195 l_fcst_amt := l_fcst_amount_tab(l_dummy);
3196 END IF;
3197 END IF;
3198 l_parent_assign_id := l_parent_assign_id_tab (1); --assign value from PL/SQL table
3199 END IF;
3200
3201 IF (p_calling_module = 'ORG_FORECAST') THEN
3202 l_start_date := l_start_date_tab(l_dummy);
3203 l_fcst_amt := l_fcst_amount_tab(l_dummy);
3204 l_parent_assign_id := l_parent_assign_id_tab (1); --assign value from PL/SQL table
3205 END IF;
3206
3207 IF l_start_date = l_period1_start_date THEN
3208 l_pd_amt1 := l_fcst_amt;
3209 ELSIF l_start_date = l_period2_start_date THEN
3210 l_pd_amt2 := l_fcst_amt;
3211 ELSIF l_start_date = l_period3_start_date THEN
3212 l_pd_amt3 := l_fcst_amt;
3213 ELSIF l_start_date = l_period4_start_date THEN
3214 l_pd_amt4 := l_fcst_amt;
3215 ELSIF l_start_date = l_period5_start_date THEN
3216 l_pd_amt5 := l_fcst_amt;
3217 ELSIF l_start_date = l_period6_start_date THEN
3218 l_pd_amt6 := l_fcst_amt;
3219 ELSIF l_start_date = l_period7_start_date THEN
3220 l_pd_amt7 := l_fcst_amt;
3221 ELSIF l_start_date = l_period8_start_date THEN
3222 l_pd_amt8 := l_fcst_amt;
3223 ELSIF l_start_date = l_period9_start_date THEN
3224 l_pd_amt9 := l_fcst_amt;
3225 ELSIF l_start_date = l_period10_start_date THEN
3226 l_pd_amt10 := l_fcst_amt;
3227 ELSIF l_start_date = l_period11_start_date THEN
3228 l_pd_amt11 := l_fcst_amt;
3229 ELSIF l_start_date = l_period12_start_date THEN
3230 l_pd_amt12 := l_fcst_amt;
3231 ELSIF l_start_date = l_period13_start_date THEN
3232 l_pd_amt13 := l_fcst_amt;
3233 ELSIF l_start_date = l_period14_start_date THEN
3234 l_pd_amt14 := l_fcst_amt;
3235 ELSIF l_start_date = l_period15_start_date THEN
3236 l_pd_amt15 := l_fcst_amt;
3237 ELSIF l_start_date = l_period16_start_date THEN
3238 l_pd_amt16 := l_fcst_amt;
3239 ELSIF l_start_date = l_period17_start_date THEN
3240 l_pd_amt17 := l_fcst_amt;
3241 ELSIF l_start_date = l_period18_start_date THEN
3242 l_pd_amt18 := l_fcst_amt;
3243 ELSIF l_start_date = l_period19_start_date THEN
3244 l_pd_amt19 := l_fcst_amt;
3245 ELSIF l_start_date = l_period20_start_date THEN
3246 l_pd_amt20 := l_fcst_amt;
3247 ELSIF l_start_date = l_period21_start_date THEN
3248 l_pd_amt21 := l_fcst_amt;
3249 ELSIF l_start_date = l_period22_start_date THEN
3250 l_pd_amt22 := l_fcst_amt;
3251 ELSIF l_start_date = l_period23_start_date THEN
3252 l_pd_amt23 := l_fcst_amt;
3253 ELSIF l_start_date = l_period24_start_date THEN
3254 l_pd_amt24 := l_fcst_amt;
3255 ELSIF l_start_date = l_period25_start_date THEN
3256 l_pd_amt25 := l_fcst_amt;
3257 ELSIF l_start_date = l_period26_start_date THEN
3258 l_pd_amt26 := l_fcst_amt;
3259 ELSIF l_start_date = l_period27_start_date THEN
3260 l_pd_amt27 := l_fcst_amt;
3261 ELSIF l_start_date = l_period28_start_date THEN
3262 l_pd_amt28 := l_fcst_amt;
3263 ELSIF l_start_date = l_period29_start_date THEN
3264 l_pd_amt29 := l_fcst_amt;
3265 ELSIF l_start_date = l_period30_start_date THEN
3266 l_pd_amt30 := l_fcst_amt;
3267 ELSIF l_start_date = l_period31_start_date THEN
3268 l_pd_amt31 := l_fcst_amt;
3269 ELSIF l_start_date = l_period32_start_date THEN
3270 l_pd_amt32 := l_fcst_amt;
3274 l_pd_amt34 := l_fcst_amt;
3271 ELSIF l_start_date = l_period33_start_date THEN
3272 l_pd_amt33 := l_fcst_amt;
3273 ELSIF l_start_date = l_period34_start_date THEN
3275 ELSIF l_start_date = l_period35_start_date THEN
3276 l_pd_amt35 := l_fcst_amt;
3277 ELSIF l_start_date = l_period36_start_date THEN
3278 l_pd_amt36 := l_fcst_amt;
3279 ELSIF l_start_date = l_period37_start_date THEN
3280 l_pd_amt37 := l_fcst_amt;
3281 ELSIF l_start_date = l_period38_start_date THEN
3282 l_pd_amt38 := l_fcst_amt;
3283 ELSIF l_start_date = l_period39_start_date THEN
3284 l_pd_amt39 := l_fcst_amt;
3285 ELSIF l_start_date = l_period40_start_date THEN
3286 l_pd_amt40 := l_fcst_amt;
3287 ELSIF l_start_date = l_period41_start_date THEN
3288 l_pd_amt41 := l_fcst_amt;
3289 ELSIF l_start_date = l_period42_start_date THEN
3290 l_pd_amt42 := l_fcst_amt;
3291 ELSIF l_start_date = l_period43_start_date THEN
3292 l_pd_amt43 := l_fcst_amt;
3293 ELSIF l_start_date = l_period44_start_date THEN
3294 l_pd_amt44 := l_fcst_amt;
3295 ELSIF l_start_date = l_period45_start_date THEN
3296 l_pd_amt45 := l_fcst_amt;
3297 ELSIF l_start_date = l_period46_start_date THEN
3298 l_pd_amt46 := l_fcst_amt;
3299 ELSIF l_start_date = l_period47_start_date THEN
3300 l_pd_amt47 := l_fcst_amt;
3301 ELSIF l_start_date = l_period48_start_date THEN
3302 l_pd_amt48 := l_fcst_amt;
3303 ELSIF l_start_date = l_period49_start_date THEN
3304 l_pd_amt49 := l_fcst_amt;
3305 ELSIF l_start_date = l_period50_start_date THEN
3306 l_pd_amt50 := l_fcst_amt;
3307 ELSIF l_start_date = l_period51_start_date THEN
3308 l_pd_amt51 := l_fcst_amt;
3309 ELSIF l_start_date = l_period52_start_date THEN
3310 l_pd_amt52 := l_fcst_amt;
3311 END IF;
3312 END LOOP;
3313
3314 /* for period name tab */
3315 /* Earlier, it was planned that the matrix table will be deleted for the given
3316 budget version id and inserted again for all res asg ids. But later it has been
3317 decided that we have to update the matrix table, if res asg id exists, otherwise
3318 to insert the record. So to avoid using another 52 additional variables, the
3319 table is updated with the pl sql table entries. If the update count is successful,
3320 then all the recent pl sql table elements will be deleted.
3321 If this causes performancet problems, then this logic should be changed to
3322 include additional 52 local variables and pl sql table deletion could be
3323 avoided. */
3324
3325 -- Added by Vijay s Gautam
3326
3327 IF (main_cur_rec.currency_type = 'TRANSACTION' OR p_calling_module = 'ORG_FORECAST') THEN
3328 --
3329 UPDATE Pa_Proj_Periods_Denorm SET
3330 preceding_periods_amount = l_prev_amt,
3331 succeeding_periods_amount = l_next_amt,
3332 prior_period_amount = l_prior_amt,
3333 period_amount1 = l_pd_amt1,
3334 period_amount2 = l_pd_amt2,
3335 period_amount3 = l_pd_amt3,
3336 period_amount4 = l_pd_amt4,
3337 period_amount5 = l_pd_amt5,
3338 period_amount6 = l_pd_amt6,
3339 period_amount7 = l_pd_amt7,
3340 period_amount8 = l_pd_amt8,
3341 period_amount9 = l_pd_amt9,
3342 period_amount10 = l_pd_amt10,
3343 period_amount11 = l_pd_amt11,
3344 period_amount12 = l_pd_amt12,
3345 period_amount13 = l_pd_amt13,
3346 period_amount14 = l_pd_amt14,
3347 period_amount15 = l_pd_amt15,
3348 period_amount16 = l_pd_amt16,
3349 period_amount17 = l_pd_amt17,
3350 period_amount18 = l_pd_amt18,
3351 period_amount19 = l_pd_amt19,
3352 period_amount20 = l_pd_amt20,
3353 period_amount21 = l_pd_amt21,
3354 period_amount22 = l_pd_amt22,
3355 period_amount23 = l_pd_amt23,
3356 period_amount24 = l_pd_amt24,
3357 period_amount25 = l_pd_amt25,
3358 period_amount26 = l_pd_amt26,
3359 period_amount27 = l_pd_amt27,
3360 period_amount28 = l_pd_amt28,
3361 period_amount29 = l_pd_amt29,
3362 period_amount30 = l_pd_amt30,
3363 period_amount31 = l_pd_amt31,
3364 period_amount32 = l_pd_amt32,
3365 period_amount33 = l_pd_amt33,
3366 period_amount34 = l_pd_amt34,
3367 period_amount35 = l_pd_amt35,
3368 period_amount36 = l_pd_amt36,
3369 period_amount37 = l_pd_amt37,
3370 period_amount38 = l_pd_amt38,
3371 period_amount39 = l_pd_amt39,
3372 period_amount40 = l_pd_amt40,
3373 period_amount41 = l_pd_amt41,
3374 period_amount42 = l_pd_amt42,
3375 period_amount43 = l_pd_amt43,
3379 period_amount47 = l_pd_amt47,
3376 period_amount44 = l_pd_amt44,
3377 period_amount45 = l_pd_amt45,
3378 period_amount46 = l_pd_amt46,
3380 period_amount48 = l_pd_amt48,
3381 period_amount49 = l_pd_amt49,
3382 period_amount50 = l_pd_amt50,
3383 period_amount51 = l_pd_amt51,
3384 period_amount52 = l_pd_amt52,
3385 parent_assignment_id = DECODE(l_parent_assign_id, NULL, l_parent_assignment_id, l_parent_assign_id),
3386 LAST_UPDATE_LOGIN = l_last_update_login,
3387 LAST_UPDATED_BY = l_last_updated_by,
3388 LAST_UPDATE_DATE = l_last_update_date
3389 WHERE
3390 Budget_Version_Id = p_budget_version_id AND
3391 project_id = p_project_id AND
3392 Resource_Assignment_Id = l_res_asg_id AND
3393 Object_Id = l_obj_id AND
3394 Object_Type_Code = l_obj_type_code AND
3395 Amount_Type_Code = l_amt_type_code AND
3396 Amount_Subtype_Code = l_amt_subtype_code AND
3397 Currency_Type = l_currency_type AND
3398 Currency_Code = l_currency_code;
3399 IF SQL%ROWCOUNT = 0 THEN
3400 -- Get the value for parent assignment id through the decode function
3401 SELECT DECODE(l_parent_assign_id, NULL, l_parent_assignment_id, l_parent_assign_id)
3402 INTO l_parent_assign_id_local from DUAL;
3403
3404 l_prev_amt_tab(l_matrix_counter) := l_prev_amt;
3405 l_next_amt_tab(l_matrix_counter) := l_next_amt;
3406 l_prior_amt_tab(l_matrix_counter):= l_prior_amt;
3407 l_res_asg_id_tab(l_matrix_counter) := l_res_asg_id;
3408 l_obj_id_tab(l_matrix_counter) := l_obj_id;
3409 l_obj_type_code_tab(l_matrix_counter):= l_obj_type_code;
3410 l_amt_type_tab(l_matrix_counter) := l_amt_type_code;
3411 l_amt_subtype_tab(l_matrix_counter) := l_amt_subtype_code;
3412 l_amt_type_id_tab(l_matrix_counter) := l_amt_type_id;
3413 l_amt_subtype_id_tab(l_matrix_counter) := l_amt_subtype_id;
3414 l_currency_code_tab(l_matrix_counter):= l_currency_code;
3415 l_currency_type_tab(l_matrix_counter):= l_currency_type;
3416 l_amount_tab1(l_matrix_counter) := l_pd_amt1;
3417 l_amount_tab2(l_matrix_counter) := l_pd_amt2;
3418 l_amount_tab3(l_matrix_counter) := l_pd_amt3;
3419 l_amount_tab4(l_matrix_counter) := l_pd_amt4;
3420 l_amount_tab5(l_matrix_counter) := l_pd_amt5;
3421 l_amount_tab6(l_matrix_counter) := l_pd_amt6;
3422 l_amount_tab7(l_matrix_counter) := l_pd_amt7;
3423 l_amount_tab8(l_matrix_counter) := l_pd_amt8;
3424 l_amount_tab9(l_matrix_counter) := l_pd_amt9;
3425 l_amount_tab10(l_matrix_counter) := l_pd_amt10;
3426 l_amount_tab11(l_matrix_counter) := l_pd_amt11;
3427 l_amount_tab12(l_matrix_counter) := l_pd_amt12;
3428 l_amount_tab13(l_matrix_counter) := l_pd_amt13;
3429 l_amount_tab14(l_matrix_counter) := l_pd_amt14;
3430 l_amount_tab15(l_matrix_counter) := l_pd_amt15;
3431 l_amount_tab16(l_matrix_counter) := l_pd_amt16;
3432 l_amount_tab17(l_matrix_counter) := l_pd_amt17;
3433 l_amount_tab18(l_matrix_counter) := l_pd_amt18;
3434 l_amount_tab19(l_matrix_counter) := l_pd_amt19;
3435 l_amount_tab20(l_matrix_counter) := l_pd_amt20;
3436 l_amount_tab21(l_matrix_counter) := l_pd_amt21;
3437 l_amount_tab22(l_matrix_counter) := l_pd_amt22;
3438 l_amount_tab23(l_matrix_counter) := l_pd_amt23;
3439 l_amount_tab24(l_matrix_counter) := l_pd_amt24;
3440 l_amount_tab25(l_matrix_counter) := l_pd_amt25;
3441 l_amount_tab26(l_matrix_counter) := l_pd_amt26;
3442 l_amount_tab27(l_matrix_counter) := l_pd_amt27;
3443 l_amount_tab28(l_matrix_counter) := l_pd_amt28;
3444 l_amount_tab29(l_matrix_counter) := l_pd_amt29;
3445 l_amount_tab30(l_matrix_counter) := l_pd_amt30;
3446 l_amount_tab31(l_matrix_counter) := l_pd_amt31;
3447 l_amount_tab32(l_matrix_counter) := l_pd_amt32;
3448 l_amount_tab33(l_matrix_counter) := l_pd_amt33;
3449 l_amount_tab34(l_matrix_counter) := l_pd_amt34;
3450 l_amount_tab35(l_matrix_counter) := l_pd_amt35;
3451 l_amount_tab36(l_matrix_counter) := l_pd_amt36;
3452 l_amount_tab37(l_matrix_counter) := l_pd_amt37;
3453 l_amount_tab38(l_matrix_counter) := l_pd_amt38;
3454 l_amount_tab39(l_matrix_counter) := l_pd_amt39;
3455 l_amount_tab40(l_matrix_counter) := l_pd_amt40;
3456 l_amount_tab41(l_matrix_counter) := l_pd_amt41;
3457 l_amount_tab42(l_matrix_counter) := l_pd_amt42;
3458 l_amount_tab43(l_matrix_counter) := l_pd_amt43;
3459 l_amount_tab44(l_matrix_counter) := l_pd_amt44;
3460 l_amount_tab45(l_matrix_counter) := l_pd_amt45;
3461 l_amount_tab46(l_matrix_counter) := l_pd_amt46;
3462 l_amount_tab47(l_matrix_counter) := l_pd_amt47;
3463 l_amount_tab48(l_matrix_counter) := l_pd_amt48;
3464 l_amount_tab49(l_matrix_counter) := l_pd_amt49;
3465 l_amount_tab50(l_matrix_counter) := l_pd_amt50;
3466 l_amount_tab51(l_matrix_counter) := l_pd_amt51;
3470 END IF;
3467 l_amount_tab52(l_matrix_counter) := l_pd_amt52;
3468 l_parent_assignment_id_tab(l_matrix_counter):= l_parent_assign_id_local;
3469 l_matrix_counter := l_matrix_counter + 1;
3471 /* end if for the sql row count = 0 */
3472
3473 ELSE
3474 /* for currency type other than transaction */
3475 BEGIN
3476 UPDATE Pa_Proj_Periods_Denorm SET
3477 preceding_periods_amount = l_prev_amt,
3478 succeeding_periods_amount = l_next_amt,
3479 prior_period_amount = l_prior_amt,
3480 period_amount1 = NVL(period_amount1,0) + l_pd_amt1,
3481 period_amount2 = NVL(period_amount2,0) + l_pd_amt2,
3482 period_amount3 = NVL(period_amount3,0) + l_pd_amt3,
3483 period_amount4 = NVL(period_amount4,0) + l_pd_amt4,
3484 period_amount5 = NVL(period_amount5,0) + l_pd_amt5,
3485 period_amount6 = NVL(period_amount6,0) + l_pd_amt6,
3486 period_amount7 = NVL(period_amount7,0) + l_pd_amt7,
3487 period_amount8 = NVL(period_amount8,0) + l_pd_amt8,
3488 period_amount9 = NVL(period_amount9,0) + l_pd_amt9,
3489 period_amount10 = NVL(period_amount10,0) + l_pd_amt10,
3490 period_amount11 = NVL(period_amount11,0) + l_pd_amt11,
3491 period_amount12 = NVL(period_amount12,0) + l_pd_amt12,
3492 period_amount13 = NVL(period_amount13,0) + l_pd_amt13,
3493 period_amount14 = NVL(period_amount14,0) + l_pd_amt14,
3494 period_amount15 = NVL(period_amount15,0) + l_pd_amt15,
3495 period_amount16 = NVL(period_amount16,0) + l_pd_amt16,
3496 period_amount17 = NVL(period_amount17,0) + l_pd_amt17,
3497 period_amount18 = NVL(period_amount18,0) + l_pd_amt18,
3498 period_amount19 = NVL(period_amount19,0) + l_pd_amt19,
3499 period_amount20 = NVL(period_amount20,0) + l_pd_amt20,
3500 period_amount21 = NVL(period_amount21,0) + l_pd_amt21,
3501 period_amount22 = NVL(period_amount22,0) + l_pd_amt22,
3502 period_amount23 = NVL(period_amount23,0) + l_pd_amt23,
3503 period_amount24 = NVL(period_amount24,0) + l_pd_amt24,
3504 period_amount25 = NVL(period_amount25,0) + l_pd_amt25,
3505 period_amount26 = NVL(period_amount26,0) + l_pd_amt26,
3506 period_amount27 = NVL(period_amount27,0) + l_pd_amt27,
3507 period_amount28 = NVL(period_amount28,0) + l_pd_amt28,
3508 period_amount29 = NVL(period_amount29,0) + l_pd_amt29,
3509 period_amount30 = NVL(period_amount30,0) + l_pd_amt30,
3510 period_amount31 = NVL(period_amount31,0) + l_pd_amt31,
3511 period_amount32 = NVL(period_amount32,0) + l_pd_amt32,
3512 period_amount33 = NVL(period_amount33,0) + l_pd_amt33,
3513 period_amount34 = NVL(period_amount34,0) + l_pd_amt34,
3514 period_amount35 = NVL(period_amount35,0) + l_pd_amt35,
3515 period_amount36 = NVL(period_amount36,0) + l_pd_amt36,
3516 period_amount37 = NVL(period_amount37,0) + l_pd_amt37,
3517 period_amount38 = NVL(period_amount38,0) + l_pd_amt38,
3518 period_amount39 = NVL(period_amount39,0) + l_pd_amt39,
3519 period_amount40 = NVL(period_amount40,0) + l_pd_amt40,
3520 period_amount41 = NVL(period_amount41,0) + l_pd_amt41,
3521 period_amount42 = NVL(period_amount42,0) + l_pd_amt42,
3522 period_amount43 = NVL(period_amount43,0) + l_pd_amt43,
3523 period_amount44 = NVL(period_amount44,0) + l_pd_amt44,
3524 period_amount45 = NVL(period_amount45,0) + l_pd_amt45,
3525 period_amount46 = NVL(period_amount46,0) + l_pd_amt46,
3526 period_amount47 = NVL(period_amount47,0) + l_pd_amt47,
3527 period_amount48 = NVL(period_amount48,0) + l_pd_amt48,
3528 period_amount49 = NVL(period_amount49,0) + l_pd_amt49,
3529 period_amount50 = NVL(period_amount50,0) + l_pd_amt50,
3530 period_amount51 = NVL(period_amount51,0) + l_pd_amt51,
3531 period_amount52 = NVL(period_amount52,0) + l_pd_amt52,
3532 parent_assignment_id = DECODE(l_parent_assign_id, NULL, l_parent_assignment_id, l_parent_assign_id),
3533 LAST_UPDATE_LOGIN = l_last_update_login,
3534 LAST_UPDATED_BY = l_last_updated_by,
3535 LAST_UPDATE_DATE = l_last_update_date
3536 WHERE
3537 Budget_Version_Id = p_budget_version_id AND
3538 project_id = p_project_id AND
3539 Resource_Assignment_Id = l_res_asg_id AND
3540 Object_Id = l_obj_id AND
3541 Object_Type_Code = l_obj_type_code AND
3542 Amount_Type_Code = l_amt_type_code AND
3543 Amount_Subtype_Code = l_amt_subtype_code AND
3544 Currency_Type = l_currency_type AND
3545 Currency_Code = l_currency_code;
3546 IF SQL%ROWCOUNT = 0 THEN
3547 INSERT INTO Pa_Proj_Periods_Denorm
3548 ( CREATION_DATE ,
3549 CREATED_BY ,
3550 LAST_UPDATE_LOGIN ,
3551 LAST_UPDATED_BY ,
3552 LAST_UPDATE_DATE ,
3553 Project_Id,
3554 Budget_Version_Id,
3555 Resource_Assignment_Id,
3556 Period_Profile_Id,
3557 Object_Id,
3558 Object_Type_Code,
3559 Currency_Type,
3560 Currency_Code,
3561 Amount_Type_Code,
3562 Amount_Subtype_Code,
3566 Period_Amount1 ,
3563 Preceding_Periods_Amount ,
3564 Succeeding_Periods_Amount ,
3565 Prior_Period_Amount,
3567 Period_Amount2 ,
3568 Period_Amount3 ,
3569 Period_Amount4 ,
3570 Period_Amount5 ,
3571 Period_Amount6 ,
3572 Period_Amount7 ,
3573 Period_Amount8 ,
3574 Period_Amount9 ,
3575 Period_Amount10 ,
3576 Period_Amount11 ,
3577 Period_Amount12 ,
3578 Period_Amount13 ,
3579 Period_Amount14 ,
3580 Period_Amount15 ,
3581 Period_Amount16 ,
3582 Period_Amount17 ,
3583 Period_Amount18 ,
3584 Period_Amount19 ,
3585 Period_Amount20 ,
3586 Period_Amount21 ,
3587 Period_Amount22 ,
3588 Period_Amount23 ,
3589 Period_Amount24 ,
3590 Period_Amount25 ,
3591 Period_Amount26 ,
3592 Period_Amount27 ,
3593 Period_Amount28 ,
3594 Period_Amount29 ,
3595 Period_Amount30 ,
3596 Period_Amount31 ,
3597 Period_Amount32 ,
3598 Period_Amount33 ,
3599 Period_Amount34 ,
3600 Period_Amount35 ,
3601 Period_Amount36 ,
3602 Period_Amount37 ,
3603 Period_Amount38 ,
3604 Period_Amount39 ,
3605 Period_Amount40 ,
3606 Period_Amount41 ,
3607 Period_Amount42 ,
3608 Period_Amount43 ,
3609 Period_Amount44 ,
3610 Period_Amount45 ,
3611 Period_Amount46 ,
3612 Period_Amount47 ,
3613 Period_Amount48 ,
3614 Period_Amount49 ,
3615 Period_Amount50 ,
3616 Period_Amount51 ,
3617 Period_Amount52 ,
3618 parent_assignment_id,
3619 Amount_Type_Id,
3620 Amount_SubType_Id )
3621 VALUES(
3622 l_creation_date ,
3623 l_created_by ,
3624 l_last_update_login ,
3625 l_last_updated_by ,
3626 l_last_update_date ,
3627 p_project_id,
3628 p_budget_version_id,
3629 l_res_asg_id,
3630 p_period_profile_id,
3631 l_obj_id,
3632 l_obj_type_code,
3633 l_currency_type,
3634 l_currency_code,
3635 l_amt_type_code,
3636 l_amt_subtype_code,
3637 l_prev_amt,
3638 l_next_amt,
3639 l_prior_amt,
3640 l_pd_amt1,
3641 l_pd_amt2,
3642 l_pd_amt3,
3643 l_pd_amt4,
3644 l_pd_amt5,
3645 l_pd_amt6,
3646 l_pd_amt7,
3647 l_pd_amt8,
3648 l_pd_amt9,
3649 l_pd_amt10,
3650 l_pd_amt11,
3651 l_pd_amt12,
3652 l_pd_amt13,
3653 l_pd_amt14,
3654 l_pd_amt15,
3655 l_pd_amt16,
3656 l_pd_amt17,
3657 l_pd_amt18,
3658 l_pd_amt19,
3659 l_pd_amt20,
3660 l_pd_amt21,
3661 l_pd_amt22,
3662 l_pd_amt23,
3663 l_pd_amt24,
3664 l_pd_amt25,
3665 l_pd_amt26,
3666 l_pd_amt27,
3667 l_pd_amt28,
3668 l_pd_amt29,
3669 l_pd_amt30,
3670 l_pd_amt31,
3671 l_pd_amt32,
3672 l_pd_amt33,
3673 l_pd_amt34,
3674 l_pd_amt35,
3675 l_pd_amt36,
3676 l_pd_amt37,
3677 l_pd_amt38,
3678 l_pd_amt39,
3679 l_pd_amt40,
3680 l_pd_amt41,
3681 l_pd_amt42,
3682 l_pd_amt43,
3683 l_pd_amt44,
3684 l_pd_amt45,
3685 l_pd_amt46,
3686 l_pd_amt47,
3687 l_pd_amt48,
3688 l_pd_amt49,
3689 l_pd_amt50,
3690 l_pd_amt51,
3691 l_pd_amt52,
3692 DECODE(l_parent_assign_id, NULL, l_parent_assignment_id, l_parent_assign_id),
3693 l_amt_type_id,
3694 l_amt_subtype_id
3695 );
3696 END IF;
3697 /* End if for the IF in zero row counts in update */
3698 EXCEPTION
3699 WHEN OTHERS THEN
3700 FND_MSG_PUB.add_exc_msg
3701 ( p_pkg_name => 'PA_PLAN_MATRIX.maintain_plan_matrix'
3702 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
3703 IF P_PA_DEBUG_MODE = 'Y' THEN
3707 END IF;
3704 PA_DEBUG.g_err_stage := 'Exception while trying to insert ' ||
3705 'data in proj denorm table';
3706 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3708 IF p_add_msg_in_stack = 'Y' THEN
3709 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
3710 p_msg_name => 'PA_FP_UNEX_ERR_DENORM_IN');
3711 END IF;
3712 x_return_status := FND_API.G_RET_STS_ERROR;
3713 x_msg_data := 'PA_FP_UNEX_ERR_DENORM_IN';
3714 PA_DEBUG.Reset_Curr_Function;
3715 RAISE;
3716 END;
3717 END IF;
3718 /* end if for the currency_type differentiator */
3719 END IF;
3720 /* End if for the quantity filter flag */
3721 END IF;
3722 /* end if for valid amount flag */
3723 /* PA_DEBUG.g_err_stage := 'moving to next amount type';
3724 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage); */
3725 END LOOP;
3726 /* end loop for amount type loop */
3727 END LOOP;
3728 /* end loop for main cursor */
3729
3730 IF P_PA_DEBUG_MODE = 'Y' THEN
3731 PA_DEBUG.g_err_stage := 'bef bulk insert into pds denorm';
3732 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3733 END IF;
3734
3735 FORALL l_ins_temp IN 1 .. l_amount_tab50.count
3736 INSERT INTO Pa_Proj_Periods_Denorm(
3737 CREATION_DATE ,
3738 CREATED_BY ,
3739 LAST_UPDATE_LOGIN ,
3740 LAST_UPDATED_BY ,
3741 LAST_UPDATE_DATE ,
3742 Project_Id,
3743 Budget_Version_Id,
3744 Resource_Assignment_Id,
3745 Period_Profile_Id,
3746 Object_Id,
3747 Object_Type_Code,
3748 Currency_Type,
3749 Currency_Code,
3750 Amount_Type_Code,
3751 Amount_Subtype_Code,
3752 Preceding_Periods_Amount ,
3753 Succeeding_Periods_Amount ,
3754 Prior_Period_Amount,
3755 Period_Amount1 ,
3756 Period_Amount2 ,
3757 Period_Amount3 ,
3758 Period_Amount4 ,
3759 Period_Amount5 ,
3760 Period_Amount6 ,
3761 Period_Amount7 ,
3762 Period_Amount8 ,
3763 Period_Amount9 ,
3764 Period_Amount10 ,
3765 Period_Amount11 ,
3766 Period_Amount12 ,
3767 Period_Amount13 ,
3768 Period_Amount14 ,
3769 Period_Amount15 ,
3770 Period_Amount16 ,
3771 Period_Amount17 ,
3772 Period_Amount18 ,
3773 Period_Amount19 ,
3774 Period_Amount20 ,
3775 Period_Amount21 ,
3776 Period_Amount22 ,
3777 Period_Amount23 ,
3778 Period_Amount24 ,
3779 Period_Amount25 ,
3780 Period_Amount26 ,
3781 Period_Amount27 ,
3782 Period_Amount28 ,
3783 Period_Amount29 ,
3784 Period_Amount30 ,
3785 Period_Amount31 ,
3786 Period_Amount32 ,
3787 Period_Amount33 ,
3788 Period_Amount34 ,
3789 Period_Amount35 ,
3790 Period_Amount36 ,
3791 Period_Amount37 ,
3792 Period_Amount38 ,
3793 Period_Amount39 ,
3794 Period_Amount40 ,
3795 Period_Amount41 ,
3796 Period_Amount42 ,
3797 Period_Amount43 ,
3798 Period_Amount44 ,
3799 Period_Amount45 ,
3800 Period_Amount46 ,
3801 Period_Amount47 ,
3802 Period_Amount48 ,
3803 Period_Amount49 ,
3804 Period_Amount50 ,
3805 Period_Amount51 ,
3806 Period_Amount52 ,
3807 Parent_Assignment_Id,
3808 Amount_Type_Id,
3809 Amount_SubType_Id )
3810 VALUES(
3811 l_creation_date ,
3812 l_created_by ,
3813 l_last_update_login ,
3814 l_last_updated_by ,
3815 l_last_update_date ,
3816 p_project_id,
3817 p_budget_version_id,
3818 l_res_asg_id_tab(l_ins_temp),
3819 p_period_profile_id,
3820 l_obj_id_tab(l_ins_temp),
3821 l_obj_type_code_tab(l_ins_temp),
3822 l_currency_type_tab(l_ins_temp),
3823 l_currency_code_tab(l_ins_temp),
3824 l_amt_type_tab(l_ins_temp),
3825 l_amt_subtype_tab(l_ins_temp),
3826 l_prev_amt_tab(l_ins_temp),
3827 l_next_amt_tab(l_ins_temp),
3828 l_prior_amt_tab(l_ins_temp),
3829 l_amount_tab1(l_ins_temp),
3830 l_amount_tab2(l_ins_temp),
3831 l_amount_tab3(l_ins_temp),
3832 l_amount_tab4(l_ins_temp),
3833 l_amount_tab5(l_ins_temp),
3834 l_amount_tab6(l_ins_temp),
3835 l_amount_tab7(l_ins_temp),
3836 l_amount_tab8(l_ins_temp),
3837 l_amount_tab9(l_ins_temp),
3838 l_amount_tab10(l_ins_temp),
3839 l_amount_tab11(l_ins_temp),
3840 l_amount_tab12(l_ins_temp),
3841 l_amount_tab13(l_ins_temp),
3842 l_amount_tab14(l_ins_temp),
3843 l_amount_tab15(l_ins_temp),
3844 l_amount_tab16(l_ins_temp),
3845 l_amount_tab17(l_ins_temp),
3846 l_amount_tab18(l_ins_temp),
3847 l_amount_tab19(l_ins_temp),
3848 l_amount_tab20(l_ins_temp),
3849 l_amount_tab21(l_ins_temp),
3850 l_amount_tab22(l_ins_temp),
3851 l_amount_tab23(l_ins_temp),
3852 l_amount_tab24(l_ins_temp),
3853 l_amount_tab25(l_ins_temp),
3854 l_amount_tab26(l_ins_temp),
3855 l_amount_tab27(l_ins_temp),
3856 l_amount_tab28(l_ins_temp),
3857 l_amount_tab29(l_ins_temp),
3858 l_amount_tab30(l_ins_temp),
3859 l_amount_tab31(l_ins_temp),
3860 l_amount_tab32(l_ins_temp),
3861 l_amount_tab33(l_ins_temp),
3862 l_amount_tab34(l_ins_temp),
3863 l_amount_tab35(l_ins_temp),
3864 l_amount_tab36(l_ins_temp),
3865 l_amount_tab37(l_ins_temp),
3866 l_amount_tab38(l_ins_temp),
3867 l_amount_tab39(l_ins_temp),
3868 l_amount_tab40(l_ins_temp),
3869 l_amount_tab41(l_ins_temp),
3870 l_amount_tab42(l_ins_temp),
3871 l_amount_tab43(l_ins_temp),
3872 l_amount_tab44(l_ins_temp),
3873 l_amount_tab45(l_ins_temp),
3874 l_amount_tab46(l_ins_temp),
3875 l_amount_tab47(l_ins_temp),
3876 l_amount_tab48(l_ins_temp),
3877 l_amount_tab49(l_ins_temp),
3878 l_amount_tab50(l_ins_temp),
3879 l_amount_tab51(l_ins_temp),
3880 l_amount_tab52(l_ins_temp),
3881 l_parent_assignment_id_tab(l_ins_temp),
3882 l_amt_type_id_tab(l_ins_temp),
3883 l_amt_subtype_id_tab(l_ins_temp)
3884 );
3885
3886 IF P_PA_DEBUG_MODE = 'Y' THEN
3887 PA_DEBUG.g_err_stage := 'after bulk insert into pds denorm and returning';
3888 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3889 END IF;
3890
3891 /* Commit interval is not decided yet. So, commiting at the end */
3892 IF NVL(P_COMMIT_FLAG,'N') = 'Y' THEN
3893 COMMIT;
3894 END IF;
3895 PA_DEBUG.Reset_Curr_Function;
3896 RETURN;
3897 EXCEPTION
3898 WHEN OTHERS THEN
3899 FND_MSG_PUB.add_exc_msg
3900 ( p_pkg_name => 'PA_PLAN_MATRIX.maintain_plan_matrix'
3901 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
3902 IF P_PA_DEBUG_MODE = 'Y' THEN
3903 PA_DEBUG.g_err_stage := 'Unexpected error in maintain plan matrix ' ||
3904 'PD-Txn data from budget lines table';
3905 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3906 END IF;
3907 IF p_add_msg_in_stack = 'Y' THEN
3908 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
3909 p_msg_name => 'PA_FP_UNEX_ERR_DENORM_IN');
3910 END IF;
3911 x_return_status := FND_API.G_RET_STS_ERROR;
3912 x_msg_data := 'PA_FP_UNEX_ERR_DENORM_IN';
3913 PA_DEBUG.Reset_Curr_Function;
3914 RAISE;
3915 END Maintain_Plan_Matrix;
3916
3917 END PA_PLAN_MATRIX;