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