[Home] [Help]
PACKAGE BODY: APPS.JL_AR_AP_AWT_REPORTS_PKG
Source
1 PACKAGE BODY JL_AR_AP_AWT_REPORTS_PKG AS
2 /* $Header: jlarpwrb.pls 120.18 2008/02/12 00:13:06 dbetanco ship $ */
3
4 PROCEDURE CHECK_RETURN (v_return BOOLEAN)
5 IS
6 function_error EXCEPTION;
7 BEGIN
8 IF (v_return = FALSE ) THEN
9 RAISE function_error;
10 END IF;
11
12 EXCEPTION
13
14 WHEN function_error THEN
15 RAISE;
16 END CHECK_RETURN;
17
18
19
20
21 /**********************************************************************
22 * FUNCTION JL_AR_AP_VOID_CERTIFICATES *
23 **********************************************************************/
24
25
26
27 PROCEDURE JL_AR_AP_VOID_CERTIFICATES(
28 p_payment_id IN Number,
29 P_Calling_Sequence IN Varchar2)
30 IS
31
32
33 -----------VARIABLES-----------
34 l_debug_info Varchar2(300);
35 l_awt_success Varchar2(2000) := 'SUCCESS';
36 l_calling_sequence Varchar2(2000);
37
38 l_checkrun_name Varchar2(100);
39 l_checkrun_id Number;
40 l_check_number Number;
41
42
43
44 -------------------------------
45 -- Cursor definition
46 -------------------------------
47
48 CURSOR c_void_certificates (P_payment_Id IN Number) IS
49 SELECT payment_instruction_id,
50 paper_document_number
51 FROM iby_fd_payments_v
52 WHERE payment_id = p_payment_id;
53
54 ------------------------------
55 BEGIN
56
57
58 -------------------------------
59 -- Initializes debug variables
60 -------------------------------
61 l_calling_sequence := 'JL_AR_AP_AWT_REPORTS_PKG' || '.' ||
62 'JL_AR_AP_VOID_CERTIFICATES<--' || P_Calling_Sequence;
63
64 OPEN c_void_certificates(P_PAYMENT_ID);
65 FETCH c_void_certificates INTO l_checkrun_id, l_check_number;
66
67
68 UPDATE jl_ar_ap_awt_certif
69 set status = 'VOID'
70 where checkrun_id = l_checkrun_id
71 and check_number = l_check_number;
72
73 close c_void_certificates;
74
75 EXCEPTION
76
77 WHEN NO_DATA_FOUND THEN
78 null;
79
80 WHEN others THEN
81 IF (SQLCODE <> -20001) THEN
82 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
83 Fnd_Message.Set_Token('ERROR', SQLERRM);
84 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
85 Fnd_Message.Set_Token('PARAMETERS',
86 ', Check Id= ' || to_char(P_payment_Id));
87 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
88 END IF;
89
90
91 END JL_AR_AP_VOID_CERTIFICATES;
92
93
94
95
96
97 /**********************************************************************
98 | |
99 | |
100 | |
101 | FUNCTION JL_AR_AP_GEN_CERTIFICATES |
102 | |
103 | |
104 | |
105 | NOTES |
106 | This module is called from Wrapper to IBY Callout PMt. |
107 | The package will insert records into the table |
108 | JL_AR_AP_AWT_CERTIF. This table is used for create the |
109 | Withholding Certificates. |
110 | Check runname is the Payment Innstruction |
111 | Check ID id the payment ID |
112 | |
113 **********************************************************************/
114
115
116
117 FUNCTION JL_AR_AP_GEN_CERTIFICATES(
118 p_payment_instruction_id IN NUMBER,
119 p_calling_module IN VARCHAR2,
120 p_errmsg IN OUT NOCOPY VARCHAR2)
121 RETURN BOOLEAN
122 IS
123 -----------VARIABLES-----------
124 v_return BOOLEAN ;
125 v_total_wh_amount NUMBER ;
126 v_total_tba NUMBER ;
127 v_location_id NUMBER(15) ;
128 v_awt_type_code VARCHAR2(30) ;
129 v_tax_name VARCHAR2(15) ;
130 v_checkrun_name VARCHAR2(50) ;
131 v_checkrun_id NUMBER;
132
133 v_withholding_date DATE ;
134 v_next_certificate_number NUMBER ;
135 v_credit_amount NUMBER ;
136
137 v_prev_bank_account_num VARCHAR2(30) ;
138 v_prev_pay_document_name VARCHAR2(20) ;
139 v_prev_check_number NUMBER(15) ;
140 v_prev_payment_identification NUMBER(15) ;
141 v_prev_payment_type VARCHAR2(30) ;
142 v_prev_tax_name VARCHAR2(15) ;
143 v_prev_vendor_id NUMBER ;
144 v_prev_awt_type_code VARCHAR2(30) ;
145 v_prev_checkrun_name VARCHAR2(50) ;
146 v_prev_withholding_date DATE ;
147 v_prev_checkrun_id NUMBER;
148 v_prev_legal_entity_id NUMBER;
149 v_prev_payment_instruction_id NUMBER;
150 DEBUG_Var varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
151 l_debug_info Varchar2(300);
152 -----------CURSOR-----------
153
154
155 CURSOR cur_certificates (p_payment_instruction_id IN NUMBER ) IS
156 -- RG check run name is similar to payment batch 11i concept
157 -- The process is for the Payment Batch
158 SELECT
159 ipmt.process_type payment_type ,
160 ipmt.payment_id payment_identification,
161 aba.bank_account_num bank_account_num ,
162 nvl(ipmt.paper_document_number,ipmt.payment_reference_number) payment_document_name, --Bug6792997
163 nvl(ipmt.paper_document_number,ipmt.payment_reference_number) check_number, --Bug6792997
164 atc.global_attribute4 awt_type_code ,
165 pv.vendor_id vendor_id ,
166 'ipmt.call_app_pay_service_req_code' checkrun_name,
167 ipmt.payment_instruction_id checkrun_id,
168 aid.amount withholding_amount ,
169 aid.base_amount base_withholding_amount ,
170 aid.awt_gross_amount*nvl(aid.exchange_rate,1) taxable_base_amount,
171 ipmt.payment_date withholding_date,
172 atc.name tax_name,
173 docs.legal_entity_id legal_entity_id
174 FROM IBY_FD_PAYMENTS_V ipmt,
175 CE_BANK_ACCOUNTS aba,
176 IBY_FD_DOCS_PAYABLE_V docs,
177 AP_TAX_CODES_ALL atc,
178 AP_INVOICE_DISTRIBUTIONS_ALL aid,
179 AP_INVOICE_PAYMENTS_ALL aip,
180 PO_VENDORS pv
181 WHERE ipmt.payment_instruction_id = p_payment_instruction_id
182 and ipmt.process_type ='IMMEDIATE'
183 and ( ('GENERATE' = p_calling_module AND ipmt.payment_status ='INSTRUCTION_CREATED') OR
184 ('GENERATE' = p_calling_module AND ipmt.payment_status ='FORMATTED') OR
185 ('REPRINT' = p_calling_module AND ipmt.payment_status ='READY_TO_REPRINT'))
186 and ipmt.payment_id = docs.payment_id
187 and aip.invoice_id = docs.calling_app_doc_unique_ref2
188 and ipmt.INTERNAL_BANK_ACCOUNT_ID = aba.bank_account_id
189 and atc.tax_id = aid.withholding_tax_code_id -- BUG 954106
190 and aid.amount <> 0
191 and aid.line_type_lookup_code = 'AWT'
192 and aid.awt_invoice_payment_id = aip.invoice_payment_id
193 and aid.invoice_id = aip.invoice_id
194 and ipmt.payee_party_id = pv.party_id
195 UNION ALL
196 SELECT ipmt.process_type payment_type,
197 ipmt.payment_id payment_identification,
198 aba.bank_account_num bank_account_num,
199 nvl(ipmt.paper_document_number,ipmt.payment_reference_number) payment_document_name, --bug6792997
200 nvl(ipmt.paper_document_number,ipmt.payment_reference_number) check_number, --Bug6792997
201 atc.global_attribute4 awt_type_code,
202 pv.vendor_id vendor_id,
203 'ipmt.call_app_pay_service_req_code' checkrun_name,
204 ipmt.payment_instruction_id checkrun_id,
205 aid.withholding_amount*(-1) withholding_amount,
206 aid.base_withholding_amount*(-1) base_withholding_amount,
207 aid.gross_amount taxable_base_amoun,
208 ipmt.payment_date withholding_date,
209 atc.name tax_name,
210 docs.legal_entity_id legal_entity_id
211 FROM iby_fd_payments_v ipmt,
212 ce_bank_accounts aba,
213 iby_fd_docs_payable_v docs,
214 ap_tax_codes_all atc,
215 AP_AWT_TEMP_DISTRIBUTIONS_ALL aid,
216 ap_selected_invoices_all api,
217 po_vendors pv
218 WHERE ipmt.payment_instruction_id = p_payment_instruction_id
219 AND ipmt.process_type ='STANDARD'
220 AND (('GENERATE' = p_calling_module AND ipmt.payment_status ='INSTRUCTION_CREATED') OR
221 ('GENERATE' = p_calling_module AND ipmt.payment_status ='FORMATTED') OR
222 ('REPRINT' = p_calling_module AND ipmt.payment_status ='READY_TO_REPRINT')) -- Dario
223 AND ipmt.payment_id = docs.payment_id
224 AND api.invoice_id = docs.calling_app_doc_unique_ref2
225 AND ipmt.internal_bank_account_id = aba.bank_account_id
226 --AND atc.tax_id = aid.withholding_tax_code_id -- BUG 954106
227 AND atc.name = aid.tax_name
228 AND aid.checkrun_name = api.checkrun_name
229 AND aid.invoice_id = api.invoice_id
230 AND aid.withholding_amount <> 0
231 AND aid.payment_num = api.payment_num
232 AND ipmt.payee_party_id = pv.party_id;
233
234 /* Commented out for bug 1398067 */
235 -- and pv.global_attribute9 = 'DOMESTIC_ORIGIN'
236 --- and pv.vendor_id = ap.vendor_id;
237 -- and ap.checkrun_name = p_checkrun_name
238
239 ------------------------------------------------------------------------------------------
240 FUNCTION INS_INTO_JL_AR_AP_AWT_CERTIF(
241 v_next_certificate_number IN NUMBER ,
242 v_bank_account_num IN VARCHAR2 ,
243 v_payment_document_name IN VARCHAR2 ,
244 v_check_number IN NUMBER ,
245 v_location_id IN NUMBER ,
246 v_awt_type_code IN VARCHAR2 ,
247 v_tax_name IN VARCHAR2 ,
248 v_vendor_id IN NUMBER ,
249 v_checkrun_name IN VARCHAR2 ,
250 v_withholding_amount IN NUMBER ,
251 v_taxable_base_amount IN NUMBER ,
252 v_withholding_date IN DATE ,
253 v_credit_amount IN NUMBER,
254 v_checkrun_id IN NUMBER,
255 v_legal_entity_id IN NUMBER ,
256 v_payment_instruction_id IN NUMBER)
257
258 RETURN BOOLEAN
259 IS
260 v_table_id NUMBER;
261 column_required EXCEPTION;
262 v_column_required varchar2(50);
263 v_org_id NUMBER(15);
264 BEGIN
265
266 IF v_next_certificate_number is null THEN
267 v_column_required := 'CERTIFICATE_NUMBER';
268 raise column_required;
269 ELSIF
270 v_bank_account_num is null THEN
271
272 v_column_required := 'BANK_ACCOUNT_NUM';
273 raise column_required;
274 ELSIF
275 v_payment_document_name is null THEN
276
277 v_column_required := 'PAYMENT_DOCUMENT_NAME';
278 raise column_required;
279 ELSIF
280 v_check_number is null THEN
281
282 v_column_required := 'CHECK_NUMBER';
283 raise column_required;
284 ELSIF
285 v_location_id is null THEN
286
287 v_column_required := 'LOCATION_ID';
288 raise column_required;
289 ELSIF
290 v_withholding_date is null THEN
291
292 v_column_required := 'AWT_DATE';
293 raise column_required;
294 ELSIF
295 v_awt_type_code is null THEN
296
297 v_column_required := 'AWT_TYPE_CODE';
298 raise column_required;
299 ELSIF
300 v_tax_name is null THEN
301
302 v_column_required := 'TAX_NAME';
303 raise column_required;
304 ELSIF
305 v_vendor_id is null THEN
306
307 v_column_required := 'VENDOR_ID';
308 raise column_required;
309 -- ELSIF
310 -- v_checkrun_name is null THEN
311 --
312 -- v_column_required := 'CHECKRUN_NAME';
313 -- raise column_required;
314 ELSIF
315 v_withholding_amount is null THEN
316
317 v_column_required := 'WITHHOLDING_AMOUNT';
318 raise column_required;
319 ELSIF
320 v_taxable_base_amount is null THEN
321
322 v_column_required := 'TAXABLE_BASE_AMOUNT';
323 raise column_required;
324 ELSIF
325 v_credit_amount is null THEN
326
327 v_column_required := 'CREDIT_AMOUNT';
328 raise column_required;
329 END IF;
330
331
332 SELECT jl_ar_ap_awt_certif_s.nextval
333 INTO v_table_id
334 FROM dual ;
335
336 --Bug 6504015. added code for setting the org-id while insert.
337 v_org_id := MO_GLOBAL.get_current_org_id;
338 --in certificates we insert now for r12 payment id rather than check id
339
340 INSERT INTO jl_ar_ap_awt_certif_all
341 (certificate_id ,
342 certificate_number ,
343 bank_account_num ,
344 payment_document_name ,
345 check_number ,
346 location_id ,
347 awt_date ,
348 awt_type_code ,
349 tax_name ,
350 vendor_id ,
351 checkrun_name ,
352 credit_amount ,
353 taxable_base_amount ,
354 withholding_amount ,
355 status ,
356 created_by ,
357 creation_date ,
358 last_updated_by ,
359 last_update_date ,
360 last_update_login ,
361 request_id ,
362 program_application_id ,
363 program_id ,
364 program_update_date ,
365 checkrun_id,
366 legal_entity_id,
367 payment_instruction_id,
368 org_id)
369 VALUES(
370 v_table_id ,
371 v_next_certificate_number ,
372 v_bank_account_num ,
373 v_payment_document_name ,
374 v_check_number ,
375 v_location_id ,
376 v_withholding_date ,
377 v_awt_type_code ,
378 v_tax_name ,
379 v_vendor_id ,
380 v_checkrun_name ,
381 v_credit_amount ,
382 v_taxable_base_amount ,
383 v_withholding_amount ,
384 'NOT PRINTED' ,
385 fnd_global.user_id ,
386 SYSDATE ,
387 fnd_global.user_id ,
388 SYSDATE ,
389 fnd_global.login_id ,
390 fnd_global.conc_request_id ,
391 fnd_global.prog_appl_id ,
392 fnd_global.conc_program_id ,
393 SYSDATE,
394 v_checkrun_id,
395 v_legal_entity_id,
396 v_payment_instruction_id,
397 v_org_id );
398
399 RETURN (TRUE);
400
401 EXCEPTION
402 WHEN column_required THEN
403 p_errmsg := p_errmsg||'Column constraint NOT NULL in the column '
404 ||v_column_required||' - Error in INS_INTO_JL_AR_AP_AWT_CERTIF';
405 RETURN(FALSE);
406
407 WHEN OTHERS THEN
408 p_errmsg := p_errmsg||SUBSTR(SQLERRM, 1, 100);
409 p_errmsg := p_errmsg||' - Error in INS_INTO_JL_AR_AP_AWT_CERTIF';
410 RETURN(FALSE);
411 END;
412
413 ------------------------------------------------------------------------------------------
414
415 FUNCTION SAME_PAYMENT_TAX_NAME_VENDOR(
416 v_prev_bank_account_num IN VARCHAR2 ,
417 v_prev_pay_document_name IN VARCHAR2 ,
418 v_prev_check_number IN NUMBER ,
419 v_prev_tax_name IN VARCHAR2 ,
420 v_prev_vendor_id IN NUMBER ,
421 v_tax_name IN VARCHAR2 ,
422 v_vendor_id IN NUMBER ,
423 v_bank_account_num IN VARCHAR2 ,
424 v_pay_document_name IN VARCHAR2 ,
425 v_check_number IN NUMBER )
426
427
428 RETURN BOOLEAN
429 IS
430 BEGIN
431 -- RG
432 -- IF (v_prev_tax_name is null and v_prev_vendor_id is null) then
433 -- v_prev_tax_name:= v_tax_name;
434 -- v_prev_vendor_id:=v_vendor_id;
435 -- v_prev_bank_account_num:=v_bank_account_num;
436 -- v_prev_pay_document_name:=v_pay_document_name;
437 -- v_prev_check_number:=v_check_number;
438 -- RETURN(TRUE) ;
439 -- else
440 IF (v_tax_name = v_prev_tax_name)
441 and (v_vendor_id = v_prev_vendor_id)
442 and (v_bank_account_num = v_prev_bank_account_num)
443 and (v_pay_document_name = v_prev_pay_document_name)
444 and (v_check_number = v_prev_check_number)
445 THEN
446 RETURN(TRUE) ;
447 ELSE
448 RETURN(FALSE) ;
449 END IF;
450 -- end if;
451 END;
452
453 ------------------------------------------------------------------------------------------
454
455 FUNCTION GEN_CERTIFICATE_NUMBER (
456 p_awt_type_code IN VARCHAR2,
457 p_next_certificate_number IN OUT NOCOPY NUMBER)
458
459 RETURN BOOLEAN
460 IS
461 max_number NUMBER ;
462 BEGIN
463
464
465 SELECT jaaac1.certificate_number
466 INTO max_number
467 FROM jl_ar_ap_awt_certif_all jaaac1
468 WHERE jaaac1.awt_type_code = p_awt_type_code
469 AND jaaac1.certificate_number = (select max(jaaac2.certificate_number)
470 from jl_ar_ap_awt_certif_all jaaac2
471 where jaaac2.awt_type_code = p_awt_type_code)
472 FOR UPDATE;
473
474
475 IF max_number IS NULL
476 THEN
477 p_next_certificate_number := 1;
478 ELSE
479 p_next_certificate_number := max_number + 1;
480 END IF ;
481
482 RETURN (TRUE);
483
484 EXCEPTION
485
486 WHEN NO_DATA_FOUND THEN
487
488 p_next_certificate_number := 1;
489 RETURN (TRUE);
490
491 WHEN OTHERS THEN
492 p_errmsg := SUBSTR(SQLERRM, 1, 100);
493 p_errmsg := p_errmsg||' - Error in GEN_CERTIFICATE_NUMBER';
494 RETURN(FALSE);
495 END;
496
497
498 ------------------------------------------------------------------------------------------
499
500 FUNCTION CALCULATE_CREDIT_AMOUNT(
501 v_vendor_id IN NUMBER ,
502 v_awt_type_code IN VARCHAR2 ,
503 v_tax_name IN VARCHAR2 ,
504 v_payment_identification IN NUMBER ,
505 v_payment_type IN VARCHAR2 ,
506 v_credit_amount IN OUT NOCOPY NUMBER)
507 RETURN BOOLEAN
508 IS
509 v_amount NUMBER;
510 BEGIN
511
512 --- Initialize Variables ---
513 v_credit_amount := 0;
514
515
516 SELECT (jlts.calc_wh_amnt - jlts.act_wheld_amnt)
517 INTO v_amount
518 FROM jl_ar_ap_sup_awt_cr_lts jlts,
519 ap_tax_codes atc
520 WHERE
521 jlts.po_vendor_id = v_vendor_id
522 and jlts.awt_type_code = v_awt_type_code
523 and jlts.tax_id = atc.tax_id
524 and status = 'AA'
525 -- and For KI uptake
526 -- (( v_payment_type = 'BATCH'
527 -- and selected_check_id = v_payment_identification ) --this condition is NA but will leave it
528 -- or ( v_payment_type = 'QUICK'
529 -- and check_id = v_payment_identification ))
530 and atc.name = v_tax_name;
531
532 v_credit_amount := v_amount;
533 RETURN (TRUE);
534
535
536 EXCEPTION
537
538 WHEN NO_DATA_FOUND THEN
539 RETURN (TRUE);
540
541 WHEN OTHERS THEN
542 p_errmsg := SUBSTR(SQLERRM, 1, 100);
543 p_errmsg := p_errmsg||' - Error in GEN_CERTIFICATE_NUMBER';
544 RETURN(FALSE);
545 END;
546
547 --------------------------------------------------------------------------------
548 --------------------------------- MAIN CODE ------------------------------------
549 --------------------------------------------------------------------------------
550
551
552 BEGIN
553
554
555 --- Initialize Variables ---
556 v_prev_tax_name := NULL ;
557 v_prev_vendor_id := NULL ;
558 v_total_wh_amount := 0 ;
559 v_prev_awt_type_code := NULL ;
560 v_prev_tax_name := NULL ;
561 v_prev_checkrun_name := NULL ;
562 v_prev_checkrun_id := NULL ;
563 v_prev_withholding_date := NULL ;
564 v_prev_bank_account_num := NULL ;
565 v_prev_pay_document_name := NULL ;
566 v_prev_check_number := 0 ;
567 v_prev_payment_type := NULL ;
568 v_prev_payment_identification := 0 ;
569 v_prev_legal_entity_id := NULL;
570 v_prev_payment_instruction_id :=NULL;
571
572 v_next_certificate_number := 0 ;
573 v_total_wh_amount := 0 ;
574 v_total_tba := 0 ;
575 --
576
577 IF (p_payment_instruction_id is not null ) THEN
578
579 -- Retrieving LOCATION_ID
580 v_location_id := jg_zz_company_info.get_location_id;
581
582 l_debug_info := 'Selecting Certificates ...';
583 IF (DEBUG_Var = 'Y') THEN
584 JL_ZZ_AP_EXT_AWT_UTIL.Debug (l_debug_info);
585 END IF;
586
587 FOR rec_batch IN cur_certificates (p_payment_instruction_id) LOOP
588 l_debug_info := 'Selected Certificate Check:' ||
589 to_char(rec_batch.check_number) || ' Type:' || rec_batch.awt_type_code;
590 -- Debug Information
591 IF (DEBUG_Var = 'Y') THEN
592 JL_ZZ_AP_EXT_AWT_UTIL.Debug (l_debug_info);
593 END IF;
594 -- End Debug
595
596 IF SAME_PAYMENT_TAX_NAME_VENDOR (
597 v_prev_bank_account_num,
598 v_prev_pay_document_name,
599 v_prev_check_number,
600 v_prev_tax_name,
601 v_prev_vendor_id,
602 rec_batch.tax_name,
603 rec_batch.vendor_id,
604 rec_batch.bank_account_num,
605 rec_batch.payment_document_name,
606 rec_batch.check_number)
607 THEN
608 IF rec_batch.base_withholding_amount is null
609 THEN
610 v_total_wh_amount := v_total_wh_amount +
611 rec_batch.withholding_amount;
612 ELSE
613 v_total_wh_amount := v_total_wh_amount +
614 rec_batch.base_withholding_amount;
615 END IF;
616 v_total_tba := v_total_tba + rec_batch.taxable_base_amount;
617
618 ELSE
619 IF (v_prev_tax_name IS NOT NULL)
620 and (v_prev_vendor_id IS NOT NULL)
621 THEN
622 v_return := GEN_CERTIFICATE_NUMBER (
623 v_prev_awt_type_code ,
624 v_next_certificate_number) ;
625
626 -- check_return (v_return);
627
628 v_return := CALCULATE_CREDIT_AMOUNT(
629 v_prev_vendor_id ,
630 v_prev_awt_type_code ,
631 v_prev_tax_name ,
632 v_prev_payment_identification ,
633 v_prev_payment_type ,
634 v_credit_amount);
635
636 -- check_return (v_return);
637
638 v_return := INS_INTO_JL_AR_AP_AWT_CERTIF(
639 v_next_certificate_number ,
640 v_prev_bank_account_num ,
641 v_prev_pay_document_name ,
642 v_prev_check_number ,
643 v_location_id ,
644 v_prev_awt_type_code ,
645 v_prev_tax_name ,
646 v_prev_vendor_id ,
647 v_prev_checkrun_name ,
648 v_total_wh_amount ,
649 v_total_tba ,
650 v_prev_withholding_date ,
651 v_credit_amount,
652 v_prev_checkrun_id,
653 v_prev_legal_entity_id,
654 v_prev_payment_instruction_id );
655
656 -- check_return (v_return);
657
658 END IF;
659
660
661 ---Initialize Variables for sum New TAX_NAME and SUPPLIER---
662
663
664 v_prev_bank_account_num := rec_batch.bank_account_num ;
665 v_prev_pay_document_name := rec_batch.payment_document_name;
666 v_prev_check_number := rec_batch.check_number ;
667 v_prev_tax_name := rec_batch.tax_name ;
668 v_prev_vendor_id := rec_batch.vendor_id ;
669 v_prev_awt_type_code := rec_batch.awt_type_code ;
670 v_prev_tax_name := rec_batch.tax_name ;
671 v_prev_checkrun_name := rec_batch.checkrun_name ;
672 v_prev_withholding_date := rec_batch.withholding_date ;
673 v_total_tba := rec_batch.taxable_base_amount;
674 v_prev_payment_type := rec_batch.payment_type ;
675 v_prev_payment_identification := rec_batch.payment_identification;
676 v_prev_checkrun_id := rec_batch.checkrun_id;
677 v_prev_legal_entity_id := rec_batch.legal_entity_id;
678 v_prev_payment_instruction_id := p_payment_instruction_id;
679
680
681 IF rec_batch.base_withholding_amount is null
682 THEN
683 v_total_wh_amount := rec_batch.withholding_amount;
684 ELSE
685 v_total_wh_amount := rec_batch.base_withholding_amount;
686 END IF;
687
688 ---
689 END IF;
690
691 END LOOP;
692
693
694 --
695 -- INSERT THE LAST LINE
696 --
697
698 IF (v_prev_tax_name IS NOT NULL)
699 and (v_prev_vendor_id IS NOT NULL)
700 THEN
701
702 v_return := GEN_CERTIFICATE_NUMBER (
703 v_prev_awt_type_code ,
704 v_next_certificate_number);
705
706 -- check_return (v_return);
707
708
709 v_return := CALCULATE_CREDIT_AMOUNT(
710 v_prev_vendor_id ,
711 v_prev_awt_type_code ,
712 v_prev_tax_name ,
713 v_prev_payment_identification ,
714 v_prev_payment_type ,
715 v_credit_amount);
716
717 -- check_return (v_return);
718
719
720 v_return := INS_INTO_JL_AR_AP_AWT_CERTIF(
721 v_next_certificate_number ,
722 v_prev_bank_account_num ,
723 v_prev_pay_document_name ,
724 v_prev_check_number ,
725 v_location_id ,
726 v_prev_awt_type_code ,
727 v_prev_tax_name ,
728 v_prev_vendor_id ,
729 v_prev_checkrun_name ,
730 v_total_wh_amount ,
731 v_total_tba ,
732 v_prev_withholding_date ,
733 v_credit_amount,
734 v_prev_checkrun_id,
735 v_prev_legal_entity_id,
736 v_prev_payment_instruction_id );
737
738 -- check_return (v_return);
739
740 p_errmsg := ' All the certificates have been generated correctly ';
741 RETURN(TRUE);
742 ELSE
743 p_errmsg := ' Certificates have not been generated';
744 RETURN(TRUE);
745 END IF;
746
747 ELSE
748 p_errmsg := ' The parameter is null ';
749 RETURN(FALSE);
750 END IF;
751
752
753 EXCEPTION
754 WHEN NO_DATA_FOUND THEN
755 RETURN(TRUE);
756 WHEN OTHERS THEN
757 p_errmsg := p_errmsg||' - Package JL_AR_AP_GEN_CERTIFICATES ';
758 -- R12 Cannot have this rollback.
759 --rollback;
760 -- Debug Information
761 IF (DEBUG_Var = 'Y') THEN
762 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('CERTIFICATES ERROR: '||SQLERRM);
763 END IF;
764 -- end debug
765
766 RETURN(FALSE);
767
768 END JL_AR_AP_GEN_CERTIFICATES;
769
770 END JL_AR_AP_AWT_REPORTS_PKG;