[Home] [Help]
PACKAGE BODY: APPS.PA_PROCESS_CI_LINES_PKG
Source
1 package body pa_process_ci_lines_pkg AS
2 /* $Header: PAPPCILB.pls 120.1 2010/10/07 21:26:56 rmunjulu noship $*/
3
4 --
5 -- Procedure update_planning_transaction():
6 -- This API is called from the process_planning_lines() API.
7 -- The API accepts rolled up planning lines and calls the
8 -- update_planning_transactions() API to update resource assignments.
9 --
10 -- History:
11 -- Date Update By Comment
12 -- racheruv Created
13 --
14
15 procedure update_planning_transaction(p_api_version IN NUMBER,
16 p_init_msg_list IN VARCHAR2,
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_msg_count OUT NOCOPY NUMBER,
19 x_msg_data OUT NOCOPY VARCHAR2,
20 p_bvid IN NUMBER,
21 p_project_id in NUMBER,
22 p_task_id_tbl in SYSTEM.PA_NUM_TBL_TYPE,
23 p_effective_from_tbl in SYSTEM.PA_DATE_TBL_TYPE,
24 p_effective_to_tbl in SYSTEM.PA_DATE_TBL_TYPE,
25 p_rlmi_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
26 p_quantity_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
27 p_raw_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
28 p_currency_code_tbl IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE
29 ) IS
30
31
32 l_task_name_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
33 l_task_number_tbl SYSTEM.PA_VARCHAR2_100_TBL_TYPE := SYSTEM.PA_VARCHAR2_100_TBL_TYPE();
34 l_in_start_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
35 l_in_end_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
36 l_planned_people_effort_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
37 l_resource_assignment_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
38 l_assignment_description_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
39 l_project_assignment_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
40 l_planning_resource_alias_tbl SYSTEM.PA_VARCHAR2_80_TBL_TYPE := SYSTEM.PA_VARCHAR2_80_TBL_TYPE();
41 l_resource_class_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
42 l_resource_class_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
43 l_resource_class_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
44 l_res_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
45 l_resource_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
46 l_resource_name SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
47 l_project_role_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
48 l_project_role_name_tbl SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
49 l_organization_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
50 l_organization_name_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
51 l_fc_res_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
52 l_financial_category_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
53 l_supplier_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
54 l_unit_of_measure_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
55 l_spread_curve_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
56 l_etc_method_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
57 l_mfc_cost_type_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
58 l_procure_resource_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
59 l_incurred_by_res_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
60 l_incur_by_resource_name_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
61 l_Incur_by_resource_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
62 l_use_task_schedule_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
63 l_planning_start_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
64 l_planning_end_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
65 l_schedule_start_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
66 l_schedule_end_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
67 l_total_quantity_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
68 l_override_currency_code_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
69 l_total_raw_cost_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
70 l_raw_cost_rate_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
71 l_bill_rate_override_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
72 l_billable_percent_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
73 l_cost_rate_override_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
74 l_burdened_rate_override_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
75 l_sp_fixed_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
76 l_named_role_tbl SYSTEM.PA_VARCHAR2_80_TBL_TYPE := SYSTEM.PA_VARCHAR2_80_TBL_TYPE();
77 l_financial_category_name_tbl SYSTEM.PA_VARCHAR2_80_TBL_TYPE := SYSTEM.PA_VARCHAR2_80_TBL_TYPE();
78 l_supplier_name_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
79 l_attribute_category_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
80 l_attribute1_tbl SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
81 l_scheduled_delay SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
82 l_direct_expenditure_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
83 l_person_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
84 l_job_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
85 l_person_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
86
87 l_burdened_cost_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
88 l_revenue_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
89 l_bill_rate_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
90 l_burdened_rate_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
91 l_task_elem_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
92
93 l_bom_resource_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
94 l_non_labor_resource_tbl SYSTEM.PA_VARCHAR2_20_TBL_TYPE := SYSTEM.PA_VARCHAR2_20_TBL_TYPE();
95 l_inventory_item_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
96 l_item_category_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
97
98 l_expenditure_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
99 l_expenditure_category_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
100 l_event_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
101 l_revenue_category_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
102
103 l_incur_by_res_class_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
104 l_incur_by_role_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
105
106 l_struct_elem_version_id Pa_proj_element_versions.parent_structure_version_id%TYPE := -1;
107 l_elem_ver_id Pa_proj_element_versions.element_version_id%TYPE := -1;
108
109 l_apply_progress_flag VARCHAR2(1) := 'N';
110 l_pji_rollup_required VARCHAR2(1) := 'Y';
111 l_upd_cost_amts_too_for_ta_flg VARCHAR2(1) := 'N';
112
113
114 cursor get_elem_ver_id (c_project_id number, c_task_id number) is
115 select pev.element_version_id,parent_structure_version_id
116 from pa_proj_element_versions pev,pa_proj_elem_ver_structure pevs
117 where pev.project_id = c_project_id
118 and pev.project_id = pevs.project_id
119 and pev.proj_element_id = c_task_id
120 and pev.parent_structure_version_id = pevs.element_version_id
121 --and pevs.CURRENT_WORKING_FLAG = 'Y';
122 -- gboomina modified for bug 9714622 to fetch correct element version id
123 and pevs.element_version_id=PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(c_project_id);
124
125 cursor get_task_details(c_project_id number, c_task_id number) is
126 select task_number
127 from pa_tasks
128 where project_id = c_project_id
129 and task_id = c_task_id;
130
131 cursor get_resource_assignment_id(c_bv_Id NUMBER,
132 c_task_id NUMBER,
133 c_rlmi_id number,
134 c_currency_code varchar2) is
135 select pra.resource_assignment_id,
136 pra.unit_of_measure,
137 pra.project_assignment_id,
138 pra.organization_id,
139 pra.supplier_id,
140 pra.spread_curve_id,
141 pra.etc_method_code,
142 pra.mfc_cost_type_id,
143 pra.procure_resource_flag,
144 decode(pra.use_task_schedule_flag,'Y','Y','N') as use_task_schedule_flag,
145 pra.planning_start_date,
146 pra.planning_end_date,
147 pra.schedule_start_date,
148 pra.schedule_end_date,
149 pra.sp_fixed_date,
150 pra.named_role
151 from pa_resource_assignments pra, pa_resource_asgn_curr prc
152 where pra.budget_version_id = c_bv_Id
153 and pra.task_id = c_Task_Id
154 and pra.resource_list_member_id = c_rlmi_id
155 and pra.resource_assignment_id = prc.resource_assignment_id
156 and prc.txn_currency_code = c_currency_code;
157
158 cursor get_resource_details(p_rlmi_id number) is
159 select alias resource_alias,
160 resource_class_flag,
161 resource_class_code,
162 --resource_class_id,
163 res_type_code,
164 fc_res_type_code
165 from pa_resource_list_members
166 where resource_list_member_id = p_rlmi_id;
167
168 cursor get_dc_burden_cost_rate(c_task_id number,
169 c_rlmi_id number,
170 c_currency_code varchar2) IS
171 select distinct burden_cost_rate
172 from pa_ci_direct_cost_details
173 where task_id = c_task_id
174 and resource_list_member_id = c_rlmi_id
175 and currency_code = c_currency_code
176 and ci_id = (select ci_id from pa_budget_versions where budget_version_id = p_bvid)
177 and burden_cost_rate is not null;
178
179 cursor get_sc_burden_cost_rate(c_task_id number,
180 c_rlmi_id number,
181 c_currency_code varchar2) IS
182 select sum(burdened_cost)/sum(raw_cost)
183 from pa_ci_supplier_details
184 where task_id = c_task_id
185 and resource_list_member_id = c_rlmi_id
186 and currency_code = c_currency_code
187 and ci_id = (select ci_id from pa_budget_versions where budget_version_id = p_bvid)
188 and burdened_cost is not null and raw_cost is not null;
189
190 l_burden_cost_rate number;
191
192 l_api_name CONSTANT varchar2(30) := 'CI.update_planning_trx';
193 l_return_status varchar2(1);
194 l_msg_count number;
195 l_msg_data varchar2(2000);
196
197 begin
198
199 l_return_status := PA_API.START_ACTIVITY(l_api_name,
200 p_init_msg_list,
201 '_PUB',
202 x_return_status);
203 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
204 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
205 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
206 RAISE PA_API.G_EXCEPTION_ERROR;
207 END IF;
208
209 if p_task_id_tbl.count > 0 then
210 for i in p_task_id_tbl.first..p_task_id_tbl.last loop
211 open get_elem_ver_id(p_project_id, p_task_id_tbl(i));
212 fetch get_elem_ver_id into l_elem_ver_id, l_struct_elem_version_id;
213 close get_elem_ver_id;
214
215 l_task_elem_version_id_tbl.extend(1);
216 l_task_elem_version_id_tbl(i) := l_elem_ver_id;
217
218 --p_task_name_tbl
219 l_task_name_tbl.extend(1);
220 l_task_name_tbl(i) := '';
221
222 --p_task_number_tbl
223 l_task_number_tbl.extend(1);
224 l_task_number_tbl(i) := '';
225
226 open get_task_details(p_project_id, p_task_id_tbl(i));
227 fetch get_task_details into l_task_number_tbl(i);
228 close get_task_details;
229
230
231 --p_start_date_tbl
232 l_in_start_date_tbl.extend(1);
233 --l_in_start_date_tbl(i) := to_date(p_effective_from_tbl(i),'YYYY/MM/DD');
234 l_in_start_date_tbl(i) := to_date(null);
235 --p_end_date_tbl
236 l_in_end_date_tbl.extend(1);
237 --l_in_end_date_tbl(i) := to_date(p_effective_to_tbl(i),'YYYY/MM/DD');
238 l_in_end_date_tbl(i) := to_date(null);
239
240
241
242 --p_planned_people_effort_tbl
243 l_planned_people_effort_tbl.extend(1);
244 l_planned_people_effort_tbl(i) := '';
245
246 l_resource_assignment_id_tbl.extend(1);
247 l_project_assignment_id_tbl.extend(1);
248 l_unit_of_measure_tbl.extend(1);
249 l_organization_id_tbl.extend(1);
250 l_supplier_id_tbl.extend(1);
251 l_spread_curve_id_tbl.extend(1);
252 l_etc_method_code_tbl.extend(1);
253 l_mfc_cost_type_id_tbl.extend(1);
254 l_procure_resource_flag_tbl.extend(1);
255 l_use_task_schedule_flag_tbl.extend(1);
256 l_planning_start_date_tbl.extend(1);
257 l_planning_end_date_tbl.extend(1);
258 l_schedule_start_date_tbl.extend(1);
259 l_schedule_end_date_tbl.extend(1);
260 l_sp_fixed_date_tbl.extend(1);
261 l_named_role_tbl.extend(1);
262
263 open get_resource_assignment_id(p_bvId, p_task_id_tbl(i), p_rlmi_id_tbl(i), p_currency_code_tbl(i));
264
265 fetch get_resource_assignment_id into
266 l_resource_assignment_id_tbl(i),l_unit_of_measure_tbl(i),l_project_assignment_id_tbl(i),
267 l_organization_id_tbl(i),l_supplier_id_tbl(i),l_spread_curve_id_tbl(i),
268 l_etc_method_code_tbl(i),l_mfc_cost_type_id_tbl(i),l_procure_resource_flag_tbl(i),
269 l_use_task_schedule_flag_tbl(i),l_planning_start_date_tbl(i),l_planning_end_date_tbl(i),
270 l_schedule_start_date_tbl(i),l_schedule_end_date_tbl(i),l_sp_fixed_date_tbl(i),
271 l_named_role_tbl(i);
272
273 close get_resource_assignment_id;
274
275 -- planning start date
276 l_planning_start_date_tbl(i) := p_effective_from_tbl(i);
277
278 -- planning end date
279 l_planning_end_date_tbl(i) := p_effective_to_tbl(i);
280
281 --p_assignment_description_tbl
282 l_assignment_description_tbl.extend(1);
283 l_assignment_description_tbl(i) := '';
284
285 --p_resource_alias_tbl
286 l_planning_resource_alias_tbl.extend(1);
287 l_planning_resource_alias_tbl(i) := '';
288
289 --p_resource_class_flag_tbl
290 l_resource_class_flag_tbl.extend(1);
291 l_resource_class_flag_tbl(i) := '';
292
293 --p_resource_class_code_tbl
294 l_resource_class_code_tbl.extend(1);
295 l_resource_class_code_tbl(i) := '';
296
297 --p_resource_class_id_tbl
298 l_resource_class_id_tbl.extend(1);
299 l_resource_class_id_tbl(i) := '';
300
301 --p_res_type_code_tbl
302 l_res_type_code_tbl.extend(1);
303 l_res_type_code_tbl(i) := '';
304
305 --p_resource_code_tbl
306 l_resource_code_tbl.extend(1);
307 l_resource_code_tbl(i) := '';
308
309
310 --p_fc_res_type_code_tbl
311 l_fc_res_type_code_tbl.extend(1);
312 l_fc_res_type_code_tbl(i) := '';
313
314 open get_resource_details(p_rlmi_id_tbl(i));
315 fetch get_resource_details into l_planning_resource_alias_tbl(i),
316 l_resource_class_flag_tbl(i), l_resource_class_code_tbl(i),
317 l_res_type_code_tbl(i), l_fc_res_type_code_tbl(i);
318
319 close get_resource_details;
320
321 --p_resource_name
322 l_resource_name.extend(1);
323 l_resource_name(i) := l_planning_resource_alias_tbl(i);
324
325 --p_project_role_id_tbl
326 l_project_role_id_tbl.extend(1);
327 l_project_role_id_tbl(i) := '';
328
329 --p_project_role_name_tbl
330 l_project_role_name_tbl.extend(1);
331 l_project_role_name_tbl(i) := '';
332
333 --p_organization_name_tbl
334 l_organization_name_tbl.extend(1);
335 l_organization_name_tbl(i) := '';
336
337 --p_financial_category_code_tbl
338 l_financial_category_code_tbl.extend(1);
339 l_financial_category_code_tbl(i) := '';
340
341 --p_incur_by_resource_code_tbl
342 l_Incur_by_resource_code_tbl.extend(1);
343 l_Incur_by_resource_code_tbl(i) := '';
344
345 --p_incur_by_resource_name_tbl
346 l_incur_by_resource_name_tbl.extend(1);
347 l_incur_by_resource_name_tbl(i) := '';
348
349
350 --p_txn_currency_override_tbl
351 l_override_currency_code_tbl.extend(1);
352 l_override_currency_code_tbl(i) := '';
353
354
355 --p_burdened_cost_tbl
356 l_burdened_cost_tbl.extend(1);
357 l_burdened_cost_tbl(i) := NULL;
358
359 --p_revenue_tbl
360 l_revenue_tbl.extend(1);
361 l_revenue_tbl(i) := null;
362 --p_cost_rate_tbl
363 l_raw_cost_rate_tbl.extend(1);
364 l_raw_cost_rate_tbl(i) := NULL;
365 --p_bill_rate_tbl
366 l_bill_rate_tbl.extend(1);
367 l_bill_rate_tbl(i) := NULL;
368 --p_bill_rate_override_tbl
369 l_bill_rate_override_tbl.extend(1);
370 l_bill_rate_override_tbl(i) := FND_API.G_MISS_NUM;
371 --p_billable_percent_tbl
372 l_billable_percent_tbl.extend(1);
373 l_billable_percent_tbl(i) := NULL;
374 --p_cost_rate_override_tbl
375 l_cost_rate_override_tbl.extend(1);
376 l_cost_rate_override_tbl(i) := FND_API.G_MISS_NUM;
377 --p_burdened_rate_tbl
378 l_burdened_rate_tbl.extend(1);
379 l_burdened_rate_tbl(i) := NULL;
380 --p_burdened_rate_override_tbl
381 l_burdened_rate_override_tbl.extend(1);
382 l_burdened_rate_override_tbl(i) := FND_API.G_MISS_NUM;
383
384 --p_financial_category_name_tbl
385 l_financial_category_name_tbl.extend(1);
386 l_financial_category_name_tbl(i) := '';
387 --p_supplier_name_tbl
388 l_supplier_name_tbl.extend(1);
389 l_supplier_name_tbl(i) := '';
390
391 --p_attribute_category_tbl 1 --> 30
392 l_attribute_category_tbl.extend(1);
393 l_attribute1_tbl.extend(i);
394 l_attribute_category_tbl(i) := '';
395 l_attribute1_tbl(i) := '';
396
397 --p_scheduled_delay
398 l_scheduled_delay.extend(1);
399 l_scheduled_delay(i) := null;
400
401 --p_direct_expenditure_type_tbl
402 l_direct_expenditure_type_tbl.extend(1);
403 l_direct_expenditure_type_tbl(i) := '';
404
405 l_person_id_tbl.extend(1);
406 l_person_id_tbl(i) := NULL;
407
408 l_job_id_tbl.extend(1);
409 l_job_id_tbl(i) := null;
410
411 l_person_type_code_tbl.extend(1);
412 l_person_type_code_tbl(i) := null;
413
414 l_bom_resource_id_tbl.extend(1);
415 l_bom_resource_id_tbl(i) := null;
416
417 l_non_labor_resource_tbl.extend(1);
418 l_non_labor_resource_tbl(i) := null;
419
420 l_inventory_item_id_tbl.extend(1);
421 l_inventory_item_id_tbl(i) := null;
422
423 l_item_category_id_tbl.extend(1);
424 l_item_category_id_tbl(i) := null;
425
426 l_expenditure_type_tbl.extend(1);
427 l_expenditure_type_tbl(i) := null;
428 l_expenditure_category_tbl.extend(1);
429 l_expenditure_category_tbl(i) := null;
430 l_event_type_tbl.extend(1);
431 l_event_type_tbl(i) := null;
432 l_revenue_category_code_tbl.extend(1);
433 l_revenue_category_code_tbl(i) := null;
434 l_incurred_by_res_flag_tbl.extend(1);
435 l_incurred_by_res_flag_tbl(i) := null;
436 l_incur_by_res_class_code_tbl.extend(1);
437 l_incur_by_res_class_code_tbl(i) := null;
438 l_incur_by_role_id_tbl.extend(1);
439 l_incur_by_role_id_tbl(i) := null;
440 l_total_quantity_tbl.extend(1);
441 l_total_raw_cost_tbl.extend(1);
442
443
444 if p_quantity_tbl.exists(i) then
445 l_total_quantity_tbl(i) := p_quantity_tbl(i);
446 else
447 l_total_quantity_tbl(i) := NULL;
448 end if;
449
450 if p_raw_cost_tbl.exists(i) then
451 l_total_raw_cost_tbl(i) := p_raw_cost_tbl(i);
452
453 -- bug 9696864: provide the new burden cost to remove override.
454 l_burden_cost_rate := null;
455 open get_dc_burden_cost_rate(p_task_id_tbl(i), p_rlmi_id_tbl(i),
456 p_currency_code_tbl(i));
457 fetch get_dc_burden_cost_rate into l_burden_cost_rate;
458 close get_dc_burden_cost_rate;
459
460 if l_burden_cost_rate is null then
461 open get_sc_burden_cost_rate(p_task_id_tbl(i), p_rlmi_id_tbl(i),
462 p_currency_code_tbl(i));
463 fetch get_sc_burden_cost_rate into l_burden_cost_rate;
464 close get_sc_burden_cost_rate;
465 end if;
466
467 l_burdened_cost_tbl(i) := p_raw_cost_tbl(i) * l_burden_cost_rate;
468 -- bug 9696864: end change
469 else
470 l_total_raw_cost_tbl(i) := NULL;
471 end if;
472
473 end loop;
474 end if;
475
476 --calling api
477
478 pa_fp_planning_transaction_pub.update_planning_transactions(
479 p_context => 'BUDGET'
480 ,p_calling_context => NULL
481 ,p_struct_elem_version_id => l_struct_elem_version_id
482 ,p_budget_version_id => p_bvid
483 ,p_task_elem_version_id_tbl => l_task_elem_version_id_tbl
484 ,p_task_name_tbl => l_task_name_tbl
485 ,p_task_number_tbl => l_task_number_tbl
486 ,p_start_date_tbl => l_in_start_date_tbl
487 ,p_end_date_tbl => l_in_end_date_tbl
488 ,p_planned_people_effort_tbl => l_planned_people_effort_tbl
489 ,p_resource_assignment_id_tbl => l_resource_assignment_id_tbl
490 ,p_resource_list_member_id_tbl => p_rlmi_id_tbl
491 ,p_assignment_description_tbl => l_assignment_description_tbl
492 ,p_project_assignment_id_tbl => l_project_assignment_id_tbl
493 ,p_resource_alias_tbl => l_planning_resource_alias_tbl
494 ,p_resource_class_flag_tbl => l_resource_class_flag_tbl
495 ,p_resource_class_code_tbl => l_resource_class_code_tbl
496 ,p_resource_class_id_tbl => l_resource_class_id_tbl
497 ,p_res_type_code_tbl => l_res_type_code_tbl
498 ,p_resource_code_tbl => l_resource_code_tbl
499 ,p_resource_name => l_resource_name
500 ,p_person_id_tbl => l_person_id_tbl
501 ,p_job_id_tbl => l_job_id_tbl
502 ,p_person_type_code => l_person_type_code_tbl
503 ,p_bom_resource_id_tbl => l_bom_resource_id_tbl
504 ,p_non_labor_resource_tbl => l_non_labor_resource_tbl
505 ,p_inventory_item_id_tbl => l_inventory_item_id_tbl
506 ,p_item_category_id_tbl => l_item_category_id_tbl
507 ,p_project_role_id_tbl => l_project_role_id_tbl
508 ,p_project_role_name_tbl => l_project_role_name_tbl
509 ,p_organization_id_tbl => l_organization_id_tbl
510 ,p_organization_name_tbl => l_organization_name_tbl
511 ,p_fc_res_type_code_tbl => l_fc_res_type_code_tbl
512 ,p_financial_category_code_tbl => l_financial_category_code_tbl
513 ,p_expenditure_type_tbl => l_expenditure_type_tbl
514 ,p_expenditure_category_tbl => l_expenditure_category_tbl
515 ,p_event_type_tbl => l_event_type_tbl
516 ,p_revenue_category_code_tbl => l_revenue_category_code_tbl
517 ,p_incurred_by_res_flag_tbl => l_incurred_by_res_flag_tbl
518 ,p_incur_by_res_class_code_tbl => l_incur_by_res_class_code_tbl
519 ,p_incur_by_role_id_tbl => l_incur_by_role_id_tbl
520 ,p_supplier_id_tbl => l_supplier_id_tbl
521 ,p_unit_of_measure_tbl => l_unit_of_measure_tbl,
522 p_spread_curve_id_tbl => l_spread_curve_id_tbl,
523 p_etc_method_code_tbl => l_etc_method_code_tbl,
524 p_mfc_cost_type_id_tbl => l_mfc_cost_type_id_tbl,
525 p_procure_resource_flag_tbl => l_procure_resource_flag_tbl,
526 p_incur_by_resource_code_tbl => l_Incur_by_resource_code_tbl,
527 p_incur_by_resource_name_tbl => l_incur_by_resource_name_tbl,
528 p_use_task_schedule_flag_tbl => l_use_task_schedule_flag_tbl,
529 p_planning_start_date_tbl => l_planning_start_date_tbl,
530 p_planning_end_date_tbl => l_planning_end_date_tbl,
531 p_schedule_start_date_tbl => l_schedule_start_date_tbl,
532 p_schedule_end_date_tbl => l_schedule_end_date_tbl,
533 p_quantity_tbl => l_total_quantity_tbl,
534 p_currency_code_tbl => p_currency_code_tbl,
535 p_txn_currency_override_tbl => l_override_currency_code_tbl,
536 p_raw_cost_tbl => l_total_raw_cost_tbl,
537 p_burdened_cost_tbl => l_burdened_cost_tbl,
538 p_revenue_tbl => l_revenue_tbl,
539 p_cost_rate_tbl => l_raw_cost_rate_tbl,
540 p_cost_rate_override_tbl => l_cost_rate_override_tbl,
541 p_burdened_rate_tbl => l_burdened_rate_tbl,
542 p_burdened_rate_override_tbl => l_burdened_rate_override_tbl,
543 p_bill_rate_tbl => l_bill_rate_tbl,
544 p_bill_rate_override_tbl => l_bill_rate_override_tbl,
545 p_billable_percent_tbl => l_billable_percent_tbl,
546 p_sp_fixed_date_tbl => l_sp_fixed_date_tbl,
547 p_named_role_tbl => l_named_role_tbl,
548 p_financial_category_name_tbl => l_financial_category_name_tbl,
549 p_supplier_name_tbl => l_supplier_name_tbl,
550 p_attribute_category_tbl => l_attribute_category_tbl,
551 p_attribute1_tbl => l_attribute1_tbl,
552 p_attribute2_tbl => l_attribute1_tbl,
553 p_attribute3_tbl => l_attribute1_tbl,
554 p_attribute4_tbl => l_attribute1_tbl,
555 p_attribute5_tbl => l_attribute1_tbl,
556 p_attribute6_tbl => l_attribute1_tbl,
557 p_attribute7_tbl => l_attribute1_tbl,
558 p_attribute8_tbl => l_attribute1_tbl,
559 p_attribute9_tbl => l_attribute1_tbl,
560 p_attribute10_tbl => l_attribute1_tbl,
561 p_attribute11_tbl => l_attribute1_tbl,
562 p_attribute12_tbl => l_attribute1_tbl,
563 p_attribute13_tbl => l_attribute1_tbl,
564 p_attribute14_tbl => l_attribute1_tbl,
565 p_attribute15_tbl => l_attribute1_tbl,
566 p_attribute16_tbl => l_attribute1_tbl,
567 p_attribute17_tbl => l_attribute1_tbl,
568 p_attribute18_tbl => l_attribute1_tbl,
569 p_attribute19_tbl => l_attribute1_tbl,
570 p_attribute20_tbl => l_attribute1_tbl,
571 p_attribute21_tbl => l_attribute1_tbl,
572 p_attribute22_tbl => l_attribute1_tbl,
573 p_attribute23_tbl => l_attribute1_tbl,
574 p_attribute24_tbl => l_attribute1_tbl,
575 p_attribute25_tbl => l_attribute1_tbl,
576 p_attribute26_tbl => l_attribute1_tbl,
577 p_attribute27_tbl => l_attribute1_tbl,
578 p_attribute28_tbl => l_attribute1_tbl,
579 p_attribute29_tbl => l_attribute1_tbl,
580 p_attribute30_tbl => l_attribute1_tbl
581 ,p_apply_progress_flag => 'N'
582 ,p_scheduled_delay => l_scheduled_delay
583 ,p_pji_rollup_required => 'Y'
584 ,p_upd_cost_amts_too_for_ta_flg => 'N'
585 ,p_distrib_amts => 'Y'
586 ,p_direct_expenditure_type_tbl => l_direct_expenditure_type_tbl,
587 x_return_status => l_return_status,
588 x_msg_count => l_msg_count,
589 x_msg_data => l_msg_data);
590
591 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
592 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
593 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
594 RAISE PA_API.G_EXCEPTION_ERROR;
595 END IF;
596
597 PA_API.END_ACTIVITY(l_msg_count, l_msg_data);
598 x_return_status := PA_API.G_RET_STS_SUCCESS;
599
600 EXCEPTION
601 WHEN PA_API.G_EXCEPTION_ERROR THEN
602 x_return_status := PA_API.HANDLE_EXCEPTIONS
603 (
604 l_api_name,
605 G_PKG_NAME,
606 'PA_API.G_RET_STS_ERROR',
607 x_msg_count,
608 x_msg_data,
609 '_PUB'
610 );
611 WHEN PA_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
612 x_return_status := PA_API.HANDLE_EXCEPTIONS
613 (
614 l_api_name,
615 G_PKG_NAME,
616 'PA_API.G_RET_STS_UNEXP_ERROR',
617 x_msg_count,
618 x_msg_data,
619 '_PUB'
620 );
621 WHEN OTHERS THEN
622 x_return_status := PA_API.HANDLE_EXCEPTIONS
623 (
624 l_api_name,
625 G_PKG_NAME,
626 'OTHERS',
627 x_msg_count,
628 x_msg_data,
629 '_PUB'
630 );
631 end update_planning_transaction;
632 --
633 -- Procedure delete_planning_transaction():
634 -- This API is called from the process_planning_lines() API.
635 -- The API accepts rolled up planning lines and calls the
636 -- delete_planning_transactions() API to delete resource assignments.
637 --
638 -- History:
639 -- Date Update By Comment
640 -- racheruv Created
641 --
642 procedure delete_planning_transaction(p_api_version IN NUMBER,
643 p_init_msg_list IN VARCHAR2,
644 x_return_status OUT NOCOPY VARCHAR2,
645 x_msg_count OUT NOCOPY NUMBER,
646 x_msg_data OUT NOCOPY VARCHAR2,
647 p_bvid IN number,
648 p_project_id IN number,
649 p_task_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
650 p_currency_tbl IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE,
651 p_rlmi_tbl IN SYSTEM.PA_NUM_TBL_TYPE
652 ) IS
653
654 cursor get_assignment_details(c_bvId number,
655 c_task_id number,
656 c_rlmi_id number,
657 c_currency_code varchar2) is
658 select pra.RESOURCE_ASSIGNMENT_ID, ppe.ELEMENT_NUMBER, ppe.NAME
659 from pa_resource_assignments pra, pa_proj_elements ppe, pa_tasks pt,
660 pa_resource_asgn_curr prc
661 WHERE pra.budget_version_id = c_bvId
662 and pra.task_id = c_task_id
663 and pra.RESOURCE_LIST_MEMBER_ID = c_rlmi_id
664 and pra.resource_assignment_id = prc.resource_assignment_id
665 and prc.txn_currency_code = c_currency_code
666 and pt.task_id = ppe.proj_element_id;
667
668 cursor get_elem_ver_id (c_proj_id number, c_task_id number) is
669 select pev.element_version_id
670 from pa_proj_element_versions pev, pa_proj_elem_ver_structure pevs
671 where pev.project_id = c_proj_id and pev.project_id=pevs.project_id
672 and pev.proj_element_id=c_task_id
673 and pev.parent_structure_version_id=pevs.element_version_id
674 --and pevs.CURRENT_WORKING_FLAG='Y';
675 -- gboomina modified for bug 9714622 to fetch correct element version id
676 and pevs.element_version_id=PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(c_proj_id);
677
678
679 l_task_elem_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
680 l_task_number_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
681 l_task_name_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
682 l_res_assgn_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
683
684 l_resource_assignment_id number;
685 l_task_number varchar2(30);
686 l_task_name varchar2(30);
687 l_task_elem_version_id number;
688
689 l_rbs_element_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
690 l_rate_based_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.pa_varchar2_1_tbl_type();
691 l_resource_class_code_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
692
693 l_api_name CONSTANT VARCHAR2(30) := 'CI.delete_planning_trx';
694 l_return_status varchar2(1);
695 l_msg_data varchar2(2000);
696 l_msg_count number;
697
698 --(
699 begin
700
701 l_return_status := PA_API.START_ACTIVITY(l_api_name,
702 p_init_msg_list,
703 '_PUB',
704 x_return_status);
705 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
706 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
707 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
708 RAISE PA_API.G_EXCEPTION_ERROR;
709 END IF;
710
711 if p_task_tbl.count > 0 then
712 for i in p_task_tbl.first..p_task_tbl.last loop
713 open get_assignment_details(p_bvId, p_task_tbl(i),
714 p_rlmi_tbl(i), p_currency_tbl(i));
715 fetch get_assignment_details into l_resource_assignment_id,
716 l_task_number, l_task_name;
717
718 l_task_number_tbl.extend(1);
719 l_task_name_tbl.extend(1);
720 l_res_assgn_tbl.extend(1);
721
722 l_res_assgn_tbl(i) := l_resource_assignment_id;
723 l_task_number_tbl(i) := l_task_number;
724 l_task_name_tbl(i) := l_task_name;
725
726 close get_assignment_details;
727
728 open get_elem_ver_id(p_project_id, p_task_tbl(i));
729 fetch get_elem_ver_id into l_task_elem_version_id;
730
731 l_task_elem_version_id_tbl.extend(1);
732 l_task_elem_version_id_tbl(i) := l_task_elem_version_id;
733
734
735 l_rbs_element_id_tbl.extend(1);
736 l_rbs_element_id_tbl(i) := null;
737
738 l_rate_based_flag_tbl.extend(1);
739 l_rate_based_flag_tbl(i) := null;
740
741 l_resource_class_code_tbl.extend(1);
742 l_resource_class_code_tbl(i) := null;
743
744 close get_elem_ver_id;
745 end loop;
746 end if;
747
748 pa_fp_planning_transaction_pub.delete_planning_transactions(
749 p_context => 'BUDGET'
750 ,p_calling_context => NULL
751 ,p_task_or_res => 'ASSIGNMENT'
752 ,p_element_version_id_tbl => l_task_elem_version_id_tbl
753 ,p_task_number_tbl => l_task_number_tbl
754 ,p_task_name_tbl => l_task_name_tbl
755 ,p_resource_assignment_tbl => l_res_assgn_tbl
756 ,p_validate_delete_flag => 'N'
757 ,p_currency_code_tbl => p_currency_tbl
758 ,p_calling_module => NULL
759 ,p_task_id_tbl => p_task_tbl
760 ,p_rbs_element_id_tbl => l_rbs_element_id_tbl
761 ,p_rate_based_flag_tbl => l_rate_based_flag_tbl
762 ,p_resource_class_code_tbl => l_resource_class_code_tbl
763 ,p_rollup_required_flag => 'Y'
764 ,p_pji_rollup_required => 'Y'
765 ,x_return_status => l_return_status
766 ,x_msg_count => l_msg_count
767 ,x_msg_data => l_msg_data);
768
769 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
770 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
771 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
772 RAISE PA_API.G_EXCEPTION_ERROR;
773 END IF;
774
775 PA_API.END_ACTIVITY(l_msg_count, l_msg_data);
776 x_return_status := PA_API.G_RET_STS_SUCCESS;
777
778 EXCEPTION
779 WHEN PA_API.G_EXCEPTION_ERROR THEN
780 x_return_status := PA_API.HANDLE_EXCEPTIONS
781 (
782 l_api_name,
783 G_PKG_NAME,
784 'PA_API.G_RET_STS_ERROR',
785 x_msg_count,
786 x_msg_data,
787 '_PUB'
788 );
789 WHEN PA_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
790 x_return_status := PA_API.HANDLE_EXCEPTIONS
791 (
792 l_api_name,
793 G_PKG_NAME,
794 'PA_API.G_RET_STS_UNEXP_ERROR',
795 x_msg_count,
796 x_msg_data,
797 '_PUB'
798 );
799 WHEN OTHERS THEN
800 x_return_status := PA_API.HANDLE_EXCEPTIONS
801 (
802 l_api_name,
803 G_PKG_NAME,
804 'OTHERS',
805 x_msg_count,
806 x_msg_data,
807 '_PUB'
808 );
809 end delete_planning_transaction;
810
811 --
812 -- Procedure insert_planning_transaction():
813 -- This API is called from the process_planning_lines() API.
814 -- The API accepts rolled up planning lines and calls the
815 -- add_new_resource_assignments() API to create a new resource assignment.
816 --
817 -- History:
818 -- Date Update By Comment
819 -- racheruv Created
820 --
821
822 procedure insert_planning_transaction(p_api_version IN NUMBER,
823 p_init_msg_list IN VARCHAR2,
824 x_return_status OUT NOCOPY VARCHAR2,
825 x_msg_count OUT NOCOPY NUMBER,
826 x_msg_data OUT NOCOPY VARCHAR2,
827 P_BVID IN NUMBER,
828 P_PROJECT_ID IN NUMBER,
829 P_TASK_ID_TBL IN SYSTEM.pa_num_tbl_type,
830 P_RLMI_ID_TBL IN SYSTEM.pa_num_tbl_type,
831 P_CURRENCY_CODE_TBL IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE,
832 P_QUANTITY_TBL IN SYSTEM.pa_num_tbl_type,
833 P_RAW_COST_TBL IN SYSTEM.pa_num_tbl_type
834 ) IS
835 --{
836 l_task_elem_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
837 l_resource_list_member_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
838 l_quantity_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
839 l_currency_code_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
840 l_raw_cost_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
841 l_burdened_cost_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
842 l_revenue_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
843 l_cost_rate_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
844 l_bill_rate_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
845 l_burdened_rate_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
846 l_unplanned_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
847 l_expenditure_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
848
849
850 cursor get_elem_ver_id (c_proj_id number, c_task_id number) is
851 select pev.element_version_id
852 from pa_proj_element_versions pev, pa_proj_elem_ver_structure pevs
853 where pev.project_id = c_proj_id
854 and pev.project_id=pevs.project_id
855 and pev.proj_element_id=c_task_id
856 and pev.parent_structure_version_id=pevs.element_version_id
857 -- and pevs.CURRENT_WORKING_FLAG='Y';
858 -- gboomina modified for bug 9714622 to fetch correct element version id
859 and pevs.element_version_id=PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(c_proj_id);
860
861
862
863 l_api_name CONSTANT varchar2(30) := 'CI.insert_planning_trx';
864 l_return_status varchar2(1);
865 l_msg_data varchar2(2000);
866 l_msg_count number;
867
868 begin
869
870 l_return_status := PA_API.START_ACTIVITY(l_api_name,
871 p_init_msg_list,
872 '_PUB',
873 x_return_status);
874
875 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
876 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
877 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
878 RAISE PA_API.G_EXCEPTION_ERROR;
879 END IF;
880
881 if p_bvId is not null then
882 if p_task_id_tbl.count > 0 then
883 for i in p_task_id_tbl.first..p_task_id_tbl.last loop
884
885 --task_element_version_id
886 l_task_elem_version_id_tbl.extend(1);
887 open get_elem_ver_id(p_project_id, p_task_id_tbl(i));
888 fetch get_elem_ver_id into l_task_elem_version_id_tbl(i);
889 close get_elem_ver_id;
890
891
892 --burdened cost
893 l_burdened_cost_tbl.extend(1);
894 l_burdened_cost_tbl(i) := NULL;
895
896 --revenue
897 l_revenue_tbl.extend(1);
898 l_revenue_tbl(i) := null;
899
900 --cost rate
901 l_cost_rate_tbl.extend(1);
902 l_cost_rate_tbl(i) := NULL;
903
904 --bill rate
905 l_bill_rate_tbl.extend(1);
906 l_bill_rate_tbl(i) := null;
907
908 --burdened rate
909 l_burdened_rate_tbl.extend(1);
910 l_burdened_rate_tbl(i) := NULL;
911
912 --unplanned flag
913 l_unplanned_flag_tbl.extend(1);
914 l_unplanned_flag_tbl(i) := NULL;
915
916 --expenditure type
917 l_expenditure_type_tbl.extend(1);
918 l_expenditure_type_tbl(i) := NULL;
919
920
921 end loop;
922 end if;
923
924
925 --call pa_planning_element_utils.add_new_resource_assignments
926 -- to add the resource assignment
927 pa_planning_element_utils.add_new_resource_assignments(
928 p_context => 'BUDGET',
929 p_project_id => p_project_id,
930 p_budget_version_id => p_bvId,
931 p_task_elem_version_id_tbl => l_task_elem_version_id_tbl,
932 p_resource_list_member_id_tbl => p_rlmi_id_tbl,
933 p_quantity_tbl => p_quantity_tbl,
934 p_currency_code_tbl => p_currency_code_tbl,
935 p_raw_cost_tbl => p_raw_cost_tbl,
936 p_burdened_cost_tbl => l_burdened_cost_tbl,
937 p_revenue_tbl => l_revenue_tbl,
938 p_cost_rate_tbl => l_cost_rate_tbl,
939 p_bill_rate_tbl => l_bill_rate_tbl,
940 p_burdened_rate_tbl => l_burdened_rate_tbl,
941 p_unplanned_flag_tbl => l_unplanned_flag_tbl,
942 p_expenditure_type_tbl => l_expenditure_type_tbl,
943 x_return_status => l_return_status,
944 x_msg_count => l_msg_count,
945 x_msg_data => l_msg_data);
946
947 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
948 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
949 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
950 RAISE PA_API.G_EXCEPTION_ERROR;
951 END IF;
952 end if; -- if p_bvid is not null
953 --}
954
955 PA_API.END_ACTIVITY(l_msg_count, l_msg_data);
956 x_return_status := PA_API.G_RET_STS_SUCCESS;
957
958 EXCEPTION
959 WHEN PA_API.G_EXCEPTION_ERROR THEN
960 x_return_status := PA_API.HANDLE_EXCEPTIONS
961 (
962 l_api_name,
963 G_PKG_NAME,
964 'PA_API.G_RET_STS_ERROR',
965 x_msg_count,
966 x_msg_data,
967 '_PUB'
968 );
969 WHEN PA_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
970 x_return_status := PA_API.HANDLE_EXCEPTIONS
971 (
972 l_api_name,
973 G_PKG_NAME,
974 'PA_API.G_RET_STS_UNEXP_ERROR',
975 x_msg_count,
976 x_msg_data,
977 '_PUB'
978 );
979 WHEN OTHERS THEN
980 x_return_status := PA_API.HANDLE_EXCEPTIONS
981 (
982 l_api_name,
983 G_PKG_NAME,
984 'OTHERS',
985 x_msg_count,
986 x_msg_data,
987 '_PUB'
988 );
989 end insert_planning_transaction;
990
991 --
992 -- Procedure process_planning_lines():
993 -- This API is called from the process API of the direct cost and the
994 -- supplier cost regions. This is the entry API to this package.
995 -- The API rolls up the input data to Task/Planning Resource/Currency and
996 -- verifies if a resource assignment is present for that combination.
997 -- Based on the existence of the resource assignment and the action type,
998 -- insert/update/delete API is called to affect the resource assignment.
999 --
1000 -- p_calling_context: valid values: 'DIRECT_COST'/'SUPPLIER_COST'
1001 -- p_action_type: valid values are: 'INSERT'/'UPDATE'/'DELETE'
1002 --
1003 -- History:
1004 -- Date Update By Comment
1005 -- racheruv Created
1006
1007 procedure process_planning_lines(p_api_version IN NUMBER,
1008 p_init_msg_list IN VARCHAR2,
1009 x_return_status OUT NOCOPY VARCHAR2,
1010 x_msg_count OUT NOCOPY NUMBER,
1011 x_msg_data OUT NOCOPY VARCHAR2,
1012 p_calling_context IN VARCHAR2,
1013 p_action_type IN VARCHAR2,
1014 p_bvid IN NUMBER,
1015 p_ci_id IN NUMBER,
1016 p_line_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
1017 p_project_id IN NUMBER,
1018 p_task_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
1019 p_currency_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
1020 p_rlmi_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
1021 p_res_assgn_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
1022 p_quantity_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1023 DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
1024 p_raw_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1025 DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
1026 ) IS
1027
1028
1029 cursor dc_csr (p_ci_id number, p_project_id number,
1030 p_task_id number,
1031 p_resource_list_member_id number,
1032 p_currency_code varchar2) is
1033 select min(effective_from),
1034 max(effective_to),
1035 sum(quantity),
1036 sum(raw_cost),
1037 count(*)
1038 from pa_ci_direct_cost_details
1039 where ci_id = p_ci_id
1040 and task_id = p_task_id
1041 and resource_list_member_id = p_resource_list_member_id
1042 and currency_code = p_currency_code;
1043
1044 cursor sup_csr(p_ci_id number, p_project_id number,
1045 p_task_id number,
1046 p_resource_list_member_id number,
1047 p_currency_code varchar2) is
1048 select min(from_change_date) effective_from,
1049 max(to_change_date) effective_to,
1050 sum(NULL) quantity,
1051 sum(raw_cost),
1052 count(*)
1053 from pa_ci_supplier_details
1054 where ci_id = p_ci_id
1055 and task_id = p_task_id
1056 and resource_list_member_id = p_resource_list_member_id
1057 and currency_code = p_currency_code;
1058
1059 j number := 0;
1060 TYPE varchar1_tbl IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
1061
1062 rollup_project_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1063 rollup_task_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1064 rollup_rlmi_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1065 rollup_raw_cost_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1066 rollup_quantity_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1067 rollup_res_assgn_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1068 rollup_effective_from SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
1069 rollup_effective_to SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
1070 db_raw_cost_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1071 db_quantity_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1072 db_res_assgn_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1073
1074 rollup_currency_tbl SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
1075 rollup_rate_based varchar1_tbl;
1076 rollup_ra_exists varchar1_tbl;
1077 rolled_up varchar1_tbl;
1078
1079 upd_task_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1080 upd_effective_from SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
1081 upd_effective_to SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
1082 upd_rlmi_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1083 upd_quantity_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1084 upd_raw_cost_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1085 upd_currency_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
1086
1087 ins_task_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1088 ins_rlmi_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1089 ins_quantity_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1090 ins_raw_cost_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1091 ins_currency_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
1092
1093 del_task_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1094 del_rlmi_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1095 del_currency_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
1096
1097 ip_task_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1098
1099 qty_from_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1100 amt_from_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type();
1101
1102 TYPE num_tbl is table of number index by binary_integer;
1103 rollup_count NUM_TBL;
1104
1105 TYPE curr_tbl IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
1106
1107 fc_currency_tbl curr_tbl;
1108
1109 cursor does_ra_exist(c_bvid number,
1110 c_task_id number,
1111 c_rlmi_id number,
1112 c_currency_code varchar2) is
1113 select 'Y', pra.resource_assignment_id,
1114 total_quantity,
1115 total_txn_raw_cost
1116 from pa_resource_assignments pra, pa_resource_asgn_curr prac
1117 where pra.budget_version_id = c_bvid and pra.task_id = c_task_id
1118 and pra.resource_list_member_id = c_rlmi_id
1119 and prac.txn_currency_code = c_currency_code
1120 and prac.resource_assignment_id = pra.resource_assignment_id;
1121
1122 cursor get_resource_asgn_csr(c_bvid number,
1123 c_task_id number,
1124 c_rlmi_id number) is
1125 select txn_currency_code
1126 from pa_resource_assignments pra, pa_resource_asgn_curr prc
1127 where pra.resource_assignment_id = prc.resource_assignment_id
1128 and pra.budget_version_id = c_bvid
1129 and pra.task_id = c_task_id
1130 and pra.resource_list_member_id = c_rlmi_id
1131 and prc.total_quantity is not null;
1132 --and prc.total_txn_raw_cost is not null;
1133
1134 l_rate_based_res_curr varchar2(15);
1135
1136 txn_quantity_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1137 txn_raw_cost_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1138
1139 subtype PaCiDirCostTblType is pa_ci_dir_cost_pvt.PaCiDirectCostDetailsTblType;
1140 DeleteDCTblRecs PaCiDirCostTblType;
1141
1142 l_api_version number := 1;
1143 l_api_name constant varchar2(30) := 'CI.process_planning_lines';
1144 l_return_status varchar2(1);
1145 l_msg_count number;
1146 l_msg_data varchar2(2000);
1147
1148 k number := 0;
1149 tbl_count number := 0;
1150 l_found number := 0;
1151 l_delete varchar2(1) := 'N';
1152 l_update varchar2(1) := 'N';
1153 l_count number := 0;
1154 upd_count number := 0;
1155 fc_count number := 0;
1156 fc_quantity number := 0;
1157
1158 l_effective_from date;
1159 l_effective_to date;
1160 l_quantity number;
1161 l_raw_cost number;
1162
1163 i_resource_rate number;
1164 i_burden_cost_rate number;
1165
1166 begin
1167
1168 l_return_status := PA_API.START_ACTIVITY(l_api_name,
1169 p_init_msg_list,
1170 '_PUB',
1171 x_return_status);
1172
1173 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
1174 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
1175 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
1176 RAISE PA_API.G_EXCEPTION_ERROR;
1177 END IF;
1178
1179 -- Step 1: rollup the input tables to P/T/R/C
1180 if p_task_id_tbl.count > 0 then
1181 for i in p_task_id_tbl.first..p_task_id_tbl.last loop
1182
1183 ip_task_id_tbl.extend(1);
1184 ip_task_id_tbl(i) := p_task_id_tbl(i);
1185 rolled_up(i) := 'N';
1186
1187 qty_from_tbl.extend(1);
1188 amt_from_tbl.extend(1);
1189 end loop;
1190 end if;
1191
1192 if ip_task_id_tbl.count > 0 then
1193 j := 0;
1194
1195 rollup_task_tbl.extend(1);
1196 rollup_rlmi_tbl.extend(1);
1197 rollup_currency_tbl.extend(1);
1198 rollup_raw_cost_tbl.extend(1);
1199 rollup_quantity_tbl.extend(1);
1200
1201 k := 1;
1202
1203 for i in ip_task_id_tbl.first..ip_task_id_tbl.last loop
1204
1205 if rolled_up(i) = 'Y' then
1206 goto null_processing;
1207 else
1208 if k <> 1 then
1209 rollup_task_tbl.extend(1);
1210 rollup_rlmi_tbl.extend(1);
1211 rollup_currency_tbl.extend(1);
1212 rollup_raw_cost_tbl.extend(1);
1213 rollup_quantity_tbl.extend(1);
1214 end if;
1215 end if;
1216
1217 rollup_task_tbl(k) := ip_task_id_tbl(i);
1218 rollup_rlmi_tbl(k) := p_rlmi_id_tbl(i);
1219 rollup_currency_tbl(k) := p_currency_code_tbl(i);
1220
1221
1222 rolled_up(i) := 'Y';
1223 if nvl(pa_planning_resource_utils.get_rate_based_flag(p_rlmi_id_tbl(i)), 'N') = 'Y' then
1224 rollup_rate_based(k) := 'Y';
1225 else
1226 rollup_rate_based(k) := 'N';
1227 end if;
1228
1229
1230 if i = ip_task_id_tbl.last then
1231 exit;
1232 end if;
1233
1234 for j in i+1..ip_task_id_tbl.count loop
1235
1236 if (rollup_task_tbl(k) = ip_task_id_tbl(j) and
1237 rollup_rlmi_tbl(k) = p_rlmi_id_tbl(j) and
1238 rollup_currency_tbl(k) = p_currency_code_tbl(j)) then
1239 rolled_up(j) := 'Y';
1240 end if;
1241 end loop;
1242
1243 k := k + 1;
1244
1245 <<NULL_PROCESSING>>
1246 if i = ip_task_id_tbl.last then
1247 exit;
1248 end if;
1249 end loop;
1250 end if; -- p_task_id_tbl.count
1251
1252 -- Step 2: rollup the db data to P/T/R/C .. based on the table above
1253 if rollup_task_tbl.count > 0 then
1254
1255 for i in rollup_task_tbl.first..rollup_task_tbl.last loop
1256 rollup_effective_from.extend(1);
1257 rollup_effective_to.extend(1);
1258 db_quantity_tbl.extend(1);
1259 db_raw_cost_tbl.extend(1);
1260 rollup_res_assgn_tbl.extend(1);
1261 txn_quantity_tbl.extend(1);
1262 txn_raw_cost_tbl.extend(1);
1263 rollup_count(i) := null;
1264 end loop;
1265
1266 for i in rollup_task_tbl.first..rollup_task_tbl.last loop -- loop 1
1267
1268 --elsif p_calling_context = 'DIRECT_COST' then
1269 open dc_csr(p_ci_id, p_project_id, rollup_task_tbl(i),
1270 rollup_rlmi_tbl(i), rollup_currency_tbl(i));
1271
1272 fetch dc_csr into l_effective_from, l_effective_to,
1273 l_quantity, l_raw_cost,
1274 l_count;
1275 if nvl(l_count, 0) > 0 then
1276 rollup_effective_from(i) := l_effective_from;
1277 rollup_effective_to(i) := l_effective_to;
1278 db_quantity_tbl(i) := l_quantity;
1279 db_raw_cost_tbl(i) := l_raw_cost;
1280 rollup_count(i) := l_count;
1281 end if;
1282 close dc_csr;
1283
1284 --if p_calling_context = 'SUPPLIER_COST' then
1285
1286 open sup_csr(p_ci_id, p_project_id, rollup_task_tbl(i),
1287 rollup_rlmi_tbl(i), rollup_currency_tbl(i));
1288
1289 fetch sup_csr into l_effective_from, l_effective_to,
1290 l_quantity, l_raw_cost,
1291 l_count;
1292 if nvl(l_count, 0) > 0 then
1293 rollup_effective_from(i) := least(l_effective_from, nvl(rollup_effective_from(i),
1294 l_effective_from));
1295
1296 rollup_effective_to(i) := greatest(l_effective_to, nvl(rollup_effective_to(i),
1297 l_effective_to));
1298
1299 db_quantity_tbl(i) := nvl(db_quantity_tbl(i), 0) + l_quantity;
1300 db_raw_cost_tbl(i) := nvl(db_raw_cost_tbl(i), 0) + l_raw_cost;
1301 rollup_count(i) := nvl(rollup_count(i), 0) + l_count;
1302 end if;
1303 close sup_csr;
1304
1305 if rollup_count(i) is null then
1306 rollup_count(i) := 0;
1307 end if;
1308
1309
1310 if rollup_rate_based(i) = 'Y' then
1311 db_raw_cost_tbl(i) := NULL;
1312 else
1313 db_quantity_tbl(i) := NULL;
1314 end if;
1315 --end if;
1316
1317 -- Step 3: verify if there exists a resource_assignment data for the rollup
1318 open does_ra_exist(p_bvid, rollup_task_tbl(i),
1319 rollup_rlmi_tbl(i),
1320 rollup_currency_tbl(i));
1321 fetch does_ra_exist into rollup_ra_exists(i), rollup_res_assgn_tbl(i),
1322 txn_quantity_tbl(i), txn_raw_cost_tbl(i);
1323
1324 if does_ra_exist%NOTFOUND then
1325 rollup_ra_exists(i) := 'N';
1326 end if;
1327 close does_ra_exist;
1328
1329 end loop; -- loop 1
1330 end if; -- rollup_task_tbl.count > 0
1331
1332 -- Step 4: based on the existence of the resource assignment and the
1333 -- action_type, call the appropriate api
1334 k := 0;
1335 j := 0;
1336 if p_action_type = 'DELETE' then
1337 for i in rollup_task_tbl.first..rollup_task_tbl.last loop -- delete loop
1338 l_delete := 'N';
1339 l_update := 'N';
1340
1341 if rollup_count(i) = 0 then
1342 l_delete := 'Y';
1343 else
1344 l_update := 'Y';
1345 end if;
1346 if l_delete = 'Y' then
1347 del_task_tbl.extend(1);
1348 del_currency_tbl.extend(1);
1349 del_rlmi_tbl.extend(1);
1350
1351 j := j + 1;
1352 del_task_tbl(j) := rollup_task_tbl(i);
1353 del_currency_tbl(j) := rollup_currency_tbl(i);
1354 del_rlmi_tbl(j) := rollup_rlmi_tbl(i);
1355 end if;
1356
1357
1358 if l_update = 'Y' then
1359 k := k + 1;
1360 upd_task_tbl.extend(1);
1361 upd_effective_from.extend(1);
1362 upd_effective_to.extend(1);
1363 upd_rlmi_tbl.extend(1);
1364 upd_quantity_tbl.extend(1);
1365 upd_raw_cost_tbl.extend(1);
1366 upd_currency_tbl.extend(1);
1367
1368 upd_task_tbl(k) := rollup_task_tbl(i);
1369 upd_effective_from(k) := rollup_effective_from(i);
1370 upd_effective_to(k) := rollup_effective_to(i);
1371 upd_rlmi_tbl(k) := rollup_rlmi_tbl(i);
1372 upd_quantity_tbl(k) := db_quantity_tbl(i);
1373 upd_raw_cost_tbl(k) := db_raw_cost_tbl(i);
1374 upd_currency_tbl(k) := rollup_currency_tbl(i);
1375 end if;
1376 end loop; -- delete loop
1377
1378 if del_task_tbl.count > 0 then
1379 delete_planning_transaction(p_api_version => l_api_version,
1380 p_init_msg_list => FND_API.G_FALSE,
1381 x_return_status => l_return_status,
1382 x_msg_count => l_msg_count,
1383 x_msg_data => l_msg_data,
1384 p_bvid => p_bvid,
1385 p_project_id => p_project_id,
1386 p_task_tbl => del_task_tbl,
1387 p_currency_tbl => del_currency_tbl,
1388 p_rlmi_tbl => del_rlmi_tbl);
1389
1390 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
1391 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
1392 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
1393 RAISE PA_API.G_EXCEPTION_ERROR;
1394 END IF;
1395
1396 end if; -- del_task_tbl.count > 0
1397
1398 if upd_task_tbl.count > 0 then
1399 update_planning_transaction(p_api_version => l_api_version,
1400 p_init_msg_list => p_init_msg_list,
1401 x_return_status => l_return_status,
1402 x_msg_count => l_msg_count,
1403 x_msg_data => l_msg_data,
1404 p_bvid => p_bvid,
1405 p_project_id => p_project_id,
1406 p_task_id_tbl => upd_task_tbl,
1407 p_effective_from_tbl => upd_effective_from,
1408 p_effective_to_tbl => upd_effective_to,
1409 p_rlmi_id_tbl => upd_rlmi_tbl,
1410 p_quantity_tbl => upd_quantity_tbl,
1411 p_raw_cost_tbl => upd_raw_cost_tbl,
1412 p_currency_code_tbl => upd_currency_tbl
1413 );
1414
1415 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
1416 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
1417 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
1418 RAISE PA_API.G_EXCEPTION_ERROR;
1419 END IF;
1420
1421 end if; -- upd_task_tbl.count > 0
1422 end if; -- if pa_action_type = 'DELETE'
1423
1424 k := 0;
1425 j := 0;
1426 -- for action types of insert/update.
1427 if p_action_type in ('UPDATE', 'INSERT') then
1428 if rollup_task_tbl.count > 0 then
1429 for i in rollup_task_tbl.first..rollup_task_tbl.last loop
1430 if rollup_ra_exists(i) = 'Y' then
1431 upd_task_tbl.extend(1);
1432 upd_effective_from.extend(1);
1433 upd_effective_to.extend(1);
1434 upd_rlmi_tbl.extend(1);
1435 upd_quantity_tbl.extend(1);
1436 upd_raw_cost_tbl.extend(1);
1437 upd_currency_tbl.extend(1);
1438
1439 j := j + 1;
1440 upd_task_tbl(j) := rollup_task_tbl(i);
1441 upd_effective_from(j) := rollup_effective_from(i);
1442 upd_effective_to(j) := rollup_effective_to(i);
1443 upd_rlmi_tbl(j) := rollup_rlmi_tbl(i);
1444 upd_quantity_tbl(j) := db_quantity_tbl(i);
1445 upd_raw_cost_tbl(j) := db_raw_cost_tbl(i);
1446 upd_currency_tbl(j) := rollup_currency_tbl(i);
1447
1448 elsif (rollup_ra_exists(i) is null or rollup_ra_exists(i) = 'N') then
1449 ins_task_tbl.extend(1);
1450 ins_rlmi_tbl.extend(1);
1451 ins_quantity_tbl.extend(1);
1452 ins_raw_cost_tbl.extend(1);
1453 ins_currency_tbl.extend(1);
1454
1455 k := k + 1;
1456 ins_task_tbl(k) := rollup_task_tbl(i);
1457 ins_rlmi_tbl(k) := rollup_rlmi_tbl(i);
1458 ins_quantity_tbl(k) := db_quantity_tbl(i);
1459 ins_raw_cost_tbl(k) := db_raw_cost_tbl(i);
1460 ins_currency_tbl(k) := rollup_currency_tbl(i);
1461 end if;
1462 end loop;
1463
1464 if upd_task_tbl.count > 0 then
1465 update_planning_transaction(p_api_version => l_api_version,
1466 p_init_msg_list => p_init_msg_list,
1467 x_return_status => l_return_status,
1468 x_msg_count => l_msg_count,
1469 x_msg_data => l_msg_data,
1470 p_bvid => p_bvid,
1471 p_project_id => p_project_id,
1472 p_task_id_tbl => upd_task_tbl,
1473 p_effective_from_tbl => upd_effective_from,
1474 p_effective_to_tbl => upd_effective_to,
1475 p_rlmi_id_tbl => upd_rlmi_tbl,
1476 p_quantity_tbl => upd_quantity_tbl,
1477 p_raw_cost_tbl => upd_raw_cost_tbl,
1478 p_currency_code_tbl => upd_currency_tbl
1479 );
1480
1481 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
1482 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
1483 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
1484 RAISE PA_API.G_EXCEPTION_ERROR;
1485 END IF;
1486
1487 end if;
1488
1489 if ins_task_tbl.count > 0 then
1490
1491 insert_planning_transaction(p_api_version => l_api_version,
1492 p_init_msg_list => p_init_msg_list,
1493 x_return_status => l_return_status,
1494 x_msg_count => l_msg_count,
1495 x_msg_data => l_msg_data,
1496 P_BVID => p_bvid,
1497 P_PROJECT_ID => p_project_id,
1498 P_TASK_ID_TBL => ins_task_tbl,
1499 P_RLMI_ID_TBL => ins_rlmi_tbl,
1500 P_CURRENCY_CODE_TBL => ins_currency_tbl,
1501 P_QUANTITY_TBL => ins_quantity_tbl,
1502 P_RAW_COST_TBL => ins_raw_cost_tbl
1503 );
1504
1505 IF (l_return_status = PA_API.G_RET_STS_UNEXP_ERROR) THEN
1506 RAISE PA_API.G_EXCEPTION_UNEXPECTED_ERROR;
1507 ELSIF (l_return_status = PA_API.G_RET_STS_ERROR) THEN
1508 RAISE PA_API.G_EXCEPTION_ERROR;
1509 END IF;
1510 end if;
1511
1512 l_count := 0;
1513
1514 for i in rollup_task_tbl.first..rollup_task_tbl.last loop
1515 if (db_quantity_tbl.exists(i) and
1516 db_quantity_tbl(i) is not null and
1517 db_quantity_tbl(i) <> FND_API.G_MISS_NUM )then
1518 open get_resource_asgn_csr(p_bvid, rollup_task_tbl(i),rollup_rlmi_tbl(i));
1519 fetch get_resource_asgn_csr into l_rate_based_res_curr;
1520 close get_resource_asgn_csr;
1521
1522 l_found := 0;
1523 l_count := 0;
1524
1525 if l_rate_based_res_curr <> rollup_currency_tbl(i) then
1526 for j in p_line_id_tbl.first..p_line_id_tbl.last loop
1527 if p_task_id_tbl(j) = rollup_task_tbl(i) and
1528 p_rlmi_id_tbl(j) = rollup_rlmi_tbl(i) and
1529 p_currency_code_tbl(j) = rollup_currency_tbl(i) and
1530 p_currency_code_tbl(j) <> l_rate_based_res_curr and
1531 p_quantity_tbl(j) is not null then
1532
1533 begin
1534
1535 -- bug 9839069: if a rate based resource record exists with the combo of t/r/c
1536 -- get the rates from there and use them as the default.
1537 begin
1538 i_resource_rate := null;
1539 i_burden_cost_rate := null;
1540
1541 select planning_resource_rate, burden_cost_rate
1542 into i_resource_rate, i_burden_cost_rate
1543 from pa_ci_direct_cost_details
1544 where ci_id = p_ci_id
1545 and task_id = p_task_id_tbl(j)
1546 and resource_list_member_id = p_rlmi_id_tbl(j)
1547 and currency_code = l_rate_based_res_curr
1548 and rownum < 2;
1549 exception
1550 when no_data_found then
1551 null;
1552 end;
1553
1554 insert into pa_ci_direct_cost_details(
1555 dc_line_id
1556 ,ci_id
1557 ,project_id
1558 ,task_id
1559 ,expenditure_type
1560 ,resource_list_member_id
1561 ,unit_of_measure
1562 ,currency_code
1563 ,quantity
1564 ,planning_resource_rate
1565 ,raw_cost
1566 ,burdened_cost
1567 ,burden_cost_rate
1568 ,resource_assignment_id
1569 ,effective_from
1570 ,effective_to
1571 ,change_reason_code
1572 ,change_description
1573 ,created_by
1574 ,creation_date
1575 ,last_update_by
1576 ,last_update_date
1577 ,last_update_login)
1578 select pa_ci_dir_cost_details_s.nextval
1579 ,pc.ci_id
1580 ,pc.project_id
1581 ,pc.task_id
1582 ,pc.expenditure_type
1583 ,pc.resource_list_member_id
1584 ,pc.unit_of_measure
1585 ,prc.txn_currency_code
1586 ,pc.quantity
1587 ,nvl(i_resource_rate, prc.txn_average_raw_cost_rate)
1588 ,nvl(i_resource_rate, prc.txn_average_raw_cost_rate) * pc.quantity
1589 ,nvl(i_burden_cost_rate, prc.txn_average_burden_cost_rate) * pc.quantity
1590 ,nvl(i_burden_cost_rate, prc.txn_average_burden_cost_rate)
1591 ,prc.resource_assignment_id
1592 ,pra.planning_start_date
1593 ,pra.planning_end_date
1594 ,pc.change_reason_code
1595 ,pc.change_description
1596 ,pc.created_by
1597 ,pc.creation_date
1598 ,pc.last_update_by
1599 ,pc.last_update_date
1600 ,pc.last_update_login
1601 from pa_ci_direct_cost_details pc,
1602 pa_resource_assignments pra,
1603 pa_resource_asgn_curr prc
1604 where pc.ci_id = p_ci_id
1605 and pc.dc_line_id = p_line_id_tbl(j)
1606 and pc.resource_assignment_id is null
1607 and pra.task_id = pc.task_id
1608 and pra.resource_list_member_id = pc.resource_list_member_id
1609 and pra.resource_assignment_id = prc.resource_assignment_id
1610 and prc.txn_currency_code = l_rate_based_res_curr
1611 and pra.budget_version_id = p_bvid;
1612
1613 update pa_ci_direct_cost_details a
1614 set quantity = NULL
1615 where a.ci_id = p_ci_id
1616 and dc_line_id = p_line_id_tbl(j);
1617
1618 exception
1619 when dup_val_on_index then
1620 update pa_ci_direct_cost_details a
1621 set (quantity, raw_cost, burdened_cost) =
1622 (select sum(quantity) + a.quantity,
1623 (sum(quantity) + a.quantity) * a.planning_resource_rate,
1624 (sum(quantity) + a.quantity) * a.burden_cost_rate
1625 from pa_ci_direct_cost_details b
1626 where b.ci_id = p_ci_id
1627 and b.dc_line_id = p_line_id_tbl(j))
1628 where a.ci_id = p_ci_id
1629 and currency_code = l_rate_based_res_curr
1630 and task_id = rollup_task_tbl(i)
1631 and resource_list_member_id = rollup_rlmi_tbl(i)
1632 and expenditure_type = (select expenditure_type
1633 from pa_ci_direct_cost_details
1634 where dc_line_id = p_line_id_tbl(j));
1635
1636 update pa_ci_direct_cost_details a
1637 set quantity = NULL
1638 where a.ci_id = p_ci_id
1639 and dc_line_id = p_line_id_tbl(j);
1640
1641 end;
1642
1643 end if;
1644 end loop;
1645 end if;
1646
1647 end if;
1648 end loop;
1649
1650 end if; -- if rollup_task_tbl.count > 0
1651 end if; -- if p_action_type
1652
1653 PA_API.END_ACTIVITY(l_msg_count, l_msg_data);
1654 x_return_status := PA_API.G_RET_STS_SUCCESS;
1655 EXCEPTION
1656 WHEN PA_API.G_EXCEPTION_ERROR THEN
1657 x_return_status := PA_API.HANDLE_EXCEPTIONS
1658 (
1659 l_api_name,
1660 G_PKG_NAME,
1661 'PA_API.G_RET_STS_ERROR',
1662 x_msg_count,
1663 x_msg_data,
1664 '_PUB'
1665 );
1666 WHEN PA_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1667 x_return_status := PA_API.HANDLE_EXCEPTIONS
1668 (
1669 l_api_name,
1670 G_PKG_NAME,
1671 'PA_API.G_RET_STS_UNEXP_ERROR',
1672 x_msg_count,
1673 x_msg_data,
1674 '_PUB'
1675 );
1676 WHEN OTHERS THEN
1677 x_return_status := PA_API.HANDLE_EXCEPTIONS
1678 (
1679 l_api_name,
1680 G_PKG_NAME,
1681 'OTHERS',
1682 x_msg_count,
1683 x_msg_data,
1684 '_PUB'
1685 );
1686 end process_planning_lines;
1687
1688 end pa_process_ci_lines_pkg;