1 PACKAGE BODY FV_IPAC_AUTOPAYMENTS_PKG AS
2 /* $Header: FVIPAPMB.pls 120.13 2011/09/12 09:31:29 amaddula ship $*/
3 -- -----------------------------------------------------------------
4 -- Global Variable Declarations
5 -- ------------------------------------------------------------------
6 -- g_debug_flag VARCHAR2(1) := NVL(Fnd_Profile.Value('FV_DEBUG_FLAG'),'N');
7 g_module_name VARCHAR2(100) := 'fv.plsql.FV_IPAC_AUTOPAYMENTS_PKG.';
8 g_errbuf VARCHAR2(1000);
9 g_retcode NUMBER;
10 g_batch_name Ap_Batches_All.batch_name%TYPE;
11 -- g_document_id Ap_Inv_Selection_Criteria_All.check_stock_id%TYPE;
12 -- TC Obsoletion
13 -- g_tran_code Gl_Ussgl_Transaction_Codes.ussgl_transaction_code%TYPE;
14 g_sob_id Gl_Sets_Of_Books.set_of_books_id%TYPE;
15 g_batch_id Ap_Batches_All.batch_id%TYPE;
16 g_payment_bank_acct_id NUMBER;
17 g_payment_profile_id NUMBER;
18 g_payment_document_id NUMBER;
19 g_org_id NUMBER;
20 -- ------------------------------------------------------------------
21 -- Procedure Main
22 -- ------------------------------------------------------------------
23 -- Main procedure that is called from the IPAC Disbursement Process.
24 -- This procedure calls all the subsequent procedures in the
25 -- Automatic Payments process.
26 --
27 -- Parameters:
28 -- x_errbuf: Output variable for error messages from the Main process.
29 --
30 -- x_retcode: Output variable for the return code from the Main process.
31 --
32 -- p_batch_name: Batch name that is passed to this process.
33 -- Used for picking up the invoices for validation.
34 --
35 -- p_document_id: The check stock id that needs to be passed to the
36 -- pay in full API.
37 --
38 -- ------------------------------------------------------------------
39 PROCEDURE Main( x_errbuf OUT NOCOPY VARCHAR2,
40 x_retcode OUT NOCOPY NUMBER,
41 p_batch_name VARCHAR2,
42 p_payment_bank_acct_id IN NUMBER,
43 p_payment_profile_id IN NUMBER,
44 p_payment_document_id IN NUMBER,
45 p_org_id IN NUMBER,
46 p_set_of_books_id IN NUMBER
47 -- p_document_id IN NUMBER
48 ) IS
49 l_module_name VARCHAR2(200) := g_module_name || 'Main';
50 BEGIN
51 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
52 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Start Automatic Payments Process.....');
53 END IF;
54
55 -- Assign initial values
56 g_errbuf := NULL;
57 g_retcode := 0;
58 g_batch_name := p_batch_name;
59 g_payment_bank_acct_id := p_payment_bank_acct_id;
60 g_payment_profile_id := p_payment_profile_id;
61 g_payment_document_id := p_payment_document_id;
62 -- g_document_id := p_document_id;
63 -- TC Obsoletion
64 -- g_tran_code := p_tran_code;\
65 g_org_id := p_org_id;
66 g_sob_id := p_set_of_books_id;
67 x_errbuf := NULL;
68 x_retcode := 0;
69
70 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
71 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'The parameters passed to the process are: ');
72 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Batch Name: '||g_batch_name);
73 -- FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Transaction Code: '||g_tran_code);
74 END IF;
75
76 -- Derive the Batch Id and Sob
77 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
78 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Deriving the Batch Id and Set Of Books Id');
79 END IF;
80
81 Get_Required_Parameters;
82
83 IF (g_retcode = 0) THEN
84 -- Validate the invoices
85 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
86 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Validating the Invoices');
87 END IF;
88 Validate_Invoices;
89 END IF;
90
91 IF g_retcode <> 0 THEN
92 -- Check for errors
93 x_errbuf := g_errbuf;
94 x_retcode := g_retcode;
95 ROLLBACK;
96 ELSE
97 COMMIT;
98 x_retcode := 0;
99 x_errbuf := '** Automatic Payments Process completed successfully **';
100 END IF;
101
102 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
103 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'End Automatic Payments Process.....');
104 END IF;
105
106 EXCEPTION
107 WHEN OTHERS THEN
108 x_errbuf := SQLERRM || ' -- Error in Main Procedure.';
109 x_retcode := 2;
110 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_errbuf) ;
111 END Main;
112
113 -- ------------------------------------------------------------------
114 -- Procedure Get_Required_Parameters
115 -- ------------------------------------------------------------------
116 -- Get_Required_Parameters procedure is called from Main procedure.
117 -- It gets the sob and the batch_id.
118 -- ------------------------------------------------------------------
119 PROCEDURE Get_Required_Parameters IS
120 l_module_name VARCHAR2(200) := g_module_name || 'Get_Required_Parameters';
121 -- l_operating_unit NUMBER;
122 l_ledger_name Gl_ledgers_public_v.name%TYPE;
123 BEGIN
124 -- Get the Operating Unit
125 -- l_operating_unit := mo_global.get_current_org_id;
126 -- Get the Sob
127
128 mo_utils.get_ledger_info(g_org_id, g_sob_id, l_ledger_name);
129
130 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
131 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, ' Set of Books Id : '||TO_CHAR(g_sob_id));
132 END IF;
133
134 -- Get the Batch Id
135 BEGIN
136 -- Getting the batch id from the multi-org view ap_batches,
137 -- since the uniqueness for batch name is enforced thru'
138 -- the Invoice workbench.
139 SELECT batch_id
140 INTO g_batch_id
141 FROM Ap_Batches_All
142 WHERE batch_name = g_batch_name;
143 EXCEPTION
144 WHEN NO_DATA_FOUND THEN
145 g_batch_id := NULL;
146 RETURN;
147 WHEN OTHERS THEN
148 g_errbuf := SQLERRM ||
149 ' -- Error in Get_Required_Parameters Procedure, while deriving
150 the batch id.';
151 g_retcode := 2;
152 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception1',g_errbuf) ;
153 END;
154 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
155 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, ' Batch Id : '||TO_CHAR(g_batch_id));
156 END IF;
157 EXCEPTION
158 WHEN OTHERS THEN
159 g_errbuf := SQLERRM || ' -- Error in Get_Required_Parameters Procedure.';
160 g_retcode := 2;
161 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_errbuf) ;
162 END Get_Required_Parameters;
163
164 -- ------------------------------------------------------------------
165 -- Procedure Validate_Invoices
166 -- ------------------------------------------------------------------
167 -- Validate_Invoices procedure is called from Main procedure.
168 -- This procedure is used to validate the invoices by calling the
169 -- call_approval_api procedure.
170 -- ------------------------------------------------------------------
171 PROCEDURE Validate_Invoices IS
172 l_module_name VARCHAR2(200) := g_module_name || 'Validate_Invoices';
173
174 CURSOR get_invoice_csr IS
175 SELECT invoice_id,invoice_num,invoice_date
176 FROM Ap_Invoices
177 WHERE set_of_books_id = g_sob_id
178 AND source = 'IPAC'
179 AND batch_id = g_batch_id
180 ORDER BY invoice_num;
181
182 CURSOR get_recinv_csr(inv_action VARCHAR2) IS
183 SELECT fv.invoice_id, ap.invoice_num,
184 fv.accomplish_date, ap.payment_method_lookup_code
185 FROM Fv_Ipac_Recurring_Inv fv, Ap_Invoices ap
186 WHERE fv.invoice_id = ap.invoice_id
187 AND fv.batch_name = g_batch_name
188 AND fv.invoice_action = inv_action
189 ORDER BY ap.invoice_num;
190
191 l_invoice_num Ap_Invoices_All.invoice_num%TYPE;
192 l_inv_action Fv_Ipac_Recurring_Inv.invoice_action%TYPE;
193 l_validate_flag VARCHAR2(1);
194
195 BEGIN
196 Log_Mesg('O',' Invoice Approval and Payment Output Report '||
197 'for the Batch '|| g_batch_name);
198 Log_Mesg('O',' ---------------------------------------------'||
199 '---------------------------------- ');
200 Log_Mesg('O',' ');
201 Log_Mesg('O',' ');
202 Log_Mesg('O','Invoice Number '||
203 'Approval Status '||
204 'Holds Count '||
205 'Payment Status '||
206 'Incorrect Interagency Paygroup');
207 Log_Mesg('O','-------------- '||
208 '-------------- '||
209 '----------- '||
210 '-------------- '||
211 '------------------------------ ');
212
213 FOR l_invoice_csr IN get_invoice_csr LOOP
214 l_invoice_num := l_invoice_csr.invoice_num;
215 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
216 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' Validating IPAC Invoice '||l_invoice_num);
217 END IF;
218
219 -- Call the approval api
220 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
221 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' Calling the Approval API ');
222 END IF;
223 Call_Approval_Api(l_invoice_csr.invoice_id,l_invoice_num,
224 l_invoice_csr.invoice_date,NULL,'I','Y');
225
226 IF (g_retcode <> 0) THEN
227 RETURN;
228 END IF;
229 END LOOP;
230
231 -- Call the approval api twice.
232 -- Once,for recurring invoices which need validation(when i=2).
233 -- Second time for recurring invoices which just need to be paid, and not
234 -- validated(these are already validated).
235 FOR i IN 1..2 LOOP
236 IF (i = 1) THEN
237 l_inv_action := 'P';
238 l_validate_flag := 'N';
239 ELSE
240 l_inv_action := 'V';
241 l_validate_flag := 'Y';
242 END IF;
243
244 FOR l_recinv_csr IN get_recinv_csr(l_inv_action) LOOP
245 l_invoice_num := l_recinv_csr.invoice_num;
246 IF (i = 1) THEN
247 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
248 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' Validating Recurring Invoice '||l_invoice_num);
249 END IF;
250 ELSE
251 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
252 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' Processing Recurring Invoice '||l_invoice_num);
253 END IF;
254 END IF;
255
256 -- Call the approval api
257 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
258 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' Calling the Approval API ');
259 END IF;
260 Call_Approval_Api(l_recinv_csr.invoice_id,l_invoice_num,
261 l_recinv_csr.accomplish_date,
262 l_recinv_csr.payment_method_lookup_code,
263 'R',l_validate_flag);
264
265 IF (g_retcode <> 0) THEN
266 RETURN;
267 END IF;
268 END LOOP;
269 END LOOP;
270
271 -- Call the output messages
272 Create_Output_Messages;
273
274 EXCEPTION
275 WHEN OTHERS THEN
276 g_errbuf := SQLERRM || ' -- Error in Validate_Invoices Procedure.';
277 g_retcode := 2;
278 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_errbuf) ;
279 END Validate_Invoices;
280
281
282 -- ------------------------------------------------------------------
283 -- Procedure Call_Approval_Api
284 -- ------------------------------------------------------------------
285 -- Call_Approval_Api procedure is called from Validate_Invoices procedure.
286 -- This procedure calls the ap_approval_pkg to validate.
287 -- This procedure also updates the invoice_action column in the
288 -- Fv_Ipac_Recurring_Inv table, to show that the recurring invoice has
289 -- been validated and is ready to be picked up for payments.
290 -- Parameters:
291 -- p_invoice_id: Invoice Id that needs be validated by the approval api.
292 --
293 -- p_invoice_num: Invoice number used for the purpose of showing
294 -- it in the output report.
295 --
296 -- p_invoice_date: Invoice date to be passed to the pay in full api.
297 -- This would be the gl_date for payments.
298 --
299 -- p_payment_method: Payment method used for the recurring invoices.
300 --
301 -- p_invoice_flag: Flag to indicate if the invoice is an IPAC invoice
302 -- or a recurring invoice.
303 --
304 -- p_validate_flag: Flag to indicate if the recurring invoice is
305 -- is to be validated and then paid or just paid.
306 -- ------------------------------------------------------------------
307 PROCEDURE Call_Approval_Api(p_invoice_id NUMBER,
308 p_invoice_num VARCHAR2,
309 p_invoice_date DATE,
310 p_payment_method VARCHAR2,
311 p_invoice_flag VARCHAR2,
312 p_validate_flag VARCHAR2) IS
313
314 --bnarang
315 l_api_version CONSTANT NUMBER := 1.0;
316 l_init_msg_list VARCHAR2(1);
317 l_return_status VARCHAR2(1);
318 l_msg_count NUMBER;
319 l_msg_data VARCHAR2(200);
320 l_errorIds IBY_DISBURSE_SINGLE_PMT_PKG.trxnErrorIdsTab;
321 --bnarang
322 l_module_name VARCHAR2(200) := g_module_name || 'Call_Approval_Api';
323 l_holds_count NUMBER;
324 l_approval_status VARCHAR2(25);
325 l_mesg VARCHAR(2000);
326 x_paygroup Ap_Invoices.pay_group_lookup_code%TYPE;
327 l_funds_return_code VARCHAR2(15);
328
329 BEGIN
330 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
331 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' In call approval api with p_invoice_num:'||p_invoice_num||
332 ', p_invoice_flag: '||p_invoice_flag||', p_validate_flag:'||
333 p_validate_flag);
334 END IF;
335
336 l_init_msg_list := fnd_api.g_true;
337
338 IF (p_validate_flag = 'Y') THEN
339
340 --Added parameter names in the call to Ap_Approval_Pkg.Approve
341
342 Ap_Approval_Pkg.Approve(p_run_option => '',
343 p_invoice_batch_id => '',
344 p_vendor_id => '',
345 p_pay_group => '',
346 p_invoice_id => p_invoice_id,
347 p_entered_by => '',
348 p_set_of_books_id =>'',
349 p_trace_option => '',
350 p_conc_flag => 'N',
351 p_holds_count => l_holds_count,
352 p_approval_status =>l_approval_status,
353 p_funds_return_code =>l_funds_return_code,
354 p_calling_sequence =>'FVIPAPMB'
355 );
356
357 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
358 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' Approval Status: '||l_approval_status||
359 ' Hold Count: '||TO_CHAR(l_holds_count));
360 END IF;
361 END IF;
362
363 IF ( ((p_validate_flag = 'Y') AND (l_holds_count = 0)
364 AND (l_approval_status = 'APPROVED')) OR (p_validate_flag = 'N') ) THEN --cnt
365
366 IF ((p_invoice_flag = 'R') AND (p_validate_flag = 'Y')) THEN
367 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
368 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' Updating the invoice_action in the '||
369 'Fv_Ipac_Recurring_Inv Table.');
370 END IF;
371
372 UPDATE Fv_Ipac_Recurring_Inv
373 SET invoice_action = 'P'
374 WHERE invoice_id = p_invoice_id;
375 END IF;
376
377 BEGIN
378 -- Check the payment method for recurring invoices,
379 -- if it is not 'Clearing', then put in the exception report
380 IF ((p_invoice_flag = 'R') AND (p_payment_method <> 'CLEARING')) THEN
381 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_EXCEPTION, l_module_name,' The payment method is not CLEARING '||
382 'for the invoice '||p_invoice_num||
383 '. Hence payment is not made for this invoice.');
384 GOTO end_label;
385 END IF;
386
387 -- Update the Wf Approval Status
388 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
389 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' Updating the Workflow approval status for '||
390 'the invoice '||p_invoice_num);
391 END IF;
392 Update_WfStatus(p_invoice_id);
393
394 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
395 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' Calling the pay in full API.');
396 END IF;
397
398
399 Ap_Pay_Single_Invoice_Pkg.Ap_Pay_Invoice_In_Full(
400 p_api_version => l_api_version,
401 p_init_msg_list=> l_init_msg_list,
402 p_invoice_id => p_invoice_id,
403 p_payment_type_flag => 'M',
404 p_internal_bank_acct_id => g_payment_bank_acct_id,
405 p_payment_method_code => 'CLEARING',
406 p_payment_profile_id => g_payment_profile_id,
407 p_payment_document_id => g_payment_document_id,
408 p_take_discount => '',
409 p_check_date => p_invoice_date,
410 p_doc_category_code => '',
411 p_exchange_rate_type => '',
412 p_exchange_rate => '',
413 p_exchange_date => '',
414 x_return_status => l_return_status ,
415 x_msg_count => l_msg_count ,
416 x_msg_data => l_msg_data ,
417 x_errorIds => l_errorIds );
418
419 /* Ap_Pay_Single_Invoice_Pkg.Ap_Pay_Invoice_In_Full(
420 p_invoice_id,
421 'M',
422 g_document_id,
423 '',
424 --g_tran_code, --Bug#4574367
425 --'', --Bug#4574367
426 p_invoice_date,
427 '',
428 '',
429 '',
430 '',
431 '',
432 'FVIPAPMB');
433 */
434 EXCEPTION
435 WHEN OTHERS THEN
436 l_mesg := Fnd_Message.Get||SQLERRM;
437 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,' In the exception of the pay in full API with '||
438 'the mesg '||l_mesg);
439 Log_Mesg('O',RPAD(NVL(p_invoice_num,' '),50,' ')
440 ||RPAD(NVL(l_approval_status,' '),25,' ')
441 ||RPAD(NVL(TO_CHAR(l_holds_count),' '),19,' ')
442 ||l_mesg);
443 RETURN;
444 END;
445
446 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
447 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' Inserting the transaction into the '||
448 'Fv_Interagency_Funds Table.');
449 END IF;
450 -- Call Insert_IA_Txns Procedure
451 Insert_IA_Txns(p_invoice_id,p_invoice_num,x_paygroup);
452
453 --Bug 12752034: If Ap_Pay_Single_Invoice_Pkg.Ap_Pay_Invoice_In_Full does
454 -- not create payment, then Payment status column on Invoice approval
455 -- and Payment output report should contain the correct value.
456 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
457 Log_Mesg('O',RPAD(NVL(p_invoice_num,' '),50,' ')
458 ||RPAD(NVL(l_approval_status,' '),25,' ')
459 ||RPAD(NVL(TO_CHAR(l_holds_count),' '),19,' ')
460 ||RPAD('Payment not created',50,' ')
461 ||NVL(x_paygroup,' '));
462 ELSE
463 Log_Mesg('O',RPAD(NVL(p_invoice_num,' '),50,' ')
464 ||RPAD(NVL(l_approval_status,' '),25,' ')
465 ||RPAD(NVL(TO_CHAR(l_holds_count),' '),19,' ')
466 ||RPAD('Payment Successfully Created',50,' ')
467 ||NVL(x_paygroup,' '));
468 END IF;
469
470 IF (g_retcode <> 0) THEN
471 RETURN;
472 END IF;
473 ELSE --cnt
474 -- When an invoice has a hold placed on it
475 Log_Mesg('O',RPAD(NVL(p_invoice_num,' '),50,' ')
476 ||RPAD(NVL(l_approval_status,' '),25,' ')
477 ||RPAD(NVL(TO_CHAR(l_holds_count),' '),19,' ')
478 ||'INVOICE_NOT_APPROVED');
479 END IF; --cnt
480 GOTO end_label1;
481
482 <<end_label>>
483 Log_Mesg('O',RPAD(NVL(p_invoice_num,' '),50,' ')
484 ||RPAD(NVL(l_approval_status,' '),25,' ')
485 ||RPAD(NVL(TO_CHAR(l_holds_count),' '),19,' ')
486 ||'PAYMENT_METHOD_NOT_CLEARING');
487
488 <<end_label1>>
489 NULL;
490 EXCEPTION
491 WHEN OTHERS THEN
492 g_errbuf := SQLERRM || ' -- Error in Call_Approval_Api Procedure.';
493 g_retcode := 2;
494 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_errbuf) ;
495 END Call_Approval_Api;
496
497 -- ------------------------------------------------------------------
498 -- Procedure Insert_IA_Txns
499 -- ------------------------------------------------------------------
500 -- Insert_IA_Txns procedure is called from Call_Approval_Api procedure.
501 -- This procedure inserts a record in Fv_Interagency_Funds table
502 -- for all the IPAC txn's and recurring txn's.
503 -- Parameters:
504 -- p_invoice_id: Invoice Id that needs be inserted into the table.
505 --
506 -- p_invoice_num: Invoice number that needs be inserted into the table.
507 --
508 -- x_paygroup: This is an out variable which holds the paygroup of
509 -- a recurring invoice that is different from Interagency
510 -- paygroup. It is shown in the exception report as an
511 -- incorrect paygroup.
512 -- ------------------------------------------------------------------
513 PROCEDURE Insert_IA_Txns(p_invoice_id NUMBER,
514 p_invoice_num VARCHAR2,
515 x_paygroup OUT NOCOPY VARCHAR2) IS
516 l_module_name VARCHAR2(200) := g_module_name || 'Insert_IA_Txns';
517 l_vendor_id Ap_Invoices.vendor_id%TYPE;
518 l_vendor_name Po_Vendors.vendor_name%TYPE;
519 l_date Ap_Invoices.creation_date%TYPE;
520 l_count NUMBER := 0;
521 BEGIN
522 -- Check if the paygroup matches with the paygroup in Federal options.
523 BEGIN
524 SELECT COUNT(*)
525 INTO l_count
526 FROM Fv_Operating_Units fo,Ap_Invoices ai
527 WHERE ai.invoice_id = p_invoice_id
528 AND fo.set_of_books_id = g_sob_id
529 AND fo.payables_ia_paygroup = ai.pay_group_lookup_code;
530 EXCEPTION
531 WHEN OTHERS THEN
532 g_errbuf := SQLERRM ||
533 ' -- Error in Insert_IA_Txns Procedure'||
534 ' while checking for the paygroup for the invoice '||p_invoice_num;
535 g_retcode := 2;
536 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception7',g_errbuf) ;
537 END;
538
539 -- Get the vendor and creation date information for the invoice.
540 -- If the paygroup matches, then get the vendor info,
541 -- otherwise send that paygroup as an out variable to be shown in the
542 -- exception report.
543 IF (l_count > 0) THEN -- count
544
545 BEGIN
546 SELECT ai.vendor_id, pv.vendor_name, ai.creation_date
547 INTO l_vendor_id, l_vendor_name, l_date
548 FROM Ap_Invoices ai, Po_Vendors pv
549 WHERE ai.invoice_id = p_invoice_id
550 AND ai.vendor_id = pv.vendor_id
551 AND NOT EXISTS (SELECT 'X'
552 FROM Fv_Interagency_Funds
553 WHERE set_of_books_id = g_sob_id
554 AND invoice_id IS NOT NULL
555 AND invoice_id = ai.invoice_id);
556 EXCEPTION
557 WHEN NO_DATA_FOUND THEN
558 g_errbuf := SQLERRM ||
559 ' -- Error in Insert_IA_Txns Procedure (no data found)'||
560 ' while getting the vendor information for the invoice '||
561 p_invoice_num;
562 g_retcode := 2;
563 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.exception8',g_errbuf) ;
564 WHEN OTHERS THEN
565 g_errbuf := SQLERRM ||
566 ' -- Error in Insert_IA_Txns Procedure'||
567 ' while getting the vendor information for the invoice '
568 ||p_invoice_num;
569 g_retcode := 2;
570 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception9',g_errbuf) ;
571 END;
572 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
573 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' The invoice '||p_invoice_num||' has been inserted '||
574 'into the interagency table.');
575 END IF;
576 ELSE -- count
577 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_EXCEPTION, l_module_name,' The paygroup on the invoice '||p_invoice_num||
578 ' does not match to the Interagency paygroup defined '||
579 'on the Federal Options Form.');
580 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_EXCEPTION, l_module_name,' The invoice '||p_invoice_num||
581 ' has not been inserted '|| 'into the interagency table.');
582 BEGIN
583 SELECT pay_group_lookup_code
584 INTO x_paygroup
585 FROM Ap_Invoices
586 WHERE invoice_id = p_invoice_id;
587
588 EXCEPTION
589 WHEN OTHERS THEN
590 g_errbuf := SQLERRM ||
591 ' -- Error in Insert_IA_Txns Procedure'||
592 ' while deriving the paygroup for the invoice '||p_invoice_num;
593 g_retcode := 2;
594 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception10',g_errbuf) ;
595 END;
596 RETURN;
597 END IF; -- count
598
599 -- Inserting into the table.
600 INSERT INTO Fv_Interagency_Funds
601 (interagency_fund_id,
602 set_of_books_id,
603 processed_flag,
604 chargeback_flag,
605 last_update_date,
606 last_updated_by,
607 created_by,
608 creation_date,
609 vendor_id,
610 vendor_name,
611 invoice_id,
612 invoice_number,
613 org_id)
614 VALUES
615 (Fv_Interagency_Funds_S.NEXTVAL,
616 g_sob_id,
617 'N',
618 'N',
619 SYSDATE,
620 Fnd_Global.user_id,
621 Fnd_Global.user_id,
622 SYSDATE,
623 l_vendor_id,
624 l_vendor_name,
625 p_invoice_id,
626 p_invoice_num,
627 g_org_id
628 );
629
630 EXCEPTION
631 WHEN OTHERS THEN
632 g_errbuf := SQLERRM || ' -- Error in Insert_IA_Txns Procedure.';
633 g_retcode := 2;
634 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_errbuf) ;
635 END Insert_IA_Txns;
636
637
638 -- ------------------------------------------------------------------
639 -- Procedure Update_WfStatus
640 -- ------------------------------------------------------------------
641 -- Update_WfStatus procedure is called from Call_Approval_Api procedure.
642 -- This procedure is used to manually update the wfapproval_status
643 -- to be not required, so even if workflow is turned on, then we
644 -- would be able to create payments.
645 -- ------------------------------------------------------------------
646 PROCEDURE Update_WfStatus(p_invoice_id NUMBER) IS
647 l_module_name VARCHAR2(200) := g_module_name || 'Update_WfStatus';
648 BEGIN
649 UPDATE Ap_Invoices
650 SET wfapproval_status = 'NOT REQUIRED'
651 WHERE invoice_id = p_invoice_id;
652 EXCEPTION
653 WHEN OTHERS THEN
654 g_errbuf := SQLERRM || ' -- Error in Update_WfStatus Procedure.';
655 g_retcode := 2;
656 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_errbuf) ;
657 END Update_WfStatus;
658
659
660 -- ------------------------------------------------------------------
661 -- Procedure Create_Output_Messages
662 -- ------------------------------------------------------------------
663 -- Create_Output_Messages procedure is used for creating the output
664 -- message codes and thier descriptions.
665 -- ------------------------------------------------------------------
666 PROCEDURE Create_Output_Messages IS
667 l_module_name VARCHAR2(200) := g_module_name || 'Create_Output_Messages';
668 BEGIN
669 FOR i IN 1..5 LOOP
670 Log_Mesg('O',' ');
671 END LOOP;
672
673 Log_Mesg('O','Note: The invoices which are placed on hold will need to be '||
674 'manually approved.');
675 Log_Mesg('O',' Payments for these invoices will need to be '||
676 'manually created.');
677
678 FOR i IN 1..3 LOOP
679 Log_Mesg('O',' ');
680 END LOOP;
681
682 Log_Mesg('O','The following is the list of the descriptions of the '||
683 'Payment Status codes : ');
684 Log_Mesg('O',' ');
685 Log_Mesg('O','INVOICE_NOT_APPROVED - '||
686 'The invoice cannot be paid as it has holds placed '||
687 'on it and is not approved');
688 Log_Mesg('O','PAYMENT_METHOD_NOT_CLEARING - '||
689 'The Payment Method for this invoice is not CLEARING');
690 Log_Mesg('O','AP_PERIOD_NOT_OPEN - '||
691 'The GL Period is not Open');
692 Log_Mesg('O','AP_NO_USER_XRATE - '||
693 'Exchange Rate is needed if Exchange Type is USER');
694 Log_Mesg('O','AP_NO_XRATE - '||
695 'No Exchange Rate found for the Exchange Type and Date');
696 Log_Mesg('O','AP_INVOICE_CANNOT_BE_PAID - '||
697 'The invoice cannot be paid');
698 Log_Mesg('O','AP_MISMATCHED_PAYMENT_SCHEDS - '||
699 'The Payment Schedules for this '||
700 'invoice may have different payment methods');
701 Log_Mesg('O','AP_PAY_FAIL_SEL_BY_BATCH - '||
702 'The invoice is being paid by a Payment Batch');
703 Log_Mesg('O','AP_NO_VENDOR_SITE - '||
704 'The Vendor Site that was on the invoice '||
705 'does not exist or invoices cannot be paid for this Vendor or '||
706 'the Vendor has no active Pay Sites');
707 Log_Mesg('O','AP_PAY_DOCUMENT_ALREADY_IN_USE - '||
708 'The Payment Document is already '||
709 'in use and cannot be used by this invoice');
710 Log_Mesg('O','AP_PAY_DOCUMENT_BANK_INACTIVE - '||
711 'The Bank Account, Bank Branch or '||
712 'Payables Document is inactive or does not exist');
713 Log_Mesg('O','AP_PAY_NO_VENDOR - '||
714 'Cannot find the Vendor Name');
715 Log_Mesg('O','AP_SEQ_DOC_CAT_NOT_REQ - '||
716 'Cannot have a user specified Sequence '||
717 'when Sequential Numbering is not used');
718 Log_Mesg('O','AP_SEQ_NO_DOC_CAT - '||
719 'Document Category that is passed by the user '||
720 'does not exist');
721 Log_Mesg('O','AP_SEQ_DOC_NO_REQ - '||
722 'The user has passed in a Category Code when '||
723 'Payment Document Category Code override is not allowed');
724 Log_Mesg('O','AP_SEQ_DOC_CAT_NO_FOUND - '||
725 'Document Category Code is not found');
726 Log_Mesg('O','AP_SEQ_CREATE_ERROR - '||
727 'Cannot get a valid Document Sequence value');
728 Log_Mesg('O','AP_DEBUG - Generic Error Message');
729 EXCEPTION
730 WHEN OTHERS THEN
731 g_errbuf := SQLERRM || ' -- Error in Create_Output_Messages Procedure.';
732 g_retcode := 2;
733 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_errbuf) ;
734 END Create_Output_Messages;
735
736
737 -- ------------------------------------------------------------------
738 -- Procedure Log_Mesg
739 -- ------------------------------------------------------------------
740 -- Log_Mesg procedure is used for logging a debug or a log message
741 -- in the log file.
742 -- Parameters:
743 --
744 -- p_debug_flag: Indicates whether the message is a log message(L),
745 -- a debug message(D) or a output message(O).
746 -- p_message: The message that needs to be printed in the log/output.
747 -- ------------------------------------------------------------------
748 PROCEDURE Log_Mesg(p_debug_flag VARCHAR2,
749 p_message VARCHAR2) IS
750 l_module_name VARCHAR2(200) := g_module_name || 'Log_Mesg';
751 BEGIN
752 -- IF ((p_debug_flag = 'L') OR (p_debug_flag = 'D' AND g_debug_flag = 'Y')) THEN
753 -- Fnd_File.Put_Line(FND_FILE.LOG, p_message);
754 IF (p_debug_flag = 'O') THEN
755 Fnd_File.Put_Line(FND_FILE.OUTPUT, p_message);
756 END IF;
757 EXCEPTION
758 WHEN OTHERS THEN
759 g_errbuf := SQLERRM || ' -- Error in Log_Mesg Procedure.';
760 g_retcode := 2;
761 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_errbuf) ;
762 END Log_Mesg;
763
764 END Fv_Ipac_AutoPayments_Pkg;
[Home] [Help]