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