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