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