[Home] [Help]
PACKAGE BODY: APPS.PA_CI_SUPPLIER_PKG
Source
4 BEGIN
1 package body PA_CI_SUPPLIER_PKG as
2 -- $Header: PACISIIB.pls 120.24 2010/06/24 16:21:27 racheruv ship $
3 PROCEDURE print_msg(p_msg varchar2) IS
5 --dbms_output.put_line('Log:'||p_msg);
6 --r_debug.r_msg('Log:'||p_msg);
7 PA_DEBUG.g_err_stage := p_msg;
8 PA_DEBUG.write_file('LOG',pa_debug.g_err_stage);
9 null;
10 END print_msg;
11
12 PROCEDURE insert_row (
13 x_rowid IN OUT NOCOPY VARCHAR2
14 ,x_CI_TRANSACTION_ID IN OUT NOCOPY NUMBER
15 ,p_CI_TYPE_ID IN NUMBER
16 ,p_CI_ID IN NUMBER
17 ,p_CI_IMPACT_ID IN NUMBER
21 ,p_ADJUSTED_TRANSACTION_ID IN NUMBER
18 ,p_VENDOR_ID IN NUMBER
19 ,p_PO_HEADER_ID IN NUMBER
20 ,p_PO_LINE_ID IN NUMBER
22 ,p_CURRENCY_CODE IN VARCHAR2
23 ,p_CHANGE_AMOUNT IN NUMBER
24 ,p_CHANGE_TYPE IN VARCHAR2
25 ,p_CHANGE_DESCRIPTION IN VARCHAR2
26 ,p_CREATED_BY IN NUMBER
27 ,p_CREATION_DATE IN DATE
28 ,p_LAST_UPDATED_BY IN NUMBER
29 ,p_LAST_UPDATE_DATE IN DATE
30 ,p_LAST_UPDATE_LOGIN IN NUMBER
31 ,p_Task_Id IN NUMBER
32 ,p_Resource_List_Mem_Id IN NUMBER
33 ,p_From_Date IN varchar2
34 ,p_To_Date IN varchar2
35 ,p_Estimated_Cost IN NUMBER
36 ,p_Quoted_Cost IN NUMBER
37 ,p_Negotiated_Cost IN NUMBER
38 ,p_Burdened_cost IN NUMBER
39 ,p_Revenue IN NUMBER default NULl
40 ,p_revenue_override_rate in number
41 ,p_audit_history_number in number
42 ,p_current_audit_flag in varchar2
43 ,p_Original_supp_trans_id in number
44 ,p_Source_supp_trans_id in number
45 ,p_Sup_ref_no in number default null
46 ,p_version_type in varchar2 default 'ALL'
47 ,p_ci_status IN VARCHAR2
48 -- gboomina modified for supplier cost 12.1.3 requirement - start
49 ,p_expenditure_type in varchar2 default null
50 ,p_expenditure_org_id in number default null
51 ,p_change_reason_code in varchar2 default null
52 ,p_quote_negotiation_reference in varchar2 default null
53 ,p_need_by_date in varchar2 default null
54 -- gboomina modified for supplier cost 12.1.3 requirement - end
55 ,x_return_status OUT NOCOPY VARCHAR2
56 ,x_error_msg_code OUT NOCOPY VARCHAR2
57 )IS
58 cursor return_rowid is
59 select rowid
60 from pa_ci_supplier_details
61 where ci_transaction_id = x_CI_TRANSACTION_ID;
62
63 cursor get_itemid is
64 select pa_ci_supplier_details_s.nextval
65 from sys.dual;
66
67 cursor get_budget_data(bvId number) is
68 select prac.total_projfunc_burdened_cost , prac.total_projfunc_revenue, prac.txn_average_bill_rate
69 from pa_resource_assignments pra, pa_resource_asgn_curr prac
70 where
71 pra.budget_version_id = bvId and pra.task_id = p_task_id and
72 pra.resource_list_member_id = p_Resource_List_Mem_Id and
73 prac.resource_assignment_id = pra.resource_assignment_id;
74
75 cursor get_ciTypeId is
76 select ci_type_id
77 from pa_control_items
78 where ci_id = p_ci_id;
79
80 cursor get_res_asgn_id is
81 select pa_resource_assignments_s.nextval
82 from sys.dual;
83
84 cursor get_burdened_cost(ci_trans_id number) is
85 select final_cost from pa_ci_supplier_details
86 where ci_transaction_id = ci_trans_id;
87
88 cursor get_budget_version_id is
89 select budget_version_id
90 from pa_budget_versions
91 where ci_id = p_ci_id;
92
93 cursor get_budget_cost_version_id(proj_id number) is
94 SELECT budget_version_id FROM pa_budget_versions
95 WHERE ci_id = p_ci_id and
96 budget_version_id in (select fin_plan_version_id from pa_proj_fp_options where project_id = proj_id and fin_plan_preference_code = 'COST_ONLY');
97
98 cursor get_budget_rev_version_id(proj_id number) is
99 SELECT budget_version_id FROM pa_budget_versions
100 WHERE ci_id = p_ci_id and
101 budget_version_id in (select fin_plan_version_id from pa_proj_fp_options where project_id = proj_id and fin_plan_preference_code = 'REVENUE_ONLY');
102
103 cursor get_project_id is
104 select project_id
105 from pa_control_items
106 where ci_id = p_ci_id;
107
108 cursor get_elem_ver_id (proj_id number) is
109 select pev.element_version_id,parent_structure_version_id
110 from pa_proj_element_versions pev,pa_proj_elem_ver_structure pevs
111 where pev.project_id=proj_id and pev.project_id=pevs.project_id
112 and pev.proj_element_id=p_task_id and pev.parent_structure_version_id=pevs.element_version_id
113 and pevs.CURRENT_FLAG='Y';
114
115 cursor get_elem_ver_all_id (proj_id number) is
116 select pev.element_version_id,parent_structure_version_id
117 from pa_proj_element_versions pev,pa_proj_elem_ver_structure pevs
118 where pev.project_id=proj_id and pev.project_id=pevs.project_id
119 and pev.proj_element_id=p_task_id and pev.parent_structure_version_id=pevs.element_version_id
120 and pevs.CURRENT_WORKING_FLAG='Y';
121
122
123
124 cursor get_resource_assignment_id(bv_Id number) is
125 select resource_assignment_id,
126 unit_of_measure,
127 project_assignment_id,
128 organization_id,
129 supplier_id,
130 spread_curve_id,
131 etc_method_code,
132 mfc_cost_type_id,
133 procure_resource_flag,
134 decode(use_task_schedule_flag,'Y','Y','N') as use_task_schedule_flag,
135 planning_start_date,
136 planning_end_date,
137 schedule_start_date,
138 schedule_end_date,
139 sp_fixed_date,
140 named_role
141 from pa_resource_assignments
142 where budget_version_id = bv_Id
146 cursor get_resource_details is
143 and task_id = p_Task_Id
144 and resource_list_member_id = p_Resource_List_Mem_Id;
145
147 select ORGANIZATION_ID
148 ,SPREAD_CURVE_ID
149 ,ETC_METHOD_CODE
150 ,RESOURCE_CLASS_CODE
151 ,RESOURCE_CLASS_FLAG
152 ,RECORD_VERSION_NUMBER
153 ,INCURRED_BY_RES_FLAG
154 ,UNIT_OF_MEASURE
155 ,RESOURCE_TYPE_CODE
156 from pa_resource_list_members where RESOURCE_LIST_MEMBER_ID = p_Resource_List_Mem_Id;
157
158 cursor get_cost_rate_id is
159 SELECT
160 ppfo.use_planning_rates_flag,
161 ppfo.RES_CLASS_RAW_COST_SCH_ID,
162 ppfo.COST_RES_CLASS_RATE_SCH_ID,
163 ppfo.RES_CLASS_BILL_RATE_SCH_ID,
164 ppfo.REV_RES_CLASS_RATE_SCH_ID
165 FROM pa_proj_fp_options ppfo,
166 pa_control_items pci
167 WHERE pci.ci_id = p_ci_id and
168 pci.project_id = ppfo.project_id and
169 NVL(ppfo.Approved_Cost_Plan_Type_Flag ,'N') = 'Y' and
170 ppfo.Fin_Plan_Option_Level_Code = 'PLAN_TYPE';
171
172 cursor get_cost_markup(p_rate_sch_id number) is
173 select rate from PA_BILL_RATES_ALL
174 WHERE bill_rate_sch_id = p_rate_sch_id AND
175 resource_class_code = 'FINANCIAL_ELEMENTS' and
176 trunc(Nvl(to_date(null,'YYYY.MM.DD'),start_date_active)) between trunc(start_date_active)
177 and trunc(nvl(end_date_active,Nvl(to_date(null,'YYYY.MM.DD'),start_date_active)));
178
179 res_info get_resource_details%rowtype;
180 l_audit_history_number number := p_audit_history_number;
181 l_return_status varchar2(100) := 'S';
182 l_error_msg_code varchar2(100) := NULL;
183 l_msg_count number := 0;
184 l_debug_mode varchar2(1) := 'N';
185 l_CHANGE_DESCRIPTION varchar2(100);
186 l_CI_TRANSACTION_ID NUMBER;
187 l_original_supp_id number := p_Original_supp_trans_id;
188 l_source_supp_id number := p_Source_supp_trans_id;
189 l_markup_cost number := 0;
190 l_res_rate_sch_id number :=0;
191 l_use_planning_rates_flag varchar2(1) :='N';
192 l_cost_rate_sch_id number :=0;
193 l_cost_rate number := 0;
194 l_res_bill_sch_id number := 0;
195 l_rev_rate_sch_id number := 0;
196 l_revenue_rate number :=0;
197 l_revenue number :=0;
198 l_burdened_cost number := 0;
199 l_bvId number := 0;
200 l_res_asgn_Id number := 0;
201 l_project_id number := 0;
202 l_CI_TYPE_ID number := 0;
203 l_elem_ver_id number := 0;
204 L_BUR_COST number := 0;
205
206 --defining the table type variables for insert into budget operation
207 l_task_elem_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
208 l_resource_list_member_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
209 l_quantity_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
210 l_currency_code_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
211 l_raw_cost_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
212 l_burdened_cost_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
213 l_revenue_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
214 l_cost_rate_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
215 l_bill_rate_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
216 l_burdened_rate_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
217 l_unplanned_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
218 l_expenditure_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
219
220 --defining the table type variables for update into budget operation
221 l_task_name_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
222 l_task_number_tbl SYSTEM.PA_VARCHAR2_100_TBL_TYPE := SYSTEM.PA_VARCHAR2_100_TBL_TYPE();
223 l_in_start_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
224 l_in_end_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
225 l_planned_people_effort_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
226 l_resource_assignment_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
227 l_assignment_description_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
228 l_project_assignment_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
229 l_planning_resource_alias_tbl SYSTEM.PA_VARCHAR2_80_TBL_TYPE := SYSTEM.PA_VARCHAR2_80_TBL_TYPE();
230 l_resource_class_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
231 l_resource_class_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
232 l_resource_class_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
233 l_res_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
234 l_resource_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
235 l_resource_name SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
236 l_project_role_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
237 l_project_role_name_tbl SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
238 l_organization_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
239 l_organization_name_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
243 l_unit_of_measure_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
240 l_fc_res_type_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
241 l_financial_category_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
242 l_supplier_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
244 l_spread_curve_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
245 l_etc_method_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
246 l_mfc_cost_type_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
247 l_procure_resource_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
248 l_incurred_by_res_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
249 l_incur_by_resource_name_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
250 l_Incur_by_resource_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
251 l_use_task_schedule_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
252 l_planning_start_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
253 l_planning_end_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
254 l_schedule_start_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
255 l_schedule_end_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
256 l_total_quantity_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
257 l_override_currency_code_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
258 l_total_raw_cost_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
259 l_raw_cost_rate_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
260 l_bill_rate_override_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
261 l_billable_percent_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
262 l_cost_rate_override_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
263 l_burdened_rate_override_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
264 l_sp_fixed_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
265 l_named_role_tbl SYSTEM.PA_VARCHAR2_80_TBL_TYPE := SYSTEM.PA_VARCHAR2_80_TBL_TYPE();
266 l_financial_category_name_tbl SYSTEM.PA_VARCHAR2_80_TBL_TYPE := SYSTEM.PA_VARCHAR2_80_TBL_TYPE();
267 l_supplier_name_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
268 l_attribute_category_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
269 l_attribute1_tbl SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
270 l_scheduled_delay SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
271 l_direct_expenditure_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
272 l_struct_elem_version_id Pa_proj_element_versions.element_version_id%TYPE := -1;
273
274
275
276 BEGIN
277 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
278 l_debug_mode := NVL(l_debug_mode, 'N');
279 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
280
281 IF l_debug_mode = 'Y' THEN
282 print_msg('Inside pa_ci_supplier_pkg table handler..');
283 End IF;
284
285 open get_itemid;
286 fetch get_itemid into x_ci_transaction_id;
287 close get_itemid;
288
289 if (p_CI_TYPE_ID is null) then
290 open get_ciTypeId;
291 fetch get_ciTypeId into l_CI_TYPE_ID;
292 close get_ciTypeId;
293 else
294 l_CI_TYPE_ID := p_CI_TYPE_ID;
295 end if;
296
297
298 IF l_debug_mode = 'Y' THEN
299 print_msg('Info transacton id ..'||x_ci_transaction_id);
300 End IF;
301 l_CHANGE_DESCRIPTION := '';
302
303 if (p_Original_supp_trans_id is null OR p_Original_supp_trans_id = 0) THEN
304 l_original_supp_id := x_ci_transaction_id;
305 end if;
306
307 if (p_Source_supp_trans_id is null OR p_Source_supp_trans_id = 0) THEN
308 l_source_supp_id := x_ci_transaction_id;
309 end if;
310
311 if (p_audit_history_number is null OR p_audit_history_number = -99) THEN
312 l_audit_history_number := 1;
313 end if;
314
315 open get_cost_rate_id;
316 fetch get_cost_rate_id into l_use_planning_rates_flag, l_res_rate_sch_id,l_cost_rate_sch_id,l_res_bill_sch_id,l_rev_rate_sch_id;
317 close get_cost_rate_id;
318
319 if l_use_planning_rates_flag = 'Y' THEN
320 open get_cost_markup(l_cost_rate_sch_id);
321 fetch get_cost_markup into l_cost_rate;
322 close get_cost_markup;
323 open get_cost_markup(l_res_bill_sch_id);
324 fetch get_cost_markup into l_revenue_rate;
325 close get_cost_markup;
326 else
327 open get_cost_markup(l_res_rate_sch_id);
328 fetch get_cost_markup into l_cost_rate;
329 close get_cost_markup;
330 open get_cost_markup(l_rev_rate_sch_id);
331 fetch get_cost_markup into l_revenue_rate;
332 close get_cost_markup;
333 end if;
334
335 if p_revenue_override_rate is not null then
336 if p_CHANGE_AMOUNT is not null then
337 l_revenue := (p_CHANGE_AMOUNT*l_cost_rate)*p_revenue_override_rate;
338 end if;
339 else
340 if p_CHANGE_AMOUNT is not null then
341 l_revenue := (p_CHANGE_AMOUNT*l_cost_rate)*l_revenue_rate;
342 end if;
343 end if;
344
345 INSERT into pa_ci_supplier_details
349 ,CI_IMPACT_ID
346 ( CI_TRANSACTION_ID
347 ,CI_TYPE_ID
348 ,CI_ID
350 ,VENDOR_ID
351 ,PO_HEADER_ID
352 ,PO_LINE_ID
353 ,ADJUSTED_CI_TRANSACTION_ID
354 ,CURRENCY_CODE
355 ,CHANGE_AMOUNT
356 ,CHANGE_TYPE
357 ,CHANGE_DESCRIPTION
358 ,CREATED_BY
359 ,CREATION_DATE
360 ,LAST_UPDATED_BY
361 ,LAST_UPDATE_DATE
362 ,LAST_UPDATE_LOGIN
363 ,TASK_ID
364 ,RESOURCE_LIST_MEMBER_ID
365 ,FROM_CHANGE_DATE
366 ,TO_CHANGE_DATE
367 ,ESTIMATED_COST
368 ,QUOTED_COST
369 ,NEGOTIATED_COST
370 ,FINAL_COST
371 ,RAW_COST
372 ,BURDENED_COST
373 ,revenue_rate
374 ,revenue_override_rate
375 ,revenue
376 ,total_revenue
377 ,CURRENT_AUDIT_FLAG
378 ,STATUS
379 ,audit_history_number
380 ,original_supp_trans_id
381 ,source_supp_trans_id
382 ,sup_quote_ref_no
383 -- gboomina modified for supplier cost 12.1.3 requirement - start
384 ,expenditure_type
385 ,expenditure_org_id
386 ,change_reason_code
387 ,quote_negotiation_reference
388 ,need_by_date
389 -- gboomina modified for supplier cost 12.1.3 requirement - end
390 ) VALUES
391 ( x_CI_TRANSACTION_ID
392 ,l_CI_TYPE_ID
393 ,p_CI_ID
394 ,p_CI_IMPACT_ID
395 ,p_VENDOR_ID
396 ,p_PO_HEADER_ID
397 ,p_PO_LINE_ID
398 ,p_ADJUSTED_TRANSACTION_ID
399 ,p_CURRENCY_CODE
400 ,pa_currency.round_trans_currency_amt
401 (decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT),p_CURRENCY_CODE)
402 ,p_CHANGE_TYPE
403 ,NVL(p_CHANGE_DESCRIPTION,l_CHANGE_DESCRIPTION)
404 ,p_CREATED_BY
405 ,p_CREATION_DATE
406 ,p_LAST_UPDATE_LOGIN
407 ,p_LAST_UPDATE_DATE
408 ,p_LAST_UPDATE_LOGIN
409 ,p_Task_Id
410 ,p_Resource_List_Mem_Id
411 ,to_date(p_From_Date)
412 ,to_date(p_To_Date)
413 ,pa_currency.round_trans_currency_amt
414 (decode(p_Estimated_Cost,null,0,p_Estimated_Cost),p_CURRENCY_CODE)
415 ,pa_currency.round_trans_currency_amt
416 (decode(p_Quoted_Cost,null,0,p_Quoted_Cost),p_CURRENCY_CODE)
417 ,pa_currency.round_trans_currency_amt
418 (decode(p_Negotiated_Cost,null,0,p_Negotiated_Cost),p_CURRENCY_CODE)
419 ,pa_currency.round_trans_currency_amt
420 (decode(p_Burdened_cost,null,(p_CHANGE_AMOUNT+(p_CHANGE_AMOUNT*0.1)),p_Burdened_cost),p_CURRENCY_CODE)
421 ,pa_currency.round_trans_currency_amt
422 (decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT),p_CURRENCY_CODE)
423 --,pa_currency.round_trans_currency_amt
424 -- (decode(p_Burdened_cost,null,(p_CHANGE_AMOUNT+(p_CHANGE_AMOUNT*0.1)),p_Burdened_cost),p_CURRENCY_CODE)
425 ,pa_currency.round_trans_currency_amt(p_Burdened_cost,p_CURRENCY_CODE)
426 ,l_revenue_rate
427 ,p_revenue_override_rate
428 ,pa_currency.round_trans_currency_amt
429 (decode(l_revenue,null,0,l_revenue),p_CURRENCY_CODE)
430 ,pa_currency.round_trans_currency_amt
431 (decode(l_revenue,null,0,l_revenue),p_CURRENCY_CODE)
432 ,'Y'
433 ,p_version_type
434 ,l_audit_history_number
435 ,l_original_supp_id
436 ,l_source_supp_id
437 ,p_Sup_ref_no
438 -- gboomina modified for supplier cost 12.1.3 requirement - start
439 ,p_expenditure_type
440 ,p_expenditure_org_id
441 ,p_change_reason_code
442 ,p_quote_negotiation_reference
443 ,to_date(p_need_by_date)
444 -- gboomina modified for supplier cost 12.1.3 requirement - end
445 );
446 IF (l_audit_history_number > 1) then
447 update pa_ci_supplier_details set CURRENT_AUDIT_FLAG = 'N' where ci_transaction_id = l_source_supp_id;
448 end if;
449 /*
450 OPEN return_rowid;
451 FETCH return_rowid into x_rowid;
452 IF (return_rowid%notfound) then
453 l_return_status := 'E';
454 l_error_msg_code := 'NO_DATA_FOUND';
455 IF l_debug_mode = 'Y' THEN
456 print_msg('rowid not found raise insert failed');
457 End If;
458 raise NO_DATA_FOUND; -- should we return something else?
459 Else
460 IF l_debug_mode = 'Y' THEN
461 print_msg('Insert success');
462 End If;
463 l_return_status := 'S';
464 l_error_msg_code := NULL;
465 End if;
466 CLOSE return_rowid;
467
468 IF (p_task_id is not null AND p_Resource_List_Mem_Id is not null) then
469 open get_project_id;
470 fetch get_project_id into l_project_id;
471 close get_project_id;
472 --checking for budget version id
473 if (p_version_type = 'ALL') then
474 OPEN get_budget_version_id;
475 FETCH get_budget_version_id INTO l_bvId;
476 If get_budget_version_id%NOTFOUND then
477 If l_debug_mode = 'Y' Then
478 print_msg('row not found return');
479 End If;
480 return;
481 End If;
482 CLOSE get_budget_version_id;
483 elsif (p_version_type = 'COST') then
484 OPEN get_budget_cost_version_id(l_project_id);
485 FETCH get_budget_cost_version_id INTO l_bvId;
486 If get_budget_cost_version_id%NOTFOUND then
487 If l_debug_mode = 'Y' Then
488 print_msg('row not found return');
492 CLOSE get_budget_cost_version_id;
489 End If;
490 return;
491 End If;
493 else
494 OPEN get_budget_rev_version_id(l_project_id);
495 FETCH get_budget_rev_version_id INTO l_bvId;
496 If get_budget_rev_version_id%NOTFOUND then
497 If l_debug_mode = 'Y' Then
498 print_msg('row not found return');
499 End If;
500 return;
501 End If;
502 CLOSE get_budget_rev_version_id;
503 end if;
504
505 --budget version id not null
506 IF l_bvId is not null then
507 IF (l_audit_history_number = 1) then
508 --task_element_version_id
509
510 l_task_elem_version_id_tbl.extend(1);
511 if (p_version_type = 'ALL') then
512 open get_elem_ver_all_id(l_project_id);
513 fetch get_elem_ver_all_id into l_task_elem_version_id_tbl(1), l_struct_elem_version_id;
514 close get_elem_ver_all_id;
515 else
516 open get_elem_ver_id(l_project_id);
517 fetch get_elem_ver_id into l_task_elem_version_id_tbl(1), l_struct_elem_version_id;
518 close get_elem_ver_id;
519 end if;
520 --l_task_elem_version_id_tbl(1) := p_task_id;
521 --resource list member id
522 l_resource_list_member_id_tbl.extend(1);
523 l_resource_list_member_id_tbl(1) := p_Resource_List_Mem_Id;
524
525 --quantity
526 l_quantity_tbl.extend(1);
527 l_quantity_tbl(1) := NULL;
528
529 --currency code
530 l_currency_code_tbl.extend(1);
531 l_currency_code_tbl(1) := p_CURRENCY_CODE;
532
533 --burdened cost
534 l_burdened_cost_tbl.extend(1);
535 l_burdened_cost_tbl(1) := p_Burdened_cost;
536
537 --raw cost
538 l_raw_cost_tbl.extend(1);
539 l_raw_cost_tbl(1) := NVL(p_CHANGE_AMOUNT,0);
540
541 --revenue
542 l_revenue_tbl.extend(1);
543 l_revenue_tbl(1) := p_Revenue;
544
545 --cost rate
546 l_cost_rate_tbl.extend(1);
547 l_cost_rate_tbl(1) := NULL;
548
549 --bill rate
550 l_bill_rate_tbl.extend(1);
551 l_bill_rate_tbl(1) := p_revenue_override_rate;
552
553 --burdened rate
554 l_burdened_rate_tbl.extend(1);
555 l_burdened_rate_tbl(1) := NULL;
556
557 --unplanned flag
558 l_unplanned_flag_tbl.extend(1);
559 l_unplanned_flag_tbl(1) := NULL;
560
561 --expenditure type
562 l_expenditure_type_tbl.extend(1);
563 l_expenditure_type_tbl(1) := '';
564
565 --call pa_planning_element_utils.add_new_resource_assignments to add the resource assignment
566 pa_planning_element_utils.add_new_resource_assignments(
567 p_context => 'BUDGET',
568 p_project_id => l_project_id,
569 p_budget_version_id => l_bvId,
570 p_task_elem_version_id_tbl => l_task_elem_version_id_tbl,
571 p_resource_list_member_id_tbl => l_resource_list_member_id_tbl,
572 p_quantity_tbl => l_quantity_tbl,
573 p_currency_code_tbl => l_currency_code_tbl,
574 p_burdened_cost_tbl => l_burdened_cost_tbl,
575 p_raw_cost_tbl => l_raw_cost_tbl,
576 p_revenue_tbl => l_revenue_tbl,
577 p_cost_rate_tbl => l_cost_rate_tbl,
578 p_bill_rate_tbl => l_bill_rate_tbl,
579 p_burdened_rate_tbl => l_burdened_rate_tbl,
580 p_unplanned_flag_tbl => l_unplanned_flag_tbl,
581 p_expenditure_type_tbl => l_expenditure_type_tbl,
582 x_return_status => l_return_status,
583 x_msg_count => l_error_msg_code,
584 x_msg_data => l_msg_count);
585
586 If l_return_status = 'S' then
587 --fetch burdened_cost, revenue_rate, revenue from budget tables
588 open get_budget_data(l_bvId);
589 fetch get_budget_data into l_burdened_cost, l_revenue, l_revenue_rate;
590 close get_budget_data;
591 --updating the pa_ci_supplier_details table information
592
593 update pa_ci_supplier_details
594 set burdened_cost = l_burdened_cost,
595 revenue_rate = l_revenue_rate,
596 revenue_override_rate = '',
597 revenue = l_revenue,
598 total_revenue = l_revenue
599 where
600 ci_transaction_id = x_CI_TRANSACTION_ID;
601
602 End if;
603 else -- audit number is not 1
604 --call update api
605 --p_struct_elem_version_id and p_task_elem_version_id_tbl
606 open get_elem_ver_id(l_project_id);
607 fetch get_elem_ver_id into l_elem_ver_id, l_struct_elem_version_id;
608 close get_elem_ver_id;
609 l_task_elem_version_id_tbl.extend(1);
610 l_task_elem_version_id_tbl(1) := l_elem_ver_id;
611 --p_task_name_tbl
612 l_task_name_tbl.extend(1);
613 l_task_name_tbl(1) := '';
614 --p_task_number_tbl
615 l_task_number_tbl.extend(1);
616 l_task_number_tbl(1) := '';
617 --p_start_date_tbl
618 l_in_start_date_tbl.extend(1);
619 l_in_start_date_tbl(1) := to_date(p_From_Date,'YYYY/MM/DD');
620 --p_end_date_tbl
621 l_in_end_date_tbl.extend(1);
622 l_in_end_date_tbl(1) := to_date(p_To_Date,'YYYY/MM/DD');
623 --p_planned_people_effort_tbl
624 l_planned_people_effort_tbl.extend(1);
628 --p_project_assignment_id_tbl
625 l_planned_people_effort_tbl(1) := '';
626 --p_resource_assignment_id_tbl
627 --p_unit_of_measure_tbl
629 --p_supplier_id_tbl
630 --p_spread_curve_id_tbl
631 --p_etc_method_code_tbl
632 --p_mfc_cost_type_id_tbl
633
634 --p_use_task_schedule_flag_tbl
635 --p_planning_start_date_tbl
636 --p_planning_end_date_tbl
637
638 --p_sp_fixed_date_tbl
639 l_resource_assignment_id_tbl.extend(1);
640 l_project_assignment_id_tbl.extend(1);
641 l_unit_of_measure_tbl.extend(1);
642 l_organization_id_tbl.extend(1);
643 l_supplier_id_tbl.extend(1);
644 l_spread_curve_id_tbl.extend(1);
645 l_etc_method_code_tbl.extend(1);
646 l_mfc_cost_type_id_tbl.extend(1);
647 l_procure_resource_flag_tbl.extend(1);
648 l_use_task_schedule_flag_tbl.extend(1);
649 l_planning_start_date_tbl.extend(1);
650 l_planning_end_date_tbl.extend(1);
651 l_schedule_start_date_tbl.extend(1);
652 l_schedule_end_date_tbl.extend(1);
653 l_sp_fixed_date_tbl.extend(1);
654 l_named_role_tbl.extend(1);
655 open get_resource_assignment_id(l_bvId);
656 fetch get_resource_assignment_id into
657 l_resource_assignment_id_tbl(1),l_unit_of_measure_tbl(1),l_project_assignment_id_tbl(1),
658 l_organization_id_tbl(1),l_supplier_id_tbl(1),l_spread_curve_id_tbl(1),
659 l_etc_method_code_tbl(1),l_mfc_cost_type_id_tbl(1),l_procure_resource_flag_tbl(1),
660 l_use_task_schedule_flag_tbl(1),l_planning_start_date_tbl(1),l_planning_end_date_tbl(1),
661 l_schedule_start_date_tbl(1),l_schedule_end_date_tbl(1),l_sp_fixed_date_tbl(1),
662 l_named_role_tbl(1);
663 close get_resource_assignment_id;
664 --p_resource_list_member_id_tbl
665 l_resource_list_member_id_tbl.extend(1);
666 l_resource_list_member_id_tbl(1) := p_Resource_List_Mem_Id;
667 --p_assignment_description_tbl
668 l_assignment_description_tbl.extend(1);
669 l_assignment_description_tbl(1) := '';
670 --p_resource_alias_tbl
671 l_planning_resource_alias_tbl.extend(1);
672 l_planning_resource_alias_tbl(1) := '';
673 --p_resource_class_flag_tbl
674 l_resource_class_flag_tbl.extend(1);
675 l_resource_class_flag_tbl(1) := '';
676 --p_resource_class_code_tbl
677 l_resource_class_code_tbl.extend(1);
678 l_resource_class_code_tbl(1) := '';
679 --p_resource_class_id_tbl
680 l_resource_class_id_tbl.extend(1);
681 l_resource_class_id_tbl(1) := '';
682 --p_res_type_code_tbl
683 l_res_type_code_tbl.extend(1);
684 l_res_type_code_tbl(1) := '';
685 --p_resource_code_tbl
686 l_resource_code_tbl.extend(1);
687 l_resource_code_tbl(1) := '';
688 --p_resource_name
689 l_resource_name.extend(1);
690 l_resource_name(1) := '';
691 --p_project_role_id_tbl
692 l_project_role_id_tbl.extend(1);
693 l_project_role_id_tbl(1) := '';
694 --p_project_role_name_tbl
695 l_project_role_name_tbl.extend(1);
696 l_project_role_name_tbl(1) := '';
697 --p_organization_name_tbl
698 l_organization_name_tbl.extend(1);
699 l_organization_name_tbl(1) := '';
700 --p_fc_res_type_code_tbl
701 l_fc_res_type_code_tbl.extend(1);
702 l_fc_res_type_code_tbl(1) := '';
703 --p_financial_category_code_tbl
704 l_financial_category_code_tbl.extend(1);
705 l_financial_category_code_tbl(1) := '';
706 --p_incur_by_resource_code_tbl =>
707 l_Incur_by_resource_code_tbl.extend(1);
708 l_Incur_by_resource_code_tbl(1) := '';
709 --p_incur_by_resource_name_tbl
710 l_incur_by_resource_name_tbl.extend(1);
711 l_incur_by_resource_name_tbl(1) := '';
712
713 --p_quantity_tbl
714 l_total_quantity_tbl.extend(1);
715 l_total_quantity_tbl(1) := NULL;
716 --p_currency_code_tbl
717 l_currency_code_tbl.extend(1);
718 l_currency_code_tbl(1) := p_CURRENCY_CODE;
719 --p_txn_currency_override_tbl
720 l_override_currency_code_tbl.extend(1);
721 l_override_currency_code_tbl(1) := '';
722 --p_raw_cost_tbl
723 l_total_raw_cost_tbl.extend(1);
724 l_total_raw_cost_tbl(1) := p_CHANGE_AMOUNT;
725 --p_burdened_cost_tbl
726 l_burdened_cost_tbl.extend(1);
727 open get_burdened_cost(x_CI_TRANSACTION_ID);
728 fetch get_burdened_cost into l_bur_cost;
729 close get_burdened_cost;
730 if l_bur_cost = p_Burdened_cost then
731 l_burdened_cost_tbl(1) := NULL;
732 else
733 l_burdened_cost_tbl(1) := p_Burdened_cost;
734 end if;
735 --p_revenue_tbl
736 l_revenue_tbl.extend(1);
737 l_revenue_tbl(1) := p_Revenue;
738 --p_cost_rate_tbl
739 l_raw_cost_rate_tbl.extend(1);
740 l_raw_cost_rate_tbl(1) := NULL;
741 --p_bill_rate_tbl
742 l_bill_rate_tbl.extend(1);
743 l_bill_rate_tbl(1) := NULL;
744 --p_bill_rate_override_tbl
745 l_bill_rate_override_tbl.extend(1);
746 l_bill_rate_override_tbl(1) := p_revenue_override_rate;
747 --p_billable_percent_tbl
748 l_billable_percent_tbl.extend(1);
749 l_billable_percent_tbl(1) := p_revenue_override_rate;
750 --p_cost_rate_override_tbl
751 l_cost_rate_override_tbl.extend(1);
752 l_cost_rate_override_tbl(1) := NULL;
753 --p_burdened_rate_tbl
754 l_burdened_rate_tbl.extend(1);
755 l_burdened_rate_tbl(1) := NULL;
756 --p_burdened_rate_override_tbl
760 --p_financial_category_name_tbl
757 l_burdened_rate_override_tbl.extend(1);
758 l_burdened_rate_override_tbl(1) := '';
759
761 l_financial_category_name_tbl.extend(1);
762 l_financial_category_name_tbl(1) := '';
763 --p_supplier_name_tbl
764 l_supplier_name_tbl.extend(1);
765 l_supplier_name_tbl(1) := '';
766 --p_attribute_category_tbl 1 --> 30
767 l_attribute_category_tbl.extend(1);
768 l_attribute1_tbl.extend(1);
769 l_attribute_category_tbl(1) := '';
770 l_attribute1_tbl(1) := '';
771 --p_scheduled_delay
772 l_scheduled_delay := null;
773 --p_direct_expenditure_type_tbl
774 l_direct_expenditure_type_tbl.extend(1);
775 l_direct_expenditure_type_tbl(1) := '';
776 --calling api
777
778 pa_fp_planning_transaction_pub.update_planning_transactions(
779 p_context => 'BUDGET',
780 p_struct_elem_version_id => l_struct_elem_version_id,
781 p_budget_version_id => l_bvId,
782 p_task_elem_version_id_tbl => l_task_elem_version_id_tbl,
783 p_task_name_tbl => l_task_name_tbl,
784 p_task_number_tbl => l_task_number_tbl,
785 p_start_date_tbl => l_in_start_date_tbl,
786 p_end_date_tbl => l_in_end_date_tbl,
787 p_planned_people_effort_tbl => l_planned_people_effort_tbl,
788 p_resource_assignment_id_tbl => l_resource_assignment_id_tbl,
789 p_resource_list_member_id_tbl => l_resource_list_member_id_tbl,
790 p_assignment_description_tbl => l_assignment_description_tbl,
791 p_project_assignment_id_tbl => l_project_assignment_id_tbl,
792 p_resource_alias_tbl => l_planning_resource_alias_tbl,
793 p_resource_class_flag_tbl => l_resource_class_flag_tbl,
794 p_resource_class_code_tbl => l_resource_class_code_tbl,
795 p_resource_class_id_tbl => l_resource_class_id_tbl,
796 p_res_type_code_tbl => l_res_type_code_tbl,
797 p_resource_code_tbl => l_resource_code_tbl,
798 p_resource_name => l_resource_name,
799 p_project_role_id_tbl => l_project_role_id_tbl,
800 p_project_role_name_tbl => l_project_role_name_tbl,
801 p_organization_id_tbl => l_organization_id_tbl,
802 p_organization_name_tbl => l_organization_name_tbl,
803 p_fc_res_type_code_tbl => l_fc_res_type_code_tbl,
804 p_financial_category_code_tbl => l_financial_category_code_tbl,
805 p_supplier_id_tbl => l_supplier_id_tbl,
806 p_unit_of_measure_tbl => l_unit_of_measure_tbl,
807 p_spread_curve_id_tbl => l_spread_curve_id_tbl,
808 p_etc_method_code_tbl => l_etc_method_code_tbl,
809 p_mfc_cost_type_id_tbl => l_mfc_cost_type_id_tbl,
810 p_procure_resource_flag_tbl => l_procure_resource_flag_tbl,
811 p_incur_by_resource_code_tbl => l_Incur_by_resource_code_tbl,
812 p_incur_by_resource_name_tbl => l_incur_by_resource_name_tbl,
813 p_use_task_schedule_flag_tbl => l_use_task_schedule_flag_tbl,
814 p_planning_start_date_tbl => l_planning_start_date_tbl,
815 p_planning_end_date_tbl => l_planning_end_date_tbl,
816 p_schedule_start_date_tbl => l_schedule_start_date_tbl,
817 p_schedule_end_date_tbl => l_schedule_end_date_tbl,
818 p_quantity_tbl => l_total_quantity_tbl,
819 p_currency_code_tbl => l_currency_code_tbl,
820 p_txn_currency_override_tbl => l_override_currency_code_tbl,
821 p_raw_cost_tbl => l_total_raw_cost_tbl,
822 p_burdened_cost_tbl => l_burdened_cost_tbl,
823 p_revenue_tbl => l_revenue_tbl,
824 p_cost_rate_tbl => l_raw_cost_rate_tbl,
825 p_bill_rate_tbl => l_bill_rate_tbl,
826 p_bill_rate_override_tbl => l_bill_rate_override_tbl,
827 p_billable_percent_tbl => l_billable_percent_tbl,
828 p_cost_rate_override_tbl => l_cost_rate_override_tbl,
829 p_burdened_rate_tbl => l_burdened_rate_tbl,
830 p_burdened_rate_override_tbl => l_burdened_rate_override_tbl,
831 p_sp_fixed_date_tbl => l_sp_fixed_date_tbl,
832 p_named_role_tbl => l_named_role_tbl,
833 p_financial_category_name_tbl => l_financial_category_name_tbl,
834 p_supplier_name_tbl => l_supplier_name_tbl,
835 p_attribute_category_tbl => l_attribute_category_tbl,
836 p_attribute1_tbl => l_attribute1_tbl,
837 p_attribute2_tbl => l_attribute1_tbl,
838 p_attribute3_tbl => l_attribute1_tbl,
839 p_attribute4_tbl => l_attribute1_tbl,
840 p_attribute5_tbl => l_attribute1_tbl,
841 p_attribute6_tbl => l_attribute1_tbl,
842 p_attribute7_tbl => l_attribute1_tbl,
843 p_attribute8_tbl => l_attribute1_tbl,
844 p_attribute9_tbl => l_attribute1_tbl,
845 p_attribute10_tbl => l_attribute1_tbl,
846 p_attribute11_tbl => l_attribute1_tbl,
850 p_attribute15_tbl => l_attribute1_tbl,
847 p_attribute12_tbl => l_attribute1_tbl,
848 p_attribute13_tbl => l_attribute1_tbl,
849 p_attribute14_tbl => l_attribute1_tbl,
851 p_attribute16_tbl => l_attribute1_tbl,
852 p_attribute17_tbl => l_attribute1_tbl,
853 p_attribute18_tbl => l_attribute1_tbl,
854 p_attribute19_tbl => l_attribute1_tbl,
855 p_attribute20_tbl => l_attribute1_tbl,
856 p_attribute21_tbl => l_attribute1_tbl,
857 p_attribute22_tbl => l_attribute1_tbl,
858 p_attribute23_tbl => l_attribute1_tbl,
859 p_attribute24_tbl => l_attribute1_tbl,
860 p_attribute25_tbl => l_attribute1_tbl,
861 p_attribute26_tbl => l_attribute1_tbl,
862 p_attribute27_tbl => l_attribute1_tbl,
863 p_attribute28_tbl => l_attribute1_tbl,
864 p_attribute29_tbl => l_attribute1_tbl,
865 p_attribute30_tbl => l_attribute1_tbl,
866 p_scheduled_delay => l_scheduled_delay,
867 p_distrib_amts => 'Y',
868 p_direct_expenditure_type_tbl => l_direct_expenditure_type_tbl,
869 x_return_status => l_return_status,
870 x_msg_count => l_error_msg_code,
871 x_msg_data => l_msg_count);
872
873 If l_return_status = 'S' then
874 --fetch burdened_cost, revenue_rate, revenue from budget tables
875
876 open get_budget_data(l_bvId);
877 fetch get_budget_data into l_burdened_cost, l_revenue, l_revenue_rate;
878 close get_budget_data;
879 --updating the pa_ci_supplier_details table information
880
881 update pa_ci_supplier_details
882 set change_amount = p_CHANGE_AMOUNT,
883 burdened_cost = l_burdened_cost,
884 revenue_rate = l_revenue_rate,
885 revenue_override_rate = '',
886 revenue = l_revenue,
887 total_revenue = l_revenue
888 where
889 ci_transaction_id = x_CI_TRANSACTION_ID;
890
891 End if;
892
893
894 end if; --audit number is 1
895 end if; --budget version id not null
896 end if; -- task_id not null
897 */
898
899
900 x_return_status := l_return_status;
901 x_error_msg_code := l_error_msg_code;
902 EXCEPTION
903 when others then
904 x_error_msg_code := sqlcode||sqlerrm;
905 IF l_debug_mode = 'Y' THEN
906 print_msg('x_err_msg_code exception:'||x_error_msg_code);
907 End If;
908 Raise;
909
910 END insert_row;
911
912 PROCEDURE update_row
913 (p_rowid IN VARCHAR2
914 ,p_ci_transaction_id IN NUMBER
915 ,p_CI_TYPE_ID IN NUMBER
916 ,p_CI_ID IN NUMBER
917 ,p_CI_IMPACT_ID IN NUMBER
918 ,p_VENDOR_ID IN NUMBER
919 ,p_PO_HEADER_ID IN NUMBER
920 ,p_PO_LINE_ID IN NUMBER
921 ,p_ADJUSTED_TRANSACTION_ID IN NUMBER
922 ,p_CURRENCY_CODE IN VARCHAR2
923 ,p_CHANGE_AMOUNT IN NUMBER
924 ,p_CHANGE_TYPE IN VARCHAR2
925 ,p_CHANGE_DESCRIPTION IN VARCHAR2
926 ,p_LAST_UPDATED_BY IN NUMBER
927 ,p_LAST_UPDATE_DATE IN DATE
928 ,p_LAST_UPDATE_LOGIN IN NUMBER
929 ,p_Task_Id IN NUMBER
930 ,p_Resource_List_Mem_Id IN NUMBER
931 ,p_From_Date IN varchar2
932 ,p_To_Date IN varchar2
933 ,p_Estimated_Cost IN NUMBER
934 ,p_Quoted_Cost IN NUMBER
935 ,p_Negotiated_Cost IN NUMBER
936 ,p_Burdened_cost IN NUMBER
937 ,p_Revenue IN NUMBER default NULL
938 ,p_revenue_override_rate in number
939 ,p_audit_history_number in number
940 ,p_current_audit_flag in varchar2
941 ,p_Original_supp_trans_id in number
942 ,p_Source_supp_trans_id in number
943 ,p_Sup_ref_no in number default null
944 ,p_version_type in varchar2 default 'ALL'
945 ,p_ci_status IN VARCHAR2 default null
946 -- gboomina modified for supplier cost 12.1.3 requirement - start
947 ,p_expenditure_type in varchar2 default null
948 ,p_expenditure_org_id in number default null
949 ,p_change_reason_code in varchar2 default null
950 ,p_quote_negotiation_reference in varchar2 default null
951 ,p_need_by_date in varchar2 default null
952 -- gboomina modified for supplier cost 12.1.3 requirement - end
953 ,x_return_status OUT NOCOPY VARCHAR2
954 ,x_error_msg_code OUT NOCOPY VARCHAR2
955 )IS
956 cursor cur_audit is
957 select nvl(audit_history_number,0),original_supp_trans_id
958 from pa_ci_supplier_details
959 where ci_transaction_id = p_ci_transaction_id;
960
961 cursor get_res_asgn_id is
962 select pa_resource_assignments_s.nextval
963 from sys.dual;
964
968 ,CI_IMPACT_ID
965 CURSOR cur_row is
966 SELECT CI_TYPE_ID
967 ,CI_ID
969 ,VENDOR_ID
970 ,PO_HEADER_ID
971 ,PO_LINE_ID
972 ,ADJUSTED_CI_TRANSACTION_ID
973 ,CURRENCY_CODE
974 ,CHANGE_AMOUNT
975 ,CHANGE_TYPE
976 ,CHANGE_DESCRIPTION
977 ,TASK_ID
978 ,RESOURCE_LIST_MEMBER_ID
979 ,FROM_CHANGE_DATE
980 ,TO_CHANGE_DATE
981 ,ESTIMATED_COST
982 ,QUOTED_COST
983 ,NEGOTIATED_COST
984 ,Burdened_cost
985 ,revenue
986 ,audit_history_number
987 ,ORIGINAL_SUPP_TRANS_ID
988 ,SOURCE_SUPP_TRANS_ID
989 ,SUP_QUOTE_REF_NO
990 ,status
991 -- gboomina modified for supplier cost 12.1.3 requirement - start
992 ,expenditure_type
993 ,expenditure_org_id
994 ,change_reason_code
995 ,quote_negotiation_reference
996 ,need_by_date
997 -- gboomina modified for supplier cost 12.1.3 requirement - end
998 FROM pa_ci_supplier_details
999 WHERE ci_transaction_id = p_ci_transaction_id
1000 FOR UPDATE OF ci_transaction_id NOWAIT;
1001
1002 cursor get_budget_version_id is
1003 select budget_version_id
1004 from pa_budget_versions
1005 where ci_id = p_ci_id;
1006
1007 cursor get_project_id is
1008 select project_id
1009 from pa_control_items
1010 where ci_id = p_ci_id;
1011
1012 cursor get_resource_assignment_id(bv_Id number) is
1013 select resource_assignment_id
1014 from pa_resource_assignments
1015 where budget_version_id = bv_Id
1016 and task_id = p_Task_Id
1017 and resource_list_member_id = p_Resource_List_Mem_Id;
1018
1019 cursor get_resource_details is
1020 select ORGANIZATION_ID
1021 ,SPREAD_CURVE_ID
1022 ,ETC_METHOD_CODE
1023 ,RESOURCE_CLASS_CODE
1024 ,RESOURCE_CLASS_FLAG
1025 ,RECORD_VERSION_NUMBER
1026 ,INCURRED_BY_RES_FLAG
1027 ,UNIT_OF_MEASURE
1028 ,RESOURCE_TYPE_CODE
1029 from pa_resource_list_members where RESOURCE_LIST_MEMBER_ID = p_Resource_List_Mem_Id;
1030
1031 cursor get_cost_rate_id is
1032 SELECT
1033 ppfo.use_planning_rates_flag,
1034 ppfo.RES_CLASS_RAW_COST_SCH_ID,
1035 ppfo.COST_RES_CLASS_RATE_SCH_ID,
1036 ppfo.RES_CLASS_BILL_RATE_SCH_ID,
1037 ppfo.REV_RES_CLASS_RATE_SCH_ID
1038 FROM pa_proj_fp_options ppfo,
1039 pa_control_items pci
1040 WHERE pci.ci_id = p_ci_id and
1041 pci.project_id = ppfo.project_id and
1042 NVL(ppfo.Approved_Cost_Plan_Type_Flag ,'N') = 'Y' and
1043 ppfo.Fin_Plan_Option_Level_Code = 'PLAN_TYPE';
1044
1045 cursor get_cost_markup(p_rate_sch_id number) is
1046 select markup_percentage from PA_BILL_RATES_ALL
1047 WHERE bill_rate_sch_id = p_rate_sch_id AND
1048 resource_class_code = 'FINANCIAL_ELEMENTS' and
1049 trunc(to_date(p_From_Date)) between trunc(start_date_active)
1050 and trunc(nvl(end_date_active,to_date(p_From_Date)));
1051
1052 res_info get_resource_details%rowtype;
1053 recinfo cur_row%rowtype;
1054 l_debug_mode varchar2(1) := 'N';
1055 l_change_amount number;
1056 l_audit_version_number number;
1057 l_return_status varchar2(1) := 'S';
1058 l_msg_count number := 0;
1059 l_error_msg_code varchar2(1000);
1060 l_status varchar2(10) := 'New';
1061 l_rowid varchar2(100) := p_rowid;
1062 l_Original_supp_trans_id number;
1063 l_res_rate_sch_id number :=0;
1064 l_use_planning_rates_flag varchar2(1) :='N';
1065 l_cost_rate_sch_id number :=0;
1066 l_cost_rate number := 0;
1067 l_res_bill_sch_id number := 0;
1068 l_rev_rate_sch_id number := 0;
1069 l_revenue_rate number :=0;
1070 l_revenue number :=0;
1071 l_burdened_cost number :=0;
1072 l_margin number :=0;
1073 l_bvId number :=0;
1074 l_res_asgn_Id number := 0;
1075 l_project_id number := 0;
1076 l_ci_transaction_id number := p_ci_transaction_id;
1077 l_source_ci_trans_id number;
1078 l_original_ci_trans_id number;
1079
1080 BEGIN
1081
1082 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1083 l_debug_mode := NVL(l_debug_mode, 'N');
1084
1085 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1086
1087 /** set the return status to success **/
1088 x_return_status := 'S';
1089 x_error_msg_code := NULL;
1090
1091 If l_debug_mode = 'Y' Then
1092 print_msg('Inside update row.');
1093 End If;
1094
1095
1096
1097 OPEN cur_row;
1098 FETCH cur_row INTO recinfo;
1099 If cur_row%NOTFOUND then
1100 If l_debug_mode = 'Y' Then
1101 print_msg('row not found return');
1102 End If;
1103 return;
1104 End If;
1105 CLOSE cur_row;
1106
1107
1108 open get_cost_rate_id;
1109 fetch get_cost_rate_id into l_use_planning_rates_flag, l_res_rate_sch_id,l_cost_rate_sch_id,l_res_bill_sch_id,l_rev_rate_sch_id;
1110 close get_cost_rate_id;
1111
1112 if l_use_planning_rates_flag = 'Y' THEN
1113 open get_cost_markup(l_cost_rate_sch_id);
1114 fetch get_cost_markup into l_cost_rate;
1115 close get_cost_markup;
1116 open get_cost_markup(l_rev_rate_sch_id);
1117 fetch get_cost_markup into l_revenue_rate;
1118 close get_cost_markup;
1119 else
1120 open get_cost_markup(l_res_rate_sch_id);
1121 fetch get_cost_markup into l_cost_rate;
1125 close get_cost_markup;
1122 close get_cost_markup;
1123 open get_cost_markup(l_res_bill_sch_id);
1124 fetch get_cost_markup into l_revenue_rate;
1126 end if;
1127
1128
1129
1130 /** check if any of the attributes changed then update else donot **/
1131 /* IF Nvl(recinfo.vendor_id,0) <> nvl(p_vendor_id,0) OR
1132 Nvl(recinfo.po_header_id,0) <> nvl(p_po_header_id,0) OR
1133 Nvl(recinfo.po_line_id,0) <> nvl(p_po_line_id,0) OR
1134 Nvl(recinfo.currency_code,'X') <> nvl(p_currency_code,'X') OR
1135 nvl(recinfo.change_type,'X') <> nvl(p_change_type,'X') OR
1136 Nvl(recinfo.change_description,'X') <> nvl(p_change_description,'X') OR
1137 NVL(recinfo.SUP_QUOTE_REF_NO,0) <> NVL(p_ci_status,0)
1138 THEN
1139 */
1140 If l_debug_mode = 'Y' Then
1141 print_msg('firing update query');
1142 End If;
1143
1144 UPDATE pa_ci_supplier_details SET
1145 VENDOR_ID = p_vendor_id
1146 ,PO_HEADER_ID = p_po_header_id
1147 ,PO_LINE_ID = p_po_line_id
1148 ,ADJUSTED_CI_TRANSACTION_ID = p_adjusted_transaction_id
1149 ,CURRENCY_CODE = p_currency_code
1150 ,CHANGE_AMOUNT = pa_currency.round_trans_currency_amt
1151 (decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT),p_CURRENCY_CODE)
1152 ,CHANGE_TYPE = p_change_type
1153 ,CHANGE_DESCRIPTION = p_change_description
1154 ,LAST_UPDATED_BY = p_last_updated_by
1155 ,LAST_UPDATE_DATE = p_last_update_date
1156 ,LAST_UPDATE_LOGIN = p_last_update_login
1157 ,SUP_QUOTE_REF_NO = p_Sup_ref_no
1158 -- gboomina added for supplier cost 12.1.3 - start
1159 ,change_reason_code = p_change_reason_code
1160 ,quote_negotiation_reference = p_quote_negotiation_reference
1161 ,FROM_CHANGE_DATE = to_date(p_From_Date)
1162 ,TO_CHANGE_DATE = to_date(p_To_Date)
1163 ,expenditure_org_id = p_expenditure_org_id
1164 ,need_by_date = to_date(p_need_by_date)
1165 -- gboomina added for supplier cost 12.1.3 - end
1166 -- racheruv added for bug 9840053
1167 ,burdened_cost = p_burdened_cost
1168 WHERE ci_transaction_id = p_ci_transaction_id;
1169
1170 If sql%found then
1171 x_return_status := 'S';
1172 Else
1173 x_return_status := 'E';
1174 x_error_msg_code := 'NO_DATA_FOUND';
1175 If l_debug_mode = 'Y' Then
1176 print_msg('Update failure:'||x_error_msg_code);
1177 End If;
1178 raise NO_DATA_FOUND;
1179 End If;
1180
1181 -- End IF;
1182
1183 IF NVL(recinfo.ESTIMATED_COST,0) <> NVL(p_Estimated_Cost,0) OR
1184 NVL(recinfo.QUOTED_COST,0) <> NVL(p_Quoted_Cost,0) OR
1185 NVL(recinfo.NEGOTIATED_COST,0) <> NVL(p_Negotiated_Cost,0) OR
1186 Nvl(recinfo.change_amount,0) <> nvl(p_change_amount,0) OR
1187 nvl(to_char(recinfo.FROM_CHANGE_DATE,'YYYY-MM-DD'),'X') <> NVL(p_From_date,'X') or
1188 NVL(to_char(recinfo.FROM_CHANGE_DATE,'YYYY-MM-DD'),'X') <> NVL(p_To_date,'X') or
1189 NVL(recinfo.Burdened_cost,0) <> NVL(p_Burdened_cost,0) or
1190 NVL(recinfo.revenue,0) <> NVL(p_Revenue,0) or
1191 NVL(p_revenue_override_rate,-99) = -99
1192 THEN
1193
1194 If l_debug_mode = 'Y' Then
1195 print_msg('firing update query');
1196 End If;
1197 l_source_ci_trans_id := recinfo.SOURCE_SUPP_TRANS_ID;
1198 l_original_ci_trans_id := recinfo.ORIGINAL_SUPP_TRANS_ID;
1199 l_audit_version_number := recinfo.audit_history_number + 1;
1200
1201 insert_row (
1202 x_rowid => l_rowid
1203 ,x_ci_transaction_id => l_ci_transaction_id
1204 ,p_CI_TYPE_ID => p_ci_type_id
1205 ,p_CI_ID => p_CI_ID
1206 ,p_CI_IMPACT_ID => recinfo.CI_IMPACT_ID
1207 ,p_VENDOR_ID => p_vendor_id
1208 ,p_PO_HEADER_ID => p_po_header_id
1209 ,p_PO_LINE_ID => p_po_line_id
1210 ,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID
1211 ,p_CURRENCY_CODE => p_CURRENCY_CODE
1212 ,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT
1213 ,p_CHANGE_TYPE => p_CHANGE_TYPE
1214 ,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION
1215 ,p_CREATED_BY => FND_GLOBAL.login_id
1216 ,p_CREATION_DATE => trunc(sysdate)
1217 ,p_LAST_UPDATED_BY => FND_GLOBAL.login_id
1218 ,p_LAST_UPDATE_DATE => trunc(sysdate)
1219 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
1220 ,p_Task_Id => p_Task_Id
1221 ,p_Resource_List_Mem_Id => p_Resource_List_Mem_Id
1222 ,p_From_Date => p_From_Date
1223 ,p_To_Date => p_To_Date
1224 ,p_Estimated_Cost => p_Estimated_Cost
1225 ,p_Quoted_Cost => p_Quoted_Cost
1226 ,p_Negotiated_Cost => p_Negotiated_Cost
1227 ,p_Burdened_cost => p_Burdened_cost
1228 ,p_Revenue => p_Revenue
1229 ,p_revenue_override_rate => p_revenue_override_rate
1230 ,p_audit_history_number => l_audit_version_number
1231 ,p_current_audit_flag => p_current_audit_flag
1235 ,p_version_type => recinfo.status
1232 ,p_Original_supp_trans_id => l_original_ci_trans_id
1233 ,p_Source_supp_trans_id => l_ci_transaction_id
1234 ,p_Sup_ref_no => p_Sup_ref_no
1236 -- gboomina modified for supplier cost 12.1.3 requirement - start
1237 ,p_expenditure_type => p_expenditure_type
1238 ,p_expenditure_org_id => p_expenditure_org_id
1239 ,p_change_reason_code => p_change_reason_code
1240 ,p_quote_negotiation_reference => p_quote_negotiation_reference
1241 ,p_need_by_date => p_need_by_date
1242 -- gboomina modified for supplier cost 12.1.3 requirement - end
1243 ,p_ci_status => ''
1244 ,x_return_status => l_return_status
1245 ,x_error_msg_code => l_error_msg_code );
1246
1247
1248
1249 END If;
1250
1251 /*
1252 IF (p_task_id is not null AND p_Resource_List_Mem_Id is not null) then
1253 --knk resource assignment tables
1254 --nag_test('UPDATE Before assignment tables');
1255 OPEN get_budget_version_id;
1256 FETCH get_budget_version_id INTO l_bvId;
1257 If get_budget_version_id%NOTFOUND then
1258 --nag_test('UPDATE NOt found');
1259 If l_debug_mode = 'Y' Then
1260 print_msg('Budget version doesnt exist');
1261 End If;
1262 return;
1263 End If;
1264 CLOSE get_budget_version_id;
1265 --nag_test('UPDATE Budget version id is '||l_bvId);
1266
1267 OPEN get_resource_assignment_id(l_bvId);
1268 FETCH get_resource_assignment_id INTO l_res_asgn_Id;
1269 If get_resource_assignment_id%NOTFOUND then
1270 --nag_test('Create New');
1271 If l_debug_mode = 'Y' Then
1272 print_msg('New record needs to be inserted into resource assignments and asgn curr');
1273 End If;
1274 OPEN get_resource_details;
1275 FETCH get_resource_details INTO res_info;
1276 If get_resource_details%NOTFOUND then
1277 If l_debug_mode = 'Y' Then
1278 print_msg('Unable to retrieve resource details');
1279 End If;
1280 return;
1281 End If;
1282 CLOSE get_resource_details;
1283 open get_res_asgn_id;
1284 fetch get_res_asgn_id into l_res_asgn_Id;
1285 close get_res_asgn_id;
1286 open get_project_id;
1287 fetch get_project_id into l_project_id;
1288 close get_project_id;
1289 INSERT INTO PA_RESOURCE_ASSIGNMENTS(
1290 RESOURCE_ASSIGNMENT_ID
1291 ,BUDGET_VERSION_ID
1292 ,PROJECT_ID
1293 ,TASK_ID
1294 ,RESOURCE_LIST_MEMBER_ID
1295 ,LAST_UPDATE_DATE
1296 ,LAST_UPDATED_BY
1297 ,CREATION_DATE
1298 ,CREATED_BY
1299 ,LAST_UPDATE_LOGIN
1300 ,UNIT_OF_MEASURE
1301 ,TRACK_AS_LABOR_FLAG
1302 ,PROJECT_ASSIGNMENT_ID
1303 ,TOTAL_PLAN_REVENUE
1304 ,TOTAL_PLAN_RAW_COST
1305 ,TOTAL_PLAN_QUANTITY
1306 ,RESOURCE_ASSIGNMENT_TYPE
1307 ,TOTAL_PROJECT_RAW_COST
1308 ,TOTAL_PROJECT_BURDENED_COST
1309 ,TOTAL_PROJECT_REVENUE
1310 --RBS_ELEMENT_ID
1311 ,PLANNING_START_DATE
1312 ,PLANNING_END_DATE
1313 ,SPREAD_CURVE_ID
1314 ,ETC_METHOD_CODE
1315 ,RES_TYPE_CODE
1316 ,RESOURCE_CLASS_CODE
1317 ,ORGANIZATION_ID
1318 ,RECORD_VERSION_NUMBER
1319 ,INCURRED_BY_RES_FLAG
1320 ,RATE_BASED_FLAG
1321 ,RATE_EXP_FUNC_CURR_CODE
1322 --,RATE_EXPENDITURE_ORG_ID
1323 ,RESOURCE_CLASS_FLAG
1324 ,RESOURCE_RATE_BASED_FLAG
1325 )VALUES
1326 ( l_res_asgn_Id
1327 ,l_bvId
1328 ,l_project_id
1329 ,p_Task_Id
1330 ,p_Resource_List_Mem_Id
1331 ,sysdate
1332 ,1319
1333 ,sysdate
1334 ,1319
1335 ,p_LAST_UPDATE_LOGIN
1336 ,res_info.UNIT_OF_MEASURE
1337 ,'Y'
1338 ,-1
1339 ,l_revenue
1340 ,decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT)
1341 ,decode(p_Burdened_cost,null,(p_CHANGE_AMOUNT+(p_CHANGE_AMOUNT*0.1)),p_Burdened_cost)
1342 ,'USER_ENTERED'
1343 ,decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT)
1344 ,decode(p_Burdened_cost,null,(p_CHANGE_AMOUNT+(p_CHANGE_AMOUNT*0.1)),p_Burdened_cost)
1345 ,l_revenue
1346 ,to_date(p_From_Date,'YYYY/MM/DD')
1347 ,to_date(p_To_Date,'YYYY/MM/DD')
1348 ,res_info.SPREAD_CURVE_ID
1349 ,res_info.ETC_METHOD_CODE
1350 ,res_info.RESOURCE_TYPE_CODE
1351 ,res_info.RESOURCE_CLASS_CODE
1352 ,res_info.ORGANIZATION_ID
1353 ,res_info.RECORD_VERSION_NUMBER
1354 ,res_info.INCURRED_BY_RES_FLAG
1355 ,'N'
1356 ,p_CURRENCY_CODE
1357 ,res_info.RESOURCE_CLASS_FLAG
1358 ,'Y'
1359 );
1360 else
1361 --nag_test('UPDATE resource assignment id '||l_res_asgn_Id);
1362 If l_debug_mode = 'Y' Then
1363 print_msg('Updating the existing resource assignment');
1364 End If;
1365 update PA_RESOURCE_ASSIGNMENTS SET
1366 LAST_UPDATE_DATE = p_LAST_UPDATE_DATE
1367 ,LAST_UPDATED_BY = 1319
1368 ,LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN
1369 ,TOTAL_PLAN_REVENUE = l_revenue
1370 ,TOTAL_PLAN_RAW_COST = decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT)
1371 ,TOTAL_PLAN_BURDENED_COST = decode(p_Burdened_cost,null,(p_CHANGE_AMOUNT+(p_CHANGE_AMOUNT*0.1)),p_Burdened_cost)
1372 ,TOTAL_PLAN_QUANTITY = decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT)
1376 ,RATE_BASED_FLAG = 'N'
1373 ,TOTAL_PROJECT_RAW_COST = decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT)
1374 ,TOTAL_PROJECT_BURDENED_COST = decode(p_Burdened_cost,null,(p_CHANGE_AMOUNT+(p_CHANGE_AMOUNT*0.1)),p_Burdened_cost)
1375 ,TOTAL_PROJECT_REVENUE = l_revenue
1377 ,RESOURCE_RATE_BASED_FLAG = 'N'
1378 WHERE RESOURCE_ASSIGNMENT_ID = l_res_asgn_Id;
1379
1380 --nag_test('Done with resource assignment insert');
1381
1382 UPDATE pa_resource_asgn_curr SET
1383 TOTAL_QUANTITY = pa_currency.round_trans_currency_amt
1384 (decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT),p_CURRENCY_CODE)
1385 ,TXN_AVERAGE_RAW_COST_RATE = 1
1386 ,TXN_AVERAGE_BURDEN_COST_RATE = 0.1
1387 ,TXN_AVERAGE_BILL_RATE = l_revenue_rate
1388 ,TOTAL_TXN_RAW_COST = pa_currency.round_trans_currency_amt
1389 (decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT),p_CURRENCY_CODE)
1390 ,TOTAL_TXN_BURDENED_COST = decode(p_Burdened_cost,null,(p_CHANGE_AMOUNT+(p_CHANGE_AMOUNT*0.1)),p_Burdened_cost)
1391 ,TOTAL_TXN_REVENUE = l_revenue
1392 ,TOTAL_PROJECT_RAW_COST = pa_currency.round_trans_currency_amt
1393 (decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT),p_CURRENCY_CODE)
1394 ,TOTAL_PROJECT_BURDENED_COST = decode(p_Burdened_cost,null,(p_CHANGE_AMOUNT+(p_CHANGE_AMOUNT*0.1)),p_Burdened_cost)
1395 ,TOTAL_PROJECT_REVENUE = l_revenue
1396 ,TOTAL_PROJFUNC_RAW_COST = pa_currency.round_trans_currency_amt
1397 (decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT),p_CURRENCY_CODE)
1398 ,TOTAL_PROJFUNC_BURDENED_COST = decode(p_Burdened_cost,null,(p_CHANGE_AMOUNT+(p_CHANGE_AMOUNT*0.1)),p_Burdened_cost)
1399 ,TOTAL_PROJFUNC_REVENUE = l_revenue
1400 ,LAST_UPDATE_DATE = p_LAST_UPDATE_DATE
1401 ,LAST_UPDATED_BY = 1319
1402 ,LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN
1403 WHERE RESOURCE_ASSIGNMENT_ID = l_res_asgn_Id;
1404 End If;
1405 CLOSE get_resource_assignment_id;
1406
1407 end if; -- task_id not null
1408 */
1409
1410 EXCEPTION
1411 when others then
1412
1413 x_error_msg_code := sqlcode||sqlerrm;
1414 If l_debug_mode = 'Y' Then
1415 print_msg('Exception :'||x_error_msg_code);
1416 End if;
1417 Raise;
1418
1419 END update_row;
1420
1421
1422 PROCEDURE delete_row (p_ci_transaction_id in NUMBER)IS
1423
1424 cursor fetch_details is
1425 select TASK_ID, RESOURCE_LIST_MEMBER_ID, ci_id
1426 from pa_ci_supplier_details
1427 where CI_TRANSACTION_ID = p_ci_transaction_id;
1428
1429 cursor get_budget_version_id(p_ci_id number) is
1430 select budget_version_id
1431 from pa_budget_versions
1432 where ci_id = p_ci_id;
1433
1434 cursor get_assignment_details(p_bvId number, p_task_id number, p_res_id number) is
1435 select pra.RESOURCE_ASSIGNMENT_ID, ppe.ELEMENT_NUMBER, ppe.NAME
1436 from pa_resource_assignments pra, pa_proj_elements ppe, pa_tasks pt
1437 WHERE pra.budget_version_id = p_bvId
1438 and pra.task_id = p_task_id
1439 and pra.RESOURCE_LIST_MEMBER_ID = p_res_id
1440 and pt.task_id = ppe.proj_element_id;
1441
1442
1443 l_currency_code_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
1444 l_task_elem_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1445 l_resource_assignment_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1446 l_task_name_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
1447 l_task_number_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
1448
1449 l_bvId number := 0;
1450 l_res_asgn_Id number := 0;
1451 l_return_status varchar2(100) := 'S';
1452 l_error_msg_code varchar2(100) := NULL;
1453 l_msg_count number := 0;
1454 l_debug_mode varchar2(1) := 'N';
1455 l_res_id number;
1456 l_task_id number;
1457 l_res_asg_id number;
1458 l_task_number varchar2(100);
1459 l_task_name varchar2(100);
1460 l_ci_id number;
1461
1462 BEGIN
1463
1464 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1465 l_debug_mode := NVL(l_debug_mode, 'N');
1466
1467 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1468 open fetch_details;
1469 fetch fetch_details into l_task_id, l_res_id, l_ci_id;
1470 close fetch_details;
1471
1472 DELETE FROM PA_CI_SUPPLIER_DETAILS
1473 WHERE CI_TRANSACTION_ID = P_CI_TRANSACTION_ID;
1474 if sql%found then
1475 If l_debug_mode = 'Y' Then
1476 print_msg('Delete Success');
1477 End iF;
1478
1479 IF (l_task_id is not null AND l_res_id is not null) then
1480 --call delete planning element api
1481 open get_budget_version_id(l_ci_id);
1482 fetch get_budget_version_id into l_bvId;
1483 close get_budget_version_id;
1484 l_currency_code_tbl.extend(1);
1485 l_task_elem_version_id_tbl.extend(1);
1486 l_task_elem_version_id_tbl(1) := l_bvId;
1487 l_resource_assignment_id_tbl.extend(1);
1488 l_task_name_tbl.extend(1);
1489 l_task_number_tbl.extend(1);
1490
1491 open get_assignment_details(l_bvId,l_task_id,l_res_id);
1492 fetch get_assignment_details into l_res_asg_id,l_task_number,l_task_name;
1493 close get_assignment_details;
1494
1495 l_currency_code_tbl(1) := 'USD';
1496 l_resource_assignment_id_tbl(1) := l_res_asg_id;
1497 l_task_name_tbl(1) := l_task_name;
1498 l_task_number_tbl(1) := l_task_number;
1499
1500
1504 ,p_task_or_res => 'ASSIGNMENT'
1501
1502 pa_fp_planning_transaction_pub.delete_planning_transactions(
1503 p_context => 'BUDGET'
1505 ,p_element_version_id_tbl => l_task_elem_version_id_tbl
1506 ,p_task_number_tbl => l_task_number_tbl
1507 ,p_task_name_tbl => l_task_name_tbl
1508 ,p_resource_assignment_tbl => l_resource_assignment_id_tbl
1509 ,p_currency_code_tbl => l_currency_code_tbl
1510 ,x_return_status => l_return_status
1511 ,x_msg_count => l_msg_count
1512 ,x_msg_data => l_error_msg_code);
1513
1514 end if;
1515
1516 Else
1517 If l_debug_mode = 'Y' Then
1518 print_msg('Delete Failure');
1519 End If;
1520 End if;
1521
1522 END delete_row;
1523
1524 PROCEDURE delete_row (x_rowid in VARCHAR2)IS
1525
1526 cursor get_itemid is
1527 select ci_transaction_id
1528 from pa_ci_supplier_details
1529 where rowid = x_rowid;
1530
1531 l_ci_transaction_id Number;
1532
1533 BEGIN
1534 open get_itemid;
1535 fetch get_itemid into l_ci_transaction_id;
1536 close get_itemid;
1537
1538 delete_row (l_ci_transaction_id);
1539
1540 END delete_row;
1541
1542 PROCEDURE lock_row (x_rowid in VARCHAR2)IS
1543 BEGIN
1544 null;
1545 END lock_row;
1546
1547 END PA_CI_SUPPLIER_PKG;