1 PACKAGE BODY GMS_PA_API AS
2 /* $Header: gmspax1b.pls 120.10.12020000.2 2012/10/16 13:46:17 navemish 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 union /* BUG 14216205 : Added the union for SAT */
1589 select adl.award_id
1590 from GMS_AWARD_DISTRIBUTIONS ADL,
1591 AP_SELF_ASSESSED_TAX_DIST_ALL apsat
1592 where apsat.award_id = adl.award_set_id
1593 and adl.adl_line_num = 1
1594 and adl.adl_status = 'A'
1595 and apsat.invoice_id = to_number(x_invoice_id)
1596 and apsat.invoice_distribution_id = x_inv_dist_id
1597 and adl.document_type = 'AP'
1598 and APSAT.INVOICE_ID = NVL( ADL.INVOICE_ID, 0)
1599 and apsat.invoice_distribution_id = NVL ( adl.invoice_distribution_id, 0) ;
1600
1601 --
1602 -- Bug 5237650
1603 -- R12.PJ:XB4:DEV:APL:EXP ITEM DATE VALIDATIONS FOR SUPPLIER COST.
1604 -- =====
1605 CURSOR GET_VALID_AWARDS IS
1606 Select Allowable_Schedule_Id,
1607 nvl(Preaward_Date,START_DATE_ACTIVE) preaward_date,
1608 End_Date_Active end_date,
1609 Close_Date close_date,
1610 Status
1611 from GMS_AWARDS
1612 where award_id = l_award_id;
1613
1614 c_award_rec GET_VALID_AWARDS%ROWTYPE ;
1615 -- =========================================================================
1616 -- 1646518 - GMS_VALIDATION_FAILED WHEN RUNNING TRANSACTION IMPORT PROCESS
1617 -- Date : 02/19/2001
1618 -- Fix : additional Join T.task_id = P.project_id was added.
1619 -- =========================================================================
1620
1621 --=========================================================================
1622 --bug 1651938 - Transaction Import Failed with TXN_NOT_FOUND.
1623 -- T.task_id = P.project_id was bad join fixed.
1624 --=========================================================================
1625 -- --Fix bug 2355391 ( Modified for the bug )
1626 -- 2747838 ( SUPPLIER INVOICE DO NOT INTERFACE TO GRANTS. )
1627 -- Projects is using project_id column value for projects
1628 -- seeded transaction sources.
1629 -- PA.K Certification changes.
1630 -- ========================================================================
1631 --
1632 -- bug : 3617328 perf issue in gmspax1b.pls
1633 --
1634 CURSOR C_txn_rec is
1635 SELECT txn.project_id project_id,
1636 txn.task_id task_id,
1637 txn.project_number project_number,
1638 txn.task_number task_number,
1639 txn.expenditure_type expenditure_type,
1640 txn.expenditure_item_date expenditure_item_date,
1641 txn.cdl_system_reference2 invoice_id,
1642 -- AP Lines uptake: use invoice_distribution_id instead of distribution_line_number
1643 txn.cdl_system_reference5 invoice_distribution_id,
1644 txn.system_linkage system_linkage,
1645 Gtxn.award_id award_id,
1646 -- Bug 3221039 and 3035863 : Added below columns
1647 gtxn.award_number award_number,
1648 txn.transaction_source transaction_source,
1649 txn.batch_name batch_name,
1650 GTXN.txn_interface_id txn_interface_id
1651 FROM gms_transaction_interface_all Gtxn,
1652 pa_transaction_interface_all txn
1653 WHERE txn.txn_interface_id = X_txn_interface_id
1654 AND txn.txn_interface_id = Gtxn.txn_interface_id (+) ;
1655
1656 txn_rec c_txn_rec%ROWTYPE ;
1657
1658 --
1659 -- bug : 3617328 perf issue in gmspax1b.pls
1660 --
1661 cursor c_get_project_id is
1662 select project_id
1663 from pa_projects_all
1664 where segment1 = l_project_number ;
1665
1666 --
1667 -- bug : 3617328 perf issue in gmspax1b.pls
1668 --
1669 cursor c_get_project_num is
1670 select segment1
1671 from pa_projects_all
1672 where project_id = l_project_id ;
1673
1674 --
1675 -- bug : 3617328 perf issue in gmspax1b.pls
1676 --
1677 cursor c_get_task_id is
1678 select task_id
1679 from pa_tasks
1680 where task_number = l_task_number
1681 and project_id = l_project_id ;
1682
1683 --
1684 -- bug : 3617328 perf issue in gmspax1b.pls
1685 --
1686 cursor c_get_task_num is
1687 select task_number
1688 from pa_tasks
1689 where task_id = l_task_id ;
1690
1691 BEGIN
1692
1693 L_DEBUG := NVL(FND_PROFILE.value('GMS_ENABLE_DEBUG_MODE'), 'N');
1694
1695 pa_cc_utils.log_message( 'GMS_PA_API.VERT_APP_VALIDATE - START' ,1);
1696 IF X_status is not NULL then
1697 RETURN ;
1698 END IF ;
1699 -- -------------------------------------------------
1700 -- Vertical application will override the code here.
1701 -- -------------------------------------------------
1702 IF not gms_install.enabled(X_org_id) THEN
1703 RETURN ;
1704 END IF ;
1705
1706 OPEN C_txn_rec ;
1707 FETCH C_txn_rec into TXN_REC ;
1708
1709 IF C_txn_rec%NOTFOUND THEN
1710 raise no_data_found ;
1711 END IF ;
1712
1713 --
1714 -- bug : 3617328 perf issue in gmspax1b.pls
1715 --
1716 l_project_id := txn_rec.project_id ;
1717 l_task_id := txn_rec.task_id ;
1718 l_project_number := txn_rec.project_number ;
1719 l_task_number := txn_rec.task_number ;
1720
1721 --
1722 -- bug : 3617328 perf issue in gmspax1b.pls
1723 --
1724 if l_project_id is not null then
1725 open c_get_project_num ;
1726 fetch c_get_project_num into l_project_number ;
1727 close c_get_project_num ;
1728 else
1729 open c_get_project_id ;
1730 fetch c_get_project_id into l_project_id ;
1731 close c_get_project_id ;
1732 end if ;
1733
1734 --
1735 -- bug : 3617328 perf issue in gmspax1b.pls
1736 --
1737 IF l_task_id is not NULL THEN
1738 open c_get_task_num ;
1739 fetch c_get_task_num into l_task_number ;
1740 close c_get_task_num ;
1741
1742 ELSE
1743 open c_get_task_id ;
1744 fetch c_get_task_id into l_task_id ;
1745 close c_get_task_id ;
1746 END IF ;
1747
1748 -- Bug: 3016256
1749 -- Stop INV transactions from being created for the operating unit having grants implemented.
1750 --
1751 IF txn_rec.system_linkage = 'INV' then
1752 x_status := 'GMS_INV_NOT_ALLOWED' ;
1753 IF C_txn_rec%IsOpen THEN
1754 CLOSE C_txn_rec;
1755 END IF ;
1756 return ;
1757 END IF ;
1758
1759
1760 -- Bug 3035863: The following code is added to stop the further processing of
1761 -- encumbrance if proper pre processing extension is not defined .
1762
1763 -- This validation will be also fired from Projects main import code if proper
1764 -- pre-processing extension is not defined and hence will be rejected.
1765
1766 IF SUBSTR(txn_rec.transaction_source,1,4) ='GMSE' THEN
1767 OPEN c_gl_accted ;
1768 FETCH c_gl_accted into l_gl_accted_flag,l_pre_processing_extension ;
1769 CLOSE c_gl_accted ;
1770
1771 IF NVL(l_pre_processing_extension,'DUMMY') <> 'GMS_ENC_IMPORT_PKG.PRE_PROCESS' THEN
1772
1773 x_status := 'GMS_IMP_ENC_INCORR_EXT';
1774 CLOSE C_txn_rec ;
1775 RETURN ;
1776
1777 END IF;
1778
1779 END IF;
1780
1781 -- ------------------------------------------------------
1782 -- Proceed only if entered project is sponsored project.
1783 -- ------------------------------------------------------
1784 IF NOT is_sponsored_project( l_project_id ) THEN
1785
1786 If txn_rec.award_id IS NOT NULL THEN
1787 x_status := 'GMS_NOT_A_SPONSORED_PROJECT'; -- Fix for bug : 2439320
1788 end if ;
1789
1790 -- Bug 3035863: Reject the transaction if Encumbrance imported from
1791 -- External system against a non sponsored project.
1792
1793 If substr(txn_rec.transaction_source,1,4) ='GMSE' OR txn_rec.transaction_source ='GOLDE' THEN
1794 x_status := 'GMS_IMP_ENC_NONSPON';
1795 end if ;
1796
1797 CLOSE C_txn_rec ;
1798 RETURN ;
1799 END IF ;
1800
1801 -- -----------------------------------------------------------------------------------
1802 -- BUG : 2484010 INTERFACE SUP INV FROM PAYABLES DOES NOT SHOW APPROPRIATE EXCEPTIONS
1803 -- -----------------------------------------------------------------------------------
1804 -- Add budget validations
1805 IF NVL( G_TRX_SOURCE,'X') <> NVL(x_transaction_source,'XX') THEN
1806
1807 open c_gl_accted ;
1808 fetch c_gl_accted into l_gl_accted_flag,l_pre_processing_extension ;
1809 close c_gl_accted ;
1810 g_gl_accted_flag := l_gl_accted_flag ;
1811 G_TRX_SOURCE := x_transaction_source ;
1812
1813 ELSE
1814 l_gl_accted_flag := g_gl_accted_flag ;
1815 END IF ;
1816
1817 X_invoice_id := txn_rec.invoice_id ;
1818 -- AP Lines uptake:use document_distribution_id instead of distribution_line_number
1819 x_inv_dist_id := txn_rec.invoice_distribution_id;
1820
1821 -- ---------------------------------------------------------------
1822 -- Supplier invoice interface transactions do not have records in
1823 -- gms_transaction_interface_all table. So we need to have award
1824 -- from the ap distribution table.
1825 -- ----------------------------------------------------------------
1826 --
1827 -- BUG:4164822 PJ.M:B11:P11:QA:GMS IMPORTED TRANSACTIONS REJECTED DUE TO INVALID REASON
1828 -- Resolution: We shouldn't be checking award details from ap dist lines for external
1829 -- transaction source.
1830 --
1831 -- Bug 4231758 : Added code to consider AP EXPENSE's having system linkage 'ER'
1832 IF txn_rec.system_linkage IN ('ER','VI') and
1833 substr(X_transaction_source, 1,4) NOT IN ('GMSA','GMSE')
1834 then
1835 open c_get_award ;
1836 fetch c_get_award into l_award_id ;
1837
1838 IF c_get_award%NOTFOUND THEN
1839 x_status := 'GMS_AP_ADLS_MISSING' ;
1840 -- Supplier invoice lines has incorrect award distribution lines, Pls
1841 -- contact oracle support.
1842 --
1843 END IF ;
1844 close c_get_award ;
1845 --
1846 -- Bug 5237650
1847 -- R12.PJ:XB4:DEV:APL:EXP ITEM DATE VALIDATIONS FOR SUPPLIER COST.
1848 --
1849 IF l_award_id is not null then
1850 pa_cc_utils.log_message( 'GMS_PA_API.VERT_APP_VALIDATE - calling get_valid_awards') ;
1851
1852 open get_valid_awards ;
1853 fetch get_valid_awards into c_award_rec ;
1854 close get_valid_awards ;
1855
1856 pa_cc_utils.log_message( 'GMS_PA_API.VERT_APP_VALIDATE - EI Date validations') ;
1857 IF txn_rec.expenditure_item_date < TRUNC(c_award_rec.preaward_date) THEN
1858 x_status := 'GMS_EXP_ITEM_DT_BEFORE_AWD_ST' ;
1859 ELSIF txn_rec.expenditure_item_date > TRUNC(c_award_rec.end_date) THEN
1860 x_status := 'GMS_EXP_ITEM_DT_AFTER_AWD_END' ;
1861 ELSIF c_award_rec.close_date < TRUNC(SYSDATE) THEN
1862 x_status := 'GMS_AWARD_IS_CLOSED' ;
1863 END IF ;
1864 pa_cc_utils.log_message( 'GMS_PA_API.VERT_APP_VALIDATE - EI Date validations :'||x_status) ;
1865 END IF ;
1866 --
1867 -- Bug 5237650
1868 -- R12.PJ:XB4:DEV:APL:EXP ITEM DATE VALIDATIONS FOR SUPPLIER COST.
1869 -- End here
1870 ELSE
1871 -- Bug 3221039 : Added below code to populate and validate award id and
1872 -- award number for non VI transaction.
1873
1874 IF txn_rec.award_id IS NULL AND txn_rec.award_number IS NULL THEN
1875 x_status := 'GMS_AWARD_REQUIRED' ;
1876 ELSE
1877 txn_rec.award_id := get_award_id (txn_rec.award_id,txn_rec.award_number);
1878 l_award_id := txn_rec.award_id;
1879 pa_cc_utils.log_message( 'GMS_PA_API.VERT_APP_VALIDATE - After calling get_award_id , value of award_id'
1880 ||txn_rec.award_id ,1);
1881 IF NVL(txn_rec.award_id,0) = 0 then
1882 x_status := 'GMS_INVALID_AWARD';
1883 END IF;
1884 END IF;
1885
1886 end if ;
1887
1888 IF x_status is not NULL then
1889 IF C_txn_rec%IsOpen THEN
1890 CLOSE C_txn_rec;
1891 END IF ;
1892 return ;
1893 END IF ;
1894
1895 -- ====================================
1896 -- End of bug fix 2484010 INTERFACE SUP
1897 -- ====================================
1898
1899 -- ----------------------------------------------------------------
1900 -- GL accounted transactions are costed transactions. Costed trans
1901 -- must have baselined budget and records in burden summary
1902 -- table.
1903 -- ----------------------------------------------------------------
1904 IF NVL(l_gl_accted_flag,'N') = 'Y' and
1905 l_project_id is not NULL and
1906 l_award_id is not null and
1907 txn_rec.expenditure_type is not null THEN
1908
1909 l_bud_ver_id := NULL ;
1910 --
1911 -- BUG:4164822 PJ.M:B11:P11:QA:GMS IMPORTED TRANSACTIONS REJECTED DUE TO INVALID REASON
1912 -- Resolution: We shouldn't be checking award details from ap dist lines for external
1913 -- transaction source.
1914 --
1915
1916 open c_budget_check( l_project_id, l_award_id ) ;
1917 fetch c_budget_check into l_bud_ver_id ;
1918 close c_budget_check ;
1919
1920 IF l_bud_ver_id is NULL THEN
1921 X_status := 'GMS_AWD_BUDGET_NOT_BASELINED' ;
1922 ELSE
1923 l_dummy := NULL ;
1924 -- bug : 3777692
1925 -- PJ.M:B6:P13:FC:SUPPLIER INVOICE INTERFACE FAILS WITH AWARD SUMMARY RECORD NOT FO
1926 -- Resolution :
1927 -- Check was removed.
1928 -- X_status := 'GMS_AWD_EXP_SUMMARY_NOT_FOUND' ;
1929 END IF ;
1930 END IF ;
1931 -- End of bug 2484010 ***
1932 -- --------------------------------
1933
1934
1935 -- -----------------------------------------------------------------
1936 -- BUG: 1361739 - Supplier Invoice Interface cause validation
1937 -- failed. ERROR - GMS_VALIDATION_FAILED.
1938 -- -----------------------------------------------------------------
1939
1940 -- ---------------------------------------------------------------
1941 -- Continue processing only if transaction source is custom
1942 -- interface.
1943 -- ---------------------------------------------------------------
1944 IF ( ( txn_rec.TXN_INTERFACE_ID is NULL AND
1945 substr(X_transaction_source, 1,4) NOT IN ('GMSA','GMSE')) OR
1946 x_status is not null ) then
1947
1948 CLOSE C_txn_rec ;
1949
1950 IF L_DEBUG = 'Y' THEN
1951 gms_error_pkg.gms_DEBUG('GMS Validate X_status:'||X_status, 'C');
1952 END IF;
1953 RETURN ;
1954 END IF ;
1955
1956 -- ================================================================
1957 -- Transaction Import should fail if there are missing record in
1958 -- gms_transaction_interface_all table.
1959 -- ================================================================
1960 -- Bug 3221039 :Commented the following code as the validation is shifted to
1961 -- newly introduced function set_award_info
1962
1963 -- --------------------------------------------------------------
1964 -- Data integrity checks between GMS and PA tables.
1965 -- --------------------------------------------------------------
1966 -- Bug 3221039 : Commented the below validations as the columns are obsolete
1967
1968 -- -----------------------------------------------------------------
1969 -- Standard GMS validations
1970 -- -----------------------------------------------------------------
1971 GMS_TRANSACTIONS_PUB.VALIDATE_TRANSACTION( l_project_id,
1972 l_task_id,
1973 txn_rec.award_id,
1974 txn_rec.expenditure_type,
1975 txn_rec.expenditure_item_date,
1976 'PAXTTRXB',
1977 X_outcome ) ;
1978 IF X_outcome is not NULL THEN
1979 IF L_DEBUG = 'Y' THEN
1980 gms_error_pkg.gms_DEBUG(X_outcome, 'C');
1981 END IF;
1982 X_status := substr(x_outcome,1,30); --bug 2305262
1983 END IF ;
1984
1985 CLOSE C_txn_rec ;
1986
1987 EXCEPTION
1988 WHEN no_data_found then
1989 IF C_txn_rec%ISOPEN THEN
1990 CLOSE C_txn_rec ;
1991 END IF ;
1992
1993 -- -----------------------------------------------------------------------------------
1994 -- BUG : 2484010 INTERFACE SUP INV FROM PAYABLES DOES NOT SHOW APPROPRIATE EXCEPTIONS
1995 -- -----------------------------------------------------------------------------------
1996 IF c_budget_check%ISOPEN THEN
1997 close c_budget_check ;
1998 END IF ;
1999
2000 IF c_awd_exp_type_check%ISOPEN THEN
2001 close c_awd_exp_type_check ;
2002 END IF ;
2003
2004 IF L_DEBUG = 'Y' THEN
2005 gms_error_pkg.gms_DEBUG('Transaction Record not found for TXN_interface_id :'||to_char(X_txn_interface_id), 'C');
2006 END IF;
2007 X_status := 'TXN_NOT_FOUND' ;
2008
2009 WHEN OTHERS THEN
2010 IF C_txn_rec%ISOPEN THEN
2011 CLOSE C_txn_rec ;
2012 END IF ;
2013
2014 IF c_budget_check%ISOPEN THEN
2015 close c_budget_check ;
2016 END IF ;
2017
2018 IF c_awd_exp_type_check%ISOPEN THEN
2019 close c_awd_exp_type_check ;
2020 END IF ;
2021
2022 X_status := 'GMS_UNEXPECTED_ERROR' ;
2023 IF L_DEBUG = 'Y' THEN
2024 gms_error_pkg.gms_DEBUG('GMS_UNEXPECTED_ERROR for TXN_interface_id :'||to_char(X_txn_interface_id), 'C');
2025 END IF;
2026 END VERT_APP_VALIDATE ;
2027
2028
2029 PROCEDURE VERT_SI_ADJ ( x_expenditure_item_id IN NUMBER,
2030 x_invoice_id IN NUMBER,
2031 x_distribution_line_number IN NUMBER,
2032 x_project_id IN NUMBER,
2033 x_task_id IN NUMBER,
2034 status IN OUT NOCOPY NUMBER ) is
2035 BEGIN
2036 -- -------------------------------------------------
2037 -- Vertical application will override the code here.
2038 -- -------------------------------------------------
2039 NULL ;
2040 END VERT_SI_ADJ ;
2041
2042
2043
2044 -- --------------------------------------------------------------------
2045 -- BUG: 1332945 - GMS not doing validations for award informations.
2046 -- called from GMS_TXN_INTERFACE_AIT1
2047 -- file : gmstxntr.sql
2048 -- Gms_validations may reject transaction import records.
2049 -- --------------------------------------------------------------------
2050 PROCEDURE VERT_REJECT_TXN( x_txn_interface_id IN NUMBER,
2051 x_batch_name IN VARCHAR2,
2052 x_txn_source IN VARCHAR2,
2053 x_status IN VARCHAR2,
2054 x_calling_source IN varchar2 ) is
2055 BEGIN
2056 IF NVL(x_calling_source,'X') = 'GMS_TXN_INTERFACE_AIT1' OR
2057 NVL(x_calling_source,'X') = 'GMS_TXN_INTERFACE_AIT2' THEN --bug 2305262
2058
2059 UPDATE PA_TRANSACTION_INTERFACE_ALL
2060 SET transaction_rejection_code = X_status ,
2061 transaction_status_code = 'PR'
2062 where TXN_INTERFACE_ID = x_txn_interface_id
2063 and batch_name = X_batch_name
2064 and transaction_source=x_txn_source ;
2065
2066 ELSE
2067 UPDATE PA_TRANSACTION_INTERFACE_ALL
2068 SET transaction_rejection_code = X_status ,
2069 transaction_status_code = 'R'
2070 where TXN_INTERFACE_ID = x_txn_interface_id
2071 and batch_name = X_batch_name
2072 and transaction_source=x_txn_source ;
2073
2074 END IF ;
2075 EXCEPTION
2076 When others THEN
2077 RAISE ;
2078 END VERT_REJECT_TXN ;
2079
2080 -- ---------------------------------------------------------------------
2081 -- BUG:1380464 - net zero invoice items having different awards are not
2082 -- picked up by supplier invoice interface process.
2083 -- Call to this function is added in package PAAPIMP_PKG.
2084 -- ----------------------------------------------------------------------
2085 FUNCTION VERT_GET_AWARD_ID( x_award_set_id IN NUMBER,
2086 x_invoice_id IN NUMBER,
2087 x_dist_lno IN NUMBER ) return NUMBER is
2088 l_award_id NUMBER ;
2089
2090 -- ===========================================
2091 -- bug : 1765806 jackson lab issue ported here.
2092 -- ============================================
2093
2094 -- BUG: 2319153 ( Stuck AP lines not interfaced to Grants.
2095 -- cursor changed and only criteria used is award_set_id
2096 -- and active adls.
2097 -- ----
2098 -- bug : 2305048 ( Unable to interface AP to OGA.
2099 -- This is due to multiple active ADLs.
2100 cursor C1 is
2101 SELECT award_id
2102 FROM gms_award_distributions
2103 WHERE award_set_id = x_award_set_id
2104 AND adl_status = 'A'
2105 and adl_line_num = 1 ;
2106 --AND document_type = 'AP'
2107 --AND invoice_id = x_invoice_id
2108 --AND distribution_line_number <= x_dist_lno ;
2109
2110 -- ========================================================================
2111 -- BUG:1772926 - bug was created for situation as follows ..
2112 -- 1. Create a PO, Approve it.
2113 -- 2. Create a matching AP.
2114 -- 3. reverse ap distribution line
2115 -- 4. add a new line.
2116 -- 5. approve AP.
2117 -- 6. cancelling lines doesn't go fundschecking and ADL will still point to
2118 -- po. Need a cursor to fix this issue.
2119 -- =========================================================================
2120
2121 cursor C_PO is
2122 SELECT award_id
2123 FROM gms_award_distributions
2124 WHERE award_set_id = x_award_set_id
2125 AND adl_status = 'A'
2126 AND document_type = 'PO'
2127 and adl_line_num = 1 ;
2128
2129 BEGIN
2130 IF NVL(x_award_set_id,0) = 0 THEN
2131 return 0 ;
2132 END IF ;
2133
2134 open C1 ;
2135 fetch C1 into l_award_id ;
2136
2137 IF C1%NOTFOUND THEN
2138 open C_po ;
2139 fetch C_po into l_award_id ;
2140 close c_po ;
2141 END IF ;
2142
2143 close C1 ;
2144
2145 return l_award_id ;
2146 EXCEPTION
2147 WHEN others THEN
2148
2149 IF C1%ISOPEN THEN
2150 close C1 ;
2151 END IF ;
2152
2153 IF c_po%ISOPEN THEN
2154
2155 close c_po ;
2156
2157 END IF ;
2158
2159 RAISE ;
2160 END VERT_GET_AWARD_ID ;
2161
2162 -- ----------------------------------------------------------------------------
2163 -- This function verifies whether GMS is installed or not
2164 -- This function is changed to cache the gms_install status and all references
2165 -- to gms_install.enabled in this package will use this function.
2166 -- Bug 3002305.
2167 -- ----------------------------------------------------------------------------
2168
2169 FUNCTION VERT_INSTALL return BOOLEAN IS
2170
2171 --l_profile_org NUMBER := to_number(fnd_profile.value('ORG_ID'));
2172 l_profile_org NUMBER := PA_MOAC_UTILS.get_current_org_id ;
2173 BEGIN
2174
2175 IF ((G_ORG_ID_CHECKED is null AND G_GMS_ENABLED is null) OR
2176 (G_ORG_ID_CHECKED <> l_profile_org)) THEN
2177
2178 G_ORG_ID_CHECKED := l_profile_org;
2179
2180 IF gms_install.enabled then
2181 G_GMS_ENABLED := 'Y';
2182 return TRUE ;
2183 Else
2184 G_GMS_ENABLED := 'N';
2185 return FALSE ;
2186 END IF ;
2187
2188 ELSE
2189
2190 IF G_GMS_ENABLED = 'Y' THEN
2191 return TRUE;
2192 ELSE
2193 return FALSE;
2194 END IF;
2195
2196 END IF;
2197
2198 END VERT_INSTALL ;
2199
2200
2201 -- -----------------------------------------------------------------------------
2202 -- Procedure to set the adjust_action
2203 -- -----------------------------------------------------------------------------
2204 PROCEDURE set_adjust_action(x_adjust_action IN VARCHAR2 ) is
2205 begin
2206 X_adj_action := '' ;
2207 X_adj_action := x_adjust_action ;
2208 end ;
2209
2210 PROCEDURE OVERRIDE_RATE_REV_ID(
2211 p_tran_item_id IN number ,
2212 p_tran_type IN Varchar2 ,
2213 p_task_id IN number ,
2214 p_schedule_type IN Varchar2 ,
2215 p_exp_item_date IN Date ,
2216 x_sch_fixed_date OUT NOCOPY Date,
2217 x_rate_sch_rev_id OUT NOCOPY number,
2218 x_status OUT NOCOPY number ) is
2219
2220 l_sponsored_flag varchar2(1);
2221 l_award_id number;
2222 l_stage varchar2(10);
2223 l_transaction_source pa_transaction_sources.transaction_source%TYPE ;
2224 l_system_linkage pa_transaction_interface_all.system_linkage%TYPE ;
2225 l_system_reference2 pa_transaction_interface_all.cdl_system_reference2%TYPE ;
2226 l_system_reference3 pa_transaction_interface_all.cdl_system_reference3%TYPE ;
2227 --
2228 --BUG 5620362 R12.PJ:XB13:ST3:QA:BC:SYSTEM SHOWS AN ERROR WHEN NR TAX IS INTERFACED TO GRANTS
2229 --
2230 l_system_reference5 pa_transaction_interface_all.cdl_system_reference5%TYPE ;
2231
2232 l_predefined_flag pa_transaction_sources.predefined_flag%TYPE ;
2233 BEGIN
2234 x_sch_fixed_date := NULL;
2235 x_rate_sch_rev_id := NULL;
2236 x_status := NULL;
2237
2238 if p_tran_item_id is NULL then
2239 return;
2240 end if;
2241
2242 select nvl(sponsored_flag,'N')
2243 into l_sponsored_flag
2244 from pa_tasks t,
2245 pa_projects_all p,
2246 gms_project_types gpt
2247 where p.project_id = t.project_id
2248 and gpt.project_type = p.project_type
2249 and t.task_id = nvl(p_task_id,0);
2250
2251 if l_sponsored_flag = 'Y' then
2252 --
2253 -- BUG 3596533
2254 -- Transaction Import process failed with no revesion. award not found due to
2255 -- p_tran_type values 'TRANSACTION_IMPORT' was not considered before.
2256 --
2257 -- Resolution : Get award from invoice distribution table for supplier invoice
2258 -- and get award from gms_transaction interface table for user
2259 -- defined sources supported by GMS.
2260 --
2261 IF p_tran_type = 'TRANSACTION_IMPORT' THEN
2262 --
2263 -- Determine the transaction source details.
2264 --
2265 --
2266 --BUG 5620362 R12.PJ:XB13:ST3:QA:BC:SYSTEM SHOWS AN ERROR WHEN NR TAX IS INTERFACED TO GRANTS
2267 -- cdl_system_reference5 was added to the select for invoice distribution ID
2268 --
2269 select pti.transaction_source,
2270 pti.system_linkage,
2271 pti.cdl_system_reference2,
2272 pti.cdl_system_reference3,
2273 pti.cdl_system_reference5,
2274 pts.predefined_flag
2275 into l_transaction_source,
2276 l_system_linkage,
2277 l_system_reference2,
2278 l_system_reference3,
2279 l_system_reference5,
2280 l_predefined_flag
2281 from pa_transaction_interface_all pti,
2282 pa_transaction_sources pts
2283 where pti.txn_interface_id = p_tran_item_id
2284 and pti.transaction_source = pts.transaction_source ;
2285 --
2286 -- Supplier invoice system linkage from pre defined source : get award from
2287 -- invoice distributions and award distribution lines.
2288 --
2289 -- Bug 4231758 : Added code to consider AP EXPENSE's having system linkage 'ER'
2290 IF l_system_linkage IN ('ER','VI') and l_predefined_flag = 'Y' THEN
2291 --
2292 --BUG 5620362 R12.PJ:XB13:ST3:QA:BC:SYSTEM SHOWS AN ERROR WHEN NR TAX IS INTERFACED TO GRANTS
2293 --Query was based on invoice distribution ID stored in the l_system_reference5
2294 --
2295 /* BUG 14216205 : Commented the below code : Starts */
2296 /*select adl.award_id
2297 into l_award_id
2298 from gms_Award_distributions adl,
2299 ap_invoice_distributions_all apd
2300 where apd.invoice_id = l_system_reference2
2301 and apd.invoice_distribution_id = l_system_reference5
2302 and apd.invoice_id = adl.invoice_id
2303 and apd.invoice_distribution_id = adl.invoice_distribution_id
2304 and apd.award_id = adl.award_set_id
2305 and adl.adl_status = 'A'
2306 and adl.document_type = 'AP'
2307 and adl.adl_line_num = 1
2308 and rownum = 1;*/
2309 /* BUG 14216205 : Commented the below code : Ends */
2310
2311 /* BUG 14216205 : Added the following Code for SAT : Starts */
2312 begin
2313 select adl.award_id
2314 into l_award_id
2315 from gms_Award_distributions adl,
2316 ap_invoice_distributions_all apd
2317 where apd.invoice_id = l_system_reference2
2318 and apd.invoice_distribution_id = l_system_reference5
2319 and apd.invoice_id = adl.invoice_id
2320 and apd.invoice_distribution_id = adl.invoice_distribution_id
2321 and apd.award_id = adl.award_set_id
2322 and adl.adl_status = 'A'
2323 and adl.document_type = 'AP'
2324 and ADL.ADL_LINE_NUM = 1
2325 and rownum = 1;
2326 EXCEPTION
2327 when NO_DATA_FOUND THEN
2328 select ADL.AWARD_ID
2329 into l_award_id
2330 from GMS_AWARD_DISTRIBUTIONS ADL,
2331 AP_SELF_ASSESSED_TAX_DIST_ALL APSAT
2332 where APSAT.INVOICE_ID = l_system_reference2
2333 and apsat.invoice_distribution_id = l_system_reference5
2334 and apsat.invoice_id = adl.invoice_id
2335 and apsat.invoice_distribution_id = adl.invoice_distribution_id
2336 and apsat.award_id = adl.award_set_id
2337 and adl.adl_status = 'A'
2338 and adl.document_type = 'AP'
2339 and ADL.ADL_LINE_NUM = 1
2340 and rownum = 1;
2341
2342 END;
2343
2344 /* BUG 14216205 : Added the following Code for SAT : Ends */
2345
2346 END IF ;
2347 --
2348 -- USER defined transaction source having gms supported transaction source
2349 -- get award id from gms_transaction_interface_all table record.
2350 --
2351 IF l_predefined_flag = 'N' and
2352 ( SUBSTR(l_transaction_source, 1,4) in ('GMSA', 'GMSE' ) )
2353 THEN
2354 select awd.award_id
2355 into l_award_id
2356 from gms_transaction_interface_all gti,
2357 gms_awards_all awd
2358 where gti.txn_interface_id = p_tran_item_id
2359 and NVL(gti.award_id, awd.award_id) = awd.award_id
2360 and NVL(gti.award_number, awd.award_number ) = awd.award_number
2361 and ( gti.award_id is NOT NULL OR gti.award_number is NOT NULL
2362 ) ;
2363 END IF ;
2364 ELSE
2365 select adl.award_id
2366 into l_award_id
2367 from gms_Award_distributions adl
2368 where adl.expenditure_item_id = p_tran_item_id
2369 and adl.adl_status = 'A'
2370 and adl.document_type = 'EXP'
2371 and adl.adl_line_num = 1
2372 and rownum = 1;
2373
2374 END IF ;
2375 gms_cost_plus_extn.get_award_ind_rate_sch_rev_id(l_award_id ,
2376 --Added for Bug 2097676 :Multiple Indirect Cost Schedules build
2377 p_task_id ,
2378 p_exp_item_date ,
2379 x_rate_sch_rev_id,
2380 x_status ,
2381 l_stage);
2382
2383 -- ==============================================================
2384 -- We need to return x_status 0 here.
2385 -- We don't want PA to process anything for award specific
2386 -- transactions.
2387 -- PA_COST_PLUS do not process task level overrides when GMS hooks
2388 -- returns 0.
2389 -- 2995239 gms_pa_api3 main line code related changes.
2390 -- =============================================================
2391 x_status := 0 ;
2392 end if;
2393
2394 END Override_Rate_Rev_Id ;
2395
2396 -- ========================================================================================
2397 -- 30-APR-2001 aaggarwa BUG : 1751995
2398 -- Description : Multiple awards funding single projects causes
2399 -- burdening problem.
2400 -- Resolution : PA_CLIENT_EXTN_BURDEN_SUMMARY.CLIENT_GROUPING
2401 -- was modified for grants accounting to add award
2402 -- parameter for grouping. This will allow to create
2403 -- burden summarization lines for each award.
2404 -- ========================================================================================
2405 FUNCTION CLIENT_GROUPING
2406 (
2407 p_src_expnd_type IN PA_EXPENDITURE_TYPES.expenditure_type%TYPE,
2408 p_src_ind_expnd_type IN PA_EXPENDITURE_TYPES.expenditure_type%TYPE,
2409 p_src_attribute1 IN PA_EXPENDITURE_TYPES.attribute1%TYPE ,
2410 v_grouping_method IN varchar2
2411 ) return varchar2 is
2412
2413 x_grouping_method varchar2(2000) ;
2414 BEGIN
2415
2416 x_grouping_method := v_grouping_method ;
2417
2418 -- if not gms_install.enabled then -- Bug 3002305
2419 if not vert_install then
2420
2421
2422 IF v_grouping_method is NOT NULL THEN
2423 x_grouping_method := x_grouping_method||p_src_attribute1 ;
2424 ELSE
2425 x_grouping_method := p_src_attribute1 ;
2426 END IF ;
2427
2428 END IF ;
2429
2430 return x_grouping_method ;
2431
2432
2433 END CLIENT_GROUPING;
2434
2435
2436 -- --------------------------------------------------------------------------
2437 -- Function to check the award status before doing any adjustments in
2438 -- Expenditure Inquiry form.
2439 -- --------------------------------------------------------------------------
2440 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
2441
2442 l_award_status gms_awards_all.status%TYPE ;
2443 l_close_date gms_awards_all.close_date%TYPE ;
2444 l_project_id pa_projects_all.project_id%TYPE ;
2445
2446 Begin
2447
2448 select aw.status, aw.close_date
2449 into l_award_status ,l_close_date
2450 from gms_award_distributions adl ,gms_awards_all aw
2451 where adl.expenditure_item_id = x_expenditure_item_id
2452 and adl.adl_status = 'A'
2453 and adl.document_type = nvl(x_doc_type, 'EXP') --Bug 5726575
2454 and adl.award_id = aw.award_id
2455 and rownum = 1 ;
2456
2457 IF l_award_status = 'CLOSED' or l_close_date < trunc (sysdate ) then
2458 RETURN 'Y' ;
2459 Else
2460 RETURN 'N' ;
2461 END IF ;
2462
2463 EXCEPTION
2464 when NO_DATA_FOUND then
2465 --
2466 -- 3134005
2467 -- GMS.L: COMPILATION OF BURDEN SCHEDULE COMPLETES IN ERROR
2468 -- SQL was wrong causing too many rows found
2469 -- join with pa_expenditure_items_all was removed.
2470 --
2471 select t.project_id into l_project_id
2472 from pa_tasks t
2473 where t.task_id = x_task_id ;
2474
2475 If is_sponsored_project(l_project_id) THEN
2476 RETURN 'Y' ; -- adl is missing hence don't process that item.
2477 Else
2478 RETURN 'N' ; -- This is non-sponsored project , let PA continue its process
2479 End if ;
2480 END is_award_closed ;
2481 -- ------------------------------------------------------------
2482
2483 /* R12 Changes Start */
2484 -- -------------------------------------------------------------------------
2485 -- This function gets the award id for the specified expenditure item
2486 -- -------------------------------------------------------------------------
2487 FUNCTION VERT_GET_EI_AWARD_ID(p_expenditure_item_id NUMBER)
2488 RETURN NUMBER IS
2489
2490 l_award_id NUMBER := NULL; /* Bug 5194265 - Initialized to NULL */
2491
2492 CURSOR C_AWARD_ID_CUR(p_expenditure_item_id NUMBER) IS
2493 SELECT AWARD_ID
2494 FROM GMS_AWARD_DISTRIBUTIONS
2495 WHERE EXPENDITURE_ITEM_ID = p_expenditure_item_id
2496 AND ADL_LINE_NUM = 1
2497 AND DOCUMENT_TYPE = 'EXP'
2498 AND ADL_STATUS = 'A';
2499
2500 BEGIN
2501
2502 OPEN C_AWARD_ID_CUR(p_expenditure_item_id);
2503 FETCH C_AWARD_ID_CUR INTO l_award_id;
2504 CLOSE C_AWARD_ID_CUR;
2505
2506 RETURN l_award_id; /* Bug 5194265 - Missed out the RETURN statement :-( */
2507
2508 END VERT_GET_EI_AWARD_ID;
2509 -- -------------------------------------------------------------------------
2510 /* R12 Changes End */
2511
2512 /* Added for Bug 5490120
2513 This function accepts the expenditure_item_id as the input and returns the award associated with
2514 this expenditure item.
2515 The function raises an exception if no award is associated with the expenditure item.
2516 */
2517 FUNCTION VERT_GET_AWARD_NUMBER(
2518 p_expenditure_item_id IN PA_EXPENDITURE_ITEMS_ALL.EXPENDITURE_ITEM_ID%TYPE
2519 ) RETURN VARCHAR2 IS
2520 l_award_number GMS_AWARDS_ALL.AWARD_NUMBER%TYPE := NULL;
2521 BEGIN
2522 IF p_expenditure_item_id = G_EXPENDITURE_ITEM_ID THEN
2523 l_award_number := G_AWARD_NUMBER;
2524 ELSE
2525 SELECT a.award_number
2526 INTO l_award_number
2527 FROM gms_awards_all a
2528 , gms_award_distributions adl
2529 WHERE adl.award_id = a.award_id
2530 AND adl.expenditure_item_id = p_expenditure_item_id
2531 AND adl.adl_line_num = 1
2532 AND adl.adl_status = 'A'
2533 AND adl.document_type = 'EXP';
2534 G_AWARD_NUMBER := l_award_number;
2535 G_EXPENDITURE_ITEM_ID := p_expenditure_item_id;
2536 END IF;
2537 RETURN l_award_number;
2538 END VERT_GET_AWARD_NUMBER;
2539
2540 /* Added for Bug 5490120
2541 This function accepts the expenditure_item_id as the input.
2542 If the exenditure item belongs to a sponsored project:
2543 The function determines the Award Number and verifies if the Award Number falls in the specified range.
2544 If yes, then the function returns 'Y'.
2545 If no, then the funciton returns 'N'.
2546 If the expenditure item belongs to a non-sponsored project:
2547 If award range is not specified, then the function returns 'Y'.
2548 If award range is specified, then the function returns 'N'.
2549 */
2550 FUNCTION VERT_IS_AWARD_WITHIN_RANGE(
2551 p_expenditure_item_id IN PA_EXPENDITURE_ITEMS_ALL.EXPENDITURE_ITEM_ID%TYPE
2552 ,p_from_award_number IN GMS_AWARDS_ALL.AWARD_NUMBER%TYPE DEFAULT NULL
2553 ,p_to_award_number IN GMS_AWARDS_ALL.AWARD_NUMBER%TYPE DEFAULT NULL
2554 ) RETURN VARCHAR2 IS
2555 l_award_number GMS_AWARDS_ALL.AWARD_NUMBER%TYPE := NULL;
2556 BEGIN
2557 l_award_number := VERT_GET_AWARD_NUMBER(p_expenditure_item_id);
2558 IF l_award_number BETWEEN NVL(p_from_award_number,l_award_number) AND NVL(p_to_award_number,l_award_number) THEN
2559 RETURN 'Y';
2560 ELSE
2561 RETURN 'N';
2562 END IF;
2563 EXCEPTION
2564 WHEN NO_DATA_FOUND THEN
2565 IF p_from_award_number IS NULL
2566 AND p_to_award_number IS NULL THEN
2567 RETURN 'Y';
2568 ELSE
2569 RETURN 'N';
2570 END IF;
2571 END VERT_IS_AWARD_WITHIN_RANGE;
2572
2573 BEGIN
2574 SELECT default_dist_award_id
2575 into x_default_dist_award_id
2576 from gms_implementations ;
2577 exception
2578 when no_data_found then
2579 x_default_dist_award_id := NULL;
2580 --For Bug 4581880
2581 when OTHERS then
2582 x_default_dist_award_id := NULL;
2583
2584 END GMS_PA_API;
[Home] [Help]