[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.27.12020000.2 2012/11/01 22:20:43 abuissa ship $ */
3
4 -- Logging Infra
5 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'JL.PLSQL.JL_AR_AP_AWT_REPORTS_PKG.';
13 x_org_id NUMBER; --bug 14798548
14 -- Logging Infra
15
16 PROCEDURE CHECK_RETURN (v_return BOOLEAN)
17 IS
18 function_error EXCEPTION;
19 BEGIN
20 IF (v_return = FALSE ) THEN
21 RAISE function_error;
22 END IF;
23
24 EXCEPTION
25
26 WHEN function_error THEN
27 RAISE;
28 END CHECK_RETURN;
29
30
31
32
33 /**********************************************************************
34 * FUNCTION JL_AR_AP_VOID_CERTIFICATES *
35 **********************************************************************/
36
37
38
39 PROCEDURE JL_AR_AP_VOID_CERTIFICATES(
40 p_payment_id IN Number,
41 P_Calling_Sequence IN Varchar2)
42 IS
43
44
45 -----------VARIABLES-----------
46 l_debug_info Varchar2(300);
47 l_awt_success Varchar2(2000) := 'SUCCESS';
48 l_calling_sequence Varchar2(2000);
49
50 l_checkrun_name Varchar2(100);
51 l_checkrun_id Number;
52 l_check_number Number;
53
54
55 -- Logging Infra
56 l_procedure_name CONSTANT VARCHAR2(30) := 'JL_AR_AP_VOID_CERTIFICATES';
57 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
58 -- Logging Infra
59
60
61
62 -------------------------------
63 -- Cursor definition
64 -------------------------------
65
66 CURSOR c_void_certificates (P_payment_Id IN Number) IS
67 SELECT payment_instruction_id,
68 --paper_document_number
69 nvl(paper_document_number,payment_reference_number) check_number --bug10012521
70 FROM iby_fd_payments_v
71 WHERE payment_id = p_payment_id;
72
73 ------------------------------
74 BEGIN
75 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Calling Sequence Debug:'||P_Calling_Sequence);
76
77
78
79 -- Logging Infra: Procedure level
80 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
81 l_log_msg := l_procedure_name||'(+)';
82 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
83 END IF;
84 -- Logging Infra: Procedure level
85
86 -------------------------------
87 -- Initializes debug variables
88 -------------------------------
89 l_calling_sequence := 'JL_AR_AP_AWT_REPORTS_PKG' || '.' ||
90 'JL_AR_AP_VOID_CERTIFICATES<--' || P_Calling_Sequence;
91 -- Logging Infra: Statement level
92 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
93 l_log_msg := 'Parameter ';
94 l_log_msg := l_log_msg||'P_Payment_Id: '||p_payment_id;
95 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
96 END IF;
97 -- Logging Infra: Statement level
98
99 OPEN c_void_certificates(P_PAYMENT_ID);
100 FETCH c_void_certificates INTO l_checkrun_id, l_check_number;
101
102 -- Logging Infra: Statement level
103 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
104 l_log_msg := 'l_checkrun_id: '||l_checkrun_id;
105 l_log_msg := l_log_msg||'l_check_number: '||l_check_number;
106 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
107 END IF;
108 -- Logging Infra: Statement level
109 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Checkrun_id:'||l_checkrun_id);
110 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Check_number:'||l_check_number);
111
112 UPDATE jl_ar_ap_awt_certif
113 set status = 'VOID'
114 where checkrun_id = l_checkrun_id
115 and check_number = l_check_number;
116
117 close c_void_certificates;
118
119 -- Logging Infra: Procedure level
120 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
121 l_log_msg := l_procedure_name||'(-)';
122 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
123 END IF;
124 -- Logging Infra: Procedure level
125
126
127 EXCEPTION
128
129 WHEN NO_DATA_FOUND THEN
130 -- null;
131
132 -- Logging Infra: Statement level
133 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
134 l_log_msg := 'EXCEPTION: No Data Found';
135 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
136 END IF;
137 -- Logging Infra: Statement level
138
139
140 WHEN others THEN
141 IF (SQLCODE <> -20001) THEN
142 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
143 Fnd_Message.Set_Token('ERROR', SQLERRM);
144 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
145 Fnd_Message.Set_Token('PARAMETERS',
146 ', Check Id= ' || to_char(P_payment_Id));
147 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
148 END IF;
149
150 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
151 FND_LOG.STRING(G_LEVEL_STATEMENT,
152 G_MODULE_NAME || l_procedure_name,
153 SQLCODE || ': ' || SQLERRM);
154 END IF;
155
156
157 END JL_AR_AP_VOID_CERTIFICATES;
158
159
160
161 /**********************************************************************
162 | |
163 | |
164 | |
165 | FUNCTION JL_AR_AP_GEN_CERTIFICATES |
166 | |
167 | |
168 | |
169 | NOTES |
170 | This module is called from Wrapper to IBY Callout PMt. |
171 | The package will insert records into the table |
172 | JL_AR_AP_AWT_CERTIF. This table is used for create the |
173 | Withholding Certificates. |
174 | Check runname is the Payment Innstruction |
175 | Check ID id the payment ID |
176 | |
177 **********************************************************************/
178
179
180
181 FUNCTION JL_AR_AP_GEN_CERTIFICATES(
182 p_payment_instruction_id IN NUMBER,
183 p_calling_module IN VARCHAR2,
184 p_errmsg IN OUT NOCOPY VARCHAR2)
185 RETURN BOOLEAN
186 IS
187 -----------VARIABLES-----------
188 v_return BOOLEAN ;
189 v_total_wh_amount NUMBER ;
190 v_total_tba NUMBER ;
191 v_location_id NUMBER(15) ;
192 v_awt_type_code VARCHAR2(30) ;
193 v_tax_name VARCHAR2(15) ;
194 v_checkrun_name VARCHAR2(50) ;
195 v_checkrun_id NUMBER;
196
197 v_withholding_date DATE ;
198 v_next_certificate_number NUMBER ;
199 v_credit_amount NUMBER ;
200
201 v_prev_bank_account_num VARCHAR2(30) ;
202 v_prev_pay_document_name VARCHAR2(20) ;
203 v_prev_check_number NUMBER(15) ;
204 v_prev_payment_identification NUMBER(15) ;
205 v_prev_payment_type VARCHAR2(30) ;
206 v_prev_tax_name VARCHAR2(15) ;
207 v_prev_vendor_id NUMBER ;
208 v_prev_awt_type_code VARCHAR2(30) ;
209 v_prev_checkrun_name VARCHAR2(50) ;
210 v_prev_withholding_date DATE ;
211 v_prev_checkrun_id NUMBER;
212 v_prev_legal_entity_id NUMBER;
213 v_prev_payment_instruction_id NUMBER;
214 DEBUG_Var varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
215 l_debug_info Varchar2(300);
216 --Logging infra
217 l_procedure_name CONSTANT VARCHAR2(30) := 'JL_AR_AP_GEN_CERTIFICATES';
218 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
219 --Logging infra
220 -----------CURSOR-----------
221
222
223 CURSOR cur_certificates (p_payment_instruction_id IN NUMBER ) IS
224 -- RG check run name is similar to payment batch 11i concept
225 -- The process is for the Payment Batch
226 SELECT
227 ipmt.process_type payment_type ,
228 ipmt.payment_id payment_identification,
229 aba.bank_account_num bank_account_num ,
230 nvl(ipmt.paper_document_number,ipmt.payment_reference_number) payment_document_name, --Bug6792997
231 nvl(ipmt.paper_document_number,ipmt.payment_reference_number) check_number, --Bug6792997
232 atc.global_attribute4 awt_type_code ,
233 pv.vendor_id vendor_id ,
234 'ipmt.call_app_pay_service_req_code' checkrun_name,
235 ipmt.payment_instruction_id checkrun_id,
236 aid.amount withholding_amount ,
237 aid.base_amount base_withholding_amount ,
238 -- Payment Exchange Rate ER 8648739 Start
239 aid.awt_gross_amount*nvl(aip.exchange_rate,1) taxable_base_amount,
240 -- Payment Exchange Rate ER 8648739 End
241 ipmt.payment_date withholding_date,
242 atc.name tax_name,
243 docs.legal_entity_id legal_entity_id
244 , aid.org_id --bug 14798548
245 FROM IBY_FD_PAYMENTS_V ipmt,
246 CE_BANK_ACCOUNTS aba,
247 IBY_FD_DOCS_PAYABLE_V docs,
248 AP_TAX_CODES_ALL atc,
249 AP_INVOICE_DISTRIBUTIONS_ALL aid,
250 AP_INVOICE_PAYMENTS_ALL aip,
251 PO_VENDORS pv
252 WHERE ipmt.payment_instruction_id = p_payment_instruction_id
253 and ipmt.process_type ='IMMEDIATE'
254 and ( ('GENERATE' = p_calling_module AND ipmt.payment_status ='INSTRUCTION_CREATED') OR
255 ('GENERATE' = p_calling_module AND ipmt.payment_status ='FORMATTED') OR
256 ('REPRINT' = p_calling_module AND ipmt.payment_status ='READY_TO_REPRINT'))
257 and ipmt.payment_id = docs.payment_id
258 and aip.invoice_id = docs.calling_app_doc_unique_ref2
259 -- Payment Exchange Rate ER 8648739 Start
260 and aip.invoice_payment_id = docs.calling_app_doc_unique_ref4
261 -- Payment Exchange Rate ER 8648739 End
262 and ipmt.INTERNAL_BANK_ACCOUNT_ID = aba.bank_account_id
263 and atc.tax_id = aid.withholding_tax_code_id -- BUG 954106
264 and aid.amount <> 0
265 and aid.line_type_lookup_code = 'AWT'
266 and aid.awt_invoice_payment_id = aip.invoice_payment_id
267 and aid.invoice_id = aip.invoice_id
268 and aid.org_id = aip.org_id --bug 12853840
269 and aid.org_id = atc.org_id --bug 12853840
270 and ipmt.payee_party_id = pv.party_id
271 UNION ALL
272 SELECT ipmt.process_type payment_type,
273 ipmt.payment_id payment_identification,
274 aba.bank_account_num bank_account_num,
275 nvl(ipmt.paper_document_number,ipmt.payment_reference_number) payment_document_name, --bug6792997
276 nvl(ipmt.paper_document_number,ipmt.payment_reference_number) check_number, --Bug6792997
277 atc.global_attribute4 awt_type_code,
278 pv.vendor_id vendor_id,
279 'ipmt.call_app_pay_service_req_code' checkrun_name,
280 ipmt.payment_instruction_id checkrun_id,
281 aid.withholding_amount*(-1) withholding_amount,
282 aid.base_withholding_amount*(-1) base_withholding_amount,
283 -- Payment Exchange Rate ER 8648739 Start
284 aid.gross_amount * nvl(api.payment_exchange_rate,1) taxable_base_amount,
285 -- Payment Exchange Rate ER 8648739 End
286 ipmt.payment_date withholding_date,
287 atc.name tax_name,
288 docs.legal_entity_id legal_entity_id
289 , aid.org_id --bug 14798548
290 FROM iby_fd_payments_v ipmt,
291 ce_bank_accounts aba,
292 iby_fd_docs_payable_v docs,
293 ap_tax_codes_all atc,
294 AP_AWT_TEMP_DISTRIBUTIONS_ALL aid,
295 ap_selected_invoices_all api,
296 po_vendors pv
297 WHERE ipmt.payment_instruction_id = p_payment_instruction_id
298 AND ipmt.process_type ='STANDARD'
299 AND (('GENERATE' = p_calling_module AND ipmt.payment_status ='INSTRUCTION_CREATED') OR
300 ('GENERATE' = p_calling_module AND ipmt.payment_status ='FORMATTED') OR
301 ('REPRINT' = p_calling_module AND ipmt.payment_status ='READY_TO_REPRINT')) -- Dario
302 AND ipmt.payment_id = docs.payment_id
303 AND api.invoice_id = docs.calling_app_doc_unique_ref2
304 AND ipmt.internal_bank_account_id = aba.bank_account_id
305 --AND atc.tax_id = aid.withholding_tax_code_id -- BUG 954106
306 AND atc.name = aid.tax_name
307 AND aid.checkrun_name = api.checkrun_name
308 AND aid.invoice_id = api.invoice_id
309 AND aid.withholding_amount <> 0
310 AND aid.payment_num = api.payment_num
311 and aid.org_id = api.org_id --bug 12853840
312 and aid.org_id = atc.org_id --bug 12853840
313 AND ipmt.payee_party_id = pv.party_id
314 ORDER BY tax_name,
315 vendor_id,
316 bank_account_num,
317 payment_document_name,
318 check_number;
319
320 /* Commented out for bug 1398067 */
321 -- and pv.global_attribute9 = 'DOMESTIC_ORIGIN'
322 --- and pv.vendor_id = ap.vendor_id;
323 -- and ap.checkrun_name = p_checkrun_name
324
325 -- Bug 10157541 Start
326 -- For Argentina PPR process should be run for one org only
327 -- since the recommended setup is 1 (LE) - 1(OU) and 1(SOB/Ledger)
328 -- Since payment_instruction is not unique in the table I am doing
329 -- a distinct
330 Cursor C_Location Is
331 Select distinct location_id
332 From iby_payments_all ipa,
333 hr_organization_units org
334 Where ipa.payment_instruction_id = p_payment_instruction_id
335 And org.organization_id = ipa.org_id;
336 -- Bug 10157541 End
337 ------------------------------------------------------------------------------------------
338 FUNCTION INS_INTO_JL_AR_AP_AWT_CERTIF(
339 v_next_certificate_number IN NUMBER ,
340 v_bank_account_num IN VARCHAR2 ,
341 v_payment_document_name IN VARCHAR2 ,
342 v_check_number IN NUMBER ,
343 v_location_id IN NUMBER ,
344 v_awt_type_code IN VARCHAR2 ,
345 v_tax_name IN VARCHAR2 ,
346 v_vendor_id IN NUMBER ,
347 v_checkrun_name IN VARCHAR2 ,
348 v_withholding_amount IN NUMBER ,
349 v_taxable_base_amount IN NUMBER ,
350 v_withholding_date IN DATE ,
351 v_credit_amount IN NUMBER,
352 v_checkrun_id IN NUMBER,
353 v_legal_entity_id IN NUMBER ,
354 v_payment_instruction_id IN NUMBER)
355
356 RETURN BOOLEAN
357 IS
358 v_table_id NUMBER;
359 column_required EXCEPTION;
360 v_column_required varchar2(50);
361 /* bug 14798548 - Will use x_org_id var now
362 v_org_id NUMBER(15); */
363
364 -- Logging Infra
365 l_procedure_name CONSTANT VARCHAR2(30) := 'INS_INTO_JL_AR_AP_AWT_CERTIF';
366 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
367 -- Logging Infra
368
369 BEGIN
370
371 -- Logging Infra: Procedure level
372 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
373 l_log_msg := l_procedure_name||'(+)';
374 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
375 END IF;
376 -- Logging Infra: Procedure level
377
378
379 IF v_next_certificate_number is null THEN
380 v_column_required := 'CERTIFICATE_NUMBER';
381 raise column_required;
382 ELSIF
383 v_bank_account_num is null THEN
384
385 v_column_required := 'BANK_ACCOUNT_NUM';
386 raise column_required;
387 ELSIF
388 v_payment_document_name is null THEN
389
390 v_column_required := 'PAYMENT_DOCUMENT_NAME';
391 raise column_required;
392 ELSIF
393 v_check_number is null THEN
394
395 v_column_required := 'CHECK_NUMBER';
396 raise column_required;
397 ELSIF
398 v_location_id is null THEN
399
400 v_column_required := 'LOCATION_ID';
401 raise column_required;
402 ELSIF
403 v_withholding_date is null THEN
404
405 v_column_required := 'AWT_DATE';
406 raise column_required;
407 ELSIF
408 v_awt_type_code is null THEN
409
410 v_column_required := 'AWT_TYPE_CODE';
411 raise column_required;
412 ELSIF
413 v_tax_name is null THEN
414
415 v_column_required := 'TAX_NAME';
416 raise column_required;
417 ELSIF
418 v_vendor_id is null THEN
419
420 v_column_required := 'VENDOR_ID';
421 raise column_required;
422 -- ELSIF
423 -- v_checkrun_name is null THEN
424 --
425 -- v_column_required := 'CHECKRUN_NAME';
426 -- raise column_required;
427 ELSIF
428 v_withholding_amount is null THEN
429
430 v_column_required := 'WITHHOLDING_AMOUNT';
431 raise column_required;
432 ELSIF
433 v_taxable_base_amount is null THEN
434
435 v_column_required := 'TAXABLE_BASE_AMOUNT';
436 raise column_required;
437 ELSIF
438 v_credit_amount is null THEN
439
440 v_column_required := 'CREDIT_AMOUNT';
441 raise column_required;
442 END IF;
443
444
445 -- Logging Infra: Statement level
446 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
447 l_log_msg := 'Getting next sequence from jl_ar_ap_awt_certif_s';
448 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
449 END IF;
450 -- Logging Infra: Statement level
451
452
453 SELECT jl_ar_ap_awt_certif_s.nextval
454 INTO v_table_id
455 FROM dual ;
456
457 --Bug 6504015. added code for setting the org-id while insert.
458 --bug 14798548 v_org_id := MO_GLOBAL.get_current_org_id;
459 --in certificates we insert now for r12 payment id rather than check id
460
461 -- Logging Infra: Statement level
462 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
463 l_log_msg := 'Parameters ';
464 l_log_msg := l_log_msg||'x_org_id: '||x_org_id; --bug 14798548
465 l_log_msg := l_log_msg||'v_table_id: '||v_table_id;
466 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
467 END IF;
468 -- Logging Infra: Statement level
469
470
471 -- Logging Infra: Statement level
472 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
473 l_log_msg := 'Inserting into jl_ar_ap_awt_certif_all table ';
474 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
475 END IF;
476 -- Logging Infra: Statement level
477
478
479 INSERT INTO jl_ar_ap_awt_certif_all
480 (certificate_id ,
481 certificate_number ,
482 bank_account_num ,
483 payment_document_name ,
484 check_number ,
485 location_id ,
486 awt_date ,
487 awt_type_code ,
488 tax_name ,
489 vendor_id ,
490 checkrun_name ,
491 credit_amount ,
492 taxable_base_amount ,
493 withholding_amount ,
494 status ,
495 created_by ,
496 creation_date ,
497 last_updated_by ,
498 last_update_date ,
499 last_update_login ,
500 request_id ,
501 program_application_id ,
502 program_id ,
503 program_update_date ,
504 checkrun_id,
505 legal_entity_id,
506 payment_instruction_id,
507 org_id)
508 VALUES(
509 v_table_id ,
510 v_next_certificate_number ,
511 v_bank_account_num ,
512 v_payment_document_name ,
513 v_check_number ,
514 v_location_id ,
515 v_withholding_date ,
516 v_awt_type_code ,
517 v_tax_name ,
518 v_vendor_id ,
519 v_checkrun_name ,
520 v_credit_amount ,
521 v_taxable_base_amount ,
522 v_withholding_amount ,
523 'NOT PRINTED' ,
524 fnd_global.user_id ,
525 SYSDATE ,
526 fnd_global.user_id ,
527 SYSDATE ,
528 fnd_global.login_id ,
529 fnd_global.conc_request_id ,
530 fnd_global.prog_appl_id ,
531 fnd_global.conc_program_id ,
532 SYSDATE,
533 v_checkrun_id,
534 v_legal_entity_id,
535 v_payment_instruction_id,
536 x_org_id ); --bug 14798548
537
538
539
540 -- Logging Infra: Procedure level
541 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
542 l_log_msg := l_procedure_name||'(-)';
543 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
544 END IF;
545 -- Logging Infra: Procedure level
546
547 RETURN (TRUE);
548
549 EXCEPTION
550 WHEN column_required THEN
551 p_errmsg := 'Column constraint NOT NULL in the column '
552 ||v_column_required||' - Error in INS_INTO_JL_AR_AP_AWT_CERTIF';
553
554 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
555 l_log_msg := 'EXCEPTION: '||p_errmsg;
556 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
557 END IF;
558
559 RETURN(FALSE);
560
561 WHEN OTHERS THEN
562 p_errmsg := SUBSTR(SQLERRM, 1, 100);
563 p_errmsg := p_errmsg||' - Error in INS_INTO_JL_AR_AP_AWT_CERTIF';
564 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
565 l_log_msg := 'EXCEPTION: '||p_errmsg;
566 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
567 END IF;
568
569 RETURN(FALSE);
570 END;
571
572 ------------------------------------------------------------------------------------------
573
574 FUNCTION SAME_PAYMENT_TAX_NAME_VENDOR(
575 v_prev_bank_account_num IN VARCHAR2 ,
576 v_prev_pay_document_name IN VARCHAR2 ,
577 v_prev_check_number IN NUMBER ,
578 v_prev_tax_name IN VARCHAR2 ,
579 v_prev_vendor_id IN NUMBER ,
580 v_tax_name IN VARCHAR2 ,
581 v_vendor_id IN NUMBER ,
582 v_bank_account_num IN VARCHAR2 ,
583 v_pay_document_name IN VARCHAR2 ,
584 v_check_number IN NUMBER )
585
586
587 RETURN BOOLEAN
588 IS
589
590 -- Logging Infra
591 l_procedure_name CONSTANT VARCHAR2(30) := 'SAME_PAYMENT_TAX_NAME_VENDOR';
592 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
593 -- Logging Infra
594
595 BEGIN
596 -- RG
597 -- IF (v_prev_tax_name is null and v_prev_vendor_id is null) then
598 -- v_prev_tax_name:= v_tax_name;
599 -- v_prev_vendor_id:=v_vendor_id;
600 -- v_prev_bank_account_num:=v_bank_account_num;
601 -- v_prev_pay_document_name:=v_pay_document_name;
602 -- v_prev_check_number:=v_check_number;
603 -- RETURN(TRUE) ;
604 -- else
605
606 -- Logging Infra: Procedure level
607 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
608 l_log_msg := l_procedure_name||'(+)';
609 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
610 END IF;
611 -- Logging Infra: Procedure level
612
613 -- Logging Infra: Statement level
614 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
615 l_log_msg := 'Parameters ';
616 l_log_msg := l_log_msg||'v_tax_name: '||v_tax_name;
617 l_log_msg := l_log_msg||'v_vendor_id: '||v_vendor_id;
618 l_log_msg := l_log_msg||'v_bank_account_num: '||v_bank_account_num;
619 l_log_msg := l_log_msg||'v_pay_document_name: '||v_pay_document_name;
620 l_log_msg := l_log_msg||'v_check_number: '||v_check_number;
621 l_log_msg := l_log_msg||'v_prev_tax_name: '||v_prev_tax_name;
622 l_log_msg := l_log_msg||'v_prev_vendor_id: '||v_prev_vendor_id;
623 l_log_msg := l_log_msg||'v_prev_bank_account_num: '||v_prev_bank_account_num;
624 l_log_msg := l_log_msg||'v_prev_pay_document_name: '||v_prev_pay_document_name;
625 l_log_msg := l_log_msg||'v_prev_check_number: '||v_prev_check_number;
626 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
627 END IF;
628 -- Logging Infra: Statement level
629
630
631 IF (v_tax_name = v_prev_tax_name)
632 and (v_vendor_id = v_prev_vendor_id)
633 and (v_bank_account_num = v_prev_bank_account_num)
634 and (v_pay_document_name = v_prev_pay_document_name)
635 and (v_check_number = v_prev_check_number)
636 THEN
637
638 -- Logging Infra: Procedure level
639 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
640 l_log_msg := l_procedure_name||'+TRUE+(-)';
641 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
642 END IF;
643 -- Logging Infra: Procedure level
644
645 RETURN(TRUE) ;
646 ELSE
647
648 -- Logging Infra: Procedure level
649 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
650 l_log_msg := l_procedure_name||'+FALSE+(-)';
651 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
652 END IF;
653 -- Logging Infra: Procedure level
654
655 RETURN(FALSE) ;
656 END IF;
657 -- end if;
658 END;
659
660 ------------------------------------------------------------------------------------------
661
662 FUNCTION GEN_CERTIFICATE_NUMBER (
663 p_awt_type_code IN VARCHAR2,
664 p_next_certificate_number IN OUT NOCOPY NUMBER)
665
666 RETURN BOOLEAN
667 IS
668 max_number NUMBER ;
669 /* bug 14798548 - will use x_org_id var now
670 v_org_id NUMBER; -- bug11792095 */
671
672 -- Logging Infra
673 l_procedure_name CONSTANT VARCHAR2(30) := 'GEN_CERTIFICATE_NUMBER';
674 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
675 -- Logging Infra
676
677 BEGIN
678
679
680 -- Logging Infra: Procedure level
681 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
682 l_log_msg := l_procedure_name||'(+)';
683 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
684 END IF;
685 -- Logging Infra: Procedure level
686
687
688 -- Logging Infra: Statement level
689 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
690 l_log_msg := 'Parameters ';
691 l_log_msg := l_log_msg||'P_awt_type_code: '||p_awt_type_code;
692 l_log_msg := l_log_msg||'IN p_next_certificate_number: '||p_next_certificate_number;
693 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
694 END IF;
695 -- Logging Infra: Statement level
696
697 -- bug11792095:Added variable v_org_id and it is used in query below
698 --bug 14798548 v_org_id := MO_GLOBAL.get_current_org_id;
699
700 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
701 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, 'x_org_id = '||x_org_id); --bug 14798548
702 END IF;
703
704
705
706 SELECT jaaac1.certificate_number
707 INTO max_number
708 FROM jl_ar_ap_awt_certif_all jaaac1
709 WHERE jaaac1.awt_type_code = p_awt_type_code
710 AND jaaac1.org_id = x_org_id --bug 14798548
711 AND jaaac1.certificate_number = (select max(jaaac2.certificate_number)
712 from jl_ar_ap_awt_certif_all jaaac2
713 where jaaac2.awt_type_code = p_awt_type_code
714 and jaaac2.org_id = x_org_id ) --bug 14798548
715 FOR UPDATE;
716
717
718 IF max_number IS NULL
719 THEN
720 p_next_certificate_number := 1;
721 ELSE
722 p_next_certificate_number := max_number + 1;
723 END IF ;
724
725 -- Logging Infra: Statement level
726 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
727 l_log_msg := 'OUT P_next_certificate_number = '||p_next_certificate_number;
728 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
729 END IF;
730 -- Logging Infra: Statement level
731
732
733 -- Logging Infra: Procedure level
734 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
735 l_log_msg := l_procedure_name||'(-)';
736 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
737 END IF;
738 -- Logging Infra: Procedure level
739
740
741 RETURN (TRUE);
742
743 EXCEPTION
744
745 WHEN NO_DATA_FOUND THEN
746
747 p_next_certificate_number := 1;
748
749 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
750 l_log_msg := 'EXCEPTION: P_next_certificate_number = 1';
751 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
752 END IF;
753
754 RETURN (TRUE);
755
756 WHEN OTHERS THEN
757 p_errmsg := SUBSTR(SQLERRM, 1, 100);
758 p_errmsg := p_errmsg||' - Error in GEN_CERTIFICATE_NUMBER';
759
760 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
761 l_log_msg := 'EXCEPTION: '||p_errmsg;
762 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
763 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, ':EXOUT P_next_certificate_number = '||p_next_certificate_number);
764 END IF;
765
766
767 RETURN(FALSE);
768 END;
769
770
771 ------------------------------------------------------------------------------------------
772
773 FUNCTION CALCULATE_CREDIT_AMOUNT(
774 v_vendor_id IN NUMBER ,
775 v_awt_type_code IN VARCHAR2 ,
776 v_tax_name IN VARCHAR2 ,
777 v_payment_identification IN NUMBER ,
778 v_payment_type IN VARCHAR2 ,
779 v_credit_amount IN OUT NOCOPY NUMBER)
780 RETURN BOOLEAN
781 IS
782 v_amount NUMBER;
783
784 -- Logging Infra
785 l_procedure_name CONSTANT VARCHAR2(30) := 'CALCULATE_CREDIT_AMOUNT';
786 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
787 -- Logging Infra
788
789 BEGIN
790
791 -- Logging Infra: Procedure level
792 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
793 l_log_msg := l_procedure_name||'(+)';
794 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
795 END IF;
796 -- Logging Infra: Procedure level
797
798
799 --- Initialize Variables ---
800 v_credit_amount := 0;
801
802
803 -- Logging Infra: Statement level
804 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
805 l_log_msg := 'Parameters ';
806 l_log_msg := l_log_msg||'v_vendor_id: '||v_vendor_id;
807 l_log_msg := l_log_msg||'v_awt_type_code: '||v_awt_type_code;
808 l_log_msg := l_log_msg||'v_tax_name: '||v_tax_name;
809 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
810 END IF;
811 -- Logging Infra: Statement level
812
813
814 SELECT (jlts.calc_wh_amnt - jlts.act_wheld_amnt)
815 INTO v_amount
816 FROM jl_ar_ap_sup_awt_cr_lts jlts,
817 ap_tax_codes atc
818 WHERE
819 jlts.po_vendor_id = v_vendor_id
820 and jlts.awt_type_code = v_awt_type_code
821 and jlts.tax_id = atc.tax_id
822 and status = 'AA'
823 -- and For KI uptake
824 -- (( v_payment_type = 'BATCH'
825 -- and selected_check_id = v_payment_identification ) --this condition is NA but will leave it
826 -- or ( v_payment_type = 'QUICK'
827 -- and check_id = v_payment_identification ))
828 and atc.name = v_tax_name;
829
830 v_credit_amount := v_amount;
831
832 -- Logging Infra: Statement level
833 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
834 l_log_msg := 'v_credit_amount = '||v_credit_amount;
835 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
836 END IF;
837 -- Logging Infra: Statement level
838
839 -- Logging Infra: Procedure level
840 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
841 l_log_msg := l_procedure_name||'(-)';
842 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
843 END IF;
844 -- Logging Infra: Procedure level
845
846 RETURN (TRUE);
847
848
849 EXCEPTION
850
851 WHEN NO_DATA_FOUND THEN
852
853 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
854 l_log_msg := 'EXCEPTION: No data found - returning TRUE ';
855 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
856 END IF;
857
858 RETURN (TRUE);
859
860 WHEN OTHERS THEN
861 p_errmsg := SUBSTR(SQLERRM, 1, 100);
862 p_errmsg := p_errmsg||' - Error in GEN_CERTIFICATE_NUMBER';
863
864 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
865 l_log_msg := 'EXCEPTION: '||p_errmsg;
866 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
867 END IF;
868
869 RETURN(FALSE);
870 END;
871
872 --------------------------------------------------------------------------------
873 --------------------------------- MAIN CODE ------------------------------------
874 --------------------------------------------------------------------------------
875
876
877 BEGIN
878
879
880 -- Logging Infra
881 -- l_procedure_name := 'JL_AR_AP_GEN_CERTIFICATES';
882 -- Logging Infra
883
884
885 --- Initialize Variables ---
886 v_prev_tax_name := NULL ;
887 v_prev_vendor_id := NULL ;
888 v_total_wh_amount := 0 ;
889 v_prev_awt_type_code := NULL ;
890 v_prev_tax_name := NULL ;
891 v_prev_checkrun_name := NULL ;
892 v_prev_checkrun_id := NULL ;
893 v_prev_withholding_date := NULL ;
894 v_prev_bank_account_num := NULL ;
895 v_prev_pay_document_name := NULL ;
896 v_prev_check_number := 0 ;
897 v_prev_payment_type := NULL ;
898 v_prev_payment_identification := 0 ;
899 v_prev_legal_entity_id := NULL;
900 v_prev_payment_instruction_id :=NULL;
901
902 v_next_certificate_number := 0 ;
903 v_total_wh_amount := 0 ;
904 v_total_tba := 0 ;
905 --
906
907 -- Logging Infra: Procedure level
908 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
909 l_log_msg := l_procedure_name||'(+)';
910 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
911 END IF;
912 -- Logging Infra: Procedure level
913
914
915 -- Logging Infra: Statement level
916 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
917 l_log_msg := 'Parameters ';
918 l_log_msg := l_log_msg||'P_payment_instruction_id:'||p_payment_instruction_id;
919 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
920 END IF;
921 -- Logging Infra: Statement level
922
923
924 IF (p_payment_instruction_id is not null ) THEN
925
926 -- Retrieving LOCATION_ID
927 -- Bug 10157541 Start
928 -- v_location_id := jg_zz_company_info.get_location_id;
929 For C_Location_Rec in C_Location Loop
930 v_location_id := C_Location_Rec.location_id;
931 End Loop;
932 -- Bug 10157541 End
933
934 -- Logging Infra: Statement level
935 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
936 l_log_msg := 'v_location_id:'||v_location_id;
937 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
938 END IF;
939 -- Logging Infra: Statement level
940
941 -- l_debug_info := 'Selecting Certificates ...';
942 -- IF (DEBUG_Var = 'Y') THEN
943 -- JL_ZZ_AP_EXT_AWT_UTIL.Debug (l_debug_info);
944 -- END IF;
945
946 -- Logging Infra: Statement level
947 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
948 l_log_msg := 'Selecting Certificates...';
949 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
950 END IF;
951 -- Logging Infra: Statement level
952
953
954 FOR rec_batch IN cur_certificates (p_payment_instruction_id) LOOP
955
956 x_org_id := rec_batch.org_id; --bug 14798548
957
958 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Checkrun_id:'||rec_batch.checkrun_id);
959 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Check_number:'||rec_batch.check_number);
960 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('x_org_id :'||rec_batch.org_id);
961
962 -- Logging Infra: Statement level
963 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
964 l_log_msg := 'Selected Certificate Check: '||rec_batch.check_number;
965 l_log_msg := l_log_msg||' Type: '||rec_batch.awt_type_code;
966 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
967 END IF;
968 -- Logging Infra: Statement level
969
970 -- l_debug_info := 'Selected Certificate Check:' ||
971 -- to_char(rec_batch.check_number) || ' Type:' || rec_batch.awt_type_code;
972 -- Debug Information
973 -- IF (DEBUG_Var = 'Y') THEN
974 -- JL_ZZ_AP_EXT_AWT_UTIL.Debug (l_debug_info);
975 -- END IF;
976 -- End Debug
977
978 IF SAME_PAYMENT_TAX_NAME_VENDOR (
979 v_prev_bank_account_num,
980 v_prev_pay_document_name,
981 v_prev_check_number,
982 v_prev_tax_name,
983 v_prev_vendor_id,
984 rec_batch.tax_name,
985 rec_batch.vendor_id,
986 rec_batch.bank_account_num,
987 rec_batch.payment_document_name,
988 rec_batch.check_number)
989 THEN
990 IF rec_batch.base_withholding_amount is null
991 THEN
992 v_total_wh_amount := v_total_wh_amount +
993 rec_batch.withholding_amount;
994 ELSE
995 v_total_wh_amount := v_total_wh_amount +
996 rec_batch.base_withholding_amount;
997 END IF;
998 v_total_tba := v_total_tba + rec_batch.taxable_base_amount;
999
1000 -- Logging Infra: Statement level
1001 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1002 l_log_msg := 'Parameters ';
1003 l_log_msg := l_log_msg||'v_total_tba: '||v_total_tba;
1004 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1005 END IF;
1006 -- Logging Infra: Statement level
1007
1008
1009 ELSE
1010
1011 -- Logging Infra: Statement level
1012 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1013 l_log_msg := 'Parameters ';
1014 l_log_msg := l_log_msg||'v_prev_tax_name: '||v_prev_tax_name;
1015 l_log_msg := l_log_msg||'v_prev_vendor_id: '||v_prev_vendor_id;
1016 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1017 END IF;
1018 -- Logging Infra: Statement level
1019
1020 IF (v_prev_tax_name IS NOT NULL)
1021 and (v_prev_vendor_id IS NOT NULL)
1022 THEN
1023 v_return := GEN_CERTIFICATE_NUMBER (
1024 v_prev_awt_type_code ,
1025 v_next_certificate_number) ;
1026
1027 -- check_return (v_return);
1028
1029 v_return := CALCULATE_CREDIT_AMOUNT(
1030 v_prev_vendor_id ,
1031 v_prev_awt_type_code ,
1032 v_prev_tax_name ,
1033 v_prev_payment_identification ,
1034 v_prev_payment_type ,
1035 v_credit_amount);
1036
1037 -- check_return (v_return);
1038
1039 v_return := INS_INTO_JL_AR_AP_AWT_CERTIF(
1040 v_next_certificate_number ,
1041 v_prev_bank_account_num ,
1042 v_prev_pay_document_name ,
1043 v_prev_check_number ,
1044 v_location_id ,
1045 v_prev_awt_type_code ,
1046 v_prev_tax_name ,
1047 v_prev_vendor_id ,
1048 v_prev_checkrun_name ,
1049 v_total_wh_amount ,
1050 v_total_tba ,
1051 v_prev_withholding_date ,
1052 v_credit_amount,
1053 v_prev_checkrun_id,
1054 v_prev_legal_entity_id,
1055 v_prev_payment_instruction_id );
1056
1057 -- check_return (v_return);
1058
1059 END IF;
1060
1061
1062 ---Initialize Variables for sum New TAX_NAME and SUPPLIER---
1063
1064
1065 v_prev_bank_account_num := rec_batch.bank_account_num ;
1066 v_prev_pay_document_name := rec_batch.payment_document_name;
1067 v_prev_check_number := rec_batch.check_number ;
1068 v_prev_tax_name := rec_batch.tax_name ;
1069 v_prev_vendor_id := rec_batch.vendor_id ;
1070 v_prev_awt_type_code := rec_batch.awt_type_code ;
1071 v_prev_tax_name := rec_batch.tax_name ;
1072 v_prev_checkrun_name := rec_batch.checkrun_name ;
1073 v_prev_withholding_date := rec_batch.withholding_date ;
1074 v_total_tba := rec_batch.taxable_base_amount;
1075 v_prev_payment_type := rec_batch.payment_type ;
1076 v_prev_payment_identification := rec_batch.payment_identification;
1077 v_prev_checkrun_id := rec_batch.checkrun_id;
1078 v_prev_legal_entity_id := rec_batch.legal_entity_id;
1079 v_prev_payment_instruction_id := p_payment_instruction_id;
1080
1081
1082
1083 -- Logging Infra: Statement level
1084 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1085 l_log_msg := 'Parameters ';
1086 l_log_msg := l_log_msg||'rec_batch.base_withholding_amount: '||rec_batch.base_withholding_amount;
1087 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1088 END IF;
1089 -- Logging Infra: Statement level
1090
1091 IF rec_batch.base_withholding_amount is null
1092 THEN
1093 v_total_wh_amount := rec_batch.withholding_amount;
1094 ELSE
1095 v_total_wh_amount := rec_batch.base_withholding_amount;
1096 END IF;
1097
1098 -- Logging Infra: Statement level
1099 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1100 l_log_msg := 'v_total_wh_amount = '||v_total_wh_amount;
1101 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1102 END IF;
1103 -- Logging Infra: Statement level
1104
1105 END IF;
1106
1107 END LOOP;
1108
1109
1110 --
1111 -- INSERT THE LAST LINE
1112 --
1113
1114
1115 -- Logging Infra: Statement level
1116 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1117 l_log_msg := 'Inserting last line... ';
1118 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1119 END IF;
1120 -- Logging Infra: Statement level
1121
1122 IF (v_prev_tax_name IS NOT NULL)
1123 and (v_prev_vendor_id IS NOT NULL)
1124 THEN
1125
1126 v_return := GEN_CERTIFICATE_NUMBER (
1127 v_prev_awt_type_code ,
1128 v_next_certificate_number);
1129
1130 -- check_return (v_return);
1131
1132
1133 v_return := CALCULATE_CREDIT_AMOUNT(
1134 v_prev_vendor_id ,
1135 v_prev_awt_type_code ,
1136 v_prev_tax_name ,
1137 v_prev_payment_identification ,
1138 v_prev_payment_type ,
1139 v_credit_amount);
1140
1141 -- check_return (v_return);
1142
1143
1144 v_return := INS_INTO_JL_AR_AP_AWT_CERTIF(
1145 v_next_certificate_number ,
1146 v_prev_bank_account_num ,
1147 v_prev_pay_document_name ,
1148 v_prev_check_number ,
1149 v_location_id ,
1150 v_prev_awt_type_code ,
1151 v_prev_tax_name ,
1152 v_prev_vendor_id ,
1153 v_prev_checkrun_name ,
1154 v_total_wh_amount ,
1155 v_total_tba ,
1156 v_prev_withholding_date ,
1157 v_credit_amount,
1158 v_prev_checkrun_id,
1159 v_prev_legal_entity_id,
1160 v_prev_payment_instruction_id );
1161
1162 -- check_return (v_return);
1163
1164 p_errmsg := ' All the certificates have been generated correctly ';
1165
1166 -- Logging Infra: Procedure level
1167 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1168 l_log_msg := l_procedure_name||'+Certificates generated+(-)';
1169 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1170 END IF;
1171 -- Logging Infra: Procedure level
1172
1173 RETURN(TRUE);
1174 ELSE
1175 p_errmsg := ' Certificates have not been generated';
1176
1177 -- Logging Infra: Procedure level
1178 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1179 l_log_msg := l_procedure_name||'+Certificates NOT generated+(-)';
1180 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1181 END IF;
1182 -- Logging Infra: Procedure level
1183
1184 RETURN(TRUE);
1185 END IF;
1186
1187 ELSE
1188 p_errmsg := ' The parameter is null ';
1189
1190 -- Logging Infra: Procedure level
1191 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1192 l_log_msg := l_procedure_name||'+Parameter is NULL+Return FALSE+(-)';
1193 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1194 END IF;
1195 -- Logging Infra: Procedure level
1196
1197 RETURN(FALSE);
1198 END IF;
1199
1200
1201 EXCEPTION
1202 WHEN NO_DATA_FOUND THEN
1203
1204 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1205 l_log_msg := 'EXCEPTION: No data found - returning TRUE.';
1206 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1207 END IF;
1208
1209 RETURN(TRUE);
1210 WHEN OTHERS THEN
1211 p_errmsg := SUBSTR(SQLERRM, 1, 100);
1212 p_errmsg := p_errmsg||' - Package JL_AR_AP_GEN_CERTIFICATES ';
1213 -- R12 Cannot have this rollback.
1214 --rollback;
1215 -- Debug Information
1216 -- IF (DEBUG_Var = 'Y') THEN
1217 -- JL_ZZ_AP_EXT_AWT_UTIL.Debug ('CERTIFICATES ERROR: '||SQLERRM);
1218 -- END IF;
1219 -- end debug
1220
1221 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1222 l_log_msg := 'EXCEPTION: '||p_errmsg;
1223 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1224 END IF;
1225
1226 RETURN(FALSE);
1227
1228 END JL_AR_AP_GEN_CERTIFICATES;
1229
1230 END JL_AR_AP_AWT_REPORTS_PKG;