1 PACKAGE BODY GMS_AP_API2 AS
2 /* $Header: gmsapx2b.pls 120.1.12020000.2 2012/10/16 14:05:36 navemish ship $ */
3
4 -- Start of comments
5 -- -----------------
6 -- API Name : check_award_funding
7 -- Type : private
8 -- Pre Reqs : None
9 -- BUG : 3077074
10 -- Description : EXECEPTION HANDLING TO GMS_AP_API TO SUPPORT CODE HOOK IN AP APPROVAL.
11 --
12 -- Function : check award funding identifies the award funding the project.
13 -- Calling API : verify_create_adl
14 -- End of comments
15 -- ----------------
16
17 PROCEDURE check_award_funding ( p_project_id IN NUMBER,
18 p_award_id IN OUT NOCOPY NUMBER,
19 p_status out NOCOPY NUMBER ) is
20
21 l_award_id NUMBER ;
22 l_status NUMBER ;
23
24 -- =====================================================
25 -- Cursor : c_validate_award
26 -- Cursor verifies that award is funded by the
27 -- project.
28 -- =====================================================
29 cursor c_validate_award is
30 select ins.award_id
31 from gms_installments ins,
32 gms_summary_project_fundings pf
33 where ins.installment_id = pf.installment_id
34 and pf.project_id = p_project_id
35 and ins.award_id = p_award_id ;
36
37 -- =====================================================
38 -- Cursor : c_get_award
39 -- Cursor finds out if there is a award funding the
40 -- project charged to a transaction.
41 -- =====================================================
42 cursor c_get_award is
43 select ins.award_id
44 from gms_installments ins,
45 gms_summary_project_fundings pf
46 where ins.installment_id = pf.installment_id
47 and pf.project_id = p_project_id
48 and NOT EXISTS ( select 1 from gms_installments ins2,
49 gms_summary_project_fundings pf2
50 where ins2.installment_id = pf2.installment_id
51 and pf2.project_id = pf.project_id
52 and ins2.award_id <> ins.award_id ) ;
53 BEGIN
54 l_award_id := p_award_id ;
55 l_status := 0 ;
56
57 -- =================================
58 -- Validate award.
59 -- =================================
60 IF p_award_id is not NULL THEN
61 open c_validate_award ;
62 fetch c_validate_award into l_award_id ;
63 close c_validate_award ;
64 END IF ;
65
66 -- There is no valid award yet.
67 -- checking to see if there
68
69 IF l_award_id is NULL THEN
70 open c_get_award ;
71 fetch c_get_award into l_award_id ;
72 close c_get_award ;
73 END IF ;
74
75 IF l_award_id is NULL THEN
76 l_status:= -1 ;
77 ELSE
78 p_award_id := l_award_id ;
79 END IF ;
80
81 p_status := l_status ;
82
83 END check_award_funding ;
84 -- End of check_award_funding
85 -- ----------------------------
86
87 -- Start of comments
88 -- -----------------
89 -- API Name : verify_create_adl
90 -- Type : public
91 -- Pre Reqs : None
92 -- BUG : 2789359, 3046767
93 -- Description : RECURRING INVOICES USING AP DISTRIBUTION SETS FAILING
94 -- GL FUNDSCHECK F00
95 -- GMS: ENHANCE GMS LOGIC FOR AWARD DISTRIBUTION LINE VALIDATIONS.
96 -- Function : This is used to create award distribution lines
97 -- using the bulk processing. This provides a
98 -- interface with ap recurring invoice feature.
99 -- Logic : Identify the newly created invoice distribution
100 -- lines and create award distribution lines for
101 -- sponsored project.
102 -- Parameters :
103 -- IN : p_invoice_id IN NUMBER
104 -- The invoice id created and that may
105 -- have distributions associated with
106 -- an award.
107 -- : p_calling_sequence IN varchar2
108 -- calling sequence of the API for the debugging purpose.
109 -- Calling API : AP_RECURRING_INVOICES_PKG.ap_create_recurring_invoices
110 -- AP_APPROVAL_PKG.APPROVE
111 -- End of comments
112 -- ----------------
113
114 PROCEDURE VERIFY_CREATE_ADLS( p_invoice_id IN NUMBER ,
115 p_calling_sequence IN VARCHAR2 ) is
116
117 type gms_ap_type_number is table of number index by binary_integer;
118 type gms_ap_type_date is table of date index by binary_integer;
119
120 l_invoice_id gms_ap_type_number;
121 l_distribution_line_number gms_ap_type_number;
122 l_invoice_distribution_id gms_ap_type_number;
123 l_project_id gms_ap_type_number;
124 l_task_id gms_ap_type_number;
125 l_award_id gms_ap_type_number;
126 l_new_award_set_id gms_ap_type_number;
127 l_last_update_date gms_ap_type_date;
128 l_creation_date gms_ap_type_date;
129 l_last_updated_by gms_ap_type_number;
130 l_created_by gms_ap_type_number;
131 l_last_update_login gms_ap_type_number;
132 l_dummy_tab gms_ap_type_number;
133
134 l_award_set_id gms_ap_type_number ;
138 l_award_idX NUMBER ;
135 l_award_set_idX NUMBER ;
136 l_count NUMBER ;
137 l_project_idX NUMBER ;
139 l_status NUMBER ;
140 AWARD_NOT_FOUND EXCEPTION ;
141
142 l_invoice_num ap_invoices_all.invoice_num%TYPE ;
143
144 cursor c_ap is
145 SELECT A.invoice_id INVOICE_ID,
146 A.distribution_line_number distribution_line_number,
147 A.invoice_distribution_id invoice_distribution_id,
148 A.project_id PROJECT_ID,
149 A.task_id TASK_ID,
150 A.award_id award_set_id,
151 A.last_update_date LAST_UPDATE_DATE,
152 A.creation_date CREATION_DATE,
153 A.last_updated_by LAST_UPDATED_BY,
154 A.created_by CREATED_BY,
155 NVL(A.last_update_login,0) LAST_UPDATE_LOGIN,
156 inv.invoice_num invoice_num
157 from ap_invoice_distributions_all A,
158 pa_projects_all p,
159 gms_project_types gpt,
160 ap_invoices_all inv
161 where a.invoice_id = p_invoice_id
162 and a.project_id = p.project_id
163 and p.project_type = gpt.project_type
164 and inv.invoice_id = a.invoice_id
165 and gpt.sponsored_flag = 'Y'
166 UNION /* BUG 14216205 : Added the union for SAT */
167 SELECT A.invoice_id INVOICE_ID,
168 A.distribution_line_number distribution_line_number,
169 A.invoice_distribution_id invoice_distribution_id,
170 A.project_id PROJECT_ID,
171 A.task_id TASK_ID,
172 A.award_id award_set_id,
173 A.last_update_date LAST_UPDATE_DATE,
174 A.creation_date CREATION_DATE,
175 A.last_updated_by LAST_UPDATED_BY,
176 A.created_by CREATED_BY,
177 NVL(A.last_update_login,0) LAST_UPDATE_LOGIN,
178 inv.invoice_num invoice_num
179 FROM ap_self_assessed_tax_dist_all A,
180 PA_PROJECTS_ALL P,
181 gms_project_types gpt,
182 AP_INVOICES_ALL INV
183 WHERE a.invoice_id = p_invoice_id
184 AND a.project_id = p.project_id
185 AND A.line_type_lookup_code='NONREC_TAX'
186 AND p.project_type = gpt.project_type
187 AND INV.INVOICE_ID = a.INVOICE_ID
188 AND gpt.sponsored_flag = 'Y' ;
189
190 cursor c_adl is
191 select * from gms_award_distributions where award_set_id = l_award_set_idX
192 and adl_line_num = 1 ;
193 l_adl_rec c_adl%ROWTYPE ;
194
195 cursor c2 is
196 SELECT 1
197 from ap_invoice_distributions_all A,
198 pa_projects_all p,
199 gms_project_types gpt
200 where a.invoice_id = p_invoice_id
201 and a.project_id = p.project_id
202 and p.project_type = gpt.project_type
203 and gpt.sponsored_flag = 'Y'
204 and not exists ( select 1 from gms_award_distributions adl
205 where adl.invoice_id = p_invoice_id
206 and adl.distribution_line_number = A.distribution_line_number
207 and adl.document_type = 'AP'
208 and adl.award_set_id = NVL(a.award_id,0)
209 and adl.adl_line_num = 1
210 and adl.adl_status = 'A' )
211 union /* BUG 14216205 : Added the union for SAT */
212 select 1
213 FROM AP_SELF_ASSESSED_TAX_DIST_ALL b,
214 PA_PROJECTS_ALL P,
215 GMS_PROJECT_TYPES_ALL GPT
216 WHERE b.invoice_id = p_invoice_id
217 AND b.project_id = p.project_id
218 AND P.PROJECT_TYPE = GPT.PROJECT_TYPE
219 AND GPT.SPONSORED_FLAG = 'Y'
220 AND b.LINE_TYPE_LOOKUP_CODE='NONREC_TAX'
221 AND NOT EXISTS
222 (SELECT 1
223 FROM GMS_AWARD_DISTRIBUTIONS ADL
224 WHERE ADL.INVOICE_ID = p_invoice_id
225 AND adl.distribution_line_number = b.distribution_line_number
226 AND ADL.DOCUMENT_TYPE = 'AP'
227 AND adl.award_set_id = NVL(b.award_id,0)
228 AND ADL.ADL_LINE_NUM = 1
229 AND ADL.ADL_STATUS = 'A' );
230
231 /* BUG 14216205 : Added the following cursors for SAT : Starts */
232 CURSOR c3(p_invoice_id IN NUMBER)
233 IS
234 SELECT A.invoice_id INVOICE_ID,
235 A.distribution_line_number distribution_line_number,
236 A.invoice_distribution_id invoice_distribution_id,
237 A.project_id PROJECT_ID,
238 A.task_id TASK_ID,
239 ADL.award_id AWARD_ID,
240 A.award_id award_set_id,
241 A.last_update_date LAST_UPDATE_DATE,
242 A.creation_date CREATION_DATE,
243 A.last_updated_by LAST_UPDATED_BY,
244 A.created_by CREATED_BY,
245 NVL(A.last_update_login,0) LAST_UPDATE_LOGIN,
246 gms_awards_dist_pkg.get_award_set_id NEW_AWARD_SET_ID
247 FROM ap_invoice_distributions_all A,
248 GMS_AWARD_DISTRIBUTIONS ADL
249 WHERE a.invoice_id = p_invoice_id
250 AND adl.award_set_id = a.award_id
251 AND adl.adl_line_num = 1
252 AND a.AWARD_ID IS NOT NULL
253 UNION
254 SELECT A.invoice_id INVOICE_ID,
255 A.distribution_line_number distribution_line_number,
256 A.invoice_distribution_id invoice_distribution_id,
257 A.project_id PROJECT_ID,
258 a.TASK_ID TASK_ID,
259 ADL.award_id AWARD_ID,
260 A.award_id award_set_id,
261 A.last_update_date LAST_UPDATE_DATE,
262 A.creation_date CREATION_DATE,
263 A.last_updated_by LAST_UPDATED_BY,
264 a.CREATED_BY CREATED_BY,
265 NVL(a.LAST_UPDATE_LOGIN,0) LAST_UPDATE_LOGIN,
269 WHERE a.invoice_id = p_invoice_id
266 gms_awards_dist_pkg.get_award_set_id NEW_AWARD_SET_ID
267 FROM ap_self_assessed_tax_dist_all A,
268 GMS_AWARD_DISTRIBUTIONS ADL
270 AND ADL.AWARD_SET_ID = a.AWARD_ID
271 AND line_type_lookup_code='NONREC_TAX'
272 and ADL.ADL_LINE_NUM = 1
273 AND a.award_id IS NOT NULL;
274
275 cursor C4(p_invoice_id in number)
276 IS
277 select adl2.award_set_id
278 from gms_award_distributions adl2,
279 AP_INVOICE_DISTRIBUTIONS_ALL APD
280 where apd.invoice_id = p_invoice_id
281 and apd.award_id is not null
282 and adl2.award_set_id = apd.award_id
283 and adl2.invoice_id = apd.invoice_id
284 and adl2.document_type = 'AP'
285 and adl2.distribution_line_number = apd.distribution_line_number
286 and ADL2.INVOICE_DISTRIBUTION_ID = APD.INVOICE_DISTRIBUTION_ID
287 and ADL2.ADL_STATUS = 'I'
288 union
289 select adl2.award_set_id
290 from gms_award_distributions adl2,
291 AP_SELF_ASSESSED_TAX_DIST_ALL APSAT
292 where apsat.invoice_id = p_invoice_id
293 and APSAT.AWARD_ID is not null
294 and line_type_lookup_code='NONREC_TAX'
295 and adl2.award_set_id = apsat.award_id
296 and adl2.invoice_id = apsat.invoice_id
297 and adl2.document_type = 'AP'
298 and adl2.distribution_line_number = apsat.distribution_line_number
299 and ADL2.INVOICE_DISTRIBUTION_ID = APSAT.INVOICE_DISTRIBUTION_ID
300 and ADL2.ADL_STATUS = 'I';
301
302 cursor C5(p_invoice_id in number)
303 IS
304 select adl2.award_set_id
305 from gms_award_distributions adl2,
306 AP_INVOICE_DISTRIBUTIONS_ALL APD
307 where apd.invoice_id = p_invoice_id
308 and APD.AWARD_ID is not null
309 and adl2.award_set_id <> apd.award_id
310 and adl2.invoice_id = apd.invoice_id
311 and adl2.document_type = 'AP'
312 and adl2.distribution_line_number = apd.distribution_line_number
313 and ADL2.INVOICE_DISTRIBUTION_ID = APD.INVOICE_DISTRIBUTION_ID
314 and adl2.adl_status = 'A'
315 UNION
316 select ADL2.AWARD_SET_ID
317 from GMS_AWARD_DISTRIBUTIONS ADL2,
318 AP_SELF_ASSESSED_TAX_DIST_ALL APSAT
319 where apsat.invoice_id = p_invoice_id
320 and APSAT.AWARD_ID is not null
321 and adl2.award_set_id <> apsat.award_id
322 and adl2.invoice_id = apsat.invoice_id
323 and adl2.document_type = 'AP'
324 and adl2.distribution_line_number = apsat.distribution_line_number
325 and ADL2.INVOICE_DISTRIBUTION_ID = APSAT.INVOICE_DISTRIBUTION_ID
326 and adl2.adl_status = 'A' ;
327
328 cursor C6(p_invoice_id in number)
329 IS
330 select a.invoice_distribution_id
331 from ap_invoice_distributions_all A,
332 pa_projects_all p,
333 gms_project_types gpt
334 where a.invoice_id = p_invoice_id
335 and a.project_id = p.project_id
336 and a.award_id is not NULL
337 and P.PROJECT_TYPE = GPT.PROJECT_TYPE
338 and gpt.sponsored_flag = 'N'
339 union
340 select b.INVOICE_DISTRIBUTION_ID
341 from AP_SELF_ASSESSED_TAX_DIST_ALL B,
342 pa_projects_all p,
343 gms_project_types gpt
344 where b.invoice_id = p_invoice_id
345 and b.project_id = p.project_id
346 and b.award_id is not NULL
347 and P.PROJECT_TYPE = GPT.PROJECT_TYPE
348 and gpt.sponsored_flag = 'N';
349 /* BUG 14216205 : Added the following cursors for SAT : Ends */
350
351
352 BEGIN
353
354 -- Start of comment
355 -- Verify that grants is enabled.
356 -- End of comments.
357 --
358 IF NOT gms_install.enabled THEN
359 return ;
360 END IF ;
361
362 -- Load the collection with the AP inv dist data first.
363 -- The AP inv dist should have the invoice_id in parameters
364 -- value.
365 -- The AP inv dist should have award_id column populated.
366
367 IF NVL(SUBSTR(p_calling_sequence, 1,15),'X') <> 'AP_APPROVAL_PKG' THEN
368 /* BUG 14216205 : Commented the code : Starts */
369 /*SELECT A.invoice_id INVOICE_ID,
370 A.distribution_line_number distribution_line_number,
371 A.invoice_distribution_id invoice_distribution_id,
372 A.project_id PROJECT_ID,
373 A.task_id TASK_ID,
374 ADL.award_id AWARD_ID,
375 A.award_id award_set_id,
376 A.last_update_date LAST_UPDATE_DATE,
377 A.creation_date CREATION_DATE,
378 A.last_updated_by LAST_UPDATED_BY,
379 A.created_by CREATED_BY,
380 NVL(A.last_update_login,0) LAST_UPDATE_LOGIN,
381 gms_awards_dist_pkg.get_award_set_id NEW_AWARD_SET_ID
382 BULK COLLECT INTO l_invoice_id,
383 l_distribution_line_number,
384 l_invoice_distribution_id,
385 l_project_id ,
386 l_task_id ,
387 l_award_id,
388 l_award_set_id,
389 l_last_update_date ,
390 l_creation_date ,
391 l_last_updated_by ,
392 l_created_by ,
393 l_last_update_login ,
394 l_new_award_set_id
395 from ap_invoice_distributions_all A,
396 gms_award_distributions adl
397 where a.invoice_id = p_invoice_id
398 and adl.award_set_id = a.award_id
399 and adl.adl_line_num = 1
403 /* BUG 14216205 : Added the following Code for SAT : Starts */
400 and a.award_id IS NOT NULL;*/
401 /* BUG 14216205 : Commented the code : Ends */
402
404 OPEN C3(P_INVOICE_ID);
405 FETCH C3 bulk collect
406 INTO l_invoice_id,
407 l_distribution_line_number,
408 l_invoice_distribution_id,
409 l_project_id ,
410 l_task_id ,
411 l_award_id,
412 l_award_set_id,
413 l_last_update_date ,
414 l_creation_date ,
415 l_last_updated_by ,
416 l_created_by ,
417 L_LAST_UPDATE_LOGIN ,
418 L_NEW_AWARD_SET_ID;
419 close c3;
420 /* BUG 14216205 : Added the following Code for SAT : Ends */
421 END IF ;
422
423 -- 3046767 GMS: ENHANCE GMS LOGIC FOR AWARD DISTRIBUTION LINE VALIDATIONS. STARTS
424 IF NVL(SUBSTR(p_calling_sequence, 1,15),'X') = 'AP_APPROVAL_PKG' THEN
425 l_count := 0 ;
426
427 -- 2308005 ( CLEARING INVOICE DIST. LINE AFTER CHANGING AWARD MAKES ADL STATUS 'I' )
428
429 -- ==============================================================================================
430 -- BUG : 4953772
431 -- Description : R12.PJ:XB2:DEV:GMS: APPSPERF: PACKAGE:GMSAPX2B.PLS
432 -- Resolution : Sql statement to update award id NULL on ap distribution was changed to bulk
433 -- Processing. This has resolved the Share Memory Size 1,282,674
434 -- SQL ID : 14724997 Share Memory Size 1,282,722
435 -- SQL ID : 14724976 Share Memory Size 452,386
436 -- SQL ID : 14724956 Share Memory Size 444,106
437 -- ==============================================================================================
438
439 l_dummy_tab.delete ;
440 /* BUG 14216205 : Commented the below code : Starts */
441 /*select adl2.award_set_id
442 bulk collect into l_dummy_tab
443 from gms_award_distributions adl2,
444 ap_invoice_distributions_all apd
445 where apd.invoice_id = p_invoice_id
446 and apd.award_id is not null
447 and adl2.award_set_id = apd.award_id
448 and adl2.invoice_id = apd.invoice_id
449 and adl2.document_type = 'AP'
450 and adl2.distribution_line_number = apd.distribution_line_number
451 and adl2.invoice_distribution_id = apd.invoice_distribution_id
452 and adl2.adl_status = 'I' ;*/
453 /* BUG 14216205 : Commented the below code : Ends */
454
455 /* BUG 14216205 : Added the following Code for SAT : Starts */
456 open C4(P_INVOICE_ID);
457 FETCH C4 bulk collect INTO L_DUMMY_TAB;
458 close C4;
459 /* BUG 14216205 : Added the following Code for SAT : Ends */
460
461 IF l_dummy_tab.count > 0 THEN
462
463 FORALL i in l_dummy_tab.FIRST..l_dummy_tab.LAST
464 UPDATE gms_award_distributions
465 SET adl_status = 'A'
466 where award_set_id = l_dummy_tab(i) ;
467
468 END IF ;
469
470 l_dummy_tab.delete ;
471
472 -- Inactivate ADLS that belongs to the AP distribution line but
473 -- not tied up with award_id in ap_distribution line.
474 -- Inactivate dangling active adls.
475 -- ----
476
477 -- ==============================================================================================
478 -- BUG : 4953772
479 -- Description : R12.PJ:XB2:DEV:GMS: APPSPERF: PACKAGE:GMSAPX2B.PLS
480 -- Resolution : Sql statement to update award id NULL on ap distribution was changed to bulk
481 -- Processing. This has resolved the Share Memory Size 1,282,674
482 -- SQL ID : 14724997 Share Memory Size 1,282,722
483 -- SQL ID : 14724976 Share Memory Size 452,386
484 -- SQL ID : 14724956 Share Memory Size 444,106
485 -- ==============================================================================================
486 /* BUG 14216205 : Commented the below code : Starts */
487 /*select adl2.award_set_id
488 bulk collect into l_dummy_tab
489 from gms_award_distributions adl2,
490 ap_invoice_distributions_all apd
491 where apd.invoice_id = p_invoice_id
492 and apd.award_id is not null
493 and adl2.award_set_id <> apd.award_id
494 and adl2.invoice_id = apd.invoice_id
495 and adl2.document_type = 'AP'
496 and adl2.distribution_line_number = apd.distribution_line_number
497 and adl2.invoice_distribution_id = apd.invoice_distribution_id
498 and adl2.adl_status = 'A' ;*/
499 /* BUG 14216205 : Commented the below code : Ends */
500
501 /* BUG 14216205 : Added the following Code for SAT : Starts */
502 open C5(P_INVOICE_ID);
503 FETCH C5 bulk collect into L_DUMMY_TAB;
504 close c5;
505 /* BUG 14216205 : Added the following Code for SAT : Ends */
506
507 IF l_dummy_tab.count > 0 THEN
508
509 FORALL i in l_dummy_tab.FIRST..l_dummy_tab.LAST
510 UPDATE gms_award_distributions
511 SET adl_status = 'I'
512 where award_set_id = l_dummy_tab(i) ;
513
514 END IF ;
515
516 l_dummy_tab.delete ;
517
518 -- ==================================================
519 -- Update award_id to NULL for non sponsored
520 -- projects.
521 -- =================================================
522 -- Bug : 4953772
523 -- R12.PJ:XB2:DEV:GMS: APPSPERF: PACKAGE:GMSAPX2B.PLS
524 --
525 -- ==============================================================================================
526 -- BUG : 4953772
527 -- Description : R12.PJ:XB2:DEV:GMS: APPSPERF: PACKAGE:GMSAPX2B.PLS
531 -- SQL ID : 14724976 Share Memory Size 452,386
528 -- Resolution : Sql statement to update award id NULL on ap distribution was changed to bulk
529 -- Processing. This has resolved the Share Memory Size 1,282,674
530 -- SQL ID : 14724997 Share Memory Size 1,282,722
532 -- SQL ID : 14724956 Share Memory Size 444,106
533 -- ==============================================================================================
534 /* 25-jan-2006
535 ** Update statement was changed to bulk statement to resolve the share memory performance issue.
536 */
537 /* BUG 14216205 : Commented the below code : Starts */
538 /*select a.invoice_distribution_id
539 bulk collect into l_dummy_tab
540 from ap_invoice_distributions_all A,
541 pa_projects_all p,
542 gms_project_types gpt
543 where a.invoice_id = p_invoice_id
544 and a.project_id = p.project_id
545 and a.award_id is not NULL
546 and p.project_type = gpt.project_type
547 and gpt.sponsored_flag = 'N' ;*/
548 /* BUG 14216205 : Commented the below code : Ends */
549
550 /* BUG 14216205 : Added the following Code for SAT : Starts */
551 open C6(P_INVOICE_ID);
552 FETCH C6 bulk collect into l_dummy_tab;
553 close c6;
554 /* BUG 14216205 : Added the following Code for SAT : Ends */
555 IF l_dummy_tab.count > 0 THEN
556
557 FORALL i in l_dummy_tab.FIRST..l_dummy_tab.LAST
558 UPDATE ap_invoice_distributions_all apd
559 SET award_id = NULL
560 where apd.invoice_id = p_invoice_id
561 and apd.invoice_distribution_id = l_dummy_tab(i) ;
562 /* BUG 14216205 : Added the following Code for SAT : Starts */
563 FORALL j in l_dummy_tab.FIRST..l_dummy_tab.LAST
564 UPDATE AP_SELF_ASSESSED_TAX_DIST_ALL apsat
565 set AWARD_ID = null
566 where APSAT.INVOICE_ID = P_INVOICE_ID
567 and apsat.invoice_distribution_id = l_dummy_tab(j) ;
568 /* BUG 14216205 : Added the following Code for SAT : Ends */
569 END IF ;
570
571 l_dummy_tab.delete ;
572
573 FOR ap_rec in c_ap LOOP
574
575 l_award_set_idX := NVL(ap_rec.award_set_id,0) ;
576 l_invoice_num := ap_rec.invoice_num ;
577
578 l_adl_rec := NULL ;
579
580 open c_adl ;
581 fetch c_adl into l_adl_rec ;
582 close c_adl ;
583
584 IF NOT (( NVL(l_adl_rec.adl_status,'I') = 'A' ) and
585 ( NVL(l_adl_rec.document_type,'X') = 'AP' ) and
586 ( NVL(l_adl_rec.invoice_id,0) = NVL( ap_rec.invoice_id,0) ) AND
587 ( NVL(l_adl_rec.distribution_line_number,0) = NVL(ap_rec.distribution_line_number,0) ) AND
588 ( NVL(l_adl_rec.invoice_distribution_id,0) = NVL( ap_rec.invoice_distribution_id,0) )) THEN
589
590 l_count := l_count + 1 ;
591 l_invoice_id(l_count) := ap_rec.invoice_id ;
592 l_distribution_line_number(l_count) := ap_rec.distribution_line_number;
593 l_invoice_distribution_id(l_count) := ap_rec.invoice_distribution_id;
594 l_project_id(l_count) := ap_rec.project_id;
595 l_task_id(l_count) := ap_rec.task_id;
596 l_project_idX := ap_rec.project_id;
597 l_award_idX := l_adl_rec.award_id ;
598
599 check_award_funding( l_project_idX, l_award_idX, l_status ) ;
600
601 IF l_status = -1 THEN
602 raise AWARD_NOT_FOUND ;
603 ELSE
604 l_award_id(l_count) := l_award_idX ;
605 END IF ;
606
607 l_last_update_date(l_count) := ap_rec.last_update_date;
608 l_creation_date(l_count) := ap_rec.creation_date;
609 l_last_updated_by(l_count) := ap_rec.last_updated_by;
610 l_created_by(l_count) := NVL(ap_rec.created_by,0);
611 l_last_update_login(l_count) := ap_rec.last_update_login;
612 l_new_award_set_id(l_count) := gms_awards_dist_pkg.get_award_set_id ;
613
614 END IF ;
615
616 END LOOP ;
617
618 END IF ;
619 -- 3046767 GMS: ENHANCE GMS LOGIC FOR AWARD DISTRIBUTION LINE VALIDATIONS. END
620
621 -- Start of comments
622 -- Check if need to proceed.
623 -- End of comment.
624
625 IF l_invoice_id.count = 0 then
626 -- bug 3772472 PJ.M:B5: QA:P11:OTH: DELETION OF ADLS WHEN REQ,PO OR AP LINES
627 -- ARE DELETED.
628 -- delete orphan adls for a given invoice id.
629 -- scenarions : POETA ap distribution line was changed to gl related.
630 -- Distribution line is deleted but all other distributions
631 -- has correct adls.
632 -- ADLS are in sych so we can delete orphan adls now.
633 delete from gms_award_distributions
634 where invoice_id = p_invoice_id
635 and document_type = 'AP'
636 and award_set_id not in ( select award_id from ap_invoice_distributions_all
637 where invoice_id = p_invoice_id
638 and award_id is not NULL
639 union /* BUG 14216205 : Added the union for SAT */
640 select award_id from AP_SELF_ASSESSED_TAX_DIST_ALL
641 where INVOICE_ID = P_INVOICE_ID
642 and AWARD_ID is not null) ;
643
644 return ;
645 end if ;
646
647 -- Start of comment.
648 -- Loop through all the collection and insert into the ADL table.
649 -- Update the ap inv dist record with the newly created ADLs award set id.
650 -- End of comment
654 INSERT into gms_award_distributions ( award_set_id ,
651
652
653 FORALL i in l_invoice_distribution_id.FIRST..l_invoice_distribution_id.LAST
655 adl_line_num,
656 document_type,
657 distribution_value,
658 project_id ,
659 task_id ,
660 award_id ,
661 request_id ,
662 adl_status ,
663 fc_status ,
664 line_type ,
665 capitalized_flag ,
666 capitalizable_flag ,
667 revenue_distributed_flag ,
668 billed_flag ,
669 bill_hold_flag ,
670 invoice_distribution_id ,
671 invoice_id ,
672 distribution_line_number ,
673 burdenable_raw_cost ,
674 cost_distributed_flag ,
675 last_update_date ,
676 last_updated_by ,
677 created_by ,
678 creation_date ,
679 last_update_login ,
680 billable_flag )
681 VALUES ( l_new_award_set_id(i) ,
682 1, --adl_line_num,
683 'AP' , --document_type,
684 100,
685 l_project_id(i) ,
686 l_task_id(i) ,
687 l_award_id(i) ,
688 l_distribution_line_number(i) ,
689 'A', --adl_status ,
690 'N', --fc_status ,
691 'R', --line_type ,
692 'N' ,
693 'N' ,
694 'N' ,
695 'N' ,
696 'N' ,
697 l_invoice_distribution_id(i), --invoice_distribution_id ,
698 l_invoice_id(i), --invoice_id ,
699 l_distribution_line_number(i), --distribution_line_number ,
700 NULL, --burdenable_raw_cost ,
701 'N' ,
702 l_last_update_date(i) ,
703 l_last_updated_by(i) ,
704 l_created_by(i) ,
705 l_creation_date(i) ,
706 l_last_update_login(i) ,
707 'N') ;
708
709 -- Start of comment.
710 -- Update AP distribution with the award set id.
711 -- End of comment.
712
713 FORALL k in l_invoice_distribution_id.FIRST..l_invoice_distribution_id.LAST
714 update ap_invoice_distributions_all
715 set award_id = l_new_award_set_id(k)
716 where invoice_id = l_invoice_id(k)
717 and distribution_line_number = l_distribution_line_number(k)
718 and invoice_distribution_id = l_invoice_distribution_id(k) ;
719 /* BUG 14216205 : Added the following Code for SAT : Starts */
720 FORALL J in l_invoice_distribution_id.FIRST..l_invoice_distribution_id.LAST
721 update AP_SELF_ASSESSED_TAX_DIST_ALL
722 set AWARD_ID = L_NEW_AWARD_SET_ID(J)
723 where INVOICE_ID = L_INVOICE_ID(J)
724 and DISTRIBUTION_LINE_NUMBER = L_DISTRIBUTION_LINE_NUMBER(J)
725 and INVOICE_DISTRIBUTION_ID = L_INVOICE_DISTRIBUTION_ID(J) ;
726 /* BUG 14216205 : Added the following Code for SAT : Ends */
727 -- bug 3772472 PJ.M:B5: QA:P11:OTH: DELETION OF ADLS WHEN AP LINES
728 -- ARE DELETED.
729 -- delete orphan adls for a given invoice id.
730 -- ADLS are in sych so we can delete orphan adls now.
731 --
732 delete from gms_award_distributions
733 where invoice_id = p_invoice_id
734 and document_type = 'AP'
735 and award_set_id not in ( select award_id from ap_invoice_distributions_all
736 where invoice_id = p_invoice_id
737 and award_id is not NULL
738 union /* BUG 14216205 : Added the union for SAT */
739 select award_id from ap_self_assessed_tax_dist_all
740 where INVOICE_ID = P_INVOICE_ID
741 and AWARD_ID is not null) ;
742
743 -- Bug 3077074
744 -- EXECEPTION HANDLING TO GMS_AP_API TO SUPPORT CODE HOOK IN AP APPROVAL.
745 -- Added exception handling routine.
746 EXCEPTION
747 WHEN AWARD_NOT_FOUND THEN
748 fnd_message.set_name('GMS','GMS_INVALID_AWARD_FOUND');
749 --
750 -- Message : Incorrect award is associated with the invoice id : ??? and
751 -- distribution line number : ??????. Please change award information
752 -- on the distribution line.
753
754 fnd_message.set_token('INVNUM',l_invoice_num);
755 fnd_message.set_token('DISTLNO', l_distribution_line_number(l_count));
756 app_exception.raise_exception;
757
758 WHEN OTHERS THEN
759 fnd_message.set_name('GMS','GMS_UNEXPECTED_ERROR');
760 fnd_message.set_token('PROGRAM_NAME',NVL(p_calling_sequence,' ')||'->gms_ap_api.verify_create_adls');
761 fnd_message.set_token('OERRNO',to_char(sqlcode));
762 fnd_message.set_token('OERRM',sqlerrm);
763 app_exception.raise_exception;
764
765 -- EXECEPTION HANDLING TO GMS_AP_API TO SUPPORT CODE HOOK IN AP APPROVAL.
766 -- Bug 3077074 End here
767
768 END VERIFY_CREATE_ADLS ;
769
770
771 -- Start of comments
772 -- -----------------
773 -- API Name : validate_transaction
774 -- Type : public
775 -- Pre Reqs : None
776 -- BUG : 2755183
777 -- Description : INVOICE ENTRY DOES NOT VALIDATE EXP ITEM DATE W/ AWARD COPIED FROM DIST SET.
778 --
779 -- Function : This function is called from AP_INVOICE_DISTRIBUTIONS_PKG.
780 -- insert_from_dist_set to validate the award related
781 -- information.
782 -- Logic : Determine the award and call gms standard
783 -- validation routine.
784 -- Parameters :
785 -- IN : x_project_id IN Number
786 -- Project ID value.
787 -- x_task_id IN Number
788 -- Task Identifier.
789 -- x_award_id IN number
790 -- ADL identifier, AWARD_SET_ID reference value.
791 -- x_expenditure_type IN varchar2
792 -- Expenditure type
793 -- x_expenditure_item_date in date
794 -- Expenditure item date.
795 -- x_calling_sequence in varchar2
796 -- calling api identifier.
797 -- x_msg_application in varchar2
798 -- application identifier = 'GMS'
799 -- x_msg_type out varchar2,
800 -- identify the message type.
801 -- X_msg_count out number
802 -- count of message
803 -- X_msg_data out varchar2
804 -- message label
805 -- Calling API : AP_INVOICE_DISTRIBUTIONS_PKG.insert_from_dist_set
806 --
807 -- End of comments
808 -- ----------------
809
810 PROCEDURE validate_transaction( x_project_id IN NUMBER,
811 x_task_id IN NUMBER,
812 x_award_id IN NUMBER,
813 x_expenditure_type IN varchar2,
814 x_expenditure_item_date IN DATE,
815 x_calling_sequence in VARCHAR2,
816 x_msg_application in out nocopy VARCHAR2,
817 x_msg_type out nocopy VARCHAR2,
818 X_msg_count OUT nocopy NUMBER,
819 X_msg_data OUT nocopy VARCHAR2 ) is
820 cursor c1 is
821 select award_id
822 from gms_award_distributions
823 where award_set_id = x_award_id
824 and adl_line_num = 1 ;
825
826 l_award_id number ;
827 l_outcome varchar2(2000) ;
828
829 begin
830 open c1 ;
831 fetch c1 into l_award_id ;
832 IF c1%notfound then
833 raise no_data_found ;
834 end if ;
835 close c1 ;
836
837
838 -- ===========================================================================
839 -- inavlida parameter was passed to p_award_id argument. The correct value
840 -- should have l_award_id. The previously x_award_id which holds award_set_id
841 -- was passed.
842 -- ===========================================================================
843
844 gms_transactions_pub.validate_transaction( p_project_id => x_project_id,
845 p_task_id => x_task_id,
846 p_award_id => l_award_id,
847 p_expenditure_type => x_expenditure_type,
848 P_expenditure_item_date=> x_expenditure_item_date,
849 P_calling_module => 'TXNVALID',
850 p_outcome => l_outcome ) ;
851
852
853 IF l_outcome is not null then
854 x_msg_type := 'E' ;
855 X_msg_count := 1 ;
856 X_msg_data := l_outcome ;
857 x_msg_application := 'GMS' ;
858 end if ;
859
860 EXCEPTION
861 WHEN no_data_found then
862 IF c1%isopen then
863 close c1 ;
864 end if ;
865
866 x_msg_type := 'E' ;
867 X_msg_count := 1 ;
868 X_msg_data := 'GMS_AWARD_REQUIRED' ;
869 x_msg_application := 'GMS' ;
870
871 end validate_transaction ;
872
873 END GMS_AP_API2;
[Home] [Help]