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