1 PACKAGE BODY AP_IMPORT_INVOICES_PKG AS
2 /* $Header: apiimptb.pls 120.64.12010000.11 2008/12/11 10:31:37 imandal ship $ */
3
4 --==============================================================
5 -- delete attachment association
6 -- called by import_purge
7 -- (this function is also called by APXIIPRG.rdf)
8 --
9 --==============================================================
10 FUNCTION delete_attachments(p_invoice_id IN NUMBER)
11 RETURN NUMBER IS
12 l_attachments_count NUMBER := 0;
13 debug_info VARCHAR2(500);
14 BEGIN
15 select count(1)
16 into l_attachments_count
17 from fnd_attached_documents
18 where entity_name = 'AP_INVOICES_INTERFACE'
19 and pk1_value = p_invoice_id;
20
21 -- only delete if there is an attachment
22 if ( l_attachments_count > 0 ) then
23 -- assuming deleting only the association with related documents
24 -- need to see if that's always the case
25 fnd_attached_documents2_pkg.delete_attachments(
26 X_entity_name => 'AP_INVOICES_INTERFACE',
27 X_pk1_value => p_invoice_id,
28 X_delete_document_flag => 'N' );
29 end if;
30 return l_attachments_count;
31
32 EXCEPTION
33
34 WHEN OTHERS then
35 IF (SQLCODE < 0) then
36 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
37 AP_IMPORT_UTILITIES_PKG.Print(
38 AP_IMPORT_INVOICES_PKG.g_debug_switch, SQLERRM);
39 END IF;
40 END IF;
41 RETURN 0;
42 END delete_attachments;
43
44
45 --Payment Request: Added p_invoice_interface_id and p_needs_invoice_approval
46 --for importing payment request type invoices
47 FUNCTION IMPORT_INVOICES(
48 p_batch_name IN VARCHAR2,
49 p_gl_date IN DATE,
50 p_hold_code IN VARCHAR2,
51 p_hold_reason IN VARCHAR2,
52 p_commit_cycles IN NUMBER,
53 p_source IN VARCHAR2,
54 p_group_id IN VARCHAR2,
55 p_conc_request_id IN NUMBER,
56 p_debug_switch IN VARCHAR2,
57 p_org_id IN NUMBER,
58 p_batch_error_flag OUT NOCOPY VARCHAR2,
59 p_invoices_fetched OUT NOCOPY NUMBER,
60 p_invoices_created OUT NOCOPY NUMBER,
61 p_total_invoice_amount OUT NOCOPY NUMBER,
62 p_print_batch OUT NOCOPY VARCHAR2,
63 p_calling_sequence IN VARCHAR2,
64 p_invoice_interface_id IN NUMBER DEFAULT NULL,
65 p_needs_invoice_approval IN VARCHAR2 DEFAULT 'N',
66 p_commit IN VARCHAR2 DEFAULT 'Y')
67 RETURN BOOLEAN IS
68
69 -- Define invoice cursor
70
71 /* For bug3988118.
72 * Need to add UPPER for the flag values as the user can populate any
73 * value in the import tables and we do not validate if it is
74 * directly populated with 'y' or 'Y' or 'n' or 'N'
75 * Added UPPER for exclusive_payment_flag and invoice_includes_prepay_flag
76 * */
77
78 /* For bug 3972507
79 * Changed trim to rtrim in order to rtrim only
80 * trailing spaces */
81
82 -- Bug 4145391. Modified the select for the cursor to improve performance.
83 -- Removed the p_group_id where clause and added it to the cursor
84 -- import_invoices_group
85
86 /* Bug 6349739- Modified cursors import_invoices and
87 * import_invoices_group for RETEK invoices to set
88 * calc_tax_during_import_flag to 'N' for RETEK invoices */
89
90 CURSOR import_invoices is
91 SELECT invoice_id,
92 invoice_num,
93 invoice_type_lookup_code,
94 invoice_date,
95 po_number,
96 vendor_id,
97 vendor_num,
98 vendor_name,
99 vendor_site_id,
100 vendor_site_code,
101 invoice_amount,
102 invoice_currency_code,
103 exchange_rate,
104 exchange_rate_type,
105 exchange_date,
106 terms_id,
107 terms_name,
108 terms_date,
109 trim(description) description,
110 awt_group_id,
111 awt_group_name,
112 pay_awt_group_id,--bug6639866
113 pay_awt_group_name,--bug6639866
114 amount_applicable_to_discount,
115 sysdate,
116 last_updated_by,
117 last_update_login,
118 sysdate,
119 created_by,
120 status,
121 rtrim(attribute_category) attribute_category,
122 rtrim(attribute1) attribute1,
123 rtrim(attribute2) attribute2,
124 rtrim(attribute3) attribute3,
125 rtrim(attribute4) attribute4,
126 rtrim(attribute5) attribute5,
127 rtrim(attribute6) attribute6,
128 rtrim(attribute7) attribute7,
129 rtrim(attribute8) attribute8,
130 rtrim(attribute9) attribute9,
131 rtrim(attribute10) attribute10,
132 rtrim(attribute11) attribute11,
133 rtrim(attribute12) attribute12,
134 rtrim(attribute13) attribute13,
135 rtrim(attribute14) attribute14,
136 rtrim(attribute15) attribute15,
137 rtrim(global_attribute_category) global_attribute_category,
138 rtrim(global_attribute1) global_attribute1,
139 rtrim(global_attribute2) global_attribute2,
140 rtrim(global_attribute3) global_attribute3,
141 rtrim(global_attribute4) global_attribute4,
142 rtrim(global_attribute5) global_attribute5,
143 rtrim(global_attribute6) global_attribute6,
144 rtrim(global_attribute7) global_attribute7,
145 rtrim(global_attribute8) global_attribute8,
146 rtrim(global_attribute9) global_attribute9,
147 rtrim(global_attribute10) global_attribute10,
148 rtrim(global_attribute11) global_attribute11,
149 rtrim(global_attribute12) global_attribute12,
150 rtrim(global_attribute13) global_attribute13,
151 rtrim(global_attribute14) global_attribute14,
152 rtrim(global_attribute15) global_attribute15,
153 rtrim(global_attribute16) global_attribute16,
154 rtrim(global_attribute17) global_attribute17,
155 rtrim(global_attribute18) global_attribute18,
156 rtrim(global_attribute19) global_attribute19,
157 rtrim(global_attribute20) global_attribute20,
158 payment_currency_code,
159 payment_cross_rate,
160 payment_cross_rate_type,
161 payment_cross_rate_date,
162 doc_category_code,
163 voucher_num,
164 payment_method_code,
165 pay_group_lookup_code,
166 goods_received_date,
167 invoice_received_date,
168 gl_date,
169 accts_pay_code_combination_id,
170 -- bug 6509776
171 RTRIM(accts_pay_code_concatenated,'-'),
172 -- ussgl_transaction_code, - Bug 4277744
173 UPPER(exclusive_payment_flag),
174 prepay_num,
175 prepay_line_num,
176 prepay_apply_amount,
177 prepay_gl_date,
178 UPPER(invoice_includes_prepay_flag),
179 no_xrate_base_amount,
180 requester_id,
181 org_id,
182 operating_unit,
183 source,
184 group_id,
185 request_id,
186 workflow_flag,
187 vendor_email_address,
188 NVL(calc_tax_during_import_flag, 'N'), -- bug 6349739,bug6328293
189 control_amount,
190 add_tax_to_inv_amt_flag,
191 tax_related_invoice_id,
192 taxation_country,
193 document_sub_type,
194 supplier_tax_invoice_number,
195 supplier_tax_invoice_date,
196 supplier_tax_exchange_rate,
197 tax_invoice_recording_date,
198 tax_invoice_internal_seq,
199 legal_entity_id,
200 null,
201 ap_import_utilities_pkg.get_tax_only_rcv_matched_flag(invoice_id),
202 ap_import_utilities_pkg.get_tax_only_flag(invoice_id),
203 apply_advances_flag,
204 application_id,
205 product_table,
206 reference_key1,
207 reference_key2,
208 reference_key3,
209 reference_key4,
210 reference_key5,
211 reference_1,
212 reference_2,
213 net_of_retainage_flag,
214 cust_registration_code,
215 cust_registration_number,
216 paid_on_behalf_employee_id,
217 party_id, -- Added for Payment Requests
218 party_site_id,
219 pay_proc_trxn_type_code,
220 payment_function,
221 payment_priority,
222 BANK_CHARGE_BEARER,
223 REMITTANCE_MESSAGE1,
224 REMITTANCE_MESSAGE2,
225 REMITTANCE_MESSAGE3,
226 UNIQUE_REMITTANCE_IDENTIFIER,
227 URI_CHECK_DIGIT,
228 SETTLEMENT_PRIORITY,
229 PAYMENT_REASON_CODE,
230 PAYMENT_REASON_COMMENTS,
231 DELIVERY_CHANNEL_CODE,
232 EXTERNAL_BANK_ACCOUNT_ID,
233 --Bug 7357218 Quick Pay and Dispute Resolution Project
234 ORIGINAL_INVOICE_AMOUNT ,
235 DISPUTE_REASON,
236 --Third Party Payments
237 REMIT_TO_SUPPLIER_NAME,
238 REMIT_TO_SUPPLIER_ID ,
239 REMIT_TO_SUPPLIER_SITE,
240 REMIT_TO_SUPPLIER_SITE_ID,
241 RELATIONSHIP_ID,
242 REMIT_TO_SUPPLIER_NUM
243 FROM ap_invoices_interface
244 WHERE ((status is NULL) OR (status = 'REJECTED'))
245 AND source = p_source
246 AND ((p_invoice_interface_id IS NULL AND
247 NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
248 OR (invoice_id = p_invoice_interface_id))
249 AND NVL(workflow_flag,'D') = 'D'
250 AND ( (p_commit_cycles IS NULL)
251 OR (rownum <= p_commit_cycles))
252 AND ( (org_id IS NOT NULL AND
253 p_org_id IS NOT NULL AND
254 org_id = p_org_id)
255 OR (p_org_id IS NULL AND
256 org_id IS NOT NULL AND
257 (mo_global.check_access(org_id)= 'Y'))
258 OR (p_org_id is NOT NULL AND org_id IS NULL)
259 OR (p_org_id is NULL AND org_id IS NULL))
260 ORDER BY org_id,
261 invoice_id,
262 vendor_id,
263 vendor_num,
264 vendor_name,
265 vendor_site_id,
266 vendor_site_code,
267 invoice_num
268 For UPDATE of invoice_id NOWAIT;
269
270
271 CURSOR import_invoices_group is
272 SELECT invoice_id,
273 invoice_num,
274 invoice_type_lookup_code,
275 invoice_date,
276 po_number,
277 vendor_id,
278 vendor_num,
279 vendor_name,
280 vendor_site_id,
281 vendor_site_code,
282 invoice_amount,
283 invoice_currency_code,
284 exchange_rate,
285 exchange_rate_type,
286 exchange_date,
287 terms_id,
288 terms_name,
289 terms_date,
290 trim(description) description,
291 awt_group_id,
292 awt_group_name,
293 pay_awt_group_id,--bug6639866
294 pay_awt_group_name,--bug6639866
295 amount_applicable_to_discount,
296 sysdate,
297 last_updated_by,
298 last_update_login,
299 sysdate,
300 created_by,
301 status,
302 rtrim(attribute_category) attribute_category,
303 rtrim(attribute1) attribute1,
304 rtrim(attribute2) attribute2,
305 rtrim(attribute3) attribute3,
306 rtrim(attribute4) attribute4,
307 rtrim(attribute5) attribute5,
308 rtrim(attribute6) attribute6,
309 rtrim(attribute7) attribute7,
310 rtrim(attribute8) attribute8,
311 rtrim(attribute9) attribute9,
312 rtrim(attribute10) attribute10,
313 rtrim(attribute11) attribute11,
314 rtrim(attribute12) attribute12,
315 rtrim(attribute13) attribute13,
316 rtrim(attribute14) attribute14,
317 rtrim(attribute15) attribute15,
318 rtrim(global_attribute_category) global_attribute_category,
319 rtrim(global_attribute1) global_attribute1,
320 rtrim(global_attribute2) global_attribute2,
321 rtrim(global_attribute3) global_attribute3,
322 rtrim(global_attribute4) global_attribute4,
323 rtrim(global_attribute5) global_attribute5,
324 rtrim(global_attribute6) global_attribute6,
325 rtrim(global_attribute7) global_attribute7,
326 rtrim(global_attribute8) global_attribute8,
327 rtrim(global_attribute9) global_attribute9,
328 rtrim(global_attribute10) global_attribute10,
329 rtrim(global_attribute11) global_attribute11,
330 rtrim(global_attribute12) global_attribute12,
331 rtrim(global_attribute13) global_attribute13,
332 rtrim(global_attribute14) global_attribute14,
333 rtrim(global_attribute15) global_attribute15,
334 rtrim(global_attribute16) global_attribute16,
335 rtrim(global_attribute17) global_attribute17,
336 rtrim(global_attribute18) global_attribute18,
337 rtrim(global_attribute19) global_attribute19,
338 rtrim(global_attribute20) global_attribute20,
339 payment_currency_code,
340 payment_cross_rate,
341 payment_cross_rate_type,
342 payment_cross_rate_date,
343 doc_category_code,
344 voucher_num,
345 payment_method_code,
346 pay_group_lookup_code,
347 goods_received_date,
348 invoice_received_date,
349 gl_date,
350 accts_pay_code_combination_id,
351 -- bug 6509776
352 RTRIM(accts_pay_code_concatenated,'-'),
353 -- ussgl_transaction_code, - Bug 4277744
354 UPPER(exclusive_payment_flag),
355 prepay_num,
356 prepay_line_num,
357 prepay_apply_amount,
358 prepay_gl_date,
359 UPPER(invoice_includes_prepay_flag),
360 no_xrate_base_amount,
361 requester_id,
362 org_id,
363 operating_unit,
364 source,
365 group_id,
366 request_id,
367 workflow_flag,
368 vendor_email_address,
369 NVL(calc_tax_during_import_flag, 'N'), -- bug 6349739,bug6328293
370 control_amount,
371 add_tax_to_inv_amt_flag,
372 tax_related_invoice_id,
373 taxation_country,
374 document_sub_type,
375 supplier_tax_invoice_number,
376 supplier_tax_invoice_date,
377 supplier_tax_exchange_rate,
378 tax_invoice_recording_date,
379 tax_invoice_internal_seq,
380 legal_entity_id,
381 null,
382 ap_import_utilities_pkg.get_tax_only_rcv_matched_flag(invoice_id),
383 ap_import_utilities_pkg.get_tax_only_flag(invoice_id),
384 apply_advances_flag,
385 application_id,
386 product_table,
387 reference_key1,
388 reference_key2,
389 reference_key3,
390 reference_key4,
391 reference_key5,
392 reference_1,
393 reference_2,
394 net_of_retainage_flag,
395 cust_registration_code,
396 cust_registration_number,
397 paid_on_behalf_employee_id,
398 party_id, -- Added for Payment Requests
399 party_site_id,
400 pay_proc_trxn_type_code,
401 payment_function,
402 payment_priority,
403 BANK_CHARGE_BEARER,
404 REMITTANCE_MESSAGE1,
405 REMITTANCE_MESSAGE2,
406 REMITTANCE_MESSAGE3,
407 UNIQUE_REMITTANCE_IDENTIFIER,
408 URI_CHECK_DIGIT,
409 SETTLEMENT_PRIORITY,
410 PAYMENT_REASON_CODE,
411 PAYMENT_REASON_COMMENTS,
412 DELIVERY_CHANNEL_CODE,
413 EXTERNAL_BANK_ACCOUNT_ID,
414 --Bug 7357218 Quick Pay and Dispute Resolution Project
415 ORIGINAL_INVOICE_AMOUNT,
416 DISPUTE_REASON,
417 --Third Party Payments
418 REMIT_TO_SUPPLIER_NAME,
419 REMIT_TO_SUPPLIER_ID ,
420 REMIT_TO_SUPPLIER_SITE,
421 REMIT_TO_SUPPLIER_SITE_ID,
422 RELATIONSHIP_ID,
423 REMIT_TO_SUPPLIER_NUM
424 FROM ap_invoices_interface
425 WHERE ((status is NULL) OR (status = 'REJECTED'))
426 AND source = p_source
427 AND group_id = p_group_id
428 AND ((p_invoice_interface_id IS NULL AND
429 NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
430 OR (invoice_id = p_invoice_interface_id))
431 AND NVL(workflow_flag,'D') = 'D'
432 AND ( (p_commit_cycles IS NULL)
433 OR (rownum <= p_commit_cycles))
434 AND ( (org_id IS NOT NULL AND
435 p_org_id IS NOT NULL AND
436 org_id = p_org_id)
437 OR (p_org_id IS NULL AND
438 org_id IS NOT NULL AND
439 (mo_global.check_access(org_id)= 'Y'))
440 OR (p_org_id is NOT NULL AND org_id IS NULL)
441 OR (p_org_id is NULL AND org_id IS NULL))
442 ORDER BY org_id,
443 invoice_id,
444 vendor_id,
445 vendor_num,
446 vendor_name,
447 vendor_site_id,
448 vendor_site_code,
449 invoice_num
450 For UPDATE of invoice_id NOWAIT;
451
452 l_invoice_rec AP_IMPORT_INVOICES_PKG.r_invoice_info_rec;
453 l_invoice_lines_tab AP_IMPORT_INVOICES_PKG.t_lines_table;
454 l_default_last_updated_by NUMBER;
455 l_default_last_update_login NUMBER;
456 l_multi_currency_flag VARCHAR2(1);
457 l_make_rate_mandatory_flag VARCHAR2(1);
458 l_default_exchange_rate_type VARCHAR2(30);
459 l_base_currency_code VARCHAR2(15);
460 l_batch_control_flag VARCHAR2(1);
461 l_base_min_acct_unit NUMBER;
462 l_base_precision NUMBER;
463 l_sequence_numbering VARCHAR2(1);
464 l_awt_include_tax_amt VARCHAR2(1);
465 l_gl_date_from_get_info DATE;
466 -- l_ussgl_transcation_code VARCHAR2(30); - Bug 4277744
467 l_transfer_po_desc_flex_flag VARCHAR2(1);
468 l_gl_date_from_receipt_flag VARCHAR2(25);
469 l_purch_encumbrance_flag VARCHAR2(1);
470 l_retainage_ccid NUMBER;
471 l_pa_installed VARCHAR2(1):='N';
472 l_chart_of_accounts_id NUMBER;
473 l_positive_price_tolerance NUMBER;
474 l_negative_price_tolerance NUMBER;
475 l_qty_tolerance NUMBER;
476 l_qty_rec_tolerance NUMBER;
477 l_amt_tolerance NUMBER;
478 l_amt_rec_tolerance NUMBER;
479 l_max_qty_ord_tolerance NUMBER;
480 l_max_qty_rec_tolerance NUMBER;
481 l_max_amt_ord_tolerance NUMBER;
482 l_max_amt_rec_tolerance NUMBER;
483 l_goods_ship_amt_tolerance NUMBER;
484 l_goods_rate_amt_tolerance NUMBER;
485 l_goods_total_amt_tolerance NUMBER;
486 l_services_ship_amt_tolerance NUMBER;
487 l_services_rate_amt_tolerance NUMBER;
488 l_services_total_amt_tolerance NUMBER;
489 l_inv_doc_cat_override VARCHAR2(1):='N';
490 l_pay_curr_invoice_amount NUMBER;
491 l_invoice_amount_limit NUMBER;
492 l_hold_future_payments_flag VARCHAR2(1);
493 l_supplier_hold_reason VARCHAR2(240);
494 l_invoice_status VARCHAR2(1) :='Y';
495 l_gl_date DATE;
496 l_min_acct_unit NUMBER;
497 l_precision NUMBER;
498 l_payment_priority NUMBER;
499 l_batch_id NUMBER;
500 l_batch_name VARCHAR2(50);
501 l_continue_flag VARCHAR2(1) := 'Y';
502 l_fatal_error_flag VARCHAR2(1) := 'N';
503 l_base_invoice_id NUMBER(15);
504 l_invoice_currency_code VARCHAR2(15);
505 l_batch_exists_flag VARCHAR2(1) := 'N';
506 l_batch_type VARCHAR2(30);
507 l_valid_invoices_count NUMBER:=0;
508 l_match_mode VARCHAR2(25);
509 l_dbseqnm VARCHAR2(30);
510 l_dbseqid NUMBER;
511 l_seqval NUMBER;
512 l_apply_prepay_log LONG;
513 l_invoices_fetched NUMBER:=0;
514 l_actual_invoice_total NUMBER:=0;
515 import_invoice_failure EXCEPTION;
516 current_calling_sequence VARCHAR2(2000);
517 debug_info VARCHAR2(500);
518 l_total_invoice_amount NUMBER :=0;
519 l_calc_user_xrate VARCHAR2(1);
520 l_approval_workflow_flag VARCHAR2(1);
521 l_freight_code_combination_id NUMBER;
522 l_old_org_id NUMBER;
523 l_default_org_id NUMBER;
524 l_ou_count NUMBER;
525 l_default_ou_name VARCHAR2(240);
526 l_derived_operating_unit VARCHAR2(240);
527 l_null_org_id BOOLEAN;
528 l_total_count NUMBER := 0;
529 l_set_of_books_id NUMBER;
530 l_error_code VARCHAR2(500);
531
532 l_prepay_appl_log ap_prepay_pkg.Prepay_Appl_Log_Tab;
533 l_prepay_period_name VARCHAR2(25);
534 --Contract Payments
535 l_prepay_invoice_id NUMBER;
536 l_prepay_case_name VARCHAR2(50);
537 l_inv_amount_unpaid NUMBER;
538 l_amount_to_apply NUMBER;
539
540 l_allow_interest_invoices VARCHAR2(1); --bugfix:4113223
541 l_option_defined_org NUMBER; -- bug 5140002
542
543 TYPE numlist is TABLE OF ap_interface_rejections.parent_id%TYPE;
544
545 enums numlist;
546 --bug:4930111
547 l_add_days_settlement_date NUMBER;
548 --bug 4931755
549 l_disc_is_inv_less_tax_flag VARCHAR2(1);
550 l_exclude_freight_from_disc VARCHAR2(1);
551
552 l_exclusive_tax_amount NUMBER;
553 l_inv_hdr_amount NUMBER;
554 l_payment_status_flag VARCHAR2(50);
555 l_message1 VARCHAR2(50);
556 l_message2 VARCHAR2(50);
557 l_reset_match_status VARCHAR2(50);
558 l_liability_adjusted_flag VARCHAR2(50);
559 l_revalidate_ps VARCHAR2(50);
560 -- Bug 5448579
561 l_moac_org_table AP_IMPORT_INVOICES_PKG.moac_ou_tab_type;
562 l_fsp_org_table AP_IMPORT_INVOICES_PKG.fsp_org_tab_type;
563 l_index_org_id NUMBER;
564 l_asset_book_type FA_BOOK_CONTROLS.book_type_code%TYPE;
565
566 -- Bug 5645581.
567 l_inv_gl_date DATE; --Bug 5382889. LE Timezone
568 l_rts_txn_le_date DATE; --Bug 5382889. LE Timezone
569 l_inv_le_date DATE; --Bug 5382889. LE Timezone
570 l_sys_le_date DATE; --Bug 5382889. LE Timezone
571
572
573 --7567527
574 l_parameter_list wf_parameter_list_t;
575 l_event_key VARCHAR2(100);
576 l_event_name VARCHAR2(100) := 'oracle.apps.ap.invoice.import';
577
578 BEGIN
579
580 -- Update the calling sequence and initialize variables
581
582 current_calling_sequence := 'Import_invoices<- '||p_calling_sequence;
583
584 p_batch_error_flag := 'N';
585 l_gl_date_from_get_info := TRUNC(p_gl_date);
586
587 AP_IMPORT_INVOICES_PKG.g_debug_switch := p_debug_switch;
588 AP_IMPORT_INVOICES_PKG.g_source := p_source;
589 AP_IMPORT_INVOICES_PKG.g_program_application_id := FND_GLOBAL.prog_appl_id;
590 AP_IMPORT_INVOICES_PKG.g_program_id := FND_GLOBAL.conc_program_id;
591 AP_IMPORT_INVOICES_PKG.g_conc_request_id := p_conc_request_id;
592
593 debug_info := 'Request_id'||p_conc_request_id;
594 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
595 AP_IMPORT_UTILITIES_PKG.Print(
596 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
597 END IF;
598 -- Retropricing
599 IF AP_IMPORT_INVOICES_PKG.g_source = 'PPA' THEN
600 AP_IMPORT_INVOICES_PKG.g_invoices_table := 'AP_PPA_INVOICES_GT';
601 AP_IMPORT_INVOICES_PKG.g_invoice_lines_table := 'AP_PPA_INVOICE_LINES_GT';
602 AP_IMPORT_INVOICES_PKG.g_instructions_table := 'AP_PPA_INSTRUCTIONS_GT';
603 ELSE
604 AP_IMPORT_INVOICES_PKG.g_invoices_table := 'AP_INVOICES_INTERFACE';
605 AP_IMPORT_INVOICES_PKG.g_invoice_lines_table := 'AP_INVOICE_LINES_INTERFACE';
606 AP_IMPORT_INVOICES_PKG.g_instructions_table := NULL;
607 END IF;
608
609 -- Bug 5448579
610 ----------------------------------------------------------------
611 debug_info := '(Import_invoice 0.1) Calling Caching Function for Org Id/Name';
612 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
613 AP_IMPORT_UTILITIES_PKG.Print(
614 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
615 END IF;
616 IF (AP_IMPORT_UTILITIES_PKG.Cache_Org_Id_Name (
617 P_Moac_Org_Table => AP_IMPORT_INVOICES_PKG.g_moac_ou_tab,
618 P_Fsp_Org_Table => AP_IMPORT_INVOICES_PKG.g_fsp_ou_tab,
619 P_Calling_Sequence => current_calling_sequence ) <> TRUE) THEN
620 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
621 AP_IMPORT_UTILITIES_PKG.Print(
622 AP_IMPORT_INVOICES_PKG.g_debug_switch,
623 'Cache_Org_Id_Name <-'||current_calling_sequence);
624 END IF;
625 Raise import_invoice_failure;
626 END IF;
627
628 debug_info := '(Import_Invoices 0.2) Calling Caching Function for Currency';
629 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
630 AP_IMPORT_UTILITIES_PKG.Print(
631 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
632 END IF;
633 IF (AP_IMPORT_UTILITIES_PKG.Cache_Fnd_Currency (
634 P_Fnd_Currency_Table => AP_IMPORT_INVOICES_PKG.g_fnd_currency_tab,
635 P_Calling_Sequence => current_calling_sequence ) <> TRUE) THEN
636 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
637 AP_IMPORT_UTILITIES_PKG.Print(
638 AP_IMPORT_INVOICES_PKG.g_debug_switch,
639 'Cache_Fnd_Currency <-'||current_calling_sequence);
640 END IF;
641 Raise import_invoice_failure;
642 END IF;
643
644
645 debug_info := '(Import_Invoices 0.3) Calling Caching Function for Payment Method';
646 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
647 AP_IMPORT_UTILITIES_PKG.Print(
648 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
649 END IF;
650 IF (AP_IMPORT_UTILITIES_PKG.Cache_Payment_Method (
651 P_Payment_Method_Table => AP_IMPORT_INVOICES_PKG.g_payment_method_tab,
652 P_Calling_Sequence => current_calling_sequence ) <> TRUE) THEN
653 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
654 AP_IMPORT_UTILITIES_PKG.Print(
655 AP_IMPORT_INVOICES_PKG.g_debug_switch,
656 'Cache_Payment_Method <-'||current_calling_sequence);
657 END IF;
658 Raise import_invoice_failure;
659 END IF;
660
661 debug_info := '(Import_Invoices 0.4) Calling Caching Function for Payment Group';
662 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
663 AP_IMPORT_UTILITIES_PKG.Print(
664 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
665 END IF;
666 IF (AP_IMPORT_UTILITIES_PKG.Cache_Pay_Group (
667 P_Pay_Group_Table => AP_IMPORT_INVOICES_PKG.g_pay_group_tab,
668 P_Calling_Sequence => current_calling_sequence ) <> TRUE) THEN
669 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
670 AP_IMPORT_UTILITIES_PKG.Print(
671 AP_IMPORT_INVOICES_PKG.g_debug_switch,
672 'Cache_Pay_Group <-'||current_calling_sequence);
673 END IF;
674 Raise import_invoice_failure;
675 END IF;
676
677 debug_info := '(Import_Invoices 0.5) Caching Structure Id';
678 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
679 AP_IMPORT_UTILITIES_PKG.Print(
680 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
681 END IF;
682
683 BEGIN
684
685 SELECT structure_id
686 INTO AP_IMPORT_INVOICES_PKG.g_structure_id
687 FROM mtl_default_sets_view
688 WHERE functional_area_id = 2;
689
690 EXCEPTION WHEN OTHERS THEN
691 NULL;
692
693 END;
694
695
696 fnd_plsql_cache.generic_1tom_init(
697 'PeriodName',
698 lg_many_controller,
699 lg_generic_storage);
700
701 fnd_plsql_cache.generic_1tom_init(
702 'ValidateSegs',
703 lg_many_controller1,
704 lg_generic_storage1);
705
706 fnd_plsql_cache.generic_1tom_init(
707 'CodeCombinations',
708 lg_many_controller2,
709 lg_generic_storage2);
710
711 -- Bug 5572876
712 fnd_plsql_cache.generic_1tom_init(
713 'IncomeTaxType',
714 lg_incometax_controller,
715 lg_incometax_storage);
716
717 -- 5572876
718 fnd_plsql_cache.generic_1tom_init(
719 'IncomeTaxRegion',
720 lg_incometaxr_controller,
721 lg_incometaxr_storage);
722
723 --------------------------------------------------------
724 -- Step 1
725 -- Check control table for the import batch
726 --------------------------------------------------------
727
728 debug_info := '(Import_invoice 1) Check control table for the import batch';
729
730 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
731 AP_IMPORT_UTILITIES_PKG.Print(
732 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
733 END IF;
734 /*
735 IF (AP_IMPORT_UTILITIES_PKG.check_control_table(
736 p_source,
737 p_group_id,
738 current_calling_sequence) <> TRUE) THEN
739
740 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
741 AP_IMPORT_UTILITIES_PKG.Print(
742 AP_IMPORT_INVOICES_PKG.g_debug_switch,'check_control_table<-'
743 ||current_calling_sequence);
744 END IF;
745 Raise import_invoice_failure;
746 END IF;
747 */
748 --------------------------------------------------------
749 -- Step 2
750 -- AP_IMPORT_UTILITIES_PKG.Print source if debug is turned on and
751 -- get default last updated by and last update login information.
752 --------------------------------------------------------
753 debug_info := '(Import_invoice 2) Print Source';
754
755 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
756 AP_IMPORT_UTILITIES_PKG.Print(
757 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
758 END IF;
759
760 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
761 AP_IMPORT_UTILITIES_PKG.Print(
762 AP_IMPORT_INVOICES_PKG.g_debug_switch, 'p_source' || p_source);
763 END IF;
764
765 l_default_last_updated_by := to_number(FND_GLOBAL.USER_ID);
766 l_default_last_update_login := to_number(FND_GLOBAL.LOGIN_ID);
767
768 ----------------------------------------------------------------
769 -- Step 3 Delete any rejections from previous failed imports
770 -- of this invoice line
771 ----------------------------------------------------------------
772
773 debug_info := '(Import Invoice 3) Delete Rejections from previous failed '||
774 'imports';
775 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
776 AP_IMPORT_UTILITIES_PKG.Print(
777 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
778 END IF;
779
780 debug_info := '(Check_lines 3a) Select all the Rejected Invoices';
781 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
782 AP_IMPORT_UTILITIES_PKG.Print(
783 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
784 END IF;
785
786 -- Bug 4145391. To improve the performance of the import program coding two
787 -- different select stmts based on the parameter p_group_id
788 IF (p_group_id IS NULL) THEN
789 SELECT invoice_id
790 BULK COLLECT INTO enums
791 FROM ap_invoices_interface
792 WHERE ((status is NULL) or (status = 'REJECTED'))
793 AND source = p_source
794 AND ((p_invoice_interface_id IS NULL AND
795 NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
796 OR (invoice_id = p_invoice_interface_id))
797 AND nvl(workflow_flag,'D') = 'D'
798 AND ((org_id is NOT NULL AND
799 p_org_id is NOT NULL AND
800 org_id = p_org_id)
801 or (p_org_id is NULL AND
802 org_id is NOT NULL AND
803 (mo_global.check_access(org_id)= 'Y'))
804 or (p_org_id is NOT NULL AND
805 org_id is NULL)
806 or (p_org_id is NULL AND
807 org_id is NULL));
808 ELSE
809 SELECT invoice_id
810 BULK COLLECT INTO enums
811 FROM ap_invoices_interface
812 WHERE ((status is NULL) or (status = 'REJECTED'))
813 AND source = p_source
814 AND group_id = p_group_id
815 AND ((p_invoice_interface_id IS NULL AND
816 NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
817 OR (invoice_id = p_invoice_interface_id))
818 AND nvl(workflow_flag,'D') = 'D'
819 AND ((org_id is NOT NULL AND
820 p_org_id is NOT NULL AND
821 org_id = p_org_id)
822 or (p_org_id is NULL AND
823 org_id is NOT NULL AND
824 (mo_global.check_access(org_id)= 'Y'))
825 or (p_org_id is NOT NULL AND
826 org_id is NULL)
827 or (p_org_id is NULL AND
828 org_id is NULL));
829 END IF;
830
831 debug_info := '(Check_lines 3b) Delete invoices from ap_interface_rejections';
832
833 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
834 AP_IMPORT_UTILITIES_PKG.Print(
835 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
836 END IF;
837
838 -- Retropricing
839 --The PPA Rejections will be deleted from ap_interface_rejections
840 -- in the After Report Trigger of APXIIMPT.
841 IF enums.COUNT > 0 THEN
842
843 ForALL i IN enums.FIRST .. enums.LAST
844 DELETE FROM ap_interface_rejections
845 WHERE parent_table = 'AP_INVOICES_INTERFACE'
846 AND parent_id = enums(i);
847
848 ForALL i IN enums.FIRST .. enums.LAST
849 DELETE FROM ap_interface_rejections
850 WHERE parent_table = 'AP_INVOICE_LINES_INTERFACE'
851 AND parent_id IN (SELECT invoice_line_id
852 FROM ap_invoice_lines_interface
853 WHERE invoice_id = enums(i));
854 END IF;
855 --Start of Bug 6801046
856 debug_info := '(Check_lines 3c) Update requestid on the Selected Invoices';
857
858 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
859 AP_IMPORT_UTILITIES_PKG.Print(
860 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
861 END IF;
862
863 IF enums.COUNT > 0 THEN
864 ForALL i IN enums.FIRST .. enums.LAST
865 UPDATE AP_INVOICES_INTERFACE
866 SET request_id = AP_IMPORT_INVOICES_PKG.g_conc_request_id
867 WHERE invoice_id = enums(i);
868 END IF;
869 --End of Bug 6801046
870 ----------------------------------------------------------------
871 -- Step 4 Update the org_id whenever null IF operating unit
872 -- is not null.
873 ----------------------------------------------------------------
874 debug_info := '(Import Invoice 4) Update the org_id';
875 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
876 AP_IMPORT_UTILITIES_PKG.Print(
877 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
878 END IF;
879
880 debug_info := '(Import_Invoices 4a) Updating Interface WHERE org_id '||
881 'is null but operating unit is not null';
882 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
883 AP_IMPORT_UTILITIES_PKG.Print(
884 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
885 END IF;
886
887
888 --Bug 6839034 Added additional filters to the update below
889 -- Coding 2 different update stmts based on p_group_id to improve performance
890
891 IF (p_group_id IS NULL) THEN
892 UPDATE ap_invoices_interface i
893 SET org_id = (SELECT hr.organization_id org_id
894 FROM hr_operating_units hr,
895 per_business_groups per
896 WHERE hr.business_group_id = per.business_group_id
897 AND mo_global.check_access(hr.organization_id) = 'Y'
898 AND hr.name = i.operating_unit)
899 WHERE i.org_id is null
900 AND i.operating_unit is not null
901 AND ((status is NULL) OR (status = 'REJECTED'))
902 AND source = p_source
903 AND ((p_invoice_interface_id IS NULL AND
904 NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
905 OR (invoice_id = p_invoice_interface_id))
906 AND NVL(workflow_flag,'D') = 'D' ;
907
908 --Bug 6839034 Added ELSE part
909 ELSE
910
911 UPDATE ap_invoices_interface i
912 SET org_id = (SELECT hr.organization_id org_id
913 FROM hr_operating_units hr,
914 per_business_groups per
915 WHERE hr.business_group_id = per.business_group_id
916 AND mo_global.check_access(hr.organization_id) = 'Y'
917 AND hr.name = i.operating_unit)
918 WHERE i.org_id is null
919 AND i.operating_unit is not null
920 AND ((status is NULL) OR (status = 'REJECTED'))
921 AND source = p_source
922 AND group_id = p_group_id
923 AND ((p_invoice_interface_id IS NULL AND
924 NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
925 OR (invoice_id = p_invoice_interface_id))
926 AND NVL(workflow_flag,'D') = 'D' ;
927
928 END IF;
929
930 debug_info := '(Import_Invoices 4b) Getting Deafult Operating Unit '||
931 'Information';
932 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
933 AP_IMPORT_UTILITIES_PKG.Print(
934 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
935 END IF;
936
937
938 Mo_Utils.get_default_ou(
939 l_default_org_id,
940 l_default_ou_name,
941 l_ou_count);
942
943 ----------------------------------------------------------------
944 -- Step 5 Get number of invoices to process.
945 ----------------------------------------------------------------
946 debug_info := '(Import Invoices 5) Get The Total Number of Invoices '||
947 'In Interface';
948 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
949 AP_IMPORT_UTILITIES_PKG.Print(
950 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
951 END IF;
952
953 -- Bug 4145391. To improve the performance of the import program coding two
954 -- different select stmts based on the parameter p_group_id
955 IF (p_group_id IS NULL) THEN
956 BEGIN
957 SELECT count(*)
958 INTO l_total_count
959 FROM ap_invoices_interface
960 WHERE ((status is NULL) or (status = 'REJECTED'))
961 AND source = p_source
962 AND ((p_invoice_interface_id IS NULL AND
963 NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
964 OR (invoice_id = p_invoice_interface_id))
965 AND nvl(workflow_flag,'D') = 'D'
966 AND ((org_id is NOT NULL AND
967 p_org_id is NOT NULL AND
968 org_id = p_org_id)
969 or (p_org_id is NULL AND
970 org_id is NOT NULL AND
971 (mo_global.check_access(org_id)= 'Y'))
972 or (p_org_id is NOT NULL AND
973 org_id is NULL)
974 or (p_org_id is NULL AND
975 org_id is NULL))
976 AND ROWNUM = 1;
977
978 IF (l_total_count = 0) THEN
979 l_continue_flag := 'N';
980 END IF;
981
982 EXCEPTION
983 WHEN NO_DATA_FOUND THEN
984 l_continue_flag := 'N';
985 END;
986
987 ELSE
988 BEGIN
989 SELECT count(*)
990 INTO l_total_count
991 FROM ap_invoices_interface
992 WHERE ((status is NULL) or (status = 'REJECTED'))
993 AND source = p_source
994 AND group_id = p_group_id
995 AND ((p_invoice_interface_id IS NULL AND
996 NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
997 OR (invoice_id = p_invoice_interface_id))
998 AND nvl(workflow_flag,'D') = 'D'
999 AND ((org_id is NOT NULL AND
1000 p_org_id is NOT NULL AND
1001 org_id = p_org_id)
1002 or (p_org_id is NULL AND
1003 org_id is NOT NULL AND
1004 (mo_global.check_access(org_id)= 'Y'))
1005 or (p_org_id is NOT NULL AND
1006 org_id is NULL)
1007 or (p_org_id is NULL AND
1008 org_id is NULL))
1009 AND ROWNUM = 1;
1010
1011 IF (l_total_count = 0) THEN
1012 l_continue_flag := 'N';
1013 END IF;
1014
1015 EXCEPTION
1016 WHEN NO_DATA_FOUND THEN
1017 l_continue_flag := 'N';
1018 END;
1019 END IF;
1020
1021 -- Bug 5448579
1022 debug_info := '(Import_invoice 5.5) Unwinding Caching Org Id/Name';
1023 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1024 AP_IMPORT_UTILITIES_PKG.Print(
1025 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1026 END IF;
1027
1028 FOR i IN 1..g_moac_ou_tab.COUNT
1029 LOOP
1030 l_index_org_id := g_moac_ou_tab(i).org_id;
1031 l_moac_org_table(l_index_org_id).org_id := g_moac_ou_tab(i).org_id;
1032 l_moac_org_table(l_index_org_id).org_name := g_moac_ou_tab(i).org_name;
1033
1034 debug_info := 'Index Value: '||l_index_org_id
1035 ||', MOAC Cached Org_Id: '||l_moac_org_table(l_index_org_id).org_id
1036 ||', MOAC Cached Operating Unit: '|| l_moac_org_table(l_index_org_id).org_name;
1037
1038 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1039 AP_IMPORT_UTILITIES_PKG.Print(
1040 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1041 END IF;
1042 END LOOP;
1043
1044 FOR i IN 1..g_fsp_ou_tab.COUNT
1045 LOOP
1046 l_index_org_id := g_fsp_ou_tab(i).org_id;
1047 l_fsp_org_table(l_index_org_id).org_id := g_fsp_ou_tab(i).org_id;
1048
1049 debug_info := 'Index Value: '||l_index_org_id
1050 ||', FSP Cached Org_Id: '||l_fsp_org_table(l_index_org_id).org_id;
1051
1052 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1053 AP_IMPORT_UTILITIES_PKG.Print(
1054 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1055 END IF;
1056
1057 END LOOP;
1058
1059 ----------------------------------------------------------------
1060 -- Step 6 LOOP through invoices/Instructions(Retropricing)
1061 ----------------------------------------------------------------
1062 WHILE (l_continue_flag = 'Y') LOOP
1063
1064 debug_info := '(Import_invoice 6) Open import_invoices cursor';
1065 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1066 AP_IMPORT_UTILITIES_PKG.Print(
1067 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1068 END IF;
1069
1070 -- Bug 4145391. To improve the performance of the import program coding two
1071 -- different cursors based on the parameter p_group_id
1072 IF (p_group_id IS NULL) THEN
1073 OPEN import_invoices;
1074 ELSE
1075 OPEN import_invoices_group;
1076 END IF;
1077
1078 LOOP
1079 BEGIN --veramach bug 7121842
1080 -- Retropricing:
1081 -- Invoice/Instructions LOOP, cursor size always be less or equal to p_commit_cycle
1082 ---------------------------------------------------------------
1083 -- Step 7 FETCH invoice interface record INTO invoice record
1084 ---------------------------------------------------------------
1085
1086 debug_info := '(Import_invoice 7) FETCH import_invoices';
1087 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1088 AP_IMPORT_UTILITIES_PKG.Print(
1089 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1090 END IF;
1091
1092 -- Bug 4145391
1093 IF (p_group_id IS NULL) THEN
1094 FETCH import_invoices INTO l_invoice_rec;
1095 EXIT WHEN import_invoices%NOTFOUND
1096 OR import_invoices%NOTFOUND IS NULL;
1097 ELSE
1098 FETCH import_invoices_group INTO l_invoice_rec;
1099 EXIT WHEN import_invoices_group%NOTFOUND
1100 OR import_invoices_group%NOTFOUND IS NULL;
1101 END IF;
1102
1103 --
1104 AP_IMPORT_INVOICES_PKG.g_inv_sysdate := TRUNC(sysdate);
1105
1106 -- Set invoice counter to get invoice count for fetched invoices
1107
1108 l_invoices_fetched := l_invoices_fetched + 1;
1109 -- show output values (only IF debug_switch = 'Y')
1110
1111 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1112 AP_IMPORT_UTILITIES_PKG.Print(
1113 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1114 '------------------> invoice_id = '
1115 ||to_char(l_invoice_rec.invoice_id)
1116 ||' invoice_num = ' ||l_invoice_rec.invoice_num
1117 ||' invoice_type_lookup_code = '
1118 || l_invoice_rec.invoice_type_lookup_code
1119 ||' invoice_date = ' ||to_char(l_invoice_rec.invoice_date)
1120 ||' po_number = ' ||l_invoice_rec.po_number
1121 ||' vendor_id = ' ||to_char(l_invoice_rec.vendor_id)
1122 ||' vendor_num = ' ||l_invoice_rec.vendor_num
1123 ||' vendor_name = ' ||l_invoice_rec.vendor_name
1124 ||' vendor_site_id = ' ||to_char(l_invoice_rec.vendor_site_id)
1125 ||' vendor_site_code = ' ||l_invoice_rec.vendor_site_code
1126 ||' party_id = ' ||to_char(l_invoice_rec.party_id)
1127 ||' party_site_id = ' ||to_char(l_invoice_rec.party_site_id)
1128 ||' pay_proc_trxn_type_code = ' ||l_invoice_rec.pay_proc_trxn_type_code
1129 ||' payment_function = ' ||l_invoice_rec.payment_function
1130 ||' invoice_amount = ' ||to_char(l_invoice_rec.invoice_amount)
1131 ||' base_currency_code = ' ||l_base_currency_code
1132 ||' invoice_currency_code = '||l_invoice_rec.invoice_currency_code
1133 ||' payment_currency_code = '||l_invoice_rec.payment_currency_code
1134 ||' exchange_rate = ' ||to_char(l_invoice_rec.exchange_rate)
1135 ||' exchange_rate_type = '||l_invoice_rec.exchange_rate_type
1136 ||' exchange_date = ' ||to_char(l_invoice_rec.exchange_date)
1137 ||' terms_id = ' ||to_char(l_invoice_rec.terms_id)
1138 ||' terms_name = ' ||l_invoice_rec.terms_name
1139 ||' description = ' ||l_invoice_rec.description
1140 ||' awt_group_id = ' ||to_char(l_invoice_rec.awt_group_id)
1141 ||' awt_group_name = ' ||l_invoice_rec.awt_group_name
1142 ||' pay_awt_group_id = ' ||to_char(l_invoice_rec.pay_awt_group_id)
1143 ||' pay_awt_group_name = ' ||l_invoice_rec.pay_awt_group_name --bug6639866
1144 ||' last_update_date = ' ||to_char(l_invoice_rec.last_update_date)
1145 ||' last_updated_by = ' ||to_char(l_invoice_rec.last_updated_by)
1146 ||' last_update_login = '||to_char(l_invoice_rec.last_update_login)
1147 ||' creation_date = ' ||to_char(l_invoice_rec.creation_date)
1148 ||' attribute_category = '||l_invoice_rec.attribute_category
1149 ||' attribute1 = ' ||l_invoice_rec.attribute1
1150 ||' attribute2 = ' ||l_invoice_rec.attribute2
1151 ||' attribute3 = ' ||l_invoice_rec.attribute3
1152 ||' attribute4 = ' ||l_invoice_rec.attribute4
1153 ||' attribute5 = ' ||l_invoice_rec.attribute5
1154 ||' attribute6 = ' ||l_invoice_rec.attribute6
1155 ||' attribute7 = ' ||l_invoice_rec.attribute7
1156 ||' attribute8 = ' ||l_invoice_rec.attribute8
1157 ||' attribute9 = ' ||l_invoice_rec.attribute9
1158 ||' attribute10 = ' ||l_invoice_rec.attribute10
1159 ||' attribute11 = ' ||l_invoice_rec.attribute11
1160 ||' attribute12 = ' ||l_invoice_rec.attribute12
1161 ||' attribute13 = ' ||l_invoice_rec.attribute13
1162 ||' attribute14 = ' ||l_invoice_rec.attribute14
1163 ||' attribute15 = ' ||l_invoice_rec.attribute15
1164 ||' global_attribute_category = '
1165 || l_invoice_rec.global_attribute_category
1166 ||' global_attribute1 = ' ||l_invoice_rec.global_attribute1
1167 ||' global_attribute2 = ' ||l_invoice_rec.global_attribute2
1168 ||' global_attribute3 = ' ||l_invoice_rec.global_attribute3
1169 ||' global_attribute4 = ' ||l_invoice_rec.global_attribute4
1170 ||' global_attribute5 = ' ||l_invoice_rec.global_attribute5
1171 ||' global_attribute6 = ' ||l_invoice_rec.global_attribute6
1172 ||' global_attribute7 = ' ||l_invoice_rec.global_attribute7
1173 ||' global_attribute8 = ' ||l_invoice_rec.global_attribute8
1174 ||' global_attribute9 = ' ||l_invoice_rec.global_attribute9
1175 ||' global_attribute10 = '||l_invoice_rec.global_attribute10
1176 ||' global_attribute11 = '||l_invoice_rec.global_attribute11
1177 ||' global_attribute12 = '||l_invoice_rec.global_attribute12
1178 ||' global_attribute13 = '||l_invoice_rec.global_attribute13
1179 ||' global_attribute14 = '||l_invoice_rec.global_attribute14
1180 ||' global_attribute15 = '||l_invoice_rec.global_attribute15
1181 ||' global_attribute16 = '||l_invoice_rec.global_attribute16
1182 ||' global_attribute17 = '||l_invoice_rec.global_attribute17
1183 ||' global_attribute18 = '||l_invoice_rec.global_attribute18
1184 ||' global_attribute19 = '||l_invoice_rec.global_attribute19
1185 ||' global_attribute20 = '||l_invoice_rec.global_attribute20
1186 ||' doc_category_code = '||l_invoice_rec.doc_category_code
1187 ||' voucher_num = ' ||l_invoice_rec.voucher_num
1188 ||' payment_method_code = '
1189 || l_invoice_rec.payment_method_code
1190 ||' pay_group_lookup_code = '||l_invoice_rec.pay_group_lookup_code
1191 ||' goods_received_date = '
1192 || to_char(l_invoice_rec.goods_received_date)
1193 ||' invoice_received_date = '
1194 || to_char(l_invoice_rec.invoice_received_date)
1195 ||' exclusive_payment_flag = '
1196 || l_invoice_rec.exclusive_payment_flag
1197 ||' prepay_num = ' ||l_invoice_rec.prepay_num
1198 ||' prepay_line_num = ' ||l_invoice_rec.prepay_line_num
1199 ||' prepay_apply_amount = '||l_invoice_rec.prepay_apply_amount
1200 ||' prepay_gl_date = ' ||l_invoice_rec.prepay_gl_date
1201 ||' set_of_books_id = '||l_invoice_rec.set_of_books_id
1202 ||' legal_entity_id = '||l_invoice_rec.legal_entity_id
1203 ||' tax_only_flag = '||l_invoice_rec.tax_only_flag
1204 ||' tax_only_rcv_matched_flag = '||l_invoice_rec.tax_only_rcv_matched_flag
1205 --Third Party Payments
1206 ||' remit_to_supplier_name = '||l_invoice_rec.remit_to_supplier_name
1207 ||' remit_to_supplier_id = '||l_invoice_rec.remit_to_supplier_id
1208 ||' remit_to_supplier_site = '||l_invoice_rec.remit_to_supplier_site
1209 ||' remit_to_supplier_site_id = '||l_invoice_rec.remit_to_supplier_site_id
1210 ||' relationship_id = '||l_invoice_rec.relationship_id
1211 ||' remit_to_supplier_num = '||l_invoice_rec.remit_to_supplier_num
1212 );
1213 END IF;
1214
1215 ---------------------------------------------------------------
1216 -- Step 8 Check for inconsistent OU
1217 ----------------------------------------------------------------
1218 debug_info := '(Import Invoices 8) Checking for Inconsistent OU';
1219 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1220 AP_IMPORT_UTILITIES_PKG.Print(
1221 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1222 END IF;
1223
1224 IF l_invoice_rec.org_id is NULL THEN
1225 IF (l_ou_count > 1 AND p_org_id is NULL) THEN
1226 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections
1227 (AP_IMPORT_INVOICES_PKG.g_invoices_table,
1228 l_invoice_rec.invoice_id,
1229 'NO OPERATING UNIT',
1230 l_default_last_updated_by,
1231 l_default_last_update_login,
1232 current_calling_sequence) <> TRUE) THEN
1233
1234 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1235 AP_IMPORT_UTILITIES_PKG.Print(
1236 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1237 'insert_rejections<- '||current_calling_sequence);
1238 END IF;
1239 Raise import_invoice_failure;
1240 END IF; --Insert rejections
1241
1242 l_invoice_status := 'N';
1243 l_null_org_id := TRUE;
1244
1245 ELSIF (l_ou_count = 1 AND p_org_id is NULL) THEN
1246
1247 UPDATE ap_invoices_interface
1248 SET org_id = l_default_org_id
1249 WHERE invoice_id = l_invoice_rec.invoice_id ;
1250
1251 l_invoice_rec.org_id := l_default_org_id;
1252 l_invoice_status := 'Y';
1253 l_null_org_id := TRUE;
1254
1255 ELSIF (p_org_id is NOT NULL) THEN
1256
1257 UPDATE ap_invoices_interface
1258 SET org_id = p_org_id
1259 WHERE invoice_id = l_invoice_rec.invoice_id ;
1260
1261 l_invoice_rec.org_id := p_org_id;
1262 l_invoice_status := 'Y';
1263 l_null_org_id := TRUE;
1264
1265 END IF; -- OU count AND p_org_id
1266
1267 ELSE -- invoice_rec.org_id is not null
1268
1269 /* Following block is for bug 5140002 */
1270 /* BEGIN
1271
1272 SELECT org_id
1273 INTO l_option_defined_org
1274 FROM financials_system_parameters
1275 WHERE org_id = l_invoice_rec.org_id;
1276
1277 EXCEPTION
1278 WHEN NO_DATA_FOUND THEN
1279 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections
1280 (AP_IMPORT_INVOICES_PKG.g_invoices_table,
1281 l_invoice_rec.invoice_id,
1282 'UNDEFINED OPERATING UNIT',
1283 l_default_last_updated_by,
1284 l_default_last_update_login,
1285 current_calling_sequence) <> TRUE) THEN
1286
1287 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1288 AP_IMPORT_UTILITIES_PKG.Print(
1289 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1290 'insert_rejections<- '||current_calling_sequence);
1291 END IF;
1292 Raise import_invoice_failure;
1293 END IF; -- Insert rejections
1294
1295 l_invoice_status := 'N';
1296 l_null_org_id := FALSE;
1297
1298 END ; */
1299
1300 -- Big 5448579. Replace the above lock
1301 IF l_fsp_org_table.exists(l_invoice_rec.org_id) THEN
1302
1303 Null;
1304
1305 ELSE
1306
1307 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections
1308 (AP_IMPORT_INVOICES_PKG.g_invoices_table,
1309 l_invoice_rec.invoice_id,
1310 'UNDEFINED OPERATING UNIT',
1311 l_default_last_updated_by,
1312 l_default_last_update_login,
1313 current_calling_sequence) <> TRUE) THEN
1314
1315 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1316 AP_IMPORT_UTILITIES_PKG.Print(
1317 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1318 'insert_rejections<- '||current_calling_sequence);
1319 END IF;
1320 Raise import_invoice_failure;
1321 END IF; -- Insert rejections
1322
1323 l_invoice_status := 'N';
1324 l_null_org_id := FALSE;
1325
1326 END IF;
1327
1328 IF l_invoice_rec.operating_unit is NOT NULL THEN
1329 -- Bug 5448579
1330 -- l_derived_operating_unit :=
1331 -- mo_global.get_ou_name(l_invoice_rec.org_id);
1332 l_derived_operating_unit := l_moac_org_table(l_invoice_rec.org_id).org_name;
1333
1334 debug_info := ' Derived Operating Unit: '||l_derived_operating_unit;
1335 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1336 AP_IMPORT_UTILITIES_PKG.Print(
1337 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1338 END IF;
1339
1340 IF l_invoice_rec.operating_unit <> l_derived_operating_unit THEN
1341 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections
1342 (AP_IMPORT_INVOICES_PKG.g_invoices_table,
1343 l_invoice_rec.invoice_id,
1344 'INCONSISTENT OPERATING UNITS',
1345 l_default_last_updated_by,
1346 l_default_last_update_login,
1347 current_calling_sequence) <> TRUE) THEN
1348
1349 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1350 AP_IMPORT_UTILITIES_PKG.Print(
1351 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1352 'insert_rejections<- '||current_calling_sequence);
1353 END IF;
1354 Raise import_invoice_failure;
1355 END IF; -- Insert rejections
1356
1357 l_invoice_status := 'N';
1358 l_null_org_id := FALSE;
1359
1360 ELSE -- operating units are consistent
1361
1362 l_invoice_status := 'Y';
1363 l_null_org_id := FALSE;
1364
1365 END IF;
1366
1367 ELSE -- operating unit name was null in invoice rec
1368
1369 l_invoice_status := 'Y';
1370 l_null_org_id := FALSE;
1371
1372 END IF;
1373
1374 END IF; -- invoice rec org id is null
1375
1376
1377 -----------------------------------------------------
1378 -- Step 9 Set the org context AND cache lookup codes
1379 -- AND parameters. IF batch control enabled, get batch
1380 -- id IF org id has changed.
1381 -----------------------------------------------------
1382 debug_info := '(Import Invoice 9a) Setting the org Context';
1383 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1384 AP_IMPORT_UTILITIES_PKG.Print(
1385 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1386 END IF;
1387
1388 IF l_invoice_status = 'Y' THEN
1389 IF l_invoice_rec.org_id <> NVL(l_old_org_id, -3115) THEN
1390 Mo_Global.set_policy_context('S', l_invoice_rec.org_id);
1391
1392 debug_info := '(Import_invoice 9b) Call get_info to get '||
1393 'required info';
1394 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1395 AP_IMPORT_UTILITIES_PKG.Print(
1396 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1397 END IF;
1398
1399 IF (AP_IMPORT_UTILITIES_PKG.get_info(
1400 l_invoice_rec.org_id, -- IN
1401 l_set_of_books_id, -- OUT NOCOPY
1402 l_multi_currency_flag, -- OUT NOCOPY
1403 l_make_rate_mandatory_flag, -- OUT NOCOPY
1404 l_default_exchange_rate_type, -- OUT NOCOPY
1405 l_base_currency_code, -- OUT NOCOPY
1406 l_batch_control_flag, -- OUT NOCOPY
1407 l_invoice_currency_code, -- OUT NOCOPY
1408 l_base_min_acct_unit, -- OUT NOCOPY
1409 l_base_precision, -- OUT NOCOPY
1410 l_sequence_numbering, -- OUT NOCOPY
1411 l_awt_include_tax_amt, -- OUT NOCOPY
1412 l_gl_date_from_get_info, -- IN OUT NOCOPY
1413 -- l_ussgl_transcation_code, -- OUT NOCOPY -Bug 4277744
1414 l_transfer_po_desc_flex_flag, -- OUT NOCOPY
1415 l_gl_date_from_receipt_flag, -- OUT NOCOPY
1416 l_purch_encumbrance_flag, -- OUT NOCOPY
1417 l_retainage_ccid, -- OUT NOCOPY
1418 l_pa_installed, -- OUT NOCOPY
1419 l_chart_of_accounts_id, -- OUT NOCOPY
1420 l_inv_doc_cat_override, -- OUT NOCOPY
1421 l_calc_user_xrate, -- OUT NOCOPY
1422 current_calling_sequence,
1423 l_approval_workflow_flag, -- OUT NOCOPY
1424 l_freight_code_combination_id, -- OUT NOCOPY
1425 l_allow_interest_invoices, -- OUT NOCOPY
1426 l_add_days_settlement_date, -- OUT NOCOPY --bug4930111
1427 l_disc_is_inv_less_tax_flag, -- OUT NOCOPY --bug4931755
1428 AP_IMPORT_INVOICES_PKG.g_source, -- IN --bug5382889 LE TimeZone
1429 l_invoice_rec.invoice_date, -- IN --bug5382889 LE TimeZone
1430 l_invoice_rec.goods_received_date, -- IN --bug5382889 LE TimeZone
1431 l_asset_book_type -- OUT NOCOPY --Bug 5448579
1432 ) <> TRUE) THEN
1433 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1434 AP_IMPORT_UTILITIES_PKG.Print(
1435 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1436 'get_info<-'||current_calling_sequence);
1437 END IF;
1438 Raise import_invoice_failure;
1439 END IF;
1440 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1441 AP_IMPORT_UTILITIES_PKG.Print(
1442 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1443 '------------------> '
1444 ||' p_org_id = '||to_char(l_invoice_rec.org_id)
1445 ||' p_set_of_books_id = '|| to_char(l_set_of_books_id)
1446 ||' l_multi_currency_flag = '||l_multi_currency_flag
1447 ||' l_make_rate_mANDatory_flag = '||l_make_rate_mandatory_flag
1448 ||' l_default_exchange_rate_type = '
1449 || l_default_exchange_rate_type
1450 ||' l_base_currency_code = ' ||l_base_currency_code
1451 ||' l_batch_control_flag = ' ||l_batch_control_flag
1452 ||' l_payment_cross_rate = '
1453 || to_char(l_invoice_rec.payment_cross_rate)
1454 ||' l_base_min_acct_unit = ' ||to_char(l_base_min_acct_unit)
1455 ||' l_base_precision = ' ||to_char(l_base_precision)
1456 ||' l_sequence_numbering = ' ||l_sequence_numbering
1457 ||' l_awt_include_tax_amt = ' ||l_awt_include_tax_amt
1458 ||' l_gl_date_from_get_info = ' ||to_char(l_gl_date_from_get_info)
1459 -- Removed for bug 4277744
1460 -- ||' l_ussgl_transcation_code = '||l_ussgl_transcation_code
1461 ||' l_gl_date_from_receipt_flag = '||l_gl_date_from_receipt_flag
1462 ||' l_purch_encumbrance_flag = '||l_purch_encumbrance_flag
1463 ||' l_chart_of_accounts_id = ' ||to_char(l_chart_of_accounts_id)
1464 ||' l_pa_installed = ' ||l_pa_installed
1465 ||' l_positive_price_tolerance = '
1466 || to_char(l_positive_price_tolerance)
1467 ||' l_negative_price_tolerance = '
1468 || to_char(l_negative_price_tolerance)
1469 ||' l_qty_tolerance = ' ||to_char(l_qty_tolerance)
1470 ||' l_max_qty_ord_tolerance = ' ||to_char(l_max_qty_ord_tolerance)
1471 ||' l_inv_doc_cat_override = ' ||l_inv_doc_cat_override
1472 ||' l_allow_interest_invoices = ' ||l_allow_interest_invoices);
1473 END IF;
1474
1475 -- Retek Integration bug 6349739
1476 IF AP_IMPORT_INVOICES_PKG.g_source = 'RETEK' THEN
1477 -- get the segment delimiter
1478 AP_IMPORT_INVOICES_PKG.g_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
1479 'SQLGL',
1480 'GL#',
1481 l_chart_of_accounts_id);
1482 END IF;
1483
1484 --------------------------------------------------------
1485 -- Step 9c
1486 -- Get batch_id first IF batch_control is on
1487 -- This batch_id is for the Invoice Batch Name
1488 -- Retropricing: It seems the get_batch_id will be called
1489 -- again and again for new batches creating a gap in the
1490 -- batch sequence
1491 --------------------------------------------------------
1492 IF (NVL(l_batch_control_flag,'N') = 'Y' ) THEN
1493 debug_info := '(Import_invoice 9c) Get batch_id IF '||
1494 'batch_control is on';
1495 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1496 AP_IMPORT_UTILITIES_PKG.Print(
1497 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1498 END IF;
1499
1500 IF (AP_IMPORT_UTILITIES_PKG.get_batch_id(
1501 p_batch_name,
1502 l_batch_id,
1503 l_batch_type,
1504 current_calling_sequence) <> TRUE) THEN
1505
1506 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1507 AP_IMPORT_UTILITIES_PKG.Print(
1508 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1509 'get_batch_id<-'||current_calling_sequence);
1510 END IF;
1511 Raise import_invoice_failure;
1512
1513 END IF;
1514 --
1515 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1516 AP_IMPORT_UTILITIES_PKG.Print(
1517 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1518 '------------------> l_batch_id = ' || to_char(l_batch_id)
1519 ||' l_batch_type = '||l_batch_type);
1520 END IF;
1521
1522 ----------------------------------------------------------------
1523 -- IF there is no batch id AND batch control is turned on
1524 -- batch error is raised AND STOP PROCESSING. Fatal error message
1525 -- should be Printed on the report in this case.We do not have
1526 -- a reject code in this case.
1527 ----------------------------------------------------------------
1528 IF ( l_batch_id is NULL ) THEN
1529 p_batch_error_flag := 'Y';
1530 RETURN(TRUE);
1531 ELSE
1532 p_print_batch := 'Y';
1533 END IF;
1534
1535 END IF; -- NVL(l_batch_control_flag,'N') = 'Y'
1536 END IF; -- org id is <> old org id
1537 END IF; -- invoice status = Y
1538
1539
1540 ----------------------------------------------------------------
1541 -- Retropricing: IF source = 'PPA' Go to Step 16.
1542 ----------------------------------------------------------------
1543 IF AP_IMPORT_INVOICES_PKG.g_source <> 'PPA' THEN
1544 --
1545 -----------------------------------------------------
1546 -- Step 10 Get GL Date
1547 -----------------------------------------------------
1548 --
1549 IF (l_invoice_rec.gl_date is NOT NULL) THEN
1550 debug_info := '(Import Invoice 10a) Default GL Date From Invoice ';
1551 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1552 AP_IMPORT_UTILITIES_PKG.Print(
1553 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1554 END IF;
1555 ELSE
1556 debug_info := '(Import Invoice 10b) Default GL Date Based on Calculation ';
1557 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1558 AP_IMPORT_UTILITIES_PKG.Print(
1559 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1560 END IF;
1561
1562 -- Bug 5654581. Moving Gl_Date Related code here
1563 IF AP_IMPORT_INVOICES_PKG.g_source = 'ERS' THEN -- bug 5382889, LE TimeZone
1564
1565 debug_info := 'Determine gl_date for ERS invoice';
1566
1567 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1568 AP_IMPORT_UTILITIES_PKG.Print
1569 (AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
1570 END IF;
1571
1572 l_rts_txn_le_date := INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Ou(
1573 p_trxn_date => nvl(l_invoice_rec.goods_received_date,
1574 l_invoice_rec.invoice_date)
1575 ,p_ou_id => l_invoice_rec.org_id);
1576
1577 l_inv_le_date := INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Ou(
1578 p_trxn_date => l_invoice_rec.invoice_date
1579 ,p_ou_id => l_invoice_rec.org_id);
1580
1581 l_sys_le_date := INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Ou(
1582 p_trxn_date => sysdate
1583 ,p_ou_id => l_invoice_rec.org_id);
1584
1585
1586 /* The gl_date id determined from the flag gl_date_from_receipt_flag
1587 If the flag = 'I' -- take Invoice_date
1588 = 'S' -- take System date
1589 = 'N' -- take nvl(receipt_date, invoice_date)
1590 = 'Y' -- take nvl(receipt_date, sysdate)
1591 Note here that the Invoice date is no longer the same as the receipt_date,
1592 i.e. the RETURN tranasaction_date , so case I and N are no longer the same */
1593
1594 debug_info := 'Determine invoice gl_date from LE Timezone API ';
1595 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1596 AP_IMPORT_UTILITIES_PKG.Print
1597 (AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
1598 END IF;
1599
1600 If (l_gl_date_from_receipt_flag = 'I') Then
1601 l_inv_gl_date := l_inv_le_date;
1602 Elsif (l_gl_date_from_receipt_flag = 'N') Then
1603 l_inv_gl_date := nvl(l_rts_txn_le_date, l_inv_le_date);
1604 Elsif (l_gl_date_from_receipt_flag = 'S') Then
1605 l_inv_gl_date := l_sys_le_date;
1606 Elsif (l_gl_date_from_receipt_flag = 'Y') Then
1607 l_inv_gl_date := nvl(l_rts_txn_le_date, l_sys_le_date);
1608 End if;
1609
1610 l_invoice_rec.gl_date := l_inv_gl_date;
1611
1612 ELSE
1613
1614 IF p_gl_date IS NULL THEN
1615
1616 IF (l_gl_date_from_receipt_flag IN ('S', 'Y')) THEN
1617 debug_info := ' GL Date is Sysdate based on gl_date_reciept_flaf option';
1618 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1619 AP_IMPORT_UTILITIES_PKG.Print
1620 (AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1621 END IF;
1622
1623 l_invoice_rec.gl_date := AP_IMPORT_INVOICES_PKG.g_inv_sysdate;
1624
1625 ELSE
1626
1627 IF l_invoice_rec.invoice_date is NOT NULL THEN
1628 debug_info := ' GL Date is Invoice Date';
1629 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1630 AP_IMPORT_UTILITIES_PKG.Print
1631 (AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1632 END IF;
1633
1634 l_invoice_rec.gl_date := l_invoice_rec.invoice_date;
1635 ELSE
1636 debug_info := ' GL Date is Sysdate Date';
1637 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1638 AP_IMPORT_UTILITIES_PKG.Print
1639 (AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1640 END IF;
1641
1642 l_invoice_rec.gl_date := AP_IMPORT_INVOICES_PKG.g_inv_sysdate;
1643 END IF;
1644
1645 END IF;
1646
1647 ELSE
1648
1649 debug_info := ' GL Date is Parameter Gl Date';
1650 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1651 AP_IMPORT_UTILITIES_PKG.Print
1652 (AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1653 END IF;
1654
1655 l_invoice_rec.gl_date := p_gl_date;
1656 END IF;
1657
1658 END IF; --- end g_source = 'ERS'
1659
1660 /*
1661 IF l_gl_date_from_get_info is NULL THEN
1662 IF l_invoice_rec.invoice_date is NOT NULL THEN
1663 l_invoice_rec.gl_date := l_invoice_rec.invoice_date;
1664 ELSE
1665 l_invoice_rec.gl_date := AP_IMPORT_INVOICES_PKG.g_inv_sysdate;
1666 END IF;
1667 ELSIF l_gl_date_from_get_info is NOT NULL THEN
1668 l_invoice_rec.gl_date := l_gl_date_from_get_info;
1669 END IF; */
1670 END IF;
1671
1672 l_invoice_rec.invoice_date := TRUNC(l_invoice_rec.invoice_date);
1673 l_invoice_rec.gl_date := TRUNC(l_invoice_rec.gl_date);
1674
1675 -- For bug 2984396. Added by LGOPALSA.
1676 -- Added trunc for all date variables.
1677
1678 If l_invoice_rec.exchange_date is not null Then
1679 l_invoice_rec.exchange_date :=
1680 trunc(l_invoice_rec.exchange_date);
1681 End if;
1682
1683 If l_invoice_rec.goods_received_date is not null Then
1684 l_invoice_rec.goods_received_date :=
1685 trunc(l_invoice_rec.goods_received_date);
1686 End if;
1687
1688 If l_invoice_rec.invoice_received_date is not null Then
1689 l_invoice_rec.invoice_received_date :=
1690 trunc(l_invoice_rec.invoice_received_date);
1691 End if;
1692
1693 If l_invoice_rec.terms_date is not null Then
1694 l_invoice_rec.terms_date := trunc(l_invoice_rec.terms_date);
1695 End if;
1696
1697 -- End for bug2984396.
1698
1699 ----------------------------
1700 -- Step 11
1701 -- Validate invoice level
1702 ----------------------------
1703
1704 debug_info := '(Import_invoice 11) Validate invoice ';
1705 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1706 AP_IMPORT_UTILITIES_PKG.Print(
1707 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1708 END IF;
1709 AP_IMPORT_UTILITIES_PKG.Print(
1710 AP_IMPORT_INVOICES_PKG.g_debug_switch, 'SOBID is :'||
1711 l_invoice_rec.set_of_books_id);
1712
1713 IF (AP_IMPORT_VALIDATION_PKG.v_check_invoice_validation
1714 (l_invoice_rec, -- IN OUT
1715 l_match_mode, -- OUT
1716 l_min_acct_unit, -- OUT
1717 l_precision, -- OUT
1718 l_positive_price_tolerance, -- OUT
1719 l_negative_price_tolerance, -- OUT
1720 l_qty_tolerance, -- OUT
1721 l_qty_rec_tolerance, -- OUT
1722 l_max_qty_ord_tolerance, -- OUT
1723 l_max_qty_rec_tolerance, -- OUT
1724 l_amt_tolerance, -- OUT
1725 l_amt_rec_tolerance, -- OUT
1726 l_max_amt_ord_tolerance, -- OUT
1727 l_max_amt_rec_tolerance, -- OUT
1728 l_goods_ship_amt_tolerance, -- OUT
1729 l_goods_rate_amt_tolerance, -- OUT
1730 l_goods_total_amt_tolerance, -- OUT
1731 l_services_ship_amt_tolerance, -- OUT
1732 l_services_rate_amt_tolerance, -- OUT
1733 l_services_total_amt_tolerance, -- OUT
1734 l_base_currency_code, -- IN
1735 l_multi_currency_flag, -- IN
1736 l_set_of_books_id, -- IN
1737 l_default_exchange_rate_type, -- IN
1738 l_make_rate_mandatory_flag, -- IN
1739 l_default_last_updated_by, -- IN
1740 l_default_last_update_login, -- IN
1741 l_fatal_error_flag, -- OUT
1742 l_invoice_status, -- OUT
1743 l_calc_user_xrate, -- IN
1744 l_prepay_period_name, -- IN OUT
1745 l_prepay_invoice_id, -- OUT --Contract Payments
1746 l_prepay_case_name, -- OUT --Contract Payments
1747 p_conc_request_id,
1748 l_allow_interest_invoices, -- IN
1749 current_calling_sequence) <> TRUE) THEN
1750 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1751 AP_IMPORT_UTILITIES_PKG.Print(
1752 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1753 'v_check_invoice_validation<-'||current_calling_sequence);
1754 END IF;
1755 Raise import_invoice_failure;
1756 END IF;
1757 --
1758 -- show output values (only IF debug_switch = 'Y')
1759 --
1760 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1761 AP_IMPORT_UTILITIES_PKG.Print( AP_IMPORT_INVOICES_PKG.g_debug_switch,
1762 '------------------> vendor_id = '||to_char(l_invoice_rec.vendor_id)
1763 ||' vendor_site_id = ' ||to_char(l_invoice_rec.vendor_site_id)
1764 ||' invoice_status = ' ||l_invoice_status
1765 ||' terms_id = ' ||to_char(l_invoice_rec.terms_id)
1766 ||' fatal_error_flag = ' ||l_fatal_error_flag
1767 ||' invoice_type_lookup_code = '
1768 ||l_invoice_rec.invoice_type_lookup_code
1769 ||' match_mode = ' ||l_match_mode);
1770 END IF;
1771
1772 IF (( l_invoice_status = 'Y') AND
1773 (NVL(l_fatal_error_flag,'N') = 'N')) THEN
1774
1775 --------------------------
1776 -- Step 12
1777 -- Validate invoice lines
1778 --------------------------
1779 debug_info := '(Import_invoice 12) Validate line';
1780 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1781 AP_IMPORT_UTILITIES_PKG.Print(
1782 AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
1783 END IF;
1784 AP_IMPORT_UTILITIES_PKG.Print(
1785 AP_IMPORT_INVOICES_PKG.g_debug_switch, 'SOBID is :'||l_invoice_rec.set_of_books_id);
1786
1787 IF (AP_IMPORT_VALIDATION_PKG.v_check_lines_validation (
1788 l_invoice_rec, -- IN
1789 l_invoice_lines_tab, -- OUT NOCOPY
1790 l_invoice_rec.gl_date, -- IN
1791 l_gl_date_from_receipt_flag, -- IN
1792 l_positive_price_tolerance, -- IN
1793 l_pa_installed, -- IN
1794 l_qty_tolerance, -- IN
1795 l_amt_tolerance, -- IN
1796 l_max_qty_ord_tolerance, -- IN
1797 l_max_amt_ord_tolerance, -- IN
1798 l_min_acct_unit, -- IN
1799 l_precision, -- IN
1800 l_base_currency_code, -- IN
1801 l_base_min_acct_unit, -- IN
1802 l_base_precision, -- IN
1803 l_set_of_books_id, -- IN
1804 l_asset_book_type, -- IN -- Bug 5448579
1805 l_chart_of_accounts_id, -- IN
1806 l_freight_code_combination_id, -- IN
1807 l_purch_encumbrance_flag, -- IN
1808 l_retainage_ccid, -- IN
1809 l_default_last_updated_by, -- IN
1810 l_default_last_update_login, -- IN
1811 l_invoice_status, -- OUT NOCOPY
1812 current_calling_sequence) <> TRUE) THEN
1813
1814 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1815 AP_IMPORT_UTILITIES_PKG.Print(
1816 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1817 'v_check_lines_validation<-'||current_calling_sequence);
1818 END IF;
1819 Raise import_invoice_failure;
1820 END IF;
1821 END IF;
1822
1823 AP_IMPORT_UTILITIES_PKG.Print(
1824 AP_IMPORT_INVOICES_PKG.g_debug_switch, 'invoice_status is:'||l_invoice_status);
1825
1826 -- Payment Request: Do not call eTax API for Payment Requests
1827 IF ((l_invoice_status = 'Y') AND
1828 (l_invoice_rec.invoice_type_lookup_code <> 'PAYMENT REQUEST')) THEN
1829 --------------------------------------------------------------
1830 -- Step 13. Call validate eTax API. This API will validate
1831 -- tax information for taxable and tax lines.
1832 --------------------------------------------------------------
1833 debug_info := '(Import_invoice 13) Validate tax info for '||
1834 'tax and taxable lines';
1835 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1836 AP_IMPORT_UTILITIES_PKG.Print(
1837 AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
1838 END IF;
1839
1840
1841 AP_IMPORT_UTILITIES_PKG.Print(
1842 AP_IMPORT_INVOICES_PKG.g_debug_switch, 'SOBID is :'||l_invoice_rec.set_of_books_id);
1843 -- assigning the following variable that was included in the
1844 -- header rec (denormalized)
1845 -- this variable will be used by Tax
1846 l_invoice_rec.set_of_books_id := l_set_of_books_id;
1847
1848 --------------------------------------------------------------
1849 -- Call validate eTax API. This API will validate tax info
1850 -- for taxable and tax lines.
1851 --------------------------------------------------------------
1852
1853 IF NOT (ap_etax_services_pkg.validate_default_import(
1854 p_invoice_rec => l_invoice_rec,
1855 p_invoice_lines_tab => l_invoice_lines_tab,
1856 p_calling_mode => 'VALIDATE IMPORT',
1857 p_all_error_messages => 'Y',
1858 p_invoice_status => l_invoice_status,
1859 p_error_code => l_error_code,
1860 p_calling_sequence => current_calling_sequence)) THEN
1861
1862 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
1863 AP_IMPORT_UTILITIES_PKG.Print(
1864 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1865 'ap_etax_services_pkg.validate_default_import<-'||current_calling_sequence);
1866
1867 END IF;
1868
1869 -- If the validation call fails the import process fails.
1870 -- The validate_default_import will populate the rejections table
1871 -- for the import if required.
1872 -- If the API fails because the call to the eTax service fails
1873 -- the following code will get the messages from the message
1874 -- stack
1875
1876 IF (l_error_code IS NOT NULL) THEN
1877 -- Print the error returned from the service even if the debug
1878 -- mode is off
1879 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
1880
1881 ELSE
1882 -- If the l_error_code is null is because the service returned
1883 -- more than one error. The calling module will need to get
1884 -- them from the message stack
1885 LOOP
1886 l_error_code := FND_MSG_PUB.Get;
1887
1888 IF l_error_code IS NULL THEN
1889 EXIT;
1890 ELSE
1891 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
1892 END IF;
1893 END LOOP;
1894
1895 END IF;
1896 Raise import_invoice_failure;
1897
1898 END IF;
1899
1900 END IF;
1901
1902 IF (l_invoice_status = 'Y') THEN
1903
1904 ------------------------------------
1905 -- Step 14
1906 -- Call Sequential Numbering Routine
1907 ------------------------------------
1908 debug_info := '(Import_invoice 14) Get Doc Sequence';
1909 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
1910 AP_IMPORT_UTILITIES_PKG.Print(
1911 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1912 END IF;
1913
1914 AP_IMPORT_UTILITIES_PKG.Print(
1915 AP_IMPORT_INVOICES_PKG.g_debug_switch, 'SOBID is :'||l_invoice_rec.set_of_books_id);
1916
1917 IF (AP_IMPORT_UTILITIES_PKG.get_doc_sequence (
1918 l_invoice_rec, -- IN OUT
1919 l_inv_doc_cat_override, -- IN
1920 l_set_of_books_id, -- IN
1921 l_sequence_numbering, -- IN
1922 l_default_last_updated_by, -- IN
1923 l_default_last_update_login, -- IN
1924 l_seqval, -- OUT NOCOPY
1925 l_dbseqnm, -- OUT NOCOPY
1926 l_dbseqid, -- OUT NOCOPY
1927 l_invoice_status, -- OUT NOCOPY
1928 current_calling_sequence)<> TRUE) THEN
1929
1930 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
1931 AP_IMPORT_UTILITIES_PKG.Print(
1932 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1933 'get_doc_sequence<-'||current_calling_sequence);
1934 END IF;
1935 Raise import_invoice_failure;
1936 END IF;
1937
1938 -- show output values (only IF debug_switch = 'Y')
1939
1940 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
1941 AP_IMPORT_UTILITIES_PKG.Print(
1942 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1943 '------------------> l_invoice_status = '|| l_invoice_status
1944 ||' l_seqval = '||to_char(l_seqval)
1945 ||' l_dbseqnm = '||l_dbseqnm
1946 ||' l_dbseqid = '||to_char(l_dbseqid));
1947 END IF;
1948 END IF; -- Invoice Status = 'Y'before get_doc_sequence
1949
1950 ---------------------------------------------------------------
1951 -- Step 15 Process invoice AND lines IF l_invoice_status is 'Y'
1952 -- or skip these steps
1953 ----------------------------------------------------------------
1954 IF (l_invoice_status = 'Y') THEN
1955
1956 -----------------------------------------------------
1957 -- Step 15.1a
1958 -- Get some required fields for creating invoices
1959 -- most of them are from po_vendor_sites
1960 -----------------------------------------------------
1961 debug_info := '(Import_invoice 15.1a) Call get_invoice_info';
1962 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
1963 AP_IMPORT_UTILITIES_PKG.Print(
1964 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1965 END IF;
1966
1967 IF (AP_IMPORT_UTILITIES_PKG.get_invoice_info(
1968 l_invoice_rec, -- IN OUT NOCOPY
1969 l_default_last_updated_by, -- IN
1970 l_default_last_update_login, -- IN
1971 l_pay_curr_invoice_amount, -- OUT NOCOPY
1972 l_payment_priority, -- OUT NOCOPY
1973 l_invoice_amount_limit, -- OUT NOCOPY
1974 l_hold_future_payments_flag, -- OUT NOCOPY
1975 l_supplier_hold_reason, -- OUT NOCOPY
1976 l_exclude_freight_from_disc, -- OUT NOCOPY /* bug 4931755 */
1977 current_calling_sequence ) <> TRUE) THEN
1978 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
1979 AP_IMPORT_UTILITIES_PKG.Print(
1980 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1981 'get_invoice_info<-'||current_calling_sequence);
1982 END IF;
1983 Raise import_invoice_failure;
1984
1985 END IF;
1986
1987 -- show output values (only IF debug_switch = 'Y')
1988
1989 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
1990 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1991 '------------------> l_pay_curr_invoice_amount = '
1992 ||to_char(l_pay_curr_invoice_amount)
1993 ||' l_payment_priority = ' ||to_char(l_payment_priority)
1994 ||' l_invoice_amount_limit = ' ||to_char(l_invoice_amount_limit)
1995 ||' l_hold_future_payments_flag = '||l_hold_future_payments_flag
1996 ||' l_supplier_hold_reason = ' ||l_supplier_hold_reason );
1997 END IF;
1998
1999 /* Bug 4014019: Commenting the call to jg_globe_flex_val due to build issues.
2000
2001 -----------------------------------------------------
2002 -- Step 15.1b
2003 -- Update global_context_code with the right
2004 -- value corresponding to flexfield JG_AP_INVOICES
2005 -----------------------------------------------------
2006 debug_info := '(Import_invoice 15.1b) Update global context code';
2007 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2008 AP_IMPORT_UTILITIES_PKG.Print(
2009 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2010 END IF;
2011
2012 -- > IN global context code in interface table
2013 -- > OUT NOCOPY global context code in base table
2014
2015
2016 IF ( jg_globe_flex_val.reassign_context_code(
2017 l_invoice_rec.global_attribute_category) <> TRUE) THEN
2018
2019 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2020 AP_IMPORT_UTILITIES_PKG.Print(
2021 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2022 'reassign_context_code<-'||current_calling_sequence);
2023 END IF;
2024 Raise import_invoice_failure;
2025 END IF;
2026 */
2027 ----------------------------------------------------------
2028 -- Step 15.2
2029 -- Insert record INTO ap_invoices
2030 ----------------------------------------------------------
2031 debug_info := '(Import_invoice 15.2) Insert record INTO ap_invoices';
2032 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2033 AP_IMPORT_UTILITIES_PKG.Print(
2034 AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2035 END IF;
2036
2037 -- Payment Request: Added p_needs_invoice_approval for payment requests
2038 IF (AP_IMPORT_UTILITIES_PKG.insert_ap_invoices(
2039 l_invoice_rec, -- IN OUT
2040 l_base_invoice_id, -- OUT NOCOPY
2041 l_set_of_books_id, -- IN
2042 l_dbseqid, -- IN
2043 l_seqval, -- IN
2044 l_batch_id, -- IN
2045 l_pay_curr_invoice_amount, -- IN
2046 l_approval_workflow_flag, -- IN
2047 p_needs_invoice_approval,
2048 l_add_days_settlement_date, -- IN --bug 4930111
2049 l_disc_is_inv_less_tax_flag, -- IN --bug 4931755
2050 l_exclude_freight_from_disc, -- IN --bug 4931755
2051 current_calling_sequence) <> TRUE) THEN
2052 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2053 AP_IMPORT_UTILITIES_PKG.Print(
2054 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2055 '<-'||current_calling_sequence);
2056 END IF;
2057
2058 Raise import_invoice_failure;
2059 END IF;
2060
2061 -- Set counter for created invoices
2062 l_valid_invoices_count := l_valid_invoices_count +1;
2063
2064 l_total_invoice_amount := l_total_invoice_amount +
2065 NVL(l_invoice_rec.no_xrate_base_amount,
2066 l_invoice_rec.invoice_amount);
2067 l_actual_invoice_total := l_actual_invoice_total +
2068 l_invoice_rec.invoice_amount;
2069
2070 g_invoice_id := l_base_invoice_id;
2071
2072 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2073 AP_IMPORT_UTILITIES_PKG.Print(
2074 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2075 '------------------> l_base_invoice_id = '
2076 || to_char(l_base_invoice_id)
2077 ||' l_valid_invoices_count = '||to_char(l_valid_invoices_count));
2078 END IF;
2079
2080 ---------------------------------------------------------------
2081 -- Step 15.3: Call AP_CREATE_PAY_SCHEDS_PKG.AP_Create_From_Terms
2082 -- Insert payment schedules FROM term
2083 ---------------------------------------------------------------
2084
2085 debug_info := '(Import_invoice 15.3) Insert payment schedules '||
2086 'from terms';
2087 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2088 AP_IMPORT_UTILITIES_PKG.Print(
2089 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2090 END IF;
2091
2092 AP_CREATE_PAY_SCHEDS_PKG.Create_payment_schedules(
2093 p_invoice_id =>l_base_invoice_id,
2094 p_terms_id =>l_invoice_rec.terms_id,
2095 p_last_updated_by =>l_invoice_rec.last_updated_by,
2096 p_created_by =>l_invoice_rec.created_by,
2097 p_payment_priority =>l_payment_priority,
2098 p_batch_id =>l_batch_id,
2099 p_terms_date =>l_invoice_rec.terms_date,
2100 p_invoice_amount =>l_invoice_rec.invoice_amount,
2101 p_pay_curr_invoice_amount =>l_pay_curr_invoice_amount,
2102 p_payment_cross_rate =>l_invoice_rec.payment_cross_rate,
2103 p_amount_for_discount =>
2104 l_invoice_rec.amount_applicable_to_discount,
2105 p_payment_method =>l_invoice_rec.payment_method_code,
2106 p_invoice_currency =>l_invoice_rec.invoice_currency_code,
2107 p_payment_currency =>l_invoice_rec.payment_currency_code,
2108 p_calling_sequence =>current_calling_sequence);
2109
2110
2111 -------------------------------------------------------------
2112 -- Step 15.4: Insert holds for this invoice.
2113 -- There are 2 holds FROM supplier site AND 1 hold FROM input
2114 -- parameter.
2115 -------------------------------------------------------------
2116 debug_info := '(Import_invoice 15.4) Insert holds for this invoice';
2117 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2118 AP_IMPORT_UTILITIES_PKG.Print(
2119 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2120 END IF;
2121
2122 IF (AP_IMPORT_UTILITIES_PKG.insert_holds(
2123 l_base_invoice_id,
2124 p_hold_code,
2125 p_hold_reason,
2126 l_hold_future_payments_flag,
2127 l_supplier_hold_reason,
2128 l_invoice_amount_limit,
2129 /*bug fix:3022381 Added the NVL condition*/
2130 nvl(l_invoice_rec.no_xrate_base_amount, -- Bug 4692091. Added ap_round_currency
2131 ap_utilities_pkg.ap_round_currency(
2132 l_invoice_rec.invoice_amount*nvl(l_invoice_rec.exchange_rate,1),
2133 l_invoice_rec.invoice_currency_code)),
2134 l_invoice_rec.last_updated_by,
2135 current_calling_sequence ) <> TRUE) THEN
2136
2137 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2138 AP_IMPORT_UTILITIES_PKG.Print(
2139 AP_IMPORT_INVOICES_PKG.g_debug_switch,'<-'||
2140 current_calling_sequence);
2141 END IF;
2142 Raise import_invoice_failure;
2143 END IF;
2144
2145 --------------------------------------------------------------
2146 -- Step 15.5:
2147 -- Create invoice lines
2148 --------------------------------------------------------------
2149 debug_info := '(Import_invoice 15.5) Create invoice lines';
2150 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2151 AP_IMPORT_UTILITIES_PKG.Print(
2152 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2153 END IF;
2154
2155
2156 AP_IMPORT_UTILITIES_PKG.Print(
2157 AP_IMPORT_INVOICES_PKG.g_debug_switch, 'SOBID is :'||l_invoice_rec.set_of_books_id);
2158
2159 IF (AP_IMPORT_UTILITIES_PKG.create_lines(
2160 l_batch_id,
2161 l_base_invoice_id,
2162 l_invoice_lines_tab,
2163 l_base_currency_code,
2164 l_set_of_books_id,
2165 l_approval_workflow_flag,
2166 l_invoice_rec.tax_only_flag,
2167 l_invoice_rec.tax_only_rcv_matched_flag,
2168 l_default_last_updated_by,
2169 l_default_last_update_login,
2170 current_calling_sequence) <> TRUE) THEN
2171
2172 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2173 AP_IMPORT_UTILITIES_PKG.Print(
2174 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2175 'create_lines<-'||current_calling_sequence);
2176 Raise import_invoice_failure;
2177 END IF;
2178 END IF;
2179
2180 --------------------------------------------------------------
2181 -- Step 15.6:
2182 -- Execute the Argentine/Colombian defaulting procedure
2183 --------------------------------------------------------------
2184 debug_info := '(Import_invoice 15.6) Execute the '||
2185 'Argentine/Colombian defaulting procedure';
2186 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2187 AP_IMPORT_UTILITIES_PKG.Print(
2188 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2189 END IF;
2190
2191 --Bugfix:4674229
2192 DECLARE
2193 l_awt_success Varchar2(1000);
2194 BEGIN
2195 AP_EXTENDED_WITHHOLDING_PKG.Ap_Ext_Withholding_Default(
2196 P_Invoice_Id => l_base_invoice_id,
2197 P_Inv_Line_Num => NULL,
2198 P_Inv_Dist_Id => NULL,
2199 P_calling_module => 'IMPORT',
2200 P_Parent_Dist_Id => NULL,
2201 P_Awt_Success => l_awt_success);
2202 IF (l_awt_success <> 'SUCCESS') THEN
2203 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2204 AP_IMPORT_UTILITIES_PKG.Print(
2205 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2206 'ap_ext_withholding_default<-'||current_calling_sequence);
2207 END IF;
2208 Raise import_invoice_failure;
2209 END IF;
2210 END;
2211
2212 --------------------------------------------------------------
2213 -- Step 15.7:
2214 -- If the user intention is to import TAX, eTax will be call to
2215 -- import the lines previous to any prepayment application.
2216 -- If the user intention is calculate and there is a prepayment
2217 -- application tax will be calculated during the prepayment
2218 -- application for the whole invoice. If there is no prepayment
2219 -- application, eTax will be called to calculate.
2220 --------------------------------------------------------------
2221
2222 debug_info := '(Import_invoice 15.7) Call import before any prepayment '||
2223 'application if the user intention is to import TAX';
2224
2225 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2226 AP_IMPORT_UTILITIES_PKG.Print(
2227 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2228 END IF;
2229
2230
2231 IF ( l_invoice_rec.calc_tax_during_import_flag = 'N') THEN
2232 --------------------------------------------------------------
2233 -- Step 15.7a. Call import document with tax.
2234 -- If it is a tax only invoice and has a receipt matched tax
2235 -- line, call calculate instead of import and call determine
2236 -- recovery right after because tax-only lines had been created
2237 --------------------------------------------------------------
2238 debug_info := '(Import_invoice 15.7a) User intention is to import TAX';
2239
2240 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2241 AP_IMPORT_UTILITIES_PKG.Print(
2242 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2243 END IF;
2244
2245 IF ( NVL(l_invoice_rec.tax_only_rcv_matched_flag, 'N') = 'Y') THEN
2246 -----------------------------------------------------------------
2247 -- Step 15.7b. Invoice is tax only and is matched to receipt
2248 -- call to calculate tax is required
2249 -----------------------------------------------------------------
2250
2251 debug_info := '(Import_invoice 15.7b) Invoice is tax only and is matched to receipt '||
2252 'so calculate shoould be called instead of import';
2253
2254 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2255 AP_IMPORT_UTILITIES_PKG.Print(
2256 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2257 END IF;
2258
2259 IF NOT (ap_etax_pkg.calling_etax(
2260 p_invoice_id => l_base_invoice_id,
2261 p_calling_mode => 'CALCULATE IMPORT',
2262 p_override_status => NULL,
2263 p_line_number_to_delete => NULL,
2264 P_Interface_Invoice_Id => l_invoice_rec.invoice_id,
2265 p_all_error_messages => 'Y',
2266 p_error_code => l_error_code,
2267 p_calling_sequence => current_calling_sequence)) THEN
2268
2269 -- If the call to calculate fails, the import process will
2270 -- fail. In this case the invoice cannot be imported since
2271 -- user is trying to import tax lines
2272 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2273 AP_IMPORT_UTILITIES_PKG.Print(
2274 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2275 'ap_etax_pkg.calling_etax(CALCULATE IMPORT)<-'||current_calling_sequence);
2276
2277 END IF;
2278
2279 -- If the API fails because the call to the eTax service fails
2280 -- the following code will get the messages from the message
2281 -- stack
2282
2283 IF (l_error_code IS NOT NULL) THEN
2284 -- Print the error returned from the service even if the debug
2285 -- mode is off
2286 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
2287
2288 ELSE
2289 -- If the l_error_code is null is because the service returned
2290 -- more than one error. The calling module will need to get
2291 -- them from the message stack
2292 LOOP
2293 l_error_code := FND_MSG_PUB.Get;
2294 IF l_error_code IS NULL THEN
2295 EXIT;
2296 ELSE
2297 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
2298 END IF;
2299 END LOOP;
2300 END IF; -- if l_error_code is not null
2301
2302 RAISE import_invoice_failure;
2303 END IF;
2304
2305 ELSE -- tax_only_rcv_matched_flag is N. We will call import tax service
2306 --------------------------------------------------------------
2307 -- Step 15.7c. For any other case call import document with tax.
2308 -- In this case could be necesary to populate a pseudo trx line
2309 -- in the global temp tables to pass to eTax the additional
2310 -- info in the tax line. This is handled in the population of
2311 -- the temp tables in the validation API since we are using the
2312 -- same information provided at that time.
2313 --------------------------------------------------------------
2314
2315 IF (NVL(l_invoice_rec.tax_only_flag, 'N') = 'Y') THEN -- tax_only_flag is Y.
2316
2317 debug_info := '(Import_invoice 15.7c) Invoice is tax only '||
2318 'but not matched to receipt so call IMPORT TAX';
2319
2320 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2321 AP_IMPORT_UTILITIES_PKG.Print(
2322 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2323 END IF;
2324
2325 IF NOT (ap_etax_pkg.calling_etax(
2326 p_invoice_id => l_base_invoice_id,
2327 p_calling_mode => 'IMPORT INTERFACE',
2328 p_override_status => NULL,
2329 p_line_number_to_delete => NULL,
2330 P_Interface_Invoice_Id => l_invoice_rec.invoice_id,
2331 p_all_error_messages => 'Y',
2332 p_error_code => l_error_code,
2333 p_calling_sequence => current_calling_sequence)) THEN
2334
2335 -- If the import of tax fails, the import process will fail.
2336 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2337 AP_IMPORT_UTILITIES_PKG.Print(
2338 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2339 'ap_etax_pkg.calling_etax(IMPORT INTERFACE)<-'||current_calling_sequence);
2340
2341 END IF;
2342
2343 -- If the API fails because the call to the eTax service fails
2344 -- the following code will get the messages from the message
2345 -- stack
2346
2347 IF (l_error_code IS NOT NULL) THEN
2348 -- Print the error returned from the service even if the debug
2349 -- mode is off
2350 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
2351
2352 ELSE
2353 -- If the l_error_code is null is because the service returned
2354 -- more than one error. The calling module will need to get
2355 -- them from the message stack
2356 LOOP
2357 l_error_code := FND_MSG_PUB.Get;
2358 IF l_error_code IS NULL THEN
2359 EXIT;
2360 ELSE
2361 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
2362 END IF;
2363 END LOOP;
2364 END IF; -- if l_error_code is not null
2365 RAISE import_invoice_failure;
2366 END IF; -- end of call to IMPORT INTERFACE
2367 END IF; -- End of tax_only_flag
2368 END IF; -- End of if for tax_only_rcv_matched_flag
2369
2370 --------------------------------------------------------------------
2371 -- Step 15.7d. Call determine_recovery if the invoice is tax-only.
2372 --------------------------------------------------------------------
2373
2374 IF (NVL(l_invoice_rec.tax_only_flag, 'N') = 'Y') THEN
2375 debug_info := '(Import_invoice 15.7d) Invoice is tax only so we will '||
2376 'call determine_recovery';
2377
2378 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2379 AP_IMPORT_UTILITIES_PKG.Print(
2380 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2381 END IF;
2382
2383 IF NOT (ap_etax_pkg.calling_etax(
2384 p_invoice_id => l_base_invoice_id,
2385 p_calling_mode => 'DISTRIBUTE IMPORT',
2386 p_override_status => NULL,
2387 p_line_number_to_delete => NULL,
2388 P_Interface_Invoice_Id => l_invoice_rec.invoice_id,
2389 p_all_error_messages => 'Y',
2390 p_error_code => l_error_code,
2391 p_calling_sequence => current_calling_sequence)) THEN
2392
2393 -- If the call to determine recovery fails, the import process
2394 -- will fail. In this case the invoice cannot be imported since
2395 -- user is trying to import tax lines
2396 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2397 AP_IMPORT_UTILITIES_PKG.Print(
2398 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2399 'ap_etax_pkg.calling_etax(DISTRIBUTE IMPORT)<-'||current_calling_sequence);
2400
2401 END IF;
2402
2403 -- If the API fails because the call to the eTax service fails
2404 -- the following code will get the messages from the message
2405 -- stack
2406
2407 IF (l_error_code IS NOT NULL) THEN
2408 -- Print the error returned from the service even if the debug
2409 -- mode is off
2410 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
2411
2412 ELSE
2413 -- If the l_error_code is null is because the service returned
2414 -- more than one error. The calling module will need to get
2415 -- them from the message stack
2416 LOOP
2417 l_error_code := FND_MSG_PUB.Get;
2418 IF l_error_code IS NULL THEN
2419 EXIT;
2420 ELSE
2421 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
2422 END IF;
2423 END LOOP;
2424 END IF; -- if l_error_code is not null
2425
2426 RAISE import_invoice_failure;
2427
2428 END IF;
2429 END IF; -- call distribute if tax-only invoice
2430
2431 END IF; -- calc_tax_during_import_flag is N. User expects import TAX
2432 -- to be called
2433
2434 --------------------------------------------------------------
2435 -- Step 15.8:
2436 -- If the invoice does not have prepayment applications, call
2437 -- calculate tax if the user intention was to calculate. The case
2438 -- where the user wants to import was handle previously.
2439 -- Also verify that the invoice is not AWT or INTEREST previous to
2440 -- calling etax to calculate.
2441 --------------------------------------------------------------
2442
2443 debug_info := '(Import_invoice 15.8) Call calculate or the prepayment '||
2444 'application';
2445
2446 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2447 AP_IMPORT_UTILITIES_PKG.Print(
2448 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2449 END IF;
2450
2451 IF ( l_invoice_rec.prepay_num IS NULL AND
2452 l_invoice_rec.prepay_line_num IS NULL AND
2453 l_invoice_rec.prepay_apply_amount IS NULL ) THEN
2454
2455 IF ( l_invoice_rec.invoice_type_lookup_code
2456 NOT IN ('AWT', 'INTEREST')) THEN
2457
2458 IF ( l_invoice_rec.calc_tax_during_import_flag = 'Y') THEN
2459 --------------------------------------------------------------
2460 -- Step 15.8a: calc_tax_during_import_flag = Y. User intention is
2461 -- calculate. To minimize the calls to the eTax service, we will exclude
2462 -- calling tax for invoices that will have any prepayment application.
2463 -- Tax calculation will be done during the prepayment application
2464 -- for those invoices.
2465 -- Call calculate tax.
2466 -- The big difference between this call here and the one done
2467 -- during the prepayment application is the source of the data.
2468 -- Here we will use the pl/sql tables populated during validation
2469 -- in the prepayment case, the API will select the lines from the
2470 -- ap_invoice_lines_all table
2471 --------------------------------------------------------------
2472 debug_info := '(Import_invoice 15.8a) Call calculate tax';
2473
2474 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2475 AP_IMPORT_UTILITIES_PKG.Print(
2476 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2477 END IF;
2478
2479 IF NOT (ap_etax_pkg.calling_etax(
2480 p_invoice_id => l_base_invoice_id,
2481 p_calling_mode => 'CALCULATE IMPORT',
2482 p_override_status => NULL,
2483 p_line_number_to_delete => NULL,
2484 P_Interface_Invoice_Id => l_invoice_rec.invoice_id,
2485 p_all_error_messages => 'Y',
2486 p_error_code => l_error_code,
2487 p_calling_sequence => current_calling_sequence)) THEN
2488
2489 -- If the calculation of tax fails the invoice will be imported
2490 -- anyway, and the error(s) will be included in the log file.
2491 -- Tax can be later be calculated from the invoice workbench or
2492 -- during the validation of the invoice.
2493
2494 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2495 AP_IMPORT_UTILITIES_PKG.Print(
2496 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2497 'ap_etax_pkg.calling_etax(CALCULATE IMPORT)<-'||current_calling_sequence);
2498
2499 END IF;
2500
2501 -- If the API fails because the call to the eTax service fails
2502 -- the following code will get the messages from the message
2503 -- stack
2504
2505 IF (l_error_code IS NOT NULL) THEN
2506 -- Print the error returned from the service even if the debug
2507 -- mode is off
2508 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
2509
2510 ELSE
2511 -- If the l_error_code is null is because the service returned
2512 -- more than one error. The calling module will need to get
2513 -- them from the message stack
2514 LOOP
2515 l_error_code := FND_MSG_PUB.Get;
2516
2517 IF l_error_code IS NULL THEN
2518 EXIT;
2519 ELSE
2520 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
2521 END IF;
2522 END LOOP;
2523 END IF; -- if l_error_code is not null
2524 END IF; -- end call to ap_etax_pkg.calling_etax
2525 END IF; -- if for the calc_tax_during_import_flag
2526 END IF; -- invoice is not AWT or INTEREST. There is no tax
2527 -- calculation for invoices of this type
2528
2529 ELSE -- if invoice has prepayment applications tax will be called in
2530 -- the prepay application package.
2531
2532 --------------------------------------------------------------
2533 -- Step 15.8b:
2534 -- Apply Prepayment(s) to invoice.
2535 -- Fix using invoice record
2536 --------------------------------------------------------------
2537 debug_info := '(Import_invoice 15.8b) Apply Prepayment(s) to invoice.';
2538
2539 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2540 AP_IMPORT_UTILITIES_PKG.Print(
2541 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2542 END IF;
2543
2544
2545 SELECT sum(nvl(amount_remaining,0))
2546 INTO l_inv_amount_unpaid
2547 FROM ap_payment_schedules
2548 WHERE invoice_id = l_base_invoice_id;
2549
2550 IF (nvl(l_inv_amount_unpaid,0) < l_invoice_rec.prepay_apply_amount) THEN
2551 l_amount_to_apply := l_inv_amount_unpaid;
2552 ELSE
2553 l_amount_to_apply := l_invoice_rec.prepay_apply_amount;
2554 END IF;
2555
2556 -- Prepayments project - 11ix
2557 AP_PREPAY_PKG.APPLY_PREPAY_IMPORT(
2558 p_prepay_invoice_id => l_prepay_invoice_id,
2559 p_prepay_num => l_invoice_rec.prepay_num,
2560 p_prepay_line_num => l_invoice_rec.prepay_line_num,
2561 p_prepay_apply_amount => l_amount_to_apply,
2562 p_prepay_case_name => l_prepay_case_name,
2563 p_import_invoice_id => l_invoice_rec.invoice_id,
2564 p_request_id => p_conc_request_id,
2565 p_invoice_id => l_base_invoice_id,
2566 p_vendor_id => l_invoice_rec.vendor_id,
2567 p_prepay_gl_date => l_invoice_rec.prepay_gl_date,
2568 p_prepay_period_name => l_prepay_period_name,
2569 p_prepay_included => l_invoice_rec.invoice_includes_prepay_flag,
2570 p_user_id => l_default_last_updated_by,
2571 p_last_update_login => l_default_last_update_login,
2572 p_calling_sequence => current_calling_sequence,
2573 p_prepay_appl_log => l_prepay_appl_log);
2574
2575 END IF;
2576
2577 --------------------------------------------------------------
2578 -- Step 15.9:
2579 -- Update the invoice amount if flag add_tax_to_inv_amt_flag is
2580 -- set
2581 --------------------------------------------------------------
2582 debug_info := '(Import_invoice 15.9) Update the invoice amount '||
2583 'if flag add_tax_to_inv_amt_flag is set with the '||
2584 'total of the exclusive tax lines created for the '||
2585 'invoice';
2586
2587 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2588 AP_IMPORT_UTILITIES_PKG.Print(
2589 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2590 END IF;
2591
2592 IF (NVL(l_invoice_rec.add_tax_to_inv_amt_flag, 'N') = 'Y') THEN
2593
2594 SELECT ai.invoice_amount,
2595 (SELECT NVL(SUM(NVL(ail.amount, 0)), 0)
2596 FROM ap_invoice_lines_all ail
2597 WHERE ail.invoice_id = l_base_invoice_id
2598 AND ail.line_type_lookup_code = 'TAX')
2599 INTO l_inv_hdr_amount, l_exclusive_tax_amount
2600 FROM ap_invoices_all ai
2601 WHERE ai.invoice_id = l_base_invoice_id;
2602
2603 l_payment_status_flag := AP_INVOICES_UTILITY_PKG.get_payment_status (l_base_invoice_id);
2604
2605 AP_PAYMENT_SCHEDULES_PKG.adjust_pay_schedule(
2606 X_invoice_id => l_base_invoice_id,
2607 X_invoice_amount => l_inv_hdr_amount + l_exclusive_tax_amount ,
2608 X_payment_status_flag => l_payment_status_flag,
2609 X_invoice_type_lookup_code => l_invoice_rec.invoice_type_lookup_code,
2610 X_last_updated_by => l_default_last_updated_by,
2611 X_message1 => l_message1,
2612 X_message2 => l_message2,
2613 X_reset_match_status => l_reset_match_status,
2614 X_liability_adjusted_flag => l_liability_adjusted_flag,
2615 X_calling_sequence => 'APXIIMPT',
2616 X_calling_mode => 'APXIIMPT',
2617 X_revalidate_ps => l_revalidate_ps);
2618
2619 UPDATE ap_invoices_all ai
2620 SET ai.invoice_amount = ai.invoice_amount + l_exclusive_tax_amount,
2621 ai.amount_applicable_to_discount = ai.amount_applicable_to_discount + l_exclusive_tax_amount
2622 WHERE ai.invoice_id = l_base_invoice_id;
2623
2624 IF ( l_invoice_rec.payment_cross_rate is NOT NULL) THEN
2625
2626 UPDATE ap_invoices_all ai
2627 SET ai.pay_curr_invoice_amount = ai.pay_curr_invoice_amount +
2628 gl_currency_api.convert_amount(
2629 ai.invoice_currency_code,
2630 ai.payment_currency_code,
2631 ai.payment_cross_rate_date,
2632 ai.payment_cross_rate_type,
2633 l_exclusive_tax_amount)
2634 WHERE ai.invoice_id = l_base_invoice_id;
2635
2636 END IF;
2637
2638
2639 END IF;
2640
2641 --------------------------------------------------------------
2642 -- Step 15.10:
2643 -- Delete the contents of the l_invoice_lines_tab Lines Table
2644 --------------------------------------------------------------
2645 debug_info := '(Import_invoice 15.10) Delete the contents of '||
2646 'the l_invoice_lines_tab Lines Table';
2647
2648 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2649 AP_IMPORT_UTILITIES_PKG.Print(
2650 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2651 END IF;
2652
2653 l_invoice_lines_tab.DELETE;
2654
2655 --------------------------------------------------------------
2656 -- Step 15.11:
2657 -- Delete the contents of the eTax global temporary tables
2658 --------------------------------------------------------------
2659 debug_info := '(Import_invoice 15.11) Delete the contents of '||
2660 'the eTax global temp tables';
2661
2662 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2663 AP_IMPORT_UTILITIES_PKG.Print(
2664 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2665 END IF;
2666
2667 BEGIN DELETE zx_trx_headers_gt;
2668 EXCEPTION WHEN NO_DATA_FOUND THEN null;
2669 END;
2670
2671 BEGIN DELETE zx_transaction_lines_gt;
2672 EXCEPTION WHEN NO_DATA_FOUND THEN null;
2673 END;
2674
2675 BEGIN DELETE zx_import_tax_lines_gt;
2676 EXCEPTION WHEN NO_DATA_FOUND THEN null;
2677 END;
2678
2679 BEGIN DELETE zx_trx_tax_link_gt;
2680 EXCEPTION WHEN NO_DATA_FOUND THEN null;
2681 END;
2682
2683 END IF; -- Invoice Status = 'Y' before call to get_invoice_info
2684
2685 ----------------------------------------------------------------
2686 -- Step 16. Retropricing.
2687 ----------------------------------------------------------------
2688 ELSE
2689 debug_info := '(Import Invoice 16) Import_Retroprice_Adjustments';
2690 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2691 AP_IMPORT_UTILITIES_PKG.Print(
2692 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2693 END IF;
2694 --
2695 IF (AP_RETRO_PRICING_PKG.Import_Retroprice_Adjustments(
2696 l_invoice_rec, -- IN p_instr_header_rec
2697 l_base_currency_code, -- IN
2698 l_multi_currency_flag, -- IN
2699 l_set_of_books_id, -- IN
2700 l_default_exchange_rate_type, -- IN
2701 l_make_rate_mandatory_flag, -- IN
2702 l_invoice_rec.gl_date, -- IN
2703 l_gl_date_from_receipt_flag, -- IN
2704 l_positive_price_tolerance, -- IN
2705 l_pa_installed, -- IN
2706 l_qty_tolerance, -- IN
2707 l_max_qty_ord_tolerance, -- IN
2708 l_base_min_acct_unit, -- IN
2709 l_base_precision, -- IN
2710 l_chart_of_accounts_id, -- IN
2711 l_freight_code_combination_id, -- IN
2712 l_purch_encumbrance_flag, -- IN
2713 l_calc_user_xrate, -- IN
2714 l_default_last_updated_by, -- IN
2715 l_default_last_update_login, -- IN
2716 l_invoice_status, -- OUT instr_status_flag
2717 l_valid_invoices_count, -- OUT p_invoices_count
2718 l_total_invoice_amount, -- OUT p_invoices_total
2719 current_calling_sequence) <> TRUE) THEN
2720 --
2721 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2722 AP_IMPORT_UTILITIES_PKG.Print(
2723 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2724 'Import_Retroprice_Adjustments<-'||current_calling_sequence);
2725 END IF;
2726 Raise import_invoice_failure;
2727 --
2728 END IF;
2729 --
2730 -- NOTE : The logic based on l_actual_invoice_total doesn't make sense.
2731 -- l_total_invoices_amount -- is the out parameter for import_invoices
2732 -- l_actual_invoice_total -- is used in
2733 -- AP_IMPORT_UTILITIES_PKG.Insert_ap_batches and
2734 -- AP_IMPORT_UTILITIES_PKG.Update_ap_batches.
2735 -- Ideally they shud be the same.
2736 l_actual_invoice_total := l_total_invoice_amount;
2737 --
2738 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2739 AP_IMPORT_UTILITIES_PKG.Print(
2740 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2741 '------------------> l_instruction_id = '
2742 || to_char(l_invoice_rec.invoice_id));
2743 END IF;
2744 --
2745 END IF; --Retropricing
2746 --veramach bug 7121842 start
2747 EXCEPTION
2748 WHEN import_invoice_failure THEN
2749 l_invoice_status := 'N';
2750 END;
2751 --veramach bug 7121842 end
2752 -------------------------------------------------
2753 -- Step 17
2754 -- Change temporary status in ap_invoice_interface
2755 -------------------------------------------------
2756 BEGIN--veramach bug 7121842
2757 IF (l_invoice_status = 'N') THEN
2758 -----------------------------------------------------
2759 -- Step 17.1. Change the invoice status to 'REJECTING'
2760 -----------------------------------------------------
2761 debug_info := '(Import_invoice 17.1) Change the invoice status to '||
2762 'REJECTING';
2763 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2764 AP_IMPORT_UTILITIES_PKG.Print(
2765 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2766 END IF;
2767
2768 IF (AP_IMPORT_UTILITIES_PKG.change_invoice_status(
2769 'REJECTING',
2770 l_invoice_rec.invoice_id,
2771 current_calling_sequence) <> TRUE) THEN
2772 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2773 AP_IMPORT_UTILITIES_PKG.Print(
2774 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2775 'change_invoice_status<-'||current_calling_sequence);
2776 END IF;
2777 Raise import_invoice_failure;
2778 END IF;
2779 ELSE
2780 ------------------------------------------------------
2781 -- Step 17.2 Change the invoice status to 'PROCESSING'
2782 ------------------------------------------------------
2783 debug_info := '(Import_invoice 17.2) Change the invoice status to '||
2784 'PROCESSIPRNG';
2785 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2786 AP_IMPORT_UTILITIES_PKG.Print(
2787 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2788 END IF;
2789
2790 IF (AP_IMPORT_UTILITIES_PKG.change_invoice_status(
2791 'PROCESSING',
2792 l_invoice_rec.invoice_id,
2793 current_calling_sequence) <> TRUE) THEN
2794 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2795 AP_IMPORT_UTILITIES_PKG.Print(
2796 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2797 'change_invoice_status<-'||current_calling_sequence);
2798 END IF;
2799 Raise import_invoice_failure;
2800 END IF;
2801 END IF;
2802 --veramach bug 7121842 start
2803 EXCEPTION
2804 WHEN import_invoice_failure THEN
2805 NULL;
2806 END;
2807 --veramach bug 7121842 end
2808 l_old_org_id := nvl(l_invoice_rec.org_id, nvl(p_org_id, NULL));
2809
2810 END LOOP; -- invoice LOOP
2811
2812 debug_info := '(Import_invoice) CLOSE import_invoices';
2813 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2814 AP_IMPORT_UTILITIES_PKG.Print(
2815 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2816 END IF;
2817
2818 -- Bug 4145391
2819 IF (p_group_id IS NULL) THEN
2820 CLOSE import_invoices;
2821 ELSE
2822 CLOSE import_invoices_group;
2823 END IF;
2824
2825 ---------------------------------------------------------------------
2826 -- Step18
2827 -- Create batch IF batch_control is on AND has invoices created.
2828 -- Create batch only the first time, in subsequent commit cycles
2829 -- do not try to create the batch again.
2830 ---------------------------------------------------------------------
2831 debug_info := '(Import_invoice 18a) Get/Initialize batch name';
2832 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2833 AP_IMPORT_UTILITIES_PKG.Print(
2834 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2835 END IF;
2836 BEGIN--veramach bug 7121842
2837 IF (p_batch_name IS NULL AND
2838 l_batch_name IS NULL AND
2839 NVL(l_batch_control_flag, 'N') = 'Y' AND
2840 l_valid_invoices_count > 0) THEN
2841 IF ( AP_IMPORT_UTILITIES_PKG.get_auto_batch_name(
2842 p_source,
2843 l_batch_name,
2844 current_calling_sequence) <> TRUE ) THEN
2845 Raise import_invoice_failure;
2846 END IF;
2847
2848 ELSIF (p_batch_name IS NOT NULL AND
2849 l_batch_name IS NULL AND
2850 NVL(l_batch_control_flag, 'N') = 'Y' AND
2851 l_valid_invoices_count > 0) THEN
2852 l_batch_name := p_batch_name;
2853 END IF;
2854
2855 IF (NVL(l_batch_control_flag,'N') = 'Y' AND
2856 (l_batch_id is NOT NULL) AND
2857 (l_batch_name IS NOT NULL) AND
2858 (l_batch_exists_flag = 'N') AND
2859 (l_valid_invoices_count >0 ) AND
2860 (l_batch_type = 'NEW BATCH')) THEN
2861
2862 debug_info := '(Import_invoice 18b) Create ap_batches';
2863 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2864 AP_IMPORT_UTILITIES_PKG.Print(
2865 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2866 END IF;
2867
2868 IF (AP_IMPORT_UTILITIES_PKG.Insert_ap_batches(
2869 l_batch_id,
2870 l_batch_name,
2871 l_invoice_rec.invoice_currency_code,
2872 l_invoice_rec.payment_currency_code,
2873 l_valid_invoices_count,-- bug1721820
2874 l_actual_invoice_total,-- bug1721820
2875 l_default_last_updated_by,
2876 current_calling_sequence) <> TRUE) THEN
2877 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2878 AP_IMPORT_UTILITIES_PKG.Print(
2879 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2880 'Insert_ap_batches<-'||current_calling_sequence);
2881 END IF;
2882 Raise import_invoice_failure;
2883 END IF;
2884
2885 debug_info := '(Import_invoice 18c) Set batch exists flag to Y';
2886 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2887 AP_IMPORT_UTILITIES_PKG.Print(
2888 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2889 END IF;
2890
2891 l_batch_exists_flag := 'Y';
2892
2893 ELSIF (NVL(l_batch_control_flag,'N') = 'Y' AND
2894 (l_batch_id is NOT NULL) AND
2895 (l_batch_name is NOT NULL) AND
2896 ((l_batch_exists_flag = 'Y') OR
2897 (l_batch_type = 'OLD BATCH')) AND
2898 (l_valid_invoices_count >0 )) THEN
2899
2900 debug_info := '(Import_invoice 18d) Create ap_batches';
2901 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2902 AP_IMPORT_UTILITIES_PKG.Print(
2903 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2904 END IF;
2905
2906 IF (AP_IMPORT_UTILITIES_PKG.Update_Ap_Batches(
2907 l_batch_id,
2908 p_batch_name,
2909 l_valid_invoices_count,
2910 l_actual_invoice_total,
2911 l_default_last_updated_by,
2912 current_calling_sequence) <> TRUE) THEN
2913 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2914 AP_IMPORT_UTILITIES_PKG.Print(
2915 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2916 'Update_Ap_Batches<-'||current_calling_sequence);
2917 END IF;
2918 Raise import_invoice_failure;
2919 END IF;
2920
2921 debug_info := '(Import_invoice 18e) Set batch exists flag to Y';
2922 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2923 AP_IMPORT_UTILITIES_PKG.Print(
2924 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2925 END IF;
2926
2927 l_batch_exists_flag := 'Y';
2928 ELSE
2929 debug_info := '(Import_invoice 18f) Do Not create batch';
2930 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2931 AP_IMPORT_UTILITIES_PKG.Print(
2932 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2933 END IF;
2934 END IF;
2935
2936 -----------------------------------------------------------
2937 -- Step 19
2938 -- For each commit cycle, do a commit.
2939 -----------------------------------------------------------
2940 debug_info := '(Import_invoice 19) COMMIT to the database';
2941 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2942 AP_IMPORT_UTILITIES_PKG.Print(
2943 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2944 END IF;
2945
2946 IF NVL(p_commit,'Y') = 'Y' THEN
2947 COMMIT;
2948 END IF;
2949
2950 -----------------------------------------------------
2951 -- Step 20 Check IF there's still any record left
2952 --
2953 -----------------------------------------------------
2954 debug_info := '(Import_purge 20) Check IF there is still any record left';
2955 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2956 AP_IMPORT_UTILITIES_PKG.Print(
2957 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2958 END IF;
2959
2960 IF l_total_count > l_invoices_fetched THEN
2961 l_continue_flag := 'Y';
2962 ELSE
2963 l_continue_flag := 'N';
2964 END IF;
2965
2966 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2967 AP_IMPORT_UTILITIES_PKG.Print(
2968 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2969 '------------------> l_continue_flag = '|| l_continue_flag);
2970 END IF;
2971 --veramach bug 7121842 start
2972 EXCEPTION
2973 WHEN import_invoice_failure THEN
2974 NULL;
2975 END;
2976 --veramach bug 7121842 end
2977 END LOOP; -- invoice group LOOP
2978
2979 ----------------------------------------------------------------------
2980 -- Step 21
2981 -- Update temporary status in ap_invoices_interface for all invoices
2982 -- FROM 'PROCESSING' to 'PROCESSED' AND,
2983 -- 'REJECTING' to 'REJECTED'
2984 ----------------------------------------------------------------------
2985
2986 debug_info := '(Import_invoice 21) Update temporary status';
2987 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2988 AP_IMPORT_UTILITIES_PKG.Print(
2989 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2990 END IF;
2991 BEGIN--veramach bug 7121842
2992 IF (AP_IMPORT_UTILITIES_PKG.Update_temp_invoice_status(
2993 p_source,
2994 p_group_id,
2995 current_calling_sequence) <> TRUE) THEN
2996 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2997 AP_IMPORT_UTILITIES_PKG.Print(
2998 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2999 'Update_temp_invoice_statu<-'||current_calling_sequence);
3000 END IF;
3001 Raise import_invoice_failure;
3002 END IF;
3003 --veramach bug 7121842 start
3004 EXCEPTION
3005 WHEN import_invoice_failure THEN
3006 NULL;
3007 END;
3008 --veramach bug 7121842 end
3009 p_invoices_created := nvl(l_valid_invoices_count,0);
3010 p_invoices_fetched := l_invoices_fetched;
3011 p_total_invoice_amount := l_total_invoice_amount; -- for bug 989221
3012
3013 debug_info := '(Import_invoice 22) Return No of invoices fetched '||
3014 'during process ,p_invoices_fetched'||l_invoices_fetched;
3015 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3016 AP_IMPORT_UTILITIES_PKG.Print(
3017 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3018 END IF;
3019
3020
3021 debug_info := 'Now Block to Raise the Business event to pass the Concurrent request_id'
3022 ||AP_IMPORT_INVOICES_PKG.g_conc_request_id;
3023 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3024 AP_IMPORT_UTILITIES_PKG.Print(
3025 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3026 END IF;
3027
3028 --7567527 PL/SQL Block for Enhancement to raise Business events after invoices are imported
3029 BEGIN
3030
3031
3032 l_parameter_list := wf_parameter_list_t( wf_parameter_t('REQUEST_ID',
3033 to_char(AP_IMPORT_INVOICES_PKG.g_conc_request_id)
3034 )
3035 );
3036
3037
3038 --bug 7636400
3039 /*
3040 SELECT to_char(ap_invoice_import_wfevent_s.nextval)
3041 INTO l_event_key
3042 FROM dual;
3043 */
3044
3045 SELECT to_char(AP_INV_IMPORT_EVENT_S.nextval)
3046 INTO l_event_key
3047 FROM dual;
3048
3049 wf_event.raise( p_event_name => l_event_name,
3050 p_event_key => l_event_key,
3051 p_parameters => l_parameter_list);
3052
3053 debug_info := 'After raising workflow event : '
3054 || 'event_name = ' || l_event_name
3055 || ' event_key = ' || l_event_key ;
3056 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3057 AP_IMPORT_UTILITIES_PKG.Print(
3058 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3059 END IF;
3060
3061 EXCEPTION
3062
3063
3064 WHEN OTHERS THEN
3065 debug_info := 'Error Was Raised in raising event';
3066 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3067 AP_IMPORT_UTILITIES_PKG.Print(
3068 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3069 END IF;
3070 WF_CORE.CONTEXT('AP_IMPORT_INVOICES_PKG', 'IMPORT_INVOICES', l_event_name,
3071 l_event_key);
3072 RAISE;
3073 END;
3074
3075 RETURN (TRUE);
3076 EXCEPTION
3077 WHEN OTHERS THEN
3078 IF (SQLCODE < 0) THEN
3079 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3080 AP_IMPORT_UTILITIES_PKG.Print(
3081 AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
3082 END IF;
3083 END IF;
3084
3085 IF (SQLCODE = -54) THEN
3086 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3087 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
3088 '(Import_invoice:EXCEPTION) The invoices to be SELECTed by this ' ||
3089 'process are locked');
3090 END IF;
3091 END IF;
3092
3093 IF import_invoices%isOPEN THEN
3094 CLOSE import_invoices;
3095 ELSIF import_invoices_group%ISOPEN THEN
3096 CLOSE import_invoices_group;
3097 END IF;
3098
3099 RETURN (FALSE);
3100
3101 END Import_INVOICES;
3102
3103
3104 --===============================================================
3105 -- Main functions: Import_purge
3106 --
3107 --===============================================================
3108 FUNCTION IMPORT_PURGE(
3109 p_source IN VARCHAR2,
3110 p_group_id IN VARCHAR2,
3111 p_org_id IN NUMBER,
3112 p_commit_cycles IN NUMBER,
3113 p_calling_sequence IN VARCHAR2)
3114 RETURN BOOLEAN IS
3115
3116 -- Bug 4145391. Modified the select for the cursor to improve performance.
3117 -- Removed the p_group_id where clause and added it to the cursor
3118 -- purge_invoices_group
3119 CURSOR purge_invoices IS
3120 SELECT invoice_id
3121 FROM ap_invoices_interface
3122 WHERE source = p_source
3123 AND status = 'PROCESSED'
3124 AND ((p_commit_cycles IS NULL) OR
3125 (ROWNUM <= p_commit_cycles))
3126 AND ((org_id IS NOT NULL and p_org_id IS NOT NULL and
3127 org_id = p_org_id)
3128 OR (p_org_id IS NULL and org_id is NOT NULL and
3129 (mo_global.check_access(org_id)= 'Y'))
3130 OR (p_org_id IS NOT NULL and org_id IS NULL)
3131 OR (p_org_id IS NULL and org_id IS NULL))
3132 ORDER BY vendor_id,
3133 vendor_num,
3134 vendor_name,
3135 vendor_site_id,
3136 vendor_site_code,
3137 invoice_num;
3138
3139 CURSOR purge_invoices_group IS
3140 SELECT invoice_id
3141 FROM ap_invoices_interface
3142 WHERE source = p_source
3143 AND group_id = p_group_id
3144 AND status = 'PROCESSED'
3145 AND ((p_commit_cycles IS NULL) OR
3146 (ROWNUM <= p_commit_cycles))
3147 AND ((org_id IS NOT NULL and p_org_id IS NOT NULL and
3148 org_id = p_org_id)
3149 OR (p_org_id IS NULL and org_id is NOT NULL and
3150 (mo_global.check_access(org_id)= 'Y'))
3151 OR (p_org_id IS NOT NULL and org_id IS NULL)
3152 OR (p_org_id IS NULL and org_id IS NULL))
3153 ORDER BY vendor_id,
3154 vendor_num,
3155 vendor_name,
3156 vendor_site_id,
3157 vendor_site_code,
3158 invoice_num;
3159
3160 l_continue_flag VARCHAR2(1) := 'Y';
3161 l_invoice_id NUMBER;
3162 import_purge_failure EXCEPTION;
3163 current_calling_sequence VARCHAR2(2000);
3164 debug_info VARCHAR2(500); /* Bug 4166583 */
3165 l_total_count NUMBER := 0;
3166 l_counter NUMBER := 0;
3167 l_attachments_count NUMBER;
3168
3169
3170 BEGIN
3171 -- Update the calling sequence
3172
3173 current_calling_sequence := 'Import_purge<- '||p_calling_sequence;
3174
3175 debug_info := '(Import_purge ) Deleting records in interface tables...';
3176
3177 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3178 AP_IMPORT_UTILITIES_PKG.Print(
3179 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3180 END IF;
3181
3182 -- Outside while loop for commit cycle
3183
3184 IF p_group_id IS NULL THEN
3185 BEGIN
3186 SELECT count(*)
3187 INTO l_total_count
3188 FROM ap_invoices_interface
3189 WHERE source = p_source
3190 AND status = 'PROCESSED'
3191 AND ( (org_id IS NOT NULL AND
3192 p_org_id IS NOT NULL AND
3193 org_id = p_org_id)
3194 OR (p_org_id IS NULL AND
3195 org_id is NOT NULL and
3196 (mo_global.check_access(org_id)= 'Y'))
3197 OR (p_org_id IS NOT NULL and org_id IS NULL)
3198 OR (p_org_id IS NULL and org_id IS NULL));
3199 EXCEPTION
3200 WHEN NO_DATA_FOUND THEN
3201 l_continue_flag := 'N';
3202 END;
3203 ELSE
3204 BEGIN
3205 SELECT count(*)
3206 INTO l_total_count
3207 FROM ap_invoices_interface
3208 WHERE source = p_source
3209 AND group_id = p_group_id
3210 AND status = 'PROCESSED'
3211 AND ( (org_id IS NOT NULL AND
3212 p_org_id IS NOT NULL AND
3213 org_id = p_org_id)
3214 OR (p_org_id IS NULL AND
3215 org_id is NOT NULL and
3216 (mo_global.check_access(org_id)= 'Y'))
3217 OR (p_org_id IS NOT NULL and org_id IS NULL)
3218 OR (p_org_id IS NULL and org_id IS NULL));
3219 EXCEPTION
3220 WHEN NO_DATA_FOUND THEN
3221 l_continue_flag := 'N';
3222 END;
3223 END IF;
3224
3225 WHILE (l_continue_flag = 'Y') LOOP
3226
3227 ---------------------------------------------------------------
3228 -- Step 1, Open cursor
3229 ---------------------------------------------------------------
3230
3231 debug_info := '(Import_purge 1) Open purge_invoices cursor';
3232 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3233 AP_IMPORT_UTILITIES_PKG.Print(
3234 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3235 END IF;
3236
3237 -- Bug 4145391. To improve the performance of the import program coding two
3238 -- different cursors based on the parameter p_group_id
3239 IF (p_group_id IS NULL) THEN
3240 OPEN purge_invoices;
3241 ELSE
3242 OPEN purge_invoices_group;
3243 END IF;
3244
3245
3246 LOOP
3247 -- Invoice loop
3248
3249 ---------------------------------------------------------------
3250 -- Step 2, Fetch invoice interface record into local variables
3251 --
3252 ----------------------------------------------------------------
3253 debug_info := '(Import_puege 2) Fetch purge_invoices';
3254 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3255 AP_IMPORT_UTILITIES_PKG.Print(
3256 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3257 END IF;
3258
3259 -- Bug 4145391
3260 IF (p_group_id IS NULL) THEN
3261 FETCH purge_invoices INTO l_invoice_id;
3262 EXIT WHEN purge_invoices%NOTFOUND OR
3263 purge_invoices%NOTFOUND IS NULL;
3264 ELSE
3265 FETCH purge_invoices_group INTO l_invoice_id;
3266 EXIT WHEN purge_invoices_group%NOTFOUND OR
3267 purge_invoices_group%NOTFOUND IS NULL;
3268 END IF;
3269
3270 --
3271 -- show output values (only if debug_switch = 'Y')
3272 --
3273 debug_info := '---------> l_invoice_id = '|| to_char(l_invoice_id);
3274 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3275 AP_IMPORT_UTILITIES_PKG.Print(
3276 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3277 END IF;
3278
3279 ------------------------------------------------------------------------
3280 -- Step 3, Delete records for ap_invoice_lines_interface
3281 -- Multiple lines
3282 ------------------------------------------------------------------------
3283 debug_info := '(Import_purge 3) Delete records in ' ||
3284 'ap_invoice_lines_interface...';
3285 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3286 AP_IMPORT_UTILITIES_PKG.Print(
3287 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3288 END IF;
3289
3290 DELETE FROM AP_INVOICE_LINES_INTERFACE
3291 WHERE invoice_id = l_invoice_id;
3292
3293 ------------------------------------------------------------------------
3294 -- Step 4, Delete records for ap_invoices_interface
3295 -- Only one line
3296 ------------------------------------------------------------------------
3297 -- also delete attachments if any
3298 debug_info := '(Import_purge 4.1) Delete attachments if any...';
3299 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3300 AP_IMPORT_UTILITIES_PKG.Print(
3301 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3302 END IF;
3303
3304 -- delete attachments for the invoice
3305 debug_info := '(Import_purge 4.2) before delete attachments: '||
3306 'source = ' || p_source || ', invoice_id = ' || l_invoice_id;
3307 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3308 AP_IMPORT_UTILITIES_PKG.Print(
3309 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3310 END IF;
3311
3312 -- not necessary to restrict to souce
3313 l_attachments_count := delete_attachments(l_invoice_id);
3314 debug_info := '(Import_purge 4.2) delete attachments done: '||
3315 l_attachments_count;
3316 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3317 AP_IMPORT_UTILITIES_PKG.Print(
3318 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3319 END IF;
3320
3321 -- delete the invoice_interface record now
3322 debug_info := '(Import_purge 4) Delete records in ' ||
3323 'ap_invoices_interface...';
3324 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3325 AP_IMPORT_UTILITIES_PKG.Print(
3326 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3327 END IF;
3328
3329 DELETE FROM AP_INVOICES_INTERFACE
3330 WHERE invoice_id = l_invoice_id
3331 AND ( (org_id IS NOT NULL AND
3332 p_org_id IS NOT NULL AND
3333 org_id = p_org_id)
3334 OR (p_org_id IS NULL AND
3335 org_id is NOT NULL AND
3336 (mo_global.check_access(org_id)= 'Y'))
3337 OR (p_org_id IS NOT NULL AND
3338 org_id IS NULL)
3339 OR (p_org_id IS NULL AND
3340 org_id IS NULL));
3341
3342 l_counter := l_counter + 1;
3343
3344 END LOOP; -- invoice loop
3345
3346 debug_info := '(Import_purge ) Close purge_invoices cursor';
3347 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3348 AP_IMPORT_UTILITIES_PKG.Print(
3349 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3350 END IF;
3351
3352 -- Bug 4145391
3353 IF (p_group_id IS NULL) THEN
3354 CLOSE purge_invoices;
3355 ELSE
3356 CLOSE purge_invoices_group;
3357 END IF;
3358
3359 -----------------------------------------------------
3360 -- Step 5, COMMIT for each commit cycle
3361 -----------------------------------------------------
3362 debug_info := '(Import_purge 5) Commit to the database';
3363 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3364 AP_IMPORT_UTILITIES_PKG.Print(
3365 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3366 END IF;
3367 COMMIT;
3368
3369 -----------------------------------------------------
3370 -- Step 6, Check if there's still any record left
3371 -----------------------------------------------------
3372 debug_info := '(Import_purge 6) Check if there is still any record left';
3373 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3374 AP_IMPORT_UTILITIES_PKG.Print(
3375 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3376 END IF;
3377
3378 If l_total_count > l_counter THEN
3379 l_continue_flag := 'Y';
3380 Else
3381 l_continue_flag := 'N';
3382 End If;
3383
3384 debug_info := '---------> l_continue_flag = '|| l_continue_flag;
3385 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3386 AP_IMPORT_UTILITIES_PKG.Print(
3387 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3388 END IF;
3389 END LOOP; -- Outside commit cycle loop
3390 RETURN (TRUE);
3391
3392 EXCEPTION
3393 WHEN OTHERS THEN
3394
3395 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3396 AP_IMPORT_UTILITIES_PKG.Print(
3397 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3398 END IF;
3399
3400 IF (SQLCODE < 0) then
3401 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3402 AP_IMPORT_UTILITIES_PKG.Print(
3403 AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
3404 END IF;
3405 END IF;
3406
3407 IF (purge_invoices%ISOPEN) THEN
3408 CLOSE purge_invoices;
3409 ELSIF (purge_invoices_group%ISOPEN) THEN
3410 CLOSE purge_invoices_group;
3411 END IF;
3412
3413 RETURN (FALSE);
3414 END IMPORT_PURGE;
3415
3416 --===============================================================
3417 -- Private functions: xml_import_purge
3418 --
3419 --===============================================================
3420 FUNCTION XML_IMPORT_PURGE(
3421 p_group_id IN VARCHAR2,
3422 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
3423
3424 TYPE headerlist IS TABLE OF ap_invoices_interface.invoice_id%TYPE;
3425
3426 h_list HEADERLIST;
3427 current_calling_sequence VARCHAR2(2000);
3428 debug_info VARCHAR2(500); /* Bug 4166583 */
3429
3430 BEGIN
3431
3432 -- update calling_sequence
3433 current_calling_sequence := 'xml_import_purge<--'||p_calling_sequence;
3434
3435 debug_info := '(XML Import Purge 1) before getting list of invoice_id';
3436 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3437 AP_IMPORT_UTILITIES_PKG.Print(
3438 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3439 END IF;
3440
3441 -- get all XML invoices with supplier rejections
3442
3443 -- Bug 4145391. To improve the performance of the import program coding two
3444 -- different select stmts based on the parameter p_group_id
3445 IF (p_group_id IS NULL) THEN
3446 SELECT h.invoice_id BULK COLLECT
3447 INTO h_list
3448 FROM ap_invoices_interface h,
3449 ap_invoice_lines_interface l,
3450 ap_interface_rejections r
3451 WHERE DECODE(r.parent_table, 'AP_INVOICES_INTERFACE',
3452 h.invoice_id,
3453 'AP_INVOICE_LINES_INTERFACE', l.invoice_line_id)
3454 = r.parent_id
3455 AND h.invoice_id = l.invoice_id
3456 AND nvl(r.notify_vendor_flag,'N') = 'Y'
3457 AND h.status = 'REJECTED'
3458 AND h.source = 'XML GATEWAY'
3459 AND nvl(h.ORG_ID,
3460 to_number(nvl(decode(SUBSTR(USERENV('CLIENT_INFO'),1,1),
3461 ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99')) )
3462 = to_number(nvl(decode(SUBSTR(USERENV('CLIENT_INFO'),1,1),
3463 ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99'))
3464 GROUP BY h.invoice_id;
3465 ELSE
3466 SELECT h.invoice_id BULK COLLECT
3467 INTO h_list
3468 FROM ap_invoices_interface h,
3469 ap_invoice_lines_interface l,
3470 ap_interface_rejections r
3471 WHERE DECODE(r.parent_table, 'AP_INVOICES_INTERFACE',
3472 h.invoice_id,
3473 'AP_INVOICE_LINES_INTERFACE', l.invoice_line_id)
3474 = r.parent_id
3475 AND h.invoice_id = l.invoice_id
3476 AND nvl(r.notify_vendor_flag,'N') = 'Y'
3477 AND h.status = 'REJECTED'
3478 AND h.source = 'XML GATEWAY'
3479 AND h.group_id = p_group_id
3480 AND nvl(h.ORG_ID,
3481 to_number(nvl(decode(SUBSTR(USERENV('CLIENT_INFO'),1,1),
3482 ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99')) )
3483 = to_number(nvl(decode(SUBSTR(USERENV('CLIENT_INFO'),1,1),
3484 ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99'))
3485 GROUP BY h.invoice_id;
3486 END IF;
3487
3488
3489 debug_info := '(XML Import Purge 1.1) number of invoices to delete: '
3490 || nvl(h_list.count,0);
3491 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3492 AP_IMPORT_UTILITIES_PKG.Print(
3493 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3494 END IF;
3495
3496 debug_info := '(XML Import Purge 2) before deleting header rejections';
3497 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3498 AP_IMPORT_UTILITIES_PKG.Print(
3499 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3500 END IF;
3501
3502 FORALL i IN nvl(h_list.FIRST,0) .. nvl(h_list.LAST,-1)
3503 DELETE FROM ap_interface_rejections r
3504 WHERE r.parent_id = h_list(i)
3505 AND r.parent_table = 'AP_INVOICES_INTERFACE';
3506
3507 debug_info := '(XML Import Purge 3) before deleting line rejections';
3508 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3509 AP_IMPORT_UTILITIES_PKG.Print(
3510 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3511 END IF;
3512
3513 FORALL i IN nvl(h_list.FIRST,0) .. nvl(h_list.LAST,-1)
3514 DELETE FROM ap_interface_rejections r
3515 WHERE r.parent_id IN (SELECT l.invoice_line_id
3516 FROM ap_invoice_lines_interface l
3517 WHERE l.invoice_id = h_list(i) )
3518 AND r.parent_table = 'AP_INVOICE_LINES_INTERFACE';
3519
3520 debug_info := '(XML Import Purge 4) before deleting header interface';
3521 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3522 AP_IMPORT_UTILITIES_PKG.Print(
3523 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3524 END IF;
3525
3526 -- Delete from ap_invoice_lines_interface table
3527 FORALL i IN nvl(h_list.FIRST,0) .. nvl(h_list.LAST,-1)
3528 DELETE FROM ap_invoice_lines_interface l
3529 WHERE l.invoice_id = h_list(i);
3530
3531 debug_info := '(XML Import Purge 5) before deleting line interface';
3532 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3533 AP_IMPORT_UTILITIES_PKG.Print(
3534 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3535 END IF;
3536
3537 -- Delete from ap_invoices_interface table
3538 FORALL i IN nvl(h_list.FIRST,0) .. nvl(h_list.LAST,-1)
3539 DELETE FROM ap_invoices_interface h
3540 WHERE h.invoice_id = h_list(i);
3541
3542 debug_info := '(XML Import Purge 6) COMMIT';
3543 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3544 AP_IMPORT_UTILITIES_PKG.Print(
3545 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3546 END IF;
3547
3548 COMMIT;
3549
3550 RETURN(TRUE);
3551
3552 EXCEPTION
3553 WHEN OTHERS THEN
3554
3555 debug_info := 'Failed after ' || debug_info;
3556 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3557 AP_IMPORT_UTILITIES_PKG.Print(
3558 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3559 END IF;
3560 RETURN(false);
3561
3562 END xml_import_purge;
3563
3564
3565 PROCEDURE SUBMIT_PAYMENT_REQUEST(
3566 p_api_version IN VARCHAR2 DEFAULT '1.0',
3567 p_invoice_interface_id IN NUMBER,
3568 p_budget_control IN VARCHAR2 DEFAULT 'Y',
3569 p_needs_invoice_approval IN VARCHAR2 DEFAULT 'N',
3570 p_invoice_id OUT NOCOPY NUMBER,
3571 x_return_status OUT NOCOPY VARCHAR2,
3572 x_msg_count OUT NOCOPY NUMBER,
3573 x_msg_data OUT NOCOPY VARCHAR2,
3574 x_rejection_list OUT NOCOPY rejection_tab_type,
3575 p_calling_sequence IN VARCHAR2,
3576 p_commit IN VARCHAR2 DEFAULT 'Y') IS
3577
3578
3579 l_batch_error_flag VARCHAR2(1);
3580 l_invoices_fetched NUMBER;
3581 l_invoices_created NUMBER;
3582 l_total_invoice_amount NUMBER;
3583 l_print_batch VARCHAR2(1);
3584
3585 payment_request_failure EXCEPTION;
3586 current_calling_sequence VARCHAR2(2000);
3587 debug_info VARCHAR2(500);
3588
3589 l_invoice_id NUMBER;
3590 l_source VARCHAR2(80);
3591 l_holds_count NUMBER;
3592 l_approval_status VARCHAR2(30);
3593 l_funds_return_code VARCHAR2(30);
3594
3595 CURSOR c_rejections IS
3596 SELECT parent_table,
3597 parent_id,
3598 reject_lookup_code
3599 FROM ap_interface_rejections
3600 WHERE parent_table = 'AP_INVOICES_INTERFACE'
3601 AND parent_id = p_invoice_interface_id
3602 UNION
3603 SELECT parent_table,
3604 parent_id,
3605 reject_lookup_code
3606 FROM ap_interface_rejections
3607 WHERE parent_table = 'AP_INVOICE_LINES_INTERFACE'
3608 AND parent_id IN (SELECT invoice_line_id
3609 FROM ap_invoice_lines_interface
3610 WHERE invoice_id = p_invoice_interface_id);
3611
3612 BEGIN
3613
3614 -- Update the calling sequence and initialize variables
3615 current_calling_sequence := 'Submit_Payment_Request<- '||p_calling_sequence;
3616
3617
3618 -- Give error message if the interface invoice id is not provided
3619 IF p_invoice_interface_id IS NULL THEN
3620
3621 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3622 AP_IMPORT_UTILITIES_PKG.Print(
3623 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3624 'No invoice_id<- '||current_calling_sequence);
3625 END IF;
3626
3627 FND_MESSAGE.Set_Name('SQLAP', 'AP_IMP_NO_INVOICE_ID');
3628 x_msg_data := FND_MESSAGE.Get;
3629
3630 x_return_status := 'F';
3631 return;
3632
3633 ELSE
3634
3635 SELECT source
3636 INTO l_source
3637 FROM ap_invoices_interface
3638 WHERE invoice_id = p_invoice_interface_id;
3639
3640
3641 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3642 AP_IMPORT_UTILITIES_PKG.Print(
3643 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3644 'Calling Import_Invoices<- '||current_calling_sequence);
3645 END IF;
3646
3647 -- Calling the import invoices routine to import the payment request
3648 -- invoices
3649 IF (IMPORT_INVOICES(
3650 NULL, --p_batch_name
3651 NULL, --p_gl_date
3652 NULL, --p_hold_code
3653 NULL, --p_hold_reason
3654 NULL, --p_commit_cycles
3655 l_source, --p_source
3656 NULL, --p_group_id
3657 NULL, --p_conc_request_id
3658 'N', --p_debug_switch
3659 NULL, --p_org_id,
3660 l_batch_error_flag,
3661 l_invoices_fetched,
3662 l_invoices_created,
3663 l_total_invoice_amount,
3664 l_print_batch,
3665 current_calling_sequence,
3666 p_invoice_interface_id,
3667 p_needs_invoice_approval,
3668 p_commit) <> TRUE) THEN
3669
3670 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3671 AP_IMPORT_UTILITIES_PKG.Print(
3672 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3673 'Error during import<- '||current_calling_sequence);
3674 END IF;
3675
3676 Raise payment_request_failure;
3677 END IF; -- Import Invoices
3678
3679
3680 -- If no invoices are created then get the list of rejections and
3681 -- send rejections to the calling routine
3682 IF l_invoices_created = 0 THEN
3683
3684 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3685 AP_IMPORT_UTILITIES_PKG.Print(
3686 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3687 'No invoices created<- '||current_calling_sequence);
3688 END IF;
3689
3690 x_return_status := 'R';
3691
3692 OPEN c_rejections;
3693 FETCH c_rejections BULK COLLECT INTO x_rejection_list;
3694 CLOSE c_rejections;
3695
3696 ELSE
3697 x_return_status := 'S';
3698
3699 END IF;
3700
3701
3702 IF g_invoice_id IS NOT NULL THEN
3703
3704 l_invoice_id := g_invoice_id;
3705 p_invoice_id := g_invoice_id;
3706
3707 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3708 AP_IMPORT_UTILITIES_PKG.Print(
3709 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3710 'Calling invoice validation<- '||current_calling_sequence);
3711 END IF;
3712
3713
3714 -- Calling the approve routine to validate the invoices that are
3715 -- imported
3716 ap_approval_pkg.approve
3717 ('', -- p_run_option
3718 '', -- p_invoice_batch_id
3719 '', -- p_begin_invoice_date
3720 '', -- p_end_invoice_date
3721 '', -- p_vendor_id
3722 '', -- p_pay_group
3723 l_invoice_id,
3724 '', -- p_entered_by
3725 '', -- p_set_of_books_id
3726 '', -- p_trace_option
3727 '', -- p_conc_flag
3728 l_holds_count,
3729 l_approval_status,
3730 l_funds_return_code,
3731 'PAYMENT REQUEST',
3732 current_calling_sequence,
3733 'N',
3734 p_budget_control,
3735 p_commit);
3736
3737 x_return_status := 'S';
3738
3739 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3740 AP_IMPORT_UTILITIES_PKG.Print(
3741 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3742 'Validation complete<- '||current_calling_sequence);
3743 END IF;
3744
3745 END IF;
3746
3747 END IF;
3748
3749 g_invoice_id := null;
3750
3751 EXCEPTION
3752 WHEN no_data_found THEN
3753 FND_MESSAGE.Set_Name('SQLAP', 'AP_IMP_NO_INVOICE_ID');
3754 x_msg_data := FND_MESSAGE.Get;
3755 x_return_status := 'F';
3756
3757 WHEN OTHERS THEN
3758 IF (SQLCODE < 0) THEN
3759 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3760 AP_IMPORT_UTILITIES_PKG.Print(
3761 AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
3762 END IF;
3763 x_msg_data := SQLERRM;
3764 x_return_status := 'F';
3765 END IF;
3766
3767 END SUBMIT_PAYMENT_REQUEST;
3768
3769
3770
3771 END AP_IMPORT_INVOICES_PKG;
[Home] [Help]