[Home] [Help]
PACKAGE BODY: APPS.PA_FP_GEN_FCST_RMAP_PKG
Source
1 PACKAGE body PA_FP_GEN_FCST_RMAP_PKG as
2 /* $Header: PAFPFGRB.pls 120.2.12020000.2 2013/05/17 10:56:15 bpottipa ship $ */
3
4 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6 /** This method is used to map the ETC source txns to the target
7 * plan version resource list. */
8 PROCEDURE FCST_SRC_TXNS_RMAP
9 ( P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
10 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
11 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
12 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
13 X_MSG_COUNT OUT NOCOPY NUMBER,
14 X_MSG_DATA OUT NOCOPY VARCHAR2 )
15 IS
16 --preparing input param for PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS
17 -- Bug 4070976: Because we have added some pre-processing before this
18 -- cursor is used, we should only pick up resources with null values for
19 -- TARGET_RLM_ID, which have not been processed yet.
20 CURSOR map_to_target_fp_cur IS
21 SELECT tmp1.RESOURCE_ASSIGNMENT_ID, --p_TXN_SOURCE_ID,
22 'RES_ASSIGNMENTS', --tmp1.TXN_SOURCE_TYPE_CODE,
23 tmp1.PERSON_ID,
24 tmp1.JOB_ID,
25 tmp1.ORGANIZATION_ID,
26 tmp1.SUPPLIER_ID,
27 tmp1.EXPENDITURE_TYPE,
28 tmp1.EVENT_TYPE,
29 tmp1.NON_LABOR_RESOURCE,
30 tmp1.EXPENDITURE_CATEGORY,
31 tmp1.REVENUE_CATEGORY_CODE,
32 NULL, --tmp1.NLR_ORGANIZATION_ID,
33 tmp1.event_type,--tmp1.EVENT_CLASSIFICATION,
34 NULL, --tmp1.SYS_LINK_FUNCTION,
35 NVL(tmp1.INCUR_BY_ROLE_ID,tmp1.PROJECT_ROLE_ID),
36 NVL(tmp1.INCUR_BY_RES_CLASS_CODE,tmp1.RESOURCE_CLASS_CODE),
37 tmp1.MFC_COST_TYPE_ID,
38 tmp1.RESOURCE_CLASS_FLAG,
39 tmp1.FC_RES_TYPE_CODE,
40 tmp1.INVENTORY_ITEM_ID,
41 tmp1.ITEM_CATEGORY_ID,
42 tmp1.PERSON_TYPE_CODE,
43 tmp1.BOM_RESOURCE_ID,
44 tmp1.NAMED_ROLE,
45 tmp1.INCURRED_BY_RES_FLAG,
46 tmp1.RATE_BASED_FLAG,
47 tmp1.mapped_fin_task_id,
48 NULL, --TXN_WBS_ELEMENT_VER_ID
49 NULL, --tmp1.TXN_RBS_ELEMENT_ID,
50 tmp1.planning_start_date, --TXN_PLAN_START_DATE,
51 tmp1.planning_end_date --TXN_PLAN_END_DATE
52 FROM PA_FP_CALC_AMT_TMP1 tmp1
53 WHERE RESOURCE_ASSIGNMENT_ID > 0
54 AND TRANSACTION_SOURCE_CODE <> 'OPEN_COMMITMENTS'
55 AND TARGET_RLM_ID IS NULL;
56
57 l_TXN_SOURCE_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
58 l_TXN_SOURCE_TYPE_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
59 l_PERSON_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
60 l_JOB_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
61 l_ORGANIZATION_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
62 l_VENDOR_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
63 l_EXPENDITURE_TYPE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
64 l_EVENT_TYPE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
65 l_NON_LABOR_RESOURCE_tab PA_PLSQL_DATATYPES.Char20TabTyp;
66 l_EXPENDITURE_CATEGORY_tab PA_PLSQL_DATATYPES.Char30TabTyp;
67 l_REVENUE_CATEGORY_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
68 l_NLR_ORGANIZATION_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
69 l_EVENT_CLASSIFICATION_tab PA_PLSQL_DATATYPES.Char30TabTyp;
70 l_SYS_LINK_FUNCTION_tab PA_PLSQL_DATATYPES.Char30TabTyp;
71 l_PROJECT_ROLE_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
72 l_RESOURCE_CLASS_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
73 l_MFC_COST_TYPE_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
74 l_RESOURCE_CLASS_FLAG_tab PA_PLSQL_DATATYPES.Char1TabTyp;
75 l_FC_RES_TYPE_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
76 l_INVENTORY_ITEM_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
77 l_ITEM_CATEGORY_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
78 l_PERSON_TYPE_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
79 l_BOM_RESOURCE_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
80 l_NAMED_ROLE_tab PA_PLSQL_DATATYPES.Char80TabTyp;
81 l_INCURRED_BY_RES_FLAG_tab PA_PLSQL_DATATYPES.Char1TabTyp;
82 l_RATE_BASED_FLAG_tab PA_PLSQL_DATATYPES.Char1TabTyp;
83 l_TXN_TASK_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
84 l_TXN_WBS_ELEMENT_VER_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
85 l_TXN_RBS_ELEMENT_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
86 l_TXN_PLAN_START_DATE_tab PA_PLSQL_DATATYPES.DateTabTyp;
87 l_TXN_PLAN_END_DATE_tab PA_PLSQL_DATATYPES.DateTabTyp;
88
89 --out param from PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS
90 l_map_txn_source_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
91 l_map_rlm_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
92 l_map_rbs_element_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
93 l_map_txn_accum_header_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
94
95 /*after calling create_res_asg and update_res_asg
96 *we will create the new res_asg_id for the mapped
97 *rlm_id and task id for target budget_version; and
98 *also, the newly created res_asg_id is written back
99 *to PA_FP_PLANNING_RES_TMP1. We need to update this
100 *value to calc_amt_tmp1 and calc_amt_tmp2 to facilitae
101 *our future operation. */
102 CURSOR update_res_asg IS
103 SELECT task_id,
104 resource_list_member_id,
105 cbs_element_id, --bug#16791711
106 resource_assignment_id
107 FROM PA_FP_PLANNING_RES_TMP1;
108
109 l_upd_task_id_tab PA_PLSQL_DATATYPES.NumTabTyp;
110 l_upd_rlm_id_tab PA_PLSQL_DATATYPES.NumTabTyp;
111 l_upd_cbs_id_tab PA_PLSQL_DATATYPES.NumTabTyp; --bug#16791711
112 l_upd_target_ra_id_tab PA_PLSQL_DATATYPES.NumTabTyp;
113
114 l_upd_ra_id_tab1 PA_PLSQL_DATATYPES.NumTabTyp;
115 l_upd_target_ra_id_tab1 PA_PLSQL_DATATYPES.NumTabTyp;
116
117 l_count_tmp NUMBER;
118 p_called_mode varchar2(20) := 'SELF_SERVICE';
119
120 /* PL/SQL tables for updating target transaction_source_code values */
121 l_tgt_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
122 l_txn_src_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
123
124 l_sysdate DATE;
125 l_last_updated_by PA_RESOURCE_ASSIGNMENTS.LAST_UPDATED_BY%TYPE;
126 l_last_update_login PA_RESOURCE_ASSIGNMENTS.LAST_UPDATE_LOGIN%TYPE;
127
128 /* Local copy of target version details for getting source version ids. */
129 l_fp_cols_rec PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
130 l_resource_list_id PA_BUDGET_VERSIONS.RESOURCE_LIST_ID%TYPE;
131
132 /* Date update variables */
133 l_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
134 l_start_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
135 l_end_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
136
137 l_etc_start_date DATE;
138
139 l_module_name VARCHAR2(100) := 'pa.plsql.PA_FP_GEN_FCST_RMAP_PKG.FCST_SRC_TXNS_RMAP';
140 l_count NUMBER;
141 l_msg_count NUMBER;
142 l_data VARCHAR2(1000);
143 l_msg_data VARCHAR2(1000);
144 l_msg_index_out NUMBER;
145 l_uncategorized_flag VARCHAR2(1);
146 l_rlm_id NUMBER;
147 BEGIN
148 x_return_status := FND_API.G_RET_STS_SUCCESS;
149 x_msg_count := 0;
150
151 IF p_pa_debug_mode = 'Y' THEN
152 PA_DEBUG.SET_CURR_FUNCTION( p_function => 'FCST_SRC_TXNS_RMAP',
153 p_debug_mode => p_pa_debug_mode );
154 END IF;
155
156 SELECT NVL(uncategorized_flag,'N')
157 INTO l_uncategorized_flag
158 FROM pa_resource_lists_all_bg
159 WHERE resource_list_id = p_fp_cols_rec.X_RESOURCE_LIST_ID;
160
161 IF l_uncategorized_flag = 'Y' THEN
162 l_rlm_id := PA_FP_GEN_AMOUNT_UTILS.GET_RLM_ID
163 ( p_project_id => p_project_id,
164 p_resource_list_id => p_fp_cols_rec.X_RESOURCE_LIST_ID,
165 p_resource_class_code => 'FINANCIAL_ELEMENTS' );
166 UPDATE PA_FP_CALC_AMT_TMP1
167 SET target_rlm_id = l_rlm_id;
168 ELSIF l_uncategorized_flag = 'N' THEN
169
170 -- Beginning of code change for Bug 4070976 --
171 /* We get a fresh local copy of the target version details because
172 * p_fp_cols_rec does not have the updated source version ids. */
173 IF p_pa_debug_mode = 'Y' THEN
174 pa_fp_gen_amount_utils.fp_debug
175 ( p_msg => 'Before calling
176 pa_fp_gen_amount_utils.get_plan_version_dtls',
177 p_module_name => l_module_name,
178 p_log_level => 5 );
179 END IF;
180 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS
181 ( P_BUDGET_VERSION_ID => p_budget_version_id,
182 X_FP_COLS_REC => l_fp_cols_rec,
183 X_RETURN_STATUS => x_return_status,
184 X_MSG_COUNT => x_msg_count,
185 X_MSG_DATA => x_msg_data );
186 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
187 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
188 END IF;
189 IF p_pa_debug_mode = 'Y' THEN
190 pa_fp_gen_amount_utils.fp_debug
191 ( p_msg => 'Status after calling
192 pa_fp_gen_amount_utils.get_plan_version_dtls: ' ||
193 x_return_status,
194 p_module_name => l_module_name,
195 p_log_level => 5 );
196 END IF;
197
198 /* If target has a Workplan source AND source and target resource lists
199 * match, then copy source rlm_id to target_rlm_id for WP records in tmp1. */
200 IF l_fp_cols_rec.x_gen_src_wp_version_id IS NOT NULL THEN
201 SELECT resource_list_id
202 INTO l_resource_list_id
203 FROM pa_budget_versions
204 WHERE budget_version_id = l_fp_cols_rec.x_gen_src_wp_version_id;
205
206 IF l_fp_cols_rec.x_resource_list_id = l_resource_list_id THEN
207 UPDATE PA_FP_CALC_AMT_TMP1
208 SET target_rlm_id = resource_list_member_id
209 WHERE transaction_source_code = 'WORKPLAN_RESOURCES';
210 END IF;
211 END IF;
212
213 /* If target has a Financial Plan source AND source and target resource lists
214 * match, then copy source rlm_id to target_rlm_id for FP records in tmp1. */
215 IF l_fp_cols_rec.x_gen_src_plan_version_id IS NOT NULL THEN
216 SELECT resource_list_id
217 INTO l_resource_list_id
218 FROM pa_budget_versions
219 WHERE budget_version_id = l_fp_cols_rec.x_gen_src_plan_version_id;
220
221 IF l_fp_cols_rec.x_resource_list_id = l_resource_list_id THEN
222 UPDATE PA_FP_CALC_AMT_TMP1
223 SET target_rlm_id = resource_list_member_id
224 WHERE transaction_source_code = 'FINANCIAL_PLAN';
225 END IF;
226 END IF;
227 -- End of code change for Bug 4070976 --
228
229 OPEN map_to_target_fp_cur;
230 FETCH map_to_target_fp_cur
231 BULK COLLECT
232 INTO l_TXN_SOURCE_ID_tab,
233 l_TXN_SOURCE_TYPE_CODE_tab,
234 l_PERSON_ID_tab,
235 l_JOB_ID_tab,
236 l_ORGANIZATION_ID_tab,
237 l_VENDOR_ID_tab,
238 l_EXPENDITURE_TYPE_tab,
239 l_EVENT_TYPE_tab,
240 l_NON_LABOR_RESOURCE_tab,
241 l_EXPENDITURE_CATEGORY_tab,
242 l_REVENUE_CATEGORY_CODE_tab,
243 l_NLR_ORGANIZATION_ID_tab,
244 l_EVENT_CLASSIFICATION_tab,
245 l_SYS_LINK_FUNCTION_tab,
246 l_PROJECT_ROLE_ID_tab,
247 l_RESOURCE_CLASS_CODE_tab,
248 l_MFC_COST_TYPE_ID_tab,
249 l_RESOURCE_CLASS_FLAG_tab,
250 l_FC_RES_TYPE_CODE_tab,
251 l_INVENTORY_ITEM_ID_tab,
252 l_ITEM_CATEGORY_ID_tab,
253 l_PERSON_TYPE_CODE_tab,
254 l_BOM_RESOURCE_ID_tab,
255 l_NAMED_ROLE_tab,
256 l_INCURRED_BY_RES_FLAG_tab,
257 l_RATE_BASED_FLAG_tab,
258 l_TXN_TASK_ID_tab,
259 l_TXN_WBS_ELEMENT_VER_ID_tab,
260 l_TXN_RBS_ELEMENT_ID_tab,
261 l_TXN_PLAN_START_DATE_tab,
262 l_TXN_PLAN_END_DATE_tab;
263 CLOSE map_to_target_fp_cur;
264
265 /*IF p_pa_debug_mode = 'Y' THEN
266 pa_fp_gen_amount_utils.fp_debug
267 (p_msg => 'Value of l_txn_source_id_tab.count: '||l_txn_source_id_tab.count,
268 p_module_name => l_module_name,
269 p_log_level => 5);
270 END IF;*/
271 --dbms_output.put_line('--l_txn_source_id_tab.count:'||l_txn_source_id_tab.count);
272
273 IF ( l_TXN_SOURCE_ID_tab.count > 0 ) THEN
274 IF p_pa_debug_mode = 'Y' THEN
275 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG (
276 P_CALLED_MODE => p_called_mode,
277 P_MSG => 'Before calling PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS',
278 P_MODULE_NAME => l_module_name );
279 END IF;
280
281 /* bug 3576766 : p_project_id parameter added for
282 non centrally controlled resource list mapping. */
283 -- select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;
284 -- hr_utility.trace('=!!!=PA_FP_CALC_AMT_TMP1.count bef calling res map api'||l_count_tmp);
285 PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS (
286 P_PROJECT_ID => p_project_id,
287 P_BUDGET_VERSION_ID => NULL,
288 P_RESOURCE_LIST_ID => P_FP_COLS_REC.X_RESOURCE_LIST_ID,
289 P_RBS_VERSION_ID => NULL,
290 P_CALLING_PROCESS => 'FORECAST_GENERATION',
291 P_CALLING_CONTEXT => 'PLSQL',
292 P_PROCESS_CODE => 'RES_MAP',
293 P_CALLING_MODE => 'PLSQL_TABLE',
294 P_INIT_MSG_LIST_FLAG => 'N',
295 P_COMMIT_FLAG => 'N',
296 P_TXN_SOURCE_ID_TAB => l_TXN_SOURCE_ID_tab,
297 P_TXN_SOURCE_TYPE_CODE_TAB => l_TXN_SOURCE_TYPE_CODE_tab,
298 P_PERSON_ID_TAB => l_PERSON_ID_tab,
299 P_JOB_ID_TAB => l_JOB_ID_tab,
300 P_ORGANIZATION_ID_TAB => l_ORGANIZATION_ID_tab,
301 P_VENDOR_ID_TAB => l_VENDOR_ID_tab,
302 P_EXPENDITURE_TYPE_TAB => l_EXPENDITURE_TYPE_tab,
303 P_EVENT_TYPE_TAB => l_EVENT_TYPE_tab,
304 P_NON_LABOR_RESOURCE_TAB => l_NON_LABOR_RESOURCE_tab,
305 P_EXPENDITURE_CATEGORY_TAB => l_EXPENDITURE_CATEGORY_tab,
306 P_REVENUE_CATEGORY_CODE_TAB => l_REVENUE_CATEGORY_CODE_tab,
307 P_NLR_ORGANIZATION_ID_TAB => l_NLR_ORGANIZATION_ID_tab,
308 P_EVENT_CLASSIFICATION_TAB => l_EVENT_CLASSIFICATION_tab,
309 P_SYS_LINK_FUNCTION_TAB => l_SYS_LINK_FUNCTION_tab,
310 P_PROJECT_ROLE_ID_TAB => l_PROJECT_ROLE_ID_tab,
311 P_RESOURCE_CLASS_CODE_TAB => l_RESOURCE_CLASS_CODE_tab,
312 P_MFC_COST_TYPE_ID_TAB => l_MFC_COST_TYPE_ID_tab,
313 P_RESOURCE_CLASS_FLAG_TAB => l_RESOURCE_CLASS_FLAG_tab,
314 P_FC_RES_TYPE_CODE_TAB => l_FC_RES_TYPE_CODE_tab,
315 P_INVENTORY_ITEM_ID_TAB => l_INVENTORY_ITEM_ID_tab,
316 P_ITEM_CATEGORY_ID_TAB => l_ITEM_CATEGORY_ID_tab,
317 P_PERSON_TYPE_CODE_TAB => l_PERSON_TYPE_CODE_tab,
318 P_BOM_RESOURCE_ID_TAB => l_BOM_RESOURCE_ID_tab,
319 P_NAMED_ROLE_TAB => l_NAMED_ROLE_tab,
320 P_INCURRED_BY_RES_FLAG_TAB => l_INCURRED_BY_RES_FLAG_tab,
321 P_RATE_BASED_FLAG_TAB => l_RATE_BASED_FLAG_tab,
322 P_TXN_TASK_ID_TAB => l_TXN_TASK_ID_tab,
323 P_TXN_WBS_ELEMENT_VER_ID_TAB => l_TXN_WBS_ELEMENT_VER_ID_tab,
324 P_TXN_RBS_ELEMENT_ID_TAB => l_TXN_RBS_ELEMENT_ID_tab,
325 P_TXN_PLAN_START_DATE_TAB => l_TXN_PLAN_START_DATE_tab,
326 P_TXN_PLAN_END_DATE_TAB => l_TXN_PLAN_END_DATE_tab,
327 X_TXN_SOURCE_ID_TAB => l_map_txn_source_id_tab,
328 X_RES_LIST_MEMBER_ID_TAB => l_map_rlm_id_tab,
329 X_RBS_ELEMENT_ID_TAB => l_map_rbs_element_id_tab,
330 X_TXN_ACCUM_HEADER_ID_TAB => l_map_txn_accum_header_id_tab,
331 X_RETURN_STATUS => x_return_status,
332 X_MSG_COUNT => x_msg_count,
333 X_MSG_DATA => x_msg_data );
334 IF p_pa_debug_mode = 'Y' THEN
335 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
336 P_CALLED_MODE => p_called_mode,
337 P_MSG => 'After calling PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS: '||
338 x_return_status,
339 P_MODULE_NAME => l_module_name);
340 END IF;
341 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
342 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
343 END IF;
344
345 /**Previously, we populate latest published fwbs back to pa_budget_versions,
346 but, if planning at task level, project_structure_version_id is already
347 in pa_budget_versions, if planning at project level,project_structure
348 version_id not needed in pa_budget_versions. So update deleted **/
349
350 --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;
351
352 /*IF p_pa_debug_mode = 'Y' THEN
353 pa_fp_gen_amount_utils.fp_debug
354 (p_msg => 'Count of PA_FP_CALC_AMT_TMP1: '||l_count_tmp,
355 p_module_name => l_module_name,
356 p_log_level => 5);
357 END IF;*/
358
359 --dbms_output.put_line('@@tmp1 has:'||l_count_tmp);
360 --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1
361 --where task_id = 0;
362 --dbms_output.put_line('@@tmp1 with 0 task_id has:'||l_count_tmp);
363
364 /* hr_utility.trace('==PA_FP_CALC_AMT_TMP1.count aft res map call'||l_count_tmp);
365 hr_utility.trace('map rlm id tab count '||l_map_rlm_id_tab.count);
366 hr_utility.trace('map src id tab count '||l_map_txn_source_id_tab.count); */
367 /*IF p_pa_debug_mode = 'Y' THEN
368 pa_fp_gen_amount_utils.fp_debug
369 (p_msg => 'Value of l_map_rlm_id_tab.count: '||l_map_rlm_id_tab.count,
370 p_module_name => l_module_name,
371 p_log_level => 5);
372 END IF;*/
373
374 FORALL i IN 1..l_map_rlm_id_tab.count
375 UPDATE /*+ INDEX(PA_FP_CALC_AMT_TMP1,PA_FP_CALC_AMT_TMP1_N2)*/
376 PA_FP_CALC_AMT_TMP1
377 SET target_rlm_id = l_map_rlm_id_tab(i)
378 WHERE resource_assignment_id = l_map_txn_source_id_tab(i);
379
380 -- hr_utility.trace('no of rows updated in tmp1 aft res map:'||sql%rowcount);
381 -- delete from calc_amt_tmp1;
382 -- insert into calc_amt_tmp1 select * from pa_fp_calc_amt_tmp1;
383 END IF;
384 /* end if for table count greater than zero */
385 END IF;
386 /* uncategorized flag check */
387
388 /* end if for calling the mapping api for the target version */
389
390 --select count(*) into l_count_tmp
391 --from pa_resource_assignments where budget_version_id = P_BUDGET_VERSION_ID;
392 --dbms_output.put_line('@@before maintain_res_asg, count of pa_resource_asgs:' ||l_count_tmp);
393 --hr_utility.trace('before calling maintain res asg');
394
395 IF P_PA_DEBUG_MODE = 'Y' THEN
396 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
397 P_CALLED_MODE => P_CALLED_MODE,
398 P_MSG => 'Before calling PA_FP_GEN_FCST_AMT_PUB.MAINTAIN_RES_ASG',
399 P_MODULE_NAME => l_module_name );
400 END IF;
401 PA_FP_GEN_FCST_AMT_PUB.MAINTAIN_RES_ASG(
402 P_PROJECT_ID => P_PROJECT_ID,
403 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
404 P_FP_COLS_REC => P_FP_COLS_REC,
405 X_RETURN_STATUS => x_return_status,
406 X_MSG_COUNT => x_msg_count,
407 X_MSG_DATA => x_msg_data );
408 IF P_PA_DEBUG_MODE = 'Y' THEN
409 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
410 P_CALLED_MODE => P_CALLED_MODE,
411 P_MSG => 'After calling PA_FP_GEN_FCST_AMT_PUB.'||
412 'MAINTAIN_RES_ASG: '||x_return_status,
413 P_MODULE_NAME => l_module_name);
414 END IF;
415 --hr_utility.trace('after calling maintain res asg:'||x_return_status);
416 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
417 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
418 END IF;
419 --select count(*) into l_count_tmp
420 --from pa_resource_assignments where budget_version_id = P_BUDGET_VERSION_ID;
421 --dbms_output.put_line('@@after maintain_res_asg,count of pa_resource_asgs:' ||l_count_tmp);
422
423 -- Bug 3982592: Between forecast generation processes users may have changed
424 -- planning resource start and end dates. Thus, we must update the resource
425 -- assignments table with the source dates in pa_fp_planning_res_tmp1.
426 -- Bug 4114589: Moved logic for bug 3982592 from UPDATE_RES_ASG to here
427 -- so that we only update the planning dates once. Added manual lines logic.
428
429 -- Bug 4301959: Modified the Retain Manually Added Lines logic to
430 -- handle the non-time phased case separately, using the (quantity <>
431 -- actual quantity) check instead of (start_date > etc_start_date).
432
433 IF p_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'N' THEN
434 SELECT resource_assignment_id,
435 MIN(planning_start_date),
436 MAX(planning_end_date)
437 BULK COLLECT
438 INTO l_res_asg_id_tab,
439 l_start_date_tab,
440 l_end_date_tab
441 FROM pa_fp_planning_res_tmp1
442 GROUP BY resource_assignment_id;
443 ELSIF p_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'Y' THEN
444 IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
445 l_etc_start_date :=
446 PA_FP_GEN_AMOUNT_UTILS.GET_ETC_START_DATE(p_budget_version_id);
447
448 SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N1)*/
449 tmp1.resource_assignment_id,
450 MIN(tmp1.planning_start_date),
451 MAX(tmp1.planning_end_date)
452 BULK COLLECT
453 INTO l_res_asg_id_tab,
454 l_start_date_tab,
455 l_end_date_tab
456 FROM pa_fp_planning_res_tmp1 tmp1,
457 pa_resource_assignments ra
458 WHERE ra.budget_version_id = p_budget_version_id
459 AND ra.task_id = tmp1.task_id
460 AND ra.resource_list_member_id = tmp1.resource_list_member_id
461 AND nvl(ra.cbs_element_id,-1) = nvl(tmp1.cbs_element_id,-1) --bug#16791711
462 --AND ra.resource_assignment_id = tmp1.resource_assignment_id
463 AND ( ra.transaction_source_code IS NOT NULL
464 OR ( ra.transaction_source_code IS NULL
465 AND NOT EXISTS ( SELECT 1
466 FROM pa_budget_lines bl
467 WHERE bl.resource_assignment_id =
468 ra.resource_assignment_id
469 AND bl.start_date >= l_etc_start_date
470 AND rownum = 1 )))
471 GROUP BY tmp1.resource_assignment_id;
472 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
473 SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N1)*/
474 tmp1.resource_assignment_id,
475 MIN(tmp1.planning_start_date),
476 MAX(tmp1.planning_end_date)
477 BULK COLLECT
478 INTO l_res_asg_id_tab,
479 l_start_date_tab,
480 l_end_date_tab
481 FROM pa_fp_planning_res_tmp1 tmp1,
482 pa_resource_assignments ra
483 WHERE ra.budget_version_id = p_budget_version_id
484 AND ra.task_id = tmp1.task_id
485 AND ra.resource_list_member_id = tmp1.resource_list_member_id
486 AND nvl(ra.cbs_element_id,-1) = nvl(tmp1.cbs_element_id,-1) --bug#16791711
487 --AND ra.resource_assignment_id = tmp1.resource_assignment_id
488 AND ( ra.transaction_source_code IS NOT NULL
489 OR ( ra.transaction_source_code IS NULL
490 AND NOT EXISTS ( SELECT 1
491 FROM pa_budget_lines bl
492 WHERE bl.resource_assignment_id =
493 ra.resource_assignment_id
494 AND NVL(bl.quantity,0) <>
495 NVL(bl.init_quantity,0)
496 AND rownum = 1 )))
497 GROUP BY tmp1.resource_assignment_id;
498 END IF; -- time phase check
499 END IF;
500
501 l_last_updated_by := FND_GLOBAL.USER_ID;
502 l_last_update_login := FND_GLOBAL.LOGIN_ID;
503 l_sysdate := SYSDATE;
504
505 FORALL i in 1..l_res_asg_id_tab.count
506 UPDATE pa_resource_assignments
507 SET planning_start_date = l_start_date_tab(i),
508 planning_end_date = l_end_date_tab(i),
509 last_update_date = l_sysdate,
510 last_updated_by = l_last_updated_by,
511 last_update_login = l_last_update_login,
512 record_version_number = record_version_number + 1
513 WHERE resource_assignment_id = l_res_asg_id_tab(i);
514
515 -- End Bug 3982592
516
517 /**Now, the new res_asg_id needs to be populated back from pjlanning_tmp1
518 *to calc_tmp1 and calc_tmp2**/
519
520 OPEN update_res_asg;
521 FETCH update_res_asg
522 BULK COLLECT
523 INTO l_upd_task_id_tab,
524 l_upd_rlm_id_tab,
525 l_upd_cbs_id_tab, --bug#16791711
526 l_upd_target_ra_id_tab;
527 CLOSE update_res_asg;
528
529 /*IF p_pa_debug_mode = 'Y' THEN
530 pa_fp_gen_amount_utils.fp_debug
531 (p_msg => 'Value of l_upd_target_ra_id_tab.count: '||l_upd_target_ra_id_tab.count,
532 p_module_name => l_module_name,
533 p_log_level => 5);
534 END IF;*/
535 --hr_utility.trace('++++l_upd_task_id_tab.count:'||l_upd_task_id_tab.count);
536 --for i in 1..l_upd_task_id_tab.count LOOP
537 --hr_utility.trace('+++l_upd_task_id_tab(i):'||l_upd_task_id_tab(i));
538 --hr_utility.trace('+++l_upd_rlm_id_tab(i):'||l_upd_rlm_id_tab(i));
539 --hr_utility.trace('+++l_upd_target_ra_id_tab(i):'||l_upd_target_ra_id_tab(i));
540 --end loop;
541 --hr_utility.trace('==l_upd_task_id_tab.count'||l_upd_task_id_tab.count);
542
543 IF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'L' THEN
544 FORALL i IN 1..l_upd_target_ra_id_tab.count
545 UPDATE /*+ INDEX(PA_FP_CALC_AMT_TMP1,PA_FP_CALC_AMT_TMP1_N3)*/
546 PA_FP_CALC_AMT_TMP1
547 SET target_res_asg_id = l_upd_target_ra_id_tab(i)
548 WHERE mapped_fin_task_id = l_upd_task_id_tab(i)
549 AND target_rlm_id = l_upd_rlm_id_tab(i)
550 AND nvl(cbs_element_id,-1) = nvl(l_upd_cbs_id_tab(i),-1)--bug#16791711
551 ;
552 ELSIF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'T' THEN
553 FORALL i IN 1..l_upd_target_ra_id_tab.count
554 UPDATE /*+ INDEX(PA_FP_CALC_AMT_TMP1,PA_FP_CALC_AMT_TMP1_N3)*/
555 PA_FP_CALC_AMT_TMP1
556 SET target_res_asg_id = l_upd_target_ra_id_tab(i)
557 WHERE mapped_fin_task_id = l_upd_task_id_tab(i)
558 AND target_rlm_id = l_upd_rlm_id_tab(i)
559 AND nvl(cbs_element_id,-1) = nvl(l_upd_cbs_id_tab(i),-1)--bug#16791711
560 ;
561 ELSIF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'P' THEN
562 FORALL i IN 1..l_upd_target_ra_id_tab.count
563 -- SQL Repository Bug 4884824; SQL ID 14901771
564 -- Fixed Full Index Scan violation by replacing
565 -- existing hint with leading hint.
566 UPDATE /*+ LEADING(PA_FP_CALC_AMT_TMP1) */
567 PA_FP_CALC_AMT_TMP1
568 SET target_res_asg_id = l_upd_target_ra_id_tab(i)
569 WHERE target_rlm_id = l_upd_rlm_id_tab(i)
570 AND nvl(cbs_element_id,-1) = nvl(l_upd_cbs_id_tab(i),-1)--bug#16791711
571 ;
572 END IF;
573
574 /* Since the ETC generation source can change between successive generations,
575 * we need to update the transaction_source_code for target resources. */
576
577 -- Bug 4301959: Modified the Retain Manually Added Lines logic to
578 -- handle the non-time phased case separately, using the (quantity <>
579 -- actual quantity) check instead of (start_date > etc_start_date).
580
581 IF p_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'N' THEN
582 SELECT DISTINCT target_res_asg_id, transaction_source_code
583 BULK COLLECT
584 INTO l_tgt_res_asg_id_tab,
585 l_txn_src_code_tab
586 FROM PA_FP_CALC_AMT_TMP1;
587 ELSIF p_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'Y' THEN
588 IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
589 SELECT /*+ INDEX(tmp1,PA_FP_CALC_AMT_TMP1_N1)*/
590 DISTINCT tmp1.target_res_asg_id, tmp1.transaction_source_code
591 BULK COLLECT
592 INTO l_tgt_res_asg_id_tab,
593 l_txn_src_code_tab
594 FROM PA_FP_CALC_AMT_TMP1 tmp1,
595 pa_resource_assignments ra
596 WHERE ra.budget_version_id = p_budget_version_id
597 AND ra.resource_assignment_id = tmp1.target_res_asg_id
598 AND ( ra.transaction_source_code IS NOT NULL
599 OR ( ra.transaction_source_code IS NULL
600 AND NOT EXISTS ( SELECT 1
601 FROM pa_budget_lines bl
602 WHERE bl.resource_assignment_id =
603 ra.resource_assignment_id
604 AND bl.start_date >= l_etc_start_date
605 AND rownum = 1 )));
606 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
607 SELECT /*+ INDEX(tmp1,PA_FP_CALC_AMT_TMP1_N1)*/
608 DISTINCT tmp1.target_res_asg_id, tmp1.transaction_source_code
609 BULK COLLECT
610 INTO l_tgt_res_asg_id_tab,
611 l_txn_src_code_tab
612 FROM PA_FP_CALC_AMT_TMP1 tmp1,
613 pa_resource_assignments ra
614 WHERE ra.budget_version_id = p_budget_version_id
615 AND ra.resource_assignment_id = tmp1.target_res_asg_id
616 AND ( ra.transaction_source_code IS NOT NULL
617 OR ( ra.transaction_source_code IS NULL
618 AND NOT EXISTS ( SELECT 1
619 FROM pa_budget_lines bl
620 WHERE bl.resource_assignment_id =
621 ra.resource_assignment_id
622 AND NVL(bl.quantity,0) <>
623 NVL(bl.init_quantity,0)
624 AND rownum = 1 )));
625 END IF; -- time phase check
626 END IF;
627
628 l_sysdate := SYSDATE;
629 l_last_updated_by := FND_GLOBAL.USER_ID;
630 l_last_update_login := FND_GLOBAL.LOGIN_ID;
631
632 FORALL i in 1..l_tgt_res_asg_id_tab.count
633 UPDATE pa_resource_assignments
634 SET transaction_source_code = l_txn_src_code_tab(i),
635 last_update_date = l_sysdate,
636 last_updated_by = l_last_updated_by,
637 last_update_login = l_last_update_login,
638 record_version_number = record_version_number + 1
639 WHERE resource_assignment_id = l_tgt_res_asg_id_tab(i);
640
641 SELECT resource_assignment_id, target_res_asg_id
642 BULK COLLECT
643 INTO l_upd_ra_id_tab1,
644 l_upd_target_ra_id_tab1
645 FROM PA_FP_CALC_AMT_TMP1;
646
647 --hr_utility.trace('??l_upd_ra_id_tab1.count:'||l_upd_ra_id_tab1.count);
648 --for i in 1.. l_upd_ra_id_tab1.count LOOP
649 --hr_utility.trace('??l_upd_ra_id_tab1(i):'||l_upd_ra_id_tab1(i));
650 --hr_utility.trace('??l_upd_target_ra_id_tab1(i):'||l_upd_target_ra_id_tab1(i));
651 --END LOOP;
652 /*IF p_pa_debug_mode = 'Y' THEN
653 pa_fp_gen_amount_utils.fp_debug
654 (p_msg => 'Value of l_upd_ra_id_tab1.count: '||l_upd_ra_id_tab1.count,
655 p_module_name => l_module_name,
656 p_log_level => 5);
657 END IF;*/
658
659 FORALL i IN 1..l_upd_ra_id_tab1.count
660 UPDATE /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
661 PA_FP_CALC_AMT_TMP2
662 SET target_res_asg_id = l_upd_target_ra_id_tab1(i)
663 WHERE resource_assignment_id = l_upd_ra_id_tab1(i);
664
665 IF p_pa_debug_mode = 'Y' THEN
666 PA_DEBUG.RESET_CURR_FUNCTION;
667 END IF;
668 EXCEPTION
669 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
670 l_msg_count := FND_MSG_PUB.count_msg;
671 IF l_msg_count = 1 THEN
672 PA_INTERFACE_UTILS_PUB.GET_MESSAGES
673 ( p_encoded => FND_API.G_TRUE,
674 p_msg_index => 1,
675 p_msg_count => l_msg_count,
676 p_msg_data => l_msg_data,
677 p_data => l_data,
678 p_msg_index_out => l_msg_index_out);
679 x_msg_data := l_data;
680 x_msg_count := l_msg_count;
681 ELSE
682 x_msg_count := l_msg_count;
683 END IF;
684
685 ROLLBACK;
686 x_return_status := FND_API.G_RET_STS_ERROR;
687
688 IF p_pa_debug_mode = 'Y' THEN
689 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
690 (p_msg => 'Invalid Arguments Passed',
691 p_module_name => l_module_name,
692 p_log_level => 5);
693 PA_DEBUG.RESET_CURR_FUNCTION;
694 END IF;
695 RAISE;
696 WHEN OTHERS THEN
697 ROLLBACK;
698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
699 x_msg_count := 1;
700 x_msg_data := substr(sqlerrm,1,240);
701 FND_MSG_PUB.ADD_EXC_MSG
702 ( p_pkg_name => 'PA_FP_GEN_FCST_RMAP_PKG',
703 p_procedure_name => 'FCST_SRC_TXNS_RMAP',
704 p_error_text => substr(sqlerrm,1,240));
705
706 IF p_pa_debug_mode = 'Y' THEN
707 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
708 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
709 p_module_name => l_module_name,
710 p_log_level => 5);
711 PA_DEBUG.RESET_CURR_FUNCTION;
712 END IF;
713 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
714
715 END FCST_SRC_TXNS_RMAP;
716
717 END PA_FP_GEN_FCST_RMAP_PKG;