[Home] [Help]
PACKAGE BODY: APPS.PA_FP_GEN_FCST_AMT_PUB
Source
1 PACKAGE body PA_FP_GEN_FCST_AMT_PUB as
2 /* $Header: PAFPFGPB.pls 120.16 2007/11/28 14:05:05 vgovvala ship $ */
3
4
5 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
6
7 -----------------------------------------------------------------------
8 --------- Forward declarations for local/private procedures -----------
9 -----------------------------------------------------------------------
10
11 PROCEDURE UPD_REV_CALCULATION_ERR
12 (P_PROJECT_ID IN PA_PROJECTS_ALL.PROJECT_ID%TYPE,
13 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
14 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
15 P_ETC_START_DATE IN DATE,
16 P_CALLED_MODE IN VARCHAR2 DEFAULT 'SELF_SERVICE',
17 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
18 X_MSG_COUNT OUT NOCOPY NUMBER,
19 X_MSG_DATA OUT NOCOPY VARCHAR2 );
20
21 ------------------------------------------------------------------------------
22 --------- END OF Forward declarations for local/private procedures -----------
23 ------------------------------------------------------------------------------
24
25 /**GENERATE_FCST_AMT_WRP is called from PA_FP_GEN_FCST_PG_PKG.UPD_VER_DTLS_AND_GEN_AMT,
26 *which was called directly from forecast generation page.
27 **/
28 PROCEDURE GENERATE_FCST_AMT_WRP
29 ( P_PROJECT_ID IN PA_PROJECTS_ALL.PROJECT_ID%TYPE,
30 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
31 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
32 P_CALLED_MODE IN VARCHAR2,
33 P_COMMIT_FLAG IN VARCHAR2,
34 P_INIT_MSG_FLAG IN VARCHAR2,
35 P_VERSION_TYPE IN PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE,
36 P_UNSPENT_AMT_FLAG IN PA_PROJ_FP_OPTIONS.GEN_COST_INCL_UNSPENT_AMT_FLAG%TYPE,
37 P_UNSPENT_AMT_PERIOD IN VARCHAR2,
38 P_INCL_CHG_DOC_FLAG IN PA_PROJ_FP_OPTIONS.GEN_COST_INCL_CHANGE_DOC_FLAG%TYPE,
39 P_INCL_OPEN_CMT_FLAG IN PA_PROJ_FP_OPTIONS.GEN_COST_INCL_OPEN_COMM_FLAG%TYPE,
40 P_INCL_BILL_EVT_FLAG IN PA_PROJ_FP_OPTIONS.GEN_REV_INCL_BILL_EVENT_FLAG%TYPE,
41 P_RET_MANUAL_LNS_FLAG IN PA_PROJ_FP_OPTIONS.GEN_COST_RET_MANUAL_LINE_FLAG%TYPE,
42 P_PLAN_TYPE_ID IN PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
43 P_PLAN_VERSION_ID IN PA_PROJ_FP_OPTIONS.FIN_PLAN_VERSION_ID%TYPE,
44 P_PLAN_VERSION_NAME IN PA_BUDGET_VERSIONS.VERSION_NAME%TYPE,
45 P_ETC_PLAN_TYPE_ID IN PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
46 P_ETC_PLAN_VERSION_ID IN PA_PROJ_FP_OPTIONS.FIN_PLAN_VERSION_ID%TYPE,
47 P_ETC_PLAN_VERSION_NAME IN PA_BUDGET_VERSIONS.VERSION_NAME%TYPE,
48 P_ACTUALS_FROM_PERIOD IN VARCHAR2,
49 P_ACTUALS_TO_PERIOD IN VARCHAR2,
50 P_ETC_FROM_PERIOD IN VARCHAR2,
51 P_ETC_TO_PERIOD IN VARCHAR2,
52 P_ACTUALS_THRU_PERIOD IN PA_BUDGET_VERSIONS.ACTUAL_AMTS_THRU_PERIOD%TYPE,
53 P_ACTUALS_THRU_DATE IN PA_PERIODS_ALL.END_DATE%TYPE,
54 P_WP_STRUCTURE_VERSION_ID IN PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE,
55 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
56 X_MSG_COUNT OUT NOCOPY NUMBER,
57 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
58
59 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_fcst_amt_pub.generate_fcst_amt_wrp';
60 l_cost_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
61 l_ci_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
62 l_gen_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
63 l_deleted_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
64
65 l_rev_gen_method VARCHAR2(3);
66 l_error_msg VARCHAR2(30);
67 l_msg_count NUMBER;
68 l_msg_data VARCHAR2(2000);
69 l_data VARCHAR2(2000);
70 l_msg_index_out NUMBER:=0;
71
72 l_count NUMBER;
73 l_dummy NUMBER;
74 l_task_count NUMBER;
75
76 l_src_plan_class_code pa_fin_plan_types_b.plan_class_code%type;
77 l_src_version_type pa_budget_versions.version_type%type;
78 lx_deleted_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
79 lx_gen_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
80
81 -- Bug 4592564: Removed variables for Unspent Amounts option.
82
83 -- Added as part of fix for Bug 4232094
84 l_budget_generation_flow_flag VARCHAR2(1) := 'N';
85
86 l_call_maintain_data_api VARCHAR2(1);
87 BEGIN
88 /* hr_utility.trace_on(null,'mftest');
89 hr_utility.trace('---BEGIN---'); */
90 IF P_INIT_MSG_FLAG = 'Y' THEN
91 FND_MSG_PUB.initialize;
92 END IF;
93 X_MSG_COUNT := 0;
94 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
95
96 IF p_pa_debug_mode = 'Y' THEN
97 pa_debug.set_curr_function( p_function => 'GENERATE_FCST_AMT_WRP',
98 p_debug_mode => p_pa_debug_mode);
99 END IF;
100
101 IF P_PA_DEBUG_MODE = 'Y' THEN
102 pa_fp_gen_amount_utils.fp_debug
103 (p_called_mode => P_CALLED_MODE,
104 p_msg =>
105 'Before calling PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_BV_FOR_GEN_DATE',
106 p_module_name => l_module_name);
107 END IF;
108 PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_BV_FOR_GEN_DATE
109 (P_PROJECT_ID => P_PROJECT_ID,
110 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
111 P_ETC_START_DATE => P_ACTUALS_THRU_DATE + 1,
112 X_RETURN_STATUS => X_RETURN_STATUS,
113 X_MSG_COUNT => X_MSG_COUNT,
114 X_MSG_DATA => X_MSG_DATA);
115 IF P_PA_DEBUG_MODE = 'Y' THEN
116 pa_fp_gen_amount_utils.fp_debug
117 (p_called_mode => P_CALLED_MODE,
118 p_msg => 'After calling PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_BV_FOR_GEN_DATE,
119 ret status: '||x_return_status,
120 p_module_name => l_module_name);
121 END IF;
122
123 DELETE FROM PA_FP_CALC_AMT_TMP1;
124 DELETE FROM PA_FP_CALC_AMT_TMP2;
125 IF p_fp_cols_rec.x_gen_incl_open_comm_flag = 'Y' THEN
126 IF p_pa_debug_mode = 'Y' THEN
127 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
128 P_CALLED_MODE => P_CALLED_MODE,
129 P_MSG => 'Before calling pa_fp_commitment_amounts.'||
130 'get_commitment_amts',
131 P_MODULE_NAME => l_module_name);
132 END IF;
133 PA_FP_COMMITMENT_AMOUNTS.GET_COMMITMENT_AMTS
134 (P_PROJECT_ID => P_PROJECT_ID,
135 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
136 P_FP_COLS_REC => p_fp_cols_rec,
137 PX_GEN_RES_ASG_ID_TAB => l_gen_res_asg_id_tab,
138 PX_DELETED_RES_ASG_ID_TAB => l_deleted_res_asg_id_tab,
139 X_RETURN_STATUS => X_RETURN_STATUS,
140 X_MSG_COUNT => X_MSG_COUNT,
141 X_MSG_DATA => X_MSG_DATA);
142 --dbms_output.put_line('??x_msg_count:'||x_msg_count);
143 x_msg_count := 0;
144 IF p_pa_debug_mode = 'Y' THEN
145 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
146 P_CALLED_MODE => P_CALLED_MODE,
147 P_MSG => 'After calling pa_fp_commitment_amounts.'||
148 'get_commitment_amts: '||x_return_status,
149 P_MODULE_NAME => l_module_name);
150 END IF;
151 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
152 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
153 END IF;
154 END IF;
155
156 IF p_etc_plan_type_id IS NOT NULL AND
157 p_etc_plan_version_id IS NOT NULL THEN
158
159 SELECT plan_class_code
160 INTO l_src_plan_class_code
161 FROM pa_fin_plan_types_b
162 WHERE fin_plan_type_id = P_ETC_PLAN_TYPE_ID;
163
164 SELECT version_type
165 INTO l_src_version_type
166 FROM pa_budget_versions
167 WHERE budget_version_id = P_ETC_PLAN_VERSION_ID;
168
169 END IF;
170
171 -- Bug 4130319: Moved initialization of l_rev_gen_method from below.
172 -- Before, it was being set after the call to COPY_ACTUALS for the
173 -- Event-based Revenue and Resource Schedule cases.
174 l_rev_gen_method := nvl(P_FP_COLS_REC.X_REVENUE_DERIVATION_METHOD,PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(p_project_id)); --Bug 5462471
175 --l_rev_gen_method := PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(p_project_id);
176
177 -- Bug 4114589: We call the Copy Actuals API for these two cases here,
178 -- and postpone calling it until after CREATE_RES_ASG and UPDATE_RES_ASG
179 -- have been called in the remaining two cases (see conditional logic below).
180 IF (P_FP_COLS_REC.X_VERSION_TYPE = 'REVENUE' AND l_rev_gen_method = 'E') OR
181 (P_FP_COLS_REC.X_GEN_ETC_SRC_CODE = 'RESOURCE_SCHEDULE') THEN
182 IF p_pa_debug_mode = 'Y' THEN
183 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
184 P_CALLED_MODE => P_CALLED_MODE,
185 P_MSG => 'Before calling pa_fp_copy_actuals_pub.copy_actuals',
186 P_MODULE_NAME => l_module_name);
187 END IF;
188 PA_FP_COPY_ACTUALS_PUB.COPY_ACTUALS
189 (P_PROJECT_ID => P_PROJECT_ID,
190 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
191 P_FP_COLS_REC => P_fp_cols_rec,
192 P_END_DATE => P_ACTUALS_THRU_DATE,
193 P_INIT_MSG_FLAG => 'N',
194 P_COMMIT_FLAG => 'N',
195 X_RETURN_STATUS => X_RETURN_STATUS,
196 X_MSG_COUNT => X_MSG_COUNT,
197 X_MSG_DATA => X_MSG_DATA);
198 IF p_pa_debug_mode = 'Y' THEN
199 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
200 P_CALLED_MODE => P_CALLED_MODE,
201 P_MSG => 'After calling pa_fp_copy_actuals_pub.copy_actuals:'
202 ||x_return_status,
203 P_MODULE_NAME => l_module_name);
204 END IF;
205 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
206 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
207 END IF;
208 END IF;
209
210 --dbms_output.put_line('++P_FP_COLS_REC.X_GEN_ETC_SRC_CODE is :' ||P_FP_COLS_REC.X_GEN_ETC_SRC_CODE);
211 IF P_FP_COLS_REC.X_VERSION_TYPE = 'REVENUE' AND l_rev_gen_method = 'E' THEN
212 /*Skip both resource_schedule and task_level_sel*/
213 l_dummy := 1;
214 ELSIF P_FP_COLS_REC.X_GEN_ETC_SRC_CODE = 'RESOURCE_SCHEDULE' THEN
215 -- Bug 4222555: When the Target is a Revenue version and the accrual
216 -- method is Cost, we need to call the GEN_REV_BDGT_AMT_RES_SCH_WRP API.
217 IF p_fp_cols_rec.x_version_type = 'REVENUE' AND l_rev_gen_method = 'C' THEN
218 IF p_pa_debug_mode = 'Y' THEN
219 pa_fp_gen_amount_utils.fp_debug
220 ( p_called_mode => p_called_mode,
221 p_msg => 'Before calling
222 pa_fp_gen_budget_amt_pub.gen_rev_bdgt_amt_res_sch_wrp',
223 p_module_name => l_module_name,
224 p_log_level => 5 );
225 END IF;
226 --hr_utility.trace('before PA_FP_GEN_BUDGET_AMT_PUB.GEN_REV_BDGT_AMT_RES_SCH_WRP');
227 PA_FP_GEN_BUDGET_AMT_PUB.GEN_REV_BDGT_AMT_RES_SCH_WRP
228 ( P_PROJECT_ID => P_PROJECT_ID,
229 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
230 P_FP_COLS_REC => P_fp_cols_rec,
231 P_PLAN_CLASS_CODE => P_fp_cols_rec.x_plan_class_code,
232 P_GEN_SRC_CODE => P_fp_cols_rec.x_gen_etc_src_code,
233 P_COST_PLAN_TYPE_ID => P_fp_cols_rec.x_gen_src_plan_type_id,
234 p_COST_VERSION_ID => l_cost_version_id,
235 P_RETAIN_MANUAL_FLAG => P_fp_cols_rec.x_gen_ret_manual_line_flag,
236 P_CALLED_MODE => P_CALLED_MODE,
237 P_INC_CHG_DOC_FLAG => P_fp_cols_rec.x_gen_incl_change_doc_flag,
238 P_INC_BILL_EVENT_FLAG => P_fp_cols_rec.x_gen_incl_bill_event_flag,
239 P_INC_OPEN_COMMIT_FLAG => P_fp_cols_rec.x_gen_incl_open_comm_flag,
240 P_ACTUALS_THRU_DATE => P_ACTUALS_THRU_DATE,
241 P_CI_ID_TAB => l_ci_id_tab,
242 PX_GEN_RES_ASG_ID_TAB => l_gen_res_asg_id_tab,
243 PX_DELETED_RES_ASG_ID_TAB => l_deleted_res_asg_id_tab,
244 P_COMMIT_FLAG => 'N',
245 P_INIT_MSG_FLAG => 'N',
246 X_RETURN_STATUS => X_RETURN_STATUS,
247 X_MSG_COUNT => X_MSG_COUNT,
248 X_MSG_DATA => X_MSG_DATA );
249 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
250 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
251 END IF;
252 IF p_pa_debug_mode = 'Y' THEN
253 pa_fp_gen_amount_utils.fp_debug
254 ( p_called_mode => p_called_mode,
255 p_msg => 'Status after calling pa_fp_gen_budget_amt_pub.'||
256 'gen_rev_bdgt_amt_res_sch_wrp:'||x_return_status,
257 p_module_name => l_module_name,
258 p_log_level => 5 );
259 END IF;
260 ELSE
261 IF p_pa_debug_mode = 'Y' THEN
262 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
263 P_CALLED_MODE => P_CALLED_MODE,
264 P_MSG => 'Before calling pa_fp_gen_budget_amt_pub.'||
265 'generate_budget_amt_res_sch',
266 P_MODULE_NAME => l_module_name);
267 END IF;
268 PA_FP_GEN_BUDGET_AMT_PUB.GENERATE_BUDGET_AMT_RES_SCH
269 (P_PROJECT_ID => P_PROJECT_ID,
270 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
271 P_FP_COLS_REC => P_fp_cols_rec,
272 P_PLAN_CLASS_CODE => P_fp_cols_rec.x_plan_class_code,
273 P_GEN_SRC_CODE => P_fp_cols_rec.x_gen_etc_src_code,
274 P_COST_PLAN_TYPE_ID => P_fp_cols_rec.x_gen_src_plan_type_id,
275 p_COST_VERSION_ID => l_cost_version_id,
276 P_RETAIN_MANUAL_FLAG => P_fp_cols_rec.x_gen_ret_manual_line_flag,
277 P_CALLED_MODE => P_CALLED_MODE,
278 P_INC_CHG_DOC_FLAG => P_fp_cols_rec.x_gen_incl_change_doc_flag,
279 P_INC_BILL_EVENT_FLAG => P_fp_cols_rec.x_gen_incl_bill_event_flag,
280 P_INC_OPEN_COMMIT_FLAG => P_fp_cols_rec.x_gen_incl_open_comm_flag,
281 P_ACTUALS_THRU_DATE => P_ACTUALS_THRU_DATE,
282 P_CI_ID_TAB => l_ci_id_tab,
283 PX_GEN_RES_ASG_ID_TAB => l_gen_res_asg_id_tab,
284 PX_DELETED_RES_ASG_ID_TAB => l_deleted_res_asg_id_tab,
285 P_COMMIT_FLAG => 'N',
286 P_INIT_MSG_FLAG => 'N',
287 X_RETURN_STATUS => X_RETURN_STATUS,
288 X_MSG_COUNT => X_MSG_COUNT,
289 X_MSG_DATA => X_MSG_DATA);
290 IF p_pa_debug_mode = 'Y' THEN
291 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
292 P_CALLED_MODE => P_CALLED_MODE,
293 P_MSG => 'After calling pa_fp_gen_budget_amt_pub.'||
294 'generate_budget_amt_res_sch: '||x_return_status,
295 P_MODULE_NAME => l_module_name);
296 END IF;
297 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
298 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
299 END IF;
300 END IF; -- cost-based revenue check
301 ELSIF P_FP_COLS_REC.X_VERSION_TYPE = 'REVENUE' AND
302 p_etc_plan_type_id IS NOT NULL AND
303 l_src_plan_class_code = 'FORECAST' AND
304 (l_src_version_type = 'COST' OR l_src_version_type = 'ALL') THEN
305
306 -- Added as part of fix for Bug 4232094
307 l_budget_generation_flow_flag := 'Y';
308
309 /* For revenue fcst generation, source can only be cost and all.
310 From revenue version is provented from UI.
311 Revenue fcst generation should follow the budget generation logic.*/
312 IF p_pa_debug_mode = 'Y' THEN
313 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
314 P_CALLED_MODE => P_CALLED_MODE,
315 P_MSG =>
316 'Before calling PA_FP_WP_GEN_BUDGET_AMT_PUB.'||
317 'GENERATE_WP_BUDGET_AMT',
318 P_MODULE_NAME => l_module_name);
319 END IF;
320 PA_FP_WP_GEN_BUDGET_AMT_PUB.GENERATE_WP_BUDGET_AMT
321 (P_PROJECT_ID => P_PROJECT_ID,
322 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
323 P_PLAN_CLASS_CODE => 'FORECAST',
324 P_GEN_SRC_CODE => 'FINANCIAL_PLAN',
325 P_COST_PLAN_TYPE_ID => NULL,
326 P_COST_VERSION_ID => NULL,
327 P_RETAIN_MANUAL_FLAG => P_RET_MANUAL_LNS_FLAG,
328 P_CALLED_MODE => P_CALLED_MODE,
329 P_INC_CHG_DOC_FLAG => P_INCL_CHG_DOC_FLAG,
330 P_INC_BILL_EVENT_FLAG => P_INCL_BILL_EVT_FLAG,
331 P_INC_OPEN_COMMIT_FLAG => P_INCL_OPEN_CMT_FLAG,
332 --P_CI_ID_TAB
333 P_INIT_MSG_FLAG => 'N',
334 P_COMMIT_FLAG => 'N',
335 P_CALLING_CONTEXT => 'FORECAST_GENERATION',
336 P_ETC_PLAN_TYPE_ID => P_ETC_PLAN_TYPE_ID,
337 P_ETC_PLAN_VERSION_ID => P_ETC_PLAN_VERSION_ID,
338 --P_ETC_PLAN_VERSION_NAME
339 P_ACTUALS_THRU_DATE => P_ACTUALS_THRU_DATE,
340 PX_DELETED_RES_ASG_ID_TAB => lx_deleted_res_asg_id_tab,
341 PX_GEN_RES_ASG_ID_TAB => lx_gen_res_asg_id_tab,
342 X_RETURN_STATUS => X_RETURN_STATUS,
343 X_MSG_COUNT => X_MSG_COUNT,
344 X_MSG_DATA => X_MSG_DATA );
345 IF p_pa_debug_mode = 'Y' THEN
346 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
347 P_CALLED_MODE => P_CALLED_MODE,
348 P_MSG =>
349 'After calling PA_FP_WP_GEN_BUDGET_AMT_PUB.'||
350 'GENERATE_WP_BUDGET_AMT: '||x_return_status,
351 P_MODULE_NAME => l_module_name);
352 END IF;
353 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
354 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
355 END IF;
356 ELSE
357 IF p_pa_debug_mode = 'Y' THEN
358 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
359 P_CALLED_MODE => P_CALLED_MODE,
360 P_MSG => 'Before calling pa_fp_gen_fcst_amt_pub.'||
361 'gen_fcst_task_level_amt',
362 P_MODULE_NAME => l_module_name);
363 END IF;
364 PA_FP_GEN_FCST_AMT_PUB.GEN_FCST_TASK_LEVEL_AMT
365 (P_PROJECT_ID => P_PROJECT_ID,
366 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
367 P_FP_COLS_REC => P_FP_COLS_REC,
368 P_WP_STRUCTURE_VERSION_ID => P_WP_STRUCTURE_VERSION_ID,
369 P_ETC_FP_PLAN_VERSION_ID => P_ETC_PLAN_VERSION_ID,
370 P_ACTUALS_THRU_DATE => P_ACTUALS_THRU_DATE,
371 P_ACTUALS_FROM_PERIOD => P_ACTUALS_FROM_PERIOD,
372 P_ACTUALS_TO_PERIOD => P_ACTUALS_TO_PERIOD,
373 P_ETC_FROM_PERIOD => P_ETC_FROM_PERIOD,
374 P_ETC_TO_PERIOD => P_ETC_TO_PERIOD,
375 X_RETURN_STATUS => X_RETURN_STATUS,
376 X_MSG_COUNT => X_MSG_COUNT,
377 X_MSG_DATA => X_MSG_DATA);
378
379 IF p_pa_debug_mode = 'Y' THEN
380 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
381 P_CALLED_MODE => P_CALLED_MODE,
382 P_MSG => 'After calling pa_fp_gen_fcst_amt_pub.'||
383 'gen_fcst_task_level_amt: '||x_return_status,
384 P_MODULE_NAME => l_module_name);
385 END IF;
386 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
387 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
388 END IF;
389 END IF;
390
391 --hr_utility.trace('p_fp_cols_rec.x_gen_incl_open_comm_flag:'||p_fp_cols_rec.x_gen_incl_open_comm_flag);
392 /* IF p_pa_debug_mode = 'Y' THEN
393 pa_fp_gen_amount_utils.fp_debug
394 (p_msg => 'Value of gen_incl_open_comm_flag: '||p_fp_cols_rec.x_gen_incl_open_comm_flag,
395 p_module_name => l_module_name,
396 p_log_level => 5);
397 END IF;*/
398
399 /*IF p_pa_debug_mode = 'Y' THEN
400 pa_fp_gen_amount_utils.fp_debug
401 (p_msg => 'Value of gen_incl_change_doc_flag: '||p_fp_cols_rec.x_gen_incl_change_doc_flag,
402 p_module_name => l_module_name,
403 p_log_level => 5);
404 END IF;*/
405
406 IF p_fp_cols_rec.x_gen_incl_open_comm_flag = 'Y' THEN
407 IF p_pa_debug_mode = 'Y' THEN
408 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
409 P_CALLED_MODE => P_CALLED_MODE,
410 P_MSG => 'Before calling PA_FP_GEN_COMMITMENT_AMOUNTS.'||
411 'GEN_COMMITMENT_AMOUNTS',
412 P_MODULE_NAME => l_module_name);
413 END IF;
414 PA_FP_GEN_COMMITMENT_AMOUNTS.GEN_COMMITMENT_AMOUNTS
415 (P_PROJECT_ID => P_PROJECT_ID,
416 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
417 P_FP_COLS_REC => p_fp_cols_rec,
418 PX_GEN_RES_ASG_ID_TAB => l_gen_res_asg_id_tab,
419 PX_DELETED_RES_ASG_ID_TAB => l_deleted_res_asg_id_tab,
420 X_RETURN_STATUS => X_RETURN_STATUS,
421 X_MSG_COUNT => X_MSG_COUNT,
422 X_MSG_DATA => X_MSG_DATA);
423 IF p_pa_debug_mode = 'Y' THEN
424 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
425 P_CALLED_MODE => P_CALLED_MODE,
426 P_MSG => 'After calling PA_FP_GEN_COMMITMENT_AMOUNTS.'||
427 'GEN_COMMITMENT_AMOUNTS: '||x_return_status,
428 P_MODULE_NAME => l_module_name);
429 END IF;
430 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
431 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
432 END IF;
433 END IF;--p_fp_cols_rec.x_gen_incl_open_comm_flag = 'Y'
434
435 --dbms_output.put_line('++Before reven++');
436 --dbms_output.put_line('??x_msg_count:'||x_msg_count);
437 /* IF p_pa_debug_mode = 'Y' THEN
438 pa_fp_gen_amount_utils.fp_debug
439 (p_msg => 'Value of version_type: '||p_fp_cols_rec.x_version_type,
440 p_module_name => l_module_name,
441 p_log_level => 5);
442 END IF;*/
443 --------------FOR REVENUE: FROM PAFPGAMB.pls------
444 IF (p_fp_cols_rec.x_version_type = 'ALL'
445 OR p_fp_cols_rec.x_version_type = 'REVENUE')
446 AND l_rev_gen_method = 'C' THEN
447
448 -- Bug 4232094: When the Target version is Revenue with accrual method
449 -- of Cost, time phasing is None, and the code did not go through the
450 -- budget generation flow (PAFPWPGB), then we need to call the currency
451 -- conversion API.
452
453 -- Bug 4549862: When generating a Cost and Revenue together version
454 -- from Staffing Plan with revenue accrual method of COST, the
455 -- currency conversion step is performed on the PA_FP_ROLLUP_TMP
456 -- table (instead of pa_budget_lines) earlier in the code flow by the
457 -- GENERATE_BUDGET_AMT_RES_SCH API so that pc/pfc Commitment amounts
458 -- can be honored. We should not call the currency conversion API in
459 -- this case.
460
461 IF ( p_fp_cols_rec.x_version_type = 'ALL' AND
462 p_fp_cols_rec.x_gen_etc_src_code <> 'RESOURCE_SCHEDULE' ) OR
463 (l_budget_generation_flow_flag = 'N' AND
464 p_fp_cols_rec.x_version_type = 'REVENUE' AND
465 l_rev_gen_method = 'C' AND
466 p_fp_cols_rec.x_time_phased_code = 'N') THEN
467 --Calling the currency conversion API
468 IF p_pa_debug_mode = 'Y' THEN
469 pa_fp_gen_amount_utils.fp_debug
470 (p_called_mode => p_called_mode,
471 p_msg => 'Before calling
472 pa_fp_multi_currency_pkg.convert_txn_currency',
473 p_module_name => l_module_name,
474 p_log_level => 5);
475 END IF;
476 PA_FP_MULTI_CURRENCY_PKG.CONVERT_TXN_CURRENCY
477 (p_budget_version_id => P_BUDGET_VERSION_ID,
478 p_entire_version => 'Y',
479 p_calling_module => 'FORECAST_GENERATION', -- Added for Bug#5395732
480 X_RETURN_STATUS => X_RETURN_STATUS,
481 X_MSG_COUNT => X_MSG_COUNT,
482 X_MSG_DATA => X_MSG_DATA);
483 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
484 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
485 END IF;
486
487 IF p_pa_debug_mode = 'Y' THEN
488 pa_fp_gen_amount_utils.fp_debug
489 (p_called_mode => p_called_mode,
490 p_msg => 'Status after calling
491 pa_fp_multi_currency_pkg.convert_txn_currency: '
492 ||x_return_status,
493 p_module_name => l_module_name,
494 p_log_level => 5);
495 END IF;
496 END IF;
497
498 /* Calling rollup budget version api
499 rollup amounts for the version level not required as the
500 amounts are derived from budget lines data
501 IF p_pa_debug_mode = 'Y' THEN
502 pa_fp_gen_amount_utils.fp_debug
503 (p_called_mode => p_called_mode,
504 p_msg => 'Before calling
505 pa_fp_rollup_pkg.rollup_budget_version',
506 p_module_name => l_module_name,
507 p_log_level => 5);
508 END IF;
509 PA_FP_ROLLUP_PKG.ROLLUP_BUDGET_VERSION
510 (p_budget_version_id => P_BUDGET_VERSION_ID,
511 p_entire_version => 'Y',
512 X_RETURN_STATUS => X_RETURN_STATUS,
513 X_MSG_COUNT => X_MSG_COUNT,
514 X_MSG_DATA => X_MSG_DATA);
515
516 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
517 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
518 END IF;
519 IF p_pa_debug_mode = 'Y' THEN
520 pa_fp_gen_amount_utils.fp_debug
521 (p_called_mode => p_called_mode,
522 p_msg => 'Status after calling
523 pa_fp_rollup_pkg.rollup_budget_version: '
524 ||x_return_status,
525 p_module_name => l_module_name,
526 p_log_level => 5);
527 END IF;
528 IF p_pa_debug_mode = 'Y' THEN
529 pa_fp_gen_amount_utils.fp_debug
530 (p_msg => 'Value of project_value: '||p_fp_cols_rec.x_project_value,
531 p_module_name => l_module_name,
532 p_log_level => 5);
533 END IF;
534 IF p_fp_cols_rec.x_project_value IS NULL THEN
535 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
536 p_msg_name => 'PA_FCST_NO_PRJ_VALUE');
537 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
538 END IF;
539 the error for project value null chk is handled in
540 gen cost based revenue gen API */
541 --Calling gen cost based revenue api
542 IF p_pa_debug_mode = 'Y' THEN
543 pa_fp_gen_amount_utils.fp_debug
544 (p_called_mode => p_called_mode,
545 p_msg => 'Before calling
546 pa_fp_rev_gen_pub.gen_cost_based_revenue',
547 p_module_name => l_module_name,
548 p_log_level => 5);
549 END IF;
550 --hr_utility.trace('---calling cost based rev gen---');
551 PA_FP_REV_GEN_PUB.GEN_COST_BASED_REVENUE
552 (P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
553 P_FP_COLS_REC => p_fp_cols_rec,
554 P_ETC_START_DATE => P_ACTUALS_THRU_DATE + 1,
555 X_RETURN_STATUS => X_RETURN_STATUS,
556 X_MSG_COUNT => X_MSG_COUNT,
557 X_MSG_DATA => X_MSG_DATA);
558 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
559 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
560 END IF;
561 IF p_pa_debug_mode = 'Y' THEN
562 pa_fp_gen_amount_utils.fp_debug
563 (p_called_mode => p_called_mode,
564 p_msg => 'Status after calling
565 pa_fp_rev_gen_pub.gen_cost_based_revenue'
566 ||x_return_status,
567 p_module_name => l_module_name,
568 p_log_level => 5);
569 END IF;
570 END IF;
571
572 -- Bug 4592564: Removed Unspent Amounts API call and related logic for
573 -- getting the Source Approved Budget version.
574
575 /*Only for ALL or Revenue version, revenue generation method can be set
576 and include billing event flag can be chosen. This logic is implemented
577 in both here and UI*/
578 IF (p_fp_cols_rec.x_version_type = 'ALL'
579 OR p_fp_cols_rec.x_version_type = 'REVENUE')
580 AND (l_rev_gen_method = 'E'
581 OR p_fp_cols_rec.x_gen_incl_bill_event_flag = 'Y') THEN
582 --Calling Billing Events API
583 IF p_pa_debug_mode = 'Y' THEN
584 pa_fp_gen_amount_utils.fp_debug
585 (p_called_mode => p_called_mode,
586 p_msg => 'Before calling
587 pa_fp_gen_billing_amounts.gen_billing_amounts',
588 p_module_name => l_module_name,
589 p_log_level => 5);
590 END IF;
591 PA_FP_GEN_BILLING_AMOUNTS.GEN_BILLING_AMOUNTS
592 (P_PROJECT_ID => P_PROJECT_ID,
593 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
594 P_FP_COLS_REC => p_fp_cols_rec,
595 PX_GEN_RES_ASG_ID_TAB => l_gen_res_asg_id_tab,
596 P_ETC_START_DATE => P_ACTUALS_THRU_DATE + 1,
597 PX_DELETED_RES_ASG_ID_TAB => l_deleted_res_asg_id_tab,
598 X_RETURN_STATUS => X_RETURN_STATUS,
599 X_MSG_COUNT => X_MSG_COUNT,
600 X_MSG_DATA => X_MSG_DATA);
601
602 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
603 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
604 END IF;
605 IF p_pa_debug_mode = 'Y' THEN
606 pa_fp_gen_amount_utils.fp_debug
607 (p_called_mode => p_called_mode,
608 p_msg => 'Status after calling
609 pa_fp_gen_billing_amounts.gen_billing_amounts: '
610 ||x_return_status,
611 p_module_name => l_module_name,
612 p_log_level => 5);
613 END IF;
614 END IF;
615
616 IF p_fp_cols_rec.x_version_type = 'REVENUE' THEN
617 IF p_pa_debug_mode = 'Y' THEN
618 pa_fp_gen_amount_utils.fp_debug
619 (p_called_mode => p_called_mode,
620 p_msg => 'Before calling
621 pa_fp_gen_budget_amt_pub.reset_cost_amounts',
622 p_module_name => l_module_name,
623 p_log_level => 5);
624 END IF;
625 --hr_utility.trace('######Calling RESET_COST_AMOUNTS');
626 PA_FP_GEN_BUDGET_AMT_PUB.RESET_COST_AMOUNTS
627 (P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
628 X_RETURN_STATUS => X_RETURN_STATUS,
629 X_MSG_COUNT => X_MSG_COUNT,
630 X_MSG_DATA => X_MSG_DATA);
631 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
632 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
633 END IF;
634 IF p_pa_debug_mode = 'Y' THEN
635 pa_fp_gen_amount_utils.fp_debug
636 (p_called_mode => p_called_mode,
637 p_msg => 'Status after calling
638 pa_fp_gen_budget_amt_pub.reset_cost_amounts: '
639 ||x_return_status,
640 p_module_name => l_module_name,
641 p_log_level => 5);
642 END IF;
643 END IF;
644 --dbms_output.put_line('??x_msg_count:'||x_msg_count);
645 --dbms_output.put_line('++Exiting rev part++');
646 -----------------------------------
647 /* calling the change document merge API after calling
648 the cost based revenue generation API for bug 3815353 */
649
650 IF p_fp_cols_rec.x_gen_incl_change_doc_flag = 'Y' THEN
651 IF p_etc_plan_version_id IS NOT NULL THEN
652 SELECT count(*)
653 INTO l_task_count
654 FROM pa_tasks
655 WHERE project_id = p_project_id
656 AND gen_etc_source_code = 'FINANCIAL_PLAN'
657 AND gen_etc_source_code IS NOT NULL;
658
659 IF l_task_count > 0 THEN
660 IF p_pa_debug_mode = 'Y' THEN
661 pa_fp_gen_amount_utils.fp_debug
662 (p_called_mode => p_called_mode,
663 p_msg => 'Before calling
664 pa_fp_ci_merge.copy_merged_ctrl_items',
665 p_module_name => l_module_name,
666 p_log_level => 5);
667 END IF;
668 PA_FP_CI_MERGE.copy_merged_ctrl_items
669 ( p_project_id => p_project_id
670 ,p_source_version_id => p_etc_plan_version_id
671 ,p_target_version_id => p_fp_cols_rec.x_budget_version_id
672 ,p_calling_context =>'GENERATION' --Bug 4247703
673 ,x_return_status => x_return_status
674 ,x_msg_count => x_msg_count
675 ,x_msg_data => x_msg_data);
676 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
677 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
678 END IF;
679 IF p_pa_debug_mode = 'Y' THEN
680 pa_fp_gen_amount_utils.fp_debug
681 (p_called_mode => p_called_mode,
682 p_msg => 'Status after calling
683 pa_fp_ci_merge.copy_merged_ctrl_items: '
684 ||x_return_status,
685 p_module_name => l_module_name,
686 p_log_level => 5);
687 END IF;
688 END IF;
689 END IF;
690 IF p_pa_debug_mode = 'Y' THEN
691 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
692 P_CALLED_MODE => P_CALLED_MODE,
693 P_MSG => 'Before calling pa_fp_gen_pub.'||
694 'include_change_document_wrp',
695 P_MODULE_NAME => l_module_name);
696 END IF;
697 --dbms_output.put_line('++Before chg doc++');
698 PA_FP_GEN_PUB.INCLUDE_CHANGE_DOCUMENT_WRP
699 (P_FP_COLS_REC => p_fp_cols_rec,
700 X_RETURN_STATUS => X_RETURN_STATUS,
701 X_MSG_COUNT => X_MSG_COUNT,
702 X_MSG_DATA => X_MSG_DATA);
703 --dbms_output.put_line('++After chg doc is: '||x_return_status);
704 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
705 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
706 END IF;
707 IF p_pa_debug_mode = 'Y' THEN
708 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
709 P_CALLED_MODE => P_CALLED_MODE,
710 P_MSG => 'After calling pa_fp_gen_pub.'||
711 'include_change_document_wrp:'||x_return_status,
712 P_MODULE_NAME => l_module_name);
713 END IF;
714
715 END IF;
716
717 -- Bug 4247669: We should not update the budget lines after calling
718 -- the MAINTAIN_BUDGET_VERSION API because it calls the PJI create
719 -- and delete APIs. Hence, moved UPDATE_TOTAL_PLAN_AMTS call before
720 -- the MAINTAIN_BUDGET_VERSION call.
721
722 /*Due to the request from workplan team, this part gets handled in
723 PA_FP_MAINTAIN_ACTUAL_PUB.MAINTAIN_ACTUAL_AMT_RA. Hence commented
724 out this part */
725
726 -- Bug 4232094: Uncommented the UPDATE_TOTAL_PLAN_AMTS API call to
727 -- address Issue 2 of the bug concerning inccorect ETC amounts being
728 -- displayed. In addition, added IF condition so that the API call
729 -- happens only when the Target is None timephased.
730
731 -- Bug 4292083: Commenting the UPDATE_TOTAL_PLAN_AMTS API call again
732 -- in favor of a different approach to updating the plan amounts. See
733 -- bug updates for more details.
734
735 /* Begin Comment for Bug 4292083 ************************************************
736
737 IF p_fp_cols_rec.x_time_phased_code = 'N' THEN
738 IF p_pa_debug_mode = 'Y' THEN
739 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
740 P_CALLED_MODE => P_CALLED_MODE,
741 P_MSG => 'Before calling pa_fp_gen_fcst_amt_pub.'||
742 'update_total_plan_amts',
743 P_MODULE_NAME => l_module_name);
744 END IF;
745 PA_FP_GEN_FCST_AMT_PVT.UPDATE_TOTAL_PLAN_AMTS
746 (P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
747 X_RETURN_STATUS => X_RETURN_STATUS,
748 X_MSG_COUNT => X_MSG_COUNT,
749 X_MSG_DATA => X_MSG_DATA);
750 --dbms_output.put_line('Status of update total plan amts api: '||X_RETURN_STATUS);
751 --dbms_output.put_line('??x_msg_count:'||x_msg_count);
752 IF p_pa_debug_mode = 'Y' THEN
753 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
754 P_CALLED_MODE => P_CALLED_MODE,
755 P_MSG => 'AFTER CALLING PA_FP_GEN_FCST_AMT_PUB.'||
756 'UPDATE_TOTAL_PLAN_AMTS:'||x_return_status,
757 P_MODULE_NAME => l_module_name);
758 END IF;
759 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
760 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
761 END IF;
762 END IF;
763
764 ** End Comment for Bug 4292083 **************************************************/
765
766 -- Call API to update pa_budget_lines.other_rejection_code
767 -- with any ETC revenue amount calculation errors.
768 -- See bug 5203622 for more details.
769 IF p_pa_debug_mode = 'Y' THEN
770 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
771 P_MSG => 'Before calling PA_FP_GEN_FCST_AMT_PUB.' ||
772 'UPD_REV_CALCULATION_ERR',
773 P_CALLED_MODE => p_called_mode,
774 P_MODULE_NAME => l_module_name);
775 END IF;
776 PA_FP_GEN_FCST_AMT_PUB.UPD_REV_CALCULATION_ERR
777 (P_PROJECT_ID => p_project_id,
778 P_BUDGET_VERSION_ID => p_budget_version_id,
779 P_FP_COLS_REC => p_fp_cols_rec,
780 P_ETC_START_DATE => p_actuals_thru_date + 1,
781 P_CALLED_MODE => p_called_mode,
782 X_RETURN_STATUS => X_RETURN_STATUS,
783 X_MSG_COUNT => X_MSG_COUNT,
784 X_MSG_DATA => X_MSG_DATA);
785 IF p_pa_debug_mode = 'Y' THEN
786 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
787 P_MSG => 'After calling PA_FP_GEN_FCST_AMT_PUB.' ||
788 'UPD_REV_CALCULATION_ERR: '||x_return_status,
789 P_CALLED_MODE => p_called_mode,
790 P_MODULE_NAME => l_module_name);
791 END IF;
792
793 --Start Bug 5726785
794
795 PA_FP_GEN_FCST_AMT_PUB1.call_clnt_extn_and_update_bl
796 (p_project_id => p_project_id
797 ,p_budget_version_id => p_budget_version_id
798 ,x_call_maintain_data_api => l_call_maintain_data_api
799 ,x_return_status => x_return_status
800 ,x_msg_count => x_msg_count
801 ,x_msg_data => x_msg_data);
802
803 IF p_pa_debug_mode = 'Y' THEN
804 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
805 P_CALLED_MODE => P_CALLED_MODE,
806 P_MSG => 'After calling pa_fp_gen_fcst_amt_pub.'||
807 'call_clnt_extn_and_update_bl '||x_return_status,
808 P_MODULE_NAME => l_module_name);
809 END IF;
810 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
811 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
812 END IF;
813
814
815
816 --End Bug 5726785
817
818
819 IF p_pa_debug_mode = 'Y' THEN
820 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
821 P_CALLED_MODE => P_CALLED_MODE,
822 P_MSG => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.'||
823 'MAINTAIN_BUDGET_VERSION',
824 P_MODULE_NAME => l_module_name);
825 END IF;
826 --dbms_output.put_line('++Before pub1.maintain_bv++');
827 PA_FP_GEN_FCST_AMT_PUB1.MAINTAIN_BUDGET_VERSION
828 (P_PROJECT_ID => P_PROJECT_ID,
829 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
830 P_ETC_START_DATE => P_ACTUALS_THRU_DATE + 1,
831 P_CALL_MAINTAIN_DATA_API => L_CALL_MAINTAIN_DATA_API,
832 X_RETURN_STATUS => x_return_status,
833 X_MSG_COUNT => x_msg_count,
834 X_MSG_DATA => x_msg_data );
835 --dbms_output.put_line('++AFter pub1.maintain_bv++:'||x_return_status);
836 --dbms_output.put_line('??x_msg_count:'||x_msg_count);
837 IF p_pa_debug_mode = 'Y' THEN
838 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
839 P_CALLED_MODE => P_CALLED_MODE,
840 P_MSG => 'After calling PA_FP_GEN_FCST_AMT_PUB1.'||
841 'MAINTAIN_BUDGET_VERSION: '||x_return_status,
842 P_MODULE_NAME => l_module_name);
843 END IF;
844 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
845 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
846 END IF;
847
848 IF P_COMMIT_FLAG = 'Y' THEN
849 COMMIT;
850 END IF;
851 IF p_pa_debug_mode = 'Y' THEN
852 PA_DEBUG.Reset_Curr_Function;
853 END IF;
854 --dbms_output.put_line('??END: x_msg_count:'||x_msg_count);
855 /*temp */
856 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
857 x_msg_count := 0;
858 END IF;
859 EXCEPTION
860 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
861 l_msg_count := FND_MSG_PUB.count_msg;
862 IF l_msg_count = 1 THEN
863 PA_INTERFACE_UTILS_PUB.get_messages
864 (p_encoded => FND_API.G_TRUE
865 ,p_msg_index => 1
866 ,p_msg_count => l_msg_count
867 ,p_msg_data => l_msg_data
868 ,p_data => l_data
869 ,p_msg_index_out => l_msg_index_out);
870 x_msg_data := l_data;
871 x_msg_count := l_msg_count;
872 ELSE
873 x_msg_count := l_msg_count;
874 END IF;
875 ROLLBACK;
876
877 x_return_status := FND_API.G_RET_STS_ERROR;
878 IF P_PA_DEBUG_MODE = 'Y' THEN
879 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
880 P_CALLED_MODE => P_CALLED_MODE,
881 P_MSG => 'Invalid Arguments Passed',
882 P_MODULE_NAME => l_module_name);
883 PA_DEBUG.Reset_Curr_Function;
884 END IF;
885 RAISE;
886
887 WHEN OTHERS THEN
888 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
889 x_msg_data := SUBSTR(SQLERRM,1,240);
890 FND_MSG_PUB.add_exc_msg
891 ( p_pkg_name => 'PA_FP_GEN_FCST_AMT_PUB'
892 ,p_procedure_name => 'GENERATE_FCST_AMT_WRP');
893 IF P_PA_DEBUG_MODE = 'Y' THEN
894 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
895 P_CALLED_MODE => P_CALLED_MODE,
896 P_MSG => 'Unexpected Error'||substr(sqlerrm, 1, 240),
897 P_MODULE_NAME => l_module_name);
898 PA_DEBUG.Reset_Curr_Function;
899 END IF;
900 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
901
902 END GENERATE_FCST_AMT_WRP;
903
904 PROCEDURE GEN_FCST_TASK_LEVEL_AMT
905 (P_PROJECT_ID IN PA_PROJECTS_ALL.PROJECT_ID%TYPE,
906 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
907 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
908 P_WP_STRUCTURE_VERSION_ID IN PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE,
909 P_ETC_FP_PLAN_VERSION_ID IN PA_PROJ_FP_OPTIONS.FIN_PLAN_VERSION_ID%TYPE,
910 P_ACTUALS_THRU_DATE IN PA_PERIODS_ALL.END_DATE%TYPE,
911 P_ACTUALS_FROM_PERIOD IN VARCHAR2,
912 P_ACTUALS_TO_PERIOD IN VARCHAR2,
913 P_ETC_FROM_PERIOD IN VARCHAR2,
914 P_ETC_TO_PERIOD IN VARCHAR2,
915 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
916 X_MSG_COUNT OUT NOCOPY NUMBER,
917 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
918
919 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_fcst_amt_pub.gen_fcst_task_level_amt';
920
921 l_latest_published_fwbs_id NUMBER;
922 l_proj_struc_sharing_code VARCHAR2(30);
923
924 l_wp_version_id NUMBER;
925 l_fp_cols_rec_target PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
926 l_fp_cols_rec_wp PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
927 l_fp_cols_rec_fp PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
928 l_fp_cols_rec_approved PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
929 l_resource_list_id NUMBER;
930 l_struct_ver_id NUMBER;
931 l_calendar_type VARCHAR2(3);
932 l_record_type VARCHAR2(10);
933
934 l_calling_context VARCHAR2(30);
935
936 CURSOR traverse_tasks_cur(c_gen_etc_src_code PA_PROJ_FP_OPTIONS.GEN_COST_SRC_CODE%TYPE) IS
937 SELECT task_id,
938 DECODE(c_gen_etc_src_code,
939 NULL,NVL(gen_etc_source_code,'NONE'),
940 c_gen_etc_src_code)
941 FROM pa_tasks t
942 WHERE project_id = P_PROJECT_ID;
943
944 CURSOR traverse_top_tasks_cur(c_gen_etc_src_code PA_PROJ_FP_OPTIONS.GEN_COST_SRC_CODE%TYPE) IS
945 SELECT task_id,
946 DECODE(c_gen_etc_src_code,
947 NULL,NVL(gen_etc_source_code,'NONE'),
948 c_gen_etc_src_code)
949 FROM pa_tasks t
950 WHERE project_id = P_PROJECT_ID and
951 parent_task_id is null;
952
953 l_temp_top_task_id PA_TASKS.TOP_TASK_ID%TYPE;
954 CURSOR traverse_child_tasks_cur IS
955 SELECT task_id
956 FROM pa_tasks t
957 WHERE project_id = P_PROJECT_ID and
958 top_task_id = l_temp_top_task_id and
959 task_id <> top_task_id; -- don't want to retrieve the current node
960
961 l_top_task_id_tab PA_PLSQL_DATATYPES.NumTabTyp; -- ETC Enhancements 10/2004
962 l_child_task_id_tab PA_PLSQL_DATATYPES.NumTabTyp; -- ETC Enhancements 10/2004
963 l_top_gen_etc_src_code_tab pa_plsql_datatypes.Char30TabTyp; -- ETC Enhancements 10/2004
964 l_task_id_tab PA_PLSQL_DATATYPES.NumTabTyp;
965 l_gen_etc_source_code_tab pa_plsql_datatypes.Char30TabTyp;
966 l_curr_task_id PA_TASKS.TASK_ID%TYPE;
967 l_curr_etc_source PA_TASKS.GEN_ETC_SOURCE_CODE%TYPE;
968 l_curr_etc_ver_id NUMBER;
969 l_curr_etc_method_code VARCHAR2(30);
970 l_curr_src_ra_id NUMBER;
971 l_curr_tgt_ra_id NUMBER;
972 l_curr_rlm_id NUMBER;
973 l_txn_amt_rec PA_FP_GEN_FCST_AMT_PUB.TXN_AMT_REC_TYP;
974 l_work_qty_cnt NUMBER:= 0;
975
976 /* Indices for ETC method PL/SQL tables */
977 l_rb_index BINARY_INTEGER;
978 l_bc_index BINARY_INTEGER;
979 l_ev_index BINARY_INTEGER;
980
981 /* PL/SQL tables for Remaining Budget - Performance Bug 4194849 */
982 l_rb_src_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
983 l_rb_tgt_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
984 l_rb_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
985 l_rb_rlm_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
986 l_rb_etc_source_tab PA_PLSQL_DATATYPES.Char30TabTyp;
987
988 /* PL/SQL tables for Budget To Complete - Performance Bug 4194849 */
989 l_bc_src_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
990 l_bc_tgt_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
991 l_bc_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
992 l_bc_rlm_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
993 l_bc_etc_source_tab PA_PLSQL_DATATYPES.Char30TabTyp;
994
995 /* PL/SQL tables for Earned Value - Performance Bug 4194849 */
996 l_ev_src_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
997 l_ev_tgt_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
998 l_ev_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
999 l_ev_rlm_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1000 l_ev_etc_source_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1001
1002 -- Bug 4114589: When populating temporary table data, we need to process
1003 -- tasks with source of Average of Actuals separately after copying actuals.
1004 l_avg_actuals_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1005
1006 -- Bug 4089850: Modified traverse_tasks_rlm_cur so that we no longer
1007 -- join with pa_tasks to get the ETC source code. Instead, we return
1008 -- the transaction_source_code from tmp1.
1009
1010 CURSOR traverse_tasks_rlm_cur (c_gen_etc_source_code VARCHAR2) IS
1011 SELECT tmp1.task_id,
1012 NVL(c_gen_etc_source_code, NVL(tmp1.transaction_source_code,'NONE')),
1013 tmp1.resource_assignment_id,
1014 tmp1.target_res_asg_id,
1015 tmp1.resource_list_member_id,
1016 tmp1.etc_method_code
1017 FROM PA_FP_CALC_AMT_TMP1 tmp1;
1018
1019 l_task_id_tab2 PA_PLSQL_DATATYPES.NumTabTyp;
1020 l_gen_etc_source_code_tab2 pa_plsql_datatypes.Char30TabTyp;
1021 l_src_ra_id_tab2 PA_PLSQL_DATATYPES.NumTabTyp;
1022 l_tgt_ra_id_tab2 PA_PLSQL_DATATYPES.NumTabTyp;
1023 l_rlm_id_tab2 PA_PLSQL_DATATYPES.NumTabTyp;
1024 l_etc_method_tab2 pa_plsql_datatypes.Char30TabTyp;
1025
1026 l_gen_etc_source_code_override VARCHAR2(30);
1027
1028 --in param for PPA_FP_MAP_BV_PUB.GEN_MAP_BV_TO_TARGET_RL to
1029 --get baselined cost.
1030 l_approved_fp_version_id pa_proj_fp_options.fin_plan_version_id%TYPE;
1031 l_approved_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
1032
1033 l_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
1034 l_version_type VARCHAR2(15);
1035
1036 l_plan_level VARCHAR2(15);
1037
1038 l_amt_dtls_tbl pa_fp_maintain_actual_pub.l_amt_dtls_tbl_typ;
1039
1040 /*For None Time Phase*/
1041 l_start_date DATE;
1042 l_end_date DATE;
1043
1044 --local PL/SQL table used for calling Calculate API
1045 l_refresh_rates_flag VARCHAR2(1);
1046 l_refresh_conv_rates_flag VARCHAR2(1);
1047 l_spread_required_flag VARCHAR2(1);
1048 l_conv_rates_required_flag VARCHAR2(1);
1049 l_raTxn_rollup_api_call_flag VARCHAR2(1); -- Added for IPM new entity ER
1050
1051 l_source_context VARCHAR2(30) :='RESOURCE_ASSIGNMENT';
1052 l_cal_ra_id_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1053 l_cal_src_ra_id_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1054 l_cal_rate_based_flag_tab SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
1055 l_cal_rlm_id_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1056 l_cal_task_id_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1057 l_cal_etc_method_code_tab SYSTEM.pa_varchar2_30_tbl_type:=SYSTEM.pa_varchar2_30_tbl_type();
1058 l_cal_rcost_rate_override_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1059 l_cal_bcost_rate_override_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1060 l_cal_bill_rate_override_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1061 l_delete_budget_lines_tab SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
1062 l_spread_amts_flag_tab SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
1063 l_cal_txn_currency_code_tab SYSTEM.pa_varchar2_15_tbl_type:=SYSTEM.pa_varchar2_15_tbl_type();
1064 l_txn_currency_override_tab SYSTEM.pa_varchar2_15_tbl_type:=SYSTEM.pa_varchar2_15_tbl_type();
1065 l_cal_etc_qty_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1066 l_addl_qty_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1067 l_cal_etc_raw_cost_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1068 l_addl_raw_cost_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1069 l_cal_etc_burdened_cost_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1070 l_addl_burdened_cost_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1071 l_cal_etc_revenue_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1072 l_addl_revenue_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1073 l_raw_cost_rate_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1074 l_b_cost_rate_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1075 l_bill_rate_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1076 l_line_start_date_tab SYSTEM.pa_date_tbl_type:=SYSTEM.pa_date_tbl_type();
1077 l_line_end_date_tab SYSTEM.pa_date_tbl_type:=SYSTEM.pa_date_tbl_type();
1078 l_input_period_rates_tbl PA_FP_FCST_GEN_CLIENT_EXT.l_pds_rate_dtls_tab;
1079 l_period_rates_tbl PA_FP_FCST_GEN_CLIENT_EXT.l_pds_rate_dtls_tab;
1080 l_cal_unit_of_measure_tab SYSTEM.pa_varchar2_30_tbl_type:=SYSTEM.pa_varchar2_30_tbl_type();
1081
1082 L_RES_ASG_UOM_UPDATE_TAB PA_PLSQL_DATATYPES.IdTabTyp;
1083
1084 l_target_class_rlm_id NUMBER;
1085 l_rev_gen_method VARCHAR2(10);
1086
1087 l_msg_count NUMBER;
1088 l_msg_data VARCHAR2(2000);
1089 l_data VARCHAR2(2000);
1090 l_msg_index_out NUMBER:=0;
1091
1092 l_count_tmp NUMBER;
1093 l_test NUMBER;
1094 p_called_mode varchar2(20) := 'SELF_SERVICE';
1095 l_dummy NUMBER;
1096 l_date date;
1097 l_count number;
1098 l_task_index NUMBER; -- used for populating task pl/sql tables
1099
1100 l_fcst_etc_qty NUMBER;
1101 l_fcst_etc_raw_cost NUMBER;
1102 l_fcst_etc_burdened_cost NUMBER;
1103 l_fcst_etc_revenue NUMBER;
1104 l_init_qty NUMBER;
1105 l_init_raw_cost NUMBER;
1106 l_init_burdened_cost NUMBER;
1107 l_init_revenue NUMBER;
1108
1109 l_fp_cols_rec_src PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
1110 /* Bug 4369741: Replaced l_planning_options_flag with 2 separate
1111 * planning options flags for WP and FP later in the declaration. */
1112 --l_planning_options_flag VARCHAR2(1);
1113 l_ra_txn_source_code PA_RESOURCE_ASSIGNMENTS.TRANSACTION_SOURCE_CODE%TYPE;
1114 l_bl_count NUMBER;
1115
1116 l_cnt NUMBER := 1;
1117 l_cal_ra_id_tab_tmp SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1118 l_cal_txn_curr_code_tab_tmp SYSTEM.pa_varchar2_15_tbl_type:=SYSTEM.pa_varchar2_15_tbl_type();
1119 l_cal_rate_based_flag_tab_tmp SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
1120 l_cal_rlm_id_tab_tmp SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1121 l_cal_task_id_tab_tmp SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1122 l_cal_unit_of_measure_tab_tmp SYSTEM.pa_varchar2_30_tbl_type:=SYSTEM.pa_varchar2_30_tbl_type();
1123 l_cal_etc_method_code_tab_tmp SYSTEM.pa_varchar2_30_tbl_type:=SYSTEM.pa_varchar2_30_tbl_type();
1124 l_cal_etc_qty_tab_tmp SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1125 l_cal_etc_raw_cost_tab_tmp SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1126 l_cal_etc_brdn_cost_tab_tmp SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1127 l_cal_etc_revenue_tab_tmp SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1128
1129 l_fcst_gen_src_code PA_PROJ_FP_OPTIONS.GEN_COST_SRC_CODE%TYPE;
1130
1131 l_etc_start_date DATE;
1132
1133 /* Bug 3968748: Variables for populating PA_FP_GEN_RATE_TMP */
1134 l_ext_period_name_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1135 l_ext_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1136 l_ext_burdened_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1137 l_ext_revenue_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1138
1139 l_entered_flag VARCHAR2(1) := 'N';
1140 l_proceed_flag VARCHAR2(1) := 'Y';
1141
1142 -- Bug 4346172: l_use_src_rates_flag will be 'Y' when the src/tgt planning
1143 -- options match, and when the source ETC method is not EARNED_VALUE.
1144 l_use_src_rates_flag VARCHAR2(1);
1145
1146 /* Variables Added for ER 4376722 */
1147 l_billable_flag_tab SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
1148 l_billable_flag_tab_tmp SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
1149 l_cal_src_ra_id_tab_tmp SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1150
1151 -- This index is used in billability logic to track the running index of the _tmp tables
1152 l_tmp_index NUMBER;
1153
1154 /* Variables Added for Bug 4369741 */
1155 l_wp_planning_options_flag VARCHAR2(1);
1156 l_fp_planning_options_flag VARCHAR2(1);
1157
1158 l_gen_etc_src_code_tab_tmp SYSTEM.pa_varchar2_30_tbl_type:=SYSTEM.pa_varchar2_30_tbl_type();
1159 l_gen_etc_src_code_tab SYSTEM.pa_varchar2_30_tbl_type:=SYSTEM.pa_varchar2_30_tbl_type();
1160
1161 -- Bug 4346172: As part of the fix, added join on pa_fp_calc_amt_tmp1
1162 -- to get the correct source ETC method code.
1163 -- As a result of further testing, discovered that the original fix
1164 -- introduced a duplicate resource error when Commitments are included.
1165 -- PROBLEM:
1166 -- Normally, there should be 1 record per target_res_asg_id in the
1167 -- PA_FP_CALC_AMT_TMP1 table. However, when Commitments are included,
1168 -- extra PA_FP_CALC_AMT_TMP1 records are added for commitment records.
1169 -- As a result, the query returned 2 records for target resources with
1170 -- commitments and ETC.
1171 -- SOLUTION:
1172 -- Ignore records in the PA_FP_CALC_AMT_TMP1 table with transaction
1173 -- source code of 'OPEN_COMMITMENTS'. This will ensure that the query
1174 -- retrieves at most 1 record per target_res_asg_id. Note that we do
1175 -- not need to worry about the scenario when there is a commitment
1176 -- record in the temp table but no ETC record, since the purpose of
1177 -- this query is to get ETC amounts (and Commitments are generated
1178 -- later by a separate API).
1179
1180 -- ER 4376722: To carry out the Task Billability logic, we need to
1181 -- modify the queries to fetch the task billable_flag for each target
1182 -- resource. Since ra.task_id can be NULL or 0, we take the outer
1183 -- join: NVL(ra.task_id,0) = ta.task_id (+). By default, tasks are
1184 -- billable, so we SELECT NVL(ta.billable_flag,'Y').
1185
1186 -- Bug 4571025: To avoid TMP2 records matching multiple TMP1 records,
1187 -- added the following additional WHERE clause join condition to ETC
1188 -- amount cursors (etc_amts_cur_wp_opt_same, etc_amts_cur_fp_opt_same,
1189 -- etc_amts_cur_wp_fp_opt_diff, and etc_amts_cur_wp_fp_opt_same):
1190 -- AND tmp_ra.resource_assignment_id = tmp.resource_assignment_id
1191
1192 /* Bug 4369741: Added cursor etc_amts_cur_wp_fp_opt_same to be used in
1193 * the following scenarios:
1194 * 1. Target ETC generation source = 'WORKPLAN_RESOURCES'
1195 * l_wp_planning_options_flag = Y
1196 * 2. Target ETC generation source = 'FINANCIAL_PLAN'
1197 * l_fp_planning_options_flag = Y
1198 * 3. Target ETC generation source = 'TASK_LEVEL_SEL'
1199 * l_wp_planning_options_flag = Y
1200 * l_fp_planning_options_flag = Y
1201 * When the ETC generation source is Workplan or Financial Plan,
1202 * 'WORKPLAN_RESOURCES' or 'FINANCIAL_PLAN' should be passed for the
1203 * c_gen_etc_source_code cursor parameter, respectively.
1204 * When the ETC generation source is Task Level Selection, the
1205 * c_gen_etc_source_code cursor parameter should be NULL so that the
1206 * cursor picks up each task's generation source. */
1207
1208 CURSOR etc_amts_cur_wp_fp_opt_same
1209 (c_gen_etc_source_code VARCHAR2 DEFAULT NULL) IS
1210 SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1211 INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1212 tmp.RESOURCE_ASSIGNMENT_ID,
1213 tmp.TARGET_RES_ASG_ID,
1214 tmp.ETC_CURRENCY_CODE,
1215 ra.rate_based_flag,
1216 ra.resource_list_member_id,
1217 ra.task_id,
1218 ra.unit_of_measure,
1219 tmp_ra.etc_method_code,
1220 SUM(tmp.ETC_PLAN_QUANTITY),
1221 SUM(tmp.ETC_TXN_RAW_COST),
1222 SUM(tmp.ETC_TXN_BURDENED_COST),
1223 SUM(tmp.ETC_TXN_REVENUE),
1224 NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1225 nvl(c_gen_etc_source_code, /* Added for Bug 4369741 */
1226 nvl(tmp_ra.transaction_source_code, 'NONE'))
1227 FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
1228 PA_FP_CALC_AMT_TMP2 tmp,
1229 PA_RESOURCE_ASSIGNMENTS ra,
1230 pa_tasks ta /* Added for ER 4376722 */
1231 WHERE tmp.target_res_asg_id = ra.resource_assignment_id
1232 AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
1233 AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id
1234 AND tmp_ra.transaction_source_code <> 'OPEN_COMMITMENTS' /* Bug 4346172 */
1235 and NVL(ra.task_id,0) = ta.task_id (+) /* Added for ER 4376722 */
1236 --and ta.project_id = P_PROJECT_ID /* Added for ER 4376722 */
1237 AND ra.budget_version_id = P_BUDGET_VERSION_ID /* Added for Bug 4369741 Perf */
1238 AND ra.project_id = P_PROJECT_ID /* Added for Bug 4369741 Perf */
1239 AND tmp_ra.resource_assignment_id = tmp.resource_assignment_id /* Added for Bug 4571025 */
1240 GROUP BY tmp.RESOURCE_ASSIGNMENT_ID,
1241 tmp.TARGET_RES_ASG_ID,
1242 tmp.ETC_CURRENCY_CODE,
1243 ra.rate_based_flag,
1244 ra.resource_list_member_id,
1245 ra.task_id,
1246 ra.unit_of_measure,
1247 tmp_ra.etc_method_code,
1248 NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1249 nvl(c_gen_etc_source_code, /* Added for Bug 4369741 */
1250 nvl(tmp_ra.transaction_source_code, 'NONE'));
1251
1252 -- 5/10/05 : When planning options do not match, we potentially
1253 -- have many source resources mapping to each target resource. As
1254 -- such, we cannot determine a single source ETC method. Thus,
1255 -- replaced ra.etc_method_code with NULL in the SELECT clause below.
1256
1257 /* Bug 4369741: Added cursor etc_amts_cur_wp_fp_opt_diff to be used in
1258 * the following scenarios:
1259 * 1. Target ETC generation source = 'WORKPLAN_RESOURCES'
1260 * l_wp_planning_options_flag = N
1261 * 2. Target ETC generation source = 'FINANCIAL_PLAN'
1262 * l_fp_planning_options_flag = N
1263 * 3. Target ETC generation source = 'TASK_LEVEL_SEL'
1264 * l_wp_planning_options_flag = N
1265 * l_fp_planning_options_flag = N
1266 * When the ETC generation source is Workplan or Financial Plan,
1267 * 'WORKPLAN_RESOURCES' or 'FINANCIAL_PLAN' should be passed for the
1268 * c_gen_etc_source_code cursor parameter, respectively.
1269 * When the ETC generation source is Task Level Selection, the
1270 * c_gen_etc_source_code cursor parameter should be NULL so that the
1271 * cursor picks up each task's generation source. */
1272
1273 CURSOR etc_amts_cur_wp_fp_opt_diff
1274 (c_gen_etc_source_code VARCHAR2 DEFAULT NULL) IS
1275 SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1276 INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1277 sum(1*null),
1278 tmp.TARGET_RES_ASG_ID,
1279 tmp.ETC_CURRENCY_CODE,
1280 ra.rate_based_flag,
1281 ra.resource_list_member_id,
1282 ra.task_id,
1283 ra.unit_of_measure,
1284 null, --ra.etc_method_code,
1285 SUM(tmp.ETC_PLAN_QUANTITY),
1286 SUM(tmp.ETC_TXN_RAW_COST),
1287 SUM(tmp.ETC_TXN_BURDENED_COST),
1288 SUM(tmp.ETC_TXN_REVENUE),
1289 NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1290 nvl(c_gen_etc_source_code, /* Added for Bug 4369741 */
1291 nvl(tmp_ra.transaction_source_code, 'NONE'))
1292 FROM PA_FP_CALC_AMT_TMP1 tmp_ra, /* Added for Bug 4369741 */
1293 PA_FP_CALC_AMT_TMP2 tmp,
1294 PA_RESOURCE_ASSIGNMENTS ra,
1295 pa_tasks ta /* Added for ER 4376722 */
1296 WHERE tmp.target_res_asg_id = ra.resource_assignment_id
1297 AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
1298 and NVL(ra.task_id,0) = ta.task_id (+) /* Added for ER 4376722 */
1299 --and ta.project_id = P_PROJECT_ID /* Added for ER 4376722 */
1300 AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id /* Added for Bug 4369741 */
1301 AND tmp_ra.transaction_source_code <> 'OPEN_COMMITMENTS' /* Added for Bug 4369741 */
1302 AND ra.budget_version_id = P_BUDGET_VERSION_ID /* Added for Bug 4369741 Perf */
1303 AND ra.project_id = P_PROJECT_ID /* Added for Bug 4369741 Perf */
1304 AND tmp_ra.resource_assignment_id = tmp.resource_assignment_id /* Added for Bug 4571025 */
1305 GROUP BY
1306 tmp.TARGET_RES_ASG_ID,
1307 tmp.ETC_CURRENCY_CODE,
1308 ra.rate_based_flag,
1309 ra.resource_list_member_id,
1310 ra.task_id,
1311 ra.unit_of_measure,
1312 null, --ra.etc_method_code,
1313 NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1314 nvl(c_gen_etc_source_code, /* Added for Bug 4369741 */
1315 nvl(tmp_ra.transaction_source_code, 'NONE'));
1316
1317 /* Bug 4369741: Added cursor etc_amts_cur_wp_opt_same to be used in
1318 * the following scenario:
1319 * 1. Target ETC generation source = 'TASK_LEVEL_SEL'
1320 * l_wp_planning_options_flag = Y
1321 * l_fp_planning_options_flag = N
1322 * This cursor's SELECT statement uses the etc_amts_cur_wp_fp_opt_same
1323 * cursor's SELECT statement for resources with Workplan source UNION
1324 * ALL the etc_amts_cur_wp_fp_opt_diff cursor's SELECT statement for
1325 * resources with non-Workplan source. */
1326
1327 CURSOR etc_amts_cur_wp_opt_same IS
1328 SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1329 INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1330 tmp.RESOURCE_ASSIGNMENT_ID,
1331 tmp.TARGET_RES_ASG_ID,
1332 tmp.ETC_CURRENCY_CODE,
1333 ra.rate_based_flag,
1334 ra.resource_list_member_id,
1335 ra.task_id,
1336 ra.unit_of_measure,
1337 tmp_ra.etc_method_code,
1338 SUM(tmp.ETC_PLAN_QUANTITY),
1339 SUM(tmp.ETC_TXN_RAW_COST),
1340 SUM(tmp.ETC_TXN_BURDENED_COST),
1341 SUM(tmp.ETC_TXN_REVENUE),
1342 NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1343 tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1344 FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
1345 PA_FP_CALC_AMT_TMP2 tmp,
1346 PA_RESOURCE_ASSIGNMENTS ra,
1347 pa_tasks ta /* Added for ER 4376722 */
1348 WHERE tmp.target_res_asg_id = ra.resource_assignment_id
1349 AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
1350 AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id
1351 AND tmp_ra.transaction_source_code = 'WORKPLAN_RESOURCES' /* Added for Bug 4369741 */
1352 and NVL(ra.task_id,0) = ta.task_id (+) /* Added for ER 4376722 */
1353 --and ta.project_id = P_PROJECT_ID /* Added for ER 4376722 */
1354 AND ra.budget_version_id = P_BUDGET_VERSION_ID /* Added for Bug 4369741 Perf */
1355 AND ra.project_id = P_PROJECT_ID /* Added for Bug 4369741 Perf */
1356 AND tmp_ra.resource_assignment_id = tmp.resource_assignment_id /* Added for Bug 4571025 */
1357 GROUP BY tmp.RESOURCE_ASSIGNMENT_ID,
1358 tmp.TARGET_RES_ASG_ID,
1359 tmp.ETC_CURRENCY_CODE,
1360 ra.rate_based_flag,
1361 ra.resource_list_member_id,
1362 ra.task_id,
1363 ra.unit_of_measure,
1364 tmp_ra.etc_method_code,
1365 NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1366 tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1367 UNION ALL
1368 SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1369 INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1370 sum(1*null),
1371 tmp.TARGET_RES_ASG_ID,
1372 tmp.ETC_CURRENCY_CODE,
1373 ra.rate_based_flag,
1374 ra.resource_list_member_id,
1375 ra.task_id,
1376 ra.unit_of_measure,
1377 null, --ra.etc_method_code,
1378 SUM(tmp.ETC_PLAN_QUANTITY),
1379 SUM(tmp.ETC_TXN_RAW_COST),
1380 SUM(tmp.ETC_TXN_BURDENED_COST),
1381 SUM(tmp.ETC_TXN_REVENUE),
1382 NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1383 tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1384 FROM PA_FP_CALC_AMT_TMP1 tmp_ra, /* Added for Bug 4369741 */
1385 PA_FP_CALC_AMT_TMP2 tmp,
1386 PA_RESOURCE_ASSIGNMENTS ra,
1387 pa_tasks ta /* Added for ER 4376722 */
1388 WHERE tmp.target_res_asg_id = ra.resource_assignment_id
1389 AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
1390 and NVL(ra.task_id,0) = ta.task_id (+) /* Added for ER 4376722 */
1391 --and ta.project_id = P_PROJECT_ID /* Added for ER 4376722 */
1392 AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id /* Added for Bug 4369741 */
1393 AND tmp_ra.transaction_source_code <> 'OPEN_COMMITMENTS' /* Added for Bug 4369741 */
1394 AND tmp_ra.transaction_source_code <> 'WORKPLAN_RESOURCES' /* Added for Bug 4369741 */
1395 AND ra.budget_version_id = P_BUDGET_VERSION_ID /* Added for Bug 4369741 Perf */
1396 AND ra.project_id = P_PROJECT_ID /* Added for Bug 4369741 Perf */
1397 AND tmp_ra.resource_assignment_id = tmp.resource_assignment_id /* Added for Bug 4571025 */
1398 GROUP BY
1399 tmp.TARGET_RES_ASG_ID,
1400 tmp.ETC_CURRENCY_CODE,
1401 ra.rate_based_flag,
1402 ra.resource_list_member_id,
1403 ra.task_id,
1404 ra.unit_of_measure,
1405 null, --ra.etc_method_code,
1406 NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1407 tmp_ra.transaction_source_code; /* Added for Bug 4369741 */
1408
1409 /* Bug 4369741: Added cursor etc_amts_cur_fp_opt_same to be used in
1410 * the following scenario:
1411 * 1. Target ETC generation source = 'TASK_LEVEL_SEL'
1412 * l_wp_planning_options_flag = N
1413 * l_fp_planning_options_flag = Y
1414 * This cursor's SELECT statement uses the etc_amts_cur_wp_fp_opt_same
1415 * cursor's SELECT statement for resources with Financial Plan source
1416 * UNION ALL the etc_amts_cur_wp_fp_opt_diff cursor's SELECT statement for
1417 * resources with non Financial Plan source. */
1418
1419 CURSOR etc_amts_cur_fp_opt_same IS
1420 SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1421 INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1422 tmp.RESOURCE_ASSIGNMENT_ID,
1423 tmp.TARGET_RES_ASG_ID,
1424 tmp.ETC_CURRENCY_CODE,
1425 ra.rate_based_flag,
1426 ra.resource_list_member_id,
1427 ra.task_id,
1428 ra.unit_of_measure,
1429 tmp_ra.etc_method_code,
1430 SUM(tmp.ETC_PLAN_QUANTITY),
1431 SUM(tmp.ETC_TXN_RAW_COST),
1432 SUM(tmp.ETC_TXN_BURDENED_COST),
1433 SUM(tmp.ETC_TXN_REVENUE),
1434 NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1435 tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1436 FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
1437 PA_FP_CALC_AMT_TMP2 tmp,
1438 PA_RESOURCE_ASSIGNMENTS ra,
1439 pa_tasks ta /* Added for ER 4376722 */
1440 WHERE tmp.target_res_asg_id = ra.resource_assignment_id
1441 AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
1442 AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id
1443 AND tmp_ra.transaction_source_code = 'FINANCIAL_PLAN' /* Added for Bug 4369741 */
1444 and NVL(ra.task_id,0) = ta.task_id (+) /* Added for ER 4376722 */
1445 --and ta.project_id = P_PROJECT_ID /* Added for ER 4376722 */
1446 AND ra.budget_version_id = P_BUDGET_VERSION_ID /* Added for Bug 4369741 Perf */
1447 AND ra.project_id = P_PROJECT_ID /* Added for Bug 4369741 Perf */
1448 AND tmp_ra.resource_assignment_id = tmp.resource_assignment_id /* Added for Bug 4571025 */
1449 GROUP BY tmp.RESOURCE_ASSIGNMENT_ID,
1450 tmp.TARGET_RES_ASG_ID,
1451 tmp.ETC_CURRENCY_CODE,
1452 ra.rate_based_flag,
1453 ra.resource_list_member_id,
1454 ra.task_id,
1455 ra.unit_of_measure,
1456 tmp_ra.etc_method_code,
1457 NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1458 tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1459 UNION ALL
1460 SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1461 INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1462 sum(1*null),
1463 tmp.TARGET_RES_ASG_ID,
1464 tmp.ETC_CURRENCY_CODE,
1465 ra.rate_based_flag,
1466 ra.resource_list_member_id,
1467 ra.task_id,
1468 ra.unit_of_measure,
1469 null, --ra.etc_method_code,
1470 SUM(tmp.ETC_PLAN_QUANTITY),
1471 SUM(tmp.ETC_TXN_RAW_COST),
1472 SUM(tmp.ETC_TXN_BURDENED_COST),
1473 SUM(tmp.ETC_TXN_REVENUE),
1474 NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1475 tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1476 FROM PA_FP_CALC_AMT_TMP1 tmp_ra, /* Added for Bug 4369741 */
1477 PA_FP_CALC_AMT_TMP2 tmp,
1478 PA_RESOURCE_ASSIGNMENTS ra,
1479 pa_tasks ta /* Added for ER 4376722 */
1480 WHERE tmp.target_res_asg_id = ra.resource_assignment_id
1481 AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
1482 and NVL(ra.task_id,0) = ta.task_id (+) /* Added for ER 4376722 */
1483 --and ta.project_id = P_PROJECT_ID /* Added for ER 4376722 */
1484 AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id /* Added for Bug 4369741 */
1485 AND tmp_ra.transaction_source_code <> 'OPEN_COMMITMENTS' /* Added for Bug 4369741 */
1486 AND tmp_ra.transaction_source_code <> 'FINANCIAL_PLAN' /* Added for Bug 4369741 */
1487 AND ra.budget_version_id = P_BUDGET_VERSION_ID /* Added for Bug 4369741 Perf */
1488 AND ra.project_id = P_PROJECT_ID /* Added for Bug 4369741 Perf */
1489 AND tmp_ra.resource_assignment_id = tmp.resource_assignment_id /* Added for Bug 4571025 */
1490 GROUP BY
1491 tmp.TARGET_RES_ASG_ID,
1492 tmp.ETC_CURRENCY_CODE,
1493 ra.rate_based_flag,
1494 ra.resource_list_member_id,
1495 ra.task_id,
1496 ra.unit_of_measure,
1497 null, --ra.etc_method_code,
1498 NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1499 tmp_ra.transaction_source_code; /* Added for Bug 4369741 */
1500
1501 /* Bug 4654157 and 4670253: Variables for enforcing positive total plan quantity */
1502 l_index NUMBER; -- index for _tmp tables
1503 l_total_plan_qty NUMBER;
1504 l_cal_rcost_rate_ovrd_tab_tmp SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1505 l_cal_bcost_rate_ovrd_tab_tmp SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1506 l_cal_bill_rate_ovrd_tab_tmp SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1507
1508 -- IPM: New Entity ER ------------------------------------------
1509 -- Stores ids of resources with Financial Plan as the ETC generation source
1510 l_fp_ra_id_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1511 -- Stores ids of resources with Workplan as the ETC generation source
1512 l_wp_ra_id_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1513 -- Stores ids of non-billable resources with Financial Plan as the ETC generation source
1514 l_non_billable_fp_ra_id_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
1515
1516 -- Defining a table of tables to process tables in a loop.
1517 TYPE SystemPaNumTblTypeTabType
1518 IS TABLE OF SYSTEM.pa_num_tbl_type INDEX BY BINARY_INTEGER;
1519
1520 l_ra_id_tab_table SystemPaNumTblTypeTabType;
1521 l_planning_options_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
1522 l_src_version_id_tab PA_PLSQL_DATATYPES.NumTabTyp;
1523
1524 /***
1525 * Added in IPM
1526 * Variable : L_REMOVE_RECORD_FLAG_TAB
1527 * Background : There are a growing number of cases where it is
1528 * necessary to skip certain records in further
1529 * processing. The strategy up till now has been
1530 * to copy non-skipped records into a set of temporary
1531 * pl/sql tables and then copy back to the original
1532 * tables. This table is being introduced to reduce
1533 * the number of times tables are copied back and forth.
1534 * Usage : - Records in this table should be in a 1-to-1 mapping
1535 * with planning txns given by the l_cal_ra_id_tab and
1536 * l_cal_txn_curr_code_tab tables.
1537 * - By default, initialize all records in this table to 'N'.
1538 * - Any time it becomes necessary for a planning txn
1539 * to be skipped in further processing, the corresponding
1540 * record in this table should be set to 'Y'.
1541 * - Downstream generation code should check this table
1542 * at the beginning of logical processing blocks.
1543 *
1544 * Variable : L_REMOVE_RECORDS_FLAG
1545 * Usage : - By default, initialize this flag to 'N'.
1546 * - Set this flag to 'Y' if any record in the associated
1547 * l_remove_record_flag_tab table is set to 'Y'.
1548 * - Before the Calculate or Maintain_Actual_Amt_Ra APIs
1549 * are called, if this flag is 'Y', then records with
1550 * l_remove_record_flag_tab(i) = 'Y' should be filtered
1551 * from the main pl/sql tables.
1552 *
1553 * Variable : L_REV_ONLY_SRC_TXN_FLAG_TAB
1554 * Background : As of IPM, it is possible to plan for just revenue
1555 * amounts (without cost amounts) in a Cost and Revenue
1556 * Together version. Pre-IPM, quantity was always set to
1557 * raw cost for non-rate-based transactions. However, in
1558 * the revenue only case, we store quantity as revenue.
1559 * In order to handle this correctly, it is necessary to
1560 * know if ETC revenue exists without ETC raw cost. This
1561 * latter piece of information is initially available
1562 * after querying for the ETC numbers. However, it may
1563 * be lost once the generation logic begins processing
1564 * and manipulating the amounts. Hence, this table is being
1565 * introduced to track which transactions have only ETC
1566 * revenue amounts.
1567 * Usage : - Records in this table should be in a 1-to-1 mapping
1568 * with planning txns given by the l_cal_ra_id_tab and
1569 * l_cal_txn_curr_code_tab tables.
1570 * - By default, initialize all records in this table to 'N'.
1571 * - Set records whose corresponding planning txns have only
1572 * ETC revenue amounts (i.e. Null ETC raw cost) to 'Y'.
1573 */
1574
1575 -- Added in IPM to track if a record in the existing set of
1576 -- pl/sql tables needs to be removed.
1577 l_remove_record_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
1578 l_remove_records_flag VARCHAR2(1);
1579
1580 -- Added in IPM to track if only ETC revenue is available.
1581 -- Note that this table should be initialized after ETC is
1582 -- fetched but before assignments to ETC raw cost begin.
1583 l_rev_only_src_txn_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
1584
1585 /* This user-defined exception is used to skip processing of
1586 * a planning transaction within a loop */
1587 continue_loop EXCEPTION;
1588
1589 l_source_version_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1590 l_target_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE;
1591
1592 BEGIN
1593 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1594 X_MSG_COUNT := 0;
1595 IF p_pa_debug_mode = 'Y' THEN
1596 pa_debug.set_curr_function( p_function => 'GEN_FCST_TASK_LEVEL_AMT',
1597 p_debug_mode => p_pa_debug_mode);
1598 END IF;
1599 l_latest_published_fwbs_id := PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_FIN_STRUC_VER_ID(P_PROJECT_ID);
1600 l_proj_struc_sharing_code := NVL(PA_PROJECT_STRUCTURE_UTILS.
1601 get_Structure_sharing_code(P_PROJECT_ID),'SHARE_FULL');
1602 /*IF p_pa_debug_mode = 'Y' THEN
1603 pa_fp_gen_amount_utils.fp_debug
1604 (p_msg => 'Value of l_latest_published_fwbs_id: '||l_latest_published_fwbs_id,
1605 p_module_name => l_module_name,
1606 p_log_level => 5);
1607 END IF;*/
1608 /*IF p_pa_debug_mode = 'Y' THEN
1609 pa_fp_gen_amount_utils.fp_debug
1610 (p_msg => 'Value of l_proj_struc_sharing_code: '||l_proj_struc_sharing_code,
1611 p_module_name => l_module_name,
1612 p_log_level => 5);
1613 END IF;*/
1614 /*IF p_pa_debug_mode = 'Y' THEN
1615 pa_fp_gen_amount_utils.fp_debug
1616 (p_msg => 'Value of p_wp_structure_version_id: '||p_wp_structure_version_id,
1617 p_module_name => l_module_name,
1618 p_log_level => 5);
1619 END IF;*/
1620
1621 IF p_fp_cols_rec.x_version_type = 'REVENUE' THEN
1622 l_fcst_gen_src_code := p_fp_cols_rec.x_gen_etc_src_code;
1623 END IF;
1624
1625 IF p_fp_cols_rec.x_version_type = 'REVENUE' AND
1626 l_fcst_gen_src_code IN ('NONE','AVERAGE_ACTUALS') THEN
1627
1628 IF p_pa_debug_mode = 'Y' THEN
1629 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1630 P_CALLED_MODE => P_CALLED_MODE,
1631 P_MSG => 'Before calling pa_fp_copy_actuals_pub.copy_actuals',
1632 P_MODULE_NAME => l_module_name);
1633 END IF;
1634 PA_FP_COPY_ACTUALS_PUB.COPY_ACTUALS
1635 (P_PROJECT_ID => P_PROJECT_ID,
1636 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
1637 P_FP_COLS_REC => P_fp_cols_rec,
1638 P_END_DATE => P_ACTUALS_THRU_DATE,
1639 P_INIT_MSG_FLAG => 'N',
1640 P_COMMIT_FLAG => 'N',
1641 X_RETURN_STATUS => X_RETURN_STATUS,
1642 X_MSG_COUNT => X_MSG_COUNT,
1643 X_MSG_DATA => X_MSG_DATA);
1644 IF p_pa_debug_mode = 'Y' THEN
1645 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1646 P_CALLED_MODE => P_CALLED_MODE,
1647 P_MSG => 'After calling pa_fp_copy_actuals_pub.copy_actuals:'
1648 ||x_return_status,
1649 P_MODULE_NAME => l_module_name);
1650 END IF;
1651 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1652 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1653 END IF;
1654
1655 IF l_fcst_gen_src_code = 'NONE' THEN
1656 IF p_pa_debug_mode = 'Y' THEN
1657 PA_DEBUG.Reset_Curr_Function;
1658 END IF;
1659 RETURN;
1660 END IF;
1661
1662 IF l_fcst_gen_src_code = 'AVERAGE_ACTUALS' THEN
1663 IF p_pa_debug_mode = 'Y' THEN
1664 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1665 P_CALLED_MODE => P_CALLED_MODE,
1666 P_MSG => 'Before calling PA_FP_GEN_FCST_AMT_PUB.'||
1667 'GEN_AVERAGE_OF_ACTUALS_WRP for Revenue only version',
1668 P_MODULE_NAME => l_module_name);
1669 END IF;
1670
1671 /* hr_utility.trace('Values passed to call GEN_AVERAGE_OF_ACTUALS_WRP api');
1672 hr_utility.trace('P_BUDGET_VERSION_ID: '||P_BUDGET_VERSION_ID);
1673 hr_utility.trace('l_curr_task_id: '||l_curr_task_id);
1674 hr_utility.trace('P_ACTUALS_THRU_DATE: '||P_ACTUALS_THRU_DATE);
1675 hr_utility.trace('P_ACTUALS_FROM_PERIOD: '||P_ACTUALS_FROM_PERIOD);
1676 hr_utility.trace('P_ACTUALS_TO_PERIOD: '||P_ACTUALS_TO_PERIOD);
1677 hr_utility.trace('P_ETC_FROM_PERIOD: '||P_ETC_FROM_PERIOD);
1678 hr_utility.trace('P_ETC_TO_PERIOD: '||P_ETC_TO_PERIOD); */
1679
1680 /* When the task id is passed as NULL, the wrapper API generates
1681 the ETC numbers for all the target version planning resources
1682 based on the average value of the actual txn data. */
1683
1684 PA_FP_GEN_FCST_AMT_PUB1.GEN_AVERAGE_OF_ACTUALS_WRP
1685 (P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
1686 P_TASK_ID => NULL,
1687 P_ACTUALS_THRU_DATE => P_ACTUALS_THRU_DATE,
1688 P_FP_COLS_REC => P_FP_COLS_REC,
1689 P_ACTUALS_FROM_PERIOD => P_ACTUALS_FROM_PERIOD,
1690 P_ACTUALS_TO_PERIOD => P_ACTUALS_TO_PERIOD,
1691 P_ETC_FROM_PERIOD => P_ETC_FROM_PERIOD,
1692 P_ETC_TO_PERIOD => P_ETC_TO_PERIOD,
1693 X_RETURN_STATUS => X_RETURN_STATUS,
1694 X_MSG_COUNT => X_MSG_COUNT,
1695 X_MSG_DATA => X_MSG_DATA );
1696 -- hr_utility.trace('Return status after calling GEN_AVERAGE_OF_ACTUALS_WRP: '
1697 -- ||x_return_status);
1698 IF p_pa_debug_mode = 'Y' THEN
1699 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1700 P_CALLED_MODE => P_CALLED_MODE,
1701 P_MSG => 'After calling PA_FP_GEN_FCST_AMT_PUB.'||
1702 'GEN_AVERAGE_OF_ACTUALS_WRP: '||x_return_status,
1703 P_MODULE_NAME => l_module_name);
1704 END IF;
1705
1706 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1707 IF p_pa_debug_mode = 'Y' THEN
1708 PA_DEBUG.Reset_Curr_Function;
1709 END IF;
1710 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1711 END IF;
1712
1713 IF p_pa_debug_mode = 'Y' THEN
1714 PA_DEBUG.Reset_Curr_Function;
1715 END IF;
1716
1717 RETURN;
1718 END IF; -- for average actual check
1719 /* the processing should continue when the target plan version type is
1720 REVENUE and the ETC Revenue generation source is either 'FINANCIAL_PLAN'
1721 or 'WORKPLAN_RESOURCES'. */
1722
1723 END IF; -- for revenue check
1724
1725 IF P_WP_STRUCTURE_VERSION_ID IS NOT NULL THEN
1726 l_wp_version_id := Pa_Fp_wp_gen_amt_utils.get_wp_version_id
1727 ( p_project_id => P_PROJECT_ID,
1728 p_plan_type_id => -1,
1729 p_proj_str_ver_id => P_WP_STRUCTURE_VERSION_ID );
1730
1731 IF P_PA_DEBUG_MODE = 'Y' THEN
1732 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1733 P_CALLED_MODE => P_CALLED_MODE,
1734 P_MSG => 'Before calling PA_FP_GEN_AMOUNT_UTILS.'||
1735 'GET_PLAN_VERSION_DTL',
1736 P_MODULE_NAME => l_module_name);
1737 END IF;
1738 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS(
1739 P_PROJECT_ID => P_PROJECT_ID,
1740 P_BUDGET_VERSION_ID => l_wp_version_id,
1741 X_FP_COLS_REC => l_fp_cols_rec_wp,
1742 X_RETURN_STATUS => x_return_status,
1743 X_MSG_COUNT => x_msg_count,
1744 X_MSG_DATA => x_msg_data);
1745 IF P_PA_DEBUG_MODE = 'Y' THEN
1746 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1747 P_CALLED_MODE => P_CALLED_MODE,
1748 P_MSG => 'After calling PA_FP_GEN_AMOUNT_UTILS.'||
1749 'GET_PLAN_VERSION_DTL:'||x_return_status,
1750 P_MODULE_NAME => l_module_name);
1751 END IF;
1752 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1753 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1754 END IF;
1755 END IF;
1756
1757 /*IF p_pa_debug_mode = 'Y' THEN
1758 pa_fp_gen_amount_utils.fp_debug
1759 (p_msg => 'Value of p_etc_fp_plan_version_id: '||p_etc_fp_plan_version_id,
1760 p_module_name => l_module_name,
1761 p_log_level => 5);
1762 END IF;*/
1763
1764 IF P_ETC_FP_PLAN_VERSION_ID IS NOT NULL THEN
1765 IF P_PA_DEBUG_MODE = 'Y' THEN
1766 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1767 P_CALLED_MODE => P_CALLED_MODE,
1768 P_MSG => 'Before calling PA_FP_GEN_AMOUNT_UTILS.'||
1769 'GET_PLAN_VERSION_DTLS',
1770 P_MODULE_NAME => l_module_name);
1771 END IF;
1772 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS(
1773 P_PROJECT_ID => P_PROJECT_ID,
1774 P_BUDGET_VERSION_ID => P_ETC_FP_PLAN_VERSION_ID,
1775 X_FP_COLS_REC => l_fp_cols_rec_fp,
1776 X_RETURN_STATUS => x_return_status,
1777 X_MSG_COUNT => x_msg_count,
1778 X_MSG_DATA => x_msg_data);
1779 IF P_PA_DEBUG_MODE = 'Y' THEN
1780 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1781 P_CALLED_MODE => P_CALLED_MODE,
1782 P_MSG => 'After calling PA_FP_GEN_AMOUNT_UTILS.'||
1783 'GET_PLAN_VERSION_DTLS: '||x_return_status,
1784 P_MODULE_NAME => l_module_name);
1785 END IF;
1786 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1787 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1788 END IF;
1789 END IF;
1790
1791 l_fp_cols_rec_target := P_FP_COLS_REC;
1792 l_calendar_type := l_fp_cols_rec_target.X_TIME_PHASED_CODE;
1793
1794 /**l_record_type: XXXX
1795 *1st X: 'Y',data will be returned in periods;
1796 * 'N',ITD amounts will be returned;
1797 *2nd X: 'Y',data will be returned by planning resources at
1798 * entered level(periodic/total);
1799 *3rd X: 'Y',data is returned by tasks;
1800 *4th X: 'N',amt will be gotten at entered level, no rollup is done.**/
1801 IF (l_calendar_type = 'G' OR l_calendar_type = 'P') THEN
1802 l_record_type := 'Y';
1803 ELSE
1804 l_record_type := 'N';
1805 END IF;
1806 l_record_type := l_record_type||'Y'||'Y'||'N';
1807
1808 --*****Populate pji tmp1,fcst tmp1 tables from workplan version******--
1809 -- hr_utility.trace('P_WP_STRUCTURE_VERSION_ID: '||P_WP_STRUCTURE_VERSION_ID);
1810 IF P_WP_STRUCTURE_VERSION_ID IS NOT NULL THEN
1811 l_resource_list_id := l_fp_cols_rec_wp.X_RESOURCE_LIST_ID;
1812 l_struct_ver_id := l_fp_cols_rec_wp.X_PROJECT_STRUCTURE_VERSION_ID;
1813
1814 /*IF p_pa_debug_mode = 'Y' THEN
1815 pa_fp_gen_amount_utils.fp_debug
1816 (p_msg => 'Value of l_resource_list_id: '||l_resource_list_id,
1817 p_module_name => l_module_name,
1818 p_log_level => 5);
1819 END IF;*/
1820 /*IF p_pa_debug_mode = 'Y' THEN
1821 pa_fp_gen_amount_utils.fp_debug
1822 (p_msg => 'Value of l_struct_ver_id: '||l_struct_ver_id,
1823 p_module_name => l_module_name,
1824 p_log_level => 5);
1825 END IF;*/
1826
1827 IF P_PA_DEBUG_MODE = 'Y' THEN
1828 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1829 P_CALLED_MODE => P_CALLED_MODE,
1830 P_MSG => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.'||
1831 'CALL_SUMM_POP_TMPS',
1832 P_MODULE_NAME => l_module_name);
1833 END IF;
1834
1835 /* hr_utility.trace('1.l_calendar_type: '||l_calendar_type);
1836 hr_utility.trace('1.l_record_type: '||l_record_type);
1837 hr_utility.trace('1.l_resource_list_id: '||l_resource_list_id);
1838 hr_utility.trace('1.l_struct_ver_id: '||l_struct_ver_id);
1839 hr_utility.trace('1.P_ACTUALS_THRU_DATE: '||P_ACTUALS_THRU_DATE); */
1840
1841 PA_FP_GEN_FCST_AMT_PUB1.CALL_SUMM_POP_TMPS(
1842 P_PROJECT_ID => P_PROJECT_ID,
1843 P_CALENDAR_TYPE => l_calendar_type,
1844 P_RECORD_TYPE => l_record_type,
1845 P_RESOURCE_LIST_ID => l_resource_list_id,
1846 P_STRUCT_VER_ID => l_struct_ver_id,
1847 P_ACTUALS_THRU_DATE => P_ACTUALS_THRU_DATE,
1848 P_DATA_TYPE_CODE => 'ETC_WP',
1849 X_RETURN_STATUS => x_return_status,
1850 X_MSG_COUNT => x_msg_count,
1851 X_MSG_DATA => x_msg_data );
1852 -- hr_utility.trace('1.Status after calling call_summ_pop_tmps api: '||x_return_status);
1853 IF P_PA_DEBUG_MODE = 'Y' THEN
1854 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1855 P_CALLED_MODE => P_CALLED_MODE,
1856 P_MSG => 'After calling PA_FP_GEN_FCST_AMT_PUB1.'||
1857 'CALL_SUMM_POP_TMPS:'||x_return_status,
1858 P_MODULE_NAME => l_module_name);
1859 END IF;
1860 --dbms_output.put_line('After calling pji api: '||x_return_status);
1861 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1862 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1863 END IF;
1864 END IF; -- for wp_structure_version_id check
1865
1866 --*****Populate pji tmp1,fcst tmp1 tables from ETC financial version******--
1867 -- hr_utility.trace('P_ETC_FP_PLAN_VERSION_ID: '||P_ETC_FP_PLAN_VERSION_ID);
1868 IF P_ETC_FP_PLAN_VERSION_ID IS NOT NULL THEN
1869 l_resource_list_id := l_fp_cols_rec_fp.X_RESOURCE_LIST_ID;
1870 l_struct_ver_id := l_fp_cols_rec_fp.X_PROJECT_STRUCTURE_VERSION_ID;
1871
1872 /*IF p_pa_debug_mode = 'Y' THEN
1873 pa_fp_gen_amount_utils.fp_debug
1874 (p_msg => 'Value of l_resource_list_id: '||l_resource_list_id,
1875 p_module_name => l_module_name,
1876 p_log_level => 5);
1877 END IF;*/
1878 /*IF p_pa_debug_mode = 'Y' THEN
1879 pa_fp_gen_amount_utils.fp_debug
1880 (p_msg => 'Value of l_struct_ver_id: '||l_struct_ver_id,
1881 p_module_name => l_module_name,
1882 p_log_level => 5);
1883 END IF;*/
1884
1885 IF P_PA_DEBUG_MODE = 'Y' THEN
1886 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1887 P_CALLED_MODE => P_CALLED_MODE,
1888 P_MSG => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.'||
1889 'CALL_SUMM_POP_TMPS',
1890 P_MODULE_NAME => l_module_name);
1891 END IF;
1892
1893 /* hr_utility.trace('2.l_calendar_type: '||l_calendar_type);
1894 hr_utility.trace('2.l_record_type: '||l_record_type);
1895 hr_utility.trace('2.l_resource_list_id: '||l_resource_list_id);
1896 hr_utility.trace('2.l_struct_ver_id: '||l_struct_ver_id);
1897 hr_utility.trace('2.P_ACTUALS_THRU_DATE: '||P_ACTUALS_THRU_DATE); */
1898
1899 PA_FP_GEN_FCST_AMT_PUB1.CALL_SUMM_POP_TMPS(
1900 P_PROJECT_ID => P_PROJECT_ID,
1901 P_CALENDAR_TYPE => l_calendar_type,
1902 P_RECORD_TYPE => l_record_type,
1903 P_RESOURCE_LIST_ID => l_resource_list_id,
1904 P_STRUCT_VER_ID => l_struct_ver_id,
1905 P_ACTUALS_THRU_DATE => P_ACTUALS_THRU_DATE,
1906 P_DATA_TYPE_CODE => 'ETC_FP',
1907 X_RETURN_STATUS => x_return_status,
1908 X_MSG_COUNT => x_msg_count,
1909 X_MSG_DATA => x_msg_data );
1910 -- hr_utility.trace('2.Status after calling call_summ_pop_tmps api: '||x_return_status);
1911 IF P_PA_DEBUG_MODE = 'Y' THEN
1912 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1913 P_CALLED_MODE => P_CALLED_MODE,
1914 P_MSG => 'After calling PA_FP_GEN_FCST_AMT_PUB1.'||
1915 'CALL_SUMM_POP_TMPS:'||x_return_status,
1916 P_MODULE_NAME => l_module_name);
1917 END IF;
1918 --dbms_output.put_line('After calling pji api: '||x_return_status);
1919 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1920 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1921 END IF;
1922 END IF; -- for P_ETC_FP_PLAN_VERSION_ID check
1923
1924 --*****Populate pji tmp1,fcst tmp1 tables from target financial version******--
1925 l_resource_list_id := l_fp_cols_rec_target.X_RESOURCE_LIST_ID;
1926 l_struct_ver_id := l_fp_cols_rec_target.X_PROJECT_STRUCTURE_VERSION_ID;
1927 /*IF p_pa_debug_mode = 'Y' THEN
1928 pa_fp_gen_amount_utils.fp_debug
1929 (p_msg => 'Value of l_resource_list_id: '||l_resource_list_id,
1930 p_module_name => l_module_name,
1931 p_log_level => 5);
1932 END IF;
1933 IF p_pa_debug_mode = 'Y' THEN
1934 pa_fp_gen_amount_utils.fp_debug
1935 (p_msg => 'Value of l_struct_ver_id: '||l_struct_ver_id,
1936 p_module_name => l_module_name,
1937 p_log_level => 5);
1938 END IF;*/
1939 IF P_PA_DEBUG_MODE = 'Y' THEN
1940 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1941 P_CALLED_MODE => P_CALLED_MODE,
1942 P_MSG => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.'||
1943 'CALL_SUMM_POP_TMPS',
1944 P_MODULE_NAME => l_module_name);
1945 END IF;
1946 /* hr_utility.trace('3.l_calendar_type: '||l_calendar_type);
1947 hr_utility.trace('3.l_record_type: '||l_record_type);
1948 hr_utility.trace('3.l_resource_list_id: '||l_resource_list_id);
1949 hr_utility.trace('3.l_struct_ver_id: '||l_struct_ver_id);
1950 hr_utility.trace('3.P_ACTUALS_THRU_DATE: '||P_ACTUALS_THRU_DATE); */
1951 PA_FP_GEN_FCST_AMT_PUB1.CALL_SUMM_POP_TMPS(
1952 P_PROJECT_ID => P_PROJECT_ID,
1953 P_CALENDAR_TYPE => l_calendar_type,
1954 P_RECORD_TYPE => l_record_type,
1955 P_RESOURCE_LIST_ID => l_resource_list_id,
1956 P_STRUCT_VER_ID => l_struct_ver_id,
1957 P_ACTUALS_THRU_DATE => P_ACTUALS_THRU_DATE,
1958 P_DATA_TYPE_CODE => 'TARGET_FP',
1959 X_RETURN_STATUS => x_return_status,
1960 X_MSG_COUNT => x_msg_count,
1961 X_MSG_DATA => x_msg_data );
1962 -- hr_utility.trace('3.Status after calling call_summ_pop_tmps api: '||x_return_status);
1963 IF P_PA_DEBUG_MODE = 'Y' THEN
1964 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1965 P_CALLED_MODE => P_CALLED_MODE,
1966 P_MSG => 'After calling PA_FP_GEN_FCST_AMT_PUB1.'||
1967 'CALL_SUMM_POP_TMPS:'||x_return_status,
1968 P_MODULE_NAME => l_module_name);
1969 END IF;
1970 --dbms_output.put_line(' --After calling pji get_summarized api: '||x_return_status);
1971 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1972 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1973 END IF;
1974
1975 /**traverse each node in latest published finanical WBS,
1976 *check for each task's etc source code, do corresponding
1977 *operations.**/
1978
1979 /* 10/11/2004 ETC Enhancements:
1980 For Top Task planning, copy ETC method from Top Task to all of its children
1981 */
1982 -- hr_utility.trace('FIN_PLAN_LEVEL_CODE: '||l_fp_cols_rec_target.X_FIN_PLAN_LEVEL_CODE);
1983 /* Planning level : top task */
1984 if l_fp_cols_rec_target.X_FIN_PLAN_LEVEL_CODE = 'T' then
1985 OPEN traverse_top_tasks_cur(l_fcst_gen_src_code);
1986 FETCH traverse_top_tasks_cur
1987 BULK COLLECT
1988 INTO l_top_task_id_tab,
1989 l_top_gen_etc_src_code_tab;
1990 CLOSE traverse_top_tasks_cur;
1991 /* hr_utility.trace('l_top_task_id_tab,count: '||l_top_task_id_tab.count);
1992 hr_utility.trace('l_top_gen_etc_src_code_tab.count: '||l_top_gen_etc_src_code_tab.count); */
1993 l_task_index := 0;
1994 FOR i in 1..l_top_task_id_tab.last LOOP
1995 /* Add the Top Task to pl/sql tables */
1996 l_task_id_tab(l_task_index) := l_top_task_id_tab(i);
1997 l_gen_etc_source_code_tab(l_task_index) := l_top_gen_etc_src_code_tab(i);
1998 l_task_index := l_task_index + 1;
1999 l_temp_top_task_id := l_top_task_id_tab(i);
2000 l_child_task_id_tab.DELETE;
2001
2002 OPEN traverse_child_tasks_cur;
2003 FETCH traverse_child_tasks_cur
2004 BULK COLLECT
2005 INTO l_child_task_id_tab;
2006 CLOSE traverse_child_tasks_cur;
2007
2008 -- hr_utility.trace('l_child_task_id_tab.count: '||l_child_task_id_tab.count);
2009 FOR j in 1..l_child_task_id_tab.count LOOP
2010 /* Add the Top Task's childen to pl/sql tables */
2011 l_task_id_tab(l_task_index) := l_child_task_id_tab(j);
2012 l_gen_etc_source_code_tab(l_task_index) := l_top_gen_etc_src_code_tab(i);
2013 l_task_index := l_task_index + 1;
2014 END LOOP;
2015 END LOOP;
2016 ELSIF l_fp_cols_rec_target.X_FIN_PLAN_LEVEL_CODE IN ('L','P') THEN
2017 /* Lowest task or Project */
2018 OPEN traverse_tasks_cur(l_fcst_gen_src_code);
2019 FETCH traverse_tasks_cur
2020 BULK COLLECT
2021 INTO l_task_id_tab,
2022 l_gen_etc_source_code_tab;
2023 CLOSE traverse_tasks_cur;
2024 END IF; -- for plan level check
2025
2026 IF P_PA_DEBUG_MODE = 'Y' THEN
2027 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2028 P_CALLED_MODE => P_CALLED_MODE,
2029 P_MSG => 'In traverse cursor, we have how many records?'||
2030 l_task_id_tab.count,
2031 P_MODULE_NAME => l_module_name);
2032 END IF;
2033 IF l_task_id_tab.count = 0 THEN
2034 IF p_pa_debug_mode = 'Y' THEN
2035 PA_DEBUG.Reset_Curr_Function;
2036 END IF;
2037 RETURN;
2038 END IF;
2039 /* hr_utility.trace('--Before traverse any task node');
2040 hr_utility.trace('--we have :'||l_task_id_tab.count||' task ids'); */
2041 FOR i IN l_task_id_tab.first..l_task_id_tab.last LOOP
2042 -- hr_utility.trace(i||'th task->');
2043
2044 l_curr_task_id := l_task_id_tab(i);
2045 l_curr_etc_source := l_gen_etc_source_code_tab(i);
2046
2047 /* hr_utility.trace('--task id is:'||l_task_id_tab(i));
2048 hr_utility.trace('--curr etc source is fin/wp/wkqty/avgact:'||
2049 l_gen_etc_source_code_tab(i)); */
2050 --dbms_output.put_line('--task id is:'||l_task_id_tab(i));
2051 --dbms_output.put_line('--curr etc source is fin/wp/wkqty/avgact:'||l_gen_etc_source_code_tab(i));
2052 /* IF p_pa_debug_mode = 'Y' THEN
2053 pa_fp_gen_amount_utils.fp_debug
2054 (p_msg => 'Value of l_curr_etc_source: '||l_curr_etc_source,
2055 p_module_name => l_module_name,
2056 p_log_level => 5);
2057 END IF;*/
2058 -- hr_utility.trace('l_curr_etc_source: '||l_curr_etc_source);
2059 IF l_curr_etc_source = 'AVERAGE_ACTUALS' THEN
2060 -- Bug 4114589: Processing of tasks with source of Average of Actuals
2061 -- has moved to after the resource mapping and Copy Actuals API call.
2062 l_avg_actuals_task_id_tab(l_avg_actuals_task_id_tab.count+1) := l_curr_task_id;
2063 ELSIF l_curr_etc_source = 'FINANCIAL_PLAN'
2064 OR l_curr_etc_source = 'WORKPLAN_RESOURCES' THEN
2065 l_proceed_flag := 'Y';
2066 IF l_curr_etc_source = 'WORKPLAN_RESOURCES' AND
2067 l_proj_struc_sharing_code = 'SPLIT_NO_MAPPING' THEN
2068 IF l_entered_flag = 'Y' THEN
2069 l_proceed_flag := 'N';
2070 ELSE
2071 l_proceed_flag := 'Y';
2072 l_entered_flag := 'Y';
2073 END IF;
2074 END IF;
2075 IF l_proceed_flag = 'Y' THEN
2076 IF l_curr_etc_source = 'FINANCIAL_PLAN' THEN
2077 l_calling_context := 'FINANCIAL_PLAN';
2078 ELSIF l_curr_etc_source = 'WORKPLAN_RESOURCES' THEN
2079 l_calling_context := 'WORK_PLAN';
2080 END IF;
2081
2082 /*IF p_pa_debug_mode = 'Y' THEN
2083 pa_fp_gen_amount_utils.fp_debug
2084 (p_msg => 'Value of l_calling_context: '||l_calling_context,
2085 p_module_name => l_module_name,
2086 p_log_level => 5);
2087 END IF;*/
2088
2089 /**Calling the total_plan_txn_amts api to get the total
2090 *transaction amts for a given task**/
2091 IF P_PA_DEBUG_MODE = 'Y' THEN
2092 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2093 P_CALLED_MODE => P_CALLED_MODE,
2094 P_MSG => 'Before calling pa_fp_gen_fcst_amt_pub.'||
2095 'get_total_plan_txn_amts',
2096 P_MODULE_NAME => l_module_name);
2097 END IF;
2098 --dbms_output.put_line('--Before GET_TOTAL--');
2099 --dbms_output.put_line('--P_PROJECT_ID:'||p_project_id);
2100 --dbms_output.put_line('--P_BUDGET_VERSION_ID:'||P_BUDGET_VERSION_ID);
2101 --dbms_output.put_line('--l_wp_version_id:'||l_wp_version_id);
2102 --dbms_output.put_line('--P_ETC_FP_PLAN_VERSION_ID:'||P_ETC_FP_PLAN_VERSION_ID);
2103 --dbms_output.put_line('--l_fp_cols_rec_wp:'||l_fp_cols_rec_wp.X_PROJ_FP_OPTIONS_ID);
2104 --dbms_output.put_line('--l_fp_cols_rec_fp:'||l_fp_cols_rec_fp.X_PROJ_FP_OPTIONS_ID);
2105 --dbms_output.put_line('--P_FP_COLS_REC:'||P_FP_COLS_REC.X_PROJ_FP_OPTIONS_ID);
2106 --dbms_output.put_line('--l_curr_task_id:'||l_curr_task_id);
2107 --dbms_output.put_line('--l_calling_context:'||l_calling_context);
2108
2109 BEGIN
2110 PA_FP_GEN_FCST_AMT_PVT.GET_TOTAL_PLAN_TXN_AMTS
2111 ( P_PROJECT_ID => P_PROJECT_ID,
2112 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
2113 P_BV_ID_ETC_WP => l_wp_version_id,
2114 P_BV_ID_ETC_FP => P_ETC_FP_PLAN_VERSION_ID,
2115 P_FP_COLS_REC_ETC_WP => l_fp_cols_rec_wp,
2116 P_FP_COLS_REC_ETC_FP => l_fp_cols_rec_fp,
2117 P_FP_COLS_REC => P_FP_COLS_REC,
2118 P_TASK_ID => l_curr_task_id,
2119 --P_RES_LIST_MEMBER_ID => NULL,
2120 --P_TXN_CURRENCY_CODE => NULL,
2121 P_LATEST_PUBLISH_FP_WBS_ID => l_latest_published_fwbs_id,
2122 P_CALLING_CONTEXT => l_calling_context,
2123 X_TXN_AMT_REC => l_txn_amt_rec,
2124 X_RETURN_STATUS => x_return_status,
2125 X_MSG_COUNT => x_msg_count,
2126 X_MSG_DATA => x_msg_data );
2127 EXCEPTION
2128 WHEN no_data_found THEN
2129 IF p_pa_debug_mode = 'Y' THEN
2130 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2131 P_CALLED_MODE => P_CALLED_MODE,
2132 P_MSG => 'PA_FP_GEN_FCST_AMT_PVT.GET_TOTAL_PLAN_TXN_AMTS '||
2133 'throws out no_data_found exception',
2134 P_MODULE_NAME => l_module_name);
2135 END IF;
2136 END;
2137 IF p_pa_debug_mode = 'Y' THEN
2138 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2139 P_CALLED_MODE => P_CALLED_MODE,
2140 P_MSG => 'After calling pa_fp_gen_fcst_amt_pub.'||
2141 'get_total_plan_txn_amts: '||x_return_status,
2142 P_MODULE_NAME => l_module_name);
2143 END IF;
2144 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2145 IF p_pa_debug_mode = 'Y' THEN
2146 PA_DEBUG.Reset_Curr_Function;
2147 END IF;
2148 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2149 END IF;
2150 END IF;
2151 ELSIF l_curr_etc_source = 'WORK_QUANTITY' THEN
2152 /*IF p_pa_debug_mode = 'Y' THEN
2153 pa_fp_gen_amount_utils.fp_debug
2154 (p_msg => 'Value of l_curr_etc_source: '||l_curr_etc_source,
2155 p_module_name => l_module_name,
2156 p_log_level => 5);
2157 END IF;
2158 IF p_pa_debug_mode = 'Y' THEN
2159 pa_fp_gen_amount_utils.fp_debug
2160 (p_msg => 'Value of l_work_qty_cnt: '||l_work_qty_cnt,
2161 p_module_name => l_module_name,
2162 p_log_level => 5);
2163 END IF;
2164 IF p_pa_debug_mode = 'Y' THEN
2165 pa_fp_gen_amount_utils.fp_debug
2166 (p_msg => 'Value of l_proj_struc_sharing_code: '||l_proj_struc_sharing_code,
2167 p_module_name => l_module_name,
2168 p_log_level => 5);
2169 END IF;*/
2170 IF l_work_qty_cnt = 0 THEN
2171 IF l_proj_struc_sharing_code = 'SPLIT_NO_MAPPING' THEN
2172 l_work_qty_cnt := 1;
2173 l_curr_task_id := NULL;
2174 END IF;
2175 IF P_PA_DEBUG_MODE = 'Y' THEN
2176 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2177 P_CALLED_MODE => P_CALLED_MODE,
2178 P_MSG => 'Before calling pa_fp_gen_fcst_amt_pub1.'||
2179 'GET_ETC_WORK_QTY_AMTS',
2180 P_MODULE_NAME => l_module_name);
2181 END IF;
2182 /*WORK_QTY_AMTS are generated at task level, so P_RESOURCE_ASSIGNMENT and
2183 P_RESOURCE_LIST_MEMBER_ID are not needed*/
2184 PA_FP_GEN_FCST_AMT_PUB1.GET_ETC_WORK_QTY_AMTS(
2185 P_PROJECT_ID => P_PROJECT_ID,
2186 P_PROJ_CURRENCY_CODE => P_FP_COLS_REC.X_PROJECT_CURRENCY_CODE,
2187 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
2188 P_TASK_ID => l_curr_task_id,
2189 P_TARGET_RES_LIST_ID => l_fp_cols_rec_target.X_RESOURCE_LIST_ID,
2190 P_ACTUALS_THRU_DATE => P_ACTUALS_THRU_DATE,
2191 P_FP_COLS_REC => P_FP_COLS_REC,
2192 P_WP_STRUCTURE_VERSION_ID => P_WP_STRUCTURE_VERSION_ID,
2193 X_RETURN_STATUS => x_return_status,
2194 X_MSG_COUNT => x_msg_count,
2195 X_MSG_DATA => x_msg_data);
2196 IF p_pa_debug_mode = 'Y' THEN
2197 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2198 P_CALLED_MODE => P_CALLED_MODE,
2199 P_MSG => 'After calling pa_fp_gen_fcst_amt_pub1.'||
2200 'GET_ETC_WORK_QTY_AMTS: '||x_return_status,
2201 P_MODULE_NAME => l_module_name);
2202 END IF;
2203 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2204 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2205 END IF;
2206 END IF;
2207 ELSIF l_curr_etc_source = 'NONE' THEN
2208 IF P_PA_DEBUG_MODE = 'Y' THEN
2209 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2210 P_CALLED_MODE => P_CALLED_MODE,
2211 P_MSG => 'Before calling pa_fp_gen_fcst_amt_pub1.none_etc_src',
2212 P_MODULE_NAME => l_module_name);
2213 END IF;
2214 PA_FP_GEN_FCST_AMT_PUB1.NONE_ETC_SRC(
2215 P_PROJECT_ID => P_PROJECT_ID,
2216 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
2217 P_RESOURCE_LIST_ID => l_fp_cols_rec_target.X_RESOURCE_LIST_ID,
2218 P_TASK_ID => l_curr_task_id,
2219 X_RETURN_STATUS => x_return_status,
2220 X_MSG_COUNT => x_msg_count,
2221 X_MSG_DATA => x_msg_data );
2222 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2223 IF p_pa_debug_mode = 'Y' THEN
2224 PA_DEBUG.Reset_Curr_Function;
2225 END IF;
2226 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2227 END IF;
2228 END IF;
2229 END LOOP;
2230
2231 --Bug 6407972
2232 --Bug 5929269. If the plan version into whihc amounts are being forecasted has
2233 --Top Task Planning level then the task ids in PA_FP_FCST_GEN_TMP1 should be updated
2234 --to the top task ids since the ETC should now be calculated for top tasks
2235 IF p_fp_cols_rec.x_fin_plan_level_code = 'T' THEN
2236
2237 UPDATE pa_fp_fcst_gen_tmp1 tmp
2238 SET tmp.project_element_id = (SELECT pt.top_task_id
2239 FROM pa_tasks pt
2240 WHERE tmp.project_element_id = pt.task_id)
2241 WHERE tmp.data_type_code = 'ETC_FP'
2242 AND tmp.project_element_id
2243 IN
2244 (SELECT pt.task_id
2245 FROM pa_tasks pt
2246 WHERE pt.top_task_id IN (SELECT tmp1.task_id
2247 FROM pa_fp_calc_amt_tmp1 tmp1
2248 WHERE tmp1.budget_version_id =
2249 p_etc_fp_plan_version_id)
2250 AND pt.task_id NOT IN (SELECT tmp1.task_id
2251 FROM pa_fp_calc_amt_tmp1 tmp1
2252 WHERE tmp1.budget_version_id =
2253 p_etc_fp_plan_version_id)
2254 AND pt.project_id=p_project_id
2255 AND pt.task_id<>pt.top_task_id
2256 );
2257
2258 END IF;
2259
2260 /* Call resource mapping API on source transaction resources.
2261 The planning resources from the generation source must be mapped to the
2262 target forecast verion resource list before calculating the ETC numbers.
2263 The ETC calculation is based on the rate based flag of the target
2264 planning resource. */
2265
2266 IF p_pa_debug_mode = 'Y' THEN
2267 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2268 P_CALLED_MODE => P_CALLED_MODE,
2269 P_MSG => 'Before calling pa_fp_gen_fcst_rmap_pkg.fcst_src_txns_rmap',
2270 P_MODULE_NAME => l_module_name);
2271 END IF;
2272 -- hr_utility.trace('before fcst src txns rmap');
2273 PA_FP_GEN_FCST_RMAP_PKG.FCST_SRC_TXNS_RMAP
2274 (P_PROJECT_ID => P_PROJECT_ID,
2275 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
2276 P_FP_COLS_REC => P_fp_cols_rec,
2277 X_RETURN_STATUS => X_RETURN_STATUS,
2278 X_MSG_COUNT => X_MSG_COUNT,
2279 X_MSG_DATA => X_MSG_DATA);
2280 -- hr_utility.trace('after fcst src txns rmap');
2281 IF p_pa_debug_mode = 'Y' THEN
2282 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2283 P_CALLED_MODE => P_CALLED_MODE,
2284 P_MSG => 'After calling pa_fp_gen_fcst_rmap_pkg.fcst_src_txns_rmap'
2285 ||x_return_status,
2286 P_MODULE_NAME => l_module_name);
2287 END IF;
2288 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2289 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2290 END IF;
2291
2292 -- Bug 4114589: Moved from beginning of GENERATE_FCST_AMT_WRP to after
2293 -- resource mapping, which also calls CREATE_RES_ASG and UPDATE_RES_ASG
2294 -- via call to MAINTAIN_RES_ASG, so that planning dates from the source
2295 -- are honored when possible, since resources created by the Copy Actuals
2296 -- API use task/project-level default dates.
2297 IF p_pa_debug_mode = 'Y' THEN
2298 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2299 P_CALLED_MODE => P_CALLED_MODE,
2300 P_MSG => 'Before calling pa_fp_copy_actuals_pub.copy_actuals',
2301 P_MODULE_NAME => l_module_name);
2302 END IF;
2303 PA_FP_COPY_ACTUALS_PUB.COPY_ACTUALS
2304 (P_PROJECT_ID => P_PROJECT_ID,
2305 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
2306 P_FP_COLS_REC => P_fp_cols_rec,
2307 P_END_DATE => P_ACTUALS_THRU_DATE,
2308 P_INIT_MSG_FLAG => 'N',
2309 P_COMMIT_FLAG => 'N',
2310 X_RETURN_STATUS => X_RETURN_STATUS,
2311 X_MSG_COUNT => X_MSG_COUNT,
2312 X_MSG_DATA => X_MSG_DATA);
2313 IF p_pa_debug_mode = 'Y' THEN
2314 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2315 P_CALLED_MODE => P_CALLED_MODE,
2316 P_MSG => 'After calling pa_fp_copy_actuals_pub.copy_actuals:'
2317 ||x_return_status,
2318 P_MODULE_NAME => l_module_name);
2319 END IF;
2320 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2321 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2322 END IF;
2323
2324 -- Now that we have copied the actuals, we do the delayed processing
2325 -- for tasks with source of Average of Actuals.
2326 FOR i IN 1..l_avg_actuals_task_id_tab.count LOOP
2327 IF p_pa_debug_mode = 'Y' THEN
2328 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2329 P_CALLED_MODE => P_CALLED_MODE,
2330 P_MSG => 'Before calling PA_FP_GEN_FCST_AMT_PUB.'||
2331 'GEN_AVERAGE_OF_ACTUALS_WRP',
2332 P_MODULE_NAME => l_module_name);
2333 END IF;
2334 /* hr_utility.trace('Values passed to call GEN_AVERAGE_OF_ACTUALS_WRP api');
2335 hr_utility.trace('P_BUDGET_VERSION_ID: '||P_BUDGET_VERSION_ID);
2336 hr_utility.trace('l_curr_task_id: '||l_curr_task_id);
2337 hr_utility.trace('P_ACTUALS_THRU_DATE: '||P_ACTUALS_THRU_DATE);
2338 hr_utility.trace('P_ACTUALS_FROM_PERIOD: '||P_ACTUALS_FROM_PERIOD);
2339 hr_utility.trace('P_ACTUALS_TO_PERIOD: '||P_ACTUALS_TO_PERIOD);
2340 hr_utility.trace('P_ETC_FROM_PERIOD: '||P_ETC_FROM_PERIOD);
2341 hr_utility.trace('P_ETC_TO_PERIOD: '||P_ETC_TO_PERIOD); */
2342 PA_FP_GEN_FCST_AMT_PUB1.GEN_AVERAGE_OF_ACTUALS_WRP
2343 (P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
2344 P_TASK_ID => l_avg_actuals_task_id_tab(i),
2345 P_ACTUALS_THRU_DATE => P_ACTUALS_THRU_DATE,
2346 P_FP_COLS_REC => P_FP_COLS_REC,
2347 P_ACTUALS_FROM_PERIOD => P_ACTUALS_FROM_PERIOD,
2348 P_ACTUALS_TO_PERIOD => P_ACTUALS_TO_PERIOD,
2349 P_ETC_FROM_PERIOD => P_ETC_FROM_PERIOD,
2350 P_ETC_TO_PERIOD => P_ETC_TO_PERIOD,
2351 X_RETURN_STATUS => X_RETURN_STATUS,
2352 X_MSG_COUNT => X_MSG_COUNT,
2353 X_MSG_DATA => X_MSG_DATA );
2354 -- hr_utility.trace('Return status after calling GEN_AVERAGE_OF_ACTUALS_WRP: '
2355 -- ||x_return_status);
2356 IF p_pa_debug_mode = 'Y' THEN
2357 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2358 P_CALLED_MODE => P_CALLED_MODE,
2359 P_MSG => 'After calling PA_FP_GEN_FCST_AMT_PUB.'||
2360 'GEN_AVERAGE_OF_ACTUALS_WRP: '||x_return_status,
2361 P_MODULE_NAME => l_module_name);
2362 END IF;
2363 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2364 IF p_pa_debug_mode = 'Y' THEN
2365 PA_DEBUG.Reset_Curr_Function;
2366 END IF;
2367 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2368 END IF;
2369 END LOOP;
2370 -- Bug 4114589: End changes.
2371
2372 --dbms_output.put_line('--next, we will get all etc amts for each task');
2373 /***********************************************************
2374 *Above gets all the plan amounts for any specifc tasks
2375 *Below will get all the etc amounts for the tasks.
2376 ***********************************************************/
2377
2378 /**From latest approved version, by calling gen_map_bv_to_target_rl
2379 *we get PA_FP_CALC_AMT_TMP3 popuated, from it, we can get the
2380 *baselined cost**/
2381
2382 /* select count(*) into l_test from Pa_fp_CALC_AMT_TMP1;
2383 hr_utility.trace('fp calc amt tmp1 tab count '||l_test);
2384 select count(*) into l_test from Pa_fp_CALC_AMT_TMP2;
2385 hr_utility.trace('fp calc amt tmp2 tab count '||l_test); */
2386
2387 DELETE FROM PA_FP_CALC_AMT_TMP3;
2388 /* the following code is commented. We are not going to use the
2389 baselined budget cost for ETC gen method Plan to Complete. */
2390 /* IF P_PA_DEBUG_MODE = 'Y' THEN
2391 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2392 P_CALLED_MODE => P_CALLED_MODE,
2393 P_MSG => 'Before calling PA_FIN_PLAN_UTILS.'||
2394 'Get_Appr_Cost_Plan_Type_Info',
2395 P_MODULE_NAME => l_module_name);
2396 END IF;
2397 PA_FIN_PLAN_UTILS.Get_Appr_Cost_Plan_Type_Info(
2398 p_project_id => P_PROJECT_ID,
2399 x_plan_type_id => l_fin_plan_type_id,
2400 x_return_status => x_return_status,
2401 x_msg_count => x_msg_count,
2402 x_msg_data => x_msg_data);
2403
2404 --dbms_output.put_line('l_fin_plan_type_id is '||l_fin_plan_type_id);
2405 IF P_PA_DEBUG_MODE = 'Y' THEN
2406 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2407 P_CALLED_MODE => P_CALLED_MODE,
2408 P_MSG => 'After calling PA_FIN_PLAN_UTILS.'||
2409 'Get_Appr_Cost_Plan_Type_Info: '||x_return_status,
2410 P_MODULE_NAME => l_module_name);
2411 END IF;
2412 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2413 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2414 END IF;
2415 IF (l_fin_plan_type_id IS NULL) THEN
2416 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2417 p_msg_name => 'PA_FP_GENFCST_NO_COST_PTYPE');
2418 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2419 END IF;
2420
2421 SELECT DECODE( FIN_PLAN_PREFERENCE_CODE,'COST_ONLY', 'COST' ,
2422 'COST_AND_REV_SEP', 'COST',
2423 'COST_AND_REV_SAME', 'ALL') INTO l_version_type
2424 FROM pa_proj_fp_options
2425 WHERE fin_plan_type_id = l_fin_plan_type_id
2426 AND fin_plan_option_level_code = 'PLAN_TYPE'
2427 AND project_id = P_PROJECT_ID;
2428
2429 --dbms_output.put_line('--l_version_type: '||l_version_type);
2430 IF P_PA_DEBUG_MODE = 'Y' THEN
2431 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2432 P_CALLED_MODE => P_CALLED_MODE,
2433 P_MSG => 'Before calling PA_FP_GEN_AMOUNT_UTILS.'||
2434 'Get_Curr_Original_Version_Info',
2435 P_MODULE_NAME => l_module_name);
2436 END IF;
2437 PA_FP_GEN_AMOUNT_UTILS.Get_Curr_Original_Version_Info(
2438 p_project_id => P_PROJECT_ID,
2439 p_fin_plan_type_id => l_fin_plan_type_id,
2440 p_version_type => l_version_type,
2441 p_status_code => 'CURRENT_APPROVED',
2442 x_fp_options_id => l_approved_fp_options_id,
2443 x_fin_plan_version_id => l_approved_fp_version_id,
2444 x_return_status => x_return_status,
2445 x_msg_count => x_msg_count,
2446 x_msg_data => x_msg_data);
2447 --dbms_output.put_line('--!after PA_FP_GEN_AMOUNT_UTILS.Get_Curr_Original_Version_Info'||x_return_status);
2448 --dbms_output.put_line('--l_approved_fp_version_id is:'||l_approved_fp_version_id);
2449 IF P_PA_DEBUG_MODE = 'Y' THEN
2450 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2451 P_CALLED_MODE => P_CALLED_MODE,
2452 P_MSG => 'After calling PA_FP_GEN_AMOUNT_UTILS.'||
2453 'Get_Curr_Original_Version_Info: '||x_return_status,
2454 P_MODULE_NAME => l_module_name);
2455 END IF;
2456 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2457 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2458 END IF;
2459 IF (l_approved_fp_version_id IS NULL) THEN
2460 --dbms_output.put_line('--l_approved_fp_version_id is NULL');
2461 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2462 p_msg_name => 'PA_FP_GENFCST_NO_APPR_FPVER');
2463 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2464 END IF;
2465
2466 IF P_PA_DEBUG_MODE = 'Y' THEN
2467 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2468 P_CALLED_MODE => P_CALLED_MODE,
2469 P_MSG => 'Before calling PA_FP_GEN_AMOUNT_UTILS.'||
2470 'GET_PLAN_VERSION_DTLS',
2471 P_MODULE_NAME => l_module_name);
2472 END IF;
2473 --dbms_output.put_line('--before PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS');
2474 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS(
2475 P_PROJECT_ID => P_PROJECT_ID,
2476 P_BUDGET_VERSION_ID => l_approved_fp_version_id,
2477 X_FP_COLS_REC => l_fp_cols_rec_approved,
2478 X_RETURN_STATUS => x_return_status,
2479 X_MSG_COUNT => x_msg_count,
2480 X_MSG_DATA => x_msg_data);
2481 --dbms_output.put_line('--after PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS :'||x_return_status);
2482 IF P_PA_DEBUG_MODE = 'Y' THEN
2483 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2484 P_CALLED_MODE => P_CALLED_MODE,
2485 P_MSG => 'After calling PA_FP_GEN_AMOUNT_UTILS.'||
2486 'GET_PLAN_VERSION_DTLS: '||x_return_status,
2487 P_MODULE_NAME => l_module_name);
2488 END IF;
2489 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2490 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2491 END IF;
2492
2493 IF P_PA_DEBUG_MODE = 'Y' THEN
2494 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2495 P_CALLED_MODE => P_CALLED_MODE,
2496 P_MSG => 'Before calling PA_FP_MAP_BV_PUB.'||
2497 'GEN_MAP_BV_TO_TARGET_RL',
2498 P_MODULE_NAME => l_module_name);
2499 END IF;
2500 --dbms_output.put_line('--before MAP_BV_TO_TARGET_RL');
2501 --hr_utility.trace('bef call PA_FP_MAP_BV_PUB.GEN_MAP_BV_TO_TARGET_RL api');
2502 PA_FP_MAP_BV_PUB.GEN_MAP_BV_TO_TARGET_RL
2503 (P_SOURCE_BV_ID => l_approved_fp_version_id,
2504 P_TARGET_FP_COLS_REC => P_FP_COLS_REC,
2505 P_ETC_FP_COLS_REC => l_fp_cols_rec_wp,
2506 P_CB_FP_COLS_REC => l_fp_cols_rec_approved,
2507 X_RETURN_STATUS => x_return_status,
2508 X_MSG_COUNT => x_msg_count,
2509 X_MSG_DATA => x_msg_data);
2510 --dbms_output.put_line('--PA_FP_MAP_BV_PUB.GEN_MAP_BV_TO_TARGET_RL:' ||x_return_status);
2511 --hr_utility.trace('aft call GEN_MAP_BV_TO_TARGET_RL api:'||x_return_status);
2512 IF P_PA_DEBUG_MODE = 'Y' THEN
2513 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2514 P_CALLED_MODE => P_CALLED_MODE,
2515 P_MSG => 'After calling PA_FP_MAP_BV_PUB.'||
2516 'GEN_MAP_BV_TO_TARGET_RL: '||x_return_status,
2517 P_MODULE_NAME => l_module_name);
2518 END IF;
2519 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2520 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2521 END IF;
2522
2523 IF P_PA_DEBUG_MODE = 'Y' THEN
2524 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2525 P_CALLED_MODE => P_CALLED_MODE,
2526 P_MSG => 'Before calling PA_FP_MAP_BV_PUB.'||
2527 'GEN_MAP_BV_TO_TARGET_RL(2nd time)',
2528 P_MODULE_NAME => l_module_name);
2529 END IF;
2530 PA_FP_MAP_BV_PUB.GEN_MAP_BV_TO_TARGET_RL
2531 (P_SOURCE_BV_ID => l_approved_fp_version_id,
2532 P_TARGET_FP_COLS_REC => P_FP_COLS_REC,
2533 P_ETC_FP_COLS_REC => l_fp_cols_rec_fp,
2534 P_CB_FP_COLS_REC => l_fp_cols_rec_approved,
2535 X_RETURN_STATUS => x_return_status,
2536 X_MSG_COUNT => x_msg_count,
2537 X_MSG_DATA => x_msg_data);
2538 --tmp3's basedline value will be used in method api
2539 IF P_PA_DEBUG_MODE = 'Y' THEN
2540 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2541 P_CALLED_MODE => P_CALLED_MODE,
2542 P_MSG => 'After calling PA_FP_MAP_BV_PUB.'||
2543 'GEN_MAP_BV_TO_TARGET_RL:(2nd time) '||x_return_status,
2544 P_MODULE_NAME => l_module_name);
2545 END IF;
2546 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2547 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2548 END IF; */
2549 /*End baselined comments*/
2550
2551 /* For Forecast Generation of Revenue-only plans, we should honor the
2552 * target's gen source code instead of the gen source codes specified
2553 * at the task level. */
2554 IF p_fp_cols_rec.x_version_type = 'REVENUE' THEN
2555 l_gen_etc_source_code_override := l_fcst_gen_src_code;
2556 ELSE
2557 l_gen_etc_source_code_override := NULL;
2558 END IF;
2559
2560 --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;
2561 --dbms_output.put_line('l_count_tmp:'||l_count_tmp);
2562 --hr_utility.trace('bef cursor for etc amt calc');
2563 OPEN traverse_tasks_rlm_cur(l_gen_etc_source_code_override);
2564 FETCH traverse_tasks_rlm_cur
2565 BULK COLLECT
2566 INTO l_task_id_tab2,
2567 l_gen_etc_source_code_tab2,
2568 l_src_ra_id_tab2,
2569 l_tgt_ra_id_tab2,
2570 l_rlm_id_tab2,
2571 l_etc_method_tab2;
2572 CLOSE traverse_tasks_rlm_cur;
2573 /*IF p_pa_debug_mode = 'Y' THEN
2574 pa_fp_gen_amount_utils.fp_debug
2575 (p_msg => 'Value of l_ra_id_tab2.count: '||l_ra_id_tab2.count,
2576 p_module_name => l_module_name,
2577 p_log_level => 5);
2578 END IF;*/
2579 --dbms_output.put_line('++l_ra_id_tab2.count:'||l_ra_id_tab2.count);
2580 --dbms_output.put_line('++l_ra_id_tab2.first:'||l_ra_id_tab2.first);
2581 --dbms_output.put_line('++l_ra_id_tab2.last:'||l_ra_id_tab2.last);
2582 IF P_PA_DEBUG_MODE = 'Y' THEN
2583 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2584 P_CALLED_MODE => P_CALLED_MODE,
2585 P_MSG => 'second traverse cursor, we have how many records?'
2586 ||l_src_ra_id_tab2.count,
2587 P_MODULE_NAME => l_module_name);
2588 END IF;
2589
2590 /*Check the planning options */
2591
2592 -- Bug 4369741: Initialize planning options flags to 'N'
2593 l_wp_planning_options_flag := 'N';
2594 l_fp_planning_options_flag := 'N';
2595
2596 /* Bug 4369741: Call the COMPARE_ETC_SRC_TARGET_FP_OPT API separately
2597 * for Workplan and Financial Plan source(s) as needed and store the
2598 * results in l_wp_planning_options_flag and l_fp_planning_options_flag. */
2599
2600 IF l_wp_version_id IS NOT NULL AND
2601 p_fp_cols_rec.x_gen_etc_src_code IN ('WORKPLAN_RESOURCES','TASK_LEVEL_SEL') THEN
2602
2603 IF P_PA_DEBUG_MODE = 'Y' THEN
2604 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2605 P_CALLED_MODE => P_CALLED_MODE,
2606 P_MSG => 'Before calling PA_FP_FCST_GEN_AMT_UTILS.'||
2607 'COMPARE_ETC_SRC_TARGET_FP_OPT',
2608 P_MODULE_NAME => l_module_name);
2609 END IF;
2610 PA_FP_FCST_GEN_AMT_UTILS.COMPARE_ETC_SRC_TARGET_FP_OPT
2611 (P_PROJECT_ID => P_PROJECT_ID,
2612 P_WP_SRC_PLAN_VER_ID => l_wp_version_id,
2613 P_FP_SRC_PLAN_VER_ID => NULL, /* Bug 4369741 */
2614 P_FP_TARGET_PLAN_VER_ID => P_BUDGET_VERSION_ID,
2615 X_SAME_PLANNING_OPTION_FLAG => l_wp_planning_options_flag, /* Bug 4369741 */
2616 X_RETURN_STATUS => X_RETURN_STATUS,
2617 X_MSG_COUNT => X_MSG_COUNT,
2618 X_MSG_DATA => X_MSG_DATA);
2619 IF P_PA_DEBUG_MODE = 'Y' THEN
2620 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2621 P_CALLED_MODE => P_CALLED_MODE,
2622 P_MSG => 'After calling PA_FP_FCST_GEN_AMT_UTILS.'||
2623 'COMPARE_ETC_SRC_TARGET_FP_OPT:'||l_wp_planning_options_flag,
2624 P_MODULE_NAME => l_module_name);
2625 END IF;
2626 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2627 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2628 END IF;
2629 END IF; -- get WP planning options flags
2630
2631 IF p_etc_fp_plan_version_id IS NOT NULL AND
2632 p_fp_cols_rec.x_gen_etc_src_code IN ('FINANCIAL_PLAN','TASK_LEVEL_SEL') THEN
2633
2634 IF P_PA_DEBUG_MODE = 'Y' THEN
2635 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2636 P_CALLED_MODE => P_CALLED_MODE,
2637 P_MSG => 'Before calling PA_FP_FCST_GEN_AMT_UTILS.'||
2638 'COMPARE_ETC_SRC_TARGET_FP_OPT',
2639 P_MODULE_NAME => l_module_name);
2640 END IF;
2641 PA_FP_FCST_GEN_AMT_UTILS.COMPARE_ETC_SRC_TARGET_FP_OPT
2642 (P_PROJECT_ID => P_PROJECT_ID,
2643 P_WP_SRC_PLAN_VER_ID => null, /* Bug 4369741 */
2644 P_FP_SRC_PLAN_VER_ID => P_ETC_FP_PLAN_VERSION_ID,
2645 P_FP_TARGET_PLAN_VER_ID => P_BUDGET_VERSION_ID,
2646 X_SAME_PLANNING_OPTION_FLAG => l_fp_planning_options_flag, /* Bug 4369741 */
2647 X_RETURN_STATUS => X_RETURN_STATUS,
2648 X_MSG_COUNT => X_MSG_COUNT,
2649 X_MSG_DATA => X_MSG_DATA);
2650 IF P_PA_DEBUG_MODE = 'Y' THEN
2651 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2652 P_CALLED_MODE => P_CALLED_MODE,
2653 P_MSG => 'After calling PA_FP_FCST_GEN_AMT_UTILS.'||
2654 'COMPARE_ETC_SRC_TARGET_FP_OPT:'||l_fp_planning_options_flag,
2655 P_MODULE_NAME => l_module_name);
2656 END IF;
2657 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2658 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2659 END IF;
2660 END IF; -- get FP planning options flags
2661
2662
2663 /* hr_utility.trace('l_src_ra_id_tab2.COUNT :'||l_src_ra_id_tab2.COUNT);
2664 hr_utility.trace('before for loop :'); */
2665
2666 IF P_PA_DEBUG_MODE = 'Y' THEN
2667 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2668 P_CALLED_MODE => P_CALLED_MODE,
2669 P_MSG => 'Before calling PA_FP_GEN_FCST_AMT_PVT.'||
2670 'UPD_TGT_RATE_BASED_FLAG',
2671 P_MODULE_NAME => l_module_name);
2672 END IF;
2673 PA_FP_GEN_FCST_AMT_PVT.UPD_TGT_RATE_BASED_FLAG(
2674 P_FP_COLS_REC => P_FP_COLS_REC,
2675 X_RETURN_STATUS => x_return_status,
2676 X_MSG_COUNT => x_msg_count,
2677 X_MSG_DATA => x_msg_data);
2678 IF P_PA_DEBUG_MODE = 'Y' THEN
2679 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2680 P_CALLED_MODE => P_CALLED_MODE,
2681 P_MSG => 'After calling PA_FP_GEN_FCST_AMT_PVT.'||
2682 'UPD_TGT_RATE_BASED_FLAG: '||x_return_status,
2683 P_MODULE_NAME => l_module_name);
2684 END IF;
2685 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2686 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2687 END IF;
2688
2689 /* Initialize indices for ETC method PL/SQL tables */
2690 l_rb_index := 0;
2691 l_bc_index := 0;
2692 l_ev_index := 0;
2693
2694 FOR i IN 1..l_src_ra_id_tab2.COUNT LOOP
2695 /*hr_utility.trace('===i===='||i);
2696 hr_utility.trace('task id :'||l_task_id_tab2(i));
2697 hr_utility.trace('rlm id :'||l_rlm_id_tab2(i));
2698 hr_utility.trace('src ra id :'||l_src_ra_id_tab2(i));
2699 hr_utility.trace('gen etc src code:'||l_gen_etc_source_code_tab2(i));
2700 hr_utility.trace('gen etc mtd code:'||l_etc_method_tab2(i)); */
2701
2702 l_curr_task_id := l_task_id_tab2(i);
2703 l_curr_etc_source := l_gen_etc_source_code_tab2(i);
2704 l_curr_src_ra_id := l_src_ra_id_tab2(i);
2705 l_curr_tgt_ra_id := l_tgt_ra_id_tab2(i);
2706 l_curr_rlm_id := l_rlm_id_tab2(i);
2707 l_curr_etc_method_code := l_etc_method_tab2(i);
2708 /*IF p_pa_debug_mode = 'Y' THEN
2709 pa_fp_gen_amount_utils.fp_debug
2710 (p_msg => 'Value of l_curr_task_id: '||l_curr_task_id,
2711 p_module_name => l_module_name,
2712 p_log_level => 5);
2713 END IF;
2714 IF p_pa_debug_mode = 'Y' THEN
2715 pa_fp_gen_amount_utils.fp_debug
2716 (p_msg => 'Value of l_curr_etc_source: '||l_curr_etc_source,
2717 p_module_name => l_module_name,
2718 p_log_level => 5);
2719 END IF;
2720 IF p_pa_debug_mode = 'Y' THEN
2721 pa_fp_gen_amount_utils.fp_debug
2722 (p_msg => 'Value of l_curr_ra_id: '||l_curr_ra_id,
2723 p_module_name => l_module_name,
2724 p_log_level => 5);
2725 END IF;
2726 IF p_pa_debug_mode = 'Y' THEN
2727 pa_fp_gen_amount_utils.fp_debug
2728 (p_msg => 'Value of l_curr_rlm_id: '||l_curr_rlm_id,
2729 p_module_name => l_module_name,
2730 p_log_level => 5);
2731 END IF;
2732 IF p_pa_debug_mode = 'Y' THEN
2733 pa_fp_gen_amount_utils.fp_debug
2734 (p_msg => 'Value of l_curr_etc_method_code: '||l_curr_etc_method_code,
2735 p_module_name => l_module_name,
2736 p_log_level => 5);
2737 END IF;*/
2738 --dbms_output.put_line('@l_curr_task_id:'|| l_curr_task_id);
2739 --dbms_output.put_line('@l_curr_etc_src:'|| l_curr_etc_source);
2740 --dbms_output.put_line('@l_curr_rlm_id'||l_curr_rlm_id);
2741 --dbms_output.put_line('@l_curr_ra_id'||l_curr_ra_id);
2742 --dbms_output.put_line('@l_curr_etc_method:'|| l_curr_etc_method_code);
2743
2744 IF l_curr_etc_source = 'FINANCIAL_PLAN'
2745 OR l_curr_etc_source = 'WORKPLAN_RESOURCES' THEN
2746
2747 IF l_curr_etc_method_code = 'REMAINING_BUDGET' THEN
2748 l_rb_index := l_rb_index + 1;
2749 l_rb_src_ra_id_tab(l_rb_index) := l_curr_src_ra_id;
2750 l_rb_tgt_ra_id_tab(l_rb_index) := l_curr_tgt_ra_id;
2751 l_rb_task_id_tab(l_rb_index) := l_curr_task_id;
2752 l_rb_rlm_id_tab(l_rb_index) := l_curr_rlm_id;
2753 l_rb_etc_source_tab(l_rb_index) := l_curr_etc_source;
2754 ELSIF l_curr_etc_method_code = 'BUDGET_TO_COMPLETE' THEN
2755 l_bc_index := l_bc_index + 1;
2756 l_bc_src_ra_id_tab(l_bc_index) := l_curr_src_ra_id;
2757 l_bc_tgt_ra_id_tab(l_bc_index) := l_curr_tgt_ra_id;
2758 l_bc_task_id_tab(l_bc_index) := l_curr_task_id;
2759 l_bc_rlm_id_tab(l_bc_index) := l_curr_rlm_id;
2760 l_bc_etc_source_tab(l_bc_index) := l_curr_etc_source;
2761 ELSIF l_curr_etc_method_code = 'EARNED_VALUE' THEN
2762 l_ev_index := l_ev_index + 1;
2763 l_ev_src_ra_id_tab(l_ev_index) := l_curr_src_ra_id;
2764 l_ev_tgt_ra_id_tab(l_ev_index) := l_curr_tgt_ra_id;
2765 l_ev_task_id_tab(l_ev_index) := l_curr_task_id;
2766 l_ev_rlm_id_tab(l_ev_index) := l_curr_rlm_id;
2767 l_ev_etc_source_tab(l_ev_index) := l_curr_etc_source;
2768 END IF;
2769 END IF;
2770 END LOOP;
2771
2772 IF l_rb_src_ra_id_tab.count > 0 THEN
2773 /* Bug 4369741: Pass NULL for the P_PLANNING_OPTIONS_FLAG parameter
2774 * to make explicit the fact that the ETC method APIs do not make
2775 * use of the planning options flag. All of the planning options logic
2776 * is present in this API itself. */
2777 IF P_PA_DEBUG_MODE = 'Y' THEN
2778 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2779 P_CALLED_MODE => P_CALLED_MODE,
2780 P_MSG => 'Before calling pa_fp_gen_fcst_amt_pub3.'||
2781 'GET_ETC_REMAIN_BDGT_AMTS_BLK',
2782 P_MODULE_NAME => l_module_name);
2783 END IF;
2784 PA_FP_GEN_FCST_AMT_PUB3.GET_ETC_REMAIN_BDGT_AMTS_BLK(
2785 P_SRC_RES_ASG_ID_TAB => l_rb_src_ra_id_tab,
2786 P_TGT_RES_ASG_ID_TAB => l_rb_tgt_ra_id_tab,
2787 P_FP_COLS_SRC_REC_FP => l_fp_cols_rec_fp,
2788 P_FP_COLS_SRC_REC_WP => l_fp_cols_rec_wp,
2789 P_FP_COLS_TGT_REC => P_FP_COLS_REC,
2790 P_TASK_ID_TAB => l_rb_task_id_tab,
2791 P_RES_LIST_MEMBER_ID_TAB => l_rb_rlm_id_tab,
2792 P_ETC_SOURCE_CODE_TAB => l_rb_etc_source_tab,
2793 P_WP_STRUCTURE_VERSION_ID => P_WP_STRUCTURE_VERSION_ID,
2794 P_ACTUALS_THRU_DATE => P_ACTUALS_THRU_DATE,
2795 P_PLANNING_OPTIONS_FLAG => NULL, /* Bug 4369741 */
2796 X_RETURN_STATUS => x_return_status,
2797 X_MSG_COUNT => x_msg_count,
2798 X_MSG_DATA => x_msg_data);
2799 IF p_pa_debug_mode = 'Y' THEN
2800 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2801 P_CALLED_MODE => P_CALLED_MODE,
2802 P_MSG => 'After calling pa_fp_gen_fcst_amt_pub3.'||
2803 'GET_ETC_REMAIN_BDGT_AMTS_BLK: '||x_return_status,
2804 P_MODULE_NAME => l_module_name);
2805 END IF;
2806 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2807 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2808 END IF;
2809 END IF;
2810
2811 IF l_bc_src_ra_id_tab.count > 0 THEN
2812 /* Bug 4369741: Pass NULL for the P_PLANNING_OPTIONS_FLAG parameter
2813 * to make explicit the fact that the ETC method APIs do not make
2814 * use of the planning options flag. All of the planning options logic
2815 * is present in this API itself. */
2816 IF P_PA_DEBUG_MODE = 'Y' THEN
2817 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2818 P_CALLED_MODE => P_CALLED_MODE,
2819 P_MSG => 'Before calling pa_fp_gen_fcst_amt_pub4.'||
2820 'GET_ETC_BDGT_COMPLETE_AMTS_BLK',
2821 P_MODULE_NAME => l_module_name);
2822 END IF;
2823 PA_FP_GEN_FCST_AMT_PUB4.GET_ETC_BDGT_COMPLETE_AMTS_BLK(
2824 P_SRC_RES_ASG_ID_TAB => l_bc_src_ra_id_tab,
2825 P_TGT_RES_ASG_ID_TAB => l_bc_tgt_ra_id_tab,
2826 P_FP_COLS_SRC_REC_FP => l_fp_cols_rec_fp,
2827 P_FP_COLS_SRC_REC_WP => l_fp_cols_rec_wp,
2828 P_FP_COLS_TGT_REC => P_FP_COLS_REC,
2829 P_TASK_ID_TAB => l_bc_task_id_tab,
2830 P_RES_LIST_MEMBER_ID_TAB => l_bc_rlm_id_tab,
2831 P_ETC_SOURCE_CODE_TAB => l_bc_etc_source_tab,
2832 P_WP_STRUCTURE_VERSION_ID => P_WP_STRUCTURE_VERSION_ID,
2833 P_ACTUALS_THRU_DATE => P_ACTUALS_THRU_DATE,
2834 P_PLANNING_OPTIONS_FLAG => NULL, /* Bug 4369741 */
2835 X_RETURN_STATUS => x_return_status,
2836 X_MSG_COUNT => x_msg_count,
2837 X_MSG_DATA => x_msg_data);
2838 IF p_pa_debug_mode = 'Y' THEN
2839 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2840 P_CALLED_MODE => P_CALLED_MODE,
2841 P_MSG => 'After calling pa_fp_gen_fcst_amt_pub4.'||
2842 'GET_ETC_BDGT_COMPLETE_AMTS_BLK: '||x_return_status,
2843 P_MODULE_NAME => l_module_name);
2844 END IF;
2845 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2846 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2847 END IF;
2848 END IF;
2849
2850 IF l_ev_src_ra_id_tab.count > 0 THEN
2851 /* Bug 4369741: Pass NULL for the P_PLANNING_OPTIONS_FLAG parameter
2852 * to make explicit the fact that the ETC method APIs do not make
2853 * use of the planning options flag. All of the planning options logic
2854 * is present in this API itself. */
2855 IF P_PA_DEBUG_MODE = 'Y' THEN
2856 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2857 P_CALLED_MODE => P_CALLED_MODE,
2858 P_MSG => 'Before calling pa_fp_gen_fcst_amt_pub5.'||
2859 'GET_ETC_EARNED_VALUE_AMTS_BLK',
2860 P_MODULE_NAME => l_module_name);
2861 END IF;
2862 PA_FP_GEN_FCST_AMT_PUB5.GET_ETC_EARNED_VALUE_AMTS_BLK(
2863 P_SRC_RES_ASG_ID_TAB => l_ev_src_ra_id_tab,
2864 P_TGT_RES_ASG_ID_TAB => l_ev_tgt_ra_id_tab,
2865 P_FP_COLS_SRC_REC_FP => l_fp_cols_rec_fp,
2866 P_FP_COLS_SRC_REC_WP => l_fp_cols_rec_wp,
2867 P_FP_COLS_TGT_REC => P_FP_COLS_REC,
2868 P_TASK_ID_TAB => l_ev_task_id_tab,
2869 P_RES_LIST_MEMBER_ID_TAB => l_ev_rlm_id_tab,
2870 P_ETC_SOURCE_CODE_TAB => l_ev_etc_source_tab,
2871 P_WP_STRUCTURE_VERSION_ID => P_WP_STRUCTURE_VERSION_ID,
2872 P_ACTUALS_THRU_DATE => P_ACTUALS_THRU_DATE,
2873 P_PLANNING_OPTIONS_FLAG => NULL, /* Bug 4369741 */
2874 X_RETURN_STATUS => x_return_status,
2875 X_MSG_COUNT => x_msg_count,
2876 X_MSG_DATA => x_msg_data);
2877 IF p_pa_debug_mode = 'Y' THEN
2878 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2879 P_CALLED_MODE => P_CALLED_MODE,
2880 P_MSG => 'After calling pa_fp_gen_fcst_amt_pub5.'||
2881 'GET_ETC_EARNED_VALUE_AMTS_BLK: '||x_return_status,
2882 P_MODULE_NAME => l_module_name);
2883 END IF;
2884 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2885 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2886 END IF;
2887 END IF;
2888
2889 IF p_fp_cols_rec.x_gen_incl_open_comm_flag = 'Y' THEN
2890
2891 /* Bug 4369741: Modified parameters of GET_ETC_COMMITMENT_AMTS
2892 * API to reflect spec change from a single planning options
2893 * flag to 2 separate flags for Workplan and Financial Plan sources. */
2894
2895 -- hr_utility.trace('after for loop :');
2896 IF P_PA_DEBUG_MODE = 'Y' THEN
2897 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2898 P_CALLED_MODE => P_CALLED_MODE,
2899 P_MSG => 'Before calling PA_FP_GEN_FCST_AMT_PUB3.'||
2900 'GET_ETC_COMMITMENT_AMTS',
2901 P_MODULE_NAME => l_module_name);
2902 END IF;
2903 -- hr_utility.trace('before pub3 cmt amts:');
2904 PA_FP_GEN_FCST_AMT_PUB3.GET_ETC_COMMITMENT_AMTS
2905 (P_FP_COLS_TGT_REC => p_fp_cols_rec,
2906 P_WP_PLANNING_OPTIONS_FLAG => l_wp_planning_options_flag, /* Bug 4369741 */
2907 P_FP_PLANNING_OPTIONS_FLAG => l_fp_planning_options_flag, /* Bug 4369741 */
2908 X_RETURN_STATUS => x_return_status,
2909 X_MSG_COUNT => x_msg_count,
2910 X_MSG_DATA => x_msg_data);
2911
2912 -- hr_utility.trace('after pub3 cmt amts:');
2913
2914 IF p_pa_debug_mode = 'Y' THEN
2915 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2916 P_CALLED_MODE => P_CALLED_MODE,
2917 P_MSG => 'After calling PA_FP_GEN_FCST_AMT_PUB3.'||
2918 'GET_ETC_COMMITMENT_AMTS: '||x_return_status,
2919 P_MODULE_NAME => l_module_name);
2920 END IF;
2921 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2922 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2923 END IF;
2924 END IF;
2925
2926 --hr_utility.trace('After processing all etc source!');
2927 --dbms_output.put_line('--before mapping++');
2928 /**After calling apis based on each res_asg_id's gen_method, we have all the plan totals and
2929 *ETC amounts populated in tmp2 table, now, we need to map the amounts to the target fin
2930 *plan's resource list, summ up, call calculate to spread into pa_budget_lines if time_phase
2931 *is not null, otherwise, we need to populate into pa_budget_lines by ourselves**/
2932
2933 /* select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;
2934 hr_utility.trace('tmp1 count :'|| l_count_tmp);
2935 select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;
2936 hr_utility.trace('tmp2 count :'|| l_count_tmp); */
2937 --delete from calc_amt_tmp11;
2938 --insert into calc_amt_tmp11 select * from pa_fp_calc_amt_tmp1;
2939
2940 --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;
2941 --hr_utility.trace('***PA_FP_CALC_AMT_TMP1.count'||l_count_tmp);
2942 --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;
2943 --hr_utility.trace('**PA_FP_CALC_AMT_TMP2.count'||l_count_tmp);
2944 --hr_utility.trace('**P_FP_COLS_REC.X_TIME_PHASED_CODE:'||P_FP_COLS_REC.X_TIME_PHASED_CODE);
2945
2946 --l_rev_gen_method := PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(p_project_id);
2947 l_rev_gen_method := nvl(P_FP_COLS_REC.X_REVENUE_DERIVATION_METHOD,PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(p_project_id)); --Bug 5462471
2948 IF l_fp_cols_rec_target.x_version_type = 'REVENUE' and l_rev_gen_method = 'C' THEN
2949 IF p_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'N' THEN
2950 SELECT DISTINCT target_res_asg_id
2951 BULK COLLECT INTO l_res_asg_uom_update_tab
2952 FROM PA_FP_CALC_AMT_TMP2
2953 WHERE transaction_source_code = 'ETC';
2954 ELSIF p_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'Y' THEN
2955 IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
2956 l_etc_start_date :=
2957 PA_FP_GEN_AMOUNT_UTILS.GET_ETC_START_DATE(p_budget_version_id);
2958
2959 SELECT /*+ INDEX(tmp1,PA_FP_CALC_AMT_TMP1_N1)*/
2960 DISTINCT tmp1.target_res_asg_id
2961 BULK COLLECT
2962 INTO l_res_asg_uom_update_tab
2963 FROM PA_FP_CALC_AMT_TMP1 tmp1,
2964 pa_resource_assignments ra
2965 WHERE ra.budget_version_id = p_budget_version_id
2966 AND ra.resource_assignment_id = tmp1.target_res_asg_id
2967 AND ( ra.transaction_source_code IS NOT NULL
2968 OR ( ra.transaction_source_code IS NULL
2969 AND NOT EXISTS ( SELECT 1
2970 FROM pa_budget_lines bl
2971 WHERE bl.resource_assignment_id =
2972 ra.resource_assignment_id
2973 AND bl.start_date >= l_etc_start_date
2974 AND rownum = 1 )))
2975 AND EXISTS ( SELECT /*+ INDEX(tmp2,PA_FP_CALC_AMT_TMP2_N1)*/ 1
2976 FROM PA_FP_CALC_AMT_TMP2 tmp2
2977 WHERE tmp2.target_res_asg_id = tmp1.target_res_asg_id
2978 AND tmp2.transaction_source_code = 'ETC'
2979 AND rownum = 1 );
2980 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
2981 SELECT /*+ INDEX(tmp1,PA_FP_CALC_AMT_TMP1_N1)*/
2982 DISTINCT tmp1.target_res_asg_id
2983 BULK COLLECT
2984 INTO l_res_asg_uom_update_tab
2985 FROM PA_FP_CALC_AMT_TMP1 tmp1,
2986 pa_resource_assignments ra
2987 WHERE ra.budget_version_id = p_budget_version_id
2988 AND ra.resource_assignment_id = tmp1.target_res_asg_id
2989 AND ( ra.transaction_source_code IS NOT NULL
2990 OR ( ra.transaction_source_code IS NULL
2991 AND NOT EXISTS ( SELECT 1
2992 FROM pa_budget_lines bl
2993 WHERE bl.resource_assignment_id =
2994 ra.resource_assignment_id
2995 AND NVL(bl.quantity,0) <>
2996 NVL(bl.init_quantity,0)
2997 AND rownum = 1 )))
2998 AND EXISTS ( SELECT /*+ INDEX(tmp2,PA_FP_CALC_AMT_TMP2_N1)*/ 1
2999 FROM PA_FP_CALC_AMT_TMP2 tmp2
3000 WHERE tmp2.target_res_asg_id = tmp1.target_res_asg_id
3001 AND tmp2.transaction_source_code = 'ETC'
3002 AND rownum = 1 );
3003 END IF; -- time phase check
3004 END IF;
3005
3006 FORALL i IN 1..l_res_asg_uom_update_tab.count
3007 UPDATE pa_resource_assignments
3008 SET unit_of_measure = 'DOLLARS',
3009 rate_based_flag = 'N'
3010 WHERE resource_assignment_id = l_res_asg_uom_update_tab(i);
3011 END IF;
3012
3013 l_cal_ra_id_tab.delete;
3014 l_cal_txn_currency_code_tab.delete;
3015 l_cal_unit_of_measure_tab.delete;
3016 l_cal_etc_qty_tab.delete;
3017 l_cal_etc_raw_cost_tab.delete;
3018 l_cal_etc_burdened_cost_tab.delete;
3019 l_cal_rate_based_flag_tab.delete;
3020
3021 l_cal_rlm_id_tab.delete;
3022 l_cal_task_id_tab.delete;
3023 l_cal_etc_method_code_tab.delete;
3024
3025 --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;
3026 --hr_utility.trace('?????PA_FP_CALC_AMT_TMP2.count'||l_count_tmp);
3027
3028 -- select count(*) into l_test from PA_FP_CALC_AMT_TMP2 where
3029 -- TRANSACTION_SOURCE_CODE = 'ETC';
3030 -- hr_utility.trace('calc amt tmp2 count with src code as ETC:'||l_test);
3031
3032 IF ( l_fp_planning_options_flag = 'Y' AND
3033 P_FP_COLS_REC.x_gen_etc_src_code = 'FINANCIAL_PLAN' ) OR
3034 ( l_wp_planning_options_flag = 'Y' AND
3035 P_FP_COLS_REC.x_gen_etc_src_code = 'WORKPLAN_RESOURCES' ) THEN
3036 OPEN etc_amts_cur_wp_fp_opt_same
3037 ( p_fp_cols_rec.x_gen_etc_src_code );
3038 FETCH etc_amts_cur_wp_fp_opt_same
3039 BULK COLLECT
3040 INTO l_cal_src_ra_id_tab_tmp, /* Modified during ER 4376722 */
3041 l_cal_ra_id_tab_tmp,
3042 l_cal_txn_curr_code_tab_tmp,
3043 l_cal_rate_based_flag_tab_tmp,
3044 l_cal_rlm_id_tab_tmp,
3045 l_cal_task_id_tab_tmp,
3046 l_cal_unit_of_measure_tab_tmp,
3047 l_cal_etc_method_code_tab_tmp,
3048 l_cal_etc_qty_tab_tmp,
3049 l_cal_etc_raw_cost_tab_tmp,
3050 l_cal_etc_brdn_cost_tab_tmp,
3051 l_cal_etc_revenue_tab_tmp,
3052 l_billable_flag_tab_tmp, /* Added for ER 4376722 */
3053 l_gen_etc_src_code_tab_tmp; /* Added for Bug 4369741 */
3054 CLOSE etc_amts_cur_wp_fp_opt_same;
3055 ELSIF ( l_fp_planning_options_flag = 'N' AND
3056 P_FP_COLS_REC.x_gen_etc_src_code = 'FINANCIAL_PLAN' ) OR
3057 ( l_wp_planning_options_flag = 'N' AND
3058 P_FP_COLS_REC.x_gen_etc_src_code = 'WORKPLAN_RESOURCES' ) THEN
3059 OPEN etc_amts_cur_wp_fp_opt_diff
3060 ( p_fp_cols_rec.x_gen_etc_src_code );
3061 FETCH etc_amts_cur_wp_fp_opt_diff
3062 BULK COLLECT
3063 INTO l_cal_src_ra_id_tab_tmp, /* Modified during ER 4376722 */
3064 l_cal_ra_id_tab_tmp,
3065 l_cal_txn_curr_code_tab_tmp,
3066 l_cal_rate_based_flag_tab_tmp,
3067 l_cal_rlm_id_tab_tmp,
3068 l_cal_task_id_tab_tmp,
3069 l_cal_unit_of_measure_tab_tmp,
3070 l_cal_etc_method_code_tab_tmp,
3071 l_cal_etc_qty_tab_tmp,
3072 l_cal_etc_raw_cost_tab_tmp,
3073 l_cal_etc_brdn_cost_tab_tmp,
3074 l_cal_etc_revenue_tab_tmp,
3075 l_billable_flag_tab_tmp, /* Added for ER 4376722 */
3076 l_gen_etc_src_code_tab_tmp; /* Added for Bug 4369741 */
3077 CLOSE etc_amts_cur_wp_fp_opt_diff;
3078 ELSIF P_FP_COLS_REC.x_gen_etc_src_code = 'TASK_LEVEL_SEL' THEN
3079 IF l_wp_planning_options_flag = 'Y' AND
3080 l_fp_planning_options_flag = 'Y' THEN
3081 OPEN etc_amts_cur_wp_fp_opt_same;
3082 FETCH etc_amts_cur_wp_fp_opt_same
3083 BULK COLLECT
3084 INTO l_cal_src_ra_id_tab_tmp, /* Modified during ER 4376722 */
3085 l_cal_ra_id_tab_tmp,
3086 l_cal_txn_curr_code_tab_tmp,
3087 l_cal_rate_based_flag_tab_tmp,
3088 l_cal_rlm_id_tab_tmp,
3089 l_cal_task_id_tab_tmp,
3090 l_cal_unit_of_measure_tab_tmp,
3091 l_cal_etc_method_code_tab_tmp,
3092 l_cal_etc_qty_tab_tmp,
3093 l_cal_etc_raw_cost_tab_tmp,
3094 l_cal_etc_brdn_cost_tab_tmp,
3095 l_cal_etc_revenue_tab_tmp,
3096 l_billable_flag_tab_tmp, /* Added for ER 4376722 */
3097 l_gen_etc_src_code_tab_tmp; /* Added for Bug 4369741 */
3098 CLOSE etc_amts_cur_wp_fp_opt_same;
3099 ELSIF l_wp_planning_options_flag = 'Y' AND
3100 l_fp_planning_options_flag = 'N' THEN
3101 OPEN etc_amts_cur_wp_opt_same;
3102 FETCH etc_amts_cur_wp_opt_same
3103 BULK COLLECT
3104 INTO l_cal_src_ra_id_tab_tmp, /* Modified during ER 4376722 */
3105 l_cal_ra_id_tab_tmp,
3106 l_cal_txn_curr_code_tab_tmp,
3107 l_cal_rate_based_flag_tab_tmp,
3108 l_cal_rlm_id_tab_tmp,
3109 l_cal_task_id_tab_tmp,
3110 l_cal_unit_of_measure_tab_tmp,
3111 l_cal_etc_method_code_tab_tmp,
3112 l_cal_etc_qty_tab_tmp,
3113 l_cal_etc_raw_cost_tab_tmp,
3114 l_cal_etc_brdn_cost_tab_tmp,
3115 l_cal_etc_revenue_tab_tmp,
3116 l_billable_flag_tab_tmp, /* Added for ER 4376722 */
3117 l_gen_etc_src_code_tab_tmp; /* Added for Bug 4369741 */
3118 CLOSE etc_amts_cur_wp_opt_same;
3119 ELSIF l_wp_planning_options_flag = 'N' AND
3120 l_fp_planning_options_flag = 'Y' THEN
3121 OPEN etc_amts_cur_fp_opt_same;
3122 FETCH etc_amts_cur_fp_opt_same
3123 BULK COLLECT
3124 INTO l_cal_src_ra_id_tab_tmp, /* Modified during ER 4376722 */
3125 l_cal_ra_id_tab_tmp,
3126 l_cal_txn_curr_code_tab_tmp,
3127 l_cal_rate_based_flag_tab_tmp,
3128 l_cal_rlm_id_tab_tmp,
3129 l_cal_task_id_tab_tmp,
3130 l_cal_unit_of_measure_tab_tmp,
3131 l_cal_etc_method_code_tab_tmp,
3132 l_cal_etc_qty_tab_tmp,
3133 l_cal_etc_raw_cost_tab_tmp,
3134 l_cal_etc_brdn_cost_tab_tmp,
3135 l_cal_etc_revenue_tab_tmp,
3136 l_billable_flag_tab_tmp, /* Added for ER 4376722 */
3137 l_gen_etc_src_code_tab_tmp; /* Added for Bug 4369741 */
3138 CLOSE etc_amts_cur_fp_opt_same;
3139 ELSIF l_wp_planning_options_flag = 'N' AND
3140 l_fp_planning_options_flag = 'N' THEN
3141 OPEN etc_amts_cur_wp_fp_opt_diff;
3142 FETCH etc_amts_cur_wp_fp_opt_diff
3143 BULK COLLECT
3144 INTO l_cal_src_ra_id_tab_tmp, /* Modified during ER 4376722 */
3145 l_cal_ra_id_tab_tmp,
3146 l_cal_txn_curr_code_tab_tmp,
3147 l_cal_rate_based_flag_tab_tmp,
3148 l_cal_rlm_id_tab_tmp,
3149 l_cal_task_id_tab_tmp,
3150 l_cal_unit_of_measure_tab_tmp,
3151 l_cal_etc_method_code_tab_tmp,
3152 l_cal_etc_qty_tab_tmp,
3153 l_cal_etc_raw_cost_tab_tmp,
3154 l_cal_etc_brdn_cost_tab_tmp,
3155 l_cal_etc_revenue_tab_tmp,
3156 l_billable_flag_tab_tmp, /* Added for ER 4376722 */
3157 l_gen_etc_src_code_tab_tmp; /* Added for Bug 4369741 */
3158 CLOSE etc_amts_cur_wp_fp_opt_diff;
3159 END IF;
3160 ELSE
3161 -- error handling code stub
3162 l_dummy := 1;
3163 END IF; -- fetch ETC data
3164 --hr_utility.trace('????l_cal_ra_id_tab.count:'||l_cal_ra_id_tab.count);
3165
3166
3167 IF p_fp_cols_rec.x_gen_ret_manual_line_flag = 'Y' THEN
3168
3169 FOR i in 1..l_cal_ra_id_tab_tmp.count LOOP
3170 SELECT transaction_source_code
3171 INTO l_ra_txn_source_code
3172 FROM pa_resource_assignments
3173 WHERE resource_assignment_id = l_cal_ra_id_tab_tmp(i);
3174
3175 l_bl_count := 0;
3176
3177 -- Bug 4301959: Modified the Retain Manually Added Lines logic to
3178 -- handle the non-time phased case separately, using the (quantity <>
3179 -- actual quantity) check instead of (start_date > etc_start_date).
3180
3181 IF l_ra_txn_source_code IS NULL THEN
3182 IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
3183 SELECT count(*)
3184 INTO l_bl_count
3185 FROM pa_budget_lines
3186 WHERE resource_assignment_id = l_cal_ra_id_tab_tmp(i)
3187 AND start_date > p_actuals_thru_date;
3188 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
3189 SELECT count(*)
3190 INTO l_bl_count
3191 FROM pa_budget_lines
3192 WHERE resource_assignment_id = l_cal_ra_id_tab_tmp(i)
3193 AND NVL(quantity,0) <> NVL(init_quantity,0);
3194 END IF;
3195 END IF;
3196
3197 /* hr_utility.trace('blines count for res asg id '||
3198 l_bl_count || ' for res asg '||l_cal_ra_id_tab_tmp(i)); */
3199
3200 /* modified the logic for handling retain manually added lines
3201 for bug 3968630. If the txn source code for the planning resource is
3202 not null (generated - not manually entered), then the existing
3203 plan lines should be deleted and amounts should be generated from the
3204 generation source. */
3205
3206 IF ( l_ra_txn_source_code IS NULL AND l_bl_count > 0 ) THEN
3207 /* Mannually entered lines do exist, so they will be honored,
3208 source records will be dropped */
3209 l_dummy := 1;
3210 ELSE
3211 /* Mannually entered lines don't exist, so source records will
3212 be honored */
3213 IF p_fp_cols_rec.x_time_phased_code = 'P'
3214 OR p_fp_cols_rec.x_time_phased_code = 'G' THEN
3215 DELETE FROM pa_budget_lines
3216 WHERE resource_assignment_id = l_cal_ra_id_tab_tmp(i)
3217 AND start_date > p_actuals_thru_date;
3218 END IF;
3219
3220 -- hr_utility.trace('inside table population ');
3221
3222 l_cal_ra_id_tab.EXTEND;
3223 l_cal_txn_currency_code_tab.EXTEND;
3224 l_cal_rate_based_flag_tab.EXTEND;
3225 l_cal_rlm_id_tab.EXTEND;
3226 l_cal_task_id_tab.EXTEND;
3227 l_cal_unit_of_measure_tab.EXTEND;
3228 l_cal_etc_method_code_tab.EXTEND;
3229 l_cal_etc_qty_tab.EXTEND;
3230 l_cal_etc_raw_cost_tab.EXTEND;
3231 l_cal_etc_burdened_cost_tab.EXTEND;
3232 l_cal_etc_revenue_tab.EXTEND;
3233 l_billable_flag_tab.EXTEND; /* Added for ER 4376722 */
3234 l_cal_src_ra_id_tab.EXTEND; /* Modified during ER 4376722 */
3235 l_gen_etc_src_code_tab.EXTEND; /* Added for Bug 4369741 */
3236
3237 l_cal_ra_id_tab(l_cnt) := l_cal_ra_id_tab_tmp(i);
3238 l_cal_txn_currency_code_tab(l_cnt) := l_cal_txn_curr_code_tab_tmp(i);
3239 l_cal_rate_based_flag_tab(l_cnt) := l_cal_rate_based_flag_tab_tmp(i);
3240 l_cal_rlm_id_tab(l_cnt) := l_cal_rlm_id_tab_tmp(i);
3241 l_cal_task_id_tab(l_cnt) := l_cal_task_id_tab_tmp(i);
3242 l_cal_unit_of_measure_tab(l_cnt) := l_cal_unit_of_measure_tab_tmp(i);
3243 l_cal_etc_method_code_tab(l_cnt) := l_cal_etc_method_code_tab_tmp(i);
3244 l_cal_etc_qty_tab(l_cnt) := l_cal_etc_qty_tab_tmp(i);
3245 l_cal_etc_raw_cost_tab(l_cnt) := l_cal_etc_raw_cost_tab_tmp(i);
3246 l_cal_etc_burdened_cost_tab(l_cnt) := l_cal_etc_brdn_cost_tab_tmp(i);
3247 l_cal_etc_revenue_tab(l_cnt) := l_cal_etc_revenue_tab_tmp(i);
3248 l_billable_flag_tab(l_cnt) := l_billable_flag_tab_tmp(i); /* Added for ER 4376722 */
3249 l_cal_src_ra_id_tab(l_cnt) := l_cal_src_ra_id_tab_tmp(i); /* Modified during ER 4376722 */
3250 l_gen_etc_src_code_tab(l_cnt) := l_gen_etc_src_code_tab_tmp(i); /* Added for Bug 4369741 */
3251
3252 l_cnt := l_cnt + 1;
3253 END IF;
3254 END LOOP;
3255 -- hr_utility.trace('after the for loop for calc api ');
3256 /* End the logic to handle mannually updated lines*/
3257
3258 ELSE
3259
3260 l_cal_ra_id_tab := l_cal_ra_id_tab_tmp;
3261 l_cal_txn_currency_code_tab := l_cal_txn_curr_code_tab_tmp;
3262 l_cal_rate_based_flag_tab := l_cal_rate_based_flag_tab_tmp;
3263 l_cal_rlm_id_tab := l_cal_rlm_id_tab_tmp;
3264 l_cal_task_id_tab := l_cal_task_id_tab_tmp;
3265 l_cal_unit_of_measure_tab := l_cal_unit_of_measure_tab_tmp;
3266 l_cal_etc_method_code_tab := l_cal_etc_method_code_tab_tmp;
3267 l_cal_etc_qty_tab := l_cal_etc_qty_tab_tmp;
3268 l_cal_etc_raw_cost_tab := l_cal_etc_raw_cost_tab_tmp;
3269 l_cal_etc_burdened_cost_tab := l_cal_etc_brdn_cost_tab_tmp;
3270 l_cal_etc_revenue_tab := l_cal_etc_revenue_tab_tmp;
3271 l_billable_flag_tab := l_billable_flag_tab_tmp; /* Added for ER 4376722 */
3272 l_cal_src_ra_id_tab := l_cal_src_ra_id_tab_tmp; /* Modified during ER 4376722 */
3273 l_gen_etc_src_code_tab := l_gen_etc_src_code_tab_tmp; /* Added for Bug 4369741 */
3274
3275 END IF; -- manual lines condition
3276
3277
3278 -- IPM: New Entity ER ------------------------------------------
3279 IF l_fp_planning_options_flag = 'Y' OR
3280 l_wp_planning_options_flag = 'Y' THEN
3281
3282 -- Sort resource assignment ids into two pl/sql tables
3283 -- based on whether the ETC generation source is FP or WP.
3284 -- Also, track non-billable resources whose source is FP.
3285 FOR i IN 1..l_cal_ra_id_tab.count LOOP
3286 IF l_gen_etc_src_code_tab(i) = 'FINANCIAL_PLAN' THEN
3287 l_fp_ra_id_tab.EXTEND;
3288 l_fp_ra_id_tab(l_fp_ra_id_tab.count) := l_cal_ra_id_tab(i);
3289
3290 IF l_billable_flag_tab(i) = 'N' THEN
3291 l_non_billable_fp_ra_id_tab.EXTEND;
3292 l_non_billable_fp_ra_id_tab(l_non_billable_fp_ra_id_tab.count)
3293 := l_cal_ra_id_tab(i);
3294 END IF;
3295 ELSIF l_gen_etc_src_code_tab(i) = 'WORKPLAN_RESOURCES' THEN
3296 l_wp_ra_id_tab.EXTEND;
3297 l_wp_ra_id_tab(l_wp_ra_id_tab.count) := l_cal_ra_id_tab(i);
3298 END IF;
3299 END LOOP;
3300
3301 -- Index 1 stores Financial Plan data.
3302 l_ra_id_tab_table(1) := l_fp_ra_id_tab;
3303 l_planning_options_flag_tab(1) := l_fp_planning_options_flag;
3304 l_src_version_id_tab(1) := p_etc_fp_plan_version_id;
3305
3306 -- Index 2 stores Workplan data.
3307 l_ra_id_tab_table(2) := l_wp_ra_id_tab;
3308 l_planning_options_flag_tab(2) := l_wp_planning_options_flag;
3309 l_src_version_id_tab(2) := l_wp_version_id;
3310
3311 FOR i IN 1..l_ra_id_tab_table.count LOOP
3312
3313 IF l_ra_id_tab_table(i).count > 0 AND
3314 l_planning_options_flag_tab(i) = 'Y' THEN
3315
3316 DELETE pa_resource_asgn_curr_tmp;
3317
3318 -- As per the copy_table_records API specification, when calling the
3319 -- maintenance API in temp table Copy mode, populate the temp table
3320 -- with distinct target ra_id values (without txn_currency_code values).
3321
3322 INSERT INTO pa_resource_asgn_curr_tmp
3323 ( resource_assignment_id )
3324 SELECT DISTINCT column_value
3325 FROM TABLE( CAST( l_ra_id_tab_table(i) AS SYSTEM.pa_num_tbl_type ));
3326
3327 -- Call the maintenance api in COPY mode
3328 IF p_pa_debug_mode = 'Y' THEN
3329 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
3330 P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.' ||
3331 'MAINTAIN_DATA',
3332 P_CALLED_MODE => p_called_mode,
3333 P_MODULE_NAME => l_module_name);
3334 END IF;
3335 PA_RES_ASG_CURRENCY_PUB.MAINTAIN_DATA
3336 ( P_FP_COLS_REC => l_fp_cols_rec_target,
3337 P_CALLING_MODULE => 'FORECAST_GENERATION',
3338 P_COPY_FLAG => 'Y',
3339 P_SRC_VERSION_ID => l_src_version_id_tab(i),
3340 P_COPY_MODE => 'COPY_OVERRIDES',
3341 P_VERSION_LEVEL_FLAG => 'N',
3342 P_CALLED_MODE => p_called_mode,
3343 X_RETURN_STATUS => x_return_status,
3344 X_MSG_COUNT => x_msg_count,
3345 X_MSG_DATA => x_msg_data );
3346 IF p_pa_debug_mode = 'Y' THEN
3347 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
3348 P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.' ||
3349 'MAINTAIN_DATA: '||x_return_status,
3350 P_CALLED_MODE => p_called_mode,
3351 P_MODULE_NAME => l_module_name);
3352 END IF;
3353 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3354 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3355 END IF;
3356 END IF; -- planning options flag check
3357 END LOOP; -- FOR i IN 1..l_ra_id_tab_table.count LOOP
3358
3359 -- Ensure that non-billable tasks do not have bill rate overrides
3360 -- in the new entity table by re-Inserting new entity records with
3361 -- existing cost rate overrides but Null bill rate overrides for
3362 -- non-billable tasks.
3363 -- Note: Processing resources with Workplan as the source is not
3364 -- required since workplans are guaranteed to store only costs.
3365
3366 IF l_fp_planning_options_flag = 'Y' AND
3367 l_non_billable_fp_ra_id_tab.count > 0 AND
3368 l_fp_cols_rec_fp.x_version_type = 'ALL' AND
3369 l_fp_cols_rec_target.x_version_type IN ('REVENUE','ALL') THEN
3370
3371 DELETE pa_resource_asgn_curr_tmp;
3372
3373 -- Note: An outer join on pa_tasks is not needed in the query
3374 -- below because we are only interested in updating resources
3375 -- for non-billable tasks. Project-level tasks that require an
3376 -- outer join are always billable.
3377 INSERT INTO pa_resource_asgn_curr_tmp
3378 ( RESOURCE_ASSIGNMENT_ID,
3379 TXN_CURRENCY_CODE,
3380 TXN_RAW_COST_RATE_OVERRIDE,
3381 TXN_BURDEN_COST_RATE_OVERRIDE )
3382 SELECT rbc.resource_assignment_id,
3383 rbc.txn_currency_code,
3384 rbc.txn_raw_cost_rate_override,
3385 rbc.txn_burden_cost_rate_override
3386 FROM pa_resource_asgn_curr rbc
3387 WHERE rbc.budget_version_id = p_budget_version_id
3388 AND rbc.txn_bill_rate_override IS NOT NULL
3389 AND EXISTS ( SELECT null
3390 FROM TABLE(CAST( l_non_billable_fp_ra_id_tab AS SYSTEM.pa_num_tbl_type ))
3391 WHERE rbc.resource_assignment_id = column_value );
3392
3393 l_count := SQL%ROWCOUNT;
3394
3395 IF l_count > 0 THEN
3396 -- CALL the maintenance api in INSERT mode
3397 IF p_pa_debug_mode = 'Y' THEN
3398 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
3399 P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.' ||
3400 'MAINTAIN_DATA',
3401 P_CALLED_MODE => p_called_mode,
3402 P_MODULE_NAME => l_module_name);
3403 END IF;
3404 PA_RES_ASG_CURRENCY_PUB.MAINTAIN_DATA
3405 ( P_FP_COLS_REC => l_fp_cols_rec_target,
3406 P_CALLING_MODULE => 'FORECAST_GENERATION',
3407 P_VERSION_LEVEL_FLAG => 'N',
3408 P_ROLLUP_FLAG => 'N', -- 'N' indicates Insert
3409 P_CALLED_MODE => p_called_mode,
3410 X_RETURN_STATUS => x_return_status,
3411 X_MSG_COUNT => x_msg_count,
3412 X_MSG_DATA => x_msg_data );
3413 IF p_pa_debug_mode = 'Y' THEN
3414 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
3415 P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.' ||
3416 'MAINTAIN_DATA: '||x_return_status,
3417 P_CALLED_MODE => p_called_mode,
3418 P_MODULE_NAME => l_module_name);
3419 END IF;
3420 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3421 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3422 END IF;
3423 END IF; -- IF l_count > 0 THEN
3424 END IF; -- logic to null out bill rate overrides for non-billable tasks
3425
3426 END IF; -- logic to copy source pa_resource_asgn_curr overrides
3427 -- END OF IPM: New Entity ER ------------------------------------------
3428
3429
3430 IF l_fp_cols_rec_target.x_version_type = 'REVENUE' and l_rev_gen_method = 'C' THEN
3431 l_cal_etc_revenue_tab := l_cal_etc_burdened_cost_tab;
3432 END IF;
3433 /*IF p_pa_debug_mode = 'Y' THEN
3434 pa_fp_gen_amount_utils.fp_debug
3435 (p_msg => 'Value of l_rev_gen_method: '||l_rev_gen_method,
3436 p_module_name => l_module_name,
3437 p_log_level => 5);
3438 END IF;
3439 IF p_pa_debug_mode = 'Y' THEN
3440 pa_fp_gen_amount_utils.fp_debug
3441 (p_msg => 'Value of l_cal_etc_revenue_tab.count: '||l_cal_etc_revenue_tab.count,
3442 p_module_name => l_module_name,
3443 p_log_level => 5);
3444 END IF;
3445 IF p_pa_debug_mode = 'Y' THEN
3446 pa_fp_gen_amount_utils.fp_debug
3447 (p_msg => 'Value of l_cal_ra_id_tab.count: '||l_cal_ra_id_tab.count,
3448 p_module_name => l_module_name,
3449 p_log_level => 5);
3450 END IF;*/
3451 -- hr_utility.trace('==Before calling calculate api in the loop');
3452 -- hr_utility.trace('==l_cal_ra_id tab count bef calculate api '||l_cal_ra_id_tab.count);
3453
3454
3455 /* ER 4376722: When the Target is a Revenue-only Forecast, we do not
3456 * generate quantity or amounts for non-rate-based resources of
3457 * non-billable tasks. The simple algorithm to do this is as follows:
3458 *
3459 * 0. Clear out any data in the _tmp tables.
3460 * 1. Copy records for
3461 * a) billable tasks
3462 * b) rate-based resources of non-billable tasks
3463 * into _tmp tables.
3464 * 2. Copy records from _tmp tables back to non-temporary tables.
3465 *
3466 * The result is that we do not process records for non-rate-based
3467 * resources of non-billable task afterwards. Hence, quantity and
3468 * amounts for these resources will not be generated. */
3469
3470 IF l_fp_cols_rec_target.x_version_type = 'REVENUE' THEN
3471
3472 -- 0. Clear out any data in the _tmp tables.
3473 l_cal_src_ra_id_tab_tmp.delete;
3474 l_cal_ra_id_tab_tmp.delete;
3475 l_cal_txn_curr_code_tab_tmp.delete;
3476 l_cal_rate_based_flag_tab_tmp.delete;
3477 l_cal_rlm_id_tab_tmp.delete;
3478 l_cal_task_id_tab_tmp.delete;
3479 l_cal_unit_of_measure_tab_tmp.delete;
3480 l_cal_etc_method_code_tab_tmp.delete;
3481 l_cal_etc_qty_tab_tmp.delete;
3482 l_cal_etc_raw_cost_tab_tmp.delete;
3483 l_cal_etc_brdn_cost_tab_tmp.delete;
3484 l_cal_etc_revenue_tab_tmp.delete;
3485 l_billable_flag_tab_tmp.delete;
3486
3487 -- 1. Copy records for billable tasks into _tmp tables.
3488 l_tmp_index := 0;
3489 FOR i IN 1..l_cal_ra_id_tab.count LOOP
3490 IF l_billable_flag_tab(i) = 'Y' OR
3491 (l_billable_flag_tab(i) = 'N' AND
3492 l_cal_rate_based_flag_tab(i) = 'Y') THEN
3493
3494 l_cal_src_ra_id_tab_tmp.extend;
3495 l_cal_ra_id_tab_tmp.extend;
3496 l_cal_txn_curr_code_tab_tmp.extend;
3497 l_cal_rate_based_flag_tab_tmp.extend;
3498 l_cal_rlm_id_tab_tmp.extend;
3499 l_cal_task_id_tab_tmp.extend;
3500 l_cal_unit_of_measure_tab_tmp.extend;
3501 l_cal_etc_method_code_tab_tmp.extend;
3502 l_cal_etc_qty_tab_tmp.extend;
3503 l_cal_etc_raw_cost_tab_tmp.extend;
3504 l_cal_etc_brdn_cost_tab_tmp.extend;
3505 l_cal_etc_revenue_tab_tmp.extend;
3506 l_billable_flag_tab_tmp.extend;
3507
3508 l_tmp_index := l_tmp_index + 1;
3509 l_cal_src_ra_id_tab_tmp(l_tmp_index) := l_cal_src_ra_id_tab(i);
3510 l_cal_ra_id_tab_tmp(l_tmp_index) := l_cal_ra_id_tab(i);
3511 l_cal_txn_curr_code_tab_tmp(l_tmp_index) := l_cal_txn_currency_code_tab(i);
3512 l_cal_rate_based_flag_tab_tmp(l_tmp_index) := l_cal_rate_based_flag_tab(i);
3513 l_cal_rlm_id_tab_tmp(l_tmp_index) := l_cal_rlm_id_tab(i);
3514 l_cal_task_id_tab_tmp(l_tmp_index) := l_cal_task_id_tab(i);
3515 l_cal_unit_of_measure_tab_tmp(l_tmp_index) := l_cal_unit_of_measure_tab(i);
3516 l_cal_etc_method_code_tab_tmp(l_tmp_index) := l_cal_etc_method_code_tab(i);
3517 l_cal_etc_qty_tab_tmp(l_tmp_index) := l_cal_etc_qty_tab(i);
3518 l_cal_etc_raw_cost_tab_tmp(l_tmp_index) := l_cal_etc_raw_cost_tab(i);
3519 l_cal_etc_brdn_cost_tab_tmp(l_tmp_index) := l_cal_etc_burdened_cost_tab(i);
3520 l_cal_etc_revenue_tab_tmp(l_tmp_index) := l_cal_etc_revenue_tab(i);
3521 l_billable_flag_tab_tmp(l_tmp_index) := l_billable_flag_tab(i);
3522 END IF;
3523 END LOOP;
3524
3525 -- 2. Copy records from _tmp_ tables back to non-temporary tables.
3526 l_cal_src_ra_id_tab := l_cal_src_ra_id_tab_tmp;
3527 l_cal_ra_id_tab := l_cal_ra_id_tab_tmp;
3528 l_cal_txn_currency_code_tab := l_cal_txn_curr_code_tab_tmp;
3529 l_cal_rate_based_flag_tab := l_cal_rate_based_flag_tab_tmp;
3530 l_cal_rlm_id_tab := l_cal_rlm_id_tab_tmp;
3531 l_cal_task_id_tab := l_cal_task_id_tab_tmp;
3532 l_cal_unit_of_measure_tab := l_cal_unit_of_measure_tab_tmp;
3533 l_cal_etc_method_code_tab := l_cal_etc_method_code_tab_tmp;
3534 l_cal_etc_qty_tab := l_cal_etc_qty_tab_tmp;
3535 l_cal_etc_raw_cost_tab := l_cal_etc_raw_cost_tab_tmp;
3536 l_cal_etc_burdened_cost_tab := l_cal_etc_brdn_cost_tab_tmp;
3537 l_cal_etc_revenue_tab := l_cal_etc_revenue_tab_tmp;
3538 l_billable_flag_tab := l_billable_flag_tab_tmp;
3539
3540 END IF; -- ER 4376722 billability logic for REVENUE Forecast
3541
3542 -- Initialize l_remove_record_flag
3543 l_remove_records_flag := 'N';
3544
3545 -- Initialize l_remove_record_flag_tab
3546 FOR i IN 1..l_cal_ra_id_tab.count LOOP
3547 l_remove_record_flag_tab(i) := 'N';
3548 END LOOP;
3549
3550 -- Initialize l_source_version_type_tab
3551 -- Initialize l_rev_only_src_txn_flag_tab
3552 FOR i IN 1..l_cal_ra_id_tab.count LOOP
3553
3554 IF l_gen_etc_src_code_tab(i) = 'FINANCIAL_PLAN' THEN
3555 l_source_version_type_tab(i) := l_fp_cols_rec_fp.x_version_type;
3556 ELSIF l_gen_etc_src_code_tab(i) IN ('WORKPLAN_RESOURCES','WORK_QUANTITY') THEN
3557 l_source_version_type_tab(i) := l_fp_cols_rec_wp.x_version_type;
3558 ELSE -- l_gen_etc_src_code_tab(i) = 'NONE'
3559 l_source_version_type_tab(i) := null;
3560 END IF;
3561
3562 l_rev_only_src_txn_flag_tab(i) := 'N';
3563
3564 -- NOTE: Generation from Revenue-only versions not supported.
3565 -- This case has been added in case of future changes.
3566 IF l_source_version_type_tab(i) = 'REVENUE' OR
3567 ( l_source_version_type_tab(i) = 'ALL' AND
3568 nvl(l_cal_etc_raw_cost_tab(i),0) = 0 ) THEN
3569 l_rev_only_src_txn_flag_tab(i) := 'Y';
3570 END IF;
3571 END LOOP;
3572
3573 l_target_version_type := l_fp_cols_rec_target.x_version_type;
3574
3575 -- Added for IPM :
3576 -- This loop processes each planning txn, based on the source/target
3577 -- version type combination, and does the following:
3578 -- 1. Updates l_remove_record_flag_tab
3579
3580 FOR i in 1..l_cal_ra_id_tab.count LOOP
3581
3582 -- 1. Update l_remove_record_flag_tab based on source/target version types.
3583 IF l_source_version_type_tab(i) = 'ALL' AND l_target_version_type = 'COST' THEN
3584 IF l_cal_rate_based_flag_tab(i) = 'N' THEN
3585 IF l_rev_only_src_txn_flag_tab(i) = 'Y' THEN
3586 l_remove_record_flag_tab(i) := 'Y';
3587 l_remove_records_flag := 'Y';
3588 END IF;
3589 END IF;
3590 ELSIF l_source_version_type_tab(i) = 'ALL' AND
3591 l_target_version_type = 'REVENUE' AND
3592 l_rev_gen_method = 'T' THEN
3593 -- Set quantity to revenue in the main processing loop later.
3594 -- Do nothing for now.
3595 l_dummy := 1;
3596 ELSIF l_source_version_type_tab(i) = 'ALL' AND
3597 l_target_version_type = 'REVENUE' AND
3598 l_rev_gen_method = 'C' THEN
3599 IF l_cal_rate_based_flag_tab(i) = 'N' THEN
3600 IF l_rev_only_src_txn_flag_tab(i) = 'Y' THEN
3601 l_remove_record_flag_tab(i) := 'Y';
3602 l_remove_records_flag := 'Y';
3603 END IF;
3604 END IF;
3605 ELSIF l_source_version_type_tab(i) = 'ALL' AND
3606 l_target_version_type = 'REVENUE' AND
3607 l_rev_gen_method = 'E' THEN
3608 /*Revenue is only based on billing events, which is handled seperately*/
3609 l_dummy := 1;
3610 IF P_PA_DEBUG_MODE = 'Y' THEN
3611 PA_DEBUG.RESET_CURR_FUNCTION;
3612 END IF;
3613 RETURN;
3614 ELSIF l_source_version_type_tab(i) = 'ALL' AND
3615 l_target_version_type = 'ALL' AND
3616 l_rev_gen_method = 'T' THEN
3617 -- Set quantity to revenue in the main processing loop later.
3618 -- Do nothing for now.
3619 l_dummy := 1;
3620 ELSIF l_source_version_type_tab(i) = 'ALL' AND
3621 l_target_version_type = 'ALL' AND
3622 l_rev_gen_method = 'C' THEN
3623 IF l_cal_rate_based_flag_tab(i) = 'N' THEN
3624 IF l_rev_only_src_txn_flag_tab(i) = 'Y' THEN
3625 l_remove_record_flag_tab(i) := 'Y';
3626 l_remove_records_flag := 'Y';
3627 END IF;
3628 END IF;
3629 ELSIF l_source_version_type_tab(i) = 'ALL' AND
3630 l_target_version_type = 'ALL' AND
3631 l_rev_gen_method = 'E' THEN
3632 IF l_cal_rate_based_flag_tab(i) = 'N' THEN
3633 IF l_rev_only_src_txn_flag_tab(i) = 'Y' THEN
3634 l_remove_record_flag_tab(i) := 'Y';
3635 l_remove_records_flag := 'Y';
3636 END IF;
3637 END IF;
3638 END IF;
3639
3640 END LOOP; -- IPM filtering logic
3641
3642 -- Added for IPM : In a Cost and Revenue Together version,
3643 -- if a non-billable, non-rate-based planning txn has only
3644 -- ETC revenue amounts, then filter out the record as if the
3645 -- Target were a Revenue-only version.
3646
3647 IF l_target_version_type = 'ALL' THEN
3648 FOR i IN 1..l_cal_ra_id_tab.count LOOP
3649 IF l_billable_flag_tab(i) = 'N' AND
3650 l_rev_only_src_txn_flag_tab(i) = 'Y' AND
3651 l_cal_rate_based_flag_tab(i) = 'N' THEN
3652 l_remove_record_flag_tab(i) := 'Y';
3653 l_remove_records_flag := 'Y';
3654 END IF;
3655 END LOOP;
3656 END IF; -- Added billability logic for ALL versions in IPM
3657
3658 -- Added for IPM : Properly size pl/sql system tables
3659 -- outside of the loop so that skipping records using
3660 -- the continue_loop exception does not throw the loop
3661 -- iterator and table sizes out of sync.
3662
3663 IF l_cal_ra_id_tab.count > 0 THEN
3664 l_cal_rcost_rate_override_tab.extend(l_cal_ra_id_tab.count);
3665 l_cal_bcost_rate_override_tab.extend(l_cal_ra_id_tab.count);
3666 l_cal_bill_rate_override_tab.extend(l_cal_ra_id_tab.count);
3667 END IF;
3668
3669 FOR i in 1..l_cal_ra_id_tab.count LOOP
3670 BEGIN
3671
3672 -- Added in IPM:
3673 -- Before processing the current planning txn, check if the
3674 -- record should be skipped. If so, continue with next record.
3675 IF l_remove_record_flag_tab(i) = 'Y' THEN
3676 RAISE continue_loop;
3677 END IF;
3678
3679 -- Bug 4346172: In general, when source/target planning options match,
3680 -- we use the source version's periodic planning rates to calculate the
3681 -- amounts. However, when the ETC method is Earned Value, we honor the
3682 -- actuals rates. The l_use_src_rates_flag captures this information.
3683
3684 -- Bug 4369741: Extended IF condition logic to set l_use_src_rates_flag
3685 -- based on 2 separate planning options flags (for WP and FP) and the
3686 -- ETC generation source stored in l_gen_etc_src_code_tab.
3687
3688 IF l_cal_etc_method_code_tab(i) <> 'EARNED_VALUE' AND
3689 (( l_fp_planning_options_flag = 'Y' AND
3690 l_gen_etc_src_code_tab(i) = 'FINANCIAL_PLAN' ) OR
3691 ( l_wp_planning_options_flag = 'Y' AND
3692 l_gen_etc_src_code_tab(i) = 'WORKPLAN_RESOURCES' )) THEN
3693 l_use_src_rates_flag := 'Y';
3694 ELSE
3695 l_use_src_rates_flag := 'N';
3696 END IF;
3697
3698
3699 IF l_cal_rate_based_flag_tab(i) = 'N' THEN
3700 -- Bug 4232094, 4232253: For Cost-based Revenue generation, quantity should
3701 -- equal burdened cost. We make use of this modified l_cal_etc_qty_tab later
3702 -- for another part of the bug fix.
3703 IF l_fp_cols_rec_target.x_version_type = 'REVENUE' and l_rev_gen_method = 'C' THEN
3704 -- IPM : Earlier, the code checks if cost amounts exists and
3705 -- skips processing if they do not exist. Hence, the assignment
3706 -- below should only be reached when it makes sense to do so.
3707 l_cal_etc_qty_tab(i) := l_cal_etc_burdened_cost_tab(i);
3708 ELSE
3709 -- IPM : When only revenue amounts exist, quantity should equal revenue
3710 -- for non-rate-based transactions. Based on record filtering logic
3711 -- upstream, this should only happen under the following conditions:
3712 -- * Source is an 'ALL' version
3713 -- * Target is either a 'REVENUE' or 'ALL' version
3714 -- * Revenue accrual method is 'T' (work-based)
3715 IF l_rev_only_src_txn_flag_tab(i) = 'Y' THEN
3716 l_cal_etc_qty_tab(i) := l_cal_etc_revenue_tab(i);
3717 ELSE
3718 l_cal_etc_qty_tab(i) := l_cal_etc_raw_cost_tab(i);
3719 END IF;
3720
3721 END IF;
3722 END IF;
3723
3724 -- hr_utility.trace('==inside the loop for calling calcualte ');
3725
3726 -- Modified for IPM : Instead of extending the table size by 1 on
3727 -- each loop iteration, we now extend all at once before the loop.
3728 --l_cal_rcost_rate_override_tab.extend;
3729 --l_cal_bcost_rate_override_tab.extend;
3730
3731 -- ER 5726773: Instead of requiring l_cal_etc_qty_tab(i) be positive,
3732 -- relax the condition to ensure it is non-zero.
3733 IF l_cal_etc_qty_tab(i) <> 0 THEN
3734 l_cal_rcost_rate_override_tab(i) := l_cal_etc_raw_cost_tab(i)/l_cal_etc_qty_tab(i);
3735 l_cal_bcost_rate_override_tab(i) := l_cal_etc_burdened_cost_tab(i)/l_cal_etc_qty_tab(i);
3736 ELSE
3737 l_cal_rcost_rate_override_tab(i) := NULL;
3738 l_cal_bcost_rate_override_tab(i) := NULL;
3739 END IF;
3740 -- Modified for IPM : Instead of extending the table size by 1 on
3741 -- each loop iteration, we now extend all at once before the loop.
3742 --l_cal_bill_rate_override_tab.extend;
3743 l_cal_bill_rate_override_tab(i) := NULL;
3744 IF l_fp_cols_rec_target.x_version_type = 'REVENUE' and l_rev_gen_method = 'C' THEN
3745 l_cal_bill_rate_override_tab(i) := l_cal_bcost_rate_override_tab(i);
3746 l_cal_etc_raw_cost_tab(i) := NULL;
3747 l_cal_etc_burdened_cost_tab(i) := NULL;
3748 l_cal_rcost_rate_override_tab(i) := NULL;
3749 l_cal_bcost_rate_override_tab(i) := NULL;
3750 END IF;
3751
3752 -- Added in IPM :
3753 -- For the case given below, set bill rate override to 1
3754 -- so that revenue will equal quantity (and not be rederived).
3755 -- If the target is an ALL version, then also set the
3756 -- cost rate overrides to 0 so the Calculate API does not
3757 -- default them to 1 and then compute cost amounts.
3758
3759 IF l_rev_only_src_txn_flag_tab(i) = 'Y' AND
3760 l_cal_rate_based_flag_tab(i) = 'N' AND
3761 l_target_version_type IN ('REVENUE','ALL') AND
3762 l_rev_gen_method = 'T' THEN
3763 l_cal_bill_rate_override_tab(i) := 1;
3764 IF l_target_version_type = 'ALL' THEN
3765 l_cal_rcost_rate_override_tab(i) := 0;
3766 l_cal_bcost_rate_override_tab(i) := 0;
3767 END IF;
3768 END IF;
3769
3770
3771 -- Bug 4216423: We now need to populate PA_FP_GEN_RATE_TMP with cost
3772 -- rates for both rate-based and non-rate based resources when generating
3773 -- work-based revenue for a Revenue-only target version.
3774
3775 -- Added in IPM : When generating revenue-only, non-rate-based txns
3776 -- for ALL versions with work-based revenue accrual, do not use periodic
3777 -- source rates. Instead, populate the temp table with bill rate
3778 -- override as 1 and cost rate overrides as 0.
3779
3780 IF l_use_src_rates_flag = 'Y' AND
3781 NOT ( p_fp_cols_rec.x_version_type = 'REVENUE' AND l_rev_gen_method = 'T' ) AND
3782 NOT ( p_fp_cols_rec.x_version_type = 'ALL' AND l_rev_gen_method = 'T' AND
3783 l_rev_only_src_txn_flag_tab(i) = 'Y' AND l_cal_rate_based_flag_tab(i) = 'N') THEN
3784
3785 -- hr_utility.trace('==inside plan option same flag = Y ');
3786 IF P_PA_DEBUG_MODE = 'Y' THEN
3787 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
3788 P_CALLED_MODE => P_CALLED_MODE,
3789 P_MSG =>
3790 'Before calling PA_FP_GEN_FCST_AMT_PUB1.POPULATE_GEN_RATE',
3791 P_MODULE_NAME => l_module_name);
3792 END IF;
3793 PA_FP_GEN_FCST_AMT_PUB1.POPULATE_GEN_RATE
3794 (P_SOURCE_RES_ASG_ID => l_cal_src_ra_id_tab(i),
3795 P_TARGET_RES_ASG_ID => l_cal_ra_id_tab(i),
3796 P_TXN_CURRENCY_CODE => l_cal_txn_currency_code_tab(i),
3797 X_RETURN_STATUS => x_return_status,
3798 X_MSG_COUNT => x_msg_count,
3799 X_MSG_DATA => x_msg_data);
3800 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
3801 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3802 END IF;
3803 IF P_PA_DEBUG_MODE = 'Y' THEN
3804 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
3805 P_CALLED_MODE => P_CALLED_MODE,
3806 P_MSG =>
3807 'After calling PA_FP_GEN_FCST_AMT_PUB1.POPULATE_GEN_RATE: '||x_return_status,
3808 P_MODULE_NAME => l_module_name);
3809 END IF;
3810
3811 /* Populate the period rates table for the Client Extension API */
3812
3813 -- Bug 4320954: When we fetch data from and update data to the
3814 -- PA_FP_GEN_RATE_TMP table for the Client Extension API, we need
3815 -- to check both the target_res_asg_id AND the TXN_CURRENCY_CODE
3816 -- in the WHERE clause so that the plan currency is honored.
3817
3818 SELECT /*+ INDEX(PA_FP_GEN_RATE_TMP,PA_FP_GEN_RATE_TMP_N1)*/
3819 period_name,
3820 raw_cost_rate,
3821 burdened_cost_rate,
3822 revenue_bill_rate
3823 BULK COLLECT
3824 INTO l_ext_period_name_tab,
3825 l_ext_raw_cost_rate_tab,
3826 l_ext_burdened_cost_rate_tab,
3827 l_ext_revenue_bill_rate_tab
3828 FROM pa_fp_gen_rate_tmp
3829 WHERE target_res_asg_id = l_cal_ra_id_tab(i)
3830 AND txn_currency_code = l_cal_txn_currency_code_tab(i);
3831
3832 l_input_period_rates_tbl.delete;
3833 FOR j IN 1..l_ext_period_name_tab.count LOOP
3834 l_input_period_rates_tbl(j).period_name := l_ext_period_name_tab(j);
3835 l_input_period_rates_tbl(j).raw_cost_rate := l_ext_raw_cost_rate_tab(j);
3836 l_input_period_rates_tbl(j).burdened_cost_rate := l_ext_burdened_cost_rate_tab(j);
3837 l_input_period_rates_tbl(j).revenue_bill_rate := l_ext_revenue_bill_rate_tab(j);
3838 END LOOP;
3839 ELSIF ( p_fp_cols_rec.x_version_type = 'REVENUE' AND l_rev_gen_method = 'T' ) THEN
3840 /* Populate the period rates table for the Client Extension API */
3841 l_input_period_rates_tbl.delete;
3842 l_input_period_rates_tbl(1).raw_cost_rate := l_cal_rcost_rate_override_tab(i);
3843 l_input_period_rates_tbl(1).burdened_cost_rate := l_cal_bcost_rate_override_tab(i);
3844
3845 IF l_rev_only_src_txn_flag_tab(i) = 'Y' AND
3846 l_cal_rate_based_flag_tab(i) = 'N' THEN
3847 l_input_period_rates_tbl(1).revenue_bill_rate := l_cal_bill_rate_override_tab(i);
3848 END IF;
3849
3850 -- Added in IPM : This handles a corner case.
3851 ELSIF ( p_fp_cols_rec.x_version_type = 'ALL' AND l_rev_gen_method = 'T' AND
3852 l_rev_only_src_txn_flag_tab(i) = 'Y' AND l_cal_rate_based_flag_tab(i) = 'N') THEN
3853
3854 /* Populate the period rates table for the Client Extension API */
3855 l_input_period_rates_tbl.delete;
3856 l_input_period_rates_tbl(1).raw_cost_rate := l_cal_rcost_rate_override_tab(i);
3857 l_input_period_rates_tbl(1).burdened_cost_rate := l_cal_bcost_rate_override_tab(i);
3858 l_input_period_rates_tbl(1).revenue_bill_rate := l_cal_bill_rate_override_tab(i);
3859
3860 END IF;
3861
3862
3863 /* ER 4376722: When the Target is a Revenue-only Forecast, we
3864 * generate quantity but not revenue for rate-based resources of
3865 * non-billable tasks. To do this, null out revenue amounts,
3866 * overrides, and possible periodic rates for rate-based
3867 * resources of non-billable tasks.
3868 * Note that we handle the case of non-rated-based resources
3869 * of non-billable tasks earlier in the code. */
3870
3871 IF l_fp_cols_rec_target.x_version_type = 'REVENUE' THEN
3872 -- Null out revenue amounts for non-billable tasks
3873 IF l_billable_flag_tab(i) = 'N' AND
3874 l_cal_rate_based_flag_tab(i) = 'Y' THEN
3875
3876 l_cal_etc_revenue_tab(i) := NULL;
3877 l_cal_bill_rate_override_tab(i) := NULL;
3878
3879 FOR j IN 1..l_input_period_rates_tbl.count LOOP
3880 -- null out cost rates in case of Work-based revenue
3881 l_input_period_rates_tbl(j).raw_cost_rate := NULL;
3882 l_input_period_rates_tbl(j).burdened_cost_rate := NULL;
3883 l_input_period_rates_tbl(j).revenue_bill_rate := NULL;
3884 END LOOP;
3885 END IF;
3886 END IF; -- ER 4376722 billability logic for REVENUE Forecast
3887
3888
3889 /* ER 4376722: When the Target is a Cost and Revenue together
3890 * version, we do not generate revenue for non-billable tasks.
3891 * To do this, null out revenue amounts, overrides, and possible
3892 * periodic rates for non-billable tasks. Since we call the
3893 * Client Extension, it's possible for revenue to be overriden
3894 * for non-billable tasks. */
3895
3896 IF l_fp_cols_rec_target.x_version_type = 'ALL' THEN
3897 -- Null out revenue amounts for non-billable tasks
3898 IF l_billable_flag_tab(i) = 'N' THEN
3899
3900 l_cal_etc_revenue_tab(i) := NULL;
3901 l_cal_bill_rate_override_tab(i) := NULL;
3902
3903 FOR j IN 1..l_input_period_rates_tbl.count LOOP
3904 l_input_period_rates_tbl(j).revenue_bill_rate := NULL;
3905 END LOOP;
3906 END IF;
3907 END IF; -- ER 4376722 billability logic for ALL versions
3908
3909
3910 IF P_PA_DEBUG_MODE = 'Y' THEN
3911 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
3912 P_CALLED_MODE => P_CALLED_MODE,
3913 P_MSG =>
3914 'Before calling pa_fp_fcst_gen_client_ext.fcst_gen_client_extn',
3915 P_MODULE_NAME => l_module_name);
3916 END IF;
3917 /*For client_extn, many attributes are passed as NULL is because
3918 this API is still under developmen and some parameters are not
3919 available.Null values will be replaced later*/
3920 /* Call PA_FP_GEN_FCST_AMT_PUB1.POPULATE_GEN_RATE to populate
3921 pa_fp_gen_rate_tmp, which is used to populate input parameter
3922 P_PERIOD_RATES_TBL */
3923
3924 -- hr_utility.trace('==before calling client extn api ');
3925
3926 --This code has been commented due to bug 5726785 where the client extension
3927 --call has been made be passing the existing and also the periodic level amounts.
3928
3929 /* PA_FP_FCST_GEN_CLIENT_EXT.FCST_GEN_CLIENT_EXTN
3930 (P_PROJECT_ID => P_PROJECT_ID,
3931 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
3932 P_RESOURCE_ASSIGNMENT_ID => l_cal_ra_id_tab(i),
3933 P_TASK_ID => l_cal_task_id_tab(i),
3934 P_TASK_PERCENT_COMPLETE => NULL,
3935 P_PROJECT_PERCENT_COMPLETE => NULL,
3936 P_RESOURCE_LIST_MEMBER_ID => l_cal_rlm_id_tab(i),
3937 P_UNIT_OF_MEASURE => l_cal_unit_of_measure_tab(i), -- NEW PARAM
3938 P_TXN_CURRENCY_CODE => l_cal_txn_currency_code_tab(i),
3939 P_ETC_QTY => l_cal_etc_qty_tab(i),
3940 P_ETC_RAW_COST => l_cal_etc_raw_cost_tab(i),
3941 P_ETC_BURDENED_COST => l_cal_etc_burdened_cost_tab(i),
3942 P_ETC_REVENUE => l_cal_etc_revenue_tab(i),
3943 P_ETC_SOURCE => NULL,
3944 P_ETC_GEN_METHOD => l_cal_etc_method_code_tab(i),
3945 P_ACTUAL_THRU_DATE => P_ACTUALS_THRU_DATE,
3946 P_ETC_START_DATE => P_ACTUALS_THRU_DATE+1,
3947 P_ETC_END_DATE => NULL,
3948 P_PLANNED_WORK_QTY => NULL,
3949 P_ACTUAL_WORK_QTY => NULL,
3950 P_ACTUAL_QTY => NULL,
3951 P_ACTUAL_RAW_COST => NULL,
3952 P_ACTUAL_BURDENED_COST => NULL,
3953 P_ACTUAL_REVENUE => NULL,
3954 P_PERIOD_RATES_TBL => l_input_period_rates_tbl, -- NEW PARAM
3955 X_ETC_QTY => l_fcst_etc_qty,
3956 X_ETC_RAW_COST => l_fcst_etc_raw_cost,
3957 X_ETC_BURDENED_COST => l_fcst_etc_burdened_cost,
3958 X_ETC_REVENUE => l_fcst_etc_revenue,
3959 X_PERIOD_RATES_TBL => l_period_rates_tbl, -- NEW PARAM
3960 X_RETURN_STATUS => x_return_status,
3961 X_MSG_DATA => x_msg_data,
3962 X_MSG_COUNT => x_msg_count);
3963
3964 -- hr_utility.trace('==after calling client extn api ');
3965
3966 IF P_PA_DEBUG_MODE = 'Y' THEN
3967 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
3968 P_CALLED_MODE => P_CALLED_MODE,
3969 P_MSG =>
3970 'After calling pa_fp_fcst_gen_client_ext.fcst_gen_client_extn: '
3971 ||x_return_status,
3972 P_MODULE_NAME => l_module_name);
3973 END IF;
3974 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
3975 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3976 END IF; */
3977
3978 -- Code in client extn kept here to avoid further changes in code after client extn.
3979 /* bug fix 5726773 : commenting out unecessary client-extension related code */
3980 /*
3981 l_fcst_etc_qty := l_cal_etc_qty_tab(i);
3982 l_fcst_etc_raw_cost := l_cal_etc_raw_cost_tab(i);
3983 l_fcst_etc_burdened_cost := l_cal_etc_burdened_cost_tab(i);
3984 l_fcst_etc_revenue := l_cal_etc_revenue_tab(i);
3985
3986 IF l_input_period_rates_tbl.count > 0 and
3987 l_period_rates_tbl.count = 0 THEN
3988 FOR j IN 1..l_input_period_rates_tbl.count LOOP
3989 l_period_rates_tbl(j).period_name := l_input_period_rates_tbl(j).period_name;
3990 l_period_rates_tbl(j).raw_cost_rate := l_input_period_rates_tbl(j).raw_cost_rate;
3991 l_period_rates_tbl(j).burdened_cost_rate := l_input_period_rates_tbl(j).burdened_cost_rate;
3992 l_period_rates_tbl(j).revenue_bill_rate := l_input_period_rates_tbl(j).revenue_bill_rate;
3993 END LOOP;
3994 END IF;
3995 */
3996 /* end bug fix 5726773 */
3997 -- Code in client extn kept here to avoid further changes in code after client extn.
3998
3999 -- Bug 3968748: We need to populate the PA_FP_GEN_RATE_TMP table with
4000 -- burdened cost rates for non-rate-based resources for Calculate API
4001 -- when generating work-based revenue for a Revenue-only target version.
4002
4003 -- Bug 4216423: We now need to populate PA_FP_GEN_RATE_TMP with cost
4004 -- rates for both rate-based and non-rate based resources when generating
4005 -- work-based revenue for a Revenue-only target version.
4006
4007 IF l_use_src_rates_flag = 'Y' OR
4008 ( p_fp_cols_rec.x_version_type = 'REVENUE' AND l_rev_gen_method = 'T' ) OR
4009 -- Added Condition for IPM
4010 ( p_fp_cols_rec.x_version_type = 'ALL' AND l_rev_gen_method = 'T' AND
4011 l_rev_only_src_txn_flag_tab(i) = 'Y' AND l_cal_rate_based_flag_tab(i) = 'N') THEN
4012
4013 -- Bug 4320954: When we fetch data from and update data to the
4014 -- PA_FP_GEN_RATE_TMP table for the Client Extension API, we need
4015 -- to check both the target_res_asg_id AND the TXN_CURRENCY_CODE
4016 -- in the WHERE clause so that the plan currency is honored.
4017
4018 DELETE /*+ INDEX(PA_FP_GEN_RATE_TMP,PA_FP_GEN_RATE_TMP_N1)*/
4019 FROM pa_fp_gen_rate_tmp
4020 WHERE target_res_asg_id = l_cal_ra_id_tab(i)
4021 AND txn_currency_code = l_cal_txn_currency_code_tab(i);
4022
4023 l_ext_period_name_tab.delete;
4024 l_ext_raw_cost_rate_tab.delete;
4025 l_ext_burdened_cost_rate_tab.delete;
4026 l_ext_revenue_bill_rate_tab.delete;
4027
4028 /* bug fix 5726773: Use l_input_period_rates_tbl instead of l_period_rates_tbl */
4029 FOR j IN 1..l_input_period_rates_tbl.count LOOP
4030 l_ext_period_name_tab(j) := l_input_period_rates_tbl(j).period_name;
4031 l_ext_raw_cost_rate_tab(j) := l_input_period_rates_tbl(j).raw_cost_rate;
4032 l_ext_burdened_cost_rate_tab(j) := l_input_period_rates_tbl(j).burdened_cost_rate;
4033 l_ext_revenue_bill_rate_tab(j) := l_input_period_rates_tbl(j).revenue_bill_rate;
4034 END LOOP;
4035
4036 FORALL j IN 1..l_ext_period_name_tab.count
4037 INSERT INTO PA_FP_GEN_RATE_TMP
4038 ( SOURCE_RES_ASG_ID,
4039 TXN_CURRENCY_CODE,
4040 PERIOD_NAME,
4041 RAW_COST_RATE,
4042 BURDENED_COST_RATE,
4043 REVENUE_BILL_RATE,
4044 TARGET_RES_ASG_ID )
4045 VALUES
4046 ( l_cal_src_ra_id_tab(i),
4047 l_cal_txn_currency_code_tab(i),
4048 l_ext_period_name_tab(j),
4049 l_ext_raw_cost_rate_tab(j),
4050 l_ext_burdened_cost_rate_tab(j),
4051 l_ext_revenue_bill_rate_tab(j),
4052 l_cal_ra_id_tab(i) );
4053 END IF;
4054
4055 /* hr_utility.trace('==etc qty aft client extn api '||l_fcst_etc_qty);
4056 hr_utility.trace('==etc cost aft client extn api '||l_fcst_etc_raw_cost);
4057 hr_utility.trace('==etc bd cost aft client extn api '||l_fcst_etc_burdened_cost);
4058 hr_utility.trace('==etc rev aft client extn api '||l_fcst_etc_revenue); */
4059
4060 /* bug fix 5726773 : commenting out unecessary client-extension related code */
4061 /*
4062 l_cal_etc_qty_tab(i) := l_fcst_etc_qty;
4063 l_cal_etc_raw_cost_tab(i) := l_fcst_etc_raw_cost;
4064 l_cal_etc_burdened_cost_tab(i) := l_fcst_etc_burdened_cost;
4065 l_cal_etc_revenue_tab(i) := l_fcst_etc_revenue;
4066 */
4067 /* end bug fix 5726773 */
4068
4069 IF (l_use_src_rates_flag = 'Y' OR
4070 (p_fp_cols_rec.x_version_type = 'REVENUE' AND l_rev_gen_method = 'T'))
4071 AND l_cal_rate_based_flag_tab(i) = 'Y' THEN
4072 l_cal_etc_raw_cost_tab(i) := NULL;
4073 l_cal_etc_burdened_cost_tab(i) := NULL;
4074 l_cal_etc_revenue_tab(i) := NULL;
4075
4076 l_cal_rcost_rate_override_tab(i) := NULL;
4077 l_cal_bcost_rate_override_tab(i) := NULL;
4078 END IF;
4079
4080 /*Bug fix: 4258968: for none rate based resources, bill markup should be applied
4081 on top of raw cost, not revenue. So,nullify revenue here*/
4082
4083 IF (p_fp_cols_rec.x_version_type = 'REVENUE' OR p_fp_cols_rec.x_version_type = 'ALL')
4084 AND l_rev_gen_method = 'T'
4085 AND l_cal_rate_based_flag_tab(i) = 'N' THEN
4086 l_cal_etc_revenue_tab(i) := NULL;
4087 l_cal_bill_rate_override_tab(i) := NULL;
4088 END IF;
4089
4090 /* Start bug 3826548
4091 The following code has been added as a short term fix only.
4092 The Calculate API expects the total amounts for populating the
4093 budget lines for the ETC periods. Inside the Calculate API, they
4094 derived the actual amounts and subtract it from the passed total
4095 amounts. This fix is being patched for only testing the forecast
4096 generation process with the following assumptions.
4097
4098 1. There is going to be only one txn currency for the actual txn
4099 for the planning resource. - Actual amt currency.
4100 2. ETC source - Work Plan/Financial Plan - The Plan amount for the
4101 planning resource should be planned in only one txn currency.
4102 (For Work plan, it is always going to be only one txn currency.
4103 But, this may change in the future.) - Plan amt currency.
4104 3. The Actual amt currency and the Plan amt currency should be same.
4105
4106 The above fix will not cover all the cases. If we are going to have
4107 actual txn amounts in multiple currencies or if the ETC source total
4108 plan amount is going to be planned in multiple currencies, we will
4109 generate the ETC amount in Project Currency. In this case, if we have
4110 three different actual txn currencies and one planning currency for the
4111 planning currency (which is different from the actual txn currencies),
4112 then the actual amount cannot be derived from the budget lines for the
4113 target version. B/c, the Calculate/Spread API gets the actual amounts
4114 based on the txn currency passed from the fcst gen code. In this case,
4115 we wont get any amounts.
4116
4117 The fcst gen process will not give better performance results. B/c,
4118 from the fcst gen process, we are going to add the actual amount by
4119 reading the budget line data. Then, the Calculate/Spread API is going
4120 to select the same data and subtract the amounts from the total amount.
4121
4122 Permanent Fix : The 'Calculate/Spread' API should spread only the amount
4123 passed from the fcst generation process and should not manipulate the
4124 data in any way. We have the p_calling_module parameter and this
4125 parameter should be used to avoid any manipulation to the passed data.
4126
4127 If we go with the above strategy then the following code to select the
4128 actual amount (and adding the actual amount to the ETC amounts) should be
4129 removed and the changes should be made in the
4130 Calculate API/Spread API.
4131
4132 If we are going to address this issue using a different strategy then
4133 the code changes should be made in Fcst gen/Calculate/Spread API.
4134
4135 End bug 3826548
4136 */
4137
4138 /* bug fix start */
4139
4140 -- Bug 4211776, 4194849: Commented out logic for addition of actuals.
4141 /*
4142 SELECT sum(init_quantity),
4143 sum(txn_init_raw_cost),
4144 sum(txn_init_burdened_cost),
4145 sum(txn_init_revenue)
4146 INTO l_init_qty,
4147 l_init_raw_cost,
4148 l_init_burdened_cost,
4149 l_init_revenue
4150 FROM pa_budget_lines
4151 WHERE resource_assignment_id = l_cal_ra_id_tab(i)
4152 AND txn_currency_code = l_cal_txn_currency_code_tab(i);
4153
4154 l_cal_etc_qty_tab(i) := NVL(l_cal_etc_qty_tab(i),0) +
4155 NVL(l_init_qty,0);
4156
4157 IF p_fp_cols_rec.x_version_type = 'COST' THEN
4158 l_cal_etc_raw_cost_tab(i) := NVL(l_cal_etc_raw_cost_tab(i),0) +
4159 NVL(l_init_raw_cost,0);
4160 l_cal_etc_burdened_cost_tab(i) := NVL(l_cal_etc_burdened_cost_tab(i),0) +
4161 NVL(l_init_burdened_cost,0);
4162 ELSIF p_fp_cols_rec.x_version_type = 'REVENUE' THEN
4163 l_cal_etc_revenue_tab(i) := NVL(l_cal_etc_revenue_tab(i),0) +
4164 NVL(l_init_revenue,0);
4165 ELSIF p_fp_cols_rec.x_version_type = 'ALL' THEN
4166 l_cal_etc_raw_cost_tab(i) := NVL(l_cal_etc_raw_cost_tab(i),0) +
4167 NVL(l_init_raw_cost,0);
4168 l_cal_etc_burdened_cost_tab(i) := NVL(l_cal_etc_burdened_cost_tab(i),0) +
4169 NVL(l_init_burdened_cost,0);
4170 l_cal_etc_revenue_tab(i) := NVL(l_cal_etc_revenue_tab(i),0) +
4171 NVL(l_init_revenue,0);
4172 END IF;
4173 */
4174
4175 /* bug fix end */
4176 --hr_utility.trace('==Before calculate, l_cal_ra_id_tab('||i||'):'||l_cal_ra_id_tab(i));
4177 --hr_utility.trace('==Before calculate, l_cal_txn_currency_code_tab('||i||'):'||l_cal_txn_currency_code_tab(i));
4178 --hr_utility.trace('==Before calculate, l_cal_etc_qty_tab('||i||'):'||l_cal_etc_qty_tab(i));
4179 --hr_utility.trace('==Before calculate, l_cal_etc_raw_cost_tab('||i||'):'||l_cal_etc_raw_cost_tab(i));
4180 --hr_utility.trace('==Before calculate, l_cal_etc_burdened_cost_tab('||i||'):'||l_cal_etc_burdened_cost_tab(i));
4181 --hr_utility.trace('==Before calculate, l_cal_etc_revenue_tab('||i||'):'||l_cal_etc_revenue_tab(i));
4182 --hr_utility.trace('==Before calculate, l_cal_rcost_rate_override_tab('||i||'):'||l_cal_rcost_rate_override_tab(i));
4183 --hr_utility.trace('==Before calculate, l_cal_bcost_rate_override_tab('||i||'):'||l_cal_bcost_rate_override_tab(i));
4184 --hr_utility.trace('==Before calculate, l_cal_bill_rate_override_tab('||i||'):'||l_cal_bill_rate_override_tab(i));
4185 -- dbms_output.put_line('==Before calculate, l_cal_ra_id_tab('||i||'):'||l_cal_ra_id_tab(i));
4186 --dbms_output.put_line('==Before calculate, l_cal_txn_currency_code_tab('||i||'):'||l_cal_txn_currency_code_tab(i));
4187 --dbms_output.put_line('==Before calculate, l_cal_etc_qty_tab('||i||'):'||l_cal_etc_qty_tab(i));
4188 --dbms_output.put_line('==Before calculate, l_cal_etc_raw_cost_tab('||i||'):'||l_cal_etc_raw_cost_tab(i));
4189 --dbms_output.put_line('==Before calculate, l_cal_etc_burdened_cost_tab('||i||'):'||l_cal_etc_burdened_cost_tab(i));
4190
4191 -- Added in IPM: The continue_loop Exception is used within the
4192 -- loop to skip further processing of the current planning txn.
4193 EXCEPTION
4194 WHEN CONTINUE_LOOP THEN
4195 l_dummy := 1;
4196 WHEN OTHERS THEN
4197 RAISE;
4198 END;
4199 END LOOP;
4200
4201
4202 -- Added in IPM. If there are any pl/sql table records that need to
4203 -- be removed, use a separate set of _tmp_ tables to filter them out.
4204
4205 IF l_remove_records_flag = 'Y' THEN
4206
4207 -- 0. Clear out any data in the _tmp_ tables.
4208 l_cal_src_ra_id_tab_tmp.delete;
4209 l_cal_ra_id_tab_tmp.delete;
4210 l_cal_txn_curr_code_tab_tmp.delete;
4211 l_cal_rate_based_flag_tab_tmp.delete;
4212 l_cal_rlm_id_tab_tmp.delete;
4213 l_cal_task_id_tab_tmp.delete;
4214 l_cal_unit_of_measure_tab_tmp.delete;
4215 l_cal_etc_method_code_tab_tmp.delete;
4216 l_cal_etc_qty_tab_tmp.delete;
4217 l_cal_etc_raw_cost_tab_tmp.delete;
4218 l_cal_etc_brdn_cost_tab_tmp.delete;
4219 l_cal_etc_revenue_tab_tmp.delete;
4220 l_billable_flag_tab_tmp.delete;
4221 l_cal_rcost_rate_ovrd_tab_tmp.delete;
4222 l_cal_bcost_rate_ovrd_tab_tmp.delete;
4223 l_cal_bill_rate_ovrd_tab_tmp.delete;
4224
4225 -- 1. Copy records into _tmp_ tables
4226 l_index := 1;
4227 FOR i in 1..l_cal_ra_id_tab.count LOOP
4228 IF l_remove_record_flag_tab(i) <> 'Y' THEN
4229
4230 l_cal_src_ra_id_tab_tmp.extend;
4231 l_cal_ra_id_tab_tmp.extend;
4232 l_cal_txn_curr_code_tab_tmp.extend;
4233 l_cal_rate_based_flag_tab_tmp.extend;
4234 l_cal_rlm_id_tab_tmp.extend;
4235 l_cal_task_id_tab_tmp.extend;
4236 l_cal_unit_of_measure_tab_tmp.extend;
4237 l_cal_etc_method_code_tab_tmp.extend;
4238 l_cal_etc_qty_tab_tmp.extend;
4239 l_cal_etc_raw_cost_tab_tmp.extend;
4240 l_cal_etc_brdn_cost_tab_tmp.extend;
4241 l_cal_etc_revenue_tab_tmp.extend;
4242 l_billable_flag_tab_tmp.extend;
4243 l_cal_rcost_rate_ovrd_tab_tmp.extend;
4244 l_cal_bcost_rate_ovrd_tab_tmp.extend;
4245 l_cal_bill_rate_ovrd_tab_tmp.extend;
4246
4247 l_cal_src_ra_id_tab_tmp(l_index) := l_cal_src_ra_id_tab(i);
4248 l_cal_ra_id_tab_tmp(l_index) := l_cal_ra_id_tab(i);
4249 l_cal_txn_curr_code_tab_tmp(l_index) := l_cal_txn_currency_code_tab(i);
4250 l_cal_rate_based_flag_tab_tmp(l_index) := l_cal_rate_based_flag_tab(i);
4251 l_cal_rlm_id_tab_tmp(l_index) := l_cal_rlm_id_tab(i);
4252 l_cal_task_id_tab_tmp(l_index) := l_cal_task_id_tab(i);
4253 l_cal_unit_of_measure_tab_tmp(l_index) := l_cal_unit_of_measure_tab(i);
4254 l_cal_etc_method_code_tab_tmp(l_index) := l_cal_etc_method_code_tab(i);
4255 l_cal_etc_qty_tab_tmp(l_index) := l_cal_etc_qty_tab(i);
4256 l_cal_etc_raw_cost_tab_tmp(l_index) := l_cal_etc_raw_cost_tab(i);
4257 l_cal_etc_brdn_cost_tab_tmp(l_index) := l_cal_etc_burdened_cost_tab(i);
4258 l_cal_etc_revenue_tab_tmp(l_index) := l_cal_etc_revenue_tab(i);
4259 l_billable_flag_tab_tmp(l_index) := l_billable_flag_tab(i);
4260 l_cal_rcost_rate_ovrd_tab_tmp(l_index) := l_cal_rcost_rate_override_tab(i);
4261 l_cal_bcost_rate_ovrd_tab_tmp(l_index) := l_cal_bcost_rate_override_tab(i);
4262 l_cal_bill_rate_ovrd_tab_tmp(l_index) := l_cal_bill_rate_override_tab(i);
4263
4264 l_index := l_index + 1;
4265 END IF;
4266 END LOOP;
4267
4268 -- 2. Copy records from _tmp_ tables back to non-temporary tables.
4269 l_cal_src_ra_id_tab := l_cal_src_ra_id_tab_tmp;
4270 l_cal_ra_id_tab := l_cal_ra_id_tab_tmp;
4271 l_cal_txn_currency_code_tab := l_cal_txn_curr_code_tab_tmp;
4272 l_cal_rate_based_flag_tab := l_cal_rate_based_flag_tab_tmp;
4273 l_cal_rlm_id_tab := l_cal_rlm_id_tab_tmp;
4274 l_cal_task_id_tab := l_cal_task_id_tab_tmp;
4275 l_cal_unit_of_measure_tab := l_cal_unit_of_measure_tab_tmp;
4276 l_cal_etc_method_code_tab := l_cal_etc_method_code_tab_tmp;
4277 l_cal_etc_qty_tab := l_cal_etc_qty_tab_tmp;
4278 l_cal_etc_raw_cost_tab := l_cal_etc_raw_cost_tab_tmp;
4279 l_cal_etc_burdened_cost_tab := l_cal_etc_brdn_cost_tab_tmp;
4280 l_cal_etc_revenue_tab := l_cal_etc_revenue_tab_tmp;
4281 l_billable_flag_tab := l_billable_flag_tab_tmp;
4282 l_cal_rcost_rate_override_tab := l_cal_rcost_rate_ovrd_tab_tmp;
4283 l_cal_bcost_rate_override_tab := l_cal_bcost_rate_ovrd_tab_tmp;
4284 l_cal_bill_rate_override_tab := l_cal_bill_rate_ovrd_tab_tmp;
4285
4286 END IF; -- IF l_remove_records_flag = 'Y' THEN
4287
4288 -- End IPM filtering logic.
4289
4290 /*********************************************************************
4291 ER 5726773: Commenting out logic that filters out planning
4292 transaction records with: (total plan quantity <= 0).
4293
4294 -- Bug 4654157 and 4670253 : Before calling the Calculate API, we should ensure that only
4295 -- target resources with Total Plan Quantity >= 0 are passed. Total Plan Quantity
4296 -- is the sum of Actual Quantity (from target budget lines) + ETC Quantity.
4297
4298 l_index := 1;
4299
4300 l_cal_ra_id_tab_tmp.delete;
4301 l_cal_txn_curr_code_tab_tmp.delete;
4302 l_cal_rate_based_flag_tab_tmp.delete;
4303 l_cal_rlm_id_tab_tmp.delete;
4304 l_cal_task_id_tab_tmp.delete;
4305 l_cal_unit_of_measure_tab_tmp.delete;
4306 l_cal_etc_method_code_tab_tmp.delete;
4307 l_cal_etc_qty_tab_tmp.delete;
4308 l_cal_etc_raw_cost_tab_tmp.delete;
4309 l_cal_etc_brdn_cost_tab_tmp.delete;
4310 l_cal_etc_revenue_tab_tmp.delete;
4311 l_cal_rcost_rate_ovrd_tab_tmp.delete;
4312 l_cal_bcost_rate_ovrd_tab_tmp.delete;
4313 l_cal_bill_rate_ovrd_tab_tmp.delete;
4314
4315 FOR i in 1..l_cal_ra_id_tab.count LOOP
4316 -- Bug 4670253: Added NVL around actual quantity sum to
4317 -- ensure that l_init_qty is not null.
4318 BEGIN
4319 SELECT nvl(sum(nvl(init_quantity,0)),0)
4320 INTO l_init_qty
4321 FROM pa_budget_lines
4322 WHERE resource_assignment_id = l_cal_ra_id_tab(i)
4323 AND txn_currency_code = l_cal_txn_currency_code_tab(i);
4324 EXCEPTION
4325 WHEN NO_DATA_FOUND THEN
4326 l_init_qty := 0;
4327 END;
4328
4329 l_total_plan_qty := l_init_qty + nvl(l_cal_etc_qty_tab(i),0);
4330
4331 IF nvl(l_total_plan_qty,0) > 0 THEN
4332 l_cal_ra_id_tab_tmp.EXTEND;
4333 l_cal_txn_curr_code_tab_tmp.EXTEND;
4334 l_cal_rate_based_flag_tab_tmp.EXTEND;
4335 l_cal_rlm_id_tab_tmp.EXTEND;
4336 l_cal_task_id_tab_tmp.EXTEND;
4337 l_cal_unit_of_measure_tab_tmp.EXTEND;
4338 l_cal_etc_method_code_tab_tmp.EXTEND;
4339 l_cal_etc_qty_tab_tmp.EXTEND;
4340 l_cal_etc_raw_cost_tab_tmp.EXTEND;
4341 l_cal_etc_brdn_cost_tab_tmp.EXTEND;
4342 l_cal_etc_revenue_tab_tmp.EXTEND;
4343 l_cal_rcost_rate_ovrd_tab_tmp.EXTEND;
4344 l_cal_bcost_rate_ovrd_tab_tmp.EXTEND;
4345 l_cal_bill_rate_ovrd_tab_tmp.EXTEND;
4346
4347 l_cal_ra_id_tab_tmp(l_index) := l_cal_ra_id_tab(i);
4348 l_cal_txn_curr_code_tab_tmp(l_index) := l_cal_txn_currency_code_tab(i);
4349 l_cal_rate_based_flag_tab_tmp(l_index) := l_cal_rate_based_flag_tab(i);
4350 l_cal_rlm_id_tab_tmp(l_index) := l_cal_rlm_id_tab(i);
4351 l_cal_task_id_tab_tmp(l_index) := l_cal_task_id_tab(i);
4352 l_cal_unit_of_measure_tab_tmp(l_index) := l_cal_unit_of_measure_tab(i);
4353 l_cal_etc_method_code_tab_tmp(l_index) := l_cal_etc_method_code_tab(i);
4354 l_cal_etc_qty_tab_tmp(l_index) := l_cal_etc_qty_tab(i);
4355 l_cal_etc_raw_cost_tab_tmp(l_index) := l_cal_etc_raw_cost_tab(i);
4356 l_cal_etc_brdn_cost_tab_tmp(l_index) := l_cal_etc_burdened_cost_tab(i);
4357 l_cal_etc_revenue_tab_tmp(l_index) := l_cal_etc_revenue_tab(i);
4358 l_cal_rcost_rate_ovrd_tab_tmp(l_index) := l_cal_rcost_rate_override_tab(i);
4359 l_cal_bcost_rate_ovrd_tab_tmp(l_index) := l_cal_bcost_rate_override_tab(i);
4360 l_cal_bill_rate_ovrd_tab_tmp(l_index) := l_cal_bill_rate_override_tab(i);
4361
4362 l_index := l_index + 1;
4363 END IF; -- l_total_plan_qty > 0
4364
4365 END LOOP; -- FOR i in 1..l_cal_ra_id_tab.count LOOP
4366
4367 l_cal_ra_id_tab := l_cal_ra_id_tab_tmp;
4368 l_cal_txn_currency_code_tab := l_cal_txn_curr_code_tab_tmp;
4369 l_cal_rate_based_flag_tab := l_cal_rate_based_flag_tab_tmp;
4370 l_cal_rlm_id_tab := l_cal_rlm_id_tab_tmp;
4371 l_cal_task_id_tab := l_cal_task_id_tab_tmp;
4372 l_cal_unit_of_measure_tab := l_cal_unit_of_measure_tab_tmp;
4373 l_cal_etc_method_code_tab := l_cal_etc_method_code_tab_tmp;
4374 l_cal_etc_qty_tab := l_cal_etc_qty_tab_tmp;
4375 l_cal_etc_raw_cost_tab := l_cal_etc_raw_cost_tab_tmp;
4376 l_cal_etc_burdened_cost_tab := l_cal_etc_brdn_cost_tab_tmp;
4377 l_cal_etc_revenue_tab := l_cal_etc_revenue_tab_tmp;
4378 l_cal_rcost_rate_override_tab := l_cal_rcost_rate_ovrd_tab_tmp;
4379 l_cal_bcost_rate_override_tab := l_cal_bcost_rate_ovrd_tab_tmp;
4380 l_cal_bill_rate_override_tab := l_cal_bill_rate_ovrd_tab_tmp;
4381
4382 -- End Bug Fix 4654157 and 4670253
4383
4384 ER 5726773: End of commented out section.
4385 *********************************************************************/
4386
4387 IF P_FP_COLS_REC.X_TIME_PHASED_CODE='N' AND
4388 ( p_fp_cols_rec.x_version_type = 'COST' OR
4389 ( p_fp_cols_rec.x_version_type = 'REVENUE' AND
4390 l_rev_gen_method IN ('C','E') ) ) THEN
4391
4392 FOR i in 1..l_cal_ra_id_tab.count LOOP
4393 SELECT planning_start_date,
4394 planning_end_date
4395 INTO l_start_date,
4396 l_end_date
4397 FROM pa_resource_assignments
4398 WHERE resource_assignment_id = l_cal_ra_id_tab(i);
4399
4400 /*Start of the rounding handing*/
4401 IF l_cal_rate_based_flag_tab(i) = 'Y' THEN
4402 l_cal_etc_qty_tab(i) := pa_fin_plan_utils2.round_quantity
4403 (p_quantity => l_cal_etc_qty_tab(i));
4404 ELSE
4405 l_cal_etc_qty_tab(i) := pa_currency.round_trans_currency_amt1
4406 (x_amount => l_cal_etc_qty_tab(i),
4407 x_curr_Code => l_cal_txn_currency_code_tab(i));
4408 END IF;
4409 l_cal_etc_raw_cost_tab(i) := pa_currency.round_trans_currency_amt1
4410 (x_amount => l_cal_etc_raw_cost_tab(i),
4411 x_curr_Code => l_cal_txn_currency_code_tab(i));
4412 l_cal_etc_burdened_cost_tab(i) := pa_currency.round_trans_currency_amt1
4413 (x_amount => l_cal_etc_burdened_cost_tab(i),
4414 x_curr_Code => l_cal_txn_currency_code_tab(i));
4415 /*End of the rounding handling*/
4416
4417 /*dbms_output.put_line('ra_id = ' || l_cal_ra_id_tab(i) ||
4418 ',qty = ' || l_cal_etc_qty_tab(i) ||
4419 ', rc = ' || l_cal_etc_raw_cost_tab(i) ||
4420 ', bc = ' || l_cal_etc_burdened_cost_tab(i));*/
4421
4422 l_amt_dtls_tbl.delete;
4423 l_amt_dtls_tbl(1).period_name := null;
4424 l_amt_dtls_tbl(1).start_date := l_start_date;
4425 l_amt_dtls_tbl(1).end_date := l_end_date;
4426 l_amt_dtls_tbl(1).quantity := l_cal_etc_qty_tab(i);
4427 l_amt_dtls_tbl(1).txn_raw_cost := l_cal_etc_raw_cost_tab(i);
4428 l_amt_dtls_tbl(1).txn_burdened_cost := l_cal_etc_burdened_cost_tab(i);
4429 l_amt_dtls_tbl(1).txn_revenue := null;
4430 l_amt_dtls_tbl(1).project_raw_cost := null;
4431 l_amt_dtls_tbl(1).project_burdened_cost := null;
4432 l_amt_dtls_tbl(1).project_revenue := null;
4433 l_amt_dtls_tbl(1).project_func_raw_cost := null;
4434 l_amt_dtls_tbl(1).project_func_burdened_cost := null;
4435 l_amt_dtls_tbl(1).project_func_revenue := null;
4436
4437 -- ER 4376722: Note that we do not need to check task billability
4438 -- at this point; the billability logic performed upstream should
4439 -- be sufficient. The IF statement below is the only point within
4440 -- this None timephase logic block where Revenue is updated by an
4441 -- assignment. However, the assignment statement is safe, because
4442 -- the billable_flag cannot be 'N' at this point - if it were 'N',
4443 -- then the billability logic upstream would have removed the
4444 -- current record from the pl/sql tables.
4445
4446 -- if then introduced for bug 4232253
4447 IF p_fp_cols_rec.x_version_type = 'REVENUE' AND
4448 l_cal_rate_based_flag_tab(i) = 'N' THEN
4449 -- Earlier, l_cal_etc_qty value has been set to raw cost or
4450 -- burdened cost based on the revenue accrual method.
4451 l_amt_dtls_tbl(1).txn_revenue := l_amt_dtls_tbl(1).quantity;
4452 END IF;
4453
4454 IF P_PA_DEBUG_MODE = 'Y' THEN
4455 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
4456 P_CALLED_MODE => P_CALLED_MODE,
4457 P_MSG => 'Before calling PA_FP_MAINTAIN_ACTUAL_PUB.'||
4458 'MAINTAIN_ACTUAL_AMT_RA',
4459 P_MODULE_NAME => l_module_name);
4460 END IF;
4461 PA_FP_MAINTAIN_ACTUAL_PUB.MAINTAIN_ACTUAL_AMT_RA (
4462 P_PROJECT_ID => P_PROJECT_ID,
4463 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
4464 P_RESOURCE_ASSIGNMENT_ID => l_cal_ra_id_tab(i),
4465 P_TXN_CURRENCY_CODE => l_cal_txn_currency_code_tab(i),
4466 P_AMT_DTLS_REC_TAB => l_amt_dtls_tbl,
4467 P_CALLING_CONTEXT => 'FP_GEN_FCST_COPY_ACTUAL',
4468 P_TXN_AMT_TYPE_CODE => 'PLANNING_TXN',
4469 X_RETURN_STATUS => x_return_Status,
4470 X_MSG_COUNT => x_msg_count,
4471 X_MSG_DATA => x_msg_data );
4472 IF P_PA_DEBUG_MODE = 'Y' THEN
4473 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
4474 P_CALLED_MODE => P_CALLED_MODE,
4475 P_MSG => 'After calling PA_FP_MAINTAIN_ACTUAL_PUB.'||
4476 'MAINTAIN_ACTUAL_AMT_RA: '||x_return_status,
4477 P_MODULE_NAME => l_module_name);
4478 END IF;
4479 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4480 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4481 END IF;
4482 END LOOP;
4483 IF p_pa_debug_mode = 'Y' THEN
4484 PA_DEBUG.Reset_Curr_Function;
4485 END IF;
4486 RETURN;
4487 END IF;
4488
4489 /*
4490 select count(*) into l_count from
4491 pa_budget_lines where budget_version_id = P_BUDGET_VERSION_ID;
4492
4493 hr_utility.trace('==before calculate=== no of bdgt lines'||
4494 l_count );
4495 Initializing every pl sql table to null for calling calculate API */
4496
4497 FOR i in 1..l_cal_ra_id_tab.count LOOP
4498 l_delete_budget_lines_tab.extend;
4499 l_spread_amts_flag_tab.extend;
4500 l_txn_currency_override_tab.extend;
4501 l_addl_qty_tab.extend;
4502 l_addl_raw_cost_tab.extend;
4503 l_addl_burdened_cost_tab.extend;
4504 l_addl_revenue_tab.extend;
4505 l_raw_cost_rate_tab.extend;
4506 l_b_cost_rate_tab.extend;
4507 l_bill_rate_tab.extend;
4508 l_line_start_date_tab.extend;
4509 l_line_end_date_tab.extend;
4510
4511 l_delete_budget_lines_tab(i) := Null;
4512 l_spread_amts_flag_tab(i) := 'Y';
4513 l_txn_currency_override_tab(i) := Null;
4514 l_addl_qty_tab(i) := Null;
4515 l_addl_raw_cost_tab(i) := Null;
4516 l_addl_burdened_cost_tab(i) := Null;
4517 l_addl_revenue_tab(i) := Null;
4518 l_raw_cost_rate_tab(i) := Null;
4519 l_b_cost_rate_tab(i) := Null;
4520 l_bill_rate_tab(i) := Null;
4521 l_line_start_date_tab(i) := Null;
4522 l_line_end_date_tab(i) := Null;
4523 -- --dbms_output.put_line('----'||i||'-----');
4524 -- --dbms_output.put_line(l_resource_assignment_id_tab(i));
4525 -- --dbms_output.put_line(l_quantity_tab(i));
4526 END LOOP;
4527 l_refresh_rates_flag := 'N';
4528 l_refresh_conv_rates_flag := 'N';
4529 l_spread_required_flag := 'Y';
4530 l_conv_rates_required_flag := 'Y';
4531 l_raTxn_rollup_api_call_flag := 'N'; -- Added for IPM new entity ER
4532
4533 -- Bug 3991151: Before calling the Calculate API, we need to copy source
4534 -- attributes (including the spread_curve_id) to target resources where
4535 -- applicable.
4536
4537 /* Populate target ra_ids in tmp1 for COPY_SRC_ATTRS_TO_TARGET_FCST */
4538 DELETE pa_res_list_map_tmp1;
4539 FORALL i in 1..l_cal_ra_id_tab.count
4540 INSERT INTO pa_res_list_map_tmp1
4541 ( txn_resource_assignment_id )
4542 VALUES ( l_cal_ra_id_tab(i) );
4543
4544 IF p_pa_debug_mode = 'Y' THEN
4545 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
4546 ( p_msg => 'Before calling PA_FP_GEN_PUB.' ||
4547 'COPY_SRC_ATTRS_TO_TARGET_FCST',
4548 p_module_name => l_module_name,
4549 p_log_level => 5 );
4550 END IF;
4551 PA_FP_GEN_PUB.COPY_SRC_ATTRS_TO_TARGET_FCST
4552 ( P_FP_COLS_REC => p_fp_cols_rec,
4553 X_RETURN_STATUS => x_return_status,
4554 X_MSG_COUNT => x_msg_count,
4555 X_MSG_DATA => x_msg_data );
4556 IF p_pa_debug_mode = 'Y' THEN
4557 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
4558 ( p_msg => 'Status after calling PA_FP_GEN_PUB.' ||
4559 'COPY_SRC_ATTRS_TO_TARGET_FCST: ' ||
4560 x_return_status,
4561 p_module_name => l_module_name,
4562 p_log_level => 5 );
4563 END IF;
4564 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4565 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4566 END IF;
4567 -- Bug 3991151: End changes.
4568
4569 IF P_PA_DEBUG_MODE = 'Y' THEN
4570 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
4571 P_CALLED_MODE => P_CALLED_MODE,
4572 P_MSG => 'Before calling PA_FP_CALC_PLAN_PKG.calculate',
4573 P_MODULE_NAME => l_module_name);
4574 END IF;
4575 /* select etc_start_date into l_date from
4576 pa_budget_versions where budget_version_id = P_BUDGET_VERSION_ID;
4577
4578 hr_utility.trace('==before calculate=== etc start date '||
4579 to_char(l_date,'dd-mon-yyyy') );
4580 hr_utility.trace('==Entering Calculate=================='); */
4581
4582 /* p_calling module parameter added for bug 3796136 to
4583 spread ETC amount only after the actual thru period end date
4584 and update the planning end date in res asg table.
4585 This logic is handled in the calculate API based on this
4586 new parameter. */
4587
4588 PA_FP_CALC_PLAN_PKG.calculate(
4589 p_calling_module => 'FORECAST_GENERATION',
4590 P_PROJECT_ID => P_PROJECT_ID,
4591 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
4592 P_REFRESH_RATES_FLAG => l_refresh_rates_flag,
4593 P_REFRESH_CONV_RATES_FLAG => l_refresh_conv_rates_flag,
4594 P_SPREAD_REQUIRED_FLAG => l_spread_required_flag,
4595 P_CONV_RATES_REQUIRED_FLAG => l_conv_rates_required_flag,
4596 P_ROLLUP_REQUIRED_FLAG => 'N',
4597 --P_MASS_ADJUST_FLAG
4598 --P_QUANTITY_ADJ_PCT
4599 --P_COST_RATE_ADJ_PCT
4600 --P_BURDENED_RATE_ADJ_PCT
4601 --P_BILL_RATE_ADJ_PCT
4602 P_SOURCE_CONTEXT => l_source_context,
4603 P_RESOURCE_ASSIGNMENT_TAB => l_cal_ra_id_tab,
4604 P_DELETE_BUDGET_LINES_TAB => l_delete_budget_lines_tab,
4605 P_SPREAD_AMTS_FLAG_TAB => l_spread_amts_flag_tab,
4606 P_TXN_CURRENCY_CODE_TAB => l_cal_txn_currency_code_tab,
4607 P_TXN_CURRENCY_OVERRIDE_TAB => l_txn_currency_override_tab,
4608 P_TOTAL_QTY_TAB => l_cal_etc_qty_tab,
4609 P_ADDL_QTY_TAB => l_addl_qty_tab,
4610 P_TOTAL_RAW_COST_TAB => l_cal_etc_raw_cost_tab,
4611 P_ADDL_RAW_COST_TAB => l_addl_raw_cost_tab,
4612 P_TOTAL_BURDENED_COST_TAB => l_cal_etc_burdened_cost_tab,
4613 P_ADDL_BURDENED_COST_TAB => l_addl_burdened_cost_tab,
4614 P_TOTAL_REVENUE_TAB => l_cal_etc_revenue_tab,
4615 P_ADDL_REVENUE_TAB => l_addl_revenue_tab,
4616 P_RAW_COST_RATE_TAB => l_raw_cost_rate_tab,
4617 P_RW_COST_RATE_OVERRIDE_TAB => l_cal_rcost_rate_override_tab,
4618 P_B_COST_RATE_TAB => l_b_cost_rate_tab,
4619 P_B_COST_RATE_OVERRIDE_TAB => l_cal_bcost_rate_override_tab,
4620 P_BILL_RATE_TAB => l_bill_rate_tab,
4621 P_BILL_RATE_OVERRIDE_TAB => l_cal_bill_rate_override_tab,
4622 P_LINE_START_DATE_TAB => l_line_start_date_tab,
4623 P_LINE_END_DATE_TAB => l_line_start_date_tab,
4624 P_RATXN_ROLLUP_API_CALL_FLAG => l_raTxn_rollup_api_call_flag,
4625 X_RETURN_STATUS => x_return_status,
4626 X_MSG_COUNT => x_msg_count,
4627 X_MSG_DATA => x_msg_data );
4628
4629 -- hr_utility.trace('==Leaving Calculate==================:'||x_return_status);
4630
4631 IF P_PA_DEBUG_MODE = 'Y' THEN
4632 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
4633 P_CALLED_MODE => P_CALLED_MODE,
4634 P_MSG => 'After calling PA_FP_CALC_PLAN_PKG.calculate: '||
4635 x_return_status,
4636 P_MODULE_NAME => l_module_name);
4637 END IF;
4638 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
4639 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4640 END IF;
4641
4642 IF p_pa_debug_mode = 'Y' THEN
4643 PA_DEBUG.Reset_Curr_Function;
4644 END IF;
4645 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4646 EXCEPTION
4647 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
4648 --dbms_output.put_line('--INSIDE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc');
4649 l_msg_count := FND_MSG_PUB.count_msg;
4650 IF l_msg_count = 1 THEN
4651 PA_INTERFACE_UTILS_PUB.get_messages
4652 (p_encoded => FND_API.G_TRUE
4653 ,p_msg_index => 1
4654 ,p_msg_count => l_msg_count
4655 ,p_msg_data => l_msg_data
4656 ,p_data => l_data
4657 ,p_msg_index_out => l_msg_index_out);
4658 x_msg_data := l_data;
4659 x_msg_count := l_msg_count;
4660 ELSE
4661 x_msg_count := l_msg_count;
4662 END IF;
4663 ROLLBACK;
4664
4665 x_return_status := FND_API.G_RET_STS_ERROR;
4666 IF P_PA_DEBUG_MODE = 'Y' THEN
4667 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
4668 P_CALLED_MODE => P_CALLED_MODE,
4669 P_MSG => 'Invalid Arguments Passed',
4670 P_MODULE_NAME => l_module_name);
4671 PA_DEBUG.Reset_Curr_Function;
4672 END IF;
4673 RAISE;
4674
4675 WHEN OTHERS THEN
4676 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4677 x_msg_data := SUBSTR(SQLERRM,1,240);
4678 FND_MSG_PUB.add_exc_msg
4679 ( p_pkg_name => 'PA_FP_GEN_FCST_AMT_PUB'
4680 ,p_procedure_name => 'GEN_FCST_TASK_LEVEL_AMT');
4681 IF P_PA_DEBUG_MODE = 'Y' THEN
4682 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
4683 P_CALLED_MODE => P_CALLED_MODE,
4684 P_MSG => 'Unexpected Error'||substr(sqlerrm, 1, 240),
4685 P_MODULE_NAME => l_module_name);
4686 PA_DEBUG.Reset_Curr_Function;
4687 END IF;
4688 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4689 END GEN_FCST_TASK_LEVEL_AMT;
4690
4691 PROCEDURE MAINTAIN_RES_ASG(
4692 P_PROJECT_ID IN PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4693 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
4694 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
4695 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4696 X_MSG_COUNT OUT NOCOPY NUMBER,
4697 X_MSG_DATA OUT NOCOPY VARCHAR2)
4698 IS
4699 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_fcst_amt_pub.maintain_res_asg';
4700
4701 l_msg_count NUMBER;
4702 l_msg_data VARCHAR2(2000);
4703 l_data VARCHAR2(2000);
4704 l_msg_index_out NUMBER:=0;
4705
4706 l_count number;
4707 BEGIN
4708 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4709 X_MSG_COUNT := 0;
4710 IF p_pa_debug_mode = 'Y' THEN
4711 pa_debug.set_curr_function( p_function => 'MAINTAIN_RES_ASG',
4712 p_debug_mode => p_pa_debug_mode);
4713 END IF;
4714
4715 DELETE FROM PA_FP_PLANNING_RES_TMP1;
4716 IF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'L' THEN
4717 -- hr_utility.trace('inside lowest task in maintain res asg :');
4718 /* PA_FP_PLANNING_RES_TMP1 will have res asg id column with
4719 > 0 value - actual res asg id from WP or FP budget version
4720 source, all negative res asg values are inserted either for
4721 tasks with etc source as WORK_QUANTITY or the etc is NONE or NULL. */
4722 INSERT INTO PA_FP_PLANNING_RES_TMP1 (
4723 TASK_ID,
4724 RESOURCE_LIST_MEMBER_ID,
4725 RESOURCE_ASSIGNMENT_ID,
4726 planning_start_date,
4727 planning_end_date )
4728 (SELECT MAPPED_FIN_TASK_ID,
4729 TARGET_RLM_ID,
4730 to_number(NULL),
4731 min(planning_start_date),
4732 max(planning_end_date)
4733 FROM PA_FP_CALC_AMT_TMP1
4734 GROUP BY mapped_fin_task_id,TARGET_RLM_ID,to_number(NULL));
4735 ELSIF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'P' THEN
4736 -- hr_utility.trace('inside proj lvl in maintain res asg :');
4737 INSERT INTO PA_FP_PLANNING_RES_TMP1 (
4738 TASK_ID,
4739 RESOURCE_LIST_MEMBER_ID,
4740 RESOURCE_ASSIGNMENT_ID,
4741 planning_start_date,
4742 planning_end_date )
4743 (SELECT 0,
4744 TARGET_RLM_ID,
4745 to_number(NULL),
4746 min(planning_start_date),
4747 max(planning_end_date)
4748 FROM PA_FP_CALC_AMT_TMP1
4749 group by 0, TARGET_RLM_ID,
4750 to_number(NULL) );
4751 ELSIF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'T' THEN
4752 -- hr_utility.trace('inside top task in maintain res asg :');
4753 INSERT INTO PA_FP_PLANNING_RES_TMP1 (
4754 TASK_ID,
4755 RESOURCE_LIST_MEMBER_ID,
4756 RESOURCE_ASSIGNMENT_ID,
4757 planning_start_date,
4758 planning_end_date )
4759 (SELECT MAPPED_FIN_TASK_ID,
4760 TARGET_RLM_ID,
4761 to_number(NULL),
4762 min(planning_start_date),
4763 max(planning_end_date)
4764 FROM PA_FP_CALC_AMT_TMP1 group by
4765 mapped_fin_task_id,TARGET_RLM_ID,
4766 to_number(NULL) );
4767 END IF;
4768 -- select count(*) into l_count from PA_FP_PLANNING_RES_TMP1;
4769 -- hr_utility.trace('in maintain res asg plan res tmp1 count :'||l_count);
4770 IF P_PA_DEBUG_MODE = 'Y' THEN
4771 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
4772 P_MSG => 'Before calling pa_fp_copy_actuals_pub.create_res_asg',
4773 P_MODULE_NAME => l_module_name,
4774 p_log_level => 5);
4775 END IF;
4776 PA_FP_COPY_ACTUALS_PUB.CREATE_RES_ASG (
4777 P_PROJECT_ID => P_PROJECT_ID,
4778 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
4779 P_FP_COLS_REC => P_FP_COLS_REC,
4780 P_CALLING_PROCESS => 'FORECAST_GENERATION',
4781 X_RETURN_STATUS => x_return_status,
4782 X_MSG_COUNT => x_msg_count,
4783 X_MSG_DATA => x_msg_data );
4784 IF P_PA_DEBUG_MODE = 'Y' THEN
4785 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
4786 P_MSG => 'After calling create_res_asg,return status is: '||x_return_status,
4787 P_MODULE_NAME => l_module_name,
4788 p_log_level => 5);
4789 END IF;
4790 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4791 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4792 END IF;
4793
4794 IF P_PA_DEBUG_MODE = 'Y' THEN
4795 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
4796 P_MSG => 'Before calling pa_fp_copy_actuals_pub.update_res_asg',
4797 P_MODULE_NAME => l_module_name,
4798 p_log_level => 5);
4799 END IF;
4800 PA_FP_COPY_ACTUALS_PUB.UPDATE_RES_ASG (
4801 P_PROJECT_ID => P_PROJECT_ID,
4802 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
4803 P_FP_COLS_REC => P_FP_COLS_REC,
4804 P_CALLING_PROCESS => 'FORECAST_GENERATION',
4805 X_RETURN_STATUS => x_return_status,
4806 X_MSG_COUNT => x_msg_count,
4807 X_MSG_DATA => x_msg_data );
4808 IF P_PA_DEBUG_MODE = 'Y' THEN
4809 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
4810 P_MSG => 'After calling update_res_asg,return status is: '||x_return_status,
4811 P_MODULE_NAME => l_module_name,
4812 p_log_level => 5);
4813 END IF;
4814 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4815 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4816 END IF;
4817
4818 IF p_pa_debug_mode = 'Y' THEN
4819 PA_DEBUG.Reset_Curr_Function;
4820 END IF;
4821 EXCEPTION
4822 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
4823 l_msg_count := FND_MSG_PUB.count_msg;
4824 IF l_msg_count = 1 THEN
4825 PA_INTERFACE_UTILS_PUB.get_messages
4826 (p_encoded => FND_API.G_TRUE
4827 ,p_msg_index => 1
4828 ,p_msg_count => l_msg_count
4829 ,p_msg_data => l_msg_data
4830 ,p_data => l_data
4831 ,p_msg_index_out => l_msg_index_out);
4832 x_msg_data := l_data;
4833 x_msg_count := l_msg_count;
4834 ELSE
4835 x_msg_count := l_msg_count;
4836 END IF;
4837 ROLLBACK;
4838
4839 x_return_status := FND_API.G_RET_STS_ERROR;
4840 IF P_PA_DEBUG_MODE = 'Y' THEN
4841 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
4842 P_MSG => 'Invalid Arguments Passed',
4843 P_MODULE_NAME => l_module_name,
4844 p_log_level => 5);
4845 PA_DEBUG.Reset_Curr_Function;
4846 END IF;
4847 RAISE;
4848
4849 WHEN OTHERS THEN
4850 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4851 x_msg_data := SUBSTR(SQLERRM,1,240);
4852 FND_MSG_PUB.add_exc_msg
4853 ( p_pkg_name => 'PA_FP_GEN_FCST_AMT_PUB'
4854 ,p_procedure_name => 'MAINTAIN_RES_ASG');
4855 IF P_PA_DEBUG_MODE = 'Y' THEN
4856 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
4857 P_MSG => 'Unexpected Error'||substr(sqlerrm, 1, 240),
4858 P_MODULE_NAME => l_module_name,
4859 p_log_level => 5);
4860 PA_DEBUG.Reset_Curr_Function;
4861 END IF;
4862 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4863 END MAINTAIN_RES_ASG;
4864
4865
4866 /**
4867 * This procedure updates pa_budget_lines.other_rejection_code
4868 * for the purpose of signalling ETC revenue amount calculation
4869 * errors. See bug 5203622.
4870 *
4871 * Pre-Conditions:
4872 * 1. At this point, other_rejection_code values should be stored
4873 * in the txn_currency_code column of the pa_fp_calc_amt_tmp2
4874 * table for planning txns with ETC revenue calculation errors.
4875 *
4876 * Note: The etc_currency_code column (not txn_currency_code)
4877 * to store the currency for ETC records in pa_fp_calc_amt_tmp2.
4878 *
4879 * Also worth noting is that this procedure is package-private.
4880 */
4881 PROCEDURE UPD_REV_CALCULATION_ERR
4882 (P_PROJECT_ID IN PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4883 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
4884 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
4885 P_ETC_START_DATE IN DATE,
4886 P_CALLED_MODE IN VARCHAR2 DEFAULT 'SELF_SERVICE',
4887 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4888 X_MSG_COUNT OUT NOCOPY NUMBER,
4889 X_MSG_DATA OUT NOCOPY VARCHAR2 )
4890 IS
4891 l_package_name VARCHAR2(30) := 'PA_FP_GEN_FCST_AMT_PUB';
4892 l_procedure_name VARCHAR2(30) := 'UPD_REV_CALCULATION_ERR';
4893 l_module_name VARCHAR2(100);
4894
4895 l_log_level NUMBER := 5;
4896 l_msg_count NUMBER;
4897 l_data VARCHAR2(1000);
4898 l_msg_data VARCHAR2(1000);
4899 l_msg_index_out NUMBER;
4900 BEGIN
4901 l_module_name := 'pa.plsql.' || l_package_name || '.' || l_procedure_name;
4902
4903 x_return_status := FND_API.G_RET_STS_SUCCESS;
4904 x_msg_count := 0;
4905
4906 IF p_pa_debug_mode = 'Y' THEN
4907 PA_DEBUG.SET_CURR_FUNCTION( p_function => l_procedure_name,
4908 p_debug_mode => p_pa_debug_mode );
4909 END IF;
4910
4911 -- Print values of Input Parameters to debug log
4912 IF p_pa_debug_mode = 'Y' THEN
4913 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
4914 ( p_msg => 'Input Parameters for '
4915 || l_module_name || '():',
4916 p_called_mode => p_called_mode,
4917 p_module_name => l_module_name,
4918 p_log_level => l_log_level );
4919 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
4920 ( p_msg => 'P_PROJECT_ID:['||p_project_id||']',
4921 p_called_mode => p_called_mode,
4922 p_module_name => l_module_name,
4923 p_log_level => l_log_level );
4924 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
4925 ( p_msg => 'P_BUDGET_VERSION_ID:['||p_budget_version_id||']',
4926 p_called_mode => p_called_mode,
4927 p_module_name => l_module_name,
4928 p_log_level => l_log_level );
4929 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
4930 ( p_msg => 'P_ETC_START_DATE:['||p_etc_start_date||']',
4931 p_called_mode => p_called_mode,
4932 p_module_name => l_module_name,
4933 p_log_level => l_log_level );
4934 END IF; -- IF p_pa_debug_mode = 'Y' THEN
4935
4936 -- Validate input parameters
4937 IF p_project_id is NULL OR
4938 p_budget_version_id is NULL OR
4939 p_etc_start_date is NULL THEN
4940 IF p_pa_debug_mode = 'Y' THEN
4941 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
4942 ( p_msg => 'Input Parameter Validation FAILED',
4943 p_called_mode => p_called_mode,
4944 p_module_name => l_module_name,
4945 p_log_level => l_log_level );
4946 PA_DEBUG.RESET_CURR_FUNCTION;
4947 END IF;
4948 RETURN;
4949 END IF;
4950
4951 -- Update pa_budget_lines with any other_rejection_codes stored
4952 -- in the txn_currency_code column of the task level selection
4953 -- forecast generation processing table pa_fp_calc_amt_tmp2.
4954
4955 UPDATE pa_budget_lines bl
4956 SET bl.other_rejection_code =
4957 ( SELECT tmp2.txn_currency_code
4958 FROM pa_fp_calc_amt_tmp2 tmp2
4959 WHERE tmp2.transaction_source_code = 'ETC'
4960 AND tmp2.txn_currency_code is not null
4961 AND bl.resource_assignment_id = tmp2.target_res_asg_id
4962 AND bl.txn_currency_code = tmp2.etc_currency_code )
4963 WHERE bl.budget_version_id = p_budget_version_id
4964 AND nvl(bl.quantity,0) <> nvl(bl.init_quantity,0) -- ETC lines only
4965 AND EXISTS
4966 ( SELECT null
4967 FROM pa_fp_calc_amt_tmp2 tmp2
4968 WHERE tmp2.transaction_source_code = 'ETC'
4969 AND tmp2.txn_currency_code is not null
4970 AND bl.resource_assignment_id = tmp2.target_res_asg_id
4971 AND bl.txn_currency_code = tmp2.etc_currency_code );
4972
4973 IF p_pa_debug_mode = 'Y' THEN
4974 PA_DEBUG.RESET_CURR_FUNCTION;
4975 END IF;
4976 EXCEPTION
4977 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
4978 l_msg_count := FND_MSG_PUB.count_msg;
4979 IF l_msg_count = 1 THEN
4980 PA_INTERFACE_UTILS_PUB.GET_MESSAGES
4981 ( p_encoded => FND_API.G_TRUE,
4982 p_msg_index => 1,
4983 p_msg_count => l_msg_count,
4984 p_msg_data => l_msg_data,
4985 p_data => l_data,
4986 p_msg_index_out => l_msg_index_out);
4987 x_msg_data := l_data;
4988 x_msg_count := l_msg_count;
4989 ELSE
4990 x_msg_count := l_msg_count;
4991 END IF;
4992
4993 ROLLBACK;
4994 x_return_status := FND_API.G_RET_STS_ERROR;
4995
4996 IF p_pa_debug_mode = 'Y' THEN
4997 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
4998 (p_msg => 'Invalid Arguments Passed',
4999 p_module_name => l_module_name,
5000 p_log_level => 5);
5001 PA_DEBUG.RESET_CURR_FUNCTION;
5002 END IF;
5003 RAISE;
5004 WHEN OTHERS THEN
5005 ROLLBACK;
5006 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5007 x_msg_count := 1;
5008 x_msg_data := substr(sqlerrm,1,240);
5009 FND_MSG_PUB.ADD_EXC_MSG
5010 ( p_pkg_name => l_package_name,
5011 p_procedure_name => l_procedure_name,
5012 p_error_text => substr(sqlerrm,1,240));
5013
5014 IF p_pa_debug_mode = 'Y' THEN
5015 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
5016 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
5017 p_module_name => l_module_name,
5018 p_log_level => 5);
5019 PA_DEBUG.RESET_CURR_FUNCTION;
5020 END IF;
5021 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5022
5023 END UPD_REV_CALCULATION_ERR;
5024
5025
5026 END PA_FP_GEN_FCST_AMT_PUB;