[Home] [Help]
PACKAGE BODY: APPS.ZX_ON_FLY_TRX_UPGRADE_AP_PKG
Source
1 PACKAGE BODY ZX_ON_FLY_TRX_UPGRADE_AP_PKG AS
2 /* $Header: zxmigtrxflyapb.pls 120.40 2011/11/25 07:47:17 prigovin 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 -------------------------------------------------------------------------------
12 -- PUBLIC PROCEDURE
13 -- upgrade_trx_on_fly_ap
14 --
15 -- DESCRIPTION
16 -- on the fly migration of one transaction for AP
17 --
18 -------------------------------------------------------------------------------
19
20 PROCEDURE upgrade_trx_on_fly_ap(
21 p_upg_trx_info_rec IN ZX_ON_FLY_TRX_UPGRADE_PKG.zx_upg_trx_info_rec_type,
22 x_return_status OUT NOCOPY VARCHAR2
23 ) AS
24
25 l_multi_org_flag fnd_product_groups.multi_org_flag%TYPE;
26 l_org_id NUMBER;
27 l_estb_id NUMBER;
28
29 BEGIN
30 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
31
32 IF (g_level_procedure >= g_current_runtime_level ) THEN
33 FND_LOG.STRING(g_level_procedure,
34 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_ap.BEGIN',
35 'ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_ap(+)');
36 END IF;
37
38 x_return_status := FND_API.G_RET_STS_SUCCESS;
39 SELECT multi_org_flag INTO l_multi_org_flag FROM fnd_product_groups;
40
41 -- for single org environment, get value of org_id from profile
42 IF l_multi_org_flag = 'N' THEN
43 FND_PROFILE.GET('ORG_ID',l_org_id);
44 IF l_org_id is NULL THEN
45 IF (g_level_unexpected >= g_current_runtime_level ) THEN
46 FND_LOG.STRING(g_level_unexpected,
47 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_blk_ap',
48 'Current envionment is a Single Org environment,'||
49 ' but peofile ORG_ID is not set up');
50 END IF;
51
52 END IF;
53 END IF;
54
55 -- Populating the Establishment ID
56 BEGIN
57 SELECT xle_etb.establishment_id
58 INTO l_estb_id
59 FROM zx_party_tax_profile ptp,
60 xle_etb_profiles xle_etb,
61 ap_invoices_all ai
62 WHERE ai.invoice_id = p_upg_trx_info_rec.trx_id
63 AND ptp.party_id = xle_etb.party_id
64 AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
65 AND xle_etb.legal_entity_id = ai.legal_entity_id
66 AND xle_etb.main_establishment_flag = 'Y'
67 AND ai.invoice_date BETWEEN NVL(xle_etb.main_effective_from,ai.invoice_date)
68 AND NVL(xle_etb.main_effective_to,ai.invoice_date);
69 EXCEPTION
70 WHEN OTHERS THEN
71 l_estb_id := NULL;
72 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
73 FND_LOG.STRING(G_LEVEL_UNEXPECTED,'ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_ap',
74 'Main Establishment Not Defined ' ||SQLERRM);
75 END IF;
76 END;
77
78 IF (g_level_statement >= g_current_runtime_level ) THEN
79 FND_LOG.STRING(g_level_statement,
80 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_ap',
81 'Inserting data into zx_lines_det_factors and zx_lines_summary');
82 END IF;
83
84
85 -- Insert data into zx_lines_det_factors and zx_lines_summary
86 --
87 INSERT ALL
88 WHEN AP_LINE_LOOKUP_CODE IN ('ITEM', 'PREPAY','FREIGHT','MISCELLANEOUS') OR
89 TAX_ONLY_LINE_FLAG = 'Y'
90 THEN
91 INTO ZX_LINES_DET_FACTORS (
92 EVENT_ID
93 ,OBJECT_VERSION_NUMBER
94 ,INTERNAL_ORGANIZATION_ID
95 ,APPLICATION_ID
96 ,ENTITY_CODE
97 ,EVENT_CLASS_CODE
98 ,EVENT_TYPE_CODE
99 ,TAX_EVENT_CLASS_CODE
100 ,TAX_EVENT_TYPE_CODE
101 -- ,DOC_EVENT_STATUS
102 ,LINE_LEVEL_ACTION
103 ,LINE_CLASS
104 -- ,APPLICATION_DOC_STATUS
105 ,TRX_ID
106 ,TRX_LINE_ID
107 ,TRX_LEVEL_TYPE
108 ,TRX_DATE
109 ,LEDGER_ID
110 ,TRX_CURRENCY_CODE
111 ,CURRENCY_CONVERSION_DATE
112 ,CURRENCY_CONVERSION_RATE
113 ,CURRENCY_CONVERSION_TYPE
114 ,MINIMUM_ACCOUNTABLE_UNIT
115 ,PRECISION
116 ,LEGAL_ENTITY_ID
117 ,ESTABLISHMENT_ID
118 ,DEFAULT_TAXATION_COUNTRY
119 ,TRX_NUMBER
120 ,TRX_LINE_NUMBER
121 ,TRX_LINE_DESCRIPTION
122 ,TRX_DESCRIPTION
123 ,TRX_COMMUNICATED_DATE
124 ,TRX_LINE_GL_DATE
125 ,BATCH_SOURCE_ID
126 -- ,BATCH_SOURCE_NAME
127 ,DOC_SEQ_ID
128 ,DOC_SEQ_NAME
129 ,DOC_SEQ_VALUE
130 ,TRX_DUE_DATE
131 -- ,TRX_TYPE_DESCRIPTION
132 ,TRX_LINE_TYPE
133 ,TRX_LINE_DATE
134 -- ,TRX_SHIPPING_DATE
135 -- ,TRX_RECEIPT_DATE
136 ,LINE_AMT
137 ,TRX_LINE_QUANTITY
138 ,UNIT_PRICE
139 ,PRODUCT_ID
140 -- ,PRODUCT_ORG_ID
141 ,UOM_CODE
142 ,PRODUCT_TYPE
143 -- ,PRODUCT_CODE
144 ,PRODUCT_DESCRIPTION
145 ,FIRST_PTY_ORG_ID
146 -- ,ASSET_NUMBER
147 -- ,ASSET_ACCUM_DEPRECIATION
148 -- ,ASSET_TYPE
149 -- ,ASSET_COST
150 ,ACCOUNT_CCID
151 -- ,ACCOUNT_STRING
152 -- ,RELATED_DOC_APPLICATION_ID
153 -- ,RELATED_DOC_ENTITY_CODE
154 -- ,RELATED_DOC_EVENT_CLASS_CODE
155 -- ,RELATED_DOC_TRX_ID
156 -- ,RELATED_DOC_NUMBER
157 -- ,RELATED_DOC_DATE
158 ,APPLIED_FROM_APPLICATION_ID
159 ,APPLIED_FROM_ENTITY_CODE
160 ,APPLIED_FROM_EVENT_CLASS_CODE
161 ,APPLIED_FROM_TRX_ID
162 ,APPLIED_FROM_LINE_ID
163 ,ADJUSTED_DOC_APPLICATION_ID
164 ,ADJUSTED_DOC_ENTITY_CODE
165 ,ADJUSTED_DOC_EVENT_CLASS_CODE
166 ,ADJUSTED_DOC_TRX_ID
167 ,ADJUSTED_DOC_LINE_ID
168 -- ,ADJUSTED_DOC_NUMBER
169 -- ,ADJUSTED_DOC_DATE
170 ,APPLIED_TO_APPLICATION_ID
171 ,APPLIED_TO_ENTITY_CODE
172 ,APPLIED_TO_EVENT_CLASS_CODE
173 ,APPLIED_TO_TRX_ID
174 ,APPLIED_TO_TRX_LINE_ID
175 -- ,APPLIED_TO_TRX_NUMBER
176 ,REF_DOC_TRX_LEVEL_TYPE
177 ,REF_DOC_APPLICATION_ID
178 ,REF_DOC_ENTITY_CODE
179 ,REF_DOC_EVENT_CLASS_CODE
180 ,REF_DOC_TRX_ID
181 ,REF_DOC_LINE_ID
182 -- ,REF_DOC_LINE_QUANTITY
183 ,APPLIED_TO_TRX_LEVEL_TYPE
184 ,APPLIED_FROM_TRX_LEVEL_TYPE
185 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
186 ,MERCHANT_PARTY_NAME
187 ,MERCHANT_PARTY_DOCUMENT_NUMBER
188 ,MERCHANT_PARTY_REFERENCE
189 ,MERCHANT_PARTY_TAXPAYER_ID
190 ,MERCHANT_PARTY_TAX_REG_NUMBER
191 -- ,MERCHANT_PARTY_ID
192 ,MERCHANT_PARTY_COUNTRY
193 ,START_EXPENSE_DATE
194 ,SHIP_TO_LOCATION_ID
195 -- ,SHIP_FROM_LOCATION_ID
196 -- ,BILL_TO_LOCATION_ID
197 -- ,BILL_FROM_LOCATION_ID
198 -- ,SHIP_TO_PARTY_TAX_PROF_ID
199 -- ,SHIP_FROM_PARTY_TAX_PROF_ID
200 -- ,BILL_TO_PARTY_TAX_PROF_ID
201 -- ,BILL_FROM_PARTY_TAX_PROF_ID
202 -- ,SHIP_TO_SITE_TAX_PROF_ID
203 -- ,SHIP_FROM_SITE_TAX_PROF_ID
204 -- ,BILL_TO_SITE_TAX_PROF_ID
205 -- ,BILL_FROM_SITE_TAX_PROF_ID
206 -- ,MERCHANT_PARTY_TAX_PROF_ID
207 -- ,HQ_ESTB_PARTY_TAX_PROF_ID
208 -- ,CTRL_TOTAL_LINE_TX_AMT
209 -- ,CTRL_TOTAL_HDR_TX_AMT
210 -- ,INPUT_TAX_CLASSIFICATION_CODE
211 -- ,OUTPUT_TAX_CLASSIFICATION_CODE
212 -- ,INTERNAL_ORG_LOCATION_ID
213 ,RECORD_TYPE_CODE
214 ,PRODUCT_FISC_CLASSIFICATION
215 ,PRODUCT_CATEGORY
216 ,USER_DEFINED_FISC_CLASS
217 ,ASSESSABLE_VALUE
218 ,TRX_BUSINESS_CATEGORY
219 ,SUPPLIER_TAX_INVOICE_NUMBER
220 ,SUPPLIER_TAX_INVOICE_DATE
221 ,SUPPLIER_EXCHANGE_RATE
222 ,TAX_INVOICE_DATE
223 ,TAX_INVOICE_NUMBER
224 ,DOCUMENT_SUB_TYPE
225 ,LINE_INTENDED_USE
226 ,PORT_OF_ENTRY_CODE
227 -- ,SOURCE_APPLICATION_ID
228 -- ,SOURCE_ENTITY_CODE
229 -- ,SOURCE_EVENT_CLASS_CODE
230 -- ,SOURCE_TRX_ID
231 -- ,SOURCE_LINE_ID
232 -- ,SOURCE_TRX_LEVEL_TYPE
233 ,HISTORICAL_FLAG
234 ,LINE_AMT_INCLUDES_TAX_FLAG
235 ,CTRL_HDR_TX_APPL_FLAG
236 ,TAX_REPORTING_FLAG
237 ,TAX_AMT_INCLUDED_FLAG
238 ,COMPOUNDING_TAX_FLAG
239 ,INCLUSIVE_TAX_OVERRIDE_FLAG
240 ,THRESHOLD_INDICATOR_FLAG
241 ,USER_UPD_DET_FACTORS_FLAG
242 ,TAX_PROCESSING_COMPLETED_FLAG
243 ,ASSET_FLAG
244 ,CREATION_DATE
245 ,CREATED_BY
246 ,LAST_UPDATE_DATE
247 ,LAST_UPDATED_BY
248 ,LAST_UPDATE_LOGIN
249 ,EVENT_CLASS_MAPPING_ID
250 ,SHIP_THIRD_PTY_ACCT_ID
251 ,SHIP_THIRD_PTY_ACCT_SITE_ID
252 ,GLOBAL_ATTRIBUTE_CATEGORY
253 ,GLOBAL_ATTRIBUTE1
254 -- ,ICX_SESSION_ID
255 -- ,TRX_LINE_CURRENCY_CODE
256 -- ,TRX_LINE_CURRENCY_CONV_RATE
257 -- ,TRX_LINE_CURRENCY_CONV_DATE
258 -- ,TRX_LINE_PRECISION
259 -- ,TRX_LINE_MAU
260 -- ,TRX_LINE_CURRENCY_CONV_TYPE
261 -- ,INTERFACE_ENTITY_CODE
262 -- ,INTERFACE_LINE_ID
263 -- ,SOURCE_TAX_LINE_ID
264 ,BILL_THIRD_PTY_ACCT_ID
265 ,BILL_THIRD_PTY_ACCT_SITE_ID
266 )
267 VALUES(
268 EVENT_ID
269 ,OBJECT_VERSION_NUMBER
270 ,INTERNAL_ORGANIZATION_ID
271 ,APPLICATION_ID
272 ,ENTITY_CODE
273 ,EVENT_CLASS_CODE
274 ,EVENT_TYPE_CODE
275 ,TAX_EVENT_CLASS_CODE
276 ,TAX_EVENT_TYPE_CODE
277 -- ,DOC_EVENT_STATUS
278 ,LINE_LEVEL_ACTION
279 ,LINE_CLASS
280 -- ,APPLICATION_DOC_STATUS
281 ,TRX_ID
282 ,TRX_LINE_ID
283 ,TRX_LEVEL_TYPE
284 ,TRX_DATE
285 ,LEDGER_ID
286 ,TRX_CURRENCY_CODE
287 ,CURRENCY_CONVERSION_DATE
288 ,CURRENCY_CONVERSION_RATE
289 ,CURRENCY_CONVERSION_TYPE
290 ,MINIMUM_ACCOUNTABLE_UNIT
291 ,PRECISION
292 ,LEGAL_ENTITY_ID
293 ,ESTABLISHMENT_ID
294 ,DEFAULT_TAXATION_COUNTRY
295 ,TRX_NUMBER
296 ,TRX_LINE_NUMBER
297 ,TRX_LINE_DESCRIPTION
298 ,TRX_DESCRIPTION
299 ,TRX_COMMUNICATED_DATE
300 ,TRX_LINE_GL_DATE
301 ,BATCH_SOURCE_ID
302 -- ,BATCH_SOURCE_NAME
303 ,DOC_SEQ_ID
304 ,DOC_SEQ_NAME
305 ,DOC_SEQ_VALUE
306 ,TRX_DUE_DATE
307 -- ,TRX_TYPE_DESCRIPTION
308 ,TRX_LINE_TYPE
309 ,TRX_LINE_DATE
310 -- ,TRX_SHIPPING_DATE
311 -- ,TRX_RECEIPT_DATE
312 ,LINE_AMT
313 ,TRX_LINE_QUANTITY
314 ,UNIT_PRICE
315 ,PRODUCT_ID
316 -- ,PRODUCT_ORG_ID
317 ,UOM_CODE
318 ,PRODUCT_TYPE
319 -- ,PRODUCT_CODE
320 ,PRODUCT_DESCRIPTION
321 ,FIRST_PTY_ORG_ID
322 -- ,ASSET_NUMBER
323 -- ,ASSET_ACCUM_DEPRECIATION
324 -- ,ASSET_TYPE
325 -- ,ASSET_COST
326 ,ACCOUNT_CCID
327 -- ,ACCOUNT_STRING
328 -- ,RELATED_DOC_APPLICATION_ID
329 -- ,RELATED_DOC_ENTITY_CODE
330 -- ,RELATED_DOC_EVENT_CLASS_CODE
331 -- ,RELATED_DOC_TRX_ID
332 -- ,RELATED_DOC_NUMBER
333 -- ,RELATED_DOC_DATE
334 ,APPLIED_FROM_APPLICATION_ID
335 ,APPLIED_FROM_ENTITY_CODE
336 ,APPLIED_FROM_EVENT_CLASS_CODE
337 ,APPLIED_FROM_TRX_ID
338 ,APPLIED_FROM_LINE_ID
339 ,ADJUSTED_DOC_APPLICATION_ID
340 ,ADJUSTED_DOC_ENTITY_CODE
341 ,ADJUSTED_DOC_EVENT_CLASS_CODE
342 ,ADJUSTED_DOC_TRX_ID
343 ,ADJUSTED_DOC_LINE_ID
344 -- ,ADJUSTED_DOC_NUMBER
345 -- ,ADJUSTED_DOC_DATE
346 ,APPLIED_TO_APPLICATION_ID
347 ,APPLIED_TO_ENTITY_CODE
348 ,APPLIED_TO_EVENT_CLASS_CODE
349 ,APPLIED_TO_TRX_ID
350 ,APPLIED_TO_TRX_LINE_ID
351 -- ,APPLIED_TO_TRX_NUMBER
352 ,REF_DOC_TRX_LEVEL_TYPE
353 ,REF_DOC_APPLICATION_ID
354 ,REF_DOC_ENTITY_CODE
355 ,REF_DOC_EVENT_CLASS_CODE
356 ,REF_DOC_TRX_ID
357 ,REF_DOC_LINE_ID
358 -- ,REF_DOC_LINE_QUANTITY
359 ,APPLIED_TO_TRX_LEVEL_TYPE
360 ,APPLIED_FROM_TRX_LEVEL_TYPE
361 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
362 ,MERCHANT_PARTY_NAME
363 ,MERCHANT_PARTY_DOCUMENT_NUMBER
364 ,MERCHANT_PARTY_REFERENCE
365 ,MERCHANT_PARTY_TAXPAYER_ID
366 ,MERCHANT_PARTY_TAX_REG_NUMBER
367 -- ,MERCHANT_PARTY_ID
368 ,MERCHANT_PARTY_COUNTRY
369 ,START_EXPENSE_DATE
370 ,SHIP_TO_LOCATION_ID
371 -- ,SHIP_FROM_LOCATION_ID
372 -- ,BILL_TO_LOCATION_ID
373 -- ,BILL_FROM_LOCATION_ID
374 -- ,SHIP_TO_PARTY_TAX_PROF_ID
375 -- ,SHIP_FROM_PARTY_TAX_PROF_ID
376 -- ,BILL_TO_PARTY_TAX_PROF_ID
377 -- ,BILL_FROM_PARTY_TAX_PROF_ID
378 -- ,SHIP_TO_SITE_TAX_PROF_ID
379 -- ,SHIP_FROM_SITE_TAX_PROF_ID
380 -- ,BILL_TO_SITE_TAX_PROF_ID
381 -- ,BILL_FROM_SITE_TAX_PROF_ID
382 -- ,MERCHANT_PARTY_TAX_PROF_ID
383 -- ,HQ_ESTB_PARTY_TAX_PROF_ID
384 -- ,CTRL_TOTAL_LINE_TX_AMT
385 -- ,CTRL_TOTAL_HDR_TX_AMT
386 -- ,INPUT_TAX_CLASSIFICATION_CODE
387 -- ,OUTPUT_TAX_CLASSIFICATION_CODE
388 -- ,INTERNAL_ORG_LOCATION_ID
389 ,RECORD_TYPE_CODE
390 ,PRODUCT_FISC_CLASSIFICATION
391 ,PRODUCT_CATEGORY
392 ,USER_DEFINED_FISC_CLASS
393 ,ASSESSABLE_VALUE
394 ,TRX_BUSINESS_CATEGORY
395 ,SUPPLIER_TAX_INVOICE_NUMBER
396 ,SUPPLIER_TAX_INVOICE_DATE
397 ,SUPPLIER_EXCHANGE_RATE
398 ,TAX_INVOICE_DATE
399 ,TAX_INVOICE_NUMBER
400 ,DOCUMENT_SUB_TYPE
401 ,LINE_INTENDED_USE
402 ,PORT_OF_ENTRY_CODE
403 -- ,SOURCE_APPLICATION_ID
404 -- ,SOURCE_ENTITY_CODE
405 -- ,SOURCE_EVENT_CLASS_CODE
406 -- ,SOURCE_TRX_ID
407 -- ,SOURCE_LINE_ID
408 -- ,SOURCE_TRX_LEVEL_TYPE
409 ,HISTORICAL_FLAG
410 ,LINE_AMT_INCLUDES_TAX_FLAG
411 ,CTRL_HDR_TX_APPL_FLAG
412 ,TAX_REPORTING_FLAG
413 ,TAX_AMT_INCLUDED_FLAG
414 ,COMPOUNDING_TAX_FLAG
415 ,INCLUSIVE_TAX_OVERRIDE_FLAG
416 ,THRESHOLD_INDICATOR_FLAG
417 ,USER_UPD_DET_FACTORS_FLAG
418 ,TAX_PROCESSING_COMPLETED_FLAG
419 ,ASSET_FLAG
420 ,CREATION_DATE
421 ,CREATED_BY
422 ,LAST_UPDATE_DATE
423 ,LAST_UPDATED_BY
424 ,LAST_UPDATE_LOGIN
425 ,EVENT_CLASS_MAPPING_ID
426 ,SHIP_THIRD_PTY_ACCT_ID
427 ,SHIP_THIRD_PTY_ACCT_SITE_ID
428 ,GLOBAL_ATTRIBUTE_CATEGORY
429 ,GLOBAL_ATTRIBUTE1
430 -- ,ICX_SESSION_ID
431 -- ,TRX_LINE_CURRENCY_CODE
432 -- ,TRX_LINE_CURRENCY_CONV_RATE
433 -- ,TRX_LINE_CURRENCY_CONV_DATE
434 -- ,TRX_LINE_PRECISION
435 -- ,TRX_LINE_MAU
436 -- ,TRX_LINE_CURRENCY_CONV_TYPE
437 -- ,INTERFACE_ENTITY_CODE
438 -- ,INTERFACE_LINE_ID
439 -- ,SOURCE_TAX_LINE_ID
440 ,BILL_THIRD_PTY_ACCT_ID
441 ,BILL_THIRD_PTY_ACCT_SITE_ID
442 )
443 WHEN AP_LINE_LOOKUP_CODE = 'TAX' THEN
444 INTO ZX_LINES_SUMMARY (
445 SUMMARY_TAX_LINE_ID
446 ,INTERNAL_ORGANIZATION_ID
447 ,APPLICATION_ID
448 ,ENTITY_CODE
449 ,EVENT_CLASS_CODE
450 ,TRX_ID
451 ,TRX_NUMBER
452 ,APPLIED_FROM_APPLICATION_ID
453 ,APPLIED_FROM_EVENT_CLASS_CODE
454 ,APPLIED_FROM_ENTITY_CODE
455 ,APPLIED_FROM_TRX_ID
456 ,ADJUSTED_DOC_APPLICATION_ID
457 ,ADJUSTED_DOC_ENTITY_CODE
458 ,ADJUSTED_DOC_EVENT_CLASS_CODE
459 ,ADJUSTED_DOC_TRX_ID
460 ,SUMMARY_TAX_LINE_NUMBER
461 ,CONTENT_OWNER_ID
462 ,TAX_REGIME_CODE
463 ,TAX
464 ,TAX_STATUS_CODE
465 ,TAX_RATE_ID
466 ,TAX_RATE_CODE
467 ,TAX_RATE
468 ,TAX_AMT
469 ,TAX_AMT_TAX_CURR
470 ,TAX_AMT_FUNCL_CURR
471 ,TAX_JURISDICTION_CODE
472 ,TOTAL_REC_TAX_AMT
473 ,TOTAL_REC_TAX_AMT_FUNCL_CURR
474 ,TOTAL_NREC_TAX_AMT
475 ,TOTAL_NREC_TAX_AMT_FUNCL_CURR
476 ,LEDGER_ID
477 ,LEGAL_ENTITY_ID
478 ,ESTABLISHMENT_ID
479 ,CURRENCY_CONVERSION_DATE
480 ,CURRENCY_CONVERSION_TYPE
481 ,CURRENCY_CONVERSION_RATE
482 -- ,SUMMARIZATION_TEMPLATE_ID
483 ,TAXABLE_BASIS_FORMULA
484 ,TAX_CALCULATION_FORMULA
485 ,HISTORICAL_FLAG
486 ,CANCEL_FLAG
487 ,DELETE_FLAG
488 ,TAX_AMT_INCLUDED_FLAG
489 ,COMPOUNDING_TAX_FLAG
490 ,SELF_ASSESSED_FLAG
491 ,OVERRIDDEN_FLAG
492 ,REPORTING_ONLY_FLAG
493 ,ASSOCIATED_CHILD_FROZEN_FLAG
494 ,COPIED_FROM_OTHER_DOC_FLAG
495 ,MANUALLY_ENTERED_FLAG
496 ,LAST_MANUAL_ENTRY --BUG7146063
497 ,RECORD_TYPE_CODE
498 -- ,TAX_PROVIDER_ID
499 ,TAX_ONLY_LINE_FLAG
500 ,CREATED_BY
501 ,CREATION_DATE
502 ,LAST_UPDATED_BY
503 ,LAST_UPDATE_DATE
504 ,LAST_UPDATE_LOGIN
505 ,ATTRIBUTE_CATEGORY
506 ,ATTRIBUTE1
507 ,ATTRIBUTE2
508 ,ATTRIBUTE3
509 ,ATTRIBUTE4
510 ,ATTRIBUTE5
511 ,ATTRIBUTE6
512 ,ATTRIBUTE7
513 ,ATTRIBUTE8
514 ,ATTRIBUTE9
515 ,ATTRIBUTE10
516 ,ATTRIBUTE11
517 ,ATTRIBUTE12
518 ,ATTRIBUTE13
519 ,ATTRIBUTE14
520 ,ATTRIBUTE15
521 ,APPLIED_FROM_LINE_ID
522 ,APPLIED_TO_APPLICATION_ID
523 ,APPLIED_TO_EVENT_CLASS_CODE
524 ,APPLIED_TO_ENTITY_CODE
525 ,APPLIED_TO_TRX_ID
526 ,APPLIED_TO_LINE_ID
527 -- ,TAX_EXEMPTION_ID
528 -- ,TAX_RATE_BEFORE_EXEMPTION
529 -- ,TAX_RATE_NAME_BEFORE_EXEMPTION
530 -- ,EXEMPT_RATE_MODIFIER
531 -- ,EXEMPT_CERTIFICATE_NUMBER
532 -- ,EXEMPT_REASON
533 -- ,EXEMPT_REASON_CODE
534 -- ,TAX_RATE_BEFORE_EXCEPTION
535 -- ,TAX_RATE_NAME_BEFORE_EXCEPTION
536 -- ,TAX_EXCEPTION_ID
537 -- ,EXCEPTION_RATE
538 ,TOTAL_REC_TAX_AMT_TAX_CURR
539 ,TOTAL_NREC_TAX_AMT_TAX_CURR
540 ,MRC_TAX_LINE_FLAG
541 ,GLOBAL_ATTRIBUTE_CATEGORY
542 ,GLOBAL_ATTRIBUTE1
543 ,GLOBAL_ATTRIBUTE2
544 ,GLOBAL_ATTRIBUTE3
545 ,GLOBAL_ATTRIBUTE4
546 ,GLOBAL_ATTRIBUTE5
547 ,GLOBAL_ATTRIBUTE6
548 ,GLOBAL_ATTRIBUTE7
549 ,GLOBAL_ATTRIBUTE8
550 ,GLOBAL_ATTRIBUTE9
551 ,GLOBAL_ATTRIBUTE10
552 ,GLOBAL_ATTRIBUTE11
553 ,GLOBAL_ATTRIBUTE12
554 ,GLOBAL_ATTRIBUTE13
555 ,GLOBAL_ATTRIBUTE14
556 ,GLOBAL_ATTRIBUTE15
557 ,GLOBAL_ATTRIBUTE16
558 ,GLOBAL_ATTRIBUTE17
559 ,GLOBAL_ATTRIBUTE18
560 ,GLOBAL_ATTRIBUTE19
561 ,GLOBAL_ATTRIBUTE20
562 ,APPLIED_FROM_TRX_LEVEL_TYPE
563 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
564 ,APPLIED_TO_TRX_LEVEL_TYPE
565 ,TRX_LEVEL_TYPE
566 ,ADJUST_TAX_AMT_FLAG
567 ,OBJECT_VERSION_NUMBER)
568 VALUES(
569 SUMMARY_TAX_LINE_ID
570 ,INTERNAL_ORGANIZATION_ID
571 ,APPLICATION_ID
572 ,ENTITY_CODE
573 ,EVENT_CLASS_CODE
574 ,TRX_ID
575 ,TRX_NUMBER
576 ,APPLIED_FROM_APPLICATION_ID
577 ,APPLIED_FROM_EVENT_CLASS_CODE
578 ,APPLIED_FROM_ENTITY_CODE
579 ,APPLIED_FROM_TRX_ID
580 ,ADJUSTED_DOC_APPLICATION_ID
581 ,ADJUSTED_DOC_ENTITY_CODE
582 ,ADJUSTED_DOC_EVENT_CLASS_CODE
583 ,ADJUSTED_DOC_TRX_ID
584 ,SUMMARY_TAX_LINE_NUMBER
585 ,CONTENT_OWNER_ID
586 ,TAX_REGIME_CODE
587 ,TAX
588 ,TAX_STATUS_CODE
589 ,TAX_RATE_ID
590 ,TAX_RATE_CODE
591 ,TAX_RATE
592 ,TAX_AMT
593 ,TAX_AMT_TAX_CURR
594 ,TAX_AMT_FUNCL_CURR
595 ,TAX_JURISDICTION_CODE
596 ,TOTAL_REC_TAX_AMT
597 ,TOTAL_REC_TAX_AMT_FUNCL_CURR
598 ,TOTAL_NREC_TAX_AMT
599 ,TOTAL_NREC_TAX_AMT_FUNCL_CURR
600 ,LEDGER_ID
601 ,LEGAL_ENTITY_ID
602 ,ESTABLISHMENT_ID
603 ,CURRENCY_CONVERSION_DATE
604 ,CURRENCY_CONVERSION_TYPE
605 ,CURRENCY_CONVERSION_RATE
606 -- ,NULL -- SUMMARIZATION_TEMPLATE_ID
607 -- Bug 9037817 'STANDARD_TB'
608 ,TAXABLE_BASIS_FORMULA
609 ,'STANDARD_TC' -- TAX_CALCULATION_FORMULA
610 ,HISTORICAL_FLAG
611 ,CANCEL_FLAG
612 ,'N' -- DELETE_FLAG
613 ,TAX_AMT_INCLUDED_FLAG
614 ,COMPOUNDING_TAX_FLAG
615 ,SELF_ASSESSED_FLAG
616 ,OVERRIDDEN_FLAG
617 ,'N' -- REPORTING_ONLY_FLAG
618 ,'N' -- ASSOCIATED_CHILD_FROZEN_FLAG
619 ,'N' -- COPIED_FROM_OTHER_DOC_FLAG
620 ,MANUALLY_ENTERED_FLAG --BUG7146063
621 ,LAST_MANUAL_ENTRY --BUG7146063
622 ,RECORD_TYPE_CODE
623 -- ,NULL -- TAX_PROVIDER_ID
624 ,TAX_ONLY_LINE_FLAG
625 ,CREATED_BY
626 ,CREATION_DATE
627 ,LAST_UPDATED_BY
628 ,LAST_UPDATE_DATE
629 ,LAST_UPDATE_LOGIN
630 ,ATTRIBUTE_CATEGORY
631 ,ATTRIBUTE1
632 ,ATTRIBUTE2
633 ,ATTRIBUTE3
634 ,ATTRIBUTE4
635 ,ATTRIBUTE5
636 ,ATTRIBUTE6
637 ,ATTRIBUTE7
638 ,ATTRIBUTE8
639 ,ATTRIBUTE9
640 ,ATTRIBUTE10
641 ,ATTRIBUTE11
642 ,ATTRIBUTE12
643 ,ATTRIBUTE13
644 ,ATTRIBUTE14
645 ,ATTRIBUTE15
646 ,APPLIED_FROM_LINE_ID
647 ,APPLIED_TO_APPLICATION_ID
648 ,APPLIED_TO_EVENT_CLASS_CODE
649 ,APPLIED_TO_ENTITY_CODE
650 ,APPLIED_TO_TRX_ID
651 ,APPLIED_TO_TRX_LINE_ID -- APPLIED_TO_LINE_ID
652 -- ,NULL -- TAX_EXEMPTION_ID
653 -- ,NULL -- TAX_RATE_BEFORE_EXEMPTION
654 -- ,NULL -- TAX_RATE_NAME_BEFORE_EXEMPTION
655 -- ,NULL -- EXEMPT_RATE_MODIFIER
656 -- ,NULL -- EXEMPT_CERTIFICATE_NUMBER
657 -- ,NULL -- EXEMPT_REASON
658 -- ,NULL -- EXEMPT_REASON_CODE
659 -- ,NULL -- TAX_RATE_BEFORE_EXCEPTION
660 -- ,NULL -- TAX_RATE_NAME_BEFORE_EXCEPTION
661 -- ,NULL -- TAX_EXCEPTION_ID
662 -- ,NULL -- EXCEPTION_RATE
663 ,TOTAL_REC_TAX_AMT_FUNCL_CURR
664 ,TOTAL_NREC_TAX_AMT_FUNCL_CURR
665 ,'N' -- MRC_TAX_LINE_FLAG
666 ,GLOBAL_ATTRIBUTE_CATEGORY
667 ,GLOBAL_ATTRIBUTE1
668 ,GLOBAL_ATTRIBUTE2
669 ,GLOBAL_ATTRIBUTE3
670 ,GLOBAL_ATTRIBUTE4
671 ,GLOBAL_ATTRIBUTE5
672 ,GLOBAL_ATTRIBUTE6
673 ,GLOBAL_ATTRIBUTE7
674 ,GLOBAL_ATTRIBUTE8
675 ,GLOBAL_ATTRIBUTE9
676 ,GLOBAL_ATTRIBUTE10
677 ,GLOBAL_ATTRIBUTE11
678 ,GLOBAL_ATTRIBUTE12
679 ,GLOBAL_ATTRIBUTE13
680 ,GLOBAL_ATTRIBUTE14
681 ,GLOBAL_ATTRIBUTE15
682 ,GLOBAL_ATTRIBUTE16
683 ,GLOBAL_ATTRIBUTE17
684 ,GLOBAL_ATTRIBUTE18
685 ,GLOBAL_ATTRIBUTE19
686 ,GLOBAL_ATTRIBUTE20
687 ,APPLIED_FROM_TRX_LEVEL_TYPE
688 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
689 ,APPLIED_TO_TRX_LEVEL_TYPE
690 ,TRX_LEVEL_TYPE
691 ,NULL -- ADJUST_TAX_AMT_FLAG
692 ,OBJECT_VERSION_NUMBER
693 )
694 (SELECT /*+ ROWID(inv) ORDERED use_nl(fnd_curr,fds,lines,poll,ptp)*/
695 NULL EVENT_ID
696 ,1 OBJECT_VERSION_NUMBER
697 ,NVL(lines.org_id,-99) INTERNAL_ORGANIZATION_ID
698 ,200 APPLICATION_ID
699 ,'AP_INVOICES' ENTITY_CODE
700 ,DECODE(inv.INVOICE_TYPE_LOOKUP_CODE,
701 'STANDARD', 'STANDARD INVOICES',
702 'CREDIT' , 'STANDARD INVOICES', --Bug 6489409
703 'DEBIT' , 'STANDARD INVOICES', --Bug 6489409
704 'MIXED' , 'STANDARD INVOICES', --Bug 6489409
705 'ADJUSTMENT','STANDARD INVOICES', --Bug 6489409
706 'PO PRICE ADJUST','STANDARD INVOICES', --Bug 6489409
707 'INVOICE REQUEST','STANDARD INVOICES', --Bug 6489409
708 'CREDIT MEMO REQUEST','STANDARD INVOICES',--Bug 6489409
709 'RETAINAGE RELEASE' ,'STANDARD INVOICES',--Bug 6489409
710 'PREPAYMENT', 'PREPAYMENT INVOICES',
711 'EXPENSE REPORT', 'EXPENSE REPORTS',
712 'INTEREST INVOICE', 'INTEREST INVOICES','NA') EVENT_CLASS_CODE
713 ,DECODE(inv.INVOICE_TYPE_LOOKUP_CODE, 'STANDARD', 1,
714 'PREPAYMENT', 7, 'EXPENSE REPORT', 2, NULL) EVENT_CLASS_MAPPING_ID
715 ,DECODE(inv.INVOICE_TYPE_LOOKUP_CODE,
716 'STANDARD','STANDARD INVOICE CREATED',
717 'PREPAYMENT','PREPAYMENT INVOICE CREATED',
718 'EXPENSE REPORT','EXPENSE REPORT CREATED',
719 'INTEREST INVOICE','INTEREST INVOICE CREATED','NA') EVENT_TYPE_CODE
720 ,(CASE
721 WHEN inv.invoice_type_lookup_code in
722 ('ADJUSTMENT','CREDIT','DEBIT','INTEREST',
723 'MIXED','QUICKDEFAULT','PO PRICE ADJUST',
724 'QUICKMATCH','STANDARD','AWT')
725 THEN 'PURCHASE_TRANSACTION'
726 WHEN inv.invoice_type_lookup_code = 'PREPAYMENT'
727 THEN 'PURCHASE_PREPAYMENTTRANSACTION'
728 WHEN inv.invoice_type_lookup_code='EXPENSE REPORT'
729 THEN 'EXPENSE_REPORT'
730 ELSE NULL
731 END) TAX_EVENT_CLASS_CODE
732 ,'VALIDATE' TAX_EVENT_TYPE_CODE
733 -- ,NULL DOC_EVENT_STATUS
734 ,'CREATE' LINE_LEVEL_ACTION
735 ,DECODE(lines.po_line_location_id,
736 NULL, DECODE(lines.line_type_lookup_code,
737 'PREPAY', 'PREPAY_APPLICATION',
738 DECODE(inv.invoice_type_lookup_code,
739 'STANDARD', 'STANDARD INVOICES',
740 'CREDIT','AP_CREDIT_MEMO',
741 'CREDIT MEMO REQUEST', 'AP_CREDIT_MEMO',
742 'DEBIT','AP_DEBIT_MEMO',
743 'PREPAYMENT','PREPAYMENT INVOICES',
744 'EXPENSE REPORT','EXPENSE REPORTS',
745 'STANDARD INVOICES'
746 )
747 ),
748 DECODE(poll.shipment_type,
749 'PREPAYMENT', DECODE(poll.payment_type,
750 'ADVANCE', 'ADVANCE',
751 'MILESTONE', 'FINANCING',
752 'RATE', 'FINANCING',
753 'LUMPSUM', 'FINANCING',
754 DECODE(poll.matching_basis,
755 'AMOUNT','AMOUNT_MATCHED',
756 'STANDARD INVOICES')
757 ),
758 DECODE(poll.matching_basis,
759 'AMOUNT','AMOUNT_MATCHED',
760 'STANDARD INVOICES')
761 )
762 ) LINE_CLASS
763 -- ,NULL APPLICATION_DOC_STATUS
764 ,lines.line_type_lookup_code AP_LINE_LOOKUP_CODE
765 ,lines.invoice_id TRX_ID
766 ,NVL(inv.invoice_date,sysdate) TRX_DATE
767 ,lines.set_of_books_id LEDGER_ID
768 ,inv.invoice_currency_code TRX_CURRENCY_CODE
769 ,NVL(inv.legal_entity_id, -99) LEGAL_ENTITY_ID
770 ,l_estb_id ESTABLISHMENT_ID
771 ,inv.taxation_country DEFAULT_TAXATION_COUNTRY
772 ,inv.invoice_num TRX_NUMBER
773 ,lines.description TRX_LINE_DESCRIPTION
774 ,inv.description TRX_DESCRIPTION
775 ,inv.invoice_received_date TRX_COMMUNICATED_DATE
776 ,NVL(lines.accounting_date,sysdate) TRX_LINE_GL_DATE
777 ,inv.batch_id BATCH_SOURCE_ID
778 -- ,NULL BATCH_SOURCE_NAME
779 ,inv.doc_sequence_id DOC_SEQ_ID
780 ,fds.name DOC_SEQ_NAME
781 ,inv.doc_sequence_value DOC_SEQ_VALUE
782 ,inv.terms_date TRX_DUE_DATE
783 -- ,NULL TRX_TYPE_DESCRIPTION
784 ,lines.line_type_lookup_code TRX_LINE_TYPE
785 ,lines.accounting_date TRX_LINE_DATE
786 -- ,NULL TRX_SHIPPING_DATE
787 -- ,NULL TRX_RECEIPT_DATE
788 ,NVL(lines.amount,0) LINE_AMT
789 ,lines.quantity_invoiced TRX_LINE_QUANTITY
790 ,lines.unit_price -- UNIT_PRICE
791 ,lines.inventory_item_id PRODUCT_ID
792 -- ,NULL PRODUCT_ORG_ID
793 ,lines.unit_meas_lookup_code UOM_CODE
794 ,lines.product_type -- PRODUCT_TYPE
795 -- ,NULL PRODUCT_CODE
796 ,lines.item_description PRODUCT_DESCRIPTION
797 ,ptp.party_tax_profile_id FIRST_PTY_ORG_ID
798 -- ,NULL ASSET_NUMBER
799 -- ,NULL ASSET_ACCUM_DEPRECIATION
800 -- ,NULL ASSET_TYPE
801 -- ,NULL ASSET_COST
802 -- ,NULL RELATED_DOC_APPLICATION_ID,
803 -- ,NULL RELATED_DOC_ENTITY_CODE
804 -- ,NULL RELATED_DOC_EVENT_CLASS_CODE
805 -- ,NULL RELATED_DOC_TRX_ID
806 -- ,NULL RELATED_DOC_NUMBER
807 -- ,NULL RELATED_DOC_DATE
808 ,DECODE(lines.prepay_invoice_id, NULL, NULL, 200) APPLIED_FROM_APPLICATION_ID
809 ,DECODE(lines.prepay_invoice_id, NULL, NULL,
810 'AP_INVOICES') APPLIED_FROM_ENTITY_CODE
811 ,DECODE(lines.prepay_invoice_id, NULL, NULL,
812 'PREPAYMENT INVOICES') APPLIED_FROM_EVENT_CLASS_CODE
813 ,lines.prepay_invoice_id APPLIED_FROM_TRX_ID
814 ,lines.prepay_line_number APPLIED_FROM_LINE_ID
815 ,DECODE(lines.corrected_inv_id, NULL, NULL, 200) ADJUSTED_DOC_APPLICATION_ID
816 ,DECODE(lines.corrected_inv_id, NULL, NULL,
817 'AP_INVOICES') ADJUSTED_DOC_ENTITY_CODE
818 ,DECODE(lines.corrected_inv_id, NULL, NULL,
819 'STANDARD INVOICES') ADJUSTED_DOC_EVENT_CLASS_CODE
820 ,lines.corrected_inv_id ADJUSTED_DOC_TRX_ID
821 ,lines.corrected_line_number ADJUSTED_DOC_LINE_ID
822 -- ,NULL ADJUSTED_DOC_NUMBER
823 -- ,NULL ADJUSTED_DOC_DATE
824 ,DECODE(lines.rcv_transaction_id, NULL, NULL, 707) APPLIED_TO_APPLICATION_ID
825 ,DECODE(lines.rcv_transaction_id, NULL, NULL,
826 'RCV_ACCOUNTING_EVENTS') APPLIED_TO_ENTITY_CODE
827 ,DECODE(lines.rcv_transaction_id, NULL, NULL,
828 'RCPT_REC_INSP') APPLIED_TO_EVENT_CLASS_CODE
829 ,lines.rcv_transaction_id APPLIED_TO_TRX_ID
830 ,lines.rcv_shipment_line_id APPLIED_TO_TRX_LINE_ID
831 -- ,NULL APPLIED_TO_TRX_NUMBER
832 ,DECODE(NVL(lines.po_release_id, lines.po_header_id),
833 NULL, NULL, 'SHIPMENT') REF_DOC_TRX_LEVEL_TYPE
834 ,NVL(lines.po_release_id, lines.po_header_id) REF_DOC_TRX_ID
835 ,lines.po_line_location_id REF_DOC_LINE_ID
836 -- ,NULL REF_DOC_LINE_QUANTITY
837 ,DECODE(lines.rcv_transaction_id, NULL, NULL,
838 'LINE') APPLIED_TO_TRX_LEVEL_TYPE
839 ,DECODE(lines.prepay_invoice_id, NULL, NULL,
840 'LINE') APPLIED_FROM_TRX_LEVEL_TYPE
841 ,DECODE(lines.corrected_inv_id, NULL, NULL,
842 'LINE') ADJUSTED_DOC_TRX_LEVEL_TYPE
843 ,lines.merchant_name MERCHANT_PARTY_NAME
844 ,lines.merchant_document_number MERCHANT_PARTY_DOCUMENT_NUMBER
845 ,lines.merchant_reference MERCHANT_PARTY_REFERENCE
846 ,lines.merchant_taxpayer_id MERCHANT_PARTY_TAXPAYER_ID
847 ,lines.merchant_tax_reg_number MERCHANT_PARTY_TAX_REG_NUMBER
848 -- ,NULL MERCHANT_PARTY_ID
849 ,lines.country_of_supply MERCHANT_PARTY_COUNTRY
850 ,lines.start_expense_date -- START_EXPENSE_DATE
851 ,lines.ship_to_location_id -- SHIP_TO_LOCATION_ID
852 -- ,NULL SHIP_FROM_LOCATION_ID
853 -- ,NULL BILL_TO_LOCATION_ID
854 -- ,NULL BILL_FROM_LOCATION_ID
855 -- ,NULL SHIP_TO_PARTY_TAX_PROF_ID
856 -- ,NULL SHIP_FROM_PARTY_TAX_PROF_ID
857 -- ,NULL BILL_TO_PARTY_TAX_PROF_ID
858 -- ,NULL BILL_FROM_PARTY_TAX_PROF_ID
859 -- ,NULL SHIP_TO_SITE_TAX_PROF_ID
860 -- ,NULL SHIP_FROM_SITE_TAX_PROF_ID
861 -- ,NULL BILL_TO_SITE_TAX_PROF_ID
862 -- ,NULL BILL_FROM_SITE_TAX_PROF_ID
863 -- ,NULL MERCHANT_PARTY_TAX_PROF_ID
864 -- ,NULL HQ_ESTB_PARTY_TAX_PROF_ID
865 -- ,NULL CTRL_TOTAL_LINE_TX_AMT
866 -- ,NULL CTRL_TOTAL_HDR_TX_AMT
867 -- ,NULL INPUT_TAX_CLASSIFICATION_CODE
868 -- ,NULL OUTPUT_TAX_CLASSIFICATION_CODE
869 -- ,NULL INTERNAL_ORG_LOCATION_ID
870 ,'MIGRATED' RECORD_TYPE_CODE
871 ,lines.product_fisc_classification -- PRODUCT_FISC_CLASSIFICATION
872 ,lines.product_category -- PRODUCT_CATEGORY
873 ,lines.user_defined_fisc_class -- USER_DEFINED_FISC_CLASS
874 ,lines.assessable_value -- ASSESSABLE_VALUE
875 ,lines.trx_business_category -- TRX_BUSINESS_CATEGORY
876 ,inv.supplier_tax_invoice_number -- SUPPLIER_TAX_INVOICE_NUMBER
877 ,inv.supplier_tax_invoice_date -- SUPPLIER_TAX_INVOICE_DATE
878 ,inv.supplier_tax_exchange_rate SUPPLIER_EXCHANGE_RATE
879 ,inv.tax_invoice_recording_date TAX_INVOICE_DATE
880 ,inv.tax_invoice_internal_seq TAX_INVOICE_NUMBER
881 ,inv.document_sub_type -- DOCUMENT_SUB_TYPE
882 ,lines.primary_intended_use LINE_INTENDED_USE
883 ,inv.port_of_entry_code -- PORT_OF_ENTRY_CODE
884 -- ,NULL SOURCE_APPLICATION_ID
885 -- ,NULL SOURCE_ENTITY_CODE
886 -- ,NULL SOURCE_EVENT_CLASS_CODE
887 -- ,NULL SOURCE_TRX_ID,
888 -- ,NULL SOURCE_LINE_ID,
889 -- ,NULL SOURCE_TRX_LEVEL_TYPE
890 ,'N' LINE_AMT_INCLUDES_TAX_FLAG
891 ,'N' CTRL_HDR_TX_APPL_FLAG
892 ,'Y' TAX_REPORTING_FLAG
893 ,'N' TAX_AMT_INCLUDED_FLAG
894 ,'N' COMPOUNDING_TAX_FLAG
895 ,'N' INCLUSIVE_TAX_OVERRIDE_FLAG
896 ,'N' THRESHOLD_INDICATOR_FLAG
897 ,'N' USER_UPD_DET_FACTORS_FLAG
898 ,'N' TAX_PROCESSING_COMPLETED_FLAG
899 ,lines.assets_tracking_flag ASSET_FLAG
900 ,ptp.party_tax_profile_id CONTENT_OWNER_ID
901 ,inv.exchange_date CURRENCY_CONVERSION_DATE
902 ,inv.exchange_rate CURRENCY_CONVERSION_RATE
903 ,inv.exchange_rate_type CURRENCY_CONVERSION_TYPE
904 ,fnd_curr.minimum_accountable_unit MINIMUM_ACCOUNTABLE_UNIT
905 ,NVL(fnd_curr.precision,0) PRECISION
906 ,DECODE(NVL(lines.po_release_id, lines.po_header_id),
907 NULL, NULL, 201) REF_DOC_APPLICATION_ID
908 ,DECODE(lines.po_release_id, NULL,
909 DECODE(lines.po_header_id, NULL, NULL,
910 'PURCHASE_ORDER'), 'RELEASE') REF_DOC_ENTITY_CODE
911 ,DECODE(lines.po_release_id, NULL,
912 DECODE(lines.po_header_id, NULL, NULL,
913 'PO_PA'), 'RELEASE') REF_DOC_EVENT_CLASS_CODE
914 ,lines.SUMMARY_TAX_LINE_ID SUMMARY_TAX_LINE_ID
915 ,lines.TAX TAX
916 ,DECODE(lines.line_type_lookup_code, 'TAX',
917 RANK() OVER (PARTITION BY inv.invoice_id,
918 lines.line_type_lookup_code
919 ORDER BY lines.line_number), NULL) SUMMARY_TAX_LINE_NUMBER
920 ,lines.tax_rate -- TAX_RATE
921 ,lines.tax_rate_code -- TAX_RATE_CODE
922 ,lines.tax_rate_id -- TAX_RATE_ID
923 ,lines.tax_regime_code -- TAX_REGIME_CODE
924 ,lines.tax_status_code -- TAX_STATUS_CODE
925 ,lines.tax_jurisdiction_code -- TAX_JURISDICTION_CODE
926 ,'LINE' TRX_LEVEL_TYPE
927 ,lines.line_number TRX_LINE_ID
928 ,lines.line_number TRX_LINE_NUMBER
929 ,lines.default_dist_ccid ACCOUNT_CCID
930 -- ,NULL ACCOUNT_STRING
931 ,lines.amount TAX_AMT
932 ,lines.base_amount TAX_AMT_TAX_CURR
933 ,lines.base_amount TAX_AMT_FUNCL_CURR
934 ,lines.attribute_category -- ATTRIBUTE_CATEGORY
935 ,lines.attribute1 -- ATTRIBUTE1
936 ,lines.attribute2 -- ATTRIBUTE2
937 ,lines.attribute3 -- ATTRIBUTE3
938 ,lines.attribute4 -- ATTRIBUTE4
939 ,lines.attribute5 -- ATTRIBUTE5
940 ,lines.attribute6 -- ATTRIBUTE6
941 ,lines.attribute7 -- ATTRIBUTE7
942 ,lines.attribute8 -- ATTRIBUTE8
943 ,lines.attribute9 -- ATTRIBUTE9
944 ,lines.attribute10 -- ATTRIBUTE10
945 ,lines.attribute11 -- ATTRIBUTE11
946 ,lines.attribute12 -- ATTRIBUTE12
947 ,lines.attribute13 -- ATTRIBUTE13
948 ,lines.attribute14 -- ATTRIBUTE14
949 ,lines.attribute15 -- ATTRIBUTE15
950 ,lines.global_attribute_category -- GLOBAL_ATTRIBUTE_CATEGORY
951 ,lines.global_attribute1 -- GLOBAL_ATTRIBUTE1
952 ,lines.global_attribute2 -- GLOBAL_ATTRIBUTE2
953 ,lines.global_attribute3 -- GLOBAL_ATTRIBUTE3
954 ,lines.global_attribute4 -- GLOBAL_ATTRIBUTE4
955 ,lines.global_attribute5 -- GLOBAL_ATTRIBUTE5
956 ,lines.global_attribute6 -- GLOBAL_ATTRIBUTE6
957 ,lines.global_attribute7 -- GLOBAL_ATTRIBUTE7
958 ,lines.global_attribute8 -- GLOBAL_ATTRIBUTE8
959 ,lines.global_attribute9 -- GLOBAL_ATTRIBUTE9
960 ,lines.global_attribute10 -- GLOBAL_ATTRIBUTE10
961 ,lines.global_attribute11 -- GLOBAL_ATTRIBUTE11
962 ,lines.global_attribute12 -- GLOBAL_ATTRIBUTE12
963 ,lines.global_attribute13 -- GLOBAL_ATTRIBUTE13
964 ,lines.global_attribute14 -- GLOBAL_ATTRIBUTE14
965 ,lines.global_attribute15 -- GLOBAL_ATTRIBUTE15
966 ,lines.global_attribute16 -- GLOBAL_ATTRIBUTE16
967 ,lines.global_attribute17 -- GLOBAL_ATTRIBUTE17
968 ,lines.global_attribute18 -- GLOBAL_ATTRIBUTE18
969 ,lines.global_attribute19 -- GLOBAL_ATTRIBUTE19
970 ,lines.global_attribute20 -- GLOBAL_ATTRIBUTE20
971 ,'Y' HISTORICAL_FLAG
972 ,'N' OVERRIDDEN_FLAG
973 ,'N' SELF_ASSESSED_FLAG
974 ,1 CREATED_BY
975 ,SYSDATE CREATION_DATE
976 ,SYSDATE LAST_UPDATE_DATE
977 ,1 LAST_UPDATE_LOGIN
978 ,1 LAST_UPDATED_BY
979 -- ,NULL LAST_MANUAL_ENTRY
980 ,CASE
981 WHEN lines.line_type_lookup_code <> 'TAX'
982 THEN NULL
983 WHEN NOT EXISTS -- Tax Lines
984 (SELECT 1
985 FROM AP_INV_DISTS_TARGET dists
986 WHERE dists.invoice_id = lines.invoice_id
987 AND dists.invoice_line_number = lines.line_number
988 AND dists.charge_applicable_to_dist_id IS NOT NULL
989 )
990 THEN 'Y'
991 ELSE 'N'
992 END TAX_ONLY_LINE_FLAG
993 ,lines.total_rec_tax_amount TOTAL_REC_TAX_AMT
994 ,lines.total_nrec_tax_amount TOTAL_NREC_TAX_AMT
995 ,lines.total_rec_tax_amt_funcl_curr -- TOTAL_REC_TAX_AMT_FUNCL_CURR,
996 ,lines.total_nrec_tax_amt_funcl_curr -- TOTAL_NREC_TAX_AMT_FUNCL_CURR,
997 ,inv.vendor_id SHIP_THIRD_PTY_ACCT_ID
998 ,inv.vendor_site_id SHIP_THIRD_PTY_ACCT_SITE_ID
999 ,inv.vendor_id BILL_THIRD_PTY_ACCT_ID
1000 ,inv.vendor_site_id BILL_THIRD_PTY_ACCT_SITE_ID
1001 -- ,NULL ICX_SESSION_ID
1002 -- ,NULL TRX_LINE_CURRENCY_CODE
1003 -- ,NULL TRX_LINE_CURRENCY_CONV_RATE
1004 -- ,NULL TRX_LINE_CURRENCY_CONV_DATE
1005 -- ,NULL TRX_LINE_PRECISION
1006 -- ,NULL TRX_LINE_MAU
1007 -- ,NULL TRX_LINE_CURRENCY_CONV_TYPE
1008 -- ,NULL INTERFACE_ENTITY_CODE
1009 -- ,NULL INTERFACE_LINE_ID
1010 -- ,NULL SOURCE_TAX_LINE_ID
1011 ,DECODE(lines.line_source,'MANUAL LINE ENTRY','PRORATED_TB','STANDARD_TB') TAXABLE_BASIS_FORMULA --Bug 9037817
1012 ,DECODE(lines.discarded_flag, 'Y', 'Y', 'N') CANCEL_FLAG
1013 ,DECODE(lines.line_source,'MANUAL LINE ENTRY','Y','N') MANUALLY_ENTERED_FLAG --BUG7146063
1014 ,DECODE(lines.line_source,'MANUAL LINE ENTRY','TAX_AMOUNT',NULL) LAST_MANUAL_ENTRY --BUG7146063
1015 FROM ap_invoices_all inv,
1016 fnd_currencies fnd_curr,
1017 fnd_document_sequences fds,
1018 ap_invoice_lines_all lines,
1019 po_line_locations_all poll,
1020 zx_party_tax_profile ptp
1021 WHERE inv.invoice_id = p_upg_trx_info_rec.trx_id
1022 AND fnd_curr.currency_code = inv.invoice_currency_code
1023 AND inv.doc_sequence_id = fds.doc_sequence_id(+)
1024 AND lines.invoice_id = inv.invoice_id
1025 AND NVL(lines.historical_flag, 'N') = 'Y'
1026 AND poll.line_location_id(+) = lines.po_line_location_id
1027 AND ptp.party_type_code = 'OU'
1028 AND ptp.party_id = DECODE(l_multi_org_flag,'N',l_org_id,lines.org_id)
1029 AND NOT EXISTS (SELECT 1 FROM zx_lines_det_factors zxdet --Bug 6738188
1030 WHERE zxdet.application_id = 200
1031 AND zxdet.entity_code = 'AP_INVOICES'
1032 AND zxdet.event_class_code = DECODE(inv.INVOICE_TYPE_LOOKUP_CODE,
1033 'STANDARD', 'STANDARD INVOICES',
1034 'CREDIT' , 'STANDARD INVOICES',
1035 'DEBIT' , 'STANDARD INVOICES',
1036 'MIXED' , 'STANDARD INVOICES',
1037 'ADJUSTMENT','STANDARD INVOICES',
1038 'PO PRICE ADJUST','STANDARD INVOICES',
1039 'INVOICE REQUEST','STANDARD INVOICES',
1040 'CREDIT MEMO REQUEST','STANDARD INVOICES',
1041 'RETAINAGE RELEASE' ,'STANDARD INVOICES',
1042 'PREPAYMENT', 'PREPAYMENT INVOICES',
1043 'EXPENSE REPORT', 'EXPENSE REPORTS',
1044 'INTEREST INVOICE', 'INTEREST INVOICES','NA')
1045 AND zxdet.trx_id=inv.invoice_id
1046 AND zxdet.trx_line_id=lines.line_number)
1047 );
1048
1049 IF (g_level_statement >= g_current_runtime_level ) THEN
1050 FND_LOG.STRING(g_level_statement,
1051 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_ap',
1052 'Inserting data into zx_lines and zx_rec_nrec_dist');
1053 END IF;
1054
1055 -- Insert data into zx_lines and zx_rec_nrec_dist
1056 --
1057 INSERT ALL
1058 INTO ZX_REC_NREC_DIST(
1059 TAX_LINE_ID
1060 ,REC_NREC_TAX_DIST_ID
1061 ,REC_NREC_TAX_DIST_NUMBER
1062 ,APPLICATION_ID
1063 ,CONTENT_OWNER_ID
1064 ,CURRENCY_CONVERSION_DATE
1065 ,CURRENCY_CONVERSION_RATE
1066 ,CURRENCY_CONVERSION_TYPE
1067 ,ENTITY_CODE
1068 ,EVENT_CLASS_CODE
1069 ,EVENT_TYPE_CODE
1070 ,LEDGER_ID
1071 ,MINIMUM_ACCOUNTABLE_UNIT
1072 ,PRECISION
1073 ,RECORD_TYPE_CODE
1074 ,REF_DOC_APPLICATION_ID
1075 ,REF_DOC_ENTITY_CODE
1076 ,REF_DOC_EVENT_CLASS_CODE
1077 ,REF_DOC_LINE_ID
1078 ,REF_DOC_TRX_ID
1079 ,REF_DOC_TRX_LEVEL_TYPE
1080 ,SUMMARY_TAX_LINE_ID
1081 ,TAX
1082 ,TAX_APPORTIONMENT_LINE_NUMBER
1083 ,TAX_CURRENCY_CODE
1084 ,TAX_CURRENCY_CONVERSION_DATE
1085 ,TAX_CURRENCY_CONVERSION_RATE
1086 ,TAX_CURRENCY_CONVERSION_TYPE
1087 ,TAX_EVENT_CLASS_CODE
1088 ,TAX_EVENT_TYPE_CODE
1089 ,TAX_ID
1090 ,TAX_LINE_NUMBER
1091 ,TAX_RATE
1092 ,TAX_RATE_CODE
1093 ,TAX_RATE_ID
1094 ,TAX_REGIME_CODE
1095 ,TAX_REGIME_ID
1096 ,TAX_STATUS_CODE
1097 ,TAX_STATUS_ID
1098 ,TRX_CURRENCY_CODE
1099 ,TRX_ID
1100 ,TRX_LEVEL_TYPE
1101 ,TRX_LINE_ID
1102 ,TRX_LINE_NUMBER
1103 ,TRX_NUMBER
1104 ,UNIT_PRICE
1105 ,ACCOUNT_CCID
1106 -- ,ACCOUNT_STRING
1107 -- ,ADJUSTED_DOC_TAX_DIST_ID
1108 -- ,APPLIED_FROM_TAX_DIST_ID
1109 -- ,APPLIED_TO_DOC_CURR_CONV_RATE
1110 ,AWARD_ID
1111 ,EXPENDITURE_ITEM_DATE
1112 ,EXPENDITURE_ORGANIZATION_ID
1113 ,EXPENDITURE_TYPE
1114 ,FUNC_CURR_ROUNDING_ADJUSTMENT
1115 ,GL_DATE
1116 ,INTENDED_USE
1117 ,ITEM_DIST_NUMBER
1118 -- ,MRC_LINK_TO_TAX_DIST_ID
1119 -- ,ORIG_REC_NREC_RATE
1120 -- ,ORIG_REC_NREC_TAX_AMT
1121 -- ,ORIG_REC_NREC_TAX_AMT_TAX_CURR
1122 -- ,ORIG_REC_RATE_CODE
1123 -- ,PER_TRX_CURR_UNIT_NR_AMT
1124 -- ,PER_UNIT_NREC_TAX_AMT
1125 -- ,PRD_TAX_AMT
1126 -- ,PRICE_DIFF
1127 ,PROJECT_ID
1128 -- ,QTY_DIFF
1129 -- ,RATE_TAX_FACTOR
1130 ,REC_NREC_RATE
1131 ,REC_NREC_TAX_AMT
1132 ,REC_NREC_TAX_AMT_FUNCL_CURR
1133 ,REC_NREC_TAX_AMT_TAX_CURR
1134 ,RECOVERY_RATE_CODE
1135 ,RECOVERY_TYPE_CODE
1136 -- ,RECOVERY_TYPE_ID
1137 -- ,REF_DOC_CURR_CONV_RATE
1138 ,REF_DOC_DIST_ID
1139 -- ,REF_DOC_PER_UNIT_NREC_TAX_AMT
1140 -- ,REF_DOC_TAX_DIST_ID
1141 -- ,REF_DOC_TRX_LINE_DIST_QTY
1142 -- ,REF_DOC_UNIT_PRICE
1143 -- ,REF_PER_TRX_CURR_UNIT_NR_AMT
1144 ,REVERSED_TAX_DIST_ID
1145 -- ,ROUNDING_RULE_CODE
1146 ,TASK_ID
1147 ,TAXABLE_AMT_FUNCL_CURR
1148 ,TAXABLE_AMT_TAX_CURR
1149 ,TRX_LINE_DIST_AMT
1150 ,TRX_LINE_DIST_ID
1151 ,TRX_LINE_DIST_QTY
1152 ,TRX_LINE_DIST_TAX_AMT
1153 -- ,UNROUNDED_REC_NREC_TAX_AMT
1154 -- ,UNROUNDED_TAXABLE_AMT
1155 ,TAXABLE_AMT
1156 ,ATTRIBUTE_CATEGORY
1157 ,ATTRIBUTE1
1158 ,ATTRIBUTE2
1159 ,ATTRIBUTE3
1160 ,ATTRIBUTE4
1161 ,ATTRIBUTE5
1162 ,ATTRIBUTE6
1163 ,ATTRIBUTE7
1164 ,ATTRIBUTE8
1165 ,ATTRIBUTE9
1166 ,ATTRIBUTE10
1167 ,ATTRIBUTE11
1168 ,ATTRIBUTE12
1169 ,ATTRIBUTE13
1170 ,ATTRIBUTE14
1171 ,ATTRIBUTE15
1172 ,GLOBAL_ATTRIBUTE_CATEGORY
1173 ,GLOBAL_ATTRIBUTE1
1174 ,GLOBAL_ATTRIBUTE2
1175 ,GLOBAL_ATTRIBUTE3
1176 ,GLOBAL_ATTRIBUTE4
1177 ,GLOBAL_ATTRIBUTE5
1178 ,GLOBAL_ATTRIBUTE6
1179 ,GLOBAL_ATTRIBUTE7
1180 ,GLOBAL_ATTRIBUTE8
1181 ,GLOBAL_ATTRIBUTE9
1182 ,GLOBAL_ATTRIBUTE10
1183 ,GLOBAL_ATTRIBUTE11
1184 ,GLOBAL_ATTRIBUTE12
1185 ,GLOBAL_ATTRIBUTE13
1186 ,GLOBAL_ATTRIBUTE14
1187 ,GLOBAL_ATTRIBUTE15
1188 ,GLOBAL_ATTRIBUTE16
1189 ,GLOBAL_ATTRIBUTE17
1190 ,GLOBAL_ATTRIBUTE18
1191 ,GLOBAL_ATTRIBUTE19
1192 ,GLOBAL_ATTRIBUTE20
1193 ,HISTORICAL_FLAG
1194 ,OVERRIDDEN_FLAG
1195 ,SELF_ASSESSED_FLAG
1196 ,TAX_APPORTIONMENT_FLAG
1197 ,TAX_ONLY_LINE_FLAG
1198 ,INCLUSIVE_FLAG
1199 ,MRC_TAX_DIST_FLAG
1200 ,REC_TYPE_RULE_FLAG
1201 ,NEW_REC_RATE_CODE_FLAG
1202 ,RECOVERABLE_FLAG
1203 ,REVERSE_FLAG
1204 ,REC_RATE_DET_RULE_FLAG
1205 ,BACKWARD_COMPATIBILITY_FLAG
1206 ,FREEZE_FLAG
1207 ,POSTING_FLAG
1208 ,LEGAL_ENTITY_ID
1209 ,CREATED_BY
1210 ,CREATION_DATE
1211 ,LAST_MANUAL_ENTRY
1212 ,LAST_UPDATE_DATE
1213 ,LAST_UPDATE_LOGIN
1214 ,LAST_UPDATED_BY
1215 ,OBJECT_VERSION_NUMBER
1216 ,ORIG_AP_CHRG_DIST_NUM
1217 ,ORIG_AP_CHRG_DIST_ID
1218 ,ORIG_AP_TAX_DIST_NUM
1219 ,ORIG_AP_TAX_DIST_ID
1220 ,INTERNAL_ORGANIZATION_ID
1221 ,DEF_REC_SETTLEMENT_OPTION_CODE
1222 --,TAX_JURISDICTION_ID
1223 ,ACCOUNT_SOURCE_TAX_RATE_ID
1224 ,RECOVERY_RATE_ID
1225 )
1226 VALUES(
1227 ZX_LINES_S.NEXTVAL
1228 ,REC_NREC_TAX_DIST_ID
1229 ,REC_NREC_TAX_DIST_NUMBER
1230 ,APPLICATION_ID
1231 ,CONTENT_OWNER_ID
1232 ,CURRENCY_CONVERSION_DATE
1233 ,CURRENCY_CONVERSION_RATE
1234 ,CURRENCY_CONVERSION_TYPE
1235 ,ENTITY_CODE
1236 ,EVENT_CLASS_CODE
1237 ,EVENT_TYPE_CODE
1238 ,AP_LEDGER_ID
1239 ,MINIMUM_ACCOUNTABLE_UNIT
1240 ,PRECISION
1241 ,RECORD_TYPE_CODE
1242 ,REF_DOC_APPLICATION_ID
1243 ,REF_DOC_ENTITY_CODE
1244 ,REF_DOC_EVENT_CLASS_CODE
1245 ,REF_DOC_LINE_ID
1246 ,REF_DOC_TRX_ID
1247 ,REF_DOC_TRX_LEVEL_TYPE
1248 ,SUMMARY_TAX_LINE_ID
1249 ,TAX
1250 ,TAX_APPORTIONMENT_LINE_NUMBER
1251 ,TAX_CURRENCY_CODE
1252 ,TAX_CURRENCY_CONVERSION_DATE
1253 ,TAX_CURRENCY_CONVERSION_RATE
1254 ,TAX_CURRENCY_CONVERSION_TYPE
1255 ,TAX_EVENT_CLASS_CODE
1256 ,TAX_EVENT_TYPE_CODE
1257 ,TAX_ID
1258 ,TAX_LINE_NUMBER
1259 ,TAX_RATE
1260 ,TAX_RATE_CODE
1261 ,TAX_RATE_ID
1262 ,TAX_REGIME_CODE
1263 ,TAX_REGIME_ID
1264 ,TAX_STATUS_CODE
1265 ,TAX_STATUS_ID
1266 ,TRX_CURRENCY_CODE
1267 ,TRX_ID
1268 ,TRX_LEVEL_TYPE
1269 ,TRX_LINE_ID
1270 ,TRX_LINE_NUMBER
1271 ,TRX_NUMBER
1272 ,UNIT_PRICE
1273 ,ACCOUNT_CCID
1274 -- ,ACCOUNT_STRING
1275 -- ,ADJUSTED_DOC_TAX_DIST_ID
1276 -- ,APPLIED_FROM_TAX_DIST_ID
1277 -- ,APPLIED_TO_DOC_CURR_CONV_RATE
1278 ,AWARD_ID
1279 ,EXPENDITURE_ITEM_DATE
1280 ,EXPENDITURE_ORGANIZATION_ID
1281 ,EXPENDITURE_TYPE
1282 ,FUNC_CURR_ROUNDING_ADJUSTMENT
1283 ,GL_DATE
1284 ,INTENDED_USE
1285 ,ITEM_DIST_NUMBER
1286 -- ,MRC_LINK_TO_TAX_DIST_ID
1287 -- ,ORIG_REC_NREC_RATE
1288 -- ,ORIG_REC_NREC_TAX_AMT
1289 -- ,ORIG_REC_NREC_TAX_AMT_TAX_CURR
1290 -- ,ORIG_REC_RATE_CODE
1291 -- ,PER_TRX_CURR_UNIT_NR_AMT
1292 -- ,PER_UNIT_NREC_TAX_AMT
1293 -- ,PRD_TAX_AMT
1294 -- ,PRICE_DIFF
1295 ,PROJECT_ID
1296 -- ,QTY_DIFF
1297 -- ,RATE_TAX_FACTOR
1298 ,REC_NREC_RATE
1299 ,REC_NREC_TAX_AMT
1300 ,REC_NREC_TAX_AMT_FUNCL_CURR
1301 ,REC_NREC_TAX_AMT_TAX_CURR
1302 ,RECOVERY_RATE_CODE
1303 ,RECOVERY_TYPE_CODE
1304 -- ,RECOVERY_TYPE_ID
1305 -- ,REF_DOC_CURR_CONV_RATE
1306 ,REF_DOC_DIST_ID
1307 -- ,REF_DOC_PER_UNIT_NREC_TAX_AMT
1308 -- ,REF_DOC_TAX_DIST_ID
1309 -- ,REF_DOC_TRX_LINE_DIST_QTY
1310 -- ,REF_DOC_UNIT_PRICE
1311 -- ,REF_PER_TRX_CURR_UNIT_NR_AMT
1312 ,REVERSED_TAX_DIST_ID
1313 -- ,ROUNDING_RULE_CODE
1314 ,TASK_ID
1315 ,TAXABLE_AMT_FUNCL_CURR
1316 ,TAXABLE_AMT_TAX_CURR
1317 ,TRX_LINE_DIST_AMT
1318 ,TRX_LINE_DIST_ID
1319 ,TRX_LINE_DIST_QTY
1320 ,TRX_LINE_DIST_TAX_AMT
1321 -- ,UNROUNDED_REC_NREC_TAX_AMT
1322 -- ,UNROUNDED_TAXABLE_AMT
1323 ,TAXABLE_AMT
1324 ,ATTRIBUTE_CATEGORY
1325 ,ATTRIBUTE1
1326 ,ATTRIBUTE2
1327 ,ATTRIBUTE3
1328 ,ATTRIBUTE4
1329 ,ATTRIBUTE5
1330 ,ATTRIBUTE6
1331 ,ATTRIBUTE7
1332 ,ATTRIBUTE8
1333 ,ATTRIBUTE9
1334 ,ATTRIBUTE10
1335 ,ATTRIBUTE11
1336 ,ATTRIBUTE12
1337 ,ATTRIBUTE13
1338 ,ATTRIBUTE14
1339 ,ATTRIBUTE15
1340 ,GLOBAL_ATTRIBUTE_CATEGORY
1341 ,GLOBAL_ATTRIBUTE1
1342 ,GLOBAL_ATTRIBUTE2
1343 ,GLOBAL_ATTRIBUTE3
1344 ,GLOBAL_ATTRIBUTE4
1345 ,GLOBAL_ATTRIBUTE5
1346 ,GLOBAL_ATTRIBUTE6
1347 ,GLOBAL_ATTRIBUTE7
1348 ,GLOBAL_ATTRIBUTE8
1349 ,GLOBAL_ATTRIBUTE9
1350 ,GLOBAL_ATTRIBUTE10
1351 ,GLOBAL_ATTRIBUTE11
1352 ,GLOBAL_ATTRIBUTE12
1353 ,GLOBAL_ATTRIBUTE13
1354 ,GLOBAL_ATTRIBUTE14
1355 ,GLOBAL_ATTRIBUTE15
1356 ,GLOBAL_ATTRIBUTE16
1357 ,GLOBAL_ATTRIBUTE17
1358 ,GLOBAL_ATTRIBUTE18
1359 ,GLOBAL_ATTRIBUTE19
1360 ,GLOBAL_ATTRIBUTE20
1361 ,HISTORICAL_FLAG
1362 ,OVERRIDDEN_FLAG
1363 ,SELF_ASSESSED_FLAG
1364 ,TAX_APPORTIONMENT_FLAG
1365 ,TAX_ONLY_LINE_FLAG
1366 ,INCLUSIVE_FLAG
1367 ,MRC_TAX_DIST_FLAG
1368 ,REC_TYPE_RULE_FLAG
1369 ,NEW_REC_RATE_CODE_FLAG
1370 ,RECOVERABLE_FLAG
1371 ,REVERSE_FLAG
1372 ,REC_RATE_DET_RULE_FLAG
1373 ,BACKWARD_COMPATIBILITY_FLAG
1374 ,FREEZE_FLAG
1375 ,POSTING_FLAG
1376 ,LEGAL_ENTITY_ID
1377 ,CREATED_BY
1378 ,CREATION_DATE
1379 ,LAST_MANUAL_ENTRY
1380 ,LAST_UPDATE_DATE
1381 ,LAST_UPDATE_LOGIN
1382 ,LAST_UPDATED_BY
1383 ,OBJECT_VERSION_NUMBER
1384 ,ORIG_AP_CHRG_DIST_NUM
1385 ,ORIG_AP_CHRG_DIST_ID
1386 ,ORIG_AP_TAX_DIST_NUM
1387 ,ORIG_AP_TAX_DIST_ID
1388 ,INTERNAL_ORGANIZATION_ID
1389 ,DEF_REC_SETTLEMENT_OPTION_CODE
1390 --,TAX_JURISDICTION_ID
1391 ,ACCOUNT_SOURCE_TAX_RATE_ID
1392 ,RECOVERY_RATE_ID
1393 )
1394 INTO ZX_LINES(
1395 TAX_LINE_ID
1396 ,TAX_LINE_NUMBER
1397 ,APPLICATION_ID
1398 ,CONTENT_OWNER_ID
1399 ,CURRENCY_CONVERSION_DATE
1400 ,CURRENCY_CONVERSION_RATE
1401 ,CURRENCY_CONVERSION_TYPE
1402 ,ENTITY_CODE
1403 ,EVENT_CLASS_CODE
1404 ,EVENT_TYPE_CODE
1405 ,LEDGER_ID
1406 ,MINIMUM_ACCOUNTABLE_UNIT
1407 ,PRECISION
1408 ,RECORD_TYPE_CODE
1409 ,REF_DOC_APPLICATION_ID
1410 ,REF_DOC_ENTITY_CODE
1411 ,REF_DOC_EVENT_CLASS_CODE
1412 ,REF_DOC_LINE_ID
1413 ,REF_DOC_TRX_ID
1414 ,REF_DOC_TRX_LEVEL_TYPE
1415 ,SUMMARY_TAX_LINE_ID
1416 ,TAX
1417 ,TAX_APPORTIONMENT_LINE_NUMBER
1418 ,TAX_CURRENCY_CODE
1419 ,TAX_CURRENCY_CONVERSION_DATE
1420 ,TAX_CURRENCY_CONVERSION_RATE
1421 ,TAX_CURRENCY_CONVERSION_TYPE
1422 ,TAX_EVENT_CLASS_CODE
1423 ,TAX_EVENT_TYPE_CODE
1424 ,TAX_ID
1425 ,TAX_RATE
1426 ,TAX_RATE_CODE
1427 ,TAX_RATE_ID
1428 ,TAX_REGIME_CODE
1429 ,TAX_REGIME_ID
1430 ,TAX_STATUS_CODE
1431 ,TAX_STATUS_ID
1432 ,TRX_CURRENCY_CODE
1433 ,TRX_ID
1434 ,TRX_LEVEL_TYPE
1435 ,TRX_LINE_ID
1436 ,TRX_LINE_NUMBER
1437 ,TRX_NUMBER
1438 ,UNIT_PRICE
1439 ,TAX_RATE_TYPE
1440 ,ADJUSTED_DOC_APPLICATION_ID
1441 -- ,ADJUSTED_DOC_DATE
1442 ,ADJUSTED_DOC_ENTITY_CODE
1443 ,ADJUSTED_DOC_EVENT_CLASS_CODE
1444 ,ADJUSTED_DOC_LINE_ID
1445 -- ,ADJUSTED_DOC_NUMBER
1446 ,ADJUSTED_DOC_TRX_ID
1447 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
1448 ,APPLIED_FROM_APPLICATION_ID
1449 ,APPLIED_FROM_ENTITY_CODE
1450 ,APPLIED_FROM_EVENT_CLASS_CODE
1451 ,APPLIED_FROM_LINE_ID
1452 -- ,APPLIED_FROM_TRX_NUMBER
1453 ,APPLIED_FROM_TRX_ID
1454 ,APPLIED_FROM_TRX_LEVEL_TYPE
1455 ,APPLIED_TO_APPLICATION_ID
1456 ,APPLIED_TO_ENTITY_CODE
1457 ,APPLIED_TO_EVENT_CLASS_CODE
1458 ,APPLIED_TO_LINE_ID
1459 ,APPLIED_TO_TRX_ID
1460 ,APPLIED_TO_TRX_LEVEL_TYPE
1461 -- ,APPLIED_TO_TRX_NUMBER
1462 -- ,CAL_TAX_AMT
1463 -- ,CAL_TAX_AMT_FUNCL_CURR
1464 -- ,CAL_TAX_AMT_TAX_CURR
1465 -- ,DOC_EVENT_STATUS
1466 -- ,INTERNAL_ORG_LOCATION_ID
1467 ,INTERNAL_ORGANIZATION_ID
1468 ,LINE_AMT
1469 ,LINE_ASSESSABLE_VALUE
1470 -- ,MRC_LINK_TO_TAX_LINE_ID
1471 ,NREC_TAX_AMT
1472 ,NREC_TAX_AMT_FUNCL_CURR
1473 ,NREC_TAX_AMT_TAX_CURR
1474 -- ,OFFSET_LINK_TO_TAX_LINE_ID
1475 -- ,OFFSET_TAX_RATE_CODE
1476 -- ,ORIG_TAX_AMT
1477 -- ,ORIG_TAX_AMT_TAX_CURR
1478 -- ,ORIG_TAX_RATE
1479 -- ,ORIG_TAX_RATE_CODE
1480 -- ,ORIG_TAX_RATE_ID
1481 -- ,ORIG_TAX_STATUS_CODE
1482 -- ,ORIG_TAX_STATUS_ID
1483 -- ,ORIG_TAXABLE_AMT
1484 -- ,ORIG_TAXABLE_AMT_TAX_CURR
1485 -- ,OTHER_DOC_LINE_AMT
1486 -- ,OTHER_DOC_LINE_TAX_AMT
1487 -- ,OTHER_DOC_LINE_TAXABLE_AMT
1488 -- ,OTHER_DOC_SOURCE
1489 -- ,PRORATION_CODE
1490 ,REC_TAX_AMT
1491 ,REC_TAX_AMT_FUNCL_CURR
1492 ,REC_TAX_AMT_TAX_CURR
1493 -- ,REF_DOC_LINE_QUANTITY
1494 -- ,RELATED_DOC_APPLICATION_ID
1495 -- ,RELATED_DOC_DATE
1496 -- ,RELATED_DOC_ENTITY_CODE
1497 -- ,RELATED_DOC_EVENT_CLASS_CODE
1498 -- ,RELATED_DOC_NUMBER
1499 -- ,RELATED_DOC_TRX_ID
1500 -- ,RELATED_DOC_TRX_LEVEL_TYPE
1501 -- ,REPORTING_CURRENCY_CODE
1502 ,TAX_AMT
1503 ,TAX_AMT_FUNCL_CURR
1504 ,TAX_AMT_TAX_CURR
1505 ,TAX_CALCULATION_FORMULA
1506 -- ,TAX_CODE
1507 ,TAX_DATE
1508 ,TAX_DETERMINE_DATE
1509 ,TAX_POINT_DATE
1510 -- ,TAX_TYPE_CODE
1511 -- ,ROUNDING_RULE_CODE
1512 ,TAXABLE_AMT
1513 ,TAXABLE_AMT_FUNCL_CURR
1514 ,TAXABLE_AMT_TAX_CURR
1515 ,TAXABLE_BASIS_FORMULA
1516 ,TRX_DATE
1517 ,TRX_LINE_DATE
1518 ,TRX_LINE_QUANTITY
1519 -- ,UNROUNDED_TAX_AMT
1520 -- ,UNROUNDED_TAXABLE_AMT
1521 ,HISTORICAL_FLAG
1522 ,OVERRIDDEN_FLAG
1523 ,SELF_ASSESSED_FLAG
1524 ,TAX_APPORTIONMENT_FLAG
1525 ,TAX_ONLY_LINE_FLAG
1526 ,TAX_AMT_INCLUDED_FLAG
1527 ,MRC_TAX_LINE_FLAG
1528 ,OFFSET_FLAG
1529 ,OFFSET_TAX_RATE_CODE
1530 ,PROCESS_FOR_RECOVERY_FLAG
1531 ,COMPOUNDING_TAX_FLAG
1532 ,ORIG_TAX_AMT_INCLUDED_FLAG
1533 ,ORIG_SELF_ASSESSED_FLAG
1534 ,CANCEL_FLAG
1535 ,PURGE_FLAG
1536 ,DELETE_FLAG
1537 ,MANUALLY_ENTERED_FLAG
1538 --,LAST_MANUAL_ENTRY --BUG7146063
1539 ,REPORTING_ONLY_FLAG
1540 ,FREEZE_UNTIL_OVERRIDDEN_FLAG
1541 ,COPIED_FROM_OTHER_DOC_FLAG
1542 ,RECALC_REQUIRED_FLAG
1543 ,SETTLEMENT_FLAG
1544 ,ITEM_DIST_CHANGED_FLAG
1545 ,ASSOCIATED_CHILD_FROZEN_FLAG
1546 ,COMPOUNDING_DEP_TAX_FLAG
1547 ,ENFORCE_FROM_NATURAL_ACCT_FLAG
1548 ,ATTRIBUTE_CATEGORY
1549 ,ATTRIBUTE1
1550 ,ATTRIBUTE2
1551 ,ATTRIBUTE3
1552 ,ATTRIBUTE4
1553 ,ATTRIBUTE5
1554 ,ATTRIBUTE6
1555 ,ATTRIBUTE7
1556 ,ATTRIBUTE8
1557 ,ATTRIBUTE9
1558 ,ATTRIBUTE10
1559 ,ATTRIBUTE11
1560 ,ATTRIBUTE12
1561 ,ATTRIBUTE13
1562 ,ATTRIBUTE14
1563 ,ATTRIBUTE15
1564 ,GLOBAL_ATTRIBUTE_CATEGORY
1565 ,GLOBAL_ATTRIBUTE1
1566 ,GLOBAL_ATTRIBUTE2
1567 ,GLOBAL_ATTRIBUTE3
1568 ,GLOBAL_ATTRIBUTE4
1569 ,GLOBAL_ATTRIBUTE5
1570 ,GLOBAL_ATTRIBUTE6
1571 ,GLOBAL_ATTRIBUTE7
1572 ,GLOBAL_ATTRIBUTE8
1573 ,GLOBAL_ATTRIBUTE9
1574 ,GLOBAL_ATTRIBUTE10
1575 ,GLOBAL_ATTRIBUTE11
1576 ,GLOBAL_ATTRIBUTE12
1577 ,GLOBAL_ATTRIBUTE13
1578 ,GLOBAL_ATTRIBUTE14
1579 ,GLOBAL_ATTRIBUTE15
1580 ,LAST_MANUAL_ENTRY
1581 ,LEGAL_ENTITY_ID
1582 ,ESTABLISHMENT_ID
1583 ,CREATED_BY
1584 ,CREATION_DATE
1585 ,LAST_UPDATE_DATE
1586 ,LAST_UPDATE_LOGIN
1587 ,LAST_UPDATED_BY
1588 ,OBJECT_VERSION_NUMBER
1589 ,MULTIPLE_JURISDICTIONS_FLAG
1590 ,LEGAL_REPORTING_STATUS
1591 ,ACCOUNT_SOURCE_TAX_RATE_ID
1592 )
1593 VALUES (
1594 ZX_LINES_S.NEXTVAL
1595 ,TAX_LINE_NUMBER
1596 ,APPLICATION_ID
1597 ,CONTENT_OWNER_ID
1598 ,CURRENCY_CONVERSION_DATE
1599 ,CURRENCY_CONVERSION_RATE
1600 ,CURRENCY_CONVERSION_TYPE
1601 ,ENTITY_CODE
1602 ,EVENT_CLASS_CODE
1603 ,EVENT_TYPE_CODE
1604 ,AP_LEDGER_ID
1605 ,MINIMUM_ACCOUNTABLE_UNIT
1606 ,PRECISION
1607 ,RECORD_TYPE_CODE
1608 ,REF_DOC_APPLICATION_ID
1609 ,REF_DOC_ENTITY_CODE
1610 ,REF_DOC_EVENT_CLASS_CODE
1611 ,REF_DOC_LINE_ID
1612 ,REF_DOC_TRX_ID
1613 ,REF_DOC_TRX_LEVEL_TYPE
1614 ,SUMMARY_TAX_LINE_ID
1615 ,TAX
1616 ,TAX_APPORTIONMENT_LINE_NUMBER
1617 ,TAX_CURRENCY_CODE
1618 ,TAX_CURRENCY_CONVERSION_DATE
1619 ,TAX_CURRENCY_CONVERSION_RATE
1620 ,TAX_CURRENCY_CONVERSION_TYPE
1621 ,TAX_EVENT_CLASS_CODE
1622 ,TAX_EVENT_TYPE_CODE
1623 ,TAX_ID
1624 ,TAX_RATE
1625 ,TAX_RATE_CODE
1626 ,TAX_RATE_ID
1627 ,TAX_REGIME_CODE
1628 ,TAX_REGIME_ID
1629 ,TAX_STATUS_CODE
1630 ,TAX_STATUS_ID
1631 ,TRX_CURRENCY_CODE
1632 ,TRX_ID
1633 ,TRX_LEVEL_TYPE
1634 ,TRX_LINE_ID
1635 ,TRX_LINE_NUMBER
1636 ,TRX_NUMBER
1637 ,UNIT_PRICE
1638 ,RATE_TYPE_CODE
1639 ,ADJUSTED_DOC_APPLICATION_ID
1640 -- ,ADJUSTED_DOC_DATE
1641 ,ADJUSTED_DOC_ENTITY_CODE
1642 ,ADJUSTED_DOC_EVENT_CLASS_CODE
1643 ,ADJUSTED_DOC_LINE_ID
1644 -- ,ADJUSTED_DOC_NUMBER
1645 ,ADJUSTED_DOC_TRX_ID
1646 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
1647 ,APPLIED_FROM_APPLICATION_ID
1648 ,APPLIED_FROM_ENTITY_CODE
1649 ,APPLIED_FROM_EVENT_CLASS_CODE
1650 ,APPLIED_FROM_LINE_ID
1651 -- ,APPLIED_FROM_TRX_NUMBER
1652 ,APPLIED_FROM_TRX_ID
1653 ,APPLIED_FROM_TRX_LEVEL_TYPE
1654 ,APPLIED_TO_APPLICATION_ID
1655 ,APPLIED_TO_ENTITY_CODE
1656 ,APPLIED_TO_EVENT_CLASS_CODE
1657 ,APPLIED_TO_LINE_ID
1658 ,APPLIED_TO_TRX_ID
1659 ,APPLIED_TO_TRX_LEVEL_TYPE
1660 -- ,APPLIED_TO_TRX_NUMBER
1661 -- ,NULL -- CAL_TAX_AMT
1662 -- ,NULL -- CAL_TAX_AMT_FUNCL_CURR
1663 -- ,NULL -- CAL_TAX_AMT_TAX_CURR
1664 -- ,DOC_EVENT_STATUS
1665 -- ,INTERNAL_ORG_LOCATION_ID
1666 ,INTERNAL_ORGANIZATION_ID
1667 ,LINE_AMT
1668 ,ASSESSABLE_VALUE
1669 -- ,NULL -- MRC_LINK_TO_TAX_LINE_ID
1670 ,DECODE(AP_DIST_LOOKUP_CODE,
1671 'NONREC_TAX', REC_NREC_TAX_AMT, NULL) -- NREC_TAX_AMT
1672 ,DECODE(AP_DIST_LOOKUP_CODE,
1673 'NONREC_TAX', REC_NREC_TAX_AMT_FUNCL_CURR, NULL) -- NREC_TAX_AMT_FUNCL_CURR
1674 ,DECODE(AP_DIST_LOOKUP_CODE,
1675 'NONREC_TAX', REC_NREC_TAX_AMT_TAX_CURR, NULL) -- NREC_TAX_AMT_TAX_CURR
1676 -- ,NULL -- OFFSET_LINK_TO_TAX_LINE_ID
1677 -- ,NULL -- OFFSET_TAX_RATE_CODE
1678 -- ,NULL -- ORIG_TAX_AMT
1679 -- ,NULL -- ORIG_TAX_AMT_TAX_CURR
1680 -- ,NULL -- ORIG_TAX_RATE
1681 -- ,NULL -- ORIG_TAX_RATE_CODE
1682 -- ,NULL -- ORIG_TAX_RATE_ID
1683 -- ,NULL -- ORIG_TAX_STATUS_CODE
1684 -- ,NULL -- ORIG_TAX_STATUS_ID
1685 -- ,NULL -- ORIG_TAXABLE_AMT
1686 -- ,NULL -- ORIG_TAXABLE_AMT_TAX_CURR
1687 -- ,NULL -- OTHER_DOC_LINE_AMT
1688 -- ,NULL -- OTHER_DOC_LINE_TAX_AMT
1689 -- ,NULL -- OTHER_DOC_LINE_TAXABLE_AMT
1690 -- ,NULL -- OTHER_DOC_SOURCE
1691 -- ,NULL -- PRORATION_CODE
1692 ,DECODE(AP_DIST_LOOKUP_CODE,
1693 'REC_TAX', REC_NREC_TAX_AMT, NULL) -- REC_TAX_AMT
1694 ,DECODE(AP_DIST_LOOKUP_CODE,
1695 'REC_TAX', REC_NREC_TAX_AMT_FUNCL_CURR, NULL) -- REC_TAX_AMT_FUNCL_CURR
1696 ,DECODE(AP_DIST_LOOKUP_CODE,
1697 'REC_TAX', REC_NREC_TAX_AMT_TAX_CURR, NULL) -- REC_TAX_AMT_TAX_CURR
1698 -- ,REF_DOC_LINE_QUANTITY
1699 -- ,RELATED_DOC_APPLICATION_ID
1700 -- ,RELATED_DOC_DATE
1701 -- ,RELATED_DOC_ENTITY_CODE
1702 -- ,RELATED_DOC_EVENT_CLASS_CODE
1703 -- ,RELATED_DOC_NUMBER
1704 -- ,RELATED_DOC_TRX_ID
1705 -- ,RELATED_DOC_TRX_LEVEL_TYPE
1706 -- ,NULL -- REPORTING_CURRENCY_CODE
1707 ,TAX_AMT
1708 ,TAX_AMT_FUNCL_CURR
1709 ,TAX_AMT_TAX_CURR
1710 ,'STANDARD_TC'
1711 -- ,NULL -- TAX_CODE
1712 ,TAX_DATE
1713 ,TAX_DETERMINE_DATE
1714 ,TAX_POINT_DATE
1715 -- ,NULL -- TAX_TYPE_CODE
1716 -- ,ROUNDING_RULE_CODE
1717 ,TAXABLE_AMT
1718 ,TAXABLE_AMT_FUNCL_CURR
1719 ,TAXABLE_AMT_TAX_CURR
1720 -- Bug 9037817 'STANDARD_TB'
1721 ,TAXABLE_BASIS_FORMULA
1722 ,TRX_DATE
1723 ,TRX_LINE_DATE
1724 ,TRX_LINE_QUANTITY
1725 -- ,NULL -- UNROUNDED_TAX_AMT
1726 -- ,NULL -- UNROUNDED_TAXABLE_AMT
1727 ,HISTORICAL_FLAG
1728 ,OVERRIDDEN_FLAG
1729 ,SELF_ASSESSED_FLAG
1730 ,TAX_APPORTIONMENT_FLAG
1731 ,TAX_ONLY_LINE_FLAG
1732 ,TAX_AMT_INCLUDED_FLAG
1733 ,'N' -- MRC_TAX_LINE_FLAG
1734 ,OFFSET_FLAG --Bug 8303411
1735 ,OFFSET_TAX_RATE_CODE
1736 ,'N' -- PROCESS_FOR_RECOVERY_FLAG
1737 ,COMPOUNDING_TAX_FLAG
1738 ,'N' -- ORIG_TAX_AMT_INCLUDED_FLAG
1739 ,'N' -- ORIG_SELF_ASSESSED_FLAG
1740 ,CANCEL_FLAG
1741 ,'N' -- PURGE_FLAG
1742 ,'N' -- DELETE_FLAG
1743 ,MANUALLY_ENTERED_FLAG --BUG7146063
1744 --,LAST_MANUAL_ENTRY --BUG7146063
1745 ,'N' -- REPORTING_ONLY_FLAG
1746 ,'N' -- FREEZE_UNTIL_OVERRIDDEN_FLAG
1747 ,'N' -- COPIED_FROM_OTHER_DOC_FLAG
1748 ,'N' -- RECALC_REQUIRED_FLAG
1749 ,'N' -- SETTLEMENT_FLAG
1750 ,'N' -- ITEM_DIST_CHANGED_FLAG
1751 ,ASSOCIATED_CHILD_FROZEN_FLAG
1752 ,'N' -- COMPOUNDING_DEP_TAX_FLAG
1753 ,'N' -- ENFORCE_FROM_NATURAL_ACCT_FLAG
1754 ,ATTRIBUTE_CATEGORY
1755 ,ATTRIBUTE1
1756 ,ATTRIBUTE2
1757 ,ATTRIBUTE3
1758 ,ATTRIBUTE4
1759 ,ATTRIBUTE5
1760 ,ATTRIBUTE6
1761 ,ATTRIBUTE7
1762 ,ATTRIBUTE8
1763 ,ATTRIBUTE9
1764 ,ATTRIBUTE10
1765 ,ATTRIBUTE11
1766 ,ATTRIBUTE12
1767 ,ATTRIBUTE13
1768 ,ATTRIBUTE14
1769 ,ATTRIBUTE15
1770 ,GLOBAL_ATTRIBUTE_CATEGORY
1771 ,GLOBAL_ATTRIBUTE1
1772 ,GLOBAL_ATTRIBUTE2
1773 ,GLOBAL_ATTRIBUTE3
1774 ,GLOBAL_ATTRIBUTE4
1775 ,GLOBAL_ATTRIBUTE5
1776 ,GLOBAL_ATTRIBUTE6
1777 ,GLOBAL_ATTRIBUTE7
1778 ,GLOBAL_ATTRIBUTE8
1779 ,GLOBAL_ATTRIBUTE9
1780 ,GLOBAL_ATTRIBUTE10
1781 ,GLOBAL_ATTRIBUTE11
1782 ,GLOBAL_ATTRIBUTE12
1783 ,GLOBAL_ATTRIBUTE13
1784 ,GLOBAL_ATTRIBUTE14
1785 ,GLOBAL_ATTRIBUTE15
1786 ,LAST_MANUAL_ENTRY
1787 ,LEGAL_ENTITY_ID
1788 ,ESTABLISHMENT_ID
1789 ,CREATED_BY
1790 ,CREATION_DATE
1791 ,LAST_UPDATE_DATE
1792 ,LAST_UPDATE_LOGIN
1793 ,LAST_UPDATED_BY
1794 ,OBJECT_VERSION_NUMBER
1795 ,MULTIPLE_JURISDICTIONS_FLAG
1796 ,LEGAL_REPORTING_STATUS
1797 ,ACCOUNT_SOURCE_TAX_RATE_ID
1798 )
1799 (SELECT /*+ ROWID(inv) NO_EXPAND ORDERED
1800 use_nl(fnd_curr,ap_dists,ap_dists1,lines1,rates,regimes,taxes,status,ptp)
1801 index(taxes,ZX_TAXES_B_U2) */
1802 NVL(lines1.org_id,-99) INTERNAL_ORGANIZATION_ID
1803 ,200 APPLICATION_ID
1804 ,'AP_INVOICES' ENTITY_CODE
1805 ,DECODE(inv.INVOICE_TYPE_LOOKUP_CODE ,
1806 'STANDARD', 'STANDARD INVOICES' ,
1807 'CREDIT' , 'STANDARD INVOICES', --Bug 6489409
1808 'DEBIT' , 'STANDARD INVOICES', --Bug 6489409
1809 'MIXED' , 'STANDARD INVOICES', --Bug 6489409
1810 'ADJUSTMENT','STANDARD INVOICES', --Bug 6489409
1811 'PO PRICE ADJUST','STANDARD INVOICES', --Bug 6489409
1812 'INVOICE REQUEST','STANDARD INVOICES', --Bug 6489409
1813 'CREDIT MEMO REQUEST','STANDARD INVOICES',--Bug 6489409
1814 'RETAINAGE RELEASE' ,'STANDARD INVOICES',--Bug 6489409
1815 'PREPAYMENT','PREPAYMENT INVOICES' ,
1816 'EXPENSE REPORT','EXPENSE REPORTS' ,
1817 'INTEREST INVOICE','INTEREST INVOICES','NA') EVENT_CLASS_CODE
1818 ,DECODE(inv.INVOICE_TYPE_LOOKUP_CODE,
1819 'STANDARD','STANDARD INVOICE CREATED',
1820 'PREPAYMENT','PREPAYMENT INVOICE CREATED',
1821 'EXPENSE REPORT','EXPENSE REPORT CREATED',
1822 'INTEREST INVOICE','INTEREST INVOICE CREATED','NA') EVENT_TYPE_CODE
1823 ,(CASE WHEN inv.invoice_type_lookup_code in
1824 ('ADJUSTMENT','CREDIT','DEBIT','INTEREST',
1825 'MIXED','QUICKDEFAULT','PO PRICE ADJUST',
1826 'QUICKMATCH','STANDARD','AWT')
1827 THEN 'PURCHASE_TRANSACTION'
1828 WHEN (inv.invoice_type_lookup_code =
1829 'PREPAYMENT')
1830 THEN 'PURCHASE_PREPAYMENTTRANSACTION'
1831 WHEN (inv.invoice_type_lookup_code =
1832 'EXPENSE REPORT')
1833 THEN 'EXPENSE_REPORT'
1834 ELSE NULL
1835 END) TAX_EVENT_CLASS_CODE
1836 ,'VALIDATE' TAX_EVENT_TYPE_CODE
1837 -- ,NULL DOC_EVENT_STATUS
1838 ,lines1.invoice_id TRX_ID
1839 ,NVL(inv.invoice_date,sysdate) TRX_DATE
1840 ,inv.invoice_currency_code TRX_CURRENCY_CODE
1841 ,NVL(inv.legal_entity_id, -99) LEGAL_ENTITY_ID
1842 ,l_estb_id ESTABLISHMENT_ID
1843 ,inv.invoice_num TRX_NUMBER
1844 -- ,DECODE(ap_dists.charge_applicable_to_dist_id,NULL,1,
1845 ,(RANK() OVER (PARTITION BY inv.invoice_id ORDER BY
1846 ap_dists1.invoice_line_number,
1847 ap_dists.invoice_distribution_id)) TAX_LINE_NUMBER
1848 ,lines1.accounting_date TRX_LINE_DATE
1849 ,NVL(lines1.amount,0) LINE_AMT
1850 ,NVL(lines1.quantity_invoiced, 0) TRX_LINE_QUANTITY
1851 ,lines1.UNIT_PRICE UNIT_PRICE
1852 -- ,NULL RELATED_DOC_APPLICATION_ID
1853 -- ,NULL RELATED_DOC_ENTITY_CODE
1854 -- ,NULL RELATED_DOC_EVENT_CLASS_CODE
1855 -- ,NULL RELATED_DOC_TRX_ID
1856 -- ,NULL RELATED_DOC_TRX_LEVEL_TYPE
1857 -- ,NULL RELATED_DOC_NUMBER
1858 -- ,NULL RELATED_DOC_DATE
1859 ,DECODE(lines1.prepay_invoice_id, NULL, NULL, 200) APPLIED_FROM_APPLICATION_ID
1860 ,DECODE(lines1.prepay_invoice_id, NULL, NULL,
1861 'AP_INVOICES') APPLIED_FROM_ENTITY_CODE
1862 ,DECODE(lines1.prepay_invoice_id, NULL, NULL,
1863 'PREPAYMENT INVOICES') APPLIED_FROM_EVENT_CLASS_CODE
1864 ,lines1.prepay_invoice_id APPLIED_FROM_TRX_ID
1865 ,lines1.prepay_line_number APPLIED_FROM_LINE_ID
1866 -- ,NULL APPLIED_FROM_TRX_NUMBER
1867 ,DECODE(lines1.corrected_inv_id, NULL, NULL, 200) ADJUSTED_DOC_APPLICATION_ID
1868 ,DECODE(lines1.corrected_inv_id, NULL, NULL,
1869 'AP_INVOICES') ADJUSTED_DOC_ENTITY_CODE
1870 ,DECODE(lines1.corrected_inv_id, NULL, NULL,
1871 'STANDARD INVOICES') ADJUSTED_DOC_EVENT_CLASS_CODE
1872 ,lines1.corrected_inv_id ADJUSTED_DOC_TRX_ID
1873 ,lines1.Corrected_Line_Number ADJUSTED_DOC_LINE_ID
1874 -- ,NULL ADJUSTED_DOC_NUMBER
1875 -- ,NULL ADJUSTED_DOC_DATE
1876 ,DECODE(lines1.rcv_transaction_id, NULL, NULL, 707) APPLIED_TO_APPLICATION_ID
1877 ,DECODE(lines1.rcv_transaction_id, NULL, NULL,
1878 'RCV_ACCOUNTING_EVENTS') APPLIED_TO_ENTITY_CODE
1879 ,DECODE(lines1.rcv_transaction_id, NULL, NULL,
1880 'RCPT_REC_INSP') APPLIED_TO_EVENT_CLASS_CODE
1881 ,lines1.rcv_transaction_id APPLIED_TO_TRX_ID
1882 ,lines1.rcv_shipment_line_id APPLIED_TO_LINE_ID
1883 -- ,NULL APPLIED_TO_TRX_NUMBER
1884 ,DECODE(NVL(lines1.po_release_id,lines1.po_header_id),
1885 NULL, NULL, 'SHIPMENT') REF_DOC_TRX_LEVEL_TYPE
1886 ,NVL(lines1.po_release_id, lines1.po_header_id) REF_DOC_TRX_ID
1887 ,lines1.po_line_location_id REF_DOC_LINE_ID
1888 -- ,NULL REF_DOC_LINE_QUANTITY
1889 ,DECODE(lines1.rcv_transaction_id, NULL, NULL,
1890 'LINE') APPLIED_TO_TRX_LEVEL_TYPE
1891 ,DECODE(lines1.prepay_invoice_id, NULL, NULL,
1892 'LINE') APPLIED_FROM_TRX_LEVEL_TYPE
1893 ,DECODE(lines1.corrected_inv_id, NULL, NULL,
1894 'LINE') ADJUSTED_DOC_TRX_LEVEL_TYPE
1895 -- ,NULL INTERNAL_ORG_LOCATION_ID
1896 ,'MIGRATED' RECORD_TYPE_CODE
1897 ,lines1.ASSESSABLE_VALUE -- ASSESSABLE_VALUE
1898 ,'N' TAX_AMT_INCLUDED_FLAG
1899 ,'N' COMPOUNDING_TAX_FLAG
1900 ,DECODE(taxes.tax_type_code,'OFFSET','Y','N') OFFSET_FLAG --Bug 8303411
1901 --,rates.OFFSET_TAX_RATE_CODE OFFSET_TAX_RATE_CODE
1902 -- Bug12986875
1903 ,DECODE((SELECT offset_tax_flag FROM ap_supplier_sites_all ap_sup
1904 WHERE ap_sup.vendor_site_id = inv.vendor_site_id),'Y',
1905 rates.offset_tax_rate_code, NULL) OFFSET_TAX_RATE_CODE
1906 ,ap_dists.DETAIL_TAX_DIST_ID REC_NREC_TAX_DIST_ID
1907 ,ap_dists.line_type_lookup_code AP_DIST_LOOKUP_CODE
1908 -- DECODE(ap_dists.charge_applicable_to_dist_id, NULL, 1,
1909 ,RANK() OVER (PARTITION BY inv.invoice_id,
1910 ap_dists.charge_applicable_to_dist_id
1911 ORDER BY
1912 ap_dists.line_type_lookup_code desc,
1913 ap_dists.invoice_distribution_id) REC_NREC_TAX_DIST_NUMBER
1914 ,ptp.party_tax_profile_id CONTENT_OWNER_ID
1915 ,inv.exchange_date CURRENCY_CONVERSION_DATE
1916 ,inv.exchange_rate CURRENCY_CONVERSION_RATE
1917 ,inv.exchange_rate_type CURRENCY_CONVERSION_TYPE
1918 ,DECODE(lines.line_source,'MANUAL LINE ENTRY','PRORATED_TB','STANDARD_TB') TAXABLE_BASIS_FORMULA -- Bug 9037817
1919 ,ap_dists.set_of_books_id AP_LEDGER_ID
1920 ,fnd_curr.minimum_accountable_unit MINIMUM_ACCOUNTABLE_UNIT
1921 ,NVL(fnd_curr.precision, 0) PRECISION
1922 ,DECODE(NVL(lines1.po_release_id, lines1.po_header_id),
1923 NULL, NULL, 201) REF_DOC_APPLICATION_ID
1924 ,DECODE(lines1.po_release_id, NULL,
1925 DECODE(lines1.po_header_id, NULL, NULL,
1926 'PURCHASE_ORDER'), 'RELEASE') REF_DOC_ENTITY_CODE
1927 ,DECODE(lines1.po_release_id, NULL,
1928 DECODE(lines1.po_header_id, NULL, NULL,
1929 'PO_PA'), 'RELEASE') REF_DOC_EVENT_CLASS_CODE
1930 ,ap_dists.summary_tax_line_id SUMMARY_TAX_LINE_ID
1931 ,rates.TAX TAX
1932 -- ,DECODE(ap_dists.charge_applicable_to_dist_id,NULL,1,
1933 ,RANK() OVER (PARTITION BY inv.invoice_id,
1934 ap_dists1.invoice_line_number,
1935 rates.tax_regime_code, rates.tax
1936 ORDER BY
1937 ap_dists.invoice_distribution_id) TAX_APPORTIONMENT_LINE_NUMBER
1938 ,taxes.tax_currency_code -- TAX_CURRENCY_CODE
1939 ,inv.exchange_date TAX_CURRENCY_CONVERSION_DATE
1940 ,inv.exchange_rate TAX_CURRENCY_CONVERSION_RATE
1941 ,inv.exchange_rate_type TAX_CURRENCY_CONVERSION_TYPE
1942 ,taxes.tax_id -- TAX_ID
1943 ,rates.percentage_rate TAX_RATE
1944 ,rates.tax_rate_code -- TAX_RATE_CODE
1945 ,rates.tax_rate_id -- TAX_RATE_ID
1946 ,rates.tax_regime_code -- TAX_REGIME_CODE
1947 ,rates.rate_type_code
1948 ,regimes.tax_regime_id -- TAX_REGIME_ID
1949 ,rates.tax_status_code -- TAX_STATUS_CODE
1950 ,status.tax_status_id -- TAX_STATUS_ID
1951 ,'LINE' TRX_LEVEL_TYPE
1952 ,lines1.line_number TRX_LINE_ID
1953 ,lines1.line_number TRX_LINE_NUMBER
1954 ,ap_dists.dist_code_combination_id ACCOUNT_CCID
1955 -- ,NULL ACCOUNT_STRING
1956 -- ,NULL ADJUSTED_DOC_TAX_DIST_ID
1957 -- ,NULL APPLIED_FROM_TAX_DIST_ID
1958 -- ,NULL APPLIED_TO_DOC_CURR_CONV_RATE
1959 ,ap_dists.award_id -- AWARD_ID
1960 ,ap_dists.expenditure_item_date -- EXPENDITURE_ITEM_DATE
1961 ,ap_dists.expenditure_organization_id -- EXPENDITURE_ORGANIZATION_ID
1962 ,ap_dists.expenditure_type -- EXPENDITURE_TYPE
1963 ,NULL FUNC_CURR_ROUNDING_ADJUSTMENT
1964 ,ap_dists.ACCOUNTING_DATE GL_DATE
1965 ,ap_dists.intended_use -- INTENDED_USE
1966 ,ap_dists1.distribution_line_number ITEM_DIST_NUMBER
1967 -- ,NULL MRC_LINK_TO_TAX_DIST_ID
1968 -- ,NULL ORIG_REC_NREC_RATE
1969 -- ,NULL ORIG_REC_NREC_TAX_AMT
1970 -- ,NULL ORIG_REC_NREC_TAX_AMT_TAX_CURR
1971 -- ,NULL ORIG_REC_RATE_CODE
1972 -- ,NULL PER_TRX_CURR_UNIT_NR_AMT
1973 -- ,NULL PER_UNIT_NREC_TAX_AMT
1974 -- ,NULL PRD_TAX_AMT
1975 -- ,NULL PRICE_DIFF
1976 ,ap_dists.project_id -- PROJECT_ID
1977 -- ,NULL QTY_DIFF
1978 -- ,NULL RATE_TAX_FACTOR
1979 --,NVL(ap_dists.rec_nrec_rate, 0) REC_NREC_RATE
1980 ,100 REC_NREC_RATE
1981 ,NVL(ap_dists.amount,0) REC_NREC_TAX_AMT
1982 ,ap_dists.base_amount REC_NREC_TAX_AMT_FUNCL_CURR
1983 ,ap_dists.base_amount REC_NREC_TAX_AMT_TAX_CURR
1984 ,DECODE(ap_dists.line_type_lookup_code,
1985 'REC_TAX', 'AD_HOC_RECOVERY', NULL) RECOVERY_RATE_CODE
1986 ,DECODE(ap_dists.line_type_lookup_code,
1987 'REC_TAX', 'STANDARD', NULL) RECOVERY_TYPE_CODE
1988 ,NVL(ap_dists.amount,0) TAX_AMT
1989 ,ap_dists.base_amount TAX_AMT_FUNCL_CURR
1990 ,ap_dists.base_amount TAX_AMT_TAX_CURR
1991 -- ,NULL RECOVERY_TYPE_ID
1992 -- ,NULL REF_DOC_CURR_CONV_RATE
1993 ,ap_dists1.po_distribution_id REF_DOC_DIST_ID
1994 -- ,NULL REF_DOC_PER_UNIT_NREC_TAX_AMT
1995 -- ,NULL REF_DOC_TAX_DIST_ID
1996 -- ,NULL REF_DOC_TRX_LINE_DIST_QTY
1997 -- ,NULL REF_DOC_UNIT_PRICE
1998 -- ,NULL REF_PER_TRX_CURR_UNIT_NR_AMT
1999 ,ap_dists.parent_reversal_id REVERSED_TAX_DIST_ID
2000 -- ,NULL ROUNDING_RULE_CODE
2001 ,ap_dists.task_id -- TASK_ID
2002 ,ap_dists.taxable_base_amount TAXABLE_AMT_FUNCL_CURR
2003 ,ap_dists.taxable_base_amount TAXABLE_AMT_TAX_CURR
2004 ,ap_dists1.amount TRX_LINE_DIST_AMT
2005 --,ap_dists1.invoice_distribution_id TRX_LINE_DIST_ID
2006 -- Bug 9947835
2007 ,DECODE(ap_dists.charge_applicable_to_dist_id,
2008 NULL, lines1.line_number, ap_dists1.invoice_distribution_id) TRX_LINE_DIST_ID
2009 ,NVL(ap_dists1.quantity_invoiced, 0) TRX_LINE_DIST_QTY
2010 ,DECODE(ap_dists.charge_applicable_to_dist_id, NULL,
2011 ap_dists.amount,
2012 SUM (ap_dists.amount) OVER
2013 (PARTITION BY ap_dists.invoice_id,
2014 ap_dists.charge_applicable_to_dist_id)) TRX_LINE_DIST_TAX_AMT
2015 -- ,NULL UNROUNDED_REC_NREC_TAX_AMT
2016 -- ,NULL UNROUNDED_TAXABLE_AMT
2017 ,ap_dists.TAXABLE_AMOUNT TAXABLE_AMT
2018 ,ap_dists.ATTRIBUTE_CATEGORY -- ATTRIBUTE_CATEGORY
2019 ,ap_dists.ATTRIBUTE1 -- ATTRIBUTE1
2020 ,ap_dists.ATTRIBUTE2 -- ATTRIBUTE2
2021 ,ap_dists.ATTRIBUTE3 -- ATTRIBUTE3
2022 ,ap_dists.ATTRIBUTE4 -- ATTRIBUTE4
2023 ,ap_dists.ATTRIBUTE5 -- ATTRIBUTE5
2024 ,ap_dists.ATTRIBUTE6 -- ATTRIBUTE6
2025 ,ap_dists.ATTRIBUTE7 -- ATTRIBUTE7
2026 ,ap_dists.ATTRIBUTE8 -- ATTRIBUTE8
2027 ,ap_dists.ATTRIBUTE9 -- ATTRIBUTE9
2028 ,ap_dists.ATTRIBUTE10 -- ATTRIBUTE10
2029 ,ap_dists.ATTRIBUTE11 -- ATTRIBUTE11
2030 ,ap_dists.ATTRIBUTE12 -- ATTRIBUTE12
2031 ,ap_dists.ATTRIBUTE13 -- ATTRIBUTE13
2032 ,ap_dists.ATTRIBUTE14 -- ATTRIBUTE14
2033 ,ap_dists.ATTRIBUTE15 -- ATTRIBUTE15
2034 ,ap_dists.GLOBAL_ATTRIBUTE_CATEGORY -- GLOBAL_ATTRIBUTE_CATEGORY
2035 ,ap_dists.GLOBAL_ATTRIBUTE1 -- GLOBAL_ATTRIBUTE1
2036 ,ap_dists.GLOBAL_ATTRIBUTE2 -- GLOBAL_ATTRIBUTE2
2037 ,ap_dists.GLOBAL_ATTRIBUTE3 -- GLOBAL_ATTRIBUTE3
2038 ,ap_dists.GLOBAL_ATTRIBUTE4 -- GLOBAL_ATTRIBUTE4
2039 ,ap_dists.GLOBAL_ATTRIBUTE5 -- GLOBAL_ATTRIBUTE5
2040 ,ap_dists.GLOBAL_ATTRIBUTE6 -- GLOBAL_ATTRIBUTE6
2041 ,ap_dists.GLOBAL_ATTRIBUTE7 -- GLOBAL_ATTRIBUTE7
2042 ,ap_dists.GLOBAL_ATTRIBUTE8 -- GLOBAL_ATTRIBUTE8
2043 ,ap_dists.GLOBAL_ATTRIBUTE9 -- GLOBAL_ATTRIBUTE9
2044 ,ap_dists.GLOBAL_ATTRIBUTE10 -- GLOBAL_ATTRIBUTE10
2045 ,ap_dists.GLOBAL_ATTRIBUTE11 -- GLOBAL_ATTRIBUTE11
2046 ,ap_dists.GLOBAL_ATTRIBUTE12 -- GLOBAL_ATTRIBUTE12
2047 ,ap_dists.GLOBAL_ATTRIBUTE13 -- GLOBAL_ATTRIBUTE13
2048 ,ap_dists.GLOBAL_ATTRIBUTE14 -- GLOBAL_ATTRIBUTE14
2049 ,ap_dists.GLOBAL_ATTRIBUTE15 -- GLOBAL_ATTRIBUTE15
2050 ,ap_dists.GLOBAL_ATTRIBUTE16 -- GLOBAL_ATTRIBUTE16
2051 ,ap_dists.GLOBAL_ATTRIBUTE17 -- GLOBAL_ATTRIBUTE17
2052 ,ap_dists.GLOBAL_ATTRIBUTE18 -- GLOBAL_ATTRIBUTE18
2053 ,ap_dists.GLOBAL_ATTRIBUTE19 -- GLOBAL_ATTRIBUTE19
2054 ,ap_dists.GLOBAL_ATTRIBUTE20 -- GLOBAL_ATTRIBUTE20
2055 ,'Y' HISTORICAL_FLAG
2056 ,'N' OVERRIDDEN_FLAG
2057 ,'N' SELF_ASSESSED_FLAG
2058 ,'Y' TAX_APPORTIONMENT_FLAG
2059 ,DECODE(ap_dists.charge_applicable_to_dist_id,
2060 NULL, 'Y', 'N') TAX_ONLY_LINE_FLAG
2061 ,'N' INCLUSIVE_FLAG
2062 ,'N' MRC_TAX_DIST_FLAG
2063 ,'N' REC_TYPE_RULE_FLAG
2064 ,'N' NEW_REC_RATE_CODE_FLAG
2065 ,NVL(ap_dists.tax_recoverable_flag, 'N') RECOVERABLE_FLAG
2066 ,ap_dists.reversal_flag REVERSE_FLAG
2067 ,'N' REC_RATE_DET_RULE_FLAG
2068 ,'N' BACKWARD_COMPATIBILITY_FLAG
2069 ,DECODE(ap_dists.charge_applicable_to_dist_id, -- For Tax Only Line stamp
2070 NULL,'Y', -- Freeze_Flag always Y
2071 DECODE(ap_dists.accounting_event_id, -- Else if acct event id is
2072 NULL,'N','Y') -- Not Null then Y Else N
2073 ) FREEZE_FLAG
2074 ,DECODE(ap_dists.charge_applicable_to_dist_id, -- For Tax Only Line stamp
2075 NULL,'Y', -- Associated_Child_Frozen_Flag always Y
2076 DECODE(ap_dists.accounting_event_id, -- Else if acct event id is
2077 NULL,'N','Y') -- Not Null then Y Else N
2078 ) ASSOCIATED_CHILD_FROZEN_FLAG
2079 ,DECODE(ap_dists.posted_flag, 'Y', 'A', NULL) POSTING_FLAG
2080 ,NVL(lines1.accounting_date,
2081 NVL(inv.invoice_date, sysdate)) TAX_DATE
2082 ,NVL(lines1.accounting_date,
2083 NVL(inv.invoice_date, sysdate)) TAX_DETERMINE_DATE
2084 ,NVL(lines1.accounting_date,
2085 NVL(inv.invoice_date, sysdate)) TAX_POINT_DATE
2086 ,1 CREATED_BY
2087 ,SYSDATE CREATION_DATE
2088 --,NULL LAST_MANUAL_ENTRY
2089 ,SYSDATE LAST_UPDATE_DATE
2090 ,1 LAST_UPDATE_LOGIN
2091 ,1 LAST_UPDATED_BY
2092 ,1 OBJECT_VERSION_NUMBER
2093 ,ap_dists1.old_dist_line_number ORIG_AP_CHRG_DIST_NUM
2094 ,ap_dists1.old_distribution_id ORIG_AP_CHRG_DIST_ID
2095 ,ap_dists.old_dist_line_number ORIG_AP_TAX_DIST_NUM
2096 ,ap_dists.old_distribution_id ORIG_AP_TAX_DIST_ID
2097 ,'N' MULTIPLE_JURISDICTIONS_FLAG
2098 ,DECODE(ap_dists.posted_flag, 'Y', '111111111111111',
2099 'P', '111111111111111',
2100 '000000000000000') LEGAL_REPORTING_STATUS
2101 ,DECODE(lines.discarded_flag, 'Y', 'Y', 'N') CANCEL_FLAG
2102 ,NVL(rates.def_rec_settlement_option_code,
2103 taxes.def_rec_settlement_option_code) DEF_REC_SETTLEMENT_OPTION_CODE
2104 --,TAX_JURISDICTION_ID
2105 ,rates.tax_rate_id ACCOUNT_SOURCE_TAX_RATE_ID
2106 ,(SELECT tax_rate_id FROM zx_rates_b
2107 WHERE tax_rate_code = 'AD_HOC_RECOVERY'
2108 AND rate_type_code = 'RECOVERY'
2109 AND tax_regime_code = rates.tax_regime_code
2110 AND tax = rates.tax
2111 AND content_owner_id = ptp.party_tax_profile_id
2112 AND record_type_code = 'MIGRATED'
2113 AND tax_class = 'INPUT') RECOVERY_RATE_ID
2114 ,DECODE(lines.line_source,'MANUAL LINE ENTRY','Y','N') MANUALLY_ENTERED_FLAG --BUG7146063
2115 ,DECODE(lines.line_source,'MANUAL LINE ENTRY','TAX_AMOUNT',NULL) LAST_MANUAL_ENTRY --BUG7146063
2116 FROM ap_invoices_all inv,
2117 fnd_currencies fnd_curr,
2118 -- fnd_document_sequences fds,
2119 ap_invoice_distributions_all ap_dists,
2120 ap_invoice_distributions_all ap_dists1,
2121 ap_invoice_lines_all lines1,
2122 ap_invoice_lines_all lines,
2123 zx_rates_b rates,
2124 zx_regimes_b regimes,
2125 zx_taxes_b taxes,
2126 zx_status_b status,
2127 zx_party_tax_profile ptp
2128 WHERE inv.invoice_id = p_upg_trx_info_rec.trx_id
2129 AND fnd_curr.currency_code = inv.invoice_currency_code
2130 -- AND inv.doc_sequence_id = fds.doc_sequence_id(+)
2131 AND ap_dists.invoice_id = inv.invoice_id
2132 AND ap_dists.line_type_lookup_code IN ('REC_TAX','NONREC_TAX')
2133 AND NVL(ap_dists.historical_flag, 'N') = 'Y'
2134 -- AND ap_dists1.invoice_id = ap_dists.invoice_id
2135 AND ap_dists1.invoice_distribution_id = NVL(ap_dists.charge_applicable_to_dist_id,
2136 ap_dists.invoice_distribution_id)
2137 AND lines1.invoice_id = ap_dists1.invoice_id
2138 AND lines1.line_number = ap_dists1.invoice_line_number
2139 AND NVL(lines1.historical_flag, 'N') = 'Y'
2140 AND lines.invoice_id = ap_dists.invoice_id
2141 AND lines.line_number = ap_dists.invoice_line_number
2142 AND NVL(lines.historical_flag, 'N') = 'Y'
2143 AND rates.source_id(+) = ap_dists.tax_code_id
2144 AND regimes.tax_regime_code(+) = rates.tax_regime_code
2145 AND taxes.tax_regime_code(+) = rates.tax_regime_code
2146 AND taxes.tax(+) = rates.tax
2147 AND taxes.content_owner_id(+) = rates.content_owner_id
2148 AND status.tax_regime_code(+) = rates.tax_regime_code
2149 AND status.tax(+) = rates.tax
2150 AND status.tax_status_code(+) = rates.tax_status_code
2151 AND status.content_owner_id(+) = rates.content_owner_id
2152 -- AND NVL(taxes.effective_from,
2153 -- NVL(lines1.accounting_date, NVL(inv.invoice_date, sysdate)))
2154 -- <= NVL(lines1.accounting_date, NVL(inv.invoice_date, sysdate))
2155 -- AND (NVL(taxes.effective_to,
2156 -- NVL(lines1.accounting_date,
2157 -- NVL(inv.invoice_date, sysdate)) )
2158 -- >= NVL(lines1.accounting_date, NVL(inv.invoice_date, sysdate))
2159 -- OR taxes.effective_to IS NULL)
2160 AND ptp.party_type_code = 'OU'
2161 AND ptp.party_id = DECODE(l_multi_org_flag,'N', l_org_id, ap_dists.org_id)
2162 AND NOT EXISTS -- Bug 6738188
2163 (SELECT 1 FROM zx_lines zxl
2164 WHERE zxl.application_id = 200
2165 AND zxl.event_class_code = DECODE(inv.INVOICE_TYPE_LOOKUP_CODE,
2166 'STANDARD', 'STANDARD INVOICES',
2167 'CREDIT' , 'STANDARD INVOICES',
2168 'DEBIT' , 'STANDARD INVOICES',
2169 'MIXED' , 'STANDARD INVOICES',
2170 'ADJUSTMENT','STANDARD INVOICES',
2171 'PO PRICE ADJUST','STANDARD INVOICES',
2172 'INVOICE REQUEST','STANDARD INVOICES',
2173 'CREDIT MEMO REQUEST','STANDARD INVOICES',
2174 'RETAINAGE RELEASE' ,'STANDARD INVOICES',
2175 'PREPAYMENT', 'PREPAYMENT INVOICES',
2176 'EXPENSE REPORT', 'EXPENSE REPORTS',
2177 'INTEREST INVOICE', 'INTEREST INVOICES','NA')
2178 AND zxl.trx_id = inv.invoice_id
2179 AND zxl.entity_code = 'AP_INVOICES'
2180 AND zxl.trx_line_id = lines1.line_number)
2181 );
2182
2183
2184 IF (g_level_procedure >= g_current_runtime_level ) THEN
2185 FND_LOG.STRING(g_level_procedure,
2186 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_ap.END',
2187 'ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_ap(+)');
2188 END IF;
2189
2190 EXCEPTION
2191 WHEN OTHERS THEN
2192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2193 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2194 FND_LOG.STRING(g_level_unexpected,
2195 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_ap',
2196 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2197 FND_LOG.STRING(g_level_unexpected,
2198 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_ap.END',
2199 'ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_ap(-)');
2200 END IF;
2201
2202 END upgrade_trx_on_fly_ap;
2203
2204 -------------------------------------------------------------------------------
2205 -- PUBLIC PROCEDURE
2206 -- upgrade_trx_on_fly_blk_ap
2207 --
2208 -- DESCRIPTION
2209 -- handle bulk on the fly migration for AP, called from validate and default API
2210 --
2211 -------------------------------------------------------------------------------
2212 PROCEDURE upgrade_trx_on_fly_blk_ap(
2213 x_return_status OUT NOCOPY VARCHAR2
2214 ) AS
2215
2216 l_multi_org_flag fnd_product_groups.multi_org_flag%TYPE;
2217 l_org_id NUMBER;
2218
2219 BEGIN
2220 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2221
2222 IF (g_level_procedure >= g_current_runtime_level ) THEN
2223 FND_LOG.STRING(g_level_procedure,
2224 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_blk_ap.BEGIN',
2225 'ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_blk_ap(+)');
2226 END IF;
2227
2228 x_return_status := FND_API.G_RET_STS_SUCCESS;
2229 SELECT multi_org_flag INTO l_multi_org_flag FROM fnd_product_groups;
2230
2231 -- for single org environment, get value of org_id from profile
2232 IF l_multi_org_flag = 'N' THEN
2233 FND_PROFILE.GET('ORG_ID',l_org_id);
2234 IF l_org_id is NULL THEN
2235 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2236 FND_LOG.STRING(g_level_unexpected,
2237 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_blk_ar',
2238 'Current envionment is a Single Org environment,'||
2239 ' but peofile ORG_ID is not set up');
2240 END IF;
2241
2242 END IF;
2243 END IF;
2244
2245 IF (g_level_statement >= g_current_runtime_level ) THEN
2246 FND_LOG.STRING(g_level_statement,
2247 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_blk_ap',
2248 'Inserting data into zx_lines_det_factors and zx_lines_summary');
2249 END IF;
2250
2251 -- Insert data into zx_lines_det_factors and zx_lines_summary
2252 --
2253 INSERT ALL
2254 WHEN AP_LINE_LOOKUP_CODE IN ('ITEM', 'PREPAY','FREIGHT','MISCELLANEOUS') OR
2255 TAX_ONLY_LINE_FLAG = 'Y'
2256 THEN
2257 INTO ZX_LINES_DET_FACTORS (
2258 EVENT_ID
2259 ,OBJECT_VERSION_NUMBER
2260 ,INTERNAL_ORGANIZATION_ID
2261 ,APPLICATION_ID
2262 ,ENTITY_CODE
2263 ,EVENT_CLASS_CODE
2264 ,EVENT_TYPE_CODE
2265 ,TAX_EVENT_CLASS_CODE
2266 ,TAX_EVENT_TYPE_CODE
2267 -- ,DOC_EVENT_STATUS
2268 ,LINE_LEVEL_ACTION
2269 ,LINE_CLASS
2270 -- ,APPLICATION_DOC_STATUS
2271 ,TRX_ID
2272 ,TRX_LINE_ID
2273 ,TRX_LEVEL_TYPE
2274 ,TRX_DATE
2275 ,LEDGER_ID
2276 ,TRX_CURRENCY_CODE
2277 ,CURRENCY_CONVERSION_DATE
2278 ,CURRENCY_CONVERSION_RATE
2279 ,CURRENCY_CONVERSION_TYPE
2280 ,MINIMUM_ACCOUNTABLE_UNIT
2281 ,PRECISION
2282 ,LEGAL_ENTITY_ID
2283 -- ,ESTABLISHMENT_ID
2284 ,DEFAULT_TAXATION_COUNTRY
2285 ,TRX_NUMBER
2286 ,TRX_LINE_NUMBER
2287 ,TRX_LINE_DESCRIPTION
2288 ,TRX_DESCRIPTION
2289 ,TRX_COMMUNICATED_DATE
2290 ,TRX_LINE_GL_DATE
2291 ,BATCH_SOURCE_ID
2292 -- ,BATCH_SOURCE_NAME
2293 ,DOC_SEQ_ID
2294 ,DOC_SEQ_NAME
2295 ,DOC_SEQ_VALUE
2296 ,TRX_DUE_DATE
2297 -- ,TRX_TYPE_DESCRIPTION
2298 ,TRX_LINE_TYPE
2299 ,TRX_LINE_DATE
2300 -- ,TRX_SHIPPING_DATE
2301 -- ,TRX_RECEIPT_DATE
2302 ,LINE_AMT
2303 ,TRX_LINE_QUANTITY
2304 ,UNIT_PRICE
2305 ,PRODUCT_ID
2306 -- ,PRODUCT_ORG_ID
2307 ,UOM_CODE
2308 ,PRODUCT_TYPE
2309 -- ,PRODUCT_CODE
2310 ,PRODUCT_DESCRIPTION
2311 ,FIRST_PTY_ORG_ID
2312 -- ,ASSET_NUMBER
2313 -- ,ASSET_ACCUM_DEPRECIATION
2314 -- ,ASSET_TYPE
2315 -- ,ASSET_COST
2316 ,ACCOUNT_CCID
2317 -- ,ACCOUNT_STRING
2318 -- ,RELATED_DOC_APPLICATION_ID
2319 -- ,RELATED_DOC_ENTITY_CODE
2320 -- ,RELATED_DOC_EVENT_CLASS_CODE
2321 -- ,RELATED_DOC_TRX_ID
2322 -- ,RELATED_DOC_NUMBER
2323 -- ,RELATED_DOC_DATE
2324 ,APPLIED_FROM_APPLICATION_ID
2325 ,APPLIED_FROM_ENTITY_CODE
2326 ,APPLIED_FROM_EVENT_CLASS_CODE
2327 ,APPLIED_FROM_TRX_ID
2328 ,APPLIED_FROM_LINE_ID
2329 ,ADJUSTED_DOC_APPLICATION_ID
2330 ,ADJUSTED_DOC_ENTITY_CODE
2331 ,ADJUSTED_DOC_EVENT_CLASS_CODE
2332 ,ADJUSTED_DOC_TRX_ID
2333 ,ADJUSTED_DOC_LINE_ID
2334 -- ,ADJUSTED_DOC_NUMBER
2335 -- ,ADJUSTED_DOC_DATE
2336 ,APPLIED_TO_APPLICATION_ID
2337 ,APPLIED_TO_ENTITY_CODE
2338 ,APPLIED_TO_EVENT_CLASS_CODE
2339 ,APPLIED_TO_TRX_ID
2340 ,APPLIED_TO_TRX_LINE_ID
2341 -- ,APPLIED_TO_TRX_NUMBER
2342 ,REF_DOC_TRX_LEVEL_TYPE
2343 ,REF_DOC_APPLICATION_ID
2344 ,REF_DOC_ENTITY_CODE
2345 ,REF_DOC_EVENT_CLASS_CODE
2346 ,REF_DOC_TRX_ID
2347 ,REF_DOC_LINE_ID
2348 -- ,REF_DOC_LINE_QUANTITY
2349 ,APPLIED_TO_TRX_LEVEL_TYPE
2350 ,APPLIED_FROM_TRX_LEVEL_TYPE
2351 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
2352 ,MERCHANT_PARTY_NAME
2353 ,MERCHANT_PARTY_DOCUMENT_NUMBER
2354 ,MERCHANT_PARTY_REFERENCE
2355 ,MERCHANT_PARTY_TAXPAYER_ID
2356 ,MERCHANT_PARTY_TAX_REG_NUMBER
2357 -- ,MERCHANT_PARTY_ID
2358 ,MERCHANT_PARTY_COUNTRY
2359 ,START_EXPENSE_DATE
2360 ,SHIP_TO_LOCATION_ID
2361 -- ,SHIP_FROM_LOCATION_ID
2362 -- ,BILL_TO_LOCATION_ID
2363 -- ,BILL_FROM_LOCATION_ID
2364 -- ,SHIP_TO_PARTY_TAX_PROF_ID
2365 -- ,SHIP_FROM_PARTY_TAX_PROF_ID
2366 -- ,BILL_TO_PARTY_TAX_PROF_ID
2367 -- ,BILL_FROM_PARTY_TAX_PROF_ID
2368 -- ,SHIP_TO_SITE_TAX_PROF_ID
2369 -- ,SHIP_FROM_SITE_TAX_PROF_ID
2370 -- ,BILL_TO_SITE_TAX_PROF_ID
2371 -- ,BILL_FROM_SITE_TAX_PROF_ID
2372 -- ,MERCHANT_PARTY_TAX_PROF_ID
2373 -- ,HQ_ESTB_PARTY_TAX_PROF_ID
2374 -- ,CTRL_TOTAL_LINE_TX_AMT
2375 -- ,CTRL_TOTAL_HDR_TX_AMT
2376 -- ,INPUT_TAX_CLASSIFICATION_CODE
2377 -- ,OUTPUT_TAX_CLASSIFICATION_CODE
2378 -- ,INTERNAL_ORG_LOCATION_ID
2379 ,RECORD_TYPE_CODE
2380 ,PRODUCT_FISC_CLASSIFICATION
2381 ,PRODUCT_CATEGORY
2382 ,USER_DEFINED_FISC_CLASS
2383 ,ASSESSABLE_VALUE
2384 ,TRX_BUSINESS_CATEGORY
2385 ,SUPPLIER_TAX_INVOICE_NUMBER
2386 ,SUPPLIER_TAX_INVOICE_DATE
2387 ,SUPPLIER_EXCHANGE_RATE
2388 ,TAX_INVOICE_DATE
2389 ,TAX_INVOICE_NUMBER
2390 ,DOCUMENT_SUB_TYPE
2391 ,LINE_INTENDED_USE
2392 ,PORT_OF_ENTRY_CODE
2393 -- ,SOURCE_APPLICATION_ID
2394 -- ,SOURCE_ENTITY_CODE
2395 -- ,SOURCE_EVENT_CLASS_CODE
2396 -- ,SOURCE_TRX_ID
2397 -- ,SOURCE_LINE_ID
2398 -- ,SOURCE_TRX_LEVEL_TYPE
2399 ,HISTORICAL_FLAG
2400 ,LINE_AMT_INCLUDES_TAX_FLAG
2401 ,CTRL_HDR_TX_APPL_FLAG
2402 ,TAX_REPORTING_FLAG
2403 ,TAX_AMT_INCLUDED_FLAG
2404 ,COMPOUNDING_TAX_FLAG
2405 ,INCLUSIVE_TAX_OVERRIDE_FLAG
2406 ,THRESHOLD_INDICATOR_FLAG
2407 ,USER_UPD_DET_FACTORS_FLAG
2408 ,TAX_PROCESSING_COMPLETED_FLAG
2409 ,ASSET_FLAG
2410 ,CREATION_DATE
2411 ,CREATED_BY
2412 ,LAST_UPDATE_DATE
2413 ,LAST_UPDATED_BY
2414 ,LAST_UPDATE_LOGIN
2415 ,EVENT_CLASS_MAPPING_ID
2416 ,SHIP_THIRD_PTY_ACCT_ID
2417 ,SHIP_THIRD_PTY_ACCT_SITE_ID
2418 ,GLOBAL_ATTRIBUTE_CATEGORY
2419 ,GLOBAL_ATTRIBUTE1
2420 -- ,ICX_SESSION_ID
2421 -- ,TRX_LINE_CURRENCY_CODE
2422 -- ,TRX_LINE_CURRENCY_CONV_RATE
2423 -- ,TRX_LINE_CURRENCY_CONV_DATE
2424 -- ,TRX_LINE_PRECISION
2425 -- ,TRX_LINE_MAU
2426 -- ,TRX_LINE_CURRENCY_CONV_TYPE
2427 -- ,INTERFACE_ENTITY_CODE
2428 -- ,INTERFACE_LINE_ID
2429 -- ,SOURCE_TAX_LINE_ID
2430 ,BILL_THIRD_PTY_ACCT_ID
2431 ,BILL_THIRD_PTY_ACCT_SITE_ID
2432 )
2433 VALUES(
2434 EVENT_ID
2435 ,OBJECT_VERSION_NUMBER
2436 ,INTERNAL_ORGANIZATION_ID
2437 ,APPLICATION_ID
2438 ,ENTITY_CODE
2439 ,EVENT_CLASS_CODE
2440 ,EVENT_TYPE_CODE
2441 ,TAX_EVENT_CLASS_CODE
2442 ,TAX_EVENT_TYPE_CODE
2443 -- ,DOC_EVENT_STATUS
2444 ,LINE_LEVEL_ACTION
2445 ,LINE_CLASS
2446 -- ,APPLICATION_DOC_STATUS
2447 ,TRX_ID
2448 ,TRX_LINE_ID
2449 ,TRX_LEVEL_TYPE
2450 ,TRX_DATE
2451 ,LEDGER_ID
2452 ,TRX_CURRENCY_CODE
2453 ,CURRENCY_CONVERSION_DATE
2454 ,CURRENCY_CONVERSION_RATE
2455 ,CURRENCY_CONVERSION_TYPE
2456 ,MINIMUM_ACCOUNTABLE_UNIT
2457 ,PRECISION
2458 ,LEGAL_ENTITY_ID
2459 -- ,ESTABLISHMENT_ID
2460 ,DEFAULT_TAXATION_COUNTRY
2461 ,TRX_NUMBER
2462 ,TRX_LINE_NUMBER
2463 ,TRX_LINE_DESCRIPTION
2464 ,TRX_DESCRIPTION
2465 ,TRX_COMMUNICATED_DATE
2466 ,TRX_LINE_GL_DATE
2467 ,BATCH_SOURCE_ID
2468 -- ,BATCH_SOURCE_NAME
2469 ,DOC_SEQ_ID
2470 ,DOC_SEQ_NAME
2471 ,DOC_SEQ_VALUE
2472 ,TRX_DUE_DATE
2473 -- ,TRX_TYPE_DESCRIPTION
2474 ,TRX_LINE_TYPE
2475 ,TRX_LINE_DATE
2476 -- ,TRX_SHIPPING_DATE
2477 -- ,TRX_RECEIPT_DATE
2478 ,LINE_AMT
2479 ,TRX_LINE_QUANTITY
2480 ,UNIT_PRICE
2481 ,PRODUCT_ID
2482 -- ,PRODUCT_ORG_ID
2483 ,UOM_CODE
2484 ,PRODUCT_TYPE
2485 -- ,PRODUCT_CODE
2486 ,PRODUCT_DESCRIPTION
2487 ,FIRST_PTY_ORG_ID
2488 -- ,ASSET_NUMBER
2489 -- ,ASSET_ACCUM_DEPRECIATION
2490 -- ,ASSET_TYPE
2491 -- ,ASSET_COST
2492 ,ACCOUNT_CCID
2493 -- ,ACCOUNT_STRING
2494 -- ,RELATED_DOC_APPLICATION_ID
2495 -- ,RELATED_DOC_ENTITY_CODE
2496 -- ,RELATED_DOC_EVENT_CLASS_CODE
2497 -- ,RELATED_DOC_TRX_ID
2498 -- ,RELATED_DOC_NUMBER
2499 -- ,RELATED_DOC_DATE
2500 ,APPLIED_FROM_APPLICATION_ID
2501 ,APPLIED_FROM_ENTITY_CODE
2502 ,APPLIED_FROM_EVENT_CLASS_CODE
2503 ,APPLIED_FROM_TRX_ID
2504 ,APPLIED_FROM_LINE_ID
2505 ,ADJUSTED_DOC_APPLICATION_ID
2506 ,ADJUSTED_DOC_ENTITY_CODE
2507 ,ADJUSTED_DOC_EVENT_CLASS_CODE
2508 ,ADJUSTED_DOC_TRX_ID
2509 ,ADJUSTED_DOC_LINE_ID
2510 -- ,ADJUSTED_DOC_NUMBER
2511 -- ,ADJUSTED_DOC_DATE
2512 ,APPLIED_TO_APPLICATION_ID
2513 ,APPLIED_TO_ENTITY_CODE
2514 ,APPLIED_TO_EVENT_CLASS_CODE
2515 ,APPLIED_TO_TRX_ID
2516 ,APPLIED_TO_TRX_LINE_ID
2517 -- ,APPLIED_TO_TRX_NUMBER
2518 ,REF_DOC_TRX_LEVEL_TYPE
2519 ,REF_DOC_APPLICATION_ID
2520 ,REF_DOC_ENTITY_CODE
2521 ,REF_DOC_EVENT_CLASS_CODE
2522 ,REF_DOC_TRX_ID
2523 ,REF_DOC_LINE_ID
2524 -- ,REF_DOC_LINE_QUANTITY
2525 ,APPLIED_TO_TRX_LEVEL_TYPE
2526 ,APPLIED_FROM_TRX_LEVEL_TYPE
2527 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
2528 ,MERCHANT_PARTY_NAME
2529 ,MERCHANT_PARTY_DOCUMENT_NUMBER
2530 ,MERCHANT_PARTY_REFERENCE
2531 ,MERCHANT_PARTY_TAXPAYER_ID
2532 ,MERCHANT_PARTY_TAX_REG_NUMBER
2533 -- ,MERCHANT_PARTY_ID
2534 ,MERCHANT_PARTY_COUNTRY
2535 ,START_EXPENSE_DATE
2536 ,SHIP_TO_LOCATION_ID
2537 -- ,SHIP_FROM_LOCATION_ID
2538 -- ,BILL_TO_LOCATION_ID
2539 -- ,BILL_FROM_LOCATION_ID
2540 -- ,SHIP_TO_PARTY_TAX_PROF_ID
2541 -- ,SHIP_FROM_PARTY_TAX_PROF_ID
2542 -- ,BILL_TO_PARTY_TAX_PROF_ID
2543 -- ,BILL_FROM_PARTY_TAX_PROF_ID
2544 -- ,SHIP_TO_SITE_TAX_PROF_ID
2545 -- ,SHIP_FROM_SITE_TAX_PROF_ID
2546 -- ,BILL_TO_SITE_TAX_PROF_ID
2547 -- ,BILL_FROM_SITE_TAX_PROF_ID
2548 -- ,MERCHANT_PARTY_TAX_PROF_ID
2549 -- ,HQ_ESTB_PARTY_TAX_PROF_ID
2550 -- ,CTRL_TOTAL_LINE_TX_AMT
2551 -- ,CTRL_TOTAL_HDR_TX_AMT
2552 -- ,INPUT_TAX_CLASSIFICATION_CODE
2553 -- ,OUTPUT_TAX_CLASSIFICATION_CODE
2554 -- ,INTERNAL_ORG_LOCATION_ID
2555 ,RECORD_TYPE_CODE
2556 ,PRODUCT_FISC_CLASSIFICATION
2557 ,PRODUCT_CATEGORY
2558 ,USER_DEFINED_FISC_CLASS
2559 ,ASSESSABLE_VALUE
2560 ,TRX_BUSINESS_CATEGORY
2561 ,SUPPLIER_TAX_INVOICE_NUMBER
2562 ,SUPPLIER_TAX_INVOICE_DATE
2563 ,SUPPLIER_EXCHANGE_RATE
2564 ,TAX_INVOICE_DATE
2565 ,TAX_INVOICE_NUMBER
2566 ,DOCUMENT_SUB_TYPE
2567 ,LINE_INTENDED_USE
2568 ,PORT_OF_ENTRY_CODE
2569 -- ,SOURCE_APPLICATION_ID
2570 -- ,SOURCE_ENTITY_CODE
2571 -- ,SOURCE_EVENT_CLASS_CODE
2572 -- ,SOURCE_TRX_ID
2573 -- ,SOURCE_LINE_ID
2574 -- ,SOURCE_TRX_LEVEL_TYPE
2575 ,HISTORICAL_FLAG
2576 ,LINE_AMT_INCLUDES_TAX_FLAG
2577 ,CTRL_HDR_TX_APPL_FLAG
2578 ,TAX_REPORTING_FLAG
2579 ,TAX_AMT_INCLUDED_FLAG
2580 ,COMPOUNDING_TAX_FLAG
2581 ,INCLUSIVE_TAX_OVERRIDE_FLAG
2582 ,THRESHOLD_INDICATOR_FLAG
2583 ,USER_UPD_DET_FACTORS_FLAG
2584 ,TAX_PROCESSING_COMPLETED_FLAG
2585 ,ASSET_FLAG
2586 ,CREATION_DATE
2587 ,CREATED_BY
2588 ,LAST_UPDATE_DATE
2589 ,LAST_UPDATED_BY
2590 ,LAST_UPDATE_LOGIN
2591 ,EVENT_CLASS_MAPPING_ID
2592 ,SHIP_THIRD_PTY_ACCT_ID
2593 ,SHIP_THIRD_PTY_ACCT_SITE_ID
2594 ,GLOBAL_ATTRIBUTE_CATEGORY
2595 ,GLOBAL_ATTRIBUTE1
2596 -- ,ICX_SESSION_ID
2597 -- ,TRX_LINE_CURRENCY_CODE
2598 -- ,TRX_LINE_CURRENCY_CONV_RATE
2599 -- ,TRX_LINE_CURRENCY_CONV_DATE
2600 -- ,TRX_LINE_PRECISION
2601 -- ,TRX_LINE_MAU
2602 -- ,TRX_LINE_CURRENCY_CONV_TYPE
2603 -- ,INTERFACE_ENTITY_CODE
2604 -- ,INTERFACE_LINE_ID
2605 -- ,SOURCE_TAX_LINE_ID
2606 ,BILL_THIRD_PTY_ACCT_ID
2607 ,BILL_THIRD_PTY_ACCT_SITE_ID
2608 )
2609 WHEN AP_LINE_LOOKUP_CODE = 'TAX' THEN
2610 INTO ZX_LINES_SUMMARY (
2611 SUMMARY_TAX_LINE_ID
2612 ,INTERNAL_ORGANIZATION_ID
2613 ,APPLICATION_ID
2614 ,ENTITY_CODE
2615 ,EVENT_CLASS_CODE
2616 ,TRX_ID
2617 ,TRX_NUMBER
2618 ,APPLIED_FROM_APPLICATION_ID
2619 ,APPLIED_FROM_EVENT_CLASS_CODE
2620 ,APPLIED_FROM_ENTITY_CODE
2621 ,APPLIED_FROM_TRX_ID
2622 ,ADJUSTED_DOC_APPLICATION_ID
2623 ,ADJUSTED_DOC_ENTITY_CODE
2624 ,ADJUSTED_DOC_EVENT_CLASS_CODE
2625 ,ADJUSTED_DOC_TRX_ID
2626 ,SUMMARY_TAX_LINE_NUMBER
2627 ,CONTENT_OWNER_ID
2628 ,TAX_REGIME_CODE
2629 ,TAX
2630 ,TAX_STATUS_CODE
2631 ,TAX_RATE_ID
2632 ,TAX_RATE_CODE
2633 ,TAX_RATE
2634 ,TAX_AMT
2635 ,TAX_AMT_TAX_CURR
2636 ,TAX_AMT_FUNCL_CURR
2637 ,TAX_JURISDICTION_CODE
2638 ,TOTAL_REC_TAX_AMT
2639 ,TOTAL_REC_TAX_AMT_FUNCL_CURR
2640 ,TOTAL_NREC_TAX_AMT
2641 ,TOTAL_NREC_TAX_AMT_FUNCL_CURR
2642 ,LEDGER_ID
2643 ,LEGAL_ENTITY_ID
2644 -- ,ESTABLISHMENT_ID
2645 ,CURRENCY_CONVERSION_DATE
2646 ,CURRENCY_CONVERSION_TYPE
2647 ,CURRENCY_CONVERSION_RATE
2648 -- ,SUMMARIZATION_TEMPLATE_ID
2649 ,TAXABLE_BASIS_FORMULA
2650 ,TAX_CALCULATION_FORMULA
2651 ,HISTORICAL_FLAG
2652 ,CANCEL_FLAG
2653 ,DELETE_FLAG
2654 ,TAX_AMT_INCLUDED_FLAG
2655 ,COMPOUNDING_TAX_FLAG
2656 ,SELF_ASSESSED_FLAG
2657 ,OVERRIDDEN_FLAG
2658 ,REPORTING_ONLY_FLAG
2659 ,ASSOCIATED_CHILD_FROZEN_FLAG
2660 ,COPIED_FROM_OTHER_DOC_FLAG
2661 ,MANUALLY_ENTERED_FLAG
2662 ,LAST_MANUAL_ENTRY --BUG7146063
2663 ,RECORD_TYPE_CODE
2664 -- ,TAX_PROVIDER_ID
2665 ,TAX_ONLY_LINE_FLAG
2666 ,CREATED_BY
2667 ,CREATION_DATE
2668 ,LAST_UPDATED_BY
2669 ,LAST_UPDATE_DATE
2670 ,LAST_UPDATE_LOGIN
2671 ,ATTRIBUTE_CATEGORY
2672 ,ATTRIBUTE1
2673 ,ATTRIBUTE2
2674 ,ATTRIBUTE3
2675 ,ATTRIBUTE4
2676 ,ATTRIBUTE5
2677 ,ATTRIBUTE6
2678 ,ATTRIBUTE7
2679 ,ATTRIBUTE8
2680 ,ATTRIBUTE9
2681 ,ATTRIBUTE10
2682 ,ATTRIBUTE11
2683 ,ATTRIBUTE12
2684 ,ATTRIBUTE13
2685 ,ATTRIBUTE14
2686 ,ATTRIBUTE15
2687 ,APPLIED_FROM_LINE_ID
2688 ,APPLIED_TO_APPLICATION_ID
2689 ,APPLIED_TO_EVENT_CLASS_CODE
2690 ,APPLIED_TO_ENTITY_CODE
2691 ,APPLIED_TO_TRX_ID
2692 ,APPLIED_TO_LINE_ID
2693 -- ,TAX_EXEMPTION_ID
2694 -- ,TAX_RATE_BEFORE_EXEMPTION
2695 -- ,TAX_RATE_NAME_BEFORE_EXEMPTION
2696 -- ,EXEMPT_RATE_MODIFIER
2697 -- ,EXEMPT_CERTIFICATE_NUMBER
2698 -- ,EXEMPT_REASON
2699 -- ,EXEMPT_REASON_CODE
2700 -- ,TAX_RATE_BEFORE_EXCEPTION
2701 -- ,TAX_RATE_NAME_BEFORE_EXCEPTION
2702 -- ,TAX_EXCEPTION_ID
2703 -- ,EXCEPTION_RATE
2704 ,TOTAL_REC_TAX_AMT_TAX_CURR
2705 ,TOTAL_NREC_TAX_AMT_TAX_CURR
2706 ,MRC_TAX_LINE_FLAG
2707 ,GLOBAL_ATTRIBUTE_CATEGORY
2708 ,GLOBAL_ATTRIBUTE1
2709 ,GLOBAL_ATTRIBUTE2
2710 ,GLOBAL_ATTRIBUTE3
2711 ,GLOBAL_ATTRIBUTE4
2712 ,GLOBAL_ATTRIBUTE5
2713 ,GLOBAL_ATTRIBUTE6
2714 ,GLOBAL_ATTRIBUTE7
2715 ,GLOBAL_ATTRIBUTE8
2716 ,GLOBAL_ATTRIBUTE9
2717 ,GLOBAL_ATTRIBUTE10
2718 ,GLOBAL_ATTRIBUTE11
2719 ,GLOBAL_ATTRIBUTE12
2720 ,GLOBAL_ATTRIBUTE13
2721 ,GLOBAL_ATTRIBUTE14
2722 ,GLOBAL_ATTRIBUTE15
2723 ,GLOBAL_ATTRIBUTE16
2724 ,GLOBAL_ATTRIBUTE17
2725 ,GLOBAL_ATTRIBUTE18
2726 ,GLOBAL_ATTRIBUTE19
2727 ,GLOBAL_ATTRIBUTE20
2728 ,APPLIED_FROM_TRX_LEVEL_TYPE
2729 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
2730 ,APPLIED_TO_TRX_LEVEL_TYPE
2731 ,TRX_LEVEL_TYPE
2732 ,ADJUST_TAX_AMT_FLAG
2733 ,OBJECT_VERSION_NUMBER)
2734 VALUES(
2735 SUMMARY_TAX_LINE_ID
2736 ,INTERNAL_ORGANIZATION_ID
2737 ,APPLICATION_ID
2738 ,ENTITY_CODE
2739 ,EVENT_CLASS_CODE
2740 ,TRX_ID
2741 ,TRX_NUMBER
2742 ,APPLIED_FROM_APPLICATION_ID
2743 ,APPLIED_FROM_EVENT_CLASS_CODE
2744 ,APPLIED_FROM_ENTITY_CODE
2745 ,APPLIED_FROM_TRX_ID
2746 ,ADJUSTED_DOC_APPLICATION_ID
2747 ,ADJUSTED_DOC_ENTITY_CODE
2748 ,ADJUSTED_DOC_EVENT_CLASS_CODE
2749 ,ADJUSTED_DOC_TRX_ID
2750 ,SUMMARY_TAX_LINE_NUMBER
2751 ,CONTENT_OWNER_ID
2752 ,TAX_REGIME_CODE
2753 ,TAX
2754 ,TAX_STATUS_CODE
2755 ,TAX_RATE_ID
2756 ,TAX_RATE_CODE
2757 ,TAX_RATE
2758 ,TAX_AMT
2759 ,TAX_AMT_TAX_CURR
2760 ,TAX_AMT_FUNCL_CURR
2761 ,TAX_JURISDICTION_CODE
2762 ,TOTAL_REC_TAX_AMT
2763 ,TOTAL_REC_TAX_AMT_FUNCL_CURR
2764 ,TOTAL_NREC_TAX_AMT
2765 ,TOTAL_NREC_TAX_AMT_FUNCL_CURR
2766 ,LEDGER_ID
2767 ,LEGAL_ENTITY_ID
2768 -- ,ESTABLISHMENT_ID
2769 ,CURRENCY_CONVERSION_DATE
2770 ,CURRENCY_CONVERSION_TYPE
2771 ,CURRENCY_CONVERSION_RATE
2772 -- ,NULL -- SUMMARIZATION_TEMPLATE_ID
2773 -- Bug 9037817 'STANDARD_TB'
2774 ,TAXABLE_BASIS_FORMULA
2775 ,'STANDARD_TC' -- TAX_CALCULATION_FORMULA
2776 ,HISTORICAL_FLAG
2777 ,CANCEL_FLAG
2778 ,'N' -- DELETE_FLAG
2779 ,TAX_AMT_INCLUDED_FLAG
2780 ,COMPOUNDING_TAX_FLAG
2781 ,SELF_ASSESSED_FLAG
2782 ,OVERRIDDEN_FLAG
2783 ,'N' -- REPORTING_ONLY_FLAG
2784 ,'N' -- ASSOCIATED_CHILD_FROZEN_FLAG
2785 ,'N' -- COPIED_FROM_OTHER_DOC_FLAG
2786 ,MANUALLY_ENTERED_FLAG --BUG7146063
2787 ,LAST_MANUAL_ENTRY --BUG7146063
2788 ,RECORD_TYPE_CODE
2789 -- ,NULL -- TAX_PROVIDER_ID
2790 ,TAX_ONLY_LINE_FLAG
2791 ,CREATED_BY
2792 ,CREATION_DATE
2793 ,LAST_UPDATED_BY
2794 ,LAST_UPDATE_DATE
2795 ,LAST_UPDATE_LOGIN
2796 ,ATTRIBUTE_CATEGORY
2797 ,ATTRIBUTE1
2798 ,ATTRIBUTE2
2799 ,ATTRIBUTE3
2800 ,ATTRIBUTE4
2801 ,ATTRIBUTE5
2802 ,ATTRIBUTE6
2803 ,ATTRIBUTE7
2804 ,ATTRIBUTE8
2805 ,ATTRIBUTE9
2806 ,ATTRIBUTE10
2807 ,ATTRIBUTE11
2808 ,ATTRIBUTE12
2809 ,ATTRIBUTE13
2810 ,ATTRIBUTE14
2811 ,ATTRIBUTE15
2812 ,APPLIED_FROM_LINE_ID
2813 ,APPLIED_TO_APPLICATION_ID
2814 ,APPLIED_TO_EVENT_CLASS_CODE
2815 ,APPLIED_TO_ENTITY_CODE
2816 ,APPLIED_TO_TRX_ID
2817 ,APPLIED_TO_TRX_LINE_ID -- APPLIED_TO_LINE_ID
2818 -- ,NULL -- TAX_EXEMPTION_ID
2819 -- ,NULL -- TAX_RATE_BEFORE_EXEMPTION
2820 -- ,NULL -- TAX_RATE_NAME_BEFORE_EXEMPTION
2821 -- ,NULL -- EXEMPT_RATE_MODIFIER
2822 -- ,NULL -- EXEMPT_CERTIFICATE_NUMBER
2823 -- ,NULL -- EXEMPT_REASON
2824 -- ,NULL -- EXEMPT_REASON_CODE
2825 -- ,NULL -- TAX_RATE_BEFORE_EXCEPTION
2826 -- ,NULL -- TAX_RATE_NAME_BEFORE_EXCEPTION
2827 -- ,NULL -- TAX_EXCEPTION_ID
2828 -- ,NULL -- EXCEPTION_RATE
2829 ,TOTAL_REC_TAX_AMT_FUNCL_CURR
2830 ,TOTAL_NREC_TAX_AMT_FUNCL_CURR
2831 ,'N' -- MRC_TAX_LINE_FLAG
2832 ,GLOBAL_ATTRIBUTE_CATEGORY
2833 ,GLOBAL_ATTRIBUTE1
2834 ,GLOBAL_ATTRIBUTE2
2835 ,GLOBAL_ATTRIBUTE3
2836 ,GLOBAL_ATTRIBUTE4
2837 ,GLOBAL_ATTRIBUTE5
2838 ,GLOBAL_ATTRIBUTE6
2839 ,GLOBAL_ATTRIBUTE7
2840 ,GLOBAL_ATTRIBUTE8
2841 ,GLOBAL_ATTRIBUTE9
2842 ,GLOBAL_ATTRIBUTE10
2843 ,GLOBAL_ATTRIBUTE11
2844 ,GLOBAL_ATTRIBUTE12
2845 ,GLOBAL_ATTRIBUTE13
2846 ,GLOBAL_ATTRIBUTE14
2847 ,GLOBAL_ATTRIBUTE15
2848 ,GLOBAL_ATTRIBUTE16
2849 ,GLOBAL_ATTRIBUTE17
2850 ,GLOBAL_ATTRIBUTE18
2851 ,GLOBAL_ATTRIBUTE19
2852 ,GLOBAL_ATTRIBUTE20
2853 ,APPLIED_FROM_TRX_LEVEL_TYPE
2854 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
2855 ,APPLIED_TO_TRX_LEVEL_TYPE
2856 ,TRX_LEVEL_TYPE
2857 ,NULL -- ADJUST_TAX_AMT_FLAG
2858 ,OBJECT_VERSION_NUMBER
2859 )
2860 (SELECT /*+ ROWID(inv) ORDERED use_nl(fnd_curr,fds,lines,poll,ptp)*/
2861 NULL EVENT_ID
2862 ,1 OBJECT_VERSION_NUMBER
2863 ,NVL(lines.org_id,-99) INTERNAL_ORGANIZATION_ID
2864 ,200 APPLICATION_ID
2865 ,'AP_INVOICES' ENTITY_CODE
2866 ,DECODE(inv.INVOICE_TYPE_LOOKUP_CODE,
2867 'STANDARD', 'STANDARD INVOICES',
2868 'CREDIT' , 'STANDARD INVOICES', --Bug 6489409
2869 'DEBIT' , 'STANDARD INVOICES', --Bug 6489409
2870 'MIXED' , 'STANDARD INVOICES', --Bug 6489409
2871 'ADJUSTMENT','STANDARD INVOICES', --Bug 6489409
2872 'PO PRICE ADJUST','STANDARD INVOICES', --Bug 6489409
2873 'INVOICE REQUEST','STANDARD INVOICES', --Bug 6489409
2874 'CREDIT MEMO REQUEST','STANDARD INVOICES',--Bug 6489409
2875 'RETAINAGE RELEASE' ,'STANDARD INVOICES',--Bug 6489409
2876 'PREPAYMENT', 'PREPAYMENT INVOICES',
2877 'EXPENSE REPORT', 'EXPENSE REPORTS',
2878 'INTEREST INVOICE', 'INTEREST INVOICES','NA') EVENT_CLASS_CODE
2879 ,DECODE(inv.INVOICE_TYPE_LOOKUP_CODE, 'STANDARD', 1,
2880 'PREPAYMENT', 7, 'EXPENSE REPORT', 2, NULL) EVENT_CLASS_MAPPING_ID
2881 ,DECODE(inv.INVOICE_TYPE_LOOKUP_CODE,
2882 'STANDARD','STANDARD INVOICE CREATED',
2883 'PREPAYMENT','PREPAYMENT INVOICE CREATED',
2884 'EXPENSE REPORT','EXPENSE REPORT CREATED',
2885 'INTEREST INVOICE','INTEREST INVOICE CREATED','NA') EVENT_TYPE_CODE
2886 ,(CASE
2887 WHEN inv.invoice_type_lookup_code in
2888 ('ADJUSTMENT','CREDIT','DEBIT','INTEREST',
2889 'MIXED','QUICKDEFAULT','PO PRICE ADJUST',
2890 'QUICKMATCH','STANDARD','AWT')
2891 THEN 'PURCHASE_TRANSACTION'
2892 WHEN inv.invoice_type_lookup_code = 'PREPAYMENT'
2893 THEN 'PURCHASE_PREPAYMENTTRANSACTION'
2894 WHEN inv.invoice_type_lookup_code='EXPENSE REPORT'
2895 THEN 'EXPENSE_REPORT'
2896 ELSE NULL
2897 END) TAX_EVENT_CLASS_CODE
2898 ,'VALIDATE' TAX_EVENT_TYPE_CODE
2899 -- ,NULL DOC_EVENT_STATUS
2900 ,'CREATE' LINE_LEVEL_ACTION
2901 ,DECODE(lines.po_line_location_id,
2902 NULL, DECODE(lines.line_type_lookup_code,
2903 'PREPAY', 'PREPAY_APPLICATION',
2904 DECODE(inv.invoice_type_lookup_code,
2905 'STANDARD', 'STANDARD INVOICES',
2906 'CREDIT','AP_CREDIT_MEMO',
2907 'CREDIT MEMO REQUEST', 'AP_CREDIT_MEMO',
2908 'DEBIT','AP_DEBIT_MEMO',
2909 'PREPAYMENT','PREPAYMENT INVOICES',
2910 'EXPENSE REPORT','EXPENSE REPORTS',
2911 'STANDARD INVOICES'
2912 )
2913 ),
2914 DECODE(poll.shipment_type,
2915 'PREPAYMENT', DECODE(poll.payment_type,
2916 'ADVANCE', 'ADVANCE',
2917 'MILESTONE', 'FINANCING',
2918 'RATE', 'FINANCING',
2919 'LUMPSUM', 'FINANCING',
2920 DECODE(poll.matching_basis,
2921 'AMOUNT','AMOUNT_MATCHED',
2922 'STANDARD INVOICES')
2923 ),
2924 DECODE(poll.matching_basis,
2925 'AMOUNT','AMOUNT_MATCHED',
2926 'STANDARD INVOICES')
2927 )
2928 ) LINE_CLASS
2929 -- ,NULL APPLICATION_DOC_STATUS
2930 ,lines.line_type_lookup_code AP_LINE_LOOKUP_CODE
2931 ,lines.invoice_id TRX_ID
2932 ,NVL(inv.invoice_date,sysdate) TRX_DATE
2933 ,lines.set_of_books_id LEDGER_ID
2934 ,inv.invoice_currency_code TRX_CURRENCY_CODE
2935 ,NVL(inv.legal_entity_id, -99) LEGAL_ENTITY_ID
2936 -- ,NULL ESTABLISHMENT_ID
2937 ,inv.taxation_country DEFAULT_TAXATION_COUNTRY
2938 ,inv.invoice_num TRX_NUMBER
2939 ,lines.description TRX_LINE_DESCRIPTION
2940 ,inv.description TRX_DESCRIPTION
2941 ,inv.invoice_received_date TRX_COMMUNICATED_DATE
2942 ,NVL(lines.accounting_date,sysdate) TRX_LINE_GL_DATE
2943 ,inv.batch_id BATCH_SOURCE_ID
2944 -- ,NULL BATCH_SOURCE_NAME
2945 ,inv.doc_sequence_id DOC_SEQ_ID
2946 ,fds.name DOC_SEQ_NAME
2947 ,inv.doc_sequence_value DOC_SEQ_VALUE
2948 ,inv.terms_date TRX_DUE_DATE
2949 -- ,NULL TRX_TYPE_DESCRIPTION
2950 ,lines.line_type_lookup_code TRX_LINE_TYPE
2951 ,lines.accounting_date TRX_LINE_DATE
2952 -- ,NULL TRX_SHIPPING_DATE
2953 -- ,NULL TRX_RECEIPT_DATE
2954 ,NVL(lines.amount,0) LINE_AMT
2955 ,lines.quantity_invoiced TRX_LINE_QUANTITY
2956 ,lines.unit_price -- UNIT_PRICE
2957 ,lines.inventory_item_id PRODUCT_ID
2958 -- ,NULL PRODUCT_ORG_ID
2959 ,lines.unit_meas_lookup_code UOM_CODE
2960 ,lines.product_type -- PRODUCT_TYPE
2961 -- ,NULL PRODUCT_CODE
2962 ,lines.item_description PRODUCT_DESCRIPTION
2963 ,ptp.party_tax_profile_id FIRST_PTY_ORG_ID
2964 -- ,NULL ASSET_NUMBER
2965 -- ,NULL ASSET_ACCUM_DEPRECIATION
2966 -- ,NULL ASSET_TYPE
2967 -- ,NULL ASSET_COST
2968 -- ,NULL RELATED_DOC_APPLICATION_ID,
2969 -- ,NULL RELATED_DOC_ENTITY_CODE
2970 -- ,NULL RELATED_DOC_EVENT_CLASS_CODE
2971 -- ,NULL RELATED_DOC_TRX_ID
2972 -- ,NULL RELATED_DOC_NUMBER
2973 -- ,NULL RELATED_DOC_DATE
2974 ,DECODE(lines.prepay_invoice_id, NULL, NULL, 200) APPLIED_FROM_APPLICATION_ID
2975 ,DECODE(lines.prepay_invoice_id, NULL, NULL,
2976 'AP_INVOICES') APPLIED_FROM_ENTITY_CODE
2977 ,DECODE(lines.prepay_invoice_id, NULL, NULL,
2978 'PREPAYMENT INVOICES') APPLIED_FROM_EVENT_CLASS_CODE
2979 ,lines.prepay_invoice_id APPLIED_FROM_TRX_ID
2980 ,lines.prepay_line_number APPLIED_FROM_LINE_ID
2981 ,DECODE(lines.corrected_inv_id, NULL, NULL, 200) ADJUSTED_DOC_APPLICATION_ID
2982 ,DECODE(lines.corrected_inv_id, NULL, NULL,
2983 'AP_INVOICES') ADJUSTED_DOC_ENTITY_CODE
2984 ,DECODE(lines.corrected_inv_id, NULL, NULL,
2985 'STANDARD INVOICES') ADJUSTED_DOC_EVENT_CLASS_CODE
2986 ,lines.corrected_inv_id ADJUSTED_DOC_TRX_ID
2987 ,lines.corrected_line_number ADJUSTED_DOC_LINE_ID
2988 -- ,NULL ADJUSTED_DOC_NUMBER
2989 -- ,NULL ADJUSTED_DOC_DATE
2990 ,DECODE(lines.rcv_transaction_id, NULL, NULL, 707) APPLIED_TO_APPLICATION_ID
2991 ,DECODE(lines.rcv_transaction_id, NULL, NULL,
2992 'RCV_ACCOUNTING_EVENTS') APPLIED_TO_ENTITY_CODE
2993 ,DECODE(lines.rcv_transaction_id, NULL, NULL,
2994 'RCPT_REC_INSP') APPLIED_TO_EVENT_CLASS_CODE
2995 ,lines.rcv_transaction_id APPLIED_TO_TRX_ID
2996 ,lines.rcv_shipment_line_id APPLIED_TO_TRX_LINE_ID
2997 -- ,NULL APPLIED_TO_TRX_NUMBER
2998 ,DECODE(NVL(lines.po_release_id, lines.po_header_id),
2999 NULL, NULL, 'SHIPMENT') REF_DOC_TRX_LEVEL_TYPE
3000 ,NVL(lines.po_release_id, lines.po_header_id) REF_DOC_TRX_ID
3001 ,lines.po_line_location_id REF_DOC_LINE_ID
3002 -- ,NULL REF_DOC_LINE_QUANTITY
3003 ,DECODE(lines.rcv_transaction_id, NULL, NULL,
3004 'LINE') APPLIED_TO_TRX_LEVEL_TYPE
3005 ,DECODE(lines.prepay_invoice_id, NULL, NULL,
3006 'LINE') APPLIED_FROM_TRX_LEVEL_TYPE
3007 ,DECODE(lines.corrected_inv_id, NULL, NULL,
3008 'LINE') ADJUSTED_DOC_TRX_LEVEL_TYPE
3009 ,lines.merchant_name MERCHANT_PARTY_NAME
3010 ,lines.merchant_document_number MERCHANT_PARTY_DOCUMENT_NUMBER
3011 ,lines.merchant_reference MERCHANT_PARTY_REFERENCE
3012 ,lines.merchant_taxpayer_id MERCHANT_PARTY_TAXPAYER_ID
3013 ,lines.merchant_tax_reg_number MERCHANT_PARTY_TAX_REG_NUMBER
3014 -- ,NULL MERCHANT_PARTY_ID
3015 ,lines.country_of_supply MERCHANT_PARTY_COUNTRY
3016 ,lines.start_expense_date -- START_EXPENSE_DATE
3017 ,lines.ship_to_location_id -- SHIP_TO_LOCATION_ID
3018 -- ,NULL SHIP_FROM_LOCATION_ID
3019 -- ,NULL BILL_TO_LOCATION_ID
3020 -- ,NULL BILL_FROM_LOCATION_ID
3021 -- ,NULL SHIP_TO_PARTY_TAX_PROF_ID
3022 -- ,NULL SHIP_FROM_PARTY_TAX_PROF_ID
3023 -- ,NULL BILL_TO_PARTY_TAX_PROF_ID
3024 -- ,NULL BILL_FROM_PARTY_TAX_PROF_ID
3025 -- ,NULL SHIP_TO_SITE_TAX_PROF_ID
3026 -- ,NULL SHIP_FROM_SITE_TAX_PROF_ID
3027 -- ,NULL BILL_TO_SITE_TAX_PROF_ID
3028 -- ,NULL BILL_FROM_SITE_TAX_PROF_ID
3029 -- ,NULL MERCHANT_PARTY_TAX_PROF_ID
3030 -- ,NULL HQ_ESTB_PARTY_TAX_PROF_ID
3031 -- ,NULL CTRL_TOTAL_LINE_TX_AMT
3032 -- ,NULL CTRL_TOTAL_HDR_TX_AMT
3033 -- ,NULL INPUT_TAX_CLASSIFICATION_CODE
3034 -- ,NULL OUTPUT_TAX_CLASSIFICATION_CODE
3035 -- ,NULL INTERNAL_ORG_LOCATION_ID
3036 ,'MIGRATED' RECORD_TYPE_CODE
3037 ,lines.product_fisc_classification -- PRODUCT_FISC_CLASSIFICATION
3038 ,lines.product_category -- PRODUCT_CATEGORY
3039 ,lines.user_defined_fisc_class -- USER_DEFINED_FISC_CLASS
3040 ,lines.assessable_value -- ASSESSABLE_VALUE
3041 ,lines.trx_business_category -- TRX_BUSINESS_CATEGORY
3042 ,inv.supplier_tax_invoice_number -- SUPPLIER_TAX_INVOICE_NUMBER
3043 ,inv.supplier_tax_invoice_date -- SUPPLIER_TAX_INVOICE_DATE
3044 ,inv.supplier_tax_exchange_rate SUPPLIER_EXCHANGE_RATE
3045 ,inv.tax_invoice_recording_date TAX_INVOICE_DATE
3046 ,inv.tax_invoice_internal_seq TAX_INVOICE_NUMBER
3047 ,inv.document_sub_type -- DOCUMENT_SUB_TYPE
3048 ,lines.primary_intended_use LINE_INTENDED_USE
3049 ,inv.port_of_entry_code -- PORT_OF_ENTRY_CODE
3050 -- ,NULL SOURCE_APPLICATION_ID
3051 -- ,NULL SOURCE_ENTITY_CODE
3052 -- ,NULL SOURCE_EVENT_CLASS_CODE
3053 -- ,NULL SOURCE_TRX_ID,
3054 -- ,NULL SOURCE_LINE_ID,
3055 -- ,NULL SOURCE_TRX_LEVEL_TYPE
3056 ,'N' LINE_AMT_INCLUDES_TAX_FLAG
3057 ,'N' CTRL_HDR_TX_APPL_FLAG
3058 ,'Y' TAX_REPORTING_FLAG
3059 ,'N' TAX_AMT_INCLUDED_FLAG
3060 ,'N' COMPOUNDING_TAX_FLAG
3061 ,'N' INCLUSIVE_TAX_OVERRIDE_FLAG
3062 ,'N' THRESHOLD_INDICATOR_FLAG
3063 ,'N' USER_UPD_DET_FACTORS_FLAG
3064 ,'N' TAX_PROCESSING_COMPLETED_FLAG
3065 ,lines.assets_tracking_flag ASSET_FLAG
3066 ,ptp.party_tax_profile_id CONTENT_OWNER_ID
3067 ,inv.exchange_date CURRENCY_CONVERSION_DATE
3068 ,inv.exchange_rate CURRENCY_CONVERSION_RATE
3069 ,inv.exchange_rate_type CURRENCY_CONVERSION_TYPE
3070 ,DECODE(lines.line_source,'MANUAL LINE ENTRY','PRORATED_TB','STANDARD_TB') TAXABLE_BASIS_FORMULA --Bug 9037817
3071 ,fnd_curr.minimum_accountable_unit MINIMUM_ACCOUNTABLE_UNIT
3072 ,NVL(fnd_curr.precision,0) PRECISION
3073 ,DECODE(NVL(lines.po_release_id, lines.po_header_id),
3074 NULL, NULL, 201) REF_DOC_APPLICATION_ID
3075 ,DECODE(lines.po_release_id, NULL,
3076 DECODE(lines.po_header_id, NULL, NULL,
3077 'PURCHASE_ORDER'), 'RELEASE') REF_DOC_ENTITY_CODE
3078 ,DECODE(lines.po_release_id, NULL,
3079 DECODE(lines.po_header_id, NULL, NULL,
3080 'PO_PA'), 'RELEASE') REF_DOC_EVENT_CLASS_CODE
3081 ,lines.SUMMARY_TAX_LINE_ID SUMMARY_TAX_LINE_ID
3082 ,lines.TAX TAX
3083 ,DECODE(lines.line_type_lookup_code, 'TAX',
3084 RANK() OVER (PARTITION BY inv.invoice_id,
3085 lines.line_type_lookup_code
3086 ORDER BY lines.line_number), NULL) SUMMARY_TAX_LINE_NUMBER
3087 ,lines.tax_rate -- TAX_RATE
3088 ,lines.tax_rate_code -- TAX_RATE_CODE
3089 ,lines.tax_rate_id -- TAX_RATE_ID
3090 ,lines.tax_regime_code -- TAX_REGIME_CODE
3091 ,lines.tax_status_code -- TAX_STATUS_CODE
3092 ,lines.tax_jurisdiction_code -- TAX_JURISDICTION_CODE
3093 ,'LINE' TRX_LEVEL_TYPE
3094 ,lines.line_number TRX_LINE_ID
3095 ,lines.line_number TRX_LINE_NUMBER
3096 ,lines.default_dist_ccid ACCOUNT_CCID
3097 -- ,NULL ACCOUNT_STRING
3098 ,lines.amount TAX_AMT
3099 ,lines.base_amount TAX_AMT_TAX_CURR
3100 ,lines.base_amount TAX_AMT_FUNCL_CURR
3101 ,lines.attribute_category -- ATTRIBUTE_CATEGORY
3102 ,lines.attribute1 -- ATTRIBUTE1
3103 ,lines.attribute2 -- ATTRIBUTE2
3104 ,lines.attribute3 -- ATTRIBUTE3
3105 ,lines.attribute4 -- ATTRIBUTE4
3106 ,lines.attribute5 -- ATTRIBUTE5
3107 ,lines.attribute6 -- ATTRIBUTE6
3108 ,lines.attribute7 -- ATTRIBUTE7
3109 ,lines.attribute8 -- ATTRIBUTE8
3110 ,lines.attribute9 -- ATTRIBUTE9
3111 ,lines.attribute10 -- ATTRIBUTE10
3112 ,lines.attribute11 -- ATTRIBUTE11
3113 ,lines.attribute12 -- ATTRIBUTE12
3114 ,lines.attribute13 -- ATTRIBUTE13
3115 ,lines.attribute14 -- ATTRIBUTE14
3116 ,lines.attribute15 -- ATTRIBUTE15
3117 ,lines.global_attribute_category -- GLOBAL_ATTRIBUTE_CATEGORY
3118 ,lines.global_attribute1 -- GLOBAL_ATTRIBUTE1
3119 ,lines.global_attribute2 -- GLOBAL_ATTRIBUTE2
3120 ,lines.global_attribute3 -- GLOBAL_ATTRIBUTE3
3121 ,lines.global_attribute4 -- GLOBAL_ATTRIBUTE4
3122 ,lines.global_attribute5 -- GLOBAL_ATTRIBUTE5
3123 ,lines.global_attribute6 -- GLOBAL_ATTRIBUTE6
3124 ,lines.global_attribute7 -- GLOBAL_ATTRIBUTE7
3125 ,lines.global_attribute8 -- GLOBAL_ATTRIBUTE8
3126 ,lines.global_attribute9 -- GLOBAL_ATTRIBUTE9
3127 ,lines.global_attribute10 -- GLOBAL_ATTRIBUTE10
3128 ,lines.global_attribute11 -- GLOBAL_ATTRIBUTE11
3129 ,lines.global_attribute12 -- GLOBAL_ATTRIBUTE12
3130 ,lines.global_attribute13 -- GLOBAL_ATTRIBUTE13
3131 ,lines.global_attribute14 -- GLOBAL_ATTRIBUTE14
3132 ,lines.global_attribute15 -- GLOBAL_ATTRIBUTE15
3133 ,lines.global_attribute16 -- GLOBAL_ATTRIBUTE16
3134 ,lines.global_attribute17 -- GLOBAL_ATTRIBUTE17
3135 ,lines.global_attribute18 -- GLOBAL_ATTRIBUTE18
3136 ,lines.global_attribute19 -- GLOBAL_ATTRIBUTE19
3137 ,lines.global_attribute20 -- GLOBAL_ATTRIBUTE20
3138 ,'Y' HISTORICAL_FLAG
3139 ,'N' OVERRIDDEN_FLAG
3140 ,'N' SELF_ASSESSED_FLAG
3141 ,1 CREATED_BY
3142 ,SYSDATE CREATION_DATE
3143 ,SYSDATE LAST_UPDATE_DATE
3144 ,1 LAST_UPDATE_LOGIN
3145 ,1 LAST_UPDATED_BY
3146 -- ,NULL LAST_MANUAL_ENTRY
3147 ,CASE
3148 WHEN lines.line_type_lookup_code <> 'TAX'
3149 THEN NULL
3150 WHEN NOT EXISTS -- Tax Lines
3151 (SELECT 1
3152 FROM AP_INV_DISTS_TARGET dists
3153 WHERE dists.invoice_id = lines.invoice_id
3154 AND dists.invoice_line_number = lines.line_number
3155 AND dists.charge_applicable_to_dist_id IS NOT NULL
3156 )
3157 THEN 'Y'
3158 ELSE 'N'
3159 END TAX_ONLY_LINE_FLAG
3160 ,lines.total_rec_tax_amount TOTAL_REC_TAX_AMT
3161 ,lines.total_nrec_tax_amount TOTAL_NREC_TAX_AMT
3162 ,lines.total_rec_tax_amt_funcl_curr -- TOTAL_REC_TAX_AMT_FUNCL_CURR,
3163 ,lines.total_nrec_tax_amt_funcl_curr -- TOTAL_NREC_TAX_AMT_FUNCL_CURR,
3164 ,inv.vendor_id SHIP_THIRD_PTY_ACCT_ID
3165 ,inv.vendor_site_id SHIP_THIRD_PTY_ACCT_SITE_ID
3166 ,inv.vendor_id BILL_THIRD_PTY_ACCT_ID
3167 ,inv.vendor_site_id BILL_THIRD_PTY_ACCT_SITE_ID
3168 -- ,NULL ICX_SESSION_ID
3169 -- ,NULL TRX_LINE_CURRENCY_CODE
3170 -- ,NULL TRX_LINE_CURRENCY_CONV_RATE
3171 -- ,NULL TRX_LINE_CURRENCY_CONV_DATE
3172 -- ,NULL TRX_LINE_PRECISION
3173 -- ,NULL TRX_LINE_MAU
3174 -- ,NULL TRX_LINE_CURRENCY_CONV_TYPE
3175 -- ,NULL INTERFACE_ENTITY_CODE
3176 -- ,NULL INTERFACE_LINE_ID
3177 -- ,NULL SOURCE_TAX_LINE_ID
3178 ,DECODE(lines.discarded_flag, 'Y', 'Y', 'N') CANCEL_FLAG
3179 ,DECODE(lines.line_source,'MANUAL LINE ENTRY','Y','N') MANUALLY_ENTERED_FLAG --BUG7146063
3180 ,DECODE(lines.line_source,'MANUAL LINE ENTRY','TAX_AMOUNT',NULL) LAST_MANUAL_ENTRY --BUG7146063
3181 FROM ( select distinct other_doc_application_id,other_doc_trx_id from ZX_VALIDATION_ERRORS_GT ) zxvalerr, --Bug 5187701
3182 ap_invoices_all inv,
3183 fnd_currencies fnd_curr,
3184 fnd_document_sequences fds,
3185 ap_invoice_lines_all lines,
3186 po_line_locations_all poll,
3187 zx_party_tax_profile ptp
3188 WHERE zxvalerr.other_doc_application_id = 200
3189 AND inv.invoice_id = zxvalerr.other_doc_trx_id
3190 AND fnd_curr.currency_code = inv.invoice_currency_code
3191 AND inv.doc_sequence_id = fds.doc_sequence_id(+)
3192 AND lines.invoice_id = inv.invoice_id
3193 AND NVL(lines.historical_flag, 'N') = 'Y'
3194 AND poll.line_location_id(+) = lines.po_line_location_id
3195 AND ptp.party_type_code = 'OU'
3196 AND ptp.party_id = DECODE(l_multi_org_flag,'N',l_org_id,lines.org_id)
3197 );
3198
3199 IF (g_level_statement >= g_current_runtime_level ) THEN
3200 FND_LOG.STRING(g_level_statement,
3201 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_blk_ap',
3202 'Inserting data into zx_lines and zx_rec_nrec_dist');
3203 END IF;
3204
3205 -- Insert data into zx_lines and zx_rec_nrec_dist
3206 --
3207 INSERT ALL
3208 INTO ZX_REC_NREC_DIST(
3209 TAX_LINE_ID
3210 ,REC_NREC_TAX_DIST_ID
3211 ,REC_NREC_TAX_DIST_NUMBER
3212 ,APPLICATION_ID
3213 ,CONTENT_OWNER_ID
3214 ,CURRENCY_CONVERSION_DATE
3215 ,CURRENCY_CONVERSION_RATE
3216 ,CURRENCY_CONVERSION_TYPE
3217 ,ENTITY_CODE
3218 ,EVENT_CLASS_CODE
3219 ,EVENT_TYPE_CODE
3220 ,LEDGER_ID
3221 ,MINIMUM_ACCOUNTABLE_UNIT
3222 ,PRECISION
3223 ,RECORD_TYPE_CODE
3224 ,REF_DOC_APPLICATION_ID
3225 ,REF_DOC_ENTITY_CODE
3226 ,REF_DOC_EVENT_CLASS_CODE
3227 ,REF_DOC_LINE_ID
3228 ,REF_DOC_TRX_ID
3229 ,REF_DOC_TRX_LEVEL_TYPE
3230 ,SUMMARY_TAX_LINE_ID
3231 ,TAX
3232 ,TAX_APPORTIONMENT_LINE_NUMBER
3233 ,TAX_CURRENCY_CODE
3234 ,TAX_CURRENCY_CONVERSION_DATE
3235 ,TAX_CURRENCY_CONVERSION_RATE
3236 ,TAX_CURRENCY_CONVERSION_TYPE
3237 ,TAX_EVENT_CLASS_CODE
3238 ,TAX_EVENT_TYPE_CODE
3239 ,TAX_ID
3240 ,TAX_LINE_NUMBER
3241 ,TAX_RATE
3242 ,TAX_RATE_CODE
3243 ,TAX_RATE_ID
3244 ,TAX_REGIME_CODE
3245 ,TAX_REGIME_ID
3246 ,TAX_STATUS_CODE
3247 ,TAX_STATUS_ID
3248 ,TRX_CURRENCY_CODE
3249 ,TRX_ID
3250 ,TRX_LEVEL_TYPE
3251 ,TRX_LINE_ID
3252 ,TRX_LINE_NUMBER
3253 ,TRX_NUMBER
3254 ,UNIT_PRICE
3255 ,ACCOUNT_CCID
3256 -- ,ACCOUNT_STRING
3257 -- ,ADJUSTED_DOC_TAX_DIST_ID
3258 -- ,APPLIED_FROM_TAX_DIST_ID
3259 -- ,APPLIED_TO_DOC_CURR_CONV_RATE
3260 ,AWARD_ID
3261 ,EXPENDITURE_ITEM_DATE
3262 ,EXPENDITURE_ORGANIZATION_ID
3263 ,EXPENDITURE_TYPE
3264 ,FUNC_CURR_ROUNDING_ADJUSTMENT
3265 ,GL_DATE
3266 ,INTENDED_USE
3267 ,ITEM_DIST_NUMBER
3268 -- ,MRC_LINK_TO_TAX_DIST_ID
3269 -- ,ORIG_REC_NREC_RATE
3270 -- ,ORIG_REC_NREC_TAX_AMT
3271 -- ,ORIG_REC_NREC_TAX_AMT_TAX_CURR
3272 -- ,ORIG_REC_RATE_CODE
3273 -- ,PER_TRX_CURR_UNIT_NR_AMT
3274 -- ,PER_UNIT_NREC_TAX_AMT
3275 -- ,PRD_TAX_AMT
3276 -- ,PRICE_DIFF
3277 ,PROJECT_ID
3278 -- ,QTY_DIFF
3279 -- ,RATE_TAX_FACTOR
3280 ,REC_NREC_RATE
3281 ,REC_NREC_TAX_AMT
3282 ,REC_NREC_TAX_AMT_FUNCL_CURR
3283 ,REC_NREC_TAX_AMT_TAX_CURR
3284 ,RECOVERY_RATE_CODE
3285 ,RECOVERY_TYPE_CODE
3286 -- ,RECOVERY_TYPE_ID
3287 -- ,REF_DOC_CURR_CONV_RATE
3288 ,REF_DOC_DIST_ID
3289 -- ,REF_DOC_PER_UNIT_NREC_TAX_AMT
3290 -- ,REF_DOC_TAX_DIST_ID
3291 -- ,REF_DOC_TRX_LINE_DIST_QTY
3292 -- ,REF_DOC_UNIT_PRICE
3293 -- ,REF_PER_TRX_CURR_UNIT_NR_AMT
3294 ,REVERSED_TAX_DIST_ID
3295 -- ,ROUNDING_RULE_CODE
3296 ,TASK_ID
3297 ,TAXABLE_AMT_FUNCL_CURR
3298 ,TAXABLE_AMT_TAX_CURR
3299 ,TRX_LINE_DIST_AMT
3300 ,TRX_LINE_DIST_ID
3301 ,TRX_LINE_DIST_QTY
3302 ,TRX_LINE_DIST_TAX_AMT
3303 -- ,UNROUNDED_REC_NREC_TAX_AMT
3304 -- ,UNROUNDED_TAXABLE_AMT
3305 ,TAXABLE_AMT
3306 ,ATTRIBUTE_CATEGORY
3307 ,ATTRIBUTE1
3308 ,ATTRIBUTE2
3309 ,ATTRIBUTE3
3310 ,ATTRIBUTE4
3311 ,ATTRIBUTE5
3312 ,ATTRIBUTE6
3313 ,ATTRIBUTE7
3314 ,ATTRIBUTE8
3315 ,ATTRIBUTE9
3316 ,ATTRIBUTE10
3317 ,ATTRIBUTE11
3318 ,ATTRIBUTE12
3319 ,ATTRIBUTE13
3320 ,ATTRIBUTE14
3321 ,ATTRIBUTE15
3322 ,GLOBAL_ATTRIBUTE_CATEGORY
3323 ,GLOBAL_ATTRIBUTE1
3324 ,GLOBAL_ATTRIBUTE2
3325 ,GLOBAL_ATTRIBUTE3
3326 ,GLOBAL_ATTRIBUTE4
3327 ,GLOBAL_ATTRIBUTE5
3328 ,GLOBAL_ATTRIBUTE6
3329 ,GLOBAL_ATTRIBUTE7
3330 ,GLOBAL_ATTRIBUTE8
3331 ,GLOBAL_ATTRIBUTE9
3332 ,GLOBAL_ATTRIBUTE10
3333 ,GLOBAL_ATTRIBUTE11
3334 ,GLOBAL_ATTRIBUTE12
3335 ,GLOBAL_ATTRIBUTE13
3336 ,GLOBAL_ATTRIBUTE14
3337 ,GLOBAL_ATTRIBUTE15
3338 ,GLOBAL_ATTRIBUTE16
3339 ,GLOBAL_ATTRIBUTE17
3340 ,GLOBAL_ATTRIBUTE18
3341 ,GLOBAL_ATTRIBUTE19
3342 ,GLOBAL_ATTRIBUTE20
3343 ,HISTORICAL_FLAG
3344 ,OVERRIDDEN_FLAG
3345 ,SELF_ASSESSED_FLAG
3346 ,TAX_APPORTIONMENT_FLAG
3347 ,TAX_ONLY_LINE_FLAG
3348 ,INCLUSIVE_FLAG
3349 ,MRC_TAX_DIST_FLAG
3350 ,REC_TYPE_RULE_FLAG
3351 ,NEW_REC_RATE_CODE_FLAG
3352 ,RECOVERABLE_FLAG
3353 ,REVERSE_FLAG
3354 ,REC_RATE_DET_RULE_FLAG
3355 ,BACKWARD_COMPATIBILITY_FLAG
3356 ,FREEZE_FLAG
3357 ,POSTING_FLAG
3358 ,LEGAL_ENTITY_ID
3359 ,CREATED_BY
3360 ,CREATION_DATE
3361 ,LAST_MANUAL_ENTRY
3362 ,LAST_UPDATE_DATE
3363 ,LAST_UPDATE_LOGIN
3364 ,LAST_UPDATED_BY
3365 ,OBJECT_VERSION_NUMBER
3366 ,ORIG_AP_CHRG_DIST_NUM
3367 ,ORIG_AP_CHRG_DIST_ID
3368 ,ORIG_AP_TAX_DIST_NUM
3369 ,ORIG_AP_TAX_DIST_ID
3370 ,INTERNAL_ORGANIZATION_ID
3371 ,DEF_REC_SETTLEMENT_OPTION_CODE
3372 --,TAX_JURISDICTION_ID
3373 ,ACCOUNT_SOURCE_TAX_RATE_ID
3374 ,RECOVERY_RATE_ID
3375 )
3376 VALUES(
3377 ZX_LINES_S.NEXTVAL
3378 ,REC_NREC_TAX_DIST_ID
3379 ,REC_NREC_TAX_DIST_NUMBER
3380 ,APPLICATION_ID
3381 ,CONTENT_OWNER_ID
3382 ,CURRENCY_CONVERSION_DATE
3383 ,CURRENCY_CONVERSION_RATE
3384 ,CURRENCY_CONVERSION_TYPE
3385 ,ENTITY_CODE
3386 ,EVENT_CLASS_CODE
3387 ,EVENT_TYPE_CODE
3388 ,AP_LEDGER_ID
3389 ,MINIMUM_ACCOUNTABLE_UNIT
3390 ,PRECISION
3391 ,RECORD_TYPE_CODE
3392 ,REF_DOC_APPLICATION_ID
3393 ,REF_DOC_ENTITY_CODE
3394 ,REF_DOC_EVENT_CLASS_CODE
3395 ,REF_DOC_LINE_ID
3396 ,REF_DOC_TRX_ID
3397 ,REF_DOC_TRX_LEVEL_TYPE
3398 ,SUMMARY_TAX_LINE_ID
3399 ,TAX
3400 ,TAX_APPORTIONMENT_LINE_NUMBER
3401 ,TAX_CURRENCY_CODE
3402 ,TAX_CURRENCY_CONVERSION_DATE
3403 ,TAX_CURRENCY_CONVERSION_RATE
3404 ,TAX_CURRENCY_CONVERSION_TYPE
3405 ,TAX_EVENT_CLASS_CODE
3406 ,TAX_EVENT_TYPE_CODE
3407 ,TAX_ID
3408 ,TAX_LINE_NUMBER
3409 ,TAX_RATE
3410 ,TAX_RATE_CODE
3411 ,TAX_RATE_ID
3412 ,TAX_REGIME_CODE
3413 ,TAX_REGIME_ID
3414 ,TAX_STATUS_CODE
3415 ,TAX_STATUS_ID
3416 ,TRX_CURRENCY_CODE
3417 ,TRX_ID
3418 ,TRX_LEVEL_TYPE
3419 ,TRX_LINE_ID
3420 ,TRX_LINE_NUMBER
3421 ,TRX_NUMBER
3422 ,UNIT_PRICE
3423 ,ACCOUNT_CCID
3424 -- ,ACCOUNT_STRING
3425 -- ,ADJUSTED_DOC_TAX_DIST_ID
3426 -- ,APPLIED_FROM_TAX_DIST_ID
3427 -- ,APPLIED_TO_DOC_CURR_CONV_RATE
3428 ,AWARD_ID
3429 ,EXPENDITURE_ITEM_DATE
3430 ,EXPENDITURE_ORGANIZATION_ID
3431 ,EXPENDITURE_TYPE
3432 ,FUNC_CURR_ROUNDING_ADJUSTMENT
3433 ,GL_DATE
3434 ,INTENDED_USE
3435 ,ITEM_DIST_NUMBER
3436 -- ,MRC_LINK_TO_TAX_DIST_ID
3437 -- ,ORIG_REC_NREC_RATE
3438 -- ,ORIG_REC_NREC_TAX_AMT
3439 -- ,ORIG_REC_NREC_TAX_AMT_TAX_CURR
3440 -- ,ORIG_REC_RATE_CODE
3441 -- ,PER_TRX_CURR_UNIT_NR_AMT
3442 -- ,PER_UNIT_NREC_TAX_AMT
3443 -- ,PRD_TAX_AMT
3444 -- ,PRICE_DIFF
3445 ,PROJECT_ID
3446 -- ,QTY_DIFF
3447 -- ,RATE_TAX_FACTOR
3448 ,REC_NREC_RATE
3449 ,REC_NREC_TAX_AMT
3450 ,REC_NREC_TAX_AMT_FUNCL_CURR
3451 ,REC_NREC_TAX_AMT_TAX_CURR
3452 ,RECOVERY_RATE_CODE
3453 ,RECOVERY_TYPE_CODE
3454 -- ,RECOVERY_TYPE_ID
3455 -- ,REF_DOC_CURR_CONV_RATE
3456 ,REF_DOC_DIST_ID
3457 -- ,REF_DOC_PER_UNIT_NREC_TAX_AMT
3458 -- ,REF_DOC_TAX_DIST_ID
3459 -- ,REF_DOC_TRX_LINE_DIST_QTY
3460 -- ,REF_DOC_UNIT_PRICE
3461 -- ,REF_PER_TRX_CURR_UNIT_NR_AMT
3462 ,REVERSED_TAX_DIST_ID
3463 -- ,ROUNDING_RULE_CODE
3464 ,TASK_ID
3465 ,TAXABLE_AMT_FUNCL_CURR
3466 ,TAXABLE_AMT_TAX_CURR
3467 ,TRX_LINE_DIST_AMT
3468 ,TRX_LINE_DIST_ID
3469 ,TRX_LINE_DIST_QTY
3470 ,TRX_LINE_DIST_TAX_AMT
3471 -- ,UNROUNDED_REC_NREC_TAX_AMT
3472 -- ,UNROUNDED_TAXABLE_AMT
3473 ,TAXABLE_AMT
3474 ,ATTRIBUTE_CATEGORY
3475 ,ATTRIBUTE1
3476 ,ATTRIBUTE2
3477 ,ATTRIBUTE3
3478 ,ATTRIBUTE4
3479 ,ATTRIBUTE5
3480 ,ATTRIBUTE6
3481 ,ATTRIBUTE7
3482 ,ATTRIBUTE8
3483 ,ATTRIBUTE9
3484 ,ATTRIBUTE10
3485 ,ATTRIBUTE11
3486 ,ATTRIBUTE12
3487 ,ATTRIBUTE13
3488 ,ATTRIBUTE14
3489 ,ATTRIBUTE15
3490 ,GLOBAL_ATTRIBUTE_CATEGORY
3491 ,GLOBAL_ATTRIBUTE1
3492 ,GLOBAL_ATTRIBUTE2
3493 ,GLOBAL_ATTRIBUTE3
3494 ,GLOBAL_ATTRIBUTE4
3495 ,GLOBAL_ATTRIBUTE5
3496 ,GLOBAL_ATTRIBUTE6
3497 ,GLOBAL_ATTRIBUTE7
3498 ,GLOBAL_ATTRIBUTE8
3499 ,GLOBAL_ATTRIBUTE9
3500 ,GLOBAL_ATTRIBUTE10
3501 ,GLOBAL_ATTRIBUTE11
3502 ,GLOBAL_ATTRIBUTE12
3503 ,GLOBAL_ATTRIBUTE13
3504 ,GLOBAL_ATTRIBUTE14
3505 ,GLOBAL_ATTRIBUTE15
3506 ,GLOBAL_ATTRIBUTE16
3507 ,GLOBAL_ATTRIBUTE17
3508 ,GLOBAL_ATTRIBUTE18
3509 ,GLOBAL_ATTRIBUTE19
3510 ,GLOBAL_ATTRIBUTE20
3511 ,HISTORICAL_FLAG
3512 ,OVERRIDDEN_FLAG
3513 ,SELF_ASSESSED_FLAG
3514 ,TAX_APPORTIONMENT_FLAG
3515 ,TAX_ONLY_LINE_FLAG
3516 ,INCLUSIVE_FLAG
3517 ,MRC_TAX_DIST_FLAG
3518 ,REC_TYPE_RULE_FLAG
3519 ,NEW_REC_RATE_CODE_FLAG
3520 ,RECOVERABLE_FLAG
3521 ,REVERSE_FLAG
3522 ,REC_RATE_DET_RULE_FLAG
3523 ,BACKWARD_COMPATIBILITY_FLAG
3524 ,FREEZE_FLAG
3525 ,POSTING_FLAG
3526 ,LEGAL_ENTITY_ID
3527 ,CREATED_BY
3528 ,CREATION_DATE
3529 ,LAST_MANUAL_ENTRY
3530 ,LAST_UPDATE_DATE
3531 ,LAST_UPDATE_LOGIN
3532 ,LAST_UPDATED_BY
3533 ,OBJECT_VERSION_NUMBER
3534 ,ORIG_AP_CHRG_DIST_NUM
3535 ,ORIG_AP_CHRG_DIST_ID
3536 ,ORIG_AP_TAX_DIST_NUM
3537 ,ORIG_AP_TAX_DIST_ID
3538 ,INTERNAL_ORGANIZATION_ID
3539 ,DEF_REC_SETTLEMENT_OPTION_CODE
3540 --,TAX_JURISDICTION_ID
3541 ,ACCOUNT_SOURCE_TAX_RATE_ID
3542 ,RECOVERY_RATE_ID
3543 )
3544 INTO ZX_LINES(
3545 TAX_LINE_ID
3546 ,TAX_LINE_NUMBER
3547 ,APPLICATION_ID
3548 ,CONTENT_OWNER_ID
3549 ,CURRENCY_CONVERSION_DATE
3550 ,CURRENCY_CONVERSION_RATE
3551 ,CURRENCY_CONVERSION_TYPE
3552 ,ENTITY_CODE
3553 ,EVENT_CLASS_CODE
3554 ,EVENT_TYPE_CODE
3555 ,LEDGER_ID
3556 ,MINIMUM_ACCOUNTABLE_UNIT
3557 ,PRECISION
3558 ,RECORD_TYPE_CODE
3559 ,REF_DOC_APPLICATION_ID
3560 ,REF_DOC_ENTITY_CODE
3561 ,REF_DOC_EVENT_CLASS_CODE
3562 ,REF_DOC_LINE_ID
3563 ,REF_DOC_TRX_ID
3564 ,REF_DOC_TRX_LEVEL_TYPE
3565 ,SUMMARY_TAX_LINE_ID
3566 ,TAX
3567 ,TAX_APPORTIONMENT_LINE_NUMBER
3568 ,TAX_CURRENCY_CODE
3569 ,TAX_CURRENCY_CONVERSION_DATE
3570 ,TAX_CURRENCY_CONVERSION_RATE
3571 ,TAX_CURRENCY_CONVERSION_TYPE
3572 ,TAX_EVENT_CLASS_CODE
3573 ,TAX_EVENT_TYPE_CODE
3574 ,TAX_ID
3575 ,TAX_RATE
3576 ,TAX_RATE_CODE
3577 ,TAX_RATE_ID
3578 ,TAX_REGIME_CODE
3579 ,TAX_REGIME_ID
3580 ,TAX_STATUS_CODE
3581 ,TAX_STATUS_ID
3582 ,TRX_CURRENCY_CODE
3583 ,TRX_ID
3584 ,TRX_LEVEL_TYPE
3585 ,TRX_LINE_ID
3586 ,TRX_LINE_NUMBER
3587 ,TRX_NUMBER
3588 ,UNIT_PRICE
3589 ,TAX_RATE_TYPE
3590 ,ADJUSTED_DOC_APPLICATION_ID
3591 -- ,ADJUSTED_DOC_DATE
3592 ,ADJUSTED_DOC_ENTITY_CODE
3593 ,ADJUSTED_DOC_EVENT_CLASS_CODE
3594 ,ADJUSTED_DOC_LINE_ID
3595 -- ,ADJUSTED_DOC_NUMBER
3596 ,ADJUSTED_DOC_TRX_ID
3597 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
3598 ,APPLIED_FROM_APPLICATION_ID
3599 ,APPLIED_FROM_ENTITY_CODE
3600 ,APPLIED_FROM_EVENT_CLASS_CODE
3601 ,APPLIED_FROM_LINE_ID
3602 -- ,APPLIED_FROM_TRX_NUMBER
3603 ,APPLIED_FROM_TRX_ID
3604 ,APPLIED_FROM_TRX_LEVEL_TYPE
3605 ,APPLIED_TO_APPLICATION_ID
3606 ,APPLIED_TO_ENTITY_CODE
3607 ,APPLIED_TO_EVENT_CLASS_CODE
3608 ,APPLIED_TO_LINE_ID
3609 ,APPLIED_TO_TRX_ID
3610 ,APPLIED_TO_TRX_LEVEL_TYPE
3611 -- ,APPLIED_TO_TRX_NUMBER
3612 -- ,CAL_TAX_AMT
3613 -- ,CAL_TAX_AMT_FUNCL_CURR
3614 -- ,CAL_TAX_AMT_TAX_CURR
3615 -- ,DOC_EVENT_STATUS
3616 -- ,INTERNAL_ORG_LOCATION_ID
3617 ,INTERNAL_ORGANIZATION_ID
3618 ,LINE_AMT
3619 ,LINE_ASSESSABLE_VALUE
3620 -- ,MRC_LINK_TO_TAX_LINE_ID
3621 ,NREC_TAX_AMT
3622 ,NREC_TAX_AMT_FUNCL_CURR
3623 ,NREC_TAX_AMT_TAX_CURR
3624 -- ,OFFSET_LINK_TO_TAX_LINE_ID
3625 -- ,OFFSET_TAX_RATE_CODE
3626 -- ,ORIG_TAX_AMT
3627 -- ,ORIG_TAX_AMT_TAX_CURR
3628 -- ,ORIG_TAX_RATE
3629 -- ,ORIG_TAX_RATE_CODE
3630 -- ,ORIG_TAX_RATE_ID
3631 -- ,ORIG_TAX_STATUS_CODE
3632 -- ,ORIG_TAX_STATUS_ID
3633 -- ,ORIG_TAXABLE_AMT
3634 -- ,ORIG_TAXABLE_AMT_TAX_CURR
3635 -- ,OTHER_DOC_LINE_AMT
3636 -- ,OTHER_DOC_LINE_TAX_AMT
3637 -- ,OTHER_DOC_LINE_TAXABLE_AMT
3638 -- ,OTHER_DOC_SOURCE
3639 -- ,PRORATION_CODE
3640 ,REC_TAX_AMT
3641 ,REC_TAX_AMT_FUNCL_CURR
3642 ,REC_TAX_AMT_TAX_CURR
3643 -- ,REF_DOC_LINE_QUANTITY
3644 -- ,RELATED_DOC_APPLICATION_ID
3645 -- ,RELATED_DOC_DATE
3646 -- ,RELATED_DOC_ENTITY_CODE
3647 -- ,RELATED_DOC_EVENT_CLASS_CODE
3648 -- ,RELATED_DOC_NUMBER
3649 -- ,RELATED_DOC_TRX_ID
3650 -- ,RELATED_DOC_TRX_LEVEL_TYPE
3651 -- ,REPORTING_CURRENCY_CODE
3652 ,TAX_AMT
3653 ,TAX_AMT_FUNCL_CURR
3654 ,TAX_AMT_TAX_CURR
3655 ,TAX_CALCULATION_FORMULA
3656 -- ,TAX_CODE
3657 ,TAX_DATE
3658 ,TAX_DETERMINE_DATE
3659 ,TAX_POINT_DATE
3660 -- ,TAX_TYPE_CODE
3661 -- ,ROUNDING_RULE_CODE
3662 ,TAXABLE_AMT
3663 ,TAXABLE_AMT_FUNCL_CURR
3664 ,TAXABLE_AMT_TAX_CURR
3665 ,TAXABLE_BASIS_FORMULA
3666 ,TRX_DATE
3667 ,TRX_LINE_DATE
3668 ,TRX_LINE_QUANTITY
3669 -- ,UNROUNDED_TAX_AMT
3670 -- ,UNROUNDED_TAXABLE_AMT
3671 ,HISTORICAL_FLAG
3672 ,OVERRIDDEN_FLAG
3673 ,SELF_ASSESSED_FLAG
3674 ,TAX_APPORTIONMENT_FLAG
3675 ,TAX_ONLY_LINE_FLAG
3676 ,TAX_AMT_INCLUDED_FLAG
3677 ,MRC_TAX_LINE_FLAG
3678 ,OFFSET_FLAG
3679 ,PROCESS_FOR_RECOVERY_FLAG
3680 ,COMPOUNDING_TAX_FLAG
3681 ,ORIG_TAX_AMT_INCLUDED_FLAG
3682 ,ORIG_SELF_ASSESSED_FLAG
3683 ,CANCEL_FLAG
3684 ,PURGE_FLAG
3685 ,DELETE_FLAG
3686 ,MANUALLY_ENTERED_FLAG
3687 --,LAST_MANUAL_ENTRY --BUG7146063
3688 ,REPORTING_ONLY_FLAG
3689 ,FREEZE_UNTIL_OVERRIDDEN_FLAG
3690 ,COPIED_FROM_OTHER_DOC_FLAG
3691 ,RECALC_REQUIRED_FLAG
3692 ,SETTLEMENT_FLAG
3693 ,ITEM_DIST_CHANGED_FLAG
3694 ,ASSOCIATED_CHILD_FROZEN_FLAG
3695 ,COMPOUNDING_DEP_TAX_FLAG
3696 ,ENFORCE_FROM_NATURAL_ACCT_FLAG
3697 ,ATTRIBUTE_CATEGORY
3698 ,ATTRIBUTE1
3699 ,ATTRIBUTE2
3700 ,ATTRIBUTE3
3701 ,ATTRIBUTE4
3702 ,ATTRIBUTE5
3703 ,ATTRIBUTE6
3704 ,ATTRIBUTE7
3705 ,ATTRIBUTE8
3706 ,ATTRIBUTE9
3707 ,ATTRIBUTE10
3708 ,ATTRIBUTE11
3709 ,ATTRIBUTE12
3710 ,ATTRIBUTE13
3711 ,ATTRIBUTE14
3712 ,ATTRIBUTE15
3713 ,GLOBAL_ATTRIBUTE_CATEGORY
3714 ,GLOBAL_ATTRIBUTE1
3715 ,GLOBAL_ATTRIBUTE2
3716 ,GLOBAL_ATTRIBUTE3
3717 ,GLOBAL_ATTRIBUTE4
3718 ,GLOBAL_ATTRIBUTE5
3719 ,GLOBAL_ATTRIBUTE6
3720 ,GLOBAL_ATTRIBUTE7
3721 ,GLOBAL_ATTRIBUTE8
3722 ,GLOBAL_ATTRIBUTE9
3723 ,GLOBAL_ATTRIBUTE10
3724 ,GLOBAL_ATTRIBUTE11
3725 ,GLOBAL_ATTRIBUTE12
3726 ,GLOBAL_ATTRIBUTE13
3727 ,GLOBAL_ATTRIBUTE14
3728 ,GLOBAL_ATTRIBUTE15
3729 ,LAST_MANUAL_ENTRY
3730 ,LEGAL_ENTITY_ID
3731 -- ,ESTABLISHMENT_ID
3732 ,CREATED_BY
3733 ,CREATION_DATE
3734 ,LAST_UPDATE_DATE
3735 ,LAST_UPDATE_LOGIN
3736 ,LAST_UPDATED_BY
3737 ,OBJECT_VERSION_NUMBER
3738 ,MULTIPLE_JURISDICTIONS_FLAG
3739 ,LEGAL_REPORTING_STATUS
3740 ,ACCOUNT_SOURCE_TAX_RATE_ID
3741 )
3742 VALUES (
3743 ZX_LINES_S.NEXTVAL
3744 ,TAX_LINE_NUMBER
3745 ,APPLICATION_ID
3746 ,CONTENT_OWNER_ID
3747 ,CURRENCY_CONVERSION_DATE
3748 ,CURRENCY_CONVERSION_RATE
3749 ,CURRENCY_CONVERSION_TYPE
3750 ,ENTITY_CODE
3751 ,EVENT_CLASS_CODE
3752 ,EVENT_TYPE_CODE
3753 ,AP_LEDGER_ID
3754 ,MINIMUM_ACCOUNTABLE_UNIT
3755 ,PRECISION
3756 ,RECORD_TYPE_CODE
3757 ,REF_DOC_APPLICATION_ID
3758 ,REF_DOC_ENTITY_CODE
3759 ,REF_DOC_EVENT_CLASS_CODE
3760 ,REF_DOC_LINE_ID
3761 ,REF_DOC_TRX_ID
3762 ,REF_DOC_TRX_LEVEL_TYPE
3763 ,SUMMARY_TAX_LINE_ID
3764 ,TAX
3765 ,TAX_APPORTIONMENT_LINE_NUMBER
3766 ,TAX_CURRENCY_CODE
3767 ,TAX_CURRENCY_CONVERSION_DATE
3768 ,TAX_CURRENCY_CONVERSION_RATE
3769 ,TAX_CURRENCY_CONVERSION_TYPE
3770 ,TAX_EVENT_CLASS_CODE
3771 ,TAX_EVENT_TYPE_CODE
3772 ,TAX_ID
3773 ,TAX_RATE
3774 ,TAX_RATE_CODE
3775 ,TAX_RATE_ID
3776 ,TAX_REGIME_CODE
3777 ,TAX_REGIME_ID
3778 ,TAX_STATUS_CODE
3779 ,TAX_STATUS_ID
3780 ,TRX_CURRENCY_CODE
3781 ,TRX_ID
3782 ,TRX_LEVEL_TYPE
3783 ,TRX_LINE_ID
3784 ,TRX_LINE_NUMBER
3785 ,TRX_NUMBER
3786 ,UNIT_PRICE
3787 ,RATE_TYPE_CODE
3788 ,ADJUSTED_DOC_APPLICATION_ID
3789 -- ,ADJUSTED_DOC_DATE
3790 ,ADJUSTED_DOC_ENTITY_CODE
3791 ,ADJUSTED_DOC_EVENT_CLASS_CODE
3792 ,ADJUSTED_DOC_LINE_ID
3793 -- ,ADJUSTED_DOC_NUMBER
3794 ,ADJUSTED_DOC_TRX_ID
3795 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
3796 ,APPLIED_FROM_APPLICATION_ID
3797 ,APPLIED_FROM_ENTITY_CODE
3798 ,APPLIED_FROM_EVENT_CLASS_CODE
3799 ,APPLIED_FROM_LINE_ID
3800 -- ,APPLIED_FROM_TRX_NUMBER
3801 ,APPLIED_FROM_TRX_ID
3802 ,APPLIED_FROM_TRX_LEVEL_TYPE
3803 ,APPLIED_TO_APPLICATION_ID
3804 ,APPLIED_TO_ENTITY_CODE
3805 ,APPLIED_TO_EVENT_CLASS_CODE
3806 ,APPLIED_TO_LINE_ID
3807 ,APPLIED_TO_TRX_ID
3808 ,APPLIED_TO_TRX_LEVEL_TYPE
3809 -- ,APPLIED_TO_TRX_NUMBER
3810 -- ,NULL -- CAL_TAX_AMT
3811 -- ,NULL -- CAL_TAX_AMT_FUNCL_CURR
3812 -- ,NULL -- CAL_TAX_AMT_TAX_CURR
3813 -- ,DOC_EVENT_STATUS
3814 -- ,INTERNAL_ORG_LOCATION_ID
3815 ,INTERNAL_ORGANIZATION_ID
3816 ,LINE_AMT
3817 ,ASSESSABLE_VALUE
3818 -- ,NULL -- MRC_LINK_TO_TAX_LINE_ID
3819 ,DECODE(AP_DIST_LOOKUP_CODE,
3820 'NONREC_TAX', REC_NREC_TAX_AMT, NULL) -- NREC_TAX_AMT
3821 ,DECODE(AP_DIST_LOOKUP_CODE,
3822 'NONREC_TAX', REC_NREC_TAX_AMT_FUNCL_CURR, NULL) -- NREC_TAX_AMT_FUNCL_CURR
3823 ,DECODE(AP_DIST_LOOKUP_CODE,
3824 'NONREC_TAX', REC_NREC_TAX_AMT_TAX_CURR, NULL) -- NREC_TAX_AMT_TAX_CURR
3825 -- ,NULL -- OFFSET_LINK_TO_TAX_LINE_ID
3826 -- ,NULL -- OFFSET_TAX_RATE_CODE
3827 -- ,NULL -- ORIG_TAX_AMT
3828 -- ,NULL -- ORIG_TAX_AMT_TAX_CURR
3829 -- ,NULL -- ORIG_TAX_RATE
3830 -- ,NULL -- ORIG_TAX_RATE_CODE
3831 -- ,NULL -- ORIG_TAX_RATE_ID
3832 -- ,NULL -- ORIG_TAX_STATUS_CODE
3833 -- ,NULL -- ORIG_TAX_STATUS_ID
3834 -- ,NULL -- ORIG_TAXABLE_AMT
3835 -- ,NULL -- ORIG_TAXABLE_AMT_TAX_CURR
3836 -- ,NULL -- OTHER_DOC_LINE_AMT
3837 -- ,NULL -- OTHER_DOC_LINE_TAX_AMT
3838 -- ,NULL -- OTHER_DOC_LINE_TAXABLE_AMT
3839 -- ,NULL -- OTHER_DOC_SOURCE
3840 -- ,NULL -- PRORATION_CODE
3841 ,DECODE(AP_DIST_LOOKUP_CODE,
3842 'REC_TAX', REC_NREC_TAX_AMT, NULL) -- REC_TAX_AMT
3843 ,DECODE(AP_DIST_LOOKUP_CODE,
3844 'REC_TAX', REC_NREC_TAX_AMT_FUNCL_CURR, NULL) -- REC_TAX_AMT_FUNCL_CURR
3845 ,DECODE(AP_DIST_LOOKUP_CODE,
3846 'REC_TAX', REC_NREC_TAX_AMT_TAX_CURR, NULL) -- REC_TAX_AMT_TAX_CURR
3847 -- ,REF_DOC_LINE_QUANTITY
3848 -- ,RELATED_DOC_APPLICATION_ID
3849 -- ,RELATED_DOC_DATE
3850 -- ,RELATED_DOC_ENTITY_CODE
3851 -- ,RELATED_DOC_EVENT_CLASS_CODE
3852 -- ,RELATED_DOC_NUMBER
3853 -- ,RELATED_DOC_TRX_ID
3854 -- ,RELATED_DOC_TRX_LEVEL_TYPE
3855 -- ,NULL -- REPORTING_CURRENCY_CODE
3856 ,TAX_AMT
3857 ,TAX_AMT_FUNCL_CURR
3858 ,TAX_AMT_TAX_CURR
3859 ,'STANDARD_TC'
3860 -- ,NULL -- TAX_CODE
3861 ,TAX_DATE
3862 ,TAX_DETERMINE_DATE
3863 ,TAX_POINT_DATE
3864 -- ,NULL -- TAX_TYPE_CODE
3865 -- ,ROUNDING_RULE_CODE
3866 ,TAXABLE_AMT
3867 ,TAXABLE_AMT_FUNCL_CURR
3868 ,TAXABLE_AMT_TAX_CURR
3869 -- Bug 9037817 'STANDARD_TB'
3870 ,TAXABLE_BASIS_FORMULA
3871 ,TRX_DATE
3872 ,TRX_LINE_DATE
3873 ,TRX_LINE_QUANTITY
3874 -- ,NULL -- UNROUNDED_TAX_AMT
3875 -- ,NULL -- UNROUNDED_TAXABLE_AMT
3876 ,HISTORICAL_FLAG
3877 ,OVERRIDDEN_FLAG
3878 ,SELF_ASSESSED_FLAG
3879 ,TAX_APPORTIONMENT_FLAG
3880 ,TAX_ONLY_LINE_FLAG
3881 ,TAX_AMT_INCLUDED_FLAG
3882 ,'N' -- MRC_TAX_LINE_FLAG
3883 ,OFFSET_FLAG -- Bug 8303411
3884 ,'N' -- PROCESS_FOR_RECOVERY_FLAG
3885 ,COMPOUNDING_TAX_FLAG
3886 ,'N' -- ORIG_TAX_AMT_INCLUDED_FLAG
3887 ,'N' -- ORIG_SELF_ASSESSED_FLAG
3888 ,CANCEL_FLAG
3889 ,'N' -- PURGE_FLAG
3890 ,'N' -- DELETE_FLAG
3891 ,MANUALLY_ENTERED_FLAG
3892 --,LAST_MANUAL_ENTRY --BUG7146063
3893 ,'N' -- REPORTING_ONLY_FLAG
3894 ,'N' -- FREEZE_UNTIL_OVERRIDDEN_FLAG
3895 ,'N' -- COPIED_FROM_OTHER_DOC_FLAG
3896 ,'N' -- RECALC_REQUIRED_FLAG
3897 ,'N' -- SETTLEMENT_FLAG
3898 ,'N' -- ITEM_DIST_CHANGED_FLAG
3899 ,ASSOCIATED_CHILD_FROZEN_FLAG
3900 ,'N' -- COMPOUNDING_DEP_TAX_FLAG
3901 ,'N' -- ENFORCE_FROM_NATURAL_ACCT_FLAG
3902 ,ATTRIBUTE_CATEGORY
3903 ,ATTRIBUTE1
3904 ,ATTRIBUTE2
3905 ,ATTRIBUTE3
3906 ,ATTRIBUTE4
3907 ,ATTRIBUTE5
3908 ,ATTRIBUTE6
3909 ,ATTRIBUTE7
3910 ,ATTRIBUTE8
3911 ,ATTRIBUTE9
3912 ,ATTRIBUTE10
3913 ,ATTRIBUTE11
3914 ,ATTRIBUTE12
3915 ,ATTRIBUTE13
3916 ,ATTRIBUTE14
3917 ,ATTRIBUTE15
3918 ,GLOBAL_ATTRIBUTE_CATEGORY
3919 ,GLOBAL_ATTRIBUTE1
3920 ,GLOBAL_ATTRIBUTE2
3921 ,GLOBAL_ATTRIBUTE3
3922 ,GLOBAL_ATTRIBUTE4
3923 ,GLOBAL_ATTRIBUTE5
3924 ,GLOBAL_ATTRIBUTE6
3925 ,GLOBAL_ATTRIBUTE7
3926 ,GLOBAL_ATTRIBUTE8
3927 ,GLOBAL_ATTRIBUTE9
3928 ,GLOBAL_ATTRIBUTE10
3929 ,GLOBAL_ATTRIBUTE11
3930 ,GLOBAL_ATTRIBUTE12
3931 ,GLOBAL_ATTRIBUTE13
3932 ,GLOBAL_ATTRIBUTE14
3933 ,GLOBAL_ATTRIBUTE15
3934 ,LAST_MANUAL_ENTRY
3935 ,LEGAL_ENTITY_ID
3936 -- ,ESTABLISHMENT_ID
3937 ,CREATED_BY
3938 ,CREATION_DATE
3939 ,LAST_UPDATE_DATE
3940 ,LAST_UPDATE_LOGIN
3941 ,LAST_UPDATED_BY
3942 ,OBJECT_VERSION_NUMBER
3943 ,MULTIPLE_JURISDICTIONS_FLAG
3944 ,LEGAL_REPORTING_STATUS
3945 ,ACCOUNT_SOURCE_TAX_RATE_ID
3946 )
3947 (SELECT /*+ ROWID(inv) NO_EXPAND ORDERED
3948 use_nl(fnd_curr,ap_dists,ap_dists1,lines1,rates,regimes,taxes,status,ptp)
3949 index(taxes,ZX_TAXES_B_U2) */
3950 NVL(lines1.org_id,-99) INTERNAL_ORGANIZATION_ID
3951 ,200 APPLICATION_ID
3952 ,'AP_INVOICES' ENTITY_CODE
3953 ,DECODE(inv.INVOICE_TYPE_LOOKUP_CODE ,
3954 'STANDARD', 'STANDARD INVOICES' ,
3955 'CREDIT' , 'STANDARD INVOICES', --Bug 6489409
3956 'DEBIT' , 'STANDARD INVOICES', --Bug 6489409
3957 'MIXED' , 'STANDARD INVOICES', --Bug 6489409
3958 'ADJUSTMENT','STANDARD INVOICES', --Bug 6489409
3959 'PO PRICE ADJUST','STANDARD INVOICES', --Bug 6489409
3960 'INVOICE REQUEST','STANDARD INVOICES', --Bug 6489409
3961 'CREDIT MEMO REQUEST','STANDARD INVOICES',--Bug 6489409
3962 'RETAINAGE RELEASE' ,'STANDARD INVOICES',--Bug 6489409
3963 'PREPAYMENT','PREPAYMENT INVOICES' ,
3964 'EXPENSE REPORT','EXPENSE REPORTS' ,
3965 'INTEREST INVOICE','INTEREST INVOICES','NA') EVENT_CLASS_CODE
3966 ,DECODE(inv.INVOICE_TYPE_LOOKUP_CODE,
3967 'STANDARD','STANDARD INVOICE CREATED',
3968 'PREPAYMENT','PREPAYMENT INVOICE CREATED',
3969 'EXPENSE REPORT','EXPENSE REPORT CREATED',
3970 'INTEREST INVOICE','INTEREST INVOICE CREATED','NA') EVENT_TYPE_CODE
3971 ,(CASE WHEN inv.invoice_type_lookup_code in
3972 ('ADJUSTMENT','CREDIT','DEBIT','INTEREST',
3973 'MIXED','QUICKDEFAULT','PO PRICE ADJUST',
3974 'QUICKMATCH','STANDARD','AWT')
3975 THEN 'PURCHASE_TRANSACTION'
3976 WHEN (inv.invoice_type_lookup_code =
3977 'PREPAYMENT')
3978 THEN 'PURCHASE_PREPAYMENTTRANSACTION'
3979 WHEN (inv.invoice_type_lookup_code =
3980 'EXPENSE REPORT')
3981 THEN 'EXPENSE_REPORT'
3982 ELSE NULL
3983 END) TAX_EVENT_CLASS_CODE
3984 ,'VALIDATE' TAX_EVENT_TYPE_CODE
3985 -- ,NULL DOC_EVENT_STATUS
3986 ,lines1.invoice_id TRX_ID
3987 ,NVL(inv.invoice_date,sysdate) TRX_DATE
3988 ,inv.invoice_currency_code TRX_CURRENCY_CODE
3989 ,NVL(inv.legal_entity_id, -99) LEGAL_ENTITY_ID
3990 -- ,NULL ESTABLISHMENT_ID
3991 ,inv.invoice_num TRX_NUMBER
3992 -- ,DECODE(ap_dists.charge_applicable_to_dist_id,NULL,1,
3993 ,(RANK() OVER (PARTITION BY inv.invoice_id ORDER BY
3994 ap_dists1.invoice_line_number,
3995 ap_dists.invoice_distribution_id)) TAX_LINE_NUMBER
3996 ,lines1.accounting_date TRX_LINE_DATE
3997 ,NVL(lines1.amount,0) LINE_AMT
3998 ,NVL(lines1.quantity_invoiced, 0) TRX_LINE_QUANTITY
3999 ,lines1.UNIT_PRICE UNIT_PRICE
4000 -- ,NULL RELATED_DOC_APPLICATION_ID
4001 -- ,NULL RELATED_DOC_ENTITY_CODE
4002 -- ,NULL RELATED_DOC_EVENT_CLASS_CODE
4003 -- ,NULL RELATED_DOC_TRX_ID
4004 -- ,NULL RELATED_DOC_TRX_LEVEL_TYPE
4005 -- ,NULL RELATED_DOC_NUMBER
4006 -- ,NULL RELATED_DOC_DATE
4007 ,DECODE(lines1.prepay_invoice_id, NULL, NULL, 200) APPLIED_FROM_APPLICATION_ID
4008 ,DECODE(lines1.prepay_invoice_id, NULL, NULL,
4009 'AP_INVOICES') APPLIED_FROM_ENTITY_CODE
4010 ,DECODE(lines1.prepay_invoice_id, NULL, NULL,
4011 'PREPAYMENT INVOICES') APPLIED_FROM_EVENT_CLASS_CODE
4012 ,lines1.prepay_invoice_id APPLIED_FROM_TRX_ID
4013 ,lines1.prepay_line_number APPLIED_FROM_LINE_ID
4014 -- ,NULL APPLIED_FROM_TRX_NUMBER
4015 ,DECODE(lines1.corrected_inv_id, NULL, NULL, 200) ADJUSTED_DOC_APPLICATION_ID
4016 ,DECODE(lines1.corrected_inv_id, NULL, NULL,
4017 'AP_INVOICES') ADJUSTED_DOC_ENTITY_CODE
4018 ,DECODE(lines1.corrected_inv_id, NULL, NULL,
4019 'STANDARD INVOICES') ADJUSTED_DOC_EVENT_CLASS_CODE
4020 ,lines1.corrected_inv_id ADJUSTED_DOC_TRX_ID
4021 ,lines1.Corrected_Line_Number ADJUSTED_DOC_LINE_ID
4022 -- ,NULL ADJUSTED_DOC_NUMBER
4023 -- ,NULL ADJUSTED_DOC_DATE
4024 ,DECODE(lines1.rcv_transaction_id, NULL, NULL, 707) APPLIED_TO_APPLICATION_ID
4025 ,DECODE(lines1.rcv_transaction_id, NULL, NULL,
4026 'RCV_ACCOUNTING_EVENTS') APPLIED_TO_ENTITY_CODE
4027 ,DECODE(lines1.rcv_transaction_id, NULL, NULL,
4028 'RCPT_REC_INSP') APPLIED_TO_EVENT_CLASS_CODE
4029 ,lines1.rcv_transaction_id APPLIED_TO_TRX_ID
4030 ,lines1.rcv_shipment_line_id APPLIED_TO_LINE_ID
4031 -- ,NULL APPLIED_TO_TRX_NUMBER
4032 ,DECODE(NVL(lines1.po_release_id,lines1.po_header_id),
4033 NULL, NULL, 'SHIPMENT') REF_DOC_TRX_LEVEL_TYPE
4034 ,NVL(lines1.po_release_id, lines1.po_header_id) REF_DOC_TRX_ID
4035 ,lines1.po_line_location_id REF_DOC_LINE_ID
4036 -- ,NULL REF_DOC_LINE_QUANTITY
4037 ,DECODE(lines1.rcv_transaction_id, NULL, NULL,
4038 'LINE') APPLIED_TO_TRX_LEVEL_TYPE
4039 ,DECODE(lines1.prepay_invoice_id, NULL, NULL,
4040 'LINE') APPLIED_FROM_TRX_LEVEL_TYPE
4041 ,DECODE(lines1.corrected_inv_id, NULL, NULL,
4042 'LINE') ADJUSTED_DOC_TRX_LEVEL_TYPE
4043 -- ,NULL INTERNAL_ORG_LOCATION_ID
4044 ,'MIGRATED' RECORD_TYPE_CODE
4045 ,lines1.ASSESSABLE_VALUE -- ASSESSABLE_VALUE
4046 ,'N' TAX_AMT_INCLUDED_FLAG
4047 ,'N' COMPOUNDING_TAX_FLAG
4048 ,DECODE(taxes.tax_type_code,'OFFSET','Y','N') OFFSET_FLAG --Bug 8303411
4049 ,ap_dists.DETAIL_TAX_DIST_ID REC_NREC_TAX_DIST_ID
4050 ,ap_dists.line_type_lookup_code AP_DIST_LOOKUP_CODE
4051 -- DECODE(ap_dists.charge_applicable_to_dist_id, NULL, 1,
4052 ,RANK() OVER (PARTITION BY inv.invoice_id,
4053 ap_dists.charge_applicable_to_dist_id
4054 ORDER BY
4055 ap_dists.line_type_lookup_code desc,
4056 ap_dists.invoice_distribution_id) REC_NREC_TAX_DIST_NUMBER
4057 ,ptp.party_tax_profile_id CONTENT_OWNER_ID
4058 ,inv.exchange_date CURRENCY_CONVERSION_DATE
4059 ,inv.exchange_rate CURRENCY_CONVERSION_RATE
4060 ,inv.exchange_rate_type CURRENCY_CONVERSION_TYPE
4061 ,ap_dists.set_of_books_id AP_LEDGER_ID
4062 ,fnd_curr.minimum_accountable_unit MINIMUM_ACCOUNTABLE_UNIT
4063 ,NVL(fnd_curr.precision, 0) PRECISION
4064 ,DECODE(NVL(lines1.po_release_id, lines1.po_header_id),
4065 NULL, NULL, 201) REF_DOC_APPLICATION_ID
4066 ,DECODE(lines1.po_release_id, NULL,
4067 DECODE(lines1.po_header_id, NULL, NULL,
4068 'PURCHASE_ORDER'), 'RELEASE') REF_DOC_ENTITY_CODE
4069 ,DECODE(lines1.po_release_id, NULL,
4070 DECODE(lines1.po_header_id, NULL, NULL,
4071 'PO_PA'), 'RELEASE') REF_DOC_EVENT_CLASS_CODE
4072 ,ap_dists.summary_tax_line_id SUMMARY_TAX_LINE_ID
4073 ,rates.TAX TAX
4074 -- ,DECODE(ap_dists.charge_applicable_to_dist_id,NULL,1,
4075 ,RANK() OVER (PARTITION BY inv.invoice_id,
4076 ap_dists1.invoice_line_number,
4077 rates.tax_regime_code, rates.tax
4078 ORDER BY
4079 ap_dists.invoice_distribution_id) TAX_APPORTIONMENT_LINE_NUMBER
4080 ,taxes.tax_currency_code -- TAX_CURRENCY_CODE
4081 ,inv.exchange_date TAX_CURRENCY_CONVERSION_DATE
4082 ,inv.exchange_rate TAX_CURRENCY_CONVERSION_RATE
4083 ,inv.exchange_rate_type TAX_CURRENCY_CONVERSION_TYPE
4084 ,DECODE(lines.line_source,'MANUAL LINE ENTRY','PRORATED_TB','STANDARD_TB') TAXABLE_BASIS_FORMULA
4085 ,taxes.tax_id -- TAX_ID
4086 ,rates.percentage_rate TAX_RATE
4087 ,rates.tax_rate_code -- TAX_RATE_CODE
4088 ,rates.tax_rate_id -- TAX_RATE_ID
4089 ,rates.tax_regime_code -- TAX_REGIME_CODE
4090 ,rates.rate_type_code
4091 ,regimes.tax_regime_id -- TAX_REGIME_ID
4092 ,rates.tax_status_code -- TAX_STATUS_CODE
4093 ,status.tax_status_id -- TAX_STATUS_ID
4094 ,'LINE' TRX_LEVEL_TYPE
4095 ,lines1.line_number TRX_LINE_ID
4096 ,lines1.line_number TRX_LINE_NUMBER
4097 ,ap_dists.dist_code_combination_id ACCOUNT_CCID
4098 -- ,NULL ACCOUNT_STRING
4099 -- ,NULL ADJUSTED_DOC_TAX_DIST_ID
4100 -- ,NULL APPLIED_FROM_TAX_DIST_ID
4101 -- ,NULL APPLIED_TO_DOC_CURR_CONV_RATE
4102 ,ap_dists.award_id -- AWARD_ID
4103 ,ap_dists.expenditure_item_date -- EXPENDITURE_ITEM_DATE
4104 ,ap_dists.expenditure_organization_id -- EXPENDITURE_ORGANIZATION_ID
4105 ,ap_dists.expenditure_type -- EXPENDITURE_TYPE
4106 ,NULL FUNC_CURR_ROUNDING_ADJUSTMENT
4107 ,ap_dists.ACCOUNTING_DATE GL_DATE
4108 ,ap_dists.intended_use -- INTENDED_USE
4109 ,ap_dists1.distribution_line_number ITEM_DIST_NUMBER
4110 -- ,NULL MRC_LINK_TO_TAX_DIST_ID
4111 -- ,NULL ORIG_REC_NREC_RATE
4112 -- ,NULL ORIG_REC_NREC_TAX_AMT
4113 -- ,NULL ORIG_REC_NREC_TAX_AMT_TAX_CURR
4114 -- ,NULL ORIG_REC_RATE_CODE
4115 -- ,NULL PER_TRX_CURR_UNIT_NR_AMT
4116 -- ,NULL PER_UNIT_NREC_TAX_AMT
4117 -- ,NULL PRD_TAX_AMT
4118 -- ,NULL PRICE_DIFF
4119 ,ap_dists.project_id -- PROJECT_ID
4120 -- ,NULL QTY_DIFF
4121 -- ,NULL RATE_TAX_FACTOR
4122 --,NVL(ap_dists.rec_nrec_rate, 0) REC_NREC_RATE
4123 ,100 REC_NREC_RATE
4124 ,NVL(ap_dists.amount,0) REC_NREC_TAX_AMT
4125 ,ap_dists.base_amount REC_NREC_TAX_AMT_FUNCL_CURR
4126 ,ap_dists.base_amount REC_NREC_TAX_AMT_TAX_CURR
4127 ,DECODE(ap_dists.line_type_lookup_code,
4128 'REC_TAX', 'AD_HOC_RECOVERY', NULL) RECOVERY_RATE_CODE
4129 ,DECODE(ap_dists.line_type_lookup_code,
4130 'REC_TAX', 'STANDARD', NULL) RECOVERY_TYPE_CODE
4131 ,NVL(ap_dists.amount,0) TAX_AMT
4132 ,ap_dists.base_amount TAX_AMT_FUNCL_CURR
4133 ,ap_dists.base_amount TAX_AMT_TAX_CURR
4134 -- ,NULL RECOVERY_TYPE_ID
4135 -- ,NULL REF_DOC_CURR_CONV_RATE
4136 ,ap_dists1.po_distribution_id REF_DOC_DIST_ID
4137 -- ,NULL REF_DOC_PER_UNIT_NREC_TAX_AMT
4138 -- ,NULL REF_DOC_TAX_DIST_ID
4139 -- ,NULL REF_DOC_TRX_LINE_DIST_QTY
4140 -- ,NULL REF_DOC_UNIT_PRICE
4141 -- ,NULL REF_PER_TRX_CURR_UNIT_NR_AMT
4142 ,ap_dists.parent_reversal_id REVERSED_TAX_DIST_ID
4143 -- ,NULL ROUNDING_RULE_CODE
4144 ,ap_dists.task_id -- TASK_ID
4145 ,ap_dists.taxable_base_amount TAXABLE_AMT_FUNCL_CURR
4146 ,ap_dists.taxable_base_amount TAXABLE_AMT_TAX_CURR
4147 ,ap_dists1.amount TRX_LINE_DIST_AMT
4148 --,ap_dists1.invoice_distribution_id TRX_LINE_DIST_ID
4149 -- Bug 9947835
4150 ,DECODE(ap_dists.charge_applicable_to_dist_id,
4151 NULL, lines1.line_number, ap_dists1.invoice_distribution_id) TRX_LINE_DIST_ID
4152 ,NVL(ap_dists1.quantity_invoiced, 0) TRX_LINE_DIST_QTY
4153 ,DECODE(ap_dists.charge_applicable_to_dist_id, NULL,
4154 ap_dists.amount,
4155 SUM (ap_dists.amount) OVER
4156 (PARTITION BY ap_dists.invoice_id,
4157 ap_dists.charge_applicable_to_dist_id)) TRX_LINE_DIST_TAX_AMT
4158 -- ,NULL UNROUNDED_REC_NREC_TAX_AMT
4159 -- ,NULL UNROUNDED_TAXABLE_AMT
4160 ,ap_dists.TAXABLE_AMOUNT TAXABLE_AMT
4161 ,ap_dists.ATTRIBUTE_CATEGORY -- ATTRIBUTE_CATEGORY
4162 ,ap_dists.ATTRIBUTE1 -- ATTRIBUTE1
4163 ,ap_dists.ATTRIBUTE2 -- ATTRIBUTE2
4164 ,ap_dists.ATTRIBUTE3 -- ATTRIBUTE3
4165 ,ap_dists.ATTRIBUTE4 -- ATTRIBUTE4
4166 ,ap_dists.ATTRIBUTE5 -- ATTRIBUTE5
4167 ,ap_dists.ATTRIBUTE6 -- ATTRIBUTE6
4168 ,ap_dists.ATTRIBUTE7 -- ATTRIBUTE7
4169 ,ap_dists.ATTRIBUTE8 -- ATTRIBUTE8
4170 ,ap_dists.ATTRIBUTE9 -- ATTRIBUTE9
4171 ,ap_dists.ATTRIBUTE10 -- ATTRIBUTE10
4172 ,ap_dists.ATTRIBUTE11 -- ATTRIBUTE11
4173 ,ap_dists.ATTRIBUTE12 -- ATTRIBUTE12
4174 ,ap_dists.ATTRIBUTE13 -- ATTRIBUTE13
4175 ,ap_dists.ATTRIBUTE14 -- ATTRIBUTE14
4176 ,ap_dists.ATTRIBUTE15 -- ATTRIBUTE15
4177 ,ap_dists.GLOBAL_ATTRIBUTE_CATEGORY -- GLOBAL_ATTRIBUTE_CATEGORY
4178 ,ap_dists.GLOBAL_ATTRIBUTE1 -- GLOBAL_ATTRIBUTE1
4179 ,ap_dists.GLOBAL_ATTRIBUTE2 -- GLOBAL_ATTRIBUTE2
4180 ,ap_dists.GLOBAL_ATTRIBUTE3 -- GLOBAL_ATTRIBUTE3
4181 ,ap_dists.GLOBAL_ATTRIBUTE4 -- GLOBAL_ATTRIBUTE4
4182 ,ap_dists.GLOBAL_ATTRIBUTE5 -- GLOBAL_ATTRIBUTE5
4183 ,ap_dists.GLOBAL_ATTRIBUTE6 -- GLOBAL_ATTRIBUTE6
4184 ,ap_dists.GLOBAL_ATTRIBUTE7 -- GLOBAL_ATTRIBUTE7
4185 ,ap_dists.GLOBAL_ATTRIBUTE8 -- GLOBAL_ATTRIBUTE8
4186 ,ap_dists.GLOBAL_ATTRIBUTE9 -- GLOBAL_ATTRIBUTE9
4187 ,ap_dists.GLOBAL_ATTRIBUTE10 -- GLOBAL_ATTRIBUTE10
4188 ,ap_dists.GLOBAL_ATTRIBUTE11 -- GLOBAL_ATTRIBUTE11
4189 ,ap_dists.GLOBAL_ATTRIBUTE12 -- GLOBAL_ATTRIBUTE12
4190 ,ap_dists.GLOBAL_ATTRIBUTE13 -- GLOBAL_ATTRIBUTE13
4191 ,ap_dists.GLOBAL_ATTRIBUTE14 -- GLOBAL_ATTRIBUTE14
4192 ,ap_dists.GLOBAL_ATTRIBUTE15 -- GLOBAL_ATTRIBUTE15
4193 ,ap_dists.GLOBAL_ATTRIBUTE16 -- GLOBAL_ATTRIBUTE16
4194 ,ap_dists.GLOBAL_ATTRIBUTE17 -- GLOBAL_ATTRIBUTE17
4195 ,ap_dists.GLOBAL_ATTRIBUTE18 -- GLOBAL_ATTRIBUTE18
4196 ,ap_dists.GLOBAL_ATTRIBUTE19 -- GLOBAL_ATTRIBUTE19
4197 ,ap_dists.GLOBAL_ATTRIBUTE20 -- GLOBAL_ATTRIBUTE20
4198 ,'Y' HISTORICAL_FLAG
4199 ,'N' OVERRIDDEN_FLAG
4200 ,'N' SELF_ASSESSED_FLAG
4201 ,'Y' TAX_APPORTIONMENT_FLAG
4202 ,DECODE(ap_dists.charge_applicable_to_dist_id,
4203 NULL, 'Y', 'N') TAX_ONLY_LINE_FLAG
4204 ,'N' INCLUSIVE_FLAG
4205 ,'N' MRC_TAX_DIST_FLAG
4206 ,'N' REC_TYPE_RULE_FLAG
4207 ,'N' NEW_REC_RATE_CODE_FLAG
4208 ,NVL(ap_dists.tax_recoverable_flag, 'N') RECOVERABLE_FLAG
4209 ,ap_dists.reversal_flag REVERSE_FLAG
4210 ,'N' REC_RATE_DET_RULE_FLAG
4211 ,'N' BACKWARD_COMPATIBILITY_FLAG
4212 ,DECODE(ap_dists.charge_applicable_to_dist_id, -- For Tax Only Line stamp
4213 NULL,'Y', -- Freeze_Flag always Y
4214 DECODE(ap_dists.accounting_event_id, -- Else if acct event id is
4215 NULL,'N','Y') -- Not Null then Y Else N
4216 ) FREEZE_FLAG
4217 ,DECODE(ap_dists.charge_applicable_to_dist_id, -- For Tax Only Line stamp
4218 NULL,'Y', -- Associated_Child_Frozen_Flag always Y
4219 DECODE(ap_dists.accounting_event_id, -- Else if acct event id is
4220 NULL,'N','Y') -- Not Null then Y Else N
4221 ) ASSOCIATED_CHILD_FROZEN_FLAG
4222 ,DECODE(ap_dists.posted_flag, 'Y', 'A', NULL) POSTING_FLAG
4223 ,NVL(lines1.accounting_date,
4224 NVL(inv.invoice_date, sysdate)) TAX_DATE
4225 ,NVL(lines1.accounting_date,
4226 NVL(inv.invoice_date, sysdate)) TAX_DETERMINE_DATE
4227 ,NVL(lines1.accounting_date,
4228 NVL(inv.invoice_date, sysdate)) TAX_POINT_DATE
4229 ,1 CREATED_BY
4230 ,SYSDATE CREATION_DATE
4231 --,NULL LAST_MANUAL_ENTRY
4232 ,SYSDATE LAST_UPDATE_DATE
4233 ,1 LAST_UPDATE_LOGIN
4234 ,1 LAST_UPDATED_BY
4235 ,1 OBJECT_VERSION_NUMBER
4236 ,ap_dists1.old_dist_line_number ORIG_AP_CHRG_DIST_NUM
4237 ,ap_dists1.old_distribution_id ORIG_AP_CHRG_DIST_ID
4238 ,ap_dists.old_dist_line_number ORIG_AP_TAX_DIST_NUM
4239 ,ap_dists.old_distribution_id ORIG_AP_TAX_DIST_ID
4240 ,'N' MULTIPLE_JURISDICTIONS_FLAG
4241 ,DECODE(ap_dists.posted_flag, 'Y', '111111111111111',
4242 'P', '111111111111111',
4243 '000000000000000') LEGAL_REPORTING_STATUS
4244 ,DECODE(lines.discarded_flag, 'Y', 'Y', 'N') CANCEL_FLAG
4245 ,NVL(rates.def_rec_settlement_option_code,
4246 taxes.def_rec_settlement_option_code) DEF_REC_SETTLEMENT_OPTION_CODE
4247 --,TAX_JURISDICTION_ID
4248 ,rates.tax_rate_id ACCOUNT_SOURCE_TAX_RATE_ID
4249 ,(SELECT tax_rate_id FROM zx_rates_b
4250 WHERE tax_rate_code = 'AD_HOC_RECOVERY'
4251 AND rate_type_code = 'RECOVERY'
4252 AND tax_regime_code = rates.tax_regime_code
4253 AND tax = rates.tax
4254 AND content_owner_id = ptp.party_tax_profile_id
4255 AND record_type_code = 'MIGRATED'
4256 AND tax_class = 'INPUT') RECOVERY_RATE_ID
4257 ,DECODE(lines.line_source,'MANUAL LINE ENTRY','Y','N') MANUALLY_ENTERED_FLAG --BUG7146063
4258 ,DECODE(lines.line_source,'MANUAL LINE ENTRY','TAX_AMOUNT',NULL) LAST_MANUAL_ENTRY --BUG7146063
4259 FROM ( select distinct other_doc_application_id,other_doc_trx_id from ZX_VALIDATION_ERRORS_GT ) zxvalerr, --Bug 5187701
4260 ap_invoices_all inv,
4261 fnd_currencies fnd_curr,
4262 -- fnd_document_sequences fds,
4263 ap_invoice_distributions_all ap_dists,
4264 ap_invoice_distributions_all ap_dists1,
4265 ap_invoice_lines_all lines1,
4266 ap_invoice_lines_all lines,
4267 zx_rates_b rates,
4268 zx_regimes_b regimes,
4269 zx_taxes_b taxes,
4270 zx_status_b status,
4271 zx_party_tax_profile ptp
4272 WHERE zxvalerr.other_doc_application_id = 200
4273 AND inv.invoice_id = zxvalerr.other_doc_trx_id
4274 AND fnd_curr.currency_code = inv.invoice_currency_code
4275 -- AND inv.doc_sequence_id = fds.doc_sequence_id(+)
4276 AND ap_dists.invoice_id = inv.invoice_id
4277 AND ap_dists.line_type_lookup_code IN ('REC_TAX','NONREC_TAX')
4278 AND NVL(ap_dists.historical_flag, 'N') = 'Y'
4279 -- AND ap_dists1.invoice_id = ap_dists.invoice_id
4280 AND ap_dists1.invoice_distribution_id = NVL(ap_dists.charge_applicable_to_dist_id,
4281 ap_dists.invoice_distribution_id)
4282 AND lines1.invoice_id = ap_dists1.invoice_id
4283 AND lines1.line_number = ap_dists1.invoice_line_number
4284 AND NVL(lines1.historical_flag, 'N') = 'Y'
4285 AND lines.invoice_id = ap_dists.invoice_id
4286 AND lines.line_number = ap_dists.invoice_line_number
4287 AND NVL(lines.historical_flag, 'N') = 'Y'
4288 AND rates.source_id(+) = ap_dists.tax_code_id
4289 AND regimes.tax_regime_code(+) = rates.tax_regime_code
4290 AND taxes.tax_regime_code(+) = rates.tax_regime_code
4291 AND taxes.tax(+) = rates.tax
4292 AND taxes.content_owner_id(+) = rates.content_owner_id
4293 AND status.tax_regime_code(+) = rates.tax_regime_code
4294 AND status.tax(+) = rates.tax
4295 AND status.tax_status_code(+) = rates.tax_status_code
4296 AND status.content_owner_id(+) = rates.content_owner_id
4297 -- AND NVL(taxes.effective_from,
4298 -- NVL(lines1.accounting_date, NVL(inv.invoice_date, sysdate)))
4299 -- <= NVL(lines1.accounting_date, NVL(inv.invoice_date, sysdate))
4300 -- AND (NVL(taxes.effective_to,
4301 -- NVL(lines1.accounting_date,
4302 -- NVL(inv.invoice_date, sysdate)) )
4303 -- >= NVL(lines1.accounting_date, NVL(inv.invoice_date, sysdate))
4304 -- OR taxes.effective_to IS NULL)
4305 AND ptp.party_type_code = 'OU'
4306 AND ptp.party_id = DECODE(l_multi_org_flag,'N', l_org_id, ap_dists.org_id));
4307
4308 IF (g_level_procedure >= g_current_runtime_level ) THEN
4309 FND_LOG.STRING(g_level_procedure,
4310 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_blk_ap.END',
4311 'ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_blk_ap(+)');
4312 END IF;
4313
4314 EXCEPTION
4315 WHEN OTHERS THEN
4316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4317 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4318 FND_LOG.STRING(g_level_unexpected,
4319 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_blk_ap',
4320 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
4321 FND_LOG.STRING(g_level_unexpected,
4322 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_blk_ap.END',
4323 'ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_blk_ap(-)');
4324 END IF;
4325
4326 END upgrade_trx_on_fly_blk_ap;
4327
4328 END ZX_ON_FLY_TRX_UPGRADE_AP_PKG;
4329