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