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