[Home] [Help]
PACKAGE BODY: APPS.GMS_ENC_COPY
Source
1 PACKAGE BODY GMS_ENC_COPY AS
2 /* $Header: GMSTEXCB.pls 115.6 2002/11/26 12:33:17 mmalhotr ship $ */
3
4 PROCEDURE DUMMY -- Dummy procedure to validate package.
5 IS
6 BEGIN
7 NULL;
8 END DUMMY;
9 /*
10 This package is not used...
11
12 dummy NUMBER;
13 org_id NUMBER(15);
14 X_user NUMBER(15);
15 X_module VARCHAR2(30);
16 copy_mode VARCHAR2(1);
17 outcome VARCHAR2(30);
18 X_exp_class_code VARCHAR2(2);
19
20 INVALID_EXPENDITURE EXCEPTION;
21 INVALID_ITEM EXCEPTION;
22
23 --* Bug# 728286
24 --* New exception defined to check whether the new Exp group (in reverseexpgroup proc.)
25 --* already exists in the system or not.
26
27 INVALID_EXP_GROUP EXCEPTION;
28
29
30 function check_reverse_allowed ( net_zero_flag varchar2,
31 related_item number,
32 transferred_item number ) return BOOLEAN ;
33
34 PROCEDURE ValidateEmp ( X_person_id IN NUMBER
35 , X_date IN DATE
36 , X_status OUT NOCOPY VARCHAR2 )
37
38 IS
39
40 BEGIN
41
42 X_status := NULL;
43 org_id := NULL;
44 dummy := NULL;
45
46 org_id := pa_utils.GetEmpOrgId ( X_person_id, X_date );
47
48 IF ( org_id IS NULL ) THEN
49 X_status := 'PA_EX_NO_ORG_ASSGN';
50 RETURN;
51 END IF;
52
53 dummy := NULL;
54 dummy := pa_utils.GetEmpJobId ( X_person_id, X_date );
55
56 IF ( dummy IS NULL ) THEN
57 X_status := 'PA_EX_NO_ASSGN';
58 RETURN;
59 END IF;
60
61 END ValidateEmp;
62
63 PROCEDURE CopyItems ( X_orig_exp_id IN NUMBER
64 , X_new_exp_id IN NUMBER
65 , X_date IN DATE
66 , X_person_id IN NUMBER )
67
68 IS
69
70 temp_outcome VARCHAR2(30) DEFAULT NULL;
71 temp_outcome_type VARCHAR2(1) DEFAULT 'E';
72 temp_msg_application VARCHAR2(50) DEFAULT 'PA';
73 temp_msg_token1 VARCHAR2(240) DEFAULT NULL;
74 temp_msg_token2 VARCHAR2(240) DEFAULT NULL;
75 temp_msg_token3 VARCHAR2(240) DEFAULT NULL;
76 temp_msg_count NUMBER DEFAULT 1;
77 temp_status NUMBER DEFAULT NULL;
78 i BINARY_INTEGER DEFAULT 0;
79
80 CURSOR getEI IS
81 SELECT
82 gms_encumbrance_items_s.nextval encumbrance_item_id
83 , X_new_exp_id encumbrance_id
84 , i.task_id
85 , to_number( NULL ) raw_cost
86 , to_number( NULL ) raw_cost_rate
87 , decode( copy_mode, 'O',
88 next_day((to_date(X_date)-7),
89 to_char(i.encumbrance_item_date, 'DAY')),
90 X_date ) encumbrance_item_date
91 , i.encumbrance_type
92 , i.system_linkage_function
93 , decode( copy_mode, 'S', NULL, i.amount ) amount
94 , t.project_id
95 , t.billable_flag
96 , i.attribute_category
97 , i.attribute1
98 , i.attribute2
99 , i.attribute3
100 , i.attribute4
101 , i.attribute5
102 , i.attribute6
103 , i.attribute7
104 , i.attribute8
105 , i.attribute9
106 , i.attribute10
107 , X_person_id person_id
108 , job_id
109 , i.org_id
110 , i.labor_cost_multiplier_name
111 , i.receipt_currency_amount
112 , i.receipt_currency_code
113 , i.receipt_exchange_rate
114 , i.denom_currency_code
115 , i.denom_raw_cost
116 , i.denom_burdened_cost
117 , i.acct_currency_code
118 , i.acct_rate_date
119 , i.acct_rate_type
120 , i.acct_exchange_rate
121 , i.acct_raw_cost
122 , i.acct_burdened_cost
123 , i.acct_exchange_rounding_limit
124 , i.project_currency_code
125 , i.project_rate_type
126 , i.project_rate_date
127 , i.project_exchange_rate
128 FROM
129 pa_tasks t
130 , gms_encumbrance_items i
131 WHERE
132 ( X_exp_class_code = 'OE'
133 OR i.system_linkage_function = 'ST' )
134 AND i.task_id = t.task_id
135 AND i.encumbrance_id = X_orig_exp_id
136 AND i.adjusted_encumbrance_item_id IS NULL
137 AND nvl(i.net_zero_adjustment_flag, 'N' ) <> 'Y'
138 AND i.source_encumbrance_item_id IS NULL;
139
140
141 PROCEDURE CheckOutcome( X_outcome IN OUT NOCOPY VARCHAR2,
142 X_outcome_type IN VARCHAR2 DEFAULT 'E' )
143
144 IS
145
146 BEGIN
147
148 IF ( X_outcome IS NULL ) THEN
149 RETURN;
150 ELSE
151 IF ( X_outcome_type = 'W' ) THEN
152 RETURN;
153 ELSE
154 outcome := X_outcome;
155 gms_transactions.FlushEiTabs;
156
157 IF ( copy_mode = 'M' ) THEN
158 RAISE INVALID_EXPENDITURE;
159 ELSE
160 RAISE INVALID_ITEM;
161 END IF;
162 END IF;
163 END IF;
164
165 END CheckOutcome;
166
167 BEGIN
168
169 FOR EI IN getEI LOOP
170
171 i := i + 1;
172
173 IF ( X_exp_class_code <> 'PT' ) THEN
174 ValidateEmp ( EI.person_id
175 , EI.encumbrance_item_date
176 , temp_outcome );
177 CheckOutcome ( temp_outcome );
178 END IF;
179
180 IF ( NOT pa_utils.CheckExpTypeActive( EI.encumbrance_type
181 , EI.encumbrance_item_date ) ) THEN
182 temp_outcome := 'PA_TR_EXP_TYPE_INACTIVE';
183 CheckOutcome( temp_outcome );
184 END IF;
185
186 IF ( X_exp_class_code = 'OE' ) THEN
187 EI.raw_cost_rate := pa_utils.GetExpTypeCostRate( EI.encumbrance_type
188 , EI.encumbrance_item_date );
189 EI.raw_cost := PA_CURRENCY.ROUND_CURRENCY_AMT( ( EI.quantity * EI.raw_cost_rate ) );
190 END IF;
191
192 pa_transactions_pub.validate_transaction( X_project_id => EI.project_id
193 , X_task_id => EI.task_id
194 , X_ei_date => EI.encumbrance_item_date
195 , X_encumbrance_type => EI.encumbrance_type
196 , X_non_labor_resource => NULL
197 , X_person_id => X_person_id
198 , X_quantity => EI.quantity
199 , X_denom_currency_code => EI.denom_currency_code
200 , X_acct_currency_code => EI.acct_currency_code
201 , X_denom_raw_cost => EI.denom_raw_cost
202 , X_acct_raw_cost => EI.acct_raw_cost
203 , X_acct_rate_type => EI.acct_rate_type
204 , X_acct_rate_date => EI.acct_rate_date
205 , X_acct_exchange_rate => EI.acct_exchange_rate
206 , X_transfer_ei => NULL
207 , X_incurred_by_org_id => org_id
208 , X_nl_resource_org_id => NULL
209 , X_transaction_source => NULL
210 , X_calling_module => X_module
211 , X_vendor_id => NULL
212 , X_entered_by_user_id => X_user
213 , X_attribute_category => EI.attribute_category
214 , X_attribute1 => EI.attribute1
215 , X_attribute2 => EI.attribute2
216 , X_attribute3 => EI.attribute3
217 , X_attribute4 => EI.attribute4
218 , X_attribute5 => EI.attribute5
219 , X_attribute6 => EI.attribute6
220 , X_attribute7 => EI.attribute7
221 , X_attribute8 => EI.attribute8
222 , X_attribute9 => EI.attribute9
223 , X_attribute10 => EI.attribute10
224 , X_attribute11 => NULL
225 , X_attribute12 => NULL
226 , X_attribute13 => NULL
227 , X_attribute14 => NULL
228 , X_attribute15 => NULL
229 , X_msg_application => temp_msg_application
230 , X_msg_type => temp_outcome_type
231 , X_msg_token1 => temp_msg_token1
232 , X_msg_token2 => temp_msg_token2
233 , X_msg_token3 => temp_msg_token3
234 , X_msg_count => temp_msg_count
235 , X_msg_data => temp_outcome
236 , X_billable_flag => EI.billable_flag);
237
238 CheckOutcome( temp_outcome ,temp_outcome_type);
239
240 -- NO IC Changes, Copy just creates new EI from existing EI's
241 -- So they should be treated as New txns. For any new EI created
242 -- through form we do not derive the ic attributes. Following
243 -- the same approach here, loadei will insert the defaults for
244 -- all IC columns.
245
246 gms_transactions.LoadEi( X_encumbrance_item_id => EI.encumbrance_item_id
247 ,X_encumbrance_id => EI.encumbrance_id
248 ,X_encumbrance_item_date => EI.encumbrance_item_date
249 ,X_project_id => NULL
250 ,X_task_id => EI.task_id
251 ,X_encumbrance_type => EI.encumbrance_type
252 ,X_non_labor_resource => NULL
253 ,X_nl_resource_org_id => NULL
254 ,X_quantity => EI.quantity
255 ,X_raw_cost => NULL
256 ,X_raw_cost_rate => NULL
257 ,X_override_to_org_id => NULL
258 ,X_billable_flag => EI.billable_flag
259 ,X_bill_hold_flag => 'N'
260 ,X_orig_transaction_ref => NULL
261 ,X_transferred_from_ei => NULL
262 ,X_adj_expend_item_id => NULL
263 ,X_attribute_category => EI.attribute_category
264 ,X_attribute1 => EI.attribute1
265 ,X_attribute2 => EI.attribute2
266 ,X_attribute3 => EI.attribute3
267 ,X_attribute4 => EI.attribute4
268 ,X_attribute5 => EI.attribute5
269 ,X_attribute6 => EI.attribute6
270 ,X_attribute7 => EI.attribute7
271 ,X_attribute8 => EI.attribute8
272 ,X_attribute9 => EI.attribute9
273 ,X_attribute10 => EI.attribute10
274 ,X_ei_comment => NULL
275 ,X_transaction_source => NULL
276 ,X_source_exp_item_id => NULL
277 ,i => i
278 ,X_job_id => EI.job_id
279 ,X_org_id => EI.org_id
280 ,X_labor_cost_multiplier_name => EI.labor_cost_multiplier_name
281 ,X_drccid => NULL
282 ,X_crccid => NULL
283 ,X_cdlsr1 => NULL
284 ,X_cdlsr2 => NULL
285 ,X_cdlsr3 => NULL
286 ,X_gldate => NULL
287 ,X_bcost => NULL
288 ,X_bcostrate => NULL
289 ,X_etypeclass => EI.system_linkage_function
290 ,X_burden_sum_dest_run_id => NULL
291 ,X_burden_compile_set_id => NULL
292 ,X_receipt_currency_amount => NULL
293 ,X_receipt_currency_code => EI.receipt_currency_code
294 ,X_receipt_exchange_rate => EI.receipt_exchange_rate
295 ,X_denom_currency_code => EI.denom_currency_code
296 ,X_denom_raw_cost => NULL
297 ,X_denom_burdened_cost => NULL
298 ,X_acct_currency_code => EI.acct_currency_code
299 ,X_acct_rate_date => EI.acct_rate_date
300 ,X_acct_rate_type => EI.acct_rate_type
301 ,X_acct_exchange_rate => EI.acct_exchange_rate
302 ,X_acct_raw_cost => NULL
303 ,X_acct_burdened_cost => NULL
304 ,X_acct_exchange_rounding_limit =>EI.acct_exchange_rounding_limit
305 ,X_project_currency_code => EI.project_currency_code
309 END LOOP;
306 ,X_project_rate_date => EI.project_rate_date
307 ,X_project_rate_type => EI.project_rate_type
308 ,X_project_exchange_rate => EI.project_exchange_rate);
310 gms_transactions.InsItems( X_user => X_user
311 , X_login => NULL
312 , X_module => X_module
313 , X_calling_process => 'EXPEND_COPY'
314 , Rows => i
315 , X_status => temp_status
316 , X_gl_flag => NULL );
317
318 pa_adjustments.CheckStatus( status_indicator => temp_status );
319
320 END CopyItems;
321
322 PROCEDURE preapproved ( copy_option IN VARCHAR2
323 , copy_items IN VARCHAR2
324 , orig_exp_group IN VARCHAR2
325 , new_exp_group IN VARCHAR2
326 , orig_exp_id IN NUMBER
327 , exp_ending_date IN DATE
328 , new_inc_by_person IN NUMBER
329 , userid IN NUMBER
330 , procedure_num_copied IN OUT NOCOPY NUMBER
331 , procedure_num_rejected IN OUT NOCOPY NUMBER
332 , procedure_return_code IN OUT NOCOPY VARCHAR2 )
333
334 IS
335 num_copied NUMBER := 0;
336 num_rejected NUMBER := 0;
337
338 CURSOR getEXP IS
339 SELECT
340 encumbrance_id orig_exp_id
341 , gms_encumbrances_s.nextval new_exp_id
342 , description
343 , nvl( new_inc_by_person, incurred_by_person_id ) person_id
344 , decode( copy_mode, 'S', NULL,
345 decode( copy_items, 'Y', control_total_amount, NULL ))
346 control_total_amount
347 , attribute_category
348 , attribute1
349 , attribute2
350 , attribute3
351 , attribute4
352 , attribute5
353 , attribute6
354 , attribute7
355 , attribute8
356 , attribute9
357 , attribute10
358 , denom_currency_code
359 , acct_currency_code
360 , acct_rate_type
361 , acct_rate_date
362 , acct_exchange_rate
363 FROM
364 gms_encumbrances
365 WHERE
366 encumbrance_group = orig_exp_group
367 AND encumbrance_id = nvl( orig_exp_id, encumbrance_id );
368
369 EXP getEXP%ROWTYPE;
370
371 BEGIN
372
373 copy_mode := copy_option;
374 X_user := userid;
375 X_exp_class_code := 'PT';
376 X_module := 'PAXEXCOP/PAXTEXCB';
377
378 IF ( orig_exp_group = new_exp_group ) THEN
379 outcome := 'PA_EX_SAME_EX';
380 RAISE INVALID_ITEM;
381 END IF;
382
383 OPEN getEXP;
384
385 LOOP
386 FETCH getEXP INTO EXP;
387
388 IF ( getEXP%ROWCOUNT = 0 ) THEN
389 outcome := 'PA_EX_NO_EX';
390 RAISE INVALID_ITEM;
391 END IF;
392
393 EXIT WHEN getEXP%NOTFOUND;
394
395 BEGIN
396 ValidateEmp ( EXP.person_id
397 , exp_ending_date
398 , outcome );
399
400 IF ( outcome IS NOT NULL ) THEN
401 IF ( copy_mode = 'M' ) THEN
402 RAISE INVALID_EXPENDITURE;
403 ELSE
404 RAISE INVALID_ITEM;
405 END IF;
406 END IF;
407
408 IF ( copy_items = 'Y' ) THEN
409 CopyItems ( EXP.orig_exp_id
410 , EXP.new_exp_id
411 , exp_ending_date
412 , EXP.person_id );
413 END IF;
414
415 gms_transactions.InsertExp(
416 X_encumbrance_id => EXP.new_exp_id,
417 X_expend_status => 'SUBMITTED',
418 X_expend_ending => exp_ending_date,
419 X_expend_class => 'PT',
420 X_inc_by_person => EXP.person_id,
421 X_inc_by_org => org_id,
422 X_expend_group => new_exp_group,
423 X_entered_by_id => X_user,
424 X_created_by_id => X_user,
425 X_attribute_category=> EXP.attribute_category,
426 X_attribute1 => EXP.attribute1,
427 X_attribute2 => EXP.attribute2,
428 X_attribute3 => EXP.attribute3,
429 X_attribute4 => EXP.attribute4,
430 X_attribute5 => EXP.attribute5,
434 X_attribute9 => EXP.attribute9,
431 X_attribute6 => EXP.attribute6,
432 X_attribute7 => EXP.attribute7,
433 X_attribute8 => EXP.attribute8,
435 X_attribute10 => EXP.attribute10,
436 X_description => EXP.description,
437 X_control_total => EXP.control_total_amount,
438 X_denom_currency_code => EXP.denom_currency_code,
439 X_acct_currency_code => EXP.acct_currency_code,
440 X_acct_rate_type => EXP.acct_rate_type,
441 X_acct_rate_date => EXP.acct_rate_date,
442 X_acct_exchange_rate=> EXP.acct_exchange_rate);
443
444 num_copied := num_copied + 1;
445
446 -- Copies the attachments for the original encumbrance
447 -- to the newly created encumbrance
448
449 fnd_attached_documents2_pkg.copy_attachments('PA_EXPENDITURES',
450 EXP.orig_exp_id,
451 null,
452 null,
453 null,
454 null,
455 'PA_EXPENDITURES',
456 EXP.new_exp_id,
457 null,
458 null,
459 null,
460 null,
461 FND_GLOBAL.USER_ID,
462 FND_GLOBAL.LOGIN_ID,
463 null,
464 null,
465 null);
466
467 EXCEPTION
468 WHEN INVALID_EXPENDITURE THEN
469 num_rejected := num_rejected + 1;
470 WHEN INVALID_ITEM THEN
471 num_rejected := num_rejected + 1;
472 WHEN OTHERS THEN
473 RAISE;
474
475 END;
476
477 END LOOP;
478
479 CLOSE getEXP;
480
481 procedure_return_code := 'PA_EX_COPY_OUTCOME';
482 procedure_num_copied := num_copied;
483 procedure_num_rejected := num_rejected;
484
485 EXCEPTION
486 WHEN OTHERS THEN
487 RAISE;
488
489 END preapproved;
490
491 PROCEDURE online ( orig_exp_id IN NUMBER
492 , new_exp_id IN NUMBER
493 , exp_ending_date IN DATE
494 , X_inc_by_person IN NUMBER
495 , userid IN NUMBER
496 , procedure_return_code IN OUT NOCOPY VARCHAR2 )
497
498 IS
499
500 BEGIN
501
502 copy_mode := 'O';
503 X_user := userid;
504
505 SELECT
506 e.encumbrance_class_code
507 INTO
508 X_exp_class_code
509 FROM
510 gms_encumbrances e
511 WHERE
512 e.encumbrance_id = orig_exp_id;
513
514 IF ( X_exp_class_code = 'OT' ) THEN
515 X_module := 'PAXEXEER/PAXTEXCB';
516 ELSIF ( X_exp_class_code = 'OE' ) THEN
517 X_module := 'PAXEXTCE/PAXTEXCB';
518 END IF;
519
520 CopyItems ( orig_exp_id
521 , new_exp_id
522 , exp_ending_date
523 , X_inc_by_person );
524
525 EXCEPTION
526 WHEN INVALID_ITEM THEN
527 procedure_return_code := outcome;
528
529 END online;
530
531 --Bug#: 728286
532 --New parameter added: X_expgrp_status (status of the exp group to be created)
533 --All the program/function calls are changed to named parameter method.
534
535
536 PROCEDURE ReverseExpGroup( X_orig_exp_group IN VARCHAR2
537 , X_new_exp_group IN VARCHAR2
538 , X_user_id IN NUMBER
539 , X_module IN VARCHAR2
540 , X_num_reversed IN OUT NOCOPY NUMBER
541 , X_num_rejected IN OUT NOCOPY NUMBER
542 , X_return_code IN OUT NOCOPY VARCHAR2
543 , X_expgrp_status IN VARCHAR2 DEFAULT 'WORKING' )
544 IS
545
546 InsertExp BOOLEAN := TRUE ;
547 InsertBatch BOOLEAN := FALSE ;
548 no_of_items number := 0 ;
549 num_reversed number := 0 ;
550 num_rejected number := 0 ;
551 exp_status varchar2(20);
552
553 CURSOR RevExp is
554 SELECT
558 , e.description
555 e.encumbrance_id orig_exp_id
556 , gms_encumbrances_s.nextval new_exp_id
557 , e.encumbrance_ending_date
559 , e.incurred_by_person_id person_id
560 , e.incurred_by_organization_id inc_by_org_id
561 , e.encumbrance_class_code
562 , e.control_total_amount
563 , e.attribute_category
564 , e.attribute1
565 , e.attribute2
566 , e.attribute3
567 , e.attribute4
568 , e.attribute5
569 , e.attribute6
570 , e.attribute7
571 , e.attribute8
572 , e.attribute9
573 , e.attribute10
574 , e.denom_currency_code
575 , e.acct_currency_code
576 , e.acct_rate_type
577 , e.acct_rate_date
578 , e.acct_exchange_rate
579 FROM
580 gms_encumbrances e
581 WHERE
582 e.encumbrance_group = X_orig_exp_group ;
583
584 cursor RevExpItems(expend_id NUMBER ) is
585 select
586 ei.encumbrance_item_id
587 , ei.net_zero_adjustment_flag
588 , ei.source_encumbrance_item_id
589 , ei.transferred_from_exp_item_id
590 from
591 gms_encumbrance_items_all ei
592 where
593 encumbrance_id = expend_id ;
594
595 cursor ReverseGroup is
596 select
597 encumbrance_group
598 , encumbrance_ending_date
599 , system_linkage_function
600 , control_count
601 , control_total_amount
602 , request_id
603 , program_id
604 , program_application_id
605 , transaction_source
606 from
607 gms_encumbrance_groups
608 where
609 encumbrance_group = X_orig_exp_group ;
610
611 Exp RevExp%rowtype ;
612 ExpEi RevExpItems%rowtype ;
613 ExpGroup ReverseGroup%rowtype ;
614 outcome VARCHAR2(100);
615 Dummy NUMBER;
616
617 BEGIN
618
619 --Bug#: 728286
620 --Check: The new Exp Group already exists in the system or not.
621 --Note: This check is not required when it's called from the Form PAXTREPE because
622 --this validation is already done there.
623
624
625 IF X_module <> 'PAXTREPE' THEN
626 BEGIN
627 SELECT 1
628 INTO Dummy
629 FROM gms_encumbrance_groups
630 WHERE encumbrance_group = X_new_exp_group;
631
632 outcome := 'PA_TR_EPE_GROUP_NOT_UNIQ';
633 RAISE INVALID_EXP_GROUP;
634
635 EXCEPTION
636 WHEN NO_DATA_FOUND THEN
637 NULL;
638 END;
639 END IF;
640
641
642 OPEN RevExp ;
643
644 LOOP
645
646 FETCH RevExp into Exp ;
647
648 IF ( RevExp%ROWCOUNT = 0 ) THEN
649 outcome := 'PA_EX_NO_EX';
650 RAISE INVALID_ITEM;
651 END IF;
652
653 EXIT WHEN RevExp%NOTFOUND;
654
655 InsertExp := TRUE ;
656 no_of_items := 0 ;
657
658 OPEN RevExpItems(Exp.orig_exp_id) ;
659 LOOP
660 Fetch RevExpItems into ExpEi ;
661
662 If ( RevExpItems%ROWCOUNT = 0 ) THEN
663 InsertExp := FALSE ;
664 EXIT ;
665 END IF;
666 EXIT WHEN RevExpItems%NOTFOUND;
667
668 if not check_reverse_allowed( net_zero_flag => ExpEi.net_zero_adjustment_flag,
669 related_item => ExpEi.source_encumbrance_item_id,
670 transferred_item => ExpEi.transferred_from_exp_item_id) then
671 num_rejected := num_rejected + 1 ;
672 else
673
674 pa_adjustments.BackOutItem( X_exp_item_id => ExpEi.encumbrance_item_id,
675 X_encumbrance_id => Exp.new_exp_id,
676 X_adj_activity => 'REVERSE BATCH',
677 X_module => 'PAXTREPE',
678 X_user => x_user_id,
679 X_login => x_user_id,
680 X_status => outcome );
681
682 pa_adjustments.ReverseRelatedItems( X_source_exp_item_id => ExpEi.encumbrance_item_id,
683 X_encumbrance_id => NULL,
684 X_module => 'PAXTREPE',
685 X_user => x_user_id,
686 X_login => x_user_id,
687 X_status => outcome );
688
689 IF outcome <> 0 then
690 num_rejected := num_rejected + 1 ;
691 RAISE INVALID_ITEM ;
692 END IF;
696 end if;
693 no_of_items := no_of_items + 1 ;
694 num_reversed := num_reversed + 1 ;
695
697 END LOOP ;
698
699 CLOSE RevExpItems ;
700 If ( InsertExp ) and (no_of_items > 0) then
701
702 IF X_expgrp_status = 'WORKING' THEN
703 exp_status := 'SUBMITTED';
704 ELSE
705 exp_status := 'APPROVED';
706 END IF;
707
708 gms_transactions.InsertExp( X_encumbrance_id => Exp.new_exp_id,
709 X_expend_status => exp_status,
710 X_expend_ending => Exp.encumbrance_ending_date ,
711 X_expend_class => Exp.encumbrance_class_code ,
712 X_inc_by_person => Exp.person_id ,
713 X_inc_by_org => Exp.inc_by_org_id ,
714 X_expend_group => X_new_exp_group ,
715 X_entered_by_id => X_user_id ,
716 X_created_by_id => X_user_id ,
717 X_attribute_category => Exp.attribute_category ,
718 X_attribute1 => Exp.attribute1 ,
719 X_attribute2 => Exp.attribute2 ,
720 X_attribute3 => Exp.attribute3 ,
721 X_attribute4 => Exp.attribute4 ,
722 X_attribute5 => Exp.attribute5 ,
723 X_attribute6 => Exp.attribute6 ,
724 X_attribute7 => Exp.attribute7 ,
725 X_attribute8 => Exp.attribute8 ,
726 X_attribute9 => Exp.attribute9 ,
727 X_attribute10 => Exp.attribute10 ,
728 X_description => Exp.description ,
729 X_control_total => Exp.control_total_amount,
730 X_denom_currency_code => Exp.denom_currency_code ,
731 X_acct_currency_code => Exp.acct_currency_code ,
732 X_acct_rate_type => Exp.acct_rate_type ,
733 X_acct_rate_date => Exp.acct_rate_date ,
734 X_acct_exchange_rate => Exp.acct_exchange_rate);
735
736 -- Copies the attachments for the original encumbrance
737 -- to the newly created encumbrance
738
739 fnd_attached_documents2_pkg.copy_attachments( X_from_entity_name => 'PA_EXPENDITURES',
740 X_from_pk1_value => Exp.orig_exp_id,
741 X_from_pk2_value => null,
742 X_from_pk3_value => null,
743 X_from_pk4_value => null,
744 X_from_pk5_value => null,
745 X_to_entity_name => 'PA_EXPENDITURES',
746 X_to_pk1_value => Exp.new_exp_id,
747 X_to_pk2_value => null,
748 X_to_pk3_value => null,
749 X_to_pk4_value => null,
750 X_to_pk5_value => null,
751 X_created_by => FND_GLOBAL.USER_ID,
752 X_last_update_login => FND_GLOBAL.LOGIN_ID,
753 X_program_application_id => null,
754 X_program_id => null,
755 X_request_id => null);
756
757 InsertBatch := TRUE ;
758
759 End if ;
760
761 END LOOP ;
762
763 CLOSE RevExp ;
764
765 if ((InsertBatch ) AND (X_module <> 'PAXTREPE')) then
766 OPEN ReverseGroup ;
767 FETCH ReverseGroup into ExpGroup ;
768 if ReverseGroup%notfound then
769 return ;
770 end if;
771
772
773 --Bug#: 728286
774 --The supplied exp_group name is used to create the new Expenditure Group.
775 --The status is set as supplied by the calling program (thru param x_expgrp_status)
776
777 gms_transactions.InsertExpGroup( X_encumbrance_group => X_new_exp_group ,
778 X_exp_group_status_code => X_expgrp_status ,
779 X_ending_date => ExpGroup.encumbrance_ending_date ,
780 X_system_linkage => ExpGroup.system_linkage_function ,
781 X_created_by => X_user_id ,
782 X_transaction_source => ExpGroup.transaction_source );
783 end if;
784
785 if num_reversed <= 0 then
786 outcome := 'PA_NO_ITEMS_FOR_REVERSAL' ;
787 null ;
788 end if;
789
790 X_num_reversed := num_reversed ;
791 X_num_rejected := num_rejected ;
792 X_return_code := outcome ;
793
794 EXCEPTION
795 WHEN INVALID_ITEM THEN
796 X_return_code := outcome;
797
798 --Bug#: 728286
799 --Error handling
800
804 RAISE ;
801 WHEN INVALID_EXP_GROUP THEN
802 X_return_code := outcome;
803 WHEN OTHERS THEN
805
806 End ReverseExpGroup ;
807
808 FUNCTION check_reverse_allowed ( net_zero_flag varchar2,
809 related_item number,
810 transferred_item number ) return BOOLEAN
811 IS
812
813 BEGIN
814
815 if nvl(net_zero_flag, 'N') = 'Y' then
816 return FALSE ;
817 elsif related_item is not null then
818 return FALSE ;
819 elsif transferred_item is not null then
820 return FALSE ;
821 end if;
822
823 return TRUE ;
824
825 END check_reverse_allowed ;
826 */
827
828 END GMS_ENC_COPY;