[Home] [Help]
PACKAGE BODY: APPS.PA_FP_COPY_ACTUALS_PUB
Source
1 PACKAGE body PA_FP_COPY_ACTUALS_PUB as
2 /* $Header: PAFPCAPB.pls 120.6.12010000.2 2008/08/20 11:42:30 sugupta ship $ */
3 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4
5 /**This procedure is called to copy actuals to budget version lines**/
6 PROCEDURE COPY_ACTUALS
7 (P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
8 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
9 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
10 P_END_DATE IN DATE,
11 P_INIT_MSG_FLAG IN VARCHAR2 default 'Y',
12 P_COMMIT_FLAG IN VARCHAR2 default 'N',
13 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
14 X_MSG_COUNT OUT NOCOPY NUMBER,
15 X_MSG_DATA OUT NOCOPY VARCHAR2)
16 IS
17 l_module_name VARCHAR2(100) := 'pa.plsql.pa_fp_copy_actuals_pub.copy_actuals';
18 l_project_id_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
19 l_resource_list_id_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
20 l_struct_ver_id_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
21 l_calendar_type_tab SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type();
22 l_end_date_pji_tab SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
23 l_calendar_type VARCHAR2(15);
24 l_record_type VARCHAR2(15);
25
26 l_count NUMBER;
27 l_msg_count NUMBER;
28 l_data VARCHAR2(1000);
29 l_msg_data VARCHAR2(1000);
30 l_msg_index_out NUMBER;
31
32 CURSOR distinct_ra_curr_cursor (c_multi_currency_flag VARCHAR2,
33 c_proj_currency_code VARCHAR2,
34 c_projfunc_currency_code VARCHAR2) IS
35 SELECT distinct pji_tmp.source_id,
36 DECODE(c_multi_currency_flag,
37 'Y', pji_tmp.txn_currency_code,
38 'N', c_proj_currency_code,
39 'A', c_projfunc_currency_code)
40 FROM pji_fm_xbs_accum_tmp1 pji_tmp;
41
42 /* Bug No.3858184
43 Cursors(budget_line_cursor_pa, budget_line_cursor_gl, budget_line_cursor_np)
44 modified to filter the records based on the VERSION_TYPE.
45 For COST versions, the records in the PJI_FM_XBS_ACCUM_TMP1 table
46 will be processed only if raw cost or the burdened cost is not equal to zero.
47 For Revenue versions, the records in the PJI_FM_XBS_ACCUM_TMP1 table
48 will be processed only if the revenue amt is not equal to zero. */
49
50 CURSOR budget_line_cursor_pa(c_multi_currency_flag VARCHAR2,
51 c_res_asg_id NUMBER,
52 c_txn_currency_code VARCHAR2,
53 c_org_id NUMBER,
54 c_version_type VARCHAR2) IS
55 SELECT pji_tmp.period_name,
56 pd.start_date,
57 pd.end_date,
58 sum(pji_tmp.quantity),
59 sum(DECODE(c_multi_currency_flag,
60 'Y', pji_tmp.txn_raw_cost,
61 'N', pji_tmp.prj_raw_cost,
62 'A', pji_tmp.pou_raw_cost)),
63 sum(DECODE(c_multi_currency_flag,
64 'Y', pji_tmp.txn_brdn_cost,
65 'N', pji_tmp.prj_brdn_cost,
66 'A', pji_tmp.pou_brdn_cost)),
67 sum(DECODE(c_multi_currency_flag,
68 'Y', pji_tmp.txn_revenue,
69 'N', pji_tmp.prj_revenue,
70 'A', pji_tmp.pou_revenue)),
71 sum(pji_tmp.prj_raw_cost),
72 sum(pji_tmp.prj_brdn_cost),
73 sum(pji_tmp.prj_revenue),
74 sum(DECODE(c_multi_currency_flag,
75 'Y', pji_tmp.pou_raw_cost,
76 'N', pji_tmp.prj_raw_cost,
77 'A', pji_tmp.pou_raw_cost)),
78 sum(DECODE(c_multi_currency_flag,
79 'Y', pji_tmp.pou_brdn_cost,
80 'N', pji_tmp.prj_brdn_cost,
81 'A', pji_tmp.pou_brdn_cost)),
82 sum(DECODE(c_multi_currency_flag,
83 'Y', pji_tmp.pou_revenue,
84 'N', pji_tmp.prj_revenue,
85 'A', pji_tmp.pou_revenue))
86 FROM pji_fm_xbs_accum_tmp1 pji_tmp,pa_periods_all pd
87 WHERE c_version_type = 'ALL'
88 AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
89 (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
90 (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
91 (NVL(pji_tmp.quantity,0) <> 0)
92 )
93 AND pd.org_id = c_org_id
94 AND pd.period_name = pji_tmp.period_name
95 AND pji_tmp.source_id = c_res_asg_id
96 AND DECODE(c_multi_currency_flag,'Y',
97 pji_tmp.txn_currency_code,c_txn_currency_code)
98 = c_txn_currency_code
99 GROUP BY pji_tmp.period_name,
100 pd.start_date,
101 pd.end_date
102 UNION ALL
103 SELECT pji_tmp.period_name,
104 pd.start_date,
105 pd.end_date,
106 sum(pji_tmp.quantity),
107 sum(DECODE(c_multi_currency_flag,
108 'Y', pji_tmp.txn_raw_cost,
109 'N', pji_tmp.prj_raw_cost,
110 'A', pji_tmp.pou_raw_cost)),
111 sum(DECODE(c_multi_currency_flag,
112 'Y', pji_tmp.txn_brdn_cost,
113 'N', pji_tmp.prj_brdn_cost,
114 'A', pji_tmp.pou_brdn_cost)),
115 sum(DECODE(c_multi_currency_flag,
116 'Y', pji_tmp.txn_revenue,
117 'N', pji_tmp.prj_revenue,
118 'A', pji_tmp.pou_revenue)),
119 sum(pji_tmp.prj_raw_cost),
120 sum(pji_tmp.prj_brdn_cost),
121 sum(pji_tmp.prj_revenue),
122 sum(DECODE(c_multi_currency_flag,
123 'Y', pji_tmp.pou_raw_cost,
124 'N', pji_tmp.prj_raw_cost,
125 'A', pji_tmp.pou_raw_cost)),
126 sum(DECODE(c_multi_currency_flag,
127 'Y', pji_tmp.pou_brdn_cost,
128 'N', pji_tmp.prj_brdn_cost,
129 'A', pji_tmp.pou_brdn_cost)),
130 sum(DECODE(c_multi_currency_flag,
131 'Y', pji_tmp.pou_revenue,
132 'N', pji_tmp.prj_revenue,
133 'A', pji_tmp.pou_revenue))
134 FROM pji_fm_xbs_accum_tmp1 pji_tmp,pa_periods_all pd
135 WHERE c_version_type = 'COST'
136 AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
137 (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
138 (NVL(pji_tmp.quantity,0) <> 0)
139 )
140 AND pd.org_id = c_org_id
141 AND pd.period_name = pji_tmp.period_name
142 AND pji_tmp.source_id = c_res_asg_id
143 AND DECODE(c_multi_currency_flag,'Y',
144 pji_tmp.txn_currency_code,c_txn_currency_code)
145 = c_txn_currency_code
146 GROUP BY pji_tmp.period_name,
147 pd.start_date,
148 pd.end_date
149 UNION ALL
150 SELECT pji_tmp.period_name,
151 pd.start_date,
152 pd.end_date,
153 sum(pji_tmp.quantity),
154 sum(DECODE(c_multi_currency_flag,
155 'Y', pji_tmp.txn_raw_cost,
156 'N', pji_tmp.prj_raw_cost,
157 'A', pji_tmp.pou_raw_cost)),
158 sum(DECODE(c_multi_currency_flag,
159 'Y', pji_tmp.txn_brdn_cost,
160 'N', pji_tmp.prj_brdn_cost,
161 'A', pji_tmp.pou_brdn_cost)),
162 sum(DECODE(c_multi_currency_flag,
163 'Y', pji_tmp.txn_revenue,
164 'N', pji_tmp.prj_revenue,
165 'A', pji_tmp.pou_revenue)),
166 sum(pji_tmp.prj_raw_cost),
167 sum(pji_tmp.prj_brdn_cost),
168 sum(pji_tmp.prj_revenue),
169 sum(DECODE(c_multi_currency_flag,
170 'Y', pji_tmp.pou_raw_cost,
171 'N', pji_tmp.prj_raw_cost,
172 'A', pji_tmp.pou_raw_cost)),
173 sum(DECODE(c_multi_currency_flag,
174 'Y', pji_tmp.pou_brdn_cost,
175 'N', pji_tmp.prj_brdn_cost,
176 'A', pji_tmp.pou_brdn_cost)),
177 sum(DECODE(c_multi_currency_flag,
178 'Y', pji_tmp.pou_revenue,
179 'N', pji_tmp.prj_revenue,
180 'A', pji_tmp.pou_revenue))
181 FROM pji_fm_xbs_accum_tmp1 pji_tmp,pa_periods_all pd
182 WHERE c_version_type = 'REVENUE'
183 AND (
184 (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
185 (NVL(pji_tmp.quantity,0) <> 0)
186 )
187 AND pd.org_id = c_org_id
188 AND pd.period_name = pji_tmp.period_name
189 AND pji_tmp.source_id = c_res_asg_id
190 AND DECODE(c_multi_currency_flag,'Y',
191 pji_tmp.txn_currency_code,c_txn_currency_code)
192 = c_txn_currency_code
193 GROUP BY pji_tmp.period_name,
194 pd.start_date,
195 pd.end_date;
196
197 CURSOR budget_line_cursor_gl(c_multi_currency_flag VARCHAR2,
198 c_res_asg_id NUMBER,
199 c_txn_currency_code VARCHAR2,
200 c_set_of_books_id NUMBER,
201 c_version_type VARCHAR2) IS
202 SELECT pji_tmp.period_name,
203 gd.start_date,
204 gd.end_date,
205 sum(pji_tmp.quantity),
206 sum(DECODE(c_multi_currency_flag,
207 'Y', pji_tmp.txn_raw_cost,
208 'N', pji_tmp.prj_raw_cost,
209 'A', pji_tmp.pou_raw_cost)),
210 sum(DECODE(c_multi_currency_flag,
211 'Y', pji_tmp.txn_brdn_cost,
212 'N', pji_tmp.prj_brdn_cost,
213 'A', pji_tmp.pou_brdn_cost)),
214 sum(DECODE(c_multi_currency_flag,
215 'Y', pji_tmp.txn_revenue,
216 'N', pji_tmp.prj_revenue,
217 'A', pji_tmp.pou_revenue)),
218 sum(pji_tmp.prj_raw_cost),
219 sum(pji_tmp.prj_brdn_cost),
220 sum(pji_tmp.prj_revenue),
221 sum(DECODE(c_multi_currency_flag,
222 'Y', pji_tmp.pou_raw_cost,
223 'N', pji_tmp.prj_raw_cost,
224 'A', pji_tmp.pou_raw_cost)),
225 sum(DECODE(c_multi_currency_flag,
226 'Y', pji_tmp.pou_brdn_cost,
227 'N', pji_tmp.prj_brdn_cost,
228 'A', pji_tmp.pou_brdn_cost)),
229 sum(DECODE(c_multi_currency_flag,
230 'Y', pji_tmp.pou_revenue,
231 'N', pji_tmp.prj_revenue,
232 'A', pji_tmp.pou_revenue))
233 FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
234 WHERE c_version_type = 'ALL'
235 AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
236 (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
237 (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
238 (NVL(pji_tmp.quantity,0) <> 0)
239 )
240 AND gd.SET_OF_BOOKS_ID = c_set_of_books_id
241 AND gd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
242 AND gd.ADJUSTMENT_PERIOD_FLAG = 'N'
243 AND gd.period_name = pji_tmp.period_name
244 AND pji_tmp.source_id = c_res_asg_id
245 AND DECODE(c_multi_currency_flag,'Y',
246 pji_tmp.txn_currency_code,c_txn_currency_code)
247 = c_txn_currency_code
248 GROUP BY pji_tmp.period_name,
249 gd.start_date,
250 gd.end_date
251 UNION ALL
252 SELECT pji_tmp.period_name,
253 gd.start_date,
254 gd.end_date,
255 sum(pji_tmp.quantity),
256 sum(DECODE(c_multi_currency_flag,
257 'Y', pji_tmp.txn_raw_cost,
258 'N', pji_tmp.prj_raw_cost,
259 'A', pji_tmp.pou_raw_cost)),
260 sum(DECODE(c_multi_currency_flag,
261 'Y', pji_tmp.txn_brdn_cost,
262 'N', pji_tmp.prj_brdn_cost,
263 'A', pji_tmp.pou_brdn_cost)),
264 sum(DECODE(c_multi_currency_flag,
265 'Y', pji_tmp.txn_revenue,
266 'N', pji_tmp.prj_revenue,
267 'A', pji_tmp.pou_revenue)),
268 sum(pji_tmp.prj_raw_cost),
269 sum(pji_tmp.prj_brdn_cost),
270 sum(pji_tmp.prj_revenue),
271 sum(DECODE(c_multi_currency_flag,
272 'Y', pji_tmp.pou_raw_cost,
273 'N', pji_tmp.prj_raw_cost,
274 'A', pji_tmp.pou_raw_cost)),
275 sum(DECODE(c_multi_currency_flag,
276 'Y', pji_tmp.pou_brdn_cost,
277 'N', pji_tmp.prj_brdn_cost,
278 'A', pji_tmp.pou_brdn_cost)),
279 sum(DECODE(c_multi_currency_flag,
280 'Y', pji_tmp.pou_revenue,
281 'N', pji_tmp.prj_revenue,
282 'A', pji_tmp.pou_revenue))
283 FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
284 WHERE c_version_type = 'COST'
285 AND (
286 (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
287 (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
288 (NVL(pji_tmp.quantity,0) <> 0)
289 )
290 AND gd.SET_OF_BOOKS_ID = c_set_of_books_id
291 AND gd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
292 AND gd.ADJUSTMENT_PERIOD_FLAG = 'N'
293 AND gd.period_name = pji_tmp.period_name
294 AND pji_tmp.source_id = c_res_asg_id
295 AND DECODE(c_multi_currency_flag,'Y',
296 pji_tmp.txn_currency_code,c_txn_currency_code)
297 = c_txn_currency_code
298 GROUP BY pji_tmp.period_name,
299 gd.start_date,
300 gd.end_date
301 UNION ALL
302 SELECT pji_tmp.period_name,
303 gd.start_date,
304 gd.end_date,
305 sum(pji_tmp.quantity),
306 sum(DECODE(c_multi_currency_flag,
307 'Y', pji_tmp.txn_raw_cost,
308 'N', pji_tmp.prj_raw_cost,
309 'A', pji_tmp.pou_raw_cost)),
310 sum(DECODE(c_multi_currency_flag,
311 'Y', pji_tmp.txn_brdn_cost,
312 'N', pji_tmp.prj_brdn_cost,
313 'A', pji_tmp.pou_brdn_cost)),
314 sum(DECODE(c_multi_currency_flag,
315 'Y', pji_tmp.txn_revenue,
316 'N', pji_tmp.prj_revenue,
317 'A', pji_tmp.pou_revenue)),
318 sum(pji_tmp.prj_raw_cost),
319 sum(pji_tmp.prj_brdn_cost),
320 sum(pji_tmp.prj_revenue),
321 sum(DECODE(c_multi_currency_flag,
322 'Y', pji_tmp.pou_raw_cost,
323 'N', pji_tmp.prj_raw_cost,
324 'A', pji_tmp.pou_raw_cost)),
325 sum(DECODE(c_multi_currency_flag,
326 'Y', pji_tmp.pou_brdn_cost,
327 'N', pji_tmp.prj_brdn_cost,
328 'A', pji_tmp.pou_brdn_cost)),
329 sum(DECODE(c_multi_currency_flag,
330 'Y', pji_tmp.pou_revenue,
331 'N', pji_tmp.prj_revenue,
332 'A', pji_tmp.pou_revenue))
333 FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
334 WHERE c_version_type = 'REVENUE'
335 AND (
336 (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
337 (NVL(pji_tmp.quantity,0) <> 0)
338 )
339 AND gd.SET_OF_BOOKS_ID = c_set_of_books_id
340 AND gd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
341 AND gd.ADJUSTMENT_PERIOD_FLAG = 'N'
342 AND gd.period_name = pji_tmp.period_name
343 AND pji_tmp.source_id = c_res_asg_id
344 AND DECODE(c_multi_currency_flag,'Y',
345 pji_tmp.txn_currency_code,c_txn_currency_code)
346 = c_txn_currency_code
347 GROUP BY pji_tmp.period_name,
348 gd.start_date,
349 gd.end_date;
350
351 CURSOR budget_line_cursor_np(c_multi_currency_flag VARCHAR2,
352 c_res_asg_id NUMBER,
353 c_txn_currency_code VARCHAR2,
354 c_proj_id NUMBER,
355 c_version_type VARCHAR2) IS
356 SELECT pji_tmp.period_name,
357 nvl(ra.planning_start_date, TRUNC(Sysdate)),
358 nvl(ra.planning_end_date, TRUNC(Sysdate)),
359 sum(pji_tmp.quantity),
360 sum(DECODE(c_multi_currency_flag,
361 'Y', pji_tmp.txn_raw_cost,
362 'N', pji_tmp.prj_raw_cost,
363 'A', pji_tmp.pou_raw_cost)),
364 sum(DECODE(c_multi_currency_flag,
365 'Y', pji_tmp.txn_brdn_cost,
366 'N', pji_tmp.prj_brdn_cost,
367 'A', pji_tmp.pou_brdn_cost)),
368 sum(DECODE(c_multi_currency_flag,
369 'Y', pji_tmp.txn_revenue,
370 'N', pji_tmp.prj_revenue,
371 'A', pji_tmp.pou_revenue)),
372 sum(pji_tmp.prj_raw_cost),
373 sum(pji_tmp.prj_brdn_cost),
374 sum(pji_tmp.prj_revenue),
375 sum(DECODE(c_multi_currency_flag,
376 'Y', pji_tmp.pou_raw_cost,
377 'N', pji_tmp.prj_raw_cost,
378 'A', pji_tmp.pou_raw_cost)),
379 sum(DECODE(c_multi_currency_flag,
380 'Y', pji_tmp.pou_brdn_cost,
381 'N', pji_tmp.prj_brdn_cost,
382 'A', pji_tmp.pou_brdn_cost)),
383 sum(DECODE(c_multi_currency_flag,
384 'Y', pji_tmp.pou_revenue,
385 'N', pji_tmp.prj_revenue,
386 'A', pji_tmp.pou_revenue))
387 FROM pji_fm_xbs_accum_tmp1 pji_tmp,
388 pa_resource_assignments ra
389 WHERE c_version_type = 'ALL'
390 AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
391 (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
392 (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
393 (NVL(pji_tmp.quantity,0) <> 0)
394 )
395 AND pji_tmp.source_id = c_res_asg_id
396 AND DECODE(c_multi_currency_flag,'Y',
397 pji_tmp.txn_currency_code,c_txn_currency_code)
398 = c_txn_currency_code
399 AND ra.resource_assignment_id = c_res_asg_id
400 GROUP BY pji_tmp.period_name,
401 nvl(ra.planning_start_date, TRUNC(Sysdate)),
402 nvl(ra.planning_end_date, TRUNC(Sysdate))
403 UNION ALL
404 SELECT pji_tmp.period_name,
405 nvl(ra.planning_start_date, TRUNC(Sysdate)),
406 nvl(ra.planning_end_date, TRUNC(Sysdate)),
407 sum(pji_tmp.quantity),
408 sum(DECODE(c_multi_currency_flag,
409 'Y', pji_tmp.txn_raw_cost,
410 'N', pji_tmp.prj_raw_cost,
411 'A', pji_tmp.pou_raw_cost)),
412 sum(DECODE(c_multi_currency_flag,
413 'Y', pji_tmp.txn_brdn_cost,
414 'N', pji_tmp.prj_brdn_cost,
415 'A', pji_tmp.pou_brdn_cost)),
416 sum(DECODE(c_multi_currency_flag,
417 'Y', pji_tmp.txn_revenue,
418 'N', pji_tmp.prj_revenue,
419 'A', pji_tmp.pou_revenue)),
420 sum(pji_tmp.prj_raw_cost),
421 sum(pji_tmp.prj_brdn_cost),
422 sum(pji_tmp.prj_revenue),
423 sum(DECODE(c_multi_currency_flag,
424 'Y', pji_tmp.pou_raw_cost,
425 'N', pji_tmp.prj_raw_cost,
426 'A', pji_tmp.pou_raw_cost)),
427 sum(DECODE(c_multi_currency_flag,
428 'Y', pji_tmp.pou_brdn_cost,
429 'N', pji_tmp.prj_brdn_cost,
430 'A', pji_tmp.pou_brdn_cost)),
431 sum(DECODE(c_multi_currency_flag,
432 'Y', pji_tmp.pou_revenue,
433 'N', pji_tmp.prj_revenue,
434 'A', pji_tmp.pou_revenue))
435 FROM pji_fm_xbs_accum_tmp1 pji_tmp,
436 pa_resource_assignments ra
437 WHERE c_version_type = 'COST'
438 AND (
439 (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
440 (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
441 (NVL(pji_tmp.quantity,0) <> 0)
442 )
443 AND pji_tmp.source_id = c_res_asg_id
444 AND DECODE(c_multi_currency_flag,'Y',
445 pji_tmp.txn_currency_code,c_txn_currency_code)
446 = c_txn_currency_code
447 AND ra.resource_assignment_id = c_res_asg_id
448 GROUP BY pji_tmp.period_name,
449 nvl(ra.planning_start_date, TRUNC(Sysdate)),
450 nvl(ra.planning_end_date, TRUNC(Sysdate))
451 UNION ALL
452 SELECT pji_tmp.period_name,
453 nvl(ra.planning_start_date, TRUNC(Sysdate)),
454 nvl(ra.planning_end_date, TRUNC(Sysdate)),
455 sum(pji_tmp.quantity),
456 sum(DECODE(c_multi_currency_flag,
457 'Y', pji_tmp.txn_raw_cost,
458 'N', pji_tmp.prj_raw_cost,
459 'A', pji_tmp.pou_raw_cost)),
460 sum(DECODE(c_multi_currency_flag,
461 'Y', pji_tmp.txn_brdn_cost,
462 'N', pji_tmp.prj_brdn_cost,
463 'A', pji_tmp.pou_brdn_cost)),
464 sum(DECODE(c_multi_currency_flag,
465 'Y', pji_tmp.txn_revenue,
466 'N', pji_tmp.prj_revenue,
467 'A', pji_tmp.pou_revenue)),
468 sum(pji_tmp.prj_raw_cost),
469 sum(pji_tmp.prj_brdn_cost),
470 sum(pji_tmp.prj_revenue),
471 sum(DECODE(c_multi_currency_flag,
472 'Y', pji_tmp.pou_raw_cost,
473 'N', pji_tmp.prj_raw_cost,
474 'A', pji_tmp.pou_raw_cost)),
475 sum(DECODE(c_multi_currency_flag,
476 'Y', pji_tmp.pou_brdn_cost,
477 'N', pji_tmp.prj_brdn_cost,
478 'A', pji_tmp.pou_brdn_cost)),
479 sum(DECODE(c_multi_currency_flag,
480 'Y', pji_tmp.pou_revenue,
481 'N', pji_tmp.prj_revenue,
482 'A', pji_tmp.pou_revenue))
483 FROM pji_fm_xbs_accum_tmp1 pji_tmp,
484 pa_resource_assignments ra
485 WHERE c_version_type = 'REVENUE'
486 AND (
487 (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
488 (NVL(pji_tmp.quantity,0) <> 0)
489 )
490 AND pji_tmp.source_id = c_res_asg_id
491 AND DECODE(c_multi_currency_flag,'Y',
492 pji_tmp.txn_currency_code,c_txn_currency_code)
493 = c_txn_currency_code
494 AND ra.resource_assignment_id = c_res_asg_id
495 GROUP BY pji_tmp.period_name,
496 nvl(ra.planning_start_date, TRUNC(Sysdate)),
497 nvl(ra.planning_end_date, TRUNC(Sysdate));
498
499 l_ra NUMBER;
500 l_org_id NUMBER;
501 l_set_of_books_id NUMBER;
502 l_rlm_id pa_resource_list_members.resource_list_member_id%TYPE;
503 l_res_asg_id_tab pa_plsql_datatypes.IdTabTyp;
504 l_txn_currency_code_tab pa_plsql_datatypes.Char30TabTyp;
505 l_period_name_tab pa_plsql_datatypes.Char30TabTyp;
506 l_quantity_tab pa_plsql_datatypes.NumTabTyp;
507 l_txn_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
508 l_txn_brdn_cost_tab pa_plsql_datatypes.NumTabTyp;
509 l_txn_revenue_tab pa_plsql_datatypes.NumTabTyp;
510 l_proj_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
511 l_proj_brdn_cost_tab pa_plsql_datatypes.NumTabTyp;
512 l_proj_revenue_tab pa_plsql_datatypes.NumTabTyp;
513 l_pou_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
514 l_pou_brdn_cost_tab pa_plsql_datatypes.NumTabTyp;
515 l_pou_revenue_tab pa_plsql_datatypes.NumTabTyp;
516 l_start_date_tab pa_plsql_datatypes.DateTabTyp;
517 l_end_date_tab pa_plsql_datatypes.DateTabTyp;
518 l_start_date Date;
519 l_end_date Date;
520
521 l_amt_dtls_tbl pa_fp_maintain_actual_pub.l_amt_dtls_tbl_typ;
522 l_wp_version_flag VARCHAR2(1);
523 l_count_no_rlm NUMBER;
524 l_rate_based_flag VARCHAR2(1);
525 l_uncategorized_flag VARCHAR2(1);
526 l_rev_gen_method VARCHAR2(3);
527 l_res_asg_id_tmp_tab pa_plsql_datatypes.IdTabTyp;
528
529 l_plan_class_code PA_FIN_PLAN_TYPES_B.PLAN_CLASS_CODE%TYPE;
530 l_txn_currency_flag VARCHAR2(1) := 'Y';
531 l_fin_plan_type_id PA_PROJ_FP_OPTIONS.fin_plan_type_id%TYPE;
532
533 l_etc_start_date DATE;
534 BEGIN
535 IF P_PA_DEBUG_MODE = 'Y' THEN
536 pa_debug.set_curr_function( p_function => 'COPY_ACTUALS',
537 p_debug_mode => p_pa_debug_mode );
538 END IF;
539
540 x_return_status := FND_API.G_RET_STS_SUCCESS;
541 x_msg_count := 0;
542
543 IF p_init_msg_flag = 'Y' THEN
544 FND_MSG_PUB.initialize;
545 x_msg_count := 0;
546 END IF;
547
548 IF P_PROJECT_ID is null or p_budget_version_id is null THEN
549 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
550 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
551 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
552 END IF;
553
554 l_rev_gen_method := nvl(P_FP_COLS_REC.X_REVENUE_DERIVATION_METHOD,PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(p_project_id)); --Bug 5462471
555 --l_rev_gen_method := PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(p_project_id);
556
557 /* Set the currency flag as follows:
558 l_txn_currency_flag is 'Y' means we use txn_currency_code
559 l_txn_currency_flag is 'N' means we use proj_currency_code
560 l_txn_currency_flag is 'A' means we use projfunc_currency_code
561 */
562
563 -- Bug 7302700 - Moved the condition on x_plan_in_multi_curr_flag before checking if the
564 -- revenue forecast is generated from a forecast plan type with cost accrual method.
565 IF p_fp_cols_rec.x_plan_in_multi_curr_flag = 'N' THEN
566 l_txn_currency_flag := 'N';
567 END IF;
568
569 IF l_rev_gen_method = 'C' AND
570 p_fp_cols_rec.x_version_type = 'REVENUE' AND
571 p_fp_cols_rec.X_GEN_SRC_PLAN_TYPE_ID IS NOT NULL THEN
572
573 SELECT plan_class_code
574 INTO l_plan_class_code
575 FROM pa_fin_plan_types_b
576 WHERE fin_plan_type_id = p_fp_cols_rec.X_GEN_SRC_PLAN_TYPE_ID;
577
578 IF l_plan_class_code = 'FORECAST' THEN
579 l_txn_currency_flag := 'A';
580 END IF;
581
582 END IF;
583
584 l_calendar_type := p_fp_cols_rec.X_TIME_PHASED_CODE;
585
586 l_project_id_tab.extend;
587 l_resource_list_id_tab.extend;
588 l_struct_ver_id_tab.extend;
589 l_calendar_type_tab.extend;
590 l_end_date_pji_tab.extend;
591
592 l_project_id_tab(1) := p_project_id;
593 l_resource_list_id_tab(1) := p_fp_cols_rec.X_RESOURCE_LIST_ID;
594 l_calendar_type_tab(1) := l_calendar_type;
595 l_end_date_pji_tab(1) := p_end_date;
596
597 --Structure version id should be the structure version id of the current published version
598 --for B/F.
599 SELECT wp_version_flag
600 INTO l_wp_version_flag
601 FROM pa_budget_Versions
602 WHERE budget_version_id=P_BUDGET_VERSION_ID;
603
604 IF l_wp_version_flag = 'Y' THEN
605 l_struct_ver_id_tab(1) := p_fp_cols_rec.X_PROJECT_STRUCTURE_VERSION_ID;
606 ELSE
607 l_struct_ver_id_tab(1) := PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(p_project_id => p_project_id );
608 END IF;
609
610 /**l_record_type: XXXX
611 *1st X: 'Y',data will be returned in periods;
612 * 'N',ITD amounts will be returned;
613 *2nd X: 'Y',data will be returned by planning resources at
614 * entered level(periodic/total);
615 *3rd X: 'Y',data is returned by tasks;
616 * 'N',data is returned by project level;
617 *4th X: 'N',amt will be gotten at entered level, no rollup is done.**/
618 IF (l_calendar_type = 'G' OR l_calendar_type = 'P') THEN
619 l_record_type := 'Y';
620 ELSE
621 l_record_type := 'N';
622 END IF;
623 l_record_type := l_record_type||'Y';
624 IF p_fp_cols_rec.X_FIN_PLAN_LEVEL_CODE IN ('L', 'T') THEN
625 l_record_type := l_record_type||'Y';
626 ELSE
627 l_record_type := l_record_type||'N';
628 END IF;
629 l_record_type := l_record_type||'N';
630 IF P_PA_DEBUG_MODE = 'Y' THEN
631 pa_fp_gen_amount_utils.fp_debug
632 (p_msg => 'Before calling pji_fm_xbs_accum_tmp1',
633 p_module_name => l_module_name,
634 p_log_level => 5);
635 END IF;
636 --dbms_output.put_line('Before calling pji api');
637 --Calling PJI API to get table pji_fm_xbs_accum_tmp1 populated
638 --hr_utility.trace_on(null,'mftest');
639 --hr_utility.trace('before entering get_sum');
640 --hr_utility.trace('l_project_id_tab:'||l_project_id_tab(1));
641 --hr_utility.trace('l_resource_list_id_tab:'||l_resource_list_id_tab(1));
642 --hr_utility.trace('l_struct_ver_id_tab:'||l_struct_ver_id_tab(1));
643 --hr_utility.trace('p_end_date:'||p_end_date);
644 --hr_utility.trace('l_calendar_type:'||l_calendar_type);
645 --hr_utility.trace('l_record_type:'||l_record_type);
646 PJI_FM_XBS_ACCUM_UTILS.get_summarized_data(
647 p_project_ids => l_project_id_tab,
648 p_resource_list_ids => l_resource_list_id_tab,
649 p_struct_ver_ids => l_struct_ver_id_tab,
650 --p_start_date => NULL,
651 p_end_date => l_end_date_pji_tab,
652 --p_start_period_name => NULL,
653 --p_end_period_name => NULL,
654 p_calendar_type => l_calendar_type_tab,
655 p_record_type => l_record_type,
656 p_currency_type => 6,
657 x_return_status => x_return_status,
658 x_msg_code => x_msg_data);
659 --dbms_output.put_line('After calling pji api: '||x_return_status);
660 select count(*) into l_count from pji_fm_xbs_accum_tmp1;
661
662
663 --hr_utility.trace('after entering get_sum:'||x_return_status);
664 --delete from get_sum_test;
665 --insert into get_sum_test (select * from pji_fm_xbs_accum_tmp1);
666 IF P_PA_DEBUG_MODE = 'Y' THEN
667 pa_fp_gen_amount_utils.fp_debug
668 (p_msg => 'After calling pji_fm_xbs_accum_tmp1,return status is: '||x_return_status,
669 p_module_name => l_module_name,
670 p_log_level => 5);
671 END IF;
672 --dbms_output.put_line('After calling pji api: '||x_return_status);
673 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
674 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
675 END IF;
676
677 IF l_count = 0 THEN
678 IF P_PA_DEBUG_MODE = 'Y' THEN
679 pa_fp_gen_amount_utils.fp_debug
680 (p_msg => 'no actual data as of '||
681 to_char(p_end_date,'dd-mon-rrrr'),
682 p_module_name => l_module_name,
683 p_log_level => 5);
684 PA_DEBUG.RESET_CURR_FUNCTION;
685 END IF;
686 RETURN;
687 END IF;
688
689 select count(*) into l_count_no_rlm from pji_fm_xbs_accum_tmp1 WHERE
690 res_list_member_id IS NULL;
691
692 IF l_count_no_rlm > 0 THEN
693 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
694 p_msg_name => 'PA_FP_NO_RLM_ID_FOR_ACTUAL');
695 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
696 END IF;
697
698 /* Update rlm_id for all rows in pji_fm_xbs_accum_tmp1 if the resource list
699 * (p_fp_cols_rec.X_RESOURCE_LIST_ID) is None - Uncategorized.
700 * This logic is not handled by the PJI generic resource mapping API. */
701
702 SELECT NVL(uncategorized_flag,'N')
703 INTO l_uncategorized_flag
704 FROM pa_resource_lists_all_bg
705 WHERE resource_list_id = p_fp_cols_rec.X_RESOURCE_LIST_ID;
706
707 IF l_uncategorized_flag = 'Y' THEN
708 l_rlm_id := PA_FP_GEN_AMOUNT_UTILS.GET_RLM_ID (
709 p_project_id => p_project_id,
710 p_resource_list_id => p_fp_cols_rec.X_RESOURCE_LIST_ID,
711 p_resource_class_code => 'FINANCIAL_ELEMENTS' );
712 UPDATE pji_fm_xbs_accum_tmp1
713 SET res_list_member_id = l_rlm_id;
714 END IF;
715
716 /* updating the project element id ( task id ) to NULL
717 when the value is <= 0 for addressing the P1 bug 3841480.
718 Please note that we cannot resolve the issue by populating the NULL value
719 into the tmp table PA_FP_PLANNING_RES_TMP1. Because, the task id value is referred
720 in the pji_fm_xbs_accum_tmp1 table later in the code. */
721
722 update pji_fm_xbs_accum_tmp1 set project_element_id = null
723 where NVL(project_element_id,0) <= 0;
724
725 /**Populating PA_FP_PLANNING_RES_TMP1, call COPY_ACUTALS_PUB.CREATE_RES_ASG to create
726 *missing resource assignment in pa_resource_assignment table. After that, resource_
727 *assignment_id will be populated pa_fp_planning_res_tmp1.
728 **/
729 DELETE FROM PA_FP_PLANNING_RES_TMP1;
730 INSERT INTO PA_FP_PLANNING_RES_TMP1 (
731 TASK_ID,
732 RESOURCE_LIST_MEMBER_ID,
733 RESOURCE_ASSIGNMENT_ID )
734 ( SELECT DISTINCT PROJECT_ELEMENT_ID,
735 RES_LIST_MEMBER_ID,
736 NULL
737 FROM PJI_FM_XBS_ACCUM_TMP1);
738 -- select count(*) into l_count from pa_resource_assignments where
739 -- budget_version_id = p_budget_version_id;
740 --dbms_output.put_line('before calling cre res asg api: res_assign has: '||l_count);
741 IF P_PA_DEBUG_MODE = 'Y' THEN
742 pa_fp_gen_amount_utils.fp_debug
743 (p_msg => 'Before calling pa_fp_copy_actuals_pub.create_res_asg',
744 p_module_name => l_module_name,
745 p_log_level => 5);
746 END IF;
747 PA_FP_COPY_ACTUALS_PUB.CREATE_RES_ASG (
748 P_PROJECT_ID => P_PROJECT_ID,
749 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
750 P_FP_COLS_REC => P_FP_COLS_REC,
751 X_RETURN_STATUS => x_return_status,
752 X_MSG_COUNT => x_msg_count,
753 X_MSG_DATA => x_msg_data );
754 --dbms_output.put_line('Status after calling cre res asg api: '||x_return_status);
755 IF P_PA_DEBUG_MODE = 'Y' THEN
756 pa_fp_gen_amount_utils.fp_debug
757 (p_msg => 'After calling create_res_asg,return status is: '||x_return_status,
758 p_module_name => l_module_name,
759 p_log_level => 5);
760 END IF;
761 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
762 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
763 END IF;
764
765 /**Calling update_res_asg to populate the newly created resource_assignment_id back to
766 *pa_fp_planning_res_tmp1. Then this value needs to populated back to pji_fm_xbs_accum_tmp1
767 **/
768 IF P_PA_DEBUG_MODE = 'Y' THEN
769 pa_fp_gen_amount_utils.fp_debug
770 (p_msg => 'Before calling update_res_asg',
771 p_module_name => l_module_name,
772 p_log_level => 5);
773 END IF;
774 PA_FP_COPY_ACTUALS_PUB.UPDATE_RES_ASG (
775 P_PROJECT_ID => P_PROJECT_ID,
776 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
777 P_FP_COLS_REC => P_FP_COLS_REC,
778 X_RETURN_STATUS => x_return_status,
779 X_MSG_COUNT => x_msg_count,
780 X_MSG_DATA => x_msg_data);
781 --dbms_output.put_line('Status after calling upd res asg api: '||x_return_status);
782 IF P_PA_DEBUG_MODE = 'Y' THEN
783 pa_fp_gen_amount_utils.fp_debug
784 (p_msg => 'After calling update_res_asg,return status is: '||x_return_status,
785 p_module_name => l_module_name,
786 p_log_level => 5);
787 END IF;
788 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
789 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
790 END IF;
791
792 UPDATE PJI_FM_XBS_ACCUM_TMP1 tmp1
793 SET source_id =
794 (SELECT /*+ INDEX(ra,PA_FP_PLANNING_RES_TMP1_N2)*/ resource_assignment_id
795 FROM PA_FP_PLANNING_RES_TMP1 ra
796 WHERE nvl(ra.task_id,0) = nvl(tmp1.project_element_id,0)
797 AND ra.resource_list_member_id = tmp1.res_list_member_id );
798 --dbms_output.put_line('No.of rows updated in pji_fm_xbs_accum_tmp1 table: '||sql%rowcount);
799 --dbms_output.put_line('Opening distinct_ra_curr_cursor');
800 OPEN distinct_ra_curr_cursor(l_txn_currency_flag,
801 P_FP_COLS_REC.X_PROJECT_CURRENCY_CODE,
802 P_FP_COLS_REC.X_PROJFUNC_CURRENCY_CODE);
803 FETCH distinct_ra_curr_cursor
804 BULK COLLECT
805 INTO l_res_asg_id_tab,
806 l_txn_currency_code_tab;
807 CLOSE distinct_ra_curr_cursor;
808 --dbms_output.put_line('Closing distinct_ra_curr_cursor');
809 IF l_res_asg_id_tab.count = 0 THEN
810 IF P_PA_DEBUG_MODE = 'Y' THEN
811 PA_DEBUG.RESET_CURR_FUNCTION;
812 END IF;
813 RETURN;
814 END IF;
815
816 IF l_rev_gen_method = 'C' AND
817 p_fp_cols_rec.x_version_type = 'REVENUE' THEN
818
819 SELECT DISTINCT source_id
820 BULK COLLECT
821 INTO l_res_asg_id_tmp_tab
822 FROM pji_fm_xbs_accum_tmp1;
823
824 -- Bug 4170419: Start
825 -- FORALL k IN 1..l_res_asg_id_tmp_tab.count
826 -- UPDATE pa_resource_assignments ra
827 -- SET ra.unit_of_measure = 'DOLLARS',
828 -- ra.rate_based_flag = 'N'
829 -- WHERE ra.resource_assignment_id = l_res_asg_id_tmp_tab(k);
830
831 If p_fp_cols_rec.x_time_phased_code IN ('P','G') then
832 l_etc_start_date := PA_FP_GEN_AMOUNT_UTILS.get_etc_start_date(p_fp_cols_rec.x_budget_version_id);
833
834 FORALL k IN 1..l_res_asg_id_tmp_tab.count
835 UPDATE pa_resource_assignments ra
836 SET ra.unit_of_measure = 'DOLLARS',
837 ra.rate_based_flag = 'N'
838 WHERE ra.resource_assignment_id = l_res_asg_id_tmp_tab(k)
839 AND ( ra.transaction_source_code is not null
840 OR
841 (ra.transaction_source_code is null and NOT exists
842 ( select 1
843 from pa_budget_lines pbl
844 where pbl.resource_assignment_id = ra.resource_assignment_id
845 and pbl.start_date >= l_etc_start_date
846 )
847 )
848 );
849
850 Else
851 FORALL k IN 1..l_res_asg_id_tmp_tab.count
852 UPDATE pa_resource_assignments ra
853 SET ra.unit_of_measure = 'DOLLARS',
854 ra.rate_based_flag = 'N'
855 WHERE ra.resource_assignment_id = l_res_asg_id_tmp_tab(k)
856 AND ( ra.transaction_source_code is not null
857 OR
858 (ra.transaction_source_code is null and NOT exists
859 ( select 1
860 from pa_budget_lines pbl
861 where pbl.resource_assignment_id = ra.resource_assignment_id
862 )
863 )
864 );
865 End If;
866
867 -- Bug 4170419: End
868
869 END IF;
870
871 l_org_id := P_FP_COLS_REC.x_org_id;
872 l_set_of_books_id := P_FP_COLS_REC.x_set_of_books_id;
873 --dbms_output.put_line('l_calendar_type: '||l_calendar_type);
874 FOR i IN 1..l_res_asg_id_tab.count LOOP
875 IF l_calendar_type = 'P' THEN
876 --dbms_output.put_line('Opening budget_line_cursor_pa');
877 OPEN budget_line_cursor_pa(
878 l_txn_currency_flag,
879 l_res_asg_id_tab(i),
880 l_txn_currency_code_tab(i),
881 l_org_id,
882 P_FP_COLS_REC.X_VERSION_TYPE);
883 FETCH budget_line_cursor_pa
884 BULK COLLECT
885 INTO l_period_name_tab,
886 l_start_date_tab,
887 l_end_date_tab,
888 l_quantity_tab,
889 l_txn_raw_cost_tab,
890 l_txn_brdn_cost_tab,
891 l_txn_revenue_tab,
892 l_proj_raw_cost_tab,
893 l_proj_brdn_cost_tab,
894 l_proj_revenue_tab,
895 l_pou_raw_cost_tab,
896 l_pou_brdn_cost_tab,
897 l_pou_revenue_tab;
898 CLOSE budget_line_cursor_pa;
899 --dbms_output.put_line('Closing budget_line_cursor_pa');
900 ELSIF l_calendar_type = 'G' THEN
901 --dbms_output.put_line('Opening budget_line_cursor_gl');
902 OPEN budget_line_cursor_gl(
903 l_txn_currency_flag,
904 l_res_asg_id_tab(i),
905 l_txn_currency_code_tab(i),
906 l_set_of_books_id,
907 P_FP_COLS_REC.X_VERSION_TYPE);
908 FETCH budget_line_cursor_gl
909 BULK COLLECT
910 INTO l_period_name_tab,
911 l_start_date_tab,
912 l_end_date_tab,
913 l_quantity_tab,
914 l_txn_raw_cost_tab,
915 l_txn_brdn_cost_tab,
916 l_txn_revenue_tab,
917 l_proj_raw_cost_tab,
918 l_proj_brdn_cost_tab,
919 l_proj_revenue_tab,
920 l_pou_raw_cost_tab,
921 l_pou_brdn_cost_tab,
922 l_pou_revenue_tab;
923 CLOSE budget_line_cursor_gl;
924 --dbms_output.put_line('Closing budget_line_cursor_gl');
925 ELSE
926 --dbms_output.put_line('Opening budget_line_cursor_np');
927 OPEN budget_line_cursor_np(
928 l_txn_currency_flag,
929 l_res_asg_id_tab(i),
930 l_txn_currency_code_tab(i),
931 P_PROJECT_ID,
932 P_FP_COLS_REC.X_VERSION_TYPE);
933 FETCH budget_line_cursor_np
934 BULK COLLECT
935 INTO l_period_name_tab,
936 l_start_date_tab,
937 l_end_date_tab,
938 l_quantity_tab,
939 l_txn_raw_cost_tab,
940 l_txn_brdn_cost_tab,
941 l_txn_revenue_tab,
942 l_proj_raw_cost_tab,
943 l_proj_brdn_cost_tab,
944 l_proj_revenue_tab,
945 l_pou_raw_cost_tab,
946 l_pou_brdn_cost_tab,
947 l_pou_revenue_tab;
948 CLOSE budget_line_cursor_np;
949 --dbms_output.put_line('Closing budget_line_cursor_np');
950 END IF;
951
952 SELECT rate_based_flag into l_rate_based_flag
953 FROM pa_resource_assignments
954 WHERE resource_assignment_id = l_res_asg_id_tab(i);
955 --dbms_output.put_line('l_rate_based_flag: '||l_rate_based_flag);
956 IF l_rate_based_flag = 'N' THEN
957 IF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
958 l_quantity_tab := l_txn_revenue_tab;
959 ELSE
960 l_quantity_tab := l_txn_raw_cost_tab;
961 END IF;
962 END IF;
963
964
965 l_amt_dtls_tbl.delete;
966 FOR j IN 1..l_period_name_tab.count LOOP
967 l_amt_dtls_tbl(j).period_name := l_period_name_tab(j);
968 l_amt_dtls_tbl(j).start_date := l_start_date_tab(j);
969 l_amt_dtls_tbl(j).end_date := l_end_date_tab(j);
970 l_amt_dtls_tbl(j).quantity := l_quantity_tab(j);
971 l_amt_dtls_tbl(j).txn_raw_cost := l_txn_raw_cost_tab(j);
972 l_amt_dtls_tbl(j).txn_burdened_cost := l_txn_brdn_cost_tab(j);
973 l_amt_dtls_tbl(j).txn_revenue := l_txn_revenue_tab(j);
974 l_amt_dtls_tbl(j).project_raw_cost := l_proj_raw_cost_tab(j);
975 l_amt_dtls_tbl(j).project_burdened_cost := l_proj_brdn_cost_tab(j);
976 l_amt_dtls_tbl(j).project_revenue := l_proj_revenue_tab(j);
977 l_amt_dtls_tbl(j).project_func_raw_cost := l_pou_raw_cost_tab(j);
978 l_amt_dtls_tbl(j).project_func_burdened_cost := l_pou_brdn_cost_tab(j);
979 l_amt_dtls_tbl(j).project_func_revenue := l_pou_revenue_tab(j);
980 /*For cost version, revenue amounts should be null
981 For revenue version, cost amounts should be null */
982 IF p_fp_cols_rec.x_version_type = 'COST' THEN
983 l_amt_dtls_tbl(j).txn_revenue := null;
984 l_amt_dtls_tbl(j).project_revenue := null;
985 l_amt_dtls_tbl(j).project_func_revenue := null;
986 ELSIF p_fp_cols_rec.x_version_type = 'REVENUE' THEN
987 l_amt_dtls_tbl(j).txn_raw_cost := null;
988 l_amt_dtls_tbl(j).txn_burdened_cost := null;
989 l_amt_dtls_tbl(j).project_raw_cost := null;
990 l_amt_dtls_tbl(j).project_burdened_cost := null;
991 l_amt_dtls_tbl(j).project_func_raw_cost := null;
992 l_amt_dtls_tbl(j).project_func_burdened_cost := null;
993 END IF;
994
995 /* The following logic needs to be handled in Calculate API.
996 Currently, Calculate API does not handle the NULL qty logic. *.
997 IF p_fp_cols_rec.x_version_type = 'REVENUE' AND
998 l_rev_gen_method = 'C' THEN
999 l_amt_dtls_tbl(j).quantity := null;
1000 END IF; */
1001
1002 END LOOP;
1003
1004 IF P_PA_DEBUG_MODE = 'Y' THEN
1005 pa_fp_gen_amount_utils.fp_debug
1006 (p_msg => 'Before calling PA_FP_MAINTAIN_ACTUAL_PUB.MAINTAIN_ACTUAL_AMT_RA',
1007 p_module_name => l_module_name,
1008 p_log_level => 5);
1009 END IF;
1010 /**Populating target budget lines by summing up the values.
1011 *P_AMT_DTLS_REC_TAB has the amt data for each specific resource_assignment_id
1012 *3.and txn_currency_code**/
1013 --dbms_output.put_line('b4 calling MAINTAIN_ACTUAL_AMT_RA');
1014 PA_FP_MAINTAIN_ACTUAL_PUB.MAINTAIN_ACTUAL_AMT_RA (
1015 P_PROJECT_ID => P_PROJECT_ID,
1016 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
1017 P_RESOURCE_ASSIGNMENT_ID => l_res_asg_id_tab(i),
1018 P_TXN_CURRENCY_CODE => l_txn_currency_code_tab(i),
1019 P_AMT_DTLS_REC_TAB => l_amt_dtls_tbl,
1020 P_CALLING_CONTEXT => 'FP_GEN_FCST_COPY_ACTUAL',
1021 X_RETURN_STATUS => x_return_Status,
1022 X_MSG_COUNT => x_msg_count,
1023 X_MSG_DATA => x_msg_data );
1024 --dbms_output.put_line('Status after calling MAINTAIN_ACTUAL_AMT_RA api: '||x_return_status);
1025 IF P_PA_DEBUG_MODE = 'Y' THEN
1026 pa_fp_gen_amount_utils.fp_debug
1027 (p_msg => 'After calling PA_FP_MAINTAIN_ACTUAL_PUB.MAINTAIN_ACTUAL_AMT_RA,
1028 return status is: '||x_return_status,
1029 p_module_name => l_module_name,
1030 p_log_level => 5);
1031 END IF;
1032 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1033 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1034 END IF;
1035
1036 END LOOP;
1037
1038 /* the planning start date and end date in pa_resource assignments table
1039 * should be synched up with the budget lines after copying the actual
1040 * data for all the planning resources. */
1041 IF p_pa_debug_mode = 'Y' THEN
1042 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1043 ( p_msg => 'Before calling PA_FP_MAINTAIN_ACTUAL_PUB.' ||
1044 'SYNC_UP_PLANNING_DATES',
1045 p_module_name => l_module_name,
1046 p_log_level => 5 );
1047 END IF;
1048 --dbms_output.put_line('b4 calling SYNC_UP_PLANNING_DATES');
1049 PA_FP_MAINTAIN_ACTUAL_PUB.SYNC_UP_PLANNING_DATES
1050 ( P_BUDGET_VERSION_ID => p_budget_version_id,
1051 P_CALLING_CONTEXT => 'COPY_ACTUALS',
1052 X_RETURN_STATUS => x_return_Status,
1053 X_MSG_COUNT => x_msg_count,
1054 X_MSG_DATA => x_msg_data );
1055 --dbms_output.put_line('Status after calling SYNC_UP_PLANNING_DATES api: '||x_return_status);
1056 IF p_pa_debug_mode = 'Y' THEN
1057 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1058 ( p_msg => 'Aft calling PA_FP_MAINTAIN_ACTUAL_PUB.' ||
1059 'SYNC_UP_PLANNING_DATES return status ' ||
1060 x_return_status,
1061 p_module_name => l_module_name,
1062 p_log_level => 5 );
1063 END IF;
1064 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1065 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1066 END IF;
1067
1068 -- IPM: New Entity ER ------------------------------------------
1069 -- Actual amounts must be rolled up for non-timephased versions
1070 -- before the Calculate API is called since actuals and planned
1071 -- amounts exist in the same budget line in this case.
1072 IF p_fp_cols_rec.x_time_phased_code = 'N' THEN
1073
1074 DELETE pa_resource_asgn_curr_tmp;
1075
1076 FORALL i IN 1..l_res_asg_id_tab.count
1077 INSERT INTO pa_resource_asgn_curr_tmp (
1078 resource_assignment_id,
1079 txn_currency_code )
1080 VALUES (
1081 l_res_asg_id_tab(i),
1082 l_txn_currency_code_tab(i) );
1083
1084 UPDATE pa_resource_asgn_curr_tmp tmp
1085 SET ( txn_raw_cost_rate_override,
1086 txn_burden_cost_rate_override,
1087 txn_bill_rate_override ) =
1088 ( SELECT rbc.txn_raw_cost_rate_override,
1089 rbc.txn_burden_cost_rate_override,
1090 rbc.txn_bill_rate_override
1091 FROM pa_resource_asgn_curr rbc
1092 WHERE tmp.resource_assignment_id = rbc.resource_assignment_id
1093 AND tmp.txn_currency_code = rbc.txn_currency_code );
1094
1095 -- Call the maintenance api in ROLLUP mode
1096 IF p_pa_debug_mode = 'Y' THEN
1097 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1098 P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.' ||
1099 'MAINTAIN_DATA',
1100 --P_CALLED_MODE => p_called_mode,
1101 P_MODULE_NAME => l_module_name);
1102 END IF;
1103 PA_RES_ASG_CURRENCY_PUB.MAINTAIN_DATA
1104 ( P_FP_COLS_REC => p_fp_cols_rec,
1105 P_CALLING_MODULE => 'FORECAST_GENERATION',
1106 P_VERSION_LEVEL_FLAG => 'N',
1107 P_ROLLUP_FLAG => 'Y',
1108 --P_CALLED_MODE => p_called_mode,
1109 X_RETURN_STATUS => x_return_status,
1110
1111 X_MSG_COUNT => x_msg_count,
1112 X_MSG_DATA => x_msg_data );
1113 IF p_pa_debug_mode = 'Y' THEN
1114 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1115 P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.' ||
1116 'MAINTAIN_DATA: '||x_return_status,
1117 --P_CALLED_MODE => p_called_mode,
1118 P_MODULE_NAME => l_module_name);
1119 END IF;
1120 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1121 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1122 END IF;
1123
1124 END IF; -- IF p_fp_cols_rec.x_time_phased_code = 'N' THEN
1125 -- END OF IPM: New Entity ER ------------------------------------------
1126
1127 IF P_COMMIT_FLAG = 'Y' THEN
1128 COMMIT;
1129 END IF;
1130 IF P_PA_DEBUG_MODE = 'Y' THEN
1131 PA_DEBUG.RESET_CURR_FUNCTION;
1132 END IF;
1133 EXCEPTION
1134 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1135 l_msg_count := FND_MSG_PUB.count_msg;
1136 IF l_msg_count = 1 THEN
1137 PA_INTERFACE_UTILS_PUB.get_messages
1138 ( p_encoded => FND_API.G_TRUE,
1139 p_msg_index => 1,
1140 p_msg_count => l_msg_count,
1141 p_msg_data => l_msg_data,
1142 p_data => l_data,
1143 p_msg_index_out => l_msg_index_out);
1144 x_msg_data := l_data;
1145 x_msg_count := l_msg_count;
1146 ELSE
1147 x_msg_count := l_msg_count;
1148 END IF;
1149
1150 ROLLBACK;
1151
1152 x_return_status := FND_API.G_RET_STS_ERROR;
1153 IF P_PA_DEBUG_MODE = 'Y' THEN
1154 pa_fp_gen_amount_utils.fp_debug
1155 (p_msg => 'Invalid Arguments Passed',
1156 p_module_name => l_module_name,
1157 p_log_level => 5);
1158 PA_DEBUG.RESET_CURR_FUNCTION;
1159 END IF;
1160 RAISE;
1161 WHEN OTHERS THEN
1162 rollback;
1163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1164 x_msg_count := 1;
1165 x_msg_data := substr(sqlerrm,1,240);
1166 -- dbms_output.put_line('error msg :'||x_msg_data);
1167 FND_MSG_PUB.add_exc_msg
1168 ( p_pkg_name => 'PA_FP_COPY_ACTUALS_PUB',
1169 p_procedure_name => 'COPY_ACTUALS',
1170 p_error_text => substr(sqlerrm,1,240));
1171
1172 IF P_PA_DEBUG_MODE = 'Y' THEN
1173 pa_fp_gen_amount_utils.fp_debug
1174 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1175 p_module_name => l_module_name,
1176 p_log_level => 5);
1177 PA_DEBUG.RESET_CURR_FUNCTION;
1178 END IF;
1179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1180
1181 END COPY_ACTUALS;
1182
1183
1184 PROCEDURE CREATE_RES_ASG (
1185 P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
1186 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
1187 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
1188 P_CALLING_PROCESS IN VARCHAR2,
1189 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1190 X_MSG_COUNT OUT NOCOPY NUMBER,
1191 X_MSG_DATA OUT NOCOPY VARCHAR2)
1192 IS
1193 l_module_name VARCHAR2(100) := 'pa.plsql.pa_fp_copy_actuals_pub.create_res_asg';
1194
1195 l_fp_cols_rec PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
1196 l_res_plan_level VARCHAR2(15);
1197
1198 CURSOR project_res_asg_cur
1199 ( p_proj_start_date DATE,
1200 p_proj_completion_date DATE,
1201 c_gen_etc_source_code VARCHAR2 ) IS
1202 SELECT distinct nvl(tmp1.task_id,0),
1203 tmp1.resource_list_member_id,
1204 DECODE(p_calling_process, 'COPY_ACTUALS',
1205 p_proj_start_date,
1206 tmp1.planning_start_date),
1207 DECODE(p_calling_process, 'COPY_ACTUALS',
1208 p_proj_completion_date,
1209 tmp1.planning_end_date),
1210 NVL(c_gen_etc_source_code, NULL)
1211 FROM PA_FP_PLANNING_RES_TMP1 tmp1
1212 WHERE nvl(tmp1.task_id,0) = 0
1213 AND NOT EXISTS (
1214 SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N2)*/ 1
1215 FROM pa_resource_assignments ra
1216 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1217 AND NVL(ra.task_id,0) = 0
1218 AND ra.resource_list_member_id = tmp1.resource_list_member_id);
1219
1220 CURSOR lowestTask_res_asg_cur
1221 ( p_proj_start_date DATE,
1222 p_proj_completion_date DATE,
1223 c_gen_etc_source_code VARCHAR2 ) IS
1224 SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N1)*/
1225 distinct tmp1.task_id,
1226 tmp1.resource_list_member_id,
1227 DECODE(p_calling_process, 'COPY_ACTUALS',
1228 NVL(task.start_date, p_proj_start_date),
1229 tmp1.planning_start_date),
1230 DECODE(p_calling_process, 'COPY_ACTUALS',
1231 NVL(task.completion_date, p_proj_completion_date),
1232 tmp1.planning_end_date),
1233 NVL(c_gen_etc_source_code,
1234 DECODE(p_calling_process, 'COPY_ACTUALS', NULL,task.GEN_ETC_SOURCE_CODE)) -- Bug 4193368 for staffing plan src should not be
1235 -- based on task's etc source
1236 FROM PA_FP_PLANNING_RES_TMP1 tmp1,
1237 pa_tasks task
1238 WHERE nvl(tmp1.task_id,0) > 0
1239 AND tmp1.task_id = task.task_id
1240 AND NOT EXISTS (
1241 SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N2)*/ 1
1242 FROM pa_resource_assignments ra
1243 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1244 AND nvl(ra.task_id,0) = nvl(tmp1.task_id,0)
1245 AND ra.resource_list_member_id = tmp1.resource_list_member_id)
1246 UNION
1247 SELECT distinct nvl(tmp1.task_id,0),
1248 tmp1.resource_list_member_id,
1249 DECODE(p_calling_process, 'COPY_ACTUALS',
1250 p_proj_start_date,
1251 tmp1.planning_start_date),
1252 DECODE(p_calling_process, 'COPY_ACTUALS',
1253 p_proj_completion_date,
1254 tmp1.planning_end_date),
1255 NVL(c_gen_etc_source_code, NULL)
1256 FROM PA_FP_PLANNING_RES_TMP1 tmp1
1257 WHERE nvl(tmp1.task_id,0) = 0
1258 AND NOT EXISTS (
1259 SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N2)*/ 1
1260 FROM pa_resource_assignments ra
1261 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1262 AND NVL(ra.task_id,0) = 0
1263 AND ra.resource_list_member_id = tmp1.resource_list_member_id);
1264
1265 CURSOR topTask_res_asg_cur
1266 ( p_proj_start_date DATE,
1267 p_proj_completion_date DATE,
1268 c_gen_etc_source_code VARCHAR2 ) IS
1269 SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N1)*/
1270 task_t.task_id,
1271 tmp1.resource_list_member_id,
1272 MIN(DECODE(p_calling_process, 'COPY_ACTUALS',
1273 NVL(task_t.start_date, p_proj_start_date),
1274 tmp1.planning_start_date)),
1275 MAX(DECODE(p_calling_process, 'COPY_ACTUALS',
1276 NVL(task_t.completion_date, p_proj_completion_date),
1277 tmp1.planning_end_date)),
1278 NVL(c_gen_etc_source_code,
1279 DECODE(p_calling_process, 'COPY_ACTUALS', NULL,task_t.GEN_ETC_SOURCE_CODE)) -- Bug 4193368 for staffing plan src should not be
1280 -- based on task's etc source
1281 FROM PA_FP_PLANNING_RES_TMP1 tmp1,
1282 pa_tasks task, pa_tasks task_t
1283 WHERE nvl(tmp1.task_id,0) > 0
1284 AND tmp1.task_id = task.task_id
1285 AND task.top_task_id = task_t.task_id
1286 AND NOT EXISTS (
1287 SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N2)*/ 1
1288 FROM pa_resource_assignments ra
1289 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1290 AND nvl(ra.task_id,0) = task_t.task_id
1291 AND ra.resource_list_member_id = tmp1.resource_list_member_id)
1292 GROUP BY task_t.task_id,
1293 tmp1.resource_list_member_id,
1294 NVL(c_gen_etc_source_code,
1295 DECODE(p_calling_process, 'COPY_ACTUALS', NULL,task_t.GEN_ETC_SOURCE_CODE)) -- Bug 4193368 for staffing plan src should not be
1296 -- based on task's etc source
1297 UNION
1298 SELECT nvl(tmp1.task_id,0),
1299 tmp1.resource_list_member_id,
1300 MIN(DECODE(p_calling_process, 'COPY_ACTUALS',
1301 p_proj_start_date,
1302 tmp1.planning_start_date)),
1303 MAX(DECODE(p_calling_process, 'COPY_ACTUALS',
1304 p_proj_completion_date,
1305 tmp1.planning_end_date)),
1306 NVL(c_gen_etc_source_code, NULL)
1307 FROM PA_FP_PLANNING_RES_TMP1 tmp1
1308 WHERE nvl(tmp1.task_id,0) = 0
1309 AND NOT EXISTS (
1310 SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N2)*/ 1
1311 FROM pa_resource_assignments ra
1312 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1313 AND NVL(ra.task_id,0) = 0
1314 AND ra.resource_list_member_id = tmp1.resource_list_member_id)
1315 GROUP BY nvl(tmp1.task_id,0),
1316 tmp1.resource_list_member_id,
1317 NVL(c_gen_etc_source_code, NULL);
1318
1319 l_task_id_tab pa_plsql_datatypes.IdTabTyp;
1320 l_rlm_id_tab pa_plsql_datatypes.IdTabTyp;
1321 l_start_date_tab pa_plsql_datatypes.DateTabTyp;
1322 l_completion_date_tab pa_plsql_datatypes.DateTabTyp;
1323 l_etc_src_code_tab pa_plsql_datatypes.Char30TabTyp;
1324 l_proj_start_date DATE;
1325 l_proj_completion_date DATE;
1326
1327 l_gen_etc_source_code_override VARCHAR2(30);
1328
1329 l_count NUMBER;
1330 l_msg_count NUMBER;
1331 l_data VARCHAR2(1000);
1332 l_msg_data VARCHAR2(1000);
1333 l_msg_index_out NUMBER;
1334 l_spread_curve_id pa_spread_curves_b.spread_curve_id%TYPE;
1335
1336 /* Variables added to replace literals in INSERT stmts. */
1337 l_project_as_id_minus1 NUMBER:=-1;
1338 l_res_as_type_USER_ENTERED VARCHAR2(30):='USER_ENTERED';
1339 l_rec_ver_number_1 NUMBER:=1;
1340
1341 l_proj_struct_sharing_code PA_PROJECTS_ALL.STRUCTURE_SHARING_CODE%TYPE;
1342 BEGIN
1343 IF P_PA_DEBUG_MODE = 'Y' THEN
1344 pa_debug.set_curr_function( p_function => 'CREATE_RES_ASG',
1345 p_debug_mode => p_pa_debug_mode );
1346 END IF;
1347
1348 x_return_status := FND_API.G_RET_STS_SUCCESS;
1349 x_msg_count := 0;
1350
1351 IF (P_FP_COLS_REC.X_BUDGET_VERSION_ID IS NULL) THEN
1352 IF P_PA_DEBUG_MODE = 'Y' THEN
1353 pa_fp_gen_amount_utils.fp_debug
1354 (p_msg => 'Before calling PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTL',
1355 p_module_name => l_module_name,
1356 p_log_level => 5);
1357 END IF;
1358 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS(
1359 P_PROJECT_ID => P_PROJECT_ID,
1360 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
1361 X_FP_COLS_REC => l_fp_cols_rec,
1362 X_RETURN_STATUS => x_return_status,
1363 X_MSG_COUNT => x_msg_count,
1364 X_MSG_DATA => x_msg_data);
1365 IF P_PA_DEBUG_MODE = 'Y' THEN
1366 pa_fp_gen_amount_utils.fp_debug
1367 (p_msg => 'After calling PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS,
1368 return status:'||x_return_status,
1369 p_module_name => l_module_name,
1370 p_log_level => 5);
1371 END IF;
1372 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1373 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1374 END IF;
1375 ELSE
1376 l_fp_cols_rec := P_FP_COLS_REC;
1377 END IF;
1378 l_res_plan_level := l_fp_cols_rec.X_FIN_PLAN_LEVEL_CODE;
1379
1380 SELECT NVL(start_date,trunc(sysdate)),
1381 NVL(completion_date,trunc(sysdate))
1382 INTO l_proj_start_date, l_proj_completion_date
1383 FROM pa_projects_all
1384 WHERE project_id = P_PROJECT_ID;
1385
1386 /* When the Target is a Revenue-only version, we need to take the target
1387 * version's ETC source code instead of the task-level source code for
1388 * the target resources that we are processing. */
1389 IF l_fp_cols_rec.x_version_type = 'REVENUE' THEN
1390 l_gen_etc_source_code_override := l_fp_cols_rec.x_gen_etc_src_code;
1391 ELSE
1392 l_gen_etc_source_code_override := NULL;
1393 END IF;
1394
1395 l_proj_struct_sharing_code := NVL(pa_project_structure_utils.
1396 get_structure_sharing_code(P_PROJECT_ID), 'SHARE_FULL');
1397
1398 -- Bug 4174997: If the calling process is COPY_ACTUALS then we should
1399 -- go with the Target version's planning level, since we always have
1400 -- financial tasks for actuals.
1401
1402 -- Bug 4232094: When the structure is 'SPLIT_NO_MAPPING', the only
1403 -- scenario in which we need to use the project-level cursor is when
1404 -- the Target version is Revenue and the Source version is Workplan.
1405 -- In all other scenarios, we should go with the Target version's
1406 -- planning level. This change overrides Bug fix 4174997.
1407
1408 IF (l_res_plan_level = 'P' OR
1409 (l_proj_struct_sharing_code = 'SPLIT_NO_MAPPING' AND
1410 l_fp_cols_rec.x_version_type = 'REVENUE' AND
1411 l_fp_cols_rec.x_gen_etc_src_code = 'WORKPLAN_RESOURCES')) THEN
1412 OPEN project_res_asg_cur
1413 ( l_proj_start_date,
1414 l_proj_completion_date,
1415 l_gen_etc_source_code_override );
1416 FETCH project_res_asg_cur
1417 BULK COLLECT
1418 INTO l_task_id_tab,
1419 l_rlm_id_tab,
1420 l_start_date_tab,
1421 l_completion_date_tab,
1422 l_etc_src_code_tab;
1423 CLOSE project_res_asg_cur;
1424 ELSIF (l_res_plan_level = 'L') THEN
1425 -- hr_utility.trace('in create res asg low task fetch '||l_task_id_tab.count);
1426 OPEN lowestTask_res_asg_cur
1427 ( l_proj_start_date,
1428 l_proj_completion_date,
1429 l_gen_etc_source_code_override );
1430 FETCH lowestTask_res_asg_cur
1431 BULK COLLECT
1432 INTO l_task_id_tab,
1433 l_rlm_id_tab,
1434 l_start_date_tab,
1435 l_completion_date_tab,
1436 l_etc_src_code_tab;
1437 CLOSE lowestTask_res_asg_cur;
1438 ELSIF (l_res_plan_level = 'T') THEN
1439 OPEN topTask_res_asg_cur
1440 ( l_proj_start_date,
1441 l_proj_completion_date,
1442 l_gen_etc_source_code_override );
1443 FETCH topTask_res_asg_cur
1444 BULK COLLECT
1445 INTO l_task_id_tab,
1446 l_rlm_id_tab,
1447 l_start_date_tab,
1448 l_completion_date_tab,
1449 l_etc_src_code_tab;
1450 CLOSE topTask_res_asg_cur;
1451 END IF;
1452 -- hr_utility.trace('in create res asg tab count '||l_task_id_tab.count);
1453 IF (l_task_id_tab.count = 0 ) THEN
1454 IF P_PA_DEBUG_MODE = 'Y' THEN
1455 PA_DEBUG.RESET_CURR_FUNCTION;
1456 END IF;
1457 RETURN;
1458 END IF;
1459
1460 FORALL i in l_task_id_tab.first .. l_task_id_tab.last
1461 INSERT INTO PA_RESOURCE_ASSIGNMENTS (
1462 RESOURCE_ASSIGNMENT_ID,
1463 BUDGET_VERSION_ID,
1464 PROJECT_ID,
1465 RESOURCE_LIST_MEMBER_ID,
1466 TASK_ID,
1467 LAST_UPDATE_DATE,
1468 LAST_UPDATED_BY,
1469 CREATION_DATE,
1470 CREATED_BY,
1471 LAST_UPDATE_LOGIN,
1472 PROJECT_ASSIGNMENT_ID,
1473 PLANNING_START_DATE,
1474 PLANNING_END_DATE,
1475 RESOURCE_ASSIGNMENT_TYPE,
1476 RECORD_VERSION_NUMBER,
1477 TRANSACTION_SOURCE_CODE )
1478 VALUES (
1479 pa_resource_assignments_s.nextval,
1480 p_budget_version_id,
1481 p_project_id,
1482 l_rlm_id_tab(i),
1483 l_task_id_tab(i),
1484 sysdate,
1485 FND_GLOBAL.USER_ID,
1486 sysdate,
1487 FND_GLOBAL.USER_ID,
1488 FND_GLOBAL.LOGIN_ID,
1489 l_project_as_id_minus1,
1490 l_start_date_tab(i),
1491 l_completion_date_tab(i),
1492 l_res_as_type_USER_ENTERED,
1493 l_rec_ver_number_1,
1494 l_etc_src_code_tab(i)
1495 );
1496 IF P_PA_DEBUG_MODE = 'Y' THEN
1497 pa_fp_gen_amount_utils.fp_debug
1498 (p_msg => 'Before calling update_res_defaults',
1499 p_module_name => l_module_name,
1500 p_log_level => 5);
1501 END IF;
1502 PA_FP_GEN_PUB.UPDATE_RES_DEFAULTS
1503 (P_PROJECT_ID => P_PROJECT_ID,
1504 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
1505 X_RETURN_STATUS => x_return_status,
1506 X_MSG_COUNT => x_msg_count,
1507 X_MSG_DATA => x_msg_data );
1508 IF P_PA_DEBUG_MODE = 'Y' THEN
1509 pa_fp_gen_amount_utils.fp_debug
1510 (p_msg => 'Before calling update_res_defaults',
1511 p_module_name => l_module_name,
1512 p_log_level => 5);
1513 END IF;
1514 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1515 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1516 END IF;
1517
1518 SELECT spread_curve_id
1519 INTO l_spread_curve_id
1520 FROM pa_spread_curves_b
1521 WHERE spread_curve_code = 'FIXED_DATE';
1522
1523 UPDATE PA_RESOURCE_ASSIGNMENTS
1524 SET SP_FIXED_DATE = PLANNING_START_DATE
1525 WHERE SP_FIXED_DATE IS NULL
1526 AND SPREAD_CURVE_ID = l_spread_curve_id
1527 AND BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
1528
1529 IF P_PA_DEBUG_MODE = 'Y' THEN
1530 PA_DEBUG.RESET_CURR_FUNCTION;
1531 END IF;
1532 EXCEPTION
1533 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1534 l_msg_count := FND_MSG_PUB.count_msg;
1535 IF l_msg_count = 1 THEN
1536 PA_INTERFACE_UTILS_PUB.get_messages
1537 ( p_encoded => FND_API.G_TRUE,
1538 p_msg_index => 1,
1539 p_msg_count => l_msg_count,
1540 p_msg_data => l_msg_data,
1541 p_data => l_data,
1542 p_msg_index_out => l_msg_index_out);
1543 x_msg_data := l_data;
1544 x_msg_count := l_msg_count;
1545 ELSE
1546 x_msg_count := l_msg_count;
1547 END IF;
1548
1549 ROLLBACK;
1550
1551 x_return_status := FND_API.G_RET_STS_ERROR;
1552 IF P_PA_DEBUG_MODE = 'Y' THEN
1553 pa_fp_gen_amount_utils.fp_debug
1554 (p_msg => 'Invalid Arguments Passed',
1555 p_module_name => l_module_name,
1556 p_log_level => 5);
1557 PA_DEBUG.RESET_CURR_FUNCTION;
1558 END IF;
1559 RAISE;
1560 WHEN OTHERS THEN
1561 rollback;
1562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1563 x_msg_count := 1;
1564 x_msg_data := substr(sqlerrm,1,240);
1565 -- dbms_output.put_line('error msg :'||x_msg_data);
1566 FND_MSG_PUB.add_exc_msg
1567 ( p_pkg_name => 'PA_FP_COPY_ACTUALS_PUB',
1568 p_procedure_name => 'CREATE_RES_ASG',
1569 p_error_text => substr(sqlerrm,1,240));
1570
1571 IF P_PA_DEBUG_MODE = 'Y' THEN
1572 pa_fp_gen_amount_utils.fp_debug
1573 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1574 p_module_name => l_module_name,
1575 p_log_level => 5);
1576 PA_DEBUG.RESET_CURR_FUNCTION;
1577 END IF;
1578 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1579 END CREATE_RES_ASG;
1580
1581
1582 PROCEDURE UPDATE_RES_ASG (
1583 P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
1584 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
1585 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
1586 P_CALLING_PROCESS IN VARCHAR2,
1587 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1588 X_MSG_COUNT OUT NOCOPY NUMBER,
1589 X_MSG_DATA OUT NOCOPY VARCHAR2)
1590 IS
1591 l_module_name VARCHAR2(100) := 'pa.plsql.pa_fp_copy_actuals_pub.update_res_asg';
1592 l_res_plan_level VARCHAR2(15);
1593
1594 l_count NUMBER;
1595 l_msg_count NUMBER;
1596 l_data VARCHAR2(1000);
1597 l_msg_data VARCHAR2(1000);
1598 l_msg_index_out NUMBER;
1599
1600 l_proj_struct_sharing_code PA_PROJECTS_ALL.STRUCTURE_SHARING_CODE%TYPE;
1601 BEGIN
1602 IF P_PA_DEBUG_MODE = 'Y' THEN
1603 pa_debug.set_curr_function( p_function => 'UPDATE_RES_ASG',
1604 p_debug_mode => p_pa_debug_mode );
1605 END IF;
1606
1607 x_return_status := FND_API.G_RET_STS_SUCCESS;
1608 x_msg_count := 0;
1609 l_res_plan_level := p_fp_cols_rec.X_FIN_PLAN_LEVEL_CODE;
1610
1611 l_proj_struct_sharing_code := NVL(pa_project_structure_utils.
1612 get_structure_sharing_code(P_PROJECT_ID),'SHARE_FULL');
1613
1614 -- Bug 4174997: If the calling process is COPY_ACTUALS then we should
1615 -- go with the Target version's planning level, since we always have
1616 -- financial tasks for actuals.
1617
1618 -- Bug 4232094: When the structure is 'SPLIT_NO_MAPPING', the only
1619 -- scenario in which we need to use the project-level cursor is when
1620 -- the Target version is Revenue and the Source version is Workplan.
1621 -- In all other scenarios, we should go with the Target version's
1622 -- planning level. This change overrides Bug fix 4174997.
1623
1624 IF (l_res_plan_level = 'P' OR
1625 (l_proj_struct_sharing_code = 'SPLIT_NO_MAPPING' AND
1626 p_fp_cols_rec.x_version_type = 'REVENUE' AND
1627 p_fp_cols_rec.x_gen_etc_src_code = 'WORKPLAN_RESOURCES')) THEN
1628
1629 UPDATE PA_FP_PLANNING_RES_TMP1 tmp1
1630 SET resource_assignment_id =
1631 (SELECT resource_assignment_id
1632 FROM pa_resource_assignments ra
1633 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1634 AND ra.project_id = P_PROJECT_ID
1635 AND nvl(ra.task_id,0) = 0
1636 AND ra.resource_list_member_id = tmp1.resource_list_member_id);
1637
1638 ELSIF l_res_plan_level = 'L' THEN
1639
1640 UPDATE PA_FP_PLANNING_RES_TMP1 tmp1
1641 SET resource_assignment_id =
1642 (SELECT resource_assignment_id
1643 FROM pa_resource_assignments ra
1644 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1645 AND ra.project_id = P_PROJECT_ID
1646 AND ra.task_id = tmp1.task_id
1647 AND ra.resource_list_member_id = tmp1.resource_list_member_id)
1648 WHERE tmp1.task_id is NOT NULL
1649 AND tmp1.task_id > 0;
1650
1651 UPDATE PA_FP_PLANNING_RES_TMP1 tmp1
1652 SET resource_assignment_id =
1653 (SELECT resource_assignment_id
1654 FROM pa_resource_assignments ra
1655 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1656 AND ra.project_id = P_PROJECT_ID
1657 AND nvl(ra.task_id,0) = 0
1658 AND ra.resource_list_member_id = tmp1.resource_list_member_id)
1659 WHERE nvl(tmp1.task_id,0) = 0;
1660
1661 ELSIF l_res_plan_level = 'T' THEN
1662
1663 UPDATE PA_FP_PLANNING_RES_TMP1 tmp1
1664 SET resource_assignment_id =
1665 (SELECT resource_assignment_id
1666 FROM pa_resource_assignments ra,
1667 pa_tasks t
1668 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1669 AND ra.project_id = P_PROJECT_ID
1670 AND tmp1.task_id = t.task_id
1671 AND t.top_task_id = ra.task_id
1672 AND ra.resource_list_member_id = tmp1.resource_list_member_id)
1673 WHERE tmp1.task_id is NOT NULL
1674 AND tmp1.task_id > 0;
1675
1676 UPDATE PA_FP_PLANNING_RES_TMP1 tmp1
1677 SET resource_assignment_id =
1678 (SELECT resource_assignment_id
1679 FROM pa_resource_assignments ra
1680 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1681 AND ra.project_id = P_PROJECT_ID
1682 AND nvl(ra.task_id,0) = 0
1683 AND ra.resource_list_member_id = tmp1.resource_list_member_id)
1684 WHERE nvl(tmp1.task_id,0) = 0;
1685
1686 END IF;
1687
1688 IF P_PA_DEBUG_MODE = 'Y' THEN
1689 PA_DEBUG.RESET_CURR_FUNCTION;
1690 END IF;
1691 EXCEPTION
1692 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1693 l_msg_count := FND_MSG_PUB.count_msg;
1694 IF l_msg_count = 1 THEN
1695 PA_INTERFACE_UTILS_PUB.get_messages
1696 ( p_encoded => FND_API.G_TRUE,
1697 p_msg_index => 1,
1698 p_msg_count => l_msg_count,
1699 p_msg_data => l_msg_data,
1700 p_data => l_data,
1701 p_msg_index_out => l_msg_index_out);
1702 x_msg_data := l_data;
1703 x_msg_count := l_msg_count;
1704 ELSE
1705 x_msg_count := l_msg_count;
1706 END IF;
1707
1708 ROLLBACK;
1709
1710 x_return_status := FND_API.G_RET_STS_ERROR;
1711
1712 IF P_PA_DEBUG_MODE = 'Y' THEN
1713 pa_fp_gen_amount_utils.fp_debug
1714 (p_msg => 'Invalid Arguments Passed',
1715 p_module_name => l_module_name,
1716 p_log_level => 5);
1717 PA_DEBUG.RESET_CURR_FUNCTION;
1718 END IF;
1719 RAISE;
1720 WHEN OTHERS THEN
1721 rollback;
1722 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1723 x_msg_count := 1;
1724 x_msg_data := substr(sqlerrm,1,240);
1725 -- dbms_output.put_line('error msg :'||x_msg_data);
1726 FND_MSG_PUB.add_exc_msg
1727 ( p_pkg_name => 'PA_FP_COPY_ACTUALS_PUB',
1728 p_procedure_name => 'UPDATE_RES_ASG',
1729 p_error_text => substr(sqlerrm,1,240));
1730
1731 IF P_PA_DEBUG_MODE = 'Y' THEN
1732 pa_fp_gen_amount_utils.fp_debug
1733 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1734 p_module_name => l_module_name,
1735 p_log_level => 5);
1736 PA_DEBUG.RESET_CURR_FUNCTION;
1737 END IF;
1738 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1739 END UPDATE_RES_ASG;
1740
1741
1742 END PA_FP_COPY_ACTUALS_PUB;