[Home] [Help]
PACKAGE BODY: APPS.PA_FP_GEN_PUB
Source
1 PACKAGE body PA_FP_GEN_PUB as
2 /* $Header: PAFPGNPB.pls 120.6.12010000.2 2009/06/25 11:01:43 rthumma ship $ */
3 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4
5 PROCEDURE UPDATE_RES_DEFAULTS
6 (P_PROJECT_ID IN pa_projects_all.PROJECT_ID%TYPE,
7 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
8 P_CALLED_MODE IN VARCHAR2,
9 P_COMMIT_FLAG IN VARCHAR2,
10 P_INIT_MSG_FLAG IN VARCHAR2,
11 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
12 X_MSG_COUNT OUT NOCOPY NUMBER,
13 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
14
15 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_PUB.UPDATE_RES_DEFAULTS';
16
17 l_last_updated_by NUMBER := FND_GLOBAL.user_id;
18 l_last_update_login NUMBER := FND_GLOBAL.login_id;
19 l_sysdate DATE := SYSDATE;
20 l_ret_status VARCHAR2(100);
21 l_msg_count NUMBER;
22 l_msg_data VARCHAR2(2000);
23 l_data VARCHAR2(2000);
24 l_msg_index_out NUMBER:=0;
25
26 --Bug 4895793 : Local Variables for calling get_resource_defaults API with DISTINCT rlm_ids.
27 l_resource_list_members_tab SYSTEM.PA_NUM_TBL_TYPE:=SYSTEM.PA_NUM_TBL_TYPE();
28 l_resource_class_flag_tab SYSTEM.PA_VARCHAR2_1_TBL_TYPE;
29 l_resource_class_code_tab SYSTEM.PA_VARCHAR2_30_TBL_TYPE;
30 l_resource_class_id_tab SYSTEM.PA_NUM_TBL_TYPE;
31 l_res_type_code_tab SYSTEM.PA_VARCHAR2_30_TBL_TYPE;
32 l_person_id_tab SYSTEM.PA_NUM_TBL_TYPE;
33 l_job_id_tab SYSTEM.PA_NUM_TBL_TYPE;
34 l_person_type_code_tab SYSTEM.PA_VARCHAR2_30_TBL_TYPE;
35 l_named_role_tab SYSTEM.PA_VARCHAR2_80_TBL_TYPE;
36 l_bom_resource_id_tab SYSTEM.PA_NUM_TBL_TYPE;
37 l_non_labor_resource_tab SYSTEM.PA_VARCHAR2_20_TBL_TYPE;
38 l_inventory_item_id_tab SYSTEM.PA_NUM_TBL_TYPE;
39 l_item_category_id_tab SYSTEM.PA_NUM_TBL_TYPE;
40 l_project_role_id_tab SYSTEM.PA_NUM_TBL_TYPE;
41 l_organization_id_tab SYSTEM.PA_NUM_TBL_TYPE;
42 l_fc_res_type_code_tab SYSTEM.PA_VARCHAR2_30_TBL_TYPE;
43 l_expenditure_type_tab SYSTEM.PA_VARCHAR2_30_TBL_TYPE;
44 l_expenditure_category_tab SYSTEM.PA_VARCHAR2_30_TBL_TYPE;
45 l_event_type_tab SYSTEM.PA_VARCHAR2_30_TBL_TYPE;
46 l_revenue_category_code_tab SYSTEM.PA_VARCHAR2_30_TBL_TYPE;
47 l_supplier_id_tab SYSTEM.PA_NUM_TBL_TYPE;
48 l_spread_curve_id_tab SYSTEM.PA_NUM_TBL_TYPE;
49 l_etc_method_code_tab SYSTEM.PA_VARCHAR2_30_TBL_TYPE;
50 l_mfc_cost_type_id_tab SYSTEM.PA_NUM_TBL_TYPE;
51 l_incurred_by_res_flag_tab SYSTEM.PA_VARCHAR2_1_TBL_TYPE;
52 l_incur_by_res_cls_code_tab SYSTEM.PA_VARCHAR2_30_TBL_TYPE;
53 l_incur_by_role_id_tab SYSTEM.PA_NUM_TBL_TYPE;
54 l_unit_of_measure_tab SYSTEM.PA_VARCHAR2_30_TBL_TYPE;
55 l_org_id_tab SYSTEM.PA_NUM_TBL_TYPE;
56 l_rate_based_flag_tab SYSTEM.PA_VARCHAR2_1_TBL_TYPE;
57 l_rate_expenditure_type_tab SYSTEM.PA_VARCHAR2_30_TBL_TYPE;
58 l_rate_func_curr_code_tab SYSTEM.PA_VARCHAR2_30_TBL_TYPE;
59 --l_rat_incured_by_org_id_tab SYSTEM.PA_NUM_TBL_TYPE;
60 l_incur_by_res_type_tab SYSTEM.PA_VARCHAR2_30_TBL_TYPE;
61
62 -- Maps rlm_ids to indexes for l_resource_list_members_tab
63 l_rlmid_index_map PA_PLSQL_DATATYPES.IdTabTyp;
64 l_index NUMBER;
65 l_dummy NUMBER;
66
67 --Local Variables for storing default attribute to be used in UPDATE.
68 l_da_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp; -- NEW
69 l_da_resource_list_members_tab PA_PLSQL_DATATYPES.IdTabTyp;
70 l_da_resource_class_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
71 l_da_resource_class_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
72 l_da_resource_class_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
73 l_da_res_type_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
74 l_da_person_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
75 l_da_job_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
76 l_da_person_type_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
77 l_da_named_role_tab PA_PLSQL_DATATYPES.Char80TabTyp;
78 l_da_bom_resource_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
79 l_da_non_labor_resource_tab PA_PLSQL_DATATYPES.Char20TabTyp;
80 l_da_inventory_item_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
81 l_da_item_category_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
82 l_da_project_role_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
83 l_da_organization_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
84 l_da_fc_res_type_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
85 l_da_expenditure_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
86 l_da_expenditure_category_tab PA_PLSQL_DATATYPES.Char30TabTyp;
87 l_da_event_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
88 l_da_revenue_category_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
89 l_da_supplier_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
90 l_da_spread_curve_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
91 l_da_etc_method_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
92 l_da_mfc_cost_type_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
93 l_da_incurred_by_res_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
94 l_da_incur_by_res_cls_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
95 l_da_incur_by_role_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
96 l_da_unit_of_measure_tab PA_PLSQL_DATATYPES.Char30TabTyp;
97 l_da_org_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
98 l_da_rate_based_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
99 l_da_rate_expenditure_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
100 l_da_rate_func_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
101 --l_da_rat_incured_by_org_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
102 l_da_incur_by_res_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
103
104 l_fp_cols_rec PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
105 l_etc_start_date DATE;
106 BEGIN
107 --Setting initial values
108 IF p_init_msg_flag = 'Y' THEN
109 FND_MSG_PUB.initialize;
110 END IF;
111
112 X_MSG_COUNT := 0;
113 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
114
115 IF p_pa_debug_mode = 'Y' AND p_init_msg_flag = 'Y' THEN
116 PA_DEBUG.init_err_stack('PA_FP_GEN_PUB.UPDATE_RES_DEFAULTS');
117 ELSIF p_pa_debug_mode = 'Y' AND p_init_msg_flag = 'N' THEN
118 pa_debug.set_curr_function( p_function => 'UPDATE_RES_DEFAULTS'
119 ,p_debug_mode => p_pa_debug_mode);
120 END IF;
121
122 -- 1. Bug 4895793: Get all target resources and their rlm_ids.
123 SELECT resource_assignment_id,
124 resource_list_member_id
125 BULK COLLECT
126 INTO l_da_ra_id_tab,
127 l_da_resource_list_members_tab
128 FROM pa_resource_assignments
129 WHERE budget_version_id = p_budget_version_id;
130
131 IF l_da_resource_list_members_tab.count = 0 then
132 IF p_pa_debug_mode = 'Y' AND p_init_msg_flag = 'Y' THEN
133 PA_DEBUG.reset_err_stack;
134 ELSIF p_pa_debug_mode = 'Y' AND p_init_msg_flag = 'N' THEN
135 PA_DEBUG.Reset_Curr_Function;
136 END IF;
137 RETURN;
138 END IF;
139
140 -- 2. Bug 4895793: Find the distinct rlm_ids from l_da_resource_list_members_tab
141 -- and store them in l_resource_list_members_tab. The l_rlmid_index_map
142 -- table stores (rlm_id, l_resource_list_members_tab index value) pairs.
143 -- The l_rlmid_index_map is used to determine if rlm_ids are distinct.
144
145 FOR i IN 1..l_da_resource_list_members_tab.count LOOP
146 -- If the current rlm_id is distinct, then add it to the
147 -- l_rlmid_index_map and l_resource_list_members_tab tables.
148 IF NOT l_rlmid_index_map.EXISTS(l_da_resource_list_members_tab(i)) THEN
149 l_rlmid_index_map(l_da_resource_list_members_tab(i)) :=
150 l_resource_list_members_tab.count + 1;
151 l_resource_list_members_tab.EXTEND;
152 l_resource_list_members_tab(l_resource_list_members_tab.count) :=
153 l_da_resource_list_members_tab(i);
154 END IF;
155 END LOOP;
156
157 -- 3. Bug 4895793: Get default attribute values for the distinct rlm_ids and store
158 -- them in the pl/sql tables prefixed by "l_" instead of by "l_da_".
159
160 --Calling resource defualt API
161 IF p_pa_debug_mode = 'Y' THEN
162 pa_fp_gen_amount_utils.fp_debug
163 (p_msg => 'Before calling
164 pa_planning_resource_utils.get_resource_defaults',
165 p_module_name => l_module_name,
166 p_log_level => 5);
167 END IF;
168
169 -- dbms_output.put_line('Value of x_msg_count, before calling get_res_def api: '||x_msg_count);
170 PA_PLANNING_RESOURCE_UTILS.get_resource_defaults (
171 P_resource_list_members => l_resource_list_members_tab,
172 P_project_id => p_project_id,
173 X_resource_class_flag => l_resource_class_flag_tab,
174 X_resource_class_code => l_resource_class_code_tab,
175 X_resource_class_id => l_resource_class_id_tab,
176 X_res_type_code => l_res_type_code_tab,
177 X_incur_by_res_type => l_incur_by_res_type_tab,
178 X_person_id => l_person_id_tab,
179 X_job_id => l_job_id_tab,
180 X_person_type_code => l_person_type_code_tab,
181 X_named_role => l_named_role_tab,
182 X_bom_resource_id => l_bom_resource_id_tab,
183 X_non_labor_resource => l_non_labor_resource_tab,
184 X_inventory_item_id => l_inventory_item_id_tab,
185 X_item_category_id => l_item_category_id_tab,
186 X_project_role_id => l_project_role_id_tab,
187 X_organization_id => l_organization_id_tab,
188 X_fc_res_type_code => l_fc_res_type_code_tab,
189 X_expenditure_type => l_expenditure_type_tab,
190 X_expenditure_category => l_expenditure_category_tab,
191 X_event_type => l_event_type_tab,
192 X_revenue_category_code => l_revenue_category_code_tab,
193 X_supplier_id => l_supplier_id_tab,
194 X_spread_curve_id => l_spread_curve_id_tab,
195 X_etc_method_code => l_etc_method_code_tab,
196 X_mfc_cost_type_id => l_mfc_cost_type_id_tab,
197 X_incurred_by_res_flag => l_incurred_by_res_flag_tab,
198 X_incur_by_res_class_code => l_incur_by_res_cls_code_tab,
199 X_incur_by_role_id => l_incur_by_role_id_tab,
200 X_unit_of_measure => l_unit_of_measure_tab,
201 X_org_id => l_org_id_tab,
202 X_rate_based_flag => l_rate_based_flag_tab,
203 X_rate_expenditure_type => l_rate_expenditure_type_tab,
204 X_rate_func_curr_code => l_rate_func_curr_code_tab,
205 --X_rate_incurred_by_org_id => l_rat_incured_by_org_id_tab,
206 X_msg_data => X_MSG_DATA,
207 X_msg_count => X_MSG_COUNT,
208 X_return_status => X_RETURN_STATUS);
209 --dbms_output.put_line('Value of x_msg_count, after calling get_res_def api: '||x_msg_count);
210
211
212 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
213 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
214 END IF;
215 IF p_pa_debug_mode = 'Y' THEN
216 pa_fp_gen_amount_utils.fp_debug
217 (p_msg => 'Status after calling
218 pa_planning_resource_utils.get_resource_defaults'
219 ||x_return_status,
220 p_module_name => l_module_name,
221 p_log_level => 5);
222 END IF;
223
224 -- 4. Bug 4895793: Populate the "l_" default attribute value tables to be used by the
225 -- UPDATE statement. The l_rlmid_index_map takes an rlm_id value and
226 -- returns the index for that rlm_id's default attributes in the "l_da_"
227 -- tables.
228
229 FOR i IN 1..l_da_ra_id_tab.count LOOP
230 IF NOT l_rlmid_index_map.EXISTS(l_da_resource_list_members_tab(i)) THEN
231 -- Error handling code goes here. This should never happen.
232 l_dummy := 1;
233 END IF;
234
235 l_index := l_rlmid_index_map(l_da_resource_list_members_tab(i));
236
237 l_da_resource_class_flag_tab(i) := l_resource_class_flag_tab(l_index);
238 l_da_resource_class_code_tab(i) := l_resource_class_code_tab(l_index);
239 l_da_resource_class_id_tab(i) := l_resource_class_id_tab(l_index);
240 l_da_res_type_code_tab(i) := l_res_type_code_tab(l_index);
241 l_da_person_id_tab(i) := l_person_id_tab(l_index);
242 l_da_job_id_tab(i) := l_job_id_tab(l_index);
243 l_da_person_type_code_tab(i) := l_person_type_code_tab(l_index);
244 l_da_named_role_tab(i) := l_named_role_tab(l_index);
245 l_da_bom_resource_id_tab(i) := l_bom_resource_id_tab(l_index);
246 l_da_non_labor_resource_tab(i) := l_non_labor_resource_tab(l_index);
247 l_da_inventory_item_id_tab(i) := l_inventory_item_id_tab(l_index);
248 l_da_item_category_id_tab(i) := l_item_category_id_tab(l_index);
249 l_da_project_role_id_tab(i) := l_project_role_id_tab(l_index);
250 l_da_organization_id_tab(i) := l_organization_id_tab(l_index);
251 l_da_fc_res_type_code_tab(i) := l_fc_res_type_code_tab(l_index);
252 l_da_expenditure_type_tab(i) := l_expenditure_type_tab(l_index);
253 l_da_expenditure_category_tab(i) := l_expenditure_category_tab(l_index);
254 l_da_event_type_tab(i) := l_event_type_tab(l_index);
255 l_da_revenue_category_code_tab(i) := l_revenue_category_code_tab(l_index);
256 l_da_supplier_id_tab(i) := l_supplier_id_tab(l_index);
257 l_da_spread_curve_id_tab(i) := l_spread_curve_id_tab(l_index);
258 l_da_etc_method_code_tab(i) := l_etc_method_code_tab(l_index);
259 l_da_mfc_cost_type_id_tab(i) := l_mfc_cost_type_id_tab(l_index);
260 l_da_incurred_by_res_flag_tab(i) := l_incurred_by_res_flag_tab(l_index);
261 l_da_incur_by_res_cls_code_tab(i) := l_incur_by_res_cls_code_tab(l_index);
262 l_da_incur_by_role_id_tab(i) := l_incur_by_role_id_tab(l_index);
263 l_da_unit_of_measure_tab(i) := l_unit_of_measure_tab(l_index);
264 l_da_org_id_tab(i) := l_org_id_tab(l_index);
265 l_da_rate_based_flag_tab(i) := l_rate_based_flag_tab(l_index);
266 l_da_rate_expenditure_type_tab(i) := l_rate_expenditure_type_tab(l_index);
267 l_da_rate_func_curr_code_tab(i) := l_rate_func_curr_code_tab(l_index);
268 --l_da_rat_incured_by_org_id_tab(i) := l_rat_incured_by_org_id_tab(l_index);
269 l_da_incur_by_res_type_tab(i) := l_incur_by_res_type_tab(l_index);
270
271 END LOOP;
272
273
274 -- Bug 4143869: Added call to GET_PLAN_VERSION_DTLS to get the value of the
275 -- Retain Maually Added Lines flag. Also, added manual lines logic to the
276 -- UPDATE statement for pa_resource_assignments.
277
278 IF P_PA_DEBUG_MODE = 'Y' THEN
279 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
280 P_CALLED_MODE => P_CALLED_MODE,
281 P_MSG => 'Before calling PA_FP_GEN_AMOUNT_UTILS.'||
282 'GET_PLAN_VERSION_DTL',
283 P_MODULE_NAME => l_module_name);
284 END IF;
285 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS(
286 P_PROJECT_ID => p_project_id,
287 P_BUDGET_VERSION_ID => p_budget_version_id,
288 X_FP_COLS_REC => l_fp_cols_rec,
289 X_RETURN_STATUS => x_return_status,
290 X_MSG_COUNT => x_msg_count,
291 X_MSG_DATA => x_msg_data);
292 IF P_PA_DEBUG_MODE = 'Y' THEN
293 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
294 P_CALLED_MODE => P_CALLED_MODE,
295 P_MSG => 'After calling PA_FP_GEN_AMOUNT_UTILS.'||
296 'GET_PLAN_VERSION_DTL:'||x_return_status,
297 P_MODULE_NAME => l_module_name);
298 END IF;
299 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
300 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
301 END IF;
302
303 /* 5. Bug 4895793 : Update resource attributes by resource_assignment_id
304 instead of by (budget_version_id, resource_list_member_id).*/
305
306 -- IPM: At the time of resource creation, the resource_rate_based_flag
307 -- should be set based on the default rate_based_flag for the resource.
308 -- Modified the Update statements below to set resource_rate_based_flag.
309 -- Note that this API is used exclusively by the Forecast Generation
310 -- process and is called by CREATE_RES_ASG in PAFPCAPB.pls.
311
312 IF l_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'N' THEN
313 FORALL i IN 1 .. l_da_ra_id_tab.count --l_da_resource_list_members_tab.count Bug 4895793
314 UPDATE PA_RESOURCE_ASSIGNMENTS RA
315 SET RESOURCE_CLASS_FLAG = l_da_resource_class_flag_tab(i),
316 RESOURCE_CLASS_CODE = l_da_resource_class_code_tab(i),
317 RES_TYPE_CODE = l_da_res_type_code_tab(i),
318 PERSON_ID = l_da_person_id_tab(i),
319 JOB_ID = l_da_job_id_tab(i),
320 PERSON_TYPE_CODE = l_da_person_type_code_tab(i),
321 NAMED_ROLE = l_da_named_role_tab(i),
322 BOM_RESOURCE_ID = l_da_bom_resource_id_tab(i),
323 NON_LABOR_RESOURCE = l_da_non_labor_resource_tab(i),
324 INVENTORY_ITEM_ID = l_da_inventory_item_id_tab(i),
325 ITEM_CATEGORY_ID = l_da_item_category_id_tab(i),
326 PROJECT_ROLE_ID = l_da_project_role_id_tab(i),
327 ORGANIZATION_ID = l_da_organization_id_tab(i),
328 FC_RES_TYPE_CODE = l_da_fc_res_type_code_tab(i),
329 EXPENDITURE_TYPE = l_da_expenditure_type_tab(i),
330 EXPENDITURE_CATEGORY = l_da_expenditure_category_tab(i),
331 EVENT_TYPE = l_da_event_type_tab(i),
332 REVENUE_CATEGORY_CODE = l_da_revenue_category_code_tab(i),
333 SUPPLIER_ID = l_da_supplier_id_tab(i),
334 SPREAD_CURVE_ID = l_da_spread_curve_id_tab(i),
335 ETC_METHOD_CODE = l_da_etc_method_code_tab(i),
336 MFC_COST_TYPE_ID = l_da_mfc_cost_type_id_tab(i),
337 INCURRED_BY_RES_FLAG = l_da_incurred_by_res_flag_tab(i),
338 INCUR_BY_RES_CLASS_CODE = l_da_incur_by_res_cls_code_tab(i),
339 INCUR_BY_ROLE_ID = l_da_incur_by_role_id_tab(i),
340 UNIT_OF_MEASURE = l_da_unit_of_measure_tab(i),
341 RATE_BASED_FLAG = l_da_rate_based_flag_tab(i),
342 RESOURCE_RATE_BASED_FLAG = l_da_rate_based_flag_tab(i), -- Added for IPM ER
343 RATE_EXPENDITURE_TYPE = l_da_rate_expenditure_type_tab(i),
344 RATE_EXP_FUNC_CURR_CODE = l_da_rate_func_curr_code_tab(i),
345 --RATE_INCURRED_BY_ORGANZ_ID = l_da_rat_incured_by_org_id_tab(i),
346 LAST_UPDATE_DATE = l_sysdate,
347 LAST_UPDATED_BY = l_last_updated_by,
348 CREATION_DATE = l_sysdate,
349 CREATED_BY = l_last_updated_by,
350 LAST_UPDATE_LOGIN = l_last_update_login,
351 PROJECT_ASSIGNMENT_ID = -1,
352 RATE_EXPENDITURE_ORG_ID = l_da_org_id_tab(i)
353 WHERE resource_assignment_id = l_da_ra_id_tab(i);
354 --budget_version_id = p_budget_version_id
355 --AND RESOURCE_LIST_MEMBER_ID = l_da_resource_list_members_tab(i);
356 ELSIF l_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'Y' THEN
357 IF l_fp_cols_rec.x_plan_class_code = 'FORECAST' THEN
358 l_etc_start_date :=
359 PA_FP_GEN_AMOUNT_UTILS.GET_ETC_START_DATE(p_budget_version_id);
360 END IF;
361 FORALL i IN 1 .. l_da_ra_id_tab.count --l_da_resource_list_members_tab.count Bug 4895793.
362 UPDATE PA_RESOURCE_ASSIGNMENTS RA
363 SET RESOURCE_CLASS_FLAG = l_da_resource_class_flag_tab(i),
364 RESOURCE_CLASS_CODE = l_da_resource_class_code_tab(i),
365 RES_TYPE_CODE = l_da_res_type_code_tab(i),
366 PERSON_ID = l_da_person_id_tab(i),
367 JOB_ID = l_da_job_id_tab(i),
368 PERSON_TYPE_CODE = l_da_person_type_code_tab(i),
369 NAMED_ROLE = l_da_named_role_tab(i),
370 BOM_RESOURCE_ID = l_da_bom_resource_id_tab(i),
371 NON_LABOR_RESOURCE = l_da_non_labor_resource_tab(i),
372 INVENTORY_ITEM_ID = l_da_inventory_item_id_tab(i),
373 ITEM_CATEGORY_ID = l_da_item_category_id_tab(i),
374 PROJECT_ROLE_ID = l_da_project_role_id_tab(i),
375 ORGANIZATION_ID = l_da_organization_id_tab(i),
376 FC_RES_TYPE_CODE = l_da_fc_res_type_code_tab(i),
377 EXPENDITURE_TYPE = l_da_expenditure_type_tab(i),
378 EXPENDITURE_CATEGORY = l_da_expenditure_category_tab(i),
379 EVENT_TYPE = l_da_event_type_tab(i),
380 REVENUE_CATEGORY_CODE = l_da_revenue_category_code_tab(i),
381 SUPPLIER_ID = l_da_supplier_id_tab(i),
382 SPREAD_CURVE_ID = l_da_spread_curve_id_tab(i),
383 ETC_METHOD_CODE = l_da_etc_method_code_tab(i),
384 MFC_COST_TYPE_ID = l_da_mfc_cost_type_id_tab(i),
385 INCURRED_BY_RES_FLAG = l_da_incurred_by_res_flag_tab(i),
386 INCUR_BY_RES_CLASS_CODE = l_da_incur_by_res_cls_code_tab(i),
387 INCUR_BY_ROLE_ID = l_da_incur_by_role_id_tab(i),
388 UNIT_OF_MEASURE = l_da_unit_of_measure_tab(i),
389 RATE_BASED_FLAG = l_da_rate_based_flag_tab(i),
390 RESOURCE_RATE_BASED_FLAG = l_da_rate_based_flag_tab(i), -- Added for IPM ER
391 RATE_EXPENDITURE_TYPE = l_da_rate_expenditure_type_tab(i),
392 RATE_EXP_FUNC_CURR_CODE = l_da_rate_func_curr_code_tab(i),
393 --RATE_INCURRED_BY_ORGANZ_ID = l_da_rat_incured_by_org_id_tab(i),
394 LAST_UPDATE_DATE = l_sysdate,
395 LAST_UPDATED_BY = l_last_updated_by,
396 CREATION_DATE = l_sysdate,
397 CREATED_BY = l_last_updated_by,
398 LAST_UPDATE_LOGIN = l_last_update_login,
399 PROJECT_ASSIGNMENT_ID = -1,
400 RATE_EXPENDITURE_ORG_ID = l_da_org_id_tab(i)
401 WHERE resource_assignment_id = l_da_ra_id_tab(i)
402 --budget_version_id = p_budget_version_id
403 --AND RESOURCE_LIST_MEMBER_ID = l_da_resource_list_members_tab(i)
404 AND ( ra.transaction_source_code IS NOT NULL
405 OR ( ra.transaction_source_code IS NULL
406 AND NOT EXISTS ( SELECT 1
407 FROM pa_budget_lines bl
408 WHERE bl.resource_assignment_id =
409 ra.resource_assignment_id
410 AND bl.start_date >=
411 DECODE(l_fp_cols_rec.x_plan_class_code,
412 'FORECAST', l_etc_start_date,
413 bl.start_date)
414 AND rownum = 1 )));
415 END IF;
416
417 IF p_pa_debug_mode = 'Y' AND p_init_msg_flag = 'Y' THEN
418 PA_DEBUG.reset_err_stack;
419 ELSIF p_pa_debug_mode = 'Y' AND p_init_msg_flag = 'N' THEN
420 PA_DEBUG.Reset_Curr_Function;
421 END IF;
422 RETURN;
423
424 EXCEPTION
425 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
426 /* MRC Elimination changes: PA_MRC_FINPLAN.G_CALLING_MODULE := Null; **/
427 l_msg_count := FND_MSG_PUB.count_msg;
428 IF l_msg_count = 1 THEN
429 PA_INTERFACE_UTILS_PUB.get_messages
430 (p_encoded => FND_API.G_TRUE
431 ,p_msg_index => 1
432 ,p_msg_count => l_msg_count
433 ,p_msg_data => l_msg_data
434 ,p_data => l_data
435 ,p_msg_index_out => l_msg_index_out);
436 x_msg_data := l_data;
437 x_msg_count := l_msg_count;
438 ELSE
439 x_msg_count := l_msg_count;
440 END IF;
441 ROLLBACK;
442 x_return_status := FND_API.G_RET_STS_ERROR;
443 IF p_pa_debug_mode = 'Y' AND p_init_msg_flag = 'Y' THEN
444 PA_DEBUG.reset_err_stack;
445 ELSIF p_pa_debug_mode = 'Y' AND p_init_msg_flag = 'N' THEN
446 PA_DEBUG.Reset_Curr_Function;
447 END IF;
448
449 WHEN OTHERS THEN
450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
451 x_msg_data := SUBSTR(SQLERRM,1,240);
452 -- dbms_output.put_line('inside excep create res asg');
453 -- dbms_output.put_line(SUBSTR(SQLERRM,1,240));
454 FND_MSG_PUB.add_exc_msg
455 ( p_pkg_name => 'PA_FP_GEN_PUB'
456 ,p_procedure_name => 'UPDATE_RES_DEFAULTS');
457
458 IF p_pa_debug_mode = 'Y' AND p_init_msg_flag = 'Y' THEN
459 PA_DEBUG.reset_err_stack;
460 ELSIF p_pa_debug_mode = 'Y' AND p_init_msg_flag = 'N' THEN
461 PA_DEBUG.Reset_Curr_Function;
462 END IF;
463 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
464
465 END UPDATE_RES_DEFAULTS;
466
467 PROCEDURE INCLUDE_CHANGE_DOCUMENT_WRP
468 (P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
469 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
470 X_MSG_COUNT OUT NOCOPY NUMBER,
471 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
472
473 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_PUB.INCLUDE_CHANGE_DOCUMENT_WRP';
474
475 l_ci_id_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.PA_NUM_TBL_TYPE();
476 l_translated_msgs_tbl SYSTEM.pa_varchar2_2000_tbl_type;
477 l_translated_err_msg_count NUMBER;
478 l_translated_err_msg_level_tbl SYSTEM.pa_varchar2_30_tbl_type;
479 l_budget_version_id_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.PA_NUM_TBL_TYPE();
480 l_impl_cost_flag_tbl SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
481 l_impl_rev_flag_tbl SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
482 l_msg_count number(15);
483 l_msg_index_out number(15);
484 l_msg_data varchar2(1000);
485 l_data varchar2(1000);
486 l_calling_context varchar2(30);
487
488 l_raTxn_rollup_api_call_flag VARCHAR2(1) := 'N'; -- Added for IPM new entity ER
489 BEGIN
490 --hr_utility.trace_on(null,'Sharmila');
491 /* Setting initial values */
492 X_MSG_COUNT := 0;
493 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
494
495 IF p_pa_debug_mode = 'Y' THEN
496 pa_debug.set_curr_function( p_function => 'INCLUDE_CHANGE_DOCUMENT_WRP'
497 ,p_debug_mode => p_pa_debug_mode);
498 END IF;
499
500 -- Bug 5845142
501 IF Pa_Fp_Control_Items_Utils.check_valid_combo
502 ( p_project_id => p_fp_cols_rec.x_project_id
503 ,p_targ_app_cost_flag => 'N'
504 ,p_targ_app_rev_flag => 'N') = 'N' THEN
505
506 IF P_PA_DEBUG_MODE = 'Y' THEN
507 PA_DEBUG.Reset_Curr_Function;
508 END IF;
509 RETURN;
510
511 END IF;
512
513 -- Modified Select statement for adding distinct clause - Bug 3749556
514 SELECT /* pfc.ci_type_name as cd_type
515 ,pfc.cd_number as cd_number
516 ,pfc.summary as summary
517 ,pfc.task_no as task_no
518 ,pfc.project_status_name as project_status_name
519 ,pal.meaning as project_system_status
520 ,pfc.people_effort as people_effort
521 ,pfc.equipment_effort as equipment_effort
522 ,PA_FP_CONTROL_ITEMS_UTILS.get_cost
523 (CI_VERSION_TYPE,p_fp_cols_rec.x_budget_version_id,
524 CI_VERSION_ID,RAW_COST,BURDENED_COST) as cost
525 ,PA_FP_CONTROL_ITEMS_UTILS.get_revenue_partial
526 (CI_VERSION_TYPE,p_fp_cols_rec.x_budget_version_id,
527 CI_VERSION_ID,REVENUE) as revenue
528 ,'0' as margin
529 ,'0' as margin_percent */
530 distinct pfc.ci_id as ci_id
531 /* ,pci.ci_type_class_code as ci_type_class_code */
532 BULK COLLECT
533 INTO l_ci_id_tbl
534 FROM pa_fp_eligible_ci_v pfc,
535 pa_lookups pal
536 -- ,pa_ci_types_vl pci
537 WHERE pfc.project_id = p_fp_cols_rec.x_project_id
538 AND pfc.fin_plan_type_id = p_fp_cols_rec.x_fin_plan_type_id
539 AND CI_VERSION_TYPE <> decode(p_fp_cols_rec.x_version_type,
540 'COST','REVENUE',
541 'REVENUE','COST',
542 'ALL','-99')
543 AND decode (CI_VERSION_TYPE,
544 'ALL',PT_CT_VERSION_TYPE,
545 CI_VERSION_TYPE) = PT_CT_VERSION_TYPE
546 AND (pfc.REV_PARTIALLY_IMPL_FLAG='Y'
547 OR (pfc.ci_version_type='ALL' AND
548 decode(p_fp_cols_rec.x_version_type,'ALL',2,1) >
549 (SELECT COUNT(*)
550 FROM pa_fp_merged_ctrl_items merge
551 WHERE merge.ci_plan_version_id = pfc.ci_version_id
552 AND merge.plan_version_id = p_fp_cols_rec.x_budget_version_id))
553 OR (pfc.ci_version_type <> 'ALL' AND
554 NOT EXISTS (SELECT 'X'
555 FROM pa_fp_merged_ctrl_items merge
556 WHERE merge.ci_plan_version_id = pfc.ci_version_id
557 AND merge.plan_version_id = p_fp_cols_rec.x_budget_version_id
558 AND merge.version_type = pfc.ci_version_type)))
559 AND pfc.project_system_status_code = pal.lookup_code
560 AND pal.lookup_type = 'CONTROL_ITEM_SYSTEM_STATUS';
561 -- AND pfc.ci_type_id = pci.ci_type_id;
562
563 IF l_ci_id_tbl.count = 0 THEN
564 IF p_pa_debug_mode = 'Y' THEN
565 pa_fp_gen_amount_utils.fp_debug
566 (p_msg => 'No CIs to implement. no rows returned from the view.Returning',
567 p_module_name => l_module_name,
568 p_log_level => 5);
569 END IF;
570 IF P_PA_DEBUG_MODE = 'Y' THEN
571 PA_DEBUG.Reset_Curr_Function;
572 END IF;
573 RETURN;
574 END IF;
575 l_budget_version_id_tbl.extend;
576 l_budget_version_id_tbl(1) := P_FP_COLS_REC.X_BUDGET_VERSION_ID;
577
578 IF p_fp_cols_rec.x_plan_class_code = 'BUDGET' THEN
579 l_calling_context := 'BUDGET_GENERATION';
580 ELSIF p_fp_cols_rec.x_plan_class_code = 'FORECAST' THEN
581 l_calling_context := 'FORECAST_GENERATION';
582 END IF;
583
584 /* Added PA_FP_MULTI_CURRENCY_PKG.CONVERT_TXN_CURRENCY and PA_FP_ROLLUP_PKG.ROLLUP_BUDGET_VERSION APIs
585 to include change orders. Bug 3985706 */
586
587 IF P_PA_DEBUG_MODE = 'Y' THEN
588 pa_fp_gen_amount_utils.fp_debug
589 (p_msg => 'Before calling PA_FP_MULTI_CURRENCY_PKG.CONVERT_TXN_CURRENCY',
590 p_module_name => l_module_name,
591 p_log_level => 5);
592 END IF;
593 PA_FP_MULTI_CURRENCY_PKG.CONVERT_TXN_CURRENCY
594 (p_budget_version_id => P_FP_COLS_REC.X_BUDGET_VERSION_ID,
595 p_entire_version => 'Y',
596 p_calling_module => 'BUDGET_GENERATION', -- Added for Bug#5395732
597 X_RETURN_STATUS => X_RETURN_STATUS,
598 X_MSG_COUNT => X_MSG_COUNT,
599 X_MSG_DATA => X_MSG_DATA);
600 IF P_PA_DEBUG_MODE = 'Y' THEN
601 pa_fp_gen_amount_utils.fp_debug
602 (p_msg => 'After calling PA_FP_MULTI_CURRENCY_PKG.CONVERT_TXN_CURRENCY,
603 ret status: '||x_return_status,
604 p_module_name => l_module_name,
605 p_log_level => 5);
606 END IF;
607 --dbms_output.put_line('After calling convert_txn_currency api: '||x_return_status);
608 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
609 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
610 END IF;
611
612 IF P_PA_DEBUG_MODE = 'Y' THEN
613 pa_fp_gen_amount_utils.fp_debug
614 (p_msg => 'Before calling PA_FP_ROLLUP_PKG.ROLLUP_BUDGET_VERSION',
615 p_module_name => l_module_name,
616 p_log_level => 5);
617 END IF;
618 PA_FP_ROLLUP_PKG.ROLLUP_BUDGET_VERSION
619 (p_budget_version_id => P_FP_COLS_REC.X_BUDGET_VERSION_ID,
620 p_entire_version => 'Y',
621 X_RETURN_STATUS => X_RETURN_STATUS,
622 X_MSG_COUNT => X_MSG_COUNT,
623 X_MSG_DATA => X_MSG_DATA);
624 IF P_PA_DEBUG_MODE = 'Y' THEN
625 pa_fp_gen_amount_utils.fp_debug
626 (p_msg => 'After calling PA_FP_ROLLUP_PKG.ROLLUP_BUDGET_VERSION,
627 ret status: '||x_return_status,
628 p_module_name => l_module_name,
629 p_log_level => 5);
630 END IF;
631 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
632 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
633 END IF;
634
635
636 IF p_pa_debug_mode = 'Y' THEN
637 pa_fp_gen_amount_utils.fp_debug
638 (p_msg => 'Before calling
639 pa_fp_ci_merge.implement_change_document',
640 p_module_name => l_module_name,
641 p_log_level => 5);
642 END IF;
643 PA_FP_CI_MERGE.implement_change_document
644 (p_context =>
645 'INCLUDE',
646 p_calling_context =>
647 l_calling_context,
648 p_ci_id_tbl =>
649 l_ci_id_tbl,
650 p_budget_version_id_tbl =>
651 l_budget_version_id_tbl,
652 p_impl_cost_flag_tbl =>
653 l_impl_cost_flag_tbl,
654 p_impl_rev_flag_tbl =>
655 l_impl_rev_flag_tbl,
656 p_raTxn_rollup_api_call_flag =>
657 l_raTxn_rollup_api_call_flag, --Added for IPM new entity ER
658 x_translated_msgs_tbl =>
659 l_translated_msgs_tbl,
660 x_translated_err_msg_count =>
661 l_translated_err_msg_count,
662 x_translated_err_msg_level_tbl =>
663 l_translated_err_msg_level_tbl,
664 x_return_status =>
665 x_return_status,
666 x_msg_count =>
667 x_msg_count,
668 x_msg_data =>
669 x_msg_data);
670 IF p_pa_debug_mode = 'Y' THEN
671 pa_fp_gen_amount_utils.fp_debug
672 (p_msg => 'Status after calling pa_fp_ci_merge.implement_change_document'
673 ||x_return_status,
674 p_module_name => l_module_name,
675 p_log_level => 5);
676 END IF;
677
678 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
679 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
680 END IF;
681
682 IF P_PA_DEBUG_MODE = 'Y' THEN
683 PA_DEBUG.Reset_Curr_Function;
684 END IF;
685
686
687 EXCEPTION
688 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
689 l_msg_count := FND_MSG_PUB.count_msg;
690 IF l_msg_count = 1 THEN
691 PA_INTERFACE_UTILS_PUB.get_messages
692 (p_encoded => FND_API.G_TRUE
693 ,p_msg_index => 1
694 ,p_msg_count => l_msg_count
695 ,p_msg_data => l_msg_data
696 ,p_data => l_data
697 ,p_msg_index_out => l_msg_index_out);
698 x_msg_data := l_data;
699 x_msg_count := l_msg_count;
700 ELSE
701 x_msg_count := l_msg_count;
702 END IF;
703 ROLLBACK;
704 x_return_status := FND_API.G_RET_STS_ERROR;
705
706 IF P_PA_DEBUG_MODE = 'Y' THEN
707 PA_DEBUG.Reset_Curr_Function;
708 END IF;
709 WHEN OTHERS THEN
710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
711 x_msg_data := SUBSTR(SQLERRM,1,240);
712 FND_MSG_PUB.add_exc_msg
713 ( p_pkg_name => 'PA_FP_GEN_PUB'
714 ,p_procedure_name => 'INCLUDE_CHANGE_DOCUMENT_WRP');
715 IF P_PA_DEBUG_MODE = 'Y' THEN
716 PA_DEBUG.Reset_Curr_Function;
717 END IF;
718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
719
720 END INCLUDE_CHANGE_DOCUMENT_WRP;
721
722 PROCEDURE UNSPENT_AMOUNT
723 (P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
724 P_APP_COST_BDGT_VER_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
725 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
726 P_UNSPENT_AMT_PERIOD IN VARCHAR2,
727 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
728 X_MSG_COUNT OUT NOCOPY NUMBER,
729 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
730
731 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_PUB.UNSPENT_AMOUNT';
732
733 l_fp_cols_rec_app_cost PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
734
735 l_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
736 l_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
737 l_rate_based_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
738 l_res_list_mem_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
739 l_planning_start_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
740 l_planning_end_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
741
742 l_etc_start_date PA_BUDGET_VERSIONS.ETC_START_DATE%TYPE;
743 l_time_phase PA_PROJ_FP_OPTIONS.COST_TIME_PHASED_CODE%TYPE;
744 l_start_date PA_BUDGET_LINES.START_DATE%TYPE;
745 l_end_date PA_BUDGET_LINES.END_DATE%TYPE;
746 l_period_name PA_BUDGET_LINES.PERIOD_NAME%TYPE;
747 l_pc_currency_code PA_PROJECTS_ALL.PROJECT_CURRENCY_CODE%TYPE;
748 l_pfc_currency_code PA_PROJECTS_ALL.PROJFUNC_CURRENCY_CODE%TYPE;
749
750 /* Plan amount pl/sql tables */
751 l_plan_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
752 l_plan_txn_cur_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
753 l_plan_qty_tab PA_PLSQL_DATATYPES.NumTabTyp;
754 l_plan_pc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
755 l_plan_txn_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
756 l_plan_pc_burd_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
757 l_plan_txn_burd_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
758
759 /* Actual amount pl/sql tables */
760 l_init_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
761 l_init_txn_cur_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
762 l_init_qty_tab PA_PLSQL_DATATYPES.NumTabTyp;
763 l_init_pc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
764 l_init_txn_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
765 l_init_pc_burd_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
766 l_init_txn_burd_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
767
768 /* Indices for Plan and Actual pl/sql tables */
769 p_index NUMBER;
770 i_index NUMBER;
771 l_prev_i_index NUMBER;
772 l_actuals_exist_flag VARCHAR2(1);
773
774 l_curr_ra_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE;
775
776 /* Scalar variables for summing Plan amounts per ra_id */
777 l_plan_qty PA_BUDGET_LINES.QUANTITY%TYPE;
778 l_plan_pc_raw_cost PA_BUDGET_LINES.PROJECT_RAW_COST%TYPE;
779 l_plan_txn_raw_cost PA_BUDGET_LINES.TXN_RAW_COST%TYPE;
780 l_plan_pc_burd_cost PA_BUDGET_LINES.PROJECT_BURDENED_COST%TYPE;
781 l_plan_txn_burd_cost PA_BUDGET_LINES.TXN_BURDENED_COST%TYPE;
782 l_plan_currency_tab PA_PLSQL_DATATYPES.Char30TabTyp;
783
784 /* Scalar variables for summing Actual amounts per ra_id */
785 l_init_qty PA_BUDGET_LINES.QUANTITY%TYPE;
786 l_init_pc_raw_cost PA_BUDGET_LINES.PROJECT_RAW_COST%TYPE;
787 l_init_txn_raw_cost PA_BUDGET_LINES.TXN_RAW_COST%TYPE;
788 l_init_pc_burd_cost PA_BUDGET_LINES.PROJECT_BURDENED_COST%TYPE;
789 l_init_txn_burd_cost PA_BUDGET_LINES.TXN_BURDENED_COST%TYPE;
790 l_init_currency_tab PA_PLSQL_DATATYPES.Char30TabTyp;
791
792 /* Variables for unspent amounts per ra_id */
793 l_unspent_amt_currency PA_BUDGET_LINES.TXN_CURRENCY_CODE%TYPE;
794 l_unspent_qty PA_BUDGET_LINES.QUANTITY%TYPE;
795 l_unspent_txn_raw_cost PA_BUDGET_LINES.TXN_RAW_COST%TYPE;
796 l_unspent_txn_burd_cost PA_BUDGET_LINES.TXN_BURDENED_COST%TYPE;
797
798 l_txn_raw_cost_rate NUMBER;
799 l_txn_burd_cost_rate NUMBER;
800
801 /* Variables for insert/update of Unspent Amount budget lines */
802 l_insert_flag VARCHAR2(1);
803 l_update_flag VARCHAR2(1);
804 l_upd_bl_id PA_BUDGET_LINES.BUDGET_LINE_ID%TYPE;
805 l_index NUMBER;
806
807 /* Variables for amounts of budget lines to be updated */
808 l_quantity PA_BUDGET_LINES.QUANTITY%TYPE;
809 l_txn_raw_cost PA_BUDGET_LINES.TXN_RAW_COST%TYPE;
810 l_txn_burdened_cost PA_BUDGET_LINES.TXN_BURDENED_COST%TYPE;
811
812 l_last_updated_by PA_BUDGET_LINES.LAST_UPDATED_BY%TYPE := FND_GLOBAL.user_id;
813 l_last_update_login PA_BUDGET_LINES.LAST_UPDATE_LOGIN%TYPE := FND_GLOBAL.login_id;
814 l_sysdate DATE := SYSDATE;
815
816 /* Tables for budget line Insert */
817 l_ins_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
818 l_ins_start_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
819 l_ins_end_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
820 l_ins_txn_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
821 l_ins_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
822 l_ins_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
823 l_ins_burd_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
824 l_ins_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
825 l_ins_burd_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
826
827 /* Tables for budget line Update */
828 l_upd_bl_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
829 l_upd_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
830 l_upd_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
831 l_upd_burd_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
832
833 /* Variables for Fixed Dates spread curve logic */
834 lc_fixed_date_code VARCHAR2(30) := 'FIXED_DATE';
835 l_fixed_date_curve_id PA_RESOURCE_ASSIGNMENTS.SPREAD_CURVE_ID%TYPE;
836 l_fixed_date_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
837
838 l_count NUMBER;
839 l_msg_count NUMBER;
840 l_data VARCHAR2(1000);
841 l_msg_data VARCHAR2(1000);
842 l_msg_index_out NUMBER;
843 BEGIN
844
845 /* Setting initial values */
846 X_MSG_COUNT := 0;
847 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
848
849 IF p_pa_debug_mode = 'Y' THEN
850 pa_debug.set_curr_function( p_function => 'UNSPENT_AMOUNT'
851 ,p_debug_mode => p_pa_debug_mode);
852 END IF;
853
854 --dbms_output.put_line('p_app_cost_bdgt_ver_id = ' || p_app_cost_bdgt_ver_id);
855
856 /* Calling the get_plan_version_dtls api
857 for the given app_cost_bdgt_ver_id*/
858 IF p_pa_debug_mode = 'Y' THEN
859 pa_fp_gen_amount_utils.fp_debug
860 (p_msg => 'Before calling
861 pa_fp_gen_amount_utils.get_plan_version_dtls',
862 p_module_name => l_module_name,
863 p_log_level => 5);
864 END IF;
865 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS
866 (P_PROJECT_ID => p_fp_cols_rec.x_project_id,
867 P_BUDGET_VERSION_ID => p_app_cost_bdgt_ver_id,
868 X_FP_COLS_REC => l_fp_cols_rec_app_cost,
869 X_RETURN_STATUS => X_RETURN_STATUS,
870 X_MSG_COUNT => X_MSG_COUNT,
871 X_MSG_DATA => X_MSG_DATA);
872 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
873 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
874 END IF;
875 IF p_pa_debug_mode = 'Y' THEN
876 pa_fp_gen_amount_utils.fp_debug
877 (p_msg => 'Status after calling
878 pa_fp_gen_amount_utils.get_plan_version_dtls'
879 ||x_return_status,
880 p_module_name => l_module_name,
881 p_log_level => 5);
882 END IF;
883
884 /* We are mapping the approved cost budget version
885 planning attributes to target budget version
886 resource list and the amounts will be
887 populated in the pa_fp_calc_amt_tmp3 table only
888 for the periods till the actual thru period */
889
890 IF P_PA_DEBUG_MODE = 'Y' THEN
891 pa_fp_gen_amount_utils.fp_debug
892 (p_msg => 'Before calling
893 pa_fp_map_bv_pub.gen_map_bv_to_target_rl',
894 p_module_name => l_module_name,
895 p_log_level => 5);
896 END IF;
897 PA_FP_MAP_BV_PUB.GEN_MAP_BV_TO_TARGET_RL
898 (P_SOURCE_BV_ID => p_app_cost_bdgt_ver_id,
899 P_TARGET_FP_COLS_REC => p_fp_cols_rec,
900 P_ETC_FP_COLS_REC => p_fp_cols_rec,
901 P_CB_FP_COLS_REC => l_fp_cols_rec_app_cost,
902 X_RETURN_STATUS => x_return_status,
903 X_MSG_COUNT => x_msg_count,
904 X_MSG_DATA => x_msg_data);
905 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
906 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
907 END IF;
908 IF P_PA_DEBUG_MODE = 'Y' THEN
909 pa_fp_gen_amount_utils.fp_debug
910 (p_msg => 'Status after calling
911 pa_fp_map_bv_pub.gen_map_bv_to_target_rl'
912 ||x_return_status,
913 p_module_name => l_module_name,
914 p_log_level => 5);
915 END IF;
916
917 /* Insert the distinct target task_id and rlm_id values from tmp3 into tmp4.
918 * These are the only resources that have planned amounts in the baselined
919 * approved cost budget and are therefore the only resources that can possibly
920 * have unspent amounts. */
921 DELETE PA_RES_LIST_MAP_TMP4;
922 INSERT INTO PA_RES_LIST_MAP_TMP4
923 ( txn_task_id,
924 txn_resource_list_member_id )
925 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP3,PA_FP_CALC_AMT_TMP3_N1)*/
926 DISTINCT
927 task_id,
928 res_list_member_id
929 FROM PA_FP_CALC_AMT_TMP3
930 WHERE plan_version_id = p_app_cost_bdgt_ver_id;
931
932 select count(*) into l_count from pa_res_list_map_tmp4 where rownum=1;
933 --dbms_output.put_line('Number of records inserted into tmp4 from tmp3 = ' || l_count);
934
935 IF l_count = 0 THEN
936 IF p_pa_debug_mode = 'Y' THEN
937 PA_DEBUG.RESET_CURR_FUNCTION;
938 END IF;
939 RETURN;
940 END IF;
941
942 l_etc_start_date :=
943 PA_FP_GEN_AMOUNT_UTILS.GET_ETC_START_DATE(p_budget_version_id);
944
945 /* Get target resource assignment ids. */
946 IF p_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'N' THEN
947 SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N1)*/
948 ra.resource_assignment_id,
949 ra.task_id,
950 ra.resource_list_member_id,
951 ra.rate_based_flag,
952 ra.planning_start_date,
953 ra.planning_end_date
954 BULK COLLECT
955 INTO l_res_asg_id_tab,
956 l_task_id_tab,
957 l_res_list_mem_id_tab,
958 l_rate_based_flag_tab,
959 l_planning_start_date_tab,
960 l_planning_end_date_tab
961 FROM pa_resource_assignments ra,
962 pa_res_list_map_tmp4 tmp4
963 WHERE ra.budget_version_id = p_budget_version_id
964 AND ra.task_id = tmp4.txn_task_id
965 AND ra.resource_list_member_id = tmp4.txn_resource_list_member_id
966 ORDER BY ra.resource_assignment_id ASC;
967 ELSIF p_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'Y' THEN
968 SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N1)*/
969 ra.resource_assignment_id,
970 ra.task_id,
971 ra.resource_list_member_id,
972 ra.rate_based_flag,
973 ra.planning_start_date,
974 ra.planning_end_date
975 BULK COLLECT
976 INTO l_res_asg_id_tab,
977 l_task_id_tab,
978 l_res_list_mem_id_tab,
979 l_rate_based_flag_tab,
980 l_planning_start_date_tab,
981 l_planning_end_date_tab
982 FROM pa_resource_assignments ra,
983 pa_res_list_map_tmp4 tmp4
984 WHERE ra.budget_version_id = p_budget_version_id
985 AND ra.task_id = tmp4.txn_task_id
986 AND ra.resource_list_member_id = tmp4.txn_resource_list_member_id
987 AND ( ra.transaction_source_code IS NOT NULL
988 OR ( ra.transaction_source_code IS NULL
989 AND NOT EXISTS ( SELECT 1
990 FROM pa_budget_lines bl
991 WHERE bl.resource_assignment_id =
992 ra.resource_assignment_id
993 AND bl.start_date >= l_etc_start_date
994 AND rownum = 1 )))
995 ORDER BY ra.resource_assignment_id ASC;
996 END IF;
997
998 /* Add target task_id, rlm_id, and ra_id values from pl/sql tables into tmp4.
999 * We delete tmp4 and insert new lines instead of updating the existing ones
1000 * to simplify the manually added plan lines logic. */
1001 DELETE PA_RES_LIST_MAP_TMP4;
1002 FORALL i IN 1..l_res_asg_id_tab.count
1003 INSERT INTO PA_RES_LIST_MAP_TMP4
1004 ( txn_task_id,
1005 txn_resource_list_member_id,
1006 txn_resource_assignment_id )
1007 VALUES
1008 ( l_task_id_tab(i),
1009 l_res_list_mem_id_tab(i),
1010 l_res_asg_id_tab(i) );
1011
1012 select count(*) into l_count from pa_res_list_map_tmp4 where rownum=1;
1013 --dbms_output.put_line('Number of target resources in tmp4 to be processed = ' || l_count);
1014
1015 IF l_count = 0 THEN
1016 IF p_pa_debug_mode = 'Y' THEN
1017 PA_DEBUG.RESET_CURR_FUNCTION;
1018 END IF;
1019 RETURN;
1020 END IF;
1021
1022 /* Bulk collect plan amounts, ordered by ascending ra_id. */
1023 SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N1) INDEX(bl,PA_FP_CALC_AMT_TMP3_N1)*/
1024 tmp4.txn_resource_assignment_id,
1025 bl.txn_currency_code,
1026 nvl(sum(nvl(bl.quantity,0)),0),
1027 nvl(sum(nvl(bl.pc_raw_cost,0)),0),
1028 nvl(sum(nvl(bl.txn_raw_cost,0)),0),
1029 nvl(sum(nvl(bl.pc_burdened_cost,0)),0),
1030 nvl(sum(nvl(bl.txn_burdened_cost,0)),0)
1031 BULK COLLECT
1032 INTO l_plan_ra_id_tab,
1033 l_plan_txn_cur_code_tab,
1034 l_plan_qty_tab,
1035 l_plan_pc_raw_cost_tab,
1036 l_plan_txn_raw_cost_tab,
1037 l_plan_pc_burd_cost_tab,
1038 l_plan_txn_burd_cost_tab
1039 FROM pa_fp_calc_amt_tmp3 bl,
1040 pa_res_list_map_tmp4 tmp4
1041 WHERE bl.plan_version_id = p_app_cost_bdgt_ver_id
1042 AND bl.task_id = tmp4.txn_task_id
1043 AND bl.res_list_member_id = tmp4.txn_resource_list_member_id
1044 GROUP BY tmp4.txn_resource_assignment_id,
1045 bl.txn_currency_code
1046 ORDER BY tmp4.txn_resource_assignment_id ASC;
1047
1048 /* Bulk collect actuals amounts, ordered by ascending ra_id. */
1049 -- SQL Repository Bug 4884824; SQL ID 14902142
1050 -- Fixed Full Index Scan violation by replacing
1051 -- existing hint with leading hint.
1052 SELECT /*+ LEADING(tmp4) */
1053 tmp4.txn_resource_assignment_id,
1054 bl.txn_currency_code,
1055 nvl(sum(nvl(bl.init_quantity,0)),0),
1056 nvl(sum(nvl(bl.project_init_raw_cost,0)),0),
1057 nvl(sum(nvl(bl.txn_init_raw_cost,0)),0),
1058 nvl(sum(nvl(bl.project_init_burdened_cost,0)),0),
1059 nvl(sum(nvl(bl.txn_init_burdened_cost,0)),0)
1060 BULK COLLECT
1061 INTO l_init_ra_id_tab,
1062 l_init_txn_cur_code_tab,
1063 l_init_qty_tab,
1064 l_init_pc_raw_cost_tab,
1065 l_init_txn_raw_cost_tab,
1066 l_init_pc_burd_cost_tab,
1067 l_init_txn_burd_cost_tab
1068 FROM pa_budget_lines bl,
1069 pa_res_list_map_tmp4 tmp4
1070 WHERE bl.resource_assignment_id = tmp4.txn_resource_assignment_id
1071 AND bl.start_date < l_etc_start_date
1072 GROUP BY tmp4.txn_resource_assignment_id,
1073 bl.txn_currency_code
1074 ORDER BY tmp4.txn_resource_assignment_id ASC;
1075
1076 /* Initialize local currency code variables. */
1077 l_pc_currency_code := p_fp_cols_rec.X_PROJECT_CURRENCY_CODE;
1078 l_pfc_currency_code := p_fp_cols_rec.X_PROJFUNC_CURRENCY_CODE;
1079
1080 -- should InvalidArgException be thrown when period not found?
1081
1082 l_time_phase := p_fp_cols_rec.x_time_phased_code;
1083 /* Initialize start/end dates and l_period for p_unspent_amt_period. */
1084 l_period_name := p_unspent_amt_period;
1085 IF l_time_phase = 'P' THEN
1086 BEGIN
1087 SELECT pap.start_date,
1088 pap.end_date
1089 INTO l_start_date,
1090 l_end_date
1091 FROM pa_periods_all pap
1092 WHERE pap.period_name = p_unspent_amt_period
1093 AND pap.org_id = p_fp_cols_rec.x_org_id;
1094 EXCEPTION
1095 WHEN OTHERS THEN RAISE;
1096 END;
1097 ELSIF l_time_phase = 'G' THEN
1098 BEGIN
1099 SELECT glp.start_date,
1100 glp.end_date
1101 INTO l_start_date,
1102 l_end_date
1103 FROM gl_period_statuses glp
1104 WHERE glp.period_name = p_unspent_amt_period
1105 AND glp.application_id = pa_period_process_pkg.application_id
1106 AND glp.set_of_books_id = p_fp_cols_rec.x_set_of_books_id
1107 AND glp.adjustment_period_flag = 'N';
1108 EXCEPTION
1109 WHEN OTHERS THEN RAISE;
1110 END;
1111 ELSIF l_time_phase = 'N' THEN
1112 l_period_name := NULL;
1113 l_start_date := NULL;
1114 l_end_date := NULL;
1115 END IF;
1116
1117 --dbms_output.put_line('l_start_date = ' || l_start_date || ', l_end_date = ' || l_end_date);
1118
1119 /* Initialize indices for traversal of plan/init pl/sql tables. */
1120 p_index := 1;
1121 i_index := 1;
1122
1123 --dbms_output.put_line('Entering ra_id processing loop [count = ' || l_res_asg_id_tab.count || ']');
1124
1125 FOR i IN 1..l_res_asg_id_tab.count LOOP
1126 FOR wrapper_loop_iterator IN 1..1 LOOP
1127 l_curr_ra_id := l_res_asg_id_tab(i);
1128
1129 /* Sum plan quantity and pc amounts. */
1130 l_plan_qty := 0;
1131 l_plan_pc_raw_cost := 0;
1132 l_plan_txn_raw_cost := 0;
1133 l_plan_pc_burd_cost := 0;
1134 l_plan_txn_burd_cost := 0;
1135 l_plan_currency_tab.delete;
1136 WHILE ( p_index <= l_plan_ra_id_tab.count AND
1137 l_plan_ra_id_tab(p_index) <= l_curr_ra_id ) LOOP
1138 IF l_plan_ra_id_tab(p_index) = l_curr_ra_id THEN
1139 l_plan_currency_tab(l_plan_currency_tab.count+1)
1140 := l_plan_txn_cur_code_tab(p_index);
1141 l_plan_qty := l_plan_qty + l_plan_qty_tab(p_index);
1142 l_plan_pc_raw_cost := l_plan_pc_raw_cost + l_plan_pc_raw_cost_tab(p_index);
1143 l_plan_txn_raw_cost := l_plan_txn_raw_cost + l_plan_txn_raw_cost_tab(p_index);
1144 l_plan_pc_burd_cost := l_plan_pc_burd_cost + l_plan_pc_burd_cost_tab(p_index);
1145 l_plan_txn_burd_cost := l_plan_txn_burd_cost + l_plan_txn_burd_cost_tab(p_index);
1146 END IF;
1147 p_index := p_index + 1;
1148 END LOOP; -- plan
1149
1150 --dbms_output.put_line('ra_id = ' || l_curr_ra_id || ', l_plan_qty = ' || l_plan_qty);
1151
1152 /* Skip to the next target resource if planned quantity is 0. */
1153 IF l_plan_qty = 0 THEN
1154 EXIT;
1155 END IF;
1156
1157 /* Sum actual quantity and pc amounts */
1158 l_init_qty := 0;
1159 l_init_pc_raw_cost := 0;
1160 l_init_txn_raw_cost := 0;
1161 l_init_pc_burd_cost := 0;
1162 l_init_txn_burd_cost := 0;
1163 l_init_currency_tab.delete;
1164 l_actuals_exist_flag := 'Y';
1165 l_prev_i_index := i_index;
1166 WHILE ( i_index <= l_init_ra_id_tab.count AND
1167 l_init_ra_id_tab(i_index) <= l_curr_ra_id ) LOOP
1168 IF l_init_ra_id_tab(i_index) = l_curr_ra_id THEN
1169 l_init_currency_tab(l_init_currency_tab.count+1)
1170 := l_init_txn_cur_code_tab(i_index);
1171 l_init_qty := l_init_qty + l_init_qty_tab(i_index);
1172 l_init_pc_raw_cost := l_init_pc_raw_cost + l_init_pc_raw_cost_tab(i_index);
1173 l_init_txn_raw_cost := l_init_txn_raw_cost + l_init_txn_raw_cost_tab(i_index);
1174 l_init_pc_burd_cost := l_init_pc_burd_cost + l_init_pc_burd_cost_tab(i_index);
1175 l_init_txn_burd_cost := l_init_txn_burd_cost + l_init_txn_burd_cost_tab(i_index);
1176 END IF;
1177 i_index := i_index + 1;
1178 END LOOP; -- actuals
1179 IF i_index = l_prev_i_index THEN
1180 l_actuals_exist_flag := 'N';
1181 END IF;
1182
1183 --dbms_output.put_line('l_init_qty = ' || l_init_qty);
1184 --dbms_output.put_line('l_rate_based_flag_tab(i) = ' || l_rate_based_flag_tab(i));
1185 --dbms_output.put_line('l_plan_pc_raw_cost = ' || l_plan_pc_raw_cost);
1186 --dbms_output.put_line('l_init_pc_raw_cost = ' || l_init_pc_raw_cost);
1187
1188 IF l_rate_based_flag_tab(i) = 'N' THEN
1189 IF l_unspent_amt_currency = l_pc_currency_code THEN
1190 l_plan_qty := l_plan_pc_raw_cost;
1191 l_init_qty := l_init_pc_raw_cost;
1192 ELSE
1193 l_plan_qty := l_plan_txn_raw_cost;
1194 l_init_qty := l_init_txn_raw_cost;
1195 END IF;
1196 END IF;
1197
1198 /* Compute unspent quantity. */
1199 l_unspent_qty := l_plan_qty - l_init_qty;
1200
1201 --dbms_output.put_line('l_unspent_qty = ' || l_unspent_qty);
1202
1203 /* Skip to the next target resource if planned quantity is 0. */
1204 IF l_unspent_qty = 0 THEN
1205 EXIT;
1206 END IF;
1207
1208 /* Determine txn currency for unspent amounts. */
1209 IF p_fp_cols_rec.x_plan_in_multi_curr_flag = 'N' THEN
1210 l_unspent_amt_currency := l_pc_currency_code;
1211 /* If planned amounts are in a single currency and either there
1212 * are no actuals or actuals are planned all in the same currency,
1213 * then the unspent amount should be in the txn currency of
1214 * the planned amounts. */
1215 ELSIF ( l_actuals_exist_flag = 'N' AND
1216 l_plan_currency_tab.count = 1 ) OR
1217 ( l_plan_currency_tab.count = 1 AND
1218 l_init_currency_tab.count = 1 AND
1219 l_plan_currency_tab(1) = l_init_currency_tab(1) ) THEN
1220 l_unspent_amt_currency := l_plan_currency_tab(1);
1221 ELSE
1222 l_unspent_amt_currency := l_pc_currency_code;
1223 END IF;
1224
1225 --dbms_output.put_line('l_unspent_amt_currency = ' || l_unspent_amt_currency);
1226
1227 /* Derive rates based on actual amounts. */
1228 IF l_actuals_exist_flag = 'Y' AND l_init_qty <> 0 THEN
1229 IF l_unspent_amt_currency = l_pc_currency_code THEN
1230 l_txn_raw_cost_rate := l_init_pc_raw_cost / l_init_qty;
1231 l_txn_burd_cost_rate := l_init_pc_burd_cost / l_init_qty;
1232 ELSE
1233 l_txn_raw_cost_rate := l_init_txn_raw_cost / l_init_qty;
1234 l_txn_burd_cost_rate := l_init_txn_burd_cost / l_init_qty;
1235 END IF;
1236 /* If no actuals exist, then derive rates based on planned amounts. */
1237 ELSIF l_actuals_exist_flag = 'N' AND l_plan_qty <> 0 THEN
1238 IF l_unspent_amt_currency = l_pc_currency_code THEN
1239 l_txn_raw_cost_rate := l_plan_pc_raw_cost / l_plan_qty;
1240 l_txn_burd_cost_rate := l_plan_pc_burd_cost / l_plan_qty;
1241 ELSE
1242 l_txn_raw_cost_rate := l_plan_txn_raw_cost / l_plan_qty;
1243 l_txn_burd_cost_rate := l_plan_txn_burd_cost / l_plan_qty;
1244 END IF;
1245 ELSE
1246 -- Add additional Error Handling logic here if desired.
1247 -- For now, if rates cannot be derivced, skip this resource.
1248 EXIT;
1249 END IF;
1250
1251 --dbms_output.put_line('l_txn_raw_cost_rate = ' || l_txn_raw_cost_rate);
1252 --dbms_output.put_line('l_txn_burd_cost_rate = ' || l_txn_burd_cost_rate);
1253
1254 /* Compute unspent amounts. */
1255 l_unspent_txn_raw_cost := l_unspent_qty * l_txn_raw_cost_rate;
1256 l_unspent_txn_burd_cost := l_unspent_qty * l_txn_burd_cost_rate;
1257
1258 /* Check if we should insert a new budget line or update an existing one
1259 * with the unspent amounts. Store data in corresponding pl/sql tables. */
1260 l_update_flag := 'Y';
1261 l_insert_flag := 'N';
1262 BEGIN
1263 SELECT budget_line_id,
1264 quantity,
1265 txn_raw_cost,
1266 txn_burdened_cost
1267 INTO l_upd_bl_id,
1268 l_quantity,
1269 l_txn_raw_cost,
1270 l_txn_burdened_cost
1271 FROM pa_budget_lines
1272 WHERE resource_assignment_id = l_curr_ra_id
1273 AND txn_currency_code = l_unspent_amt_currency
1274 AND start_date = DECODE(l_time_phase, 'N', start_date, l_start_date);
1275 EXCEPTION
1276 WHEN NO_DATA_FOUND THEN
1277 l_insert_flag := 'Y';
1278 l_update_flag := 'N';
1279 END;
1280
1281 IF l_insert_flag = 'Y' THEN
1282 l_index := l_ins_ra_id_tab.count+1;
1283 l_ins_ra_id_tab(l_index) := l_curr_ra_id;
1284 l_ins_start_date_tab(l_index) := l_planning_start_date_tab(i);
1285 l_ins_end_date_tab(l_index) := l_planning_end_date_tab(i);
1286 l_ins_txn_curr_code_tab(l_index) := l_unspent_amt_currency;
1287 l_ins_quantity_tab(l_index) := l_unspent_qty;
1288 l_ins_raw_cost_tab(l_index) := l_unspent_txn_raw_cost;
1289 l_ins_burd_cost_tab(l_index) := l_unspent_txn_burd_cost;
1290 l_ins_raw_cost_rate_tab(l_index) := l_txn_raw_cost_rate;
1291 l_ins_burd_cost_rate_tab(l_index) := l_txn_burd_cost_rate;
1292 END IF;
1293 IF l_update_flag = 'Y' THEN
1294 l_index := l_upd_bl_id_tab.count+1;
1295 l_upd_bl_id_tab(l_index) := l_upd_bl_id;
1296 l_upd_quantity_tab(l_index) := l_unspent_qty;
1297 l_upd_raw_cost_tab(l_index) := l_unspent_txn_raw_cost;
1298 l_upd_burd_cost_tab(l_index) := l_unspent_txn_burd_cost;
1299 END IF;
1300
1301 END LOOP; -- wrapper
1302 END LOOP; -- target ra_id processing
1303
1304 --dbms_output.put_line('l_ins_ra_id_tab.count = ' || l_ins_ra_id_tab.count);
1305
1306 IF l_ins_ra_id_tab.count > 0 THEN
1307
1308 FORALL i IN 1..l_ins_ra_id_tab.count
1309 INSERT INTO PA_BUDGET_LINES (
1310 BUDGET_LINE_ID,
1311 BUDGET_VERSION_ID,
1312 RESOURCE_ASSIGNMENT_ID,
1313 START_DATE,
1314 TXN_CURRENCY_CODE,
1315 END_DATE,
1316 PERIOD_NAME,
1317 QUANTITY,
1318 TXN_RAW_COST,
1319 TXN_BURDENED_COST,
1320 TXN_COST_RATE_OVERRIDE,
1321 BURDEN_COST_RATE_OVERRIDE,
1322 LAST_UPDATE_DATE,
1323 LAST_UPDATED_BY,
1324 CREATION_DATE,
1325 CREATED_BY,
1326 LAST_UPDATE_LOGIN,
1327 PROJECT_CURRENCY_CODE,
1328 PROJFUNC_CURRENCY_CODE)
1329 VALUES (
1330 pa_budget_lines_s.nextval,
1331 p_budget_version_id,
1332 l_ins_ra_id_tab(i),
1333 NVL(l_start_date,l_ins_start_date_tab(i)),
1334 l_ins_txn_curr_code_tab(i),
1335 NVL(l_end_date,l_ins_end_date_tab(i)),
1336 l_period_name,
1337 l_ins_quantity_tab(i),
1338 l_ins_raw_cost_tab(i),
1339 l_ins_burd_cost_tab(i),
1340 l_ins_raw_cost_rate_tab(i),
1341 l_ins_burd_cost_rate_tab(i),
1342 l_sysdate,
1343 l_last_updated_by,
1344 l_sysdate,
1345 l_last_updated_by,
1346 l_last_update_login,
1347 l_pc_currency_code,
1348 l_pfc_currency_code );
1349
1350 /* If the resource uses Fixed Date spread and the fixed date is not in the
1351 * unspent amounts period, then NULL out the spread curve and fixed date. */
1352
1353 DELETE PA_RES_LIST_MAP_TMP4;
1354 FORALL i IN 1..l_ins_ra_id_tab.count
1355 INSERT INTO PA_RES_LIST_MAP_TMP4
1356 ( txn_resource_assignment_id )
1357 VALUES ( l_ins_ra_id_tab(i) );
1358
1359 SELECT spread_curve_id
1360 INTO l_fixed_date_curve_id
1361 FROM pa_spread_curves_b
1362 WHERE spread_curve_code = lc_fixed_date_code;
1363
1364 -- SQL Repository Bug 4884824; SQL ID 14902330
1365 -- Fixed Full Index Scan violation by replacing
1366 -- existing hint with leading hint.
1367 SELECT /*+ LEADING(tmp4) */
1368 ra.resource_assignment_id
1369 BULK COLLECT
1370 INTO l_fixed_date_ra_id_tab
1371 FROM pa_resource_assignments ra,
1372 pa_res_list_map_tmp4 tmp4
1373 WHERE ra.resource_assignment_id = tmp4.txn_resource_assignment_id
1374 AND ra.spread_curve_id = l_fixed_date_curve_id
1375 AND NOT ( ra.sp_fixed_date BETWEEN l_start_date AND l_end_date );
1376
1377 FORALL i IN 1..l_fixed_date_ra_id_tab.count
1378 UPDATE pa_resource_assignments
1379 SET spread_curve_id = NULL,
1380 sp_fixed_date = NULL,
1381 last_update_date = l_sysdate,
1382 last_updated_by = l_last_updated_by,
1383 last_update_login = l_last_update_login,
1384 record_version_number = NVL(record_version_number,0)+1
1385 WHERE resource_assignment_id = l_fixed_date_ra_id_tab(i);
1386 END IF; -- budget line insertion
1387
1388 --dbms_output.put_line('l_upd_bl_id_tab.count = ' || l_upd_bl_id_tab.count);
1389
1390 IF l_upd_bl_id_tab.count > 0 THEN
1391 FORALL i IN 1..l_upd_bl_id_tab.count
1392 UPDATE PA_BUDGET_LINES
1393 SET LAST_UPDATE_DATE = l_sysdate
1394 , LAST_UPDATED_BY = l_last_updated_by
1395 , LAST_UPDATE_LOGIN = l_last_update_login
1396 , QUANTITY = nvl(quantity,0) + nvl(l_upd_quantity_tab(i),0)
1397 , TXN_RAW_COST = nvl(txn_raw_cost,0) + nvl(l_upd_raw_cost_tab(i),0)
1398 , TXN_BURDENED_COST = nvl(txn_burdened_cost,0) + nvl(l_upd_burd_cost_tab(i),0)
1399 , TXN_COST_RATE_OVERRIDE = (nvl(txn_raw_cost,0) + nvl(l_upd_raw_cost_tab(i),0)) /
1400 (nvl(quantity,0) + nvl(l_upd_quantity_tab(i),0))
1401 , BURDEN_COST_RATE_OVERRIDE = (nvl(txn_burdened_cost,0) + nvl(l_upd_burd_cost_tab(i),0)) /
1402 (nvl(quantity,0) + nvl(l_upd_quantity_tab(i),0))
1403 WHERE BUDGET_LINE_ID = l_upd_bl_id_tab(i);
1404 END IF;
1405
1406 IF p_pa_debug_mode = 'Y' THEN
1407 pa_fp_gen_amount_utils.fp_debug
1408 (p_msg => 'Before calling
1409 pa_fp_maintain_actual_pub.sync_up_planning_dates',
1410 p_module_name => l_module_name,
1411 p_log_level => 5);
1412 END IF;
1413 PA_FP_MAINTAIN_ACTUAL_PUB.SYNC_UP_PLANNING_DATES
1414 (P_BUDGET_VERSION_ID => p_budget_version_id,
1415 P_CALLING_CONTEXT => 'SYNC_VERSION_LEVEL',
1416 X_RETURN_STATUS => x_return_Status,
1417 X_MSG_COUNT => x_msg_count,
1418 X_MSG_DATA => x_msg_data );
1419 IF p_pa_debug_mode = 'Y' THEN
1420 pa_fp_gen_amount_utils.fp_debug
1421 (p_msg => 'Status after calling
1422 pa_fp_maintain_actual_pub.sync_up_planning_dates'
1423 ||x_return_status,
1424 p_module_name => l_module_name,
1425 p_log_level => 5);
1426 END IF;
1427 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1428 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1429 END IF;
1430
1431 IF P_PA_DEBUG_MODE = 'Y' THEN
1432 PA_DEBUG.RESET_CURR_FUNCTION;
1433 END IF;
1434
1435 EXCEPTION
1436 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1437 l_msg_count := FND_MSG_PUB.count_msg;
1438 IF l_msg_count = 1 THEN
1439 PA_INTERFACE_UTILS_PUB.GET_MESSAGES
1440 ( p_encoded => FND_API.G_TRUE,
1441 p_msg_index => 1,
1442 p_msg_count => l_msg_count,
1443 p_msg_data => l_msg_data,
1444 p_data => l_data,
1445 p_msg_index_out => l_msg_index_out );
1446 x_msg_data := l_data;
1447 x_msg_count := l_msg_count;
1448 ELSE
1449 x_msg_count := l_msg_count;
1450 END IF;
1451
1452 ROLLBACK;
1453 x_return_status := FND_API.G_RET_STS_ERROR;
1454
1455 IF p_pa_debug_mode = 'Y' THEN
1456 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1457 ( p_msg => 'Invalid Arguments Passed',
1458 p_module_name => l_module_name,
1459 p_log_level => 5 );
1460 PA_DEBUG.RESET_CURR_FUNCTION;
1461 END IF;
1462 RAISE;
1463 WHEN OTHERS THEN
1464 ROLLBACK;
1465 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1466 x_msg_count := 1;
1467 x_msg_data := substr(sqlerrm,1,240);
1468 FND_MSG_PUB.ADD_EXC_MSG
1469 ( p_pkg_name => 'PA_FP_GEN_PUB',
1470 p_procedure_name => 'UNSPENT_AMOUNT',
1471 p_error_text => substr(sqlerrm,1,240) );
1472
1473 IF p_pa_debug_mode = 'Y' THEN
1474 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1475 ( p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1476 p_module_name => l_module_name,
1477 p_log_level => 5 );
1478 PA_DEBUG.RESET_CURR_FUNCTION;
1479 END IF;
1480 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1481
1482 END UNSPENT_AMOUNT;
1483
1484 --Please note that wbs_element_version id will be NULL for budgets and forecasts. If this API is called
1485 --for a B/F version then nothing will happen. The API just return without doing any processing
1486 PROCEDURE UPD_WBS_ELEMENT_VERSION_ID
1487 (P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
1488 P_STRUCTURE_VERSION_ID IN NUMBER,
1489 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1490 X_MSG_COUNT OUT NOCOPY NUMBER,
1491 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
1492
1493 l_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1494 l_wbs_element_ver_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1495 l_structure_version_id NUMBER;
1496 l_wp_version_flag pa_budget_versions.wp_version_flag%TYPE;
1497
1498 BEGIN
1499
1500 /* Setting initial values */
1501 X_MSG_COUNT := 0;
1502 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1503
1504 IF p_pa_debug_mode = 'Y' THEN
1505 pa_debug.set_curr_function( p_function => 'UPD_WBS_ELEMENT_VERSION_ID'
1506 ,p_debug_mode => p_pa_debug_mode);
1507 END IF;
1508
1509
1510
1511 SELECT NVL(P_STRUCTURE_VERSION_ID,project_structure_version_id),
1512 NVL(wp_version_flag,'N')
1513 INTO l_structure_version_id,
1514 l_wp_version_flag
1515 FROM pa_budget_versions
1516 WHERE budget_version_id = p_budget_version_id;
1517
1518 IF l_wp_version_flag = 'N' THEN
1519 IF P_PA_DEBUG_MODE = 'Y' THEN
1520 PA_DEBUG.Reset_Curr_Function;
1521 END IF;
1522 RETURN;
1523 END IF;
1524
1525
1526 SELECT ra.task_id,
1527 pa_proj_elements_utils.get_task_version_id(
1528 l_structure_version_id,ra.task_id)
1529 BULK COLLECT
1530 INTO l_task_id_tab,
1531 l_wbs_element_ver_id_tab
1532 FROM pa_resource_assignments ra
1533 WHERE ra.budget_version_id = p_budget_version_id
1534 AND nvl(ra.task_id,0) > 0;
1535
1536 IF l_task_id_tab.count = 0 THEN
1537 IF P_PA_DEBUG_MODE = 'Y' THEN
1538 PA_DEBUG.Reset_Curr_Function;
1539 END IF;
1540 RETURN;
1541 END IF;
1542
1543 FORALL i in 1..l_task_id_tab.count
1544 UPDATE pa_resource_assignments
1545 SET wbs_element_version_id = l_wbs_element_ver_id_tab(i)
1546 WHERE budget_version_id = p_budget_version_id
1547 AND task_id = l_task_id_tab(i);
1548
1549 IF P_PA_DEBUG_MODE = 'Y' THEN
1550 PA_DEBUG.Reset_Curr_Function;
1551 END IF;
1552
1553 EXCEPTION
1554 WHEN OTHERS THEN
1555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1556 x_msg_data := SUBSTR(SQLERRM,1,240);
1557 FND_MSG_PUB.add_exc_msg
1558 ( p_pkg_name => 'PA_FP_GEN_PUB'
1559 ,p_procedure_name => 'UPD_WBS_ELEMENT_VERSION_ID');
1560 IF P_PA_DEBUG_MODE = 'Y' THEN
1561 PA_DEBUG.Reset_Curr_Function;
1562 END IF;
1563 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1564
1565 END UPD_WBS_ELEMENT_VERSION_ID;
1566
1567 /* Procedure Name: PRORATE_UNALIGNED_PERIOD_AMOUNTS
1568 Created: 10/15/2004
1569 Summary: This procedure is called when generating forecast amounts for a particular planning
1570 element. When the source version and target version periods do not align (ie. one is PA,
1571 and the other is GL), then amounts from the less granular period must be pro-rated when
1572 copied over to the more granular period.
1573 */
1574 PROCEDURE PRORATE_UNALIGNED_PERIOD_AMTS
1575 (P_SRC_RES_ASG_ID_TAB IN PA_PLSQL_DATATYPES.IdTabTyp,
1576 P_TARGET_RES_ASG_ID IN PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE,
1577 P_CURRENCY_CODE IN PA_BUDGET_LINES.TXN_CURRENCY_CODE%TYPE,
1578 P_CURRENCY_CODE_FLAG IN VARCHAR2,
1579 P_ACTUAL_THRU_DATE IN PA_PERIODS_ALL.END_DATE%TYPE,
1580 X_QUANTITY OUT NOCOPY PA_BUDGET_LINES.QUANTITY%TYPE,
1581 X_TXN_RAW_COST OUT NOCOPY PA_BUDGET_LINES.TXN_RAW_COST%TYPE,
1582 X_TXN_BURDENED_COST OUT NOCOPY PA_BUDGET_LINES.TXN_BURDENED_COST%TYPE,
1583 X_TXN_REVENUE OUT NOCOPY PA_BUDGET_LINES.TXN_REVENUE%TYPE,
1584 X_PROJ_RAW_COST OUT NOCOPY PA_BUDGET_LINES.PROJECT_RAW_COST%TYPE,
1585 X_PROJ_BURDENED_COST OUT NOCOPY PA_BUDGET_LINES.PROJECT_BURDENED_COST%TYPE,
1586 X_PROJ_REVENUE OUT NOCOPY PA_BUDGET_LINES.PROJECT_REVENUE%TYPE,
1587 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1588 X_MSG_COUNT OUT NOCOPY NUMBER,
1589 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
1590
1591 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
1592 l_org_id pa_projects_all.org_id%TYPE;
1593 l_target_ver_period_type pa_proj_fp_options.cost_time_phased_code%TYPE;
1594 l_target_set_of_books_id pa_implementations_all.set_of_books_id%TYPE;
1595 l_target_period_name pa_budget_lines.period_name%TYPE;
1596 l_target_start_date pa_budget_lines.start_date%TYPE;
1597 l_target_end_date pa_budget_lines.end_date%TYPE;
1598 l_source_ver_period_type pa_proj_fp_options.cost_time_phased_code%TYPE;
1599 l_source_set_of_books_id pa_implementations_all.set_of_books_id%TYPE;
1600 l_source_period_name pa_budget_lines.period_name%TYPE;
1601 l_source_start_date pa_budget_lines.start_date%TYPE;
1602 l_source_end_date pa_budget_lines.end_date%TYPE;
1603
1604 l_prorated_multiplier NUMBER;
1605 l_quantity NUMBER;
1606 l_txn_raw_cost NUMBER;
1607 l_txn_burdened_cost NUMBER;
1608 l_txn_revenue NUMBER;
1609 l_pc_raw_cost NUMBER;
1610 l_pc_burdened_cost NUMBER;
1611 l_pc_revenue NUMBER;
1612
1613 --Cursor used to select the PA period that contains the amts_thru_date and later
1614 CURSOR pa_period_csr(c_amt_thru PA_PERIODS_ALL.END_DATE%TYPE,
1615 c_org_id PA_PROJECTS_ALL.ORG_ID%TYPE) IS
1616 SELECT period_name, start_date, end_date
1617 FROM pa_periods_all
1618 WHERE org_id = c_org_id and -- R12 MOAC 4447573: nvl(org_id,-99) = nvl(c_org_id,-99)
1619 c_amt_thru between start_date and end_date;
1620 pa_period_rec pa_period_csr%ROWTYPE;
1621
1622 --Cursor used to select the GL period that contains the amts_thru_date and later
1623 CURSOR gl_period_csr(c_amt_thru PA_PERIODS_ALL.END_DATE%TYPE,
1624 c_set_of_books PA_IMPLEMENTATIONS_ALL.SET_OF_BOOKS_ID%TYPE) IS
1625 SELECT period_name, start_date , end_date
1626 FROM gl_period_statuses
1627 WHERE application_id = PA_PERIOD_PROCESS_PKG.Application_id and
1628 set_of_books_id = c_set_of_books and
1629 adjustment_period_flag = 'N' and
1630 c_amt_thru between start_date and end_date;
1631 gl_period_rec gl_period_csr%ROWTYPE;
1632
1633 l_source_bv_id pa_budget_lines.budget_version_id%TYPE;
1634 BEGIN
1635
1636 IF P_PA_DEBUG_MODE = 'Y' THEN
1637 pa_debug.set_curr_function( p_function => 'PRORATE_UNALIGNED_PERIOD_AMTS',
1638 p_debug_mode => p_pa_debug_mode );
1639 END IF;
1640
1641 x_return_status := FND_API.G_RET_STS_SUCCESS;
1642 /* Business Rules check */
1643 -- Check for null P_TARGET_RES_ASG_ID
1644
1645 /* Begin processing */
1646
1647 -- Initialize output parameters; currently, we do not use the proj_ params
1648 x_quantity := 0;
1649 x_txn_raw_cost := 0;
1650 x_txn_burdened_cost := 0;
1651 x_txn_revenue := 0;
1652 x_proj_raw_cost := NULL;
1653 x_proj_burdened_cost := NULL;
1654 x_proj_revenue := NULL;
1655
1656 IF p_src_res_asg_id_tab.count = 0 THEN
1657 IF P_PA_DEBUG_MODE = 'Y' THEN
1658 PA_DEBUG.RESET_CURR_FUNCTION;
1659 END IF;
1660 RETURN;
1661 END IF;
1662
1663 -- get necessary source budget version info
1664 select nvl(p.org_id,-99),
1665 DECODE(po.fin_plan_preference_code,
1666 'COST_ONLY', po.cost_time_phased_code,
1667 'REVENUE_ONLY', po.revenue_time_phased_code,
1668 po.all_time_phased_code),
1669 pia.set_of_books_id,
1670 ra.budget_version_id
1671 into l_org_id,
1672 l_source_ver_period_type,
1673 l_source_set_of_books_id,
1674 l_source_bv_id
1675 from pa_resource_assignments ra,
1676 pa_projects_all p,
1677 pa_proj_fp_options po,
1678 pa_implementations_all pia
1679 where ra.resource_assignment_id = p_src_res_asg_id_tab(1) and
1680 ra.project_id = p.project_id and
1681 ra.budget_version_id = po.fin_plan_version_id and
1682 po.fin_plan_option_level_code = 'PLAN_VERSION' and
1683 p.org_id = pia.org_id;
1684 -- R12 MOAC 4447573: nvl(p.org_id, -99) = nvl(pia.org_id, -99)
1685
1686 IF l_source_ver_period_type = 'P' THEN
1687 -- Open PA Cursor using TARGET Actuals Thru Date
1688 OPEN pa_period_csr(p_actual_thru_date, l_org_id);
1689 FETCH pa_period_csr
1690 INTO l_source_period_name,
1691 l_source_start_date,
1692 l_source_end_date;
1693 CLOSE pa_period_csr;
1694 ELSIF l_source_ver_period_type = 'G' THEN
1695 OPEN gl_period_csr(p_actual_thru_date, l_source_set_of_books_id);
1696 FETCH gl_period_csr
1697 INTO l_source_period_name,
1698 l_source_start_date,
1699 l_source_end_date;
1700 CLOSE gl_period_csr;
1701 ELSE
1702 IF P_PA_DEBUG_MODE = 'Y' THEN
1703 PA_DEBUG.RESET_CURR_FUNCTION;
1704 END IF;
1705 RETURN;
1706 END IF;
1707
1708 l_prorated_multiplier := (l_source_end_date - p_actual_thru_date) /
1709 (l_source_end_date - l_source_start_date + 1);
1710
1711 IF l_prorated_multiplier = 0 THEN
1712 IF P_PA_DEBUG_MODE = 'Y' THEN
1713 PA_DEBUG.RESET_CURR_FUNCTION;
1714 END IF;
1715 RETURN;
1716 END IF;
1717
1718 /* Use temporary table to Bulk process resources.
1719 * We use the target_res_asg_id column instead of source_res_asg_id
1720 * so that we can make use of the index on the temp table. */
1721 DELETE pa_fp_gen_rate_tmp;
1722 FORALL i IN 1..p_src_res_asg_id_tab.count
1723 INSERT INTO pa_fp_gen_rate_tmp
1724 ( target_res_asg_id )
1725 VALUES ( p_src_res_asg_id_tab(i) );
1726
1727 -- SQL Repository Bug 4884824; SQL ID 14902567
1728 -- Fixed Full Index Scan violation by replacing
1729 -- existing hint with leading hint.
1730 SELECT /*+ LEADING(tmp) */
1731 nvl(sum(sbl.quantity),0),
1732 nvl(sum(decode(p_currency_code_flag,
1733 'Y', sbl.txn_raw_cost,
1734 'N', sbl.project_raw_cost,
1735 'A', sbl.raw_cost)),0),
1736 nvl(sum(decode(p_currency_code_flag,
1737 'Y', sbl.txn_burdened_cost,
1738 'N', sbl.project_burdened_cost,
1739 'A', sbl.burdened_cost)),0),
1740 nvl(sum(decode(p_currency_code_flag,
1741 'Y', sbl.txn_revenue,
1742 'N', sbl.project_revenue,
1743 'A', sbl.revenue)),0)
1744 INTO l_quantity,
1745 l_txn_raw_cost,
1746 l_txn_burdened_cost,
1747 l_txn_revenue
1748 FROM pa_fp_gen_rate_tmp tmp,
1749 pa_budget_lines sbl
1750 WHERE tmp.target_res_asg_id = sbl.resource_assignment_id
1751 and sbl.budget_version_id = l_source_bv_id
1752 and sbl.period_name = l_source_period_name
1753 and sbl.txn_currency_code = decode(p_currency_code_flag,
1754 'Y', p_currency_code,
1755 'N', sbl.txn_currency_code,
1756 'A', sbl.txn_currency_code)
1757 and sbl.cost_rejection_code is null
1758 and sbl.revenue_rejection_code is null
1759 and sbl.burden_rejection_code is null
1760 and sbl.other_rejection_code is null
1761 and sbl.pc_cur_conv_rejection_code is null
1762 and sbl.pfc_cur_conv_rejection_code is null;
1763
1764 x_quantity := l_quantity * l_prorated_multiplier;
1765 x_txn_raw_cost := l_txn_raw_cost * l_prorated_multiplier;
1766 x_txn_burdened_cost := l_txn_burdened_cost * l_prorated_multiplier;
1767 x_txn_revenue := l_txn_revenue * l_prorated_multiplier;
1768 EXCEPTION
1769 WHEN OTHERS THEN
1770 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1771 x_msg_data := SUBSTR(SQLERRM,1,240);
1772 FND_MSG_PUB.add_exc_msg
1773 ( p_pkg_name => 'PA_FP_GEN_PUB'
1774 ,p_procedure_name => 'PRORATE_UNALIGNED_PERIOD_AMTS');
1775 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1776 END PRORATE_UNALIGNED_PERIOD_AMTS;
1777
1778
1779 /**
1780 * This procedure updates the fixed date spread curve fields in the
1781 * pa_resource_assignments table for all resource assignments belonging
1782 * to the given budget version as necessary.
1783 * More specifically, for each resource assignment of interest, we null
1784 * out the spread_curve_id and sp_fixed_date pa_resource_assignments
1785 * table values if there exists a budget line for which the resource
1786 * assignment's sp_fixed_date is not in the budget line's start and end
1787 * date range.
1788 * Additionally, for resources not having Fixed Date spread curves, we
1789 * ensure that sp_fixed_date is Nulled out to address Bug 4229963.
1790 *
1791 * Note: This API currently updates the PA_RESOURCE_ASSIGNMENTS table
1792 * multiple times. In the future, we revisit this as a Performance
1793 * issue and modify the logic so that we only update once.
1794 *
1795 * Note that the p_fp_col_rec parameter is currently not used.
1796 */
1797 PROCEDURE MAINTAIN_FIXED_DATE_SP
1798 (P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
1799 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
1800 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1801 X_MSG_COUNT OUT NOCOPY NUMBER,
1802 X_MSG_DATA OUT NOCOPY VARCHAR2)
1803 IS
1804 l_module_name VARCHAR2(100) := 'pa.plsql.PA_FP_GEN_PUB.MAINTAIN_FIXED_DATE_SP';
1805
1806 /* String constant for the fixed date spread curve code */
1807 lc_FixedDate CONSTANT PA_SPREAD_CURVES_B.SPREAD_CURVE_CODE%TYPE := 'FIXED_DATE';
1808
1809 /* This cursor picks up resource assignment id's for resource assignments
1810 * having a fixed date spread curve and at least two budget lines for some
1811 * transaction currency code. */
1812 CURSOR multi_bl_fixed_date_ra_cur IS
1813 SELECT DISTINCT(bl.resource_assignment_id)
1814 FROM pa_resource_assignments ra,
1815 pa_spread_curves_b sp,
1816 pa_budget_lines bl
1817 WHERE ra.budget_version_id = p_budget_version_id
1818 AND sp.spread_curve_id = ra.spread_curve_id
1819 AND sp.spread_curve_code = lc_FixedDate
1820 AND bl.resource_assignment_id = ra.resource_assignment_id
1821 GROUP BY bl.resource_assignment_id,
1822 bl.txn_currency_code
1823 HAVING count(*) > 1;
1824
1825 /* This cursor picks up resource assignment id's for resource assignments
1826 * having a fixed date spread curve and a budget line whose start and end
1827 * dates do not contain the resource assignment's sp_fixed_date.
1828 * Note that by first processing resource assignments returned by the
1829 * the multi_bl_fixed_date_ra_cur cursor, we can reduce the amount of
1830 * processing required by this cursor. */
1831 CURSOR one_bl_fixed_date_ra_cur IS
1832 SELECT DISTINCT(bl.resource_assignment_id)
1833 FROM pa_resource_assignments ra,
1834 pa_spread_curves_b sp,
1835 pa_budget_lines bl
1836 WHERE ra.budget_version_id = p_budget_version_id
1837 AND sp.spread_curve_id = ra.spread_curve_id
1838 AND sp.spread_curve_code = lc_FixedDate
1839 AND bl.resource_assignment_id = ra.resource_assignment_id
1840 AND ra.sp_fixed_date NOT BETWEEN bl.start_date AND bl.end_date;
1841
1842 /* PL/SQL table variable for the cursors */
1843 l_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1844
1845 l_count NUMBER;
1846 l_msg_count NUMBER;
1847 l_data VARCHAR2(1000);
1848 l_msg_data VARCHAR2(1000);
1849 l_msg_index_out NUMBER;
1850
1851 l_fixed_date_id PA_SPREAD_CURVES_B.SPREAD_CURVE_ID%TYPE;
1852
1853 l_last_updated_by NUMBER := FND_GLOBAL.user_id;
1854 l_last_update_login NUMBER := FND_GLOBAL.login_id;
1855 l_sysdate DATE := SYSDATE;
1856 BEGIN
1857 x_return_status := FND_API.G_RET_STS_SUCCESS;
1858 x_msg_count := 0;
1859
1860 IF p_pa_debug_mode = 'Y' THEN
1861 PA_DEBUG.SET_CURR_FUNCTION
1862 ( p_function => 'MAINTAIN_FIXED_DATE_SP',
1863 p_debug_mode => p_pa_debug_mode );
1864 END IF;
1865
1866 /* Check the input parameter(s) */
1867 IF p_budget_version_id IS NULL THEN
1868 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1869 END IF;
1870
1871 /* Get the spread curve id for Fixed Date */
1872 SELECT spread_curve_id INTO l_fixed_date_id
1873 FROM pa_spread_curves_b
1874 WHERE spread_curve_code = lc_FixedDate;
1875
1876 /* Fetch resource assignment id's for resource assignments having
1877 * a fixed date spread curve and multiple budget lines for some
1878 * transaction currency code. */
1879 OPEN multi_bl_fixed_date_ra_cur;
1880 FETCH multi_bl_fixed_date_ra_cur
1881 BULK COLLECT
1882 INTO l_res_asg_id_tab;
1883 CLOSE multi_bl_fixed_date_ra_cur;
1884
1885 /* Null out the sp_fixed_date and spread_curve_id in the
1886 * pa_resource_assignments table for the collected resource
1887 * assignment id's */
1888 FORALL i in 1..l_res_asg_id_tab.count
1889 UPDATE pa_resource_assignments
1890 SET sp_fixed_date = NULL,
1891 spread_curve_id = NULL,
1892 last_update_date = l_sysdate,
1893 last_updated_by = l_last_updated_by,
1894 last_update_login = l_last_update_login,
1895 record_version_number = NVL(record_version_number,0) + 1
1896 WHERE resource_assignment_id = l_res_asg_id_tab(i);
1897
1898 /* Of the remaining fixed date resource assignments for the given
1899 * budget version (each of which should now have at most 1 budget
1900 * line), fetch the id's for resource assignments having a budget
1901 * line whose start and end dates do not contain the resource
1902 * assignment's sp_fixed_date. */
1903 OPEN one_bl_fixed_date_ra_cur;
1904 FETCH one_bl_fixed_date_ra_cur
1905 BULK COLLECT
1906 INTO l_res_asg_id_tab;
1907 CLOSE one_bl_fixed_date_ra_cur;
1908
1909 /* Null out the sp_fixed_date and spread_curve_id in the
1910 * pa_resource_assignments table for the collected resource
1911 * assignment id's */
1912 FORALL i in 1..l_res_asg_id_tab.count
1913 UPDATE pa_resource_assignments
1914 SET sp_fixed_date = NULL,
1915 spread_curve_id = NULL,
1916 last_update_date = l_sysdate,
1917 last_updated_by = l_last_updated_by,
1918 last_update_login = l_last_update_login,
1919 record_version_number = NVL(record_version_number,0) + 1
1920 WHERE resource_assignment_id = l_res_asg_id_tab(i);
1921
1922 -- Bug 4229963: Ensure sp_fixed_date is NULL when spread is not Fixed Date.
1923 UPDATE pa_resource_assignments
1924 SET sp_fixed_date = NULL,
1925 last_update_date = l_sysdate,
1926 last_updated_by = l_last_updated_by,
1927 last_update_login = l_last_update_login,
1928 record_version_number = NVL(record_version_number,0) + 1
1929 WHERE budget_version_id = p_budget_version_id
1930 AND spread_curve_id <> l_fixed_date_id
1931 AND sp_fixed_date IS NOT NULL;
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 x_return_status := FND_API.G_RET_STS_ERROR;
1955
1956 IF p_pa_debug_mode = 'Y' THEN
1957 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1958 ( p_msg => 'Invalid Arguments Passed',
1959 p_module_name => l_module_name,
1960 p_log_level => 5 );
1961 PA_DEBUG.RESET_CURR_FUNCTION;
1962 END IF;
1963 RAISE;
1964 WHEN OTHERS THEN
1965 ROLLBACK;
1966 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1967 x_msg_count := 1;
1968 x_msg_data := substr(sqlerrm,1,240);
1969 FND_MSG_PUB.ADD_EXC_MSG
1970 ( p_pkg_name => 'PA_FP_GEN_PUB',
1971 p_procedure_name => 'MAINTAIN_FIXED_DATE_SP',
1972 p_error_text => substr(sqlerrm,1,240) );
1973
1974 IF p_pa_debug_mode = 'Y' THEN
1975 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1976 ( p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1977 p_module_name => l_module_name,
1978 p_log_level => 5 );
1979 PA_DEBUG.RESET_CURR_FUNCTION;
1980 END IF;
1981 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1982
1983 END MAINTAIN_FIXED_DATE_SP;
1984
1985 /**
1986 * This procedure copies source attributes to target resources with the
1987 * intended context as Forecast Generation. Attributes will only be copied
1988 * when the following Source/Target conditions are met:
1989 * 1. Planning Level must be same.
1990 * 2. Resource List must be same.
1991 * 3. Structure should be a fully shared structure.
1992 * The only exception to the above is that planning attributes are not
1993 * carried over when the generation FP/WP source is None time-phased and
1994 * the target forecast version is time phased.
1995 *
1996 * Before calling this API, the TXN_RESOURCE_ASSIGNMENT_ID column of the
1997 * PA_RES_LIST_MAP_TMP1 table should be populated with resources to be
1998 * processed. Furthermore, the PA_FP_CALC_AMT_TMP1 table should contain
1999 * the resource mapping and ETC source code information for said resources.
2000 *
2001 * An Invalid Argument Exception will be raised if the p_fp_cols_rec
2002 * parameter is NULL or has NULL values for either the project id or the
2003 * budget version id.
2004 */
2005 PROCEDURE COPY_SRC_ATTRS_TO_TARGET_FCST
2006 (P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
2007 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2008 X_MSG_COUNT OUT NOCOPY NUMBER,
2009 X_MSG_DATA OUT NOCOPY VARCHAR2)
2010 IS
2011 l_module_name VARCHAR2(100) := 'pa.plsql.PA_FP_GEN_PUB.' ||
2012 'COPY_SRC_ATTRS_TO_TARGET_FCST';
2013 l_log_level CONSTANT PLS_INTEGER := 5;
2014
2015 l_stru_sharing_code PA_PROJECTS_ALL.STRUCTURE_SHARING_CODE%TYPE;
2016
2017 l_src_version_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2018 l_gen_etc_src_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2019 l_src_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
2020 l_gen_etc_src_code PA_PROJ_FP_OPTIONS.GEN_COST_ETC_SRC_CODE%TYPE;
2021 l_fp_cols_rec_src PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
2022
2023 l_tgt_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2024
2025 /* PL/SQL tables for copying source resource assignment attributes */
2026 l_resource_class_flag_tab PA_PLSQL_DATATYPES.Char15TabTyp;
2027 l_resource_class_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2028 l_res_type_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2029 l_person_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2030 l_job_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2031 l_person_type_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2032 l_named_role_tab PA_PLSQL_DATATYPES.Char80TabTyp;
2033 l_bom_resource_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2034 l_non_labor_resource_tab PA_PLSQL_DATATYPES.Char20TabTyp;
2035 l_inventory_item_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2036 l_item_category_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2037 l_project_role_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2038 l_organization_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2039 l_fc_res_type_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2040 l_expenditure_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2041 l_expenditure_category_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2042 l_event_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2043 l_revenue_category_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2044 l_supplier_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2045 l_spread_curve_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2046 l_sp_fixed_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2047 l_mfc_cost_type_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2048 l_incurred_by_res_flag_tab PA_PLSQL_DATATYPES.Char15TabTyp;
2049 l_incur_by_res_cls_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2050 l_incur_by_role_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2051 l_rate_expenditure_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2052 l_rate_func_curr_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2053 l_org_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2054 -- IPM: Added table for copying source resource_rate_based_flag values.
2055 l_res_rate_based_flag_tab PA_PLSQL_DATATYPES.Char15TabTyp;
2056
2057 l_sysdate DATE;
2058 l_last_updated_by PA_RESOURCE_ASSIGNMENTS.LAST_UPDATED_BY%TYPE
2059 := FND_GLOBAL.user_id;
2060 l_last_update_login PA_RESOURCE_ASSIGNMENTS.LAST_UPDATE_LOGIN%TYPE
2061 := FND_GLOBAL.login_id;
2062
2063 l_count NUMBER;
2064 l_msg_count NUMBER;
2065 l_data VARCHAR2(1000);
2066 l_msg_data VARCHAR2(1000);
2067 l_msg_index_out NUMBER;
2068 BEGIN
2069 x_return_status := FND_API.G_RET_STS_SUCCESS;
2070 x_msg_count := 0;
2071
2072 IF p_pa_debug_mode = 'Y' THEN
2073 PA_DEBUG.SET_CURR_FUNCTION
2074 ( p_function => 'COPY_SRC_ATTRS_TO_TARGET_FCST',
2075 p_debug_mode => p_pa_debug_mode );
2076 END IF;
2077
2078 /* Enforce that p_fp_cols_rec has valid id values. */
2079 IF p_fp_cols_rec.x_project_id IS NULL OR
2080 p_fp_cols_rec.x_budget_version_id IS NULL THEN
2081 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2082 p_msg_name => 'PA_FP_INV_PARAM_PASSED' );
2083 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2084 END IF;
2085
2086 /* Check that the project has Fully Shared WBS and a non-null source id */
2087 l_stru_sharing_code :=
2088 PA_PROJECT_STRUCTURE_UTILS.GET_STRUCTURE_SHARING_CODE
2089 ( p_project_id => p_fp_cols_rec.x_project_id );
2090 IF l_stru_sharing_code <> 'SHARE_FULL' OR
2091 ( p_fp_cols_rec.x_gen_src_wp_version_id IS NULL AND
2092 p_fp_cols_rec.x_gen_src_plan_version_id IS NULL ) THEN
2093 IF p_pa_debug_mode = 'Y' THEN
2094 PA_DEBUG.RESET_CURR_FUNCTION;
2095 END IF;
2096 RETURN;
2097 END IF;
2098
2099 /* Initialize l_src_version_id_tab and l_gen_etc_src_code_tab */
2100 l_src_version_id_tab(1) := p_fp_cols_rec.x_gen_src_wp_version_id;
2101 l_src_version_id_tab(2) := p_fp_cols_rec.x_gen_src_plan_version_id;
2102 l_gen_etc_src_code_tab(1) := 'WORKPLAN_RESOURCES';
2103 l_gen_etc_src_code_tab(2) := 'FINANCIAL_PLAN';
2104
2105 FOR i IN 1..l_src_version_id_tab.count LOOP
2106 l_src_version_id := l_src_version_id_tab(i);
2107 IF l_src_version_id IS NOT NULL THEN
2108 --dbms_output.put_line('l_src_version_id = ' || l_src_version_id);
2109 /* CAll API to get Source data into l_fp_cols_rec_src */
2110 IF p_pa_debug_mode = 'Y' THEN
2111 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
2112 ( p_msg => 'Before calling PA_FP_GEN_AMOUNT_UTILS.' ||
2113 'GET_PLAN_VERSION_DTLS',
2114 p_module_name => l_module_name,
2115 p_log_level => l_log_level );
2116 END IF;
2117 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS
2118 ( p_project_id => p_fp_cols_rec.x_project_id,
2119 p_budget_version_id => l_src_version_id,
2120 x_fp_cols_rec => l_fp_cols_rec_src,
2121 x_return_status => x_return_status,
2122 x_msg_count => x_msg_count,
2123 x_msg_data => x_msg_data );
2124 IF p_pa_debug_mode = 'Y' THEN
2125 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
2126 ( p_msg => 'Status after calling PA_FP_GEN_AMOUNT_UTILS.' ||
2127 'GET_PLAN_VERSION_DTLS: ' ||
2128 x_return_status,
2129 p_module_name => l_module_name,
2130 p_log_level => l_log_level );
2131 END IF;
2132 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2133 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2134 END IF;
2135
2136 IF ( p_fp_cols_rec.x_resource_list_id =
2137 l_fp_cols_rec_src.x_resource_list_id ) AND
2138 ( p_fp_cols_rec.x_fin_plan_level_code =
2139 l_fp_cols_rec_src.x_fin_plan_level_code ) AND
2140 NOT ( p_fp_cols_rec.x_time_phased_code <> 'N' AND
2141 l_fp_cols_rec_src.x_time_phased_code = 'N' ) THEN
2142
2143 l_gen_etc_src_code := l_gen_etc_src_code_tab(i);
2144 --dbms_output.put_line('l_gen_etc_src_code = ' || l_gen_etc_src_code);
2145 /* Pick up the source resource assignment attributes. */
2146 SELECT /*+ INDEX(map,PA_FP_CALC_AMT_TMP1_N1)*/
2147 TMP1.TXN_RESOURCE_ASSIGNMENT_ID,
2148 RA.RESOURCE_CLASS_FLAG,
2149 RA.RESOURCE_CLASS_CODE,
2150 RA.RES_TYPE_CODE,
2151 RA.PERSON_ID,
2152 RA.JOB_ID,
2153 RA.PERSON_TYPE_CODE,
2154 RA.NAMED_ROLE,
2155 RA.BOM_RESOURCE_ID,
2156 RA.NON_LABOR_RESOURCE,
2157 RA.INVENTORY_ITEM_ID,
2158 RA.ITEM_CATEGORY_ID,
2159 RA.PROJECT_ROLE_ID,
2160 RA.ORGANIZATION_ID,
2161 RA.FC_RES_TYPE_CODE,
2162 RA.EXPENDITURE_TYPE,
2163 RA.EXPENDITURE_CATEGORY,
2164 RA.EVENT_TYPE,
2165 RA.REVENUE_CATEGORY_CODE,
2166 RA.SUPPLIER_ID,
2167 RA.SPREAD_CURVE_ID,
2168 RA.SP_FIXED_DATE,
2169 RA.MFC_COST_TYPE_ID,
2170 RA.INCURRED_BY_RES_FLAG,
2171 RA.INCUR_BY_RES_CLASS_CODE,
2172 RA.INCUR_BY_ROLE_ID,
2173 RA.RATE_EXPENDITURE_TYPE,
2174 RA.RATE_EXP_FUNC_CURR_CODE,
2175 RA.RATE_EXPENDITURE_ORG_ID,
2176 RA.RESOURCE_RATE_BASED_FLAG -- Added for IPM ER
2177 BULK COLLECT
2178 INTO l_tgt_res_asg_id_tab,
2179 l_resource_class_flag_tab,
2180 l_resource_class_code_tab,
2181 l_res_type_code_tab,
2182 l_person_id_tab,
2183 l_job_id_tab,
2184 l_person_type_code_tab,
2185 l_named_role_tab,
2186 l_bom_resource_id_tab,
2187 l_non_labor_resource_tab,
2188 l_inventory_item_id_tab,
2189 l_item_category_id_tab,
2190 l_project_role_id_tab,
2191 l_organization_id_tab,
2192 l_fc_res_type_code_tab,
2193 l_expenditure_type_tab,
2194 l_expenditure_category_tab,
2195 l_event_type_tab,
2196 l_revenue_category_code_tab,
2197 l_supplier_id_tab,
2198 l_spread_curve_id_tab,
2199 l_sp_fixed_date_tab,
2200 l_mfc_cost_type_id_tab,
2201 l_incurred_by_res_flag_tab,
2202 l_incur_by_res_cls_code_tab,
2203 l_incur_by_role_id_tab,
2204 l_rate_expenditure_type_tab,
2205 l_rate_func_curr_code_tab,
2206 l_org_id_tab,
2207 l_res_rate_based_flag_tab -- Added for IPM ER
2208 FROM PA_RESOURCE_ASSIGNMENTS RA,
2209 PA_RES_LIST_MAP_TMP1 tmp1,
2210 PA_FP_CALC_AMT_TMP1 map
2211 WHERE RA.budget_version_id = l_src_version_id
2212 AND RA.resource_assignment_id = map.resource_assignment_id
2213 AND map.target_res_asg_id = tmp1.txn_resource_assignment_id
2214 AND map.transaction_source_code = l_gen_etc_src_code;
2215
2216 --dbms_output.put_line('l_tgt_res_asg_id_tab.count = ' || l_tgt_res_asg_id_tab.count);
2217 l_sysdate := SYSDATE;
2218
2219 FORALL j IN 1..l_tgt_res_asg_id_tab.count
2220 UPDATE PA_RESOURCE_ASSIGNMENTS
2221 SET RESOURCE_CLASS_FLAG = l_resource_class_flag_tab(j),
2222 RESOURCE_CLASS_CODE = l_resource_class_code_tab(j),
2223 RES_TYPE_CODE = l_res_type_code_tab(j),
2224 PERSON_ID = l_person_id_tab(j),
2225 JOB_ID = l_job_id_tab(j),
2226 PERSON_TYPE_CODE = l_person_type_code_tab(j),
2227 NAMED_ROLE = l_named_role_tab(j),
2228 BOM_RESOURCE_ID = l_bom_resource_id_tab(j),
2229 NON_LABOR_RESOURCE = l_non_labor_resource_tab(j),
2230 INVENTORY_ITEM_ID = l_inventory_item_id_tab(j),
2231 ITEM_CATEGORY_ID = l_item_category_id_tab(j),
2232 PROJECT_ROLE_ID = l_project_role_id_tab(j),
2233 ORGANIZATION_ID = l_organization_id_tab(j),
2234 FC_RES_TYPE_CODE = l_fc_res_type_code_tab(j),
2235 EXPENDITURE_TYPE = l_expenditure_type_tab(j),
2236 EXPENDITURE_CATEGORY = l_expenditure_category_tab(j),
2237 EVENT_TYPE = l_event_type_tab(j),
2238 REVENUE_CATEGORY_CODE = l_revenue_category_code_tab(j),
2239 SUPPLIER_ID = l_supplier_id_tab(j),
2240 SPREAD_CURVE_ID = l_spread_curve_id_tab(j),
2241 SP_FIXED_DATE = l_sp_fixed_date_tab(j),
2242 MFC_COST_TYPE_ID = l_mfc_cost_type_id_tab(j),
2243 INCURRED_BY_RES_FLAG = l_incurred_by_res_flag_tab(j),
2244 INCUR_BY_RES_CLASS_CODE = l_incur_by_res_cls_code_tab(j),
2245 INCUR_BY_ROLE_ID = l_incur_by_role_id_tab(j),
2246 RATE_EXPENDITURE_TYPE = l_rate_expenditure_type_tab(j),
2247 RATE_EXP_FUNC_CURR_CODE = l_rate_func_curr_code_tab(j),
2248 LAST_UPDATE_DATE = l_sysdate,
2249 LAST_UPDATED_BY = l_last_updated_by,
2250 LAST_UPDATE_LOGIN = l_last_update_login,
2251 RATE_EXPENDITURE_ORG_ID = l_org_id_tab(j),
2252 RESOURCE_RATE_BASED_FLAG = l_res_rate_based_flag_tab(j) -- Added for IPM ER
2253 WHERE budget_version_id = p_fp_cols_rec.x_budget_version_id
2254 AND resource_assignment_id = l_tgt_res_asg_id_tab(j);
2255
2256 END IF; -- copy attributes logic
2257 END IF; -- src id not null
2258 END LOOP;
2259
2260 IF p_pa_debug_mode = 'Y' THEN
2261 PA_DEBUG.RESET_CURR_FUNCTION;
2262 END IF;
2263 EXCEPTION
2264 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2265 l_msg_count := FND_MSG_PUB.count_msg;
2266 IF l_msg_count = 1 THEN
2267 PA_INTERFACE_UTILS_PUB.GET_MESSAGES
2268 ( p_encoded => FND_API.G_TRUE,
2269 p_msg_index => 1,
2270 p_msg_count => l_msg_count,
2271 p_msg_data => l_msg_data,
2272 p_data => l_data,
2273 p_msg_index_out => l_msg_index_out );
2274 x_msg_data := l_data;
2275 x_msg_count := l_msg_count;
2276 ELSE
2277 x_msg_count := l_msg_count;
2278 END IF;
2279
2280 ROLLBACK;
2281 x_return_status := FND_API.G_RET_STS_ERROR;
2282
2283 IF p_pa_debug_mode = 'Y' THEN
2284 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
2285 ( p_msg => 'Invalid Arguments Passed',
2286 p_module_name => l_module_name,
2287 p_log_level => 5 );
2288 PA_DEBUG.RESET_CURR_FUNCTION;
2289 END IF;
2290 RAISE;
2291 WHEN OTHERS THEN
2292 ROLLBACK;
2293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2294 x_msg_count := 1;
2295 x_msg_data := substr(sqlerrm,1,240);
2296 FND_MSG_PUB.ADD_EXC_MSG
2297 ( p_pkg_name => 'PA_FP_GEN_PUB',
2298 p_procedure_name => 'COPY_SRC_ATTRS_TO_TARGET_FCST',
2299 p_error_text => substr(sqlerrm,1,240) );
2300
2301 IF p_pa_debug_mode = 'Y' THEN
2302 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
2303 ( p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
2304 p_module_name => l_module_name,
2305 p_log_level => 5 );
2306 PA_DEBUG.RESET_CURR_FUNCTION;
2307 END IF;
2308 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2309
2310 END COPY_SRC_ATTRS_TO_TARGET_FCST;
2311
2312 END PA_FP_GEN_PUB;