[Home] [Help]
PACKAGE BODY: APPS.PA_FP_GEN_BILLING_AMOUNTS
Source
1 PACKAGE body PA_FP_GEN_BILLING_AMOUNTS as
2 /* $Header: PAFPGABB.pls 120.5 2007/02/06 09:54:47 dthakker ship $ */
3
4 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6 FUNCTION GET_EVENT_DATE(P_EVENT_DATE IN DATE,
7 P_ETC_START_DATE IN DATE,
8 P_PLAN_CLASS_CODE IN VARCHAR2)
9 RETURN DATE IS
10 x_event_date DATE;
11 x_etc_start_date DATE;
12 BEGIN
13 x_event_date := p_event_date;
14 x_etc_start_date := p_etc_start_date;
15 IF p_plan_class_code = 'BUDGET' THEN
16 RETURN x_event_date;
17 ELSIF p_plan_class_code = 'FORECAST' THEN
18 IF p_event_date < p_etc_start_date THEN
19 RETURN x_etc_start_date;
20 END IF;
21 END IF;
22 RETURN x_event_date;
23
24 EXCEPTION
25 WHEN OTHERS THEN
26 RETURN TRUNC(SYSDATE);
27 END GET_EVENT_DATE;
28
29 PROCEDURE CONVERT_TXN_AMT_TO_PC_PFC
30 (P_PROJECT_ID IN NUMBER,
31 P_BUDGET_VERSION_ID IN NUMBER,
32 P_RES_ASG_ID IN NUMBER,
33 P_START_DATE IN DATE,
34 P_END_DATE IN DATE,
35 P_CURRENCY_CODE IN VARCHAR2,
36 P_TXN_REV_AMOUNT IN NUMBER,
37 P_TXN_RAW_COST IN NUMBER,
38 P_TXN_BURDENED_COST IN NUMBER,
39 X_PROJFUNC_RAW_COST OUT NOCOPY NUMBER,
40 X_PROJFUNC_BURDENED_COST OUT NOCOPY NUMBER,
41 X_PROJFUNC_REVENUE OUT NOCOPY NUMBER,
42 X_PROJFUNC_REJECTION OUT NOCOPY VARCHAR2,
43 X_PROJ_RAW_COST OUT NOCOPY NUMBER,
44 X_PROJ_BURDENED_COST OUT NOCOPY NUMBER,
45 X_PROJ_REVENUE OUT NOCOPY NUMBER,
46 X_PROJ_REJECTION OUT NOCOPY VARCHAR2,
47 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
48 X_MSG_COUNT OUT NOCOPY NUMBER,
49 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
50
51 l_module_name VARCHAR2(200) :=
52 'pa.plsql.PA_FP_GEN_BILLING_AMOUNTS.CONVERT_TXN_AMT_TO_PC_PFC';
53
54 /* Local variables pa_fp_multi_currency_pkg.conv_mc_bulk */
55 l_res_asn_id_tab pa_fp_multi_currency_pkg.number_type_tab;
56 l_start_date_tab pa_fp_multi_currency_pkg.date_type_tab;
57 l_end_date_tab pa_fp_multi_currency_pkg.date_type_tab;
58 l_txn_currency_code_tab pa_fp_multi_currency_pkg.char240_type_tab;
59 l_txn_rw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
60 l_txn_burdend_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
61 l_txn_rev_tab pa_fp_multi_currency_pkg.number_type_tab;
62 l_projfunc_currency_code_tab pa_fp_multi_currency_pkg.char240_type_tab;
63 l_projfunc_cost_rate_type_tab pa_fp_multi_currency_pkg.char240_type_tab;
64 l_projfunc_cost_rate_tab pa_fp_multi_currency_pkg.number_type_tab;
65 l_projfunc_cost_rate_date_tab pa_fp_multi_currency_pkg.date_type_tab;
66 l_projfunc_rev_rate_type_tab pa_fp_multi_currency_pkg.char240_type_tab;
67 l_projfunc_rev_rate_tab pa_fp_multi_currency_pkg.number_type_tab;
68 l_projfunc_rev_rate_date_tab pa_fp_multi_currency_pkg.date_type_tab;
69 l_projfunc_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
70 l_projfunc_burdened_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
71 l_projfunc_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
72 l_projfunc_rejection_tab pa_fp_multi_currency_pkg.char30_type_tab;
73 l_proj_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
74 l_proj_burdened_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
75 l_proj_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
76 l_proj_rejection_tab pa_fp_multi_currency_pkg.char30_type_tab;
77 l_proj_currency_code_tab pa_fp_multi_currency_pkg.char240_type_tab;
78 l_proj_cost_rate_type_tab pa_fp_multi_currency_pkg.char240_type_tab;
79 l_proj_cost_rate_tab pa_fp_multi_currency_pkg.number_type_tab;
80 l_proj_cost_rate_date_tab pa_fp_multi_currency_pkg.date_type_tab;
81 l_proj_rev_rate_type_tab pa_fp_multi_currency_pkg.char240_type_tab;
82 l_proj_rev_rate_tab pa_fp_multi_currency_pkg.number_type_tab;
83 l_proj_rev_rate_date_tab pa_fp_multi_currency_pkg.date_type_tab;
84 l_user_validate_flag_tab pa_fp_multi_currency_pkg.char240_type_tab;
85 /* end */
86
87 l_count NUMBER;
88 l_msg_count NUMBER;
89 l_data VARCHAR2(1000);
90 l_msg_data VARCHAR2(1000);
91 l_msg_index_out NUMBER;
92 l_pc_code pa_projects_all.project_currency_code%type;
93 l_pfc_code pa_projects_all.project_currency_code%type;
94 l_project_name pa_projects_all.name%TYPE;
95 l_task_id pa_tasks.task_id%TYPE;
96 l_task_name pa_proj_elements.name%TYPE;
97 l_resource_name pa_resource_list_members.alias%TYPE;
98 BEGIN
99 /* Setting initial values */
100 X_MSG_COUNT := 0;
101 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
102
103 IF p_pa_debug_mode = 'Y' THEN
104 pa_debug.set_curr_function( p_function => 'CONVERT_TXN_AMT_TO_PC_PFC'
105 ,p_debug_mode => p_pa_debug_mode);
106 END IF;
107
108 /*Bug 4151764 neet to get project_name, task_name, resource_name for the error message*/
109
110 SELECT project_currency_code,projfunc_currency_code, name
111 INTO l_pc_code , l_pfc_code, l_project_name
112 FROM pa_projects_all
113 WHERE project_id = p_project_id;
114
115 IF p_res_asg_id is NULL THEN
116 l_task_name := null;
117 l_resource_name := null;
118 ELSE
119 BEGIN
120 SELECT prlm.alias, nvl(ra.task_id,0)
121 INTO l_resource_name, l_task_id
122 FROM pa_resource_list_members prlm, pa_Resource_assignments ra
123 WHERE ra.resource_assignment_id = p_res_asg_id
124 AND ra.resource_list_member_id = prlm.resource_list_member_id;
125 EXCEPTION
126 WHEN OTHERS THEN
127 l_resource_name := null;
128 END;
129
130 IF l_task_id > 0 THEN
131 BEGIN
132 SELECT task_name
133 INTO l_task_name
134 FROM pa_tasks
135 WHERE task_id = l_task_id;
136 EXCEPTION
137 WHEN OTHERS THEN
138 l_task_name := null;
139 END;
140 ELSE
141 l_task_name := null;
142 END IF;
143
144 END IF;
145
146 --Calling the conv_mc_bulk api
147 IF p_pa_debug_mode = 'Y' THEN
148 pa_fp_gen_amount_utils.fp_debug
149 (p_msg => 'Before calling
150 pa_fp_multi_currency_pkg.conv_mc_bulk',
151 p_module_name => l_module_name,
152 p_log_level => 5);
153 END IF;
154
155 l_res_asn_id_tab.delete;
156 l_start_date_tab.delete;
157 l_end_date_tab.delete;
158 l_txn_currency_code_tab.delete;
159 l_txn_rev_tab.delete;
160 l_txn_rw_cost_tab.delete;
161 l_txn_burdend_cost_tab.delete;
162 l_projfunc_currency_code_tab.delete;
163 l_projfunc_cost_rate_type_tab.delete;
164 l_projfunc_cost_rate_tab.delete;
165 l_projfunc_cost_rate_date_tab.delete;
166 l_projfunc_rev_rate_type_tab.delete;
167 l_projfunc_rev_rate_tab.delete;
168 l_projfunc_rev_rate_date_tab.delete;
169 l_projfunc_raw_cost_tab.delete;
170 l_projfunc_burdened_cost_tab.delete;
171 l_projfunc_revenue_tab.delete;
172 l_projfunc_rejection_tab.delete;
173 l_proj_raw_cost_tab.delete;
174 l_proj_burdened_cost_tab.delete;
175 l_proj_revenue_tab.delete;
176 l_proj_rejection_tab.delete;
177 l_proj_currency_code_tab.delete;
178 l_proj_cost_rate_type_tab.delete;
179 l_proj_cost_rate_tab.delete;
180 l_proj_cost_rate_date_tab.delete;
181 l_proj_rev_rate_type_tab.delete;
182 l_proj_rev_rate_tab.delete;
183 l_proj_rev_rate_date_tab.delete;
184 l_user_validate_flag_tab.delete;
185
186 l_res_asn_id_tab(1) := p_res_asg_id;
187 l_start_date_tab(1) := p_start_date;
188 l_end_date_tab(1) := p_end_date;
189 l_txn_currency_code_tab(1) := p_currency_code;
190 l_txn_rev_tab(1) := p_txn_rev_amount;
191 /*dbms_output.put_line('----values passed to conv_mc_bulk----');
192 dbms_output.put_line('l_res_asn_id_tab(1):'||l_res_asn_id_tab(1));
193 dbms_output.put_line('l_start_date_tab(1):'||l_start_date_tab(1));
194 dbms_output.put_line('l_end_date_tab(1):'|| l_end_date_tab(1));
195 dbms_output.put_line('l_txn_currency_code_tab(1):'||l_txn_currency_code_tab(1));
196 dbms_output.put_line('l_txn_rev_tab(1):'|| l_txn_rev_tab(1)); */
197
198 -- Bug 5112436 (SQL Repository ID 16507222):
199 -- Modified the sql to join on proj_fp_options_id instead
200 -- of fin_plan_version_id. This avoids a Full Table Scan.
201
202 /*when p_res_asg_id is null, need to rely on p_budget_version_id to get the
203 conversion attribute. */
204 SELECT PROJECT_REV_RATE_TYPE,
205 DECODE(opt.PROJECT_REV_RATE_TYPE,
206 'User', NULL,
207 DECODE(opt.PROJECT_REV_RATE_DATE_TYPE,
208 'START_DATE',P_START_DATE,
209 'END_DATE' ,P_END_DATE,
210 opt.PROJECT_REV_RATE_DATE)),
211 DECODE(opt.PROJECT_REV_RATE_TYPE,
212 'User', tc.PROJECT_REV_EXCHANGE_RATE,
213 NULL),
214 PROJFUNC_REV_RATE_TYPE,
215 DECODE(opt.PROJFUNC_REV_RATE_TYPE,
216 'User', NULL,
217 DECODE(opt.PROJFUNC_REV_RATE_DATE_TYPE,
218 'START_DATE',P_START_DATE,
219 'END_DATE' ,P_END_DATE,
220 opt.PROJFUNC_REV_RATE_DATE)),
221 DECODE(opt.PROJFUNC_REV_RATE_TYPE,
222 'User', tc.PROJFUNC_REV_EXCHANGE_RATE,
223 NULL)
224 INTO l_proj_rev_rate_type_tab(1),
225 l_proj_rev_rate_date_tab(1),
226 l_proj_rev_rate_tab(1),
227 l_projfunc_rev_rate_type_tab(1),
228 l_projfunc_rev_rate_date_tab(1),
229 l_projfunc_rev_rate_tab(1)
230 FROM pa_proj_fp_options opt,
231 pa_fp_txn_currencies tc
232 WHERE opt.fin_plan_version_id = P_BUDGET_VERSION_ID
233 --AND opt.fin_plan_version_id = tc.fin_plan_version_id(+)
234 AND opt.proj_fp_options_id = tc.proj_fp_options_id(+) /* Added for Bug 5112436 */
235 AND tc.txn_currency_code(+) = p_currency_code;
236 /*dbms_output.put_line('l_proj_rev_rate_type_tab(1):'|| l_proj_rev_rate_type_tab(1));
237 dbms_output.put_line('l_proj_rev_rate_date_tab(1):'||l_proj_rev_rate_date_tab(1));
238 dbms_output.put_line('l_proj_rev_rate_tab(1):'||l_proj_rev_rate_tab(1));
239 dbms_output.put_line('l_projfunc_rev_rate_type_tab(1):'|| l_projfunc_rev_rate_type_tab(1));
240 dbms_output.put_line('l_projfunc_rev_rate_date_tab(1):'||l_projfunc_rev_rate_date_tab(1));
241 dbms_output.put_line(' l_projfunc_rev_rate_tab(1):'||l_projfunc_rev_rate_tab(1)); */
242
243 l_txn_rw_cost_tab(1) := null;
244 l_txn_burdend_cost_tab(1) := null;
245
246 l_proj_currency_code_tab(1) := l_pc_code;
247 l_proj_cost_rate_tab(1) := null;
248 l_proj_cost_rate_type_tab(1) := null;
249 l_proj_cost_rate_date_tab(1) := null;
250
251 l_projfunc_currency_code_tab(1) := l_pfc_code;
252 l_projfunc_cost_rate_tab(1):= null;
253 l_projfunc_cost_rate_type_tab(1) := null;
254 l_projfunc_cost_rate_date_tab(1) := null;
255
256 l_user_validate_flag_tab(1) := null;
257
258 PA_FP_MULTI_CURRENCY_PKG.CONV_MC_BULK(
259 p_resource_assignment_id_tab => l_res_asn_id_tab
260 ,p_start_date_tab => l_start_date_tab
261 ,p_end_date_tab => l_end_date_tab
262 ,p_txn_currency_code_tab => l_txn_currency_code_tab
263 ,p_txn_raw_cost_tab => l_txn_rw_cost_tab
264 ,p_txn_burdened_cost_tab => l_txn_burdend_cost_tab
265 ,p_txn_revenue_tab => l_txn_rev_tab
266 ,p_projfunc_currency_code_tab => l_projfunc_currency_code_tab
267 ,p_projfunc_cost_rate_type_tab => l_projfunc_cost_rate_type_tab
268 ,p_projfunc_cost_rate_tab => l_projfunc_cost_rate_tab
269 ,p_projfunc_cost_rate_date_tab => l_projfunc_cost_rate_date_tab
270 ,p_projfunc_rev_rate_type_tab => l_projfunc_rev_rate_type_tab
271 ,p_projfunc_rev_rate_tab => l_projfunc_rev_rate_tab
272 ,p_projfunc_rev_rate_date_tab => l_projfunc_rev_rate_date_tab
273 ,x_projfunc_raw_cost_tab => l_projfunc_raw_cost_tab
274 ,x_projfunc_burdened_cost_tab => l_projfunc_burdened_cost_tab
275 ,x_projfunc_revenue_tab => l_projfunc_revenue_tab
276 ,x_projfunc_rejection_tab => l_projfunc_rejection_tab
277 ,p_proj_currency_code_tab => l_proj_currency_code_tab
278 ,p_proj_cost_rate_type_tab => l_proj_cost_rate_type_tab
279 ,p_proj_cost_rate_tab => l_proj_cost_rate_tab
280 ,p_proj_cost_rate_date_tab => l_proj_cost_rate_date_tab
281 ,p_proj_rev_rate_type_tab => l_proj_rev_rate_type_tab
282 ,p_proj_rev_rate_tab => l_proj_rev_rate_tab
283 ,p_proj_rev_rate_date_tab => l_proj_rev_rate_date_tab
284 ,x_proj_raw_cost_tab => l_proj_raw_cost_tab
285 ,x_proj_burdened_cost_tab => l_proj_burdened_cost_tab
286 ,x_proj_revenue_tab => l_proj_revenue_tab
287 ,x_proj_rejection_tab => l_proj_rejection_tab
288 ,p_user_validate_flag_tab => l_user_validate_flag_tab
289 ,p_calling_module => 'BUDGET_GENERATION' ---- Added for Bug 5395732
290 ,x_return_status => x_return_status
291 ,x_msg_count => x_msg_count
292 ,x_msg_data => x_msg_data);
293 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
294 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
295 END IF;
296 IF p_pa_debug_mode = 'Y' THEN
297 pa_fp_gen_amount_utils.fp_debug
298 (p_msg => 'Status after calling
299 pa_fp_multi_currency_pkg.conv_mc_bulk: '
300 ||x_return_status,
301 p_module_name => l_module_name,
302 p_log_level => 5);
303 END IF;
304
305 if l_projfunc_rejection_tab(1) is not null then
306 l_projfunc_rejection_tab(1) := substr(l_projfunc_rejection_tab(1),1,30);
307 end if;
308 if l_proj_rejection_tab(1) is not null then
309 l_proj_rejection_tab(1) := substr(l_proj_rejection_tab(1),1,30);
310 end if;
311 x_projfunc_raw_cost := l_projfunc_raw_cost_tab(1);
312 x_projfunc_burdened_cost := l_projfunc_burdened_cost_tab(1);
313 x_projfunc_revenue := l_projfunc_revenue_tab(1);
314 x_projfunc_rejection := l_projfunc_rejection_tab(1);
315 x_proj_raw_cost := l_proj_raw_cost_tab(1);
316 x_proj_burdened_cost := l_proj_burdened_cost_tab(1);
317 x_proj_revenue := l_proj_revenue_tab(1);
318 x_proj_rejection := l_proj_rejection_tab(1);
319
320 /* Bug4151764 Added token-values for the error msg in case of rejection */
321 IF x_projfunc_rejection is not null then
322 x_return_status := FND_API.G_RET_STS_ERROR;
323 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
324 p_msg_name => x_projfunc_rejection,
325 p_token1 => 'PROJECT' ,
326 p_value1 => l_project_name,
327 p_token2 => 'TASK',
328 p_value2 => l_task_name,
329 p_token3 => 'RESOURCE_NAME',
330 p_value3 => l_resource_name,
331 p_token4 => 'RATE_DATE',
332 p_value4 => l_projfunc_rev_rate_date_tab(1),
333 p_token5 => 'TXN_CURRENCY',
334 p_value5 => l_txn_currency_code_tab(1));
335 end if;
336
337 IF x_proj_rejection is not null then
338 x_return_status := FND_API.G_RET_STS_ERROR;
339 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
340 p_msg_name => x_proj_rejection,
341 p_token1 => 'PROJECT' ,
342 p_value1 => l_project_name,
343 p_token2 => 'TASK',
344 p_value2 => l_task_name,
345 p_token3 => 'RESOURCE_NAME',
346 p_value3 => l_resource_name,
347 p_token4 => 'RATE_DATE',
348 p_value4 => l_proj_rev_rate_date_tab(1),
349 p_token5 => 'TXN_CURRENCY',
350 p_value5 => l_txn_currency_code_tab(1));
351 end if;
352 /* dbms_output.put_line('Value of x_projfunc_raw_cost: '||x_projfunc_raw_cost);
353 dbms_output.put_line('Value of x_projfunc_burdened_cost: '||x_projfunc_burdened_cost);
354 dbms_output.put_line('Value of x_projfunc_revenue: '||x_projfunc_revenue);
355 dbms_output.put_line('Value of x_projfunc_rejection: '||x_projfunc_rejection);
356 dbms_output.put_line('Value of x_proj_raw_cost: '||x_proj_raw_cost);
357 dbms_output.put_line('Value of x_proj_burdened_cost: '||x_proj_burdened_cost);
358 dbms_output.put_line('Value of x_proj_revenue: '||x_proj_revenue);
359 dbms_output.put_line('Value of x_proj_rejection: '||x_proj_rejection); */
360
361 IF P_PA_DEBUG_MODE = 'Y' THEN
362 PA_DEBUG.Reset_curr_function;
363 END IF;
364 EXCEPTION
365 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
366 -- Bug Fix: 4569365. Removed MRC code.
367 -- PA_MRC_FINPLAN.G_CALLING_MODULE := Null;
368 l_msg_count := FND_MSG_PUB.count_msg;
369 IF l_msg_count = 1 THEN
370 PA_INTERFACE_UTILS_PUB.get_messages
371 (p_encoded => FND_API.G_TRUE
372 ,p_msg_index => 1
373 ,p_msg_count => l_msg_count
374 ,p_msg_data => l_msg_data
375 ,p_data => l_data
376 ,p_msg_index_out => l_msg_index_out);
377 x_msg_data := l_data;
378 x_msg_count := l_msg_count;
379 ELSE
380 x_msg_count := l_msg_count;
381 END IF;
382 ROLLBACK;
383
384 x_return_status := FND_API.G_RET_STS_ERROR;
385 IF P_PA_DEBUG_MODE = 'Y' THEN
386 PA_DEBUG.Reset_curr_function;
387 END IF;
388 RAISE;
389
390 WHEN OTHERS THEN
391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
392 x_msg_data := SUBSTR(SQLERRM,1,240);
393 FND_MSG_PUB.add_exc_msg
394 ( p_pkg_name => 'PA_FP_GEN_BILLING_AMOUNTS'
395 ,p_procedure_name => 'CONVERT_TXN_AMT_TO_PC_PFC');
396
397 IF P_PA_DEBUG_MODE = 'Y' THEN
398 PA_DEBUG.Reset_curr_function;
399 END IF;
400 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
401
402 END CONVERT_TXN_AMT_TO_PC_PFC;
403
404
405 PROCEDURE GEN_BILLING_AMOUNTS
406 (P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
407 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
408 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
409 P_ETC_START_DATE IN
410 PA_BUDGET_VERSIONS.ETC_START_DATE%TYPE,
411 PX_GEN_RES_ASG_ID_TAB IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
412 PX_DELETED_RES_ASG_ID_TAB IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
413 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
414 X_MSG_COUNT OUT NOCOPY NUMBER,
415 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
416
417 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_BILLING_AMOUNTS.GEN_BILLING_AMOUNTS';
418
419 --Cursor used to sum the revenue amount
420 CURSOR SUM_BILL_CRSR(c_tphase PA_PROJ_FP_OPTIONS.COST_TIME_PHASED_CODE%TYPE,
421 c_appl_id GL_PERIOD_STATUSES.APPLICATION_ID%TYPE,
422 c_set_of_books_id PA_IMPLEMENTATIONS_ALL.SET_OF_BOOKS_ID%TYPE,
423 c_org_id PA_PROJECTS_ALL.ORG_ID%TYPE,
424 c_multi_flag PA_PROJ_FP_OPTIONS.PLAN_IN_MULTI_CURR_FLAG%TYPE,
425 c_etc_start_date PA_BUDGET_VERSIONS.ETC_START_DATE%TYPE,
426 c_plan_class_code PA_FIN_PLAN_TYPES_B.PLAN_CLASS_CODE%TYPE)
427 IS
428 SELECT /*+ INDEX(TMP,PA_RES_LIST_MAP_TMP4_N2)*/
429 P.RESOURCE_ASSIGNMENT_ID,
430 V.BILL_TRANS_CURRENCY_CODE,
431 PAP.PERIOD_NAME,
432 PAP.START_DATE,
433 PAP.END_DATE,
434 SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION,
435 'WRITE OFF', -1 * NVL(V.BILL_TRANS_REV_AMOUNT,0),
436 'REALIZED_LOSSES', -1 * NVL(V.BILL_TRANS_REV_AMOUNT,0),
437 NVL(V.BILL_TRANS_REV_AMOUNT,0)))
438 FROM PA_EVENTS_DELIVERABLE_V V,
439 PA_EVENT_TYPES ET,
440 PA_RES_LIST_MAP_TMP4 TMP,
441 PA_RESOURCE_ASSIGNMENTS P,
442 PA_PERIODS PAP
443 WHERE TMP.TXN_SOURCE_ID = V.EVENT_ID
444 AND V.EVENT_TYPE = ET.EVENT_TYPE
445 AND V.PROJECT_ID = P_PROJECT_ID
446 AND P.RESOURCE_ASSIGNMENT_ID = TMP.TXN_RESOURCE_ASSIGNMENT_ID
447 AND P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
448 AND c_tphase = 'P'
449 AND V.EVENT_DATE >= NVL(c_etc_start_date, V.EVENT_DATE)
450 AND V.EVENT_DATE BETWEEN PAP.START_DATE AND PAP.END_DATE
451 GROUP BY P.RESOURCE_ASSIGNMENT_ID,
452 V.BILL_TRANS_CURRENCY_CODE,
453 PAP.PERIOD_NAME,
454 PAP.START_DATE,
455 PAP.END_DATE
456 UNION ALL
457 SELECT /*+ INDEX(TMP,PA_RES_LIST_MAP_TMP4_N2)*/
458 P.RESOURCE_ASSIGNMENT_ID,
459 V.BILL_TRANS_CURRENCY_CODE,
460 GLP.PERIOD_NAME,
461 GLP.START_DATE,
462 GLP.END_DATE,
463 SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION,
464 'WRITE OFF', -1 * NVL(V.BILL_TRANS_REV_AMOUNT,0),
465 'REALIZED_LOSSES', -1 * NVL(V.BILL_TRANS_REV_AMOUNT,0),
466 NVL(V.BILL_TRANS_REV_AMOUNT,0)))
467 FROM PA_EVENTS_DELIVERABLE_V V,
468 PA_EVENT_TYPES ET,
469 PA_RES_LIST_MAP_TMP4 TMP,
470 PA_RESOURCE_ASSIGNMENTS P,
471 GL_PERIOD_STATUSES GLP
472 WHERE TMP.TXN_SOURCE_ID = V.EVENT_ID
473 AND V.EVENT_TYPE = ET.EVENT_TYPE
474 AND V.PROJECT_ID = P_PROJECT_ID
475 AND P.RESOURCE_ASSIGNMENT_ID = TMP.TXN_RESOURCE_ASSIGNMENT_ID
476 AND P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
477 AND c_tphase = 'G'
478 AND V.EVENT_DATE >= NVL(c_etc_start_date, V.EVENT_DATE)
479 AND V.EVENT_DATE BETWEEN GLP.START_DATE AND GLP.END_DATE
480 AND GLP.APPLICATION_ID = c_appl_id
481 AND GLP.SET_OF_BOOKS_ID = c_set_of_books_id
482 AND GLP.ADJUSTMENT_PERIOD_FLAG = 'N'
483 GROUP BY P.RESOURCE_ASSIGNMENT_ID,
484 V.BILL_TRANS_CURRENCY_CODE,
485 GLP.PERIOD_NAME,
486 GLP.START_DATE,
487 GLP.END_DATE
488 UNION ALL
489 SELECT /*+ INDEX(TMP,PA_RES_LIST_MAP_TMP4_N2)*/
490 P.RESOURCE_ASSIGNMENT_ID,
491 V.BILL_TRANS_CURRENCY_CODE,
492 TO_CHAR(NULL),
493 GET_EVENT_DATE(V.EVENT_DATE,c_etc_start_date,c_plan_class_code),
494 GET_EVENT_DATE(V.EVENT_DATE,c_etc_start_date,c_plan_class_code),
495 SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION,
496 'WRITE OFF', -1 * NVL(V.BILL_TRANS_REV_AMOUNT,0),
497 'REALIZED_LOSSES', -1 * NVL(V.BILL_TRANS_REV_AMOUNT,0),
498 NVL(V.BILL_TRANS_REV_AMOUNT,0)))
499 FROM PA_EVENTS_DELIVERABLE_V V,
500 PA_EVENT_TYPES ET,
501 PA_RES_LIST_MAP_TMP4 TMP,
502 PA_RESOURCE_ASSIGNMENTS P
503 WHERE TMP.TXN_SOURCE_ID = V.EVENT_ID
504 AND V.EVENT_TYPE = ET.EVENT_TYPE
505 AND V.PROJECT_ID = P_PROJECT_ID
506 AND V.EVENT_DATE >= NVL(c_etc_start_date, V.EVENT_DATE)
507 AND P.RESOURCE_ASSIGNMENT_ID = TMP.TXN_RESOURCE_ASSIGNMENT_ID
508 AND P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
509 AND c_tphase = 'N'
510 GROUP BY P.RESOURCE_ASSIGNMENT_ID,
511 V.BILL_TRANS_CURRENCY_CODE,
512 TO_CHAR(null),
513 GET_EVENT_DATE(V.EVENT_DATE,c_etc_start_date,c_plan_class_code),
514 GET_EVENT_DATE(V.EVENT_DATE,c_etc_start_date,c_plan_class_code);
515
516
517 l_res_asg_id PA_PLSQL_DATATYPES.IdTabTyp;
518 l_currency_code PA_PLSQL_DATATYPES.Char15TabTyp;
519 l_tphase PA_PLSQL_DATATYPES.Char30TabTyp;
520 l_billstart_date PA_PLSQL_DATATYPES.DateTabTyp;
521 l_billend_date PA_PLSQL_DATATYPES.DateTabTyp;
522 l_rev_sum PA_PLSQL_DATATYPES.NumTabTyp;
523
524 l_stru_sharing_code PA_PROJECTS_ALL.STRUCTURE_SHARING_CODE%TYPE;
525
526 l_icount NUMBER := 0;
527 l_ucount NUMBER := 0;
528 l_budget_line_id PA_BUDGET_LINES.BUDGET_LINE_ID%TYPE;
529
530 l_appl_id NUMBER;
531
532 l_last_updated_by NUMBER := FND_GLOBAL.user_id;
533 l_last_update_login NUMBER := FND_GLOBAL.login_id;
534 l_sysdate DATE := SYSDATE;
535 l_ret_status VARCHAR2(100);
536 l_msg_count NUMBER;
537 l_msg_data VARCHAR2(2000);
538 l_data VARCHAR2(2000);
539 l_msg_index_out NUMBER:=0;
540
541 l_res_assgn_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
542 l_rlm_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
543 l_DELETED_RES_ASG_ID_TAB PA_PLSQL_DATATYPES.IdTabTyp;
544
545 l_gen_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
546 l_chk_duplicate_flag VARCHAR2(1) := 'N';
547
548 l_resource_class_id PA_RESOURCE_CLASSES_B.RESOURCE_CLASS_ID%TYPE;
549
550 l_count number;
551
552 l_resource_asg_id NUMBER;
553 l_start_date DATE;
554 l_end_date DATE;
555 l_curr_code pa_budget_lines.txn_currency_code%type;
556 l_txn_curr_code pa_budget_lines.txn_currency_code%type;
557 l_bill_trans_rev_amount NUMBER;
558 l_time_phase VARCHAR2(30);
559 l_projfunc_raw_cost NUMBER;
560 l_projfunc_burdened_cost NUMBER;
561 l_projfunc_revenue NUMBER;
562 l_projfunc_rejection_code VARCHAR2(50);
563 l_proj_raw_cost NUMBER;
564 l_proj_burdened_cost NUMBER;
565 l_proj_revenue NUMBER;
566 l_proj_rejection_code VARCHAR2(50);
567
568 l_approved_rev_plan_type_flag PA_BUDGET_VERSIONS.APPROVED_REV_PLAN_TYPE_FLAG%TYPE;
569
570 l_count1 NUMBER;
571 l_project_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
572 --Local pl/sql table to call Map_Rlmi_Rbs api
573 l_TXN_SOURCE_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
574 l_TXN_SOURCE_TYPE_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
575 l_PERSON_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
576 l_JOB_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
577 l_ORGANIZATION_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
578 l_VENDOR_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
579 l_EXPENDITURE_TYPE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
580 l_EVENT_TYPE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
581 l_NON_LABOR_RESOURCE_tab PA_PLSQL_DATATYPES.Char20TabTyp;
582 l_EXPENDITURE_CATEGORY_tab PA_PLSQL_DATATYPES.Char30TabTyp;
583 l_REVENUE_CATEGORY_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
584 l_NLR_ORGANIZATION_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
585 l_EVENT_CLASSIFICATION_tab PA_PLSQL_DATATYPES.Char30TabTyp;
586 l_SYS_LINK_FUNCTION_tab PA_PLSQL_DATATYPES.Char30TabTyp;
587 l_PROJECT_ROLE_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
588 l_RESOURCE_CLASS_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
589 l_MFC_COST_TYPE_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
590 l_RESOURCE_CLASS_FLAG_tab PA_PLSQL_DATATYPES.Char1TabTyp;
591 l_FC_RES_TYPE_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
592 l_INVENTORY_ITEM_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
593 l_ITEM_CATEGORY_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
594 l_PERSON_TYPE_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
595 l_BOM_RESOURCE_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
596 l_NAMED_ROLE_tab PA_PLSQL_DATATYPES.Char80TabTyp;
597 l_INCURRED_BY_RES_FLAG_tab PA_PLSQL_DATATYPES.Char1TabTyp;
598 l_RATE_BASED_FLAG_tab PA_PLSQL_DATATYPES.Char1TabTyp;
599 l_TXN_TASK_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
600 l_TXN_WBS_ELEMENT_VER_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
601 l_TXN_RBS_ELEMENT_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
602 l_TXN_PLAN_START_DATE_tab PA_PLSQL_DATATYPES.DateTabTyp;
603 l_TXN_PLAN_END_DATE_tab PA_PLSQL_DATATYPES.DateTabTyp;
604 --out param from PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS
605 l_map_txn_source_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
606 l_map_rlm_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
607 l_map_rbs_element_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
608 l_map_txn_accum_header_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
609
610 -- Variables added for Bug 5059327
611 l_txn_raw_cost PA_BUDGET_LINES.TXN_RAW_COST%TYPE;
612 -- IPM: Added local variable to pass variable values of the
613 -- p_calling_module parameter of the MAINTAIN_DATA API.
614 l_calling_module VARCHAR2(30);
615 /* String constants for valid calling module values */
616 lc_BudgetGeneration CONSTANT VARCHAR2(30) := 'BUDGET_GENERATION';
617 lc_ForecastGeneration CONSTANT VARCHAR2(30) := 'FORECAST_GENERATION';
618
619 BEGIN
620 /* Setting initial values */
621 X_MSG_COUNT := 0;
622 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
623
624 IF p_pa_debug_mode = 'Y' THEN
625 pa_debug.set_curr_function( p_function => 'GEN_BILLING_AMOUNTS'
626 ,p_debug_mode => p_pa_debug_mode);
627 END IF;
628
629 l_stru_sharing_code :=
630 PA_PROJECT_STRUCTURE_UTILS.get_Structure_sharing_code(P_PROJECT_ID=> P_PROJECT_ID);
631
632 /* dbms_output.put_line('Value for struct sharing code:
633 '||l_stru_sharing_code);*/
634
635 /* Deleting all the records from the temporary table */
636 DELETE FROM PA_RES_LIST_MAP_TMP1;
637 DELETE FROM PA_RES_LIST_MAP_TMP4;
638
639 -- hr_utility.trace_on(null,'GOD');
640 SELECT RESOURCE_CLASS_ID
641 INTO l_resource_class_id
642 FROM PA_RESOURCE_CLASSES_B
643 WHERE RESOURCE_CLASS_CODE = 'FINANCIAL_ELEMENTS';
644
645
646
647 SELECT PROJECT_ID,
648 nvl(TASK_ID,0),
649 EVENT_ID,
650 EVENT_TYPE,
651 'BILLING_EVENTS',
652 ORGANIZATION_ID,
653 INVENTORY_ITEM_ID,
654 event_date,
655 event_date,
656 DECODE(EVENT_TYPE,null,NULL,'EVENT_TYPE'),
657 'FINANCIAL_ELEMENTS'
658 BULK COLLECT
659 INTO l_project_id_tab,
660 l_TXN_TASK_ID_tab,
661 l_TXN_SOURCE_ID_tab,
662 l_EVENT_TYPE_tab,
663 l_TXN_SOURCE_TYPE_CODE_tab,
664 l_ORGANIZATION_ID_tab,
665 l_INVENTORY_ITEM_ID_tab,
666 l_TXN_PLAN_START_DATE_tab,
667 l_TXN_PLAN_END_DATE_tab,
668 l_FC_RES_TYPE_CODE_tab,
669 l_RESOURCE_CLASS_CODE_tab
670 FROM PA_EVENTS_DELIVERABLE_V
671 WHERE PROJECT_ID = P_PROJECT_ID;
672
673 IF l_TXN_SOURCE_ID_tab.count = 0 THEN
674 IF P_PA_DEBUG_MODE = 'Y' THEN
675 PA_DEBUG.Reset_curr_function;
676 END IF;
677 RETURN;
678 END IF;
679
680 FOR bb in 1..l_TXN_SOURCE_ID_tab.count LOOP
681 l_PERSON_ID_tab(bb) := null;
682 l_JOB_ID_tab(bb) := null;
683 l_VENDOR_ID_tab(bb) := null;
684 l_EXPENDITURE_TYPE_tab(bb) := null;
685 l_NON_LABOR_RESOURCE_tab(bb) := null;
686 l_EXPENDITURE_CATEGORY_tab(bb) := null;
687 l_REVENUE_CATEGORY_CODE_tab(bb) := null;
688 l_NLR_ORGANIZATION_ID_tab(bb) := null;
689 l_EVENT_CLASSIFICATION_tab(bb) := null;
690 l_SYS_LINK_FUNCTION_tab(bb) := null;
691 l_PROJECT_ROLE_ID_tab(bb) := null;
692 l_MFC_COST_TYPE_ID_tab(bb) := null;
693 l_RESOURCE_CLASS_FLAG_tab(bb) := null;
694 l_ITEM_CATEGORY_ID_tab(bb) := null;
695 l_PERSON_TYPE_CODE_tab(bb) := null;
696 l_BOM_RESOURCE_ID_tab(bb) := null;
697 l_NAMED_ROLE_tab(bb) := null;
698 l_INCURRED_BY_RES_FLAG_tab(bb) := null;
699 l_RATE_BASED_FLAG_tab(bb) := null;
700 l_TXN_WBS_ELEMENT_VER_ID_tab(bb):= null;
701 l_TXN_RBS_ELEMENT_ID_tab(bb) := null;
702 END LOOP;
703 --dbms_output.put_line('l_TXN_SOURCE_ID_tab.count: '||l_TXN_SOURCE_ID_tab.count);
704 IF P_PA_DEBUG_MODE = 'Y' THEN
705 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
706 P_MSG => 'Before calling PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS',
707 P_MODULE_NAME => l_module_name);
708 END IF;
709 PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS (
710 P_PROJECT_ID => p_project_id,
711 P_BUDGET_VERSION_ID => NULL,
712 P_RESOURCE_LIST_ID => P_FP_COLS_REC.X_RESOURCE_LIST_ID,
713 P_RBS_VERSION_ID => NULL,
714 P_CALLING_PROCESS => 'BUDGET_GENERATION',
715 P_CALLING_CONTEXT => 'PLSQL',
716 P_PROCESS_CODE => 'RES_MAP',
717 P_CALLING_MODE => 'PLSQL_TABLE',
718 P_INIT_MSG_LIST_FLAG => 'N',
719 P_COMMIT_FLAG => 'N',
720 P_TXN_SOURCE_ID_TAB => l_TXN_SOURCE_ID_tab,
721 P_TXN_SOURCE_TYPE_CODE_TAB => l_TXN_SOURCE_TYPE_CODE_tab,
722 P_PERSON_ID_TAB => l_PERSON_ID_tab,
723 P_JOB_ID_TAB => l_JOB_ID_tab,
724 P_ORGANIZATION_ID_TAB => l_ORGANIZATION_ID_tab,
725 P_VENDOR_ID_TAB => l_VENDOR_ID_tab,
726 P_EXPENDITURE_TYPE_TAB => l_EXPENDITURE_TYPE_tab,
727 P_EVENT_TYPE_TAB => l_EVENT_TYPE_tab,
728 P_NON_LABOR_RESOURCE_TAB => l_NON_LABOR_RESOURCE_tab,
729 P_EXPENDITURE_CATEGORY_TAB => l_EXPENDITURE_CATEGORY_tab,
730 P_REVENUE_CATEGORY_CODE_TAB =>l_REVENUE_CATEGORY_CODE_tab,
731 P_NLR_ORGANIZATION_ID_TAB =>l_NLR_ORGANIZATION_ID_tab,
732 P_EVENT_CLASSIFICATION_TAB => l_EVENT_CLASSIFICATION_tab,
733 P_SYS_LINK_FUNCTION_TAB => l_SYS_LINK_FUNCTION_tab,
734 P_PROJECT_ROLE_ID_TAB => l_PROJECT_ROLE_ID_tab,
735 P_RESOURCE_CLASS_CODE_TAB => l_RESOURCE_CLASS_CODE_tab,
736 P_MFC_COST_TYPE_ID_TAB => l_MFC_COST_TYPE_ID_tab,
737 P_RESOURCE_CLASS_FLAG_TAB => l_RESOURCE_CLASS_FLAG_tab,
738 P_FC_RES_TYPE_CODE_TAB => l_FC_RES_TYPE_CODE_tab,
739 P_INVENTORY_ITEM_ID_TAB => l_INVENTORY_ITEM_ID_tab,
740 P_ITEM_CATEGORY_ID_TAB => l_ITEM_CATEGORY_ID_tab,
741 P_PERSON_TYPE_CODE_TAB => l_PERSON_TYPE_CODE_tab,
742 P_BOM_RESOURCE_ID_TAB =>l_BOM_RESOURCE_ID_tab,
743 P_NAMED_ROLE_TAB =>l_NAMED_ROLE_tab,
744 P_INCURRED_BY_RES_FLAG_TAB =>l_INCURRED_BY_RES_FLAG_tab,
745 P_RATE_BASED_FLAG_TAB =>l_RATE_BASED_FLAG_tab,
746 P_TXN_TASK_ID_TAB =>l_TXN_TASK_ID_tab,
747 P_TXN_WBS_ELEMENT_VER_ID_TAB => l_TXN_WBS_ELEMENT_VER_ID_tab,
748 P_TXN_RBS_ELEMENT_ID_TAB => l_TXN_RBS_ELEMENT_ID_tab,
749 P_TXN_PLAN_START_DATE_TAB => l_TXN_PLAN_START_DATE_tab,
750 P_TXN_PLAN_END_DATE_TAB => l_TXN_PLAN_END_DATE_tab,
751 X_TXN_SOURCE_ID_TAB =>l_map_txn_source_id_tab,
752 X_RES_LIST_MEMBER_ID_TAB =>l_map_rlm_id_tab,
753 X_RBS_ELEMENT_ID_TAB =>l_map_rbs_element_id_tab,
754 X_TXN_ACCUM_HEADER_ID_TAB =>l_map_txn_accum_header_id_tab,
755 X_RETURN_STATUS => x_return_status,
756 X_MSG_COUNT => x_msg_count,
757 X_MSG_DATA => x_msg_data );
758 IF P_PA_DEBUG_MODE = 'Y' THEN
759 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
760 P_MSG => 'After calling PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS: '||
761 x_return_status,
762 P_MODULE_NAME => l_module_name);
763 END IF;
764 /*dbms_output.put_line('After calling PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS: '||x_return_status);
765 dbms_output.put_line('l_map_rlm_id_tab.count: '||l_map_rlm_id_tab.count);*/
766 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
767 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
768 END IF;
769
770 SELECT /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
771 count(*) INTO l_count1
772 FROM PA_RES_LIST_MAP_TMP4
773 WHERE RESOURCE_LIST_MEMBER_ID IS NULL and rownum=1;
774 IF l_count1 > 0 THEN
775 PA_UTILS.ADD_MESSAGE
776 (p_app_short_name => 'PA',
777 p_msg_name => 'PA_INVALID_MAPPING_ERR');
778 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
779 END IF;
780
781 /*dbms_output.put_line('Status of mapping api:
782 '||X_RETURN_STATUS);*/
783 --select count(*) into l_count from PA_RES_LIST_MAP_TMP4;
784 -- hr_utility.trace('tmp4 count aft mapping api call '||l_count);
785 --dbms_output.put_line('tmp4 count :'||l_count);
786 /* Calling the API to get the resource_assignment_id */
787 IF p_pa_debug_mode = 'Y' THEN
788 pa_fp_gen_amount_utils.fp_debug
789 (p_msg => 'Before calling
790 pa_fp_gen_budget_amt_pub.create_res_asg',
791 p_module_name => l_module_name,
792 p_log_level => 5);
793 END IF;
794 PA_FP_GEN_BUDGET_AMT_PUB.CREATE_RES_ASG
795 (P_PROJECT_ID => P_PROJECT_ID,
796 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
797 P_STRU_SHARING_CODE => l_stru_sharing_code,
798 P_GEN_SRC_CODE => 'BILLING_EVENTS',
799 P_FP_COLS_REC => P_FP_COLS_REC,
800 X_RETURN_STATUS => X_RETURN_STATUS,
801 X_MSG_COUNT => X_MSG_COUNT,
802 X_MSG_DATA => X_MSG_DATA);
803 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
804 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
805 END IF;
806 /*dbms_output.put_line('Status of create res asg api:
807 '||X_RETURN_STATUS);*/
808
809 /* Calling the API to update the tmp4
810 table with resource_assignment_id */
811 IF p_pa_debug_mode = 'Y' THEN
812 pa_fp_gen_amount_utils.fp_debug
813 (p_msg => 'Before calling
814 pa_fp_gen_budget_amt_pub.update_res_asg',
815 p_module_name => l_module_name,
816 p_log_level => 5);
817 END IF;
818 PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_RES_ASG
819 (P_PROJECT_ID => P_PROJECT_ID,
820 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
821 P_STRU_SHARING_CODE => l_stru_sharing_code,
822 P_GEN_SRC_CODE => 'BILLING_EVENTS',
823 P_FP_COLS_REC => P_FP_COLS_REC,
824 X_RETURN_STATUS => X_RETURN_STATUS,
825 X_MSG_COUNT => X_MSG_COUNT,
826 X_MSG_DATA => X_MSG_DATA);
827 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
828 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
829 END IF;
830 IF p_pa_debug_mode = 'Y' THEN
831 pa_fp_gen_amount_utils.fp_debug
832 (p_msg => 'Status after calling
833 pa_fp_gen_budget_amt_pub.update_res_asg'
834 ||x_return_status,
835 p_module_name => l_module_name,
836 p_log_level => 5);
837 END IF;
838 /*dbms_output.put_line('Status of update res asg api:
839 '||X_RETURN_STATUS);*/
840
841 /* Calling Del manual bdgt lines api
842 IF p_pa_debug_mode = 'Y' THEN
843 pa_fp_gen_amount_utils.fp_debug
844 (p_msg => 'Before calling
845 pa_fp_gen_budget_amt_pub.del_manual_bdgt_lines',
846 p_module_name => l_module_name,
847 p_log_level => 5);
848 END IF;
849 PA_FP_GEN_BUDGET_AMT_PUB.DEL_MANUAL_BDGT_LINES
850 (P_PROJECT_ID => P_PROJECT_ID,
851 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
852 PX_RES_ASG_ID_TAB => l_res_assgn_id_tab,
853 PX_DELETED_RES_ASG_ID_TAB => l_DELETED_RES_ASG_ID_TAB,
854 X_RETURN_STATUS => X_RETURN_STATUS,
855 X_MSG_COUNT => X_MSG_COUNT,
856 X_MSG_DATA => X_MSG_DATA);
857 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
858 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
859 END IF;
860 IF p_pa_debug_mode = 'Y' THEN
861 pa_fp_gen_amount_utils.fp_debug
862 (p_msg => 'Status after calling
863 pa_fp_gen_budget_amt_pub.del_manual_bdgt_lines'
864 ||x_return_status,
865 p_module_name => l_module_name,
866 p_log_level => 5);
867 END IF;
868 --dbms_output.put_line('Status of del manual bdgt lines api:
869 -- '||X_RETURN_STATUS);
870
871 --Calling get generated res asg api
872 IF p_pa_debug_mode = 'Y' THEN
873 pa_fp_gen_amount_utils.fp_debug
874 (p_msg => 'Before calling
875 pa_fp_gen_budget_amt_pub.get_generated_res_asg',
876 p_module_name => l_module_name,
877 p_log_level => 5);
878 END IF;
879 PA_FP_GEN_BUDGET_AMT_PUB.GET_GENERATED_RES_ASG
880 (P_PROJECT_ID => P_PROJECT_ID,
881 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
882 PX_GEN_RES_ASG_ID_TAB => l_gen_res_asg_id_tab,
883 P_CHK_DUPLICATE_FLAG => l_chk_duplicate_flag,
884 X_RETURN_STATUS => X_RETURN_STATUS,
885 X_MSG_COUNT => X_MSG_COUNT,
886 X_MSG_DATA => X_MSG_DATA);
887 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
888 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
889 END IF;
890 IF p_pa_debug_mode = 'Y' THEN
891 pa_fp_gen_amount_utils.fp_debug
892 (p_msg => 'Status after calling
893 pa_fp_gen_budget_amt_pub.get_generated_res_asg'
894 ||x_return_status,
895 p_module_name => l_module_name,
896 p_log_level => 5);
897 END IF;
898 --dbms_output.put_line('Status of get generated res asg api:
899 -- '||X_RETURN_STATUS);
900 --dbms_output.put_line('Count of res asg id tab after calling
901 --get gen res asg api: '||l_gen_res_asg_id_tab.count);
902
903 PX_GEN_RES_ASG_ID_TAB.delete;
904 PX_GEN_RES_ASG_ID_TAB := l_gen_res_asg_id_tab;
905 */
906 l_appl_id := PA_PERIOD_PROCESS_PKG.Application_id;
907 --dbms_output.put_line('Value of application id: '||l_appl_id);
908
909 l_res_asg_id.delete;
910 l_currency_code.delete;
911 l_tphase.delete;
912 l_billstart_date.delete;
913 l_billend_date.delete;
914 l_rev_sum.delete;
915
916 /* for billing events, the resource class is always FINANCIAL_ELEMENTS and
917 the UOM is always CURRENCY. So, the revenue amount is used for quantity
918 attribute and the txn bill rate override value will be 1. */
919 OPEN SUM_BILL_CRSR(P_FP_COLS_REC.X_TIME_PHASED_CODE,
920 l_appl_id,
921 P_FP_COLS_REC.X_SET_OF_BOOKS_ID,
922 P_FP_COLS_REC.X_ORG_ID,
923 P_FP_COLS_REC.X_PLAN_IN_MULTI_CURR_FLAG,
924 P_ETC_START_DATE,
925 P_FP_COLS_REC.X_PLAN_CLASS_CODE);
926
927 FETCH SUM_BILL_CRSR
928 BULK COLLECT
929 INTO l_res_asg_id,
930 l_currency_code,
931 l_tphase,
932 l_billstart_date,
933 l_billend_date,
934 l_rev_sum;
935
936 CLOSE SUM_BILL_CRSR;
937 /*dbms_output.put_line('after cursor fetch :'||l_res_asg_id.count);
938 dbms_output.put_line('aft cursor fetch rev sum:'||l_rev_sum(1)); */
939 -- hr_utility.trace('aft cursor fetch '||l_res_asg_id.count);
940 SELECT NVL(approved_rev_plan_type_flag,'N')
941 INTO l_approved_rev_plan_type_flag
942 FROM pa_budget_versions
943 WHERE budget_version_id = p_budget_version_id;
944
945 /* dbms_output.put_line('plan_in_multi_curr_flag: '||p_fp_cols_rec.x_plan_in_multi_curr_flag);
946 dbms_output.put_line('approved_rev_plan_type_flag: '||l_approved_rev_plan_type_flag); */
947
948 FOR i in 1..l_res_asg_id.count LOOP
949 l_resource_asg_id := l_res_asg_id(i);
950 l_start_date := l_billstart_date(i);
951 l_end_date := l_billend_date(i);
952 l_curr_code := l_currency_code(i);
953 l_txn_curr_code := l_currency_code(i);
954 l_bill_trans_rev_amount := l_rev_sum(i);
955 l_time_phase := l_tphase(i);
956 /* hr_utility.trace('curr code :'||l_curr_code );
957 hr_utility.trace('txn rev amt :'||l_bill_trans_rev_amount );
958 hr_utility.trace('pc code :'||p_fp_cols_rec.x_project_currency_code );
959 hr_utility.trace('pfc code :'||p_fp_cols_rec.x_projfunc_currency_code ); */
960 /* if multi curr flag is not enabled
961 then the bill_trans_currency_code is chked against PC currency code.
962 If they are not same then convert it to PC currency code */
963 IF ( p_fp_cols_rec.x_plan_in_multi_curr_flag = 'N' AND
964 l_txn_curr_code <> p_fp_cols_rec.x_project_currency_code ) OR
965 ( l_approved_rev_plan_type_flag = 'Y' AND
966 l_txn_curr_code <> p_fp_cols_rec.x_projfunc_currency_code ) THEN
967 /* Call the conversion API to convert
968 bill_trans_currency_code to project currency code*/
969
970 l_curr_code := p_fp_cols_rec.x_projfunc_currency_code;
971
972 --Calling the convert_currency_code api
973 IF p_pa_debug_mode = 'Y' THEN
974 pa_fp_gen_amount_utils.fp_debug
975 (p_msg => 'Before calling
976 pa_fp_gen_billing_amounts.CONVERT_TXN_AMT_TO_PC_PFC',
977 p_module_name => l_module_name,
978 p_log_level => 5);
979 END IF;
980 PA_FP_GEN_BILLING_AMOUNTS.CONVERT_TXN_AMT_TO_PC_PFC
981 (P_PROJECT_ID => p_project_id,
982 P_BUDGET_VERSION_ID => p_budget_version_id,
983 P_RES_ASG_ID => l_resource_asg_id,
984 P_START_DATE => l_start_date,
985 P_END_DATE => l_end_date,
986 P_CURRENCY_CODE => l_txn_curr_code,
987 P_TXN_RAW_COST => NULL,
988 P_TXN_BURDENED_COST => NULL,
989 P_TXN_REV_AMOUNT => l_bill_trans_rev_amount,
990 X_PROJFUNC_RAW_COST => l_projfunc_raw_cost,
991 X_PROJFUNC_BURDENED_COST => l_projfunc_burdened_cost,
992 X_PROJFUNC_REVENUE => l_projfunc_revenue,
993 X_PROJFUNC_REJECTION => l_projfunc_rejection_code,
994 X_PROJ_RAW_COST => l_proj_raw_cost,
995 X_PROJ_BURDENED_COST => l_proj_burdened_cost,
996 X_PROJ_REVENUE => l_proj_revenue,
997 X_PROJ_REJECTION => l_proj_rejection_code,
998 X_RETURN_STATUS => x_return_status,
999 X_MSG_COUNT => x_MSG_COUNT,
1000 X_MSG_DATA => x_MSG_DATA);
1001 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1002 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1003 END IF;
1004 IF p_pa_debug_mode = 'Y' THEN
1005 pa_fp_gen_amount_utils.fp_debug
1006 (p_msg => 'Status after calling
1007 pa_fp_gen_billing_amounts.CONVERT_TXN_AMT_TO_PC_PFC:'
1008 ||x_return_status,
1009 p_module_name => l_module_name,
1010 p_log_level => 5);
1011 END IF;
1012
1013 /* Setting PC values */
1014 IF ( p_fp_cols_rec.x_plan_in_multi_curr_flag = 'N' AND
1015 l_txn_curr_code <> p_fp_cols_rec.x_project_currency_code ) THEN
1016 l_curr_code := p_fp_cols_rec.x_project_currency_code;
1017 l_bill_trans_rev_amount := l_proj_revenue;
1018 END IF;
1019 /* Setting PFC values */
1020 IF ( l_approved_rev_plan_type_flag = 'Y' AND
1021 l_txn_curr_code <> p_fp_cols_rec.x_projfunc_currency_code ) THEN
1022 l_curr_code := p_fp_cols_rec.x_projfunc_currency_code;
1023 l_bill_trans_rev_amount := l_projfunc_revenue;
1024 END IF;
1025
1026 END IF;
1027 /* dbms_output.put_line('PFC Currency Code: '||p_fp_cols_rec.x_projfunc_currency_code);
1028 dbms_output.put_line('PFC Rev amount: '||l_projfunc_revenue);
1029 dbms_output.put_line('Currency Code: '||l_curr_code);
1030 dbms_output.put_line('Rev amount(l_bill_trans_rev_amount): '||l_bill_trans_rev_amount);*/
1031
1032 -- Added for Bug 5059327:
1033 -- Update the currency code table with the actual value
1034 -- so that later code will know which (resource_assignment_id,
1035 -- txn_currency_code)'s have commmitments.
1036 l_currency_code(i) := l_curr_code;
1037
1038 --Checking for budget_line_id
1039 -- For Bug 5059327, also get the txn raw cost amount.
1040 -- Beginning in IPM, the following rule applies at the
1041 -- budget line level to non-rate-based transactions in
1042 -- Cost and Revenue Together versions:
1043 -- i) If txn_raw_cost is Null, then (quantity = txn_revenue)
1044 -- ii) If txn_raw_cost is not Null, then (quantity = txn_raw_cost)
1045 -- For forecasts, check ETC txn_raw_cost instead of txn_raw_cost.
1046
1047 BEGIN
1048 IF P_FP_COLS_REC.X_TIME_PHASED_CODE = 'N' THEN
1049 SELECT BUDGET_LINE_ID,
1050 TXN_RAW_COST - NVL(TXN_INIT_RAW_COST,0)
1051 INTO l_budget_line_id,
1052 l_txn_raw_cost
1053 FROM PA_BUDGET_LINES BL
1054 WHERE BL.RESOURCE_ASSIGNMENT_ID = l_resource_asg_id
1055 AND BL.TXN_CURRENCY_CODE = l_curr_code;
1056 ELSE -- P_FP_COLS_REC.X_TIME_PHASED_CODE IN ('P','G')
1057 SELECT BUDGET_LINE_ID,
1058 TXN_RAW_COST
1059 INTO l_budget_line_id,
1060 l_txn_raw_cost
1061 FROM PA_BUDGET_LINES BL
1062 WHERE BL.RESOURCE_ASSIGNMENT_ID = l_resource_asg_id
1063 AND BL.TXN_CURRENCY_CODE = l_curr_code
1064 AND BL.START_DATE = l_start_date;
1065 END IF;
1066 EXCEPTION
1067 WHEN NO_DATA_FOUND THEN
1068 l_budget_line_id := null;
1069 l_txn_raw_cost := null;
1070 -- dbms_output.put_line('inside no data fnd bl');
1071 END;
1072
1073 /* Checking for the existing record in pa_budget_lines table */
1074 IF l_budget_line_id IS NULL THEN
1075 /* if the record does not exist then insert
1076 the record into the pa_budget_lines table */
1077 -- dbms_output.put_line('inside insert bl');
1078
1079 -- For Cost and Revenue Together versions, non-rate-based
1080 -- planning transactions with only revenue amounts should
1081 -- have budget line cost override rates stamped as 0. This
1082 -- behavior is introduced in IPM. Note that Billing Events
1083 -- are always non-rate-based, so the rate_based_flag does
1084 -- not need to be checked here.
1085
1086 INSERT INTO PA_BUDGET_LINES (
1087 RESOURCE_ASSIGNMENT_ID,
1088 START_DATE,
1089 LAST_UPDATE_DATE,
1090 LAST_UPDATED_BY,
1091 CREATION_DATE,
1092 CREATED_BY,
1093 LAST_UPDATE_LOGIN,
1094 END_DATE,
1095 TXN_CURRENCY_CODE,
1096 TXN_REVENUE,
1097 BUDGET_LINE_ID,
1098 BUDGET_VERSION_ID,
1099 PROJECT_CURRENCY_CODE,
1100 PROJFUNC_CURRENCY_CODE,
1101 QUANTITY,
1102 TXN_BILL_RATE_OVERRIDE,
1103 TXN_COST_RATE_OVERRIDE, -- Added for Bug 5059327
1104 BURDEN_COST_RATE_OVERRIDE, -- Added for Bug 5059327
1105 PERIOD_NAME )
1106 VALUES (
1107 l_resource_asg_id,
1108 l_start_date,
1109 l_sysdate,
1110 l_last_updated_by,
1111 l_sysdate,
1112 l_last_updated_by,
1113 l_last_update_login,
1114 l_end_date,
1115 l_curr_code,
1116 l_bill_trans_rev_amount,
1117 PA_BUDGET_LINES_S.nextval,
1118 P_BUDGET_VERSION_ID,
1119 p_FP_COLS_REC.X_PROJECT_CURRENCY_CODE,
1120 p_FP_COLS_REC.X_PROJFUNC_CURRENCY_CODE,
1121 l_bill_trans_rev_amount,
1122 1,
1123 decode(p_fp_cols_rec.x_version_type,'ALL',0,null), -- Added for Bug 5059327
1124 decode(p_fp_cols_rec.x_version_type,'ALL',0,null), -- Added for Bug 5059327
1125 l_time_phase );
1126
1127 ELSIF l_budget_line_id IS NOT NULL THEN
1128 /* if the record does exist then update
1129 the record in the pa_budget_lines table */
1130 /* dbms_output.put_line('inside update bl');
1131 dbms_output.put_line('budget line id in update '||
1132 l_budget_line_id); */
1133
1134 IF p_fp_cols_rec.x_version_type = 'REVENUE' OR
1135 ( p_fp_cols_rec.x_version_type = 'ALL' AND
1136 nvl(l_txn_raw_cost,0) = 0 ) THEN
1137
1138 UPDATE PA_BUDGET_LINES
1139 SET LAST_UPDATE_DATE = l_sysdate
1140 , LAST_UPDATED_BY = l_last_updated_by
1141 , LAST_UPDATE_LOGIN = l_last_update_login
1142 , TXN_REVENUE = NVL(TXN_REVENUE,0) + l_bill_trans_rev_amount
1143 , quantity = nvl(quantity,0) + l_bill_trans_rev_amount
1144 WHERE BUDGET_LINE_ID = l_budget_line_id;
1145
1146 ELSIF ( p_fp_cols_rec.x_version_type = 'ALL' AND
1147 nvl(l_txn_raw_cost,0) <> 0 ) THEN
1148
1149 -- In this case, the update is occuring for a non-rate-based
1150 -- planning txn with quantity = raw cost. Update the revenue
1151 -- and recompute the bill rate override.
1152
1153 UPDATE PA_BUDGET_LINES
1154 SET LAST_UPDATE_DATE = l_sysdate
1155 , LAST_UPDATED_BY = l_last_updated_by
1156 , LAST_UPDATE_LOGIN = l_last_update_login
1157 , TXN_REVENUE = NVL(TXN_REVENUE,0) + l_bill_trans_rev_amount
1158 , txn_bill_rate_override =
1159 decode(p_fp_cols_rec.x_time_phased_code,'N',
1160 decode((nvl(quantity,0)-nvl(init_quantity,0)),0,null,
1161 (NVL(TXN_REVENUE,0) + l_bill_trans_rev_amount)
1162 /(nvl(quantity,0)-nvl(init_quantity,0)) ),
1163 decode( nvl(quantity,0),0,null,
1164 (NVL(TXN_REVENUE,0) + l_bill_trans_rev_amount)/quantity ))
1165 --, quantity = nvl(quantity,0) + l_bill_trans_rev_amount
1166 WHERE BUDGET_LINE_ID = l_budget_line_id;
1167
1168 END IF; -- version_type check
1169 END IF; -- budget line exists check
1170 END LOOP;
1171
1172 /*dbms_output.put_line('No.of records inserted into
1173 bdgt lines table: '||l_icount);
1174 dbms_output.put_line('No.of records updated into
1175 bdgt lines table: '||l_ucount);*/
1176 IF P_FP_COLS_REC.X_PLAN_IN_MULTI_CURR_FLAG = 'Y' THEN
1177 IF p_pa_debug_mode = 'Y' THEN
1178 pa_fp_gen_amount_utils.fp_debug
1179 (p_msg => 'Before calling
1180 pa_fp_gen_budget_amt_pub.insert_txn_currency',
1181 p_module_name => l_module_name,
1182 p_log_level => 5);
1183 END IF;
1184 PA_FP_GEN_BUDGET_AMT_PUB.INSERT_TXN_CURRENCY
1185 (P_PROJECT_ID => P_PROJECT_ID,
1186 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
1187 P_FP_COLS_REC => P_FP_COLS_REC,
1188 X_RETURN_STATUS => X_RETURN_STATUS,
1189 X_MSG_COUNT => X_MSG_COUNT,
1190 X_MSG_DATA => X_MSG_DATA);
1191 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1192 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1193 END IF;
1194 IF p_pa_debug_mode = 'Y' THEN
1195 pa_fp_gen_amount_utils.fp_debug
1196 (p_msg => 'Status after calling
1197 pa_fp_gen_budget_amt_pub.insert_txn_currency'
1198 ||x_return_status,
1199 p_module_name => l_module_name,
1200 p_log_level => 5);
1201 END IF;
1202 /* dbms_output.put_line('Status of insert txn currency api:
1203 '||X_RETURN_STATUS);*/
1204 END IF;
1205
1206 -- Added 11/30/2004 by dkuo to synch billing event dates
1207 IF p_pa_debug_mode = 'Y' THEN
1208 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1209 ( p_msg => 'Before calling PA_FP_MAINTAIN_ACTUAL_PUB.' ||
1210 'SYNC_UP_PLANNING_DATES',
1211 p_module_name => l_module_name,
1212 p_log_level => 5 );
1213 END IF;
1214 PA_FP_MAINTAIN_ACTUAL_PUB.SYNC_UP_PLANNING_DATES
1215 ( P_BUDGET_VERSION_ID => p_budget_version_id,
1216 P_CALLING_CONTEXT => 'GEN_BILLING_EVENTS',
1217 X_RETURN_STATUS => x_return_Status,
1218 X_MSG_COUNT => x_msg_count,
1219 X_MSG_DATA => x_msg_data );
1220 IF p_pa_debug_mode = 'Y' THEN
1221 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1222 ( p_msg => 'Aft calling PA_FP_MAINTAIN_ACTUAL_PUB.' ||
1223 'SYNC_UP_PLANNING_DATES return status ' ||
1224 x_return_status,
1225 p_module_name => l_module_name,
1226 p_log_level => 5 );
1227 END IF;
1228 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1229 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1230 END IF;
1231
1232 -- Bug 5059327: In IPM, the following business rule was
1233 -- introduced for non-rate-based planning transactions:
1234 -- When planning cost and revenue together, if the user
1235 -- enters only revenue amounts, then no cost amounts
1236 -- should be generated.
1237 -- The approach taken by the Calculate API to support
1238 -- this case is to populate the new entity record with
1239 -- 1 for the bill rate override and 0 for the cost rate
1240 -- overrides. The same overrides are stamped at the
1241 -- budget line level as well.
1242 --
1243 -- Fix Overview
1244 -- ------------
1245 -- 1. Earlier, when populating budget lines, stamp bill
1246 -- rate override as 1 and cost rate overrides as 0
1247 -- in the budget lines where appropriate.
1248 -- 2. Call the maintain_data API in Insert mode to set
1249 -- bill rate override as 1 and cost rate overrides
1250 -- as 0 for (non-rate-based) planning transactions
1251 -- with only revenue amounts in all budget lines that
1252 -- do not have rejection codes. This applies only to
1253 -- Cost and Revenue Together versions.
1254
1255 IF p_fp_cols_rec.x_version_type = 'ALL' THEN
1256 DELETE pa_fp_rollup_tmp;
1257 DELETE pa_resource_asgn_curr_tmp;
1258
1259 -- Use pa_fp_rollup_tmp to get DISTINCT records later.
1260 FORALL i IN 1..l_res_asg_id.count
1261 INSERT INTO pa_fp_rollup_tmp (
1262 RESOURCE_ASSIGNMENT_ID,
1263 TXN_CURRENCY_CODE )
1264 VALUES (
1265 l_res_asg_id(i),
1266 l_currency_code(i) );
1267
1268 -- Populate temp table with overrides for 'revenue-only' txns.
1269 -- Note that the Select handles both budget and forecasts as
1270 -- well PA/GL-timephased and non-timephased versions.
1271 INSERT INTO pa_resource_asgn_curr_tmp (
1272 RESOURCE_ASSIGNMENT_ID,
1273 TXN_CURRENCY_CODE,
1274 TXN_RAW_COST_RATE_OVERRIDE,
1275 TXN_BURDEN_COST_RATE_OVERRIDE,
1276 TXN_BILL_RATE_OVERRIDE )
1277 SELECT bl.resource_assignment_id,
1278 bl.txn_currency_code,
1279 0,
1280 0,
1281 1
1282 FROM pa_budget_lines bl,
1283 (SELECT DISTINCT resource_assignment_id, txn_currency_code
1284 FROM pa_fp_rollup_tmp) tmp
1285 WHERE bl.resource_assignment_id = tmp.resource_assignment_id
1286 AND bl.txn_currency_code = tmp.txn_currency_code
1287 AND bl.budget_version_id = p_budget_version_id
1288 AND bl.cost_rejection_code is null
1289 AND bl.revenue_rejection_code is null
1290 AND bl.burden_rejection_code is null
1291 AND bl.other_rejection_code is null
1292 AND bl.pc_cur_conv_rejection_code is null
1293 AND bl.pfc_cur_conv_rejection_code is null
1294 GROUP BY bl.resource_assignment_id,
1295 bl.txn_currency_code
1296 HAVING NVL(sum(txn_raw_cost)-nvl(sum(txn_init_raw_cost),0),0) = 0;
1297
1298 l_count := SQL%ROWCOUNT;
1299 IF l_count > 0 THEN
1300
1301 IF p_fp_cols_rec.x_plan_class_code = 'BUDGET' THEN
1302 l_calling_module := lc_BudgetGeneration;
1303 ELSIF p_fp_cols_rec.x_plan_class_code = 'FORECAST' THEN
1304 l_calling_module := lc_ForecastGeneration;
1305 END IF;
1306
1307 -- CALL the maintenance api in INSERT mode
1308 IF p_pa_debug_mode = 'Y' THEN
1309 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1310 P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.' ||
1311 'MAINTAIN_DATA',
1312 --P_CALLED_MODE => p_called_mode,
1313 P_MODULE_NAME => l_module_name);
1314 END IF;
1315 PA_RES_ASG_CURRENCY_PUB.MAINTAIN_DATA
1316 ( P_FP_COLS_REC => p_fp_cols_rec,
1317 P_CALLING_MODULE => l_calling_module,
1318 P_VERSION_LEVEL_FLAG => 'N',
1319 P_ROLLUP_FLAG => 'N', -- 'N' indicates Insert
1320 --P_CALLED_MODE => p_called_mode,
1321 X_RETURN_STATUS => x_return_status,
1322 X_MSG_COUNT => x_msg_count,
1323 X_MSG_DATA => x_msg_data );
1324 IF p_pa_debug_mode = 'Y' THEN
1325 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1326 P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.' ||
1327 'MAINTAIN_DATA: '||x_return_status,
1328 --P_CALLED_MODE => p_called_mode,
1329 P_MODULE_NAME => l_module_name);
1330 END IF;
1331 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1332 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1333 END IF;
1334 END IF; -- IF l_count > 0 THEN
1335 END IF; --IF p_fp_cols_rec.x_version_type = 'ALL' THEN
1336 -- End Bug Fix 5059327
1337
1338 IF P_PA_DEBUG_MODE = 'Y' THEN
1339 PA_DEBUG.Reset_curr_function;
1340 END IF;
1341 EXCEPTION
1342 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1343 -- Bug Fix: 4569365. Removed MRC code.
1344 -- PA_MRC_FINPLAN.G_CALLING_MODULE := Null;
1345 l_msg_count := FND_MSG_PUB.count_msg;
1346 IF l_msg_count = 1 THEN
1347 PA_INTERFACE_UTILS_PUB.get_messages
1348 (p_encoded => FND_API.G_TRUE
1349 ,p_msg_index => 1
1350 ,p_msg_count => l_msg_count
1351 ,p_msg_data => l_msg_data
1352 ,p_data => l_data
1353 ,p_msg_index_out => l_msg_index_out);
1354 x_msg_data := l_data;
1355 x_msg_count := l_msg_count;
1356 ELSE
1357 x_msg_count := l_msg_count;
1358 END IF;
1359 ROLLBACK;
1360
1361 x_return_status := FND_API.G_RET_STS_ERROR;
1362
1363 IF P_PA_DEBUG_MODE = 'Y' THEN
1364 PA_DEBUG.Reset_curr_function;
1365 END IF;
1366
1367 RAISE;
1368
1369 WHEN OTHERS THEN
1370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1371 x_msg_data := SUBSTR(SQLERRM,1,240);
1372 FND_MSG_PUB.add_exc_msg
1373 ( p_pkg_name => 'PA_FP_GEN_BILLING_AMOUNTS'
1374 ,p_procedure_name => 'GEN_BILLING_AMOUNTS');
1375
1376 IF P_PA_DEBUG_MODE = 'Y' THEN
1377 PA_DEBUG.Reset_curr_function;
1378 END IF;
1379
1380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1381
1382 END GEN_BILLING_AMOUNTS;
1383
1384 PROCEDURE GET_BILLING_EVENT_AMT_IN_PFC
1385 (P_PROJECT_ID IN pa_projects_all.project_id%type,
1386 P_BUDGET_VERSION_ID IN pa_budget_versions.budget_version_id%type,
1387 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
1388 P_PROJFUNC_CURRENCY_CODE IN pa_projects_all.projfunc_currency_code%type,
1389 P_PROJECT_CURRENCY_CODE IN pa_projects_all.project_currency_code%type,
1390 X_PROJFUNC_REVENUE OUT NOCOPY NUMBER,
1391 X_PROJECT_REVENUE OUT NOCOPY NUMBER,
1392 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1393 X_MSG_COUNT OUT NOCOPY NUMBER,
1394 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
1395 l_module_name VARCHAR2(200) :=
1396 'pa.plsql.PA_FP_GEN_BILLING_AMOUNTS.GET_BILLING_EVENT_AMT_IN_PFC';
1397 l_event_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1398 l_txn_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1399 l_txn_rev_amt_tab PA_PLSQL_DATATYPES.NumTabTyp;
1400 l_projfunc_rev_amt_tab PA_PLSQL_DATATYPES.NumTabTyp;
1401 l_project_rev_amt_tab PA_PLSQL_DATATYPES.NumTabTyp;
1402
1403 l_projfunc_raw_cost NUMBER;
1404 l_projfunc_burdened_cost NUMBER;
1405 l_projfunc_revenue NUMBER;
1406 l_projfunc_rejection_code VARCHAR2(50);
1407 l_proj_raw_cost NUMBER;
1408 l_proj_burdened_cost NUMBER;
1409 l_proj_revenue NUMBER;
1410 l_proj_rejection_code VARCHAR2(50);
1411
1412 l_conversion_required_flag VARCHAR2(1);
1413
1414 l_etc_start_date DATE;
1415
1416 l_txn_source_id_count NUMBER;
1417 --out param from MAP_BILLING_EVENT_RLMI_RBS
1418 l_map_txn_source_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1419 l_map_rlm_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1420 l_map_rbs_element_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1421 l_map_txn_accum_header_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1422
1423 l_msg_count NUMBER;
1424 l_msg_data VARCHAR2(2000);
1425 l_data VARCHAR2(2000);
1426 l_msg_index_out NUMBER:=0;
1427 BEGIN
1428 IF p_pa_debug_mode = 'Y' THEN
1429 pa_debug.set_curr_function( p_function => ' GET_BILLING_EVENT_AMT_IN_PFC',
1430 p_debug_mode => p_pa_debug_mode);
1431 END IF;
1432 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1433 X_MSG_COUNT := 0;
1434
1435 -- Initialize pc/pfc revenue out parameters.
1436 X_PROJFUNC_REVENUE := 0;
1437 X_PROJECT_REVENUE := 0;
1438
1439 SELECT etc_start_date
1440 INTO l_etc_start_date
1441 FROM pa_budget_versions
1442 WHERE budget_version_id = P_BUDGET_VERSION_ID;
1443
1444 -- Bug 4067837: Added check for Retain Manual Lines flag. If the flag
1445 -- is 'N', then we fetch Billing Event amounts using the original query.
1446 -- If the flag is 'Y', then we do the following:
1447 -- * Call the Mapping API to populate the tmp4 table
1448 -- * Update the tmp4 table with txn_resource_assignment_ids,
1449 -- and remove records for manually added resources.
1450 -- * Fetch Billing Event amounts using the original query, but
1451 -- modified to join with the tmp4 table so that we only pick up
1452 -- events that are mapped to non-manually-added resources.
1453
1454 IF p_fp_cols_rec.x_gen_ret_manual_line_flag = 'N' THEN
1455 SELECT V.EVENT_DATE,
1456 V.BILL_TRANS_CURRENCY_CODE,
1457 SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION,
1458 'WRITE OFF', -1 * NVL(V.BILL_TRANS_REV_AMOUNT,0),
1459 'REALIZED_LOSSES', -1 * NVL(V.BILL_TRANS_REV_AMOUNT,0),
1460 NVL(V.BILL_TRANS_REV_AMOUNT,0))),
1461 SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION,
1462 'WRITE OFF', -1 * NVL(V.PROJFUNC_REVENUE_AMOUNT,0),
1463 'REALIZED_LOSSES', -1 * NVL(V.PROJFUNC_REVENUE_AMOUNT,0),
1464 NVL(V.PROJFUNC_REVENUE_AMOUNT,0))),
1465 SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION,
1466 'WRITE OFF', -1 * NVL(V.PROJECT_REVENUE_AMOUNT,0),
1467 'RZED_LOSSES', -1 * NVL(V.PROJECT_REVENUE_AMOUNT,0),
1468 NVL(V.PROJECT_REVENUE_AMOUNT,0)))
1469 BULK COLLECT
1470 INTO l_event_date_tab,
1471 l_txn_currency_code_tab,
1472 l_txn_rev_amt_tab,
1473 l_projfunc_rev_amt_tab,
1474 l_project_rev_amt_tab
1475 FROM PA_EVENTS_DELIVERABLE_V V,
1476 PA_EVENT_TYPES ET
1477 WHERE V.PROJECT_ID = P_PROJECT_ID
1478 AND V.EVENT_DATE >= NVL(l_etc_start_date, V.EVENT_DATE)
1479 AND V.EVENT_TYPE = ET.EVENT_TYPE
1480 AND NVL(V.BILL_TRANS_REV_AMOUNT,0) <> 0
1481 GROUP BY V.EVENT_DATE,
1482 V.BILL_TRANS_CURRENCY_CODE;
1483 ELSIF p_fp_cols_rec.x_gen_ret_manual_line_flag = 'Y' THEN
1484 -- Call the Billing Events mapping API wrapper to populate the tm4
1485 -- table with the mapping from billing events to target resources.
1486 IF p_pa_debug_mode = 'Y' THEN
1487 pa_fp_gen_amount_utils.fp_debug
1488 ( p_msg => 'Before calling pa_fp_gen_billing_amounts.
1489 MAP_BILLING_EVENT_RLMI_RBS',
1490 p_module_name => l_module_name,
1491 p_log_level => 5 );
1492 END IF;
1493 PA_FP_GEN_BILLING_AMOUNTS.MAP_BILLING_EVENT_RLMI_RBS
1494 ( P_PROJECT_ID => p_project_id,
1495 P_BUDGET_VERSION_ID => p_budget_version_id,
1496 P_FP_COLS_REC => p_fp_cols_rec,
1497 X_TXN_SOURCE_ID_COUNT => l_txn_source_id_count,
1498 X_TXN_SOURCE_ID_TAB => l_map_txn_source_id_tab,
1499 X_RES_LIST_MEMBER_ID_TAB => l_map_rlm_id_tab,
1500 X_RBS_ELEMENT_ID_TAB => l_map_rbs_element_id_tab,
1501 X_TXN_ACCUM_HEADER_ID_TAB => l_map_txn_accum_header_id_tab,
1502 X_RETURN_STATUS => x_return_status,
1503 X_MSG_COUNT => x_msg_count,
1504 X_MSG_DATA => x_msg_data );
1505 IF p_pa_debug_mode = 'Y' THEN
1506 pa_fp_gen_amount_utils.fp_debug
1507 ( p_msg => 'Status after calling pa_fp_gen_billing_amounts.
1508 MAP_BILLING_EVENT_RLMI_RBS:'
1509 ||x_return_status,
1510 p_module_name => l_module_name,
1511 p_log_level => 5 );
1512 END IF;
1513 -- X_TXN_SOURCE_ID_COUNT = 0 means there are no events to process.
1514 IF l_txn_source_id_count = 0 THEN
1515 IF P_PA_DEBUG_MODE = 'Y' THEN
1516 PA_DEBUG.Reset_curr_function;
1517 END IF;
1518 RETURN;
1519 END IF;
1520
1521 -- Bug 4297225: As of this bug fix, we no longer join pa_res_list_tmp4
1522 -- with pa_resource_assignments using the Target resource assignment id.
1523 -- However, we still need to call the UPD_TMP4_TXN_RA_ID_AND_ML API to
1524 -- handle the Retain Manually Added Plan Lines logic for the tmp4 table,
1525 -- which is still used when fetching Billing Event amounts downstream.
1526
1527 -- Call API to update tmp4 with txn_resource_assignment_ids and to
1528 -- clear records for manually added resources from the tmp4 table.
1529 IF p_pa_debug_mode = 'Y' THEN
1530 pa_fp_gen_amount_utils.fp_debug
1531 ( p_msg => 'Before calling pa_fp_gen_billing_amounts.
1532 UPD_TMP4_TXN_RA_ID_AND_ML',
1533 p_module_name => l_module_name,
1534 p_log_level => 5 );
1535 END IF;
1536 PA_FP_GEN_BILLING_AMOUNTS.UPD_TMP4_TXN_RA_ID_AND_ML
1537 ( P_PROJECT_ID => p_project_id,
1538 P_BUDGET_VERSION_ID => p_budget_version_id,
1539 P_FP_COLS_REC => p_fp_cols_rec,
1540 P_GEN_SRC_CODE => 'BILLING_EVENTS',
1541 X_RETURN_STATUS => x_return_status,
1542 X_MSG_COUNT => x_msg_count,
1543 X_MSG_DATA => x_msg_data );
1544 IF p_pa_debug_mode = 'Y' THEN
1545 pa_fp_gen_amount_utils.fp_debug
1546 ( p_msg => 'Status after calling pa_fp_gen_billing_amounts.
1547 UPD_TMP4_TXN_RA_ID_AND_ML:'
1548 ||x_return_status,
1549 p_module_name => l_module_name,
1550 p_log_level => 5 );
1551 END IF;
1552
1553 -- Bug 4297225: Billing Events may map to Target resources that have not
1554 -- been created yet. Still, we should include the amounts for such Billing
1555 -- Events in the total amount returned by this API. To this end, the join
1556 -- between pa_resource_assignments with pa_res_list_tmp4 has been commented
1557 -- out in the query below that gets Billing Event amounts.
1558
1559 SELECT /*+ INDEX(TMP,PA_RES_LIST_MAP_TMP4_N2)*/
1560 V.EVENT_DATE,
1561 V.BILL_TRANS_CURRENCY_CODE,
1562 SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION,
1563 'WRITE OFF', -1 * NVL(V.BILL_TRANS_REV_AMOUNT,0),
1564 'REALIZED_LOSSES', -1 * NVL(V.BILL_TRANS_REV_AMOUNT,0),
1565 NVL(V.BILL_TRANS_REV_AMOUNT,0))),
1566 SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION,
1567 'WRITE OFF', -1 * NVL(V.PROJFUNC_REVENUE_AMOUNT,0),
1568 'REALIZED_LOSSES', -1 * NVL(V.PROJFUNC_REVENUE_AMOUNT,0),
1569 NVL(V.PROJFUNC_REVENUE_AMOUNT,0))),
1570 SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION,
1571 'WRITE OFF', -1 * NVL(V.PROJECT_REVENUE_AMOUNT,0),
1572 'RZED_LOSSES', -1 * NVL(V.PROJECT_REVENUE_AMOUNT,0),
1573 NVL(V.PROJECT_REVENUE_AMOUNT,0)))
1574 BULK COLLECT
1575 INTO l_event_date_tab,
1576 l_txn_currency_code_tab,
1577 l_txn_rev_amt_tab,
1578 l_projfunc_rev_amt_tab,
1579 l_project_rev_amt_tab
1580 FROM PA_EVENTS_DELIVERABLE_V V,
1581 PA_EVENT_TYPES ET,
1582 PA_RES_LIST_MAP_TMP4 TMP
1583 --,PA_RESOURCE_ASSIGNMENTS RA
1584 WHERE V.PROJECT_ID = P_PROJECT_ID
1585 AND V.EVENT_DATE >= NVL(l_etc_start_date, V.EVENT_DATE)
1586 AND V.EVENT_TYPE = ET.EVENT_TYPE
1587 AND NVL(V.BILL_TRANS_REV_AMOUNT,0) <> 0
1588 AND TMP.TXN_SOURCE_ID = V.EVENT_ID
1589 --AND RA.RESOURCE_ASSIGNMENT_ID = TMP.TXN_RESOURCE_ASSIGNMENT_ID
1590 --AND RA.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
1591 GROUP BY V.EVENT_DATE,
1592 V.BILL_TRANS_CURRENCY_CODE;
1593 END IF; -- manual lines check
1594
1595 -- End changes for Bug 4067837
1596
1597 FOR i IN 1..l_event_date_tab.count LOOP
1598 l_conversion_required_flag := 'N';
1599
1600 IF l_txn_currency_code_tab(i) = P_PROJFUNC_CURRENCY_CODE THEN
1601 l_projfunc_revenue := l_txn_rev_amt_tab(i);
1602 ELSIF l_projfunc_rev_amt_tab(i) <> 0 THEN
1603 l_projfunc_revenue := l_projfunc_rev_amt_tab(i);
1604 ELSE
1605 l_conversion_required_flag := 'Y';
1606 END IF;
1607
1608 IF l_txn_currency_code_tab(i) = P_PROJECT_CURRENCY_CODE THEN
1609 l_proj_revenue := l_txn_rev_amt_tab(i);
1610 ELSIF l_project_rev_amt_tab(i) <> 0 THEN
1611 l_proj_revenue := l_project_rev_amt_tab(i);
1612 ELSE
1613 l_conversion_required_flag := 'Y';
1614 END IF;
1615
1616 IF l_conversion_required_flag = 'Y' THEN
1617 IF p_pa_debug_mode = 'Y' THEN
1618 pa_fp_gen_amount_utils.fp_debug
1619 (p_msg => 'Before calling
1620 pa_fp_gen_billing_amounts.CONVERT_TXN_AMT_TO_PC_PFC',
1621 p_module_name => l_module_name,
1622 p_log_level => 5);
1623 END IF;
1624 PA_FP_GEN_BILLING_AMOUNTS.CONVERT_TXN_AMT_TO_PC_PFC
1625 (P_PROJECT_ID => P_PROJECT_ID,
1626 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
1627 P_RES_ASG_ID => NULL,
1628 P_START_DATE => l_event_date_tab(i),
1629 P_END_DATE => l_event_date_tab(i),
1630 P_CURRENCY_CODE => l_txn_currency_code_tab(i),
1631 P_TXN_RAW_COST => NULL,
1632 P_TXN_BURDENED_COST => NULL,
1633 P_TXN_REV_AMOUNT => l_txn_rev_amt_tab(i),
1634 X_PROJFUNC_RAW_COST => l_projfunc_raw_cost,
1635 X_PROJFUNC_BURDENED_COST => l_projfunc_burdened_cost,
1636 X_PROJFUNC_REVENUE => l_projfunc_revenue,
1637 X_PROJFUNC_REJECTION => l_projfunc_rejection_code,
1638 X_PROJ_RAW_COST => l_proj_raw_cost,
1639 X_PROJ_BURDENED_COST => l_proj_burdened_cost,
1640 X_PROJ_REVENUE => l_proj_revenue,
1641 X_PROJ_REJECTION => l_proj_rejection_code,
1642 X_RETURN_STATUS => x_return_status,
1643 X_MSG_COUNT => x_MSG_COUNT,
1644 X_MSG_DATA => x_MSG_DATA);
1645 IF p_pa_debug_mode = 'Y' THEN
1646 pa_fp_gen_amount_utils.fp_debug
1647 (p_msg => 'Status after calling
1648 pa_fp_gen_billing_amounts.CONVERT_TXN_AMT_TO_PC_PFC:'
1649 ||x_return_status,
1650 p_module_name => l_module_name,
1651 p_log_level => 5);
1652 END IF;
1653 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1654 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1655 END IF;
1656 END IF;
1657
1658 X_PROJFUNC_REVENUE := X_PROJFUNC_REVENUE + NVL(l_projfunc_revenue,0);
1659 X_PROJECT_REVENUE := X_PROJECT_REVENUE + NVL(l_proj_revenue,0);
1660 END LOOP;
1661
1662 IF p_pa_debug_mode = 'Y' THEN
1663 PA_DEBUG.Reset_Curr_Function;
1664 END IF;
1665 EXCEPTION
1666 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1667 l_msg_count := FND_MSG_PUB.count_msg;
1668 IF l_msg_count = 1 THEN
1669 PA_INTERFACE_UTILS_PUB.get_messages
1670 (p_encoded => FND_API.G_TRUE
1671 ,p_msg_index => 1
1672 ,p_msg_count => l_msg_count
1673 ,p_msg_data => l_msg_data
1674 ,p_data => l_data
1675 ,p_msg_index_out => l_msg_index_out);
1676 x_msg_data := l_data;
1677 x_msg_count := l_msg_count;
1678 ELSE
1679 x_msg_count := l_msg_count;
1680 END IF;
1681 ROLLBACK;
1682
1683 x_return_status := FND_API.G_RET_STS_ERROR;
1684 IF P_PA_DEBUG_MODE = 'Y' THEN
1685 pa_fp_gen_amount_utils.fp_debug
1686 (p_msg => 'Invalid Arguments Passed',
1687 p_module_name => l_module_name,
1688 p_log_level => 5);
1689 PA_DEBUG.Reset_Curr_Function;
1690 END IF;
1691 RAISE;
1692
1693 WHEN OTHERS THEN
1694 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1695 x_msg_data := SUBSTR(SQLERRM,1,240);
1696 FND_MSG_PUB.add_exc_msg
1697 ( p_pkg_name => 'PA_FP_GEN_BILLING_AMOUNTS',
1698 p_procedure_name => 'GET_BILLING_EVENT_AMT_IN_PFC');
1699 IF P_PA_DEBUG_MODE = 'Y' THEN
1700 pa_fp_gen_amount_utils.fp_debug
1701 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1702 p_module_name => l_module_name,
1703 p_log_level => 5);
1704 PA_DEBUG.Reset_Curr_Function;
1705 END IF;
1706 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1707 END GET_BILLING_EVENT_AMT_IN_PFC;
1708
1709
1710 /**
1711 * This procedure calls the PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS API
1712 * with all of the parameter information for Billing Events.
1713 *
1714 * The logic for this procedure has been taken directly from the
1715 * GEN_BILLING_AMOUNTS API (PAFPGABB.pls version 115.28).
1716 *
1717 * This API has been created for the GET_BILLING_EVENT_AMT_IN_PFC
1718 * API to address bug 4067836.
1719 */
1720 PROCEDURE MAP_BILLING_EVENT_RLMI_RBS
1721 (P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
1722 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
1723 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
1724 X_TXN_SOURCE_ID_COUNT OUT NOCOPY NUMBER,
1725 X_TXN_SOURCE_ID_TAB OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
1726 X_RES_LIST_MEMBER_ID_TAB OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
1727 X_RBS_ELEMENT_ID_TAB OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
1728 X_TXN_ACCUM_HEADER_ID_TAB OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
1729 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1730 X_MSG_COUNT OUT NOCOPY NUMBER,
1731 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
1732
1733 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_BILLING_AMOUNTS.MAP_BILLING_EVENT_RLMI_RBS';
1734
1735 l_ret_status VARCHAR2(100);
1736 l_msg_count NUMBER;
1737 l_msg_data VARCHAR2(2000);
1738 l_data VARCHAR2(2000);
1739 l_msg_index_out NUMBER:=0;
1740
1741 l_count1 NUMBER;
1742 l_project_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1743 --Local pl/sql table to call Map_Rlmi_Rbs api
1744 l_TXN_SOURCE_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
1745 l_TXN_SOURCE_TYPE_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1746 l_PERSON_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
1747 l_JOB_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
1748 l_ORGANIZATION_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
1749 l_VENDOR_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
1750 l_EXPENDITURE_TYPE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1751 l_EVENT_TYPE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1752 l_NON_LABOR_RESOURCE_tab PA_PLSQL_DATATYPES.Char20TabTyp;
1753 l_EXPENDITURE_CATEGORY_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1754 l_REVENUE_CATEGORY_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1755 l_NLR_ORGANIZATION_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
1756 l_EVENT_CLASSIFICATION_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1757 l_SYS_LINK_FUNCTION_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1758 l_PROJECT_ROLE_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
1759 l_RESOURCE_CLASS_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1760 l_MFC_COST_TYPE_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
1761 l_RESOURCE_CLASS_FLAG_tab PA_PLSQL_DATATYPES.Char1TabTyp;
1762 l_FC_RES_TYPE_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1763 l_INVENTORY_ITEM_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
1764 l_ITEM_CATEGORY_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
1765 l_PERSON_TYPE_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1766 l_BOM_RESOURCE_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
1767 l_NAMED_ROLE_tab PA_PLSQL_DATATYPES.Char80TabTyp;
1768 l_INCURRED_BY_RES_FLAG_tab PA_PLSQL_DATATYPES.Char1TabTyp;
1769 l_RATE_BASED_FLAG_tab PA_PLSQL_DATATYPES.Char1TabTyp;
1770 l_TXN_TASK_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
1771 l_TXN_WBS_ELEMENT_VER_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
1772 l_TXN_RBS_ELEMENT_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
1773 l_TXN_PLAN_START_DATE_tab PA_PLSQL_DATATYPES.DateTabTyp;
1774 l_TXN_PLAN_END_DATE_tab PA_PLSQL_DATATYPES.DateTabTyp;
1775 --out param from PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS
1776 l_map_txn_source_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1777 l_map_rlm_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1778 l_map_rbs_element_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1779 l_map_txn_accum_header_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1780
1781 BEGIN
1782 /* Setting initial values */
1783 X_MSG_COUNT := 0;
1784 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1785
1786 IF p_pa_debug_mode = 'Y' THEN
1787 pa_debug.set_curr_function( p_function => 'MAP_BILLING_EVENT_RLMI_RBS'
1788 ,p_debug_mode => p_pa_debug_mode);
1789 END IF;
1790
1791 /* Deleting all the records from the temporary table */
1792 DELETE FROM PA_RES_LIST_MAP_TMP1;
1793 DELETE FROM PA_RES_LIST_MAP_TMP4;
1794
1795 SELECT PROJECT_ID,
1796 nvl(TASK_ID,0),
1797 EVENT_ID,
1798 EVENT_TYPE,
1799 'BILLING_EVENTS',
1800 ORGANIZATION_ID,
1801 INVENTORY_ITEM_ID,
1802 event_date,
1803 event_date,
1804 DECODE(EVENT_TYPE,null,NULL,'EVENT_TYPE'),
1805 'FINANCIAL_ELEMENTS'
1806 BULK COLLECT
1807 INTO l_project_id_tab,
1808 l_TXN_TASK_ID_tab,
1809 l_TXN_SOURCE_ID_tab,
1810 l_EVENT_TYPE_tab,
1811 l_TXN_SOURCE_TYPE_CODE_tab,
1812 l_ORGANIZATION_ID_tab,
1813 l_INVENTORY_ITEM_ID_tab,
1814 l_TXN_PLAN_START_DATE_tab,
1815 l_TXN_PLAN_END_DATE_tab,
1816 l_FC_RES_TYPE_CODE_tab,
1817 l_RESOURCE_CLASS_CODE_tab
1818 FROM PA_EVENTS_DELIVERABLE_V
1819 WHERE PROJECT_ID = P_PROJECT_ID;
1820
1821 x_txn_source_id_count := l_TXN_SOURCE_ID_tab.count;
1822
1823 IF l_TXN_SOURCE_ID_tab.count = 0 THEN
1824 IF P_PA_DEBUG_MODE = 'Y' THEN
1825 PA_DEBUG.Reset_curr_function;
1826 END IF;
1827 RETURN;
1828 END IF;
1829
1830 FOR bb in 1..l_TXN_SOURCE_ID_tab.count LOOP
1831 l_PERSON_ID_tab(bb) := null;
1832 l_JOB_ID_tab(bb) := null;
1833 l_VENDOR_ID_tab(bb) := null;
1834 l_EXPENDITURE_TYPE_tab(bb) := null;
1835 l_NON_LABOR_RESOURCE_tab(bb) := null;
1836 l_EXPENDITURE_CATEGORY_tab(bb) := null;
1837 l_REVENUE_CATEGORY_CODE_tab(bb) := null;
1838 l_NLR_ORGANIZATION_ID_tab(bb) := null;
1839 l_EVENT_CLASSIFICATION_tab(bb) := null;
1840 l_SYS_LINK_FUNCTION_tab(bb) := null;
1841 l_PROJECT_ROLE_ID_tab(bb) := null;
1842 l_MFC_COST_TYPE_ID_tab(bb) := null;
1843 l_RESOURCE_CLASS_FLAG_tab(bb) := null;
1844 l_ITEM_CATEGORY_ID_tab(bb) := null;
1845 l_PERSON_TYPE_CODE_tab(bb) := null;
1846 l_BOM_RESOURCE_ID_tab(bb) := null;
1847 l_NAMED_ROLE_tab(bb) := null;
1848 l_INCURRED_BY_RES_FLAG_tab(bb) := null;
1849 l_RATE_BASED_FLAG_tab(bb) := null;
1850 l_TXN_WBS_ELEMENT_VER_ID_tab(bb):= null;
1851 l_TXN_RBS_ELEMENT_ID_tab(bb) := null;
1852 END LOOP;
1853 --dbms_output.put_line('l_TXN_SOURCE_ID_tab.count: '||l_TXN_SOURCE_ID_tab.count);
1854 IF P_PA_DEBUG_MODE = 'Y' THEN
1855 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1856 P_MSG => 'Before calling PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS',
1857 P_MODULE_NAME => l_module_name);
1858 END IF;
1859 PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS (
1860 P_PROJECT_ID => p_project_id,
1861 P_BUDGET_VERSION_ID => NULL,
1862 P_RESOURCE_LIST_ID => P_FP_COLS_REC.X_RESOURCE_LIST_ID,
1863 P_RBS_VERSION_ID => NULL,
1864 P_CALLING_PROCESS => 'BUDGET_GENERATION',
1865 P_CALLING_CONTEXT => 'PLSQL',
1866 P_PROCESS_CODE => 'RES_MAP',
1867 P_CALLING_MODE => 'PLSQL_TABLE',
1868 P_INIT_MSG_LIST_FLAG => 'N',
1869 P_COMMIT_FLAG => 'N',
1870 P_TXN_SOURCE_ID_TAB => l_TXN_SOURCE_ID_tab,
1871 P_TXN_SOURCE_TYPE_CODE_TAB => l_TXN_SOURCE_TYPE_CODE_tab,
1872 P_PERSON_ID_TAB => l_PERSON_ID_tab,
1873 P_JOB_ID_TAB => l_JOB_ID_tab,
1874 P_ORGANIZATION_ID_TAB => l_ORGANIZATION_ID_tab,
1875 P_VENDOR_ID_TAB => l_VENDOR_ID_tab,
1876 P_EXPENDITURE_TYPE_TAB => l_EXPENDITURE_TYPE_tab,
1877 P_EVENT_TYPE_TAB => l_EVENT_TYPE_tab,
1878 P_NON_LABOR_RESOURCE_TAB => l_NON_LABOR_RESOURCE_tab,
1879 P_EXPENDITURE_CATEGORY_TAB => l_EXPENDITURE_CATEGORY_tab,
1880 P_REVENUE_CATEGORY_CODE_TAB =>l_REVENUE_CATEGORY_CODE_tab,
1881 P_NLR_ORGANIZATION_ID_TAB =>l_NLR_ORGANIZATION_ID_tab,
1882 P_EVENT_CLASSIFICATION_TAB => l_EVENT_CLASSIFICATION_tab,
1883 P_SYS_LINK_FUNCTION_TAB => l_SYS_LINK_FUNCTION_tab,
1884 P_PROJECT_ROLE_ID_TAB => l_PROJECT_ROLE_ID_tab,
1885 P_RESOURCE_CLASS_CODE_TAB => l_RESOURCE_CLASS_CODE_tab,
1886 P_MFC_COST_TYPE_ID_TAB => l_MFC_COST_TYPE_ID_tab,
1887 P_RESOURCE_CLASS_FLAG_TAB => l_RESOURCE_CLASS_FLAG_tab,
1888 P_FC_RES_TYPE_CODE_TAB => l_FC_RES_TYPE_CODE_tab,
1889 P_INVENTORY_ITEM_ID_TAB => l_INVENTORY_ITEM_ID_tab,
1890 P_ITEM_CATEGORY_ID_TAB => l_ITEM_CATEGORY_ID_tab,
1891 P_PERSON_TYPE_CODE_TAB => l_PERSON_TYPE_CODE_tab,
1892 P_BOM_RESOURCE_ID_TAB =>l_BOM_RESOURCE_ID_tab,
1893 P_NAMED_ROLE_TAB =>l_NAMED_ROLE_tab,
1894 P_INCURRED_BY_RES_FLAG_TAB =>l_INCURRED_BY_RES_FLAG_tab,
1895 P_RATE_BASED_FLAG_TAB =>l_RATE_BASED_FLAG_tab,
1896 P_TXN_TASK_ID_TAB =>l_TXN_TASK_ID_tab,
1897 P_TXN_WBS_ELEMENT_VER_ID_TAB => l_TXN_WBS_ELEMENT_VER_ID_tab,
1898 P_TXN_RBS_ELEMENT_ID_TAB => l_TXN_RBS_ELEMENT_ID_tab,
1899 P_TXN_PLAN_START_DATE_TAB => l_TXN_PLAN_START_DATE_tab,
1900 P_TXN_PLAN_END_DATE_TAB => l_TXN_PLAN_END_DATE_tab,
1901 X_TXN_SOURCE_ID_TAB =>x_txn_source_id_tab,
1902 X_RES_LIST_MEMBER_ID_TAB =>x_res_list_member_id_tab,
1903 X_RBS_ELEMENT_ID_TAB =>x_rbs_element_id_tab,
1904 X_TXN_ACCUM_HEADER_ID_TAB =>x_txn_accum_header_id_tab,
1905 X_RETURN_STATUS => x_return_status,
1906 X_MSG_COUNT => x_msg_count,
1907 X_MSG_DATA => x_msg_data );
1908 IF P_PA_DEBUG_MODE = 'Y' THEN
1909 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1910 P_MSG => 'After calling PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS: '||
1911 x_return_status,
1912 P_MODULE_NAME => l_module_name);
1913 END IF;
1914
1915 /*dbms_output.put_line('After calling PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS: '||x_return_status);
1916 dbms_output.put_line('l_map_rlm_id_tab.count: '||l_map_rlm_id_tab.count);*/
1917 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1918 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1919 END IF;
1920
1921 SELECT /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
1922 count(*) INTO l_count1
1923 FROM PA_RES_LIST_MAP_TMP4
1924 WHERE RESOURCE_LIST_MEMBER_ID IS NULL and rownum=1;
1925 IF l_count1 > 0 THEN
1926 PA_UTILS.ADD_MESSAGE
1927 (p_app_short_name => 'PA',
1928 p_msg_name => 'PA_INVALID_MAPPING_ERR');
1929 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1930 END IF;
1931
1932 IF P_PA_DEBUG_MODE = 'Y' THEN
1933 PA_DEBUG.Reset_curr_function;
1934 END IF;
1935
1936 EXCEPTION
1937 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1938 -- Bug Fix: 4569365. Removed MRC code.
1939 -- PA_MRC_FINPLAN.G_CALLING_MODULE := Null;
1940 l_msg_count := FND_MSG_PUB.count_msg;
1941 IF l_msg_count = 1 THEN
1942 PA_INTERFACE_UTILS_PUB.get_messages
1943 (p_encoded => FND_API.G_TRUE
1944 ,p_msg_index => 1
1945 ,p_msg_count => l_msg_count
1946 ,p_msg_data => l_msg_data
1947 ,p_data => l_data
1948 ,p_msg_index_out => l_msg_index_out);
1949 x_msg_data := l_data;
1950 x_msg_count := l_msg_count;
1951 ELSE
1952 x_msg_count := l_msg_count;
1953 END IF;
1954 ROLLBACK;
1955
1956 x_return_status := FND_API.G_RET_STS_ERROR;
1957
1958 IF P_PA_DEBUG_MODE = 'Y' THEN
1959 PA_DEBUG.Reset_curr_function;
1960 END IF;
1961
1962 RAISE;
1963
1964 WHEN OTHERS THEN
1965 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1966 x_msg_data := SUBSTR(SQLERRM,1,240);
1967 FND_MSG_PUB.add_exc_msg
1968 ( p_pkg_name => 'PA_FP_GEN_BILLING_AMOUNTS'
1969 ,p_procedure_name => 'MAP_BILLING_EVENT_RLMI_RBS');
1970
1971 IF P_PA_DEBUG_MODE = 'Y' THEN
1972 PA_DEBUG.Reset_curr_function;
1973 END IF;
1974
1975 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1976
1977 END MAP_BILLING_EVENT_RLMI_RBS;
1978
1979 /**
1980 * This procedure updates PA_RES_LIST_TMP4 records with the proper
1981 * txn_resource_assignment_id. Additionally, if the Retain Manually
1982 * Added Plan Lines option is enabled, then records for manually
1983 * added resources are deleted from the tmp4 table.
1984 *
1985 * The logic for this procedure has been taken directly from the
1986 * UPDATE_RES_ASG API (PAFPGAMB.pls version 115.90).
1987 *
1988 * This API has been created for the GET_BILLING_EVENT_AMT_IN_PFC
1989 * API to address bug 4067836.
1990 *
1991 * Note: parameter P_WP_STRUCTURE_VER_ID has Default value of Null.
1992 */
1993 PROCEDURE UPD_TMP4_TXN_RA_ID_AND_ML
1994 (P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
1995 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
1996 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
1997 P_GEN_SRC_CODE IN PA_PROJ_FP_OPTIONS.GEN_ALL_SRC_CODE%TYPE,
1998 P_WP_STRUCTURE_VER_ID IN PA_BUDGET_VERSIONS.PROJECT_STRUCTURE_VERSION_ID%TYPE,
1999 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2000 X_MSG_COUNT OUT NOCOPY NUMBER,
2001 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
2002
2003 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_BILLING_AMOUNTS.UPD_TMP4_TXN_RA_ID_AND_ML';
2004
2005 l_etc_start_date DATE;
2006 l_stru_sharing_code PA_PROJECTS_ALL.STRUCTURE_SHARING_CODE%TYPE;
2007
2008 l_res_assgn_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2009 l_rlm_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2010 l_txn_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2011 l_txn_top_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2012 l_txn_sub_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2013 l_mapped_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2014
2015
2016 l_ret_status VARCHAR2(100);
2017 l_msg_count NUMBER;
2018 l_msg_data VARCHAR2(2000);
2019 l_data VARCHAR2(2000);
2020 l_msg_index_out NUMBER:=0;
2021 BEGIN
2022 /* Setting initial values */
2023 X_MSG_COUNT := 0;
2024 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2025
2026 IF p_pa_debug_mode = 'Y' THEN
2027 pa_debug.set_curr_function( p_function => 'UPD_TMP4_TXN_RA_ID_AND_ML'
2028 ,p_debug_mode => p_pa_debug_mode);
2029 END IF;
2030
2031 l_stru_sharing_code := PA_PROJECT_STRUCTURE_UTILS.
2032 get_Structure_sharing_code(P_PROJECT_ID=> P_PROJECT_ID);
2033
2034 IF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'P' or
2035 P_GEN_SRC_CODE = 'RESOURCE_SCHEDULE' THEN
2036
2037 /* Updating the TMP4 table with resource_assignment_id */
2038 SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
2039 distinct P.RESOURCE_ASSIGNMENT_ID,
2040 P.RESOURCE_LIST_MEMBER_ID
2041 BULK COLLECT
2042 INTO l_res_assgn_id_tab,
2043 l_rlm_id_tab
2044 FROM PA_RESOURCE_ASSIGNMENTS P,
2045 PA_RES_LIST_MAP_TMP4 T
2046 WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
2047 AND NVL(P.TASK_ID,0) = 0
2048 AND P.PROJECT_ASSIGNMENT_ID = -1
2049 AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID;
2050
2051 FORALL i IN 1..l_res_assgn_id_tab.count
2052 UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
2053 PA_RES_LIST_MAP_TMP4
2054 SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
2055 WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i);
2056 /* AND NVL(TXN_TASK_ID,0) = l_txn_task_id_tab(i);
2057 task id check is not required. commented for bug 3475017 */
2058
2059 /* Updating the TMP4 table with resource_assignment_id
2060 when planning level is Lowest task (Financial task only)*/
2061 ELSIF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'L'
2062 AND ( l_stru_sharing_code IS NULL OR
2063 l_stru_sharing_code = 'SHARE_FULL' OR
2064 P_GEN_SRC_CODE IN ( 'FINANCIAL_PLAN',
2065 'OPEN_COMMITMENTS','BILLING_EVENTS' )) THEN
2066
2067 SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
2068 P.RESOURCE_ASSIGNMENT_ID,
2069 P.RESOURCE_LIST_MEMBER_ID,
2070 NVL(T.TXN_TASK_ID,0)
2071 BULK COLLECT
2072 INTO l_res_assgn_id_tab,
2073 l_rlm_id_tab,
2074 l_txn_task_id_tab
2075 FROM PA_RESOURCE_ASSIGNMENTS P,
2076 PA_RES_LIST_MAP_TMP4 T
2077 WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
2078 AND NVL(P.TASK_ID,0) = NVL(T.TXN_TASK_ID,0)
2079 AND P.PROJECT_ASSIGNMENT_ID = -1
2080 AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID;
2081
2082 FORALL i IN 1..l_res_assgn_id_tab.count
2083 UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
2084 PA_RES_LIST_MAP_TMP4
2085 SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
2086 WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i)
2087 AND NVL(TXN_TASK_ID,0) = l_txn_task_id_tab(i);
2088
2089 /* Updating the TMP4 table with resource_assignment_id
2090 when planning level is Top task (Financial task only)*/
2091 ELSIF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'T'
2092 AND ( l_stru_sharing_code IS NULL OR
2093 l_stru_sharing_code = 'SHARE_FULL' OR
2094 P_GEN_SRC_CODE IN ( 'FINANCIAL_PLAN',
2095 'OPEN_COMMITMENTS','BILLING_EVENTS' )) THEN
2096
2097 SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
2098 P.RESOURCE_ASSIGNMENT_ID,
2099 P.RESOURCE_LIST_MEMBER_ID,
2100 NVL(P.TASK_ID,0),
2101 NVL(T.TXN_TASK_ID,0)
2102 BULK COLLECT
2103 INTO l_res_assgn_id_tab,
2104 l_rlm_id_tab,
2105 l_txn_top_task_id_tab,
2106 l_txn_sub_task_id_tab
2107 FROM PA_RESOURCE_ASSIGNMENTS P,
2108 PA_RES_LIST_MAP_TMP4 T,
2109 PA_TASKS TS
2110 WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
2111 AND TS.TASK_ID(+) = NVL(T.TXN_TASK_ID,0)
2112 AND NVL(P.TASK_ID,0) = NVL(TS.TOP_TASK_ID,0)
2113 AND P.PROJECT_ASSIGNMENT_ID = -1
2114 AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID;
2115
2116 FORALL i IN 1..l_res_assgn_id_tab.count
2117 UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
2118 PA_RES_LIST_MAP_TMP4 tmp4
2119 SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
2120 WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i)
2121 AND NVL(TXN_TASK_ID,0) = l_txn_sub_task_id_tab(i);
2122
2123 /* Updating the TMP4 table with resource_assignment_id when
2124 planning level is Lowest task (both Financial task and Workplan task)*/
2125
2126 ELSIF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'L' AND l_stru_sharing_code IS NOT NULL THEN
2127 SELECT resource_assignment_id,
2128 resource_list_member_id,
2129 txn_task_id,
2130 mapped_fin_task_id
2131 BULK COLLECT INTO
2132 l_res_assgn_id_tab,
2133 l_rlm_id_tab,
2134 l_txn_task_id_tab,
2135 l_mapped_task_id_tab
2136 FROM
2137 (
2138 SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
2139 P.RESOURCE_ASSIGNMENT_ID resource_assignment_id,
2140 P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
2141 NVL(T.TXN_TASK_ID,0) txn_task_id ,
2142 NVL(V.MAPPED_FIN_TASK_ID,0) mapped_fin_task_id
2143 FROM PA_RESOURCE_ASSIGNMENTS P,
2144 PA_RES_LIST_MAP_TMP4 T,
2145 PA_MAP_WP_TO_FIN_TASKS_V V
2146 WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
2147 AND V.PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VER_ID
2148 AND NVL(T.TXN_TASK_ID,0) = NVL(V.PROJ_ELEMENT_ID,0)
2149 AND P.PROJECT_ASSIGNMENT_ID = -1
2150 AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
2151 AND NVL(P.TASK_ID,0) = NVL(V.MAPPED_FIN_TASK_ID,0)
2152 AND NVL(T.TXN_TASK_ID,0) > 0
2153 union
2154 SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
2155 P.RESOURCE_ASSIGNMENT_ID resource_assignment_id,
2156 P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
2157 0 txn_task_id,
2158 0 mapped_fin_task_id
2159 FROM PA_RESOURCE_ASSIGNMENTS P,
2160 PA_RES_LIST_MAP_TMP4 T
2161 WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
2162 AND P.PROJECT_ASSIGNMENT_ID = -1
2163 AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
2164 AND NVL(P.TASK_ID,0) = 0 );
2165
2166 --@@
2167 IF P_PA_DEBUG_MODE = 'Y' THEN
2168 for i in 1..l_res_assgn_id_tab.count loop
2169 pa_fp_gen_amount_utils.fp_debug
2170 (p_msg => 'within update when share partial and planning at lowest task i:'
2171 ||i||'; ra id in cursor:'||l_res_assgn_id_tab(i)
2172 ||';rlm id in cursor:'||l_rlm_id_tab(i)
2173 ||';task id in cursor:'||l_txn_task_id_tab(i)
2174 ||';mapped task id in cursor:'||l_mapped_task_id_tab(i),
2175 p_module_name => l_module_name,
2176 p_log_level => 5);
2177 end loop;
2178 END IF;
2179 --@@
2180
2181 --dbms_output.put_line('@@l_res_assgn_id_tab.count'||l_res_assgn_id_tab.count);
2182 --dbms_output.put_line('@@l_res_assgn_id_tab(1):'||l_res_assgn_id_tab(1));
2183 --dbms_output.put_line('@@l_res_assgn_id_tab(2):'||l_res_assgn_id_tab(2));
2184 --dbms_output.put_line('@@l_res_assgn_id_tab(3):'||l_res_assgn_id_tab(3));
2185 --dbms_output.put_line('@@l_res_assgn_id_tab(4):'||l_res_assgn_id_tab(4));
2186 --dbms_output.put_line('@@l_rlm_id_tab(1):'||l_rlm_id_tab(1));
2187 --dbms_output.put_line('@@l_rlm_id_tab(2):'||l_rlm_id_tab(2));
2188 --dbms_output.put_line('@@l_rlm_id_tab(1):'||l_rlm_id_tab(3));
2189 --dbms_output.put_line('@@l_rlm_id_tab(2):'||l_rlm_id_tab(4));
2190 --dbms_output.put_line('@@l_txn_task_id_tab(1):'||l_txn_task_id_tab(1));
2191 --dbms_output.put_line('@@l_txn_task_id_tab(2):'||l_txn_task_id_tab(2));
2192 --dbms_output.put_line('@@l_txn_task_id_tab(3):'||l_txn_task_id_tab(3));
2193 --dbms_output.put_line('@@l_txn_task_id_tab(4):'||l_txn_task_id_tab(4));
2194 --select count(*) into tmp_count from PA_RES_LIST_MAP_TMP4;
2195 --dbms_output.put_line('@@l_count of tmp4:'||tmp_count);
2196 --select txn_resource_assignment_id,resource_list_member_id, txn_task_id
2197 --bulk collect into tmp_ra_id_tab, tmp_rlm_id_tab, tmp_task_id_tab
2198 --from PA_RES_LIST_MAP_TMP4;
2199 --dbms_output.put_line('@@tmp_ra_id_tab.count'||tmp_ra_id_tab.count);
2200 --dbms_output.put_line('@@tmp_ra_id_tab(1):'||tmp_ra_id_tab(1));
2201 --dbms_output.put_line('@@tmp_ra_id_tab(2):'||tmp_ra_id_tab(2));
2202 --dbms_output.put_line('@@tmp_ra_id_tab(3):'||tmp_ra_id_tab(3));
2203 --dbms_output.put_line('@@tmp_rlm_id_tab(1):'||tmp_rlm_id_tab(1));
2204 --dbms_output.put_line('@@tmp_rlm_id_tab(2):'||tmp_rlm_id_tab(2));
2205 --dbms_output.put_line('@@tmp_rlm_id_tab(3):'||tmp_rlm_id_tab(3));
2206 --dbms_output.put_line('@@tmp_task_id_tab(1):'||tmp_task_id_tab(1));
2207 --dbms_output.put_line('@@tmp_task_id_tab(2):'||tmp_task_id_tab(2));
2208 --dbms_output.put_line('@@tmp_task_id_tab(3):'||tmp_task_id_tab(3));
2209
2210 FORALL i IN 1..l_res_assgn_id_tab.count
2211 UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
2212 PA_RES_LIST_MAP_TMP4
2213 SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
2214 WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i)
2215 AND NVL(TXN_TASK_ID,0) = l_txn_task_id_tab(i);
2216
2217 ELSIF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'T'
2218 AND l_stru_sharing_code IS NOT NULL THEN
2219 SELECT resource_assignment_id,
2220 resource_list_member_id,
2221 txn_task_id,
2222 mapped_fin_task_id
2223 BULK COLLECT INTO
2224 l_res_assgn_id_tab,
2225 l_rlm_id_tab,
2226 l_txn_task_id_tab,
2227 l_mapped_task_id_tab
2228 FROM
2229 (
2230 SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
2231 P.RESOURCE_ASSIGNMENT_ID resource_assignment_id,
2232 P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
2233 NVL(T.TXN_TASK_ID,0) txn_task_id,
2234 NVL(V.MAPPED_FIN_TASK_ID,0) mapped_fin_task_id
2235 FROM PA_RESOURCE_ASSIGNMENTS P,
2236 PA_RES_LIST_MAP_TMP4 T,
2237 PA_MAP_WP_TO_FIN_TASKS_V V,
2238 PA_TASKS TS
2239 WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
2240 AND V.PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VER_ID
2241 AND t.txn_task_id = v.PROJ_ELEMENT_ID
2242 AND NVL(TS.top_TASK_ID,0) = NVL(p.task_id,0)
2243 AND TS.TASK_ID(+) = NVL(V.MAPPED_FIN_TASK_ID,0)
2244 AND P.PROJECT_ASSIGNMENT_ID = -1
2245 AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
2246 AND NVL(T.TXN_TASK_ID,0) > 0
2247 union
2248 SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
2249 DISTINCT P.RESOURCE_ASSIGNMENT_ID resource_assignment_id,
2250 P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
2251 0 txn_task_id,
2252 0 mapped_fin_task_id
2253 FROM PA_RESOURCE_ASSIGNMENTS P,
2254 PA_RES_LIST_MAP_TMP4 T
2255 WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
2256 AND P.PROJECT_ASSIGNMENT_ID = -1
2257 AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
2258 AND NVL(P.TASK_ID,0) = 0
2259 AND NVL(T.TXN_TASK_ID,0) = NVL(P.TASK_ID,0) );
2260
2261 FORALL i IN 1..l_res_assgn_id_tab.count
2262 UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
2263 PA_RES_LIST_MAP_TMP4
2264 SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
2265 WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i)
2266 AND NVL(TXN_TASK_ID,0) = l_txn_task_id_tab(i);
2267
2268 END IF;
2269
2270 /* If the Retain Manually Added Plan Lines option is enabled, we remove
2271 * all rows in the PA_RES_LIST_MAP_TMP4 table with target resources that
2272 * have manually added plan lines. Thus, after this point, we can use the
2273 * mapping table without checking for the manually added lines condition. */
2274 IF p_fp_cols_rec.x_gen_ret_manual_line_flag = 'Y' THEN
2275 IF p_fp_cols_rec.x_plan_class_code = 'BUDGET' THEN
2276 DELETE FROM pa_res_list_map_tmp4 tmp
2277 WHERE EXISTS
2278 ( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
2279 FROM pa_resource_assignments ra
2280 WHERE ra.budget_version_id = p_budget_version_id
2281 AND ra.resource_assignment_id = tmp.txn_resource_assignment_id
2282 AND ra.transaction_source_code IS NULL
2283 AND EXISTS
2284 ( SELECT 1
2285 FROM pa_budget_lines bl
2286 WHERE bl.resource_assignment_id = ra.resource_assignment_id
2287 AND rownum = 1 ));
2288 ELSIF p_fp_cols_rec.x_plan_class_code = 'FORECAST' THEN
2289 l_etc_start_date := PA_FP_GEN_AMOUNT_UTILS.GET_ETC_START_DATE
2290 ( p_budget_version_id );
2291 IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
2292 DELETE FROM pa_res_list_map_tmp4 tmp
2293 WHERE EXISTS
2294 ( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
2295 FROM pa_resource_assignments ra
2296 WHERE ra.budget_version_id = p_budget_version_id
2297 AND ra.resource_assignment_id = tmp.txn_resource_assignment_id
2298 AND ra.transaction_source_code IS NULL
2299 AND EXISTS
2300 ( SELECT 1
2301 FROM pa_budget_lines bl
2302 WHERE bl.resource_assignment_id = ra.resource_assignment_id
2303 AND bl.start_date >= l_etc_start_date
2304 AND rownum = 1 ));
2305 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
2306 DELETE FROM pa_res_list_map_tmp4 tmp
2307 WHERE EXISTS
2308 ( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
2309 FROM pa_resource_assignments ra
2310 WHERE ra.budget_version_id = p_budget_version_id
2311 AND ra.resource_assignment_id = tmp.txn_resource_assignment_id
2312 AND ra.transaction_source_code IS NULL
2313 AND EXISTS
2314 ( SELECT 1
2315 FROM pa_budget_lines bl
2316 WHERE bl.resource_assignment_id = ra.resource_assignment_id
2317 AND NVL(quantity,0) <> NVL(init_quantity,0)
2318 AND rownum = 1 ));
2319 END IF;
2320 END IF;
2321 END IF; -- end manual lines logic
2322
2323 IF P_PA_DEBUG_MODE = 'Y' THEN
2324 PA_DEBUG.Reset_curr_function;
2325 END IF;
2326 EXCEPTION
2327 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2328 -- Bug Fix: 4569365. Removed MRC code.
2329 -- PA_MRC_FINPLAN.G_CALLING_MODULE := Null;
2330 l_msg_count := FND_MSG_PUB.count_msg;
2331 IF l_msg_count = 1 THEN
2332 PA_INTERFACE_UTILS_PUB.get_messages
2333 (p_encoded => FND_API.G_TRUE
2334 ,p_msg_index => 1
2335 ,p_msg_count => l_msg_count
2336 ,p_msg_data => l_msg_data
2337 ,p_data => l_data
2338 ,p_msg_index_out => l_msg_index_out);
2339 x_msg_data := l_data;
2340 x_msg_count := l_msg_count;
2341 ELSE
2342 x_msg_count := l_msg_count;
2343 END IF;
2344 ROLLBACK;
2345
2346 x_return_status := FND_API.G_RET_STS_ERROR;
2347
2348 IF P_PA_DEBUG_MODE = 'Y' THEN
2349 PA_DEBUG.Reset_curr_function;
2350 END IF;
2351
2352 RAISE;
2353
2354 WHEN OTHERS THEN
2355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2356 x_msg_data := SUBSTR(SQLERRM,1,240);
2357 FND_MSG_PUB.add_exc_msg
2358 ( p_pkg_name => 'PA_FP_GEN_BILLING_AMOUNTS'
2359 ,p_procedure_name => 'UPD_TMP4_TXN_RA_ID_AND_ML');
2360
2361 IF P_PA_DEBUG_MODE = 'Y' THEN
2362 PA_DEBUG.Reset_curr_function;
2363 END IF;
2364
2365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2366
2367 END UPD_TMP4_TXN_RA_ID_AND_ML;
2368
2369
2370 END PA_FP_GEN_BILLING_AMOUNTS;