[Home] [Help]
PACKAGE BODY: APPS.GMS_ENC_ADJUSTMENTS
Source
1 PACKAGE BODY GMS_ENC_ADJUSTMENTS AS
2 /* $Header: gmsencab.pls 120.5 2007/03/13 13:14:02 srachako ship $ */
3
4
5 dummy NUMBER;
6 org_id NUMBER(15);
7 X_user NUMBER(15);
8 X_module VARCHAR2(30);
9 copy_mode VARCHAR2(1);
10 outcome VARCHAR2(30);
11 X_enc_class_code VARCHAR2(2);
12
13 INVALID_EXPENDITURE EXCEPTION;
14 INVALID_ITEM EXCEPTION;
15 SUBROUTINE_ERROR EXCEPTION;
16 INVALID_EXP_GROUP EXCEPTION;
17
18
19 function check_reverse_allowed ( net_zero_flag varchar2,
20 related_item number,
21 transferred_item number ) return BOOLEAN ;
22
23
24
25
26 -- ========================================================================
27 -- PROCEDURE SetNetZero
28 -- ========================================================================
29
30 PROCEDURE SetNetZero( X_enc_item_id IN NUMBER
31 , X_user IN NUMBER
32 , X_login IN NUMBER
33 , X_status OUT NOCOPY NUMBER )
34 IS
35 BEGIN
36 UPDATE gms_encumbrance_items_all ei
37 SET
38 ei.net_zero_adjustment_flag = 'Y'
39 , ei.last_update_date = sysdate
40 , ei.last_updated_by = X_user
41 , ei.last_update_login = X_login
42 WHERE
43 ei.encumbrance_item_id = X_enc_item_id;
44
45 X_status := 0;
46
47 EXCEPTION
48 WHEN OTHERS THEN
49 X_status := SQLCODE;
50 RAISE;
51
52 END SetNetZero;
53
54
55 -- ========================================================================
56 -- PROCEDURE CheckStatus
57 -- ========================================================================
58
59 PROCEDURE CheckStatus( status_indicator IN OUT NOCOPY NUMBER )
60 IS
61 BEGIN
62
63 IF ( status_indicator <> 0 ) THEN
64 RAISE SUBROUTINE_ERROR;
65 ELSIF ( status_indicator = 0 ) THEN
66 status_indicator := NULL;
67 END IF;
68
69 END CheckStatus;
70
71
72 -- =======================================================================
73 -- PROCEDURE BackoutItem
74 -- ========================================================================
75
76 PROCEDURE BackoutItem( X_enc_item_id IN NUMBER
77 , X_encumbrance_id IN NUMBER
78 , X_adj_activity IN VARCHAR2
79 , X_module IN VARCHAR2
80 , X_user IN NUMBER
81 , X_login IN NUMBER
82 , X_status OUT NOCOPY NUMBER )
83 IS
84 X_backout_id NUMBER(15);
85 temp_status NUMBER DEFAULT NULL;
86
87 BEGIN
88 select gms_encumbrance_items_s.nextval
89 into X_backout_id
90 from SYS.dual ;
91 -- X_backout_id := pa_utils.GetNextEiId;
92
93 INSERT INTO gms_encumbrance_items_all(
94 encumbrance_item_id
95 , task_id
96 , project_id --Bug 5726575
97 , encumbrance_type
98 , system_linkage_function
99 , encumbrance_item_date
100 , encumbrance_id
101 , override_to_organization_id
102 , last_update_date
103 , last_updated_by
104 , creation_date
105 , created_by
106 , last_update_login
107 , amount
108 , enc_distributed_flag
109 , adjusted_encumbrance_item_id
110 , net_zero_adjustment_flag
111 , attribute_category
112 , attribute1
113 , attribute2
114 , attribute3
115 , attribute4
116 , attribute5
117 , attribute6
118 , attribute7
119 , attribute8
120 , attribute9
121 , attribute10
122 , transferred_from_enc_item_id
123 , transaction_source
124 , orig_transaction_reference
125 , source_encumbrance_item_id
126 , job_id
127 , org_id
128 , denom_currency_code
129 , denom_raw_amount
130 , acct_currency_code
131 , acct_rate_date
132 , acct_rate_type
133 , acct_exchange_rate
134 , acct_raw_cost
135 , acct_exchange_rounding_limit
136 , project_currency_code
137 , project_rate_date
138 , project_rate_type
139 , project_exchange_rate
140 , denom_tp_currency_code
141 , denom_transfer_price
142 , encumbrance_comment ) /* Added for Bug:5879427 */
143
144 SELECT
145 X_backout_id -- encumbrance_item_id
146 , ei.task_id -- task_id
147 , ei.project_id -- project_id Bug 5726575
148 , ei.encumbrance_type -- encumbrance_type
149 , ei.system_linkage_function -- system_linkage_function
150 , ei.encumbrance_item_date -- encumbrance_item_date
151 , nvl( X_encumbrance_id,
152 ei.encumbrance_id ) -- encumbrance_id
153 , ei.override_to_organization_id -- override enc organization
154 , sysdate -- last_update_date
155 , X_user -- last_updated_by
156 , sysdate -- creation_date
157 , X_user -- created_by
158 , X_login -- last_update_login
159 , (0 - ei.amount) -- quantity
160 , 'N' -- enc_distributed_flag
161 , ei.encumbrance_item_id -- adjusted_encumbrance_item_id
162 , 'Y' -- net_zero_adjustment_flag
163 , ei.attribute_category -- attribute_category
164 , ei.attribute1 -- attribute1
165 , ei.attribute2 -- attribute2
166 , ei.attribute3 -- attribute3
167 , ei.attribute4 -- attribute4
168 , ei.attribute5 -- attribute5
169 , ei.attribute6 -- attribute6
170 , ei.attribute7 -- attribute7
171 , ei.attribute8 -- attribute8
172 , ei.attribute9 -- attribute9
173 , ei.attribute10 -- attribute10
174 , ei.transferred_from_enc_item_id -- tfr from enc item id
175 , ei.transaction_source -- transaction_source
176 , decode(ei.transaction_source,'PTE TIME',NULL,
177 decode(ei.transaction_source,'PTE EXPENSE',NULL,
178 ei.orig_transaction_reference)) -- orig_transaction_reference
179 , ei.source_encumbrance_item_id -- source_encumbrance_item_id
180 , ei.job_id -- job_id
181 , ei.org_id -- org_id
182 , ei.denom_currency_code -- denom_currency_code
183 , (0 - ei.denom_raw_amount) -- denom_raw_amount
184 , ei.acct_currency_code -- acct_currency_code
185 , ei.acct_rate_date -- acct_rate_date
186 , ei.acct_rate_type -- acct_rate_type
187 , ei.acct_exchange_rate -- acct_exchange_rate
188 , (0 - ei.acct_raw_cost) -- acct_raw_cost
189 , ei.acct_exchange_rounding_limit -- acct_exchange_rounding_limit
193 , ei.project_exchange_rate -- project_exchange_rate
190 , ei.project_currency_code -- project_currency_code
191 , ei.project_rate_date -- project_rate_date
192 , ei.project_rate_type -- project_rate_type
194 , ei.denom_tp_currency_code -- denom_tp_currency_code
195 , (0 - ei.denom_transfer_price) -- denom_transfer_price
196 , ei.encumbrance_comment -- encumbrance_comment
197 FROM
198 gms_encumbrance_items_all ei
199
200 WHERE
201 ei.encumbrance_item_id = X_enc_item_id ;
202 /*
203 Project Summarization changes:
204 Store the backout_id in the global variable
205 */
206 gms_enc_adjustments.BackOutId := X_backout_id;
207
208 SetNetZero( X_enc_item_id
209 , X_user
210 , X_login
211 , temp_status );
212 CheckStatus( temp_status );
213
214 X_status := 0;
215
216 EXCEPTION
217 WHEN OTHERS THEN
218 X_status := SQLCODE;
219 RAISE;
220
221 END BackoutItem;
222
223 -- =================================================================================================
224 --
225 -- =================================================================================================
226
227 PROCEDURE ValidateEmp ( X_person_id IN NUMBER
228 , X_date IN DATE
229 , X_status OUT NOCOPY VARCHAR2 )
230
231 IS
232
233 BEGIN
234
235 X_status := NULL;
236 org_id := NULL;
237 dummy := NULL;
238
239 org_id := pa_utils.GetEmpOrgId ( X_person_id, X_date );
240
241 IF ( org_id IS NULL ) THEN
242 X_status := 'PA_EX_NO_ORG_ASSGN';
243 RETURN;
244 END IF;
245
246 dummy := NULL;
247 dummy := pa_utils.GetEmpJobId ( X_person_id, X_date );
248
249 IF ( dummy IS NULL ) THEN
250 X_status := 'PA_EX_NO_ASSGN';
251 RETURN;
252 END IF;
253
254 END ValidateEmp;
255
256 -- ================================================================================================
257 --
258 -- ================================================================================================
259
260 PROCEDURE CopyItems ( X_orig_enc_id IN NUMBER
261 , X_new_enc_id IN NUMBER
262 , X_date IN DATE
263 , X_person_id IN NUMBER )
264
265 IS
266
267 temp_outcome VARCHAR2(30) DEFAULT NULL;
268 temp_outcome_type VARCHAR2(1) DEFAULT 'E';
269 temp_msg_application VARCHAR2(50) DEFAULT 'GMS';
270 temp_msg_token1 VARCHAR2(240) DEFAULT NULL;
271 temp_msg_token2 VARCHAR2(240) DEFAULT NULL;
272 temp_msg_token3 VARCHAR2(240) DEFAULT NULL;
273 temp_msg_count NUMBER DEFAULT 1;
274 temp_status NUMBER DEFAULT NULL;
275 i BINARY_INTEGER DEFAULT 0;
276 P_Award_Id NUMBER ;
277 P_OUTCOME VARCHAR2(1000);
278
279 CURSOR getEI IS
280 -- verified the columns with latest table order
281 SELECT gms_encumbrance_items_s.nextval encumbrance_item_id,
282 i.last_update_date,
283 i.last_updated_by,
284 i.creation_date,
285 i.created_by,
286 X_new_enc_id encumbrance_id ,
287 i.task_id,
288 decode( copy_mode, 'O',
289 --next_day((to_date(X_date)-7), --For bug 3066504
290 next_day((trunc(X_date)-7), --For bug 3066504
291 to_char(i.encumbrance_item_date, 'DAY')),
292 X_date ) encumbrance_item_date,
293 i.encumbrance_type,
294 i.enc_distributed_flag,
295 i.override_to_organization_id,
296 i.adjusted_encumbrance_item_id,
297 i.net_zero_adjustment_flag,
298 i.transferred_from_enc_item_id,
299 i.last_update_login,
300 i.request_id,
301 i.attribute_category,
302 i.attribute1,
303 i.attribute2,
304 i.attribute3,
305 i.attribute4,
306 i.attribute5,
307 i.attribute6,
308 i.attribute7,
309 i.attribute8,
310 i.attribute9,
311 i.attribute10,
312 i.orig_transaction_reference,
313 i.transaction_source,
314 t.project_id,
315 i.source_encumbrance_item_id,
316 i.job_id,
317 i.org_id,
318 i.system_linkage_function,
319 i.denom_currency_code,
320 i.denom_raw_amount,
321 i.acct_currency_code,
325 i.acct_raw_cost,
322 i.acct_rate_date,
323 i.acct_rate_type,
324 i.acct_exchange_rate,
326 i.acct_exchange_rounding_limit,
327 i.project_currency_code,
328 i.project_rate_date,
329 i.project_rate_type,
330 i.project_exchange_rate,
331 i.denom_tp_currency_code,
332 i.denom_transfer_price,
333 decode( copy_mode, 'S', NULL, i.amount ) amount,
334 NULL , -- Fix for Bugno : 1348099
335 X_person_id person_id,
336 i.incurred_by_person_id,
337 i.ind_compiled_set_id,
338 i.pa_date,
339 i.gl_date,
340 i.line_num,
341 i.burden_sum_dest_run_id,
342 i.burden_sum_source_run_id,
343 t.billable_flag
344
345 FROM
346 pa_tasks t
347 ,gms_encumbrance_items i
348 WHERE
349 (X_enc_class_code = 'ER'
350 OR i.system_linkage_function = 'ST' )
351 AND i.task_id = t.task_id
352 AND i.encumbrance_id = X_orig_enc_id
353 AND i.adjusted_encumbrance_item_id IS NULL
354 AND nvl(i.net_zero_adjustment_flag, 'N' ) <> 'Y'
355 AND i.source_encumbrance_item_id IS NULL;
356
357
358 PROCEDURE CheckOutcome( X_outcome IN OUT NOCOPY VARCHAR2,
359 X_outcome_type IN VARCHAR2 DEFAULT 'E' )
360
361 IS
362
363 BEGIN
364
365 IF ( X_outcome IS NULL ) THEN
366 RETURN;
367 ELSE
368 IF ( X_outcome_type = 'W' ) THEN
369 RETURN;
370 ELSE
371 outcome := X_outcome;
372 -- gms_transactions.FlushEiTabs;
373
374 IF ( copy_mode = 'M' ) THEN
375 RAISE INVALID_EXPENDITURE;
376 ELSE
377 -- RAISE INVALID_ITEM;
378 NULL ;
379 END IF;
380 END IF;
381 END IF;
382
383 END CheckOutcome;
384
385 BEGIN
386
387 FOR EI IN getEI LOOP
388
389 Begin
390 select award_id into P_Award_Id
391 from gms_award_distributions adl ,gms_encumbrance_items ei,gms_encumbrances es
392 where adl.expenditure_item_id = ei.encumbrance_item_id
393 and ei.encumbrance_id = es.encumbrance_id
394 and es.encumbrance_id = X_orig_enc_id
395 and adl.document_type = 'ENC'
396 and nvl(adl.reversed_flag, 'N') = 'N' --Bug 5726575
397 and adl.line_num_reversed IS null --Bug 5726575
398 and adl.adl_status = 'A';
399 Exception
400 when too_many_rows then
401 null ;
402 End ;
403
404 i := i + 1;
405
406 IF ( X_enc_class_code <> 'PT' ) THEN
407 ValidateEmp ( EI.person_id
408 , EI.encumbrance_item_date
409 , temp_outcome );
410 CheckOutcome ( temp_outcome );
411 END IF;
412
413 IF ( NOT pa_utils.CheckExpTypeActive( EI.encumbrance_type
414 , EI.encumbrance_item_date ) ) THEN
415 temp_outcome := 'GMS_TR_ENC_TYPE_INACTIVE';
416 CheckOutcome( temp_outcome );
417 END IF;
418
419 -- IF ( X_enc_class_code = 'OE' ) THEN
420 -- EI.raw_cost_rate := pa_utils.GetEncTypeCostRate( EI.encumbrance_type
421 -- , EI.encumbrance_item_date );
422 -- EI.raw_cost := PA_CURRENCY.ROUND_CURRENCY_AMT( ( EI.quantity * EI.raw_cost_rate ) );
423 -- END IF;
424
425 pa_transactions_pub.validate_transaction( X_project_id => EI.project_id
426 , X_task_id => EI.task_id
427 , X_ei_date => EI.encumbrance_item_date
428 , X_expenditure_type => EI.encumbrance_type
429 , X_non_labor_resource => NULL
430 , X_person_id => X_person_id
431 , X_quantity => EI.amount
432 , X_denom_currency_code => EI.denom_currency_code
433 , X_acct_currency_code => EI.acct_currency_code
434 , X_denom_raw_cost => EI.denom_raw_amount
435 , X_acct_raw_cost => EI.acct_raw_cost
436 , X_acct_rate_type => EI.acct_rate_type
437 , X_acct_rate_date => EI.acct_rate_date
438 , X_acct_exchange_rate => EI.acct_exchange_rate
439 , X_transfer_ei => NULL
440 , X_incurred_by_org_id => org_id
441 , X_nl_resource_org_id => NULL
442 , X_transaction_source => NULL
443 , X_calling_module => X_module
444 , X_vendor_id => NULL
445 , X_entered_by_user_id => X_user
446 , X_attribute_category => EI.attribute_category
447 , X_attribute1 => EI.attribute1
448 , X_attribute2 => EI.attribute2
449 , X_attribute3 => EI.attribute3
450 , X_attribute4 => EI.attribute4
451 , X_attribute5 => EI.attribute5
452 , X_attribute6 => EI.attribute6
453 , X_attribute7 => EI.attribute7
454 , X_attribute8 => EI.attribute8
455 , X_attribute9 => EI.attribute9
456 , X_attribute10 => EI.attribute10
457 , X_attribute11 => NULL
458 , X_attribute12 => NULL
459 , X_attribute13 => NULL
460 , X_attribute14 => NULL
461 , X_attribute15 => NULL
462 , X_msg_application => temp_msg_application
463 , X_msg_type => temp_outcome_type
464 , X_msg_token1 => temp_msg_token1
465 , X_msg_token2 => temp_msg_token2
466 , X_msg_token3 => temp_msg_token3
467 , X_msg_count => temp_msg_count
468 , X_msg_data => temp_outcome
469 , X_billable_flag => EI.billable_flag);
470
471 CheckOutcome( temp_outcome ,temp_outcome_type);
472
473
474 gms_transactions_pub.validate_transaction( EI.project_id
475 , EI.task_id
476 , P_award_id
477 , EI.encumbrance_type
478 , EI.encumbrance_item_date
479 , 'EXPEND_COPY'
480 , P_OUTCOME ) ;
481
482 -- verified the columns with getEI order.(latest table order )
483
484 gms_encumbrance_items_pkg.insert_row(
485 x_dummy,
486 EI.encumbrance_item_id,
487 EI.last_update_date,
488 EI.last_updated_by,
489 EI.creation_date,
490 EI.created_by,
491 EI.encumbrance_id ,
492 EI.task_id,
493 EI.encumbrance_item_date,
494 EI.encumbrance_type,
495 -- fix for bug : 2469854
496 -- EI.enc_distributed_flag,
497 'N' ,
498 EI.amount,
499 EI.override_to_organization_id,
500 EI.adjusted_encumbrance_item_id,
501 EI.net_zero_adjustment_flag,
502 EI.transferred_from_enc_item_id,
503 EI.last_update_login,
504 EI.request_id,
505 EI.attribute_category,
506 EI.attribute1,
507 EI.attribute2,
508 EI.attribute3,
509 EI.attribute4,
510 EI.attribute5,
511 EI.attribute6,
512 EI.attribute7,
513 EI.attribute8,
514 EI.attribute9,
515 EI.attribute10,
516 EI.orig_transaction_reference,
517 EI.transaction_source,
518 EI.project_id, --NULL, Bug 5726575
519 EI.source_encumbrance_item_id,
520 EI.job_id,
521 EI.system_linkage_function,
522 EI.denom_currency_code,
523 EI.denom_raw_amount,
527 EI.acct_rate_type,
524 EI.acct_exchange_rounding_limit,
525 EI.acct_currency_code,
526 EI.acct_rate_date,
528 EI.acct_exchange_rate,
529 EI.acct_raw_cost,
530 EI.project_currency_code,
531 EI.project_rate_date,
532 EI.project_rate_type,
533 EI.project_exchange_rate,
534 NULL ,
535 EI.org_id ,
536 EI.denom_tp_currency_code,
537 EI.denom_transfer_price,
538 EI.person_id,
539 EI.incurred_by_person_id,
540 EI.ind_compiled_set_id,
541 EI.pa_date,
542 EI.gl_date,
543 EI.line_num,
544 EI.burden_sum_dest_run_id,
545 EI.burden_sum_source_run_id );
546
547 END LOOP;
548 /* gms_transactions.InsItems( X_user => X_user
549 , X_login => NULL
550 , X_module => X_module
551 , X_calling_process => 'EXPEND_COPY'
552 , Rows => i
553 , X_status => temp_status
554 , X_gl_flag => NULL );
555 */
556
557 pa_adjustments.CheckStatus( status_indicator => temp_status );
558
559 END CopyItems;
560 --------------------------------------------------------------------------------------------
561 PROCEDURE preapproved ( copy_option IN VARCHAR2
562 , copy_items IN VARCHAR2
563 , orig_enc_group IN VARCHAR2
564 , new_enc_group IN VARCHAR2
565 , orig_enc_id IN NUMBER
566 , enc_ending_date IN DATE
567 , new_inc_by_person IN NUMBER
568 , userid IN NUMBER
569 , procedure_num_copied IN OUT NOCOPY NUMBER
570 , procedure_num_rejected IN OUT NOCOPY NUMBER
571 , procedure_return_code IN OUT NOCOPY VARCHAR2 )
572
573 IS
574 num_copied NUMBER := 0;
575 num_rejected NUMBER := 0;
576 new_enc_id NUMBER ;
577 x_orig_enc_id NUMBER ;
578
579 CURSOR getENC IS
580 SELECT encumbrance_id,
581 last_update_date,
582 last_updated_by,
583 creation_date,
584 created_by,
585 encumbrance_status_code,
586 encumbrance_ending_date,
587 encumbrance_class_code,
588 -- incurred_by_person_id,
589 -- nvl( new_inc_by_person, incurred_by_person_id ) person_id,
590 incurred_by_person_id person_id,
591 incurred_by_organization_id,
592 encumbrance_group,
593 -- control_total_amount,
594 decode( copy_mode, 'S', NULL,
595 decode( copy_items, 'Y', control_total_amount, NULL ))
596 control_total_amount,
597 entered_by_person_id,
598 description,
599 initial_submission_date,
600 last_update_login,
601 attribute_category,
602 attribute1,
603 attribute2,
604 attribute3,
605 attribute4,
606 attribute5,
607 attribute6,
608 attribute7,
609 attribute8,
610 attribute9,
611 attribute10,
612 denom_currency_code,
613 acct_currency_code,
614 acct_rate_type,
615 acct_rate_date,
616 acct_exchange_rate,
617 orig_enc_txn_reference1,
618 orig_enc_txn_reference2,
619 orig_enc_txn_reference3,
620 orig_user_enc_txn_reference,
621 vendor_id,
622 org_id
623 FROM
624 gms_encumbrances
625 WHERE
626 encumbrance_group = orig_enc_group
627 AND encumbrance_id = nvl( orig_enc_id, encumbrance_id );
628
629 ENC getENC%ROWTYPE;
630
631 BEGIN
632
633 copy_mode := copy_option;
634 X_user := userid;
635 X_enc_class_code := 'PT';
636 -- X_module := 'PAXEXCOP/GMSTEXCB';
637 X_module := 'GMSTRENE' ;
638
639 IF ( orig_enc_group = new_enc_group ) THEN
640 outcome := 'GMS_EX_SAME_EX';
641 RAISE INVALID_ITEM;
642 END IF;
643
644 OPEN getENC;
645
646 LOOP
647 FETCH getENC INTO ENC;
648
649 IF ( getENC%ROWCOUNT = 0 ) THEN
650 outcome := 'GMS_EX_NO_EX';
651 RAISE INVALID_ITEM;
652 END IF;
653
657
654 EXIT WHEN getENC%NOTFOUND;
655
656 x_orig_enc_id := enc.encumbrance_id ;
658 select gms_encumbrances_s.nextval
659 into new_enc_id
660 from dual;
661
662 BEGIN
663 ValidateEmp ( ENC.person_id
664 , enc_ending_date
665 , outcome );
666
667 IF ( outcome IS NOT NULL ) THEN
668 IF ( copy_mode = 'M' ) THEN
669 RAISE INVALID_EXPENDITURE ;
670 ELSE
671 RAISE INVALID_ITEM;
672 END IF;
673 END IF;
674
675 IF ( copy_items = 'Y' ) THEN
676 CopyItems ( x_orig_enc_id
677 , new_enc_id
678 , enc_ending_date
679 , ENC.person_id );
680 END IF;
681
682 gms_encumbrances_pkg.Insert_row (x_rowid => x_dummy ,
683 x_encumbrance_id => new_enc_id,
684 x_last_update_date => sysdate ,
685 x_last_updated_by => X_user ,
686 x_creation_date => sysdate ,
687 x_created_by => X_user ,
688 x_encumbrance_status_code =>'SUBMITTED',
689 x_encumbrance_ending_date => enc_ending_date ,
690 x_encumbrance_class_code => 'PT' ,
691 x_incurred_by_person_id => ENC.person_id ,
692 x_incurred_by_organization_id => ENC.incurred_by_organization_id ,
693 x_encumbrance_group => new_enc_group ,
694 x_control_total_amount => ENC.control_total_amount ,
695 x_entered_by_person_id => X_user ,
696 x_last_update_login => ENC.last_update_login,
697 x_attribute_category => ENC.attribute_category,
698 x_attribute1 => ENC.attribute1,
699 x_attribute2 => ENC.attribute2,
700 x_attribute3 => ENC.attribute3,
701 x_attribute4 => ENC.attribute4,
702 x_attribute5 => ENC.attribute5,
703 x_attribute6 => ENC.attribute6,
704 x_attribute7 => ENC.attribute7,
705 x_attribute8 => ENC.attribute8,
706 x_attribute9 => ENC.attribute9,
707 x_attribute10 => ENC.attribute10,
708 x_description => ENC.description ,
709 x_denom_currency_code => ENC.denom_currency_code,
710 x_acct_currency_code => ENC.acct_currency_code,
711 x_acct_rate_type => ENC.acct_rate_type,
712 x_acct_rate_date => ENC.acct_rate_date,
713 x_acct_exchange_rate => ENC.acct_exchange_rate,
714 x_orig_enc_txn_reference1 => ENC.orig_enc_txn_reference1,
715 x_orig_enc_txn_reference2 => ENC.orig_enc_txn_reference2,
716 x_orig_enc_txn_reference3 => ENC.orig_enc_txn_reference3,
717 x_orig_user_enc_txn_reference => ENC.orig_user_enc_txn_reference,
718 x_vendor_id => ENC.vendor_id ,
719 x_org_id => ENC.org_id );
720
721 num_copied := num_copied + 1;
722
723 EXCEPTION
724 WHEN INVALID_EXPENDITURE then
725 num_rejected := num_rejected + 1;
726 WHEN INVALID_ITEM THEN
727 num_rejected := num_rejected + 1;
728 WHEN OTHERS THEN
729 RAISE;
730
731 END;
732
733 END LOOP;
734
735 CLOSE getENC;
736
737 procedure_return_code := 'GMS_EN_COPY_OUTCOME';
738 procedure_num_copied := num_copied;
739 procedure_num_rejected := num_rejected;
740
741 EXCEPTION
742 WHEN OTHERS THEN
743 RAISE;
744
745 END preapproved;
746 -- ===========================================================================================================
747 --
748 -- ==========================================================================================================
749 PROCEDURE ReverseEncGroup( X_orig_enc_group IN VARCHAR2
750 , X_new_enc_group IN VARCHAR2
751 , X_user_id IN NUMBER
752 , X_module IN VARCHAR2
753 , X_num_reversed IN OUT NOCOPY NUMBER
754 , X_num_rejected IN OUT NOCOPY NUMBER
755 , X_return_code IN OUT NOCOPY VARCHAR2
756 , X_encgrp_status IN VARCHAR2 DEFAULT 'WORKING' )
757 IS
758
759 InsertEnc BOOLEAN := TRUE ;
760 InsertBatch BOOLEAN := FALSE ;
761 no_of_items number := 0 ;
762 num_reversed number := 0 ;
763 num_rejected number := 0 ;
764 enc_status varchar2(20);
765
766 CURSOR RevEnc is
767 SELECT
768 e.encumbrance_id orig_enc_id
769 , gms_encumbrances_s.nextval new_enc_id
770 , e.encumbrance_ending_date
771 , e.description
772 , e.incurred_by_person_id person_id
773 , e.incurred_by_organization_id inc_by_org_id
774 , e.encumbrance_class_code
775 , e.control_total_amount
776 , e.attribute_category
777 , e.attribute1
778 , e.attribute2
779 , e.attribute3
780 , e.attribute4
781 , e.attribute5
782 , e.attribute6
783 , e.attribute7
784 , e.attribute8
785 , e.attribute9
786 , e.attribute10
787 , e.denom_currency_code
788 , e.acct_currency_code
789 , e.acct_rate_type
790 , e.acct_rate_date
791 , e.acct_exchange_rate
792 , e.org_id
793 FROM
794 gms_encumbrances e
795 WHERE
796 e.encumbrance_group = X_orig_enc_group ;
797
798 cursor RevEncItems(encend_id NUMBER ) is
799 select
800 ei.encumbrance_item_id
801 , ei.net_zero_adjustment_flag
802 , ei.source_encumbrance_item_id
803 , ei.transferred_from_enc_item_id
804 from
805 gms_encumbrance_items_all ei
806 where
807 encumbrance_id = encend_id ;
808
809 cursor ReverseGroup is
810 select
811 encumbrance_group
812 , encumbrance_ending_date
813 , system_linkage_function
814 , control_count
815 , control_total_amount
816 , request_id
817 , program_id
818 , program_application_id
819 , transaction_source
820 , org_id -- fix for bug : 2376730
821 from
822 gms_encumbrance_groups
823 where
824 encumbrance_group = X_orig_enc_group ;
825
826 Enc RevEnc%rowtype ;
827 EncEi RevEncItems%rowtype ;
828 EncGroup ReverseGroup%rowtype ;
829 outcome VARCHAR2(100);
830 Dummy NUMBER;
831
832 BEGIN
833 /*
834 Check: The new Enc Group already exists in the system or not.
835 Note: This check is not required when it's called from the Form GMSTRENE because
836 this validation is already done there.
837 */
838 /*
839 IF X_module <> 'GMSTRENE' THEN
840 BEGIN
841 SELECT 1
842 INTO Dummy
843 FROM gms_encumbrance_groups
844 WHERE encumbrance_group = X_new_enc_group;
845
846 outcome := 'PA_TR_EPE_GROUP_NOT_UNIQ';
847 RAISE INVALID_EXP_GROUP;
848
849 EXCEPTION
850 WHEN NO_DATA_FOUND THEN
851 NULL;
852 END;
853 END IF;
854 */
855
856 OPEN RevEnc ;
857
858 LOOP
859
860 FETCH RevEnc into Enc ;
861
862 IF ( RevEnc%ROWCOUNT = 0 ) THEN
863 outcome := 'GMS_EN_NO_EN';
864 RAISE INVALID_ITEM;
865 END IF;
866
867 EXIT WHEN RevEnc%NOTFOUND;
868
869 InsertEnc := TRUE ;
870 no_of_items := 0 ;
871
872 OPEN RevEncItems(Enc.orig_enc_id) ;
873 LOOP
874 Fetch RevEncItems into EncEi ;
875
876 If ( RevEncItems%ROWCOUNT = 0 ) THEN
877 InsertEnc := FALSE ;
878 EXIT ;
879 END IF;
880 EXIT WHEN RevEncItems%NOTFOUND;
881
882 if not check_reverse_allowed( net_zero_flag => EncEi.net_zero_adjustment_flag,
883 related_item => EncEi.source_encumbrance_item_id,
884 transferred_item => EncEi.transferred_from_enc_item_id) then
885 num_rejected := num_rejected + 1 ;
886 else
887
888 BackOutItem( X_enc_item_id => EncEi.encumbrance_item_id,
889 X_encumbrance_id => Enc.new_enc_id,
890 X_adj_activity => 'REVERSE BATCH',
891 X_module => 'PAXTREPE',
892 X_user => x_user_id,
893 X_login => x_user_id,
894 X_status => outcome );
895 IF outcome <> 0 then
896 num_rejected := num_rejected + 1 ;
897 RAISE INVALID_ITEM ;
898 END IF;
899 no_of_items := no_of_items + 1 ;
900 num_reversed := num_reversed + 1 ;
901
902 end if;
903 END LOOP ;
904
905 CLOSE RevEncItems ;
906 If ( InsertEnc ) and (no_of_items > 0) then
907
908 IF X_encgrp_status = 'WORKING' THEN
909 enc_status := 'SUBMITTED';
910 ELSE
911 enc_status := 'APPROVED';
912 END IF;
913
914 gms_encumbrances_pkg.Insert_row(x_rowid => x_dummy ,
915 X_encumbrance_id => Enc.new_enc_id,
916 X_last_update_date => sysdate ,
917 X_last_updated_by => fnd_global.user_id ,
918 X_creation_date => sysdate ,
919 X_created_by => X_user_id ,
920 X_encumbrance_status_code => enc_status,
921 X_encumbrance_ending_date => Enc.encumbrance_ending_date ,
922 X_encumbrance_class_code => Enc.encumbrance_class_code ,
923 X_incurred_by_person_id => Enc.person_id ,
924 X_incurred_by_organization_id => Enc.inc_by_org_id ,
925 X_encumbrance_group => X_new_enc_group ,
926 X_control_total_amount => Enc.control_total_amount,
927 X_entered_by_person_id => X_user_id ,
928 X_description => Enc.description ,
929 X_attribute_category => Enc.attribute_category ,
930 X_attribute1 => Enc.attribute1 ,
931 X_attribute2 => Enc.attribute2 ,
932 X_attribute3 => Enc.attribute3 ,
933 X_attribute4 => Enc.attribute4 ,
934 X_attribute5 => Enc.attribute5 ,
935 X_attribute6 => Enc.attribute6 ,
936 X_attribute7 => Enc.attribute7 ,
937 X_attribute8 => Enc.attribute8 ,
938 X_attribute9 => Enc.attribute9 ,
939 X_attribute10 => Enc.attribute10 ,
940 X_denom_currency_code => Enc.denom_currency_code ,
941 X_acct_currency_code => Enc.acct_currency_code ,
942 X_acct_rate_type => Enc.acct_rate_type ,
943 X_acct_rate_date => Enc.acct_rate_date ,
944 X_acct_exchange_rate => Enc.acct_exchange_rate,
945 X_org_id => Enc.org_id );
946
947 InsertBatch := TRUE ;
948
949 End if ;
950
951 END LOOP ;
952
953 CLOSE RevEnc ;
954
955 if ((InsertBatch ) AND (X_module <> 'GMSTRENE')) then
956 OPEN ReverseGroup ;
957 FETCH ReverseGroup into EncGroup ;
958 if ReverseGroup%notfound then
959 return ;
960 end if;
961
962 /*
963 Bug#: 728286
964 The supplied enc_group name is used to create the new Encenditure Group.
965 The status is set as supplied by the calling program (thru param x_encgrp_status)
966 */
967
968
969 gms_encumbrance_groups_pkg.insert_row (x_rowid => x_dummy,
970 x_encumbrance_group => X_new_enc_group,
971 x_last_update_date => sysdate,
972 x_last_updated_by => fnd_global.user_id,
973 x_creation_date => sysdate,
974 x_created_by => X_user_id,
975 x_encumbrance_group_status => X_encgrp_status,
976 x_encumbrance_ending_date => EncGroup.encumbrance_ending_date,
977 x_system_linkage_function => EncGroup.system_linkage_function,
978 -- x_control_count => null,
979 -- x_control_total_amount => null,
980 -- x_description => null,
981 -- x_last_update_login => null,
982 x_transaction_source => EncGroup.transaction_source ,
983 x_org_id => encgroup.org_id );
984
985 end if;
986
987 if num_reversed <= 0 then
988 outcome := 'GMS_NO_ITEMS_FOR_REVERSAL' ;
989 null ;
990 end if;
991
992 X_num_reversed := num_reversed ;
993 X_num_rejected := num_rejected ;
994 X_return_code := outcome ;
995
996 EXCEPTION
997 WHEN INVALID_ITEM THEN
998 X_return_code := outcome;
999 WHEN INVALID_EXP_GROUP THEN
1000 X_return_code := outcome;
1001 WHEN OTHERS THEN
1002 RAISE ;
1003
1004 End ReverseEncGroup ;
1005 -- =============================================================================================================
1006 --
1007 -- =============================================================================================================
1008
1009 FUNCTION check_reverse_allowed ( net_zero_flag varchar2,
1010 related_item number,
1011 transferred_item number ) return BOOLEAN
1012 IS
1013
1014 BEGIN
1015
1016 if nvl(net_zero_flag, 'N') = 'Y' then
1017 return FALSE ;
1018 elsif related_item is not null then
1019 return FALSE ;
1020 elsif transferred_item is not null then
1021 return FALSE ;
1022 end if;
1023
1024 return TRUE ;
1025
1026 END check_reverse_allowed ;
1027
1028
1029 -- =======================================================================
1030 -- PROCEDURE revalidate_employee
1031 -- ========================================================================
1032
1033
1034 procedure revalidate_employee ( p_incurred_by_person_id IN NUMBER,
1035 p_week_end_date IN DATE,
1036 x_count OUT NOCOPY NUMBER,
1037 x_org_id OUT NOCOPY NUMBER,
1038 x_org_name OUT NOCOPY VARCHAR2) is
1039
1040
1041 cursor ORG_CUR is
1042 select o.organization_id,
1043 o.name
1044 from pa_employees p,
1045 per_assignments_f a,
1046 hr_organization_units o
1047 where a.person_id = p.person_id
1048 and a.effective_start_date <= p_week_end_date
1049 and nvl(a.effective_end_date,p_week_end_date) >= p_week_end_date - 6
1050 and a.primary_flag = 'Y'
1051 and a.organization_id = o.organization_id
1052 and p.person_id = p_incurred_by_person_id;
1053
1054 l_org_id_tab PA_PLSQL_DATATYPES.Num15TabTyp;
1055 l_org_name_tab PA_PLSQL_DATATYPES.Char240TabTyp;
1056
1057
1058 BEGIN
1059
1060 l_org_id_tab.delete;
1061 l_org_name_tab.delete;
1062
1063 Open ORG_CUR;
1064 Fetch ORG_CUR Bulk Collect INTO
1065 l_org_id_tab,
1066 l_org_name_tab;
1067 Close ORG_CUR;
1068
1069 x_count := l_org_id_tab.count;
1070 If x_count <> 0 then
1071 x_org_id := l_org_id_tab(1);
1072 x_org_name := l_org_name_tab(1);
1073 End If;
1074 EXCEPTION
1075 WHEN OTHERS THEN
1076 raise;
1077
1078 END revalidate_employee;
1079
1080
1081
1082 END GMS_ENC_ADJUSTMENTS ;