[Home] [Help]
PACKAGE BODY: APPS.FV_IPAC_DISBURSEMENT_PKG
Source
1 PACKAGE BODY fv_ipac_disbursement_pkg AS
2 /* $Header: FVIPDISB.pls 120.19.12000000.3 2007/08/16 21:15:33 sasukuma 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 (
281 p_error_code OUT NOCOPY NUMBER,
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,
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,
388 terms_name,
385 exchange_rate_type,
386 exchange_date,
387 terms_id,
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,
537 p_invoice_hdr_rec.payment_cross_rate_date,
534 p_invoice_hdr_rec.group_id,
535 p_invoice_hdr_rec.request_id,
536 p_invoice_hdr_rec.payment_cross_rate_type,
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,
633 amount,
630 line_number,
631 line_type_lookup_code,
632 line_group_number,
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,
787 p_invoice_lines_rec.attribute4,
784 p_invoice_lines_rec.attribute1,
785 p_invoice_lines_rec.attribute2,
786 p_invoice_lines_rec.attribute3,
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 *--
889 --* : fnd_log.level_unexpected READ *--
886 --* : g_ERROR READ *--
887 --* : fnd_log.level_statement READ *--
888 --* : fnd_log.g_current_runtime_level 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;
974 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
971
972 IF (p_error_code = g_SUCCESS) THEN
973 BEGIN
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
1076 END IF;
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||')');
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,
1167 argument5 => g_org_id,
1164 argument2 => p_payment_bank_acct_id,
1165 argument3 => p_payment_profile_id,
1166 argument4 => p_payment_document_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 *--
1261 PROCEDURE kick_off_ap_invoices_import
1258 --* Logic : Submit a request for APXIIMPT using the parameters passed and wait *--
1259 --* : for the request to finish *--
1260 --****************************************************************************************--
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
1384 --* Purpose : Procedure to kick of the concurrent process *--
1381 --****************************************************************************************--
1382 --* Name : kick_off_exception_report *--
1383 --* Type : Procedure *--
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
1479 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling wait_for_request');
1476
1477 IF (p_error_code = g_SUCCESS) THEN
1478 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
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 (
1565 p_group_id OUT NOCOPY NUMBER,
1562 p_data_file_name IN VARCHAR2,
1563 p_agency_location_code IN VARCHAR2,
1564 p_batch_name OUT NOCOPY VARCHAR2,
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
1681 (
1678 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_error');
1679 END IF;
1680 insert_error
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);
1886 END IF;
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'));
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 *--
1995 --* Global Vars : g_module_name READ *--
1992 --* : p_record_status OUT Record Status *--
1993 --* : p_errbuf Error returned to the concurrent process *--
1994 --* : p_retcode Return Code to concurrent process *--
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
2087 l_unit_of_measure := NULL;
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;
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,
2176 IS
2173 p_error_code OUT NOCOPY NUMBER,
2174 p_error_desc OUT NOCOPY VARCHAR2
2175 )
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
2286 IF (fnd_log.level_procedure >= 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
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
2372 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2369 p_ap_inv_lines_rec.po_number := p_ipac_import_rec.purchase_order_number;
2370 END IF;
2371
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
2475 WHEN OTHERS 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
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');
2576
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;
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,
2680 p_validation_err => l_validation_err,
2677 p_invoice_id => l_recurr_invoice_id,
2678 p_invoice_found => l_invoice_found,
2679 p_validation_code => l_validation_code,
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 *--
2768 --* : fv_utility.log_mesg *--
2765 --* Called from : process_data *--
2766 --* Calls : insert_invoice_hdr *--
2767 --* : fv_utility.debug_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;
2861 EXCEPTION
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;
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
2946 l_module_name := g_module_name || 'process_data';
2943 AND fii.record_status = g_status_preprocessed
2944 ORDER BY fii.invoice_number;
2945 BEGIN
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 *--
3035 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3032 --* written to the database. *--
3033 --****************************************************************************************--
3034
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 l_inv_lines_interface_rec.description := l_ipac_import_record.description;
3115 -- TC Obsoletion
3116 -- l_inv_lines_interface_rec.ussgl_transaction_code := p_transaction_code;
3120 ' Quantity: '||
3117 IF (l_inv_lines_interface_rec.description IS NULL) THEN
3118 l_inv_lines_interface_rec.description := 'IPAC Disbursement Ref. Number: '||
3119 l_ipac_import_record.ipac_doc_ref_number||
3121 l_inv_lines_interface_rec.quantity_invoiced||
3122 ' Unit Price: '||
3123 l_ipac_import_record.unit_price||
3124 ' Contract Num: '||
3125 l_ipac_import_record.contract_number;
3126 END IF;
3127 END IF;
3128
3129 --****************************************************************************************--
3130 --* Validate the purchase order and populate the relevant information such as *--
3131 --* po_header_id, po_line_id, po_number, po_line_number, vendor_id and vendor_site_id *--
3132 --* back to l_ipac_import_record and l_inv_lines_interface_rec *--
3133 --****************************************************************************************--
3134 IF (p_error_code = g_SUCCESS) THEN
3135 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3136 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling validate_po');
3137 END IF;
3138 validate_po
3139 (
3140 p_ipac_import_rec => l_ipac_import_record,
3141 p_ap_inv_lines_rec => l_inv_lines_interface_rec,
3142 p_error_code => p_error_code,
3143 p_error_desc => p_error_desc
3144 );
3145 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3146 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling validate_po returned');
3147 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code ='||p_error_code);
3148 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc ='||p_error_desc);
3149 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'record_status ='||l_ipac_import_record.record_status);
3150 END IF;
3151 END IF;
3152
3153 --****************************************************************************************--
3154 --* validate_po will set the status of l_ipac_import_record to ERROR if there was any *--
3155 --* validation error. If the invoice is already part of reecurring invoice then the *--
3156 --* status will be set to PROCESSED. In both these cases, no new invoice should be *--
3157 --* created and hence the flag l_ok_to_insert_inv to be set to 'N'. *--
3158 --****************************************************************************************--
3159 IF (p_error_code = g_SUCCESS) THEN
3160 IF (l_ipac_import_record.record_status IN (g_status_error, g_status_processed)) THEN
3161 l_ok_to_insert_inv := 'N';
3162 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3163 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Going to FINISHED_PROCESS');
3164 END IF;
3165 GOTO FINISHED_PROCESS;
3166 END IF;
3167
3168 --****************************************************************************************--
3169 --* vendor_id and vendor_site_id was populated in l_ipac_import_record by validate_po *--
3170 --* Since l_inv_hdrs_interface_rec was not passed to validate_po and since this info *--
3171 --* is required by the header, populate them from l_ipac_import_record *--
3172 --****************************************************************************************--
3173 l_inv_hdrs_interface_rec.vendor_id := l_ipac_import_record.vendor_id;
3174 l_inv_hdrs_interface_rec.vendor_site_id := l_ipac_import_record.vendor_site_id;
3175 END IF;
3176
3177 IF (l_ipac_import_record.receiver_duns IS NOT NULL) THEN
3178 --****************************************************************************************--
3179 --* Validate the receiver DUNS and DUNS+4 information. *--
3180 --****************************************************************************************--
3181 IF (p_error_code = g_SUCCESS) THEN
3182 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3183 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling validate_duns');
3184 END IF;
3185 validate_duns
3186 (
3187 p_ipac_import_rec => l_ipac_import_record,
3188 p_error_code => p_error_code,
3189 p_error_desc => p_error_desc
3190 );
3191 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3192 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling validate_duns returned');
3193 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code ='||p_error_code);
3194 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc ='||p_error_desc);
3195 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'record_status ='||l_ipac_import_record.record_status);
3196 END IF;
3197 END IF;
3198
3202 --****************************************************************************************--
3199 --****************************************************************************************--
3200 --* validate_duns will set the status of l_ipac_import_record to ERROR if there was any *--
3201 --* validation error. *--
3203 IF (p_error_code = g_SUCCESS) THEN
3204 IF (l_ipac_import_record.record_status = g_status_error) THEN
3205 l_ok_to_insert_inv := 'N';
3206 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3207 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Going to FINISHED_PROCESS');
3208 END IF;
3209 GOTO FINISHED_PROCESS;
3210 END IF;
3211 END IF;
3212 END IF;
3213
3214 --****************************************************************************************--
3215 --* The UOM passed in the file is of 2 characters which possibly maps to the UOM in po *--
3216 --* But the invoice lines interface requires unit_of_measure and not uom *--
3217 --* Also make sure that the PO UOM is same as File UOM *--
3218 --****************************************************************************************--
3219 IF (p_error_code = g_SUCCESS AND l_ok_to_insert_inv = 'Y') THEN
3220 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3221 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling resolve_uom');
3222 END IF;
3223 resolve_uom
3224 (
3225 p_ipac_import_id => l_ipac_import_record.ipac_import_id,
3226 p_uom_code => l_ipac_import_record.unit_of_issue,
3227 p_unit_of_measure => l_inv_lines_interface_rec.po_unit_of_measure,
3228 p_record_status => l_ipac_import_record.record_status,
3229 p_error_code => p_error_code,
3230 p_error_desc => p_error_desc
3231 );
3232 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3233 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling resolve_uom returned');
3234 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code ='||p_error_code);
3235 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc ='||p_error_desc);
3236 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_unit_of_measure ='||l_inv_lines_interface_rec.po_unit_of_measure);
3237 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'record_status ='||l_ipac_import_record.record_status);
3238 END IF;
3239 END IF;
3240
3241 IF (p_error_code = g_SUCCESS) THEN
3242 IF (l_ipac_import_record.record_status = g_status_error) THEN
3243 l_ok_to_insert_inv := 'N';
3244 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3245 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Going to FINISHED_PROCESS');
3246 END IF;
3247 GOTO FINISHED_PROCESS;
3248 END IF;
3249 END IF;
3250
3251 --****************************************************************************************--
3252 --* If all is well, insert the invoice line *--
3253 --* Remember we haven't inserted the header record unitl now. This happens when the *--
3254 --* invoice number changes. *--
3255 --****************************************************************************************--
3256 IF (p_error_code = g_SUCCESS AND l_ok_to_insert_inv = 'Y') THEN
3257 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3258 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_invoice_line');
3259 END IF;
3260 insert_invoice_line
3261 (
3262 p_invoice_lines_rec => l_inv_lines_interface_rec,
3263 p_error_code => p_error_code,
3264 p_error_desc => p_error_desc
3265 );
3266 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3267 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling insert_invoice_line returned');
3268 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code ='||p_error_code);
3269 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc ='||p_error_desc);
3270 END IF;
3271 l_current_inv_lines := l_current_inv_lines + 1;
3272 l_ipac_import_record.record_status := g_status_processed;
3273 ELSE
3274 l_ipac_import_record.record_status := g_status_other_error;
3275 END IF;
3276
3277 <<FINISHED_PROCESS>>
3278 IF (p_error_code = g_SUCCESS) THEN
3279 BEGIN
3280 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3281 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Updating fv_ipac_import');
3282 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'record_status='||l_ipac_import_record.record_status);
3283 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'invoice_id='||l_ipac_import_record.invoice_id);
3284 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'invoice_line_id='||l_ipac_import_record.invoice_line_id);
3288 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'vendor_site_id='||l_ipac_import_record.vendor_site_id);
3285 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'actual_po_number='||l_ipac_import_record.actual_po_number);
3286 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'actual_po_line_number='||l_ipac_import_record.actual_po_line_number);
3287 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'vendor_id='||l_ipac_import_record.vendor_id);
3289 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'ipac_import_id='||l_ipac_import_record.ipac_import_id);
3290 END IF;
3291 UPDATE fv_ipac_import fii
3292 SET record_status = l_ipac_import_record.record_status,
3293 invoice_id = l_ipac_import_record.invoice_id,
3294 int_invoice_id = l_inv_lines_interface_rec.invoice_id,
3295 int_invoice_line_id = l_inv_lines_interface_rec.invoice_line_id,
3296 actual_po_number = l_inv_lines_interface_rec.po_number,
3297 actual_po_line_number = l_inv_lines_interface_rec.po_line_number,
3298 vendor_id = l_inv_hdrs_interface_rec.vendor_id,
3299 vendor_site_id = l_inv_hdrs_interface_rec.vendor_site_id
3300 WHERE fii.ipac_import_id = l_ipac_import_record.ipac_import_id;
3301 l_rowcount := SQL%ROWCOUNT;
3302 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3303 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Updated '||l_rowcount||' rows in fv_ipac_import.');
3304 END IF;
3305 EXCEPTION
3306 WHEN OTHERS THEN
3307 p_error_code := g_FAILURE;
3308 p_error_desc := SQLERRM;
3309 l_location := l_module_name||'update_fv_ipac_import';
3310 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3311 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3312 END;
3313 END IF;
3314
3315 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3316 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'p_error_code1='||p_error_code);
3317 END IF;
3318 IF (p_error_code <> g_SUCCESS) THEN
3319 EXIT;
3320 END IF;
3321
3322 END LOOP;
3323
3324 --Insert the last invoice too.
3325 IF (p_error_code = g_SUCCESS) THEN
3326 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3327 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling save_or_erase_invoice1');
3328 END IF;
3329 save_or_erase_invoice
3330 (
3331 p_ap_inv_hdr_rec => l_inv_hdrs_interface_rec,
3332 p_previous_inv_number => l_save_invoice_number,
3333 p_okay_to_insert_inv => l_ok_to_insert_inv,
3334 p_total_invoice_lines => l_current_inv_lines,
3335 p_total_invoices => l_no_of_invoices_inserted,
3336 p_error_code => p_error_code,
3337 p_error_desc => p_error_desc
3338 );
3339 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3340 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling save_or_erase_invoice returned');
3341 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code ='||p_error_code);
3342 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc ='||p_error_desc);
3343 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_no_of_invoices_inserted ='||l_no_of_invoices_inserted);
3344 END IF;
3345 END IF;
3346 END IF;
3347
3348 IF (l_no_of_invoices_inserted > 0) THEN
3349 p_ok_to_import := 'Y';
3350 END IF;
3351
3352 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3353 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_batch_name='||p_batch_name);
3354 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_group_id='||p_group_id);
3355 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_ok_to_import='||p_ok_to_import);
3356 END IF;
3357
3358 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3359 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
3360 END IF;
3361
3362 EXCEPTION
3363 WHEN OTHERS THEN
3364 p_error_code := g_FAILURE;
3365 p_error_desc := SQLERRM;
3366 l_location := l_module_name||'.final_exception';
3367 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3368 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3369 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3370 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
3371 END IF;
3372 END;
3373
3374 --****************************************************************************************--
3375 --* Name : cleanup_previous_failed_run *--
3376 --* Type : Procedure *--
3377 --* Purpose : Cleanup previously failed run with records in status IMPORTED *--
3378 --* Parameters : p_errbuf Error returned to the concurrent process *--
3379 --* : p_retcode Return Code to concurrent process *--
3383 --* : fnd_log.level_statement READ *--
3380 --* Global Vars : g_module_name READ *--
3381 --* : g_SUCCESS READ *--
3382 --* : g_ERROR READ *--
3384 --* : fnd_log.g_current_runtime_level READ *--
3385 --* : fnd_log.level_unexpected READ *--
3386 --* : fnd_log.level_procedure READ *--
3387 --* : g_enter READ *--
3388 --* : g_exit READ *--
3389 --* Called from : load_data_file *--
3390 --* Calls : fv_utility.debug_mesg *--
3391 --* : fv_utility.log_mesg *--
3392 --* Tables Used : fv_ipac_import DELETE *--
3393 --* Logic : Delete all records from fv_ipac_import table with status IMPORTED *--
3394 --****************************************************************************************--
3395 PROCEDURE cleanup_previous_failed_run
3396 (
3397 p_error_code OUT NOCOPY NUMBER,
3398 p_error_desc OUT NOCOPY VARCHAR2
3399 )
3400 IS
3401 l_module_name VARCHAR2(200);
3402 l_location VARCHAR2(200);
3403 l_rowcount NUMBER;
3404 BEGIN
3405 l_module_name := g_module_name || 'cleanup_previous_failed_run';
3406 p_error_code := g_SUCCESS;
3407 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3408 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
3409 END IF;
3410
3411 BEGIN
3412 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3413 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Deleting fv_ipac_import');
3414 END IF;
3415 DELETE fv_ipac_import
3416 WHERE record_status = 'IMPORTED';
3417 l_rowcount := SQL%ROWCOUNT;
3418 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3419 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from fv_ipac_import.');
3420 END IF;
3421 EXCEPTION
3422 WHEN OTHERS THEN
3423 p_error_code := g_FAILURE;
3424 p_error_desc := SQLERRM;
3425 l_location := l_module_name||'.delete_fv_ipac_import';
3426 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3427 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3428 END;
3429 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3430 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
3431 END IF;
3432
3433 EXCEPTION
3434 WHEN OTHERS THEN
3435 p_error_code := g_FAILURE;
3436 p_error_desc := SQLERRM;
3437 l_location := l_module_name||'.final_exception';
3438 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3439 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3440 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3441 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
3442 END IF;
3443 END;
3444
3445 --****************************************************************************************--
3446 --* Name : cleanup_current_failed_run *--
3447 --* Type : Procedure *--
3448 --* Purpose : This procedure removes data from ap_invoices_interface, *--
3449 --* : ap_invoice_lines_interface and ap_interface_rejections after a *--
3450 --* : failed run *--
3451 --* Parameters : p_group_id IN The group id for which the data is processed *--
3452 --* : p_errbuf OUT Error returned to the concurrent process *--
3453 --* : p_retcode OUT Return Code to concurrent process *--
3454 --* Global Vars : g_module_name READ *--
3455 --* : g_SUCCESS READ *--
3456 --* : g_ERROR READ *--
3457 --* : fnd_log.level_statement READ *--
3458 --* : fnd_log.g_current_runtime_level READ *--
3459 --* : fnd_log.level_unexpected READ *--
3460 --* : fnd_log.level_procedure READ *--
3461 --* : g_enter READ *--
3462 --* : g_exit READ *--
3463 --* Called from : main *--
3464 --* Calls : fv_utility.debug_mesg *--
3465 --* : fv_utility.log_mesg *--
3469 --* : ap_invoices_interface DELETE *--
3466 --* Tables Used : fv_ipac_import SELECT, UPDATE *--
3467 --* : ap_interface_rejections DELETE *--
3468 --* : ap_invoice_lines_interface DELETE *--
3470 --* Logic : For each invoice number with status of ERROR in fv_ipac_import table *--
3471 --* : do the following *--
3472 --* : 1. Delete all data from ap_interface_rejections for lines *--
3473 --* : 2. Delete all data from ap_interface_rejections for header *--
3474 --* : 3. Delete all data from ap_invoice_lines_interface *--
3475 --* : 4. Delete all data from ap_invoices_interface *--
3476 --* : 5. Update all other records of fv_ipac_import table with same *--
3477 --* : invoice and status of not ERROR to ERROR IN OTHER LINES *--
3478 --****************************************************************************************--
3479 PROCEDURE cleanup_current_failed_run
3480 (
3481 p_group_id IN NUMBER,
3482 p_error_code OUT NOCOPY NUMBER,
3483 p_error_desc OUT NOCOPY VARCHAR2
3484 )
3485 IS
3486 l_module_name VARCHAR2(200);
3487 l_location VARCHAR2(200);
3488 l_rowcount NUMBER;
3489 BEGIN
3490 l_module_name := g_module_name || 'cleanup_current_failed_run';
3491 p_error_code := g_SUCCESS;
3492 RETURN;
3493 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3494 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
3495 END IF;
3496 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3497 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_group_id = '||p_group_id);
3498 END IF;
3499
3500 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3501 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Getting into cursor import_rec');
3502 END IF;
3503
3504 FOR import_rec IN (SELECT invoice_number
3505 FROM fv_ipac_import
3506 WHERE group_id = p_group_id
3507 AND record_status = g_status_error) LOOP
3508
3509 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3510 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Currently Processing Invoice Number = '||import_rec.invoice_number);
3511 END IF;
3512
3513 IF (p_error_code = g_SUCCESS) THEN
3514 BEGIN
3515 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3516 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Deleting ap_interface_rejections1');
3517 END IF;
3518 DELETE ap_interface_rejections
3519 WHERE parent_table = 'AP_INVOICE_LINES_INTERFACE'
3520 AND parent_id IN (SELECT invoice_line_id
3521 FROM ap_invoice_lines_interface aili,
3522 ap_invoices_interface aii
3523 WHERE aii.invoice_num = import_rec.invoice_number
3524 AND aii.group_id = p_group_id
3525 AND aii.invoice_id = aili.invoice_id);
3526 l_rowcount := SQL%ROWCOUNT;
3527 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3528 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from ap_interface_rejections1.');
3529 END IF;
3530 EXCEPTION
3531 WHEN OTHERS THEN
3532 p_error_code := g_FAILURE;
3533 p_error_desc := SQLERRM;
3534 l_location := l_module_name||'.delete_ap_interface_rejections1';
3535 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3536 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3537 END;
3538 END IF;
3539
3540 IF (p_error_code = g_SUCCESS) THEN
3541 BEGIN
3542 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3543 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Deleting ap_interface_rejections2');
3544 END IF;
3545 DELETE ap_interface_rejections
3546 WHERE parent_table = 'AP_INVOICES_INTERFACE'
3547 AND parent_id IN (SELECT invoice_id
3548 FROM ap_invoices_interface aii
3549 WHERE aii.invoice_num = import_rec.invoice_number
3550 AND aii.group_id = p_group_id);
3551 l_rowcount := SQL%ROWCOUNT;
3552 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3553 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from ap_interface_rejections2.');
3554 END IF;
3555 EXCEPTION
3556 WHEN OTHERS THEN
3557 p_error_code := g_FAILURE;
3558 p_error_desc := SQLERRM;
3559 l_location := l_module_name||'.delete_ap_interface_rejections2';
3560 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3561 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3562 END;
3563 END IF;
3564
3565 IF (p_error_code = g_SUCCESS) THEN
3566 BEGIN
3570 DELETE ap_invoice_lines_interface
3567 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3568 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Deleting ap_invoice_lines_interface');
3569 END IF;
3571 WHERE invoice_id IN (SELECT invoice_id
3572 FROM ap_invoices_interface
3573 WHERE invoice_num = import_rec.invoice_number
3574 AND group_id = p_group_id);
3575 l_rowcount := SQL%ROWCOUNT;
3576 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3577 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from ap_invoice_lines_interface.');
3578 END IF;
3579 EXCEPTION
3580 WHEN OTHERS THEN
3581 p_error_code := g_FAILURE;
3582 p_error_desc := SQLERRM;
3583 l_location := l_module_name||'.delete_ap_invoice_lines_interface';
3584 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3585 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3586 END;
3587 END IF;
3588
3589
3590 IF (p_error_code = g_SUCCESS) THEN
3591 BEGIN
3592 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3593 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Deleting ap_invoices_interface');
3594 END IF;
3595 DELETE ap_invoices_interface
3596 WHERE invoice_num = import_rec.invoice_number
3597 AND group_id = p_group_id;
3598 l_rowcount := SQL%ROWCOUNT;
3599 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3600 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from ap_invoices_interface.');
3601 END IF;
3602 EXCEPTION
3603 WHEN OTHERS THEN
3604 p_error_code := g_FAILURE;
3605 p_error_desc := SQLERRM;
3606 l_location := l_module_name||'.delete_ap_invoices_interface';
3607 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3608 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3609 END;
3610 END IF;
3611
3612 IF (p_error_code = g_SUCCESS) THEN
3613 BEGIN
3614 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3615 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Updating fv_ipac_import');
3616 END IF;
3617 UPDATE fv_ipac_import
3618 SET record_status = g_status_other_error
3619 WHERE group_id = p_group_id
3620 AND invoice_number = import_rec.invoice_number
3621 AND record_status <> g_status_error;
3622 l_rowcount := SQL%ROWCOUNT;
3623 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3624 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Updated '||l_rowcount||' rows in fv_ipac_import.');
3625 END IF;
3626 EXCEPTION
3627 WHEN OTHERS THEN
3628 p_error_code := g_FAILURE;
3629 p_error_desc := SQLERRM;
3630 l_location := l_module_name||'.update_fv_ipac_import';
3631 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3632 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3633 END;
3634 END IF;
3635
3636 IF (p_error_code <> g_SUCCESS) THEN
3637 EXIT;
3638 END IF;
3639
3640 END LOOP;
3641 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3642 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Out of cursor import_rec');
3643 END IF;
3644
3645 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3646 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
3647 END IF;
3648
3649 EXCEPTION
3650 WHEN OTHERS THEN
3651 p_error_code := g_FAILURE;
3652 p_error_desc := SQLERRM;
3653 l_location := l_module_name||'.final_exception';
3654 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
3655 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
3656 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3657 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
3658 END IF;
3659 END;
3660 --****************************************************************************************--
3661 --* Name : move_data_to_history *--
3662 --* Type : Procedure *--
3663 --* Purpose : This procedure moves all the processsed records from fv_ipac_import *--
3664 --* : table into fv_ipac_import_history table *--
3665 --* Parameters : p_group_id IN The group id for which the data is processed *--
3666 --* : p_errbuf OUT Error returned to the concurrent process *--
3667 --* : p_retcode OUT Return Code to concurrent process *--
3668 --* Global Vars : g_module_name READ *--
3669 --* : g_SUCCESS READ *--
3673 --* : fnd_log.level_unexpected READ *--
3670 --* : g_ERROR READ *--
3671 --* : fnd_log.level_statement READ *--
3672 --* : fnd_log.g_current_runtime_level READ *--
3674 --* : fnd_log.level_procedure READ *--
3675 --* : g_enter READ *--
3676 --* : g_exit READ *--
3677 --* Called from : main *--
3678 --* Calls : fv_utility.debug_mesg *--
3679 --* : fv_utility.log_mesg *--
3680 --* Tables Used : fv_ipac_import_history INSERT *--
3681 --* : fv_ipac_import SELECT, DELETE *--
3682 --* Logic : 1. Insert into fv_ipac_import_history by selecting all processed *--
3683 --* : records from fv_ipac_import *--
3684 --* : 2. Delete all processed records from fv_ipac_import *--
3685 --****************************************************************************************--
3686 PROCEDURE move_data_to_history
3687 (
3688 p_group_id IN NUMBER,
3689 p_error_code OUT NOCOPY NUMBER,
3690 p_error_desc OUT NOCOPY VARCHAR2
3691 )
3692 IS
3693 l_module_name VARCHAR2(200);
3694 l_location VARCHAR2(200);
3695 l_rowcount NUMBER;
3696 BEGIN
3697 l_module_name := g_module_name || 'move_data_to_history';
3698 p_error_code := g_SUCCESS;
3699 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3700 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
3701 END IF;
3702 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3703 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_group_id = '||p_group_id);
3704 END IF;
3705
3706 BEGIN
3707 INSERT INTO fv_ipac_import_history
3708 (
3709 ipac_import_id,
3710 batch_name,
3711 transaction_id,
3712 submitter_alc,
3713 originating_alc,
3714 customer_alc,
3715 contact_name,
3716 contact_email_address,
3717 contact_phone_number,
3718 summary_amount,
3719 numer_of_detail_lines,
3720 accomplished_date,
3721 accounting_date,
3722 detail_line_number,
3723 contract_number,
3724 purchase_order_number,
3725 clin,
3726 invoice_number,
3727 requisition_number,
3728 quantity,
3729 unit_of_issue,
3730 unit_price,
3731 detail_amount,
3732 pay_flag,
3733 fy_obligation_id,
3734 receiver_tres_acct_symbol,
3735 receiver_betc,
3736 receiver_duns,
3737 receiver_duns_4,
3738 sender_tres_acct_symbol,
3739 sender_betc,
3740 sender_duns,
3741 sender_duns_4,
3742 receiver_department_code,
3743 accounting_class_code,
3744 acrn,
3745 job_project_number,
3746 jas_number,
3747 fsn_aaa_adsn,
3748 obligating_doc_number,
3749 act_trace_number,
3750 description,
3751 misc_information,
3752 transaction_type,
3753 ipac_doc_ref_number,
3754 sender_do_symbol,
3755 dodacc,
3756 transaction_contact,
3757 transcation_contact_phone,
3758 voucher_number,
3759 original_do_symbol,
3760 orig_accomplished_date,
3761 orig_accounting_date,
3762 orig_doc_ref_number,
3763 orig_transaction_type,
3764 sender_sgl_comment,
3765 receiver_sgl_comment,
3766 sgl_number1,
3767 sgl_sender_receiver_flag1,
3768 sgl_federal_flag1,
3769 sgl_debit_credit_flag1,
3770 sgl_amount1,
3771 sgl_number2,
3772 sgl_sender_receiver_flag2,
3773 sgl_federal_flag2,
3774 sgl_debit_credit_flag2,
3775 sgl_amount2,
3776 sgl_number3,
3777 sgl_sender_receiver_flag3,
3778 sgl_federal_flag3,
3779 sgl_debit_credit_flag3,
3780 sgl_amount3,
3781 sgl_number4,
3782 sgl_sender_receiver_flag4,
3783 sgl_federal_flag4,
3784 sgl_debit_credit_flag4,
3785 sgl_amount4,
3786 sgl_number5,
3787 sgl_sender_receiver_flag5,
3788 sgl_federal_flag5,
3789 sgl_debit_credit_flag5,
3790 sgl_amount5,
3791 sgl_number6,
3792 sgl_sender_receiver_flag6,
3793 sgl_federal_flag6,
3794 sgl_debit_credit_flag6,
3795 sgl_amount6,
3796 sgl_number7,
3797 sgl_sender_receiver_flag7,
3798 sgl_federal_flag7,
3799 sgl_debit_credit_flag7,
3800 sgl_amount7,
3801 sgl_number8,
3802 sgl_sender_receiver_flag8,
3803 sgl_federal_flag8,
3804 sgl_debit_credit_flag8,
3805 sgl_amount8,
3806 sgl_number9,
3810 sgl_amount9,
3807 sgl_sender_receiver_flag9,
3808 sgl_federal_flag9,
3809 sgl_debit_credit_flag9,
3811 sgl_number10,
3812 sgl_sender_receiver_flag10,
3813 sgl_federal_flag10,
3814 sgl_debit_credit_flag10,
3815 sgl_amount10,
3816 sgl_number11,
3817 sgl_sender_receiver_flag11,
3818 sgl_federal_flag11,
3819 sgl_debit_credit_flag11,
3820 sgl_amount11,
3821 sgl_number12,
3822 sgl_sender_receiver_flag12,
3823 sgl_federal_flag12,
3824 sgl_debit_credit_flag12,
3825 sgl_amount12,
3826 sgl_number13,
3827 sgl_sender_receiver_flag13,
3828 sgl_federal_flag13,
3829 sgl_debit_credit_flag13,
3830 sgl_amount13,
3831 sgl_number14,
3832 sgl_sender_receiver_flag14,
3833 sgl_federal_flag14,
3834 sgl_debit_credit_flag14,
3835 sgl_amount14,
3836 sgl_number15,
3837 sgl_sender_receiver_flag15,
3838 sgl_federal_flag15,
3839 sgl_debit_credit_flag15,
3840 sgl_amount15,
3841 sgl_number16,
3842 sgl_sender_receiver_flag16,
3843 sgl_federal_flag16,
3844 sgl_debit_credit_flag16,
3845 sgl_amount16,
3846 record_status,
3847 org_id,
3848 set_of_books_id,
3849 invoice_id,
3850 invoice_line_id,
3851 actual_po_number,
3852 actual_po_line_number,
3853 vendor_id,
3854 vendor_site_id,
3855 created_by,
3856 creation_date,
3857 last_updated_by,
3858 last_update_date,
3859 request_id,
3860 group_id,
3861 data_file,
3862 int_invoice_id,
3863 int_invoice_line_id
3864 )
3865 SELECT ipac_import_id,
3866 batch_name,
3867 transaction_id,
3868 submitter_alc,
3869 originating_alc,
3870 customer_alc,
3871 contact_name,
3872 contact_email_address,
3873 contact_phone_number,
3874 summary_amount,
3875 numer_of_detail_lines,
3876 accomplished_date,
3877 accounting_date,
3878 detail_line_number,
3879 contract_number,
3880 purchase_order_number,
3881 clin,
3882 invoice_number,
3883 requisition_number,
3884 quantity,
3885 unit_of_issue,
3886 unit_price,
3887 detail_amount,
3888 pay_flag,
3889 fy_obligation_id,
3890 receiver_tres_acct_symbol,
3891 receiver_betc,
3892 receiver_duns,
3893 receiver_duns_4,
3894 sender_tres_acct_symbol,
3895 sender_betc,
3896 sender_duns,
3897 sender_duns_4,
3898 receiver_department_code,
3899 accounting_class_code,
3900 acrn,
3901 job_project_number,
3902 jas_number,
3903 fsn_aaa_adsn,
3904 obligating_doc_number,
3905 act_trace_number,
3906 description,
3907 misc_information,
3908 transaction_type,
3909 ipac_doc_ref_number,
3910 sender_do_symbol,
3911 dodacc,
3912 transaction_contact,
3913 transcation_contact_phone,
3914 voucher_number,
3915 original_do_symbol,
3916 orig_accomplished_date,
3917 orig_accounting_date,
3918 orig_doc_ref_number,
3919 orig_transaction_type,
3920 sender_sgl_comment,
3921 receiver_sgl_comment,
3922 sgl_number1,
3923 sgl_sender_receiver_flag1,
3924 sgl_federal_flag1,
3925 sgl_debit_credit_flag1,
3926 sgl_amount1,
3927 sgl_number2,
3928 sgl_sender_receiver_flag2,
3929 sgl_federal_flag2,
3930 sgl_debit_credit_flag2,
3931 sgl_amount2,
3932 sgl_number3,
3933 sgl_sender_receiver_flag3,
3934 sgl_federal_flag3,
3935 sgl_debit_credit_flag3,
3936 sgl_amount3,
3937 sgl_number4,
3938 sgl_sender_receiver_flag4,
3939 sgl_federal_flag4,
3940 sgl_debit_credit_flag4,
3941 sgl_amount4,
3942 sgl_number5,
3943 sgl_sender_receiver_flag5,
3944 sgl_federal_flag5,
3945 sgl_debit_credit_flag5,
3946 sgl_amount5,
3947 sgl_number6,
3948 sgl_sender_receiver_flag6,
3949 sgl_federal_flag6,
3950 sgl_debit_credit_flag6,
3951 sgl_amount6,
3952 sgl_number7,
3953 sgl_sender_receiver_flag7,
3954 sgl_federal_flag7,
3955 sgl_debit_credit_flag7,
3956 sgl_amount7,
3957 sgl_number8,
3961 sgl_amount8,
3958 sgl_sender_receiver_flag8,
3959 sgl_federal_flag8,
3960 sgl_debit_credit_flag8,
3962 sgl_number9,
3963 sgl_sender_receiver_flag9,
3964 sgl_federal_flag9,
3965 sgl_debit_credit_flag9,
3966 sgl_amount9,
3967 sgl_number10,
3968 sgl_sender_receiver_flag10,
3969 sgl_federal_flag10,
3970 sgl_debit_credit_flag10,
3971 sgl_amount10,
3972 sgl_number11,
3973 sgl_sender_receiver_flag11,
3974 sgl_federal_flag11,
3975 sgl_debit_credit_flag11,
3976 sgl_amount11,
3977 sgl_number12,
3978 sgl_sender_receiver_flag12,
3979 sgl_federal_flag12,
3980 sgl_debit_credit_flag12,
3981 sgl_amount12,
3982 sgl_number13,
3983 sgl_sender_receiver_flag13,
3984 sgl_federal_flag13,
3985 sgl_debit_credit_flag13,
3986 sgl_amount13,
3987 sgl_number14,
3988 sgl_sender_receiver_flag14,
3989 sgl_federal_flag14,
3990 sgl_debit_credit_flag14,
3991 sgl_amount14,
3992 sgl_number15,
3993 sgl_sender_receiver_flag15,
3994 sgl_federal_flag15,
3995 sgl_debit_credit_flag15,
3996 sgl_amount15,
3997 sgl_number16,
3998 sgl_sender_receiver_flag16,
3999 sgl_federal_flag16,
4000 sgl_debit_credit_flag16,
4001 sgl_amount16,
4002 record_status,
4003 org_id,
4004 set_of_books_id,
4005 invoice_id,
4006 invoice_line_id,
4007 actual_po_number,
4008 actual_po_line_number,
4009 vendor_id,
4010 vendor_site_id,
4011 created_by,
4012 creation_date,
4013 last_updated_by,
4014 last_update_date,
4015 request_id,
4016 group_id,
4017 data_file,
4018 int_invoice_id,
4019 int_invoice_line_id
4020 FROM fv_ipac_import
4021 WHERE group_id = p_group_id
4022 AND record_status = g_status_processed;
4023 l_rowcount := SQL%ROWCOUNT;
4024 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4025 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Inserted '||l_rowcount||' rows into fv_ipac_import_history.');
4026 END IF;
4027 EXCEPTION
4028 WHEN OTHERS THEN
4029 p_error_code := g_FAILURE;
4030 p_error_desc := SQLERRM;
4031 l_location := l_module_name||'.insert_fv_ipac_import_history';
4032 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
4033 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
4034 END;
4035
4036 BEGIN
4037 DELETE FROM fv_ipac_import
4038 WHERE group_id = p_group_id
4039 AND record_status = g_status_processed;
4040 l_rowcount := SQL%ROWCOUNT;
4041 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4042 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,'Deleted '||l_rowcount||' rows from fv_ipac_import.');
4043 END IF;
4044 EXCEPTION
4045 WHEN OTHERS THEN
4046 p_error_code := g_FAILURE;
4047 p_error_desc := SQLERRM;
4048 l_location := l_module_name||'.delete_fv_ipac_import';
4049 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
4050 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
4051 END;
4052
4053 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4054 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
4055 END IF;
4056 EXCEPTION
4057 WHEN OTHERS THEN
4058 p_error_code := g_FAILURE;
4059 p_error_desc := SQLERRM;
4060 l_location := l_module_name||'.final_exception';
4061 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
4062 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
4063 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4064 fv_utility.debug_mesg(fnd_log.level_procedure, l_location,g_exit||'('||p_error_code||')');
4065 END IF;
4066 END;
4067
4068 --****************************************************************************************--
4069 --* Name : load_data_file *--
4070 --* Type : Procedure *--
4071 --* Purpose : Uploads the file into the table fv_ipac_import table *--
4072 --* Parameters : p_data_file_name IN The data file that is to be imported *--
4073 --* : p_errbuf OUT Error returned to the concurrent process *--
4074 --* : p_retcode OUT Return Code to concurrent process *--
4075 --* Global Vars : g_org_id READ *--
4076 --* : g_module_name READ *--
4080 --* : fnd_log.level_statement READ *--
4077 --* : g_SUCCESS READ *--
4078 --* : g_ERROR READ *--
4079 --* : g_WARNING READ *--
4081 --* : fnd_log.g_current_runtime_level READ *--
4082 --* : fnd_log.level_unexpected READ *--
4083 --* : fnd_log.level_procedure READ *--
4084 --* : g_enter READ *--
4085 --* : g_exit READ *--
4086 --* Called from : main *--
4087 --* Calls : cleanup_previous_failed_run *--
4088 --* : fnd_request.set_org_id *--
4089 --* : fnd_request.submit_request *--
4090 --* : fnd_concurrent.wait_for_request *--
4091 --* : fnd_profile.value *--
4092 --* : fv_utility.debug_mesg *--
4093 --* : fv_utility.log_mesg *--
4094 --* Tables Used : None *--
4095 --* Logic : 1. Get the value of FV_DOWNLOAD_TREASURY_FILES_DIRECTORY profile *--
4096 --* : This has the directory name where to look for the file *--
4097 --* : 2. Append the file name by determining if the OS is win or UNIX *--
4098 --* : 3. Remove all the records with status of IMPORTED that failed *--
4099 --* : previous run somehow. *--
4100 --* : 4. Submit a request for FVSLRPRO using the parameters passed and *--
4101 --* : wait for the request to finish *--
4102 --****************************************************************************************--
4103 PROCEDURE load_data_file
4104 (
4105 p_data_file_name IN VARCHAR2,
4106 p_error_code OUT NOCOPY NUMBER,
4107 p_error_desc OUT NOCOPY VARCHAR2
4108 )
4109 IS
4110 l_module_name VARCHAR2(200);
4111 l_location VARCHAR2(200);
4112 l_directory_path VARCHAR2(1024);
4113 l_data_file VARCHAR2(1024);
4114 l_request_id NUMBER;
4115 l_request_wait_status BOOLEAN;
4116 l_phase VARCHAR2(100);
4117 l_status VARCHAR2(100);
4118 l_dev_phase VARCHAR2(100);
4119 l_dev_status VARCHAR2(100);
4120 l_message VARCHAR2(100);
4121 BEGIN
4122 l_module_name := g_module_name || 'load_data_file';
4123 p_error_code := g_SUCCESS;
4124 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4125 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
4126 END IF;
4127 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4128 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_data_file_name = '||p_data_file_name);
4129 END IF;
4130
4131 l_directory_path := fnd_profile.value('FV_DOWNLOAD_TREASURY_FILES_DIRECTORY');
4132
4133 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4134 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_directory_path = '||l_directory_path);
4135 END IF;
4136
4137 IF (l_directory_path IS NULL) THEN
4138 p_error_code := g_FAILURE;
4139 p_error_desc := 'The directory path is not set in the "FV:Download Treasury Files Directory" profile';
4140 fv_utility.log_mesg(fnd_log.level_error, l_module_name,p_error_desc) ;
4141 END IF;
4142
4143 IF (p_error_code = g_SUCCESS) THEN
4144 --
4145 -- Reformat the input file name ( '\\' --> for WINDOWS NT , '/' --> UNIX )
4146 --
4147
4148 IF (INSTR(l_directory_path, '\') <> 0 ) THEN
4149 l_data_file := l_directory_path || '\\' || p_data_file_name;
4150 ELSE
4151 l_data_file := l_directory_path || '/' || p_data_file_name;
4152 END IF;
4153
4154 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4155 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_data_file = '||l_data_file);
4156 END IF;
4157
4158 END IF;
4159
4160 IF (p_error_code = g_SUCCESS) THEN
4161 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4162 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling cleanup_previous_failed_run');
4163 END IF;
4164 cleanup_previous_failed_run
4165 (
4166 p_error_code => p_error_code,
4167 p_error_desc => p_error_desc
4168 );
4169 END IF;
4170
4171 IF (p_error_code = g_SUCCESS) THEN
4172 --
4173 -- Submit request to execute SQL*Loader.
4174 --
4175 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4179
4176 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Submitting request');
4177 END IF;
4178
4180 fnd_request.set_org_id(g_org_id);
4181 l_request_id := fnd_request.submit_request
4182 (
4183 application => 'FV',
4184 program => 'FVSLRPRO',
4185 description => '',
4186 start_time => '',
4187 sub_request => FALSE ,
4188 argument1 => l_data_file
4189 ) ;
4190 IF (l_request_id = 0) THEN
4191 p_error_code := g_FAILURE;
4192 p_error_desc := 'Failed to submit request for SQL*LOADER';
4193 fv_utility.log_mesg(fnd_log.level_error, l_module_name,p_error_desc) ;
4194 ELSE
4195 COMMIT;
4196 END IF;
4197 END IF;
4198
4199 IF (p_error_code = g_SUCCESS) THEN
4200 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4201 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Waiting for request to finish');
4202 END IF;
4203 l_request_wait_status := fnd_concurrent.wait_for_request
4204 (
4205 request_id => l_request_id,
4206 interval => 20,
4207 max_wait => 0,
4208 phase => l_phase,
4209 status => l_status,
4210 dev_phase => l_dev_phase,
4211 dev_status => l_dev_status,
4212 message => l_message
4213 );
4214
4215 COMMIT;
4216 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4217 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'wait_for_request retured');
4218 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_phase = '||l_phase);
4219 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_status = '||l_status);
4220 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_dev_phase = '||l_dev_phase);
4221 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_dev_status = '||l_dev_status);
4222 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_message = '||l_message);
4223 END IF;
4224
4225 IF (l_request_wait_status = FALSE) THEN
4226 p_error_code := g_FAILURE;
4227 p_error_desc := 'Failed to load the IPAC Disbursement records-1.';
4228 fv_utility.log_mesg(fnd_log.level_error, l_module_name,p_error_desc) ;
4229 END IF;
4230 END IF;
4231
4232 IF (p_error_code = g_SUCCESS) THEN
4233 IF ((l_dev_phase = 'COMPLETE') AND (l_dev_status IN ('NORMAL','WARNING'))) THEN
4234 NULL;
4235 ELSE
4236 p_error_code := g_FAILURE;
4237 p_error_desc := 'Failed to load the IPAC Disbursement records-2.';
4238 fv_utility.log_mesg(fnd_log.level_error, l_module_name,p_error_desc) ;
4239 END IF;
4240 END IF;
4241 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4242 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
4243 END IF;
4244
4245 EXCEPTION
4246 WHEN OTHERS THEN
4247 p_error_code := g_FAILURE;
4248 p_error_desc := SQLERRM;
4249 l_location := l_module_name||'.final_exception';
4250 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
4251 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
4252 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4253 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_error_code||')');
4254 END IF;
4255 END;
4256
4257 --****************************************************************************************--
4258 --* Name : main *--
4259 --* Type : Procedure *--
4260 --* Purpose : This is the main procedure called from the concurrent program *--
4261 --* : Upload IPAC Disbursement (FVIPDISB) *--
4262 --* Parameters : p_errbuf Error returned to the concurrent process *--
4263 --* : p_retcode Return Code to concurrent process *--
4264 --* : p_data_file_name The input data file name to be imported *--
4265 --* : p_agency_location_code The Agency Location Code for which invoices *--
4266 --* : will be created. *--
4267 --* : p_payment_bank_acct_id Payment bank account id. *--
4268 --* : p_document_id Document Id *--
4269 --* Global Vars : g_module_name READ *--
4270 --* : g_SUCCESS READ *--
4271 --* : g_WARNING READ *--
4272 --* : g_ERROR READ *--
4273 --* : fnd_log.level_procedure READ *--
4274 --* : fnd_log.g_current_runtime_level READ *--
4275 --* : g_enter READ *--
4276 --* : g_exit READ *--
4280 --* : g_org_id READ *--
4277 --* : g_request_id READ *--
4278 --* : g_userid READ *--
4279 --* : g_login_id READ *--
4281 --* : g_set_of_books_id READ *--
4282 --* : g_ia_paygroup WRITE *--
4283 --* Called from : Concurrent Program Upload IPAC Disbursement (FVIPDISB) *--
4284 --* Calls : fv_utility.debug_mesg *--
4285 --* : fv_utility.log_mesg *--
4286 --* : fnd_profile.value *--
4287 --* : load_data_file *--
4288 --* : process_data *--
4289 --* : kick_off_ap_invoices_import *--
4290 --* : check_for_ap_import_errors *--
4291 --* : kick_off_exception_report *--
4292 --* : kick_off_ipac_auto_pmt_process *--
4293 --* : cleanup_current_failed_run *--
4294 --* : move_data_to_history *--
4295 --* Tables Used : fv_operating_units SELECT *--
4296 --* Logic : *--
4297 --****************************************************************************************--
4298 PROCEDURE main
4299 (
4300 p_errbuf OUT NOCOPY VARCHAR2,
4301 p_retcode OUT NOCOPY NUMBER,
4302 p_data_file_name IN VARCHAR2,
4303 p_agency_location_code IN VARCHAR2,
4304 p_payment_bank_acct_id IN NUMBER,
4305 p_payment_profile_id IN NUMBER,
4306 p_payment_document_id IN NUMBER
4307 --p_document_id IN NUMBER
4308 )
4309 IS
4310 l_module_name VARCHAR2(200);
4311 l_location VARCHAR2(200);
4312 l_batch_control VARCHAR2(1);
4313 l_batch_name fv_ipac_import.batch_name%TYPE;
4314 l_ok_to_import VARCHAR2(1);
4315 l_group_id NUMBER;
4316 BEGIN
4317 l_module_name := g_module_name || 'main';
4318 p_retcode := g_SUCCESS;
4319
4320 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4321 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
4322 END IF;
4323
4324 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4325 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_data_file_name = '||p_data_file_name);
4326 -- fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_transaction_code = '||p_transaction_code);
4327 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_agency_location_code = '||p_agency_location_code);
4328 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_payment_bank_acct_id = '||p_payment_bank_acct_id);
4329 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_payment_profile_id = '||p_payment_profile_id);
4330 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_payment_document_id = '||p_payment_document_id);
4331 -- fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_pay_trans_code = '||p_pay_trans_code);
4332 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'g_request_id = '||g_request_id);
4333 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'g_user_id = '||g_user_id);
4334 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'g_login_id = '||g_login_id);
4335 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'g_org_id_id = '||g_org_id);
4336 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'g_set_of_books_id = '||g_set_of_books_id);
4337 END IF;
4338
4339 /*
4340 *****************************************************************************
4341 * Check to see if the AP:Use Invoice Batch Controls is set *
4342 *****************************************************************************
4343 */
4344 l_batch_control := NVL(fnd_profile.value('AP_USE_INV_BATCH_CONTROLS'),'N');
4345
4346 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4347 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_batch_control = '||l_batch_control);
4348 END IF;
4349
4350 IF (l_batch_control = 'N') THEN
4351 p_retcode := g_FAILURE;
4352 p_errbuf := 'The profile option, "AP:Use Invoice Batch Controls" must be set to Yes for the Federal Administrator.';
4353 fv_utility.log_mesg(fnd_log.level_error, l_module_name,p_errbuf) ;
4354 END IF;
4355
4356
4357 /*
4358 *****************************************************************************
4359 * Check to see if Pay Group is defined in Federal Options Window *
4360 *****************************************************************************
4361 */
4362 IF (p_retcode = g_SUCCESS) THEN
4363 BEGIN
4364 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4368 INTO g_ia_paygroup
4365 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from fv_operating_units');
4366 END IF;
4367 SELECT payables_ia_paygroup
4369 FROM fv_operating_units
4370 WHERE set_of_books_id = g_set_of_books_id;
4371 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4372 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'g_ia_paygroup = '||g_ia_paygroup);
4373 END IF;
4374 EXCEPTION
4375 WHEN NO_DATA_FOUND THEN
4376 p_retcode := g_FAILURE;
4377 p_errbuf := 'No Paygroup defined on Define Federal Options Window ';
4378 fv_utility.log_mesg(fnd_log.level_error, l_module_name,p_errbuf);
4379 END;
4380 END IF;
4381
4382 /*
4383 *****************************************************************************
4384 * Load the data file into table fv_ipac_import *
4385 *****************************************************************************
4386 */
4387 IF (p_retcode = g_SUCCESS) THEN
4388 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4389 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling load_data_file');
4390 END IF;
4391 load_data_file
4392 (
4393 p_data_file_name => p_data_file_name,
4394 p_error_code => p_retcode,
4395 p_error_desc => p_errbuf
4396 );
4397 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4398 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'load_data_file returned');
4399 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code = '||p_retcode);
4400 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc = '||p_errbuf);
4401 END IF;
4402 END IF;
4403
4404 /*
4405 *****************************************************************************
4406 * Process the data *
4407 *****************************************************************************
4408 */
4409 IF (p_retcode = g_SUCCESS) THEN
4410 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4411 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling process_data');
4412 END IF;
4413 process_data
4414 (
4415 p_data_file_name => p_data_file_name,
4416 p_agency_location_code => p_agency_location_code,
4417 p_batch_name => l_batch_name,
4418 p_group_id => l_group_id,
4419 p_ok_to_import => l_ok_to_import,
4420 p_error_code => p_retcode,
4421 p_error_desc => p_errbuf
4422 );
4423 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4424 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'process_data returned');
4425 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_batch_name = '||l_batch_name);
4426 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_group_id = '||l_group_id);
4427 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_ok_to_import = '||l_ok_to_import);
4428 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code = '||p_retcode);
4429 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc = '||p_errbuf);
4430 END IF;
4431 END IF;
4432
4433 /*
4434 *****************************************************************************
4435 * Import AP Invoices *
4436 *****************************************************************************
4437 */
4438 IF (p_retcode = g_SUCCESS) THEN
4439 IF (l_ok_to_import = 'Y') THEN
4440 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4441 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling kick_off_ap_invoices_import');
4442 END IF;
4443 kick_off_ap_invoices_import
4444 (
4445 p_batch_name => l_batch_name,
4446 p_group_id => l_group_id,
4447 p_error_code => p_retcode,
4448 p_error_desc => p_errbuf
4449 );
4450 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4451 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'kick_off_ap_invoices_import returned');
4452 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code = '||p_retcode);
4453 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc = '||p_errbuf);
4454 END IF;
4455 END IF;
4456 END IF;
4457
4458
4459 IF (p_retcode = g_SUCCESS) THEN
4460 IF (l_ok_to_import = 'Y') THEN
4461 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4462 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling check_for_ap_import_errors');
4463 END IF;
4464 check_for_ap_import_errors
4465 (
4466 p_group_id => l_group_id,
4467 p_error_code => p_retcode,
4468 p_error_desc => p_errbuf
4469 );
4470 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4471 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'check_for_ap_import_errors returned');
4472 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code = '||p_retcode);
4473 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc = '||p_errbuf);
4474 END IF;
4475 END IF;
4476 END IF;
4477 /*
4478 *****************************************************************************
4479 * Kick off the exception report *
4480 *****************************************************************************
4481 */
4482 IF (p_retcode = g_SUCCESS OR p_retcode = g_WARNING) THEN
4483 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4484 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling kick_off_exception_report');
4485 END IF;
4486 kick_off_exception_report
4487 (
4488 p_data_file_name => p_data_file_name,
4489 p_agency_location_code => p_agency_location_code,
4490 p_group_id => l_group_id,
4491 p_error_code => p_retcode,
4492 p_error_desc => p_errbuf
4493 );
4494 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4495 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'kick_off_exception_report returned');
4496 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code = '||p_retcode);
4497 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc = '||p_errbuf);
4498 END IF;
4499 END IF;
4500
4501 /*
4502 *****************************************************************************
4503 * Kick off the ipac auto payment process *
4504 *****************************************************************************
4505 */
4506 IF (p_retcode = g_SUCCESS OR p_retcode = g_WARNING) THEN
4507 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4508 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling kick_off_ipac_auto_pmt_process');
4509 END IF;
4510 kick_off_ipac_auto_pmt_process
4511 (
4512 p_batch_name => l_batch_name,
4513 p_payment_bank_acct_id => p_payment_bank_acct_id,
4514 p_payment_profile_id => p_payment_profile_id,
4515 p_payment_document_id => p_payment_document_id,
4516 -- p_document_id => p_document_id,
4517 p_error_code => p_retcode,
4518 p_error_desc => p_errbuf
4519 );
4520 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4521 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'kick_off_ipac_auto_pmt_process returned');
4522 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code = '||p_retcode);
4523 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc = '||p_errbuf);
4524 END IF;
4525 END IF;
4526
4527 IF (p_retcode = g_SUCCESS OR p_retcode = g_WARNING) THEN
4528 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4529 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling cleanup_current_failed_run process');
4530 END IF;
4531 cleanup_current_failed_run
4532 (
4533 p_group_id => l_group_id,
4534 p_error_code => p_retcode,
4535 p_error_desc => p_errbuf
4536 );
4537 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4538 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'cleanup_current_failed_run returned');
4539 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code = '||p_retcode);
4540 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc = '||p_errbuf);
4541 END IF;
4542 END IF;
4543
4544 /*
4545 *****************************************************************************
4546 * Move data to history table *
4547 *****************************************************************************
4548 */
4549 IF (p_retcode = g_SUCCESS OR p_retcode = g_WARNING) THEN
4550 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4551 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling move_data_to_history process');
4552 END IF;
4553 move_data_to_history
4554 (
4555 p_group_id => l_group_id,
4556 p_error_code => p_retcode,
4557 p_error_desc => p_errbuf
4558 );
4559 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4560 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'move_data_to_history returned');
4561 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_code = '||p_retcode);
4562 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_error_desc = '||p_errbuf);
4563 END IF;
4564 END IF;
4565
4566 IF (p_retcode = g_SUCCESS OR p_retcode = g_WARNING) THEN
4567 COMMIT;
4568 ELSE
4569 ROLLBACK;
4570 END IF;
4571
4572 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4573 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
4574 END IF;
4575
4576 EXCEPTION
4577 WHEN OTHERS THEN
4578 p_retcode := g_FAILURE;
4579 p_errbuf := SQLERRM;
4580 l_location := l_module_name||'.final_exception';
4581 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
4582 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
4583 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4584 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
4585 END IF;
4586 ROLLBACK;
4587 END;
4588
4589 BEGIN
4590 initialize_global_variables;
4591 END fv_ipac_disbursement_pkg;