1 PACKAGE BODY ARP_ETAX_AUTOINV_UTIL AS
2 /* $Header: AREBTAIB.pls 120.31.12010000.9 2008/12/22 17:49:43 mraymond ship $ */
3
4 /*=======================================================================+
5 | Package Globals
6 +=======================================================================*/
7 g_inv_manual_tax BOOLEAN := FALSE;
8 g_cm_manual_tax BOOLEAN := FALSE;
9 g_tax_detected BOOLEAN := FALSE;
10 g_latin_tax BOOLEAN := FALSE;
11 g_ebt_gt_populated BOOLEAN := FALSE; -- 7329586
12 g_headers_inserted NUMBER;
13 g_lines_inserted NUMBER;
14 g_tax_lines_inserted NUMBER;
15 g_tax_line_links_inserted NUMBER;
16 l_status VARCHAR2(1); -- junk variable
17 l_industry VARCHAR2(1); -- junk variable
18
19 /*========================================================================
20 | Prototype Declarations Procedures
21 *=======================================================================*/
22
23 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
24
25 /*========================================================================
26 | Prototype Declarations Functions
27 *=======================================================================*/
28
29 PROCEDURE debug(text IN VARCHAR2) IS
30 BEGIN
31 fnd_file.put_line(FND_FILE.LOG, text);
32 -- arp_standard.debug(text);
33 END;
34
35 /* Private Procedure - Inserts headers into ZX_TRX_HEADERS_GT
36
37 15-MAY-07 MRAYMOND 6033706 Added document_sub_type and
38 default_taxation_country
39 */
40 PROCEDURE insert_headers(
41 p_request_id IN NUMBER,
42 p_phase IN VARCHAR2) IS
43
44 BEGIN
45 IF PG_DEBUG in ('Y', 'C') THEN
46 debug('arp_etax_autoinv_util.insert_headers()+');
47 END IF;
48
49 INSERT INTO ZX_TRX_HEADERS_GT
50 (
51 internal_organization_id,
52 internal_org_location_id,
53 legal_entity_id,
54 application_id,
55 ledger_id,
56 entity_code,
57 event_class_code,
58 event_type_code,
59 tax_reporting_flag,
60 trx_id,
61 trx_number,
62 trx_description,
63 doc_seq_id,
64 doc_seq_name,
65 doc_seq_value,
66 batch_source_id,
67 batch_source_name,
68 receivables_trx_type_id,
69 trx_type_description,
70 trx_date,
71 trx_communicated_date,
72 trx_due_date,
73 trx_currency_code,
74 precision,
75 minimum_accountable_unit,
76 currency_conversion_date,
77 currency_conversion_rate,
78 currency_conversion_type,
79 rounding_bill_to_party_id,
80 rndg_bill_to_party_site_id,
81 bill_third_pty_acct_id,
82 bill_to_cust_acct_site_use_id,
83 bill_third_pty_acct_site_id,
84 application_doc_status,
85 related_doc_application_id,
86 related_doc_entity_code,
87 related_doc_event_class_code,
88 related_doc_trx_id,
89 related_doc_number,
90 related_doc_date
91 )
92 SELECT
93 AR.org_id,
94 HR.location_id,
95 T.legal_entity_id,
96 222,
97 AR.set_of_books_id,
98 'TRANSACTIONS',
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 SEQ.name, -- 6806843
111 T.doc_sequence_value,
112 T.batch_source_id,
113 TB.name,
114 T.cust_trx_type_id,
115 TT.description,
116 T.trx_date,
117 T.printing_original_date,
118 T.term_due_date,
119 T.invoice_currency_code,
120 C.precision,
121 C.minimum_accountable_unit,
122 T.exchange_date,
123 T.exchange_rate,
124 T.exchange_rate_type,
125 BTCA.party_id,
126 BTPS.party_site_id,
127 T.bill_to_customer_id,
128 T.bill_to_site_use_id,
129 BTPS.cust_acct_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 NVL(T.previous_customer_trx_id, -99) =
154 DECODE(p_phase, 'INV', -99, T.previous_customer_trx_id)
155 AND T.invoice_currency_code = C.currency_code
156 AND T.org_id = AR.org_id
157 AND T.cust_trx_type_id = TT.cust_trx_type_id
158 AND T.doc_sequence_id = SEQ.doc_sequence_id (+)
159 AND T.batch_source_id = TB.batch_source_id
160 AND T.bill_to_customer_id = BTCA.cust_account_id
161 AND T.bill_to_site_use_id = BTCSU.site_use_id
162 AND BTCSU.cust_acct_site_id = BTPS.cust_acct_site_id
163 AND HR.organization_id = T.org_id
164 AND T.legal_entity_id is NOT NULL
165 AND T.related_customer_trx_id = REL_T.customer_trx_id (+)
166 AND REL_T.cust_trx_type_id = REL_TT.cust_trx_type_id (+);
167
168 /* Store total for output in debug log */
169 g_headers_inserted := SQL%ROWCOUNT;
170
171 /* 6033706 - set document_sub_type and default_taxaction_country */
172 /* also set tax_reporting_flag when necessary */
173 UPDATE ZX_TRX_HEADERS_GT HGT
174 SET (document_sub_type, default_taxation_country,
175 tax_reporting_flag) =
176 (SELECT MAX(document_sub_type),
177 MAX(default_taxation_country),
178 MAX(decode(IL.taxed_upstream_flag,'Y','N','Y'))
179 FROM RA_INTERFACE_LINES IL
180 WHERE HGT.trx_id = IL.customer_trx_id
181 GROUP BY IL.customer_trx_id);
182
183 IF PG_DEBUG in ('Y', 'C') THEN
184 debug('arp_etax_autoinv_util.insert_headers()-');
185 END IF;
186
187 EXCEPTION
188 WHEN NO_DATA_FOUND
189 THEN
190 debug('arp_etax_autoinv_util.insert_headers()- No transaction headers to process.');
191 RETURN;
192 WHEN OTHERS
193 THEN
194 debug('EXCEPTION: ARP_ETAX_AUTOINV_UTIL.insert_headers()-');
195 RAISE;
196
197 END insert_headers;
198
199 /* Private Procedure - Inserts lines (not tax) into ZX_TRANSACTION_LINES_GT
200
201 Dev Note:
202
203 1) Added outer joins to REC/gldist because the dists don't exist (yet) for
204 CMs. This was preventing insertion of CM lines for tax calculation.
205
206 2) Added support for tax-only type memo lines
207
208 3) Populated poo and poa party and location values
209
210 4) Populated bill_from_location_id (same as poa_location_id)
211
212
213 15-MAY-07 MRAYMOND 6033706 Added 6 additional etax columns
214 */
215 PROCEDURE insert_lines(
216 p_request_id IN NUMBER,
217 p_phase IN VARCHAR2) IS
218
219 l_return_status VARCHAR2(50);
220 l_so_org_id VARCHAR2(20);
221 l_lines_updated NUMBER;
222 BEGIN
223 IF PG_DEBUG in ('Y', 'C') THEN
224 debug('arp_etax_autoinv_util.insert_lines()+');
225 END IF;
226
227 l_so_org_id := oe_profile.value('SO_ORGANIZATION_ID',
228 arp_global.sysparam.org_id);
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,
254 uom_code,
255 fob_point,
256 ship_from_party_id, -- warehouse_id
257 ship_from_location_id, -- warehouse location
258 ship_to_party_id,
259 ship_to_party_site_id,
260 bill_to_party_id,
261 bill_to_party_site_id,
262 adjusted_doc_application_id,
263 adjusted_doc_entity_code,
264 adjusted_doc_event_class_code,
265 adjusted_doc_trx_id,
266 adjusted_doc_line_id,
267 adjusted_doc_trx_level_type,
268 adjusted_doc_number,
269 adjusted_doc_date,
270 source_application_id,
271 source_entity_code,
272 source_event_class_code,
273 source_trx_id,
274 source_line_id,
275 source_trx_level_type,
276 source_tax_line_id, -- 6470486
277 output_tax_classification_code,
278 trx_line_number,
279 historical_flag,
280 ctrl_hdr_tx_appl_flag, -- 'N'
281 trx_line_gl_date,
282 ship_to_location_id,
283 bill_to_location_id,
284 trx_line_currency_code,
285 trx_line_precision,
286 trx_line_mau,
287 ship_third_pty_acct_id,
288 ship_third_pty_acct_site_id,
289 ship_to_cust_acct_site_use_id,
290 poa_party_id,
291 poa_location_id,
292 poo_party_id,
293 poo_location_id,
294 cash_discount,
295 bill_from_location_id,
296 trx_business_category,
297 product_fisc_classification,
298 product_category,
299 product_type,
300 line_intended_use,
301 assessable_value,
302 user_defined_fisc_class,
303 account_ccid,
304 trx_line_description
305 )
306 SELECT
307 222,
308 ZTH.entity_code,
309 ZTH.event_class_code,
310 'RA_INTERFACE_LINES',
311 TL.customer_trx_line_id,
312 TL.customer_trx_id,
313 'LINE',
314 TL.customer_trx_line_id,
315 DECODE(ITT.type, 'CM', 'CREDIT_MEMO',
316 'DM', 'DEBIT_MEMO',
317 'INV','INVOICE'),
318 DECODE(NVL(ITL.taxable_flag, TL.taxable_flag), 'N', 'RECORD_WITH_NO_TAX',
319 DECODE(ML.line_type,'TAX','LINE_INFO_TAX_ONLY',
320 'CREATE')),
321 NVL(TL.sales_order_date,T.ship_date_actual),
322 DECODE(TL.inventory_item_id, NULL, 'MISC', 'ITEM'),
323 NULL,
324 DECODE(TL.amount_includes_tax_flag,'Y','A','N','N','S'),
325 TL.extended_amount,
326 TL.quantity_invoiced,
327 TL.unit_selling_price,
328 TL.tax_exempt_number,
329 TL.tax_exempt_reason_code,
330 TL.tax_exempt_flag,
331 NVL(TL.inventory_item_id, TL.memo_line_id), -- product_id
332 DECODE(TL.memo_line_id, NULL,
333 NVL(TL.warehouse_id,to_number(l_so_org_id)), NULL), -- product_org_id
334 TL.uom_code,
335 T.fob_point,
336 TL.warehouse_id, -- ship_from_party_id
337 HR.location_id, -- ship_from_location_id
338 STCA.party_id, -- ship to party
339 STPS.party_site_id, -- ship to site
340 ZTH.rounding_bill_to_party_id, -- bill to party
341 ZTH.rndg_bill_to_party_site_id, -- bill to site
342 DECODE(TL.previous_customer_trx_line_id, NULL, NULL, 222),
343 DECODE(TL.previous_customer_trx_line_id, NULL, NULL, 'TRANSACTIONS'),
344 /* bug6769106 vavenugo
345 modified the line below to pass the correct value for adjusted_doc_event_class_code based on the type of the document */
346 DECODE(TL.previous_customer_trx_line_id, NULL, NULL, DECODE(ITT.TYPE,'DM','DEBIT_MEMO','INVOICE')),
347 DECODE(TL.previous_customer_trx_line_id, NULL, NULL,
348 T.previous_customer_trx_id),
349 DECODE(TL.previous_customer_trx_line_id, NULL, NULL,
350 TL.previous_customer_trx_line_id),
351 DECODE(TL.previous_customer_trx_line_id, NULL, NULL, 'LINE'),
352 DECODE(T.previous_customer_trx_id, NULL, NULL, IT.trx_number),
353 DECODE(T.previous_customer_trx_id, NULL, NULL, IT.trx_date),
354 RIL.source_application_id,
355 RIL.source_entity_code,
356 RIL.source_event_class_code,
357 RIL.source_trx_id,
358 RIL.source_trx_line_id,
359 RIL.source_trx_line_type,
360 RIL.source_trx_detail_tax_line_id, -- 6470486
361 TL.tax_classification_code,
362 TL.line_number,
363 TL.historical_flag,
364 'N',
365 NVL(REC.gl_date, TRUNC(sysdate)),
366 STPSU.location_id,
367 BTPSU.location_id,
368 ZTH.trx_currency_code,
369 ZTH.precision,
370 ZTH.minimum_accountable_unit,
371 TL.ship_to_customer_id,
372 STPS.cust_acct_site_id, -- ship_third_pty_acct_site_id
373 STCSU.site_use_id,
374 ZTH.internal_organization_id, -- poa_party_id
375 ZTH.internal_org_location_id, -- poa_location_id
376 ZTH.internal_organization_id, -- poo_party_id (default value)
377 ZTH.internal_org_location_id, -- poo_location_id (default value)
378 TL.extended_amount * arp_etax_util.get_discount_rate(T.customer_trx_id),
379 ZTH.internal_org_location_id, -- bill_from_location_id
380 RIL.trx_business_category, -- 6033706
381 RIL.product_fisc_classification, -- 6033706
382 NVL(RIL.product_category,ML.tax_product_category),
383 RIL.product_type, -- 6033706
384 RIL.line_intended_use, -- 6033706
385 RIL.assessable_value, -- 6033706
386 RIL.user_defined_fisc_class, -- 6033706
387 ( SELECT Decode( p_phase,'CM',null,code_combination_id)
388 FROM ra_cust_trx_line_gl_dist gld
389 WHERE rownum = 1
390 AND gld.customer_trx_line_id = TL.customer_trx_line_id
391 AND gld.account_class = 'REV'
392 AND gld.request_id = tl.request_id) account_ccid,
393 TL.description
394 FROM
395 RA_CUSTOMER_TRX_LINES TL,
396 RA_CUSTOMER_TRX T,
397 RA_INTERFACE_LINES RIL,
398 ZX_TRX_HEADERS_GT ZTH,
399 HZ_CUST_ACCOUNTS STCA,
400 HZ_CUST_ACCT_SITES STPS,
401 HZ_CUST_SITE_USES STCSU,
402 RA_CUSTOMER_TRX IT,
403 RA_CUST_TRX_TYPES ITT,
404 RA_CUSTOMER_TRX_LINES ITL,
405 RA_CUST_TRX_LINE_GL_DIST REC,
406 HZ_PARTY_SITES STPSU,
407 HZ_PARTY_SITES BTPSU,
408 HR_ALL_ORGANIZATION_UNITS HR,
409 AR_MEMO_LINES_B ML
410 WHERE
411 TL.request_id = p_request_id
412 AND TL.line_type = 'LINE'
413 AND TL.customer_trx_id = T.customer_trx_id
414 AND TL.customer_trx_line_id = RIL.interface_line_id (+)
415 AND TL.customer_trx_id = ZTH.trx_id
416 AND NVL(T.previous_customer_trx_id, -99) =
417 DECODE(p_phase, 'INV', -99, T.previous_customer_trx_id)
418 AND TL.ship_to_customer_id =
419 STCA.cust_account_id (+)
420 AND TL.ship_to_site_use_id =
421 STCSU.site_use_id (+)
422 AND STCSU.cust_acct_site_id = STPS.cust_acct_site_id (+)
423 AND STPS.party_site_id = STPSU.party_site_id (+)
424 AND ZTH.rndg_bill_to_party_site_id = BTPSU.party_site_id
425 AND T.previous_customer_trx_id =
426 IT.customer_trx_id (+)
427 AND IT.cust_trx_type_id =
428 ITT.cust_trx_type_id (+)
429 AND TL.previous_customer_trx_line_id =
430 ITL.customer_trx_line_id (+)
431 AND REC.customer_trx_id (+) = T.customer_trx_id
432 AND REC.account_class (+) = 'REC'
433 AND REC.latest_rec_flag (+) = 'Y'
434 AND TL.warehouse_id = HR.organization_id (+)
435 AND TL.memo_line_id = ML.memo_line_id (+)
436 AND TL.org_id = ML.org_id (+);
437
438 g_lines_inserted := SQL%ROWCOUNT;
439
440 IF PG_DEBUG in ('Y', 'C') THEN
441 debug('lines inserted = ' || g_lines_inserted);
442 END IF;
443
444 /* 6874006 - removed salesrep/person logic from main insert
445 and shifted it to a separate UPDATE */
446 update zx_transaction_lines ZXL
447 set (poo_party_id, poo_location_id) =
448 (select SR_PER.organization_id, -- poo_party_id
449 SR_HRL.location_id -- poo_location_id
450 from RA_CUSTOMER_TRX TRX,
451 JTF_RS_SALESREPS SR,
452 PER_ALL_ASSIGNMENTS_F SR_PER,
453 HR_ORGANIZATION_UNITS SR_HRL
454 where TRX.customer_trx_id = ZXL.trx_id
455 and TRX.primary_salesrep_id IS NOT NULL
456 and TRX.primary_salesrep_id = SR.salesrep_id
457 and TRX.org_id = SR.org_id
458 and SR.person_id = SR_PER.person_id
459 and TRX.trx_date BETWEEN nvl(SR_PER.effective_start_date, TRX.trx_date)
460 AND nvl(SR_PER.effective_end_date, TRX.trx_date)
461 and NVL(SR_PER.primary_flag, 'Y') = 'Y'
462 and SR_PER.assignment_type = 'E'
463 and SR_PER.organization_id = SR_HRL.organization_id);
464
465 IF PG_DEBUG in ('Y', 'C') THEN
466 l_lines_updated := SQL%ROWCOUNT;
467 debug('lines update (poo columns) = ' || l_lines_updated);
468 END IF;
469
470 --BUG 6798210 combined the CCID update in above select
471
472 /* 4705358 - If LTE is enabled then call etax routine to set
473 specific columns for LTE processing */
474 /* 5924521 - Modified 'LATIN' to 'LTE' */
475 IF arp_global.sysparam.tax_method = 'LTE'
476 THEN
477 IF PG_DEBUG in ('Y', 'C')
478 THEN
479 debug(' calling zx_product_integration_pkg.copy_lte_gdfs...');
480 END IF;
481
482 zx_product_integration_pkg.copy_lte_gdfs(l_return_status);
483
484 END IF;
485
486 IF PG_DEBUG in ('Y', 'C') THEN
487 debug('arp_etax_autoinv_util.insert_lines()-');
488 END IF;
489
490 EXCEPTION
491 WHEN OTHERS
492 THEN
493 debug('EXCEPTION: ARP_ETAX_AUTOINV_UTIL.insert_lines()- ' ||
494 SQLERRM);
495 RAISE;
496 END insert_lines;
497
498 /* Inserts manual tax lines into IMPORT_GT table when
499 then are present in ra_interface_lines
500
501 DEV NOTE:
502
503 1) Jury is still out on how to link the manual tax lines to
504 the invoice lines. Harsh is following up with the
505 other etax people about why no ID column was added
506 to the LINK_GT table.
507
508 RESP: Harsh responded 3/28 in IM stating that we won't need to
509 use link table anymore. They are gonna provide a link column
510 in the manual tax lines.
511 */
512
513 PROCEDURE insert_tax_lines(
514 p_request_id IN NUMBER,
515 p_phase IN VARCHAR2) IS
516
517 BEGIN
518 IF PG_DEBUG in ('Y', 'C') THEN
519 debug('arp_etax_autoinv_util.insert_tax_lines()+');
520 END IF;
521
522 /* Note that this code does not directly use p_phase
523 However, it does join to ZX_TRX_HEADERS_GT which
524 would implicitly restrict the appearance of
525 INV rows in CM phase. */
526
527 INSERT INTO ZX_IMPORT_TAX_LINES_GT
528 (
529 internal_organization_id,
530 application_id,
531 entity_code,
532 event_class_code,
533 interface_entity_code,
534 interface_tax_line_id,
535 trx_id,
536 trx_line_id,
537 tax_regime_code,
538 tax,
539 tax_status_code,
540 tax_rate_code,
541 tax_rate,
542 tax_amt,
543 tax_jurisdiction_code,
544 tax_amt_included_flag,
545 tax_exception_id,
546 tax_exemption_id,
547 exempt_reason_code,
548 exempt_certificate_number,
549 tax_line_allocation_flag,
550 summary_tax_line_number -- 4698302
551 )
552 SELECT
553 ZTH.internal_organization_id,
554 222,
555 ZTH.entity_code,
556 ZTH.event_class_code,
557 'RA_INTERFACE_LINES',
558 RIL.interface_line_id, -- tax line
559 ZTH.trx_id,
560 RIL.link_to_line_id,
561 RIL.tax_regime_code,
562 RIL.tax,
563 RIL.tax_status_code,
564 RIL.tax_rate_code,
565 RIL.tax_rate,
566 RIL.amount,
567 RIL.tax_jurisdiction_code,
568 DECODE(RIL.amount_includes_tax_flag,'Y','Y','N'),
569 RIL.exception_id,
570 RIL.exemption_id,
571 RIL.tax_exempt_reason_code,
572 RIL.tax_exempt_number,
573 'N', -- no rows in zx_trx_tax_link_gt
574 RIL.interface_line_id -- 4698302
575 FROM
576 RA_INTERFACE_LINES RIL, -- tax lines
577 ZX_TRX_HEADERS_GT ZTH
578 WHERE
579 RIL.line_type = 'TAX'
580 AND RIL.request_id = p_request_id
581 AND RIL.customer_trx_id = ZTH.trx_id;
582
583 g_tax_lines_inserted := SQL%ROWCOUNT;
584
585 /* If we processed manual tax lines, we need to change
586 the line-level action to CREATE_WITH_TAX */
587 IF g_tax_lines_inserted > 0
588 THEN
589 /* set line level action */
590 UPDATE zx_transaction_lines_gt line
591 SET line_level_action = 'CREATE_WITH_TAX'
592 WHERE EXISTS
593 (SELECT 'manual tax line'
594 FROM ZX_IMPORT_TAX_LINES_GT tax
595 WHERE tax.trx_line_id = line.trx_line_id);
596 END IF;
597
598 IF PG_DEBUG in ('Y', 'C') THEN
599 debug('arp_etax_autoinv_util.insert_tax_lines()-');
600 END IF;
601
602 EXCEPTION
603 WHEN OTHERS
604 THEN
605 debug('EXCEPTION: ARP_ETAX_AUTOINV_UTIL.insert_tax_lines()-');
606 RAISE;
607
608 END insert_tax_lines;
609
610
611 /* Detects manual tax lines for both INV and CM and
612 records findings in global package variables */
613
614 /* 7329586 - almost don't need this anymore. The reason for it
615 was that the insert for manual tax lines would likely do a FTS
616 on zx_trx_header_gt (could take some time). But with the recent
617 bugs, I'm included to just obsolete this routine and always
618 do the insert. */
619 PROCEDURE detect_manual_tax(
620 p_request_id IN NUMBER) IS
621
622 l_ret_val NUMBER := 0;
623
624 BEGIN
625 IF PG_DEBUG in ('Y', 'C') THEN
626 debug('arp_etax_autoinv_util.detect_manual_tax()+');
627 END IF;
628
629 /* This routine detects presence of at least one
630 manual tax line for invoices and credits separately.
631 The idea behind this is that this routine will get
632 called twice (potentially) but only execute the
633 search for each phase on the first call.
634
635 Part of the criteria for determining if the line is
636 a credit is based on l.reference_line_id. This column
637 can be populated for credits and invs against commitments
638 but invoices against commitments will not have reference_line_id
639 populated on their tax lines. The second part of the criteria
640 restricts the test to only TAX lines. */
641
642 IF (g_tax_detected = FALSE)
643 THEN
644
645
646 BEGIN
647
648 SELECT 1
649 INTO l_ret_val
650 FROM ra_interface_lines
651 WHERE request_id = p_request_id
652 AND line_type = 'TAX'
653 AND reference_line_id is NULL
654 AND rownum = 1;
655
656 IF l_ret_val = 1
657 THEN
658 IF PG_DEBUG in ('Y', 'C') THEN
659 debug('Manual tax for invoices detected');
660 END IF;
661 g_inv_manual_tax := TRUE;
662 END IF;
663
664 EXCEPTION
665 WHEN NO_DATA_FOUND THEN
666 g_inv_manual_tax := FALSE;
667 END;
668
669
670 BEGIN
671 SELECT 2
672 INTO l_ret_val
673 FROM ra_interface_lines
674 WHERE request_id = p_request_id
675 AND line_type = 'TAX'
676 AND reference_line_id is not NULL
677 AND rownum = 1;
678
679 IF l_ret_val = 2
680 THEN
681 IF PG_DEBUG in ('Y', 'C') THEN
682 debug('Manual tax for credits detected');
683 END IF;
684 g_cm_manual_tax := TRUE;
685 END IF;
686
687 EXCEPTION
688 WHEN NO_DATA_FOUND THEN
689 g_cm_manual_tax := FALSE;
690 END;
691
692 /* prevent this from getting called again in this session */
693 g_tax_detected := TRUE;
694 ELSE
695 /* Tax already detected, just note the call and leave */
696 IF PG_DEBUG in ('Y', 'C') THEN
697 debug('tax detect not necesary this time...');
698 END IF;
699
700 END IF;
701
702
703 IF PG_DEBUG in ('Y', 'C') THEN
704 debug('arp_etax_autoinv_util.detect_manual_tax()-');
705 END IF;
706
707 END detect_manual_tax;
708
709 /*========================================================================
710 | PUBLIC PROCEDURE populate_ebt_gt
711 |
712 | DESCRIPTION
713 | Procedure inserts data into ebt GT tables for processing. At
714 | this time, the code is limited to use in autoinvoice and is
715 | designed to be called with either 'INV' or 'CM' as the mode
716 | which corresponds to the two autoinvoice phases.
717 |
718 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
719 |
720 | PARAMETERS
721 | p_request_id IN RAXTRX request_id
722 | p_phase IN Either 'INV' or 'CM' indicating RAXTRX phase
723 |
724 | KNOWN ISSUES
725 |
726 | NOTES
727 |
728 | MODIFICATION HISTORY
729 | Date Author Description of Changes
730 | 28-FEB-2005 MRAYMOND Created
731 |
732 *=======================================================================*/
733 PROCEDURE populate_ebt_gt(
734 p_request_id IN NUMBER,
735 p_phase IN VARCHAR2) IS
736
737 /*-----------------------------------------------------------------------+
738 | Cursor Declarations |
739 +-----------------------------------------------------------------------*/
740
741 /*-----------------------------------------------------------------------+
742 | User defined exceptions |
743 +-----------------------------------------------------------------------*/
744
745 /*-----------------------------------------------------------------------+
746 | Local Variable Declarations and initializations |
747 +-----------------------------------------------------------------------*/
748
749 BEGIN
750
751 IF PG_DEBUG in ('Y', 'C') THEN
752 debug('arp_etax_util.populate_ebt_gt()+');
753 debug(' p_request_id :' || p_request_id);
754 debug(' p_phase :' || p_phase);
755 END IF;
756
757 /* 7329586 - clear ebt_gt tables if this is second
758 or subsequent call within this session */
759 IF g_ebt_gt_populated
760 THEN
761 arp_etax_util.clear_ebt_gt;
762 g_ebt_gt_populated := FALSE;
763 g_tax_detected := FALSE ; -- Bug6731444 retest for manual tax
764 END IF;
765
766 /* Insert lines into ZX_TRX_HEADERS_GT */
767 insert_headers(p_request_id, p_phase);
768
769 /* Insert lines into ZX_TRANSACTION_LINES_GT */
770 insert_lines(p_request_id, p_phase);
771
772 /* Detect Manual Tax Lines and stores (caches) results
773 in global variables. This will not do anything on subsequent
774 calls.*/
775 detect_manual_tax(p_request_id);
776
777 /* If manual tax lines exist, populate ZX_IMPORT_TAX_LINES_GT
778 and ZX_TRX_TAX_LINK_GT. Also set line-level action
779 on parent lines to CREATE_WITH_TAX accordingly */
780 IF (p_phase = 'INV' and g_inv_manual_tax) OR
781 (p_phase = 'CM' and g_cm_manual_tax)
782 THEN
783 IF PG_DEBUG in ('Y', 'C')
784 THEN
785 debug('Processing manual tax lines...');
786 END IF;
787
788 insert_tax_lines(p_request_id, p_phase);
789
790 ELSE
791 IF PG_DEBUG in ('Y', 'C')
792 THEN
793 debug('No manual tax lines');
794 END IF;
795 END IF;
796
797 /* 7329586 - set global so next call in this session
798 will clear GT tables */
799 IF NVL(g_headers_inserted,0) +
800 NVL(g_lines_inserted,0) +
801 NVL(g_tax_lines_inserted,0) > 0
802 THEN
803 g_ebt_gt_populated := TRUE;
804 END IF;
805
806 IF PG_DEBUG in ('Y', 'C') THEN
807 debug('PHASE [' || p_phase || ']');
808 debug('headers inserted : ' || g_headers_inserted);
809 debug('lines inserted : ' || g_lines_inserted);
810 debug('tax lines inserted : ' || g_tax_lines_inserted);
811 debug('arp_etax_util.populate_ebt_gt()-');
812 END IF;
813
814 EXCEPTION
815 WHEN OTHERS THEN
816 IF PG_DEBUG in ('Y', 'C') THEN
817 debug('EXCEPTION: ARP_ETAX_AUTOINV_UTIL.populate_ebt_gt()');
818 END IF;
819 RAISE;
820
821 END populate_ebt_gt;
822
823 /* Procedure to retrieve TAX lines from ZX and populate
824 RA_CUSTOMER_TRX_LINES accordingly
825
826 06-JAN-2006 M Raymond 4740826 - Added code to retrieve
827 tax classifications back
828 from etax and stamp them
829 on line records.
830
831 */
832
833
834 PROCEDURE build_ar_tax_lines(
835 p_request_id IN NUMBER,
836 p_phase IN VARCHAR2) IS
837
838 l_rows NUMBER;
839
840 BEGIN
841 IF PG_DEBUG in ('Y', 'C') THEN
842 debug('arp_etax_util.build_ar_tax_lines()+');
843 END IF;
844
845 /* Dev Notes:
846
847 1) We set autotax flag as inverse of manaully_entered_flag
848 coming from eTax
849
850 RESP: Ok. That should be fine
851
852 2) Does eTax generate autotax lines when manual ones for
853 same inv/cm line are present? If so, can we still rely
854 upon manually_entered_flag to determine which is which?
855
856 RESP: That is soft-configurable. So we can use
857 manually_entered_flag to determine what value
858 of autotax should be.
859
860 3) Previous_customer_trx_line_id.. we need a way to set this
861 for each CM tax line. I communicated this to Santosh on
862 3-MAR-05 along with my suggestion which was to have them add
863 a 45th column for link_to_trx_line_id or perhaps applied_to_trx_line_id
864 The idea for this is that they pass me the eTax line_id of the target
865 tax line and I use that to fetch the customer_trx_line_id of the
866 corresponding line in ra_customer_trx_lines.
867
868 However, I'll probably need an index based on tax_line_id in
869 RA_CUSTOMER_TRX_LINES table to make that search fast enough
870
871 RESP: They are now allowing me to pass the interface_line_id
872 of the invoice lines and tax lines in -- and they pass them back
873 out. This should be enough to let me get what I need from
874 ra_interface_lines
875
876 4) changed zx_detail_tax_lines_gt to zx_lines at request of santosh
877 during IM conv on 25-MAY. Apparently, zx_detail_tax_lines_gt
878 is only for quotes.
879
880 5) Identified a loose end where we were not populating prev_cust_trx_line_id
881 on tax lines. corrected both here and ARP_ETAX_UTIL
882
883 6) ORA-28115 raised because we were not setting org_id
884 End Dev Notes */
885
886
887 /* Insert rows into RA_CUSTOMER_TRX_LINES for the
888 new TAX lines */
889 INSERT INTO RA_CUSTOMER_TRX_LINES
890 (
891 CUSTOMER_TRX_LINE_ID,
892 LAST_UPDATE_DATE,
893 LAST_UPDATED_BY,
894 CREATION_DATE,
895 CREATED_BY,
896 LAST_UPDATE_LOGIN,
897 PROGRAM_ID,
898 PROGRAM_APPLICATION_ID,
899 CUSTOMER_TRX_ID,
900 LINE_NUMBER,
901 SET_OF_BOOKS_ID,
902 LINE_TYPE, -- TAX
903 LINK_TO_CUST_TRX_LINE_ID, -- parent line
904 DEFAULT_USSGL_TRANSACTION_CODE,
905 REQUEST_ID,
906 EXTENDED_AMOUNT,
907 TAX_RATE,
908 AUTOTAX,
909 AMOUNT_INCLUDES_TAX_FLAG,
910 TAXABLE_AMOUNT,
911 VAT_TAX_ID,
912 TAX_LINE_ID, -- ID in ZX_ table
913 PREVIOUS_CUSTOMER_TRX_LINE_ID,
914 INTERFACE_LINE_CONTEXT,
915 INTERFACE_LINE_ATTRIBUTE1,
916 INTERFACE_LINE_ATTRIBUTE2,
917 INTERFACE_LINE_ATTRIBUTE3,
918 INTERFACE_LINE_ATTRIBUTE4,
919 INTERFACE_LINE_ATTRIBUTE5,
920 INTERFACE_LINE_ATTRIBUTE6,
921 INTERFACE_LINE_ATTRIBUTE7,
922 INTERFACE_LINE_ATTRIBUTE8,
923 INTERFACE_LINE_ATTRIBUTE9,
924 INTERFACE_LINE_ATTRIBUTE10,
925 INTERFACE_LINE_ATTRIBUTE11,
926 INTERFACE_LINE_ATTRIBUTE12,
927 INTERFACE_LINE_ATTRIBUTE13,
928 INTERFACE_LINE_ATTRIBUTE14,
929 INTERFACE_LINE_ATTRIBUTE15,
930 ORG_ID
931 )
932 SELECT
933 NVL(mtax.interface_line_id,ra_customer_trx_lines_s.nextval),
934 sysdate,
935 arp_standard.profile.user_id,
936 sysdate,
937 arp_standard.profile.user_id,
938 arp_standard.profile.user_id,
939 arp_standard.profile.program_id,
940 arp_standard.application_id,
941 zxt.trx_id,
942 zxt.tax_line_number,
943 arp_standard.sysparm.set_of_books_id,
944 'TAX',
945 zxt.trx_line_id,
946 plin.default_ussgl_transaction_code,
947 p_request_id,
948 zxt.tax_amt,
949 zxt.tax_rate,
950 DECODE(NVL(zxt.manually_entered_flag, 'N'), 'Y', NULL, 'Y'),
951 zxt.tax_amt_included_flag, -- either Y or N from zx_lines
952 zxt.taxable_amt,
953 zxt.tax_rate_id,
954 zxt.tax_line_id,
955 inv_lin.customer_trx_line_id,
956 mtax.interface_line_context,
957 mtax.interface_line_attribute1,
958 mtax.interface_line_attribute2,
959 mtax.interface_line_attribute3,
960 mtax.interface_line_attribute4,
961 mtax.interface_line_attribute5,
962 mtax.interface_line_attribute6,
963 mtax.interface_line_attribute7,
964 mtax.interface_line_attribute8,
965 mtax.interface_line_attribute9,
966 mtax.interface_line_attribute10,
967 mtax.interface_line_attribute11,
968 mtax.interface_line_attribute12,
969 mtax.interface_line_attribute13,
970 mtax.interface_line_attribute14,
971 mtax.interface_line_attribute15,
972 plin.org_id
973 FROM ZX_LINES zxt,
974 RA_CUSTOMER_TRX_LINES plin,
975 RA_INTERFACE_LINES mtax,
976 ZX_LINES inv_zxt,
977 RA_CUSTOMER_TRX_LINES inv_lin
978 WHERE plin.request_id = p_request_id
979 AND zxt.application_id = 222
980 AND zxt.entity_code = 'TRANSACTIONS'
981 AND zxt.event_class_code in ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')
982 AND zxt.trx_id = plin.customer_trx_id
983 AND zxt.trx_level_type = 'LINE'
984 AND zxt.trx_line_id = plin.customer_trx_line_id
985 AND zxt.interface_tax_line_id = mtax.interface_line_id (+)
986 AND zxt.adjusted_doc_tax_line_id = inv_zxt.tax_line_id (+)
987 AND inv_zxt.trx_line_id = inv_lin.link_to_cust_trx_line_id (+)
988 AND inv_zxt.tax_line_id = inv_lin.tax_line_id (+)
989 AND decode(p_phase, 'CM', plin.previous_customer_trx_line_id,-99) =
990 nvl(plin.previous_customer_trx_line_id, -99);
991
992 l_rows := SQL%ROWCOUNT;
993
994 IF l_rows > 0
995 THEN
996 /* Stamp transaction lines with tax_classification
997 from ZX_LINES_DET_FACTORS */
998 arp_etax_util.set_default_tax_classification(p_request_id, p_phase);
999
1000 /* adjust for inclusive tax */
1001 arp_etax_util.adjust_for_inclusive_tax(null, p_request_id, p_phase);
1002 END IF;
1003
1004 /* Set line_recoverable and tax_recoverable here */
1005 arp_etax_util.set_recoverable(null, p_request_id, p_phase);
1006
1007 IF PG_DEBUG in ('Y', 'C') THEN
1008 debug(' Number of tax lines retrieved = ' || l_rows);
1009 debug('arp_etax_util.build_ar_tax_lines()-');
1010 END IF;
1011 END build_ar_tax_lines;
1012
1013 /* Procedure to extract error/validation messages from ZX
1014 and insert them into RA_INTERFACE_ERRORS */
1015 PROCEDURE retrieve_tax_validation_errors(p_error_count IN OUT NOCOPY NUMBER) IS
1016
1017 l_trx_errors NUMBER := 0;
1018 l_trx_line_errors NUMBER := 0;
1019 l_tax_line_errors NUMBER := 0;
1020
1021 BEGIN
1022
1023 IF PG_DEBUG in ('Y', 'C') THEN
1024 debug('arp_etax_util.retrieve_tax_validation_errors()+');
1025 END IF;
1026
1027 /* Dev Notes:
1028
1029 */
1030
1031 /* Line level errors */
1032 INSERT INTO RA_INTERFACE_ERRORS
1033 (
1034 interface_line_id,
1035 message_text,
1036 org_id
1037 )
1038 SELECT
1039 zxe.trx_line_id,
1040 zxe.message_text,
1041 il.org_id
1042 FROM ZX_VALIDATION_ERRORS_GT zxe,
1043 ra_interface_lines il
1044 WHERE zxe.interface_line_id = il.interface_line_id
1045 AND il.line_type <> 'TAX'
1046 AND zxe.summary_tax_line_number is NULL;
1047
1048 l_trx_line_errors := SQL%ROWCOUNT;
1049
1050 /* Manual Tax Line errors */
1051 INSERT INTO RA_INTERFACE_ERRORS
1052 (
1053 interface_line_id,
1054 message_text,
1055 invalid_value,
1056 org_id
1057 )
1058 SELECT
1059 zxe.summary_tax_line_number,
1060 zxe.message_text,
1061 DECODE(zxe.message_name,
1062 'ZX_DEFAULT_RATE_CODE_NOT_EXIST', 'tax_regime = ' || it.tax_regime_code ||
1063 ' tax = ' || it.tax,
1064 'ZX_DEFAULT_JUR_CODE_NOT_EXIST', 'tax_regime = ' || it.tax_regime_code ||
1065 ' tax = ' || it.tax,
1066 'ZX_JUR_CODE_NOT_EFFECTIVE', it.tax_jurisdiction_code,
1067 'ZX_JUR_CODE_NOT_EXIST', it.tax_jurisdiction_code,
1068 'ZX_TAX_NOT_EXIST', it.tax,
1069 'ZX_TAX_NOT_LIVE', it.tax,
1070 'ZX_TAX_RECOV_OR_OFFSET', it.tax,
1071 'ZX_TAX_STATUS_NOT_EFFECTIVE', it.tax_status_code,
1072 'ZX_TAX_RATE_NOT_EXIST', it.tax_rate_code,
1073 'ZX_TAX_RATE_NOT_EFFECTIVE', it.tax_rate_code,
1074 'ZX_TAX_RATE_NOT_ACTIVE', it.tax_rate_code,
1075 NULL),
1076 it.org_id
1077 FROM ZX_VALIDATION_ERRORS_GT zxe,
1078 ra_interface_lines it
1079 WHERE zxe.trx_id = it.customer_trx_id
1080 AND zxe.interface_tax_line_id = it.interface_line_id
1081 AND it.line_type = 'TAX';
1082
1083 l_tax_line_errors := SQL%ROWCOUNT;
1084
1085 INSERT INTO RA_INTERFACE_ERRORS
1086 (
1087 interface_line_id,
1088 message_text,
1089 invalid_value,
1090 org_id
1091 )
1092 SELECT
1093 it.interface_line_id,
1094 zxe.message_text,
1095 NULL,
1096 it.org_id
1097 FROM ZX_ERRORS_GT zxe,
1098 ra_interface_lines it
1099 WHERE zxe.trx_id = it.customer_trx_id;
1100
1101 l_tax_line_errors := l_tax_line_errors + SQL%ROWCOUNT;
1102 debug('rows inserted through zx_errors_gt '||sql%rowcount);
1103
1104 p_error_count := l_trx_errors + l_trx_line_errors + l_tax_line_errors;
1105
1106 IF PG_DEBUG in ('Y', 'C') THEN
1107 debug('Validation errors: ' || l_trx_line_errors ||
1108 '+' || l_tax_line_errors ||
1109 '=' || p_error_count);
1110 debug('arp_etax_util.retrieve_tax_validation_errors()-');
1111 END IF;
1112
1113 END retrieve_tax_validation_errors;
1114
1115 /* External public call designed for autoinvoice. This will
1116 populate the ZX tables, validate the data, calculate the tax,
1117 and insert resulting tax lines back into AR */
1118 PROCEDURE calculate_tax(p_request_id IN NUMBER,
1119 p_phase IN VARCHAR2,
1120 p_error_count IN OUT NOCOPY NUMBER,
1121 p_return_status OUT NOCOPY NUMBER) IS
1122
1123 l_return_status NUMBER;
1124
1125 BEGIN
1126 IF PG_DEBUG in ('Y', 'C')
1127 THEN
1128 debug('arp_etax_autoinv_util.calculate_tax()+');
1129 debug('request_id = ' || p_request_id);
1130 debug('phase = ' || p_phase);
1131 END IF;
1132
1133 /* Insert data into ebt tables */
1134 populate_ebt_gt(p_request_id, p_phase);
1135
1136 /* Call validate_and_default_tax_attr */
1137 arp_etax_util.validate_tax_int(p_return_status => l_return_status,
1138 p_called_from_AI => 'Y');
1139 p_return_status := l_return_status;
1140
1141 /* Only call these routines if the prior call returns successful.
1142 Otherwise, a rollback will occur once the call returns
1143 to raaebt (or invoice API) */
1144 IF l_return_status = 0
1145 THEN
1146 /* Call import_document_with_tax */
1147 arp_etax_util.calculate_tax_int(p_return_status => l_return_status,
1148 p_called_from_AI => 'Y');
1149 p_return_status := l_return_status;
1150 END IF;
1151
1152 /* Only call these routines if the prior call returns successful.
1153 Otherwise, a rollback will occur once the call returns
1154 to raaebt (or invoice API) */
1155 IF l_return_status = 0
1156 THEN
1157 /* retrieve validation errors and populate RA_INTERFACE_ERRORS */
1158 retrieve_tax_validation_errors(p_error_count);
1159
1160 /* Pull resulting tax lines and populate RA_CUSTOMER_TRX_LINES */
1161 build_ar_tax_lines(p_request_id, p_phase);
1162 END IF;
1163
1164 IF PG_DEBUG in ('Y', 'C')
1165 THEN
1166 debug('arp_etax_autoinv_util.calculate_tax()-');
1167 END IF;
1168 END calculate_tax;
1169
1170 /*========================================================================
1171 | INITIALIZATION SECTION
1172 |
1173 | DESCRIPTION
1174 | Initialized global variables for controlling program flow
1175 |
1176 | KNOWN ISSUES
1177 |
1178 | NOTES
1179 |
1180 | MODIFICATION HISTORY
1181 | Date Author Description of Changes
1182 | 28-FEB-2005 MRAYMOND Created
1183 *=======================================================================*/
1184
1185 BEGIN
1186 NULL;
1187 END ARP_ETAX_AUTOINV_UTIL;
[Home] [Help]