[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.11 2010/09/13 09:35:45 bifernan 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
1914 PROCEDURE spread_day_level( p_number_of_amounts IN INTEGER,
1915 p_amount1 IN NUMBER,
1916 p_amount2 IN NUMBER,
1917 p_amount3 IN NUMBER,
1918 p_amount4 IN NUMBER,
1919 p_amount5 IN NUMBER,
1920 p_amount6 IN NUMBER,
1921 p_amount7 IN NUMBER,
1922 p_amount8 IN NUMBER,
1923 p_amount9 IN NUMBER,
1924 p_amount10 IN NUMBER,
1925 p_start_end_date IN start_end_date_table_type,
1926 p_start_period IN INTEGER := 0,
1927 p_end_period IN INTEGER := 0,
1928 p_global_start_date IN Date,
1929 x_spread_amounts IN OUT NOCOPY spread_table_type,
1930 x_return_status OUT NOCOPY VARCHAR2,
1931 x_msg_count OUT NOCOPY NUMBER,
1932 x_msg_data OUT NOCOPY VARCHAR2) IS
1933
1934 l_start_period INTEGER;
1935 l_end_period INTEGER;
1936 --accumulated_allocation NUMBER;-- position of accumulated allocation
1937 amount_sum NUMBER;
1938 tmp_start_date DATE;
1939 tmp_end_date DATE;
1940 --tmp_rec spread_record_type;
1941 k INTEGER;
1942 j INTEGER;
1943 l_period_counter INTEGER;
1944 exit_flag BOOLEAN;
1945
1946 l_msg_count NUMBER := 0;
1947 l_data VARCHAR2(2000);
1948 l_msg_data VARCHAR2(2000);
1949 l_msg_index_out NUMBER;
1950 l_debug_mode VARCHAR2(30);
1951
1952 --l_global_actual_periods NUMBER;
1953 --l_global_allocation NUMBER;
1954 --l_global_percentage NUMBER;
1955 l_resource_assign_duration NUMBER;
1956
1957 l_stage INTEGER;
1958
1959 BEGIN
1960 l_stage := 10.1;
1961 print_msg(' '||l_stage||' enter spread_daily_level()');
1962 x_return_status := FND_API.G_RET_STS_SUCCESS;
1963 If p_pa_debug_mode = 'Y' Then
1964 pa_debug.init_err_stack('PA_FP_SPREAD_AMTS_PKG.spread_daily_level');
1965 End If;
1966
1967 fnd_profile.get('PA_DEBUG_MODE', l_debug_mode);
1968 pa_debug.set_process('PLSQL', 'LOG', l_debug_mode);
1969
1970 pa_debug.g_err_stage := 'Entered PA_FP_SPREAD_AMTS_PKG.spread_daily_level';
1971 IF P_PA_DEBUG_MODE = 'Y' THEN
1972 pa_debug.write('spread: '||g_module_name,
1973 pa_debug.g_err_stage,
1974 3);
1975 END IF;
1976
1977 l_stage := 20.1;
1978 print_msg(' '||l_stage||' p_number_of_amounts => '||p_number_of_amounts);
1979
1980 print_msg(' '||'p_amount1 => '||p_amount1);
1981 print_msg(' '||'p_amount2 => '||p_amount2);
1982 print_msg(' '||'p_amount3 => '||p_amount3);
1983 print_msg(' '||'p_amount4 => '||p_amount4);
1984 print_msg(' '||'p_amount5 => '||p_amount5);
1985 print_msg(' '||'p_amount6 => '||p_amount6);
1986 print_msg(' '||'p_amount7 => '||p_amount7);
1987 print_msg(' '||'p_amount8 => '||p_amount8);
1988 print_msg(' '||'p_amount9 => '||p_amount9);
1989 print_msg(' '||'p_amount10 => '||p_amount10);
1990
1991 -- Validating
1992
1993 l_stage := 30.1;
1994 print_msg(' '||l_stage||' before validate p_number_of_amounts');
1995 -- p_number_of_amounts cannot overflow
1996 IF NOT p_number_of_amounts BETWEEN 1 AND 10 THEN
1997
1998 x_return_status := FND_API.G_RET_STS_ERROR;
1999 x_msg_data := 'PA_FP_NUM_OF_AMTS_OVERFLOW';
2000 If p_pa_debug_mode = 'Y' Then
2001 pa_debug.reset_err_stack;
2002 End If;
2003 RETURN;
2004 END IF;
2005
2006 l_stage := 40.1;
2007 print_msg(' '||l_stage||' before validate p_start_end_date');
2008 -- p_start_end_date cannot be null and
2009 -- each start_date must earlier than end_date in p_start_end_date,
2010 -- and they cannot overlap each other.
2011 IF p_start_end_date IS NULL THEN
2012 x_return_status := FND_API.G_RET_STS_ERROR;
2013 x_msg_data := 'PA_FP_PLAN_START_END_DATE_NULL';
2014 If p_pa_debug_mode = 'Y' Then
2015 pa_debug.reset_err_stack;
2016 End If;
2017 RETURN;
2018 END IF;
2019 FOR k IN 1 .. p_start_end_date.COUNT()
2020 LOOP
2021 IF --p_start_end_date(k) IS NULL OR
2022 p_start_end_date(k).start_date IS NULL OR
2023 p_start_end_date(k).end_date IS NULL OR
2024 p_start_end_date(k).start_date >
2025 p_start_end_date(k).end_date OR
2026 k < p_start_end_date.COUNT() AND
2027 p_start_end_date(k + 1).start_date <=
2028 p_start_end_date(k).end_date THEN
2029
2030 x_return_status := FND_API.G_RET_STS_ERROR;
2031 x_msg_data := 'PA_FP_START_END_DATE_OVERLAP';
2032 If p_pa_debug_mode = 'Y' Then
2033 pa_debug.reset_err_stack;
2034 End If;
2035 RETURN;
2036 END IF;
2037 END LOOP;
2038
2039 l_stage := 60.1;
2040 print_msg(' '||l_stage||' before validate x_spread_amounts');
2041 -- x_spread_amounts cannot be NULL and
2042 -- x_spread_amounts' start end date must match with p_start_end_date.
2043 IF (x_spread_amounts IS NULL OR x_spread_amounts.COUNT() = 0 ) THEN
2044 x_return_status := FND_API.G_RET_STS_ERROR;
2045 x_msg_data := 'PA_FP_PERIODS_IS_NULL';
2046 print_msg('x_msg_data['||x_msg_data||']');
2047 If p_pa_debug_mode = 'Y' Then
2048 pa_debug.reset_err_stack;
2049 End If;
2050 RETURN;
2051 END IF;
2052 --print_msg('Count of x_spread_amounts.COUNT()['||x_spread_amounts.COUNT()||']');
2053 FOR k IN 1 .. x_spread_amounts.COUNT()
2054 LOOP
2055 IF --x_spread_amounts(k) IS NULL OR
2056 x_spread_amounts(k).start_date IS NULL OR
2057 x_spread_amounts(k).end_date IS NULL OR
2058 x_spread_amounts(k).start_date >
2059 x_spread_amounts(k).end_date OR
2060 k < x_spread_amounts.COUNT() AND
2061 x_spread_amounts(k + 1).start_date <=
2062 x_spread_amounts(k).end_date THEN
2063
2064 x_return_status := FND_API.G_RET_STS_ERROR;
2065 x_msg_data := 'PA_FP_START_END_DATE_NOT_MATCH';
2066 If p_pa_debug_mode = 'Y' Then
2067 pa_debug.reset_err_stack;
2068 End If;
2069 --print_msg('x_msg_data['||x_msg_data||']');
2070 RETURN;
2071 END IF;
2072 END LOOP;
2073 IF p_start_end_date(1).start_date >
2074 x_spread_amounts(1).end_date OR
2075 p_start_end_date(p_start_end_date.COUNT()).end_date <
2076 x_spread_amounts(x_spread_amounts.COUNT()).start_date THEN
2077
2078 x_return_status := FND_API.G_RET_STS_ERROR;
2079 x_msg_data := 'PA_FP_START_END_DATE_NOT_MATCH';
2080 --print_msg('x_msg_data['||x_msg_data||']');
2081 If p_pa_debug_mode = 'Y' Then
2082 pa_debug.reset_err_stack;
2083 End If;
2084 RETURN;
2085 END IF;
2086
2087
2088 l_stage := 70;
2089 print_msg(' '||l_stage||' before validate p_start/end_period');
2090 -- p_start_period/p_end_period validateing
2091 IF NOT (p_start_period BETWEEN 1 AND x_spread_amounts.COUNT() AND
2092 p_end_period BETWEEN 1 AND x_spread_amounts.COUNT() AND
2093 p_start_period <= p_end_period) THEN
2094
2095 x_return_status := FND_API.G_RET_STS_ERROR;
2096 x_msg_data := 'PA_FP_PERIOD_NO_MATCH';
2097 print_msg('x_msg_data['||x_msg_data||']');
2098 If p_pa_debug_mode = 'Y' Then
2099 pa_debug.reset_err_stack;
2100 End If;
2101 RETURN;
2102 END IF;
2103 l_start_period := p_start_period;
2104 l_end_period := p_end_period;
2105 l_stage := 80;
2106 print_msg(' '||l_stage||' after validation');
2107
2108 -- Calculate the number of period for each period and
2109 -- total number of period
2110
2111 FOR k IN 1 .. x_spread_amounts.COUNT()
2112 LOOP
2113 x_spread_amounts(k).actual_days := 0;
2114 END LOOP;
2115
2116
2117 k := 1;
2118 FOR j IN 1 .. x_spread_amounts.COUNT()
2119 LOOP
2120
2121 IF x_spread_amounts(j).end_date < p_start_end_date(k).start_date THEN
2122
2123 x_spread_amounts(j).actual_days := 0;
2124 x_spread_amounts(j).actual_periods := 0;
2125
2126 ELSE
2127
2128 IF p_start_end_date(k).start_date BETWEEN
2129 x_spread_amounts(j).start_date AND
2130 x_spread_amounts(j).end_date THEN
2131 tmp_start_date := p_start_end_date(k).start_date;
2132 ELSE
2133 tmp_start_date := x_spread_amounts(j).start_date;
2134 END IF;
2135 IF p_start_end_date(k).end_date BETWEEN
2136 x_spread_amounts(j).start_date AND
2137 x_spread_amounts(j).end_date THEN
2138 tmp_end_date := p_start_end_date(k).end_date;
2139 ELSE
2140 tmp_end_date := x_spread_amounts(j).end_date;
2141 END IF;
2142 x_spread_amounts(j).actual_days :=
2143 x_spread_amounts(j).actual_days +
2144 tmp_end_date - tmp_start_date + 1;
2145 x_spread_amounts(j).actual_periods :=
2146 x_spread_amounts(j).actual_days /
2147 (x_spread_amounts(j).end_date
2148 - x_spread_amounts(j).start_date + 1);
2149
2150 LOOP
2151 EXIT WHEN NOT (k < p_start_end_date.COUNT() AND
2152 p_start_end_date(k + 1).end_date <=
2153 x_spread_amounts(j).end_date);
2154
2155 k := k + 1;
2156
2157 x_spread_amounts(j).actual_days :=
2158 x_spread_amounts(j).actual_days +
2159 p_start_end_date(k).end_date -
2160 p_start_end_date(k).start_date + 1;
2161 x_spread_amounts(j).actual_periods :=
2162 x_spread_amounts(j).actual_days /
2163 (x_spread_amounts(j).end_date
2164 - x_spread_amounts(j).start_date + 1);
2165
2166 END LOOP;
2167
2168 IF k < p_start_end_date.COUNT() AND
2169 p_start_end_date(k + 1).start_date <=
2170 x_spread_amounts(j).end_date THEN
2171
2172 k := k + 1;
2173 tmp_start_date := p_start_end_date(k).start_date;
2174 tmp_end_date := x_spread_amounts(j).end_date;
2175 x_spread_amounts(j).actual_days :=
2176 x_spread_amounts(j).actual_days +
2177 tmp_end_date - tmp_start_date + 1;
2178 x_spread_amounts(j).actual_periods :=
2179 x_spread_amounts(j).actual_days /
2180 (x_spread_amounts(j).end_date
2181 - x_spread_amounts(j).start_date + 1);
2182 END IF;
2183
2184 IF k < p_start_end_date.COUNT() AND
2185 p_start_end_date(k).end_date <=
2186 x_spread_amounts(j).end_date THEN
2187 k := k + 1;
2188 END IF;
2189
2190 END IF;
2191
2192 END LOOP;
2193 /*
2194 IF p_global_start_date IS NOT NULL THEN
2195 --print_msg('end date['||x_spread_amounts(l_start_period).end_date||']StartDate['||x_spread_amounts(l_start_period).start_date||']');
2196 l_global_actual_periods :=
2197 (x_spread_amounts(l_start_period).end_date -
2198 p_global_start_date + 1) /
2199 (x_spread_amounts(l_start_period).end_date -
2200 x_spread_amounts(l_start_period).start_date + 1);
2201 --print_msg('l_global_actual_periods['||l_global_actual_periods||']');
2202 END IF;
2203 l_stage := 81;
2204 print_msg(' '||l_stage||' after calculate actual period for global start date '||round(l_global_actual_periods,2));
2205 print_msg('Actual num of periods['||x_spread_amounts(k).actual_periods||']SpCount['||x_spread_amounts.COUNT||']');
2206
2207 l_stage := 90;
2208 FOR k IN 1 .. x_spread_amounts.COUNT()
2209 LOOP
2210
2211 print_msg('p_global_start_date['||p_global_start_date||']');
2212 */
2213 -- IF p_global_start_date IS NOT NULL THEN
2214
2215 l_stage := 121;
2216 FOR r IN 1 .. p_start_end_date.COUNT() LOOP
2217
2218 print_msg('p_start_end_date index :'||r);
2219 print_msg('p_start_end_date(r).end_date'||p_start_end_date(r).end_date);
2220 print_msg('p_start_end_date(r).start_date'||p_start_end_date(r).start_date);
2221
2222 END LOOP;
2223
2224 l_resource_assign_duration := (p_start_end_date(1).end_date - p_start_end_date(1).start_date) + 1;
2225 print_msg('l_resource_assign_duration'||To_Char(l_resource_assign_duration));
2226
2227
2228 FOR k IN 1 .. x_spread_amounts.COUNT()
2229 LOOP
2230
2231 FOR j IN 1 .. p_number_of_amounts --p_amounts.COUNT()
2232 LOOP
2233
2234 x_spread_amounts(k).number_of_amounts :=
2235 p_number_of_amounts;
2236
2237 IF k BETWEEN l_start_period AND l_end_period THEN
2238 --tmp_amounts(j) := p_amounts(j) *
2239
2240 IF j = 1 THEN
2241 x_spread_amounts(k).amount1 := p_amount1 *
2242 x_spread_amounts(k).actual_days / l_resource_assign_duration ;
2243 ELSIF j = 2 THEN
2244 x_spread_amounts(k).amount2 := p_amount2 *
2245 x_spread_amounts(k).actual_days / l_resource_assign_duration ;
2246 ELSIF j = 3 THEN
2247 x_spread_amounts(k).amount3 := p_amount3 *
2248 x_spread_amounts(k).actual_days / l_resource_assign_duration ;
2249 ELSIF j = 4 THEN
2250 x_spread_amounts(k).amount4 := p_amount4 *
2251 x_spread_amounts(k).actual_days / l_resource_assign_duration ;
2252 ELSIF j = 5 THEN
2253 x_spread_amounts(k).amount5 := p_amount5 *
2254 x_spread_amounts(k).actual_days / l_resource_assign_duration ;
2255 ELSIF j = 6 THEN
2256 x_spread_amounts(k).amount6 := p_amount6 *
2257 x_spread_amounts(k).actual_days / l_resource_assign_duration ;
2258 ELSIF j = 7 THEN
2259 x_spread_amounts(k).amount7 := p_amount7 *
2260 x_spread_amounts(k).actual_days / l_resource_assign_duration ;
2261 ELSIF j = 8 THEN
2262 x_spread_amounts(k).amount8 := p_amount8 *
2263 x_spread_amounts(k).actual_days / l_resource_assign_duration ;
2264 ELSIF j = 9 THEN
2265 x_spread_amounts(k).amount9 := p_amount9 *
2266 x_spread_amounts(k).actual_days / l_resource_assign_duration ;
2267 ELSIF j = 10 THEN
2268 x_spread_amounts(k).amount10 := p_amount10 *
2269 x_spread_amounts(k).actual_days / l_resource_assign_duration ;
2270 END IF;
2271 ELSE
2272 IF j = 1 THEN x_spread_amounts(k).amount1 := 0;
2273 ELSIF j = 2 THEN x_spread_amounts(k).amount2 := 0;
2274 ELSIF j = 3 THEN x_spread_amounts(k).amount3 := 0;
2275 ELSIF j = 4 THEN x_spread_amounts(k).amount4 := 0;
2276 ELSIF j = 5 THEN x_spread_amounts(k).amount5 := 0;
2277 ELSIF j = 6 THEN x_spread_amounts(k).amount6 := 0;
2278 ELSIF j = 7 THEN x_spread_amounts(k).amount7 := 0;
2279 ELSIF j = 8 THEN x_spread_amounts(k).amount8 := 0;
2280 ELSIF j = 9 THEN x_spread_amounts(k).amount9 := 0;
2281 ELSIF j = 10 THEN x_spread_amounts(k).amount10 := 0;
2282 END IF;
2283 --tmp_amounts(j) := 0;
2284 END IF;
2285
2286 print_msg('printing x_spread_amounts values');
2287 print_msg('x_spread_amounts(k) index # '||k);
2288 print_msg('x_spread_amounts(k).actual_days amount of days in the period'||x_spread_amounts(k).actual_days);
2289 print_msg('x_spread_amounts(k).amount1 value is :'||x_spread_amounts(k).amount1);
2290 print_msg('x_spread_amounts(k).amount2 value is :'||x_spread_amounts(k).amount2);
2291 print_msg('x_spread_amounts(k).amount3 value is :'||x_spread_amounts(k).amount3);
2292 print_msg('x_spread_amounts(k).amount4 value is :'||x_spread_amounts(k).amount4);
2293
2294 END LOOP;
2295
2296
2297 END LOOP;
2298
2299 FOR k IN 1 .. x_spread_amounts.COUNT()
2300 LOOP
2301 -- make sure that amount1 is always passed with quantity
2302 x_spread_amounts(k).amount1 :=
2303 Round_Qty_Amts(G_rate_based_flag,'Y',G_curr_code,(x_spread_amounts(k).amount1));
2304 x_spread_amounts(k).amount2 :=
2305 Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,(x_spread_amounts(k).amount2));
2306 x_spread_amounts(k).amount3 :=
2307 Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,(x_spread_amounts(k).amount3));
2308 x_spread_amounts(k).amount4 :=
2309 Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,(x_spread_amounts(k).amount4));
2310 x_spread_amounts(k).amount5 :=
2311 Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,(x_spread_amounts(k).amount5));
2312 x_spread_amounts(k).amount6 :=
2313 Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,(x_spread_amounts(k).amount6));
2314 x_spread_amounts(k).amount7 :=
2315 Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,(x_spread_amounts(k).amount7));
2316 x_spread_amounts(k).amount8 :=
2317 Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,(x_spread_amounts(k).amount8));
2318 x_spread_amounts(k).amount9 :=
2319 Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,(x_spread_amounts(k).amount9));
2320 x_spread_amounts(k).amount10 :=
2321 Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,(x_spread_amounts(k).amount10));
2322 END LOOP;
2323 l_stage := 130;
2324 print_msg(' '||l_stage||' after calculate amounts');
2325
2326 -- Adjust the amounts for last period
2327 FOR k IN 1 .. p_number_of_amounts --p_amounts.COUNT()
2328 LOOP
2329
2330 amount_sum := 0;
2331 FOR j IN 1 .. x_spread_amounts.COUNT()
2332 LOOP
2333 IF k = 1 THEN
2334 amount_sum := amount_sum +
2335 nvl(x_spread_amounts(j).amount1,0);
2336 ELSIF k = 2 THEN
2337 amount_sum := amount_sum +
2338 nvl(x_spread_amounts(j).amount2,0);
2339 ELSIF k = 3 THEN
2340 amount_sum := amount_sum +
2341 nvl(x_spread_amounts(j).amount3,0);
2342 ELSIF k = 4 THEN
2343 amount_sum := amount_sum +
2344 nvl(x_spread_amounts(j).amount4,0);
2345 ELSIF k = 5 THEN
2346 amount_sum := amount_sum +
2347 nvl(x_spread_amounts(j).amount5,0);
2348 ELSIF k = 6 THEN
2349 amount_sum := amount_sum +
2350 nvl(x_spread_amounts(j).amount6,0);
2351 ELSIF k = 7 THEN
2352 amount_sum := amount_sum +
2353 nvl(x_spread_amounts(j).amount7,0);
2354 ELSIF k = 8 THEN
2355 amount_sum := amount_sum +
2356 nvl(x_spread_amounts(j).amount8,0);
2357 ELSIF k = 9 THEN
2358 amount_sum := amount_sum +
2359 nvl(x_spread_amounts(j).amount9,0);
2360 ELSIF k = 10 THEN
2361 amount_sum := amount_sum +
2362 nvl(x_spread_amounts(j).amount10,0);
2363 END IF;
2364 END LOOP;
2365
2366 /* Bug fix: 3961955 : The last period is getting updated with -ve amounts when spread curve weightage is zero
2367 * Logic: The following code is updating the last budget line with the rounding diff amount
2368 * Loop through the periodic budget lines in the reverse order. If the last period line is having zero weightage
2369 * then put the diff amounts in the previous period. If all the periods are zero weightage then put the
2370 * entire amounts/diff amounts in the Last period of the profile
2371 */
2372 IF k = 1 THEN
2373 IF (p_amount1 - amount_sum) <> 0 Then
2374 IF (p_amount1 - amount_sum) > 0 Then
2375 x_spread_amounts(l_end_period).amount1 := nvl(x_spread_amounts(l_end_period).amount1,0) +
2376 (p_amount1 - amount_sum);
2377 Else
2378 l_period_counter := l_end_period;
2379 FOR i IN REVERSE l_start_period .. l_end_period LOOP
2380 If x_spread_amounts.EXISTS(i) Then
2381 IF nvl(x_spread_amounts(i).amount1,0) <> 0 Then
2382 If (nvl(x_spread_amounts(i).amount1,0) + (p_amount1 - amount_sum)) > 0 Then
2383 x_spread_amounts(i).amount1 := nvl(x_spread_amounts(i).amount1,0) +
2384 (p_amount1 - amount_sum);
2385 x_spread_amounts(i).amount1 :=
2386 Round_Qty_Amts(G_rate_based_flag,'Y',G_curr_code,(x_spread_amounts(i).amount1));
2387 Exit;
2388 End If;
2389 End If;
2390 End If;
2391 l_period_counter := i;
2392 END LOOP;
2393 /* check all the periods are having zero weightage so put the amounts in the last period */
2394 If l_period_counter = l_start_period Then
2395 x_spread_amounts(l_end_period).amount1 := nvl(x_spread_amounts(l_end_period).amount1,0) +
2396 (p_amount1 - amount_sum);
2397 print_msg('Adding round diff makes all the lines -ve,so just put diff in first bucket');
2398 x_spread_amounts(l_end_period).amount1 :=
2399 Round_Qty_Amts(G_rate_based_flag,'Y',G_curr_code,(x_spread_amounts(l_end_period).amount1));
2400 End If;
2401 End If;
2402 End If;
2403 ELSIF k = 2 THEN
2404 IF (p_amount2 - amount_sum) <> 0 Then
2405 IF (p_amount2 - amount_sum) > 0 Then
2406 x_spread_amounts(l_end_period).amount2 := nvl(x_spread_amounts(l_end_period).amount2,0) +
2407 (p_amount2 - amount_sum);
2408 ELSE
2409 l_period_counter := l_end_period;
2410 FOR i IN REVERSE l_start_period .. l_end_period LOOP
2411 If x_spread_amounts.EXISTS(i) Then
2412 IF nvl(x_spread_amounts(i).amount2,0) <> 0 Then
2413 If(nvl(x_spread_amounts(i).amount2,0) + (p_amount2 - amount_sum)) > 0 Then
2414 x_spread_amounts(i).amount2 := nvl(x_spread_amounts(i).amount2,0) +
2415 (p_amount2 - amount_sum);
2416 Exit;
2417 End If;
2418 End If;
2419 End If;
2420 l_period_counter := i;
2421 END LOOP;
2422 /* check all the periods are having zero weightage so put the amounts in the last period */
2423 If l_period_counter = l_start_period Then
2424 x_spread_amounts(l_end_period).amount2 := nvl(x_spread_amounts(l_end_period).amount2,0) +
2425 (p_amount2 - amount_sum);
2426 End If;
2427 END IF;
2428 End If;
2429 ELSIF k = 3 THEN
2430 IF (p_amount3 - amount_sum) <> 0 Then
2431 IF (p_amount3 - amount_sum) > 0 Then
2432 x_spread_amounts(l_end_period).amount3 := nvl(x_spread_amounts(l_end_period).amount3,0) +
2433 (p_amount3 - amount_sum);
2434 ELSE
2435 l_period_counter := l_end_period;
2436 FOR i IN REVERSE l_start_period .. l_end_period LOOP
2437 If x_spread_amounts.EXISTS(i) Then
2438 IF nvl(x_spread_amounts(i).amount3,0) <> 0 Then
2439 If (nvl(x_spread_amounts(i).amount3,0)+ (p_amount3 - amount_sum)) > 0 Then
2440 x_spread_amounts(i).amount3 := nvl(x_spread_amounts(i).amount3,0) +
2441 (p_amount3 - amount_sum);
2442 Exit;
2443 End If;
2444 End If;
2445 End If;
2446 l_period_counter := i;
2447 END LOOP;
2448 /* check all the periods are having zero weightage so put the amounts in the last period */
2449 If l_period_counter = l_start_period Then
2450 x_spread_amounts(l_end_period).amount3 := nvl(x_spread_amounts(l_end_period).amount3,0) +
2451 (p_amount3 - amount_sum);
2452 End If;
2453 END IF;
2454 End If;
2455 ELSIF k = 4 THEN
2456 IF (p_amount4 - amount_sum) <> 0 Then
2457 IF (p_amount4 - amount_sum) > 0 Then
2458 x_spread_amounts(l_end_period).amount4 := nvl(x_spread_amounts(l_end_period).amount4,0) +
2459 (p_amount4 - amount_sum);
2460 ELSE
2461 l_period_counter := l_end_period;
2462 FOR i IN REVERSE l_start_period .. l_end_period LOOP
2463 If x_spread_amounts.EXISTS(i) Then
2464 IF nvl(x_spread_amounts(i).amount4,0) <> 0 Then
2465 If (nvl(x_spread_amounts(i).amount4,0) + (p_amount4 - amount_sum)) > 0 Then
2466 x_spread_amounts(i).amount4 := nvl(x_spread_amounts(i).amount4,0) +
2467 (p_amount4 - amount_sum);
2468 Exit;
2469 End If;
2470 End If;
2471 End If;
2472 l_period_counter := i;
2473 END LOOP;
2474 /* check all the periods are having zero weightage so put the amounts in the last period */
2475 If l_period_counter = l_start_period Then
2476 x_spread_amounts(l_end_period).amount4 := nvl(x_spread_amounts(l_end_period).amount4,0) +
2477 (p_amount4 - amount_sum);
2478 End If;
2479 END IF;
2480 End If;
2481 ELSIF k = 5 THEN
2482 IF (p_amount5 - amount_sum) <> 0 Then
2483 IF (p_amount5 - amount_sum) > 0 Then
2484 x_spread_amounts(l_end_period).amount5 := nvl(x_spread_amounts(l_end_period).amount5,0) +
2485 (p_amount5 - amount_sum);
2486 Else
2487 l_period_counter := l_end_period;
2488 FOR i IN REVERSE l_start_period .. l_end_period LOOP
2489 If x_spread_amounts.EXISTS(i) Then
2490 IF nvl(x_spread_amounts(i).amount5,0) <> 0 Then
2491 If (nvl(x_spread_amounts(i).amount5,0) + (p_amount5 - amount_sum)) > 0 Then
2492 x_spread_amounts(i).amount5 := nvl(x_spread_amounts(i).amount5,0) +
2493 (p_amount5 - amount_sum);
2494 Exit;
2495 End If;
2496 End If;
2497 End If;
2498 l_period_counter := i;
2499 END LOOP;
2500 /* check all the periods are having zero weightage so put the amounts in the last period */
2501 If l_period_counter = l_start_period Then
2502 x_spread_amounts(l_end_period).amount5 := nvl(x_spread_amounts(l_end_period).amount5,0) +
2503 (p_amount5 - amount_sum);
2504 End If;
2505 End If;
2506 End If;
2507 ELSIF k = 6 THEN
2508 IF (p_amount6 - amount_sum) <> 0 Then
2509 IF (p_amount6 - amount_sum) > 0 Then
2510 x_spread_amounts(l_end_period).amount6 := nvl(x_spread_amounts(l_end_period).amount6,0) +
2511 (p_amount6 - amount_sum);
2512 Else
2513 l_period_counter := l_end_period;
2514 FOR i IN REVERSE l_start_period .. l_end_period LOOP
2515 If x_spread_amounts.EXISTS(i) Then
2516 IF nvl(x_spread_amounts(i).amount6,0) <> 0 Then
2517 If (nvl(x_spread_amounts(i).amount6,0) + (p_amount6 - amount_sum)) > 0 Then
2518 x_spread_amounts(i).amount6 := nvl(x_spread_amounts(i).amount6,0) +
2519 (p_amount6 - amount_sum);
2520 Exit;
2521 End If;
2522 End If;
2523 End If;
2524 l_period_counter := i;
2525 END LOOP;
2526 /* check all the periods are having zero weightage so put the amounts in the last period */
2527 If l_period_counter = l_start_period Then
2528 x_spread_amounts(l_end_period).amount6 := nvl(x_spread_amounts(l_end_period).amount6,0) +
2529 (p_amount6 - amount_sum);
2530 End If;
2531 End If;
2532 End If;
2533 ELSIF k = 7 THEN
2534 IF (p_amount7 - amount_sum) <> 0 Then
2535 IF (p_amount7 - amount_sum) > 0 Then
2536 x_spread_amounts(l_end_period).amount7 := nvl(x_spread_amounts(l_end_period).amount7,0) +
2537 (p_amount7 - amount_sum);
2538 Else
2539 l_period_counter := l_end_period;
2540 FOR i IN REVERSE l_start_period .. l_end_period LOOP
2541 If x_spread_amounts.EXISTS(i) Then
2542 IF nvl(x_spread_amounts(i).amount7,0) <> 0 Then
2543 If (nvl(x_spread_amounts(i).amount7,0) + (p_amount7 - amount_sum)) > 0 Then
2544 x_spread_amounts(i).amount7 := nvl(x_spread_amounts(i).amount7,0) +
2545 (p_amount7 - amount_sum);
2546 Exit;
2547 End If;
2548 End If;
2549 End If;
2550 l_period_counter := i;
2551 END LOOP;
2552 /* check all the periods are having zero weightage so put the amounts in the last period */
2553 If l_period_counter = l_start_period Then
2554 x_spread_amounts(l_end_period).amount7 := nvl(x_spread_amounts(l_end_period).amount7,0) +
2555 (p_amount7 - amount_sum);
2556 End If;
2557 End If;
2558 End If;
2559 ELSIF k = 8 THEN
2560 IF (p_amount8 - amount_sum) <> 0 Then
2561 IF (p_amount8 - amount_sum) > 0 Then
2562 x_spread_amounts(l_end_period).amount8 := nvl(x_spread_amounts(l_end_period).amount8,0) +
2563 (p_amount8 - amount_sum);
2564 Else
2565 l_period_counter := l_end_period;
2566 FOR i IN REVERSE l_start_period .. l_end_period LOOP
2567 If x_spread_amounts.EXISTS(i) Then
2568 IF nvl(x_spread_amounts(i).amount8,0) <> 0 Then
2569 If (nvl(x_spread_amounts(i).amount8,0) + (p_amount8 - amount_sum)) > 0 Then
2570 x_spread_amounts(i).amount8 := nvl(x_spread_amounts(i).amount8,0) +
2571 (p_amount8 - amount_sum);
2572 Exit;
2573 End If;
2574 End If;
2575 End If;
2576 l_period_counter := i;
2577 END LOOP;
2578 /* check all the periods are having zero weightage so put the amounts in the last period */
2579 If l_period_counter = l_start_period Then
2580 x_spread_amounts(l_end_period).amount8 := nvl(x_spread_amounts(l_end_period).amount8,0) +
2581 (p_amount8 - amount_sum);
2582 End If;
2583 End If;
2584 End If;
2585 ELSIF k = 9 THEN
2586 IF (p_amount9 - amount_sum) <> 0 Then
2587 IF (p_amount9 - amount_sum) > 0 Then
2588 x_spread_amounts(l_end_period).amount9 := nvl(x_spread_amounts(l_end_period).amount9,0) +
2589 (p_amount9 - amount_sum);
2590 Else
2591 l_period_counter := l_end_period;
2592 FOR i IN REVERSE l_start_period .. l_end_period LOOP
2593 If x_spread_amounts.EXISTS(i) Then
2594 IF nvl(x_spread_amounts(i).amount9,0) <> 0 Then
2595 If (nvl(x_spread_amounts(i).amount9,0) + (p_amount9 - amount_sum)) > 0 Then
2596 x_spread_amounts(i).amount9 := nvl(x_spread_amounts(i).amount9,0) +
2597 (p_amount9 - amount_sum);
2598 Exit;
2599 End If;
2600 End If;
2601 End If;
2602 l_period_counter := i;
2603 END LOOP;
2604 /* check all the periods are having zero weightage so put the amounts in the last period */
2605 If l_period_counter = l_start_period Then
2606 x_spread_amounts(l_end_period).amount9 := nvl(x_spread_amounts(l_end_period).amount9,0) +
2607 (p_amount9 - amount_sum);
2608 End If;
2609 End If;
2610 End If;
2611 ELSIF k = 10 THEN
2612 IF (p_amount10 - amount_sum) <> 0 Then
2613 IF (p_amount10 - amount_sum) > 0 Then
2614 x_spread_amounts(l_end_period).amount10 := nvl(x_spread_amounts(l_end_period).amount10,0) +
2615 (p_amount10 - amount_sum);
2616 Else
2617 l_period_counter := l_end_period;
2618 FOR i IN REVERSE l_start_period .. l_end_period LOOP
2619 If x_spread_amounts.EXISTS(i) Then
2620 IF nvl(x_spread_amounts(i).amount10,0) <> 0 Then
2621 If (nvl(x_spread_amounts(i).amount10,0) + (p_amount10 - amount_sum)) > 0 Then
2622 x_spread_amounts(i).amount10 := nvl(x_spread_amounts(i).amount10,0) +
2623 (p_amount10 - amount_sum);
2624 Exit;
2625 End If;
2626 End If;
2627 End If;
2628 l_period_counter := i;
2629 END LOOP;
2630 /* check all the periods are having zero weightage so put the amounts in the last period */
2631 If l_period_counter = l_start_period Then
2632 x_spread_amounts(l_end_period).amount10 := nvl(x_spread_amounts(l_end_period).amount10,0) +
2633 (p_amount10 - amount_sum);
2634 End If;
2635 End If;
2636 End If;
2637 END IF;
2638
2639 END LOOP;
2640
2641 END spread_day_level;
2642
2643 PROCEDURE get_options (
2644 p_budget_version_id IN pa_budget_versions.
2645 budget_version_id%TYPE,
2646 x_period_set_name OUT NOCOPY gl_sets_of_books.
2647 period_set_name%TYPE,
2648 x_accounted_period_type OUT NOCOPY gl_sets_of_books.
2649 accounted_period_type%TYPE,
2650 x_pa_period_type OUT NOCOPY pa_implementations_all.
2651 pa_period_type%TYPE,
2652 x_time_phase_code OUT NOCOPY pa_proj_fp_options.
2653 all_time_phased_code%TYPE,
2654 x_return_status OUT NOCOPY VARCHAR2,
2655 x_msg_count OUT NOCOPY NUMBER,
2656 x_msg_data OUT NOCOPY VARCHAR2) IS
2657
2658 l_msg_count NUMBER := 0;
2659 l_data VARCHAR2(2000);
2660 l_msg_data VARCHAR2(2000);
2661 l_msg_index_out NUMBER;
2662 l_debug_mode VARCHAR2(30);
2663
2664
2665
2666 l_stage INTEGER;
2667
2668 CURSOR get_name_and_type_csr IS
2669 SELECT --gsb.period_set_name /*Start changes for bug 6156873*/
2670 decode(decode(pbv.version_type,
2671 'COST',ppfo.cost_time_phased_code,
2672 'REVENUE',ppfo.revenue_time_phased_code,
2673 ppfo.all_time_phased_code)
2674 ,'P', pia.period_set_name
2675 ,gsb.period_set_name) period_set_name /*End changes for bug 6156873*/
2676 ,gsb.accounted_period_type
2677 ,pia.pa_period_type
2678 ,decode(pbv.version_type,
2679 'COST',ppfo.cost_time_phased_code,
2680 'REVENUE',ppfo.revenue_time_phased_code,
2681 ppfo.all_time_phased_code) time_phase_code
2682 FROM gl_sets_of_books gsb
2683 ,pa_implementations_all pia
2684 ,pa_projects_all ppa
2685 ,pa_budget_versions pbv
2686 ,pa_proj_fp_options ppfo
2687 WHERE ppa.project_id = pbv.project_id
2688 AND pbv.budget_version_id = ppfo.fin_plan_version_id
2689 /* MOAC Changes: AND nvl(ppa.org_id,-99) = nvl(pia.org_id,-99) */
2690 AND ppa.org_id = pia.org_id
2691 AND gsb.set_of_books_id = pia.set_of_books_id
2692 AND pbv.budget_version_id = p_budget_version_id;
2693
2694
2695 get_name_and_type_rec get_name_and_type_csr%ROWTYPE;
2696
2697 BEGIN
2698 l_stage := 200;
2699 print_msg(' '||l_stage||' enter get_options()');
2700
2701 x_return_status := FND_API.G_RET_STS_SUCCESS;
2702 If p_pa_debug_mode = 'Y' Then
2703 pa_debug.init_err_stack( 'PA_FP_SPREAD_AMTS_PKG.get_options');
2704 pa_debug.set_process('PLSQL', 'LOG', p_pa_debug_mode);
2705 End If;
2706
2707 l_stage := 205;
2708 print_msg(l_stage||'input parameters:p_budget_version_id=> '||p_budget_version_id);
2709
2710
2711 -- get set name, period type and time phase
2712 get_name_and_type_rec := NULL;
2713 OPEN get_name_and_type_csr;
2714 FETCH get_name_and_type_csr INTO get_name_and_type_rec;
2715
2716 IF get_name_and_type_csr%NOTFOUND THEN
2717
2718 CLOSE get_name_and_type_csr;
2719
2720 x_return_status := FND_API.G_RET_STS_ERROR;
2721 x_msg_data := 'PA_FP_CANNOT_GET_TIME_PHASE';
2722 If p_pa_debug_mode = 'Y' Then
2723 pa_debug.reset_err_stack;
2724 End If;
2725 l_stage := 206;
2726 print_msg(' '||'cannot found name type');
2727 RETURN;
2728 END IF;
2729
2730 CLOSE get_name_and_type_csr;
2731
2732 x_period_set_name :=
2733 get_name_and_type_rec.period_set_name;
2734 x_accounted_period_type :=
2735 get_name_and_type_rec.accounted_period_type;
2736 x_pa_period_type :=
2737 get_name_and_type_rec.pa_period_type;
2738 x_time_phase_code :=
2739 get_name_and_type_rec.time_phase_code;
2740 l_stage := 230;
2741 If p_pa_debug_mode = 'Y' Then
2742 print_msg(' '||l_stage||' after get set name,period type,time phase');
2743 print_msg(' '||'period_set_name => '||get_name_and_type_rec.period_set_name);
2744 print_msg(' '||'accounted_period_type => '||get_name_and_type_rec.accounted_period_type);
2745 print_msg(' '||'pa_period_type => '||get_name_and_type_rec.pa_period_type);
2746 print_msg(' '||'time_phase_code => '||get_name_and_type_rec.time_phase_code);
2747 End If;
2748
2749 /* reset error stack */
2750 If p_pa_debug_mode = 'Y' Then
2751 pa_debug.reset_err_stack;
2752 End If;
2753 l_stage := 240;
2754 print_msg(' '||l_stage||' leave get options');
2755
2756 EXCEPTION
2757
2758 WHEN OTHERS THEN
2759
2760 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2761 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_FP_SPREAD_AMTS_PKG',
2762 p_procedure_name => 'get_options');
2763 If p_pa_debug_mode = 'Y' Then
2764 pa_debug.reset_err_stack;
2765 End If;
2766 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2767
2768 END get_options;
2769
2770 PROCEDURE get_periods (
2771 p_start_date IN pa_budget_lines.start_date%TYPE,
2772 p_end_date IN pa_budget_lines.end_date%TYPE,
2773 p_period_set_name IN gl_sets_of_books.
2774 period_set_name%TYPE,
2775 p_accounted_period_type IN gl_sets_of_books.
2776 accounted_period_type%TYPE,
2777 p_pa_period_type IN pa_implementations_all.
2778 pa_period_type%TYPE,
2779 p_time_phase_code IN pa_proj_fp_options.
2780 all_time_phased_code%TYPE,
2781 x_spread_amounts OUT NOCOPY spread_table_type,
2782 x_return_status OUT NOCOPY VARCHAR2,
2783 x_msg_count OUT NOCOPY NUMBER,
2784 x_msg_data OUT NOCOPY VARCHAR2) IS
2785
2786 l_msg_count NUMBER := 0;
2787 l_data VARCHAR2(2000);
2788 l_msg_data VARCHAR2(2000);
2789 l_msg_index_out NUMBER;
2790 l_debug_mode VARCHAR2(30);
2791
2792 l_period_set_name gl_sets_of_books.period_set_name%TYPE;
2793 l_accounted_period_type gl_sets_of_books.accounted_period_type%TYPE;
2794 l_pa_period_type pa_implementations_all.pa_period_type%TYPE;
2795 l_time_phase_code pa_proj_fp_options.cost_time_phased_code%TYPE;
2796 l_start_date DATE;
2797 l_end_date DATE;
2798
2799 tmp_rec spread_record_type;
2800 n INTEGER;
2801
2802 l_stage INTEGER;
2803
2804
2805 /** Explain plan for the tuned sql
2806 ================
2807 EXPLAIN PLAN IS:
2808 ================
2809 1:SELECT STATEMENT :(cost=8,rows=2)
2810 2:SORT ORDER BY :(cost=8,rows=2)
2811 3:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=2)
2812 4:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=2)
2813 **/
2814 CURSOR get_gl_periods_csr IS
2815 SELECT START_DATE, END_DATE, PERIOD_NAME
2816 FROM gl_periods gp
2817 WHERE gp.period_set_name = l_period_set_name
2818 AND gp.period_type = decode(l_time_phase_code,'G',l_accounted_period_type,'P',l_pa_period_type)
2819 AND gp.adjustment_period_flag = 'N'
2820 AND gp.start_date <= l_end_date -- plan end date
2821 AND gp.end_date >= l_start_date -- planning start date
2822 ORDER BY gp.start_date;
2823
2824 /** Performance Fix: SQL modified to avoid hitting MIN and MAX again and again
2825 ================
2826 EXPLAIN PLAN IS:
2827 ================
2828 1:SELECT STATEMENT :(cost=8,rows=1)
2829 2:FILTER :(cost=,rows=)
2830 3:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=8,rows=1)
2831 4:INDEX RANGE SCAN GL_PERIODS_N1 :(cost=2,rows=1)
2832 5:SORT AGGREGATE :(cost=,rows=1)
2833 6:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=4)
2834 7:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=4)
2835 3:SORT AGGREGATE :(cost=,rows=1)
2836 4:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=5)
2837 5:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=5)
2838 CURSOR get_gl_periods_csr IS
2839 SELECT START_DATE, END_DATE, PERIOD_NAME
2840 FROM gl_periods gp
2841 WHERE gp.period_set_name = l_period_set_name
2842 AND gp.period_type = decode(l_time_phase_code,'G',l_accounted_period_type,'P',l_pa_period_type)
2843 AND gp.start_date >=
2844 (SELECT MIN(start_date)
2845 FROM gl_periods
2846 WHERE end_date >= l_start_date
2847 AND period_set_name = l_period_set_name
2848 AND period_type =
2849 decode(l_time_phase_code,'G',
2850 l_accounted_period_type,
2851 'P',l_pa_period_type)
2852 AND adjustment_period_flag = 'N')
2853 AND gp.end_date <=
2854 (SELECT MAX(end_date)
2855 FROM gl_periods
2856 WHERE start_date <= l_end_date
2857 AND period_set_name = l_period_set_name
2858 AND period_type =
2859 decode(l_time_phase_code,'G',
2860 l_accounted_period_type,
2861 'P',l_pa_period_type)
2862 AND adjustment_period_flag = 'N')
2863 AND gp.adjustment_period_flag = 'N'
2864 ORDER BY gp.start_date;
2865 *****End of Performance fix **/
2866
2867 BEGIN
2868 l_stage := 250;
2869 print_msg(' '||l_stage||' enter get_periods()');
2870
2871 x_return_status := FND_API.G_RET_STS_SUCCESS;
2872 If p_pa_debug_mode = 'Y' Then
2873 pa_debug.init_err_stack('PA_FP_SPREAD_AMTS_PKG.get_periods');
2874 pa_debug.set_process('PLSQL', 'LOG', p_pa_debug_mode);
2875 End If;
2876
2877 l_stage := 255;
2878 print_msg(' '||'input parameters:');
2879 print_msg(' '||'p_start/end_date => '||p_start_date||'/'||p_end_date);
2880
2881 --Validation
2882
2883 l_stage := 260;
2884 print_msg(' '||l_stage||' before validate p_start/end_date');
2885 -- p_start_date must less than p_end_date
2886 IF p_start_date > p_end_date THEN
2887
2888 x_return_status := FND_API.G_RET_STS_ERROR;
2889 x_msg_data := 'PA_FP_START_END_DATE_OVERLAP';
2890 If p_pa_debug_mode = 'Y' Then
2891 pa_debug.reset_err_stack;
2892 End If;
2893 RETURN;
2894 END IF;
2895 l_stage := 270;
2896 print_msg(' '||l_stage||' after validation');
2897
2898
2899 l_period_set_name := p_period_set_name;
2900 l_accounted_period_type := p_accounted_period_type;
2901 l_pa_period_type := p_pa_period_type;
2902 l_time_phase_code := p_time_phase_code;
2903 l_stage := 280;
2904 If p_pa_debug_mode = 'Y' Then
2905 print_msg(' '||l_stage||' after assign set name,period type,time phase');
2906 print_msg(' '||'period_set_name => '||l_period_set_name);
2907 print_msg(' '||'accounted_period_type => '||l_accounted_period_type);
2908 print_msg(' '||'pa_period_type => '||l_pa_period_type);
2909 print_msg(' '||'time_phase_code => '||l_time_phase_code);
2910 End If;
2911
2912
2913 -- Get periods from gl_periods
2914
2915 l_start_date := p_start_date;
2916 l_end_date := p_end_date;
2917 x_spread_amounts := spread_table_type();
2918 n := 0;
2919 FOR rec IN get_gl_periods_csr
2920 LOOP
2921 --print_msg('inside get_gl_periods_csr for SD['||rec.start_date||']');
2922 n := n + 1;
2923 x_spread_amounts.EXTEND();
2924 tmp_rec.start_date := rec.start_date;
2925 tmp_rec.end_date := rec.end_date;
2926 tmp_rec.period_name := rec.period_name;
2927 tmp_rec.actual_days := 0;
2928 x_spread_amounts(n) := tmp_rec;
2929 END LOOP;
2930
2931 IF n = 0 AND (l_time_phase_code = 'G' OR l_time_phase_code = 'P') THEN
2932 x_return_status := FND_API.G_RET_STS_ERROR;
2933 x_msg_data := 'PA_FP_PERIODS_IS_NULL';
2934 If p_pa_debug_mode = 'Y' Then
2935 pa_debug.reset_err_stack;
2936 End If;
2937 RETURN;
2938 END IF;
2939 If p_pa_debug_mode = 'Y' Then
2940 pa_debug.reset_err_stack;
2941 End If;
2942 l_stage := 290;
2943 print_msg(' '||l_stage||' leave get period');
2944
2945 EXCEPTION
2946
2947 WHEN OTHERS THEN
2948
2949 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2950 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_FP_SPREAD_AMTS_PKG',
2951 p_procedure_name => 'get_periods');
2952 If p_pa_debug_mode = 'Y' Then
2953 pa_debug.reset_err_stack;
2954 End If;
2955 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2956
2957 END get_periods;
2958
2959
2960 PROCEDURE spread_amounts
2961 ( p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
2962 ,x_return_status OUT NOCOPY VARCHAR2
2963 ,x_msg_count OUT NOCOPY NUMBER
2964 ,x_msg_data OUT NOCOPY VARCHAR2) IS
2965
2966 l_msg_count NUMBER := 0;
2967 l_data VARCHAR2(2000);
2968 l_msg_data VARCHAR2(2000);
2969 l_msg_index_out NUMBER;
2970 l_debug_mode VARCHAR2(30);
2971
2972 v_return_status VARCHAR2(3);
2973 v_msg_count NUMBER;
2974 v_msg_data VARCHAR2(2000);
2975
2976 l_stage INTEGER;
2977
2978 l_err_msg VARCHAR2(2000);
2979
2980 l_project_name pa_projects_all.name%TYPE;
2981 l_task_name pa_proj_elements.name%TYPE;
2982 l_resource_name pa_resource_list_members.alias%TYPE;
2983 l_proj_curr_cd pa_projects_all.project_currency_code%TYPE;
2984 l_projfunc_curr_cd pa_projects_all.projfunc_currency_code%TYPE;
2985
2986 v_spread_amounts spread_table_type;
2987 v_spread_curve spread_curve_type;
2988 v_start_end_date start_end_date_table_type;
2989 v_start_end start_end_date_record_type;
2990 i INTEGER;
2991 bl_exist BOOLEAN;
2992
2993 --l_time_phase_code pa_proj_fp_options.all_time_phased_code%TYPE;
2994 l_line_start INTEGER;
2995 l_line_end INTEGER;
2996 l_line_start_date DATE;
2997 l_line_end_date DATE;
2998 l_plan_start_date DATE;
2999 l_plan_end_date DATE;
3000 l_budget_line_time_phase_count INTEGER;
3001 l_fixed_date DATE;
3002 l_fixed_date_period_count INTEGER;
3003 l_last_budget_line_id pa_budget_lines.budget_line_id%TYPE;
3004 l_spread_curve_id pa_spread_curves_b.spread_curve_id%TYPE;
3005 v_budget_line_id pa_budget_lines.budget_line_id%TYPE;
3006
3007 v_resource_assignment_id pa_budget_lines.resource_assignment_id%TYPE;
3008 v_txn_currency_code pa_budget_lines.txn_currency_code%TYPE;
3009
3010 v_total_quantity pa_budget_lines.quantity%TYPE;
3011 v_total_raw_cost pa_budget_lines.raw_cost%TYPE;
3012 v_total_burdened_cost pa_budget_lines.burdened_cost%TYPE;
3013 v_total_revenue pa_budget_lines.revenue%TYPE;
3014
3015 l_sum_txn_quantity pa_budget_lines.quantity%TYPE;
3016 l_avg_raw_cost_rate pa_budget_lines.txn_standard_cost_rate%TYPE;
3017 l_avg_raw_cost_rate_override pa_budget_lines.txn_cost_rate_override%TYPE;
3018 l_sum_txn_raw_cost pa_budget_lines.raw_cost%TYPE;
3019 l_avg_burden_cost_rate pa_budget_lines.burden_cost_rate%TYPE;
3020 l_avg_burden_cost_rate_ovrid pa_budget_lines.
3021 burden_cost_rate_override%TYPE;
3022 l_sum_txn_burdened_cost pa_budget_lines.burdened_cost%TYPE;
3023 l_avg_bill_rate pa_budget_lines.txn_standard_bill_rate%TYPE;
3024 l_avg_bill_rate_override pa_budget_lines.txn_bill_rate_override%TYPE;
3025 l_sum_txn_revenue pa_budget_lines.revenue%TYPE;
3026
3027 tmp_quantity NUMBER;
3028 tmp_txn_raw_cost NUMBER;
3029 tmp_txn_burdened_cost NUMBER;
3030 tmp_txn_revenue NUMBER;
3031
3032 l_quantity pa_budget_lines.quantity%TYPE;
3033 l_txn_raw_cost pa_budget_lines.raw_cost%TYPE;
3034 l_txn_burdened_cost pa_budget_lines.burdened_cost%TYPE;
3035 l_txn_revenue pa_budget_lines.revenue%TYPE;
3036
3037 l_g_start_date DATE;
3038 l_g_init_quantity pa_budget_lines.init_quantity%TYPE;
3039 l_g_txn_init_raw_cost pa_budget_lines.txn_init_raw_cost%TYPE;
3040 l_g_txn_init_burdened_cost pa_budget_lines.txn_init_burdened_cost%TYPE;
3041 l_g_txn_init_revenue pa_budget_lines.txn_init_revenue%TYPE;
3042 l_g_sum_etc_quantity pa_budget_lines.init_quantity%TYPE;
3043 l_g_sum_txn_etc_raw_cost pa_budget_lines.txn_init_raw_cost%TYPE;
3044 l_g_sum_txn_etc_burdened_cost pa_budget_lines.txn_init_burdened_cost%TYPE;
3045 l_g_sum_txn_etc_revenue pa_budget_lines.txn_init_revenue%TYPE;
3046 l_g_bl_init_count INTEGER;
3047
3048 l_g_bl_count INTEGER;
3049 l_g_sum_txn_quantity pa_budget_lines.quantity%TYPE;
3050 l_g_sum_txn_raw_cost pa_budget_lines.txn_raw_cost%TYPE;
3051 l_g_sum_txn_burdened_cost pa_budget_lines.txn_burdened_cost%TYPE;
3052 l_g_sum_txn_revenue pa_budget_lines.txn_revenue%TYPE;
3053
3054 l_txn_quantity_addl pa_fp_res_assignments_tmp.
3055 TXN_PLAN_QUANTITY%TYPE;
3056 l_txn_raw_cost_addl pa_fp_res_assignments_tmp.
3057 TXN_RAW_COST%TYPE;
3058 l_txn_burdened_cost_addl pa_fp_res_assignments_tmp.
3059 TXN_BURDENED_COST%TYPE;
3060 l_txn_revenue_addl pa_fp_res_assignments_tmp.
3061 TXN_REVENUE%TYPE;
3062
3063 l_bl_count INTEGER;
3064 l_dummy_count INTEGER;
3065 l_bl_line_id Number;
3066 l_dummy_bl_id Number;
3067
3068 l_period_set_name gl_sets_of_books.period_set_name%TYPE;
3069 l_accounted_period_type gl_sets_of_books.accounted_period_type%TYPE;
3070 l_pa_period_type pa_implementations_all.pa_period_type%TYPE;
3071 l_time_phase_code pa_proj_fp_options.cost_time_phased_code%TYPE;
3072
3073 /* bug fix:5726773 */
3074 l_neg_qty_er_flag VARCHAR2(1);
3075
3076 /* This cursor is used for fixed date spread curve */
3077 CURSOR cur_spFixDateBdgtLines IS
3078 SELECT RESOURCE_ASSIGNMENT_ID,
3079 START_DATE,
3080 END_DATE,
3081 PERIOD_NAME,
3082 QUANTITY,
3083 TXN_RAW_COST,
3084 TXN_BURDENED_COST,
3085 TXN_REVENUE,
3086 INIT_QUANTITY,
3087 TXN_INIT_RAW_COST,
3088 TXN_INIT_BURDENED_COST,
3089 TXN_INIT_REVENUE,
3090 TXN_CURRENCY_CODE,
3091 BUDGET_LINE_ID,
3092 BUDGET_VERSION_ID
3093 FROM PA_BUDGET_LINES
3094 WHERE RESOURCE_ASSIGNMENT_ID = v_resource_assignment_id
3095 AND TXN_CURRENCY_CODE = v_txn_currency_code
3096 AND START_DATE BETWEEN l_line_start_date AND l_line_end_date
3097 AND END_DATE BETWEEN l_line_start_date AND l_line_end_date
3098 AND PERIOD_NAME IS NOT NULL
3099 ORDER BY START_DATE;
3100
3101
3102 /* This cursor is used for Existing Line Distributioin Method */
3103 CURSOR cur_ExistBdgtLines(p_resAsgnId Number
3104 ,p_txn_cur_code Varchar2
3105 ,p_line_start_date Date
3106 ,p_line_end_date Date ) IS
3107 SELECT RESOURCE_ASSIGNMENT_ID,
3108 START_DATE,
3109 END_DATE,
3110 PERIOD_NAME,
3111 QUANTITY,
3112 TXN_RAW_COST,
3113 TXN_BURDENED_COST,
3114 TXN_REVENUE,
3115 INIT_QUANTITY,
3116 TXN_INIT_RAW_COST,
3117 TXN_INIT_BURDENED_COST,
3118 TXN_INIT_REVENUE,
3119 TXN_CURRENCY_CODE,
3120 BUDGET_LINE_ID,
3121 BUDGET_VERSION_ID
3122 FROM PA_BUDGET_LINES
3123 WHERE RESOURCE_ASSIGNMENT_ID = p_resAsgnId
3124 AND TXN_CURRENCY_CODE = p_txn_cur_code
3125 AND START_DATE BETWEEN p_line_start_date AND p_line_end_date
3126 AND END_DATE BETWEEN p_line_start_date AND p_line_end_date
3127 AND PERIOD_NAME IS NOT NULL
3128 ORDER BY START_DATE;
3129
3130 /* This Cursor is used for Non-Time phase budgets */
3131 CURSOR budget_line_time_phase_csr IS
3132 SELECT BUDGET_LINE_ID
3133 FROM PA_BUDGET_LINES
3134 WHERE RESOURCE_ASSIGNMENT_ID = v_resource_assignment_id
3135 AND TXN_CURRENCY_CODE = v_txn_currency_code
3136 --AND START_DATE = l_plan_start_date bug 6339811
3137 --AND END_DATE = l_plan_end_date bug 6339811
3138 AND PERIOD_NAME IS NULL;
3139
3140 budget_line_time_phase_rec budget_line_time_phase_csr%ROWTYPE;
3141
3142 -- notes: for periodic page
3143 CURSOR resource_assignment_csr IS
3144 SELECT RESOURCE_ASSIGNMENT_ID
3145 ,BUDGET_VERSION_ID
3146 ,PROJECT_ID
3147 ,TASK_ID
3148 ,RESOURCE_LIST_MEMBER_ID
3149 ,PLANNING_START_DATE
3150 ,PLANNING_END_DATE
3151 ,SPREAD_CURVE_ID
3152 ,SP_FIXED_DATE
3153 ,TXN_CURRENCY_CODE
3154 ,TXN_CURRENCY_CODE_OVERRIDE
3155 ,PROJECT_CURRENCY_CODE
3156 ,PROJFUNC_CURRENCY_CODE
3157 ,TXN_REVENUE
3158 ,TXN_REVENUE_ADDL
3159 ,TXN_RAW_COST
3160 ,TXN_RAW_COST_ADDL
3161 ,TXN_BURDENED_COST
3162 ,TXN_BURDENED_COST_ADDL
3163 ,TXN_PLAN_QUANTITY
3164 ,TXN_PLAN_QUANTITY_ADDL
3165 ,LINE_START_DATE
3166 ,LINE_END_DATE
3167 ,SOURCE_CONTEXT
3168 ,RAW_COST_RATE
3169 ,RW_COST_RATE_OVERRIDE
3170 ,BURDEN_COST_RATE
3171 ,BURDEN_COST_RATE_OVERRIDE
3172 ,BILL_RATE
3173 ,BILL_RATE_OVERRIDE
3174 ,RATE_BASED_FLAG
3175 ,SPREAD_AMOUNTS_FLAG
3176 ,INIT_QUANTITY
3177 ,TXN_INIT_RAW_COST
3178 ,TXN_INIT_BURDENED_COST
3179 ,TXN_INIT_REVENUE
3180 /* Bug fix:5726773 : Added the following columns to store the negative quantity/amt change flags*/
3181 ,NVL(NEG_QUANTITY_CHANGE_FLAG,'N') neg_Qty_Change_flag
3182 ,NVL(NEG_RAWCOST_CHANGE_FLAG,'N') neg_RawCst_Change_flag
3183 ,NVL(NEG_BURDEN_CHANGE_FALG,'N') neg_BurdCst_Change_flag
3184 ,NVL(NEG_REVENUE_CHANGE_FLAG,'N') neg_rev_Change_flag
3185 FROM PA_FP_RES_ASSIGNMENTS_TMP tmp
3186 WHERE tmp.BUDGET_VERSION_ID = p_budget_version_id
3187 AND (NVL(tmp.TXN_PLAN_QUANTITY_ADDL,0) <> 0
3188 OR NVL(tmp.TXN_RAW_COST_ADDL,0) <> 0
3189 OR NVL(tmp.TXN_BURDENED_COST_ADDL,0) <> 0
3190 OR NVL(tmp.TXN_REVENUE_ADDL,0) <> 0
3191 );
3192 /* Now the spread is called in bulk mode
3193 AND RESOURCE_ASSIGNMENT_ID = p_res_assignment_id
3194 AND TXN_CURRENCY_CODE = p_txn_currency_code
3195 AND ((p_line_start_date IS NULL and p_line_end_date IS NULL)
3196 OR
3197 (p_line_start_date IS NOT NULL and p_line_end_date IS NOT NULL
3198 and LINE_START_DATE = p_line_start_date
3199 and LINE_END_DATE = p_line_end_date)
3200 );
3201 */
3202
3203 resource_assignment_rec resource_assignment_rec_type;
3204
3205 CURSOR spread_curve_csr IS
3206 SELECT POINT1,
3207 POINT2,
3208 POINT3,
3209 POINT4,
3210 POINT5,
3211 POINT6,
3212 POINT7,
3213 POINT8,
3214 POINT9,
3215 POINT10,
3216 SPREAD_CURVE_CODE
3217 FROM PA_SPREAD_CURVES_B
3218 WHERE SPREAD_CURVE_ID = l_spread_curve_id;
3219 spread_curve_b_rec spread_curve_csr%ROWTYPE;
3220
3221
3222 CURSOR get_line_info (p_resource_assignment_id IN NUMBER) IS
3223 SELECT ppa.name project_name
3224 ,pt.name task_name
3225 ,prl.alias resource_name
3226 FROM pa_projects_all ppa
3227 ,pa_proj_elements pt
3228 ,pa_resource_list_members prl
3229 ,pa_resource_assignments pra
3230 WHERE pra.resource_assignment_id = p_resource_assignment_id
3231 AND ppa.project_id = pra.project_id
3232 AND pt.proj_element_id(+) = pra.task_id
3233 AND prl.resource_list_member_id = pra.resource_list_member_id;
3234
3235 /* performance bug fix: 4100256 */
3236 CURSOR bl_details(p_resAsgnId Number
3237 ,p_txn_cur_code Varchar2
3238 ,p_start_date Date
3239 ,p_end_date Date
3240 ,p_source_context Varchar2) IS
3241 SELECT sum(bl.quantity)
3242 ,sum(bl.txn_raw_cost)
3243 ,sum(bl.txn_burdened_cost)
3244 ,sum(bl.txn_revenue)
3245 ,min(bl.budget_line_id)
3246 ,decode(min(bl.budget_line_id),NULL,0,1) NumOfBudgetLines
3247 ,sum(NVL(bl.quantity,0)-NVL(bl.init_quantity,0)) Etc_Quantity
3248 FROM pa_budget_lines bl
3249 WHERE bl.resource_assignment_id = p_resAsgnId
3250 AND bl.txn_currency_code = p_txn_cur_code
3251 AND bl.START_DATE BETWEEN p_start_date AND p_end_date
3252 AND bl.END_DATE BETWEEN p_start_date AND p_end_date ;
3253
3254 SPREAD_AMOUNTS_EXCEPTION EXCEPTION;
3255 SKIP_EXCEPTION EXCEPTION;
3256
3257 l_sprd_exception_count Number :=0;
3258 l_sp_fixed_qty Number;
3259 l_sp_fixed_cost Number;
3260 l_sp_fixed_burden Number;
3261 l_sp_fixed_revenue Number;
3262 v_bl_total_quantity Number := 0;
3263 v_bl_total_raw_cost Number := 0;
3264 v_bl_total_burdened_cost Number :=0;
3265 v_bl_total_revenue Number := 0;
3266
3267 L_FINAL_RETURN_STATUS varchar2(10) := 'S';
3268
3269 BEGIN
3270 l_stage := 800;
3271 print_msg(l_stage||' *** ENTERED SPREAD AMOUNTS API***');
3272 x_return_status := 'S';
3273 L_FINAL_RETURN_STATUS := 'S';
3274 If p_pa_debug_mode = 'Y' Then
3275 pa_debug.init_err_stack('PA_FP_SPREAD_AMTS_PKG.spread_amounts');
3276 End If;
3277 /* Bug fix: 4078623 Both set_curr_fun and Init_err_stack are similar, since reset_curr_function is not called
3278 * This might have been causing the plsql numeric or value error. Not sure because of this. Just to avoid
3279 * confusions commenting out this call
3280 * PA_DEBUG.Set_Curr_Function( p_function => 'PA_FP_SPREAD_AMTS_PKG.spread_amounts' ,p_debug_mode => 'Y');
3281 */
3282
3283 -- validation
3284 IF ( p_budget_version_id IS NULL ) THEN
3285 l_err_msg := 'PA_FP_BUDGET_RES_CURRENCY_NULL';
3286 RAISE SPREAD_AMOUNTS_EXCEPTION;
3287 END IF;
3288
3289 -- get options
3290 print_msg('Deriving finplan option information for the budget version');
3291 get_options( p_budget_version_id,
3292 l_period_set_name,
3293 l_accounted_period_type,
3294 l_pa_period_type,
3295 l_time_phase_code,
3296 v_return_status,
3297 v_msg_count,
3298 v_msg_data);
3299
3300 l_stage := 805;
3301 print_msg(l_stage||'after get_options');
3302 IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3303 l_stage := 806;
3304 print_msg(l_stage||' get_options() err MsgData['||v_msg_data||']');
3305 l_err_msg := v_msg_data;
3306 RAISE SPREAD_AMOUNTS_EXCEPTION;
3307
3308 END IF;
3309
3310 /* call to initialize the global tables */
3311 Initialize_spread_plsqlTabs;
3312
3313 -- For each resource assignment record in global temporary table
3314 -- based on budget_version_id, resource_assignment_id and
3315 -- txn currency code
3316 -- main looooooooooooop starts here
3317 FOR raRec IN resource_assignment_csr LOOP --{
3318
3319 BEGIN
3320 /* set ETC start date from RA */
3321 l_g_start_date := PA_FP_CALC_PLAN_PKG.g_spread_from_date;
3322 print_msg('ETC l_g_start_date['||l_g_start_date||']l_line_end_date['||l_line_end_date||']');
3323
3324 /* Without changing much of the code, assiging the loop variable values to record*/
3325 resource_assignment_rec.RESOURCE_ASSIGNMENT_ID := raRec.RESOURCE_ASSIGNMENT_ID;
3326 resource_assignment_rec.BUDGET_VERSION_ID := raRec.BUDGET_VERSION_ID;
3327 resource_assignment_rec.PROJECT_ID := raRec.PROJECT_ID;
3328 resource_assignment_rec.TASK_ID := raRec.TASK_ID;
3329 resource_assignment_rec.RESOURCE_LIST_MEMBER_ID := raRec.RESOURCE_LIST_MEMBER_ID;
3330 resource_assignment_rec.PLANNING_START_DATE := raRec.PLANNING_START_DATE;
3331 resource_assignment_rec.PLANNING_END_DATE := raRec.PLANNING_END_DATE;
3332 resource_assignment_rec.SPREAD_CURVE_ID := raRec.SPREAD_CURVE_ID;
3333 resource_assignment_rec.SP_FIXED_DATE := raRec.SP_FIXED_DATE;
3334 resource_assignment_rec.TXN_CURRENCY_CODE := raRec.TXN_CURRENCY_CODE;
3335 resource_assignment_rec.TXN_CURRENCY_CODE_OVERRIDE := raRec.TXN_CURRENCY_CODE_OVERRIDE;
3336 resource_assignment_rec.PROJECT_CURRENCY_CODE := raRec.PROJECT_CURRENCY_CODE;
3337 resource_assignment_rec.PROJFUNC_CURRENCY_CODE := raRec.PROJFUNC_CURRENCY_CODE;
3338 resource_assignment_rec.TXN_REVENUE := raRec.TXN_REVENUE;
3339 resource_assignment_rec.TXN_REVENUE_ADDL := raRec.TXN_REVENUE_ADDL;
3340 resource_assignment_rec.TXN_RAW_COST := raRec.TXN_RAW_COST;
3341 resource_assignment_rec.TXN_RAW_COST_ADDL := raRec.TXN_RAW_COST_ADDL;
3342 resource_assignment_rec.TXN_BURDENED_COST := raRec.TXN_BURDENED_COST;
3343 resource_assignment_rec.TXN_BURDENED_COST_ADDL := raRec.TXN_BURDENED_COST_ADDL;
3344 resource_assignment_rec.TXN_PLAN_QUANTITY := raRec.TXN_PLAN_QUANTITY;
3345 resource_assignment_rec.TXN_PLAN_QUANTITY_ADDL := raRec.TXN_PLAN_QUANTITY_ADDL;
3346 resource_assignment_rec.LINE_START_DATE := raRec.LINE_START_DATE;
3347 resource_assignment_rec.LINE_END_DATE := raRec.LINE_END_DATE;
3348 resource_assignment_rec.SOURCE_CONTEXT := raRec.SOURCE_CONTEXT;
3349 resource_assignment_rec.RAW_COST_RATE := raRec.RAW_COST_RATE;
3350 resource_assignment_rec.RAW_COST_RATE_OVERRIDE := raRec.RW_COST_RATE_OVERRIDE;
3351 resource_assignment_rec.BURDEN_COST_RATE := raRec.BURDEN_COST_RATE;
3352 resource_assignment_rec.BURDEN_COST_RATE_OVERRIDE := raRec.BURDEN_COST_RATE_OVERRIDE;
3353 resource_assignment_rec.BILL_RATE := raRec.BILL_RATE;
3354 resource_assignment_rec.BILL_RATE_OVERRIDE := raRec.BILL_RATE_OVERRIDE;
3355 resource_assignment_rec.RATE_BASED_FLAG := raRec.RATE_BASED_FLAG;
3356 resource_assignment_rec.SPREAD_AMOUNTS_FLAG := raRec.SPREAD_AMOUNTS_FLAG;
3357 resource_assignment_rec.INIT_QUANTITY := raRec.INIT_QUANTITY;
3358 resource_assignment_rec.TXN_INIT_RAW_COST := raRec.TXN_INIT_RAW_COST;
3359 resource_assignment_rec.TXN_INIT_BURDENED_COST := raRec.TXN_INIT_BURDENED_COST;
3360 resource_assignment_rec.TXN_INIT_REVENUE := raRec.TXN_INIT_REVENUE;
3361
3362 -- Get resource assignment id and txn currency code
3363 v_resource_assignment_id := resource_assignment_rec.RESOURCE_ASSIGNMENT_ID;
3364 v_txn_currency_code := resource_assignment_rec.TXN_CURRENCY_CODE;
3365 G_rate_based_flag := NVL(resource_assignment_rec.RATE_BASED_FLAG,'N');
3366 G_Curr_code := NVL(resource_assignment_rec.TXN_CURRENCY_CODE_OVERRIDE,resource_assignment_rec.TXN_CURRENCY_CODE);
3367 l_proj_curr_cd := resource_assignment_rec.PROJECT_CURRENCY_CODE;
3368 l_projfunc_curr_cd := resource_assignment_rec.PROJFUNC_CURRENCY_CODE;
3369
3370 /* Bug fix:4030310 if etc start date is less than line start date the set the etc date as null */
3371 IF l_g_start_date IS NOT NULL AND trunc(l_g_start_date) < trunc(resource_assignment_rec.PLANNING_START_DATE)THEN
3372 l_stage := 810.1;
3373 print_msg(l_stage||'set though date to null because earlier than plan start_date');
3374 l_g_start_date := NULL;
3375 END IF;
3376
3377 l_stage := 820;
3378 If p_pa_debug_mode = 'Y' Then
3379 print_msg(l_stage||' input data:');
3380 print_msg('ra_resource_assignment_id => '||resource_assignment_rec.resource_assignment_id);
3381 print_msg('ra_BUDGET_VERSION_ID => '||resource_assignment_rec.BUDGET_VERSION_ID);
3382 print_msg('ra_PLANNING_START/END_DATE => '||resource_assignment_rec.PLANNING_START_DATE||'/'||resource_assignment_rec.PLANNING_END_DATE);
3383 print_msg('ra_SPREAD_CURVE_ID/FIXED_DATE => '||resource_assignment_rec.SPREAD_CURVE_ID||'/'||resource_assignment_rec.SP_FIXED_DATE);
3384 print_msg('ra_TXN_CURRENCY_CODE/OVERRIDE => '||resource_assignment_rec.TXN_CURRENCY_CODE||'/'||resource_assignment_rec.TXN_CURRENCY_CODE_OVERRIDE);
3385 print_msg('ra_TXN_REVENUE/ADDL => '||resource_assignment_rec.TXN_REVENUE||'/'||resource_assignment_rec.TXN_REVENUE_ADDL);
3386 print_msg('ra_TXN_RAW_COST/ADDL => '||resource_assignment_rec.TXN_RAW_COST||'/'||resource_assignment_rec.TXN_RAW_COST_ADDL);
3387 print_msg('ra_TXN_BURDENED_COST/ADDL => '||resource_assignment_rec.TXN_BURDENED_COST||'/'||resource_assignment_rec.TXN_BURDENED_COST_ADDL);
3388 print_msg('ra_TXN_PLAN_QUANTITY/ADDL => '||resource_assignment_rec.TXN_PLAN_QUANTITY||'/'||resource_assignment_rec.TXN_PLAN_QUANTITY_ADDL);
3389 print_msg('ra_SOURCE_CONTEXT => '||resource_assignment_rec.SOURCE_CONTEXT);
3390 print_msg('ra_LINE_START/END_DATE => '||resource_assignment_rec.LINE_START_DATE||'/'||resource_assignment_rec.LINE_END_DATE);
3391 print_msg('ra_RAW_COST_RATE/OVERRIDE => '||resource_assignment_rec.RAW_COST_RATE||'/'||resource_assignment_rec.RAW_COST_RATE_OVERRIDE);
3392 print_msg('ra_BURDEN_COST_RATE/OVERRIDE => '||resource_assignment_rec.BURDEN_COST_RATE||'/'||resource_assignment_rec.BURDEN_COST_RATE_OVERRIDE);
3393 print_msg('ra_BILL_RATE/OVERRIDE => '||resource_assignment_rec.BILL_RATE||'/'||resource_assignment_rec.BILL_RATE_OVERRIDE);
3394 print_msg('ra_RATE_BASED_FLAG => '||G_rate_based_flag);
3395 print_msg('ra_SPREAD_AMOUNTS_FLAG => '||resource_assignment_rec.SPREAD_AMOUNTS_FLAG);
3396 print_msg('neg_Qty_Change_flag => '||raRec.neg_Qty_Change_flag);
3397 End If;
3398
3399 /*** Bug fix:4194475 execute only when there is error so moved to exception block
3400 l_stage := 821;
3401 print_msg(l_stage||' get project_name, task_name and resource_name');
3402 OPEN get_line_info(v_resource_assignment_id);
3403 FETCH get_line_info
3404 INTO l_project_name
3405 , l_task_name
3406 , l_resource_name;
3407 CLOSE get_line_info;
3408 ****/
3409
3410 -- validate resource assignment record
3411 IF ( resource_assignment_rec.PLANNING_START_DATE IS NULL OR
3412 resource_assignment_rec.PLANNING_END_DATE IS NULL) OR
3413 ( resource_assignment_rec.PLANNING_START_DATE >
3414 resource_assignment_rec.PLANNING_END_DATE ) OR
3415 (resource_assignment_rec.SOURCE_CONTEXT = 'BUDGET_LINE' AND
3416 ((resource_assignment_rec.LINE_START_DATE IS NULL OR
3417 resource_assignment_rec.LINE_END_DATE IS NULL) OR
3418 (resource_assignment_rec.LINE_START_DATE >
3419 resource_assignment_rec.LINE_END_DATE
3420 ))) THEN
3421
3422 IF resource_assignment_rec.SOURCE_CONTEXT = 'BUDGET_LINE' THEN
3423 l_line_start_date := resource_assignment_rec.LINE_START_DATE;
3424 l_line_end_date := resource_assignment_rec.LINE_END_DATE;
3425 ELSE
3426 l_line_start_date := resource_assignment_rec.PLANNING_START_DATE;
3427 l_line_end_date := resource_assignment_rec.PLANNING_END_DATE;
3428 END IF;
3429
3430 l_err_msg := 'PA_FP_PLAN_START_END_DATE_ERR';
3431 RAISE SPREAD_AMOUNTS_EXCEPTION;
3432
3433 END IF;
3434
3435
3436 -- skip record when all the amouns are null
3437
3438 IF ( resource_assignment_rec.TXN_PLAN_QUANTITY_ADDL IS NULL AND
3439 resource_assignment_rec.TXN_RAW_COST_ADDL IS NULL AND
3440 resource_assignment_rec.TXN_BURDENED_COST_ADDL IS NULL AND
3441 resource_assignment_rec.TXN_REVENUE_ADDL IS NULL ) THEN
3442 l_stage := 841;
3443 print_msg(l_stage||' all amounts are null, skip the resource assignment');
3444 RAISE SKIP_EXCEPTION;
3445 END IF;
3446
3447
3448
3449 -- Note: 1. if plan start/end date shift, all budget lines beyond
3450 -- the new plan state/end date will be
3451 -- deleted before calling spread_amounts()
3452 -- 2. if budget line(s) is(are) there, it's not allowed to
3453 -- change the time phase code - from N/R to G/P or from G/P
3454 -- to N/R or from G to P or from P to G etc.
3455 -- 3. line_start/end_date must at begin/end of period
3456
3457 l_stage := 860;
3458 print_msg('Deriving period information for the budget version');
3459 get_periods(
3460 NVL(l_g_start_date,resource_assignment_rec.PLANNING_START_DATE),
3461 resource_assignment_rec.PLANNING_END_DATE,
3462 l_period_set_name,
3463 l_accounted_period_type,
3464 l_pa_period_type,
3465 l_time_phase_code,
3466 v_spread_amounts,
3467 v_return_status,
3468 v_msg_count,
3469 v_msg_data);
3470 print_msg(l_stage||' after get periods retSts['||v_return_status||']v_spread_amounts.coount['||v_spread_amounts.COUNT||']');
3471 IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3472 l_err_msg := v_msg_data;
3473 RAISE SPREAD_AMOUNTS_EXCEPTION;
3474 END IF;
3475
3476 IF p_pa_debug_mode = 'Y' Then
3477 IF ( v_spread_amounts.COUNT > 0
3478 AND l_time_phase_code IN ('P','G')) THEN
3479
3480 FOR i IN v_spread_amounts.first.. v_spread_amounts.last LOOP
3481 IF (i = v_spread_amounts.first OR i = v_spread_amounts.last) THEN
3482 NULL;
3483 print_msg('start/end date '||i||'=> '||v_spread_amounts(i).start_date||'/'||v_spread_amounts(i).end_date);
3484 END IF;
3485 END LOOP;
3486 END IF;
3487 END IF;
3488
3489 -- updated 030204 Sgoteti
3490 IF l_time_phase_code IN ('P','G')
3491 AND resource_assignment_rec.SOURCE_CONTEXT = 'BUDGET_LINE'
3492 AND ((resource_assignment_rec.LINE_END_DATE <
3493 v_spread_amounts(1).start_date )
3494 OR
3495 ( v_spread_amounts(v_spread_amounts.COUNT()).end_date <
3496 resource_assignment_rec.LINE_START_DATE)) THEN
3497
3498 l_stage := 863;
3499 print_msg(l_stage||' line start/end date miss the planning period, skip the resource assignment');
3500 RAISE SKIP_EXCEPTION;
3501 END IF;
3502
3503 /* Initialize line start and line end date with planning SD and ED*/
3504 l_line_start_date := NVL(l_g_start_date,resource_assignment_rec.PLANNING_START_DATE);
3505 l_line_end_date := resource_assignment_rec.PLANNING_END_DATE;
3506
3507 -- updated 030204 Sgoteti
3508 IF l_time_phase_code IN ('P','G') THEN
3509 l_line_start_date := v_spread_amounts(1).start_date;
3510 l_line_end_date := v_spread_amounts(v_spread_amounts.count()).end_date;
3511
3512 IF resource_assignment_rec.SOURCE_CONTEXT = 'BUDGET_LINE' THEN
3513 IF resource_assignment_rec.LINE_START_DATE > l_line_start_date THEN
3514 -- resource_assignment_rec.PLANNING_START_DATE THEN
3515 l_line_start_date := resource_assignment_rec.LINE_START_DATE;
3516 END IF;
3517 IF resource_assignment_rec.LINE_END_DATE < l_line_end_date THEN
3518 -- resource_assignment_rec.PLANNING_END_DATE THEN
3519 l_line_end_date := resource_assignment_rec.LINE_END_DATE;
3520 END IF;
3521 END IF;
3522
3523 -- updated 030204 Sgoteti
3524 END IF;
3525
3526 l_stage := 845;
3527 print_msg(l_stage||' after get line start/end date '||l_line_start_date||'/'||l_line_end_date);
3528
3529 /* Get budget line amounts for the given resource */
3530 BEGIN
3531 l_stage := 520;
3532 print_msg(l_stage||' before get sum of amounts from budget line');
3533 /* Initialize the budget line varaibles */
3534 l_sum_txn_quantity := NULL;
3535 l_sum_txn_raw_cost := NULL;
3536 l_sum_txn_burdened_cost := NULL;
3537 l_sum_txn_revenue := NULL;
3538 l_bl_line_id := NULL;
3539 l_bl_count := 0;
3540 OPEN bl_details(v_resource_assignment_id
3541 ,v_txn_currency_code
3542 ,l_line_start_date
3543 ,l_line_end_date
3544 ,resource_assignment_rec.SOURCE_CONTEXT) ;
3545 FETCH bl_details INTO
3546 l_sum_txn_quantity
3547 ,l_sum_txn_raw_cost
3548 ,l_sum_txn_burdened_cost
3549 ,l_sum_txn_revenue
3550 ,l_bl_line_id
3551 ,l_bl_count
3552 ,l_g_sum_etc_quantity;
3553 CLOSE bl_details;
3554 /* set the linecount variable to zero if its null or the cursor not found */
3555 IF l_bl_count is NULL Then
3556 l_bl_count := 0;
3557 End If;
3558 print_msg(l_stage||'l_bl_count['||l_bl_count||']');
3559
3560
3561 EXCEPTION
3562 WHEN NO_DATA_FOUND THEN
3563 null;
3564 END;
3565 l_stage := 530;
3566 If p_pa_debug_mode = 'Y' Then
3567 print_msg(l_stage||' Before spread Amts from Budget Line l_time_phase_code '||l_time_phase_code);
3568 print_msg(' l_sum_txn_quantity '||l_sum_txn_quantity||']l_sum_txn_raw_cost['||l_sum_txn_raw_cost||']');
3569 print_msg(' l_sum_txn_burdened_cost['||l_sum_txn_burdened_cost||']l_sum_txn_revenue['||l_sum_txn_revenue||']');
3570 End If;
3571
3572 l_stage := 880;
3573 -- when time phase code is R or N
3574 IF (l_time_phase_code = 'R' OR l_time_phase_code = 'N') THEN
3575
3576 l_stage := 890;
3577 print_msg(l_stage||' enter time phase is R or N');
3578 /* Bug fixL: 3877889 For Non-Timephase budgets PlanSDate should be considiered
3579 -- l_plan_start_date := NVL(l_g_start_date,resource_assignment_rec.PLANNING_START_DATE);
3580 */
3581 l_plan_start_date := resource_assignment_rec.PLANNING_START_DATE;
3582 l_plan_end_date := resource_assignment_rec.PLANNING_END_DATE;
3583 budget_line_time_phase_rec := NULL;
3584 OPEN budget_line_time_phase_csr;
3585 FETCH budget_line_time_phase_csr
3586 INTO budget_line_time_phase_rec;
3587 l_budget_line_time_phase_count := budget_line_time_phase_csr%ROWCOUNT;
3588 CLOSE budget_line_time_phase_csr;
3589 print_msg('l_budget_line_time_phase_count['||l_budget_line_time_phase_count||']');
3590
3591 IF l_budget_line_time_phase_count = 0 THEN
3592 --print_msg('Inserting records into budget line for l_budget_line_time_phase_count = 0');
3593 -- Insert into PA_BUDGET_LINES,
3594 insert_budget_line(
3595 v_resource_assignment_id,
3596 resource_assignment_rec.PLANNING_START_DATE,
3597 resource_assignment_rec.PLANNING_END_DATE,
3598 NULL,
3599 resource_assignment_rec.TXN_CURRENCY_CODE,
3600 resource_assignment_rec.TXN_CURRENCY_CODE_OVERRIDE,
3601 v_budget_line_id,
3602 p_budget_version_id,
3603 l_proj_curr_cd,
3604 l_projfunc_curr_cd,
3605 v_return_status,
3606 v_msg_count,
3607 v_msg_data);
3608
3609 IF v_return_status <> 'S' Then
3610 l_err_msg := v_msg_data;
3611 RAISE SPREAD_AMOUNTS_EXCEPTION;
3612 END IF;
3613
3614 --print_msg('Inserting records into rollup tmp for l_budget_line_time_phase_count = 0');
3615 insert_rollup_tmp(
3616 resource_assignment_rec,
3617 p_budget_version_id,
3618 resource_assignment_rec.PLANNING_START_DATE,
3619 resource_assignment_rec.PLANNING_END_DATE,
3620 NULL,
3621 v_budget_line_id,
3622 resource_assignment_rec.TXN_PLAN_QUANTITY,
3623 resource_assignment_rec.TXN_RAW_COST,
3624 resource_assignment_rec.TXN_BURDENED_COST,
3625 resource_assignment_rec.TXN_REVENUE,
3626 v_return_status,
3627 v_msg_count,
3628 v_msg_data);
3629
3630 IF v_return_status <> 'S' Then
3631 l_err_msg := v_msg_data;
3632 RAISE SPREAD_AMOUNTS_EXCEPTION;
3633 END IF;
3634
3635 ELSIF l_budget_line_time_phase_count = 1 THEN
3636 --print_msg('Inserting records into rollup tmp for l_budget_line_time_phase_count = 1');
3637 -- Insert Rollup Temporary Table
3638 insert_rollup_tmp_with_bl(
3639 resource_assignment_rec,
3640 p_budget_version_id,
3641 resource_assignment_rec.PLANNING_START_DATE,
3642 resource_assignment_rec.PLANNING_END_DATE,
3643 NULL,
3644 budget_line_time_phase_rec.budget_line_id,
3645 resource_assignment_rec.TXN_PLAN_QUANTITY,
3646 resource_assignment_rec.TXN_RAW_COST,
3647 resource_assignment_rec.TXN_BURDENED_COST,
3648 resource_assignment_rec.TXN_REVENUE,
3649 v_return_status,
3650 v_msg_count,
3651 v_msg_data);
3652
3653 IF v_return_status <> 'S' Then
3654 l_err_msg := v_msg_data;
3655 RAISE SPREAD_AMOUNTS_EXCEPTION;
3656 END IF;
3657 ELSE
3658 l_err_msg := 'FA_FP_MULTI_NON_PERIOD';
3659 RAISE SPREAD_AMOUNTS_EXCEPTION;
3660 END IF;
3661
3662 ELSE -- time phase code is not R or N
3663 l_stage := 850;
3664 print_msg(l_stage||' Entered time phase code is G or P');
3665 -- get spread curve
3666 l_spread_curve_id := resource_assignment_rec.SPREAD_CURVE_ID;
3667 spread_curve_b_rec := NULL;
3668 OPEN spread_curve_csr;
3669 FETCH spread_curve_csr
3670 INTO spread_curve_b_rec;
3671 IF spread_curve_csr%ROWCOUNT = 1 THEN
3672 v_spread_curve := spread_curve_type(
3673 spread_curve_b_rec.POINT1,
3674 spread_curve_b_rec.POINT2,
3675 spread_curve_b_rec.POINT3,
3676 spread_curve_b_rec.POINT4,
3677 spread_curve_b_rec.POINT5,
3678 spread_curve_b_rec.POINT6,
3679 spread_curve_b_rec.POINT7,
3680 spread_curve_b_rec.POINT8,
3681 spread_curve_b_rec.POINT9,
3682 spread_curve_b_rec.POINT10);
3683 ELSE
3684 v_spread_curve := spread_curve_type
3685 (10, 10, 10, 10, 10, 10, 10, 10, 10, 10);
3686 END IF;
3687 CLOSE spread_curve_csr;
3688 l_stage := 850;
3689 If p_pa_debug_mode = 'Y' Then
3690 print_msg(l_stage||' after get spread curve');
3691 print_msg(' '||'spread curve => '||v_spread_curve(1)||' '||v_spread_curve(2)||' '||v_spread_curve(3));
3692 print_msg(' '||v_spread_curve(4)||' '||v_spread_curve(5)||' '||v_spread_curve(6)||' '||v_spread_curve(7));
3693 print_msg(' '||v_spread_curve(8)||' '||v_spread_curve(9)||' '||v_spread_curve(10));
3694 End If;
3695
3696 IF ( l_g_start_date IS NOT NULL
3697 AND ( l_g_start_date > l_line_end_date ))THEN
3698 -- v_spread_amounts(v_spread_amounts.COUNT).end_date
3699 IF l_g_start_date > resource_assignment_rec.PLANNING_END_DATE THEN
3700 l_stage := 910;
3701 print_msg(l_stage||'enter though date after plan end date');
3702 print_msg(l_stage||'ETC start date is greater than planning end date');
3703 BEGIN
3704 bl_exist := TRUE;
3705 i := v_spread_amounts.COUNT;
3706 l_quantity := NULL;
3707 l_txn_raw_cost := NULL;
3708 l_txn_burdened_cost := NULL;
3709 l_txn_revenue := NULL;
3710 v_budget_line_id := NULL;
3711 l_dummy_count := 0;
3712 OPEN bl_details(v_resource_assignment_id
3713 ,v_txn_currency_code
3714 ,v_spread_amounts(i).start_date
3715 ,v_spread_amounts(i).end_date
3716 ,resource_assignment_rec.SOURCE_CONTEXT);
3717 FETCH bl_details INTO
3718 l_quantity
3719 ,l_txn_raw_cost
3720 ,l_txn_burdened_cost
3721 ,l_txn_revenue
3722 ,v_budget_line_id
3723 ,l_dummy_count
3724 ,l_g_sum_etc_quantity;
3725 CLOSE bl_details;
3726 /* set the linecount variable to zero if its null or the cursor not found */
3727 IF l_dummy_count is NULL Then
3728 l_dummy_count := 0;
3729 End If;
3730 If l_dummy_count = 0 Then
3731 bl_exist := FALSE;
3732 End If;
3733
3734 EXCEPTION
3735 WHEN NO_DATA_FOUND THEN
3736 bl_exist := FALSE;
3737 END;
3738
3739 IF bl_exist = FALSE THEN
3740 print_msg('Budget line not exists');
3741 IF (( resource_assignment_rec.TXN_PLAN_QUANTITY_ADDL IS NOT NULL
3742 AND resource_assignment_rec.TXN_PLAN_QUANTITY_ADDL <> 0 )
3743 OR ( resource_assignment_rec.TXN_RAW_COST_ADDL IS NOT NULL
3744 AND resource_assignment_rec.TXN_RAW_COST_ADDL <> 0)
3745 OR (resource_assignment_rec.TXN_BURDENED_COST_ADDL IS NOT NULL
3746 AND resource_assignment_rec.TXN_BURDENED_COST_ADDL <> 0)
3747 OR (resource_assignment_rec.TXN_REVENUE_ADDL IS NOT NULL
3748 AND resource_assignment_rec.TXN_REVENUE_ADDL <> 0)) THEN
3749
3750 insert_budget_line(
3751 v_resource_assignment_id,
3752 v_spread_amounts(v_spread_amounts.COUNT).start_date,
3753 v_spread_amounts(v_spread_amounts.COUNT).end_date,
3754 v_spread_amounts(v_spread_amounts.COUNT).period_name,
3755 v_txn_currency_code,
3756 resource_assignment_rec.TXN_CURRENCY_CODE_OVERRIDE,
3757 v_budget_line_id,
3758 p_budget_version_id,
3759 l_proj_curr_cd,
3760 l_projfunc_curr_cd,
3761 v_return_status,
3762 v_msg_count,
3763 v_msg_data);
3764
3765 IF v_return_status <> 'S' Then
3766 l_err_msg := v_msg_data;
3767 RAISE SPREAD_AMOUNTS_EXCEPTION;
3768 END IF;
3769
3770 l_stage := 1004;
3771 -- print_msg(l_stage||' after insert budget line');
3772 -- Insert into Rollup Temporary Table
3773 insert_rollup_tmp(
3774 resource_assignment_rec,
3775 p_budget_version_id,
3776 v_spread_amounts(v_spread_amounts.COUNT).start_date,
3777 v_spread_amounts(v_spread_amounts.COUNT).end_date,
3778 v_spread_amounts(v_spread_amounts.COUNT).period_name,
3779 v_budget_line_id,
3780 resource_assignment_rec.TXN_PLAN_QUANTITY_ADDL,
3781 resource_assignment_rec.TXN_RAW_COST_ADDL,
3782 resource_assignment_rec.TXN_BURDENED_COST_ADDL,
3783 resource_assignment_rec.TXN_REVENUE_ADDL,
3784 v_return_status,
3785 v_msg_count,
3786 v_msg_data);
3787
3788 IF v_return_status <> 'S' Then
3789 l_err_msg := v_msg_data;
3790 RAISE SPREAD_AMOUNTS_EXCEPTION;
3791 END IF;
3792 END IF;
3793
3794 ELSE -- budget line exists
3795
3796 insert_rollup_tmp_with_bl(
3797 resource_assignment_rec,
3798 p_budget_version_id,
3799 v_spread_amounts(v_spread_amounts.COUNT).start_date,
3800 v_spread_amounts(v_spread_amounts.COUNT).end_date,
3801 v_spread_amounts(v_spread_amounts.COUNT).period_name,
3802 v_budget_line_id,
3803 nvl(l_quantity,0) + resource_assignment_rec.TXN_PLAN_QUANTITY_ADDL,
3804 nvl(l_txn_raw_cost,0) + resource_assignment_rec.TXN_RAW_COST_ADDL,
3805 nvl(l_txn_burdened_cost,0) + resource_assignment_rec.TXN_BURDENED_COST_ADDL,
3806 nvl(l_txn_revenue,0) + resource_assignment_rec.TXN_REVENUE_ADDL,
3807 v_return_status,
3808 v_msg_count,
3809 v_msg_data);
3810
3811 IF v_return_status <> 'S' Then
3812 l_err_msg := v_msg_data;
3813 RAISE SPREAD_AMOUNTS_EXCEPTION;
3814 END IF;
3815 END IF;
3816 END IF;
3817 RAISE SKIP_EXCEPTION;
3818 END IF; -- end of etc start date is greater than planning end date
3819
3820 /* if etc start date is less than line start date the set the etc date as null */
3821 IF l_g_start_date IS NOT NULL AND l_g_start_date < l_line_start_date THEN
3822 l_stage := 920;
3823 print_msg(l_stage||'set though date to null because earlier than line start_date');
3824 l_g_start_date := NULL;
3825 END IF;
3826
3827 IF l_g_start_date IS NOT NULL THEN
3828 FOR i IN 1 .. v_spread_amounts.COUNT LOOP
3829 IF (( i > 1)
3830 AND l_g_start_date > v_spread_amounts(i - 1).end_date
3831 AND l_g_start_date < v_spread_amounts(i).start_date) THEN
3832 l_g_start_date := v_spread_amounts(i).start_date;
3833 l_stage := 930;
3834 print_msg(l_stage||'set though date to begin of next period because fall between periods '||l_g_start_date);
3835 END IF;
3836 IF l_g_start_date BETWEEN v_spread_amounts(i).start_date
3837 AND v_spread_amounts(i).end_date THEN
3838 l_line_start_date := v_spread_amounts(i).start_date;
3839 l_stage := 940;
3840 print_msg(l_stage||' set line start date to begin of period though date falls '||l_line_start_date);
3841 END IF;
3842 END LOOP;
3843 END IF;
3844
3845 IF l_g_start_date IS NOT NULL THEN
3846 /* get budget line amounts for the period */
3847 l_g_sum_txn_quantity := NULL;
3848 l_g_sum_txn_raw_cost := NULL;
3849 l_g_sum_txn_burdened_cost := NULL;
3850 l_g_sum_txn_revenue := NULL;
3851 l_dummy_bl_id := NULL;
3852 l_g_bl_count := 0;
3853 OPEN bl_details(v_resource_assignment_id
3854 ,v_txn_currency_code
3855 ,l_line_start_date
3856 ,l_line_end_date
3857 ,resource_assignment_rec.SOURCE_CONTEXT);
3858 FETCH bl_details INTO
3859 l_g_sum_txn_quantity
3860 ,l_g_sum_txn_raw_cost
3861 ,l_g_sum_txn_burdened_cost
3862 ,l_g_sum_txn_revenue
3863 ,l_dummy_bl_id
3864 ,l_g_bl_count
3865 ,l_g_sum_etc_quantity;
3866 CLOSE bl_details;
3867 If l_g_bl_count is NULL Then
3868 l_g_bl_count := 0;
3869 End If;
3870 l_stage := 950;
3871 print_msg(l_stage||' get sum of amounts from though date to line end date l_g_bl_count '||l_g_bl_count);
3872
3873 END IF;
3874
3875 l_txn_quantity_addl := resource_assignment_rec.TXN_PLAN_QUANTITY_ADDL;
3876 l_txn_raw_cost_addl := resource_assignment_rec.TXN_RAW_COST_ADDL;
3877 l_txn_burdened_cost_addl := resource_assignment_rec.TXN_BURDENED_COST_ADDL;
3878 l_txn_revenue_addl := resource_assignment_rec.TXN_REVENUE_ADDL;
3879 print_msg('960l_txn_quantity_addl['||l_txn_quantity_addl||']l_txn_raw_cost_addl['||l_txn_raw_cost_addl||']');
3880 print_msg('l_txn_burdened_cost_addl['||l_txn_burdened_cost_addl||']l_txn_revenue_addl['||l_txn_revenue_addl||']');
3881
3882 IF l_g_start_date IS NOT NULL THEN
3883 print_msg('setting the l_sum variables');
3884 l_sum_txn_quantity := l_g_sum_Etc_quantity;
3885 l_sum_txn_raw_cost := l_g_sum_txn_raw_cost ;
3886 l_sum_txn_burdened_cost := l_g_sum_txn_burdened_cost;
3887 l_sum_txn_revenue := l_g_sum_txn_revenue;
3888 END IF;
3889 l_stage := 970;
3890 If p_pa_debug_mode = 'Y' Then
3891 print_msg(l_stage||' get amounts addl plus etc');
3892 print_msg('l_txn_quantity_addl '||l_txn_quantity_addl||']l_txn_raw_cost_addl['||l_txn_raw_cost_addl||']');
3893 print_msg('l_txn_burdened_cost_addl '||l_txn_burdened_cost_addl||']l_txn_revenue_addl['||l_txn_revenue_addl||']');
3894 print_msg('l_sum_txn_quantity['||l_sum_txn_quantity||']l_sum_txn_raw_cost['||l_sum_txn_raw_cost||']');
3895 End If;
3896
3897 -- When spread curve's SPREAD_CURVE_CODE
3898 -- is not FIXED
3899 IF NOT (spread_curve_b_rec.SPREAD_CURVE_CODE IS NOT NULL
3900 AND spread_curve_b_rec.SPREAD_CURVE_CODE = 'FIXED_DATE') THEN
3901 l_stage := 980;
3902 print_msg(l_stage||' Entered spread curve code is NOT Fixed date');
3903
3904 l_stage := 1025;
3905 print_msg(l_stage||' sum of budget lines minus etc');
3906
3907 /* bug fix:5726773 : negative quantity spread
3908 * when budget lines exists and sum of total quantity is zero then
3909 * distributing the quantity based on existing line distribution method fails with
3910 * divide by zero error. In order to avoid this spread the quantity based on the
3911 * spread curve. This is proposed by PMs
3912 */
3913 l_neg_qty_er_flag := 'N';
3914 If l_bl_count <> 0
3915 AND l_txn_quantity_addl <> 0
3916 AND ((l_sum_txn_quantity = 0 and l_g_start_date IS NULL)
3917 OR (l_g_sum_txn_quantity = 0 and l_g_start_date IS NOT NULL)
3918 OR (l_g_bl_count = 0 and l_g_sum_Etc_quantity = 0
3919 and l_g_sum_txn_quantity <> 0 and l_g_start_date IS NOT NULL)) Then
3920 l_neg_qty_er_flag := 'Y';
3921 End If;
3922
3923 -- if need call spread()
3924 IF ( l_bl_count = 0
3925 OR (l_g_start_date IS NOT NULL AND l_g_bl_count = 0)
3926 OR (resource_assignment_rec.SPREAD_AMOUNTS_FLAG = 'Y')
3927 OR l_neg_qty_er_flag = 'Y' ) THEN /* bug fix:5726773 */
3928
3929 l_stage := 990;
3930 print_msg(l_stage||' enter spread or respread');
3931 -- set start/end date
3932 v_start_end_date := start_end_date_table_type();
3933 v_start_end.start_date := NVL(l_g_start_date,resource_assignment_rec.PLANNING_START_DATE);
3934 v_start_end.end_date := resource_assignment_rec.PLANNING_END_DATE;
3935 v_start_end_date.EXTEND();
3936 v_start_end_date(1) := v_start_end;
3937
3938 -- set line start/end period
3939 FOR i IN 1 .. v_spread_amounts.COUNT() LOOP
3940 IF l_line_start_date BETWEEN v_spread_amounts(i).start_date
3941 AND v_spread_amounts(i).end_date THEN
3942 l_line_start := i;
3943 END IF;
3944 IF l_line_end_date BETWEEN v_spread_amounts(i).start_date
3945 AND v_spread_amounts(i).end_date THEN
3946 l_line_end := i;
3947 END IF;
3948 END LOOP;
3949
3950 -- change to support fiscal calendar distribution. call proc srepad if spread_code <> "FISCAL CALENDAR" otherwise call spread_day_level
3951 -- Bug 9848673
3952 -- IF spread_curve_b_rec.SPREAD_CURVE_CODE <> 'FISCAL_CALENDAR' THEN
3953 IF ( spread_curve_b_rec.spread_curve_code IS NOT NULL AND
3954 spread_curve_b_rec.spread_curve_code <> 'FISCAL_CALENDAR' ) OR
3955 ( spread_curve_b_rec.spread_curve_code IS NULL )
3956 THEN
3957 /* Calling Spread api to calculate the amounts,qty and burdened cost to spread across periods*/
3958 print_msg('CALLING SPREAD api');
3959 spread(4,
3960 l_txn_quantity_addl,
3961 l_txn_raw_cost_addl,
3962 l_txn_burdened_cost_addl,
3963 l_txn_revenue_addl,
3964 0,
3965 0,
3966 0,
3967 0,
3968 0,
3969 0,
3970 v_start_end_date,
3971 v_spread_curve,
3972 l_line_start,
3973 l_line_end,
3974 l_g_start_date,
3975 v_spread_amounts,
3976 v_return_status,
3977 v_msg_count,
3978 v_msg_data);
3979
3980 ELSE
3981 --Spread As Daily Level Distribution
3982 /* Calling Spread api to calculate the amounts,qty and burdened cost to spread across periods by day level*/
3983
3984
3985 spread_day_level(4,
3986 l_txn_quantity_addl,
3987 l_txn_raw_cost_addl,
3988 l_txn_burdened_cost_addl,
3989 l_txn_revenue_addl,
3990 0,
3991 0,
3992 0,
3993 0,
3994 0,
3995 0,
3996 v_start_end_date,
3997 l_line_start,
3998 l_line_end,
3999 l_g_start_date,
4000 v_spread_amounts,
4001 v_return_status,
4002 v_msg_count,
4003 v_msg_data);
4004
4005 END IF;
4006
4007 IF v_return_status <> 'S' Then
4008 l_err_msg := v_msg_data;
4009 RAISE SPREAD_AMOUNTS_EXCEPTION;
4010 END IF;
4011
4012 l_stage := 1000;
4013 If p_pa_debug_mode = 'Y' Then
4014 print_msg(l_stage||' after call spread()');
4015 FOR i IN 1 .. v_spread_amounts.COUNT() LOOP
4016 IF i = l_line_start OR i = l_line_end THEN
4017 NULL;
4018 print_msg('start/end date '||i||'=> '||v_spread_amounts(i).start_date||'/'||v_spread_amounts(i).end_date);
4019 print_msg('txn quantity '||i||'=> '||v_spread_amounts(i).amount1);
4020 print_msg('txn raw cost '||i||'=> '||v_spread_amounts(i).amount2);
4021 print_msg('txn burdened cost '||i||'=> '||v_spread_amounts(i).amount3);
4022 print_msg('txn revenue '||i||'=> '||v_spread_amounts(i).amount4);
4023 END IF;
4024 END LOOP;
4025 End If;
4026
4027
4028 l_stage := 1001;
4029 print_msg(l_stage||' before update loop');
4030 FOR i IN l_line_start .. l_line_end LOOP
4031
4032 bl_exist := TRUE;
4033
4034 BEGIN
4035 l_quantity := NULL;
4036 l_txn_raw_cost := NULL;
4037 l_txn_burdened_cost := NULL;
4038 l_txn_revenue := NULL;
4039 v_budget_line_id := NULL;
4040 l_dummy_count := 0;
4041 OPEN bl_details(v_resource_assignment_id
4042 ,v_txn_currency_code
4043 ,v_spread_amounts(i).start_date
4044 ,v_spread_amounts(i).end_date
4045 ,resource_assignment_rec.SOURCE_CONTEXT);
4046 FETCH bl_details INTO
4047 l_quantity
4048 ,l_txn_raw_cost
4049 ,l_txn_burdened_cost
4050 ,l_txn_revenue
4051 ,v_budget_line_id
4052 ,l_dummy_count
4053 ,l_g_sum_etc_quantity;
4054 CLOSE bl_details;
4055 /* set the linecount variable to zero if its null or the cursor not found */
4056 IF l_dummy_count is NULL Then
4057 l_dummy_count := 0;
4058 End If;
4059 If l_dummy_count = 0 Then
4060 bl_exist := FALSE;
4061 End If;
4062
4063 EXCEPTION
4064 WHEN NO_DATA_FOUND THEN
4065 bl_exist := FALSE;
4066 END;
4067
4068
4069 IF NOT bl_exist THEN
4070
4071 l_stage := 1003;
4072 --print_msg(l_stage||' enter budget line id not found ');
4073
4074 IF v_spread_amounts(i).amount1 IS NOT NULL AND
4075 v_spread_amounts(i).amount1 <> 0 OR
4076 v_spread_amounts(i).amount2 IS NOT NULL AND
4077 v_spread_amounts(i).amount2 <> 0 OR
4078 v_spread_amounts(i).amount3 IS NOT NULL AND
4079 v_spread_amounts(i).amount3 <> 0 OR
4080 v_spread_amounts(i).amount4 IS NOT NULL AND
4081 v_spread_amounts(i).amount4 <> 0 THEN
4082
4083 -- Insert into PA_BUDGET_LINES,
4084
4085 insert_budget_line(
4086 v_resource_assignment_id,
4087 v_spread_amounts(i).start_date,
4088 v_spread_amounts(i).end_date,
4089 v_spread_amounts(i).period_name,
4090 v_txn_currency_code,
4091 resource_assignment_rec.TXN_CURRENCY_CODE_OVERRIDE,
4092 v_budget_line_id,
4093 p_budget_version_id,
4094 l_proj_curr_cd,
4095 l_projfunc_curr_cd,
4096 v_return_status,
4097 v_msg_count,
4098 v_msg_data);
4099
4100 IF v_return_status <>
4101 FND_API.G_RET_STS_SUCCESS THEN
4102 l_err_msg := v_msg_data;
4103 RAISE SPREAD_AMOUNTS_EXCEPTION;
4104
4105 END IF;
4106
4107 l_stage := 1004;
4108 -- print_msg(l_stage||' after insert budget line');
4109
4110 -- Insert into Rollup Temporary Table
4111
4112 insert_rollup_tmp(
4113 resource_assignment_rec,
4114 p_budget_version_id,
4115 v_spread_amounts(i).start_date,
4116 v_spread_amounts(i).end_date,
4117 v_spread_amounts(i).period_name,
4118 v_budget_line_id,
4119 v_spread_amounts(i).amount1,
4120 v_spread_amounts(i).amount2,
4121 v_spread_amounts(i).amount3,
4122 v_spread_amounts(i).amount4,
4123 v_return_status,
4124 v_msg_count,
4125 v_msg_data);
4126
4127 IF v_return_status <>
4128 FND_API.G_RET_STS_SUCCESS THEN
4129 l_err_msg := v_msg_data;
4130 RAISE SPREAD_AMOUNTS_EXCEPTION;
4131
4132 END IF;
4133
4134 l_stage := 1005;
4135 -- print_msg(l_stage||' after insert rollup tmp');
4136 END IF; -- all amounts 0 or null
4137
4138 ELSE -- found one budget line
4139
4140 l_stage := 1006;
4141 --print_msg(l_stage||' enter budget line id found');
4142 If l_txn_quantity_addl is NOT NULL Then
4143 If l_neg_qty_er_flag = 'Y' Then /* bug fix:5726773 */
4144 l_quantity := v_spread_amounts(i).amount1;
4145 Else
4146 l_quantity := nvl(l_quantity, 0) + v_spread_amounts(i).amount1;
4147 End If;
4148 Else
4149 l_quantity := nvl(l_quantity,0) ;
4150 End If;
4151
4152 If l_txn_raw_cost_addl is NOT NULL Then
4153 l_txn_raw_cost := nvl(l_txn_raw_cost, 0) + v_spread_amounts(i).amount2;
4154 Else
4155 l_txn_raw_cost := nvl(l_txn_raw_cost,0) ;
4156 End If;
4157
4158 If l_txn_burdened_cost_addl is NOT NULL Then
4159 l_txn_burdened_cost := nvl(l_txn_burdened_cost, 0)+ v_spread_amounts(i).amount3;
4160 Else
4161 l_txn_burdened_cost := nvl(l_txn_burdened_cost,0) ;
4162 End If;
4163
4164 If l_txn_revenue_addl is NOT NULL Then
4165 l_txn_revenue := nvl(l_txn_revenue, 0) + v_spread_amounts(i).amount4;
4166 Else
4167 l_txn_revenue := nvl(l_txn_revenue,0) ;
4168 End If;
4169
4170 insert_rollup_tmp_with_bl(
4171 resource_assignment_rec,
4172 p_budget_version_id,
4173 v_spread_amounts(i).start_date,
4174 v_spread_amounts(i).end_date,
4175 v_spread_amounts(i).period_name,
4176 v_budget_line_id,
4177 l_quantity,
4178 l_txn_raw_cost,
4179 l_txn_burdened_cost,
4180 l_txn_revenue,
4181 v_return_status,
4182 v_msg_count,
4183 v_msg_data);
4184
4185 IF v_return_status <>
4186 FND_API.G_RET_STS_SUCCESS THEN
4187
4188 l_err_msg := v_msg_data;
4189 RAISE SPREAD_AMOUNTS_EXCEPTION;
4190
4191 END IF;
4192 l_stage := 1007;
4193 -- print_msg(l_stage||' after update rollup tmp');
4194 -- END IF; -- all amounts 0
4195 END IF;
4196 END LOOP;
4197 l_stage := 1010;
4198 print_msg(l_stage||' after update db');
4199
4200
4201
4202 ELSE -- spread based on existing distribution
4203
4204 l_stage := 1020;
4205 print_msg(l_stage||' enter spread based on existing distribution');
4206
4207 v_total_quantity := 0;
4208 v_total_raw_cost := 0;
4209 v_total_burdened_cost := 0;
4210 v_total_revenue := 0;
4211 tmp_quantity := 0;
4212 tmp_txn_raw_cost :=0;
4213 tmp_txn_burdened_cost :=0;
4214 tmp_txn_revenue := 0;
4215
4216 FOR budget_line_rec IN cur_ExistBdgtLines( v_resource_assignment_id
4217 ,v_txn_currency_code
4218 ,l_line_start_date
4219 ,l_line_end_date ) LOOP
4220
4221 --print_msg('inside loop For each record in PA_BUDGET_LINES');
4222 -- get Budget Line ID and amounts
4223 -- update amounts based on existing distribution
4224
4225
4226 l_last_budget_line_id := budget_line_rec.budget_line_id;
4227
4228 print_msg('sumtxnqty['||l_sum_txn_quantity||'LnQty['||budget_line_rec.QUANTITY||']LnInit['||budget_line_rec.INIT_QUANTITY||']');
4229 IF l_sum_txn_quantity IS NOT NULL AND
4230 l_sum_txn_quantity <> 0 THEN
4231
4232 /* bug fix:5726773 */
4233 If raRec.neg_Qty_Change_flag = 'Y' Then
4234 tmp_quantity := nvl(budget_line_rec.QUANTITY,0);
4235 Else
4236
4237 IF l_txn_quantity_addl is NOT NULL Then
4238 tmp_quantity :=
4239 (nvl(l_txn_quantity_addl,0) * ((nvl(budget_line_rec.QUANTITY,0)- nvl(budget_line_rec.INIT_QUANTITY,0)) /
4240 l_sum_txn_quantity));
4241 tmp_quantity := Round_Qty_Amts(G_rate_based_flag,'Y',G_curr_code,tmp_quantity);
4242 Else
4243 tmp_quantity := 0;
4244 End If;
4245 tmp_quantity := tmp_quantity + nvl(budget_line_rec.QUANTITY,0);
4246 tmp_quantity := Round_Qty_Amts(G_rate_based_flag,'Y',G_curr_code,tmp_quantity);
4247 --print_msg(' tmp_quantity => '||to_char(tmp_quantity));
4248 --print_msg(' v_total_quantity => '||to_char(v_total_quantity));
4249 End If;
4250 END IF;
4251
4252 IF l_sum_txn_raw_cost IS NOT NULL AND
4253 l_sum_txn_raw_cost <> 0 THEN
4254
4255 If l_txn_raw_cost_addl is NOT NULL Then
4256 tmp_txn_raw_cost :=
4257 (nvl(l_txn_raw_cost_addl,0) * nvl(budget_line_rec.TXN_RAW_COST,0) / nvl(l_sum_txn_raw_cost,0));
4258 tmp_txn_raw_cost := Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,tmp_txn_raw_cost);
4259 Else
4260 tmp_txn_raw_cost := 0;
4261 End If;
4262 tmp_txn_raw_cost := tmp_txn_raw_cost + nvl(budget_line_rec.TXN_RAW_COST,0);
4263 tmp_txn_raw_cost := Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,tmp_txn_raw_cost);
4264
4265 END IF;
4266
4267 IF l_sum_txn_burdened_cost IS NOT NULL AND
4268 l_sum_txn_burdened_cost <> 0 THEN
4269
4270 If l_txn_burdened_cost_addl is NOT NULL Then
4271 tmp_txn_burdened_cost := (nvl(l_txn_burdened_cost_addl,0) *
4272 (nvl(budget_line_rec.TXN_BURDENED_COST,0) / nvl(l_sum_txn_burdened_cost,0)));
4273 tmp_txn_burdened_cost := Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,tmp_txn_burdened_cost);
4274 Else
4275 tmp_txn_burdened_cost := 0;
4276 End if;
4277 tmp_txn_burdened_cost := tmp_txn_burdened_cost + nvl(budget_line_rec.TXN_BURDENED_COST,0);
4278 tmp_txn_burdened_cost := Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,tmp_txn_burdened_cost);
4279 END IF;
4280
4281 IF l_sum_txn_revenue IS NOT NULL AND
4282 l_sum_txn_revenue <> 0 THEN
4283
4284 If l_txn_revenue_addl is NOT NULL Then
4285 tmp_txn_revenue := (nvl(l_txn_revenue_addl,0) *
4286 (nvl(budget_line_rec.TXN_REVENUE,0) / nvl(l_sum_txn_revenue,0)));
4287 tmp_txn_revenue := Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,tmp_txn_revenue);
4288 Else
4289 tmp_txn_revenue := 0;
4290 End if;
4291 tmp_txn_revenue := tmp_txn_revenue + nvl(budget_line_rec.TXN_REVENUE,0);
4292 tmp_txn_revenue := Round_Qty_Amts(G_rate_based_flag,'N',G_curr_code,tmp_txn_revenue);
4293 END IF;
4294
4295 /*
4296 print_msg(' Before insert into insert_rollup_tmp_with_bl');
4297 print_msg(' tmp_quantity => '||to_char(tmp_quantity)||']tmp_txn_raw_cost['||tmp_txn_raw_cost||']');
4298 print_msg('tmp_txn_burdened_cost => '||tmp_txn_burdened_cost||']tmp_txn_revenue['||tmp_txn_revenue||']');
4299 */
4300 -- Insert into rollup tmp table
4301 insert_rollup_tmp_with_bl(
4302 resource_assignment_rec,
4303 p_budget_version_id,
4304 budget_line_rec.start_date,
4305 budget_line_rec.end_date,
4306 budget_line_rec.period_name,
4307 budget_line_rec.budget_line_id,
4308 tmp_quantity,
4309 tmp_txn_raw_cost,
4310 tmp_txn_burdened_cost,
4311 tmp_txn_revenue,
4312 v_return_status,
4313 v_msg_count,
4314 v_msg_data);
4315
4316 IF v_return_status <> 'S' Then
4317 l_err_msg := v_msg_data;
4318 RAISE SPREAD_AMOUNTS_EXCEPTION;
4319 END IF;
4320
4321 END LOOP; -- existing amounts, for each budget line
4322 print_msg('End of Existing line distribution Loop');
4323
4324 If l_last_budget_line_id is NOT NULL Then --{
4325 print_msg(' Adding last budget line id to plsql tab of PA_FP_ROLLUP_TMP with l_last_budget_line_id');
4326 /* getting the totals from budgetline to add final difference */
4327 g_edist_blId(NVL(g_edist_blId.LAST,0)+1) := l_last_budget_line_id;
4328 g_edist_RaId(NVL(g_edist_RaId.LAST,0)+1) := v_resource_assignment_id;
4329 g_edist_Curcode(NVL(g_edist_Curcode.LAST,0)+1) := v_txn_currency_code;
4330 g_edist_Curcode_ovr(NVL(g_edist_Curcode_ovr.LAST,0)+1) := resource_assignment_rec.txn_currency_code_override;
4331 g_edist_sdate(NVL(g_edist_sdate.LAST,0)+1) := l_line_start_date;
4332 g_edist_edate(NVL(g_edist_edate.LAST,0)+1) := l_line_end_date;
4333 g_edist_etc_sdate(NVL(g_edist_etc_sdate.LAST,0)+1) := l_g_start_date;
4334 g_edist_source_context(NVL(g_edist_source_context.LAST,0)+1) := resource_assignment_rec.source_context;
4335 g_edist_line_start_date(NVL(g_edist_line_start_date.LAST,0)+1) := resource_assignment_rec.LINE_START_DATE;
4336 g_edist_txn_quantity_addl(NVL(g_edist_txn_quantity_addl.LAST,0)+1) := resource_assignment_rec.TXN_PLAN_QUANTITY_ADDL;
4337 g_edist_txn_plan_quantity(NVL(g_edist_txn_plan_quantity.LAST,0)+1) := resource_assignment_rec.TXN_PLAN_QUANTITY;
4338 End If; --}
4339
4340 END IF; -- call spread()?
4341 ELSE -- fixed date
4342
4343 l_stage := 1040;
4344 print_msg(l_stage||' enter fixed date');
4345
4346
4347 -- when SP_FIXED_DATE is NULL,
4348 -- take LINE_START_DATE or PLAN_START_DATE
4349 -- also when SP_FIX_DATE is not NULL
4350 -- and not within LINE START/END
4351 -- or PLAN START/END DATE, report error.
4352
4353 IF resource_assignment_rec.SP_FIXED_DATE IS NOT NULL THEN
4354
4355 l_fixed_date := resource_assignment_rec.SP_FIXED_DATE;
4356 IF NOT l_fixed_date BETWEEN l_line_start_date AND l_line_end_date THEN
4357 l_err_msg := 'PA_FP_FIXED_DATE_NOT_MATCH';
4358 RAISE SPREAD_AMOUNTS_EXCEPTION;
4359 END IF;
4360 ELSE
4361 l_fixed_date := l_line_start_date;
4362 END IF;
4363 l_stage := 1041;
4364 print_msg(l_stage||' l_fixed_date '||l_fixed_date);
4365
4366 -- find fixed date period in budget lines
4367 l_fixed_date_period_count := 0;
4368 FOR budget_line_rec IN cur_spFixDateBdgtLines LOOP
4369
4370 IF l_fixed_date BETWEEN budget_line_rec.START_DATE AND budget_line_rec.END_DATE THEN
4371
4372 l_stage := 1042;
4373 print_msg(l_stage||' enter found fixed date period');
4374
4375 l_fixed_date_period_count := l_fixed_date_period_count + 1;
4376
4377 -- Insert Rollup Temporary Table
4378 IF l_g_start_date IS NOT NULL AND
4379 (budget_line_rec.init_quantity IS NOT NULL OR
4380 budget_line_rec.txn_init_raw_cost IS NOT NULL OR
4381 budget_line_rec.txn_init_burdened_cost IS NOT NULL OR
4382 budget_line_rec.txn_init_revenue IS NOT NULL) THEN
4383
4384 l_stage := 1043;
4385 print_msg(l_stage||' enter found fixed date period with init');
4386
4387 If l_txn_quantity_addl is NOT NULL Then
4388 l_sp_fixed_qty := (nvl(budget_line_rec.quantity,0) + nvl(l_txn_quantity_addl,0));
4389 Else
4390 l_sp_fixed_qty := nvl(budget_line_rec.quantity,0);
4391 End If;
4392 If l_txn_raw_cost_addl is NOT NULL Then
4393 l_sp_fixed_cost := (nvl(budget_line_rec.txn_raw_cost,0) + nvl(l_txn_raw_cost_addl,0));
4394 Else
4395 l_sp_fixed_cost := nvl(budget_line_rec.txn_raw_cost,0);
4396 End If;
4397 If l_txn_burdened_cost_addl is NOT NULL Then
4398 l_sp_fixed_burden := (nvl(budget_line_rec.txn_burdened_cost,0) + nvl(l_txn_burdened_cost_addl,0));
4399 Else
4400 l_sp_fixed_burden := nvl(budget_line_rec.txn_burdened_cost,0);
4401 End If;
4402 If l_txn_revenue_addl is NOT NULL Then
4403 l_sp_fixed_revenue := (nvl(budget_line_rec.txn_revenue,0) + nvl(l_txn_revenue_addl,0));
4404 Else
4405 l_sp_fixed_revenue := nvl(budget_line_rec.txn_revenue,0);
4406 End If;
4407
4408 insert_rollup_tmp_with_bl(
4409 resource_assignment_rec,
4410 p_budget_version_id,
4411 budget_line_rec.start_date,
4412 budget_line_rec.end_date,
4413 budget_line_rec.period_name,
4414 budget_line_rec.budget_line_id,
4415 l_sp_fixed_qty,
4416 l_sp_fixed_cost,
4417 l_sp_fixed_burden,
4418 l_sp_fixed_revenue,
4419 v_return_status,
4420 v_msg_count,
4421 v_msg_data);
4422
4423 IF v_return_status <>
4424 FND_API.G_RET_STS_SUCCESS THEN
4425 l_err_msg := v_msg_data;
4426 RAISE SPREAD_AMOUNTS_EXCEPTION;
4427
4428 END IF;
4429
4430 ELSE
4431 l_stage := 1044;
4432 print_msg(l_stage||' enter found fixed date period without init');
4433
4434
4435 insert_rollup_tmp_with_bl(
4436 resource_assignment_rec,
4437 p_budget_version_id,
4438 budget_line_rec.start_date,
4439 budget_line_rec.end_date,
4440 budget_line_rec.period_name,
4441 budget_line_rec.budget_line_id,
4442 (nvl(budget_line_rec.quantity,0) + nvl(l_txn_quantity_addl,0)),
4443 (nvl(budget_line_rec.txn_raw_cost,0) + nvl(l_txn_raw_cost_addl,0)),
4444 (nvl(budget_line_rec.txn_burdened_cost,0) + nvl(l_txn_burdened_cost_addl,0)),
4445 (nvl(budget_line_rec.txn_revenue,0) + nvl(l_txn_revenue_addl,0)),
4446 v_return_status,
4447 v_msg_count,
4448 v_msg_data);
4449
4450 IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4451 l_err_msg := v_msg_data;
4452 RAISE SPREAD_AMOUNTS_EXCEPTION;
4453
4454 END IF;
4455
4456 END IF;
4457 ELSE -- fixed date not in period
4458 l_stage := 1045;
4459 print_msg(l_stage||' enter found non fixed date period');
4460 IF l_g_start_date IS NOT NULL AND
4461 (budget_line_rec.init_quantity IS NOT NULL OR
4462 budget_line_rec.txn_init_raw_cost IS NOT NULL OR
4463 budget_line_rec.txn_init_burdened_cost IS NOT NULL OR
4464 budget_line_rec.txn_init_revenue IS NOT NULL) THEN
4465
4466 l_stage := 1046;
4467 print_msg(l_stage||' enter found non fixed date period with init');
4468 insert_rollup_tmp_with_bl(
4469 resource_assignment_rec,
4470 p_budget_version_id,
4471 budget_line_rec.start_date,
4472 budget_line_rec.end_date,
4473 budget_line_rec.period_name,
4474 budget_line_rec.budget_line_id,
4475 budget_line_rec.init_quantity,
4476 budget_line_rec.txn_init_raw_cost,
4477 budget_line_rec.txn_init_burdened_cost,
4478 budget_line_rec.txn_init_revenue,
4479 v_return_status,
4480 v_msg_count,
4481 v_msg_data);
4482
4483 IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4484 l_err_msg := v_msg_data;
4485 RAISE SPREAD_AMOUNTS_EXCEPTION;
4486
4487 END IF;
4488 ELSE
4489 l_stage := 1047;
4490 print_msg(l_stage||' enter found non fixed date period when g_start_date is null');
4491 print_msg(' or g_start_date is not null but no init, and return err');
4492 l_err_msg := 'PA_FP_FIXED_DATE_NOT_CLEAR';
4493 RAISE SPREAD_AMOUNTS_EXCEPTION;
4494 END IF;
4495 END IF;
4496
4497 END LOOP;
4498
4499 IF l_fixed_date_period_count = 0 THEN
4500 l_stage := 1048;
4501 print_msg(l_stage||' enter no fixed date period found');
4502 i := 1;
4503 LOOP
4504 EXIT WHEN i >
4505 v_spread_amounts.COUNT OR
4506 l_fixed_date BETWEEN
4507 v_spread_amounts(i).start_date
4508 AND
4509 v_spread_amounts(i).end_date;
4510 i := i + 1;
4511 END LOOP;
4512
4513 IF i > v_spread_amounts.COUNT() THEN
4514
4515 l_err_msg := 'PA_FP_FIXED_DATE_NOT_MATCH';
4516 RAISE SPREAD_AMOUNTS_EXCEPTION;
4517
4518 END IF;
4519 l_stage := 1049;
4520 print_msg(l_stage||' after get fixed date period ');
4521 print_msg(' '||v_spread_amounts(i).start_date||'/'||v_spread_amounts(i).end_date);
4522
4523 -- Insert into PA_BUDGET_LINES,
4524
4525 insert_budget_line(
4526 v_resource_assignment_id,
4527 v_spread_amounts(i).start_date,
4528 v_spread_amounts(i).end_date,
4529 v_spread_amounts(i).period_name,
4530 resource_assignment_rec.TXN_CURRENCY_CODE,
4531 resource_assignment_rec.TXN_CURRENCY_CODE_OVERRIDE,
4532 v_budget_line_id,
4533 p_budget_version_id,
4534 l_proj_curr_cd,
4535 l_projfunc_curr_cd,
4536 v_return_status,
4537 v_msg_count,
4538 v_msg_data);
4539
4540 IF v_return_status <>
4541 FND_API.G_RET_STS_SUCCESS THEN
4542 l_err_msg := v_msg_data;
4543 RAISE SPREAD_AMOUNTS_EXCEPTION;
4544
4545 END IF;
4546
4547
4548 -- Insert into Rollup Temporary Table
4549 -- INSERT INTO PA_FP_ROLLUP_TMP
4550 insert_rollup_tmp(
4551 resource_assignment_rec,
4552 p_budget_version_id,
4553 v_spread_amounts(i).start_date,
4554 v_spread_amounts(i).end_date,
4555 v_spread_amounts(i).period_name,
4556 v_budget_line_id,
4557 l_txn_quantity_addl,
4558 l_txn_raw_cost_addl,
4559 l_txn_burdened_cost_addl,
4560 l_txn_revenue_addl,
4561 v_return_status,
4562 v_msg_count,
4563 v_msg_data);
4564
4565 IF v_return_status <>
4566 FND_API.G_RET_STS_SUCCESS THEN
4567 l_err_msg := v_msg_data;
4568 RAISE SPREAD_AMOUNTS_EXCEPTION;
4569
4570 END IF;
4571
4572 END IF;
4573 l_stage := 1050;
4574 print_msg(l_stage||' after update db ');
4575
4576 END IF; -- fixed date or not
4577
4578 END IF; -- whether time phase code is R or N
4579
4580 EXCEPTION
4581 WHEN SKIP_EXCEPTION THEN
4582 NULL;
4583
4584 WHEN SPREAD_AMOUNTS_EXCEPTION THEN
4585 /* bug fix:4194475 open the cursor only when error msg needs to populated */
4586 print_msg(l_stage||' get project_name, task_name and resource_name');
4587 OPEN get_line_info(v_resource_assignment_id);
4588 FETCH get_line_info
4589 INTO l_project_name
4590 , l_task_name
4591 , l_resource_name;
4592 CLOSE get_line_info;
4593
4594 l_sprd_exception_count := l_sprd_exception_count + 1;
4595 L_FINAL_RETURN_STATUS := 'E';
4596 IF l_err_msg = 'PA_FP_BUDGET_RES_CURRENCY_NULL' THEN
4597 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4598 p_msg_name => l_err_msg
4599 );
4600 ELSE
4601 /* bug fix: 3762278 passing the incorrect msg tokens */
4602 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
4603 ,p_msg_name => l_err_msg
4604 ,p_token1 => 'L_PROJECT_NAME'
4605 ,p_value1 => l_project_name
4606 ,p_token2 => 'L_TASK_NAME'
4607 ,p_value2 => l_task_name
4608 ,p_token3 => 'L_RESOURCE_NAME'
4609 ,p_value3 => l_resource_name
4610 ,p_token4 => 'L_LINE_START_DATE'
4611 ,p_value4 => l_line_start_date
4612 ,p_token5 => 'L_LINE_END_DATE'
4613 ,p_value5 => l_line_end_date);
4614 END IF;
4615 END;
4616
4617
4618 END LOOP; --} for each resource assignment
4619
4620 /* Now Bulk insert all the budget lines */
4621 print_msg('Bulk update/Insert of budget and rollup tmp lines');
4622 blkInsertBudgetLines(x_return_status => L_FINAL_RETURN_STATUS);
4623 blkInsertFpLines(x_return_status => L_FINAL_RETURN_STATUS);
4624 blkInsertBlFpLines(x_return_status => L_FINAL_RETURN_STATUS);
4625
4626 IF NVL(L_FINAL_RETURN_STATUS,'S') = 'S' Then
4627 /* update the last rollup tmp line with rounding difference amount */
4628 IF g_edist_blId.COUNT > 0 THEN
4629 print_msg('Calling Process_Rounding_Diff API');
4630 Process_Rounding_Diff(p_budget_version_id => p_budget_version_id
4631 ,x_return_status => L_FINAL_RETURN_STATUS
4632 );
4633 print_msg('RetSts of Process_Rounding_Diff API['||L_FINAL_RETURN_STATUS||']');
4634 IF NVL(L_FINAL_RETURN_STATUS,'S') <> 'S' Then
4635 x_return_status := L_FINAL_RETURN_STATUS;
4636 End IF;
4637 END IF;
4638 ELSIF NVL(L_FINAL_RETURN_STATUS,'S') <> 'S' Then
4639 print_msg('Error occured during the spread, set the return status to E');
4640 x_return_status := L_FINAL_RETURN_STATUS;
4641 END IF;
4642
4643 If p_pa_debug_mode = 'Y' Then
4644 FOR i IN ( SELECT tmp.resource_assignment_id resAgnId
4645 ,tmp.txn_currency_code Currency
4646 ,sum(tmp.quantity) tmpqty
4647 ,sum(tmp.txn_raw_cost) tmprawcost
4648 ,sum(tmp.txn_burdened_cost) tmpburdencost
4649 ,sum(tmp.txn_revenue) tmprevenue
4650 ,sum(tmp.init_quantity) initQty
4651 ,sum(tmp.txn_init_raw_cost) initraw
4652 ,sum(tmp.txn_init_burdened_cost) initbud
4653 ,sum(tmp.txn_init_revenue) initrev
4654 ,count(*) numrows
4655 FROM pa_fp_rollup_tmp tmp
4656 WHERE tmp.budget_version_id = p_budget_version_id
4657 AND NVL(tmp.system_reference5,'N') = 'N'
4658 GROUP BY tmp.resource_assignment_id,tmp.txn_currency_code ) LOOP
4659 print_msg('Number of Records in rolluptmp after Spread['||i.numrows||']');
4660 print_msg('tmpRes['||i.resAgnId||']Cur['||i.Currency||']tmpqty['||i.tmpqty||']tmpRaw['||i.tmprawcost||']tmpbd['||i.tmpburdencost||']');
4661 print_msg('tmprev['||i.tmprevenue||']initQty['||i.initQty||']initRaw['||i.initraw||']initbud['||i.initbud||']initrev['||i.initrev||']');
4662 END LOOP;
4663 End If;
4664 l_stage := 1060;
4665 print_msg(l_stage||' **********leave spread_amounts() with retSts['||x_return_status||']');
4666
4667 /* Reset the error stack */
4668 If p_pa_debug_mode = 'Y' Then
4669 pa_debug.reset_err_stack;
4670 End If;
4671
4672 EXCEPTION
4673
4674 WHEN SPREAD_AMOUNTS_EXCEPTION THEN
4675
4676 x_return_status := FND_API.G_RET_STS_ERROR;
4677 L_FINAL_RETURN_STATUS := 'E';
4678 /* bug fix:4194475 open the cursor only when error msg needs to populated */
4679 print_msg(l_stage||' get project_name, task_name and resource_name');
4680 OPEN get_line_info(v_resource_assignment_id);
4681 FETCH get_line_info
4682 INTO l_project_name
4683 , l_task_name
4684 , l_resource_name;
4685 CLOSE get_line_info;
4686 IF l_err_msg = 'PA_FP_BUDGET_RES_CURRENCY_NULL' THEN
4687 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4688 p_msg_name => l_err_msg
4689 );
4690 ELSE
4691 /* bug fix: 3762278 passing the incorrect msg tokens */
4692 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
4693 ,p_msg_name => l_err_msg
4694 ,p_token1 => 'L_PROJECT_NAME'
4695 ,p_value1 => l_project_name
4696 ,p_token2 => 'L_TASK_NAME'
4697 ,p_value2 => l_task_name
4698 ,p_token3 => 'L_RESOURCE_NAME'
4699 ,p_value3 => l_resource_name
4700 ,p_token4 => 'L_LINE_START_DATE'
4701 ,p_value4 => l_line_start_date
4702 ,p_token5 => 'L_LINE_END_DATE'
4703 ,p_value5 => l_line_end_date);
4704 END IF;
4705
4706 x_msg_count := fnd_msg_pub.count_msg;
4707 /* BUG FIX 3632873 Retrive the msg from stack */
4708 print_msg('Retrive the msg from stack MsgCt['||x_msg_count||']');
4709 If x_msg_count = 1 then
4710 pa_interface_utils_pub.get_messages
4711 ( p_encoded => FND_API.G_TRUE
4712 ,p_msg_index => 1
4713 ,p_data => x_msg_data
4714 ,p_msg_index_out => l_msg_index_out
4715 );
4716 x_return_status := 'E';
4717 Elsif x_msg_count > 1 then
4718 x_return_status := 'E';
4719 x_msg_count := x_msg_count;
4720 x_msg_data := null;
4721 End if;
4722 print_msg('Final ReturnSts['||x_return_status||
4723 ']msgCt['||x_msg_count||']msgData['||x_msg_data||']');
4724 If p_pa_debug_mode = 'Y' Then
4725 pa_debug.reset_err_stack;
4726 End If;
4727 RETURN;
4728
4729
4730 WHEN OTHERS THEN
4731
4732 print_msg('Encountered Unexpected error from Spread API['||SQLCODE||SQLERRM);
4733 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4734 L_FINAL_RETURN_STATUS := 'U';
4735 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_FP_SPREAD_AMTS_PKG',
4736 p_procedure_name => 'spread_amounts');
4737 If p_pa_debug_mode = 'Y' Then
4738 pa_debug.reset_err_stack;
4739 End If;
4740 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4741
4742 END spread_amounts;
4743
4744
4745 END PA_FP_SPREAD_AMTS_PKG;