[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.10.12020000.4 2013/05/23 07:28:09 bpottipa 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.prj_raw_cost, 0) <> 0) OR --Bug 9666017
90 (NVL(pji_tmp.pou_raw_cost, 0) <> 0) OR --Bug 9666017
91 (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
92 (NVL(pji_tmp.prj_brdn_cost, 0) <> 0) OR --Bug 9666017
93 (NVL(pji_tmp.pou_brdn_cost, 0) <> 0) OR --Bug 9666017
94 (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
95 (NVL(pji_tmp.prj_revenue, 0) <> 0) OR --Bug 9666017
96 (NVL(pji_tmp.pou_revenue, 0) <> 0) OR --Bug 9666017
97 (NVL(pji_tmp.quantity,0) <> 0)
98 )
99 AND pd.org_id = c_org_id
100 AND pd.period_name = pji_tmp.period_name
101 AND pji_tmp.source_id = c_res_asg_id
102 AND DECODE(c_multi_currency_flag,'Y',
103 pji_tmp.txn_currency_code,c_txn_currency_code)
104 = c_txn_currency_code
105 GROUP BY pji_tmp.period_name,
106 pd.start_date,
107 pd.end_date
108 UNION ALL
109 SELECT pji_tmp.period_name,
110 pd.start_date,
111 pd.end_date,
112 sum(pji_tmp.quantity),
113 sum(DECODE(c_multi_currency_flag,
114 'Y', pji_tmp.txn_raw_cost,
115 'N', pji_tmp.prj_raw_cost,
116 'A', pji_tmp.pou_raw_cost)),
117 sum(DECODE(c_multi_currency_flag,
118 'Y', pji_tmp.txn_brdn_cost,
119 'N', pji_tmp.prj_brdn_cost,
120 'A', pji_tmp.pou_brdn_cost)),
121 sum(DECODE(c_multi_currency_flag,
122 'Y', pji_tmp.txn_revenue,
123 'N', pji_tmp.prj_revenue,
124 'A', pji_tmp.pou_revenue)),
125 sum(pji_tmp.prj_raw_cost),
126 sum(pji_tmp.prj_brdn_cost),
127 sum(pji_tmp.prj_revenue),
128 sum(DECODE(c_multi_currency_flag,
129 'Y', pji_tmp.pou_raw_cost,
130 'N', pji_tmp.prj_raw_cost,
131 'A', pji_tmp.pou_raw_cost)),
132 sum(DECODE(c_multi_currency_flag,
133 'Y', pji_tmp.pou_brdn_cost,
134 'N', pji_tmp.prj_brdn_cost,
135 'A', pji_tmp.pou_brdn_cost)),
136 sum(DECODE(c_multi_currency_flag,
137 'Y', pji_tmp.pou_revenue,
138 'N', pji_tmp.prj_revenue,
139 'A', pji_tmp.pou_revenue))
140 FROM pji_fm_xbs_accum_tmp1 pji_tmp,pa_periods_all pd
141 WHERE c_version_type = 'COST'
142 AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
143 (NVL(pji_tmp.prj_raw_cost, 0) <> 0) OR --Bug 9666017
144 (NVL(pji_tmp.pou_raw_cost, 0) <> 0) OR --Bug 9666017
145 (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
146 (NVL(pji_tmp.prj_brdn_cost, 0) <> 0) OR --Bug 9666017
147 (NVL(pji_tmp.pou_brdn_cost, 0) <> 0) OR --Bug 9666017
148 (NVL(pji_tmp.quantity,0) <> 0)
149 )
150 AND pd.org_id = c_org_id
151 AND pd.period_name = pji_tmp.period_name
152 AND pji_tmp.source_id = c_res_asg_id
153 AND DECODE(c_multi_currency_flag,'Y',
154 pji_tmp.txn_currency_code,c_txn_currency_code)
155 = c_txn_currency_code
156 GROUP BY pji_tmp.period_name,
157 pd.start_date,
158 pd.end_date
159 UNION ALL
160 SELECT pji_tmp.period_name,
161 pd.start_date,
162 pd.end_date,
163 sum(pji_tmp.quantity),
164 sum(DECODE(c_multi_currency_flag,
165 'Y', pji_tmp.txn_raw_cost,
166 'N', pji_tmp.prj_raw_cost,
167 'A', pji_tmp.pou_raw_cost)),
168 sum(DECODE(c_multi_currency_flag,
169 'Y', pji_tmp.txn_brdn_cost,
170 'N', pji_tmp.prj_brdn_cost,
171 'A', pji_tmp.pou_brdn_cost)),
172 sum(DECODE(c_multi_currency_flag,
173 'Y', pji_tmp.txn_revenue,
174 'N', pji_tmp.prj_revenue,
175 'A', pji_tmp.pou_revenue)),
176 sum(pji_tmp.prj_raw_cost),
177 sum(pji_tmp.prj_brdn_cost),
178 sum(pji_tmp.prj_revenue),
179 sum(DECODE(c_multi_currency_flag,
180 'Y', pji_tmp.pou_raw_cost,
181 'N', pji_tmp.prj_raw_cost,
182 'A', pji_tmp.pou_raw_cost)),
183 sum(DECODE(c_multi_currency_flag,
184 'Y', pji_tmp.pou_brdn_cost,
185 'N', pji_tmp.prj_brdn_cost,
186 'A', pji_tmp.pou_brdn_cost)),
187 sum(DECODE(c_multi_currency_flag,
188 'Y', pji_tmp.pou_revenue,
189 'N', pji_tmp.prj_revenue,
190 'A', pji_tmp.pou_revenue))
191 FROM pji_fm_xbs_accum_tmp1 pji_tmp,pa_periods_all pd
192 WHERE c_version_type = 'REVENUE'
193 AND (
194 (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
195 (NVL(pji_tmp.prj_revenue, 0) <> 0) OR --Bug 9666017
196 (NVL(pji_tmp.pou_revenue, 0) <> 0) OR --Bug 9666017
197 (NVL(pji_tmp.quantity,0) <> 0)
198 )
199 AND pd.org_id = c_org_id
200 AND pd.period_name = pji_tmp.period_name
201 AND pji_tmp.source_id = c_res_asg_id
202 AND DECODE(c_multi_currency_flag,'Y',
203 pji_tmp.txn_currency_code,c_txn_currency_code)
204 = c_txn_currency_code
205 GROUP BY pji_tmp.period_name,
206 pd.start_date,
207 pd.end_date;
208
209 CURSOR budget_line_cursor_gl(c_multi_currency_flag VARCHAR2,
210 c_res_asg_id NUMBER,
211 c_txn_currency_code VARCHAR2,
212 c_set_of_books_id NUMBER,
213 c_version_type VARCHAR2) IS
214 SELECT pji_tmp.period_name,
215 gd.start_date,
216 gd.end_date,
217 sum(pji_tmp.quantity),
218 sum(DECODE(c_multi_currency_flag,
219 'Y', pji_tmp.txn_raw_cost,
220 'N', pji_tmp.prj_raw_cost,
221 'A', pji_tmp.pou_raw_cost)),
222 sum(DECODE(c_multi_currency_flag,
223 'Y', pji_tmp.txn_brdn_cost,
224 'N', pji_tmp.prj_brdn_cost,
225 'A', pji_tmp.pou_brdn_cost)),
226 sum(DECODE(c_multi_currency_flag,
227 'Y', pji_tmp.txn_revenue,
228 'N', pji_tmp.prj_revenue,
229 'A', pji_tmp.pou_revenue)),
230 sum(pji_tmp.prj_raw_cost),
231 sum(pji_tmp.prj_brdn_cost),
232 sum(pji_tmp.prj_revenue),
233 sum(DECODE(c_multi_currency_flag,
234 'Y', pji_tmp.pou_raw_cost,
235 'N', pji_tmp.prj_raw_cost,
236 'A', pji_tmp.pou_raw_cost)),
237 sum(DECODE(c_multi_currency_flag,
238 'Y', pji_tmp.pou_brdn_cost,
239 'N', pji_tmp.prj_brdn_cost,
240 'A', pji_tmp.pou_brdn_cost)),
241 sum(DECODE(c_multi_currency_flag,
242 'Y', pji_tmp.pou_revenue,
243 'N', pji_tmp.prj_revenue,
244 'A', pji_tmp.pou_revenue))
245 FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
246 WHERE c_version_type = 'ALL'
247 AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
248 (NVL(pji_tmp.prj_raw_cost, 0) <> 0) OR --Bug 9666017
249 (NVL(pji_tmp.pou_raw_cost, 0) <> 0) OR --Bug 9666017
250 (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
251 (NVL(pji_tmp.prj_brdn_cost, 0) <> 0) OR --Bug 9666017
252 (NVL(pji_tmp.pou_brdn_cost, 0) <> 0) OR --Bug 9666017
253 (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
254 (NVL(pji_tmp.prj_revenue, 0) <> 0) OR --Bug 9666017
255 (NVL(pji_tmp.pou_revenue, 0) <> 0) OR --Bug 9666017
256 (NVL(pji_tmp.quantity,0) <> 0)
257 )
258 AND gd.SET_OF_BOOKS_ID = c_set_of_books_id
259 AND gd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
260 AND gd.ADJUSTMENT_PERIOD_FLAG = 'N'
261 AND gd.period_name = pji_tmp.period_name
262 AND pji_tmp.source_id = c_res_asg_id
263 AND DECODE(c_multi_currency_flag,'Y',
264 pji_tmp.txn_currency_code,c_txn_currency_code)
265 = c_txn_currency_code
266 GROUP BY pji_tmp.period_name,
267 gd.start_date,
268 gd.end_date
269 UNION ALL
270 SELECT pji_tmp.period_name,
271 gd.start_date,
272 gd.end_date,
273 sum(pji_tmp.quantity),
274 sum(DECODE(c_multi_currency_flag,
275 'Y', pji_tmp.txn_raw_cost,
276 'N', pji_tmp.prj_raw_cost,
277 'A', pji_tmp.pou_raw_cost)),
278 sum(DECODE(c_multi_currency_flag,
279 'Y', pji_tmp.txn_brdn_cost,
280 'N', pji_tmp.prj_brdn_cost,
281 'A', pji_tmp.pou_brdn_cost)),
282 sum(DECODE(c_multi_currency_flag,
283 'Y', pji_tmp.txn_revenue,
284 'N', pji_tmp.prj_revenue,
285 'A', pji_tmp.pou_revenue)),
286 sum(pji_tmp.prj_raw_cost),
287 sum(pji_tmp.prj_brdn_cost),
288 sum(pji_tmp.prj_revenue),
289 sum(DECODE(c_multi_currency_flag,
290 'Y', pji_tmp.pou_raw_cost,
291 'N', pji_tmp.prj_raw_cost,
292 'A', pji_tmp.pou_raw_cost)),
293 sum(DECODE(c_multi_currency_flag,
294 'Y', pji_tmp.pou_brdn_cost,
295 'N', pji_tmp.prj_brdn_cost,
296 'A', pji_tmp.pou_brdn_cost)),
297 sum(DECODE(c_multi_currency_flag,
298 'Y', pji_tmp.pou_revenue,
299 'N', pji_tmp.prj_revenue,
300 'A', pji_tmp.pou_revenue))
301 FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
302 WHERE c_version_type = 'COST'
303 AND (
304 (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
305 (NVL(pji_tmp.prj_raw_cost, 0) <> 0) OR --Bug 9666017
306 (NVL(pji_tmp.pou_raw_cost, 0) <> 0) OR --Bug 9666017
307 (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
308 (NVL(pji_tmp.prj_brdn_cost, 0) <> 0) OR --Bug 9666017
309 (NVL(pji_tmp.pou_brdn_cost, 0) <> 0) OR --Bug 9666017
310 (NVL(pji_tmp.quantity,0) <> 0)
311 )
312 AND gd.SET_OF_BOOKS_ID = c_set_of_books_id
313 AND gd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
314 AND gd.ADJUSTMENT_PERIOD_FLAG = 'N'
315 AND gd.period_name = pji_tmp.period_name
316 AND pji_tmp.source_id = c_res_asg_id
317 AND DECODE(c_multi_currency_flag,'Y',
318 pji_tmp.txn_currency_code,c_txn_currency_code)
319 = c_txn_currency_code
320 GROUP BY pji_tmp.period_name,
321 gd.start_date,
322 gd.end_date
323 UNION ALL
324 SELECT pji_tmp.period_name,
325 gd.start_date,
326 gd.end_date,
327 sum(pji_tmp.quantity),
328 sum(DECODE(c_multi_currency_flag,
329 'Y', pji_tmp.txn_raw_cost,
330 'N', pji_tmp.prj_raw_cost,
331 'A', pji_tmp.pou_raw_cost)),
332 sum(DECODE(c_multi_currency_flag,
333 'Y', pji_tmp.txn_brdn_cost,
334 'N', pji_tmp.prj_brdn_cost,
335 'A', pji_tmp.pou_brdn_cost)),
336 sum(DECODE(c_multi_currency_flag,
337 'Y', pji_tmp.txn_revenue,
338 'N', pji_tmp.prj_revenue,
339 'A', pji_tmp.pou_revenue)),
340 sum(pji_tmp.prj_raw_cost),
341 sum(pji_tmp.prj_brdn_cost),
342 sum(pji_tmp.prj_revenue),
343 sum(DECODE(c_multi_currency_flag,
344 'Y', pji_tmp.pou_raw_cost,
345 'N', pji_tmp.prj_raw_cost,
346 'A', pji_tmp.pou_raw_cost)),
347 sum(DECODE(c_multi_currency_flag,
348 'Y', pji_tmp.pou_brdn_cost,
349 'N', pji_tmp.prj_brdn_cost,
350 'A', pji_tmp.pou_brdn_cost)),
351 sum(DECODE(c_multi_currency_flag,
352 'Y', pji_tmp.pou_revenue,
353 'N', pji_tmp.prj_revenue,
354 'A', pji_tmp.pou_revenue))
355 FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
356 WHERE c_version_type = 'REVENUE'
357 AND (
358 (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
359 (NVL(pji_tmp.prj_revenue, 0) <> 0) OR --Bug 9666017
360 (NVL(pji_tmp.pou_revenue, 0) <> 0) OR --Bug 9666017
361 (NVL(pji_tmp.quantity,0) <> 0)
362 )
363 AND gd.SET_OF_BOOKS_ID = c_set_of_books_id
364 AND gd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
365 AND gd.ADJUSTMENT_PERIOD_FLAG = 'N'
366 AND gd.period_name = pji_tmp.period_name
367 AND pji_tmp.source_id = c_res_asg_id
368 AND DECODE(c_multi_currency_flag,'Y',
369 pji_tmp.txn_currency_code,c_txn_currency_code)
370 = c_txn_currency_code
371 GROUP BY pji_tmp.period_name,
372 gd.start_date,
373 gd.end_date;
374
375 CURSOR budget_line_cursor_np(c_multi_currency_flag VARCHAR2,
376 c_res_asg_id NUMBER,
377 c_txn_currency_code VARCHAR2,
378 c_proj_id NUMBER,
379 c_version_type VARCHAR2) IS
380 SELECT pji_tmp.period_name,
381 nvl(ra.planning_start_date, TRUNC(Sysdate)),
382 nvl(ra.planning_end_date, TRUNC(Sysdate)),
383 sum(pji_tmp.quantity),
384 sum(DECODE(c_multi_currency_flag,
385 'Y', pji_tmp.txn_raw_cost,
386 'N', pji_tmp.prj_raw_cost,
387 'A', pji_tmp.pou_raw_cost)),
388 sum(DECODE(c_multi_currency_flag,
389 'Y', pji_tmp.txn_brdn_cost,
390 'N', pji_tmp.prj_brdn_cost,
391 'A', pji_tmp.pou_brdn_cost)),
392 sum(DECODE(c_multi_currency_flag,
393 'Y', pji_tmp.txn_revenue,
394 'N', pji_tmp.prj_revenue,
395 'A', pji_tmp.pou_revenue)),
396 sum(pji_tmp.prj_raw_cost),
397 sum(pji_tmp.prj_brdn_cost),
398 sum(pji_tmp.prj_revenue),
399 sum(DECODE(c_multi_currency_flag,
400 'Y', pji_tmp.pou_raw_cost,
401 'N', pji_tmp.prj_raw_cost,
402 'A', pji_tmp.pou_raw_cost)),
403 sum(DECODE(c_multi_currency_flag,
404 'Y', pji_tmp.pou_brdn_cost,
405 'N', pji_tmp.prj_brdn_cost,
406 'A', pji_tmp.pou_brdn_cost)),
407 sum(DECODE(c_multi_currency_flag,
408 'Y', pji_tmp.pou_revenue,
409 'N', pji_tmp.prj_revenue,
410 'A', pji_tmp.pou_revenue))
411 FROM pji_fm_xbs_accum_tmp1 pji_tmp,
412 pa_resource_assignments ra
413 WHERE c_version_type = 'ALL'
414 AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
415 (NVL(pji_tmp.prj_raw_cost, 0) <> 0) OR --Bug 9666017
416 (NVL(pji_tmp.pou_raw_cost, 0) <> 0) OR --Bug 9666017
417 (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
418 (NVL(pji_tmp.prj_brdn_cost, 0) <> 0) OR --Bug 9666017
419 (NVL(pji_tmp.pou_brdn_cost, 0) <> 0) OR --Bug 9666017
420 (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
421 (NVL(pji_tmp.prj_revenue, 0) <> 0) OR --Bug 9666017
422 (NVL(pji_tmp.pou_revenue, 0) <> 0) OR --Bug 9666017
423 (NVL(pji_tmp.quantity,0) <> 0)
424 )
425 AND pji_tmp.source_id = c_res_asg_id
426 AND DECODE(c_multi_currency_flag,'Y',
427 pji_tmp.txn_currency_code,c_txn_currency_code)
428 = c_txn_currency_code
429 AND ra.resource_assignment_id = c_res_asg_id
430 GROUP BY pji_tmp.period_name,
431 nvl(ra.planning_start_date, TRUNC(Sysdate)),
432 nvl(ra.planning_end_date, TRUNC(Sysdate))
433 UNION ALL
434 SELECT pji_tmp.period_name,
435 nvl(ra.planning_start_date, TRUNC(Sysdate)),
436 nvl(ra.planning_end_date, TRUNC(Sysdate)),
437 sum(pji_tmp.quantity),
438 sum(DECODE(c_multi_currency_flag,
439 'Y', pji_tmp.txn_raw_cost,
440 'N', pji_tmp.prj_raw_cost,
441 'A', pji_tmp.pou_raw_cost)),
442 sum(DECODE(c_multi_currency_flag,
443 'Y', pji_tmp.txn_brdn_cost,
444 'N', pji_tmp.prj_brdn_cost,
445 'A', pji_tmp.pou_brdn_cost)),
446 sum(DECODE(c_multi_currency_flag,
447 'Y', pji_tmp.txn_revenue,
448 'N', pji_tmp.prj_revenue,
449 'A', pji_tmp.pou_revenue)),
450 sum(pji_tmp.prj_raw_cost),
451 sum(pji_tmp.prj_brdn_cost),
452 sum(pji_tmp.prj_revenue),
453 sum(DECODE(c_multi_currency_flag,
454 'Y', pji_tmp.pou_raw_cost,
455 'N', pji_tmp.prj_raw_cost,
456 'A', pji_tmp.pou_raw_cost)),
457 sum(DECODE(c_multi_currency_flag,
458 'Y', pji_tmp.pou_brdn_cost,
459 'N', pji_tmp.prj_brdn_cost,
460 'A', pji_tmp.pou_brdn_cost)),
461 sum(DECODE(c_multi_currency_flag,
462 'Y', pji_tmp.pou_revenue,
463 'N', pji_tmp.prj_revenue,
464 'A', pji_tmp.pou_revenue))
465 FROM pji_fm_xbs_accum_tmp1 pji_tmp,
466 pa_resource_assignments ra
467 WHERE c_version_type = 'COST'
468 AND (
469 (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
470 (NVL(pji_tmp.prj_raw_cost, 0) <> 0) OR --Bug 9666017
471 (NVL(pji_tmp.pou_raw_cost, 0) <> 0) OR --Bug 9666017
472 (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
473 (NVL(pji_tmp.prj_brdn_cost, 0) <> 0) OR --Bug 9666017
474 (NVL(pji_tmp.pou_brdn_cost, 0) <> 0) OR --Bug 9666017
475 (NVL(pji_tmp.quantity,0) <> 0)
476 )
477 AND pji_tmp.source_id = c_res_asg_id
478 AND DECODE(c_multi_currency_flag,'Y',
479 pji_tmp.txn_currency_code,c_txn_currency_code)
480 = c_txn_currency_code
481 AND ra.resource_assignment_id = c_res_asg_id
482 GROUP BY pji_tmp.period_name,
483 nvl(ra.planning_start_date, TRUNC(Sysdate)),
484 nvl(ra.planning_end_date, TRUNC(Sysdate))
485 UNION ALL
486 SELECT pji_tmp.period_name,
487 nvl(ra.planning_start_date, TRUNC(Sysdate)),
488 nvl(ra.planning_end_date, TRUNC(Sysdate)),
489 sum(pji_tmp.quantity),
490 sum(DECODE(c_multi_currency_flag,
491 'Y', pji_tmp.txn_raw_cost,
492 'N', pji_tmp.prj_raw_cost,
493 'A', pji_tmp.pou_raw_cost)),
494 sum(DECODE(c_multi_currency_flag,
495 'Y', pji_tmp.txn_brdn_cost,
496 'N', pji_tmp.prj_brdn_cost,
497 'A', pji_tmp.pou_brdn_cost)),
498 sum(DECODE(c_multi_currency_flag,
499 'Y', pji_tmp.txn_revenue,
500 'N', pji_tmp.prj_revenue,
501 'A', pji_tmp.pou_revenue)),
502 sum(pji_tmp.prj_raw_cost),
503 sum(pji_tmp.prj_brdn_cost),
504 sum(pji_tmp.prj_revenue),
505 sum(DECODE(c_multi_currency_flag,
506 'Y', pji_tmp.pou_raw_cost,
507 'N', pji_tmp.prj_raw_cost,
508 'A', pji_tmp.pou_raw_cost)),
509 sum(DECODE(c_multi_currency_flag,
510 'Y', pji_tmp.pou_brdn_cost,
511 'N', pji_tmp.prj_brdn_cost,
512 'A', pji_tmp.pou_brdn_cost)),
513 sum(DECODE(c_multi_currency_flag,
514 'Y', pji_tmp.pou_revenue,
515 'N', pji_tmp.prj_revenue,
516 'A', pji_tmp.pou_revenue))
517 FROM pji_fm_xbs_accum_tmp1 pji_tmp,
518 pa_resource_assignments ra
519 WHERE c_version_type = 'REVENUE'
520 AND (
521 (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
522 (NVL(pji_tmp.prj_revenue, 0) <> 0) OR --Bug 9666017
523 (NVL(pji_tmp.pou_revenue, 0) <> 0) OR --Bug 9666017
524 (NVL(pji_tmp.quantity,0) <> 0)
525 )
526 AND pji_tmp.source_id = c_res_asg_id
527 AND DECODE(c_multi_currency_flag,'Y',
528 pji_tmp.txn_currency_code,c_txn_currency_code)
529 = c_txn_currency_code
530 AND ra.resource_assignment_id = c_res_asg_id
531 GROUP BY pji_tmp.period_name,
532 nvl(ra.planning_start_date, TRUNC(Sysdate)),
533 nvl(ra.planning_end_date, TRUNC(Sysdate));
534
535 l_ra NUMBER;
536 l_org_id NUMBER;
537 l_set_of_books_id NUMBER;
538 l_rlm_id pa_resource_list_members.resource_list_member_id%TYPE;
539 l_res_asg_id_tab pa_plsql_datatypes.IdTabTyp;
540 l_txn_currency_code_tab pa_plsql_datatypes.Char30TabTyp;
541 l_period_name_tab pa_plsql_datatypes.Char30TabTyp;
542 l_quantity_tab pa_plsql_datatypes.NumTabTyp;
543 l_txn_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
544 l_txn_brdn_cost_tab pa_plsql_datatypes.NumTabTyp;
545 l_txn_revenue_tab pa_plsql_datatypes.NumTabTyp;
546 l_proj_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
547 l_proj_brdn_cost_tab pa_plsql_datatypes.NumTabTyp;
548 l_proj_revenue_tab pa_plsql_datatypes.NumTabTyp;
549 l_pou_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
550 l_pou_brdn_cost_tab pa_plsql_datatypes.NumTabTyp;
551 l_pou_revenue_tab pa_plsql_datatypes.NumTabTyp;
552 l_start_date_tab pa_plsql_datatypes.DateTabTyp;
553 l_end_date_tab pa_plsql_datatypes.DateTabTyp;
554 l_start_date Date;
555 l_end_date Date;
556
557 l_amt_dtls_tbl pa_fp_maintain_actual_pub.l_amt_dtls_tbl_typ;
558 l_wp_version_flag VARCHAR2(1);
559 l_count_no_rlm NUMBER;
560 l_rate_based_flag VARCHAR2(1);
561 l_uncategorized_flag VARCHAR2(1);
562 l_rev_gen_method VARCHAR2(3);
563 l_res_asg_id_tmp_tab pa_plsql_datatypes.IdTabTyp;
564
565 l_plan_class_code PA_FIN_PLAN_TYPES_B.PLAN_CLASS_CODE%TYPE;
566 l_txn_currency_flag VARCHAR2(1) := 'Y';
567 l_fin_plan_type_id PA_PROJ_FP_OPTIONS.fin_plan_type_id%TYPE;
568
569 l_etc_start_date DATE;
570 BEGIN
571 IF P_PA_DEBUG_MODE = 'Y' THEN
572 pa_debug.set_curr_function( p_function => 'COPY_ACTUALS',
573 p_debug_mode => p_pa_debug_mode );
574 END IF;
575
576 x_return_status := FND_API.G_RET_STS_SUCCESS;
577 x_msg_count := 0;
578
579 IF p_init_msg_flag = 'Y' THEN
580 FND_MSG_PUB.initialize;
581 x_msg_count := 0;
582 END IF;
583
584 IF P_PROJECT_ID is null or p_budget_version_id is null THEN
585 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
586 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
587 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
588 END IF;
589
590 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
591 --l_rev_gen_method := PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(p_project_id);
592
593 /* Set the currency flag as follows:
594 l_txn_currency_flag is 'Y' means we use txn_currency_code
595 l_txn_currency_flag is 'N' means we use proj_currency_code
596 l_txn_currency_flag is 'A' means we use projfunc_currency_code
597 */
598
599 -- Bug 7302700 - Moved the condition on x_plan_in_multi_curr_flag before checking if the
600 -- revenue forecast is generated from a forecast plan type with cost accrual method.
601 IF p_fp_cols_rec.x_plan_in_multi_curr_flag = 'N' THEN
602 l_txn_currency_flag := 'N';
603 END IF;
604
605 IF l_rev_gen_method = 'C' AND
606 p_fp_cols_rec.x_version_type = 'REVENUE' AND
607 p_fp_cols_rec.X_GEN_SRC_PLAN_TYPE_ID IS NOT NULL THEN
608
609 SELECT plan_class_code
610 INTO l_plan_class_code
611 FROM pa_fin_plan_types_b
612 WHERE fin_plan_type_id = p_fp_cols_rec.X_GEN_SRC_PLAN_TYPE_ID;
613
614 IF l_plan_class_code = 'FORECAST' THEN
615 l_txn_currency_flag := 'A';
616 END IF;
617
618 END IF;
619
620 l_calendar_type := p_fp_cols_rec.X_TIME_PHASED_CODE;
621
622 l_project_id_tab.extend;
623 l_resource_list_id_tab.extend;
624 l_struct_ver_id_tab.extend;
625 l_calendar_type_tab.extend;
626 l_end_date_pji_tab.extend;
627
628 l_project_id_tab(1) := p_project_id;
629 l_resource_list_id_tab(1) := p_fp_cols_rec.X_RESOURCE_LIST_ID;
630 l_calendar_type_tab(1) := l_calendar_type;
631 l_end_date_pji_tab(1) := p_end_date;
632
633 --Structure version id should be the structure version id of the current published version
634 --for B/F.
635 SELECT wp_version_flag
636 INTO l_wp_version_flag
637 FROM pa_budget_Versions
638 WHERE budget_version_id=P_BUDGET_VERSION_ID;
639
640 IF l_wp_version_flag = 'Y' THEN
641 l_struct_ver_id_tab(1) := p_fp_cols_rec.X_PROJECT_STRUCTURE_VERSION_ID;
642 ELSE
643 l_struct_ver_id_tab(1) := PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(p_project_id => p_project_id );
644 END IF;
645
646 /**l_record_type: XXXX
647 *1st X: 'Y',data will be returned in periods;
648 * 'N',ITD amounts will be returned;
649 *2nd X: 'Y',data will be returned by planning resources at
650 * entered level(periodic/total);
651 *3rd X: 'Y',data is returned by tasks;
652 * 'N',data is returned by project level;
653 *4th X: 'N',amt will be gotten at entered level, no rollup is done.**/
654 IF (l_calendar_type = 'G' OR l_calendar_type = 'P') THEN
655 l_record_type := 'Y';
656 ELSE
657 l_record_type := 'N';
658 END IF;
659 l_record_type := l_record_type||'Y';
660 IF p_fp_cols_rec.X_FIN_PLAN_LEVEL_CODE IN ('L', 'T') THEN
661 l_record_type := l_record_type||'Y';
662 ELSE
663 l_record_type := l_record_type||'N';
664 END IF;
665 l_record_type := l_record_type||'N';
666 IF P_PA_DEBUG_MODE = 'Y' THEN
667 pa_fp_gen_amount_utils.fp_debug
668 (p_msg => 'Before calling pji_fm_xbs_accum_tmp1',
669 p_module_name => l_module_name,
670 p_log_level => 5);
671 END IF;
672 --dbms_output.put_line('Before calling pji api');
673 --Calling PJI API to get table pji_fm_xbs_accum_tmp1 populated
674 --hr_utility.trace_on(null,'mftest');
675 --hr_utility.trace('before entering get_sum');
676 --hr_utility.trace('l_project_id_tab:'||l_project_id_tab(1));
677 --hr_utility.trace('l_resource_list_id_tab:'||l_resource_list_id_tab(1));
678 --hr_utility.trace('l_struct_ver_id_tab:'||l_struct_ver_id_tab(1));
679 --hr_utility.trace('p_end_date:'||p_end_date);
680 --hr_utility.trace('l_calendar_type:'||l_calendar_type);
681 --hr_utility.trace('l_record_type:'||l_record_type);
682 PJI_FM_XBS_ACCUM_UTILS.get_summarized_data(
683 p_project_ids => l_project_id_tab,
684 p_resource_list_ids => l_resource_list_id_tab,
685 p_struct_ver_ids => l_struct_ver_id_tab,
686 --p_start_date => NULL,
687 p_end_date => l_end_date_pji_tab,
688 --p_start_period_name => NULL,
689 --p_end_period_name => NULL,
690 p_calendar_type => l_calendar_type_tab,
691 p_record_type => l_record_type,
692 p_currency_type => 6,
693 x_return_status => x_return_status,
694 x_msg_code => x_msg_data);
695 --dbms_output.put_line('After calling pji api: '||x_return_status);
696 select count(*) into l_count from pji_fm_xbs_accum_tmp1;
697
698
699 --hr_utility.trace('after entering get_sum:'||x_return_status);
700 --delete from get_sum_test;
701 --insert into get_sum_test (select * from pji_fm_xbs_accum_tmp1);
702 IF P_PA_DEBUG_MODE = 'Y' THEN
703 pa_fp_gen_amount_utils.fp_debug
704 (p_msg => 'After calling pji_fm_xbs_accum_tmp1,return status is: '||x_return_status,
705 p_module_name => l_module_name,
706 p_log_level => 5);
707 END IF;
708 --dbms_output.put_line('After calling pji api: '||x_return_status);
709 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
710 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
711 END IF;
712
713 IF l_count = 0 THEN
714 IF P_PA_DEBUG_MODE = 'Y' THEN
715 pa_fp_gen_amount_utils.fp_debug
716 (p_msg => 'no actual data as of '||
717 to_char(p_end_date,'dd-mon-rrrr'),
718 p_module_name => l_module_name,
719 p_log_level => 5);
720 PA_DEBUG.RESET_CURR_FUNCTION;
721 END IF;
722 RETURN;
723 END IF;
724
725 select count(*) into l_count_no_rlm from pji_fm_xbs_accum_tmp1 WHERE
726 res_list_member_id IS NULL;
727
728 IF l_count_no_rlm > 0 THEN
729 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
730 p_msg_name => 'PA_FP_NO_RLM_ID_FOR_ACTUAL');
731 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
732 END IF;
733
734 /* Update rlm_id for all rows in pji_fm_xbs_accum_tmp1 if the resource list
735 * (p_fp_cols_rec.X_RESOURCE_LIST_ID) is None - Uncategorized.
736 * This logic is not handled by the PJI generic resource mapping API. */
737
738 SELECT NVL(uncategorized_flag,'N')
739 INTO l_uncategorized_flag
740 FROM pa_resource_lists_all_bg
741 WHERE resource_list_id = p_fp_cols_rec.X_RESOURCE_LIST_ID;
742
743 IF l_uncategorized_flag = 'Y' THEN
744 l_rlm_id := PA_FP_GEN_AMOUNT_UTILS.GET_RLM_ID (
745 p_project_id => p_project_id,
746 p_resource_list_id => p_fp_cols_rec.X_RESOURCE_LIST_ID,
747 p_resource_class_code => 'FINANCIAL_ELEMENTS' );
748 UPDATE pji_fm_xbs_accum_tmp1
749 SET res_list_member_id = l_rlm_id;
750 END IF;
751
752 /* updating the project element id ( task id ) to NULL
753 when the value is <= 0 for addressing the P1 bug 3841480.
754 Please note that we cannot resolve the issue by populating the NULL value
755 into the tmp table PA_FP_PLANNING_RES_TMP1. Because, the task id value is referred
756 in the pji_fm_xbs_accum_tmp1 table later in the code. */
757
758 update pji_fm_xbs_accum_tmp1 set project_element_id = null
759 where NVL(project_element_id,0) <= 0;
760
761 /**Populating PA_FP_PLANNING_RES_TMP1, call COPY_ACUTALS_PUB.CREATE_RES_ASG to create
762 *missing resource assignment in pa_resource_assignment table. After that, resource_
763 *assignment_id will be populated pa_fp_planning_res_tmp1.
764 **/
765 DELETE FROM PA_FP_PLANNING_RES_TMP1;
766 INSERT INTO PA_FP_PLANNING_RES_TMP1 (
767 TASK_ID,
768 RESOURCE_LIST_MEMBER_ID,
769 RESOURCE_ASSIGNMENT_ID,
770 CBS_ELEMENT_ID --bug#16791711
771 )
772 ( SELECT DISTINCT PROJECT_ELEMENT_ID,
773 RES_LIST_MEMBER_ID,
774 NULL,
775 CBS_ELEMENT_ID --bug#16791711
776 FROM PJI_FM_XBS_ACCUM_TMP1);
777 -- select count(*) into l_count from pa_resource_assignments where
778 -- budget_version_id = p_budget_version_id;
779 --dbms_output.put_line('before calling cre res asg api: res_assign has: '||l_count);
780 IF P_PA_DEBUG_MODE = 'Y' THEN
781 pa_fp_gen_amount_utils.fp_debug
782 (p_msg => 'Before calling pa_fp_copy_actuals_pub.create_res_asg',
783 p_module_name => l_module_name,
784 p_log_level => 5);
785 END IF;
786 PA_FP_COPY_ACTUALS_PUB.CREATE_RES_ASG (
787 P_PROJECT_ID => P_PROJECT_ID,
788 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
789 P_FP_COLS_REC => P_FP_COLS_REC,
790 X_RETURN_STATUS => x_return_status,
791 X_MSG_COUNT => x_msg_count,
792 X_MSG_DATA => x_msg_data );
793 --dbms_output.put_line('Status after calling cre res asg api: '||x_return_status);
794 IF P_PA_DEBUG_MODE = 'Y' THEN
795 pa_fp_gen_amount_utils.fp_debug
796 (p_msg => 'After calling create_res_asg,return status is: '||x_return_status,
797 p_module_name => l_module_name,
798 p_log_level => 5);
799 END IF;
800 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
801 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
802 END IF;
803
804 /**Calling update_res_asg to populate the newly created resource_assignment_id back to
805 *pa_fp_planning_res_tmp1. Then this value needs to populated back to pji_fm_xbs_accum_tmp1
806 **/
807 IF P_PA_DEBUG_MODE = 'Y' THEN
808 pa_fp_gen_amount_utils.fp_debug
809 (p_msg => 'Before calling update_res_asg',
810 p_module_name => l_module_name,
811 p_log_level => 5);
812 END IF;
813 PA_FP_COPY_ACTUALS_PUB.UPDATE_RES_ASG (
814 P_PROJECT_ID => P_PROJECT_ID,
815 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
816 P_FP_COLS_REC => P_FP_COLS_REC,
817 X_RETURN_STATUS => x_return_status,
818 X_MSG_COUNT => x_msg_count,
819 X_MSG_DATA => x_msg_data);
820 --dbms_output.put_line('Status after calling upd res asg api: '||x_return_status);
821 IF P_PA_DEBUG_MODE = 'Y' THEN
822 pa_fp_gen_amount_utils.fp_debug
823 (p_msg => 'After calling update_res_asg,return status is: '||x_return_status,
824 p_module_name => l_module_name,
825 p_log_level => 5);
826 END IF;
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
831 UPDATE PJI_FM_XBS_ACCUM_TMP1 tmp1
832 SET source_id =
833 (SELECT /*+ INDEX(ra,PA_FP_PLANNING_RES_TMP1_N2)*/ resource_assignment_id
834 FROM PA_FP_PLANNING_RES_TMP1 ra
835 WHERE nvl(ra.task_id,0) = nvl(tmp1.project_element_id,0)
836 AND ra.resource_list_member_id = tmp1.res_list_member_id
837 AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
838 );
839 --dbms_output.put_line('No.of rows updated in pji_fm_xbs_accum_tmp1 table: '||sql%rowcount);
840 --dbms_output.put_line('Opening distinct_ra_curr_cursor');
841 OPEN distinct_ra_curr_cursor(l_txn_currency_flag,
842 P_FP_COLS_REC.X_PROJECT_CURRENCY_CODE,
843 P_FP_COLS_REC.X_PROJFUNC_CURRENCY_CODE);
844 FETCH distinct_ra_curr_cursor
845 BULK COLLECT
846 INTO l_res_asg_id_tab,
847 l_txn_currency_code_tab;
848 CLOSE distinct_ra_curr_cursor;
849 --dbms_output.put_line('Closing distinct_ra_curr_cursor');
850 IF l_res_asg_id_tab.count = 0 THEN
851 IF P_PA_DEBUG_MODE = 'Y' THEN
852 PA_DEBUG.RESET_CURR_FUNCTION;
853 END IF;
854 RETURN;
855 END IF;
856
857 IF l_rev_gen_method = 'C' AND
858 p_fp_cols_rec.x_version_type = 'REVENUE' THEN
859
860 SELECT DISTINCT source_id
861 BULK COLLECT
862 INTO l_res_asg_id_tmp_tab
863 FROM pji_fm_xbs_accum_tmp1;
864
865 -- Bug 4170419: Start
866 -- FORALL k IN 1..l_res_asg_id_tmp_tab.count
867 -- UPDATE pa_resource_assignments ra
868 -- SET ra.unit_of_measure = 'DOLLARS',
869 -- ra.rate_based_flag = 'N'
870 -- WHERE ra.resource_assignment_id = l_res_asg_id_tmp_tab(k);
871
872 If p_fp_cols_rec.x_time_phased_code IN ('P','G') then
873 l_etc_start_date := PA_FP_GEN_AMOUNT_UTILS.get_etc_start_date(p_fp_cols_rec.x_budget_version_id);
874
875 FORALL k IN 1..l_res_asg_id_tmp_tab.count
876 UPDATE pa_resource_assignments ra
877 SET ra.unit_of_measure = 'DOLLARS',
878 ra.rate_based_flag = 'N'
879 WHERE ra.resource_assignment_id = l_res_asg_id_tmp_tab(k)
880 AND ( ra.transaction_source_code is not null
881 OR
882 (ra.transaction_source_code is null and NOT exists
883 ( select 1
884 from pa_budget_lines pbl
885 where pbl.resource_assignment_id = ra.resource_assignment_id
886 and pbl.start_date >= l_etc_start_date
887 )
888 )
889 );
890
891 Else
892 FORALL k IN 1..l_res_asg_id_tmp_tab.count
893 UPDATE pa_resource_assignments ra
894 SET ra.unit_of_measure = 'DOLLARS',
895 ra.rate_based_flag = 'N'
896 WHERE ra.resource_assignment_id = l_res_asg_id_tmp_tab(k)
897 AND ( ra.transaction_source_code is not null
898 OR
899 (ra.transaction_source_code is null and NOT exists
900 ( select 1
901 from pa_budget_lines pbl
902 where pbl.resource_assignment_id = ra.resource_assignment_id
903 )
904 )
905 );
906 End If;
907
908 -- Bug 4170419: End
909
910 END IF;
911
912 l_org_id := P_FP_COLS_REC.x_org_id;
913 l_set_of_books_id := P_FP_COLS_REC.x_set_of_books_id;
914 --dbms_output.put_line('l_calendar_type: '||l_calendar_type);
915 FOR i IN 1..l_res_asg_id_tab.count LOOP
916 IF l_calendar_type = 'P' THEN
917 --dbms_output.put_line('Opening budget_line_cursor_pa');
918 OPEN budget_line_cursor_pa(
919 l_txn_currency_flag,
920 l_res_asg_id_tab(i),
921 l_txn_currency_code_tab(i),
922 l_org_id,
923 P_FP_COLS_REC.X_VERSION_TYPE);
924 FETCH budget_line_cursor_pa
925 BULK COLLECT
926 INTO l_period_name_tab,
927 l_start_date_tab,
928 l_end_date_tab,
929 l_quantity_tab,
930 l_txn_raw_cost_tab,
931 l_txn_brdn_cost_tab,
932 l_txn_revenue_tab,
933 l_proj_raw_cost_tab,
934 l_proj_brdn_cost_tab,
935 l_proj_revenue_tab,
936 l_pou_raw_cost_tab,
937 l_pou_brdn_cost_tab,
938 l_pou_revenue_tab;
939 CLOSE budget_line_cursor_pa;
940 --dbms_output.put_line('Closing budget_line_cursor_pa');
941 ELSIF l_calendar_type = 'G' THEN
942 --dbms_output.put_line('Opening budget_line_cursor_gl');
943 OPEN budget_line_cursor_gl(
944 l_txn_currency_flag,
945 l_res_asg_id_tab(i),
946 l_txn_currency_code_tab(i),
947 l_set_of_books_id,
948 P_FP_COLS_REC.X_VERSION_TYPE);
949 FETCH budget_line_cursor_gl
950 BULK COLLECT
951 INTO l_period_name_tab,
952 l_start_date_tab,
953 l_end_date_tab,
954 l_quantity_tab,
955 l_txn_raw_cost_tab,
956 l_txn_brdn_cost_tab,
957 l_txn_revenue_tab,
958 l_proj_raw_cost_tab,
959 l_proj_brdn_cost_tab,
960 l_proj_revenue_tab,
961 l_pou_raw_cost_tab,
962 l_pou_brdn_cost_tab,
963 l_pou_revenue_tab;
964 CLOSE budget_line_cursor_gl;
965 --dbms_output.put_line('Closing budget_line_cursor_gl');
966 ELSE
967 --dbms_output.put_line('Opening budget_line_cursor_np');
968 OPEN budget_line_cursor_np(
969 l_txn_currency_flag,
970 l_res_asg_id_tab(i),
971 l_txn_currency_code_tab(i),
972 P_PROJECT_ID,
973 P_FP_COLS_REC.X_VERSION_TYPE);
974 FETCH budget_line_cursor_np
975 BULK COLLECT
976 INTO l_period_name_tab,
977 l_start_date_tab,
978 l_end_date_tab,
979 l_quantity_tab,
980 l_txn_raw_cost_tab,
981 l_txn_brdn_cost_tab,
982 l_txn_revenue_tab,
983 l_proj_raw_cost_tab,
984 l_proj_brdn_cost_tab,
985 l_proj_revenue_tab,
986 l_pou_raw_cost_tab,
987 l_pou_brdn_cost_tab,
988 l_pou_revenue_tab;
989 CLOSE budget_line_cursor_np;
990 --dbms_output.put_line('Closing budget_line_cursor_np');
991 END IF;
992
993 SELECT rate_based_flag into l_rate_based_flag
994 FROM pa_resource_assignments
995 WHERE resource_assignment_id = l_res_asg_id_tab(i);
996 --dbms_output.put_line('l_rate_based_flag: '||l_rate_based_flag);
997 IF l_rate_based_flag = 'N' THEN
998 IF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
999 l_quantity_tab := l_txn_revenue_tab;
1000 ELSE
1001 l_quantity_tab := l_txn_raw_cost_tab;
1002 END IF;
1003 END IF;
1004
1005
1006 l_amt_dtls_tbl.delete;
1007 FOR j IN 1..l_period_name_tab.count LOOP
1008 l_amt_dtls_tbl(j).period_name := l_period_name_tab(j);
1009 l_amt_dtls_tbl(j).start_date := l_start_date_tab(j);
1010 l_amt_dtls_tbl(j).end_date := l_end_date_tab(j);
1011 l_amt_dtls_tbl(j).quantity := l_quantity_tab(j);
1012 l_amt_dtls_tbl(j).txn_raw_cost := l_txn_raw_cost_tab(j);
1013 l_amt_dtls_tbl(j).txn_burdened_cost := l_txn_brdn_cost_tab(j);
1014 l_amt_dtls_tbl(j).txn_revenue := l_txn_revenue_tab(j);
1015 l_amt_dtls_tbl(j).project_raw_cost := l_proj_raw_cost_tab(j);
1016 l_amt_dtls_tbl(j).project_burdened_cost := l_proj_brdn_cost_tab(j);
1017 l_amt_dtls_tbl(j).project_revenue := l_proj_revenue_tab(j);
1018 l_amt_dtls_tbl(j).project_func_raw_cost := l_pou_raw_cost_tab(j);
1019 l_amt_dtls_tbl(j).project_func_burdened_cost := l_pou_brdn_cost_tab(j);
1020 l_amt_dtls_tbl(j).project_func_revenue := l_pou_revenue_tab(j);
1021 /*For cost version, revenue amounts should be null
1022 For revenue version, cost amounts should be null */
1023 IF p_fp_cols_rec.x_version_type = 'COST' THEN
1024 l_amt_dtls_tbl(j).txn_revenue := null;
1025 l_amt_dtls_tbl(j).project_revenue := null;
1026 l_amt_dtls_tbl(j).project_func_revenue := null;
1027 ELSIF p_fp_cols_rec.x_version_type = 'REVENUE' THEN
1028 l_amt_dtls_tbl(j).txn_raw_cost := null;
1029 l_amt_dtls_tbl(j).txn_burdened_cost := null;
1030 l_amt_dtls_tbl(j).project_raw_cost := null;
1031 l_amt_dtls_tbl(j).project_burdened_cost := null;
1032 l_amt_dtls_tbl(j).project_func_raw_cost := null;
1033 l_amt_dtls_tbl(j).project_func_burdened_cost := null;
1034 END IF;
1035
1036 /* The following logic needs to be handled in Calculate API.
1037 Currently, Calculate API does not handle the NULL qty logic. *.
1038 IF p_fp_cols_rec.x_version_type = 'REVENUE' AND
1039 l_rev_gen_method = 'C' THEN
1040 l_amt_dtls_tbl(j).quantity := null;
1041 END IF; */
1042
1043 END LOOP;
1044
1045 IF P_PA_DEBUG_MODE = 'Y' THEN
1046 pa_fp_gen_amount_utils.fp_debug
1047 (p_msg => 'Before calling PA_FP_MAINTAIN_ACTUAL_PUB.MAINTAIN_ACTUAL_AMT_RA',
1048 p_module_name => l_module_name,
1049 p_log_level => 5);
1050 END IF;
1051 /**Populating target budget lines by summing up the values.
1052 *P_AMT_DTLS_REC_TAB has the amt data for each specific resource_assignment_id
1053 *3.and txn_currency_code**/
1054 --dbms_output.put_line('b4 calling MAINTAIN_ACTUAL_AMT_RA');
1055 PA_FP_MAINTAIN_ACTUAL_PUB.MAINTAIN_ACTUAL_AMT_RA (
1056 P_PROJECT_ID => P_PROJECT_ID,
1057 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
1058 P_RESOURCE_ASSIGNMENT_ID => l_res_asg_id_tab(i),
1059 P_TXN_CURRENCY_CODE => l_txn_currency_code_tab(i),
1060 P_AMT_DTLS_REC_TAB => l_amt_dtls_tbl,
1061 P_CALLING_CONTEXT => 'FP_GEN_FCST_COPY_ACTUAL',
1062 X_RETURN_STATUS => x_return_Status,
1063 X_MSG_COUNT => x_msg_count,
1064 X_MSG_DATA => x_msg_data );
1065 --dbms_output.put_line('Status after calling MAINTAIN_ACTUAL_AMT_RA api: '||x_return_status);
1066 IF P_PA_DEBUG_MODE = 'Y' THEN
1067 pa_fp_gen_amount_utils.fp_debug
1068 (p_msg => 'After calling PA_FP_MAINTAIN_ACTUAL_PUB.MAINTAIN_ACTUAL_AMT_RA,
1069 return status is: '||x_return_status,
1070 p_module_name => l_module_name,
1071 p_log_level => 5);
1072 END IF;
1073 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1074 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1075 END IF;
1076
1077 END LOOP;
1078
1079 /* the planning start date and end date in pa_resource assignments table
1080 * should be synched up with the budget lines after copying the actual
1081 * data for all the planning resources. */
1082 IF p_pa_debug_mode = 'Y' THEN
1083 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1084 ( p_msg => 'Before calling PA_FP_MAINTAIN_ACTUAL_PUB.' ||
1085 'SYNC_UP_PLANNING_DATES',
1086 p_module_name => l_module_name,
1087 p_log_level => 5 );
1088 END IF;
1089 --dbms_output.put_line('b4 calling SYNC_UP_PLANNING_DATES');
1090 PA_FP_MAINTAIN_ACTUAL_PUB.SYNC_UP_PLANNING_DATES
1091 ( P_BUDGET_VERSION_ID => p_budget_version_id,
1092 P_CALLING_CONTEXT => 'COPY_ACTUALS',
1093 X_RETURN_STATUS => x_return_Status,
1094 X_MSG_COUNT => x_msg_count,
1095 X_MSG_DATA => x_msg_data );
1096 --dbms_output.put_line('Status after calling SYNC_UP_PLANNING_DATES api: '||x_return_status);
1097 IF p_pa_debug_mode = 'Y' THEN
1098 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1099 ( p_msg => 'Aft calling PA_FP_MAINTAIN_ACTUAL_PUB.' ||
1100 'SYNC_UP_PLANNING_DATES return status ' ||
1101 x_return_status,
1102 p_module_name => l_module_name,
1103 p_log_level => 5 );
1104 END IF;
1105 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1106 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1107 END IF;
1108
1109 -- IPM: New Entity ER ------------------------------------------
1110 -- Actual amounts must be rolled up for non-timephased versions
1111 -- before the Calculate API is called since actuals and planned
1112 -- amounts exist in the same budget line in this case.
1113 IF p_fp_cols_rec.x_time_phased_code = 'N' THEN
1114
1115 DELETE pa_resource_asgn_curr_tmp;
1116
1117 FORALL i IN 1..l_res_asg_id_tab.count
1118 INSERT INTO pa_resource_asgn_curr_tmp (
1119 resource_assignment_id,
1120 txn_currency_code )
1121 VALUES (
1122 l_res_asg_id_tab(i),
1123 l_txn_currency_code_tab(i) );
1124
1125 UPDATE pa_resource_asgn_curr_tmp tmp
1126 SET ( txn_raw_cost_rate_override,
1127 txn_burden_cost_rate_override,
1128 txn_bill_rate_override ) =
1129 ( SELECT rbc.txn_raw_cost_rate_override,
1130 rbc.txn_burden_cost_rate_override,
1131 rbc.txn_bill_rate_override
1132 FROM pa_resource_asgn_curr rbc
1133 WHERE tmp.resource_assignment_id = rbc.resource_assignment_id
1134 AND tmp.txn_currency_code = rbc.txn_currency_code );
1135
1136 -- Call the maintenance api in ROLLUP mode
1137 IF p_pa_debug_mode = 'Y' THEN
1138 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1139 P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.' ||
1140 'MAINTAIN_DATA',
1141 --P_CALLED_MODE => p_called_mode,
1142 P_MODULE_NAME => l_module_name);
1143 END IF;
1144 PA_RES_ASG_CURRENCY_PUB.MAINTAIN_DATA
1145 ( P_FP_COLS_REC => p_fp_cols_rec,
1146 P_CALLING_MODULE => 'FORECAST_GENERATION',
1147 P_VERSION_LEVEL_FLAG => 'N',
1148 P_ROLLUP_FLAG => 'Y',
1149 --P_CALLED_MODE => p_called_mode,
1150 X_RETURN_STATUS => x_return_status,
1151
1152 X_MSG_COUNT => x_msg_count,
1153 X_MSG_DATA => x_msg_data );
1154 IF p_pa_debug_mode = 'Y' THEN
1155 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
1156 P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.' ||
1157 'MAINTAIN_DATA: '||x_return_status,
1158 --P_CALLED_MODE => p_called_mode,
1159 P_MODULE_NAME => l_module_name);
1160 END IF;
1161 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1162 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1163 END IF;
1164
1165 END IF; -- IF p_fp_cols_rec.x_time_phased_code = 'N' THEN
1166 -- END OF IPM: New Entity ER ------------------------------------------
1167
1168 IF P_COMMIT_FLAG = 'Y' THEN
1169 COMMIT;
1170 END IF;
1171 IF P_PA_DEBUG_MODE = 'Y' THEN
1172 PA_DEBUG.RESET_CURR_FUNCTION;
1173 END IF;
1174 EXCEPTION
1175 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1176 l_msg_count := FND_MSG_PUB.count_msg;
1177 IF l_msg_count = 1 THEN
1178 PA_INTERFACE_UTILS_PUB.get_messages
1179 ( p_encoded => FND_API.G_TRUE,
1180 p_msg_index => 1,
1181 p_msg_count => l_msg_count,
1182 p_msg_data => l_msg_data,
1183 p_data => l_data,
1184 p_msg_index_out => l_msg_index_out);
1185 x_msg_data := l_data;
1186 x_msg_count := l_msg_count;
1187 ELSE
1188 x_msg_count := l_msg_count;
1189 END IF;
1190
1191 ROLLBACK;
1192
1193 x_return_status := FND_API.G_RET_STS_ERROR;
1194 IF P_PA_DEBUG_MODE = 'Y' THEN
1195 pa_fp_gen_amount_utils.fp_debug
1196 (p_msg => 'Invalid Arguments Passed',
1197 p_module_name => l_module_name,
1198 p_log_level => 5);
1199 PA_DEBUG.RESET_CURR_FUNCTION;
1200 END IF;
1201 RAISE;
1202 WHEN OTHERS THEN
1203 rollback;
1204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1205 x_msg_count := 1;
1206 x_msg_data := substr(sqlerrm,1,240);
1207 -- dbms_output.put_line('error msg :'||x_msg_data);
1208 FND_MSG_PUB.add_exc_msg
1209 ( p_pkg_name => 'PA_FP_COPY_ACTUALS_PUB',
1210 p_procedure_name => 'COPY_ACTUALS',
1211 p_error_text => substr(sqlerrm,1,240));
1212
1213 IF P_PA_DEBUG_MODE = 'Y' THEN
1214 pa_fp_gen_amount_utils.fp_debug
1215 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1216 p_module_name => l_module_name,
1217 p_log_level => 5);
1218 PA_DEBUG.RESET_CURR_FUNCTION;
1219 END IF;
1220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1221
1222 END COPY_ACTUALS;
1223
1224
1225 PROCEDURE CREATE_RES_ASG (
1226 P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
1227 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
1228 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
1229 P_CALLING_PROCESS IN VARCHAR2,
1230 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1231 X_MSG_COUNT OUT NOCOPY NUMBER,
1232 X_MSG_DATA OUT NOCOPY VARCHAR2)
1233 IS
1234 l_module_name VARCHAR2(100) := 'pa.plsql.pa_fp_copy_actuals_pub.create_res_asg';
1235
1236 l_fp_cols_rec PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
1237 l_res_plan_level VARCHAR2(15);
1238
1239 CURSOR project_res_asg_cur
1240 ( p_proj_start_date DATE,
1241 p_proj_completion_date DATE,
1242 c_gen_etc_source_code VARCHAR2 ) IS
1243 SELECT distinct nvl(tmp1.task_id,0),
1244 tmp1.resource_list_member_id,
1245 tmp1.CBS_ELEMENT_ID, --bug#16791711
1246 DECODE(p_calling_process, 'COPY_ACTUALS',
1247 p_proj_start_date,
1248 tmp1.planning_start_date),
1249 DECODE(p_calling_process, 'COPY_ACTUALS',
1250 p_proj_completion_date,
1251 tmp1.planning_end_date),
1252 NVL(c_gen_etc_source_code, NULL)
1253 FROM PA_FP_PLANNING_RES_TMP1 tmp1
1254 WHERE nvl(tmp1.task_id,0) = 0
1255 AND NOT EXISTS (
1256 SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N2)*/ 1
1257 FROM pa_resource_assignments ra
1258 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1259 AND NVL(ra.task_id,0) = 0
1260 AND ra.resource_list_member_id = tmp1.resource_list_member_id
1261 AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
1262 );
1263
1264 CURSOR lowestTask_res_asg_cur
1265 ( p_proj_start_date DATE,
1266 p_proj_completion_date DATE,
1267 c_gen_etc_source_code VARCHAR2 ) IS
1268 SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N1)*/
1269 distinct tmp1.task_id,
1270 tmp1.resource_list_member_id,
1271 tmp1.CBS_ELEMENT_ID, --bug#16791711
1272 DECODE(p_calling_process, 'COPY_ACTUALS',
1273 NVL(task.start_date, p_proj_start_date),
1274 tmp1.planning_start_date),
1275 DECODE(p_calling_process, 'COPY_ACTUALS',
1276 NVL(task.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.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
1283 WHERE nvl(tmp1.task_id,0) > 0
1284 AND tmp1.task_id = task.task_id
1285 AND NOT EXISTS (
1286 SELECT /*+ INDEX(tmp1,PA_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
1287 FROM pa_resource_assignments ra
1288 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1289 AND nvl(ra.task_id,0) = nvl(tmp1.task_id,0)
1290 AND ra.resource_list_member_id = tmp1.resource_list_member_id
1291 AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
1292 )
1293 UNION
1294 SELECT distinct nvl(tmp1.task_id,0),
1295 tmp1.resource_list_member_id,
1296 tmp1.CBS_ELEMENT_ID, --bug#16791711
1297 DECODE(p_calling_process, 'COPY_ACTUALS',
1298 p_proj_start_date,
1299 tmp1.planning_start_date),
1300 DECODE(p_calling_process, 'COPY_ACTUALS',
1301 p_proj_completion_date,
1302 tmp1.planning_end_date),
1303 NVL(c_gen_etc_source_code, NULL)
1304 FROM PA_FP_PLANNING_RES_TMP1 tmp1
1305 WHERE nvl(tmp1.task_id,0) = 0
1306 AND NOT EXISTS (
1307 SELECT /*+ INDEX(tmp1,PA_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
1308 FROM pa_resource_assignments ra
1309 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1310 AND NVL(ra.task_id,0) = 0
1311 AND ra.resource_list_member_id = tmp1.resource_list_member_id
1312 AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
1313 );
1314
1315 CURSOR topTask_res_asg_cur
1316 ( p_proj_start_date DATE,
1317 p_proj_completion_date DATE,
1318 c_gen_etc_source_code VARCHAR2 ) IS
1319 SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N1)*/
1320 task_t.task_id,
1321 tmp1.resource_list_member_id,
1322 tmp1.CBS_ELEMENT_ID, --bug#16791711
1323 MIN(DECODE(p_calling_process, 'COPY_ACTUALS',
1324 NVL(task_t.start_date, p_proj_start_date),
1325 tmp1.planning_start_date)),
1326 MAX(DECODE(p_calling_process, 'COPY_ACTUALS',
1327 NVL(task_t.completion_date, p_proj_completion_date),
1328 tmp1.planning_end_date)),
1329 NVL(c_gen_etc_source_code,
1330 DECODE(p_calling_process, 'COPY_ACTUALS', NULL,task_t.GEN_ETC_SOURCE_CODE)) -- Bug 4193368 for staffing plan src should not be
1331 -- based on task's etc source
1332 FROM PA_FP_PLANNING_RES_TMP1 tmp1,
1333 pa_tasks task, pa_tasks task_t
1334 WHERE nvl(tmp1.task_id,0) > 0
1335 AND tmp1.task_id = task.task_id
1336 AND task.top_task_id = task_t.task_id
1337 AND NOT EXISTS (
1338 SELECT /*+ INDEX(tmp1,PA_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
1339 FROM pa_resource_assignments ra
1340 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1341 AND nvl(ra.task_id,0) = task_t.task_id
1342 AND ra.resource_list_member_id = tmp1.resource_list_member_id
1343 AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
1344 )
1345 GROUP BY task_t.task_id,
1346 tmp1.resource_list_member_id,
1347 tmp1.CBS_ELEMENT_ID,--bug#16791711
1348 NVL(c_gen_etc_source_code,
1349 DECODE(p_calling_process, 'COPY_ACTUALS', NULL,task_t.GEN_ETC_SOURCE_CODE)) -- Bug 4193368 for staffing plan src should not be
1350 -- based on task's etc source
1351 UNION
1352 SELECT nvl(tmp1.task_id,0),
1353 tmp1.resource_list_member_id,
1354 tmp1.CBS_ELEMENT_ID, --bug#16791711
1355 MIN(DECODE(p_calling_process, 'COPY_ACTUALS',
1356 p_proj_start_date,
1357 tmp1.planning_start_date)),
1358 MAX(DECODE(p_calling_process, 'COPY_ACTUALS',
1359 p_proj_completion_date,
1360 tmp1.planning_end_date)),
1361 NVL(c_gen_etc_source_code, NULL)
1362 FROM PA_FP_PLANNING_RES_TMP1 tmp1
1363 WHERE nvl(tmp1.task_id,0) = 0
1364 AND NOT EXISTS (
1365 SELECT /*+ INDEX(tmp1,PA_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
1366 FROM pa_resource_assignments ra
1367 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1368 AND NVL(ra.task_id,0) = 0
1369 AND ra.resource_list_member_id = tmp1.resource_list_member_id
1370 AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
1371 )
1372 GROUP BY nvl(tmp1.task_id,0),
1373 tmp1.resource_list_member_id,
1374 tmp1.CBS_ELEMENT_ID,--bug#16791711
1375 NVL(c_gen_etc_source_code, NULL);
1376
1377 -- bug#16200605
1378 CURSOR lowestTask_res_asg_cur_cbs
1379 ( p_proj_start_date DATE,
1380 p_proj_completion_date DATE,
1381 c_gen_etc_source_code VARCHAR2 ) IS
1382 SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N1)*/
1383 distinct tmp1.task_id,
1384 tmp1.resource_list_member_id,
1385 tmp1.CBS_ELEMENT_ID,
1386 DECODE(p_calling_process, 'COPY_ACTUALS',
1387 NVL(task.start_date, p_proj_start_date),
1388 tmp1.planning_start_date),
1389 DECODE(p_calling_process, 'COPY_ACTUALS',
1390 NVL(task.completion_date, p_proj_completion_date),
1391 tmp1.planning_end_date),
1392 NVL(c_gen_etc_source_code,
1393 DECODE(p_calling_process, 'COPY_ACTUALS', NULL,task.GEN_ETC_SOURCE_CODE)) -- Bug 4193368 for staffing plan src should not be
1394 -- based on task's etc source
1395 FROM (SELECT tmp.MAPPED_FIN_TASK_ID AS TASK_ID,
1396 tmp.TARGET_RLM_ID AS RESOURCE_LIST_MEMBER_ID,
1397 PRA.CBS_ELEMENT_ID AS CBS_ELEMENT_ID,
1398 min(tmp.planning_start_date) AS planning_start_date,
1399 max(tmp.planning_end_date) AS planning_end_date
1400 FROM PA_FP_CALC_AMT_TMP1 tmp,
1401 PA_RESOURCE_ASSIGNMENTS PRA
1402 WHERE tmp.RESOURCE_ASSIGNMENT_ID = PRA.RESOURCE_ASSIGNMENT_ID
1403 GROUP BY mapped_fin_task_id,TARGET_RLM_ID,PRA.CBS_ELEMENT_ID) tmp1,
1404 pa_tasks task
1405 WHERE nvl(tmp1.task_id,0) > 0
1406 AND tmp1.task_id = task.task_id
1407 AND NOT EXISTS (
1408 SELECT /*+ INDEX(tmp1,PA_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
1409 FROM pa_resource_assignments ra
1410 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1411 AND nvl(ra.task_id,0) = nvl(tmp1.task_id,0)
1412 AND ra.resource_list_member_id = tmp1.resource_list_member_id
1413 AND ra.CBS_ELEMENT_ID = tmp1.CBS_ELEMENT_ID);
1414
1415 CURSOR topTask_res_asg_cur_cbs
1416 ( p_proj_start_date DATE,
1417 p_proj_completion_date DATE,
1418 c_gen_etc_source_code VARCHAR2 ) IS
1419 SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N1)*/
1420 task_t.task_id,
1421 tmp1.resource_list_member_id,
1422 tmp1.CBS_ELEMENT_ID,
1423 MIN(DECODE(p_calling_process, 'COPY_ACTUALS',
1424 NVL(task_t.start_date, p_proj_start_date),
1425 tmp1.planning_start_date)),
1426 MAX(DECODE(p_calling_process, 'COPY_ACTUALS',
1427 NVL(task_t.completion_date, p_proj_completion_date),
1428 tmp1.planning_end_date)),
1429 NVL(c_gen_etc_source_code,
1430 DECODE(p_calling_process, 'COPY_ACTUALS', NULL,task_t.GEN_ETC_SOURCE_CODE)) -- Bug 4193368 for staffing plan src should not be
1431 -- based on task's etc source
1432 FROM (SELECT tmp.MAPPED_FIN_TASK_ID AS TASK_ID,
1433 tmp.TARGET_RLM_ID AS RESOURCE_LIST_MEMBER_ID,
1434 PRA.CBS_ELEMENT_ID AS CBS_ELEMENT_ID,
1435 min(tmp.planning_start_date) AS planning_start_date,
1436 max(tmp.planning_end_date) AS planning_end_date
1437 FROM PA_FP_CALC_AMT_TMP1 tmp,
1438 PA_RESOURCE_ASSIGNMENTS PRA
1439 WHERE tmp.RESOURCE_ASSIGNMENT_ID = PRA.RESOURCE_ASSIGNMENT_ID
1440 GROUP BY mapped_fin_task_id,TARGET_RLM_ID,PRA.CBS_ELEMENT_ID) tmp1,
1441 pa_tasks task, pa_tasks task_t
1442 WHERE nvl(tmp1.task_id,0) > 0
1443 AND tmp1.task_id = task.task_id
1444 AND task.top_task_id = task_t.task_id
1445 AND NOT EXISTS (
1446 SELECT /*+ INDEX(tmp1,PA_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
1447 FROM pa_resource_assignments ra
1448 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1449 AND nvl(ra.task_id,0) = task_t.task_id
1450 AND ra.resource_list_member_id = tmp1.resource_list_member_id
1451 AND ra.CBS_ELEMENT_ID = tmp1.CBS_ELEMENT_ID)
1452 GROUP BY task_t.task_id,
1453 tmp1.resource_list_member_id,
1454 tmp1.CBS_ELEMENT_ID,
1455 NVL(c_gen_etc_source_code,
1456 DECODE(p_calling_process, 'COPY_ACTUALS', NULL,task_t.GEN_ETC_SOURCE_CODE)) -- Bug 4193368 for staffing plan src should not be
1457 -- based on task's etc source
1458 ;
1459
1460 l_task_id_tab pa_plsql_datatypes.IdTabTyp;
1461 l_rlm_id_tab pa_plsql_datatypes.IdTabTyp;
1462 l_start_date_tab pa_plsql_datatypes.DateTabTyp;
1463 l_completion_date_tab pa_plsql_datatypes.DateTabTyp;
1464 l_etc_src_code_tab pa_plsql_datatypes.Char30TabTyp;
1465 l_proj_start_date DATE;
1466 l_proj_completion_date DATE;
1467
1468 l_gen_etc_source_code_override VARCHAR2(30);
1469
1470 l_count NUMBER;
1471 l_msg_count NUMBER;
1472 l_data VARCHAR2(1000);
1473 l_msg_data VARCHAR2(1000);
1474 l_msg_index_out NUMBER;
1475 l_spread_curve_id pa_spread_curves_b.spread_curve_id%TYPE;
1476
1477 /* Variables added to replace literals in INSERT stmts. */
1478 l_project_as_id_minus1 NUMBER:=-1;
1479 l_res_as_type_USER_ENTERED VARCHAR2(30):='USER_ENTERED';
1480 l_rec_ver_number_1 NUMBER:=1;
1481
1482 l_proj_struct_sharing_code PA_PROJECTS_ALL.STRUCTURE_SHARING_CODE%TYPE;
1483
1484 l_Cbs_Enabled VARCHAR2(1) := 'N'; -- bug#16200605
1485 l_cbs_ele_ids PA_PLSQL_DATATYPES.IdTabTyp; -- bug#16200605
1486 l_cbs_id_tab pa_plsql_datatypes.IdTabTyp; --bug#16791711
1487 BEGIN
1488 IF P_PA_DEBUG_MODE = 'Y' THEN
1489 pa_debug.set_curr_function( p_function => 'CREATE_RES_ASG',
1490 p_debug_mode => p_pa_debug_mode );
1491 END IF;
1492
1493 x_return_status := FND_API.G_RET_STS_SUCCESS;
1494 x_msg_count := 0;
1495
1496 l_Cbs_Enabled := PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(p_Project_Id => P_PROJECT_ID); -- bug#16200605
1497
1498 IF (P_FP_COLS_REC.X_BUDGET_VERSION_ID IS NULL) THEN
1499 IF P_PA_DEBUG_MODE = 'Y' THEN
1500 pa_fp_gen_amount_utils.fp_debug
1501 (p_msg => 'Before calling PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTL',
1502 p_module_name => l_module_name,
1503 p_log_level => 5);
1504 END IF;
1505 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS(
1506 P_PROJECT_ID => P_PROJECT_ID,
1507 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
1508 X_FP_COLS_REC => l_fp_cols_rec,
1509 X_RETURN_STATUS => x_return_status,
1510 X_MSG_COUNT => x_msg_count,
1511 X_MSG_DATA => x_msg_data);
1512 IF P_PA_DEBUG_MODE = 'Y' THEN
1513 pa_fp_gen_amount_utils.fp_debug
1514 (p_msg => 'After calling PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS,
1515 return status:'||x_return_status,
1516 p_module_name => l_module_name,
1517 p_log_level => 5);
1518 END IF;
1519 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1520 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1521 END IF;
1522 ELSE
1523 l_fp_cols_rec := P_FP_COLS_REC;
1524 END IF;
1525 l_res_plan_level := l_fp_cols_rec.X_FIN_PLAN_LEVEL_CODE;
1526
1527 SELECT NVL(start_date,trunc(sysdate)),
1528 NVL(completion_date,trunc(sysdate))
1529 INTO l_proj_start_date, l_proj_completion_date
1530 FROM pa_projects_all
1531 WHERE project_id = P_PROJECT_ID;
1532
1533 /* When the Target is a Revenue-only version, we need to take the target
1534 * version's ETC source code instead of the task-level source code for
1535 * the target resources that we are processing. */
1536 IF l_fp_cols_rec.x_version_type = 'REVENUE' THEN
1537 l_gen_etc_source_code_override := l_fp_cols_rec.x_gen_etc_src_code;
1538 ELSE
1539 l_gen_etc_source_code_override := NULL;
1540 END IF;
1541
1542 l_proj_struct_sharing_code := NVL(pa_project_structure_utils.
1543 get_structure_sharing_code(P_PROJECT_ID), 'SHARE_FULL');
1544
1545 -- Bug 4174997: If the calling process is COPY_ACTUALS then we should
1546 -- go with the Target version's planning level, since we always have
1547 -- financial tasks for actuals.
1548
1549 -- Bug 4232094: When the structure is 'SPLIT_NO_MAPPING', the only
1550 -- scenario in which we need to use the project-level cursor is when
1551 -- the Target version is Revenue and the Source version is Workplan.
1552 -- In all other scenarios, we should go with the Target version's
1553 -- planning level. This change overrides Bug fix 4174997.
1554
1555 --IF (true or l_Cbs_Enabled = 'N' OR p_calling_process = 'COPY_ACTUALS' ) THEN -- bug#16200605
1556 IF (l_res_plan_level = 'P' OR
1557 (l_proj_struct_sharing_code = 'SPLIT_NO_MAPPING' AND
1558 l_fp_cols_rec.x_version_type = 'REVENUE' AND
1559 l_fp_cols_rec.x_gen_etc_src_code = 'WORKPLAN_RESOURCES')) THEN
1560 OPEN project_res_asg_cur
1561 ( l_proj_start_date,
1562 l_proj_completion_date,
1563 l_gen_etc_source_code_override );
1564 FETCH project_res_asg_cur
1565 BULK COLLECT
1566 INTO l_task_id_tab,
1567 l_rlm_id_tab,
1568 l_cbs_id_tab,--bug#16791711
1569 l_start_date_tab,
1570 l_completion_date_tab,
1571 l_etc_src_code_tab;
1572 CLOSE project_res_asg_cur;
1573 ELSIF (l_res_plan_level = 'L') THEN
1574 -- hr_utility.trace('in create res asg low task fetch '||l_task_id_tab.count);
1575 OPEN lowestTask_res_asg_cur
1576 ( l_proj_start_date,
1577 l_proj_completion_date,
1578 l_gen_etc_source_code_override );
1579 FETCH lowestTask_res_asg_cur
1580 BULK COLLECT
1581 INTO l_task_id_tab,
1582 l_rlm_id_tab,
1583 l_cbs_id_tab,--bug#16791711
1584 l_start_date_tab,
1585 l_completion_date_tab,
1586 l_etc_src_code_tab;
1587 CLOSE lowestTask_res_asg_cur;
1588 ELSIF (l_res_plan_level = 'T') THEN
1589 OPEN topTask_res_asg_cur
1590 ( l_proj_start_date,
1591 l_proj_completion_date,
1592 l_gen_etc_source_code_override );
1593 FETCH topTask_res_asg_cur
1594 BULK COLLECT
1595 INTO l_task_id_tab,
1596 l_rlm_id_tab,
1597 l_cbs_id_tab,--bug#16791711
1598 l_start_date_tab,
1599 l_completion_date_tab,
1600 l_etc_src_code_tab;
1601 CLOSE topTask_res_asg_cur;
1602 END IF;
1603 -- hr_utility.trace('in create res asg tab count '||l_task_id_tab.count);
1604 IF (l_task_id_tab.count = 0 ) THEN
1605 IF P_PA_DEBUG_MODE = 'Y' THEN
1606 PA_DEBUG.RESET_CURR_FUNCTION;
1607 END IF;
1608 RETURN;
1609 END IF;
1610
1611 FORALL i in l_task_id_tab.first .. l_task_id_tab.last
1612 INSERT INTO PA_RESOURCE_ASSIGNMENTS (
1613 RESOURCE_ASSIGNMENT_ID,
1614 BUDGET_VERSION_ID,
1615 PROJECT_ID,
1616 RESOURCE_LIST_MEMBER_ID,
1617 CBS_ELEMENT_ID, --bug#16791711
1618 TASK_ID,
1619 LAST_UPDATE_DATE,
1620 LAST_UPDATED_BY,
1621 CREATION_DATE,
1622 CREATED_BY,
1623 LAST_UPDATE_LOGIN,
1624 PROJECT_ASSIGNMENT_ID,
1625 PLANNING_START_DATE,
1626 PLANNING_END_DATE,
1627 RESOURCE_ASSIGNMENT_TYPE,
1628 RECORD_VERSION_NUMBER,
1629 TRANSACTION_SOURCE_CODE )
1630 VALUES (
1631 pa_resource_assignments_s.nextval,
1632 p_budget_version_id,
1633 p_project_id,
1634 l_rlm_id_tab(i),
1635 l_cbs_id_tab(i),--bug#16791711
1636 l_task_id_tab(i),
1637 sysdate,
1638 FND_GLOBAL.USER_ID,
1639 sysdate,
1640 FND_GLOBAL.USER_ID,
1641 FND_GLOBAL.LOGIN_ID,
1642 l_project_as_id_minus1,
1643 l_start_date_tab(i),
1644 l_completion_date_tab(i),
1645 l_res_as_type_USER_ENTERED,
1646 l_rec_ver_number_1,
1647 l_etc_src_code_tab(i)
1648 );
1649
1650 /*ELSE -- Else condition l_Cbs_Enabled = 'Y' -- bug#16200605
1651
1652 IF (l_res_plan_level = 'L') THEN
1653 -- hr_utility.trace('in create res asg low task fetch '||l_task_id_tab.count);
1654 OPEN lowestTask_res_asg_cur_cbs
1655 ( l_proj_start_date,
1656 l_proj_completion_date,
1657 l_gen_etc_source_code_override );
1658 FETCH lowestTask_res_asg_cur_cbs
1659 BULK COLLECT
1660 INTO l_task_id_tab,
1661 l_rlm_id_tab,
1662 l_cbs_ele_ids,
1663 l_start_date_tab,
1664 l_completion_date_tab,
1665 l_etc_src_code_tab;
1666 CLOSE lowestTask_res_asg_cur_cbs;
1667 ELSIF (l_res_plan_level = 'T') THEN
1668 OPEN topTask_res_asg_cur_cbs
1669 ( l_proj_start_date,
1670 l_proj_completion_date,
1671 l_gen_etc_source_code_override );
1672 FETCH topTask_res_asg_cur_cbs
1673 BULK COLLECT
1674 INTO l_task_id_tab,
1675 l_rlm_id_tab,
1676 l_cbs_ele_ids,
1677 l_start_date_tab,
1678 l_completion_date_tab,
1679 l_etc_src_code_tab;
1680 CLOSE topTask_res_asg_cur_cbs;
1681 END IF;
1682 -- hr_utility.trace('in create res asg tab count '||l_task_id_tab.count);
1683 IF (l_task_id_tab.count = 0 ) THEN
1684 IF P_PA_DEBUG_MODE = 'Y' THEN
1685 PA_DEBUG.RESET_CURR_FUNCTION;
1686 END IF;
1687 RETURN;
1688 END IF;
1689
1690 FORALL i in l_task_id_tab.first .. l_task_id_tab.last
1691 INSERT INTO PA_RESOURCE_ASSIGNMENTS (
1692 RESOURCE_ASSIGNMENT_ID,
1693 BUDGET_VERSION_ID,
1694 PROJECT_ID,
1695 RESOURCE_LIST_MEMBER_ID,
1696 CBS_ELEMENT_ID,
1697 TASK_ID,
1698 LAST_UPDATE_DATE,
1699 LAST_UPDATED_BY,
1700 CREATION_DATE,
1701 CREATED_BY,
1702 LAST_UPDATE_LOGIN,
1703 PROJECT_ASSIGNMENT_ID,
1704 PLANNING_START_DATE,
1705 PLANNING_END_DATE,
1706 RESOURCE_ASSIGNMENT_TYPE,
1707 RECORD_VERSION_NUMBER,
1708 TRANSACTION_SOURCE_CODE )
1709 VALUES (
1710 pa_resource_assignments_s.nextval,
1711 p_budget_version_id,
1712 p_project_id,
1713 l_rlm_id_tab(i),
1714 l_cbs_ele_ids(i),
1715 l_task_id_tab(i),
1716 sysdate,
1717 FND_GLOBAL.USER_ID,
1718 sysdate,
1719 FND_GLOBAL.USER_ID,
1720 FND_GLOBAL.LOGIN_ID,
1721 l_project_as_id_minus1,
1722 l_start_date_tab(i),
1723 l_completion_date_tab(i),
1724 l_res_as_type_USER_ENTERED,
1725 l_rec_ver_number_1,
1726 l_etc_src_code_tab(i)
1727 );
1728 END IF;
1729 */
1730
1731 IF P_PA_DEBUG_MODE = 'Y' THEN
1732 pa_fp_gen_amount_utils.fp_debug
1733 (p_msg => 'Before calling update_res_defaults',
1734 p_module_name => l_module_name,
1735 p_log_level => 5);
1736 END IF;
1737 PA_FP_GEN_PUB.UPDATE_RES_DEFAULTS
1738 (P_PROJECT_ID => P_PROJECT_ID,
1739 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
1740 X_RETURN_STATUS => x_return_status,
1741 X_MSG_COUNT => x_msg_count,
1742 X_MSG_DATA => x_msg_data );
1743 IF P_PA_DEBUG_MODE = 'Y' THEN
1744 pa_fp_gen_amount_utils.fp_debug
1745 (p_msg => 'Before calling update_res_defaults',
1746 p_module_name => l_module_name,
1747 p_log_level => 5);
1748 END IF;
1749 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1750 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1751 END IF;
1752
1753 SELECT spread_curve_id
1754 INTO l_spread_curve_id
1755 FROM pa_spread_curves_b
1756 WHERE spread_curve_code = 'FIXED_DATE';
1757
1758 UPDATE PA_RESOURCE_ASSIGNMENTS
1759 SET SP_FIXED_DATE = PLANNING_START_DATE
1760 WHERE SP_FIXED_DATE IS NULL
1761 AND SPREAD_CURVE_ID = l_spread_curve_id
1762 AND BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
1763
1764 IF P_PA_DEBUG_MODE = 'Y' THEN
1765 PA_DEBUG.RESET_CURR_FUNCTION;
1766 END IF;
1767 EXCEPTION
1768 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1769 l_msg_count := FND_MSG_PUB.count_msg;
1770 IF l_msg_count = 1 THEN
1771 PA_INTERFACE_UTILS_PUB.get_messages
1772 ( p_encoded => FND_API.G_TRUE,
1773 p_msg_index => 1,
1774 p_msg_count => l_msg_count,
1775 p_msg_data => l_msg_data,
1776 p_data => l_data,
1777 p_msg_index_out => l_msg_index_out);
1778 x_msg_data := l_data;
1779 x_msg_count := l_msg_count;
1780 ELSE
1781 x_msg_count := l_msg_count;
1782 END IF;
1783
1784 ROLLBACK;
1785
1786 x_return_status := FND_API.G_RET_STS_ERROR;
1787 IF P_PA_DEBUG_MODE = 'Y' THEN
1788 pa_fp_gen_amount_utils.fp_debug
1789 (p_msg => 'Invalid Arguments Passed',
1790 p_module_name => l_module_name,
1791 p_log_level => 5);
1792 PA_DEBUG.RESET_CURR_FUNCTION;
1793 END IF;
1794 RAISE;
1795 WHEN OTHERS THEN
1796 rollback;
1797 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1798 x_msg_count := 1;
1799 x_msg_data := substr(sqlerrm,1,240);
1800 -- dbms_output.put_line('error msg :'||x_msg_data);
1801 FND_MSG_PUB.add_exc_msg
1802 ( p_pkg_name => 'PA_FP_COPY_ACTUALS_PUB',
1803 p_procedure_name => 'CREATE_RES_ASG',
1804 p_error_text => substr(sqlerrm,1,240));
1805
1806 IF P_PA_DEBUG_MODE = 'Y' THEN
1807 pa_fp_gen_amount_utils.fp_debug
1808 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1809 p_module_name => l_module_name,
1810 p_log_level => 5);
1811 PA_DEBUG.RESET_CURR_FUNCTION;
1812 END IF;
1813 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1814 END CREATE_RES_ASG;
1815
1816
1817 PROCEDURE UPDATE_RES_ASG (
1818 P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
1819 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
1820 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
1821 P_CALLING_PROCESS IN VARCHAR2,
1822 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1823 X_MSG_COUNT OUT NOCOPY NUMBER,
1824 X_MSG_DATA OUT NOCOPY VARCHAR2)
1825 IS
1826 l_module_name VARCHAR2(100) := 'pa.plsql.pa_fp_copy_actuals_pub.update_res_asg';
1827 l_res_plan_level VARCHAR2(15);
1828
1829 l_count NUMBER;
1830 l_msg_count NUMBER;
1831 l_data VARCHAR2(1000);
1832 l_msg_data VARCHAR2(1000);
1833 l_msg_index_out NUMBER;
1834
1835 l_proj_struct_sharing_code PA_PROJECTS_ALL.STRUCTURE_SHARING_CODE%TYPE;
1836 BEGIN
1837 IF P_PA_DEBUG_MODE = 'Y' THEN
1838 pa_debug.set_curr_function( p_function => 'UPDATE_RES_ASG',
1839 p_debug_mode => p_pa_debug_mode );
1840 END IF;
1841
1842 x_return_status := FND_API.G_RET_STS_SUCCESS;
1843 x_msg_count := 0;
1844 l_res_plan_level := p_fp_cols_rec.X_FIN_PLAN_LEVEL_CODE;
1845
1846 l_proj_struct_sharing_code := NVL(pa_project_structure_utils.
1847 get_structure_sharing_code(P_PROJECT_ID),'SHARE_FULL');
1848
1849 -- Bug 4174997: If the calling process is COPY_ACTUALS then we should
1850 -- go with the Target version's planning level, since we always have
1851 -- financial tasks for actuals.
1852
1853 -- Bug 4232094: When the structure is 'SPLIT_NO_MAPPING', the only
1854 -- scenario in which we need to use the project-level cursor is when
1855 -- the Target version is Revenue and the Source version is Workplan.
1856 -- In all other scenarios, we should go with the Target version's
1857 -- planning level. This change overrides Bug fix 4174997.
1858
1859 IF (l_res_plan_level = 'P' OR
1860 (l_proj_struct_sharing_code = 'SPLIT_NO_MAPPING' AND
1861 p_fp_cols_rec.x_version_type = 'REVENUE' AND
1862 p_fp_cols_rec.x_gen_etc_src_code = 'WORKPLAN_RESOURCES')) THEN
1863
1864 UPDATE PA_FP_PLANNING_RES_TMP1 tmp1
1865 SET resource_assignment_id =
1866 (SELECT resource_assignment_id
1867 FROM pa_resource_assignments ra
1868 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1869 AND ra.project_id = P_PROJECT_ID
1870 AND nvl(ra.task_id,0) = 0
1871 AND ra.resource_list_member_id = tmp1.resource_list_member_id
1872 AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
1873 );
1874
1875 ELSIF l_res_plan_level = 'L' THEN
1876
1877 UPDATE PA_FP_PLANNING_RES_TMP1 tmp1
1878 SET resource_assignment_id =
1879 (SELECT resource_assignment_id
1880 FROM pa_resource_assignments ra
1881 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1882 AND ra.project_id = P_PROJECT_ID
1883 AND ra.task_id = tmp1.task_id
1884 AND ra.resource_list_member_id = tmp1.resource_list_member_id
1885 AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
1886 )
1887 WHERE tmp1.task_id is NOT NULL
1888 AND tmp1.task_id > 0;
1889
1890 UPDATE PA_FP_PLANNING_RES_TMP1 tmp1
1891 SET resource_assignment_id =
1892 (SELECT resource_assignment_id
1893 FROM pa_resource_assignments ra
1894 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1895 AND ra.project_id = P_PROJECT_ID
1896 AND nvl(ra.task_id,0) = 0
1897 AND ra.resource_list_member_id = tmp1.resource_list_member_id
1898 AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
1899 )
1900 WHERE nvl(tmp1.task_id,0) = 0;
1901
1902 ELSIF l_res_plan_level = 'T' THEN
1903
1904 UPDATE PA_FP_PLANNING_RES_TMP1 tmp1
1905 SET resource_assignment_id =
1906 (SELECT resource_assignment_id
1907 FROM pa_resource_assignments ra,
1908 pa_tasks t
1909 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1910 AND ra.project_id = P_PROJECT_ID
1911 AND tmp1.task_id = t.task_id
1912 AND t.top_task_id = ra.task_id
1913 AND ra.resource_list_member_id = tmp1.resource_list_member_id
1914 AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
1915 )
1916 WHERE tmp1.task_id is NOT NULL
1917 AND tmp1.task_id > 0;
1918
1919 UPDATE PA_FP_PLANNING_RES_TMP1 tmp1
1920 SET resource_assignment_id =
1921 (SELECT resource_assignment_id
1922 FROM pa_resource_assignments ra
1923 WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1924 AND ra.project_id = P_PROJECT_ID
1925 AND nvl(ra.task_id,0) = 0
1926 AND ra.resource_list_member_id = tmp1.resource_list_member_id
1927 AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
1928 )
1929 WHERE nvl(tmp1.task_id,0) = 0;
1930
1931 END IF;
1932
1933 IF P_PA_DEBUG_MODE = 'Y' THEN
1934 PA_DEBUG.RESET_CURR_FUNCTION;
1935 END IF;
1936 EXCEPTION
1937 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1938 l_msg_count := FND_MSG_PUB.count_msg;
1939 IF l_msg_count = 1 THEN
1940 PA_INTERFACE_UTILS_PUB.get_messages
1941 ( p_encoded => FND_API.G_TRUE,
1942 p_msg_index => 1,
1943 p_msg_count => l_msg_count,
1944 p_msg_data => l_msg_data,
1945 p_data => l_data,
1946 p_msg_index_out => l_msg_index_out);
1947 x_msg_data := l_data;
1948 x_msg_count := l_msg_count;
1949 ELSE
1950 x_msg_count := l_msg_count;
1951 END IF;
1952
1953 ROLLBACK;
1954
1955 x_return_status := FND_API.G_RET_STS_ERROR;
1956
1957 IF P_PA_DEBUG_MODE = 'Y' THEN
1958 pa_fp_gen_amount_utils.fp_debug
1959 (p_msg => 'Invalid Arguments Passed',
1960 p_module_name => l_module_name,
1961 p_log_level => 5);
1962 PA_DEBUG.RESET_CURR_FUNCTION;
1963 END IF;
1964 RAISE;
1965 WHEN OTHERS THEN
1966 rollback;
1967 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1968 x_msg_count := 1;
1969 x_msg_data := substr(sqlerrm,1,240);
1970 -- dbms_output.put_line('error msg :'||x_msg_data);
1971 FND_MSG_PUB.add_exc_msg
1972 ( p_pkg_name => 'PA_FP_COPY_ACTUALS_PUB',
1973 p_procedure_name => 'UPDATE_RES_ASG',
1974 p_error_text => substr(sqlerrm,1,240));
1975
1976 IF P_PA_DEBUG_MODE = 'Y' THEN
1977 pa_fp_gen_amount_utils.fp_debug
1978 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1979 p_module_name => l_module_name,
1980 p_log_level => 5);
1981 PA_DEBUG.RESET_CURR_FUNCTION;
1982 END IF;
1983 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1984 END UPDATE_RES_ASG;
1985
1986 /**
1987 * gboomina added this method for AAI requirement
1988 * This procedure is called to collect actuals for a selected resource assignments or
1989 * for a whole budget version given.
1990 **/
1991 PROCEDURE COLLECT_ACTUALS
1992 (P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
1993 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
1994 P_RESOURCE_ASSGN_IDS IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
1995 P_INIT_MSG_FLAG IN VARCHAR2 default 'Y',
1996 P_COMMIT_FLAG IN VARCHAR2 default 'N',
1997 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1998 X_MSG_COUNT OUT NOCOPY NUMBER,
1999 X_MSG_DATA OUT NOCOPY VARCHAR2)
2000 IS
2001 l_module_name VARCHAR2(100) := 'pa.plsql.pa_fp_copy_actuals_pub.collect_actuals';
2002 l_msg_count NUMBER;
2003 l_msg_data VARCHAR2(2000);
2004 l_data VARCHAR2(2000);
2005 l_msg_index_out NUMBER:=0;
2006
2007 l_fp_cols_rec PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
2008
2009 l_actuals_through_date DATE;
2010 l_resource_assgn_id_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2011 l_txn_currency_code_tab pa_plsql_datatypes.Char30TabTyp;
2012 l_del_resource_assgn_id_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2013 l_project_id_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
2014 l_resource_list_id_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
2015 l_struct_ver_id_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
2016 l_calendar_type_tab SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type();
2017 l_end_date_pji_tab SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
2018 l_calendar_type VARCHAR2(15);
2019 l_record_type VARCHAR2(15);
2020 l_uncategorized_flag VARCHAR2(1);
2021 i NUMBER;
2022 l_count NUMBER := 1;
2023 l_found BOOLEAN := FALSE;
2024 l_count_no_rlm NUMBER;
2025 l_txn_currency_flag VARCHAR2(1) := 'Y';
2026 l_org_id NUMBER;
2027 l_rate_based_flag VARCHAR2(1);
2028 l_budget_line_exists varchar2(1) := 'N';
2029 l_record_version_number pa_budget_versions.record_version_number%type;
2030 l_wp_version_flag pa_budget_versions.wp_version_flag%type;
2031 l_rlm_id pa_resource_list_members.resource_list_member_id%TYPE;
2032
2033 l_last_updated_by NUMBER := FND_GLOBAL.user_id;
2034 l_last_update_login NUMBER := FND_GLOBAL.login_id;
2035 l_sysdate DATE := SYSDATE;
2036
2037 l_period_name_tab pa_plsql_datatypes.Char30TabTyp;
2038 l_quantity_tab pa_plsql_datatypes.NumTabTyp;
2039 l_txn_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
2040 l_txn_brdn_cost_tab pa_plsql_datatypes.NumTabTyp;
2041 l_txn_revenue_tab pa_plsql_datatypes.NumTabTyp;
2042 l_proj_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
2043 l_proj_brdn_cost_tab pa_plsql_datatypes.NumTabTyp;
2044 l_proj_revenue_tab pa_plsql_datatypes.NumTabTyp;
2045 l_pou_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
2046 l_pou_brdn_cost_tab pa_plsql_datatypes.NumTabTyp;
2047 l_pou_revenue_tab pa_plsql_datatypes.NumTabTyp;
2048 l_start_date_tab pa_plsql_datatypes.DateTabTyp;
2049 l_end_date_tab pa_plsql_datatypes.DateTabTyp;
2050
2051 l_amt_dtls_tbl pa_fp_maintain_actual_pub.l_amt_dtls_tbl_typ;
2052
2053 -- Cursor to get 'Copy ETC from Plan' flag
2054 CURSOR get_copy_etc_from_plan_csr
2055 IS
2056 SELECT COPY_ETC_FROM_PLAN_FLAG
2057 FROM PA_PROJ_FP_OPTIONS
2058 WHERE FIN_PLAN_VERSION_ID = P_BUDGET_VERSION_ID;
2059 l_copy_etc_from_plan_flag PA_PROJ_FP_OPTIONS.COPY_ETC_FROM_PLAN_FLAG%TYPE;
2060
2061 -- Cursor to get resource assignment ids for a budget version
2062 CURSOR get_resource_assgn_ids_csr IS
2063 SELECT RESOURCE_ASSIGNMENT_ID
2064 FROM PA_RESOURCE_ASSIGNMENTS
2065 WHERE BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
2066 get_resource_assgn_ids_rec get_resource_assgn_ids_csr%rowtype;
2067
2068 -- Cursor to get budget line information for a resource assignment id
2069 cursor get_period_info_csr
2070 (p_resource_assignment_id pa_resource_assignments.RESOURCE_ASSIGNMENT_ID%type)
2071 is
2072 select tmp.source_id
2073 ,tmp.txn_currency_code
2074 ,tmp.period_name
2075 FROM pji_fm_xbs_accum_tmp1 tmp
2076 WHERE tmp.source_id = p_resource_assignment_id;
2077 get_period_info_rec get_period_info_csr%rowtype;
2078
2079 -- Get distinct resource assignment id and txn currency code from temp table
2080 CURSOR distinct_ra_curr_cursor (c_multi_currency_flag VARCHAR2,
2081 c_proj_currency_code VARCHAR2,
2082 c_projfunc_currency_code VARCHAR2) IS
2083 SELECT distinct pji_tmp.source_id,
2084 DECODE(c_multi_currency_flag,
2085 'Y', pji_tmp.txn_currency_code,
2086 'N', c_proj_currency_code,
2087 'A', c_projfunc_currency_code)
2088 FROM pji_fm_xbs_accum_tmp1 pji_tmp
2089 WHERE pji_tmp.source_id is NOT NULL --bug#8485646
2090 order by 1,2;
2091
2092 -- Cursor to check whether budget line already exists for the period
2093 cursor budget_line_info_csr
2094 (p_resource_assignment_id pa_budget_lines.resource_assignment_id%type,
2095 p_period_name pa_budget_lines.period_name%type,
2096 p_txn_currency_code pa_budget_lines.txn_currency_code%type)
2097 is
2098 SELECT bl.budget_line_id
2099 ,bl.resource_assignment_id
2100 ,bl.txn_currency_code
2101 ,bl.start_date
2102 ,bl.end_date
2103 ,bl.period_name
2104 ,bl.quantity
2105 ,bl.txn_raw_cost
2106 ,bl.txn_burdened_cost
2107 ,bl.txn_revenue
2108 ,bl.project_raw_cost
2109 ,bl.project_burdened_cost
2110 ,bl.project_revenue
2111 ,bl.raw_cost projfunc_raw_cost
2112 ,bl.burdened_cost projfunc_burdened_cost
2113 ,bl.revenue projfunc_revenue
2114 ,bl.project_currency_code
2115 ,bl.projfunc_currency_code
2116 ,bl.cost_rejection_code
2117 ,bl.revenue_rejection_code
2118 ,bl.burden_rejection_code
2119 ,bl.pfc_cur_conv_rejection_code
2120 ,bl.pc_cur_conv_rejection_code
2121 FROM pa_budget_lines bl
2122 WHERE bl.resource_assignment_id = p_resource_assignment_id
2123 AND bl.period_name = NVL(p_period_name,bl.period_name)
2124 AND bl.txn_currency_code = p_txn_currency_code;
2125
2126 budget_line_rec budget_line_info_csr%rowtype;
2127
2128 -- Cursor to get budget line information if calender type is 'PA Period'
2129 CURSOR budget_line_cursor_pa(c_multi_currency_flag VARCHAR2,
2130 c_res_asg_id NUMBER,
2131 c_txn_currency_code VARCHAR2,
2132 c_org_id NUMBER,
2133 c_version_type VARCHAR2) IS
2134 SELECT pji_tmp.period_name,
2135 pd.start_date,
2136 pd.end_date,
2137 sum(pji_tmp.quantity),
2138 sum(DECODE(c_multi_currency_flag,
2139 'Y', pji_tmp.txn_raw_cost,
2140 'N', pji_tmp.prj_raw_cost,
2141 'A', pji_tmp.pou_raw_cost)),
2142 sum(DECODE(c_multi_currency_flag,
2143 'Y', pji_tmp.txn_brdn_cost,
2144 'N', pji_tmp.prj_brdn_cost,
2145 'A', pji_tmp.pou_brdn_cost)),
2146 sum(DECODE(c_multi_currency_flag,
2147 'Y', pji_tmp.txn_revenue,
2148 'N', pji_tmp.prj_revenue,
2149 'A', pji_tmp.pou_revenue)),
2150 sum(pji_tmp.prj_raw_cost),
2151 sum(pji_tmp.prj_brdn_cost),
2152 sum(pji_tmp.prj_revenue),
2153 sum(DECODE(c_multi_currency_flag,
2154 'Y', pji_tmp.pou_raw_cost,
2155 'N', pji_tmp.prj_raw_cost,
2156 'A', pji_tmp.pou_raw_cost)),
2157 sum(DECODE(c_multi_currency_flag,
2158 'Y', pji_tmp.pou_brdn_cost,
2159 'N', pji_tmp.prj_brdn_cost,
2160 'A', pji_tmp.pou_brdn_cost)),
2161 sum(DECODE(c_multi_currency_flag,
2162 'Y', pji_tmp.pou_revenue,
2163 'N', pji_tmp.prj_revenue,
2164 'A', pji_tmp.pou_revenue))
2165 FROM pji_fm_xbs_accum_tmp1 pji_tmp,pa_periods_all pd
2166 WHERE c_version_type = 'ALL'
2167 AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
2168 (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
2169 (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
2170 (NVL(pji_tmp.quantity,0) <> 0)
2171 )
2172 AND pd.org_id = c_org_id
2173 AND pd.period_name = pji_tmp.period_name
2174 AND pji_tmp.source_id = c_res_asg_id
2175 AND DECODE(c_multi_currency_flag,'Y',
2176 pji_tmp.txn_currency_code,c_txn_currency_code)
2177 = c_txn_currency_code
2178 GROUP BY pji_tmp.period_name,
2179 pd.start_date,
2180 pd.end_date
2181 UNION ALL
2182 SELECT pji_tmp.period_name,
2183 pd.start_date,
2184 pd.end_date,
2185 sum(pji_tmp.quantity),
2186 sum(DECODE(c_multi_currency_flag,
2187 'Y', pji_tmp.txn_raw_cost,
2188 'N', pji_tmp.prj_raw_cost,
2189 'A', pji_tmp.pou_raw_cost)),
2190 sum(DECODE(c_multi_currency_flag,
2191 'Y', pji_tmp.txn_brdn_cost,
2192 'N', pji_tmp.prj_brdn_cost,
2193 'A', pji_tmp.pou_brdn_cost)),
2194 sum(DECODE(c_multi_currency_flag,
2195 'Y', pji_tmp.txn_revenue,
2196 'N', pji_tmp.prj_revenue,
2197 'A', pji_tmp.pou_revenue)),
2198 sum(pji_tmp.prj_raw_cost),
2199 sum(pji_tmp.prj_brdn_cost),
2200 sum(pji_tmp.prj_revenue),
2201 sum(DECODE(c_multi_currency_flag,
2202 'Y', pji_tmp.pou_raw_cost,
2203 'N', pji_tmp.prj_raw_cost,
2204 'A', pji_tmp.pou_raw_cost)),
2205 sum(DECODE(c_multi_currency_flag,
2206 'Y', pji_tmp.pou_brdn_cost,
2207 'N', pji_tmp.prj_brdn_cost,
2208 'A', pji_tmp.pou_brdn_cost)),
2209 sum(DECODE(c_multi_currency_flag,
2210 'Y', pji_tmp.pou_revenue,
2211 'N', pji_tmp.prj_revenue,
2212 'A', pji_tmp.pou_revenue))
2213 FROM pji_fm_xbs_accum_tmp1 pji_tmp,pa_periods_all pd
2214 WHERE c_version_type = 'COST'
2215 AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
2216 (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
2217 (NVL(pji_tmp.quantity,0) <> 0)
2218 )
2219 AND pd.org_id = c_org_id
2220 AND pd.period_name = pji_tmp.period_name
2221 AND pji_tmp.source_id = c_res_asg_id
2222 AND DECODE(c_multi_currency_flag,'Y',
2223 pji_tmp.txn_currency_code,c_txn_currency_code)
2224 = c_txn_currency_code
2225 GROUP BY pji_tmp.period_name,
2226 pd.start_date,
2227 pd.end_date;
2228
2229 -- Cursor to get budget line information when the calender
2230 -- type is 'GL Period'
2231 CURSOR budget_line_cursor_gl(c_source_id NUMBER,
2232 c_multi_currency_flag VARCHAR2,
2233 c_set_of_books_id NUMBER,
2234 c_version_type VARCHAR2,
2235 c_proj_currency_code VARCHAR2,
2236 c_projfunc_currency_code VARCHAR2) IS
2237 select * from
2238 (SELECT pji_tmp.source_id,
2239 DECODE(c_multi_currency_flag,
2240 'Y', pji_tmp.txn_currency_code,
2241 'N', c_proj_currency_code,
2242 'A', c_projfunc_currency_code) txn_currency_code,
2243 pji_tmp.period_name,
2244 gd.start_date,
2245 gd.end_date,
2246 sum(pji_tmp.quantity)quantity,
2247 sum(DECODE(c_multi_currency_flag,
2248 'Y', pji_tmp.txn_raw_cost,
2249 'N', pji_tmp.prj_raw_cost,
2250 'A', pji_tmp.pou_raw_cost)) txn_raw_cost,
2251 sum(DECODE(c_multi_currency_flag,
2252 'Y', pji_tmp.txn_brdn_cost,
2253 'N', pji_tmp.prj_brdn_cost,
2254 'A', pji_tmp.pou_brdn_cost)) txn_brdn_cost,
2255 sum(DECODE(c_multi_currency_flag,
2256 'Y', pji_tmp.txn_revenue,
2257 'N', pji_tmp.prj_revenue,
2258 'A', pji_tmp.pou_revenue)) txn_revenue,
2259 sum(pji_tmp.prj_raw_cost) prj_raw_cost,
2260 sum(pji_tmp.prj_brdn_cost)prj_brdn_cost,
2261 sum(pji_tmp.prj_revenue) prj_revenue,
2262 sum(DECODE(c_multi_currency_flag,
2263 'Y', pji_tmp.pou_raw_cost,
2264 'N', pji_tmp.prj_raw_cost,
2265 'A', pji_tmp.pou_raw_cost)) pou_raw_cost,
2266 sum(DECODE(c_multi_currency_flag,
2267 'Y', pji_tmp.pou_brdn_cost,
2268 'N', pji_tmp.prj_brdn_cost,
2269 'A', pji_tmp.pou_brdn_cost)) pou_brdn_cost,
2270 sum(DECODE(c_multi_currency_flag,
2271 'Y', pji_tmp.pou_revenue,
2272 'N', pji_tmp.prj_revenue,
2273 'A', pji_tmp.pou_revenue)) pou_revenue
2274 FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
2275 WHERE c_version_type = 'ALL'
2276 AND pji_tmp.source_id = c_source_id
2277 AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
2278 (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
2279 (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
2280 (NVL(pji_tmp.quantity,0) <> 0)
2281 )
2282 AND gd.SET_OF_BOOKS_ID = c_set_of_books_id
2283 AND gd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
2284 AND gd.ADJUSTMENT_PERIOD_FLAG = 'N'
2285 AND gd.period_name = pji_tmp.period_name
2286 GROUP BY pji_tmp.source_id,
2287 DECODE(c_multi_currency_flag,
2288 'Y', pji_tmp.txn_currency_code,
2289 'N', c_proj_currency_code,
2290 'A', c_projfunc_currency_code),
2291 pji_tmp.period_name,
2292 gd.start_date,
2293 gd.end_date
2294 UNION ALL
2295 SELECT pji_tmp.source_id,
2296 DECODE(c_multi_currency_flag,
2297 'Y', pji_tmp.txn_currency_code,
2298 'N', c_proj_currency_code,
2299 'A', c_projfunc_currency_code) txn_currency_code,
2300 pji_tmp.period_name,
2301 gd.start_date,
2302 gd.end_date,
2303 sum(pji_tmp.quantity) quantity,
2304 sum(DECODE(c_multi_currency_flag,
2305 'Y', pji_tmp.txn_raw_cost,
2306 'N', pji_tmp.prj_raw_cost,
2307 'A', pji_tmp.pou_raw_cost)) txn_raw_cost,
2308 sum(DECODE(c_multi_currency_flag,
2309 'Y', pji_tmp.txn_brdn_cost,
2310 'N', pji_tmp.prj_brdn_cost,
2311 'A', pji_tmp.pou_brdn_cost)) txn_brdn_cost,
2312 sum(DECODE(c_multi_currency_flag,
2313 'Y', pji_tmp.txn_revenue,
2314 'N', pji_tmp.prj_revenue,
2315 'A', pji_tmp.pou_revenue)) txn_revenue,
2316 sum(pji_tmp.prj_raw_cost) prj_raw_cost,
2317 sum(pji_tmp.prj_brdn_cost)prj_brdn_cost,
2318 sum(pji_tmp.prj_revenue) prj_revenue,
2319 sum(DECODE(c_multi_currency_flag,
2320 'Y', pji_tmp.pou_raw_cost,
2321 'N', pji_tmp.prj_raw_cost,
2322 'A', pji_tmp.pou_raw_cost)) pou_raw_cost,
2323 sum(DECODE(c_multi_currency_flag,
2324 'Y', pji_tmp.pou_brdn_cost,
2325 'N', pji_tmp.prj_brdn_cost,
2326 'A', pji_tmp.pou_brdn_cost)) pou_brdn_cost,
2327 sum(DECODE(c_multi_currency_flag,
2328 'Y', pji_tmp.pou_revenue,
2329 'N', pji_tmp.prj_revenue,
2330 'A', pji_tmp.pou_revenue)) pou_revenue
2331 FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
2332 WHERE c_version_type = 'COST'
2333 AND pji_tmp.source_id = c_source_id
2334 AND (
2335 (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
2336 (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
2337 (NVL(pji_tmp.quantity,0) <> 0)
2338 )
2339 AND gd.SET_OF_BOOKS_ID = c_set_of_books_id
2340 AND gd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
2341 AND gd.ADJUSTMENT_PERIOD_FLAG = 'N'
2342 AND gd.period_name = pji_tmp.period_name
2343 GROUP BY pji_tmp.source_id,
2344 DECODE(c_multi_currency_flag,
2345 'Y', pji_tmp.txn_currency_code,
2346 'N', c_proj_currency_code,
2347 'A', c_projfunc_currency_code),
2348 pji_tmp.period_name,
2349 gd.start_date,
2350 gd.end_date)
2351 order by source_id, txn_currency_code;
2352
2353 l_budget_line_gl_rec budget_line_cursor_gl%rowtype;
2354
2355 -- Cursor to get GL period start date and end date
2356 cursor gl_period_start_end_dates_csr(
2357 p_period_name gl_period_statuses.period_name%type,
2358 p_set_of_books_id gl_period_statuses.set_of_books_id%type)
2359 is
2360 select start_date, end_date
2361 from gl_period_statuses
2362 where period_name = p_period_name
2363 and set_of_books_id = p_set_of_books_id;
2364
2365 -- Cursor to get PA period start date and end date
2366 cursor pa_period_start_end_dates_csr(
2367 p_period_name pa_periods_all.period_name%type,
2368 p_ord_id pa_periods_all.org_id%type)
2369 is
2370 select start_date, end_date
2371 from pa_periods_all
2372 where period_name = p_period_name
2373 and org_id = p_ord_id;
2374
2375 l_start_date DATE;
2376 l_end_date DATE;
2377 BEGIN
2378 /* Initialization */
2379 FND_MSG_PUB.initialize;
2380 X_MSG_COUNT := 0;
2381 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2382
2383 IF p_pa_debug_mode = 'Y' THEN
2384 pa_debug.set_curr_function( p_function => 'COLLECT_ACTUALS'
2385 ,p_debug_mode => p_pa_debug_mode);
2386 END IF;
2387
2388 /* Begining of acquiring lock */
2389 -- acquire version lock
2390 select record_version_number
2391 into l_record_version_number
2392 from pa_budget_versions
2393 where budget_version_id = p_budget_version_id;
2394 IF p_pa_debug_mode = 'Y' THEN
2395 pa_fp_gen_amount_utils.fp_debug
2396 (p_msg => 'Before calling pa_fin_plan_pvt.lock_unlock_version',
2397 p_module_name => l_module_name);
2398 END IF;
2399 pa_fin_plan_pvt.lock_unlock_version(
2400 p_budget_version_id => p_budget_version_id,
2401 p_record_version_number => l_record_version_number,
2402 p_action => 'L',
2403 p_user_id => FND_GLOBAL.USER_ID,
2404 p_person_id => NULL,
2405 x_return_status => x_return_status,
2406 x_msg_count => x_msg_count,
2407 x_msg_data => x_msg_data);
2408 IF p_pa_debug_mode = 'Y' THEN
2409 pa_fp_gen_amount_utils.fp_debug
2410 (p_msg => 'Status after calling pa_fin_plan_pvt.lock_unlock_version:'
2411 ||x_return_status,
2412 p_module_name => l_module_name);
2413 END IF;
2414 if x_return_status <> fnd_api.g_ret_sts_success then
2415 IF p_pa_debug_mode = 'Y' THEN
2416 pa_debug.reset_curr_function;
2417 END IF;
2418 RETURN;
2419 END IF;
2420
2421 /* we need to commit the changes so that the locked by person info
2422 will be available for other sessions. */
2423 COMMIT;
2424
2425 --acquire lock for collect_actual
2426 -- using copy_actuals api to acquire lock for collect_acutuals
2427 -- as the underlying table are same in both cases.
2428 IF p_pa_debug_mode = 'Y' THEN
2429 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2430 P_MSG => 'Before calling PA_FP_COPY_FROM_PKG.'
2431 ||'ACQUIRE_LOCKS_FOR_COPY_ACTUAL',
2432 P_MODULE_NAME => l_module_name);
2433 END IF;
2434
2435 PA_FP_COPY_FROM_PKG.ACQUIRE_LOCKS_FOR_COPY_ACTUAL
2436 (P_PLAN_VERSION_ID => P_BUDGET_VERSION_ID,
2437 X_RETURN_STATUS => X_RETURN_STATUS,
2438 X_MSG_COUNT => X_MSG_COUNT,
2439 X_MSG_DATA => X_MSG_DATA);
2440 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2441 --If can't acquire lock, customized message is thrown from within
2442 -- the API, so we should suppress exception error
2443 IF p_pa_debug_mode = 'Y' THEN
2444 PA_DEBUG.Reset_Curr_Function;
2445 END IF;
2446 RETURN;
2447 END IF;
2448
2449 IF p_pa_debug_mode = 'Y' THEN
2450 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2451 P_MSG => 'After calling PA_FP_COPY_FROM_PKG.'
2452 ||'ACQUIRE_LOCKS_FOR_COPY_ACTUAL: '
2453 ||x_return_status,
2454 P_MODULE_NAME => l_module_name);
2455 END IF;
2456
2457 --delete temp table used for reporting purpose
2458 delete from PJI_FM_EXTR_PLAN_LINES;
2459 /* End of acquiring lock */
2460
2461 /* Validation - Begin */
2462 -- Validate Input parameters
2463 if P_PROJECT_ID is null or p_budget_version_id is null then
2464 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2465 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
2466 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2467 end if;
2468 /* Validation - End */
2469
2470 /* Calling utility api to get plan version details - Begin */
2471 if p_pa_debug_mode = 'Y' then
2472 pa_fp_gen_amount_utils.fp_debug
2473 (p_msg => 'Before calling
2474 pa_fp_gen_amount_utils.get_plan_version_dtls(p_budget_version_id)',
2475 p_module_name => l_module_name,
2476 p_log_level => 5);
2477 end if;
2478 pa_fp_gen_amount_utils.get_plan_version_dtls
2479 (p_budget_version_id => p_budget_version_id,
2480 x_fp_cols_rec => l_fp_cols_rec,
2481 x_return_status => x_return_status,
2482 x_msg_count => x_msg_count,
2483 x_msg_data => x_msg_data);
2484 if x_return_status <> fnd_api.g_ret_sts_success then
2485 raise pa_fp_constants_pkg.invalid_arg_exc;
2486 end if;
2487 if p_pa_debug_mode = 'Y' then
2488 pa_fp_gen_amount_utils.fp_debug
2489 (p_msg => 'Status after calling
2490 pa_fp_gen_amount_utils.get_plan_version_dtls(p_budget_version_id): '
2491 ||x_return_status,
2492 p_module_name => l_module_name,
2493 p_log_level => 5);
2494 end if;
2495 /* Calling utility api to get plan version details - End */
2496
2497 /*---------------------------------------------------------------
2498 Populate actual amounts in PJI_FM_XBS_ACCUM_TMP1 table - Begin
2499 ---------------------------------------------------------------*/
2500 -- get actual thru date
2501 l_actuals_through_date := to_date(PA_FP_GEN_FCST_PG_PKG.GET_ACTUALS_THRU_PERIOD_DTLS(p_budget_version_id, 'END_DATE'),'rrrrmmdd');
2502 l_calendar_type := l_fp_cols_rec.X_TIME_PHASED_CODE;
2503
2504 IF l_fp_cols_rec.x_plan_in_multi_curr_flag = 'N' THEN
2505 l_txn_currency_flag := 'N';
2506 END IF;
2507
2508 l_project_id_tab.extend;
2509 l_resource_list_id_tab.extend;
2510 l_struct_ver_id_tab.extend;
2511 l_calendar_type_tab.extend;
2512 l_end_date_pji_tab.extend;
2513
2514 l_project_id_tab(1) := p_project_id;
2515 l_resource_list_id_tab(1) := l_fp_cols_rec.X_RESOURCE_LIST_ID;
2516 l_calendar_type_tab(1) := l_calendar_type;
2517 l_end_date_pji_tab(1) := l_actuals_through_date;
2518
2519 --Structure version id should be the structure version id of the
2520 --current published version for B/F.
2521 select wp_version_flag
2522 into l_wp_version_flag
2523 from pa_budget_versions
2524 where budget_version_id=p_budget_version_id;
2525
2526 if l_wp_version_flag = 'Y' then
2527 l_struct_ver_id_tab(1) := l_fp_cols_rec.x_project_structure_version_id;
2528 else
2529 l_struct_ver_id_tab(1) := pa_project_structure_utils.get_fin_struc_ver_id(p_project_id => p_project_id );
2530 end if;
2531
2532 /**l_record_type: XXXX
2533 *1st X: 'Y',data will be returned in periods;
2534 * 'N',ITD amounts will be returned;
2535 *2nd X: 'Y',data will be returned by planning resources at
2536 * entered level(periodic/total);
2537 *3rd X: 'Y',data is returned by tasks;
2538 * 'N',data is returned by project level;
2539 *4th X: 'N',amt will be gotten at entered level, no rollup is done.**/
2540 IF (l_calendar_type = 'G' OR l_calendar_type = 'P') THEN
2541 l_record_type := 'Y';
2542 ELSE
2543 l_record_type := 'N';
2544 END IF;
2545 l_record_type := l_record_type||'Y';
2546 IF l_fp_cols_rec.X_FIN_PLAN_LEVEL_CODE IN ('L', 'T') THEN
2547 l_record_type := l_record_type||'Y';
2548 ELSE
2549 l_record_type := l_record_type||'N';
2550 END IF;
2551 l_record_type := l_record_type||'N';
2552
2553 IF P_PA_DEBUG_MODE = 'Y' THEN
2554 pa_fp_gen_amount_utils.fp_debug
2555 (p_msg => 'Before calling pji_fm_xbs_accum_tmp1',
2556 p_module_name => l_module_name,
2557 p_log_level => 5);
2558 END IF;
2559
2560 PJI_FM_XBS_ACCUM_UTILS.get_summarized_data(
2561 p_project_ids => l_project_id_tab,
2562 p_resource_list_ids => l_resource_list_id_tab,
2563 p_struct_ver_ids => l_struct_ver_id_tab,
2564 p_end_date => l_end_date_pji_tab,
2565 p_calendar_type => l_calendar_type_tab,
2566 p_record_type => l_record_type,
2567 p_currency_type => 6,
2568 x_return_status => x_return_status,
2569 x_msg_code => x_msg_data);
2570
2571 select count(*) into l_count from pji_fm_xbs_accum_tmp1;
2572
2573 IF P_PA_DEBUG_MODE = 'Y' THEN
2574 pa_fp_gen_amount_utils.fp_debug
2575 (p_msg => 'After calling pji_fm_xbs_accum_tmp1,return status is: '||x_return_status,
2576 p_module_name => l_module_name,
2577 p_log_level => 5);
2578 END IF;
2579
2580 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2581 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2582 END IF;
2583
2584 IF l_count = 0 THEN
2585 IF P_PA_DEBUG_MODE = 'Y' THEN
2586 pa_fp_gen_amount_utils.fp_debug
2587 (p_msg => 'no actual data ',
2588 p_module_name => l_module_name,
2589 p_log_level => 5);
2590 PA_DEBUG.RESET_CURR_FUNCTION;
2591 END IF;
2592 RETURN;
2593 END IF;
2594
2595 select count(*) into l_count_no_rlm from pji_fm_xbs_accum_tmp1 WHERE
2596 res_list_member_id IS NULL;
2597
2598 IF l_count_no_rlm > 0 THEN
2599 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2600 p_msg_name => 'PA_FP_NO_RLM_ID_FOR_ACTUAL');
2601 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2602 END IF;
2603
2604 /* Update rlm_id for all rows in pji_fm_xbs_accum_tmp1 if the resource list
2605 * (l_fp_cols_rec.X_RESOURCE_LIST_ID) is None - Uncategorized.
2606 * This logic is not handled by the PJI generic resource mapping API. */
2607
2608 SELECT NVL(uncategorized_flag,'N')
2609 INTO l_uncategorized_flag
2610 FROM pa_resource_lists_all_bg
2611 WHERE resource_list_id = l_fp_cols_rec.X_RESOURCE_LIST_ID;
2612
2613 IF l_uncategorized_flag = 'Y' THEN
2614 l_rlm_id := PA_FP_GEN_AMOUNT_UTILS.GET_RLM_ID (
2615 p_project_id => p_project_id,
2616 p_resource_list_id => l_fp_cols_rec.X_RESOURCE_LIST_ID,
2617 p_resource_class_code => 'FINANCIAL_ELEMENTS' );
2618 UPDATE pji_fm_xbs_accum_tmp1
2619 SET res_list_member_id = l_rlm_id;
2620 END IF;
2621
2622 /* updating the project element id ( task id ) to NULL
2623 when the value is <= 0. */
2624 update pji_fm_xbs_accum_tmp1 set project_element_id = null
2625 where NVL(project_element_id,0) <= 0;
2626
2627 /* updating the resource assignment id in pji_fm_xbs_accum_tmp1 */
2628 UPDATE PJI_FM_XBS_ACCUM_TMP1 tmp1
2629 SET source_id =
2630 (SELECT resource_assignment_id
2631 FROM pa_resource_assignments ra
2632 WHERE nvl(ra.task_id,0) = nvl(tmp1.project_element_id,0)
2633 AND ra.resource_list_member_id = tmp1.res_list_member_id
2634 AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
2635 AND ra.budget_version_id = p_budget_version_id);
2636
2637 /*---------------------------------------------------------------
2638 Populate actual amounts in PJI_FM_XBS_ACCUM_TMP1 table - End
2639 -----------------------------------------------------------------*/
2640
2641 /*---------------------------------------------------------------
2642 Delete resource assignments which are not selected if resouce
2643 assignment id is passed to this api. Otherwise populate all the
2644 resource assignment ids present for the budget version in
2645 l_resource_assgn_id_tab pl/sql table. - Begin
2646 -----------------------------------------------------------------*/
2647 l_resource_assgn_id_tab := p_resource_assgn_ids;
2648 l_count := 1;
2649 if l_resource_assgn_id_tab is not null then
2650 for get_resource_assgn_ids_rec in get_resource_assgn_ids_csr loop
2651 l_found := false;
2652 for i in 1..l_resource_assgn_id_tab.count loop
2653 if ( get_resource_assgn_ids_rec.resource_assignment_id = l_resource_assgn_id_tab(i) ) then
2654 l_found := true;
2655 exit;
2656 end if;
2657 end loop;
2658 if not l_found then
2659 l_del_resource_assgn_id_tab.extend;
2660 l_del_resource_assgn_id_tab(l_count) := get_resource_assgn_ids_rec.resource_assignment_id;
2661 l_count := l_count + 1;
2662 end if;
2663 end loop;
2664
2665 forall i in 1..l_del_resource_assgn_id_tab.count
2666 delete from pji_fm_xbs_accum_tmp1
2667 where source_id = l_del_resource_assgn_id_tab(i);
2668 end if;
2669 -- populate the resource assignment id along with currency code
2670 -- that needs to be processed
2671 OPEN distinct_ra_curr_cursor(l_txn_currency_flag,
2672 l_FP_COLS_REC.X_PROJECT_CURRENCY_CODE,
2673 l_FP_COLS_REC.X_PROJFUNC_CURRENCY_CODE);
2674 FETCH distinct_ra_curr_cursor
2675 BULK COLLECT
2676 INTO l_resource_assgn_id_tab,
2677 l_txn_currency_code_tab;
2678 CLOSE distinct_ra_curr_cursor;
2679 /*---------------------------------------------------------------
2680 Getting relevant resource assignment ids - End
2681 -----------------------------------------------------------------*/
2682
2683 /*---------------------------------------------------------------
2684 Check whether budget lines are available in pa_budget_lines for
2685 all period for each resource assignment present in
2686 PJI_FM_XBS_ACCUM_TMP1 temp table. If budget line is not present,
2687 create budget line.
2688 -----------------------------------------------------------------*/
2689 if l_resource_assgn_id_tab.count > 0 then
2690 for i in 1..l_resource_assgn_id_tab.count loop
2691 for period_info_rec in get_period_info_csr(l_resource_assgn_id_tab(i))
2692 loop
2693 -- Check whether budget line exists in PJI_FM_XBS_ACCUM_TMP1
2694 -- table for that period
2695 open budget_line_info_csr(l_resource_assgn_id_tab(i),
2696 period_info_rec.period_name,
2697 l_txn_currency_code_tab(i));
2698 fetch budget_line_info_csr into budget_line_rec;
2699 if budget_line_info_csr%found then
2700 l_budget_line_exists := 'Y';
2701 else
2702 l_budget_line_exists := 'N';
2703 end if;
2704 close budget_line_info_csr;
2705 -- if budget line doesn't exist, create a budget line
2706 if (l_budget_line_exists = 'N') then
2707 if(l_calendar_type = 'G') then
2708 open gl_period_start_end_dates_csr(period_info_rec.period_name,
2709 l_fp_cols_rec.x_set_of_books_id);
2710 fetch gl_period_start_end_dates_csr into l_start_date, l_end_date;
2711 close gl_period_start_end_dates_csr;
2712 elsif (l_calendar_type = 'P') then
2713 open pa_period_start_end_dates_csr(period_info_rec.period_name,
2714 l_fp_cols_rec.x_set_of_books_id);
2715 fetch pa_period_start_end_dates_csr into l_start_date, l_end_date;
2716 close pa_period_start_end_dates_csr;
2717 end if;
2718 INSERT INTO PA_BUDGET_LINES(BUDGET_VERSION_ID,
2719 RESOURCE_ASSIGNMENT_ID,
2720 START_DATE,
2721 END_DATE,
2722 LAST_UPDATE_DATE,
2723 LAST_UPDATED_BY,
2724 CREATION_DATE,
2725 CREATED_BY,
2726 LAST_UPDATE_LOGIN,
2727 PERIOD_NAME,
2728 BUDGET_LINE_ID,
2729 TXN_CURRENCY_CODE,
2730 RAW_COST_SOURCE,
2731 BURDENED_COST_SOURCE,
2732 QUANTITY_SOURCE,
2733 REQUEST_ID,
2734 PROJFUNC_CURRENCY_CODE,
2735 PROJECT_CURRENCY_CODE
2736 )
2737 VALUES(p_budget_version_id,
2738 l_resource_assgn_id_tab(i),
2739 l_start_date,
2740 l_end_date,
2741 l_sysdate,
2742 l_last_updated_by,
2743 l_sysdate,
2744 l_last_updated_by,
2745 l_last_update_login,
2746 period_info_rec.period_name,
2747 PA_BUDGET_LINES_S.nextval,
2748 period_info_rec.txn_currency_code,
2749 PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_COPY_P,
2750 PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_COPY_P,
2751 PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_COPY_P,
2752 fnd_global.conc_request_id,
2753 l_fp_cols_rec.x_projfunc_currency_code,
2754 l_fp_cols_rec.x_project_currency_code);
2755 end if;
2756 end loop;
2757 end loop;
2758 -- if no resource assignment to be processed, simply return
2759 else
2760 return;
2761 end if;
2762
2763 /*-------------------------------------------------------------------
2764 Populate l_amt_dtls_tbl and call PA_FP_MAINTAIN_ACTUAL_PUB.MAINTAIN_ACTUAL_AMT_RA
2765 API to update the budget lines with correct ETC, EAC values - Begin
2766 ---------------------------------------------------------------------*/
2767 FOR i IN 1..l_resource_assgn_id_tab.count LOOP
2768 IF l_calendar_type = 'P' THEN
2769 OPEN budget_line_cursor_pa(
2770 l_txn_currency_flag,
2771 l_resource_assgn_id_tab(i),
2772 l_txn_currency_code_tab(i),
2773 l_org_id,
2774 l_FP_COLS_REC.X_VERSION_TYPE);
2775 FETCH budget_line_cursor_pa
2776 BULK COLLECT
2777 INTO l_period_name_tab,
2778 l_start_date_tab,
2779 l_end_date_tab,
2780 l_quantity_tab,
2781 l_txn_raw_cost_tab,
2782 l_txn_brdn_cost_tab,
2783 l_txn_revenue_tab,
2784 l_proj_raw_cost_tab,
2785 l_proj_brdn_cost_tab,
2786 l_proj_revenue_tab,
2787 l_pou_raw_cost_tab,
2788 l_pou_brdn_cost_tab,
2789 l_pou_revenue_tab;
2790 CLOSE budget_line_cursor_pa;
2791 ELSIF l_calendar_type = 'G' THEN
2792 l_count :=1;
2793 l_period_name_tab.delete;
2794 l_start_date_tab.delete;
2795 l_end_date_tab.delete;
2796 l_quantity_tab.delete;
2797 l_txn_raw_cost_tab.delete;
2798 l_txn_brdn_cost_tab.delete;
2799 l_txn_revenue_tab.delete;
2800 l_proj_raw_cost_tab.delete;
2801 l_proj_brdn_cost_tab.delete;
2802 l_proj_revenue_tab.delete;
2803 l_pou_raw_cost_tab.delete;
2804 l_pou_brdn_cost_tab.delete;
2805 l_pou_revenue_tab.delete;
2806
2807 OPEN budget_line_cursor_gl(
2808 l_resource_assgn_id_tab(i),
2809 l_txn_currency_flag,
2810 l_fp_cols_rec.x_set_of_books_id,
2811 l_fp_cols_rec.x_version_type,
2812 l_fp_cols_rec.x_project_currency_code,
2813 l_fp_cols_rec.x_projfunc_currency_code);
2814 FETCH budget_line_cursor_gl into l_budget_line_gl_rec;
2815 Loop
2816 exit when budget_line_cursor_gl%notfound;
2817 exit when l_budget_line_gl_rec.period_name is NULL;
2818 if l_budget_line_gl_rec.source_id is not null and
2819 l_budget_line_gl_rec.source_id = l_resource_assgn_id_tab(i) and
2820 (l_txn_currency_flag <> 'Y'
2821 or l_budget_line_gl_rec.txn_currency_code = l_txn_currency_code_tab(i))
2822 then
2823 l_period_name_tab(l_count) := l_budget_line_gl_rec.period_name;
2824 l_start_date_tab(l_count) := l_budget_line_gl_rec.start_date;
2825 l_end_date_tab(l_count) := l_budget_line_gl_rec.end_date;
2826 l_quantity_tab(l_count) := l_budget_line_gl_rec.quantity;
2827 l_txn_raw_cost_tab(l_count) := l_budget_line_gl_rec.txn_raw_cost;
2828 l_txn_brdn_cost_tab(l_count) := l_budget_line_gl_rec.txn_brdn_cost;
2829 l_txn_revenue_tab(l_count) := l_budget_line_gl_rec.txn_revenue;
2830 l_proj_raw_cost_tab(l_count) := l_budget_line_gl_rec.prj_raw_cost;
2831 l_proj_brdn_cost_tab(l_count) := l_budget_line_gl_rec.prj_brdn_cost;
2832 l_proj_revenue_tab(l_count) := l_budget_line_gl_rec.prj_revenue;
2833 l_pou_raw_cost_tab(l_count) := l_budget_line_gl_rec.pou_raw_cost;
2834 l_pou_brdn_cost_tab(l_count) := l_budget_line_gl_rec.pou_brdn_cost;
2835 l_pou_revenue_tab(l_count) := l_budget_line_gl_rec.pou_revenue;
2836 l_count := l_count+1;
2837 FETCH budget_line_cursor_gl into l_budget_line_gl_rec;
2838 end if;
2839 end loop;
2840 close budget_line_cursor_gl;
2841
2842 END IF;
2843
2844 SELECT rate_based_flag into l_rate_based_flag
2845 FROM pa_resource_assignments
2846 WHERE resource_assignment_id = l_resource_assgn_id_tab(i);
2847 IF l_rate_based_flag = 'N' THEN
2848 IF l_FP_COLS_REC.x_version_type = 'REVENUE' THEN
2849 l_quantity_tab := l_txn_revenue_tab;
2850 ELSE
2851 l_quantity_tab := l_txn_raw_cost_tab;
2852 END IF;
2853 END IF;
2854
2855
2856 l_amt_dtls_tbl.delete;
2857 FOR j IN 1..l_period_name_tab.count LOOP
2858 l_amt_dtls_tbl(j).period_name := l_period_name_tab(j);
2859 l_amt_dtls_tbl(j).start_date := l_start_date_tab(j);
2860 l_amt_dtls_tbl(j).end_date := l_end_date_tab(j);
2861 l_amt_dtls_tbl(j).quantity := l_quantity_tab(j);
2862 l_amt_dtls_tbl(j).txn_raw_cost := l_txn_raw_cost_tab(j);
2863 l_amt_dtls_tbl(j).txn_burdened_cost := l_txn_brdn_cost_tab(j);
2864 l_amt_dtls_tbl(j).txn_revenue := l_txn_revenue_tab(j);
2865 l_amt_dtls_tbl(j).project_raw_cost := l_proj_raw_cost_tab(j);
2866 l_amt_dtls_tbl(j).project_burdened_cost := l_proj_brdn_cost_tab(j);
2867 l_amt_dtls_tbl(j).project_revenue := l_proj_revenue_tab(j);
2868 l_amt_dtls_tbl(j).project_func_raw_cost := l_pou_raw_cost_tab(j);
2869 l_amt_dtls_tbl(j).project_func_burdened_cost := l_pou_brdn_cost_tab(j);
2870 l_amt_dtls_tbl(j).project_func_revenue := l_pou_revenue_tab(j);
2871 /*For cost version, revenue amounts should be null
2872 For revenue version, cost amounts should be null */
2873 IF l_fp_cols_rec.x_version_type = 'COST' THEN
2874 l_amt_dtls_tbl(j).txn_revenue := null;
2875 l_amt_dtls_tbl(j).project_revenue := null;
2876 l_amt_dtls_tbl(j).project_func_revenue := null;
2877 ELSIF l_fp_cols_rec.x_version_type = 'REVENUE' THEN
2878 l_amt_dtls_tbl(j).txn_raw_cost := null;
2879 l_amt_dtls_tbl(j).txn_burdened_cost := null;
2880 l_amt_dtls_tbl(j).project_raw_cost := null;
2881 l_amt_dtls_tbl(j).project_burdened_cost := null;
2882 l_amt_dtls_tbl(j).project_func_raw_cost := null;
2883 l_amt_dtls_tbl(j).project_func_burdened_cost := null;
2884 END IF;
2885 END LOOP;
2886
2887 IF P_PA_DEBUG_MODE = 'Y' THEN
2888 pa_fp_gen_amount_utils.fp_debug
2889 (p_msg => 'Before calling PA_FP_MAINTAIN_ACTUAL_PUB.MAINTAIN_ACTUAL_AMT_RA',
2890 p_module_name => l_module_name,
2891 p_log_level => 5);
2892 END IF;
2893 /**Populating target budget lines by summing up the values.
2894 *P_AMT_DTLS_REC_TAB has the amt data for each specific resource_assignment_id
2895 *3.and txn_currency_code**/
2896 PA_FP_MAINTAIN_ACTUAL_PUB.MAINTAIN_ACTUAL_AMT_RA (
2897 P_PROJECT_ID => P_PROJECT_ID,
2898 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
2899 P_RESOURCE_ASSIGNMENT_ID => l_resource_assgn_id_tab(i),
2900 P_TXN_CURRENCY_CODE => l_txn_currency_code_tab(i),
2901 P_AMT_DTLS_REC_TAB => l_amt_dtls_tbl,
2902 P_CALLING_CONTEXT => 'FP_GEN_FCST_COPY_ACTUAL',
2903 X_RETURN_STATUS => x_return_Status,
2904 X_MSG_COUNT => x_msg_count,
2905 X_MSG_DATA => x_msg_data );
2906 IF P_PA_DEBUG_MODE = 'Y' THEN
2907 pa_fp_gen_amount_utils.fp_debug
2908 (p_msg => 'After calling PA_FP_MAINTAIN_ACTUAL_PUB.MAINTAIN_ACTUAL_AMT_RA,
2909 return status is: '||x_return_status,
2910 p_module_name => l_module_name,
2911 p_log_level => 5);
2912 END IF;
2913 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2914 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2915 END IF;
2916 END LOOP;
2917 /*---------------------------------------------------------------
2918 Populate l_amt_dtls_tbl - End
2919 -----------------------------------------------------------------*/
2920
2921 /*---------------------------------------------------------------
2922 Rollup amounts - Begin
2923 -----------------------------------------------------------------*/
2924 -- ROLLUP PC and PFC numbers to pa_resource_assignments
2925 pa_fp_calc_plan_pkg.rollup_pf_pfc_to_ra
2926 ( p_budget_version_id => p_budget_version_id
2927 ,p_calling_module => 'COLLECT_ACTUALS'
2928 ,x_return_status => x_return_status
2929 ,x_msg_count => x_msg_count
2930 ,x_msg_data => l_msg_data
2931 );
2932
2933 IF p_pa_debug_mode = 'Y' THEN
2934 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2935 P_MSG => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.' ||
2936 'MAINTAIN_BUDGET_VERSION',
2937 P_MODULE_NAME => l_module_name);
2938 END IF;
2939 PA_FP_GEN_FCST_AMT_PUB1.MAINTAIN_BUDGET_VERSION
2940 (P_PROJECT_ID => P_PROJECT_ID,
2941 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
2942 P_ETC_START_DATE => l_ACTUALS_THROUGH_DATE + 1,
2943 P_CALL_MAINTAIN_DATA_API => 'Y',
2944 X_RETURN_STATUS => x_return_status,
2945 X_MSG_COUNT => x_msg_count,
2946 X_MSG_DATA => x_msg_data );
2947
2948 IF p_pa_debug_mode = 'Y' THEN
2949 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
2950 P_MSG => 'After calling PA_FP_GEN_FCST_AMT_PUB1.' ||
2951 'MAINTAIN_BUDGET_VERSION: '||x_return_status,
2952 P_MODULE_NAME => l_module_name);
2953 END IF;
2954 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2955 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2956 END IF;
2957 /*---------------------------------------------------------------
2958 Rollup amounts - End
2959 -----------------------------------------------------------------*/
2960
2961 IF P_PA_DEBUG_MODE = 'Y' THEN
2962 PA_DEBUG.RESET_CURR_FUNCTION;
2963 END IF;
2964 EXCEPTION
2965 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2966 l_msg_count := FND_MSG_PUB.count_msg;
2967 IF l_msg_count = 1 THEN
2968 PA_INTERFACE_UTILS_PUB.get_messages
2969 ( p_encoded => FND_API.G_TRUE,
2970 p_msg_index => 1,
2971 p_msg_count => l_msg_count,
2972 p_msg_data => l_msg_data,
2973 p_data => l_data,
2974 p_msg_index_out => l_msg_index_out);
2975 x_msg_data := l_data;
2976 x_msg_count := l_msg_count;
2977 ELSE
2978 x_msg_count := l_msg_count;
2979 END IF;
2980
2981 ROLLBACK;
2982
2983 x_return_status := FND_API.G_RET_STS_ERROR;
2984
2985 IF P_PA_DEBUG_MODE = 'Y' THEN
2986 pa_fp_gen_amount_utils.fp_debug
2987 (p_msg => 'Invalid Arguments Passed',
2988 p_module_name => l_module_name,
2989 p_log_level => 5);
2990 PA_DEBUG.RESET_CURR_FUNCTION;
2991 END IF;
2992 RAISE;
2993 WHEN OTHERS THEN
2994 rollback;
2995 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2996 x_msg_count := 1;
2997 x_msg_data := substr(sqlerrm,1,240);
2998 FND_MSG_PUB.add_exc_msg
2999 ( p_pkg_name => 'PA_FP_COPY_ACTUALS_PUB',
3000 p_procedure_name => 'COLLECT_ACTUALS',
3001 p_error_text => substr(sqlerrm,1,240));
3002
3003 IF P_PA_DEBUG_MODE = 'Y' THEN
3004 pa_fp_gen_amount_utils.fp_debug
3005 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
3006 p_module_name => l_module_name,
3007 p_log_level => 5);
3008 PA_DEBUG.RESET_CURR_FUNCTION;
3009 END IF;
3010 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3011
3012 END COLLECT_ACTUALS;
3013
3014 END PA_FP_COPY_ACTUALS_PUB;