[Home] [Help]
PACKAGE BODY: APPS.ARP_ETAX_RECURR_UTIL
Source
1 PACKAGE BODY ARP_ETAX_RECURR_UTIL AS
2 /* $Header: AREBTICB.pls 120.39.12020000.3 2012/11/23 12:30:08 kknekkal ship $ */
3
4 /*=======================================================================+
5 | Package Globals
6 +=======================================================================*/
7 g_headers_inserted NUMBER;
8 g_lines_inserted NUMBER;
9 g_tax_lines_inserted NUMBER;
10 l_status VARCHAR2(1); -- junk variable
11 l_industry VARCHAR2(1); -- junk variable
12 g_default_country VARCHAR2(50);
13 g_legal_entity_id NUMBER;
14
15 /*========================================================================
16 | Prototype Declarations Procedures
17 *=======================================================================*/
18
19 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
20
21 /*========================================================================
22 | Prototype Declarations Functions
23 *=======================================================================*/
24
25 PROCEDURE debug(text IN VARCHAR2) IS
26 BEGIN
27 -- fnd_file.put_line(FND_FILE.LOG, text);
28 arp_debug.debug(text);
29 END;
30
31 /* Private Procedure - Inserts headers into ZX_TRX_HEADERS_GT */
32 PROCEDURE insert_header(p_customer_trx_id IN NUMBER) IS
33
34 BEGIN
35 IF PG_DEBUG in ('Y', 'C') THEN
36 debug('arp_etax_recurr_util.insert_header()+');
37 END IF;
38
39 INSERT INTO ZX_TRX_HEADERS_GT
40 (
41 internal_organization_id,
42 internal_org_location_id,
43 legal_entity_id,
44 application_id,
45 ledger_id,
46 entity_code,
47 event_class_code,
48 event_type_code,
49 tax_reporting_flag,
50 trx_id,
51 trx_number,
52 trx_description,
53 doc_seq_id,
54 doc_seq_name,
55 doc_seq_value,
56 batch_source_id,
57 batch_source_name,
58 receivables_trx_type_id,
59 trx_type_description,
60 trx_date,
61 trx_communicated_date,
62 trx_due_date,
63 bill_to_cust_acct_site_use_id,
64 trx_currency_code,
65 precision,
66 minimum_accountable_unit,
67 currency_conversion_date,
68 currency_conversion_rate,
69 currency_conversion_type,
70 rounding_bill_to_party_id,
71 rndg_bill_to_party_site_id,
72 bill_third_pty_acct_id,
73 bill_third_pty_acct_site_id,
74 application_doc_status,
75 related_doc_application_id,
76 related_doc_entity_code,
77 related_doc_event_class_code,
78 related_doc_trx_id,
79 related_doc_number,
80 related_doc_date
81 )
82 SELECT
83 AR.org_id,
84 HR.location_id,
85 T.legal_entity_id,
86 222,
87 AR.set_of_books_id,
88 'TRANSACTIONS',
89 'INVOICE', -- event_class
90 'INV_CREATE', -- event_type
91 'Y',
92 T.customer_trx_id,
93 T.trx_number,
94 SUBSTRB(T.comments,1,240),
95 T.doc_sequence_id,
96 -- bug 6806843
97 -- TT.name,
98 SEQ.name,
99 T.doc_sequence_value,
100 T.batch_source_id,
101 TB.name,
102 T.cust_trx_type_id,
103 TT.description,
104 T.trx_date,
105 T.printing_original_date,
106 T.term_due_date,
107 T.bill_to_site_use_id,
108 T.invoice_currency_code,
109 C.precision,
110 C.minimum_accountable_unit,
111 T.exchange_date,
112 T.exchange_rate,
113 T.exchange_rate_type,
114 BTCA.party_id,
115 BTPS.party_site_id,
116 T.bill_to_customer_id,
117 BTPS.cust_acct_site_id,
118 DECODE(T.status_trx, 'VD','VD',NULL), -- void
119 DECODE(REL_T.customer_trx_id, NULL, NULL, 222),
120 DECODE(REL_T.customer_trx_id, NULL, NULL, 'TRANSACTIONS'),
121 DECODE(REL_T.customer_trx_id, NULL, NULL,
122 DECODE(REL_TT.type, 'INV', 'INVOICE',
123 'DM', 'DEBIT_MEMO',
124 'CM', 'CREDIT_MEMO')),
125 DECODE(REL_T.customer_trx_id, NULL, NULL, REL_T.customer_trx_id),
126 DECODE(REL_T.customer_trx_id, NULL, NULL, REL_T.trx_number),
127 DECODE(REL_T.customer_trx_id, NULL, NULL, REL_T.trx_date)
128 FROM RA_CUSTOMER_TRX T,
129 RA_CUST_TRX_TYPES TT,
130 RA_BATCH_SOURCES TB,
131 FND_CURRENCIES C,
132 FND_DOCUMENT_SEQUENCES SEQ,
133 AR_SYSTEM_PARAMETERS AR,
134 HZ_CUST_ACCOUNTS BTCA,
135 HZ_CUST_SITE_USES BTCSU,
136 HZ_CUST_ACCT_SITES BTPS,
137 HR_ORGANIZATION_UNITS HR,
138 RA_CUSTOMER_TRX REL_T,
139 RA_CUST_TRX_TYPES REL_TT
140 WHERE T.customer_trx_id = p_customer_trx_id
141 AND T.invoice_currency_code = C.currency_code
142 AND T.org_id = AR.org_id
143 AND T.cust_trx_type_id = TT.cust_trx_type_id
144 AND T.doc_sequence_id = SEQ.doc_sequence_id (+)
145 AND T.batch_source_id = TB.batch_source_id
146 AND T.bill_to_customer_id = BTCA.cust_account_id
147 AND T.bill_to_site_use_id = BTCSU.site_use_id
148 AND BTCSU.cust_acct_site_id = BTPS.cust_acct_site_id
149 AND HR.organization_id = T.org_id
150 AND T.related_customer_trx_id = REL_T.customer_trx_id (+)
151 AND REL_T.cust_trx_type_id = REL_TT.cust_trx_type_id (+);
152
153 /* Store total for output in debug log */
154 g_headers_inserted := SQL%ROWCOUNT;
155
156 IF PG_DEBUG in ('Y', 'C') THEN
157 debug(' headers inserted : ' || g_headers_inserted);
158 debug('arp_etax_recurr_util.insert_header()-');
159 END IF;
160
161 EXCEPTION
162 WHEN NO_DATA_FOUND
163 THEN
164 debug('arp_etax_recurr_util.insert_header()- No transaction headers to process.');
165 RETURN;
166 WHEN OTHERS
167 THEN
168 debug('EXCEPTION: ARP_ETAX_RECURR_UTIL.insert_header()-');
169 RAISE;
170
171 END insert_header;
172
173 /* Private Procedure - Inserts lines (not tax) into ZX_TRANSACTION_LINES_GT.
174 NOTE: In order for tax to work properly for copied invoices, we
175 must populate the source columns for the copied lines with the
176 data from the original (AR) lines.
177
178 DEV NOTE: Questions...
179
180 1) How do I insert manual tax lines in invoice copy? What fields are
181 required?
182
183 RESP: Harsh says that I do not insert anything into the ZX_TAX_LINES
184 table. Rather, I populate the SOURCE columns on the copied invoice
185 lines (in ZX table) with the line info from the original invoice
186 line.
187
188 */
189 PROCEDURE insert_line(
190 p_orig_line_id IN NUMBER,
191 p_new_line_id IN NUMBER) IS
192
193 l_so_org_id VARCHAR2(20);
194 l_lines_updated NUMBER;
195 BEGIN
196 IF PG_DEBUG in ('Y', 'C') THEN
197 debug('arp_etax_recurr_util.insert_line()+');
198 END IF;
199
200 l_so_org_id := oe_profile.value('SO_ORGANIZATION_ID',
201 arp_global.sysparam.org_id);
202
203 /* 4666566 - added support for ship_to and product_org_id to
204 line-level insert. Invoice copy automatically copies ship to
205 to line-level to make it more like autoinvoice. */
206 /*Bug 9188841 Changed Query Condition and select*/
207
208 INSERT INTO ZX_TRANSACTION_LINES_GT
209 (
210 application_id,
211 entity_code,
212 event_class_code,
213 interface_entity_code,
214 interface_line_id,
215 trx_id,
216 trx_level_type,
217 trx_line_id,
218 line_class,
219 line_level_action,
220 trx_shipping_date,
221 trx_line_type,
222 trx_line_date,
223 line_amt_includes_tax_flag,
224 line_amt,
225 trx_line_quantity,
226 unit_price,
227 exempt_certificate_number,
228 exempt_reason_code,
229 exemption_control_flag,
230 product_id, -- inventory item or memo line
231 product_org_id, -- warehouse_id
232 uom_code,
233 fob_point,
234 ship_from_party_id, -- warehouse_id
235 ship_from_location_id, -- warehouse location
236 ship_to_party_id,
237 ship_to_party_site_id,
238 bill_to_party_id,
239 bill_to_party_site_id,
240 source_application_id,
241 source_entity_code,
242 source_event_class_code,
243 source_trx_id,
244 source_line_id,
245 source_trx_level_type,
246 output_tax_classification_code,
247 trx_line_number,
248 historical_flag,
249 ctrl_hdr_tx_appl_flag, -- 'N'
250 trx_line_gl_date,
251 ship_to_location_id,
252 bill_to_location_id,
253 trx_line_currency_code,
254 trx_line_precision,
255 trx_line_mau,
256 ship_third_pty_acct_id,
257 ship_third_pty_acct_site_id,
258 ship_to_cust_acct_site_use_id,
259 poa_party_id,
260 poa_location_id,
261 poo_party_id,
262 poo_location_id,
263 cash_discount,
264 bill_from_location_id,
265 account_ccid,
266 trx_line_description,
267 product_category -- 7661349
268 )
269 SELECT
270 /*+ push_pred(STCSU) push_pred(STPSH) push_pred(STPS) push_pred(STPSH)
271 push_pred(REC) push_pred(STCSU) push_pred(STCSUH) push_pred(ML)*/
272 222,
273 ZTH.entity_code,
274 ZTH.event_class_code,
275 NULL,
276 NULL,
277 TL.customer_trx_id,
278 'LINE',
279 TL.customer_trx_line_id,
280 'INVOICE',
281 'COPY_AND_CREATE',
282 NVL(TL.sales_order_date,T.ship_date_actual),
283 DECODE(TL.inventory_item_id, NULL, 'MISC', 'ITEM'),
284 NULL,
285 DECODE(TL.amount_includes_tax_flag,'Y','A','N','N','S'),
286 TL.extended_amount,
287 TL.quantity_invoiced,
288 TL.unit_selling_price,
289 TL.tax_exempt_number,
290 TL.tax_exempt_reason_code,
291 TL.tax_exempt_flag,
292 NVL(TL.inventory_item_id, TL.memo_line_id), -- product_id
293 DECODE(TL.memo_line_id, NULL,
294 NVL(TL.warehouse_id,to_number(l_so_org_id)), NULL), -- product_org_id
295 TL.uom_code,
296 T.fob_point,
297 TL.warehouse_id, -- ship_from_party_id
298 HR.location_id, -- ship_from_location_id
299 NVL(STCA.party_id,STCAH.party_id), -- ship to party
300 NVL(STPS.party_site_id,STPSH.party_site_id), -- ship to site
301 ZTH.rounding_bill_to_party_id, -- bill to party
302 ZTH.rndg_bill_to_party_site_id, -- bill to site
303 -- null, account_ccid (set in subsequent update)
304 222,
305 ZTH.entity_code,
306 ZTH.event_class_code,
307 TL_ORIG.customer_trx_id,
308 TL_ORIG.customer_trx_line_id,
309 'LINE',
310 NVL(tl.tax_classification_code, TAX.tax_code), -- Bug 11076651
311 TL.line_number,
312 TL.historical_flag,
313 'N',
314 NVL(REC.gl_date, TRUNC(sysdate)),
315 NVL(STPSU.location_id,STPSUH.location_id),
316 BTPSU.location_id,
317 ZTH.trx_currency_code,
318 ZTH.precision,
319 ZTH.minimum_accountable_unit,
320 TL.ship_to_customer_id,
321 NVL(STPS.cust_acct_site_id,STPSH.cust_acct_site_id), -- ship_third_pty_site_id
322 NVL(STCSU.site_use_id,STCSUH.site_use_id),
323 ZTH.internal_organization_id, -- poa_party_id
324 ZTH.internal_org_location_id, -- poa_location_id
325 ZTH.internal_organization_id, -- poo_party_id (default val)
326 ZTH.internal_org_location_id, -- poo_location_id (default val)
327 TL.extended_amount * arp_etax_util.get_discount_rate(T.customer_trx_id),
328 ZTH.internal_org_location_id, -- bill_from_location_id
329 ( SELECT max(code_combination_id)
330 FROM ra_cust_trx_line_gl_dist gld
331 WHERE gld.customer_trx_line_id = TL_ORIG.customer_trx_line_id
332 AND gld.account_class = 'REV') account_ccid,
333 TL.description,
334 ML.tax_product_category -- 7661349
335 FROM
336 RA_CUSTOMER_TRX_LINES TL,
337 RA_CUSTOMER_TRX_LINES TL_ORIG,
338 RA_CUSTOMER_TRX T,
339 ZX_TRX_HEADERS_GT ZTH,
340 HZ_CUST_ACCOUNTS STCA,
341 HZ_CUST_ACCT_SITES STPS,
342 HZ_CUST_SITE_USES STCSU,
343 HZ_CUST_ACCOUNTS STCAH,
344 HZ_CUST_ACCT_SITES STPSH,
345 HZ_CUST_SITE_USES STCSUH,
346 HZ_PARTY_SITES STPSUH,
347 RA_CUST_TRX_LINE_GL_DIST REC,
348 HZ_PARTY_SITES STPSU,
349 HZ_PARTY_SITES BTPSU,
350 HR_ALL_ORGANIZATION_UNITS HR,
351 AR_MEMO_LINES_B ML,
352 AR_VAT_TAX TAX -- Bug 11076651
353 WHERE
354 TL.customer_trx_line_id = p_new_line_id
355 AND TL.line_type = 'LINE'
356 AND TL.customer_trx_id = T.customer_trx_id
357 AND TL.customer_trx_id = ZTH.trx_id
358 AND TL_ORIG.customer_trx_line_id = p_orig_line_id
359 AND TL.ship_to_customer_id = STCA.cust_account_id (+)
360 AND TL.ship_to_site_use_id = STCSU.site_use_id (+)
361 AND STCSU.cust_acct_site_id = STPS.cust_acct_site_id (+)
362 AND STPS.party_site_id = STPSU.party_site_id (+)
363 AND T.ship_to_customer_id = STCAH.cust_account_id (+)
364 AND T.ship_to_site_use_id = STCSUH.site_use_id (+)
365 AND STCSUH.cust_acct_site_id = STPSH.cust_acct_site_id (+)
366 AND STPSH.party_site_id = STPSUH.party_site_id (+)
367 AND ZTH.rndg_bill_to_party_site_id = BTPSU.party_site_id
368 AND REC.customer_trx_id (+) = T.customer_trx_id
369 AND REC.account_class (+) = 'REC'
370 AND REC.latest_rec_flag (+) = 'Y'
371 AND TL.warehouse_id = HR.organization_id (+)
372 AND TL.memo_line_id = ML.memo_line_id (+)
373 AND TL.org_id = ML.org_id (+)
374 AND TL.vat_tax_id = TAX.vat_tax_id (+); -- Bug 11076651
375
376 g_lines_inserted := SQL%ROWCOUNT;
377
378 IF PG_DEBUG in ('Y','C') THEN
379 debug('lines inserted = ' || g_lines_inserted);
380 END IF;
381
382 /* 6874006 - removed salesrep/person logic from main insert
383 and shifted it to a separate UPDATE */
384 update zx_transaction_lines ZXL
385 set (poo_party_id, poo_location_id) =
386 (select SR_PER.organization_id, -- poo_party_id
387 SR_HRL.location_id -- poo_location_id
388 from RA_CUSTOMER_TRX TRX,
389 JTF_RS_SALESREPS SR,
390 PER_ALL_ASSIGNMENTS_F SR_PER,
391 HR_ORGANIZATION_UNITS SR_HRL
392 where TRX.customer_trx_id = ZXL.trx_id
393 and TRX.primary_salesrep_id IS NOT NULL
394 and TRX.primary_salesrep_id = SR.salesrep_id
395 and TRX.org_id = SR.org_id
396 and SR.person_id = SR_PER.person_id
397 and TRX.trx_date BETWEEN nvl(SR_PER.effective_start_date, TRX.trx_date)
398 AND nvl(SR_PER.effective_end_date, TRX.trx_date)
399 and NVL(SR_PER.primary_flag, 'Y') = 'Y'
400 and SR_PER.assignment_type = 'E'
401 and SR_PER.organization_id = SR_HRL.organization_id);
402
403 IF PG_DEBUG in ('Y', 'C') THEN
404 l_lines_updated := SQL%ROWCOUNT;
405 debug('lines update (poo columns) = ' || l_lines_updated);
406 debug('arp_etax_recurr_util.insert_line()-');
407 END IF;
408
409
410 EXCEPTION
411 WHEN OTHERS
412 THEN
413 debug('EXCEPTION: ARP_ETAX_RECURR_UTIL.insert_line()- ' ||
414 SQLERRM);
415 RAISE;
416 END insert_line;
417
418 /* Inserts manual tax lines into IMPORT_GT table when
419 then are present in ra_customer_trx_lines (on original invoice)
420 with autotax flag set to 'N'
421
422 DEV NOTE:
423
424 1) What happens to legacy tax lines? By that I mean tax lines that
425 are autotax=N and predate etax. Are they converted? Can I assume
426 that the fab-five columns in zx_lines will be populated?
427
428 sent email 04/06/05 to harsh/isaac
429 RESP: Harsh says legacy lines are converted too. Yes, those columns
430 will be populated.
431
432 To extend that, I need to populate the SOURCE columns on the copied
433 lines (new ones) with info from the original invoice lines. I do not
434 need to populate teh tax lines using this routine.
435 */
436
437 PROCEDURE insert_tax_lines(
438 p_original_line_id IN NUMBER,
439 p_new_customer_trx_id IN NUMBER,
440 p_new_line_id IN NUMBER,
441 p_request_id IN NUMBER) IS
442 BEGIN
443 IF PG_DEBUG in ('Y', 'C') THEN
444 debug('arp_etax_recurr_util.insert_tax_lines()+');
445 END IF;
446
447 /* NOTE: We are passing the line_id of the original tax line
448 into this table. That means that we can get that same line
449 ID back out when inserting the shadow tax lines into
450 RA_CUSTOMER_TRX_LINES. At this point, I'm not sure
451 we need this, but I thought it was worth noting in case
452 we need to copy DFF values, etc. */
453
454 INSERT INTO ZX_IMPORT_TAX_LINES_GT
455 (
456 internal_organization_id,
457 application_id,
458 entity_code,
459 event_class_code,
460 interface_entity_code,
461 interface_tax_line_id,
462 trx_id,
463 trx_line_id,
464 tax_regime_code,
465 tax,
466 tax_status_code,
467 tax_rate_code,
468 tax_rate,
469 tax_jurisdiction_code,
470 tax_amt,
471 tax_amt_included_flag,
472 tax_exception_id,
473 tax_exemption_id,
474 exempt_reason_code,
475 exempt_certificate_number,
476 tax_line_allocation_flag,
477 summary_tax_line_number -- 4698302
478 )
479 SELECT
480 orig_line.org_id,
481 222,
482 'TRANSACTIONS',
483 'INVOICE',
484 'RA_CUSTOMER_TRX', -- interface_entity
485 p_original_line_id, -- interface_line_id
486 p_new_customer_trx_id,
487 p_new_line_id,
488 orig_etax.tax_regime_code,
489 orig_etax.tax,
490 orig_etax.tax_status_code,
491 orig_etax.tax_rate_code,
492 orig_etax.tax_rate,
493 orig_etax.tax_jurisdiction_code,
494 orig_tax.extended_amount,
495 orig_tax.amount_includes_tax_flag,
496 orig_etax.tax_exception_id,
497 orig_etax.tax_exemption_id,
498 orig_etax.exempt_reason_code,
499 orig_etax.exempt_certificate_number,
500 'N', -- no rows in LINK table
501 0 -- 4698302
502 FROM
503 RA_CUSTOMER_TRX_LINES orig_line,
504 RA_CUSTOMER_TRX_LINES orig_tax,
505 ZX_LINES orig_etax
506 WHERE
507 orig_line.customer_trx_line_id = p_original_line_id
508 AND orig_line.customer_trx_line_id = orig_tax.link_to_cust_trx_line_id
509 AND orig_tax.line_type = 'TAX'
510 AND NVL(orig_tax.autotax, 'N') = 'N'
511 AND orig_tax.tax_line_id = orig_etax.tax_line_id (+);
512
513 g_tax_lines_inserted := SQL%ROWCOUNT;
514
515 IF PG_DEBUG in ('Y', 'C') THEN
516 debug('arp_etax_recurr_util.insert_tax_lines()-');
517 END IF;
518
519 EXCEPTION
520 WHEN OTHERS
521 THEN
522 debug('EXCEPTION: ARP_ETAX_RECURR_UTIL.insert_tax_lines()-');
523 RAISE;
524
525 END insert_tax_lines;
526
527
528 /* Procedure to retrieve TAX lines from ZX and populate
529 RA_CUSTOMER_TRX_LINES accordingly */
530 PROCEDURE build_ar_tax_lines(
531 p_request_id IN NUMBER) IS
532
533 l_rows NUMBER;
534
535 BEGIN
536 IF PG_DEBUG in ('Y', 'C') THEN
537 debug('arp_etax_recurr_util.build_ar_tax_lines()+');
538 END IF;
539
540 /* Dev Notes:
541
542 End Dev Notes */
543
544 /* Insert rows into RA_CUSTOMER_TRX_LINES for the
545 new TAX lines */
546 INSERT INTO RA_CUSTOMER_TRX_LINES
547 (
548 CUSTOMER_TRX_LINE_ID,
549 LAST_UPDATE_DATE,
550 LAST_UPDATED_BY,
551 CREATION_DATE,
552 CREATED_BY,
553 LAST_UPDATE_LOGIN,
554 PROGRAM_ID,
555 PROGRAM_APPLICATION_ID,
556 CUSTOMER_TRX_ID,
557 LINE_NUMBER,
558 SET_OF_BOOKS_ID,
559 LINE_TYPE, -- TAX
560 LINK_TO_CUST_TRX_LINE_ID, -- parent line
561 DEFAULT_USSGL_TRANSACTION_CODE,
562 REQUEST_ID,
563 EXTENDED_AMOUNT,
564 TAX_RATE,
565 AUTOTAX,
566 AMOUNT_INCLUDES_TAX_FLAG,
567 TAXABLE_AMOUNT,
568 VAT_TAX_ID,
569 TAX_LINE_ID, -- ID in ZX_ table
570 ORG_ID
571 )
572 SELECT
573 ra_customer_trx_lines_s.nextval,
574 sysdate,
575 arp_standard.profile.user_id,
576 sysdate,
577 arp_standard.profile.user_id,
578 arp_standard.profile.user_id,
579 arp_standard.profile.program_id,
580 arp_standard.application_id,
581 zxt.trx_id,
582 zxt.tax_line_number,
583 arp_standard.sysparm.set_of_books_id,
584 'TAX',
585 zxt.trx_line_id,
586 plin.default_ussgl_transaction_code,
587 p_request_id,
588 zxt.tax_amt,
589 zxt.tax_rate,
590 DECODE(NVL(zxt.manually_entered_flag, 'N'), 'Y', NULL, 'Y'),
591 zxt.tax_amt_included_flag,
592 zxt.taxable_amt,
593 tax_rate_id,
594 zxt.tax_line_id,
595 plin.org_id
596 FROM ZX_LINES zxt,
597 RA_CUSTOMER_TRX_LINES plin
598 WHERE plin.request_id = p_request_id
599 AND zxt.application_id = 222
600 AND zxt.entity_code = 'TRANSACTIONS'
601 AND zxt.event_class_code in ('INVOICE','DEBIT_MEMO')
602 AND zxt.trx_id = plin.customer_trx_id
603 AND zxt.trx_level_type = 'LINE'
604 AND zxt.trx_line_id = plin.customer_trx_line_id;
605
606 l_rows := SQL%ROWCOUNT;
607
608 IF l_rows > 0
609 THEN
610 /* Stamp transaction lines with tax_classification
611 from ZX_LINES_DET_FACTORS */
612 arp_etax_util.set_default_tax_classification(p_request_id);
613
614 /* adjust for inclusive tax */
615 arp_etax_util.adjust_for_inclusive_tax(null, p_request_id, 'INV');
616 END IF;
617
618 /* Set line_recoverable and tax_recoverable */
619 arp_etax_util.set_recoverable(null, p_request_id, 'INV');
620
621 IF PG_DEBUG in ('Y', 'C') THEN
622 debug(' Number of tax lines retrieved = ' || l_rows);
623 debug('arp_etax_recurr_util.build_ar_tax_lines()-');
624 END IF;
625 END build_ar_tax_lines;
626
627 /* Procedure to extract error/validation messages from ZX
628 and insert them into RA_INTERFACE_ERRORS */
629 PROCEDURE retrieve_tax_validation_errors(p_error_count IN OUT NOCOPY NUMBER) IS
630
631 l_errors NUMBER := 0;
632
633 CURSOR zx_val_err IS
634 SELECT trx_id, trx_line_id, message_text
635 FROM zx_validation_errors_gt
636 UNION ALL
637 SELECT trx_id, trx_line_id, message_text
638 FROM ZX_ERRORS_GT;
639
640 BEGIN
641
642 IF PG_DEBUG in ('Y', 'C') THEN
643 debug('arp_etax_recurr_util.retrieve_tax_validation_errors()+');
644 END IF;
645
646 /* Dev Notes:
647
648 Just extracting messages directly to log file. In ARXREC,
649 there is no equivalent to ra_interface_errors.
650
651 */
652
653 FOR val_err IN zx_val_err LOOP
654
655 l_errors := l_errors + 1;
656
657 fnd_file.put_line(FND_FILE.LOG,
658 'EBTax calculation failure:');
659 fnd_file.put_line(FND_FILE.LOG,
660 ' customer_trx_id = ' || val_err.trx_id);
661 fnd_file.put_line(FND_FILE.LOG,
662 ' customer_trx_line_id = ' || val_err.trx_line_id);
663 fnd_file.put_line(FND_FILE.LOG, val_err.message_text);
664
665 END LOOP;
666
667 p_error_count := l_errors;
668
669 IF PG_DEBUG in ('Y', 'C') THEN
670 debug('Validation errors: ' || l_errors);
671 debug('arp_etax_recurr_util.retrieve_tax_validation_errors()-');
672 END IF;
673
674 END retrieve_tax_validation_errors;
675
676 /* Internal procedure - calculate_tax_for_copy */
677 /* wrapper for call to zx_api_pub.calculate_tax */
678
679 PROCEDURE calculate_tax_for_copy IS
680 l_return_status VARCHAR2(50);
681 l_message_count NUMBER;
682 l_message_data VARCHAR2(2000);
683 l_msg VARCHAR2(2000);
684 BEGIN
685 ZX_API_PUB.calculate_tax(
686 p_api_version => 1.0,
687 p_init_msg_list => FND_API.G_FALSE,
688 p_commit => FND_API.G_FALSE,
689 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
690 x_return_status => l_return_status,
691 x_msg_count => l_message_count,
692 x_msg_data => l_message_data
693 );
694
695 IF l_return_status = FND_API.G_RET_STS_SUCCESS
696 THEN
697 IF PG_DEBUG in ('Y', 'C') THEN
698 arp_standard.debug('calculate_tax returns successfully');
699 END IF;
700 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
701 THEN
702 IF PG_DEBUG in ('Y', 'C') THEN
703 arp_standard.debug('calculate_tax returns with validation errors');
704 END IF;
705 ELSE /* fatal error */
706 IF PG_DEBUG in ('Y', 'C') THEN
707 arp_standard.debug('calculate_tax returns failure');
708 END IF;
709
710 /* Retrieve and log errors */
711 IF l_message_count = 1
712 THEN
713 debug(l_message_data);
714 ELSIF l_message_count > 1
715 THEN
716 LOOP
717 l_msg := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
718 FND_API.G_FALSE);
719 IF l_msg IS NULL
720 THEN
721 EXIT;
722 ELSE
723 debug(l_msg);
724 END IF;
725 END LOOP;
726 END IF;
727 END IF;
728
729 END calculate_tax_for_copy;
730
731 /* External public call designed for invoice copy. This will
732 calculate the tax,
733 and insert resulting tax lines back into AR */
734 PROCEDURE calculate_tax(p_request_id IN NUMBER,
735 p_error_count IN OUT NOCOPY NUMBER,
736 p_return_status OUT NOCOPY NUMBER) IS
737 l_return_status NUMBER := 0;
738 BEGIN
739 IF PG_DEBUG in ('Y', 'C')
740 THEN
741 debug('arp_etax_recurr_util.calculate_tax()+');
742 debug('request_id = ' || p_request_id);
743 END IF;
744
745 /* Call validate_and_default_tax_attr */
746 arp_etax_util.validate_tax_int(
747 p_return_status => l_return_status,
748 p_called_from_AI => 'Y');
749
750 p_return_status := l_return_status;
751
752 IF l_return_status = 0 OR l_return_status = 1
753 THEN
754
755 /* Call import_document_with_tax */
756 calculate_tax_for_copy;
757
758 /* retrieve validation errors and display them in log */
759 retrieve_tax_validation_errors(p_error_count);
760
761 /* Pull resulting tax lines and populate RA_CUSTOMER_TRX_LINES */
762 build_ar_tax_lines(p_request_id);
763 END IF;
764
765 /* 4904679 - removed detect_missing_tax_lines */
766
767 IF PG_DEBUG in ('Y', 'C')
768 THEN
769 debug('arp_etax_recurr_util.calculate_tax()-');
770 END IF;
771 END calculate_tax;
772
773
774
775 /*
776
777 /*========================================================================
778 | INITIALIZATION SECTION
779 |
780 | DESCRIPTION
781 | Initialized global variables for controlling program flow
782 |
783 | KNOWN ISSUES
784 |
785 | NOTES
786 |
787 | MODIFICATION HISTORY
788 | Date Author Description of Changes
789 | 28-FEB-2005 MRAYMOND Created
790 *=======================================================================*/
791
792 BEGIN
793 NULL;
794
795 EXCEPTION
796 WHEN OTHERS THEN
797 debug('EXCEPTION: ARP_ETAX_RECURR_UTIL.INITIALIZE()');
798 RAISE;
799
800 END ARP_ETAX_RECURR_UTIL;