[Home] [Help]
PACKAGE BODY: APPS.ZX_ON_FLY_TRX_UPGRADE_AR_PKG
Source
1 PACKAGE BODY ZX_ON_FLY_TRX_UPGRADE_AR_PKG AS
2 /* $Header: zxmigtrxflyarb.pls 120.16.12010000.1 2008/07/28 13:34:57 appldev ship $ */
3
4 g_current_runtime_level NUMBER;
5 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
6 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
7 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
8 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
9
10 -------------------------------------------------------------------------------
11 -- PUBLIC PROCEDURE
12 -- upgrade_trx_on_fly_ar
13 --
14 -- DESCRIPTION
15 -- on the fly migration of one transaction for AR
16 --
17 -------------------------------------------------------------------------------
18
19 PROCEDURE upgrade_trx_on_fly_ar(
20 p_upg_trx_info_rec IN ZX_ON_FLY_TRX_UPGRADE_PKG.zx_upg_trx_info_rec_type,
21 x_return_status OUT NOCOPY VARCHAR2
22 ) AS
23 l_multi_org_flag VARCHAR2(1);
24 l_org_id NUMBER;
25 l_inv_installed VARCHAR2(1);
26 l_inv_flag VARCHAR2(1);
27 l_industry VARCHAR2(10);
28 l_fnd_return BOOLEAN;
29
30 BEGIN
31 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
32
33 IF (g_level_procedure >= g_current_runtime_level ) THEN
34 FND_LOG.STRING(g_level_procedure,
35 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly_ar.BEGIN',
36 'ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly_ar(+)');
37 END IF;
38 x_return_status := FND_API.G_RET_STS_SUCCESS;
39
40 SELECT NVL(multi_org_flag, 'N') INTO l_multi_org_flag FROM FND_PRODUCT_GROUPS;
41 -- for single org environment, get value of org_id from profile
42 IF l_multi_org_flag = 'N' THEN
43 FND_PROFILE.GET('ORG_ID',l_org_id);
44 IF l_org_id is NULL THEN
45 l_org_id := -99;
46 END IF;
47 END IF;
48
49 l_fnd_return := FND_INSTALLATION.GET(401,401, l_inv_flag, l_industry);
50
51 if (l_inv_flag = 'I') then
52 l_inv_installed := 'Y';
53 else
54 l_inv_installed := 'N';
55 end if;
56
57
58 INSERT ALL
59 WHEN trx_line_type IN ('LINE' ,'CB') THEN
60 INTO ZX_LINES_DET_FACTORS(
61 INTERNAL_ORGANIZATION_ID
62 ,APPLICATION_ID
63 ,ENTITY_CODE
64 ,EVENT_CLASS_CODE
65 ,EVENT_CLASS_MAPPING_ID
66 ,EVENT_TYPE_CODE
67 ,DOC_EVENT_STATUS
68 ,LINE_LEVEL_ACTION
69 ,TRX_ID
70 ,TRX_LINE_ID
71 ,TRX_LEVEL_TYPE
72 ,TRX_DATE
73 --,TRX_DOC_REVISION
74 ,LEDGER_ID
75 ,TRX_CURRENCY_CODE
76 ,CURRENCY_CONVERSION_DATE
77 ,CURRENCY_CONVERSION_RATE
78 ,CURRENCY_CONVERSION_TYPE
79 ,MINIMUM_ACCOUNTABLE_UNIT
80 ,PRECISION
81 ,LEGAL_ENTITY_ID
82 --,ESTABLISHMENT_ID
83 ,RECEIVABLES_TRX_TYPE_ID
84 ,DEFAULT_TAXATION_COUNTRY
85 ,TRX_NUMBER
86 ,TRX_LINE_NUMBER
87 ,TRX_LINE_DESCRIPTION
88 --,TRX_DESCRIPTION
89 --,TRX_COMMUNICATED_DATE
90 ,BATCH_SOURCE_ID
91 ,BATCH_SOURCE_NAME
92 ,DOC_SEQ_ID
93 ,DOC_SEQ_NAME
94 ,DOC_SEQ_VALUE
95 ,TRX_DUE_DATE
96 ,TRX_TYPE_DESCRIPTION
97 ,DOCUMENT_SUB_TYPE
98 --,SUPPLIER_TAX_INVOICE_NUMBER
99 --,SUPPLIER_TAX_INVOICE_DATE
100 --,SUPPLIER_EXCHANGE_RATE
101 ,TAX_INVOICE_DATE
102 ,TAX_INVOICE_NUMBER
103 ,FIRST_PTY_ORG_ID
104 ,TAX_EVENT_CLASS_CODE
105 ,TAX_EVENT_TYPE_CODE
106 --,LINE_INTENDED_USE
107 ,TRX_LINE_TYPE
108 --,TRX_SHIPPING_DATE
109 --,TRX_RECEIPT_DATE
110 --,TRX_SIC_CODE
111 ,FOB_POINT
112 ,TRX_WAYBILL_NUMBER
113 ,PRODUCT_ID
114 ,PRODUCT_FISC_CLASSIFICATION
115 ,PRODUCT_ORG_ID
116 ,UOM_CODE
117 --,PRODUCT_TYPE
118 --,PRODUCT_CODE
119 ,PRODUCT_CATEGORY
120 ,PRODUCT_DESCRIPTION
121 ,USER_DEFINED_FISC_CLASS
122 ,LINE_AMT
123 ,TRX_LINE_QUANTITY
124 --,CASH_DISCOUNT
125 --,VOLUME_DISCOUNT
126 --,TRADING_DISCOUNT
127 --,TRANSFER_CHARGE
128 --,TRANSPORTATION_CHARGE
129 --,INSURANCE_CHARGE
130 --,OTHER_CHARGE
131 --,ASSESSABLE_VALUE
132 --,ASSET_FLAG
133 --,ASSET_NUMBER
134 ,ASSET_ACCUM_DEPRECIATION
135 --,ASSET_TYPE
136 ,ASSET_COST
137 ,RELATED_DOC_APPLICATION_ID
138 --,RELATED_DOC_ENTITY_CODE
139 --,RELATED_DOC_EVENT_CLASS_CODE
140 ,RELATED_DOC_TRX_ID
141 --,RELATED_DOC_NUMBER
142 --,RELATED_DOC_DATE
143 ,ADJUSTED_DOC_APPLICATION_ID
144 ,ADJUSTED_DOC_ENTITY_CODE
145 ,ADJUSTED_DOC_EVENT_CLASS_CODE --- Bug6024643
146 ,ADJUSTED_DOC_TRX_ID
147 ,ADJUSTED_DOC_LINE_ID
148 ,ADJUSTED_DOC_NUMBER
149 ,ADJUSTED_DOC_DATE
150 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
151 --,REF_DOC_APPLICATION_ID
152 --,REF_DOC_ENTITY_CODE
153 --,REF_DOC_EVENT_CLASS_CODE
154 --,REF_DOC_TRX_ID
155 --,REF_DOC_LINE_ID
156 --,REF_DOC_LINE_QUANTITY
157 --,REF_DOC_TRX_LEVEL_TYPE
158 ,TRX_BUSINESS_CATEGORY
159 ,EXEMPT_CERTIFICATE_NUMBER
160 --,EXEMPT_REASON
161 ,EXEMPTION_CONTROL_FLAG
162 ,EXEMPT_REASON_CODE
163 ,HISTORICAL_FLAG
164 ,TRX_LINE_GL_DATE
165 ,LINE_AMT_INCLUDES_TAX_FLAG
166 --,ACCOUNT_CCID
167 --,ACCOUNT_STRING
168 --,SHIP_TO_LOCATION_ID
169 --,SHIP_FROM_LOCATION_ID
170 --,POA_LOCATION_ID
171 --,POO_LOCATION_ID
172 --,BILL_TO_LOCATION_ID
173 --,BILL_FROM_LOCATION_ID
174 --,PAYING_LOCATION_ID
175 --,OWN_HQ_LOCATION_ID
176 --,TRADING_HQ_LOCATION_ID
177 --,POC_LOCATION_ID
178 --,POI_LOCATION_ID
179 --,POD_LOCATION_ID
180 --,TITLE_TRANSFER_LOCATION_ID
181 ,CTRL_HDR_TX_APPL_FLAG
182 --,CTRL_TOTAL_LINE_TX_AMT
183 --,CTRL_TOTAL_HDR_TX_AMT
184 ,LINE_CLASS
185 ,TRX_LINE_DATE
186 --,INPUT_TAX_CLASSIFICATION_CODE
187 ,OUTPUT_TAX_CLASSIFICATION_CODE
188 --,INTERNAL_ORG_LOCATION_ID
189 --,PORT_OF_ENTRY_CODE
190 ,TAX_REPORTING_FLAG
191 ,TAX_AMT_INCLUDED_FLAG
192 ,COMPOUNDING_TAX_FLAG
193 --,EVENT_ID
194 ,THRESHOLD_INDICATOR_FLAG
195 --,PROVNL_TAX_DETERMINATION_DATE
196 ,UNIT_PRICE
197 ,SHIP_TO_CUST_ACCT_SITE_USE_ID
198 ,BILL_TO_CUST_ACCT_SITE_USE_ID
199 ,TRX_BATCH_ID
200 --,START_EXPENSE_DATE
201 --,SOURCE_APPLICATION_ID
202 --,SOURCE_ENTITY_CODE
203 --,SOURCE_EVENT_CLASS_CODE
204 --,SOURCE_TRX_ID
205 --,SOURCE_LINE_ID
206 --,SOURCE_TRX_LEVEL_TYPE
207 ,RECORD_TYPE_CODE
208 ,INCLUSIVE_TAX_OVERRIDE_FLAG
209 ,TAX_PROCESSING_COMPLETED_FLAG
210 ,OBJECT_VERSION_NUMBER
211 ,APPLICATION_DOC_STATUS
212 ,USER_UPD_DET_FACTORS_FLAG
213 --,SOURCE_TAX_LINE_ID
214 --,REVERSED_APPLN_ID
215 --,REVERSED_ENTITY_CODE
216 --,REVERSED_EVNT_CLS_CODE
217 --,REVERSED_TRX_ID
218 --,REVERSED_TRX_LEVEL_TYPE
219 --,REVERSED_TRX_LINE_ID
220 --,TAX_CALCULATION_DONE_FLAG
221 ,PARTNER_MIGRATED_FLAG
222 ,SHIP_THIRD_PTY_ACCT_SITE_ID
223 ,BILL_THIRD_PTY_ACCT_SITE_ID
224 ,SHIP_THIRD_PTY_ACCT_ID
225 ,BILL_THIRD_PTY_ACCT_ID
226 --,INTERFACE_ENTITY_CODE
227 --,INTERFACE_LINE_ID
228 --,HISTORICAL_TAX_CODE_ID
229 --,ICX_SESSION_ID
230 --,TRX_LINE_CURRENCY_CODE
231 --,TRX_LINE_CURRENCY_CONV_RATE
232 --,TRX_LINE_CURRENCY_CONV_DATE
233 --,TRX_LINE_PRECISION
234 --,TRX_LINE_MAU
235 --,TRX_LINE_CURRENCY_CONV_TYPE
236 ,CREATION_DATE
237 ,CREATED_BY
238 ,LAST_UPDATE_DATE
239 ,LAST_UPDATED_BY
240 ,LAST_UPDATE_LOGIN
241 )
242 VALUES (
243 INTERNAL_ORGANIZATION_ID
244 ,APPLICATION_ID
245 ,ENTITY_CODE
246 ,EVENT_CLASS_CODE
247 ,EVENT_CLASS_MAPPING_ID
248 ,EVENT_TYPE_CODE
249 ,DOC_EVENT_STATUS
250 ,LINE_LEVEL_ACTION
251 ,TRX_ID
252 ,TRX_LINE_ID
253 ,TRX_LEVEL_TYPE
254 ,TRX_DATE
255 --,TRX_DOC_REVISION
256 ,LEDGER_ID
257 ,TRX_CURRENCY_CODE
258 ,CURRENCY_CONVERSION_DATE
259 ,CURRENCY_CONVERSION_RATE
260 ,CURRENCY_CONVERSION_TYPE
261 ,MINIMUM_ACCOUNTABLE_UNIT
262 ,PRECISION
263 ,LEGAL_ENTITY_ID
264 --,ESTABLISHMENT_ID
265 ,RECEIVABLES_TRX_TYPE_ID
266 ,DEFAULT_TAXATION_COUNTRY
267 ,TRX_NUMBER
268 ,TRX_LINE_NUMBER
269 ,TRX_LINE_DESCRIPTION
270 --,TRX_DESCRIPTION
271 --,TRX_COMMUNICATED_DATE
272 ,BATCH_SOURCE_ID
273 ,BATCH_SOURCE_NAME
274 ,DOC_SEQ_ID
275 ,DOC_SEQ_NAME
276 ,DOC_SEQ_VALUE
277 ,TRX_DUE_DATE
278 ,TRX_TYPE_DESCRIPTION
279 ,DOCUMENT_SUB_TYPE
280 --,SUPPLIER_TAX_INVOICE_NUMBER
281 --,SUPPLIER_TAX_INVOICE_DATE
282 --,SUPPLIER_EXCHANGE_RATE
283 ,TAX_INVOICE_DATE
284 ,TAX_INVOICE_NUMBER
285 ,FIRST_PTY_ORG_ID
286 ,TAX_EVENT_CLASS_CODE
287 ,TAX_EVENT_TYPE_CODE
288 --,LINE_INTENDED_USE
289 ,TRX_LINE_TYPE
290 --,TRX_SHIPPING_DATE
291 --,TRX_RECEIPT_DATE
292 --,TRX_SIC_CODE
293 ,FOB_POINT
294 ,TRX_WAYBILL_NUMBER
295 ,PRODUCT_ID
296 ,PRODUCT_FISC_CLASSIFICATION
297 ,PRODUCT_ORG_ID
298 ,UOM_CODE
299 --,PRODUCT_TYPE
300 --,PRODUCT_CODE
301 ,PRODUCT_CATEGORY
302 ,PRODUCT_DESCRIPTION
303 ,USER_DEFINED_FISC_CLASS
304 ,LINE_AMT
305 ,TRX_LINE_QUANTITY
306 --,CASH_DISCOUNT
307 --,VOLUME_DISCOUNT
308 --,TRADING_DISCOUNT
309 --,TRANSFER_CHARGE
310 --,TRANSPORTATION_CHARGE
311 --,INSURANCE_CHARGE
312 --,OTHER_CHARGE
313 --,ASSESSABLE_VALUE
314 --,ASSET_FLAG
315 --,ASSET_NUMBER
316 ,ASSET_ACCUM_DEPRECIATION
317 --,ASSET_TYPE
318 ,ASSET_COST
319 ,RELATED_DOC_APPLICATION_ID
320 --,RELATED_DOC_ENTITY_CODE
321 --,RELATED_DOC_EVENT_CLASS_CODE
322 ,RELATED_DOC_TRX_ID
323 --,RELATED_DOC_NUMBER
324 --,RELATED_DOC_DATE
325 ,ADJUSTED_DOC_APPLICATION_ID
326 ,ADJUSTED_DOC_ENTITY_CODE
327 ,ADJUSTED_DOC_EVENT_CLASS_CODE --- Bug6024643
328 ,ADJUSTED_DOC_TRX_ID
329 ,ADJUSTED_DOC_LINE_ID
330 ,ADJUSTED_DOC_NUMBER
331 ,ADJUSTED_DOC_DATE
332 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
333 --,REF_DOC_APPLICATION_ID
334 --,REF_DOC_ENTITY_CODE
335 --,REF_DOC_EVENT_CLASS_CODE
336 --,REF_DOC_TRX_ID
337 --,REF_DOC_LINE_ID
338 --,REF_DOC_LINE_QUANTITY
339 --,REF_DOC_TRX_LEVEL_TYPE
340 ,TRX_BUSINESS_CATEGORY
341 ,EXEMPT_CERTIFICATE_NUMBER
342 --,EXEMPT_REASON
343 ,EXEMPTION_CONTROL_FLAG
344 ,EXEMPT_REASON_CODE
345 ,'Y' --HISTORICAL_FLAG
346 ,TRX_LINE_GL_DATE
347 ,'N' --LINE_AMT_INCLUDES_TAX_FLAG
348 --,ACCOUNT_CCID
349 --,ACCOUNT_STRING
350 --,SHIP_TO_LOCATION_ID
351 --,SHIP_FROM_LOCATION_ID
352 --,POA_LOCATION_ID
353 --,POO_LOCATION_ID
354 --,BILL_TO_LOCATION_ID
355 --,BILL_FROM_LOCATION_ID
356 --,PAYING_LOCATION_ID
357 --,OWN_HQ_LOCATION_ID
358 --,TRADING_HQ_LOCATION_ID
359 --,POC_LOCATION_ID
360 --,POI_LOCATION_ID
361 --,POD_LOCATION_ID
362 --,TITLE_TRANSFER_LOCATION_ID
363 ,'N' --CTRL_HDR_TX_APPL_FLAG
364 --,CTRL_TOTAL_LINE_TX_AMT
365 --,CTRL_TOTAL_HDR_TX_AMT
366 ,LINE_CLASS
367 ,TRX_LINE_DATE
368 --,INPUT_TAX_CLASSIFICATION_CODE
369 ,OUTPUT_TAX_CLASSIFICATION_CODE
370 --,INTERNAL_ORG_LOCATION_ID
371 --,PORT_OF_ENTRY_CODE
372 ,'Y' --TAX_REPORTING_FLAG
373 ,'N' --TAX_AMT_INCLUDED_FLAG
374 ,'N' --COMPOUNDING_TAX_FLAG
375 --,EVENT_ID
376 ,'N' --THRESHOLD_INDICATOR_FLAG
377 --,PROVNL_TAX_DETERMINATION_DATE
378 ,UNIT_PRICE
379 ,SHIP_TO_CUST_ACCT_SITE_USE_ID
380 ,BILL_TO_CUST_ACCT_SITE_USE_ID
381 ,TRX_BATCH_ID
382 --,START_EXPENSE_DATE
383 --,SOURCE_APPLICATION_ID
384 --,SOURCE_ENTITY_CODE
385 --,SOURCE_EVENT_CLASS_CODE
386 --,SOURCE_TRX_ID
387 --,SOURCE_LINE_ID
388 --,SOURCE_TRX_LEVEL_TYPE
389 ,'MIGRATED' --RECORD_TYPE_CODE
390 ,'N' --INCLUSIVE_TAX_OVERRIDE_FLAG
391 ,'N' --TAX_PROCESSING_COMPLETED_FLAG
392 ,OBJECT_VERSION_NUMBER
393 ,APPLICATION_DOC_STATUS
394 ,'N' --USER_UPD_DET_FACTORS_FLAG
395 --,SOURCE_TAX_LINE_ID
396 --,REVERSED_APPLN_ID
397 --,REVERSED_ENTITY_CODE
398 --,REVERSED_EVNT_CLS_CODE
399 --,REVERSED_TRX_ID
400 --,REVERSED_TRX_LEVEL_TYPE
401 --,REVERSED_TRX_LINE_ID
402 --,TAX_CALCULATION_DONE_FLAG
403 ,PARTNER_MIGRATED_FLAG
404 ,SHIP_THIRD_PTY_ACCT_SITE_ID
405 ,BILL_THIRD_PTY_ACCT_SITE_ID
406 ,SHIP_THIRD_PTY_ACCT_ID
407 ,BILL_THIRD_PTY_ACCT_ID
408 --,INTERFACE_ENTITY_CODE
409 --,INTERFACE_LINE_ID
410 --,HISTORICAL_TAX_CODE_ID
411 --,ICX_SESSION_ID
412 --,TRX_LINE_CURRENCY_CODE
413 --,TRX_LINE_CURRENCY_CONV_RATE
414 --,TRX_LINE_CURRENCY_CONV_DATE
415 --,TRX_LINE_PRECISION
416 --,TRX_LINE_MAU
417 --,TRX_LINE_CURRENCY_CONV_TYPE
418 ,CREATION_DATE
419 ,CREATED_BY
420 ,LAST_UPDATE_DATE
421 ,LAST_UPDATED_BY
422 ,LAST_UPDATE_LOGIN
423 )
424 WHEN (trx_line_type = 'TAX') THEN
425 INTO ZX_LINES (
426 TAX_LINE_ID
427 ,INTERNAL_ORGANIZATION_ID
428 ,APPLICATION_ID
429 ,ENTITY_CODE
430 ,EVENT_CLASS_CODE
431 ,EVENT_TYPE_CODE
432 ,TRX_ID
433 ,TRX_LINE_ID
434 ,TRX_LEVEL_TYPE
435 ,TRX_LINE_NUMBER
439 ,TAX_LINE_NUMBER
436 ,DOC_EVENT_STATUS
437 ,TAX_EVENT_CLASS_CODE
438 ,TAX_EVENT_TYPE_CODE
440 ,CONTENT_OWNER_ID
441 ,TAX_REGIME_ID
442 ,TAX_REGIME_CODE
443 ,TAX_ID
444 ,TAX
445 ,TAX_STATUS_ID
446 ,TAX_STATUS_CODE
447 ,TAX_RATE_ID
448 ,TAX_RATE_CODE
449 ,TAX_RATE
450 ,TAX_RATE_TYPE
451 ,TAX_APPORTIONMENT_LINE_NUMBER
452 ,MRC_TAX_LINE_FLAG
453 ,LEDGER_ID
454 --,ESTABLISHMENT_ID
455 ,LEGAL_ENTITY_ID
456 --,LEGAL_ENTITY_TAX_REG_NUMBER
457 --,HQ_ESTB_REG_NUMBER
458 --,HQ_ESTB_PARTY_TAX_PROF_ID
459 ,CURRENCY_CONVERSION_DATE
460 ,CURRENCY_CONVERSION_TYPE
461 ,CURRENCY_CONVERSION_RATE
462 --,TAX_CURRENCY_CONVERSION_DATE
463 --,TAX_CURRENCY_CONVERSION_TYPE
464 --,TAX_CURRENCY_CONVERSION_RATE
465 ,TRX_CURRENCY_CODE
466 ,MINIMUM_ACCOUNTABLE_UNIT
467 ,PRECISION
468 ,TRX_NUMBER
469 ,TRX_DATE
470 ,UNIT_PRICE
471 ,LINE_AMT
472 ,TRX_LINE_QUANTITY
473 ,TAX_BASE_MODIFIER_RATE
474 --,REF_DOC_APPLICATION_ID
475 --,REF_DOC_ENTITY_CODE
476 --,REF_DOC_EVENT_CLASS_CODE
477 --,REF_DOC_TRX_ID
478 --,REF_DOC_LINE_ID
479 --,REF_DOC_LINE_QUANTITY
480 --,REF_DOC_TRX_LEVEL_TYPE
481 --,OTHER_DOC_LINE_AMT
482 --,OTHER_DOC_LINE_TAX_AMT
483 --,OTHER_DOC_LINE_TAXABLE_AMT
484 ,UNROUNDED_TAXABLE_AMT
485 ,UNROUNDED_TAX_AMT
486 ,RELATED_DOC_APPLICATION_ID
487 --,RELATED_DOC_ENTITY_CODE
488 --,RELATED_DOC_EVENT_CLASS_CODE
489 ,RELATED_DOC_TRX_ID
490 --,RELATED_DOC_NUMBER
491 --,RELATED_DOC_DATE
492 --,RELATED_DOC_TRX_LEVEL_TYPE
493 ,ADJUSTED_DOC_APPLICATION_ID
494 ,ADJUSTED_DOC_ENTITY_CODE
495 ,ADJUSTED_DOC_EVENT_CLASS_CODE --- Bug6024643
496 ,ADJUSTED_DOC_TRX_ID
497 ,ADJUSTED_DOC_LINE_ID
498 ,ADJUSTED_DOC_NUMBER
499 ,ADJUSTED_DOC_DATE
500 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
501 --,SUMMARY_TAX_LINE_ID
502 --,OFFSET_LINK_TO_TAX_LINE_ID
503 ,OFFSET_FLAG
504 ,PROCESS_FOR_RECOVERY_FLAG
505 --,TAX_JURISDICTION_ID
506 --,TAX_JURISDICTION_CODE
507 --,PLACE_OF_SUPPLY
508 ,PLACE_OF_SUPPLY_TYPE_CODE
509 --,PLACE_OF_SUPPLY_RESULT_ID
510 --,TAX_DATE_RULE_ID
511 ,TAX_DATE
512 ,TAX_DETERMINE_DATE
513 ,TAX_POINT_DATE
514 ,TRX_LINE_DATE
515 ,TAX_TYPE_CODE
516 --,TAX_CODE
517 --,TAX_REGISTRATION_ID
518 --,TAX_REGISTRATION_NUMBER
519 --,REGISTRATION_PARTY_TYPE
520 ,ROUNDING_LEVEL_CODE
521 ,ROUNDING_RULE_CODE
522 --,ROUNDING_LVL_PARTY_TAX_PROF_ID
523 --,ROUNDING_LVL_PARTY_TYPE
524 ,COMPOUNDING_TAX_FLAG
525 --,ORIG_TAX_STATUS_ID
526 --,ORIG_TAX_STATUS_CODE
527 --,ORIG_TAX_RATE_ID
528 --,ORIG_TAX_RATE_CODE
529 --,ORIG_TAX_RATE
530 --,ORIG_TAX_JURISDICTION_ID
531 --,ORIG_TAX_JURISDICTION_CODE
532 --,ORIG_TAX_AMT_INCLUDED_FLAG
533 --,ORIG_SELF_ASSESSED_FLAG
534 ,TAX_CURRENCY_CODE
535 ,TAX_AMT
536 ,TAX_AMT_TAX_CURR
537 ,TAX_AMT_FUNCL_CURR
538 ,TAXABLE_AMT
539 ,TAXABLE_AMT_TAX_CURR
540 ,TAXABLE_AMT_FUNCL_CURR
541 --,ORIG_TAXABLE_AMT
542 --,ORIG_TAXABLE_AMT_TAX_CURR
543 ,CAL_TAX_AMT
544 ,CAL_TAX_AMT_TAX_CURR
545 ,CAL_TAX_AMT_FUNCL_CURR
546 --,ORIG_TAX_AMT
547 --,ORIG_TAX_AMT_TAX_CURR
548 --,REC_TAX_AMT
549 --,REC_TAX_AMT_TAX_CURR
550 --,REC_TAX_AMT_FUNCL_CURR
551 --,NREC_TAX_AMT
552 --,NREC_TAX_AMT_TAX_CURR
553 --,NREC_TAX_AMT_FUNCL_CURR
554 ,TAX_EXEMPTION_ID
555 --,TAX_RATE_BEFORE_EXEMPTION
556 --,TAX_RATE_NAME_BEFORE_EXEMPTION
557 --,EXEMPT_RATE_MODIFIER
558 ,EXEMPT_CERTIFICATE_NUMBER
559 --,EXEMPT_REASON
560 ,EXEMPT_REASON_CODE
561 ,TAX_EXCEPTION_ID
562 ,TAX_RATE_BEFORE_EXCEPTION
563 --,TAX_RATE_NAME_BEFORE_EXCEPTION
564 --,EXCEPTION_RATE
565 ,TAX_APPORTIONMENT_FLAG
566 ,HISTORICAL_FLAG
567 ,TAXABLE_BASIS_FORMULA
568 ,TAX_CALCULATION_FORMULA
569 ,CANCEL_FLAG
570 ,PURGE_FLAG
571 ,DELETE_FLAG
572 ,TAX_AMT_INCLUDED_FLAG
573 ,SELF_ASSESSED_FLAG
574 ,OVERRIDDEN_FLAG
575 ,MANUALLY_ENTERED_FLAG
576 ,REPORTING_ONLY_FLAG
577 ,FREEZE_UNTIL_OVERRIDDEN_FLAG
578 ,COPIED_FROM_OTHER_DOC_FLAG
579 ,RECALC_REQUIRED_FLAG
583 ,TAX_ONLY_LINE_FLAG
580 ,SETTLEMENT_FLAG
581 ,ITEM_DIST_CHANGED_FLAG
582 ,ASSOCIATED_CHILD_FROZEN_FLAG
584 ,COMPOUNDING_DEP_TAX_FLAG
585 ,ENFORCE_FROM_NATURAL_ACCT_FLAG
586 ,COMPOUNDING_TAX_MISS_FLAG
587 ,SYNC_WITH_PRVDR_FLAG
588 --,LAST_MANUAL_ENTRY
589 ,TAX_PROVIDER_ID
590 ,RECORD_TYPE_CODE
591 --,REPORTING_PERIOD_ID
592 --,LEGAL_MESSAGE_APPL_2
593 --,LEGAL_MESSAGE_STATUS
594 --,LEGAL_MESSAGE_RATE
595 --,LEGAL_MESSAGE_BASIS
596 --,LEGAL_MESSAGE_CALC
597 --,LEGAL_MESSAGE_THRESHOLD
598 --,LEGAL_MESSAGE_POS
599 --,LEGAL_MESSAGE_TRN
600 --,LEGAL_MESSAGE_EXMPT
601 --,LEGAL_MESSAGE_EXCPT
602 --,TAX_REGIME_TEMPLATE_ID
603 --,TAX_APPLICABILITY_RESULT_ID
604 --,DIRECT_RATE_RESULT_ID
605 --,STATUS_RESULT_ID
606 --,RATE_RESULT_ID
607 --,BASIS_RESULT_ID
608 --,THRESH_RESULT_ID
609 --,CALC_RESULT_ID
610 --,TAX_REG_NUM_DET_RESULT_ID
611 --,EVAL_EXMPT_RESULT_ID
612 --,EVAL_EXCPT_RESULT_ID
613 --,TAX_HOLD_CODE
614 --,TAX_HOLD_RELEASED_CODE
615 --,PRD_TOTAL_TAX_AMT
616 --,PRD_TOTAL_TAX_AMT_TAX_CURR
617 --,PRD_TOTAL_TAX_AMT_FUNCL_CURR
618 --,INTERNAL_ORG_LOCATION_ID
619 ,ATTRIBUTE_CATEGORY
620 ,ATTRIBUTE1
621 ,ATTRIBUTE2
622 ,ATTRIBUTE3
623 ,ATTRIBUTE4
624 ,ATTRIBUTE5
625 ,ATTRIBUTE6
626 ,ATTRIBUTE7
627 ,ATTRIBUTE8
628 ,ATTRIBUTE9
629 ,ATTRIBUTE10
630 ,ATTRIBUTE11
631 ,ATTRIBUTE12
632 ,ATTRIBUTE13
633 ,ATTRIBUTE14
634 ,ATTRIBUTE15
635 ,GLOBAL_ATTRIBUTE_CATEGORY
636 ,GLOBAL_ATTRIBUTE1
637 ,GLOBAL_ATTRIBUTE2
638 ,GLOBAL_ATTRIBUTE3
639 ,GLOBAL_ATTRIBUTE4
640 ,GLOBAL_ATTRIBUTE5
641 ,GLOBAL_ATTRIBUTE6
642 ,GLOBAL_ATTRIBUTE7
643 ,GLOBAL_ATTRIBUTE8
644 ,GLOBAL_ATTRIBUTE9
645 ,GLOBAL_ATTRIBUTE10
646 ,GLOBAL_ATTRIBUTE11
647 ,GLOBAL_ATTRIBUTE12
648 ,GLOBAL_ATTRIBUTE13
649 ,GLOBAL_ATTRIBUTE14
650 ,GLOBAL_ATTRIBUTE15
651 ,GLOBAL_ATTRIBUTE16
652 ,GLOBAL_ATTRIBUTE17
653 ,GLOBAL_ATTRIBUTE18
654 ,GLOBAL_ATTRIBUTE19
655 ,GLOBAL_ATTRIBUTE20
656 ,LEGAL_JUSTIFICATION_TEXT1
657 ,LEGAL_JUSTIFICATION_TEXT2
658 ,LEGAL_JUSTIFICATION_TEXT3
659 --,REPORTING_CURRENCY_CODE
660 --,LINE_ASSESSABLE_VALUE
661 --,TRX_LINE_INDEX
662 --,OFFSET_TAX_RATE_CODE
663 --,PRORATION_CODE
664 --,OTHER_DOC_SOURCE
665 --,CTRL_TOTAL_LINE_TX_AMT
666 --,MRC_LINK_TO_TAX_LINE_ID
667 --,APPLIED_TO_TRX_NUMBER
668 --,INTERFACE_ENTITY_CODE
669 --,INTERFACE_TAX_LINE_ID
670 --,TAXING_JURIS_GEOGRAPHY_ID
671 ,NUMERIC1
672 ,NUMERIC2
673 ,NUMERIC3
674 ,NUMERIC4
675 ,ADJUSTED_DOC_TAX_LINE_ID
676 ,OBJECT_VERSION_NUMBER
677 ,MULTIPLE_JURISDICTIONS_FLAG
678 ,CREATED_BY
679 ,CREATION_DATE
680 ,LAST_UPDATED_BY
681 ,LAST_UPDATE_DATE
682 ,LAST_UPDATE_LOGIN
683 ,LEGAL_REPORTING_STATUS
684 ,ACCOUNT_SOURCE_TAX_RATE_ID
685 )
686 VALUES(
687 TAX_LINE_ID
688 ,INTERNAL_ORGANIZATION_ID
689 ,APPLICATION_ID
690 ,ENTITY_CODE
691 ,EVENT_CLASS_CODE
692 ,EVENT_TYPE_CODE
693 ,TRX_ID
694 ,TRX_LINE_ID
695 ,TRX_LEVEL_TYPE
696 ,TRX_LINE_NUMBER
697 ,DOC_EVENT_STATUS
698 ,TAX_EVENT_CLASS_CODE
699 ,TAX_EVENT_TYPE_CODE
700 ,TAX_LINE_NUMBER
701 ,CONTENT_OWNER_ID
702 ,TAX_REGIME_ID
703 ,TAX_REGIME_CODE
704 ,TAX_ID
705 ,TAX
706 ,TAX_STATUS_ID
707 ,TAX_STATUS_CODE
708 ,TAX_RATE_ID
709 ,TAX_RATE_CODE
710 ,TAX_RATE
711 ,TAX_RATE_TYPE
712 ,TAX_APPORTIONMENT_LINE_NUMBER
713 ,'N' --MRC_TAX_LINE_FLAG
714 ,LEDGER_ID
715 --,ESTABLISHMENT_ID
716 ,LEGAL_ENTITY_ID
717 --,LEGAL_ENTITY_TAX_REG_NUMBER
718 --,HQ_ESTB_REG_NUMBER
719 --,HQ_ESTB_PARTY_TAX_PROF_ID
720 ,CURRENCY_CONVERSION_DATE
721 ,CURRENCY_CONVERSION_TYPE
722 ,CURRENCY_CONVERSION_RATE
723 --,TAX_CURRENCY_CONVERSION_DATE
724 --,TAX_CURRENCY_CONVERSION_TYPE
725 --,TAX_CURRENCY_CONVERSION_RATE
726 ,TRX_CURRENCY_CODE
727 ,MINIMUM_ACCOUNTABLE_UNIT
728 ,PRECISION
729 ,TRX_NUMBER
733 ,TRX_LINE_QUANTITY
730 ,TRX_DATE
731 ,UNIT_PRICE
732 ,LINE_AMT
734 ,TAX_BASE_MODIFIER_RATE
735 --,REF_DOC_APPLICATION_ID
736 --,REF_DOC_ENTITY_CODE
737 --,REF_DOC_EVENT_CLASS_CODE
738 --,REF_DOC_TRX_ID
739 --,REF_DOC_LINE_ID
740 --,REF_DOC_LINE_QUANTITY
741 --,REF_DOC_TRX_LEVEL_TYPE
742 --,OTHER_DOC_LINE_AMT
743 --,OTHER_DOC_LINE_TAX_AMT
744 --,OTHER_DOC_LINE_TAXABLE_AMT
745 ,UNROUNDED_TAXABLE_AMT
746 ,UNROUNDED_TAX_AMT
747 ,RELATED_DOC_APPLICATION_ID
748 --,RELATED_DOC_ENTITY_CODE
749 --,RELATED_DOC_EVENT_CLASS_CODE
750 ,RELATED_DOC_TRX_ID
751 --,RELATED_DOC_NUMBER
752 --,RELATED_DOC_DATE
753 --,RELATED_DOC_TRX_LEVEL_TYPE
754 ,ADJUSTED_DOC_APPLICATION_ID
755 ,ADJUSTED_DOC_ENTITY_CODE
756 ,ADJUSTED_DOC_EVENT_CLASS_CODE --- Bug6024643
757 ,ADJUSTED_DOC_TRX_ID
758 ,ADJUSTED_DOC_LINE_ID
759 ,ADJUSTED_DOC_NUMBER
760 ,ADJUSTED_DOC_DATE
761 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
762 --,SUMMARY_TAX_LINE_ID
763 --,OFFSET_LINK_TO_TAX_LINE_ID
764 ,'N' --OFFSET_FLAG
765 ,'N' --PROCESS_FOR_RECOVERY_FLAG
766 --,TAX_JURISDICTION_ID
767 --,TAX_JURISDICTION_CODE
768 --,PLACE_OF_SUPPLY
769 ,PLACE_OF_SUPPLY_TYPE_CODE
770 --,PLACE_OF_SUPPLY_RESULT_ID
771 --,TAX_DATE_RULE_ID
772 ,TAX_DATE
773 ,TAX_DETERMINE_DATE
774 ,TAX_POINT_DATE
775 ,TRX_LINE_DATE
776 ,TAX_TYPE_CODE
777 --,TAX_CODE
778 --,TAX_REGISTRATION_ID
779 --,TAX_REGISTRATION_NUMBER
780 --,REGISTRATION_PARTY_TYPE
781 ,ROUNDING_LEVEL_CODE
782 ,ROUNDING_RULE_CODE
783 --,ROUNDING_LVL_PARTY_TAX_PROF_ID
784 --,ROUNDING_LVL_PARTY_TYPE
785 ,'N' --COMPOUNDING_TAX_FLAG
786 --,ORIG_TAX_STATUS_ID
787 --,ORIG_TAX_STATUS_CODE
788 --,ORIG_TAX_RATE_ID
789 --,ORIG_TAX_RATE_CODE
790 --,ORIG_TAX_RATE
791 --,ORIG_TAX_JURISDICTION_ID
792 --,ORIG_TAX_JURISDICTION_CODE
793 --,ORIG_TAX_AMT_INCLUDED_FLAG
794 --,ORIG_SELF_ASSESSED_FLAG
795 ,TAX_CURRENCY_CODE
796 ,TAX_AMT
797 ,TAX_AMT_TAX_CURR
798 ,TAX_AMT_FUNCL_CURR
799 ,TAXABLE_AMT
800 ,TAXABLE_AMT_TAX_CURR
801 ,TAXABLE_AMT_FUNCL_CURR
802 --,ORIG_TAXABLE_AMT
803 --,ORIG_TAXABLE_AMT_TAX_CURR
804 ,CAL_TAX_AMT
805 ,CAL_TAX_AMT_TAX_CURR
806 ,CAL_TAX_AMT_FUNCL_CURR
807 --,ORIG_TAX_AMT
808 --,ORIG_TAX_AMT_TAX_CURR
809 --,REC_TAX_AMT
810 --,REC_TAX_AMT_TAX_CURR
811 --,REC_TAX_AMT_FUNCL_CURR
812 --,NREC_TAX_AMT
813 --,NREC_TAX_AMT_TAX_CURR
814 --,NREC_TAX_AMT_FUNCL_CURR
815 ,TAX_EXEMPTION_ID
816 --,TAX_RATE_BEFORE_EXEMPTION
817 --,TAX_RATE_NAME_BEFORE_EXEMPTION
818 --,EXEMPT_RATE_MODIFIER
819 ,EXEMPT_CERTIFICATE_NUMBER
820 --,EXEMPT_REASON
821 ,EXEMPT_REASON_CODE
822 ,TAX_EXCEPTION_ID
823 ,TAX_RATE_BEFORE_EXCEPTION
824 --,TAX_RATE_NAME_BEFORE_EXCEPTION
825 --,EXCEPTION_RATE
826 ,'N' --TAX_APPORTIONMENT_FLAG
827 ,'Y' --HISTORICAL_FLAG
828 ,TAXABLE_BASIS_FORMULA
829 ,TAX_CALCULATION_FORMULA
830 ,'N' --CANCEL_FLAG
831 ,'N' --PURGE_FLAG
832 ,'N' --DELETE_FLAG
833 ,'N' --TAX_AMT_INCLUDED_FLAG
834 ,'N' --SELF_ASSESSED_FLAG
835 ,'N' --OVERRIDDEN_FLAG
836 ,'N' --MANUALLY_ENTERED_FLAG
837 ,'N' --REPORTING_ONLY_FLAG
838 ,'N' --FREEZE_UNTIL_OVERRIDDEN_FLAG
839 ,'N' --COPIED_FROM_OTHER_DOC_FLAG
840 ,'N' --RECALC_REQUIRED_FLAG
841 ,'N' --SETTLEMENT_FLAG
842 ,'N' --ITEM_DIST_CHANGED_FLAG
843 ,'N' --ASSOCIATED_CHILD_FROZEN_FLAG
844 ,TAX_ONLY_LINE_FLAG
845 ,'N' --COMPOUNDING_DEP_TAX_FLAG
846 ,'N' --ENFORCE_FROM_NATURAL_ACCT_FLAG
847 ,'N' --COMPOUNDING_TAX_MISS_FLAG
848 ,'N' --SYNC_WITH_PRVDR_FLAG
849 --,LAST_MANUAL_ENTRY
850 ,TAX_PROVIDER_ID
851 ,'MIGRATED' --RECORD_TYPE_CODE
852 --,REPORTING_PERIOD_ID
853 --,LEGAL_MESSAGE_APPL_2
854 --,LEGAL_MESSAGE_STATUS
855 --,LEGAL_MESSAGE_RATE
856 --,LEGAL_MESSAGE_BASIS
857 --,LEGAL_MESSAGE_CALC
858 --,LEGAL_MESSAGE_THRESHOLD
859 --,LEGAL_MESSAGE_POS
860 --,LEGAL_MESSAGE_TRN
861 --,LEGAL_MESSAGE_EXMPT
862 --,LEGAL_MESSAGE_EXCPT
863 --,TAX_REGIME_TEMPLATE_ID
864 --,TAX_APPLICABILITY_RESULT_ID
865 --,DIRECT_RATE_RESULT_ID
866 --,STATUS_RESULT_ID
867 --,RATE_RESULT_ID
871 --,TAX_REG_NUM_DET_RESULT_ID
868 --,BASIS_RESULT_ID
869 --,THRESH_RESULT_ID
870 --,CALC_RESULT_ID
872 --,EVAL_EXMPT_RESULT_ID
873 --,EVAL_EXCPT_RESULT_ID
874 --,TAX_HOLD_CODE
875 --,TAX_HOLD_RELEASED_CODE
876 --,PRD_TOTAL_TAX_AMT
877 --,PRD_TOTAL_TAX_AMT_TAX_CURR
878 --,PRD_TOTAL_TAX_AMT_FUNCL_CURR
879 --,INTERNAL_ORG_LOCATION_ID
880 ,ATTRIBUTE_CATEGORY
881 ,ATTRIBUTE1
882 ,ATTRIBUTE2
883 ,ATTRIBUTE3
884 ,ATTRIBUTE4
885 ,ATTRIBUTE5
886 ,ATTRIBUTE6
887 ,ATTRIBUTE7
888 ,ATTRIBUTE8
889 ,ATTRIBUTE9
890 ,ATTRIBUTE10
891 ,ATTRIBUTE11
892 ,ATTRIBUTE12
893 ,ATTRIBUTE13
894 ,ATTRIBUTE14
895 ,ATTRIBUTE15
896 ,GLOBAL_ATTRIBUTE_CATEGORY
897 ,GLOBAL_ATTRIBUTE1
898 ,GLOBAL_ATTRIBUTE2
899 ,GLOBAL_ATTRIBUTE3
900 ,GLOBAL_ATTRIBUTE4
901 ,GLOBAL_ATTRIBUTE5
902 ,GLOBAL_ATTRIBUTE6
903 ,GLOBAL_ATTRIBUTE7
904 ,GLOBAL_ATTRIBUTE8
905 ,GLOBAL_ATTRIBUTE9
906 ,GLOBAL_ATTRIBUTE10
907 ,GLOBAL_ATTRIBUTE11
908 ,GLOBAL_ATTRIBUTE12
909 ,GLOBAL_ATTRIBUTE13
910 ,GLOBAL_ATTRIBUTE14
911 ,GLOBAL_ATTRIBUTE15
912 ,GLOBAL_ATTRIBUTE16
913 ,GLOBAL_ATTRIBUTE17
914 ,GLOBAL_ATTRIBUTE18
915 ,GLOBAL_ATTRIBUTE19
916 ,GLOBAL_ATTRIBUTE20
917 ,LEGAL_JUSTIFICATION_TEXT1
918 ,LEGAL_JUSTIFICATION_TEXT2
919 ,LEGAL_JUSTIFICATION_TEXT3
920 --,REPORTING_CURRENCY_CODE
921 --,LINE_ASSESSABLE_VALUE
922 --,TRX_LINE_INDEX
923 --,OFFSET_TAX_RATE_CODE
924 --,PRORATION_CODE
925 --,OTHER_DOC_SOURCE
926 --,CTRL_TOTAL_LINE_TX_AMT
927 --,MRC_LINK_TO_TAX_LINE_ID
928 --,APPLIED_TO_TRX_NUMBER
929 --,INTERFACE_ENTITY_CODE
930 --,INTERFACE_TAX_LINE_ID
931 --,TAXING_JURIS_GEOGRAPHY_ID
932 ,NUMERIC1
933 ,NUMERIC2
934 ,NUMERIC3
935 ,NUMERIC4
936 ,ADJUSTED_DOC_TAX_LINE_ID
937 ,OBJECT_VERSION_NUMBER
938 ,'N' --MULTIPLE_JURISDICTIONS_FLAG
939 ,CREATED_BY
940 ,CREATION_DATE
941 ,LAST_UPDATED_BY
942 ,LAST_UPDATE_DATE
943 ,LAST_UPDATE_LOGIN
944 ,LEGAL_REPORTING_STATUS
945 ,ACCOUNT_SOURCE_TAX_RATE_ID
946 )
947 SELECT /*+ ROWID(custtrx) ORDERED use_hash(arsysparam) swap_join_inputs(arsysparam)
948 use_nl(types,fndcurr,fds,ptp,rbs,custtrx_prev,custtrxl,vat,rates,custtrxll,memoline)
949 PUSH_SUBQ */
950 NVL(custtrx.org_id, l_org_id) INTERNAL_ORGANIZATION_ID,
951 222 APPLICATION_ID,
952 'TRANSACTIONS' ENTITY_CODE,
953 DECODE(types.type,
954 'INV','INVOICE',
955 'CM', 'CREDIT_MEMO',
956 'DM', 'DEBIT_MEMO',
957 'NONE') EVENT_CLASS_CODE,
958 DECODE(types.type,
959 'INV',4,
960 'DM', 5,
961 'CM', 6, NULL ) EVENT_CLASS_MAPPING_ID,
962 -- DECODE(types.type,
963 -- 'INV', 'INV_CREATE',
964 -- 'CM', 'CM_CREATE',
965 -- 'DM', 'DM_CREATE',
966 -- 'CREATE') EVENT_TYPE_CODE,
967 DECODE(types.type,
968 'INV',DECODE(NVL(SIGN(custtrx.printing_count), 0),
969 1, 'INV_PRINT',
970 DECODE(custtrx.complete_flag,
971 'Y', 'INV_COMPLETE',
972 'INV_CREATE')),
973 'CM',DECODE(NVL(SIGN(custtrx.printing_count), 0),
974 1, 'CM_PRINT',
975 DECODE(custtrx.complete_flag,
976 'Y', 'CM_COMPLETE',
977 'CM_CREATE')),
978 'DM',DECODE(NVL(SIGN(custtrx.printing_count), 0),
979 1, 'DM_PRINT',
980 DECODE(custtrx.complete_flag,
981 'Y', 'DM_COMPLETE',
982 'DM_CREATE')),
983 'CREATE') EVENT_TYPE_CODE,
984 'CREATED' DOC_EVENT_STATUS,
985 'CREATE' LINE_LEVEL_ACTION,
986 custtrx.customer_trx_id TRX_ID,
987 DECODE(custtrxl.line_type,
988 'TAX', custtrxl.link_to_cust_trx_line_id,
989 custtrxl.customer_trx_line_id) TRX_LINE_ID,
990 'LINE' TRX_LEVEL_TYPE,
991 NVL(custtrx.trx_date,sysdate) TRX_DATE,
992
993 --NULL TRX_DOC_REVISION,
994 NVL(custtrx.invoice_currency_code,'USD') TRX_CURRENCY_CODE,
995 custtrx.exchange_date CURRENCY_CONVERSION_DATE,
996 custtrx.exchange_rate CURRENCY_CONVERSION_RATE,
1000 NVL(custtrx.legal_entity_id, -99 ) LEGAL_ENTITY_ID,
997 custtrx.exchange_rate_type CURRENCY_CONVERSION_TYPE,
998 fndcurr.minimum_accountable_unit MINIMUM_ACCOUNTABLE_UNIT,
999 NVL(fndcurr.precision,0) PRECISION,
1001 --NULL ESTABLISHMENT_ID,
1002 custtrx.cust_trx_type_id RECEIVABLES_TRX_TYPE_ID,
1003 arsysparam.default_country DEFAULT_TAXATION_COUNTRY,
1004 custtrx.trx_number TRX_NUMBER,
1005 DECODE(custtrxl.line_type,
1006 'TAX', custtrxll.line_number,
1007 custtrxl.line_number) TRX_LINE_NUMBER,
1008 SUBSTRB(custtrxl.description,1,240) TRX_LINE_DESCRIPTION,
1009 --NULL TRX_DESCRIPTION,
1010 --NULL TRX_COMMUNICATED_DATE,
1011 custtrx.batch_source_id BATCH_SOURCE_ID,
1012 rbs.name BATCH_SOURCE_NAME,
1013 custtrx.doc_sequence_id DOC_SEQ_ID,
1014 fds.name DOC_SEQ_NAME,
1015 custtrx.doc_sequence_value DOC_SEQ_VALUE,
1016 custtrx.term_due_date TRX_DUE_DATE,
1017 types.description TRX_TYPE_DESCRIPTION,
1018 (CASE
1019 WHEN (custtrx.global_attribute_category
1020 = 'JA.TW.ARXTWMAI.RA_CUSTOMER_TRX' AND
1021 custtrx.global_attribute1 is NOT NULL) THEN
1022 'GUI TYPE.' || custtrx.global_attribute1
1023 END) DOCUMENT_SUB_TYPE,
1024 --NULL SUPPLIER_TAX_INVOICE_NUMBER,
1025 --NULL SUPPLIER_TAX_INVOICE_DATE,
1026 --NULL SUPPLIER_EXCHANGE_RATE,
1027 (CASE
1028 WHEN custtrx.global_attribute_category
1029 IN ('JE.HU.ARXTWMAI.TAX_DATE',
1030 'JE.SK.ARXTWMAI.TAX_DATE',
1031 'JE.PL.ARXTWMAI.TAX_DATE',
1032 'JE.CZ.ARXTWMAI.TAX_DATE')
1033 THEN
1034 TO_DATE(custtrx.global_attribute1, 'YYYY/MM/DD HH24:MI:SS')
1035 WHEN custtrx.global_attribute_category
1036 = 'JL.AR.ARXTWMAI.TGW_HEADER' THEN
1037 TO_DATE(custtrx.global_attribute18, 'YYYY/MM/DD HH24:MI:SS')
1038 END) TAX_INVOICE_DATE,
1039
1040 (CASE
1041 WHEN custtrx.global_attribute_category
1042 = 'JL.AR.ARXTWMAI.TGW_HEADER' THEN
1043 custtrx.global_attribute17
1044 END) TAX_INVOICE_NUMBER,
1045 ptp.party_tax_profile_id FIRST_PTY_ORG_ID,
1046 'SALES_TRANSACTION' TAX_EVENT_CLASS_CODE,
1047 -- 'CREATE' TAX_EVENT_TYPE_CODE,
1048 DECODE(NVL(SIGN(custtrx.printing_count), 0),
1049 1, 'FREEZE_FOR_TAX',
1050 DECODE(custtrx.complete_flag,
1051 'Y', 'VALIDATE_FOR_TAX',
1052 'CREATE') ) TAX_EVENT_TYPE_CODE,
1053
1054 --NULL LINE_INTENDED_USE,
1055 custtrxl.line_type TRX_LINE_TYPE,
1056 --NULL TRX_SHIPPING_DATE,
1057 --NULL TRX_RECEIPT_DATE,
1058 --NULL TRX_SIC_CODE,
1059 custtrx.fob_point FOB_POINT,
1060 custtrx.waybill_number TRX_WAYBILL_NUMBER,
1061 custtrxl.inventory_item_id PRODUCT_ID,
1062 (CASE
1063 WHEN custtrx.global_attribute_category
1064 = 'JA.TW.ARXTWMAI.RA_CUSTOMER_TRX'
1065 AND l_inv_installed = 'Y'
1066 THEN
1067 DECODE(custtrxl.global_attribute2,
1068 'Y', 'WINE CIGARRETE',
1069 'N', NULL)
1070
1071 WHEN custtrxl.global_attribute_category
1072 IN ('JL.AR.ARXTWMAI.LINES',
1073 'JL.BR.ARXTWMAI.Additional Info',
1074 'JL.CO.ARXTWMAI.LINES' )
1075 AND l_inv_installed = 'Y'
1076 THEN
1077 custtrxl.global_attribute2
1078 END) PRODUCT_FISC_CLASSIFICATION,
1079 custtrxl.warehouse_id PRODUCT_ORG_ID,
1080 custtrxl.uom_code UOM_CODE,
1081 --NULL PRODUCT_TYPE,
1082 --NULL PRODUCT_CODE,
1083 (CASE
1084 WHEN custtrx.global_attribute_category
1085 = 'JA.TW.ARXTWMAI.RA_CUSTOMER_TRX'
1086 AND l_inv_installed = 'N'
1087 THEN
1088 DECODE(custtrxl.global_attribute2,
1089 'Y', 'WINE CIGARRETE',
1090 'N', NULL)
1091
1092 WHEN custtrxl.global_attribute_category
1093 IN ('JL.AR.ARXTWMAI.LINES',
1094 'JL.BR.ARXTWMAI.Additional Info',
1095 'JL.CO.ARXTWMAI.LINES')
1096 AND l_inv_installed = 'N'
1097 THEN
1098 custtrxl.global_attribute2
1099 END) PRODUCT_CATEGORY,
1100
1104 (CASE
1101 DECODE( custtrxl.inventory_item_id,
1102 NULL,NULL,
1103 SUBSTRB(custtrxl.description,1,240) ) PRODUCT_DESCRIPTION,
1105 WHEN custtrxl.global_attribute_category
1106 = 'JL.BR.ARXTWMAI.Additional Info'
1107 THEN
1108 custtrxl.global_attribute1
1109 END) USER_DEFINED_FISC_CLASS,
1110
1111 DECODE( custtrxl.line_type,
1112 'TAX', nvl(custtrxll.extended_amount,0),
1113 nvl(custtrxl.extended_amount,0)) LINE_AMT,
1114
1115 DECODE(custtrxl.line_type,
1116 'TAX', custtrxll.quantity_invoiced,
1117 custtrxl.quantity_invoiced ) TRX_LINE_QUANTITY,
1118
1119 --NULL CASH_DISCOUNT,
1120 --NULL VOLUME_DISCOUNT,
1121 --NULL TRADING_DISCOUNT,
1122 --NULL TRANSFER_CHARGE,
1123 --NULL TRANSPORTATION_CHARGE,
1124 --NULL INSURANCE_CHARGE,
1125 --NULL OTHER_CHARGE,
1126 --NULL ASSESSABLE_VALUE,
1127 --NULL ASSET_FLAG,
1128 --NULL ASSET_NUMBER,
1129 1 ASSET_ACCUM_DEPRECIATION,
1130 --NULL ASSET_TYPE,
1131 1 ASSET_COST,
1132
1133 DECODE( custtrx.related_customer_trx_id,
1134 NULL, NULL,
1135 222) RELATED_DOC_APPLICATION_ID,
1136 --NULL RELATED_DOC_ENTITY_CODE,
1137 --NULL RELATED_DOC_EVENT_CLASS_CODE,
1138 custtrx.related_customer_trx_id RELATED_DOC_TRX_ID,
1139 --NULL RELATED_DOC_NUMBER,
1140 --NULL RELATED_DOC_DATE,
1141
1142 DECODE(custtrxl.previous_customer_trx_id,
1143 NULL, NULL,
1144 222 ) ADJUSTED_DOC_APPLICATION_ID,
1145 DECODE(custtrxl.previous_customer_trx_id,
1146 NULL, NULL,
1147 'TRANSACTIONS' ) ADJUSTED_DOC_ENTITY_CODE,
1148 --NULL ADJUSTED_DOC_EVENT_CLASS_CODE,
1149 ---Bug6024643
1150 DECODE(types.type,
1151 'CM', 'INVOICE',
1152 'DM', 'INVOICE',
1153 NULL) ADJUSTED_DOC_EVENT_CLASS_CODE,
1154 custtrxl.previous_customer_trx_id ADJUSTED_DOC_TRX_ID,
1155
1156 DECODE(custtrxl.line_type,
1157 'TAX', custtrxll.previous_customer_trx_line_id,
1158 custtrxl.previous_customer_trx_line_id) ADJUSTED_DOC_LINE_ID,
1159
1160 custtrx_prev.trx_number ADJUSTED_DOC_NUMBER,
1161 custtrx_prev.trx_Date ADJUSTED_DOC_DATE,
1162 DECODE(custtrxl.previous_customer_trx_id,
1163 NULL, NULL,
1164 'LINE' ) ADJUSTED_DOC_TRX_LEVEL_TYPE,
1165
1166 --NULL REF_DOC_APPLICATION_ID,
1167 --NULL REF_DOC_ENTITY_CODE,
1168 --NULL REF_DOC_EVENT_CLASS_CODE,
1169 --NULL REF_DOC_TRX_ID,
1170 --NULL REF_DOC_LINE_ID,
1171 --NULL REF_DOC_LINE_QUANTITY,
1172 --NULL REF_DOC_TRX_LEVEL_TYPE,
1173
1174 (CASE
1175 WHEN custtrx.global_attribute_category
1176 = 'JA.TW.ARXTWMAI.RA_CUSTOMER_TRX'
1177 THEN
1178 'SALES_TRANSACTION.' ||custtrx.global_attribute3
1179
1180 WHEN custtrx.global_attribute_category IN
1181 ('JE.ES.ARXTWMAI.INVOICE_INFO'
1182 ,'JE.ES.ARXTWMAI.MODELO347'
1183 ,'JE.ES.ARXTWMAI.MODELO347PR'
1184 ,'JE.ES.ARXTWMAI.MODELO349'
1185 ,'JE.ES.ARXTWMAI.MODELO415'
1186 ,'JE.ES.ARXTWMAI.MODELO415_347'
1187 ,'JE.ES.ARXTWMAI.MODELO415_347PR'
1188 ,'JE.ES.ARXTWMAI.OTHER')
1189 THEN
1190 'SALES_TRANSACTION.INVOICE TYPE.'||custtrx.global_attribute1
1191
1192 WHEN custtrxl.global_attribute_category IN
1193 ('JL.AR.ARXTWMAI.LINES'
1194 ,'JL.BR.ARXTWMAI.Additional Info'
1195 ,'JL.CO.ARXTWMAI.LINES')
1196 THEN
1197 'SALES_TRANSACTION.' ||custtrxl.global_attribute3
1198 END ) TRX_BUSINESS_CATEGORY,
1199
1200 custtrxl.tax_exempt_number EXEMPT_CERTIFICATE_NUMBER,
1201 --NULL EXEMPT_REASON,
1202 custtrxl.tax_exempt_flag EXEMPTION_CONTROL_FLAG,
1203 custtrxl.tax_exempt_reason_code EXEMPT_REASON_CODE,
1204 --'Y' HISTORICAL_FLAG,
1208 --NULL ACCOUNT_STRING,
1205 NVL(custtrx.trx_date,sysdate) TRX_LINE_GL_DATE,
1206 --'N' LINE_AMT_INCLUDES_TAX_FLAG,
1207 --NULL ACCOUNT_CCID,
1209 --NULL SHIP_TO_LOCATION_ID,
1210 --NULL SHIP_FROM_LOCATION_ID,
1211 --NULL POA_LOCATION_ID,
1212 --NULL POO_LOCATION_ID,
1213 --NULL BILL_TO_LOCATION_ID,
1214 --NULL BILL_FROM_LOCATION_ID,
1215 --NULL PAYING_LOCATION_ID,
1216 --NULL OWN_HQ_LOCATION_ID,
1217 --NULL TRADING_HQ_LOCATION_ID,
1218 --NULL POC_LOCATION_ID,
1219 --NULL POI_LOCATION_ID,
1220 --NULL POD_LOCATION_ID,
1221 --NULL TITLE_TRANSFER_LOCATION_ID,
1222 --'N' CTRL_HDR_TX_APPL_FLAG,
1223 --NULL CTRL_TOTAL_LINE_TX_AMT,
1224 --NULL CTRL_TOTAL_HDR_TX_AMT,
1225
1226 DECODE(types.type,
1227 'INV','INVOICE',
1228 'CM', 'CREDIT_MEMO',
1229 'DM', 'DEBIT_MEMO',
1230 types.type) LINE_CLASS,
1231 NVL(custtrx.trx_date,sysdate) TRX_LINE_DATE,
1232 --NULL INPUT_TAX_CLASSIFICATION_CODE,
1233 vat.tax_code OUTPUT_TAX_CLASSIFICATION_CODE,
1234 --NULL INTERNAL_ORG_LOCATION_ID,
1235 --NULL PORT_OF_ENTRY_CODE,
1236 --'Y' TAX_REPORTING_FLAG,
1237 --'N' TAX_AMT_INCLUDED_FLAG,
1238 --'N' COMPOUNDING_TAX_FLAG,
1239 --NULL EVENT_ID,
1240 --'N' THRESHOLD_INDICATOR_FLAG,
1241 --NULL PROVNL_TAX_DETERMINATION_DATE,
1242 DECODE(custtrxl.line_type,
1243 'TAX', custtrxll.unit_selling_price,
1244 custtrxl.unit_selling_price ) UNIT_PRICE,
1245 custtrx.ship_to_site_use_id SHIP_TO_CUST_ACCT_SITE_USE_ID,
1246 custtrx.bill_to_site_use_id BILL_TO_CUST_ACCT_SITE_USE_ID,
1247 custtrx.batch_id TRX_BATCH_ID,
1248
1249 --NULL START_EXPENSE_DATE,
1250 --NULL SOURCE_APPLICATION_ID,
1251 --NULL SOURCE_ENTITY_CODE,
1252 --NULL SOURCE_EVENT_CLASS_CODE,
1253 --NULL SOURCE_TRX_ID,
1254 --NULL SOURCE_LINE_ID,
1255 --NULL SOURCE_TRX_LEVEL_TYPE,
1256 --'MIGRATED' RECORD_TYPE_CODE,
1257 --'N' INCLUSIVE_TAX_OVERRIDE_FLAG,
1258 --'N' TAX_PROCESSING_COMPLETED_FLAG,
1259 1 OBJECT_VERSION_NUMBER,
1260 DECODE(types.default_status,
1261 'VD', 'VD',
1262 NULL) APPLICATION_DOC_STATUS,
1263 --'N' USER_UPD_DET_FACTORS_FLAG,
1264 --NULL SOURCE_TAX_LINE_ID,
1265 --NULL REVERSED_APPLN_ID,
1266 --NULL REVERSED_ENTITY_CODE,
1267 --NULL REVERSED_EVNT_CLS_CODE,
1268 --NULL REVERSED_TRX_ID,
1269 --NULL REVERSED_TRX_LEVEL_TYPE,
1270 --NULL REVERSED_TRX_LINE_ID,
1271 --NULL TAX_CALCULATION_DONE_FLAG,
1272 decode(arsysparam.tax_database_view_set,'_A','Y','_V','Y',NULL)
1273 PARTNER_MIGRATED_FLAG,
1274 custtrx.ship_to_address_id SHIP_THIRD_PTY_ACCT_SITE_ID,
1275 custtrx.bill_to_address_id BILL_THIRD_PTY_ACCT_SITE_ID,
1276 custtrx.ship_to_customer_id SHIP_THIRD_PTY_ACCT_ID,
1277 custtrx.bill_to_customer_id BILL_THIRD_PTY_ACCT_ID,
1278
1279 --NULL INTERFACE_ENTITY_CODE,
1280 --NULL INTERFACE_LINE_ID,
1281 --NULL HISTORICAL_TAX_CODE_ID,
1282 --NULL ICX_SESSION_ID,
1286 --NULL TRX_LINE_PRECISION,
1283 --NULL TRX_LINE_CURRENCY_CODE,
1284 --NULL TRX_LINE_CURRENCY_CONV_RATE,
1285 --NULL TRX_LINE_CURRENCY_CONV_DATE,
1287 --NULL TRX_LINE_MAU,
1288 --NULL TRX_LINE_CURRENCY_CONV_TYPE,
1289
1290 -- zx_lines columns start from here
1291
1292 custtrxl.tax_line_id TAX_LINE_ID,
1293 DECODE(custtrxl.line_type,
1294 'TAX', RANK() OVER (
1295 PARTITION BY
1296 custtrxl.link_to_cust_trx_line_id,
1297 custtrxl.customer_trx_id
1298 ORDER BY
1299 custtrxl.line_number,
1300 custtrxl.customer_trx_line_id
1301 ),
1302 NULL) TAX_LINE_NUMBER,
1303 ptp.party_tax_profile_id CONTENT_OWNER_ID,
1304 regimes.tax_regime_id TAX_REGIME_ID,
1305 rates.TAX_REGIME_CODE TAX_REGIME_CODE,
1306 taxes.tax_id TAX_ID,
1307 rates.tax TAX,
1308 status.tax_status_id TAX_STATUS_ID,
1309 rates.TAX_STATUS_CODE TAX_STATUS_CODE,
1310 custtrxl.vat_tax_id TAX_RATE_ID,
1311 rates.TAX_RATE_CODE TAX_RATE_CODE,
1312 custtrxl.tax_rate TAX_RATE,
1313 rates.rate_type_code TAX_RATE_TYPE,
1314
1315 DECODE(custtrxl.line_type,
1316 'TAX', RANK() OVER (
1317 PARTITION BY
1318 rates.tax_regime_code,
1319 rates.tax,
1320 custtrxl.link_to_cust_trx_line_id,
1321 custtrxl.customer_trx_id
1322 ORDER BY
1323 custtrxl.line_number,
1324 custtrxl.customer_trx_line_id
1325 ),
1326 NULL) TAX_APPORTIONMENT_LINE_NUMBER,
1327
1328 --'N' MRC_TAX_LINE_FLAG,
1329 custtrx.set_of_books_id LEDGER_ID,
1330 --NULL LEGAL_ENTITY_TAX_REG_NUMBER,
1331 --NULL HQ_ESTB_REG_NUMBER,
1332 --NULL HQ_ESTB_PARTY_TAX_PROF_ID,
1333 --NULL TAX_CURRENCY_CONVERSION_DATE,
1334 --NULL TAX_CURRENCY_CONVERSION_TYPE,
1335 --NULL TAX_CURRENCY_CONVERSION_RATE,
1336
1337 (CASE
1338 WHEN custtrxl.global_attribute_category
1339 IN ('JL.BR.ARXTWMAI.Additional Info',
1340 'JL.CO.ARXTWMAI.LINES',
1341 'JL.AR.ARXTWMAI.LINES')
1342 THEN
1343 DECODE(LTRIM(custtrxl.global_attribute12,'-.0123456789'),
1344 NULL,TO_NUMBER(custtrxl.global_attribute12),
1345 NULL)
1346 END) TAX_BASE_MODIFIER_RATE,
1347
1348 --NULL OTHER_DOC_LINE_AMT,
1349 --NULL OTHER_DOC_LINE_TAX_AMT,
1350 --NULL OTHER_DOC_LINE_TAXABLE_AMT,
1351 (CASE
1352 WHEN custtrxl.global_attribute_category
1353 IN ( 'JL.BR.ARXTWMAI.Additional Info',
1354 'JL.CO.ARXTWMAI.LINES',
1355 'JL.AR.ARXTWMAI.LINES')
1356 THEN
1357 DECODE(LTRIM(custtrxl.global_attribute11,'-.0123456789'),
1358 NULL,TO_NUMBER(custtrxl.global_attribute11),
1359 NULL)
1360 ELSE
1361 custtrxl.taxable_amount
1362 END) UNROUNDED_TAXABLE_AMT,
1363
1364 (CASE
1365 WHEN custtrxl.global_attribute_category
1366 IN( 'JL.BR.ARXTWMAI.Additional Info',
1367 'JL.CO.ARXTWMAI.LINES',
1368 'JL.AR.ARXTWMAI.LINES')
1369 THEN
1370 DECODE(LTRIM(custtrxl.global_attribute19,'-.0123456789'),
1371 NULL,TO_NUMBER(custtrxl.global_attribute19),
1372 NULL)
1373 ELSE
1374 custtrxl.extended_amount
1375 END) UNROUNDED_TAX_AMT,
1376 --NULL RELATED_DOC_TRX_LEVEL_TYPE,
1377 --NULL SUMMARY_TAX_LINE_ID,
1378 --NULL OFFSET_LINK_TO_TAX_LINE_ID,
1379 --'N' OFFSET_FLAG,
1380 --'N' PROCESS_FOR_RECOVERY_FLAG,
1381 --NULL TAX_JURISDICTION_ID,
1382 --NULL TAX_JURISDICTION_CODE,
1383 --NULL PLACE_OF_SUPPLY,
1387 --NULL TAX_DATE_RULE_ID,
1384 -- decode(custtrx.ship_to_site_use_id,null,'BILL_TO','SHIP_TO') PLACE_OF_SUPPLY_TYPE_CODE,
1385 'SHIP_TO_BILL_TO' PLACE_OF_SUPPLY_TYPE_CODE,
1386 --NULL PLACE_OF_SUPPLY_RESULT_ID,
1388 DECODE(custtrxl.previous_customer_trx_id,
1389 NULL, custtrx.trx_date,
1390 custtrx_prev.trx_date ) TAX_DATE,
1391 DECODE(custtrxl.previous_customer_trx_id,
1392 NULL, custtrx.trx_date,
1393 custtrx_prev.trx_date ) TAX_DETERMINE_DATE,
1394 DECODE(custtrxl.previous_customer_trx_id,
1395 NULL, custtrx.trx_date,
1396 custtrx_prev.trx_date ) TAX_POINT_DATE,
1397 taxes.tax_type_code TAX_TYPE_CODE,
1398 --NULL TAX_CODE,
1399 --NULL TAX_REGISTRATION_ID,
1400 --NULL TAX_REGISTRATION_NUMBER,
1401 --NULL REGISTRATION_PARTY_TYPE,
1402 decode (arsysparam.TRX_HEADER_LEVEL_ROUNDING,
1403 'Y', 'HEADER',
1404 'LINE') ROUNDING_LEVEL_CODE,
1405 arsysparam.TAX_ROUNDING_RULE ROUNDING_RULE_CODE,
1406 --NULL ROUNDING_LVL_PARTY_TAX_PROF_ID,
1407 --NULL ROUNDING_LVL_PARTY_TYPE,
1408 --NULL ORIG_TAX_STATUS_ID,
1409 --NULL ORIG_TAX_STATUS_CODE,
1410 --NULL ORIG_TAX_RATE_ID,
1411 --NULL ORIG_TAX_RATE_CODE,
1412 --NULL ORIG_TAX_RATE,
1413 --NULL ORIG_TAX_JURISDICTION_ID,
1414 --NULL ORIG_TAX_JURISDICTION_CODE,
1415 --NULL ORIG_TAX_AMT_INCLUDED_FLAG,
1416 --NULL ORIG_SELF_ASSESSED_FLAG,
1417 taxes.tax_currency_code TAX_CURRENCY_CODE,
1418 custtrxl.extended_amount TAX_AMT,
1419 (CASE
1420 WHEN custtrxl.global_attribute_category
1421 IN( 'JL.BR.ARXTWMAI.Additional Info',
1422 'JL.CO.ARXTWMAI.LINES',
1423 'JL.AR.ARXTWMAI.LINES')
1424 THEN
1425 DECODE(LTRIM(custtrxl.global_attribute19,'-.0123456789'),
1426 NULL,TO_NUMBER(custtrxl.global_attribute19)*
1427 NVL(custtrx.exchange_rate,1),
1428 NULL)
1429 ELSE
1430 custtrxl.extended_amount *
1431 NVL(custtrx.exchange_rate,1)
1432 END) TAX_AMT_TAX_CURR,
1433
1434 (CASE
1435 WHEN custtrxl.global_attribute_category
1436 IN( 'JL.BR.ARXTWMAI.Additional Info',
1437 'JL.CO.ARXTWMAI.LINES',
1438 'JL.AR.ARXTWMAI.LINES')
1439 THEN
1440 DECODE(LTRIM(custtrxl.global_attribute19,'-.0123456789'),
1441 NULL,TO_NUMBER(custtrxl.global_attribute19)*
1442 NVL(custtrx.exchange_rate,1),
1443 NULL)
1444 ELSE
1445 custtrxl.extended_amount *
1446 NVL(custtrx.exchange_rate,1)
1447 END) TAX_AMT_FUNCL_CURR,
1448
1449 (CASE
1450 WHEN custtrxl.global_attribute_category
1451 IN ( 'JL.BR.ARXTWMAI.Additional Info',
1452 'JL.CO.ARXTWMAI.LINES',
1453 'JL.AR.ARXTWMAI.LINES')
1454 THEN
1455 DECODE(LTRIM(custtrxl.global_attribute11,'-.0123456789'),
1456 NULL,TO_NUMBER(custtrxl.global_attribute11),
1457 NULL)
1458 ELSE
1459 custtrxl.taxable_amount
1460 END) TAXABLE_AMT,
1461
1462 (CASE
1463 WHEN custtrxl.global_attribute_category
1464 IN ( 'JL.BR.ARXTWMAI.Additional Info',
1465 'JL.CO.ARXTWMAI.LINES',
1466 'JL.AR.ARXTWMAI.LINES')
1467 THEN
1468 DECODE(LTRIM(custtrxl.global_attribute11,'-.0123456789'),
1469 NULL,TO_NUMBER(custtrxl.global_attribute11)*
1470 NVL(custtrx.exchange_rate,1),
1471 NULL)
1472 ELSE
1473 custtrxl.taxable_amount*
1474 NVL(custtrx.exchange_rate,1)
1475 END) TAXABLE_AMT_TAX_CURR,
1476
1477 (CASE
1478 WHEN custtrxl.global_attribute_category
1479 IN ( 'JL.BR.ARXTWMAI.Additional Info',
1480 'JL.CO.ARXTWMAI.LINES',
1481 'JL.AR.ARXTWMAI.LINES')
1482 THEN
1483 DECODE(LTRIM(custtrxl.global_attribute11,'-.0123456789'),
1484 NULL,TO_NUMBER(custtrxl.global_attribute11)*
1485 NVL(custtrx.exchange_rate,1),
1486 NULL)
1487 ELSE
1488 custtrxl.taxable_amount*
1489 NVL(custtrx.exchange_rate,1)
1490 END) TAXABLE_AMT_FUNCL_CURR,
1491
1495 (CASE
1492 --NULL ORIG_TAXABLE_AMT,
1493 --NULL ORIG_TAXABLE_AMT_TAX_CURR,
1494
1496 WHEN custtrxl.global_attribute_category
1497 IN ( 'JL.BR.ARXTWMAI.Additional Info',
1498 'JL.CO.ARXTWMAI.LINES',
1499 'JL.AR.ARXTWMAI.LINES')
1500 THEN
1501 DECODE(LTRIM(custtrxl.global_attribute20,'-.0123456789'),
1502 NULL,TO_NUMBER(custtrxl.global_attribute20),
1503 NULL)
1504 END) CAL_TAX_AMT,
1505
1506 (CASE
1507 WHEN custtrxl.global_attribute_category
1508 IN ( 'JL.BR.ARXTWMAI.Additional Info',
1509 'JL.CO.ARXTWMAI.LINES',
1510 'JL.AR.ARXTWMAI.LINES')
1511 THEN
1512 DECODE(LTRIM(custtrxl.global_attribute20,'-.0123456789'),
1513 NULL,TO_NUMBER(custtrxl.global_attribute20)*
1514 NVL(custtrx.EXCHANGE_RATE,1),
1515 NULL)
1516 END) CAL_TAX_AMT_TAX_CURR,
1517
1518 (CASE
1519 WHEN custtrxl.global_attribute_category
1520 IN ( 'JL.BR.ARXTWMAI.Additional Info',
1521 'JL.CO.ARXTWMAI.LINES',
1522 'JL.AR.ARXTWMAI.LINES')
1523 THEN
1524 DECODE(LTRIM(custtrxl.global_attribute20,'-.0123456789'),
1525 NULL,TO_NUMBER(custtrxl.global_attribute20)*
1526 NVL(custtrx.EXCHANGE_RATE,1),
1527 NULL)
1528 END) CAL_TAX_AMT_FUNCL_CURR,
1529
1530 --NULL ORIG_TAX_AMT,
1531 --NULL ORIG_TAX_AMT_TAX_CURR,
1532 --NULL REC_TAX_AMT,
1533 --NULL REC_TAX_AMT_TAX_CURR,
1534 --NULL REC_TAX_AMT_FUNCL_CURR,
1535 --NULL NREC_TAX_AMT,
1536 --NULL NREC_TAX_AMT_TAX_CURR,
1537 --NULL NREC_TAX_AMT_FUNCL_CURR,
1538 custtrxl.TAX_EXEMPTION_ID TAX_EXEMPTION_ID,
1539 --NULL TAX_RATE_BEFORE_EXEMPTION,
1540 --NULL TAX_RATE_NAME_BEFORE_EXEMPTION,
1541 --NULL EXEMPT_RATE_MODIFIER,
1542 custtrxl.item_exception_rate_id TAX_EXCEPTION_ID,
1543 DECODE(rates.rate_type_code,
1544 'PERCENTAGE', rates.percentage_rate,
1545 'QUANTITY', rates.quantity_rate,
1546 NULL) TAX_RATE_BEFORE_EXCEPTION,
1547 --NULL TAX_RATE_NAME_BEFORE_EXCEPTION,
1548 --NULL EXCEPTION_RATE,
1549 --'N' TAX_APPORTIONMENT_FLAG,
1550 -- DECODE(vat.taxable_basis,
1551 -- 'AFTER_EPD', 'STANDARD_TB_DISCOUNT',
1552 -- 'QUANTITY', 'STANDARD_QUANTITY',
1553 -- 'STANDARD_TB') TAXABLE_BASIS_FORMULA,
1554 -- 'STANDARD_TC' TAX_CALCULATION_FORMULA,
1555 NVL(rates.taxable_basis_formula_code,
1556 taxes.def_taxable_basis_formula) TAXABLE_BASIS_FORMULA,
1557 NVL(taxes.def_tax_calc_formula,
1558 'STANDARD_TC') TAX_CALCULATION_FORMULA,
1559 --'N' CANCEL_FLAG,
1560 --'N' PURGE_FLAG,
1561 --'N' DELETE_FLAG,
1562 --'N' SELF_ASSESSED_FLAG,
1563 --'N' OVERRIDDEN_FLAG,
1564 --'N' MANUALLY_ENTERED_FLAG,
1565 --'N' REPORTING_ONLY_FLAG,
1566 --'N' FREEZE_UNTIL_OVERRIDDEN_FLAG,
1567 --'N' COPIED_FROM_OTHER_DOC_FLAG,
1568 --'N' RECALC_REQUIRED_FLAG,
1569 --'N' SETTLEMENT_FLAG,
1570 --'N' ITEM_DIST_CHANGED_FLAG,
1571 --'N' ASSOCIATED_CHILD_FROZEN_FLAG,
1572 DECODE(memoline.line_type, 'TAX', 'Y', 'N') TAX_ONLY_LINE_FLAG,
1573 --'N' COMPOUNDING_DEP_TAX_FLAG,
1574 --'N' ENFORCE_FROM_NATURAL_ACCT_FLAG,
1575 --'N' COMPOUNDING_TAX_MISS_FLAG,
1576 --'N' SYNC_WITH_PRVDR_FLAG,
1577 --NULL LAST_MANUAL_ENTRY,
1578 decode(arsysparam.tax_database_view_set,'_A',2,'_V',1, NULL)
1579 TAX_PROVIDER_ID,
1580 --NULL REPORTING_PERIOD_ID,
1581 --NULL LEGAL_MESSAGE_APPL_2,
1582 --NULL LEGAL_MESSAGE_STATUS,
1586 --NULL LEGAL_MESSAGE_THRESHOLD,
1583 --NULL LEGAL_MESSAGE_RATE,
1584 --NULL LEGAL_MESSAGE_BASIS,
1585 --NULL LEGAL_MESSAGE_CALC,
1587 --NULL LEGAL_MESSAGE_POS,
1588 --NULL LEGAL_MESSAGE_TRN,
1589 --NULL LEGAL_MESSAGE_EXMPT,
1590 --NULL LEGAL_MESSAGE_EXCPT,
1591 --NULL TAX_REGIME_TEMPLATE_ID,
1592 --NULL TAX_APPLICABILITY_RESULT_ID,
1593 --NULL DIRECT_RATE_RESULT_ID,
1594 --NULL STATUS_RESULT_ID,
1595 --NULL RATE_RESULT_ID,
1596 --NULL BASIS_RESULT_ID,
1597 --NULL THRESH_RESULT_ID,
1598 --NULL CALC_RESULT_ID,
1599 --NULL TAX_REG_NUM_DET_RESULT_ID,
1600 --NULL EVAL_EXMPT_RESULT_ID,
1601 --NULL EVAL_EXCPT_RESULT_ID,
1602 --NULL TAX_HOLD_CODE,
1603 --NULL TAX_HOLD_RELEASED_CODE,
1604 --NULL PRD_TOTAL_TAX_AMT,
1605 --NULL PRD_TOTAL_TAX_AMT_TAX_CURR,
1606 --NULL PRD_TOTAL_TAX_AMT_FUNCL_CURR,
1607 custtrxl.GLOBAL_ATTRIBUTE8 LEGAL_JUSTIFICATION_TEXT1,
1608 custtrxl.GLOBAL_ATTRIBUTE9 LEGAL_JUSTIFICATION_TEXT2,
1609 custtrxl.GLOBAL_ATTRIBUTE10 LEGAL_JUSTIFICATION_TEXT3,
1610 --NULL REPORTING_CURRENCY_CODE,
1611 --NULL LINE_ASSESSABLE_VALUE,
1612 --NULL TRX_LINE_INDEX,
1613 --NULL OFFSET_TAX_RATE_CODE,
1614 --NULL PRORATION_CODE,
1615 --NULL OTHER_DOC_SOURCE,
1616 --NULL MRC_LINK_TO_TAX_LINE_ID,
1617 --NULL APPLIED_TO_TRX_NUMBER,
1618 --NULL INTERFACE_TAX_LINE_ID,
1619 --NULL TAXING_JURIS_GEOGRAPHY_ID,
1620 decode(arsysparam.tax_database_view_Set ,
1621 '_A',decode(custtrxl.global_attribute1,'ALL',
1622 custtrxl.global_Attribute2,null),
1623 '_V',decode(custtrxl.global_attribute1,'ALL',
1624 custtrxl.global_Attribute2,null),
1625 NULL) numeric1,
1626 decode(arsysparam.tax_database_view_Set ,
1627 '_A',decode(custtrxl.global_attribute1,'ALL',
1628 custtrxl.global_Attribute4,null),
1629 '_V',decode(custtrxl.global_attribute1,'ALL',
1630 custtrxl.global_Attribute4,null),
1631 NULL) numeric2,
1632 decode(arsysparam.tax_database_view_Set ,
1633 '_A',decode(custtrxl.global_attribute1,'ALL',
1634 custtrxl.global_Attribute6,null),
1635 '_V',decode(custtrxl.global_attribute1,'ALL',
1636 custtrxl.global_Attribute6,null),
1637 NULL) numeric3,
1638 decode(arsysparam.tax_database_view_Set,
1639 '_A',
1640 decode(custtrxl.global_attribute1,'ALL',
1641 to_number(substrb(custtrxl.global_Attribute12,1,
1642 instrb(custtrxl.global_Attribute12,'|',1,1)-1)),
1643 'STATE',
1644 to_number(substrb(custtrxl.global_Attribute12,1,
1645 instrb(custtrxl.global_Attribute12,'|',1,1)-1)),
1646 NULL),
1647 '_V',
1648 decode(custtrxl.global_attribute1,'ALL',
1649 to_number(substrb(custtrxl.global_Attribute12,1,
1650 instrb(custtrxl.global_Attribute12,'|',1,1)-1)),
1651 'STATE',
1652 to_number(substrb(custtrxl.global_Attribute12,1,
1653 instrb(custtrxl.global_Attribute12,'|',1,1)-1)),
1654 NULL)
1655 ,NULL) numeric4,
1656
1657 DECODE(custtrxl.line_type,
1658 'TAX', custtrxl.previous_customer_trx_line_id,
1659 NULL) ADJUSTED_DOC_TAX_LINE_ID,
1660 custtrxl.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,
1661 custtrxl.ATTRIBUTE1 ATTRIBUTE1,
1662 custtrxl.ATTRIBUTE2 ATTRIBUTE2,
1663 custtrxl.ATTRIBUTE3 ATTRIBUTE3,
1664 custtrxl.ATTRIBUTE4 ATTRIBUTE4,
1668 custtrxl.ATTRIBUTE8 ATTRIBUTE8,
1665 custtrxl.ATTRIBUTE5 ATTRIBUTE5,
1666 custtrxl.ATTRIBUTE6 ATTRIBUTE6,
1667 custtrxl.ATTRIBUTE7 ATTRIBUTE7,
1669 custtrxl.ATTRIBUTE9 ATTRIBUTE9,
1670 custtrxl.ATTRIBUTE10 ATTRIBUTE10,
1671 custtrxl.ATTRIBUTE11 ATTRIBUTE11,
1672 custtrxl.ATTRIBUTE12 ATTRIBUTE12,
1673 custtrxl.ATTRIBUTE13 ATTRIBUTE13,
1674 custtrxl.ATTRIBUTE14 ATTRIBUTE14,
1675 custtrxl.ATTRIBUTE15 ATTRIBUTE15,
1676 custtrxl.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY,
1677 custtrxl.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1,
1678 custtrxl.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2,
1679 custtrxl.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3,
1680 custtrxl.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4,
1681 custtrxl.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5,
1682 custtrxl.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6,
1683 custtrxl.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7,
1684 custtrxl.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8,
1685 custtrxl.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9,
1686 custtrxl.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10,
1687 custtrxl.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11,
1688 custtrxl.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12,
1689 custtrxl.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13,
1690 custtrxl.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14,
1691 custtrxl.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15,
1692 custtrxl.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16,
1693 custtrxl.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17,
1694 custtrxl.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18,
1695 custtrxl.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19,
1696 custtrxl.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20,
1697 --'N' MULTIPLE_JURISDICTIONS_FLAG,
1698 SYSDATE CREATION_DATE,
1699 1 CREATED_BY,
1700 SYSDATE LAST_UPDATE_DATE,
1701 1 LAST_UPDATED_BY,
1702 0 LAST_UPDATE_LOGIN,
1703 DECODE(custtrx.complete_flag,
1704 'Y', '111111111111111',
1705 '000000000000000') LEGAL_REPORTING_STATUS,
1706 DECODE(vat.tax_type,
1707 'LOCATION', NULL,
1708 custtrxl.vat_tax_id) ACCOUNT_SOURCE_TAX_RATE_ID
1709
1710 FROM RA_CUSTOMER_TRX_ALL custtrx,
1711 AR_SYSTEM_PARAMETERS_ALL arsysparam,
1712 RA_CUST_TRX_TYPES_ALL types,
1713 FND_CURRENCIES fndcurr,
1714 FND_DOCUMENT_SEQUENCES fds,
1715 ZX_PARTY_TAX_PROFILE ptp,
1716 RA_BATCH_SOURCES_ALL rbs,
1717 RA_CUSTOMER_TRX_ALL custtrx_prev,
1718 RA_CUSTOMER_TRX_LINES_ALL custtrxl,
1719 AR_VAT_TAX_ALL_B vat,
1720 ZX_RATES_B rates ,
1721 RA_CUSTOMER_TRX_LINES_ALL custtrxll, -- retrieve the trx line for tax lines
1722 AR_MEMO_LINES_ALL_B memoline,
1723 ZX_REGIMES_B regimes,
1724 ZX_TAXES_B taxes,
1725 ZX_STATUS_B status
1726 WHERE custtrx.customer_trx_id = p_upg_trx_info_rec.trx_id
1727 AND custtrx.customer_trx_id = custtrxl.customer_trx_id
1728 AND custtrx.previous_customer_trx_id = custtrx_prev.customer_trx_id(+)
1729 AND (case when (custtrxl.line_type IN ('LINE' ,'CB')) then custtrxl.customer_trx_line_id
1730 when (custtrxl.line_type = 'TAX') then custtrxl.link_to_cust_trx_line_id
1731 end
1732 ) = custtrxll.customer_trx_line_id
1733 AND ((custtrxl.line_type = 'TAX' AND custtrxll.line_type = 'LINE')
1734 OR
1735 (custtrxl.line_type <> 'TAX')
1736 )
1737 AND custtrx.cust_trx_type_id = types.cust_trx_type_id
1738 AND types.type in ('INV','CM', 'DM')
1739 AND decode(l_multi_org_flag,'N',l_org_id, custtrx.org_id) =
1740 decode(l_multi_org_flag,'N',l_org_id, types.org_id)
1741 AND custtrx.invoice_currency_code = fndcurr.currency_code
1742 AND custtrx.doc_sequence_id = fds.doc_sequence_id (+)
1743 AND ptp.party_id = decode(l_multi_org_flag,'N',l_org_id, custtrx.org_id)
1744 AND ptp.party_type_code = 'OU'
1745 AND custtrx.batch_source_id = rbs.batch_source_id(+)
1746 AND decode(l_multi_org_flag,'N',l_org_id, custtrx.org_id) =
1747 decode(l_multi_org_flag,'N',l_org_id, rbs.org_id(+))
1748 AND custtrxl.vat_tax_id = vat.vat_tax_id(+)
1749 AND custtrx.org_id = arsysparam.org_id
1750 AND custtrxl.vat_Tax_id = rates.tax_rate_id(+)
1751 AND custtrxll.memo_line_id = memoline.memo_line_id(+)
1755 AND rates.tax = taxes.tax(+)
1752 AND decode(l_multi_org_flag,'N',l_org_id, custtrxll.org_id) = decode(l_multi_org_flag,'N',l_org_id, memoline.org_id(+))
1753 AND rates.tax_regime_code = regimes.tax_regime_code(+)
1754 AND rates.tax_regime_code = taxes.tax_regime_code(+)
1756 AND rates.content_owner_id = taxes.content_owner_id(+)
1757 AND rates.tax_regime_code = status.tax_regime_code(+)
1758 AND rates.tax = status.tax(+)
1759 AND rates.tax_status_code = status.tax_status_code(+)
1760 AND rates.content_owner_id = status.content_owner_id(+)
1761 AND NVL(arsysparam.tax_code, '!') <> 'Localization'
1762 AND NOT EXISTS
1763 (SELECT 1 FROM zx_lines_det_factors zxl
1764 WHERE zxl.APPLICATION_ID = 222
1765 AND zxl.EVENT_CLASS_CODE = DECODE(types.type,
1766 'INV','INVOICE',
1767 'CM', 'CREDIT_MEMO',
1768 'DM', 'DEBIT_MEMO',
1769 'NONE')
1770 AND zxl.ENTITY_CODE = 'TRANSACTIONS'
1771 AND zxl.TRX_ID = p_upg_trx_info_rec.trx_id
1772 AND zxl.TRX_LINE_ID = DECODE(custtrxl.line_type,
1773 'LINE',custtrxl.customer_trx_line_id,
1774 'TAX', custtrxl.link_to_cust_trx_line_id)
1775 AND zxl.TRX_LEVEL_TYPE = 'LINE'
1776 );
1777 IF (g_level_procedure >= g_current_runtime_level ) THEN
1778 FND_LOG.STRING(g_level_procedure,
1779 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AR_PKG.upgrade_trx_on_fly_ar.BEGIN',
1780 'ZX_ON_FLY_TRX_UPGRADE_AR_PKG.upgrade_trx_on_fly_ar(+)');
1781 END IF;
1782 EXCEPTION
1783 WHEN OTHERS THEN
1784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1785 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1786 FND_LOG.STRING(g_level_unexpected,
1787 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AR_PKG.upgrade_trx_on_fly_ar',
1788 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1789 FND_LOG.STRING(g_level_unexpected,
1790 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AR_PKG.upgrade_trx_on_fly_ar.END',
1791 'ZX_ON_FLY_TRX_UPGRADE_AR_PKG.upgrade_trx_on_fly_ar(-)');
1792 END IF;
1793
1794 END upgrade_trx_on_fly_ar;
1795
1796 -------------------------------------------------------------------------------
1797 -- PUBLIC PROCEDURE
1798 -- upgrade_trx_on_fly_blk_ar
1799 --
1800 -- DESCRIPTION
1801 -- handle bulk on the fly migration for AR, called from validate and default API
1802 --
1803 -------------------------------------------------------------------------------
1804 PROCEDURE upgrade_trx_on_fly_blk_ar(
1805 x_return_status OUT NOCOPY VARCHAR2
1806 ) AS
1807 l_multi_org_flag VARCHAR2(1);
1808 l_org_id NUMBER;
1809 l_inv_installed VARCHAR2(1);
1810 l_inv_flag VARCHAR2(1);
1811 l_industry VARCHAR2(10);
1812 l_fnd_return BOOLEAN;
1813
1814 BEGIN
1815 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1816
1817 IF (g_level_procedure >= g_current_runtime_level ) THEN
1818 FND_LOG.STRING(g_level_procedure,
1819 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AR_PKG.upgrade_trx_on_fly_blk_ar.BEGIN',
1820 'ZX_ON_FLY_TRX_UPGRADE_AR_PKG.upgrade_trx_on_fly_blk_ar(+)');
1821 END IF;
1822
1823 x_return_status := FND_API.G_RET_STS_SUCCESS;
1824 SELECT NVL(multi_org_flag, 'N') INTO l_multi_org_flag FROM FND_PRODUCT_GROUPS;
1825 -- for single org environment, get value of org_id from profile
1826 IF l_multi_org_flag = 'N' THEN
1827 FND_PROFILE.GET('ORG_ID',l_org_id);
1828 IF l_org_id is NULL THEN
1829 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1830 FND_LOG.STRING(g_level_unexpected,
1831 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AR_PKG.upgrade_trx_on_fly_blk_ar',
1832 'Current envionment is a Single Org environment,'||
1833 ' but peofile ORG_ID is not set up');
1834 END IF;
1835
1836 END IF;
1837 END IF;
1838
1839 l_fnd_return := FND_INSTALLATION.GET(401,401, l_inv_flag, l_industry);
1840
1841 if (l_inv_flag = 'I') then
1842 l_inv_installed := 'Y';
1843 else
1844 l_inv_installed := 'N';
1845 end if;
1846
1847
1848 INSERT ALL
1849 WHEN trx_line_type IN ('LINE' ,'CB') THEN
1850 INTO ZX_LINES_DET_FACTORS(
1851 INTERNAL_ORGANIZATION_ID
1852 ,APPLICATION_ID
1853 ,ENTITY_CODE
1854 ,EVENT_CLASS_CODE
1855 ,EVENT_CLASS_MAPPING_ID
1856 ,EVENT_TYPE_CODE
1857 ,DOC_EVENT_STATUS
1858 ,LINE_LEVEL_ACTION
1859 ,TRX_ID
1860 ,TRX_LINE_ID
1861 ,TRX_LEVEL_TYPE
1862 ,TRX_DATE
1863 --,TRX_DOC_REVISION
1864 ,LEDGER_ID
1865 ,TRX_CURRENCY_CODE
1866 ,CURRENCY_CONVERSION_DATE
1867 ,CURRENCY_CONVERSION_RATE
1868 ,CURRENCY_CONVERSION_TYPE
1869 ,MINIMUM_ACCOUNTABLE_UNIT
1870 ,PRECISION
1871 ,LEGAL_ENTITY_ID
1872 --,ESTABLISHMENT_ID
1873 ,RECEIVABLES_TRX_TYPE_ID
1874 ,DEFAULT_TAXATION_COUNTRY
1875 ,TRX_NUMBER
1879 --,TRX_COMMUNICATED_DATE
1876 ,TRX_LINE_NUMBER
1877 ,TRX_LINE_DESCRIPTION
1878 --,TRX_DESCRIPTION
1880 ,BATCH_SOURCE_ID
1881 ,BATCH_SOURCE_NAME
1882 ,DOC_SEQ_ID
1883 ,DOC_SEQ_NAME
1884 ,DOC_SEQ_VALUE
1885 ,TRX_DUE_DATE
1886 ,TRX_TYPE_DESCRIPTION
1887 ,DOCUMENT_SUB_TYPE
1888 --,SUPPLIER_TAX_INVOICE_NUMBER
1889 --,SUPPLIER_TAX_INVOICE_DATE
1890 --,SUPPLIER_EXCHANGE_RATE
1891 ,TAX_INVOICE_DATE
1892 ,TAX_INVOICE_NUMBER
1893 ,FIRST_PTY_ORG_ID
1894 ,TAX_EVENT_CLASS_CODE
1895 ,TAX_EVENT_TYPE_CODE
1896 --,LINE_INTENDED_USE
1897 ,TRX_LINE_TYPE
1898 --,TRX_SHIPPING_DATE
1899 --,TRX_RECEIPT_DATE
1900 --,TRX_SIC_CODE
1901 ,FOB_POINT
1902 ,TRX_WAYBILL_NUMBER
1903 ,PRODUCT_ID
1904 ,PRODUCT_FISC_CLASSIFICATION
1905 ,PRODUCT_ORG_ID
1906 ,UOM_CODE
1907 --,PRODUCT_TYPE
1908 --,PRODUCT_CODE
1909 ,PRODUCT_CATEGORY
1910 ,PRODUCT_DESCRIPTION
1911 ,USER_DEFINED_FISC_CLASS
1912 ,LINE_AMT
1913 ,TRX_LINE_QUANTITY
1914 --,CASH_DISCOUNT
1915 --,VOLUME_DISCOUNT
1916 --,TRADING_DISCOUNT
1917 --,TRANSFER_CHARGE
1918 --,TRANSPORTATION_CHARGE
1919 --,INSURANCE_CHARGE
1920 --,OTHER_CHARGE
1921 --,ASSESSABLE_VALUE
1922 --,ASSET_FLAG
1923 --,ASSET_NUMBER
1924 ,ASSET_ACCUM_DEPRECIATION
1925 --,ASSET_TYPE
1926 ,ASSET_COST
1927 ,RELATED_DOC_APPLICATION_ID
1928 --,RELATED_DOC_ENTITY_CODE
1929 --,RELATED_DOC_EVENT_CLASS_CODE
1930 ,RELATED_DOC_TRX_ID
1931 --,RELATED_DOC_NUMBER
1932 --,RELATED_DOC_DATE
1933 ,ADJUSTED_DOC_APPLICATION_ID
1934 ,ADJUSTED_DOC_ENTITY_CODE
1935 --,ADJUSTED_DOC_EVENT_CLASS_CODE
1936 ,ADJUSTED_DOC_TRX_ID
1937 ,ADJUSTED_DOC_LINE_ID
1938 ,ADJUSTED_DOC_NUMBER
1939 ,ADJUSTED_DOC_DATE
1940 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
1941 --,REF_DOC_APPLICATION_ID
1942 --,REF_DOC_ENTITY_CODE
1943 --,REF_DOC_EVENT_CLASS_CODE
1944 --,REF_DOC_TRX_ID
1945 --,REF_DOC_LINE_ID
1946 --,REF_DOC_LINE_QUANTITY
1947 --,REF_DOC_TRX_LEVEL_TYPE
1948 ,TRX_BUSINESS_CATEGORY
1949 ,EXEMPT_CERTIFICATE_NUMBER
1950 --,EXEMPT_REASON
1951 ,EXEMPTION_CONTROL_FLAG
1952 ,EXEMPT_REASON_CODE
1953 ,HISTORICAL_FLAG
1954 ,TRX_LINE_GL_DATE
1955 ,LINE_AMT_INCLUDES_TAX_FLAG
1956 --,ACCOUNT_CCID
1957 --,ACCOUNT_STRING
1958 --,SHIP_TO_LOCATION_ID
1959 --,SHIP_FROM_LOCATION_ID
1960 --,POA_LOCATION_ID
1961 --,POO_LOCATION_ID
1962 --,BILL_TO_LOCATION_ID
1963 --,BILL_FROM_LOCATION_ID
1964 --,PAYING_LOCATION_ID
1965 --,OWN_HQ_LOCATION_ID
1966 --,TRADING_HQ_LOCATION_ID
1967 --,POC_LOCATION_ID
1968 --,POI_LOCATION_ID
1969 --,POD_LOCATION_ID
1970 --,TITLE_TRANSFER_LOCATION_ID
1971 ,CTRL_HDR_TX_APPL_FLAG
1972 --,CTRL_TOTAL_LINE_TX_AMT
1973 --,CTRL_TOTAL_HDR_TX_AMT
1974 ,LINE_CLASS
1975 ,TRX_LINE_DATE
1976 --,INPUT_TAX_CLASSIFICATION_CODE
1977 ,OUTPUT_TAX_CLASSIFICATION_CODE
1978 --,INTERNAL_ORG_LOCATION_ID
1979 --,PORT_OF_ENTRY_CODE
1980 ,TAX_REPORTING_FLAG
1981 ,TAX_AMT_INCLUDED_FLAG
1982 ,COMPOUNDING_TAX_FLAG
1983 --,EVENT_ID
1984 ,THRESHOLD_INDICATOR_FLAG
1985 --,PROVNL_TAX_DETERMINATION_DATE
1986 ,UNIT_PRICE
1987 ,SHIP_TO_CUST_ACCT_SITE_USE_ID
1988 ,BILL_TO_CUST_ACCT_SITE_USE_ID
1989 ,TRX_BATCH_ID
1990 --,START_EXPENSE_DATE
1991 --,SOURCE_APPLICATION_ID
1992 --,SOURCE_ENTITY_CODE
1993 --,SOURCE_EVENT_CLASS_CODE
1994 --,SOURCE_TRX_ID
1995 --,SOURCE_LINE_ID
1996 --,SOURCE_TRX_LEVEL_TYPE
1997 ,RECORD_TYPE_CODE
1998 ,INCLUSIVE_TAX_OVERRIDE_FLAG
1999 ,TAX_PROCESSING_COMPLETED_FLAG
2000 ,OBJECT_VERSION_NUMBER
2001 ,APPLICATION_DOC_STATUS
2002 ,USER_UPD_DET_FACTORS_FLAG
2003 --,SOURCE_TAX_LINE_ID
2004 --,REVERSED_APPLN_ID
2005 --,REVERSED_ENTITY_CODE
2006 --,REVERSED_EVNT_CLS_CODE
2007 --,REVERSED_TRX_ID
2008 --,REVERSED_TRX_LEVEL_TYPE
2009 --,REVERSED_TRX_LINE_ID
2010 --,TAX_CALCULATION_DONE_FLAG
2011 ,PARTNER_MIGRATED_FLAG
2012 ,SHIP_THIRD_PTY_ACCT_SITE_ID
2013 ,BILL_THIRD_PTY_ACCT_SITE_ID
2014 ,SHIP_THIRD_PTY_ACCT_ID
2015 ,BILL_THIRD_PTY_ACCT_ID
2016 --,INTERFACE_ENTITY_CODE
2017 --,INTERFACE_LINE_ID
2018 --,HISTORICAL_TAX_CODE_ID
2019 --,ICX_SESSION_ID
2020 --,TRX_LINE_CURRENCY_CODE
2024 --,TRX_LINE_MAU
2021 --,TRX_LINE_CURRENCY_CONV_RATE
2022 --,TRX_LINE_CURRENCY_CONV_DATE
2023 --,TRX_LINE_PRECISION
2025 --,TRX_LINE_CURRENCY_CONV_TYPE
2026 ,CREATION_DATE
2027 ,CREATED_BY
2028 ,LAST_UPDATE_DATE
2029 ,LAST_UPDATED_BY
2030 ,LAST_UPDATE_LOGIN
2031 )
2032 VALUES (
2033 INTERNAL_ORGANIZATION_ID
2034 ,APPLICATION_ID
2035 ,ENTITY_CODE
2036 ,EVENT_CLASS_CODE
2037 ,EVENT_CLASS_MAPPING_ID
2038 ,EVENT_TYPE_CODE
2039 ,DOC_EVENT_STATUS
2040 ,LINE_LEVEL_ACTION
2041 ,TRX_ID
2042 ,TRX_LINE_ID
2043 ,TRX_LEVEL_TYPE
2044 ,TRX_DATE
2045 --,TRX_DOC_REVISION
2046 ,LEDGER_ID
2047 ,TRX_CURRENCY_CODE
2048 ,CURRENCY_CONVERSION_DATE
2049 ,CURRENCY_CONVERSION_RATE
2050 ,CURRENCY_CONVERSION_TYPE
2051 ,MINIMUM_ACCOUNTABLE_UNIT
2052 ,PRECISION
2053 ,LEGAL_ENTITY_ID
2054 --,ESTABLISHMENT_ID
2055 ,RECEIVABLES_TRX_TYPE_ID
2056 ,DEFAULT_TAXATION_COUNTRY
2057 ,TRX_NUMBER
2058 ,TRX_LINE_NUMBER
2059 ,TRX_LINE_DESCRIPTION
2060 --,TRX_DESCRIPTION
2061 --,TRX_COMMUNICATED_DATE
2062 ,BATCH_SOURCE_ID
2063 ,BATCH_SOURCE_NAME
2064 ,DOC_SEQ_ID
2065 ,DOC_SEQ_NAME
2066 ,DOC_SEQ_VALUE
2067 ,TRX_DUE_DATE
2068 ,TRX_TYPE_DESCRIPTION
2069 ,DOCUMENT_SUB_TYPE
2070 --,SUPPLIER_TAX_INVOICE_NUMBER
2071 --,SUPPLIER_TAX_INVOICE_DATE
2072 --,SUPPLIER_EXCHANGE_RATE
2073 ,TAX_INVOICE_DATE
2074 ,TAX_INVOICE_NUMBER
2075 ,FIRST_PTY_ORG_ID
2076 ,TAX_EVENT_CLASS_CODE
2077 ,TAX_EVENT_TYPE_CODE
2078 --,LINE_INTENDED_USE
2079 ,TRX_LINE_TYPE
2080 --,TRX_SHIPPING_DATE
2081 --,TRX_RECEIPT_DATE
2082 --,TRX_SIC_CODE
2083 ,FOB_POINT
2084 ,TRX_WAYBILL_NUMBER
2085 ,PRODUCT_ID
2086 ,PRODUCT_FISC_CLASSIFICATION
2087 ,PRODUCT_ORG_ID
2088 ,UOM_CODE
2089 --,PRODUCT_TYPE
2090 --,PRODUCT_CODE
2091 ,PRODUCT_CATEGORY
2092 ,PRODUCT_DESCRIPTION
2093 ,USER_DEFINED_FISC_CLASS
2094 ,LINE_AMT
2095 ,TRX_LINE_QUANTITY
2096 --,CASH_DISCOUNT
2097 --,VOLUME_DISCOUNT
2098 --,TRADING_DISCOUNT
2099 --,TRANSFER_CHARGE
2100 --,TRANSPORTATION_CHARGE
2101 --,INSURANCE_CHARGE
2102 --,OTHER_CHARGE
2103 --,ASSESSABLE_VALUE
2104 --,ASSET_FLAG
2105 --,ASSET_NUMBER
2106 ,ASSET_ACCUM_DEPRECIATION
2107 --,ASSET_TYPE
2108 ,ASSET_COST
2109 ,RELATED_DOC_APPLICATION_ID
2110 --,RELATED_DOC_ENTITY_CODE
2111 --,RELATED_DOC_EVENT_CLASS_CODE
2112 ,RELATED_DOC_TRX_ID
2113 --,RELATED_DOC_NUMBER
2114 --,RELATED_DOC_DATE
2115 ,ADJUSTED_DOC_APPLICATION_ID
2116 ,ADJUSTED_DOC_ENTITY_CODE
2117 --,ADJUSTED_DOC_EVENT_CLASS_CODE
2118 ,ADJUSTED_DOC_TRX_ID
2119 ,ADJUSTED_DOC_LINE_ID
2120 ,ADJUSTED_DOC_NUMBER
2121 ,ADJUSTED_DOC_DATE
2122 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
2123 --,REF_DOC_APPLICATION_ID
2124 --,REF_DOC_ENTITY_CODE
2125 --,REF_DOC_EVENT_CLASS_CODE
2126 --,REF_DOC_TRX_ID
2127 --,REF_DOC_LINE_ID
2128 --,REF_DOC_LINE_QUANTITY
2129 --,REF_DOC_TRX_LEVEL_TYPE
2130 ,TRX_BUSINESS_CATEGORY
2131 ,EXEMPT_CERTIFICATE_NUMBER
2132 --,EXEMPT_REASON
2133 ,EXEMPTION_CONTROL_FLAG
2134 ,EXEMPT_REASON_CODE
2135 ,'Y' --HISTORICAL_FLAG
2136 ,TRX_LINE_GL_DATE
2137 ,'N' --LINE_AMT_INCLUDES_TAX_FLAG
2138 --,ACCOUNT_CCID
2139 --,ACCOUNT_STRING
2140 --,SHIP_TO_LOCATION_ID
2141 --,SHIP_FROM_LOCATION_ID
2142 --,POA_LOCATION_ID
2143 --,POO_LOCATION_ID
2144 --,BILL_TO_LOCATION_ID
2145 --,BILL_FROM_LOCATION_ID
2146 --,PAYING_LOCATION_ID
2147 --,OWN_HQ_LOCATION_ID
2148 --,TRADING_HQ_LOCATION_ID
2149 --,POC_LOCATION_ID
2150 --,POI_LOCATION_ID
2151 --,POD_LOCATION_ID
2152 --,TITLE_TRANSFER_LOCATION_ID
2153 ,'N' --CTRL_HDR_TX_APPL_FLAG
2154 --,CTRL_TOTAL_LINE_TX_AMT
2155 --,CTRL_TOTAL_HDR_TX_AMT
2156 ,LINE_CLASS
2157 ,TRX_LINE_DATE
2158 --,INPUT_TAX_CLASSIFICATION_CODE
2159 ,OUTPUT_TAX_CLASSIFICATION_CODE
2160 --,INTERNAL_ORG_LOCATION_ID
2161 --,PORT_OF_ENTRY_CODE
2162 ,'Y' --TAX_REPORTING_FLAG
2163 ,'N' --TAX_AMT_INCLUDED_FLAG
2164 ,'N' --COMPOUNDING_TAX_FLAG
2165 --,EVENT_ID
2166 ,'N' --THRESHOLD_INDICATOR_FLAG
2167 --,PROVNL_TAX_DETERMINATION_DATE
2168 ,UNIT_PRICE
2169 ,SHIP_TO_CUST_ACCT_SITE_USE_ID
2173 --,SOURCE_APPLICATION_ID
2170 ,BILL_TO_CUST_ACCT_SITE_USE_ID
2171 ,TRX_BATCH_ID
2172 --,START_EXPENSE_DATE
2174 --,SOURCE_ENTITY_CODE
2175 --,SOURCE_EVENT_CLASS_CODE
2176 --,SOURCE_TRX_ID
2177 --,SOURCE_LINE_ID
2178 --,SOURCE_TRX_LEVEL_TYPE
2179 ,'MIGRATED' --RECORD_TYPE_CODE
2180 ,'N' --INCLUSIVE_TAX_OVERRIDE_FLAG
2181 ,'N' --TAX_PROCESSING_COMPLETED_FLAG
2182 ,OBJECT_VERSION_NUMBER
2183 ,APPLICATION_DOC_STATUS
2184 ,'N' --USER_UPD_DET_FACTORS_FLAG
2185 --,SOURCE_TAX_LINE_ID
2186 --,REVERSED_APPLN_ID
2187 --,REVERSED_ENTITY_CODE
2188 --,REVERSED_EVNT_CLS_CODE
2189 --,REVERSED_TRX_ID
2190 --,REVERSED_TRX_LEVEL_TYPE
2191 --,REVERSED_TRX_LINE_ID
2192 --,TAX_CALCULATION_DONE_FLAG
2193 ,PARTNER_MIGRATED_FLAG
2194 ,SHIP_THIRD_PTY_ACCT_SITE_ID
2195 ,BILL_THIRD_PTY_ACCT_SITE_ID
2196 ,SHIP_THIRD_PTY_ACCT_ID
2197 ,BILL_THIRD_PTY_ACCT_ID
2198 --,INTERFACE_ENTITY_CODE
2199 --,INTERFACE_LINE_ID
2200 --,HISTORICAL_TAX_CODE_ID
2201 --,ICX_SESSION_ID
2202 --,TRX_LINE_CURRENCY_CODE
2203 --,TRX_LINE_CURRENCY_CONV_RATE
2204 --,TRX_LINE_CURRENCY_CONV_DATE
2205 --,TRX_LINE_PRECISION
2206 --,TRX_LINE_MAU
2207 --,TRX_LINE_CURRENCY_CONV_TYPE
2208 ,CREATION_DATE
2209 ,CREATED_BY
2210 ,LAST_UPDATE_DATE
2211 ,LAST_UPDATED_BY
2212 ,LAST_UPDATE_LOGIN
2213 )
2214 WHEN (trx_line_type = 'TAX') THEN
2215 INTO ZX_LINES (
2216 TAX_LINE_ID
2217 ,INTERNAL_ORGANIZATION_ID
2218 ,APPLICATION_ID
2219 ,ENTITY_CODE
2220 ,EVENT_CLASS_CODE
2221 ,EVENT_TYPE_CODE
2222 ,TRX_ID
2223 ,TRX_LINE_ID
2224 ,TRX_LEVEL_TYPE
2225 ,TRX_LINE_NUMBER
2226 ,DOC_EVENT_STATUS
2227 ,TAX_EVENT_CLASS_CODE
2228 ,TAX_EVENT_TYPE_CODE
2229 ,TAX_LINE_NUMBER
2230 ,CONTENT_OWNER_ID
2231 ,TAX_REGIME_ID
2232 ,TAX_REGIME_CODE
2233 ,TAX_ID
2234 ,TAX
2235 ,TAX_STATUS_ID
2236 ,TAX_STATUS_CODE
2237 ,TAX_RATE_ID
2238 ,TAX_RATE_CODE
2239 ,TAX_RATE
2240 ,TAX_RATE_TYPE
2241 ,TAX_APPORTIONMENT_LINE_NUMBER
2242 ,MRC_TAX_LINE_FLAG
2243 ,LEDGER_ID
2244 --,ESTABLISHMENT_ID
2245 ,LEGAL_ENTITY_ID
2246 --,LEGAL_ENTITY_TAX_REG_NUMBER
2247 --,HQ_ESTB_REG_NUMBER
2248 --,HQ_ESTB_PARTY_TAX_PROF_ID
2249 ,CURRENCY_CONVERSION_DATE
2250 ,CURRENCY_CONVERSION_TYPE
2251 ,CURRENCY_CONVERSION_RATE
2252 --,TAX_CURRENCY_CONVERSION_DATE
2253 --,TAX_CURRENCY_CONVERSION_TYPE
2254 --,TAX_CURRENCY_CONVERSION_RATE
2255 ,TRX_CURRENCY_CODE
2256 ,MINIMUM_ACCOUNTABLE_UNIT
2257 ,PRECISION
2258 ,TRX_NUMBER
2259 ,TRX_DATE
2260 ,UNIT_PRICE
2261 ,LINE_AMT
2262 ,TRX_LINE_QUANTITY
2263 ,TAX_BASE_MODIFIER_RATE
2264 --,REF_DOC_APPLICATION_ID
2265 --,REF_DOC_ENTITY_CODE
2266 --,REF_DOC_EVENT_CLASS_CODE
2267 --,REF_DOC_TRX_ID
2268 --,REF_DOC_LINE_ID
2269 --,REF_DOC_LINE_QUANTITY
2270 --,REF_DOC_TRX_LEVEL_TYPE
2271 --,OTHER_DOC_LINE_AMT
2272 --,OTHER_DOC_LINE_TAX_AMT
2273 --,OTHER_DOC_LINE_TAXABLE_AMT
2274 ,UNROUNDED_TAXABLE_AMT
2275 ,UNROUNDED_TAX_AMT
2276 ,RELATED_DOC_APPLICATION_ID
2277 --,RELATED_DOC_ENTITY_CODE
2278 --,RELATED_DOC_EVENT_CLASS_CODE
2279 ,RELATED_DOC_TRX_ID
2280 --,RELATED_DOC_NUMBER
2281 --,RELATED_DOC_DATE
2282 --,RELATED_DOC_TRX_LEVEL_TYPE
2283 ,ADJUSTED_DOC_APPLICATION_ID
2284 ,ADJUSTED_DOC_ENTITY_CODE
2285 --,ADJUSTED_DOC_EVENT_CLASS_CODE
2286 ,ADJUSTED_DOC_TRX_ID
2287 ,ADJUSTED_DOC_LINE_ID
2288 ,ADJUSTED_DOC_NUMBER
2289 ,ADJUSTED_DOC_DATE
2290 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
2291 --,SUMMARY_TAX_LINE_ID
2292 --,OFFSET_LINK_TO_TAX_LINE_ID
2293 ,OFFSET_FLAG
2294 ,PROCESS_FOR_RECOVERY_FLAG
2295 --,TAX_JURISDICTION_ID
2296 --,TAX_JURISDICTION_CODE
2297 --,PLACE_OF_SUPPLY
2298 ,PLACE_OF_SUPPLY_TYPE_CODE
2299 --,PLACE_OF_SUPPLY_RESULT_ID
2300 --,TAX_DATE_RULE_ID
2301 ,TAX_DATE
2302 ,TAX_DETERMINE_DATE
2303 ,TAX_POINT_DATE
2304 ,TRX_LINE_DATE
2305 ,TAX_TYPE_CODE
2306 --,TAX_CODE
2307 --,TAX_REGISTRATION_ID
2308 --,TAX_REGISTRATION_NUMBER
2309 --,REGISTRATION_PARTY_TYPE
2310 ,ROUNDING_LEVEL_CODE
2311 ,ROUNDING_RULE_CODE
2312 --,ROUNDING_LVL_PARTY_TAX_PROF_ID
2316 --,ORIG_TAX_STATUS_CODE
2313 --,ROUNDING_LVL_PARTY_TYPE
2314 ,COMPOUNDING_TAX_FLAG
2315 --,ORIG_TAX_STATUS_ID
2317 --,ORIG_TAX_RATE_ID
2318 --,ORIG_TAX_RATE_CODE
2319 --,ORIG_TAX_RATE
2320 --,ORIG_TAX_JURISDICTION_ID
2321 --,ORIG_TAX_JURISDICTION_CODE
2322 --,ORIG_TAX_AMT_INCLUDED_FLAG
2323 --,ORIG_SELF_ASSESSED_FLAG
2324 ,TAX_CURRENCY_CODE
2325 ,TAX_AMT
2326 ,TAX_AMT_TAX_CURR
2327 ,TAX_AMT_FUNCL_CURR
2328 ,TAXABLE_AMT
2329 ,TAXABLE_AMT_TAX_CURR
2330 ,TAXABLE_AMT_FUNCL_CURR
2331 --,ORIG_TAXABLE_AMT
2332 --,ORIG_TAXABLE_AMT_TAX_CURR
2333 ,CAL_TAX_AMT
2334 ,CAL_TAX_AMT_TAX_CURR
2335 ,CAL_TAX_AMT_FUNCL_CURR
2336 --,ORIG_TAX_AMT
2337 --,ORIG_TAX_AMT_TAX_CURR
2338 --,REC_TAX_AMT
2339 --,REC_TAX_AMT_TAX_CURR
2340 --,REC_TAX_AMT_FUNCL_CURR
2341 --,NREC_TAX_AMT
2342 --,NREC_TAX_AMT_TAX_CURR
2343 --,NREC_TAX_AMT_FUNCL_CURR
2344 ,TAX_EXEMPTION_ID
2345 --,TAX_RATE_BEFORE_EXEMPTION
2346 --,TAX_RATE_NAME_BEFORE_EXEMPTION
2347 --,EXEMPT_RATE_MODIFIER
2348 ,EXEMPT_CERTIFICATE_NUMBER
2349 --,EXEMPT_REASON
2350 ,EXEMPT_REASON_CODE
2351 ,TAX_EXCEPTION_ID
2352 ,TAX_RATE_BEFORE_EXCEPTION
2353 --,TAX_RATE_NAME_BEFORE_EXCEPTION
2354 --,EXCEPTION_RATE
2355 ,TAX_APPORTIONMENT_FLAG
2356 ,HISTORICAL_FLAG
2357 ,TAXABLE_BASIS_FORMULA
2358 ,TAX_CALCULATION_FORMULA
2359 ,CANCEL_FLAG
2360 ,PURGE_FLAG
2361 ,DELETE_FLAG
2362 ,TAX_AMT_INCLUDED_FLAG
2363 ,SELF_ASSESSED_FLAG
2364 ,OVERRIDDEN_FLAG
2365 ,MANUALLY_ENTERED_FLAG
2366 ,REPORTING_ONLY_FLAG
2367 ,FREEZE_UNTIL_OVERRIDDEN_FLAG
2368 ,COPIED_FROM_OTHER_DOC_FLAG
2369 ,RECALC_REQUIRED_FLAG
2370 ,SETTLEMENT_FLAG
2371 ,ITEM_DIST_CHANGED_FLAG
2372 ,ASSOCIATED_CHILD_FROZEN_FLAG
2373 ,TAX_ONLY_LINE_FLAG
2374 ,COMPOUNDING_DEP_TAX_FLAG
2375 ,ENFORCE_FROM_NATURAL_ACCT_FLAG
2376 ,COMPOUNDING_TAX_MISS_FLAG
2377 ,SYNC_WITH_PRVDR_FLAG
2378 --,LAST_MANUAL_ENTRY
2379 ,TAX_PROVIDER_ID
2380 ,RECORD_TYPE_CODE
2381 --,REPORTING_PERIOD_ID
2382 --,LEGAL_MESSAGE_APPL_2
2383 --,LEGAL_MESSAGE_STATUS
2384 --,LEGAL_MESSAGE_RATE
2385 --,LEGAL_MESSAGE_BASIS
2386 --,LEGAL_MESSAGE_CALC
2387 --,LEGAL_MESSAGE_THRESHOLD
2388 --,LEGAL_MESSAGE_POS
2389 --,LEGAL_MESSAGE_TRN
2390 --,LEGAL_MESSAGE_EXMPT
2391 --,LEGAL_MESSAGE_EXCPT
2392 --,TAX_REGIME_TEMPLATE_ID
2393 --,TAX_APPLICABILITY_RESULT_ID
2394 --,DIRECT_RATE_RESULT_ID
2395 --,STATUS_RESULT_ID
2396 --,RATE_RESULT_ID
2397 --,BASIS_RESULT_ID
2398 --,THRESH_RESULT_ID
2399 --,CALC_RESULT_ID
2400 --,TAX_REG_NUM_DET_RESULT_ID
2401 --,EVAL_EXMPT_RESULT_ID
2402 --,EVAL_EXCPT_RESULT_ID
2403 --,TAX_HOLD_CODE
2404 --,TAX_HOLD_RELEASED_CODE
2405 --,PRD_TOTAL_TAX_AMT
2406 --,PRD_TOTAL_TAX_AMT_TAX_CURR
2407 --,PRD_TOTAL_TAX_AMT_FUNCL_CURR
2408 --,INTERNAL_ORG_LOCATION_ID
2409 ,ATTRIBUTE_CATEGORY
2410 ,ATTRIBUTE1
2411 ,ATTRIBUTE2
2412 ,ATTRIBUTE3
2413 ,ATTRIBUTE4
2414 ,ATTRIBUTE5
2415 ,ATTRIBUTE6
2416 ,ATTRIBUTE7
2417 ,ATTRIBUTE8
2418 ,ATTRIBUTE9
2419 ,ATTRIBUTE10
2420 ,ATTRIBUTE11
2421 ,ATTRIBUTE12
2422 ,ATTRIBUTE13
2423 ,ATTRIBUTE14
2424 ,ATTRIBUTE15
2425 ,GLOBAL_ATTRIBUTE_CATEGORY
2426 ,GLOBAL_ATTRIBUTE1
2427 ,GLOBAL_ATTRIBUTE2
2428 ,GLOBAL_ATTRIBUTE3
2429 ,GLOBAL_ATTRIBUTE4
2430 ,GLOBAL_ATTRIBUTE5
2431 ,GLOBAL_ATTRIBUTE6
2432 ,GLOBAL_ATTRIBUTE7
2433 ,GLOBAL_ATTRIBUTE8
2434 ,GLOBAL_ATTRIBUTE9
2435 ,GLOBAL_ATTRIBUTE10
2436 ,GLOBAL_ATTRIBUTE11
2437 ,GLOBAL_ATTRIBUTE12
2438 ,GLOBAL_ATTRIBUTE13
2439 ,GLOBAL_ATTRIBUTE14
2440 ,GLOBAL_ATTRIBUTE15
2441 ,GLOBAL_ATTRIBUTE16
2442 ,GLOBAL_ATTRIBUTE17
2443 ,GLOBAL_ATTRIBUTE18
2444 ,GLOBAL_ATTRIBUTE19
2445 ,GLOBAL_ATTRIBUTE20
2446 ,LEGAL_JUSTIFICATION_TEXT1
2447 ,LEGAL_JUSTIFICATION_TEXT2
2448 ,LEGAL_JUSTIFICATION_TEXT3
2449 --,REPORTING_CURRENCY_CODE
2450 --,LINE_ASSESSABLE_VALUE
2451 --,TRX_LINE_INDEX
2452 --,OFFSET_TAX_RATE_CODE
2453 --,PRORATION_CODE
2454 --,OTHER_DOC_SOURCE
2455 --,CTRL_TOTAL_LINE_TX_AMT
2456 --,MRC_LINK_TO_TAX_LINE_ID
2457 --,APPLIED_TO_TRX_NUMBER
2461 ,NUMERIC1
2458 --,INTERFACE_ENTITY_CODE
2459 --,INTERFACE_TAX_LINE_ID
2460 --,TAXING_JURIS_GEOGRAPHY_ID
2462 ,NUMERIC2
2463 ,NUMERIC3
2464 ,NUMERIC4
2465 ,ADJUSTED_DOC_TAX_LINE_ID
2466 ,OBJECT_VERSION_NUMBER
2467 ,MULTIPLE_JURISDICTIONS_FLAG
2468 ,CREATED_BY
2469 ,CREATION_DATE
2470 ,LAST_UPDATED_BY
2471 ,LAST_UPDATE_DATE
2472 ,LAST_UPDATE_LOGIN
2473 ,LEGAL_REPORTING_STATUS
2474 )
2475 VALUES(
2476 TAX_LINE_ID
2477 ,INTERNAL_ORGANIZATION_ID
2478 ,APPLICATION_ID
2479 ,ENTITY_CODE
2480 ,EVENT_CLASS_CODE
2481 ,EVENT_TYPE_CODE
2482 ,TRX_ID
2483 ,TRX_LINE_ID
2484 ,TRX_LEVEL_TYPE
2485 ,TRX_LINE_NUMBER
2486 ,DOC_EVENT_STATUS
2487 ,TAX_EVENT_CLASS_CODE
2488 ,TAX_EVENT_TYPE_CODE
2489 ,TAX_LINE_NUMBER
2490 ,CONTENT_OWNER_ID
2491 ,TAX_REGIME_ID
2492 ,TAX_REGIME_CODE
2493 ,TAX_ID
2494 ,TAX
2495 ,TAX_STATUS_ID
2496 ,TAX_STATUS_CODE
2497 ,TAX_RATE_ID
2498 ,TAX_RATE_CODE
2499 ,TAX_RATE
2500 ,TAX_RATE_TYPE
2501 ,TAX_APPORTIONMENT_LINE_NUMBER
2502 ,'N' --MRC_TAX_LINE_FLAG
2503 ,LEDGER_ID
2504 --,ESTABLISHMENT_ID
2505 ,LEGAL_ENTITY_ID
2506 --,LEGAL_ENTITY_TAX_REG_NUMBER
2507 --,HQ_ESTB_REG_NUMBER
2508 --,HQ_ESTB_PARTY_TAX_PROF_ID
2509 ,CURRENCY_CONVERSION_DATE
2510 ,CURRENCY_CONVERSION_TYPE
2511 ,CURRENCY_CONVERSION_RATE
2512 --,TAX_CURRENCY_CONVERSION_DATE
2513 --,TAX_CURRENCY_CONVERSION_TYPE
2514 --,TAX_CURRENCY_CONVERSION_RATE
2515 ,TRX_CURRENCY_CODE
2516 ,MINIMUM_ACCOUNTABLE_UNIT
2517 ,PRECISION
2518 ,TRX_NUMBER
2519 ,TRX_DATE
2520 ,UNIT_PRICE
2521 ,LINE_AMT
2522 ,TRX_LINE_QUANTITY
2523 ,TAX_BASE_MODIFIER_RATE
2524 --,REF_DOC_APPLICATION_ID
2525 --,REF_DOC_ENTITY_CODE
2526 --,REF_DOC_EVENT_CLASS_CODE
2527 --,REF_DOC_TRX_ID
2528 --,REF_DOC_LINE_ID
2529 --,REF_DOC_LINE_QUANTITY
2530 --,REF_DOC_TRX_LEVEL_TYPE
2531 --,OTHER_DOC_LINE_AMT
2532 --,OTHER_DOC_LINE_TAX_AMT
2533 --,OTHER_DOC_LINE_TAXABLE_AMT
2534 ,UNROUNDED_TAXABLE_AMT
2535 ,UNROUNDED_TAX_AMT
2536 ,RELATED_DOC_APPLICATION_ID
2537 --,RELATED_DOC_ENTITY_CODE
2538 --,RELATED_DOC_EVENT_CLASS_CODE
2539 ,RELATED_DOC_TRX_ID
2540 --,RELATED_DOC_NUMBER
2541 --,RELATED_DOC_DATE
2542 --,RELATED_DOC_TRX_LEVEL_TYPE
2543 ,ADJUSTED_DOC_APPLICATION_ID
2544 ,ADJUSTED_DOC_ENTITY_CODE
2545 --,ADJUSTED_DOC_EVENT_CLASS_CODE
2546 ,ADJUSTED_DOC_TRX_ID
2547 ,ADJUSTED_DOC_LINE_ID
2548 ,ADJUSTED_DOC_NUMBER
2549 ,ADJUSTED_DOC_DATE
2550 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
2551 --,SUMMARY_TAX_LINE_ID
2552 --,OFFSET_LINK_TO_TAX_LINE_ID
2553 ,'N' --OFFSET_FLAG
2554 ,'N' --PROCESS_FOR_RECOVERY_FLAG
2555 --,TAX_JURISDICTION_ID
2556 --,TAX_JURISDICTION_CODE
2557 --,PLACE_OF_SUPPLY
2558 ,PLACE_OF_SUPPLY_TYPE_CODE
2559 --,PLACE_OF_SUPPLY_RESULT_ID
2560 --,TAX_DATE_RULE_ID
2561 ,TAX_DATE
2562 ,TAX_DETERMINE_DATE
2563 ,TAX_POINT_DATE
2564 ,TRX_LINE_DATE
2565 ,TAX_TYPE_CODE
2566 --,TAX_CODE
2567 --,TAX_REGISTRATION_ID
2568 --,TAX_REGISTRATION_NUMBER
2569 --,REGISTRATION_PARTY_TYPE
2570 ,ROUNDING_LEVEL_CODE
2571 ,ROUNDING_RULE_CODE
2572 --,ROUNDING_LVL_PARTY_TAX_PROF_ID
2573 --,ROUNDING_LVL_PARTY_TYPE
2574 ,'N' --COMPOUNDING_TAX_FLAG
2575 --,ORIG_TAX_STATUS_ID
2576 --,ORIG_TAX_STATUS_CODE
2577 --,ORIG_TAX_RATE_ID
2578 --,ORIG_TAX_RATE_CODE
2579 --,ORIG_TAX_RATE
2580 --,ORIG_TAX_JURISDICTION_ID
2581 --,ORIG_TAX_JURISDICTION_CODE
2582 --,ORIG_TAX_AMT_INCLUDED_FLAG
2583 --,ORIG_SELF_ASSESSED_FLAG
2584 ,TAX_CURRENCY_CODE
2585 ,TAX_AMT
2586 ,TAX_AMT_TAX_CURR
2587 ,TAX_AMT_FUNCL_CURR
2588 ,TAXABLE_AMT
2589 ,TAXABLE_AMT_TAX_CURR
2590 ,TAXABLE_AMT_FUNCL_CURR
2591 --,ORIG_TAXABLE_AMT
2592 --,ORIG_TAXABLE_AMT_TAX_CURR
2593 ,CAL_TAX_AMT
2594 ,CAL_TAX_AMT_TAX_CURR
2595 ,CAL_TAX_AMT_FUNCL_CURR
2596 --,ORIG_TAX_AMT
2597 --,ORIG_TAX_AMT_TAX_CURR
2598 --,REC_TAX_AMT
2599 --,REC_TAX_AMT_TAX_CURR
2600 --,REC_TAX_AMT_FUNCL_CURR
2601 --,NREC_TAX_AMT
2602 --,NREC_TAX_AMT_TAX_CURR
2603 --,NREC_TAX_AMT_FUNCL_CURR
2604 ,TAX_EXEMPTION_ID
2608 ,EXEMPT_CERTIFICATE_NUMBER
2605 --,TAX_RATE_BEFORE_EXEMPTION
2606 --,TAX_RATE_NAME_BEFORE_EXEMPTION
2607 --,EXEMPT_RATE_MODIFIER
2609 --,EXEMPT_REASON
2610 ,EXEMPT_REASON_CODE
2611 ,TAX_EXCEPTION_ID
2612 ,TAX_RATE_BEFORE_EXCEPTION
2613 --,TAX_RATE_NAME_BEFORE_EXCEPTION
2614 --,EXCEPTION_RATE
2615 ,'N' --TAX_APPORTIONMENT_FLAG
2616 ,'Y' --HISTORICAL_FLAG
2617 ,TAXABLE_BASIS_FORMULA
2618 ,TAX_CALCULATION_FORMULA
2619 ,'N' --CANCEL_FLAG
2620 ,'N' --PURGE_FLAG
2621 ,'N' --DELETE_FLAG
2622 ,'N' --TAX_AMT_INCLUDED_FLAG
2623 ,'N' --SELF_ASSESSED_FLAG
2624 ,'N' --OVERRIDDEN_FLAG
2625 ,'N' --MANUALLY_ENTERED_FLAG
2626 ,'N' --REPORTING_ONLY_FLAG
2627 ,'N' --FREEZE_UNTIL_OVERRIDDEN_FLAG
2628 ,'N' --COPIED_FROM_OTHER_DOC_FLAG
2629 ,'N' --RECALC_REQUIRED_FLAG
2630 ,'N' --SETTLEMENT_FLAG
2631 ,'N' --ITEM_DIST_CHANGED_FLAG
2632 ,'N' --ASSOCIATED_CHILD_FROZEN_FLAG
2633 ,TAX_ONLY_LINE_FLAG
2634 ,'N' --COMPOUNDING_DEP_TAX_FLAG
2635 ,'N' --ENFORCE_FROM_NATURAL_ACCT_FLAG
2636 ,'N' --COMPOUNDING_TAX_MISS_FLAG
2637 ,'N' --SYNC_WITH_PRVDR_FLAG
2638 --,LAST_MANUAL_ENTRY
2639 ,TAX_PROVIDER_ID
2640 ,'MIGRATED' --RECORD_TYPE_CODE
2641 --,REPORTING_PERIOD_ID
2642 --,LEGAL_MESSAGE_APPL_2
2643 --,LEGAL_MESSAGE_STATUS
2644 --,LEGAL_MESSAGE_RATE
2645 --,LEGAL_MESSAGE_BASIS
2646 --,LEGAL_MESSAGE_CALC
2647 --,LEGAL_MESSAGE_THRESHOLD
2648 --,LEGAL_MESSAGE_POS
2649 --,LEGAL_MESSAGE_TRN
2650 --,LEGAL_MESSAGE_EXMPT
2651 --,LEGAL_MESSAGE_EXCPT
2652 --,TAX_REGIME_TEMPLATE_ID
2653 --,TAX_APPLICABILITY_RESULT_ID
2654 --,DIRECT_RATE_RESULT_ID
2655 --,STATUS_RESULT_ID
2656 --,RATE_RESULT_ID
2657 --,BASIS_RESULT_ID
2658 --,THRESH_RESULT_ID
2659 --,CALC_RESULT_ID
2660 --,TAX_REG_NUM_DET_RESULT_ID
2661 --,EVAL_EXMPT_RESULT_ID
2662 --,EVAL_EXCPT_RESULT_ID
2663 --,TAX_HOLD_CODE
2664 --,TAX_HOLD_RELEASED_CODE
2665 --,PRD_TOTAL_TAX_AMT
2666 --,PRD_TOTAL_TAX_AMT_TAX_CURR
2667 --,PRD_TOTAL_TAX_AMT_FUNCL_CURR
2668 --,INTERNAL_ORG_LOCATION_ID
2669 ,ATTRIBUTE_CATEGORY
2670 ,ATTRIBUTE1
2671 ,ATTRIBUTE2
2672 ,ATTRIBUTE3
2673 ,ATTRIBUTE4
2674 ,ATTRIBUTE5
2675 ,ATTRIBUTE6
2676 ,ATTRIBUTE7
2677 ,ATTRIBUTE8
2678 ,ATTRIBUTE9
2679 ,ATTRIBUTE10
2680 ,ATTRIBUTE11
2681 ,ATTRIBUTE12
2682 ,ATTRIBUTE13
2683 ,ATTRIBUTE14
2684 ,ATTRIBUTE15
2685 ,GLOBAL_ATTRIBUTE_CATEGORY
2686 ,GLOBAL_ATTRIBUTE1
2687 ,GLOBAL_ATTRIBUTE2
2688 ,GLOBAL_ATTRIBUTE3
2689 ,GLOBAL_ATTRIBUTE4
2690 ,GLOBAL_ATTRIBUTE5
2691 ,GLOBAL_ATTRIBUTE6
2692 ,GLOBAL_ATTRIBUTE7
2693 ,GLOBAL_ATTRIBUTE8
2694 ,GLOBAL_ATTRIBUTE9
2695 ,GLOBAL_ATTRIBUTE10
2696 ,GLOBAL_ATTRIBUTE11
2697 ,GLOBAL_ATTRIBUTE12
2698 ,GLOBAL_ATTRIBUTE13
2699 ,GLOBAL_ATTRIBUTE14
2700 ,GLOBAL_ATTRIBUTE15
2701 ,GLOBAL_ATTRIBUTE16
2702 ,GLOBAL_ATTRIBUTE17
2703 ,GLOBAL_ATTRIBUTE18
2704 ,GLOBAL_ATTRIBUTE19
2705 ,GLOBAL_ATTRIBUTE20
2706 ,LEGAL_JUSTIFICATION_TEXT1
2707 ,LEGAL_JUSTIFICATION_TEXT2
2708 ,LEGAL_JUSTIFICATION_TEXT3
2709 --,REPORTING_CURRENCY_CODE
2710 --,LINE_ASSESSABLE_VALUE
2711 --,TRX_LINE_INDEX
2712 --,OFFSET_TAX_RATE_CODE
2713 --,PRORATION_CODE
2714 --,OTHER_DOC_SOURCE
2715 --,CTRL_TOTAL_LINE_TX_AMT
2716 --,MRC_LINK_TO_TAX_LINE_ID
2717 --,APPLIED_TO_TRX_NUMBER
2718 --,INTERFACE_ENTITY_CODE
2719 --,INTERFACE_TAX_LINE_ID
2720 --,TAXING_JURIS_GEOGRAPHY_ID
2721 ,NUMERIC1
2722 ,NUMERIC2
2723 ,NUMERIC3
2724 ,NUMERIC4
2725 ,ADJUSTED_DOC_TAX_LINE_ID
2726 ,OBJECT_VERSION_NUMBER
2727 ,'N' --MULTIPLE_JURISDICTIONS_FLAG
2728 ,CREATED_BY
2729 ,CREATION_DATE
2730 ,LAST_UPDATED_BY
2731 ,LAST_UPDATE_DATE
2732 ,LAST_UPDATE_LOGIN
2733 ,LEGAL_REPORTING_STATUS
2734 )
2735 SELECT /*+ ROWID(custtrx) ORDERED use_hash(arsysparam) swap_join_inputs(arsysparam)
2736 use_nl(types,fndcurr,fds,ptp,rbs,custtrx_prev,custtrxl,vat,rates,custtrxll,memoline) */
2737 NVL(custtrx.org_id, l_org_id) INTERNAL_ORGANIZATION_ID,
2738 222 APPLICATION_ID,
2739 'TRANSACTIONS' ENTITY_CODE,
2740 DECODE(types.type,
2741 'INV','INVOICE',
2742 'CM', 'CREDIT_MEMO',
2746 'INV',4,
2743 'DM', 'DEBIT_MEMO',
2744 'NONE') EVENT_CLASS_CODE,
2745 DECODE(types.type,
2747 'DM', 5,
2748 'CM', 6, NULL ) EVENT_CLASS_MAPPING_ID,
2749 -- DECODE(types.type,
2750 -- 'INV', 'INV_CREATE',
2751 -- 'CM', 'CM_CREATE',
2752 -- 'DM', 'DM_CREATE',
2753 -- 'CREATE') EVENT_TYPE_CODE,
2754 DECODE(types.type,
2755 'INV',DECODE(NVL(SIGN(custtrx.printing_count), 0),
2756 1, 'INV_PRINT',
2757 DECODE(custtrx.complete_flag,
2758 'Y', 'INV_COMPLETE',
2759 'INV_CREATE')),
2760 'CM',DECODE(NVL(SIGN(custtrx.printing_count), 0),
2761 1, 'CM_PRINT',
2762 DECODE(custtrx.complete_flag,
2763 'Y', 'CM_COMPLETE',
2764 'CM_CREATE')),
2765 'DM',DECODE(NVL(SIGN(custtrx.printing_count), 0),
2766 1, 'DM_PRINT',
2767 DECODE(custtrx.complete_flag,
2768 'Y', 'DM_COMPLETE',
2769 'DM_CREATE')),
2770 'CREATE') EVENT_TYPE_CODE,
2771 'CREATED' DOC_EVENT_STATUS,
2772 'CREATE' LINE_LEVEL_ACTION,
2773 custtrx.customer_trx_id TRX_ID,
2774 DECODE(custtrxl.line_type,
2775 'TAX', custtrxl.link_to_cust_trx_line_id,
2776 custtrxl.customer_trx_line_id) TRX_LINE_ID,
2777 'LINE' TRX_LEVEL_TYPE,
2778 NVL(custtrx.trx_date,sysdate) TRX_DATE,
2779
2780 --NULL TRX_DOC_REVISION,
2781 NVL(custtrx.invoice_currency_code,'USD') TRX_CURRENCY_CODE,
2782 custtrx.exchange_date CURRENCY_CONVERSION_DATE,
2783 custtrx.exchange_rate CURRENCY_CONVERSION_RATE,
2784 custtrx.exchange_rate_type CURRENCY_CONVERSION_TYPE,
2785 fndcurr.minimum_accountable_unit MINIMUM_ACCOUNTABLE_UNIT,
2786 NVL(fndcurr.precision,0) PRECISION,
2787 NVL(custtrx.legal_entity_id, -99 ) LEGAL_ENTITY_ID,
2788 --NULL ESTABLISHMENT_ID,
2789 custtrx.cust_trx_type_id RECEIVABLES_TRX_TYPE_ID,
2790 arsysparam.default_country DEFAULT_TAXATION_COUNTRY,
2791 custtrx.trx_number TRX_NUMBER,
2792 DECODE(custtrxl.line_type,
2793 'TAX', custtrxll.line_number,
2794 custtrxl.line_number) TRX_LINE_NUMBER,
2795 SUBSTRB(custtrxl.description,1,240) TRX_LINE_DESCRIPTION,
2796 --NULL TRX_DESCRIPTION,
2797 --NULL TRX_COMMUNICATED_DATE,
2798 custtrx.batch_source_id BATCH_SOURCE_ID,
2799 rbs.name BATCH_SOURCE_NAME,
2800 custtrx.doc_sequence_id DOC_SEQ_ID,
2801 fds.name DOC_SEQ_NAME,
2802 custtrx.doc_sequence_value DOC_SEQ_VALUE,
2803 custtrx.term_due_date TRX_DUE_DATE,
2804 types.description TRX_TYPE_DESCRIPTION,
2805 (CASE
2806 WHEN (custtrx.global_attribute_category
2807 = 'JA.TW.ARXTWMAI.RA_CUSTOMER_TRX' AND
2808 custtrx.global_attribute1 is NOT NULL) THEN
2809 'GUI TYPE.' || custtrx.global_attribute1
2810 END) DOCUMENT_SUB_TYPE,
2811 --NULL SUPPLIER_TAX_INVOICE_NUMBER,
2812 --NULL SUPPLIER_TAX_INVOICE_DATE,
2813 --NULL SUPPLIER_EXCHANGE_RATE,
2814 (CASE
2815 WHEN custtrx.global_attribute_category
2816 IN ('JE.HU.ARXTWMAI.TAX_DATE',
2817 'JE.SK.ARXTWMAI.TAX_DATE',
2818 'JE.PL.ARXTWMAI.TAX_DATE',
2819 'JE.CZ.ARXTWMAI.TAX_DATE')
2820 THEN
2821 TO_DATE(custtrx.global_attribute1, 'YYYY/MM/DD HH24:MI:SS')
2822 WHEN custtrx.global_attribute_category
2823 = 'JL.AR.ARXTWMAI.TGW_HEADER' THEN
2824 TO_DATE(custtrx.global_attribute18, 'YYYY/MM/DD HH24:MI:SS')
2825 END) TAX_INVOICE_DATE,
2826
2827 (CASE
2828 WHEN custtrx.global_attribute_category
2829 = 'JL.AR.ARXTWMAI.TGW_HEADER' THEN
2830 custtrx.global_attribute17
2831 END) TAX_INVOICE_NUMBER,
2832 ptp.party_tax_profile_id FIRST_PTY_ORG_ID,
2833 'SALES_TRANSACTION' TAX_EVENT_CLASS_CODE,
2834 -- 'CREATE' TAX_EVENT_TYPE_CODE,
2835 DECODE(NVL(SIGN(custtrx.printing_count), 0),
2836 1, 'FREEZE_FOR_TAX',
2837 DECODE(custtrx.complete_flag,
2838 'Y', 'VALIDATE_FOR_TAX',
2839 'CREATE') ) TAX_EVENT_TYPE_CODE,
2840
2841 --NULL LINE_INTENDED_USE,
2845 --NULL TRX_SIC_CODE,
2842 custtrxl.line_type TRX_LINE_TYPE,
2843 --NULL TRX_SHIPPING_DATE,
2844 --NULL TRX_RECEIPT_DATE,
2846 custtrx.fob_point FOB_POINT,
2847 custtrx.waybill_number TRX_WAYBILL_NUMBER,
2848 custtrxl.inventory_item_id PRODUCT_ID,
2849 (CASE
2850 WHEN custtrx.global_attribute_category
2851 = 'JA.TW.ARXTWMAI.RA_CUSTOMER_TRX'
2852 AND l_inv_installed = 'Y'
2853 THEN
2854 DECODE(custtrxl.global_attribute2,
2855 'Y', 'WINE CIGARRETE',
2856 'N', NULL)
2857
2858 WHEN custtrxl.global_attribute_category
2859 IN ('JL.AR.ARXTWMAI.LINES',
2860 'JL.BR.ARXTWMAI.Additional Info',
2861 'JL.CO.ARXTWMAI.LINES' )
2862 AND l_inv_installed = 'Y'
2863 THEN
2864 custtrxl.global_attribute2
2865 END) PRODUCT_FISC_CLASSIFICATION,
2866 custtrxl.warehouse_id PRODUCT_ORG_ID,
2867 custtrxl.uom_code UOM_CODE,
2868 --NULL PRODUCT_TYPE,
2869 --NULL PRODUCT_CODE,
2870 (CASE
2871 WHEN custtrx.global_attribute_category
2872 = 'JA.TW.ARXTWMAI.RA_CUSTOMER_TRX'
2873 AND l_inv_installed = 'N'
2874 THEN
2875 DECODE(custtrxl.global_attribute2,
2876 'Y', 'WINE CIGARRETE',
2877 'N', NULL)
2878
2879 WHEN custtrxl.global_attribute_category
2880 IN ('JL.AR.ARXTWMAI.LINES',
2881 'JL.BR.ARXTWMAI.Additional Info',
2882 'JL.CO.ARXTWMAI.LINES')
2883 AND l_inv_installed = 'N'
2884 THEN
2885 custtrxl.global_attribute2
2886 END) PRODUCT_CATEGORY,
2887
2888 DECODE( custtrxl.inventory_item_id,
2889 NULL,NULL,
2890 SUBSTRB(custtrxl.description,1,240) ) PRODUCT_DESCRIPTION,
2891 (CASE
2892 WHEN custtrxl.global_attribute_category
2893 = 'JL.BR.ARXTWMAI.Additional Info'
2894 THEN
2895 custtrxl.global_attribute1
2896 END) USER_DEFINED_FISC_CLASS,
2897
2898 DECODE( custtrxl.line_type,
2899 'TAX', nvl(custtrxll.extended_amount,0),
2900 nvl(custtrxl.extended_amount,0)) LINE_AMT,
2901
2902 DECODE(custtrxl.line_type,
2903 'TAX', custtrxll.quantity_invoiced,
2904 custtrxl.quantity_invoiced ) TRX_LINE_QUANTITY,
2905
2906 --NULL CASH_DISCOUNT,
2907 --NULL VOLUME_DISCOUNT,
2908 --NULL TRADING_DISCOUNT,
2909 --NULL TRANSFER_CHARGE,
2910 --NULL TRANSPORTATION_CHARGE,
2911 --NULL INSURANCE_CHARGE,
2912 --NULL OTHER_CHARGE,
2913 --NULL ASSESSABLE_VALUE,
2914 --NULL ASSET_FLAG,
2915 --NULL ASSET_NUMBER,
2916 1 ASSET_ACCUM_DEPRECIATION,
2917 --NULL ASSET_TYPE,
2918 1 ASSET_COST,
2919
2920 DECODE( custtrx.related_customer_trx_id,
2921 NULL, NULL,
2922 222) RELATED_DOC_APPLICATION_ID,
2923 --NULL RELATED_DOC_ENTITY_CODE,
2924 --NULL RELATED_DOC_EVENT_CLASS_CODE,
2925 custtrx.related_customer_trx_id RELATED_DOC_TRX_ID,
2926 --NULL RELATED_DOC_NUMBER,
2927 --NULL RELATED_DOC_DATE,
2928
2929 DECODE(custtrxl.previous_customer_trx_id,
2930 NULL, NULL,
2931 222 ) ADJUSTED_DOC_APPLICATION_ID,
2932 DECODE(custtrxl.previous_customer_trx_id,
2933 NULL, NULL,
2934 'TRANSACTIONS' ) ADJUSTED_DOC_ENTITY_CODE,
2935 --NULL ADJUSTED_DOC_EVENT_CLASS_CODE,
2936 custtrxl.previous_customer_trx_id ADJUSTED_DOC_TRX_ID,
2937
2938 DECODE(custtrxl.line_type,
2939 'TAX', custtrxll.previous_customer_trx_line_id,
2940 custtrxl.previous_customer_trx_line_id) ADJUSTED_DOC_LINE_ID,
2941
2942 custtrx_prev.trx_number ADJUSTED_DOC_NUMBER,
2943 custtrx_prev.trx_Date ADJUSTED_DOC_DATE,
2944 DECODE(custtrxl.previous_customer_trx_id,
2945 NULL, NULL,
2946 'LINE' ) ADJUSTED_DOC_TRX_LEVEL_TYPE,
2947
2948 --NULL REF_DOC_APPLICATION_ID,
2949 --NULL REF_DOC_ENTITY_CODE,
2953 --NULL REF_DOC_LINE_QUANTITY,
2950 --NULL REF_DOC_EVENT_CLASS_CODE,
2951 --NULL REF_DOC_TRX_ID,
2952 --NULL REF_DOC_LINE_ID,
2954 --NULL REF_DOC_TRX_LEVEL_TYPE,
2955
2956 (CASE
2957 WHEN custtrx.global_attribute_category
2958 = 'JA.TW.ARXTWMAI.RA_CUSTOMER_TRX'
2959 THEN
2960 'SALES_TRANSACTION.' ||custtrx.global_attribute3
2961
2962 WHEN custtrx.global_attribute_category IN
2963 ('JE.ES.ARXTWMAI.INVOICE_INFO'
2964 ,'JE.ES.ARXTWMAI.MODELO347'
2965 ,'JE.ES.ARXTWMAI.MODELO347PR'
2966 ,'JE.ES.ARXTWMAI.MODELO349'
2967 ,'JE.ES.ARXTWMAI.MODELO415'
2968 ,'JE.ES.ARXTWMAI.MODELO415_347'
2969 ,'JE.ES.ARXTWMAI.MODELO415_347PR'
2970 ,'JE.ES.ARXTWMAI.OTHER')
2971 THEN
2972 'SALES_TRANSACTION.INVOICE TYPE.'||custtrx.global_attribute1
2973
2974 WHEN custtrxl.global_attribute_category IN
2975 ('JL.AR.ARXTWMAI.LINES'
2976 ,'JL.BR.ARXTWMAI.Additional Info'
2977 ,'JL.CO.ARXTWMAI.LINES')
2978 THEN
2979 'SALES_TRANSACTION.' ||custtrxl.global_attribute3
2980 END ) TRX_BUSINESS_CATEGORY,
2981
2982 custtrxl.tax_exempt_number EXEMPT_CERTIFICATE_NUMBER,
2983 --NULL EXEMPT_REASON,
2984 custtrxl.tax_exempt_flag EXEMPTION_CONTROL_FLAG,
2985 custtrxl.tax_exempt_reason_code EXEMPT_REASON_CODE,
2986 --'Y' HISTORICAL_FLAG,
2987 NVL(custtrx.trx_date,sysdate) TRX_LINE_GL_DATE,
2988 --'N' LINE_AMT_INCLUDES_TAX_FLAG,
2989 --NULL ACCOUNT_CCID,
2990 --NULL ACCOUNT_STRING,
2991 --NULL SHIP_TO_LOCATION_ID,
2992 --NULL SHIP_FROM_LOCATION_ID,
2993 --NULL POA_LOCATION_ID,
2994 --NULL POO_LOCATION_ID,
2995 --NULL BILL_TO_LOCATION_ID,
2996 --NULL BILL_FROM_LOCATION_ID,
2997 --NULL PAYING_LOCATION_ID,
2998 --NULL OWN_HQ_LOCATION_ID,
2999 --NULL TRADING_HQ_LOCATION_ID,
3000 --NULL POC_LOCATION_ID,
3001 --NULL POI_LOCATION_ID,
3002 --NULL POD_LOCATION_ID,
3003 --NULL TITLE_TRANSFER_LOCATION_ID,
3004 --'N' CTRL_HDR_TX_APPL_FLAG,
3005 --NULL CTRL_TOTAL_LINE_TX_AMT,
3006 --NULL CTRL_TOTAL_HDR_TX_AMT,
3007
3008 DECODE(types.type,
3009 'INV','INVOICE',
3010 'CM', 'CREDIT_MEMO',
3011 'DM', 'DEBIT_MEMO',
3012 types.type) LINE_CLASS,
3013 NVL(custtrx.trx_date,sysdate) TRX_LINE_DATE,
3014 --NULL INPUT_TAX_CLASSIFICATION_CODE,
3015 vat.tax_code OUTPUT_TAX_CLASSIFICATION_CODE,
3016 --NULL INTERNAL_ORG_LOCATION_ID,
3017 --NULL PORT_OF_ENTRY_CODE,
3018 --'Y' TAX_REPORTING_FLAG,
3019 --'N' TAX_AMT_INCLUDED_FLAG,
3020 --'N' COMPOUNDING_TAX_FLAG,
3021 --NULL EVENT_ID,
3022 --'N' THRESHOLD_INDICATOR_FLAG,
3023 --NULL PROVNL_TAX_DETERMINATION_DATE,
3024 DECODE(custtrxl.line_type,
3025 'TAX', custtrxll.unit_selling_price,
3026 custtrxl.unit_selling_price ) UNIT_PRICE,
3027 custtrx.ship_to_site_use_id SHIP_TO_CUST_ACCT_SITE_USE_ID,
3028 custtrx.bill_to_site_use_id BILL_TO_CUST_ACCT_SITE_USE_ID,
3029 custtrx.batch_id TRX_BATCH_ID,
3030
3031 --NULL START_EXPENSE_DATE,
3032 --NULL SOURCE_APPLICATION_ID,
3033 --NULL SOURCE_ENTITY_CODE,
3034 --NULL SOURCE_EVENT_CLASS_CODE,
3035 --NULL SOURCE_TRX_ID,
3036 --NULL SOURCE_LINE_ID,
3037 --NULL SOURCE_TRX_LEVEL_TYPE,
3038 --'MIGRATED' RECORD_TYPE_CODE,
3042 DECODE(types.default_status,
3039 --'N' INCLUSIVE_TAX_OVERRIDE_FLAG,
3040 --'N' TAX_PROCESSING_COMPLETED_FLAG,
3041 1 OBJECT_VERSION_NUMBER,
3043 'VD', 'VD',
3044 NULL) APPLICATION_DOC_STATUS,
3045 --'N' USER_UPD_DET_FACTORS_FLAG,
3046 --NULL SOURCE_TAX_LINE_ID,
3047 --NULL REVERSED_APPLN_ID,
3048 --NULL REVERSED_ENTITY_CODE,
3049 --NULL REVERSED_EVNT_CLS_CODE,
3050 --NULL REVERSED_TRX_ID,
3051 --NULL REVERSED_TRX_LEVEL_TYPE,
3052 --NULL REVERSED_TRX_LINE_ID,
3053 --NULL TAX_CALCULATION_DONE_FLAG,
3054 decode(arsysparam.tax_database_view_set,'_A','Y','_V','Y',NULL)
3055 PARTNER_MIGRATED_FLAG,
3056 custtrx.ship_to_address_id SHIP_THIRD_PTY_ACCT_SITE_ID,
3057 custtrx.bill_to_address_id BILL_THIRD_PTY_ACCT_SITE_ID,
3058 custtrx.ship_to_customer_id SHIP_THIRD_PTY_ACCT_ID,
3059 custtrx.bill_to_customer_id BILL_THIRD_PTY_ACCT_ID,
3060
3061 --NULL INTERFACE_ENTITY_CODE,
3062 --NULL INTERFACE_LINE_ID,
3063 --NULL HISTORICAL_TAX_CODE_ID,
3064 --NULL ICX_SESSION_ID,
3065 --NULL TRX_LINE_CURRENCY_CODE,
3066 --NULL TRX_LINE_CURRENCY_CONV_RATE,
3067 --NULL TRX_LINE_CURRENCY_CONV_DATE,
3068 --NULL TRX_LINE_PRECISION,
3069 --NULL TRX_LINE_MAU,
3070 --NULL TRX_LINE_CURRENCY_CONV_TYPE,
3071
3072 -- zx_lines columns start from here
3073
3074 custtrxl.tax_line_id TAX_LINE_ID,
3075 DECODE(custtrxl.line_type,
3076 'TAX', RANK() OVER (
3077 PARTITION BY
3078 custtrxl.link_to_cust_trx_line_id,
3079 custtrxl.customer_trx_id
3080 ORDER BY
3081 custtrxl.line_number,
3082 custtrxl.customer_trx_line_id
3083 ),
3084 NULL) TAX_LINE_NUMBER,
3085 ptp.party_tax_profile_id CONTENT_OWNER_ID,
3086 regimes.tax_regime_id TAX_REGIME_ID,
3087 rates.TAX_REGIME_CODE TAX_REGIME_CODE,
3088 taxes.tax_id TAX_ID,
3089 rates.tax TAX,
3090 status.tax_status_id TAX_STATUS_ID,
3091 rates.TAX_STATUS_CODE TAX_STATUS_CODE,
3092 custtrxl.vat_tax_id TAX_RATE_ID,
3093 rates.TAX_RATE_CODE TAX_RATE_CODE,
3094 custtrxl.tax_rate TAX_RATE,
3095 rates.rate_type_code TAX_RATE_TYPE,
3096
3097 DECODE(custtrxl.line_type,
3098 'TAX', RANK() OVER (
3099 PARTITION BY
3100 rates.tax_regime_code,
3101 rates.tax,
3102 custtrxl.link_to_cust_trx_line_id,
3103 custtrxl.customer_trx_id
3104 ORDER BY
3105 custtrxl.line_number,
3106 custtrxl.customer_trx_line_id
3107 ),
3108 NULL) TAX_APPORTIONMENT_LINE_NUMBER,
3109
3110 --'N' MRC_TAX_LINE_FLAG,
3111 custtrx.set_of_books_id LEDGER_ID,
3112 --NULL LEGAL_ENTITY_TAX_REG_NUMBER,
3113 --NULL HQ_ESTB_REG_NUMBER,
3114 --NULL HQ_ESTB_PARTY_TAX_PROF_ID,
3115 --NULL TAX_CURRENCY_CONVERSION_DATE,
3116 --NULL TAX_CURRENCY_CONVERSION_TYPE,
3117 --NULL TAX_CURRENCY_CONVERSION_RATE,
3118
3119 (CASE
3120 WHEN custtrxl.global_attribute_category
3121 IN ('JL.BR.ARXTWMAI.Additional Info',
3122 'JL.CO.ARXTWMAI.LINES',
3123 'JL.AR.ARXTWMAI.LINES')
3124 THEN
3125 DECODE(LTRIM(custtrxl.global_attribute12,'-.0123456789'),
3126 NULL,TO_NUMBER(custtrxl.global_attribute12),
3127 NULL)
3128 END) TAX_BASE_MODIFIER_RATE,
3129
3130 --NULL OTHER_DOC_LINE_AMT,
3131 --NULL OTHER_DOC_LINE_TAX_AMT,
3135 IN ( 'JL.BR.ARXTWMAI.Additional Info',
3132 --NULL OTHER_DOC_LINE_TAXABLE_AMT,
3133 (CASE
3134 WHEN custtrxl.global_attribute_category
3136 'JL.CO.ARXTWMAI.LINES',
3137 'JL.AR.ARXTWMAI.LINES')
3138 THEN
3139 DECODE(LTRIM(custtrxl.global_attribute11,'-.0123456789'),
3140 NULL,TO_NUMBER(custtrxl.global_attribute11),
3141 NULL)
3142 ELSE
3143 custtrxl.taxable_amount
3144 END) UNROUNDED_TAXABLE_AMT,
3145
3146 (CASE
3147 WHEN custtrxl.global_attribute_category
3148 IN( 'JL.BR.ARXTWMAI.Additional Info',
3149 'JL.CO.ARXTWMAI.LINES',
3150 'JL.AR.ARXTWMAI.LINES')
3151 THEN
3152 DECODE(LTRIM(custtrxl.global_attribute19,'-.0123456789'),
3153 NULL,TO_NUMBER(custtrxl.global_attribute19),
3154 NULL)
3155 ELSE
3156 custtrxl.extended_amount
3157 END) UNROUNDED_TAX_AMT,
3158 --NULL RELATED_DOC_TRX_LEVEL_TYPE,
3159 --NULL SUMMARY_TAX_LINE_ID,
3160 --NULL OFFSET_LINK_TO_TAX_LINE_ID,
3161 --'N' OFFSET_FLAG,
3162 --'N' PROCESS_FOR_RECOVERY_FLAG,
3163 --NULL TAX_JURISDICTION_ID,
3164 --NULL TAX_JURISDICTION_CODE,
3165 --NULL PLACE_OF_SUPPLY,
3166 -- decode(custtrx.ship_to_site_use_id,null,'BILL_TO','SHIP_TO') PLACE_OF_SUPPLY_TYPE_CODE,
3167 'SHIP_TO_BILL_TO' PLACE_OF_SUPPLY_TYPE_CODE,
3168 --NULL PLACE_OF_SUPPLY_RESULT_ID,
3169 --NULL TAX_DATE_RULE_ID,
3170 DECODE(custtrxl.previous_customer_trx_id,
3171 NULL, custtrx.trx_date,
3172 custtrx_prev.trx_date ) TAX_DATE,
3173 DECODE(custtrxl.previous_customer_trx_id,
3174 NULL, custtrx.trx_date,
3175 custtrx_prev.trx_date ) TAX_DETERMINE_DATE,
3176 DECODE(custtrxl.previous_customer_trx_id,
3177 NULL, custtrx.trx_date,
3178 custtrx_prev.trx_date ) TAX_POINT_DATE,
3179 taxes.tax_type_code TAX_TYPE_CODE,
3180 --NULL TAX_CODE,
3181 --NULL TAX_REGISTRATION_ID,
3182 --NULL TAX_REGISTRATION_NUMBER,
3183 --NULL REGISTRATION_PARTY_TYPE,
3184 decode (arsysparam.TRX_HEADER_LEVEL_ROUNDING,
3185 'Y', 'HEADER',
3186 'LINE') ROUNDING_LEVEL_CODE,
3187 arsysparam.TAX_ROUNDING_RULE ROUNDING_RULE_CODE,
3188 --NULL ROUNDING_LVL_PARTY_TAX_PROF_ID,
3189 --NULL ROUNDING_LVL_PARTY_TYPE,
3190 --NULL ORIG_TAX_STATUS_ID,
3191 --NULL ORIG_TAX_STATUS_CODE,
3192 --NULL ORIG_TAX_RATE_ID,
3193 --NULL ORIG_TAX_RATE_CODE,
3194 --NULL ORIG_TAX_RATE,
3195 --NULL ORIG_TAX_JURISDICTION_ID,
3196 --NULL ORIG_TAX_JURISDICTION_CODE,
3197 --NULL ORIG_TAX_AMT_INCLUDED_FLAG,
3198 --NULL ORIG_SELF_ASSESSED_FLAG,
3199 taxes.tax_currency_code TAX_CURRENCY_CODE,
3200 custtrxl.extended_amount TAX_AMT,
3201 (CASE
3202 WHEN custtrxl.global_attribute_category
3203 IN( 'JL.BR.ARXTWMAI.Additional Info',
3204 'JL.CO.ARXTWMAI.LINES',
3205 'JL.AR.ARXTWMAI.LINES')
3206 THEN
3207 DECODE(LTRIM(custtrxl.global_attribute19,'-.0123456789'),
3208 NULL,TO_NUMBER(custtrxl.global_attribute19)*
3209 NVL(custtrx.exchange_rate,1),
3210 NULL)
3211 ELSE
3212 custtrxl.extended_amount *
3213 NVL(custtrx.exchange_rate,1)
3214 END) TAX_AMT_TAX_CURR,
3215
3216 (CASE
3217 WHEN custtrxl.global_attribute_category
3218 IN( 'JL.BR.ARXTWMAI.Additional Info',
3219 'JL.CO.ARXTWMAI.LINES',
3220 'JL.AR.ARXTWMAI.LINES')
3221 THEN
3222 DECODE(LTRIM(custtrxl.global_attribute19,'-.0123456789'),
3223 NULL,TO_NUMBER(custtrxl.global_attribute19)*
3224 NVL(custtrx.exchange_rate,1),
3225 NULL)
3226 ELSE
3227 custtrxl.extended_amount *
3228 NVL(custtrx.exchange_rate,1)
3229 END) TAX_AMT_FUNCL_CURR,
3230
3231 (CASE
3232 WHEN custtrxl.global_attribute_category
3236 THEN
3233 IN ( 'JL.BR.ARXTWMAI.Additional Info',
3234 'JL.CO.ARXTWMAI.LINES',
3235 'JL.AR.ARXTWMAI.LINES')
3237 DECODE(LTRIM(custtrxl.global_attribute11,'-.0123456789'),
3238 NULL,TO_NUMBER(custtrxl.global_attribute11),
3239 NULL)
3240 ELSE
3241 custtrxl.taxable_amount
3242 END) TAXABLE_AMT,
3243
3244 (CASE
3245 WHEN custtrxl.global_attribute_category
3246 IN ( 'JL.BR.ARXTWMAI.Additional Info',
3247 'JL.CO.ARXTWMAI.LINES',
3248 'JL.AR.ARXTWMAI.LINES')
3249 THEN
3250 DECODE(LTRIM(custtrxl.global_attribute11,'-.0123456789'),
3251 NULL,TO_NUMBER(custtrxl.global_attribute11)*
3252 NVL(custtrx.exchange_rate,1),
3253 NULL)
3254 ELSE
3255 custtrxl.taxable_amount*
3256 NVL(custtrx.exchange_rate,1)
3257 END) TAXABLE_AMT_TAX_CURR,
3258
3259 (CASE
3260 WHEN custtrxl.global_attribute_category
3261 IN ( 'JL.BR.ARXTWMAI.Additional Info',
3262 'JL.CO.ARXTWMAI.LINES',
3263 'JL.AR.ARXTWMAI.LINES')
3264 THEN
3265 DECODE(LTRIM(custtrxl.global_attribute11,'-.0123456789'),
3266 NULL,TO_NUMBER(custtrxl.global_attribute11)*
3267 NVL(custtrx.exchange_rate,1),
3268 NULL)
3269 ELSE
3270 custtrxl.taxable_amount*
3271 NVL(custtrx.exchange_rate,1)
3272 END) TAXABLE_AMT_FUNCL_CURR,
3273
3274 --NULL ORIG_TAXABLE_AMT,
3275 --NULL ORIG_TAXABLE_AMT_TAX_CURR,
3276
3277 (CASE
3278 WHEN custtrxl.global_attribute_category
3279 IN ( 'JL.BR.ARXTWMAI.Additional Info',
3280 'JL.CO.ARXTWMAI.LINES',
3281 'JL.AR.ARXTWMAI.LINES')
3282 THEN
3283 DECODE(LTRIM(custtrxl.global_attribute20,'-.0123456789'),
3284 NULL,TO_NUMBER(custtrxl.global_attribute20),
3285 NULL)
3286 END) CAL_TAX_AMT,
3287
3288 (CASE
3289 WHEN custtrxl.global_attribute_category
3290 IN ( 'JL.BR.ARXTWMAI.Additional Info',
3291 'JL.CO.ARXTWMAI.LINES',
3292 'JL.AR.ARXTWMAI.LINES')
3293 THEN
3294 DECODE(LTRIM(custtrxl.global_attribute20,'-.0123456789'),
3295 NULL,TO_NUMBER(custtrxl.global_attribute20)*
3296 NVL(custtrx.EXCHANGE_RATE,1),
3297 NULL)
3298 END) CAL_TAX_AMT_TAX_CURR,
3299
3300 (CASE
3301 WHEN custtrxl.global_attribute_category
3302 IN ( 'JL.BR.ARXTWMAI.Additional Info',
3303 'JL.CO.ARXTWMAI.LINES',
3304 'JL.AR.ARXTWMAI.LINES')
3305 THEN
3306 DECODE(LTRIM(custtrxl.global_attribute20,'-.0123456789'),
3307 NULL,TO_NUMBER(custtrxl.global_attribute20)*
3308 NVL(custtrx.EXCHANGE_RATE,1),
3309 NULL)
3310 END) CAL_TAX_AMT_FUNCL_CURR,
3311
3312 --NULL ORIG_TAX_AMT,
3313 --NULL ORIG_TAX_AMT_TAX_CURR,
3314 --NULL REC_TAX_AMT,
3315 --NULL REC_TAX_AMT_TAX_CURR,
3316 --NULL REC_TAX_AMT_FUNCL_CURR,
3317 --NULL NREC_TAX_AMT,
3318 --NULL NREC_TAX_AMT_TAX_CURR,
3319 --NULL NREC_TAX_AMT_FUNCL_CURR,
3320 custtrxl.TAX_EXEMPTION_ID TAX_EXEMPTION_ID,
3321 --NULL TAX_RATE_BEFORE_EXEMPTION,
3322 --NULL TAX_RATE_NAME_BEFORE_EXEMPTION,
3323 --NULL EXEMPT_RATE_MODIFIER,
3324 custtrxl.item_exception_rate_id TAX_EXCEPTION_ID,
3325 DECODE(rates.rate_type_code,
3326 'PERCENTAGE', rates.percentage_rate,
3327 'QUANTITY', rates.quantity_rate,
3328 NULL) TAX_RATE_BEFORE_EXCEPTION,
3329 --NULL TAX_RATE_NAME_BEFORE_EXCEPTION,
3330 --NULL EXCEPTION_RATE,
3331 --'N' TAX_APPORTIONMENT_FLAG,
3332 -- DECODE(vat.taxable_basis,
3333 -- 'AFTER_EPD', 'STANDARD_TB_DISCOUNT',
3334 -- 'QUANTITY', 'STANDARD_QUANTITY',
3335 -- 'STANDARD_TB') TAXABLE_BASIS_FORMULA,
3336 -- 'STANDARD_TC' TAX_CALCULATION_FORMULA,
3337 NVL(rates.taxable_basis_formula_code,
3338 taxes.def_taxable_basis_formula) TAXABLE_BASIS_FORMULA,
3339 NVL(taxes.def_tax_calc_formula,
3340 'STANDARD_TC') TAX_CALCULATION_FORMULA,
3341 --'N' CANCEL_FLAG,
3345 --'N' OVERRIDDEN_FLAG,
3342 --'N' PURGE_FLAG,
3343 --'N' DELETE_FLAG,
3344 --'N' SELF_ASSESSED_FLAG,
3346 --'N' MANUALLY_ENTERED_FLAG,
3347 --'N' REPORTING_ONLY_FLAG,
3348 --'N' FREEZE_UNTIL_OVERRIDDEN_FLAG,
3349 --'N' COPIED_FROM_OTHER_DOC_FLAG,
3350 --'N' RECALC_REQUIRED_FLAG,
3351 --'N' SETTLEMENT_FLAG,
3352 --'N' ITEM_DIST_CHANGED_FLAG,
3353 --'N' ASSOCIATED_CHILD_FROZEN_FLAG,
3354 DECODE(memoline.line_type, 'TAX', 'Y', 'N') TAX_ONLY_LINE_FLAG,
3355 --'N' COMPOUNDING_DEP_TAX_FLAG,
3356 --'N' ENFORCE_FROM_NATURAL_ACCT_FLAG,
3357 --'N' COMPOUNDING_TAX_MISS_FLAG,
3358 --'N' SYNC_WITH_PRVDR_FLAG,
3359 --NULL LAST_MANUAL_ENTRY,
3360 decode(arsysparam.tax_database_view_set,'_A',2,'_V',1, NULL)
3361 TAX_PROVIDER_ID,
3362 --NULL REPORTING_PERIOD_ID,
3363 --NULL LEGAL_MESSAGE_APPL_2,
3364 --NULL LEGAL_MESSAGE_STATUS,
3365 --NULL LEGAL_MESSAGE_RATE,
3366 --NULL LEGAL_MESSAGE_BASIS,
3367 --NULL LEGAL_MESSAGE_CALC,
3368 --NULL LEGAL_MESSAGE_THRESHOLD,
3369 --NULL LEGAL_MESSAGE_POS,
3370 --NULL LEGAL_MESSAGE_TRN,
3371 --NULL LEGAL_MESSAGE_EXMPT,
3372 --NULL LEGAL_MESSAGE_EXCPT,
3373 --NULL TAX_REGIME_TEMPLATE_ID,
3374 --NULL TAX_APPLICABILITY_RESULT_ID,
3375 --NULL DIRECT_RATE_RESULT_ID,
3376 --NULL STATUS_RESULT_ID,
3377 --NULL RATE_RESULT_ID,
3378 --NULL BASIS_RESULT_ID,
3379 --NULL THRESH_RESULT_ID,
3380 --NULL CALC_RESULT_ID,
3381 --NULL TAX_REG_NUM_DET_RESULT_ID,
3382 --NULL EVAL_EXMPT_RESULT_ID,
3383 --NULL EVAL_EXCPT_RESULT_ID,
3384 --NULL TAX_HOLD_CODE,
3385 --NULL TAX_HOLD_RELEASED_CODE,
3386 --NULL PRD_TOTAL_TAX_AMT,
3387 --NULL PRD_TOTAL_TAX_AMT_TAX_CURR,
3388 --NULL PRD_TOTAL_TAX_AMT_FUNCL_CURR,
3389 custtrxl.GLOBAL_ATTRIBUTE8 LEGAL_JUSTIFICATION_TEXT1,
3390 custtrxl.GLOBAL_ATTRIBUTE9 LEGAL_JUSTIFICATION_TEXT2,
3391 custtrxl.GLOBAL_ATTRIBUTE10 LEGAL_JUSTIFICATION_TEXT3,
3392 --NULL REPORTING_CURRENCY_CODE,
3393 --NULL LINE_ASSESSABLE_VALUE,
3394 --NULL TRX_LINE_INDEX,
3395 --NULL OFFSET_TAX_RATE_CODE,
3396 --NULL PRORATION_CODE,
3397 --NULL OTHER_DOC_SOURCE,
3398 --NULL MRC_LINK_TO_TAX_LINE_ID,
3399 --NULL APPLIED_TO_TRX_NUMBER,
3400 --NULL INTERFACE_TAX_LINE_ID,
3401 --NULL TAXING_JURIS_GEOGRAPHY_ID,
3402 decode(arsysparam.tax_database_view_Set ,
3403 '_A',decode(custtrxl.global_attribute1,'ALL',
3404 custtrxl.global_Attribute2,null),
3405 '_V',decode(custtrxl.global_attribute1,'ALL',
3406 custtrxl.global_Attribute2,null),
3407 NULL) numeric1,
3408 decode(arsysparam.tax_database_view_Set ,
3409 '_A',decode(custtrxl.global_attribute1,'ALL',
3410 custtrxl.global_Attribute4,null),
3411 '_V',decode(custtrxl.global_attribute1,'ALL',
3412 custtrxl.global_Attribute4,null),
3413 NULL) numeric2,
3414 decode(arsysparam.tax_database_view_Set ,
3415 '_A',decode(custtrxl.global_attribute1,'ALL',
3416 custtrxl.global_Attribute6,null),
3420 decode(arsysparam.tax_database_view_Set,
3417 '_V',decode(custtrxl.global_attribute1,'ALL',
3418 custtrxl.global_Attribute6,null),
3419 NULL) numeric3,
3421 '_A',
3422 decode(custtrxl.global_attribute1,'ALL',
3423 to_number(substrb(custtrxl.global_Attribute12,1,
3424 instrb(custtrxl.global_Attribute12,'|',1,1)-1)),
3425 'STATE',
3426 to_number(substrb(custtrxl.global_Attribute12,1,
3427 instrb(custtrxl.global_Attribute12,'|',1,1)-1)),
3428 NULL),
3429 '_V',
3430 decode(custtrxl.global_attribute1,'ALL',
3431 to_number(substrb(custtrxl.global_Attribute12,1,
3432 instrb(custtrxl.global_Attribute12,'|',1,1)-1)),
3433 'STATE',
3434 to_number(substrb(custtrxl.global_Attribute12,1,
3435 instrb(custtrxl.global_Attribute12,'|',1,1)-1)),
3436 NULL)
3437 ,NULL) numeric4,
3438
3439 DECODE(custtrxl.line_type,
3440 'TAX', custtrxl.previous_customer_trx_line_id,
3441 NULL) ADJUSTED_DOC_TAX_LINE_ID,
3442 custtrxl.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,
3443 custtrxl.ATTRIBUTE1 ATTRIBUTE1,
3444 custtrxl.ATTRIBUTE2 ATTRIBUTE2,
3445 custtrxl.ATTRIBUTE3 ATTRIBUTE3,
3446 custtrxl.ATTRIBUTE4 ATTRIBUTE4,
3447 custtrxl.ATTRIBUTE5 ATTRIBUTE5,
3448 custtrxl.ATTRIBUTE6 ATTRIBUTE6,
3449 custtrxl.ATTRIBUTE7 ATTRIBUTE7,
3450 custtrxl.ATTRIBUTE8 ATTRIBUTE8,
3451 custtrxl.ATTRIBUTE9 ATTRIBUTE9,
3452 custtrxl.ATTRIBUTE10 ATTRIBUTE10,
3453 custtrxl.ATTRIBUTE11 ATTRIBUTE11,
3454 custtrxl.ATTRIBUTE12 ATTRIBUTE12,
3455 custtrxl.ATTRIBUTE13 ATTRIBUTE13,
3456 custtrxl.ATTRIBUTE14 ATTRIBUTE14,
3457 custtrxl.ATTRIBUTE15 ATTRIBUTE15,
3458 custtrxl.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY,
3459 custtrxl.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1,
3460 custtrxl.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2,
3461 custtrxl.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3,
3462 custtrxl.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4,
3463 custtrxl.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5,
3464 custtrxl.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6,
3465 custtrxl.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7,
3466 custtrxl.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8,
3467 custtrxl.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9,
3468 custtrxl.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10,
3469 custtrxl.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11,
3470 custtrxl.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12,
3471 custtrxl.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13,
3472 custtrxl.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14,
3473 custtrxl.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15,
3474 custtrxl.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16,
3475 custtrxl.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17,
3476 custtrxl.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18,
3477 custtrxl.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19,
3478 custtrxl.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20,
3479 --'N' MULTIPLE_JURISDICTIONS_FLAG,
3480 SYSDATE CREATION_DATE,
3481 1 CREATED_BY,
3482 SYSDATE LAST_UPDATE_DATE,
3483 1 LAST_UPDATED_BY,
3484 0 LAST_UPDATE_LOGIN,
3485 DECODE(custtrx.complete_flag,
3486 'Y', '111111111111111',
3487 '000000000000000') LEGAL_REPORTING_STATUS
3488
3489 FROM ( select distinct other_doc_application_id,other_doc_trx_id from ZX_VALIDATION_ERRORS_GT ) zxvalerr, --Bug 5187701
3490 RA_CUSTOMER_TRX_ALL custtrx,
3491 AR_SYSTEM_PARAMETERS_ALL arsysparam,
3492 RA_CUST_TRX_TYPES_ALL types,
3493 FND_CURRENCIES fndcurr,
3494 FND_DOCUMENT_SEQUENCES fds,
3495 ZX_PARTY_TAX_PROFILE ptp,
3496 RA_BATCH_SOURCES_ALL rbs,
3497 RA_CUSTOMER_TRX_ALL custtrx_prev,
3498 RA_CUSTOMER_TRX_LINES_ALL custtrxl,
3499 AR_VAT_TAX_ALL_B vat,
3500 ZX_RATES_B rates ,
3501 RA_CUSTOMER_TRX_LINES_ALL custtrxll, -- retrieve the line for tax lines
3502 AR_MEMO_LINES_ALL_B memoline,
3503 ZX_REGIMES_B regimes,
3504 ZX_TAXES_B taxes,
3505 ZX_STATUS_B status
3506 WHERE zxvalerr.other_doc_application_id = 222
3507 AND custtrx.customer_trx_id = zxvalerr.other_doc_trx_id
3508 AND custtrx.customer_trx_id = custtrxl.customer_trx_id
3509 AND custtrx.previous_customer_trx_id = custtrx_prev.customer_trx_id(+)
3510 AND (case when (custtrxl.line_type IN ('LINE' ,'CB')) then custtrxl.customer_trx_line_id
3511 when (custtrxl.line_type = 'TAX') then custtrxl.link_to_cust_trx_line_id
3512 end
3513 ) = custtrxll.customer_trx_line_id
3514 AND ((custtrxl.line_type = 'TAX' AND custtrxll.line_type = 'LINE')
3515 OR
3516 (custtrxl.line_type <> 'TAX')
3517 )
3518 AND custtrx.cust_trx_type_id = types.cust_trx_type_id
3519 AND types.type in ('INV','CM', 'DM')
3520 AND decode(l_multi_org_flag,'N',l_org_id, custtrx.org_id) =
3521 decode(l_multi_org_flag,'N',l_org_id, types.org_id)
3522 AND custtrx.invoice_currency_code = fndcurr.currency_code
3523 AND custtrx.doc_sequence_id = fds.doc_sequence_id (+)
3524 AND ptp.party_id = decode(l_multi_org_flag,'N',l_org_id, custtrx.org_id)
3525 AND ptp.party_type_code = 'OU'
3526 AND custtrx.batch_source_id = rbs.batch_source_id(+)
3527 AND decode(l_multi_org_flag,'N',l_org_id, custtrx.org_id) =
3528 decode(l_multi_org_flag,'N',l_org_id, rbs.org_id(+))
3529 AND custtrxl.vat_tax_id = vat.vat_tax_id(+)
3530 AND custtrx.org_id = arsysparam.org_id
3531 AND custtrxl.vat_Tax_id = rates.tax_rate_id(+)
3532 AND custtrxll.memo_line_id = memoline.memo_line_id(+)
3533 AND decode(l_multi_org_flag,'N',l_org_id, custtrxll.org_id) = decode(l_multi_org_flag,'N',l_org_id, memoline.org_id(+))
3534 AND rates.tax_regime_code = regimes.tax_regime_code(+)
3535 AND rates.tax_regime_code = taxes.tax_regime_code(+)
3536 AND rates.tax = taxes.tax(+)
3537 AND rates.content_owner_id = taxes.content_owner_id(+)
3538 AND rates.tax_regime_code = status.tax_regime_code(+)
3539 AND rates.tax = status.tax(+)
3540 AND rates.tax_status_code = status.tax_status_code(+)
3541 AND rates.content_owner_id = status.content_owner_id(+)
3542 AND NVL(arsysparam.tax_code, '!') <> 'Localization';
3543
3544 IF (g_level_procedure >= g_current_runtime_level ) THEN
3545 FND_LOG.STRING(g_level_procedure,
3546 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AR_PKG.upgrade_trx_on_fly_blk_ar.BEGIN',
3547 'ZX_ON_FLY_TRX_UPGRADE_AR_PKG.upgrade_trx_on_fly_blk_ar(+)');
3548 END IF;
3549
3550 EXCEPTION
3551 WHEN OTHERS THEN
3552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3553 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3554 FND_LOG.STRING(g_level_unexpected,
3555 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AR_PKG.upgrade_trx_on_fly_blk_ar',
3556 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
3557 FND_LOG.STRING(g_level_unexpected,
3558 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AR_PKG.upgrade_trx_on_fly_blk_ar.END',
3559 'ZX_ON_FLY_TRX_UPGRADE_AR_PKG.upgrade_trx_on_fly_blk_ar(-)');
3560 END IF;
3561
3562 END upgrade_trx_on_fly_blk_ar;
3563
3564 END ZX_ON_FLY_TRX_UPGRADE_AR_PKG;
3565