[Home] [Help]
PACKAGE BODY: APPS.PA_PWP_INVOICE_REL
Source
1 PACKAGE BODY PA_PWP_INVOICE_REL AS
2 -- $Header: PAPWPRIB.pls 120.0.12010000.13 2009/01/28 12:53:57 jjgeorge noship $
3
4 -- This procedure is called by concurrent program PRC: Release Pay When Paid Holds
5
6 PROCEDURE Release_Invoice ( errbuf OUT NOCOPY VARCHAR2
7 ,retcode OUT NOCOPY VARCHAR2
8 ,p_mode IN VARCHAR2
9 ,p_project_type IN VARCHAR2
10 ,p_proj_num IN VARCHAR2
11 ,P_from_proj_num IN VARCHAR2
12 ,p_to_proj_num IN VARCHAR2
13 ,p_customer_name IN VARCHAR2
14 ,p_customer_number IN number
15 ,p_rec_date_from IN VARCHAR2
16 ,p_rec_date_to IN VARCHAR2
17 ,p_sort IN VARCHAR2
18 )
19 IS
20 req_id NUMBER;
21 rec_date_from DATE;
22 rec_date_to DATE;
23 start_project_num varchar2(30);
24 end_project_num varchar2(30);
25 l_project_id number;
26 l_draft_invoice_num number;
27 l_hold_exists number := 0;
28 l_override_flag varchar2(1) := 'N';
29 l_results_flag varchar2(1) := 'N';
30 l_billed varchar2(1) := 'N';
31 l_user_id number(15);
32 l_date date ;
33 p_return_status VARCHAR2(5) ;
34 l_inv_tab InvoiceId;
35 l_return_status VARCHAR2(1);
36 l_msg_count NUMBER;
37 l_msg_data VARCHAR2(100);
38 p_error_message_code VARCHAR2(2000);
39 xml_layout BOOLEAN;
40
41 TYPE IdsTab IS TABLE OF PA_EXPENDITURE_ITEMS.DOCUMENT_HEADER_ID%TYPE;
42 l_unbill_invids IdsTab;
43
44
45
46
47 -- Select all Supplier Invoices that are
48 --automatically linked / Manualy linked and
49 -- falls in criteria specified by concurrent pogram parameters .
50
51 CURSOR sel_inv_autolink IS
52 SELECT pdi.project_id
53 ,pdi.draft_invoice_num
54 ,EI.document_header_id AP_INVOICE_ID
55 ,'AUTOLINK' link_type
56 FROM pa_projects prj
57 , ra_customer_trx rac
58 , pa_draft_invoices pdi
59 , pa_draft_invoice_items pdii
60 , pa_cust_rev_dist_lines crdl
61 , pa_expenditure_items ei
62 WHERE prj.project_type = NVL(p_project_type ,prj.project_type)
63 AND prj.AUTO_RELEASE_PWP_INV = 'Y'
64 AND prj.segment1 BETWEEN start_project_num AND end_project_num
65 AND prj.project_id = pdi.project_id
66 AND pdi.customer_id = NVL(p_customer_number,pdi.customer_id )
67 AND pdi.project_id = pdii.project_id
68 AND pdi.draft_invoice_num = pdii.draft_invoice_num
69 AND rac.interface_header_attribute1 = prj.segment1
70 AND rac.interface_header_attribute2 = TO_CHAR(pdi.draft_invoice_num)
71 AND rac.interface_header_context = ( SELECT NAME
72 FROM
73 RA_BATCH_SOURCES RBS
74 ,PA_IMPLEMENTATIONS PI
75 WHERE
76 PI.INVOICE_BATCH_SOURCE_ID = BATCH_SOURCE_ID
77 ) --Bug 8204634
78 AND PDII.project_id = crdl.project_id
79 AND pdii.draft_invoice_num = crdl.draft_invoice_num
80 AND pdii.line_num = crdl.draft_invoice_item_line_num
81 AND crdl.expenditure_item_id = ei.expenditure_item_id
82 AND ei.document_header_id IS NOT NULL
83 AND ei.system_linkage_function = 'VI'
84 AND ei.transaction_source ='AP INVOICE' --AND prj.project_id = 1027;
85 AND EXISTS -- atleast one reciept applied between rec start and end date params
86 (SELECT 1
87 FROM AR_RECEIVABLE_APPLICATIONS_ALL ARA
88 WHERE ARA.STATUS = 'APP'
89 AND ara.APPLICATION_TYPE = 'CASH'
90 AND ARA.applied_customer_trx_id = RAC.customer_trx_id
91 and trunc(ARA.APPLY_DATE) between trunc(nvl(rec_date_from,ARA.APPLY_DATE-1)) and trunc(nvl(rec_date_to,ARA.APPLY_DATE + 1))
92 )
93 and EXISTS -- only those invoice where a Pay When Paid hold exists .
94 ( SELECT 1
95 FROM AP_HOLDS_ALL
96 WHERE invoice_id = ei.document_header_id
97 AND hold_lookup_code = 'Pay When Paid'
98 AND release_reason is null )
99 UNION
100 -- To pickup manual links
101 SELECT pwp.project_id
102 , pwp.draft_invoice_num
103 , pwp.AP_INVOICE_ID
104 ,'MANUAL' link_type
105 FROM pa_projects prj
106 , ra_customer_trx rac
107 , pa_draft_invoices pdi
108 , pa_pwp_linked_invoices pwp
109 WHERE prj.project_type = NVL(p_project_type ,prj.project_type)
110 AND prj.segment1 BETWEEN start_project_num AND end_project_num
111 AND prj.project_id = pdi.project_id
112 AND pdi.customer_id = NVL(p_customer_number,pdi.customer_id )
113 AND pdi.draft_invoice_num = PWP.draft_invoice_num
114 AND pdi.project_id = PWP.project_id
115 AND rac.interface_header_attribute1 = prj.segment1
116 AND rac.interface_header_attribute2 = TO_CHAR(pdi.draft_invoice_num)
117 AND rac.interface_header_context = ( SELECT NAME
118 FROM
119 RA_BATCH_SOURCES RBS
120 ,PA_IMPLEMENTATIONS PI
121 WHERE
122 PI.INVOICE_BATCH_SOURCE_ID = BATCH_SOURCE_ID
123 ) --Bug 8204634
124 AND EXISTS -- atleast one reciept applied between rec start and end date params
125 (SELECT 1
126 FROM AR_RECEIVABLE_APPLICATIONS_ALL ARA
127 WHERE ARA.STATUS = 'APP'
128 AND ara.APPLICATION_TYPE = 'CASH'
129 AND ARA.applied_customer_trx_id = RAC.customer_trx_id
130 and trunc(ARA.APPLY_DATE) between trunc(nvl(rec_date_from,ARA.APPLY_DATE-1)) and trunc(nvl(rec_date_to,ARA.APPLY_DATE + 1))
131
132 )
133 and EXISTS -- only those invoice where a Pay When Paid hold exists .
134 ( SELECT 1
135 FROM AP_HOLDS_ALL
136 WHERE invoice_id = pwp.AP_INVOICE_ID
137 AND hold_lookup_code = 'Pay When Paid'
138 AND release_reason is null)
139 ;
140
141
142 -- select all Draft Invoice Lines that are
143 -- not linked to any expenditure_item
144 CURSOR sel_inv_unlink IS
145 SELECT pdi.project_id
146 ,pdi.draft_invoice_num
147 ,'UNLINKED' link_type
148 , NVL(
149 CASE
150 WHEN 1 =
151 (SELECT 1
152 FROM dual
153 WHERE EXISTS
154 (SELECT 1
155 FROM ar_payment_schedules_all arp
156 WHERE arp.status = 'OP'
157 AND rac.customer_trx_id = arp.customer_trx_id
158 )
159 )
160 THEN 'N'
161 ELSE 'Y'
162 END, 'Y') payment_status
163 FROM pa_projects prj
164 , ra_customer_trx rac
165 , pa_draft_invoices pdi
166 , pa_draft_invoice_items pdii
167 , pa_events pae
168 , pa_event_types pet
169 WHERE prj.project_type = NVL(p_project_type ,prj.project_type)
170 AND prj.AUTO_RELEASE_PWP_INV = 'N'
171 AND prj.segment1 BETWEEN start_project_num AND end_project_num
172 AND prj.project_id = pdi.project_id
173 AND pdi.customer_id = NVL(p_customer_number,pdi.customer_id )
174 AND pdi.project_id = pdii.project_id
175 AND pdi.project_id = pdii.project_id
176 AND pdi.draft_invoice_num = pdii.draft_invoice_num
177 AND pdii.project_id = pae.project_id
178 AND nvl(pdii.task_id,-999) = nvl(pae.task_id,-999)
179 AND pdii.event_num = pae.event_num
180 AND pae.event_type = pet.event_type
181 AND pet.event_type_classification IN ('AUTOMATIC','MANUAL')
182 AND rac.interface_header_attribute1 = prj.segment1
183 AND rac.interface_header_attribute2 = TO_CHAR(pdi.draft_invoice_num)
184 AND rac.interface_header_context = ( SELECT NAME
185 FROM
186 RA_BATCH_SOURCES RBS
187 ,PA_IMPLEMENTATIONS PI
188 WHERE
189 PI.INVOICE_BATCH_SOURCE_ID = BATCH_SOURCE_ID
190 ) --Bug 8204634
191 AND EXISTS -- atleast one reciept applied between rec start and end date params
192 (SELECT 1
193 FROM AR_RECEIVABLE_APPLICATIONS_ALL ARA
194 WHERE ARA.STATUS = 'APP'
195 AND ara.APPLICATION_TYPE = 'CASH'
196 AND ARA.applied_customer_trx_id = RAC.customer_trx_id
197 and trunc(ARA.APPLY_DATE) between trunc(nvl(rec_date_from,ARA.APPLY_DATE-1)) and trunc(nvl(rec_date_to,ARA.APPLY_DATE + 1))
198
199 )
200 AND NOT EXISTS
201 (SELECT 1 FROM PA_PWP_LINKED_INVOICES
202 WHERE PROJECT_ID = pdi.project_id
203 AND DRAFT_INVOICE_NUM = pdi.draft_invoice_num );
204
205 /*
206 This cusrsor should pick up all the Supplier invoices that are totally non billable and
207 falls in the criteria specified for the concurrent program.
208 */
209 CURSOR cur_inv_unbillable IS
210 SELECT DISTINCT EI.DOCUMENT_HEADER_ID
211 FROM PA_EXPENDITURE_ITEMS EI
212 ,PA_PROJECTS PROJ
213 ,PA_TASKS TASK
214 ,PA_TASKS TOPTASK
215 WHERE proj.project_type = NVL(p_project_type ,proj.project_type)
216 AND proj.AUTO_RELEASE_PWP_INV = 'Y'
217 AND proj.segment1 BETWEEN start_project_num AND end_project_num
218 AND PROJ.PROJECT_ID = EI.PROJECT_ID
219 AND EI.DOCUMENT_HEADER_ID IS NOT NULL
220 AND EI.SYSTEM_LINKAGE_FUNCTION = 'VI'
221 AND EI.TRANSACTION_SOURCE ='AP INVOICE'
222 AND EI.BILLABLE_FLAG = 'N'
223 AND NVL(NET_ZERO_ADJUSTMENT_FLAG,'N') <> 'Y'
224 AND PROJ.PROJECT_ID = TASK.PROJECT_ID
225 AND TASK.PROJECT_ID = EI.PROJECT_ID
226 AND TASK.TASK_ID = EI.TASK_ID
227 AND TASK.TOP_TASK_ID = TOPTASK.TASK_ID
228 AND TOPTASK.PROJECT_ID = EI.PROJECT_ID
229 AND DECODE(PROJ.ENABLE_TOP_TASK_INV_MTH_FLAG, 'Y', TOPTASK.INVOICE_METHOD,PROJ.INVOICE_METHOD) = 'WORK'
230 AND NOT EXISTS
231 (SELECT /*+ INDEX(EI2 PA_EXPENDITURE_ITEMS_N27)*/
232 1
233 FROM PA_EXPENDITURE_ITEMS EI2
234 ,PA_PROJECTS PROJ
235 ,PA_TASKS TASK
236 ,PA_TASKS TOPTASK
237 WHERE EI2.DOCUMENT_HEADER_ID =EI.DOCUMENT_HEADER_ID
238 AND EI2.PROJECT_ID = PROJ.PROJECT_ID
239 AND PROJ.PROJECT_ID = TASK.PROJECT_ID
240 AND TASK.PROJECT_ID = EI2.PROJECT_ID
241 AND TASK.TASK_ID = EI2.TASK_ID
242 AND TASK.TOP_TASK_ID = TOPTASK.TASK_ID
243 AND TOPTASK.PROJECT_ID = EI2.PROJECT_ID
244 AND NVL(EI2.NET_ZERO_ADJUSTMENT_FLAG,'N') <> 'Y'
245 AND EI2.DOCUMENT_HEADER_ID IS NOT NULL
246 AND EI2.SYSTEM_LINKAGE_FUNCTION = 'VI'
247 AND EI2.TRANSACTION_SOURCE ='AP INVOICE'
248 AND
249 (( proj.AUTO_RELEASE_PWP_INV = 'Y' AND
250 (
251 EI2.BILLABLE_FLAG = 'Y'
252 AND DECODE(PROJ.ENABLE_TOP_TASK_INV_MTH_FLAG, 'Y', TOPTASK.INVOICE_METHOD,PROJ.INVOICE_METHOD) = 'WORK'
253 )
254 OR DECODE(PROJ.ENABLE_TOP_TASK_INV_MTH_FLAG, 'Y', TOPTASK.INVOICE_METHOD,PROJ.INVOICE_METHOD) = 'COST'
255 OR DECODE(PROJ.ENABLE_TOP_TASK_INV_MTH_FLAG, 'Y', TOPTASK.INVOICE_METHOD,PROJ.INVOICE_METHOD) = 'EVENT'
256 )
257 OR nvl(proj.AUTO_RELEASE_PWP_INV,'N') = 'N'
258 )
259 )
260 AND NOT EXISTS -- unpaid manually linked invoices exist
261 (SELECT 1
262 FROM
263 PA_PWP_LINKED_INVOICES PWP
264 WHERE
265 PWP.AP_INVOICE_ID = EI.DOCUMENT_HEADER_ID
266 )
267 AND EXISTS -- ONLY THOSE INVOICE WHERE A PAY WHEN PAID HOLD EXISTS .
268 (SELECT 1
269 FROM AP_HOLDS_ALL
270 WHERE INVOICE_ID = EI.DOCUMENT_HEADER_ID
271 AND HOLD_LOOKUP_CODE = 'Pay When Paid'
272 AND RELEASE_REASON IS NULL
273 );
274
275
276 BEGIN
277 P_DEBUG_MODE := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
278
279 pa_debug.init_err_stack ('Pay When Paid');
280 pa_debug.set_process(
281 x_process => 'PLSQL',
282 x_debug_mode => P_DEBUG_MODE);
283
284
285 write_log (LOG, '---------------PARAMETERS----------- ');
286 write_log (LOG, 'p_mode -> ' || p_mode);
287 write_log (LOG, 'project_type -> ' || p_project_type);
288 write_log (LOG, 'p_proj_num -> ' || p_proj_num);
289 write_log (LOG, 'P_from_proj_num -> ' || P_from_proj_num);
290 write_log (LOG, 'p_to_proj_num -> ' || p_to_proj_num);
291 write_log (LOG, 'p_customer_name -> ' || p_customer_name);
292 write_log (LOG, 'p_customer_number -> ' || p_customer_number);
293 write_log (LOG, 'p_rec_date_from -> ' || p_rec_date_from);
294 write_log (LOG, 'p_rec_date_to -> ' || p_rec_date_to);
295 write_log (LOG, 'p_sort -> ' || p_sort);
296 write_log (LOG, '------------------------------------' );
297
298 --process the parameters
299 G_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID();
300 SELECT org_id
301 INTO G_ORG_ID
302 FROM pa_implementations;
303
304 select fnd_global.user_id into l_user_id from dual;
305 select sysdate into l_date from dual;
306
307
308 if (p_proj_num is null) then
309 if ( P_from_proj_num is null ) then
310 select min(pap.segment1) into start_project_num from pa_projects pap where project_type = nvl(p_project_type,pap.project_type) ;
311 else
312 start_project_num := P_from_proj_num;
313 end if;
314 if (p_to_proj_num is null ) then
315 select max(pap.segment1) into end_project_num from pa_projects pap where project_type = nvl(p_project_type,pap.project_type) ;
316 else
317 end_project_num := p_to_proj_num;
318 end if;
319 else
320 start_project_num := p_proj_num;
321 end_project_num := p_proj_num;
322
323 end if;
324 write_log (LOG, 'start_project_num-> ' || start_project_num);
325 write_log (LOG, 'end_project_num-> ' || end_project_num);
326
327 rec_date_from := fnd_date.canonical_to_date(p_rec_date_from);
328 rec_date_to := fnd_date.canonical_to_date(p_rec_date_to);
329 -- End parameter processing
330
331 -- Pick the paid draft invoices .
332 FOR invrec IN sel_inv_autolink LOOP
333 write_log (LOG, '======================================' );
334 write_log (LOG, 'Processing Supplier Invoice Id -> ' || invrec.AP_INVOICE_ID);
335 l_billed := 'N';
336 If (is_processed(invrec.AP_INVOICE_ID) = 'N') THEN
337
338
339 if (is_eligible(invrec.AP_INVOICE_ID) = 'Y') THEN
340 l_billed := is_billed(invrec.AP_INVOICE_ID);
341 If ( l_billed = 'Y' ) then
342
343 write_log (LOG, 'Invoice eligible for release');
344 --Make entry in the report table
345 INSERT INTO PA_PWP_RELEASE_REPORT
346 (
347 ORG_ID,
348 REQUEST_ID,
349 PROJECT_ID,
350 DRAFT_INVOICE_NUM,
351 AP_INVOICE_ID,
352 LINK_TYPE,
353 RELEASE_FLAG,
354 CREATED_BY,
355 CREATION_DATE,
356 LAST_UPDATED_BY,
357 LAST_UPDATE_DATE
358 )values
359 (
360 G_ORG_ID,
361 G_REQUEST_ID,
362 invrec.project_id,
363 invrec.DRAFT_INVOICE_NUM,
364 invrec.AP_INVOICE_ID,
365 invrec.link_type,
366 'Y',
367 l_user_id,
368 l_date,
369 l_user_id,
370 l_date
371 );
372
373
374 else
375 write_log (LOG,'Billed completly ? ' || l_billed );
376 write_log (LOG, 'Invoice will not be released' );
377
378 INSERT INTO PA_PWP_RELEASE_REPORT
379 (
380 ORG_ID,
381 REQUEST_ID,
382 PROJECT_ID,
383 DRAFT_INVOICE_NUM,
384 AP_INVOICE_ID,
385 LINK_TYPE,
386 RELEASE_FLAG,
387 EXCEPTION,
388 CREATED_BY,
389 CREATION_DATE,
390 LAST_UPDATED_BY,
391 LAST_UPDATE_DATE
392 )
393 values
394 (
395 G_ORG_ID,
396 G_REQUEST_ID,
397 invrec.project_id,
398 invrec.DRAFT_INVOICE_NUM,
399 invrec.AP_INVOICE_ID,
400 invrec.link_type,
401 'N',
402 'PA_INV_UNREL_UNBILL',
403 l_user_id,
404 l_date,
405 l_user_id,
406 l_date
407 );
408 -- Make exception entry to report table
409 write_log (LOG, 'This Invoice is not yet paid fully' );
410 End if ; -- is billed
411
412 else -- is eligible
413 write_log (LOG, 'Project on hold' );
414 INSERT INTO PA_PWP_RELEASE_REPORT
415 (
416 ORG_ID,
417 REQUEST_ID,
418 PROJECT_ID,
419 DRAFT_INVOICE_NUM,
420 AP_INVOICE_ID,
421 LINK_TYPE,
422 RELEASE_FLAG,
423 EXCEPTION,
424 CREATED_BY,
425 CREATION_DATE,
426 LAST_UPDATED_BY,
427 LAST_UPDATE_DATE
428 )
429 values
430 (
431 G_ORG_ID,
432 G_REQUEST_ID,
433 invrec.project_id,
434 invrec.DRAFT_INVOICE_NUM,
435 invrec.AP_INVOICE_ID,
436 invrec.link_type,
437 'N',
438 'PA_INV_UNREL_FLAG',
439 l_user_id,
440 l_date,
441 l_user_id,
442 l_date
443 );
444 End if;
445
446 else
447 write_log (LOG, 'This invoice already processed in the current run ' );
448 End If;
449 End Loop;
450 write_log (LOG, 'End of Linked Invoice Processing' );
451 write_log (LOG, 'Start of Unlinked Invoice Processing' );
452
453 FOR invrec IN sel_inv_unlink LOOP
454 INSERT INTO PA_PWP_RELEASE_REPORT
455 (
456 ORG_ID,
457 REQUEST_ID,
458 PROJECT_ID,
459 DRAFT_INVOICE_NUM,
460 LINK_TYPE,
461 RELEASE_FLAG,
462 CREATED_BY,
463 CREATION_DATE,
464 LAST_UPDATED_BY,
465 LAST_UPDATE_DATE
466 )values
467 (G_ORG_ID,
468 G_REQUEST_ID,
469 invrec.project_id,
470 invrec.DRAFT_INVOICE_NUM,
471 invrec.link_type,
472 'X',
473 l_user_id,
474 l_date,
475 l_user_id,
476 l_date
477 );
478 End Loop;
479
480
481 write_log (LOG, 'End of Unlinked Invoice Processing' );
482 write_log (LOG, 'Start of Not Billable Invoice Processing' );
483
484 OPEN cur_inv_unbillable;
485 FETCH cur_inv_unbillable BULK COLLECT INTO l_unbill_invids;
486 CLOSE cur_inv_unbillable;
487 if l_unbill_invids.count > 0 then
488 FORALL i IN l_unbill_invids.FIRST..l_unbill_invids.LAST
489 INSERT INTO PA_PWP_RELEASE_REPORT
490 (
491 ORG_ID,
492 REQUEST_ID,
493 AP_INVOICE_ID,
494 LINK_TYPE,
495 RELEASE_FLAG,
496 CREATED_BY,
497 CREATION_DATE,
498 LAST_UPDATED_BY,
499 LAST_UPDATE_DATE
500 )values
501 (
502 G_ORG_ID,
503 G_REQUEST_ID,
504 l_unbill_invids(i),
505 'AUTOLINK',
506 'Y',
507 l_user_id,
508 l_date,
509 l_user_id,
510 l_date
511 );
512
513 End if;
514 write_log (LOG, 'End of Not Billable Invoice Processing' );
515
516 commit;
517 -- Call client extension
518
519
520 PA_CLIENT_EXTN_PWP.RELEASE_INV(G_REQUEST_ID,p_project_type,start_project_num,end_project_num,p_customer_name,p_customer_number,
521 p_rec_date_from,p_rec_date_to,p_return_status,p_error_message_code);
522 --
523
524 -- Call release API .
525 if upper(p_mode) = 'FINAL' then
526 write_log(LOG , 'FINAL Mode - Calling API to release hold');
527
528 select ap_invoice_id bulk collect into l_inv_tab
529 from PA_PWP_RELEASE_REPORT
530 where nvl(CUSTOM_RELEASE_FLAG,RELEASE_FLAG) = 'Y' and request_id = G_REQUEST_ID ;
531
532 paap_release_hold ( l_Inv_Tab
533 ,l_return_status
534 ,l_msg_count
535 ,l_msg_data );
536
537 end if;
538 -- Submitting the report request .
539
540 G_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID();
541 FND_REQUEST.set_org_id(G_ORG_ID);
542
543 write_log(LOG , 'Launching process to generate Audit Report');
544 xml_layout := FND_REQUEST.ADD_LAYOUT('PA','PAPWPRIREP','en','US','PDF');
545 req_id := FND_REQUEST.SUBMIT_REQUEST('PA', 'PAPWPRIREP', '', '', FALSE, G_REQUEST_ID
546 ,p_mode,p_project_type,p_proj_num,start_project_num,end_project_num,p_customer_name,
547 p_customer_number,p_rec_date_from,p_rec_date_to,p_sort);
548
549 write_log (LOG,'Submitted Request Id' ||req_id );
550 EXCEPTION
551 WHEN OTHERS THEN
552 write_log (LOG,'Exception' ||SQLERRM );
553 END Release_Invoice;
554
555 Procedure paap_release_hold (P_Inv_Tbl IN InvoiceId
556 ,X_return_status OUT NOCOPY VARCHAR2
557 ,X_msg_count OUT NOCOPY NUMBER
558 ,X_msg_data OUT NOCOPY VARCHAR2) IS
559
560 l_hold_reason Varchar2(240):= 'Automatic Release';
561
562 -- Cursor c1 is to fetch hold lookup code
563 -- for the invoice being passed if any of the PWP hold exists.
564 Cursor c1(p_invoice_id Number) Is
565 select hold_lookup_code from ap_holds_all
566 where invoice_id= p_invoice_id
567 and hold_lookup_code = 'Pay When Paid'
568 and release_reason IS NULL;
569
570 l_err_msg Varchar2(4000);
571
572 BEGIN
573 x_return_status := 'S';
574 X_msg_count :=0;
575
576 IF P_DEBUG_MODE = 'Y' THEN
577 write_log(LOG,'Begin: paap_release_hold ');
578 END IF;
579
580 IF p_inv_tbl.count > 0 THEN
581
582 FOR Inv_RelHOld_rec in 1..p_inv_tbl.count LOOP
583 FOR HoldRec in c1(p_inv_tbl(Inv_RelHOld_rec)) LOOP
584 BEGIN
585 IF P_DEBUG_MODE = 'Y' THEN
586 write_log(LOG,'Before calling AP_HOLDS_PKG.release_single_hold API '
587 ||'[Invoice_Id : '||p_inv_tbl(Inv_RelHOld_rec)||'] '
588 ||'[hold_lookup_code: '||HoldRec.hold_lookup_code||'] '
589 ||'[l_hold_reason: '||l_hold_reason||'] ');
590 END IF;
591
592 AP_HOLDS_PKG.release_single_hold
593 (X_invoice_id => p_inv_tbl(Inv_RelHOld_rec),
594 X_hold_lookup_code=> HoldRec.hold_lookup_code,
595 X_release_lookup_code =>l_hold_reason);
596
597 EXCEPTION
598 WHEN OTHERS THEN
599 l_err_msg:= SQLERRM;
600
601 IF P_DEBUG_MODE = 'Y' THEN
602 write_log(LOG,'In When Others Exception '||SQLERRM);
603 END IF;
604
605 x_msg_count := 1;
606 x_return_status :='E';
607 x_msg_data := SQLERRM;
608 END;
609 END LOOP;
610
611 END LOOP;
612
613 END IF;
614 COMMIT;
615
616 IF P_DEBUG_MODE = 'Y' THEN
617 write_log(LOG,'[x_return_status : '||x_return_status||' ]');
618 END IF;
619
620 IF x_return_status = 'S' THEN
621 X_msg_data := 'PA_INV_HOLD_RELEASE';
622 END IF;
623
624 EXCEPTION
625 WHEN OTHERS THEN
626 IF P_DEBUG_MODE = 'Y' THEN
627 write_log(LOG,'In When Others Exception :'||SQLERRM);
628 END IF;
629
630 x_msg_count:=1;
631 x_return_status := 'U';
632 X_msg_data:=SQLERRM;
633 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
634 END paap_release_hold;
635
636
637
638
639 PROCEDURE write_log (
640 p_message_type IN NUMBER,
641 p_message IN VARCHAR2) IS
642
643 buffer_overflow EXCEPTION;
644 PRAGMA EXCEPTION_INIT(buffer_overflow, -20000);
645
646 BEGIN
647 --FND_FILE.PUT_LINE(FND_FILE.LOG,to_char(sysdate,'HH:MI:SS: ')|| p_message);
648 pa_debug.write_file('LOG', 'pa.plsql.PA_PWP_INVOICE_REL : '|| p_message , 1);
649 EXCEPTION /* When exception occurs, program needs to be aborted. */
650 WHEN OTHERS THEN
651 raise;
652
653 END write_log;
654 /*
655 -- Function : is_billed
656 -- Type : Private
657 -- Purpose : To find out if all transactions in Projects
658 -- related to a supplier invoice are completly
659 -- billed(invoiced) and paid(in AR)
660 -- Note : For ei's with distribution rule as work , API checks
661 -- 1.if there are any CRDL lines not yet invoiced
662 -- 2.Any EI's not yet revenue generated.
663 -- 3.Any Invoices that still open in AR
664 -- For Ei's with distribution rule as COST API checks
665 -- 1.if there are any Draft Invoices linked to the Supplier Invoice
666 -- in pa_pwp_linked_invoices
667 -- Assumptions : For distribution rule 'Cost' , if User wants to keep a Pay when paid hold on
668 -- the AP invoice till a draft invoice is paid , they have to manually link the
669 -- draft invoice with the Supplier Invoice.
670 --
671 -- Parameter :
672 -- Parameters Type Required Description and Purpose
673 -- --------------------------- ------ -------- --------------------------------------------------------
674 -- p_invoice_id NUMBER YES Supplier Invoice Id
675 */
676
677 FUNCTION is_billed ( p_invoice_id IN NUMBER )
678 RETURN VARCHAR2
679 IS
680
681 v_tmp VARCHAR2(1) := 'Y';
682
683 BEGIN
684 --cchek the condition of billed but not yet paid .
685 -- WE may need to check this based of Distribution rule
686 --write_log (LOG, 'is_billed start ');
687 SELECT 'N' into v_tmp
688 FROM dual
689 WHERE EXISTS
690 (
691 -- revnue generated but unbilled transactions exist for the AP Invoice
692 SELECT 1
693 FROM pa_projects_all proj,
694 pa_tasks task,
695 pa_tasks toptask,
696 pa_expenditure_items ei,
697 pa_cust_rev_dist_lines crdl
698 WHERE proj.project_id = task.project_id
699 and task.top_task_id = toptask.task_id
700 and toptask.project_id = ei.project_id
701 and task.project_id = ei.project_id
702 and task.task_id = ei.task_id
703 and ei.DOCUMENT_HEADER_ID = p_invoice_id
704 AND crdl.expenditure_item_id = ei.expenditure_item_id
705 AND ei.system_linkage_function = 'VI'
706 AND ei.transaction_source ='AP INVOICE'
707 AND nvl(ei.net_zero_adjustment_flag,'N') <> 'Y'
708 AND ei.billable_flag = 'Y'
709 and nvl(crdl.REVERSED_FLAG,'N') <> 'Y'
710 AND crdl.LINE_NUM_REVERSED is null
711 AND crdl.draft_invoice_num IS NULL
712 AND nvl(crdl.ADDITIONAL_REVENUE_FLAG,'N') <> 'Y'
713 AND Decode(PROJ.Enable_Top_Task_Inv_Mth_Flag, 'Y', TOPTASK.Invoice_Method,PROJ.INVOICE_METHOD) = 'WORK'
714
715 )
716 OR exists
717 (
718 -- Pending revenue generation
719 SELECT 1
720 FROM
721 pa_expenditure_items ei ,
722 pa_projects_all proj,
723 pa_tasks task,
724 pa_tasks toptask
725 WHERE
726 ei.DOCUMENT_HEADER_ID = p_invoice_id
727 AND nvl(ei.net_zero_adjustment_flag,'N') <> 'Y'
728 AND ei.system_linkage_function = 'VI'
729 AND ei.revenue_distributed_flag = 'N'
730 AND ei.billable_flag = 'Y'
731 AND proj.project_id = task.project_id
732 and task.top_task_id = toptask.task_id
733 and toptask.project_id = ei.project_id
734 and task.project_id = ei.project_id
735 and task.task_id = ei.task_id
736 AND Decode(PROJ.Enable_Top_Task_Inv_Mth_Flag, 'Y', TOPTASK.Invoice_Method,PROJ.INVOICE_METHOD) = 'WORK'
737 )
738
739 OR exists
740 --Any invoices that are not interfaced to AR or interfaced but unpaid in AR
741 (
742 SELECT 1
743 FROM PA_EXPENDITURE_ITEMS EI
744 , PA_CUST_REV_DIST_LINES CRDL
745 , PA_DRAFT_INVOICE_ITEMS PDII
746 , PA_DRAFT_INVOICES PDI
747 , PA_PROJECTS PRJ
748 , PA_TASKS TASK
749 , PA_TASKS TOPTASK
750 WHERE EI.PROJECT_ID = TASK.PROJECT_ID
751 AND EI.TASK_ID = TASK.TASK_ID
752 AND PRJ.PROJECT_ID = TASK.PROJECT_ID
753 AND TASK.TOP_TASK_ID = TOPTASK.TASK_ID
754 AND TOPTASK.PROJECT_ID = EI.PROJECT_ID
755 AND TASK.PROJECT_ID = EI.PROJECT_ID
756 AND EI.DOCUMENT_HEADER_ID = P_INVOICE_ID
757 AND EI.SYSTEM_LINKAGE_FUNCTION = 'VI'
758 AND EI.BILLABLE_FLAG = 'Y'
759 AND EI.TRANSACTION_SOURCE ='AP INVOICE'
760 AND CRDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
761 AND PDII.DRAFT_INVOICE_NUM = CRDL.DRAFT_INVOICE_NUM
762 AND PDII.LINE_NUM = CRDL.DRAFT_INVOICE_ITEM_LINE_NUM
763 AND PDII.PROJECT_ID = CRDL.PROJECT_ID
764 AND DECODE(PRJ.ENABLE_TOP_TASK_INV_MTH_FLAG, 'Y', TOPTASK.INVOICE_METHOD,PRJ.INVOICE_METHOD) = 'WORK'
765 AND PDI.PROJECT_ID = CRDL.PROJECT_ID --BUG 7704332 missing join conditions added.
766 AND PDI.DRAFT_INVOICE_NUM = CRDL.DRAFT_INVOICE_NUM
767 AND (PDI.TRANSFER_STATUS_CODE IN ('P' ,'R','X')-- INVOICE NOT YET TRANSFERED TO AR
768 OR EXISTS
769 (SELECT 1
770 FROM AR_PAYMENT_SCHEDULES_ALL ARP
771 ,RA_CUSTOMER_TRX RAC
772 WHERE ARP.STATUS = 'OP'
773 AND RAC.CUSTOMER_TRX_ID = ARP.CUSTOMER_TRX_ID
774 AND RAC.INTERFACE_HEADER_ATTRIBUTE1 = PRJ.SEGMENT1
775 AND RAC.INTERFACE_HEADER_ATTRIBUTE2 = TO_CHAR(PDII.DRAFT_INVOICE_NUM)
776 AND RAC.INTERFACE_HEADER_CONTEXT = ( SELECT NAME
777 FROM
778 RA_BATCH_SOURCES RBS
779 ,PA_IMPLEMENTATIONS PI
780 WHERE
781 PI.INVOICE_BATCH_SOURCE_ID = BATCH_SOURCE_ID
782 ) --Bug 8204634
783 ) )
784
785 )
786 OR exists
787 -- unpaid manually linked invoices exist
788 (SELECT 1
789 FROM PA_PROJECTS PRJ
790 , PA_DRAFT_INVOICES PDI
791 , PA_PWP_LINKED_INVOICES PWP
792 WHERE PRJ.PROJECT_ID = PDI.PROJECT_ID
793 AND PDI.PROJECT_ID = PWP.PROJECT_ID -- Bug 7720228
794 AND PDI.DRAFT_INVOICE_NUM = PWP.DRAFT_INVOICE_NUM
795 AND PWP.AP_INVOICE_ID = P_INVOICE_ID
796 AND (PDI.TRANSFER_STATUS_CODE IN ('P' ,'R','X')
797 OR EXISTS
798 (SELECT 1
799 FROM AR_PAYMENT_SCHEDULES_ALL ARP
800 , RA_CUSTOMER_TRX RAC
801 WHERE ARP.STATUS = 'OP'
802 AND RAC.CUSTOMER_TRX_ID = ARP.CUSTOMER_TRX_ID
803 AND RAC.INTERFACE_HEADER_ATTRIBUTE1 = PRJ.SEGMENT1
804 AND RAC.INTERFACE_HEADER_ATTRIBUTE2 = TO_CHAR(PDI.DRAFT_INVOICE_NUM)
805 AND RAC.INTERFACE_HEADER_CONTEXT = ( SELECT NAME
806 FROM
807 RA_BATCH_SOURCES RBS
808 ,PA_IMPLEMENTATIONS PI
809 WHERE
810 PI.INVOICE_BATCH_SOURCE_ID = BATCH_SOURCE_ID
811 )--Bug 8204634
812 ) ) )
813
814 ;
815
816 return v_tmp;
817 EXCEPTION
818 WHEN NO_DATA_FOUND THEN
819
820 v_tmp := 'Y';
821 RETURN v_tmp;
822
823 END is_billed;
824
825 -- Function : is_processed
826 -- Type :Private
827 -- Purpose : To find if this Supplier Invoice is already processed in the
828 -- Current Run .
829 -- Note :
830 -- Assumptions :
831 -- Parameter :
832 -- Parameters Type Required Description and Purpose
833 -- --------------------------- ------ -------- --------------------------------------------------------
834 -- p_invoice_id NUMBER YES Supplier Invoice Id
835
836
837 FUNCTION is_processed ( p_invoice_id IN NUMBER )
838 RETURN VARCHAR2
839 IS
840
841 v_tmp VARCHAR2(1) := 'N';
842
843 BEGIN
844 SELECT 'Y' into v_tmp
845 FROM dual
846 WHERE EXISTS
847 ( select 1 from PA_PWP_RELEASE_REPORT
848 where request_id = G_REQUEST_ID and AP_INVOICE_ID = p_invoice_id );
849
850
851 return v_tmp;
852 EXCEPTION
853 WHEN NO_DATA_FOUND THEN
854 -- write_log (LOG, ' EX hold');
855 v_tmp := 'N';
856 RETURN v_tmp;
857
858 END is_processed;
859 /*
860 -- Function : is_eligible
861 -- Type :Private
862 -- Purpose : To find if all AUTO_RELEASE_PWP_INV is yes for all
863 -- related projects.
864 -- Note : Checks if PA_PROJECTS_ALL.AUTO_RELEASE_PWP_INV is Y/N.
865 -- Assumptions :
866 -- Parameter :
867 -- Parameters Type Required Description and Purpose
868 -- --------------------------- ------ -------- --------------------------------------------------------
869 -- p_invoice_id NUMBER YES Supplier Invoice Id
870 */
871
872 FUNCTION is_eligible ( p_invoice_id IN NUMBER )
873 RETURN VARCHAR2
874 IS
875
876 v_tmp VARCHAR2(1) := 'Y';
877
878 BEGIN
879
880 SELECT 'N' into v_tmp
881 FROM dual
882 WHERE EXISTS
883 ( select 1 from
884 pa_projects proj,
885 pa_expenditure_items_all ei
886 where
887 ei.project_id = proj.project_id and
888 ei.document_header_id = p_invoice_id and
889 proj.AUTO_RELEASE_PWP_INV = 'N' );
890
891
892
893 return v_tmp;
894 EXCEPTION
895 WHEN NO_DATA_FOUND THEN
896 v_tmp := 'Y';
897 RETURN v_tmp;
898
899 END is_eligible;
900 END PA_PWP_INVOICE_REL;