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