[Home] [Help]
PACKAGE BODY: APPS.AP_IMPORT_INVOICES_PKG
Source
1 PACKAGE BODY AP_IMPORT_INVOICES_PKG AS
2 /* $Header: apiimptb.pls 120.100.12020000.11 2013/04/16 15:18:33 nbshaik ship $ */
3
4
5 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
7
8 --==============================================================
9 -- delete attachment association
10 -- called by import_purge
11 -- (this function is also called by APXIIPRG.rdf)
12 --
13 --==============================================================
14 FUNCTION delete_attachments(p_invoice_id IN NUMBER)
15 RETURN NUMBER IS
16 l_attachments_count NUMBER := 0;
17 debug_info VARCHAR2(500);
18 BEGIN
19 select count(1)
20 into l_attachments_count
21 from fnd_attached_documents
22 where entity_name = 'AP_INVOICES_INTERFACE'
23 and pk1_value = p_invoice_id;
24
25 -- only delete if there is an attachment
26 if ( l_attachments_count > 0 ) then
27 -- assuming deleting only the association with related documents
28 -- need to see if that's always the case
29 fnd_attached_documents2_pkg.delete_attachments(
30 X_entity_name => 'AP_INVOICES_INTERFACE',
31 X_pk1_value => p_invoice_id,
32 X_delete_document_flag => 'N' );
33 end if;
34 return l_attachments_count;
35
36 EXCEPTION
37
38 WHEN OTHERS then
39 IF (SQLCODE < 0) then
40 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
41 AP_IMPORT_UTILITIES_PKG.Print(
42 AP_IMPORT_INVOICES_PKG.g_debug_switch, SQLERRM);
43 END IF;
44 END IF;
45 RETURN 0;
46 END delete_attachments;
47
48
49 --Payment Request: Added p_invoice_interface_id and p_needs_invoice_approval
50 --for importing payment request type invoices
51 FUNCTION IMPORT_INVOICES(
52 p_batch_name IN VARCHAR2,
53 p_gl_date IN DATE,
54 p_hold_code IN VARCHAR2,
55 p_hold_reason IN VARCHAR2,
56 p_commit_cycles IN NUMBER,
57 p_source IN VARCHAR2,
58 p_group_id IN VARCHAR2,
59 p_conc_request_id IN NUMBER,
60 p_debug_switch IN VARCHAR2,
61 p_org_id IN NUMBER,
62 p_batch_error_flag OUT NOCOPY VARCHAR2,
63 p_invoices_fetched OUT NOCOPY NUMBER,
64 p_invoices_created OUT NOCOPY NUMBER,
65 p_total_invoice_amount OUT NOCOPY NUMBER,
66 p_print_batch OUT NOCOPY VARCHAR2,
67 p_calling_sequence IN VARCHAR2,
68 p_invoice_interface_id IN NUMBER DEFAULT NULL,
69 p_needs_invoice_approval IN VARCHAR2 DEFAULT 'N',
70 p_commit IN VARCHAR2 DEFAULT 'Y')
71 RETURN BOOLEAN IS
72
73 -- Define invoice cursor
74
75 /* For bug3988118.
76 * Need to add UPPER for the flag values as the user can populate any
77 * value in the import tables and we do not validate if it is
78 * directly populated with 'y' or 'Y' or 'n' or 'N'
79 * Added UPPER for exclusive_payment_flag and invoice_includes_prepay_flag
80 * */
81
82 /* For bug 3972507
83 * Changed trim to rtrim in order to rtrim only
84 * trailing spaces */
85
86 -- Bug 4145391. Modified the select for the cursor to improve performance.
87 -- Removed the p_group_id where clause and added it to the cursor
88 -- import_invoices_group
89
90 /* Bug 6349739- Modified cursors import_invoices and
91 * import_invoices_group for RETEK invoices to set
92 * calc_tax_during_import_flag to 'N' for RETEK invoices */
93
94 --Modified below cursor for bug #9254176
95 --Added rtrim for all varchar2 fields.
96
97 --Bug 13711758: Changed rtrim() to trim() on description / DFF attributes to sync with Lines.
98
99 CURSOR import_invoices is
100 SELECT invoice_id,
101 rtrim(invoice_num) invoice_num,
102 rtrim(invoice_type_lookup_code) invoice_type_lookup_code,
103 invoice_date,
104 po_number,
105 vendor_id,
106 vendor_num,
107 vendor_name,
108 vendor_site_id,
109 vendor_site_code,
110 invoice_amount,
111 rtrim(invoice_currency_code) invoice_currency_code,
112 exchange_rate,
113 rtrim(exchange_rate_type) exchange_rate_type,
114 exchange_date,
115 terms_id,
116 terms_name,
117 terms_date,
118 trim(description) description, --Bug 13711758
119 awt_group_id,
120 awt_group_name,
121 pay_awt_group_id,--bug6639866
122 pay_awt_group_name,--bug6639866
123 amount_applicable_to_discount,
124 sysdate,
125 last_updated_by,
126 last_update_login,
127 sysdate,
128 created_by,
129 rtrim(status) status,
130 --Bug 13711758 Start
131 trim(attribute_category) attribute_category,
132 trim(attribute1) attribute1,
133 trim(attribute2) attribute2,
134 trim(attribute3) attribute3,
135 trim(attribute4) attribute4,
136 trim(attribute5) attribute5,
137 trim(attribute6) attribute6,
138 trim(attribute7) attribute7,
139 trim(attribute8) attribute8,
140 trim(attribute9) attribute9,
141 trim(attribute10) attribute10,
142 trim(attribute11) attribute11,
143 trim(attribute12) attribute12,
144 trim(attribute13) attribute13,
145 trim(attribute14) attribute14,
146 trim(attribute15) attribute15,
147 trim(global_attribute_category) global_attribute_category,
148 trim(global_attribute1) global_attribute1,
149 trim(global_attribute2) global_attribute2,
150 trim(global_attribute3) global_attribute3,
151 trim(global_attribute4) global_attribute4,
152 trim(global_attribute5) global_attribute5,
153 trim(global_attribute6) global_attribute6,
154 trim(global_attribute7) global_attribute7,
155 trim(global_attribute8) global_attribute8,
156 trim(global_attribute9) global_attribute9,
157 trim(global_attribute10) global_attribute10,
158 trim(global_attribute11) global_attribute11,
159 trim(global_attribute12) global_attribute12,
160 trim(global_attribute13) global_attribute13,
161 trim(global_attribute14) global_attribute14,
162 trim(global_attribute15) global_attribute15,
163 trim(global_attribute16) global_attribute16,
164 trim(global_attribute17) global_attribute17,
165 trim(global_attribute18) global_attribute18,
166 trim(global_attribute19) global_attribute19,
167 trim(global_attribute20) global_attribute20,
168 --Bug 13711758 end
169 rtrim(payment_currency_code) payment_currency_code,
170 payment_cross_rate,
171 rtrim(payment_cross_rate_type) payment_cross_rate_type,
172 payment_cross_rate_date,
173 doc_category_code,
174 rtrim(voucher_num) voucher_num,
175 rtrim(payment_method_code) payment_method_code,
176 rtrim(pay_group_lookup_code) pay_group_lookup_code,
177 trunc(goods_received_date), /*16240104*/
178 trunc(invoice_received_date), /*16240104*/
179 trunc(gl_date), /*16240104*/
180 accts_pay_code_combination_id,
181 -- bug 6509776
182 RTRIM(accts_pay_code_concatenated,'-'),
183 -- ussgl_transaction_code, - Bug 4277744
184 UPPER(exclusive_payment_flag),
185 prepay_num,
186 prepay_line_num,
187 prepay_apply_amount,
188 prepay_gl_date,
189 UPPER(invoice_includes_prepay_flag),
190 no_xrate_base_amount,
191 requester_id,
192 org_id,
193 operating_unit,
194 rtrim(source) source,
195 group_id,
196 request_id,
197 workflow_flag,
198 vendor_email_address,
199 NVL(calc_tax_during_import_flag, 'N'), -- bug 6349739,bug6328293
200 control_amount,
201 add_tax_to_inv_amt_flag,
202 tax_related_invoice_id,
203 rtrim(taxation_country) taxation_country,
204 rtrim(document_sub_type) document_sub_type,
205 rtrim(supplier_tax_invoice_number) supplier_tax_invoice_number,
206 supplier_tax_invoice_date,
207 supplier_tax_exchange_rate,
208 tax_invoice_recording_date,
209 tax_invoice_internal_seq,
210 legal_entity_id,
211 null,
212 ap_import_utilities_pkg.get_tax_only_rcv_matched_flag(invoice_id),
213 ap_import_utilities_pkg.get_tax_only_flag(invoice_id),
214 apply_advances_flag,
215 application_id,
216 product_table,
217 reference_key1,
218 reference_key2,
219 reference_key3,
220 reference_key4,
221 reference_key5,
222 reference_1,
223 reference_2,
224 net_of_retainage_flag,
225 rtrim(cust_registration_code) cust_registration_code,
226 rtrim(cust_registration_number) cust_registration_number,
227 paid_on_behalf_employee_id,
228 party_id, -- Added for Payment Requests
229 party_site_id,
230 rtrim(pay_proc_trxn_type_code) pay_proc_trxn_type_code,
231 rtrim(payment_function) payment_function,
232 rtrim(payment_priority) payment_priority,
233 rtrim(BANK_CHARGE_BEARER) BANK_CHARGE_BEARER,
234 rtrim(REMITTANCE_MESSAGE1) REMITTANCE_MESSAGE1,
235 rtrim(REMITTANCE_MESSAGE2) REMITTANCE_MESSAGE2,
236 rtrim(REMITTANCE_MESSAGE3) REMITTANCE_MESSAGE3,
237 rtrim(UNIQUE_REMITTANCE_IDENTIFIER) UNIQUE_REMITTANCE_IDENTIFIER,
238 URI_CHECK_DIGIT,
239 SETTLEMENT_PRIORITY,
240 rtrim(PAYMENT_REASON_CODE) PAYMENT_REASON_CODE,
241 rtrim(PAYMENT_REASON_COMMENTS) PAYMENT_REASON_COMMENTS,
242 rtrim(DELIVERY_CHANNEL_CODE) DELIVERY_CHANNEL_CODE,
243 EXTERNAL_BANK_ACCOUNT_ID,
244 --Bug 7357218 Quick Pay and Dispute Resolution Project
245 ORIGINAL_INVOICE_AMOUNT ,
246 DISPUTE_REASON,
247 --Third Party Payments
248 rtrim(REMIT_TO_SUPPLIER_NAME) REMIT_TO_SUPPLIER_NAME,
249 REMIT_TO_SUPPLIER_ID ,
250 rtrim(REMIT_TO_SUPPLIER_SITE) REMIT_TO_SUPPLIER_SITE,
251 REMIT_TO_SUPPLIER_SITE_ID,
252 RELATIONSHIP_ID,
253 REMIT_TO_SUPPLIER_NUM
254 /* Added for bug 10226070 */
255 ,REQUESTER_LAST_NAME
256 ,REQUESTER_FIRST_NAME
257 /* Added for bug 13074325 */
258 ,REQUESTER_EMPLOYEE_NUM
259 --bug 15862708 null columns to sync data with the new
260 --columns added in the record type
261 ,null
262 ,null
263 ,null
264 ,null
265 ,null
266 --bug 16092065 starts
267 ,null
268 ,null
269 ,null
270 --bug 16092065 ends
271 FROM ap_invoices_interface
272 WHERE ((status is NULL) OR (status = 'REJECTED'))
273 AND source = p_source
274 AND ((p_invoice_interface_id IS NULL AND
275 NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
276 OR (invoice_id = p_invoice_interface_id))
277 AND NVL(workflow_flag,'D') = 'D'
278 AND ( (p_commit_cycles IS NULL)
279 OR (rownum <= p_commit_cycles))
280 AND ( (org_id IS NOT NULL AND
281 p_org_id IS NOT NULL AND
282 org_id = p_org_id)
283 OR (p_org_id IS NULL AND
284 org_id IS NOT NULL AND
285 (mo_global.check_access(org_id)= 'Y'))
286 OR (p_org_id is NOT NULL AND org_id IS NULL)
287 OR (p_org_id is NULL AND org_id IS NULL))
288 ORDER BY org_id,
289 invoice_id,
290 vendor_id,
291 vendor_num,
292 vendor_name,
293 vendor_site_id,
294 vendor_site_code,
295 invoice_num
296 For UPDATE of invoice_id NOWAIT;
297
298
299 --Modified below cursor for bug #9254176
300 --Added rtrim for all varchar2 fields.
301
302 --Bug 13711758: Changed rtrim() to trim() on description / DFF attributes to sync with Lines.
303
304 CURSOR import_invoices_group is
305 SELECT invoice_id,
306 rtrim(invoice_num) invoice_num,
307 rtrim(invoice_type_lookup_code) invoice_type_lookup_code,
308 invoice_date,
309 po_number,
310 vendor_id,
311 vendor_num,
312 vendor_name,
313 vendor_site_id,
314 vendor_site_code,
315 invoice_amount,
316 rtrim(invoice_currency_code) invoice_currency_code,
317 exchange_rate,
318 rtrim(exchange_rate_type) exchange_rate_type,
319 exchange_date,
320 terms_id,
321 terms_name,
322 terms_date,
323 trim(description) description, --Bug 13711758
324 awt_group_id,
325 awt_group_name,
326 pay_awt_group_id,--bug6639866
327 pay_awt_group_name,--bug6639866
328 amount_applicable_to_discount,
329 sysdate,
330 last_updated_by,
331 last_update_login,
332 sysdate,
333 created_by,
334 rtrim(status) status,
335 --Bug 13711758 start
336 trim(attribute_category) attribute_category,
337 trim(attribute1) attribute1,
338 trim(attribute2) attribute2,
339 trim(attribute3) attribute3,
340 trim(attribute4) attribute4,
341 trim(attribute5) attribute5,
342 trim(attribute6) attribute6,
343 trim(attribute7) attribute7,
344 trim(attribute8) attribute8,
345 trim(attribute9) attribute9,
346 trim(attribute10) attribute10,
347 trim(attribute11) attribute11,
348 trim(attribute12) attribute12,
349 trim(attribute13) attribute13,
350 trim(attribute14) attribute14,
351 trim(attribute15) attribute15,
352 trim(global_attribute_category) global_attribute_category,
353 trim(global_attribute1) global_attribute1,
354 trim(global_attribute2) global_attribute2,
355 trim(global_attribute3) global_attribute3,
356 trim(global_attribute4) global_attribute4,
357 trim(global_attribute5) global_attribute5,
358 trim(global_attribute6) global_attribute6,
359 trim(global_attribute7) global_attribute7,
360 trim(global_attribute8) global_attribute8,
361 trim(global_attribute9) global_attribute9,
362 trim(global_attribute10) global_attribute10,
363 trim(global_attribute11) global_attribute11,
364 trim(global_attribute12) global_attribute12,
365 trim(global_attribute13) global_attribute13,
366 trim(global_attribute14) global_attribute14,
367 trim(global_attribute15) global_attribute15,
368 trim(global_attribute16) global_attribute16,
369 trim(global_attribute17) global_attribute17,
370 trim(global_attribute18) global_attribute18,
371 trim(global_attribute19) global_attribute19,
372 trim(global_attribute20) global_attribute20,
373 --Bug 13711758 end
374 rtrim(payment_currency_code) payment_currency_code,
375 payment_cross_rate,
376 rtrim(payment_cross_rate_type) payment_cross_rate_type,
377 payment_cross_rate_date,
378 doc_category_code,
379 rtrim(voucher_num) voucher_num,
380 rtrim(payment_method_code) payment_method_code,
381 rtrim(pay_group_lookup_code) pay_group_lookup_code,
382 trunc(goods_received_date), /*16240104*/
383 trunc(invoice_received_date), /*16240104*/
384 trunc(gl_date), /*16240104*/
385 accts_pay_code_combination_id,
386 -- bug 6509776
387 RTRIM(accts_pay_code_concatenated,'-'),
388 -- ussgl_transaction_code, - Bug 4277744
389 UPPER(exclusive_payment_flag),
390 prepay_num,
391 prepay_line_num,
392 prepay_apply_amount,
393 prepay_gl_date,
394 UPPER(invoice_includes_prepay_flag),
395 no_xrate_base_amount,
396 requester_id,
397 org_id,
398 operating_unit,
399 rtrim(source) source,
400 group_id,
401 request_id,
402 workflow_flag,
403 vendor_email_address,
404 NVL(calc_tax_during_import_flag, 'N'), -- bug 6349739,bug6328293
405 control_amount,
406 add_tax_to_inv_amt_flag,
407 tax_related_invoice_id,
408 rtrim(taxation_country) taxation_country,
409 rtrim(document_sub_type) document_sub_type,
410 rtrim(supplier_tax_invoice_number) supplier_tax_invoice_number,
411 supplier_tax_invoice_date,
412 supplier_tax_exchange_rate,
413 tax_invoice_recording_date,
414 tax_invoice_internal_seq,
415 legal_entity_id,
416 null,
417 ap_import_utilities_pkg.get_tax_only_rcv_matched_flag(invoice_id),
418 ap_import_utilities_pkg.get_tax_only_flag(invoice_id),
419 apply_advances_flag,
420 application_id,
421 product_table,
422 reference_key1,
423 reference_key2,
424 reference_key3,
425 reference_key4,
426 reference_key5,
427 reference_1,
428 reference_2,
429 net_of_retainage_flag,
430 rtrim(cust_registration_code) cust_registration_code,
431 rtrim(cust_registration_number) cust_registration_number,
432 paid_on_behalf_employee_id,
433 party_id, -- Added for Payment Requests
434 party_site_id,
435 rtrim(pay_proc_trxn_type_code) pay_proc_trxn_type_code,
436 rtrim(payment_function) payment_function,
437 rtrim(payment_priority) payment_priority,
438 rtrim(BANK_CHARGE_BEARER) BANK_CHARGE_BEARER,
439 rtrim(REMITTANCE_MESSAGE1) REMITTANCE_MESSAGE1,
440 rtrim(REMITTANCE_MESSAGE2) REMITTANCE_MESSAGE2,
441 rtrim(REMITTANCE_MESSAGE3) REMITTANCE_MESSAGE3,
442 rtrim(UNIQUE_REMITTANCE_IDENTIFIER) UNIQUE_REMITTANCE_IDENTIFIER,
443 URI_CHECK_DIGIT,
444 SETTLEMENT_PRIORITY,
445 rtrim(PAYMENT_REASON_CODE) PAYMENT_REASON_CODE,
446 rtrim(PAYMENT_REASON_COMMENTS) PAYMENT_REASON_COMMENTS,
447 rtrim(DELIVERY_CHANNEL_CODE) DELIVERY_CHANNEL_CODE,
448 EXTERNAL_BANK_ACCOUNT_ID,
449 --Bug 7357218 Quick Pay and Dispute Resolution Project
450 ORIGINAL_INVOICE_AMOUNT,
451 DISPUTE_REASON,
452 --Third Party Payments
453 rtrim(REMIT_TO_SUPPLIER_NAME) REMIT_TO_SUPPLIER_NAME,
454 REMIT_TO_SUPPLIER_ID,
455 rtrim(REMIT_TO_SUPPLIER_SITE) REMIT_TO_SUPPLIER_SITE,
456 REMIT_TO_SUPPLIER_SITE_ID,
457 RELATIONSHIP_ID,
458 REMIT_TO_SUPPLIER_NUM
459 /* Added for bug 10226070 */
460 ,REQUESTER_LAST_NAME
461 ,REQUESTER_FIRST_NAME
462 /* Added for bug 13074325 */
463 ,REQUESTER_EMPLOYEE_NUM
464 --bug 15862708
465 ,null
466 ,null
467 ,null
468 ,null
469 ,null
470 --bug 16092065 starts
471 ,null
472 ,null
473 ,null
474 --bug 16092065 ends
475 FROM ap_invoices_interface
476 WHERE ((status is NULL) OR (status = 'REJECTED'))
477 AND source = p_source
478 AND group_id = p_group_id
479 AND ((p_invoice_interface_id IS NULL AND
480 NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
481 OR (invoice_id = p_invoice_interface_id))
482 AND NVL(workflow_flag,'D') = 'D'
483 AND ( (p_commit_cycles IS NULL)
484 OR (rownum <= p_commit_cycles))
485 AND ( (org_id IS NOT NULL AND
486 p_org_id IS NOT NULL AND
487 org_id = p_org_id)
488 OR (p_org_id IS NULL AND
489 org_id IS NOT NULL AND
490 (mo_global.check_access(org_id)= 'Y'))
491 OR (p_org_id is NOT NULL AND org_id IS NULL)
492 OR (p_org_id is NULL AND org_id IS NULL))
493 ORDER BY org_id,
494 invoice_id,
495 vendor_id,
496 vendor_num,
497 vendor_name,
498 vendor_site_id,
499 vendor_site_code,
500 invoice_num
501 For UPDATE of invoice_id NOWAIT;
502
503 l_invoice_rec AP_IMPORT_INVOICES_PKG.r_invoice_info_rec;
504 l_invoice_lines_tab AP_IMPORT_INVOICES_PKG.t_lines_table;
505 l_default_last_updated_by NUMBER;
506 l_default_last_update_login NUMBER;
507 l_multi_currency_flag VARCHAR2(1);
508 l_make_rate_mandatory_flag VARCHAR2(1);
509 l_default_exchange_rate_type VARCHAR2(30);
510 l_base_currency_code VARCHAR2(15);
511 l_batch_control_flag VARCHAR2(1);
512 l_base_min_acct_unit NUMBER;
513 l_base_precision NUMBER;
514 l_sequence_numbering VARCHAR2(1);
515 l_awt_include_tax_amt VARCHAR2(1);
516 l_gl_date_from_get_info DATE;
517 -- l_ussgl_transcation_code VARCHAR2(30); - Bug 4277744
518 l_transfer_po_desc_flex_flag VARCHAR2(1);
519 l_gl_date_from_receipt_flag VARCHAR2(25);
520 l_purch_encumbrance_flag VARCHAR2(1);
521 l_retainage_ccid NUMBER;
522 l_pa_installed VARCHAR2(1):='N';
523 l_chart_of_accounts_id NUMBER;
524 l_positive_price_tolerance NUMBER;
525 l_negative_price_tolerance NUMBER;
526 l_qty_tolerance NUMBER;
527 l_qty_rec_tolerance NUMBER;
528 l_amt_tolerance NUMBER;
529 l_amt_rec_tolerance NUMBER;
530 l_max_qty_ord_tolerance NUMBER;
531 l_max_qty_rec_tolerance NUMBER;
532 l_max_amt_ord_tolerance NUMBER;
533 l_max_amt_rec_tolerance NUMBER;
534 l_goods_ship_amt_tolerance NUMBER;
535 l_goods_rate_amt_tolerance NUMBER;
536 l_goods_total_amt_tolerance NUMBER;
537 l_services_ship_amt_tolerance NUMBER;
538 l_services_rate_amt_tolerance NUMBER;
539 l_services_total_amt_tolerance NUMBER;
540 l_inv_doc_cat_override VARCHAR2(1):='N';
541 l_pay_curr_invoice_amount NUMBER;
542 l_invoice_amount_limit NUMBER;
543 l_hold_future_payments_flag VARCHAR2(1);
544 l_supplier_hold_reason VARCHAR2(240);
545 l_invoice_status VARCHAR2(1) :='Y';
546 l_gl_date DATE;
547 l_min_acct_unit NUMBER;
548 l_precision NUMBER;
549 l_payment_priority NUMBER;
550 l_batch_id NUMBER;
551 l_batch_name VARCHAR2(50);
552 l_continue_flag VARCHAR2(1) := 'Y';
553 l_fatal_error_flag VARCHAR2(1) := 'N';
554 l_base_invoice_id NUMBER(15);
555 l_invoice_currency_code VARCHAR2(15);
556 l_batch_exists_flag VARCHAR2(1) := 'N';
557 l_batch_type VARCHAR2(30);
558 l_valid_invoices_count NUMBER:=0;
559 l_match_mode VARCHAR2(25);
560 l_dbseqnm VARCHAR2(30);
561 l_dbseqid NUMBER;
562 l_seqval NUMBER;
563 l_apply_prepay_log LONG;
564 l_invoices_fetched NUMBER:=0;
565 l_actual_invoice_total NUMBER:=0;
566 import_invoice_failure EXCEPTION;
567 current_calling_sequence VARCHAR2(2000);
568 debug_info VARCHAR2(500);
569 l_total_invoice_amount NUMBER :=0;
570 l_calc_user_xrate VARCHAR2(1);
571 l_approval_workflow_flag VARCHAR2(1);
572 l_freight_code_combination_id NUMBER;
573 l_old_org_id NUMBER;
574 l_default_org_id NUMBER;
575 l_ou_count NUMBER;
576 l_default_ou_name VARCHAR2(240);
577 l_derived_operating_unit VARCHAR2(240);
578 l_null_org_id BOOLEAN;
579 l_total_count NUMBER := 0;
580 l_set_of_books_id NUMBER;
581 l_error_code VARCHAR2(500);
582
583 l_prepay_appl_log ap_prepay_pkg.Prepay_Appl_Log_Tab;
584 l_prepay_period_name VARCHAR2(25);
585 --Contract Payments
586 l_prepay_invoice_id NUMBER;
587 l_prepay_case_name VARCHAR2(50);
588 l_inv_amount_unpaid NUMBER;
589 l_amount_to_apply NUMBER;
590
591 l_allow_interest_invoices VARCHAR2(1); --bugfix:4113223
592 l_option_defined_org NUMBER; -- bug 5140002
593
594 TYPE numlist is TABLE OF ap_interface_rejections.parent_id%TYPE;
595
596 enums numlist;
597 --bug:4930111
598 l_add_days_settlement_date NUMBER;
599 --bug 4931755
600 l_disc_is_inv_less_tax_flag VARCHAR2(1);
601 l_exclude_freight_from_disc VARCHAR2(1);
602
603 l_exclusive_tax_amount NUMBER;
604 l_inv_hdr_amount NUMBER;
605 l_payment_status_flag VARCHAR2(50);
606 l_message1 VARCHAR2(50);
607 l_message2 VARCHAR2(50);
608 l_reset_match_status VARCHAR2(50);
609 l_liability_adjusted_flag VARCHAR2(50);
610 l_revalidate_ps VARCHAR2(50);
611 -- Bug 5448579
612 l_moac_org_table AP_IMPORT_INVOICES_PKG.moac_ou_tab_type;
613 l_fsp_org_table AP_IMPORT_INVOICES_PKG.fsp_org_tab_type;
614 l_index_org_id NUMBER;
615 l_asset_book_type FA_BOOK_CONTROLS.book_type_code%TYPE;
616
617 -- Bug 5645581.
618 l_inv_gl_date DATE; --Bug 5382889. LE Timezone
619 l_rts_txn_le_date DATE; --Bug 5382889. LE Timezone
620 l_inv_le_date DATE; --Bug 5382889. LE Timezone
621 l_sys_le_date DATE; --Bug 5382889. LE Timezone
622
623 -- Bug 7282839 start added variables to calculate tax for base currency.
624 l_base_exclusive_tax_amount number;
625 l_exchange_rate number;
626 -- Bug 7282839 end
627
628 -- added for bug 8237318
629 l_stmt varchar2(5000);
630
631 --7567527
632 l_parameter_list wf_parameter_list_t;
633 l_event_key VARCHAR2(100);
634 l_event_name VARCHAR2(100) := 'oracle.apps.ap.invoice.import';
635
636 --Bug8876668
637 l_reject_status_code VARCHAR2(1) :='Y';
638 l_period_name gl_period_statuses.period_name%TYPE := ''; /* Added for bug#12356854 */
639 --bug 15862708 start
640 tab_invoice_table AP_IMPORT_INVOICES_PKG.t_invoice_table;
641 tab_lines_table AP_IMPORT_INVOICES_PKG.t_lines_table;
642 l_inv_index NUMBER := 0;
643 l_line_index NUMBER := 0;
644 k NUMBER := 0;
645 j NUMBER := 0;
646 l_get_info_rec AP_IMPORT_INVOICES_PKG.get_info_rec;
647 calculate_tax_failure EXCEPTION; --bug 15862708
648 LINES_TAB AP_IMPORT_INVOICES_PKG.LINES_TABLE := AP_IMPORT_INVOICES_PKG.LINES_TABLE();
649 k_fir NUMBER := 0;
650 --bug 15862708 ends
651 l_invoices_created NUMBER:=0; --bug 14530013
652 BEGIN
653 /*Bug 14723659:Start*/
654 AP_IMPORT_INVOICES_PKG.g_inv_sysdate := TRUNC(sysdate);
655 /*Bug 14723659:End*/
656
657 --bug 15862708 start
658 debug_info := '(Import_invoices start time:'||to_char(sysdate, 'DD-Mon-YYYY HH24:MI:SS');
659 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
660 AP_IMPORT_UTILITIES_PKG.Print(
661 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
662 END IF;
663 --bug 15862708 ends
664 -- Update the calling sequence and initialize variables
665
666 current_calling_sequence := 'Import_invoices<- '||p_calling_sequence;
667
668 p_batch_error_flag := 'N';
669 l_gl_date_from_get_info := TRUNC(p_gl_date);
670
671 AP_IMPORT_INVOICES_PKG.g_debug_switch := p_debug_switch;
672 AP_IMPORT_INVOICES_PKG.g_source := p_source;
673 AP_IMPORT_INVOICES_PKG.g_program_application_id := FND_GLOBAL.prog_appl_id;
674 AP_IMPORT_INVOICES_PKG.g_program_id := FND_GLOBAL.conc_program_id;
675 AP_IMPORT_INVOICES_PKG.g_conc_request_id := p_conc_request_id;
676
677 --bug 16656238
678 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
679 AP_IMPORT_INVOICES_PKG.g_debug_switch := 'Y';
680 END IF;
681
682 debug_info := 'Request_id'||p_conc_request_id;
683 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
684 AP_IMPORT_UTILITIES_PKG.Print(
685 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
686 END IF;
687 -- Retropricing
688 IF AP_IMPORT_INVOICES_PKG.g_source = 'PPA' THEN
689 AP_IMPORT_INVOICES_PKG.g_invoices_table := 'AP_PPA_INVOICES_GT';
690 AP_IMPORT_INVOICES_PKG.g_invoice_lines_table := 'AP_PPA_INVOICE_LINES_GT';
691 AP_IMPORT_INVOICES_PKG.g_instructions_table := 'AP_PPA_INSTRUCTIONS_GT';
692 ELSE
693 AP_IMPORT_INVOICES_PKG.g_invoices_table := 'AP_INVOICES_INTERFACE';
694 AP_IMPORT_INVOICES_PKG.g_invoice_lines_table := 'AP_INVOICE_LINES_INTERFACE';
695 AP_IMPORT_INVOICES_PKG.g_instructions_table := NULL;
696 END IF;
697
698 -- Bug 5448579
699 ----------------------------------------------------------------
700 debug_info := '(Import_invoice 0.1) Calling Caching Function for Org Id/Name';
701 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
702 AP_IMPORT_UTILITIES_PKG.Print(
703 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
704 END IF;
705 IF (AP_IMPORT_UTILITIES_PKG.Cache_Org_Id_Name (
706 P_Moac_Org_Table => AP_IMPORT_INVOICES_PKG.g_moac_ou_tab,
707 P_Fsp_Org_Table => AP_IMPORT_INVOICES_PKG.g_fsp_ou_tab,
708 P_Calling_Sequence => current_calling_sequence ) <> TRUE) THEN
709 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
710 AP_IMPORT_UTILITIES_PKG.Print(
711 AP_IMPORT_INVOICES_PKG.g_debug_switch,
712 'Cache_Org_Id_Name <-'||current_calling_sequence);
713 END IF;
714 Raise import_invoice_failure;
715 END IF;
716
717
718 debug_info := '(Import_Invoices 0.2) Calling Caching Function for Currency';
719 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
720 AP_IMPORT_UTILITIES_PKG.Print(
721 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
722 END IF;
723 IF (AP_IMPORT_UTILITIES_PKG.Cache_Fnd_Currency (
724 P_Fnd_Currency_Table => AP_IMPORT_INVOICES_PKG.g_fnd_currency_tab,
725 P_Calling_Sequence => current_calling_sequence ) <> TRUE) THEN
726 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
727 AP_IMPORT_UTILITIES_PKG.Print(
728 AP_IMPORT_INVOICES_PKG.g_debug_switch,
729 'Cache_Fnd_Currency <-'||current_calling_sequence);
730 END IF;
731 Raise import_invoice_failure;
732 END IF;
733
734
735 debug_info := '(Import_Invoices 0.3) Calling Caching Function for Payment Method';
736 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
737 AP_IMPORT_UTILITIES_PKG.Print(
738 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
739 END IF;
740 IF (AP_IMPORT_UTILITIES_PKG.Cache_Payment_Method (
741 P_Payment_Method_Table => AP_IMPORT_INVOICES_PKG.g_payment_method_tab,
742 P_Calling_Sequence => current_calling_sequence ) <> TRUE) THEN
743 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
744 AP_IMPORT_UTILITIES_PKG.Print(
745 AP_IMPORT_INVOICES_PKG.g_debug_switch,
746 'Cache_Payment_Method <-'||current_calling_sequence);
747 END IF;
748 Raise import_invoice_failure;
749 END IF;
750
751 debug_info := '(Import_Invoices 0.4) Calling Caching Function for Payment Group';
752 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
753 AP_IMPORT_UTILITIES_PKG.Print(
754 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
755 END IF;
756 IF (AP_IMPORT_UTILITIES_PKG.Cache_Pay_Group (
757 P_Pay_Group_Table => AP_IMPORT_INVOICES_PKG.g_pay_group_tab,
758 P_Calling_Sequence => current_calling_sequence ) <> TRUE) THEN
759 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
760 AP_IMPORT_UTILITIES_PKG.Print(
761 AP_IMPORT_INVOICES_PKG.g_debug_switch,
762 'Cache_Pay_Group <-'||current_calling_sequence);
763 END IF;
764 Raise import_invoice_failure;
765 END IF;
766
767 debug_info := '(Import_Invoices 0.5) Caching Structure Id';
768 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
769 AP_IMPORT_UTILITIES_PKG.Print(
770 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
771 END IF;
772
773 BEGIN
774
775 SELECT structure_id
776 INTO AP_IMPORT_INVOICES_PKG.g_structure_id
777 FROM mtl_default_sets_view
778 WHERE functional_area_id = 2;
779
780 EXCEPTION WHEN OTHERS THEN
781 NULL;
782
783 END;
784
785
786 fnd_plsql_cache.generic_1tom_init(
787 'PeriodName',
788 lg_many_controller,
789 lg_generic_storage);
790
791 fnd_plsql_cache.generic_1tom_init(
792 'ValidateSegs',
793 lg_many_controller1,
794 lg_generic_storage1);
795
796 fnd_plsql_cache.generic_1tom_init(
797 'CodeCombinations',
798 lg_many_controller2,
799 lg_generic_storage2);
800
801 -- Bug 5572876
802 fnd_plsql_cache.generic_1tom_init(
803 'IncomeTaxType',
804 lg_incometax_controller,
805 lg_incometax_storage);
806
807 -- 5572876
808 fnd_plsql_cache.generic_1tom_init(
809 'IncomeTaxRegion',
810 lg_incometaxr_controller,
811 lg_incometaxr_storage);
812
813 --------------------------------------------------------
814 -- Step 1
815 -- Check control table for the import batch
816 --------------------------------------------------------
817
818 debug_info := '(Import_invoice 1) Check control table for the import batch';
819
820 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
821 AP_IMPORT_UTILITIES_PKG.Print(
822 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
823 END IF;
824 /*
825 IF (AP_IMPORT_UTILITIES_PKG.check_control_table(
826 p_source,
827 p_group_id,
828 current_calling_sequence) <> TRUE) THEN
829
830 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
831 AP_IMPORT_UTILITIES_PKG.Print(
832 AP_IMPORT_INVOICES_PKG.g_debug_switch,'check_control_table<-'
833 ||current_calling_sequence);
834 END IF;
835 Raise import_invoice_failure;
836 END IF;
837 */
838 --------------------------------------------------------
839 -- Step 2
840 -- AP_IMPORT_UTILITIES_PKG.Print source if debug is turned on and
841 -- get default last updated by and last update login information.
842 --------------------------------------------------------
843 debug_info := '(Import_invoice 2) Print Source';
844
845 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
846 AP_IMPORT_UTILITIES_PKG.Print(
847 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
848 END IF;
849
850 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
851 AP_IMPORT_UTILITIES_PKG.Print(
852 AP_IMPORT_INVOICES_PKG.g_debug_switch, 'p_source' || p_source);
853 END IF;
854
855 l_default_last_updated_by := to_number(FND_GLOBAL.USER_ID);
856 l_default_last_update_login := to_number(FND_GLOBAL.LOGIN_ID);
857
858 ----------------------------------------------------------------
859 -- Step 3 Delete any rejections from previous failed imports
860 -- of this invoice line
861 ----------------------------------------------------------------
862
863 debug_info := '(Import Invoice 3) Delete Rejections from previous failed '||
864 'imports';
865 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
866 AP_IMPORT_UTILITIES_PKG.Print(
867 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
868 END IF;
869
870 debug_info := '(Check_lines 3a) Select all the Rejected Invoices';
871 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
872 AP_IMPORT_UTILITIES_PKG.Print(
873 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
874 END IF;
875
876 -- Bug 4145391. To improve the performance of the import program coding two
877 -- different select stmts based on the parameter p_group_id
878
879 /* BUG 8237318 - Begin modified the existing query to dynamic sql to skip the null checks
880 for passing parameters in the query. */
881 l_stmt := 'SELECT invoice_id '
882 ||' FROM ap_invoices_interface WHERE source = ''' ||p_source || ''' '
883 ||' AND ((status is NULL) or (status = ''REJECTED'')) ';
884
885
886
887 IF p_group_id IS NOT NULL THEN
888 l_stmt := l_stmt || ' AND group_id = '''|| p_group_id || ''' ';
889 END IF;
890
891 IF p_invoice_interface_id IS NULL THEN
892 l_stmt := l_stmt || ' AND NVL(invoice_type_lookup_code, ''STANDARD'') <> ''PAYMENT REQUEST'' ';
893 ELSE
894 l_stmt := l_stmt || ' AND invoice_id = ' || p_invoice_interface_id || ' ';
895 END IF;
896
897 IF p_org_id IS NULL THEN
898 l_stmt := l_stmt || ' AND ( (org_id IS NULL) OR (org_id IS NOT NULL AND mo_global.check_access(org_id)= ''Y'' ) ) ';
899 ELSE
900 l_stmt := l_stmt || ' AND ( (org_id IS NULL) OR (org_id IS NOT NULL AND org_id = ' || p_org_id || ') ) ';
901 END IF;
902
903 l_stmt := l_stmt || ' AND nvl(workflow_flag,''D'') = ''D'' ';
904
905 EXECUTE IMMEDIATE l_stmt BULK COLLECT INTO enums;
906
907 /* IF (p_group_id IS NULL) THEN
908 SELECT invoice_id
909 BULK COLLECT INTO enums
910 FROM ap_invoices_interface
911 WHERE ((status is NULL) or (status = 'REJECTED'))
912 AND source = p_source
913 AND ((p_invoice_interface_id IS NULL AND
914 NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
915 OR (invoice_id = p_invoice_interface_id))
916 AND nvl(workflow_flag,'D') = 'D'
917 AND ((org_id is NOT NULL AND
918 p_org_id is NOT NULL AND
919 org_id = p_org_id)
920 or (p_org_id is NULL AND
921 org_id is NOT NULL AND
922 (mo_global.check_access(org_id)= 'Y'))
923 or (p_org_id is NOT NULL AND
924 org_id is NULL)
925 or (p_org_id is NULL AND
926 org_id is NULL));
927 ELSE
928 SELECT invoice_id
929 BULK COLLECT INTO enums
930 FROM ap_invoices_interface
931 WHERE ((status is NULL) or (status = 'REJECTED'))
932 AND source = p_source
933 AND group_id = p_group_id
934 AND ((p_invoice_interface_id IS NULL AND
935 NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
936 OR (invoice_id = p_invoice_interface_id))
937 AND nvl(workflow_flag,'D') = 'D'
938 AND ((org_id is NOT NULL AND
939 p_org_id is NOT NULL AND
940 org_id = p_org_id)
941 or (p_org_id is NULL AND
942 org_id is NOT NULL AND
943 (mo_global.check_access(org_id)= 'Y'))
944 or (p_org_id is NOT NULL AND
945 org_id is NULL)
946 or (p_org_id is NULL AND
947 org_id is NULL));
948 END IF; */
949
950 /* BUG 8237318 - End modified the existing query to dynamic sql to skip the null checks
951 for passing parameters in the query. */
952 debug_info := '(Check_lines 3b) Delete invoices from ap_interface_rejections';
953
954 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
955 AP_IMPORT_UTILITIES_PKG.Print(
956 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
957 END IF;
958
959 -- Retropricing
960 --The PPA Rejections will be deleted from ap_interface_rejections
961 -- in the After Report Trigger of APXIIMPT.
962 IF enums.COUNT > 0 THEN
963
964 ForALL i IN enums.FIRST .. enums.LAST
965 DELETE FROM ap_interface_rejections
966 WHERE parent_table = 'AP_INVOICES_INTERFACE'
967 AND parent_id = enums(i);
968
969 ForALL i IN enums.FIRST .. enums.LAST
970 DELETE FROM ap_interface_rejections
971 WHERE parent_table = 'AP_INVOICE_LINES_INTERFACE'
972 AND parent_id IN (SELECT invoice_line_id
973 FROM ap_invoice_lines_interface
974 WHERE invoice_id = enums(i));
975 END IF;
976 --Start of Bug 6801046
977 debug_info := '(Check_lines 3c) Update requestid on the Selected Invoices';
978
979 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
980 AP_IMPORT_UTILITIES_PKG.Print(
981 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
982 END IF;
983
984 IF enums.COUNT > 0 THEN
985 ForALL i IN enums.FIRST .. enums.LAST
986 UPDATE AP_INVOICES_INTERFACE
987 SET request_id = AP_IMPORT_INVOICES_PKG.g_conc_request_id
988 WHERE invoice_id = enums(i);
989 END IF;
990 --End of Bug 6801046
991 ----------------------------------------------------------------
992 -- Step 4 Update the org_id whenever null IF operating unit
993 -- is not null.
994 ----------------------------------------------------------------
995 debug_info := '(Import Invoice 4) Update the org_id';
996 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
997 AP_IMPORT_UTILITIES_PKG.Print(
998 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
999 END IF;
1000
1001 debug_info := '(Import_Invoices 4a) Updating Interface WHERE org_id '||
1002 'is null but operating unit is not null';
1003 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1004 AP_IMPORT_UTILITIES_PKG.Print(
1005 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1006 END IF;
1007
1008
1009 --Bug 6839034 Added additional filters to the update below
1010 -- Coding 2 different update stmts based on p_group_id to improve performance
1011 /* BUG 8237318 - begin modified the existing query to dynamic sql to skip the null checks
1012 for passing parameters in the query. */
1013
1014 l_stmt := 'UPDATE ap_invoices_interface i '
1015 ||' SET org_id = (SELECT hr.organization_id org_id '
1016 ||' FROM hr_operating_units hr,per_business_groups per '
1017 ||' WHERE hr.business_group_id = per.business_group_id '
1018 /* Commented for bug 11871938 */
1019 /* ||' AND mo_global.check_access(hr.organization_id) = ''Y'' ' */
1020 ||' AND hr.name = i.operating_unit) '
1021 ||' WHERE i.org_id is null AND i.operating_unit is not null '
1022 ||' AND ((status is NULL) OR (status = ''REJECTED'')) AND source = ''' || p_source || ''' '
1023 ||' AND NVL(workflow_flag,''D'') = ''D'' ';
1024
1025
1026
1027 IF p_group_id IS NOT NULL THEN
1028 l_stmt := l_stmt || ' AND group_id = ''' || p_group_id || ''' ';
1029 END IF;
1030
1031 IF p_invoice_interface_id IS NULL THEN
1032 l_stmt := l_stmt || ' AND NVL(invoice_type_lookup_code, ''STANDARD'') <> ''PAYMENT REQUEST'' ';
1033 ELSE
1034 l_stmt := l_stmt || ' AND invoice_id = ' || p_invoice_interface_id || ' ';
1035 END IF;
1036
1037 EXECUTE IMMEDIATE l_stmt;
1038
1039 /* IF (p_group_id IS NULL) THEN
1040 UPDATE ap_invoices_interface i
1041 SET org_id = (SELECT hr.organization_id org_id
1042 FROM hr_operating_units hr,
1043 per_business_groups per
1044 WHERE hr.business_group_id = per.business_group_id
1045 AND mo_global.check_access(hr.organization_id) = 'Y'
1046 AND hr.name = i.operating_unit)
1047 WHERE i.org_id is null
1048 AND i.operating_unit is not null
1049 AND ((status is NULL) OR (status = 'REJECTED'))
1050 AND source = p_source
1051 AND ((p_invoice_interface_id IS NULL AND
1052 NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
1053 OR (invoice_id = p_invoice_interface_id))
1054 AND NVL(workflow_flag,'D') = 'D' ;
1055
1056 --Bug 6839034 Added ELSE part
1057 ELSE
1058
1059 UPDATE ap_invoices_interface i
1060 SET org_id = (SELECT hr.organization_id org_id
1061 FROM hr_operating_units hr,
1062 per_business_groups per
1063 WHERE hr.business_group_id = per.business_group_id
1064 AND mo_global.check_access(hr.organization_id) = 'Y'
1065 AND hr.name = i.operating_unit)
1066 WHERE i.org_id is null
1067 AND i.operating_unit is not null
1068 AND ((status is NULL) OR (status = 'REJECTED'))
1069 AND source = p_source
1070 AND group_id = p_group_id
1071 AND ((p_invoice_interface_id IS NULL AND
1072 NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
1073 OR (invoice_id = p_invoice_interface_id))
1074 AND NVL(workflow_flag,'D') = 'D' ;
1075
1076 END IF;
1077 */
1078 /* BUG 8237318 - END modified the existing query to dynamic sql to skip the null checks
1079 for passing parameters in the query. */
1080
1081 debug_info := '(Import_Invoices 4b) Getting Deafult Operating Unit '||
1082 'Information';
1083 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1084 AP_IMPORT_UTILITIES_PKG.Print(
1085 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1086 END IF;
1087
1088
1089 Mo_Utils.get_default_ou(
1090 l_default_org_id,
1091 l_default_ou_name,
1092 l_ou_count);
1093
1094 ----------------------------------------------------------------
1095 -- Step 5 Get number of invoices to process.
1096 ----------------------------------------------------------------
1097 debug_info := '(Import Invoices 5) Get The Total Number of Invoices '||
1098 'In Interface';
1099 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1100 AP_IMPORT_UTILITIES_PKG.Print(
1101 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1102 END IF;
1103
1104 -- Bug 4145391. To improve the performance of the import program coding two
1105 -- different select stmts based on the parameter p_group_id
1106 /* BUG 8237318 - Begin modified the existing query to dynamic sql to skip the null checks
1107 for passing parameters in the query. */
1108
1109 l_stmt := 'SELECT count(*) FROM ap_invoices_interface '
1110 ||' WHERE source = ''' || p_source || ''' '
1111 ||' AND ((status is NULL) or (status = ''REJECTED'')) ';
1112
1113
1114
1115 IF p_group_id IS NOT NULL THEN
1116 l_stmt := l_stmt || ' AND group_id = ''' || p_group_id || ''' ';
1117 END IF;
1118
1119 IF p_invoice_interface_id IS NULL THEN
1120 l_stmt := l_stmt || ' AND NVL(invoice_type_lookup_code, ''STANDARD'') <> ''PAYMENT REQUEST'' ';
1121 ELSE
1122 l_stmt := l_stmt || ' AND invoice_id = ' || p_invoice_interface_id || ' ';
1123 END IF;
1124
1125 --Bug13616489 - corrected the else part
1126 IF p_org_id IS NULL THEN
1127 l_stmt := l_stmt || ' AND ( (org_id IS NULL) OR (org_id IS NOT NULL AND mo_global.check_access(org_id)= ''Y'' ) ) ';
1128 ELSE
1129 --l_stmt := l_stmt || ' AND ( (org_id IS NULL) OR ( org_id = ' || p_org_id || ') ) ';
1130 l_stmt := l_stmt || ' AND ( (org_id IS NULL) OR (org_id IS NOT NULL AND org_id = ' || p_org_id || ') ) ';
1131 END IF;
1132
1133 --Bug13616489 - removed ROWNUM condition as l_total_count is used to get the commit cycles for which we need the total count of invoices.
1134 --l_stmt := l_stmt || ' AND nvl(workflow_flag,''D'') = ''D'' AND ROWNUM = 1 ';
1135 l_stmt := l_stmt || ' AND nvl(workflow_flag,''D'') = ''D'' ';
1136
1137 --Bug13616489 - added debug stmt
1138 debug_info := '(Import_Invoices 5a) Total Invoices l_stmt:'||l_stmt;
1139 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1140 AP_IMPORT_UTILITIES_PKG.Print(
1141 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1142 END IF;
1143
1144 BEGIN
1145 EXECUTE IMMEDIATE l_stmt INTO l_total_count;
1146
1147 IF (l_total_count = 0) THEN
1148 l_continue_flag := 'N';
1149 END IF;
1150
1151 --Bug13616489 - added debug stmt
1152 debug_info := '(Import_Invoices 4c) Total Invoices: l_total_count:'||l_total_count;
1153 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1154 AP_IMPORT_UTILITIES_PKG.Print(
1155 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1156 END IF;
1157 --Bug13616489 - added debug stmt
1158 debug_info := '(Import_Invoices 4d) l_continue_flag:'||l_continue_flag;
1159 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1160 AP_IMPORT_UTILITIES_PKG.Print(
1161 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1162 END IF;
1163
1164 EXCEPTION
1165 WHEN NO_DATA_FOUND THEN
1166 l_continue_flag := 'N';
1167 END;
1168
1169
1170 /* IF (p_group_id IS NULL) THEN
1171 BEGIN
1172 SELECT count(*)
1173 INTO l_total_count
1174 FROM ap_invoices_interface
1175 WHERE ((status is NULL) or (status = 'REJECTED'))
1176 AND source = p_source
1177 AND ((p_invoice_interface_id IS NULL AND
1178 NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
1179 OR (invoice_id = p_invoice_interface_id))
1180 AND nvl(workflow_flag,'D') = 'D'
1181 AND ((org_id is NOT NULL AND
1182 p_org_id is NOT NULL AND
1183 org_id = p_org_id)
1184 or (p_org_id is NULL AND
1185 org_id is NOT NULL AND
1186 (mo_global.check_access(org_id)= 'Y'))
1187 or (p_org_id is NOT NULL AND
1188 org_id is NULL)
1189 or (p_org_id is NULL AND
1190 org_id is NULL))
1191 AND ROWNUM = 1;
1192
1193 IF (l_total_count = 0) THEN
1194 l_continue_flag := 'N';
1195 END IF;
1196
1197 EXCEPTION
1198 WHEN NO_DATA_FOUND THEN
1199 l_continue_flag := 'N';
1200 END;
1201
1202 ELSE
1203 BEGIN
1204 SELECT count(*)
1205 INTO l_total_count
1206 FROM ap_invoices_interface
1207 WHERE ((status is NULL) or (status = 'REJECTED'))
1208 AND source = p_source
1209 AND group_id = p_group_id
1210 AND ((p_invoice_interface_id IS NULL AND
1211 NVL(invoice_type_lookup_code, 'STANDARD') <> 'PAYMENT REQUEST')
1212 OR (invoice_id = p_invoice_interface_id))
1213 AND nvl(workflow_flag,'D') = 'D'
1214 AND ((org_id is NOT NULL AND
1215 p_org_id is NOT NULL AND
1216 org_id = p_org_id)
1217 or (p_org_id is NULL AND
1218 org_id is NOT NULL AND
1219 (mo_global.check_access(org_id)= 'Y'))
1220 or (p_org_id is NOT NULL AND
1221 org_id is NULL)
1222 or (p_org_id is NULL AND
1223 org_id is NULL))
1224 AND ROWNUM = 1;
1225
1226 IF (l_total_count = 0) THEN
1227 l_continue_flag := 'N';
1228 END IF;
1229
1230 EXCEPTION
1231 WHEN NO_DATA_FOUND THEN
1232 l_continue_flag := 'N';
1233 END;
1234 END IF;
1235 */
1236
1237 /* BUG 8237318 - End modified the existing query to dynamic sql to skip the null checks
1238 for passing parameters in the query. */
1239
1240 -- Bug 5448579
1241 debug_info := '(Import_invoice 5.5) Unwinding Caching Org Id/Name';
1242 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1243 AP_IMPORT_UTILITIES_PKG.Print(
1244 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1245 END IF;
1246
1247 FOR i IN 1..g_moac_ou_tab.COUNT
1248 LOOP
1249 l_index_org_id := g_moac_ou_tab(i).org_id;
1250 l_moac_org_table(l_index_org_id).org_id := g_moac_ou_tab(i).org_id;
1251 l_moac_org_table(l_index_org_id).org_name := g_moac_ou_tab(i).org_name;
1252
1253 debug_info := 'Index Value: '||l_index_org_id
1254 ||', MOAC Cached Org_Id: '||l_moac_org_table(l_index_org_id).org_id
1255 ||', MOAC Cached Operating Unit: '|| l_moac_org_table(l_index_org_id).org_name;
1256
1257 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1258 AP_IMPORT_UTILITIES_PKG.Print(
1259 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1260 END IF;
1261 END LOOP;
1262
1263 FOR i IN 1..g_fsp_ou_tab.COUNT
1264 LOOP
1265 l_index_org_id := g_fsp_ou_tab(i).org_id;
1266 l_fsp_org_table(l_index_org_id).org_id := g_fsp_ou_tab(i).org_id;
1267
1268 debug_info := 'Index Value: '||l_index_org_id
1269 ||', FSP Cached Org_Id: '||l_fsp_org_table(l_index_org_id).org_id;
1270
1271 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1272 AP_IMPORT_UTILITIES_PKG.Print(
1273 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1274 END IF;
1275
1276 END LOOP;
1277
1278 ----------------------------------------------------------------
1279 -- Step 6 LOOP through invoices/Instructions(Retropricing)
1280 ----------------------------------------------------------------
1281 WHILE (l_continue_flag = 'Y') LOOP
1282
1283 --resetting the values for every loop
1284 l_valid_invoices_count :=0; --bug 14530013
1285 l_actual_invoice_total :=0; --bug 14530013
1286
1287 --bug 15862708 start
1288 tab_invoice_table.DELETE;
1289 tab_lines_table.DELETE;
1290 AP_IMPORT_VALIDATION_PKG.lg_cascade_rept_flag.DELETE;
1291
1292 --bug 15862708 ends
1293
1294 debug_info := '(Import_invoice 6) Open import_invoices cursor';
1295 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1296 AP_IMPORT_UTILITIES_PKG.Print(
1297 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1298 END IF;
1299
1300 -- Bug 4145391. To improve the performance of the import program coding two
1301 -- different cursors based on the parameter p_group_id
1302 IF (p_group_id IS NULL) THEN
1303 OPEN import_invoices;
1304 ELSE
1305 OPEN import_invoices_group;
1306 END IF;
1307
1308 debug_info := '(Import_invoice -->Execution started for first loop:'
1309 ||to_char(sysdate,'YY-MON-DD HH24:MI:SS');
1310
1311 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
1312 AP_IMPORT_UTILITIES_PKG.Print(
1313 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1314 END IF;
1315
1316 LOOP
1317 BEGIN --veramach bug 7121842
1318 -- Retropricing:
1319 -- Invoice/Instructions LOOP, cursor size always be less or equal to p_commit_cycle
1320 ---------------------------------------------------------------
1321 -- Step 7 FETCH invoice interface record INTO invoice record
1322 ---------------------------------------------------------------
1323
1324 debug_info := '(Import_invoice 7) FETCH import_invoices';
1325 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1326 AP_IMPORT_UTILITIES_PKG.Print(
1327 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1328 END IF;
1329
1330 -- Bug 4145391
1331 IF (p_group_id IS NULL) THEN
1332 FETCH import_invoices INTO l_invoice_rec;
1333 EXIT WHEN import_invoices%NOTFOUND
1334 OR import_invoices%NOTFOUND IS NULL;
1335 ELSE
1336 FETCH import_invoices_group INTO l_invoice_rec;
1337 EXIT WHEN import_invoices_group%NOTFOUND
1338 OR import_invoices_group%NOTFOUND IS NULL;
1339 END IF;
1340
1341 -- reset global variable for each invoice. bug 10158760
1342 --AP_IMPORT_INVOICES_PKG.g_inv_has_tax_line := 'N'; --bug 15862708
1343 l_invoice_rec.inv_has_tax_line :='N';
1344 --
1345 AP_IMPORT_INVOICES_PKG.g_inv_sysdate := TRUNC(sysdate);
1346
1347 -- Set invoice counter to get invoice count for fetched invoices
1348
1349 l_invoices_fetched := l_invoices_fetched + 1;
1350 -- show output values (only IF debug_switch = 'Y')
1351
1352 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1353 AP_IMPORT_UTILITIES_PKG.Print(
1354 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1355 '------------------> invoice_id = '
1356 ||to_char(l_invoice_rec.invoice_id)
1357 ||' invoice_num = ' ||l_invoice_rec.invoice_num
1358 ||' invoice_type_lookup_code = '
1359 || l_invoice_rec.invoice_type_lookup_code
1360 ||' invoice_date = ' ||to_char(l_invoice_rec.invoice_date)
1361 ||' po_number = ' ||l_invoice_rec.po_number
1362 ||' vendor_id = ' ||to_char(l_invoice_rec.vendor_id)
1363 ||' vendor_num = ' ||l_invoice_rec.vendor_num
1364 ||' vendor_name = ' ||l_invoice_rec.vendor_name
1365 ||' vendor_site_id = ' ||to_char(l_invoice_rec.vendor_site_id)
1366 ||' vendor_site_code = ' ||l_invoice_rec.vendor_site_code
1367 ||' party_id = ' ||to_char(l_invoice_rec.party_id)
1368 ||' party_site_id = ' ||to_char(l_invoice_rec.party_site_id)
1369 ||' pay_proc_trxn_type_code = ' ||l_invoice_rec.pay_proc_trxn_type_code
1370 ||' payment_function = ' ||l_invoice_rec.payment_function
1371 ||' invoice_amount = ' ||to_char(l_invoice_rec.invoice_amount)
1372 ||' base_currency_code = ' ||l_base_currency_code
1373 ||' invoice_currency_code = '||l_invoice_rec.invoice_currency_code
1374 ||' payment_currency_code = '||l_invoice_rec.payment_currency_code
1375 ||' exchange_rate = ' ||to_char(l_invoice_rec.exchange_rate)
1376 ||' exchange_rate_type = '||l_invoice_rec.exchange_rate_type
1377 ||' exchange_date = ' ||to_char(l_invoice_rec.exchange_date)
1378 ||' terms_id = ' ||to_char(l_invoice_rec.terms_id)
1379 ||' terms_name = ' ||l_invoice_rec.terms_name
1380 ||' description = ' ||l_invoice_rec.description
1381 ||' awt_group_id = ' ||to_char(l_invoice_rec.awt_group_id)
1382 ||' awt_group_name = ' ||l_invoice_rec.awt_group_name
1383 ||' pay_awt_group_id = ' ||to_char(l_invoice_rec.pay_awt_group_id)
1384 ||' pay_awt_group_name = ' ||l_invoice_rec.pay_awt_group_name --bug6639866
1385 ||' last_update_date = ' ||to_char(l_invoice_rec.last_update_date)
1386 ||' last_updated_by = ' ||to_char(l_invoice_rec.last_updated_by)
1387 ||' last_update_login = '||to_char(l_invoice_rec.last_update_login)
1388 ||' creation_date = ' ||to_char(l_invoice_rec.creation_date)
1389 ||' attribute_category = '||l_invoice_rec.attribute_category
1390 ||' attribute1 = ' ||l_invoice_rec.attribute1
1391 ||' attribute2 = ' ||l_invoice_rec.attribute2
1392 ||' attribute3 = ' ||l_invoice_rec.attribute3
1393 ||' attribute4 = ' ||l_invoice_rec.attribute4
1394 ||' attribute5 = ' ||l_invoice_rec.attribute5
1395 ||' attribute6 = ' ||l_invoice_rec.attribute6
1396 ||' attribute7 = ' ||l_invoice_rec.attribute7
1397 ||' attribute8 = ' ||l_invoice_rec.attribute8
1398 ||' attribute9 = ' ||l_invoice_rec.attribute9
1399 ||' attribute10 = ' ||l_invoice_rec.attribute10
1400 ||' attribute11 = ' ||l_invoice_rec.attribute11
1401 ||' attribute12 = ' ||l_invoice_rec.attribute12
1402 ||' attribute13 = ' ||l_invoice_rec.attribute13
1403 ||' attribute14 = ' ||l_invoice_rec.attribute14
1404 ||' attribute15 = ' ||l_invoice_rec.attribute15
1405 ||' global_attribute_category = '
1406 || l_invoice_rec.global_attribute_category
1407 ||' global_attribute1 = ' ||l_invoice_rec.global_attribute1
1408 ||' global_attribute2 = ' ||l_invoice_rec.global_attribute2
1409 ||' global_attribute3 = ' ||l_invoice_rec.global_attribute3
1410 ||' global_attribute4 = ' ||l_invoice_rec.global_attribute4
1411 ||' global_attribute5 = ' ||l_invoice_rec.global_attribute5
1412 ||' global_attribute6 = ' ||l_invoice_rec.global_attribute6
1413 ||' global_attribute7 = ' ||l_invoice_rec.global_attribute7
1414 ||' global_attribute8 = ' ||l_invoice_rec.global_attribute8
1415 ||' global_attribute9 = ' ||l_invoice_rec.global_attribute9
1416 ||' global_attribute10 = '||l_invoice_rec.global_attribute10
1417 ||' global_attribute11 = '||l_invoice_rec.global_attribute11
1418 ||' global_attribute12 = '||l_invoice_rec.global_attribute12
1419 ||' global_attribute13 = '||l_invoice_rec.global_attribute13
1420 ||' global_attribute14 = '||l_invoice_rec.global_attribute14
1421 ||' global_attribute15 = '||l_invoice_rec.global_attribute15
1422 ||' global_attribute16 = '||l_invoice_rec.global_attribute16
1423 ||' global_attribute17 = '||l_invoice_rec.global_attribute17
1424 ||' global_attribute18 = '||l_invoice_rec.global_attribute18
1425 ||' global_attribute19 = '||l_invoice_rec.global_attribute19
1426 ||' global_attribute20 = '||l_invoice_rec.global_attribute20
1427 ||' doc_category_code = '||l_invoice_rec.doc_category_code
1428 ||' voucher_num = ' ||l_invoice_rec.voucher_num
1429 ||' payment_method_code = '
1430 || l_invoice_rec.payment_method_code
1431 ||' pay_group_lookup_code = '||l_invoice_rec.pay_group_lookup_code
1432 ||' goods_received_date = '
1433 || to_char(l_invoice_rec.goods_received_date)
1434 ||' invoice_received_date = '
1435 || to_char(l_invoice_rec.invoice_received_date)
1436 ||' exclusive_payment_flag = '
1437 || l_invoice_rec.exclusive_payment_flag
1438 ||' prepay_num = ' ||l_invoice_rec.prepay_num
1439 ||' prepay_line_num = ' ||l_invoice_rec.prepay_line_num
1440 ||' prepay_apply_amount = '||l_invoice_rec.prepay_apply_amount
1441 ||' prepay_gl_date = ' ||l_invoice_rec.prepay_gl_date
1442 ||' set_of_books_id = '||l_invoice_rec.set_of_books_id
1443 ||' legal_entity_id = '||l_invoice_rec.legal_entity_id
1444 ||' tax_only_flag = '||l_invoice_rec.tax_only_flag
1445 ||' tax_only_rcv_matched_flag = '||l_invoice_rec.tax_only_rcv_matched_flag
1446 --Third Party Payments
1447 ||' remit_to_supplier_name = '||l_invoice_rec.remit_to_supplier_name
1448 ||' remit_to_supplier_id = '||l_invoice_rec.remit_to_supplier_id
1449 ||' remit_to_supplier_site = '||l_invoice_rec.remit_to_supplier_site
1450 ||' remit_to_supplier_site_id = '||l_invoice_rec.remit_to_supplier_site_id
1451 ||' relationship_id = '||l_invoice_rec.relationship_id
1452 ||' remit_to_supplier_num = '||l_invoice_rec.remit_to_supplier_num
1453 );
1454 END IF;
1455
1456 ---------------------------------------------------------------
1457 -- Step 8 Check for inconsistent OU
1458 ----------------------------------------------------------------
1459 debug_info := '(Import Invoices 8) Checking for Inconsistent OU';
1460 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1461 AP_IMPORT_UTILITIES_PKG.Print(
1462 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1463 END IF;
1464
1465 IF l_invoice_rec.org_id is NULL THEN
1466
1467 IF (l_ou_count > 1 AND p_org_id is NULL) THEN
1468 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections
1469 (AP_IMPORT_INVOICES_PKG.g_invoices_table,
1470 l_invoice_rec.invoice_id,
1471 'NO OPERATING UNIT',
1472 l_default_last_updated_by,
1473 l_default_last_update_login,
1474 current_calling_sequence) <> TRUE) THEN
1475
1476 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1477 AP_IMPORT_UTILITIES_PKG.Print(
1478 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1479 'insert_rejections<- '||current_calling_sequence);
1480 END IF;
1481
1482 Raise import_invoice_failure;
1483 END IF; --Insert rejections
1484
1485 l_invoice_status := 'N';
1486 l_null_org_id := TRUE;
1487
1488 ELSIF (l_ou_count = 1 AND p_org_id is NULL) THEN
1489
1490 UPDATE ap_invoices_interface
1491 SET org_id = l_default_org_id
1492 WHERE invoice_id = l_invoice_rec.invoice_id ;
1493
1494 l_invoice_rec.org_id := l_default_org_id;
1495 l_invoice_status := 'Y';
1496 l_null_org_id := TRUE;
1497
1498 ELSIF (p_org_id is NOT NULL) THEN
1499
1500 UPDATE ap_invoices_interface
1501 SET org_id = p_org_id
1502 WHERE invoice_id = l_invoice_rec.invoice_id ;
1503
1504 l_invoice_rec.org_id := p_org_id;
1505 l_invoice_status := 'Y';
1506 l_null_org_id := TRUE;
1507
1508 END IF; -- OU count AND p_org_id
1509
1510 /* Added for bug 11655050 */
1511 /* Added Invoice status validation for bug 11838897 */
1512 IF (l_invoice_rec.operating_unit IS NOT NULL
1513 AND l_invoice_rec.org_id IS NOT NULL) THEN
1514
1515 l_derived_operating_unit := l_moac_org_table(l_invoice_rec.org_id).org_name;
1516 debug_info := 'When org_id of invoice is null, Derived Operating Unit: '||l_derived_operating_unit;
1517 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1518 AP_IMPORT_UTILITIES_PKG.Print(
1519 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1520 END IF;
1521
1522 IF l_invoice_rec.operating_unit <> l_derived_operating_unit THEN
1523 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections
1524 (AP_IMPORT_INVOICES_PKG.g_invoices_table,
1525 l_invoice_rec.invoice_id,
1526 'INCONSISTENT OPERATING UNITS',
1527 l_default_last_updated_by,
1528 l_default_last_update_login,
1529 current_calling_sequence) <> TRUE) THEN
1530
1531 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1532 AP_IMPORT_UTILITIES_PKG.Print(
1533 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1534 'insert_rejections<- '||current_calling_sequence);
1535 END IF;
1536
1537 Raise import_invoice_failure;
1538 END IF; -- Insert rejections
1539
1540 l_invoice_status := 'N';
1541 l_null_org_id := FALSE;
1542
1543 ELSE -- operating units are consistent
1544 l_invoice_status := 'Y';
1545 l_null_org_id := FALSE;
1546
1547 END IF;
1548 END IF; -- operating_unit is not null
1549 /* Addition for bug 11655050 ends */
1550
1551
1552 ELSE -- invoice_rec.org_id is not null
1553
1554 /* Following block is for bug 5140002 */
1555 /* BEGIN
1556
1557 SELECT org_id
1558 INTO l_option_defined_org
1559 FROM financials_system_parameters
1560 WHERE org_id = l_invoice_rec.org_id;
1561
1562 EXCEPTION
1563 WHEN NO_DATA_FOUND THEN
1564 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections
1565 (AP_IMPORT_INVOICES_PKG.g_invoices_table,
1566 l_invoice_rec.invoice_id,
1567 'UNDEFINED OPERATING UNIT',
1568 l_default_last_updated_by,
1569 l_default_last_update_login,
1570 current_calling_sequence) <> TRUE) THEN
1571
1572 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1573 AP_IMPORT_UTILITIES_PKG.Print(
1574 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1575 'insert_rejections<- '||current_calling_sequence);
1576 END IF;
1577 Raise import_invoice_failure;
1578 END IF; -- Insert rejections
1579
1580 l_invoice_status := 'N';
1581 l_null_org_id := FALSE;
1582
1583 END ; */
1584
1585 -- Big 5448579. Replace the above lock
1586 IF l_fsp_org_table.exists(l_invoice_rec.org_id) THEN
1587 Null;
1588
1589 ELSE
1590
1591 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections
1592 (AP_IMPORT_INVOICES_PKG.g_invoices_table,
1593 l_invoice_rec.invoice_id,
1594 'UNDEFINED OPERATING UNIT',
1595 l_default_last_updated_by,
1596 l_default_last_update_login,
1597 current_calling_sequence) <> TRUE) THEN
1598
1599 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1600 AP_IMPORT_UTILITIES_PKG.Print(
1601 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1602 'insert_rejections<- '||current_calling_sequence);
1603 END IF;
1604 RAISE import_invoice_failure;
1605 END IF; -- Insert rejections
1606
1607 l_invoice_status := 'N';
1608 l_null_org_id := FALSE;
1609
1610 END IF;
1611
1612 IF l_invoice_rec.operating_unit is NOT NULL THEN
1613 -- Bug 5448579
1614 -- l_derived_operating_unit :=
1615 -- mo_global.get_ou_name(l_invoice_rec.org_id);
1616 l_derived_operating_unit := l_moac_org_table(l_invoice_rec.org_id).org_name;
1617
1618 debug_info := ' Derived Operating Unit: '||l_derived_operating_unit;
1619 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1620 AP_IMPORT_UTILITIES_PKG.Print(
1621 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1622 END IF;
1623
1624 IF l_invoice_rec.operating_unit <> l_derived_operating_unit THEN
1625 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections
1626 (AP_IMPORT_INVOICES_PKG.g_invoices_table,
1627 l_invoice_rec.invoice_id,
1628 'INCONSISTENT OPERATING UNITS',
1629 l_default_last_updated_by,
1630 l_default_last_update_login,
1631 current_calling_sequence) <> TRUE) THEN
1632
1633 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1634 AP_IMPORT_UTILITIES_PKG.Print(
1635 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1636 'insert_rejections<- '||current_calling_sequence);
1637 END IF;
1638
1639 Raise import_invoice_failure;
1640 END IF; -- Insert rejections
1641
1642 l_invoice_status := 'N';
1643 l_null_org_id := FALSE;
1644
1645 ELSE -- operating units are consistent
1646
1647 l_invoice_status := 'Y';
1648 l_null_org_id := FALSE;
1649
1650 END IF;
1651
1652 ELSE -- operating unit name was null in invoice rec
1653
1654 l_invoice_status := 'Y';
1655 l_null_org_id := FALSE;
1656
1657 END IF;
1658
1659 END IF; -- invoice rec org id is null
1660
1661
1662 -----------------------------------------------------
1663 -- Step 9 Set the org context AND cache lookup codes
1664 -- AND parameters. IF batch control enabled, get batch
1665 -- id IF org id has changed.
1666 -----------------------------------------------------
1667 debug_info := '(Import Invoice 9a) Setting the org Context';
1668 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1669 AP_IMPORT_UTILITIES_PKG.Print(
1670 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1671 END IF;
1672
1673 -- Commented l_invoice_status condition for Bug 9452076.
1674 -- AP_IMPORT_UTILITIES_PKG.get_info needs to be invoked to fetch
1675 -- the setup information even though org exception exists.
1676 --IF l_invoice_status = 'Y' THEN
1677 IF l_invoice_rec.org_id <> NVL(l_old_org_id, -3115) THEN
1678 Mo_Global.set_policy_context('S', l_invoice_rec.org_id);
1679
1680 -- bug7531219 setting the ledger context to get only
1681 -- valid Balancing and Management segments for the ledger
1682 BEGIN
1683 GL_GLOBAL.set_aff_validation(context_type => 'OU',
1684 context_id => l_invoice_rec.org_id);
1685 EXCEPTION
1686 WHEN OTHERS THEN
1687 NULL;
1688 END;
1689
1690 debug_info := '(Import_invoice 9b) Call get_info to get '||
1691 'required info';
1692 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1693 AP_IMPORT_UTILITIES_PKG.Print(
1694 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1695 END IF;
1696 -- bug 15862708 commented the below procedure and added new procedure which will return
1697 -- record type as output instead of local variables
1698 /*
1699 IF (AP_IMPORT_UTILITIES_PKG.get_info(
1700 l_invoice_rec.org_id, -- IN
1701 l_set_of_books_id, -- OUT NOCOPY
1702 l_multi_currency_flag, -- OUT NOCOPY
1703 l_make_rate_mandatory_flag, -- OUT NOCOPY
1704 l_default_exchange_rate_type, -- OUT NOCOPY
1705 l_base_currency_code, -- OUT NOCOPY
1706 l_batch_control_flag, -- OUT NOCOPY
1707 l_invoice_currency_code, -- OUT NOCOPY
1708 l_base_min_acct_unit, -- OUT NOCOPY
1709 l_base_precision, -- OUT NOCOPY
1710 l_sequence_numbering, -- OUT NOCOPY
1711 l_awt_include_tax_amt, -- OUT NOCOPY
1712 l_gl_date_from_get_info, -- IN OUT NOCOPY
1713 -- l_ussgl_transcation_code, -- OUT NOCOPY -Bug 4277744
1714 l_transfer_po_desc_flex_flag, -- OUT NOCOPY
1715 l_gl_date_from_receipt_flag, -- OUT NOCOPY
1716 l_purch_encumbrance_flag, -- OUT NOCOPY
1717 l_retainage_ccid, -- OUT NOCOPY
1718 l_pa_installed, -- OUT NOCOPY
1719 l_chart_of_accounts_id, -- OUT NOCOPY
1720 l_inv_doc_cat_override, -- OUT NOCOPY
1721 l_calc_user_xrate, -- OUT NOCOPY
1722 current_calling_sequence,
1723 l_approval_workflow_flag, -- OUT NOCOPY
1724 l_freight_code_combination_id, -- OUT NOCOPY
1725 l_allow_interest_invoices, -- OUT NOCOPY
1726 l_add_days_settlement_date, -- OUT NOCOPY --bug4930111
1727 l_disc_is_inv_less_tax_flag, -- OUT NOCOPY --bug4931755
1728 AP_IMPORT_INVOICES_PKG.g_source, -- IN --bug5382889 LE TimeZone
1729 l_invoice_rec.invoice_date, -- IN --bug5382889 LE TimeZone
1730 l_invoice_rec.goods_received_date, -- IN --bug5382889 LE TimeZone
1731 l_asset_book_type -- OUT NOCOPY --Bug 5448579
1732 ) <> TRUE) THEN
1733 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1734 AP_IMPORT_UTILITIES_PKG.Print(
1735 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1736 'get_info<-'||current_calling_sequence);
1737 END IF;
1738 Raise import_invoice_failure;
1739 END IF;
1740 */
1741 --bug 15862708 replaced above proc with the below one
1742 IF (AP_IMPORT_INVOICES_PKG.get_info_1 (
1743 l_invoice_rec.org_id , --IN
1744 l_gl_date_from_get_info , --IN OUT
1745 p_calling_sequence , --IN
1746 p_source , --IN --bug 5382889. LE TimeZone
1747 l_invoice_rec.invoice_date , --IN -- bug 5382889. LE TimeZone
1748 l_invoice_rec.goods_received_date, --IN -- bug 5382889. LE TimeZone
1749 l_get_info_rec -- OUT
1750 ) <> TRUE) THEN
1751 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1752 AP_IMPORT_UTILITIES_PKG.Print(
1753 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1754 'get_info<-'||current_calling_sequence);
1755 END IF;
1756 RAISE import_invoice_failure;
1757 END IF;
1758 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1759 AP_IMPORT_UTILITIES_PKG.Print(
1760 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1761 '------------------> '
1762 ||' p_org_id = '||to_char(l_invoice_rec.org_id)
1763 ||' p_set_of_books_id = '|| to_char(l_set_of_books_id)
1764 ||' l_multi_currency_flag = '||l_multi_currency_flag
1765 ||' l_make_rate_mANDatory_flag = '||l_make_rate_mandatory_flag
1766 ||' l_default_exchange_rate_type = '
1767 || l_default_exchange_rate_type
1768 ||' l_base_currency_code = ' ||l_base_currency_code
1769 ||' l_batch_control_flag = ' ||l_batch_control_flag
1770 ||' l_payment_cross_rate = '
1771 || to_char(l_invoice_rec.payment_cross_rate)
1772 ||' l_base_min_acct_unit = ' ||to_char(l_base_min_acct_unit)
1773 ||' l_base_precision = ' ||to_char(l_base_precision)
1774 ||' l_sequence_numbering = ' ||l_sequence_numbering
1775 ||' l_awt_include_tax_amt = ' ||l_awt_include_tax_amt
1776 ||' l_gl_date_from_get_info = ' ||to_char(l_gl_date_from_get_info)
1777 -- Removed for bug 4277744
1778 -- ||' l_ussgl_transcation_code = '||l_ussgl_transcation_code
1779 ||' l_gl_date_from_receipt_flag = '||l_get_info_rec.p_gl_date_from_receipt_flag
1780 ||' l_purch_encumbrance_flag = '||l_purch_encumbrance_flag
1781 ||' l_chart_of_accounts_id = ' ||to_char(l_chart_of_accounts_id)
1782 ||' l_pa_installed = ' ||l_pa_installed
1783 ||' l_positive_price_tolerance = '
1784 || to_char(l_positive_price_tolerance)
1785 ||' l_negative_price_tolerance = '
1786 || to_char(l_negative_price_tolerance)
1787 ||' l_qty_tolerance = ' ||to_char(l_qty_tolerance)
1788 ||' l_max_qty_ord_tolerance = ' ||to_char(l_max_qty_ord_tolerance)
1789 ||' l_inv_doc_cat_override = ' ||l_inv_doc_cat_override
1790 ||' l_allow_interest_invoices = ' ||l_allow_interest_invoices);
1791 END IF;
1792
1793 -- Retek Integration bug 6349739
1794 IF AP_IMPORT_INVOICES_PKG.g_source = 'RETEK' THEN
1795 -- get the segment delimiter
1796 AP_IMPORT_INVOICES_PKG.g_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
1797 'SQLGL',
1798 'GL#',
1799 l_chart_of_accounts_id);
1800 END IF;
1801
1802 --------------------------------------------------------
1803 -- Step 9c
1804 -- Get batch_id first IF batch_control is on
1805 -- This batch_id is for the Invoice Batch Name
1806 -- Retropricing: It seems the get_batch_id will be called
1807 -- again and again for new batches creating a gap in the
1808 -- batch sequence
1809 --------------------------------------------------------
1810 l_batch_control_flag :=l_get_info_rec.p_batch_control_flag;
1811
1812 IF (NVL(l_batch_control_flag,'N') = 'Y'
1813 AND l_batch_id IS NULL /* Added for bug#7294733 */
1814 )
1815 THEN
1816 debug_info := '(Import_invoice 9c) Get batch_id IF '||
1817 'batch_control is on';
1818 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1819 AP_IMPORT_UTILITIES_PKG.Print(
1820 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1821 END IF;
1822
1823 IF (AP_IMPORT_UTILITIES_PKG.get_batch_id(
1824 p_batch_name,
1825 l_batch_id,
1826 l_batch_type,
1827 current_calling_sequence) <> TRUE) THEN
1828
1829 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1830 AP_IMPORT_UTILITIES_PKG.Print(
1831 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1832 'get_batch_id<-'||current_calling_sequence);
1833 END IF;
1834
1835 /*Added following code to raise fatal error in case when
1836 import program is executed for duplicated batch name for
1837 Bug 16554255*/
1838
1839 IF ( l_batch_id is NULL ) THEN
1840 p_batch_error_flag := 'Y';
1841 RETURN(TRUE);
1842 END IF;
1843
1844 /* RAISE import_invoice_failure; */ /*Commented for bug 16554255*/
1845 END IF;
1846 --
1847 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1848 AP_IMPORT_UTILITIES_PKG.Print(
1849 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1850 '------------------> l_batch_id = ' || to_char(l_batch_id)
1851 ||' l_batch_type = '||l_batch_type);
1852 END IF;
1853
1854 ----------------------------------------------------------------
1855 -- IF there is no batch id AND batch control is turned on
1856 -- batch error is raised AND STOP PROCESSING. Fatal error message
1857 -- should be Printed on the report in this case.We do not have
1858 -- a reject code in this case.
1859 ----------------------------------------------------------------
1860 IF ( l_batch_id is NULL ) THEN
1861 p_batch_error_flag := 'Y';
1862 RETURN(TRUE);
1863 ELSE
1864 p_print_batch := 'Y';
1865 END IF;
1866
1867 END IF; -- NVL(l_batch_control_flag,'N') = 'Y'
1868 END IF; -- org id is <> old org id
1869 --END IF; -- invoice status = Y. Commented for bug 9452076.
1870
1871
1872 ----------------------------------------------------------------
1873 -- Retropricing: IF source = 'PPA' Go to Step 16.
1874 ----------------------------------------------------------------
1875 IF AP_IMPORT_INVOICES_PKG.g_source <> 'PPA' THEN
1876 --
1877 -----------------------------------------------------
1878 -- Step 10 Get GL Date
1879 -----------------------------------------------------
1880 --
1881 IF (l_invoice_rec.gl_date is NOT NULL) THEN
1882 debug_info := '(Import Invoice 10a) Default GL Date From Invoice ';
1883 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1884 AP_IMPORT_UTILITIES_PKG.Print(
1885 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1886 END IF;
1887 ELSE
1888 debug_info := '(Import Invoice 10b) Default GL Date Based on Calculation ';
1889 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1890 AP_IMPORT_UTILITIES_PKG.Print(
1891 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1892 END IF;
1893
1894 -- Bug 5654581. Moving Gl_Date Related code here
1895 /* Added AND condition to following IF for bug 9804420 */
1896 IF (AP_IMPORT_INVOICES_PKG.g_source = 'ERS'
1897 AND p_gl_date IS NULL) THEN -- bug 5382889, LE TimeZone
1898 debug_info := 'Determine gl_date for ERS invoice';
1899
1900 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1901 AP_IMPORT_UTILITIES_PKG.Print
1902 (AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
1903 END IF;
1904
1905 l_rts_txn_le_date := INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Ou(
1906 p_trxn_date => nvl(l_invoice_rec.goods_received_date,
1907 l_invoice_rec.invoice_date)
1908 ,p_ou_id => l_invoice_rec.org_id);
1909
1910 l_inv_le_date := INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Ou(
1911 p_trxn_date => l_invoice_rec.invoice_date
1912 ,p_ou_id => l_invoice_rec.org_id);
1913
1914 l_sys_le_date := INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Ou(
1915 p_trxn_date => sysdate
1916 ,p_ou_id => l_invoice_rec.org_id);
1917
1918
1919 /* The gl_date id determined from the flag gl_date_from_receipt_flag
1920 If the flag = 'I' -- take Invoice_date
1921 = 'S' -- take System date
1922 = 'N' -- take nvl(receipt_date, invoice_date)
1923 = 'Y' -- take nvl(receipt_date, sysdate)
1924 Note here that the Invoice date is no longer the same as the receipt_date,
1925 i.e. the RETURN tranasaction_date , so case I and N are no longer the same */
1926
1927 debug_info := 'Determine invoice gl_date from LE Timezone API ';
1928 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1929 AP_IMPORT_UTILITIES_PKG.Print
1930 (AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
1931 END IF;
1932
1933 IF (l_get_info_rec.p_gl_date_from_receipt_flag = 'I') THEN
1934 l_inv_gl_date := l_inv_le_date;
1935 ELSIF (l_get_info_rec.p_gl_date_from_receipt_flag = 'N') THEN
1936 l_inv_gl_date := nvl(l_rts_txn_le_date, l_inv_le_date);
1937 ELSIF (l_get_info_rec.p_gl_date_from_receipt_flag = 'S') THEN
1938 l_inv_gl_date := l_sys_le_date;
1939 ELSIF (l_get_info_rec.p_gl_date_from_receipt_flag = 'Y') THEN
1940 l_inv_gl_date := nvl(l_rts_txn_le_date, l_sys_le_date);
1941 END IF;
1942
1943 l_invoice_rec.gl_date := l_inv_gl_date;
1944
1945 ELSE
1946
1947 IF p_gl_date IS NULL THEN
1948
1949 IF (l_get_info_rec.p_gl_date_from_receipt_flag IN ('S', 'Y')) THEN
1950 debug_info := ' GL Date is Sysdate based on gl_date_reciept_flaf option';
1951 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1952 AP_IMPORT_UTILITIES_PKG.Print
1953 (AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1954 END IF;
1955
1956 l_invoice_rec.gl_date := AP_IMPORT_INVOICES_PKG.g_inv_sysdate;
1957
1958 ELSE
1959
1960 IF l_invoice_rec.invoice_date is NOT NULL THEN
1961 debug_info := ' GL Date is Invoice Date';
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 /* l_invoice_rec.gl_date := l_invoice_rec.invoice_date; Commented for bug#12356854 */
1968 /* Added for bug#12356854 Start */
1969 ap_utilities_pkg.get_gl_date_and_period_1
1970 ( l_invoice_rec.invoice_date,
1971 NULL,
1972 l_period_name,
1973 l_invoice_rec.gl_date,
1974 NULL,
1975 l_invoice_rec.org_id
1976 );
1977 /* Added for bug#12356854 End */
1978
1979 ELSE
1980 debug_info := ' GL Date is Sysdate Date';
1981 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1982 AP_IMPORT_UTILITIES_PKG.Print
1983 (AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1984 END IF;
1985
1986 l_invoice_rec.gl_date := AP_IMPORT_INVOICES_PKG.g_inv_sysdate;
1987 END IF;
1988
1989 END IF;
1990
1991 ELSE
1992
1993 debug_info := ' GL Date is Parameter Gl Date';
1994 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1995 AP_IMPORT_UTILITIES_PKG.Print
1996 (AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
1997 END IF;
1998
1999 l_invoice_rec.gl_date := p_gl_date;
2000 END IF;
2001
2002 END IF; --- end g_source = 'ERS'
2003
2004 /*
2005 IF l_gl_date_from_get_info is NULL THEN
2006 IF l_invoice_rec.invoice_date is NOT NULL THEN
2007 l_invoice_rec.gl_date := l_invoice_rec.invoice_date;
2008 ELSE
2009 l_invoice_rec.gl_date := AP_IMPORT_INVOICES_PKG.g_inv_sysdate;
2010 END IF;
2011 ELSIF l_gl_date_from_get_info is NOT NULL THEN
2012 l_invoice_rec.gl_date := l_gl_date_from_get_info;
2013 END IF; */
2014 END IF;
2015
2016 l_invoice_rec.invoice_date := TRUNC(l_invoice_rec.invoice_date);
2017 l_invoice_rec.gl_date := TRUNC(l_invoice_rec.gl_date);
2018
2019 -- For bug 2984396. Added by LGOPALSA.
2020 -- Added trunc for all date variables.
2021
2022 If l_invoice_rec.exchange_date is not null Then
2023 l_invoice_rec.exchange_date :=
2024 trunc(l_invoice_rec.exchange_date);
2025 End if;
2026
2027 If l_invoice_rec.goods_received_date is not null Then
2028 l_invoice_rec.goods_received_date :=
2029 trunc(l_invoice_rec.goods_received_date);
2030 End if;
2031
2032 If l_invoice_rec.invoice_received_date is not null Then
2033 l_invoice_rec.invoice_received_date :=
2034 trunc(l_invoice_rec.invoice_received_date);
2035 End if;
2036
2037 If l_invoice_rec.terms_date is not null Then
2038 l_invoice_rec.terms_date := trunc(l_invoice_rec.terms_date);
2039 End if;
2040
2041 -- End for bug2984396.
2042
2043 ----------------------------
2044 -- Step 11
2045 -- Validate invoice level
2046 ----------------------------
2047
2048 debug_info := '(Import_invoice 11) Validate invoice ';
2049 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2050 AP_IMPORT_UTILITIES_PKG.Print(
2051 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2052 END IF;
2053 AP_IMPORT_UTILITIES_PKG.Print(
2054 AP_IMPORT_INVOICES_PKG.g_debug_switch, 'SOBID is :'||
2055 l_invoice_rec.set_of_books_id);
2056
2057
2058 IF (AP_IMPORT_VALIDATION_PKG.v_check_invoice_validation
2059 (l_invoice_rec, -- IN OUT
2060 l_match_mode, -- OUT
2061 l_min_acct_unit, -- OUT
2062 l_precision, -- OUT
2063 l_positive_price_tolerance, -- OUT
2064 l_negative_price_tolerance, -- OUT
2065 l_qty_tolerance, -- OUT
2066 l_qty_rec_tolerance, -- OUT
2067 l_max_qty_ord_tolerance, -- OUT
2068 l_max_qty_rec_tolerance, -- OUT
2069 l_amt_tolerance, -- OUT
2070 l_amt_rec_tolerance, -- OUT
2071 l_max_amt_ord_tolerance, -- OUT
2072 l_max_amt_rec_tolerance, -- OUT
2073 l_goods_ship_amt_tolerance, -- OUT
2074 l_goods_rate_amt_tolerance, -- OUT
2075 l_goods_total_amt_tolerance, -- OUT
2076 l_services_ship_amt_tolerance, -- OUT
2077 l_services_rate_amt_tolerance, -- OUT
2078 l_services_total_amt_tolerance, -- OUT
2079 --bug 15862708 local variables changed to record type columns
2080 /* l_base_currency_code, -- IN
2081 l_multi_currency_flag, -- IN
2082 l_set_of_books_id, -- IN
2083 l_default_exchange_rate_type, -- IN
2084 l_make_rate_mandatory_flag, -- IN */
2085 l_get_info_rec.p_base_currency_code, -- IN
2086 l_get_info_rec.p_multi_currency_flag, -- IN
2087 l_get_info_rec.p_set_of_books_id, -- IN
2088 l_get_info_rec.p_default_exchange_rate_type, -- IN
2089 l_get_info_rec.p_make_rate_mandatory_flag, -- IN
2090 l_default_last_updated_by, -- IN
2091 l_default_last_update_login, -- IN
2092 l_fatal_error_flag, -- OUT
2093 l_invoice_status, -- OUT
2094 --l_calc_user_xrate, -- IN --bug 15862708
2095 l_get_info_rec.p_calc_user_xrate, -- IN
2096 l_prepay_period_name, -- IN OUT
2097 l_prepay_invoice_id, -- OUT --Contract Payments
2098 l_prepay_case_name, -- OUT --Contract Payments
2099 p_conc_request_id,
2100 --l_allow_interest_invoices, -- IN --bug 15862708
2101 l_get_info_rec.p_allow_interest_invoices, -- IN
2102 current_calling_sequence) <> TRUE) THEN
2103
2104 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2105 AP_IMPORT_UTILITIES_PKG.Print(
2106 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2107 'v_check_invoice_validation<-'||current_calling_sequence);
2108 END IF;
2109 Raise import_invoice_failure;
2110 END IF;
2111
2112 --bug 15862708 start
2113 l_invoice_rec.positive_price_tolerance := l_positive_price_tolerance;
2114 l_invoice_rec.qty_tolerance := l_qty_tolerance;
2115 l_invoice_rec.max_qty_ord_tolerance := l_max_qty_ord_tolerance;
2116 --bug 15862708 ends
2117 --BUG 16092065 starts
2118 l_invoice_rec.PREPAY_PERIOD_NAME := l_prepay_period_name;
2119 l_invoice_rec.PREPAY_INV_ID := l_prepay_invoice_id;
2120 l_invoice_rec.PREPAY_CASE_NAME := l_prepay_case_name;
2121 --bug 16092065 ends
2122 --
2123 -- show output values (only IF debug_switch = 'Y')
2124 --
2125 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2126 AP_IMPORT_UTILITIES_PKG.Print( AP_IMPORT_INVOICES_PKG.g_debug_switch,
2127 '------------------> vendor_id = '||to_char(l_invoice_rec.vendor_id)
2128 ||' vendor_site_id = ' ||to_char(l_invoice_rec.vendor_site_id)
2129 ||' invoice_status = ' ||l_invoice_status
2130 ||' terms_id = ' ||to_char(l_invoice_rec.terms_id)
2131 ||' fatal_error_flag = ' ||l_fatal_error_flag
2132 ||' invoice_type_lookup_code = '
2133 ||l_invoice_rec.invoice_type_lookup_code
2134 ||' match_mode = ' ||l_match_mode);
2135 END IF;
2136
2137 IF (( l_invoice_status = 'Y') AND
2138 (NVL(l_fatal_error_flag,'N') = 'N')) THEN
2139
2140 --------------------------
2141 -- Step 12
2142 -- Validate invoice lines
2143 --------------------------
2144 debug_info := '(Import_invoice 12) Validate line';
2145 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2146 AP_IMPORT_UTILITIES_PKG.Print(
2147 AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2148 END IF;
2149 AP_IMPORT_UTILITIES_PKG.Print(
2150 AP_IMPORT_INVOICES_PKG.g_debug_switch, 'SOBID is :'||l_invoice_rec.set_of_books_id);
2151
2152 IF (AP_IMPORT_VALIDATION_PKG.v_check_lines_validation (
2153 l_invoice_rec, -- IN
2154 l_invoice_lines_tab, -- OUT NOCOPY
2155 l_invoice_rec.gl_date, -- IN
2156 --l_gl_date_from_receipt_flag, -- IN --bug 15862708
2157 l_get_info_rec.p_gl_date_from_receipt_flag, -- IN
2158 l_positive_price_tolerance, -- IN
2159 --l_pa_installed, -- IN --bug 15862708
2160 l_get_info_rec.p_pa_installed, -- IN
2161 l_qty_tolerance, -- IN
2162 l_amt_tolerance, -- IN
2163 l_max_qty_ord_tolerance, -- IN
2164 l_max_amt_ord_tolerance, -- IN
2165 l_min_acct_unit, -- IN
2166 l_precision, -- IN
2167 /* --bug 15862708
2168 l_base_currency_code, -- IN
2169 l_base_min_acct_unit, -- IN
2170 l_base_precision, -- IN
2171 l_set_of_books_id, -- IN
2172 l_asset_book_type, -- IN -- Bug 5448579
2173 l_chart_of_accounts_id, -- IN
2174 l_freight_code_combination_id, -- IN
2175 l_purch_encumbrance_flag, -- IN
2176 l_retainage_ccid, -- IN */
2177 l_get_info_rec.p_base_currency_code, -- IN
2178 l_get_info_rec.p_base_min_acct_unit, -- IN
2179 l_get_info_rec.p_base_precision, -- IN
2180 l_get_info_rec.p_set_of_books_id, -- IN
2181 l_get_info_rec.p_asset_book_type, -- IN -- Bug 5448579
2182 l_get_info_rec.p_chart_of_accounts_id, -- IN
2183 l_get_info_rec.p_freight_code_combination_id, -- IN
2184 l_get_info_rec.p_purch_encumbrance_flag, -- IN
2185 l_get_info_rec.p_retainage_ccid, -- IN
2186 l_default_last_updated_by, -- IN
2187 l_default_last_update_login, -- IN
2188 l_invoice_status, -- OUT NOCOPY
2189 current_calling_sequence) <> TRUE) THEN
2190
2191 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2192 AP_IMPORT_UTILITIES_PKG.Print(
2193 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2194 'v_check_lines_validation<-'||current_calling_sequence);
2195 END IF;
2196 Raise import_invoice_failure;
2197 END IF;
2198 END IF;
2199
2200
2201 AP_IMPORT_UTILITIES_PKG.Print(
2202 AP_IMPORT_INVOICES_PKG.g_debug_switch, 'invoice_status is:'||l_invoice_status);
2203
2204 -- assigning the following variable that was included in the
2205 -- header rec (denormalized)
2206 -- this variable will be used by Tax
2207 --bug 15862708 start
2208 -- l_invoice_rec.set_of_books_id := l_set_of_books_id;
2209 l_invoice_rec.set_of_books_id := l_get_info_rec.p_set_of_books_id;
2210 --bug 15862708 end
2211 --bug 15862708 starts
2212 IF l_invoice_status='Y' then
2213
2214 l_inv_index := l_inv_index+1;
2215 tab_invoice_table(l_inv_index):=l_invoice_rec;
2216
2217 IF l_invoice_lines_tab.COUNT>0 THEN
2218 FOR i in l_invoice_lines_tab.FIRST .. l_invoice_lines_tab.LAST
2219 LOOP
2220 l_line_index := l_line_index+1;
2221 tab_lines_table(l_line_index):= l_invoice_lines_tab(i);
2222 END LOOP;
2223 END IF;
2224
2225 END IF;
2226 --bug 15862708 ends
2227
2228 -------------------------------------------------
2229 -- Step 17
2230 -- Change temporary status in ap_invoice_interface
2231 -------------------------------------------------
2232 BEGIN --veramach bug 7121842
2233 IF (l_invoice_status = 'N') THEN
2234 -----------------------------------------------------
2235 -- Step 17.1. Change the invoice status to 'REJECTING'
2236 -----------------------------------------------------
2237 debug_info := '(Import_invoice 17.1) Change the invoice status to '||
2238 'REJECTING';
2239 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2240 AP_IMPORT_UTILITIES_PKG.Print(
2241 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2242 END IF;
2243
2244 IF (AP_IMPORT_UTILITIES_PKG.change_invoice_status(
2245 'REJECTING',
2246 l_invoice_rec.invoice_id,
2247 current_calling_sequence) <> TRUE) THEN
2248 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2249 AP_IMPORT_UTILITIES_PKG.Print(
2250 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2251 'change_invoice_status<-'||current_calling_sequence);
2252 END IF;
2253 Raise import_invoice_failure;
2254 END IF;
2255 ELSE
2256 ------------------------------------------------------
2257 -- Step 17.2 Change the invoice status to 'PROCESSING'
2258 ------------------------------------------------------
2259 debug_info := '(Import_invoice 17.2) Change the invoice status to '||
2260 'PROCESSING';
2261 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2262 AP_IMPORT_UTILITIES_PKG.Print(
2263 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2264 END IF;
2265
2266 IF (AP_IMPORT_UTILITIES_PKG.change_invoice_status(
2267 'PROCESSING',
2268 l_invoice_rec.invoice_id,
2269 current_calling_sequence) <> TRUE) THEN
2270 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2271 AP_IMPORT_UTILITIES_PKG.Print(
2272 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2273 'change_invoice_status<-'||current_calling_sequence);
2274 END IF;
2275 Raise import_invoice_failure;
2276 END IF;
2277 END IF;
2278 --veramach bug 7121842 start
2279 EXCEPTION
2280 WHEN import_invoice_failure THEN
2281 NULL;
2282 END;
2283 --veramach bug 7121842 end
2284 l_old_org_id := nvl(l_invoice_rec.org_id, nvl(p_org_id, NULL));
2285 ELSE --ppa invoices else block
2286 --bug 15862708 starts
2287 l_inv_index := l_inv_index+1;
2288 tab_invoice_table(l_inv_index):=l_invoice_rec;
2289
2290 ----------------------------------------------------------------
2291 -- Step 17.2 Change the invoice status to 'PROCESSING' for PPA invoices
2292 -------------------------------------------------------------------
2293 debug_info := '(Import_invoice 17.2) Change the invoice status to '||
2294 'PROCESSING';
2295 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2296 AP_IMPORT_UTILITIES_PKG.Print(
2297 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2298 END IF;
2299
2300 IF (AP_IMPORT_UTILITIES_PKG.change_invoice_status(
2301 'PROCESSING',
2302 l_invoice_rec.invoice_id,
2303 current_calling_sequence) <> TRUE) THEN
2304 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2305 AP_IMPORT_UTILITIES_PKG.Print(
2306 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2307 'change_invoice_status<-'||current_calling_sequence);
2308 END IF;
2309 Raise import_invoice_failure;
2310 END IF;
2311 l_old_org_id := nvl(l_invoice_rec.org_id, nvl(p_org_id, NULL));
2312 --bug 15862708 ends
2313 END IF; -- ppa Invoices end if
2314 --veramach bug 7121842 start
2315 EXCEPTION
2316 WHEN import_invoice_failure THEN
2317 l_invoice_status := 'N';
2318 -----------------------------------------------------
2319 -- Step 17.1. Change the invoice status to 'REJECTING'
2320 -----------------------------------------------------
2321 debug_info := '(Import_invoice 17.1) Change the invoice status to '||
2322 'REJECTING';
2323 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2324 AP_IMPORT_UTILITIES_PKG.Print(
2325 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2326 END IF;
2327
2328 IF (AP_IMPORT_UTILITIES_PKG.change_invoice_status(
2329 'REJECTING',
2330 l_invoice_rec.invoice_id,
2331 current_calling_sequence) <> TRUE) THEN
2332 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2333 AP_IMPORT_UTILITIES_PKG.Print(
2334 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2335 'change_invoice_status<-'||current_calling_sequence);
2336 END IF;
2337 Raise import_invoice_failure;
2338 END IF;
2339 END;
2340 --veramach bug 7121842 end
2341 END LOOP; -- invoice LOOP
2342
2343 debug_info := '(Import_invoice) CLOSE import_invoices';
2344 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2345 AP_IMPORT_UTILITIES_PKG.Print(
2346 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2347 END IF;
2348
2349 debug_info := '(Import_invoice -->Execution completed for first loop:'
2350 ||to_char(sysdate,'YY-MON-DD HH24:MI:SS');
2351
2352 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2353 AP_IMPORT_UTILITIES_PKG.Print(
2354 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2355 END IF;
2356
2357 -- Bug 4145391
2358 IF (p_group_id IS NULL) THEN
2359 CLOSE import_invoices;
2360 ELSE
2361 CLOSE import_invoices_group;
2362 END IF;
2363 --bug 15862708 ends
2364
2365 /* --bug 15862708 if clause commented and the validate_default_import will be
2366 --called in bulk
2367 -- Payment Request: Do not call eTax API for Payment Requests
2368 IF ((l_invoice_status = 'Y') AND
2369 (l_invoice_rec.invoice_type_lookup_code <> 'PAYMENT REQUEST')) THEN */
2370
2371 --------------------------------------------------------------
2372 -- Step 13. Call validate eTax API. This API will validate
2373 -- tax information for taxable and tax lines.
2374 --------------------------------------------------------------
2375 debug_info := '(Import_invoice 13) Validate tax info for '||
2376 'tax and taxable lines';
2377 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2378 AP_IMPORT_UTILITIES_PKG.Print(
2379 AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2380 END IF;
2381
2382
2383 AP_IMPORT_UTILITIES_PKG.Print(
2384 AP_IMPORT_INVOICES_PKG.g_debug_switch, 'SOBID is :'||l_invoice_rec.set_of_books_id);
2385 --------------------------------------------------------------
2386 -- Call validate eTax API. This API will validate tax info
2387 -- for taxable and tax lines.
2388 --------------------------------------------------------------
2389 debug_info := '(Import_invoice 13.1) Header table count '||tab_invoice_table.COUNT;
2390 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2391 AP_IMPORT_UTILITIES_PKG.Print(
2392 AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2393 END IF;
2394
2395
2396
2397 IF tab_invoice_table.COUNT>0 THEN
2398 k_fir := tab_invoice_table.FIRST;
2399 END IF;
2400
2401
2402 BEGIN
2403 IF (tab_invoice_table.COUNT=1 AND tab_invoice_table(k_fir).invoice_type_lookup_code = 'PAYMENT REQUEST') THEN
2404 NULL;
2405 ELSE
2406 IF (tab_invoice_table.COUNT>0 AND tab_lines_table.COUNT>0 ) THEN
2407 IF NOT (ap_etax_services_pkg.validate_default_import(
2408 /* p_invoice_rec => l_invoice_rec,
2409 p_invoice_lines_tab => l_invoice_lines_tab, */ --bug 14009019
2410 P_Invoice_Rec_table => tab_invoice_table,
2411 p_invoice_lines_table => tab_lines_table,
2412 p_calling_mode => 'VALIDATE IMPORT',
2413 p_all_error_messages => 'Y',
2414 p_invoice_status => l_invoice_status,
2415 p_error_code => l_error_code,
2416 p_calling_sequence => current_calling_sequence)) THEN
2417
2418 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2419 AP_IMPORT_UTILITIES_PKG.Print(
2420 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2421 'ap_etax_services_pkg.validate_default_import<-'||current_calling_sequence);
2422 END IF;
2423
2424 --bug 14009019 starts
2425 IF tab_invoice_table.count>0 then
2426 FOR i in tab_invoice_table.first .. tab_invoice_table.last
2427 LOOP
2428 tab_invoice_table(i).import_status := 'N';
2429
2430 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections
2431 (AP_IMPORT_INVOICES_PKG.g_invoices_table, -- Bug 9452076.
2432 tab_invoice_table(i).invoice_id,
2433 'VALIDATE DEFAULT IMPORT FAIL',
2434 l_default_last_updated_by,
2435 l_default_last_update_login,
2436 current_calling_sequence) <> TRUE ) Then
2437 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
2438 AP_IMPORT_UTILITIES_PKG.Print(
2439 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2440 'insert_rejections<- ' ||current_calling_sequence);
2441 END IF;
2442
2443 END IF;
2444
2445 -----------------------------------------------------
2446 -- Step 17.1. Change the invoice status to 'REJECTING'
2447 -----------------------------------------------------
2448 debug_info := '(Import_invoice 17.1) Change the invoice status to '||
2449 'REJECTING';
2450 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2451 AP_IMPORT_UTILITIES_PKG.Print(
2452 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2453 END IF;
2454
2455 IF (AP_IMPORT_UTILITIES_PKG.change_invoice_status(
2456 'REJECTING',
2457 tab_invoice_table(i).invoice_id,
2458 current_calling_sequence) <> TRUE) THEN
2459 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2460 AP_IMPORT_UTILITIES_PKG.Print(
2461 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2462 'change_invoice_status<-'||current_calling_sequence);
2463 END IF;
2464 Raise import_invoice_failure;
2465 END IF;
2466
2467 END LOOP;
2468 END IF;
2469
2470 --bug 14009019 ends
2471
2472 -- If the validation call fails the import process fails.
2473 -- The validate_default_import will populate the rejections table
2474 -- for the import if required.
2475 -- If the API fails because the call to the eTax service fails
2476 -- the following code will get the messages from the message
2477 -- stack
2478
2479 IF (l_error_code IS NOT NULL) THEN
2480 -- Print the error returned from the service even if the debug
2481 -- mode is off
2482 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
2483
2484 ELSE
2485 -- If the l_error_code is null is because the service returned
2486 -- more than one error. The calling module will need to get
2487 -- them from the message stack
2488 LOOP
2489 l_error_code := FND_MSG_PUB.Get;
2490
2491 IF l_error_code IS NULL THEN
2492 EXIT;
2493 ELSE
2494 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
2495 END IF;
2496 END LOOP;
2497
2498 END IF;
2499 --Raise import_invoice_failure;
2500
2501 END IF;
2502 END IF;
2503 END IF;
2504 -- END IF; --bug 14009019 if commented for bulk call
2505 EXCEPTION
2506 WHEN import_invoice_failure THEN
2507 debug_info := 'Exception occured in the validate default import '||sqlerrm;
2508 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2509 AP_IMPORT_UTILITIES_PKG.Print(
2510 AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2511 END IF;
2512 END;
2513 -- END IF; --bug 15862708 if commented for bulk call
2514
2515 debug_info := '(Import_invoice 13) Validate tax info for '||
2516 'tax and taxable lines completed: ' ||to_char(sysdate,'YY-MON-DD HH24:MI:SS');
2517 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2518 AP_IMPORT_UTILITIES_PKG.Print(
2519 AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2520 END IF;
2521
2522 l_invoice_lines_tab.delete;
2523
2524
2525 debug_info := '(Import_invoice -->Execution started for second loop:'
2526 ||to_char(sysdate,'YY-MON-DD HH24:MI:SS');
2527
2528 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2529 AP_IMPORT_UTILITIES_PKG.Print(
2530 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2531 END IF;
2532
2533
2534 --bug 15862708 loop after validate_default_import call
2535 IF tab_invoice_table.count>0 THEN
2536 FOR i in tab_invoice_table.first .. tab_invoice_table.last LOOP
2537
2538 l_invoice_rec := tab_invoice_table(i);
2539
2540 -- if logic for import processing, if 'Y' then proceed
2541 -- else update to 'REJECTING' and dont process
2542
2543 IF nvl(tab_invoice_table(i).import_status,'Y') = 'Y' then
2544
2545 debug_info := '(Import_invoice 13.3) check Invoice Status Loop 1 '||tab_invoice_table(i).invoice_id;
2546 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2547 AP_IMPORT_UTILITIES_PKG.Print(
2548 AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2549 END IF;
2550
2551
2552 l_invoice_status := 'Y';
2553 k :=1;
2554
2555 --added for bulk processing with invoices of different orgs in
2556 -- the second loop
2557 IF l_invoice_rec.org_id <> NVL(l_old_org_id, -3115) THEN
2558 Mo_Global.set_policy_context('S', l_invoice_rec.org_id);
2559 l_old_org_id := l_invoice_rec.org_id;
2560 END IF;
2561
2562 BEGIN
2563 IF AP_IMPORT_INVOICES_PKG.g_source <> 'PPA' THEN
2564 debug_info := '(Import_invoice 13.3.1) Deleting the LINES_TAB collection';
2565 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2566 AP_IMPORT_UTILITIES_PKG.Print(
2567 AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2568 END IF;
2569
2570
2571 debug_info := '(Import_invoice 13.4) Before loop 2 ';
2572 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2573 AP_IMPORT_UTILITIES_PKG.Print(
2574 AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2575 END IF;
2576 debug_info := '(Import_invoice 13.4) Before loop 2 '||tab_lines_table.count;
2577 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2578 AP_IMPORT_UTILITIES_PKG.Print(
2579 AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2580 END IF;
2581
2582 FOR j IN tab_lines_table.FIRST .. tab_lines_table.LAST LOOP
2583
2584 debug_info := '(Import_invoice 13.5) Lines Loop 2 ';
2585 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2586 AP_IMPORT_UTILITIES_PKG.Print(
2587 AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2588 END IF;
2589
2590 IF tab_lines_table(j).invoice_id=l_invoice_rec.invoice_id then
2591 --l_invoice_lines_tab(k) := tab_lines_table(j);
2592
2593
2594 debug_info := '(Import_invoice 13.5.1) Assign Lines Collection,Lines Loop 2 '||tab_lines_table(j).line_number;
2595 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2596 AP_IMPORT_UTILITIES_PKG.Print(
2597 AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2598 END IF;
2599 lines_tab.extend(1);
2600
2601 debug_info := '(Import_invoice 13.5.2) Assign Lines Collection,Lines Loop 2 '||lines_tab.count;
2602 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2603 AP_IMPORT_UTILITIES_PKG.Print(
2604 AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2605 END IF;
2606
2607 BEGIN
2608 SELECT DECODE(tab_lines_table(j).line_type_lookup_code, 'ITEM',
2609 DECODE(tab_lines_table(j).match_type, NULL, 'NOT_MATCHED',tab_lines_table(j).match_type),
2610 DECODE(tab_lines_table(j).rcv_transaction_id, NULL, 'NOT_MATCHED','OTHER_TO_RECEIPT'))
2611 INTO lines_tab(k).match_type
2612 FROM dual;
2613 EXCEPTION
2614 WHEN OTHERS THEN
2615 NULL;
2616 END;
2617
2618
2619 lines_tab(k).INVOICE_ID := tab_lines_table(j).INVOICE_ID ;
2620 lines_tab(k).LINE_NUMBER := tab_lines_table(j).LINE_NUMBER;
2621 lines_tab(k).LINE_TYPE_LOOKUP_CODE := tab_lines_table(j).LINE_TYPE_LOOKUP_CODE;
2622 lines_tab(k).REQUESTER_ID := tab_lines_table(j).REQUESTER_ID ;
2623 lines_tab(k).DESCRIPTION := tab_lines_table(j).DESCRIPTION ;
2624 lines_tab(k).LINE_SOURCE := 'IMPORTED' ;
2625 lines_tab(k).ORG_ID := tab_lines_table(j).ORG_ID ;
2626 lines_tab(k).LINE_GROUP_NUMBER := tab_lines_table(j).LINE_GROUP_NUMBER ;
2627 lines_tab(k).INVENTORY_ITEM_ID := tab_lines_table(j).INVENTORY_ITEM_ID ;
2628 lines_tab(k).ITEM_DESCRIPTION := tab_lines_table(j).ITEM_DESCRIPTION ;
2629 lines_tab(k).SERIAL_NUMBER := tab_lines_table(j).SERIAL_NUMBER ;
2630 lines_tab(k).MANUFACTURER := tab_lines_table(j).MANUFACTURER ;
2631 lines_tab(k).MODEL_NUMBER := tab_lines_table(j).MODEL_NUMBER ;
2632 lines_tab(k).WARRANTY_NUMBER := tab_lines_table(j).WARRANTY_NUMBER ;
2633 lines_tab(k).GENERATE_DISTS := tab_lines_table(j).GENERATE_DISTS ;
2634 --lines_tab(k).MATCH_TYPE := tab_lines_table(j).MATCH_TYPE ;
2635 lines_tab(k).DISTRIBUTION_SET_ID := tab_lines_table(j).DISTRIBUTION_SET_ID ;
2636 lines_tab(k).ACCOUNT_SEGMENT := tab_lines_table(j).ACCOUNT_SEGMENT ;
2637 lines_tab(k).BALANCING_SEGMENT := tab_lines_table(j).BALANCING_SEGMENT ;
2638 lines_tab(k).COST_CENTER_SEGMENT := tab_lines_table(j).COST_CENTER_SEGMENT ;
2639 lines_tab(k).OVERLAY_DIST_CODE_CONCAT := tab_lines_table(j).dist_code_concatenated ;
2640 lines_tab(k).DEFAULT_DIST_CCID := tab_lines_table(j).dist_code_combination_id ;
2641 lines_tab(k).PRORATE_ACROSS_ALL_ITEMS := tab_lines_table(j).prorate_across_flag ;
2642 lines_tab(k).ACCOUNTING_DATE := tab_lines_table(j).ACCOUNTING_DATE ;
2643 lines_tab(k).PERIOD_NAME := tab_lines_table(j).PERIOD_NAME ;
2644 lines_tab(k).DEFERRED_ACCTG_FLAG := nvl(tab_lines_table(j).deferred_acctg_flag,'N') ;
2645 lines_tab(k).DEF_ACCTG_START_DATE := tab_lines_table(j).DEF_ACCTG_START_DATE ;
2646 lines_tab(k).DEF_ACCTG_END_DATE := tab_lines_table(j).DEF_ACCTG_END_DATE ;
2647 lines_tab(k).DEF_ACCTG_NUMBER_OF_PERIODS := tab_lines_table(j).DEF_ACCTG_NUMBER_OF_PERIODS ;
2648 lines_tab(k).DEF_ACCTG_PERIOD_TYPE := tab_lines_table(j).DEF_ACCTG_PERIOD_TYPE ;
2649 lines_tab(k).SET_OF_BOOKS_ID := l_invoice_rec.SET_OF_BOOKS_ID ;
2650 lines_tab(k).AMOUNT := tab_lines_table(j).AMOUNT ;
2651 lines_tab(k).BASE_AMOUNT := tab_lines_table(j).BASE_AMOUNT ;
2652 lines_tab(k).ROUNDING_AMT := NULL ;
2653 lines_tab(k).QUANTITY_INVOICED := tab_lines_table(j).QUANTITY_INVOICED ;
2654 lines_tab(k).UNIT_MEAS_LOOKUP_CODE := tab_lines_table(j).unit_of_meas_lookup_code ;
2655 lines_tab(k).UNIT_PRICE := tab_lines_table(j).UNIT_PRICE ;
2656 lines_tab(k).WFAPPROVAL_STATUS := tab_lines_table(j).WFAPPROVAL_STATUS ;
2657 lines_tab(k).USSGL_TRANSACTION_CODE := NULL ;
2658 lines_tab(k).DISCARDED_FLAG := 'N' ;
2659 lines_tab(k).ORIGINAL_AMOUNT := NULL ;
2660 lines_tab(k).ORIGINAL_BASE_AMOUNT := NULL ;
2661 lines_tab(k).ORIGINAL_ROUNDING_AMT := NULL ;
2662 lines_tab(k).CANCELLED_FLAG := 'N' ;
2663 lines_tab(k).INCOME_TAX_REGION := tab_lines_table(j).INCOME_TAX_REGION ;
2664 lines_tab(k).TYPE_1099 := tab_lines_table(j).TYPE_1099 ;
2665 lines_tab(k).STAT_AMOUNT := tab_lines_table(j).STAT_AMOUNT ;
2666 lines_tab(k).PREPAY_INVOICE_ID := NULL ;
2667 lines_tab(k).PREPAY_LINE_NUMBER := NULL ;
2668 lines_tab(k).INVOICE_INCLUDES_PREPAY_FLAG := NULL ;
2669 lines_tab(k).CORRECTED_INV_ID := tab_lines_table(j).CORRECTED_INV_ID ;
2670 lines_tab(k).CORRECTED_LINE_NUMBER := tab_lines_table(j).price_correct_inv_line_num ;
2671 lines_tab(k).PO_HEADER_ID := tab_lines_table(j).PO_HEADER_ID ;
2672 lines_tab(k).PO_LINE_ID := tab_lines_table(j).PO_LINE_ID ;
2673 lines_tab(k).PO_RELEASE_ID := tab_lines_table(j).PO_RELEASE_ID ;
2674 lines_tab(k).PO_LINE_LOCATION_ID := tab_lines_table(j).PO_LINE_LOCATION_ID ;
2675 lines_tab(k).PO_DISTRIBUTION_ID := tab_lines_table(j).PO_DISTRIBUTION_ID ;
2676 lines_tab(k).RCV_TRANSACTION_ID := tab_lines_table(j).RCV_TRANSACTION_ID ;
2677 lines_tab(k).FINAL_MATCH_FLAG := tab_lines_table(j).FINAL_MATCH_FLAG ;
2678 lines_tab(k).ASSETS_TRACKING_FLAG := nvl(tab_lines_table(j).assets_tracking_flag, 'N') ;
2679 lines_tab(k).ASSET_BOOK_TYPE_CODE := tab_lines_table(j).ASSET_BOOK_TYPE_CODE ;
2680 lines_tab(k).ASSET_CATEGORY_ID := tab_lines_table(j).ASSET_CATEGORY_ID ;
2681 lines_tab(k).PROJECT_ID := tab_lines_table(j).PROJECT_ID ;
2682 lines_tab(k).TASK_ID := tab_lines_table(j).TASK_ID ;
2683 lines_tab(k).EXPENDITURE_TYPE := tab_lines_table(j).EXPENDITURE_TYPE ;
2684 lines_tab(k).EXPENDITURE_ITEM_DATE := tab_lines_table(j).EXPENDITURE_ITEM_DATE ;
2685 lines_tab(k).EXPENDITURE_ORGANIZATION_ID := tab_lines_table(j).EXPENDITURE_ORGANIZATION_ID ;
2686 lines_tab(k).PA_QUANTITY := tab_lines_table(j).PA_QUANTITY ;
2687 lines_tab(k).PA_CC_AR_INVOICE_ID := tab_lines_table(j).PA_CC_AR_INVOICE_ID ;
2688 lines_tab(k).PA_CC_AR_INVOICE_LINE_NUM := tab_lines_table(j).PA_CC_AR_INVOICE_LINE_NUM ;
2689 lines_tab(k).PA_CC_PROCESSED_CODE := tab_lines_table(j).PA_CC_PROCESSED_CODE ;
2690 lines_tab(k).AWARD_ID := tab_lines_table(j).AWARD_ID ;
2691 lines_tab(k).AWT_GROUP_ID := tab_lines_table(j).AWT_GROUP_ID ;
2692 lines_tab(k).REFERENCE_1 := tab_lines_table(j).REFERENCE_1 ;
2693 lines_tab(k).REFERENCE_2 := tab_lines_table(j).REFERENCE_2 ;
2694 lines_tab(k).RECEIPT_VERIFIED_FLAG := NULL ;
2695 lines_tab(k).RECEIPT_REQUIRED_FLAG := NULL ;
2696 lines_tab(k).RECEIPT_MISSING_FLAG := NULL ;
2697 lines_tab(k).JUSTIFICATION := tab_lines_table(j).JUSTIFICATION ;
2698 lines_tab(k).EXPENSE_GROUP := tab_lines_table(j).EXPENSE_GROUP ;
2699 lines_tab(k).START_EXPENSE_DATE := tab_lines_table(j).EXPENSE_START_DATE ;
2700 lines_tab(k).END_EXPENSE_DATE := tab_lines_table(j).EXPENSE_END_DATE ;
2701 lines_tab(k).RECEIPT_CURRENCY_CODE := tab_lines_table(j).RECEIPT_CURRENCY_CODE ;
2702 lines_tab(k).RECEIPT_CONVERSION_RATE := tab_lines_table(j).RECEIPT_CONVERSION_RATE ;
2703 lines_tab(k).RECEIPT_CURRENCY_AMOUNT := tab_lines_table(j).RECEIPT_CURRENCY_AMOUNT ;
2704 lines_tab(k).DAILY_AMOUNT := NULL ;
2705 lines_tab(k).WEB_PARAMETER_ID := NULL ;
2706 lines_tab(k).ADJUSTMENT_REASON := NULL ;
2707 lines_tab(k).MERCHANT_DOCUMENT_NUMBER := tab_lines_table(j).MERCHANT_DOCUMENT_NUMBER ;
2708 lines_tab(k).MERCHANT_NAME := tab_lines_table(j).MERCHANT_NAME ;
2709 lines_tab(k).MERCHANT_REFERENCE := tab_lines_table(j).MERCHANT_REFERENCE ;
2710 lines_tab(k).MERCHANT_TAX_REG_NUMBER := tab_lines_table(j).MERCHANT_TAX_REG_NUMBER ;
2711 lines_tab(k).MERCHANT_TAXPAYER_ID := tab_lines_table(j).MERCHANT_TAXPAYER_ID ;
2712 lines_tab(k).COUNTRY_OF_SUPPLY := tab_lines_table(j).COUNTRY_OF_SUPPLY ;
2713 lines_tab(k).CREDIT_CARD_TRX_ID := tab_lines_table(j).CREDIT_CARD_TRX_ID ;
2714 lines_tab(k).COMPANY_PREPAID_INVOICE_ID := tab_lines_table(j).COMPANY_PREPAID_INVOICE_ID ;
2715 lines_tab(k).CC_REVERSAL_FLAG := tab_lines_table(j).CC_REVERSAL_FLAG ;
2716 /*Bug 14271140 Start*/
2717 lines_tab(k).CREATION_DATE := nvl(tab_lines_table(j).CREATION_DATE,AP_IMPORT_INVOICES_PKG.g_inv_sysdate);
2718 lines_tab(k).CREATED_BY := nvl(tab_lines_table(j).CREATED_BY,L_default_last_updated_by);
2719 lines_tab(k).LAST_UPDATED_BY := nvl(tab_lines_table(j).LAST_UPDATED_BY,L_default_last_updated_by);
2720 lines_tab(k).LAST_UPDATE_DATE := nvl(tab_lines_table(j).LAST_UPDATE_DATE,AP_IMPORT_INVOICES_PKG.g_inv_sysdate ) ;
2721 lines_tab(k).LAST_UPDATE_LOGIN := nvl(tab_lines_table(j).LAST_UPDATE_LOGIN,l_default_last_update_login) ;
2722 /*Bug 14271140 End*/
2723 lines_tab(k).PROGRAM_APPLICATION_ID := AP_IMPORT_INVOICES_PKG.g_program_application_id ;
2724 lines_tab(k).PROGRAM_ID := AP_IMPORT_INVOICES_PKG.g_program_id ;
2725 lines_tab(k).PROGRAM_UPDATE_DATE := AP_IMPORT_INVOICES_PKG.g_inv_sysdate ;
2726 lines_tab(k).REQUEST_ID := AP_IMPORT_INVOICES_PKG.g_conc_request_id ;
2727 lines_tab(k).ATTRIBUTE_CATEGORY := tab_lines_table(j).ATTRIBUTE_CATEGORY ;
2728 lines_tab(k).ATTRIBUTE1 := tab_lines_table(j).ATTRIBUTE1 ;
2729 lines_tab(k).ATTRIBUTE2 := tab_lines_table(j).ATTRIBUTE2 ;
2730 lines_tab(k).ATTRIBUTE3 := tab_lines_table(j).ATTRIBUTE3 ;
2731 lines_tab(k).ATTRIBUTE4 := tab_lines_table(j).ATTRIBUTE4 ;
2732 lines_tab(k).ATTRIBUTE5 := tab_lines_table(j).ATTRIBUTE5 ;
2733 lines_tab(k).ATTRIBUTE6 := tab_lines_table(j).ATTRIBUTE6 ;
2734 lines_tab(k).ATTRIBUTE7 := tab_lines_table(j).ATTRIBUTE7 ;
2735 lines_tab(k).ATTRIBUTE8 := tab_lines_table(j).ATTRIBUTE8 ;
2736 lines_tab(k).ATTRIBUTE9 := tab_lines_table(j).ATTRIBUTE9 ;
2737 lines_tab(k).ATTRIBUTE10 := tab_lines_table(j).ATTRIBUTE10 ;
2738 lines_tab(k).ATTRIBUTE11 := tab_lines_table(j).ATTRIBUTE11 ;
2739 lines_tab(k).ATTRIBUTE12 := tab_lines_table(j).ATTRIBUTE12 ;
2740 lines_tab(k).ATTRIBUTE13 := tab_lines_table(j).ATTRIBUTE13 ;
2741 lines_tab(k).ATTRIBUTE14 := tab_lines_table(j).ATTRIBUTE14 ;
2742 lines_tab(k).ATTRIBUTE15 := tab_lines_table(j).ATTRIBUTE15 ;
2743 lines_tab(k).GLOBAL_ATTRIBUTE_CATEGORY := tab_lines_table(j).GLOBAL_ATTRIBUTE_CATEGORY ;
2744 lines_tab(k).GLOBAL_ATTRIBUTE1 := tab_lines_table(j).GLOBAL_ATTRIBUTE1 ;
2745 lines_tab(k).GLOBAL_ATTRIBUTE2 := tab_lines_table(j).GLOBAL_ATTRIBUTE2 ;
2746 lines_tab(k).GLOBAL_ATTRIBUTE3 := tab_lines_table(j).GLOBAL_ATTRIBUTE3 ;
2747 lines_tab(k).GLOBAL_ATTRIBUTE4 := tab_lines_table(j).GLOBAL_ATTRIBUTE4 ;
2748 lines_tab(k).GLOBAL_ATTRIBUTE5 := tab_lines_table(j).GLOBAL_ATTRIBUTE5 ;
2749 lines_tab(k).GLOBAL_ATTRIBUTE6 := tab_lines_table(j).GLOBAL_ATTRIBUTE6 ;
2750 lines_tab(k).GLOBAL_ATTRIBUTE7 := tab_lines_table(j).GLOBAL_ATTRIBUTE7 ;
2751 lines_tab(k).GLOBAL_ATTRIBUTE8 := tab_lines_table(j).GLOBAL_ATTRIBUTE8 ;
2752 lines_tab(k).GLOBAL_ATTRIBUTE9 := tab_lines_table(j).GLOBAL_ATTRIBUTE9 ;
2753 lines_tab(k).GLOBAL_ATTRIBUTE10 := tab_lines_table(j).GLOBAL_ATTRIBUTE10 ;
2754 lines_tab(k).GLOBAL_ATTRIBUTE11 := tab_lines_table(j).GLOBAL_ATTRIBUTE11 ;
2755 lines_tab(k).GLOBAL_ATTRIBUTE12 := tab_lines_table(j).GLOBAL_ATTRIBUTE12 ;
2756 lines_tab(k).GLOBAL_ATTRIBUTE13 := tab_lines_table(j).GLOBAL_ATTRIBUTE13 ;
2757 lines_tab(k).GLOBAL_ATTRIBUTE14 := tab_lines_table(j).GLOBAL_ATTRIBUTE14 ;
2758 lines_tab(k).GLOBAL_ATTRIBUTE15 := tab_lines_table(j).GLOBAL_ATTRIBUTE15 ;
2759 lines_tab(k).GLOBAL_ATTRIBUTE16 := tab_lines_table(j).GLOBAL_ATTRIBUTE16 ;
2760 lines_tab(k).GLOBAL_ATTRIBUTE17 := tab_lines_table(j).GLOBAL_ATTRIBUTE17 ;
2761 lines_tab(k).GLOBAL_ATTRIBUTE18 := tab_lines_table(j).GLOBAL_ATTRIBUTE18 ;
2762 lines_tab(k).GLOBAL_ATTRIBUTE19 := tab_lines_table(j).GLOBAL_ATTRIBUTE19 ;
2763 lines_tab(k).GLOBAL_ATTRIBUTE20 := tab_lines_table(j).GLOBAL_ATTRIBUTE20 ;
2764 lines_tab(k).LINE_SELECTED_FOR_APPL_FLAG := NULL ;
2765 lines_tab(k).PREPAY_APPL_REQUEST_ID := NULL ;
2766 lines_tab(k).APPLICATION_ID := tab_lines_table(j).APPLICATION_ID ;
2767 lines_tab(k).PRODUCT_TABLE := tab_lines_table(j).PRODUCT_TABLE ;
2768 lines_tab(k).REFERENCE_KEY1 := tab_lines_table(j).REFERENCE_KEY1 ;
2769 lines_tab(k).REFERENCE_KEY2 := tab_lines_table(j).REFERENCE_KEY2 ;
2770 lines_tab(k).REFERENCE_KEY3 := tab_lines_table(j).REFERENCE_KEY3 ;
2771 lines_tab(k).REFERENCE_KEY4 := tab_lines_table(j).REFERENCE_KEY4 ;
2772 lines_tab(k).REFERENCE_KEY5 := tab_lines_table(j).REFERENCE_KEY5 ;
2773 lines_tab(k).PURCHASING_CATEGORY_ID := tab_lines_table(j).PURCHASING_CATEGORY_ID ;
2774 lines_tab(k).COST_FACTOR_ID := tab_lines_table(j).COST_FACTOR_ID ;
2775 lines_tab(k).CONTROL_AMOUNT := tab_lines_table(j).CONTROL_AMOUNT ;
2776 lines_tab(k).ASSESSABLE_VALUE := tab_lines_table(j).ASSESSABLE_VALUE ;
2777 lines_tab(k).TOTAL_REC_TAX_AMOUNT := NULL ;
2778 lines_tab(k).TOTAL_NREC_TAX_AMOUNT := NULL ;
2779 lines_tab(k).TOTAL_REC_TAX_AMT_FUNCL_CURR := NULL ;
2780 lines_tab(k).TOTAL_NREC_TAX_AMT_FUNCL_CURR := NULL ;
2781 lines_tab(k).INCLUDED_TAX_AMOUNT := NULL ;
2782 lines_tab(k).PRIMARY_INTENDED_USE := tab_lines_table(j).PRIMARY_INTENDED_USE ;
2783 lines_tab(k).TAX_ALREADY_CALCULATED_FLAG := NULL ;
2784 lines_tab(k).SHIP_TO_LOCATION_ID := tab_lines_table(j).SHIP_TO_LOCATION_ID ;
2785 lines_tab(k).PRODUCT_TYPE := tab_lines_table(j).PRODUCT_TYPE ;
2786 lines_tab(k).PRODUCT_CATEGORY := tab_lines_table(j).PRODUCT_CATEGORY ;
2787 lines_tab(k).PRODUCT_FISC_CLASSIFICATION := tab_lines_table(j).PRODUCT_FISC_CLASSIFICATION ;
2788 lines_tab(k).USER_DEFINED_FISC_CLASS := tab_lines_table(j).USER_DEFINED_FISC_CLASS ;
2789 lines_tab(k).TRX_BUSINESS_CATEGORY := tab_lines_table(j).TRX_BUSINESS_CATEGORY ;
2790 lines_tab(k).SUMMARY_TAX_LINE_ID := NULL ;
2791 lines_tab(k).TAX_REGIME_CODE := tab_lines_table(j).TAX_REGIME_CODE ;
2792 lines_tab(k).TAX := tab_lines_table(j).TAX ;
2793 lines_tab(k).TAX_JURISDICTION_CODE := tab_lines_table(j).TAX_JURISDICTION_CODE ;
2794 lines_tab(k).TAX_STATUS_CODE := tab_lines_table(j).TAX_STATUS_CODE ;
2795 lines_tab(k).TAX_RATE_ID := tab_lines_table(j).TAX_RATE_ID ;
2796 lines_tab(k).TAX_RATE_CODE := tab_lines_table(j).TAX_RATE_CODE ;
2797 lines_tab(k).TAX_RATE := tab_lines_table(j).TAX_RATE ;
2798 lines_tab(k).TAX_CODE_ID := NULL ;
2799 lines_tab(k).HISTORICAL_FLAG := NULL ;
2800 lines_tab(k).TAX_CLASSIFICATION_CODE := tab_lines_table(j).TAX_CLASSIFICATION_CODE ;
2801 lines_tab(k).SOURCE_APPLICATION_ID := tab_lines_table(j).SOURCE_APPLICATION_ID ;
2802 lines_tab(k).SOURCE_EVENT_CLASS_CODE := tab_lines_table(j).SOURCE_EVENT_CLASS_CODE ;
2803 lines_tab(k).SOURCE_ENTITY_CODE := tab_lines_table(j).SOURCE_ENTITY_CODE ;
2804 lines_tab(k).SOURCE_TRX_ID := tab_lines_table(j).SOURCE_TRX_ID ;
2805 lines_tab(k).SOURCE_LINE_ID := tab_lines_table(j).SOURCE_LINE_ID ;
2806 lines_tab(k).SOURCE_TRX_LEVEL_TYPE := tab_lines_table(j).SOURCE_TRX_LEVEL_TYPE ;
2807 lines_tab(k).RETAINED_AMOUNT := tab_lines_table(j).RETAINED_AMOUNT ;
2808 lines_tab(k).RETAINED_AMOUNT_REMAINING := (-tab_lines_table(j).RETAINED_AMOUNT ) ;
2809 lines_tab(k).RETAINED_INVOICE_ID := NULL ;
2810 lines_tab(k).RETAINED_LINE_NUMBER := NULL ;
2811 lines_tab(k).LINE_SELECTED_FOR_RELEASE_FLAG := NULL ;
2812 lines_tab(k).LINE_OWNER_ROLE := NULL ;
2813 lines_tab(k).DISPUTABLE_FLAG := NULL ;
2814 lines_tab(k).RCV_SHIPMENT_LINE_ID := tab_lines_table(j).RCV_SHIPMENT_LINE_ID ;
2815 lines_tab(k).AIL_INVOICE_ID := NULL ;
2816 lines_tab(k).AIL_DISTRIBUTION_LINE_NUMBER := NULL ;
2817 lines_tab(k).AIL_INVOICE_ID2 := NULL ;
2818 lines_tab(k).AIL_DISTRIBUTION_LINE_NUMBER2 := NULL ;
2819 lines_tab(k).AIL_INVOICE_ID3 := NULL ;
2820 lines_tab(k).AIL_DISTRIBUTION_LINE_NUMBER3 := NULL ;
2821 lines_tab(k).AIL_INVOICE_ID4 := NULL ;
2822 lines_tab(k).PAY_AWT_GROUP_ID := tab_lines_table(j).PAY_AWT_GROUP_ID ;
2823
2824 debug_info := '(Import_invoice 13.5) Assign Lines Collection,Lines Loop 2 end';
2825 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2826 AP_IMPORT_UTILITIES_PKG.Print(
2827 AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2828 END IF;
2829
2830 k := k+1;
2831
2832 END IF;
2833 END LOOP;
2834
2835
2836 IF (l_invoice_status = 'Y') THEN
2837 ------------------------------------
2838 -- Step 14
2839 -- Call Sequential Numbering Routine
2840 ------------------------------------
2841 debug_info := '(Import_invoice 14) Get Doc Sequence';
2842 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2843 AP_IMPORT_UTILITIES_PKG.Print(
2844 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2845 END IF;
2846
2847 AP_IMPORT_UTILITIES_PKG.Print(
2848 AP_IMPORT_INVOICES_PKG.g_debug_switch, 'SOBID is :'||l_invoice_rec.set_of_books_id);
2849
2850 IF (AP_IMPORT_UTILITIES_PKG.get_doc_sequence (
2851 l_invoice_rec, -- IN OUT
2852 /*l_inv_doc_cat_override, -- IN
2853 l_set_of_books_id, -- IN
2854 l_sequence_numbering, -- IN */ --bug 15862708
2855 NVL(AP_IMPORT_INVOICES_PKG.tab_get_info_rec(l_invoice_rec.org_id).p_inv_doc_cat_override,'N'), -- IN
2856 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(l_invoice_rec.org_id).p_set_of_books_id, -- IN
2857 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(l_invoice_rec.org_id).p_sequence_numbering, -- IN
2858 l_default_last_updated_by, -- IN
2859 l_default_last_update_login, -- IN
2860 l_seqval, -- OUT NOCOPY
2861 l_dbseqnm, -- OUT NOCOPY
2862 l_dbseqid, -- OUT NOCOPY
2863 l_invoice_status, -- OUT NOCOPY
2864 current_calling_sequence)<> TRUE) THEN
2865
2866 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2867 AP_IMPORT_UTILITIES_PKG.Print(
2868 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2869 'get_doc_sequence<-'||current_calling_sequence);
2870 END IF;
2871 Raise import_invoice_failure;
2872 END IF;
2873
2874 -- show output values (only IF debug_switch = 'Y')
2875
2876 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2877 AP_IMPORT_UTILITIES_PKG.Print(
2878 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2879 '------------------> l_invoice_status = '|| l_invoice_status
2880 ||' l_seqval = '||to_char(l_seqval)
2881 ||' l_dbseqnm = '||l_dbseqnm
2882 ||' l_dbseqid = '||to_char(l_dbseqid));
2883 END IF;
2884 END IF; -- Invoice Status = 'Y'before get_doc_sequence
2885
2886 ---------------------------------------------------------------
2887 -- Step 15 Process invoice AND lines IF l_invoice_status is 'Y'
2888 -- or skip these steps
2889 ----------------------------------------------------------------
2890 IF (l_invoice_status = 'Y') THEN
2891
2892 -----------------------------------------------------
2893 -- Step 15.1a
2894 -- Get some required fields for creating invoices
2895 -- most of them are from po_vendor_sites
2896 -----------------------------------------------------
2897 debug_info := '(Import_invoice 15.1a) Call get_invoice_info';
2898 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2899 AP_IMPORT_UTILITIES_PKG.Print(
2900 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2901 END IF;
2902
2903 IF (AP_IMPORT_UTILITIES_PKG.get_invoice_info(
2904 l_invoice_rec, -- IN OUT NOCOPY
2905 l_default_last_updated_by, -- IN
2906 l_default_last_update_login, -- IN
2907 l_pay_curr_invoice_amount, -- OUT NOCOPY
2908 l_payment_priority, -- OUT NOCOPY
2909 l_invoice_amount_limit, -- OUT NOCOPY
2910 l_hold_future_payments_flag, -- OUT NOCOPY
2911 l_supplier_hold_reason, -- OUT NOCOPY
2912 l_exclude_freight_from_disc, -- OUT NOCOPY /* bug 4931755 */
2913 current_calling_sequence ) <> TRUE) THEN
2914 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2915 AP_IMPORT_UTILITIES_PKG.Print(
2916 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2917 'get_invoice_info<-'||current_calling_sequence);
2918 END IF;
2919 Raise import_invoice_failure;
2920
2921 END IF;
2922
2923 -- show output values (only IF debug_switch = 'Y')
2924
2925 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2926 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
2927 '------------------> l_pay_curr_invoice_amount = '
2928 ||to_char(l_pay_curr_invoice_amount)
2929 ||' l_payment_priority = ' ||to_char(l_payment_priority)
2930 ||' l_invoice_amount_limit = ' ||to_char(l_invoice_amount_limit)
2931 ||' l_hold_future_payments_flag = '||l_hold_future_payments_flag
2932 ||' l_supplier_hold_reason = ' ||l_supplier_hold_reason );
2933 END IF;
2934
2935 -- Bug 7588730: Start: Uncommenting the code which is required to initiliaze GDF for JG.
2936 -- Bug 4014019: Commenting the call to jg_globe_flex_val due to build issues.
2937
2938 -----------------------------------------------------
2939 -- Step 15.1b
2940 -- Update global_context_code with the right
2941 -- value corresponding to flexfield JG_AP_INVOICES
2942 -----------------------------------------------------
2943 debug_info := '(Import_invoice 15.1b) Update global context code';
2944 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2945 AP_IMPORT_UTILITIES_PKG.Print(
2946 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2947 END IF;
2948
2949 -- > IN global context code in interface table
2950 -- > OUT NOCOPY global context code in base table
2951
2952
2953 IF ( jg_globe_flex_val.reassign_context_code(
2954 l_invoice_rec.global_attribute_category) <> TRUE) THEN
2955 --Bug8876668
2956 jg_globe_flex_val.reject_invalid_context_code(
2957 'APXIIMPT',
2958 AP_IMPORT_INVOICES_PKG.g_invoices_table,
2959 l_invoice_rec.invoice_id,
2960 l_default_last_updated_by,
2961 l_default_last_update_login,
2962 l_invoice_rec.global_attribute_category,
2963 l_reject_status_code,
2964 current_calling_sequence);
2965
2966 IF (l_reject_status_code <> 'Y') THEN
2967 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2968 AP_IMPORT_UTILITIES_PKG.Print(
2969 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2970 'reassign_context_code<-'||current_calling_sequence);
2971 END IF;
2972 Raise import_invoice_failure;
2973 END IF;
2974 --End of Bug8876668
2975 END IF;
2976
2977 -- Bug 7588730: End: Uncommenting the code which is required to initiliaze GDF for JG.
2978
2979 ----------------------------------------------------------
2980 -- Step 15.2
2981 -- Insert record INTO ap_invoices
2982 ----------------------------------------------------------
2983 debug_info := '(Import_invoice 15.2) Insert record INTO ap_invoices';
2984 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2985 AP_IMPORT_UTILITIES_PKG.Print(
2986 AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
2987 END IF;
2988
2989 -- Payment Request: Added p_needs_invoice_approval for payment requests
2990 IF (AP_IMPORT_UTILITIES_PKG.insert_ap_invoices(
2991 l_invoice_rec, -- IN OUT
2992 l_base_invoice_id, -- OUT NOCOPY
2993 --l_set_of_books_id, -- IN --bug 15862708
2994 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(l_invoice_rec.org_id).p_set_of_books_id, -- IN
2995 l_dbseqid, -- IN
2996 l_seqval, -- IN
2997 l_batch_id, -- IN
2998 l_pay_curr_invoice_amount, -- IN
2999 --l_approval_workflow_flag, -- IN --bug 15862708
3000 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(l_invoice_rec.org_id).p_approval_workflow_flag, -- IN
3001 p_needs_invoice_approval,
3002 /*l_add_days_settlement_date, -- IN --bug 4930111
3003 l_disc_is_inv_less_tax_flag, -- IN --bug 4931755 */ --bug 15862708
3004 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(l_invoice_rec.org_id).p_add_days_settlement_date, -- IN --bug 4930111
3005 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(l_invoice_rec.org_id).p_disc_is_inv_less_tax_flag, -- IN --bug 4931755
3006 l_exclude_freight_from_disc, -- IN --bug 4931755
3007 current_calling_sequence) <> TRUE) THEN
3008 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3009 AP_IMPORT_UTILITIES_PKG.Print(
3010 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3011 '<-'||current_calling_sequence);
3012 END IF;
3013
3014 Raise import_invoice_failure;
3015 END IF;
3016
3017 -- Set counter for created invoices
3018 l_valid_invoices_count := l_valid_invoices_count +1;
3019 l_invoices_created := l_invoices_created +1; --bug 14530013
3020 l_total_invoice_amount := l_total_invoice_amount +
3021 NVL(l_invoice_rec.no_xrate_base_amount,
3022 l_invoice_rec.invoice_amount);
3023 l_actual_invoice_total := l_actual_invoice_total +
3024 l_invoice_rec.invoice_amount;
3025
3026 g_invoice_id := l_base_invoice_id;
3027
3028 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3029 AP_IMPORT_UTILITIES_PKG.Print(
3030 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3031 '------------------> l_base_invoice_id = '
3032 || to_char(l_base_invoice_id)
3033 ||' l_valid_invoices_count = '||to_char(l_valid_invoices_count));
3034 END IF;
3035
3036 ---------------------------------------------------------------
3037 -- Step 15.3: Call AP_CREATE_PAY_SCHEDS_PKG.AP_Create_From_Terms
3038 -- Insert payment schedules FROM term
3039 ---------------------------------------------------------------
3040
3041 debug_info := '(Import_invoice 15.3) Insert payment schedules '||
3042 'from terms';
3043 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3044 AP_IMPORT_UTILITIES_PKG.Print(
3045 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3046 END IF;
3047
3048 AP_CREATE_PAY_SCHEDS_PKG.Create_payment_schedules(
3049 p_invoice_id =>l_base_invoice_id,
3050 p_terms_id =>l_invoice_rec.terms_id,
3051 p_last_updated_by =>l_invoice_rec.last_updated_by,
3052 p_created_by =>l_invoice_rec.created_by,
3053 p_payment_priority =>l_payment_priority,
3054 p_batch_id =>l_batch_id,
3055 p_terms_date =>l_invoice_rec.terms_date,
3056 p_invoice_amount =>l_invoice_rec.invoice_amount,
3057 p_pay_curr_invoice_amount =>l_pay_curr_invoice_amount,
3058 p_payment_cross_rate =>l_invoice_rec.payment_cross_rate,
3059 p_amount_for_discount =>l_invoice_rec.amount_applicable_to_discount,
3060 p_payment_method =>l_invoice_rec.payment_method_code,
3061 p_invoice_currency =>l_invoice_rec.invoice_currency_code,
3062 p_payment_currency =>l_invoice_rec.payment_currency_code,
3063 p_calling_sequence =>current_calling_sequence);
3064
3065
3066 -------------------------------------------------------------
3067 -- Step 15.4: Insert holds for this invoice.
3068 -- There are 2 holds FROM supplier site AND 1 hold FROM input
3069 -- parameter.
3070 -------------------------------------------------------------
3071 debug_info := '(Import_invoice 15.4) Insert holds for this invoice';
3072 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3073 AP_IMPORT_UTILITIES_PKG.Print(
3074 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3075 END IF;
3076
3077 IF (AP_IMPORT_UTILITIES_PKG.insert_holds(
3078 l_base_invoice_id,
3079 p_hold_code,
3080 p_hold_reason,
3081 l_hold_future_payments_flag,
3082 l_supplier_hold_reason,
3083 l_invoice_amount_limit,
3084 /*bug fix:3022381 Added the NVL condition*/
3085 nvl(l_invoice_rec.no_xrate_base_amount, -- Bug 4692091. Added ap_round_currency
3086 ap_utilities_pkg.ap_round_currency(
3087 l_invoice_rec.invoice_amount*nvl(l_invoice_rec.exchange_rate,1),
3088 l_invoice_rec.invoice_currency_code)),
3089 l_invoice_rec.last_updated_by,
3090 current_calling_sequence ) <> TRUE) THEN
3091
3092 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3093 AP_IMPORT_UTILITIES_PKG.Print(
3094 AP_IMPORT_INVOICES_PKG.g_debug_switch,'<-'||
3095 current_calling_sequence);
3096 END IF;
3097 Raise import_invoice_failure;
3098 END IF;
3099
3100 --------------------------------------------------------------
3101 -- Step 15.5:
3102 -- Create invoice lines
3103 --------------------------------------------------------------
3104 debug_info := '(Import_invoice 15.5) Create invoice lines';
3105 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3106 AP_IMPORT_UTILITIES_PKG.Print(
3107 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3108 END IF;
3109
3110
3111 AP_IMPORT_UTILITIES_PKG.Print(
3112 AP_IMPORT_INVOICES_PKG.g_debug_switch, 'SOBID is :'||l_invoice_rec.set_of_books_id);
3113
3114 debug_info := '(Import_invoice 15.5) Create invoice lines '||lines_tab.count;
3115 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3116 AP_IMPORT_UTILITIES_PKG.Print(
3117 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3118 END IF;
3119
3120 IF (AP_IMPORT_UTILITIES_PKG.create_lines(
3121 l_batch_id,
3122 l_base_invoice_id,
3123 lines_tab, --bug 15862708
3124 /*l_base_currency_code,
3125 l_set_of_books_id,
3126 l_approval_workflow_flag, */ --bug 15862708
3127 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(l_invoice_rec.org_id).p_base_currency_code,
3128 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(l_invoice_rec.org_id).p_set_of_books_id,
3129 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(l_invoice_rec.org_id).p_approval_workflow_flag,
3130 l_invoice_rec.tax_only_flag,
3131 l_invoice_rec.tax_only_rcv_matched_flag,
3132 l_default_last_updated_by,
3133 l_default_last_update_login,
3134 current_calling_sequence) <> TRUE) THEN
3135
3136 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3137 AP_IMPORT_UTILITIES_PKG.Print(
3138 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3139 'create_lines<-'||current_calling_sequence);
3140 Raise import_invoice_failure;
3141 END IF;
3142 END IF;
3143
3144 --------------------------------------------------------------
3145 -- Step 15.6:
3146 -- Execute the Argentine/Colombian defaulting procedure
3147 --------------------------------------------------------------
3148 debug_info := '(Import_invoice 15.6) Execute the '||
3149 'Argentine/Colombian defaulting procedure';
3150 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3151 AP_IMPORT_UTILITIES_PKG.Print(
3152 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3153 END IF;
3154
3155 --Bugfix:4674229
3156 DECLARE
3157 l_awt_success Varchar2(1000);
3158 BEGIN
3159 AP_EXTENDED_WITHHOLDING_PKG.Ap_Ext_Withholding_Default(
3160 P_Invoice_Id => l_base_invoice_id,
3161 P_Inv_Line_Num => NULL,
3162 P_Inv_Dist_Id => NULL,
3163 P_calling_module => 'IMPORT',
3164 P_Parent_Dist_Id => NULL,
3165 P_Awt_Success => l_awt_success);
3166 IF (l_awt_success <> 'SUCCESS') THEN
3167 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3168 AP_IMPORT_UTILITIES_PKG.Print(
3169 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3170 'ap_ext_withholding_default<-'||current_calling_sequence);
3171 END IF;
3172 Raise import_invoice_failure;
3173 END IF;
3174 END;
3175
3176 --------------------------------------------------------------
3177 -- Step 15.7:
3178 -- If the user intention is to import TAX, eTax will be call to
3179 -- import the lines previous to any prepayment application.
3180 -- If the user intention is calculate and there is a prepayment
3181 -- application tax will be calculated during the prepayment
3182 -- application for the whole invoice. If there is no prepayment
3183 -- application, eTax will be called to calculate.
3184 --------------------------------------------------------------
3185
3186 debug_info := '(Import_invoice 15.7) Call import before any prepayment '||
3187 'application if the user intention is to import TAX';
3188
3189 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3190 AP_IMPORT_UTILITIES_PKG.Print(
3191 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3192 END IF;
3193
3194
3195 IF ( l_invoice_rec.calc_tax_during_import_flag = 'N') THEN
3196 --------------------------------------------------------------
3197 -- Step 15.7a. Call import document with tax.
3198 -- If it is a tax only invoice and has a receipt matched tax
3199 -- line, call calculate instead of import and call determine
3200 -- recovery right after because tax-only lines had been created
3201 --------------------------------------------------------------
3202 debug_info := '(Import_invoice 15.7a) User intention is to import TAX';
3203
3204 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3205 AP_IMPORT_UTILITIES_PKG.Print(
3206 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3207 END IF;
3208
3209 IF ( NVL(l_invoice_rec.tax_only_rcv_matched_flag, 'N') = 'Y') THEN
3210 -----------------------------------------------------------------
3211 -- Step 15.7b. Invoice is tax only and is matched to receipt
3212 -- call to calculate tax is required
3213 -----------------------------------------------------------------
3214
3215 debug_info := '(Import_invoice 15.7b) Invoice is tax only and is matched to receipt '||
3216 'so calculate shoould be called instead of import';
3217
3218 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3219 AP_IMPORT_UTILITIES_PKG.Print(
3220 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3221 END IF;
3222
3223 IF NOT (ap_etax_pkg.calling_etax(
3224 p_invoice_id => l_base_invoice_id,
3225 p_calling_mode => 'CALCULATE IMPORT',
3226 p_override_status => NULL,
3227 p_line_number_to_delete => NULL,
3228 P_Interface_Invoice_Id => l_invoice_rec.invoice_id,
3229 p_all_error_messages => 'Y',
3230 p_error_code => l_error_code,
3231 p_calling_sequence => current_calling_sequence)) THEN
3232
3233 -- If the call to calculate fails, the import process will
3234 -- fail. In this case the invoice cannot be imported since
3235 -- user is trying to import tax lines
3236 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3237 AP_IMPORT_UTILITIES_PKG.Print(
3238 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3239 'ap_etax_pkg.calling_etax(CALCULATE IMPORT)<-'||current_calling_sequence);
3240
3241 END IF;
3242
3243 -- If the API fails because the call to the eTax service fails
3244 -- the following code will get the messages from the message
3245 -- stack
3246
3247 IF (l_error_code IS NOT NULL) THEN
3248 -- Print the error returned from the service even if the debug
3249 -- mode is off
3250 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
3251
3252 ELSE
3253 -- If the l_error_code is null is because the service returned
3254 -- more than one error. The calling module will need to get
3255 -- them from the message stack
3256 LOOP
3257 l_error_code := FND_MSG_PUB.Get;
3258 IF l_error_code IS NULL THEN
3259 EXIT;
3260 ELSE
3261 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
3262 END IF;
3263 END LOOP;
3264 END IF; -- if l_error_code is not null
3265 --RAISE import_invoice_failure; bug 15862708
3266 RAISE calculate_tax_failure;
3267 END IF;
3268
3269 ELSE -- tax_only_rcv_matched_flag is N. We will call import tax service
3270 --------------------------------------------------------------
3271 -- Step 15.7c. For any other case call import document with tax.
3272 -- In this case could be necesary to populate a pseudo trx line
3273 -- in the global temp tables to pass to eTax the additional
3274 -- info in the tax line. This is handled in the population of
3275 -- the temp tables in the validation API since we are using the
3276 -- same information provided at that time.
3277 --------------------------------------------------------------
3278
3279 IF (NVL(l_invoice_rec.tax_only_flag, 'N') = 'Y') THEN -- tax_only_flag is Y.
3280
3281 debug_info := '(Import_invoice 15.7c) Invoice is tax only '||
3282 'but not matched to receipt so call IMPORT TAX';
3283
3284 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3285 AP_IMPORT_UTILITIES_PKG.Print(
3286 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3287 END IF;
3288
3289 IF NOT (ap_etax_pkg.calling_etax(
3290 p_invoice_id => l_base_invoice_id,
3291 p_calling_mode => 'IMPORT INTERFACE',
3292 p_override_status => NULL,
3293 p_line_number_to_delete => NULL,
3294 P_Interface_Invoice_Id => l_invoice_rec.invoice_id,
3295 p_all_error_messages => 'Y',
3296 p_error_code => l_error_code,
3297 p_calling_sequence => current_calling_sequence)) THEN
3298
3299 -- If the import of tax fails, the import process will fail.
3300 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3301 AP_IMPORT_UTILITIES_PKG.Print(
3302 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3303 'ap_etax_pkg.calling_etax(IMPORT INTERFACE)<-'||current_calling_sequence);
3304
3305 END IF;
3306
3307 -- If the API fails because the call to the eTax service fails
3308 -- the following code will get the messages from the message
3309 -- stack
3310
3311 IF (l_error_code IS NOT NULL) THEN
3312 -- Print the error returned from the service even if the debug
3313 -- mode is off
3314 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
3315
3316 ELSE
3317 -- If the l_error_code is null is because the service returned
3318 -- more than one error. The calling module will need to get
3319 -- them from the message stack
3320 LOOP
3321 l_error_code := FND_MSG_PUB.Get;
3322 IF l_error_code IS NULL THEN
3323 EXIT;
3324 ELSE
3325 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
3326 END IF;
3327 END LOOP;
3328 END IF; -- if l_error_code is not null
3329 --RAISE import_invoice_failure; --bug 15862708
3330 RAISE calculate_tax_failure;
3331 END IF; -- end of call to IMPORT INTERFACE
3332 END IF; -- End of tax_only_flag
3333 END IF; -- End of if for tax_only_rcv_matched_flag
3334
3335 --------------------------------------------------------------------
3336 -- Step 15.7d. Call determine_recovery if the invoice is tax-only.
3337 --------------------------------------------------------------------
3338
3339 IF (NVL(l_invoice_rec.tax_only_flag, 'N') = 'Y') THEN
3340 debug_info := '(Import_invoice 15.7d) Invoice is tax only so we will '||
3341 'call determine_recovery';
3342
3343 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3344 AP_IMPORT_UTILITIES_PKG.Print(
3345 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3346 END IF;
3347
3348 IF NOT (ap_etax_pkg.calling_etax(
3349 p_invoice_id => l_base_invoice_id,
3350 p_calling_mode => 'DISTRIBUTE IMPORT',
3351 p_override_status => NULL,
3352 p_line_number_to_delete => NULL,
3353 P_Interface_Invoice_Id => l_invoice_rec.invoice_id,
3354 p_all_error_messages => 'Y',
3355 p_error_code => l_error_code,
3356 p_calling_sequence => current_calling_sequence)) THEN
3357
3358 -- If the call to determine recovery fails, the import process
3359 -- will fail. In this case the invoice cannot be imported since
3360 -- user is trying to import tax lines
3361 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3362 AP_IMPORT_UTILITIES_PKG.Print(
3363 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3364 'ap_etax_pkg.calling_etax(DISTRIBUTE IMPORT)<-'||current_calling_sequence);
3365
3366 END IF;
3367
3368 -- If the API fails because the call to the eTax service fails
3369 -- the following code will get the messages from the message
3370 -- stack
3371
3372 IF (l_error_code IS NOT NULL) THEN
3373 -- Print the error returned from the service even if the debug
3374 -- mode is off
3375 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
3376
3377 ELSE
3378 -- If the l_error_code is null is because the service returned
3379 -- more than one error. The calling module will need to get
3380 -- them from the message stack
3381 LOOP
3382 l_error_code := FND_MSG_PUB.Get;
3383 IF l_error_code IS NULL THEN
3384 EXIT;
3385 ELSE
3386 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
3387 END IF;
3388 END LOOP;
3389 END IF; -- if l_error_code is not null
3390 --RAISE import_invoice_failure; --bug 15862708
3391 RAISE calculate_tax_failure;
3392
3393 END IF;
3394 END IF; -- call distribute if tax-only invoice
3395
3396 -- bug 10158760: start
3397 ------------------------------------------------------------------------
3398 -- Step 15.7e. Call import document with tax if the invoice is tax line.
3399 ------------------------------------------------------------------------
3400 -- if the invoice has tax line we need to call
3401 -- ZX_API_PUB.IMPORT_DOCUMENT_WITH_TAX (thru' 'CALCULATE')
3402 IF (NVL(l_invoice_rec.tax_only_flag, 'N') = 'N' AND
3403 NVL(l_invoice_rec.tax_only_rcv_matched_flag, 'N') = 'N' AND
3404 -- AP_IMPORT_INVOICES_PKG.g_inv_has_tax_line = 'Y' --bug 15862708
3405 l_invoice_rec.inv_has_tax_line = 'Y'
3406 ) THEN
3407
3408 debug_info := '(Import_invoice 15.7e) Invoice is tax line '||
3409 'call IMPORT INTERFACE';
3410
3411 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3412 AP_IMPORT_UTILITIES_PKG.Print(
3413 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3414 END IF;
3415
3416 IF NOT (ap_etax_pkg.calling_etax(
3417 p_invoice_id => l_base_invoice_id,
3418 p_calling_mode => 'CALCULATE',
3419 p_override_status => NULL,
3420 p_line_number_to_delete => NULL,
3421 P_Interface_Invoice_Id => l_invoice_rec.invoice_id,
3422 p_all_error_messages => 'Y',
3423 p_error_code => l_error_code,
3424 p_calling_sequence => current_calling_sequence)) THEN
3425
3426 -- If the import of tax fails, the import process will fail.
3427 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3428 AP_IMPORT_UTILITIES_PKG.Print(
3429 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3430 'ap_etax_pkg.calling_etax(IMPORT INTERFACE)<-'||current_calling_sequence);
3431
3432 END IF;
3433
3434 -- If the API fails because the call to the eTax service fails
3435 -- the following code will get the messages from the message
3436 -- stack
3437
3438 IF (l_error_code IS NOT NULL) THEN
3439 -- Print the error returned from the service even if the debug
3440 -- mode is off
3441 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
3442
3443 ELSE
3444 -- If the l_error_code is null is because the service returned
3445 -- more than one error. The calling module will need to get
3446 -- them from the message stack
3447 LOOP
3448 l_error_code := FND_MSG_PUB.Get;
3449 IF l_error_code IS NULL THEN
3450 EXIT;
3451 ELSE
3452 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
3453 END IF;
3454 END LOOP;
3455 END IF; -- if l_error_code is not null
3456 --RAISE import_invoice_failure; --bug 15862708
3457 RAISE calculate_tax_failure;
3458 END IF; -- end of call to IMPORT INTERFACE
3459 END IF; -- end of IF AP_IMPORT_INVOICES_PKG.g_inv_has_tax_line = 'Y'
3460 -- bug 10158760: end
3461
3462 END IF; -- calc_tax_during_import_flag is N. User expects import TAX
3463 -- to be called
3464
3465 --------------------------------------------------------------
3466 -- Step 15.8:
3467 -- If the invoice does not have prepayment applications, call
3468 -- calculate tax if the user intention was to calculate. The case
3469 -- where the user wants to import was handle previously.
3470 -- Also verify that the invoice is not AWT or INTEREST previous to
3471 -- calling etax to calculate.
3472 --------------------------------------------------------------
3473
3474 debug_info := '(Import_invoice 15.8) Call calculate or the prepayment '||
3475 'application';
3476
3477 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3478 AP_IMPORT_UTILITIES_PKG.Print(
3479 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3480 END IF;
3481
3482 IF ( l_invoice_rec.prepay_num IS NULL AND
3483 l_invoice_rec.prepay_line_num IS NULL AND
3484 l_invoice_rec.prepay_apply_amount IS NULL ) THEN
3485
3486 IF ( l_invoice_rec.invoice_type_lookup_code
3487 NOT IN ('AWT', 'INTEREST')) THEN
3488
3489 IF ( l_invoice_rec.calc_tax_during_import_flag = 'Y') THEN
3490 --------------------------------------------------------------
3491 -- Step 15.8a: calc_tax_during_import_flag = Y. User intention is
3492 -- calculate. To minimize the calls to the eTax service, we will exclude
3493 -- calling tax for invoices that will have any prepayment application.
3494 -- Tax calculation will be done during the prepayment application
3495 -- for those invoices.
3496 -- Call calculate tax.
3497 -- The big difference between this call here and the one done
3498 -- during the prepayment application is the source of the data.
3499 -- Here we will use the pl/sql tables populated during validation
3500 -- in the prepayment case, the API will select the lines from the
3501 -- ap_invoice_lines_all table
3502 --------------------------------------------------------------
3503 debug_info := '(Import_invoice 15.8a) calc_tax_during_import_flag = Y, Call calculate tax';
3504
3505 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3506 AP_IMPORT_UTILITIES_PKG.Print(
3507 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3508 END IF;
3509
3510 IF NOT (ap_etax_pkg.calling_etax(
3511 p_invoice_id => l_base_invoice_id,
3512 p_calling_mode => 'CALCULATE IMPORT',
3513 p_override_status => NULL,
3514 p_line_number_to_delete => NULL,
3515 P_Interface_Invoice_Id => l_invoice_rec.invoice_id,
3516 p_all_error_messages => 'Y',
3517 p_error_code => l_error_code,
3518 p_calling_sequence => current_calling_sequence)) THEN
3519
3520 -- If the calculation of tax fails the invoice will be imported
3521 -- anyway, and the error(s) will be included in the log file.
3522 -- Tax can be later be calculated from the invoice workbench or
3523 -- during the validation of the invoice.
3524
3525 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3526 AP_IMPORT_UTILITIES_PKG.Print(
3527 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3528 'ap_etax_pkg.calling_etax(CALCULATE IMPORT)<-'||current_calling_sequence);
3529
3530 END IF;
3531
3532 -- If the API fails because the call to the eTax service fails
3533 -- the following code will get the messages from the message
3534 -- stack
3535
3536 IF (l_error_code IS NOT NULL) THEN
3537 -- Print the error returned from the service even if the debug
3538 -- mode is off
3539 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
3540
3541 ELSE
3542 -- If the l_error_code is null is because the service returned
3543 -- more than one error. The calling module will need to get
3544 -- them from the message stack
3545 LOOP
3546 l_error_code := FND_MSG_PUB.Get;
3547
3548 IF l_error_code IS NULL THEN
3549 EXIT;
3550 ELSE
3551 AP_IMPORT_UTILITIES_PKG.Print('Y', l_error_code);
3552 END IF;
3553 END LOOP;
3554 END IF; -- if l_error_code is not null
3555 END IF; -- end call to ap_etax_pkg.calling_etax
3556 END IF; -- if for the calc_tax_during_import_flag
3557 END IF; -- invoice is not AWT or INTEREST. There is no tax
3558 -- calculation for invoices of this type
3559
3560 ELSE -- if invoice has prepayment applications tax will be called in
3561 -- the prepay application package.
3562 /* Bug 9668395 Added If Condition to Reject Prepayment Application on Credit/Debit*/
3563 IF (l_invoice_rec.invoice_type_lookup_code
3564 NOT IN ('CREDIT','DEBIT')) THEN
3565
3566 --------------------------------------------------------------
3567 -- Step 15.8b:
3568 -- Apply Prepayment(s) to invoice.
3569 -- Fix using invoice record
3570 --------------------------------------------------------------
3571 debug_info := '(Import_invoice 15.8b) Apply Prepayment(s) to invoice.';
3572
3573 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3574 AP_IMPORT_UTILITIES_PKG.Print(
3575 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3576 END IF;
3577
3578
3579 SELECT sum(nvl(amount_remaining,0))
3580 INTO l_inv_amount_unpaid
3581 FROM ap_payment_schedules
3582 WHERE invoice_id = l_base_invoice_id;
3583
3584 IF (nvl(l_inv_amount_unpaid,0) < l_invoice_rec.prepay_apply_amount) THEN
3585 l_amount_to_apply := l_inv_amount_unpaid;
3586 ELSE
3587 l_amount_to_apply := l_invoice_rec.prepay_apply_amount;
3588 END IF;
3589 --bug 16092065 added to pass prepay columns instead of local variables to collection
3590 -- variables
3591 -- Prepayments project - 11ix
3592 AP_PREPAY_PKG.APPLY_PREPAY_IMPORT(
3593 p_prepay_invoice_id => l_invoice_rec.PREPAY_INV_ID,
3594 p_prepay_num => l_invoice_rec.prepay_num,
3595 p_prepay_line_num => l_invoice_rec.prepay_line_num,
3596 p_prepay_apply_amount => l_amount_to_apply,
3597 p_prepay_case_name => l_invoice_rec.PREPAY_CASE_NAME,
3598 p_import_invoice_id => l_invoice_rec.invoice_id,
3599 p_request_id => p_conc_request_id,
3600 p_invoice_id => l_base_invoice_id,
3601 p_vendor_id => l_invoice_rec.vendor_id,
3602 p_prepay_gl_date => l_invoice_rec.prepay_gl_date,
3603 p_prepay_period_name => l_invoice_rec.PREPAY_PERIOD_NAME,
3604 p_prepay_included => l_invoice_rec.invoice_includes_prepay_flag,
3605 p_user_id => l_default_last_updated_by,
3606 p_last_update_login => l_default_last_update_login,
3607 p_calling_sequence => current_calling_sequence,
3608 p_prepay_appl_log => l_prepay_appl_log);
3609 ELSE
3610 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections
3611 (AP_IMPORT_INVOICES_PKG.g_invoices_table,
3612 l_invoice_rec.invoice_id,
3613 'AP_INV_CANNOT_APPLY_CRDR_IMPRT',
3614 l_default_last_updated_by,
3615 l_default_last_update_login,
3616 current_calling_sequence) <> TRUE) THEN
3617
3618 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3619
3620 AP_IMPORT_UTILITIES_PKG.Print(
3621 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3622 'insert_rejections<- '||current_calling_sequence);
3623 END IF;
3624
3625 Raise import_invoice_failure;
3626 END IF; --Insert rejections
3627 l_invoice_status := 'N';
3628
3629 END IF;
3630
3631 END IF;
3632
3633 --------------------------------------------------------------
3634 -- Step 15.9:
3635 -- Update the invoice amount if flag add_tax_to_inv_amt_flag is
3636 -- set
3637 --------------------------------------------------------------
3638 debug_info := '(Import_invoice 15.9) Update the invoice amount '||
3639 'if flag add_tax_to_inv_amt_flag is set with the '||
3640 'total of the exclusive tax lines created for the '||
3641 'invoice';
3642
3643 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3644 AP_IMPORT_UTILITIES_PKG.Print(
3645 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3646 END IF;
3647
3648 IF (NVL(l_invoice_rec.add_tax_to_inv_amt_flag, 'N') = 'Y') THEN
3649
3650 SELECT ai.invoice_amount,
3651 (SELECT NVL(SUM(NVL(ail.amount, 0)), 0)
3652 FROM ap_invoice_lines_all ail
3653 WHERE ail.invoice_id = l_base_invoice_id
3654 AND ail.line_type_lookup_code = 'TAX'
3655 --bug 12727643 start
3656 and (ail.PREPAY_INVOICE_ID is null or
3657 (ail.PREPAY_INVOICE_ID is not null
3658 and ail.INVOICE_INCLUDES_PREPAY_FLAG='Y' ))
3659 --bug 12727643 ends
3660 )
3661 INTO l_inv_hdr_amount, l_exclusive_tax_amount
3662 FROM ap_invoices_all ai
3663 WHERE ai.invoice_id = l_base_invoice_id;
3664
3665 --Bug 8513242 Added code to add tax amount to the control amount of the batch created.
3666 l_actual_invoice_total := l_actual_invoice_total + l_exclusive_tax_amount;
3667 --End Bug 8513242
3668
3669 l_payment_status_flag := AP_INVOICES_UTILITY_PKG.get_payment_status (l_base_invoice_id);
3670
3671 AP_PAYMENT_SCHEDULES_PKG.adjust_pay_schedule(
3672 X_invoice_id => l_base_invoice_id,
3673 X_invoice_amount => l_inv_hdr_amount + l_exclusive_tax_amount ,
3674 X_payment_status_flag => l_payment_status_flag,
3675 X_invoice_type_lookup_code => l_invoice_rec.invoice_type_lookup_code,
3676 X_last_updated_by => l_default_last_updated_by,
3677 X_message1 => l_message1,
3678 X_message2 => l_message2,
3679 X_reset_match_status => l_reset_match_status,
3680 X_liability_adjusted_flag => l_liability_adjusted_flag,
3681 X_calling_sequence => 'APXIIMPT',
3682 X_calling_mode => 'APXIIMPT',
3683 X_revalidate_ps => l_revalidate_ps);
3684
3685 -- Bug 7282839 start
3686 -- Calculate the tax amount in base currency
3687 l_base_exclusive_tax_amount := 0;
3688 IF (AP_IMPORT_INVOICES_PKG.tab_get_info_rec(l_invoice_rec.org_id).p_base_currency_code <> l_invoice_rec.invoice_currency_code) THEN
3689
3690 -- Retreive the exchange rate for the invoice from record
3691 IF ( l_invoice_rec.exchange_rate IS NOT NULL) THEN
3692 l_exchange_rate := l_invoice_rec.exchange_rate;
3693 ELSE
3694 -- Retreive exchange rate from ap_invoices_all for the invoice
3695 select exchange_rate into l_exchange_rate
3696 from ap_invoices_all
3697 where invoice_id=l_base_invoice_id;
3698 END IF;
3699
3700 IF ( l_exchange_rate IS NOT NULL
3701 AND AP_IMPORT_INVOICES_PKG.tab_get_info_rec(l_invoice_rec.org_id).p_base_currency_code IS NOT NULL ) THEN
3702 l_base_exclusive_tax_amount :=AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
3703 l_exclusive_tax_amount * l_exchange_rate,
3704 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(l_invoice_rec.org_id).p_base_currency_code);
3705 END IF;
3706 END IF;
3707
3708 UPDATE ap_invoices_all ai
3709 SET ai.invoice_amount = ai.invoice_amount + l_exclusive_tax_amount,
3710 ai.amount_applicable_to_discount = ai.amount_applicable_to_discount + l_exclusive_tax_amount,
3711 ai.base_amount=ai.base_amount+l_base_exclusive_tax_amount
3712 WHERE ai.invoice_id = l_base_invoice_id;
3713
3714 -- Bug 7282839 end
3715
3716 IF ( l_invoice_rec.payment_cross_rate is NOT NULL) THEN
3717
3718 UPDATE ap_invoices_all ai
3719 SET ai.pay_curr_invoice_amount = ai.pay_curr_invoice_amount +
3720 gl_currency_api.convert_amount(
3721 ai.invoice_currency_code,
3722 ai.payment_currency_code,
3723 ai.payment_cross_rate_date,
3724 ai.payment_cross_rate_type,
3725 l_exclusive_tax_amount)
3726 WHERE ai.invoice_id = l_base_invoice_id;
3727
3728 END IF;
3729
3730
3731 END IF;
3732
3733 --------------------------------------------------------------
3734 -- Step 15.10:
3735 -- Delete the contents of the l_invoice_lines_tab Lines Table
3736 --------------------------------------------------------------
3737 debug_info := '(Import_invoice 15.10) Delete the contents of '||
3738 'the l_invoice_lines_tab Lines Table';
3739
3740 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3741 AP_IMPORT_UTILITIES_PKG.Print(
3742 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3743 END IF;
3744
3745 --l_invoice_lines_tab.DELETE;
3746
3747 --------------------------------------------------------------
3748 -- Step 15.11:
3749 -- Delete the contents of the eTax global temporary tables
3750 --------------------------------------------------------------
3751 debug_info := '(Import_invoice 15.11) Delete the contents of '||
3752 'the eTax global temp tables';
3753
3754 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3755 AP_IMPORT_UTILITIES_PKG.Print(
3756 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3757 END IF;
3758 /* --commented for bug 16656238
3759 BEGIN DELETE zx_trx_headers_gt;
3760 EXCEPTION WHEN NO_DATA_FOUND THEN null;
3761 END;
3762
3763 BEGIN DELETE zx_transaction_lines_gt;
3764 EXCEPTION WHEN NO_DATA_FOUND THEN null;
3765 END;
3766
3767 BEGIN DELETE zx_import_tax_lines_gt;
3768 EXCEPTION WHEN NO_DATA_FOUND THEN null;
3769 END;
3770
3771 BEGIN DELETE zx_trx_tax_link_gt;
3772 EXCEPTION WHEN NO_DATA_FOUND THEN null;
3773 END;
3774 */
3775
3776 END IF; -- Invoice Status = 'Y' before call to get_invoice_info
3777
3778 ----------------------------------------------------------------
3779 -- Step 16. Retropricing.
3780 ----------------------------------------------------------------
3781 ELSE
3782
3783 debug_info := '(Import Invoice 16) Import_Retroprice_Adjustments';
3784 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3785 AP_IMPORT_UTILITIES_PKG.Print(
3786 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3787 END IF;
3788 --
3789 IF (AP_RETRO_PRICING_PKG.Import_Retroprice_Adjustments(
3790 l_invoice_rec, -- IN p_instr_header_rec
3791 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(nvl(p_org_id,l_invoice_rec.org_id)).p_base_currency_code, -- IN
3792 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(nvl(p_org_id,l_invoice_rec.org_id)).p_multi_currency_flag, -- IN
3793 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(nvl(p_org_id,l_invoice_rec.org_id)).p_set_of_books_id, -- IN
3794 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(nvl(p_org_id,l_invoice_rec.org_id)).p_default_exchange_rate_type, -- IN
3795 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(nvl(p_org_id,l_invoice_rec.org_id)).p_make_rate_mandatory_flag, -- IN
3796 l_invoice_rec.gl_date, -- IN
3797 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(nvl(p_org_id,l_invoice_rec.org_id)).p_gl_date_from_receipt_flag, -- IN
3798 l_invoice_rec.positive_price_tolerance, -- IN
3799 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(nvl(p_org_id,l_invoice_rec.org_id)).p_pa_installed, -- IN
3800 l_invoice_rec.qty_tolerance, -- IN
3801 l_invoice_rec.max_qty_ord_tolerance, -- IN
3802 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(nvl(p_org_id,l_invoice_rec.org_id)).p_base_min_acct_unit, -- IN
3803 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(nvl(p_org_id,l_invoice_rec.org_id)).p_base_precision, -- IN
3804 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(nvl(p_org_id,l_invoice_rec.org_id)).p_chart_of_accounts_id, -- IN
3805 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(nvl(p_org_id,l_invoice_rec.org_id)).p_freight_code_combination_id, -- IN
3806 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(nvl(p_org_id,l_invoice_rec.org_id)).p_purch_encumbrance_flag, -- IN
3807 AP_IMPORT_INVOICES_PKG.tab_get_info_rec(nvl(p_org_id,l_invoice_rec.org_id)).p_calc_user_xrate, -- IN
3808 l_default_last_updated_by, -- IN
3809 l_default_last_update_login, -- IN
3810 l_invoice_status, -- OUT instr_status_flag
3811 l_valid_invoices_count, -- OUT p_invoices_count
3812 l_total_invoice_amount, -- OUT p_invoices_total
3813 current_calling_sequence) <> TRUE) THEN
3814 --
3815 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3816 AP_IMPORT_UTILITIES_PKG.Print(
3817 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3818 'Import_Retroprice_Adjustments<-'||current_calling_sequence);
3819 END IF;
3820 Raise import_invoice_failure;
3821 --
3822 END IF;
3823
3824
3825 --
3826 -- NOTE : The logic based on l_actual_invoice_total doesn't make sense.
3827 -- l_total_invoices_amount -- is the out parameter for import_invoices
3828 -- l_actual_invoice_total -- is used in
3829 -- AP_IMPORT_UTILITIES_PKG.Insert_ap_batches and
3830 -- AP_IMPORT_UTILITIES_PKG.Update_ap_batches.
3831 -- Ideally they shud be the same.
3832 l_actual_invoice_total := l_total_invoice_amount;
3833 l_invoices_created := l_valid_invoices_count; --bug 14530013
3834 --
3835 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3836 AP_IMPORT_UTILITIES_PKG.Print(
3837 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3838 '------------------> l_instruction_id = '
3839 || to_char(l_invoice_rec.invoice_id));
3840 END IF;
3841 --
3842 END IF; --Retropricing
3843 --veramach bug 7121842 start
3844 EXCEPTION
3845 WHEN import_invoice_failure THEN
3846 l_invoice_status := 'N';
3847
3848 WHEN calculate_tax_failure THEN --bug 15862708
3849 l_invoice_status := 'Y';
3850 END;
3851 --veramach bug 7121842 end
3852 -------------------------------------------------
3853 -- Step 17
3854 -- Change temporary status in ap_invoice_interface
3855 -------------------------------------------------
3856 BEGIN--veramach bug 7121842
3857 IF (l_invoice_status = 'N') THEN
3858 -----------------------------------------------------
3859 -- Step 17.1. Change the invoice status to 'REJECTING'
3860 -----------------------------------------------------
3861 debug_info := '(Import_invoice 17.1) Change the invoice status to '||
3862 'REJECTING';
3863 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3864 AP_IMPORT_UTILITIES_PKG.Print(
3865 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3866 END IF;
3867
3868 IF (AP_IMPORT_UTILITIES_PKG.change_invoice_status(
3869 'REJECTING',
3870 l_invoice_rec.invoice_id,
3871 current_calling_sequence) <> TRUE) THEN
3872 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3873 AP_IMPORT_UTILITIES_PKG.Print(
3874 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3875 'change_invoice_status<-'||current_calling_sequence);
3876 END IF;
3877 Raise import_invoice_failure;
3878 END IF;
3879 ELSE
3880 ------------------------------------------------------
3881 -- Step 17.2 Change the invoice status to 'PROCESSING'
3882 ------------------------------------------------------
3883 debug_info := '(Import_invoice 17.2) Change the invoice status to '||
3884 'PROCESSING';
3885 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3886 AP_IMPORT_UTILITIES_PKG.Print(
3887 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3888 END IF;
3889
3890 IF (AP_IMPORT_UTILITIES_PKG.change_invoice_status(
3891 'PROCESSING',
3892 l_invoice_rec.invoice_id,
3893 current_calling_sequence) <> TRUE) THEN
3894 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3895 AP_IMPORT_UTILITIES_PKG.Print(
3896 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3897 'change_invoice_status<-'||current_calling_sequence);
3898 END IF;
3899 Raise import_invoice_failure;
3900 END IF;
3901 END IF;
3902 --veramach bug 7121842 start
3903 EXCEPTION
3904 WHEN import_invoice_failure THEN
3905 NULL;
3906 END;
3907 --veramach bug 7121842 end
3908 l_old_org_id := nvl(l_invoice_rec.org_id, nvl(p_org_id, NULL));
3909
3910 ELSE
3911 debug_info := '(Import_invoice 17.1) Change the status to '||
3912 'REJECTING for invoice rejected by validate_default_import '
3913 || l_invoice_rec.invoice_id;
3914
3915 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3916 AP_IMPORT_UTILITIES_PKG.Print(
3917 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3918 END IF;
3919
3920 IF (AP_IMPORT_UTILITIES_PKG.change_invoice_status(
3921 'REJECTING',
3922 l_invoice_rec.invoice_id,
3923 current_calling_sequence) <> TRUE) THEN
3924 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3925 AP_IMPORT_UTILITIES_PKG.Print(
3926 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3927 'change_invoice_status<-'||current_calling_sequence);
3928 END IF;
3929 Raise import_invoice_failure;
3930 END IF;
3931 END IF;
3932 IF LINES_TAB.count>0 THEN
3933 LINES_TAB.delete;
3934 END IF;
3935 END LOOP; -- invoice LOOP
3936 END IF;
3937
3938 debug_info := '(Import_invoice -->Execution completed for second loop:'
3939 ||to_char(sysdate,'YY-MON-DD HH24:MI:SS');
3940
3941 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3942 AP_IMPORT_UTILITIES_PKG.Print(
3943 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3944 END IF;
3945
3946 debug_info := '(Import_invoice) CLOSE import_invoices';
3947 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3948 AP_IMPORT_UTILITIES_PKG.Print(
3949 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3950 END IF;
3951 --bug 15862708
3952 -- Bug 4145391
3953 /* IF (p_group_id IS NULL) THEN
3954 CLOSE import_invoices;
3955 ELSE
3956 CLOSE import_invoices_group;
3957 END IF;
3958 */
3959 ---------------------------------------------------------------------
3960 -- Step18
3961 -- Create batch IF batch_control is on AND has invoices created.
3962 -- Create batch only the first time, in subsequent commit cycles
3963 -- do not try to create the batch again.
3964 ---------------------------------------------------------------------
3965 debug_info := '(Import_invoice 18a) Get/Initialize batch name';
3966 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3967 AP_IMPORT_UTILITIES_PKG.Print(
3968 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3969 END IF;
3970
3971 BEGIN--veramach bug 7121842
3972 IF (p_batch_name IS NULL AND
3973 l_batch_name IS NULL AND
3974 NVL(l_batch_control_flag, 'N') = 'Y' AND
3975 l_valid_invoices_count > 0) THEN
3976 IF ( AP_IMPORT_UTILITIES_PKG.get_auto_batch_name(
3977 p_source,
3978 l_batch_name,
3979 current_calling_sequence) <> TRUE ) THEN
3980 Raise import_invoice_failure;
3981 END IF;
3982
3983 ELSIF (p_batch_name IS NOT NULL AND
3984 l_batch_name IS NULL AND
3985 NVL(l_batch_control_flag, 'N') = 'Y' AND
3986 l_valid_invoices_count > 0) THEN
3987 l_batch_name := p_batch_name;
3988 END IF;
3989
3990 IF (NVL(l_batch_control_flag,'N') = 'Y' AND
3991 (l_batch_id is NOT NULL) AND
3992 (l_batch_name IS NOT NULL) AND
3993 (l_batch_exists_flag = 'N') AND
3994 (l_valid_invoices_count >0 ) AND
3995 (l_batch_type = 'NEW BATCH')) THEN
3996
3997 debug_info := '(Import_invoice 18b) Create ap_batches';
3998 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3999 AP_IMPORT_UTILITIES_PKG.Print(
4000 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4001 END IF;
4002
4003 IF (AP_IMPORT_UTILITIES_PKG.Insert_ap_batches(
4004 l_batch_id,
4005 l_batch_name,
4006 l_invoice_rec.invoice_currency_code,
4007 l_invoice_rec.payment_currency_code,
4008 l_valid_invoices_count,-- bug1721820
4009 l_actual_invoice_total,-- bug1721820
4010 l_default_last_updated_by,
4011 current_calling_sequence) <> TRUE) THEN
4012 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4013 AP_IMPORT_UTILITIES_PKG.Print(
4014 AP_IMPORT_INVOICES_PKG.g_debug_switch,
4015 'Insert_ap_batches<-'||current_calling_sequence);
4016 END IF;
4017 Raise import_invoice_failure;
4018 END IF;
4019
4020 debug_info := '(Import_invoice 18c) Set batch exists flag to Y';
4021 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4022 AP_IMPORT_UTILITIES_PKG.Print(
4023 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4024 END IF;
4025
4026 l_batch_exists_flag := 'Y';
4027
4028 ELSIF --(NVL(l_batch_control_flag,'N') = 'Y' AND --bug 15862708
4029 (NVL(AP_IMPORT_INVOICES_PKG.tab_get_info_rec(nvl(p_org_id,l_invoice_rec.org_id)).p_batch_control_flag,'N') = 'Y' AND
4030 (l_batch_id is NOT NULL) AND
4031 (l_batch_name is NOT NULL) AND
4032 ((l_batch_exists_flag = 'Y') OR
4033 (l_batch_type = 'OLD BATCH')) AND
4034 (l_valid_invoices_count >0 )) THEN
4035
4036 debug_info := '(Import_invoice 18d) Create ap_batches';
4037 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4038 AP_IMPORT_UTILITIES_PKG.Print(
4039 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4040 END IF;
4041
4042 IF (AP_IMPORT_UTILITIES_PKG.Update_Ap_Batches(
4043 l_batch_id,
4044 p_batch_name,
4045 l_valid_invoices_count,
4046 l_actual_invoice_total,
4047 l_default_last_updated_by,
4048 current_calling_sequence) <> TRUE) THEN
4049 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4050 AP_IMPORT_UTILITIES_PKG.Print(
4051 AP_IMPORT_INVOICES_PKG.g_debug_switch,
4052 'Update_Ap_Batches<-'||current_calling_sequence);
4053 END IF;
4054 Raise import_invoice_failure;
4055 END IF;
4056
4057 debug_info := '(Import_invoice 18e) Set batch exists flag to Y';
4058 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4059 AP_IMPORT_UTILITIES_PKG.Print(
4060 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4061 END IF;
4062
4063 l_batch_exists_flag := 'Y';
4064 ELSE
4065 debug_info := '(Import_invoice 18f) Do Not create batch';
4066 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4067 AP_IMPORT_UTILITIES_PKG.Print(
4068 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4069 END IF;
4070 END IF;
4071
4072 -----------------------------------------------------------
4073 -- Step 19
4074 -- For each commit cycle, do a commit.
4075 -----------------------------------------------------------
4076 debug_info := '(Import_invoice 19) COMMIT to the database';
4077 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4078 AP_IMPORT_UTILITIES_PKG.Print(
4079 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4080 END IF;
4081
4082 IF NVL(p_commit,'Y') = 'Y' THEN
4083 COMMIT;
4084 END IF;
4085
4086 -----------------------------------------------------
4087 -- Step 20 Check IF there's still any record left
4088 --
4089 -----------------------------------------------------
4090 debug_info := '(Import_purge 20) Check IF there is still any record left';
4091 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4092 AP_IMPORT_UTILITIES_PKG.Print(
4093 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4094 END IF;
4095
4096 IF l_total_count > l_invoices_fetched THEN
4097 l_continue_flag := 'Y';
4098 ELSE
4099 l_continue_flag := 'N';
4100 END IF;
4101
4102 --Bug13616489 - added debug stmt
4103 debug_info := '(Import_invoice 20a) l_invoices_fetched:'||l_invoices_fetched;
4104 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4105 AP_IMPORT_UTILITIES_PKG.Print(
4106 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4107 END IF;
4108
4109 --Bug13616489 - added debug stmt
4110 debug_info := '(Import_Invoices 20b) l_total_count:'||l_total_count;
4111 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4112 AP_IMPORT_UTILITIES_PKG.Print(
4113 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4114 END IF;
4115
4116 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4117 AP_IMPORT_UTILITIES_PKG.Print(
4118 AP_IMPORT_INVOICES_PKG.g_debug_switch,
4119 '------------------> l_continue_flag = '|| l_continue_flag);
4120 END IF;
4121 --veramach bug 7121842 start
4122 EXCEPTION
4123 WHEN import_invoice_failure THEN
4124 IF l_total_count > l_invoices_fetched THEN --Bug8587808 Start
4125 l_continue_flag := 'Y';
4126 ELSE
4127 l_continue_flag := 'N';
4128 END IF; --Bug8587808 End
4129 --Bug13616489 - added debug stmt
4130 debug_info := '(Import_Invoices ****EXCEPTION) l_continue_flag:'||l_continue_flag;
4131 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4132 AP_IMPORT_UTILITIES_PKG.Print(
4133 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4134 END IF;
4135 END;
4136 --veramach bug 7121842 end
4137
4138 END LOOP; -- invoice group LOOP
4139
4140 debug_info := '(Import_invoice -->Execute outer while loop end:'
4141 ||to_char(sysdate,'YY-MON-DD HH24:MI:SS');
4142
4143 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4144 AP_IMPORT_UTILITIES_PKG.Print(
4145 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4146 END IF;
4147
4148 ----------------------------------------------------------------------
4149 -- Step 21
4150 -- Update temporary status in ap_invoices_interface for all invoices
4151 -- FROM 'PROCESSING' to 'PROCESSED' AND,
4152 -- 'REJECTING' to 'REJECTED'
4153 ----------------------------------------------------------------------
4154
4155 debug_info := '(Import_invoice 21) Update temporary status';
4156 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4157 AP_IMPORT_UTILITIES_PKG.Print(
4158 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4159 END IF;
4160 BEGIN--veramach bug 7121842
4161 IF (AP_IMPORT_UTILITIES_PKG.Update_temp_invoice_status(
4162 p_source,
4163 p_group_id,
4164 current_calling_sequence) <> TRUE) THEN
4165 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4166 AP_IMPORT_UTILITIES_PKG.Print(
4167 AP_IMPORT_INVOICES_PKG.g_debug_switch,
4168 'Update_temp_invoice_statu<-'||current_calling_sequence);
4169 END IF;
4170 Raise import_invoice_failure;
4171 END IF;
4172 --veramach bug 7121842 start
4173 EXCEPTION
4174 WHEN import_invoice_failure THEN
4175 NULL;
4176 END;
4177 --veramach bug 7121842 end
4178 /* p_invoices_created := nvl(l_valid_invoices_count,0); */ --bug 14530013
4179 p_invoices_created := nvl(l_invoices_created,0); --bug 14530013
4180 p_invoices_fetched := l_invoices_fetched;
4181 p_total_invoice_amount := l_total_invoice_amount; -- for bug 989221
4182
4183 debug_info := '(Import_invoice 22) Return No of invoices fetched '||
4184 'during process ,p_invoices_fetched'||l_invoices_fetched;
4185 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4186 AP_IMPORT_UTILITIES_PKG.Print(
4187 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4188 END IF;
4189
4190
4191 debug_info := 'Now Block to Raise the Business event to pass the Concurrent request_id'
4192 ||AP_IMPORT_INVOICES_PKG.g_conc_request_id;
4193 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4194 AP_IMPORT_UTILITIES_PKG.Print(
4195 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4196 END IF;
4197
4198 --7567527 PL/SQL Block for Enhancement to raise Business events after invoices are imported
4199 BEGIN
4200
4201
4202 l_parameter_list := wf_parameter_list_t( wf_parameter_t('REQUEST_ID',
4203 to_char(AP_IMPORT_INVOICES_PKG.g_conc_request_id)
4204 )
4205 );
4206
4207
4208 --bug 7636400
4209 /*
4210 SELECT to_char(ap_invoice_import_wfevent_s.nextval)
4211 INTO l_event_key
4212 FROM dual;
4213 */
4214
4215 SELECT to_char(AP_INV_IMPORT_EVENT_S.nextval)
4216 INTO l_event_key
4217 FROM dual;
4218
4219 wf_event.raise( p_event_name => l_event_name,
4220 p_event_key => l_event_key,
4221 p_parameters => l_parameter_list);
4222
4223 debug_info := 'After raising workflow event : '
4224 || 'event_name = ' || l_event_name
4225 || ' event_key = ' || l_event_key ;
4226 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4227 AP_IMPORT_UTILITIES_PKG.Print(
4228 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4229 END IF;
4230
4231 EXCEPTION
4232
4233 WHEN OTHERS THEN
4234 debug_info := 'Error Was Raised in raising event';
4235 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4236 AP_IMPORT_UTILITIES_PKG.Print(
4237 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4238 END IF;
4239 WF_CORE.CONTEXT('AP_IMPORT_INVOICES_PKG', 'IMPORT_INVOICES', l_event_name,
4240 l_event_key);
4241 RAISE;
4242 END;
4243 debug_info := 'Returned True';
4244 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4245 AP_IMPORT_UTILITIES_PKG.Print(
4246 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4247 END IF;
4248 RETURN (TRUE);
4249 EXCEPTION
4250 WHEN OTHERS THEN
4251 IF (SQLCODE < 0) THEN
4252 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4253 AP_IMPORT_UTILITIES_PKG.Print(
4254 AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
4255 END IF;
4256 END IF;
4257
4258 IF (SQLCODE = -54) THEN
4259 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4260 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
4261 '(Import_invoice:EXCEPTION) The invoices to be SELECTed by this ' ||
4262 'process are locked');
4263 END IF;
4264 END IF;
4265
4266 IF import_invoices%isOPEN THEN
4267 CLOSE import_invoices;
4268 ELSIF import_invoices_group%ISOPEN THEN
4269 CLOSE import_invoices_group;
4270 END IF;
4271
4272 RETURN (FALSE);
4273
4274 END Import_INVOICES;
4275
4276
4277 --===============================================================
4278 -- Main functions: Import_purge
4279 --
4280 --===============================================================
4281 FUNCTION IMPORT_PURGE(
4282 p_source IN VARCHAR2,
4283 p_group_id IN VARCHAR2,
4284 p_org_id IN NUMBER,
4285 p_commit_cycles IN NUMBER,
4286 p_calling_sequence IN VARCHAR2)
4287 RETURN BOOLEAN IS
4288
4289 -- Bug 4145391. Modified the select for the cursor to improve performance.
4290 -- Removed the p_group_id where clause and added it to the cursor
4291 -- purge_invoices_group
4292 CURSOR purge_invoices IS
4293 SELECT invoice_id
4294 FROM ap_invoices_interface
4295 WHERE source = p_source
4296 AND status = 'PROCESSED'
4297 AND ((p_commit_cycles IS NULL) OR
4298 (ROWNUM <= p_commit_cycles))
4299 AND ((org_id IS NOT NULL and p_org_id IS NOT NULL and
4300 org_id = p_org_id)
4301 OR (p_org_id IS NULL and org_id is NOT NULL and
4302 (mo_global.check_access(org_id)= 'Y'))
4303 OR (p_org_id IS NOT NULL and org_id IS NULL)
4304 OR (p_org_id IS NULL and org_id IS NULL))
4305 ORDER BY vendor_id,
4306 vendor_num,
4307 vendor_name,
4308 vendor_site_id,
4309 vendor_site_code,
4310 invoice_num;
4311
4312 CURSOR purge_invoices_group IS
4313 SELECT invoice_id
4314 FROM ap_invoices_interface
4315 WHERE source = p_source
4316 AND group_id = p_group_id
4317 AND status = 'PROCESSED'
4318 AND ((p_commit_cycles IS NULL) OR
4319 (ROWNUM <= p_commit_cycles))
4320 AND ((org_id IS NOT NULL and p_org_id IS NOT NULL and
4321 org_id = p_org_id)
4322 OR (p_org_id IS NULL and org_id is NOT NULL and
4323 (mo_global.check_access(org_id)= 'Y'))
4324 OR (p_org_id IS NOT NULL and org_id IS NULL)
4325 OR (p_org_id IS NULL and org_id IS NULL))
4326 ORDER BY vendor_id,
4327 vendor_num,
4328 vendor_name,
4329 vendor_site_id,
4330 vendor_site_code,
4331 invoice_num;
4332
4333 l_continue_flag VARCHAR2(1) := 'Y';
4334 l_invoice_id NUMBER;
4335 import_purge_failure EXCEPTION;
4336 current_calling_sequence VARCHAR2(2000);
4337 debug_info VARCHAR2(500); /* Bug 4166583 */
4338 l_total_count NUMBER := 0;
4339 l_counter NUMBER := 0;
4340 l_attachments_count NUMBER;
4341
4342
4343 BEGIN
4344 -- Update the calling sequence
4345
4346 current_calling_sequence := 'Import_purge<- '||p_calling_sequence;
4347
4348 debug_info := '(Import_purge ) Deleting records in interface tables...';
4349
4350 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4351 AP_IMPORT_UTILITIES_PKG.Print(
4352 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4353 END IF;
4354
4355 -- Outside while loop for commit cycle
4356
4357 IF p_group_id IS NULL THEN
4358 BEGIN
4359 SELECT count(*)
4360 INTO l_total_count
4361 FROM ap_invoices_interface
4362 WHERE source = p_source
4363 AND status = 'PROCESSED'
4364 AND ( (org_id IS NOT NULL AND
4365 p_org_id IS NOT NULL AND
4366 org_id = p_org_id)
4367 OR (p_org_id IS NULL AND
4368 org_id is NOT NULL and
4369 (mo_global.check_access(org_id)= 'Y'))
4370 OR (p_org_id IS NOT NULL and org_id IS NULL)
4371 OR (p_org_id IS NULL and org_id IS NULL));
4372 EXCEPTION
4373 WHEN NO_DATA_FOUND THEN
4374 l_continue_flag := 'N';
4375 END;
4376 ELSE
4377 BEGIN
4378 SELECT count(*)
4379 INTO l_total_count
4380 FROM ap_invoices_interface
4381 WHERE source = p_source
4382 AND group_id = p_group_id
4383 AND status = 'PROCESSED'
4384 AND ( (org_id IS NOT NULL AND
4385 p_org_id IS NOT NULL AND
4386 org_id = p_org_id)
4387 OR (p_org_id IS NULL AND
4388 org_id is NOT NULL and
4389 (mo_global.check_access(org_id)= 'Y'))
4390 OR (p_org_id IS NOT NULL and org_id IS NULL)
4391 OR (p_org_id IS NULL and org_id IS NULL));
4392 EXCEPTION
4393 WHEN NO_DATA_FOUND THEN
4394 l_continue_flag := 'N';
4395 END;
4396 END IF;
4397
4398 WHILE (l_continue_flag = 'Y') LOOP
4399
4400 ---------------------------------------------------------------
4401 -- Step 1, Open cursor
4402 ---------------------------------------------------------------
4403
4404 debug_info := '(Import_purge 1) Open purge_invoices cursor';
4405 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4406 AP_IMPORT_UTILITIES_PKG.Print(
4407 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4408 END IF;
4409
4410 -- Bug 4145391. To improve the performance of the import program coding two
4411 -- different cursors based on the parameter p_group_id
4412 IF (p_group_id IS NULL) THEN
4413 OPEN purge_invoices;
4414 ELSE
4415 OPEN purge_invoices_group;
4416 END IF;
4417
4418
4419 LOOP
4420 -- Invoice loop
4421
4422 ---------------------------------------------------------------
4423 -- Step 2, Fetch invoice interface record into local variables
4424 --
4425 ----------------------------------------------------------------
4426 debug_info := '(Import_puege 2) Fetch purge_invoices';
4427 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4428 AP_IMPORT_UTILITIES_PKG.Print(
4429 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4430 END IF;
4431
4432 -- Bug 4145391
4433 IF (p_group_id IS NULL) THEN
4434 FETCH purge_invoices INTO l_invoice_id;
4435 EXIT WHEN purge_invoices%NOTFOUND OR
4436 purge_invoices%NOTFOUND IS NULL;
4437 ELSE
4438 FETCH purge_invoices_group INTO l_invoice_id;
4439 EXIT WHEN purge_invoices_group%NOTFOUND OR
4440 purge_invoices_group%NOTFOUND IS NULL;
4441 END IF;
4442
4443 --
4444 -- show output values (only if debug_switch = 'Y')
4445 --
4446 debug_info := '---------> l_invoice_id = '|| to_char(l_invoice_id);
4447 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4448 AP_IMPORT_UTILITIES_PKG.Print(
4449 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4450 END IF;
4451
4452 ------------------------------------------------------------------------
4453 -- Step 3, Delete records for ap_invoice_lines_interface
4454 -- Multiple lines
4455 ------------------------------------------------------------------------
4456 debug_info := '(Import_purge 3) Delete records in ' ||
4457 'ap_invoice_lines_interface...';
4458 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4459 AP_IMPORT_UTILITIES_PKG.Print(
4460 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4461 END IF;
4462
4463 DELETE FROM AP_INVOICE_LINES_INTERFACE
4464 WHERE invoice_id = l_invoice_id;
4465
4466 ------------------------------------------------------------------------
4467 -- Step 4, Delete records for ap_invoices_interface
4468 -- Only one line
4469 ------------------------------------------------------------------------
4470 -- also delete attachments if any
4471 debug_info := '(Import_purge 4.1) Delete attachments if any...';
4472 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4473 AP_IMPORT_UTILITIES_PKG.Print(
4474 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4475 END IF;
4476
4477 -- delete attachments for the invoice
4478 debug_info := '(Import_purge 4.2) before delete attachments: '||
4479 'source = ' || p_source || ', invoice_id = ' || l_invoice_id;
4480 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4481 AP_IMPORT_UTILITIES_PKG.Print(
4482 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4483 END IF;
4484
4485 -- not necessary to restrict to souce
4486 l_attachments_count := delete_attachments(l_invoice_id);
4487 debug_info := '(Import_purge 4.2) delete attachments done: '||
4488 l_attachments_count;
4489 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4490 AP_IMPORT_UTILITIES_PKG.Print(
4491 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4492 END IF;
4493
4494 -- delete the invoice_interface record now
4495 debug_info := '(Import_purge 4) Delete records in ' ||
4496 'ap_invoices_interface...';
4497 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4498 AP_IMPORT_UTILITIES_PKG.Print(
4499 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4500 END IF;
4501
4502 DELETE FROM AP_INVOICES_INTERFACE
4503 WHERE invoice_id = l_invoice_id
4504 AND ( (org_id IS NOT NULL AND
4505 p_org_id IS NOT NULL AND
4506 org_id = p_org_id)
4507 OR (p_org_id IS NULL AND
4508 org_id is NOT NULL AND
4509 (mo_global.check_access(org_id)= 'Y'))
4510 OR (p_org_id IS NOT NULL AND
4511 org_id IS NULL)
4512 OR (p_org_id IS NULL AND
4513 org_id IS NULL));
4514
4515 l_counter := l_counter + 1;
4516
4517 END LOOP; -- invoice loop
4518
4519 debug_info := '(Import_purge ) Close purge_invoices cursor';
4520 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4521 AP_IMPORT_UTILITIES_PKG.Print(
4522 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4523 END IF;
4524
4525 -- Bug 4145391
4526 IF (p_group_id IS NULL) THEN
4527 CLOSE purge_invoices;
4528 ELSE
4529 CLOSE purge_invoices_group;
4530 END IF;
4531
4532 -----------------------------------------------------
4533 -- Step 5, COMMIT for each commit cycle
4534 -----------------------------------------------------
4535 debug_info := '(Import_purge 5) Commit to the database';
4536 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4537 AP_IMPORT_UTILITIES_PKG.Print(
4538 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4539 END IF;
4540 COMMIT;
4541
4542 -----------------------------------------------------
4543 -- Step 6, Check if there's still any record left
4544 -----------------------------------------------------
4545 debug_info := '(Import_purge 6) Check if there is still any record left';
4546 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4547 AP_IMPORT_UTILITIES_PKG.Print(
4548 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4549 END IF;
4550
4551 If l_total_count > l_counter THEN
4552 l_continue_flag := 'Y';
4553 Else
4554 l_continue_flag := 'N';
4555 End If;
4556
4557 debug_info := '---------> l_continue_flag = '|| l_continue_flag;
4558 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4559 AP_IMPORT_UTILITIES_PKG.Print(
4560 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4561 END IF;
4562 END LOOP; -- Outside commit cycle loop
4563 RETURN (TRUE);
4564
4565 EXCEPTION
4566 WHEN OTHERS THEN
4567
4568 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4569 AP_IMPORT_UTILITIES_PKG.Print(
4570 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4571 END IF;
4572
4573 IF (SQLCODE < 0) then
4574 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4575 AP_IMPORT_UTILITIES_PKG.Print(
4576 AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
4577 END IF;
4578 END IF;
4579
4580 IF (purge_invoices%ISOPEN) THEN
4581 CLOSE purge_invoices;
4582 ELSIF (purge_invoices_group%ISOPEN) THEN
4583 CLOSE purge_invoices_group;
4584 END IF;
4585
4586 RETURN (FALSE);
4587 END IMPORT_PURGE;
4588
4589 --===============================================================
4590 -- Private functions: xml_import_purge
4591 --
4592 --===============================================================
4593 FUNCTION XML_IMPORT_PURGE(
4594 p_group_id IN VARCHAR2,
4595 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
4596
4597 TYPE headerlist IS TABLE OF ap_invoices_interface.invoice_id%TYPE;
4598
4599 h_list HEADERLIST;
4600 current_calling_sequence VARCHAR2(2000);
4601 debug_info VARCHAR2(500); /* Bug 4166583 */
4602
4603 BEGIN
4604
4605 -- update calling_sequence
4606 current_calling_sequence := 'xml_import_purge<--'||p_calling_sequence;
4607
4608 debug_info := '(XML Import Purge 1) before getting list of invoice_id';
4609 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4610 AP_IMPORT_UTILITIES_PKG.Print(
4611 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4612 END IF;
4613
4614 -- get all XML invoices with supplier rejections
4615
4616 -- Bug 4145391. To improve the performance of the import program coding two
4617 -- different select stmts based on the parameter p_group_id
4618 IF (p_group_id IS NULL) THEN
4619 SELECT h.invoice_id BULK COLLECT
4620 INTO h_list
4621 FROM ap_invoices_interface h,
4622 ap_invoice_lines_interface l,
4623 ap_interface_rejections r
4624 WHERE DECODE(r.parent_table, 'AP_INVOICES_INTERFACE',
4625 h.invoice_id,
4626 'AP_INVOICE_LINES_INTERFACE', l.invoice_line_id)
4627 = r.parent_id
4628 AND h.invoice_id = l.invoice_id
4629 AND nvl(r.notify_vendor_flag,'N') = 'Y'
4630 AND h.status = 'REJECTED'
4631 AND h.source = 'XML GATEWAY'
4632 AND nvl(h.ORG_ID,
4633 to_number(nvl(decode(SUBSTR(USERENV('CLIENT_INFO'),1,1),
4634 ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99')) )
4635 = to_number(nvl(decode(SUBSTR(USERENV('CLIENT_INFO'),1,1),
4636 ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99'))
4637 GROUP BY h.invoice_id;
4638 ELSE
4639 SELECT h.invoice_id BULK COLLECT
4640 INTO h_list
4641 FROM ap_invoices_interface h,
4642 ap_invoice_lines_interface l,
4643 ap_interface_rejections r
4644 WHERE DECODE(r.parent_table, 'AP_INVOICES_INTERFACE',
4645 h.invoice_id,
4646 'AP_INVOICE_LINES_INTERFACE', l.invoice_line_id)
4647 = r.parent_id
4648 AND h.invoice_id = l.invoice_id
4649 AND nvl(r.notify_vendor_flag,'N') = 'Y'
4650 AND h.status = 'REJECTED'
4651 AND h.source = 'XML GATEWAY'
4652 AND h.group_id = p_group_id
4653 AND nvl(h.ORG_ID,
4654 to_number(nvl(decode(SUBSTR(USERENV('CLIENT_INFO'),1,1),
4655 ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99')) )
4656 = to_number(nvl(decode(SUBSTR(USERENV('CLIENT_INFO'),1,1),
4657 ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99'))
4658 GROUP BY h.invoice_id;
4659 END IF;
4660
4661
4662 debug_info := '(XML Import Purge 1.1) number of invoices to delete: '
4663 || nvl(h_list.count,0);
4664 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4665 AP_IMPORT_UTILITIES_PKG.Print(
4666 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4667 END IF;
4668
4669 debug_info := '(XML Import Purge 2) before deleting header rejections';
4670 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4671 AP_IMPORT_UTILITIES_PKG.Print(
4672 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4673 END IF;
4674
4675 FORALL i IN nvl(h_list.FIRST,0) .. nvl(h_list.LAST,-1)
4676 DELETE FROM ap_interface_rejections r
4677 WHERE r.parent_id = h_list(i)
4678 AND r.parent_table = 'AP_INVOICES_INTERFACE';
4679
4680 debug_info := '(XML Import Purge 3) before deleting line rejections';
4681 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4682 AP_IMPORT_UTILITIES_PKG.Print(
4683 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4684 END IF;
4685
4686 FORALL i IN nvl(h_list.FIRST,0) .. nvl(h_list.LAST,-1)
4687 DELETE FROM ap_interface_rejections r
4688 WHERE r.parent_id IN (SELECT l.invoice_line_id
4689 FROM ap_invoice_lines_interface l
4690 WHERE l.invoice_id = h_list(i) )
4691 AND r.parent_table = 'AP_INVOICE_LINES_INTERFACE';
4692
4693 debug_info := '(XML Import Purge 4) before deleting header interface';
4694 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4695 AP_IMPORT_UTILITIES_PKG.Print(
4696 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4697 END IF;
4698
4699 -- Delete from ap_invoice_lines_interface table
4700 FORALL i IN nvl(h_list.FIRST,0) .. nvl(h_list.LAST,-1)
4701 DELETE FROM ap_invoice_lines_interface l
4702 WHERE l.invoice_id = h_list(i);
4703
4704 debug_info := '(XML Import Purge 5) before deleting line interface';
4705 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4706 AP_IMPORT_UTILITIES_PKG.Print(
4707 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4708 END IF;
4709
4710 -- Delete from ap_invoices_interface table
4711 FORALL i IN nvl(h_list.FIRST,0) .. nvl(h_list.LAST,-1)
4712 DELETE FROM ap_invoices_interface h
4713 WHERE h.invoice_id = h_list(i);
4714
4715 debug_info := '(XML Import Purge 6) COMMIT';
4716 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4717 AP_IMPORT_UTILITIES_PKG.Print(
4718 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4719 END IF;
4720
4721 COMMIT;
4722
4723 RETURN(TRUE);
4724
4725 EXCEPTION
4726 WHEN OTHERS THEN
4727
4728 debug_info := 'Failed after ' || debug_info;
4729 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4730 AP_IMPORT_UTILITIES_PKG.Print(
4731 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4732 END IF;
4733 RETURN(false);
4734
4735 END xml_import_purge;
4736
4737
4738 PROCEDURE SUBMIT_PAYMENT_REQUEST(
4739 p_api_version IN VARCHAR2 DEFAULT '1.0',
4740 p_invoice_interface_id IN NUMBER,
4741 p_budget_control IN VARCHAR2 DEFAULT 'Y',
4742 p_needs_invoice_approval IN VARCHAR2 DEFAULT 'N',
4743 p_invoice_id OUT NOCOPY NUMBER,
4744 x_return_status OUT NOCOPY VARCHAR2,
4745 x_msg_count OUT NOCOPY NUMBER,
4746 x_msg_data OUT NOCOPY VARCHAR2,
4747 x_rejection_list OUT NOCOPY rejection_tab_type,
4748 p_calling_sequence IN VARCHAR2,
4749 p_commit IN VARCHAR2 DEFAULT 'Y',
4750 p_batch_name IN VARCHAR2 DEFAULT NULL, --Bug 8361660
4751 p_conc_request_id IN NUMBER DEFAULT NULL --Bug 8492591
4752 ) IS
4753
4754
4755 l_batch_error_flag VARCHAR2(1);
4756 l_invoices_fetched NUMBER;
4757 l_invoices_created NUMBER;
4758 l_total_invoice_amount NUMBER;
4759 l_print_batch VARCHAR2(1);
4760
4761 payment_request_failure EXCEPTION;
4762 current_calling_sequence VARCHAR2(2000);
4763 debug_info VARCHAR2(500);
4764
4765 l_invoice_id NUMBER;
4766 l_source VARCHAR2(80);
4767 l_holds_count NUMBER;
4768 l_approval_status VARCHAR2(30);
4769 l_funds_return_code VARCHAR2(30);
4770 l_debug_switch VARCHAR2(1); --bug 15862708
4771
4772 CURSOR c_rejections IS
4773 SELECT parent_table,
4774 parent_id,
4775 reject_lookup_code
4776 FROM ap_interface_rejections
4777 WHERE parent_table = 'AP_INVOICES_INTERFACE'
4778 AND parent_id = p_invoice_interface_id
4779 UNION
4780 SELECT parent_table,
4781 parent_id,
4782 reject_lookup_code
4783 FROM ap_interface_rejections
4784 WHERE parent_table = 'AP_INVOICE_LINES_INTERFACE'
4785 AND parent_id IN (SELECT invoice_line_id
4786 FROM ap_invoice_lines_interface
4787 WHERE invoice_id = p_invoice_interface_id);
4788
4789 BEGIN
4790
4791 -- Update the calling sequence and initialize variables
4792 current_calling_sequence := 'Submit_Payment_Request<- '||p_calling_sequence;
4793
4794 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4795 l_debug_switch := 'Y';
4796 END IF;
4797
4798
4799 -- Give error message if the interface invoice id is not provided
4800 IF p_invoice_interface_id IS NULL THEN
4801
4802 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4803 AP_IMPORT_UTILITIES_PKG.Print(
4804 AP_IMPORT_INVOICES_PKG.g_debug_switch,
4805 'No invoice_id<- '||current_calling_sequence);
4806 END IF;
4807
4808 FND_MESSAGE.Set_Name('SQLAP', 'AP_IMP_NO_INVOICE_ID');
4809 x_msg_data := FND_MESSAGE.Get;
4810
4811 x_return_status := 'F';
4812 return;
4813
4814 ELSE
4815
4816 SELECT source
4817 INTO l_source
4818 FROM ap_invoices_interface
4819 WHERE invoice_id = p_invoice_interface_id;
4820
4821
4822 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4823 AP_IMPORT_UTILITIES_PKG.Print(
4824 AP_IMPORT_INVOICES_PKG.g_debug_switch,
4825 'Calling Import_Invoices<- '||current_calling_sequence);
4826 END IF;
4827
4828 -- Calling the import invoices routine to import the payment request
4829 -- invoices
4830 IF (IMPORT_INVOICES(
4831 p_batch_name, --p_batch_name Bug 8361660
4832 NULL, --p_gl_date
4833 NULL, --p_hold_code
4834 NULL, --p_hold_reason
4835 NULL, --p_commit_cycles
4836 l_source, --p_source
4837 NULL, --p_group_id
4838 p_conc_request_id, --Bug 8492591
4839 l_debug_switch, --p_debug_switch
4840 NULL, --p_org_id,
4841 l_batch_error_flag,
4842 l_invoices_fetched,
4843 l_invoices_created,
4844 l_total_invoice_amount,
4845 l_print_batch,
4846 current_calling_sequence,
4847 p_invoice_interface_id,
4848 p_needs_invoice_approval,
4849 p_commit) <> TRUE) THEN
4850
4851 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4852 AP_IMPORT_UTILITIES_PKG.Print(
4853 AP_IMPORT_INVOICES_PKG.g_debug_switch,
4854 'Error during import<- '||current_calling_sequence);
4855 END IF;
4856
4857 Raise payment_request_failure;
4858 END IF; -- Import Invoices
4859
4860
4861 -- If no invoices are created then get the list of rejections and
4862 -- send rejections to the calling routine
4863 IF l_invoices_created = 0 THEN
4864
4865 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4866 AP_IMPORT_UTILITIES_PKG.Print(
4867 AP_IMPORT_INVOICES_PKG.g_debug_switch,
4868 'No invoices created<- '||current_calling_sequence);
4869 END IF;
4870
4871 x_return_status := 'R';
4872
4873 OPEN c_rejections;
4874 FETCH c_rejections BULK COLLECT INTO x_rejection_list;
4875 CLOSE c_rejections;
4876
4877 ELSE
4878 x_return_status := 'S';
4879
4880 END IF;
4881
4882
4883 IF g_invoice_id IS NOT NULL THEN
4884
4885 l_invoice_id := g_invoice_id;
4886 p_invoice_id := g_invoice_id;
4887
4888 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4889 AP_IMPORT_UTILITIES_PKG.Print(
4890 AP_IMPORT_INVOICES_PKG.g_debug_switch,
4891 'Calling invoice validation<- '||current_calling_sequence);
4892 END IF;
4893
4894
4895 -- Calling the approve routine to validate the invoices that are
4896 -- imported
4897 ap_approval_pkg.approve
4898 ('', -- p_run_option
4899 '', -- p_invoice_batch_id
4900 '', -- p_begin_invoice_date
4901 '', -- p_end_invoice_date
4902 '', -- p_vendor_id
4903 '', -- p_pay_group
4904 l_invoice_id,
4905 '', -- p_entered_by
4906 '', -- p_set_of_books_id
4907 '', -- p_trace_option
4908 '', -- p_conc_flag
4909 l_holds_count,
4910 l_approval_status,
4911 l_funds_return_code,
4912 'PAYMENT REQUEST',
4913 current_calling_sequence,
4914 l_debug_switch,
4915 p_budget_control,
4916 p_commit);
4917
4918 x_return_status := 'S';
4919
4920 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
4921 AP_IMPORT_UTILITIES_PKG.Print(
4922 AP_IMPORT_INVOICES_PKG.g_debug_switch,
4923 'Validation complete<- '||current_calling_sequence);
4924 END IF;
4925
4926 END IF;
4927
4928 END IF;
4929
4930 g_invoice_id := null;
4931
4932 EXCEPTION
4933 WHEN no_data_found THEN
4934 FND_MESSAGE.Set_Name('SQLAP', 'AP_IMP_NO_INVOICE_ID');
4935 x_msg_data := FND_MESSAGE.Get;
4936 x_return_status := 'F';
4937
4938 WHEN OTHERS THEN
4939 IF (SQLCODE < 0) THEN
4940 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
4941 AP_IMPORT_UTILITIES_PKG.Print(
4942 AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
4943 END IF;
4944 x_msg_data := SQLERRM;
4945 x_return_status := 'F';
4946 END IF;
4947
4948 END SUBMIT_PAYMENT_REQUEST;
4949
4950 FUNCTION get_info_1 (
4951 p_org_id IN NUMBER,
4952 p_gl_date IN OUT NOCOPY DATE,
4953 p_calling_sequence IN VARCHAR2,
4954 p_source IN VARCHAR2, --bug 5382889. LE TimeZone
4955 p_invoice_date IN DATE, -- bug 5382889. LE TimeZone
4956 p_goods_received_date IN DATE, -- bug 5382889. LE TimeZone
4957 p_get_info_rec OUT NOCOPY AP_IMPORT_INVOICES_PKG.get_info_rec
4958 )
4959 RETURN BOOLEAN
4960 IS
4961
4962 l_status VARCHAR2(10);
4963 l_industry VARCHAR2(10);
4964 get_info_failure EXCEPTION;
4965 current_calling_sequence VARCHAR2(2000);
4966 debug_info VARCHAR2(500);
4967 l_ext_precision NUMBER(2);
4968 --bug 15862708
4969 l_base_currency_code ap_system_parameters_all.base_currency_code%TYPE;
4970 l_set_of_books_id ap_system_parameters_all.set_of_books_id%TYPE;
4971
4972
4973
4974
4975 l_inv_gl_date DATE; --Bug 5382889. LE Timezone
4976 l_rts_txn_le_date DATE; --Bug 5382889. LE Timezone
4977 l_inv_le_date DATE; --Bug 5382889. LE Timezone
4978 l_sys_le_date DATE; --Bug 5382889. LE Timezone
4979
4980 l_asset_book_count NUMBER;
4981 --tab_get_info_rec AP_IMPORT_INVOICES_PKG.table_get_info_rec; --bug 15862708
4982
4983 p_set_of_books_id ap_system_parameters_all.set_of_books_id%TYPE;
4984 p_multi_currency_flag ap_system_parameters_all.multi_currency_flag%TYPE;
4985 p_make_rate_mandatory_flag ap_system_parameters_all.make_rate_mandatory_flag%TYPE;
4986 p_default_exchange_rate_type ap_system_parameters_all.default_exchange_rate_type%TYPE;
4987 p_base_currency_code ap_system_parameters_all.base_currency_code%TYPE;
4988 p_batch_control_flag varchar2(2);
4989 p_invoice_currency_code ap_system_parameters_all.invoice_currency_code%TYPE;
4990 p_base_min_acct_unit number;
4991 p_base_precision number;
4992 p_sequence_numbering varchar2(30);
4993 p_awt_include_tax_amt ap_system_parameters_all.awt_include_tax_amt%TYPE;
4994 pp_gl_date date;
4995 p_transfer_desc_flex_flag ap_system_parameters_all.transfer_desc_flex_flag%TYPE;
4996 p_gl_date_from_receipt_flag ap_system_parameters_all.gl_date_from_receipt_flag%TYPE;
4997 p_purch_encumbrance_flag financials_system_params_all.purch_encumbrance_flag%TYPE;
4998 p_retainage_ccid financials_system_params_all.retainage_code_combination_id%TYPE;
4999 P_pa_installed varchar2(2);
5000 p_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%type;
5001 p_inv_doc_cat_override ap_system_parameters_all.inv_doc_category_override%TYPE;
5002 p_calc_user_xrate ap_system_parameters_all.calc_user_xrate%TYPE;
5003 p_approval_workflow_flag ap_system_parameters_all.approval_workflow_flag%TYPE;
5004 p_freight_code_combination_id ap_system_parameters_all.freight_code_combination_id%TYPE;
5005 p_allow_interest_invoices ap_system_parameters_all.auto_calculate_interest_flag%TYPE;
5006 p_add_days_settlement_date ap_system_parameters_all.add_days_settlement_date%TYPE;
5007 p_disc_is_inv_less_tax_flag ap_system_parameters_all.disc_is_inv_less_tax_flag%TYPE;
5008 p_asset_book_type varchar2(100);
5009
5010 BEGIN
5011 -- Update the calling sequence
5012
5013 current_calling_sequence :=
5014 'AP_Import_Utilities_Pkg.Get_info<-'||P_calling_sequence;
5015
5016 if tab_get_info_rec.exists(p_org_id) then
5017 /*
5018 --p_org_id := tab_get_info_rec(p_org_id).p_org_id ;
5019 p_set_of_books_id := tab_get_info_rec(p_org_id).p_set_of_books_id ;
5020 p_multi_currency_flag := tab_get_info_rec(p_org_id).p_multi_currency_flag ;
5021 p_make_rate_mandatory_flag := tab_get_info_rec(p_org_id).p_make_rate_mandatory_flag ;
5022 p_default_exchange_rate_type := tab_get_info_rec(p_org_id).p_default_exchange_rate_type ;
5023 p_base_currency_code := tab_get_info_rec(p_org_id).p_base_currency_code ;
5024 p_batch_control_flag := tab_get_info_rec(p_org_id).p_batch_control_flag ;
5025 p_invoice_currency_code := tab_get_info_rec(p_org_id).p_invoice_currency_code ;
5026 p_base_min_acct_unit := tab_get_info_rec(p_org_id).p_base_min_acct_unit ;
5027 p_base_precision := tab_get_info_rec(p_org_id).p_base_precision ;
5028 p_sequence_numbering := tab_get_info_rec(p_org_id).p_sequence_numbering ;
5029 p_awt_include_tax_amt := tab_get_info_rec(p_org_id).p_awt_include_tax_amt ;
5030 p_gl_date := tab_get_info_rec(p_org_id).p_gl_date ;
5031 p_trnasfer_desc_flex_flag := tab_get_info_rec(p_org_id).p_trnasfer_desc_flex_flag ;
5032 p_gl_date_from_receipt_flag := tab_get_info_rec(p_org_id).p_gl_date_from_receipt_flag ;
5033 p_purch_encumbrance_flag := tab_get_info_rec(p_org_id).p_purch_encumbrance_flag ;
5034 p_retainage_ccid := tab_get_info_rec(p_org_id).p_retainage_ccid ;
5035 P_pa_installed := tab_get_info_rec(p_org_id).P_pa_installed ;
5036 p_chart_of_accounts_id := tab_get_info_rec(p_org_id).p_chart_of_accounts_id ;
5037 p_inv_doc_cat_override := tab_get_info_rec(p_org_id).p_inv_doc_cat_override ;
5038 p_calc_user_xrate := tab_get_info_rec(p_org_id).p_calc_user_xrate ;
5039 --p_calling_sequence := tab_get_info_rec(p_org_id).p_calling_sequence ;
5040 p_approval_workflow_flag := tab_get_info_rec(p_org_id).p_approval_workflow_flag ;
5041 p_freight_code_combination_id := tab_get_info_rec(p_org_id).p_freight_code_combination_id ;
5042 p_allow_interest_invoices := tab_get_info_rec(p_org_id).p_allow_interest_invoices ;
5043 p_add_days_settlement_date := tab_get_info_rec(p_org_id).p_add_days_settlement_date ;
5044 p_disc_is_inv_less_tax_flag := tab_get_info_rec(p_org_id).p_disc_is_inv_less_tax_flag ;
5045 p_asset_book_type := tab_get_info_rec(p_org_id).p_asset_book_type ;
5046 */
5047 p_get_info_rec :=tab_get_info_rec(p_org_id);
5048
5049 else
5050
5051 debug_info := '(Get_info 1) Read from ap_system_parameters';
5052 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
5053 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5054 END IF;
5055
5056 begin
5057 SELECT
5058 set_of_books_id,
5059 multi_currency_flag,
5060 make_rate_mandatory_flag,
5061 default_exchange_rate_type,
5062 base_currency_code,
5063 aps.invoice_currency_code,
5064 awt_include_tax_amt,
5065 -- ussgl_transaction_code, - Bug 4277744
5066 transfer_desc_flex_flag,
5067 gl_date_from_receipt_flag,
5068 inv_doc_category_override,
5069 NVL(calc_user_xrate, 'N'),
5070 NVL(approval_workflow_flag,'N'),
5071 freight_code_combination_id ,
5072 /*we need to get the value of allow_interest_invoices
5073 from system_parameters versus product setup, since the value
5074 in the product setup is only for defaulting into suppliers,
5075 whereas the value in asp decides whether we create INT invoices
5076 or not*/
5077 asp.auto_calculate_interest_flag,
5078 --bugfix:4930111
5079 asp.add_days_settlement_date,
5080 NVL(asp.disc_is_inv_less_tax_flag, 'N') /* bug 4931755 */
5081 INTO
5082 p_set_of_books_id,
5083 p_multi_currency_flag,
5084 p_make_rate_mandatory_flag,
5085 p_default_exchange_rate_type,
5086 p_base_currency_code,
5087 p_invoice_currency_code,
5088 p_awt_include_tax_amt,
5089 --Bug 4277744
5090 p_transfer_desc_flex_flag,
5091 p_gl_date_from_receipt_flag,
5092 p_inv_doc_cat_override,
5093 p_calc_user_xrate,
5094 p_approval_workflow_flag,
5095 p_freight_code_combination_id,
5096 p_allow_interest_invoices,
5097 p_add_days_settlement_date,
5098 p_disc_is_inv_less_tax_flag
5099 FROM ap_system_parameters_all asp,
5100 ap_product_setup aps
5101 WHERE asp.org_id = p_org_id;
5102 exception
5103 when others then
5104 null;
5105 end;
5106
5107 l_base_currency_code:=p_base_currency_code ;
5108 l_set_of_books_id := p_set_of_books_id ;
5109
5110 debug_info := '(Get_info 2) Get Batch Control Profile Option';
5111 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
5112 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5113 END IF;
5114
5115 BEGIN
5116 FND_PROFILE.GET('AP_USE_INV_BATCH_CONTROLS',tab_get_info_rec(p_org_id).p_batch_control_flag);
5117
5118 EXCEPTION
5119 WHEN OTHERS THEN
5120 p_batch_control_flag := 'N';
5121 END ;
5122
5123 debug_info := '(Get_info 3) Get encumbrance option';
5124 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
5125 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5126 END IF;
5127
5128 SELECT purch_encumbrance_flag, retainage_code_combination_id
5129 INTO p_purch_encumbrance_flag,
5130 p_retainage_ccid
5131 FROM financials_system_params_all
5132 WHERE org_id = p_org_id;
5133
5134 debug_info := '(Get_info 4) Get minimum_accountable_unit';
5135 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
5136 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5137 END IF;
5138
5139 FND_CURRENCY.GET_INFO(
5140 l_base_currency_code ,
5141 p_base_precision ,
5142 l_ext_precision ,
5143 p_base_min_acct_unit);
5144
5145 debug_info := '(Get_info 5) Get p_sequence_numbering';
5146 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
5147 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5148 END IF;
5149
5150 p_sequence_numbering := FND_PROFILE.VALUE('UNIQUE:SEQ_NUMBERS');
5151
5152
5153 debug_info := '(Get_info 6) Get gl_date based on report parameters';
5154 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
5155 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5156 END IF;
5157
5158 -- Bug 5645581. Gl_date will calculated at the Import_Invoices
5159 -- Procedure in the Main Package
5160 /*IF p_source = 'ERS' THEN -- bug 5382889, LE TimeZone
5161
5162 debug_info := 'Determine gl_date from ERS invoice';
5163
5164 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
5165 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
5166 END IF;
5167
5168 l_rts_txn_le_date := INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Ou(
5169 p_trxn_date => nvl(p_goods_received_date, p_invoice_date)
5170 ,p_ou_id => p_org_id);
5171
5172 l_inv_le_date := INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Ou(
5173 p_trxn_date => p_invoice_date
5174 ,p_ou_id => p_org_id);
5175
5176 l_sys_le_date := INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Ou(
5177 p_trxn_date => sysdate
5178 ,p_ou_id => p_org_id);
5179
5180
5181 -- The gl_date id determined from the flag gl_date_from_receipt_flag
5182 -- If the flag = 'I' -- take Invoice_date
5183 -- = 'S' -- take System date
5184 -- = 'N' -- take nvl(receipt_date, invoice_date)
5185 -- = 'Y' -- take nvl(receipt_date, sysdate)
5186 -- Note here that the Invoice date is no longer the same as the receipt_date,
5187 -- i.e. the RETURN tranasaction_date , so case I and N are no longer the same
5188
5189 debug_info := 'Determine invoice gl_date from LE Timezone API ';
5190 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
5191 Print(AP_IMPORT_INVOICES_PKG.g_debug_switch, debug_info);
5192 END IF;
5193
5194 If (p_gl_date_from_receipt_flag = 'I') Then
5195 l_inv_gl_date := l_inv_le_date;
5196 Elsif (p_gl_date_from_receipt_flag = 'N') Then
5197 l_inv_gl_date := nvl(l_rts_txn_le_date, l_inv_le_date);
5198 Elsif (p_gl_date_from_receipt_flag = 'S') Then
5199 l_inv_gl_date := l_sys_le_date;
5200 Elsif (p_gl_date_from_receipt_flag = 'Y') then
5201 l_inv_gl_date := nvl(l_rts_txn_le_date, l_sys_le_date);
5202 End if;
5203
5204 p_gl_date := l_inv_gl_date;
5205
5206 ELSE
5207 IF (p_gl_date IS NULL) THEN
5208 IF (p_gl_date_from_receipt_flag IN ('S','Y')) THEN
5209 debug_info := '(Get_info 6a) GL Date is Sysdate';
5210 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
5211 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5212 END IF;
5213
5214 p_gl_date := sysdate;
5215
5216 ELSE
5217 debug_info := '(Get_info 6b) GL Date should be Invoice Date';
5218 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
5219 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5220 END IF;
5221 END IF;
5222 END IF;
5223 END IF;
5224
5225 p_gl_date := trunc(p_gl_date); */
5226 debug_info := '(Get_info 7) Check if PA is installed';
5227 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
5228 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5229 END IF;
5230
5231 IF (FND_INSTALLATION.GET(275, 275, l_status, l_industry)) THEN
5232 IF (l_status <> 'I') THEN
5233 P_PA_INSTALLED := 'N';
5234 ELSE
5235 P_PA_INSTALLED := 'Y';
5236 AP_IMPORT_INVOICES_PKG.g_pa_allows_overrides :=
5237 NVL(FND_PROFILE.VALUE('PA_ALLOW_FLEXBUILDER_OVERRIDES'), 'N');
5238 END IF;
5239 ELSE
5240 RAISE get_info_failure;
5241 END IF;
5242
5243 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
5244 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
5245 '------------------> l_status = '|| l_status
5246 ||' l_industry = ' ||l_industry
5247 ||' p_pa_installed = '||tab_get_info_rec(p_org_id).p_pa_installed);
5248 END IF;
5249
5250 debug_info := '(Get_info 8) Get chart_of_accounts_id from p_set_of_books_id';
5251 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
5252 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5253 END IF;
5254
5255 SELECT chart_of_accounts_id
5256 INTO p_chart_of_accounts_id
5257 FROM gl_sets_of_books
5258 WHERE set_of_books_id = l_set_of_books_id;
5259
5260
5261 -- Bug 5448579
5262 /* debug_info := '(Get_info 9) Get Asset Book Type Code';
5263 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
5264 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5265 END IF;
5266 BEGIN
5267 SELECT count(*)
5268 INTO l_asset_book_count
5269 FROM fa_book_controls bc
5270 WHERE bc.book_class = 'CORPORATE'
5271 AND bc.set_of_books_id = p_set_of_books_id
5272 AND bc.date_ineffective IS NULL;
5273
5274 IF (l_asset_book_count = 1) THEN
5275 SELECT bc.book_type_code
5276 INTO p_asset_book_type
5277 FROM fa_book_controls bc
5278 WHERE bc.book_class = 'CORPORATE' --bug6847888
5279 AND bc.set_of_books_id = p_set_of_books_id
5280 AND bc.date_ineffective IS NULL;
5281
5282 ELSE
5283 p_asset_book_type := NULL;
5284 END IF;
5285
5286 EXCEPTION
5287 -- No need to error handle if FA information not available.
5288 WHEN no_data_found THEN
5289 NULL;
5290 WHEN OTHERS THEN
5291 NULL;
5292 END;*/ --bug 7584682
5293
5294 p_asset_book_type := NULL; --bug 7584682
5295 --bug 15862708
5296
5297 tab_get_info_rec(p_org_id).p_set_of_books_id := p_set_of_books_id ;
5298 tab_get_info_rec(p_org_id).p_multi_currency_flag := p_multi_currency_flag ;
5299 tab_get_info_rec(p_org_id).p_make_rate_mandatory_flag := p_make_rate_mandatory_flag ;
5300 tab_get_info_rec(p_org_id).p_default_exchange_rate_type := p_default_exchange_rate_type ;
5301 tab_get_info_rec(p_org_id).p_base_currency_code := p_base_currency_code ;
5302 tab_get_info_rec(p_org_id).p_batch_control_flag := p_batch_control_flag ;
5303 tab_get_info_rec(p_org_id).p_invoice_currency_code := p_invoice_currency_code ;
5304 tab_get_info_rec(p_org_id).p_base_min_acct_unit := p_base_min_acct_unit ;
5305 tab_get_info_rec(p_org_id).p_base_precision := p_base_precision ;
5306 tab_get_info_rec(p_org_id).p_sequence_numbering := p_sequence_numbering ;
5307 tab_get_info_rec(p_org_id).p_awt_include_tax_amt := p_awt_include_tax_amt ;
5308 tab_get_info_rec(p_org_id).p_gl_date := p_gl_date ;
5309 tab_get_info_rec(p_org_id).p_transfer_desc_flex_flag := p_transfer_desc_flex_flag ;
5310 tab_get_info_rec(p_org_id).p_gl_date_from_receipt_flag := p_gl_date_from_receipt_flag ;
5311 tab_get_info_rec(p_org_id).p_purch_encumbrance_flag := p_purch_encumbrance_flag ;
5312 tab_get_info_rec(p_org_id).p_retainage_ccid := p_retainage_ccid ;
5313 tab_get_info_rec(p_org_id).P_pa_installed := P_pa_installed ;
5314 tab_get_info_rec(p_org_id).p_chart_of_accounts_id := p_chart_of_accounts_id ;
5315 tab_get_info_rec(p_org_id).p_inv_doc_cat_override := p_inv_doc_cat_override ;
5316 tab_get_info_rec(p_org_id).p_calc_user_xrate := p_calc_user_xrate ;
5317 tab_get_info_rec(p_org_id).p_approval_workflow_flag := p_approval_workflow_flag ;
5318 tab_get_info_rec(p_org_id).p_freight_code_combination_id := p_freight_code_combination_id;
5319 tab_get_info_rec(p_org_id).p_allow_interest_invoices := p_allow_interest_invoices ;
5320 tab_get_info_rec(p_org_id).p_add_days_settlement_date := p_add_days_settlement_date ;
5321 tab_get_info_rec(p_org_id).p_disc_is_inv_less_tax_flag := p_disc_is_inv_less_tax_flag ;
5322 tab_get_info_rec(p_org_id).p_asset_book_type := p_asset_book_type ;
5323 tab_get_info_rec(p_org_id).p_batch_control_flag := 'Y';
5324
5325
5326
5327 debug_info := '(Get_info 9) Get system tolerances';
5328 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
5329 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5330 END IF;
5331
5332 p_get_info_rec := tab_get_info_rec(p_org_id);
5333
5334 END IF;
5335
5336 RETURN (TRUE);
5337
5338
5339 EXCEPTION
5340 WHEN OTHERS then
5341 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
5342 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5343 END IF;
5344
5345 IF (SQLCODE < 0) then
5346 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
5347 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
5348 END IF;
5349 END IF;
5350
5351 RETURN (FALSE);
5352
5353 END get_info_1;
5354
5355 END AP_IMPORT_INVOICES_PKG;