1 package BODY GMS_PO_API2_GRP as
2 --$Header: gmsgpo2b.pls 120.0.12020000.2 2012/07/18 09:31:29 admarath ship $
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_API2_GRP' ;
11 G_file_name CONSTANT varchar2(45) := 'gmsgpo2b.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 -- Start of comments
50 -- -----------------
51 -- API Name : common_code
52 -- Type : This is a private package program unit.
53 -- Pre Reqs : None
54 -- Function : This API is for validating standard API compatibility
55 -- checks.
56 -- Calling API : Local program units.
57 -- Parameters :
58 -- : Standard parameters
59 -- p_api_version, p_commit, p_init_msg_list,
60 -- p_validation_level, x_msg_count, x_msg_data,
61 -- x_return_status
62 -- IN
63 -- : p_api_name
64 -- The name of the API calling it.
65 -- End of comments
66 -- ----------------
67 PROCEDURE common_code ( p_api_version in NUMBER,
68 p_init_msg_list in varchar2,
69 p_commit in varchar2,
70 p_validation_level in NUMBER,
71 x_msg_count out nocopy number,
72 x_msg_data out nocopy varchar2,
73 x_return_status out nocopy varchar2,
74 p_api_name IN varchar2 ) is
75
76 l_msg_count NUMBER ;
77 l_msg_data varchar2(2000) ;
78 l_return_status varchar2(1) := fnd_api.G_RET_STS_SUCCESS;
79 BEGIN
80
81 -- Standrad call to check API compatibility.
82 IF NOT FND_API.Compatible_API_Call( G_api_version,
83 p_api_version,
84 p_api_name,
85 G_pkg_name) THEN
86
87 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
88 END IF ;
89
90 -- Initialize message list if p_init_msg_list is set to TRUE
91 --
92 IF FND_API.to_boolean( p_init_msg_list) THEN
93
94 FND_MSG_PUB.initialize ;
95
96 END IF ;
97
98 -- Initialize API return status to success.
99 --
100 l_return_status := FND_API.G_RET_STS_SUCCESS ;
101
102 --
103 -- Determine the validation level
104 --
105 IF p_validation_level >= FND_API.G_VALID_LEVEL_FULL THEN
106
107 -- No validation logic required for this API.
108 --
109 NULL ;
110 END IF ;
111
112 -- Program logic begins here
113 -- Program Logic ends here
114
115 x_msg_count := l_msg_count ;
116 x_msg_data := l_msg_data ;
117 x_return_status := l_return_status ;
118
119 EXCEPTION
120 WHEN FND_API.G_EXC_ERROR then
121 x_return_status := FND_API.G_RET_STS_ERROR ;
122 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
123 p_count => x_msg_count ,
124 p_data => x_msg_data ) ;
125
126 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
128 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
129 p_count => x_msg_count ,
130 p_data => x_msg_data ) ;
131 WHEN OTHERS THEN
132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
133
134 IF FND_MSG_PUB.check_msg_level( fnd_msg_pub.g_msg_lvl_unexp_error) THEN
135 fnd_msg_pub.add_exc_msg( G_pkg_name, p_api_name ) ;
136 END IF ;
137
138 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
139 p_count => x_msg_count ,
140 p_data => x_msg_data ) ;
141 END common_code ;
142
143
144 -- Start of comments
145 -- -----------------
146 -- API Name : create_bulk_adl
147 -- Type : This is a private package program unit.
148 -- Pre Reqs : None
149 -- Function : This is used to create award distribution lines
150 -- using the bulk processing.
151 -- Logic : Loop thru all the elements of the object record
152 -- and create award distribution lines.
153 -- Parameters :
154 -- OUT : x_msg_count OUT NUMBER
155 -- Holds no. of messages in the API
156 -- message lists.
157 -- : x_msg_data OUT Varchar2
158 -- Holds the message in an encoded format.
159 -- : x_return_status OUT varchar2
160 -- Holds the result of all the operations
161 -- performed by the API.
162 -- values - G_RET_STS_SUCESS = 'S'
163 -- G_RET_STS_ERROR = 'E'
164 -- G_RET_STS_UNEXP_ERROR = 'U'
165 -- : p_interface_obj IN OUT gms_po_interface_type
166 -- SQL object that holds the value of distribution_ID,
167 -- distribution number, project, task, award set id
168 -- for bulk processing.
169 -- End of comments
170 -- ----------------
171 PROCEDURE create_bulk_adl
172 (
173 x_msg_count out nocopy number,
174 x_msg_data out nocopy varchar2,
175 x_return_status out nocopy varchar2,
176 p_interface_obj in out nocopy gms_po_interface_type) is
177
178 l_dummy NUMBER ;
179 BEGIN
180
181 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
182
183 IF NVL(p_interface_obj.distribution_id.COUNT,0) <= 0 then
184 return ;
185 END IF ;
186
187 FORALL i in p_interface_obj.distribution_id.FIRST..p_interface_obj.distribution_id.LAST
188 INSERT into gms_award_distributions
189 ( award_set_id ,
190 adl_line_num,
191 funding_pattern_id,
192 distribution_value ,
193 raw_cost,
194 document_type,
195 project_id ,
196 task_id ,
197 award_id ,
198 expenditure_item_id ,
199 cdl_line_num ,
200 ind_compiled_set_id ,
201 gl_date ,
202 request_id ,
203 line_num_reversed ,
204 resource_list_member_id ,
205 output_vat_tax_id ,
206 output_tax_exempt_flag ,
207 output_tax_exempt_reason_code ,
208 output_tax_exempt_number ,
209 adl_status ,
210 fc_status ,
211 line_type ,
212 capitalized_flag ,
213 capitalizable_flag ,
214 reversed_flag ,
215 revenue_distributed_flag ,
216 billed_flag ,
217 bill_hold_flag ,
218 distribution_id ,
219 po_distribution_id ,
220 invoice_distribution_id ,
221 parent_award_set_id ,
222 invoice_id ,
223 parent_adl_line_num ,
224 distribution_line_number ,
225 burdenable_raw_cost ,
226 cost_distributed_flag ,
227 last_update_date ,
228 last_updated_by ,
229 created_by ,
230 creation_date ,
231 last_update_login ,
232 billable_flag
233 )
234 SELECT gms_awards_dist_pkg.get_award_set_id ,
235 1, --adl_line_num,
236 funding_pattern_id,
237 distribution_value ,
238 raw_cost,
239 'PO' , --document_type,
240 project_id ,
241 task_id ,
242 award_id ,
243 NULL, --expenditure_item_id ,
244 cdl_line_num ,
245 NULL, --ind_compiled_set_id ,
246 gl_date ,
247 p_interface_obj.distribution_num(i), --request_id ,
248 line_num_reversed ,
249 NULL, --resource_list_member_id ,
250 output_vat_tax_id ,
251 output_tax_exempt_flag ,
252 output_tax_exempt_reason_code ,
253 output_tax_exempt_number ,
254 'A', --adl_status ,
255 'N', --fc_status ,
256 line_type ,
257 capitalized_flag ,
258 capitalizable_flag ,
259 reversed_flag ,
260 revenue_distributed_flag ,
261 billed_flag ,
262 bill_hold_flag ,
263 NULL, --distribution_id ,
264 p_interface_obj.distribution_id(i), --po_distribution_id ,
265 NULL, --invoice_distribution_id ,
266 parent_award_set_id ,
267 NULL, --invoice_id ,
268 parent_adl_line_num ,
269 NULL, --distribution_line_number ,
270 NULL, --burdenable_raw_cost ,
271 cost_distributed_flag ,
272 SYSDATE, --last_update_date ,
273 fnd_global.user_id , --last_updated_by ,
274 fnd_global.user_id , --created_by ,
275 SYSDATE, --creation_date ,
276 last_update_login ,
277 billable_flag
278 from gms_award_distributions
279 where award_set_id = p_interface_obj.award_set_id_in(i)
280 and adl_line_num = 1 ;
281
282 FOR i in 1..p_interface_obj.distribution_id.count LOOP
283
284 select award_set_id
285 into l_dummy
286 from gms_award_distributions
287 where po_distribution_id = p_interface_obj.distribution_id(i)
288 and document_type = 'PO'
289 and adl_status = 'A'
290 and fc_status = 'N' ;
291
292 p_interface_obj.award_set_id_out(i) := l_dummy ;
293
294 END LOOP;
295
296 END create_bulk_adl;
297
298
299
300 -- Start of comments
301 -- -----------------
302 -- API Name : CREATE_ADLS
303 -- Type : This is a Public package program unit.
304 -- Pre Reqs : None
305 -- Function : This API is for creating Award Distribution Lines for new Purchase
306 -- Order Distributions created through Copy Document feature in PO.,
307 -- 2 : This API is for creating Award Distribution Lines for new Purchase
308 -- Order Distributions created through AUTOCREATE function
309 -- 3 : This API is for creating Award Distribution Lines for new Purchase
310 -- Order Distributions created through Create release concurrent process
311 -- function
312 --
313 -- Logic : Copy award distribution line from the award set id passed.
314 -- Calling API : po_interface_s.create_distributions
315 -- Calling API : PO_RELGEN_PKG.create_release_distribution
316 -- Calling API : PO_COPYDOC_S1.insert_distribution
317 -- Parameters :
318 -- : Standard parameters
319 -- p_api_version, p_commit, p_init_msg_list,
320 -- p_validation_level, x_msg_count, x_msg_data,
321 -- x_return_status
322 -- IN OUT
323 -- : p_interface_obj gms_po_interface_type
324 -- This is a SQL object having a following table
325 -- elements.
326 -- distribution_id - Holds distribution ID
327 -- distribution_num Holds distribution number
328 -- project_id Holds Project ID
329 -- task_id Holds Task ID
330 -- award_set_id_in Holds Award Set Id Reference
331 -- award_set_id_out Holds return value of new
332 -- award distribution line
333 -- reference.
334 -- IN p_calling_module -
335 -- This tells calling API for create_adls.
336 -- COPYDOC, AUTOCREATE, CREATE_RELEASE, CHANGE_PO
337 -- End of comments
338 -- ----------------
339
340 PROCEDURE CREATE_ADLS
341 ( p_api_version in number,
342 p_init_msg_list in varchar2,
343 p_commit in varchar2,
344 p_validation_level in number,
345 x_msg_count out nocopy number,
346 x_msg_data out nocopy varchar2,
347 x_return_status out nocopy varchar2,
348 p_calling_module in varchar2,
349 p_interface_obj in out nocopy gms_po_interface_type) is
350
351 l_msg_count NUMBER ;
352 l_msg_data varchar2(4000) ;
353 l_return_status varchar2(1) := fnd_api.G_RET_STS_SUCCESS;
354 l_api_name varchar2(45) := 'create_adls' ;
355 begin
356
357 -- API standards requires a standard code to validate
358 -- api versions and init message list etc.
359 -- Common code must be added to all the program unit.
360 --
361 common_code ( p_api_version ,
362 p_init_msg_list,
363 p_commit ,
364 p_validation_level ,
365 l_msg_count ,
366 l_msg_data ,
367 l_return_status ,
368 l_api_name ) ;
369
370 -- Program logic begins here
371 -- Program Logic ends here
372
373 IF not gms_install.enabled then
374 return ;
375 END IF ;
376
377 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
378 -- =================================================
379 -- Bulk processing to create award distribution
380 -- lines.
381 -- =================================================
382 create_bulk_adl
383 ( l_msg_count ,
384 l_msg_data ,
385 l_return_status ,
386 p_interface_obj ) ;
387
388 END IF ;
389
390 x_msg_count := l_msg_count ;
391 x_msg_data := l_msg_data ;
392 x_return_status := l_return_status ;
393
394 end CREATE_ADLS ;
395 -- =====================
396
397 -- Start of comments
398 -- -----------------
399 -- API Name : validate_po_purge
400 -- Type : This is a Public package program unit.
401 -- Pre Reqs : None
402 -- Function : This api will determine if document can be purged
403 -- or not. The structure will hold the value 'N' to disallow
404 -- the purge.
405 -- Logic : A structure indicating whether PO documents can be purged
406 -- or corresponding entry in x_out_rec.purge_allowed will indicate
407 -- whether the document is purgable or not. e.g., If
408 -- x_out_rec.purge_allowedNi) is 'Y', it means that
409 -- the document specified in ip_in_rec.entity_ids(i) will not be purged.
410 -- The number of records in x_out_rec.purge_allowed should always
411 -- one that grants do not want to purge.
412
413 -- Calling API : ???????????
414 -- Parameters :
415 -- : Standard parameters
416 -- p_api_version, p_commit, p_init_msg_list,
417 -- p_validation_level, x_msg_count, x_msg_data,
418 -- x_return_status
419 --IN OUT:
420 --p_in_rec
421 -- A structure that holds PO information
422 -- p_in_rec.entity_name will expect 'PO_HEADERS',
423 -- while p_in_rec.entity_ids will be a table of all document
424 -- header ids that PO are about to be purged
425 --OUT:
426 --x_out_rec
427 -- A structure indicating whether PO documents can be purged
428 -- or corresponding entry in x_out_rec.purge_allowed will indicate
429 -- whether the document is purgable or not. e.g., If
430 -- x_out_rec.purge_allowedNi) is 'Y', it means that
431 -- the document specified in ip_in_rec.entity_ids(i) will not be purged.
432 -- The number of records in x_out_rec.purge_allowed should always
433 -- one that grants do not want to purge.
434 -- End of comments
435 -- ----------------
436 PROCEDURE validate_po_purge ( p_api_version IN NUMBER,
437 p_init_msg_list IN VARCHAR2,
438 p_commit IN VARCHAR2,
439 p_validation_level in NUMBER,
440 x_return_status OUT NOCOPY VARCHAR2,
441 x_msg_count OUT NOCOPY NUMBER,
442 x_msg_data OUT NOCOPY VARCHAR2,
443 p_in_rec IN PURGE_IN_RECTYPE,
444 x_out_rec OUT NOCOPY PURGE_OUT_RECTYPE
445 ) is
446
447 l_msg_count NUMBER ;
448 l_msg_data varchar2(4000) ;
449 l_return_status varchar2(1) := fnd_api.G_RET_STS_SUCCESS;
450 l_api_name varchar2(45) := 'validate_po_purge' ;
451 begin
452
453 -- Standrad call to check API compatibility.
454 -- API standards requires a standard code to validate
455 -- api versions and init message list etc.
456 -- Common code must be added to all the program unit.
457 --
458 common_code ( p_api_version ,
459 p_init_msg_list,
460 p_commit ,
461 p_validation_level ,
462 l_msg_count ,
463 l_msg_data ,
464 l_return_status ,
465 l_api_name ) ;
466
467 -- Program logic begins here
468 -- Program Logic ends here
469
470 -- x_out_rec
471 -- Notice that purge_out_rectype will have entity_ids table as well, specifying the documents
472 -- that the caller needs to take "action" for, while action specifies the type of action. The stubbed
473 -- procedure will not need to set anything to this OUT parameter at all (nor will the PO
474 -- Purge program before calling validate_purge); but when the actual code is being implemented
475 -- in this procedure, for now the requirement is to populate entity_ids with documents GMS does
476 -- not allow to purge, with value 'N' in the corresponding entry in action. PO will not purge documents
477 -- coming out form x_out_rec.entity_ids, with the corresponding action = 'N'.
478
479
480 -- NOTHING IS POPULATED IN OUT PARAM THIS MEANS GRANTS OKAY WITH EVERYTHING IS
481 -- PURGEABLE.
482
483 x_msg_count := l_msg_count ;
484 x_msg_data := l_msg_data ;
485 x_return_status := l_return_status ;
486
487 END validate_po_purge ;
488
489
490 -- Start of comments
491 -- -----------------
492 -- API Name : po_purge
493 -- Type : This is a Public package program unit.
494 -- Pre Reqs : None
495 -- Function : This will delette award distribution lines.
496 -- Logic : A structure indicating whether PO documents can be purged
497 -- or not For each entry in p_in_rec.entity_ids, the
498 -- corresponding entry in x_out_rec.purge_allowed will indicate
499 -- whether the document is purgable or not. e.g., If
500 -- x_out_rec.purge_allowed(i) is 'Y', it means that
501 -- p_in_rec.entity_ids(i) can be purged.
502 -- If x_out_rec.purge_allowed(i) is 'N', the document specified in
503 -- p_in_rec.entity_ids(i) will not be purged.
504 -- The number of records in x_out_rec.purge_allowed should always
505 -- be the same as that for p_in_rec.entity_ids
506
507 -- Calling API : ???????????
508 -- Parameters:
509 -- IN:
510 -- p_in_rec
511 -- A structure that holds PO information
512 -- p_in_rec.entity_name will expect 'PO_HEADERS', while
513 -- p_in_rec.entity_ids
514 -- will be a table of all document header ids that PO are
515 -- about to be purged
516 -- End of comments
517 -- -----------------
518
519 PROCEDURE po_purge ( p_api_version IN NUMBER,
520 p_init_msg_list IN VARCHAR2,
521 p_commit IN VARCHAR2,
522 p_validation_level IN NUMBER,
523 x_return_status OUT NOCOPY VARCHAR2,
524 x_msg_count OUT NOCOPY NUMBER,
525 x_msg_data OUT NOCOPY VARCHAR2,
526 p_in_rec IN PURGE_IN_RECTYPE
527 ) is
528 l_msg_count NUMBER ;
529 l_msg_data varchar2(4000) ;
530 l_return_status varchar2(1) := fnd_api.G_RET_STS_SUCCESS;
531 l_api_name varchar2(45) := 'po_purge' ;
532 begin
533
534 -- Standrad call to check API compatibility.
535 -- API standards requires a standard code to validate
536 -- api versions and init message list etc.
537 -- Common code must be added to all the program unit.
538 --
539 common_code ( p_api_version ,
540 p_init_msg_list,
541 p_commit ,
542 p_validation_level ,
543 l_msg_count ,
544 l_msg_data ,
545 l_return_status ,
546 l_api_name ) ;
547
548 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
549 -- Program logic begins here
550 IF p_in_rec.entity_ids.COUNT > 0 THEN
551 FORALL i in p_in_rec.entity_ids.first.. p_in_rec.entity_ids.last
552 delete from gms_award_distributions adl
553 where ( award_set_id , po_distribution_id) in
554 ( select pod.award_id, po_distribution_id
555 from po_distributions_all pod
556 where pod.po_header_id = p_in_rec.entity_ids(i)
557 and pod.award_id is not NULL )
558 and document_type = 'PO' ;
559 END IF ;
560 END IF ;
561 -- Program Logic ends here
562
563 x_msg_count := l_msg_count ;
564 x_msg_data := l_msg_data ;
565 x_return_status := l_return_status ;
566
567 END PO_PURGE ;
568
569
570 -- Start of comments
571 -- -----------------
572 -- API Name : get_award_number
573 -- Type : This is a Public package program unit.
574 -- Pre Reqs : None
575 -- Function : This API is for deriving the award number for the award_set_id
576 -- passed to the api. This is used in PO Fundscheck code to
577 -- populate reference column in gl bc packet with award number.
578 -- Logic : get the award number from the adl and gms_awards_all..
579 -- Calling API : PO funds check code PL/SQL Version.
580 -- Parameters :
581 -- : Standard parameters
582 -- p_api_version, p_commit, p_init_msg_list,
583 -- p_validation_level, x_msg_count, x_msg_data,
584 -- x_return_status
585 -- IN
586 -- : p_award_set_id_tbl
587 -- The list of award set IDs.
588 -- OUT x_award_num_tbl
589 -- The list of award number sent out.
590 -- End of comments
591 -- ----------------
592
593 PROCEDURE get_award_number ( p_api_version in NUMBER,
594 p_init_msg_list in varchar2,
595 p_commit in varchar2,
596 p_validation_level in NUMBER,
597 x_msg_count out nocopy number,
598 x_msg_data out nocopy varchar2,
599 x_return_status out nocopy varchar2,
600 p_award_set_id_tbl IN tbl_num,
601 x_award_num_tbl OUT nocopy tbl_v15) is
602
603 l_msg_count NUMBER ;
604 l_msg_data varchar2(2000) ;
605 l_return_status varchar2(1) := fnd_api.G_RET_STS_SUCCESS;
606 l_api_name varchar2(45) := 'get_award_number' ;
607 l_award_set_id NUMBER ;
608 l_award_number gms_awards_all.award_number%TYPE ;
609 l_count NUMBER ;
610 begin
611
612 -- Standrad call to check API compatibility.
613 -- API standards requires a standard code to validate
614 -- api versions and init message list etc.
615 -- Common code must be added to all the program unit.
616 --
617 common_code ( p_api_version ,
618 p_init_msg_list,
619 p_commit ,
620 p_validation_level ,
621 l_msg_count ,
622 l_msg_data ,
623 l_return_status ,
624 l_api_name ) ;
625
626 -- Program logic begins here
627 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
628 IF p_award_set_id_tbl.count > 0 THEN
629
630 FOR l_index in 1..p_award_set_id_tbl.count LOOP
631 l_award_set_id := p_award_set_id_tbl(l_index) ;
632 l_count := l_index ;
633
634 select awd.award_number
635 into l_award_number
636 from gms_awards_all awd, gms_award_distributions adl
637 where adl.award_set_id = l_award_set_id
638 and adl.award_id = awd.award_id ;
639
640 x_award_num_tbl(l_index) := l_award_number ;
641
642 END LOOP ;
643 END IF ;
644 END IF ;
645 -- Program Logic ends here
646 x_msg_count := l_msg_count ;
647 x_msg_data := l_msg_data ;
648 x_return_status := l_return_status ;
649
650 EXCEPTION
651 WHEN OTHERS THEN
652 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
653
654 IF FND_MSG_PUB.check_msg_level( fnd_msg_pub.g_msg_lvl_unexp_error) THEN
655 fnd_msg_pub.add_exc_msg( G_pkg_name, l_api_name ) ;
656 END IF ;
657
658 FND_MSG_PUB.count_and_get ( p_encoded => fnd_api.g_false,
659 p_count => x_msg_count ,
660 p_data => x_msg_data ) ;
661 END get_award_number ;
662
663 END GMS_PO_API2_GRP ;
664
[Home] [Help]