[Home] [Help]
PACKAGE BODY: APPS.FV_IPAC_DISBURSEMENT_PKG
Source
1 PACKAGE BODY fv_ipac_disbursement_pkg AS
2 /* $Header: FVIPDISB.pls 120.21.12010000.2 2009/02/10 07:35:35 bnarang ship $*/
3 g_module_name VARCHAR2(100);
4 g_FAILURE NUMBER;
5 g_SUCCESS NUMBER;
6 g_WARNING NUMBER;
7 g_request_id NUMBER;
8 g_user_id NUMBER;
9 g_login_id NUMBER;
10 g_org_id NUMBER;
11 g_set_of_books_id NUMBER;
12 g_status_preprocessed fv_ipac_import.record_status%TYPE;
13 g_status_imported fv_ipac_import.record_status%TYPE;
14 g_status_no_process fv_ipac_import.record_status%TYPE;
15 g_status_processed fv_ipac_import.record_status%TYPE;
16 g_status_error fv_ipac_import.record_status%TYPE;
17 g_status_other_error fv_ipac_import.record_status%TYPE;
18 g_status_ap_imported fv_ipac_import.record_status%TYPE;
19 g_ia_paygroup fv_operating_units.payables_ia_paygroup%TYPE;
20 g_enter VARCHAR2(10);
21 g_exit VARCHAR2(10);
22
23 -- variables used by "Update FV_INTERAGENCY_FUNDS Table" process
24 parm_inv_creation_date_low DATE;
25 parm_inv_creation_date_high DATE;
26 g_err_buf VARCHAR2(1024);
27 g_err_code NUMBER(15);
28
29 --****************************************************************************************--
30 --* Name : initialize_global_variables *--
31 --* Type : Procedure *--
32 --* Purpose : To initialize all global variables *--
33 --* Parameters : None *--
34 --* Global Vars : As in procedure *--
35 --* Called from : Called when initializing the package *--
36 --* Calls : mo_global.get_current_org_id *--
37 --* mo_utils.get_ledger_info *--
38 --* Tables Used : None *--
39 --* Logic : No Logic *--
40 --****************************************************************************************--
41 PROCEDURE initialize_global_variables
42 IS
43 l_ledger_name VARCHAR2(30);
44 BEGIN
45 g_module_name := 'fv.plsql.fv_ipac_disbursement_pkg.';
46 g_FAILURE := -1;
47 g_SUCCESS := 0;
48 g_WARNING := -2;
49 g_request_id := fnd_global.conc_request_id;
50 g_user_id := fnd_global.user_id;
51 g_login_id := fnd_global.login_id;
52 g_org_id := mo_global.get_current_org_id;
53 g_status_preprocessed := 'PRE_PROCESSED';
54 g_status_imported := 'IMPORTED';
55 g_status_no_process := 'NOTPROCESSED_YET';
56 g_status_processed := 'PROCESSED';
57 g_status_error := 'ERROR';
58 g_status_other_error := 'ERROR_IN_OTHER_LINES';
59 g_status_ap_imported := 'AP_IMPORTED';
60 mo_utils.get_ledger_info( g_org_id, g_set_of_books_id, l_ledger_name);
61 g_enter := 'ENTER';
62 g_exit := 'EXIT';
63 END;
64
65 --****************************************************************************************--
66 --* Name : insert_ia_recs *--
67 --* Type : Procedure *--
68 --* Purpose : Inserts record into fv_interagency_funds table *--
69 --* Parameters : inv_id *--
70 --* : inv_num *--
71 --* : ven_id *--
72 --* : ven_name *--
73 --* Global Vars : g_module_name *--
74 --* Called from : upd_ia_main *--
75 --* Calls : fv_utility.log_mesg *--
76 --* Tables Used : fv_interagency_funds INSERT *--
77 --* Logic : Inserts record into fv_interagency_funds table *--
78 --****************************************************************************************--
79 PROCEDURE insert_ia_recs
80 (
81 inv_id NUMBER ,
82 inv_num VARCHAR2 ,
83 ven_id NUMBER ,
84 ven_name VARCHAR2
85 )
86 IS
87 l_module_name VARCHAR2(200);
88
89 retcode NUMBER;
90 errbuf VARCHAR2(100);
91
92 BEGIN
93 l_module_name := g_module_name || 'insert_ia_recs';
94 g_err_buf := '';
95 g_err_code := 0;
96
97 INSERT INTO fv_interagency_funds
98 (
99 interagency_fund_id,
100 set_of_books_id,
101 processed_flag,
102 chargeback_flag,
103 last_update_date,
104 last_updated_by,
105 created_by,
106 creation_date,
107 vendor_id,
108 vendor_name,
109 invoice_id,
110 invoice_number
111 )
112 VALUES
113 (
114 fv_interagency_funds_s.nextval,
115 g_set_of_books_id,
116 'N',
117 'N',
118 SYSDATE,
119 fnd_global.user_id,
120 fnd_global.user_id,
121 SYSDATE,
122 ven_id,
123 ven_name,
124 inv_id,
125 inv_num
126 );
127 EXCEPTION
128 WHEN OTHERS THEN
129 g_err_code := SQLCODE;
130 g_err_buf := SQLERRM;
131 fv_utility.log_mesg(fnd_log.level_unexpected, l_module_name||'.final_exception',g_err_buf) ;
132 RAISE;
133 END insert_ia_recs;
134
135 --****************************************************************************************--
136 --* Name : upd_ia_main *--
137 --* Type : Procedure *--
138 --* Purpose : *--
139 --* Parameters : errbuf Error returned to the concurrent process *--
140 --* : retcode Return Code to concurrent process *--
141 --* : invoice_creation_date_low *--
142 --* : invoice_creation_date_high *--
143 --* Global Vars : g_module_name *--
144 --* : g_set_of_books_id *--
145 --* : g_err_buf *--
146 --* : g_err_code *--
147 --* : fnd_log.level_unexpected *--
148 --* : fnd_log.level_statement *--
149 --* : fnd_log.g_current_runtime_level *--
150 --* Called from : Concurrent program Update Interagency Transfers (FVIAUPDB) *--
151 --* Calls : insert_ia_recs *--
152 --* : fv_utility.debug_mesg *--
153 --* : fv_utility.log_mesg *--
154 --* : mo_global.get_current_org_id *--
155 --* : mo_utils.get_ledger_info *--
156 --* : fnd_date.canonical_to_date *--
157 --* Tables Used : ap_invoices SELECT *--
158 --* : po_vendors SELECT *--
159 --* : fv_operating_units SELECT *--
160 --* : fv_interagency_funds SELECT *--
161 --* Logic : *--
162 --****************************************************************************************--
163 PROCEDURE upd_ia_main
164 (
165 errbuf OUT NOCOPY VARCHAR2,
166 retcode OUT NOCOPY VARCHAR2,
167 invoice_creation_date_low VARCHAR2 ,
168 invoice_creation_date_high VARCHAR2
169 )
170 IS
171 l_module_name VARCHAR2(200);
172 l_ledger_name VARCHAR2(30);
173 l_org_id NUMBER;
174
175 CURSOR ia_trx_select_csr
176 (
177 p_sob_id NUMBER
178 ) IS
179 SELECT ai.invoice_id ,
180 ai.invoice_num,
181 ai.vendor_id ,
182 pv.vendor_name,
183 ai.creation_date
184 FROM ap_invoices ai , po_vendors pv
185 WHERE ai.vendor_id = pv.vendor_id
186 AND ai.invoice_num LIKE 'IPAC%'
187 AND ai.payment_method_lookup_code = 'CLEARING'
188 AND EXISTS (SELECT 'X'
189 FROM fv_operating_units
190 WHERE set_of_books_id = p_sob_id
191 AND default_alc = 'Y'
192 AND payables_ia_paygroup = ai.pay_group_lookup_code)
193 AND TO_DATE(ai.creation_date,'DD-MM-YYYY') BETWEEN
194 TO_DATE(parm_inv_creation_date_low,'DD-MM-YYYY') AND
195 TO_DATE(parm_inv_creation_date_high,'DD-MM-YYYY')
196 AND NOT EXISTS (SELECT 'X'
197 FROM fv_interagency_funds
198 WHERE set_of_books_id = p_sob_id
199 AND invoice_id IS NOT NULL
200 AND invoice_id = ai.invoice_id);
201
202 l_count NUMBER :=0;
203
204 BEGIN
205 l_module_name := g_module_name || 'upd_ia_main';
206 g_err_buf := '';
207 g_err_code := 0;
208 l_org_id := mo_global.get_current_org_id;
209 mo_utils.get_ledger_info(l_org_id, g_set_of_books_id, l_ledger_name);
210 parm_inv_creation_date_low := fnd_date.canonical_to_date(invoice_creation_date_low);
211 parm_inv_creation_date_high := fnd_date.canonical_to_date(invoice_creation_date_high);
212
213
214 FOR trx_select IN ia_trx_select_csr(g_set_of_books_id) LOOP
215 l_count := l_count+1;
216 insert_ia_recs(trx_select.invoice_id,
217 trx_select.invoice_num,
218 trx_select.vendor_id,
219 trx_select.vendor_name);
220 IF g_err_code <> 0 THEN
221 ERRBUF := g_err_buf;
222 retcode := g_err_code;
223 RETURN;
224 END IF;
225 END LOOP ;
226
227 IF l_count =0 THEN
228 errbuf := 'No Invoices found for Upload.';
229 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
230 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,errbuf);
231 END IF;
232 ELSE
233 errbuf := 'Total Number of records Uploaded : '||l_count||'.';
234 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
235 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,errbuf);
236 END IF;
237 COMMIT;
238 END IF;
239
240 EXCEPTION
241 WHEN OTHERS THEN
242 g_err_code := SQLCODE;
243 g_err_buf := SQLERRM;
244 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_err_buf) ;
245 END upd_ia_main;
246
247 --****************************************************************************************--
248 --* Name : insert_error *--
249 --* Type : Procedure *--
250 --* Purpose : This procedure inserts the import errors into the table *--
251 --* : fv_ipac_import_errors table *--
252 --* Parameters : p_ipac_import_id IN The import id *--
253 --* : p_validation_code IN The validation Code *--
254 --* : p_validation_err IN The Validation Error *--
255 --* : p_errbuf OUT Error returned to the calling process *--
256 --* : p_retcode OUT Return Code to calling process *--
257 --* Global Vars : g_module_name READ *--
258 --* : g_SUCCESS READ *--
259 --* : g_ERROR READ *--
260 --* : fnd_log.level_statement READ *--
261 --* : fnd_log.g_current_runtime_level READ *--
262 --* : fnd_log.level_unexpected READ *--
263 --* : fnd_log.level_procedure READ *--
264 --* : g_enter READ *--
265 --* : g_exit READ *--
266 --* Called from : check_for_ap_import_errors *--
267 --* : resolve_uom *--
268 --* : validate_duns *--
269 --* : validate_po *--
270 --* Calls : fv_utility.debug_mesg *--
271 --* : fv_utility.log_mesg *--
272 --* Tables Used : fv_ipac_import_errors INSERT *--
273 --* Logic : Inserts the record into table fv_ipac_import_errors *--
274 --* : using the input variables *--
275 --****************************************************************************************--
276 PROCEDURE insert_error
277 (
278 p_ipac_import_id IN fv_ipac_import_errors.ipac_import_id%TYPE,
279 p_validation_code IN fv_ipac_import_errors.error_code%TYPE,
280 p_validation_err IN fv_ipac_import_errors.error_desc%TYPE,
281 p_error_code OUT NOCOPY NUMBER,
282 p_error_desc OUT NOCOPY VARCHAR2
283 )
284 IS
285 l_module_name VARCHAR2(200);
286 l_location VARCHAR2(400);
287 BEGIN
288 l_module_name := g_module_name || 'insert_error';
289 p_error_code := g_SUCCESS;
290 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
291 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
292 END IF;
293 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
294 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_ipac_import_id = '||p_ipac_import_id);
295 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_validation_code = '||p_validation_code);
296 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_validation_err = '||p_validation_err);
297 END IF;
298
299 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
300 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Inserting into fv_ipac_import_errors');
301 END IF;
302 INSERT INTO fv_ipac_import_errors
303 (
304 ipac_import_id,
305 error_code,
306 error_desc
307 )
308 VALUES
309 (
310 p_ipac_import_id,
311 p_validation_code,
312 p_validation_err
313 );
314 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
315 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
316 END IF;
317 EXCEPTION
318 WHEN OTHERS THEN
319 p_error_code := g_FAILURE;
320 p_error_desc := SQLERRM;
321 l_location := l_module_name||'.final_exception';
322 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
323 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
324 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
325 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
326 END IF;
327 END;
328
329 --****************************************************************************************--
330 --* Name : insert_invoice_hdr *--
331 --* Type : Procedure *--
332 --* Purpose : Inserts the Invoice Header record into ap_invoices_interface *--
333 --* Parameters : p_invoice_hdr_rec The Header record that has to be inserted *--
334 --* : p_errbuf Error returned to the concurrent process *--
335 --* : p_retcode Return Code to concurrent process *--
336 --* Global Vars : g_module_name READ *--
337 --* : g_SUCCESS READ *--
338 --* : g_ERROR READ *--
339 --* : fnd_log.level_statement READ *--
340 --* : fnd_log.g_current_runtime_level READ *--
341 --* : fnd_log.level_unexpected READ *--
342 --* : fnd_log.level_procedure READ *--
343 --* : g_enter READ *--
344 --* : g_exit READ *--
345 --* Called from : save_or_erase_invoice *--
346 --* Calls : fv_utility.debug_mesg *--
347 --* : fv_utility.log_mesg *--
348 --* Tables Used : ap_invoices_interface INSERT *--
349 --* Logic : Insert the record p_invoice_hdr_rec into table ap_invoices_interface *--
350 --****************************************************************************************--
351 PROCEDURE insert_invoice_hdr
352 (
353 p_invoice_hdr_rec IN ap_invoices_interface%ROWTYPE,
354 p_error_code OUT NOCOPY NUMBER,
355 p_error_desc OUT NOCOPY VARCHAR2
356 )
357 IS
358 l_module_name VARCHAR2(200);
359 l_location VARCHAR2(400);
360 BEGIN
361 l_module_name := g_module_name || 'insert_invoice_hdr';
362 p_error_code := g_SUCCESS;
363 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
364 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
365 END IF;
366
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,'Inserting into ap_invoices_interface');
369 END IF;
370 INSERT INTO ap_invoices_interface
371 (
372 invoice_id,
373 invoice_num,
374 invoice_type_lookup_code,
375 invoice_date,
376 po_number,
377 vendor_id,
378 vendor_num,
379 vendor_name,
380 vendor_site_id,
381 vendor_site_code,
382 invoice_amount,
383 invoice_currency_code,
384 exchange_rate,
385 exchange_rate_type,
386 exchange_date,
387 terms_id,
388 terms_name,
389 description,
390 awt_group_id,
391 awt_group_name,
392 last_update_date,
393 last_updated_by,
394 last_update_login,
395 creation_date,
396 created_by,
397 attribute_category,
398 attribute1,
399 attribute2,
400 attribute3,
401 attribute4,
402 attribute5,
403 attribute6,
404 attribute7,
405 attribute8,
406 attribute9,
407 attribute10,
408 attribute11,
409 attribute12,
410 attribute13,
411 attribute14,
412 attribute15,
413 global_attribute_category,
414 global_attribute1,
415 global_attribute2,
416 global_attribute3,
417 global_attribute4,
418 global_attribute5,
419 global_attribute6,
420 global_attribute7,
421 global_attribute8,
422 global_attribute9,
423 global_attribute10,
424 global_attribute11,
425 global_attribute12,
426 global_attribute13,
427 global_attribute14,
428 global_attribute15,
429 global_attribute16,
430 global_attribute17,
431 global_attribute18,
432 global_attribute19,
433 global_attribute20,
434 status,
435 source,
436 group_id,
437 request_id,
438 payment_cross_rate_type,
439 payment_cross_rate_date,
440 payment_cross_rate,
441 payment_currency_code,
442 workflow_flag,
443 doc_category_code,
444 voucher_num,
445 payment_method_lookup_code,
446 pay_group_lookup_code,
447 goods_received_date,
448 invoice_received_date,
449 gl_date,
450 accts_pay_code_combination_id,
451 -- ussgl_transaction_code,
452 exclusive_payment_flag,
453 org_id,
454 amount_applicable_to_discount,
455 prepay_num,
456 prepay_dist_num,
457 prepay_apply_amount,
458 prepay_gl_date,
459 invoice_includes_prepay_flag,
460 no_xrate_base_amount,
461 vendor_email_address,
462 terms_date,
463 requester_id,
464 ship_to_location,
465 external_doc_ref,
466 payment_method_code
467 )
468 VALUES
469 (
470 p_invoice_hdr_rec.invoice_id,
471 p_invoice_hdr_rec.invoice_num,
472 p_invoice_hdr_rec.invoice_type_lookup_code,
473 p_invoice_hdr_rec.invoice_date,
474 p_invoice_hdr_rec.po_number,
475 p_invoice_hdr_rec.vendor_id,
476 p_invoice_hdr_rec.vendor_num,
477 p_invoice_hdr_rec.vendor_name,
478 p_invoice_hdr_rec.vendor_site_id,
479 p_invoice_hdr_rec.vendor_site_code,
480 p_invoice_hdr_rec.invoice_amount,
481 p_invoice_hdr_rec.invoice_currency_code,
482 p_invoice_hdr_rec.exchange_rate,
483 p_invoice_hdr_rec.exchange_rate_type,
484 p_invoice_hdr_rec.exchange_date,
485 p_invoice_hdr_rec.terms_id,
486 p_invoice_hdr_rec.terms_name,
487 p_invoice_hdr_rec.description,
488 p_invoice_hdr_rec.awt_group_id,
489 p_invoice_hdr_rec.awt_group_name,
490 p_invoice_hdr_rec.last_update_date,
491 p_invoice_hdr_rec.last_updated_by,
492 p_invoice_hdr_rec.last_update_login,
493 p_invoice_hdr_rec.creation_date,
494 p_invoice_hdr_rec.created_by,
495 p_invoice_hdr_rec.attribute_category,
496 p_invoice_hdr_rec.attribute1,
497 p_invoice_hdr_rec.attribute2,
498 p_invoice_hdr_rec.attribute3,
499 p_invoice_hdr_rec.attribute4,
500 p_invoice_hdr_rec.attribute5,
501 p_invoice_hdr_rec.attribute6,
502 p_invoice_hdr_rec.attribute7,
503 p_invoice_hdr_rec.attribute8,
504 p_invoice_hdr_rec.attribute9,
505 p_invoice_hdr_rec.attribute10,
506 p_invoice_hdr_rec.attribute11,
507 p_invoice_hdr_rec.attribute12,
508 p_invoice_hdr_rec.attribute13,
509 p_invoice_hdr_rec.attribute14,
510 p_invoice_hdr_rec.attribute15,
511 p_invoice_hdr_rec.global_attribute_category,
512 p_invoice_hdr_rec.global_attribute1,
513 p_invoice_hdr_rec.global_attribute2,
514 p_invoice_hdr_rec.global_attribute3,
515 p_invoice_hdr_rec.global_attribute4,
516 p_invoice_hdr_rec.global_attribute5,
517 p_invoice_hdr_rec.global_attribute6,
518 p_invoice_hdr_rec.global_attribute7,
519 p_invoice_hdr_rec.global_attribute8,
520 p_invoice_hdr_rec.global_attribute9,
521 p_invoice_hdr_rec.global_attribute10,
522 p_invoice_hdr_rec.global_attribute11,
523 p_invoice_hdr_rec.global_attribute12,
524 p_invoice_hdr_rec.global_attribute13,
525 p_invoice_hdr_rec.global_attribute14,
526 p_invoice_hdr_rec.global_attribute15,
527 p_invoice_hdr_rec.global_attribute16,
528 p_invoice_hdr_rec.global_attribute17,
529 p_invoice_hdr_rec.global_attribute18,
530 p_invoice_hdr_rec.global_attribute19,
531 p_invoice_hdr_rec.global_attribute20,
532 p_invoice_hdr_rec.status,
533 p_invoice_hdr_rec.source,
534 p_invoice_hdr_rec.group_id,
535 p_invoice_hdr_rec.request_id,
536 p_invoice_hdr_rec.payment_cross_rate_type,
537 p_invoice_hdr_rec.payment_cross_rate_date,
538 p_invoice_hdr_rec.payment_cross_rate,
539 p_invoice_hdr_rec.payment_currency_code,
540 p_invoice_hdr_rec.workflow_flag,
541 p_invoice_hdr_rec.doc_category_code,
542 p_invoice_hdr_rec.voucher_num,
543 p_invoice_hdr_rec.payment_method_lookup_code,
544 p_invoice_hdr_rec.pay_group_lookup_code,
545 p_invoice_hdr_rec.goods_received_date,
546 p_invoice_hdr_rec.invoice_received_date,
547 p_invoice_hdr_rec.gl_date,
548 p_invoice_hdr_rec.accts_pay_code_combination_id,
549 -- p_invoice_hdr_rec.ussgl_transaction_code,
550 p_invoice_hdr_rec.exclusive_payment_flag,
551 p_invoice_hdr_rec.org_id,
552 p_invoice_hdr_rec.amount_applicable_to_discount,
553 p_invoice_hdr_rec.prepay_num,
554 p_invoice_hdr_rec.prepay_dist_num,
555 p_invoice_hdr_rec.prepay_apply_amount,
556 p_invoice_hdr_rec.prepay_gl_date,
557 p_invoice_hdr_rec.invoice_includes_prepay_flag,
558 p_invoice_hdr_rec.no_xrate_base_amount,
559 p_invoice_hdr_rec.vendor_email_address,
560 p_invoice_hdr_rec.terms_date,
561 p_invoice_hdr_rec.requester_id,
562 p_invoice_hdr_rec.ship_to_location,
563 p_invoice_hdr_rec.external_doc_ref,
564 p_invoice_hdr_rec.payment_method_code
565 );
566 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
567 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
568 END IF;
569 EXCEPTION
570 WHEN OTHERS THEN
571 p_error_code := g_FAILURE;
572 p_error_desc := SQLERRM;
573 l_location := l_module_name||'.final_exception';
574 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
575 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
576 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
577 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
578 END IF;
579 END;
580
581 --****************************************************************************************--
582 --* Name : insert_invoice_line *--
583 --* Type : Procedure *--
584 --* Purpose : Inserts the Invoice Line record into ap_invoice_lines_interface *--
585 --* Parameters : p_invoice_lines_rec The Line record that has to be inserted *--
586 --* : p_errbuf Error returned to the concurrent process *--
587 --* : p_retcode Return Code to concurrent process *--
588 --* Global Vars : g_module_name READ *--
589 --* : g_SUCCESS READ *--
590 --* : g_ERROR READ *--
591 --* : fnd_log.level_statement READ *--
592 --* : fnd_log.g_current_runtime_level READ *--
593 --* : fnd_log.level_unexpected READ *--
594 --* : fnd_log.level_procedure READ *--
595 --* : g_enter READ *--
596 --* : g_exit READ *--
597 --* Called from : process_data *--
598 --* Calls : fv_utility.debug_mesg *--
599 --* : fv_utility.log_mesg *--
600 --* Sequence Used : ap_invoice_lines_interface_s *--
601 --* Tables Used : ap_invoice_lines_interface INSERT *--
602 --* Logic : Insert the record p_invoice_lines_rec into table *--
603 --* : ap_invoice_lines_interface *--
604 --* : The invoice line id is returned in *--
605 --* : p_invoice_lines_rec.invoice_line_id *--
606 --****************************************************************************************--
607 PROCEDURE insert_invoice_line
608 (
609 p_invoice_lines_rec IN OUT NOCOPY ap_invoice_lines_interface%ROWTYPE,
610 p_error_code OUT NOCOPY NUMBER,
611 p_error_desc OUT NOCOPY VARCHAR2
612 )
613 IS
614 l_module_name VARCHAR2(200);
615 l_location VARCHAR2(400);
616 BEGIN
617 l_module_name := g_module_name || 'insert_invoice_line';
618 p_error_code := g_SUCCESS;
619 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
620 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
621 END IF;
622
623 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
624 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Inserting into ap_invoice_lines_interface');
625 END IF;
626 INSERT INTO ap_invoice_lines_interface
627 (
628 invoice_id,
629 invoice_line_id,
630 line_number,
631 line_type_lookup_code,
632 line_group_number,
633 amount,
634 accounting_date,
635 description,
636 amount_includes_tax_flag,
637 prorate_across_flag,
638 tax_code,
639 final_match_flag,
640 po_header_id,
641 po_number,
642 po_line_id,
643 po_line_number,
644 po_line_location_id,
645 po_shipment_num,
646 po_distribution_id,
647 po_distribution_num,
648 po_unit_of_measure,
649 inventory_item_id,
650 item_description,
651 quantity_invoiced,
652 ship_to_location_code,
653 unit_price,
654 distribution_set_id,
655 distribution_set_name,
656 dist_code_concatenated,
657 dist_code_combination_id,
658 awt_group_id,
659 awt_group_name,
660 last_updated_by,
661 last_update_date,
662 last_update_login,
663 created_by,
664 creation_date,
665 attribute_category,
666 attribute1,
667 attribute2,
668 attribute3,
669 attribute4,
670 attribute5,
671 attribute6,
672 attribute7,
673 attribute8,
674 attribute9,
675 attribute10,
676 attribute11,
677 attribute12,
678 attribute13,
679 attribute14,
680 attribute15,
681 global_attribute_category,
682 global_attribute1,
683 global_attribute2,
684 global_attribute3,
685 global_attribute4,
686 global_attribute5,
687 global_attribute6,
688 global_attribute7,
689 global_attribute8,
690 global_attribute9,
691 global_attribute10,
692 global_attribute11,
693 global_attribute12,
694 global_attribute13,
695 global_attribute14,
696 global_attribute15,
697 global_attribute16,
698 global_attribute17,
699 global_attribute18,
700 global_attribute19,
701 global_attribute20,
702 po_release_id,
703 release_num,
704 account_segment,
705 balancing_segment,
706 cost_center_segment,
707 project_id,
708 task_id,
709 expenditure_type,
710 expenditure_item_date,
711 expenditure_organization_id,
712 project_accounting_context,
713 pa_addition_flag,
714 pa_quantity,
715 -- ussgl_transaction_code,
716 stat_amount,
717 type_1099,
718 income_tax_region,
719 assets_tracking_flag,
720 price_correction_flag,
721 org_id,
722 receipt_number,
723 receipt_line_number,
724 match_option,
725 packing_slip,
726 rcv_transaction_id,
727 pa_cc_ar_invoice_id,
728 pa_cc_ar_invoice_line_num,
729 reference_1,
730 reference_2,
731 pa_cc_processed_code,
732 tax_recovery_rate,
733 tax_recovery_override_flag,
734 tax_recoverable_flag,
735 tax_code_override_flag,
736 tax_code_id,
737 credit_card_trx_id,
738 award_id,
739 vendor_item_num,
740 taxable_flag,
741 price_correct_inv_num,
742 external_doc_line_ref
743 )
744 VALUES
745 (
746 p_invoice_lines_rec.invoice_id,
747 ap_invoice_lines_interface_s.NEXTVAL,
748 p_invoice_lines_rec.line_number,
749 p_invoice_lines_rec.line_type_lookup_code,
750 p_invoice_lines_rec.line_group_number,
751 p_invoice_lines_rec.amount,
752 p_invoice_lines_rec.accounting_date,
753 p_invoice_lines_rec.description,
754 p_invoice_lines_rec.amount_includes_tax_flag,
755 p_invoice_lines_rec.prorate_across_flag,
756 p_invoice_lines_rec.tax_code,
757 p_invoice_lines_rec.final_match_flag,
758 p_invoice_lines_rec.po_header_id,
759 p_invoice_lines_rec.po_number,
760 p_invoice_lines_rec.po_line_id,
761 p_invoice_lines_rec.po_line_number,
762 p_invoice_lines_rec.po_line_location_id,
763 p_invoice_lines_rec.po_shipment_num,
764 p_invoice_lines_rec.po_distribution_id,
765 p_invoice_lines_rec.po_distribution_num,
766 p_invoice_lines_rec.po_unit_of_measure,
767 p_invoice_lines_rec.inventory_item_id,
768 p_invoice_lines_rec.item_description,
769 p_invoice_lines_rec.quantity_invoiced,
770 p_invoice_lines_rec.ship_to_location_code,
771 p_invoice_lines_rec.unit_price,
772 p_invoice_lines_rec.distribution_set_id,
773 p_invoice_lines_rec.distribution_set_name,
774 p_invoice_lines_rec.dist_code_concatenated,
775 p_invoice_lines_rec.dist_code_combination_id,
776 p_invoice_lines_rec.awt_group_id,
777 p_invoice_lines_rec.awt_group_name,
778 p_invoice_lines_rec.last_updated_by,
779 p_invoice_lines_rec.last_update_date,
780 p_invoice_lines_rec.last_update_login,
781 p_invoice_lines_rec.created_by,
782 p_invoice_lines_rec.creation_date,
783 p_invoice_lines_rec.attribute_category,
784 p_invoice_lines_rec.attribute1,
785 p_invoice_lines_rec.attribute2,
786 p_invoice_lines_rec.attribute3,
787 p_invoice_lines_rec.attribute4,
788 p_invoice_lines_rec.attribute5,
789 p_invoice_lines_rec.attribute6,
790 p_invoice_lines_rec.attribute7,
791 p_invoice_lines_rec.attribute8,
792 p_invoice_lines_rec.attribute9,
793 p_invoice_lines_rec.attribute10,
794 p_invoice_lines_rec.attribute11,
795 p_invoice_lines_rec.attribute12,
796 p_invoice_lines_rec.attribute13,
797 p_invoice_lines_rec.attribute14,
798 p_invoice_lines_rec.attribute15,
799 p_invoice_lines_rec.global_attribute_category,
800 p_invoice_lines_rec.global_attribute1,
801 p_invoice_lines_rec.global_attribute2,
802 p_invoice_lines_rec.global_attribute3,
803 p_invoice_lines_rec.global_attribute4,
804 p_invoice_lines_rec.global_attribute5,
805 p_invoice_lines_rec.global_attribute6,
806 p_invoice_lines_rec.global_attribute7,
807 p_invoice_lines_rec.global_attribute8,
808 p_invoice_lines_rec.global_attribute9,
809 p_invoice_lines_rec.global_attribute10,
810 p_invoice_lines_rec.global_attribute11,
811 p_invoice_lines_rec.global_attribute12,
812 p_invoice_lines_rec.global_attribute13,
813 p_invoice_lines_rec.global_attribute14,
814 p_invoice_lines_rec.global_attribute15,
815 p_invoice_lines_rec.global_attribute16,
816 p_invoice_lines_rec.global_attribute17,
817 p_invoice_lines_rec.global_attribute18,
818 p_invoice_lines_rec.global_attribute19,
819 p_invoice_lines_rec.global_attribute20,
820 p_invoice_lines_rec.po_release_id,
821 p_invoice_lines_rec.release_num,
822 p_invoice_lines_rec.account_segment,
823 p_invoice_lines_rec.balancing_segment,
824 p_invoice_lines_rec.cost_center_segment,
825 p_invoice_lines_rec.project_id,
826 p_invoice_lines_rec.task_id,
827 p_invoice_lines_rec.expenditure_type,
828 p_invoice_lines_rec.expenditure_item_date,
829 p_invoice_lines_rec.expenditure_organization_id,
830 p_invoice_lines_rec.project_accounting_context,
831 p_invoice_lines_rec.pa_addition_flag,
832 p_invoice_lines_rec.pa_quantity,
833 -- p_invoice_lines_rec.ussgl_transaction_code,
834 p_invoice_lines_rec.stat_amount,
835 p_invoice_lines_rec.type_1099,
836 p_invoice_lines_rec.income_tax_region,
837 p_invoice_lines_rec.assets_tracking_flag,
838 p_invoice_lines_rec.price_correction_flag,
839 p_invoice_lines_rec.org_id,
840 p_invoice_lines_rec.receipt_number,
841 p_invoice_lines_rec.receipt_line_number,
842 p_invoice_lines_rec.match_option,
843 p_invoice_lines_rec.packing_slip,
844 p_invoice_lines_rec.rcv_transaction_id,
845 p_invoice_lines_rec.pa_cc_ar_invoice_id,
846 p_invoice_lines_rec.pa_cc_ar_invoice_line_num,
847 p_invoice_lines_rec.reference_1,
848 p_invoice_lines_rec.reference_2,
849 p_invoice_lines_rec.pa_cc_processed_code,
850 p_invoice_lines_rec.tax_recovery_rate,
851 p_invoice_lines_rec.tax_recovery_override_flag,
852 p_invoice_lines_rec.tax_recoverable_flag,
853 p_invoice_lines_rec.tax_code_override_flag,
854 p_invoice_lines_rec.tax_code_id,
855 p_invoice_lines_rec.credit_card_trx_id,
856 p_invoice_lines_rec.award_id,
857 p_invoice_lines_rec.vendor_item_num,
858 p_invoice_lines_rec.taxable_flag,
859 p_invoice_lines_rec.price_correct_inv_num,
860 p_invoice_lines_rec.external_doc_line_ref
861 ) RETURNING invoice_line_id INTO p_invoice_lines_rec.invoice_line_id;
862 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
863 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
864 END IF;
865 EXCEPTION
866 WHEN OTHERS THEN
867 p_error_code := g_FAILURE;
868 p_error_desc := SQLERRM;
869 l_location := l_module_name||'.final_exception';
870 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
871 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
872 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
873 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
874 END IF;
875 END;
876
877 --****************************************************************************************--
878 --* Name : check_for_ap_import_errors *--
879 --* Type : Procedure *--
880 --* Purpose : Checks for ap import errors for the group id *--
881 --* Parameters : p_group_id Group Id for which the errors have to be checked *--
882 --* : p_errbuf Error returned to the concurrent process *--
883 --* : p_retcode Return Code to concurrent process *--
884 --* Global Vars : g_module_name READ *--
885 --* : g_SUCCESS READ *--
886 --* : g_ERROR READ *--
887 --* : fnd_log.level_statement READ *--
888 --* : fnd_log.g_current_runtime_level READ *--
889 --* : fnd_log.level_unexpected READ *--
890 --* : fnd_log.level_procedure READ *--
891 --* : g_enter READ *--
892 --* : g_exit READ *--
893 --* Called from : main *--
894 --* Calls : insert_error *--
895 --* : fv_utility.debug_mesg *--
896 --* : fv_utility.log_mesg *--
897 --* Tables Used : ap_invoices_interface SELECT *--
898 --* : ap_invoice_lines_interface SELECT *--
899 --* : ap_interface_rejections SELECT *--
900 --* : fv_ipac_import SELECT, UPDATE *--
901 --* : ap_lookup_codes SELECT *--
902 --* Logic : Go through ap_interface_rejections for the group id and see *--
903 --* : if there are any header errors. If so insert the errors into *--
904 --* : fv_ipac_import_errors and update the table fv_ipac_import with *--
905 --* : error status. *--
906 --* : Go through ap_interface_rejections for the group id and see *--
907 --* : if there are any line errors. If so insert the errors into *--
908 --* : fv_ipac_import_errors and update the table fv_ipac_import with *--
909 --* : error status. *--
910 --****************************************************************************************--
911 PROCEDURE check_for_ap_import_errors
912 (
913 p_group_id ap_invoices_interface.group_id%TYPE,
914 p_error_code OUT NOCOPY NUMBER,
915 p_error_desc OUT NOCOPY VARCHAR2
916 )
917 IS
918 l_module_name VARCHAR2(200);
919 l_location VARCHAR2(400);
920 l_rowcount NUMBER;
921 BEGIN
922 l_module_name := g_module_name || 'check_for_ap_import_errors';
923 p_error_code := g_SUCCESS;
924 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
925 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
926 END IF;
927 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
928 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_group_id = '||p_group_id);
929 END IF;
930
931 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
932 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'About to get into invoices_rec');
933 END IF;
934 FOR invoices_rec IN (SELECT aii.invoice_id,
935 air.reject_lookup_code,
936 fii.ipac_import_id,
937 alc.description
938 FROM ap_invoices_interface aii,
939 ap_interface_rejections air,
940 fv_ipac_import fii,
941 ap_lookup_codes alc
942 WHERE aii.group_id = p_group_id
943 AND aii.status = 'REJECTED'
944 AND aii.invoice_id = air.parent_id
945 AND air.parent_table = 'AP_INVOICES_INTERFACE'
946 AND fii.group_id = p_group_id
947 AND fii.int_invoice_id = aii.invoice_id
948 AND alc.lookup_type = 'REJECT CODE'
949 AND alc.lookup_code = air.reject_lookup_code) LOOP
950 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
951 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'invoice_id='||invoices_rec.invoice_id);
952 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'reject_lookup_code='||invoices_rec.reject_lookup_code);
953 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'description='||invoices_rec.description);
954 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'ipac_import_id='||invoices_rec.ipac_import_id);
955 END IF;
956
957 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
958 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_error');
959 END IF;
960 insert_error
961 (
962 p_ipac_import_id => invoices_rec.ipac_import_id,
963 p_validation_code => invoices_rec.reject_lookup_code,
964 p_validation_err => invoices_rec.description,
965 p_error_code => p_error_code,
966 p_error_desc => p_error_desc
967 );
968 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
969 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'insert_error returned with code'||p_error_code);
970 END IF;
971
972 IF (p_error_code = g_SUCCESS) THEN
973 BEGIN
974 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
975 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'UPDATE fv_ipac_import1');
976 END IF;
977 UPDATE fv_ipac_import
978 SET record_status = g_status_error
979 WHERE ipac_import_id = invoices_rec.ipac_import_id;
980 l_rowcount := SQL%ROWCOUNT;
981 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
982 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'UPDATED '||l_rowcount||' rows.');
983 END IF;
984
985 EXCEPTION
986 WHEN OTHERS THEN
987 p_error_code := g_FAILURE;
988 p_error_desc := SQLERRM;
989 l_location := l_module_name||'.update_fv_ipac_import1';
990 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
991 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
992 END;
993 END IF;
994 IF (p_error_code <> g_SUCCESS) THEN
995 EXIT;
996 END IF;
997 END LOOP;
998 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
999 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'invoices_rec processing finished');
1000 END IF;
1001
1002 IF (p_error_code = g_SUCCESS) THEN
1003 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1004 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'About to get into invoice_lines_rec');
1005 END IF;
1006 FOR invoice_lines_rec IN (SELECT aii.invoice_id,
1007 air.reject_lookup_code,
1008 fii.ipac_import_id,
1009 alc.description
1010 FROM ap_invoices_interface aii,
1011 ap_invoice_lines_interface aili,
1012 ap_interface_rejections air,
1013 fv_ipac_import fii,
1014 ap_lookup_codes alc
1015 WHERE aii.group_id = p_group_id
1016 AND aii.status = 'REJECTED'
1017 AND aili.invoice_line_id = air.parent_id
1018 AND aii.invoice_id = aili.invoice_id
1019 AND air.parent_table = 'AP_INVOICE_LINES_INTERFACE'
1020 AND fii.group_id = p_group_id
1021 AND fii.int_invoice_id = aii.invoice_id
1022 AND fii.int_invoice_line_id = aili.invoice_line_id
1023 AND alc.lookup_type = 'REJECT CODE'
1024 AND alc.lookup_code = air.reject_lookup_code) LOOP
1025 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1026 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'invoice_id='||invoice_lines_rec.invoice_id);
1027 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'reject_lookup_code='||invoice_lines_rec.reject_lookup_code);
1028 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'description='||invoice_lines_rec.description);
1029 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'ipac_import_id='||invoice_lines_rec.ipac_import_id);
1030 END IF;
1031
1032 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1033 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_error');
1034 END IF;
1035 insert_error
1036 (
1037 p_ipac_import_id => invoice_lines_rec.ipac_import_id,
1038 p_validation_code => invoice_lines_rec.reject_lookup_code,
1039 p_validation_err => invoice_lines_rec.description,
1040 p_error_code => p_error_code,
1041 p_error_desc => p_error_desc
1042 );
1043
1044 IF (p_error_code = g_SUCCESS) THEN
1045 BEGIN
1046 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1047 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'UPDATE fv_ipac_import2');
1048 END IF;
1049 UPDATE fv_ipac_import
1050 SET record_status = g_status_error
1051 WHERE ipac_import_id = invoice_lines_rec.ipac_import_id;
1052 l_rowcount := SQL%ROWCOUNT;
1053 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1054 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'UPDATED '||l_rowcount||' rows.');
1055 END IF;
1056 EXCEPTION
1057 WHEN OTHERS THEN
1058 p_error_code := g_FAILURE;
1059 p_error_desc := SQLERRM;
1060 l_location := l_module_name||'.update_fv_ipac_import2';
1061 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1062 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1063 END;
1064 END IF;
1065 IF (p_error_code <> g_SUCCESS) THEN
1066 EXIT;
1067 END IF;
1068 END LOOP;
1069 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1070 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'invoice_lines_rec processing finished');
1071 END IF;
1072
1073 END IF;
1074 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1075 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
1076 END IF;
1077 EXCEPTION
1078 WHEN OTHERS THEN
1079 p_error_code := g_FAILURE;
1080 p_error_desc := SQLERRM;
1081 l_location := l_module_name||'.final_exception';
1082 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1083 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1084 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1085 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
1086 END IF;
1087 END;
1088
1089 --****************************************************************************************--
1090 --* Name : kick_off_ipac_auto_pmt_process *--
1091 --* Type : Procedure *--
1092 --* Purpose : Procedure to kick of the concurrent process *--
1093 --* : IPAC Automatic Payments Process (FVIPAPMT) *--
1094 --* Parameters : p_batch_name IN Batch name to be passed into conc pgm *--
1095 --* : p_document_id IN Document Id to be passed into conc pgm *--
1096 --* : p_errbuf OUT Error returned to the concurrent process *--
1097 --* : p_retcode OUT Return Code to concurrent process *--
1098 --* Global Vars : g_org_id READ *--
1099 --* : g_module_name READ *--
1100 --* : g_SUCCESS READ *--
1101 --* : g_ERROR READ *--
1102 --* : fnd_log.level_statement READ *--
1103 --* : fnd_log.g_current_runtime_level READ *--
1104 --* : fnd_log.level_unexpected READ *--
1105 --* : fnd_log.level_procedure READ *--
1106 --* : g_enter READ *--
1107 --* : g_exit READ *--
1108 --* Called from : main *--
1109 --* Calls : fnd_request.set_org_id *--
1110 --* : fnd_request.submit_request *--
1111 --* : fnd_concurrent.wait_for_request *--
1112 --* : fv_utility.debug_mesg *--
1113 --* : fv_utility.log_mesg *--
1114 --* Tables Used : None *--
1115 --* Logic : Submit a request for FVIPAPMT using the parameters passed and wait *--
1116 --* : for the request to finish *--
1117 --****************************************************************************************--
1118 PROCEDURE kick_off_ipac_auto_pmt_process
1119 (
1120 p_batch_name IN VARCHAR2,
1121 p_payment_bank_acct_id IN NUMBER,
1122 p_payment_profile_id IN NUMBER,
1123 p_payment_document_id IN NUMBER,
1124 p_error_code OUT NOCOPY NUMBER,
1125 p_error_desc OUT NOCOPY VARCHAR2
1126 )
1127 IS
1128 l_module_name VARCHAR2(200);
1129 l_location VARCHAR2(400);
1130 l_request_id NUMBER;
1131 l_request_wait_status BOOLEAN;
1132 l_phase VARCHAR2(100);
1133 l_status VARCHAR2(100);
1134 l_dev_phase VARCHAR2(100);
1135 l_dev_status VARCHAR2(100);
1136 l_message VARCHAR2(100);
1137 BEGIN
1138 l_module_name := g_module_name || 'kick_off_ipac_auto_pmt_process';
1139 p_error_code := g_SUCCESS;
1140 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1141 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
1142 END IF;
1143 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1144 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_batch_name = '||p_batch_name);
1145 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_payment_bank_acct_id = '||p_payment_bank_acct_id);
1146 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_payment_profile_id = '||p_payment_profile_id);
1147 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_payment_document_id = '||p_payment_document_id);
1148 -- fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_pay_trans_code = '||p_pay_trans_code);
1149 END IF;
1150
1151 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1152 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling submit_request');
1153 END IF;
1154
1155 fnd_request.set_org_id(g_org_id);
1156 l_request_id := fnd_request.submit_request
1157 (
1158 application => 'FV',
1159 program => 'FVIPAPMT',
1160 description => '',
1161 start_time => '',
1162 sub_request => FALSE ,
1163 argument1 => p_batch_name,
1164 argument2 => p_payment_bank_acct_id,
1165 argument3 => p_payment_profile_id,
1166 argument4 => p_payment_document_id,
1167 argument5 => g_org_id,
1168 argument6 => g_set_of_books_id
1169 );
1170
1171 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1172 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'submit_request retured');
1173 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_request_id = '||l_request_id);
1174 END IF;
1175
1176 IF (l_request_id = 0) THEN
1177 p_error_code := g_FAILURE;
1178 p_error_desc := 'Failed to submit request for IPAC Automatic Payment Process';
1179 fv_utility.log_mesg(fnd_log.level_error, l_module_name,p_error_desc) ;
1180 ELSE
1181 COMMIT;
1182 END IF;
1183
1184 IF (p_error_code = g_SUCCESS) THEN
1185 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1186 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling wait_for_request');
1187 END IF;
1188 l_request_wait_status := fnd_concurrent.wait_for_request
1189 (
1190 request_id => l_request_id,
1191 interval => 20,
1192 max_wait => 0,
1193 phase => l_phase,
1194 status => l_status,
1195 dev_phase => l_dev_phase,
1196 dev_status => l_dev_status,
1197 message => l_message
1198 );
1199 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1200 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'wait_for_request retured');
1201 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_phase = '||l_phase);
1202 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_status = '||l_status);
1203 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_dev_phase = '||l_dev_phase);
1204 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_dev_status = '||l_dev_status);
1205 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_message = '||l_message);
1206 END IF;
1207
1208 COMMIT;
1209
1210 IF l_request_wait_status = FALSE THEN
1211 p_error_code := g_FAILURE;
1212 p_error_desc := 'Failed to wait for IPAC Automatic Payment Process';
1213 fv_utility.log_mesg(fnd_log.level_error, l_module_name,p_error_desc) ;
1214 END IF;
1215 END IF;
1216 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1217 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
1218 END IF;
1219
1220 EXCEPTION
1221 WHEN OTHERS THEN
1222 p_error_code := g_FAILURE;
1223 p_error_desc := SQLERRM;
1224 l_location := l_module_name||'.final_exception';
1225 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1226 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1227 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1228 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
1229 END IF;
1230 END;
1231
1232 --****************************************************************************************--
1233 --* Name : kick_off_ap_invoices_import *--
1234 --* Type : Procedure *--
1235 --* Purpose : Procedure to kick of the concurrent process *--
1236 --* : Payables Open Interface Import (APXIIMPT) *--
1237 --* Parameters : p_batch_name IN The batch name to be passed to conc pgm *--
1238 --* : p_group_id IN The group id to be passed to conc pgm *--
1239 --* : p_errbuf OUT Error returned to the concurrent process *--
1240 --* : p_retcode OUT Return Code to concurrent process *--
1241 --* Global Vars : g_org_id READ *--
1242 --* : g_module_name READ *--
1243 --* : g_SUCCESS READ *--
1244 --* : g_ERROR READ *--
1245 --* : fnd_log.level_statement READ *--
1246 --* : fnd_log.g_current_runtime_level READ *--
1247 --* : fnd_log.level_unexpected READ *--
1248 --* : fnd_log.level_procedure READ *--
1249 --* : g_enter READ *--
1250 --* : g_exit READ *--
1251 --* Called from : main *--
1252 --* Calls : fnd_request.set_org_id *--
1253 --* : fnd_request.submit_request *--
1254 --* : fnd_concurrent.wait_for_request *--
1255 --* : fv_utility.debug_mesg *--
1256 --* : fv_utility.log_mesg *--
1257 --* Tables Used : None *--
1258 --* Logic : Submit a request for APXIIMPT using the parameters passed and wait *--
1259 --* : for the request to finish *--
1260 --****************************************************************************************--
1261 PROCEDURE kick_off_ap_invoices_import
1262 (
1263 p_batch_name IN VARCHAR2,
1264 p_group_id IN NUMBER,
1265 p_error_code OUT NOCOPY NUMBER,
1266 p_error_desc OUT NOCOPY VARCHAR2
1267 )
1268 IS
1269 l_module_name VARCHAR2(200);
1270 l_location VARCHAR2(400);
1271 l_request_id NUMBER;
1272 l_request_wait_status BOOLEAN;
1273 l_phase VARCHAR2(100);
1274 l_status VARCHAR2(100);
1275 l_dev_phase VARCHAR2(100);
1276 l_dev_status VARCHAR2(100);
1277 l_message VARCHAR2(100);
1278 BEGIN
1279 l_module_name := g_module_name || 'kick_off_ap_invoices_import';
1280 p_error_code := g_SUCCESS;
1281
1282 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1283 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
1284 END IF;
1285 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1286 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_batch_name = '||p_batch_name);
1287 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_group_id = '||p_group_id);
1288 END IF;
1289
1290 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1291 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling submit_request');
1292 END IF;
1293
1294
1295 fnd_request.set_org_id(g_org_id);
1296 l_request_id := fnd_request.submit_request
1297 (
1298 application => 'SQLAP',
1299 program => 'APXIIMPT',
1300 description => '',
1301 start_time => '',
1302 sub_request => FALSE ,
1303 argument1 => g_org_id ,
1304 argument2 => 'IPAC',
1305 argument3 => p_group_id,
1306 argument4 => p_batch_name,
1307 argument5 => '',
1308 argument6 => '',
1309 argument7 => '',
1310 argument8 => 'Y',
1311 argument9 => '',
1312 argument10 => '',
1313 argument11 => '',
1314 argument12 => '',
1315 argument13 => '',
1316 argument14 => ''
1317 ) ;
1318
1319
1320 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1321 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'submit_request retured');
1322 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_request_id = '||l_request_id);
1323 END IF;
1324
1325 IF (l_request_id = 0) THEN
1326 p_error_code := g_FAILURE;
1327 p_error_desc := 'Failed to submit request for "Payables Open Interface Import" Program';
1328 fv_utility.log_mesg(fnd_log.level_error, l_module_name,p_error_desc) ;
1329 ELSE
1330 COMMIT;
1331 END IF;
1332
1333 IF (p_error_code = g_SUCCESS) THEN
1334 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1335 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling wait_for_request');
1336 END IF;
1337 l_request_wait_status := fnd_concurrent.wait_for_request
1338 (
1339 request_id => l_request_id,
1340 interval => 20,
1341 max_wait => 0,
1342 phase => l_phase,
1343 status => l_status,
1344 dev_phase => l_dev_phase,
1345 dev_status => l_dev_status,
1346 message => l_message
1347 );
1348 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1349 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'wait_for_request retured');
1350 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_phase = '||l_phase);
1351 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_status = '||l_status);
1352 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_dev_phase = '||l_dev_phase);
1353 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_dev_status = '||l_dev_status);
1354 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_message = '||l_message);
1355 END IF;
1356
1357 COMMIT;
1358
1359 IF l_request_wait_status = FALSE THEN
1360 p_error_code := g_FAILURE;
1361 p_error_desc := 'Failed to wait for the payables open interface';
1362 fv_utility.log_mesg(fnd_log.level_error, l_module_name,p_error_desc) ;
1363 END IF;
1364 END IF;
1365 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1366 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
1367 END IF;
1368
1369 EXCEPTION
1370 WHEN OTHERS THEN
1371 p_error_code := g_FAILURE;
1372 p_error_desc := SQLERRM;
1373 l_location := l_module_name||'.final_exception';
1374 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1375 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1376 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1377 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
1378 END IF;
1379 END;
1380
1381 --****************************************************************************************--
1382 --* Name : kick_off_exception_report *--
1383 --* Type : Procedure *--
1384 --* Purpose : Procedure to kick of the concurrent process *--
1385 --* : IPAC Disbursement Exception Report (FVIPDISR) *--
1386 --* Parameters : p_data_file_name IN The data file name passed to conc pgm *--
1387 --* : p_agency_location_code IN The alc passed to the conc pgm *--
1388 --* : p_group_id IN The group id passed to tbe conc pgm *--
1389 --* : p_errbuf OUT Error returned to the concurrent process *--
1390 --* : p_retcode OUT Return Code to concurrent process *--
1391 --* Global Vars : g_org_id READ *--
1392 --* : g_module_name READ *--
1393 --* : g_SUCCESS READ *--
1394 --* : g_ERROR READ *--
1395 --* : fnd_log.level_statement READ *--
1396 --* : fnd_log.g_current_runtime_level READ *--
1397 --* : fnd_log.level_procedure READ *--
1398 --* : fnd_log.level_unexpected READ *--
1399 --* : g_enter READ *--
1400 --* : g_exit READ *--
1401 --* Called from : main *--
1402 --* Calls : fnd_request.set_org_id *--
1403 --* : fnd_request.submit_request *--
1404 --* : fnd_concurrent.wait_for_request *--
1405 --* : fv_utility.debug_mesg *--
1406 --* : fv_utility.log_mesg *--
1407 --* Tables Used : None *--
1408 --* Logic : Submit a request for FVIPDISR using the parameters passed and wait *--
1409 --* : for the request to finish *--
1410 --****************************************************************************************--
1411 PROCEDURE kick_off_exception_report
1412 (
1413 p_data_file_name IN VARCHAR2,
1414 p_agency_location_code IN VARCHAR2,
1415 p_group_id IN NUMBER,
1416 p_error_code OUT NOCOPY NUMBER,
1417 p_error_desc OUT NOCOPY VARCHAR2
1418 )
1419 IS
1420 l_module_name VARCHAR2(200);
1421 l_location VARCHAR2(400);
1422 l_request_id NUMBER;
1423 l_request_wait_status BOOLEAN;
1424 l_phase VARCHAR2(100);
1425 l_status VARCHAR2(100);
1426 l_dev_phase VARCHAR2(100);
1427 l_dev_status VARCHAR2(100);
1428 l_message VARCHAR2(100);
1429 BEGIN
1430 l_module_name := g_module_name || 'kick_off_exception_report';
1431 p_error_code := g_SUCCESS;
1432 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1433 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
1434 END IF;
1435 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1436 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_data_file_name = '||p_data_file_name);
1437 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_agency_location_code = '||p_agency_location_code);
1438 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_group_id = '||p_group_id);
1439 END IF;
1440
1441 -- The request below submits the IPAC Disbursement Exception Report. The report should
1442 -- be submitted even if there are no valid records for the Payables Open Interface
1443 -- Import process
1444 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1445 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling submit_request');
1446 END IF;
1447
1448 fnd_request.set_org_id(g_org_id);
1449 l_request_id := fnd_request.submit_request
1450 (
1451 application => 'FV',
1452 program => 'FVIPDISR',
1453 description => '',
1454 start_time => '',
1455 sub_request => FALSE ,
1456 argument1 => p_data_file_name,
1457 argument2 => p_agency_location_code,
1458 argument3 => p_group_id,
1459 argument4 => g_org_id,
1460 argument5 => g_set_of_books_id
1461 );
1462
1463 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1464 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'submit_request retured');
1465 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_request_id = '||l_request_id);
1466 END IF;
1467
1468 IF (l_request_id = 0) THEN
1469 p_error_code := g_FAILURE;
1470 p_error_desc := 'Failed to submit request for IPAC Disbursement Exception Report';
1471 fv_utility.log_mesg(fnd_log.level_error, l_module_name,p_error_desc) ;
1472 ELSE
1473 COMMIT;
1474 END IF;
1475
1476
1477 IF (p_error_code = g_SUCCESS) THEN
1478 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1479 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling wait_for_request');
1480 END IF;
1481 l_request_wait_status := fnd_concurrent.wait_for_request
1482 (
1483 request_id => l_request_id,
1484 interval => 20,
1485 max_wait => 0,
1486 phase => l_phase,
1487 status => l_status,
1488 dev_phase => l_dev_phase,
1489 dev_status => l_dev_status,
1490 message => l_message
1491 );
1492 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1493 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'wait_for_request retured');
1494 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_phase = '||l_phase);
1495 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_status = '||l_status);
1496 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_dev_phase = '||l_dev_phase);
1497 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_dev_status = '||l_dev_status);
1498 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_message = '||l_message);
1499 END IF;
1500
1501 COMMIT;
1502
1503 IF l_request_wait_status = FALSE THEN
1504 p_error_code := g_FAILURE;
1505 p_error_desc := 'Failed to wait for the exception report';
1506 fv_utility.log_mesg(fnd_log.level_error, l_module_name,p_error_desc) ;
1507 END IF;
1508 END IF;
1509 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1510 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
1511 END IF;
1512
1513 EXCEPTION
1514 WHEN OTHERS THEN
1515 p_error_code := g_FAILURE;
1516 p_error_desc := SQLERRM;
1517 l_location := l_module_name||'.final_exception';
1518 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1519 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1520 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1521 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
1522 END IF;
1523 END;
1524
1525 --****************************************************************************************--
1526 --* Name : pre_process_data *--
1527 --* Type : Procedure *--
1528 --* Purpose : Procedure used to preprocess the imported data so that it does not *--
1529 --* : get picked up by any other process *--
1530 --* Parameters : p_data_file_name IN The data file name *--
1531 --* : p_agency_location_code IN The agency location Code *--
1532 --* : p_batch_name OUT The batch name *--
1533 --* : p_group_id OUT The group id *--
1534 --* : p_errbuf OUT Error returned to the concurrent process *--
1535 --* : p_retcode OUT Return Code to concurrent process *--
1536 --* Global Vars : g_module_name READ *--
1537 --* : g_SUCCESS READ *--
1538 --* : g_ERROR READ *--
1539 --* : fnd_log.level_statement READ *--
1540 --* : fnd_log.g_current_runtime_level READ *--
1541 --* : fnd_log.level_unexpected READ *--
1542 --* : fnd_log.level_procedure READ *--
1543 --* : g_enter READ *--
1544 --* : g_exit READ *--
1545 --* Called from : process_data *--
1546 --* Calls : fv_utility.debug_mesg *--
1547 --* : fv_utility.log_mesg *--
1548 --* Sequence Used : ap_interface_groups_s *--
1549 --* : fv_ipac_batch_s *--
1550 --* Tables Used : fv_ipac_import DELETE, UPDATE *--
1551 --* Logic : 1. Get sequence number from fv_ipac_batch_s *--
1552 --* : 2. Get sequence number from ap_interface_groups_s *--
1553 --* : 3. Delete records from fv_ipac_import which does not belong to the *--
1554 --* : parameter alc code. *--
1555 --* : 4. Delete records from fv_ipac_import where transction type is in *--
1556 --* : A or P. *--
1557 --* : 5. Update the who coulumns and other columns with relevant *--
1558 --* : information *--
1559 --****************************************************************************************--
1560 PROCEDURE pre_process_data
1561 (
1562 p_data_file_name IN VARCHAR2,
1563 p_agency_location_code IN VARCHAR2,
1564 p_batch_name OUT NOCOPY VARCHAR2,
1565 p_group_id OUT NOCOPY NUMBER,
1566 p_error_code OUT NOCOPY NUMBER,
1567 p_error_desc OUT NOCOPY VARCHAR2
1568 )
1569 IS
1570 l_module_name VARCHAR2(200);
1571 l_location VARCHAR2(400);
1572 l_rowcount NUMBER;
1573 BEGIN
1574 l_module_name := g_module_name || 'pre_process_data';
1575 p_error_code := g_SUCCESS;
1576 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1577 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
1578 END IF;
1579
1580 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1581 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_agency_location_code = '||p_agency_location_code);
1582 END IF;
1583
1584 IF (p_error_code = g_SUCCESS) THEN
1585 -- determine batch name for ipac payment interface submission.
1586 BEGIN
1587 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1588 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting ap_interface_groups_s');
1589 END IF;
1590 SELECT 'IPAC'||TO_CHAR(fv_ipac_batch_s.NEXTVAL),
1591 ap_interface_groups_s.NEXTVAL
1592 INTO p_batch_name,
1593 p_group_id
1594 FROM dual;
1595
1596 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1597 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_batch_name = '||p_batch_name);
1598 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_group_id = '||p_group_id);
1599 END IF;
1600 EXCEPTION
1601 WHEN OTHERS THEN
1602 p_error_code := g_FAILURE;
1603 p_error_desc := SQLERRM;
1604 l_location := l_module_name||'.select_fv_ipac_batch_s_nextval';
1605 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1606 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1607 END;
1608 END IF;
1609
1610 IF (p_error_code = g_SUCCESS) THEN
1611 BEGIN
1612 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1613 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Updating fv_ipac_import');
1614 END IF;
1615 UPDATE fv_ipac_import
1616 SET request_id = g_request_id,
1617 record_status = g_status_preprocessed,
1618 created_by = g_user_id,
1619 last_updated_by = g_user_id,
1620 last_update_date = SYSDATE,
1621 batch_name = p_batch_name,
1622 org_id = g_org_id,
1623 set_of_books_id = g_set_of_books_id,
1624 group_id = p_group_id,
1625 data_file = p_data_file_name
1626 WHERE request_id = -1
1627 AND record_status = g_status_imported;
1628 l_rowcount := SQL%ROWCOUNT;
1629 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1630 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Updated '||l_rowcount||' rows.');
1631 END IF;
1632 EXCEPTION
1633 WHEN OTHERS THEN
1634 p_error_code := g_FAILURE;
1635 p_error_desc := SQLERRM;
1636 l_location := l_module_name||'.update_fv_ipac_import';
1637 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1638 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1639 END;
1640 END IF;
1641
1642 /*
1643 IF (p_error_code = g_SUCCESS) THEN
1644 BEGIN
1645 -- Delete all 'A' and 'P' records and keep only valid disbursement records
1646 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1647 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Deleting fv_ipac_import');
1648 END IF;
1649 DELETE fv_ipac_import
1650 WHERE group_id = p_group_id
1651 AND record_status = g_status_preprocessed
1652 AND (transaction_type IN ('A', 'P')
1653 OR customer_alc <> p_Agency_Location_Code);
1654 l_rowcount := SQL%ROWCOUNT;
1655 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1656 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Deleted '||l_rowcount||' rows.');
1657 END IF;
1658 EXCEPTION
1659 WHEN OTHERS THEN
1660 p_error_code := g_FAILURE;
1661 p_error_desc := SQLERRM;
1662 fv_utility.log_mesg(fnd_log.level_unexpected, l_module_name||'.update_fv_ipac_import',p_error_desc) ;
1663 END;
1664 END IF;
1665 */
1666
1667 IF (p_error_code = g_SUCCESS) THEN
1668 -- Reject all 'A' and 'P' records and keep only valid disbursement records
1669 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1670 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Rejecting fv_ipac_import for A and P records');
1671 END IF;
1672 FOR del_rec IN (SELECT ipac_import_id
1673 FROM fv_ipac_import
1674 WHERE group_id = p_group_id
1675 AND record_status = g_status_preprocessed
1676 AND transaction_type IN ('A', 'P')) LOOP
1677 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1678 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_error');
1679 END IF;
1680 insert_error
1681 (
1682 p_ipac_import_id => del_rec.ipac_import_id,
1683 p_validation_code => 'INVALID_TXN_TYPE',
1684 p_validation_err => 'Transaction type is A or P.',
1685 p_error_code => p_error_code,
1686 p_error_desc => p_error_desc
1687 );
1688
1689 BEGIN
1690 UPDATE fv_ipac_import
1691 SET record_status = g_status_error
1692 WHERE ipac_import_id = del_rec.ipac_import_id;
1693 EXCEPTION
1694 WHEN OTHERS THEN
1695 p_error_code := g_FAILURE;
1696 p_error_desc := SQLERRM;
1697 l_location := l_module_name||'.update_fv_ipac_import1';
1698 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1699 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1700 END;
1701 END LOOP;
1702 END IF;
1703
1704 IF (p_error_code = g_SUCCESS) THEN
1705 -- Reject all non selected ALC records
1706 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1707 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Rejecting not selected ALC records');
1708 END IF;
1709 FOR del_rec IN (SELECT ipac_import_id
1710 FROM fv_ipac_import
1711 WHERE group_id = p_group_id
1712 AND record_status = g_status_preprocessed
1713 AND customer_alc <> p_Agency_Location_Code) LOOP
1714 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1715 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_error');
1716 END IF;
1717 insert_error
1718 (
1719 p_ipac_import_id => del_rec.ipac_import_id,
1720 p_validation_code => 'INVALID_ALC',
1721 p_validation_err => 'Customer ALC is not the selected one.',
1722 p_error_code => p_error_code,
1723 p_error_desc => p_error_desc
1724 );
1725
1726 BEGIN
1727 UPDATE fv_ipac_import
1728 SET record_status = g_status_error
1729 WHERE ipac_import_id = del_rec.ipac_import_id;
1730 EXCEPTION
1731 WHEN OTHERS THEN
1732 p_error_code := g_FAILURE;
1733 p_error_desc := SQLERRM;
1734 l_location := l_module_name||'.update_fv_ipac_import1';
1735 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1736 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1737 END;
1738 END LOOP;
1739 END IF;
1740
1741 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1742 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
1743 END IF;
1744
1745 EXCEPTION
1746 WHEN OTHERS THEN
1747 p_error_code := g_FAILURE;
1748 p_error_desc := SQLERRM;
1749 l_location := l_module_name||'.final_exception';
1750 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1751 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1752 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1753 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
1754 END IF;
1755 END;
1756
1757 --****************************************************************************************--
1758 --* Name : process_recurr_invoice *--
1759 --* Type : Procedure *--
1760 --* Purpose : *--
1761 --* Parameters : p_po_num IN PO Number *--
1762 --* : p_po_line_num IN PO Line Number *--
1763 --* : p_amount IN Amount *--
1764 --* : p_batch_name IN Batch Name *--
1765 --* : p_accomplished_date IN Accomplished Date *--
1766 --* : p_invoice_found OUT If invoice is found *--
1767 --* : p_validation_code OUT Validation Code *--
1768 --* : p_validation_err OUT Validation Error *--
1769 --* : p_errbuf Error returned to the concurrent process *--
1770 --* : p_retcode Return Code to concurrent process *--
1771 --* Global Vars : g_module_name READ *--
1772 --* : g_SUCCESS READ *--
1773 --* : g_WARNING READ *--
1774 --* : g_ERROR READ *--
1775 --* : fnd_log.level_statement READ *--
1776 --* : fnd_log.g_current_runtime_level READ *--
1777 --* : fnd_log.level_unexpected READ *--
1778 --* : fnd_log.level_procedure READ *--
1779 --* : g_enter READ *--
1780 --* : g_exit READ *--
1781 --* : g_login_id READ *--
1782 --* : g_user_id READ *--
1783 --* Called from : validate_po *--
1784 --* Calls : fv_utility.debug_mesg *--
1785 --* : fv_utility.log_mesg *--
1786 --* Tables Used : ap_invoices_v VIEW, SELECT *--
1787 --* : ap_recurring_payments_v VIEW, SELECT *--
1788 --* : ap_invoices VIEW, SELECT *--
1789 --* : fv_ipac_recurring_inv SELECT, INSERT *--
1790 --* Logic : Already existing code *--
1791 --****************************************************************************************--
1792 PROCEDURE process_recurr_invoice
1793 (
1794 p_po_num IN VARCHAR2,
1795 p_po_line_num IN NUMBER,
1796 p_amount IN NUMBER,
1797 p_batch_name IN VARCHAR2,
1798 p_accomplished_date IN DATE,
1799 p_invoice_id OUT NOCOPY NUMBER,
1800 p_invoice_found OUT NOCOPY VARCHAR2,
1801 p_validation_code OUT NOCOPY VARCHAR2,
1802 p_validation_err OUT NOCOPY VARCHAR2,
1803 p_error_code OUT NOCOPY NUMBER,
1804 p_error_desc OUT NOCOPY VARCHAR2
1805 )
1806 IS
1807 l_module_name VARCHAR2(200);
1808 l_location VARCHAR2(200);
1809
1810 l_invoice_id ap_invoices.invoice_id%TYPE;
1811 l_approval_code ap_invoices_v.approval_status_lookup_code%TYPE;
1812 l_code VARCHAR2(1);
1813
1814 CURSOR invoice_cur
1815 (
1816 c_po_num VARCHAR2,
1817 c_po_line_num NUMBER,
1818 c_amount NUMBER
1819 )
1820 IS
1821 SELECT ai.invoice_id,
1822 ai.approval_status_lookup_code
1823 FROM ap_invoices_v ai,
1824 ap_recurring_payments_v arp
1825 WHERE arp.recurring_payment_id = ai.recurring_payment_id
1826 AND arp.set_of_books_id = ai.set_of_books_id
1827 AND arp.po_number = c_po_num
1828 AND arp.line_num = NVL(c_po_line_num, 1)
1829 AND ai.source = 'RECURRING INVOICE'
1830 AND ai.invoice_amount = c_amount
1831 AND ai.payment_status_flag = 'N'
1832 AND ai.set_of_books_id = g_set_of_books_id
1833 AND ai.invoice_date = (SELECT MIN(invoice_date)
1834 FROM ap_invoices aib,
1835 ap_recurring_payments_v arpb
1836 WHERE aib.source = 'RECURRING INVOICE'
1837 AND aib.set_of_books_id = g_set_of_books_id
1838 AND aib.payment_status_flag = 'N'
1839 AND aib.invoice_amount = c_amount
1840 AND aib.recurring_payment_id = arpb.recurring_payment_id
1841 AND aib.set_of_books_id = arpb.set_of_books_id
1842 AND arpb.po_number = c_po_num
1843 AND arp.line_num = NVL(c_po_line_num,1))
1844 UNION
1845 SELECT DISTINCT ai.invoice_id,
1846 ai.approval_status_lookup_code
1847 FROM ap_invoices_v ai,
1848 ap_invoice_distributions_v ali
1849 WHERE ali.po_number = c_po_num
1850 AND ai.invoice_id = ali.invoice_id
1851 AND ali.po_line_number = NVL(c_po_line_num,1)
1852 AND ai.invoice_amount = c_amount
1853 AND ai.payment_status_flag = 'N'
1854 AND ai.set_of_books_id = g_set_of_books_id
1855 AND ai.source <> 'RECURRING INVOICE'
1856 AND ai.invoice_type_lookup_code = 'STANDARD'
1857 AND ai.invoice_date = (SELECT MIN(invoice_date)
1858 FROM ap_invoices_v ai1,
1859 ap_invoice_distributions_v ali1
1860 WHERE ali1.po_number = c_po_num
1861 AND ai1.invoice_id = ali1.invoice_id
1862 AND ali1.po_line_number = NVL(c_po_line_num,1)
1863 AND ai1.invoice_amount = c_amount
1864 AND ai1.payment_status_flag = 'N'
1865 AND ai1.set_of_books_id = g_set_of_books_id
1866 AND ai1.source <> 'RECURRING INVOICE'
1867 AND ai1.invoice_type_lookup_code = 'STANDARD');
1868
1869 BEGIN
1870
1871 l_module_name := g_module_name || 'process_recurr_invoice';
1872 p_error_code := g_SUCCESS;
1873 p_validation_code := NULL;
1874 p_error_desc := NULL;
1875 p_invoice_found := 'N';
1876 p_invoice_id := NULL;
1877 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1878 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
1879 END IF;
1880 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1881 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_po_num = '||p_po_num);
1882 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_po_line_num = '||p_po_line_num);
1883 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_amount = '||p_amount);
1884 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_batch_name = '||p_batch_name);
1885 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_accomplished_date = '||TO_CHAR(p_accomplished_date, 'MM/DD/YYYY'));
1886 END IF;
1887
1888 -- find an invoice for this recurring payment template.
1889 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1890 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Opening cursor invoice_cur');
1891 END IF;
1892 OPEN invoice_cur (p_po_num, p_po_line_num, p_amount);
1893 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1894 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Fetching cursor invoice_cur');
1895 END IF;
1896 FETCH invoice_cur INTO l_invoice_id, l_approval_code;
1897
1898 IF invoice_cur%NOTFOUND THEN
1899 NULL;
1900 -- -- this is an exception since no invoice was found
1901 -- fv_utility.log_mesg(fnd_log.level_exception, l_module_name,'No Invoice Found so exception.') ;
1902 -- p_validation_err := 'Cannot find a valid recurring invoice for this Purchase Order';
1903 -- p_validation_code := 'PO_NUM_FOUND_IN_RECURRING_INV';
1904 ELSE
1905 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1906 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_invoice_id = '||l_invoice_id);
1907 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_approval_code = '||l_approval_code);
1908 END IF;
1909
1910 -- found an invoice. find approval status of invoice
1911 IF l_approval_code = 'APPROVED' THEN
1912 l_code := 'P'; -- create payment only
1913 ELSIF l_approval_code in ('UNAPPROVED','NEVER APPROVED') THEN
1914 l_code := 'V'; -- validate invoice and create payment
1915 ELSE
1916 l_code := NULL;
1917 -- this is an exception category since we can't find an invoice to use
1918
1919 fv_utility.log_mesg(fnd_log.level_exception, l_module_name,'Cannot find an correct invoice');
1920 p_validation_err := 'Cannot find a valid recurring invoice for this Purchase Order';
1921 p_validation_code := 'PO_NUM_FOUND_IN_RECURRING_INV';
1922 END IF;
1923
1924 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1925 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_code = '||l_code);
1926 END IF;
1927
1928 IF (l_code in ('V','P')) THEN
1929 -- insert this record if the invoice isn't already there
1930 -- the automatic payment program will refer to this table to create
1931 -- a payment for this invoice.
1932 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1933 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Inserting into fv_ipac_recurring_inv');
1934 END IF;
1935 INSERT INTO fv_ipac_recurring_inv
1936 (
1937 batch_name,
1938 invoice_id,
1939 invoice_action,
1940 accomplish_date,
1941 creation_date,
1942 created_by,
1943 last_update_date,
1944 last_update_login,
1945 last_updated_by
1946 )
1947 SELECT p_batch_name,
1948 l_invoice_id,
1949 l_code,
1950 p_accomplished_date, --using accomplish date (attribute11)of po
1951 SYSDATE,
1952 g_user_id,
1953 SYSDATE,
1954 g_login_id,
1955 g_user_id
1956 FROM dual
1957 WHERE NOT EXISTS (SELECT invoice_id
1958 FROM fv_ipac_recurring_inv
1959 WHERE invoice_id = l_invoice_id
1960 AND batch_name = p_batch_name);
1961
1962 p_invoice_found := 'Y';
1963 p_invoice_id := l_invoice_id;
1964 END IF;
1965 END IF;
1966
1967 CLOSE invoice_cur;
1968 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1969 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
1970 END IF;
1971
1972 EXCEPTION
1973 WHEN OTHERS THEN
1974 p_error_code := g_FAILURE;
1975 p_error_desc := SQLERRM;
1976 l_location := l_module_name||'.final_exception';
1977 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1978 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1979 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1980 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
1981 END IF;
1982 END process_recurr_invoice;
1983
1984 --****************************************************************************************--
1985 --* Name : resolve_uom *--
1986 --* Type : Procedure *--
1987 --* Purpose : This procedure will try to resolve the Unit of Measure against the *--
1988 --* : table mtl_units_of_measure_vl *--
1989 --* Parameters : p_ipac_import_id IN The ipac import id used to insert errors *--
1990 --* : p_uom_code IN The file UOM code *--
1991 --* : p_unit_of_measure IN OUT Resolved UOM code *--
1992 --* : p_record_status OUT Record Status *--
1993 --* : p_errbuf Error returned to the concurrent process *--
1994 --* : p_retcode Return Code to concurrent process *--
1995 --* Global Vars : g_module_name READ *--
1996 --* : g_SUCCESS READ *--
1997 --* : g_ERROR READ *--
1998 --* : fnd_log.level_statement READ *--
1999 --* : fnd_log.g_current_runtime_level READ *--
2000 --* : fnd_log.level_unexpected READ *--
2001 --* : fnd_log.level_procedure READ *--
2002 --* : g_enter READ *--
2003 --* : g_exit READ *--
2004 --* Called from : process_data *--
2005 --* Calls : insert_error *--
2006 --* : fv_utility.debug_mesg *--
2007 --* : fv_utility.log_mesg *--
2008 --* Tables Used : mtl_units_of_measure_vl VIEW, SELECT *--
2009 --* Logic : 1. Check to see if the input UOM code is uom_code from table *--
2010 --* : mtl_units_of_measure_vl *--
2011 --* : 2. If so return the unit of measure from the table in resolved UOM *--
2012 --* : code and return *--
2013 --* : 3. Check to see if the input UOM code is unit_of_measure from table *--
2014 --* : mtl_units_of_measure_vl *--
2015 --* : 4. If so return the unit of measure from the table in resolved UOM *--
2016 --* : code and return *--
2017 --* : 5. If the UOM could not be resolved insert an error *--
2018 --****************************************************************************************--
2019 PROCEDURE resolve_uom
2020 (
2021 p_ipac_import_id IN fv_ipac_import.ipac_import_id%TYPE,
2022 p_uom_code IN mtl_units_of_measure_vl.uom_code%TYPE,
2023 p_unit_of_measure IN OUT NOCOPY mtl_units_of_measure_vl.unit_of_measure%TYPE,
2024 p_record_status OUT NOCOPY fv_ipac_import.record_status%TYPE,
2025 p_error_code OUT NOCOPY NUMBER,
2026 p_error_desc OUT NOCOPY VARCHAR2
2027 )
2028 IS
2029 l_module_name VARCHAR2(200);
2030 l_location VARCHAR2(200);
2031 l_unit_of_measure mtl_units_of_measure_vl.unit_of_measure%TYPE;
2032 BEGIN
2033 l_module_name := g_module_name || 'resolve_uom';
2034 p_error_code := g_SUCCESS;
2035 l_unit_of_measure := NULL;
2036 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2037 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
2038 END IF;
2039 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2040 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_uom_code = '||p_uom_code);
2041 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_unit_of_measure = '||p_unit_of_measure);
2042 END IF;
2043
2044 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2045 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from mtl_units_of_measure_vl1');
2046 END IF;
2047
2048 BEGIN
2049 SELECT unit_of_measure
2050 INTO l_unit_of_measure
2051 FROM mtl_units_of_measure_vl
2052 WHERE uom_code = p_uom_code;
2053 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2054 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_unit_of_measure='||l_unit_of_measure);
2055 END IF;
2056 EXCEPTION
2057 WHEN NO_DATA_FOUND THEN
2058 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2059 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'no data found');
2060 END IF;
2061 l_unit_of_measure := NULL;
2062 WHEN OTHERS THEN
2063 p_error_code := g_FAILURE;
2064 p_error_desc := SQLERRM;
2065 l_location := l_module_name||'.select_mtl_units_of_measure_vl1';
2066 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2067 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
2068 END;
2069
2070 IF (p_error_code = g_SUCCESS) THEN
2071 IF (l_unit_of_measure IS NOT NULL) THEN
2072 p_unit_of_measure := l_unit_of_measure;
2073 ELSE
2074 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2075 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from mtl_units_of_measure_vl1');
2076 END IF;
2077 BEGIN
2078 SELECT unit_of_measure
2079 INTO l_unit_of_measure
2080 FROM mtl_units_of_measure_vl
2081 WHERE unit_of_measure = p_unit_of_measure;
2082 EXCEPTION
2083 WHEN NO_DATA_FOUND THEN
2084 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2085 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'no data found');
2086 END IF;
2087 l_unit_of_measure := NULL;
2088 WHEN OTHERS THEN
2089 p_error_code := g_FAILURE;
2090 p_error_desc := SQLERRM;
2091 l_location := l_module_name||'.select_mtl_units_of_measure_vl2';
2092 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2093 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
2094 END;
2095 END IF;
2096 END IF;
2097 IF (p_error_code = g_SUCCESS) THEN
2098 IF (l_unit_of_measure IS NULL) THEN
2099 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2100 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_error');
2101 END IF;
2102 insert_error
2103 (
2104 p_ipac_import_id => p_ipac_import_id,
2105 p_validation_code => 'INVALID_UOM',
2106 p_validation_err => 'Invalid Unit of Measure',
2107 p_error_code => p_error_code,
2108 p_error_desc => p_error_desc
2109 );
2110 IF (p_error_code = g_SUCCESS) THEN
2111 p_record_status := g_status_error;
2112 END IF;
2113 END IF;
2114 END IF;
2115
2116 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2117 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_unit_of_measure='||p_unit_of_measure);
2118 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_record_status='||p_record_status);
2119 END IF;
2120 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2121 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
2122 END IF;
2123
2124 EXCEPTION
2125 WHEN OTHERS THEN
2126 p_error_code := g_FAILURE;
2127 p_error_desc := SQLERRM;
2128 l_location := l_module_name||'.final_exception';
2129 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2130 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
2131 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2132 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
2133 END IF;
2134 END;
2135
2136
2137 --****************************************************************************************--
2138 --* Name : validate_duns *--
2139 --* Type : Procedure *--
2140 --* Purpose : Validated the DUNS and DUNS PLUS FOUR code against CCR *--
2141 --* Parameters : p_ipac_import_rec IN OUT The IPAC import record *--
2142 --* : p_errbuf OUT Error returned to the concurrent process *--
2143 --* : p_retcode OUT Return Code to concurrent process *--
2144 --* Global Vars : g_module_name READ *--
2145 --* : g_SUCCESS READ *--
2146 --* : g_ERROR READ *--
2147 --* : fnd_log.level_statement READ *--
2148 --* : fnd_log.g_current_runtime_level READ *--
2149 --* : fnd_log.level_unexpected READ *--
2150 --* : fnd_log.level_procedure READ *--
2151 --* : g_enter READ *--
2152 --* : g_exit READ *--
2153 --* Called from : process_data *--
2154 --* Calls : insert_error *--
2155 --* : fv_utility.debug_mesg *--
2156 --* : fv_utility.log_mesg *--
2157 --* Tables Used : fv_ccr_vendors SELECT *--
2158 --* : po_vendor_sites SELECT *--
2159 --* Logic : 1. Check against fv_ccr_vendors to see if a record exists with the *--
2160 --* : specific vendor id duns and duns+4 combination *--
2161 --* : 2. If no record exists then provide an error DUNS_NOT_SETUP and *--
2162 --* : insert the error and return. *--
2163 --* : 3. Get the vendor site id using the duns and duns+4 combinatiion *--
2164 --* : from po_vendor_sites table *--
2165 --* : 4. If no record exists then provide an error INAVALID_SITE_CODE and *--
2166 --* : insert the error and return. *--
2167 --* : 5. If the site id does not match with the PO site id, then provide *--
2168 --* : an error PO_SITE_MISMATCH and insert the error and return *--
2169 --****************************************************************************************--
2170 PROCEDURE validate_duns
2171 (
2172 p_ipac_import_rec IN OUT NOCOPY fv_ipac_import%ROWTYPE,
2173 p_error_code OUT NOCOPY NUMBER,
2174 p_error_desc OUT NOCOPY VARCHAR2
2175 )
2176 IS
2177 l_module_name VARCHAR2(200);
2178 l_location VARCHAR2(200);
2179 l_dummy VARCHAR2(1);
2180 l_validation_code fv_ipac_import_errors.error_code%TYPE;
2181 l_validation_err fv_ipac_import_errors.error_desc%TYPE;
2182 l_vendor_site_id po_vendor_sites.vendor_site_id%TYPE;
2183 BEGIN
2184 l_module_name := g_module_name || 'validate_duns';
2185 p_error_code := g_SUCCESS;
2186 p_error_desc := NULL;
2187 l_validation_code := NULL;
2188
2189 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2190 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
2191 END IF;
2192
2193 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2194 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_ipac_import_rec.vendor_id='||p_ipac_import_rec.vendor_id);
2195 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_ipac_import_rec.receiver_duns='||p_ipac_import_rec.receiver_duns);
2196 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_ipac_import_rec.receiver_duns_4='||p_ipac_import_rec.receiver_duns_4);
2197 END IF;
2198
2199 IF (p_error_code = g_SUCCESS) THEN
2200 BEGIN
2201 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2202 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Select fv_ccr_vendors');
2203 END IF;
2204 SELECT 'X'
2205 INTO l_dummy
2206 FROM fv_ccr_vendors fcv
2207 WHERE fcv.vendor_id = p_ipac_import_rec.vendor_id
2208 AND fcv.duns = p_ipac_import_rec.receiver_duns
2209 AND NVL(fcv.plus_four, '-1') = NVL(p_ipac_import_rec.receiver_duns_4, '-1');
2210 EXCEPTION
2211 WHEN NO_DATA_FOUND THEN
2212 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2213 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'no data found');
2214 END IF;
2215 l_validation_err := 'Invalid DUNS and DUNS PLUS FOUR combination';
2216 l_validation_code := 'DUNS_NOT_SETUP';
2217 WHEN OTHERS THEN
2218 p_error_code := g_FAILURE;
2219 p_error_desc := SQLERRM;
2220 l_location := l_module_name||'.select_po_headers';
2221 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2222 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
2223 END;
2224 END IF;
2225
2226 IF (p_error_code = g_SUCCESS AND l_validation_code IS NULL) THEN
2227 BEGIN
2228 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2229 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Select po_vendor_sites');
2230 END IF;
2231 SELECT vendor_site_id
2232 INTO l_vendor_site_id
2233 FROM po_vendor_sites pvs
2234 WHERE pvs.vendor_id = p_ipac_import_rec.vendor_id
2235 AND pvs.vendor_site_code = p_ipac_import_rec.receiver_duns||p_ipac_import_rec.receiver_duns_4;
2236 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2237 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_vendor_site_id='||l_vendor_site_id);
2238 END IF;
2239 EXCEPTION
2240 WHEN NO_DATA_FOUND THEN
2241 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2242 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'no data found');
2243 END IF;
2244 l_validation_err := 'Invalid Site Code for DUNS and DUNS PLUS FOUR combination';
2245 l_validation_code := 'INAVALID_SITE_CODE';
2246 WHEN OTHERS THEN
2247 p_error_code := g_FAILURE;
2248 p_error_desc := SQLERRM;
2249 l_location := l_module_name||'.select_po_headers';
2250 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2251 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
2252 END;
2253 END IF;
2254
2255 IF (p_error_code = g_SUCCESS AND l_validation_code IS NULL) THEN
2256 IF (l_vendor_site_id <> p_ipac_import_rec.vendor_site_id) THEN
2257 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2258 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'no match');
2259 END IF;
2260 l_validation_err := 'Purchase Order SITE does not match the DUNS Site';
2261 l_validation_code := 'PO_SITE_MISMATCH';
2262 END IF;
2263 END IF;
2264
2265 IF (l_validation_code IS NOT NULL AND p_error_code = g_SUCCESS) THEN
2266 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2267 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_error');
2268 END IF;
2269 insert_error
2270 (
2271 p_ipac_import_id => p_ipac_import_rec.ipac_import_id,
2272 p_validation_code => l_validation_code,
2273 p_validation_err => l_validation_err,
2274 p_error_code => p_error_code,
2275 p_error_desc => p_error_desc
2276 );
2277 IF (p_error_code = g_SUCCESS) THEN
2278 p_ipac_import_rec.record_status := g_status_error;
2279 END IF;
2280 END IF;
2281
2282 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2283 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_ipac_import_rec.record_status='||p_ipac_import_rec.record_status);
2284 END IF;
2285
2286 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2287 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
2288 END IF;
2289 EXCEPTION
2290 WHEN OTHERS THEN
2291 p_error_code := g_FAILURE;
2292 p_error_desc := SQLERRM;
2293 l_location := l_module_name||'.final_exception';
2294 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2295 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
2296 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2297 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
2298 END IF;
2299 END;
2300
2301 --****************************************************************************************--
2302 --* Name : validate_po *--
2303 --* Type : Procedure *--
2304 --* Purpose : This procedure validates the PO number and Line Number *--
2305 --* Parameters : p_ipac_import_rec IN OUT The import record *--
2306 --* : p_ap_inv_lines_rec IN OUT Invoice Lines record *--
2307 --* : p_errbuf OUT Error returned to the concurrent process *--
2308 --* : p_retcode OUT Return Code to concurrent process *--
2309 --* Global Vars : g_module_name READ *--
2310 --* : g_SUCCESS READ *--
2311 --* : g_ERROR READ *--
2312 --* : fnd_log.level_statement READ *--
2313 --* : fnd_log.g_current_runtime_level READ *--
2314 --* : fnd_log.level_unexpected READ *--
2315 --* : fnd_log.level_procedure READ *--
2316 --* : g_enter READ *--
2317 --* : g_exit READ *--
2318 --* Called from : process_data *--
2319 --* Calls : process_recurr_invoice *--
2320 --* : insert_error *--
2321 --* : fv_utility.debug_mesg *--
2322 --* : fv_utility.log_mesg *--
2323 --* Tables Used : po_headers SELECT *--
2324 --* : po_distributions SELECT *--
2325 --* : po_lines SELECT *--
2326 --* : ap_invoice_distributions SELECT *--
2327 --* : ap_recurring_payments_v SELECT *--
2328 --* Logic : *--
2329 --****************************************************************************************--
2330 PROCEDURE validate_po
2331 (
2332 p_ipac_import_rec IN OUT NOCOPY fv_ipac_import%ROWTYPE,
2333 p_ap_inv_lines_rec IN OUT NOCOPY ap_invoice_lines_interface%ROWTYPE,
2334 p_error_code OUT NOCOPY NUMBER,
2335 p_error_desc OUT NOCOPY VARCHAR2
2336 )
2337 IS
2338 l_module_name VARCHAR2(200);
2339 l_location VARCHAR2(200);
2340 l_hdr_closed_code po_headers.closed_code%TYPE;
2341 l_line_closed_code po_lines.closed_code%TYPE;
2342 l_count_po_disb_lines NUMBER;
2343 l_count_matched_disb_lines NUMBER;
2344 l_exists NUMBER;
2345 l_validation_code fv_ipac_import_errors.error_code%TYPE;
2346 l_validation_err fv_ipac_import_errors.error_desc%TYPE;
2347 l_invoice_found VARCHAR2(1);
2348 l_amount NUMBER;
2349 l_recurr_invoice_id NUMBER;
2350 BEGIN
2351 l_module_name := g_module_name || 'validate_po';
2352 p_error_code := g_SUCCESS;
2353 p_error_desc := NULL;
2354 l_invoice_found := 'N';
2355
2356 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2357 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
2358 END IF;
2359 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2360 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'po_number = '||p_ipac_import_rec.purchase_order_number);
2361 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'batch_name = '||p_ipac_import_rec.batch_name);
2362 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'accomplished_date = '||p_ipac_import_rec.accomplished_date);
2363 END IF;
2364
2365 -- Isolate the po_number if the po_number value contains both po_number and line_number
2366 IF INSTR(p_ipac_import_rec.purchase_order_number,'/',1,1) > 0 THEN
2367 p_ap_inv_lines_rec.po_number := SUBSTR(p_ipac_import_rec.purchase_order_number,1,INSTR(p_ipac_import_rec.purchase_order_number,'/',1,1)-1) ;
2368 ELSE
2369 p_ap_inv_lines_rec.po_number := p_ipac_import_rec.purchase_order_number;
2370 END IF;
2371
2372 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2373 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'derived po_number = '||p_ap_inv_lines_rec.po_number);
2374 END IF;
2375
2376 IF (p_error_code = g_SUCCESS) THEN
2377 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2378 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from po_headers');
2379 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'segment1='||p_ap_inv_lines_rec.po_number);
2380 END IF;
2381 BEGIN
2382 SELECT po_header_id,
2383 closed_code,
2384 vendor_id,
2385 vendor_site_id
2386 INTO p_ap_inv_lines_rec.po_header_id,
2387 l_hdr_closed_code,
2388 p_ipac_import_rec.vendor_id,
2389 p_ipac_import_rec.vendor_site_id
2390 FROM po_headers
2391 WHERE segment1 = p_ap_inv_lines_rec.po_number;
2392 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2393 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'po_header_id = '||p_ap_inv_lines_rec.po_header_id);
2394 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'closed_code = '||l_hdr_closed_code);
2395 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'vendor_id = '||p_ipac_import_rec.vendor_id);
2396 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'vendor_site_id = '||p_ipac_import_rec.vendor_site_id);
2397 END IF;
2398 IF (l_hdr_closed_code IN ('CLOSED','FINALLY CLOSED')) THEN
2399 l_validation_err := 'Purchase Order is closed';
2400 l_validation_code := 'PO_CLOSED';
2401 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2402 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Validation Error:'||l_validation_code);
2403 END IF;
2404 END IF;
2405 EXCEPTION
2406 WHEN NO_DATA_FOUND THEN
2407 l_validation_err := 'Invalid Purchase Order';
2408 l_validation_code := 'INVALID_PO_NUM';
2409 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2410 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'NO DATA Validation Error:'||l_validation_code);
2411 END IF;
2412 WHEN OTHERS THEN
2413 p_error_code := g_FAILURE;
2414 p_error_desc := SQLERRM;
2415 l_location := l_module_name||'.select_po_headers';
2416 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2417 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
2418 END;
2419 END IF;
2420
2421 IF (p_error_code = g_SUCCESS AND l_validation_code IS NULL) THEN
2422 BEGIN
2423 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2424 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from po_distributions and lines');
2425 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'po_header_id='||p_ap_inv_lines_rec.po_header_id);
2426 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'set_of_books_id='||g_set_of_books_id);
2427 END IF;
2428
2429 SELECT COUNT(*)
2430 INTO l_count_po_disb_lines
2431 FROM po_distributions pd,
2432 po_lines pl
2433 WHERE pd.po_header_id = p_ap_inv_lines_rec.po_header_id
2434 AND pl.po_line_id = pd.po_line_id
2435 AND pd.set_of_books_id = g_set_of_books_id;
2436
2437 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2438 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_count_po_disb_lines='||l_count_po_disb_lines);
2439 END IF;
2440
2441 EXCEPTION
2442 WHEN OTHERS THEN
2443 p_error_code := g_FAILURE;
2444 p_error_desc := SQLERRM;
2445 l_location := l_module_name||'.select_po_distributions1';
2446 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2447 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
2448 END;
2449 END IF;
2450
2451 IF (p_error_code = g_SUCCESS AND l_validation_code IS NULL) THEN
2452 BEGIN
2453 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2454 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from po_distributions, lines and ap_invoice_distributions');
2455 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'po_header_id='||p_ap_inv_lines_rec.po_header_id);
2456 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'set_of_books_id='||g_set_of_books_id);
2457 END IF;
2458
2459 SELECT COUNT(*)
2460 INTO l_count_matched_disb_lines
2461 FROM ap_invoice_distributions aid ,
2462 po_distributions pd ,
2463 po_lines pl
2464 WHERE aid.po_distribution_id = pd.po_distribution_id
2465 AND pd.po_header_id = p_ap_inv_lines_rec.po_header_id
2466 AND pl.po_line_id = pd.po_line_id
2467 AND aid.final_match_flag = 'D'
2468 AND aid.set_of_books_id = pd.set_of_books_id
2469 AND aid.set_of_books_id = g_set_of_books_id ;
2470
2471 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2472 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_count_matched_disb_lines='||l_count_matched_disb_lines);
2473 END IF;
2474 EXCEPTION
2475 WHEN OTHERS THEN
2476 p_error_code := g_FAILURE;
2477 p_error_desc := SQLERRM;
2478 l_location := l_module_name||'.select_po_distributions2';
2479 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2480 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
2481 END;
2482 END IF;
2483
2484 IF (p_error_code = g_SUCCESS AND l_validation_code IS NULL) THEN
2485 IF (l_count_po_disb_lines = l_count_matched_disb_lines) AND (l_count_matched_disb_lines <> 0) THEN
2486 l_validation_err := 'Purchase Order is finally matched';
2487 l_validation_code := 'PO_FINALLY_MATCHED';
2488 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2489 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Validation Error:'||l_validation_code);
2490 END IF;
2491 END IF;
2492 END IF;
2493
2494 IF (p_error_code = g_SUCCESS AND l_validation_code IS NULL) THEN
2495 IF INSTR(p_ipac_import_rec.purchase_order_number,'/',1,1) = 0 THEN
2496 p_ap_inv_lines_rec.po_line_number := NULL;
2497 ELSE
2498 BEGIN
2499 p_ap_inv_lines_rec.po_line_number := TO_NUMBER(SUBSTR(p_ipac_import_rec.purchase_order_number,INSTR(p_ipac_import_rec.purchase_order_number,'/',1,1)+1));
2500 EXCEPTION
2501 WHEN VALUE_ERROR THEN
2502 p_ap_inv_lines_rec.po_line_number := NULL;
2503 l_validation_err := 'Invalid Purchase Order Line Number';
2504 l_validation_code := 'INVALID_LINE_NUM';
2505 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2506 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Validation Error:'||l_validation_code);
2507 END IF;
2508 WHEN OTHERS THEN
2509 p_error_code := g_FAILURE;
2510 p_error_desc := SQLERRM;
2511 l_location := l_module_name||'.check_po_line_number';
2512 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2513 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
2514 END;
2515 END IF;
2516 END IF;
2517
2518 IF (p_error_code = g_SUCCESS AND l_validation_code IS NULL) THEN
2519 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2520 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'po_line_number='||p_ap_inv_lines_rec.po_line_number);
2521 END IF;
2522 BEGIN
2523 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2524 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from po_lines');
2525 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'po_header_id = '||p_ap_inv_lines_rec.po_header_id);
2526 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'line_num = '||p_ap_inv_lines_rec.po_line_number);
2527 END IF;
2528
2529 SELECT pl.po_line_id,
2530 pl.closed_code,
2531 pl.unit_meas_lookup_code
2532 INTO p_ap_inv_lines_rec.po_line_id,
2533 l_line_closed_code,
2534 p_ap_inv_lines_rec.po_unit_of_measure
2535 FROM po_lines pl
2536 WHERE po_header_id = p_ap_inv_lines_rec.po_header_id
2537 AND line_num = NVL(p_ap_inv_lines_rec.po_line_number, 1);
2538
2539 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2540 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from po_lines');
2541 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'po_line_id = '||p_ap_inv_lines_rec.po_line_id);
2542 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'closed_code = '||l_line_closed_code);
2543 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'unit_meas_lookup_code = '||p_ap_inv_lines_rec.po_unit_of_measure);
2544 END IF;
2545
2546 IF (l_line_closed_code IN ('CLOSED','FINALLY CLOSED')) THEN
2547 l_validation_err := 'Purchase Order Line Number is closed';
2548 l_validation_code := 'PO_LINE_NUM_CLOSED';
2549 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2550 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Validation Error:'||l_validation_code);
2551 END IF;
2552 END IF;
2553 EXCEPTION
2554 WHEN NO_DATA_FOUND THEN
2555 l_validation_err := 'Invalid Purchase Order Line Number';
2556 l_validation_code := 'INVALID_LINE_NUM';
2557 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2558 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'NO DATA Validation Error:'||l_validation_code);
2559 END IF;
2560 WHEN OTHERS THEN
2561 p_error_code := g_FAILURE;
2562 p_error_desc := SQLERRM;
2563 l_location := l_module_name||'.select_po_lines';
2564 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2565 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
2566 END;
2567 END IF;
2568
2569 IF (p_error_code = g_SUCCESS AND l_validation_code IS NULL) THEN
2570 BEGIN
2571 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2572 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from po_distributions');
2573 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'po_line_id = '||p_ap_inv_lines_rec.po_line_id);
2574 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'set_of_books_id = '||g_set_of_books_id);
2575 END IF;
2576
2577 SELECT COUNT(*)
2578 INTO l_count_po_disb_lines
2579 FROM po_distributions pd
2580 WHERE pd.po_line_id = p_ap_inv_lines_rec.po_line_id
2581 AND pd.set_of_books_id = g_set_of_books_id;
2582
2583 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2584 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_count_po_disb_lines='||l_count_po_disb_lines);
2585 END IF;
2586
2587 EXCEPTION
2588 WHEN OTHERS THEN
2589 p_error_code := g_FAILURE;
2590 p_error_desc := SQLERRM;
2591 l_location := l_module_name||'.select_po_distributions3';
2592 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2593 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
2594 END;
2595 END IF;
2596
2597 IF (p_error_code = g_SUCCESS AND l_validation_code IS NULL) THEN
2598 BEGIN
2599 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2600 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from po_distributions and ap_invoice_distributions');
2601 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'po_line_id = '||p_ap_inv_lines_rec.po_line_id);
2602 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'set_of_books_id = '||g_set_of_books_id);
2603 END IF;
2604 SELECT COUNT(*)
2605 INTO l_count_matched_disb_lines
2606 FROM ap_invoice_distributions aid ,
2607 po_distributions pd
2608 WHERE aid.po_distribution_id = pd.po_distribution_id
2609 AND pd.po_line_id = p_ap_inv_lines_rec.po_line_id
2610 AND aid.final_match_flag = 'D'
2611 AND aid.set_of_books_id = pd.set_of_books_id
2612 AND aid.set_of_books_id = g_set_of_books_id;
2613 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2614 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_count_matched_disb_lines='||l_count_matched_disb_lines);
2615 END IF;
2616 EXCEPTION
2617 WHEN OTHERS THEN
2618 p_error_code := g_FAILURE;
2619 p_error_desc := SQLERRM;
2620 l_location := l_module_name||'.select_po_distributions4';
2621 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2622 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
2623 END;
2624 END IF;
2625
2626 IF (p_error_code = g_SUCCESS AND l_validation_code IS NULL) THEN
2627 IF (l_count_po_disb_lines = l_count_matched_disb_lines) AND (l_count_matched_disb_lines <> 0) THEN
2628 l_validation_err := 'Purchase Order Line Number is finally matched';
2629 l_validation_code := 'PO_LINE_NUM_FINALLY_MATCHED';
2630 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2631 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Validation Error:'||l_validation_code);
2632 END IF;
2633 END IF;
2634 END IF;
2635
2636
2637 IF (p_error_code = g_SUCCESS AND l_validation_code IS NULL) THEN
2638 /*
2639 --Check whether Recurring Invoice has been created for
2640 --the PO_NUMBER and LINE_NUMBER.
2641 BEGIN
2642 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2643 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from ap_recurring_payments_v');
2644 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'po_line_id = '||p_ap_inv_lines_rec.po_line_id);
2645 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'po_header_id = '||p_ap_inv_lines_rec.po_header_id);
2646 END IF;
2647 l_exists := 0;
2648 SELECT COUNT(*)
2649 INTO l_exists
2650 FROM ap_recurring_payments_v aprpv
2651 WHERE aprpv.po_header_id = p_ap_inv_lines_rec.po_header_id
2652 AND aprpv.po_line_id = p_ap_inv_lines_rec.po_line_id;
2653 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2654 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_exists='||l_exists);
2655 END IF;
2656 EXCEPTION
2657 WHEN OTHERS THEN
2658 p_error_code := g_FAILURE;
2659 p_error_desc := SQLERRM;
2660 l_location := l_module_name||'.ap_recurring_payments_v';
2661 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2662 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
2663 END ;
2664 */
2665 --process recurring invoice;
2666 -- IF (p_error_code = g_SUCCESS AND l_exists > 0) THEN
2667 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2668 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling process_recurr_invoice');
2669 END IF;
2670 process_recurr_invoice
2671 (
2672 p_po_num => p_ap_inv_lines_rec.po_number,
2673 p_po_line_num => p_ap_inv_lines_rec.po_line_number,
2674 p_amount => p_ipac_import_rec.summary_amount,
2675 p_batch_name => p_ipac_import_rec.batch_name,
2676 p_accomplished_date => p_ipac_import_rec.accomplished_date,
2677 p_invoice_id => l_recurr_invoice_id,
2678 p_invoice_found => l_invoice_found,
2679 p_validation_code => l_validation_code,
2680 p_validation_err => l_validation_err,
2681 p_error_code => p_error_code,
2682 p_error_desc => p_error_desc
2683 );
2684 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2685 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'process_recurr_invoice returned');
2686 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code ='||p_error_code);
2687 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc ='||p_error_desc);
2688 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_invoice_found ='||l_invoice_found);
2689 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_validation_code ='||l_validation_code);
2690 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_validation_err ='||l_validation_err);
2691 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_recurr_invoice_id ='||l_recurr_invoice_id);
2692 END IF;
2693 -- END IF;
2694 END IF;
2695
2696 IF (l_validation_code IS NOT NULL AND p_error_code = g_SUCCESS) THEN
2697 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2698 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_error');
2699 END IF;
2700 insert_error
2701 (
2702 p_ipac_import_id => p_ipac_import_rec.ipac_import_id,
2703 p_validation_code => l_validation_code,
2704 p_validation_err => l_validation_err,
2705 p_error_code => p_error_code,
2706 p_error_desc => p_error_desc
2707 );
2708 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2709 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'insert_error returned');
2710 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code ='||p_error_code);
2711 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc ='||p_error_desc);
2712 END IF;
2713 IF (p_error_code = g_SUCCESS) THEN
2714 p_ipac_import_rec.record_status := g_status_error;
2715 END IF;
2716 END IF;
2717
2718 IF (l_invoice_found = 'Y') THEN
2719 p_ipac_import_rec.record_status := g_status_processed;
2720 p_ipac_import_rec.invoice_id := l_recurr_invoice_id;
2721 END IF;
2722
2723 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2724 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_ipac_import_rec.record_status ='||p_ipac_import_rec.record_status);
2725 END IF;
2726
2727 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2728 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
2729 END IF;
2730 EXCEPTION
2731 WHEN OTHERS THEN
2732 p_error_code := g_FAILURE;
2733 p_error_desc := SQLERRM;
2734 l_location := l_module_name||'.final_exception';
2735 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2736 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
2737 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2738 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
2739 END IF;
2740 END;
2741
2742
2743 --****************************************************************************************--
2744 --* Name : save_or_erase_invoice *--
2745 --* Type : Procedure *--
2746 --* Purpose : This Procedure either saves the invoice or deletes it depending *--
2747 --* : on the flag p_okay_to_insert_inv *--
2748 --* Parameters : p_ap_inv_hdr_rec IN The invoice header record to be inserted *--
2749 --* : p_previous_inv_number IN Previous invoice number *--
2750 --* : p_okay_to_insert_inv IN Is it okay to insert the invoice *--
2751 --* : p_total_invoice_lines IN Total Invoice Lines *--
2752 --* : p_total_invoices IN OUT Total Invoices *--
2753 --* : p_errbuf OUT Error returned to the concurrent process *--
2754 --* : p_retcode OUT Return Code to concurrent process *--
2755 --* Global Vars : g_module_name READ *--
2756 --* : g_SUCCESS READ *--
2757 --* : g_WARNING READ *--
2758 --* : g_ERROR READ *--
2759 --* : fnd_log.level_statement READ *--
2760 --* : fnd_log.g_current_runtime_level READ *--
2761 --* : fnd_log.level_unexpected READ *--
2762 --* : fnd_log.level_procedure READ *--
2763 --* : g_enter READ *--
2764 --* : g_exit READ *--
2765 --* Called from : process_data *--
2766 --* Calls : insert_invoice_hdr *--
2767 --* : fv_utility.debug_mesg *--
2768 --* : fv_utility.log_mesg *--
2769 --* Tables Used : ap_invoice_lines_interface DELETE *--
2770 --* Logic : 1. If the previous invoice number is not null and if it is okay to *--
2771 --* : insert the invoice, then insert the invoice header *--
2772 --* : 2. If the previous invoice number is not null and if it is not okay *--
2773 --* : to insert the invoice and if there are more than 0 lines already *--
2774 --* : inserted, delete them from ap_invoice_lines_interface *--
2775 --****************************************************************************************--
2776 PROCEDURE save_or_erase_invoice
2777 (
2778 p_ap_inv_hdr_rec IN ap_invoices_interface%ROWTYPE,
2779 p_previous_inv_number IN ap_invoices_interface.invoice_num%TYPE,
2780 p_okay_to_insert_inv IN VARCHAR2,
2781 p_total_invoice_lines IN NUMBER,
2782 p_total_invoices IN OUT NOCOPY NUMBER,
2783 p_error_code OUT NOCOPY NUMBER,
2784 p_error_desc OUT NOCOPY VARCHAR2
2785 )
2786 IS
2787 l_module_name VARCHAR2(200);
2788 l_location VARCHAR2(200);
2789 l_rowcount NUMBER;
2790 BEGIN
2791 l_module_name := g_module_name || 'save_or_erase_invoice';
2792 p_error_code := g_SUCCESS;
2793 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2794 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
2795 END IF;
2796
2797 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2798 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'p_previous_inv_number = '||p_previous_inv_number);
2799 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'p_okay_to_insert_inv = '||p_okay_to_insert_inv);
2800 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'p_total_invoice_lines = '||p_total_invoice_lines);
2801 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'p_total_invoices(IN) = '||p_total_invoices);
2802 END IF;
2803
2804 IF ((p_previous_inv_number IS NOT NULL) AND (p_okay_to_insert_inv = 'Y')) THEN
2805 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2806 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_invoice_hdr');
2807 END IF;
2808 insert_invoice_hdr
2809 (
2810 p_invoice_hdr_rec => p_ap_inv_hdr_rec,
2811 p_error_code => p_error_code,
2812 p_error_desc => p_error_desc
2813 );
2814 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2815 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'insert_invoice_hdr returned');
2816 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code ='||p_error_code);
2817 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc ='||p_error_desc);
2818 END IF;
2819 p_total_invoices := p_total_invoices + 1;
2820 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2821 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_total_invoices='||p_total_invoices);
2822 END IF;
2823 END IF;
2824
2825 --****************************************************************************************--
2826 -- Delete all invoice lines for the previous invoice if there was an error *--
2827 --****************************************************************************************--
2828 IF (p_error_code = g_SUCCESS) THEN
2829 IF ((p_previous_inv_number IS NOT NULL) AND (p_okay_to_insert_inv = 'N')) THEN
2830 IF (p_total_invoice_lines > 0) THEN
2831 BEGIN
2832 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2833 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Deleting ap_invoice_lines_interface');
2834 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'invoice_id='||p_ap_inv_hdr_rec.invoice_id);
2835 END IF;
2836
2837 DELETE ap_invoice_lines_interface
2838 WHERE invoice_id = p_ap_inv_hdr_rec.invoice_id;
2839 l_rowcount := SQL%ROWCOUNT;
2840 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2841 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from ap_invoice_lines_interface.');
2842 END IF;
2843 EXCEPTION
2844 WHEN OTHERS THEN
2845 p_error_code := g_FAILURE;
2846 p_error_desc := SQLERRM;
2847 l_location := l_module_name||'delete_ap_invoice_lines_interface';
2848 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2849 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
2850 END;
2851 END IF;
2852 END IF;
2853 END IF;
2854
2855 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2856 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'p_total_invoices(OUT) = '||p_total_invoices);
2857 END IF;
2858 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2859 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
2860 END IF;
2861 EXCEPTION
2862 WHEN OTHERS THEN
2863 p_error_code := g_FAILURE;
2864 p_error_desc := SQLERRM;
2865 l_location := l_module_name||'.final_exception';
2866 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2867 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
2868 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2869 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
2870 END IF;
2871 END;
2872
2873 --****************************************************************************************--
2874 --* Name : process_data *--
2875 --* Type : Procedure *--
2876 --* Purpose : This is the main process that does all the data validations *--
2877 --* Parameters : p_data_file_name IN The data file name *--
2878 --* : p_transaction_code IN The transaction code *--
2879 --* : p_agency_location_code IN The Agency location code *--
2880 --* : p_batch_name OUT Batch name *--
2881 --* : p_group_id OUT Group Id *--
2882 --* : p_ok_to_import OUT Is it okay to do ap import *--
2883 --* : p_errbuf OUT Error returned to the concurrent process *--
2884 --* : p_retcode OUT Return Code to concurrent process *--
2885 --* Global Vars : g_module_name READ *--
2886 --* : g_SUCCESS READ *--
2887 --* : g_WARNING READ *--
2888 --* : g_ERROR READ *--
2889 --* : fnd_log.level_statement READ *--
2890 --* : fnd_log.g_current_runtime_level READ *--
2891 --* : fnd_log.level_unexpected READ *--
2892 --* : fnd_log.level_procedure READ *--
2893 --* : g_enter READ *--
2894 --* : g_exit READ *--
2895 --* Called from : main *--
2896 --* Calls : save_or_erase_invoice *--
2897 --* : validate_po *--
2898 --* : validate_duns *--
2899 --* : resolve_uom *--
2900 --* : insert_invoice_line *--
2901 --* : fv_utility.debug_mesg *--
2902 --* : fv_utility.log_mesg *--
2903 --* Sequence Used : ap_invoices_interface_s *--
2904 --* Tables Used : fv_ipac_import SELECT, UPDATE *--
2905 --* Logic : 1. Pre process the data to get the group_id handle *--
2906 --* : 2. Process every record from fv_ipac_import table *--
2907 --* : 3. Validate the PO. *--
2908 --* : 4. Validate the DUNS *--
2909 --* : 5. Resolve the UOM *--
2910 --* : 6. If all is well insert data into ap interface tables. *--
2911 --****************************************************************************************--
2912 PROCEDURE process_data
2913 (
2914 p_data_file_name IN VARCHAR2,
2915 p_agency_location_code IN VARCHAR2,
2916 p_batch_name OUT NOCOPY VARCHAR2,
2917 p_group_id OUT NOCOPY NUMBER,
2918 p_ok_to_import OUT NOCOPY VARCHAR2,
2919 p_error_code OUT NOCOPY NUMBER,
2920 p_error_desc OUT NOCOPY VARCHAR2
2921 )
2922 IS
2923 l_module_name VARCHAR2(200);
2924 l_location VARCHAR2(200);
2925 l_ipac_import_record fv_ipac_import%ROWTYPE;
2926 l_inv_hdrs_interface_rec ap_invoices_interface%ROWTYPE;
2927 l_inv_lines_interface_rec ap_invoice_lines_interface%ROWTYPE;
2928 l_inv_hdrs_interface_rec_null ap_invoices_interface%ROWTYPE;
2929 l_inv_lines_interface_rec_null ap_invoice_lines_interface%ROWTYPE;
2930 l_save_invoice_number fv_ipac_import.invoice_number%TYPE;
2931 l_ok_to_insert_inv VARCHAR2(1);
2932 l_current_inv_lines NUMBER;
2933 l_no_of_invoices_inserted NUMBER;
2934 l_rowcount NUMBER;
2935
2936 CURSOR ipac_import_cursor
2937 (
2938 c_group_id NUMBER
2939 )IS
2940 SELECT *
2941 FROM fv_ipac_import fii
2942 WHERE fii.group_id = c_group_id
2943 AND fii.record_status = g_status_preprocessed
2944 ORDER BY fii.invoice_number;
2945 BEGIN
2946 l_module_name := g_module_name || 'process_data';
2947 p_error_code := g_SUCCESS;
2948 l_save_invoice_number := NULL;
2949 p_ok_to_import := 'N';
2950 l_ok_to_insert_inv := 'Y';
2951 l_current_inv_lines := 0;
2952 l_no_of_invoices_inserted := 0;
2953
2954 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2955 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
2956 END IF;
2957
2958 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2959 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_data_file_name = '||p_data_file_name);
2960 -- fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_transaction_code = '||p_transaction_code);
2961 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_agency_location_code = '||p_agency_location_code);
2962 END IF;
2963
2964 IF (p_error_code = g_SUCCESS) THEN
2965 --****************************************************************************************--
2966 --* Pre process the data to get a group_id handle on the data and to update the who *--
2967 --* who columns *--
2968 --****************************************************************************************--
2969 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2970 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling pre_process_data');
2971 END IF;
2972 pre_process_data
2973 (
2974 p_data_file_name => p_data_file_name,
2975 p_agency_location_code => p_agency_location_code,
2976 p_batch_name => p_batch_name,
2977 p_group_id => p_group_id,
2978 p_error_code => p_error_code,
2979 p_error_desc => p_error_desc
2980 );
2981 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2982 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'pre_process_data returned');
2983 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code ='||p_error_code);
2984 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc ='||p_error_desc);
2985 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_group_id ='||p_group_id);
2986 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_batch_name ='||p_batch_name);
2987 END IF;
2988 END IF;
2989
2990 IF (p_error_code = g_SUCCESS) THEN
2991 --****************************************************************************************--
2992 --* Initialize all contants values for invoice lines *--
2993 --****************************************************************************************--
2994 l_inv_lines_interface_rec_null.last_updated_by := g_user_id;
2995 l_inv_lines_interface_rec_null.created_by := g_user_id;
2996 l_inv_lines_interface_rec_null.creation_date := SYSDATE;
2997 l_inv_lines_interface_rec_null.last_update_date := SYSDATE;
2998 l_inv_lines_interface_rec_null.line_type_lookup_code := 'ITEM';
2999 --****************************************************************************************--
3000 --* Initialize all contants values for invoice header *--
3001 --****************************************************************************************--
3002 l_inv_hdrs_interface_rec_null.last_updated_by := g_user_id;
3003 l_inv_hdrs_interface_rec_null.created_by := g_user_id;
3004 l_inv_hdrs_interface_rec_null.creation_date := SYSDATE;
3005 l_inv_hdrs_interface_rec_null.last_update_date := SYSDATE;
3006 l_inv_hdrs_interface_rec_null.invoice_amount := 0;
3007 l_inv_hdrs_interface_rec_null.payment_method_code := 'CLEARING';
3008 l_inv_hdrs_interface_rec_null.pay_group_lookup_code := g_ia_paygroup;
3009 l_inv_hdrs_interface_rec_null.source := 'IPAC';
3010 l_inv_hdrs_interface_rec_null.invoice_received_date := SYSDATE;
3011 l_inv_hdrs_interface_rec_null.invoice_type_lookup_code := 'STANDARD';
3012 l_inv_hdrs_interface_rec_null.invoice_currency_code := 'USD';
3013 END IF;
3014
3015 IF (p_error_code = g_SUCCESS) THEN
3016 --****************************************************************************************--
3017 --* Start processing all the ipac import records *--
3018 --****************************************************************************************--
3019 FOR ipac_import_record IN ipac_import_cursor (p_group_id) LOOP
3020 l_ipac_import_record := ipac_import_record;
3021 l_ipac_import_record.record_status := g_status_no_process;
3022 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3023 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Processing Invoice = '||l_ipac_import_record.invoice_number);
3024 END IF;
3025
3026 IF (p_error_code = g_SUCCESS) THEN
3027
3028 --****************************************************************************************--
3029 --* The current invoice is saved and written only once all the lines are identified. *--
3030 --* This is to avoid another database access as the total invoice amount has to be *--
3031 --* determined. Once the invoice number changes, the old invoice header has to be *--
3032 --* written to the database. *--
3033 --****************************************************************************************--
3034
3035 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3036 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_save_invoice_number='||l_save_invoice_number);
3037 END IF;
3038 IF ((l_save_invoice_number IS NULL) OR
3039 (l_save_invoice_number <> l_ipac_import_record.invoice_number)) THEN
3040
3041 --****************************************************************************************--
3042 --* Write the invoice header or delete invoice lines depending on the flag *--
3043 --* l_ok_to_insert_inv *--
3044 --****************************************************************************************--
3045 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3046 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling save_or_erase_invoice');
3047 END IF;
3048 save_or_erase_invoice
3049 (
3050 p_ap_inv_hdr_rec => l_inv_hdrs_interface_rec,
3051 p_previous_inv_number => l_save_invoice_number,
3052 p_okay_to_insert_inv => l_ok_to_insert_inv,
3053 p_total_invoice_lines => l_current_inv_lines,
3054 p_total_invoices => l_no_of_invoices_inserted,
3055 p_error_code => p_error_code,
3056 p_error_desc => p_error_desc
3057 );
3058 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3059 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling save_or_erase_invoice returned');
3060 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code ='||p_error_code);
3061 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc ='||p_error_desc);
3062 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_no_of_invoices_inserted ='||l_no_of_invoices_inserted);
3063 END IF;
3064
3065 --****************************************************************************************--
3066 --* Initialize all variables for processing a new invoice header *--
3067 --****************************************************************************************--
3068 IF (p_error_code = g_SUCCESS) THEN
3069 l_ok_to_insert_inv := 'Y';
3070 l_current_inv_lines := 0;
3071
3072 l_inv_hdrs_interface_rec := l_inv_hdrs_interface_rec_null;
3073 l_inv_hdrs_interface_rec.group_id := p_group_id;
3074 l_save_invoice_number := l_ipac_import_record.invoice_number;
3075 l_inv_hdrs_interface_rec.invoice_num := l_ipac_import_record.invoice_number;
3076 l_inv_hdrs_interface_rec.invoice_date := l_ipac_import_record.accomplished_date;
3077 END IF;
3078
3079 IF (p_error_code = g_SUCCESS) THEN
3080 BEGIN
3081 --****************************************************************************************--
3082 --* get the invoice header sequence number *--
3083 --****************************************************************************************--
3084 SELECT ap_invoices_interface_s.NEXTVAL
3085 INTO l_inv_hdrs_interface_rec.invoice_id
3086 FROM DUAL;
3087 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3088 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'invoice_id ='||l_inv_hdrs_interface_rec.invoice_id);
3089 END IF;
3090 EXCEPTION
3091 WHEN OTHERS THEN
3092 p_error_code := g_FAILURE;
3093 p_error_desc := SQLERRM;
3094 l_location := l_module_name||'select_ap_invoices_interface_s';
3095 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3096 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3097 END;
3098 END IF;
3099 END IF;
3100 END IF;
3101
3102 --****************************************************************************************--
3103 --* Store all interface lines record and also keep on adding invoice header amount *--
3104 --****************************************************************************************--
3105 IF (p_error_code = g_SUCCESS) THEN
3106 l_inv_lines_interface_rec := l_inv_lines_interface_rec_null;
3107 l_inv_lines_interface_rec.invoice_id := l_inv_hdrs_interface_rec.invoice_id;
3108 l_inv_lines_interface_rec.line_number := l_ipac_import_record.detail_line_number;
3109 l_inv_lines_interface_rec.unit_price := l_ipac_import_record.unit_price;
3110 l_inv_lines_interface_rec.quantity_invoiced := l_ipac_import_record.quantity;
3111 l_inv_lines_interface_rec.amount := l_ipac_import_record.detail_amount;
3112 l_inv_hdrs_interface_rec.invoice_amount := l_inv_hdrs_interface_rec.invoice_amount +
3113 l_ipac_import_record.detail_amount;
3114 --Bug 7213192
3115 l_inv_lines_interface_rec.description := SUBSTR(l_ipac_import_record.description,1,240);
3116 --l_inv_lines_interface_rec.description := l_ipac_import_record.description;
3117 -- TC Obsoletion
3118 -- l_inv_lines_interface_rec.ussgl_transaction_code := p_transaction_code;
3119 IF (l_inv_lines_interface_rec.description IS NULL) THEN
3120 l_inv_lines_interface_rec.description := 'IPAC Disbursement Ref. Number: '||
3121 l_ipac_import_record.ipac_doc_ref_number||
3122 ' Quantity: '||
3123 l_inv_lines_interface_rec.quantity_invoiced||
3124 ' Unit Price: '||
3125 l_ipac_import_record.unit_price||
3126 ' Contract Num: '||
3127 l_ipac_import_record.contract_number;
3128 END IF;
3129 END IF;
3130
3131 --****************************************************************************************--
3132 --* Validate the purchase order and populate the relevant information such as *--
3133 --* po_header_id, po_line_id, po_number, po_line_number, vendor_id and vendor_site_id *--
3134 --* back to l_ipac_import_record and l_inv_lines_interface_rec *--
3135 --****************************************************************************************--
3136 IF (p_error_code = g_SUCCESS) THEN
3137 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3138 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling validate_po');
3139 END IF;
3140 validate_po
3141 (
3142 p_ipac_import_rec => l_ipac_import_record,
3143 p_ap_inv_lines_rec => l_inv_lines_interface_rec,
3144 p_error_code => p_error_code,
3145 p_error_desc => p_error_desc
3146 );
3147 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3148 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling validate_po returned');
3149 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code ='||p_error_code);
3150 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc ='||p_error_desc);
3151 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'record_status ='||l_ipac_import_record.record_status);
3152 END IF;
3153 END IF;
3154
3155 --****************************************************************************************--
3156 --* validate_po will set the status of l_ipac_import_record to ERROR if there was any *--
3157 --* validation error. If the invoice is already part of reecurring invoice then the *--
3158 --* status will be set to PROCESSED. In both these cases, no new invoice should be *--
3159 --* created and hence the flag l_ok_to_insert_inv to be set to 'N'. *--
3160 --****************************************************************************************--
3161 IF (p_error_code = g_SUCCESS) THEN
3162 IF (l_ipac_import_record.record_status IN (g_status_error, g_status_processed)) THEN
3163 l_ok_to_insert_inv := 'N';
3164 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3165 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Going to FINISHED_PROCESS');
3166 END IF;
3167 GOTO FINISHED_PROCESS;
3168 END IF;
3169
3170 --****************************************************************************************--
3171 --* vendor_id and vendor_site_id was populated in l_ipac_import_record by validate_po *--
3172 --* Since l_inv_hdrs_interface_rec was not passed to validate_po and since this info *--
3173 --* is required by the header, populate them from l_ipac_import_record *--
3174 --****************************************************************************************--
3175 l_inv_hdrs_interface_rec.vendor_id := l_ipac_import_record.vendor_id;
3176 l_inv_hdrs_interface_rec.vendor_site_id := l_ipac_import_record.vendor_site_id;
3177 END IF;
3178
3179 IF (l_ipac_import_record.receiver_duns IS NOT NULL) THEN
3180 --****************************************************************************************--
3181 --* Validate the receiver DUNS and DUNS+4 information. *--
3182 --****************************************************************************************--
3183 IF (p_error_code = g_SUCCESS) THEN
3184 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3185 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling validate_duns');
3186 END IF;
3187 validate_duns
3188 (
3189 p_ipac_import_rec => l_ipac_import_record,
3190 p_error_code => p_error_code,
3191 p_error_desc => p_error_desc
3192 );
3193 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3194 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling validate_duns returned');
3195 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code ='||p_error_code);
3196 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc ='||p_error_desc);
3197 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'record_status ='||l_ipac_import_record.record_status);
3198 END IF;
3199 END IF;
3200
3201 --****************************************************************************************--
3202 --* validate_duns will set the status of l_ipac_import_record to ERROR if there was any *--
3203 --* validation error. *--
3204 --****************************************************************************************--
3205 IF (p_error_code = g_SUCCESS) THEN
3206 IF (l_ipac_import_record.record_status = g_status_error) THEN
3207 l_ok_to_insert_inv := 'N';
3208 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3209 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Going to FINISHED_PROCESS');
3210 END IF;
3211 GOTO FINISHED_PROCESS;
3212 END IF;
3213 END IF;
3214 END IF;
3215
3216 --****************************************************************************************--
3217 --* The UOM passed in the file is of 2 characters which possibly maps to the UOM in po *--
3218 --* But the invoice lines interface requires unit_of_measure and not uom *--
3219 --* Also make sure that the PO UOM is same as File UOM *--
3220 --****************************************************************************************--
3221 IF (p_error_code = g_SUCCESS AND l_ok_to_insert_inv = 'Y') THEN
3222 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3223 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling resolve_uom');
3224 END IF;
3225 resolve_uom
3226 (
3227 p_ipac_import_id => l_ipac_import_record.ipac_import_id,
3228 p_uom_code => l_ipac_import_record.unit_of_issue,
3229 p_unit_of_measure => l_inv_lines_interface_rec.po_unit_of_measure,
3230 p_record_status => l_ipac_import_record.record_status,
3231 p_error_code => p_error_code,
3232 p_error_desc => p_error_desc
3233 );
3234 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3235 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling resolve_uom returned');
3236 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code ='||p_error_code);
3237 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc ='||p_error_desc);
3238 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_unit_of_measure ='||l_inv_lines_interface_rec.po_unit_of_measure);
3239 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'record_status ='||l_ipac_import_record.record_status);
3240 END IF;
3241 END IF;
3242
3243 IF (p_error_code = g_SUCCESS) THEN
3244 IF (l_ipac_import_record.record_status = g_status_error) THEN
3245 l_ok_to_insert_inv := 'N';
3246 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3247 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Going to FINISHED_PROCESS');
3248 END IF;
3249 GOTO FINISHED_PROCESS;
3250 END IF;
3251 END IF;
3252
3253 --****************************************************************************************--
3254 --* If all is well, insert the invoice line *--
3255 --* Remember we haven't inserted the header record unitl now. This happens when the *--
3256 --* invoice number changes. *--
3257 --****************************************************************************************--
3258 IF (p_error_code = g_SUCCESS AND l_ok_to_insert_inv = 'Y') THEN
3259 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3260 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_invoice_line');
3261 END IF;
3262 insert_invoice_line
3263 (
3264 p_invoice_lines_rec => l_inv_lines_interface_rec,
3265 p_error_code => p_error_code,
3266 p_error_desc => p_error_desc
3267 );
3268 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3269 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_invoice_line returned');
3270 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code ='||p_error_code);
3271 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc ='||p_error_desc);
3272 END IF;
3273 l_current_inv_lines := l_current_inv_lines + 1;
3274 l_ipac_import_record.record_status := g_status_processed;
3275 ELSE
3276 l_ipac_import_record.record_status := g_status_other_error;
3277 END IF;
3278
3279 <<FINISHED_PROCESS>>
3280 IF (p_error_code = g_SUCCESS) THEN
3281 BEGIN
3282 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3283 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Updating fv_ipac_import');
3284 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'record_status='||l_ipac_import_record.record_status);
3285 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'invoice_id='||l_ipac_import_record.invoice_id);
3286 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'invoice_line_id='||l_ipac_import_record.invoice_line_id);
3287 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'actual_po_number='||l_ipac_import_record.actual_po_number);
3288 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'actual_po_line_number='||l_ipac_import_record.actual_po_line_number);
3289 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'vendor_id='||l_ipac_import_record.vendor_id);
3290 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'vendor_site_id='||l_ipac_import_record.vendor_site_id);
3291 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'ipac_import_id='||l_ipac_import_record.ipac_import_id);
3292 END IF;
3293 UPDATE fv_ipac_import fii
3294 SET record_status = l_ipac_import_record.record_status,
3295 invoice_id = l_ipac_import_record.invoice_id,
3296 int_invoice_id = l_inv_lines_interface_rec.invoice_id,
3297 int_invoice_line_id = l_inv_lines_interface_rec.invoice_line_id,
3298 actual_po_number = l_inv_lines_interface_rec.po_number,
3299 actual_po_line_number = l_inv_lines_interface_rec.po_line_number,
3300 vendor_id = l_inv_hdrs_interface_rec.vendor_id,
3301 vendor_site_id = l_inv_hdrs_interface_rec.vendor_site_id
3302 WHERE fii.ipac_import_id = l_ipac_import_record.ipac_import_id;
3303 l_rowcount := SQL%ROWCOUNT;
3304 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3305 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Updated '||l_rowcount||' rows in fv_ipac_import.');
3306 END IF;
3307 EXCEPTION
3308 WHEN OTHERS THEN
3309 p_error_code := g_FAILURE;
3310 p_error_desc := SQLERRM;
3311 l_location := l_module_name||'update_fv_ipac_import';
3312 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3313 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3314 END;
3315 END IF;
3316
3317 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3318 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'p_error_code1='||p_error_code);
3319 END IF;
3320 IF (p_error_code <> g_SUCCESS) THEN
3321 EXIT;
3322 END IF;
3323
3324 END LOOP;
3325
3326 --Insert the last invoice too.
3327 IF (p_error_code = g_SUCCESS) THEN
3328 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3329 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling save_or_erase_invoice1');
3330 END IF;
3331 save_or_erase_invoice
3332 (
3333 p_ap_inv_hdr_rec => l_inv_hdrs_interface_rec,
3334 p_previous_inv_number => l_save_invoice_number,
3335 p_okay_to_insert_inv => l_ok_to_insert_inv,
3336 p_total_invoice_lines => l_current_inv_lines,
3337 p_total_invoices => l_no_of_invoices_inserted,
3338 p_error_code => p_error_code,
3339 p_error_desc => p_error_desc
3340 );
3341 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3342 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling save_or_erase_invoice returned');
3343 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code ='||p_error_code);
3344 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc ='||p_error_desc);
3345 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_no_of_invoices_inserted ='||l_no_of_invoices_inserted);
3346 END IF;
3347 END IF;
3348 END IF;
3349
3350 IF (l_no_of_invoices_inserted > 0) THEN
3351 p_ok_to_import := 'Y';
3352 END IF;
3353
3354 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3355 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_batch_name='||p_batch_name);
3356 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_group_id='||p_group_id);
3357 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_ok_to_import='||p_ok_to_import);
3358 END IF;
3359
3360 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3361 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
3362 END IF;
3363
3364 EXCEPTION
3365 WHEN OTHERS THEN
3366 p_error_code := g_FAILURE;
3367 p_error_desc := SQLERRM;
3368 l_location := l_module_name||'.final_exception';
3369 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3370 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3371 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3372 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
3373 END IF;
3374 END;
3375
3376 --****************************************************************************************--
3377 --* Name : cleanup_previous_failed_run *--
3378 --* Type : Procedure *--
3379 --* Purpose : Cleanup previously failed run with records in status IMPORTED *--
3380 --* Parameters : p_errbuf Error returned to the concurrent process *--
3381 --* : p_retcode Return Code to concurrent process *--
3382 --* Global Vars : g_module_name READ *--
3383 --* : g_SUCCESS READ *--
3384 --* : g_ERROR READ *--
3385 --* : fnd_log.level_statement READ *--
3386 --* : fnd_log.g_current_runtime_level READ *--
3387 --* : fnd_log.level_unexpected READ *--
3388 --* : fnd_log.level_procedure READ *--
3389 --* : g_enter READ *--
3390 --* : g_exit READ *--
3391 --* Called from : load_data_file *--
3392 --* Calls : fv_utility.debug_mesg *--
3393 --* : fv_utility.log_mesg *--
3394 --* Tables Used : fv_ipac_import DELETE *--
3395 --* Logic : Delete all records from fv_ipac_import table with status IMPORTED *--
3396 --****************************************************************************************--
3397 PROCEDURE cleanup_previous_failed_run
3398 (
3399 p_error_code OUT NOCOPY NUMBER,
3400 p_error_desc OUT NOCOPY VARCHAR2
3401 )
3402 IS
3403 l_module_name VARCHAR2(200);
3404 l_location VARCHAR2(200);
3405 l_rowcount NUMBER;
3406 BEGIN
3407 l_module_name := g_module_name || 'cleanup_previous_failed_run';
3408 p_error_code := g_SUCCESS;
3409 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3410 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
3411 END IF;
3412
3413 BEGIN
3414 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3415 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Deleting fv_ipac_import');
3416 END IF;
3417 DELETE fv_ipac_import
3418 WHERE record_status = 'IMPORTED';
3419 l_rowcount := SQL%ROWCOUNT;
3420 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3421 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from fv_ipac_import.');
3422 END IF;
3423 EXCEPTION
3424 WHEN OTHERS THEN
3425 p_error_code := g_FAILURE;
3426 p_error_desc := SQLERRM;
3427 l_location := l_module_name||'.delete_fv_ipac_import';
3428 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3429 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3430 END;
3431 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3432 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
3433 END IF;
3434
3435 EXCEPTION
3436 WHEN OTHERS THEN
3437 p_error_code := g_FAILURE;
3438 p_error_desc := SQLERRM;
3439 l_location := l_module_name||'.final_exception';
3440 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3441 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3442 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3443 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
3444 END IF;
3445 END;
3446
3447 --****************************************************************************************--
3448 --* Name : cleanup_current_failed_run *--
3449 --* Type : Procedure *--
3450 --* Purpose : This procedure removes data from ap_invoices_interface, *--
3451 --* : ap_invoice_lines_interface and ap_interface_rejections after a *--
3452 --* : failed run *--
3453 --* Parameters : p_group_id IN The group id for which the data is processed *--
3454 --* : p_errbuf OUT Error returned to the concurrent process *--
3455 --* : p_retcode OUT Return Code to concurrent process *--
3456 --* Global Vars : g_module_name READ *--
3457 --* : g_SUCCESS READ *--
3458 --* : g_ERROR READ *--
3459 --* : fnd_log.level_statement READ *--
3460 --* : fnd_log.g_current_runtime_level READ *--
3461 --* : fnd_log.level_unexpected READ *--
3462 --* : fnd_log.level_procedure READ *--
3463 --* : g_enter READ *--
3464 --* : g_exit READ *--
3465 --* Called from : main *--
3466 --* Calls : fv_utility.debug_mesg *--
3467 --* : fv_utility.log_mesg *--
3468 --* Tables Used : fv_ipac_import SELECT, UPDATE *--
3469 --* : ap_interface_rejections DELETE *--
3470 --* : ap_invoice_lines_interface DELETE *--
3471 --* : ap_invoices_interface DELETE *--
3472 --* Logic : For each invoice number with status of ERROR in fv_ipac_import table *--
3473 --* : do the following *--
3474 --* : 1. Delete all data from ap_interface_rejections for lines *--
3475 --* : 2. Delete all data from ap_interface_rejections for header *--
3476 --* : 3. Delete all data from ap_invoice_lines_interface *--
3477 --* : 4. Delete all data from ap_invoices_interface *--
3478 --* : 5. Update all other records of fv_ipac_import table with same *--
3479 --* : invoice and status of not ERROR to ERROR IN OTHER LINES *--
3480 --****************************************************************************************--
3481 PROCEDURE cleanup_current_failed_run
3482 (
3483 p_group_id IN NUMBER,
3484 p_error_code OUT NOCOPY NUMBER,
3485 p_error_desc OUT NOCOPY VARCHAR2
3486 )
3487 IS
3488 l_module_name VARCHAR2(200);
3489 l_location VARCHAR2(200);
3490 l_rowcount NUMBER;
3491 BEGIN
3492 l_module_name := g_module_name || 'cleanup_current_failed_run';
3493 p_error_code := g_SUCCESS;
3494 RETURN;
3495 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3496 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
3497 END IF;
3498 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3499 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_group_id = '||p_group_id);
3500 END IF;
3501
3502 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3503 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Getting into cursor import_rec');
3504 END IF;
3505
3506 FOR import_rec IN (SELECT invoice_number
3507 FROM fv_ipac_import
3508 WHERE group_id = p_group_id
3509 AND record_status = g_status_error) LOOP
3510
3511 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3512 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Currently Processing Invoice Number = '||import_rec.invoice_number);
3513 END IF;
3514
3515 IF (p_error_code = g_SUCCESS) THEN
3516 BEGIN
3517 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3518 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Deleting ap_interface_rejections1');
3519 END IF;
3520 DELETE ap_interface_rejections
3521 WHERE parent_table = 'AP_INVOICE_LINES_INTERFACE'
3522 AND parent_id IN (SELECT invoice_line_id
3523 FROM ap_invoice_lines_interface aili,
3524 ap_invoices_interface aii
3525 WHERE aii.invoice_num = import_rec.invoice_number
3526 AND aii.group_id = p_group_id
3527 AND aii.invoice_id = aili.invoice_id);
3528 l_rowcount := SQL%ROWCOUNT;
3529 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3530 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from ap_interface_rejections1.');
3531 END IF;
3532 EXCEPTION
3533 WHEN OTHERS THEN
3534 p_error_code := g_FAILURE;
3535 p_error_desc := SQLERRM;
3536 l_location := l_module_name||'.delete_ap_interface_rejections1';
3537 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3538 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3539 END;
3540 END IF;
3541
3542 IF (p_error_code = g_SUCCESS) THEN
3543 BEGIN
3544 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3545 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Deleting ap_interface_rejections2');
3546 END IF;
3547 DELETE ap_interface_rejections
3548 WHERE parent_table = 'AP_INVOICES_INTERFACE'
3549 AND parent_id IN (SELECT invoice_id
3550 FROM ap_invoices_interface aii
3551 WHERE aii.invoice_num = import_rec.invoice_number
3552 AND aii.group_id = p_group_id);
3553 l_rowcount := SQL%ROWCOUNT;
3554 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3555 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from ap_interface_rejections2.');
3556 END IF;
3557 EXCEPTION
3558 WHEN OTHERS THEN
3559 p_error_code := g_FAILURE;
3560 p_error_desc := SQLERRM;
3561 l_location := l_module_name||'.delete_ap_interface_rejections2';
3562 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3563 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3564 END;
3565 END IF;
3566
3567 IF (p_error_code = g_SUCCESS) THEN
3568 BEGIN
3569 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3570 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Deleting ap_invoice_lines_interface');
3571 END IF;
3572 DELETE ap_invoice_lines_interface
3573 WHERE invoice_id IN (SELECT invoice_id
3574 FROM ap_invoices_interface
3575 WHERE invoice_num = import_rec.invoice_number
3576 AND group_id = p_group_id);
3577 l_rowcount := SQL%ROWCOUNT;
3578 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3579 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from ap_invoice_lines_interface.');
3580 END IF;
3581 EXCEPTION
3582 WHEN OTHERS THEN
3583 p_error_code := g_FAILURE;
3584 p_error_desc := SQLERRM;
3585 l_location := l_module_name||'.delete_ap_invoice_lines_interface';
3586 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3587 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3588 END;
3589 END IF;
3590
3591
3592 IF (p_error_code = g_SUCCESS) THEN
3593 BEGIN
3594 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3595 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Deleting ap_invoices_interface');
3596 END IF;
3597 DELETE ap_invoices_interface
3598 WHERE invoice_num = import_rec.invoice_number
3599 AND group_id = p_group_id;
3600 l_rowcount := SQL%ROWCOUNT;
3601 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3602 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from ap_invoices_interface.');
3603 END IF;
3604 EXCEPTION
3605 WHEN OTHERS THEN
3606 p_error_code := g_FAILURE;
3607 p_error_desc := SQLERRM;
3608 l_location := l_module_name||'.delete_ap_invoices_interface';
3609 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3610 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3611 END;
3612 END IF;
3613
3614 IF (p_error_code = g_SUCCESS) THEN
3615 BEGIN
3616 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3617 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Updating fv_ipac_import');
3618 END IF;
3619 UPDATE fv_ipac_import
3620 SET record_status = g_status_other_error
3621 WHERE group_id = p_group_id
3622 AND invoice_number = import_rec.invoice_number
3623 AND record_status <> g_status_error;
3624 l_rowcount := SQL%ROWCOUNT;
3625 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3626 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Updated '||l_rowcount||' rows in fv_ipac_import.');
3627 END IF;
3628 EXCEPTION
3629 WHEN OTHERS THEN
3630 p_error_code := g_FAILURE;
3631 p_error_desc := SQLERRM;
3632 l_location := l_module_name||'.update_fv_ipac_import';
3633 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3634 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3635 END;
3636 END IF;
3637
3638 IF (p_error_code <> g_SUCCESS) THEN
3639 EXIT;
3640 END IF;
3641
3642 END LOOP;
3643 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3644 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Out of cursor import_rec');
3645 END IF;
3646
3647 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3648 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
3649 END IF;
3650
3651 EXCEPTION
3652 WHEN OTHERS THEN
3653 p_error_code := g_FAILURE;
3654 p_error_desc := SQLERRM;
3655 l_location := l_module_name||'.final_exception';
3656 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3657 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3658 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3659 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
3660 END IF;
3661 END;
3662 --****************************************************************************************--
3663 --* Name : move_data_to_history *--
3664 --* Type : Procedure *--
3665 --* Purpose : This procedure moves all the processsed records from fv_ipac_import *--
3666 --* : table into fv_ipac_import_history table *--
3667 --* Parameters : p_group_id IN The group id for which the data is processed *--
3668 --* : p_errbuf OUT Error returned to the concurrent process *--
3669 --* : p_retcode OUT Return Code to concurrent process *--
3670 --* Global Vars : g_module_name READ *--
3671 --* : g_SUCCESS READ *--
3672 --* : g_ERROR READ *--
3673 --* : fnd_log.level_statement READ *--
3674 --* : fnd_log.g_current_runtime_level READ *--
3675 --* : fnd_log.level_unexpected READ *--
3676 --* : fnd_log.level_procedure READ *--
3677 --* : g_enter READ *--
3678 --* : g_exit READ *--
3679 --* Called from : main *--
3680 --* Calls : fv_utility.debug_mesg *--
3681 --* : fv_utility.log_mesg *--
3682 --* Tables Used : fv_ipac_import_history INSERT *--
3683 --* : fv_ipac_import SELECT, DELETE *--
3684 --* Logic : 1. Insert into fv_ipac_import_history by selecting all processed *--
3685 --* : records from fv_ipac_import *--
3686 --* : 2. Delete all processed records from fv_ipac_import *--
3687 --****************************************************************************************--
3688 PROCEDURE move_data_to_history
3689 (
3690 p_group_id IN NUMBER,
3691 p_error_code OUT NOCOPY NUMBER,
3692 p_error_desc OUT NOCOPY VARCHAR2
3693 )
3694 IS
3695 l_module_name VARCHAR2(200);
3696 l_location VARCHAR2(200);
3697 l_rowcount NUMBER;
3698 BEGIN
3699 l_module_name := g_module_name || 'move_data_to_history';
3700 p_error_code := g_SUCCESS;
3701 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3702 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
3703 END IF;
3704 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3705 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_group_id = '||p_group_id);
3706 END IF;
3707
3708 BEGIN
3709 INSERT INTO fv_ipac_import_history
3710 (
3711 ipac_import_id,
3712 batch_name,
3713 transaction_id,
3714 submitter_alc,
3715 originating_alc,
3716 customer_alc,
3717 contact_name,
3718 contact_email_address,
3719 contact_phone_number,
3720 summary_amount,
3721 numer_of_detail_lines,
3722 accomplished_date,
3723 accounting_date,
3724 detail_line_number,
3725 contract_number,
3726 purchase_order_number,
3727 clin,
3728 invoice_number,
3729 requisition_number,
3730 quantity,
3731 unit_of_issue,
3732 unit_price,
3733 detail_amount,
3734 pay_flag,
3735 fy_obligation_id,
3736 receiver_tres_acct_symbol,
3737 receiver_betc,
3738 receiver_duns,
3739 receiver_duns_4,
3740 sender_tres_acct_symbol,
3741 sender_betc,
3742 sender_duns,
3743 sender_duns_4,
3744 receiver_department_code,
3745 accounting_class_code,
3746 acrn,
3747 job_project_number,
3748 jas_number,
3749 fsn_aaa_adsn,
3750 obligating_doc_number,
3751 act_trace_number,
3752 description,
3753 misc_information,
3754 transaction_type,
3755 ipac_doc_ref_number,
3756 sender_do_symbol,
3757 dodacc,
3758 transaction_contact,
3759 transcation_contact_phone,
3760 voucher_number,
3761 original_do_symbol,
3762 orig_accomplished_date,
3763 orig_accounting_date,
3764 orig_doc_ref_number,
3765 orig_transaction_type,
3766 sender_sgl_comment,
3767 receiver_sgl_comment,
3768 sgl_number1,
3769 sgl_sender_receiver_flag1,
3770 sgl_federal_flag1,
3771 sgl_debit_credit_flag1,
3772 sgl_amount1,
3773 sgl_number2,
3774 sgl_sender_receiver_flag2,
3775 sgl_federal_flag2,
3776 sgl_debit_credit_flag2,
3777 sgl_amount2,
3778 sgl_number3,
3779 sgl_sender_receiver_flag3,
3780 sgl_federal_flag3,
3781 sgl_debit_credit_flag3,
3782 sgl_amount3,
3783 sgl_number4,
3784 sgl_sender_receiver_flag4,
3785 sgl_federal_flag4,
3786 sgl_debit_credit_flag4,
3787 sgl_amount4,
3788 sgl_number5,
3789 sgl_sender_receiver_flag5,
3790 sgl_federal_flag5,
3791 sgl_debit_credit_flag5,
3792 sgl_amount5,
3793 sgl_number6,
3794 sgl_sender_receiver_flag6,
3795 sgl_federal_flag6,
3796 sgl_debit_credit_flag6,
3797 sgl_amount6,
3798 sgl_number7,
3799 sgl_sender_receiver_flag7,
3800 sgl_federal_flag7,
3801 sgl_debit_credit_flag7,
3802 sgl_amount7,
3803 sgl_number8,
3804 sgl_sender_receiver_flag8,
3805 sgl_federal_flag8,
3806 sgl_debit_credit_flag8,
3807 sgl_amount8,
3808 sgl_number9,
3809 sgl_sender_receiver_flag9,
3810 sgl_federal_flag9,
3811 sgl_debit_credit_flag9,
3812 sgl_amount9,
3813 sgl_number10,
3814 sgl_sender_receiver_flag10,
3815 sgl_federal_flag10,
3816 sgl_debit_credit_flag10,
3817 sgl_amount10,
3818 sgl_number11,
3819 sgl_sender_receiver_flag11,
3820 sgl_federal_flag11,
3821 sgl_debit_credit_flag11,
3822 sgl_amount11,
3823 sgl_number12,
3824 sgl_sender_receiver_flag12,
3825 sgl_federal_flag12,
3826 sgl_debit_credit_flag12,
3827 sgl_amount12,
3828 sgl_number13,
3829 sgl_sender_receiver_flag13,
3830 sgl_federal_flag13,
3831 sgl_debit_credit_flag13,
3832 sgl_amount13,
3833 sgl_number14,
3834 sgl_sender_receiver_flag14,
3835 sgl_federal_flag14,
3836 sgl_debit_credit_flag14,
3837 sgl_amount14,
3838 sgl_number15,
3839 sgl_sender_receiver_flag15,
3840 sgl_federal_flag15,
3841 sgl_debit_credit_flag15,
3842 sgl_amount15,
3843 sgl_number16,
3844 sgl_sender_receiver_flag16,
3845 sgl_federal_flag16,
3846 sgl_debit_credit_flag16,
3847 sgl_amount16,
3848 record_status,
3849 org_id,
3850 set_of_books_id,
3851 invoice_id,
3852 invoice_line_id,
3853 actual_po_number,
3854 actual_po_line_number,
3855 vendor_id,
3856 vendor_site_id,
3857 created_by,
3858 creation_date,
3859 last_updated_by,
3860 last_update_date,
3861 request_id,
3862 group_id,
3863 data_file,
3864 int_invoice_id,
3865 int_invoice_line_id
3866 )
3867 SELECT ipac_import_id,
3868 batch_name,
3869 transaction_id,
3870 submitter_alc,
3871 originating_alc,
3872 customer_alc,
3873 contact_name,
3874 contact_email_address,
3875 contact_phone_number,
3876 summary_amount,
3877 numer_of_detail_lines,
3878 accomplished_date,
3879 accounting_date,
3880 detail_line_number,
3881 contract_number,
3882 purchase_order_number,
3883 clin,
3884 invoice_number,
3885 requisition_number,
3886 quantity,
3887 unit_of_issue,
3888 unit_price,
3889 detail_amount,
3890 pay_flag,
3891 fy_obligation_id,
3892 receiver_tres_acct_symbol,
3893 receiver_betc,
3894 receiver_duns,
3895 receiver_duns_4,
3896 sender_tres_acct_symbol,
3897 sender_betc,
3898 sender_duns,
3899 sender_duns_4,
3900 receiver_department_code,
3901 accounting_class_code,
3902 acrn,
3903 job_project_number,
3904 jas_number,
3905 fsn_aaa_adsn,
3906 obligating_doc_number,
3907 act_trace_number,
3908 description,
3909 misc_information,
3910 transaction_type,
3911 ipac_doc_ref_number,
3912 sender_do_symbol,
3913 dodacc,
3914 transaction_contact,
3915 transcation_contact_phone,
3916 voucher_number,
3917 original_do_symbol,
3918 orig_accomplished_date,
3919 orig_accounting_date,
3920 orig_doc_ref_number,
3921 orig_transaction_type,
3922 sender_sgl_comment,
3923 receiver_sgl_comment,
3924 sgl_number1,
3925 sgl_sender_receiver_flag1,
3926 sgl_federal_flag1,
3927 sgl_debit_credit_flag1,
3928 sgl_amount1,
3929 sgl_number2,
3930 sgl_sender_receiver_flag2,
3931 sgl_federal_flag2,
3932 sgl_debit_credit_flag2,
3933 sgl_amount2,
3934 sgl_number3,
3935 sgl_sender_receiver_flag3,
3936 sgl_federal_flag3,
3937 sgl_debit_credit_flag3,
3938 sgl_amount3,
3939 sgl_number4,
3940 sgl_sender_receiver_flag4,
3941 sgl_federal_flag4,
3942 sgl_debit_credit_flag4,
3943 sgl_amount4,
3944 sgl_number5,
3945 sgl_sender_receiver_flag5,
3946 sgl_federal_flag5,
3947 sgl_debit_credit_flag5,
3948 sgl_amount5,
3949 sgl_number6,
3950 sgl_sender_receiver_flag6,
3951 sgl_federal_flag6,
3952 sgl_debit_credit_flag6,
3953 sgl_amount6,
3954 sgl_number7,
3955 sgl_sender_receiver_flag7,
3956 sgl_federal_flag7,
3957 sgl_debit_credit_flag7,
3958 sgl_amount7,
3959 sgl_number8,
3960 sgl_sender_receiver_flag8,
3961 sgl_federal_flag8,
3962 sgl_debit_credit_flag8,
3963 sgl_amount8,
3964 sgl_number9,
3965 sgl_sender_receiver_flag9,
3966 sgl_federal_flag9,
3967 sgl_debit_credit_flag9,
3968 sgl_amount9,
3969 sgl_number10,
3970 sgl_sender_receiver_flag10,
3971 sgl_federal_flag10,
3972 sgl_debit_credit_flag10,
3973 sgl_amount10,
3974 sgl_number11,
3975 sgl_sender_receiver_flag11,
3976 sgl_federal_flag11,
3977 sgl_debit_credit_flag11,
3978 sgl_amount11,
3979 sgl_number12,
3980 sgl_sender_receiver_flag12,
3981 sgl_federal_flag12,
3982 sgl_debit_credit_flag12,
3983 sgl_amount12,
3984 sgl_number13,
3985 sgl_sender_receiver_flag13,
3986 sgl_federal_flag13,
3987 sgl_debit_credit_flag13,
3988 sgl_amount13,
3989 sgl_number14,
3990 sgl_sender_receiver_flag14,
3991 sgl_federal_flag14,
3992 sgl_debit_credit_flag14,
3993 sgl_amount14,
3994 sgl_number15,
3995 sgl_sender_receiver_flag15,
3996 sgl_federal_flag15,
3997 sgl_debit_credit_flag15,
3998 sgl_amount15,
3999 sgl_number16,
4000 sgl_sender_receiver_flag16,
4001 sgl_federal_flag16,
4002 sgl_debit_credit_flag16,
4003 sgl_amount16,
4004 record_status,
4005 org_id,
4006 set_of_books_id,
4007 invoice_id,
4008 invoice_line_id,
4009 actual_po_number,
4010 actual_po_line_number,
4011 vendor_id,
4012 vendor_site_id,
4013 created_by,
4014 creation_date,
4015 last_updated_by,
4016 last_update_date,
4017 request_id,
4018 group_id,
4019 data_file,
4020 int_invoice_id,
4021 int_invoice_line_id
4022 FROM fv_ipac_import
4023 WHERE group_id = p_group_id
4024 AND record_status = g_status_processed;
4025 l_rowcount := SQL%ROWCOUNT;
4026 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4027 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Inserted '||l_rowcount||' rows into fv_ipac_import_history.');
4028 END IF;
4029 EXCEPTION
4030 WHEN OTHERS THEN
4031 p_error_code := g_FAILURE;
4032 p_error_desc := SQLERRM;
4033 l_location := l_module_name||'.insert_fv_ipac_import_history';
4034 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
4035 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
4036 END;
4037
4038 BEGIN
4039 DELETE FROM fv_ipac_import
4040 WHERE group_id = p_group_id
4041 AND record_status = g_status_processed;
4042 l_rowcount := SQL%ROWCOUNT;
4043 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4044 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from fv_ipac_import.');
4045 END IF;
4046 EXCEPTION
4047 WHEN OTHERS THEN
4048 p_error_code := g_FAILURE;
4049 p_error_desc := SQLERRM;
4050 l_location := l_module_name||'.delete_fv_ipac_import';
4051 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
4052 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
4053 END;
4054
4055 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4056 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
4057 END IF;
4058 EXCEPTION
4059 WHEN OTHERS THEN
4060 p_error_code := g_FAILURE;
4061 p_error_desc := SQLERRM;
4062 l_location := l_module_name||'.final_exception';
4063 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
4064 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
4065 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4066 fv_utility.debug_mesg(fnd_log.level_procedure, l_location,g_exit||'('||p_error_code||')');
4067 END IF;
4068 END;
4069
4070 --****************************************************************************************--
4071 --* Name : load_data_file *--
4072 --* Type : Procedure *--
4073 --* Purpose : Uploads the file into the table fv_ipac_import table *--
4074 --* Parameters : p_data_file_name IN The data file that is to be imported *--
4075 --* : p_errbuf OUT Error returned to the concurrent process *--
4076 --* : p_retcode OUT Return Code to concurrent process *--
4077 --* Global Vars : g_org_id READ *--
4078 --* : g_module_name READ *--
4079 --* : g_SUCCESS READ *--
4080 --* : g_ERROR READ *--
4081 --* : g_WARNING READ *--
4082 --* : fnd_log.level_statement READ *--
4083 --* : fnd_log.g_current_runtime_level READ *--
4084 --* : fnd_log.level_unexpected READ *--
4085 --* : fnd_log.level_procedure READ *--
4086 --* : g_enter READ *--
4087 --* : g_exit READ *--
4088 --* Called from : main *--
4089 --* Calls : cleanup_previous_failed_run *--
4090 --* : fnd_request.set_org_id *--
4091 --* : fnd_request.submit_request *--
4092 --* : fnd_concurrent.wait_for_request *--
4093 --* : fnd_profile.value *--
4094 --* : fv_utility.debug_mesg *--
4095 --* : fv_utility.log_mesg *--
4096 --* Tables Used : None *--
4097 --* Logic : 1. Get the value of FV_DOWNLOAD_TREASURY_FILES_DIRECTORY profile *--
4098 --* : This has the directory name where to look for the file *--
4099 --* : 2. Append the file name by determining if the OS is win or UNIX *--
4100 --* : 3. Remove all the records with status of IMPORTED that failed *--
4101 --* : previous run somehow. *--
4102 --* : 4. Submit a request for FVSLRPRO using the parameters passed and *--
4103 --* : wait for the request to finish *--
4104 --****************************************************************************************--
4105 PROCEDURE load_data_file
4106 (
4107 p_data_file_name IN VARCHAR2,
4108 p_error_code OUT NOCOPY NUMBER,
4109 p_error_desc OUT NOCOPY VARCHAR2
4110 )
4111 IS
4112 l_module_name VARCHAR2(200);
4113 l_location VARCHAR2(200);
4114 l_directory_path VARCHAR2(1024);
4115 l_data_file VARCHAR2(1024);
4116 l_request_id NUMBER;
4117 l_request_wait_status BOOLEAN;
4118 l_phase VARCHAR2(100);
4119 l_status VARCHAR2(100);
4120 l_dev_phase VARCHAR2(100);
4121 l_dev_status VARCHAR2(100);
4122 l_message VARCHAR2(100);
4123 BEGIN
4124 l_module_name := g_module_name || 'load_data_file';
4125 p_error_code := g_SUCCESS;
4126 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4127 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
4128 END IF;
4129 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4130 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_data_file_name = '||p_data_file_name);
4131 END IF;
4132
4133 l_directory_path := fnd_profile.value('FV_DOWNLOAD_TREASURY_FILES_DIRECTORY');
4134
4135 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4136 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_directory_path = '||l_directory_path);
4137 END IF;
4138
4139 IF (l_directory_path IS NULL) THEN
4140 p_error_code := g_FAILURE;
4141 p_error_desc := 'The directory path is not set in the "FV:Download Treasury Files Directory" profile';
4142 fv_utility.log_mesg(fnd_log.level_error, l_module_name,p_error_desc) ;
4143 END IF;
4144
4145 IF (p_error_code = g_SUCCESS) THEN
4146 --
4147 -- Reformat the input file name ( '\\' --> for WINDOWS NT , '/' --> UNIX )
4148 --
4149
4150 IF (INSTR(l_directory_path, '\') <> 0 ) THEN
4151 l_data_file := l_directory_path || '\\' || p_data_file_name;
4152 ELSE
4153 l_data_file := l_directory_path || '/' || p_data_file_name;
4154 END IF;
4155
4156 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4157 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_data_file = '||l_data_file);
4158 END IF;
4159
4160 END IF;
4161
4162 IF (p_error_code = g_SUCCESS) THEN
4163 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4164 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling cleanup_previous_failed_run');
4165 END IF;
4166 cleanup_previous_failed_run
4167 (
4168 p_error_code => p_error_code,
4169 p_error_desc => p_error_desc
4170 );
4171 END IF;
4172
4173 IF (p_error_code = g_SUCCESS) THEN
4174 --
4175 -- Submit request to execute SQL*Loader.
4176 --
4177 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4178 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Submitting request');
4179 END IF;
4180
4181
4182 fnd_request.set_org_id(g_org_id);
4183 l_request_id := fnd_request.submit_request
4184 (
4185 application => 'FV',
4186 program => 'FVSLRPRO',
4187 description => '',
4188 start_time => '',
4189 sub_request => FALSE ,
4190 argument1 => l_data_file
4191 ) ;
4192 IF (l_request_id = 0) THEN
4193 p_error_code := g_FAILURE;
4194 p_error_desc := 'Failed to submit request for SQL*LOADER';
4195 fv_utility.log_mesg(fnd_log.level_error, l_module_name,p_error_desc) ;
4196 ELSE
4197 COMMIT;
4198 END IF;
4199 END IF;
4200
4201 IF (p_error_code = g_SUCCESS) THEN
4202 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4203 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Waiting for request to finish');
4204 END IF;
4205 l_request_wait_status := fnd_concurrent.wait_for_request
4206 (
4207 request_id => l_request_id,
4208 interval => 20,
4209 max_wait => 0,
4210 phase => l_phase,
4211 status => l_status,
4212 dev_phase => l_dev_phase,
4213 dev_status => l_dev_status,
4214 message => l_message
4215 );
4216
4217 COMMIT;
4218 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4219 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'wait_for_request retured');
4220 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_phase = '||l_phase);
4221 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_status = '||l_status);
4222 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_dev_phase = '||l_dev_phase);
4223 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_dev_status = '||l_dev_status);
4224 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_message = '||l_message);
4225 END IF;
4226
4227 IF (l_request_wait_status = FALSE) THEN
4228 p_error_code := g_FAILURE;
4229 p_error_desc := 'Failed to load the IPAC Disbursement records-1.';
4230 fv_utility.log_mesg(fnd_log.level_error, l_module_name,p_error_desc) ;
4231 END IF;
4232 END IF;
4233
4234 IF (p_error_code = g_SUCCESS) THEN
4235 IF ((l_dev_phase = 'COMPLETE') AND (l_dev_status IN ('NORMAL','WARNING'))) THEN
4236 NULL;
4237 ELSE
4238 p_error_code := g_FAILURE;
4239 p_error_desc := 'Failed to load the IPAC Disbursement records-2.';
4240 fv_utility.log_mesg(fnd_log.level_error, l_module_name,p_error_desc) ;
4241 END IF;
4242 END IF;
4243 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4244 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
4245 END IF;
4246
4247 EXCEPTION
4248 WHEN OTHERS THEN
4249 p_error_code := g_FAILURE;
4250 p_error_desc := SQLERRM;
4251 l_location := l_module_name||'.final_exception';
4252 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
4253 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
4254 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4255 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
4256 END IF;
4257 END;
4258
4259 --****************************************************************************************--
4260 --* Name : main *--
4261 --* Type : Procedure *--
4262 --* Purpose : This is the main procedure called from the concurrent program *--
4263 --* : Upload IPAC Disbursement (FVIPDISB) *--
4264 --* Parameters : p_errbuf Error returned to the concurrent process *--
4265 --* : p_retcode Return Code to concurrent process *--
4266 --* : p_data_file_name The input data file name to be imported *--
4267 --* : p_agency_location_code The Agency Location Code for which invoices *--
4268 --* : will be created. *--
4269 --* : p_payment_bank_acct_id Payment bank account id. *--
4270 --* : p_document_id Document Id *--
4271 --* Global Vars : g_module_name READ *--
4272 --* : g_SUCCESS READ *--
4273 --* : g_WARNING READ *--
4274 --* : g_ERROR READ *--
4275 --* : fnd_log.level_procedure READ *--
4276 --* : fnd_log.g_current_runtime_level READ *--
4277 --* : g_enter READ *--
4278 --* : g_exit READ *--
4279 --* : g_request_id READ *--
4280 --* : g_userid READ *--
4281 --* : g_login_id READ *--
4282 --* : g_org_id READ *--
4283 --* : g_set_of_books_id READ *--
4284 --* : g_ia_paygroup WRITE *--
4285 --* Called from : Concurrent Program Upload IPAC Disbursement (FVIPDISB) *--
4286 --* Calls : fv_utility.debug_mesg *--
4287 --* : fv_utility.log_mesg *--
4288 --* : fnd_profile.value *--
4289 --* : load_data_file *--
4290 --* : process_data *--
4291 --* : kick_off_ap_invoices_import *--
4292 --* : check_for_ap_import_errors *--
4293 --* : kick_off_exception_report *--
4294 --* : kick_off_ipac_auto_pmt_process *--
4295 --* : cleanup_current_failed_run *--
4296 --* : move_data_to_history *--
4297 --* Tables Used : fv_operating_units SELECT *--
4298 --* Logic : *--
4299 --****************************************************************************************--
4300 PROCEDURE main
4301 (
4302 p_errbuf OUT NOCOPY VARCHAR2,
4303 p_retcode OUT NOCOPY NUMBER,
4304 p_data_file_name IN VARCHAR2,
4305 p_agency_location_code IN VARCHAR2,
4306 p_payment_bank_acct_id IN NUMBER,
4307 p_payment_profile_id IN NUMBER,
4308 p_payment_document_id IN NUMBER
4309 --p_document_id IN NUMBER
4310 )
4311 IS
4312 l_module_name VARCHAR2(200);
4313 l_location VARCHAR2(200);
4314 l_batch_control VARCHAR2(1);
4315 l_batch_name fv_ipac_import.batch_name%TYPE;
4316 l_ok_to_import VARCHAR2(1);
4317 l_group_id NUMBER;
4318 BEGIN
4319 l_module_name := g_module_name || 'main';
4320 p_retcode := g_SUCCESS;
4321
4322 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4323 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
4324 END IF;
4325
4326 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4327 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_data_file_name = '||p_data_file_name);
4328 -- fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_transaction_code = '||p_transaction_code);
4329 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_agency_location_code = '||p_agency_location_code);
4330 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_payment_bank_acct_id = '||p_payment_bank_acct_id);
4331 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_payment_profile_id = '||p_payment_profile_id);
4332 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_payment_document_id = '||p_payment_document_id);
4333 -- fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_pay_trans_code = '||p_pay_trans_code);
4334 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'g_request_id = '||g_request_id);
4335 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'g_user_id = '||g_user_id);
4336 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'g_login_id = '||g_login_id);
4337 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'g_org_id_id = '||g_org_id);
4338 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'g_set_of_books_id = '||g_set_of_books_id);
4339 END IF;
4340
4341 /*
4342 *****************************************************************************
4343 * Check to see if the AP:Use Invoice Batch Controls is set *
4344 *****************************************************************************
4345 */
4346 l_batch_control := NVL(fnd_profile.value('AP_USE_INV_BATCH_CONTROLS'),'N');
4347
4348 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4349 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_batch_control = '||l_batch_control);
4350 END IF;
4351
4352 IF (l_batch_control = 'N') THEN
4353 p_retcode := g_FAILURE;
4354 p_errbuf := 'The profile option, "AP:Use Invoice Batch Controls" must be set to Yes for the Federal Administrator.';
4355 fv_utility.log_mesg(fnd_log.level_error, l_module_name,p_errbuf) ;
4356 END IF;
4357
4358
4359 /*
4360 *****************************************************************************
4361 * Check to see if Pay Group is defined in Federal Options Window *
4362 *****************************************************************************
4363 */
4364 IF (p_retcode = g_SUCCESS) THEN
4365 BEGIN
4366 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4367 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from fv_operating_units');
4368 END IF;
4369 SELECT payables_ia_paygroup
4370 INTO g_ia_paygroup
4371 FROM fv_operating_units
4372 WHERE set_of_books_id = g_set_of_books_id;
4373 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4374 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'g_ia_paygroup = '||g_ia_paygroup);
4375 END IF;
4376 EXCEPTION
4377 WHEN NO_DATA_FOUND THEN
4378 p_retcode := g_FAILURE;
4379 p_errbuf := 'No Paygroup defined on Define Federal Options Window ';
4380 fv_utility.log_mesg(fnd_log.level_error, l_module_name,p_errbuf);
4381 END;
4382 END IF;
4383
4384 /*
4385 *****************************************************************************
4386 * Load the data file into table fv_ipac_import *
4387 *****************************************************************************
4388 */
4389 IF (p_retcode = g_SUCCESS) THEN
4390 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4391 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling load_data_file');
4392 END IF;
4393 load_data_file
4394 (
4395 p_data_file_name => p_data_file_name,
4396 p_error_code => p_retcode,
4397 p_error_desc => p_errbuf
4398 );
4399 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4400 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'load_data_file returned');
4401 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code = '||p_retcode);
4402 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc = '||p_errbuf);
4403 END IF;
4404 END IF;
4405
4406 /*
4407 *****************************************************************************
4408 * Process the data *
4409 *****************************************************************************
4410 */
4411 IF (p_retcode = g_SUCCESS) THEN
4412 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4413 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling process_data');
4414 END IF;
4415 process_data
4416 (
4417 p_data_file_name => p_data_file_name,
4418 p_agency_location_code => p_agency_location_code,
4419 p_batch_name => l_batch_name,
4420 p_group_id => l_group_id,
4421 p_ok_to_import => l_ok_to_import,
4422 p_error_code => p_retcode,
4423 p_error_desc => p_errbuf
4424 );
4425 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4426 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'process_data returned');
4427 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_batch_name = '||l_batch_name);
4428 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_group_id = '||l_group_id);
4429 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_ok_to_import = '||l_ok_to_import);
4430 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code = '||p_retcode);
4431 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc = '||p_errbuf);
4432 END IF;
4433 END IF;
4434
4435 /*
4436 *****************************************************************************
4437 * Import AP Invoices *
4438 *****************************************************************************
4439 */
4440 IF (p_retcode = g_SUCCESS) THEN
4441 IF (l_ok_to_import = 'Y') THEN
4442 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4443 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling kick_off_ap_invoices_import');
4444 END IF;
4445 kick_off_ap_invoices_import
4446 (
4447 p_batch_name => l_batch_name,
4448 p_group_id => l_group_id,
4449 p_error_code => p_retcode,
4450 p_error_desc => p_errbuf
4451 );
4452 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4453 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'kick_off_ap_invoices_import returned');
4454 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code = '||p_retcode);
4455 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc = '||p_errbuf);
4456 END IF;
4457 END IF;
4458 END IF;
4459
4460
4461 IF (p_retcode = g_SUCCESS) THEN
4462 IF (l_ok_to_import = 'Y') THEN
4463 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4464 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling check_for_ap_import_errors');
4465 END IF;
4466 check_for_ap_import_errors
4467 (
4468 p_group_id => l_group_id,
4469 p_error_code => p_retcode,
4470 p_error_desc => p_errbuf
4471 );
4472 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4473 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'check_for_ap_import_errors returned');
4474 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code = '||p_retcode);
4475 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc = '||p_errbuf);
4476 END IF;
4477 END IF;
4478 END IF;
4479 /*
4480 *****************************************************************************
4481 * Kick off the exception report *
4482 *****************************************************************************
4483 */
4484 IF (p_retcode = g_SUCCESS OR p_retcode = g_WARNING) THEN
4485 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4486 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling kick_off_exception_report');
4487 END IF;
4488 kick_off_exception_report
4489 (
4490 p_data_file_name => p_data_file_name,
4491 p_agency_location_code => p_agency_location_code,
4492 p_group_id => l_group_id,
4493 p_error_code => p_retcode,
4494 p_error_desc => p_errbuf
4495 );
4496 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4497 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'kick_off_exception_report returned');
4498 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code = '||p_retcode);
4499 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc = '||p_errbuf);
4500 END IF;
4501 END IF;
4502
4503 /*
4504 *****************************************************************************
4505 * Kick off the ipac auto payment process *
4506 *****************************************************************************
4507 */
4508 IF (p_retcode = g_SUCCESS OR p_retcode = g_WARNING) THEN
4509 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4510 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling kick_off_ipac_auto_pmt_process');
4511 END IF;
4512 kick_off_ipac_auto_pmt_process
4513 (
4514 p_batch_name => l_batch_name,
4515 p_payment_bank_acct_id => p_payment_bank_acct_id,
4516 p_payment_profile_id => p_payment_profile_id,
4517 p_payment_document_id => p_payment_document_id,
4518 -- p_document_id => p_document_id,
4519 p_error_code => p_retcode,
4520 p_error_desc => p_errbuf
4521 );
4522 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4523 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'kick_off_ipac_auto_pmt_process returned');
4524 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code = '||p_retcode);
4525 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc = '||p_errbuf);
4526 END IF;
4527 END IF;
4528
4529 IF (p_retcode = g_SUCCESS OR p_retcode = g_WARNING) THEN
4530 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4531 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling cleanup_current_failed_run process');
4532 END IF;
4533 cleanup_current_failed_run
4534 (
4535 p_group_id => l_group_id,
4536 p_error_code => p_retcode,
4537 p_error_desc => p_errbuf
4538 );
4539 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4540 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'cleanup_current_failed_run returned');
4541 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code = '||p_retcode);
4542 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc = '||p_errbuf);
4543 END IF;
4544 END IF;
4545
4546 /*
4547 *****************************************************************************
4548 * Move data to history table *
4549 *****************************************************************************
4550 */
4551 IF (p_retcode = g_SUCCESS OR p_retcode = g_WARNING) THEN
4552 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4553 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling move_data_to_history process');
4554 END IF;
4555 move_data_to_history
4556 (
4557 p_group_id => l_group_id,
4558 p_error_code => p_retcode,
4559 p_error_desc => p_errbuf
4560 );
4561 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4562 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'move_data_to_history returned');
4563 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code = '||p_retcode);
4564 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc = '||p_errbuf);
4565 END IF;
4566 END IF;
4567
4568 IF (p_retcode = g_SUCCESS OR p_retcode = g_WARNING) THEN
4569 COMMIT;
4570 ELSE
4571 ROLLBACK;
4572 END IF;
4573
4574 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4575 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
4576 END IF;
4577
4578 EXCEPTION
4579 WHEN OTHERS THEN
4580 p_retcode := g_FAILURE;
4581 p_errbuf := SQLERRM;
4582 l_location := l_module_name||'.final_exception';
4583 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
4584 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
4585 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4586 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
4587 END IF;
4588 ROLLBACK;
4589 END;
4590
4591 BEGIN
4592 initialize_global_variables;
4593 END fv_ipac_disbursement_pkg;