[Home] [Help]
PACKAGE BODY: APPS.PA_FP_SPREAD_AMTS_PKG
Source
1 PACKAGE BODY PA_FP_SPREAD_AMTS_PKG AS
2 --$Header: PAFPSCPB.pls 120.6.12010000.2 2008/10/14 13:34:56 spaladug ship $
3
4 g_module_name VARCHAR2(100) := 'pa.plsql.PA_FP_SPREAD_AMTS_PKG';
5 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
6 G_rate_based_flag Varchar2(10) := NULL;
7 G_Curr_code Varchar2(80) := NULL;
8 G_User_Id Number:= fnd_global.user_id;
9 G_Login_Id Number:= fnd_global.login_id;
10 G_budget_line_source Varchar2(10) := 'SP'; --Indicates that budget lines are created through spread api
11
12 /* Declare variables for Bulk Processing of inserting budget lines */
13 -- for inserting new budget lines
14 g_bl_res_assignment_id_tab pa_plsql_datatypes.NumTabTyp;
15 g_bl_start_date_tab pa_plsql_datatypes.DateTabTyp;
16 g_bl_end_date_tab pa_plsql_datatypes.DateTabTyp;
17 g_bl_period_name_tab pa_plsql_datatypes.Char50TabTyp;
18 g_bl_txn_curr_code_tab pa_plsql_datatypes.Char50TabTyp;
19 g_bl_txn_curr_code_ovr_tab pa_plsql_datatypes.Char50TabTyp;
20 g_bl_budget_line_id_tab pa_plsql_datatypes.NumTabTyp;
21 g_bl_budget_version_id_tab pa_plsql_datatypes.NumTabTyp;
22 g_bl_proj_curr_code_tab pa_plsql_datatypes.Char50TabTyp;
23 g_bl_projfunc_curr_code_tab pa_plsql_datatypes.Char50TabTyp;
24
25
26 -- for inserting rollup tmp lines without budget lines
27 g_rl_res_assignment_id_tab pa_plsql_datatypes.NumTabTyp;
28 g_rl_start_date_tab pa_plsql_datatypes.DateTabTyp;
29 g_rl_end_date_tab pa_plsql_datatypes.DateTabTyp;
30 g_rl_period_name_tab pa_plsql_datatypes.Char50TabTyp;
31 g_rl_txn_curr_code_tab pa_plsql_datatypes.Char50TabTyp;
32 g_rl_txn_curr_code_ovr_tab pa_plsql_datatypes.Char50TabTyp;
33 g_rl_budget_line_id_tab pa_plsql_datatypes.NumTabTyp;
34 g_rl_budget_version_id_tab pa_plsql_datatypes.NumTabTyp;
35 g_rl_proj_curr_code_tab pa_plsql_datatypes.Char50TabTyp;
36 g_rl_projfunc_curr_code_tab pa_plsql_datatypes.Char50TabTyp;
37 g_rl_quantity_tab pa_plsql_datatypes.NumTabTyp;
38 g_rl_txn_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
39 g_rl_txn_cost_rate_tab pa_plsql_datatypes.NumTabTyp;
40 g_rl_txn_cost_rate_ovr_tab pa_plsql_datatypes.NumTabTyp;
41 g_rl_txn_burden_cost_tab pa_plsql_datatypes.NumTabTyp;
42 g_rl_txn_burden_rate_tab pa_plsql_datatypes.NumTabTyp;
43 g_rl_txn_burden_rate_ovr_tab pa_plsql_datatypes.NumTabTyp;
44 g_rl_txn_revenue_tab pa_plsql_datatypes.NumTabTyp;
45 g_rl_txn_bill_rate_tab pa_plsql_datatypes.NumTabTyp;
46 g_rl_txn_bill_rate_ovr_tab pa_plsql_datatypes.NumTabTyp;
47
48
49 -- for inserting rollup tmp lines with budgetlines
50 g_rbl_res_assignment_id_tab pa_plsql_datatypes.NumTabTyp;
51 g_rbl_start_date_tab pa_plsql_datatypes.DateTabTyp;
52 g_rbl_end_date_tab pa_plsql_datatypes.DateTabTyp;
53 g_rbl_period_name_tab pa_plsql_datatypes.Char50TabTyp;
54 g_rbl_txn_curr_code_tab pa_plsql_datatypes.Char50TabTyp;
55 g_rbl_txn_curr_code_ovr_tab pa_plsql_datatypes.Char50TabTyp;
56 g_rbl_budget_line_id_tab pa_plsql_datatypes.NumTabTyp;
57 g_rbl_budget_version_id_tab pa_plsql_datatypes.NumTabTyp;
58 g_rbl_proj_curr_code_tab pa_plsql_datatypes.Char50TabTyp;
59 g_rbl_projfunc_curr_code_tab pa_plsql_datatypes.Char50TabTyp;
60 g_rbl_quantity_tab pa_plsql_datatypes.NumTabTyp;
61 g_rbl_txn_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
62 g_rbl_txn_cost_rate_tab pa_plsql_datatypes.NumTabTyp;
63 g_rbl_txn_cost_rate_ovr_tab pa_plsql_datatypes.NumTabTyp;
64 g_rbl_txn_burden_cost_tab pa_plsql_datatypes.NumTabTyp;
65 g_rbl_txn_burden_rate_tab pa_plsql_datatypes.NumTabTyp;
66 g_rbl_txn_burden_rate_ovr_tab pa_plsql_datatypes.NumTabTyp;
67 g_rbl_txn_revenue_tab pa_plsql_datatypes.NumTabTyp;
68 g_rbl_txn_bill_rate_tab pa_plsql_datatypes.NumTabTyp;
69 g_rbl_txn_bill_rate_ovr_tab pa_plsql_datatypes.NumTabTyp;
70
71
72 -- for bulk update of rounding diff lines
73 g_edist_rndiff_quantity pa_plsql_datatypes.NumTabTyp;
74 g_edist_blId pa_plsql_datatypes.NumTabTyp;
75 g_edist_RaId pa_plsql_datatypes.NumTabTyp;
76 g_edist_txn_quantity_addl pa_plsql_datatypes.NumTabTyp;
77 g_edist_txn_plan_quantity pa_plsql_datatypes.NumTabTyp;
78 g_edist_Curcode pa_plsql_datatypes.Char50TabTyp;
79 g_edist_Curcode_ovr pa_plsql_datatypes.Char50TabTyp;
80 g_edist_sdate pa_plsql_datatypes.DateTabTyp;
81 g_edist_edate pa_plsql_datatypes.DateTabTyp;
82 g_edist_etc_sdate pa_plsql_datatypes.DateTabTyp;
83 g_edist_line_start_date pa_plsql_datatypes.DateTabTyp;
84 g_edist_source_context pa_plsql_datatypes.Char100TabTyp;
85
86
87
88
89 TYPE spread_record_type IS RECORD
90 (start_date DATE,
91 end_date DATE,
92 period_name gl_periods.period_name%TYPE, --VARCHAR2,
93 actual_days INTEGER,
94 actual_periods NUMBER,
95 allocation NUMBER,
96 percentage NUMBER,
97 number_of_amounts INTEGER,
98 amount1 NUMBER,
99 amount2 NUMBER,
100 amount3 NUMBER,
101 amount4 NUMBER,
102 amount5 NUMBER,
103 amount6 NUMBER,
104 amount7 NUMBER,
105 amount8 NUMBER,
106 amount9 NUMBER,
107 amount10 NUMBER
108 );
109
110
111 TYPE spread_table_type IS TABLE OF spread_record_type;
112
113 TYPE start_end_date_record_type IS RECORD
114 (start_date DATE,
115 end_date DATE);
116
117 TYPE start_end_date_table_type IS TABLE OF start_end_date_record_type;
118
119 TYPE spread_curve_type IS TABLE OF NUMBER;
120
121 TYPE resource_assignment_rec_type IS RECORD (
122 RESOURCE_ASSIGNMENT_ID PA_FP_RES_ASSIGNMENTS_TMP.RESOURCE_ASSIGNMENT_ID%TYPE
123 ,BUDGET_VERSION_ID PA_FP_RES_ASSIGNMENTS_TMP.BUDGET_VERSION_ID%TYPE
124 ,PROJECT_ID PA_FP_RES_ASSIGNMENTS_TMP.PROJECT_ID%TYPE
125 ,TASK_ID PA_FP_RES_ASSIGNMENTS_TMP.TASK_ID%TYPE
126 ,RESOURCE_LIST_MEMBER_ID PA_FP_RES_ASSIGNMENTS_TMP.RESOURCE_LIST_MEMBER_ID%TYPE
127 ,PLANNING_START_DATE PA_FP_RES_ASSIGNMENTS_TMP.PLANNING_START_DATE%TYPE
128 ,PLANNING_END_DATE PA_FP_RES_ASSIGNMENTS_TMP.PLANNING_END_DATE%TYPE
129 ,SPREAD_CURVE_ID PA_FP_RES_ASSIGNMENTS_TMP.SPREAD_CURVE_ID%TYPE
130 ,SP_FIXED_DATE PA_FP_RES_ASSIGNMENTS_TMP.SP_FIXED_DATE%TYPE
131 ,TXN_CURRENCY_CODE PA_FP_RES_ASSIGNMENTS_TMP.TXN_CURRENCY_CODE%TYPE
132 ,TXN_CURRENCY_CODE_OVERRIDE PA_FP_RES_ASSIGNMENTS_TMP.TXN_CURRENCY_CODE%TYPE
133 ,PROJECT_CURRENCY_CODE PA_FP_RES_ASSIGNMENTS_TMP.TXN_CURRENCY_CODE%TYPE
134 ,PROJFUNC_CURRENCY_CODE PA_FP_RES_ASSIGNMENTS_TMP.TXN_CURRENCY_CODE%TYPE
135 ,TXN_REVENUE PA_FP_RES_ASSIGNMENTS_TMP.TXN_REVENUE%TYPE
136 ,TXN_REVENUE_ADDL PA_FP_RES_ASSIGNMENTS_TMP.TXN_REVENUE_ADDL%TYPE
137 ,TXN_RAW_COST PA_FP_RES_ASSIGNMENTS_TMP.TXN_RAW_COST%TYPE
138 ,TXN_RAW_COST_ADDL PA_FP_RES_ASSIGNMENTS_TMP.TXN_RAW_COST_ADDL%TYPE
139 ,TXN_BURDENED_COST PA_FP_RES_ASSIGNMENTS_TMP.TXN_BURDENED_COST%TYPE
140 ,TXN_BURDENED_COST_ADDL PA_FP_RES_ASSIGNMENTS_TMP.TXN_BURDENED_COST_ADDL%TYPE
141 ,TXN_PLAN_QUANTITY PA_FP_RES_ASSIGNMENTS_TMP.TXN_PLAN_QUANTITY%TYPE
142 ,TXN_PLAN_QUANTITY_ADDL PA_FP_RES_ASSIGNMENTS_TMP.TXN_PLAN_QUANTITY_ADDL%TYPE
143 ,LINE_START_DATE PA_FP_RES_ASSIGNMENTS_TMP.LINE_START_DATE%TYPE
144 ,LINE_END_DATE PA_FP_RES_ASSIGNMENTS_TMP.LINE_END_DATE%TYPE
145 ,SOURCE_CONTEXT PA_FP_RES_ASSIGNMENTS_TMP.SOURCE_CONTEXT%TYPE
146 ,RAW_COST_RATE PA_FP_RES_ASSIGNMENTS_TMP.RAW_COST_RATE%TYPE
147 ,RAW_COST_RATE_OVERRIDE PA_FP_RES_ASSIGNMENTS_TMP.RW_COST_RATE_OVERRIDE%TYPE
148 ,BURDEN_COST_RATE PA_FP_RES_ASSIGNMENTS_TMP.BURDEN_COST_RATE%TYPE
149 ,BURDEN_COST_RATE_OVERRIDE PA_FP_RES_ASSIGNMENTS_TMP.BURDEN_COST_RATE_OVERRIDE%TYPE
150 ,BILL_RATE PA_FP_RES_ASSIGNMENTS_TMP.BILL_RATE%TYPE
151 ,BILL_RATE_OVERRIDE PA_FP_RES_ASSIGNMENTS_TMP.BILL_RATE_OVERRIDE%TYPE
152 ,RATE_BASED_FLAG PA_FP_RES_ASSIGNMENTS_TMP.RATE_BASED_FLAG%TYPE
153 ,SPREAD_AMOUNTS_FLAG PA_FP_RES_ASSIGNMENTS_TMP.SPREAD_AMOUNTS_FLAG%TYPE
154 ,INIT_QUANTITY NUMBER
155 ,TXN_INIT_RAW_COST NUMBER
156 ,TXN_INIT_BURDENED_COST NUMBER
157 ,TXN_INIT_REVENUE NUMBER
158 );
159 /**
160 procedure calc_log(p_msg varchar2) IS
161
162 pragma autonomous_transaction ;
163 BEGIN
164 --IF P_PA_DEBUG_MODE = 'Y' Then
165 NULL;
166 INSERT INTO PA_FP_CALCULATE_LOG
167 (SESSIONID
168 ,SEQ_NUMBER
169 ,LOG_MESSAGE)
170 VALUES
171 (userenv('sessionid')
172 ,HR.PAY_US_GARN_FEE_RULES_S.nextval
173 ,substr(P_MSG,1,240)
174 );
175 --END IF;
176 COMMIT;
177
178 end calc_log;
179 **/
180 procedure PRINT_MSG(P_MSG VARCHAR2) is
181
182 BEGIN
183 --calc_log(P_MSG);
184 IF P_PA_DEBUG_MODE = 'Y' Then
185 pa_debug.g_err_stage := substr('LOG:'||p_msg,1,240);
186 PA_DEBUG.write
187 (x_Module => g_module_name
188 ,x_Msg => pa_debug.g_err_stage
189 ,x_Log_Level => 3);
190 null;
191 END IF;
192 END PRINT_MSG;
193
194 PROCEDURE Process_Rounding_Diff(p_budget_version_id IN NUMBER
195 ,x_return_status OUT NOCOPY VARCHAR2
196 ) IS
197 v_total_quantity NUMBER := 0;
198 v_bl_total_quantity NUMBER := 0;
199 l_cntr NUMBER := 0;
200 l_stage Varchar2(1000);
201
202 BEGIN
203 x_return_status := 'S';
204 l_stage := 'Entered Process_Rounding_Diff API';
205 print_msg(l_stage);
206 IF g_edist_blId.COUNT > 0 THEN
207 l_cntr := 0;
208 FOR i IN g_edist_blId.FIRST .. g_edist_blId.LAST LOOP
209 l_cntr := l_cntr + 1;
210 g_edist_rndiff_quantity(l_cntr) := 0;
211 v_total_quantity := 0;
212 v_bl_total_quantity := 0;
213
214 Begin
215 l_stage := 'Get sum of quantity from rollup tmp';
216 --print_msg(l_stage);
217 SELECT sum(nvl(bl.quantity,0))
218 INTO v_total_quantity
219 FROM pa_fp_rollup_tmp bl
220 WHERE bl.RESOURCE_ASSIGNMENT_ID = g_edist_RaId(i)
221 AND bl.TXN_CURRENCY_CODE = NVL(g_edist_Curcode_ovr(i),g_edist_Curcode(i))
222 AND bl.START_DATE BETWEEN g_edist_sdate(i) AND g_edist_edate(i)
223 AND bl.END_DATE BETWEEN g_edist_sdate(i) AND g_edist_edate(i)
224 AND bl.PERIOD_NAME IS NOT NULL;
225 Exception
226 when no_data_found then
227 v_total_quantity := 0;
228 End;
229
230 If g_edist_etc_sdate(i) is NOT NULL Then
231 /* Bug fix: 3844739 getting the totals from budgetlines prior to ETC start date to get the sum of total
232 * this is required as the total ra Plan quantity is always includes the lines prior to ETC start date
233 */
234 Begin
235 l_stage := 'Get sum of quantity from from bl prior to etc start date';
236 --print_msg(l_stage);
237 SELECT sum(nvl(bl.quantity,0))
238 INTO v_bl_total_quantity
239 FROM pa_budget_lines bl
240 ,pa_fp_res_assignments_tmp rtmp
241 WHERE bl.budget_version_id = p_budget_version_id
242 AND bl.RESOURCE_ASSIGNMENT_ID = g_edist_RaId(i)
243 AND bl.TXN_CURRENCY_CODE = g_edist_Curcode(i)
244 AND bl.budget_version_id = rtmp.budget_version_id
245 AND rtmp.resource_assignment_id = bl.resource_assignment_id
246 AND rtmp.txn_currency_code = bl.txn_currency_code
247 AND ((rtmp.SOURCE_CONTEXT = 'BUDGET_LINE'
248 AND rtmp.LINE_START_DATE = g_edist_line_start_date(i))
249 OR
250 rtmp.SOURCE_CONTEXT <> 'BUDGET_LINE'
251 )
252 AND bl.START_DATE BETWEEN decode(rtmp.SOURCE_CONTEXT,'BUDGET_LINE',rtmp.LINE_START_DATE
253 ,decode(sign(bl.START_DATE - rtmp.planning_start_date),-1,bl.START_DATE,rtmp.planning_start_date))
254 AND decode(rtmp.SOURCE_CONTEXT,'BUDGET_LINE',rtmp.LINE_END_DATE,rtmp.planning_end_date)
255 AND bl.END_DATE BETWEEN decode(rtmp.SOURCE_CONTEXT,'BUDGET_LINE',rtmp.LINE_START_DATE,rtmp.planning_start_date )
256 AND decode(rtmp.SOURCE_CONTEXT,'BUDGET_LINE',rtmp.LINE_END_DATE
257 ,decode(sign(bl.END_DATE - rtmp.planning_end_date),1,bl.END_DATE,rtmp.planning_end_date))
258 AND bl.END_DATE < g_edist_etc_sdate(i)
259 AND bl.PERIOD_NAME IS NOT NULL;
260 Exception
261 when no_data_found then
262 v_bl_total_quantity := 0;
263 End;
264 End If;
265 g_edist_rndiff_quantity(l_cntr) := nvl(g_edist_txn_plan_quantity(i),0) - (nvl(v_total_quantity,0)+ nvl(v_bl_total_quantity,0));
266 --print_msg('Last Bl with diffamt rndiff_quantity['||g_edist_rndiff_quantity(l_cntr)||']');
267 END LOOP;
268 END IF;
269
270 /* bulk update the rollup tmp with spread rounding diff amount */
271 IF g_edist_blId.COUNT > 0 THEN
272 l_stage := 'Finally one bulk update of rollup tmp lines ';
273 print_msg(l_stage);
274 FORALL i IN g_edist_blId.FIRST .. g_edist_blId.LAST
275 UPDATE PA_FP_ROLLUP_TMP tmp
276 SET tmp.QUANTITY = decode(NVL(g_edist_txn_quantity_addl(i),0),0,tmp.QUANTITY,(nvl(tmp.QUANTITY,0)+g_edist_rndiff_quantity(i)))
277 WHERE tmp.budget_version_id = p_budget_version_id
278 AND tmp.BUDGET_LINE_ID = g_edist_blId(i);
279 END IF;
280
281 EXCEPTION
282
283 WHEN OTHERS THEN
284 print_msg('Unexpected error in Process_Rounding_Diff ['||sqlcode||sqlerrm||']');
285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
286 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_FP_SPREAD_AMTS_PKG',
287 p_procedure_name => 'Process_Rounding_Diff'||l_stage);
288 If p_pa_debug_mode = 'Y' Then
289 pa_debug.reset_err_stack;
290 End If;
291 raise FND_API.G_EXC_UNEXPECTED_ERROR;
292
293 END Process_Rounding_Diff;
294
295 PROCEDURE Initialize_spread_plsqlTabs IS
296
297 BEGIN
298
299 g_bl_res_assignment_id_tab.delete;
300 g_bl_start_date_tab.delete;
301 g_bl_end_date_tab.delete;
302 g_bl_period_name_tab.delete;
303 g_bl_txn_curr_code_tab.delete;
304 g_bl_txn_curr_code_ovr_tab.delete;
305 g_bl_budget_line_id_tab.delete;
306 g_bl_budget_version_id_tab.delete;
307 g_bl_proj_curr_code_tab.delete;
308 g_bl_projfunc_curr_code_tab.delete;
309
310 -- for inserting rollup tmp lines without budget lines
311 g_rl_res_assignment_id_tab.delete;
312 g_rl_start_date_tab.delete;
313 g_rl_end_date_tab.delete;
314 g_rl_period_name_tab.delete;
315 g_rl_txn_curr_code_tab.delete;
316 g_rl_txn_curr_code_ovr_tab.delete;
317 g_rl_budget_line_id_tab.delete;
318 g_rl_budget_version_id_tab.delete;
319 g_rl_proj_curr_code_tab.delete;
320 g_rl_projfunc_curr_code_tab.delete;
321 g_rl_quantity_tab.delete;
322 g_rl_txn_raw_cost_tab.delete;
323 g_rl_txn_cost_rate_tab.delete;
324 g_rl_txn_cost_rate_ovr_tab.delete;
325 g_rl_txn_burden_cost_tab.delete;
326 g_rl_txn_burden_rate_tab.delete;
327 g_rl_txn_burden_rate_ovr_tab.delete;
328 g_rl_txn_revenue_tab.delete;
329 g_rl_txn_bill_rate_tab.delete;
330 g_rl_txn_bill_rate_ovr_tab.delete;
331
332 -- for inserting rollup tmp lines with budgetlines
333 g_rbl_res_assignment_id_tab.delete;
334 g_rbl_start_date_tab.delete;
335 g_rbl_end_date_tab.delete;
336 g_rbl_period_name_tab.delete;
337 g_rbl_txn_curr_code_tab.delete;
338 g_rbl_txn_curr_code_ovr_tab.delete;
339 g_rbl_budget_line_id_tab.delete;
340 g_rbl_budget_version_id_tab.delete;
341 g_rbl_proj_curr_code_tab.delete;
342 g_rbl_projfunc_curr_code_tab.delete;
343 g_rbl_quantity_tab.delete;
344 g_rbl_txn_raw_cost_tab.delete;
345 g_rbl_txn_cost_rate_tab.delete;
346 g_rbl_txn_cost_rate_ovr_tab.delete;
347 g_rbl_txn_burden_cost_tab.delete;
348 g_rbl_txn_burden_rate_tab.delete;
349 g_rbl_txn_burden_rate_ovr_tab.delete;
350 g_rbl_txn_revenue_tab.delete;
351 g_rbl_txn_bill_rate_tab.delete;
352 g_rbl_txn_bill_rate_ovr_tab.delete;
353
354 -- for bulk update of rollup tmp lines with rounding diff amounts
355 g_edist_rndiff_quantity.delete;
356 g_edist_blId.delete;
357 g_edist_RaId.delete;
358 g_edist_txn_quantity_addl.delete;
359 g_edist_txn_plan_quantity.delete;
360 g_edist_Curcode.delete;
361 g_edist_Curcode_ovr.delete;
362 g_edist_sdate.delete;
363 g_edist_edate.delete;
364 g_edist_etc_sdate.delete;
365 g_edist_line_start_date.delete;
366 g_edist_source_context.delete;
367
368 END Initialize_spread_plsqlTabs;
369
370 /* This API bulk inserts the budget lines from plsql tables*/
371 PROCEDURE blkInsertBudgetLines(x_return_status OUT NOCOPY Varchar2) IS
372
373 l_stage varchar2(1000);
374
375 BEGIN
376 x_return_status := 'S';
377
378 IF g_bl_res_assignment_id_tab.COUNT > 0 THEN
379 l_stage := 'Bulk Insert of Budget Lines';
380 FORALL i IN g_bl_res_assignment_id_tab.FIRST .. g_bl_res_assignment_id_tab.LAST
381 INSERT INTO PA_BUDGET_LINES
382 (
383 BUDGET_LINE_ID
384 ,BUDGET_VERSION_ID
385 ,RESOURCE_ASSIGNMENT_ID
386 ,START_DATE
387 ,END_DATE
388 ,PERIOD_NAME
389 ,TXN_CURRENCY_CODE
390 ,PROJECT_CURRENCY_CODE
391 ,PROJFUNC_CURRENCY_CODE
392 ,CREATED_BY
393 ,CREATION_DATE
394 ,LAST_UPDATED_BY
395 ,LAST_UPDATE_DATE
396 ,LAST_UPDATE_LOGIN
397 ,QUANTITY_SOURCE
398 ,RAW_COST_SOURCE
399 ,BURDENED_COST_SOURCE
400 ,REVENUE_SOURCE
401 )
402 VALUES (
403 g_bl_budget_line_id_tab(i)
404 ,g_bl_budget_version_id_tab(i)
405 ,g_bl_res_assignment_id_tab(i)
406 ,g_bl_start_date_tab(i)
407 ,g_bl_end_date_tab(i)
408 ,g_bl_period_name_tab(i)
409 ,NVL(g_bl_txn_curr_code_ovr_tab(i),g_bl_txn_curr_code_tab(i))
410 ,g_bl_proj_curr_code_tab(i)
411 ,g_bl_projfunc_curr_code_tab(i)
412 ,g_user_id
413 ,SYSDATE
414 ,g_user_id
415 ,SYSDATE
416 ,g_login_id
417 ,G_BUDGET_LINE_SOURCE
418 ,G_BUDGET_LINE_SOURCE
419 ,G_BUDGET_LINE_SOURCE
420 ,G_BUDGET_LINE_SOURCE
421 );
422 END IF;
423 EXCEPTION
424
425 WHEN OTHERS THEN
426 print_msg('Unexpected error in blkInsertBudgetLines['||sqlcode||sqlerrm||']');
427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
428 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_FP_SPREAD_AMTS_PKG',
429 p_procedure_name => 'blkInsertBudgetLines'||l_stage);
430 If p_pa_debug_mode = 'Y' Then
431 pa_debug.reset_err_stack;
432 End If;
433 raise FND_API.G_EXC_UNEXPECTED_ERROR;
434 END blkInsertBudgetLines;
435
436 /* This API bulk inserts the lines into pa_fp_rollup_tmp from plsql tables */
437 PROCEDURE blkInsertFpLines(x_return_status OUT NOCOPY Varchar2) IS
438
439 l_stage varchar2(1000);
440 BEGIN
441 x_return_status := 'S';
442 IF g_rl_res_assignment_id_tab.COUNT > 0 THEN
443 l_stage := 'Bulk Insert of Fp rollup Tmp lines';
444 FORALL i IN g_rl_res_assignment_id_tab.FIRST .. g_rl_res_assignment_id_tab.LAST
445 INSERT INTO PA_FP_ROLLUP_TMP
446 (
447 BUDGET_LINE_ID
448 ,BUDGET_VERSION_ID
449 ,RESOURCE_ASSIGNMENT_ID
450 ,START_DATE
451 ,END_DATE
452 ,PERIOD_NAME
453 ,TXN_CURRENCY_CODE
454 ,PROJECT_CURRENCY_CODE
455 ,PROJFUNC_CURRENCY_CODE
456 ,QUANTITY
457 ,TXN_RAW_COST
458 ,COST_RATE
459 ,RW_COST_RATE_OVERRIDE
460 ,TXN_BURDENED_COST
461 ,BURDEN_COST_RATE
462 ,BURDEN_COST_RATE_OVERRIDE
463 ,TXN_REVENUE
464 ,BILL_RATE
465 ,BILL_RATE_OVERRIDE
466 ,QUANTITY_SOURCE
467 ,RAW_COST_SOURCE
468 ,BURDENED_COST_SOURCE
469 ,REVENUE_SOURCE
470 )
471 VALUES (
472 g_rl_budget_line_id_tab(i)
473 ,g_rl_budget_version_id_tab(i)
474 ,g_rl_res_assignment_id_tab(i)
475 ,g_rl_start_date_tab(i)
476 ,g_rl_end_date_tab(i)
477 ,g_rl_period_name_tab(i)
478 ,NVL(g_rl_txn_curr_code_ovr_tab(i),g_rl_txn_curr_code_tab(i))
479 ,g_rl_proj_curr_code_tab(i)
480 ,g_rl_projfunc_curr_code_tab(i)
481 ,decode(g_rl_quantity_tab(i),0,NULL,g_rl_quantity_tab(i))
482 ,decode(g_rl_txn_raw_cost_tab(i),0,NULL,g_rl_txn_raw_cost_tab(i))
483 ,decode(g_rl_txn_cost_rate_tab(i),0,NULL,g_rl_txn_cost_rate_tab(i))
484 /* bug fix:4693839 : removed decode to have 0,NULL for override rates */
485 --,decode(g_rl_txn_cost_rate_ovr_tab(i),0,NULL,g_rl_txn_cost_rate_ovr_tab(i))
486 ,g_rl_txn_cost_rate_ovr_tab(i)
487 ,decode(g_rl_txn_burden_cost_tab(i),0,NULL,g_rl_txn_burden_cost_tab(i))
488 ,decode(g_rl_txn_burden_rate_tab(i),0,NULL,g_rl_txn_burden_rate_tab(i))
489 --,decode(g_rl_txn_burden_rate_ovr_tab(i),0,NULL,g_rl_txn_burden_rate_ovr_tab(i))
490 ,g_rl_txn_burden_rate_ovr_tab(i)
491 ,decode(g_rl_txn_revenue_tab(i),0,NULL,g_rl_txn_revenue_tab(i))
492 ,decode(g_rl_txn_bill_rate_tab(i),0,NULL,g_rl_txn_bill_rate_tab(i))
493 --,decode(g_rl_txn_bill_rate_ovr_tab(i),0,NULL,g_rl_txn_bill_rate_ovr_tab(i))
494 ,g_rl_txn_bill_rate_ovr_tab(i)
495 ,G_BUDGET_LINE_SOURCE
496 ,G_BUDGET_LINE_SOURCE
497 ,G_BUDGET_LINE_SOURCE
498 ,G_BUDGET_LINE_SOURCE
499 );
500 END IF;
501
502 EXCEPTION
503
504 WHEN OTHERS THEN
505 print_msg('Unexpected error in blkInsertFpLines['||sqlcode||sqlerrm||']');
506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
507 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_FP_SPREAD_AMTS_PKG',
508 p_procedure_name => 'blkInsertFpLines'||l_stage);
509 If p_pa_debug_mode = 'Y' Then
510 pa_debug.reset_err_stack;
511 End If;
512 raise FND_API.G_EXC_UNEXPECTED_ERROR;
513
514 END blkInsertFpLines;
515
516 /* This API bulk inserts lines into pa_fp_rollup_tmp from pa_budget_lines */
517 PROCEDURE blkInsertBlFpLines(x_return_status OUT NOCOPY Varchar2) IS
518
519 l_stage varchar2(1000);
520 BEGIN
521 x_return_status := 'S';
522 IF g_rbl_budget_line_id_tab.COUNT > 0 THEN
523 l_stage := 'Bulk Insert of rollup Tmp with Budget Lines';
524 FORALL i IN g_rbl_budget_line_id_tab.FIRST .. g_rbl_budget_line_id_tab.LAST
525
526 /* Bug Fix 4332086
527 Whenever currency is overridden along with a change in quantity in the workplan flow
528 in Update Task Details page, the following piece of code gets executed.
529
530 This code caches several attributes from pa_budget_lines table and will use them in the
531 later part of the flow, thus causing the above bug. When ever currency code is overwritten
532 we need to use the new currency's conversion attributes, but where as this code will use
533 old currency's conversion attributes.
534
535 As a fix the following insert is commented out and a new insert is written with a change
536 in the select statement of values clause.
537
538 INSERT INTO PA_FP_ROLLUP_TMP
539 (
540 BUDGET_LINE_ID
541 ,BUDGET_VERSION_ID
542 ,RESOURCE_ASSIGNMENT_ID
543 ,START_DATE
544 ,END_DATE
545 ,PERIOD_NAME
546 ,TXN_CURRENCY_CODE
547 ,PROJECT_CURRENCY_CODE
548 ,PROJFUNC_CURRENCY_CODE
549 ,QUANTITY
550 ,TXN_RAW_COST
551 ,COST_RATE
552 ,RW_COST_RATE_OVERRIDE
553 ,TXN_BURDENED_COST
554 ,BURDEN_COST_RATE
555 ,BURDEN_COST_RATE_OVERRIDE
556 ,TXN_REVENUE
557 ,BILL_RATE
558 ,BILL_RATE_OVERRIDE
559 ,PROJFUNC_RAW_COST
560 ,PROJFUNC_BURDENED_COST
561 ,PROJFUNC_REVENUE
562 ,COST_REJECTION_CODE
563 ,REVENUE_REJECTION_CODE
564 ,BURDEN_REJECTION_CODE
565 ,PROJFUNC_COST_RATE_TYPE
566 ,PROJFUNC_COST_EXCHANGE_RATE
567 ,PROJFUNC_COST_RATE_DATE_TYPE
568 ,PROJFUNC_COST_RATE_DATE
569 ,PROJFUNC_REV_RATE_TYPE
570 ,PROJFUNC_REV_EXCHANGE_RATE
571 ,PROJFUNC_REV_RATE_DATE_TYPE
572 ,PROJFUNC_REV_RATE_DATE
573 ,PROJECT_COST_RATE_TYPE
574 ,PROJECT_COST_EXCHANGE_RATE
575 ,PROJECT_COST_RATE_DATE_TYPE
576 ,PROJECT_COST_RATE_DATE
577 ,PROJECT_RAW_COST
578 ,PROJECT_BURDENED_COST
579 ,PROJECT_REV_RATE_TYPE
580 ,PROJECT_REV_EXCHANGE_RATE
581 ,PROJECT_REV_RATE_DATE_TYPE
582 ,PROJECT_REV_RATE_DATE
583 ,PROJECT_REVENUE
584 ,INIT_QUANTITY
585 ,TXN_INIT_RAW_COST
586 ,TXN_INIT_BURDENED_COST
587 ,TXN_INIT_REVENUE
588 ,BILL_MARKUP_PERCENTAGE
589 ,COST_IND_COMPILED_SET_ID
590 ,QUANTITY_SOURCE
591 ,RAW_COST_SOURCE
592 ,BURDENED_COST_SOURCE
593 ,REVENUE_SOURCE
594 ,INIT_RAW_COST
595 ,INIT_BURDENED_COST
596 ,INIT_REVENUE
597 ,PROJECT_INIT_RAW_COST
598 ,PROJECT_INIT_BURDENED_COST
599 ,PROJECT_INIT_REVENUE
600 )
601 SELECT
602 g_rbl_budget_line_id_tab(i)
603 ,g_rbl_budget_version_id_tab(i)
604 ,g_rbl_res_assignment_id_tab(i)
605 ,g_rbl_start_date_tab(i)
606 ,g_rbl_end_date_tab(i)
607 ,g_rbl_period_name_tab(i)
608 ,NVL(g_rbl_txn_curr_code_ovr_tab(i),g_rbl_txn_curr_code_tab(i))
609 ,g_rbl_proj_curr_code_tab(i)
610 ,g_rbl_projfunc_curr_code_tab(i)
611 ,decode(g_rbl_quantity_tab(i),0,NULL,g_rbl_quantity_tab(i))
612 ,decode(g_rbl_txn_raw_cost_tab(i),0,NULL,g_rbl_txn_raw_cost_tab(i))
613 ,decode(g_rbl_txn_cost_rate_tab(i),0,NULL,g_rbl_txn_cost_rate_tab(i))
614 ,decode(g_rbl_txn_curr_code_ovr_tab(i),NULL,NVL(g_rbl_txn_cost_rate_ovr_tab(i),bl.txn_cost_rate_override)
615 ,g_rbl_txn_cost_rate_ovr_tab(i))
616 ,decode(g_rbl_txn_burden_cost_tab(i),0,NULL,g_rbl_txn_burden_cost_tab(i))
617 ,decode(g_rbl_txn_burden_rate_tab(i),0,NULL,g_rbl_txn_burden_rate_tab(i))
618 ,decode(g_rbl_txn_curr_code_ovr_tab(i),NULL,NVL(g_rbl_txn_burden_rate_ovr_tab(i),bl.burden_cost_rate_override)
619 ,g_rbl_txn_burden_rate_ovr_tab(i))
620 ,decode(g_rbl_txn_revenue_tab(i),0,NULL,g_rbl_txn_revenue_tab(i))
621 ,decode(g_rbl_txn_bill_rate_tab(i),0,NULL,g_rbl_txn_bill_rate_tab(i))
622 ,decode(g_rbl_txn_curr_code_ovr_tab(i),NULL,NVL(g_rbl_txn_bill_rate_ovr_tab(i),bl.txn_bill_rate_override)
623 ,g_rbl_txn_bill_rate_ovr_tab(i))
624 ,bl.RAW_COST
625 ,bl.BURDENED_COST
626 ,bl.REVENUE
627 ,bl.COST_REJECTION_CODE
628 ,bl.REVENUE_REJECTION_CODE
629 ,bl.BURDEN_REJECTION_CODE
630 ,bl.PROJFUNC_COST_RATE_TYPE
631 ,bl.PROJFUNC_COST_EXCHANGE_RATE
632 ,bl.PROJFUNC_COST_RATE_DATE_TYPE
633 ,bl.PROJFUNC_COST_RATE_DATE
634 ,bl.PROJFUNC_REV_RATE_TYPE
635 ,bl.PROJFUNC_REV_EXCHANGE_RATE
636 ,bl.PROJFUNC_REV_RATE_DATE_TYPE
637 ,bl.PROJFUNC_REV_RATE_DATE
638 ,bl.PROJECT_COST_RATE_TYPE
639 ,bl.PROJECT_COST_EXCHANGE_RATE
640 ,bl.PROJECT_COST_RATE_DATE_TYPE
641 ,bl.PROJECT_COST_RATE_DATE
642 ,bl.PROJECT_RAW_COST
643 ,bl.PROJECT_BURDENED_COST
644 ,bl.PROJECT_REV_RATE_TYPE
645 ,bl.PROJECT_REV_EXCHANGE_RATE
646 ,bl.PROJECT_REV_RATE_DATE_TYPE
647 ,bl.PROJECT_REV_RATE_DATE
648 ,bl.PROJECT_REVENUE
649 ,bl.INIT_QUANTITY
650 ,bl.TXN_INIT_RAW_COST
651 ,bl.TXN_INIT_BURDENED_COST
652 ,bl.TXN_INIT_REVENUE
653 ,bl.TXN_MARKUP_PERCENT
654 ,bl.COST_IND_COMPILED_SET_ID
655 ,bl.QUANTITY_SOURCE
656 ,bl.RAW_COST_SOURCE
657 ,bl.BURDENED_COST_SOURCE
658 ,bl.REVENUE_SOURCE
659 ,bl.INIT_RAW_COST
660 ,bl.INIT_BURDENED_COST
661 ,bl.INIT_REVENUE
662 ,bl.PROJECT_INIT_RAW_COST
663 ,bl.PROJECT_INIT_BURDENED_COST
664 ,bl.PROJECT_INIT_REVENUE
665 FROM PA_BUDGET_LINES bl
666 WHERE bl.budget_line_id = g_rbl_budget_line_id_tab(i)
667
668 End of Bug Fix 4332086.
669 */
670
671 INSERT INTO PA_FP_ROLLUP_TMP
672 (
673 BUDGET_LINE_ID
674 ,BUDGET_VERSION_ID
675 ,RESOURCE_ASSIGNMENT_ID
676 ,START_DATE
677 ,END_DATE
678 ,PERIOD_NAME
679 ,TXN_CURRENCY_CODE
680 ,PROJECT_CURRENCY_CODE
681 ,PROJFUNC_CURRENCY_CODE
682 ,QUANTITY
683 ,TXN_RAW_COST
684 ,COST_RATE
685 ,RW_COST_RATE_OVERRIDE
686 ,TXN_BURDENED_COST
687 ,BURDEN_COST_RATE
688 ,BURDEN_COST_RATE_OVERRIDE
689 ,TXN_REVENUE
690 ,BILL_RATE
691 ,BILL_RATE_OVERRIDE
692 ,PROJFUNC_RAW_COST
693 ,PROJFUNC_BURDENED_COST
694 ,PROJFUNC_REVENUE
695 ,COST_REJECTION_CODE
696 ,REVENUE_REJECTION_CODE
697 ,BURDEN_REJECTION_CODE
698 ,PROJFUNC_COST_RATE_TYPE
699 ,PROJFUNC_COST_EXCHANGE_RATE
700 ,PROJFUNC_COST_RATE_DATE_TYPE
701 ,PROJFUNC_COST_RATE_DATE
702 ,PROJFUNC_REV_RATE_TYPE
703 ,PROJFUNC_REV_EXCHANGE_RATE
704 ,PROJFUNC_REV_RATE_DATE_TYPE
705 ,PROJFUNC_REV_RATE_DATE
706 ,PROJECT_COST_RATE_TYPE
707 ,PROJECT_COST_EXCHANGE_RATE
708 ,PROJECT_COST_RATE_DATE_TYPE
709 ,PROJECT_COST_RATE_DATE
710 ,PROJECT_RAW_COST
711 ,PROJECT_BURDENED_COST
712 ,PROJECT_REV_RATE_TYPE
713 ,PROJECT_REV_EXCHANGE_RATE
714 ,PROJECT_REV_RATE_DATE_TYPE
715 ,PROJECT_REV_RATE_DATE
716 ,PROJECT_REVENUE
717 ,INIT_QUANTITY
718 ,TXN_INIT_RAW_COST
719 ,TXN_INIT_BURDENED_COST
720 ,TXN_INIT_REVENUE
721 ,BILL_MARKUP_PERCENTAGE
722 ,COST_IND_COMPILED_SET_ID
723 ,QUANTITY_SOURCE
724 ,RAW_COST_SOURCE
725 ,BURDENED_COST_SOURCE
726 ,REVENUE_SOURCE
727 ,INIT_RAW_COST
728 ,INIT_BURDENED_COST
729 ,INIT_REVENUE
730 ,PROJECT_INIT_RAW_COST
731 ,PROJECT_INIT_BURDENED_COST
732 ,PROJECT_INIT_REVENUE
733 )
734 SELECT
735 g_rbl_budget_line_id_tab(i)
736 ,g_rbl_budget_version_id_tab(i)
737 ,g_rbl_res_assignment_id_tab(i)
738 ,g_rbl_start_date_tab(i)
739 ,g_rbl_end_date_tab(i)
740 ,g_rbl_period_name_tab(i)
741 ,NVL(g_rbl_txn_curr_code_ovr_tab(i),g_rbl_txn_curr_code_tab(i))
742 ,g_rbl_proj_curr_code_tab(i)
743 ,g_rbl_projfunc_curr_code_tab(i)
744 ,decode(g_rbl_quantity_tab(i),0,NULL,g_rbl_quantity_tab(i))
745 ,decode(g_rbl_txn_raw_cost_tab(i),0,NULL,g_rbl_txn_raw_cost_tab(i))
746 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,decode(g_rbl_txn_cost_rate_tab(i),0,NULL,g_rbl_txn_cost_rate_tab(i)),NULL)
747 ,decode(g_rbl_txn_curr_code_ovr_tab(i),NULL,NVL(g_rbl_txn_cost_rate_ovr_tab(i),bl.txn_cost_rate_override),g_rbl_txn_cost_rate_ovr_tab(i))
748 ,decode(g_rbl_txn_burden_cost_tab(i),0,NULL,g_rbl_txn_burden_cost_tab(i))
749 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,decode(g_rbl_txn_burden_rate_tab(i),0,NULL,g_rbl_txn_burden_rate_tab(i)),NULL)
750 ,decode(g_rbl_txn_curr_code_ovr_tab(i),NULL,NVL(g_rbl_txn_burden_rate_ovr_tab(i),bl.burden_cost_rate_override),g_rbl_txn_burden_rate_ovr_tab(i))
751 ,decode(g_rbl_txn_revenue_tab(i),0,NULL,g_rbl_txn_revenue_tab(i))
752 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,decode(g_rbl_txn_bill_rate_tab(i),0,NULL,g_rbl_txn_bill_rate_tab(i)),NULL)
753 ,decode(g_rbl_txn_curr_code_ovr_tab(i),NULL,NVL(g_rbl_txn_bill_rate_ovr_tab(i),bl.txn_bill_rate_override),g_rbl_txn_bill_rate_ovr_tab(i))
754 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.RAW_COST,NULL)
755 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.BURDENED_COST,NULL)
756 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.REVENUE,NULL)
757 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.COST_REJECTION_CODE,NULL)
758 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.REVENUE_REJECTION_CODE,NULL)
759 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.BURDEN_REJECTION_CODE,NULL)
760 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJFUNC_COST_RATE_TYPE,NULL)
761 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJFUNC_COST_EXCHANGE_RATE,NULL)
762 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJFUNC_COST_RATE_DATE_TYPE,NULL)
763 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJFUNC_COST_RATE_DATE,NULL)
764 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJFUNC_REV_RATE_TYPE,NULL)
765 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJFUNC_REV_EXCHANGE_RATE,NULL)
766 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJFUNC_REV_RATE_DATE_TYPE,NULL)
767 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJFUNC_REV_RATE_DATE,NULL)
768 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_COST_RATE_TYPE,NULL)
769 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_COST_EXCHANGE_RATE,NULL)
770 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_COST_RATE_DATE_TYPE,NULL)
771 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_COST_RATE_DATE,NULL)
772 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_RAW_COST,NULL)
773 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_BURDENED_COST,NULL)
774 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_REV_RATE_TYPE,NULL)
775 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_REV_EXCHANGE_RATE,NULL)
776 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_REV_RATE_DATE_TYPE,NULL)
777 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_REV_RATE_DATE,NULL)
778 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_REVENUE,NULL)
779 ,bl.INIT_QUANTITY
780 ,bl.TXN_INIT_RAW_COST
781 ,bl.TXN_INIT_BURDENED_COST
782 ,bl.TXN_INIT_REVENUE
783 ,bl.TXN_MARKUP_PERCENT
784 ,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.COST_IND_COMPILED_SET_ID,NULL)
785 ,bl.QUANTITY_SOURCE
786 ,bl.RAW_COST_SOURCE
787 ,bl.BURDENED_COST_SOURCE
788 ,bl.REVENUE_SOURCE
789 ,bl.INIT_RAW_COST
790 ,bl.INIT_BURDENED_COST
791 ,bl.INIT_REVENUE
792 ,bl.PROJECT_INIT_RAW_COST
793 ,bl.PROJECT_INIT_BURDENED_COST
794 ,bl.PROJECT_INIT_REVENUE
795 FROM PA_BUDGET_LINES bl
796 WHERE bl.budget_line_id = g_rbl_budget_line_id_tab(i)
797
798 /*Perf Bug fix:4251959 AND bl.budget_version_id = g_rbl_budget_version_id_tab(i) */
799 ;
800 END IF;
801 EXCEPTION
802
803 WHEN OTHERS THEN
804 print_msg('Unexpected error in blkInsertBlFpLines['||sqlcode||sqlerrm||']');
805 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
806 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_FP_SPREAD_AMTS_PKG',
807 p_procedure_name => 'blkInsertBlFpLines'||l_stage);
808 If p_pa_debug_mode = 'Y' Then
809 pa_debug.reset_err_stack;
810 End If;
811 raise FND_API.G_EXC_UNEXPECTED_ERROR;
812
813 END blkInsertBlFpLines;
814
815 /* This API populates the plsql tables with budget lines for bulk insert */
816 PROCEDURE insert_budget_line(
817 p_resource_assignment_id IN pa_budget_lines.RESOURCE_ASSIGNMENT_ID%TYPE
818 ,p_start_date IN pa_budget_lines.START_DATE%TYPE
819 ,p_end_date IN pa_budget_lines.END_DATE%TYPE
820 ,p_period_name IN pa_budget_lines.PERIOD_NAME%TYPE
821 ,p_txn_currency_code IN pa_budget_lines.TXN_CURRENCY_CODE%TYPE
822 ,p_txn_curr_code_ovr IN pa_budget_lines.TXN_CURRENCY_CODE%TYPE
823 ,x_budget_line_id OUT NOCOPY pa_budget_lines.BUDGET_LINE_ID%TYPE
824 ,p_budget_version_id IN pa_budget_lines.BUDGET_VERSION_ID%TYPE
825 ,p_proj_curr_cd IN pa_projects_all.project_currency_code%TYPE
826 ,p_projfunc_curr_cd IN pa_projects_all.projfunc_currency_code%TYPE
827 ,x_return_status OUT NOCOPY VARCHAR2
828 ,x_msg_count OUT NOCOPY NUMBER
829 ,x_msg_data OUT NOCOPY VARCHAR2) IS
830
831
832 l_stage VARCHAR2(1000);
833 l_budget_line_id NUMBER;
834
835 BEGIN
836 l_stage := '1100 :: Entered insert_budget_line()';
837 x_return_status := 'S';
838 x_msg_count := 0;
839 x_msg_data := NULL;
840
841 -- get Budget Line ID
842 SELECT PA_BUDGET_LINES_S.NEXTVAL
843 INTO l_budget_line_id
844 FROM DUAL;
845
846 x_budget_line_id := l_budget_line_id;
847 g_bl_budget_line_id_tab(nvl(g_bl_budget_line_id_tab.LAST,0)+1) := x_budget_line_id;
848 g_bl_budget_version_id_tab(nvl(g_bl_budget_version_id_tab.LAST,0)+1) := p_budget_version_id;
849 g_bl_res_assignment_id_tab(nvl(g_bl_res_assignment_id_tab.LAST,0)+1) := p_resource_assignment_id;
850 g_bl_start_date_tab(nvl(g_bl_start_date_tab.LAST,0)+1) := p_start_date;
851 g_bl_end_date_tab(nvl(g_bl_end_date_tab.LAST,0)+1) := p_end_date;
852 g_bl_period_name_tab(nvl(g_bl_period_name_tab.LAST,0)+1) := p_period_name;
853 g_bl_txn_curr_code_tab(nvl(g_bl_txn_curr_code_tab.LAST,0)+1) := p_txn_currency_code;
854 g_bl_txn_curr_code_ovr_tab(nvl(g_bl_txn_curr_code_ovr_tab.LAST,0)+1) := p_txn_curr_code_ovr;
855 g_bl_proj_curr_code_tab(nvl(g_bl_proj_curr_code_tab.LAST,0)+1) := p_proj_curr_cd;
856 g_bl_projfunc_curr_code_tab(nvl(g_bl_projfunc_curr_code_tab.LAST,0)+1) := p_projfunc_curr_cd;
857
858
859
860 EXCEPTION
861 WHEN OTHERS THEN
862 print_msg(l_stage||sqlcode||sqlerrm);
863 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
864 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_FP_SPREAD_AMTS_PKG',
865 p_procedure_name => 'insert_budget_line'||l_stage);
866 If p_pa_debug_mode = 'Y' Then
867 pa_debug.reset_err_stack;
868 End If;
869 raise FND_API.G_EXC_UNEXPECTED_ERROR;
870
871 END insert_budget_line;
872
873 /* This API populates the plsql tables with rollup tmp lines for bulk insert */
874 PROCEDURE insert_rollup_tmp(
875 p_ra_rec IN resource_assignment_rec_type
876 ,p_budget_version_id IN NUMBER
877 ,p_start_date IN pa_fp_rollup_tmp.START_DATE%TYPE
878 ,p_end_date IN pa_fp_rollup_tmp.END_DATE%TYPE
879 ,p_period_name IN pa_fp_rollup_tmp.PERIOD_NAME%TYPE
880 ,p_budget_line_id IN pa_fp_rollup_tmp.BUDGET_LINE_ID%TYPE
881 ,p_quantity IN pa_fp_rollup_tmp.QUANTITY%TYPE
882 ,p_txn_raw_cost IN pa_fp_rollup_tmp.TXN_RAW_COST%TYPE
883 ,p_txn_burdened_cost IN pa_fp_rollup_tmp.TXN_BURDENED_COST%TYPE
884 ,p_txn_revenue IN pa_fp_rollup_tmp.TXN_REVENUE%TYPE
885 ,x_return_status OUT NOCOPY VARCHAR2
886 ,x_msg_count OUT NOCOPY NUMBER
887 ,x_msg_data OUT NOCOPY VARCHAR2) IS
888
889 l_stage VARCHAR2(1000);
890
891
892 BEGIN
893 l_stage := '1200::Entered insert_rollup_tmp()';
894 x_return_status := 'S';
895 x_msg_data := NULL;
896
897 g_rl_res_assignment_id_tab(nvl(g_rl_res_assignment_id_tab.LAST,0)+1) := p_ra_rec.RESOURCE_ASSIGNMENT_ID;
898 g_rl_start_date_tab(nvl(g_rl_start_date_tab.LAST,0) +1) := p_start_date;
899 g_rl_end_date_tab(nvl(g_rl_end_date_tab.LAST,0) +1 ) := p_end_date;
900 g_rl_period_name_tab(nvl(g_rl_period_name_tab.LAST,0)+1) := p_period_name;
901 g_rl_txn_curr_code_tab(nvl(g_rl_txn_curr_code_tab.LAST,0)+1) := p_ra_rec.TXN_CURRENCY_CODE;
902 g_rl_txn_curr_code_ovr_tab(nvl(g_rl_txn_curr_code_ovr_tab.LAST,0)+1) := p_ra_rec.TXN_CURRENCY_CODE_OVERRIDE;
903 g_rl_budget_line_id_tab(nvl(g_rl_budget_line_id_tab.LAST,0)+1) := p_budget_line_id;
904 g_rl_budget_version_id_tab(nvl(g_rl_budget_version_id_tab.LAST,0)+1) := p_budget_version_id;
905 g_rl_proj_curr_code_tab(nvl(g_rl_proj_curr_code_tab.LAST,0)+1) := p_ra_rec.PROJECT_CURRENCY_CODE;
906 g_rl_projfunc_curr_code_tab(nvl(g_rl_projfunc_curr_code_tab.LAST,0)+1) := p_ra_rec.PROJFUNC_CURRENCY_CODE;
907 g_rl_quantity_tab(nvl(g_rl_quantity_tab.LAST,0)+1) := p_quantity;
908 g_rl_txn_raw_cost_tab(nvl(g_rl_txn_raw_cost_tab.LAST,0)+1) := p_txn_raw_cost;
909 g_rl_txn_cost_rate_tab(nvl(g_rl_txn_cost_rate_tab.LAST,0)+1) := p_ra_rec.RAW_COST_RATE;
910 g_rl_txn_cost_rate_ovr_tab(nvl(g_rl_txn_cost_rate_ovr_tab.LAST,0)+1) := p_ra_rec.RAW_COST_RATE_OVERRIDE;
911 g_rl_txn_burden_cost_tab(nvl(g_rl_txn_burden_cost_tab.LAST,0)+1) := p_txn_burdened_cost;
912 g_rl_txn_burden_rate_tab(nvl(g_rl_txn_burden_rate_tab.LAST,0)+1) := p_ra_rec.BURDEN_COST_RATE;
913 g_rl_txn_burden_rate_ovr_tab(nvl(g_rl_txn_burden_rate_ovr_tab.LAST,0)+1) := p_ra_rec.BURDEN_COST_RATE_OVERRIDE;
914 g_rl_txn_revenue_tab(nvl(g_rl_txn_revenue_tab.LAST,0)+1) := p_txn_revenue;
915 g_rl_txn_bill_rate_tab(nvl(g_rl_txn_bill_rate_tab.LAST,0)+1) := p_ra_rec.BILL_RATE;
916 g_rl_txn_bill_rate_ovr_tab(nvl(g_rl_txn_bill_rate_ovr_tab.LAST,0)+1) := p_ra_rec.BILL_RATE_OVERRIDE;
917
918 EXCEPTION
919 WHEN OTHERS THEN
920
921 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
922 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_FP_SPREAD_AMTS_PKG',
923 p_procedure_name => 'insert_rollup_tmp()'||l_stage);
924 If p_pa_debug_mode = 'Y' Then
925 pa_debug.reset_err_stack;
926 End If;
927 raise FND_API.G_EXC_UNEXPECTED_ERROR;
928
929 END insert_rollup_tmp;
930
931 /* This API populates the plsql tables with rollup tmp lines for bulk insert */
932 PROCEDURE insert_rollup_tmp_with_bl(
933 p_ra_rec IN resource_assignment_rec_type
934 ,p_budget_version_id IN NUMBER
935 ,p_start_date IN pa_fp_rollup_tmp.START_DATE%TYPE
936 ,p_end_date IN pa_fp_rollup_tmp.END_DATE%TYPE
937 ,p_period_name IN pa_fp_rollup_tmp.PERIOD_NAME%TYPE
938 ,p_budget_line_id IN pa_fp_rollup_tmp.BUDGET_LINE_ID%TYPE
939 ,p_quantity IN pa_fp_rollup_tmp.QUANTITY%TYPE
940 ,p_txn_raw_cost IN pa_fp_rollup_tmp.TXN_RAW_COST%TYPE
941 ,p_txn_burdened_cost IN pa_fp_rollup_tmp.TXN_BURDENED_COST%TYPE
942 ,p_txn_revenue IN pa_fp_rollup_tmp.TXN_REVENUE%TYPE
943 ,x_return_status OUT NOCOPY VARCHAR2
944 ,x_msg_count OUT NOCOPY NUMBER
945 ,x_msg_data OUT NOCOPY VARCHAR2) IS
946
947
948 l_stage VARCHAR2(1000);
949
950 BEGIN
951 l_stage := '1400:: Entered insert_rollup_tmp_with_bl()';
952 x_return_status := 'S';
953 x_msg_count := 0;
954 x_msg_data := NULL;
955
956 g_rbl_res_assignment_id_tab(nvl(g_rbl_res_assignment_id_tab.LAST,0)+1) := p_ra_rec.RESOURCE_ASSIGNMENT_ID;
957 g_rbl_start_date_tab(nvl(g_rbl_start_date_tab.LAST,0) +1) := p_start_date;
958 g_rbl_end_date_tab(nvl(g_rbl_end_date_tab.LAST,0) +1 ) := p_end_date;
959 g_rbl_period_name_tab(nvl(g_rbl_period_name_tab.LAST,0)+1) := p_period_name;
960 g_rbl_txn_curr_code_tab(nvl(g_rbl_txn_curr_code_tab.LAST,0)+1) := p_ra_rec.TXN_CURRENCY_CODE;
961 g_rbl_txn_curr_code_ovr_tab(nvl(g_rbl_txn_curr_code_ovr_tab.LAST,0)+1) := p_ra_rec.TXN_CURRENCY_CODE_OVERRIDE;
962 g_rbl_budget_line_id_tab(nvl(g_rbl_budget_line_id_tab.LAST,0)+1) := p_budget_line_id;
963 g_rbl_budget_version_id_tab(nvl(g_rbl_budget_version_id_tab.LAST,0)+1) := p_budget_version_id;
964 g_rbl_proj_curr_code_tab(nvl(g_rbl_proj_curr_code_tab.LAST,0)+1) := p_ra_rec.PROJECT_CURRENCY_CODE;
965 g_rbl_projfunc_curr_code_tab(nvl(g_rbl_projfunc_curr_code_tab.LAST,0)+1) := p_ra_rec.PROJFUNC_CURRENCY_CODE;
966 g_rbl_quantity_tab(nvl(g_rbl_quantity_tab.LAST,0)+1) := p_quantity;
967 g_rbl_txn_raw_cost_tab(nvl(g_rbl_txn_raw_cost_tab.LAST,0)+1) := p_txn_raw_cost;
968 g_rbl_txn_cost_rate_tab(nvl(g_rbl_txn_cost_rate_tab.LAST,0)+1) := p_ra_rec.RAW_COST_RATE;
969 g_rbl_txn_cost_rate_ovr_tab(nvl(g_rbl_txn_cost_rate_ovr_tab.LAST,0)+1) := p_ra_rec.RAW_COST_RATE_OVERRIDE;
970 g_rbl_txn_burden_cost_tab(nvl(g_rbl_txn_burden_cost_tab.LAST,0)+1) := p_txn_burdened_cost;
971 g_rbl_txn_burden_rate_tab(nvl(g_rbl_txn_burden_rate_tab.LAST,0)+1) := p_ra_rec.BURDEN_COST_RATE;
972 g_rbl_txn_burden_rate_ovr_tab(nvl(g_rbl_txn_burden_rate_ovr_tab.LAST,0)+1) := p_ra_rec.BURDEN_COST_RATE_OVERRIDE;
973 g_rbl_txn_revenue_tab(nvl(g_rbl_txn_revenue_tab.LAST,0)+1) := p_txn_revenue;
974 g_rbl_txn_bill_rate_tab(nvl(g_rbl_txn_bill_rate_tab.LAST,0)+1) := p_ra_rec.BILL_RATE;
975 g_rbl_txn_bill_rate_ovr_tab(nvl(g_rbl_txn_bill_rate_ovr_tab.LAST,0)+1) := p_ra_rec.BILL_RATE_OVERRIDE;
976
977
978 EXCEPTION
979 WHEN OTHERS THEN
980 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
981 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_FP_SPREAD_AMTS_PKG',
982 p_procedure_name => 'insert_rollup_tmp_with_bl()'||l_stage);
983 If p_pa_debug_mode = 'Y' Then
984 pa_debug.reset_err_stack;
985 End If;
986 raise FND_API.G_EXC_UNEXPECTED_ERROR;
987
988 END insert_rollup_tmp_with_bl;
989
990 /* This API rounds the given amount/quantity to following precision level
991 * If rate base flag is 'Y' then quantity will be rounded to 5 decimals and amount will be rounded as per the currency precision
992 * If rate base flag is 'N' then quantity will be rounded as per the currency precision level
993 */
994 FUNCTION Round_Qty_Amts(p_rate_base_flag Varchar2 default 'N'
995 ,p_quantity_flag Varchar2
996 ,p_currency_code Varchar2
997 ,p_amounts Number ) RETURN NUMBER IS
998
999 l_return_Amounts Number := NULL;
1000 BEGIN
1001 l_return_Amounts := p_amounts;
1002 If p_quantity_flag = 'Y' Then
1003 If p_amounts is NOT NULL Then
1004 If nvl(p_rate_base_flag,'N') = 'Y' Then
1005 l_return_Amounts := round(l_return_Amounts,5);
1006 Else
1007 l_return_Amounts := pa_currency.round_trans_currency_amt1(p_amounts,p_currency_code);
1008 End If;
1009 End If;
1010
1011 Else
1012 l_return_Amounts := pa_currency.round_trans_currency_amt1(p_amounts,p_currency_code);
1013 End If;
1014
1015 RETURN l_return_Amounts;
1016 EXCEPTION
1017 WHEN OTHERS THEN
1018 print_msg('Unexpected error in Round_Qty_Amts['||sqlcode||sqlerrm||']');
1019 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_FP_SPREAD_AMTS_PKG',
1020 p_procedure_name => 'spread');
1021 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1022 END Round_Qty_Amts;
1023
1024 PROCEDURE spread ( p_number_of_amounts IN INTEGER,
1025 p_amount1 IN NUMBER,
1026 p_amount2 IN NUMBER,
1027 p_amount3 IN NUMBER,
1028 p_amount4 IN NUMBER,
1029 p_amount5 IN NUMBER,
1030 p_amount6 IN NUMBER,
1031 p_amount7 IN NUMBER,
1032 p_amount8 IN NUMBER,
1033 p_amount9 IN NUMBER,
1034 p_amount10 IN NUMBER,
1035 p_start_end_date IN start_end_date_table_type,
1036 p_spread_curve IN spread_curve_type,
1037 p_start_period IN INTEGER := 0,
1038 p_end_period IN INTEGER := 0,
1039 p_global_start_date IN Date,
1040 x_spread_amounts IN OUT NOCOPY spread_table_type,
1041 x_return_status OUT NOCOPY VARCHAR2,
1042 x_msg_count OUT NOCOPY NUMBER,
1043 x_msg_data OUT NOCOPY VARCHAR2) IS
1044
1045 l_start_period INTEGER;
1046 l_end_period INTEGER;
1047 l_spread_curve spread_curve_type; -- Spread Curve
1048 nofp NUMBER; -- Actual number of periods
1049 time_step NUMBER; -- Time Step
1050 allocation NUMBER; -- position of allocation of period
1051 accumulated_allocation NUMBER;-- position of accumulated allocation
1052 weight_sum NUMBER;
1053 amount_sum NUMBER;
1054 tmp_start_date DATE;
1055 tmp_end_date DATE;
1056 tmp_rec spread_record_type;
1057 k INTEGER;
1058 j INTEGER;
1059 l_period_counter INTEGER;
1060 exit_flag BOOLEAN;
1061
1062 l_msg_count NUMBER := 0;
1063 l_data VARCHAR2(2000);
1064 l_msg_data VARCHAR2(2000);
1065 l_msg_index_out NUMBER;
1066 l_debug_mode VARCHAR2(30);
1067
1068 l_global_actual_periods NUMBER;
1069 l_global_allocation NUMBER;
1070 l_global_percentage NUMBER;
1071
1072 l_stage INTEGER;
1073
1074 BEGIN
1075 l_stage := 10;
1076 print_msg(' '||l_stage||' enter spread()');
1077 x_return_status := FND_API.G_RET_STS_SUCCESS;
1078 If p_pa_debug_mode = 'Y' Then
1079 pa_debug.init_err_stack('PA_FP_SPREAD_AMTS_PKG.spread');
1080 End If;
1081
1082 fnd_profile.get('PA_DEBUG_MODE', l_debug_mode);
1083 pa_debug.set_process('PLSQL', 'LOG', l_debug_mode);
1084
1085 pa_debug.g_err_stage := 'Entered PA_FP_SPREAD_AMTS_PKG.spread';
1086 IF P_PA_DEBUG_MODE = 'Y' THEN
1087 pa_debug.write('spread: '||g_module_name,
1088 pa_debug.g_err_stage,
1089 3);
1090 END IF;
1091
1092 l_stage := 20;
1093 print_msg(' '||l_stage||' p_number_of_amounts => '||p_number_of_amounts);
1094 /*
1095 print_msg(' '||'p_amount1 => '||p_amount1);
1096 print_msg(' '||'p_amount2 => '||p_amount2);
1097 print_msg(' '||'p_amount3 => '||p_amount3);
1098 print_msg(' '||'p_amount4 => '||p_amount4);
1099 print_msg(' '||'p_amount5 => '||p_amount5);
1100 print_msg(' '||'p_amount6 => '||p_amount6);
1101 print_msg(' '||'p_amount7 => '||p_amount7);
1102 print_msg(' '||'p_amount8 => '||p_amount8);
1103 print_msg(' '||'p_amount9 => '||p_amount9);
1104 print_msg(' '||'p_amount10 => '||p_amount10);
1105
1106 FOR i IN 1 .. p_start_end_date.COUNT()
1107 LOOP
1108 NULL;
1109 print_msg(' '||'plan start/end date => '||p_start_end_date(i).start_date||'/'||p_start_end_date(i).end_date);
1110 END LOOP;
1111
1112 print_msg(' '||'spread curve => '||p_spread_curve(1)||' '||p_spread_curve(2)||' '||p_spread_curve(3));
1113 print_msg(' '||p_spread_curve(4)||' '||p_spread_curve(5)||' '||p_spread_curve(6)||' '||p_spread_curve(7));
1114 print_msg(' '||p_spread_curve(8)||' '||p_spread_curve(9)||' '||p_spread_curve(10));
1115 print_msg(' '||'start/end period => '||p_start_period||'/'||p_end_period);
1116 print_msg(' '||'p_global_start_date => '||p_global_start_date);
1117
1118 FOR i IN 1 .. x_spread_amounts.COUNT()
1119 LOOP
1120 IF i = 1 OR i = x_spread_amounts.COUNT() THEN
1121 NULL;
1122 print_msg(' '||'start/end date '||i||' => '||x_spread_amounts(i).start_date||'/'||x_spread_amounts(i).end_date);
1123 END IF;
1124 END LOOP;
1125 */
1126
1127 -- Validating
1128
1129 l_stage := 30;
1130 print_msg(' '||l_stage||' before validate p_number_of_amounts');
1131 -- p_number_of_amounts cannot overflow
1132 IF NOT p_number_of_amounts BETWEEN 1 AND 10 THEN
1133
1134 x_return_status := FND_API.G_RET_STS_ERROR;
1135 x_msg_data := 'PA_FP_NUM_OF_AMTS_OVERFLOW';
1136 If p_pa_debug_mode = 'Y' Then
1137 pa_debug.reset_err_stack;
1138 End If;
1139 RETURN;
1140 END IF;
1141
1142 l_stage := 40;
1143 print_msg(' '||l_stage||' before validate p_start_end_date');
1144 -- p_start_end_date cannot be null and
1145 -- each start_date must earlier than end_date in p_start_end_date,
1146 -- and they cannot overlap each other.
1147 IF p_start_end_date IS NULL THEN
1148 x_return_status := FND_API.G_RET_STS_ERROR;
1149 x_msg_data := 'PA_FP_PLAN_START_END_DATE_NULL';
1150 If p_pa_debug_mode = 'Y' Then
1151 pa_debug.reset_err_stack;
1152 End If;
1153 RETURN;
1154 END IF;
1155 FOR k IN 1 .. p_start_end_date.COUNT()
1156 LOOP
1157 IF --p_start_end_date(k) IS NULL OR
1158 p_start_end_date(k).start_date IS NULL OR
1159 p_start_end_date(k).end_date IS NULL OR
1160 p_start_end_date(k).start_date >
1161 p_start_end_date(k).end_date OR
1162 k < p_start_end_date.COUNT() AND
1163 p_start_end_date(k + 1).start_date <=
1164 p_start_end_date(k).end_date THEN
1165
1166 x_return_status := FND_API.G_RET_STS_ERROR;
1167 x_msg_data := 'PA_FP_START_END_DATE_OVERLAP';
1168 If p_pa_debug_mode = 'Y' Then
1169 pa_debug.reset_err_stack;
1170 End If;
1171 RETURN;
1172 END IF;
1173 END LOOP;
1174
1175 l_stage := 50;
1176 print_msg(' '||l_stage||' before validate p_spread_curve');
1177 -- If p_spread_curve is null, spread as equal distribution.
1178 IF p_spread_curve IS NULL THEN
1179 l_spread_curve :=
1180 spread_curve_type(10,10,10,10,10,10,10,10,10,10);
1181 ELSE
1182 l_spread_curve := p_spread_curve;
1183 END IF;
1184
1185 l_stage := 60;
1186 print_msg(' '||l_stage||' before validate x_spread_amounts');
1187 -- x_spread_amounts cannot be NULL and
1188 -- x_spread_amounts' start end date must match with p_start_end_date.
1189 IF (x_spread_amounts IS NULL OR x_spread_amounts.COUNT() = 0 ) THEN
1190 x_return_status := FND_API.G_RET_STS_ERROR;
1191 x_msg_data := 'PA_FP_PERIODS_IS_NULL';
1192 print_msg('x_msg_data['||x_msg_data||']');
1193 If p_pa_debug_mode = 'Y' Then
1194 pa_debug.reset_err_stack;
1195 End If;
1196 RETURN;
1197 END IF;
1198 --print_msg('Count of x_spread_amounts.COUNT()['||x_spread_amounts.COUNT()||']');
1199 FOR k IN 1 .. x_spread_amounts.COUNT()
1200 LOOP
1201 IF --x_spread_amounts(k) IS NULL OR
1202 x_spread_amounts(k).start_date IS NULL OR
1203 x_spread_amounts(k).end_date IS NULL OR
1204 x_spread_amounts(k).start_date >
1205 x_spread_amounts(k).end_date OR
1206 k < x_spread_amounts.COUNT() AND
1207 x_spread_amounts(k + 1).start_date <=
1208 x_spread_amounts(k).end_date THEN
1209
1210 x_return_status := FND_API.G_RET_STS_ERROR;
1211 x_msg_data := 'PA_FP_START_END_DATE_NOT_MATCH';
1212 If p_pa_debug_mode = 'Y' Then
1213 pa_debug.reset_err_stack;
1214 End If;
1215 --print_msg('x_msg_data['||x_msg_data||']');
1216 RETURN;
1217 END IF;
1218 END LOOP;
1219 IF p_start_end_date(1).start_date >
1220 x_spread_amounts(1).end_date OR
1221 p_start_end_date(p_start_end_date.COUNT()).end_date <
1222 x_spread_amounts(x_spread_amounts.COUNT()).start_date THEN
1223
1224 x_return_status := FND_API.G_RET_STS_ERROR;
1225 x_msg_data := 'PA_FP_START_END_DATE_NOT_MATCH';
1226 --print_msg('x_msg_data['||x_msg_data||']');
1227 If p_pa_debug_mode = 'Y' Then
1228 pa_debug.reset_err_stack;
1229 End If;
1230 RETURN;
1231 END IF;
1232
1233
1234 l_stage := 70;
1235 print_msg(' '||l_stage||' before validate p_start/end_period');
1236 -- p_start_period/p_end_period validateing
1237 IF NOT (p_start_period BETWEEN 1 AND x_spread_amounts.COUNT() AND
1238 p_end_period BETWEEN 1 AND x_spread_amounts.COUNT() AND
1239 p_start_period <= p_end_period) THEN
1240 -- update 032504 iand
1241 -- OR
1242 -- p_start_period BETWEEN 1 AND x_spread_amounts.COUNT() AND
1243 -- p_end_period = 0 OR
1244 -- p_start_period = 0 AND
1245 -- p_end_period BETWEEN 1 AND x_spread_amounts.COUNT() OR
1246 -- p_start_period = 0 AND p_end_period = 0) THEN
1247
1248 x_return_status := FND_API.G_RET_STS_ERROR;
1249 x_msg_data := 'PA_FP_PERIOD_NO_MATCH';
1250 print_msg('x_msg_data['||x_msg_data||']');
1251 If p_pa_debug_mode = 'Y' Then
1252 pa_debug.reset_err_stack;
1253 End If;
1254 RETURN;
1255 END IF;
1256 l_start_period := p_start_period;
1257 l_end_period := p_end_period;
1258 l_stage := 80;
1259 print_msg(' '||l_stage||' after validation');
1260
1261 -- Calculate the number of period for each period and
1262 -- total number of period
1263
1264 FOR k IN 1 .. x_spread_amounts.COUNT()
1265 LOOP
1266 x_spread_amounts(k).actual_days := 0;
1267 END LOOP;
1268
1269
1270 k := 1;
1271 FOR j IN 1 .. x_spread_amounts.COUNT()
1272 LOOP
1273
1274 IF x_spread_amounts(j).end_date <
1275 p_start_end_date(k).start_date THEN
1276
1277 x_spread_amounts(j).actual_days := 0;
1278 x_spread_amounts(j).actual_periods := 0;
1279
1280 ELSE
1281
1282 IF p_start_end_date(k).start_date BETWEEN
1283 x_spread_amounts(j).start_date AND
1284 x_spread_amounts(j).end_date THEN
1285 tmp_start_date := p_start_end_date(k).start_date;
1286 ELSE
1287 tmp_start_date := x_spread_amounts(j).start_date;
1288 END IF;
1289 IF p_start_end_date(k).end_date BETWEEN
1290 x_spread_amounts(j).start_date AND
1291 x_spread_amounts(j).end_date THEN
1292 tmp_end_date := p_start_end_date(k).end_date;
1293 ELSE
1294 tmp_end_date := x_spread_amounts(j).end_date;
1295 END IF;
1296 x_spread_amounts(j).actual_days :=
1297 x_spread_amounts(j).actual_days +
1298 tmp_end_date - tmp_start_date + 1;
1299 x_spread_amounts(j).actual_periods :=
1300 x_spread_amounts(j).actual_days /
1301 (x_spread_amounts(j).end_date
1302 - x_spread_amounts(j).start_date + 1);
1303
1304 LOOP
1305 EXIT WHEN NOT (k < p_start_end_date.COUNT() AND
1306 p_start_end_date(k + 1).end_date <=
1307 x_spread_amounts(j).end_date);
1308
1309 k := k + 1;
1310
1311 x_spread_amounts(j).actual_days :=
1312 x_spread_amounts(j).actual_days +
1313 p_start_end_date(k).end_date -
1314 p_start_end_date(k).start_date + 1;
1315 x_spread_amounts(j).actual_periods :=
1316 x_spread_amounts(j).actual_days /
1317 (x_spread_amounts(j).end_date
1318 - x_spread_amounts(j).start_date + 1);
1319
1320 END LOOP;
1321
1322 IF k < p_start_end_date.COUNT() AND
1323 p_start_end_date(k + 1).start_date <=
1324 x_spread_amounts(j).end_date THEN
1325
1326 k := k + 1;
1327 tmp_start_date := p_start_end_date(k).start_date;
1328 tmp_end_date := x_spread_amounts(j).end_date;
1329 x_spread_amounts(j).actual_days :=
1330 x_spread_amounts(j).actual_days +
1331 tmp_end_date - tmp_start_date + 1;
1332 x_spread_amounts(j).actual_periods :=
1333 x_spread_amounts(j).actual_days /
1334 (x_spread_amounts(j).end_date
1335 - x_spread_amounts(j).start_date + 1);
1336 END IF;
1337
1338 IF k < p_start_end_date.COUNT() AND
1339 p_start_end_date(k).end_date <=
1340 x_spread_amounts(j).end_date THEN
1341 k := k + 1;
1342 END IF;
1343
1344 END IF;
1345
1346 END LOOP;
1347 IF p_global_start_date IS NOT NULL THEN
1348 --print_msg('end date['||x_spread_amounts(l_start_period).end_date||']StartDate['||x_spread_amounts(l_start_period).start_date||']');
1349 l_global_actual_periods :=
1350 (x_spread_amounts(l_start_period).end_date -
1351 p_global_start_date + 1) /
1352 (x_spread_amounts(l_start_period).end_date -
1353 x_spread_amounts(l_start_period).start_date + 1);
1354 --print_msg('l_global_actual_periods['||l_global_actual_periods||']');
1355 END IF;
1356 l_stage := 81;
1357 --print_msg(' '||l_stage||' after calculate actual period for global start date '||round(l_global_actual_periods,2));
1358 --print_msg('Actual num of periods['||x_spread_amounts(k).actual_periods||']SpCount['||x_spread_amounts.COUNT||']');
1359 nofp := 0;
1360 FOR k IN 1 .. x_spread_amounts.COUNT()
1361 LOOP
1362 nofp := nofp + x_spread_amounts(k).actual_periods;
1363 --print_msg('nofp['||nofp||']spactualperiods['||x_spread_amounts(k).actual_periods||']');
1364 END LOOP;
1365 l_stage := 90;
1366 print_msg(' '||l_stage||' after calculate number of period');
1367
1368 -- Calculate bucket time step
1369 print_msg('l_spread_curve['||l_spread_curve.count||']');
1370 time_step := l_spread_curve.COUNT()/nofp;
1371 l_stage := 100;
1372 print_msg(' '||l_stage||' after calculate bucket time step ['||time_step||']');
1373
1374 -- Calculate bucket allocation for each period
1375
1376 FOR k IN 1 .. x_spread_amounts.COUNT()
1377 LOOP
1378
1379 x_spread_amounts(k).allocation :=
1380 x_spread_amounts(k).actual_periods * time_step;
1381
1382 END LOOP;
1383 l_stage := 110;
1384 print_msg(l_stage||' after calculate allocation of period global_sDate['||p_global_start_date||']timeStep['||time_step||']alloc['||x_spread_amounts(k).allocation||']');
1385 -- update 032204 iand
1386 IF p_global_start_date IS NOT NULL THEN
1387 l_global_allocation := l_global_actual_periods * time_step;
1388 print_msg('l_global_allocation['||l_global_allocation||']');
1389 END IF;
1390 l_stage := 111;
1391 print_msg(l_stage||' after calculate allocation for global start date '||round(l_global_allocation,2));
1392
1393 -- Calculate percentage for each period
1394
1395 j := 1; -- position of weight in l_spread_curve
1396 allocation := 0; -- position of allocation of period
1397 accumulated_allocation := 0; -- position of accumulated allocation
1398 FOR k IN 1 .. x_spread_amounts.COUNT()
1399 LOOP
1400 allocation := allocation + x_spread_amounts(k).allocation;
1401 x_spread_amounts(k).percentage := 0;
1402 LOOP
1403 IF allocation >= j THEN
1404 x_spread_amounts(k).percentage :=
1405 x_spread_amounts(k).percentage +
1406 (j - accumulated_allocation) *
1407 l_spread_curve(j);
1408 accumulated_allocation := j;
1409 j := j + 1;
1410 END IF;
1411 EXIT WHEN allocation < j;
1412 END LOOP;
1413 IF j <= l_spread_curve.COUNT() THEN
1414 x_spread_amounts(k).percentage :=
1415 x_spread_amounts(k).percentage +
1416 (allocation - accumulated_allocation) *
1417 l_spread_curve(j);
1418 END IF;
1419 accumulated_allocation := allocation;
1420
1421 END LOOP;
1422 l_stage := 120;
1423 print_msg(' '||l_stage||' after calculate percentage of period');
1424 IF p_global_start_date IS NOT NULL THEN
1425 /** Bug 3825695 Raja Aug 11 2004 -- the logic to compute global periods is wrong
1426 -- so the following is going for a toss
1427
1428 allocation := 0;
1429 FOR k IN 1 .. l_start_period
1430 LOOP
1431 --print_msg('allocation['||allocation||']spAlloc['||x_spread_amounts(k).allocation||']');
1432 allocation :=
1433 allocation + x_spread_amounts(k).allocation;
1434 END LOOP;
1435 accumulated_allocation := allocation - l_global_allocation;
1436 --accumulated_allocation := l_global_allocation - allocation ;
1437 print_msg('accumulated_allocation['||accumulated_allocation||']l_global_allocation['||l_global_allocation||']');
1438 j := ceil(accumulated_allocation);
1439 print_msg('value of j['||j||']');
1440 l_global_percentage := 0;
1441 LOOP
1442 IF allocation >= j THEN
1443 l_global_percentage :=
1444 l_global_percentage +
1445 (j - accumulated_allocation) *
1446 l_spread_curve(j);
1447 accumulated_allocation := j;
1448 j := j + 1;
1449 END IF;
1450 EXIT WHEN allocation < j;
1451 END LOOP;
1452 IF j <= l_spread_curve.COUNT() THEN
1453 l_global_percentage :=
1454 l_global_percentage +
1455 (allocation - accumulated_allocation) *
1456 l_spread_curve(j);
1457 END IF;
1458 */
1459 -- Reusing already calculated percentages
1460 l_global_percentage := 0;
1461 FOR k IN 1 .. l_start_period
1462 LOOP
1463 l_global_percentage := l_global_percentage + nvl(x_spread_amounts(k).percentage,0);
1464 END LOOP;
1465 END IF;
1466 l_stage := 121;
1467 print_msg(' '||l_stage||' after calculate percentage for global start date '||round(l_global_percentage,2));
1468
1469 -- Calculate amounts for each period
1470 IF p_global_start_date IS NOT NULL THEN
1471 x_spread_amounts(l_start_period).percentage :=
1472 nvl(l_global_percentage,0);
1473 END IF;
1474
1475 weight_sum := 0;
1476 FOR k IN l_start_period .. l_end_period
1477 LOOP
1478 weight_sum := weight_sum + x_spread_amounts(k).percentage;
1479 END LOOP;
1480
1481 --print_msg('Total Weigt_sum to spread proportionately['||weight_sum||']');
1482 FOR k IN 1 .. x_spread_amounts.COUNT()
1483 LOOP
1484
1485 FOR j IN 1 .. p_number_of_amounts --p_amounts.COUNT()
1486 LOOP
1487
1488 x_spread_amounts(k).number_of_amounts :=
1489 p_number_of_amounts;
1490
1491 IF k BETWEEN l_start_period AND l_end_period THEN
1492 --tmp_amounts(j) := p_amounts(j) *
1493 If NVL(weight_sum,0) <> 0 Then
1494 IF j = 1 THEN
1495 x_spread_amounts(k).amount1 := p_amount1 *
1496 x_spread_amounts(k).percentage / weight_sum;
1497 ELSIF j = 2 THEN
1498 x_spread_amounts(k).amount2 := p_amount2 *
1499 x_spread_amounts(k).percentage / weight_sum;
1500 ELSIF j = 3 THEN
1501 x_spread_amounts(k).amount3 := p_amount3 *
1502 x_spread_amounts(k).percentage / weight_sum;
1503 ELSIF j = 4 THEN
1504 x_spread_amounts(k).amount4 := p_amount4 *
1505 x_spread_amounts(k).percentage / weight_sum;
1506 ELSIF j = 5 THEN
1507 x_spread_amounts(k).amount5 := p_amount5 *
1508 x_spread_amounts(k).percentage / weight_sum;
1509 ELSIF j = 6 THEN
1510 x_spread_amounts(k).amount6 := p_amount6 *
1511 x_spread_amounts(k).percentage / weight_sum;
1512 ELSIF j = 7 THEN
1513 x_spread_amounts(k).amount7 := p_amount7 *
1514 x_spread_amounts(k).percentage / weight_sum;
1515 ELSIF j = 8 THEN
1516 x_spread_amounts(k).amount8 := p_amount8 *
1517 x_spread_amounts(k).percentage / weight_sum;
1518 ELSIF j = 9 THEN
1519 x_spread_amounts(k).amount9 := p_amount9 *
1520 x_spread_amounts(k).percentage / weight_sum;
1521 ELSIF j = 10 THEN
1522 x_spread_amounts(k).amount10 := p_amount10 *
1523 x_spread_amounts(k).percentage / weight_sum;
1524 END IF;
1525 End If;
1526 ELSE
1527 IF j = 1 THEN x_spread_amounts(k).amount1 := 0;
1528 ELSIF j = 2 THEN x_spread_amounts(k).amount2 := 0;
1529 ELSIF j = 3 THEN x_spread_amounts(k).amount3 := 0;
1530 ELSIF j = 4 THEN x_spread_amounts(k).amount4 := 0;
1531 ELSIF j = 5 THEN x_spread_amounts(k).amount5 := 0;
1532 ELSIF j = 6 THEN x_spread_amounts(k).amount6 := 0;
1533 ELSIF j = 7 THEN x_spread_amounts(k).amount7 := 0;
1534 ELSIF j = 8 THEN x_spread_amounts(k).amount8 := 0;
1535 ELSIF j = 9 THEN x_spread_amounts(k).amount9 := 0;
1536 ELSIF j = 10 THEN x_spread_amounts(k).amount10 := 0;
1537 END IF;
1538 --tmp_amounts(j) := 0;
1539 END IF;
1540
1541 END LOOP;
1542
1543
1544 END LOOP;
1545
1546 FOR k IN 1 .. x_spread_amounts.COUNT()
1547 LOOP
1548 -- make sure that amount1 is always passed with quantity
1549 x_spread_amounts(k).amount1 :=
1550 Round_Qty_Amts(G_rate_based_flag,'Y',G_curr_code,(x_spread_amounts(k).amount1));
1551 x_spread_amounts(k).amount2 :=
1552 Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,(x_spread_amounts(k).amount2));
1553 x_spread_amounts(k).amount3 :=
1554 Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,(x_spread_amounts(k).amount3));
1555 x_spread_amounts(k).amount4 :=
1556 Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,(x_spread_amounts(k).amount4));
1557 x_spread_amounts(k).amount5 :=
1558 Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,(x_spread_amounts(k).amount5));
1559 x_spread_amounts(k).amount6 :=
1560 Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,(x_spread_amounts(k).amount6));
1561 x_spread_amounts(k).amount7 :=
1562 Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,(x_spread_amounts(k).amount7));
1563 x_spread_amounts(k).amount8 :=
1564 Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,(x_spread_amounts(k).amount8));
1565 x_spread_amounts(k).amount9 :=
1566 Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,(x_spread_amounts(k).amount9));
1567 x_spread_amounts(k).amount10 :=
1568 Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,(x_spread_amounts(k).amount10));
1569 END LOOP;
1570 l_stage := 130;
1571 print_msg(' '||l_stage||' after calculate amounts');
1572
1573 -- Adjust the amounts for last period
1574 FOR k IN 1 .. p_number_of_amounts --p_amounts.COUNT()
1575 LOOP
1576
1577 amount_sum := 0;
1578 FOR j IN 1 .. x_spread_amounts.COUNT()
1579 LOOP
1580 IF k = 1 THEN
1581 amount_sum := amount_sum +
1582 nvl(x_spread_amounts(j).amount1,0);
1583 ELSIF k = 2 THEN
1584 amount_sum := amount_sum +
1585 nvl(x_spread_amounts(j).amount2,0);
1586 ELSIF k = 3 THEN
1587 amount_sum := amount_sum +
1588 nvl(x_spread_amounts(j).amount3,0);
1589 ELSIF k = 4 THEN
1590 amount_sum := amount_sum +
1591 nvl(x_spread_amounts(j).amount4,0);
1592 ELSIF k = 5 THEN
1593 amount_sum := amount_sum +
1594 nvl(x_spread_amounts(j).amount5,0);
1595 ELSIF k = 6 THEN
1596 amount_sum := amount_sum +
1597 nvl(x_spread_amounts(j).amount6,0);
1598 ELSIF k = 7 THEN
1599 amount_sum := amount_sum +
1600 nvl(x_spread_amounts(j).amount7,0);
1601 ELSIF k = 8 THEN
1602 amount_sum := amount_sum +
1603 nvl(x_spread_amounts(j).amount8,0);
1604 ELSIF k = 9 THEN
1605 amount_sum := amount_sum +
1606 nvl(x_spread_amounts(j).amount9,0);
1607 ELSIF k = 10 THEN
1608 amount_sum := amount_sum +
1609 nvl(x_spread_amounts(j).amount10,0);
1610 END IF;
1611 END LOOP;
1612
1613 /* Bug fix: 3961955 : The last period is getting updated with -ve amounts when spread curve weightage is zero
1614 * Logic: The following code is updating the last budget line with the rounding diff amount
1615 * Loop through the periodic budget lines in the reverse order. If the last period line is having zero weightage
1616 * then put the diff amounts in the previous period. If all the periods are zero weightage then put the
1617 * entire amounts/diff amounts in the Last period of the profile
1618 */
1619 IF k = 1 THEN
1620 IF (p_amount1 - amount_sum) <> 0 Then
1621 IF (p_amount1 - amount_sum) > 0 Then
1622 x_spread_amounts(l_end_period).amount1 := nvl(x_spread_amounts(l_end_period).amount1,0) +
1623 (p_amount1 - amount_sum);
1624 Else
1625 l_period_counter := l_end_period;
1626 FOR i IN REVERSE l_start_period .. l_end_period LOOP
1627 If x_spread_amounts.EXISTS(i) Then
1628 IF nvl(x_spread_amounts(i).amount1,0) <> 0 Then
1629 If (nvl(x_spread_amounts(i).amount1,0) + (p_amount1 - amount_sum)) > 0 Then
1630 x_spread_amounts(i).amount1 := nvl(x_spread_amounts(i).amount1,0) +
1631 (p_amount1 - amount_sum);
1632 x_spread_amounts(i).amount1 :=
1633 Round_Qty_Amts(G_rate_based_flag,'Y',G_curr_code,(x_spread_amounts(i).amount1));
1634 Exit;
1635 End If;
1636 End If;
1637 End If;
1638 l_period_counter := i;
1639 END LOOP;
1640 /* check all the periods are having zero weightage so put the amounts in the last period */
1641 If l_period_counter = l_start_period Then
1642 x_spread_amounts(l_end_period).amount1 := nvl(x_spread_amounts(l_end_period).amount1,0) +
1643 (p_amount1 - amount_sum);
1644 print_msg('Adding round diff makes all the lines -ve,so just put diff in first bucket');
1645 x_spread_amounts(l_end_period).amount1 :=
1646 Round_Qty_Amts(G_rate_based_flag,'Y',G_curr_code,(x_spread_amounts(l_end_period).amount1));
1647 End If;
1648 End If;
1649 End If;
1650 ELSIF k = 2 THEN
1651 IF (p_amount2 - amount_sum) <> 0 Then
1652 IF (p_amount2 - amount_sum) > 0 Then
1653 x_spread_amounts(l_end_period).amount2 := nvl(x_spread_amounts(l_end_period).amount2,0) +
1654 (p_amount2 - amount_sum);
1655 ELSE
1656 l_period_counter := l_end_period;
1657 FOR i IN REVERSE l_start_period .. l_end_period LOOP
1658 If x_spread_amounts.EXISTS(i) Then
1659 IF nvl(x_spread_amounts(i).amount2,0) <> 0 Then
1660 If(nvl(x_spread_amounts(i).amount2,0) + (p_amount2 - amount_sum)) > 0 Then
1661 x_spread_amounts(i).amount2 := nvl(x_spread_amounts(i).amount2,0) +
1662 (p_amount2 - amount_sum);
1663 Exit;
1664 End If;
1665 End If;
1666 End If;
1667 l_period_counter := i;
1668 END LOOP;
1669 /* check all the periods are having zero weightage so put the amounts in the last period */
1670 If l_period_counter = l_start_period Then
1671 x_spread_amounts(l_end_period).amount2 := nvl(x_spread_amounts(l_end_period).amount2,0) +
1672 (p_amount2 - amount_sum);
1673 End If;
1674 END IF;
1675 End If;
1676 ELSIF k = 3 THEN
1677 IF (p_amount3 - amount_sum) <> 0 Then
1678 IF (p_amount3 - amount_sum) > 0 Then
1679 x_spread_amounts(l_end_period).amount3 := nvl(x_spread_amounts(l_end_period).amount3,0) +
1680 (p_amount3 - amount_sum);
1681 ELSE
1682 l_period_counter := l_end_period;
1683 FOR i IN REVERSE l_start_period .. l_end_period LOOP
1684 If x_spread_amounts.EXISTS(i) Then
1685 IF nvl(x_spread_amounts(i).amount3,0) <> 0 Then
1686 If (nvl(x_spread_amounts(i).amount3,0)+ (p_amount3 - amount_sum)) > 0 Then
1687 x_spread_amounts(i).amount3 := nvl(x_spread_amounts(i).amount3,0) +
1688 (p_amount3 - amount_sum);
1689 Exit;
1690 End If;
1691 End If;
1692 End If;
1693 l_period_counter := i;
1694 END LOOP;
1695 /* check all the periods are having zero weightage so put the amounts in the last period */
1696 If l_period_counter = l_start_period Then
1697 x_spread_amounts(l_end_period).amount3 := nvl(x_spread_amounts(l_end_period).amount3,0) +
1698 (p_amount3 - amount_sum);
1699 End If;
1700 END IF;
1701 End If;
1702 ELSIF k = 4 THEN
1703 IF (p_amount4 - amount_sum) <> 0 Then
1704 IF (p_amount4 - amount_sum) > 0 Then
1705 x_spread_amounts(l_end_period).amount4 := nvl(x_spread_amounts(l_end_period).amount4,0) +
1706 (p_amount4 - amount_sum);
1707 ELSE
1708 l_period_counter := l_end_period;
1709 FOR i IN REVERSE l_start_period .. l_end_period LOOP
1710 If x_spread_amounts.EXISTS(i) Then
1711 IF nvl(x_spread_amounts(i).amount4,0) <> 0 Then
1712 If (nvl(x_spread_amounts(i).amount4,0) + (p_amount4 - amount_sum)) > 0 Then
1713 x_spread_amounts(i).amount4 := nvl(x_spread_amounts(i).amount4,0) +
1714 (p_amount4 - amount_sum);
1715 Exit;
1716 End If;
1717 End If;
1718 End If;
1719 l_period_counter := i;
1720 END LOOP;
1721 /* check all the periods are having zero weightage so put the amounts in the last period */
1722 If l_period_counter = l_start_period Then
1723 x_spread_amounts(l_end_period).amount4 := nvl(x_spread_amounts(l_end_period).amount4,0) +
1724 (p_amount4 - amount_sum);
1725 End If;
1726 END IF;
1727 End If;
1728 ELSIF k = 5 THEN
1729 IF (p_amount5 - amount_sum) <> 0 Then
1730 IF (p_amount5 - amount_sum) > 0 Then
1731 x_spread_amounts(l_end_period).amount5 := nvl(x_spread_amounts(l_end_period).amount5,0) +
1732 (p_amount5 - amount_sum);
1733 Else
1734 l_period_counter := l_end_period;
1735 FOR i IN REVERSE l_start_period .. l_end_period LOOP
1736 If x_spread_amounts.EXISTS(i) Then
1737 IF nvl(x_spread_amounts(i).amount5,0) <> 0 Then
1738 If (nvl(x_spread_amounts(i).amount5,0) + (p_amount5 - amount_sum)) > 0 Then
1739 x_spread_amounts(i).amount5 := nvl(x_spread_amounts(i).amount5,0) +
1740 (p_amount5 - amount_sum);
1741 Exit;
1742 End If;
1743 End If;
1744 End If;
1745 l_period_counter := i;
1746 END LOOP;
1747 /* check all the periods are having zero weightage so put the amounts in the last period */
1748 If l_period_counter = l_start_period Then
1749 x_spread_amounts(l_end_period).amount5 := nvl(x_spread_amounts(l_end_period).amount5,0) +
1750 (p_amount5 - amount_sum);
1751 End If;
1752 End If;
1753 End If;
1754 ELSIF k = 6 THEN
1755 IF (p_amount6 - amount_sum) <> 0 Then
1756 IF (p_amount6 - amount_sum) > 0 Then
1757 x_spread_amounts(l_end_period).amount6 := nvl(x_spread_amounts(l_end_period).amount6,0) +
1758 (p_amount6 - amount_sum);
1759 Else
1760 l_period_counter := l_end_period;
1761 FOR i IN REVERSE l_start_period .. l_end_period LOOP
1762 If x_spread_amounts.EXISTS(i) Then
1763 IF nvl(x_spread_amounts(i).amount6,0) <> 0 Then
1764 If (nvl(x_spread_amounts(i).amount6,0) + (p_amount6 - amount_sum)) > 0 Then
1765 x_spread_amounts(i).amount6 := nvl(x_spread_amounts(i).amount6,0) +
1766 (p_amount6 - amount_sum);
1767 Exit;
1768 End If;
1769 End If;
1770 End If;
1771 l_period_counter := i;
1772 END LOOP;
1773 /* check all the periods are having zero weightage so put the amounts in the last period */
1774 If l_period_counter = l_start_period Then
1775 x_spread_amounts(l_end_period).amount6 := nvl(x_spread_amounts(l_end_period).amount6,0) +
1776 (p_amount6 - amount_sum);
1777 End If;
1778 End If;
1779 End If;
1780 ELSIF k = 7 THEN
1781 IF (p_amount7 - amount_sum) <> 0 Then
1782 IF (p_amount7 - amount_sum) > 0 Then
1783 x_spread_amounts(l_end_period).amount7 := nvl(x_spread_amounts(l_end_period).amount7,0) +
1784 (p_amount7 - amount_sum);
1785 Else
1786 l_period_counter := l_end_period;
1787 FOR i IN REVERSE l_start_period .. l_end_period LOOP
1788 If x_spread_amounts.EXISTS(i) Then
1789 IF nvl(x_spread_amounts(i).amount7,0) <> 0 Then
1790 If (nvl(x_spread_amounts(i).amount7,0) + (p_amount7 - amount_sum)) > 0 Then
1791 x_spread_amounts(i).amount7 := nvl(x_spread_amounts(i).amount7,0) +
1792 (p_amount7 - amount_sum);
1793 Exit;
1794 End If;
1795 End If;
1796 End If;
1797 l_period_counter := i;
1798 END LOOP;
1799 /* check all the periods are having zero weightage so put the amounts in the last period */
1800 If l_period_counter = l_start_period Then
1801 x_spread_amounts(l_end_period).amount7 := nvl(x_spread_amounts(l_end_period).amount7,0) +
1802 (p_amount7 - amount_sum);
1803 End If;
1804 End If;
1805 End If;
1806 ELSIF k = 8 THEN
1807 IF (p_amount8 - amount_sum) <> 0 Then
1808 IF (p_amount8 - amount_sum) > 0 Then
1809 x_spread_amounts(l_end_period).amount8 := nvl(x_spread_amounts(l_end_period).amount8,0) +
1810 (p_amount8 - amount_sum);
1811 Else
1812 l_period_counter := l_end_period;
1813 FOR i IN REVERSE l_start_period .. l_end_period LOOP
1814 If x_spread_amounts.EXISTS(i) Then
1815 IF nvl(x_spread_amounts(i).amount8,0) <> 0 Then
1816 If (nvl(x_spread_amounts(i).amount8,0) + (p_amount8 - amount_sum)) > 0 Then
1817 x_spread_amounts(i).amount8 := nvl(x_spread_amounts(i).amount8,0) +
1818 (p_amount8 - amount_sum);
1819 Exit;
1820 End If;
1821 End If;
1822 End If;
1823 l_period_counter := i;
1824 END LOOP;
1825 /* check all the periods are having zero weightage so put the amounts in the last period */
1826 If l_period_counter = l_start_period Then
1827 x_spread_amounts(l_end_period).amount8 := nvl(x_spread_amounts(l_end_period).amount8,0) +
1828 (p_amount8 - amount_sum);
1829 End If;
1830 End If;
1831 End If;
1832 ELSIF k = 9 THEN
1833 IF (p_amount9 - amount_sum) <> 0 Then
1834 IF (p_amount9 - amount_sum) > 0 Then
1835 x_spread_amounts(l_end_period).amount9 := nvl(x_spread_amounts(l_end_period).amount9,0) +
1836 (p_amount9 - amount_sum);
1837 Else
1838 l_period_counter := l_end_period;
1839 FOR i IN REVERSE l_start_period .. l_end_period LOOP
1840 If x_spread_amounts.EXISTS(i) Then
1841 IF nvl(x_spread_amounts(i).amount9,0) <> 0 Then
1842 If (nvl(x_spread_amounts(i).amount9,0) + (p_amount9 - amount_sum)) > 0 Then
1843 x_spread_amounts(i).amount9 := nvl(x_spread_amounts(i).amount9,0) +
1844 (p_amount9 - amount_sum);
1845 Exit;
1846 End If;
1847 End If;
1848 End If;
1849 l_period_counter := i;
1850 END LOOP;
1851 /* check all the periods are having zero weightage so put the amounts in the last period */
1852 If l_period_counter = l_start_period Then
1853 x_spread_amounts(l_end_period).amount9 := nvl(x_spread_amounts(l_end_period).amount9,0) +
1854 (p_amount9 - amount_sum);
1855 End If;
1856 End If;
1857 End If;
1858 ELSIF k = 10 THEN
1859 IF (p_amount10 - amount_sum) <> 0 Then
1860 IF (p_amount10 - amount_sum) > 0 Then
1861 x_spread_amounts(l_end_period).amount10 := nvl(x_spread_amounts(l_end_period).amount10,0) +
1862 (p_amount10 - amount_sum);
1863 Else
1864 l_period_counter := l_end_period;
1865 FOR i IN REVERSE l_start_period .. l_end_period LOOP
1866 If x_spread_amounts.EXISTS(i) Then
1867 IF nvl(x_spread_amounts(i).amount10,0) <> 0 Then
1868 If (nvl(x_spread_amounts(i).amount10,0) + (p_amount10 - amount_sum)) > 0 Then
1869 x_spread_amounts(i).amount10 := nvl(x_spread_amounts(i).amount10,0) +
1870 (p_amount10 - amount_sum);
1871 Exit;
1872 End If;
1873 End If;
1874 End If;
1875 l_period_counter := i;
1876 END LOOP;
1877 /* check all the periods are having zero weightage so put the amounts in the last period */
1878 If l_period_counter = l_start_period Then
1879 x_spread_amounts(l_end_period).amount10 := nvl(x_spread_amounts(l_end_period).amount10,0) +
1880 (p_amount10 - amount_sum);
1881 End If;
1882 End If;
1883 End If;
1884 END IF;
1885
1886 END LOOP;
1887
1888 pa_debug.g_err_stage := 'Leaving PA_FP_SPREAD_AMTS_PKG.spread';
1889 IF P_PA_DEBUG_MODE = 'Y' THEN
1890 pa_debug.write('spread: '||g_module_name,
1891 pa_debug.g_err_stage,
1892 3);
1893 END IF;
1894 If p_pa_debug_mode = 'Y' Then
1895 pa_debug.reset_err_stack;
1896 End If;
1897 l_stage := 140;
1898 print_msg(' '||l_stage||' leave spread()');
1899
1900 EXCEPTION
1901
1902 WHEN OTHERS THEN
1903 print_msg('Unexpected error in Spread['||sqlcode||sqlerrm||']');
1904 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1905 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_FP_SPREAD_AMTS_PKG',
1906 p_procedure_name => 'spread');
1907 If p_pa_debug_mode = 'Y' Then
1908 pa_debug.reset_err_stack;
1909 End If;
1910 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1911 END spread;
1912
1913 PROCEDURE get_options (
1914 p_budget_version_id IN pa_budget_versions.
1915 budget_version_id%TYPE,
1916 x_period_set_name OUT NOCOPY gl_sets_of_books.
1917 period_set_name%TYPE,
1918 x_accounted_period_type OUT NOCOPY gl_sets_of_books.
1919 accounted_period_type%TYPE,
1920 x_pa_period_type OUT NOCOPY pa_implementations_all.
1921 pa_period_type%TYPE,
1922 x_time_phase_code OUT NOCOPY pa_proj_fp_options.
1923 all_time_phased_code%TYPE,
1924 x_return_status OUT NOCOPY VARCHAR2,
1925 x_msg_count OUT NOCOPY NUMBER,
1926 x_msg_data OUT NOCOPY VARCHAR2) IS
1927
1928 l_msg_count NUMBER := 0;
1929 l_data VARCHAR2(2000);
1930 l_msg_data VARCHAR2(2000);
1931 l_msg_index_out NUMBER;
1932 l_debug_mode VARCHAR2(30);
1933
1934
1935
1936 l_stage INTEGER;
1937
1938 CURSOR get_name_and_type_csr IS
1939 SELECT --gsb.period_set_name /*Start changes for bug 6156873*/
1940 decode(decode(pbv.version_type,
1941 'COST',ppfo.cost_time_phased_code,
1942 'REVENUE',ppfo.revenue_time_phased_code,
1943 ppfo.all_time_phased_code)
1944 ,'P', pia.period_set_name
1945 ,gsb.period_set_name) period_set_name /*End changes for bug 6156873*/
1946 ,gsb.accounted_period_type
1947 ,pia.pa_period_type
1948 ,decode(pbv.version_type,
1949 'COST',ppfo.cost_time_phased_code,
1950 'REVENUE',ppfo.revenue_time_phased_code,
1951 ppfo.all_time_phased_code) time_phase_code
1952 FROM gl_sets_of_books gsb
1953 ,pa_implementations_all pia
1954 ,pa_projects_all ppa
1955 ,pa_budget_versions pbv
1956 ,pa_proj_fp_options ppfo
1957 WHERE ppa.project_id = pbv.project_id
1958 AND pbv.budget_version_id = ppfo.fin_plan_version_id
1959 /* MOAC Changes: AND nvl(ppa.org_id,-99) = nvl(pia.org_id,-99) */
1960 AND ppa.org_id = pia.org_id
1961 AND gsb.set_of_books_id = pia.set_of_books_id
1962 AND pbv.budget_version_id = p_budget_version_id;
1963
1964
1965 get_name_and_type_rec get_name_and_type_csr%ROWTYPE;
1966
1967 BEGIN
1968 l_stage := 200;
1969 print_msg(' '||l_stage||' enter get_options()');
1970
1971 x_return_status := FND_API.G_RET_STS_SUCCESS;
1972 If p_pa_debug_mode = 'Y' Then
1973 pa_debug.init_err_stack( 'PA_FP_SPREAD_AMTS_PKG.get_options');
1974 pa_debug.set_process('PLSQL', 'LOG', p_pa_debug_mode);
1975 End If;
1976
1977 l_stage := 205;
1978 print_msg(l_stage||'input parameters:p_budget_version_id=> '||p_budget_version_id);
1979
1980
1981 -- get set name, period type and time phase
1982 get_name_and_type_rec := NULL;
1983 OPEN get_name_and_type_csr;
1984 FETCH get_name_and_type_csr INTO get_name_and_type_rec;
1985
1986 IF get_name_and_type_csr%NOTFOUND THEN
1987
1988 CLOSE get_name_and_type_csr;
1989
1990 x_return_status := FND_API.G_RET_STS_ERROR;
1991 x_msg_data := 'PA_FP_CANNOT_GET_TIME_PHASE';
1992 If p_pa_debug_mode = 'Y' Then
1993 pa_debug.reset_err_stack;
1994 End If;
1995 l_stage := 206;
1996 print_msg(' '||'cannot found name type');
1997 RETURN;
1998 END IF;
1999
2000 CLOSE get_name_and_type_csr;
2001
2002 x_period_set_name :=
2003 get_name_and_type_rec.period_set_name;
2004 x_accounted_period_type :=
2005 get_name_and_type_rec.accounted_period_type;
2006 x_pa_period_type :=
2007 get_name_and_type_rec.pa_period_type;
2008 x_time_phase_code :=
2009 get_name_and_type_rec.time_phase_code;
2010 l_stage := 230;
2011 If p_pa_debug_mode = 'Y' Then
2012 print_msg(' '||l_stage||' after get set name,period type,time phase');
2013 print_msg(' '||'period_set_name => '||get_name_and_type_rec.period_set_name);
2014 print_msg(' '||'accounted_period_type => '||get_name_and_type_rec.accounted_period_type);
2015 print_msg(' '||'pa_period_type => '||get_name_and_type_rec.pa_period_type);
2016 print_msg(' '||'time_phase_code => '||get_name_and_type_rec.time_phase_code);
2017 End If;
2018
2019 /* reset error stack */
2020 If p_pa_debug_mode = 'Y' Then
2021 pa_debug.reset_err_stack;
2022 End If;
2023 l_stage := 240;
2024 print_msg(' '||l_stage||' leave get options');
2025
2026 EXCEPTION
2027
2028 WHEN OTHERS THEN
2029
2030 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2031 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_FP_SPREAD_AMTS_PKG',
2032 p_procedure_name => 'get_options');
2033 If p_pa_debug_mode = 'Y' Then
2034 pa_debug.reset_err_stack;
2035 End If;
2036 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2037
2038 END get_options;
2039
2040 PROCEDURE get_periods (
2041 p_start_date IN pa_budget_lines.start_date%TYPE,
2042 p_end_date IN pa_budget_lines.end_date%TYPE,
2043 p_period_set_name IN gl_sets_of_books.
2044 period_set_name%TYPE,
2045 p_accounted_period_type IN gl_sets_of_books.
2046 accounted_period_type%TYPE,
2047 p_pa_period_type IN pa_implementations_all.
2048 pa_period_type%TYPE,
2049 p_time_phase_code IN pa_proj_fp_options.
2050 all_time_phased_code%TYPE,
2051 x_spread_amounts OUT NOCOPY spread_table_type,
2052 x_return_status OUT NOCOPY VARCHAR2,
2053 x_msg_count OUT NOCOPY NUMBER,
2054 x_msg_data OUT NOCOPY VARCHAR2) IS
2055
2056 l_msg_count NUMBER := 0;
2057 l_data VARCHAR2(2000);
2058 l_msg_data VARCHAR2(2000);
2059 l_msg_index_out NUMBER;
2060 l_debug_mode VARCHAR2(30);
2061
2062 l_period_set_name gl_sets_of_books.period_set_name%TYPE;
2063 l_accounted_period_type gl_sets_of_books.accounted_period_type%TYPE;
2064 l_pa_period_type pa_implementations_all.pa_period_type%TYPE;
2065 l_time_phase_code pa_proj_fp_options.cost_time_phased_code%TYPE;
2066 l_start_date DATE;
2067 l_end_date DATE;
2068
2069 tmp_rec spread_record_type;
2070 n INTEGER;
2071
2072 l_stage INTEGER;
2073
2074
2075 /** Explain plan for the tuned sql
2076 ================
2077 EXPLAIN PLAN IS:
2078 ================
2079 1:SELECT STATEMENT :(cost=8,rows=2)
2080 2:SORT ORDER BY :(cost=8,rows=2)
2081 3:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=2)
2082 4:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=2)
2083 **/
2084 CURSOR get_gl_periods_csr IS
2085 SELECT START_DATE, END_DATE, PERIOD_NAME
2086 FROM gl_periods gp
2087 WHERE gp.period_set_name = l_period_set_name
2088 AND gp.period_type = decode(l_time_phase_code,'G',l_accounted_period_type,'P',l_pa_period_type)
2089 AND gp.adjustment_period_flag = 'N'
2090 AND gp.start_date <= l_end_date -- plan end date
2091 AND gp.end_date >= l_start_date -- planning start date
2092 ORDER BY gp.start_date;
2093
2094 /** Performance Fix: SQL modified to avoid hitting MIN and MAX again and again
2095 ================
2096 EXPLAIN PLAN IS:
2097 ================
2098 1:SELECT STATEMENT :(cost=8,rows=1)
2099 2:FILTER :(cost=,rows=)
2100 3:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=8,rows=1)
2101 4:INDEX RANGE SCAN GL_PERIODS_N1 :(cost=2,rows=1)
2102 5:SORT AGGREGATE :(cost=,rows=1)
2103 6:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=4)
2104 7:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=4)
2105 3:SORT AGGREGATE :(cost=,rows=1)
2106 4:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=5)
2107 5:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=5)
2108 CURSOR get_gl_periods_csr IS
2109 SELECT START_DATE, END_DATE, PERIOD_NAME
2110 FROM gl_periods gp
2111 WHERE gp.period_set_name = l_period_set_name
2112 AND gp.period_type = decode(l_time_phase_code,'G',l_accounted_period_type,'P',l_pa_period_type)
2113 AND gp.start_date >=
2114 (SELECT MIN(start_date)
2115 FROM gl_periods
2116 WHERE end_date >= l_start_date
2117 AND period_set_name = l_period_set_name
2118 AND period_type =
2119 decode(l_time_phase_code,'G',
2120 l_accounted_period_type,
2121 'P',l_pa_period_type)
2122 AND adjustment_period_flag = 'N')
2123 AND gp.end_date <=
2124 (SELECT MAX(end_date)
2125 FROM gl_periods
2126 WHERE start_date <= l_end_date
2127 AND period_set_name = l_period_set_name
2128 AND period_type =
2129 decode(l_time_phase_code,'G',
2130 l_accounted_period_type,
2131 'P',l_pa_period_type)
2132 AND adjustment_period_flag = 'N')
2133 AND gp.adjustment_period_flag = 'N'
2134 ORDER BY gp.start_date;
2135 *****End of Performance fix **/
2136
2137 BEGIN
2138 l_stage := 250;
2139 print_msg(' '||l_stage||' enter get_periods()');
2140
2141 x_return_status := FND_API.G_RET_STS_SUCCESS;
2142 If p_pa_debug_mode = 'Y' Then
2143 pa_debug.init_err_stack('PA_FP_SPREAD_AMTS_PKG.get_periods');
2144 pa_debug.set_process('PLSQL', 'LOG', p_pa_debug_mode);
2145 End If;
2146
2147 l_stage := 255;
2148 print_msg(' '||'input parameters:');
2149 print_msg(' '||'p_start/end_date => '||p_start_date||'/'||p_end_date);
2150
2151 --Validation
2152
2153 l_stage := 260;
2154 print_msg(' '||l_stage||' before validate p_start/end_date');
2155 -- p_start_date must less than p_end_date
2156 IF p_start_date > p_end_date THEN
2157
2158 x_return_status := FND_API.G_RET_STS_ERROR;
2159 x_msg_data := 'PA_FP_START_END_DATE_OVERLAP';
2160 If p_pa_debug_mode = 'Y' Then
2161 pa_debug.reset_err_stack;
2162 End If;
2163 RETURN;
2164 END IF;
2165 l_stage := 270;
2166 print_msg(' '||l_stage||' after validation');
2167
2168
2169 l_period_set_name := p_period_set_name;
2170 l_accounted_period_type := p_accounted_period_type;
2171 l_pa_period_type := p_pa_period_type;
2172 l_time_phase_code := p_time_phase_code;
2173 l_stage := 280;
2174 If p_pa_debug_mode = 'Y' Then
2175 print_msg(' '||l_stage||' after assign set name,period type,time phase');
2176 print_msg(' '||'period_set_name => '||l_period_set_name);
2177 print_msg(' '||'accounted_period_type => '||l_accounted_period_type);
2178 print_msg(' '||'pa_period_type => '||l_pa_period_type);
2179 print_msg(' '||'time_phase_code => '||l_time_phase_code);
2180 End If;
2181
2182
2183 -- Get periods from gl_periods
2184
2185 l_start_date := p_start_date;
2186 l_end_date := p_end_date;
2187 x_spread_amounts := spread_table_type();
2188 n := 0;
2189 FOR rec IN get_gl_periods_csr
2190 LOOP
2191 --print_msg('inside get_gl_periods_csr for SD['||rec.start_date||']');
2192 n := n + 1;
2193 x_spread_amounts.EXTEND();
2194 tmp_rec.start_date := rec.start_date;
2195 tmp_rec.end_date := rec.end_date;
2196 tmp_rec.period_name := rec.period_name;
2197 tmp_rec.actual_days := 0;
2198 x_spread_amounts(n) := tmp_rec;
2199 END LOOP;
2200
2201 IF n = 0 AND (l_time_phase_code = 'G' OR l_time_phase_code = 'P') THEN
2202 x_return_status := FND_API.G_RET_STS_ERROR;
2203 x_msg_data := 'PA_FP_PERIODS_IS_NULL';
2204 If p_pa_debug_mode = 'Y' Then
2205 pa_debug.reset_err_stack;
2206 End If;
2207 RETURN;
2208 END IF;
2209 If p_pa_debug_mode = 'Y' Then
2210 pa_debug.reset_err_stack;
2211 End If;
2212 l_stage := 290;
2213 print_msg(' '||l_stage||' leave get period');
2214
2215 EXCEPTION
2216
2217 WHEN OTHERS THEN
2218
2219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2220 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_FP_SPREAD_AMTS_PKG',
2221 p_procedure_name => 'get_periods');
2222 If p_pa_debug_mode = 'Y' Then
2223 pa_debug.reset_err_stack;
2224 End If;
2225 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2226
2227 END get_periods;
2228
2229
2230 PROCEDURE spread_amounts
2231 ( p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
2232 ,x_return_status OUT NOCOPY VARCHAR2
2233 ,x_msg_count OUT NOCOPY NUMBER
2234 ,x_msg_data OUT NOCOPY VARCHAR2) IS
2235
2236 l_msg_count NUMBER := 0;
2237 l_data VARCHAR2(2000);
2238 l_msg_data VARCHAR2(2000);
2239 l_msg_index_out NUMBER;
2240 l_debug_mode VARCHAR2(30);
2241
2242 v_return_status VARCHAR2(3);
2243 v_msg_count NUMBER;
2244 v_msg_data VARCHAR2(2000);
2245
2246 l_stage INTEGER;
2247
2248 l_err_msg VARCHAR2(2000);
2249
2250 l_project_name pa_projects_all.name%TYPE;
2251 l_task_name pa_proj_elements.name%TYPE;
2252 l_resource_name pa_resource_list_members.alias%TYPE;
2253 l_proj_curr_cd pa_projects_all.project_currency_code%TYPE;
2254 l_projfunc_curr_cd pa_projects_all.projfunc_currency_code%TYPE;
2255
2256 v_spread_amounts spread_table_type;
2257 v_spread_curve spread_curve_type;
2258 v_start_end_date start_end_date_table_type;
2259 v_start_end start_end_date_record_type;
2260 i INTEGER;
2261 bl_exist BOOLEAN;
2262
2263 --l_time_phase_code pa_proj_fp_options.all_time_phased_code%TYPE;
2264 l_line_start INTEGER;
2265 l_line_end INTEGER;
2266 l_line_start_date DATE;
2267 l_line_end_date DATE;
2268 l_plan_start_date DATE;
2269 l_plan_end_date DATE;
2270 l_budget_line_time_phase_count INTEGER;
2271 l_fixed_date DATE;
2272 l_fixed_date_period_count INTEGER;
2273 l_last_budget_line_id pa_budget_lines.budget_line_id%TYPE;
2274 l_spread_curve_id pa_spread_curves_b.spread_curve_id%TYPE;
2275 v_budget_line_id pa_budget_lines.budget_line_id%TYPE;
2276
2277 v_resource_assignment_id pa_budget_lines.resource_assignment_id%TYPE;
2278 v_txn_currency_code pa_budget_lines.txn_currency_code%TYPE;
2279
2280 v_total_quantity pa_budget_lines.quantity%TYPE;
2281 v_total_raw_cost pa_budget_lines.raw_cost%TYPE;
2282 v_total_burdened_cost pa_budget_lines.burdened_cost%TYPE;
2283 v_total_revenue pa_budget_lines.revenue%TYPE;
2284
2285 l_sum_txn_quantity pa_budget_lines.quantity%TYPE;
2286 l_avg_raw_cost_rate pa_budget_lines.txn_standard_cost_rate%TYPE;
2287 l_avg_raw_cost_rate_override pa_budget_lines.txn_cost_rate_override%TYPE;
2288 l_sum_txn_raw_cost pa_budget_lines.raw_cost%TYPE;
2289 l_avg_burden_cost_rate pa_budget_lines.burden_cost_rate%TYPE;
2290 l_avg_burden_cost_rate_ovrid pa_budget_lines.
2291 burden_cost_rate_override%TYPE;
2292 l_sum_txn_burdened_cost pa_budget_lines.burdened_cost%TYPE;
2293 l_avg_bill_rate pa_budget_lines.txn_standard_bill_rate%TYPE;
2294 l_avg_bill_rate_override pa_budget_lines.txn_bill_rate_override%TYPE;
2295 l_sum_txn_revenue pa_budget_lines.revenue%TYPE;
2296
2297 tmp_quantity NUMBER;
2298 tmp_txn_raw_cost NUMBER;
2299 tmp_txn_burdened_cost NUMBER;
2300 tmp_txn_revenue NUMBER;
2301
2302 l_quantity pa_budget_lines.quantity%TYPE;
2303 l_txn_raw_cost pa_budget_lines.raw_cost%TYPE;
2304 l_txn_burdened_cost pa_budget_lines.burdened_cost%TYPE;
2305 l_txn_revenue pa_budget_lines.revenue%TYPE;
2306
2307 l_g_start_date DATE;
2308 l_g_init_quantity pa_budget_lines.init_quantity%TYPE;
2309 l_g_txn_init_raw_cost pa_budget_lines.txn_init_raw_cost%TYPE;
2310 l_g_txn_init_burdened_cost pa_budget_lines.txn_init_burdened_cost%TYPE;
2311 l_g_txn_init_revenue pa_budget_lines.txn_init_revenue%TYPE;
2312 l_g_sum_etc_quantity pa_budget_lines.init_quantity%TYPE;
2313 l_g_sum_txn_etc_raw_cost pa_budget_lines.txn_init_raw_cost%TYPE;
2314 l_g_sum_txn_etc_burdened_cost pa_budget_lines.txn_init_burdened_cost%TYPE;
2315 l_g_sum_txn_etc_revenue pa_budget_lines.txn_init_revenue%TYPE;
2316 l_g_bl_init_count INTEGER;
2317
2318 l_g_bl_count INTEGER;
2319 l_g_sum_txn_quantity pa_budget_lines.quantity%TYPE;
2320 l_g_sum_txn_raw_cost pa_budget_lines.txn_raw_cost%TYPE;
2321 l_g_sum_txn_burdened_cost pa_budget_lines.txn_burdened_cost%TYPE;
2322 l_g_sum_txn_revenue pa_budget_lines.txn_revenue%TYPE;
2323
2324 l_txn_quantity_addl pa_fp_res_assignments_tmp.
2325 TXN_PLAN_QUANTITY%TYPE;
2326 l_txn_raw_cost_addl pa_fp_res_assignments_tmp.
2327 TXN_RAW_COST%TYPE;
2328 l_txn_burdened_cost_addl pa_fp_res_assignments_tmp.
2329 TXN_BURDENED_COST%TYPE;
2330 l_txn_revenue_addl pa_fp_res_assignments_tmp.
2331 TXN_REVENUE%TYPE;
2332
2333 l_bl_count INTEGER;
2334 l_dummy_count INTEGER;
2335 l_bl_line_id Number;
2336 l_dummy_bl_id Number;
2337
2338 l_period_set_name gl_sets_of_books.period_set_name%TYPE;
2339 l_accounted_period_type gl_sets_of_books.accounted_period_type%TYPE;
2340 l_pa_period_type pa_implementations_all.pa_period_type%TYPE;
2341 l_time_phase_code pa_proj_fp_options.cost_time_phased_code%TYPE;
2342
2343 /* bug fix:5726773 */
2344 l_neg_qty_er_flag VARCHAR2(1);
2345
2346 /* This cursor is used for fixed date spread curve */
2347 CURSOR cur_spFixDateBdgtLines IS
2348 SELECT RESOURCE_ASSIGNMENT_ID,
2349 START_DATE,
2350 END_DATE,
2351 PERIOD_NAME,
2352 QUANTITY,
2353 TXN_RAW_COST,
2354 TXN_BURDENED_COST,
2355 TXN_REVENUE,
2356 INIT_QUANTITY,
2357 TXN_INIT_RAW_COST,
2358 TXN_INIT_BURDENED_COST,
2359 TXN_INIT_REVENUE,
2360 TXN_CURRENCY_CODE,
2361 BUDGET_LINE_ID,
2362 BUDGET_VERSION_ID
2363 FROM PA_BUDGET_LINES
2364 WHERE RESOURCE_ASSIGNMENT_ID = v_resource_assignment_id
2365 AND TXN_CURRENCY_CODE = v_txn_currency_code
2366 AND START_DATE BETWEEN l_line_start_date AND l_line_end_date
2367 AND END_DATE BETWEEN l_line_start_date AND l_line_end_date
2368 AND PERIOD_NAME IS NOT NULL
2369 ORDER BY START_DATE;
2370
2371
2372 /* This cursor is used for Existing Line Distributioin Method */
2373 CURSOR cur_ExistBdgtLines(p_resAsgnId Number
2374 ,p_txn_cur_code Varchar2
2375 ,p_line_start_date Date
2376 ,p_line_end_date Date ) IS
2377 SELECT RESOURCE_ASSIGNMENT_ID,
2378 START_DATE,
2379 END_DATE,
2380 PERIOD_NAME,
2381 QUANTITY,
2382 TXN_RAW_COST,
2383 TXN_BURDENED_COST,
2384 TXN_REVENUE,
2385 INIT_QUANTITY,
2386 TXN_INIT_RAW_COST,
2387 TXN_INIT_BURDENED_COST,
2388 TXN_INIT_REVENUE,
2389 TXN_CURRENCY_CODE,
2390 BUDGET_LINE_ID,
2391 BUDGET_VERSION_ID
2392 FROM PA_BUDGET_LINES
2393 WHERE RESOURCE_ASSIGNMENT_ID = p_resAsgnId
2394 AND TXN_CURRENCY_CODE = p_txn_cur_code
2395 AND START_DATE BETWEEN p_line_start_date AND p_line_end_date
2396 AND END_DATE BETWEEN p_line_start_date AND p_line_end_date
2397 AND PERIOD_NAME IS NOT NULL
2398 ORDER BY START_DATE;
2399
2400 /* This Cursor is used for Non-Time phase budgets */
2401 CURSOR budget_line_time_phase_csr IS
2402 SELECT BUDGET_LINE_ID
2403 FROM PA_BUDGET_LINES
2404 WHERE RESOURCE_ASSIGNMENT_ID = v_resource_assignment_id
2405 AND TXN_CURRENCY_CODE = v_txn_currency_code
2406 --AND START_DATE = l_plan_start_date bug 6339811
2407 --AND END_DATE = l_plan_end_date bug 6339811
2408 AND PERIOD_NAME IS NULL;
2409
2410 budget_line_time_phase_rec budget_line_time_phase_csr%ROWTYPE;
2411
2412 -- notes: for periodic page
2413 CURSOR resource_assignment_csr IS
2414 SELECT RESOURCE_ASSIGNMENT_ID
2415 ,BUDGET_VERSION_ID
2416 ,PROJECT_ID
2417 ,TASK_ID
2418 ,RESOURCE_LIST_MEMBER_ID
2419 ,PLANNING_START_DATE
2420 ,PLANNING_END_DATE
2421 ,SPREAD_CURVE_ID
2422 ,SP_FIXED_DATE
2423 ,TXN_CURRENCY_CODE
2424 ,TXN_CURRENCY_CODE_OVERRIDE
2425 ,PROJECT_CURRENCY_CODE
2426 ,PROJFUNC_CURRENCY_CODE
2427 ,TXN_REVENUE
2428 ,TXN_REVENUE_ADDL
2429 ,TXN_RAW_COST
2430 ,TXN_RAW_COST_ADDL
2431 ,TXN_BURDENED_COST
2432 ,TXN_BURDENED_COST_ADDL
2433 ,TXN_PLAN_QUANTITY
2434 ,TXN_PLAN_QUANTITY_ADDL
2435 ,LINE_START_DATE
2436 ,LINE_END_DATE
2437 ,SOURCE_CONTEXT
2438 ,RAW_COST_RATE
2439 ,RW_COST_RATE_OVERRIDE
2440 ,BURDEN_COST_RATE
2441 ,BURDEN_COST_RATE_OVERRIDE
2442 ,BILL_RATE
2443 ,BILL_RATE_OVERRIDE
2444 ,RATE_BASED_FLAG
2445 ,SPREAD_AMOUNTS_FLAG
2446 ,INIT_QUANTITY
2447 ,TXN_INIT_RAW_COST
2448 ,TXN_INIT_BURDENED_COST
2449 ,TXN_INIT_REVENUE
2450 /* Bug fix:5726773 : Added the following columns to store the negative quantity/amt change flags*/
2451 ,NVL(NEG_QUANTITY_CHANGE_FLAG,'N') neg_Qty_Change_flag
2452 ,NVL(NEG_RAWCOST_CHANGE_FLAG,'N') neg_RawCst_Change_flag
2453 ,NVL(NEG_BURDEN_CHANGE_FALG,'N') neg_BurdCst_Change_flag
2454 ,NVL(NEG_REVENUE_CHANGE_FLAG,'N') neg_rev_Change_flag
2455 FROM PA_FP_RES_ASSIGNMENTS_TMP tmp
2456 WHERE tmp.BUDGET_VERSION_ID = p_budget_version_id
2457 AND (NVL(tmp.TXN_PLAN_QUANTITY_ADDL,0) <> 0
2458 OR NVL(tmp.TXN_RAW_COST_ADDL,0) <> 0
2459 OR NVL(tmp.TXN_BURDENED_COST_ADDL,0) <> 0
2460 OR NVL(tmp.TXN_REVENUE_ADDL,0) <> 0
2461 );
2462 /* Now the spread is called in bulk mode
2463 AND RESOURCE_ASSIGNMENT_ID = p_res_assignment_id
2464 AND TXN_CURRENCY_CODE = p_txn_currency_code
2465 AND ((p_line_start_date IS NULL and p_line_end_date IS NULL)
2466 OR
2467 (p_line_start_date IS NOT NULL and p_line_end_date IS NOT NULL
2468 and LINE_START_DATE = p_line_start_date
2469 and LINE_END_DATE = p_line_end_date)
2470 );
2471 */
2472
2473 resource_assignment_rec resource_assignment_rec_type;
2474
2475 CURSOR spread_curve_csr IS
2476 SELECT POINT1,
2477 POINT2,
2478 POINT3,
2479 POINT4,
2480 POINT5,
2481 POINT6,
2482 POINT7,
2483 POINT8,
2484 POINT9,
2485 POINT10,
2486 SPREAD_CURVE_CODE
2487 FROM PA_SPREAD_CURVES_B
2488 WHERE SPREAD_CURVE_ID = l_spread_curve_id;
2489 spread_curve_b_rec spread_curve_csr%ROWTYPE;
2490
2491
2492 CURSOR get_line_info (p_resource_assignment_id IN NUMBER) IS
2493 SELECT ppa.name project_name
2494 ,pt.name task_name
2495 ,prl.alias resource_name
2496 FROM pa_projects_all ppa
2497 ,pa_proj_elements pt
2498 ,pa_resource_list_members prl
2499 ,pa_resource_assignments pra
2500 WHERE pra.resource_assignment_id = p_resource_assignment_id
2501 AND ppa.project_id = pra.project_id
2502 AND pt.proj_element_id(+) = pra.task_id
2503 AND prl.resource_list_member_id = pra.resource_list_member_id;
2504
2505 /* performance bug fix: 4100256 */
2506 CURSOR bl_details(p_resAsgnId Number
2507 ,p_txn_cur_code Varchar2
2508 ,p_start_date Date
2509 ,p_end_date Date
2510 ,p_source_context Varchar2) IS
2511 SELECT sum(bl.quantity)
2512 ,sum(bl.txn_raw_cost)
2513 ,sum(bl.txn_burdened_cost)
2514 ,sum(bl.txn_revenue)
2515 ,min(bl.budget_line_id)
2516 ,decode(min(bl.budget_line_id),NULL,0,1) NumOfBudgetLines
2517 ,sum(NVL(bl.quantity,0)-NVL(bl.init_quantity,0)) Etc_Quantity
2518 FROM pa_budget_lines bl
2519 WHERE bl.resource_assignment_id = p_resAsgnId
2520 AND bl.txn_currency_code = p_txn_cur_code
2521 AND bl.START_DATE BETWEEN p_start_date AND p_end_date
2522 AND bl.END_DATE BETWEEN p_start_date AND p_end_date ;
2523
2524 SPREAD_AMOUNTS_EXCEPTION EXCEPTION;
2525 SKIP_EXCEPTION EXCEPTION;
2526
2527 l_sprd_exception_count Number :=0;
2528 l_sp_fixed_qty Number;
2529 l_sp_fixed_cost Number;
2530 l_sp_fixed_burden Number;
2531 l_sp_fixed_revenue Number;
2532 v_bl_total_quantity Number := 0;
2533 v_bl_total_raw_cost Number := 0;
2534 v_bl_total_burdened_cost Number :=0;
2535 v_bl_total_revenue Number := 0;
2536
2537 L_FINAL_RETURN_STATUS varchar2(10) := 'S';
2538
2539 BEGIN
2540 l_stage := 800;
2541 print_msg(l_stage||' *** ENTERED SPREAD AMOUNTS API***');
2542 x_return_status := 'S';
2543 L_FINAL_RETURN_STATUS := 'S';
2544 If p_pa_debug_mode = 'Y' Then
2545 pa_debug.init_err_stack('PA_FP_SPREAD_AMTS_PKG.spread_amounts');
2546 End If;
2547 /* Bug fix: 4078623 Both set_curr_fun and Init_err_stack are similar, since reset_curr_function is not called
2548 * This might have been causing the plsql numeric or value error. Not sure because of this. Just to avoid
2549 * confusions commenting out this call
2550 * PA_DEBUG.Set_Curr_Function( p_function => 'PA_FP_SPREAD_AMTS_PKG.spread_amounts' ,p_debug_mode => 'Y');
2551 */
2552
2553 -- validation
2554 IF ( p_budget_version_id IS NULL ) THEN
2555 l_err_msg := 'PA_FP_BUDGET_RES_CURRENCY_NULL';
2556 RAISE SPREAD_AMOUNTS_EXCEPTION;
2557 END IF;
2558
2559 -- get options
2560 print_msg('Deriving finplan option information for the budget version');
2561 get_options( p_budget_version_id,
2562 l_period_set_name,
2563 l_accounted_period_type,
2564 l_pa_period_type,
2565 l_time_phase_code,
2566 v_return_status,
2567 v_msg_count,
2568 v_msg_data);
2569
2570 l_stage := 805;
2571 print_msg(l_stage||'after get_options');
2572 IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2573 l_stage := 806;
2574 print_msg(l_stage||' get_options() err MsgData['||v_msg_data||']');
2575 l_err_msg := v_msg_data;
2576 RAISE SPREAD_AMOUNTS_EXCEPTION;
2577
2578 END IF;
2579
2580 /* call to initialize the global tables */
2581 Initialize_spread_plsqlTabs;
2582
2583 -- For each resource assignment record in global temporary table
2584 -- based on budget_version_id, resource_assignment_id and
2585 -- txn currency code
2586 -- main looooooooooooop starts here
2587 FOR raRec IN resource_assignment_csr LOOP --{
2588
2589 BEGIN
2590 /* set ETC start date from RA */
2591 l_g_start_date := PA_FP_CALC_PLAN_PKG.g_spread_from_date;
2592 print_msg('ETC l_g_start_date['||l_g_start_date||']l_line_end_date['||l_line_end_date||']');
2593
2594 /* Without changing much of the code, assiging the loop variable values to record*/
2595 resource_assignment_rec.RESOURCE_ASSIGNMENT_ID := raRec.RESOURCE_ASSIGNMENT_ID;
2596 resource_assignment_rec.BUDGET_VERSION_ID := raRec.BUDGET_VERSION_ID;
2597 resource_assignment_rec.PROJECT_ID := raRec.PROJECT_ID;
2598 resource_assignment_rec.TASK_ID := raRec.TASK_ID;
2599 resource_assignment_rec.RESOURCE_LIST_MEMBER_ID := raRec.RESOURCE_LIST_MEMBER_ID;
2600 resource_assignment_rec.PLANNING_START_DATE := raRec.PLANNING_START_DATE;
2601 resource_assignment_rec.PLANNING_END_DATE := raRec.PLANNING_END_DATE;
2602 resource_assignment_rec.SPREAD_CURVE_ID := raRec.SPREAD_CURVE_ID;
2603 resource_assignment_rec.SP_FIXED_DATE := raRec.SP_FIXED_DATE;
2604 resource_assignment_rec.TXN_CURRENCY_CODE := raRec.TXN_CURRENCY_CODE;
2605 resource_assignment_rec.TXN_CURRENCY_CODE_OVERRIDE := raRec.TXN_CURRENCY_CODE_OVERRIDE;
2606 resource_assignment_rec.PROJECT_CURRENCY_CODE := raRec.PROJECT_CURRENCY_CODE;
2607 resource_assignment_rec.PROJFUNC_CURRENCY_CODE := raRec.PROJFUNC_CURRENCY_CODE;
2608 resource_assignment_rec.TXN_REVENUE := raRec.TXN_REVENUE;
2609 resource_assignment_rec.TXN_REVENUE_ADDL := raRec.TXN_REVENUE_ADDL;
2610 resource_assignment_rec.TXN_RAW_COST := raRec.TXN_RAW_COST;
2611 resource_assignment_rec.TXN_RAW_COST_ADDL := raRec.TXN_RAW_COST_ADDL;
2612 resource_assignment_rec.TXN_BURDENED_COST := raRec.TXN_BURDENED_COST;
2613 resource_assignment_rec.TXN_BURDENED_COST_ADDL := raRec.TXN_BURDENED_COST_ADDL;
2614 resource_assignment_rec.TXN_PLAN_QUANTITY := raRec.TXN_PLAN_QUANTITY;
2615 resource_assignment_rec.TXN_PLAN_QUANTITY_ADDL := raRec.TXN_PLAN_QUANTITY_ADDL;
2616 resource_assignment_rec.LINE_START_DATE := raRec.LINE_START_DATE;
2617 resource_assignment_rec.LINE_END_DATE := raRec.LINE_END_DATE;
2618 resource_assignment_rec.SOURCE_CONTEXT := raRec.SOURCE_CONTEXT;
2619 resource_assignment_rec.RAW_COST_RATE := raRec.RAW_COST_RATE;
2620 resource_assignment_rec.RAW_COST_RATE_OVERRIDE := raRec.RW_COST_RATE_OVERRIDE;
2621 resource_assignment_rec.BURDEN_COST_RATE := raRec.BURDEN_COST_RATE;
2622 resource_assignment_rec.BURDEN_COST_RATE_OVERRIDE := raRec.BURDEN_COST_RATE_OVERRIDE;
2623 resource_assignment_rec.BILL_RATE := raRec.BILL_RATE;
2624 resource_assignment_rec.BILL_RATE_OVERRIDE := raRec.BILL_RATE_OVERRIDE;
2625 resource_assignment_rec.RATE_BASED_FLAG := raRec.RATE_BASED_FLAG;
2626 resource_assignment_rec.SPREAD_AMOUNTS_FLAG := raRec.SPREAD_AMOUNTS_FLAG;
2627 resource_assignment_rec.INIT_QUANTITY := raRec.INIT_QUANTITY;
2628 resource_assignment_rec.TXN_INIT_RAW_COST := raRec.TXN_INIT_RAW_COST;
2629 resource_assignment_rec.TXN_INIT_BURDENED_COST := raRec.TXN_INIT_BURDENED_COST;
2630 resource_assignment_rec.TXN_INIT_REVENUE := raRec.TXN_INIT_REVENUE;
2631
2632 -- Get resource assignment id and txn currency code
2633 v_resource_assignment_id := resource_assignment_rec.RESOURCE_ASSIGNMENT_ID;
2634 v_txn_currency_code := resource_assignment_rec.TXN_CURRENCY_CODE;
2635 G_rate_based_flag := NVL(resource_assignment_rec.RATE_BASED_FLAG,'N');
2636 G_Curr_code := NVL(resource_assignment_rec.TXN_CURRENCY_CODE_OVERRIDE,resource_assignment_rec.TXN_CURRENCY_CODE);
2637 l_proj_curr_cd := resource_assignment_rec.PROJECT_CURRENCY_CODE;
2638 l_projfunc_curr_cd := resource_assignment_rec.PROJFUNC_CURRENCY_CODE;
2639
2640 /* Bug fix:4030310 if etc start date is less than line start date the set the etc date as null */
2641 IF l_g_start_date IS NOT NULL AND trunc(l_g_start_date) < trunc(resource_assignment_rec.PLANNING_START_DATE)THEN
2642 l_stage := 810.1;
2643 print_msg(l_stage||'set though date to null because earlier than plan start_date');
2644 l_g_start_date := NULL;
2645 END IF;
2646
2647 l_stage := 820;
2648 If p_pa_debug_mode = 'Y' Then
2649 print_msg(l_stage||' input data:');
2650 print_msg('ra_resource_assignment_id => '||resource_assignment_rec.resource_assignment_id);
2651 print_msg('ra_BUDGET_VERSION_ID => '||resource_assignment_rec.BUDGET_VERSION_ID);
2652 print_msg('ra_PLANNING_START/END_DATE => '||resource_assignment_rec.PLANNING_START_DATE||'/'||resource_assignment_rec.PLANNING_END_DATE);
2653 print_msg('ra_SPREAD_CURVE_ID/FIXED_DATE => '||resource_assignment_rec.SPREAD_CURVE_ID||'/'||resource_assignment_rec.SP_FIXED_DATE);
2654 print_msg('ra_TXN_CURRENCY_CODE/OVERRIDE => '||resource_assignment_rec.TXN_CURRENCY_CODE||'/'||resource_assignment_rec.TXN_CURRENCY_CODE_OVERRIDE);
2655 print_msg('ra_TXN_REVENUE/ADDL => '||resource_assignment_rec.TXN_REVENUE||'/'||resource_assignment_rec.TXN_REVENUE_ADDL);
2656 print_msg('ra_TXN_RAW_COST/ADDL => '||resource_assignment_rec.TXN_RAW_COST||'/'||resource_assignment_rec.TXN_RAW_COST_ADDL);
2657 print_msg('ra_TXN_BURDENED_COST/ADDL => '||resource_assignment_rec.TXN_BURDENED_COST||'/'||resource_assignment_rec.TXN_BURDENED_COST_ADDL);
2658 print_msg('ra_TXN_PLAN_QUANTITY/ADDL => '||resource_assignment_rec.TXN_PLAN_QUANTITY||'/'||resource_assignment_rec.TXN_PLAN_QUANTITY_ADDL);
2659 print_msg('ra_SOURCE_CONTEXT => '||resource_assignment_rec.SOURCE_CONTEXT);
2660 print_msg('ra_LINE_START/END_DATE => '||resource_assignment_rec.LINE_START_DATE||'/'||resource_assignment_rec.LINE_END_DATE);
2661 print_msg('ra_RAW_COST_RATE/OVERRIDE => '||resource_assignment_rec.RAW_COST_RATE||'/'||resource_assignment_rec.RAW_COST_RATE_OVERRIDE);
2662 print_msg('ra_BURDEN_COST_RATE/OVERRIDE => '||resource_assignment_rec.BURDEN_COST_RATE||'/'||resource_assignment_rec.BURDEN_COST_RATE_OVERRIDE);
2663 print_msg('ra_BILL_RATE/OVERRIDE => '||resource_assignment_rec.BILL_RATE||'/'||resource_assignment_rec.BILL_RATE_OVERRIDE);
2664 print_msg('ra_RATE_BASED_FLAG => '||G_rate_based_flag);
2665 print_msg('ra_SPREAD_AMOUNTS_FLAG => '||resource_assignment_rec.SPREAD_AMOUNTS_FLAG);
2666 print_msg('neg_Qty_Change_flag => '||raRec.neg_Qty_Change_flag);
2667 End If;
2668
2669 /*** Bug fix:4194475 execute only when there is error so moved to exception block
2670 l_stage := 821;
2671 print_msg(l_stage||' get project_name, task_name and resource_name');
2672 OPEN get_line_info(v_resource_assignment_id);
2673 FETCH get_line_info
2674 INTO l_project_name
2675 , l_task_name
2676 , l_resource_name;
2677 CLOSE get_line_info;
2678 ****/
2679
2680 -- validate resource assignment record
2681 IF ( resource_assignment_rec.PLANNING_START_DATE IS NULL OR
2682 resource_assignment_rec.PLANNING_END_DATE IS NULL) OR
2683 ( resource_assignment_rec.PLANNING_START_DATE >
2684 resource_assignment_rec.PLANNING_END_DATE ) OR
2685 (resource_assignment_rec.SOURCE_CONTEXT = 'BUDGET_LINE' AND
2686 ((resource_assignment_rec.LINE_START_DATE IS NULL OR
2687 resource_assignment_rec.LINE_END_DATE IS NULL) OR
2688 (resource_assignment_rec.LINE_START_DATE >
2689 resource_assignment_rec.LINE_END_DATE
2690 ))) THEN
2691
2692 IF resource_assignment_rec.SOURCE_CONTEXT = 'BUDGET_LINE' THEN
2693 l_line_start_date := resource_assignment_rec.LINE_START_DATE;
2694 l_line_end_date := resource_assignment_rec.LINE_END_DATE;
2695 ELSE
2696 l_line_start_date := resource_assignment_rec.PLANNING_START_DATE;
2697 l_line_end_date := resource_assignment_rec.PLANNING_END_DATE;
2698 END IF;
2699
2700 l_err_msg := 'PA_FP_PLAN_START_END_DATE_ERR';
2701 RAISE SPREAD_AMOUNTS_EXCEPTION;
2702
2703 END IF;
2704
2705
2706 -- skip record when all the amouns are null
2707
2708 IF ( resource_assignment_rec.TXN_PLAN_QUANTITY_ADDL IS NULL AND
2709 resource_assignment_rec.TXN_RAW_COST_ADDL IS NULL AND
2710 resource_assignment_rec.TXN_BURDENED_COST_ADDL IS NULL AND
2711 resource_assignment_rec.TXN_REVENUE_ADDL IS NULL ) THEN
2712 l_stage := 841;
2713 print_msg(l_stage||' all amounts are null, skip the resource assignment');
2714 RAISE SKIP_EXCEPTION;
2715 END IF;
2716
2717
2718
2719 -- Note: 1. if plan start/end date shift, all budget lines beyond
2720 -- the new plan state/end date will be
2721 -- deleted before calling spread_amounts()
2722 -- 2. if budget line(s) is(are) there, it's not allowed to
2723 -- change the time phase code - from N/R to G/P or from G/P
2724 -- to N/R or from G to P or from P to G etc.
2725 -- 3. line_start/end_date must at begin/end of period
2726
2727 l_stage := 860;
2728 print_msg('Deriving period information for the budget version');
2729 get_periods(
2730 NVL(l_g_start_date,resource_assignment_rec.PLANNING_START_DATE),
2731 resource_assignment_rec.PLANNING_END_DATE,
2732 l_period_set_name,
2733 l_accounted_period_type,
2734 l_pa_period_type,
2735 l_time_phase_code,
2736 v_spread_amounts,
2737 v_return_status,
2738 v_msg_count,
2739 v_msg_data);
2740 print_msg(l_stage||' after get periods retSts['||v_return_status||']v_spread_amounts.coount['||v_spread_amounts.COUNT||']');
2741 IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2742 l_err_msg := v_msg_data;
2743 RAISE SPREAD_AMOUNTS_EXCEPTION;
2744 END IF;
2745
2746 IF p_pa_debug_mode = 'Y' Then
2747 IF ( v_spread_amounts.COUNT > 0
2748 AND l_time_phase_code IN ('P','G')) THEN
2749
2750 FOR i IN v_spread_amounts.first.. v_spread_amounts.last LOOP
2751 IF (i = v_spread_amounts.first OR i = v_spread_amounts.last) THEN
2752 NULL;
2753 print_msg('start/end date '||i||'=> '||v_spread_amounts(i).start_date||'/'||v_spread_amounts(i).end_date);
2754 END IF;
2755 END LOOP;
2756 END IF;
2757 END IF;
2758
2759 -- updated 030204 Sgoteti
2760 IF l_time_phase_code IN ('P','G')
2761 AND resource_assignment_rec.SOURCE_CONTEXT = 'BUDGET_LINE'
2762 AND ((resource_assignment_rec.LINE_END_DATE <
2763 v_spread_amounts(1).start_date )
2764 OR
2765 ( v_spread_amounts(v_spread_amounts.COUNT()).end_date <
2766 resource_assignment_rec.LINE_START_DATE)) THEN
2767
2768 l_stage := 863;
2769 print_msg(l_stage||' line start/end date miss the planning period, skip the resource assignment');
2770 RAISE SKIP_EXCEPTION;
2771 END IF;
2772
2773 /* Initialize line start and line end date with planning SD and ED*/
2774 l_line_start_date := NVL(l_g_start_date,resource_assignment_rec.PLANNING_START_DATE);
2775 l_line_end_date := resource_assignment_rec.PLANNING_END_DATE;
2776
2777 -- updated 030204 Sgoteti
2778 IF l_time_phase_code IN ('P','G') THEN
2779 l_line_start_date := v_spread_amounts(1).start_date;
2780 l_line_end_date := v_spread_amounts(v_spread_amounts.count()).end_date;
2781
2782 IF resource_assignment_rec.SOURCE_CONTEXT = 'BUDGET_LINE' THEN
2783 IF resource_assignment_rec.LINE_START_DATE > l_line_start_date THEN
2784 -- resource_assignment_rec.PLANNING_START_DATE THEN
2785 l_line_start_date := resource_assignment_rec.LINE_START_DATE;
2786 END IF;
2787 IF resource_assignment_rec.LINE_END_DATE < l_line_end_date THEN
2788 -- resource_assignment_rec.PLANNING_END_DATE THEN
2789 l_line_end_date := resource_assignment_rec.LINE_END_DATE;
2790 END IF;
2791 END IF;
2792
2793 -- updated 030204 Sgoteti
2794 END IF;
2795
2796 l_stage := 845;
2797 print_msg(l_stage||' after get line start/end date '||l_line_start_date||'/'||l_line_end_date);
2798
2799 /* Get budget line amounts for the given resource */
2800 BEGIN
2801 l_stage := 520;
2802 print_msg(l_stage||' before get sum of amounts from budget line');
2803 /* Initialize the budget line varaibles */
2804 l_sum_txn_quantity := NULL;
2805 l_sum_txn_raw_cost := NULL;
2806 l_sum_txn_burdened_cost := NULL;
2807 l_sum_txn_revenue := NULL;
2808 l_bl_line_id := NULL;
2809 l_bl_count := 0;
2810 OPEN bl_details(v_resource_assignment_id
2811 ,v_txn_currency_code
2812 ,l_line_start_date
2813 ,l_line_end_date
2814 ,resource_assignment_rec.SOURCE_CONTEXT) ;
2815 FETCH bl_details INTO
2816 l_sum_txn_quantity
2817 ,l_sum_txn_raw_cost
2818 ,l_sum_txn_burdened_cost
2819 ,l_sum_txn_revenue
2820 ,l_bl_line_id
2821 ,l_bl_count
2822 ,l_g_sum_etc_quantity;
2823 CLOSE bl_details;
2824 /* set the linecount variable to zero if its null or the cursor not found */
2825 IF l_bl_count is NULL Then
2826 l_bl_count := 0;
2827 End If;
2828 print_msg(l_stage||'l_bl_count['||l_bl_count||']');
2829
2830
2831 EXCEPTION
2832 WHEN NO_DATA_FOUND THEN
2833 null;
2834 END;
2835 l_stage := 530;
2836 If p_pa_debug_mode = 'Y' Then
2837 print_msg(l_stage||' Before spread Amts from Budget Line l_time_phase_code '||l_time_phase_code);
2838 print_msg(' l_sum_txn_quantity '||l_sum_txn_quantity||']l_sum_txn_raw_cost['||l_sum_txn_raw_cost||']');
2839 print_msg(' l_sum_txn_burdened_cost['||l_sum_txn_burdened_cost||']l_sum_txn_revenue['||l_sum_txn_revenue||']');
2840 End If;
2841
2842 l_stage := 880;
2843 -- when time phase code is R or N
2844 IF (l_time_phase_code = 'R' OR l_time_phase_code = 'N') THEN
2845
2846 l_stage := 890;
2847 print_msg(l_stage||' enter time phase is R or N');
2848 /* Bug fixL: 3877889 For Non-Timephase budgets PlanSDate should be considiered
2849 -- l_plan_start_date := NVL(l_g_start_date,resource_assignment_rec.PLANNING_START_DATE);
2850 */
2851 l_plan_start_date := resource_assignment_rec.PLANNING_START_DATE;
2852 l_plan_end_date := resource_assignment_rec.PLANNING_END_DATE;
2853 budget_line_time_phase_rec := NULL;
2854 OPEN budget_line_time_phase_csr;
2855 FETCH budget_line_time_phase_csr
2856 INTO budget_line_time_phase_rec;
2857 l_budget_line_time_phase_count := budget_line_time_phase_csr%ROWCOUNT;
2858 CLOSE budget_line_time_phase_csr;
2859 print_msg('l_budget_line_time_phase_count['||l_budget_line_time_phase_count||']');
2860
2861 IF l_budget_line_time_phase_count = 0 THEN
2862 --print_msg('Inserting records into budget line for l_budget_line_time_phase_count = 0');
2863 -- Insert into PA_BUDGET_LINES,
2864 insert_budget_line(
2865 v_resource_assignment_id,
2866 resource_assignment_rec.PLANNING_START_DATE,
2867 resource_assignment_rec.PLANNING_END_DATE,
2868 NULL,
2869 resource_assignment_rec.TXN_CURRENCY_CODE,
2870 resource_assignment_rec.TXN_CURRENCY_CODE_OVERRIDE,
2871 v_budget_line_id,
2872 p_budget_version_id,
2873 l_proj_curr_cd,
2874 l_projfunc_curr_cd,
2875 v_return_status,
2876 v_msg_count,
2877 v_msg_data);
2878
2879 IF v_return_status <> 'S' Then
2880 l_err_msg := v_msg_data;
2881 RAISE SPREAD_AMOUNTS_EXCEPTION;
2882 END IF;
2883
2884 --print_msg('Inserting records into rollup tmp for l_budget_line_time_phase_count = 0');
2885 insert_rollup_tmp(
2886 resource_assignment_rec,
2887 p_budget_version_id,
2888 resource_assignment_rec.PLANNING_START_DATE,
2889 resource_assignment_rec.PLANNING_END_DATE,
2890 NULL,
2891 v_budget_line_id,
2892 resource_assignment_rec.TXN_PLAN_QUANTITY,
2893 resource_assignment_rec.TXN_RAW_COST,
2894 resource_assignment_rec.TXN_BURDENED_COST,
2895 resource_assignment_rec.TXN_REVENUE,
2896 v_return_status,
2897 v_msg_count,
2898 v_msg_data);
2899
2900 IF v_return_status <> 'S' Then
2901 l_err_msg := v_msg_data;
2902 RAISE SPREAD_AMOUNTS_EXCEPTION;
2903 END IF;
2904
2905 ELSIF l_budget_line_time_phase_count = 1 THEN
2906 --print_msg('Inserting records into rollup tmp for l_budget_line_time_phase_count = 1');
2907 -- Insert Rollup Temporary Table
2908 insert_rollup_tmp_with_bl(
2909 resource_assignment_rec,
2910 p_budget_version_id,
2911 resource_assignment_rec.PLANNING_START_DATE,
2912 resource_assignment_rec.PLANNING_END_DATE,
2913 NULL,
2914 budget_line_time_phase_rec.budget_line_id,
2915 resource_assignment_rec.TXN_PLAN_QUANTITY,
2916 resource_assignment_rec.TXN_RAW_COST,
2917 resource_assignment_rec.TXN_BURDENED_COST,
2918 resource_assignment_rec.TXN_REVENUE,
2919 v_return_status,
2920 v_msg_count,
2921 v_msg_data);
2922
2923 IF v_return_status <> 'S' Then
2924 l_err_msg := v_msg_data;
2925 RAISE SPREAD_AMOUNTS_EXCEPTION;
2926 END IF;
2927 ELSE
2928 l_err_msg := 'FA_FP_MULTI_NON_PERIOD';
2929 RAISE SPREAD_AMOUNTS_EXCEPTION;
2930 END IF;
2931
2932 ELSE -- time phase code is not R or N
2933 l_stage := 850;
2934 print_msg(l_stage||' Entered time phase code is G or P');
2935 -- get spread curve
2936 l_spread_curve_id := resource_assignment_rec.SPREAD_CURVE_ID;
2937 spread_curve_b_rec := NULL;
2938 OPEN spread_curve_csr;
2939 FETCH spread_curve_csr
2940 INTO spread_curve_b_rec;
2941 IF spread_curve_csr%ROWCOUNT = 1 THEN
2942 v_spread_curve := spread_curve_type(
2943 spread_curve_b_rec.POINT1,
2944 spread_curve_b_rec.POINT2,
2945 spread_curve_b_rec.POINT3,
2946 spread_curve_b_rec.POINT4,
2947 spread_curve_b_rec.POINT5,
2948 spread_curve_b_rec.POINT6,
2949 spread_curve_b_rec.POINT7,
2950 spread_curve_b_rec.POINT8,
2951 spread_curve_b_rec.POINT9,
2952 spread_curve_b_rec.POINT10);
2953 ELSE
2954 v_spread_curve := spread_curve_type
2955 (10, 10, 10, 10, 10, 10, 10, 10, 10, 10);
2956 END IF;
2957 CLOSE spread_curve_csr;
2958 l_stage := 850;
2959 If p_pa_debug_mode = 'Y' Then
2960 print_msg(l_stage||' after get spread curve');
2961 print_msg(' '||'spread curve => '||v_spread_curve(1)||' '||v_spread_curve(2)||' '||v_spread_curve(3));
2962 print_msg(' '||v_spread_curve(4)||' '||v_spread_curve(5)||' '||v_spread_curve(6)||' '||v_spread_curve(7));
2963 print_msg(' '||v_spread_curve(8)||' '||v_spread_curve(9)||' '||v_spread_curve(10));
2964 End If;
2965
2966 IF ( l_g_start_date IS NOT NULL
2967 AND ( l_g_start_date > l_line_end_date ))THEN
2968 -- v_spread_amounts(v_spread_amounts.COUNT).end_date
2969 IF l_g_start_date > resource_assignment_rec.PLANNING_END_DATE THEN
2970 l_stage := 910;
2971 print_msg(l_stage||'enter though date after plan end date');
2972 print_msg(l_stage||'ETC start date is greater than planning end date');
2973 BEGIN
2974 bl_exist := TRUE;
2975 i := v_spread_amounts.COUNT;
2976 l_quantity := NULL;
2977 l_txn_raw_cost := NULL;
2978 l_txn_burdened_cost := NULL;
2979 l_txn_revenue := NULL;
2980 v_budget_line_id := NULL;
2981 l_dummy_count := 0;
2982 OPEN bl_details(v_resource_assignment_id
2983 ,v_txn_currency_code
2984 ,v_spread_amounts(i).start_date
2985 ,v_spread_amounts(i).end_date
2986 ,resource_assignment_rec.SOURCE_CONTEXT);
2987 FETCH bl_details INTO
2988 l_quantity
2989 ,l_txn_raw_cost
2990 ,l_txn_burdened_cost
2991 ,l_txn_revenue
2992 ,v_budget_line_id
2993 ,l_dummy_count
2994 ,l_g_sum_etc_quantity;
2995 CLOSE bl_details;
2996 /* set the linecount variable to zero if its null or the cursor not found */
2997 IF l_dummy_count is NULL Then
2998 l_dummy_count := 0;
2999 End If;
3000 If l_dummy_count = 0 Then
3001 bl_exist := FALSE;
3002 End If;
3003
3004 EXCEPTION
3005 WHEN NO_DATA_FOUND THEN
3006 bl_exist := FALSE;
3007 END;
3008
3009 IF bl_exist = FALSE THEN
3010 print_msg('Budget line not exists');
3011 IF (( resource_assignment_rec.TXN_PLAN_QUANTITY_ADDL IS NOT NULL
3012 AND resource_assignment_rec.TXN_PLAN_QUANTITY_ADDL <> 0 )
3013 OR ( resource_assignment_rec.TXN_RAW_COST_ADDL IS NOT NULL
3014 AND resource_assignment_rec.TXN_RAW_COST_ADDL <> 0)
3015 OR (resource_assignment_rec.TXN_BURDENED_COST_ADDL IS NOT NULL
3016 AND resource_assignment_rec.TXN_BURDENED_COST_ADDL <> 0)
3017 OR (resource_assignment_rec.TXN_REVENUE_ADDL IS NOT NULL
3018 AND resource_assignment_rec.TXN_REVENUE_ADDL <> 0)) THEN
3019
3020 insert_budget_line(
3021 v_resource_assignment_id,
3022 v_spread_amounts(v_spread_amounts.COUNT).start_date,
3023 v_spread_amounts(v_spread_amounts.COUNT).end_date,
3024 v_spread_amounts(v_spread_amounts.COUNT).period_name,
3025 v_txn_currency_code,
3026 resource_assignment_rec.TXN_CURRENCY_CODE_OVERRIDE,
3027 v_budget_line_id,
3028 p_budget_version_id,
3029 l_proj_curr_cd,
3030 l_projfunc_curr_cd,
3031 v_return_status,
3032 v_msg_count,
3033 v_msg_data);
3034
3035 IF v_return_status <> 'S' Then
3036 l_err_msg := v_msg_data;
3037 RAISE SPREAD_AMOUNTS_EXCEPTION;
3038 END IF;
3039
3040 l_stage := 1004;
3041 -- print_msg(l_stage||' after insert budget line');
3042 -- Insert into Rollup Temporary Table
3043 insert_rollup_tmp(
3044 resource_assignment_rec,
3045 p_budget_version_id,
3046 v_spread_amounts(v_spread_amounts.COUNT).start_date,
3047 v_spread_amounts(v_spread_amounts.COUNT).end_date,
3048 v_spread_amounts(v_spread_amounts.COUNT).period_name,
3049 v_budget_line_id,
3050 resource_assignment_rec.TXN_PLAN_QUANTITY_ADDL,
3051 resource_assignment_rec.TXN_RAW_COST_ADDL,
3052 resource_assignment_rec.TXN_BURDENED_COST_ADDL,
3053 resource_assignment_rec.TXN_REVENUE_ADDL,
3054 v_return_status,
3055 v_msg_count,
3056 v_msg_data);
3057
3058 IF v_return_status <> 'S' Then
3059 l_err_msg := v_msg_data;
3060 RAISE SPREAD_AMOUNTS_EXCEPTION;
3061 END IF;
3062 END IF;
3063
3064 ELSE -- budget line exists
3065
3066 insert_rollup_tmp_with_bl(
3067 resource_assignment_rec,
3068 p_budget_version_id,
3069 v_spread_amounts(v_spread_amounts.COUNT).start_date,
3070 v_spread_amounts(v_spread_amounts.COUNT).end_date,
3071 v_spread_amounts(v_spread_amounts.COUNT).period_name,
3072 v_budget_line_id,
3073 nvl(l_quantity,0) + resource_assignment_rec.TXN_PLAN_QUANTITY_ADDL,
3074 nvl(l_txn_raw_cost,0) + resource_assignment_rec.TXN_RAW_COST_ADDL,
3075 nvl(l_txn_burdened_cost,0) + resource_assignment_rec.TXN_BURDENED_COST_ADDL,
3076 nvl(l_txn_revenue,0) + resource_assignment_rec.TXN_REVENUE_ADDL,
3077 v_return_status,
3078 v_msg_count,
3079 v_msg_data);
3080
3081 IF v_return_status <> 'S' Then
3082 l_err_msg := v_msg_data;
3083 RAISE SPREAD_AMOUNTS_EXCEPTION;
3084 END IF;
3085 END IF;
3086 END IF;
3087 RAISE SKIP_EXCEPTION;
3088 END IF; -- end of etc start date is greater than planning end date
3089
3090 /* if etc start date is less than line start date the set the etc date as null */
3091 IF l_g_start_date IS NOT NULL AND l_g_start_date < l_line_start_date THEN
3092 l_stage := 920;
3093 print_msg(l_stage||'set though date to null because earlier than line start_date');
3094 l_g_start_date := NULL;
3095 END IF;
3096
3097 IF l_g_start_date IS NOT NULL THEN
3098 FOR i IN 1 .. v_spread_amounts.COUNT LOOP
3099 IF (( i > 1)
3100 AND l_g_start_date > v_spread_amounts(i - 1).end_date
3101 AND l_g_start_date < v_spread_amounts(i).start_date) THEN
3102 l_g_start_date := v_spread_amounts(i).start_date;
3103 l_stage := 930;
3104 print_msg(l_stage||'set though date to begin of next period because fall between periods '||l_g_start_date);
3105 END IF;
3106 IF l_g_start_date BETWEEN v_spread_amounts(i).start_date
3107 AND v_spread_amounts(i).end_date THEN
3108 l_line_start_date := v_spread_amounts(i).start_date;
3109 l_stage := 940;
3110 print_msg(l_stage||' set line start date to begin of period though date falls '||l_line_start_date);
3111 END IF;
3112 END LOOP;
3113 END IF;
3114
3115 IF l_g_start_date IS NOT NULL THEN
3116 /* get budget line amounts for the period */
3117 l_g_sum_txn_quantity := NULL;
3118 l_g_sum_txn_raw_cost := NULL;
3119 l_g_sum_txn_burdened_cost := NULL;
3120 l_g_sum_txn_revenue := NULL;
3121 l_dummy_bl_id := NULL;
3122 l_g_bl_count := 0;
3123 OPEN bl_details(v_resource_assignment_id
3124 ,v_txn_currency_code
3125 ,l_line_start_date
3126 ,l_line_end_date
3127 ,resource_assignment_rec.SOURCE_CONTEXT);
3128 FETCH bl_details INTO
3129 l_g_sum_txn_quantity
3130 ,l_g_sum_txn_raw_cost
3131 ,l_g_sum_txn_burdened_cost
3132 ,l_g_sum_txn_revenue
3133 ,l_dummy_bl_id
3134 ,l_g_bl_count
3135 ,l_g_sum_etc_quantity;
3136 CLOSE bl_details;
3137 If l_g_bl_count is NULL Then
3138 l_g_bl_count := 0;
3139 End If;
3140 l_stage := 950;
3141 print_msg(l_stage||' get sum of amounts from though date to line end date l_g_bl_count '||l_g_bl_count);
3142
3143 END IF;
3144
3145 l_txn_quantity_addl := resource_assignment_rec.TXN_PLAN_QUANTITY_ADDL;
3146 l_txn_raw_cost_addl := resource_assignment_rec.TXN_RAW_COST_ADDL;
3147 l_txn_burdened_cost_addl := resource_assignment_rec.TXN_BURDENED_COST_ADDL;
3148 l_txn_revenue_addl := resource_assignment_rec.TXN_REVENUE_ADDL;
3149 print_msg('960l_txn_quantity_addl['||l_txn_quantity_addl||']l_txn_raw_cost_addl['||l_txn_raw_cost_addl||']');
3150 print_msg('l_txn_burdened_cost_addl['||l_txn_burdened_cost_addl||']l_txn_revenue_addl['||l_txn_revenue_addl||']');
3151
3152 IF l_g_start_date IS NOT NULL THEN
3153 print_msg('setting the l_sum variables');
3154 l_sum_txn_quantity := l_g_sum_Etc_quantity;
3155 l_sum_txn_raw_cost := l_g_sum_txn_raw_cost ;
3156 l_sum_txn_burdened_cost := l_g_sum_txn_burdened_cost;
3157 l_sum_txn_revenue := l_g_sum_txn_revenue;
3158 END IF;
3159 l_stage := 970;
3160 If p_pa_debug_mode = 'Y' Then
3161 print_msg(l_stage||' get amounts addl plus etc');
3162 print_msg('l_txn_quantity_addl '||l_txn_quantity_addl||']l_txn_raw_cost_addl['||l_txn_raw_cost_addl||']');
3163 print_msg('l_txn_burdened_cost_addl '||l_txn_burdened_cost_addl||']l_txn_revenue_addl['||l_txn_revenue_addl||']');
3164 print_msg('l_sum_txn_quantity['||l_sum_txn_quantity||']l_sum_txn_raw_cost['||l_sum_txn_raw_cost||']');
3165 End If;
3166
3167 -- When spread curve's SPREAD_CURVE_CODE
3168 -- is not FIXED
3169 IF NOT (spread_curve_b_rec.SPREAD_CURVE_CODE IS NOT NULL
3170 AND spread_curve_b_rec.SPREAD_CURVE_CODE = 'FIXED_DATE') THEN
3171 l_stage := 980;
3172 print_msg(l_stage||' Entered spread curve code is NOT Fixed date');
3173
3174 l_stage := 1025;
3175 print_msg(l_stage||' sum of budget lines minus etc');
3176
3177 /* bug fix:5726773 : negative quantity spread
3178 * when budget lines exists and sum of total quantity is zero then
3179 * distributing the quantity based on existing line distribution method fails with
3180 * divide by zero error. In order to avoid this spread the quantity based on the
3181 * spread curve. This is proposed by PMs
3182 */
3183 l_neg_qty_er_flag := 'N';
3184 If l_bl_count <> 0
3185 AND l_txn_quantity_addl <> 0
3186 AND ((l_sum_txn_quantity = 0 and l_g_start_date IS NULL)
3187 OR (l_g_sum_txn_quantity = 0 and l_g_start_date IS NOT NULL)
3188 OR (l_g_bl_count = 0 and l_g_sum_Etc_quantity = 0
3189 and l_g_sum_txn_quantity <> 0 and l_g_start_date IS NOT NULL)) Then
3190 l_neg_qty_er_flag := 'Y';
3191 End If;
3192
3193 -- if need call spread()
3194 IF ( l_bl_count = 0
3195 OR (l_g_start_date IS NOT NULL AND l_g_bl_count = 0)
3196 OR (resource_assignment_rec.SPREAD_AMOUNTS_FLAG = 'Y')
3197 OR l_neg_qty_er_flag = 'Y' ) THEN /* bug fix:5726773 */
3198
3199 l_stage := 990;
3200 print_msg(l_stage||' enter spread or respread');
3201 -- set start/end date
3202 v_start_end_date := start_end_date_table_type();
3203 v_start_end.start_date := NVL(l_g_start_date,resource_assignment_rec.PLANNING_START_DATE);
3204 v_start_end.end_date := resource_assignment_rec.PLANNING_END_DATE;
3205 v_start_end_date.EXTEND();
3206 v_start_end_date(1) := v_start_end;
3207
3208 -- set line start/end period
3209 FOR i IN 1 .. v_spread_amounts.COUNT() LOOP
3210 IF l_line_start_date BETWEEN v_spread_amounts(i).start_date
3211 AND v_spread_amounts(i).end_date THEN
3212 l_line_start := i;
3213 END IF;
3214 IF l_line_end_date BETWEEN v_spread_amounts(i).start_date
3215 AND v_spread_amounts(i).end_date THEN
3216 l_line_end := i;
3217 END IF;
3218 END LOOP;
3219
3220 /* Calling Spread api to calculate the amounts,qty and burdened cost to spread across periods*/
3221 print_msg('CALLING SPREAD api');
3222 spread(4,
3223 l_txn_quantity_addl,
3224 l_txn_raw_cost_addl,
3225 l_txn_burdened_cost_addl,
3226 l_txn_revenue_addl,
3227 0,
3228 0,
3229 0,
3230 0,
3231 0,
3232 0,
3233 v_start_end_date,
3234 v_spread_curve,
3235 l_line_start,
3236 l_line_end,
3237 l_g_start_date,
3238 v_spread_amounts,
3239 v_return_status,
3240 v_msg_count,
3241 v_msg_data);
3242
3243 IF v_return_status <> 'S' Then
3244 l_err_msg := v_msg_data;
3245 RAISE SPREAD_AMOUNTS_EXCEPTION;
3246 END IF;
3247
3248 l_stage := 1000;
3249 If p_pa_debug_mode = 'Y' Then
3250 print_msg(l_stage||' after call spread()');
3251 FOR i IN 1 .. v_spread_amounts.COUNT() LOOP
3252 IF i = l_line_start OR i = l_line_end THEN
3253 NULL;
3254 print_msg('start/end date '||i||'=> '||v_spread_amounts(i).start_date||'/'||v_spread_amounts(i).end_date);
3255 print_msg('txn quantity '||i||'=> '||v_spread_amounts(i).amount1);
3256 print_msg('txn raw cost '||i||'=> '||v_spread_amounts(i).amount2);
3257 print_msg('txn burdened cost '||i||'=> '||v_spread_amounts(i).amount3);
3258 print_msg('txn revenue '||i||'=> '||v_spread_amounts(i).amount4);
3259 END IF;
3260 END LOOP;
3261 End If;
3262
3263
3264 l_stage := 1001;
3265 print_msg(l_stage||' before update loop');
3266 FOR i IN l_line_start .. l_line_end LOOP
3267
3268 bl_exist := TRUE;
3269
3270 BEGIN
3271 l_quantity := NULL;
3272 l_txn_raw_cost := NULL;
3273 l_txn_burdened_cost := NULL;
3274 l_txn_revenue := NULL;
3275 v_budget_line_id := NULL;
3276 l_dummy_count := 0;
3277 OPEN bl_details(v_resource_assignment_id
3278 ,v_txn_currency_code
3279 ,v_spread_amounts(i).start_date
3280 ,v_spread_amounts(i).end_date
3281 ,resource_assignment_rec.SOURCE_CONTEXT);
3282 FETCH bl_details INTO
3283 l_quantity
3284 ,l_txn_raw_cost
3285 ,l_txn_burdened_cost
3286 ,l_txn_revenue
3287 ,v_budget_line_id
3288 ,l_dummy_count
3289 ,l_g_sum_etc_quantity;
3290 CLOSE bl_details;
3291 /* set the linecount variable to zero if its null or the cursor not found */
3292 IF l_dummy_count is NULL Then
3293 l_dummy_count := 0;
3294 End If;
3295 If l_dummy_count = 0 Then
3296 bl_exist := FALSE;
3297 End If;
3298
3299 EXCEPTION
3300 WHEN NO_DATA_FOUND THEN
3301 bl_exist := FALSE;
3302 END;
3303
3304
3305 IF NOT bl_exist THEN
3306
3307 l_stage := 1003;
3308 --print_msg(l_stage||' enter budget line id not found ');
3309
3310 IF v_spread_amounts(i).amount1 IS NOT NULL AND
3311 v_spread_amounts(i).amount1 <> 0 OR
3312 v_spread_amounts(i).amount2 IS NOT NULL AND
3313 v_spread_amounts(i).amount2 <> 0 OR
3314 v_spread_amounts(i).amount3 IS NOT NULL AND
3315 v_spread_amounts(i).amount3 <> 0 OR
3316 v_spread_amounts(i).amount4 IS NOT NULL AND
3317 v_spread_amounts(i).amount4 <> 0 THEN
3318
3319 -- Insert into PA_BUDGET_LINES,
3320
3321 insert_budget_line(
3322 v_resource_assignment_id,
3323 v_spread_amounts(i).start_date,
3324 v_spread_amounts(i).end_date,
3325 v_spread_amounts(i).period_name,
3326 v_txn_currency_code,
3327 resource_assignment_rec.TXN_CURRENCY_CODE_OVERRIDE,
3328 v_budget_line_id,
3329 p_budget_version_id,
3330 l_proj_curr_cd,
3331 l_projfunc_curr_cd,
3332 v_return_status,
3333 v_msg_count,
3334 v_msg_data);
3335
3336 IF v_return_status <>
3337 FND_API.G_RET_STS_SUCCESS THEN
3338 l_err_msg := v_msg_data;
3339 RAISE SPREAD_AMOUNTS_EXCEPTION;
3340
3341 END IF;
3342
3343 l_stage := 1004;
3344 -- print_msg(l_stage||' after insert budget line');
3345
3346 -- Insert into Rollup Temporary Table
3347
3348 insert_rollup_tmp(
3349 resource_assignment_rec,
3350 p_budget_version_id,
3351 v_spread_amounts(i).start_date,
3352 v_spread_amounts(i).end_date,
3353 v_spread_amounts(i).period_name,
3354 v_budget_line_id,
3355 v_spread_amounts(i).amount1,
3356 v_spread_amounts(i).amount2,
3357 v_spread_amounts(i).amount3,
3358 v_spread_amounts(i).amount4,
3359 v_return_status,
3360 v_msg_count,
3361 v_msg_data);
3362
3363 IF v_return_status <>
3364 FND_API.G_RET_STS_SUCCESS THEN
3365 l_err_msg := v_msg_data;
3366 RAISE SPREAD_AMOUNTS_EXCEPTION;
3367
3368 END IF;
3369
3370 l_stage := 1005;
3371 -- print_msg(l_stage||' after insert rollup tmp');
3372 END IF; -- all amounts 0 or null
3373
3374 ELSE -- found one budget line
3375
3376 l_stage := 1006;
3377 --print_msg(l_stage||' enter budget line id found');
3378 If l_txn_quantity_addl is NOT NULL Then
3379 If l_neg_qty_er_flag = 'Y' Then /* bug fix:5726773 */
3380 l_quantity := v_spread_amounts(i).amount1;
3381 Else
3382 l_quantity := nvl(l_quantity, 0) + v_spread_amounts(i).amount1;
3383 End If;
3384 Else
3385 l_quantity := nvl(l_quantity,0) ;
3386 End If;
3387
3388 If l_txn_raw_cost_addl is NOT NULL Then
3389 l_txn_raw_cost := nvl(l_txn_raw_cost, 0) + v_spread_amounts(i).amount2;
3390 Else
3391 l_txn_raw_cost := nvl(l_txn_raw_cost,0) ;
3392 End If;
3393
3394 If l_txn_burdened_cost_addl is NOT NULL Then
3395 l_txn_burdened_cost := nvl(l_txn_burdened_cost, 0)+ v_spread_amounts(i).amount3;
3396 Else
3397 l_txn_burdened_cost := nvl(l_txn_burdened_cost,0) ;
3398 End If;
3399
3400 If l_txn_revenue_addl is NOT NULL Then
3401 l_txn_revenue := nvl(l_txn_revenue, 0) + v_spread_amounts(i).amount4;
3402 Else
3403 l_txn_revenue := nvl(l_txn_revenue,0) ;
3404 End If;
3405
3406 insert_rollup_tmp_with_bl(
3407 resource_assignment_rec,
3408 p_budget_version_id,
3409 v_spread_amounts(i).start_date,
3410 v_spread_amounts(i).end_date,
3411 v_spread_amounts(i).period_name,
3412 v_budget_line_id,
3413 l_quantity,
3414 l_txn_raw_cost,
3415 l_txn_burdened_cost,
3416 l_txn_revenue,
3417 v_return_status,
3418 v_msg_count,
3419 v_msg_data);
3420
3421 IF v_return_status <>
3422 FND_API.G_RET_STS_SUCCESS THEN
3423
3424 l_err_msg := v_msg_data;
3425 RAISE SPREAD_AMOUNTS_EXCEPTION;
3426
3427 END IF;
3428 l_stage := 1007;
3429 -- print_msg(l_stage||' after update rollup tmp');
3430 -- END IF; -- all amounts 0
3431 END IF;
3432 END LOOP;
3433 l_stage := 1010;
3434 print_msg(l_stage||' after update db');
3435
3436
3437
3438 ELSE -- spread based on existing distribution
3439
3440 l_stage := 1020;
3441 print_msg(l_stage||' enter spread based on existing distribution');
3442
3443 v_total_quantity := 0;
3444 v_total_raw_cost := 0;
3445 v_total_burdened_cost := 0;
3446 v_total_revenue := 0;
3447 tmp_quantity := 0;
3448 tmp_txn_raw_cost :=0;
3449 tmp_txn_burdened_cost :=0;
3450 tmp_txn_revenue := 0;
3451
3452 FOR budget_line_rec IN cur_ExistBdgtLines( v_resource_assignment_id
3453 ,v_txn_currency_code
3454 ,l_line_start_date
3455 ,l_line_end_date ) LOOP
3456
3457 --print_msg('inside loop For each record in PA_BUDGET_LINES');
3458 -- get Budget Line ID and amounts
3459 -- update amounts based on existing distribution
3460
3461
3462 l_last_budget_line_id := budget_line_rec.budget_line_id;
3463
3464 print_msg('sumtxnqty['||l_sum_txn_quantity||'LnQty['||budget_line_rec.QUANTITY||']LnInit['||budget_line_rec.INIT_QUANTITY||']');
3465 IF l_sum_txn_quantity IS NOT NULL AND
3466 l_sum_txn_quantity <> 0 THEN
3467
3468 /* bug fix:5726773 */
3469 If raRec.neg_Qty_Change_flag = 'Y' Then
3470 tmp_quantity := nvl(budget_line_rec.QUANTITY,0);
3471 Else
3472
3473 IF l_txn_quantity_addl is NOT NULL Then
3474 tmp_quantity :=
3475 (nvl(l_txn_quantity_addl,0) * ((nvl(budget_line_rec.QUANTITY,0)- nvl(budget_line_rec.INIT_QUANTITY,0)) /
3476 l_sum_txn_quantity));
3477 tmp_quantity := Round_Qty_Amts(G_rate_based_flag,'Y',G_curr_code,tmp_quantity);
3478 Else
3479 tmp_quantity := 0;
3480 End If;
3481 tmp_quantity := tmp_quantity + nvl(budget_line_rec.QUANTITY,0);
3482 tmp_quantity := Round_Qty_Amts(G_rate_based_flag,'Y',G_curr_code,tmp_quantity);
3483 --print_msg(' tmp_quantity => '||to_char(tmp_quantity));
3484 --print_msg(' v_total_quantity => '||to_char(v_total_quantity));
3485 End If;
3486 END IF;
3487
3488 IF l_sum_txn_raw_cost IS NOT NULL AND
3489 l_sum_txn_raw_cost <> 0 THEN
3490
3491 If l_txn_raw_cost_addl is NOT NULL Then
3492 tmp_txn_raw_cost :=
3493 (nvl(l_txn_raw_cost_addl,0) * nvl(budget_line_rec.TXN_RAW_COST,0) / nvl(l_sum_txn_raw_cost,0));
3494 tmp_txn_raw_cost := Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,tmp_txn_raw_cost);
3495 Else
3496 tmp_txn_raw_cost := 0;
3497 End If;
3498 tmp_txn_raw_cost := tmp_txn_raw_cost + nvl(budget_line_rec.TXN_RAW_COST,0);
3499 tmp_txn_raw_cost := Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,tmp_txn_raw_cost);
3500
3501 END IF;
3502
3503 IF l_sum_txn_burdened_cost IS NOT NULL AND
3504 l_sum_txn_burdened_cost <> 0 THEN
3505
3506 If l_txn_burdened_cost_addl is NOT NULL Then
3507 tmp_txn_burdened_cost := (nvl(l_txn_burdened_cost_addl,0) *
3508 (nvl(budget_line_rec.TXN_BURDENED_COST,0) / nvl(l_sum_txn_burdened_cost,0)));
3509 tmp_txn_burdened_cost := Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,tmp_txn_burdened_cost);
3510 Else
3511 tmp_txn_burdened_cost := 0;
3512 End if;
3513 tmp_txn_burdened_cost := tmp_txn_burdened_cost + nvl(budget_line_rec.TXN_BURDENED_COST,0);
3514 tmp_txn_burdened_cost := Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,tmp_txn_burdened_cost);
3515 END IF;
3516
3517 IF l_sum_txn_revenue IS NOT NULL AND
3518 l_sum_txn_revenue <> 0 THEN
3519
3520 If l_txn_revenue_addl is NOT NULL Then
3521 tmp_txn_revenue := (nvl(l_txn_revenue_addl,0) *
3522 (nvl(budget_line_rec.TXN_REVENUE,0) / nvl(l_sum_txn_revenue,0)));
3523 tmp_txn_revenue := Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,tmp_txn_revenue);
3524 Else
3525 tmp_txn_revenue := 0;
3526 End if;
3527 tmp_txn_revenue := tmp_txn_revenue + nvl(budget_line_rec.TXN_REVENUE,0);
3528 tmp_txn_revenue := Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,tmp_txn_revenue);
3529 END IF;
3530
3531 /*
3532 print_msg(' Before insert into insert_rollup_tmp_with_bl');
3533 print_msg(' tmp_quantity => '||to_char(tmp_quantity)||']tmp_txn_raw_cost['||tmp_txn_raw_cost||']');
3534 print_msg('tmp_txn_burdened_cost => '||tmp_txn_burdened_cost||']tmp_txn_revenue['||tmp_txn_revenue||']');
3535 */
3536 -- Insert into rollup tmp table
3537 insert_rollup_tmp_with_bl(
3538 resource_assignment_rec,
3539 p_budget_version_id,
3540 budget_line_rec.start_date,
3541 budget_line_rec.end_date,
3542 budget_line_rec.period_name,
3543 budget_line_rec.budget_line_id,
3544 tmp_quantity,
3545 tmp_txn_raw_cost,
3546 tmp_txn_burdened_cost,
3547 tmp_txn_revenue,
3548 v_return_status,
3549 v_msg_count,
3550 v_msg_data);
3551
3552 IF v_return_status <> 'S' Then
3553 l_err_msg := v_msg_data;
3554 RAISE SPREAD_AMOUNTS_EXCEPTION;
3555 END IF;
3556
3557 END LOOP; -- existing amounts, for each budget line
3558 print_msg('End of Existing line distribution Loop');
3559
3560 If l_last_budget_line_id is NOT NULL Then --{
3561 print_msg(' Adding last budget line id to plsql tab of PA_FP_ROLLUP_TMP with l_last_budget_line_id');
3562 /* getting the totals from budgetline to add final difference */
3563 g_edist_blId(NVL(g_edist_blId.LAST,0)+1) := l_last_budget_line_id;
3564 g_edist_RaId(NVL(g_edist_RaId.LAST,0)+1) := v_resource_assignment_id;
3565 g_edist_Curcode(NVL(g_edist_Curcode.LAST,0)+1) := v_txn_currency_code;
3566 g_edist_Curcode_ovr(NVL(g_edist_Curcode_ovr.LAST,0)+1) := resource_assignment_rec.txn_currency_code_override;
3567 g_edist_sdate(NVL(g_edist_sdate.LAST,0)+1) := l_line_start_date;
3568 g_edist_edate(NVL(g_edist_edate.LAST,0)+1) := l_line_end_date;
3569 g_edist_etc_sdate(NVL(g_edist_etc_sdate.LAST,0)+1) := l_g_start_date;
3570 g_edist_source_context(NVL(g_edist_source_context.LAST,0)+1) := resource_assignment_rec.source_context;
3571 g_edist_line_start_date(NVL(g_edist_line_start_date.LAST,0)+1) := resource_assignment_rec.LINE_START_DATE;
3572 g_edist_txn_quantity_addl(NVL(g_edist_txn_quantity_addl.LAST,0)+1) := resource_assignment_rec.TXN_PLAN_QUANTITY_ADDL;
3573 g_edist_txn_plan_quantity(NVL(g_edist_txn_plan_quantity.LAST,0)+1) := resource_assignment_rec.TXN_PLAN_QUANTITY;
3574 End If; --}
3575
3576 END IF; -- call spread()?
3577 ELSE -- fixed date
3578
3579 l_stage := 1040;
3580 print_msg(l_stage||' enter fixed date');
3581
3582
3583 -- when SP_FIXED_DATE is NULL,
3584 -- take LINE_START_DATE or PLAN_START_DATE
3585 -- also when SP_FIX_DATE is not NULL
3586 -- and not within LINE START/END
3587 -- or PLAN START/END DATE, report error.
3588
3589 IF resource_assignment_rec.SP_FIXED_DATE IS NOT NULL THEN
3590
3591 l_fixed_date := resource_assignment_rec.SP_FIXED_DATE;
3592 IF NOT l_fixed_date BETWEEN l_line_start_date AND l_line_end_date THEN
3593 l_err_msg := 'PA_FP_FIXED_DATE_NOT_MATCH';
3594 RAISE SPREAD_AMOUNTS_EXCEPTION;
3595 END IF;
3596 ELSE
3597 l_fixed_date := l_line_start_date;
3598 END IF;
3599 l_stage := 1041;
3600 print_msg(l_stage||' l_fixed_date '||l_fixed_date);
3601
3602 -- find fixed date period in budget lines
3603 l_fixed_date_period_count := 0;
3604 FOR budget_line_rec IN cur_spFixDateBdgtLines LOOP
3605
3606 IF l_fixed_date BETWEEN budget_line_rec.START_DATE AND budget_line_rec.END_DATE THEN
3607
3608 l_stage := 1042;
3609 print_msg(l_stage||' enter found fixed date period');
3610
3611 l_fixed_date_period_count := l_fixed_date_period_count + 1;
3612
3613 -- Insert Rollup Temporary Table
3614 IF l_g_start_date IS NOT NULL AND
3615 (budget_line_rec.init_quantity IS NOT NULL OR
3616 budget_line_rec.txn_init_raw_cost IS NOT NULL OR
3617 budget_line_rec.txn_init_burdened_cost IS NOT NULL OR
3618 budget_line_rec.txn_init_revenue IS NOT NULL) THEN
3619
3620 l_stage := 1043;
3621 print_msg(l_stage||' enter found fixed date period with init');
3622
3623 If l_txn_quantity_addl is NOT NULL Then
3624 l_sp_fixed_qty := (nvl(budget_line_rec.quantity,0) + nvl(l_txn_quantity_addl,0));
3625 Else
3626 l_sp_fixed_qty := nvl(budget_line_rec.quantity,0);
3627 End If;
3628 If l_txn_raw_cost_addl is NOT NULL Then
3629 l_sp_fixed_cost := (nvl(budget_line_rec.txn_raw_cost,0) + nvl(l_txn_raw_cost_addl,0));
3630 Else
3631 l_sp_fixed_cost := nvl(budget_line_rec.txn_raw_cost,0);
3632 End If;
3633 If l_txn_burdened_cost_addl is NOT NULL Then
3634 l_sp_fixed_burden := (nvl(budget_line_rec.txn_burdened_cost,0) + nvl(l_txn_burdened_cost_addl,0));
3635 Else
3636 l_sp_fixed_burden := nvl(budget_line_rec.txn_burdened_cost,0);
3637 End If;
3638 If l_txn_revenue_addl is NOT NULL Then
3639 l_sp_fixed_revenue := (nvl(budget_line_rec.txn_revenue,0) + nvl(l_txn_revenue_addl,0));
3640 Else
3641 l_sp_fixed_revenue := nvl(budget_line_rec.txn_revenue,0);
3642 End If;
3643
3644 insert_rollup_tmp_with_bl(
3645 resource_assignment_rec,
3646 p_budget_version_id,
3647 budget_line_rec.start_date,
3648 budget_line_rec.end_date,
3649 budget_line_rec.period_name,
3650 budget_line_rec.budget_line_id,
3651 l_sp_fixed_qty,
3652 l_sp_fixed_cost,
3653 l_sp_fixed_burden,
3654 l_sp_fixed_revenue,
3655 v_return_status,
3656 v_msg_count,
3657 v_msg_data);
3658
3659 IF v_return_status <>
3660 FND_API.G_RET_STS_SUCCESS THEN
3661 l_err_msg := v_msg_data;
3662 RAISE SPREAD_AMOUNTS_EXCEPTION;
3663
3664 END IF;
3665
3666 ELSE
3667 l_stage := 1044;
3668 print_msg(l_stage||' enter found fixed date period without init');
3669
3670
3671 insert_rollup_tmp_with_bl(
3672 resource_assignment_rec,
3673 p_budget_version_id,
3674 budget_line_rec.start_date,
3675 budget_line_rec.end_date,
3676 budget_line_rec.period_name,
3677 budget_line_rec.budget_line_id,
3678 (nvl(budget_line_rec.quantity,0) + nvl(l_txn_quantity_addl,0)),
3679 (nvl(budget_line_rec.txn_raw_cost,0) + nvl(l_txn_raw_cost_addl,0)),
3680 (nvl(budget_line_rec.txn_burdened_cost,0) + nvl(l_txn_burdened_cost_addl,0)),
3681 (nvl(budget_line_rec.txn_revenue,0) + nvl(l_txn_revenue_addl,0)),
3682 v_return_status,
3683 v_msg_count,
3684 v_msg_data);
3685
3686 IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3687 l_err_msg := v_msg_data;
3688 RAISE SPREAD_AMOUNTS_EXCEPTION;
3689
3690 END IF;
3691
3692 END IF;
3693 ELSE -- fixed date not in period
3694 l_stage := 1045;
3695 print_msg(l_stage||' enter found non fixed date period');
3696 IF l_g_start_date IS NOT NULL AND
3697 (budget_line_rec.init_quantity IS NOT NULL OR
3698 budget_line_rec.txn_init_raw_cost IS NOT NULL OR
3699 budget_line_rec.txn_init_burdened_cost IS NOT NULL OR
3700 budget_line_rec.txn_init_revenue IS NOT NULL) THEN
3701
3702 l_stage := 1046;
3703 print_msg(l_stage||' enter found non fixed date period with init');
3704 insert_rollup_tmp_with_bl(
3705 resource_assignment_rec,
3706 p_budget_version_id,
3707 budget_line_rec.start_date,
3708 budget_line_rec.end_date,
3709 budget_line_rec.period_name,
3710 budget_line_rec.budget_line_id,
3711 budget_line_rec.init_quantity,
3712 budget_line_rec.txn_init_raw_cost,
3713 budget_line_rec.txn_init_burdened_cost,
3714 budget_line_rec.txn_init_revenue,
3715 v_return_status,
3716 v_msg_count,
3717 v_msg_data);
3718
3719 IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3720 l_err_msg := v_msg_data;
3721 RAISE SPREAD_AMOUNTS_EXCEPTION;
3722
3723 END IF;
3724 ELSE
3725 l_stage := 1047;
3726 print_msg(l_stage||' enter found non fixed date period when g_start_date is null');
3727 print_msg(' or g_start_date is not null but no init, and return err');
3728 l_err_msg := 'PA_FP_FIXED_DATE_NOT_CLEAR';
3729 RAISE SPREAD_AMOUNTS_EXCEPTION;
3730 END IF;
3731 END IF;
3732
3733 END LOOP;
3734
3735 IF l_fixed_date_period_count = 0 THEN
3736 l_stage := 1048;
3737 print_msg(l_stage||' enter no fixed date period found');
3738 i := 1;
3739 LOOP
3740 EXIT WHEN i >
3741 v_spread_amounts.COUNT OR
3742 l_fixed_date BETWEEN
3743 v_spread_amounts(i).start_date
3744 AND
3745 v_spread_amounts(i).end_date;
3746 i := i + 1;
3747 END LOOP;
3748
3749 IF i > v_spread_amounts.COUNT() THEN
3750
3751 l_err_msg := 'PA_FP_FIXED_DATE_NOT_MATCH';
3752 RAISE SPREAD_AMOUNTS_EXCEPTION;
3753
3754 END IF;
3755 l_stage := 1049;
3756 print_msg(l_stage||' after get fixed date period ');
3757 print_msg(' '||v_spread_amounts(i).start_date||'/'||v_spread_amounts(i).end_date);
3758
3759 -- Insert into PA_BUDGET_LINES,
3760
3761 insert_budget_line(
3762 v_resource_assignment_id,
3763 v_spread_amounts(i).start_date,
3764 v_spread_amounts(i).end_date,
3765 v_spread_amounts(i).period_name,
3766 resource_assignment_rec.TXN_CURRENCY_CODE,
3767 resource_assignment_rec.TXN_CURRENCY_CODE_OVERRIDE,
3768 v_budget_line_id,
3769 p_budget_version_id,
3770 l_proj_curr_cd,
3771 l_projfunc_curr_cd,
3772 v_return_status,
3773 v_msg_count,
3774 v_msg_data);
3775
3776 IF v_return_status <>
3777 FND_API.G_RET_STS_SUCCESS THEN
3778 l_err_msg := v_msg_data;
3779 RAISE SPREAD_AMOUNTS_EXCEPTION;
3780
3781 END IF;
3782
3783
3784 -- Insert into Rollup Temporary Table
3785 -- INSERT INTO PA_FP_ROLLUP_TMP
3786 insert_rollup_tmp(
3787 resource_assignment_rec,
3788 p_budget_version_id,
3789 v_spread_amounts(i).start_date,
3790 v_spread_amounts(i).end_date,
3791 v_spread_amounts(i).period_name,
3792 v_budget_line_id,
3793 l_txn_quantity_addl,
3794 l_txn_raw_cost_addl,
3795 l_txn_burdened_cost_addl,
3796 l_txn_revenue_addl,
3797 v_return_status,
3798 v_msg_count,
3799 v_msg_data);
3800
3801 IF v_return_status <>
3802 FND_API.G_RET_STS_SUCCESS THEN
3803 l_err_msg := v_msg_data;
3804 RAISE SPREAD_AMOUNTS_EXCEPTION;
3805
3806 END IF;
3807
3808 END IF;
3809 l_stage := 1050;
3810 print_msg(l_stage||' after update db ');
3811
3812 END IF; -- fixed date or not
3813
3814 END IF; -- whether time phase code is R or N
3815
3816 EXCEPTION
3817 WHEN SKIP_EXCEPTION THEN
3818 NULL;
3819
3820 WHEN SPREAD_AMOUNTS_EXCEPTION THEN
3821 /* bug fix:4194475 open the cursor only when error msg needs to populated */
3822 print_msg(l_stage||' get project_name, task_name and resource_name');
3823 OPEN get_line_info(v_resource_assignment_id);
3824 FETCH get_line_info
3825 INTO l_project_name
3826 , l_task_name
3827 , l_resource_name;
3828 CLOSE get_line_info;
3829
3830 l_sprd_exception_count := l_sprd_exception_count + 1;
3831 L_FINAL_RETURN_STATUS := 'E';
3832 IF l_err_msg = 'PA_FP_BUDGET_RES_CURRENCY_NULL' THEN
3833 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3834 p_msg_name => l_err_msg
3835 );
3836 ELSE
3837 /* bug fix: 3762278 passing the incorrect msg tokens */
3838 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
3839 ,p_msg_name => l_err_msg
3840 ,p_token1 => 'L_PROJECT_NAME'
3841 ,p_value1 => l_project_name
3842 ,p_token2 => 'L_TASK_NAME'
3843 ,p_value2 => l_task_name
3844 ,p_token3 => 'L_RESOURCE_NAME'
3845 ,p_value3 => l_resource_name
3846 ,p_token4 => 'L_LINE_START_DATE'
3847 ,p_value4 => l_line_start_date
3848 ,p_token5 => 'L_LINE_END_DATE'
3849 ,p_value5 => l_line_end_date);
3850 END IF;
3851 END;
3852
3853
3854 END LOOP; --} for each resource assignment
3855
3856 /* Now Bulk insert all the budget lines */
3857 print_msg('Bulk update/Insert of budget and rollup tmp lines');
3858 blkInsertBudgetLines(x_return_status => L_FINAL_RETURN_STATUS);
3859 blkInsertFpLines(x_return_status => L_FINAL_RETURN_STATUS);
3860 blkInsertBlFpLines(x_return_status => L_FINAL_RETURN_STATUS);
3861
3862 IF NVL(L_FINAL_RETURN_STATUS,'S') = 'S' Then
3863 /* update the last rollup tmp line with rounding difference amount */
3864 IF g_edist_blId.COUNT > 0 THEN
3865 print_msg('Calling Process_Rounding_Diff API');
3866 Process_Rounding_Diff(p_budget_version_id => p_budget_version_id
3867 ,x_return_status => L_FINAL_RETURN_STATUS
3868 );
3869 print_msg('RetSts of Process_Rounding_Diff API['||L_FINAL_RETURN_STATUS||']');
3870 IF NVL(L_FINAL_RETURN_STATUS,'S') <> 'S' Then
3871 x_return_status := L_FINAL_RETURN_STATUS;
3872 End IF;
3873 END IF;
3874 ELSIF NVL(L_FINAL_RETURN_STATUS,'S') <> 'S' Then
3875 print_msg('Error occured during the spread, set the return status to E');
3876 x_return_status := L_FINAL_RETURN_STATUS;
3877 END IF;
3878
3879 If p_pa_debug_mode = 'Y' Then
3880 FOR i IN ( SELECT tmp.resource_assignment_id resAgnId
3881 ,tmp.txn_currency_code Currency
3882 ,sum(tmp.quantity) tmpqty
3883 ,sum(tmp.txn_raw_cost) tmprawcost
3884 ,sum(tmp.txn_burdened_cost) tmpburdencost
3885 ,sum(tmp.txn_revenue) tmprevenue
3886 ,sum(tmp.init_quantity) initQty
3887 ,sum(tmp.txn_init_raw_cost) initraw
3888 ,sum(tmp.txn_init_burdened_cost) initbud
3889 ,sum(tmp.txn_init_revenue) initrev
3890 ,count(*) numrows
3891 FROM pa_fp_rollup_tmp tmp
3892 WHERE tmp.budget_version_id = p_budget_version_id
3893 AND NVL(tmp.system_reference5,'N') = 'N'
3894 GROUP BY tmp.resource_assignment_id,tmp.txn_currency_code ) LOOP
3895 print_msg('Number of Records in rolluptmp after Spread['||i.numrows||']');
3896 print_msg('tmpRes['||i.resAgnId||']Cur['||i.Currency||']tmpqty['||i.tmpqty||']tmpRaw['||i.tmprawcost||']tmpbd['||i.tmpburdencost||']');
3897 print_msg('tmprev['||i.tmprevenue||']initQty['||i.initQty||']initRaw['||i.initraw||']initbud['||i.initbud||']initrev['||i.initrev||']');
3898 END LOOP;
3899 End If;
3900 l_stage := 1060;
3901 print_msg(l_stage||' **********leave spread_amounts() with retSts['||x_return_status||']');
3902
3903 /* Reset the error stack */
3904 If p_pa_debug_mode = 'Y' Then
3905 pa_debug.reset_err_stack;
3906 End If;
3907
3908 EXCEPTION
3909
3910 WHEN SPREAD_AMOUNTS_EXCEPTION THEN
3911
3912 x_return_status := FND_API.G_RET_STS_ERROR;
3913 L_FINAL_RETURN_STATUS := 'E';
3914 /* bug fix:4194475 open the cursor only when error msg needs to populated */
3915 print_msg(l_stage||' get project_name, task_name and resource_name');
3916 OPEN get_line_info(v_resource_assignment_id);
3917 FETCH get_line_info
3918 INTO l_project_name
3919 , l_task_name
3920 , l_resource_name;
3921 CLOSE get_line_info;
3922 IF l_err_msg = 'PA_FP_BUDGET_RES_CURRENCY_NULL' THEN
3923 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3924 p_msg_name => l_err_msg
3925 );
3926 ELSE
3927 /* bug fix: 3762278 passing the incorrect msg tokens */
3928 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
3929 ,p_msg_name => l_err_msg
3930 ,p_token1 => 'L_PROJECT_NAME'
3931 ,p_value1 => l_project_name
3932 ,p_token2 => 'L_TASK_NAME'
3933 ,p_value2 => l_task_name
3934 ,p_token3 => 'L_RESOURCE_NAME'
3935 ,p_value3 => l_resource_name
3936 ,p_token4 => 'L_LINE_START_DATE'
3937 ,p_value4 => l_line_start_date
3938 ,p_token5 => 'L_LINE_END_DATE'
3939 ,p_value5 => l_line_end_date);
3940 END IF;
3941
3942 x_msg_count := fnd_msg_pub.count_msg;
3943 /* BUG FIX 3632873 Retrive the msg from stack */
3944 print_msg('Retrive the msg from stack MsgCt['||x_msg_count||']');
3945 If x_msg_count = 1 then
3946 pa_interface_utils_pub.get_messages
3947 ( p_encoded => FND_API.G_TRUE
3948 ,p_msg_index => 1
3949 ,p_data => x_msg_data
3950 ,p_msg_index_out => l_msg_index_out
3951 );
3952 x_return_status := 'E';
3953 Elsif x_msg_count > 1 then
3954 x_return_status := 'E';
3955 x_msg_count := x_msg_count;
3956 x_msg_data := null;
3957 End if;
3958 print_msg('Final ReturnSts['||x_return_status||
3959 ']msgCt['||x_msg_count||']msgData['||x_msg_data||']');
3960 If p_pa_debug_mode = 'Y' Then
3961 pa_debug.reset_err_stack;
3962 End If;
3963 RETURN;
3964
3965
3966 WHEN OTHERS THEN
3967
3968 print_msg('Encountered Unexpected error from Spread API['||SQLCODE||SQLERRM);
3969 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3970 L_FINAL_RETURN_STATUS := 'U';
3971 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_FP_SPREAD_AMTS_PKG',
3972 p_procedure_name => 'spread_amounts');
3973 If p_pa_debug_mode = 'Y' Then
3974 pa_debug.reset_err_stack;
3975 End If;
3976 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3977
3978 END spread_amounts;
3979
3980
3981 END PA_FP_SPREAD_AMTS_PKG;