[Home] [Help]
PACKAGE BODY: APPS.GMS_AP_API
Source
1 PACKAGE BODY GMS_AP_API AS
2 -- $Header: gmsapx1b.pls 120.3 2006/05/22 12:31:49 asubrama noship $
3
4 /* Declare procedure GMS_AP_ER_HEADERS_AUT1
5 P_invoice_id Invoice_id for AP_invoice_distributions_all
6 p_report_header_id Ap_expense_report_headers ID
7 p_reject_code Rejection identifier
8 */
9 -- For Bug 3269365: To check on, whether to print debug messages in log file or not
10 L_DEBUG varchar2(1) := NVL(FND_PROFILE.value('GMS_ENABLE_DEBUG_MODE'), 'N');
11 --End of Bug 3269365
12
13 PROCEDURE GMS_AP_ER_HEADERS_AUT1 (P_invoice_id IN NUMBER,
14 p_report_header_id IN NUMBER,
15 P_reject_code IN VARCHAR2 ) is
16
17 X_expenditure_item_id NUMBER ;
18 X_cdl_line_num NUMBER ;
19 X_award_set_id NUMBER ;
20 X_ADL_REC GMS_AWARD_DISTRIBUTIONS%ROWTYPE ;
21 X_invoice_id NUMBER ;
22 X_invoice_dist_id NUMBER ;
23 X_report_header_id NUMBER ;
24 X_reject_code Varchar2(25) ;
25
26 CURSOR C_ADL is
27 select *
28 from gms_award_distributions adl
29 where expenditure_item_id = x_expenditure_item_id
30 and cdl_line_num = x_cdl_line_num
31 and document_type = 'EXP'
32 and adl_status = 'A' ;
33
34 CURSOR C_ER_lines is
35 select erl.reference_1 expenditure_item_id,
36 erl.reference_2 CDL_LINE_NUM,
37 erl.project_id PROJECT_ID,
38 erl.task_id TASK_ID,
39 erl.distribution_line_number distribution_line_number,
40 apd.invoice_distribution_id invoice_distribution_id,
41 apd.award_id award_set_id
42 from ap_expense_report_lines_all erl,
43 ap_invoice_distributions_all apd,
44 pa_projects_all p,
45 gms_project_types gpt
46 where report_header_id = X_report_header_id
47 and erl.distribution_line_number= apd.distribution_line_number
48 and apd.invoice_id = X_invoice_id
49 and erl.project_id = apd.project_id
50 and erl.task_id = apd.task_id
51 and erl.expenditure_type = apd.expenditure_type
52 and erl.project_id = p.project_id
53 and p.project_type = gpt.project_type
54 and NVL(gpt.sponsored_flag,'N') = 'Y' ;
55
56 BEGIN
57 X_invoice_id := p_invoice_id ;
58 X_report_header_id:= p_report_header_id ;
59 X_reject_code := p_reject_code ;
60
61 FOR C_ER_REC IN c_er_lines LOOP
62
63 X_expenditure_item_id := C_er_rec.expenditure_item_id ;
64 X_cdl_line_num := c_er_rec.cdl_line_num ;
65
66 open c_adl ;
67 fetch c_adl into X_adl_rec ;
68
69 IF c_adl%FOUND THEN
70 X_adl_rec.expenditure_item_id := NULL ;
71 X_adl_rec.cdl_line_num := NULL ;
72 X_adl_rec.document_type := 'AP' ;
73 X_adl_rec.invoice_id := X_invoice_id ;
74 X_adl_rec.distribution_line_number := C_ER_REC.distribution_line_number ;
75 X_adl_rec.invoice_distribution_id := C_ER_REC.invoice_distribution_id ;
76 X_adl_rec.burdenable_raw_cost := NULL ;
77 X_adl_rec.cost_distributed_flag := 'N' ;
78 X_adl_rec.Raw_cost := NULL ;
79 X_adl_rec.FC_STATUS := 'N' ;
80 X_adl_rec.adl_status := 'A' ;
81 X_adl_rec.adl_line_num := 1 ;
82 X_adl_rec.award_set_id := gms_awards_dist_pkg.get_award_set_id ;
83
84
85
86 IF c_er_rec.award_set_id is Not NULL OR
87 NVL(X_reject_code, 'X' ) <> 'X' THEN
88
89 update gms_award_distributions
90 set adl_status = 'I'
91 where award_set_id = NVL(c_er_rec.award_set_id,0)
92
93
94 and document_type = 'AP'
95 and adl_status = 'A'
96 and invoice_id = X_invoice_id
97 and distribution_line_number= C_ER_REC.distribution_line_number
98 and invoice_distribution_id = c_er_rec.invoice_distribution_id;
99
100 END IF ;
101
102 gms_awards_dist_pkg.create_adls(x_adl_rec) ;
103
104 update ap_invoice_distributions_all
105 set award_id = X_adl_rec.award_set_id
106 where invoice_id = X_invoice_id
107 and distribution_line_number = X_adl_rec.distribution_line_number
108
109
110 and invoice_distribution_id = X_adl_rec.invoice_distribution_id ;
111
112
113 END IF ;
114
115 CLOSE C_ADL ;
116
117 END LOOP ;
118
119 EXCEPTION
120 WHEN OTHERS THEN
121 RAISE ;
122 END GMS_AP_ER_HEADERS_AUT1 ;
123
124 -- Start of comments
125 -- -----------------
126 -- API Name : V_CHECK_LINE_AWARD_INFO
127 -- Type : public
128 -- Pre Reqs : None
129 -- Description : Validate award related information.
130 --
131 -- Function : This function is called from AP_IMPORT_INVOICES_PKG.
132 -- Calling API : ap_import_invoices_pkg.v_check_line_project_info
133 --
134 -- End of comments
135 -- ----------------
136
137
138 FUNCTION V_CHECK_LINE_AWARD_INFO (
139 p_invoice_line_id IN NUMBER DEFAULT NULL,
140 p_line_amount IN NUMBER DEFAULT NULL,
141 p_base_line_amount IN NUMBER DEFAULT NULL,
142 p_dist_code_concatenated IN VARCHAR2 DEFAULT NULL,
143 p_dist_code_combination_id IN OUT NOCOPY NUMBER,
144 p_default_po_number IN VARCHAR2 DEFAULT NULL,
145 p_po_number IN VARCHAR2 DEFAULT NULL,
146 p_po_header_id IN NUMBER DEFAULT NULL,
147 p_distribution_set_id IN NUMBER DEFAULT NULL,
148 p_distribution_set_name IN VARCHAR2 DEFAULT NULL,
149 p_set_of_books_id IN NUMBER DEFAULT NULL,
150 p_base_currency_code IN VARCHAR2 DEFAULT NULL,
151 p_invoice_currency_code IN VARCHAR2 DEFAULT NULL,
152 p_exchange_rate IN NUMBER DEFAULT NULL,
153 p_exchange_rate_type IN VARCHAR2 DEFAULT NULL,
154 p_exchange_rate_date IN DATE DEFAULT NULL,
155 p_project_id IN NUMBER DEFAULT NULL,
156 p_task_id IN NUMBER DEFAULT NULL,
157 p_expenditure_type IN VARCHAR2 DEFAULT NULL,
158 p_expenditure_item_date IN DATE DEFAULT NULL,
159 p_expenditure_organization_id IN NUMBER DEFAULT NULL,
160 p_project_accounting_context IN VARCHAR2 DEFAULT NULL,
161 p_pa_addition_flag IN VARCHAR2 DEFAULT NULL,
162 p_pa_quantity IN NUMBER DEFAULT NULL,
163 p_employee_id IN NUMBER DEFAULT NULL,
164 p_vendor_id IN NUMBER DEFAULT NULL,
165 p_chart_of_accounts_id IN NUMBER DEFAULT NULL,
166 p_pa_installed IN VARCHAR2 DEFAULT NULL,
167 p_prorate_across_flag IN VARCHAR2 DEFAULT NULL,
168 p_lines_attribute_category IN VARCHAR2 DEFAULT NULL,
169 p_lines_attribute1 IN VARCHAR2 DEFAULT NULL,
170 p_lines_attribute2 IN VARCHAR2 DEFAULT NULL,
171 p_lines_attribute3 IN VARCHAR2 DEFAULT NULL,
172 p_lines_attribute4 IN VARCHAR2 DEFAULT NULL,
173 p_lines_attribute5 IN VARCHAR2 DEFAULT NULL,
174 p_lines_attribute6 IN VARCHAR2 DEFAULT NULL,
175 p_lines_attribute7 IN VARCHAR2 DEFAULT NULL,
176 p_lines_attribute8 IN VARCHAR2 DEFAULT NULL,
177 p_lines_attribute9 IN VARCHAR2 DEFAULT NULL,
178 p_lines_attribute10 IN VARCHAR2 DEFAULT NULL,
179 p_lines_attribute11 IN VARCHAR2 DEFAULT NULL,
180 p_lines_attribute12 IN VARCHAR2 DEFAULT NULL,
181 p_lines_attribute13 IN VARCHAR2 DEFAULT NULL,
182 p_lines_attribute14 IN VARCHAR2 DEFAULT NULL,
183 p_lines_attribute15 IN VARCHAR2 DEFAULT NULL,
184 p_attribute_category IN VARCHAR2 DEFAULT NULL,
185 p_attribute1 IN VARCHAR2 DEFAULT NULL,
186 p_attribute2 IN VARCHAR2 DEFAULT NULL,
187 p_attribute3 IN VARCHAR2 DEFAULT NULL,
188 p_attribute4 IN VARCHAR2 DEFAULT NULL,
189 p_attribute5 IN VARCHAR2 DEFAULT NULL,
190 p_attribute6 IN VARCHAR2 DEFAULT NULL,
191 p_attribute7 IN VARCHAR2 DEFAULT NULL,
192 p_attribute8 IN VARCHAR2 DEFAULT NULL,
193 p_attribute9 IN VARCHAR2 DEFAULT NULL,
194 p_attribute10 IN VARCHAR2 DEFAULT NULL,
195 p_attribute11 IN VARCHAR2 DEFAULT NULL,
196 p_attribute12 IN VARCHAR2 DEFAULT NULL,
197 p_attribute13 IN VARCHAR2 DEFAULT NULL,
198 p_attribute14 IN VARCHAR2 DEFAULT NULL,
199 p_attribute15 IN VARCHAR2 DEFAULT NULL,
200 p_partial_segments_flag IN VARCHAR2 DEFAULT NULL,
201 p_default_last_updated_by IN NUMBER DEFAULT NULL,
202 p_default_last_update_login IN NUMBER DEFAULT NULL,
203 p_calling_sequence IN VARCHAR2 DEFAULT NULL,
204 p_award_id IN OUT NOCOPY NUMBER,
205 P_EVENT IN varchar2 ) return BOOLEAN
206 IS
207 lb_return BOOLEAN ;
208 l_output varchar2(2000) ;
209 l_award_set_id NUMBER ;
210 l_adl_rec gms_award_distributions%ROWTYPE ;
211 BEGIN
212 lb_return := TRUE ;
213 -- IF NOT gms_install.enabled THEN -- Bug 3002305.
214 IF NOT gms_pa_api.vert_install THEN
215 RETURN lb_return ;
216 END IF ;
217
218 IF P_EVENT = 'AWARD_SET_ID_REQUEST' THEN
219 GMS_TRANSACTIONS_PUB.VALIDATE_TRANSACTION( P_project_id ,
220 P_task_id ,
221 P_award_id ,
222 P_expenditure_type ,
223 P_expenditure_item_date ,
224 p_calling_sequence ,
225 l_output ) ;
226 IF l_output is not NULL THEN
227 --For bug 3269365 :ATG project common logging . Introduced gms_debug
228 IF L_DEBUG = 'Y' THEN
229 gms_error_pkg.set_debug_context ;
230 gms_error_pkg.gms_debug( 'GMS:'||l_output||' Invoice line ID :'||p_invoice_line_id, 'C') ;
231 --fnd_file.put_line(FND_FILE.LOG, 'GMS:'||l_output||' Invoice line ID :'||p_invoice_line_id) ;
232 END IF;
233 --End of bug 3269365
234 lb_return := FALSE ;
235 ELSE
236 /* AP Lines change: Create adls when p_award_id is not null.*/
237 IF p_award_id IS NULL then
238 Return lb_return;
239 END IF ;
240 /* Create ADL for Account generator . */
241
242 l_award_set_id := gms_awards_dist_pkg.get_award_set_id ;
243 l_adl_rec.award_set_id := l_award_set_id ;
244 l_adl_rec.adl_line_num := 1 ;
245 l_adl_rec.document_type := 'OPI' ;
246 l_adl_rec.distribution_value := 100 ;
247 l_adl_rec.project_id := p_project_id ;
248 l_adl_rec.task_id := p_task_id ;
249 l_adl_rec.award_id := p_award_id ;
250 l_adl_rec.request_id := FND_GLOBAL.CONC_REQUEST_ID ;
251 l_adl_rec.adl_status := 'A' ;
252 l_adl_rec.line_type := 'R' ;
253 l_adl_rec.invoice_distribution_id := p_invoice_line_id ;
254 gms_awards_dist_pkg.create_adls( l_adl_rec ) ;
255 p_award_id := l_award_set_id ;
256 END IF ;
257
258 ELSIF P_EVENT = 'AWARD_SET_ID_REMOVE' THEN
259 delete from gms_award_distributions
260 where award_set_id = p_award_id
261 and adl_line_num = 1
262 and document_type = 'OPI' ;
263 END IF ;
264 return lb_return ;
265 EXCEPTION
266 WHEN others then
267 lb_return := FALSE ;
268 RAISE ;
269 END V_CHECK_LINE_AWARD_INFO;
270
271 -- Start of comments
272 -- -----------------
273 -- API Name : CREATE_AWARD_DISTRIBUTIONS
274 -- Type : public
275 -- Pre Reqs : None
276 -- Description : Create award distribution lines for the parameter
277 -- passed.
278 -- Calling API : ap_import_invoices_pkg
279 --
280 -- End of comments
281 -- ----------------
282
283 PROCEDURE CREATE_AWARD_DISTRIBUTIONS( p_invoice_id IN NUMBER,
284 p_distribution_line_number IN NUMBER,
285 p_invoice_distribution_id IN NUMBER,
286 p_award_id IN NUMBER,
287 p_mode IN VARCHAR2 default 'AP',
288 p_dist_set_id IN NUMBER default NULL,
289 p_dist_set_line_number IN NUMBER default NULL
290 ) is
291 CURSOR C_ADL_REC is
292 SELECT *
293 from gms_award_distributions ADL
294 where award_set_id = p_award_id
295 and adl_status = 'A'
296 and adl_line_num = 1 -- AP Lines uptake
297 and document_type= 'APD' ;
298 cursor c_ap_rec is
299 SELECT A.invoice_id INVOICE_ID,
300 A.distribution_line_number distribution_line_number,
301 A.invoice_distribution_id invoice_distribution_id,
302 A.project_id PROJECT_ID,
303 A.task_id TASK_ID,
304 A.last_update_date LAST_UPDATE_DATE,
305 A.creation_date CREATION_DATE,
306 A.last_updated_by LAST_UPDATED_BY,
307 A.created_by CREATED_BY,
308 A.last_update_login LAST_UPDATE_LOGIN
309 from ap_invoice_distributions_all A,
310 pa_projects_all B,
311 gms_project_types C
312 where invoice_id = p_invoice_id
313 and distribution_line_number = p_distribution_line_number
314 and invoice_distribution_id = p_invoice_distribution_id
315 and a.project_id = b.project_id
316 and b.project_type = c.project_type
317 and c.sponsored_flag = 'Y' ;
318 l_ap_rec c_ap_rec%ROWTYPE ;
319 l_adl_rec gms_award_distributions%ROWTYPE ;
320 BEGIN
321 -- IF NOT gms_install.enabled THEN -- Bug 3002305
322 IF NOT gms_pa_api.vert_install THEN
323 return ;
324 END IF ;
325 IF p_mode = 'AP' THEN
326 FOR LOOP_AP_REC IN C_AP_REC LOOP
327 l_adl_rec.award_set_id := gms_awards_dist_pkg.get_award_set_id ;
328 l_adl_rec.invoice_id := p_invoice_id ;
332 l_adl_rec.project_id := loop_ap_rec.project_id ;
329 l_adl_rec.distribution_line_number := p_distribution_line_number ;
330 l_adl_rec.invoice_distribution_id := p_invoice_distribution_id ;
331 l_adl_rec.document_type := 'AP' ;
333 l_adl_rec.task_id := loop_ap_rec.task_id ;
334 l_adl_rec.award_id := p_award_id ;
335 l_adl_rec.adl_line_num := 1 ;
336 l_adl_rec.distribution_value := 100 ;
337 l_adl_rec.adl_status := 'A' ;
338 l_adl_rec.line_type := 'R' ;
339 l_adl_rec.last_update_date := loop_ap_rec.last_update_date ;
340 l_adl_rec.creation_date := loop_ap_rec.creation_date ;
341 l_adl_rec.last_updated_by := loop_ap_rec.last_updated_by ;
342 l_adl_rec.created_by := loop_ap_rec.created_by ;
343 l_adl_rec.last_update_login := loop_ap_rec.last_update_login ;
344 gms_awards_dist_pkg.create_adls( l_adl_rec ) ;
345 update ap_invoice_distributions_all
346 set award_id = l_adl_rec.award_set_id
347 where invoice_id = loop_ap_rec.invoice_id
348 and distribution_line_number = loop_ap_rec.distribution_line_number
349 and invoice_distribution_id = loop_ap_rec.invoice_distribution_id ;
350
351 END LOOP ;
352 ELSIF p_mode = 'APD' THEN
353 OPEN C_ADL_REC ;
354 FETCH C_ADL_REC INTO l_ADL_REC ;
355 IF C_ADL_REC%FOUND THEN
356 l_adl_rec.award_set_id := gms_awards_dist_pkg.get_award_set_id ;
357 l_adl_rec.invoice_id := p_invoice_id ;
358 l_adl_rec.distribution_line_number := p_distribution_line_number ;
359 l_adl_rec.invoice_distribution_id := p_invoice_distribution_id ;
360 l_adl_rec.document_type := 'AP' ;
361 l_adl_rec.line_type := 'R' ;
362 gms_awards_dist_pkg.create_adls( l_adl_rec ) ;
363 update ap_invoice_distributions_all
364 set award_id = l_adl_rec.award_set_id
365 where invoice_id = p_invoice_id
366 and distribution_line_number = p_distribution_line_number
367
368
369 and invoice_distribution_id = p_invoice_distribution_id ;
370
371
372 END IF ;
373 CLOSE C_ADL_REC ;
374 END IF ;
375 return ;
376
377
378 EXCEPTION
379
380
381 WHEN OTHERS THEN
382
383
384 RAISE ;
385 END CREATE_AWARD_DISTRIBUTIONS ;
386
387
388 -- Start of comments
389 -- -----------------
390 -- API Name : GET_DISTRIBUTION_AWARD
391 -- Type : public
392 -- Pre Reqs : None
393 -- Description : Get award_id attached to adls associated with the
394 -- ap invoice distribution lines.
395 -- Calling API : ap_import_invoices_pkg
396 --
397 -- End of comments
398 -- ----------------
399
400 -- ===================================================================================
401 -- BUG : 2972963
402 -- APXIIMPT IMPORT ERRORS WITH REP-1419 AND ORA-01400 WHEN PRORATING NON_ITEM_LINE
403 -- Analysis - There is a error in cursors select C_ADL_REC.
404 -- Invalid join award_set_id = p_award_id causing no data found.
405 -- Resolution -
406 -- Join condition was corrected as follows :
407 -- award_set_id = p_award_set_id
408 -- ==================================================================================
409
410 Procedure GET_DISTRIBUTION_AWARD ( p_invoice_id IN NUMBER,
411 p_distribution_line_number IN NUMBER,
412 p_invoice_distribution_id IN NUMBER,
413 p_award_set_id IN NUMBER,
414 p_award_id IN OUT NOCOPY NUMBER ) IS
415 l_award_id NUMBER ;
416
417 CURSOR C_ADL_REC is
418 select award_id
419 from gms_award_distributions ADL
420 where award_set_id = p_award_set_id
421 and adl_line_num = 1 ;
422
423 --and document_type = 'AP'
424 --and adl_status = 'A'
425 --and invoice_id = p_invoice_id
426 --and distribution_line_number = p_distribution_line_number ;
427 --where award_set_id = p_award_id ( 2972963 Fix )
428 BEGIN
429 -- =======================================================================
430 -- Code Review feedback.
431 -- We don't need to check for gms_install.enabled here. we should check for
432 -- p_award_set_id is not null. Currently test case failed for non spon
433 -- project.
434 -- =======================================================================
435
436 IF p_award_set_id is NULL THEN
437 return ;
438 END IF ;
439
440 --IF NOT gms_install.enabled THEN
441 -- return ;
442 --END IF ;
443
444
445 OPEN C_ADL_REC ;
446
447
448 fetch C_ADL_REC into l_award_id ;
449
450
451 IF C_ADL_REC%FOUND THEN
452
453
454 p_award_id := l_award_id ;
455 ELSE
456
457
458 raise no_data_found ;
459 END IF ;
460
461
462 CLOSE C_ADL_REC ;
463
464
465 EXCEPTION
466
467
468 WHEN no_data_found then
469
470
471 IF C_ADL_REC%ISOPEN THEN
472 CLOSE C_ADL_REC ;
473 END IF ;
474 RAISE ;
475 WHEN OTHERS THEN
479 END GET_DISTRIBUTION_AWARD ;
476
477
478 RAISE ;
480
481
482 FUNCTION GMS_DEBUG_SWITCH( p_debug_flag varchar2 ) return boolean is
483 BEGIN
484 IF p_debug_flag in ( 'y', 'Y' ) then
485 return TRUE ;
486 ELSE
487 return FALSE ;
488 END IF ;
489
490 EXCEPTION
491 WHEN OTHERS THEN
492 raise ;
493 END GMS_DEBUG_SWITCH ;
494
495 /* AP Lines change: Added additional parameter p_invoice_distribution_id */
496 PROCEDURE create_prepay_adl( p_prepay_dist_id IN NUMBER,
497 p_invoice_id IN NUMBER,
498 p_next_dist_line_num IN NUMBER,
499 p_invoice_distribution_id IN NUMBER
500 ) is
501 X_award_set_id NUMBER ;
502 x_adl_rec gms_award_distributions%ROWTYPE ;
503 x_ap_rec ap_invoice_distributions_all%ROWTYPE ;
504
505 x_inv_dist_id NUMBER ;
506 X_amount NUMBER ;
507 Cursor C_get_adl_id is
508 select award_id
509 from ap_invoice_distributions_all
510 where Invoice_distribution_id = p_prepay_dist_id ;
511 Cursor C_adl is
512 select *
513 from gms_award_distributions
514 where award_set_id = X_award_set_id
515 and adl_line_num = 1 ;
516 Cursor c_new_dist_line is
517 select *
518 from ap_invoice_distributions_all
519 where invoice_id = p_invoice_id
520 and distribution_line_number = p_next_dist_line_num
521 and invoice_distribution_id = p_invoice_distribution_id; -- AP Line change: added additional join
522 BEGIN
523 -- IF NOT gms_install.enabled THEN -- Bug 3002305
524 IF NOT gms_pa_api.vert_install THEN
525 return ;
526 END IF ;
527
528 OPEN C_get_adl_id ;
529 FETCH C_get_adl_id into X_award_set_id ;
530 CLOSE C_get_adl_id ;
531 IF NVL(X_award_set_id,0) = 0 THEN
532 RETURN ;
533 END IF ;
534 OPEN C_ADL ;
535 FETCH C_ADL into X_ADL_REC ;
536 IF C_ADL%NOTFOUND THEN
537 close c_adl ;
538 return ;
539 END IF ;
540 CLOSE C_adl ;
541 open c_new_dist_line ;
542 FETCH c_new_dist_line into x_ap_rec ;
543 CLOSE c_new_dist_line ;
544 x_amount := x_ap_rec.amount ;
545 x_inv_dist_id := x_ap_rec.invoice_distribution_id ;
546 x_adl_rec.award_set_id := gms_awards_dist_pkg.get_award_set_id ;
547
548 x_adl_rec.invoice_id := p_invoice_id;
549 x_adl_rec.invoice_distribution_id := x_inv_dist_id ;
550 x_adl_rec.distribution_line_number := p_next_dist_line_num ;
551 x_adl_rec.document_type := 'AP' ;
552 x_adl_rec.adl_status := 'A' ;
553 x_adl_rec.expenditure_item_id := NULL;
554 -- =====================================================
555 -- We know that funds check doesnt happen in this case.
556 -- Verify in testing ....
557 -- =====================================================
558 x_adl_rec.fc_status := 'N' ;
559 x_adl_rec.burdenable_raw_cost := 0 ;
560 gms_awardS_dist_pkg.create_adls( X_adl_rec) ;
561 UPDATE GMS_AWARD_DISTRIBUTIONS
562 SET BUD_TASK_ID = x_adl_rec.BUD_TASK_ID
563 WHERE AWARD_SET_ID = x_adl_rec.award_set_id
564 AND ADL_STATUS = 'A' ;
565 Update ap_invoice_distributions_all
566 Set award_id = x_adl_rec.award_set_id
567 Where invoice_distribution_id = x_inv_dist_id ;
568
569 gms_cost_plus_extn.CALC_prepayment_burden( X_AP_REC, x_adl_rec) ;
570
571 EXCEPTION
572 when others then
573 raise;
574 END create_prepay_adl ;
575
576 --============================================================================
577 -- BUG: 2676134 ( APXIIMPT distribution set with invalid award not validated.
578 --============================================================================
579 PROCEDURE get_dist_set_award ( p_distribution_set_id IN NUMBER,
580 p_distribution_set_line_number IN NUMBER,
581 p_award_set_id IN NUMBER,
582 p_award_id IN OUT NOCOPY NUMBER ) is
583 cursor c_get_award is
584 select adl.award_id
585 from ap_distribution_set_lines apd,
586 gms_award_distributions adl
587 where apd.distribution_set_id = p_distribution_set_id
588 and apd.distribution_set_line_number = p_distribution_set_line_number
589 and adl.award_set_id = p_award_set_id
590 and apd.award_id = adl.award_set_id
591 and adl.adl_line_num = 1 ;
592
593 l_award_id NUMBER ;
594 BEGIN
595 IF p_award_set_id is not NULL THEN
596 open c_get_award ;
597 fetch c_get_award into l_award_id ;
598 close c_get_award ;
599
600 p_award_id := l_award_id ;
601 END IF ;
602
603 END get_dist_set_award ;
604
605
606 -- Start of comments
607 -- -----------------
608 -- API Name : check_award_funding
609 -- Type : private
610 -- Pre Reqs : None
611 -- BUG : 3077074
612 -- Description : EXECEPTION HANDLING TO GMS_AP_API TO SUPPORT CODE HOOK IN AP APPROVAL.
613 --
614 -- Function : check award funding identifies the award funding the project.
615 -- Calling API : verify_create_adl
616 -- End of comments
617 -- ----------------
618
619 PROCEDURE check_award_funding ( p_project_id IN NUMBER,
620 p_award_id IN OUT NOCOPY NUMBER,
621 p_status out NOCOPY NUMBER ) is
622
623 l_award_id NUMBER ;
624 l_status NUMBER ;
625
626 -- =====================================================
627 -- Cursor : c_validate_award
628 -- Cursor verifies that award is funded by the
629 -- project.
630 -- =====================================================
631 cursor c_validate_award is
632 select ins.award_id
633 from gms_installments ins,
634 gms_summary_project_fundings pf
635 where ins.installment_id = pf.installment_id
636 and pf.project_id = p_project_id
637 and ins.award_id = p_award_id ;
638
639 -- =====================================================
640 -- Cursor : c_get_award
641 -- Cursor finds out if there is a award funding the
642 -- project charged to a transaction.
643 -- =====================================================
644 cursor c_get_award is
645 select ins.award_id
646 from gms_installments ins,
647 gms_summary_project_fundings pf
648 where ins.installment_id = pf.installment_id
649 and pf.project_id = p_project_id
650 and NOT EXISTS ( select 1 from gms_installments ins2,
651 gms_summary_project_fundings pf2
652 where ins2.installment_id = pf2.installment_id
653 and pf2.project_id = pf.project_id
654 and ins2.award_id <> ins.award_id ) ;
655 BEGIN
656 l_award_id := p_award_id ;
657 l_status := 0 ;
658
659 -- =================================
660 -- Validate award.
661 -- =================================
662 IF p_award_id is not NULL THEN
663 open c_validate_award ;
664 fetch c_validate_award into l_award_id ;
665 close c_validate_award ;
666 END IF ;
667
668 -- There is no valid award yet.
669 -- checking to see if there
670
671 IF l_award_id is NULL THEN
672 open c_get_award ;
673 fetch c_get_award into l_award_id ;
674 close c_get_award ;
675 END IF ;
676
677 IF l_award_id is NULL THEN
678 l_status:= -1 ;
679 ELSE
680 p_award_id := l_award_id ;
681 END IF ;
682
683 p_status := l_status ;
684
685 END check_award_funding ;
686 -- End of check_award_funding
687 -- ----------------------------
688
689 -- Start of comments
690 -- -----------------
691 -- API Name : verify_create_adl
692 -- Type : public
693 -- Pre Reqs : None
694 -- BUG : 2789359, 3046767
695 -- Description : RECURRING INVOICES USING AP DISTRIBUTION SETS FAILING
696 -- GL FUNDSCHECK F00
697 -- GMS: ENHANCE GMS LOGIC FOR AWARD DISTRIBUTION LINE VALIDATIONS.
698 -- Function : This is used to create award distribution lines
699 -- using the bulk processing. This provides a
703 -- sponsored project.
700 -- interface with ap recurring invoice feature.
701 -- Logic : Identify the newly created invoice distribution
702 -- lines and create award distribution lines for
704 -- Parameters :
705 -- IN : p_invoice_id IN NUMBER
706 -- The invoice id created and that may
707 -- have distributions associated with
708 -- an award.
709 -- : p_calling_sequence IN varchar2
710 -- calling sequence of the API for the debugging purpose.
711 -- Calling API : AP_RECURRING_INVOICES_PKG.ap_create_recurring_invoices
712 -- AP_APPROVAL_PKG.APPROVE
713 -- End of comments
714 -- ----------------
715
716 PROCEDURE VERIFY_CREATE_ADLS( p_invoice_id IN NUMBER ,
717 p_calling_sequence IN VARCHAR2 ) is
718
719
720 type gms_ap_type_number is table of number index by binary_integer;
721 type gms_ap_type_date is table of date index by binary_integer;
722
723 l_invoice_id gms_ap_type_number;
724 l_distribution_line_number gms_ap_type_number;
725 l_invoice_distribution_id gms_ap_type_number;
726 l_project_id gms_ap_type_number;
727 l_task_id gms_ap_type_number;
728 l_award_id gms_ap_type_number;
729 l_new_award_set_id gms_ap_type_number;
730 l_last_update_date gms_ap_type_date;
731 l_creation_date gms_ap_type_date;
732 l_last_updated_by gms_ap_type_number;
733 l_created_by gms_ap_type_number;
734 l_last_update_login gms_ap_type_number;
735 l_dummy_tab gms_ap_type_number;
736
737 l_award_set_id gms_ap_type_number ;
738 l_award_set_idX NUMBER ;
739 l_count NUMBER ;
740 l_project_idX NUMBER ;
741 l_award_idX NUMBER ;
742 l_status NUMBER ;
743 AWARD_NOT_FOUND EXCEPTION ;
744
745 l_invoice_num ap_invoices_all.invoice_num%TYPE ;
746
747 cursor c_ap is
748 SELECT A.invoice_id INVOICE_ID,
749 A.distribution_line_number distribution_line_number,
750 A.invoice_distribution_id invoice_distribution_id,
751 A.project_id PROJECT_ID,
752 A.task_id TASK_ID,
753 A.award_id award_set_id,
754 A.last_update_date LAST_UPDATE_DATE,
755 A.creation_date CREATION_DATE,
756 A.last_updated_by LAST_UPDATED_BY,
757 A.created_by CREATED_BY,
758 NVL(A.last_update_login,0) LAST_UPDATE_LOGIN,
759 inv.invoice_num invoice_num
760 from ap_invoice_distributions_all A,
761 pa_projects_all p,
762 gms_project_types gpt,
763 ap_invoices_all inv
764 where a.invoice_id = p_invoice_id
765 and a.project_id = p.project_id
766 and p.project_type = gpt.project_type
767 and inv.invoice_id = a.invoice_id
768 and gpt.sponsored_flag = 'Y' ;
769
770 cursor c_adl is
771 select * from gms_award_distributions where award_set_id = l_award_set_idX
772 and adl_line_num = 1 ;
773 l_adl_rec c_adl%ROWTYPE ;
774
775 cursor c2 is
776 SELECT 1
777 from ap_invoice_distributions_all A,
778 pa_projects_all p,
779 gms_project_types gpt
780 where a.invoice_id = p_invoice_id
781 and a.project_id = p.project_id
782 and p.project_type = gpt.project_type
783 and gpt.sponsored_flag = 'Y'
784 and not exists ( select 1 from gms_award_distributions adl
785 where adl.invoice_id = p_invoice_id
786 and adl.distribution_line_number = A.distribution_line_number
787 and adl.invoice_distribution_id = A.invoice_distribution_id -- AP Lines uptake
788 and adl.document_type = 'AP'
789 and adl.award_set_id = NVL(a.award_id,0)
790 and adl.adl_line_num = 1
791 and adl.adl_status = 'A' ) ;
792
793
794
795 BEGIN
796
797 -- Start of comment
798 -- Verify that grants is enabled.
799 -- End of comments.
800 --
801 -- IF NOT gms_install.enabled THEN -- Bug 3002305
802 IF NOT gms_pa_api.vert_install THEN
803 return ;
804 END IF ;
805
806 -- Load the collection with the AP inv dist data first.
807 -- The AP inv dist should have the invoice_id in parameters
808 -- value.
809 -- The AP inv dist should have award_id column populated.
810
811 IF NVL(SUBSTR(p_calling_sequence, 1,15),'X') <> 'AP_APPROVAL_PKG' THEN
812
813 SELECT A.invoice_id INVOICE_ID,
814 A.distribution_line_number distribution_line_number,
815 A.invoice_distribution_id invoice_distribution_id,
816 A.project_id PROJECT_ID,
817 A.task_id TASK_ID,
818 ADL.award_id AWARD_ID,
822 A.last_updated_by LAST_UPDATED_BY,
819 A.award_id award_set_id,
820 A.last_update_date LAST_UPDATE_DATE,
821 A.creation_date CREATION_DATE,
823 A.created_by CREATED_BY,
824 NVL(A.last_update_login,0) LAST_UPDATE_LOGIN,
825 gms_awards_dist_pkg.get_award_set_id NEW_AWARD_SET_ID
826 BULK COLLECT INTO l_invoice_id,
827 l_distribution_line_number,
828 l_invoice_distribution_id,
829 l_project_id ,
830 l_task_id ,
831 l_award_id,
832 l_award_set_id,
833 l_last_update_date ,
834 l_creation_date ,
835 l_last_updated_by ,
836 l_created_by ,
837 l_last_update_login ,
838 l_new_award_set_id
839 from ap_invoice_distributions_all A,
840 gms_award_distributions adl
841 where a.invoice_id = p_invoice_id
842 and adl.award_set_id = a.award_id
843 and adl.adl_line_num = 1
844 and a.award_id IS NOT NULL;
845 END IF ;
846
847 -- 3046767 GMS: ENHANCE GMS LOGIC FOR AWARD DISTRIBUTION LINE VALIDATIONS. STARTS
848 IF NVL(SUBSTR(p_calling_sequence, 1,15),'X') = 'AP_APPROVAL_PKG' THEN
849 l_count := 0 ;
850
851 -- 2308005 ( CLEARING INVOICE DIST. LINE AFTER CHANGING AWARD MAKES ADL STATUS 'I' )
852 update gms_award_distributions adl
853 set adl.adl_status = 'A'
854 where adl.document_type = 'AP'
855 and adl.adl_status = 'I'
856 and adl.award_set_id in ( select adl2.award_set_id
857 from gms_award_distributions adl2,
858 ap_invoice_distributions_all apd
859 where apd.invoice_id = p_invoice_id
860 and apd.award_id is not null
861 and adl2.award_set_id = apd.award_id
862 and adl2.invoice_id = apd.invoice_id
863 and adl2.document_type = 'AP'
864 and adl2.distribution_line_number = apd.distribution_line_number
865 and adl2.invoice_distribution_id = apd.invoice_distribution_id -- added join for AP Lines uptake
866 and adl2.adl_status = 'I' ) ;
867
868 -- Inactivate ADLS that belongs to the AP distribution line but
869 -- not tied up with award_id in ap_distribution line.
870 -- Inactivate dangling active adls.
871 -- ----
872 update gms_award_distributions adl
873 set adl.adl_status = 'I'
874 where adl.document_type = 'AP'
875 and adl.adl_status = 'A'
876 and adl.award_set_id in ( select adl2.award_set_id
877 from gms_award_distributions adl2,
878 ap_invoice_distributions_all apd
879 where apd.invoice_id = p_invoice_id
880 and apd.award_id is not null
881 and adl2.award_set_id <> apd.award_id
882 and adl2.invoice_id = apd.invoice_id
883 and adl2.document_type= 'AP'
884 and adl2.distribution_line_number = apd.distribution_line_number
885 and adl2.invoice_distribution_id = apd.invoice_distribution_id -- added join for AP Lines uptake
886 and adl2.adl_status = 'A' ) ;
887
888
889 -- ==================================================
890 -- Update award_id to NULL for non sponsored
891 -- projects.
892 -- =================================================
893 -- Bug : 4956860
894 -- R12.PJ:XB2:DEV:GMS: APPSPERF: PACKAGE:GMSAPX1B.PLS
895 --
896 -- UPDATE ap_invoice_distributions_all apd
897 -- SET award_id = NULL
898 -- where apd.invoice_id = p_invoice_id
899 -- and apd.award_id is not NULL
900 -- and apd.invoice_distribution_id in ( select a.invoice_distribution_id
901 -- from ap_invoice_distributions_all A,
902 -- pa_projects_all p,
903 -- gms_project_types gpt
904 -- where a.invoice_id = p_invoice_id
905 -- and a.project_id = p.project_id
906 -- and p.project_type = gpt.project_type
907 -- and gpt.sponsored_flag = 'N' ) ;
908
909 /* 25-jan-2006
913 bulk collect into l_dummy_tab
910 ** Update statement was changed to bulk statement to resolve the share memory performance issue.
911 */
912 select a.invoice_distribution_id
914 from ap_invoice_distributions_all A,
915 pa_projects_all p,
916 gms_project_types gpt
917 where a.invoice_id = p_invoice_id
918 and a.project_id = p.project_id
919 and a.award_id is not NULL
920 and p.project_type = gpt.project_type
921 and gpt.sponsored_flag = 'N' ;
922
923 IF l_dummy_tab.count > 0 THEN
924
925 FORALL i in l_dummy_tab.FIRST..l_dummy_tab.LAST
926 UPDATE ap_invoice_distributions_all apd
927 SET award_id = NULL
928 where apd.invoice_id = p_invoice_id
929 and apd.invoice_distribution_id = l_dummy_tab(i) ;
930
931 END IF ;
932
933 FOR ap_rec in c_ap LOOP
934
935 l_award_set_idX := NVL(ap_rec.award_set_id,0) ;
936 l_invoice_num := ap_rec.invoice_num ;
937
938 l_adl_rec := NULL ;
939
940 open c_adl ;
941 fetch c_adl into l_adl_rec ;
942 close c_adl ;
943
944 IF NOT (( NVL(l_adl_rec.adl_status,'I') = 'A' ) and
945 ( NVL(l_adl_rec.document_type,'X') = 'AP' ) and
946 ( NVL(l_adl_rec.invoice_id,0) = NVL( ap_rec.invoice_id,0) ) AND
947 ( NVL(l_adl_rec.distribution_line_number,0) = NVL(ap_rec.distribution_line_number,0) ) AND
948 ( NVL(l_adl_rec.invoice_distribution_id,0) = NVL( ap_rec.invoice_distribution_id,0) )) THEN
949
950 l_count := l_count + 1 ;
951 l_invoice_id(l_count) := ap_rec.invoice_id ;
952 l_distribution_line_number(l_count) := ap_rec.distribution_line_number;
953 l_invoice_distribution_id(l_count) := ap_rec.invoice_distribution_id;
954 l_project_id(l_count) := ap_rec.project_id;
955 l_task_id(l_count) := ap_rec.task_id;
956 l_project_idX := ap_rec.project_id;
957 l_award_idX := l_adl_rec.award_id ;
958
959 check_award_funding( l_project_idX, l_award_idX, l_status ) ;
960
961 IF l_status = -1 THEN
962 raise AWARD_NOT_FOUND ;
963 ELSE
964 l_award_id(l_count) := l_award_idX ;
965 END IF ;
966
967 l_last_update_date(l_count) := ap_rec.last_update_date;
968 l_creation_date(l_count) := ap_rec.creation_date;
969 l_last_updated_by(l_count) := ap_rec.last_updated_by;
970 l_created_by(l_count) := NVL(ap_rec.created_by,0);
971 l_last_update_login(l_count) := ap_rec.last_update_login;
972 l_new_award_set_id(l_count) := gms_awards_dist_pkg.get_award_set_id ;
973
974 END IF ;
975
976 END LOOP ;
977
978 END IF ;
979 -- 3046767 GMS: ENHANCE GMS LOGIC FOR AWARD DISTRIBUTION LINE VALIDATIONS. END
980
981 -- Start of comments
982 -- Check if need to proceed.
983 -- End of comment.
984
985 IF l_invoice_id.count = 0 then
986 return ;
987 end if ;
988
989 -- Start of comment.
990 -- Loop through all the collection and insert into the ADL table.
991 -- Update the ap inv dist record with the newly created ADLs award set id.
992 -- End of comment
993
994
995 FORALL i in l_invoice_distribution_id.FIRST..l_invoice_distribution_id.LAST
996 INSERT into gms_award_distributions ( award_set_id ,
997 adl_line_num,
998 document_type,
999 distribution_value,
1000 project_id ,
1001 task_id ,
1002 award_id ,
1003 request_id ,
1004 adl_status ,
1005 fc_status ,
1006 line_type ,
1007 capitalized_flag ,
1008 capitalizable_flag ,
1009 revenue_distributed_flag ,
1010 billed_flag ,
1011 bill_hold_flag ,
1012 invoice_distribution_id ,
1013 invoice_id ,
1014 distribution_line_number ,
1015 burdenable_raw_cost ,
1016 cost_distributed_flag ,
1017 last_update_date ,
1018 last_updated_by ,
1019 created_by ,
1020 creation_date ,
1024 1, --adl_line_num,
1021 last_update_login ,
1022 billable_flag )
1023 VALUES ( l_new_award_set_id(i) ,
1025 'AP' , --document_type,
1026 100,
1027 l_project_id(i) ,
1028 l_task_id(i) ,
1029 l_award_id(i) ,
1030 l_distribution_line_number(i) ,
1031 'A', --adl_status ,
1032 'N', --fc_status ,
1033 'R', --line_type ,
1034 'N' ,
1035 'N' ,
1036 'N' ,
1037 'N' ,
1038 'N' ,
1039 l_invoice_distribution_id(i), --invoice_distribution_id ,
1040 l_invoice_id(i), --invoice_id ,
1041 l_distribution_line_number(i), --distribution_line_number ,
1042 NULL, --burdenable_raw_cost ,
1043 'N' ,
1044 l_last_update_date(i) ,
1045 l_last_updated_by(i) ,
1046 l_created_by(i) ,
1047 l_creation_date(i) ,
1048 l_last_update_login(i) ,
1049 'N') ;
1050
1051 -- Start of comment.
1052 -- Update AP distribution with the award set id.
1053 -- End of comment.
1054
1055 FORALL k in l_invoice_distribution_id.FIRST..l_invoice_distribution_id.LAST
1056 update ap_invoice_distributions_all
1057 set award_id = l_new_award_set_id(k)
1058 where invoice_id = l_invoice_id(k)
1059 and distribution_line_number = l_distribution_line_number(k)
1060 and invoice_distribution_id = l_invoice_distribution_id(k) ;
1061
1062 -- Bug 3077074
1063 -- EXECEPTION HANDLING TO GMS_AP_API TO SUPPORT CODE HOOK IN AP APPROVAL.
1064 -- Added exception handling routine.
1065 EXCEPTION
1066 WHEN AWARD_NOT_FOUND THEN
1067 fnd_message.set_name('GMS','GMS_INVALID_AWARD_FOUND');
1068 --
1069 -- Message : Incorrect award is associated with the invoice id : ??? and
1070 -- distribution line number : ??????. Please change award information
1071 -- on the distribution line.
1072
1073 fnd_message.set_token('INVNUM',l_invoice_num);
1074 fnd_message.set_token('DISTLNO', l_distribution_line_number(l_count));
1075 app_exception.raise_exception;
1076
1077 WHEN OTHERS THEN
1078 fnd_message.set_name('GMS','GMS_UNEXPECTED_ERROR');
1079 fnd_message.set_token('PROGRAM_NAME',NVL(p_calling_sequence,' ')||'->gms_ap_api.verify_create_adls');
1080 fnd_message.set_token('OERRNO',to_char(sqlcode));
1081 fnd_message.set_token('OERRM',sqlerrm);
1082 app_exception.raise_exception;
1083
1084 -- EXECEPTION HANDLING TO GMS_AP_API TO SUPPORT CODE HOOK IN AP APPROVAL.
1085 -- Bug 3077074 End here
1086
1087 END VERIFY_CREATE_ADLS ;
1088
1089
1090 -- Start of comments
1091 -- -----------------
1092 -- API Name : validate_transaction
1093 -- Type : public
1094 -- Pre Reqs : None
1095 -- BUG : 2755183
1096 -- Description : INVOICE ENTRY DOES NOT VALIDATE EXP ITEM DATE W/ AWARD COPIED FROM DIST SET.
1097 --
1098 -- Function : This function is called from AP_INVOICE_DISTRIBUTIONS_PKG.
1099 -- insert_from_dist_set to validate the award related
1100 -- information.
1101 -- Logic : Determine the award and call gms standard
1102 -- validation routine.
1103 -- Parameters :
1104 -- IN : x_project_id IN Number
1105 -- Project ID value.
1106 -- x_task_id IN Number
1107 -- Task Identifier.
1108 -- x_award_id IN number
1109 -- ADL identifier, AWARD_SET_ID reference value.
1110 -- x_expenditure_type IN varchar2
1111 -- Expenditure type
1112 -- x_expenditure_item_date in date
1113 -- Expenditure item date.
1114 -- x_calling_sequence in varchar2
1115 -- calling api identifier.
1116 -- x_msg_application in varchar2
1117 -- application identifier = 'GMS'
1118 -- x_msg_type out varchar2,
1119 -- identify the message type.
1120 -- X_msg_count out number
1121 -- count of message
1122 -- X_msg_data out varchar2
1126 -- End of comments
1123 -- message label
1124 -- Calling API : AP_INVOICE_DISTRIBUTIONS_PKG.insert_from_dist_set
1125 --
1127 -- ----------------
1128
1129 PROCEDURE validate_transaction( x_project_id IN NUMBER,
1130 x_task_id IN NUMBER,
1131 x_award_id IN NUMBER,
1132 x_expenditure_type IN varchar2,
1133 x_expenditure_item_date IN DATE,
1134 x_calling_sequence in VARCHAR2,
1135 x_msg_application in out nocopy VARCHAR2,
1136 x_msg_type out nocopy VARCHAR2,
1137 X_msg_count OUT nocopy NUMBER,
1138 X_msg_data OUT nocopy VARCHAR2 ) is
1139 cursor c1 is
1140 select award_id
1141 from gms_award_distributions
1142 where award_set_id = x_award_id
1143 and adl_line_num = 1 ;
1144
1145 l_award_id number ;
1146 l_outcome varchar2(2000) ;
1147
1148 begin
1149 -- ===========================================================================
1150 -- AP Lines uptake: Calling sequence of 'AWARD_ID' indicates that x_award_id
1151 -- holds the value of award_id and we don't need to fetch award_id from cursor
1152 -- ===========================================================================
1153 IF x_calling_sequence = 'AWARD_ID' THEN
1154 l_award_id := x_award_id;
1155 ELSE
1156 open c1 ;
1157 fetch c1 into l_award_id ;
1158 IF c1%notfound then
1159 raise no_data_found ;
1160 end if ;
1161 close c1 ;
1162 END IF;
1163
1164
1165 -- ===========================================================================
1166 -- inavlida parameter was passed to p_award_id argument. The correct value
1167 -- should have l_award_id. The previously x_award_id which holds award_set_id
1168 -- was passed.
1169 -- ===========================================================================
1170
1171 gms_transactions_pub.validate_transaction( p_project_id => x_project_id,
1172 p_task_id => x_task_id,
1173 p_award_id => l_award_id,
1174 p_expenditure_type => x_expenditure_type,
1175 P_expenditure_item_date=> x_expenditure_item_date,
1176 P_calling_module => 'TXNVALID',
1177 p_outcome => l_outcome ) ;
1178
1179
1180 IF l_outcome is not null then
1181 x_msg_type := 'E' ;
1182 X_msg_count := 1 ;
1183 X_msg_data := l_outcome ;
1184 x_msg_application := 'GMS' ;
1185 end if ;
1186
1187 EXCEPTION
1188 WHEN no_data_found then
1189 IF c1%isopen then
1190 close c1 ;
1191 end if ;
1192
1193 x_msg_type := 'E' ;
1194 X_msg_count := 1 ;
1195 X_msg_data := 'GMS_AWARD_REQUIRED' ;
1196 x_msg_application := 'GMS' ;
1197
1198 end validate_transaction ;
1199
1200
1201 /* AP Lines uptake: Overloaded API ( with GET_DISTRIBUTION_AWARD) with
1202 * prgma settings so that it can be called in the sqls.*/
1203 FUNCTION GET_DISTRIBUTION_AWARD
1204 (p_award_set_id IN NUMBER) return NUMBER IS
1205
1206 l_award_id NUMBER ;
1207 CURSOR C_ADL_REC is
1208 select award_id
1209 from gms_award_distributions ADL
1210 where award_set_id = p_award_set_id
1211 and adl_line_num = 1 ;
1212
1213 BEGIN
1214 IF p_award_set_id is NULL THEN
1215 return to_number(NULL);
1216 END IF ;
1217 OPEN C_ADL_REC ;
1218 fetch C_ADL_REC into l_award_id ;
1219 IF C_ADL_REC%NOTFOUND THEN
1220 raise no_data_found ;
1221 END IF ;
1222 CLOSE C_ADL_REC ;
1223 Return l_award_id ;
1224 EXCEPTION
1225 WHEN no_data_found then
1226 IF C_ADL_REC%ISOPEN THEN
1227 CLOSE C_ADL_REC ;
1228 END IF ;
1229 RAISE ;
1230 WHEN OTHERS THEN
1231 RAISE ;
1232 END GET_DISTRIBUTION_AWARD ;
1233
1234 /* Added for Bug 5194359 */
1235 FUNCTION vert_install RETURN BOOLEAN IS
1236 BEGIN
1237 RETURN gms_pa_api.vert_install;
1238 END vert_install;
1239
1240 END GMS_AP_API;