[Home] [Help]
PACKAGE BODY: APPS.ARP_ETAX_INVAPI_UTIL
Source
1 PACKAGE BODY ARP_ETAX_INVAPI_UTIL AS
2 /* $Header: AREBTIAB.pls 120.37.12020000.2 2012/07/27 09:33:48 kkikkise ship $ */
3
4 /*=======================================================================+
5 | Package Globals
6 +=======================================================================*/
7 g_inv_manual_tax BOOLEAN := FALSE;
8 g_tax_detected BOOLEAN := FALSE;
9 g_headers_inserted NUMBER;
10 g_lines_inserted NUMBER;
11 g_tax_lines_inserted NUMBER;
12 g_tax_line_links_inserted NUMBER;
13 g_ebt_schema VARCHAR2(30); -- stores ZX schema name
14 l_status VARCHAR2(1); -- junk variable
15 l_industry VARCHAR2(1); -- junk variable
16
17 /*========================================================================
18 | Prototype Declarations Procedures
19 *=======================================================================*/
20
21 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
22
23 /*========================================================================
24 | Prototype Declarations Functions
25 *=======================================================================*/
26
27 PROCEDURE debug(text IN VARCHAR2) IS
28 BEGIN
29 -- fnd_file.put_line(FND_FILE.LOG, text);
30 arp_debug.debug(text);
31 END;
32
33 /* Private Procedure - Inserts headers into ZX_TRX_HEADERS_GT
34
35 15-MAY-07 MRAYMOND 6033706 Added document_sub_type and
36 default_taxation_country
37 */
38 PROCEDURE insert_headers(
39 p_request_id IN NUMBER) IS
40
41 BEGIN
42 IF PG_DEBUG in ('Y', 'C') THEN
43 debug('arp_etax_invapi_util.insert_headers()+');
44 END IF;
45
46 /* 4666566 added ship_to columns. Note that invoice API
47 only supports ship_to at header level at this time
48 */
49
50 INSERT INTO ZX_TRX_HEADERS_GT
51 (
52 internal_organization_id,
53 internal_org_location_id,
54 legal_entity_id,
55 application_id,
56 ledger_id,
57 entity_code,
58 event_class_code,
59 event_type_code,
60 tax_reporting_flag,
61 trx_id,
62 trx_number,
63 trx_description,
64 doc_seq_id,
65 doc_seq_name,
66 doc_seq_value,
67 batch_source_id,
68 batch_source_name,
69 receivables_trx_type_id,
70 trx_type_description,
71 trx_date,
72 trx_communicated_date,
73 trx_due_date,
74 bill_to_cust_acct_site_use_id,
75 trx_currency_code,
76 precision,
77 minimum_accountable_unit,
78 currency_conversion_date,
79 currency_conversion_rate,
80 currency_conversion_type,
81 rounding_bill_to_party_id,
82 rndg_bill_to_party_site_id,
83 application_doc_status,
84 related_doc_application_id,
85 related_doc_entity_code,
86 related_doc_event_class_code,
87 related_doc_trx_id,
88 related_doc_number,
89 related_doc_date,
90 bill_third_pty_acct_id,
91 bill_third_pty_acct_site_id
92 )
93 SELECT
94 AR.org_id,
95 HR.location_id,
96 T.legal_entity_id,
97 222,
98 AR.set_of_books_id,
99 'TRANSACTIONS',
100 /* 7166862 */
101 DECODE(TT.type, 'INV', 'INVOICE',
102 'DM', 'DEBIT_MEMO',
103 'CM', 'CREDIT_MEMO'), -- event_class
104 DECODE(TT.type, 'INV', 'INV_CREATE',
105 'DM', 'DM_CREATE',
106 'CM', 'CM_CREATE'), -- event_type
107 'Y',
108 T.customer_trx_id,
109 T.trx_number,
110 SUBSTRB(T.comments,1,240),
111 T.doc_sequence_id,
112 -- bug 6806843
113 --TT.name,
114 SEQ.name,
115 T.doc_sequence_value,
116 T.batch_source_id,
117 TB.name,
118 T.cust_trx_type_id,
119 TT.description,
120 T.trx_date,
121 T.printing_original_date,
122 T.term_due_date,
123 T.bill_to_site_use_id,
124 T.invoice_currency_code,
125 C.precision,
126 C.minimum_accountable_unit,
127 T.exchange_date,
128 T.exchange_rate,
129 T.exchange_rate_type,
130 BTCA.party_id,
131 BTPS.party_site_id,
132 DECODE(T.status_trx,'VD','VD',NULL), -- void
133 DECODE(REL_T.customer_trx_id, NULL, NULL, 222),
134 DECODE(REL_T.customer_trx_id, NULL, NULL, 'TRANSACTIONS'),
135 DECODE(REL_T.customer_trx_id, NULL, NULL,
136 DECODE(REL_TT.type, 'INV', 'INVOICE',
137 'DM', 'DEBIT_MEMO',
138 'CM', 'CREDIT_MEMO')),
139 DECODE(REL_T.customer_trx_id, NULL, NULL, REL_T.customer_trx_id),
140 DECODE(REL_T.customer_trx_id, NULL, NULL, REL_T.trx_number),
141 DECODE(REL_T.customer_trx_id, NULL, NULL, REL_T.trx_date),
142 T.bill_to_customer_id, -- bill_third_pty_acct_id
143 BTPS.cust_acct_site_id -- bill_third_pty_acct_site_id
144 FROM RA_CUSTOMER_TRX T,
145 RA_CUST_TRX_TYPES TT,
146 RA_BATCH_SOURCES TB,
147 FND_CURRENCIES C,
148 FND_DOCUMENT_SEQUENCES SEQ,
149 AR_SYSTEM_PARAMETERS AR,
150 HZ_CUST_ACCOUNTS BTCA,
151 HZ_CUST_SITE_USES BTCSU,
152 HZ_CUST_ACCT_SITES BTPS,
153 HR_ORGANIZATION_UNITS HR,
154 RA_CUSTOMER_TRX REL_T,
155 RA_CUST_TRX_TYPES REL_TT
156 WHERE T.request_id = p_request_id
157 AND T.invoice_currency_code = C.currency_code
158 AND T.org_id = AR.org_id
159 AND T.cust_trx_type_id = TT.cust_trx_type_id
160 AND T.doc_sequence_id = SEQ.doc_sequence_id (+)
161 AND T.batch_source_id = TB.batch_source_id
162 AND T.bill_to_customer_id = BTCA.cust_account_id
163 AND T.bill_to_site_use_id = BTCSU.site_use_id
164 AND BTCSU.cust_acct_site_id = BTPS.cust_acct_site_id
165 AND HR.organization_id = T.org_id
166 AND T.legal_entity_id is NOT NULL
167 AND T.related_customer_trx_id = REL_T.customer_trx_id (+)
168 AND REL_T.cust_trx_type_id = REL_TT.cust_trx_type_id (+);
169
170 /* Store total for output in debug log */
171 g_headers_inserted := SQL%ROWCOUNT;
172
173 /* 6033706 - set document_sub_type and default_taxaction_country */
174 /* also set tax_reporting_flag */
175 UPDATE ZX_TRX_HEADERS_GT HGT
176 SET (document_sub_type, default_taxation_country,
177 tax_reporting_flag) =
178 (SELECT MAX(document_sub_type),
179 MAX(default_taxation_country),
180 MAX(decode(taxed_upstream_flag,'Y','N','Y'))
181 FROM AR_TRX_HEADER_GT IL
182 WHERE HGT.trx_id = IL.customer_trx_id
183 GROUP BY IL.customer_trx_id);
184
185 IF PG_DEBUG in ('Y', 'C') THEN
186 debug('arp_etax_invapi_util.insert_headers()-');
187 END IF;
188
189 EXCEPTION
190 WHEN NO_DATA_FOUND
191 THEN
192 debug('arp_etax_invapi_util.insert_headers()- No transaction headers to process.');
193 RETURN;
194 WHEN OTHERS
195 THEN
196 debug('EXCEPTION: ARP_ETAX_INVAPI_UTIL.insert_headers()-');
197 RAISE;
198
199 END insert_headers;
200
201 /* Private Procedure - Inserts lines (not tax) into ZX_TRANSACTION_LINES_GT */
202
203 /* Dev Note:
204 1) Invoice API does not directly support line-level ship to info
205 at this time.
206
207 2) Coded for tax-only memo lines
208
209 3) Populated poo and poa party and location values
210
211 4) set cash_discount
212
213 5) set bill_from_location_id
214
215 15-MAY-07 MRAYMOND 6033706 Added 6 additional etax columns
216 */
217 PROCEDURE insert_lines(
218 p_request_id IN NUMBER) IS
219 l_so_org_id VARCHAR2(20);
220 l_lines_updated NUMBER;
221 BEGIN
222 IF PG_DEBUG in ('Y', 'C') THEN
223 debug('arp_etax_invapi_util.insert_lines()+');
224 END IF;
225
226 l_so_org_id := oe_profile.value('SO_ORGANIZATION_ID',
227 arp_global.sysparam.org_id);
228
229 /* 4666566 added ship_to columns to line-level. Note that
230 API currently only supports header level ship to so I
231 am copying them down to line to make uptake of line-level
232 easier later */
233
234 INSERT INTO ZX_TRANSACTION_LINES_GT
235 (
236 application_id,
237 entity_code,
238 event_class_code,
239 interface_entity_code,
240 interface_line_id,
241 trx_id,
242 trx_level_type,
243 trx_line_id,
244 line_class,
245 line_level_action,
246 trx_shipping_date,
247 trx_line_type,
248 trx_line_date,
249 line_amt_includes_tax_flag, -- decode of interface column
250 line_amt,
251 trx_line_quantity,
252 unit_price,
253 exempt_certificate_number,
254 exempt_reason_code,
255 exemption_control_flag,
256 product_id, -- inventory item or memo line
257 product_org_id, -- warehouse_Id
258 uom_code,
259 fob_point,
260 ship_from_party_id, -- warehouse_id
261 ship_from_location_id, -- warehouse location
262 bill_to_party_id,
263 bill_to_party_site_id,
264 source_application_id,
265 source_entity_code,
266 source_event_class_code,
267 source_trx_id,
268 source_line_id,
269 source_trx_level_type,
270 output_tax_classification_code,
271 trx_line_number,
272 historical_flag,
273 ctrl_hdr_tx_appl_flag, -- 'N'
274 trx_line_gl_date,
275 ship_to_location_id,
276 bill_to_location_id,
277 trx_line_currency_code,
278 trx_line_precision,
279 trx_line_mau,
280 ship_third_pty_acct_id,
281 ship_third_pty_acct_site_id,
282 ship_to_cust_acct_site_use_id,
283 ship_to_party_id,
284 ship_to_party_site_id,
285 poa_party_id,
286 poa_location_id,
287 poo_party_id,
288 poo_location_id,
289 cash_discount,
290 bill_from_location_id,
291 trx_business_category,
292 product_fisc_classification,
293 product_category,
294 product_type,
295 line_intended_use,
296 assessable_value,
297 user_defined_fisc_class,
298 account_ccid,
299 trx_line_description
300 )
301 SELECT
302 222,
303 ZTH.entity_code,
304 ZTH.event_class_code,
305 'AR_TRX_LINES_GT', -- interface_entity_code
306 TLG.trx_line_id, -- interface_entity_line_id
307 TL.customer_trx_id,
308 'LINE',
309 TL.customer_trx_line_id,
310 /* 7166862 */
311 ZTH.EVENT_CLASS_CODE,
312 DECODE(TL.taxable_flag, 'N', 'RECORD_WITH_NO_TAX',
313 DECODE(ML.line_type,'TAX','LINE_INFO_TAX_ONLY',
314 'CREATE')),
315 NVL(TL.sales_order_date,T.ship_date_actual),
316 DECODE(TL.inventory_item_id, NULL, 'MISC', 'ITEM'),
317 NULL,
318 DECODE(TL.amount_includes_tax_flag,'Y','A','N','N','S'),
319 TL.extended_amount,
320 TL.quantity_invoiced,
321 TL.unit_selling_price,
322 TL.tax_exempt_number,
323 TL.tax_exempt_reason_code,
324 TL.tax_exempt_flag,
325 NVL(TL.inventory_item_id, TL.memo_line_id), -- product_id
326 DECODE(TL.memo_line_id, NULL,
327 NVL(TL.warehouse_id,to_number(l_so_org_id)), NULL),-- product_org_id
328 TL.uom_code,
329 T.fob_point,
330 TL.warehouse_id, -- ship_from_party_id
331 HR.location_id, -- ship_from_location_id
332 ZTH.rounding_bill_to_party_id, -- bill to party
333 ZTH.rndg_bill_to_party_site_id, -- bill to site
334 -- null, account_ccid (set in subsequent update)
335 TLG.source_application_id,
336 TLG.source_entity_code,
337 TLG.source_event_class_code,
338 TLG.source_trx_id,
339 TLG.source_trx_line_id,
340 TLG.source_trx_line_type,
341 TL.tax_classification_code,
342 TL.line_number,
343 TL.historical_flag,
344 'N',
345 NVL(REC.gl_date, TRUNC(sysdate)),
346 STPSU.location_id,
347 BTPSU.location_id,
348 ZTH.trx_currency_code,
349 ZTH.precision,
350 ZTH.minimum_accountable_unit,
351 T.ship_to_customer_id,
352 STPS.cust_acct_site_id,
353 STCSU.site_use_id,
354 STCA.party_id, -- ship to party
355 STPS.party_site_id, -- ship to site
356 ZTH.internal_organization_id, -- poa_party_id
357 ZTH.internal_org_location_id, -- poa_location_id
358 ZTH.internal_organization_id, -- poo_party_id (default value)
359 ZTH.internal_org_location_id, -- poo_location_id (default value)
360 TL.extended_amount * arp_etax_util.get_discount_rate(T.customer_trx_id),
361 ZTH.internal_org_location_id, -- bill_from_location_id
362 TLG.trx_business_category, -- 6033706
363 TLG.product_fisc_classification, -- 6033706
364 NVL(TLG.product_category,ML.tax_product_category),
365 TLG.product_type, -- 6033706
366 TLG.line_intended_use, -- 6033706
367 TLG.assessable_value, -- 6033706
368 TLG.user_defined_fisc_class, -- 6033706
369 ( SELECT max(code_combination_id)
370 FROM ra_cust_trx_line_gl_dist gld
371 WHERE gld.customer_trx_line_id = TL.customer_trx_line_id
372 AND gld.account_class = 'REV') account_ccid,
373 TL.description
374 FROM
375 RA_CUSTOMER_TRX_LINES TL,
376 RA_CUSTOMER_TRX T,
377 AR_TRX_LINES_GT TLG,
378 ZX_TRX_HEADERS_GT ZTH,
379 HZ_CUST_ACCOUNTS STCA,
380 HZ_CUST_ACCT_SITES STPS,
381 HZ_CUST_SITE_USES STCSU,
382 RA_CUST_TRX_LINE_GL_DIST REC,
383 HZ_PARTY_SITES STPSU,
384 HZ_PARTY_SITES BTPSU,
385 HR_ALL_ORGANIZATION_UNITS HR,
386 AR_MEMO_LINES_B ML
387 WHERE
388 TL.request_id = p_request_id
389 AND TL.line_type = 'LINE'
390 AND TL.customer_trx_id = T.customer_trx_id
391 AND TL.customer_trx_line_id = TLG.customer_trx_line_id
392 AND TL.customer_trx_id = ZTH.trx_id
393 AND T.ship_to_customer_id =
394 STCA.cust_account_id (+)
395 AND T.ship_to_site_use_id =
396 STCSU.site_use_id (+)
397 AND STCSU.cust_acct_site_id = STPS.cust_acct_site_id (+)
398 AND STPS.party_site_id = STPSU.party_site_id (+)
399 AND ZTH.rndg_bill_to_party_site_id = BTPSU.party_site_id
400 AND REC.customer_trx_id = T.customer_trx_id
401 AND REC.account_class = 'REC'
402 AND REC.latest_rec_flag = 'Y'
403 AND TL.warehouse_id = HR.organization_id (+)
404 AND TL.memo_line_id = ML.memo_line_id (+)
405 AND TL.org_id = ML.org_id (+);
406
407 g_lines_inserted := SQL%ROWCOUNT;
408
409 IF PG_DEBUG IN ('Y','C') THEN
410 debug('lines inserted = ' || g_lines_inserted);
411 END IF;
412
413 /* 6874006 - removed salesrep/person logic from main insert
414 and shifted it to a separate UPDATE */
415 update zx_transaction_lines ZXL
416 set (poo_party_id, poo_location_id) =
417 (select SR_PER.organization_id, -- poo_party_id
418 SR_HRL.location_id -- poo_location_id
419 from RA_CUSTOMER_TRX TRX,
420 JTF_RS_SALESREPS SR,
421 PER_ALL_ASSIGNMENTS_F SR_PER,
422 HR_ORGANIZATION_UNITS SR_HRL
423 where TRX.customer_trx_id = ZXL.trx_id
424 and TRX.primary_salesrep_id IS NOT NULL
425 and TRX.primary_salesrep_id = SR.salesrep_id
426 and TRX.org_id = SR.org_id
427 and SR.person_id = SR_PER.person_id
428 and TRX.trx_date BETWEEN nvl(SR_PER.effective_start_date, TRX.trx_date)
429 AND nvl(SR_PER.effective_end_date, TRX.trx_date)
430 and NVL(SR_PER.primary_flag, 'Y') = 'Y'
431 and SR_PER.assignment_type = 'E'
432 and SR_PER.organization_id = SR_HRL.organization_id);
433
434 IF PG_DEBUG in ('Y', 'C') THEN
435 l_lines_updated := SQL%ROWCOUNT;
436 debug('lines update (poo columns) = ' || l_lines_updated);
437 debug('arp_etax_invapi_util.insert_lines()-');
438 END IF;
439
440
441 EXCEPTION
442 WHEN OTHERS
443 THEN
444 debug('EXCEPTION: ARP_ETAX_INVAPI_UTIL.insert_lines()- ' ||
445 SQLERRM);
446 RAISE;
447 END insert_lines;
448
449 /* Inserts manual tax lines into IMPORT_GT table when
450 they are present in ZX_TRX_LINES_GT
451
452 DEV NOTE:
453
454 */
455
456 PROCEDURE insert_tax_lines IS
457
458 BEGIN
459 IF PG_DEBUG in ('Y', 'C') THEN
460 debug('arp_etax_invapi_util.insert_tax_lines()+');
461 END IF;
462
463
464 INSERT INTO ZX_IMPORT_TAX_LINES_GT
465 (
466 internal_organization_id,
467 application_id,
468 entity_code,
469 event_class_code,
470 interface_entity_code,
471 interface_tax_line_id,
472 trx_id,
473 trx_line_id,
474 tax_regime_code,
475 tax,
476 tax_status_code,
477 tax_rate_code,
478 tax_rate,
479 tax_amt,
480 tax_jurisdiction_code,
481 tax_amt_included_flag,
482 tax_exception_id,
483 tax_exemption_id,
484 exempt_reason_code,
485 exempt_certificate_number,
486 tax_line_allocation_flag,
487 summary_tax_line_number -- 4698302
488 )
489 SELECT
490 ZTH.internal_organization_id,
491 222,
492 ZTH.entity_code,
493 ZTH.event_class_code,
494 'AR_TRX_LINES_GT',
495 GTL.trx_line_id, -- tax line in AR_GT table
496 ZTH.trx_id,
497 GTL.link_to_cust_trx_line_id,
498 GTL.tax_regime_code,
499 GTL.tax,
500 GTL.tax_status_code,
501 GTL.tax_rate_code,
502 GTL.tax_rate,
503 GTL.extended_amount,
504 GTL.tax_jurisdiction_code,
505 GTL.amount_includes_tax_flag,
506 GTL.item_exception_rate_id,
507 GTL.tax_exemption_id,
508 GTL.tax_exempt_reason_code,
509 GTL.tax_exempt_number,
510 'N', -- no lines in LINK table
511 GTL.trx_line_id -- 4698302
512 FROM
513 AR_TRX_LINES_GT GTL, -- tax lines
514 ZX_TRX_HEADERS_GT ZTH
515 WHERE
516 GTL.line_type = 'TAX'
517 AND GTL.customer_trx_id = ZTH.trx_id;
518
519 g_tax_lines_inserted := SQL%ROWCOUNT;
520
521 /* If we processed manual tax lines, we need to change
522 the line-level action to CREATE_WITH_TAX */
523 IF g_tax_lines_inserted > 0
524 THEN
525 /* set line level action */
526 UPDATE ZX_TRANSACTION_LINES_GT line
527 SET line_level_action = 'CREATE_WITH_TAX'
528 WHERE EXISTS
529 (SELECT 'manual tax line'
530 FROM ZX_IMPORT_TAX_LINES_GT tax
531 WHERE tax.trx_line_id = line.trx_line_id);
532 END IF;
533
534
535 IF PG_DEBUG in ('Y', 'C') THEN
536 debug('arp_etax_invapi_util.insert_tax_lines()-');
537 END IF;
538
539 EXCEPTION
540 WHEN OTHERS
541 THEN
542 debug('EXCEPTION: ARP_ETAX_INVAPI_UTIL.insert_tax_lines()-');
543 RAISE;
544
545 END insert_tax_lines;
546
547
548 /*========================================================================
549 | PUBLIC PROCEDURE populate_ebt_gt
550 |
551 | DESCRIPTION
552 | Procedure inserts data into ebt GT tables for processing.
553 | This code was designed specifically for use with invoice api.
554 |
555 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
556 |
557 | PARAMETERS
558 | p_request_id IN NUMBER request_id used for invoice api batch
559 | KNOWN ISSUES
560 |
561 | NOTES
562 |
563 | MODIFICATION HISTORY
564 | Date Author Description of Changes
565 | 11-APR-2005 MRAYMOND Created
566 |
567 *=======================================================================*/
568 PROCEDURE populate_ebt_gt(p_request_id IN NUMBER) IS
569
570 BEGIN
571
572 IF PG_DEBUG in ('Y', 'C') THEN
573 debug('arp_etax_invapi_util.populate_ebt_gt()+');
574 END IF;
575
576 /* Insert lines into ZX_TRX_HEADERS_GT */
577 insert_headers(p_request_id);
578
579 /* Insert lines into ZX_TRANSACTION_LINES_GT */
580 insert_lines(p_request_id);
581
582 /* Insert manual tax lines */
583 insert_tax_lines;
584
585 IF PG_DEBUG in ('Y', 'C') THEN
586 debug('headers inserted : ' || g_headers_inserted);
587 debug('lines inserted : ' || g_lines_inserted);
588 debug('tax lines inserted : ' || g_tax_lines_inserted);
589 debug('arp_etax_invapi_util.populate_ebt_gt()-');
590 END IF;
591
592 EXCEPTION
593 WHEN OTHERS THEN
594 IF PG_DEBUG in ('Y', 'C') THEN
595 debug('EXCEPTION: ARP_ETAX_INVAPI_UTIL.populate_ebt_gt()');
596 END IF;
597 RAISE;
598
599 END populate_ebt_gt;
600
601 /* Procedure to retrieve TAX lines from ZX and populate
602 RA_CUSTOMER_TRX_LINES accordingly */
603 PROCEDURE build_ar_tax_lines(p_request_id IN NUMBER) IS
604
605 l_rows1 NUMBER;
606 l_rows2 NUMBER;
607 /* Bug 9585582 */
608 CURSOR ctt IS
609 SELECT DISTINCT types.type
610 FROM ra_customer_trx trx ,
611 ra_cust_trx_types types
612 WHERE trx.cust_trx_type_id = types.cust_trx_type_id
613 AND trx.request_id=p_request_id;
614
615
616 BEGIN
617 IF PG_DEBUG in ('Y', 'C') THEN
618 debug('arp_etax_invapi_util.build_ar_tax_lines()+');
619 END IF;
620
621 /* Dev Notes:
622 1) swapped zx_detail_tax_lines_gt to zx_lines per svaze in IM
623 conv on 25-MAY
624
625 End Dev Notes */
626
627 /* Insert rows into RA_CUSTOMER_TRX_LINES for the
628 new TAX lines */
629 /*4410461 Breaking the insert into two for Manual and Automatic Tax lines*/
630
631 INSERT INTO RA_CUSTOMER_TRX_LINES
632 (
633 CUSTOMER_TRX_LINE_ID,
634 LAST_UPDATE_DATE,
635 LAST_UPDATED_BY,
636 CREATION_DATE,
637 CREATED_BY,
638 LAST_UPDATE_LOGIN,
639 PROGRAM_ID,
640 PROGRAM_APPLICATION_ID,
641 CUSTOMER_TRX_ID,
642 LINE_NUMBER,
643 SET_OF_BOOKS_ID,
644 LINE_TYPE, -- TAX
645 LINK_TO_CUST_TRX_LINE_ID, -- parent line
646 DEFAULT_USSGL_TRANSACTION_CODE,
647 REQUEST_ID,
648 EXTENDED_AMOUNT,
649 TAX_RATE,
650 AUTOTAX,
651 AMOUNT_INCLUDES_TAX_FLAG,
652 TAXABLE_AMOUNT,
653 VAT_TAX_ID,
654 TAX_LINE_ID, -- ID in ZX_ table
655 INTERFACE_LINE_CONTEXT,
656 INTERFACE_LINE_ATTRIBUTE1,
657 INTERFACE_LINE_ATTRIBUTE2,
658 INTERFACE_LINE_ATTRIBUTE3,
659 INTERFACE_LINE_ATTRIBUTE4,
660 INTERFACE_LINE_ATTRIBUTE5,
661 INTERFACE_LINE_ATTRIBUTE6,
662 INTERFACE_LINE_ATTRIBUTE7,
663 INTERFACE_LINE_ATTRIBUTE8,
664 INTERFACE_LINE_ATTRIBUTE9,
665 INTERFACE_LINE_ATTRIBUTE10,
666 INTERFACE_LINE_ATTRIBUTE11,
667 INTERFACE_LINE_ATTRIBUTE12,
668 INTERFACE_LINE_ATTRIBUTE13,
669 INTERFACE_LINE_ATTRIBUTE14,
670 INTERFACE_LINE_ATTRIBUTE15,
671 ORG_ID
672 )
673 SELECT
674 mtax.customer_trx_line_id,
675 sysdate,
676 arp_standard.profile.user_id,
677 sysdate,
678 arp_standard.profile.user_id,
679 arp_standard.profile.user_id,
680 arp_standard.profile.program_id,
681 arp_standard.application_id,
682 zxt.trx_id,
683 zxt.tax_line_number,
684 arp_standard.sysparm.set_of_books_id,
685 'TAX',
686 zxt.trx_line_id,
687 plin.default_ussgl_transaction_code,
688 plin.request_id,
689 zxt.tax_amt,
690 zxt.tax_rate,
691 DECODE(NVL(zxt.manually_entered_flag, 'N'), 'Y', NULL, 'Y'),
692 zxt.tax_amt_included_flag,
693 zxt.taxable_amt,
694 zxt.tax_rate_id,
695 zxt.tax_line_id,
696 mtax.interface_line_context,
697 mtax.interface_line_attribute1,
698 mtax.interface_line_attribute2,
699 mtax.interface_line_attribute3,
700 mtax.interface_line_attribute4,
701 mtax.interface_line_attribute5,
702 mtax.interface_line_attribute6,
703 mtax.interface_line_attribute7,
704 mtax.interface_line_attribute8,
705 mtax.interface_line_attribute9,
706 mtax.interface_line_attribute10,
707 mtax.interface_line_attribute11,
708 mtax.interface_line_attribute12,
709 mtax.interface_line_attribute13,
710 mtax.interface_line_attribute14,
711 mtax.interface_line_attribute15,
712 plin.org_id
713 FROM ZX_LINES zxt,
714 RA_CUSTOMER_TRX_LINES plin,
715 AR_TRX_LINES_GT mtax
716 WHERE plin.request_id = p_request_id
717 AND zxt.application_id = 222
718 AND zxt.entity_code = 'TRANSACTIONS'
719 /* 7166862 */
720 AND zxt.event_class_code IN( 'INVOICE','CREDIT_MEMO','DEBIT_MEMO')
721 AND zxt.trx_id = plin.customer_trx_id
722 AND zxt.trx_level_type = 'LINE'
723 AND zxt.trx_line_id = plin.customer_trx_line_id
724 AND NVL(zxt.manually_entered_flag, 'N') = 'Y'
725 AND zxt.interface_tax_line_id = mtax.trx_line_id (+);
726
727 l_rows1 := SQL%ROWCOUNT;
728
729 INSERT INTO RA_CUSTOMER_TRX_LINES
730 (
731 CUSTOMER_TRX_LINE_ID,
732 LAST_UPDATE_DATE,
733 LAST_UPDATED_BY,
734 CREATION_DATE,
735 CREATED_BY,
736 LAST_UPDATE_LOGIN,
737 PROGRAM_ID,
738 PROGRAM_APPLICATION_ID,
739 CUSTOMER_TRX_ID,
740 LINE_NUMBER,
741 SET_OF_BOOKS_ID,
742 LINE_TYPE, -- TAX
743 LINK_TO_CUST_TRX_LINE_ID, -- parent line
744 DEFAULT_USSGL_TRANSACTION_CODE,
745 REQUEST_ID,
746 EXTENDED_AMOUNT,
747 TAX_RATE,
748 AUTOTAX,
749 AMOUNT_INCLUDES_TAX_FLAG,
750 TAXABLE_AMOUNT,
751 VAT_TAX_ID,
752 TAX_LINE_ID, -- ID in ZX_ table
753 INTERFACE_LINE_CONTEXT,
754 INTERFACE_LINE_ATTRIBUTE1,
755 INTERFACE_LINE_ATTRIBUTE2,
756 INTERFACE_LINE_ATTRIBUTE3,
757 INTERFACE_LINE_ATTRIBUTE4,
758 INTERFACE_LINE_ATTRIBUTE5,
759 INTERFACE_LINE_ATTRIBUTE6,
760 INTERFACE_LINE_ATTRIBUTE7,
761 INTERFACE_LINE_ATTRIBUTE8,
762 INTERFACE_LINE_ATTRIBUTE9,
763 INTERFACE_LINE_ATTRIBUTE10,
764 INTERFACE_LINE_ATTRIBUTE11,
765 INTERFACE_LINE_ATTRIBUTE12,
766 INTERFACE_LINE_ATTRIBUTE13,
767 INTERFACE_LINE_ATTRIBUTE14,
768 INTERFACE_LINE_ATTRIBUTE15,
769 ORG_ID
770 )
771 SELECT
772 ra_customer_trx_lines_s.nextval,
773 sysdate,
774 arp_standard.profile.user_id,
775 sysdate,
776 arp_standard.profile.user_id,
777 arp_standard.profile.user_id,
778 arp_standard.profile.program_id,
779 arp_standard.application_id,
780 zxt.trx_id,
781 zxt.tax_line_number,
782 arp_standard.sysparm.set_of_books_id,
783 'TAX',
784 zxt.trx_line_id,
785 plin.default_ussgl_transaction_code,
786 plin.request_id,
787 zxt.tax_amt,
788 zxt.tax_rate,
789 DECODE(NVL(zxt.manually_entered_flag, 'N'), 'Y', NULL, 'Y'),
790 zxt.tax_amt_included_flag,
791 zxt.taxable_amt,
792 zxt.tax_rate_id,
793 zxt.tax_line_id,
794 mtax.interface_line_context,
795 mtax.interface_line_attribute1,
796 mtax.interface_line_attribute2,
797 mtax.interface_line_attribute3,
798 mtax.interface_line_attribute4,
799 mtax.interface_line_attribute5,
800 mtax.interface_line_attribute6,
801 mtax.interface_line_attribute7,
802 mtax.interface_line_attribute8,
803 mtax.interface_line_attribute9,
804 mtax.interface_line_attribute10,
805 mtax.interface_line_attribute11,
806 mtax.interface_line_attribute12,
807 mtax.interface_line_attribute13,
808 mtax.interface_line_attribute14,
809 mtax.interface_line_attribute15,
810 plin.org_id
811 FROM ZX_LINES zxt,
812 RA_CUSTOMER_TRX_LINES plin,
813 AR_TRX_LINES_GT mtax
814 WHERE plin.request_id = p_request_id
815 AND zxt.application_id = 222
816 AND zxt.entity_code = 'TRANSACTIONS'
817 /* 7166862 */
818 AND zxt.event_class_code in( 'INVOICE','CREDIT_MEMO','DEBIT_MEMO')
819 AND zxt.trx_id = plin.customer_trx_id
820 AND zxt.trx_level_type = 'LINE'
821 AND zxt.trx_line_id = plin.customer_trx_line_id
822 AND NVL(zxt.manually_entered_flag, 'N') = 'N'
823 AND zxt.interface_tax_line_id = mtax.trx_line_id (+);
824
825 l_rows2 := SQL%ROWCOUNT;
826
827 /* Bug 9585582 */
828
829
830
831 IF (l_rows1+l_rows2) > 0
832 THEN
833
834 For rec in ctt
835 LOOP
836 /* Stamp transaction lines with tax_classification
837 from ZX_LINES_DET_FACTORS */
838 arp_etax_util.set_default_tax_classification(p_request_id,rec.type);
839
840 /* adjust for inclusive tax */
841 arp_etax_util.adjust_for_inclusive_tax(null, p_request_id, rec.type);
842
843 END LOOP;
844 END IF;
845
846 /* Set line_recoverable and tax_recoverable */
847 arp_etax_util.set_recoverable(null, p_request_id,null);
848
849
850
851 IF PG_DEBUG in ('Y', 'C') THEN
852 debug(' Number of tax lines retrieved autotax lines = ' || l_rows2);
853 debug(' Number of tax lines retrieved manualtax lines = ' || l_rows1);
854 debug('arp_etax_invapi_util.build_ar_tax_lines()-');
855 END IF;
856 END build_ar_tax_lines;
857
858 /* Procedure to extract error/validation messages from ZX
859 and insert them into RA_INTERFACE_ERRORS */
860 PROCEDURE retrieve_tax_validation_errors(p_error_count IN OUT NOCOPY NUMBER) IS
861
862 l_trx_errors NUMBER := 0;
863 l_trx_validation_errors NUMBER := 0;
864 BEGIN
865
866 IF PG_DEBUG in ('Y', 'C') THEN
867 debug('arp_etax_invapi_util.retrieve_tax_validation_errors()+');
868 END IF;
869
870 /* Dev Notes:
871 When an error is returned on any transaction line, the API
872 will automatically reject the entire transaction. We'll insert
873 at least one row in ar_trx_errors_gt for each transaction
874 that fails in etax. Code in the API will then selectively
875 roll back data for each transaction with one or more failures.
876 */
877
878 /* Line level errors */
879 INSERT INTO AR_TRX_ERRORS_GT
880 (
881 trx_header_id,
882 trx_line_id,
883 error_message
884 )
885 SELECT
886 il.trx_header_id,
887 il.trx_line_id,
888 zxe.message_text
889 FROM ZX_VALIDATION_ERRORS_GT zxe,
890 AR_TRX_LINES_GT il
891 WHERE nvl(zxe.interface_tax_line_id, zxe.interface_line_id) =
892 il.trx_line_id;
893
894 l_trx_validation_errors := SQL%ROWCOUNT;
895
896 INSERT INTO AR_TRX_ERRORS_GT
897 (
898 trx_header_id,
899 trx_line_id,
900 error_message
901 )
902 SELECT
903 it.trx_header_id,
904 il.trx_line_id,
905 zxe.message_text
906 FROM ZX_ERRORS_GT zxe,
907 AR_TRX_HEADER_GT it,
908 AR_TRX_LINES_GT il
909 WHERE zxe.trx_id = it.customer_trx_id
910 AND NVL(zxe.trx_line_id, -99) =
911 il.customer_trx_line_id (+);
912
913 l_trx_errors := SQL%ROWCOUNT;
914
915 p_error_count := l_trx_errors + l_trx_validation_errors;
916
917 IF PG_DEBUG in ('Y', 'C') THEN
918 debug('Validation errors: ' || l_trx_validation_errors);
919 debug('Calculation errors: ' || l_trx_errors);
920 debug('arp_etax_invapi_util.retrieve_tax_validation_errors()-');
921 END IF;
922
923 END retrieve_tax_validation_errors;
924
925 /* External public call designed for invoice api. This will
926 populate the ZX tables, validate the data, calculate the tax,
927 and insert resulting tax lines back into AR */
928 PROCEDURE calculate_tax(p_request_id IN NUMBER,
929 p_error_count IN OUT NOCOPY NUMBER,
930 p_return_status OUT NOCOPY NUMBER) IS
931 l_return_status NUMBER := 0;
932 BEGIN
933 IF PG_DEBUG in ('Y', 'C')
934 THEN
935 debug('arp_etax_invapi_util.calculate_tax()+');
936 END IF;
937
938 /* Insert data into ebt tables */
939 populate_ebt_gt(p_request_id);
940
941 /* Only call etax if there is something to process */
942 IF g_headers_inserted > 0 AND
943 (g_lines_inserted > 0 OR g_tax_lines_inserted > 0)
944 THEN
945 /* Call validate_and_default_tax_attr */
946 arp_etax_util.validate_tax_int(l_return_status);
947 p_return_status := l_return_status;
948
949 IF l_return_status = 0 OR l_return_status = 1
950 THEN
951 /* Call import_document_with_tax */
952 arp_etax_util.calculate_tax_int(l_return_status);
953 p_return_status := l_return_status;
954 END IF;
955
956 IF l_return_status = 0 OR l_return_status = 1
957 THEN
958 /* retrieve validation errors and populate RA_INTERFACE_ERRORS */
959 retrieve_tax_validation_errors(p_error_count);
960
961 /* Pull resulting tax lines and populate RA_CUSTOMER_TRX_LINES */
962 build_ar_tax_lines(p_request_id);
963 END IF;
964
965 /* 4904679 - removed logic to detect missing tax lines */
966
967 ELSE
968 IF PG_DEBUG in ('Y', 'C')
969 THEN
970 debug(' no lines inserted for processing, skipping etax call');
971 END IF;
972 END IF;
973
974 IF PG_DEBUG in ('Y', 'C')
975 THEN
976 debug('arp_etax_invapi_util.calculate_tax()-');
977 END IF;
978 END calculate_tax;
979
980 PROCEDURE cleanup_tax(p_trx_id IN NUMBER) IS
981
982 BEGIN
983
984 /* Now call the API to synchronize the repository */
985 ARP_ETAX_UTIL.global_document_update(p_trx_id,
986 NULL,
987 'DELETE');
988
989 END cleanup_tax;
990
991 /*
992
993 /*========================================================================
994 | INITIALIZATION SECTION
995 |
996 | DESCRIPTION
997 | Initialized global variables for controlling program flow
998 |
999 | KNOWN ISSUES
1000 |
1001 | NOTES
1002 |
1003 | MODIFICATION HISTORY
1004 | Date Author Description of Changes
1005 | 28-FEB-2005 MRAYMOND Created
1006 *=======================================================================*/
1007
1008 BEGIN
1009 /* Get eTax schema name for TRUNCATE calls */
1010 IF FND_INSTALLATION.get_app_info('ZX', l_status, l_industry, g_ebt_schema)
1011 THEN
1012
1013 IF PG_DEBUG in ('Y', 'C') THEN
1014 debug('Retrieved schema for ZX : ' || g_ebt_schema);
1015 END IF;
1016 ELSE
1017 IF PG_DEBUG in ('Y', 'C') THEN
1018 debug('Problem retrieving ZX schema name from fnd_installation');
1019 END IF;
1020 debug('EXCEPTION: ARP_ETAX_AUTOINV_UTIL.INITIALIZE()');
1021 END IF;
1022
1023 EXCEPTION
1024 WHEN OTHERS THEN
1025 debug('EXCEPTION: ARP_ETAX_INVAPI_UTIL.INITIALIZE()');
1026 RAISE;
1027
1028 END ARP_ETAX_INVAPI_UTIL;