1 PACKAGE BODY GMS_PA_API AS
2 /* $Header: gmspax1b.pls 120.10 2007/02/06 09:51:09 rshaik ship $ */
3
4
5 -- Assing value "NVL(FND_PROFILE.value('GMS_ENABLE_DEBUG_MODE'), 'N') " to L_DEBUG
6 -- variable in all the procedures where debug messages are to be displayed
7 -- this value cannot be defaulted here as this package is having a procedure
8 -- OVERRIDE_RATE_REV_ID which has a pragma restriction of WNPS.
9
10 -- To check on, whether to print debug messages in log file or not
11 L_DEBUG varchar2(1) ;
12
13 -- The following 2 global variables are for caching the org and gms_enabled status.
14 -- Bug 3002305.
15
16 G_ORG_ID_CHECKED NUMBER := NULL;
17 G_GMS_ENABLED VARCHAR2(1) := NULL;
18
19 -- ------------------------------------------
20 -- declare package variables
21 -- ------------------------------------------
22 G_trx_source pa_transaction_sources.transaction_source%TYPE ;
23 G_GL_accted_flag varchar2(1) ;
24
25 -- ----------------------------------------------------------------------------
26 -- This function returns the error x_error set by the GMS_PA_API.VERT_TRANSFER
27 -- ----------------------------------------------------------------------------
28
29 G_EXPENDITURE_ITEM_ID PA_EXPENDITURE_ITEMS_ALL.EXPENDITURE_ITEM_ID%TYPE := NULL; /* 5490120 */
30 G_AWARD_NUMBER GMS_AWARDS_ALL.AWARD_NUMBER%TYPE := NULL; /* 5490120 */
31
32 FUNCTION return_error return VARCHAR2 IS
33 begin
34 return x_error ;
35 end ;
36
37 --=================================================================================
38 -- Bug 3221039 : The following function is introduced to fetch valid award_id from
39 -- Award Number if present else from award Id .
40 --=================================================================================
41
42 FUNCTION get_award_id (p_award_id NUMBER,
43 p_award_number VARCHAR2) return NUMBER is
44
45 X_award_id NUMBER ;
46
47 CURSOR C_get_award_id IS
48 SELECT ga.award_id
49 FROM gms_awards_all ga
50 WHERE ((p_award_number IS NULL AND ga.award_id = NVL(p_award_id,0)) OR
51 (ga.award_number = p_award_number) );
52 BEGIN
53
54 OPEN C_get_award_id ;
55 FETCH C_get_award_id into x_award_id ;
56 IF C_get_award_id %NOTFOUND THEN
57 x_award_id := 0 ;
58 END IF ;
59 CLOSE C_get_award_id ;
60 RETURN x_award_id ;
61
62 EXCEPTION
63 WHEN OTHERS THEN
64 IF C_get_award_id%ISOPEN THEN
65 close C_get_award_id ;
66 END IF ;
67 pa_cc_utils.log_message('GMS_PA_API.get_award_id : Unexpected error : '||SQLERRM,1);
68 return 0;
69 END get_award_id ;
70
71 -- ========================================================================================================
72 -- This procedure will be called from PAXTRAPE (Expenditure Inquiry ) form when an expenditure_item is SPLIT.
73 -- This will insert a reversed expendtiure_item record and two new expenditure_items records into ADL table.
74 -- =========================================================================================================
75 PROCEDURE GMS_SPLIT (x_expenditure_item_id IN NUMBER ) IS
76
77 adl_rec gms_award_distributions%ROWTYPE;
78 x_flag varchar2(1);
79 x_billable_flag varchar2(1); -- Bug 1756179
80 CURSOR rev_item(x_expenditure_item_id NUMBER ) IS
81 SELECT * from pa_expenditure_items_all
82 WHERE adjusted_expenditure_item_id = x_expenditure_item_id ;
83
84 CURSOR new_item(x_expenditure_item_id NUMBER ) IS
85 SELECT * from pa_expenditure_items_all
86 WHERE transferred_from_exp_item_id = x_expenditure_item_id ;
87
88 BEGIN
89
90 FOR rev_rec IN rev_item(x_expenditure_item_id) LOOP
91
92 begin
93 select DISTINCT award_id -- Fix for bug : 1786003
94 -- , bill_hold_flag -- Don't need to get bill_hold_flag
95 into source_award_id
96 -- ,x_flag
97 from gms_award_distributions adl
98 where adl.expenditure_item_id = x_expenditure_item_id
99 and adl.document_type = 'EXP'
100 and adl_status = 'A' ;
101
102 exception
103 when too_many_rows then
104 Raise ;
105 end ;
106
107 adl_rec.expenditure_item_id := rev_rec.expenditure_item_id;
108 adl_rec.cost_distributed_flag := 'N';
109 adl_rec.project_id := SOURCE_PROJECT_ID;
110 adl_rec.task_id := rev_rec.task_id;
111 adl_rec.cdl_line_num := NULL; -- Bug 1906331
112 adl_rec.adl_line_num := 1;
113 adl_rec.distribution_value := 100;
114 adl_rec.line_type :='R';
115 adl_rec.adl_status := 'A';
116 adl_rec.document_type := 'EXP';
117 adl_rec.billed_flag := 'N';
118 adl_rec.bill_hold_flag := x_flag ;
119 adl_rec.award_set_id := gms_awards_dist_pkg.get_award_set_id;
120 adl_rec.award_id := source_award_id ;
121 adl_rec.raw_cost := rev_rec.raw_cost;
122 adl_rec.last_update_date := rev_rec.last_update_date;
123 adl_rec.creation_date := rev_rec.creation_date;
124 adl_rec.last_updated_by := rev_rec.last_updated_by;
125 adl_rec.created_by := rev_rec.created_by;
126 adl_rec.last_update_login := rev_rec.last_update_login;
127 gms_awards_dist_pkg.create_adls(adl_rec);
128 x_billable_flag := rev_rec.billable_flag; -- Bug 1756179
129 END LOOP;
130
131 FOR new_rec IN new_item (x_expenditure_item_id) LOOP
132 adl_rec.expenditure_item_id := new_rec.expenditure_item_id;
133 adl_rec.project_id := SOURCE_PROJECT_ID;
134 adl_rec.task_id := new_rec.task_id;
135 adl_rec.cost_distributed_flag := 'N';
136 adl_rec.cdl_line_num := NULL; -- Bug 1906331
137 adl_rec.adl_line_num := 1;
138 adl_rec.distribution_value := 100 ;
139 adl_rec.line_type :='R';
140 adl_rec.adl_status := 'A';
141 adl_rec.document_type := 'EXP';
142 adl_rec.billed_flag := 'N';
143 adl_rec.bill_hold_flag := x_flag ;
144 adl_rec.award_set_id := gms_awards_dist_pkg.get_award_set_id;
145 adl_rec.award_id := source_award_id;
146 adl_rec.raw_cost := new_rec.raw_cost;
147 adl_rec.last_update_date := new_rec.last_update_date;
148 adl_rec.creation_date := new_rec.creation_date;
149 adl_rec.last_updated_by := new_rec.last_updated_by;
150 adl_rec.created_by := new_rec.created_by;
151 adl_rec.last_update_login := new_rec.last_update_login;
152 gms_awards_dist_pkg.create_adls(adl_rec);
153 -- Start, Bug 1756179
154 update pa_expenditure_items_all
155 set billable_flag = x_billable_flag
156 where expenditure_item_id = new_rec.expenditure_item_id
157 and exists (select 1 from pa_project_types t, pa_projects pa
158 where pa.project_id = SOURCE_PROJECT_ID
159 and pa.project_type=t.project_type
160 and t.Project_type_class_code= 'INDIRECT');
161 -- End, Bug 1756179
162 END LOOP;
163
164 EXCEPTION
165
166 when others then
167 raise ;
168 END GMS_SPLIT;
169
170 -- ==============================================================================
171
172 FUNCTION GMS_COMP_AWARDS(X_ADJUST_ACTION IN VARCHAR2 ) RETURN VARCHAR2 IS
173
174 BEGIN
175
176 -- Bug 2318298 : Removed Sponsored Project check as it is possible that
177 -- dest project id is sponsored , check only for source_award_id and dest_award_id.
178 -- also added NVL clause
179
180 If NVL(SOURCE_AWARD_ID,-1) = NVL(DEST_AWARD_ID,-2) THEN
181 -- If IS_SPONSORED_PROJECT( source_project_id ) AND SOURCE_AWARD_ID = DEST_AWARD_ID THEN
182 return 'Y';
183 end if;
184 RETURN 'N' ;
185 END GMS_COMP_AWARDS;
186
187 -- ==============================================================================================
188 -- GMS_CHECK_EXP_TYPE() will return TRUE
189 -- if the exp_type is allowed for the allowability_sechdule_id of the dest_award_id
190 -- This function will be called from PAXTRPAE( Expenditure_Inquiry ) while trasnferring to check
191 -- whether the expenditure_item is with in the award end_date.
192 -- ===============================================================================================
193
194 FUNCTION GMS_CHECK_EXP_TYPE (x_expenditure_item_id IN NUMBER ) RETURN BOOLEAN IS
195
196 CURSOR exp_type IS
197 select expenditure_type
198 from gms_allowable_expenditures
199 where allowability_schedule_id = x_allowable_id
200 and expenditure_type = x_expenditure_type;
201
202 BEGIN
203
204
205 -- Bug 2318298 : If dest_award_id is NOT NULL then only perform this action
206 -- Else return true.
207 -- re-arranged the following If statement.
208
209 IF dest_award_id IS NOT NULL THEN
210 OPEN exp_type ;
211 FETCH exp_type INTO x_type ;
212 IF exp_type%FOUND THEN
213 CLOSE exp_type;
214 return TRUE ;
215 END IF;
216 CLOSE exp_type;
217 return FALSE;
218 ELSE
219 return TRUE;
220 END IF;
221
222 EXCEPTION
223 WHEN OTHERS THEN
224 RAISE;
225 END GMS_CHECK_EXP_TYPE ;
226
227 -- =======================================================================================================
228 -- GMS_CHECK_AWARD_DATES
229 -- will return TRUE if the expenditure_item_date is less than or equal to the End_date of the dest_award
230 -- ========================================================================================================
231 FUNCTION GMS_CHECK_AWARD_DATES(x_expenditure_item_id IN NUMBER ,X_message_num IN OUT NOCOPY NUMBER ) RETURN BOOLEAN IS -- Bug 2458518
232
233 -- ===================================================================
234 -- Validationg the source_award_id for the status and closed_date.
235 -- ===================================================================
236 -- Fix start for bug : 2474576
237 CURSOR C_source_award(source_award_id IN NUMBER) IS
238 select status,allowable_schedule_id ,nvl(preaward_date, start_date_active) start_date_active ,
239 end_date_active,close_date
240 from gms_awards_all
241 where award_id = source_award_id ;
242 -- Fix start for bug : 2474576
243
244 CURSOR C_AWARDS(dest_award_id IN NUMBER) IS
245 select status,allowable_schedule_id ,nvl(preaward_date, start_date_active) start_date_active ,
246 end_date_active,close_date
247 from gms_awards_all
248 where award_id = dest_award_id ;
249
250 CURSOR C_EXP(x_expenditure_item_id IN NUMBER ) IS
251 select expenditure_type,expenditure_item_date
252 from pa_expenditure_items_all
253 where expenditure_item_id = x_expenditure_item_id;
254
255 x_start_date DATE; -- Bug 2458518
256 x_close_date DATE; -- Bug 2458518
257 x_award_status VARCHAR2(30) ;
258
259 BEGIN
260
261
262 IF source_award_id IS NOT NULL THEN -- Added for bug 2318298
263 -- Fix start for bug : 2474576
264 OPEN c_source_award(source_award_id ) ;
265 FETCH c_source_award INTO x_award_status ,x_allowable_id,x_start_date , x_end_date , x_close_date ;
266 CLOSE c_source_award ;
267
268 IF x_award_status NOT IN ('ACTIVE', 'AT_RISK' ) THEN
269 X_message_num := 5 ;
270 return FALSE;
271 ELSIF TRUNC (SYSDATE) > x_close_date THEN
272 X_message_num := 6 ;
273 return FALSE;
274 END IF;
275 END IF;
276
277 x_award_status := NULL ;
278 x_allowable_id := NULL ;
279 x_start_date := NULL ;
280 x_end_date := NULL ;
281 x_close_date := NULL ;
282 -- Fix start for bug : 2474576
283
284 IF dest_award_id IS NOT NULL THEN -- Added for bug 2318298
285
286 OPEN c_awards(dest_award_id ) ;
287 FETCH c_awards INTO x_award_status ,x_allowable_id,x_start_date , x_end_date , x_close_date ;
288 CLOSE c_awards ;
289
290
291 OPEN C_EXP (x_expenditure_item_id );
292 FETCH c_exp INTO x_expenditure_type , x_item_date ;
293 CLOSE C_EXP ;
294
295
296 IF TRUNC(SYSDATE) > x_close_date THEN -- Bug 2458518
297 X_message_num := 3 ;
298 return FALSE;
299 ELSIF x_item_date > x_end_date THEN
300 X_message_num := 2 ;
301 return FALSE;
302 ELSIF x_item_date < x_start_date THEN -- Bug 2458518
303 X_message_num := 1 ;
304 return FALSE;
305 END IF;
306
307 END IF;
308
309 return TRUE;
310
311 EXCEPTION
312 WHEN OTHERS THEN
313 RAISE;
314 END GMS_CHECK_AWARD_DATES ;
315
316 -- ==============================================================================
317
318 PROCEDURE GMS_SET_AWARD (X_SOURCE_AWARD_ID IN NUMBER,
319 X_DEST_AWARD_ID IN NUMBER) IS
320
321 BEGIN
322
323 SOURCE_AWARD_ID := X_SOURCE_AWARD_ID;
324 DEST_AWARD_ID := X_DEST_AWARD_ID;
325
326 END GMS_SET_AWARD;
327
328 -- ================================================================================
329 PROCEDURE GMS_SET_PROJECT_ID (X_SOURCE_PROJECT_ID IN NUMBER,
330 X_DEST_PROJECT_ID IN NUMBER) IS
331
332 BEGIN
333
334 SOURCE_PROJECT_ID := X_SOURCE_PROJECT_ID;
335 DEST_PROJECT_ID := X_DEST_PROJECT_ID;
336
337 END GMS_SET_PROJECT_ID;
338
339 -- ------------------------------------------------------------------------------------------------------
340 -- The following function is used to exclude those records which don't belong to the source award_id.
341 -- PA's query will be based on project_id and task_id. So it will get all the records that belong to P1,T1
342 -- and different awards. This function will return TRUE if the record belong to the source_award_id otherwise
343 -- if will return FALSE .
344 -- -------------------------------------------------------------------------------------------------------
345 FUNCTION check_adjust_allowed(x_expenditure_item_id IN NUMBER ) return BOOLEAN IS
346
347 x_exp_item_id NUMBER ;
348
349 CURSOR c1 is select ex.expenditure_item_id
350 from gms_award_distributions adl, pa_expenditure_items_all ex
351 where ex.expenditure_item_id = x_expenditure_item_id
352 and ex.expenditure_item_id = adl.expenditure_item_id
353 and ex.task_id = adl.task_id
354 and adl.award_id = source_award_id
355 and adl.document_type = 'EXP'
356 and adl.adl_status = 'A'
357 and adl.adl_line_num = 1 ;
358 --
359 -- 3628872 NMV View Perf issue was fixed.
360 -- and adl.award_set_id in
361 -- (select award_set_id
362 -- from gms_award_distributions adl
363 -- where award_id = source_award_id);
364 --
365
366 BEGIN
367
368
369 -- Bug 2318298 : Check for source Award Id , if it is NOT NULL then
370 -- only proceed else return TRUE.
371
372 IF source_award_id IS NOT NULL THEN
373 OPEN c1 ;
374 fetch c1 into x_exp_item_id ;
375 IF c1%FOUND THEN
376 CLOSE c1;
377 RETURN TRUE ;
378 END IF;
379 CLOSE c1;
380 RETURN FALSE;
381 ELSE
382 RETURN TRUE;
383 END IF;
384
385 EXCEPTION
386 WHEN NO_DATA_FOUND THEN
387 RAISE ;
388 WHEN OTHERS THEN
389 RAISE;
390 END check_adjust_allowed ;
391
392 -- ================================================================
393 -- API for GMS to determine whether to allow adjustments or
394 -- not. This is being called by PA_Adjustments pkg AND paxeiadj.pll
395 -- ================================================================
396
397 FUNCTION vert_allow_adjustments ( x_expenditure_item_id IN NUMBER ) return BOOLEAN IS
398 BEGIN
399
400 -- Bug 2318298 : In case of Transfer from non sponsored to sponsored Project we shouldn't return FALSE at this stage.
401 -- check for dest project Id.
402
403 IF NOT IS_SPONSORED_PROJECT (SOURCE_PROJECT_ID )
404 AND ( DEST_PROJECT_ID IS NULL
405 OR
406 (
407 DEST_PROJECT_ID IS NOT NULL AND NOT IS_SPONSORED_PROJECT (DEST_PROJECT_ID )
408 )
409 )
410 THEN
411 -- ========================================================
412 -- Since it is NOT a Sponsored project GMS will not interfere
413 -- and let PA continue its process. Fix for bug : 2236328
414 -- ========================================================
415 return FALSE ;
416 END IF ;
417
418 -- ========================================================
419 -- Fix for bug number : 1360895
420 -- Grants will process the records ONLY IF the adjust_action
421 -- is 'PROJECT OR TASK CHANGE' i.e 'TRANSFER', otherwise PA will
422 -- continue its process. Fix for bug : 2236328
423 -- ========================================================
424 If X_adj_action <> ('PROJECT OR TASK CHANGE') then
425 return FALSE ;
426 end if ;
427
428 IF CHECK_ADJUST_ALLOWED(x_expenditure_item_id)
429 AND GMS_CHECK_AWARD_DATES(x_expenditure_item_id,x_message_num) -- Bug 2458518
430 AND GMS_CHECK_EXP_TYPE(x_expenditure_item_id ) THEN
431
432 -- ========================================================
433 -- That means the expenditures are tranferable.
434 -- ========================================================
435
436 return FALSE ;
437
438 ELSE
439 -- =================================================================
440 -- That means the expenditures failed validation and NOT tranferable.
441 -- =================================================================
442 return TRUE;
443 END IF;
444
445 END vert_allow_adjustments ;
446
447
448
449
450 -- -----------------------------------------------------------
451 -- API for GMS to determine whether to allow Transfer or not
452 -- -----------------------------------------------------------
453 FUNCTION vert_transfer (x_exp_id IN NUMBER ,
454 x_status IN OUT NOCOPY VARCHAR2 ) RETURN BOOLEAN IS
455 begin
456
457
458 -- Bug 2318298 : If source or Destination Project is a sponsored project then proceed
459 -- verify this using source_award_id and dest_award_id
460
461 -- If IS_SPONSORED_PROJECT (SOURCE_PROJECT_ID)
462 If source_award_id IS NOT NULL OR dest_award_id IS NOT NULL then
463 /* Bug 5436420 - Removed call to GMS_COMP_AWARDS */
464 If NOT GMS_CHECK_AWARD_DATES(x_exp_id ,x_message_num) then
465 -- BUg 2458518
466 IF x_message_num = 1 THEN
467 x_status := 'GMS_TR_EXP_DATE_AWARD_ST_DATE' ;
468 x_error := x_status ;
469 ELSIF x_message_num = 2 THEN
470 x_status := 'GMS_TR_EXP_DATE_AWARD_DATE' ;
471 x_error := x_status ;
472 ELSIF x_message_num = 3 THEN
473 x_status := 'GMS_TR_AWARD_IS_CLOSED' ;
474 x_error := x_status ;
475 -- Fix start for bug : 2474576
476 ELSIF x_message_num = 5 THEN
477 x_status := 'GMS_TR_SOURCE_AWD_NOT_ACTIVE' ;
478 x_error := x_status ;
479 ELSIF x_message_num = 6 THEN
480 x_status := 'GMS_TR_SOURCE_AWD_IS_CLOSED' ;
481 x_error := x_status ;
482 -- Fix end for bug : 2474576
483 END IF; -- end if for x_message_num = 1
484 return FALSE ;
485
486 Elsif NOT GMS_CHECK_EXP_TYPE(x_exp_id) then
487 x_status := 'GMS_TR_DEST_EXP_TYPE_INVALID' ;
488 x_error := x_status ;
489 return FALSE ;
490
491 ELSE
492 return TRUE ;
493 x_error := x_status ;
494 end if ; -- End if for second IF
495 Else -- Else for IS _SPONSORED_PROJECT
496 return FALSE ;
497 End if;
498
499 END vert_transfer ;
500
501
502 FUNCTION IS_SPONSORED_PROJECT ( X_project_id IN NUMBER ) return BOOLEAN IS
503
504 x_dummy varchar2(1) ;
505 x_return BOOLEAN ;
506
507 CURSOR C_SPONSORED IS
508 select 'X'
509 FROM pa_projects_all P,
510 gms_project_types gpt
511 WHERE p.project_id = X_project_id
512 AND p.project_type = gpt.project_type
513 and gpt.sponsored_flag = 'Y' ;
514 begin
515
516 x_return := FALSE ;
517
518 OPEN C_SPONSORED ;
519 FETCH C_SPONSORED into x_dummy ;
520
521 IF C_SPONSORED%FOUND THEN
522
523 x_return := TRUE ;
524 END IF ;
525
526 CLOSE C_SPONSORED ;
527
528 RETURN x_return ;
529 exception
530 When others then
531 Raise ;
532
533 END IS_SPONSORED_PROJECT ;
534
535 -- ======================================================================================================================
536 -- This proceudre is called from PAXTRANB.pls while the expenditure_ites are transferred or MassAdjusted.
537 -- For TRASFER x_rows will be 1 since expenditure_items are loaded into LaoadEi record by record and
538 -- transferred one at a time. Where as while MassAdjusting all the expenditures_items are loaded into LoadEi at one shot
539 -- =======================================================================================================================
540 PROCEDURE vert_ADJUST_ITEMS( X_CALLING_PROCESS IN VARCHAR2 ,
541 X_ROWS IN NUMBER,
542 X_status IN OUT NOCOPY NUMBER ) IS
543
544 adl_rec gms_award_distributions%ROWTYPE;
545 x_exp_item_id NUMBER;
546 x_new_item_id NUMBER;
547 x_flag varchar2(1) ;
548 CURSOR rev_item(x_exp_item_id NUMBER ) IS
549 SELECT * from pa_expenditure_items_all
550 WHERE adjusted_expenditure_item_id = x_exp_item_id ;
551
552 CURSOR new_item(x_exp_item_id NUMBER ) IS
553 SELECT * from pa_expenditure_items_all
554 WHERE transferred_from_exp_item_id = x_exp_item_id ;
555
556
557 BEGIN
558 IF X_STATUS is NOT NULL THEN
559
560 -- Bug 2318298 : Modified the If statement, execute the code only
561 -- either the source or destination project is a sponsored project
562
563 IF X_CALLING_PROCESS IN ( 'TRANSFER')
564 -- AND IS_SPONSORED_PROJECT (SOURCE_PROJECT_ID ) THEN
565 AND (source_award_id IS NOT NULL OR dest_award_id IS NOT NULL) THEN
566
567 FOR i IN 1..X_ROWS LOOP
568
569 x_exp_item_id := PA_TRANSACTIONS.TfrEiTab(i);
570
571 -- Don't need to store bill_hold_flag in ADL table as Billing process will retrieve this
572 -- value from pa_expenditure_items_all table.
573
574 /*begin
575 end ;*/
576
577 -- Bug 2318298 : If source_award_id is NOT NULL then create ADL
578
579 IF source_award_id IS NOT NULL THEN
580 FOR rev_rec IN rev_item(x_exp_item_id) LOOP
581 adl_rec.expenditure_item_id := rev_rec.expenditure_item_id;
582 adl_rec.cost_distributed_flag := 'N';
583 adl_rec.project_id := SOURCE_PROJECT_ID;
584 adl_rec.task_id := rev_rec.task_id;
585 adl_rec.cdl_line_num := NULL; -- Bug 1906331
586 adl_rec.adl_line_num := 1;
587 adl_rec.distribution_value := 100;
588 adl_rec.line_type :='R';
589 adl_rec.adl_status := 'A';
590 adl_rec.document_type := 'EXP';
591 adl_rec.billed_flag := 'N';
592 adl_rec.bill_hold_flag := x_flag ;
593 adl_rec.award_set_id := gms_awards_dist_pkg.get_award_set_id;
594 adl_rec.award_id := SOURCE_AWARD_ID;
595 adl_rec.raw_cost := rev_rec.raw_cost;
596 adl_rec.last_update_date := rev_rec.last_update_date;
597 adl_rec.creation_date := rev_rec.creation_date;
598 adl_rec.last_updated_by := rev_rec.last_updated_by;
599 adl_rec.created_by := rev_rec.created_by;
600 adl_rec.last_update_login := rev_rec.last_update_login;
601 gms_awards_dist_pkg.create_adls(adl_rec);
602 END LOOP;
603 END IF;
604
605
606 -- Bug 2318298 : If dest_award_id is NOT NULL then create ADL
607
608 IF dest_award_id IS NOT NULL THEN
609
610 FOR new_rec IN new_item (x_exp_item_id) LOOP
611 adl_rec.expenditure_item_id := new_rec.expenditure_item_id;
612 adl_rec.project_id := DEST_PROJECT_ID;
613 adl_rec.task_id := new_rec.task_id;
614 adl_rec.cost_distributed_flag := 'N';
615 adl_rec.cdl_line_num := NULL; -- Bug 1906331
616 adl_rec.adl_line_num := 1;
617 adl_rec.distribution_value := 100;
618 adl_rec.line_type :='R';
619 adl_rec.adl_status := 'A';
620 adl_rec.document_type := 'EXP';
621 adl_rec.billed_flag := 'N';
622 adl_rec.bill_hold_flag := x_flag ;
623 adl_rec.award_set_id := gms_awards_dist_pkg.get_award_set_id;
624 adl_rec.award_id := DEST_AWARD_ID;
625 adl_rec.raw_cost := new_rec.raw_cost;
626 adl_rec.last_update_date := new_rec.last_update_date;
627 adl_rec.creation_date := new_rec.creation_date;
628 adl_rec.last_updated_by := new_rec.last_updated_by;
629 adl_rec.created_by := new_rec.created_by;
630 adl_rec.last_update_login := new_rec.last_update_login;
631 gms_awards_dist_pkg.create_adls(adl_rec);
632 END LOOP;
633 END IF;
634
635 END LOOP;
636
637 END IF;
638 END IF ;
639 -- These global variables are reset for every record by having the code hool before ON-UPDATE event in the pacage
640 -- paeia_transfer of PAXEIADJ.pll file
641
642 SOURCE_AWARD_ID := '';
643 DEST_AWARD_ID := '';
644 SOURCE_PROJECT_ID := '';
645 DEST_PROJECT_ID := '';
646
647 EXCEPTION
648
649 when others then
650 RAISE;
651
652
653 END vert_ADJUST_ITEMS ;
654
655 -- ------------------------------------------------------
656 -- API to allow vertical application to compare awards
657 -- X_ADJUST_ACTION = 'MASADJUST'
658 -- -------------------------------------------------------
659 FUNCTION VERT_ALLOW_ACTION(X_ADJUST_ACTION IN VARCHAR2) RETURN VARCHAR2 IS
660
661 BEGIN
662 -- ------------------------------------------------
663 -- Vertical application will override the code here.
664 -- -------------------------------------------------
665
666 -- =====================================================================
667 -- The control will come here only if the source and dest tasks ARE SAME.
668 -- If they are DIFFERENT PA will NOT call this function.
669 -- If the Source Project is not SPONSORED PROJECT GMS will not interfere.
670 -- =====================================================================
671
672 -- Bug 2318298 : Added the check for dest_project_id , If source and destination
673 -- project both are non sponsored
674 -- don't use source_award_id and dest_award_id here as this procedure
675 -- is called in other adjustments also (apart from TRANSFER)
676
677 If NOT IS_SPONSORED_PROJECT(source_project_id )
678 AND NOT IS_SPONSORED_PROJECT(dest_project_id )
679 then
680 return 'N' ;
681 end if ;
682
683 -- Fix for bug number : 1360895
684 -- ======================================================================================
685 -- If the adjust_action is NOT 'PROJECT OR TASK CHANGE', GMS will not process the records.
686 -- ======================================================================================
687
688 If X_adj_action <> ('PROJECT OR TASK CHANGE') then
689 return 'Y'; -- Let PA Continue its Action
690 end if ;
691
692 -- ============================================================================
693 -- If source and dest award ids are same , Grants will NOT transfer the records.
694 -- ============================================================================
695
696 If source_award_id = dest_award_id then
697 return 'N' ;
698 End if ;
699
700 return 'Y'; -- Let PA Continue its Action
701
702 END VERT_ALLOW_ACTION ;
703
704 -- ----------------------------------------------------------
705 -- Supplier Invoice Interface logic of creating ADLS.
706 -- LD PA Interface logic of creating ADLS.
707 -- trx_interface - Creates ADLS for the new expenditure items
708 -- created for PA Interface from payables/LD.
709 -- This is called after PA_TRX_IMPORT.NEWexpend.
710 -- -----------------------------------------------------------
711 PROCEDURE vert_trx_interface( X_user IN NUMBER
712 , X_login IN NUMBER
713 , X_module IN VARCHAR2
714 , X_calling_process IN VARCHAR2
715 , Rows IN BINARY_INTEGER
716 , X_status IN OUT NOCOPY NUMBER
717 , X_GL_FLAG IN VARCHAR2 ) IS
718 -- ---------------------
719 -- Variable declaration.
720 -- ---------------------
721 temp_status NUMBER DEFAULT NULL;
722 x_request_id NUMBER(15);
723 x_program_application_id NUMBER(15);
724 x_program_id NUMBER(15);
725 x_invoice_id NUMBER ;
726 x_project_id NUMBER ;
727 X_CDL_NUM NUMBER ;
728 x_task_id NUMBER ;
729 X_raw_cost NUMBER ;
730 X_dist_lno NUMBER ;
731 x_award_set_id NUMBER ;
732 X_ei_id NUMBER ;
733 x_exp_id NUMBER ;
734 x_exp_item_date DATE ;
735 X_ind_cmpl_set_id NUMBER ;
736 X_org_id NUMBER ;
737 X_burden_award_id NUMBER ;
738 x_ind_compiled_set_id NUMBER ;
739 X_packet_id NUMBER := 0;
740 X_costed_flag varchar2(1) ;
741 x_revenued_flag varchar2(1) ;
742 x_bill_hold_flag varchar2(1) ;
743 X_trx_src varchar2(30) ;
744 x_temp varchar2(30) ;
745 x_billable_flag varchar2(1) ;
746 x_err_code NUMBER(7) DEFAULT 0 ;
747 x_err_buff varchar2(2000) ;
748 x_err_stage VARCHAR2(255) ;
749 x_err_stack VARCHAR2(255) ;
750 x_sob_id NUMBER ;
751 x_exp_org_id NUMBER ;
752 X_purgeable VARCHAR2(1) ;
753
754 x_adl_rec gms_award_distributions%ROWTYPE ;
755
756 -- New variables for performance :
757
758 v_trx_src varchar2(30) := 'DUMMY';
759 v_gl_accounted varchar2(1);
760
761 -- ----------------------------
762 -- CURSOR Declaration. AP-XFACE
763 -- ----------------------------
764 /** AP Lines uptake: C_APREC is no longer needed because PROC_SI_INTERFACE is obsolete
765 CURSOR C_APREC is
766 -- -----------------------------------------------------------
767 -- Bug 2143160. Joined ap_invoices_all to get vendor_id
768 -- -----------------------------------------------------------
769 AP Lines uptake: C_APREC is no longer needed because PROC_SI_INTERFACE is obsolete **/
770 -- -------------------------------
771 -- CURSOR declaration. LD-XFACE.
772 -- -------------------------------
773 -- bug : 3684711 UNABLE TO ENTER A REVERSAL BATCH GMS_AWARD_REQD
774 CURSOR C_GOLD IS
775 SELECT gt.award_id award_id,
776 gt.award_number award_number, -- Bug 3221039
777 NULL invoice_distribution_id,
778 ei.cost_distributed_flag cost_distributed_flag,
779 ei.revenue_distributed_flag revenue_distributed_flag,
780 pt.txn_interface_id TXN_INTERFACE_ID,
781 pt.accrual_flag period_end_accrual_flag,
782 pt.system_linkage system_linkage
783 FROM gms_transaction_interface_all gt,
784 pa_transaction_interface_all pt,
785 pa_expenditure_items_all ei
786 WHERE ei.expenditure_item_id = x_ei_id
787 AND ei.expenditure_id = x_exp_id
788 AND ei.transaction_source = x_trx_src
789 and ei.transaction_source = pt.transaction_source
790 and ei.orig_transaction_reference = pt.orig_transaction_reference
791 and ei.expenditure_id = pt.expenditure_id
792 and ei.expenditure_item_id = pt.expenditure_item_id
793 and pt.txn_interface_id = gt.txn_interface_id;
794 -- -----------------------------
795 -- EXISTING ADL RECORD.
796 -- -----------------------------
797 CURSOR C_adlrec is
798 SELECT * from gms_award_distributions
799 where award_set_id = x_award_set_id
800 and adl_status = 'A' ;
801 -- ----------------------------------
802 -- GET max CDL line NUM.
803 -- ----------------------------------
804 CURSOR C_CDL_NUM is
805 SELECT cdl.line_num
806 FROM pa_cost_distribution_lines cdl
807 WHERE cdl.expenditure_item_id = X_ei_id
808 and line_num_reversed is null
809 and reversed_flag is NULL ;
810
811 CURSOR C_TXN_SOURCE IS
812 SELECT nvl(purgeable_flag, 'N'), nvl(gl_accounted_flag, 'N')
813 FROM pa_transaction_sources
814 WHERE transaction_source = x_trx_src ;
815
816 -- ---------------------------------------
817 -- PROCEDURE PROC_BURDEN_INTERFACE
818 -- Local procedure to create ADLS for BURDEN
819 -- Interface. This guy looks for award
820 -- details from attribute1.
821 -- ---------------------------------------
822 PROCEDURE PROC_BURDEN_INTERFACE(P_award_id number) IS
823 X_TXN_XFACE_ID NUMBER ;
824 X_award_id NUMBER ;
825 invalid_award EXCEPTION ; -- Bug 2368907
826 BEGIN
827 IF nvl(p_award_id,0) = 0 THEN
828 RAISE invalid_award ; -- Bug 2368907, Added
829 -- return ; -- Bug 2368907, Commented
830 END IF ;
831
832 x_award_set_id := gms_awards_dist_pkg.get_award_set_id ;
833 X_award_id := P_AWARD_ID ;
834 x_adl_rec.award_set_id := x_award_set_id ;
835 X_adl_rec.adl_line_num := 1 ;
836 X_adl_rec.project_id := X_project_id ;
837 X_adl_rec.document_type := 'EXP' ;
838 X_adl_rec.task_id := X_task_id ;
839 X_adl_rec.award_id := X_award_id ;
840 x_adl_rec.expenditure_item_id := x_ei_id ;
841 x_adl_rec.raw_cost := X_raw_cost ;
842 x_adl_rec.request_id := X_request_id ;
843 x_adl_rec.CDL_line_num := nvl(x_cdl_num,1) ; -- Bug 2368907, Added nvl fn to default 1 for BTC lines
844 x_adl_rec.billable_flag := x_billable_flag ;
845 x_adl_rec.billed_flag := 'N' ;
846 X_adl_rec.Ind_compiled_set_id := X_ind_cmpl_set_id ;
847 X_adl_rec.bill_hold_flag := X_bill_hold_flag ;
848 X_adl_rec.cost_distributed_flag := x_costed_flag ;
849 X_adl_rec.revenue_distributed_flag := X_revenued_flag ;
850 x_adl_rec.invoice_id := NULL ;
851 x_adl_rec.invoice_distribution_id := NULL ;
852 x_adl_rec.distribution_line_number := NULL ;
853 X_adl_rec.adl_status := 'A' ;
854 X_adl_rec.line_type := 'B' ;
855
856 /* Commenting out NOCOPY the code below as it is not relevant here... bug 2596697
857 */ -- Commented out NOCOPY the code above as it is irrelevant. Bug 2596697
858
859 IF L_DEBUG = 'Y' THEN
860 gms_error_pkg.gms_debug('GMS: Before call to gms_awards_dist_pkg.create_adl', 'C');
861 END IF;
862 gms_awards_dist_pkg.create_adls(x_adl_rec) ;
863
864 IF L_DEBUG = 'Y' THEN
865 gms_error_pkg.gms_debug('GMS: After call to gms_awards_dist_pkg.create_adl', 'C');
866 END IF;
867
868 EXCEPTION
869 WHEN invalid_award THEN -- Bug 2368907, Added
870 IF L_DEBUG = 'Y' THEN
871 gms_error_pkg.gms_debug('GMS:PROC_BURDEN_INTERFACE did not process. Parameter p_award_id was NULL or 0', 'C');
872 END IF;
873 WHEN OTHERS THEN
874 RAISE ;
875 END PROC_BURDEN_INTERFACE ;
876 -- =========== END OF PROC_BURDEN_INTERFACE ==============
877
878 -- ---------------------------------------
879 -- PROCEDURE PROC_LDPA_INTERFACE
880 -- Local procedure to create ADLS for LD
881 -- Interface. This guy looks for award
882 -- details from GMS_interface.
883 -- ---------------------------------------
884 PROCEDURE PROC_LDPA_INTERFACE IS
885 X_TXN_XFACE_ID NUMBER ;
886 X_award_id NUMBER ;
887 BEGIN
888 FOR LD_REC IN C_GOLD LOOP
889 x_award_set_id := gms_awards_dist_pkg.get_award_set_id ;
890 x_txn_xface_id := LD_REC.TXN_INTERFACE_ID ;
891 X_award_id := get_award_id( LD_REC.AWARD_ID, LD_REC.AWARD_NUMBER);
892 --LD_REC.AWARD_ID ; -- Bug 3221039
893 x_adl_rec.award_set_id := x_award_set_id ;
894 X_adl_rec.adl_line_num := 1 ;
895 X_adl_rec.project_id := X_project_id ;
896 X_adl_rec.document_type := 'EXP' ;
897 X_adl_rec.task_id := X_task_id ;
898 X_adl_rec.award_id := X_award_id ;
899 x_adl_rec.expenditure_item_id := x_ei_id ;
900 x_adl_rec.raw_cost := X_raw_cost ;
901 x_adl_rec.request_id := X_request_id ;
902 x_adl_rec.CDL_line_num := x_cdl_num ;
903 x_adl_rec.billable_flag := x_billable_flag ;
904 x_adl_rec.billed_flag := 'N' ;
905 X_adl_rec.Ind_compiled_set_id := X_ind_cmpl_set_id ;
906 X_adl_rec.Ind_compiled_set_id := X_ind_cmpl_set_id ;
907 X_adl_rec.bill_hold_flag := X_bill_hold_flag ;
908 X_adl_rec.cost_distributed_flag := x_costed_flag ;
909 X_adl_rec.revenue_distributed_flag := X_revenued_flag ;
910 x_adl_rec.invoice_id := NULL ;
911 x_adl_rec.invoice_distribution_id := NULL ;
912 x_adl_rec.distribution_line_number := NULL ;
913 X_adl_rec.adl_status := 'A' ;
914 X_adl_rec.line_type := 'R' ;
915
916 /* Commenting the whole code below as it is not relevant for txns coming from LD distributions
917 **/ -- Commented all the above code as it is irrelevant bug 2596697
918
919 gms_awards_dist_pkg.create_adls(x_adl_rec) ;
920
921 -- bug : 3684711 UNABLE TO ENTER A REVERSAL BATCH GMS_AWARD_REQD
922 -- Create award distribution line for the reversal item.
923 IF ld_rec.period_end_accrual_flag = 'Y' and
924 ld_rec.system_linkage = 'PJ' then
925
926 select ei.expenditure_item_id
927 into x_adl_rec.expenditure_item_id
928 from pa_expenditure_items_all ei
929 where ei.adjusted_expenditure_item_id = x_ei_id ;
930 IF SQL%FOUND THEN
931 x_adl_rec.raw_cost := X_raw_cost * -1 ;
932 x_award_set_id := gms_awards_dist_pkg.get_award_set_id ;
933 x_adl_rec.award_set_id := x_award_set_id ;
934 gms_awards_dist_pkg.create_adls(x_adl_rec) ;
935 END IF ;
936
937 END IF ;
938 -- end of bug : 3684711 Fix.
939
940 IF NVL(x_purgeable,'N') = 'Y' THEN
941 DELETE from gms_transaction_interface_all
942 WHERE txn_interface_id = x_txn_xface_id ;
943 END IF ;
944
945
946 END LOOP ;
947 EXCEPTION
948 WHEN OTHERS THEN
949 RAISE ;
950 END PROC_LDPA_INTERFACE ;
951 -- =========== END OF PROC_LDPA_INTERFACE ==============
952 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++
953
954 -- -------------------------------------------------------
955 -- PROCEDURE PROC_SI_INTERFACE
956 -- This guy copy the adls for each invoice distribution
957 -- lines generated into expenditure items and establish a
958 -- link with expenditure item.
959 -- -------------------------------------------------------
960 -- Start of comments
961 -- API name : PROC_SI_INTERFACE
962 -- Bug : 2569522
963 -- ENHANCE GRANTS ACCOUNTING TO WORK WITH PA I PAYABLES
964 -- DISCOUNTS FEATURE
965 -- Type : Private
966 -- Pre-reqs : None.
967 -- Function : Interface Supplier Invoices, discounts and
968 -- pre-payments to grants accounting.
969 -- Logic : Discounts and Pre Payments.
970 -- Interfaced to GA with cost distributed flag N
971 -- and line type F for CDLs.
972 -- Invoice Items not FC
973 -- Interfaced to GA with cost distributed flag N
974 -- and line type F for CDLs.
975 -- Invoice Items not FC
976 -- End of comments
977
978 /** AP Lines uptake: Obsoleted PROC_SI_INTERFACE
979 PROCEDURE PROC_SI_INTERFACE IS
980 END PROC_SI_INTERFACE ;
981 -- ========= END OF PROC_SI_INTERFACE ===================
982 AP Lines uptake: Obsoleted PROC_SI_INTERFACE **/
983
984 BEGIN
985
986 L_DEBUG := NVL(FND_PROFILE.value('GMS_ENABLE_DEBUG_MODE'), 'N');
987
988 IF L_DEBUG = 'Y' THEN
989 gms_error_pkg.gms_debug('GMS: Vert_trx_interface START TIME :'||to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'), 'C');
990 gms_error_pkg.gms_debug('GMS: Number of Rows to process: '||Rows, 'C');
991 END IF;
992
993 IF x_status < 0 THEN
994 IF L_DEBUG = 'Y' THEN
995 gms_error_pkg.gms_debug('GMS: Input Parameter x_status: '||x_status, 'C');
996 gms_error_pkg.gms_debug('GMS: Vert_trx_interface END TIME <==', 'C');
997 END IF;
998 return ;
999 END IF ;
1000
1001 X_request_id := FND_GLOBAL.CONC_REQUEST_ID ;
1002 X_program_id := FND_GLOBAL.CONC_PROGRAM_ID ;
1003 X_program_application_id := FND_GLOBAL.PROG_APPL_ID ;
1004
1005 SELECT set_of_books_id
1006 INTO x_sob_id
1007 FROM PA_IMPLEMENTATIONS ;
1008
1009 -- Check whether gms is enabled, if gms is not enabled return
1010 -- Remove references to this call inside the loops.
1011
1012 -- if not gms_install.enabled then -- Bug 3002305
1013 if not vert_install then
1014 return;
1015 end if;
1016
1017 /* check the transaction source. If it is any of Supplier Costs related do not
1018 process. They'll be processed in gms_pa_costing_pkg now. Bug : 2750896 */
1019
1020 if Rows > 0 then
1021 if PA_TRANSACTIONS.EiTrxSrcTab(1) in ('AP INVOICE', 'AP DISCOUNTS',
1022 'AP NRTAX', 'AP EXPENSE', 'AP ERV') then /* Bug 5284323 */
1023 return;
1024 end if;
1025 end if;
1026
1027 -- --------------------------------------------------
1028 -- Read from LOAD EI pa array of expenditure items.
1029 -- --------------------------------------------------
1030 IF L_DEBUG = 'Y' THEN
1031 gms_error_pkg.gms_debug('GMS: Start of Loop on LoadEI pa array', 'C');
1032 END IF;
1033 FOR i IN 1..Rows LOOP
1034
1035 -- --------------------------
1036 -- LOAD VARIABLES.
1037 -- --------------------------
1038 x_invoice_id := to_number(PA_TRANSACTIONS.Cdlsr2Tab(i)) ;
1039 X_dist_lno := to_number(PA_TRANSACTIONS.Cdlsr3Tab(i));
1040 X_raw_cost := PA_TRANSACTIONS.RawCostTab(i);
1041 X_ei_id := PA_TRANSACTIONS.EiIdTab(i);
1042 X_exp_id := PA_TRANSACTIONS.EIdTab(i) ;
1043 X_bill_hold_flag := PA_TRANSACTIONS.BillHoldTab(i) ;
1044 X_project_id := PA_TRANSACTIONS.ProjIdTab(i);
1045 X_task_id := PA_TRANSACTIONS.TskidTab(i);
1046 X_raw_cost := PA_TRANSACTIONS.RawCostTab(i);
1047 x_trx_src := PA_TRANSACTIONS.EiTrxSrcTab(i) ;
1048 x_exp_item_date := PA_TRANSACTIONS.EiDateTab(i) ;
1049 --x_burden_award_id := to_number(PA_TRANSACTIONS.Att1Tab(i)) ; -- Bug 2775237, Moved it below
1050 X_ind_cmpl_set_id := PA_TRANSACTIONS.TpIndCompiledSetIdTab(i) ;
1051 X_Billable_flag := PA_TRANSACTIONS.BillFlagTab(i); -- Added, Bug 1756179
1052 x_exp_org_id := NULL ;
1053
1054 BEGIN
1055 select ei.cost_distributed_flag, NVL( ei.override_to_organization_id, exp.incurred_by_organization_id )
1056 into X_costed_flag, x_exp_org_id
1057 from pa_expenditure_items_all ei,
1058 pa_expenditures_all exp
1059 where ei.expenditure_item_id = X_ei_id
1060 and ei.expenditure_id = exp.expenditure_id ;
1061 EXCEPTION
1062 when no_data_found then
1063 X_costed_flag := 'N' ;
1064 x_exp_org_id := NULL ;
1065 when others then
1066 X_costed_flag := 'N' ;
1067 x_exp_org_id := NULL ;
1068 END ;
1069
1070 IF nvl(X_project_id, 0) = 0 and NVL(X_task_id,0) <> 0 THEN
1071 BEGIN
1072 SELECT project_id
1073 into X_project_id
1074 from pa_tasks
1075 where task_id = X_task_id
1076 and rownum < 2 ;
1077 EXCEPTION
1078 when no_data_found then
1079 NULL ;
1080 when too_many_rows then
1081 NULL ;
1082 when others then
1083 NULL ;
1084 END ;
1085 END IF ;
1086
1087 -- ----------------------------------------
1088 -- Get the CDL line NUM only if they exist
1089 -- ----------------------------------------
1090 if v_trx_src <> x_trx_src then
1091
1092 open c_txn_source;
1093 fetch c_txn_source into x_purgeable, v_gl_accounted;
1094 close c_txn_source;
1095
1096 v_trx_src := x_trx_src;
1097
1098 end if;
1099
1100 if v_gl_accounted = 'Y' then
1101 open c_cdl_num ;
1102 fetch c_cdl_num into x_cdl_num ;
1103 close c_cdl_num ;
1104 end if;
1105
1106 IF x_cdl_num = 0 THEN
1107 x_cdl_num := NULL ;
1108 END IF ;
1109
1110 -- ---------------------------------
1111 -- LD PA GRANTS INTERFACE.
1112 -- External system Interface.
1113 -- ---------------------------------
1114 x_temp := SUBSTR(X_TRX_SRC, 1,4) ;
1115
1116 IF X_TRX_SRC = 'GOLD' OR x_temp in ( 'GOLD', 'GMSA' ) THEN
1117
1118 PROC_LDPA_INTERFACE ;
1119
1120 -- ======= END OF 'GOLD' INTERFACE. ========
1121 ELSIF X_module = 'PAXCBCAB' and X_calling_process = 'PA_BURDEN_COSTING' THEN
1122 IF L_DEBUG = 'Y' THEN
1123 gms_error_pkg.gms_debug('GMS: Processing X_module = PAXCBCAB, X_calling_process = PA_BURDEN_COSTING', 'C');
1124 gms_error_pkg.gms_debug('GMS: Before PROC_BURDEN_INTERFACE', 'C');
1125 END IF;
1126 x_burden_award_id := to_number(PA_TRANSACTIONS.Att1Tab(i)) ; -- Bug 2775237
1127 PROC_BURDEN_INTERFACE( x_burden_award_id) ;
1128 IF L_DEBUG = 'Y' THEN
1129 gms_error_pkg.gms_debug('GMS: After PROC_BURDEN_INTERFACE', 'C');
1130 END IF;
1131
1132 END IF ;
1133
1134 IF nvl( x_invoice_id, 0) = 0 THEN
1135 GOTO NEXTRECORD ;
1136 END IF ;
1137
1138 -- ---------------------------
1139 -- SUPPLIER INVOICE INTERFACE
1140 -- ---------------------------
1141
1142 -- --------------------------------------------------
1143 -- We know that in case of supplier invoice
1144 -- AcctRawCost stores the value for the raw cost.
1145 -- --------------------------------------------------
1146 X_raw_cost := PA_TRANSACTIONS.AcctRawCost(i);
1147
1148 /** AP Lines uptake: Obsoleted PROC_SI_INTERFACE
1149 AP Lines uptake: Obsoleted PROC_SI_INTERFACE **/
1150
1151 <<NEXTRECORD>>
1152 NULL ;
1153 END LOOP ; -- END OF EXP LOOP.
1154
1155 IF L_DEBUG = 'Y' THEN
1156 gms_error_pkg.gms_debug('GMS: End of Loop on LoadEI pa array', 'C');
1157 gms_error_pkg.gms_debug('GMS: x_packet_id: '||x_packet_id, 'C');
1158 END IF;
1159
1160
1161 IF NVL(x_packet_id,0) = 0 THEN
1162 RETURN ;
1163 END IF ;
1164
1165 -- ========================================================================================
1166 -- Bug : 1698738 - IDC RATE CHANGES CAUSE DISCREPENCIES IN S.I. INTERFACE TO PROJECTS.
1167 -- get_award_cmt_compiled_set_id was replaced by award_cmt_compiled_set_id
1168 -- ========================================================================================
1169
1170 -- ---------------------------------------------------------------
1171 -- Bug 2143160 Insert vendor_id into gms_bc_packets for AP and EXP
1172 -- ---------------------------------------------------------------
1173
1174 Insert into gms_bc_packets
1175 ( PACKET_ID,
1176 PROJECT_ID,
1177 AWARD_ID,
1178 TASK_ID,
1179 EXPENDITURE_TYPE,
1180 EXPENDITURE_ITEM_DATE,
1181 ACTUAL_FLAG,
1182 STATUS_CODE,
1183 LAST_UPDATE_DATE,
1184 LAST_UPDATED_BY,
1185 CREATED_BY,
1186 CREATION_DATE,
1187 LAST_UPDATE_LOGIN,
1188 SET_OF_BOOKS_ID,
1189 JE_CATEGORY_NAME,
1190 JE_SOURCE_NAME,
1191 TRANSFERED_FLAG,
1192 DOCUMENT_TYPE,
1193 EXPENDITURE_ORGANIZATION_ID,
1194 PERIOD_NAME,
1195 PERIOD_YEAR,
1196 PERIOD_NUM,
1197 DOCUMENT_HEADER_ID ,
1198 DOCUMENT_DISTRIBUTION_ID,
1199 TOP_TASK_ID,
1200 BUDGET_VERSION_ID,
1201 BUD_TASK_ID, -- Bug 3338999
1202 RESOURCE_LIST_MEMBER_ID,
1203 ACCOUNT_TYPE,
1204 ENTERED_DR,
1205 ENTERED_CR ,
1206 TOLERANCE_AMOUNT,
1207 TOLERANCE_PERCENTAGE,
1208 OVERRIDE_AMOUNT,
1209 EFFECT_ON_FUNDS_CODE ,
1210 RESULT_CODE,
1211 GL_BC_PACKETS_ROWID,
1212 BC_PACKET_ID,
1213 PARENT_BC_PACKET_ID,
1214 VENDOR_ID)
1215 select
1216 gbc.PACKET_ID,
1217 gbc.PROJECT_ID,
1218 gbc.AWARD_ID,
1219 gbc.TASK_ID,
1220 icc.EXPENDITURE_TYPE,
1221 trunc(gbc.EXPENDITURE_ITEM_DATE),
1222 gbc.ACTUAL_FLAG,
1223 gbc.STATUS_CODE,
1224 gbc.LAST_UPDATE_DATE,
1225 gbc.LAST_UPDATED_BY,
1226 gbc.CREATED_BY,
1227 gbc.CREATION_DATE,
1228 gbc.LAST_UPDATE_LOGIN,
1229 gbc.SET_OF_BOOKS_ID,
1230 gbc.JE_CATEGORY_NAME,
1231 gbc.JE_SOURCE_NAME,
1232 gbc.TRANSFERED_FLAG,
1233 gbc.DOCUMENT_TYPE,
1234 gbc.EXPENDITURE_ORGANIZATION_ID,
1235 gbc.PERIOD_NAME,
1236 gbc.PERIOD_YEAR,
1237 gbc.PERIOD_NUM,
1238 gbc.DOCUMENT_HEADER_ID ,
1239 gbc.DOCUMENT_DISTRIBUTION_ID,
1240 gbc.TOP_TASK_ID,
1241 gbc.BUDGET_VERSION_ID,
1242 gbc.BUD_TASK_ID, -- Bug 3338999
1243 NULL, -- gbc.RESOURCE_LIST_MEMBER_ID
1244 gbc.ACCOUNT_TYPE,
1245 -- Bug 1980810 PA Rounding function added
1246 pa_currency.round_currency_amt(sign(nvl(entered_dr,0)) * abs(nvl(gbc.BURDENABLE_RAW_COST ,0) * nvl(cm.compiled_multiplier,0))),
1247 pa_currency.round_currency_amt(sign(nvl(entered_cr,0)) * abs(nvl(gbc.BURDENABLE_RAW_COST ,0) * nvl(cm.compiled_multiplier,0))),
1248 gbc.TOLERANCE_AMOUNT,
1249 gbc.TOLERANCE_PERCENTAGE,
1250 gbc.OVERRIDE_AMOUNT,
1251 gbc.EFFECT_ON_FUNDS_CODE ,
1252 gbc.RESULT_CODE,
1253 gbc.gl_bc_packets_rowid,
1254 gms_bc_packets_s.nextval,
1255 gbc.BC_PACKET_ID,
1256 gbc.vendor_id
1257 from pa_ind_rate_sch_revisions irsr,
1258 pa_cost_bases cb,
1259 pa_expenditure_types et,
1260 pa_ind_cost_codes icc,
1261 pa_cost_base_exp_types cbet,
1262 pa_ind_rate_schedules_all_bg irs,
1263 pa_ind_compiled_sets ics,
1264 pa_compiled_multipliers cm,
1265 gms_bc_packets gbc
1266 where irsr.cost_plus_structure = cbet.cost_plus_structure
1267 and cb.cost_base = cbet.cost_base
1268 and cb.cost_base_type = cbet.cost_base_type
1269 and ics.cost_base = cbet.cost_base --Bug 3003584
1270 and et.expenditure_type = icc.expenditure_type
1271 and icc.ind_cost_code = cm.ind_cost_code
1272 and cbet.cost_base = cm.cost_base
1273 and cbet.cost_base_type = 'INDIRECT COST'
1274 and cbet.expenditure_type = gbc.expenditure_type
1275 and irs.ind_rate_sch_id = irsr.ind_rate_sch_id
1276 and ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
1277 and ics.organization_id = gbc.expenditure_organization_id
1278 and ics.ind_compiled_set_id = gms_cost_plus_extn.AWARD_CMT_COMPILED_SET_ID( gbc.DOCUMENT_HEADER_ID ,
1279 gbc.DOCUMENT_DISTRIBUTION_ID,
1280 gbc.task_id,
1281 gbc.document_type,
1282 gbc.expenditure_item_date,
1283 gbc.expenditure_type, -- Bug 3003584
1284 gbc.expenditure_organization_id,
1285 'C',
1286 gbc.award_id )
1287 --join with compiled setid of adl.
1288 and cm.ind_compiled_set_id = ics.ind_compiled_set_id
1289 and cm.compiled_multiplier <> 0
1290 and gbc.packet_id = x_packet_id
1291 and gbc.document_type = 'AP' ;
1292
1293 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1294 -- BUG: 1418038 Supplier invoice not updated properly in ASI and FC results .
1295 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1296 Insert into gms_bc_packets
1297 ( PACKET_ID,
1298 PROJECT_ID,
1299 AWARD_ID,
1300 TASK_ID,
1301 EXPENDITURE_TYPE,
1302 EXPENDITURE_ITEM_DATE,
1303 ACTUAL_FLAG,
1304 STATUS_CODE,
1305 LAST_UPDATE_DATE,
1306 LAST_UPDATED_BY,
1307 CREATED_BY,
1308 CREATION_DATE,
1309 LAST_UPDATE_LOGIN,
1310 SET_OF_BOOKS_ID,
1311 JE_CATEGORY_NAME,
1312 JE_SOURCE_NAME,
1313 TRANSFERED_FLAG,
1314 DOCUMENT_TYPE,
1315 EXPENDITURE_ORGANIZATION_ID,
1316 PERIOD_NAME,
1317 PERIOD_YEAR,
1318 PERIOD_NUM,
1319 DOCUMENT_HEADER_ID ,
1320 DOCUMENT_DISTRIBUTION_ID,
1321 TOP_TASK_ID,
1322 BUDGET_VERSION_ID,
1323 BUD_TASK_ID,
1324 RESOURCE_LIST_MEMBER_ID,
1325 ACCOUNT_TYPE,
1326 ENTERED_DR,
1327 ENTERED_CR ,
1328 TOLERANCE_AMOUNT,
1329 TOLERANCE_PERCENTAGE,
1330 OVERRIDE_AMOUNT,
1331 EFFECT_ON_FUNDS_CODE ,
1332 RESULT_CODE,
1333 GL_BC_PACKETS_ROWID,
1334 BC_PACKET_ID,
1335 PARENT_BC_PACKET_ID,
1336 VENDOR_ID)
1337 select
1338 gbc.PACKET_ID,
1339 gbc.PROJECT_ID,
1340 gbc.AWARD_ID,
1341 gbc.TASK_ID,
1342 icc.EXPENDITURE_TYPE,
1343 trunc(gbc.EXPENDITURE_ITEM_DATE),
1344 gbc.ACTUAL_FLAG,
1345 gbc.STATUS_CODE,
1346 gbc.LAST_UPDATE_DATE,
1347 gbc.LAST_UPDATED_BY,
1348 gbc.CREATED_BY,
1349 gbc.CREATION_DATE,
1350 gbc.LAST_UPDATE_LOGIN,
1351 gbc.SET_OF_BOOKS_ID,
1352 gbc.JE_CATEGORY_NAME,
1353 gbc.JE_SOURCE_NAME,
1354 gbc.TRANSFERED_FLAG,
1355 gbc.DOCUMENT_TYPE,
1356 gbc.EXPENDITURE_ORGANIZATION_ID,
1357 gbc.PERIOD_NAME,
1358 gbc.PERIOD_YEAR,
1359 gbc.PERIOD_NUM,
1360 gbc.DOCUMENT_HEADER_ID ,
1361 gbc.DOCUMENT_DISTRIBUTION_ID,
1362 gbc.TOP_TASK_ID,
1363 gbc.BUDGET_VERSION_ID,
1364 gbc.BUD_TASK_ID, -- Bug 3338999
1365 gbc.RESOURCE_LIST_MEMBER_ID,
1366 gbc.ACCOUNT_TYPE,
1367 -- Bug 1980810 PA Rounding function added
1368 pa_currency.round_currency_amt(decode(nvl(entered_dr,0),0,0,((nvl(gbc.BURDENABLE_RAW_COST ,0)) * nvl(cm.compiled_multiplier,0)))),
1369 pa_currency.round_currency_amt(decode(nvl(entered_cr,0),0,0,((nvl(gbc.BURDENABLE_RAW_COST ,0)) * nvl(cm.compiled_multiplier,0)))),
1370 gbc.TOLERANCE_AMOUNT,
1371 gbc.TOLERANCE_PERCENTAGE,
1372 gbc.OVERRIDE_AMOUNT,
1373 gbc.EFFECT_ON_FUNDS_CODE ,
1374 gbc.RESULT_CODE,
1375 gbc.GL_BC_PACKETS_ROWID,
1376 gms_bc_packets_s.nextval,
1377 gbc.BC_PACKET_ID,
1378 gbc.vendor_id
1379 from pa_ind_rate_sch_revisions irsr,
1380 pa_cost_bases cb,
1381 pa_expenditure_types et,
1382 pa_ind_cost_codes icc,
1383 pa_cost_base_exp_types cbet,
1384 pa_ind_rate_schedules_all_bg irs,
1385 pa_ind_compiled_sets ics,
1386 pa_compiled_multipliers cm,
1387 gms_bc_packets gbc
1388 where irsr.cost_plus_structure = cbet.cost_plus_structure
1389 and cb.cost_base = cbet.cost_base
1390 and cb.cost_base_type = cbet.cost_base_type
1391 and ics.cost_base = cbet.cost_base --Bug 3003584
1392 and et.expenditure_type = icc.expenditure_type
1393 and icc.ind_cost_code = cm.ind_cost_code
1394 and cbet.cost_base = cm.cost_base
1395 and cbet.cost_base_type = 'INDIRECT COST'
1396 and cbet.expenditure_type = gbc.expenditure_type
1397 and irs.ind_rate_sch_id = irsr.ind_rate_sch_id
1398 and ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
1399 and ics.organization_id = gbc.expenditure_organization_id
1400 and gbc.document_type = 'EXP'
1401 and ics.ind_compiled_set_id = gms_cost_plus_extn.AWARD_CMT_COMPILED_SET_ID( gbc.DOCUMENT_HEADER_ID ,
1402 gbc.DOCUMENT_DISTRIBUTION_ID,
1403 gbc.task_id,
1404 gbc.document_type,
1405 gbc.expenditure_item_date,
1406 gbc.expenditure_type, --Bug 3003584
1407 gbc.expenditure_organization_id,
1408 'C',
1409 gbc.award_id )
1410 and cm.ind_compiled_set_id = ics.ind_compiled_set_id
1411 and cm.compiled_multiplier <> 0 -- Fix for Bug 806481
1412 and gbc.packet_id = x_packet_id ;
1413
1414 x_temp := 'SETUP_RLMI' ;
1415
1416 -- FYI ----------------------------
1417 -- R-> MODE inicate RESERVED MODE.
1418 -- --------------------------------
1419
1420 -- ------------
1421 -- Bug 2143160
1422 -- ------------
1423 gms_cost_plus_extn.update_exp_rev_cat (x_packet_id);
1424
1425 IF L_DEBUG = 'Y' THEN
1426 gms_error_pkg.gms_debug('GMS :setup_rlmi START TIME :'||to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'), 'C');
1427 END IF;
1428
1429 gms_funds_control_pkg.setup_rlmi( x_packet_id, 'R', x_err_code, x_err_buff) ;
1430
1431 IF L_DEBUG = 'Y' THEN
1432 gms_error_pkg.gms_debug('GMS :setup_rlmi END TIME :'||to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'), 'C');
1433 END IF;
1434
1435 -- -----------------
1436 -- Bug 2143160
1437 -- -----------------
1438 gms_cost_plus_extn.update_top_tsk_par_res (x_packet_id);
1439
1440 x_temp := NULL ;
1441
1442 IF NVL(x_err_code, 0) <> 0 THEN
1443 pa_cc_utils.log_message('GMS: Resource mapping failed for packet :'||to_char(x_packet_id),1);
1444 raise_application_error( -20000, SQLERRM(X_ERR_CODE) ) ;
1445 END IF ;
1446
1447 SELECT count(*)
1448 into x_err_code
1449 FROM DUAL
1450 WHERE exists ( select 'X' from gms_bc_packets
1451 where packet_id = x_packet_id
1452 and substr(nvl(result_code, 'P'),1,1) = 'F' );
1453
1454 IF L_DEBUG = 'Y' THEN
1455 gms_error_pkg.gms_debug('GMS:vert_trx_interface END TIME :'||to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'), 'C');
1456 END IF;
1457
1458 IF x_err_code > 0 THEN
1459 pa_cc_utils.log_message('GMS: Resource mapping failed for packet :'||to_char(x_packet_id),1);
1460 raise_application_error( -20000, 'GMS: Resource mapping failed for packet :'||to_char(x_packet_id) ) ;
1461 END IF ;
1462
1463 EXCEPTION
1464 WHEN OTHERS THEN
1465 X_status := SQLCODE;
1466 IF L_DEBUG = 'Y' THEN
1467 gms_error_pkg.gms_debug('GMS:EXCEPTION:vert_trx_interface END TIME :'||to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'), 'C');
1468 gms_error_pkg.gms_debug('vert_trx_interface :'||SQLERRM, 'C');
1469 END IF;
1470
1471 IF NVL(x_temp,'NONE') = 'SETUP_RLMI' THEN
1472 raise_application_error( -20000, 'GMS: Resource mapping failed for packet :'||to_char(x_packet_id) ) ;
1473 END IF ;
1474
1475 RAISE;
1476
1477 END vert_trx_interface ;
1478
1479 -- ----------------------------------------------------------------
1480 -- API to allow vertical applications to take actions following the
1481 -- creation of AP distribution lines.
1482 -- This is called from PA_XFER_ADJ.
1483 -- -----------------------------------------------------------------
1484 PROCEDURE VERT_PAAP_SI_ADJUSTMENTS( x_expenditure_item_id IN NUMBER,
1485 x_invoice_id IN NUMBER,
1486 x_distribution_line_number IN NUMBER,
1487 x_cdl_line_num IN NUMBER,
1488 x_project_id IN NUMBER,
1489 x_task_id IN NUMBER,
1490 status IN OUT NOCOPY NUMBER ) IS
1491
1492 x_rec gms_award_distributions%ROWTYPE ;
1493 x_invoice_distribution_id NUMBER ;
1494
1495 BEGIN
1496
1497 L_DEBUG := NVL(FND_PROFILE.value('GMS_ENABLE_DEBUG_MODE'), 'N');
1498
1499 -- -------------------------------------------------
1500 -- Vertical application will override the code here.
1501 -- -------------------------------------------------
1502 NULL ;
1503 END VERT_PAAP_SI_ADJUSTMENTS ;
1504
1505 -- ----------------------------------------------------------------
1506 -- API to allow vertical applications to validate transaction
1507 -- interface. This is called from PA_TRX_IMPORTS just after ValidateItem
1508 -- -----------------------------------------------------------------
1509 PROCEDURE VERT_APP_VALIDATE( X_transaction_source IN VARCHAR2,
1510 X_CURRENT_BATCH IN VARCHAR2,
1511 X_txn_interface_id IN NUMBER ,
1512 X_org_id IN NUMBER,
1513 X_status IN OUT NOCOPY Varchar2 ) IS
1514
1515 x_invoice_id varchar2(20) ;
1516 -- AP Lines uptake: use invoice_distribution_id instead of distribution_line_number
1517 x_inv_dist_id NUMBER;
1518 --
1519 -- bug : 3617328 perf issue in gmspax1b.pls
1520 --
1521 l_project_id number ;
1522 l_task_id number ;
1523 l_project_number pa_projects_all.segment1%TYPE ;
1524 l_task_number pa_tasks.task_number%TYPE ;
1525 x_outcome VARCHAR2(2000) ;
1526 l_gl_accted_flag VARCHAR2(1) ;
1527 l_bud_ver_id NUMBER ;
1528 l_dummy NUMBER ;
1529 l_award_id NUMBER ;
1530 l_pre_processing_extension VARCHAR2(60); -- Bug 3035863
1531
1532
1533 -- -----------------------------------------------------------------------------------
1534 -- BUG : 2484010 INTERFACE SUP INV FROM PAYABLES DOES NOT SHOW APPROPRIATE EXCEPTIONS
1535 -- -----------------------------------------------------------------------------------
1536 cursor C_gl_accted is
1537 select gl_accounted_flag ,
1538 pre_processing_extension -- Bug 3035863
1539 from pa_transaction_sources
1540 where transaction_source = X_transaction_source ;
1541 -- -----------------------------------------------------------------
1542 -- BUG: 1361739 - Supplier Invoice Interface cause validation
1543 -- failed. ERROR - GMS_VALIDATION_FAILED.
1544 -- Supplier Invoice Interface doesn't put records into
1545 -- gms_transaction_interface_all, So award_id wasn't found and result
1546 -- in GMS_VALIDATION_FAILED.
1547 -- We should be doing this validations only if we have records in
1548 -- gms_transaction_interface_all table .
1549 -- ------------------------------------------------------------------
1550 CURSOR C_BUDGET_CHECK ( x_project_id NUMBER, x_award_id NUMBER ) is
1551 SELECT budget_version_id
1552 from gms_budget_versions
1553 where project_id = x_project_id
1554 and award_id = x_award_id
1555 and budget_status_code = 'B'
1556 and current_flag = 'Y' ;
1557
1558 CURSOR C_AWD_EXP_TYPE_CHECK ( x_award_id number, x_exp_type varchar2) is
1559 select 1
1560 from gms_award_exp_type_act_cost
1561 where award_id = x_award_id
1562 and expenditure_type = x_exp_type ;
1563
1564 cursor c_awd_exp_type_check2 ( x_award_id number, x_exp_type varchar2) is
1565 select 1
1566 from gms_bc_packets
1567 where status_code = 'A'
1568 and award_id = x_award_id
1569 and expenditure_type = x_exp_type ;
1570
1571 -- =============================================================================
1572 -- BUG : 2540841 - Reject supplier invoice dist lines having incorrect ADLS.
1573 -- =============================================================================
1574 cursor c_get_award is
1575 select adl.award_id
1576 from gms_award_distributions adl,
1577 ap_invoice_distributions_all apd
1578 where apd.award_id = adl.award_set_id
1579 and adl.adl_line_num = 1
1580 and adl.adl_status = 'A'
1581 and apd.invoice_id = to_number(x_invoice_id)
1582 -- AP Lines uptake: use invoice_distribution_id instead of distribution_line_number
1583 and apd.invoice_distribution_id = x_inv_dist_id
1584 and adl.document_type = 'AP'
1585 and apd.invoice_id = NVL( adl.invoice_id, 0)
1586 -- AP Lines uptake: use invoice_distribution_id instead of distribution_line_number
1587 and apd.invoice_distribution_id = NVL ( adl.invoice_distribution_id, 0) ;
1588 --
1589 -- Bug 5237650
1590 -- R12.PJ:XB4:DEV:APL:EXP ITEM DATE VALIDATIONS FOR SUPPLIER COST.
1591 -- =====
1592 CURSOR GET_VALID_AWARDS IS
1593 Select Allowable_Schedule_Id,
1594 nvl(Preaward_Date,START_DATE_ACTIVE) preaward_date,
1595 End_Date_Active end_date,
1596 Close_Date close_date,
1597 Status
1598 from GMS_AWARDS
1599 where award_id = l_award_id;
1600
1601 c_award_rec GET_VALID_AWARDS%ROWTYPE ;
1602 -- =========================================================================
1603 -- 1646518 - GMS_VALIDATION_FAILED WHEN RUNNING TRANSACTION IMPORT PROCESS
1604 -- Date : 02/19/2001
1605 -- Fix : additional Join T.task_id = P.project_id was added.
1606 -- =========================================================================
1607
1608 --=========================================================================
1609 --bug 1651938 - Transaction Import Failed with TXN_NOT_FOUND.
1610 -- T.task_id = P.project_id was bad join fixed.
1611 --=========================================================================
1612 -- --Fix bug 2355391 ( Modified for the bug )
1613 -- 2747838 ( SUPPLIER INVOICE DO NOT INTERFACE TO GRANTS. )
1614 -- Projects is using project_id column value for projects
1615 -- seeded transaction sources.
1616 -- PA.K Certification changes.
1617 -- ========================================================================
1618 --
1619 -- bug : 3617328 perf issue in gmspax1b.pls
1620 --
1621 CURSOR C_txn_rec is
1622 SELECT txn.project_id project_id,
1623 txn.task_id task_id,
1624 txn.project_number project_number,
1625 txn.task_number task_number,
1626 txn.expenditure_type expenditure_type,
1627 txn.expenditure_item_date expenditure_item_date,
1628 txn.cdl_system_reference2 invoice_id,
1629 -- AP Lines uptake: use invoice_distribution_id instead of distribution_line_number
1630 txn.cdl_system_reference5 invoice_distribution_id,
1631 txn.system_linkage system_linkage,
1632 Gtxn.award_id award_id,
1633 -- Bug 3221039 and 3035863 : Added below columns
1634 gtxn.award_number award_number,
1635 txn.transaction_source transaction_source,
1636 txn.batch_name batch_name,
1637 GTXN.txn_interface_id txn_interface_id
1638 FROM gms_transaction_interface_all Gtxn,
1639 pa_transaction_interface_all txn
1640 WHERE txn.txn_interface_id = X_txn_interface_id
1641 AND txn.txn_interface_id = Gtxn.txn_interface_id (+) ;
1642
1643 txn_rec c_txn_rec%ROWTYPE ;
1644
1645 --
1646 -- bug : 3617328 perf issue in gmspax1b.pls
1647 --
1648 cursor c_get_project_id is
1649 select project_id
1650 from pa_projects_all
1651 where segment1 = l_project_number ;
1652
1653 --
1654 -- bug : 3617328 perf issue in gmspax1b.pls
1655 --
1656 cursor c_get_project_num is
1657 select segment1
1658 from pa_projects_all
1659 where project_id = l_project_id ;
1660
1661 --
1662 -- bug : 3617328 perf issue in gmspax1b.pls
1663 --
1664 cursor c_get_task_id is
1665 select task_id
1666 from pa_tasks
1667 where task_number = l_task_number
1668 and project_id = l_project_id ;
1669
1670 --
1671 -- bug : 3617328 perf issue in gmspax1b.pls
1672 --
1673 cursor c_get_task_num is
1674 select task_number
1675 from pa_tasks
1676 where task_id = l_task_id ;
1677
1678 BEGIN
1679
1680 L_DEBUG := NVL(FND_PROFILE.value('GMS_ENABLE_DEBUG_MODE'), 'N');
1681
1682 pa_cc_utils.log_message( 'GMS_PA_API.VERT_APP_VALIDATE - START' ,1);
1683 IF X_status is not NULL then
1684 RETURN ;
1685 END IF ;
1686 -- -------------------------------------------------
1687 -- Vertical application will override the code here.
1688 -- -------------------------------------------------
1689 IF not gms_install.enabled(X_org_id) THEN
1690 RETURN ;
1691 END IF ;
1692
1693 OPEN C_txn_rec ;
1694 FETCH C_txn_rec into TXN_REC ;
1695
1696 IF C_txn_rec%NOTFOUND THEN
1697 raise no_data_found ;
1698 END IF ;
1699
1700 --
1701 -- bug : 3617328 perf issue in gmspax1b.pls
1702 --
1703 l_project_id := txn_rec.project_id ;
1704 l_task_id := txn_rec.task_id ;
1705 l_project_number := txn_rec.project_number ;
1706 l_task_number := txn_rec.task_number ;
1707
1708 --
1709 -- bug : 3617328 perf issue in gmspax1b.pls
1710 --
1711 if l_project_id is not null then
1712 open c_get_project_num ;
1713 fetch c_get_project_num into l_project_number ;
1714 close c_get_project_num ;
1715 else
1716 open c_get_project_id ;
1717 fetch c_get_project_id into l_project_id ;
1718 close c_get_project_id ;
1719 end if ;
1720
1721 --
1722 -- bug : 3617328 perf issue in gmspax1b.pls
1723 --
1724 IF l_task_id is not NULL THEN
1725 open c_get_task_num ;
1726 fetch c_get_task_num into l_task_number ;
1727 close c_get_task_num ;
1728
1729 ELSE
1730 open c_get_task_id ;
1731 fetch c_get_task_id into l_task_id ;
1732 close c_get_task_id ;
1733 END IF ;
1734
1735 -- Bug: 3016256
1736 -- Stop INV transactions from being created for the operating unit having grants implemented.
1737 --
1738 IF txn_rec.system_linkage = 'INV' then
1739 x_status := 'GMS_INV_NOT_ALLOWED' ;
1740 IF C_txn_rec%IsOpen THEN
1741 CLOSE C_txn_rec;
1742 END IF ;
1743 return ;
1744 END IF ;
1745
1746
1747 -- Bug 3035863: The following code is added to stop the further processing of
1748 -- encumbrance if proper pre processing extension is not defined .
1749
1750 -- This validation will be also fired from Projects main import code if proper
1751 -- pre-processing extension is not defined and hence will be rejected.
1752
1753 IF SUBSTR(txn_rec.transaction_source,1,4) ='GMSE' THEN
1754 OPEN c_gl_accted ;
1755 FETCH c_gl_accted into l_gl_accted_flag,l_pre_processing_extension ;
1756 CLOSE c_gl_accted ;
1757
1758 IF NVL(l_pre_processing_extension,'DUMMY') <> 'GMS_ENC_IMPORT_PKG.PRE_PROCESS' THEN
1759
1760 x_status := 'GMS_IMP_ENC_INCORR_EXT';
1761 CLOSE C_txn_rec ;
1762 RETURN ;
1763
1764 END IF;
1765
1766 END IF;
1767
1768 -- ------------------------------------------------------
1769 -- Proceed only if entered project is sponsored project.
1770 -- ------------------------------------------------------
1771 IF NOT is_sponsored_project( l_project_id ) THEN
1772
1773 If txn_rec.award_id IS NOT NULL THEN
1774 x_status := 'GMS_NOT_A_SPONSORED_PROJECT'; -- Fix for bug : 2439320
1775 end if ;
1776
1777 -- Bug 3035863: Reject the transaction if Encumbrance imported from
1778 -- External system against a non sponsored project.
1779
1780 If substr(txn_rec.transaction_source,1,4) ='GMSE' OR txn_rec.transaction_source ='GOLDE' THEN
1781 x_status := 'GMS_IMP_ENC_NONSPON';
1782 end if ;
1783
1784 CLOSE C_txn_rec ;
1785 RETURN ;
1786 END IF ;
1787
1788 -- -----------------------------------------------------------------------------------
1789 -- BUG : 2484010 INTERFACE SUP INV FROM PAYABLES DOES NOT SHOW APPROPRIATE EXCEPTIONS
1790 -- -----------------------------------------------------------------------------------
1791 -- Add budget validations
1792 IF NVL( G_TRX_SOURCE,'X') <> NVL(x_transaction_source,'XX') THEN
1793
1794 open c_gl_accted ;
1795 fetch c_gl_accted into l_gl_accted_flag,l_pre_processing_extension ;
1796 close c_gl_accted ;
1797 g_gl_accted_flag := l_gl_accted_flag ;
1798 G_TRX_SOURCE := x_transaction_source ;
1799
1800 ELSE
1801 l_gl_accted_flag := g_gl_accted_flag ;
1802 END IF ;
1803
1804 X_invoice_id := txn_rec.invoice_id ;
1805 -- AP Lines uptake:use document_distribution_id instead of distribution_line_number
1806 x_inv_dist_id := txn_rec.invoice_distribution_id;
1807
1808 -- ---------------------------------------------------------------
1809 -- Supplier invoice interface transactions do not have records in
1810 -- gms_transaction_interface_all table. So we need to have award
1811 -- from the ap distribution table.
1812 -- ----------------------------------------------------------------
1813 --
1814 -- BUG:4164822 PJ.M:B11:P11:QA:GMS IMPORTED TRANSACTIONS REJECTED DUE TO INVALID REASON
1815 -- Resolution: We shouldn't be checking award details from ap dist lines for external
1816 -- transaction source.
1817 --
1818 -- Bug 4231758 : Added code to consider AP EXPENSE's having system linkage 'ER'
1819 IF txn_rec.system_linkage IN ('ER','VI') and
1820 substr(X_transaction_source, 1,4) NOT IN ('GMSA','GMSE')
1821 then
1822 open c_get_award ;
1823 fetch c_get_award into l_award_id ;
1824
1825 IF c_get_award%NOTFOUND THEN
1826 x_status := 'GMS_AP_ADLS_MISSING' ;
1827 -- Supplier invoice lines has incorrect award distribution lines, Pls
1828 -- contact oracle support.
1829 --
1830 END IF ;
1831 close c_get_award ;
1832 --
1833 -- Bug 5237650
1834 -- R12.PJ:XB4:DEV:APL:EXP ITEM DATE VALIDATIONS FOR SUPPLIER COST.
1835 --
1836 IF l_award_id is not null then
1837 pa_cc_utils.log_message( 'GMS_PA_API.VERT_APP_VALIDATE - calling get_valid_awards') ;
1838
1839 open get_valid_awards ;
1840 fetch get_valid_awards into c_award_rec ;
1841 close get_valid_awards ;
1842
1843 pa_cc_utils.log_message( 'GMS_PA_API.VERT_APP_VALIDATE - EI Date validations') ;
1844 IF txn_rec.expenditure_item_date < TRUNC(c_award_rec.preaward_date) THEN
1845 x_status := 'GMS_EXP_ITEM_DT_BEFORE_AWD_ST' ;
1846 ELSIF txn_rec.expenditure_item_date > TRUNC(c_award_rec.end_date) THEN
1847 x_status := 'GMS_EXP_ITEM_DT_AFTER_AWD_END' ;
1848 ELSIF c_award_rec.close_date < TRUNC(SYSDATE) THEN
1849 x_status := 'GMS_AWARD_IS_CLOSED' ;
1850 END IF ;
1851 pa_cc_utils.log_message( 'GMS_PA_API.VERT_APP_VALIDATE - EI Date validations :'||x_status) ;
1852 END IF ;
1853 --
1854 -- Bug 5237650
1855 -- R12.PJ:XB4:DEV:APL:EXP ITEM DATE VALIDATIONS FOR SUPPLIER COST.
1856 -- End here
1857 ELSE
1858 -- Bug 3221039 : Added below code to populate and validate award id and
1859 -- award number for non VI transaction.
1860
1861 IF txn_rec.award_id IS NULL AND txn_rec.award_number IS NULL THEN
1862 x_status := 'GMS_AWARD_REQUIRED' ;
1863 ELSE
1864 txn_rec.award_id := get_award_id (txn_rec.award_id,txn_rec.award_number);
1865 l_award_id := txn_rec.award_id;
1866 pa_cc_utils.log_message( 'GMS_PA_API.VERT_APP_VALIDATE - After calling get_award_id , value of award_id'
1867 ||txn_rec.award_id ,1);
1868 IF NVL(txn_rec.award_id,0) = 0 then
1869 x_status := 'GMS_INVALID_AWARD';
1870 END IF;
1871 END IF;
1872
1873 end if ;
1874
1875 IF x_status is not NULL then
1876 IF C_txn_rec%IsOpen THEN
1877 CLOSE C_txn_rec;
1878 END IF ;
1879 return ;
1880 END IF ;
1881
1882 -- ====================================
1883 -- End of bug fix 2484010 INTERFACE SUP
1884 -- ====================================
1885
1886 -- ----------------------------------------------------------------
1887 -- GL accounted transactions are costed transactions. Costed trans
1888 -- must have baselined budget and records in burden summary
1889 -- table.
1890 -- ----------------------------------------------------------------
1891 IF NVL(l_gl_accted_flag,'N') = 'Y' and
1892 l_project_id is not NULL and
1893 l_award_id is not null and
1894 txn_rec.expenditure_type is not null THEN
1895
1896 l_bud_ver_id := NULL ;
1897 --
1898 -- BUG:4164822 PJ.M:B11:P11:QA:GMS IMPORTED TRANSACTIONS REJECTED DUE TO INVALID REASON
1899 -- Resolution: We shouldn't be checking award details from ap dist lines for external
1900 -- transaction source.
1901 --
1902
1903 open c_budget_check( l_project_id, l_award_id ) ;
1904 fetch c_budget_check into l_bud_ver_id ;
1905 close c_budget_check ;
1906
1907 IF l_bud_ver_id is NULL THEN
1908 X_status := 'GMS_AWD_BUDGET_NOT_BASELINED' ;
1909 ELSE
1910 l_dummy := NULL ;
1911 -- bug : 3777692
1912 -- PJ.M:B6:P13:FC:SUPPLIER INVOICE INTERFACE FAILS WITH AWARD SUMMARY RECORD NOT FO
1913 -- Resolution :
1914 -- Check was removed.
1915 -- X_status := 'GMS_AWD_EXP_SUMMARY_NOT_FOUND' ;
1916 END IF ;
1917 END IF ;
1918 -- End of bug 2484010 ***
1919 -- --------------------------------
1920
1921
1922 -- -----------------------------------------------------------------
1923 -- BUG: 1361739 - Supplier Invoice Interface cause validation
1924 -- failed. ERROR - GMS_VALIDATION_FAILED.
1925 -- -----------------------------------------------------------------
1926
1927 -- ---------------------------------------------------------------
1928 -- Continue processing only if transaction source is custom
1929 -- interface.
1930 -- ---------------------------------------------------------------
1931 IF ( ( txn_rec.TXN_INTERFACE_ID is NULL AND
1932 substr(X_transaction_source, 1,4) NOT IN ('GMSA','GMSE')) OR
1933 x_status is not null ) then
1934
1935 CLOSE C_txn_rec ;
1936
1937 IF L_DEBUG = 'Y' THEN
1938 gms_error_pkg.gms_DEBUG('GMS Validate X_status:'||X_status, 'C');
1939 END IF;
1940 RETURN ;
1941 END IF ;
1942
1943 -- ================================================================
1944 -- Transaction Import should fail if there are missing record in
1945 -- gms_transaction_interface_all table.
1946 -- ================================================================
1947 -- Bug 3221039 :Commented the following code as the validation is shifted to
1948 -- newly introduced function set_award_info
1949
1950 -- --------------------------------------------------------------
1951 -- Data integrity checks between GMS and PA tables.
1952 -- --------------------------------------------------------------
1953 -- Bug 3221039 : Commented the below validations as the columns are obsolete
1954
1955 -- -----------------------------------------------------------------
1956 -- Standard GMS validations
1957 -- -----------------------------------------------------------------
1958 GMS_TRANSACTIONS_PUB.VALIDATE_TRANSACTION( l_project_id,
1959 l_task_id,
1960 txn_rec.award_id,
1961 txn_rec.expenditure_type,
1962 txn_rec.expenditure_item_date,
1963 'PAXTTRXB',
1964 X_outcome ) ;
1965 IF X_outcome is not NULL THEN
1966 IF L_DEBUG = 'Y' THEN
1967 gms_error_pkg.gms_DEBUG(X_outcome, 'C');
1968 END IF;
1969 X_status := substr(x_outcome,1,30); --bug 2305262
1970 END IF ;
1971
1972 CLOSE C_txn_rec ;
1973
1974 EXCEPTION
1975 WHEN no_data_found then
1976 IF C_txn_rec%ISOPEN THEN
1977 CLOSE C_txn_rec ;
1978 END IF ;
1979
1980 -- -----------------------------------------------------------------------------------
1981 -- BUG : 2484010 INTERFACE SUP INV FROM PAYABLES DOES NOT SHOW APPROPRIATE EXCEPTIONS
1982 -- -----------------------------------------------------------------------------------
1983 IF c_budget_check%ISOPEN THEN
1984 close c_budget_check ;
1985 END IF ;
1986
1987 IF c_awd_exp_type_check%ISOPEN THEN
1988 close c_awd_exp_type_check ;
1989 END IF ;
1990
1991 IF L_DEBUG = 'Y' THEN
1992 gms_error_pkg.gms_DEBUG('Transaction Record not found for TXN_interface_id :'||to_char(X_txn_interface_id), 'C');
1993 END IF;
1994 X_status := 'TXN_NOT_FOUND' ;
1995
1996 WHEN OTHERS THEN
1997 IF C_txn_rec%ISOPEN THEN
1998 CLOSE C_txn_rec ;
1999 END IF ;
2000
2001 IF c_budget_check%ISOPEN THEN
2002 close c_budget_check ;
2003 END IF ;
2004
2005 IF c_awd_exp_type_check%ISOPEN THEN
2006 close c_awd_exp_type_check ;
2007 END IF ;
2008
2009 X_status := 'GMS_UNEXPECTED_ERROR' ;
2010 IF L_DEBUG = 'Y' THEN
2011 gms_error_pkg.gms_DEBUG('GMS_UNEXPECTED_ERROR for TXN_interface_id :'||to_char(X_txn_interface_id), 'C');
2012 END IF;
2013 END VERT_APP_VALIDATE ;
2014
2015
2016 PROCEDURE VERT_SI_ADJ ( x_expenditure_item_id IN NUMBER,
2017 x_invoice_id IN NUMBER,
2018 x_distribution_line_number IN NUMBER,
2019 x_project_id IN NUMBER,
2020 x_task_id IN NUMBER,
2021 status IN OUT NOCOPY NUMBER ) is
2022 BEGIN
2023 -- -------------------------------------------------
2024 -- Vertical application will override the code here.
2025 -- -------------------------------------------------
2026 NULL ;
2027 END VERT_SI_ADJ ;
2028
2029
2030
2031 -- --------------------------------------------------------------------
2032 -- BUG: 1332945 - GMS not doing validations for award informations.
2033 -- called from GMS_TXN_INTERFACE_AIT1
2034 -- file : gmstxntr.sql
2035 -- Gms_validations may reject transaction import records.
2036 -- --------------------------------------------------------------------
2037 PROCEDURE VERT_REJECT_TXN( x_txn_interface_id IN NUMBER,
2038 x_batch_name IN VARCHAR2,
2039 x_txn_source IN VARCHAR2,
2040 x_status IN VARCHAR2,
2041 x_calling_source IN varchar2 ) is
2042 BEGIN
2043 IF NVL(x_calling_source,'X') = 'GMS_TXN_INTERFACE_AIT1' OR
2044 NVL(x_calling_source,'X') = 'GMS_TXN_INTERFACE_AIT2' THEN --bug 2305262
2045
2046 UPDATE PA_TRANSACTION_INTERFACE_ALL
2047 SET transaction_rejection_code = X_status ,
2048 transaction_status_code = 'PR'
2049 where TXN_INTERFACE_ID = x_txn_interface_id
2050 and batch_name = X_batch_name
2051 and transaction_source=x_txn_source ;
2052
2053 ELSE
2054 UPDATE PA_TRANSACTION_INTERFACE_ALL
2055 SET transaction_rejection_code = X_status ,
2056 transaction_status_code = 'R'
2057 where TXN_INTERFACE_ID = x_txn_interface_id
2058 and batch_name = X_batch_name
2059 and transaction_source=x_txn_source ;
2060
2061 END IF ;
2062 EXCEPTION
2063 When others THEN
2064 RAISE ;
2065 END VERT_REJECT_TXN ;
2066
2067 -- ---------------------------------------------------------------------
2068 -- BUG:1380464 - net zero invoice items having different awards are not
2069 -- picked up by supplier invoice interface process.
2070 -- Call to this function is added in package PAAPIMP_PKG.
2071 -- ----------------------------------------------------------------------
2072 FUNCTION VERT_GET_AWARD_ID( x_award_set_id IN NUMBER,
2073 x_invoice_id IN NUMBER,
2074 x_dist_lno IN NUMBER ) return NUMBER is
2075 l_award_id NUMBER ;
2076
2077 -- ===========================================
2078 -- bug : 1765806 jackson lab issue ported here.
2079 -- ============================================
2080
2081 -- BUG: 2319153 ( Stuck AP lines not interfaced to Grants.
2082 -- cursor changed and only criteria used is award_set_id
2083 -- and active adls.
2084 -- ----
2085 -- bug : 2305048 ( Unable to interface AP to OGA.
2086 -- This is due to multiple active ADLs.
2087 cursor C1 is
2088 SELECT award_id
2089 FROM gms_award_distributions
2090 WHERE award_set_id = x_award_set_id
2091 AND adl_status = 'A'
2092 and adl_line_num = 1 ;
2093 --AND document_type = 'AP'
2094 --AND invoice_id = x_invoice_id
2095 --AND distribution_line_number <= x_dist_lno ;
2096
2097 -- ========================================================================
2098 -- BUG:1772926 - bug was created for situation as follows ..
2099 -- 1. Create a PO, Approve it.
2100 -- 2. Create a matching AP.
2101 -- 3. reverse ap distribution line
2102 -- 4. add a new line.
2103 -- 5. approve AP.
2104 -- 6. cancelling lines doesn't go fundschecking and ADL will still point to
2105 -- po. Need a cursor to fix this issue.
2106 -- =========================================================================
2107
2108 cursor C_PO is
2109 SELECT award_id
2110 FROM gms_award_distributions
2111 WHERE award_set_id = x_award_set_id
2112 AND adl_status = 'A'
2113 AND document_type = 'PO'
2114 and adl_line_num = 1 ;
2115
2116 BEGIN
2117 IF NVL(x_award_set_id,0) = 0 THEN
2118 return 0 ;
2119 END IF ;
2120
2121 open C1 ;
2122 fetch C1 into l_award_id ;
2123
2124 IF C1%NOTFOUND THEN
2125 open C_po ;
2126 fetch C_po into l_award_id ;
2127 close c_po ;
2128 END IF ;
2129
2130 close C1 ;
2131
2132 return l_award_id ;
2133 EXCEPTION
2134 WHEN others THEN
2135
2136 IF C1%ISOPEN THEN
2137 close C1 ;
2138 END IF ;
2139
2140 IF c_po%ISOPEN THEN
2141
2142 close c_po ;
2143
2144 END IF ;
2145
2146 RAISE ;
2147 END VERT_GET_AWARD_ID ;
2148
2149 -- ----------------------------------------------------------------------------
2150 -- This function verifies whether GMS is installed or not
2151 -- This function is changed to cache the gms_install status and all references
2152 -- to gms_install.enabled in this package will use this function.
2153 -- Bug 3002305.
2154 -- ----------------------------------------------------------------------------
2155
2156 FUNCTION VERT_INSTALL return BOOLEAN IS
2157
2158 --l_profile_org NUMBER := to_number(fnd_profile.value('ORG_ID'));
2159 l_profile_org NUMBER := PA_MOAC_UTILS.get_current_org_id ;
2160 BEGIN
2161
2162 IF ((G_ORG_ID_CHECKED is null AND G_GMS_ENABLED is null) OR
2163 (G_ORG_ID_CHECKED <> l_profile_org)) THEN
2164
2165 G_ORG_ID_CHECKED := l_profile_org;
2166
2167 IF gms_install.enabled then
2168 G_GMS_ENABLED := 'Y';
2169 return TRUE ;
2170 Else
2171 G_GMS_ENABLED := 'N';
2172 return FALSE ;
2173 END IF ;
2174
2175 ELSE
2176
2177 IF G_GMS_ENABLED = 'Y' THEN
2178 return TRUE;
2179 ELSE
2180 return FALSE;
2181 END IF;
2182
2183 END IF;
2184
2185 END VERT_INSTALL ;
2186
2187
2188 -- -----------------------------------------------------------------------------
2189 -- Procedure to set the adjust_action
2190 -- -----------------------------------------------------------------------------
2191 PROCEDURE set_adjust_action(x_adjust_action IN VARCHAR2 ) is
2192 begin
2193 X_adj_action := '' ;
2194 X_adj_action := x_adjust_action ;
2195 end ;
2196
2197 PROCEDURE OVERRIDE_RATE_REV_ID(
2198 p_tran_item_id IN number ,
2199 p_tran_type IN Varchar2 ,
2200 p_task_id IN number ,
2201 p_schedule_type IN Varchar2 ,
2202 p_exp_item_date IN Date ,
2203 x_sch_fixed_date OUT NOCOPY Date,
2204 x_rate_sch_rev_id OUT NOCOPY number,
2205 x_status OUT NOCOPY number ) is
2206
2207 l_sponsored_flag varchar2(1);
2208 l_award_id number;
2209 l_stage varchar2(10);
2210 l_transaction_source pa_transaction_sources.transaction_source%TYPE ;
2211 l_system_linkage pa_transaction_interface_all.system_linkage%TYPE ;
2212 l_system_reference2 pa_transaction_interface_all.cdl_system_reference2%TYPE ;
2213 l_system_reference3 pa_transaction_interface_all.cdl_system_reference3%TYPE ;
2214 --
2215 --BUG 5620362 R12.PJ:XB13:ST3:QA:BC:SYSTEM SHOWS AN ERROR WHEN NR TAX IS INTERFACED TO GRANTS
2216 --
2217 l_system_reference5 pa_transaction_interface_all.cdl_system_reference5%TYPE ;
2218
2219 l_predefined_flag pa_transaction_sources.predefined_flag%TYPE ;
2220 BEGIN
2221 x_sch_fixed_date := NULL;
2222 x_rate_sch_rev_id := NULL;
2223 x_status := NULL;
2224
2225 if p_tran_item_id is NULL then
2226 return;
2227 end if;
2228
2229 select nvl(sponsored_flag,'N')
2230 into l_sponsored_flag
2231 from pa_tasks t,
2232 pa_projects_all p,
2233 gms_project_types gpt
2234 where p.project_id = t.project_id
2235 and gpt.project_type = p.project_type
2236 and t.task_id = nvl(p_task_id,0);
2237
2238 if l_sponsored_flag = 'Y' then
2239 --
2240 -- BUG 3596533
2241 -- Transaction Import process failed with no revesion. award not found due to
2242 -- p_tran_type values 'TRANSACTION_IMPORT' was not considered before.
2243 --
2244 -- Resolution : Get award from invoice distribution table for supplier invoice
2245 -- and get award from gms_transaction interface table for user
2246 -- defined sources supported by GMS.
2247 --
2248 IF p_tran_type = 'TRANSACTION_IMPORT' THEN
2249 --
2250 -- Determine the transaction source details.
2251 --
2252 --
2253 --BUG 5620362 R12.PJ:XB13:ST3:QA:BC:SYSTEM SHOWS AN ERROR WHEN NR TAX IS INTERFACED TO GRANTS
2254 -- cdl_system_reference5 was added to the select for invoice distribution ID
2255 --
2256 select pti.transaction_source,
2257 pti.system_linkage,
2258 pti.cdl_system_reference2,
2259 pti.cdl_system_reference3,
2260 pti.cdl_system_reference5,
2261 pts.predefined_flag
2262 into l_transaction_source,
2263 l_system_linkage,
2264 l_system_reference2,
2265 l_system_reference3,
2266 l_system_reference5,
2267 l_predefined_flag
2268 from pa_transaction_interface_all pti,
2269 pa_transaction_sources pts
2270 where pti.txn_interface_id = p_tran_item_id
2271 and pti.transaction_source = pts.transaction_source ;
2272 --
2273 -- Supplier invoice system linkage from pre defined source : get award from
2274 -- invoice distributions and award distribution lines.
2275 --
2276 -- Bug 4231758 : Added code to consider AP EXPENSE's having system linkage 'ER'
2277 IF l_system_linkage IN ('ER','VI') and l_predefined_flag = 'Y' THEN
2278 --
2279 --BUG 5620362 R12.PJ:XB13:ST3:QA:BC:SYSTEM SHOWS AN ERROR WHEN NR TAX IS INTERFACED TO GRANTS
2280 --Query was based on invoice distribution ID stored in the l_system_reference5
2281 --
2282 select adl.award_id
2283 into l_award_id
2284 from gms_Award_distributions adl,
2285 ap_invoice_distributions_all apd
2286 where apd.invoice_id = l_system_reference2
2287 and apd.invoice_distribution_id = l_system_reference5
2288 and apd.invoice_id = adl.invoice_id
2289 and apd.invoice_distribution_id = adl.invoice_distribution_id
2290 and apd.award_id = adl.award_set_id
2291 and adl.adl_status = 'A'
2292 and adl.document_type = 'AP'
2293 and adl.adl_line_num = 1
2294 and rownum = 1;
2295 END IF ;
2296 --
2297 -- USER defined transaction source having gms supported transaction source
2298 -- get award id from gms_transaction_interface_all table record.
2299 --
2300 IF l_predefined_flag = 'N' and
2301 ( SUBSTR(l_transaction_source, 1,4) in ('GMSA', 'GMSE' ) )
2302 THEN
2303 select awd.award_id
2304 into l_award_id
2305 from gms_transaction_interface_all gti,
2306 gms_awards_all awd
2307 where gti.txn_interface_id = p_tran_item_id
2308 and NVL(gti.award_id, awd.award_id) = awd.award_id
2309 and NVL(gti.award_number, awd.award_number ) = awd.award_number
2310 and ( gti.award_id is NOT NULL OR gti.award_number is NOT NULL
2311 ) ;
2312 END IF ;
2313 ELSE
2314 select adl.award_id
2315 into l_award_id
2316 from gms_Award_distributions adl
2317 where adl.expenditure_item_id = p_tran_item_id
2318 and adl.adl_status = 'A'
2319 and adl.document_type = 'EXP'
2320 and adl.adl_line_num = 1
2321 and rownum = 1;
2322
2323 END IF ;
2324 gms_cost_plus_extn.get_award_ind_rate_sch_rev_id(l_award_id ,
2325 --Added for Bug 2097676 :Multiple Indirect Cost Schedules build
2326 p_task_id ,
2327 p_exp_item_date ,
2328 x_rate_sch_rev_id,
2329 x_status ,
2330 l_stage);
2331
2332 -- ==============================================================
2333 -- We need to return x_status 0 here.
2334 -- We don't want PA to process anything for award specific
2335 -- transactions.
2336 -- PA_COST_PLUS do not process task level overrides when GMS hooks
2337 -- returns 0.
2338 -- 2995239 gms_pa_api3 main line code related changes.
2339 -- =============================================================
2340 x_status := 0 ;
2341 end if;
2342
2343 END Override_Rate_Rev_Id ;
2344
2345 -- ========================================================================================
2346 -- 30-APR-2001 aaggarwa BUG : 1751995
2347 -- Description : Multiple awards funding single projects causes
2348 -- burdening problem.
2349 -- Resolution : PA_CLIENT_EXTN_BURDEN_SUMMARY.CLIENT_GROUPING
2350 -- was modified for grants accounting to add award
2351 -- parameter for grouping. This will allow to create
2352 -- burden summarization lines for each award.
2353 -- ========================================================================================
2354 FUNCTION CLIENT_GROUPING
2355 (
2356 p_src_expnd_type IN PA_EXPENDITURE_TYPES.expenditure_type%TYPE,
2357 p_src_ind_expnd_type IN PA_EXPENDITURE_TYPES.expenditure_type%TYPE,
2358 p_src_attribute1 IN PA_EXPENDITURE_TYPES.attribute1%TYPE ,
2359 v_grouping_method IN varchar2
2360 ) return varchar2 is
2361
2362 x_grouping_method varchar2(2000) ;
2363 BEGIN
2364
2365 x_grouping_method := v_grouping_method ;
2366
2367 -- if not gms_install.enabled then -- Bug 3002305
2368 if not vert_install then
2369
2370
2371 IF v_grouping_method is NOT NULL THEN
2372 x_grouping_method := x_grouping_method||p_src_attribute1 ;
2373 ELSE
2374 x_grouping_method := p_src_attribute1 ;
2375 END IF ;
2376
2377 END IF ;
2378
2379 return x_grouping_method ;
2380
2381
2382 END CLIENT_GROUPING;
2383
2384
2385 -- --------------------------------------------------------------------------
2386 -- Function to check the award status before doing any adjustments in
2387 -- Expenditure Inquiry form.
2388 -- --------------------------------------------------------------------------
2389 FUNCTION is_award_closed (x_expenditure_item_id IN NUMBER ,x_task_id IN NUMBER ,x_doc_type in varchar2 default 'EXP') return VARCHAR2 IS --Bug 5726575
2390
2391 l_award_status gms_awards_all.status%TYPE ;
2392 l_close_date gms_awards_all.close_date%TYPE ;
2393 l_project_id pa_projects_all.project_id%TYPE ;
2394
2395 Begin
2396
2397 select aw.status, aw.close_date
2398 into l_award_status ,l_close_date
2399 from gms_award_distributions adl ,gms_awards_all aw
2400 where adl.expenditure_item_id = x_expenditure_item_id
2401 and adl.adl_status = 'A'
2402 and adl.document_type = nvl(x_doc_type, 'EXP') --Bug 5726575
2403 and adl.award_id = aw.award_id
2404 and rownum = 1 ;
2405
2406 IF l_award_status = 'CLOSED' or l_close_date < trunc (sysdate ) then
2407 RETURN 'Y' ;
2408 Else
2409 RETURN 'N' ;
2410 END IF ;
2411
2412 EXCEPTION
2413 when NO_DATA_FOUND then
2414 --
2415 -- 3134005
2416 -- GMS.L: COMPILATION OF BURDEN SCHEDULE COMPLETES IN ERROR
2417 -- SQL was wrong causing too many rows found
2418 -- join with pa_expenditure_items_all was removed.
2419 --
2420 select t.project_id into l_project_id
2421 from pa_tasks t
2422 where t.task_id = x_task_id ;
2423
2424 If is_sponsored_project(l_project_id) THEN
2425 RETURN 'Y' ; -- adl is missing hence don't process that item.
2426 Else
2427 RETURN 'N' ; -- This is non-sponsored project , let PA continue its process
2428 End if ;
2429 END is_award_closed ;
2430 -- ------------------------------------------------------------
2431
2432 /* R12 Changes Start */
2433 -- -------------------------------------------------------------------------
2434 -- This function gets the award id for the specified expenditure item
2435 -- -------------------------------------------------------------------------
2436 FUNCTION VERT_GET_EI_AWARD_ID(p_expenditure_item_id NUMBER)
2437 RETURN NUMBER IS
2438
2439 l_award_id NUMBER := NULL; /* Bug 5194265 - Initialized to NULL */
2440
2441 CURSOR C_AWARD_ID_CUR(p_expenditure_item_id NUMBER) IS
2442 SELECT AWARD_ID
2443 FROM GMS_AWARD_DISTRIBUTIONS
2444 WHERE EXPENDITURE_ITEM_ID = p_expenditure_item_id
2445 AND ADL_LINE_NUM = 1
2446 AND DOCUMENT_TYPE = 'EXP'
2447 AND ADL_STATUS = 'A';
2448
2449 BEGIN
2450
2451 OPEN C_AWARD_ID_CUR(p_expenditure_item_id);
2452 FETCH C_AWARD_ID_CUR INTO l_award_id;
2453 CLOSE C_AWARD_ID_CUR;
2454
2455 RETURN l_award_id; /* Bug 5194265 - Missed out the RETURN statement :-( */
2456
2457 END VERT_GET_EI_AWARD_ID;
2458 -- -------------------------------------------------------------------------
2459 /* R12 Changes End */
2460
2461 /* Added for Bug 5490120
2462 This function accepts the expenditure_item_id as the input and returns the award associated with
2463 this expenditure item.
2464 The function raises an exception if no award is associated with the expenditure item.
2465 */
2466 FUNCTION VERT_GET_AWARD_NUMBER(
2467 p_expenditure_item_id IN PA_EXPENDITURE_ITEMS_ALL.EXPENDITURE_ITEM_ID%TYPE
2468 ) RETURN VARCHAR2 IS
2469 l_award_number GMS_AWARDS_ALL.AWARD_NUMBER%TYPE := NULL;
2470 BEGIN
2471 IF p_expenditure_item_id = G_EXPENDITURE_ITEM_ID THEN
2472 l_award_number := G_AWARD_NUMBER;
2473 ELSE
2474 SELECT a.award_number
2475 INTO l_award_number
2476 FROM gms_awards_all a
2477 , gms_award_distributions adl
2478 WHERE adl.award_id = a.award_id
2479 AND adl.expenditure_item_id = p_expenditure_item_id
2480 AND adl.adl_line_num = 1
2481 AND adl.adl_status = 'A'
2482 AND adl.document_type = 'EXP';
2483 G_AWARD_NUMBER := l_award_number;
2484 G_EXPENDITURE_ITEM_ID := p_expenditure_item_id;
2485 END IF;
2486 RETURN l_award_number;
2487 END VERT_GET_AWARD_NUMBER;
2488
2489 /* Added for Bug 5490120
2490 This function accepts the expenditure_item_id as the input.
2491 If the exenditure item belongs to a sponsored project:
2492 The function determines the Award Number and verifies if the Award Number falls in the specified range.
2493 If yes, then the function returns 'Y'.
2494 If no, then the funciton returns 'N'.
2495 If the expenditure item belongs to a non-sponsored project:
2496 If award range is not specified, then the function returns 'Y'.
2497 If award range is specified, then the function returns 'N'.
2498 */
2499 FUNCTION VERT_IS_AWARD_WITHIN_RANGE(
2500 p_expenditure_item_id IN PA_EXPENDITURE_ITEMS_ALL.EXPENDITURE_ITEM_ID%TYPE
2501 ,p_from_award_number IN GMS_AWARDS_ALL.AWARD_NUMBER%TYPE DEFAULT NULL
2502 ,p_to_award_number IN GMS_AWARDS_ALL.AWARD_NUMBER%TYPE DEFAULT NULL
2503 ) RETURN VARCHAR2 IS
2504 l_award_number GMS_AWARDS_ALL.AWARD_NUMBER%TYPE := NULL;
2505 BEGIN
2506 l_award_number := VERT_GET_AWARD_NUMBER(p_expenditure_item_id);
2507 IF l_award_number BETWEEN NVL(p_from_award_number,l_award_number) AND NVL(p_to_award_number,l_award_number) THEN
2508 RETURN 'Y';
2509 ELSE
2510 RETURN 'N';
2511 END IF;
2512 EXCEPTION
2513 WHEN NO_DATA_FOUND THEN
2514 IF p_from_award_number IS NULL
2515 AND p_to_award_number IS NULL THEN
2516 RETURN 'Y';
2517 ELSE
2518 RETURN 'N';
2519 END IF;
2520 END VERT_IS_AWARD_WITHIN_RANGE;
2521
2522 BEGIN
2523 SELECT default_dist_award_id
2524 into x_default_dist_award_id
2525 from gms_implementations ;
2526 exception
2527 when no_data_found then
2528 x_default_dist_award_id := NULL;
2529 --For Bug 4581880
2530 when OTHERS then
2531 x_default_dist_award_id := NULL;
2532
2533 END GMS_PA_API;