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