1 package BODY GMS_PO_API_GRP as
2 --$Header: gmsgpoxb.pls 120.3 2006/03/29 21:09:33 bkattupa noship $
3
4 -- Start of comments
5 -- Declare package variables used for FND_API calls.
6 -- End of comments
7 -- ------------------
8
9 G_api_version CONSTANT NUMBER := 1.0 ;
10 G_pkg_name CONSTANT varchar2(45) := 'GMS_PO_API_GRP' ;
11 G_file_name CONSTANT varchar2(45) := 'gmspox1b.pls';
12
13
14 -- start of comments
15 -- --------------------
16 -- Standard Parameters : Standard parameters descriptions
17 -- p_api_version : This parameter is used by the api to compare the version
18 -- numbers of incoming calls to its current version number.
19 -- return an unexpected error if they are incompatible.
20 -- p_init_msg_list : This allows api called to request that the API does
21 -- the initialization of the message list on their behalf,
22 -- thus reducing the number of calls required by a caller
23 -- in order to execute an API.
24 --
25 -- p_commit : p_commit parameter is used by api caller to ask the API
26 -- to commit on their behalf after performing its function.
27 --
28 -- p_validation_level : APIs use the parameter to determine which validation steps
29 -- should be executed and which steps should be skipped.
30 -- value 0 = none validations
31 -- value 100 = FULL validations.
32 --
33 -- x_return_status : out varchar2
34 -- represents the result of all the operations performed by
35 -- the API and must have one of the following values.
36 -- G_RET_STS_SUCCESS = 'S'
37 -- G_RET_STS_ERROR = 'E'
38 -- G_RET_STS_UNEXP_ERROR= 'U'
39 --
40 -- x_msg_count : OUT NUMBER
41 -- the message count holds the number of messages in the
42 -- API message list. If this number is one then message data
43 -- holds the message in an encoded format.
44 -- x_msg_data : OUT number
45 -- message data holds the message in an encoded format.
46 -- end of comments
47 -- -----------------
48
49
50 -- Start of comments
51 -- -----------------
52 -- API Name : create_bulk_adl
53 -- Type : This is a private package program unit.
54 -- Pre Reqs : None
55 -- Function : This is used to create award distribution lines
56 -- using the bulk processing.
57 -- Logic : Loop thru all the elements of the object record
58 -- and create award distribution lines.
59 -- Parameters :
60 -- OUT : x_msg_count OUT NUMBER
61 -- Holds no. of messages in the API
62 -- message lists.
63 -- : x_msg_data OUT Varchar2
64 -- Holds the message in an encoded format.
65 -- : x_return_status OUT varchar2
66 -- Holds the result of all the operations
67 -- performed by the API.
68 -- values - G_RET_STS_SUCESS = 'S'
69 -- G_RET_STS_ERROR = 'E'
70 -- G_RET_STS_UNEXP_ERROR = 'U'
71 -- : p_interface_obj IN OUT gms_po_interface_type
72 -- SQL object that holds the value of distribution_ID,
73 -- distribution number, project, task, award set id
74 -- for bulk processing.
75 -- End of comments
76 -- ----------------
77 PROCEDURE create_bulk_adl
78 (
79 x_msg_count out nocopy number,
80 x_msg_data out nocopy varchar2,
81 x_return_status out nocopy varchar2,
82 p_interface_obj in out nocopy gms_po_interface_type) is
83
84 l_dummy NUMBER ;
85 BEGIN
86
87 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
88
89 IF NVL(p_interface_obj.distribution_id.COUNT,0) <= 0 then
90 return ;
91 END IF ;
92
93 FORALL i in p_interface_obj.distribution_id.FIRST..p_interface_obj.distribution_id.LAST
94 INSERT into gms_award_distributions
95 ( award_set_id ,
96 adl_line_num,
97 funding_pattern_id,
98 distribution_value ,
99 raw_cost,
100 document_type,
101 project_id ,
102 task_id ,
103 award_id ,
104 expenditure_item_id ,
105 cdl_line_num ,
106 ind_compiled_set_id ,
107 gl_date ,
108 request_id ,
109 line_num_reversed ,
110 resource_list_member_id ,
111 output_tax_classification_code ,
112 output_tax_exempt_flag ,
113 output_tax_exempt_reason_code ,
114 output_tax_exempt_number ,
115 adl_status ,
116 fc_status ,
117 line_type ,
118 capitalized_flag ,
119 capitalizable_flag ,
120 reversed_flag ,
121 revenue_distributed_flag ,
122 billed_flag ,
123 bill_hold_flag ,
124 distribution_id ,
125 po_distribution_id ,
126 invoice_distribution_id ,
127 parent_award_set_id ,
128 invoice_id ,
129 parent_adl_line_num ,
130 distribution_line_number ,
131 burdenable_raw_cost ,
132 cost_distributed_flag ,
133 last_update_date ,
134 last_updated_by ,
135 created_by ,
136 creation_date ,
137 last_update_login ,
138 billable_flag
139 )
140 SELECT gms_awards_dist_pkg.get_award_set_id ,
141 1, --adl_line_num,
142 funding_pattern_id,
143 distribution_value ,
144 raw_cost,
145 'PO' , --document_type,
146 project_id ,
147 task_id ,
148 award_id ,
149 NULL, --expenditure_item_id ,
150 cdl_line_num ,
151 NULL, --ind_compiled_set_id ,
152 gl_date ,
153 p_interface_obj.distribution_num(i), --request_id ,
154 line_num_reversed ,
155 NULL, --resource_list_member_id ,
156 output_tax_classification_code ,
157 output_tax_exempt_flag ,
158 output_tax_exempt_reason_code ,
159 output_tax_exempt_number ,
160 'A', --adl_status ,
161 'N', --fc_status ,
162 line_type ,
163 capitalized_flag ,
164 capitalizable_flag ,
165 reversed_flag ,
166 revenue_distributed_flag ,
167 billed_flag ,
168 bill_hold_flag ,
169 NULL, --distribution_id ,
170 p_interface_obj.distribution_id(i), --po_distribution_id ,
171 NULL, --invoice_distribution_id ,
172 parent_award_set_id ,
173 NULL, --invoice_id ,
174 parent_adl_line_num ,
175 NULL, --distribution_line_number ,
176 NULL, --burdenable_raw_cost ,
177 cost_distributed_flag ,
178 SYSDATE, --last_update_date ,
179 fnd_global.user_id , --last_updated_by ,
180 fnd_global.user_id , --created_by ,
181 SYSDATE, --creation_date ,
182 last_update_login ,
183 billable_flag
184 from gms_award_distributions
185 where award_set_id = p_interface_obj.award_set_id_in(i)
186 and adl_line_num = 1 ;
187
188 FOR i in 1..p_interface_obj.distribution_id.count LOOP
189
190 select award_set_id
191 into l_dummy
192 from gms_award_distributions
193 where po_distribution_id = p_interface_obj.distribution_id(i)
194 and document_type = 'PO'
195 and adl_status = 'A'
196 and fc_status = 'N' ;
197
198 p_interface_obj.award_set_id_out(i) := l_dummy ;
199
200 END LOOP;
201
202 END create_bulk_adl;
203
204 -- ==================
205
206
207 -- Start of comments
208 -- -----------------
209 -- API Name : CREATE_COPY_DOC_ADL
210 -- Type : This is a Public package program unit.
211 -- Pre Reqs : None
212 -- Function : This API is for creating Award Distribution Lines for new Purchase
213 -- Order Distributions created through Copy Document feature in PO.
214 -- Logic : Copy award distribution line from the award set id passed.
215 -- Parameters :
216 -- : Standard parameters
217 -- p_api_version, p_commit, p_init_msg_list,
218 -- p_validation_level, x_msg_count, x_msg_data,
219 -- x_return_status
220 -- IN
221 -- : p_distribution_id NUMBER
222 -- Purchase order distribution line ID
223 -- : p_distribution_num NUMBER
224 -- Distribution line number
225 -- : p_project_id NUMBER
226 -- Project ID on the distribution line
227 -- : p_task_id NUMBER
228 -- Task on distribution line
229 -- : p_award_set_id NUMBER
230 -- Source award distribution line reference.
231 -- End of comments
232 -- ----------------
233
234 PROCEDURE CREATE_COPY_DOC_ADL
235 ( p_api_version IN NUMBER,
236 p_commit IN VARCHAR2,
237 p_init_msg_list IN VARCHAR2,
238 p_validation_level IN NUMBER,
239 x_msg_count OUT NOCOPY NUMBER,
240 x_msg_data OUT NOCOPY VARCHAR2,
241 x_return_status OUT NOCOPY VARCHAR2,
242 p_distribution_id IN NUMBER,
243 p_distribution_num IN NUMBER,
244 p_project_id IN NUMBER,
245 p_task_id IN NUMBER,
246 p_award_set_id IN NUMBER ) is
247
248 l_adls_rec gms_award_distributions%ROWTYPE;
249 l_cursor_found boolean ;
250 l_msg_count NUMBER ;
251 l_msg_data varchar2(2000) ;
252 l_return_status varchar2(1) ;
253 l_api_name varchar2(50) := 'create_copy_doc_adl' ;
254
255 cursor c_adl is
256 select *
257 from gms_award_distributions
258 where award_set_id = p_award_set_id
259 and adl_line_num = 1 ;
260 begin
261 -- Standrad call to check API compatibility.
262
263 IF NOT FND_API.Compatible_API_Call( G_api_version,
264 p_api_version,
265 l_api_name,
266 G_pkg_name ) THEN
267
268 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
269 END IF ;
270
271 -- Initialize message list if p_init_msg_list is set to TRUE
272 --
273 IF FND_API.to_boolean( p_init_msg_list) THEN
274
275 FND_MSG_PUB.initialize ;
276
277 END IF ;
278
279 -- Initialize API return status to success.
280 --
281 l_return_status := FND_API.G_RET_STS_SUCCESS ;
282
283 --
284 -- Determine the validation level
285 --
286 IF p_validation_level >= FND_API.G_VALID_LEVEL_FULL THEN
287
288 -- No validation logic required for this API.
289 --
290 NULL ;
291 END IF ;
292
293 -- Program logic begins here
294
295 if p_award_set_id is NULL then
296 return ;
297 end if ;
298
299 --
300 -- Get the award distribution line.
301 --
302
303 open c_adl ;
304 fetch c_adl into l_adls_rec ;
305 l_cursor_found := c_adl%FOUND ;
306 close c_adl ;
307
308 --
309 -- Check error if ADL doesn't exists.
310 --
311
312 if l_cursor_found = FALSE THEN
313 -- Error processing and return from here.
314 --
315 FND_MESSAGE.set_name('GMS', 'GMS_ADL_NOT_FOUND') ;
316 FND_MSG_PUB.add ;
317 RAISE FND_API.G_EXC_ERROR ;
318 end if ;
319
320 --
321 -- ADL creation starts here
322 --
323
324 IF NVL(l_adls_rec.po_distribution_id,0) <> p_distribution_id THEN
325 -- Award distribution line must be created here.
326 l_adls_rec.award_set_id := gms_awards_dist_pkg.get_award_set_id ;
327 l_adls_rec.adl_line_num := 1 ;
328 l_adls_rec.document_type := 'PO' ;
329 l_adls_rec.project_id := p_project_id ;
330 l_adls_rec.task_id := p_task_id ;
331 l_adls_rec.request_id := p_distribution_num ;
332 l_adls_rec.adl_status := 'A' ;
333 l_adls_rec.fc_status := 'N' ;
334
335 l_adls_rec.distribution_id := NULL ;
336 l_adls_rec.invoice_id := NULL ;
337 l_adls_rec.distribution_line_number := NULL ;
338 l_adls_rec.invoice_distribution_id := NULL ;
339 l_adls_rec.expenditure_item_id := NULL ;
340 l_adls_rec.po_distribution_id := p_distribution_id ;
341
342 l_adls_rec.burdenable_raw_cost := NULL ;
343
344 l_adls_rec.creation_date := SYSDATE ;
345 l_adls_rec.last_update_date := SYSDATE ;
346 l_adls_rec.last_updated_by := fnd_global.user_id ;
347 l_adls_rec.created_by := fnd_global.user_id ;
348
349 gms_awards_dist_pkg.create_adls(l_adls_rec) ;
350
351 update po_distributions_all
352 set award_id = l_adls_rec.award_set_id
353 where po_distribution_id = p_distribution_id ;
354
355 --po_distributions_grp.update_award_id_po( p_api_version => p_api_version,
356 -- p_commit => p_commit,
357 -- p_init_msg_list => p_init_msg_list,
358 -- p_validation_level => p_validation_level,
359 -- x_msg_count => l_msg_count,
360 -- x_msg_data => l_msg_data,
361 -- x_return_status => l_return_status,
362 -- p_award_set_id => l_adls_rec.award_set_id,
363 -- p_distribution_id => p_distribution_id ) ;
364 --
365
366
367 END IF ;
368
369 x_msg_count := l_msg_count ;
370 x_msg_data := l_msg_data ;
371 x_return_status := l_return_status ;
372 EXCEPTION
373 WHEN FND_API.G_EXC_ERROR then
374 x_return_status := FND_API.G_RET_STS_ERROR ;
375 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
376 p_count => x_msg_count ,
377 p_data => x_msg_data ) ;
378
379 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
381 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
382 p_count => x_msg_count ,
383 p_data => x_msg_data ) ;
384 WHEN OTHERS THEN
385
386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
387 IF FND_MSG_PUB.check_msg_level( fnd_msg_pub.g_msg_lvl_unexp_error) THEN
388 fnd_msg_pub.add_exc_msg( G_pkg_name, l_api_name ) ;
389 END IF ;
390 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
391 p_count => x_msg_count ,
392 p_data => x_msg_data ) ;
393 end CREATE_COPY_DOC_ADL ;
394 -- ===========================
395
396 -- Start of comments
397 -- -----------------
398 -- API Name : CREATE_AUTOCREATE_PO_ADL
399 -- Type : This is a Public package program unit.
400 -- Pre Reqs : None
401 -- Function : This API is for creating Award Distribution Lines for new Purchase
402 -- Order Distributions created through AUTOCREATE function
403 -- Logic : Copy award distribution line from the award set id passed using
404 -- bulk processing.
405 -- Calling API : po_interface_s.create_distributions
406 -- Parameters :
407 -- : Standard parameters
408 -- p_api_version, p_commit, p_init_msg_list,
409 -- p_validation_level, x_msg_count, x_msg_data,
410 -- x_return_status
411 -- IN OUT
412 -- : p_interface_obj gms_po_interface_type
413 -- This is a SQL object having a following table
414 -- elements.
415 -- distribution_id - Holds distribution ID
416 -- distribution_num Holds distribution number
417 -- project_id Holds Project ID
418 -- task_id Holds Task ID
419 -- award_set_id_in Holds Award Set Id Reference
420 -- award_set_id_out Holds return value of new
421 -- award distribution line
422 -- reference.
423 -- End of comments
424 -- ----------------
425
426 PROCEDURE CREATE_AUTOCREATE_PO_ADL
427 ( p_api_version in number,
428 p_commit in varchar2,
429 p_init_msg_list in varchar2,
430 p_validation_level in number,
431 x_msg_count out nocopy number,
432 x_msg_data out nocopy varchar2,
433 x_return_status out nocopy varchar2,
434 p_interface_obj in out nocopy gms_po_interface_type) is
435
436 l_msg_count number ;
437 l_msg_data varchar2(2000) ;
438 l_return_status varchar2(1) ;
439 l_api_name varchar2(45) := 'create_autocreate_po_adl' ;
440 begin
441
442 -- Standrad call to check API compatibility.
443 IF NOT FND_API.Compatible_API_Call( G_api_version,
444 p_api_version,
445 l_api_name,
446 G_pkg_name) THEN
447
448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
449 END IF ;
450
451 -- Initialize message list if p_init_msg_list is set to TRUE
452 --
453 IF FND_API.to_boolean( p_init_msg_list) THEN
454
455 FND_MSG_PUB.initialize ;
456
457 END IF ;
458
459 -- Initialize API return status to success.
460 --
461 l_return_status := FND_API.G_RET_STS_SUCCESS ;
462
463 --
464 -- Determine the validation level
465 --
466 IF p_validation_level >= FND_API.G_VALID_LEVEL_FULL THEN
467
468 -- No validation logic required for this API.
469 --
470 NULL ;
471 END IF ;
472
473 -- Program logic begins here
474
475 create_bulk_adl ( x_msg_count => l_msg_count,
476 x_msg_data => l_msg_data,
477 x_return_status => l_return_status,
478 p_interface_obj => p_interface_obj ) ;
479
480 x_msg_count := l_msg_count ;
481 x_msg_data := l_msg_data ;
482 x_return_status := l_return_status ;
483
484 EXCEPTION
485 WHEN FND_API.G_EXC_ERROR then
486 x_return_status := FND_API.G_RET_STS_ERROR ;
487 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
488 p_count => x_msg_count ,
489 p_data => x_msg_data ) ;
490
491 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
492 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
493 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
494 p_count => x_msg_count ,
495 p_data => x_msg_data ) ;
496 WHEN OTHERS THEN
497 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
498
499 IF FND_MSG_PUB.check_msg_level( fnd_msg_pub.g_msg_lvl_unexp_error) THEN
500 fnd_msg_pub.add_exc_msg( G_pkg_name, l_api_name ) ;
501 END IF ;
502
503 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
504 p_count => x_msg_count ,
505 p_data => x_msg_data ) ;
506 end CREATE_AUTOCREATE_PO_ADL ;
507
508 -- Start of comments
509 -- -----------------
510 -- API Name : CREATE_RELEASE_ADL
511 -- Type : This is a Public package program unit.
512 -- Pre Reqs : None
513 -- Function : This API is for creating Award Distribution Lines for new Purchase
514 -- Order Distributions created through Create release concurrent process
515 -- function
516 -- Logic : Copy award distribution line from the award set id passed using
517 -- bulk processing.
518 -- Calling API : PO_RELGEN_PKG.create_release_distribution
519 -- Parameters :
520 -- : Standard parameters
521 -- p_api_version, p_commit, p_init_msg_list,
522 -- p_validation_level, x_msg_count, x_msg_data,
523 -- x_return_status
524 -- IN OUT
525 -- : p_interface_obj gms_po_interface_type
526 -- This is a SQL object having a following table
527 -- elements.
528 -- distribution_id - Holds distribution ID
529 -- distribution_num Holds distribution number
530 -- project_id Holds Project ID
531 -- task_id Holds Task ID
532 -- award_set_id_in Holds Award Set Id Reference
533 -- award_set_id_out Holds return value of new
534 -- award distribution line
535 -- reference.
536 -- End of comments
537 -- ----------------
538 PROCEDURE CREATE_RELEASE_ADL
539 ( p_api_version in number,
540 p_commit in varchar2,
541 p_init_msg_list in varchar2,
542 p_validation_level in number,
543 x_msg_count out nocopy number,
544 x_msg_data out nocopy varchar2,
545 x_return_status out nocopy varchar2,
546 p_interface_obj in out nocopy gms_po_interface_type) is
547
548 l_msg_count NUMBER ;
549 l_msg_data varchar2(2000) ;
550 l_return_status varchar2(1) ;
551 l_api_name varchar2(45) := 'create_release_adl' ;
552 begin
553
554 -- Standrad call to check API compatibility.
555 IF NOT FND_API.Compatible_API_Call( G_api_version,
556 p_api_version,
557 l_api_name,
558 G_pkg_name) THEN
559
560 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
561 END IF ;
562
563 -- Initialize message list if p_init_msg_list is set to TRUE
564 --
565 IF FND_API.to_boolean( p_init_msg_list) THEN
566
567 FND_MSG_PUB.initialize ;
568
569 END IF ;
570
571 -- Initialize API return status to success.
572 --
573 l_return_status := FND_API.G_RET_STS_SUCCESS ;
574
575 --
576 -- Determine the validation level
577 --
578 IF p_validation_level >= FND_API.G_VALID_LEVEL_FULL THEN
579
580 -- No validation logic required for this API.
581 --
582 NULL ;
583 END IF ;
584
585 -- Program logic begins here
586
587 create_bulk_adl ( x_msg_count => l_msg_count,
588 x_msg_data => l_msg_data,
589 x_return_status => l_return_status,
590 p_interface_obj => p_interface_obj ) ;
591
592 x_msg_count := l_msg_count ;
593 x_msg_data := l_msg_data ;
594 x_return_status := l_return_status ;
595
596 EXCEPTION
597 WHEN FND_API.G_EXC_ERROR then
598 x_return_status := FND_API.G_RET_STS_ERROR ;
599 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
600 p_count => x_msg_count ,
601 p_data => x_msg_data ) ;
602
603 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
605 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
606 p_count => x_msg_count ,
607 p_data => x_msg_data ) ;
608 WHEN OTHERS THEN
609 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
610
611 IF FND_MSG_PUB.check_msg_level( fnd_msg_pub.g_msg_lvl_unexp_error) THEN
612 fnd_msg_pub.add_exc_msg( G_pkg_name, l_api_name ) ;
613 END IF ;
614
615 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
616 p_count => x_msg_count ,
617 p_data => x_msg_data ) ;
618 end CREATE_RELEASE_ADL ;
619
620
621 -- =====================
622
623 -- Function : GET_AWARD_NUMBER
624
625 -- API to return the award number
626
627 -- Calling API : PO and REQ summary window post_query triggers.
628
629 -- define function purity WNDS, WNPS for this function.
630
631 FUNCTION GET_AWARD_NUMBER
632 ( p_api_version in number,
633 p_commit in varchar2,
634 p_init_msg_list in varchar2,
635 p_validation_level in number,
636 x_msg_count out nocopy number,
637 x_msg_data out nocopy varchar2,
638 x_return_status out nocopy varchar2,
639 p_award_set_id in number) return varchar2 is
640
641 l_msg_count number ;
642 l_msg_data varchar2(2000) ;
643 l_return_status varchar2(1) ;
644 l_api_name varchar2(45) := 'get_award_number' ;
645
646 l_award_number gms_awards_all.award_number%TYPE ;
647
648 cursor c1 is
649 select awd.award_number
650 from gms_awards_all awd,
651 gms_award_distributions adl
652 where adl.award_id = awd.award_id
653 and adl.award_set_id = p_award_set_id
654 and adl.adl_line_num = 1 ;
655 begin
656
657 -- Standrad call to check API compatibility.
658 IF NOT FND_API.Compatible_API_Call( G_api_version,
659 p_api_version,
660 l_api_name,
661 G_pkg_name) THEN
662
663 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
664 END IF ;
665
666 -- Initialize message list if p_init_msg_list is set to TRUE
667 --
668 IF FND_API.to_boolean( p_init_msg_list) THEN
669
670 FND_MSG_PUB.initialize ;
671
672 END IF ;
673
674 -- Initialize API return status to success.
675 --
676 l_return_status := FND_API.G_RET_STS_SUCCESS ;
677
678 --
679 -- Determine the validation level
680 --
681 IF p_validation_level >= FND_API.G_VALID_LEVEL_FULL THEN
682
683 -- No validation logic required for this API.
684 --
685 NULL ;
686 END IF ;
687
688 -- Program logic begins here
689 IF p_award_set_id is NULL then
690 return NULL ;
691 END IF ;
692
693 open c1 ;
694 fetch c1 into l_award_number ;
695
696 IF c1%notfound then
697 close c1 ;
698 raise no_data_found ;
699 END IF ;
700
701 close c1 ;
702
703 x_return_status := l_return_status;
704
705 return l_award_number ;
706
707 EXCEPTION
708 WHEN OTHERS THEN
709 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
710
711 IF FND_MSG_PUB.check_msg_level( fnd_msg_pub.g_msg_lvl_unexp_error) THEN
712 fnd_msg_pub.add_exc_msg( G_pkg_name, l_api_name ) ;
713 END IF ;
714
715 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
716 p_count => x_msg_count ,
717 p_data => x_msg_data ) ;
718 end GET_AWARD_NUMBER ;
719 -- ==================
720
721
722 -- Start of comments
723 -- -----------------
724 -- API Name : GET_AWARD_ID
725 -- Type : This is a Public package program unit.
726 -- Return value : NUMBER ( award_id )
727 -- Pre Reqs : None
728 -- Function : API will determine the value of award_id for the award_number
729 -- passed.
730 -- Logic : select the award id from gms_awards_all
731 -- Calling API : PO_PDOI_DISTRIBUTIONS_SV1 and PO_VENDORS_SV1
732 -- Parameters :
733 -- : Standard parameters
734 -- p_api_version, p_commit, p_init_msg_list,
735 -- p_validation_level, x_msg_count, x_msg_data,
736 -- x_return_status
737 -- IN OUT
738 -- : p_award_number varchar2
739 -- award number defined in the interface tables.
740 -- End of comments
741 -- ----------------
742
743
744 FUNCTION GET_AWARD_ID
745 ( p_api_version IN number,
746 p_commit IN varchar2,
747 p_init_msg_list IN varchar2,
748 p_validation_level IN number,
749 x_msg_count out nocopy number,
750 x_msg_data out nocopy varchar2,
751 x_return_status out nocopy varchar2,
752 p_award_number IN varchar2) return number is
753
754 l_award_id NUMBER ;
755 l_msg_count NUMBER ;
756 l_msg_data varchar2(2000) ;
757 l_return_status varchar2(1) := fnd_api.G_RET_STS_SUCCESS;
758 l_api_name varchar2(50) := 'get_award_id' ;
759 l_gms_enabled BOOLEAN ;
760
761 BEGIN
762 -- STUB API for PO.
763 --
764
765
766 -- Standrad call to check API compatibility.
767
768 IF NOT FND_API.Compatible_API_Call( G_api_version,
769 p_api_version,
770 l_api_name,
771 G_pkg_name ) THEN
772
773 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
774 END IF ;
775
776 -- Initialize message list if p_init_msg_list is set to TRUE
777 --
778 IF FND_API.to_boolean( p_init_msg_list) THEN
779
780 FND_MSG_PUB.initialize ;
781
782 END IF ;
783
784 -- Initialize API return status to success.
785 --
786 l_return_status := FND_API.G_RET_STS_SUCCESS ;
787
788 --
789 -- Determine the validation level
790 --
791 IF p_validation_level >= FND_API.G_VALID_LEVEL_FULL THEN
792
793 -- No validation logic required for this API.
794 --
795 NULL ;
796 END IF ;
797
798 l_gms_enabled := gms_install.enabled ;
799
800 IF NOT l_gms_enabled THEN
801 return l_award_id ;
802 END IF ;
803
804 IF p_award_number is NULL THEN
805 return l_award_id ;
806 END IF ;
807
808 select award_id
809 into l_award_id
810 from gms_awards_all
811 where award_number = p_award_number ;
812
813 x_msg_count := l_msg_count ;
814 x_msg_data := l_msg_data ;
815 x_return_status := l_return_status ;
816 return l_award_id ;
817 EXCEPTION
818 WHEN NO_DATA_FOUND THEN
819 x_return_status := FND_API.G_RET_STS_ERROR ;
820 FND_MESSAGE.set_name('GMS', 'GMS_INVALID_AWARD') ;
821 FND_MSG_PUB.add ;
822 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
823 p_count => x_msg_count ,
824 p_data => x_msg_data ) ;
825
826 RETURN l_award_id ;
827
828 WHEN FND_API.G_EXC_ERROR then
829 x_return_status := FND_API.G_RET_STS_ERROR ;
830 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
831 p_count => x_msg_count ,
832 p_data => x_msg_data ) ;
833
834 RETURN l_award_id ;
835
836 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
838 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
839 p_count => x_msg_count ,
840 p_data => x_msg_data ) ;
841 RETURN l_award_id ;
842
843 WHEN OTHERS THEN
844
845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
846 IF FND_MSG_PUB.check_msg_level( fnd_msg_pub.g_msg_lvl_unexp_error) THEN
847 fnd_msg_pub.add_exc_msg( G_pkg_name, l_api_name ) ;
848 END IF ;
849 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
850 p_count => x_msg_count ,
851 p_data => x_msg_data ) ;
852 RETURN l_award_id ;
853 END GET_AWARD_ID ;
854
855
856 -- Start of comments
857 -- -----------------
858 -- API Name : validate_transaction
859 -- Type : This is a Public package program unit.
860 -- Pre Reqs : None
861 -- Function : Validate award ,project task related standard validations
862 -- Validations are executed only if grants is enabled.
863 --
864 -- a. Execute validations if grants is implemented.
865 -- b. Make sure that award is entered for a sponsored project.
866 -- c. Make sure that award is not entered for a no sponsored project.
867 -- d. Standard grants validations.
868 -- Logic : call gms standard validations.
869 -- Calling API : PO_PDOI_DISTRIBUTIONS_SV1 and PO_VENDORS_SV1
870 -- Parameters :
871 -- : Standard parameters
872 -- p_api_version, p_commit, p_init_msg_list,
873 -- p_validation_level, x_msg_count, x_msg_data,
874 -- x_return_status
875 -- IN OUT
876 -- : p_project_id ( value of project id )
877 -- p_task_id ( value of task id )
878 -- p_award_id ( value of award id )
879 -- p_expenditure_type ( expenditure type )
880 -- p_expenditure_item_date ( expenditure item date )
881 -- p_calling_module ( package name.procedure name )
882 -- End of comments
883 -- ----------------
884 -- BUG:4739557 x_return_status not initialized in validate_transactions.
885 --
886 PROCEDURE validate_transaction
887 ( p_api_version in number,
888 p_commit in varchar2,
889 p_init_msg_list in varchar2,
890 p_validation_level in number,
891 x_msg_count out nocopy number,
892 x_msg_data out nocopy varchar2,
893 x_return_status out nocopy varchar2,
894 p_project_id in number,
895 p_task_id in number,
896 p_award_id in number,
897 p_expenditure_type in varchar2,
898 p_expenditure_item_date in date,
899 p_calling_module in varchar2) is
900
901 l_msg_count NUMBER ;
902 l_msg_data varchar2(2000) ;
903 l_return_status varchar2(1) := fnd_api.G_RET_STS_SUCCESS;
904 l_api_name varchar2(50) := 'validate_transaction' ;
905 l_sponsored_flag varchar2(1) ;
906 l_gms_enabled BOOLEAN ;
907 l_spon_project BOOLEAN ;
908 l_project_type_class_code pa_project_types_all.project_type_class_code%TYPE ;
909
910 cursor C_spon_project is
911 select gpt.sponsored_flag , pt.project_type_class_code
912 from pa_projects_all p,
913 gms_project_types gpt ,
914 pa_project_types pt
915 where p.project_id = NVL(p_project_id,0)
916 and p.project_type = gpt.project_type
917 and p.project_type = pt.project_type ;
918
919 BEGIN
920
921 -- Standrad call to check API compatibility.
922
923 IF NOT FND_API.Compatible_API_Call( G_api_version,
924 p_api_version,
925 l_api_name,
926 G_pkg_name ) THEN
927
928 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
929 END IF ;
930
931 -- Initialize message list if p_init_msg_list is set to TRUE
932 --
933 IF FND_API.to_boolean( p_init_msg_list) THEN
934
935 FND_MSG_PUB.initialize ;
936
937 END IF ;
938
939 -- Initialize API return status to success.
940 --
941 l_return_status := FND_API.G_RET_STS_SUCCESS ;
942 -- ----------------
943 -- BUG:4739557 x_return_status not initialized in validate_transactions.
944 --
945 x_return_status := l_return_status ;
946
947 --
948 -- Determine the validation level
949 --
950 IF p_validation_level >= FND_API.G_VALID_LEVEL_FULL THEN
951
952 -- No validation logic required for this API.
953 --
954 NULL ;
955 END IF ;
956
957 l_gms_enabled := GMS_INSTALL.enabled ;
958 l_spon_project := FALSE ;
959
960 open C_spon_project ;
961 fetch C_spon_project into l_sponsored_flag , l_project_type_class_code;
962 close C_spon_project ;
963
964 IF NVL(l_sponsored_flag,'N') = 'Y' THEN
965 l_spon_project := TRUE ;
966 END IF ;
967
968 IF l_spon_project then
969 IF p_award_id is null THEN
970 x_return_status := FND_API.G_RET_STS_ERROR ;
971 FND_MESSAGE.set_name('GMS', 'GMS_AWARD_REQUIRED') ;
972 FND_MSG_PUB.add ;
973 RAISE FND_API.G_EXC_ERROR ;
974 END IF ;
975 ELSE
976 IF p_award_id is not NULL THEN
977
978 x_return_status := FND_API.G_RET_STS_ERROR ;
979 FND_MESSAGE.set_name('GMS', 'GMS_AWARD_NOT_ALLOWED') ;
980 FND_MSG_PUB.add ;
981 RAISE FND_API.G_EXC_ERROR ;
982 ELSE
983 RETURN ;
984 END IF ;
985 END IF ;
986
987
988 IF not l_gms_enabled THEN
989 return ;
990 END IF ;
991
992 if l_project_type_class_code = 'CONTRACT' then
993 x_return_status := FND_API.G_RET_STS_ERROR ;
994 fnd_message.set_name('GMS','GMS_IP_INVALID_PROJ_TYPE');
995 FND_MSG_PUB.add ;
996 RAISE FND_API.G_EXC_ERROR ;
997 end if;
998
999 gms_transactions_pub.validate_transaction(p_project_id => p_project_id,
1000 p_task_id => p_task_id,
1001 p_award_id => p_award_id,
1002 p_expenditure_type => p_expenditure_type,
1003 p_expenditure_item_date => p_expenditure_item_date,
1004 p_calling_module => 'TXNVALID',
1005 p_outcome => l_msg_data );
1006 IF l_msg_data is NOT NULL then
1007
1008 x_return_status := FND_API.G_RET_STS_ERROR ;
1009 fnd_message.set_name('GMS',l_msg_data );
1010 FND_MSG_PUB.add ;
1011 RAISE FND_API.G_EXC_ERROR ;
1012
1013 END IF ;
1014
1015 x_msg_count := l_msg_count ;
1016 x_msg_data := l_msg_data ;
1017 x_return_status := l_return_status ;
1018 EXCEPTION
1019 WHEN FND_API.G_EXC_ERROR then
1020 x_return_status := FND_API.G_RET_STS_ERROR ;
1021 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
1022 p_count => x_msg_count ,
1023 p_data => x_msg_data ) ;
1024
1025 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1026 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1027 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
1028 p_count => x_msg_count ,
1029 p_data => x_msg_data ) ;
1030 WHEN OTHERS THEN
1031
1032 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1033 IF FND_MSG_PUB.check_msg_level( fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1034 fnd_msg_pub.add_exc_msg( G_pkg_name, l_api_name ) ;
1035 END IF ;
1036 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
1037 p_count => x_msg_count ,
1038 p_data => x_msg_data ) ;
1039 END validate_transaction ;
1040
1041
1042 -- Start of comments
1043 -- -----------------
1044 -- API Name : get_new_award_set_id
1045 -- Type : This is a Public package program unit.
1046 -- Pre Reqs : None
1047 -- Function : Return award set id next sequence number.
1048 -- return null when grants is not implemented.
1049 -- Logic : get the next value of award set id sequence
1050 -- Calling API : PO_PDOI_DISTRIBUTIONS_SV1 and PO_VENDORS_SV1
1051 -- Parameters :
1052 -- : Standard parameters
1053 -- p_api_version, p_commit, p_init_msg_list,
1054 -- p_validation_level, x_msg_count, x_msg_data,
1055 -- x_return_status
1056 -- End of comments
1057 -- ----------------
1058 FUNCTION get_new_award_set_id return number is
1059 l_award_set_id NUMBER ;
1060 BEGIN
1061 IF gms_install.enabled then
1062 l_award_set_id := gms_awards_dist_pkg.get_award_set_id ;
1063 END IF ;
1064 return l_award_set_id ;
1065 END get_new_award_set_id ;
1066
1067
1068 -- Start of comments
1069 -- -----------------
1070 -- API Name : gms_enabled
1071 -- Type : This is a Public package program unit.
1072 -- Pre Reqs : None
1073 -- Function : Return TRUE if grants is enabled
1074 -- return FALSE if grants is not enabled.
1075 -- Logic : check if grants is enabled or not.
1076 -- Calling API : PO_PDOI_DISTRIBUTIONS_SV1 and PO_VENDORS_SV1
1077 -- Parameters :
1078 -- : Standard parameters
1079 -- p_api_version, p_commit, p_init_msg_list,
1080 -- p_validation_level, x_msg_count, x_msg_data,
1081 -- x_return_status
1082 -- End of comments
1083 -- ----------------
1084 FUNCTION gms_enabled return boolean is
1085 l_gms_enabled BOOLEAN := FALSE ;
1086 BEGIN
1087
1088 l_gms_enabled := gms_install.enabled ;
1089
1090 return l_gms_enabled ;
1091
1092 END gms_enabled ;
1093
1094 -- Start of comments
1095 -- -----------------
1096 -- API Name : create_pdoi_adls
1097 -- Type : This is a Public package program unit.
1098 -- Pre Reqs : None
1099 -- Function : create award distribution line for the passed award set id
1100 -- and po distribution id.
1101 -- Logic : create award distribution line.
1102 -- Calling API : PO_PDOI_DISTRIBUTIONS_SV1 and PO_VENDORS_SV1
1103 -- Parameters :
1104 -- : Standard parameters
1105 -- p_api_version, p_commit, p_init_msg_list,
1106 -- p_validation_level, x_msg_count, x_msg_data,
1107 -- x_return_status
1108 -- IN OUT
1109 -- :
1110 -- p_distribution_id value of po distribution id
1111 -- p_distribution_num value of distribution number
1112 -- p_project_id project id
1113 -- p_task_id task id
1114 -- p_award_id award id
1115 -- p_award_set_id award set id
1116 --
1117 -- End of comments
1118 -- ----------------
1119 PROCEDURE create_pdoi_adls
1120 ( p_api_version IN NUMBER,
1121 p_commit IN VARCHAR2,
1122 p_init_msg_list IN VARCHAR2,
1123 p_validation_level IN NUMBER,
1124 x_msg_count OUT NOCOPY NUMBER,
1125 x_msg_data OUT NOCOPY VARCHAR2,
1126 x_return_status OUT NOCOPY VARCHAR2,
1127 p_distribution_id IN NUMBER,
1128 p_distribution_num IN NUMBER,
1129 p_project_id IN NUMBER,
1130 p_task_id IN NUMBER,
1131 p_award_id IN NUMBER,
1132 p_award_set_id IN NUMBER ) is
1133
1134 l_msg_count NUMBER ;
1135 l_msg_data varchar2(2000) ;
1136 l_return_status varchar2(1) := fnd_api.G_RET_STS_SUCCESS;
1137 l_api_name varchar2(50) := 'create_pdoi_adls' ;
1138 l_adl_rec gms_award_distributions%ROWTYPE;
1139 l_gms_enabled BOOLEAN ;
1140
1141 BEGIN
1142 --
1143 -- STUB API
1144 --
1145 -- Standrad call to check API compatibility.
1146
1147 IF NOT FND_API.Compatible_API_Call( G_api_version,
1148 p_api_version,
1149 l_api_name,
1150 G_pkg_name ) THEN
1151
1152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1153 END IF ;
1154
1155 -- Initialize message list if p_init_msg_list is set to TRUE
1156 --
1157 IF FND_API.to_boolean( p_init_msg_list) THEN
1158
1159 FND_MSG_PUB.initialize ;
1160
1161 END IF ;
1162
1163 -- Initialize API return status to success.
1164 --
1165 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1166
1167 --
1168 -- Determine the validation level
1169 --
1170 IF p_validation_level >= FND_API.G_VALID_LEVEL_FULL THEN
1171
1172 -- No validation logic required for this API.
1173 --
1174 NULL ;
1175 END IF ;
1176
1177 l_gms_enabled := gms_install.enabled ;
1178
1179 IF not l_gms_enabled then
1180 return ;
1181 END IF ;
1182
1183 IF p_award_id is null THEN
1184 return ;
1185 END IF ;
1186
1187 l_adl_rec.expenditure_item_id := NULL ;
1188 l_adl_rec.project_id := P_project_id;
1189 l_adl_rec.task_id := p_task_id;
1190 l_adl_rec.cost_distributed_flag := 'N';
1191 l_adl_rec.cdl_line_num := NULL;
1192 l_adl_rec.adl_line_num := 1;
1193 l_adl_rec.distribution_value := 100 ;
1194 l_adl_rec.line_type := 'R';
1195 l_adl_rec.adl_status := 'A';
1196 l_adl_rec.document_type := 'PO';
1197 l_adl_rec.billed_flag := 'N';
1198 l_adl_rec.bill_hold_flag := NULL ;
1199 l_adl_rec.award_set_id := p_award_set_id ;
1200 l_adl_rec.award_id := p_award_id;
1201 l_adl_rec.raw_cost := 0;
1202 l_adl_rec.last_update_date := SYSDATE;
1203 l_adl_rec.creation_date := SYSDATE;
1204 l_adl_rec.last_updated_by := fnd_global.user_id;
1205 l_adl_rec.created_by := fnd_global.user_id;
1206 l_adl_rec.last_update_login := 0;
1207 l_adl_rec.po_distribution_id := p_distribution_id ;
1208 l_adl_rec.request_id := p_distribution_num ;
1209
1210 gms_awards_dist_pkg.create_adls(l_adl_rec);
1211
1212 x_msg_count := l_msg_count ;
1213 x_msg_data := l_msg_data ;
1214 x_return_status := l_return_status ;
1215 EXCEPTION
1216 WHEN FND_API.G_EXC_ERROR then
1217 x_return_status := FND_API.G_RET_STS_ERROR ;
1218 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
1219 p_count => x_msg_count ,
1220 p_data => x_msg_data ) ;
1221
1222 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1224 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
1225 p_count => x_msg_count ,
1226 p_data => x_msg_data ) ;
1227 WHEN OTHERS THEN
1228
1229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1230 IF FND_MSG_PUB.check_msg_level( fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1231 fnd_msg_pub.add_exc_msg( G_pkg_name, l_api_name ) ;
1232 END IF ;
1233 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
1234 p_count => x_msg_count ,
1235 p_data => x_msg_data ) ;
1236 END create_pdoi_adls ;
1237 --
1238 -- GET_AWARD_NUMBER : the function overloading was created to get the award number in a
1239 -- SQL.
1240 --
1241 FUNCTION GET_AWARD_NUMBER( p_award_set_id IN NUMBER)
1242 return varchar2 is
1243 l_award_number gms_awards_all.award_number%TYPE ;
1244 BEGIN
1245 IF p_award_set_id is NULL THEN
1246 return NULL ;
1247 END IF ;
1248
1249 select awd.award_number
1250 into l_award_number
1251 from gms_awards_all awd,
1252 gms_award_distributions adl
1253 where adl.award_set_id = p_award_set_id
1254 and adl.award_id = awd.award_id
1255 and adl.adl_line_num = 1 ;
1256
1257 return l_award_number ;
1258
1259 END get_award_number ;
1260
1261 -- Start of comments
1262 -- -----------------
1263 -- API Name : CREATE_PO_ADL
1264 -- Function : create award distribution line for a award related PO distributions.
1265 -- Parameters :
1266 -- : Standard parameters
1267 -- p_api_version, p_commit, p_init_msg_list,
1268 -- p_validation_level, x_msg_count, x_msg_data,
1269 -- x_return_status
1270 -- IN
1271 -- : p_project_id NUMBER, Project Identifier.
1272 -- : p_task_id NUMBER, Task Identifier.
1273 -- : p_award_number varchar2, Award Number
1274 -- : p_po_distribution_id NUMBER, PO distribution Identifier
1275 -- OUT
1276 -- : x_award_set_id_out NUMBER
1277 -- ADL record identifier for the award information.
1278 -- End of comments
1279 -- ----------------
1280 PROCEDURE CREATE_PO_ADL
1281 ( p_api_version in number,
1282 p_commit in varchar2,
1283 p_init_msg_list in varchar2,
1284 p_validation_level in number,
1285 x_msg_count out nocopy number,
1286 x_msg_data out nocopy varchar2,
1287 x_return_status out nocopy varchar2,
1288 p_project_id in number,
1289 p_task_id in number,
1290 p_award_number in varchar2,
1291 p_po_distribution_id in number,
1292 x_award_set_id_out out nocopy number ) is
1293
1294 l_adl_rec gms_award_distributions%ROWTYPE ;
1295 l_award_id gms_awards_all.award_id%TYPE ;
1296 l_msg_count number ;
1297 l_msg_data varchar2(2000) ;
1298 l_return_status varchar2(1) ;
1299 l_api_name varchar2(50) := 'CREATE_PO_ADL' ;
1300 l_gms_enabled boolean ;
1301
1302 BEGIN
1303 -- Standrad call to check API compatibility.
1304 IF NOT FND_API.Compatible_API_Call( G_api_version,
1305 p_api_version,
1306 l_api_name,
1307 G_pkg_name ) THEN
1308 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1309 END IF ;
1310 -- Initialize message list if p_init_msg_list is set to TRUE
1311 --
1312 IF FND_API.to_boolean( p_init_msg_list) THEN
1313 FND_MSG_PUB.initialize ;
1314 END IF ;
1315 -- Initialize API return status to success.
1316 --
1317 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1318 --
1319 -- Determine the validation level
1320 --
1321 IF p_validation_level >= FND_API.G_VALID_LEVEL_FULL THEN
1322 -- No validation logic required for this API.
1323 --
1324 NULL ;
1325 END IF ;
1326
1327 l_gms_enabled := gms_install.enabled ;
1328
1329 IF not l_gms_enabled OR p_award_number is NULL then
1330 return ;
1331 END IF ;
1332
1333 select award_id
1334 into l_award_id
1335 from gms_awards_all
1336 where award_number = p_award_number ;
1337
1338 l_adl_rec.last_update_date := sysdate ;
1339 l_adl_rec.last_updated_by := nvl(fnd_global.user_id,0) ;
1340 l_adl_rec.created_by := nvl(fnd_global.user_id,0) ;
1341 l_adl_rec.creation_date := SYSDATE;
1342 l_adl_rec.last_update_login := 0;
1343 l_adl_rec.award_set_id := get_new_award_set_id;
1344 x_award_set_id_out := l_adl_rec.award_set_id;
1345 l_adl_rec.adl_line_num := 1 ;
1346 l_adl_rec.distribution_value:= 100 ;
1347 l_adl_rec.document_type := 'PO' ;
1348 l_adl_rec.project_id := p_project_id ;
1349 l_adl_rec.task_id := p_task_id ;
1350 l_adl_rec.award_id := l_award_id ;
1351 l_adl_rec.adl_status := 'A' ;
1352 l_adl_rec.fc_status := 'N' ;
1353 l_adl_rec.line_type := 'R' ;
1354 l_adl_rec.capitalized_flag := 'N' ;
1355 l_adl_rec.capitalizable_flag:= NULL ;
1356 l_adl_rec.po_distribution_id:= p_po_distribution_id ;
1357 gms_awards_dist_pkg.create_adls(l_adl_rec) ;
1358
1359 EXCEPTION
1360 WHEN FND_API.G_EXC_ERROR then
1361 x_return_status := FND_API.G_RET_STS_ERROR ;
1362 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
1363 p_count => x_msg_count ,
1364 p_data => x_msg_data ) ;
1365
1366 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1367 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1368 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
1369 p_count => x_msg_count ,
1370 p_data => x_msg_data ) ;
1371 WHEN OTHERS THEN
1372
1373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1374 IF FND_MSG_PUB.check_msg_level( fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1375 fnd_msg_pub.add_exc_msg( G_pkg_name, l_api_name ) ;
1376 END IF ;
1377 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
1378 p_count => x_msg_count ,
1379 p_data => x_msg_data ) ;
1380 END CREATE_PO_ADL ;
1381
1382 -- Start of comments
1383 -- -----------------
1384 -- API Name : MAINTAIN_PO_ADL
1385 -- Function : Update award details on ADL associated with the PO distributions.
1386 -- Parameters :
1387 -- : Standard parameters
1388 -- p_api_version, p_commit, p_init_msg_list,
1389 -- p_validation_level, x_msg_count, x_msg_data,
1390 -- x_return_status
1391 -- IN
1392 -- : p_project_id NUMBER, Project Identifier.
1393 -- : p_task_id NUMBER, Task Identifier.
1394 -- : p_award_number varchar2, Award Number
1395 -- : p_po_distribution_id NUMBER, PO distribution Identifier
1396 -- : p_award_set_id_in NUMBER, ADL record identifier.
1397 -- OUT
1398 -- : x_award_set_id_out NUMBER
1399 -- ADL record identifier for the award information.
1400 -- x_award_set_id_out will be same as p_award_set_id_in when no new adl is created.
1401 -- x_award_set_id_out will be new value when new adl is created when PO distribution
1402 -- mismatch or when null award to new award is entered.
1403 -- End of comments
1404 -- ----------------
1405 PROCEDURE MAINTAIN_PO_ADL
1406 ( p_api_version in number,
1407 p_commit in varchar2,
1408 p_init_msg_list in varchar2,
1409 p_validation_level in number,
1410 x_msg_count out nocopy number,
1411 x_msg_data out nocopy varchar2,
1412 x_return_status out nocopy varchar2,
1413 p_award_set_id_in in number,
1414 p_project_id in number,
1415 p_task_id in number,
1416 p_award_number in varchar2,
1417 p_po_distribution_id in number,
1418 x_award_set_id_out out nocopy number ) is
1419
1420 l_adl_rec gms_award_distributions%ROWTYPE ;
1421 l_create_flag varchar2(1) ;
1422 l_update_flag varchar2(1) ;
1423 l_award_set_id_out number ;
1424 l_award_id gms_awards_all.award_id%TYPE ;
1425
1426 l_msg_count number ;
1427 l_msg_data varchar2(2000) ;
1428 l_return_status varchar2(1) ;
1429 l_api_name varchar2(50) := 'UPDATE_PO_ADL' ;
1430 l_gms_enabled boolean ;
1431
1432 cursor C_ADL is
1433 select award_set_id,
1434 adl_line_num,
1435 funding_pattern_id,
1436 distribution_value,
1437 document_type,
1438 project_id,
1439 task_id,
1440 award_id,
1441 ind_compiled_set_id,
1442 gl_date,
1443 request_id,
1444 line_num_reversed,
1445 resource_list_member_id,
1446 output_tax_classification_code,
1447 output_tax_exempt_flag,
1448 output_tax_exempt_reason_code,
1449 output_tax_exempt_number,
1450 adl_status,
1451 fc_status,
1452 line_type,
1453 capitalized_flag,
1454 capitalizable_flag,
1455 reversed_flag,
1456 revenue_distributed_flag,
1457 billed_flag,
1458 bill_hold_flag,
1459 distribution_id,
1460 po_distribution_id,
1461 invoice_distribution_id,
1462 invoice_id,
1463 distribution_line_number,
1464 burdenable_raw_cost,
1465 cost_distributed_flag,
1466 last_update_date,
1467 last_updated_by,
1468 created_by,
1469 creation_date,
1470 last_update_login,
1471 billable_flag
1472 from gms_award_distributions
1473 where award_set_id = p_award_set_id_in
1474 and document_type = 'PO'
1475 and po_distribution_id = p_po_distribution_id
1476 and adl_line_num = 1 ;
1477
1478 BEGIN
1479 -- Standrad call to check API compatibility.
1480 IF NOT FND_API.Compatible_API_Call( G_api_version,
1481 p_api_version,
1482 l_api_name,
1483 G_pkg_name ) THEN
1484 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1485 END IF ;
1486 -- Initialize message list if p_init_msg_list is set to TRUE
1487 --
1488 IF FND_API.to_boolean( p_init_msg_list) THEN
1489 FND_MSG_PUB.initialize ;
1490 END IF ;
1491 -- Initialize API return status to success.
1492 --
1493 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1494 --
1495 -- Determine the validation level
1496 --
1497 IF p_validation_level >= FND_API.G_VALID_LEVEL_FULL THEN
1498 -- No validation logic required for this API.
1499 --
1500 NULL ;
1501 END IF ;
1502
1503 l_gms_enabled := gms_install.enabled ;
1504
1505 IF not l_gms_enabled then
1506 return ;
1507 END IF ;
1508
1509 l_create_flag := 'N' ;
1510 l_update_flag := 'Y' ;
1511
1512 IF p_award_set_id_in is not NULL THEN
1513 open c_adl ;
1514 fetch c_adl into
1515 l_adl_rec.award_set_id,
1516 l_adl_rec.adl_line_num,
1517 l_adl_rec.funding_pattern_id,
1518 l_adl_rec.distribution_value,
1519 l_adl_rec.document_type,
1520 l_adl_rec.project_id,
1521 l_adl_rec.task_id,
1522 l_adl_rec.award_id,
1523 l_adl_rec.ind_compiled_set_id,
1524 l_adl_rec.gl_date,
1525 l_adl_rec.request_id,
1526 l_adl_rec.line_num_reversed,
1527 l_adl_rec.resource_list_member_id,
1528 l_adl_rec.output_tax_classification_code,
1529 l_adl_rec.output_tax_exempt_flag,
1530 l_adl_rec.output_tax_exempt_reason_code,
1531 l_adl_rec.output_tax_exempt_number,
1532 l_adl_rec.adl_status,
1533 l_adl_rec.fc_status,
1534 l_adl_rec.line_type,
1535 l_adl_rec.capitalized_flag,
1536 l_adl_rec.capitalizable_flag,
1537 l_adl_rec.reversed_flag,
1538 l_adl_rec.revenue_distributed_flag,
1539 l_adl_rec.billed_flag,
1540 l_adl_rec.bill_hold_flag,
1541 l_adl_rec.distribution_id,
1542 l_adl_rec.po_distribution_id,
1543 l_adl_rec.invoice_distribution_id,
1544 l_adl_rec.invoice_id,
1545 l_adl_rec.distribution_line_number,
1546 l_adl_rec.burdenable_raw_cost,
1547 l_adl_rec.cost_distributed_flag,
1548 l_adl_rec.last_update_date,
1549 l_adl_rec.last_updated_by,
1550 l_adl_rec.created_by,
1551 l_adl_rec.creation_date,
1552 l_adl_rec.last_update_login,
1553 l_adl_rec.billable_flag ;
1554 close c_adl ;
1555 END IF ;
1556
1557 IF NVL(l_adl_rec.po_distribution_id,0) <> p_po_distribution_id THEN
1558 l_create_flag := 'Y' ;
1559 l_update_flag := 'N' ;
1560 END IF ;
1561
1562 If p_award_number is NULL and l_update_flag = 'Y' THEN
1563 DELETE_PO_ADL
1564 ( p_api_version => p_api_version,
1565 p_commit => p_commit,
1566 p_init_msg_list => p_init_msg_list,
1567 p_validation_level=> p_validation_level,
1568 x_msg_count => x_msg_count,
1569 x_msg_data => x_msg_data,
1570 x_return_status => x_return_status,
1571 p_award_set_id_in => p_award_set_id_in,
1572 p_po_distribution_id => p_po_distribution_id ) ;
1573
1574 l_award_set_id_out := NULL ;
1575
1576 ELSIF p_award_number is not NULL and l_create_flag = 'Y' THEN
1577 CREATE_PO_ADL
1578 ( p_api_version => p_api_version,
1579 p_commit => p_commit,
1580 p_init_msg_list => p_init_msg_list,
1581 p_validation_level=> p_validation_level,
1582 x_msg_count => x_msg_count,
1583 x_msg_data => x_msg_data,
1584 x_return_status => x_return_status,
1585 p_project_id => p_project_id,
1586 p_task_id => p_task_id,
1587 p_award_number => p_award_number,
1588 p_po_distribution_id => p_po_distribution_id,
1589 x_award_set_id_out => l_award_set_id_out ) ;
1590 ELSIF p_award_number is not NULL and l_update_flag = 'Y' THEN
1591
1592 l_adl_rec.project_id := p_project_id ;
1593 l_adl_rec.task_id := p_task_id;
1594 l_award_set_id_out := p_award_set_id_in ;
1595
1596 select award_id
1597 into l_award_id
1598 from gms_awards_all
1599 where award_number = p_award_number ;
1600
1601 l_adl_rec.award_id := l_award_id ;
1602 l_adl_rec.last_update_date := sysdate ;
1603 l_adl_rec.last_updated_by := nvl(fnd_global.user_id,0) ;
1604
1605 gms_awards_dist_pkg.update_adls(l_adl_rec) ;
1606
1607 END IF ;
1608 x_award_set_id_out := l_award_set_id_out ;
1609
1610 EXCEPTION
1611 WHEN FND_API.G_EXC_ERROR then
1612 x_return_status := FND_API.G_RET_STS_ERROR ;
1613 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
1614 p_count => x_msg_count ,
1615 p_data => x_msg_data ) ;
1616
1617 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1618 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1619 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
1620 p_count => x_msg_count ,
1621 p_data => x_msg_data ) ;
1622 WHEN OTHERS THEN
1623
1624 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1625 IF FND_MSG_PUB.check_msg_level( fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1626 fnd_msg_pub.add_exc_msg( G_pkg_name, l_api_name ) ;
1627 END IF ;
1628 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
1629 p_count => x_msg_count ,
1630 p_data => x_msg_data ) ;
1631 END MAINTAIN_PO_ADL ;
1632
1633 -- Start of comments
1634 -- -----------------
1635 -- API Name : DELETE_PO_ADL
1636 -- Function : delete the award distribution line for a given po distribution line.
1637 -- ADL is deleted when PO distribution is deleted.
1638 -- Parameters :
1639 -- : Standard parameters
1640 -- p_api_version, p_commit, p_init_msg_list,
1641 -- p_validation_level, x_msg_count, x_msg_data,
1642 -- x_return_status
1643 -- IN
1644 -- : p_award_set_id_in NUMBER
1645 -- Award Set Identifier in ADL record.
1646 -- : p_po_distribution_id NUMBER
1647 -- PO distribution identifier.
1648 -- End of comments
1649 -- ----------------
1650 PROCEDURE DELETE_PO_ADL
1651 ( p_api_version in number,
1652 p_commit in varchar2,
1653 p_init_msg_list in varchar2,
1654 p_validation_level in number,
1655 x_msg_count out nocopy number,
1656 x_msg_data out nocopy varchar2,
1657 x_return_status out nocopy varchar2,
1658 p_award_set_id_in in number,
1659 p_po_distribution_id in number ) IS
1660
1661 l_msg_count number ;
1662 l_msg_data varchar2(2000) ;
1663 l_return_status varchar2(1) ;
1664 l_api_name varchar2(50) := 'DELETE_PO_ADL' ;
1665 l_gms_enabled boolean ;
1666 BEGIN
1667 -- Standrad call to check API compatibility.
1668 IF NOT FND_API.Compatible_API_Call( G_api_version,
1669 p_api_version,
1670 l_api_name,
1671 G_pkg_name ) THEN
1672 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1673 END IF ;
1674 -- Initialize message list if p_init_msg_list is set to TRUE
1675 --
1676 IF FND_API.to_boolean( p_init_msg_list) THEN
1677 FND_MSG_PUB.initialize ;
1678 END IF ;
1679 -- Initialize API return status to success.
1680 --
1681 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1682 --
1683 -- Determine the validation level
1684 --
1685 IF p_validation_level >= FND_API.G_VALID_LEVEL_FULL THEN
1686 -- No validation logic required for this API.
1687 --
1688 NULL ;
1689 END IF ;
1690
1691 l_gms_enabled := gms_install.enabled ;
1692
1693 IF not l_gms_enabled or p_award_set_id_in is NULL then
1694 return ;
1695 END IF ;
1696
1697 IF p_po_distribution_id is NULL THEN
1698
1699 delete from gms_award_distributions
1700 where award_set_id = p_award_set_id_in
1701 and document_type = 'PO'
1702 and adl_line_num = 1
1703 and po_distribution_id is NULL ;
1704 ELSE
1705 delete from gms_award_distributions
1706 where award_set_id = p_award_set_id_in
1707 and document_type = 'PO'
1708 and adl_line_num = 1
1709 and po_distribution_id = p_po_distribution_id ;
1710 END IF ;
1711
1712 EXCEPTION
1713 WHEN FND_API.G_EXC_ERROR then
1714 x_return_status := FND_API.G_RET_STS_ERROR ;
1715 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
1716 p_count => x_msg_count ,
1717 p_data => x_msg_data ) ;
1718
1719 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1720 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1721 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
1722 p_count => x_msg_count ,
1723 p_data => x_msg_data ) ;
1724 WHEN OTHERS THEN
1725
1726 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1727 IF FND_MSG_PUB.check_msg_level( fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1728 fnd_msg_pub.add_exc_msg( G_pkg_name, l_api_name ) ;
1729 END IF ;
1730 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
1731 p_count => x_msg_count ,
1732 p_data => x_msg_data ) ;
1733 END DELETE_PO_ADL ;
1734
1735 FUNCTION IS_SPONSORED_PROJECT( p_project_id in NUMBER ) return varchar2
1736 is
1737 cursor C_spon_project is
1738 select pt.sponsored_flag
1739 from pa_projects_all b,
1740 gms_project_types pt
1741 where b.project_id = p_project_id
1742 and b.project_type = pt.project_type
1743 and pt.sponsored_flag = 'Y' ;
1744
1745 x_return varchar2(1) ;
1746 x_flag varchar2(1) ;
1747 BEGIN
1748
1749 x_return := 'N' ;
1750
1751 open C_spon_project ;
1752 fetch C_spon_project into x_flag ;
1753 close C_spon_project ;
1754
1755 IF nvl(x_flag, 'N') = 'Y' THEN
1756 x_return := 'Y' ;
1757 END IF ;
1758
1759 return x_return ;
1760
1761 END IS_SPONSORED_PROJECT ;
1762
1763 END GMS_PO_API_GRP ;
1764
[Home] [Help]