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