[Home] [Help]
PACKAGE BODY: APPS.PA_FP_GEN_AMOUNT_UTILS
Source
1 PACKAGE body PA_FP_GEN_AMOUNT_UTILS as
2 /* $Header: PAFPGAUB.pls 120.11 2007/02/06 09:56:17 dthakker ship $ */
3
4 l_module_name VARCHAR2(100) := 'pa.plsql.pa_fp_gen_amount_utils';
5 Invalid_Arg_Exc EXCEPTION;
6
7 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
8
9 PROCEDURE GET_PLAN_VERSION_DTLS
10 (P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
11 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
12 X_FP_COLS_REC OUT NOCOPY FP_COLS,
13 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
14 X_MSG_COUNT OUT NOCOPY NUMBER,
15 X_MSG_DATA OUT NOCOPY VARCHAR2)
16
17 IS
18
19 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_amount_utils.get_plan_version_dtls';
20
21 l_debug_mode VARCHAR2(30);
22 l_msg_count NUMBER := 0;
23 l_data VARCHAR2(2000);
24 l_msg_data VARCHAR2(2000);
25 l_error_msg_code VARCHAR2(30);
26 l_msg_index_out NUMBER;
27 l_return_status VARCHAR2(2000);
28
29 BEGIN
30
31 X_MSG_COUNT := 0;
32
33 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
34
35 IF p_pa_debug_mode = 'Y' THEN
36 pa_debug.set_curr_function( p_function => 'GET_PLAN_VERSION_DTLS'
37 ,p_debug_mode => p_pa_debug_mode);
38 END IF;
39
40 --dbms_output.put_line('in utils before select');
41
42 SELECT
43 OPT.PROJECT_ID,
44 BV.BUDGET_VERSION_ID,
45 OPT.PROJ_FP_OPTIONS_ID,
46 OPT.FIN_PLAN_TYPE_ID,
47 DECODE(BV.VERSION_TYPE,'COST', OPT.COST_AMOUNT_SET_ID,
48 'REVENUE',OPT.REVENUE_AMOUNT_SET_ID,
49 'ALL',OPT.ALL_AMOUNT_SET_ID),
50 DECODE(BV.VERSION_TYPE,'COST', OPT.COST_FIN_PLAN_LEVEL_CODE,
51 'REVENUE',OPT.REVENUE_FIN_PLAN_LEVEL_CODE,
52 'ALL',OPT.ALL_FIN_PLAN_LEVEL_CODE),
53 DECODE(BV.VERSION_TYPE,'COST', OPT.COST_TIME_PHASED_CODE,
54 'REVENUE',OPT.REVENUE_TIME_PHASED_CODE,
55 'ALL',OPT.ALL_TIME_PHASED_CODE),
56 DECODE(BV.VERSION_TYPE,'COST', OPT.COST_RESOURCE_LIST_ID,
57 'REVENUE',OPT.REVENUE_RESOURCE_LIST_ID,
58 'ALL',OPT.ALL_RESOURCE_LIST_ID),
59 DECODE(BV.VERSION_TYPE,'COST', OPT.COST_RES_PLANNING_LEVEL,
60 'REVENUE',OPT.REVENUE_RES_PLANNING_LEVEL,
61 'ALL',OPT.ALL_RES_PLANNING_LEVEL),
62 OPT.RBS_VERSION_ID,
63 decode(nvl(opt.use_planning_rates_flag,'N'),'N',null,
64 'Y',DECODE(BV.VERSION_TYPE,
65 'COST', OPT.COST_EMP_RATE_SCH_ID,
66 'ALL',OPT.COST_EMP_RATE_SCH_ID,
67 null)),
68 decode(nvl(opt.use_planning_rates_flag,'N'),'N',null,
69 'Y',DECODE(BV.VERSION_TYPE,
70 'REVENUE',OPT.REV_EMP_RATE_SCH_ID,
71 'ALL',OPT.REV_EMP_RATE_SCH_ID,
72 null)),
73 decode(nvl(opt.use_planning_rates_flag,'N'),'N',null,
74 'Y',DECODE(BV.VERSION_TYPE,'COST', OPT.COST_JOB_RATE_SCH_ID,
75 'ALL',OPT.COST_JOB_RATE_SCH_ID,
76 null)),
77 decode(nvl(opt.use_planning_rates_flag,'N'),'N',null,
78 'Y',DECODE(BV.VERSION_TYPE,'REVENUE',OPT.REV_JOB_RATE_SCH_ID,
79 'ALL',OPT.REV_JOB_RATE_SCH_ID,
80 null)),
81 decode(nvl(opt.use_planning_rates_flag,'N'),'N',null,
82 'Y',DECODE(BV.VERSION_TYPE,'COST', OPT.COST_NON_LABOR_RES_RATE_SCH_ID,
83 'ALL', OPT.COST_NON_LABOR_RES_RATE_SCH_ID,
84 null)),
85 decode(nvl(opt.use_planning_rates_flag,'N'),'N',null,
86 'Y',DECODE(BV.VERSION_TYPE,'REVENUE',OPT.REV_NON_LABOR_RES_RATE_SCH_ID,
87 'ALL', OPT.REV_NON_LABOR_RES_RATE_SCH_ID,
88 null)),
89 decode(nvl(opt.use_planning_rates_flag,'N'),'N',null,
90 'Y',DECODE(BV.VERSION_TYPE,'COST', OPT.COST_RES_CLASS_RATE_SCH_ID,
91 'ALL',OPT.COST_RES_CLASS_RATE_SCH_ID,
92 null)),
93 decode(nvl(opt.use_planning_rates_flag,'N'),'N',null,
94 'Y',DECODE(BV.VERSION_TYPE,'REVENUE',OPT.REV_RES_CLASS_RATE_SCH_ID,
95 'ALL',OPT.REV_RES_CLASS_RATE_SCH_ID,
96 null)),
97 DECODE(BV.VERSION_TYPE,'COST', OPT.COST_BURDEN_RATE_SCH_ID,
98 'ALL', OPT.COST_BURDEN_RATE_SCH_ID,
99 null),
100 DECODE(BV.VERSION_TYPE,'COST', OPT.COST_CURRENT_PLANNING_PERIOD,
101 'REVENUE',OPT.REV_CURRENT_PLANNING_PERIOD,
102 'ALL',OPT.ALL_CURRENT_PLANNING_PERIOD),
103 DECODE(BV.VERSION_TYPE,'COST', OPT.COST_PERIOD_MASK_ID,
104 'REVENUE',OPT.REV_PERIOD_MASK_ID,
105 'ALL',OPT.ALL_PERIOD_MASK_ID),
106 DECODE(BV.VERSION_TYPE,'COST', OPT.GEN_SRC_COST_PLAN_TYPE_ID,
107 'REVENUE',OPT.GEN_SRC_REV_PLAN_TYPE_ID,
108 'ALL',OPT.GEN_SRC_ALL_PLAN_TYPE_ID),
109 DECODE(BV.VERSION_TYPE,'COST', OPT.GEN_SRC_COST_PLAN_VERSION_ID,
110 'REVENUE',OPT.GEN_SRC_REV_PLAN_VERSION_ID,
111 'ALL',OPT.GEN_SRC_ALL_PLAN_VERSION_ID),
112 DECODE(BV.VERSION_TYPE,'COST', OPT1.GEN_SRC_COST_PLAN_VER_CODE,
113 'REVENUE',OPT1.GEN_SRC_REV_PLAN_VER_CODE,
114 'ALL',OPT1.GEN_SRC_ALL_PLAN_VER_CODE),
115 DECODE(BV.VERSION_TYPE,'COST', OPT.GEN_COST_SRC_CODE,
116 'REVENUE',OPT.GEN_REV_SRC_CODE,
117 'ALL',OPT.GEN_ALL_SRC_CODE),
118 DECODE(BV.VERSION_TYPE,'COST', OPT.GEN_COST_ETC_SRC_CODE,
119 'REVENUE',OPT.GEN_REV_ETC_SRC_CODE,
120 'ALL',OPT.GEN_ALL_ETC_SRC_CODE),
121 DECODE(BV.VERSION_TYPE,'COST', OPT.GEN_COST_INCL_CHANGE_DOC_FLAG,
122 'REVENUE',OPT.GEN_REV_INCL_CHANGE_DOC_FLAG,
123 'ALL',OPT.GEN_ALL_INCL_CHANGE_DOC_FLAG),
124 DECODE(BV.VERSION_TYPE,'COST', OPT.GEN_COST_INCL_OPEN_COMM_FLAG,
125 'REVENUE','N',
126 'ALL',OPT.GEN_ALL_INCL_OPEN_COMM_FLAG),
127 DECODE(BV.VERSION_TYPE,'COST','N',
128 'REVENUE',OPT.GEN_REV_INCL_BILL_EVENT_FLAG,
129 'ALL',OPT.GEN_ALL_INCL_BILL_EVENT_FLAG),
130 DECODE(BV.VERSION_TYPE,'COST', OPT.GEN_COST_RET_MANUAL_LINE_FLAG,
131 'REVENUE',OPT.GEN_REV_RET_MANUAL_LINE_FLAG,
132 'ALL',OPT.GEN_ALL_RET_MANUAL_LINE_FLAG),
133 DECODE(BV.VERSION_TYPE,'COST', OPT.GEN_COST_ACTUAL_AMTS_THRU_CODE,
134 'REVENUE',OPT.GEN_REV_ACTUAL_AMTS_THRU_CODE,
135 'ALL',OPT.GEN_ALL_ACTUAL_AMTS_THRU_CODE),
136 DECODE(BV.VERSION_TYPE,'COST', OPT.GEN_COST_INCL_UNSPENT_AMT_FLAG,
137 'REVENUE','N',
138 'ALL',OPT.GEN_ALL_INCL_UNSPENT_AMT_FLAG),
139 OPT.PLAN_IN_MULTI_CURR_FLAG,
140 decode(OPT.REVENUE_DERIVATION_METHOD,
141 'COST','C',
142 'WORK','T',
143 'EVENT','E'), --Bug 5462471
144 NVL(P.ORG_ID,-99) ORG_ID,
145 P.PROJECT_CURRENCY_CODE,
146 P.PROJFUNC_CURRENCY_CODE,
147 I.SET_OF_BOOKS_ID,
148 FP.RAW_COST_FLAG,
149 FP.BURDENED_COST_FLAG,
150 FP.REVENUE_FLAG,
151 FP.COST_QTY_FLAG,
152 FP.REVENUE_QTY_FLAG,
153 FP.ALL_QTY_FLAG,
154 FP.BILL_RATE_FLAG,
155 FP.COST_RATE_FLAG,
156 FP.BURDEN_RATE_FLAG,
157 DECODE(BV.WP_VERSION_FLAG,'Y',BV.PROJECT_STRUCTURE_VERSION_ID,
158 PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(opt.project_id )),
159 FB.PLAN_CLASS_CODE,
160 BV.VERSION_TYPE,
161 P.PROJECT_VALUE,
162 OPT.TRACK_WORKPLAN_COSTS_FLAG,
163 DECODE(BV.VERSION_TYPE,'COST', OPT.GEN_SRC_COST_WP_VERSION_ID,
164 'REVENUE',OPT.GEN_SRC_REV_WP_VERSION_ID,
165 'ALL',OPT.GEN_SRC_ALL_WP_VERSION_ID),
166 DECODE(OPT1.FIN_PLAN_PREFERENCE_CODE,
167 'COST_ONLY',OPT1.GEN_SRC_COST_WP_VER_CODE,
168 'REVENUE_ONLY',OPT1.GEN_SRC_REV_WP_VER_CODE,
169 'COST_AND_REV_SAME',OPT1.GEN_SRC_ALL_WP_VER_CODE,
170 'COST_AND_REV_SEP',( DECODE(BV.VERSION_TYPE,
171 'COST', OPT1.GEN_SRC_COST_WP_VER_CODE,
172 'REVENUE',OPT1.GEN_SRC_REV_WP_VER_CODE)))
173 INTO X_FP_COLS_REC.X_PROJECT_ID,
174 X_FP_COLS_REC.X_BUDGET_VERSION_ID,
175 X_FP_COLS_REC.X_PROJ_FP_OPTIONS_ID,
176 X_FP_COLS_REC.X_FIN_PLAN_TYPE_ID,
177 X_FP_COLS_REC.X_AMOUNT_SET_ID,
178 X_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE,
179 X_FP_COLS_REC.X_TIME_PHASED_CODE,
180 X_FP_COLS_REC.X_RESOURCE_LIST_ID,
181 X_FP_COLS_REC.X_RES_PLANNING_LEVEL,
182 X_FP_COLS_REC.X_RBS_VERSION_ID,
183 X_FP_COLS_REC.X_COST_EMP_RATE_SCH_ID,
184 X_FP_COLS_REC.X_REV_EMP_RATE_SCH_ID,
185 X_FP_COLS_REC.X_COST_JOB_RATE_SCH_ID,
186 X_FP_COLS_REC.X_REV_JOB_RATE_SCH_ID,
187 X_FP_COLS_REC.X_CNON_LABOR_RES_RATE_SCH_ID,
188 X_FP_COLS_REC.X_RNON_LABOR_RES_RATE_SCH_ID,
189 X_FP_COLS_REC.X_COST_RES_CLASS_RATE_SCH_ID,
190 X_FP_COLS_REC.X_REV_RES_CLASS_RATE_SCH_ID,
191 X_FP_COLS_REC.X_BURDEN_RATE_SCH_ID,
192 X_FP_COLS_REC.X_CURRENT_PLANNING_PERIOD,
193 X_FP_COLS_REC.X_PERIOD_MASK_ID,
194 X_FP_COLS_REC.X_GEN_SRC_PLAN_TYPE_ID,
195 X_FP_COLS_REC.X_GEN_SRC_PLAN_VERSION_ID,
196 X_FP_COLS_REC.X_GEN_SRC_PLAN_VER_CODE,
197 X_FP_COLS_REC.X_GEN_SRC_CODE,
198 X_FP_COLS_REC.X_GEN_ETC_SRC_CODE,
199 X_FP_COLS_REC.X_GEN_INCL_CHANGE_DOC_FLAG,
200 X_FP_COLS_REC.X_GEN_INCL_OPEN_COMM_FLAG,
201 X_FP_COLS_REC.X_GEN_INCL_BILL_EVENT_FLAG,
202 X_FP_COLS_REC.X_GEN_RET_MANUAL_LINE_FLAG,
203 X_FP_COLS_REC.X_GEN_ACTUAL_AMTS_THRU_CODE,
204 X_FP_COLS_REC.X_GEN_INCL_UNSPENT_AMT_FLAG,
205 X_FP_COLS_REC.X_PLAN_IN_MULTI_CURR_FLAG,
206 X_FP_COLS_REC.X_REVENUE_DERIVATION_METHOD,--Bug 5462471
207 X_FP_COLS_REC.X_ORG_ID,
208 X_FP_COLS_REC.X_PROJECT_CURRENCY_CODE,
209 X_FP_COLS_REC.X_PROJFUNC_CURRENCY_CODE,
210 X_FP_COLS_REC.X_SET_OF_BOOKS_ID,
211 X_FP_COLS_REC.X_RAW_COST_FLAG,
212 X_FP_COLS_REC.X_BURDENED_FLAG,
213 X_FP_COLS_REC.X_REVENUE_FLAG,
214 X_FP_COLS_REC.X_COST_QUANTITY_FLAG,
215 X_FP_COLS_REC.X_REV_QUANTITY_FLAG,
216 X_FP_COLS_REC.X_ALL_QUANTITY_FLAG,
217 X_FP_COLS_REC.X_BILL_RATE_FLAG,
218 X_FP_COLS_REC.X_COST_RATE_FLAG,
219 X_FP_COLS_REC.X_BURDEN_RATE_FLAG,
220 X_FP_COLS_REC.X_PROJECT_STRUCTURE_VERSION_ID,
221 X_FP_COLS_REC.X_PLAN_CLASS_CODE,
222 X_FP_COLS_REC.X_VERSION_TYPE,
223 X_FP_COLS_REC.X_PROJECT_VALUE,
224 X_FP_COLS_REC.X_TRACK_WORKPLAN_COSTS_FLAG,
225 X_FP_COLS_REC.X_GEN_SRC_WP_VERSION_ID,
226 X_FP_COLS_REC.X_GEN_SRC_WP_VER_CODE
227 FROM PA_BUDGET_VERSIONS BV, PA_PROJ_FP_OPTIONS OPT, PA_PROJ_FP_OPTIONS OPT1,
228 PA_PROJECTS_ALL P, PA_IMPLEMENTATIONS_ALL I,
229 PA_FIN_PLAN_AMOUNT_SETS FP,
230 PA_FIN_PLAN_TYPES_B FB
231 WHERE BV.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
232 AND OPT.PROJECT_ID = BV.PROJECT_ID
233 AND OPT.FIN_PLAN_TYPE_ID = BV.FIN_PLAN_TYPE_ID
234 AND OPT.FIN_PLAN_VERSION_ID = P_BUDGET_VERSION_ID
235 AND P.PROJECT_ID = BV.PROJECT_ID
236 AND I.ORG_ID = P.ORG_ID -- R12 MOAC 4447573: NVL(I.ORG_ID,-99) = NVL(P.ORG_ID,-99)
237 AND FP.FIN_PLAN_AMOUNT_SET_ID =
238 DECODE(BV.VERSION_TYPE,'COST', OPT.COST_AMOUNT_SET_ID,
239 'REVENUE',OPT.REVENUE_AMOUNT_SET_ID,
240 'ALL',OPT.ALL_AMOUNT_SET_ID)
241 AND FB.FIN_PLAN_TYPE_ID = BV.FIN_PLAN_TYPE_ID
242 AND OPT1.PROJECT_ID = BV.PROJECT_ID
243 AND OPT1.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_TYPE'
244 AND OPT1.FIN_PLAN_TYPE_ID = BV.FIN_PLAN_TYPE_ID;
245 /* Plan_ver_code is selected at PLAN_TYPE instead of PLAN_VERSION */
246
247 --dbms_output.put_line('in utils after select');
248
249 IF p_pa_debug_mode = 'Y' THEN
250 PA_DEBUG.Reset_Curr_Function;
251 END IF;
252
253 EXCEPTION
254 WHEN Invalid_Arg_Exc THEN
255 X_MSG_COUNT := FND_MSG_PUB.count_msg;
256 X_RETURN_STATUS:= FND_API.G_RET_STS_ERROR;
257 IF p_pa_debug_mode = 'Y' THEN
258 PA_DEBUG.Reset_Curr_Function;
259 END IF;
260 -- Bug 4621171: Removed RAISE statement.
261
262 WHEN OTHERS THEN
263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
264 x_msg_data := SUBSTR(SQLERRM,1,240);
265 FND_MSG_PUB.add_exc_msg
266 ( p_pkg_name => 'PA_FP_GEN_AMOUNT_UTILS'
267 ,p_procedure_name => 'GET_PLAN_VERSION_DTLS');
268 IF P_PA_DEBUG_MODE = 'Y' THEN
269 pa_fp_gen_amount_utils.fp_debug
270 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
271 p_module_name => l_module_name,
272 p_log_level => 5);
273 PA_DEBUG.Reset_Curr_Function;
274 END IF;
275 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
276
277 END GET_PLAN_VERSION_DTLS;
278
279 PROCEDURE CHK_CMT_TXN_CURRENCY
280 (P_PROJECT_ID IN NUMBER,
281 P_PROJ_CURRENCY_CODE IN VARCHAR2,
282 X_MSG_COUNT OUT NOCOPY NUMBER,
283 X_MSG_DATA OUT NOCOPY VARCHAR2,
284 X_RETURN_STATUS OUT NOCOPY VARCHAR2)
285 IS
286 l_flag varchar2(1):='N';
287 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_amount_utils.chk_cmt_txn_currency';
288 BEGIN
289
290 X_MSG_COUNT := 0;
291 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
292
293 IF p_pa_debug_mode = 'Y' THEN
294 pa_debug.set_curr_function( p_function => 'CHK_CMT_TXN_CURRENCY'
295 ,p_debug_mode => p_pa_debug_mode);
296 END IF;
297
298 BEGIN
299 SELECT 'Y'
300 INTO l_flag
301 FROM PA_COMMITMENT_TXNS
302 WHERE PROJECT_ID = P_PROJECT_ID
303 AND DENOM_CURRENCY_CODE <> P_PROJ_CURRENCY_CODE
304 AND NVL(generation_error_flag,'N') = 'N'
305 AND ROWNUM < 2;
306 x_return_status := FND_API.G_RET_STS_ERROR;
307 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
308 p_msg_name => 'PA_FP_CMT_MUL_CURRENCY');
309
310 IF p_pa_debug_mode = 'Y' THEN
311 PA_DEBUG.Reset_Curr_Function;
312 END IF;
313
314 EXCEPTION
315 WHEN NO_DATA_FOUND THEN
316 x_return_status := FND_API.G_RET_STS_SUCCESS;
317 IF p_pa_debug_mode = 'Y' THEN
318 PA_DEBUG.Reset_Curr_Function;
319 END IF;
320 WHEN Invalid_Arg_Exc THEN
321 X_MSG_COUNT := FND_MSG_PUB.count_msg;
322 X_RETURN_STATUS:= FND_API.G_RET_STS_ERROR;
323 IF p_pa_debug_mode = 'Y' THEN
324 PA_DEBUG.Reset_Curr_Function;
325 END IF;
326 RAISE;
327
328 WHEN OTHERS THEN
329 --dbms_output.put_line('inside excep');
330 --dbms_output.put_line(SUBSTR(SQLERRM,1,240));
331 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
332 x_msg_data := SUBSTR(SQLERRM,1,240);
333 FND_MSG_PUB.add_exc_msg
334 ( p_pkg_name => 'PA_FP_GEN_AMOUNT_UTILS'
335 ,p_procedure_name => 'CHK_CMT_TXN_CURRENCY');
336 IF p_pa_debug_mode = 'Y' THEN
337 PA_DEBUG.Reset_Curr_Function;
338 END IF;
339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
340 END;
341
342 END CHK_CMT_TXN_CURRENCY;
343
344 PROCEDURE Get_Curr_Original_Version_Info(
345 p_project_id IN
346 pa_projects_all.project_id%TYPE,
347 p_fin_plan_type_id IN
348 pa_budget_versions.fin_plan_type_id%TYPE,
349 p_version_type IN
350 pa_budget_versions.version_type%TYPE,
351 p_status_code IN VARCHAR2,
352 x_fp_options_id OUT NOCOPY
353 pa_proj_fp_options.proj_fp_options_id%TYPE,
354 x_fin_plan_version_id OUT NOCOPY
355 pa_proj_fp_options.fin_plan_version_id%TYPE,
356 x_return_status OUT NOCOPY VARCHAR2,
357 x_msg_count OUT NOCOPY NUMBER,
358 x_msg_data OUT NOCOPY VARCHAR2)
359 AS
360
361 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_amount_utils.Get_Curr_Original_Version_Info';
362
363 --Start of variables used for debugging
364 l_msg_count NUMBER :=0;
365 l_data VARCHAR2(2000);
366 l_msg_data VARCHAR2(2000);
367 l_error_msg_code VARCHAR2(30);
368 l_msg_index_out NUMBER;
369 l_return_status VARCHAR2(2000);
370 l_debug_mode VARCHAR2(30);
371 --End of variables used for debugging
372
373 l_fp_preference_code
374 pa_proj_fp_options.fin_plan_preference_code%TYPE;
375 l_version_type
376 pa_budget_versions.version_type%TYPE;
377 l_current_original_version_id
378 pa_budget_versions.budget_version_id%TYPE;
379 l_fp_options_id
380 pa_proj_fp_options.proj_fp_options_id%TYPE;
381
382 BEGIN
383
384 x_msg_count := 0;
385 x_return_status := FND_API.G_RET_STS_SUCCESS;
386
387 IF p_pa_debug_mode = 'Y' THEN
388 pa_debug.set_curr_function( p_function => 'Get_Curr_Original_Version_Info'
389 ,p_debug_mode => p_pa_debug_mode);
390 END IF;
391
392 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
393 l_debug_mode := NVL(l_debug_mode, 'Y');
394
395 -- Check for business rules violations
396
397 IF l_debug_mode = 'Y' THEN
398 pa_debug.g_err_stage:='Validating input parameters';
399
400 pa_fp_gen_amount_utils.fp_debug
401 (p_msg => pa_debug.g_err_stage,
402 p_module_name => l_module_name,
403 p_log_level => 5);
404 END IF;
405
406 IF (p_project_id IS NULL) OR
407 (p_fin_plan_type_id IS NULL)
408 THEN
409
410 IF l_debug_mode = 'Y' THEN
411 pa_debug.g_err_stage:='Project_id = '||p_project_id;
412 pa_fp_gen_amount_utils.fp_debug
413 (p_msg => pa_debug.g_err_stage,
414 p_module_name => l_module_name,
415 p_log_level => 5);
416
417 pa_debug.g_err_stage:='Fin_plan_type_id = '||p_fin_plan_type_id;
418 pa_fp_gen_amount_utils.fp_debug
419 (p_msg => pa_debug.g_err_stage,
420 p_module_name => l_module_name,
421 p_log_level => 5);
422 END IF;
423
424 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
425 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
426
427 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
428
429 END IF;
430
431 --Fetch fin plan preference code
432
433 IF l_debug_mode = 'Y' THEN
434 pa_debug.g_err_stage:='Fetching fin plan preference code ';
435 pa_fp_gen_amount_utils.fp_debug
436 (p_msg => pa_debug.g_err_stage,
437 p_module_name => l_module_name,
438 p_log_level => 5);
439 END IF;
440
441 SELECT fin_plan_preference_code
442 INTO l_fp_preference_code
443 FROM pa_proj_fp_options
444 WHERE project_id = p_project_id
445 AND fin_plan_type_id = p_fin_plan_type_id
446 AND fin_plan_option_level_code =
447 PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE;
448
449 IF (l_fp_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP)
450 AND (p_version_type IS NULL) THEN
451
452 --In this case version_type should be passed and so raise error
453
454 IF l_debug_mode = 'Y' THEN
455 pa_debug.g_err_stage:='Version_Type = '||p_version_type;
456 pa_fp_gen_amount_utils.fp_debug
457 (p_msg => pa_debug.g_err_stage,
458 p_module_name => l_module_name,
459 p_log_level => 5);
460 END IF;
461
462 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
463 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
464
465 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
466
467 END IF;
468
469 IF l_debug_mode = 'Y' THEN
470 pa_debug.g_err_stage:='Parameter validation complete ';
471 pa_fp_gen_amount_utils.fp_debug
472 (p_msg => pa_debug.g_err_stage,
473 p_module_name => l_module_name,
474 p_log_level => 5);
475 END IF;
476
477 --Fetch l_element_type ifn't passed and could be derived
478
479 IF p_version_type IS NULL THEN
480
481 IF l_fp_preference_code =
482 PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME THEN
483
484 l_version_type := PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL;
485
486 ELSIF l_fp_preference_code =
487 PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY THEN
488
489 l_version_type := PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST;
490
491 ELSIF l_fp_preference_code =
492 PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY THEN
493
494 l_version_type := PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE;
495
496 END IF;
497
498 END IF;
499
500 --Fetch the current original version
501
502 BEGIN
503
504 IF l_debug_mode = 'Y' THEN
505 pa_debug.g_err_stage:='Fetching current original Version';
506 pa_fp_gen_amount_utils.fp_debug
507 (p_msg => pa_debug.g_err_stage,
508 p_module_name => l_module_name,
509 p_log_level => 5);
510 END IF;
511
512 IF p_status_code = 'CURRENT_BASELINED'
513 OR p_status_code = 'CURRENT_APPROVED' THEN
514 SELECT budget_version_id
515 INTO l_current_original_version_id
516 FROM pa_budget_versions
517 WHERE project_id = p_project_id
518 AND fin_plan_type_id = p_fin_plan_type_id
519 AND version_type = NVL(p_version_type,l_version_type)
520 AND budget_status_code = 'B'
521 AND current_flag = 'Y';
522 ELSIF p_status_code = 'ORIGINAL_BASELINED'
523 OR p_status_code = 'ORIGINAL_APPROVED' THEN
524 SELECT budget_version_id
525 INTO l_current_original_version_id
526 FROM pa_budget_versions
527 WHERE project_id = p_project_id
528 AND fin_plan_type_id = p_fin_plan_type_id
529 AND version_type = NVL(p_version_type,l_version_type)
530 AND budget_status_code = 'B'
531 AND current_original_flag = 'Y';
532 END IF;
533
534 --Fetch fp options id using plan version id
535
536 IF l_debug_mode = 'Y' THEN
537 pa_debug.g_err_stage:='Fetching fp option id';
538 pa_fp_gen_amount_utils.fp_debug
539 (p_msg => pa_debug.g_err_stage,
540 p_module_name => l_module_name,
541 p_log_level => 5);
542 END IF;
543
544 SELECT proj_fp_options_id
545 INTO l_fp_options_id
546 FROM pa_proj_fp_options
547 WHERE fin_plan_version_id = l_current_original_version_id;
548
549 EXCEPTION
550
551 WHEN NO_DATA_FOUND THEN
552
553 l_current_original_version_id := NULL;
554 l_fp_options_id := NULL;
555
556 END;
557
558 -- return the parameters to calling programme
559 x_fin_plan_version_id := l_current_original_version_id;
560 x_fp_options_id := l_fp_options_id;
561
562 IF l_debug_mode = 'Y' THEN
563 pa_debug.g_err_stage:='Exiting Get_Curr_Original_Version_Info';
564 pa_fp_gen_amount_utils.fp_debug
565 (p_msg => pa_debug.g_err_stage,
566 p_module_name => l_module_name,
567 p_log_level => 5);
568 PA_DEBUG.Reset_Curr_Function;
569 END IF;
570
571 EXCEPTION
572
573 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
574 l_msg_count := FND_MSG_PUB.count_msg;
575 IF l_msg_count = 1 THEN
576 PA_INTERFACE_UTILS_PUB.get_messages
577 (p_encoded => FND_API.G_TRUE
578 ,p_msg_index => 1
579 ,p_msg_count => l_msg_count
580 ,p_msg_data => l_msg_data
581 ,p_data => l_data
582 ,p_msg_index_out => l_msg_index_out);
583
584 x_msg_data := l_data;
585 x_msg_count := l_msg_count;
586 ELSE
587 x_msg_count := l_msg_count;
588 END IF;
589
590 x_return_status := FND_API.G_RET_STS_ERROR;
591
592 IF l_debug_mode = 'Y' THEN
593 pa_debug.g_err_stage:='Invalid Arguments Passed';
594 pa_fp_gen_amount_utils.fp_debug
595 (p_msg => pa_debug.g_err_stage,
596 p_module_name => l_module_name,
597 p_log_level => 5);
598
599 -- reset error stack
600 PA_DEBUG.Reset_Curr_Function;
601 END IF;
602 RETURN;
603
604 WHEN Others THEN
605 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
606 x_msg_count := 1;
607 x_msg_data := SQLERRM;
608
609 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_GEN_AMOUNT_UTILS'
610 ,p_procedure_name => 'Get_Curr_Original_Version_Info');
611
612 IF l_debug_mode = 'Y' THEN
613 pa_fp_gen_amount_utils.fp_debug
614 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
615 p_module_name => l_module_name,
616 p_log_level => 5);
617 PA_DEBUG.Reset_Curr_Function;
618 END IF;
619 RAISE;
620 END Get_Curr_Original_Version_Info;
621
622 PROCEDURE VALIDATE_PLAN_VERSION
623 (P_PROJECT_ID IN
624 PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
625 P_SRC_BDGT_VERSION_ID IN
626 PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
627 P_TRGT_BDGT_VERSION_ID IN
628 PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
629 X_MSG_COUNT OUT NOCOPY NUMBER,
630 X_MSG_DATA OUT NOCOPY VARCHAR2,
631 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
632
633 l_fp_cols_rec PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
634 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_amount_utils.validate_plan_version';
635
636 BEGIN
637
638 X_MSG_COUNT := 0;
639 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
640
641 IF p_pa_debug_mode = 'Y' THEN
642 pa_debug.set_curr_function( p_function => 'VALIDATE_PLAN_VERSION'
643 ,p_debug_mode => p_pa_debug_mode);
644 END IF;
645
646 --Calling the Util API
647 IF p_pa_debug_mode = 'Y' THEN
648 pa_fp_gen_amount_utils.fp_debug
649 (p_msg => 'Before calling
650 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS',
651 p_module_name => l_module_name,
652 p_log_level => 5);
653 END IF;
654 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS
655 (P_PROJECT_ID => P_PROJECT_ID,
656 P_BUDGET_VERSION_ID => P_SRC_BDGT_VERSION_ID,
657 X_FP_COLS_REC => l_fp_cols_rec,
658 X_RETURN_STATUS => X_RETURN_STATUS,
659 X_MSG_COUNT => X_MSG_COUNT,
660 X_MSG_DATA => X_MSG_DATA);
661 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
662 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
663 END IF;
664 IF p_pa_debug_mode = 'Y' THEN
665 pa_fp_gen_amount_utils.fp_debug
666 (p_msg => 'Status after calling
667 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS: '
668 ||x_return_status,
669 p_module_name => l_module_name,
670 p_log_level => 5);
671 END IF;
672 --dbms_output.put_line('Status of get plan version dtls api: '||X_RETURN_STATUS);
673
674 IF l_fp_cols_rec.X_TIME_PHASED_CODE <> 'P' OR l_fp_cols_rec.X_TIME_PHASED_CODE <> 'G' THEN
675 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
676 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
677 p_msg_name => 'PA_FP_INV_TIME_PHASE_CODE');
678 END IF;
679
680 IF p_pa_debug_mode = 'Y' THEN
681 PA_DEBUG.Reset_Curr_Function;
682 END IF;
683
684
685 EXCEPTION
686
687 WHEN OTHERS THEN
688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
689 x_msg_data := SUBSTR(SQLERRM,1,240);
690 FND_MSG_PUB.add_exc_msg
691 ( p_pkg_name => 'PA_FP_GEN_AMOUNT_UTILS'
692 ,p_procedure_name => 'VALIDATE_PLAN_VERSION');
693 IF p_pa_debug_mode = 'Y' THEN
694 PA_DEBUG.Reset_Curr_Function;
695 END IF;
696 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
697
698
699 END VALIDATE_PLAN_VERSION;
700
701 PROCEDURE GET_VALUES_FOR_PLANNING_RATE
702 (P_PROJECT_ID IN
703 PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
704 P_BUDGET_VERSION_ID IN
705 PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
706 P_RESOURCE_ASSIGNMENT_ID IN
707 PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE,
708 P_TASK_ID IN
709 PA_TASKS.TASK_ID%TYPE,
710 P_RESOURCE_LIST_MEMBER_ID IN
711 PA_RESOURCE_ASSIGNMENTS.resource_list_member_id%TYPE,
712 P_TXN_CURRENCY_CODE IN
713 PA_BUDGET_LINES.txn_currency_code%TYPE,
714 X_RES_FORMAT_ID OUT NOCOPY
715 PA_RESOURCE_LIST_MEMBERS.RES_FORMAT_ID%TYPE,
716 X_RESOURCE_ASN_REC OUT NOCOPY RESOURCE_ASN_REC,
717 X_PA_TASKS_REC OUT NOCOPY PA_TASKS_REC,
718 X_PA_PROJECTS_ALL_REC OUT NOCOPY PA_PROJECTS_ALL_REC,
719 X_PROJ_FP_OPTIONS_REC OUT NOCOPY PROJ_FP_OPTIONS_REC,
720 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
721 X_MSG_COUNT OUT NOCOPY NUMBER,
722 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
723
724 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_amount_utils.get_values_for_planning_rate';
725
726 CURSOR get_resource_asn_csr (c_resource_assignment_id IN NUMBER) IS
727 SELECT project_id
728 ,task_id
729 ,unit_of_measure
730 ,resource_class_code
731 ,organization_id
732 ,job_id
733 ,person_id
734 ,expenditure_type
735 ,non_labor_resource
736 ,bom_resource_id
737 ,inventory_item_id
738 ,item_category_id
739 ,mfc_cost_type_id
740 ,rate_based_flag
741 ,rate_expenditure_org_id
742 ,rate_expenditure_type
743 FROM pa_resource_assignments ra
744 WHERE ra.resource_assignment_id = c_resource_assignment_id;
745
746 --get_resource_asn_rec get_resource_asn_csr%ROWTYPE;
747
748 CURSOR get_tasks_csr(c_task_id IN NUMBER) IS
749 SELECT non_labor_bill_rate_org_id
750 ,non_labor_schedule_discount
751 ,non_labor_schedule_fixed_date
752 ,non_lab_std_bill_rt_sch_id
753 ,emp_bill_rate_schedule_id
754 ,job_bill_rate_schedule_id
755 ,labor_bill_rate_org_id
756 ,labor_sch_type
757 ,non_labor_sch_type
758 ,top_task_id
759 FROM pa_tasks t
760 WHERE t.task_id = c_task_id;
761
762 --get_tasks_rec get_tasks_csr%ROWTYPE;
763
764 CURSOR get_projects_all_csr (c_proj_id IN NUMBER) IS
765 SELECT assign_precedes_task
766 ,bill_job_group_id
767 ,carrying_out_organization_id
768 ,multi_currency_billing_flag
769 ,org_id
770 ,non_labor_bill_rate_org_id
771 ,project_currency_code
772 ,non_labor_schedule_discount
773 ,non_labor_schedule_fixed_date
774 ,non_lab_std_bill_rt_sch_id
775 ,project_type
776 ,projfunc_currency_code
777 ,emp_bill_rate_schedule_id
778 ,job_bill_rate_schedule_id
779 ,labor_bill_rate_org_id
780 ,labor_sch_type
781 ,non_labor_sch_type
782 FROM pa_projects_all ppa
783 WHERE ppa.project_id = c_proj_id;
784
785
786 --get_projects_all_rec get_projects_all_csr%ROWTYPE;
787
788
789
790
791 CURSOR get_proj_fp_options_csr IS
792 SELECT decode(pfo.use_planning_rates_flag,'N',
793 pfo.res_class_bill_rate_sch_id,
794 decode(bv.version_type,'REVENUE',
795 pfo.rev_res_class_rate_sch_id,
796 'ALL' ,pfo.rev_res_class_rate_sch_id,
797 NULL)) res_class_bill_rate_sch_id
798 ,decode(pfo.use_planning_rates_flag,'N',
799 pfo.res_class_raw_cost_sch_id,
800 decode(bv.version_type,'COST',
801 pfo.cost_res_class_rate_sch_id, 'ALL',
802 pfo.cost_res_class_rate_sch_id,
803 NULL)) res_class_raw_cost_sch_id
804 ,pfo.use_planning_rates_flag
805 ,decode(pfo.use_planning_rates_flag,'N',null,
806 decode(bv.version_type,'REVENUE',
807 pfo.rev_job_rate_sch_id, 'ALL',
808 pfo.rev_job_rate_sch_id, NULL)) rev_job_rate_sch_id
809 ,decode(pfo.use_planning_rates_flag,'N',null,
810 decode(bv.version_type,'COST',
811 pfo.cost_job_rate_sch_id, 'ALL',
812 pfo.cost_job_rate_sch_id, NULL)) cost_job_rate_sch_id
813 ,decode(pfo.use_planning_rates_flag,'N',null,
814 decode(bv.version_type,'REVENUE',
815 pfo.rev_emp_rate_sch_id, 'ALL',
816 pfo.rev_emp_rate_sch_id, NULL)) rev_emp_rate_sch_id
817 ,decode(pfo.use_planning_rates_flag,'N',null,
818 decode(bv.version_type,'COST',
819 pfo.cost_emp_rate_sch_id, 'ALL',
820 pfo.cost_emp_rate_sch_id, NULL)) cost_emp_rate_sch_id
821 ,decode(pfo.use_planning_rates_flag,'N',null,
822 decode(bv.version_type,'REVENUE',
823 pfo.rev_non_labor_res_rate_sch_id, 'ALL',
824 pfo.rev_non_labor_res_rate_sch_id, NULL))
825 rev_non_labor_res_rate_sch_id
826 ,decode(pfo.use_planning_rates_flag,'N',null,
827 decode(bv.version_type,'COST' ,
828 pfo.cost_non_labor_res_rate_sch_id,'ALL',
829 pfo.cost_non_labor_res_rate_sch_id, NULL))
830 cost_non_labor_res_rate_sch_id
831 ,decode(pfo.use_planning_rates_flag,'N',null,
832 decode(bv.version_type,'COST',
833 pfo.cost_burden_rate_sch_id,'ALL',
834 pfo.cost_burden_rate_sch_id, NULL))
835 cost_burden_rate_sch_id
836 ,bv.version_type
837 FROM pa_proj_fp_options pfo,
838 pa_budget_versions bv
839 WHERE pfo.fin_plan_version_id = bv.budget_version_id
840 AND bv.budget_version_id = p_budget_version_id;
841
842 --get_proj_fp_options_rec get_proj_fp_options_csr%ROWTYPE;
843
844
845 /*
846 --Local variables for pa_resource_assignments table
847 g_project_id pa_resource_assignments.project_id%TYPE;
848 l_rate_task_id pa_resource_assignments.task_id%TYPE;
849 l_unit_of_measure pa_resource_assignments.unit_of_measure%TYPE;
850 l_resource_class_code pa_resource_assignments.resource_class_code%TYPE;
851 l_organization_id pa_resource_assignments.organization_id%TYPE;
852 l_job_id pa_resource_assignments.job_id%TYPE;
853 l_person_id pa_resource_assignments.person_id%TYPE;
854 l_expenditure_type pa_resource_assignments.expenditure_type%TYPE;
855 l_non_labor_resource pa_resource_assignments.non_labor_resource%TYPE;
856 l_bom_resource_id pa_resource_assignments.bom_resource_id%TYPE;
857 l_inventory_item_id pa_resource_assignments.inventory_item_id%TYPE;
858 l_item_category_id pa_resource_assignments.item_category_id%TYPE;
859 l_mfc_cost_type_id pa_resource_assignments.mfc_cost_type_id%TYPE;
860 l_rate_based_flag pa_resource_assignments.rate_based_flag%TYPE;
861 l_rate_incurred_by_organz_id pa_resource_assignments.rate_incurred_by_organz_id%TYPE;
862 l_rate_override_to_organz_id pa_resource_assignments.rate_override_to_organz_id%TYPE;
863 l_rate_expenditure_org_id pa_resource_assignments.rate_expenditure_org_id%TYPE;
864 l_rate_expenditure_type pa_resource_assignments.rate_expenditure_type%TYPE;
865 l_rate_organization_id pa_resource_assignments.rate_organization_id%TYPE;
866 l_nlr_organization_id pa_resource_assignments.organization_id%TYPE;
867
868 -- Local variables for pa_tasks table
869 l_task_bill_rate_org_id pa_tasks.non_labor_bill_rate_org_id%TYPE;
870 l_task_sch_discount pa_tasks.non_labor_schedule_discount%TYPE;
871 l_task_sch_date pa_tasks.non_labor_schedule_fixed_date%TYPE;
872 l_task_nl_std_bill_rt_sch_id pa_tasks.non_lab_std_bill_rt_sch_id%TYPE;
873 l_task_emp_bill_rate_sch_id pa_tasks.emp_bill_rate_schedule_id%TYPE;
874 l_task_job_bill_rate_sch_id pa_tasks.job_bill_rate_schedule_id%TYPE;
875 l_task_lab_bill_rate_org_id pa_tasks.labor_bill_rate_org_id%TYPE;
876 l_task_lab_sch_type pa_tasks.labor_sch_type%TYPE;
877 l_task_non_labor_sch_type pa_tasks.non_labor_sch_type%TYPE;
878 l_top_task_id pa_tasks.top_task_id%TYPE;
879 l_lab_sch_type pa_tasks.emp_bill_rate_schedule_id%TYPE;
880
881 -- Local variables for pa_projects_all table
882 l_assign_precedes_task pa_projects_all.assign_precedes_task%TYPE;
883 l_bill_job_group_id pa_projects_all.bill_job_group_id%TYPE;
884 l_carrying_out_organization_id pa_projects_all.carrying_out_organization_id%TYPE;
885 l_multi_currency_billing_flag pa_projects_all.multi_currency_billing_flag%TYPE;
886 l_org_id pa_projects_all.org_id%TYPE;
887 l_project_bill_rate_org_id pa_projects_all.non_labor_bill_rate_org_id%TYPE;
888 l_project_sch_discount pa_projects_all.non_labor_schedule_discount%TYPE;
889 l_project_sch_date pa_projects_all.non_labor_schedule_fixed_date%TYPE;
890 l_proj_nl_std_bill_rt_sch_id pa_projects_all.non_lab_std_bill_rt_sch_id%TYPE;
891 l_project_type pa_projects_all.project_type%TYPE;
892 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
893 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
894 l_proj_emp_bill_rate_sch_id pa_projects_all.emp_bill_rate_schedule_id%TYPE;
895 l_proj_job_bill_rate_sch_id pa_projects_all.job_bill_rate_schedule_id%TYPE;
896 l_proj_lab_bill_rate_org_id pa_projects_all.labor_bill_rate_org_id%TYPE;
897 l_proj_lab_sch_type pa_projects_all.labor_sch_type%TYPE;
898 l_proj_non_labor_sch_type pa_projects_all.non_labor_sch_type%TYPE;
899 l_non_labor_sch_type pa_projects_all.non_labor_sch_type%TYPE;
900 l_lab_bill_rate_org_id pa_projects_all.labor_bill_rate_org_id%TYPE;
901 l_job_bill_rate_sch_id pa_projects_all.job_bill_rate_schedule_id%TYPE;
902 l_emp_bill_rate_sch_id pa_projects_all.emp_bill_rate_schedule_id%TYPE;
903
904 -- Local variables for pa_resource_list_members table
905 l_res_format_id pa_resource_list_members.res_format_id%TYPE;
906
907 -- Local variables for pa_proj_fp_options table
908 l_fp_res_cl_bill_rate_sch_id pa_proj_fp_options.res_class_bill_rate_sch_id%TYPE;
909 l_fp_res_cl_raw_cost_sch_id pa_proj_fp_options.res_class_raw_cost_sch_id%TYPE;
910 l_fp_use_planning_rt_flag pa_proj_fp_options.use_planning_rates_flag%TYPE;
911 l_fp_rev_job_rate_sch_id pa_proj_fp_options.rev_job_rate_sch_id%TYPE;
912 l_fp_cost_job_rate_sch_id pa_proj_fp_options.cost_job_rate_sch_id%TYPE;
913 l_fp_rev_emp_rate_sch_id pa_proj_fp_options.rev_emp_rate_sch_id%TYPE;
914 l_fp_cost_emp_rate_sch_id pa_proj_fp_options.cost_emp_rate_sch_id%TYPE;
915 l_fp_rev_non_lab_rs_rt_sch_id pa_proj_fp_options.rev_non_labor_res_rate_sch_id%TYPE;
916 l_fp_cost_non_lab_rs_rt_sch_id pa_proj_fp_options.cost_non_labor_res_rate_sch_id%TYPE;
917 l_fp_cost_burden_rate_sch_id pa_proj_fp_options.cost_burden_rate_sch_id%TYPE;
918 l_fp_budget_version_type pa_budget_versions.version_type%TYPE;
919
920 -- Local variables for pa_fp_rollup_tmp table
921 l_txn_currency_code pa_fp_rollup_tmp.txn_currency_code%TYPE := NULL;
922 l_txn_plan_quantity pa_fp_rollup_tmp.quantity%TYPE := NULL;
923 l_budget_lines_start_date pa_fp_rollup_tmp.start_date%TYPE := NULL;
924 l_budget_line_id pa_fp_rollup_tmp.budget_line_id%TYPE := NULL;
925 l_burden_cost_rate_override pa_fp_rollup_tmp.burden_cost_rate_override%TYPE := NULL;
926 l_rw_cost_rate_override pa_fp_rollup_tmp.rw_cost_rate_override%TYPE := NULL;
927 l_bill_rate_override pa_fp_rollup_tmp.bill_rate_override%TYPE := NULL;
928 l_txn_raw_cost pa_fp_rollup_tmp.txn_raw_cost%TYPE := NULL;
929 l_txn_burdened_cost pa_fp_rollup_tmp.txn_burdened_cost%TYPE := NULL;
930 l_txn_revenue pa_fp_rollup_tmp.txn_revenue%TYPE := NULL;
931
932
933 l_txn_currency_code_override pa_fp_res_assignments_tmp.txn_currency_code_override%TYPE;
934 l_assignment_id pa_project_assignments.assignment_id%TYPE := NULL;
935 l_cost_rate_multiplier CONSTANT pa_labor_cost_multipliers.multiplier%TYPE := 1;
936
937 l_bill_rate_multiplier CONSTANT NUMBER := 1;
938 l_cost_sch_type VARCHAR2(30) := 'COST';
939 l_mfc_cost_source CONSTANT NUMBER := 2;
940 l_calculate_mode VARCHAR2(60); */
941
942 l_count NUMBER;
943 l_msg_count NUMBER;
944 l_data VARCHAR2(2000);
945 l_msg_data VARCHAR2(2000);
946 l_msg_index_out NUMBER;
947
948 BEGIN
949
950 X_MSG_COUNT := 0;
951 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
952
953 IF p_pa_debug_mode = 'Y' THEN
954 pa_debug.set_curr_function( p_function => 'GET_VALUES_FOR_PLANNING_RATE'
955 ,p_debug_mode => p_pa_debug_mode);
956 END IF;
957
958
959 OPEN get_resource_asn_csr(p_resource_assignment_id);
960 FETCH get_resource_asn_csr INTO x_resource_asn_rec;
961 CLOSE get_resource_asn_csr;
962
963 /* g_project_id := get_resource_asn_rec.project_id;
964 l_rate_task_id := get_resource_asn_rec.task_id;
965 l_unit_of_measure := get_resource_asn_rec.unit_of_measure;
966 l_resource_class_code := get_resource_asn_rec.resource_class_code;
967 l_organization_id := get_resource_asn_rec.organization_id;
968 l_job_id := get_resource_asn_rec.job_id;
969 l_person_id := get_resource_asn_rec.person_id;
970 l_expenditure_type := get_resource_asn_rec.expenditure_type;
971 l_non_labor_resource := get_resource_asn_rec.non_labor_resource;
972 l_bom_resource_id := get_resource_asn_rec.bom_resource_id;
973 l_inventory_item_id := get_resource_asn_rec.inventory_item_id;
974 l_item_category_id := get_resource_asn_rec.item_category_id;
975 l_mfc_cost_type_id := get_resource_asn_rec.mfc_cost_type_id;
976 l_rate_based_flag := get_resource_asn_rec.rate_based_flag;
977 l_rate_incurred_by_organz_id := get_resource_asn_rec.rate_incurred_by_organz_id;
978 l_rate_override_to_organz_id := get_resource_asn_rec.rate_override_to_organz_id;
979 l_rate_expenditure_org_id := get_resource_asn_rec.rate_expenditure_org_id;
980 l_rate_expenditure_type := get_resource_asn_rec.rate_expenditure_type;
981 l_rate_organization_id := get_resource_asn_rec.rate_organization_id; */
982
983 OPEN get_projects_all_csr(p_project_id);
984 FETCH get_projects_all_csr INTO x_pa_projects_all_rec;
985 CLOSE get_projects_all_csr;
986
987 /* l_assign_precedes_task := get_projects_all_rec.assign_precedes_task;
988 l_bill_job_group_id := get_projects_all_rec.bill_job_group_id;
989 l_carrying_out_organization_id := get_projects_all_rec.carrying_out_organization_id;
990 l_multi_currency_billing_flag := get_projects_all_rec.multi_currency_billing_flag;
991 l_org_id := get_projects_all_rec.org_id;
992 l_project_bill_rate_org_id := get_projects_all_rec.non_labor_bill_rate_org_id;
993 l_project_currency_code := get_projects_all_rec.project_currency_code;
994 l_project_sch_discount := get_projects_all_rec.non_labor_schedule_discount;
995 l_project_sch_date := get_projects_all_rec.non_labor_schedule_fixed_date;
996 l_proj_nl_std_bill_rt_sch_id := get_projects_all_rec.non_lab_std_bill_rt_sch_id;
997 l_project_type := get_projects_all_rec.project_type;
998 l_projfunc_currency_code := get_projects_all_rec.projfunc_currency_code;
999 l_proj_emp_bill_rate_sch_id := get_projects_all_rec.emp_bill_rate_schedule_id;
1000 l_proj_job_bill_rate_sch_id := get_projects_all_rec.job_bill_rate_schedule_id;
1001 l_proj_lab_bill_rate_org_id := get_projects_all_rec.labor_bill_rate_org_id;
1002 l_proj_lab_sch_type := get_projects_all_rec.labor_sch_type;
1003 l_proj_non_labor_sch_type := get_projects_all_rec.non_labor_sch_type; */
1004
1005
1006 OPEN get_tasks_csr(p_task_id);
1007 FETCH get_tasks_csr INTO x_pa_tasks_rec;
1008 CLOSE get_tasks_csr;
1009
1010 /*
1011 IF get_tasks_csr%NOTFOUND THEN
1012
1013 l_task_bill_rate_org_id := NULL;
1014 l_task_sch_discount := NULL;
1015 l_task_sch_date := NULL;
1016 l_task_nl_std_bill_rt_sch_id := NULL;
1017 l_task_emp_bill_rate_sch_id := NULL;
1018 l_task_job_bill_rate_sch_id := NULL;
1019 l_task_lab_bill_rate_org_id := NULL;
1020 l_task_lab_sch_type := NULL;
1021 l_task_non_labor_sch_type := NULL;
1022 l_top_task_id := NULL;
1023 l_rate_task_id := NULL;
1024
1025 --If task level attributes are not found
1026 --then the following atributes can be taken from the project level
1027
1028 l_emp_bill_rate_sch_id := l_proj_emp_bill_rate_sch_id;
1029 l_job_bill_rate_sch_id := l_proj_job_bill_rate_sch_id;
1030 l_lab_bill_rate_org_id := l_proj_lab_bill_rate_org_id;
1031 l_lab_sch_type := l_proj_lab_sch_type;
1032 l_non_labor_sch_type := l_proj_non_labor_sch_type;
1033
1034 ELSE
1035
1036 l_task_bill_rate_org_id := get_tasks_rec.non_labor_bill_rate_org_id;
1037 l_task_sch_discount := get_tasks_rec.non_labor_schedule_discount;
1038 l_task_sch_date := get_tasks_rec.non_labor_schedule_fixed_date;
1039 l_task_nl_std_bill_rt_sch_id := get_tasks_rec.non_lab_std_bill_rt_sch_id;
1040 l_task_emp_bill_rate_sch_id := get_tasks_rec.emp_bill_rate_schedule_id;
1041 l_task_job_bill_rate_sch_id := get_tasks_rec.job_bill_rate_schedule_id;
1042 l_task_lab_bill_rate_org_id := get_tasks_rec.labor_bill_rate_org_id;
1043 l_task_lab_sch_type := get_tasks_rec.labor_sch_type;
1044 l_task_non_labor_sch_type := get_tasks_rec.non_labor_sch_type;
1045 l_top_task_id := get_tasks_rec.top_task_id;
1046
1047 --Task level attributes are found
1048 --the following atributes can be taken from the task level
1049
1050 l_emp_bill_rate_sch_id := l_task_emp_bill_rate_sch_id;
1051 l_job_bill_rate_sch_id := l_task_job_bill_rate_sch_id;
1052 l_lab_bill_rate_org_id := l_task_lab_bill_rate_org_id;
1053 l_lab_sch_type := l_task_lab_sch_type;
1054 l_non_labor_sch_type := l_task_non_labor_sch_type;
1055
1056 END IF; */
1057
1058
1059 SELECT res_format_id
1060 INTO x_res_format_id
1061 FROM pa_resource_list_members
1062 WHERE resource_list_member_id = p_resource_list_member_id;
1063
1064 OPEN get_proj_fp_options_csr;
1065 FETCH get_proj_fp_options_csr INTO x_proj_fp_options_rec;
1066 CLOSE get_proj_fp_options_csr;
1067
1068 /*
1069 l_fp_res_cl_bill_rate_sch_id := get_proj_fp_options_rec.res_class_bill_rate_sch_id;
1070 l_fp_res_cl_raw_cost_sch_id := get_proj_fp_options_rec.res_class_raw_cost_sch_id;
1071 l_fp_use_planning_rt_flag := get_proj_fp_options_rec.use_planning_rates_flag;
1072 l_fp_rev_job_rate_sch_id := get_proj_fp_options_rec.rev_job_rate_sch_id;
1073 l_fp_cost_job_rate_sch_id := get_proj_fp_options_rec.cost_job_rate_sch_id;
1074 l_fp_rev_emp_rate_sch_id := get_proj_fp_options_rec.rev_emp_rate_sch_id;
1075 l_fp_cost_emp_rate_sch_id := get_proj_fp_options_rec.cost_emp_rate_sch_id;
1076 l_fp_rev_non_lab_rs_rt_sch_id := get_proj_fp_options_rec.rev_non_labor_res_rate_sch_id;
1077 l_fp_cost_non_lab_rs_rt_sch_id := get_proj_fp_options_rec.cost_non_labor_res_rate_sch_id;
1078 l_fp_cost_burden_rate_sch_id := get_proj_fp_options_rec.cost_burden_rate_sch_id;
1079 l_fp_budget_version_type := get_proj_fp_options_rec.version_type;
1080
1081 IF l_fp_budget_version_type = 'REVENUE' THEN
1082 x_calculate_mode := 'REVENUE';
1083 ELSIF l_fp_budget_version_type = 'COST' THEN
1084 x_calculate_mode := 'COST';
1085 ELSIF l_fp_budget_version_type = 'ALL' THEN
1086 x_calculate_mode := 'COST_REVENUE';
1087 END IF;
1088
1089 l_nlr_organization_id := NVL(l_organization_id,l_rate_organization_id);
1090
1091 IF l_txn_currency_code_override IS NULL THEN
1092 l_txn_currency_code_override := l_txn_currency_code;
1093 ELSE
1094 l_txn_currency_code := l_txn_currency_code_override;
1095 END IF;
1096
1097 --Calling the Get_planning_Rates api
1098 IF p_pa_debug_mode = 'Y' THEN
1099 pa_fp_gen_amount_utils.fp_debug
1100 (p_called_mode => p_called_mode,
1101 p_msg => 'Before calling
1102 pa_plan_revenue.Get_planning_Rates',
1103 p_module_name => l_module_name,
1104 p_log_level => 5);
1105 END IF;
1106 PA_PLAN_REVENUE.GET_PLANNING_RATES (
1107 p_project_id => g_project_id
1108 ,p_task_id => l_rate_task_id
1109 ,p_top_task_id => l_top_task_id
1110 ,p_person_id => l_person_id
1111 ,p_job_id => l_job_id
1112 ,p_bill_job_grp_id => l_bill_job_group_id
1113 ,p_resource_class => l_resource_class_code
1114 ,p_planning_resource_format => l_res_format_id
1115 ,p_use_planning_rates_flag => l_fp_use_planning_rt_flag
1116 ,p_rate_based_flag => l_rate_based_flag
1117 ,p_uom => l_unit_of_measure
1118 ,p_system_linkage => NULL
1119 ,p_project_organz_id => l_carrying_out_organization_id
1120 ,p_rev_res_class_rate_sch_id => l_fp_res_cl_bill_rate_sch_id
1121 ,p_cost_res_class_rate_sch_id => l_fp_res_cl_raw_cost_sch_id
1122 ,p_rev_task_nl_rate_sch_id => l_task_nl_std_bill_rt_sch_id
1123 ,p_rev_proj_nl_rate_sch_id => l_proj_nl_std_bill_rt_sch_id
1124 ,p_rev_job_rate_sch_id => l_job_bill_rate_sch_id
1125 ,p_rev_emp_rate_sch_id => l_emp_bill_rate_sch_id
1126 ,p_plan_rev_job_rate_sch_id => l_fp_rev_job_rate_sch_id
1127 ,p_plan_cost_job_rate_sch_id => l_fp_cost_job_rate_sch_id
1128 ,p_plan_rev_emp_rate_sch_id => l_fp_rev_emp_rate_sch_id
1129 ,p_plan_cost_emp_rate_sch_id => l_fp_cost_emp_rate_sch_id
1130 ,p_plan_rev_nlr_rate_sch_id => l_fp_rev_non_lab_rs_rt_sch_id
1131 ,p_plan_cost_nlr_rate_sch_id => l_fp_cost_non_lab_rs_rt_sch_id
1132 ,p_plan_burden_cost_sch_id => l_fp_cost_burden_rate_sch_id
1133 ,p_calculate_mode => l_calculate_mode
1134 ,p_mcb_flag => l_multi_currency_billing_flag
1135 ,p_cost_rate_multiplier => l_cost_rate_multiplier
1136 ,p_bill_rate_multiplier => l_bill_rate_multiplier
1137 ,p_quantity => l_txn_plan_quantity
1138 ,p_item_date => l_budget_lines_start_date
1139 ,p_cost_sch_type => l_cost_sch_type
1140 ,p_labor_sch_type => l_lab_sch_type
1141 ,p_non_labor_sch_type => l_non_labor_sch_type
1142 ,p_labor_schdl_discnt => NULL
1143 ,p_labor_bill_rate_org_id => l_lab_bill_rate_org_id
1144 ,p_labor_std_bill_rate_schdl => NULL
1145 ,p_labor_schdl_fixed_date => NULL
1146 ,p_assignment_id => l_assignment_id
1147 ,p_project_org_id => l_org_id
1148 ,p_project_type => l_project_type
1149 ,p_expenditure_type => nvl(l_expenditure_type,
1150 l_rate_expenditure_type)
1151 ,p_non_labor_resource => l_non_labor_resource
1152 ,p_incurred_by_organz_id => l_organization_id
1153 ,p_override_to_organz_id => l_organization_id
1154 ,p_expenditure_org_id => nvl(l_rate_expenditure_org_id,
1155 l_org_id)
1156 ,p_assignment_precedes_task => l_assign_precedes_task
1157 ,p_planning_transaction_id => l_budget_line_id
1158 ,p_task_bill_rate_org_id => l_task_bill_rate_org_id
1159 ,p_project_bill_rate_org_id => l_project_bill_rate_org_id
1160 ,p_nlr_organization_id => nvl(l_nlr_organization_id,
1161 l_carrying_out_organization_id)
1162 ,p_project_sch_date => l_project_sch_date
1163 ,p_task_sch_date => l_task_sch_date
1164 ,p_project_sch_discount => l_project_sch_discount
1165 ,p_task_sch_discount => l_task_sch_discount
1166 ,p_inventory_item_id => l_inventory_item_id
1167 ,p_BOM_resource_Id => l_bom_resource_id
1168 ,P_mfc_cost_type_id => l_mfc_cost_type_id
1169 ,P_item_category_id => l_item_category_id
1170 ,p_mfc_cost_source => l_mfc_cost_source
1171 ,p_cost_override_rate => l_rw_cost_rate_override
1172 ,p_revenue_override_rate => l_bill_rate_override
1173 ,p_override_burden_cost_rate => l_burden_cost_rate_override
1174 ,p_override_currency_code => l_txn_currency_code_override
1175 ,p_txn_currency_code => l_txn_currency_code
1176 ,p_raw_cost => l_txn_raw_cost
1177 ,p_burden_cost => l_txn_burdened_cost
1178 ,p_raw_revenue => l_txn_revenue
1179 ,x_bill_rate => x_bill_rate
1180 ,x_cost_rate => x_cost_rate
1181 ,x_burden_cost_rate => x_burden_cost_rate
1182 ,x_burden_multiplier => x_burden_multiplier
1183 ,x_raw_cost => x_raw_cost
1184 ,x_burden_cost => x_burden_cost
1185 ,x_raw_revenue => x_raw_revenue
1186 ,x_bill_markup_percentage => x_bill_markup_percentage
1187 ,x_cost_txn_curr_code => x_cost_txn_curr_code
1188 ,x_rev_txn_curr_code => x_rev_txn_curr_code
1189 ,x_raw_cost_rejection_code => x_raw_cost_rejection_code
1190 ,x_burden_cost_rejection_code => x_burden_cost_rejection_code
1191 ,x_revenue_rejection_code => x_revenue_rejection_code
1192 ,x_cost_ind_compiled_set_id => x_cost_ind_compiled_set_id
1193 ,x_return_status => x_return_status
1194 ,x_msg_data => x_msg_data
1195 ,x_msg_count => x_msg_count);
1196 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1197 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1198 END IF;
1199 IF p_pa_debug_mode = 'Y' THEN
1200 pa_fp_gen_amount_utils.fp_debug
1201 (p_called_mode => p_called_mode,
1202 p_msg => 'Status after calling
1203 pa_plan_revenue.Get_planning_Rates'
1204 ||x_return_status,
1205 p_module_name => l_module_name,
1206 p_log_level => 5);
1207 END IF;
1208 --dbms_output.put_line('Status of Get_planning_Rates api: '||X_RETURN_STATUS);
1209
1210 */
1211
1212 IF p_pa_debug_mode = 'Y' THEN
1213 PA_DEBUG.Reset_Curr_Function;
1214 END IF;
1215
1216 EXCEPTION
1217 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1218 l_msg_count := FND_MSG_PUB.count_msg;
1219 IF l_msg_count = 1 THEN
1220 PA_INTERFACE_UTILS_PUB.get_messages
1221 ( p_encoded => FND_API.G_TRUE,
1222 p_msg_index => 1,
1223 p_msg_count => l_msg_count,
1224 p_msg_data => l_msg_data,
1225 p_data => l_data,
1226 p_msg_index_out => l_msg_index_out);
1227 x_msg_data := l_data;
1228 x_msg_count := l_msg_count;
1229 ELSE
1230 x_msg_count := l_msg_count;
1231 END IF;
1232 ROLLBACK;
1233
1234 x_return_status := FND_API.G_RET_STS_ERROR;
1235 IF P_PA_DEBUG_MODE = 'Y' THEN
1236 pa_fp_gen_amount_utils.fp_debug
1237 (p_msg => 'Invalid Arguments Passed',
1238 p_module_name => l_module_name,
1239 p_log_level => 5);
1240 PA_DEBUG.Reset_Curr_Function;
1241 END IF;
1242 RAISE;
1243
1244 WHEN OTHERS THEN
1245 --dbms_output.put_line('inside excep');
1246 --dbms_output.put_line(SUBSTR(SQLERRM,1,240));
1247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1248 x_msg_data := SUBSTR(SQLERRM,1,240);
1249 FND_MSG_PUB.add_exc_msg
1250 ( p_pkg_name => 'PA_FP_GEN_AMOUNT_UTILS'
1251 ,p_procedure_name => 'GET_VALUES_FOR_PLANNING_RATE');
1252 IF p_pa_debug_mode = 'Y' THEN
1253 PA_DEBUG.Reset_Curr_Function;
1254 END IF;
1255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1256
1257 END GET_VALUES_FOR_PLANNING_RATE;
1258
1259 PROCEDURE FP_DEBUG
1260 (P_CALLED_MODE IN VARCHAR2,
1261 P_MSG IN VARCHAR2,
1262 P_MODULE_NAME IN VARCHAR2,
1263 P_LOG_LEVEL IN NUMBER) IS
1264 BEGIN
1265 pa_debug.g_err_stage := p_msg;
1266 IF p_called_mode = 'SELF_SERVICE' THEN
1267 pa_debug.write
1268 (x_module => p_module_name,
1269 x_msg => pa_debug.g_err_stage,
1270 x_log_level=> p_log_level);
1271 ELSIF p_called_mode = 'CONCURRENT' THEN
1272 pa_debug.write_file(x_msg => pa_debug.g_err_stage);
1273 END IF;
1274
1275 END FP_DEBUG;
1276
1277
1278 FUNCTION GET_ETC_START_DATE(P_BUDGET_VERSION_ID PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE)
1279 RETURN DATE IS
1280 x_etc_start_date PA_BUDGET_VERSIONS.ETC_START_DATE%TYPE;
1281 BEGIN
1282 SELECT etc_start_date
1283 INTO x_etc_start_date
1284 FROM pa_budget_versions
1285 WHERE budget_version_id = p_budget_version_id
1286 AND etc_start_date is not null;
1287
1288 RETURN x_etc_start_date;
1289
1290 EXCEPTION
1291 WHEN OTHERS THEN
1292 RETURN x_etc_start_date;
1293 END;
1294
1295 FUNCTION GET_ACTUALS_THRU_DATE(P_BUDGET_VERSION_ID PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE)
1296 RETURN DATE IS
1297 x_actuals_thru_date PA_BUDGET_VERSIONS.ETC_START_DATE%TYPE;
1298 BEGIN
1299 SELECT (etc_start_date)-1
1300 INTO x_actuals_thru_date
1301 FROM pa_budget_versions
1302 WHERE budget_version_id = p_budget_version_id
1303 AND etc_start_date is not null;
1304
1305 RETURN x_actuals_thru_date;
1306
1307 EXCEPTION
1308 WHEN OTHERS THEN
1309 RETURN x_actuals_thru_date;
1310 END;
1311
1312 FUNCTION GET_RL_UNCATEGORIZED_FLAG(P_RESOURCE_LIST_ID PA_BUDGET_VERSIONS.RESOURCE_LIST_ID%TYPE)
1313 RETURN VARCHAR2 IS
1314 x_rl_uncategorized_flag VARCHAR2(1);
1315 BEGIN
1316
1317 SELECT NVL(UNCATEGORIZED_FLAG,'N')
1318 INTO x_rl_uncategorized_flag
1319 FROM pa_resource_lists_all_bg
1320 WHERE resource_list_id = p_resource_list_id;
1321
1322 RETURN x_rl_uncategorized_flag;
1323
1324 EXCEPTION
1325 WHEN OTHERS THEN
1326 RETURN 'N';
1327 END;
1328
1329
1330 FUNCTION GET_UC_RES_LIST_RLM_ID(P_RESOURCE_LIST_ID PA_BUDGET_VERSIONS.RESOURCE_LIST_ID%TYPE,
1331 P_RESOURCE_CLASS_CODE pa_resource_list_members.RESOURCE_CLASS_CODE%TYPE)
1332 RETURN NUMBER IS
1333 x_uc_res_list_rlm_id NUMBER;
1334 BEGIN
1335
1336 SELECT resource_list_member_id
1337 INTO x_uc_res_list_rlm_id
1338 FROM pa_resource_list_members
1339 WHERE resource_class_code = P_RESOURCE_CLASS_CODE
1340 AND object_type = 'RESOURCE_LIST'
1341 AND resource_list_id = p_resource_list_id;
1342
1343 RETURN x_uc_res_list_rlm_id;
1344
1345 EXCEPTION
1346 WHEN OTHERS THEN
1347 RETURN -1;
1348 END;
1349
1350 FUNCTION GET_RLM_ID(P_PROJECT_ID PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
1351 P_RESOURCE_LIST_ID PA_BUDGET_VERSIONS.RESOURCE_LIST_ID%TYPE,
1352 P_RESOURCE_CLASS_CODE pa_resource_assignments.resource_class_code%type)
1353 RETURN NUMBER IS
1354 l_control_flag VARCHAR2(10);
1355 x_rlm_id NUMBER;
1356 BEGIN
1357 SELECT NVL(control_flag,'N')
1358 INTO l_control_flag
1359 FROM pa_resource_lists_all_bg
1360 WHERE resource_list_id = P_RESOURCE_LIST_ID;
1361
1362 IF l_control_flag = 'Y' THEN
1363 select resource_list_member_id
1364 into x_rlm_id
1365 from pa_resource_list_members
1366 where object_type = 'RESOURCE_LIST'
1367 and object_id = p_resource_list_id
1368 and resource_list_id = p_resource_list_id
1369 and resource_class_code = P_RESOURCE_CLASS_CODE
1370 and RESOURCE_CLASS_FLAG='Y';
1371 ELSE
1372 select resource_list_member_id
1373 into x_rlm_id
1374 from pa_resource_list_members
1375 where object_type = 'PROJECT'
1376 and object_id = p_project_id
1377 and resource_list_id = p_resource_list_id
1378 and resource_class_code = P_RESOURCE_CLASS_CODE
1379 and RESOURCE_CLASS_FLAG='Y';
1380 END IF;
1381
1382 RETURN x_rlm_id;
1383
1384 EXCEPTION
1385 WHEN OTHERS THEN
1386 RETURN -1;
1387 END;
1388
1389 /**This API is to validate all support cases in budget/forecast generation. *
1390 *As of 6/27/2005, the unsupported cases are:
1391 *1.Forecast/Budget generation from cost-disabled Workplan is not supported.
1392 * ADDED FOR ER 4391254:
1393 * The only exception is that we allow Budget generation from cost-disabled
1394 * Workplan if the Target is a Cost-only version, the Structure is 'Fully-
1395 * Shared', and the following source/target planning options are equal:
1396 * a)Resource List
1397 * b)Time phase
1398 * c)Planning Level
1399 * d)Multi-currency flag
1400 * Note that when the generation option is 'Task Level Selection', we will
1401 * raise an error only when at least one of the tasks has ETC generation
1402 * source as 'WORKPLAN_RESOURCES' or 'WORK_QUANTITY'.
1403 *2.For Forecast/Budget generation from Staffing Plan:
1404 * 1)Revenue versions can't be generated.
1405 * 2)Versions with Resource List of None can't be generated.
1406 * --Bug 5325254
1407 *3.Forecast generation from non-timephased Workplan is not supported.
1408 * Note: Earlier, we restricted forecast generation from non-timephased
1409 * financial plans as well. However, this restriction has been relaxed
1410 * to support CDM's requirements.
1411 *4.Forecast/Budget generation from:
1412 * ADDED FOR ER 4391321:
1413 * 1)Workplan and/or Financial Plan that has any rejection code in the
1414 * budget lines should result in a warning or error from the UI and
1415 * Concurrent Program, respectively.
1416 * 2)Staffing Plan that has any Forecast Items with ERROR_FLAG = 'Y'
1417 * should result in a warning or error from the UI and Concurrent
1418 * Program, respectively.
1419 *5.Forecast/Budget Generation,where Revenue Derivation Method of target
1420 * is different from source, is not supported. ER: 5152892
1421 *PARAMETERS:
1422 *
1423 *P_CALLING_CONTEXT
1424 *-----------------
1425 * 'CONCURRENT' : this api is being called from a Concurrent Program.
1426 * 'SELF_SERVICE': this api is being called from the Self-Service pages.
1427 *
1428 *Added for ER 4391321:
1429 *
1430 *P_CHECK_SRC_ERRORS_FLAG
1431 *-----------------------
1432 * 'Y': when source is FP or WP, check source budget line rejection codes.
1433 * when source is Staffing Plan, check ERROR_FLAG for forecast items.
1434 * 'N': do not check source rejection codes or ERROR_FLAG values.
1435 *By default, P_CHECK_SRC_ERRORS_FLAG is 'Y'.
1436 *
1437 *X_WARNING_MESSAGE
1438 *----------------------
1439 * NULL: P_CHECK_SRC_ERRORS_FLAG = 'N', OR
1440 * P_CHECK_SRC_ERRORS_FLAG = 'Y' and source passed rejection code /
1441 * ERROR_FLAG validation.
1442 * Otherwise, contains the translated warning message text.
1443 *X_WARNING_MESSAGE will be null whenever P_CALLING_CONTEXT = 'CONCURRENT'.
1444 **/
1445
1446
1447 PROCEDURE VALIDATE_SUPPORT_CASES
1448 (P_FP_COLS_REC_TGT IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
1449 P_CALLING_CONTEXT IN VARCHAR2,
1450 P_CHECK_SRC_ERRORS_FLAG IN VARCHAR2,
1451 X_WARNING_MESSAGE OUT NOCOPY VARCHAR2,
1452 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1453 X_MSG_COUNT OUT NOCOPY NUMBER,
1454 X_MSG_DATA OUT NOCOPY VARCHAR2)
1455 IS
1456 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_AMOUNT_UTILS.VALIDATE_SUPPORT_CASES';
1457
1458 l_wp_track_cost_flag VARCHAR2(1);
1459 l_rev_gen_method VARCHAR2(1);
1460 l_plan_class_code PA_FIN_PLAN_TYPES_B.PLAN_CLASS_CODE%TYPE;
1461
1462 l_source_wp_ver_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
1463 l_stru_sharing_code PA_PROJECTS_ALL.STRUCTURE_SHARING_CODE%TYPE;
1464 l_source_fp_ver_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
1465 l_fp_cols_rec_source PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
1466
1467 l_gen_src_code PA_PROJ_FP_OPTIONS.GEN_COST_SRC_CODE%TYPE;
1468 l_count NUMBER;
1469
1470 l_fp_cols_rec_tgt PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
1471 x_fp_cols_rec_tgt PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
1472
1473 -- This flag tracks if we still need to perform defaulting
1474 -- logic for the source version id. This flag is only relevant
1475 -- if the Target is a Budget.
1476 l_default_bdgt_src_ver_flag VARCHAR2(1);
1477
1478 l_uncategorized_flag PA_RESOURCE_LISTS_ALL_BG.UNCATEGORIZED_FLAG%TYPE;
1479
1480 l_dummy NUMBER;
1481
1482 l_msg_count NUMBER;
1483 l_data VARCHAR2(2000);
1484 l_msg_data VARCHAR2(2000);
1485 l_msg_index_out NUMBER;
1486
1487 -- ER 4391321: Variables for validation Case 4
1488 l_bl_rejection_code_count NUMBER;
1489 l_raise_error_flag VARCHAR2(1);
1490 l_pa_gl_token_value VARCHAR2(30);
1491 l_warning_message_code VARCHAR2(30);
1492
1493 lc_message_code_WP CONSTANT VARCHAR2(30) := 'WP';
1494 lc_message_code_FP CONSTANT VARCHAR2(30) := 'FP';
1495 lc_message_code_SP CONSTANT VARCHAR2(30) := 'SP';
1496 lc_message_code_WPFP CONSTANT VARCHAR2(30) := 'WPFP';
1497
1498 BEGIN
1499 IF p_pa_debug_mode = 'Y' THEN
1500 pa_debug.set_curr_function( p_function => 'VALIDATE_SUPPORT_CASES',
1501 p_debug_mode => p_pa_debug_mode);
1502 END IF;
1503 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1504 X_MSG_COUNT := 0;
1505 X_WARNING_MESSAGE := NULL;
1506
1507 -- Initialize local copy of target version details
1508 l_fp_cols_rec_tgt := P_FP_COLS_REC_TGT;
1509 l_default_bdgt_src_ver_flag := 'Y';
1510
1511 l_wp_track_cost_flag :=
1512 PA_FP_WP_GEN_AMT_UTILS.GET_WP_TRACK_COST_AMT_FLAG(l_fp_cols_rec_tgt.X_PROJECT_ID);
1513
1514 --l_rev_gen_method :=
1515 -- PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(l_fp_cols_rec_tgt.X_PROJECT_ID);
1516 l_rev_gen_method := nvl(l_fp_cols_rec_tgt.x_revenue_derivation_method,PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(l_fp_cols_rec_tgt.X_PROJECT_ID)); -- Bug 5462471
1517 l_plan_class_code := l_fp_cols_rec_tgt.X_PLAN_CLASS_CODE;
1518 IF l_plan_class_code = 'BUDGET' THEN
1519 l_gen_src_code := l_fp_cols_rec_tgt.X_GEN_SRC_CODE;
1520 ELSE
1521 l_gen_src_code := l_fp_cols_rec_tgt.X_GEN_ETC_SRC_CODE;
1522 END IF;
1523
1524 /* Case 1: Source is cost-disabled Workplan */
1525 IF l_wp_track_cost_flag = 'N' AND
1526 l_gen_src_code = 'WORKPLAN_RESOURCES' THEN
1527
1528 -- Added for ER 4391254
1529 IF l_plan_class_code = 'BUDGET' THEN
1530
1531 l_stru_sharing_code :=
1532 PA_PROJECT_STRUCTURE_UTILS.GET_STRUCTURE_SHARING_CODE
1533 ( p_project_id => l_fp_cols_rec_tgt.X_PROJECT_ID );
1534
1535 -- For the special case when the Target is a Cost-only Budget
1536 -- and the structure is Fully Shared, try to default the source
1537 -- version if it is Null and we have not done defaulting earlier.
1538
1539 IF l_fp_cols_rec_tgt.x_version_type = 'COST' AND
1540 l_stru_sharing_code = 'SHARE_FULL' AND
1541 l_default_bdgt_src_ver_flag = 'Y' AND
1542 l_fp_cols_rec_tgt.X_GEN_SRC_WP_VERSION_ID IS NULL THEN
1543
1544 IF p_pa_debug_mode = 'Y' THEN
1545 pa_fp_gen_amount_utils.fp_debug
1546 (p_called_mode => p_calling_context,
1547 p_msg => 'Before calling
1548 PA_FP_GEN_AMOUNT_UTILS.DEFAULT_BDGT_SRC_VER',
1549 p_module_name => l_module_name,
1550 p_log_level => 5);
1551 END IF;
1552 /* The version defaulting API passes updated Target version details
1553 * record back as an OUT parameter. */
1554 PA_FP_GEN_AMOUNT_UTILS.DEFAULT_BDGT_SRC_VER
1555 ( P_FP_COLS_REC_TGT => l_fp_cols_rec_tgt,
1556 P_CALLING_CONTEXT => p_calling_context,
1557 X_FP_COLS_REC_TGT => x_fp_cols_rec_tgt,
1558 X_RETURN_STATUS => X_RETURN_STATUS,
1559 X_MSG_COUNT => X_MSG_COUNT,
1560 X_MSG_DATA => X_MSG_DATA );
1561 IF p_pa_debug_mode = 'Y' THEN
1562 pa_fp_gen_amount_utils.fp_debug
1563 (p_called_mode => p_calling_context,
1564 p_msg => 'Status after calling
1565 PA_FP_GEN_AMOUNT_UTILS.DEFAULT_BDGT_SRC_VER: '
1566 ||x_return_status,
1567 p_module_name => l_module_name,
1568 p_log_level => 5);
1569 END IF;
1570 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1571 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1572 END IF;
1573 l_fp_cols_rec_tgt := x_fp_cols_rec_tgt;
1574 l_default_bdgt_src_ver_flag := 'N';
1575 END IF; -- defaulting logic
1576
1577 l_source_wp_ver_id := l_fp_cols_rec_tgt.X_GEN_SRC_WP_VERSION_ID;
1578
1579 -- Defaulting logic should raise an error if source version is Null.
1580 -- However, we sill still check that it is not Null to be cautious.
1581
1582 IF l_fp_cols_rec_tgt.x_version_type = 'COST' AND
1583 l_source_wp_ver_id IS NOT NULL AND
1584 l_stru_sharing_code = 'SHARE_FULL' THEN
1585 /* Get version details for Source Workplan */
1586 IF p_pa_debug_mode = 'Y' THEN
1587 pa_fp_gen_amount_utils.fp_debug
1588 (p_called_mode => p_calling_context,
1589 p_msg => 'Before calling
1590 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS',
1591 p_module_name => l_module_name,
1592 p_log_level => 5);
1593 END IF;
1594 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS
1595 ( P_PROJECT_ID => l_fp_cols_rec_tgt.X_PROJECT_ID,
1596 P_BUDGET_VERSION_ID => l_source_wp_ver_id,
1597 X_FP_COLS_REC => l_fp_cols_rec_source,
1598 X_RETURN_STATUS => X_RETURN_STATUS,
1599 X_MSG_COUNT => X_MSG_COUNT,
1600 X_MSG_DATA => X_MSG_DATA );
1601 IF p_pa_debug_mode = 'Y' THEN
1602 pa_fp_gen_amount_utils.fp_debug
1603 (p_called_mode => p_calling_context,
1604 p_msg => 'Status after calling
1605 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS: '
1606 ||x_return_status,
1607 p_module_name => l_module_name,
1608 p_log_level => 5);
1609 END IF;
1610 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1611 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1612 END IF;
1613
1614 /* If source/target planning options are equal, then this
1615 * is a supported generation, so do nothing, and proceed
1616 * with checking remaining validation cases. Otherwise, the
1617 * generation is not supported, so Raise and error. */
1618
1619 IF (l_fp_cols_rec_tgt.X_RESOURCE_LIST_ID =
1620 l_fp_cols_rec_source.X_RESOURCE_LIST_ID
1621 AND l_fp_cols_rec_tgt.X_TIME_PHASED_CODE =
1622 l_fp_cols_rec_source.X_TIME_PHASED_CODE
1623 AND l_fp_cols_rec_tgt.X_FIN_PLAN_LEVEL_CODE =
1624 l_fp_cols_rec_source.X_FIN_PLAN_LEVEL_CODE
1625 AND l_fp_cols_rec_tgt.X_PLAN_IN_MULTI_CURR_FLAG =
1626 l_fp_cols_rec_source.X_PLAN_IN_MULTI_CURR_FLAG) THEN
1627 l_dummy := 1;
1628 ELSE
1629 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1630 p_msg_name => 'PA_BDGT_WP_CST_DIS_ERR');
1631 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1632 END IF;
1633 ELSE
1634 /* This budget does not satisfy the special exception conditions.
1635 * Thus, this generation scenario is not supported. Raise an error. */
1636
1637 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1638 p_msg_name => 'PA_BDGT_WP_CST_DIS_ERR');
1639 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1640 END IF; -- l_source_wp_ver_id NULL check
1641 -- End changes for ER 4391254
1642 ELSE
1643 -- l_plan_class_code = 'FORECAST':
1644 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1645 p_msg_name => 'PA_FCST_WP_CST_DIS_ERR');
1646 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1647 END IF; -- budget/forecast logic
1648 END IF;
1649 IF l_wp_track_cost_flag = 'N' AND
1650 l_gen_src_code = 'TASK_LEVEL_SEL' THEN
1651 SELECT COUNT(*) INTO l_count
1652 FROM pa_tasks
1653 WHERE project_id = l_fp_cols_rec_tgt.X_PROJECT_ID
1654 AND (gen_etc_source_code = 'WORKPLAN_RESOURCES'
1655 OR gen_etc_source_code = 'WORK_QUANTITY')
1656 AND rownum < 2;
1657
1658 IF l_count > 0 THEN
1659 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1660 p_msg_name => 'PA_FCST_WP_CST_DIS_ERR');
1661 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1662 END IF;
1663 END IF; -- Validation Case 1
1664
1665
1666 /* Case 2.1: Source is Staffing Plan and Target is a Revenue-only version */
1667 IF l_gen_src_code = 'RESOURCE_SCHEDULE' AND
1668 l_fp_cols_rec_tgt.X_VERSION_TYPE = 'REVENUE' THEN
1669 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1670 p_msg_name => 'PA_RES_SCH_REV_ERR');
1671 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1672 END IF; -- Validation Case 2.1
1673
1674 /* Case 2.2: Source is Staffing Plan and Target Resource List is None */
1675 IF l_gen_src_code = 'RESOURCE_SCHEDULE' THEN
1676 BEGIN
1677 SELECT nvl(UNCATEGORIZED_FLAG,'N')
1678 INTO l_uncategorized_flag
1679 FROM pa_resource_lists
1680 WHERE resource_list_id = l_fp_cols_rec_tgt.X_RESOURCE_LIST_ID;
1681 EXCEPTION
1682 WHEN NO_DATA_FOUND THEN
1683 l_uncategorized_flag := 'N';
1684 END;
1685 /* Uncategorized flag of 'Y' implies resource list is None */
1686 IF l_uncategorized_flag = 'Y' THEN
1687 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1688 p_msg_name => 'PA_RES_SCH_UNCAT_RES_LIST_ERR');
1689 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1690 END IF;
1691 END IF; -- Validation Case 2.2
1692
1693 /* Case 3: Source is WP with time phase = None, Target is a forecast */
1694 IF l_plan_class_code = 'FORECAST' AND
1695 (l_gen_src_code = 'WORKPLAN_RESOURCES' OR
1696 --l_gen_src_code = 'FINANCIAL_PLAN' OR --Bug 5325254
1697 l_gen_src_code = 'TASK_LEVEL_SEL') THEN
1698
1699 -- If source version id is null, we cannot check source time phase
1700 l_source_wp_ver_id := l_fp_cols_rec_tgt.X_GEN_SRC_WP_VERSION_ID;
1701 --l_source_fp_ver_id := l_fp_cols_rec_tgt.X_GEN_SRC_PLAN_VERSION_ID; --Bug 5325254
1702
1703 /* Do Workplan Source Validation */
1704 IF l_source_wp_ver_id IS NOT NULL AND
1705 l_gen_src_code IN ('WORKPLAN_RESOURCES','TASK_LEVEL_SEL') THEN
1706
1707 IF l_gen_src_code = 'TASK_LEVEL_SEL' THEN
1708 SELECT COUNT(*) INTO l_count
1709 FROM pa_tasks
1710 WHERE project_id = l_fp_cols_rec_tgt.X_PROJECT_ID
1711 AND (gen_etc_source_code = 'WORKPLAN_RESOURCES'
1712 OR gen_etc_source_code = 'WORK_QUANTITY')
1713 AND rownum < 2;
1714 END IF;
1715
1716 IF l_gen_src_code = 'WORKPLAN_RESOURCES' OR
1717 (l_gen_src_code = 'TASK_LEVEL_SEL' AND l_count > 0) THEN
1718
1719 /* Get version details for Source Workplan */
1720 IF p_pa_debug_mode = 'Y' THEN
1721 pa_fp_gen_amount_utils.fp_debug
1722 (p_called_mode => p_calling_context,
1723 p_msg => 'Before calling
1724 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS',
1725 p_module_name => l_module_name,
1726 p_log_level => 5);
1727 END IF;
1728 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS
1729 (P_PROJECT_ID => l_fp_cols_rec_tgt.X_PROJECT_ID,
1730 P_BUDGET_VERSION_ID => l_source_wp_ver_id,
1731 X_FP_COLS_REC => l_fp_cols_rec_source,
1732 X_RETURN_STATUS => X_RETURN_STATUS,
1733 X_MSG_COUNT => X_MSG_COUNT,
1734 X_MSG_DATA => X_MSG_DATA);
1735 IF p_pa_debug_mode = 'Y' THEN
1736 pa_fp_gen_amount_utils.fp_debug
1737 (p_called_mode => p_calling_context,
1738 p_msg => 'Status after calling
1739 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS: '
1740 ||x_return_status,
1741 p_module_name => l_module_name,
1742 p_log_level => 5);
1743 END IF;
1744 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1745 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1746 END IF;
1747
1748 IF l_fp_cols_rec_source.x_time_phased_code = 'N' THEN
1749 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1750 p_msg_name => 'PA_WP_FP_NON_TIME_PHASED_ERR');
1751 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1752 END IF;
1753 END IF;
1754 END IF; -- Workplan Source Validation
1755
1756 /*Bug 5325254 : NO LONGER Do Financial Plan Source Validation */
1757 /******************** BEGIN COMMENTING ********************
1758 IF l_source_fp_ver_id IS NOT NULL AND
1759 l_gen_src_code IN ('FINANCIAL_PLAN','TASK_LEVEL_SEL') THEN
1760
1761 IF l_gen_src_code = 'TASK_LEVEL_SEL' THEN
1762 SELECT COUNT(*) INTO l_count
1763 FROM pa_tasks
1764 WHERE project_id = l_fp_cols_rec_tgt.X_PROJECT_ID
1765 AND gen_etc_source_code = 'FINANCIAL_PLAN'
1766 AND rownum < 2;
1767 END IF;
1768
1769 IF l_gen_src_code = 'FINANCIAL_PLAN' OR
1770 (l_gen_src_code = 'TASK_LEVEL_SEL' AND l_count > 0) THEN
1771
1772 -- Get version details for Source Financial Plan
1773 IF p_pa_debug_mode = 'Y' THEN
1774 pa_fp_gen_amount_utils.fp_debug
1775 (p_called_mode => p_calling_context,
1776 p_msg => 'Before calling
1777 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS',
1778 p_module_name => l_module_name,
1779 p_log_level => 5);
1780 END IF;
1781 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS
1782 (P_PROJECT_ID => l_fp_cols_rec_tgt.X_PROJECT_ID,
1783 P_BUDGET_VERSION_ID => l_source_fp_ver_id,
1784 X_FP_COLS_REC => l_fp_cols_rec_source,
1785 X_RETURN_STATUS => X_RETURN_STATUS,
1786 X_MSG_COUNT => X_MSG_COUNT,
1787 X_MSG_DATA => X_MSG_DATA);
1788 IF p_pa_debug_mode = 'Y' THEN
1789 pa_fp_gen_amount_utils.fp_debug
1790 (p_called_mode => p_calling_context,
1791 p_msg => 'Status after calling
1792 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS: '
1793 ||x_return_status,
1794 p_module_name => l_module_name,
1795 p_log_level => 5);
1796 END IF;
1797 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1798 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1799 END IF;
1800
1801 IF l_fp_cols_rec_source.x_time_phased_code = 'N' THEN
1802 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1803 p_msg_name => 'PA_WP_FP_NON_TIME_PHASED_ERR');
1804 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1805 END IF;
1806 END IF;
1807 END IF; -- Financial Plan Source Validation
1808 ********************* END COMMENTING ********************/
1809
1810 END IF; -- Validation Case 3
1811
1812 /* Default budget source version if needed.
1813 * Note that we did this earlier inside Case 1, where it was necessary
1814 * to selectively call the defaulting API based on the case introduced
1815 * for ER 4391254.
1816 * At this point, we are doing defaulting for the remainder of the API.
1817 * FUTURE VALIDATION CASES THAT NEED SPECIAL ORDERING WITH RESPECT TO
1818 * THE DEFAULTING LOGIC SHOULD BE PLACED ABOVE THIS POINT. */
1819
1820 IF l_plan_class_code = 'BUDGET' THEN
1821
1822 -- Try to default the source version if both the WP and FP source
1823 -- versions are Null and we have not done defaulting earlier.
1824 IF l_default_bdgt_src_ver_flag = 'Y' AND
1825 l_fp_cols_rec_tgt.X_GEN_SRC_WP_VERSION_ID IS NULL AND
1826 l_fp_cols_rec_tgt.X_GEN_SRC_PLAN_VERSION_ID IS NULL THEN
1827
1828 IF p_pa_debug_mode = 'Y' THEN
1829 pa_fp_gen_amount_utils.fp_debug
1830 (p_called_mode => p_calling_context,
1831 p_msg => 'Before calling
1832 PA_FP_GEN_AMOUNT_UTILS.DEFAULT_BDGT_SRC_VER',
1833 p_module_name => l_module_name,
1834 p_log_level => 5);
1835 END IF;
1836 /* The version defaulting API passes updated Target version details
1837 * record back as an OUT parameter. */
1838 PA_FP_GEN_AMOUNT_UTILS.DEFAULT_BDGT_SRC_VER
1839 ( P_FP_COLS_REC_TGT => l_fp_cols_rec_tgt,
1840 P_CALLING_CONTEXT => p_calling_context,
1841 X_FP_COLS_REC_TGT => x_fp_cols_rec_tgt,
1842 X_RETURN_STATUS => X_RETURN_STATUS,
1843 X_MSG_COUNT => X_MSG_COUNT,
1844 X_MSG_DATA => X_MSG_DATA );
1845 IF p_pa_debug_mode = 'Y' THEN
1846 pa_fp_gen_amount_utils.fp_debug
1847 (p_called_mode => p_calling_context,
1848 p_msg => 'Status after calling
1849 PA_FP_GEN_AMOUNT_UTILS.DEFAULT_BDGT_SRC_VER: '
1850 ||x_return_status,
1851 p_module_name => l_module_name,
1852 p_log_level => 5);
1853 END IF;
1854 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1855 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1856 END IF;
1857
1858 l_fp_cols_rec_tgt := x_fp_cols_rec_tgt;
1859
1860 END IF; -- defaulting logic
1861 END IF; -- l_plan_class_code check
1862
1863 -- Whether we had to default the budget source version or not,
1864 -- we no longer need to worry about defaulting it after this point.
1865 l_default_bdgt_src_ver_flag := 'N';
1866
1867 -- ADDED FOR ER 4391321:
1868 /* Case 4: Check if Forecast/Budget generation from source that has errors */
1869 IF P_CHECK_SRC_ERRORS_FLAG = 'Y' THEN
1870
1871 -- Initialize the warning message code local variable to Null.
1872 -- We will use this to track the error state regardless of calling
1873 -- context. Further processing will be based on this variable.
1874 l_warning_message_code := NULL;
1875
1876 /* Case 4.1: Check WP/FP source budget lines do not have rejection codes. */
1877 IF l_gen_src_code = 'WORKPLAN_RESOURCES' OR
1878 l_gen_src_code = 'FINANCIAL_PLAN' OR
1879 l_gen_src_code = 'TASK_LEVEL_SEL' THEN
1880
1881 -- If source version id is null, we cannot check budget line rejection codes
1882 l_source_wp_ver_id := l_fp_cols_rec_tgt.X_GEN_SRC_WP_VERSION_ID;
1883 l_source_fp_ver_id := l_fp_cols_rec_tgt.X_GEN_SRC_PLAN_VERSION_ID;
1884
1885 /* Do Workplan Source Validation */
1886 IF l_source_wp_ver_id IS NOT NULL AND
1887 l_gen_src_code IN ('WORKPLAN_RESOURCES','TASK_LEVEL_SEL') THEN
1888
1889 -- When l_gen_src_code is 'WORKPLAN_RESOURCES', all target resources
1890 -- are generated from the source Workplan. When l_gen_src_code is
1891 -- 'TASK_LEVEL_SEL', target resources are generated from the source
1892 -- specified by the gen_etc_source_code of the target task. Thus, in
1893 -- the latter case, we need to check if any tasks are generated by
1894 -- the source Workplan.
1895
1896 IF l_gen_src_code = 'TASK_LEVEL_SEL' THEN
1897 BEGIN
1898 SELECT 1 INTO l_count
1899 FROM DUAL
1900 WHERE EXISTS
1901 ( SELECT null
1902 FROM pa_tasks
1903 WHERE project_id = l_fp_cols_rec_tgt.X_PROJECT_ID
1904 AND gen_etc_source_code IN ('WORKPLAN_RESOURCES','WORK_QUANTITY') );
1905 EXCEPTION
1906 WHEN NO_DATA_FOUND THEN
1907 l_count := 0;
1908 END; -- select l_count
1909 END IF;
1910
1911 IF l_gen_src_code = 'WORKPLAN_RESOURCES' OR
1912 (l_gen_src_code = 'TASK_LEVEL_SEL' AND l_count > 0) THEN
1913
1914 /* Check if any wp source budget line has a non-null rejection code */
1915 BEGIN
1916 SELECT 1 INTO l_bl_rejection_code_count
1917 FROM DUAL
1918 WHERE EXISTS
1919 ( SELECT null
1920 FROM pa_budget_lines
1921 WHERE budget_version_id = l_source_wp_ver_id
1922 AND ( cost_rejection_code IS NOT NULL
1923 OR revenue_rejection_code IS NOT NULL
1924 OR burden_rejection_code IS NOT NULL
1925 OR other_rejection_code IS NOT NULL
1926 OR pc_cur_conv_rejection_code IS NOT NULL
1927 OR pfc_cur_conv_rejection_code IS NOT NULL ) );
1928 EXCEPTION
1929 WHEN NO_DATA_FOUND THEN
1930 l_bl_rejection_code_count := 0;
1931 END; -- select l_bl_rejection_code_count
1932
1933 IF l_bl_rejection_code_count > 0 THEN
1934 l_warning_message_code := lc_message_code_WP;
1935 END IF; -- rejection code count check
1936 END IF;
1937 END IF; -- Workplan Source Validation
1938
1939 /* Do Financial Plan Source Validation */
1940 IF l_source_fp_ver_id IS NOT NULL AND
1941 l_gen_src_code IN ('FINANCIAL_PLAN','TASK_LEVEL_SEL') THEN
1942
1943 -- When l_gen_src_code is 'FINANCIAL_PLAN', all target resources are
1944 -- generated from the source Financial Plan. When l_gen_src_code is
1945 -- 'TASK_LEVEL_SEL', target resources are generated from the source
1946 -- specified by the gen_etc_source_code of the target task. Thus, in
1947 -- the latter case, we need to check if any tasks are generated by
1948 -- the source Financial Plan.
1949
1950 IF l_gen_src_code = 'TASK_LEVEL_SEL' THEN
1951 BEGIN
1952 SELECT 1 INTO l_count
1953 FROM DUAL
1954 WHERE EXISTS
1955 ( SELECT null
1956 FROM pa_tasks
1957 WHERE project_id = l_fp_cols_rec_tgt.X_PROJECT_ID
1958 AND gen_etc_source_code = 'FINANCIAL_PLAN' );
1959 EXCEPTION
1960 WHEN NO_DATA_FOUND THEN
1961 l_count := 0;
1962 END; -- select l_count
1963 END IF;
1964
1965 IF l_gen_src_code = 'FINANCIAL_PLAN' OR
1966 (l_gen_src_code = 'TASK_LEVEL_SEL' AND l_count > 0) THEN
1967
1968 /* Check if any fp source budget line has a non-null rejection code */
1969 BEGIN
1970 SELECT 1 INTO l_bl_rejection_code_count
1971 FROM DUAL
1972 WHERE EXISTS
1973 ( SELECT null
1974 FROM pa_budget_lines
1975 WHERE budget_version_id = l_source_fp_ver_id
1976 AND ( cost_rejection_code IS NOT NULL
1977 OR revenue_rejection_code IS NOT NULL
1978 OR burden_rejection_code IS NOT NULL
1979 OR other_rejection_code IS NOT NULL
1980 OR pc_cur_conv_rejection_code IS NOT NULL
1981 OR pfc_cur_conv_rejection_code IS NOT NULL ) );
1982 EXCEPTION
1983 WHEN NO_DATA_FOUND THEN
1984 l_bl_rejection_code_count := 0;
1985 END; -- select l_bl_rejection_code_count
1986
1987 IF l_bl_rejection_code_count > 0 THEN
1988 /* If Target is a Forecast version with source as
1989 * Task-Level Selection, both WP and FP sources may
1990 * have budget lines with rejection codes. Check this. */
1991 IF l_warning_message_code = lc_message_code_WP THEN
1992 l_warning_message_code := lc_message_code_WPFP;
1993 ELSE
1994 l_warning_message_code := lc_message_code_FP;
1995 END IF;
1996 END IF; -- rejection code count check
1997 END IF;
1998 END IF; -- Financial Plan Source Validation
1999
2000 IF l_warning_message_code IS NOT NULL THEN
2001 -- At this point, l_warning_message_code can be either:
2002 -- lc_message_code_WP, lc_message_code_FP, or lc_message_code_WPFP.
2003 -- Push the appropriate error message onto the stack depending
2004 -- on the Calling Context. Raise an exception if the context is
2005 -- 'CONCURRENT' (program).
2006
2007 IF l_warning_message_code = lc_message_code_WP THEN
2008 IF p_calling_context = 'SELF_SERVICE' THEN
2009 PA_UTILS.ADD_MESSAGE
2010 ( p_app_short_name => 'PA',
2011 p_msg_name => 'PA_SRC_WP_REJ_CODE_WARN' );
2012 ELSIF p_calling_context = 'CONCURRENT' THEN
2013 PA_UTILS.ADD_MESSAGE
2014 ( p_app_short_name => 'PA',
2015 p_msg_name => 'PA_SRC_WP_REJ_CODE_ERR' );
2016 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2017 END IF;
2018 ELSIF l_warning_message_code = lc_message_code_FP THEN
2019 IF p_calling_context = 'SELF_SERVICE' THEN
2020 PA_UTILS.ADD_MESSAGE
2021 ( p_app_short_name => 'PA',
2022 p_msg_name => 'PA_SRC_FP_REJ_CODE_WARN' );
2023 ELSIF p_calling_context = 'CONCURRENT' THEN
2024 PA_UTILS.ADD_MESSAGE
2025 ( p_app_short_name => 'PA',
2026 p_msg_name => 'PA_SRC_FP_REJ_CODE_ERR' );
2027 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2028 END IF;
2029 ELSIF l_warning_message_code = lc_message_code_WPFP THEN
2030 IF p_calling_context = 'SELF_SERVICE' THEN
2031 PA_UTILS.ADD_MESSAGE
2032 ( p_app_short_name => 'PA',
2033 p_msg_name => 'PA_SRC_WPFP_REJ_CODE_WARN' );
2034 ELSIF p_calling_context = 'CONCURRENT' THEN
2035 PA_UTILS.ADD_MESSAGE
2036 ( p_app_short_name => 'PA',
2037 p_msg_name => 'PA_SRC_WPFP_REJ_CODE_ERR' );
2038 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2039 END IF;
2040 END IF; -- l_warning_message_code check
2041 END IF; -- l_warning_message_code is not null
2042
2043 END IF; -- Validation Case 4.1
2044
2045
2046 /* Case 4.2: Check Staffing Plan source forecast items do not have ERROR_FLAG = 'Y'. */
2047
2048 IF l_gen_src_code = 'RESOURCE_SCHEDULE' THEN
2049
2050 BEGIN
2051 SELECT 1 INTO l_count
2052 FROM DUAL
2053 WHERE EXISTS
2054 ( SELECT null
2055 FROM PA_FORECAST_ITEMS
2056 WHERE project_id = l_fp_cols_rec_tgt.X_PROJECT_ID
2057 AND error_flag = 'Y' );
2058 EXCEPTION
2059 WHEN NO_DATA_FOUND THEN
2060 l_count := 0;
2061 END; -- select l_count
2062
2063 IF l_count > 0 THEN
2064
2065 l_warning_message_code := lc_message_code_SP;
2066
2067 /* The error flag can only be 'Y' if the Target timephasing
2068 * is PA or GL. Set the message token value appropriately. */
2069 l_pa_gl_token_value := null;
2070 IF l_fp_cols_rec_tgt.x_time_phased_code = 'P' THEN
2071 l_pa_gl_token_value := 'PA';
2072 ELSIF l_fp_cols_rec_tgt.x_time_phased_code = 'G' THEN
2073 l_pa_gl_token_value := 'GL';
2074 END IF;
2075
2076 IF p_calling_context = 'SELF_SERVICE' THEN
2077 PA_UTILS.ADD_MESSAGE
2078 ( p_app_short_name => 'PA',
2079 p_msg_name => 'PA_SRC_SP_ERROR_FLAG_WARN',
2080 p_token1 => 'PA_GL',
2081 p_value1 => l_pa_gl_token_value );
2082 ELSIF p_calling_context = 'CONCURRENT' THEN
2083 PA_UTILS.ADD_MESSAGE
2084 ( p_app_short_name => 'PA',
2085 p_msg_name => 'PA_SRC_SP_ERROR_FLAG_ERR',
2086 p_token1 => 'PA_GL',
2087 p_value1 => l_pa_gl_token_value );
2088 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2089 END IF;
2090 END IF;
2091
2092 END IF; -- Validation Case 4.2
2093
2094 --Case 5: Forecast/Budget Generation,where Revenue Derivation Method of target
2095 --is different from source, is not supported. ER: 5152892
2096
2097 IF (l_fp_cols_rec_tgt.X_GEN_SRC_PLAN_VERSION_ID IS NOT NULL
2098 AND l_gen_src_code = 'FINANCIAL_PLAN'
2099 AND l_fp_cols_rec_tgt.X_VERSION_TYPE IN ('REVENUE','ALL')) THEN
2100
2101 -- Get version details for Source Financial Plan
2102 IF p_pa_debug_mode = 'Y' THEN
2103 pa_fp_gen_amount_utils.fp_debug
2104 (p_called_mode => p_calling_context,
2105 p_msg => 'Before calling
2106 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS',
2107 p_module_name => l_module_name,
2108 p_log_level => 5);
2109 END IF;
2110 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS
2111 (P_PROJECT_ID => l_fp_cols_rec_tgt.X_PROJECT_ID,
2112 P_BUDGET_VERSION_ID => l_fp_cols_rec_tgt.X_GEN_SRC_PLAN_VERSION_ID,
2113 X_FP_COLS_REC => l_fp_cols_rec_source,
2114 X_RETURN_STATUS => X_RETURN_STATUS,
2115 X_MSG_COUNT => X_MSG_COUNT,
2116 X_MSG_DATA => X_MSG_DATA);
2117 IF p_pa_debug_mode = 'Y' THEN
2118 pa_fp_gen_amount_utils.fp_debug
2119 (p_called_mode => p_calling_context,
2120 p_msg => 'Status after calling
2121 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS: '
2122 ||x_return_status,
2123 p_module_name => l_module_name,
2124 p_log_level => 5);
2125 END IF;
2126 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2127 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2128 END IF;
2129 IF (l_fp_cols_rec_source.X_VERSION_TYPE = 'ALL'
2130 AND nvl(l_fp_cols_rec_source.x_revenue_derivation_method,'W')
2131 <>nvl(l_fp_cols_rec_tgt.x_revenue_derivation_method,'W')) THEN
2132 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2133 p_msg_name => 'PA_REV_DER_MTD_DIFF');
2134 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2135 END IF;
2136 END IF; -- Case 5 validation
2137
2138 /* If the validation fails, the transaction should be rolled back.
2139 * When the context is Concurrent Program, an Exception is raised,
2140 * so the exception handler will perform the rollback. When the
2141 * context is Self-Service, we report the error by using the OUT
2142 * parameter X_WARNING_MESSAGE without raising an Exception.
2143 * We handle the latter case below by performing the rollback and
2144 * fetching the translated message text. */
2145
2146 IF l_warning_message_code IS NOT NULL AND
2147 p_calling_context = 'SELF_SERVICE' THEN
2148
2149 l_msg_count := FND_MSG_PUB.count_msg;
2150
2151 -- Error handling logic.
2152 -- 2 possibilities:
2153 -- 1) This API was called with non-empty message stack.
2154 -- 2) This API pushed multiple messages onto the stack.
2155 -- In both cases, we should only have 1 message on the stack.
2156 IF l_msg_count <> 1 THEN
2157 IF P_PA_DEBUG_MODE = 'Y' THEN
2158 pa_fp_gen_amount_utils.fp_debug
2159 ( p_called_mode => p_calling_context,
2160 p_msg => 'Source data contains errors, but the number of ' ||
2161 'messages on the error stack is not equal to 1.',
2162 p_module_name => l_module_name,
2163 p_log_level => 5 );
2164 END IF;
2165 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2166 END IF;
2167
2168 PA_INTERFACE_UTILS_PUB.get_messages
2169 ( p_encoded => FND_API.G_FALSE, --TRUE,
2170 p_msg_index => 1,
2171 --p_msg_count => 1,
2172 --p_msg_data => l_msg_data ,
2173 p_data => X_WARNING_MESSAGE,
2174 p_msg_index_out => l_msg_index_out);
2175
2176 IF P_PA_DEBUG_MODE = 'Y' THEN
2177 PA_DEBUG.RESET_CURR_FUNCTION;
2178 END IF;
2179
2180 ROLLBACK;
2181 RETURN;
2182 END IF;
2183
2184 END IF; -- P_CHECK_SRC_ERRORS_FLAG check
2185
2186 IF P_PA_DEBUG_MODE = 'Y' THEN
2187 PA_DEBUG.RESET_CURR_FUNCTION;
2188 END IF;
2189 EXCEPTION
2190 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2191 l_msg_count := FND_MSG_PUB.count_msg;
2192 IF l_msg_count = 1 THEN
2193 PA_INTERFACE_UTILS_PUB.get_messages
2194 ( p_encoded => FND_API.G_TRUE,
2195 p_msg_index => 1,
2196 p_msg_count => l_msg_count,
2197 p_msg_data => l_msg_data,
2198 p_data => l_data,
2199 p_msg_index_out => l_msg_index_out);
2200 x_msg_data := l_data;
2201 x_msg_count := l_msg_count;
2202 ELSE
2203 x_msg_count := l_msg_count;
2204 END IF;
2205
2206 x_return_status := FND_API.G_RET_STS_ERROR;
2207
2208 IF P_PA_DEBUG_MODE = 'Y' THEN
2209 pa_fp_gen_amount_utils.fp_debug
2210 ( p_called_mode => p_calling_context,
2211 p_msg => 'Invalid Arguments Passed',
2212 p_module_name => l_module_name,
2213 p_log_level => 5 );
2214 PA_DEBUG.RESET_CURR_FUNCTION;
2215 END IF;
2216
2217 ROLLBACK;
2218 RAISE;
2219 WHEN OTHERS THEN
2220 rollback;
2221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2222 x_msg_count := 1;
2223 x_msg_data := substr(sqlerrm,1,240);
2224 FND_MSG_PUB.add_exc_msg
2225 ( p_pkg_name => 'PA_FP_GEN_AMOUNT_UTILS',
2226 p_procedure_name => 'VALIDATE_SUPPORT_CASES',
2227 p_error_text => substr(sqlerrm,1,240));
2228
2229 IF P_PA_DEBUG_MODE = 'Y' THEN
2230 pa_fp_gen_amount_utils.fp_debug
2231 (p_called_mode => p_calling_context,
2232 p_msg => 'Unexpected Error'||substr(sqlerrm,1,240),
2233 p_module_name => l_module_name,
2234 p_log_level => 5);
2235 PA_DEBUG.RESET_CURR_FUNCTION;
2236 END IF;
2237 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2238 END VALIDATE_SUPPORT_CASES;
2239
2240
2241 /**
2242 * This API performs defaulting logic for a Budget's source workplan
2243 * or financial plan version. It is adapted from the defaulting logic
2244 * in of the GENERATE_WP_BUDGET_AMT API in PAFPWPGB.pls version 115.73.
2245 *
2246 * Currently, nothing happens if the target version is a Forecast.
2247 *
2248 * PARAMETERS:
2249 *
2250 * P_CALLING_CONTEXT
2251 * -----------------
2252 * 'CONCURRENT' : this api is being called from a Concurrent Program.
2253 * 'SELF_SERVICE': this api is being called from the Self-Service pages.
2254 *
2255 * X_FP_COLS_REC_TGT
2256 * -----------------
2257 * This is the target version's details after the defaulting logic.
2258 * If no defaulting occurs, this will be the same as P_FP_COLS_REC_TGT.
2259 */
2260 PROCEDURE DEFAULT_BDGT_SRC_VER
2261 (P_FP_COLS_REC_TGT IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
2262 P_CALLING_CONTEXT IN VARCHAR2,
2263 X_FP_COLS_REC_TGT OUT NOCOPY PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
2264 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2265 X_MSG_COUNT OUT NOCOPY NUMBER,
2266 X_MSG_DATA OUT NOCOPY VARCHAR2)
2267 IS
2268 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_AMOUNT_UTILS.DEFAULT_BDGT_SRC_VER';
2269
2270 l_plan_class_code PA_FIN_PLAN_TYPES_B.PLAN_CLASS_CODE%TYPE;
2271 l_gen_src_code PA_PROJ_FP_OPTIONS.GEN_COST_SRC_CODE%TYPE;
2272
2273 -- Variables for Budget Generation source version defaulting logic
2274 l_wp_status PA_PROJ_FP_OPTIONS.GEN_SRC_COST_WP_VER_CODE%TYPE;
2275 l_wp_id PA_BUDGET_VERSIONS.PROJECT_STRUCTURE_VERSION_ID%TYPE := NULL;
2276 l_source_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
2277 l_versioning_enabled PA_PROJ_WORKPLAN_ATTR.WP_ENABLE_VERSION_flag%TYPE;
2278
2279 -- ER 3491321: While doing unit testing, discovered a type-mismatch error
2280 -- which was not reached during unit testing for ER 4391254, where this bug
2281 -- was introduced. Previous type was GEN_SRC_COST_PLAN_VERSION_ID%TYPE.
2282
2283 l_gen_src_plan_ver_code PA_PROJ_FP_OPTIONS.GEN_SRC_COST_PLAN_VER_CODE%TYPE;
2284
2285 l_fp_options_id PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE;
2286 l_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE;
2287
2288 l_update_details_flag VARCHAR2(1);
2289 l_dummy NUMBER;
2290
2291 l_msg_count NUMBER;
2292 l_data VARCHAR2(2000);
2293 l_msg_data VARCHAR2(2000);
2294 l_msg_index_out NUMBER;
2295 BEGIN
2296 IF p_pa_debug_mode = 'Y' THEN
2297 pa_debug.set_curr_function
2298 ( p_function => 'DEFAULT_BDGT_SRC_VER',
2299 p_debug_mode => p_pa_debug_mode );
2300 END IF;
2301
2302 -- Initialize OUT parameters with default values
2303 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2304 X_MSG_COUNT := 0;
2305 X_FP_COLS_REC_TGT := P_FP_COLS_REC_TGT;
2306
2307 l_plan_class_code := P_FP_COLS_REC_TGT.X_PLAN_CLASS_CODE;
2308 IF l_plan_class_code = 'BUDGET' THEN
2309 l_gen_src_code := P_FP_COLS_REC_TGT.X_GEN_SRC_CODE;
2310 ELSE
2311 l_gen_src_code := P_FP_COLS_REC_TGT.X_GEN_ETC_SRC_CODE;
2312 END IF;
2313
2314
2315 /* This logic is adapted from GENERATE_WP_BUDGET_AMT in PAFPWPGB.pls. */
2316 IF l_plan_class_code = 'BUDGET' THEN
2317
2318 l_update_details_flag := 'Y';
2319
2320 IF (l_gen_src_code = 'WORKPLAN_RESOURCES') THEN
2321 /*Get latest published/current working/baselined work plan version id*/
2322 IF P_FP_COLS_REC_TGT.x_gen_src_wp_version_id is not NULL THEN
2323 --l_update_details_flag := 'N';
2324 l_source_id := P_FP_COLS_REC_TGT.x_gen_src_wp_version_id;
2325 /* the x_gen_src_wp_version_id is the budget version id
2326 corresponding to the work plan structure version id selected
2327 as the source for the budget generation when the budget
2328 generation source is Work plan. */
2329 SELECT project_structure_version_id
2330 INTO l_wp_id
2331 FROM pa_budget_versions
2332 WHERE budget_version_id = l_source_id;
2333 ELSE
2334 l_versioning_enabled :=
2335 PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED
2336 ( P_FP_COLS_REC_TGT.X_PROJECT_ID );
2337 IF l_versioning_enabled = 'Y' THEN
2338 l_wp_status := P_FP_COLS_REC_TGT.x_gen_src_wp_ver_code;
2339 --dbms_output.put_line('ver code val :'||l_wp_status );
2340 IF (l_wp_status = 'LAST_PUBLISHED') THEN
2341 l_wp_id := PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_WP_VERSION
2342 ( P_PROJECT_ID => P_FP_COLS_REC_TGT.X_PROJECT_ID );
2343 IF l_wp_id is null THEN
2344 PA_UTILS.ADD_MESSAGE
2345 ( p_app_short_name => 'PA',
2346 p_msg_name => 'PA_LATEST_WPID_NULL');
2347 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2348 END IF;
2349 ELSIF (l_wp_status = 'CURRENT_WORKING') THEN
2350 --dbms_output.put_line('inside cw chk :');
2351 l_wp_id := PA_PROJECT_STRUCTURE_UTILS.GET_CURRENT_WORKING_VER_ID
2352 ( P_PROJECT_ID => P_FP_COLS_REC_TGT.X_PROJECT_ID);
2353 IF l_wp_id is null THEN
2354 --dbms_output.put_line('cw id is null calling latest pub :');
2355 l_wp_id := PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_WP_VERSION
2356 ( P_PROJECT_ID => P_FP_COLS_REC_TGT.X_PROJECT_ID );
2357 END IF;
2358 --dbms_output.put_line('wp id value : '||l_wp_id);
2359 IF l_wp_id is null THEN
2360 PA_UTILS.ADD_MESSAGE
2361 ( p_app_short_name => 'PA',
2362 p_msg_name => 'PA_CW_WPID_NULL');
2363 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2364 END IF;
2365 -- Changed 'BASELINE', which was INCORRECT, to 'BASELINED' (dkuo)
2366 ELSIF (l_wp_status = 'BASELINED') THEN
2367 l_wp_id := PA_PROJECT_STRUCTURE_UTILS.GET_BASELINE_STRUCT_VER
2368 ( P_PROJECT_ID => P_FP_COLS_REC_TGT.X_PROJECT_ID );
2369 IF l_wp_id is null THEN
2370 PA_UTILS.ADD_MESSAGE
2371 ( p_app_short_name => 'PA',
2372 p_msg_name => 'PA_BASELINED_WPID_NULL');
2373 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2374 END IF;
2375 END IF;
2376 ELSE
2377 l_wp_id := PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_WP_VERSION
2378 ( P_PROJECT_ID => P_FP_COLS_REC_TGT.X_PROJECT_ID );
2379 IF l_wp_id is null THEN
2380 PA_UTILS.ADD_MESSAGE
2381 ( p_app_short_name => 'PA',
2382 p_msg_name => 'PA_LATEST_WPID_NULL');
2383 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2384 END IF;
2385 END IF;
2386 /*Get the budget version id for the requried work plan version id
2387 *SOURCE: work plan budget version id: l_source_id
2388 *TARGET: financial budget version id: P_FP_COLS_REC_TGT.X_BUDGET_VERSION_ID*/
2389
2390 l_source_id := Pa_Fp_wp_gen_amt_utils.get_wp_version_id
2391 ( p_project_id => P_FP_COLS_REC_TGT.X_PROJECT_ID,
2392 p_plan_type_id => null,
2393 p_proj_str_ver_id => l_wp_id );
2394 END IF;
2395
2396 --dbms_output.put_line('l_source_id: '||l_source_id );
2397 --l_txn_currency_flag := '1';
2398
2399 l_version_type := P_FP_COLS_REC_TGT.x_version_type;
2400 /*As of now, we have the l_wp_id as wp struct version id
2401 * l_source_id as wp fin version id
2402 * Now, we need to update back to pa_proj_fp_options*/
2403 IF l_version_type = 'COST' THEN
2404 UPDATE PA_PROJ_FP_OPTIONS
2405 SET GEN_SRC_COST_WP_VERSION_ID = l_source_id
2406 WHERE fin_plan_version_id = P_FP_COLS_REC_TGT.X_BUDGET_VERSION_ID;
2407 ELSIF l_version_type = 'ALL' THEN
2408 UPDATE PA_PROJ_FP_OPTIONS
2409 SET GEN_SRC_ALL_WP_VERSION_ID = l_source_id
2410 WHERE fin_plan_version_id = P_FP_COLS_REC_TGT.X_BUDGET_VERSION_ID;
2411 ELSIF l_version_type = 'REVENUE' THEN
2412 UPDATE PA_PROJ_FP_OPTIONS
2413 SET GEN_SRC_REV_WP_VERSION_ID = l_source_id
2414 WHERE fin_plan_version_id = P_FP_COLS_REC_TGT.X_BUDGET_VERSION_ID;
2415 END IF;
2416
2417 /*project structure version id is populated when create new version.
2418 IF ( l_stru_sharing_code = 'SHARE_FULL' OR
2419 l_stru_sharing_code = 'SHARE_PARTIAL' ) AND
2420 P_FP_COLS_REC_TGT.X_FIN_PLAN_LEVEL_CODE <> 'P' THEN
2421 UPDATE PA_BUDGET_VERSIONS
2422 SET project_structure_version_id = l_wp_id
2423 WHERE budget_version_id = P_FP_COLS_REC_TGT.X_BUDGET_VERSION_ID;
2424 END IF;*/
2425 ELSIF (l_gen_src_code = 'FINANCIAL_PLAN') THEN
2426 IF P_FP_COLS_REC_TGT.x_gen_src_plan_version_id IS NOT NULL THEN
2427 --l_update_details_flag := 'N';
2428 l_source_id := P_FP_COLS_REC_TGT.x_gen_src_plan_version_id;
2429 ELSE
2430 l_gen_src_plan_ver_code := P_FP_COLS_REC_TGT.X_GEN_SRC_PLAN_VER_CODE;
2431 IF l_gen_src_plan_ver_code = 'CURRENT_BASELINED'
2432 OR l_gen_src_plan_ver_code = 'ORIGINAL_BASELINED'
2433 OR l_gen_src_plan_ver_code = 'CURRENT_APPROVED'
2434 OR l_gen_src_plan_ver_code = 'ORIGINAL_APPROVED' THEN
2435 /*Get the current baselined or original baselined version*/
2436 IF P_PA_DEBUG_MODE = 'Y' THEN
2437 pa_fp_gen_amount_utils.fp_debug
2438 ( p_called_mode => p_calling_context,
2439 p_msg => 'Before calling
2440 pa_fp_gen_amount_utils.Get_Curr_Original_Version_Info',
2441 p_module_name => l_module_name,
2442 p_log_level => 5 );
2443 END IF;
2444 pa_fp_gen_amount_utils.Get_Curr_Original_Version_Info(
2445 p_project_id => P_FP_COLS_REC_TGT.X_PROJECT_ID,
2446 p_fin_plan_type_id => P_FP_COLS_REC_TGT.X_GEN_SRC_PLAN_TYPE_ID,
2447 p_version_type => 'COST',
2448 p_status_code => l_gen_src_plan_ver_code,
2449 x_fp_options_id => l_fp_options_id,
2450 x_fin_plan_version_id => l_source_id,
2451 x_return_status => x_return_status,
2452 x_msg_count => x_msg_count,
2453 x_msg_data => x_msg_data );
2454 IF P_PA_DEBUG_MODE = 'Y' THEN
2455 pa_fp_gen_amount_utils.fp_debug
2456 ( p_called_mode => p_calling_context,
2457 p_msg => 'Status after calling
2458 pa_fp_gen_amount_utils.Get_Curr_Original_Version_Info'
2459 ||x_return_status,
2460 p_module_name => l_module_name,
2461 p_log_level => 5 );
2462 END IF;
2463 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2464 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2465 END IF;
2466
2467 ELSIF l_gen_src_plan_ver_code = 'CURRENT_WORKING' THEN
2468 /*Get the current working version*/
2469 IF P_PA_DEBUG_MODE = 'Y' THEN
2470 pa_fp_gen_amount_utils.fp_debug
2471 ( p_called_mode => p_calling_context,
2472 p_msg => 'Before calling
2473 pa_fin_plan_utils.Get_Curr_Working_Version_Info',
2474 p_module_name => l_module_name,
2475 p_log_level => 5 );
2476 END IF;
2477 pa_fin_plan_utils.Get_Curr_Working_Version_Info
2478 ( p_project_id => P_FP_COLS_REC_TGT.X_PROJECT_ID,
2479 p_fin_plan_type_id => P_FP_COLS_REC_TGT.X_GEN_SRC_PLAN_TYPE_ID,
2480 p_version_type => 'COST',
2481 x_fp_options_id => l_fp_options_id,
2482 x_fin_plan_version_id => l_source_id,
2483 x_return_status => x_return_status,
2484 x_msg_count => x_msg_count,
2485 x_msg_data => x_msg_data );
2486 IF P_PA_DEBUG_MODE = 'Y' THEN
2487 pa_fp_gen_amount_utils.fp_debug
2488 ( p_called_mode => p_calling_context,
2489 p_msg => 'Status after calling
2490 pa_fin_plan_utils.Get_Curr_Working_Version_Info'
2491 ||x_return_status,
2492 p_module_name => l_module_name,
2493 p_log_level => 5 );
2494 END IF;
2495 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2496 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2497 END IF;
2498 ELSE
2499 l_dummy := 1;
2500 END IF;
2501
2502 IF l_source_id IS NULL THEN
2503 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2504 p_msg_name => 'PA_SRC_FP_VER_NULL');
2505 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2506 END IF;
2507 END IF;
2508 --dbms_output.put_line('==l_source_id:'||l_source_id);
2509
2510 l_version_type := P_FP_COLS_REC_TGT.x_version_type;
2511 /*As of now, we have l_source_id as fin version id
2512 * Now, we need to update back to pa_proj_fp_options*/
2513 IF l_version_type = 'COST' THEN
2514 UPDATE PA_PROJ_FP_OPTIONS
2515 SET GEN_SRC_COST_PLAN_VERSION_ID = l_source_id
2516 WHERE fin_plan_version_id = P_FP_COLS_REC_TGT.X_BUDGET_VERSION_ID;
2517 ELSIF l_version_type = 'ALL' THEN
2518 UPDATE PA_PROJ_FP_OPTIONS
2519 SET GEN_SRC_ALL_PLAN_VERSION_ID = l_source_id
2520 WHERE fin_plan_version_id = P_FP_COLS_REC_TGT.X_BUDGET_VERSION_ID;
2521 ELSIF l_version_type = 'REVENUE' THEN
2522 UPDATE PA_PROJ_FP_OPTIONS
2523 SET GEN_SRC_REV_PLAN_VERSION_ID = l_source_id
2524 WHERE fin_plan_version_id = P_FP_COLS_REC_TGT.X_BUDGET_VERSION_ID;
2525 END IF;
2526 END IF; -- end gen_src_code-based logic
2527
2528
2529 /* Get updated Target version details */
2530 -- Currently, l_update_details_flag is always 'Y', but may change in the future.
2531 IF l_update_details_flag = 'Y' THEN
2532 IF p_pa_debug_mode = 'Y' THEN
2533 pa_fp_gen_amount_utils.fp_debug
2534 (p_called_mode => p_calling_context,
2535 p_msg => 'Before calling
2536 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS',
2537 p_module_name => l_module_name,
2538 p_log_level => 5);
2539 END IF;
2540 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS
2541 (P_PROJECT_ID => P_FP_COLS_REC_TGT.X_PROJECT_ID,
2542 P_BUDGET_VERSION_ID => P_FP_COLS_REC_TGT.X_BUDGET_VERSION_ID,
2543 X_FP_COLS_REC => X_FP_COLS_REC_TGT,
2544 X_RETURN_STATUS => X_RETURN_STATUS,
2545 X_MSG_COUNT => X_MSG_COUNT,
2546 X_MSG_DATA => X_MSG_DATA);
2547 IF p_pa_debug_mode = 'Y' THEN
2548 pa_fp_gen_amount_utils.fp_debug
2549 (p_called_mode => p_calling_context,
2550 p_msg => 'Status after calling
2551 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS: '
2552 ||x_return_status,
2553 p_module_name => l_module_name,
2554 p_log_level => 5);
2555 END IF;
2556 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2557 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2558 END IF;
2559 END IF; -- l_update_details_flag check
2560
2561 END IF; -- defaulting logic
2562
2563 IF P_PA_DEBUG_MODE = 'Y' THEN
2564 PA_DEBUG.RESET_CURR_FUNCTION;
2565 END IF;
2566 EXCEPTION
2567 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2568 l_msg_count := FND_MSG_PUB.count_msg;
2569 IF l_msg_count = 1 THEN
2570 PA_INTERFACE_UTILS_PUB.get_messages
2571 ( p_encoded => FND_API.G_TRUE,
2572 p_msg_index => 1,
2573 p_msg_count => l_msg_count,
2574 p_msg_data => l_msg_data,
2575 p_data => l_data,
2576 p_msg_index_out => l_msg_index_out);
2577 x_msg_data := l_data;
2578 x_msg_count := l_msg_count;
2579 ELSE
2580 x_msg_count := l_msg_count;
2581 END IF;
2582
2583 x_return_status := FND_API.G_RET_STS_ERROR;
2584
2585 IF P_PA_DEBUG_MODE = 'Y' THEN
2586 pa_fp_gen_amount_utils.fp_debug
2587 ( p_called_mode => p_calling_context,
2588 p_msg => 'Invalid Arguments Passed',
2589 p_module_name => l_module_name,
2590 p_log_level => 5 );
2591 PA_DEBUG.RESET_CURR_FUNCTION;
2592 END IF;
2593
2594 ROLLBACK;
2595 RAISE;
2596 WHEN OTHERS THEN
2597 rollback;
2598 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2599 x_msg_count := 1;
2600 x_msg_data := substr(sqlerrm,1,240);
2601 FND_MSG_PUB.add_exc_msg
2602 ( p_pkg_name => 'PA_FP_GEN_AMOUNT_UTILS',
2603 p_procedure_name => 'DEFAULT_BDGT_SRC_VER',
2604 p_error_text => substr(sqlerrm,1,240));
2605
2606 IF P_PA_DEBUG_MODE = 'Y' THEN
2607 pa_fp_gen_amount_utils.fp_debug
2608 (p_called_mode => p_calling_context,
2609 p_msg => 'Unexpected Error'||substr(sqlerrm,1,240),
2610 p_module_name => l_module_name,
2611 p_log_level => 5);
2612 PA_DEBUG.RESET_CURR_FUNCTION;
2613 END IF;
2614 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2615 END DEFAULT_BDGT_SRC_VER;
2616
2617
2618
2619 END PA_FP_GEN_AMOUNT_UTILS;