1 PACKAGE BODY GMS_POR_API as
2 --$Header: gmspor1b.pls 120.1 2005/09/09 16:33:16 appldev ship $
3
4
5 -- =============================================================
6 -- Following API returns award number based on the award_id,
7 -- award set id or req_distribution_id.
8 -- This is used to display award number in Billing Region.
9 -- =============================================================
10 FUNCTION get_award_number ( X_award_set_id IN NUMBER,
11 X_award_id IN NUMBER,
12 X_req_distribution_id IN NUMBER)
13 return VARCHAR2 IS
14
15 cursor c_award_number is
16 select award_number
17 from gms_awards_all
18 where award_id = X_award_id ;
19
20 cursor c_adl_award is
21 select a.award_number
22 from gms_awards_all a,
23 gms_award_distributions adl
24 where adl.award_set_id = X_award_set_id
25 and adl_status = 'A'
26 and adl_line_num = 1
27 and adl.award_id = a.award_id ;
28
29 cursor c_req_award is
30 select a.award_number
31 from gms_awards_all a,
32 gms_award_distributions adl ,
33 po_req_distributions_all req
34 where adl.award_set_id = req.award_id
35 and req.distribution_id = X_req_distribution_id
36 and adl_status = 'A'
37 and adl_line_num = 1
38 and adl.award_id = a.award_id ;
39
40 CURSOR c_check_award IS
41 SELECT default_dist_award_number
42 FROM gms_implementations
43 WHERE enabled ='Y'
44 and award_distribution_option = 'Y'
45 and default_dist_award_id = X_award_id ;
46
47 l_award_number varchar2(15) ;
48 BEGIN
49 -- ==============================================================
50 -- Do not proceed if grants is not enabled for an implementation
51 -- Org.
52 -- ==============================================================
53 IF not gms_install.enabled then
54 return l_award_number;
55 END IF ;
56
57 IF X_award_id is not NULL THEN
58
59 open c_award_number ;
60 fetch c_award_number into l_award_number ;
61
62 IF c_award_number%NOTFOUND THEN
63
64 open c_check_award ;
65 fetch c_check_award into l_award_number ;
66
67 IF c_check_award%NOTFOUND THEN
68 close c_check_award ;
69 raise no_data_found ;
70 END IF ;
71
72 CLOSE c_check_award ;
73 END IF ;
74 close c_award_number ;
75
76 ELSIF X_award_set_id is not NULL THEN
77
78 open c_adl_award ;
79 fetch c_adl_award into l_award_number ;
80
81 IF c_adl_award%NOTFOUND THEN
82 close c_adl_award ;
83 raise no_data_found ;
84 END IF ;
85 close c_adl_award ;
86
87 ELSIF X_req_distribution_id is not NULL THEN
88
89 open c_req_award ;
90 fetch c_req_award into l_award_number ;
91
92 IF c_req_award%NOTFOUND THEN
93 close c_req_award ;
94 raise no_data_found ;
95 END IF ;
96 close c_req_award ;
97
98 END IF ;
99
100 return(l_award_number) ;
101 END get_award_number ;
102
103 -- =============================================================
104 -- Following API returns award ID based on the award_number,
105 -- award set id or req_distribution_id.
106 -- This is used to determine award_id .
107 -- =============================================================
108
109 FUNCTION get_award_ID ( X_award_set_id IN NUMBER,
110 X_award_number IN VARCHAR2,
111 X_req_distribution_id IN NUMBER)
112 return NUMBER IS
113
114 cursor c_award_id is
115 select award_id
116 from gms_awards_all
117 where award_number = X_award_number ;
118
119 cursor c_adl_award is
120 select adl.award_id
121 from gms_award_distributions adl
122 where adl.award_set_id = X_award_set_id
123 and adl_status = 'A'
124 and adl_line_num = 1 ;
125
126 cursor c_req_award is
127 select adl.award_id
128 from gms_award_distributions adl ,
129 po_req_distributions_all req
130 where adl.award_set_id = req.award_id
131 and req.distribution_id = X_req_distribution_id
132 and adl_status = 'A'
133 and adl.adl_line_num = 1 ;
134
135 CURSOR c_check_award IS
136 SELECT default_dist_award_id
137 FROM gms_implementations
138 WHERE enabled ='Y'
139 and award_distribution_option = 'Y'
140 and default_dist_award_number = X_award_number ;
141
142 l_award_id number ;
143 begin
144 -- ==============================================================
145 -- Do not proceed if grants is not enabled for an implementation
146 -- Org.
147 -- ==============================================================
148 IF not gms_install.enabled then
149 return l_award_id;
150 END IF ;
151
152
153 IF X_award_number is not NULL THEN
154
155 open c_award_id ;
156 fetch c_award_id into l_award_id ;
157
158 IF c_award_id%NOTFOUND THEN
159 -- Check is default award is entered.
160 -- return default award id in this case.
161 -- BUG identified by IP.
162 -- ---------------------------------------
163 open c_check_award ;
164 fetch c_check_award into l_award_id ;
165
166 IF c_check_award%NOTFOUND THEN
167 close c_check_award ;
168 raise no_data_found ;
169 END IF ;
170
171 CLOSE c_check_award ;
172
173 END IF ;
174 close c_award_id ;
175
176 ELSIF X_award_set_id is not NULL THEN
177
178 open c_adl_award ;
179 fetch c_adl_award into l_award_id ;
180
181 IF c_adl_award%NOTFOUND THEN
182 close c_adl_award ;
183 raise no_data_found ;
184 END IF ;
185 close c_adl_award ;
186
187 ELSIF X_req_distribution_id is not NULL THEN
188
189 open c_req_award ;
190 fetch c_req_award into l_award_id ;
191
192 IF c_req_award%NOTFOUND THEN
193 close c_req_award ;
194 raise no_data_found ;
195 END IF ;
196 close c_req_award ;
197
198 END IF ;
199
200
201 return (l_award_id) ;
202 end get_award_ID ;
203
204 -- ===================================================================
205 -- Following program unit is used to determine sponsored project.
206 -- ===================================================================
207 FUNCTION IS_SPONSORED_PROJECT( x_project_id in NUMBER ) return BOOLEAN
208 is
209 cursor C_spon_project is
210 select pt.sponsored_flag
211 from pa_projects_all b,
212 gms_project_types pt
213 where b.project_id = X_project_id
214 and b.project_type = pt.project_type
215 and pt.sponsored_flag = 'Y' ;
216
217 x_return BOOLEAN ;
218 x_flag varchar2(1) ;
219 BEGIN
220
221 x_return := FALSE ;
222
223 open C_spon_project ;
224 fetch C_spon_project into x_flag ;
225 close C_spon_project ;
226
227 IF nvl(x_flag, 'N') = 'Y' THEN
228 x_return := TRUE ;
229 END IF ;
230
231 return x_return ;
232
233 END IS_SPONSORED_PROJECT ;
234
235 -- =========================================================
236 -- Validate award does the standard grants validations
237 -- program unit make sure that award is entered for
238 -- sponsored projects and expenditure item date
239 -- validations are done.
240 -- Grants common routine for standard validations is
241 -- called here.
242 -- =========================================================
243 PROCEDURE validate_award ( X_project_id IN NUMBER,
244 X_task_id IN NUMBER,
245 X_award_id IN NUMBER,
246 X_award_number IN VARCHAR2,
247 X_expenditure_type IN VARCHAR2,
248 X_expenditure_item_date IN DATE,
249 X_calling_module IN VARCHAR2,
250 X_source_type_code IN VARCHAR2,--Bug-2557041
251 X_status IN OUT NOCOPY VARCHAR2,
252 X_err_msg OUT NOCOPY VARCHAR2 ) is
253
254 l_project_type_class_code varchar2(30);
255 l_row_found varchar2(1);
256 l_award_id NUMBER ;
257 l_dummy NUMBER ;
258 l_award_dist_option varchar2(1) ;
259 l_award_number varchar2(15) ;
260 l_status varchar2(1) ;
261
262 CURSOR c_check_award IS
263 SELECT default_dist_award_id,
264 award_distribution_option,
265 default_dist_award_number
266 FROM gms_implementations
267 WHERE enabled ='Y' ;
268
269 cursor valid_award_csr is
270 select 'Y'
271 from dual
272 where exists
273 (select 1
274 from gms_awards
275 where award_number = X_award_number
276 and nvl(award_id,0) = nvl(l_award_id,0));
277 --Bug 2579915
278 Cursor valid_project_type_class is
279 select project_type_class_code
280 from pa_project_types a,
281 pa_projects_all b
282 where a.project_type = b.project_type
283 and b.project_id = X_project_id;
284
285
286 BEGIN
287 l_status := X_status ;
288 -- ==============================================================
289 -- Do not proceed if grants is not enabled for an implementation
290 -- Org.
291 -- ==============================================================
292 IF not gms_install.enabled then
293 return ;
294 END IF ;
295
296 open c_check_award ;
297 fetch c_check_award into l_dummy ,
298 l_award_dist_option , l_award_number ;
299 close c_check_award ;
300
301 IF NVL(l_award_number, '-X') = x_award_number and
302 NVL(l_award_dist_option,'N') = 'Y' then
303 RETURN ;
304 END IF ;
305
306
307 -- We don't need to continue .
308 -- entered award is dummy award.
309 --IF X_award_id < 0 and THEN
310 -- RETURN ;
311 --end if ;
312
313
314 -- ============================================
315 -- No need to proceed if project/award details
316 -- are null.
317 -- ============================================
318 IF x_project_id is NULL AND
319 x_award_id is NULL AND
320 x_award_number is NULL THEN
321
322 return ;
323 END IF ;
324
325
326 -- =======================================================
327 -- List of validations done here
328 -- 1. Check for contract project. contract project shouldn't
329 -- entered if grants is enabled.
330 -- 2. Nonsponsored project having award should fail.
331 -- 3. Invalid award should stop here.
332 -- 4. Populate award id if required.
333 -- Award id passed null and award_number is not null.
334 -- 5. Sponsored project missing award should error out.
335 -- 6. Check expenditure type belongs to allowable exp's.
336 -- 7. Call gms standard validations defined in
337 -- gms_transaction_pub.
338 -- ================================================================
339
340 l_award_id := X_award_id ;
341
342 -- 1. Check for contract project. contract project shouldn't
343 -- entered if grants is enabled.
344
345 IF X_project_id is not NULL THEN
346 OPEN valid_project_type_class;
347 FETCH valid_project_type_class INTO l_project_type_class_code;
348 CLOSE valid_project_type_class;
349 END IF ;
350
351 if l_project_type_class_code = 'CONTRACT' then
352
353 fnd_message.set_name('GMS','GMS_IP_INVALID_PROJ_TYPE');
354
355 l_status := 'E';
356 X_err_msg := fnd_message.get;
357 X_status := 'E';
358
359 return;
360 end if;
361
362 IF is_sponsored_project (X_project_id) THEN
363
364 --==============================================================
365 -- Bug-2557041
366 -- Do not proceed if grants is enabled and Requisition type is
367 -- internal
368 --==============================================================
369 -- 4555829 support internal requisitions.
370
371 IF nvl(x_source_type_code,'INVENTORY') = 'INVENTORY' and
372 gms_client_extn_po.allow_internal_req = 'N' THEN
373 fnd_message.set_name('GMS','GMS_IP_INVALID_REQ_TYPE');
374 l_status := 'E';
375 X_err_msg := fnd_message.get;
376 x_status := 'E';
377 return;
378 END IF;
379
380 -- 5. Sponsored project missing award should error out.
381 IF X_award_number is NULL then
382 fnd_message.set_name('GMS','GMS_AWARD_REQUIRED');
383 X_err_msg := fnd_message.get;
384 l_status := 'E';
385 x_status := 'E';
386 return;
387 END IF ;
388
389 ELSE
390
391 -- 2. Nonsponsored project having award should fail.
392 IF X_award_number is NOT NULL then
393 fnd_message.set_name('GMS','GMS_AWARD_NOT_ALLOWED');
394 X_err_msg := fnd_message.get;
395 X_status := 'E';
396 L_status := 'E';
397 return;
398 ELSE --Added to fix bug 2579915
399 return;
400 END IF ;
401
402 END IF ;
403
404
405 -- 3. Populate award id if required.
406 -- Award id passed null and award_number is not null.
407
408 l_award_id := X_award_id ;
409
410 if X_award_id is NULL and
411 X_award_number is not NULL then
412 -- ===============================================================
413 -- BUG : 2714080 ( FPISTIP: IP57: ERROR MESSAGE IS NOT PROPER )
414 -- ===============================================================
415
416 BEGIN
417 select award_id
418 into l_award_id
419 from gms_awards
420 where award_number = X_award_number;
421 EXCEPTION
422 When no_data_found then
423 l_award_id := 0 ;
424
425 END ;
426
427
428 end if;
429
430 -- 4. Invalid award should stop here.
431
432 open valid_award_csr;
433 fetch valid_award_csr into l_row_found;
434 close valid_award_csr;
435
436 if NVL(l_row_found,'N') <> 'Y' then
437
438 fnd_message.set_name('GMS','GMS_INVALID_AWARD');
439
440 X_err_msg := fnd_message.get;
441 X_status := 'E';
442 L_status := 'E';
443
444 return;
445
446 end if;
447
448 -- 7. Call gms standard validations defined in
449 -- gms_transaction_pub.
450
451 gms_transactions_pub.validate_transaction(p_project_id => X_project_id,
452 p_task_id => X_task_id,
453 p_award_id => l_award_id,
454 p_expenditure_type => X_expenditure_type,
455 p_expenditure_item_date => X_expenditure_item_date,
456 p_calling_module => 'GMS-IP',
457 p_outcome => X_err_msg );
458
459 if X_err_msg is NOT NULL then
460
461 X_status := 'E';
462 L_status := 'E';
463
464 end if;
465
466 x_status := l_status ;
467
468 return ;
469
470 END validate_award ;
471 --BUG 3295360 add Procedure to provide backward compatibility through overloading
472 PROCEDURE validate_award ( X_project_id IN NUMBER,
473 X_task_id IN NUMBER,
474 X_award_id IN NUMBER,
475 X_award_number IN VARCHAR2,
476 X_expenditure_type IN VARCHAR2,
477 X_expenditure_item_date IN DATE,
478 X_calling_module IN VARCHAR2,
479 X_status IN OUT NOCOPY VARCHAR2,
480 X_err_msg OUT NOCOPY VARCHAR2 ) IS
481 BEGIN
482 gms_por_api.validate_award( X_project_id => x_project_id,
483 X_task_id => x_task_id,
484 X_award_id => x_award_id,
485 X_award_number => x_award_number,
486 X_expenditure_type => X_expenditure_type,
487 X_expenditure_item_date => X_expenditure_item_date,
488 X_calling_module => x_calling_module,
489 X_source_type_code => 'GMS-IP',
490 X_status => X_status,
491 X_err_msg => X_err_msg );
492 END Validate_award ;
493
494 -- ==============================================================
495 -- ADL must be present for account generator. Event based
496 -- processing is done to create or remove adls.
497 -- ==============================================================
498
499 PROCEDURE account_generator_ADL ( X_project_id IN NUMBER,
500 X_task_id IN NUMBER,
501 X_award_id IN NUMBER,
502 X_event IN VARCHAR2,
503 X_award_set_id IN OUT NOCOPY NUMBER,
504 X_status IN OUT NOCOPY varchar2 ) is
505
506 x_adl_rec gms_award_distributions%ROWTYPE;
507 l_sponsored_flag varchar2(1) ;
508 l_award_set_id number ;
509 l_status varchar2(1) ;
510
511 cursor C_spon_project is
512 select pt.sponsored_flag
513 from pa_projects_all b,
514 gms_project_types pt
515 where b.project_id = X_project_id
516 and b.project_type = pt.project_type
517 and pt.sponsored_flag = 'Y' ;
518
519 BEGIN
520
521 l_status := X_status ;
522 l_award_set_id := X_award_set_id ;
523
524 --db_pack_message('Account Gen :'||X_event||' asid :'||NVL(X_award_set_id,0)) ;
525 -- ==============================================================
526 -- Do not proceed if grants is not enabled for an implementation
527 -- Org.
528 -- ==============================================================
529
530 IF not gms_install.enabled then
531 return ;
532 END IF ;
533
534 IF X_event = 'REMOVE' THEN
535
536 delete from gms_award_distributions
537 where award_set_id = X_award_set_id ;
538
539 RETURN ;
540 END IF ;
541
542 IF NVL(X_event,'NULL') <> 'CREATE' THEN
543 return ;
544 END IF ;
545
546 open C_spon_project ;
547 fetch C_spon_project into l_sponsored_flag ;
548 close C_spon_project ;
549
550 IF NVL(l_sponsored_flag,'N') <> 'Y' THEN
551 return ;
552 END IF ;
553
554 x_adl_rec.expenditure_item_id := NULL ;
555 x_adl_rec.project_id := X_project_id;
556 x_adl_rec.task_id := X_task_id;
557 x_adl_rec.cost_distributed_flag := 'N';
558 x_adl_rec.cdl_line_num := NULL;
559 x_adl_rec.adl_line_num := 1;
560 x_adl_rec.distribution_value := 100 ;
561 x_adl_rec.line_type := 'R';
562 x_adl_rec.adl_status := 'A';
563 x_adl_rec.document_type := 'REQ';
564 x_adl_rec.billed_flag := 'N';
565 x_adl_rec.bill_hold_flag := NULL ;
566 x_adl_rec.award_set_id := gms_awards_dist_pkg.get_award_set_id;
567 x_adl_rec.award_id := X_award_id;
568 x_adl_rec.raw_cost := 0;
569 x_adl_rec.last_update_date := SYSDATE;
570 x_adl_rec.creation_date := SYSDATE;
571 x_adl_rec.last_updated_by := 0;
572 x_adl_rec.created_by := 0;
573 x_adl_rec.last_update_login := 0;
574
575 gms_awards_dist_pkg.create_adls(X_adl_rec);
576
577 X_award_set_id := x_adl_rec.award_set_id ;
578 l_award_set_id := x_adl_rec.award_set_id ;
579 --db_pack_message('Account Gen : awsid '||X_event||' asid :'||NVL(X_award_set_id,0)) ;
580
581 EXCEPTION
582 When others then
583 X_status := SQLCODE ;
584 raise ;
585 END account_generator_ADL ;
586
587 -- Start
588 -- 3068454 ( CHANGE REQUIRED IN GMS_POR_API.WHEN_UPDATE/INSERT_LINE TO WORK
589 -- WITH OA GUIDELINE )
590 --
591 PROCEDURE get_req_dist_AwardSetID ( X_distribution_id IN NUMBER,
592 X_award_set_id OUT NOCOPY NUMBER,
593 X_status IN OUT NOCOPY varchar2 ) is
594 l_award_set_id NUMBER ;
595
596 Cursor c_award_set_id is
597 select award_id
598 from po_req_distributions_all
599 where distribution_id = X_distribution_id ;
600
601 BEGIN
602 -- ==============================================================
603 -- Do not proceed if grants is not enabled for an implementation
604 -- Org.
605 -- ==============================================================
606 IF not gms_install.enabled then
607 return ;
608 END IF ;
609
610 open c_award_set_id ;
611 fetch c_award_set_id into l_award_set_id ;
612 close c_award_set_id ;
613
614 X_award_set_id := l_award_set_id ;
615 X_status := 'S' ;
616
617 EXCEPTION
618 When Others THEN
619 X_status := SQLCODE ;
620 RAISE ;
621 END get_req_dist_AwardSetID;
622 --
623 -- 3068454 ( CHANGE REQUIRED IN GMS_POR_API.WHEN_UPDATE/INSERT_LINE TO WORK
624 -- WITH OA GUIDELINE )
625 -- END.
626
627 -- =============================================================
628 -- Create award distribution lines when REQ DISTRIBUTION LINE
629 -- is created for a sponsored projects. This also tieback
630 -- ADL with REQ.
631 -- =============================================================
632 PROCEDURE when_insert_line ( X_distribution_id IN NUMBER,
633 X_project_id IN NUMBER,
634 X_task_id IN NUMBER,
635 X_award_id IN NUMBER,
636 X_expenditure_type IN VARCHAR2,
637 X_expenditure_item_date IN DATE,
638 --X_raw_cost IN NUMBER,
639 X_award_set_id OUT NOCOPY NUMBER,
640 X_status IN OUT NOCOPY varchar2 ) is
641
642 x_adl_rec gms_award_distributions%ROWTYPE;
643 BEGIN
644 -- ==============================================================
645 -- Do not proceed if grants is not enabled for an implementation
646 -- Org.
647 -- ==============================================================
648 IF not gms_install.enabled then
649 return ;
650 END IF ;
651
652 IF NOT IS_SPONSORED_PROJECT( X_project_id ) THEN
653 return ;
654 END IF ;
655
656 x_adl_rec.expenditure_item_id := NULL ;
657 x_adl_rec.project_id := X_project_id;
658 x_adl_rec.task_id := X_task_id;
659 x_adl_rec.cost_distributed_flag := 'N';
660 x_adl_rec.cdl_line_num := NULL;
661 x_adl_rec.adl_line_num := 1;
662 x_adl_rec.distribution_value := 100 ;
663 x_adl_rec.line_type := 'R';
664 x_adl_rec.adl_status := 'A';
665 x_adl_rec.document_type := 'REQ';
666 x_adl_rec.billed_flag := 'N';
667 x_adl_rec.bill_hold_flag := NULL ;
668 x_adl_rec.award_set_id := gms_awards_dist_pkg.get_award_set_id;
669 x_adl_rec.award_id := X_award_id;
670 x_adl_rec.raw_cost := NULL;
671 x_adl_rec.last_update_date := SYSDATE;
672 x_adl_rec.creation_date := SYSDATE;
673 x_adl_rec.last_updated_by := 0;
674 x_adl_rec.created_by := 0;
675 x_adl_rec.last_update_login := 0;
676 X_adl_rec.distribution_id := X_distribution_id ;
677 --db_pack_message('When Insert Line Test :'||X_distribution_id) ;
678
679
680 gms_awards_dist_pkg.create_adls(X_adl_rec);
681
682 UPDATE PO_REQ_DISTRIBUTIONS_ALL
683 SET award_id = x_adl_rec.award_set_id
684 where distribution_id = X_distribution_id ;
685
686 x_award_set_id := x_adl_rec.award_set_id ;
687 return ;
688 END when_insert_line ;
689
690 -- Start...
691 -- 3068454 ( CHANGE REQUIRED IN GMS_POR_API.WHEN_UPDATE/INSERT_LINE TO WORK
692 -- WITH OA GUIDELINE )
693 --
694 -- When_update_line overloading
695 -- x_award_set_id was added.
696 --
697 PROCEDURE when_update_line ( X_distribution_id IN NUMBER,
698 X_project_id IN NUMBER,
699 X_task_id IN NUMBER,
700 X_award_id IN NUMBER,
701 X_expenditure_type IN VARCHAR2,
702 X_expenditure_item_date IN DATE,
703 X_award_set_id OUT NOCOPY NUMBER,
704 X_status IN OUT NOCOPY varchar2 ) is
705 l_award_set_id NUMBER ;
706
707 Cursor c_award_set_id is
708 select award_id
709 from po_req_distributions_all
710 where distribution_id = X_distribution_id ;
711
712 BEGIN
713
714 when_update_line ( X_distribution_id,
715 X_project_id,
716 X_task_id,
717 X_award_id,
718 X_expenditure_type,
719 X_expenditure_item_date,
720 X_status ) ;
721
722 open c_award_set_id ;
723 fetch c_award_set_id into l_award_set_id ;
724 close c_award_set_id ;
725
726 X_award_set_id := l_award_set_id ;
727
728 EXCEPTION
729 WHEN OTHERS THEN
730 X_status := SQLCODE ;
731 RAISE ;
732 END when_update_line ;
733
734 --
735 -- 3068454 ( CHANGE REQUIRED IN GMS_POR_API.WHEN_UPDATE/INSERT_LINE TO WORK
736 -- WITH OA GUIDELINE )
737 -- END.............
738
739 -- ================================================================
740 -- following program unit control DML operations or Poject/task
741 -- and award. ADLS are updated for change in project/task or award.
742 -- Adls are also removed for project changed from sponsored to
743 -- non sponsored.
744 -- ================================================================
745
746 PROCEDURE when_update_line ( X_distribution_id IN NUMBER,
747 X_project_id IN NUMBER,
748 X_task_id IN NUMBER,
749 X_award_id IN NUMBER,
750 X_expenditure_type IN VARCHAR2,
751 X_expenditure_item_date IN DATE,
752 --X_raw_cost IN NUMBER,
753 X_status IN OUT NOCOPY varchar2 ) is
754
755 x_award_set_id NUMBER ;
756
757 Cursor c_award_set_id is
758 select award_id
759 from po_req_distributions_all
760 where distribution_id = X_distribution_id ;
761
762 BEGIN
763 -- ==============================================================
764 -- Do not proceed if grants is not enabled for an implementation
765 -- Org.
766 -- ==============================================================
767 IF not gms_install.enabled then
768 return ;
769 END IF ;
770
771 open c_award_set_id ;
772 fetch c_award_set_id into x_award_set_id ;
773 close c_award_set_id ;
774
775 --db_pack_message('When Update Line :'||NVL(X_award_set_id,0)) ;
776
777 IF NVL(x_award_set_id, 0) = 0 THEN
778 when_insert_line ( X_distribution_id,
779 X_project_id ,
780 X_task_id ,
781 X_award_id ,
782 X_expenditure_type ,
783 X_expenditure_item_date ,
784 --X_raw_cost ,
785 X_award_set_id ,
786 X_status ) ;
787 RETURN ;
788
789 ELSE
790 IF NOT IS_SPONSORED_PROJECT( X_project_id ) THEN
791
792 delete from gms_award_distributions
793 where award_set_id = x_award_set_id
794 and adl_status = 'A' ;
795
796 UPDATE PO_REQ_DISTRIBUTIONS_ALL
797 SET award_id = NULL
798 where distribution_id = X_distribution_id ;
799
800 RETURN ;
801 END IF ;
802
803 update gms_award_distributions
804 set project_id = X_project_id,
805 task_id = X_task_id,
806 award_id = X_award_id
807 where award_set_id = x_award_set_id
808 and adl_line_num = 1
809 and document_type= 'REQ'
810 and adl_status = 'A' ;
811
812 IF SQL%NOTFOUND THEN
813 raise no_data_found ;
814 END IF ;
815
816
817 END IF ;
818 return ;
819 EXCEPTION
820 WHEN OTHERS THEN
821 X_status := SQLCODE ;
822 RAISE ;
823 END when_update_line ;
824
825 -- =================================================================
826 -- Delete unwanted award distribution lines here.
827 -- =================================================================
828
829 PROCEDURE when_delete_line ( X_distribution_id IN NUMBER,
830 X_status IN OUT NOCOPY varchar2 ) is
831
832 x_award_set_id NUMBER ;
833
834 Cursor c_award_set_id is
835 select award_id
836 from po_req_distributions_all
837 where distribution_id = X_distribution_id ;
838
839 BEGIN
840 -- ==============================================================
841 -- Do not proceed if grants is not enabled for an implementation
842 -- Org.
843 -- ==============================================================
844 IF not gms_install.enabled then
845 return ;
846 END IF ;
847
848 open c_award_set_id ;
849 fetch c_award_set_id into x_award_set_id ;
850 close c_award_set_id ;
851
852 IF NVL(x_award_set_id, 0) > 0 THEN
853
854 delete from gms_award_distributions
855 where award_set_id = x_award_set_id
856 and adl_status = 'A' ;
857
858 END IF ;
859 return ;
860 END when_delete_line ;
861
862 --
863 -- Start : 3103564
864 -- NEW DELETE API NEEDED FOR DELETING AN AWARD DISTRIBUTION LINE
865 --
866 -- Start of comments
867 -- API name : delete_adl
868 -- Type : Public
869 -- Pre-reqs : None.
870 -- Function : Deletes a record from gms_award_distributions
871 -- table.
872 -- Parameters :
873 -- IN : p_award_set_id IN NUMBER Required
874 -- .
875 -- .
876 -- OUT : x_status OUT Varchar2
877 -- values are 'S', 'E', 'U'
878 -- fnd_api.G_RET_STS_SUCCESS
879 -- fnd_api.G_RET_STS_ERROR
880 -- fnd_api.G_RET_STS_UNEXP_ERROR
881 -- End of comments
882
883 PROCEDURE delete_adl ( p_award_set_id IN NUMBER,
884 x_status OUT NOCOPY varchar2,
885 x_err_msg OUT NOCOPY varchar2 ) is
886 l_status varchar2(1) ;
887 l_err_msg varchar2(4000) ;
888 BEGIN
889 -- ==============================================================
890 -- Do not proceed if grants is not enabled for an implementation
891 -- Org.
892 -- ==============================================================
893 l_status := FND_API.G_RET_STS_SUCCESS ;
894 l_err_msg:= NULL ;
895 x_status := l_status ;
896
897 IF not gms_install.enabled then
898 return ;
899 END IF ;
900
901 IF NVL(p_award_set_id, 0) > 0 THEN
902
903 delete from gms_award_distributions
904 where award_set_id = p_award_set_id
905 and adl_status = 'A' ;
906
907 IF SQL%NOTFOUND then
908 fnd_message.set_name('GMS','GMS_INVALID_AWARD');
909
910 l_err_msg := fnd_message.get;
911 l_status := FND_API.G_RET_STS_ERROR ;
912 END IF ;
913 END IF ;
914
915 x_err_msg:= l_err_msg ;
916 x_status := l_status ;
917 EXCEPTION
918 When Others THEN
919 l_status := FND_API.G_RET_STS_UNEXP_ERROR ;
920 l_err_msg:= SQLERRM ;
921
922 x_err_msg:= l_err_msg ;
923 x_status := l_status ;
924 END delete_adl ;
925 --
926 -- NEW DELETE API NEEDED FOR DELETING AN AWARD DISTRIBUTION LINE
927 -- End : 3103564
928 --
929
930 --=================================================================
931 -- Bug-2557041
932 -- This API used by IP to determine award distribution information
933 --=================================================================
934 PROCEDURE get_award_dist_param (p_award_dist_option OUT NOCOPY VARCHAR2,
935 p_dist_award_number OUT NOCOPY VARCHAR2,
936 p_dist_award_id OUT NOCOPY NUMBER )
937 IS
938 l_award_dist_option gms_implementations_all.award_distribution_option%TYPE;
939 l_dist_award_number gms_implementations_all.default_dist_award_number%TYPE;
940 l_dist_award_id gms_implementations_all.default_dist_award_id%TYPE;
941 CURSOR c_default_award IS
942 SELECT award_distribution_option,
943 default_dist_award_number,
944 default_dist_award_id
945 FROM gms_implementations
946 WHERE enabled ='Y';
947
948 BEGIN
949 OPEN c_default_award;
950 FETCH c_default_award
951 INTO l_award_dist_option,
952 l_dist_award_number,
953 l_dist_award_id;
954 CLOSE c_default_award;
955
956 p_award_dist_option := l_award_dist_option;
957 p_dist_award_number := l_dist_award_number;
958 p_dist_award_id := l_dist_award_id;
959
960 IF NVL(l_award_dist_option,'N') ='N' THEN
961 p_award_dist_option :='N';
962 p_dist_award_number :=NULL;
963 p_dist_award_id :=NULL;
964 END IF;
965
966 END get_award_dist_param;
967
968 --=======================================================================
969 -- Bug-2557041
970 -- following API used to validate dummy award specific validations
971 --=======================================================================
972 PROCEDURE validate_dist_award( p_project_id IN NUMBER,
973 p_task_id IN NUMBER,
974 p_award_id IN NUMBER,
975 p_expenditure_type IN VARCHAR2,
976 p_status IN OUT NOCOPY VARCHAR2,
977 p_err_msg_label OUT NOCOPY VARCHAR2 )
978 AS
979 l_award_id gms_awards_all.award_id%TYPE:=p_award_id;
980 l_dummy NUMBER;
981 l_exp_type gms_allowable_expenditures.expenditure_type%TYPE;
982 l_msg_label VARCHAR2(50);
983 l_status VARCHAR2(1);
984 l_err_msg VARCHAR2(2000);
985
986 CURSOR c_check_award IS
987 SELECT default_dist_award_id
988 FROM gms_implementations
989 WHERE enabled ='Y'
990 AND award_distribution_option ='Y';
991
992 CURSOR check_fund_pattern is
993 SELECT project_id
994 from gms_funding_patterns_all FPH,
995 gms_fp_distributions FPD
996 where FPH.project_id = p_project_id
997 and NVL(FPH.task_id,p_task_id) = p_task_id
998 and FPH.funding_pattern_id= FPD.funding_pattern_id
999 and FPH.status = 'A'
1000 and NVL(FPH.retroactive_flag,'N') = 'N' ;
1001
1002 CURSOR c_exp_type IS
1003 SELECT gae.expenditure_type
1004 FROM gms_funding_patterns gfp,
1005 gms_fp_distributions gfd,
1006 gms_allowable_expenditures gae,
1007 pa_tasks t,
1008 gms_awards ga
1009 WHERE t.task_id =p_task_id
1010 AND t.project_id =p_project_id
1011 AND gfp.project_id =p_project_id
1012 AND nvl(gfp.task_id,t.top_task_id ) =t.top_task_id
1013 AND gfp.status ='A'
1014 AND gfp.retroactive_flag ='N'
1015 AND gae.expenditure_type =p_expenditure_type
1016 AND gfp.funding_pattern_id =gfd.funding_pattern_id
1017 AND ga.award_id =gfd.award_id
1018 AND ga.allowable_schedule_id =gae.allowability_schedule_id;
1019 BEGIN
1020 l_status := 'S';
1021 l_exp_type := p_expenditure_type;
1022 l_award_id := p_award_id;
1023
1024 --===============================
1025 --1 Validate Dummy award is okay
1026 --===============================
1027 OPEN c_check_award;
1028 FETCH c_check_award INTO l_dummy;
1029
1030 IF c_check_award%NOTFOUND THEN
1031 l_status := 'E';
1032 l_msg_label :='GMS_AWD_DIST_NOT_ENABLED';
1033 END IF;
1034
1035 IF l_dummy<>l_award_id AND l_status <>'E' THEN
1036 l_status := 'E';
1037 l_msg_label:='GMS_DIST_AWD_INVALID';
1038 END IF;
1039
1040 CLOSE c_check_award;
1041
1042 -- ====================================================
1043 -- Bug : 2725486 ( Validate Funding pattern exists ) .
1044 -- =====================================================
1045 IF l_status <> 'E' THEN
1046 OPEN check_fund_pattern ;
1047 fetch check_fund_pattern into l_dummy ;
1048 IF check_fund_pattern%NOTFOUND then
1049 l_status :='E';
1050 l_msg_label :='GMS_INVALID_FUNDING_PATTERN' ;
1051 END IF ;
1052 CLOSE check_fund_pattern ;
1053 END IF ;
1054
1055
1056 --===================================
1057 --2 Validate Expenditure Type Is Okay
1058 --===================================
1059
1060 IF l_status <> 'E' THEN
1061 OPEN c_exp_type;
1062 FETCH c_exp_type INTO l_exp_type;
1063 IF c_exp_type%NOTFOUND THEN
1064 l_status :='E';
1065 l_msg_label :='GMS_EXP_TYPE_NO_PATTERN';
1066 END IF;
1067 CLOSE c_exp_type;
1068 END IF;
1069
1070 p_status :=l_status;
1071
1072 IF l_msg_label IS NOT NULL THEN
1073 P_err_msg_label :=l_msg_label;
1074 END IF;
1075
1076 EXCEPTION
1077 WHEN OTHERS THEN
1078 p_status :='U';
1079 p_err_msg_label :='GMS_UNDEFINED_EXCEPTION';
1080 raise;
1081 END validate_dist_award;
1082
1083 --=============================================================
1084 -- Bug-2557041
1085 -- The purpose of this API is to prepare for award distributions
1086 -- and kicks off award distribution engine
1087 --=============================================================
1088 PROCEDURE distribute_award(p_doc_header_id IN NUMBER,
1089 p_distribution_id IN NUMBER,
1090 p_document_source IN VARCHAR2,
1091 p_gl_encumbered_date IN DATE,
1092 p_project_id IN NUMBER,
1093 p_task_id IN NUMBER,
1094 p_dummy_award_id IN NUMBER,
1095 p_expenditure_type IN VARCHAR2,
1096 p_expenditure_organization_id IN NUMBER,
1097 p_expenditure_item_date IN DATE,
1098 p_quantity IN NUMBER,
1099 p_unit_price IN NUMBER,
1100 p_func_amount IN NUMBER,
1101 p_vendor_id IN NUMBER,
1102 p_source_type_code IN VARCHAR2,
1103 p_award_qty_obj OUT NOCOPY gms_obj_award,
1104 p_status OUT NOCOPY VARCHAR2,
1105 p_error_msg_label OUT NOCOPY VARCHAR2 )
1106 AS
1107 l_doc_header_id NUMBER;
1108 l_distribution_id NUMBER;
1109 l_document_source VARCHAR2(4);
1110 l_index INTEGER;
1111
1112 l_award_qty_obj gms_obj_award;
1113 l_dist_status VARCHAR2(5);
1114 l_status VARCHAR2(1);
1115 l_msg_label VARCHAR2(2000);
1116 l_recs_processed NUMBER;
1117 l_recs_rejected NUMBER;
1118 l_source_type_code po_requisition_lines_all.source_type_code%type;
1119
1120 CURSOR c_next_header_id IS
1121 SELECT gms_packet_header_id_s.NEXTVAL
1122 FROM DUAL;
1123
1124 CURSOR c_next_dist_id IS
1125 SELECT gms_packet_dist_id_s.NEXTVAL
1126 FROM DUAL;
1127
1128 CURSOR c_awd_dist_status IS
1129 SELECT awd.dist_status
1130 FROM gms_distributions awd
1131 WHERE awd.document_distribution_id = l_distribution_id
1132 AND awd.document_header_id = l_doc_header_id
1133 AND awd.document_type = l_document_source
1134 AND awd.dist_status <>'FABA';
1135 BEGIN
1136 -- Initilaize the Object
1137 l_award_qty_obj :=GMS_OBJ_AWARD(GMS_TYPE_VARCHAR20(),GMS_TYPE_NUMBER(),GMS_TYPE_NUMBER());
1138
1139 l_doc_header_id :=p_doc_header_id ;
1140 l_distribution_id :=p_distribution_id ;
1141 l_document_source :=p_document_source ;
1142 l_status :='S';
1143 p_status :=l_status ;
1144 l_source_type_code := p_source_type_code;
1145
1146
1147 IF not gms_install.enabled then
1148 return ;
1149 END IF ;
1150
1151 IF NVL(p_dummy_award_id,0) >= 0 THEN
1152 p_status :='S';
1153 Return ;
1154 END IF ;
1155
1156 --==============================================================
1157 -- Do not proceed if grants is enabled and Requisition type is
1158 -- Internal
1159 --==============================================================
1160 IF is_sponsored_project (p_project_id) THEN
1161 IF nvl(l_source_type_code,'INVENTORY') = 'INVENTORY' THEN
1162 p_error_msg_label := 'GMS_IP_INVALID_REQ_TYPE';
1163 p_status := 'E';
1164 return;
1165 END IF;
1166 ELSE
1167 -- 2. Nonsponsored project having award should fail.
1168 p_error_msg_label := 'GMS_AWARD_NOT_ALLOWED';
1169 p_status := 'E';
1170 return;
1171 END IF;
1172
1173
1174 GMS_POR_API.validate_dist_award(P_project_id,
1175 P_task_id,
1176 P_dummy_award_id,
1177 P_expenditure_type,
1178 l_status,
1179 l_msg_label ) ;
1180
1181 IF l_status <> 'S' THEN
1182 p_error_msg_label:= l_msg_label ;
1183 p_status := l_status ;
1184 return ;
1185 END IF ;
1186
1187 IF l_doc_header_id is NULL THEN
1188 OPEN c_next_header_id;
1189 FETCH c_next_header_id
1190 INTO l_doc_header_id;
1191 CLOSE c_next_header_id;
1192 END IF ;
1193
1194 IF l_distribution_id is NULL THEN
1195 OPEN c_next_dist_id;
1196 FETCH c_next_dist_id
1197 INTO l_distribution_id;
1198 CLOSE c_next_dist_id;
1199 END IF ;
1200
1201 IF l_Document_source = 'IREQ' THEN
1202 l_document_source:= 'REQ' ;
1203 END IF ;
1204
1205 --===========================================
1206 -- Insert Records into gms_distribution table
1207 --===========================================
1208 INSERT INTO gms_distributions
1209 ( document_header_id ,
1210 document_distribution_id,
1211 document_type,
1212 gl_date,
1213 project_id,
1214 task_id,
1215 expenditure_type,
1216 expenditure_organization_id,
1217 expenditure_item_date,
1218 quantity,
1219 unit_price,
1220 amount,
1221 dist_status,
1222 creation_date)
1223 VALUES ( l_doc_header_id,
1224 l_distribution_id,
1225 l_document_source,
1226 p_gl_encumbered_date,
1227 p_project_id,
1228 p_task_id,
1229 p_expenditure_type,
1230 p_expenditure_organization_id,
1231 p_expenditure_item_date,
1232 p_quantity,
1233 p_unit_price,
1234 p_func_amount,
1235 NULL,
1236 SYSDATE );
1237
1238 GMS_AWARD_DIST_ENG.PROC_DISTRIBUTE_RECORDS(l_doc_header_id, 'REQ',l_recs_processed,l_recs_rejected);
1239 --process the results of PROC_DISTRIBUTE_RECORDS
1240
1241 IF NVL(l_recs_processed,0) > 0 THEN
1242 --populate the return variables.
1243 SELECT a.award_number ,
1244 awdd.award_id ,
1245 awdd.quantity_distributed
1246 BULK COLLECT INTO l_award_qty_obj.award_num,
1247 l_award_qty_obj.award_id,
1248 l_award_qty_obj.quantity
1249 FROM gms_distribution_details awdd,
1250 gms_distributions awd,
1251 gms_awards_all a
1252 WHERE awd.document_distribution_id = awdd.document_distribution_id
1253 AND awd.document_header_id = awdd.document_header_id
1254 AND awd.document_distribution_id = l_distribution_id
1255 AND awd.document_header_id = l_doc_header_id
1256 AND awd.document_type = l_document_source
1257 AND awd. dist_status = 'FABA'
1258 AND awdd.award_id = a.Award_id;
1259 END IF;
1260
1261 IF NVL(l_recs_rejected,0) > 0 THEN
1262
1263 l_status :='E';--failed status
1264 OPEN c_awd_dist_status;
1265 FETCH c_awd_dist_status INTO l_dist_status;
1266 CLOSE c_awd_dist_status;
1267
1268 IF l_dist_status ='ERR01' THEN
1269 l_msg_label := 'GMS_FP_VALIDATION_FAILED';
1270 --Unable to distribute because funding pattern didn't pass validations
1271 ELSIF l_dist_status ='ERR02' THEN
1272 l_msg_label := 'GMS_FP_NOT_FOUND';
1273 --Unable to distribute because funding pattern not found
1274 ELSIF l_dist_status ='ERR03' THEN
1275 l_msg_label := 'GMS_FP_CHECK_FUNDS_FAILED';
1276 -- Unable to distribute because funding pattern doesn't have enough funds
1277
1278 END IF;
1279
1280 END IF;
1281
1282 p_status := l_status;
1283 p_award_qty_obj := l_award_qty_obj;
1284
1285 IF l_msg_label IS NOT NULL THEN
1286 P_error_msg_label :=l_msg_label;
1287 END IF;
1288
1289
1290 EXCEPTION
1291 WHEN OTHERS THEN
1292 p_status :='U';
1293 p_error_msg_label :='GMS_UNDEFINED_EXCEPTION';
1294 END distribute_award;
1295
1296
1297 FUNCTION enabled return varchar2 is
1298 begin
1299 -- ==============================================================
1300 -- Do not proceed if grants is not enabled for an implementation
1301 -- Org.
1302 -- ==============================================================
1303 IF gms_install.enabled then
1304 return 'Y' ;
1305 END IF ;
1306
1307 return 'N' ;
1308 END ;
1309
1310 END GMS_POR_API;
[Home] [Help]