[Home] [Help]
PACKAGE BODY: APPS.PA_EXPENDITURE_ITEMS_PKG
Source
1 package body pa_expenditure_items_pkg as
2 /* $Header: PAXTITMB.pls 120.7.12020000.3 2013/04/24 10:49:00 rboyalap ship $ */
3
4 procedure insert_row (x_rowid in out NOCOPY VARCHAR2,
5 x_expenditure_item_id in out NOCOPY NUMBER,
6 x_last_update_date in DATE,
7 x_last_updated_by in NUMBER,
8 x_creation_date in DATE,
9 x_created_by in NUMBER,
10 x_expenditure_id in NUMBER,
11 x_task_id in NUMBER,
12 -- x_cbs_element_id in NUMBER DEFAULT NULL, -- Added as part of Bug 16220146
13 -- commented the above as part of bug 16714738
14 x_expenditure_item_date in DATE,
15 x_expenditure_type in VARCHAR2,
16 x_cost_distributed_flag in VARCHAR2,
17 x_revenue_distributed_flag in VARCHAR2,
18 x_billable_flag in VARCHAR2,
19 x_bill_hold_flag in VARCHAR2,
20 x_quantity in NUMBER,
21 x_non_labor_resource in VARCHAR2,
22 x_organization_id in NUMBER,
23 x_override_to_organization_id in NUMBER,
24 x_raw_cost in NUMBER,
25 x_raw_cost_rate in NUMBER,
26 x_burden_cost in NUMBER,
27 x_burden_cost_rate in NUMBER,
28 x_cost_dist_rejection_code in VARCHAR2,
29 x_labor_cost_multiplier_name in VARCHAR2,
30 x_raw_revenue in NUMBER,
31 x_bill_rate in NUMBER,
32 x_accrued_revenue in NUMBER,
33 x_accrual_rate in NUMBER,
34 x_adjusted_revenue in NUMBER,
35 x_adjusted_rate in NUMBER,
36 x_bill_amount in NUMBER,
37 x_forecast_revenue in NUMBER,
38 x_bill_rate_multiplier in NUMBER,
39 x_rev_dist_rejection_code in VARCHAR2,
40 x_event_num in NUMBER,
41 x_event_task_id in NUMBER,
42 x_bill_job_id in NUMBER,
43 x_bill_job_billing_title in VARCHAR2,
44 x_bill_employee_billing_title in VARCHAR2,
45 x_adjusted_expenditure_item_id in NUMBER,
46 x_net_zero_adjustment_flag in VARCHAR2,
47 x_transferred_from_exp_item_id in NUMBER,
48 x_converted_flag in VARCHAR2,
49 x_last_update_login in NUMBER,
50 x_attribute_category in VARCHAR2,
51 x_attribute1 in VARCHAR2,
52 x_attribute2 in VARCHAR2,
53 x_attribute3 in VARCHAR2,
54 x_attribute4 in VARCHAR2,
55 x_attribute5 in VARCHAR2,
56 x_attribute6 in VARCHAR2,
57 x_attribute7 in VARCHAR2,
58 x_attribute8 in VARCHAR2,
59 x_attribute9 in VARCHAR2,
60 x_attribute10 in VARCHAR2,
61 x_cost_ind_compiled_set_id in NUMBER,
62 x_rev_ind_compiled_set_id in NUMBER,
63 x_inv_ind_compiled_set_id in NUMBER,
64 x_cost_burden_distributed_flag in VARCHAR2,
65 x_ind_cost_dist_rejection_code in VARCHAR2,
66 x_orig_transaction_reference in VARCHAR2,
67 x_transaction_source in VARCHAR2,
68 x_project_id in NUMBER,
69 x_source_expenditure_item_id in NUMBER,
70 x_job_id in NUMBER,
71 x_expenditure_comment in VARCHAR2,
72 x_system_linkage_function in VARCHAR2,
73 x_receipt_currency_amount in NUMBER,
74 x_receipt_currency_code in VARCHAR2,
75 x_receipt_exchange_rate in NUMBER,
76 x_denom_currency_code in VARCHAR2,
77 x_denom_raw_cost in NUMBER,
78 x_denom_burdened_cost in NUMBER,
79 x_acct_exchange_rounding_limit in NUMBER,
80 x_acct_currency_code in VARCHAR2,
81 x_acct_rate_date in DATE,
82 x_acct_rate_type in VARCHAR2,
83 x_acct_exchange_rate in NUMBER,
84 x_acct_raw_cost in NUMBER,
85 x_acct_burdened_cost in NUMBER,
86 x_project_currency_code in VARCHAR2,
87 x_project_rate_date in DATE,
88 x_project_rate_type in VARCHAR2,
89 x_project_exchange_rate in NUMBER,
90 x_recvr_org_id in NUMBER,
91 p_assignment_id IN NUMBER default null,
92 p_work_type_id IN NUMBER default null,
93 p_projfunc_currency_code IN varchar2 default null,
94 p_projfunc_cost_rate_date IN date default null,
95 p_projfunc_cost_rate_type IN varchar2 default null,
96 p_projfunc_cost_exchange_rate IN number default null,
97 p_project_raw_cost IN number default null,
98 p_project_burdened_cost IN number default null,
99 p_tp_amt_type_code IN varchar2 default null,
100 p_prvdr_accrual_date IN date default null,
101 p_recvr_accrual_date IN date default null,
102 p_capital_event_id IN NUMBER default null,
103 p_wip_resource_id IN number default null,
104 p_inventory_item_id IN number default null,
105 p_unit_of_measure IN varchar2 default null ,
106 P_Org_ID IN NUMBER default NULL, -- 12i MOAC changes
107 --,P_Agreement_id IN NUMBER DEFAULT NULL --FSIO Changes
108 p_cbs_element_id IN NUMBER default null -- Added as part of bug 16714738
109 ) is
110
111 cursor return_rowid is select rowid from pa_expenditure_items
112 where expenditure_item_id = x_expenditure_item_id;
113 cursor get_itemid is select pa_expenditure_items_s.nextval from sys.dual;
114
115 status NUMBER;
116 l_rowid VARCHAR2(1000);
117 l_expenditure_item_id NUMBER;
118 BEGIN
119 l_expenditure_item_id := x_expenditure_item_id;
120 l_rowid := x_rowid;
121
122 if (x_expenditure_item_id is null) then
123 open get_itemid;
124 fetch get_itemid into x_expenditure_item_id;
125 close get_itemid;
126 end if;
127
128 -- if amt is negative, need to update reversed original
129
130 insert into pa_expenditure_items (
131 expenditure_item_id,
132 last_update_date,
133 last_updated_by,
134 creation_date,
135 created_by,
136 expenditure_id,
137 task_id,
138 -- cbs_element_id, -- Added as part of Bug 16220146
139 -- commented the above as part of bug 16714738
140 expenditure_item_date,
141 expenditure_type,
142 cost_distributed_flag,
143 revenue_distributed_flag,
144 billable_flag,
145 bill_hold_flag,
146 quantity,
147 non_labor_resource,
148 organization_id,
149 override_to_organization_id,
150 raw_cost,
151 raw_cost_rate,
152 burden_cost,
153 burden_cost_rate,
154 cost_dist_rejection_code,
155 labor_cost_multiplier_name,
156 raw_revenue,
157 bill_rate,
158 accrued_revenue,
159 accrual_rate,
160 adjusted_revenue,
161 adjusted_rate,
162 bill_amount,
163 forecast_revenue,
164 bill_rate_multiplier,
165 rev_dist_rejection_code,
166 event_num,
167 event_task_id,
168 bill_job_id,
169 bill_job_billing_title,
170 bill_employee_billing_title,
171 adjusted_expenditure_item_id,
172 net_zero_adjustment_flag,
173 transferred_from_exp_item_id,
174 converted_flag,
175 last_update_login,
176 attribute_category,
177 attribute1,
178 attribute2,
179 attribute3,
180 attribute4,
181 attribute5,
182 attribute6,
183 attribute7,
184 attribute8,
185 attribute9,
186 attribute10,
187 cost_ind_compiled_set_id,
188 rev_ind_compiled_set_id,
189 inv_ind_compiled_set_id,
190 cost_burden_distributed_flag,
191 ind_cost_dist_rejection_code,
192 orig_transaction_reference,
193 transaction_source,
194 project_id,
195 source_expenditure_item_id,
196 job_id,
197 system_linkage_function,
198 receipt_currency_amount,
199 receipt_currency_code,
200 receipt_exchange_rate,
201 denom_currency_code,
202 denom_raw_cost,
203 denom_burdened_cost,
204 acct_exchange_rounding_limit,
205 acct_currency_code,
206 acct_rate_date,
207 acct_rate_type,
208 acct_exchange_rate,
209 acct_raw_cost,
210 acct_burdened_cost,
211 project_currency_code,
212 project_rate_date,
213 project_rate_type,
214 project_exchange_rate,
215 recvr_org_id ,
216 assignment_id,
217 work_type_id,
218 projfunc_currency_code,
219 projfunc_cost_rate_date,
220 projfunc_cost_rate_type,
221 projfunc_cost_exchange_rate,
222 project_raw_cost,
223 project_burdened_cost,
224 tp_amt_type_code,
225 prvdr_accrual_date,
226 recvr_accrual_date,
227 capital_event_id,
228 wip_resource_id,
229 inventory_item_id,
230 unit_of_measure,
231 org_id, -- 12i MOAC changes
232 --,agreement_id --FSIO Changes
233 cbs_element_id -- Added as part of bug 16714738
234 )
235 values (x_expenditure_item_id,
236 x_last_update_date,
237 x_last_updated_by,
238 x_creation_date,
239 x_created_by,
240 x_expenditure_id,
241 x_task_id,
242 -- x_cbs_element_id, -- Added as part of Bug 16220146
243 -- commeneted as part of bug 16714738
244 x_expenditure_item_date,
245 x_expenditure_type,
246 x_cost_distributed_flag,
247 x_revenue_distributed_flag,
248 x_billable_flag,
249 x_bill_hold_flag,
250 x_quantity,
251 x_non_labor_resource,
252 x_organization_id,
253 x_override_to_organization_id,
254 x_raw_cost,
255 x_raw_cost_rate,
256 x_burden_cost,
257 x_burden_cost_rate,
258 x_cost_dist_rejection_code,
259 x_labor_cost_multiplier_name,
260 x_raw_revenue,
261 x_bill_rate,
262 x_accrued_revenue,
263 x_accrual_rate,
264 x_adjusted_revenue,
265 x_adjusted_rate,
266 x_bill_amount,
267 x_forecast_revenue,
268 x_bill_rate_multiplier,
269 x_rev_dist_rejection_code,
270 x_event_num,
271 x_event_task_id,
272 x_bill_job_id,
273 x_bill_job_billing_title,
274 x_bill_employee_billing_title,
275 x_adjusted_expenditure_item_id,
276 x_net_zero_adjustment_flag,
277 x_transferred_from_exp_item_id,
278 x_converted_flag,
279 x_last_update_login,
280 x_attribute_category,
281 x_attribute1,
282 x_attribute2,
283 x_attribute3,
284 x_attribute4,
285 x_attribute5,
286 x_attribute6,
287 x_attribute7,
288 x_attribute8,
289 x_attribute9,
290 x_attribute10,
291 x_cost_ind_compiled_set_id,
292 x_rev_ind_compiled_set_id,
293 x_inv_ind_compiled_set_id,
294 x_cost_burden_distributed_flag,
295 x_ind_cost_dist_rejection_code,
296 x_orig_transaction_reference,
297 x_transaction_source,
298 x_project_id,
299 x_source_expenditure_item_id,
300 x_job_id,
301 x_system_linkage_function,
302 x_receipt_currency_amount,
303 x_receipt_currency_code,
304 x_receipt_exchange_rate,
305 x_denom_currency_code,
306 x_denom_raw_cost,
307 x_denom_burdened_cost,
308 x_acct_exchange_rounding_limit,
309 x_acct_currency_code,
310 x_acct_rate_date,
311 x_acct_rate_type,
312 x_acct_exchange_rate,
313 x_acct_raw_cost,
314 x_acct_burdened_cost,
315 x_project_currency_code,
316 x_project_rate_date,
317 x_project_rate_type,
318 x_project_exchange_rate,
319 x_recvr_org_id,
320 p_assignment_id,
321 p_work_type_id,
322 p_projfunc_currency_code,
323 p_projfunc_cost_rate_date,
324 p_projfunc_cost_rate_type,
325 p_projfunc_cost_exchange_rate,
326 p_project_raw_cost,
327 p_project_burdened_cost,
328 p_tp_amt_type_code,
329 p_prvdr_accrual_date,
330 p_recvr_accrual_date,
331 p_capital_event_id,
332 p_wip_resource_id,
333 p_inventory_item_id,
334 p_unit_of_measure,
335 P_Org_Id, -- 12i MOAC changes
336 --,P_Agreement_id --FSIO Changes
337 p_cbs_element_id -- Added as part of bug 16714738
338 );
339
340 open return_rowid;
341 fetch return_rowid into x_rowid;
342 if (return_rowid%notfound) then
343 raise NO_DATA_FOUND; -- should we return something else?
344 end if;
345 close return_rowid;
346
347 -- this assumes the neg quantity has already been validated, and
348 -- is matched. unmatched occurs in adjustments
349 /* Bug 1715308 removed the criteria of x_quantity < 0
350 if (((x_quantity < 0) or (x_burden_cost < 0)) and
351 (x_net_zero_adjustment_flag = 'Y')) then */
352
353 If x_net_zero_adjustment_flag = 'Y' then
354 update pa_expenditure_items
355 set net_zero_adjustment_flag = 'Y',
356 last_update_date = x_last_update_date -- Added as part of Bug 12878265
357 where expenditure_item_id = x_adjusted_expenditure_item_id;
358
359 /*Added for bug 5639026 to update transfer price columns*/
360 IF nvl(x_adjusted_expenditure_item_id, 0) <> 0 then
361 UPDATE pa_expenditure_items
362 SET(cc_prvdr_organization_id,
363 cc_recvr_organization_id,
364 cc_rejection_code,
365 denom_tp_currency_code,
366 denom_transfer_price,
367 acct_tp_rate_type,
368 acct_tp_rate_date,
369 acct_tp_exchange_rate,
370 acct_transfer_price,
371 projacct_transfer_price,
372 cc_markup_base_code,
373 tp_base_amount,
374 tp_ind_compiled_set_id,
375 tp_bill_rate,
376 tp_bill_markup_percentage,
377 tp_schedule_line_percentage,
378 tp_rule_percentage,
379 cost_job_id,
380 tp_job_id,
381 prov_proj_bill_job_id,
382 project_tp_rate_date,
383 project_tp_rate_type,
384 project_tp_exchange_rate,
385 project_transfer_price,
386 /*Start-changes for bug:12588960*/
387 PROJECT_RATE_DATE,
388 PROJECT_RATE_TYPE,
389 LOCATION_ID,
390 COSTING_METHOD,
391 RATE_SOURCE_CODE,
392 PAYROLL_ACCRUAL_FLAG
393 /*End-changes for bug:12588960*/
394 )=
395 --,agreement_id) = --FSIO Changes
396 (SELECT ei.cc_prvdr_organization_id,
397 ei.cc_recvr_organization_id,
398 ei.cc_rejection_code,
399 ei.denom_tp_currency_code,
400 (0 -ei.denom_transfer_price),
401 ei.acct_tp_rate_type,
402 ei.acct_tp_rate_date,
403 ei.acct_tp_exchange_rate,
404 (0 -ei.acct_transfer_price),
405 (0 -ei.projacct_transfer_price),
406 ei.cc_markup_base_code,
407 (0 -ei.tp_base_amount),
408 ei.tp_ind_compiled_set_id,
409 ei.tp_bill_rate,
410 ei.tp_bill_markup_percentage,
411 ei.tp_schedule_line_percentage,
412 ei.tp_rule_percentage,
413 ei.cost_job_id,
414 ei.tp_job_id,
415 ei.prov_proj_bill_job_id,
416 ei.project_tp_rate_date,
417 ei.project_tp_rate_type,
418 ei.project_tp_exchange_rate,
419 (0 -ei.project_transfer_price),
420 /*Start-changes for bug:12588960*/
421 ei.PROJECT_RATE_DATE,
422 ei.PROJECT_RATE_TYPE,
423 ei.LOCATION_ID,
424 ei.COSTING_METHOD,
425 ei.RATE_SOURCE_CODE,
426 ei.PAYROLL_ACCRUAL_FLAG
427 /*End-changes for bug:12588960*/
428 -- ,ei.agreement_id --FSIO Changes
429 FROM pa_expenditure_items ei
430 WHERE ei.expenditure_item_id = x_adjusted_expenditure_item_id)
431 WHERE expenditure_item_id = x_expenditure_item_id;
432 END if;
433 /* End of bug fix for 5639026 */
434
435 -- Date : 17-JUN-99
436 --
437 -- Earlier the value for parameter expenditure id was NULL. This
438 -- resulted in the reversing related items getting creating in a
439 -- different expenditure id which is different from that of the
440 -- source item. Changed the NULL value to the current expenditure
441 -- id.
442 --
443 pa_adjustments.ReverseRelatedItems(x_adjusted_expenditure_item_id,
444 x_expenditure_id,
445 'PAXTREPE',
446 X_created_by,
447 X_last_update_login,
448 status );
449 --
450 --
451 end if;
452
453
454 if (x_expenditure_comment is not null) then
455 insert into pa_expenditure_comments (expenditure_item_id,
456 line_number,
457 expenditure_comment,
458 last_update_date,
459 last_updated_by,
460 creation_date,
461 created_by,
462 last_update_login)
463 values (x_expenditure_item_id,
464 1,
465 x_expenditure_comment,
466 x_last_update_date,
467 x_last_updated_by,
468 x_creation_date,
469 x_created_by,
470 x_last_update_login);
471 -- what if insert into comment fails?
472 end if;
473
474 -- R12 NOCOPY mandate
475 EXCEPTION WHEN OTHERS THEN
476 x_expenditure_item_id := l_expenditure_item_id;
477 x_rowid := l_rowid;
478 RAISE;
479 END insert_row;
480
481
482 procedure update_row (x_rowid in VARCHAR2,
483 x_expenditure_item_id in NUMBER,
484 x_last_update_date in DATE,
485 x_last_updated_by in NUMBER,
486 x_expenditure_id in NUMBER,
487 x_task_id in NUMBER,
488 -- x_cbs_element_id in NUMBER DEFAULT NULL, -- Added as part of Bug 16220146
489 -- commnedted the above as part of bug 16714738
490 x_expenditure_item_date in DATE,
491 x_expenditure_type in VARCHAR2,
492 x_cost_distributed_flag in VARCHAR2,
493 x_revenue_distributed_flag in VARCHAR2,
494 x_billable_flag in VARCHAR2,
495 x_bill_hold_flag in VARCHAR2,
496 x_quantity in NUMBER,
497 x_non_labor_resource in VARCHAR2,
498 x_organization_id in NUMBER,
499 x_override_to_organization_id in NUMBER,
500 x_raw_cost in NUMBER,
501 x_raw_cost_rate in NUMBER,
502 x_burden_cost in NUMBER,
503 x_burden_cost_rate in NUMBER,
504 x_cost_dist_rejection_code in VARCHAR2,
505 x_labor_cost_multiplier_name in VARCHAR2,
506 x_raw_revenue in NUMBER,
507 x_bill_rate in NUMBER,
508 x_accrued_revenue in NUMBER,
509 x_accrual_rate in NUMBER,
510 x_adjusted_revenue in NUMBER,
511 x_adjusted_rate in NUMBER,
512 x_bill_amount in NUMBER,
513 x_forecast_revenue in NUMBER,
514 x_bill_rate_multiplier in NUMBER,
515 x_rev_dist_rejection_code in VARCHAR2,
516 x_event_num in NUMBER,
517 x_event_task_id in NUMBER,
518 x_bill_job_id in NUMBER,
519 x_bill_job_billing_title in VARCHAR2,
520 x_bill_employee_billing_title in VARCHAR2,
521 x_adjusted_expenditure_item_id in NUMBER,
522 x_net_zero_adjustment_flag in VARCHAR2,
523 x_transferred_from_exp_item_id in NUMBER,
524 x_converted_flag in VARCHAR2,
525 x_last_update_login in NUMBER,
526 x_attribute_category in VARCHAR2,
527 x_attribute1 in VARCHAR2,
528 x_attribute2 in VARCHAR2,
529 x_attribute3 in VARCHAR2,
530 x_attribute4 in VARCHAR2,
531 x_attribute5 in VARCHAR2,
532 x_attribute6 in VARCHAR2,
533 x_attribute7 in VARCHAR2,
534 x_attribute8 in VARCHAR2,
535 x_attribute9 in VARCHAR2,
536 x_attribute10 in VARCHAR2,
537 x_cost_ind_compiled_set_id in NUMBER,
538 x_rev_ind_compiled_set_id in NUMBER,
539 x_inv_ind_compiled_set_id in NUMBER,
540 x_cost_burden_distributed_flag in VARCHAR2,
541 x_ind_cost_dist_rejection_code in VARCHAR2,
542 x_orig_transaction_reference in VARCHAR2,
543 x_transaction_source in VARCHAR2,
544 x_project_id in NUMBER,
545 x_source_expenditure_item_id in NUMBER,
546 x_job_id in NUMBER,
547 x_expenditure_comment in VARCHAR2,
548 x_system_linkage_function in VARCHAR2,
549 x_receipt_currency_amount in NUMBER,
550 x_receipt_currency_code in VARCHAR2,
551 x_receipt_exchange_rate in NUMBER,
552 x_denom_currency_code in VARCHAR2,
553 x_denom_raw_cost in NUMBER,
554 x_denom_burdened_cost in NUMBER,
555 x_acct_exchange_rounding_limit in NUMBER,
556 x_acct_currency_code in VARCHAR2,
557 x_acct_rate_date in DATE,
558 x_acct_rate_type in VARCHAR2,
559 x_acct_exchange_rate in NUMBER,
560 x_acct_raw_cost in NUMBER,
561 x_acct_burdened_cost in NUMBER,
562 x_project_currency_code in VARCHAR2,
563 x_project_rate_date in DATE,
564 x_project_rate_type in VARCHAR2,
565 x_project_exchange_rate in NUMBER,
566 x_recvr_org_id in NUMBER,
567 p_assignment_id IN NUMBER default null,
568 p_work_type_id IN NUMBER default null,
569 p_projfunc_currency_code IN varchar2 default null,
570 p_projfunc_cost_rate_date IN date default null,
571 p_projfunc_cost_rate_type IN varchar2 default null,
572 p_projfunc_cost_exchange_rate IN number default null,
573 p_project_raw_cost IN number default null,
574 p_project_burdened_cost IN number default null,
575 p_tp_amt_type_code IN varchar2 default null,
576 p_prvdr_accrual_date IN date default null,
577 p_recvr_accrual_date IN date default null,
578 p_capital_event_id IN number default null,
579 --,p_agreement_id IN NUMBER DEFAULT NULL --FSIO Changes
580 p_cbs_element_id IN NUMBER default null -- Added as part of bug 16714738
581 ) is
582
583 cursor c_get_comment is select expenditure_comment
584 from pa_expenditure_comments
585 where expenditure_item_id = x_expenditure_item_id;
586
587 temp_comment c_get_comment%rowtype;
588
589 action VARCHAR2(30);
590 outcome VARCHAR2(100);
591 num_processed NUMBER;
592 num_rejected NUMBER;
593 status NUMBER;
594
595 BEGIN
596 -- need to check status, force user to use adjust if necessary
597
598 update pa_expenditure_items
599 set expenditure_item_id = x_expenditure_item_id,
600 last_update_date = x_last_update_date,
601 last_updated_by = x_last_updated_by,
602 expenditure_id = x_expenditure_id,
603 task_id = x_task_id,
604 -- cbs_element_id = x_cbs_element_id, -- Added as part of Bug 16220146
605 -- commented the above as part of bug 16714738
606 expenditure_item_date = x_expenditure_item_date,
607 expenditure_type = x_expenditure_type,
608 cost_distributed_flag = x_cost_distributed_flag,
609 revenue_distributed_flag = x_revenue_distributed_flag,
610 billable_flag = x_billable_flag,
611 bill_hold_flag = x_bill_hold_flag,
612 quantity = x_quantity,
613 non_labor_resource = x_non_labor_resource,
614 organization_id = x_organization_id,
615 override_to_organization_id = x_override_to_organization_id,
616 raw_cost = x_raw_cost,
617 raw_cost_rate = x_raw_cost_rate,
618 burden_cost = x_burden_cost,
619 burden_cost_rate = x_burden_cost_rate,
620 cost_dist_rejection_code = x_cost_dist_rejection_code,
621 labor_cost_multiplier_name = x_labor_cost_multiplier_name,
622 raw_revenue = x_raw_revenue,
623 bill_rate = x_bill_rate,
624 accrued_revenue = x_accrued_revenue,
625 accrual_rate = x_accrual_rate,
626 adjusted_revenue = x_adjusted_revenue,
627 adjusted_rate = x_adjusted_rate,
628 bill_amount = x_bill_amount,
629 forecast_revenue = x_forecast_revenue,
630 bill_rate_multiplier = x_bill_rate_multiplier,
631 rev_dist_rejection_code = x_rev_dist_rejection_code,
632 event_num = x_event_num,
633 event_task_id = x_event_task_id,
634 bill_job_id = x_bill_job_id,
635 bill_job_billing_title = x_bill_job_billing_title,
636 bill_employee_billing_title = x_bill_employee_billing_title,
637 adjusted_expenditure_item_id = x_adjusted_expenditure_item_id,
638 net_zero_adjustment_flag = x_net_zero_adjustment_flag,
639 transferred_from_exp_item_id = x_transferred_from_exp_item_id,
640 converted_flag = x_converted_flag,
641 last_update_login = x_last_update_login,
642 attribute_category = x_attribute_category,
643 attribute1 = x_attribute1,
644 attribute2 = x_attribute2,
645 attribute3 = x_attribute3,
646 attribute4 = x_attribute4,
647 attribute5 = x_attribute5,
648 attribute6 = x_attribute6,
649 attribute7 = x_attribute7,
650 attribute8 = x_attribute8,
651 attribute9 = x_attribute9,
652 attribute10 = x_attribute10,
653 cost_ind_compiled_set_id = x_cost_ind_compiled_set_id,
654 rev_ind_compiled_set_id = x_rev_ind_compiled_set_id,
655 inv_ind_compiled_set_id = x_inv_ind_compiled_set_id,
656 cost_burden_distributed_flag = x_cost_burden_distributed_flag,
657 ind_cost_dist_rejection_code = x_ind_cost_dist_rejection_code,
658 orig_transaction_reference = x_orig_transaction_reference,
659 transaction_source = x_transaction_source,
660 project_id = x_project_id,
661 source_expenditure_item_id = x_source_expenditure_item_id,
662 job_id = x_job_id,
663 system_linkage_function = x_system_linkage_function,
664 receipt_currency_amount = x_receipt_currency_amount,
665 receipt_currency_code = x_receipt_currency_code,
666 receipt_exchange_rate = x_receipt_exchange_rate,
667 denom_currency_code = x_denom_currency_code,
668 denom_raw_cost = x_denom_raw_cost,
669 denom_burdened_cost = x_denom_burdened_cost,
670 acct_exchange_rounding_limit = x_acct_exchange_rounding_limit,
671 acct_currency_code = x_acct_currency_code,
672 acct_rate_date = x_acct_rate_date,
673 acct_rate_type = x_acct_rate_type,
674 acct_exchange_rate = x_acct_exchange_rate,
675 acct_raw_cost = x_acct_raw_cost,
676 acct_burdened_cost = x_acct_burdened_cost,
677 project_currency_code = x_project_currency_code,
678 project_rate_date = x_project_rate_date,
679 project_rate_type = x_project_rate_type,
680 project_exchange_rate = x_project_exchange_rate,
681 recvr_org_id = x_recvr_org_id
682 ,assignment_id = p_assignment_id
683 ,work_type_id = p_work_type_id
684 ,projfunc_currency_code = p_projfunc_currency_code
685 ,projfunc_cost_rate_date = p_projfunc_cost_rate_date
686 ,projfunc_cost_rate_type = p_projfunc_cost_rate_type
687 ,projfunc_cost_exchange_rate = p_projfunc_cost_exchange_rate
688 ,project_raw_cost = p_project_raw_cost
689 ,project_burdened_cost = p_project_burdened_cost
690 ,tp_amt_type_code = p_tp_amt_type_code
691 ,prvdr_accrual_date = p_prvdr_accrual_date
692 ,recvr_accrual_date = p_recvr_accrual_date
693 ,capital_event_id = p_capital_event_id
694 --,agreement_id = p_agreement_id --FSIO Changes
698 -- this assumes the neg quantity has already been validated, and
695 ,cbs_element_id = p_cbs_element_id -- Added as part of bug 16714738
696 where rowid = x_rowid;
697
699 -- is matched. unmatched occurs in adjustments
700 /* Bug 1715308 : Removed the condition of x_quantity < 0
701 if (((x_quantity < 0) or (x_burden_cost < 0)) and
702 (x_net_zero_adjustment_flag = 'Y')) then */
703 if x_net_zero_adjustment_flag = 'Y' then
704 update pa_expenditure_items
705 set net_zero_adjustment_flag = 'Y',
706 last_update_date = x_last_update_date -- Added as part of Bug 12878265
707 where expenditure_item_id = x_adjusted_expenditure_item_id;
708
709 -- Date : 17-JUN-99
710 --
711 -- Earlier the value for parameter expenditure id was NULL. This
712 -- resulted in the reversing related items getting creating in a
713 -- different expenditure id which is different from that of the
714 -- source item. Changed the NULL value to the current expenditure
715 -- id.
716 --
717 pa_adjustments.ReverseRelatedItems(x_adjusted_expenditure_item_id,
718 x_expenditure_id,
719 'PAXTREPE',
720 X_last_updated_by,
721 X_last_update_login,
722 status );
723 --
724 --
725 end if;
726
727 -- if item is released, then need to use the adjust package
728 open c_get_comment;
729 fetch c_get_comment into temp_comment;
730 if (c_get_comment%notfound) then
731 if (x_expenditure_comment is not null) then
732 insert into pa_expenditure_comments (expenditure_item_id,
733 line_number,
734 expenditure_comment,
735 last_update_date,
736 last_updated_by,
737 creation_date,
738 created_by,
739 last_update_login)
740 values (x_expenditure_item_id,
741 1,
742 x_expenditure_comment,
743 x_last_update_date,
744 x_last_updated_by,
745 x_last_update_date, -- is this okay
746 x_last_updated_by,
747 x_last_update_login);
748 end if;
749 else
750 if (x_expenditure_comment is not null) then
751 update pa_expenditure_comments
752 set expenditure_comment = x_expenditure_comment
753 where expenditure_item_id = x_expenditure_item_id;
754 else
755 delete from pa_expenditure_comments
756 where expenditure_item_id = x_expenditure_item_id;
757 end if;
758 end if;
759
760 END update_row;
761
762
763 -- Given the expenditure_item_id, delete the row.
764 -- If deletion of an reversing item occurs, make sure to reset the
765 -- net_zero_adjustment_flag in the reversed item.
766
767 procedure delete_row (x_expenditure_item_id in NUMBER) is
768
769 cursor check_reversing is
770 select adjusted_expenditure_item_id from pa_expenditure_items
771 where expenditure_item_id = x_expenditure_item_id;
772
773 cursor check_source is
774 select expenditure_item_id, adjusted_expenditure_item_id
775 from pa_expenditure_items
776 where source_expenditure_item_id = x_expenditure_item_id;
777
778 rev_item check_reversing%rowtype;
779 source_item check_source%rowtype;
780
781 BEGIN
782
783 -- reset the adjustment flag.
784 open check_reversing;
785 fetch check_reversing into rev_item;
786 if (rev_item.adjusted_expenditure_item_id is not null) then
787 update pa_expenditure_items
788 set net_zero_adjustment_flag = 'N'
789 where expenditure_item_id = rev_item.adjusted_expenditure_item_id;
790
791 open check_source ;
792 --
793 -- Previously the following section which deals with related items was
794 -- done based on the assumption that there can exist only one related
795 -- item. So not suprisingly bug# 912209 was logged which states that
796 -- only one of the related item was getting deleted when the source
797 -- item was deleted. Now the deletion of related items sections is
798 -- called in a loop for each of the related items.
799 --
800 LOOP
801 fetch check_source into source_item ;
802 if check_source%notfound then exit ;
803 end if;
804 fetch check_source into source_item ;
805 if (source_item.adjusted_expenditure_item_id is not null) then
806 update pa_expenditure_items
807 set net_zero_adjustment_flag = 'N'
808 where expenditure_item_id = source_item.adjusted_expenditure_item_id ;
809
810 delete from pa_expenditure_items
811 where expenditure_item_id = source_item.expenditure_item_id;
812 end if ;
813 END LOOP;
814 --
815 -- End section
816 --
817 close check_source ;
818
819 end if;
820
821 -- error checking?
822 delete from pa_expenditure_comments
823 where expenditure_item_id = x_expenditure_item_id;
824
825 delete from pa_expenditure_items
826 where expenditure_item_id = x_expenditure_item_id;
827
828
829 END delete_row;
830
831
832 procedure delete_row (x_rowid in VARCHAR2) is
833
834 cursor get_itemid is select expenditure_item_id from pa_expenditure_items
835 where rowid = x_rowid;
836 x_expenditure_item_id NUMBER;
837
838 BEGIN
839 open get_itemid;
840 fetch get_itemid into x_expenditure_item_id;
841
842 delete_row (x_expenditure_item_id);
843
844 END delete_row;
845
846
847
848 procedure lock_row (x_rowid in VARCHAR2) is
849 BEGIN
850 null;
851 END lock_row;
852
853 END pa_expenditure_items_pkg;