[Home] [Help]
PACKAGE BODY: APPS.ZX_ON_DEMAND_TRX_UPGRADE_PKG
Source
1 PACKAGE BODY ZX_ON_DEMAND_TRX_UPGRADE_PKG AS
2 /* $Header: zxmigtrxdemdpkgb.pls 120.48.12020000.2 2012/07/23 12:59:49 srajapar noship $ */
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_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
8
9 CONC_SUCCESS CONSTANT NUMBER := 0;
10 CONC_WARNING CONSTANT NUMBER := 1;
11 CONC_FAIL CONSTANT NUMBER := 2;
12
13 WORKER_FAIL EXCEPTION;
14
15 PROCEDURE zx_ar_trx_mig(
16 x_errbuf OUT NOCOPY VARCHAR2,
17 x_retcode OUT NOCOPY VARCHAR2,
18 p_start_rowid IN ROWID,
19 p_end_rowid IN ROWID,
20 p_org_id IN NUMBER,
21 p_multi_org_flag IN VARCHAR2,
22 p_inv_installed IN VARCHAR2,
23 p_worker_id IN NUMBER,
24 x_rows_processed OUT NOCOPY NUMBER);
25
26 PROCEDURE zx_ap_trx_mig (
27 x_errbuf OUT NOCOPY VARCHAR2,
28 x_retcode OUT NOCOPY VARCHAR2,
29 p_start_rowid IN ROWID,
30 p_end_rowid IN ROWID,
31 p_org_id IN NUMBER,
32 p_multi_org_flag IN VARCHAR2,
33 p_worker_id IN NUMBER,
34 x_rows_processed OUT NOCOPY NUMBER);
35
36 PROCEDURE zx_po_trx_mig (
37 x_errbuf OUT NOCOPY VARCHAR2,
38 x_retcode OUT NOCOPY VARCHAR2,
39 p_start_rowid IN ROWID,
40 p_end_rowid IN ROWID,
41 p_org_id IN NUMBER,
42 p_multi_org_flag IN VARCHAR2,
43 p_worker_id IN NUMBER,
44 x_rows_processed OUT NOCOPY NUMBER);
45
46
47 /**************************************************************/
48 -- Main Procedure
49
50 PROCEDURE ZX_TRX_UPDATE_MGR(
51 X_errbuf out NOCOPY varchar2,
52 X_retcode out NOCOPY varchar2,
53 X_batch_size in number,
54 X_Num_Workers in number,
55 p_application_id in fnd_application.application_id%type,
56 p_ledger_id in xla_upgrade_dates.ledger_id%type,
57 p_period_name in varchar2)
58 IS
59 l_update_name varchar2(30);
60
61 -- bug fix 5483850 begin
62 req_status number;
63 req_data varchar2(10);
64 strt_wrkr number;
65 submit_req boolean;
66 L_SUB_REQTAB fnd_concurrent.requests_tab_type;
67
68 TYPE WorkerList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
69 l_worker WorkerList;
70
71 l_result BOOLEAN;
72 l_phase VARCHAR2(500);
73 l_req_status VARCHAR2(500);
74 l_dev_phase VARCHAR2(500);
75 l_dev_status VARCHAR2(500);
76 l_message VARCHAR2(500);
77 l_worker_not_complete BOOLEAN;
78 l_worker_success VARCHAR2(1);
79 l_res BOOLEAN;
80 -- bug fix 5483850 end
81
82 BEGIN
83 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
84
85 --
86 -- Manager processing
87 --
88
89 IF g_level_procedure >= g_current_runtime_level THEN
90 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_TRX_UPDATE_MGR','ZX_TRX_UPDATE_MGR(+)');
91 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_TRX_UPDATE_MGR',' p_application_id: '||p_application_id);
92 END IF;
93
94 X_retcode := CONC_SUCCESS;
95
96 IF p_application_id = 222 then
97 l_update_name :='zxar_'||to_char(p_ledger_id)||p_period_name;
98 ELSIF p_application_id = 200 then
99 l_update_name :='zxap_'||to_char(p_ledger_id)||p_period_name;
100 ELSIF p_application_id = 201 then
101 l_update_name :='zxpo_'||to_char(p_ledger_id)||p_period_name;
102 END IF;
103
104
105 /* -- rewrote for bug fix 5483850
106
107 AD_CONC_UTILS_PKG.submit_subrequests(
108 X_errbuf=>X_errbuf,
109 X_retcode=>X_retcode,
110 X_WORKERCONC_APP_SHORTNAME=>'ZX',
111 X_WORKERCONC_PROGNAME=>'ZXONDEMANDWKR',
112 X_batch_size=>X_batch_size,
113 X_Num_Workers=>X_Num_Workers,
114 X_ARGUMENT4=>p_application_id,
115 X_argument5=>l_update_name);
116
117 IF g_level_statement >= g_current_runtime_level THEN
118 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_TRX_UPDATE_MGR','ZX_TRX_UPDATE_MGR(-)');
119 END IF;
120 */
121
122 -- rewrote the process to submit worker request for bug fix 5483850
123 -- When the program is run in on demand upgrade mode it is submitted from
124 -- the concurrent program and hence we need to spawn multiple child
125 -- workers
126
127 FOR i in 1..X_Num_Workers
128 LOOP
129
130 IF g_level_statement >= g_current_runtime_level THEN
131 FND_LOG.STRING(g_level_statement,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_TRX_UPDATE_MGR',
132 'Submitting concurrent request for worker '||i);
133 END IF;
134
135 l_worker(i) := fnd_request.submit_request(
136 APPLICATION=>'ZX',
137 PROGRAM=>'ZXONDEMANDWKR',
138 DESCRIPTION=> 'WRKR('||lpad(i, 2, '0')||')',
139 SUB_REQUEST=>FALSE,
140 --SUB_REQUEST=>TRUE, -- submit as child request of XLA
141 ARGUMENT1=>X_batch_size,
142 ARGUMENT2=>i,
143 ARGUMENT3=>X_Num_Workers,
144 ARGUMENT4=>p_application_id,
145 ARGUMENT5=>l_update_name );
146
147 IF l_worker(i) = 0 THEN
148 IF g_level_statement >= g_current_runtime_level THEN
149 FND_LOG.STRING(g_level_statement,
150 'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_TRX_UPDATE_MGR',
151 'Error submitting request #'||i);
152 FND_LOG.STRING(g_level_statement,
153 'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_TRX_UPDATE_MGR',
154 fnd_message.get);
155 END IF;
156 COMMIT;
157 ELSE
158 IF g_level_statement >= g_current_runtime_level THEN
159 FND_LOG.STRING(g_level_statement,
160 'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_TRX_UPDATE_MGR',
161 'Submitted request #'||i);
162 FND_LOG.STRING(g_level_statement,
163 'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_TRX_UPDATE_MGR',
164 'Request ID: ' ||l_worker(i));
165 END IF;
166 COMMIT;
167 END IF;
168
169 END LOOP;
170
171 COMMIT;
172
173
174 l_worker_not_complete := TRUE;
175 WHILE l_worker_not_complete LOOP
176 --dbms_lock.sleep(10);
177 IF g_level_statement >= g_current_runtime_level THEN
178 FND_LOG.STRING(g_level_statement,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_TRX_UPDATE_MGR',
179 'Inside Loop for checking the child request status');
180 END IF;
181
182 l_worker_not_complete := FALSE;
183 FOR i in 1..X_Num_Workers LOOP
184 l_res := FND_CONCURRENT.GET_REQUEST_STATUS
185 (l_worker(i),
186 NULL,
187 NULL,
188 l_phase,
189 l_req_status,
190 l_dev_phase,
191 l_dev_status,
192 l_message);
193
194 IF g_level_statement >= g_current_runtime_level THEN
195 FND_LOG.STRING(g_level_statement,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_TRX_UPDATE_MGR',
196 'l_dev_phase: '||l_dev_phase);
197 END IF;
198
199 IF l_dev_phase = 'COMPLETE' Then
200 --NULL;
201 IF l_dev_status NOT IN ('NORMAL', 'WARNING') THEN
202 l_worker_success := 'N';
203 END IF;
204 ELSE
205 IF g_level_statement >= g_current_runtime_level THEN
206 FND_LOG.STRING(g_level_statement,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_TRX_UPDATE_MGR',
207 'Loop once again');
208 END IF;
209 l_worker_not_complete := TRUE;
210 END IF;
211
212 --IF l_dev_status IN ('ERROR', 'TERMINATED', 'TERMINATING') THEN
213 -- l_worker_success := 'N';
214 --END IF;
215 END LOOP;
216 END LOOP;
217
218 /* If any subworkers have failed then raise an error */
219 IF l_worker_success = 'N' THEN
220 RAISE WORKER_FAIL;
221 END IF;
222
223 COMMIT;
224
225 IF g_level_procedure >= g_current_runtime_level THEN
226 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_TRX_UPDATE_MGR','ZX_TRX_UPDATE_MGR(-)');
227 END IF;
228 EXCEPTION
229 WHEN OTHERS THEN
230 X_retcode := CONC_FAIL;
231 IF g_level_unexpected >= g_current_runtime_level THEN
232 FND_LOG.STRING(g_level_unexpected,
233 'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_TRX_UPDATE_MGR',
234 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80) );
235 END IF;
236 raise;
237
238 END ZX_TRX_UPDATE_MGR;
239
240 -- Sub Worker
241
242 PROCEDURE ZX_TRX_UPDATE_WKR(
243 X_errbuf out NOCOPY varchar2,
244 X_retcode out NOCOPY varchar2,
245 X_batch_size in number,
246 X_Worker_Id in number,
247 X_Num_Workers in number,
248 p_application_id in fnd_application.application_id%type,
249 p_script_name in varchar2)
250 IS
251
252 l_worker_id number;
253 l_product varchar2(30) ;
254 l_table_name varchar2(30) := 'dual';
255 l_status varchar2(30);
256 l_industry varchar2(30);
257 l_retstatus boolean;
258 l_table_owner varchar2(30);
259 l_any_rows_to_process boolean;
260
261 l_start_rowid rowid;
262 l_end_rowid rowid;
263 l_rows_processed number; -- for IN parameter
264 x_rows_processed number; -- for OUT parameter
265
266 l_multi_org_flag VARCHAR2(1);
267 l_org_id NUMBER;
268 l_inv_installed VARCHAR2(1);
269 l_inv_flag VARCHAR2(1);
270 l_fnd_return BOOLEAN;
271 l_temp BOOLEAN;
272
273 BEGIN
274
275 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
276
277 --
278 -- get schema name of the table for ROWID range processing
279 --
280 IF g_level_procedure >= g_current_runtime_level then
281 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_TRX_UPDATE_WKR','ZX_TRX_UPDATE_WKR(+)');
282 END IF;
283
284 X_retcode := CONC_SUCCESS;
285
286 SELECT NVL(multi_org_flag, 'N')
287 INTO l_multi_org_flag
288 FROM fnd_product_groups;
289
290 -- for single org environment, get value of org_id from profile
291 IF l_multi_org_flag = 'N' THEN
292 fnd_profile.get('ORG_ID',l_org_id);
293 IF l_org_id is NULL THEN
294 l_org_id := -99;
295 END IF;
296 END IF;
297
298
299 IF g_level_statement >= g_current_runtime_level THEN
300 FND_LOG.STRING(g_level_statement,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_TRX_UPDATE_WKR','Worker: '||X_Worker_Id||' l_multi_org_flag is ' || l_multi_org_flag);
301 FND_LOG.STRING(g_level_statement,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_TRX_UPDATE_WKR','Worker: '||X_Worker_Id||' l_org_id is ' || l_org_id );
302
303 END IF;
304
305 l_fnd_return := FND_INSTALLATION.GET(401,401, l_inv_flag, l_industry);
306
307 IF (l_inv_flag = 'I') THEN
308 l_inv_installed := 'Y';
309 ELSE
310 l_inv_installed := 'N';
311 END IF;
312
313 SELECT application_short_name
314 INTO l_product
315 FROM fnd_application
316 WHERE application_id = p_application_id;
317
318 l_retstatus := fnd_installation.get_app_info(
319 l_product, l_status, l_industry, l_table_owner);
320
321 IF ((l_retstatus = FALSE)
322 OR
323 (l_table_owner is null))
324 THEN
325 RAISE_APPLICATION_ERROR(-20001,
326 'Cannot get schema name for product : '||l_product);
327 END IF;
328
329
330 IF g_level_statement >= g_current_runtime_level then
331 FND_LOG.STRING(g_level_statement,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_TRX_UPDATE_WKR','Worker: '||X_Worker_Id||' X_Worker_Id is ' || X_Worker_Id);
332 FND_LOG.STRING(g_level_statement,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_TRX_UPDATE_WKR','Worker: '||X_Worker_Id||' X_Num_Workers is ' || X_Num_Workers );
333 END IF;
334
335 BEGIN
336
337 IF p_application_id = 222 then
338 l_table_name :='RA_CUSTOMER_TRX_ALL';
339 ELSIF p_application_id = 200 then
340 l_table_name :='AP_INVOICES_ALL';
341 ELSIF p_application_id = 201 then
342 l_table_name :='PO_HEADERS_ALL';
343 END IF;
344
345 ad_parallel_updates_pkg.initialize_rowid_range(
346 ad_parallel_updates_pkg.ROWID_RANGE,
347 l_table_owner,
348 l_table_name,
349 p_script_name,
350 X_worker_id,
351 X_num_workers,
352 X_batch_size, 0);
353
354 ad_parallel_updates_pkg.get_rowid_range(
355 l_start_rowid,
356 l_end_rowid,
357 l_any_rows_to_process,
358 X_batch_size,
359 TRUE);
360
361 WHILE (l_any_rows_to_process = TRUE)
362 LOOP
363
364 IF p_application_id = 222 THEN
365
366 zx_ar_trx_mig(
367 x_errbuf,
368 x_retcode,
369 l_start_rowid,
370 l_end_rowid,
371 l_org_id,
372 l_multi_org_flag,
373 l_inv_installed,
374 X_Worker_Id,
375 x_rows_processed);
376
377 ELSIF p_application_id = 200 THEN
378
379 zx_ap_trx_mig(
380 x_errbuf,
381 x_retcode,
382 l_start_rowid,
383 l_end_rowid,
384 l_org_id,
385 l_multi_org_flag,
386 X_Worker_Id,
387 x_rows_processed);
388
389 ELSIF p_application_id = 201 THEN
390
391 zx_po_trx_mig(
392 x_errbuf,
393 x_retcode,
394 l_start_rowid,
395 l_end_rowid,
396 l_org_id,
397 l_multi_org_flag,
398 X_Worker_Id,
399 x_rows_processed);
400 END IF;
401
402
403 l_rows_processed := x_rows_processed ;
404
405 ad_parallel_updates_pkg.processed_rowid_range(
406 l_rows_processed,
407 l_end_rowid);
408
409 COMMIT;
410
411 ad_parallel_updates_pkg.get_rowid_range(
412 l_start_rowid,
413 l_end_rowid,
414 l_any_rows_to_process,
415 X_batch_size,
416 FALSE);
417
418 END LOOP;
419
420 /* X_retcode := CONC_SUCCESS;
421 l_temp := fnd_concurrent.set_completion_status
422 (status => 'NORMAL'
423 ,message => NULL);
424 */
425 EXCEPTION
426 WHEN OTHERS THEN
427 X_retcode := CONC_FAIL;
428 raise;
429 END;
430
431 IF g_level_procedure >= g_current_runtime_level then
432 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_TRX_UPDATE_WKR','ZX_TRX_UPDATE_WKR(-)');
433 END IF;
434
435 EXCEPTION
436 WHEN OTHERS THEN
437 X_retcode := CONC_FAIL;
438 IF g_level_unexpected >= g_current_runtime_level THEN
439 FND_LOG.STRING(g_level_unexpected,
440 'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_TRX_UPDATE_WKR',
441 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80) );
442 END IF;
443 raise;
444
445 END ZX_TRX_UPDATE_WKR;
446
447 /**************************************************************/
448
449 PROCEDURE zx_ar_trx_mig (x_errbuf OUT NOCOPY VARCHAR2,
450 x_retcode OUT NOCOPY VARCHAR2,
451 p_start_rowid IN ROWID,
452 p_end_rowid IN ROWID,
453 p_org_id IN NUMBER,
454 p_multi_org_flag IN VARCHAR2,
455 p_inv_installed IN VARCHAR2,
456 p_worker_id IN NUMBER,
457 x_rows_processed OUT NOCOPY NUMBER)
458
459 IS
460 l_multi_org_flag VARCHAR2(1);
461 l_org_id NUMBER;
462 l_inv_installed VARCHAR2(1);
463 BEGIN
464 l_multi_org_flag := p_multi_org_flag;
465 l_org_id := p_org_id;
466 l_inv_installed := p_inv_installed;
467
468
469 IF g_level_procedure >= g_current_runtime_level then
470 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_AR_TRX_MIG','Worker: '||p_worker_id||' zx_ar_trx_mig (+)' );
471 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_AR_TRX_MIG','Worker: '||p_worker_id||' p_start_rowid is ' || p_start_rowid );
472 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_AR_TRX_MIG','Worker: '||p_worker_id||' p_end_rowid is ' || p_end_rowid );
473 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_AR_TRX_MIG','Worker: '||p_worker_id||' p_org_id is ' || p_org_id );
474 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_AR_TRX_MIG','Worker: '||p_worker_id||' p_multi_org_flag is ' || p_multi_org_flag );
475 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_AR_TRX_MIG','Worker: '||p_worker_id||' p_inv_installed is ' || p_inv_installed );
476 END IF;
477
478 x_retcode := CONC_SUCCESS;
479
480 /* Insert All Taxable Lines into ZX_LINES_DET_FACTORS. Incase there are No taxable lines,
481 (link_to_cust_trx_line_id is null, hence insert dummy lines in ZX_LINES_DET_FACTORS with
482 trx_line_id = -9999) */
483
484 INSERT ALL
485 WHEN trx_line_type IN ('LINE' ,'CB') THEN
486 INTO ZX_LINES_DET_FACTORS(
487 INTERNAL_ORGANIZATION_ID
488 ,APPLICATION_ID
489 ,ENTITY_CODE
490 ,EVENT_CLASS_CODE
491 ,EVENT_CLASS_MAPPING_ID
492 ,EVENT_TYPE_CODE
493 ,DOC_EVENT_STATUS
494 ,LINE_LEVEL_ACTION
495 ,TRX_ID
496 ,TRX_LINE_ID
497 ,TRX_LEVEL_TYPE
498 ,TRX_DATE
499 --,TRX_DOC_REVISION
500 ,LEDGER_ID
501 ,TRX_CURRENCY_CODE
502 ,CURRENCY_CONVERSION_DATE
503 ,CURRENCY_CONVERSION_RATE
504 ,CURRENCY_CONVERSION_TYPE
505 ,MINIMUM_ACCOUNTABLE_UNIT
506 ,PRECISION
507 ,LEGAL_ENTITY_ID
508 --,ESTABLISHMENT_ID
509 ,RECEIVABLES_TRX_TYPE_ID
510 ,DEFAULT_TAXATION_COUNTRY
511 ,TRX_NUMBER
512 ,TRX_LINE_NUMBER
513 ,TRX_LINE_DESCRIPTION
514 --,TRX_DESCRIPTION
515 --,TRX_COMMUNICATED_DATE
516 ,BATCH_SOURCE_ID
517 ,BATCH_SOURCE_NAME
518 ,DOC_SEQ_ID
519 ,DOC_SEQ_NAME
520 ,DOC_SEQ_VALUE
521 ,TRX_DUE_DATE
522 ,TRX_TYPE_DESCRIPTION
523 ,DOCUMENT_SUB_TYPE
524 --,SUPPLIER_TAX_INVOICE_NUMBER
525 --,SUPPLIER_TAX_INVOICE_DATE
526 --,SUPPLIER_EXCHANGE_RATE
527 ,TAX_INVOICE_DATE
528 ,TAX_INVOICE_NUMBER
529 ,FIRST_PTY_ORG_ID
530 ,TAX_EVENT_CLASS_CODE
531 ,TAX_EVENT_TYPE_CODE
532 --,LINE_INTENDED_USE
533 ,TRX_LINE_TYPE
534 --,TRX_SHIPPING_DATE
535 --,TRX_RECEIPT_DATE
536 --,TRX_SIC_CODE
537 ,FOB_POINT
538 ,TRX_WAYBILL_NUMBER
539 ,PRODUCT_ID
540 ,PRODUCT_FISC_CLASSIFICATION
541 ,PRODUCT_ORG_ID
542 ,UOM_CODE
543 --,PRODUCT_TYPE
544 --,PRODUCT_CODE
545 ,PRODUCT_CATEGORY
546 ,PRODUCT_DESCRIPTION
547 ,USER_DEFINED_FISC_CLASS
548 ,LINE_AMT
549 ,TRX_LINE_QUANTITY
550 --,CASH_DISCOUNT
551 --,VOLUME_DISCOUNT
552 --,TRADING_DISCOUNT
553 --,TRANSFER_CHARGE
554 --,TRANSPORTATION_CHARGE
555 --,INSURANCE_CHARGE
556 --,OTHER_CHARGE
557 --,ASSESSABLE_VALUE
558 --,ASSET_FLAG
559 --,ASSET_NUMBER
560 ,ASSET_ACCUM_DEPRECIATION
561 --,ASSET_TYPE
562 ,ASSET_COST
563 ,RELATED_DOC_APPLICATION_ID
564 --,RELATED_DOC_ENTITY_CODE
565 --,RELATED_DOC_EVENT_CLASS_CODE
566 ,RELATED_DOC_TRX_ID
567 --,RELATED_DOC_NUMBER
568 --,RELATED_DOC_DATE
569 ,ADJUSTED_DOC_APPLICATION_ID
570 ,ADJUSTED_DOC_ENTITY_CODE
571 ,ADJUSTED_DOC_EVENT_CLASS_CODE
572 ,ADJUSTED_DOC_TRX_ID
573 ,ADJUSTED_DOC_LINE_ID
574 ,ADJUSTED_DOC_NUMBER
575 ,ADJUSTED_DOC_DATE
576 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
577 --,REF_DOC_APPLICATION_ID
578 --,REF_DOC_ENTITY_CODE
579 --,REF_DOC_EVENT_CLASS_CODE
580 --,REF_DOC_TRX_ID
581 --,REF_DOC_LINE_ID
582 --,REF_DOC_LINE_QUANTITY
583 --,REF_DOC_TRX_LEVEL_TYPE
584 ,TRX_BUSINESS_CATEGORY
585 ,EXEMPT_CERTIFICATE_NUMBER
586 --,EXEMPT_REASON
587 ,EXEMPTION_CONTROL_FLAG
588 ,EXEMPT_REASON_CODE
589 ,HISTORICAL_FLAG
590 ,TRX_LINE_GL_DATE
591 ,LINE_AMT_INCLUDES_TAX_FLAG
592 --,ACCOUNT_CCID
593 --,ACCOUNT_STRING
594 --,SHIP_TO_LOCATION_ID
595 --,SHIP_FROM_LOCATION_ID
596 --,POA_LOCATION_ID
597 --,POO_LOCATION_ID
598 --,BILL_TO_LOCATION_ID
599 --,BILL_FROM_LOCATION_ID
600 --,PAYING_LOCATION_ID
601 --,OWN_HQ_LOCATION_ID
602 --,TRADING_HQ_LOCATION_ID
603 --,POC_LOCATION_ID
604 --,POI_LOCATION_ID
605 --,POD_LOCATION_ID
606 --,TITLE_TRANSFER_LOCATION_ID
607 ,CTRL_HDR_TX_APPL_FLAG
608 --,CTRL_TOTAL_LINE_TX_AMT
609 --,CTRL_TOTAL_HDR_TX_AMT
610 ,LINE_CLASS
611 ,TRX_LINE_DATE
612 --,INPUT_TAX_CLASSIFICATION_CODE
613 ,OUTPUT_TAX_CLASSIFICATION_CODE
614 --,INTERNAL_ORG_LOCATION_ID
615 --,PORT_OF_ENTRY_CODE
616 ,TAX_REPORTING_FLAG
617 ,TAX_AMT_INCLUDED_FLAG
618 ,COMPOUNDING_TAX_FLAG
619 --,EVENT_ID
620 ,THRESHOLD_INDICATOR_FLAG
621 --,PROVNL_TAX_DETERMINATION_DATE
622 ,UNIT_PRICE
623 ,SHIP_TO_CUST_ACCT_SITE_USE_ID
624 ,BILL_TO_CUST_ACCT_SITE_USE_ID
625 ,TRX_BATCH_ID
626 --,START_EXPENSE_DATE
627 --,SOURCE_APPLICATION_ID
628 --,SOURCE_ENTITY_CODE
629 --,SOURCE_EVENT_CLASS_CODE
630 --,SOURCE_TRX_ID
631 --,SOURCE_LINE_ID
632 --,SOURCE_TRX_LEVEL_TYPE
633 ,RECORD_TYPE_CODE
634 ,INCLUSIVE_TAX_OVERRIDE_FLAG
635 ,TAX_PROCESSING_COMPLETED_FLAG
636 ,OBJECT_VERSION_NUMBER
637 ,APPLICATION_DOC_STATUS
638 ,USER_UPD_DET_FACTORS_FLAG
639 --,SOURCE_TAX_LINE_ID
640 --,REVERSED_APPLN_ID
641 --,REVERSED_ENTITY_CODE
642 --,REVERSED_EVNT_CLS_CODE
643 --,REVERSED_TRX_ID
644 --,REVERSED_TRX_LEVEL_TYPE
645 --,REVERSED_TRX_LINE_ID
646 --,TAX_CALCULATION_DONE_FLAG
647 ,PARTNER_MIGRATED_FLAG
648 ,SHIP_THIRD_PTY_ACCT_SITE_ID
649 ,BILL_THIRD_PTY_ACCT_SITE_ID
650 ,SHIP_THIRD_PTY_ACCT_ID
651 ,BILL_THIRD_PTY_ACCT_ID
652 --,INTERFACE_ENTITY_CODE
653 --,INTERFACE_LINE_ID
654 --,HISTORICAL_TAX_CODE_ID
655 --,ICX_SESSION_ID
656 --,TRX_LINE_CURRENCY_CODE
657 --,TRX_LINE_CURRENCY_CONV_RATE
658 --,TRX_LINE_CURRENCY_CONV_DATE
659 --,TRX_LINE_PRECISION
660 --,TRX_LINE_MAU
661 --,TRX_LINE_CURRENCY_CONV_TYPE
662 ,CREATION_DATE
663 ,CREATED_BY
664 ,LAST_UPDATE_DATE
665 ,LAST_UPDATED_BY
666 ,LAST_UPDATE_LOGIN
667 )
668 VALUES (
669 INTERNAL_ORGANIZATION_ID
670 ,APPLICATION_ID
671 ,ENTITY_CODE
672 ,EVENT_CLASS_CODE
673 ,EVENT_CLASS_MAPPING_ID
674 ,EVENT_TYPE_CODE
675 ,DOC_EVENT_STATUS
676 ,LINE_LEVEL_ACTION
677 ,TRX_ID
678 ,TRX_LINE_ID
679 ,TRX_LEVEL_TYPE
680 ,TRX_DATE
681 --,TRX_DOC_REVISION
682 ,LEDGER_ID
683 ,TRX_CURRENCY_CODE
684 ,CURRENCY_CONVERSION_DATE
685 ,CURRENCY_CONVERSION_RATE
686 ,CURRENCY_CONVERSION_TYPE
687 ,MINIMUM_ACCOUNTABLE_UNIT
688 ,PRECISION
689 ,LEGAL_ENTITY_ID
690 --,ESTABLISHMENT_ID
691 ,RECEIVABLES_TRX_TYPE_ID
692 ,DEFAULT_TAXATION_COUNTRY
693 ,TRX_NUMBER
694 ,TRX_LINE_NUMBER
695 ,TRX_LINE_DESCRIPTION
696 --,TRX_DESCRIPTION
697 --,TRX_COMMUNICATED_DATE
698 ,BATCH_SOURCE_ID
699 ,BATCH_SOURCE_NAME
700 ,DOC_SEQ_ID
701 ,DOC_SEQ_NAME
702 ,DOC_SEQ_VALUE
703 ,TRX_DUE_DATE
704 ,TRX_TYPE_DESCRIPTION
705 ,DOCUMENT_SUB_TYPE
706 --,SUPPLIER_TAX_INVOICE_NUMBER
707 --,SUPPLIER_TAX_INVOICE_DATE
708 --,SUPPLIER_EXCHANGE_RATE
709 ,TAX_INVOICE_DATE
710 ,TAX_INVOICE_NUMBER
711 ,FIRST_PTY_ORG_ID
712 ,TAX_EVENT_CLASS_CODE
713 ,TAX_EVENT_TYPE_CODE
714 --,LINE_INTENDED_USE
715 ,TRX_LINE_TYPE
716 --,TRX_SHIPPING_DATE
717 --,TRX_RECEIPT_DATE
718 --,TRX_SIC_CODE
719 ,FOB_POINT
720 ,TRX_WAYBILL_NUMBER
721 ,PRODUCT_ID
722 ,PRODUCT_FISC_CLASSIFICATION
723 ,PRODUCT_ORG_ID
724 ,UOM_CODE
725 --,PRODUCT_TYPE
726 --,PRODUCT_CODE
727 ,PRODUCT_CATEGORY
728 ,PRODUCT_DESCRIPTION
729 ,USER_DEFINED_FISC_CLASS
730 ,LINE_AMT
731 ,TRX_LINE_QUANTITY
732 --,CASH_DISCOUNT
733 --,VOLUME_DISCOUNT
734 --,TRADING_DISCOUNT
735 --,TRANSFER_CHARGE
736 --,TRANSPORTATION_CHARGE
737 --,INSURANCE_CHARGE
738 --,OTHER_CHARGE
739 --,ASSESSABLE_VALUE
740 --,ASSET_FLAG
741 --,ASSET_NUMBER
742 ,ASSET_ACCUM_DEPRECIATION
743 --,ASSET_TYPE
744 ,ASSET_COST
745 ,RELATED_DOC_APPLICATION_ID
746 --,RELATED_DOC_ENTITY_CODE
747 --,RELATED_DOC_EVENT_CLASS_CODE
748 ,RELATED_DOC_TRX_ID
749 --,RELATED_DOC_NUMBER
750 --,RELATED_DOC_DATE
751 ,ADJUSTED_DOC_APPLICATION_ID
752 ,ADJUSTED_DOC_ENTITY_CODE
753 ,ADJUSTED_DOC_EVENT_CLASS_CODE
754 ,ADJUSTED_DOC_TRX_ID
755 ,ADJUSTED_DOC_LINE_ID
756 ,ADJUSTED_DOC_NUMBER
757 ,ADJUSTED_DOC_DATE
758 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
759 --,REF_DOC_APPLICATION_ID
760 --,REF_DOC_ENTITY_CODE
761 --,REF_DOC_EVENT_CLASS_CODE
762 --,REF_DOC_TRX_ID
763 --,REF_DOC_LINE_ID
764 --,REF_DOC_LINE_QUANTITY
765 --,REF_DOC_TRX_LEVEL_TYPE
766 ,TRX_BUSINESS_CATEGORY
767 ,EXEMPT_CERTIFICATE_NUMBER
768 --,EXEMPT_REASON
769 ,EXEMPTION_CONTROL_FLAG
770 ,EXEMPT_REASON_CODE
771 ,'Y' --HISTORICAL_FLAG
772 ,TRX_LINE_GL_DATE
773 ,'N' --LINE_AMT_INCLUDES_TAX_FLAG
774 --,ACCOUNT_CCID
775 --,ACCOUNT_STRING
776 --,SHIP_TO_LOCATION_ID
777 --,SHIP_FROM_LOCATION_ID
778 --,POA_LOCATION_ID
779 --,POO_LOCATION_ID
780 --,BILL_TO_LOCATION_ID
781 --,BILL_FROM_LOCATION_ID
782 --,PAYING_LOCATION_ID
783 --,OWN_HQ_LOCATION_ID
784 --,TRADING_HQ_LOCATION_ID
785 --,POC_LOCATION_ID
786 --,POI_LOCATION_ID
787 --,POD_LOCATION_ID
788 --,TITLE_TRANSFER_LOCATION_ID
789 ,'N' --CTRL_HDR_TX_APPL_FLAG
790 --,CTRL_TOTAL_LINE_TX_AMT
791 --,CTRL_TOTAL_HDR_TX_AMT
792 ,LINE_CLASS
793 ,TRX_LINE_DATE
794 --,INPUT_TAX_CLASSIFICATION_CODE
795 ,OUTPUT_TAX_CLASSIFICATION_CODE
796 --,INTERNAL_ORG_LOCATION_ID
797 --,PORT_OF_ENTRY_CODE
798 ,'Y' --TAX_REPORTING_FLAG
799 ,'N' --TAX_AMT_INCLUDED_FLAG
800 ,'N' --COMPOUNDING_TAX_FLAG
801 --,EVENT_ID
802 ,'N' --THRESHOLD_INDICATOR_FLAG
803 --,PROVNL_TAX_DETERMINATION_DATE
804 ,UNIT_PRICE
805 ,SHIP_TO_CUST_ACCT_SITE_USE_ID
806 ,BILL_TO_CUST_ACCT_SITE_USE_ID
807 ,TRX_BATCH_ID
808 --,START_EXPENSE_DATE
809 --,SOURCE_APPLICATION_ID
810 --,SOURCE_ENTITY_CODE
811 --,SOURCE_EVENT_CLASS_CODE
812 --,SOURCE_TRX_ID
813 --,SOURCE_LINE_ID
814 --,SOURCE_TRX_LEVEL_TYPE
815 ,'MIGRATED' --RECORD_TYPE_CODE
816 ,'N' --INCLUSIVE_TAX_OVERRIDE_FLAG
817 ,'N' --TAX_PROCESSING_COMPLETED_FLAG
818 ,OBJECT_VERSION_NUMBER
819 ,APPLICATION_DOC_STATUS
820 ,'N' --USER_UPD_DET_FACTORS_FLAG
821 --,SOURCE_TAX_LINE_ID
822 --,REVERSED_APPLN_ID
823 --,REVERSED_ENTITY_CODE
824 --,REVERSED_EVNT_CLS_CODE
825 --,REVERSED_TRX_ID
826 --,REVERSED_TRX_LEVEL_TYPE
827 --,REVERSED_TRX_LINE_ID
828 --,TAX_CALCULATION_DONE_FLAG
829 ,PARTNER_MIGRATED_FLAG
830 ,SHIP_THIRD_PTY_ACCT_SITE_ID
831 ,BILL_THIRD_PTY_ACCT_SITE_ID
832 ,SHIP_THIRD_PTY_ACCT_ID
833 ,BILL_THIRD_PTY_ACCT_ID
834 --,INTERFACE_ENTITY_CODE
835 --,INTERFACE_LINE_ID
836 --,HISTORICAL_TAX_CODE_ID
837 --,ICX_SESSION_ID
838 --,TRX_LINE_CURRENCY_CODE
839 --,TRX_LINE_CURRENCY_CONV_RATE
840 --,TRX_LINE_CURRENCY_CONV_DATE
841 --,TRX_LINE_PRECISION
842 --,TRX_LINE_MAU
843 --,TRX_LINE_CURRENCY_CONV_TYPE
844 ,CREATION_DATE
845 ,CREATED_BY
846 ,LAST_UPDATE_DATE
847 ,LAST_UPDATED_BY
848 ,LAST_UPDATE_LOGIN
849 )
850 WHEN (trx_line_type = 'TAX') THEN
851 INTO ZX_LINES (
852 TAX_LINE_ID
853 ,INTERNAL_ORGANIZATION_ID
854 ,APPLICATION_ID
855 ,ENTITY_CODE
856 ,EVENT_CLASS_CODE
857 ,EVENT_TYPE_CODE
858 ,TRX_ID
859 ,TRX_LINE_ID
860 ,TRX_LEVEL_TYPE
861 ,TRX_LINE_NUMBER
862 ,DOC_EVENT_STATUS
863 ,TAX_EVENT_CLASS_CODE
864 ,TAX_EVENT_TYPE_CODE
865 ,TAX_LINE_NUMBER
866 ,CONTENT_OWNER_ID
867 ,TAX_REGIME_ID
868 ,TAX_REGIME_CODE
869 ,TAX_ID
870 ,TAX
871 ,TAX_STATUS_ID
872 ,TAX_STATUS_CODE
873 ,TAX_RATE_ID
874 ,TAX_RATE_CODE
875 ,TAX_RATE
876 ,TAX_RATE_TYPE
877 ,TAX_APPORTIONMENT_LINE_NUMBER
878 ,MRC_TAX_LINE_FLAG
879 ,LEDGER_ID
880 --,ESTABLISHMENT_ID
881 ,LEGAL_ENTITY_ID
882 --,LEGAL_ENTITY_TAX_REG_NUMBER
883 --,HQ_ESTB_REG_NUMBER
884 --,HQ_ESTB_PARTY_TAX_PROF_ID
885 ,CURRENCY_CONVERSION_DATE
886 ,CURRENCY_CONVERSION_TYPE
887 ,CURRENCY_CONVERSION_RATE
888 --,TAX_CURRENCY_CONVERSION_DATE
889 --,TAX_CURRENCY_CONVERSION_TYPE
890 --,TAX_CURRENCY_CONVERSION_RATE
891 ,TRX_CURRENCY_CODE
892 ,MINIMUM_ACCOUNTABLE_UNIT
893 ,PRECISION
894 ,TRX_NUMBER
895 ,TRX_DATE
896 ,UNIT_PRICE
897 ,LINE_AMT
898 ,TRX_LINE_QUANTITY
899 ,TAX_BASE_MODIFIER_RATE
900 --,REF_DOC_APPLICATION_ID
901 --,REF_DOC_ENTITY_CODE
902 --,REF_DOC_EVENT_CLASS_CODE
903 --,REF_DOC_TRX_ID
904 --,REF_DOC_LINE_ID
905 --,REF_DOC_LINE_QUANTITY
906 --,REF_DOC_TRX_LEVEL_TYPE
907 --,OTHER_DOC_LINE_AMT
908 --,OTHER_DOC_LINE_TAX_AMT
909 --,OTHER_DOC_LINE_TAXABLE_AMT
910 ,UNROUNDED_TAXABLE_AMT
911 ,UNROUNDED_TAX_AMT
912 ,RELATED_DOC_APPLICATION_ID
913 --,RELATED_DOC_ENTITY_CODE
914 --,RELATED_DOC_EVENT_CLASS_CODE
915 ,RELATED_DOC_TRX_ID
916 --,RELATED_DOC_NUMBER
917 --,RELATED_DOC_DATE
918 --,RELATED_DOC_TRX_LEVEL_TYPE
919 ,ADJUSTED_DOC_APPLICATION_ID
920 ,ADJUSTED_DOC_ENTITY_CODE
921 ,ADJUSTED_DOC_EVENT_CLASS_CODE
922 ,ADJUSTED_DOC_TRX_ID
923 ,ADJUSTED_DOC_LINE_ID
924 ,ADJUSTED_DOC_NUMBER
925 ,ADJUSTED_DOC_DATE
926 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
927 --,SUMMARY_TAX_LINE_ID
928 --,OFFSET_LINK_TO_TAX_LINE_ID
929 ,OFFSET_FLAG
930 ,PROCESS_FOR_RECOVERY_FLAG
931 --,TAX_JURISDICTION_ID
932 --,TAX_JURISDICTION_CODE
933 --,PLACE_OF_SUPPLY
934 ,PLACE_OF_SUPPLY_TYPE_CODE
935 --,PLACE_OF_SUPPLY_RESULT_ID
936 --,TAX_DATE_RULE_ID
937 ,TAX_DATE
938 ,TAX_DETERMINE_DATE
939 ,TAX_POINT_DATE
940 ,TRX_LINE_DATE
941 ,TAX_TYPE_CODE
942 --,TAX_CODE
943 --,TAX_REGISTRATION_ID
944 --,TAX_REGISTRATION_NUMBER
945 --,REGISTRATION_PARTY_TYPE
946 ,ROUNDING_LEVEL_CODE
947 ,ROUNDING_RULE_CODE
948 --,ROUNDING_LVL_PARTY_TAX_PROF_ID
949 --,ROUNDING_LVL_PARTY_TYPE
950 ,COMPOUNDING_TAX_FLAG
951 --,ORIG_TAX_STATUS_ID
952 --,ORIG_TAX_STATUS_CODE
953 --,ORIG_TAX_RATE_ID
954 --,ORIG_TAX_RATE_CODE
955 --,ORIG_TAX_RATE
956 --,ORIG_TAX_JURISDICTION_ID
957 --,ORIG_TAX_JURISDICTION_CODE
958 --,ORIG_TAX_AMT_INCLUDED_FLAG
959 --,ORIG_SELF_ASSESSED_FLAG
960 ,TAX_CURRENCY_CODE
961 ,TAX_AMT
962 ,TAX_AMT_TAX_CURR
963 ,TAX_AMT_FUNCL_CURR
964 ,TAXABLE_AMT
965 ,TAXABLE_AMT_TAX_CURR
966 ,TAXABLE_AMT_FUNCL_CURR
967 --,ORIG_TAXABLE_AMT
968 --,ORIG_TAXABLE_AMT_TAX_CURR
969 ,CAL_TAX_AMT
970 ,CAL_TAX_AMT_TAX_CURR
971 ,CAL_TAX_AMT_FUNCL_CURR
972 --,ORIG_TAX_AMT
973 --,ORIG_TAX_AMT_TAX_CURR
974 --,REC_TAX_AMT
975 --,REC_TAX_AMT_TAX_CURR
976 --,REC_TAX_AMT_FUNCL_CURR
977 --,NREC_TAX_AMT
978 --,NREC_TAX_AMT_TAX_CURR
979 --,NREC_TAX_AMT_FUNCL_CURR
980 ,TAX_EXEMPTION_ID
981 --,TAX_RATE_BEFORE_EXEMPTION
982 --,TAX_RATE_NAME_BEFORE_EXEMPTION
983 --,EXEMPT_RATE_MODIFIER
984 ,EXEMPT_CERTIFICATE_NUMBER
985 --,EXEMPT_REASON
986 ,EXEMPT_REASON_CODE
987 ,TAX_EXCEPTION_ID
988 ,TAX_RATE_BEFORE_EXCEPTION
989 --,TAX_RATE_NAME_BEFORE_EXCEPTION
990 --,EXCEPTION_RATE
991 ,TAX_APPORTIONMENT_FLAG
992 ,HISTORICAL_FLAG
993 ,TAXABLE_BASIS_FORMULA
994 ,TAX_CALCULATION_FORMULA
995 ,CANCEL_FLAG
996 ,PURGE_FLAG
997 ,DELETE_FLAG
998 ,TAX_AMT_INCLUDED_FLAG
999 ,SELF_ASSESSED_FLAG
1000 ,OVERRIDDEN_FLAG
1001 ,MANUALLY_ENTERED_FLAG
1002 ,REPORTING_ONLY_FLAG
1003 ,FREEZE_UNTIL_OVERRIDDEN_FLAG
1004 ,COPIED_FROM_OTHER_DOC_FLAG
1005 ,RECALC_REQUIRED_FLAG
1006 ,SETTLEMENT_FLAG
1007 ,ITEM_DIST_CHANGED_FLAG
1008 ,ASSOCIATED_CHILD_FROZEN_FLAG
1009 ,TAX_ONLY_LINE_FLAG
1010 ,COMPOUNDING_DEP_TAX_FLAG
1011 ,ENFORCE_FROM_NATURAL_ACCT_FLAG
1012 ,COMPOUNDING_TAX_MISS_FLAG
1013 ,SYNC_WITH_PRVDR_FLAG
1014 ,LAST_MANUAL_ENTRY
1015 ,TAX_PROVIDER_ID
1016 ,RECORD_TYPE_CODE
1017 --,REPORTING_PERIOD_ID
1018 --,LEGAL_MESSAGE_APPL_2
1019 --,LEGAL_MESSAGE_STATUS
1020 --,LEGAL_MESSAGE_RATE
1021 --,LEGAL_MESSAGE_BASIS
1022 --,LEGAL_MESSAGE_CALC
1023 --,LEGAL_MESSAGE_THRESHOLD
1024 --,LEGAL_MESSAGE_POS
1025 --,LEGAL_MESSAGE_TRN
1026 --,LEGAL_MESSAGE_EXMPT
1027 --,LEGAL_MESSAGE_EXCPT
1028 --,TAX_REGIME_TEMPLATE_ID
1029 --,TAX_APPLICABILITY_RESULT_ID
1030 --,DIRECT_RATE_RESULT_ID
1031 --,STATUS_RESULT_ID
1032 --,RATE_RESULT_ID
1033 --,BASIS_RESULT_ID
1034 --,THRESH_RESULT_ID
1035 --,CALC_RESULT_ID
1036 --,TAX_REG_NUM_DET_RESULT_ID
1037 --,EVAL_EXMPT_RESULT_ID
1038 --,EVAL_EXCPT_RESULT_ID
1039 --,TAX_HOLD_CODE
1040 --,TAX_HOLD_RELEASED_CODE
1041 --,PRD_TOTAL_TAX_AMT
1042 --,PRD_TOTAL_TAX_AMT_TAX_CURR
1043 --,PRD_TOTAL_TAX_AMT_FUNCL_CURR
1044 --,INTERNAL_ORG_LOCATION_ID
1045 ,ATTRIBUTE_CATEGORY
1046 ,ATTRIBUTE1
1047 ,ATTRIBUTE2
1048 ,ATTRIBUTE3
1049 ,ATTRIBUTE4
1050 ,ATTRIBUTE5
1051 ,ATTRIBUTE6
1052 ,ATTRIBUTE7
1053 ,ATTRIBUTE8
1054 ,ATTRIBUTE9
1055 ,ATTRIBUTE10
1056 ,ATTRIBUTE11
1057 ,ATTRIBUTE12
1058 ,ATTRIBUTE13
1059 ,ATTRIBUTE14
1060 ,ATTRIBUTE15
1061 ,GLOBAL_ATTRIBUTE_CATEGORY
1062 ,GLOBAL_ATTRIBUTE1
1063 ,GLOBAL_ATTRIBUTE2
1064 ,GLOBAL_ATTRIBUTE3
1065 ,GLOBAL_ATTRIBUTE4
1066 ,GLOBAL_ATTRIBUTE5
1067 ,GLOBAL_ATTRIBUTE6
1068 ,GLOBAL_ATTRIBUTE7
1069 ,GLOBAL_ATTRIBUTE8
1070 ,GLOBAL_ATTRIBUTE9
1071 ,GLOBAL_ATTRIBUTE10
1072 ,GLOBAL_ATTRIBUTE11
1073 ,GLOBAL_ATTRIBUTE12
1074 ,GLOBAL_ATTRIBUTE13
1075 ,GLOBAL_ATTRIBUTE14
1076 ,GLOBAL_ATTRIBUTE15
1077 ,GLOBAL_ATTRIBUTE16
1078 ,GLOBAL_ATTRIBUTE17
1079 ,GLOBAL_ATTRIBUTE18
1080 ,GLOBAL_ATTRIBUTE19
1081 ,GLOBAL_ATTRIBUTE20
1082 ,LEGAL_JUSTIFICATION_TEXT1
1083 ,LEGAL_JUSTIFICATION_TEXT2
1084 ,LEGAL_JUSTIFICATION_TEXT3
1085 --,REPORTING_CURRENCY_CODE
1086 --,LINE_ASSESSABLE_VALUE
1087 --,TRX_LINE_INDEX
1088 --,OFFSET_TAX_RATE_CODE
1089 --,PRORATION_CODE
1090 --,OTHER_DOC_SOURCE
1091 --,CTRL_TOTAL_LINE_TX_AMT
1092 --,MRC_LINK_TO_TAX_LINE_ID
1093 --,APPLIED_TO_TRX_NUMBER
1094 --,INTERFACE_ENTITY_CODE
1095 --,INTERFACE_TAX_LINE_ID
1096 --,TAXING_JURIS_GEOGRAPHY_ID
1097 ,NUMERIC1
1098 ,NUMERIC2
1099 ,NUMERIC3
1100 ,NUMERIC4
1101 ,ADJUSTED_DOC_TAX_LINE_ID
1102 ,OBJECT_VERSION_NUMBER
1103 ,MULTIPLE_JURISDICTIONS_FLAG
1104 ,CREATED_BY
1105 ,CREATION_DATE
1106 ,LAST_UPDATED_BY
1107 ,LAST_UPDATE_DATE
1108 ,LAST_UPDATE_LOGIN
1109 ,LEGAL_REPORTING_STATUS
1110 ,ACCOUNT_SOURCE_TAX_RATE_ID
1111 )
1112 VALUES(
1113 TAX_LINE_ID
1114 ,INTERNAL_ORGANIZATION_ID
1115 ,APPLICATION_ID
1116 ,ENTITY_CODE
1117 ,EVENT_CLASS_CODE
1118 ,EVENT_TYPE_CODE
1119 ,TRX_ID
1120 ,TRX_LINE_ID
1121 ,TRX_LEVEL_TYPE
1122 ,TRX_LINE_NUMBER
1123 ,DOC_EVENT_STATUS
1124 ,TAX_EVENT_CLASS_CODE
1125 ,TAX_EVENT_TYPE_CODE
1126 ,TAX_LINE_NUMBER
1127 ,CONTENT_OWNER_ID
1128 ,TAX_REGIME_ID
1129 ,TAX_REGIME_CODE
1130 ,TAX_ID
1131 ,TAX
1132 ,TAX_STATUS_ID
1133 ,TAX_STATUS_CODE
1134 ,TAX_RATE_ID
1135 ,TAX_RATE_CODE
1136 ,TAX_RATE
1137 ,TAX_RATE_TYPE
1138 ,TAX_APPORTIONMENT_LINE_NUMBER
1139 ,'N' --MRC_TAX_LINE_FLAG
1140 ,LEDGER_ID
1141 --,ESTABLISHMENT_ID
1142 ,LEGAL_ENTITY_ID
1143 --,LEGAL_ENTITY_TAX_REG_NUMBER
1144 --,HQ_ESTB_REG_NUMBER
1145 --,HQ_ESTB_PARTY_TAX_PROF_ID
1146 ,CURRENCY_CONVERSION_DATE
1147 ,CURRENCY_CONVERSION_TYPE
1148 ,CURRENCY_CONVERSION_RATE
1149 --,TAX_CURRENCY_CONVERSION_DATE
1150 --,TAX_CURRENCY_CONVERSION_TYPE
1151 --,TAX_CURRENCY_CONVERSION_RATE
1152 ,TRX_CURRENCY_CODE
1153 ,MINIMUM_ACCOUNTABLE_UNIT
1154 ,PRECISION
1155 ,TRX_NUMBER
1156 ,TRX_DATE
1157 ,UNIT_PRICE
1158 ,LINE_AMT
1159 ,TRX_LINE_QUANTITY
1160 ,TAX_BASE_MODIFIER_RATE
1161 --,REF_DOC_APPLICATION_ID
1162 --,REF_DOC_ENTITY_CODE
1163 --,REF_DOC_EVENT_CLASS_CODE
1164 --,REF_DOC_TRX_ID
1165 --,REF_DOC_LINE_ID
1166 --,REF_DOC_LINE_QUANTITY
1167 --,REF_DOC_TRX_LEVEL_TYPE
1168 --,OTHER_DOC_LINE_AMT
1169 --,OTHER_DOC_LINE_TAX_AMT
1170 --,OTHER_DOC_LINE_TAXABLE_AMT
1171 ,UNROUNDED_TAXABLE_AMT
1172 ,UNROUNDED_TAX_AMT
1173 ,RELATED_DOC_APPLICATION_ID
1174 --,RELATED_DOC_ENTITY_CODE
1175 --,RELATED_DOC_EVENT_CLASS_CODE
1176 ,RELATED_DOC_TRX_ID
1177 --,RELATED_DOC_NUMBER
1178 --,RELATED_DOC_DATE
1179 --,RELATED_DOC_TRX_LEVEL_TYPE
1180 ,ADJUSTED_DOC_APPLICATION_ID
1181 ,ADJUSTED_DOC_ENTITY_CODE
1182 ,ADJUSTED_DOC_EVENT_CLASS_CODE
1183 ,ADJUSTED_DOC_TRX_ID
1184 ,ADJUSTED_DOC_LINE_ID
1185 ,ADJUSTED_DOC_NUMBER
1186 ,ADJUSTED_DOC_DATE
1187 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
1188 --,SUMMARY_TAX_LINE_ID
1189 --,OFFSET_LINK_TO_TAX_LINE_ID
1190 ,'N' --OFFSET_FLAG
1191 ,'N' --PROCESS_FOR_RECOVERY_FLAG
1192 --,TAX_JURISDICTION_ID
1193 --,TAX_JURISDICTION_CODE
1194 --,PLACE_OF_SUPPLY
1195 ,PLACE_OF_SUPPLY_TYPE_CODE
1196 --,PLACE_OF_SUPPLY_RESULT_ID
1197 --,TAX_DATE_RULE_ID
1198 ,TAX_DATE
1199 ,TAX_DETERMINE_DATE
1200 ,TAX_POINT_DATE
1201 ,TRX_LINE_DATE
1202 ,TAX_TYPE_CODE
1203 --,TAX_CODE
1204 --,TAX_REGISTRATION_ID
1205 --,TAX_REGISTRATION_NUMBER
1206 --,REGISTRATION_PARTY_TYPE
1207 ,ROUNDING_LEVEL_CODE
1208 ,ROUNDING_RULE_CODE
1209 --,ROUNDING_LVL_PARTY_TAX_PROF_ID
1210 --,ROUNDING_LVL_PARTY_TYPE
1211 ,'N' --COMPOUNDING_TAX_FLAG
1212 --,ORIG_TAX_STATUS_ID
1213 --,ORIG_TAX_STATUS_CODE
1214 --,ORIG_TAX_RATE_ID
1215 --,ORIG_TAX_RATE_CODE
1216 --,ORIG_TAX_RATE
1217 --,ORIG_TAX_JURISDICTION_ID
1218 --,ORIG_TAX_JURISDICTION_CODE
1219 --,ORIG_TAX_AMT_INCLUDED_FLAG
1220 --,ORIG_SELF_ASSESSED_FLAG
1221 ,TAX_CURRENCY_CODE
1222 ,TAX_AMT
1223 ,TAX_AMT_TAX_CURR
1224 ,TAX_AMT_FUNCL_CURR
1225 ,TAXABLE_AMT
1226 ,TAXABLE_AMT_TAX_CURR
1227 ,TAXABLE_AMT_FUNCL_CURR
1228 --,ORIG_TAXABLE_AMT
1229 --,ORIG_TAXABLE_AMT_TAX_CURR
1230 ,CAL_TAX_AMT
1231 ,CAL_TAX_AMT_TAX_CURR
1232 ,CAL_TAX_AMT_FUNCL_CURR
1233 --,ORIG_TAX_AMT
1234 --,ORIG_TAX_AMT_TAX_CURR
1235 --,REC_TAX_AMT
1236 --,REC_TAX_AMT_TAX_CURR
1237 --,REC_TAX_AMT_FUNCL_CURR
1238 --,NREC_TAX_AMT
1239 --,NREC_TAX_AMT_TAX_CURR
1240 --,NREC_TAX_AMT_FUNCL_CURR
1241 ,TAX_EXEMPTION_ID
1242 --,TAX_RATE_BEFORE_EXEMPTION
1243 --,TAX_RATE_NAME_BEFORE_EXEMPTION
1244 --,EXEMPT_RATE_MODIFIER
1245 ,EXEMPT_CERTIFICATE_NUMBER
1246 --,EXEMPT_REASON
1247 ,EXEMPT_REASON_CODE
1248 ,TAX_EXCEPTION_ID
1249 ,TAX_RATE_BEFORE_EXCEPTION
1250 --,TAX_RATE_NAME_BEFORE_EXCEPTION
1251 --,EXCEPTION_RATE
1252 ,'N' --TAX_APPORTIONMENT_FLAG
1253 ,'Y' --HISTORICAL_FLAG
1254 ,TAXABLE_BASIS_FORMULA
1255 ,TAX_CALCULATION_FORMULA
1256 ,'N' --CANCEL_FLAG
1257 ,'N' --PURGE_FLAG
1258 ,'N' --DELETE_FLAG
1259 ,'N' --TAX_AMT_INCLUDED_FLAG
1260 ,'N' --SELF_ASSESSED_FLAG
1261 ,'N' --OVERRIDDEN_FLAG
1262 ,DECODE(AUTOTAX,'Y','N','Y') --MANUALLY_ENTERED_FLAG
1263 ,'N' --REPORTING_ONLY_FLAG
1264 ,'N' --FREEZE_UNTIL_OVERRIDDEN_FLAG
1265 ,'N' --COPIED_FROM_OTHER_DOC_FLAG
1266 ,'N' --RECALC_REQUIRED_FLAG
1267 ,'N' --SETTLEMENT_FLAG
1268 ,'N' --ITEM_DIST_CHANGED_FLAG
1269 ,'N' --ASSOCIATED_CHILD_FROZEN_FLAG
1270 ,TAX_ONLY_LINE_FLAG
1271 ,'N' --COMPOUNDING_DEP_TAX_FLAG
1272 ,'N' --ENFORCE_FROM_NATURAL_ACCT_FLAG
1273 ,'N' --COMPOUNDING_TAX_MISS_FLAG
1274 ,'N' --SYNC_WITH_PRVDR_FLAG
1275 ,DECODE(AUTOTAX,'Y',NULL,'TAX_AMOUNT') --LAST_MANUAL_ENTRY
1276 ,TAX_PROVIDER_ID
1277 ,'MIGRATED' --RECORD_TYPE_CODE
1278 --,REPORTING_PERIOD_ID
1279 --,LEGAL_MESSAGE_APPL_2
1280 --,LEGAL_MESSAGE_STATUS
1281 --,LEGAL_MESSAGE_RATE
1282 --,LEGAL_MESSAGE_BASIS
1283 --,LEGAL_MESSAGE_CALC
1284 --,LEGAL_MESSAGE_THRESHOLD
1285 --,LEGAL_MESSAGE_POS
1286 --,LEGAL_MESSAGE_TRN
1287 --,LEGAL_MESSAGE_EXMPT
1288 --,LEGAL_MESSAGE_EXCPT
1289 --,TAX_REGIME_TEMPLATE_ID
1290 --,TAX_APPLICABILITY_RESULT_ID
1291 --,DIRECT_RATE_RESULT_ID
1292 --,STATUS_RESULT_ID
1293 --,RATE_RESULT_ID
1294 --,BASIS_RESULT_ID
1295 --,THRESH_RESULT_ID
1296 --,CALC_RESULT_ID
1297 --,TAX_REG_NUM_DET_RESULT_ID
1298 --,EVAL_EXMPT_RESULT_ID
1299 --,EVAL_EXCPT_RESULT_ID
1300 --,TAX_HOLD_CODE
1301 --,TAX_HOLD_RELEASED_CODE
1302 --,PRD_TOTAL_TAX_AMT
1303 --,PRD_TOTAL_TAX_AMT_TAX_CURR
1304 --,PRD_TOTAL_TAX_AMT_FUNCL_CURR
1305 --,INTERNAL_ORG_LOCATION_ID
1306 ,ATTRIBUTE_CATEGORY
1307 ,ATTRIBUTE1
1308 ,ATTRIBUTE2
1309 ,ATTRIBUTE3
1310 ,ATTRIBUTE4
1311 ,ATTRIBUTE5
1312 ,ATTRIBUTE6
1313 ,ATTRIBUTE7
1314 ,ATTRIBUTE8
1315 ,ATTRIBUTE9
1316 ,ATTRIBUTE10
1317 ,ATTRIBUTE11
1318 ,ATTRIBUTE12
1319 ,ATTRIBUTE13
1320 ,ATTRIBUTE14
1321 ,ATTRIBUTE15
1322 ,GLOBAL_ATTRIBUTE_CATEGORY
1323 ,GLOBAL_ATTRIBUTE1
1324 ,GLOBAL_ATTRIBUTE2
1325 ,GLOBAL_ATTRIBUTE3
1326 ,GLOBAL_ATTRIBUTE4
1327 ,GLOBAL_ATTRIBUTE5
1328 ,GLOBAL_ATTRIBUTE6
1329 ,GLOBAL_ATTRIBUTE7
1330 ,GLOBAL_ATTRIBUTE8
1331 ,GLOBAL_ATTRIBUTE9
1332 ,GLOBAL_ATTRIBUTE10
1333 ,GLOBAL_ATTRIBUTE11
1334 ,GLOBAL_ATTRIBUTE12
1335 ,GLOBAL_ATTRIBUTE13
1336 ,GLOBAL_ATTRIBUTE14
1337 ,GLOBAL_ATTRIBUTE15
1338 ,GLOBAL_ATTRIBUTE16
1339 ,GLOBAL_ATTRIBUTE17
1340 ,GLOBAL_ATTRIBUTE18
1341 ,GLOBAL_ATTRIBUTE19
1342 ,GLOBAL_ATTRIBUTE20
1343 ,LEGAL_JUSTIFICATION_TEXT1
1344 ,LEGAL_JUSTIFICATION_TEXT2
1345 ,LEGAL_JUSTIFICATION_TEXT3
1346 --,REPORTING_CURRENCY_CODE
1347 --,LINE_ASSESSABLE_VALUE
1348 --,TRX_LINE_INDEX
1349 --,OFFSET_TAX_RATE_CODE
1350 --,PRORATION_CODE
1351 --,OTHER_DOC_SOURCE
1352 --,CTRL_TOTAL_LINE_TX_AMT
1353 --,MRC_LINK_TO_TAX_LINE_ID
1354 --,APPLIED_TO_TRX_NUMBER
1355 --,INTERFACE_ENTITY_CODE
1356 --,INTERFACE_TAX_LINE_ID
1357 --,TAXING_JURIS_GEOGRAPHY_ID
1358 ,NUMERIC1
1359 ,NUMERIC2
1360 ,NUMERIC3
1361 ,NUMERIC4
1362 ,ADJUSTED_DOC_TAX_LINE_ID
1363 ,OBJECT_VERSION_NUMBER
1364 ,'N' --MULTIPLE_JURISDICTIONS_FLAG
1365 ,CREATED_BY
1366 ,CREATION_DATE
1367 ,LAST_UPDATED_BY
1368 ,LAST_UPDATE_DATE
1369 ,LAST_UPDATE_LOGIN
1370 ,LEGAL_REPORTING_STATUS
1371 ,ACCOUNT_SOURCE_TAX_RATE_ID
1372 )
1373 SELECT /*+ ROWID(custtrx) ORDERED use_hash(arsysparam) swap_join_inputs(arsysparam) swap_join_inputs(upd)
1374 use_nl(types,fndcurr,fds,ptp,rbs,custtrx_prev,custtrxl,vat,rates,custtrxll,memoline) */
1375 NVL(custtrx.org_id, l_org_id) INTERNAL_ORGANIZATION_ID,
1376 222 APPLICATION_ID,
1377 'TRANSACTIONS' ENTITY_CODE,
1378 DECODE(types.type,
1379 'INV','INVOICE',
1380 'CM', 'CREDIT_MEMO',
1381 'DM', 'DEBIT_MEMO',
1382 'NONE') EVENT_CLASS_CODE,
1383 DECODE(types.type,
1384 'INV',4,
1385 'DM', 5,
1386 'CM', 6, NULL ) EVENT_CLASS_MAPPING_ID,
1387 -- DECODE(types.type,
1388 -- 'INV', 'INV_CREATE',
1389 -- 'CM', 'CM_CREATE',
1390 -- 'DM', 'DM_CREATE',
1391 -- 'CREATE') EVENT_TYPE_CODE,
1392 DECODE(types.type,
1393 'INV',DECODE(NVL(SIGN(custtrx.printing_count), 0),
1394 1, 'INV_PRINT',
1395 DECODE(custtrx.complete_flag,
1396 'Y', 'INV_COMPLETE',
1397 'INV_CREATE')),
1398 'CM',DECODE(NVL(SIGN(custtrx.printing_count), 0),
1399 1, 'CM_PRINT',
1400 DECODE(custtrx.complete_flag,
1401 'Y', 'CM_COMPLETE',
1402 'CM_CREATE')),
1403 'DM',DECODE(NVL(SIGN(custtrx.printing_count), 0),
1404 1, 'DM_PRINT',
1405 DECODE(custtrx.complete_flag,
1406 'Y', 'DM_COMPLETE',
1407 'DM_CREATE')),
1408 'CREATE') EVENT_TYPE_CODE,
1409 'CREATED' DOC_EVENT_STATUS,
1410 'CREATE' LINE_LEVEL_ACTION,
1411 custtrx.customer_trx_id TRX_ID,
1412 DECODE(custtrxl.line_type,
1413 'TAX', custtrxl.link_to_cust_trx_line_id,
1414 custtrxl.customer_trx_line_id) TRX_LINE_ID,
1415 'LINE' TRX_LEVEL_TYPE,
1416 NVL(custtrx.trx_date,sysdate) TRX_DATE,
1417
1418 --NULL TRX_DOC_REVISION,
1419 NVL(custtrx.invoice_currency_code,'USD') TRX_CURRENCY_CODE,
1420 custtrx.exchange_date CURRENCY_CONVERSION_DATE,
1421 custtrx.exchange_rate CURRENCY_CONVERSION_RATE,
1422 custtrx.exchange_rate_type CURRENCY_CONVERSION_TYPE,
1423 fndcurr.minimum_accountable_unit MINIMUM_ACCOUNTABLE_UNIT,
1424 NVL(fndcurr.precision,0) PRECISION,
1425 NVL(custtrx.legal_entity_id, -99 ) LEGAL_ENTITY_ID,
1426 --NULL ESTABLISHMENT_ID,
1427 custtrx.cust_trx_type_id RECEIVABLES_TRX_TYPE_ID,
1428 arsysparam.default_country DEFAULT_TAXATION_COUNTRY,
1429 custtrx.trx_number TRX_NUMBER,
1430 DECODE(custtrxl.line_type,
1431 'TAX', custtrxll.line_number,
1432 custtrxl.line_number) TRX_LINE_NUMBER,
1433 SUBSTRB(custtrxl.description,1,240) TRX_LINE_DESCRIPTION,
1434 --NULL TRX_DESCRIPTION,
1435 --NULL TRX_COMMUNICATED_DATE,
1436 custtrx.batch_source_id BATCH_SOURCE_ID,
1437 rbs.name BATCH_SOURCE_NAME,
1438 custtrx.doc_sequence_id DOC_SEQ_ID,
1439 fds.name DOC_SEQ_NAME,
1440 custtrx.doc_sequence_value DOC_SEQ_VALUE,
1441 custtrx.term_due_date TRX_DUE_DATE,
1442 types.description TRX_TYPE_DESCRIPTION,
1443 (CASE
1444 WHEN (custtrx.global_attribute_category = 'JA.TW.ARXTWMAI.RA_CUSTOMER_TRX' AND
1445 custtrx.global_attribute1 is NOT NULL) THEN
1446 'GUI TYPE/' || custtrx.global_attribute1
1447 WHEN custtrx.global_attribute_category = 'JE.ES.ARXTWMAI.MODELO347' THEN
1448 DECODE(nvl(custtrx.global_attribute6, 'N'), 'N', 'MOD340_EXCL', 'Y', 'MOD340/'||'E')
1449 WHEN custtrx.global_attribute_category = 'JE.ES.ARXTWMAI.MODELO347PR' THEN
1450 DECODE(nvl(custtrx.global_attribute6, 'N'), 'N', 'MOD340_EXCL', 'Y', 'MOD340/'||'E')
1451 WHEN custtrx.global_attribute_category = 'JE.ES.ARXTWMAI.MODELO415' THEN
1452 DECODE(nvl(custtrx.global_attribute6, 'N'), 'N', 'MOD340_EXCL', 'Y', 'MOD340/'||'F')
1453 WHEN custtrx.global_attribute_category ='JE.ES.ARXTWMAI.MODELO415_347' THEN
1454 DECODE(nvl(custtrx.global_attribute6, 'N'), 'N', 'MOD340_EXCL', 'Y',
1455 decode(custtrx.global_attribute7, 'E', 'MOD340/'||'E', 'F', 'MOD340/'||'F'))
1456 WHEN custtrx.global_attribute_category = 'JE.ES.ARXTWMAI.MODELO415_347PR' THEN
1457 DECODE(nvl(custtrx.global_attribute6, 'N'), 'N', 'MOD340_EXCL', 'Y',
1458 decode(custtrx.global_attribute7, 'E', 'MOD340/'||'E', 'F', 'MOD340/'||'F'))
1459 WHEN custtrx.global_attribute_category = 'JE.ES.ARXTWMAI.MODELO349' THEN
1460 DECODE(nvl(custtrx.global_attribute6,'N'),'N','MOD340_EXCL', 'Y',
1461 decode(custtrx.global_attribute7,'E','MOD340/E', 'U',
1462 decode(custtrx.global_attribute9,NULL,'MOD340/U','A','MOD340/UA','B','MOD340/UB')))
1463 WHEN custtrx.global_attribute_category = 'JE.ES.ARXTWMAI.MODELO340' THEN
1464 DECODE(nvl(custtrx.global_attribute6, 'N'), 'N', 'MOD340_EXCL', 'Y',
1465 decode(custtrx.global_attribute9, NULL, 'MOD340/U', 'A', 'MOD340/UA', 'B', 'MOD340/UB'))
1466 END) DOCUMENT_SUB_TYPE,
1467 --NULL SUPPLIER_TAX_INVOICE_NUMBER,
1468 --NULL SUPPLIER_TAX_INVOICE_DATE,
1469 --NULL SUPPLIER_EXCHANGE_RATE,
1470 (CASE
1471 WHEN custtrx.global_attribute_category
1472 IN ('JE.HU.ARXTWMAI.TAX_DATE',
1473 'JE.SK.ARXTWMAI.TAX_DATE',
1474 'JE.PL.ARXTWMAI.TAX_DATE',
1475 'JE.CZ.ARXTWMAI.TAX_DATE')
1476 THEN
1477 TO_DATE(custtrx.global_attribute1, 'YYYY/MM/DD HH24:MI:SS')
1478 WHEN custtrx.global_attribute_category
1479 = 'JL.AR.ARXTWMAI.TGW_HEADER' THEN
1480 TO_DATE(custtrx.global_attribute18, 'YYYY/MM/DD HH24:MI:SS')
1481 END) TAX_INVOICE_DATE,
1482
1483 (CASE
1484 WHEN custtrx.global_attribute_category
1485 = 'JL.AR.ARXTWMAI.TGW_HEADER' THEN
1486 custtrx.global_attribute17
1487 END) TAX_INVOICE_NUMBER,
1488 ptp.party_tax_profile_id FIRST_PTY_ORG_ID,
1489 'SALES_TRANSACTION' TAX_EVENT_CLASS_CODE,
1490 -- 'CREATE' TAX_EVENT_TYPE_CODE,
1491 DECODE(NVL(SIGN(custtrx.printing_count), 0),
1492 1, 'FREEZE_FOR_TAX',
1493 DECODE(custtrx.complete_flag,
1494 'Y', 'VALIDATE_FOR_TAX',
1495 'CREATE') ) TAX_EVENT_TYPE_CODE,
1496
1497 --NULL LINE_INTENDED_USE,
1498 custtrxl.line_type TRX_LINE_TYPE,
1499 --NULL TRX_SHIPPING_DATE,
1500 --NULL TRX_RECEIPT_DATE,
1501 --NULL TRX_SIC_CODE,
1502 custtrx.fob_point FOB_POINT,
1503 custtrx.waybill_number TRX_WAYBILL_NUMBER,
1504 custtrxl.inventory_item_id PRODUCT_ID,
1505 (CASE
1506 WHEN custtrx.global_attribute_category
1507 = 'JA.TW.ARXTWMAI.RA_CUSTOMER_TRX'
1508 AND l_inv_installed = 'Y'
1509 THEN
1510 DECODE(custtrxl.global_attribute2,
1511 'Y', 'WINE CIGARRETE',
1512 'N', NULL)
1513
1514 WHEN custtrxl.global_attribute_category
1515 IN ('JL.AR.ARXTWMAI.LINES',
1516 'JL.BR.ARXTWMAI.Additional Info',
1517 'JL.CO.ARXTWMAI.LINES' )
1518 AND l_inv_installed = 'Y'
1519 THEN
1520 custtrxl.global_attribute2
1521 END) PRODUCT_FISC_CLASSIFICATION,
1522 custtrxl.warehouse_id PRODUCT_ORG_ID,
1523 custtrxl.uom_code UOM_CODE,
1524 --NULL PRODUCT_TYPE,
1525 --NULL PRODUCT_CODE,
1526 (CASE
1527 WHEN custtrx.global_attribute_category
1528 = 'JA.TW.ARXTWMAI.RA_CUSTOMER_TRX'
1529 AND l_inv_installed = 'N'
1530 THEN
1531 DECODE(custtrxl.global_attribute2,
1532 'Y', 'WINE CIGARRETE',
1533 'N', NULL)
1534
1535 WHEN custtrxl.global_attribute_category
1536 IN ('JL.AR.ARXTWMAI.LINES',
1537 'JL.BR.ARXTWMAI.Additional Info',
1538 'JL.CO.ARXTWMAI.LINES')
1539 AND l_inv_installed = 'N'
1540 THEN
1541 custtrxl.global_attribute2
1542 END) PRODUCT_CATEGORY,
1543
1544 DECODE( custtrxl.inventory_item_id,
1545 NULL,NULL,
1546 SUBSTRB(custtrxl.description,1,240) ) PRODUCT_DESCRIPTION,
1547 (CASE
1548 WHEN custtrxl.global_attribute_category
1549 = 'JL.BR.ARXTWMAI.Additional Info'
1550 THEN
1551 custtrxl.global_attribute1
1552 WHEN custtrxl.interface_line_context
1553 IN ('OKL_CONTRACTS',
1554 'OKL_INVESTOR',
1555 'OKL_MANUAL')
1556 THEN
1557 custtrxl.interface_line_attribute12
1558 WHEN custtrx.global_attribute_category IN (
1559 'JE.ES.ARXTWMAI.MODELO347'
1560 ,'JE.ES.ARXTWMAI.MODELO347PR'
1561 ,'JE.ES.ARXTWMAI.MODELO349'
1562 ,'JE.ES.ARXTWMAI.MODELO415'
1563 ,'JE.ES.ARXTWMAI.MODELO415_347'
1564 ,'JE.ES.ARXTWMAI.MODELO415_347PR'
1565 ,'JE.ES.ARXTWMAI.MODELO340') THEN
1566 nvl(custtrx.global_attribute8, 'MOD340NONE')
1567 END) USER_DEFINED_FISC_CLASS,
1568
1569 DECODE( custtrxl.line_type,
1570 'TAX', nvl(custtrxll.extended_amount,0),
1571 nvl(custtrxl.extended_amount,0)) LINE_AMT,
1572
1573 DECODE(custtrxl.line_type,
1574 'TAX', custtrxll.quantity_invoiced,
1575 custtrxl.quantity_invoiced ) TRX_LINE_QUANTITY,
1576
1577 --NULL CASH_DISCOUNT,
1578 --NULL VOLUME_DISCOUNT,
1579 --NULL TRADING_DISCOUNT,
1580 --NULL TRANSFER_CHARGE,
1581 --NULL TRANSPORTATION_CHARGE,
1582 --NULL INSURANCE_CHARGE,
1583 --NULL OTHER_CHARGE,
1584 --NULL ASSESSABLE_VALUE,
1585 --NULL ASSET_FLAG,
1586 --NULL ASSET_NUMBER,
1587 1 ASSET_ACCUM_DEPRECIATION,
1588 --NULL ASSET_TYPE,
1589 1 ASSET_COST,
1590
1591 DECODE( custtrx.related_customer_trx_id,
1592 NULL, NULL,
1593 222) RELATED_DOC_APPLICATION_ID,
1594 --NULL RELATED_DOC_ENTITY_CODE,
1595 --NULL RELATED_DOC_EVENT_CLASS_CODE,
1596 custtrx.related_customer_trx_id RELATED_DOC_TRX_ID,
1597 --NULL RELATED_DOC_NUMBER,
1598 --NULL RELATED_DOC_DATE,
1599
1600 DECODE(custtrxl.previous_customer_trx_id,
1601 NULL, NULL,
1602 222 ) ADJUSTED_DOC_APPLICATION_ID,
1603 DECODE(custtrxl.previous_customer_trx_id,
1604 NULL, NULL,
1605 'TRANSACTIONS' ) ADJUSTED_DOC_ENTITY_CODE,
1606 --NULL ADJUSTED_DOC_EVENT_CLASS_CODE,
1607 DECODE(types.type,
1608 'CM', 'INVOICE',
1609 'DM', 'INVOICE',
1610 NULL) ADJUSTED_DOC_EVENT_CLASS_CODE,
1611 custtrxl.previous_customer_trx_id ADJUSTED_DOC_TRX_ID,
1612
1613 DECODE(custtrxl.line_type,
1614 'TAX', custtrxll.previous_customer_trx_line_id,
1615 custtrxl.previous_customer_trx_line_id) ADJUSTED_DOC_LINE_ID,
1616
1617 custtrx_prev.trx_number ADJUSTED_DOC_NUMBER,
1618 custtrx_prev.trx_Date ADJUSTED_DOC_DATE,
1619 DECODE(custtrxl.previous_customer_trx_id,
1620 NULL, NULL,
1621 'LINE' ) ADJUSTED_DOC_TRX_LEVEL_TYPE,
1622
1623 --NULL REF_DOC_APPLICATION_ID,
1624 --NULL REF_DOC_ENTITY_CODE,
1625 --NULL REF_DOC_EVENT_CLASS_CODE,
1626 --NULL REF_DOC_TRX_ID,
1627 --NULL REF_DOC_LINE_ID,
1628 --NULL REF_DOC_LINE_QUANTITY,
1629 --NULL REF_DOC_TRX_LEVEL_TYPE,
1630
1631 (CASE
1632 WHEN custtrx.global_attribute_category
1633 = 'JA.TW.ARXTWMAI.RA_CUSTOMER_TRX'
1634 THEN
1635 'SALES_TRANSACTION/' ||custtrx.global_attribute3
1636
1637 WHEN custtrx.global_attribute_category IN
1638 ('JE.ES.ARXTWMAI.INVOICE_INFO'
1639 ,'JE.ES.ARXTWMAI.MODELO347'
1640 ,'JE.ES.ARXTWMAI.MODELO347PR'
1641 ,'JE.ES.ARXTWMAI.MODELO349'
1642 ,'JE.ES.ARXTWMAI.MODELO415'
1643 ,'JE.ES.ARXTWMAI.MODELO415_347'
1644 ,'JE.ES.ARXTWMAI.MODELO415_347PR'
1645 ,'JE.ES.ARXTWMAI.OTHER')
1646 THEN
1647 'SALES_TRANSACTION/INVOICE TYPE/'||custtrx.global_attribute1
1648
1649 WHEN custtrxl.global_attribute_category IN
1650 ('JL.AR.ARXTWMAI.LINES'
1651 ,'JL.BR.ARXTWMAI.Additional Info'
1652 ,'JL.CO.ARXTWMAI.LINES')
1653 THEN
1654 'SALES_TRANSACTION/' ||custtrxl.global_attribute3
1655
1656 WHEN custtrx.global_attribute_category IN
1657 ('JE.ES.ARXTWMAI.INVOICE_INFO'
1658 ,'JE.ES.ARXTWMAI.OTHER')
1659 THEN
1660 'SALES_TRANSACTION/INVOICE TYPE/'||custtrx.global_attribute1
1661
1662 WHEN custtrx.global_attribute_category IN
1663 ('JE.ES.ARXTWMAI.MODELO347'
1664 ,'JE.ES.ARXTWMAI.MODELO347PR'
1665 ,'JE.ES.ARXTWMAI.MODELO349'
1666 ,'JE.ES.ARXTWMAI.MODELO415'
1667 ,'JE.ES.ARXTWMAI.MODELO415_347'
1668 ,'JE.ES.ARXTWMAI.MODELO415_347PR')
1669 THEN
1670 'SALES_TRANSACTION/INVOICE TYPE/'||custtrx.global_attribute1||'/'||nvl(custtrx.GLOBAL_ATTRIBUTE11,'B')
1671
1672 WHEN custtrx.global_attribute_category = 'JE.ES.ARXTWMAI.MODELO340'
1673 THEN
1674 'SALES_TRANSACTION/INVOICE TYPE/'||custtrx.global_attribute1||'/'||nvl(custtrx.GLOBAL_ATTRIBUTE8,'B')
1675 END ) TRX_BUSINESS_CATEGORY,
1676
1677 custtrxl.tax_exempt_number EXEMPT_CERTIFICATE_NUMBER,
1678 --NULL EXEMPT_REASON,
1679 custtrxl.tax_exempt_flag EXEMPTION_CONTROL_FLAG,
1680 custtrxl.tax_exempt_reason_code EXEMPT_REASON_CODE,
1681 --'Y' HISTORICAL_FLAG,
1682 NVL(custtrx.trx_date,sysdate) TRX_LINE_GL_DATE,
1683 --'N' LINE_AMT_INCLUDES_TAX_FLAG,
1684 --NULL ACCOUNT_CCID,
1685 --NULL ACCOUNT_STRING,
1686 --NULL SHIP_TO_LOCATION_ID,
1687 --NULL SHIP_FROM_LOCATION_ID,
1688 --NULL POA_LOCATION_ID,
1689 --NULL POO_LOCATION_ID,
1690 --NULL BILL_TO_LOCATION_ID,
1691 --NULL BILL_FROM_LOCATION_ID,
1692 --NULL PAYING_LOCATION_ID,
1693 --NULL OWN_HQ_LOCATION_ID,
1694 --NULL TRADING_HQ_LOCATION_ID,
1695 --NULL POC_LOCATION_ID,
1696 --NULL POI_LOCATION_ID,
1697 --NULL POD_LOCATION_ID,
1698 --NULL TITLE_TRANSFER_LOCATION_ID,
1699 --'N' CTRL_HDR_TX_APPL_FLAG,
1700 --NULL CTRL_TOTAL_LINE_TX_AMT,
1701 --NULL CTRL_TOTAL_HDR_TX_AMT,
1702
1703 DECODE(types.type,
1704 'INV','INVOICE',
1705 'CM', 'CREDIT_MEMO',
1706 'DM', 'DEBIT_MEMO',
1707 types.type) LINE_CLASS,
1708 NVL(custtrx.trx_date,sysdate) TRX_LINE_DATE,
1709 --NULL INPUT_TAX_CLASSIFICATION_CODE,
1710 vat.tax_code OUTPUT_TAX_CLASSIFICATION_CODE,
1711 --NULL INTERNAL_ORG_LOCATION_ID,
1712 --NULL PORT_OF_ENTRY_CODE,
1713 --'Y' TAX_REPORTING_FLAG,
1714 --'N' TAX_AMT_INCLUDED_FLAG,
1715 --'N' COMPOUNDING_TAX_FLAG,
1716 --NULL EVENT_ID,
1717 --'N' THRESHOLD_INDICATOR_FLAG,
1718 --NULL PROVNL_TAX_DETERMINATION_DATE,
1719 DECODE(custtrxl.line_type,
1720 'TAX', custtrxll.unit_selling_price,
1721 custtrxl.unit_selling_price ) UNIT_PRICE,
1722 custtrx.ship_to_site_use_id SHIP_TO_CUST_ACCT_SITE_USE_ID,
1723 custtrx.bill_to_site_use_id BILL_TO_CUST_ACCT_SITE_USE_ID,
1724 custtrx.batch_id TRX_BATCH_ID,
1725
1726 --NULL START_EXPENSE_DATE,
1727 --NULL SOURCE_APPLICATION_ID,
1728 --NULL SOURCE_ENTITY_CODE,
1729 --NULL SOURCE_EVENT_CLASS_CODE,
1730 --NULL SOURCE_TRX_ID,
1731 --NULL SOURCE_LINE_ID,
1732 --NULL SOURCE_TRX_LEVEL_TYPE,
1733 --'MIGRATED' RECORD_TYPE_CODE,
1734 --'N' INCLUSIVE_TAX_OVERRIDE_FLAG,
1735 --'N' TAX_PROCESSING_COMPLETED_FLAG,
1736 1 OBJECT_VERSION_NUMBER,
1737 DECODE(types.default_status,
1738 'VD', 'VD',
1739 NULL) APPLICATION_DOC_STATUS,
1740 --'N' USER_UPD_DET_FACTORS_FLAG,
1741 --NULL SOURCE_TAX_LINE_ID,
1742 --NULL REVERSED_APPLN_ID,
1743 --NULL REVERSED_ENTITY_CODE,
1744 --NULL REVERSED_EVNT_CLS_CODE,
1745 --NULL REVERSED_TRX_ID,
1746 --NULL REVERSED_TRX_LEVEL_TYPE,
1747 --NULL REVERSED_TRX_LINE_ID,
1748 --NULL TAX_CALCULATION_DONE_FLAG,
1749 decode(arsysparam.tax_database_view_set,'_A','Y','_V','Y',NULL)
1750 PARTNER_MIGRATED_FLAG,
1751 custtrx.ship_to_address_id SHIP_THIRD_PTY_ACCT_SITE_ID,
1752 custtrx.bill_to_address_id BILL_THIRD_PTY_ACCT_SITE_ID,
1753 custtrx.ship_to_customer_id SHIP_THIRD_PTY_ACCT_ID,
1754 custtrx.bill_to_customer_id BILL_THIRD_PTY_ACCT_ID,
1755
1756 --NULL INTERFACE_ENTITY_CODE,
1757 --NULL INTERFACE_LINE_ID,
1758 --NULL HISTORICAL_TAX_CODE_ID,
1759 --NULL ICX_SESSION_ID,
1760 --NULL TRX_LINE_CURRENCY_CODE,
1761 --NULL TRX_LINE_CURRENCY_CONV_RATE,
1762 --NULL TRX_LINE_CURRENCY_CONV_DATE,
1763 --NULL TRX_LINE_PRECISION,
1764 --NULL TRX_LINE_MAU,
1765 --NULL TRX_LINE_CURRENCY_CONV_TYPE,
1766
1767 -- zx_lines columns start from here
1768
1769 custtrxl.tax_line_id TAX_LINE_ID,
1770 DECODE(custtrxl.line_type,
1771 'TAX', RANK() OVER (
1772 PARTITION BY
1773 custtrxl.link_to_cust_trx_line_id,
1774 custtrxl.customer_trx_id
1775 ORDER BY
1776 custtrxl.line_number,
1777 custtrxl.customer_trx_line_id
1778 ),
1779 NULL) TAX_LINE_NUMBER,
1780 ptp.party_tax_profile_id CONTENT_OWNER_ID,
1781 regimes.tax_regime_id TAX_REGIME_ID,
1782 rates.TAX_REGIME_CODE TAX_REGIME_CODE,
1783 taxes.tax_id TAX_ID,
1784 rates.tax TAX,
1785 status.tax_status_id TAX_STATUS_ID,
1786 rates.TAX_STATUS_CODE TAX_STATUS_CODE,
1787 custtrxl.vat_tax_id TAX_RATE_ID,
1788 rates.TAX_RATE_CODE TAX_RATE_CODE,
1789 custtrxl.tax_rate TAX_RATE,
1790 rates.rate_type_code TAX_RATE_TYPE,
1791
1792 DECODE(custtrxl.line_type,
1793 'TAX', RANK() OVER (
1794 PARTITION BY
1795 rates.tax_regime_code,
1796 rates.tax,
1797 custtrxl.link_to_cust_trx_line_id,
1798 custtrxl.customer_trx_id
1799 ORDER BY
1800 custtrxl.line_number,
1801 custtrxl.customer_trx_line_id
1802 ),
1803 NULL) TAX_APPORTIONMENT_LINE_NUMBER,
1804
1805 --'N' MRC_TAX_LINE_FLAG,
1806 custtrx.set_of_books_id LEDGER_ID,
1807 --NULL LEGAL_ENTITY_TAX_REG_NUMBER,
1808 --NULL HQ_ESTB_REG_NUMBER,
1809 --NULL HQ_ESTB_PARTY_TAX_PROF_ID,
1810 --NULL TAX_CURRENCY_CONVERSION_DATE,
1811 --NULL TAX_CURRENCY_CONVERSION_TYPE,
1812 --NULL TAX_CURRENCY_CONVERSION_RATE,
1813
1814 (CASE
1815 WHEN custtrxl.global_attribute_category
1816 IN ('JL.BR.ARXTWMAI.Additional Info',
1817 'JL.CO.ARXTWMAI.LINES',
1818 'JL.AR.ARXTWMAI.LINES')
1819 THEN
1820 DECODE(LTRIM(custtrxl.global_attribute12,'-.0123456789'),
1821 NULL,TO_NUMBER(custtrxl.global_attribute12),
1822 NULL)
1823 END) TAX_BASE_MODIFIER_RATE,
1824
1825 --NULL OTHER_DOC_LINE_AMT,
1826 --NULL OTHER_DOC_LINE_TAX_AMT,
1827 --NULL OTHER_DOC_LINE_TAXABLE_AMT,
1828 (CASE
1829 WHEN custtrxl.global_attribute_category
1830 IN ( 'JL.BR.ARXTWMAI.Additional Info',
1831 'JL.CO.ARXTWMAI.LINES',
1832 'JL.AR.ARXTWMAI.LINES')
1833 THEN
1834 DECODE(LTRIM(custtrxl.global_attribute11,'-.0123456789'),
1835 NULL,TO_NUMBER(custtrxl.global_attribute11),
1836 NULL)
1837 ELSE
1838 custtrxl.taxable_amount
1839 END) UNROUNDED_TAXABLE_AMT,
1840
1841 (CASE
1842 WHEN custtrxl.global_attribute_category
1843 IN( 'JL.BR.ARXTWMAI.Additional Info',
1844 'JL.CO.ARXTWMAI.LINES',
1845 'JL.AR.ARXTWMAI.LINES')
1846 THEN
1847 DECODE(LTRIM(custtrxl.global_attribute19,'-.0123456789'),
1848 NULL,TO_NUMBER(custtrxl.global_attribute19),
1849 NULL)
1850 ELSE
1851 custtrxl.extended_amount
1852 END) UNROUNDED_TAX_AMT,
1853 --NULL RELATED_DOC_TRX_LEVEL_TYPE,
1854 --NULL SUMMARY_TAX_LINE_ID,
1855 --NULL OFFSET_LINK_TO_TAX_LINE_ID,
1856 --'N' OFFSET_FLAG,
1857 --'N' PROCESS_FOR_RECOVERY_FLAG,
1858 --NULL TAX_JURISDICTION_ID,
1859 --NULL TAX_JURISDICTION_CODE,
1860 --NULL PLACE_OF_SUPPLY,
1861 -- decode(custtrx.ship_to_site_use_id,null,'BILL_TO','SHIP_TO') PLACE_OF_SUPPLY_TYPE_CODE,
1862 'SHIP_TO_BILL_TO' PLACE_OF_SUPPLY_TYPE_CODE,
1863 --NULL PLACE_OF_SUPPLY_RESULT_ID,
1864 --NULL TAX_DATE_RULE_ID,
1865 DECODE(custtrxl.previous_customer_trx_id,
1866 NULL, custtrx.trx_date,
1867 custtrx_prev.trx_date ) TAX_DATE,
1868 DECODE(custtrxl.previous_customer_trx_id,
1869 NULL, custtrx.trx_date,
1870 custtrx_prev.trx_date ) TAX_DETERMINE_DATE,
1871 DECODE(custtrxl.previous_customer_trx_id,
1872 NULL, custtrx.trx_date,
1873 custtrx_prev.trx_date ) TAX_POINT_DATE,
1874 taxes.tax_type_code TAX_TYPE_CODE,
1875 --NULL TAX_CODE,
1876 --NULL TAX_REGISTRATION_ID,
1877 --NULL TAX_REGISTRATION_NUMBER,
1878 --NULL REGISTRATION_PARTY_TYPE,
1879 decode (arsysparam.TRX_HEADER_LEVEL_ROUNDING,
1880 'Y', 'HEADER',
1881 'LINE') ROUNDING_LEVEL_CODE,
1882 arsysparam.TAX_ROUNDING_RULE ROUNDING_RULE_CODE,
1883 --NULL ROUNDING_LVL_PARTY_TAX_PROF_ID,
1884 --NULL ROUNDING_LVL_PARTY_TYPE,
1885 --NULL ORIG_TAX_STATUS_ID,
1886 --NULL ORIG_TAX_STATUS_CODE,
1887 --NULL ORIG_TAX_RATE_ID,
1888 --NULL ORIG_TAX_RATE_CODE,
1889 --NULL ORIG_TAX_RATE,
1890 --NULL ORIG_TAX_JURISDICTION_ID,
1891 --NULL ORIG_TAX_JURISDICTION_CODE,
1892 --NULL ORIG_TAX_AMT_INCLUDED_FLAG,
1893 --NULL ORIG_SELF_ASSESSED_FLAG,
1894 taxes.tax_currency_code TAX_CURRENCY_CODE,
1895 custtrxl.extended_amount TAX_AMT,
1896 (CASE
1897 WHEN custtrxl.global_attribute_category
1898 IN( 'JL.BR.ARXTWMAI.Additional Info',
1899 'JL.CO.ARXTWMAI.LINES',
1900 'JL.AR.ARXTWMAI.LINES')
1901 THEN
1902 DECODE(LTRIM(custtrxl.global_attribute19,'-.0123456789'),
1903 NULL,TO_NUMBER(custtrxl.global_attribute19)*
1904 NVL(custtrx.exchange_rate,1),
1905 NULL)
1906 ELSE
1907 custtrxl.extended_amount *
1908 NVL(custtrx.exchange_rate,1)
1909 END) TAX_AMT_TAX_CURR,
1910
1911 (CASE
1912 WHEN custtrxl.global_attribute_category
1913 IN( 'JL.BR.ARXTWMAI.Additional Info',
1914 'JL.CO.ARXTWMAI.LINES',
1915 'JL.AR.ARXTWMAI.LINES')
1916 THEN
1917 DECODE(LTRIM(custtrxl.global_attribute19,'-.0123456789'),
1918 NULL,TO_NUMBER(custtrxl.global_attribute19)*
1919 NVL(custtrx.exchange_rate,1),
1920 NULL)
1921 ELSE
1922 custtrxl.extended_amount *
1923 NVL(custtrx.exchange_rate,1)
1924 END) TAX_AMT_FUNCL_CURR,
1925
1926 (CASE
1927 WHEN custtrxl.global_attribute_category
1928 IN ( 'JL.BR.ARXTWMAI.Additional Info',
1929 'JL.CO.ARXTWMAI.LINES',
1930 'JL.AR.ARXTWMAI.LINES')
1931 THEN
1932 DECODE(LTRIM(custtrxl.global_attribute11,'-.0123456789'),
1933 NULL,TO_NUMBER(custtrxl.global_attribute11),
1934 NULL)
1935 ELSE
1936 custtrxl.taxable_amount
1937 END) TAXABLE_AMT,
1938
1939 (CASE
1940 WHEN custtrxl.global_attribute_category
1941 IN ( 'JL.BR.ARXTWMAI.Additional Info',
1942 'JL.CO.ARXTWMAI.LINES',
1943 'JL.AR.ARXTWMAI.LINES')
1944 THEN
1945 DECODE(LTRIM(custtrxl.global_attribute11,'-.0123456789'),
1946 NULL,TO_NUMBER(custtrxl.global_attribute11)*
1947 NVL(custtrx.exchange_rate,1),
1948 NULL)
1949 ELSE
1950 custtrxl.taxable_amount*
1951 NVL(custtrx.exchange_rate,1)
1952 END) TAXABLE_AMT_TAX_CURR,
1953
1954 (CASE
1955 WHEN custtrxl.global_attribute_category
1956 IN ( 'JL.BR.ARXTWMAI.Additional Info',
1957 'JL.CO.ARXTWMAI.LINES',
1958 'JL.AR.ARXTWMAI.LINES')
1959 THEN
1960 DECODE(LTRIM(custtrxl.global_attribute11,'-.0123456789'),
1961 NULL,TO_NUMBER(custtrxl.global_attribute11)*
1962 NVL(custtrx.exchange_rate,1),
1963 NULL)
1964 ELSE
1965 custtrxl.taxable_amount*
1966 NVL(custtrx.exchange_rate,1)
1967 END) TAXABLE_AMT_FUNCL_CURR,
1968
1969 --NULL ORIG_TAXABLE_AMT,
1970 --NULL ORIG_TAXABLE_AMT_TAX_CURR,
1971
1972 (CASE
1973 WHEN custtrxl.global_attribute_category
1974 IN ( 'JL.BR.ARXTWMAI.Additional Info',
1975 'JL.CO.ARXTWMAI.LINES',
1976 'JL.AR.ARXTWMAI.LINES')
1977 THEN
1978 DECODE(LTRIM(custtrxl.global_attribute20,'-.0123456789'),
1979 NULL,TO_NUMBER(custtrxl.global_attribute20),
1980 NULL)
1981 END) CAL_TAX_AMT,
1982
1983 (CASE
1984 WHEN custtrxl.global_attribute_category
1985 IN ( 'JL.BR.ARXTWMAI.Additional Info',
1986 'JL.CO.ARXTWMAI.LINES',
1987 'JL.AR.ARXTWMAI.LINES')
1988 THEN
1989 DECODE(LTRIM(custtrxl.global_attribute20,'-.0123456789'),
1990 NULL,TO_NUMBER(custtrxl.global_attribute20)*
1991 NVL(custtrx.EXCHANGE_RATE,1),
1992 NULL)
1993 END) CAL_TAX_AMT_TAX_CURR,
1994
1995 (CASE
1996 WHEN custtrxl.global_attribute_category
1997 IN ( 'JL.BR.ARXTWMAI.Additional Info',
1998 'JL.CO.ARXTWMAI.LINES',
1999 'JL.AR.ARXTWMAI.LINES')
2000 THEN
2001 DECODE(LTRIM(custtrxl.global_attribute20,'-.0123456789'),
2002 NULL,TO_NUMBER(custtrxl.global_attribute20)*
2003 NVL(custtrx.EXCHANGE_RATE,1),
2004 NULL)
2005 END) CAL_TAX_AMT_FUNCL_CURR,
2006
2007 --NULL ORIG_TAX_AMT,
2008 --NULL ORIG_TAX_AMT_TAX_CURR,
2009 --NULL REC_TAX_AMT,
2010 --NULL REC_TAX_AMT_TAX_CURR,
2011 --NULL REC_TAX_AMT_FUNCL_CURR,
2012 --NULL NREC_TAX_AMT,
2013 --NULL NREC_TAX_AMT_TAX_CURR,
2014 --NULL NREC_TAX_AMT_FUNCL_CURR,
2015 custtrxl.TAX_EXEMPTION_ID TAX_EXEMPTION_ID,
2016 --NULL TAX_RATE_BEFORE_EXEMPTION,
2017 --NULL TAX_RATE_NAME_BEFORE_EXEMPTION,
2018 --NULL EXEMPT_RATE_MODIFIER,
2019 custtrxl.item_exception_rate_id TAX_EXCEPTION_ID,
2020 DECODE(rates.rate_type_code,
2021 'PERCENTAGE', rates.percentage_rate,
2022 'QUANTITY', rates.quantity_rate,
2023 NULL) TAX_RATE_BEFORE_EXCEPTION,
2024 --NULL TAX_RATE_NAME_BEFORE_EXCEPTION,
2025 --NULL EXCEPTION_RATE,
2026 --'N' TAX_APPORTIONMENT_FLAG,
2027 -- DECODE(vat.taxable_basis,
2028 -- 'AFTER_EPD', 'STANDARD_TB_DISCOUNT',
2029 -- 'QUANTITY', 'STANDARD_QUANTITY',
2030 -- 'STANDARD_TB') TAXABLE_BASIS_FORMULA,
2031 -- 'STANDARD_TC' TAX_CALCULATION_FORMULA,
2032 NVL(rates.taxable_basis_formula_code,
2033 taxes.def_taxable_basis_formula) TAXABLE_BASIS_FORMULA,
2034 NVL(taxes.def_tax_calc_formula,
2035 'STANDARD_TC') TAX_CALCULATION_FORMULA,
2036 --'N' CANCEL_FLAG,
2037 --'N' PURGE_FLAG,
2038 --'N' DELETE_FLAG,
2039 --'N' SELF_ASSESSED_FLAG,
2040 --'N' OVERRIDDEN_FLAG,
2041 --'N' MANUALLY_ENTERED_FLAG,
2042 --'N' REPORTING_ONLY_FLAG,
2043 --'N' FREEZE_UNTIL_OVERRIDDEN_FLAG,
2044 --'N' COPIED_FROM_OTHER_DOC_FLAG,
2045 --'N' RECALC_REQUIRED_FLAG,
2046 --'N' SETTLEMENT_FLAG,
2047 --'N' ITEM_DIST_CHANGED_FLAG,
2048 --'N' ASSOCIATED_CHILD_FROZEN_FLAG,
2049 DECODE(memoline.line_type, 'TAX', 'Y', 'N') TAX_ONLY_LINE_FLAG,
2050 --'N' COMPOUNDING_DEP_TAX_FLAG,
2051 --'N' ENFORCE_FROM_NATURAL_ACCT_FLAG,
2052 --'N' COMPOUNDING_TAX_MISS_FLAG,
2053 --'N' SYNC_WITH_PRVDR_FLAG,
2054 --NULL LAST_MANUAL_ENTRY,
2055 decode(arsysparam.tax_database_view_set,'_A',2,'_V',1, NULL)
2056 TAX_PROVIDER_ID,
2057 --NULL REPORTING_PERIOD_ID,
2058 --NULL LEGAL_MESSAGE_APPL_2,
2059 --NULL LEGAL_MESSAGE_STATUS,
2060 --NULL LEGAL_MESSAGE_RATE,
2061 --NULL LEGAL_MESSAGE_BASIS,
2062 --NULL LEGAL_MESSAGE_CALC,
2063 --NULL LEGAL_MESSAGE_THRESHOLD,
2064 --NULL LEGAL_MESSAGE_POS,
2065 --NULL LEGAL_MESSAGE_TRN,
2066 --NULL LEGAL_MESSAGE_EXMPT,
2067 --NULL LEGAL_MESSAGE_EXCPT,
2068 --NULL TAX_REGIME_TEMPLATE_ID,
2069 --NULL TAX_APPLICABILITY_RESULT_ID,
2070 --NULL DIRECT_RATE_RESULT_ID,
2071 --NULL STATUS_RESULT_ID,
2072 --NULL RATE_RESULT_ID,
2073 --NULL BASIS_RESULT_ID,
2074 --NULL THRESH_RESULT_ID,
2075 --NULL CALC_RESULT_ID,
2076 --NULL TAX_REG_NUM_DET_RESULT_ID,
2077 --NULL EVAL_EXMPT_RESULT_ID,
2078 --NULL EVAL_EXCPT_RESULT_ID,
2079 --NULL TAX_HOLD_CODE,
2080 --NULL TAX_HOLD_RELEASED_CODE,
2081 --NULL PRD_TOTAL_TAX_AMT,
2082 --NULL PRD_TOTAL_TAX_AMT_TAX_CURR,
2083 --NULL PRD_TOTAL_TAX_AMT_FUNCL_CURR,
2084 custtrxl.GLOBAL_ATTRIBUTE8 LEGAL_JUSTIFICATION_TEXT1,
2085 custtrxl.GLOBAL_ATTRIBUTE9 LEGAL_JUSTIFICATION_TEXT2,
2086 custtrxl.GLOBAL_ATTRIBUTE10 LEGAL_JUSTIFICATION_TEXT3,
2087 --NULL REPORTING_CURRENCY_CODE,
2088 --NULL LINE_ASSESSABLE_VALUE,
2089 --NULL TRX_LINE_INDEX,
2090 --NULL OFFSET_TAX_RATE_CODE,
2091 --NULL PRORATION_CODE,
2092 --NULL OTHER_DOC_SOURCE,
2093 --NULL MRC_LINK_TO_TAX_LINE_ID,
2094 --NULL APPLIED_TO_TRX_NUMBER,
2095 --NULL INTERFACE_TAX_LINE_ID,
2096 --NULL TAXING_JURIS_GEOGRAPHY_ID,
2097 decode(arsysparam.tax_database_view_Set ,
2098 '_A',decode(custtrxl.global_attribute1,'ALL',
2099 custtrxl.global_Attribute2,null),
2100 '_V',decode(custtrxl.global_attribute1,'ALL',
2101 custtrxl.global_Attribute2,null),
2102 NULL) numeric1,
2103 decode(arsysparam.tax_database_view_Set ,
2104 '_A',decode(custtrxl.global_attribute1,'ALL',
2105 custtrxl.global_Attribute4,null),
2106 '_V',decode(custtrxl.global_attribute1,'ALL',
2107 custtrxl.global_Attribute4,null),
2108 NULL) numeric2,
2109 decode(arsysparam.tax_database_view_Set ,
2110 '_A',decode(custtrxl.global_attribute1,'ALL',
2111 custtrxl.global_Attribute6,null),
2112 '_V',decode(custtrxl.global_attribute1,'ALL',
2113 custtrxl.global_Attribute6,null),
2114 NULL) numeric3,
2115 decode(arsysparam.tax_database_view_Set,
2116 '_A',
2117 decode(custtrxl.global_attribute1,'ALL',
2118 to_number(substrb(custtrxl.global_Attribute12,1,
2119 instrb(custtrxl.global_Attribute12,'|',1,1)-1)),
2120 'STATE',
2121 to_number(substrb(custtrxl.global_Attribute12,1,
2122 instrb(custtrxl.global_Attribute12,'|',1,1)-1)),
2123 NULL),
2124 '_V',
2125 decode(custtrxl.global_attribute1,'ALL',
2126 to_number(substrb(custtrxl.global_Attribute12,1,
2127 instrb(custtrxl.global_Attribute12,'|',1,1)-1)),
2128 'STATE',
2129 to_number(substrb(custtrxl.global_Attribute12,1,
2130 instrb(custtrxl.global_Attribute12,'|',1,1)-1)),
2131 NULL)
2132 ,NULL) numeric4,
2133
2134 --DECODE(custtrxl.line_type,
2135 -- 'TAX', custtrxl.previous_customer_trx_line_id,
2136 -- NULL) ADJUSTED_DOC_TAX_LINE_ID,
2137 decode(custtrxl_prev.line_type, 'TAX', custtrxl_prev.tax_line_id, null) ADJUSTED_DOC_TAX_LINE_ID,
2138 custtrxl.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,
2139 custtrxl.ATTRIBUTE1 ATTRIBUTE1,
2140 custtrxl.ATTRIBUTE2 ATTRIBUTE2,
2141 custtrxl.ATTRIBUTE3 ATTRIBUTE3,
2142 custtrxl.ATTRIBUTE4 ATTRIBUTE4,
2143 custtrxl.ATTRIBUTE5 ATTRIBUTE5,
2144 custtrxl.ATTRIBUTE6 ATTRIBUTE6,
2145 custtrxl.ATTRIBUTE7 ATTRIBUTE7,
2146 custtrxl.ATTRIBUTE8 ATTRIBUTE8,
2147 custtrxl.ATTRIBUTE9 ATTRIBUTE9,
2148 custtrxl.ATTRIBUTE10 ATTRIBUTE10,
2149 custtrxl.ATTRIBUTE11 ATTRIBUTE11,
2150 custtrxl.ATTRIBUTE12 ATTRIBUTE12,
2151 custtrxl.ATTRIBUTE13 ATTRIBUTE13,
2152 custtrxl.ATTRIBUTE14 ATTRIBUTE14,
2153 custtrxl.ATTRIBUTE15 ATTRIBUTE15,
2154 custtrxl.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY,
2155 custtrxl.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1,
2156 custtrxl.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2,
2157 custtrxl.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3,
2158 custtrxl.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4,
2159 custtrxl.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5,
2160 custtrxl.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6,
2161 custtrxl.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7,
2162 custtrxl.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8,
2163 custtrxl.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9,
2164 custtrxl.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10,
2165 custtrxl.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11,
2166 custtrxl.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12,
2167 custtrxl.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13,
2168 custtrxl.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14,
2169 custtrxl.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15,
2170 custtrxl.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16,
2171 custtrxl.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17,
2172 custtrxl.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18,
2173 custtrxl.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19,
2174 custtrxl.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20,
2175 --'N' MULTIPLE_JURISDICTIONS_FLAG,
2176 SYSDATE CREATION_DATE,
2177 1 CREATED_BY,
2178 SYSDATE LAST_UPDATE_DATE,
2179 1 LAST_UPDATED_BY,
2180 0 LAST_UPDATE_LOGIN,
2181 DECODE(custtrx.complete_flag,
2182 'Y', '111111111111111',
2183 '000000000000000') LEGAL_REPORTING_STATUS,
2184 DECODE(vat.tax_type,
2185 'LOCATION', NULL,
2186 custtrxl.vat_tax_id) ACCOUNT_SOURCE_TAX_RATE_ID,
2187 custtrxl.autotax AUTOTAX
2188 FROM RA_CUSTOMER_TRX_ALL custtrx,
2189 XLA_UPGRADE_DATES upd,
2190 AR_SYSTEM_PARAMETERS_ALL arsysparam,
2191 RA_CUST_TRX_TYPES_ALL types,
2192 FND_CURRENCIES fndcurr,
2193 FND_DOCUMENT_SEQUENCES fds,
2194 ZX_PARTY_TAX_PROFILE ptp,
2195 RA_BATCH_SOURCES_ALL rbs,
2196 RA_CUSTOMER_TRX_ALL custtrx_prev,
2197 RA_CUSTOMER_TRX_LINES_ALL custtrxl_prev,
2198 RA_CUSTOMER_TRX_LINES_ALL custtrxl,
2199 AR_VAT_TAX_ALL_B vat,
2200 ZX_RATES_B rates ,
2201 RA_CUSTOMER_TRX_LINES_ALL custtrxll, -- retrieve the trx line for tax lines
2202 AR_MEMO_LINES_ALL_B memoline,
2203 ZX_REGIMES_B regimes,
2204 ZX_TAXES_B taxes,
2205 ZX_STATUS_B status
2206 WHERE custtrx.rowid BETWEEN p_start_rowid AND p_end_rowid
2207 AND custtrx.customer_trx_id = custtrxl.customer_trx_id
2208 AND custtrx.previous_customer_trx_id = custtrx_prev.customer_trx_id(+)
2209 AND custtrxl.previous_customer_trx_line_id = custtrxl_prev.customer_trx_line_id(+)
2210 AND upd.ledger_id = custtrx.set_of_books_id
2211 AND (custtrx.trx_date between upd.start_date and upd.end_date )
2212 AND (case when (custtrxl.line_type IN ('LINE' ,'CB')) then custtrxl.customer_trx_line_id
2213 when (custtrxl.line_type = 'TAX') then custtrxl.link_to_cust_trx_line_id
2214 end ) = custtrxll.customer_trx_line_id
2215 AND ((custtrxl.line_type = 'TAX' AND custtrxll.line_type = 'LINE')
2216 OR
2217 (custtrxl.line_type <> 'TAX'))
2218
2219 AND custtrx.cust_trx_type_id = types.cust_trx_type_id
2220 AND types.type in ('INV','CM', 'DM')
2221 AND decode(l_multi_org_flag,'N',l_org_id, custtrx.org_id) =
2222 decode(l_multi_org_flag,'N',l_org_id, types.org_id)
2223 AND custtrx.invoice_currency_code = fndcurr.currency_code
2224 AND custtrx.doc_sequence_id = fds.doc_sequence_id (+)
2225 AND ptp.party_id = decode(l_multi_org_flag,'N',l_org_id, custtrx.org_id)
2226 AND ptp.party_type_code = 'OU'
2227 AND custtrx.batch_source_id = rbs.batch_source_id(+)
2228 AND decode(l_multi_org_flag,'N',l_org_id, custtrx.org_id) =
2229 decode(l_multi_org_flag,'N',l_org_id, rbs.org_id(+))
2230 AND custtrxl.vat_tax_id = vat.vat_tax_id(+)
2231 AND custtrx.org_id = arsysparam.org_id
2232 AND custtrxl.vat_Tax_id = rates.tax_rate_id(+)
2233 AND custtrxll.memo_line_id = memoline.memo_line_id(+)
2234 AND decode(l_multi_org_flag,'N',l_org_id, custtrxll.org_id) = decode(l_multi_org_flag,'N',l_org_id, memoline.org_id(+))
2235 AND rates.tax_regime_code = regimes.tax_regime_code(+)
2236 AND rates.tax_regime_code = taxes.tax_regime_code(+)
2237 AND rates.tax = taxes.tax(+)
2238 AND rates.content_owner_id = taxes.content_owner_id(+)
2239 AND rates.tax_regime_code = status.tax_regime_code(+)
2240 AND rates.tax = status.tax(+)
2241 AND rates.tax_status_code = status.tax_status_code(+)
2242 AND rates.content_owner_id = status.content_owner_id(+)
2243 AND NVL(arsysparam.tax_code, '!') <> 'Localization'
2244 AND NOT EXISTS
2245 (SELECT 1 FROM zx_lines_det_factors zxl
2246 WHERE zxl.APPLICATION_ID = 222
2247 AND zxl.EVENT_CLASS_CODE = DECODE(types.type,
2248 'INV','INVOICE',
2249 'CM', 'CREDIT_MEMO',
2250 'DM', 'DEBIT_MEMO',
2251 'NONE')
2252 AND zxl.ENTITY_CODE = 'TRANSACTIONS'
2253 AND zxl.TRX_ID = custtrx.customer_trx_id
2254 );
2255
2256 x_rows_processed := SQL%ROWCOUNT;
2257
2258 IF g_level_procedure >= g_current_runtime_level THEN
2259 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_AR_TRX_MIG','Worker: '||p_worker_id||' x_rows_processed is ' || x_rows_processed );
2260 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_AR_TRX_MIG','Worker: '||p_worker_id||' zx_ar_trx_mig (-)' );
2261 END IF;
2262
2263 EXCEPTION
2264 WHEN OTHERS THEN
2265 X_retcode := CONC_FAIL;
2266 IF g_level_unexpected >= g_current_runtime_level THEN
2267 FND_LOG.STRING(g_level_unexpected,
2268 'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_AR_TRX_MIG',
2269 'Worker: '||p_worker_id||'Raised exceptions: '||
2270 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80) );
2271 END IF;
2272 raise;
2273
2274 END zx_ar_trx_mig;
2275
2276 /**************************************************************/
2277
2278 PROCEDURE zx_ap_trx_mig (x_errbuf OUT NOCOPY VARCHAR2,
2279 x_retcode OUT NOCOPY VARCHAR2,
2280 p_start_rowid IN ROWID,
2281 p_end_rowid IN ROWID,
2282 p_org_id IN NUMBER,
2283 p_multi_org_flag IN VARCHAR2,
2284 p_worker_id IN NUMBER,
2285 x_rows_processed OUT NOCOPY NUMBER)
2286 IS
2287 l_multi_org_flag VARCHAR2(1);
2288 l_org_id NUMBER;
2289 BEGIN
2290 l_multi_org_flag := p_multi_org_flag;
2291 l_org_id := p_org_id;
2292
2293
2294 IF g_level_procedure >= g_current_runtime_level THEN
2295 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_AP_TRX_MIG','Worker: '||p_worker_id||' zx_ap_trx_mig (+)' );
2296 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_AP_TRX_MIG','Worker: '||p_worker_id||' p_start_rowid is ' || p_start_rowid );
2297 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_AP_TRX_MIG','Worker: '||p_worker_id||'p_end_rowid is ' || p_end_rowid );
2298 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_AP_TRX_MIG','Worker: '||p_worker_id||'p_org_id is ' || p_org_id );
2299 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_AP_TRX_MIG','Worker: '||p_worker_id||'p_multi_org_flag is ' || p_multi_org_flag );
2300 END IF;
2301
2302 x_retcode := CONC_SUCCESS;
2303
2304 -- Insert data into zx_lines_det_factors and zx_lines_summary
2305 --
2306 INSERT ALL
2307 WHEN AP_LINE_LOOKUP_CODE IN ('ITEM', 'PREPAY','FREIGHT','MISCELLANEOUS') OR
2308 TAX_ONLY_LINE_FLAG = 'Y'
2309 THEN
2310 INTO ZX_LINES_DET_FACTORS (
2311 --EVENT_ID
2312 OBJECT_VERSION_NUMBER
2313 ,INTERNAL_ORGANIZATION_ID
2314 ,APPLICATION_ID
2315 ,ENTITY_CODE
2316 ,EVENT_CLASS_CODE
2317 ,EVENT_TYPE_CODE
2318 ,TAX_EVENT_CLASS_CODE
2319 ,TAX_EVENT_TYPE_CODE
2320 ,LINE_LEVEL_ACTION
2321 ,LINE_CLASS
2322 ,TRX_ID
2323 ,TRX_LINE_ID
2324 ,TRX_LEVEL_TYPE
2325 ,TRX_DATE
2326 ,LEDGER_ID
2327 ,TRX_CURRENCY_CODE
2328 ,CURRENCY_CONVERSION_DATE
2329 ,CURRENCY_CONVERSION_RATE
2330 ,CURRENCY_CONVERSION_TYPE
2331 ,MINIMUM_ACCOUNTABLE_UNIT
2332 ,PRECISION
2333 ,LEGAL_ENTITY_ID
2334 ,DEFAULT_TAXATION_COUNTRY
2335 ,TRX_NUMBER
2336 ,TRX_LINE_NUMBER
2337 ,TRX_LINE_DESCRIPTION
2338 ,TRX_DESCRIPTION
2339 ,TRX_COMMUNICATED_DATE
2340 ,TRX_LINE_GL_DATE
2341 ,BATCH_SOURCE_ID
2342 ,DOC_SEQ_ID
2343 ,DOC_SEQ_NAME
2344 ,DOC_SEQ_VALUE
2345 ,TRX_DUE_DATE
2346 ,TRX_LINE_TYPE
2347 ,TRX_LINE_DATE
2348 ,LINE_AMT
2349 ,TRX_LINE_QUANTITY
2350 ,UNIT_PRICE
2351 ,PRODUCT_ID
2352 ,UOM_CODE
2353 ,PRODUCT_TYPE
2354 ,PRODUCT_DESCRIPTION
2355 ,FIRST_PTY_ORG_ID
2356 ,ACCOUNT_CCID
2357 ,APPLIED_FROM_APPLICATION_ID
2358 ,APPLIED_FROM_ENTITY_CODE
2359 ,APPLIED_FROM_EVENT_CLASS_CODE
2360 ,APPLIED_FROM_TRX_ID
2361 ,APPLIED_FROM_LINE_ID
2362 ,ADJUSTED_DOC_APPLICATION_ID
2363 ,ADJUSTED_DOC_ENTITY_CODE
2364 ,ADJUSTED_DOC_EVENT_CLASS_CODE
2365 ,ADJUSTED_DOC_TRX_ID
2366 ,ADJUSTED_DOC_LINE_ID
2367 ,APPLIED_TO_APPLICATION_ID
2368 ,APPLIED_TO_ENTITY_CODE
2369 ,APPLIED_TO_EVENT_CLASS_CODE
2370 ,APPLIED_TO_TRX_ID
2371 ,APPLIED_TO_TRX_LINE_ID
2372 ,REF_DOC_TRX_LEVEL_TYPE
2373 ,REF_DOC_APPLICATION_ID
2374 ,REF_DOC_ENTITY_CODE
2375 ,REF_DOC_EVENT_CLASS_CODE
2376 ,REF_DOC_TRX_ID
2377 ,REF_DOC_LINE_ID
2378 ,APPLIED_TO_TRX_LEVEL_TYPE
2379 ,APPLIED_FROM_TRX_LEVEL_TYPE
2380 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
2381 ,MERCHANT_PARTY_NAME
2382 ,MERCHANT_PARTY_DOCUMENT_NUMBER
2383 ,MERCHANT_PARTY_REFERENCE
2384 ,MERCHANT_PARTY_TAXPAYER_ID
2385 ,MERCHANT_PARTY_TAX_REG_NUMBER
2386 ,MERCHANT_PARTY_COUNTRY
2387 ,START_EXPENSE_DATE
2388 ,SHIP_TO_LOCATION_ID
2389 ,RECORD_TYPE_CODE
2390 ,PRODUCT_FISC_CLASSIFICATION
2391 ,PRODUCT_CATEGORY
2392 ,USER_DEFINED_FISC_CLASS
2393 ,ASSESSABLE_VALUE
2394 ,TRX_BUSINESS_CATEGORY
2395 ,SUPPLIER_TAX_INVOICE_NUMBER
2396 ,SUPPLIER_TAX_INVOICE_DATE
2397 ,SUPPLIER_EXCHANGE_RATE
2398 ,TAX_INVOICE_DATE
2399 ,TAX_INVOICE_NUMBER
2400 ,DOCUMENT_SUB_TYPE
2401 ,LINE_INTENDED_USE
2402 ,PORT_OF_ENTRY_CODE
2403 ,HISTORICAL_FLAG
2404 ,LINE_AMT_INCLUDES_TAX_FLAG
2405 ,CTRL_HDR_TX_APPL_FLAG
2406 ,TAX_REPORTING_FLAG
2407 ,TAX_AMT_INCLUDED_FLAG
2408 ,COMPOUNDING_TAX_FLAG
2409 ,INCLUSIVE_TAX_OVERRIDE_FLAG
2410 ,THRESHOLD_INDICATOR_FLAG
2411 ,USER_UPD_DET_FACTORS_FLAG
2412 ,TAX_PROCESSING_COMPLETED_FLAG
2413 ,ASSET_FLAG
2414 ,CREATION_DATE
2415 ,CREATED_BY
2416 ,LAST_UPDATE_DATE
2417 ,LAST_UPDATED_BY
2418 ,LAST_UPDATE_LOGIN
2419 ,EVENT_CLASS_MAPPING_ID
2420 ,SHIP_THIRD_PTY_ACCT_ID
2421 ,SHIP_THIRD_PTY_ACCT_SITE_ID
2422 ,GLOBAL_ATTRIBUTE_CATEGORY
2423 ,GLOBAL_ATTRIBUTE1
2424 ,BILL_THIRD_PTY_ACCT_ID
2425 ,BILL_THIRD_PTY_ACCT_SITE_ID
2426 )
2427 VALUES(
2428 -- -9999
2429 1
2430 ,INTERNAL_ORGANIZATION_ID
2431 ,200
2432 ,'AP_INVOICES'
2433 ,EVENT_CLASS_CODE
2434 ,EVENT_TYPE_CODE
2435 ,TAX_EVENT_CLASS_CODE
2436 ,'VALIDATE'
2437 ,'CREATE'
2438 ,LINE_CLASS
2439 ,TRX_ID
2440 ,TRX_LINE_ID
2441 ,'LINE'
2442 ,TRX_DATE
2443 ,LEDGER_ID
2444 ,TRX_CURRENCY_CODE
2445 ,CURRENCY_CONVERSION_DATE
2446 ,CURRENCY_CONVERSION_RATE
2447 ,CURRENCY_CONVERSION_TYPE
2448 ,MINIMUM_ACCOUNTABLE_UNIT
2449 ,PRECISION
2450 ,LEGAL_ENTITY_ID
2451 ,DEFAULT_TAXATION_COUNTRY
2452 ,TRX_NUMBER
2453 ,TRX_LINE_NUMBER
2454 ,TRX_LINE_DESCRIPTION
2455 ,TRX_DESCRIPTION
2456 ,TRX_COMMUNICATED_DATE
2457 ,TRX_LINE_GL_DATE
2458 ,BATCH_SOURCE_ID
2459 ,DOC_SEQ_ID
2460 ,DOC_SEQ_NAME
2461 ,DOC_SEQ_VALUE
2462 ,TRX_DUE_DATE
2463 ,TRX_LINE_TYPE
2464 ,TRX_LINE_DATE
2465 ,LINE_AMT
2466 ,TRX_LINE_QUANTITY
2467 ,UNIT_PRICE
2468 ,PRODUCT_ID
2469 ,UOM_CODE
2470 ,PRODUCT_TYPE
2471 ,PRODUCT_DESCRIPTION
2472 ,FIRST_PTY_ORG_ID
2473 ,ACCOUNT_CCID
2474 ,APPLIED_FROM_APPLICATION_ID
2475 ,APPLIED_FROM_ENTITY_CODE
2476 ,APPLIED_FROM_EVENT_CLASS_CODE
2477 ,APPLIED_FROM_TRX_ID
2478 ,APPLIED_FROM_LINE_ID
2479 ,ADJUSTED_DOC_APPLICATION_ID
2480 ,ADJUSTED_DOC_ENTITY_CODE
2481 ,ADJUSTED_DOC_EVENT_CLASS_CODE
2482 ,ADJUSTED_DOC_TRX_ID
2483 ,ADJUSTED_DOC_LINE_ID
2484 ,APPLIED_TO_APPLICATION_ID
2485 ,APPLIED_TO_ENTITY_CODE
2486 ,APPLIED_TO_EVENT_CLASS_CODE
2487 ,APPLIED_TO_TRX_ID
2488 ,APPLIED_TO_TRX_LINE_ID
2489 ,REF_DOC_TRX_LEVEL_TYPE
2490 ,REF_DOC_APPLICATION_ID
2491 ,REF_DOC_ENTITY_CODE
2492 ,REF_DOC_EVENT_CLASS_CODE
2493 ,REF_DOC_TRX_ID
2494 ,REF_DOC_LINE_ID
2495 ,APPLIED_TO_TRX_LEVEL_TYPE
2496 ,APPLIED_FROM_TRX_LEVEL_TYPE
2497 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
2498 ,MERCHANT_PARTY_NAME
2499 ,MERCHANT_PARTY_DOCUMENT_NUMBER
2500 ,MERCHANT_PARTY_REFERENCE
2501 ,MERCHANT_PARTY_TAXPAYER_ID
2502 ,MERCHANT_PARTY_TAX_REG_NUMBER
2503 ,MERCHANT_PARTY_COUNTRY
2504 ,START_EXPENSE_DATE
2505 ,SHIP_TO_LOCATION_ID
2506 ,'MIGRATED'
2507 ,PRODUCT_FISC_CLASSIFICATION
2508 ,PRODUCT_CATEGORY
2509 ,USER_DEFINED_FISC_CLASS
2510 ,ASSESSABLE_VALUE
2511 ,TRX_BUSINESS_CATEGORY
2512 ,SUPPLIER_TAX_INVOICE_NUMBER
2513 ,SUPPLIER_TAX_INVOICE_DATE
2514 ,SUPPLIER_EXCHANGE_RATE
2515 ,TAX_INVOICE_DATE
2516 ,TAX_INVOICE_NUMBER
2517 ,DOCUMENT_SUB_TYPE
2518 ,LINE_INTENDED_USE
2519 ,PORT_OF_ENTRY_CODE
2520 ,'Y'
2521 ,'N'
2522 ,'N'
2523 ,'Y'
2524 ,'N'
2525 ,'N'
2526 ,'N'
2527 ,'N'
2528 ,'N'
2529 ,'N'
2530 ,ASSET_FLAG
2531 ,sysdate
2532 ,1
2533 ,sysdate
2534 ,1
2535 ,1
2536 ,EVENT_CLASS_MAPPING_ID
2537 ,SHIP_THIRD_PTY_ACCT_ID
2538 ,SHIP_THIRD_PTY_ACCT_SITE_ID
2539 ,GLOBAL_ATTRIBUTE_CATEGORY
2540 ,GLOBAL_ATTRIBUTE1
2541 ,BILL_THIRD_PTY_ACCT_ID
2542 ,BILL_THIRD_PTY_ACCT_SITE_ID
2543 )
2544 WHEN AP_LINE_LOOKUP_CODE = 'TAX' THEN
2545 INTO ZX_LINES_SUMMARY (
2546 SUMMARY_TAX_LINE_ID
2547 ,INTERNAL_ORGANIZATION_ID
2548 ,APPLICATION_ID
2549 ,ENTITY_CODE
2550 ,EVENT_CLASS_CODE
2551 ,TRX_ID
2552 ,TRX_NUMBER
2553 ,APPLIED_FROM_APPLICATION_ID
2554 ,APPLIED_FROM_EVENT_CLASS_CODE
2555 ,APPLIED_FROM_ENTITY_CODE
2556 ,APPLIED_FROM_TRX_ID
2557 ,ADJUSTED_DOC_APPLICATION_ID
2558 ,ADJUSTED_DOC_ENTITY_CODE
2559 ,ADJUSTED_DOC_EVENT_CLASS_CODE
2560 ,ADJUSTED_DOC_TRX_ID
2561 ,SUMMARY_TAX_LINE_NUMBER
2562 ,CONTENT_OWNER_ID
2563 ,TAX_REGIME_CODE
2564 ,TAX
2565 ,TAX_STATUS_CODE
2566 ,TAX_RATE_ID
2567 ,TAX_RATE_CODE
2568 ,TAX_RATE
2569 ,TAX_AMT
2570 ,TAX_AMT_TAX_CURR
2571 ,TAX_AMT_FUNCL_CURR
2572 ,TAX_JURISDICTION_CODE
2573 ,TOTAL_REC_TAX_AMT
2574 ,TOTAL_REC_TAX_AMT_FUNCL_CURR
2575 ,TOTAL_NREC_TAX_AMT
2576 ,TOTAL_NREC_TAX_AMT_FUNCL_CURR
2577 ,LEDGER_ID
2578 ,LEGAL_ENTITY_ID
2579 ,CURRENCY_CONVERSION_DATE
2580 ,CURRENCY_CONVERSION_TYPE
2581 ,CURRENCY_CONVERSION_RATE
2582 ,TAXABLE_BASIS_FORMULA
2583 ,TAX_CALCULATION_FORMULA
2584 ,HISTORICAL_FLAG
2585 ,CANCEL_FLAG
2586 ,DELETE_FLAG
2587 ,TAX_AMT_INCLUDED_FLAG
2588 ,COMPOUNDING_TAX_FLAG
2589 ,SELF_ASSESSED_FLAG
2590 ,OVERRIDDEN_FLAG
2591 ,REPORTING_ONLY_FLAG
2592 ,ASSOCIATED_CHILD_FROZEN_FLAG
2593 ,COPIED_FROM_OTHER_DOC_FLAG
2594 ,MANUALLY_ENTERED_FLAG
2595 ,LAST_MANUAL_ENTRY --BUG7146063
2596 ,RECORD_TYPE_CODE
2597 ,TAX_ONLY_LINE_FLAG
2598 ,CREATED_BY
2599 ,CREATION_DATE
2600 ,LAST_UPDATED_BY
2601 ,LAST_UPDATE_DATE
2602 ,LAST_UPDATE_LOGIN
2603 ,ATTRIBUTE_CATEGORY
2604 ,ATTRIBUTE1
2605 ,ATTRIBUTE2
2606 ,ATTRIBUTE3
2607 ,ATTRIBUTE4
2608 ,ATTRIBUTE5
2609 ,ATTRIBUTE6
2610 ,ATTRIBUTE7
2611 ,ATTRIBUTE8
2612 ,ATTRIBUTE9
2613 ,ATTRIBUTE10
2614 ,ATTRIBUTE11
2615 ,ATTRIBUTE12
2616 ,ATTRIBUTE13
2617 ,ATTRIBUTE14
2618 ,ATTRIBUTE15
2619 ,APPLIED_FROM_LINE_ID
2620 ,APPLIED_TO_APPLICATION_ID
2621 ,APPLIED_TO_EVENT_CLASS_CODE
2622 ,APPLIED_TO_ENTITY_CODE
2623 ,APPLIED_TO_TRX_ID
2624 ,APPLIED_TO_LINE_ID
2625 ,TOTAL_REC_TAX_AMT_TAX_CURR
2626 ,TOTAL_NREC_TAX_AMT_TAX_CURR
2627 ,MRC_TAX_LINE_FLAG
2628 ,GLOBAL_ATTRIBUTE_CATEGORY
2629 ,GLOBAL_ATTRIBUTE1
2630 ,GLOBAL_ATTRIBUTE2
2631 ,GLOBAL_ATTRIBUTE3
2632 ,GLOBAL_ATTRIBUTE4
2633 ,GLOBAL_ATTRIBUTE5
2634 ,GLOBAL_ATTRIBUTE6
2635 ,GLOBAL_ATTRIBUTE7
2636 ,GLOBAL_ATTRIBUTE8
2637 ,GLOBAL_ATTRIBUTE9
2638 ,GLOBAL_ATTRIBUTE10
2639 ,GLOBAL_ATTRIBUTE11
2640 ,GLOBAL_ATTRIBUTE12
2641 ,GLOBAL_ATTRIBUTE13
2642 ,GLOBAL_ATTRIBUTE14
2643 ,GLOBAL_ATTRIBUTE15
2644 ,GLOBAL_ATTRIBUTE16
2645 ,GLOBAL_ATTRIBUTE17
2646 ,GLOBAL_ATTRIBUTE18
2647 ,GLOBAL_ATTRIBUTE19
2648 ,GLOBAL_ATTRIBUTE20
2649 ,APPLIED_FROM_TRX_LEVEL_TYPE
2650 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
2651 ,APPLIED_TO_TRX_LEVEL_TYPE
2652 ,TRX_LEVEL_TYPE
2653 ,OBJECT_VERSION_NUMBER)
2654 VALUES(
2655 SUMMARY_TAX_LINE_ID
2656 ,INTERNAL_ORGANIZATION_ID
2657 ,200
2658 ,'AP_INVOICES'
2659 ,EVENT_CLASS_CODE
2660 ,TRX_ID
2661 ,TRX_NUMBER
2662 ,APPLIED_FROM_APPLICATION_ID
2663 ,APPLIED_FROM_EVENT_CLASS_CODE
2664 ,APPLIED_FROM_ENTITY_CODE
2665 ,APPLIED_FROM_TRX_ID
2666 ,ADJUSTED_DOC_APPLICATION_ID
2667 ,ADJUSTED_DOC_ENTITY_CODE
2668 ,ADJUSTED_DOC_EVENT_CLASS_CODE
2669 ,ADJUSTED_DOC_TRX_ID
2670 ,SUMMARY_TAX_LINE_NUMBER
2671 ,CONTENT_OWNER_ID
2672 ,TAX_REGIME_CODE
2673 ,TAX
2674 ,TAX_STATUS_CODE
2675 ,TAX_RATE_ID
2676 ,TAX_RATE_CODE
2677 ,TAX_RATE
2678 ,TAX_AMT
2679 ,TAX_AMT_TAX_CURR
2680 ,TAX_AMT_FUNCL_CURR
2681 ,TAX_JURISDICTION_CODE
2682 ,TOTAL_REC_TAX_AMT
2683 ,TOTAL_REC_TAX_AMT_FUNCL_CURR
2684 ,TOTAL_NREC_TAX_AMT
2685 ,TOTAL_NREC_TAX_AMT_FUNCL_CURR
2686 ,LEDGER_ID
2687 ,LEGAL_ENTITY_ID
2688 ,CURRENCY_CONVERSION_DATE
2689 ,CURRENCY_CONVERSION_TYPE
2690 ,CURRENCY_CONVERSION_RATE
2691 --Bug 9037817,'STANDARD_TB'
2692 ,TAXABLE_BASIS_FORMULA
2693 ,'STANDARD_TC'
2694 ,'Y'
2695 ,CANCEL_FLAG
2696 ,'N'
2697 ,'N'
2698 ,'N'
2699 ,'N'
2700 ,'N'
2701 ,'N'
2702 ,'N'
2703 ,'N'
2704 ,MANUALLY_ENTERED_FLAG
2705 ,LAST_MANUAL_ENTRY --BUG7146063
2706 ,'MIGRATED'
2707 ,TAX_ONLY_LINE_FLAG
2708 ,1
2709 ,sysdate
2710 ,1
2711 ,sysdate
2712 ,1
2713 ,ATTRIBUTE_CATEGORY
2714 ,ATTRIBUTE1
2715 ,ATTRIBUTE2
2716 ,ATTRIBUTE3
2717 ,ATTRIBUTE4
2718 ,ATTRIBUTE5
2719 ,ATTRIBUTE6
2720 ,ATTRIBUTE7
2721 ,ATTRIBUTE8
2722 ,ATTRIBUTE9
2723 ,ATTRIBUTE10
2724 ,ATTRIBUTE11
2725 ,ATTRIBUTE12
2726 ,ATTRIBUTE13
2727 ,ATTRIBUTE14
2728 ,ATTRIBUTE15
2729 ,APPLIED_FROM_LINE_ID
2730 ,APPLIED_TO_APPLICATION_ID
2731 ,APPLIED_TO_EVENT_CLASS_CODE
2732 ,APPLIED_TO_ENTITY_CODE
2733 ,APPLIED_TO_TRX_ID
2734 ,APPLIED_TO_TRX_LINE_ID
2735 ,TOTAL_REC_TAX_AMT_FUNCL_CURR
2736 ,TOTAL_NREC_TAX_AMT_FUNCL_CURR
2737 ,'N'
2738 ,GLOBAL_ATTRIBUTE_CATEGORY
2739 ,GLOBAL_ATTRIBUTE1
2740 ,GLOBAL_ATTRIBUTE2
2741 ,GLOBAL_ATTRIBUTE3
2742 ,GLOBAL_ATTRIBUTE4
2743 ,GLOBAL_ATTRIBUTE5
2744 ,GLOBAL_ATTRIBUTE6
2745 ,GLOBAL_ATTRIBUTE7
2746 ,GLOBAL_ATTRIBUTE8
2747 ,GLOBAL_ATTRIBUTE9
2748 ,GLOBAL_ATTRIBUTE10
2749 ,GLOBAL_ATTRIBUTE11
2750 ,GLOBAL_ATTRIBUTE12
2751 ,GLOBAL_ATTRIBUTE13
2752 ,GLOBAL_ATTRIBUTE14
2753 ,GLOBAL_ATTRIBUTE15
2754 ,GLOBAL_ATTRIBUTE16
2755 ,GLOBAL_ATTRIBUTE17
2756 ,GLOBAL_ATTRIBUTE18
2757 ,GLOBAL_ATTRIBUTE19
2758 ,GLOBAL_ATTRIBUTE20
2759 ,APPLIED_FROM_TRX_LEVEL_TYPE
2760 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
2761 ,APPLIED_TO_TRX_LEVEL_TYPE
2762 ,'LINE'
2763 ,1
2764 )
2765 SELECT /*+ ROWID(inv) NO_EXPAND ORDERED swap_join_inputs(upd) use_nl(fnd_curr,fds,poll)
2766 use_nl_with_index(lines AP_INVOICE_LINES_U1)
2767 use_nl_with_index(PTP ZX_PARTY_TAX_PROFILE_U2) */
2768 NVL(lines.org_id,-99) INTERNAL_ORGANIZATION_ID
2769 ,DECODE(inv.INVOICE_TYPE_LOOKUP_CODE,
2770 'STANDARD', 'STANDARD INVOICES', --Bug 5859937
2771 'CREDIT' , 'STANDARD INVOICES', --Bug 5859937
2772 'DEBIT' , 'STANDARD INVOICES', --Bug 5859937
2773 'MIXED' , 'STANDARD INVOICES', --Bug 5859937
2774 'ADJUSTMENT','STANDARD INVOICES', --Bug 5859937
2775 'PO PRICE ADJUST','STANDARD INVOICES', --Bug 5859937
2776 'INVOICE REQUEST','STANDARD INVOICES', --Bug 5859937
2777 'CREDIT MEMO REQUEST','STANDARD INVOICES',--Bug 5859937
2778 'RETAINAGE RELEASE' ,'STANDARD INVOICES',--Bug 5859937
2779 'PREPAYMENT', 'PREPAYMENT INVOICES',
2780 'EXPENSE REPORT', 'EXPENSE REPORTS',
2781 'INTEREST INVOICE', 'INTEREST INVOICES','NA') EVENT_CLASS_CODE
2782 ,DECODE(inv.INVOICE_TYPE_LOOKUP_CODE, 'STANDARD', 1,
2783 'PREPAYMENT', 7, 'EXPENSE REPORT', 2, NULL) EVENT_CLASS_MAPPING_ID
2784 ,DECODE(inv.INVOICE_TYPE_LOOKUP_CODE,
2785 'STANDARD','STANDARD INVOICE CREATED',
2786 'PREPAYMENT','PREPAYMENT INVOICE CREATED',
2787 'EXPENSE REPORT','EXPENSE REPORT CREATED',
2788 'INTEREST INVOICE','INTEREST INVOICE CREATED','NA') EVENT_TYPE_CODE
2789 ,(CASE
2790 WHEN inv.invoice_type_lookup_code in
2791 ('ADJUSTMENT','CREDIT','DEBIT','INTEREST',
2792 'MIXED','QUICKDEFAULT','PO PRICE ADJUST',
2793 'QUICKMATCH','STANDARD','AWT')
2794 THEN 'PURCHASE_TRANSACTION'
2795 WHEN inv.invoice_type_lookup_code = 'PREPAYMENT'
2796 THEN 'PURCHASE_PREPAYMENTTRANSACTION'
2797 WHEN inv.invoice_type_lookup_code='EXPENSE REPORT'
2798 THEN 'EXPENSE_REPORT'
2799 ELSE NULL
2800 END) TAX_EVENT_CLASS_CODE
2801 ,DECODE(lines.po_line_location_id,
2802 NULL, DECODE(lines.line_type_lookup_code,
2803 'PREPAY', 'PREPAY_APPLICATION',
2804 DECODE(inv.invoice_type_lookup_code,
2805 'STANDARD', 'STANDARD INVOICES',
2806 'CREDIT','AP_CREDIT_MEMO',
2807 'CREDIT MEMO REQUEST', 'AP_CREDIT_MEMO',
2808 'DEBIT','AP_DEBIT_MEMO',
2809 'PREPAYMENT','PREPAYMENT INVOICES',
2810 'EXPENSE REPORT','EXPENSE REPORTS',
2811 'STANDARD INVOICES'
2812 )
2813 ),
2814 DECODE(poll.shipment_type,
2815 'PREPAYMENT', DECODE(poll.payment_type,
2816 'ADVANCE', 'ADVANCE',
2817 'MILESTONE', 'FINANCING',
2818 'RATE', 'FINANCING',
2819 'LUMPSUM', 'FINANCING',
2820 DECODE(poll.matching_basis,
2821 'AMOUNT','AMOUNT_MATCHED',
2822 'STANDARD INVOICES')
2823 ),
2824 DECODE(poll.matching_basis,
2825 'AMOUNT','AMOUNT_MATCHED',
2826 'STANDARD INVOICES')
2827 )
2828 ) LINE_CLASS
2829 ,lines.line_type_lookup_code AP_LINE_LOOKUP_CODE
2830 ,lines.invoice_id TRX_ID
2831 ,NVL(inv.invoice_date,sysdate) TRX_DATE
2832 ,lines.set_of_books_id LEDGER_ID
2833 ,inv.invoice_currency_code TRX_CURRENCY_CODE
2834 ,NVL(inv.legal_entity_id, -99) LEGAL_ENTITY_ID
2835 ,inv.taxation_country DEFAULT_TAXATION_COUNTRY
2836 ,inv.invoice_num TRX_NUMBER
2837 ,lines.description TRX_LINE_DESCRIPTION
2838 ,inv.description TRX_DESCRIPTION
2839 ,inv.invoice_received_date TRX_COMMUNICATED_DATE
2840 ,NVL(lines.accounting_date,sysdate) TRX_LINE_GL_DATE
2841 ,inv.batch_id BATCH_SOURCE_ID
2842 ,inv.doc_sequence_id DOC_SEQ_ID
2843 ,fds.name DOC_SEQ_NAME
2844 ,inv.doc_sequence_value DOC_SEQ_VALUE
2845 ,inv.terms_date TRX_DUE_DATE
2846 ,lines.line_type_lookup_code TRX_LINE_TYPE
2847 ,lines.accounting_date TRX_LINE_DATE
2848 ,NVL(lines.amount,0) LINE_AMT
2849 ,lines.quantity_invoiced TRX_LINE_QUANTITY
2850 ,lines.unit_price
2851 ,lines.inventory_item_id PRODUCT_ID
2852 ,lines.unit_meas_lookup_code UOM_CODE
2853 ,lines.product_type
2854 ,lines.item_description PRODUCT_DESCRIPTION
2855 ,ptp.party_tax_profile_id FIRST_PTY_ORG_ID
2856 ,DECODE(lines.prepay_invoice_id, NULL, NULL, 200) APPLIED_FROM_APPLICATION_ID
2857 ,DECODE(lines.prepay_invoice_id, NULL, NULL,
2858 'AP_INVOICES') APPLIED_FROM_ENTITY_CODE
2859 ,DECODE(lines.prepay_invoice_id, NULL, NULL,
2860 'PREPAYMENT INVOICES') APPLIED_FROM_EVENT_CLASS_CODE
2861 ,lines.prepay_invoice_id APPLIED_FROM_TRX_ID
2862 ,lines.prepay_line_number APPLIED_FROM_LINE_ID
2863 ,DECODE(lines.corrected_inv_id, NULL, NULL, 200) ADJUSTED_DOC_APPLICATION_ID
2864 ,DECODE(lines.corrected_inv_id, NULL, NULL,
2865 'AP_INVOICES') ADJUSTED_DOC_ENTITY_CODE
2866 ,DECODE(lines.corrected_inv_id, NULL, NULL,
2867 'STANDARD INVOICES') ADJUSTED_DOC_EVENT_CLASS_CODE
2868 ,lines.corrected_inv_id ADJUSTED_DOC_TRX_ID
2869 ,lines.corrected_line_number ADJUSTED_DOC_LINE_ID
2870 ,DECODE(lines.rcv_transaction_id, NULL, NULL, 707) APPLIED_TO_APPLICATION_ID
2871 ,DECODE(lines.rcv_transaction_id, NULL, NULL,
2872 'RCV_ACCOUNTING_EVENTS') APPLIED_TO_ENTITY_CODE
2873 ,DECODE(lines.rcv_transaction_id, NULL, NULL,
2874 'RCPT_REC_INSP') APPLIED_TO_EVENT_CLASS_CODE
2875 ,lines.rcv_transaction_id APPLIED_TO_TRX_ID
2876 ,lines.rcv_shipment_line_id APPLIED_TO_TRX_LINE_ID
2877 ,DECODE(NVL(lines.po_release_id, lines.po_header_id),
2878 NULL, NULL, 'SHIPMENT') REF_DOC_TRX_LEVEL_TYPE
2879 ,NVL(lines.po_release_id, lines.po_header_id) REF_DOC_TRX_ID
2880 ,lines.po_line_location_id REF_DOC_LINE_ID
2881 ,DECODE(lines.rcv_transaction_id, NULL, NULL,
2882 'LINE') APPLIED_TO_TRX_LEVEL_TYPE
2883 ,DECODE(lines.prepay_invoice_id, NULL, NULL,
2884 'LINE') APPLIED_FROM_TRX_LEVEL_TYPE
2885 ,DECODE(lines.corrected_inv_id, NULL, NULL,
2886 'LINE') ADJUSTED_DOC_TRX_LEVEL_TYPE
2887 ,lines.merchant_name MERCHANT_PARTY_NAME
2888 ,lines.merchant_document_number MERCHANT_PARTY_DOCUMENT_NUMBER
2889 ,lines.merchant_reference MERCHANT_PARTY_REFERENCE
2890 ,lines.merchant_taxpayer_id MERCHANT_PARTY_TAXPAYER_ID
2891 ,lines.merchant_tax_reg_number MERCHANT_PARTY_TAX_REG_NUMBER
2892 ,lines.country_of_supply MERCHANT_PARTY_COUNTRY
2893 ,lines.start_expense_date
2894 ,lines.ship_to_location_id
2895 ,lines.product_fisc_classification
2896 ,lines.product_category
2897 ,lines.user_defined_fisc_class
2898 ,lines.assessable_value
2899 ,lines.trx_business_category
2900 ,inv.supplier_tax_invoice_number
2901 ,inv.supplier_tax_invoice_date
2902 ,inv.supplier_tax_exchange_rate SUPPLIER_EXCHANGE_RATE
2903 ,inv.tax_invoice_recording_date TAX_INVOICE_DATE
2904 ,inv.tax_invoice_internal_seq TAX_INVOICE_NUMBER
2905 ,inv.document_sub_type
2906 ,lines.primary_intended_use LINE_INTENDED_USE
2907 ,inv.port_of_entry_code
2908 ,lines.assets_tracking_flag ASSET_FLAG
2909 ,ptp.party_tax_profile_id CONTENT_OWNER_ID
2910 ,inv.exchange_date CURRENCY_CONVERSION_DATE
2911 ,inv.exchange_rate CURRENCY_CONVERSION_RATE
2912 ,inv.exchange_rate_type CURRENCY_CONVERSION_TYPE
2913 --Bug 9037817
2914 ,DECODE(lines.line_source,'MANUAL LINE ENTRY','PRORATED_TB','STANDARD_TB') TAXABLE_BASIS_FORMULA
2915 ,fnd_curr.minimum_accountable_unit MINIMUM_ACCOUNTABLE_UNIT
2916 ,NVL(fnd_curr.precision,0) PRECISION
2917 ,DECODE(NVL(lines.po_release_id, lines.po_header_id),
2918 NULL, NULL, 201) REF_DOC_APPLICATION_ID
2919 ,DECODE(lines.po_release_id, NULL,
2920 DECODE(lines.po_header_id, NULL, NULL,
2921 'PURCHASE_ORDER'), 'RELEASE') REF_DOC_ENTITY_CODE
2922 ,DECODE(lines.po_release_id, NULL,
2923 DECODE(lines.po_header_id, NULL, NULL,
2924 'PO_PA'), 'RELEASE') REF_DOC_EVENT_CLASS_CODE
2925 ,lines.SUMMARY_TAX_LINE_ID SUMMARY_TAX_LINE_ID
2926 ,lines.TAX TAX
2927 ,DECODE(lines.line_type_lookup_code, 'TAX',
2928 RANK() OVER (PARTITION BY inv.invoice_id,
2929 lines.line_type_lookup_code
2930 ORDER BY lines.line_number), NULL) SUMMARY_TAX_LINE_NUMBER
2931 ,lines.tax_rate
2932 ,lines.tax_rate_code
2933 ,lines.tax_rate_id
2934 ,lines.tax_regime_code
2935 ,lines.tax_status_code
2936 ,lines.tax_jurisdiction_code
2937 ,lines.line_number TRX_LINE_ID
2938 ,lines.line_number TRX_LINE_NUMBER
2939 ,lines.default_dist_ccid ACCOUNT_CCID
2940 ,lines.amount TAX_AMT
2941 ,lines.base_amount TAX_AMT_TAX_CURR
2942 ,lines.base_amount TAX_AMT_FUNCL_CURR
2943 ,lines.attribute_category
2944 ,lines.attribute1
2945 ,lines.attribute2
2946 ,lines.attribute3
2947 ,lines.attribute4
2948 ,lines.attribute5
2949 ,lines.attribute6
2950 ,lines.attribute7
2951 ,lines.attribute8
2952 ,lines.attribute9
2953 ,lines.attribute10
2954 ,lines.attribute11
2955 ,lines.attribute12
2956 ,lines.attribute13
2957 ,lines.attribute14
2958 ,lines.attribute15
2959 ,lines.global_attribute_category
2960 ,lines.global_attribute1
2961 ,lines.global_attribute2
2962 ,lines.global_attribute3
2963 ,lines.global_attribute4
2964 ,lines.global_attribute5
2965 ,lines.global_attribute6
2966 ,lines.global_attribute7
2967 ,lines.global_attribute8
2968 ,lines.global_attribute9
2969 ,lines.global_attribute10
2970 ,lines.global_attribute11
2971 ,lines.global_attribute12
2972 ,lines.global_attribute13
2973 ,lines.global_attribute14
2974 ,lines.global_attribute15
2975 ,lines.global_attribute16
2976 ,lines.global_attribute17
2977 ,lines.global_attribute18
2978 ,lines.global_attribute19
2979 ,lines.global_attribute20
2980 ,CASE
2981 WHEN lines.line_type_lookup_code <> 'TAX'
2982 THEN NULL
2983 WHEN NOT EXISTS
2984 (SELECT /*+ index(dists AP_INVOICE_DISTRIBUTIONS_U1) */ 1
2985 FROM AP_INV_DISTS_TARGET dists
2986 WHERE dists.invoice_id = lines.invoice_id
2987 AND dists.invoice_line_number = lines.line_number
2988 AND dists.charge_applicable_to_dist_id IS NOT NULL
2989 )
2990 THEN 'Y'
2991 ELSE 'N'
2992 END TAX_ONLY_LINE_FLAG
2993 ,lines.total_rec_tax_amount TOTAL_REC_TAX_AMT
2994 ,lines.total_nrec_tax_amount TOTAL_NREC_TAX_AMT
2995 ,lines.total_rec_tax_amt_funcl_curr
2996 ,lines.total_nrec_tax_amt_funcl_curr
2997 ,inv.vendor_id SHIP_THIRD_PTY_ACCT_ID
2998 ,inv.vendor_site_id SHIP_THIRD_PTY_ACCT_SITE_ID
2999 ,inv.vendor_id BILL_THIRD_PTY_ACCT_ID
3000 ,inv.vendor_site_id BILL_THIRD_PTY_ACCT_SITE_ID
3001 ,DECODE(lines.discarded_flag, 'Y', 'Y', 'N') CANCEL_FLAG
3002 ,DECODE(lines.line_source,'MANUAL LINE ENTRY','Y','N') MANUALLY_ENTERED_FLAG --BUG7146063
3003 ,DECODE(lines.line_source,'MANUAL LINE ENTRY','TAX_AMOUNT',NULL) LAST_MANUAL_ENTRY --BUG7146063
3004 FROM ap_invoices_all inv,
3005 xla_upgrade_dates upd,
3006 fnd_currencies fnd_curr,
3007 fnd_document_sequences fds,
3008 ap_invoice_lines_all lines,
3009 po_line_locations_all poll,
3010 zx_party_tax_profile ptp
3011 WHERE inv.rowid BETWEEN p_start_rowid AND p_end_rowid
3012 AND upd.ledger_id = inv.set_of_books_id
3013 AND (TRUNC(inv.invoice_date) between upd.start_date and upd.end_date)
3014 AND fnd_curr.currency_code = inv.invoice_currency_code
3015 AND inv.doc_sequence_id = fds.doc_sequence_id(+)
3016 AND lines.invoice_id = inv.invoice_id
3017 AND poll.line_location_id(+) = lines.po_line_location_id
3018 AND ptp.party_type_code = 'OU'
3019 AND ptp.party_id = DECODE(l_multi_org_flag,'N',l_org_id,lines.org_id)
3020 AND NVL(inv.historical_flag, 'N') = 'Y'
3021 AND NOT EXISTS
3022 (SELECT 1 FROM zx_lines_Det_Factors zxdet
3023 WHERE zxdet.APPLICATION_ID = 200
3024 AND zxdet.ENTITY_CODE = 'AP_INVOICES'
3025 AND zxdet.event_class_code = DECODE(inv.INVOICE_TYPE_LOOKUP_CODE,
3026 'STANDARD', 'STANDARD INVOICES',
3027 'CREDIT' , 'STANDARD INVOICES',
3028 'DEBIT' , 'STANDARD INVOICES',
3029 'MIXED' , 'STANDARD INVOICES',
3030 'ADJUSTMENT','STANDARD INVOICES',
3031 'PO PRICE ADJUST','STANDARD INVOICES',
3032 'INVOICE REQUEST','STANDARD INVOICES',
3033 'CREDIT MEMO REQUEST','STANDARD INVOICES',
3034 'RETAINAGE RELEASE' ,'STANDARD INVOICES',
3035 'PREPAYMENT', 'PREPAYMENT INVOICES',
3036 'EXPENSE REPORT', 'EXPENSE REPORTS',
3037 'INTEREST INVOICE', 'INTEREST INVOICES','NA')
3038 AND zxdet.TRX_ID = inv.invoice_id);
3039
3040
3041 -- Insert data into zx_lines and zx_rec_nrec_dist
3042 --
3043 INSERT ALL
3044 INTO ZX_REC_NREC_DIST(
3045 TAX_LINE_ID
3046 ,REC_NREC_TAX_DIST_ID
3047 ,REC_NREC_TAX_DIST_NUMBER
3048 ,APPLICATION_ID
3049 ,CONTENT_OWNER_ID
3050 ,CURRENCY_CONVERSION_DATE
3051 ,CURRENCY_CONVERSION_RATE
3052 ,CURRENCY_CONVERSION_TYPE
3053 ,ENTITY_CODE
3054 ,EVENT_CLASS_CODE
3055 ,EVENT_TYPE_CODE
3056 ,LEDGER_ID
3057 ,MINIMUM_ACCOUNTABLE_UNIT
3058 ,PRECISION
3059 ,RECORD_TYPE_CODE
3060 ,REF_DOC_APPLICATION_ID
3061 ,REF_DOC_ENTITY_CODE
3062 ,REF_DOC_EVENT_CLASS_CODE
3063 ,REF_DOC_LINE_ID
3064 ,REF_DOC_TRX_ID
3065 ,REF_DOC_TRX_LEVEL_TYPE
3066 ,SUMMARY_TAX_LINE_ID
3067 ,TAX
3068 ,TAX_APPORTIONMENT_LINE_NUMBER
3069 ,TAX_CURRENCY_CODE
3070 ,TAX_CURRENCY_CONVERSION_DATE
3071 ,TAX_CURRENCY_CONVERSION_RATE
3072 ,TAX_CURRENCY_CONVERSION_TYPE
3073 ,TAX_EVENT_CLASS_CODE
3074 ,TAX_EVENT_TYPE_CODE
3075 ,TAX_ID
3076 ,TAX_LINE_NUMBER
3077 ,TAX_RATE
3078 ,TAX_RATE_CODE
3079 ,TAX_RATE_ID
3080 ,TAX_REGIME_CODE
3081 ,TAX_REGIME_ID
3082 ,TAX_STATUS_CODE
3083 ,TAX_STATUS_ID
3084 ,TRX_CURRENCY_CODE
3085 ,TRX_ID
3086 ,TRX_LEVEL_TYPE
3087 ,TRX_LINE_ID
3088 ,TRX_LINE_NUMBER
3089 ,TRX_NUMBER
3090 ,UNIT_PRICE
3091 ,ACCOUNT_CCID
3092 ,AWARD_ID
3093 ,EXPENDITURE_ITEM_DATE
3094 ,EXPENDITURE_ORGANIZATION_ID
3095 ,EXPENDITURE_TYPE
3096 ,GL_DATE
3097 ,INTENDED_USE
3098 ,ITEM_DIST_NUMBER
3099 ,PROJECT_ID
3100 ,REC_NREC_RATE
3101 ,REC_NREC_TAX_AMT
3102 ,REC_NREC_TAX_AMT_FUNCL_CURR
3103 ,REC_NREC_TAX_AMT_TAX_CURR
3104 ,RECOVERY_RATE_CODE
3105 ,RECOVERY_TYPE_CODE
3106 ,REF_DOC_DIST_ID
3107 ,REVERSED_TAX_DIST_ID
3108 ,TASK_ID
3109 ,TAXABLE_AMT_FUNCL_CURR
3110 ,TAXABLE_AMT_TAX_CURR
3111 ,TRX_LINE_DIST_AMT
3112 ,TRX_LINE_DIST_ID
3113 ,TRX_LINE_DIST_QTY
3114 ,TRX_LINE_DIST_TAX_AMT
3115 ,TAXABLE_AMT
3116 ,ATTRIBUTE_CATEGORY
3117 ,ATTRIBUTE1
3118 ,ATTRIBUTE2
3119 ,ATTRIBUTE3
3120 ,ATTRIBUTE4
3121 ,ATTRIBUTE5
3122 ,ATTRIBUTE6
3123 ,ATTRIBUTE7
3124 ,ATTRIBUTE8
3125 ,ATTRIBUTE9
3126 ,ATTRIBUTE10
3127 ,ATTRIBUTE11
3128 ,ATTRIBUTE12
3129 ,ATTRIBUTE13
3130 ,ATTRIBUTE14
3131 ,ATTRIBUTE15
3132 ,GLOBAL_ATTRIBUTE_CATEGORY
3133 ,GLOBAL_ATTRIBUTE1
3134 ,GLOBAL_ATTRIBUTE2
3135 ,GLOBAL_ATTRIBUTE3
3136 ,GLOBAL_ATTRIBUTE4
3137 ,GLOBAL_ATTRIBUTE5
3138 ,GLOBAL_ATTRIBUTE6
3139 ,GLOBAL_ATTRIBUTE7
3140 ,GLOBAL_ATTRIBUTE8
3141 ,GLOBAL_ATTRIBUTE9
3142 ,GLOBAL_ATTRIBUTE10
3143 ,GLOBAL_ATTRIBUTE11
3144 ,GLOBAL_ATTRIBUTE12
3145 ,GLOBAL_ATTRIBUTE13
3146 ,GLOBAL_ATTRIBUTE14
3147 ,GLOBAL_ATTRIBUTE15
3148 ,GLOBAL_ATTRIBUTE16
3149 ,GLOBAL_ATTRIBUTE17
3150 ,GLOBAL_ATTRIBUTE18
3151 ,GLOBAL_ATTRIBUTE19
3152 ,GLOBAL_ATTRIBUTE20
3153 ,HISTORICAL_FLAG
3154 ,OVERRIDDEN_FLAG
3155 ,SELF_ASSESSED_FLAG
3156 ,TAX_APPORTIONMENT_FLAG
3157 ,TAX_ONLY_LINE_FLAG
3158 ,INCLUSIVE_FLAG
3159 ,MRC_TAX_DIST_FLAG
3160 ,REC_TYPE_RULE_FLAG
3161 ,NEW_REC_RATE_CODE_FLAG
3162 ,RECOVERABLE_FLAG
3163 ,REVERSE_FLAG
3164 ,REC_RATE_DET_RULE_FLAG
3165 ,BACKWARD_COMPATIBILITY_FLAG
3166 ,FREEZE_FLAG
3167 ,POSTING_FLAG
3168 ,LEGAL_ENTITY_ID
3169 ,CREATED_BY
3170 ,CREATION_DATE
3171 ,LAST_UPDATE_DATE
3172 ,LAST_UPDATE_LOGIN
3173 ,LAST_UPDATED_BY
3174 ,OBJECT_VERSION_NUMBER
3175 ,ORIG_AP_CHRG_DIST_NUM
3176 ,ORIG_AP_CHRG_DIST_ID
3177 ,ORIG_AP_TAX_DIST_NUM
3178 ,ORIG_AP_TAX_DIST_ID
3179 ,INTERNAL_ORGANIZATION_ID
3180 ,DEF_REC_SETTLEMENT_OPTION_CODE
3181 ,ACCOUNT_SOURCE_TAX_RATE_ID
3182 ,RECOVERY_RATE_ID
3183 )
3184 VALUES(
3185 ZX_LINES_S.NEXTVAL
3186 ,REC_NREC_TAX_DIST_ID
3187 ,REC_NREC_TAX_DIST_NUMBER
3188 ,200
3189 ,CONTENT_OWNER_ID
3190 ,CURRENCY_CONVERSION_DATE
3191 ,CURRENCY_CONVERSION_RATE
3192 ,CURRENCY_CONVERSION_TYPE
3193 ,'AP_INVOICES'
3194 ,EVENT_CLASS_CODE
3195 ,EVENT_TYPE_CODE
3196 ,AP_LEDGER_ID
3197 ,MINIMUM_ACCOUNTABLE_UNIT
3198 ,PRECISION
3199 ,'MIGRATED'
3200 ,REF_DOC_APPLICATION_ID
3201 ,REF_DOC_ENTITY_CODE
3202 ,REF_DOC_EVENT_CLASS_CODE
3203 ,REF_DOC_LINE_ID
3204 ,REF_DOC_TRX_ID
3205 ,REF_DOC_TRX_LEVEL_TYPE
3206 ,SUMMARY_TAX_LINE_ID
3207 ,TAX
3208 ,TAX_APPORTIONMENT_LINE_NUMBER
3209 ,TAX_CURRENCY_CODE
3210 ,TAX_CURRENCY_CONVERSION_DATE
3211 ,TAX_CURRENCY_CONVERSION_RATE
3212 ,TAX_CURRENCY_CONVERSION_TYPE
3213 ,TAX_EVENT_CLASS_CODE
3214 ,'VALIDATE'
3215 ,TAX_ID
3216 ,TAX_LINE_NUMBER
3217 ,TAX_RATE
3218 ,TAX_RATE_CODE
3219 ,TAX_RATE_ID
3220 ,TAX_REGIME_CODE
3221 ,TAX_REGIME_ID
3222 ,TAX_STATUS_CODE
3223 ,TAX_STATUS_ID
3224 ,TRX_CURRENCY_CODE
3225 ,TRX_ID
3226 ,'LINE'
3227 ,TRX_LINE_ID
3228 ,TRX_LINE_NUMBER
3229 ,TRX_NUMBER
3230 ,UNIT_PRICE
3231 ,ACCOUNT_CCID
3232 ,AWARD_ID
3233 ,EXPENDITURE_ITEM_DATE
3234 ,EXPENDITURE_ORGANIZATION_ID
3235 ,EXPENDITURE_TYPE
3236 ,GL_DATE
3237 ,INTENDED_USE
3238 ,ITEM_DIST_NUMBER
3239 ,PROJECT_ID
3240 ,100
3241 ,REC_NREC_TAX_AMT
3242 ,REC_NREC_TAX_AMT_FUNCL_CURR
3243 ,REC_NREC_TAX_AMT_TAX_CURR
3244 ,RECOVERY_RATE_CODE
3245 ,RECOVERY_TYPE_CODE
3246 ,REF_DOC_DIST_ID
3247 ,REVERSED_TAX_DIST_ID
3248 ,TASK_ID
3249 ,TAXABLE_AMT_FUNCL_CURR
3250 ,TAXABLE_AMT_TAX_CURR
3251 ,TRX_LINE_DIST_AMT
3252 ,TRX_LINE_DIST_ID
3253 ,TRX_LINE_DIST_QTY
3254 ,TRX_LINE_DIST_TAX_AMT
3255 ,TAXABLE_AMT
3256 ,ATTRIBUTE_CATEGORY
3257 ,ATTRIBUTE1
3258 ,ATTRIBUTE2
3259 ,ATTRIBUTE3
3260 ,ATTRIBUTE4
3261 ,ATTRIBUTE5
3262 ,ATTRIBUTE6
3263 ,ATTRIBUTE7
3264 ,ATTRIBUTE8
3265 ,ATTRIBUTE9
3266 ,ATTRIBUTE10
3267 ,ATTRIBUTE11
3268 ,ATTRIBUTE12
3269 ,ATTRIBUTE13
3270 ,ATTRIBUTE14
3271 ,ATTRIBUTE15
3272 ,GLOBAL_ATTRIBUTE_CATEGORY
3273 ,GLOBAL_ATTRIBUTE1
3274 ,GLOBAL_ATTRIBUTE2
3275 ,GLOBAL_ATTRIBUTE3
3276 ,GLOBAL_ATTRIBUTE4
3277 ,GLOBAL_ATTRIBUTE5
3278 ,GLOBAL_ATTRIBUTE6
3279 ,GLOBAL_ATTRIBUTE7
3280 ,GLOBAL_ATTRIBUTE8
3281 ,GLOBAL_ATTRIBUTE9
3282 ,GLOBAL_ATTRIBUTE10
3283 ,GLOBAL_ATTRIBUTE11
3284 ,GLOBAL_ATTRIBUTE12
3285 ,GLOBAL_ATTRIBUTE13
3286 ,GLOBAL_ATTRIBUTE14
3287 ,GLOBAL_ATTRIBUTE15
3288 ,GLOBAL_ATTRIBUTE16
3289 ,GLOBAL_ATTRIBUTE17
3290 ,GLOBAL_ATTRIBUTE18
3291 ,GLOBAL_ATTRIBUTE19
3292 ,GLOBAL_ATTRIBUTE20
3293 ,'Y'
3294 ,'N'
3295 ,'N'
3296 ,'Y'
3297 ,TAX_ONLY_LINE_FLAG
3298 ,'N'
3299 ,'N'
3300 ,'N'
3301 ,'N'
3302 ,RECOVERABLE_FLAG
3303 ,REVERSE_FLAG
3304 ,'N'
3305 ,'N'
3306 ,FREEZE_FLAG
3307 ,POSTING_FLAG
3308 ,LEGAL_ENTITY_ID
3309 ,1
3310 ,sysdate
3311 ,sysdate
3312 ,1
3313 ,1
3314 ,1
3315 ,ORIG_AP_CHRG_DIST_NUM
3316 ,ORIG_AP_CHRG_DIST_ID
3317 ,ORIG_AP_TAX_DIST_NUM
3318 ,ORIG_AP_TAX_DIST_ID
3319 ,INTERNAL_ORGANIZATION_ID
3320 ,DEF_REC_SETTLEMENT_OPTION_CODE
3321 ,ACCOUNT_SOURCE_TAX_RATE_ID
3322 ,RECOVERY_RATE_ID
3323 )
3324 INTO ZX_LINES(
3325 TAX_LINE_ID
3326 ,TAX_LINE_NUMBER
3327 ,APPLICATION_ID
3328 ,CONTENT_OWNER_ID
3329 ,CURRENCY_CONVERSION_DATE
3330 ,CURRENCY_CONVERSION_RATE
3331 ,CURRENCY_CONVERSION_TYPE
3332 ,ENTITY_CODE
3333 ,EVENT_CLASS_CODE
3334 ,EVENT_TYPE_CODE
3335 ,LEDGER_ID
3336 ,MINIMUM_ACCOUNTABLE_UNIT
3337 ,PRECISION
3338 ,RECORD_TYPE_CODE
3339 ,REF_DOC_APPLICATION_ID
3340 ,REF_DOC_ENTITY_CODE
3341 ,REF_DOC_EVENT_CLASS_CODE
3342 ,REF_DOC_LINE_ID
3343 ,REF_DOC_TRX_ID
3344 ,REF_DOC_TRX_LEVEL_TYPE
3345 ,SUMMARY_TAX_LINE_ID
3346 ,TAX
3347 ,TAX_APPORTIONMENT_LINE_NUMBER
3348 ,TAX_CURRENCY_CODE
3349 ,TAX_CURRENCY_CONVERSION_DATE
3350 ,TAX_CURRENCY_CONVERSION_RATE
3351 ,TAX_CURRENCY_CONVERSION_TYPE
3352 ,TAX_EVENT_CLASS_CODE
3353 ,TAX_EVENT_TYPE_CODE
3354 ,TAX_ID
3355 ,TAX_RATE
3356 ,TAX_RATE_CODE
3357 ,TAX_RATE_ID
3358 ,TAX_REGIME_CODE
3359 ,TAX_REGIME_ID
3360 ,TAX_STATUS_CODE
3361 ,TAX_STATUS_ID
3362 ,TRX_CURRENCY_CODE
3363 ,TRX_ID
3364 ,TRX_LEVEL_TYPE
3365 ,TRX_LINE_ID
3366 ,TRX_LINE_NUMBER
3367 ,TRX_NUMBER
3368 ,UNIT_PRICE
3369 ,TAX_RATE_TYPE
3370 ,ADJUSTED_DOC_APPLICATION_ID
3371 ,ADJUSTED_DOC_ENTITY_CODE
3372 ,ADJUSTED_DOC_EVENT_CLASS_CODE
3373 ,ADJUSTED_DOC_LINE_ID
3374 ,ADJUSTED_DOC_TRX_ID
3375 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
3376 ,APPLIED_FROM_APPLICATION_ID
3377 ,APPLIED_FROM_ENTITY_CODE
3378 ,APPLIED_FROM_EVENT_CLASS_CODE
3379 ,APPLIED_FROM_LINE_ID
3380 ,APPLIED_FROM_TRX_ID
3381 ,APPLIED_FROM_TRX_LEVEL_TYPE
3382 ,APPLIED_TO_APPLICATION_ID
3383 ,APPLIED_TO_ENTITY_CODE
3384 ,APPLIED_TO_EVENT_CLASS_CODE
3385 ,APPLIED_TO_LINE_ID
3386 ,APPLIED_TO_TRX_ID
3387 ,APPLIED_TO_TRX_LEVEL_TYPE
3388 ,INTERNAL_ORGANIZATION_ID
3389 ,LINE_AMT
3390 ,LINE_ASSESSABLE_VALUE
3391 ,NREC_TAX_AMT
3392 ,NREC_TAX_AMT_FUNCL_CURR
3393 ,NREC_TAX_AMT_TAX_CURR
3394 ,REC_TAX_AMT
3395 ,REC_TAX_AMT_FUNCL_CURR
3396 ,REC_TAX_AMT_TAX_CURR
3397 ,TAX_AMT
3398 ,TAX_AMT_FUNCL_CURR
3399 ,TAX_AMT_TAX_CURR
3400 ,TAX_CALCULATION_FORMULA
3401 ,TAX_DATE
3402 ,TAX_DETERMINE_DATE
3403 ,TAX_POINT_DATE
3404 ,TAXABLE_AMT
3405 ,TAXABLE_AMT_FUNCL_CURR
3406 ,TAXABLE_AMT_TAX_CURR
3407 ,TAXABLE_BASIS_FORMULA
3408 ,TRX_DATE
3409 ,TRX_LINE_DATE
3410 ,TRX_LINE_QUANTITY
3411 ,HISTORICAL_FLAG
3412 ,OVERRIDDEN_FLAG
3413 ,SELF_ASSESSED_FLAG
3414 ,TAX_APPORTIONMENT_FLAG
3415 ,TAX_ONLY_LINE_FLAG
3416 ,TAX_AMT_INCLUDED_FLAG
3417 ,MRC_TAX_LINE_FLAG
3418 ,OFFSET_FLAG
3419 ,OFFSET_TAX_RATE_CODE
3420 ,PROCESS_FOR_RECOVERY_FLAG
3421 ,COMPOUNDING_TAX_FLAG
3422 ,ORIG_TAX_AMT_INCLUDED_FLAG
3423 ,ORIG_SELF_ASSESSED_FLAG
3424 ,CANCEL_FLAG
3425 ,PURGE_FLAG
3426 ,DELETE_FLAG
3427 ,MANUALLY_ENTERED_FLAG
3428 ,LAST_MANUAL_ENTRY --BUG7146063
3429 ,REPORTING_ONLY_FLAG
3430 ,FREEZE_UNTIL_OVERRIDDEN_FLAG
3431 ,COPIED_FROM_OTHER_DOC_FLAG
3432 ,RECALC_REQUIRED_FLAG
3433 ,SETTLEMENT_FLAG
3434 ,ITEM_DIST_CHANGED_FLAG
3435 ,ASSOCIATED_CHILD_FROZEN_FLAG
3436 ,COMPOUNDING_DEP_TAX_FLAG
3437 ,ENFORCE_FROM_NATURAL_ACCT_FLAG
3438 ,ATTRIBUTE_CATEGORY
3439 ,ATTRIBUTE1
3440 ,ATTRIBUTE2
3441 ,ATTRIBUTE3
3442 ,ATTRIBUTE4
3443 ,ATTRIBUTE5
3444 ,ATTRIBUTE6
3445 ,ATTRIBUTE7
3446 ,ATTRIBUTE8
3447 ,ATTRIBUTE9
3448 ,ATTRIBUTE10
3449 ,ATTRIBUTE11
3450 ,ATTRIBUTE12
3451 ,ATTRIBUTE13
3452 ,ATTRIBUTE14
3453 ,ATTRIBUTE15
3454 ,GLOBAL_ATTRIBUTE_CATEGORY
3455 ,GLOBAL_ATTRIBUTE1
3456 ,GLOBAL_ATTRIBUTE2
3457 ,GLOBAL_ATTRIBUTE3
3458 ,GLOBAL_ATTRIBUTE4
3459 ,GLOBAL_ATTRIBUTE5
3460 ,GLOBAL_ATTRIBUTE6
3461 ,GLOBAL_ATTRIBUTE7
3462 ,GLOBAL_ATTRIBUTE8
3463 ,GLOBAL_ATTRIBUTE9
3464 ,GLOBAL_ATTRIBUTE10
3465 ,GLOBAL_ATTRIBUTE11
3466 ,GLOBAL_ATTRIBUTE12
3467 ,GLOBAL_ATTRIBUTE13
3468 ,GLOBAL_ATTRIBUTE14
3469 ,GLOBAL_ATTRIBUTE15
3470 ,LEGAL_ENTITY_ID
3471 ,CREATED_BY
3472 ,CREATION_DATE
3473 ,LAST_UPDATE_DATE
3474 ,LAST_UPDATE_LOGIN
3475 ,LAST_UPDATED_BY
3476 ,OBJECT_VERSION_NUMBER
3477 ,MULTIPLE_JURISDICTIONS_FLAG
3478 ,LEGAL_REPORTING_STATUS
3479 ,ACCOUNT_SOURCE_TAX_RATE_ID
3480 )
3481 VALUES (
3482 ZX_LINES_S.NEXTVAL
3483 ,TAX_LINE_NUMBER
3484 ,200
3485 ,CONTENT_OWNER_ID
3486 ,CURRENCY_CONVERSION_DATE
3487 ,CURRENCY_CONVERSION_RATE
3488 ,CURRENCY_CONVERSION_TYPE
3489 ,'AP_INVOICES'
3490 ,EVENT_CLASS_CODE
3491 ,EVENT_TYPE_CODE
3492 ,AP_LEDGER_ID
3493 ,MINIMUM_ACCOUNTABLE_UNIT
3494 ,PRECISION
3495 ,'MIGRATED'
3496 ,REF_DOC_APPLICATION_ID
3497 ,REF_DOC_ENTITY_CODE
3498 ,REF_DOC_EVENT_CLASS_CODE
3499 ,REF_DOC_LINE_ID
3500 ,REF_DOC_TRX_ID
3501 ,REF_DOC_TRX_LEVEL_TYPE
3502 ,SUMMARY_TAX_LINE_ID
3503 ,TAX
3504 ,TAX_APPORTIONMENT_LINE_NUMBER
3505 ,TAX_CURRENCY_CODE
3506 ,TAX_CURRENCY_CONVERSION_DATE
3507 ,TAX_CURRENCY_CONVERSION_RATE
3508 ,TAX_CURRENCY_CONVERSION_TYPE
3509 ,TAX_EVENT_CLASS_CODE
3510 ,'VALIDATE'
3511 ,TAX_ID
3512 ,TAX_RATE
3513 ,TAX_RATE_CODE
3514 ,TAX_RATE_ID
3515 ,TAX_REGIME_CODE
3516 ,TAX_REGIME_ID
3517 ,TAX_STATUS_CODE
3518 ,TAX_STATUS_ID
3519 ,TRX_CURRENCY_CODE
3520 ,TRX_ID
3521 ,'LINE'
3522 ,TRX_LINE_ID
3523 ,TRX_LINE_NUMBER
3524 ,TRX_NUMBER
3525 ,UNIT_PRICE
3526 ,RATE_TYPE_CODE
3527 ,ADJUSTED_DOC_APPLICATION_ID
3528 ,ADJUSTED_DOC_ENTITY_CODE
3529 ,ADJUSTED_DOC_EVENT_CLASS_CODE
3530 ,ADJUSTED_DOC_LINE_ID
3531 ,ADJUSTED_DOC_TRX_ID
3532 ,ADJUSTED_DOC_TRX_LEVEL_TYPE
3533 ,APPLIED_FROM_APPLICATION_ID
3534 ,APPLIED_FROM_ENTITY_CODE
3535 ,APPLIED_FROM_EVENT_CLASS_CODE
3536 ,APPLIED_FROM_LINE_ID
3537 ,APPLIED_FROM_TRX_ID
3538 ,APPLIED_FROM_TRX_LEVEL_TYPE
3539 ,APPLIED_TO_APPLICATION_ID
3540 ,APPLIED_TO_ENTITY_CODE
3541 ,APPLIED_TO_EVENT_CLASS_CODE
3542 ,APPLIED_TO_LINE_ID
3543 ,APPLIED_TO_TRX_ID
3544 ,APPLIED_TO_TRX_LEVEL_TYPE
3545 ,INTERNAL_ORGANIZATION_ID
3546 ,LINE_AMT
3547 ,ASSESSABLE_VALUE
3548 ,DECODE(AP_DIST_LOOKUP_CODE,
3549 'NONREC_TAX', REC_NREC_TAX_AMT, NULL)
3550 ,DECODE(AP_DIST_LOOKUP_CODE,
3551 'NONREC_TAX', REC_NREC_TAX_AMT_FUNCL_CURR, NULL)
3552 ,DECODE(AP_DIST_LOOKUP_CODE,
3553 'NONREC_TAX', REC_NREC_TAX_AMT_TAX_CURR, NULL)
3554 ,DECODE(AP_DIST_LOOKUP_CODE,
3555 'REC_TAX', REC_NREC_TAX_AMT, NULL)
3556 ,DECODE(AP_DIST_LOOKUP_CODE,
3557 'REC_TAX', REC_NREC_TAX_AMT_FUNCL_CURR, NULL)
3558 ,DECODE(AP_DIST_LOOKUP_CODE,
3559 'REC_TAX', REC_NREC_TAX_AMT_TAX_CURR, NULL)
3560 ,TAX_AMT
3561 ,TAX_AMT_FUNCL_CURR
3562 ,TAX_AMT_TAX_CURR
3563 ,'STANDARD_TC'
3564 ,TAX_DATE
3565 ,TAX_DETERMINE_DATE
3566 ,TAX_POINT_DATE
3567 ,TAXABLE_AMT
3568 ,TAXABLE_AMT_FUNCL_CURR
3569 ,TAXABLE_AMT_TAX_CURR
3570 --Bug 9037817,'STANDARD_TB'
3571 ,TAXABLE_BASIS_FORMULA
3572 ,TRX_DATE
3573 ,TRX_LINE_DATE
3574 ,TRX_LINE_QUANTITY
3575 ,'Y'
3576 ,'N'
3577 ,'N'
3578 ,'Y'
3579 ,TAX_ONLY_LINE_FLAG
3580 ,'N'
3581 ,'N'
3582 ,OFFSET_FLAG
3583 ,OFFSET_TAX_RATE_CODE
3584 ,'N'
3585 ,'N'
3586 ,'N'
3587 ,'N'
3588 ,CANCEL_FLAG
3589 ,'N'
3590 ,'N'
3591 ,MANUALLY_ENTERED_FLAG
3592 ,LAST_MANUAL_ENTRY --BUG7146063
3593 ,'N'
3594 ,'N'
3595 ,'N'
3596 ,'N'
3597 ,'N'
3598 ,'N'
3599 ,ASSOCIATED_CHILD_FROZEN_FLAG
3600 ,'N'
3601 ,'N'
3602 ,ATTRIBUTE_CATEGORY
3603 ,ATTRIBUTE1
3604 ,ATTRIBUTE2
3605 ,ATTRIBUTE3
3606 ,ATTRIBUTE4
3607 ,ATTRIBUTE5
3608 ,ATTRIBUTE6
3609 ,ATTRIBUTE7
3610 ,ATTRIBUTE8
3611 ,ATTRIBUTE9
3612 ,ATTRIBUTE10
3613 ,ATTRIBUTE11
3614 ,ATTRIBUTE12
3615 ,ATTRIBUTE13
3616 ,ATTRIBUTE14
3617 ,ATTRIBUTE15
3618 ,GLOBAL_ATTRIBUTE_CATEGORY
3619 ,GLOBAL_ATTRIBUTE1
3620 ,GLOBAL_ATTRIBUTE2
3621 ,GLOBAL_ATTRIBUTE3
3622 ,GLOBAL_ATTRIBUTE4
3623 ,GLOBAL_ATTRIBUTE5
3624 ,GLOBAL_ATTRIBUTE6
3625 ,GLOBAL_ATTRIBUTE7
3626 ,GLOBAL_ATTRIBUTE8
3627 ,GLOBAL_ATTRIBUTE9
3628 ,GLOBAL_ATTRIBUTE10
3629 ,GLOBAL_ATTRIBUTE11
3630 ,GLOBAL_ATTRIBUTE12
3631 ,GLOBAL_ATTRIBUTE13
3632 ,GLOBAL_ATTRIBUTE14
3633 ,GLOBAL_ATTRIBUTE15
3634 ,LEGAL_ENTITY_ID
3635 ,1
3636 ,sysdate
3637 ,sysdate
3638 ,1
3639 ,1
3640 ,1
3641 ,'N'
3642 ,LEGAL_REPORTING_STATUS
3643 ,ACCOUNT_SOURCE_TAX_RATE_ID
3644 )
3645 SELECT /*+ ORDERED NO_EXPAND ROWID(inv) swap_join_inputs(upd) use_nl(fnd_curr)
3646 use_nl_with_index(ap_dists AP_INVOICE_DISTRIBUTIONS_N27)
3647 use_nl_with_index(ap_dists1 AP_INVOICE_DISTRIBUTIONS_U2)
3648 use_nl_with_index(lines1 AP_INVOICE_LINES_U1)
3649 use_nl_with_index(taxes ZX_TAXES_B_U2)
3650 use_nl_with_index(rates ZX_RATES_B_N2)
3651 use_nl_with_index(regimes ZX_REGIMES_B_U2)
3652 use_nl_with_index(status ZX_STATUS_B_U2)
3653 use_nl_with_index(ptp ZX_PARTY_TAX_PROFILE_U2) */
3654 NVL(lines1.org_id,-99) INTERNAL_ORGANIZATION_ID
3655 ,DECODE(inv.INVOICE_TYPE_LOOKUP_CODE,
3656 'STANDARD', 'STANDARD INVOICES', --Bug 5859937
3657 'CREDIT' , 'STANDARD INVOICES', --Bug 5859937
3658 'DEBIT' , 'STANDARD INVOICES', --Bug 5859937
3659 'MIXED' , 'STANDARD INVOICES', --Bug 5859937
3660 'ADJUSTMENT','STANDARD INVOICES', --Bug 5859937
3661 'PO PRICE ADJUST','STANDARD INVOICES', --Bug 5859937
3662 'INVOICE REQUEST','STANDARD INVOICES', --Bug 5859937
3663 'CREDIT MEMO REQUEST','STANDARD INVOICES',--Bug 5859937
3664 'RETAINAGE RELEASE' ,'STANDARD INVOICES',--Bug 5859937
3665 'PREPAYMENT', 'PREPAYMENT INVOICES',
3666 'EXPENSE REPORT', 'EXPENSE REPORTS',
3667 'INTEREST INVOICE', 'INTEREST INVOICES','NA') EVENT_CLASS_CODE
3668 ,DECODE(inv.INVOICE_TYPE_LOOKUP_CODE,
3669 'STANDARD','STANDARD INVOICE CREATED',
3670 'PREPAYMENT','PREPAYMENT INVOICE CREATED',
3671 'EXPENSE REPORT','EXPENSE REPORT CREATED',
3672 'INTEREST INVOICE','INTEREST INVOICE CREATED','NA') EVENT_TYPE_CODE
3673 ,(CASE WHEN inv.invoice_type_lookup_code in
3674 ('ADJUSTMENT','CREDIT','DEBIT','INTEREST',
3675 'MIXED','QUICKDEFAULT','PO PRICE ADJUST',
3676 'QUICKMATCH','STANDARD','AWT')
3677 THEN 'PURCHASE_TRANSACTION'
3678 WHEN (inv.invoice_type_lookup_code =
3679 'PREPAYMENT')
3680 THEN 'PURCHASE_PREPAYMENTTRANSACTION'
3681 WHEN (inv.invoice_type_lookup_code =
3682 'EXPENSE REPORT')
3683 THEN 'EXPENSE_REPORT'
3684 ELSE NULL
3685 END) TAX_EVENT_CLASS_CODE
3686 ,lines1.invoice_id TRX_ID
3687 ,NVL(inv.invoice_date,sysdate) TRX_DATE
3688 ,inv.invoice_currency_code TRX_CURRENCY_CODE
3689 ,NVL(inv.legal_entity_id, -99) LEGAL_ENTITY_ID
3690 ,inv.invoice_num TRX_NUMBER
3691 ,(RANK() OVER (PARTITION BY inv.invoice_id ORDER BY
3692 ap_dists1.invoice_line_number,
3693 ap_dists.invoice_distribution_id)) TAX_LINE_NUMBER
3694 ,lines1.accounting_date TRX_LINE_DATE
3695 ,NVL(lines1.amount,0) LINE_AMT
3696 ,NVL(lines1.quantity_invoiced, 0) TRX_LINE_QUANTITY
3697 ,lines1.UNIT_PRICE UNIT_PRICE
3698 ,DECODE(lines1.prepay_invoice_id, NULL, NULL, 200) APPLIED_FROM_APPLICATION_ID
3699 ,DECODE(lines1.prepay_invoice_id, NULL, NULL,
3700 'AP_INVOICES') APPLIED_FROM_ENTITY_CODE
3701 ,DECODE(lines1.prepay_invoice_id, NULL, NULL,
3702 'PREPAYMENT INVOICES') APPLIED_FROM_EVENT_CLASS_CODE
3703 ,lines1.prepay_invoice_id APPLIED_FROM_TRX_ID
3704 ,lines1.prepay_line_number APPLIED_FROM_LINE_ID
3705 ,DECODE(lines1.corrected_inv_id, NULL, NULL, 200) ADJUSTED_DOC_APPLICATION_ID
3706 ,DECODE(lines1.corrected_inv_id, NULL, NULL,
3707 'AP_INVOICES') ADJUSTED_DOC_ENTITY_CODE
3708 ,DECODE(lines1.corrected_inv_id, NULL, NULL,
3709 'STANDARD INVOICES') ADJUSTED_DOC_EVENT_CLASS_CODE
3710 ,lines1.corrected_inv_id ADJUSTED_DOC_TRX_ID
3711 ,lines1.Corrected_Line_Number ADJUSTED_DOC_LINE_ID
3712 ,DECODE(lines1.rcv_transaction_id, NULL, NULL, 707) APPLIED_TO_APPLICATION_ID
3713 ,DECODE(lines1.rcv_transaction_id, NULL, NULL,
3714 'RCV_ACCOUNTING_EVENTS') APPLIED_TO_ENTITY_CODE
3715 ,DECODE(lines1.rcv_transaction_id, NULL, NULL,
3716 'RCPT_REC_INSP') APPLIED_TO_EVENT_CLASS_CODE
3717 ,lines1.rcv_transaction_id APPLIED_TO_TRX_ID
3718 ,lines1.rcv_shipment_line_id APPLIED_TO_LINE_ID
3719 ,DECODE(NVL(lines1.po_release_id,lines1.po_header_id),
3720 NULL, NULL, 'SHIPMENT') REF_DOC_TRX_LEVEL_TYPE
3721 ,NVL(lines1.po_release_id, lines1.po_header_id) REF_DOC_TRX_ID
3722 ,lines1.po_line_location_id REF_DOC_LINE_ID
3723 ,DECODE(lines1.rcv_transaction_id, NULL, NULL,
3724 'LINE') APPLIED_TO_TRX_LEVEL_TYPE
3725 ,DECODE(lines1.prepay_invoice_id, NULL, NULL,
3726 'LINE') APPLIED_FROM_TRX_LEVEL_TYPE
3727 ,DECODE(lines1.corrected_inv_id, NULL, NULL,
3728 'LINE') ADJUSTED_DOC_TRX_LEVEL_TYPE
3729 ,lines1.ASSESSABLE_VALUE
3730 ,ap_dists.DETAIL_TAX_DIST_ID REC_NREC_TAX_DIST_ID
3731 ,ap_dists.line_type_lookup_code AP_DIST_LOOKUP_CODE
3732 ,RANK() OVER (PARTITION BY inv.invoice_id,
3733 ap_dists.charge_applicable_to_dist_id
3734 ORDER BY
3735 ap_dists.line_type_lookup_code desc,
3736 ap_dists.invoice_distribution_id) REC_NREC_TAX_DIST_NUMBER
3737 ,ptp.party_tax_profile_id CONTENT_OWNER_ID
3738 ,inv.exchange_date CURRENCY_CONVERSION_DATE
3739 ,inv.exchange_rate CURRENCY_CONVERSION_RATE
3740 ,inv.exchange_rate_type CURRENCY_CONVERSION_TYPE
3741 ,ap_dists.set_of_books_id AP_LEDGER_ID
3742 ,fnd_curr.minimum_accountable_unit MINIMUM_ACCOUNTABLE_UNIT
3743 ,NVL(fnd_curr.precision, 0) PRECISION
3744 ,DECODE(NVL(lines1.po_release_id, lines1.po_header_id),
3745 NULL, NULL, 201) REF_DOC_APPLICATION_ID
3746 ,DECODE(lines1.po_release_id, NULL,
3747 DECODE(lines1.po_header_id, NULL, NULL,
3748 'PURCHASE_ORDER'), 'RELEASE') REF_DOC_ENTITY_CODE
3749 ,DECODE(lines1.po_release_id, NULL,
3750 DECODE(lines1.po_header_id, NULL, NULL,
3751 'PO_PA'), 'RELEASE') REF_DOC_EVENT_CLASS_CODE
3752 ,ap_dists.summary_tax_line_id SUMMARY_TAX_LINE_ID
3753 ,rates.TAX TAX
3754 ,RANK() OVER (PARTITION BY inv.invoice_id,
3755 ap_dists1.invoice_line_number,
3756 rates.tax_regime_code, rates.tax
3757 ORDER BY
3758 ap_dists.invoice_distribution_id) TAX_APPORTIONMENT_LINE_NUMBER
3759 ,taxes.tax_currency_code
3760 ,inv.exchange_date TAX_CURRENCY_CONVERSION_DATE
3761 ,inv.exchange_rate TAX_CURRENCY_CONVERSION_RATE
3762 ,inv.exchange_rate_type TAX_CURRENCY_CONVERSION_TYPE
3763 --Bug 9037817
3764 ,DECODE(lines.line_source,'MANUAL LINE ENTRY','PRORATED_TB','STANDARD_TB') TAXABLE_BASIS_FORMULA
3765 ,taxes.tax_id
3766 ,rates.percentage_rate TAX_RATE
3767 ,rates.tax_rate_code
3768 ,rates.tax_rate_id
3769 ,rates.tax_regime_code
3770 ,rates.rate_type_code
3771 ,regimes.tax_regime_id
3772 ,rates.tax_status_code
3773 ,status.tax_status_id
3774 ,lines1.line_number TRX_LINE_ID
3775 ,lines1.line_number TRX_LINE_NUMBER
3776 ,ap_dists.dist_code_combination_id ACCOUNT_CCID
3777 ,ap_dists.award_id
3778 ,ap_dists.expenditure_item_date
3779 ,ap_dists.expenditure_organization_id
3780 ,ap_dists.expenditure_type
3781 ,ap_dists.ACCOUNTING_DATE GL_DATE
3782 ,ap_dists.intended_use
3783 ,ap_dists1.distribution_line_number ITEM_DIST_NUMBER
3784 ,ap_dists.project_id
3785 ,NVL(ap_dists.amount,0) REC_NREC_TAX_AMT
3786 ,ap_dists.base_amount REC_NREC_TAX_AMT_FUNCL_CURR
3787 ,ap_dists.base_amount REC_NREC_TAX_AMT_TAX_CURR
3788 ,DECODE(ap_dists.line_type_lookup_code,
3789 'REC_TAX', 'AD_HOC_RECOVERY', NULL) RECOVERY_RATE_CODE
3790 ,DECODE(ap_dists.line_type_lookup_code,
3791 'REC_TAX', 'STANDARD', NULL) RECOVERY_TYPE_CODE
3792 ,NVL(ap_dists.amount,0) TAX_AMT
3793 ,ap_dists.base_amount TAX_AMT_FUNCL_CURR
3794 ,ap_dists.base_amount TAX_AMT_TAX_CURR
3795 ,ap_dists1.po_distribution_id REF_DOC_DIST_ID
3796 ,ap_dists.parent_reversal_id REVERSED_TAX_DIST_ID
3797 ,ap_dists.task_id
3798 ,ap_dists.taxable_base_amount TAXABLE_AMT_FUNCL_CURR
3799 ,ap_dists.taxable_base_amount TAXABLE_AMT_TAX_CURR
3800 ,ap_dists1.amount TRX_LINE_DIST_AMT
3801 --,ap_dists1.invoice_distribution_id TRX_LINE_DIST_ID
3802 -- Bug 9947835
3803 ,DECODE(ap_dists.charge_applicable_to_dist_id,
3804 NULL, lines1.line_number, ap_dists1.invoice_distribution_id) TRX_LINE_DIST_ID
3805 ,NVL(ap_dists1.quantity_invoiced, 0) TRX_LINE_DIST_QTY
3806 ,DECODE(ap_dists.charge_applicable_to_dist_id, NULL,
3807 ap_dists.amount,
3808 SUM (ap_dists.amount) OVER
3809 (PARTITION BY ap_dists.invoice_id,
3810 ap_dists.charge_applicable_to_dist_id)) TRX_LINE_DIST_TAX_AMT
3811 ,ap_dists.TAXABLE_AMOUNT TAXABLE_AMT
3812 ,ap_dists.ATTRIBUTE_CATEGORY
3813 ,ap_dists.ATTRIBUTE1
3814 ,ap_dists.ATTRIBUTE2
3815 ,ap_dists.ATTRIBUTE3
3816 ,ap_dists.ATTRIBUTE4
3817 ,ap_dists.ATTRIBUTE5
3818 ,ap_dists.ATTRIBUTE6
3819 ,ap_dists.ATTRIBUTE7
3820 ,ap_dists.ATTRIBUTE8
3821 ,ap_dists.ATTRIBUTE9
3822 ,ap_dists.ATTRIBUTE10
3823 ,ap_dists.ATTRIBUTE11
3824 ,ap_dists.ATTRIBUTE12
3825 ,ap_dists.ATTRIBUTE13
3826 ,ap_dists.ATTRIBUTE14
3827 ,ap_dists.ATTRIBUTE15
3828 ,ap_dists.GLOBAL_ATTRIBUTE_CATEGORY
3829 ,ap_dists.GLOBAL_ATTRIBUTE1
3830 ,ap_dists.GLOBAL_ATTRIBUTE2
3831 ,ap_dists.GLOBAL_ATTRIBUTE3
3832 ,ap_dists.GLOBAL_ATTRIBUTE4
3833 ,ap_dists.GLOBAL_ATTRIBUTE5
3834 ,ap_dists.GLOBAL_ATTRIBUTE6
3835 ,ap_dists.GLOBAL_ATTRIBUTE7
3836 ,ap_dists.GLOBAL_ATTRIBUTE8
3837 ,ap_dists.GLOBAL_ATTRIBUTE9
3838 ,ap_dists.GLOBAL_ATTRIBUTE10
3839 ,ap_dists.GLOBAL_ATTRIBUTE11
3840 ,ap_dists.GLOBAL_ATTRIBUTE12
3841 ,ap_dists.GLOBAL_ATTRIBUTE13
3842 ,ap_dists.GLOBAL_ATTRIBUTE14
3843 ,ap_dists.GLOBAL_ATTRIBUTE15
3844 ,ap_dists.GLOBAL_ATTRIBUTE16
3845 ,ap_dists.GLOBAL_ATTRIBUTE17
3846 ,ap_dists.GLOBAL_ATTRIBUTE18
3847 ,ap_dists.GLOBAL_ATTRIBUTE19
3848 ,ap_dists.GLOBAL_ATTRIBUTE20
3849 ,DECODE(ap_dists.charge_applicable_to_dist_id,
3850 NULL, 'Y', 'N') TAX_ONLY_LINE_FLAG
3851 ,NVL(ap_dists.tax_recoverable_flag, 'N') RECOVERABLE_FLAG
3852 ,ap_dists.reversal_flag REVERSE_FLAG
3853 ,DECODE(ap_dists.charge_applicable_to_dist_id, -- For Tax Only Line stamp
3854 NULL,'Y', -- Freeze_Flag always Y
3855 DECODE(ap_dists.accounting_event_id, -- Else if acct event id is
3856 NULL,'N','Y') -- Not Null then Y Else N
3857 ) FREEZE_FLAG
3858 ,DECODE(ap_dists.charge_applicable_to_dist_id, -- For Tax Only Line stamp
3859 NULL,'Y', -- Associated_Child_Frozen_Flag always Y
3860 DECODE(ap_dists.accounting_event_id, -- Else if acct event id is
3861 NULL,'N','Y') -- Not Null then Y Else N
3862 ) ASSOCIATED_CHILD_FROZEN_FLAG
3863 ,DECODE(ap_dists.posted_flag, 'Y', 'A', NULL) POSTING_FLAG
3864 ,NVL(lines1.accounting_date,
3865 NVL(inv.invoice_date, sysdate)) TAX_DATE
3866 ,NVL(lines1.accounting_date,
3867 NVL(inv.invoice_date, sysdate)) TAX_DETERMINE_DATE
3868 ,NVL(lines1.accounting_date,
3869 NVL(inv.invoice_date, sysdate)) TAX_POINT_DATE
3870 ,ap_dists1.old_dist_line_number ORIG_AP_CHRG_DIST_NUM
3871 ,ap_dists1.old_distribution_id ORIG_AP_CHRG_DIST_ID
3872 ,ap_dists.old_dist_line_number ORIG_AP_TAX_DIST_NUM
3873 ,ap_dists.old_distribution_id ORIG_AP_TAX_DIST_ID
3874 ,DECODE(ap_dists.posted_flag, 'Y', '111111111111111',
3875 'P', '111111111111111',
3876 '000000000000000') LEGAL_REPORTING_STATUS
3877 ,DECODE(lines.discarded_flag, 'Y', 'Y', 'N') CANCEL_FLAG
3878 ,DECODE(taxes.tax_type_code,'OFFSET','Y','N') OFFSET_FLAG
3879 --,rates.OFFSET_TAX_RATE_CODE OFFSET_TAX_RATE_CODE
3880 -- Bug12986875
3881 ,DECODE((SELECT offset_tax_flag FROM ap_supplier_sites_all ap_sup
3882 WHERE ap_sup.vendor_site_id = inv.vendor_site_id),'Y',
3883 rates.offset_tax_rate_code, NULL) OFFSET_TAX_RATE_CODE
3884 ,NVL(rates.def_rec_settlement_option_code,
3885 taxes.def_rec_settlement_option_code) DEF_REC_SETTLEMENT_OPTION_CODE
3886 ,rates.tax_rate_id ACCOUNT_SOURCE_TAX_RATE_ID
3887 ,(SELECT tax_rate_id FROM zx_rates_b
3888 WHERE tax_rate_code = 'AD_HOC_RECOVERY'
3889 AND rate_type_code = 'RECOVERY'
3890 AND tax_regime_code = rates.tax_regime_code
3891 AND tax = rates.tax
3892 AND content_owner_id = ptp.party_tax_profile_id
3893 AND record_type_code = 'MIGRATED'
3894 AND tax_class = 'INPUT') RECOVERY_RATE_ID
3895 ,DECODE(lines.line_source,'MANUAL LINE ENTRY','Y','N') MANUALLY_ENTERED_FLAG --BUG7146063
3896 ,DECODE(lines.line_source,'MANUAL LINE ENTRY','TAX_AMOUNT',NULL) LAST_MANUAL_ENTRY --BUG7146063
3897 FROM ap_invoices_all inv,
3898 xla_upgrade_dates upd,
3899 fnd_currencies fnd_curr,
3900 ap_inv_dists_target ap_dists,
3901 ap_inv_dists_target ap_dists1,
3902 ap_invoice_lines_all lines1,
3903 ap_invoice_lines_all lines,
3904 zx_rates_b rates,
3905 zx_regimes_b regimes,
3906 zx_taxes_b taxes,
3907 zx_status_b status,
3908 zx_party_tax_profile ptp
3909 WHERE inv.rowid BETWEEN p_start_rowid AND p_end_rowid
3910 AND upd.ledger_id = inv.set_of_books_id
3911 AND (TRUNC(inv.invoice_date) between upd.start_date and upd.end_date)
3912 AND fnd_curr.currency_code = inv.invoice_currency_code
3913 -- AND inv.doc_sequence_id = fds.doc_sequence_id(+)
3914 AND ap_dists.invoice_id = inv.invoice_id
3915 AND ap_dists.line_type_lookup_code IN ('REC_TAX','NONREC_TAX')
3916 AND ap_dists1.invoice_distribution_id = NVL(ap_dists.charge_applicable_to_dist_id,
3917 ap_dists.invoice_distribution_id)
3918 AND lines1.invoice_id = ap_dists1.invoice_id
3919 AND lines1.line_number = ap_dists1.invoice_line_number
3920 AND lines.invoice_id = ap_dists.invoice_id
3921 AND lines.line_number = ap_dists.invoice_line_number
3922 AND rates.source_id(+) = ap_dists.tax_code_id
3923 AND regimes.tax_regime_code(+) = rates.tax_regime_code
3924 AND taxes.tax_regime_code(+) = rates.tax_regime_code
3925 AND taxes.tax(+) = rates.tax
3926 AND taxes.content_owner_id(+) = rates.content_owner_id
3927 AND status.tax_regime_code(+) = rates.tax_regime_code
3928 AND status.tax(+) = rates.tax
3929 AND status.tax_status_code(+) = rates.tax_status_code
3930 AND status.content_owner_id(+) = rates.content_owner_id
3931 AND ptp.party_type_code = 'OU'
3932 AND ptp.party_id = DECODE(l_multi_org_flag,'N', l_org_id, ap_dists.org_id)
3933 AND NVL(inv.historical_flag, 'N') = 'Y'
3934 AND NOT EXISTS
3935 (SELECT 1 FROM zx_lines zxl
3936 WHERE zxl.application_id = 200
3937 AND zxl.event_class_code = DECODE(inv.INVOICE_TYPE_LOOKUP_CODE,
3938 'STANDARD', 'STANDARD INVOICES',
3939 'CREDIT' , 'STANDARD INVOICES',
3940 'DEBIT' , 'STANDARD INVOICES',
3941 'MIXED' , 'STANDARD INVOICES',
3942 'ADJUSTMENT','STANDARD INVOICES',
3943 'PO PRICE ADJUST','STANDARD INVOICES',
3944 'INVOICE REQUEST','STANDARD INVOICES',
3945 'CREDIT MEMO REQUEST','STANDARD INVOICES',
3946 'RETAINAGE RELEASE' ,'STANDARD INVOICES',
3947 'PREPAYMENT', 'PREPAYMENT INVOICES',
3948 'EXPENSE REPORT', 'EXPENSE REPORTS',
3949 'INTEREST INVOICE', 'INTEREST INVOICES','NA')
3950 AND zxl.trx_id = inv.invoice_id
3951 AND zxl.entity_code = 'AP_INVOICES');
3952
3953 x_rows_processed := SQL%ROWCOUNT;
3954
3955 IF g_level_procedure >= g_current_runtime_level THEN
3956 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_AP_TRX_MIG','Worker: '||p_worker_id||' x_rows_processed is ' || x_rows_processed );
3957 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_AP_TRX_MIG','Worker: '||p_worker_id||' zx_ap_trx_mig (-)' );
3958 END IF;
3959
3960 EXCEPTION
3961 WHEN OTHERS THEN
3962 X_retcode := CONC_FAIL;
3963 IF g_level_unexpected >= g_current_runtime_level THEN
3964 FND_LOG.STRING(g_level_unexpected,
3965 'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_AP_TRX_MIG',
3966 'Worker: '||p_worker_id||'Raised exceptions: '||
3967 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80) );
3968 END IF;
3969 raise;
3970
3971 END zx_ap_trx_mig;
3972
3973 /**************************************************************/
3974
3975 PROCEDURE zx_po_trx_mig (x_errbuf OUT NOCOPY VARCHAR2,
3976 x_retcode OUT NOCOPY VARCHAR2,
3977 p_start_rowid IN ROWID,
3978 p_end_rowid IN ROWID,
3979 p_org_id IN NUMBER,
3980 p_multi_org_flag IN VARCHAR2,
3981 p_worker_id IN NUMBER,
3982 x_rows_processed OUT NOCOPY NUMBER)
3983 IS
3984 l_multi_org_flag VARCHAR2(1);
3985 l_org_id NUMBER;
3986 BEGIN
3987 l_multi_org_flag := p_multi_org_flag;
3988 l_org_id := p_org_id;
3989
3990
3991 IF g_level_procedure >= g_current_runtime_level THEN
3992 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_PO_TRX_MIG','Worker: '||p_worker_id||' zx_po_trx_mig (+)' );
3993 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_PO_TRX_MIG','Worker: '||p_worker_id||'p_start_rowid is ' || p_start_rowid );
3994 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_PO_TRX_MIG','Worker: '||p_worker_id||'p_end_rowid is ' || p_end_rowid );
3995 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_PO_TRX_MIG','Worker: '||p_worker_id||'p_org_id is ' || p_org_id );
3996 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_PO_TRX_MIG','Worker: '||p_worker_id||'p_multi_org_flag is ' || p_multi_org_flag );
3997 END IF;
3998
3999 x_retcode := CONC_SUCCESS;
4000
4001 ZX_PO_REC_PKG.get_rec_info(
4002 p_start_rowid => p_start_rowid,
4003 p_end_rowid => p_end_rowid);
4004
4005 INSERT INTO ZX_LINES_DET_FACTORS (
4006 -- ,EVENT_ID
4007 -- ,ACCOUNT_CCID
4008 -- ,ACCOUNT_STRING
4009 -- ,ADJUSTED_DOC_APPLICATION_ID
4010 -- ,ADJUSTED_DOC_DATE
4011 -- ,ADJUSTED_DOC_ENTITY_CODE
4012 -- ,ADJUSTED_DOC_EVENT_CLASS_CODE
4013 -- ,ADJUSTED_DOC_LINE_ID
4014 -- ,ADJUSTED_DOC_NUMBER
4015 -- ,ADJUSTED_DOC_TRX_ID
4016 -- ,ADJUSTED_DOC_TRX_LEVEL_TYPE
4017 -- ,APPLICATION_DOC_STATUS
4018 APPLICATION_ID
4019 -- ,APPLIED_FROM_APPLICATION_ID
4020 -- ,APPLIED_FROM_ENTITY_CODE
4021 -- ,APPLIED_FROM_EVENT_CLASS_CODE
4022 -- ,APPLIED_FROM_LINE_ID
4023 -- ,APPLIED_FROM_TRX_ID
4024 -- ,APPLIED_FROM_TRX_LEVEL_TYPE
4025 -- ,APPLIED_TO_APPLICATION_ID
4026 -- ,APPLIED_TO_ENTITY_CODE
4027 -- ,APPLIED_TO_EVENT_CLASS_CODE
4028 -- ,APPLIED_TO_TRX_ID
4029 -- ,APPLIED_TO_TRX_LEVEL_TYPE
4030 -- ,APPLIED_TO_TRX_LINE_ID
4031 -- ,APPLIED_TO_TRX_NUMBER
4032 -- ,ASSESSABLE_VALUE
4033 -- ,ASSET_ACCUM_DEPRECIATION
4034 -- ,ASSET_COST
4035 -- ,ASSET_FLAG
4036 -- ,ASSET_NUMBER
4037 -- ,ASSET_TYPE
4038 -- ,BATCH_SOURCE_ID
4039 -- ,BATCH_SOURCE_NAME
4040 -- ,BILL_FROM_LOCATION_ID
4041 -- ,BILL_FROM_PARTY_TAX_PROF_ID
4042 -- ,BILL_FROM_SITE_TAX_PROF_ID
4043 -- ,BILL_TO_LOCATION_ID
4044 -- ,BILL_TO_PARTY_TAX_PROF_ID
4045 -- ,BILL_TO_SITE_TAX_PROF_ID
4046 ,COMPOUNDING_TAX_FLAG
4047 ,CREATED_BY
4048 ,CREATION_DATE
4049 ,CTRL_HDR_TX_APPL_FLAG
4050 -- ,CTRL_TOTAL_HDR_TX_AMT
4051 -- ,CTRL_TOTAL_LINE_TX_AMT
4052 ,CURRENCY_CONVERSION_DATE
4053 ,CURRENCY_CONVERSION_RATE
4054 ,CURRENCY_CONVERSION_TYPE
4055 -- ,DEFAULT_TAXATION_COUNTRY
4056 -- ,DOC_EVENT_STATUS
4057 -- ,DOC_SEQ_ID
4058 -- ,DOC_SEQ_NAME
4059 -- ,DOC_SEQ_VALUE
4060 -- ,DOCUMENT_SUB_TYPE
4061 ,ENTITY_CODE
4062 -- ,ESTABLISHMENT_ID
4063 ,EVENT_CLASS_CODE
4064 ,EVENT_TYPE_CODE
4065 ,FIRST_PTY_ORG_ID
4066 ,HISTORICAL_FLAG
4067 -- ,HQ_ESTB_PARTY_TAX_PROF_ID
4068 ,INCLUSIVE_TAX_OVERRIDE_FLAG
4069 ,INPUT_TAX_CLASSIFICATION_CODE
4070 -- ,INTERNAL_ORG_LOCATION_ID
4071 ,INTERNAL_ORGANIZATION_ID
4072 ,LAST_UPDATE_DATE
4073 ,LAST_UPDATED_BY
4074 ,LAST_UPDATE_LOGIN
4075 ,LEDGER_ID
4076 ,LEGAL_ENTITY_ID
4077 ,LINE_AMT
4078 ,LINE_AMT_INCLUDES_TAX_FLAG
4079 ,LINE_CLASS
4080 -- ,LINE_INTENDED_USE
4081 ,LINE_LEVEL_ACTION
4082 -- ,MERCHANT_PARTY_COUNTRY
4083 -- ,MERCHANT_PARTY_DOCUMENT_NUMBER
4084 -- ,MERCHANT_PARTY_ID
4085 -- ,MERCHANT_PARTY_NAME
4086 -- ,MERCHANT_PARTY_REFERENCE
4087 -- ,MERCHANT_PARTY_TAX_PROF_ID
4088 -- ,MERCHANT_PARTY_TAX_REG_NUMBER
4089 -- ,MERCHANT_PARTY_TAXPAYER_ID
4090 ,MINIMUM_ACCOUNTABLE_UNIT
4091 ,OBJECT_VERSION_NUMBER
4092 -- ,OUTPUT_TAX_CLASSIFICATION_CODE
4093 -- ,PORT_OF_ENTRY_CODE
4094 ,PRECISION
4095 -- ,PRODUCT_CATEGORY
4096 -- ,PRODUCT_CODE
4097 -- ,PRODUCT_DESCRIPTION
4098 -- ,PRODUCT_FISC_CLASSIFICATION
4099 ,PRODUCT_ID
4100 ,PRODUCT_ORG_ID
4101 ,PRODUCT_TYPE
4102 ,RECORD_TYPE_CODE
4103 -- ,REF_DOC_APPLICATION_ID
4104 -- ,REF_DOC_ENTITY_CODE
4105 -- ,REF_DOC_EVENT_CLASS_CODE
4106 -- ,REF_DOC_LINE_ID
4107 -- ,REF_DOC_LINE_QUANTITY
4108 -- ,REF_DOC_TRX_ID
4109 -- ,REF_DOC_TRX_LEVEL_TYPE
4110 -- ,RELATED_DOC_APPLICATION_ID
4111 -- ,RELATED_DOC_DATE
4112 -- ,RELATED_DOC_ENTITY_CODE
4113 -- ,RELATED_DOC_EVENT_CLASS_CODE
4114 -- ,RELATED_DOC_NUMBER
4115 -- ,RELATED_DOC_TRX_ID
4116 -- ,SHIP_FROM_LOCATION_ID
4117 -- ,SHIP_FROM_PARTY_TAX_PROF_ID
4118 -- ,SHIP_FROM_SITE_TAX_PROF_ID
4119 ,SHIP_TO_LOCATION_ID
4120 -- ,SHIP_TO_PARTY_TAX_PROF_ID
4121 -- ,SHIP_TO_SITE_TAX_PROF_ID
4122 -- ,SOURCE_APPLICATION_ID
4123 -- ,SOURCE_ENTITY_CODE
4124 -- ,SOURCE_EVENT_CLASS_CODE
4125 -- ,SOURCE_LINE_ID
4126 -- ,SOURCE_TRX_ID
4127 -- ,SOURCE_TRX_LEVEL_TYPE
4128 -- ,START_EXPENSE_DATE
4129 -- ,SUPPLIER_EXCHANGE_RATE
4130 -- ,SUPPLIER_TAX_INVOICE_DATE
4131 -- ,SUPPLIER_TAX_INVOICE_NUMBER
4132 ,TAX_AMT_INCLUDED_FLAG
4133 ,TAX_EVENT_CLASS_CODE
4134 ,TAX_EVENT_TYPE_CODE
4135 -- ,TAX_INVOICE_DATE
4136 -- ,TAX_INVOICE_NUMBER
4137 ,TAX_PROCESSING_COMPLETED_FLAG
4138 ,TAX_REPORTING_FLAG
4139 ,THRESHOLD_INDICATOR_FLAG
4140 -- ,TRX_BUSINESS_CATEGORY
4141 -- ,TRX_COMMUNICATED_DATE
4142 ,TRX_CURRENCY_CODE
4143 ,TRX_DATE
4144 -- ,TRX_DESCRIPTION
4145 -- ,TRX_DUE_DATE
4146 ,TRX_ID
4147 ,TRX_LEVEL_TYPE
4148 ,TRX_LINE_DATE
4149 -- ,TRX_LINE_DESCRIPTION
4150 ,TRX_LINE_GL_DATE
4151 ,TRX_LINE_ID
4152 ,TRX_LINE_NUMBER
4153 ,TRX_LINE_QUANTITY
4154 ,TRX_LINE_TYPE
4155 ,TRX_NUMBER
4156 --- ,TRX_RECEIPT_DATE
4157 --- ,TRX_SHIPPING_DATE
4158 --- ,TRX_TYPE_DESCRIPTION
4159 ,UNIT_PRICE
4160 -- ,UOM_CODE
4161 -- ,USER_DEFINED_FISC_CLASS
4162 ,USER_UPD_DET_FACTORS_FLAG
4163 ,EVENT_CLASS_MAPPING_ID
4164 ,GLOBAL_ATTRIBUTE_CATEGORY
4165 ,GLOBAL_ATTRIBUTE1
4166 -- ,ICX_SESSION_ID
4167 -- ,TRX_LINE_CURRENCY_CODE
4168 -- ,TRX_LINE_CURRENCY_CONV_RATE
4169 -- ,TRX_LINE_CURRENCY_CONV_DATE
4170 -- ,TRX_LINE_PRECISION
4171 -- ,TRX_LINE_MAU
4172 -- ,TRX_LINE_CURRENCY_CONV_TYPE
4173 -- ,INTERFACE_ENTITY_CODE
4174 -- ,INTERFACE_LINE_ID
4175 -- ,SOURCE_TAX_LINE_ID
4176 ,TAX_CALCULATION_DONE_FLAG
4177 ,LINE_TRX_USER_KEY1
4178 ,LINE_TRX_USER_KEY2
4179 ,LINE_TRX_USER_KEY3
4180 )
4181 SELECT /*+ ORDERED NO_EXPAND use_nl(fc, oi, pol, poll, ptp, hr) */
4182 -- NULL EVENT_ID,
4183 -- NULL ACCOUNT_CCID,
4184 -- NULL ACCOUNT_STRING,
4185 -- NULL ADJUSTED_DOC_APPLICATION_ID,
4186 -- NULL ADJUSTED_DOC_DATE,
4187 -- NULL ADJUSTED_DOC_ENTITY_CODE,
4188 -- NULL ADJUSTED_DOC_EVENT_CLASS_CODE,
4189 -- NULL ADJUSTED_DOC_LINE_ID,
4190 -- NULL ADJUSTED_DOC_NUMBER,
4191 -- NULL ADJUSTED_DOC_TRX_ID,
4192 -- NULL ADJUSTED_DOC_TRX_LEVEL_TYPE,
4193 -- NULL APPLICATION_DOC_STATUS,
4194 201 APPLICATION_ID,
4195 -- NULL APPLIED_FROM_APPLICATION_ID,
4196 -- NULL APPLIED_FROM_ENTITY_CODE,
4197 -- NULL APPLIED_FROM_EVENT_CLASS_CODE,
4198 -- NULL APPLIED_FROM_LINE_ID,
4199 -- NULL APPLIED_FROM_TRX_ID,
4200 -- NULL APPLIED_FROM_TRX_LEVEL_TYPE,
4201 -- NULL APPLIED_TO_APPLICATION_ID,
4202 -- NULL APPLIED_TO_ENTITY_CODE,
4203 -- NULL APPLIED_TO_EVENT_CLASS_CODE,
4204 -- NULL APPLIED_TO_TRX_ID,
4205 -- NULL APPLIED_TO_TRX_LEVEL_TYPE,
4206 -- NULL APPLIED_TO_TRX_LINE_ID,
4207 -- NULL APPLIED_TO_TRX_NUMBER,
4208 -- NULL ASSESSABLE_VALUE,
4209 -- NULL ASSET_ACCUM_DEPRECIATION,
4210 -- NULL ASSET_COST,
4211 -- NULL ASSET_FLAG,
4212 -- NULL ASSET_NUMBER,
4213 -- NULL ASSET_TYPE,
4214 -- NULL BATCH_SOURCE_ID,
4215 -- NULL BATCH_SOURCE_NAME,
4216 -- NULL BILL_FROM_LOCATION_ID,
4217 -- NULL BILL_FROM_PARTY_TAX_PROF_ID,
4218 -- NULL BILL_FROM_SITE_TAX_PROF_ID,
4219 -- NULL BILL_TO_LOCATION_ID,
4220 -- NULL BILL_TO_PARTY_TAX_PROF_ID,
4221 -- NULL BILL_TO_SITE_TAX_PROF_ID,
4222 'N' COMPOUNDING_TAX_FLAG,
4223 1 CREATED_BY,
4224 SYSDATE CREATION_DATE,
4225 'N' CTRL_HDR_TX_APPL_FLAG,
4226 -- NULL CTRL_TOTAL_HDR_TX_AMT,
4227 -- NULL CTRL_TOTAL_LINE_TX_AMT,
4228 poh.rate_date CURRENCY_CONVERSION_DATE,
4229 poh.rate CURRENCY_CONVERSION_RATE,
4230 poh.rate_type CURRENCY_CONVERSION_TYPE,
4231 -- NULL DEFAULT_TAXATION_COUNTRY,
4232 -- NULL DOC_EVENT_STATUS,
4233 -- NULL DOC_SEQ_ID,
4234 -- NULL DOC_SEQ_NAME,
4235 -- NULL DOC_SEQ_VALUE,
4236 -- NULL DOCUMENT_SUB_TYPE,
4237 -- 'PURCHASE_ORDER' ENTITY_CODE,
4238 NVL2(poll.po_release_id,
4239 'RELEASE',
4240 'PURCHASE_ORDER') ENTITY_CODE,
4241 -- NULL ESTABLISHMENT_ID,
4242 -- 'PO_PA' EVENT_CLASS_CODE,
4243 NVL2(poll.po_release_id,
4244 'RELEASE', 'PO_PA') EVENT_CLASS_CODE,
4245 'PURCHASE ORDER CREATED' EVENT_TYPE_CODE,
4246 ptp.party_tax_profile_id FIRST_PTY_ORG_ID,
4247 'Y' HISTORICAL_FLAG,
4248 -- NULL HQ_ESTB_PARTY_TAX_PROF_ID,
4249 'N' INCLUSIVE_TAX_OVERRIDE_FLAG,
4250 (select name
4251 from ap_tax_codes_all
4252 where tax_id = poll.tax_code_id) INPUT_TAX_CLASSIFICATION_CODE,
4253 -- NULL INTERNAL_ORG_LOCATION_ID,
4254 nvl(poh.org_id,-99) INTERNAL_ORGANIZATION_ID,
4255 SYSDATE LAST_UPDATE_DATE,
4256 1 LAST_UPDATE_LOGIN,
4257 1 LAST_UPDATED_BY,
4258 poh.set_of_books_id LEDGER_ID,
4259 NVL(oi.org_information2, -99) LEGAL_ENTITY_ID,
4260 DECODE(pol.purchase_basis,
4261 'TEMP LABOR', NVL(POLL.amount,0),
4262 'SERVICES', DECODE(pol.matching_basis, 'AMOUNT',NVL(POLL.amount,0),
4263 NVL(poll.quantity,0) *
4264 NVL(poll.price_override,NVL(pol.unit_price,0))),
4265 NVL(poll.quantity,0) * NVL(poll.price_override,NVL(pol.unit_price,0)))
4266 LINE_AMT,
4267 'N' LINE_AMT_INCLUDES_TAX_FLAG,
4268 'INVOICE' LINE_CLASS,
4269 -- NULL LINE_INTENDED_USE,
4270 'CREATE' LINE_LEVEL_ACTION,
4271 -- NULL MERCHANT_PARTY_COUNTRY,
4272 -- NULL MERCHANT_PARTY_DOCUMENT_NUMBER,
4273 -- NULL MERCHANT_PARTY_ID,
4274 -- NULL MERCHANT_PARTY_NAME,
4275 -- NULL MERCHANT_PARTY_REFERENCE,
4276 -- NULL MERCHANT_PARTY_TAX_PROF_ID,
4277 -- NULL MERCHANT_PARTY_TAX_REG_NUMBER,
4278 -- NULL MERCHANT_PARTY_TAXPAYER_ID,
4279 fc.minimum_accountable_unit, -- MINIMUM_ACCOUNTABLE_UNIT,
4280 1 OBJECT_VERSION_NUMBER,
4281 -- NULL OUTPUT_TAX_CLASSIFICATION_CODE,
4282 -- NULL PORT_OF_ENTRY_CODE,
4283 NVL(fc.precision, 0) PRECISION,
4284 -- fc.precision PRECISION,
4285 -- NULL PRODUCT_CATEGORY,
4286 -- NULL PRODUCT_CODE,
4287 -- NULL PRODUCT_DESCRIPTION,
4288 -- NULL PRODUCT_FISC_CLASSIFICATION,
4289 pol.item_id PRODUCT_ID,
4290 poll.ship_to_organization_id PRODUCT_ORG_ID,
4291 DECODE(UPPER(pol.purchase_basis),
4292 'GOODS', 'GOODS',
4293 'SERVICES', 'SERVICES',
4294 'TEMP LABOR','SERVICES',
4295 'GOODS') PRODUCT_TYPE,
4296 'MIGRATED' RECORD_TYPE_CODE,
4297 -- NULL REF_DOC_APPLICATION_ID,
4298 -- NULL REF_DOC_ENTITY_CODE,
4299 -- NULL REF_DOC_EVENT_CLASS_CODE,
4300 -- NULL REF_DOC_LINE_ID,
4301 -- NULL REF_DOC_LINE_QUANTITY,
4302 -- NULL REF_DOC_TRX_ID,
4303 -- NULL REF_DOC_TRX_LEVEL_TYPE,
4304 -- NULL RELATED_DOC_APPLICATION_ID,
4305 -- NULL RELATED_DOC_DATE,
4306 -- NULL RELATED_DOC_ENTITY_CODE,
4307 -- NULL RELATED_DOC_EVENT_CLASS_CODE,
4308 -- NULL RELATED_DOC_NUMBER,
4309 -- NULL RELATED_DOC_TRX_ID,
4310 -- NULL SHIP_FROM_LOCATION_ID,
4311 -- NULL SHIP_FROM_PARTY_TAX_PROF_ID,
4312 -- NULL SHIP_FROM_SITE_TAX_PROF_ID,
4313 poll.ship_to_location_id, -- SHIP_TO_LOCATION_ID,
4314 -- NULL SHIP_TO_PARTY_TAX_PROF_ID,
4315 -- NULL SHIP_TO_SITE_TAX_PROF_ID,
4316 -- NULL SOURCE_APPLICATION_ID,
4317 -- NULL SOURCE_ENTITY_CODE,
4318 -- NULL SOURCE_EVENT_CLASS_CODE,
4319 -- NULL SOURCE_LINE_ID,
4320 -- NULL SOURCE_TRX_ID,
4321 -- NULL SOURCE_TRX_LEVEL_TYPE,
4322 -- NULL START_EXPENSE_DATE,
4323 -- NULL SUPPLIER_EXCHANGE_RATE,
4324 -- NULL SUPPLIER_TAX_INVOICE_DATE,
4325 -- NULL SUPPLIER_TAX_INVOICE_NUMBER,
4326 'N' TAX_AMT_INCLUDED_FLAG,
4327 'PURCHASE_TRANSACTION' TAX_EVENT_CLASS_CODE,
4328 'VALIDATE' TAX_EVENT_TYPE_CODE,
4329 -- NULL TAX_INVOICE_DATE,
4330 -- NULL TAX_INVOICE_NUMBER,
4331 'Y' TAX_PROCESSING_COMPLETED_FLAG,
4332 'N' TAX_REPORTING_FLAG,
4333 'N' THRESHOLD_INDICATOR_FLAG,
4334 -- NULL TRX_BUSINESS_CATEGORY,
4335 -- NULL TRX_COMMUNICATED_DATE,
4336 NVL(poh.currency_code,
4337 poh.base_currency_code) TRX_CURRENCY_CODE,
4338 -- NVL(poh.currency_code TRX_CURRENCY_CODE,
4339 poh.last_update_date TRX_DATE,
4340 -- NULL TRX_DESCRIPTION,
4341 -- NULL TRX_DUE_DATE,
4342 -- poh.po_header_id TRX_ID,
4343 NVL(poll.po_release_id,
4344 poh.po_header_id) TRX_ID,
4345 'SHIPMENT' TRX_LEVEL_TYPE,
4346 poll.LAST_UPDATE_DATE TRX_LINE_DATE,
4347 -- NULL TRX_LINE_DESCRIPTION,
4348 poll.LAST_UPDATE_DATE TRX_LINE_GL_DATE,
4349 poll.line_location_id TRX_LINE_ID,
4350 poll.SHIPMENT_NUM TRX_LINE_NUMBER,
4351 poll.quantity TRX_LINE_QUANTITY,
4352 'ITEM' TRX_LINE_TYPE,
4353 poh.segment1 TRX_NUMBER,
4354 --- NULL TRX_RECEIPT_DATE,
4355 --- NULL TRX_SHIPPING_DATE,
4356 --- NULL TRX_TYPE_DESCRIPTION,
4357 NVL(poll.price_override,
4358 pol.unit_price) UNIT_PRICE,
4359 -- pol.unit_price UNIT_PRICE,
4360 -- NULL UOM_CODE,
4361 -- NULL USER_DEFINED_FISC_CLASS,
4362 'N' USER_UPD_DET_FACTORS_FLAG,
4363 -- 3 EVENT_CLASS_MAPPING_ID,
4364 NVL2(poll.po_release_id,12, 3) EVENT_CLASS_MAPPING_ID,
4365 poll.GLOBAL_ATTRIBUTE_CATEGORY,-- GLOBAL_ATTRIBUTE_CATEGORY,
4366 poll.GLOBAL_ATTRIBUTE1, -- GLOBAL_ATTRIBUTE1
4367 -- NULL ICX_SESSION_ID,
4368 -- NULL TRX_LINE_CURRENCY_CODE,
4369 -- NULL TRX_LINE_CURRENCY_CONV_RATE,
4370 -- NULL TRX_LINE_CURRENCY_CONV_DATE,
4371 -- NULL TRX_LINE_PRECISION,
4372 -- NULL TRX_LINE_MAU,
4373 -- NULL TRX_LINE_CURRENCY_CONV_TYPE,
4374 -- NULL INTERFACE_ENTITY_CODE,
4375 -- NULL INTERFACE_LINE_ID,
4376 -- NULL SOURCE_TAX_LINE_ID
4377 'Y' TAX_CALCULATION_DONE_FLAG,
4378 pol.line_num LINE_TRX_USER_KEY1,
4379 hr.location_code LINE_TRX_USER_KEY2,
4380 DECODE(poll.payment_type,
4381 NULL, 0, 'DELIVERY',
4382 1,'ADVANCE', 2, 3) LINE_TRX_USER_KEY3
4383 FROM (SELECT /*+ NO_MERGE NO_EXPAND ROWID(poh) swap_join_inputs(fsp) swap_join_inputs(upd)
4384 swap_join_inputs(aps) */
4385 poh.*,
4386 fsp.set_of_books_id,
4387 aps.base_currency_code
4388 FROM po_headers_all poh,
4389 financials_system_params_all fsp,
4390 xla_upgrade_dates upd,
4391 ap_system_parameters_all aps
4392 WHERE poh.rowid BETWEEN p_start_rowid AND p_end_rowid
4393 AND NVL(poh.closed_code, 'X') <> 'FINALLY CLOSED'
4394 AND NVL(poh.org_id,-99) = NVL(fsp.org_id,-99)
4395 AND upd.ledger_id = fsp.set_of_books_id
4396 AND aps.set_of_books_id = fsp.set_of_books_id
4397 AND NVL(aps.org_id, -99) = NVL(fsp.org_id, -99)
4398 AND (trunc(poh.last_update_date) between upd.start_date and upd.end_date)
4399 ) poh,
4400 fnd_currencies fc,
4401 hr_organization_information oi,
4402 po_lines_all pol,
4403 po_line_locations_all poll,
4404 zx_party_tax_profile ptp,
4405 hr_locations_all hr
4406 WHERE NVL(poh.currency_code, poh.base_currency_code) = fc.currency_code(+)
4407 AND oi.organization_id(+) = poh.org_id
4408 AND oi.org_information_context(+) = 'Operating Unit Information'
4409 AND pol.po_header_id = poh.po_header_id
4410 AND poll.po_header_id = pol.po_header_id
4411 AND poll.po_line_id = pol.po_line_id
4412 AND ptp.party_id = DECODE(l_multi_org_flag,'N',l_org_id,poll.org_id)
4413 AND ptp.party_type_code = 'OU'
4414 AND hr.location_id(+) = poll.ship_to_location_id
4415 AND NOT EXISTS
4416 (SELECT 1 FROM zx_lines_Det_Factors zxdet
4417 WHERE zxdet.APPLICATION_ID = 201
4418 AND zxdet.ENTITY_CODE = NVL2(poll.po_release_id, 'RELEASE', 'PURCHASE_ORDER')
4419 AND zxdet.EVENT_CLASS_CODE = NVL2(poll.po_release_id, 'RELEASE', 'PO_PA')
4420 AND zxdet.TRX_ID = NVL(poll.po_release_id,poh.po_header_id)
4421 );
4422
4423 -- insert into zx_lines for tax code
4424 --
4425 INSERT INTO ZX_LINES(
4426 -- ,ADJUSTED_DOC_APPLICATION_ID
4427 -- ,ADJUSTED_DOC_DATE
4428 -- ,ADJUSTED_DOC_ENTITY_CODE
4429 -- ,ADJUSTED_DOC_EVENT_CLASS_CODE
4430 -- ,ADJUSTED_DOC_LINE_ID
4431 -- ,ADJUSTED_DOC_NUMBER
4432 -- ,ADJUSTED_DOC_TAX_LINE_ID
4433 -- ,ADJUSTED_DOC_TRX_ID
4434 -- ,ADJUSTED_DOC_TRX_LEVEL_TYPE
4435 APPLICATION_ID
4436 -- ,APPLIED_FROM_APPLICATION_ID
4437 -- ,APPLIED_FROM_ENTITY_CODE
4438 -- ,APPLIED_FROM_EVENT_CLASS_CODE
4439 -- ,APPLIED_FROM_LINE_ID
4440 -- ,APPLIED_FROM_TRX_ID
4441 -- ,APPLIED_FROM_TRX_LEVEL_TYPE
4442 -- ,APPLIED_FROM_TRX_NUMBER
4443 -- ,APPLIED_TO_APPLICATION_ID
4444 -- ,APPLIED_TO_ENTITY_CODE
4445 -- ,APPLIED_TO_EVENT_CLASS_CODE
4446 -- ,APPLIED_TO_LINE_ID
4447 -- ,APPLIED_TO_TRX_ID
4448 -- ,APPLIED_TO_TRX_LEVEL_TYPE
4449 -- ,APPLIED_TO_TRX_NUMBER
4450 ,ASSOCIATED_CHILD_FROZEN_FLAG
4451 ,ATTRIBUTE_CATEGORY
4452 ,ATTRIBUTE1
4453 ,ATTRIBUTE10
4454 ,ATTRIBUTE11
4455 ,ATTRIBUTE12
4456 ,ATTRIBUTE13
4457 ,ATTRIBUTE14
4458 ,ATTRIBUTE15
4459 ,ATTRIBUTE2
4460 ,ATTRIBUTE3
4461 ,ATTRIBUTE4
4462 ,ATTRIBUTE5
4463 ,ATTRIBUTE6
4464 ,ATTRIBUTE7
4465 ,ATTRIBUTE8
4466 ,ATTRIBUTE9
4467 -- ,BASIS_RESULT_ID
4468 -- ,CAL_TAX_AMT
4469 -- ,CAL_TAX_AMT_FUNCL_CURR
4470 -- ,CAL_TAX_AMT_TAX_CURR
4471 -- ,CALC_RESULT_ID
4472 ,CANCEL_FLAG
4473 -- ,CHAR1
4474 -- ,CHAR10
4475 -- ,CHAR2
4476 -- ,CHAR3
4477 -- ,CHAR4
4478 -- ,CHAR5
4479 -- ,CHAR6
4480 -- ,CHAR7
4481 -- ,CHAR8
4482 -- ,CHAR9
4483 ,COMPOUNDING_DEP_TAX_FLAG
4484 ,COMPOUNDING_TAX_FLAG
4485 ,COMPOUNDING_TAX_MISS_FLAG
4486 ,CONTENT_OWNER_ID
4487 ,COPIED_FROM_OTHER_DOC_FLAG
4488 ,CREATED_BY
4489 ,CREATION_DATE
4490 ,CTRL_TOTAL_LINE_TX_AMT
4491 ,CURRENCY_CONVERSION_DATE
4492 ,CURRENCY_CONVERSION_RATE
4493 ,CURRENCY_CONVERSION_TYPE
4494 -- ,DATE1
4495 -- ,DATE10
4496 -- ,DATE2
4497 -- ,DATE3
4498 -- ,DATE4
4499 -- ,DATE5
4500 -- ,DATE6
4501 -- ,DATE7
4502 -- ,DATE8
4503 -- ,DATE9
4504 ,DELETE_FLAG
4505 -- ,DIRECT_RATE_RESULT_ID
4506 -- ,DOC_EVENT_STATUS
4507 ,ENFORCE_FROM_NATURAL_ACCT_FLAG
4508 ,ENTITY_CODE
4509 --- ,ESTABLISHMENT_ID
4510 --- ,EVAL_EXCPT_RESULT_ID
4511 --- ,EVAL_EXMPT_RESULT_ID,
4512 ,EVENT_CLASS_CODE
4513 ,EVENT_TYPE_CODE
4514 -- ,EXCEPTION_RATE
4515 -- ,EXEMPT_CERTIFICATE_NUMBER
4516 -- ,EXEMPT_RATE_MODIFIER
4517 -- ,EXEMPT_REASON
4518 -- ,EXEMPT_REASON_CODE
4519 ,FREEZE_UNTIL_OVERRIDDEN_FLAG
4520 ,GLOBAL_ATTRIBUTE_CATEGORY
4521 ,GLOBAL_ATTRIBUTE1
4522 ,GLOBAL_ATTRIBUTE10
4523 ,GLOBAL_ATTRIBUTE11
4524 ,GLOBAL_ATTRIBUTE12
4525 ,GLOBAL_ATTRIBUTE13
4526 ,GLOBAL_ATTRIBUTE14
4527 ,GLOBAL_ATTRIBUTE15
4528 ,GLOBAL_ATTRIBUTE2
4529 ,GLOBAL_ATTRIBUTE3
4530 ,GLOBAL_ATTRIBUTE4
4531 ,GLOBAL_ATTRIBUTE5
4532 ,GLOBAL_ATTRIBUTE6
4533 ,GLOBAL_ATTRIBUTE7
4534 ,GLOBAL_ATTRIBUTE8
4535 ,GLOBAL_ATTRIBUTE9
4536 ,HISTORICAL_FLAG
4537 -- ,HQ_ESTB_PARTY_TAX_PROF_ID
4538 -- ,HQ_ESTB_REG_NUMBER
4539 -- ,INTERFACE_ENTITY_CODE
4540 -- ,INTERFACE_TAX_LINE_ID
4541 -- ,INTERNAL_ORG_LOCATION_ID
4542 ,INTERNAL_ORGANIZATION_ID
4543 ,ITEM_DIST_CHANGED_FLAG
4544 -- ,LAST_MANUAL_ENTRY
4545 ,LAST_UPDATE_DATE
4546 ,LAST_UPDATE_LOGIN
4547 ,LAST_UPDATED_BY
4548 ,LEDGER_ID
4549 ,LEGAL_ENTITY_ID
4550 -- ,LEGAL_ENTITY_TAX_REG_NUMBER
4551 -- ,LEGAL_JUSTIFICATION_TEXT1
4552 -- ,LEGAL_JUSTIFICATION_TEXT2
4553 -- ,LEGAL_JUSTIFICATION_TEXT3
4554 -- ,LEGAL_MESSAGE_APPL_2
4555 -- ,LEGAL_MESSAGE_BASIS
4556 -- ,LEGAL_MESSAGE_CALC
4557 -- ,LEGAL_MESSAGE_EXCPT
4558 -- ,LEGAL_MESSAGE_EXMPT
4559 -- ,LEGAL_MESSAGE_POS
4560 -- ,LEGAL_MESSAGE_RATE
4561 -- ,LEGAL_MESSAGE_STATUS
4562 -- ,LEGAL_MESSAGE_THRESHOLD
4563 -- ,LEGAL_MESSAGE_TRN
4564 ,LINE_AMT
4565 -- ,LINE_ASSESSABLE_VALUE
4566 ,MANUALLY_ENTERED_FLAG
4567 ,MINIMUM_ACCOUNTABLE_UNIT
4568 -- ,MRC_LINK_TO_TAX_LINE_ID
4569 ,MRC_TAX_LINE_FLAG
4570 -- ,NREC_TAX_AMT
4571 -- ,NREC_TAX_AMT_FUNCL_CURR
4572 -- ,NREC_TAX_AMT_TAX_CURR
4573 -- ,NUMERIC1
4574 -- ,NUMERIC10
4575 -- ,NUMERIC2
4576 -- ,NUMERIC3
4577 -- ,NUMERIC4
4578 -- ,NUMERIC5
4579 -- ,NUMERIC6
4580 -- ,NUMERIC7
4581 -- ,NUMERIC8
4582 -- ,NUMERIC9
4583 ,OBJECT_VERSION_NUMBER
4584 ,OFFSET_FLAG
4585 -- ,OFFSET_LINK_TO_TAX_LINE_ID
4586 -- ,OFFSET_TAX_RATE_CODE
4587 ,ORIG_SELF_ASSESSED_FLAG
4588 -- ,ORIG_TAX_AMT
4589 -- ,ORIG_TAX_AMT_INCLUDED_FLAG
4590 -- ,ORIG_TAX_AMT_TAX_CURR
4591 -- ,ORIG_TAX_JURISDICTION_CODE
4592 -- ,ORIG_TAX_JURISDICTION_ID
4593 -- ,ORIG_TAX_RATE
4594 -- ,ORIG_TAX_RATE_CODE
4595 -- ,ORIG_TAX_RATE_ID
4596 -- ,ORIG_TAX_STATUS_CODE
4597 -- ,ORIG_TAX_STATUS_ID
4598 -- ,ORIG_TAXABLE_AMT
4599 -- ,ORIG_TAXABLE_AMT_TAX_CURR
4600 -- ,OTHER_DOC_LINE_AMT
4601 -- ,OTHER_DOC_LINE_TAX_AMT
4602 -- ,OTHER_DOC_LINE_TAXABLE_AMT
4603 -- ,OTHER_DOC_SOURCE
4604 ,OVERRIDDEN_FLAG
4605 -- ,PLACE_OF_SUPPLY
4606 -- ,PLACE_OF_SUPPLY_RESULT_ID
4607 -- ,PLACE_OF_SUPPLY_TYPE_CODE
4608 -- ,PRD_TOTAL_TAX_AMT
4609 -- ,PRD_TOTAL_TAX_AMT_FUNCL_CURR
4610 -- ,PRD_TOTAL_TAX_AMT_TAX_CURR
4611 ,PRECISION
4612 ,PROCESS_FOR_RECOVERY_FLAG
4613 ,PRORATION_CODE
4614 ,PURGE_FLAG
4615 -- ,RATE_RESULT_ID
4616 -- ,REC_TAX_AMT
4617 -- ,REC_TAX_AMT_FUNCL_CURR
4618 -- ,REC_TAX_AMT_TAX_CURR
4619 ,RECALC_REQUIRED_FLAG
4620 ,RECORD_TYPE_CODE
4621 -- ,REF_DOC_APPLICATION_ID
4622 -- ,REF_DOC_ENTITY_CODE
4623 -- ,REF_DOC_EVENT_CLASS_CODE
4624 -- ,REF_DOC_LINE_ID
4625 -- ,REF_DOC_LINE_QUANTITY
4626 -- ,REF_DOC_TRX_ID
4627 -- ,REF_DOC_TRX_LEVEL_TYPE
4628 -- ,REGISTRATION_PARTY_TYPE
4629 -- ,RELATED_DOC_APPLICATION_ID
4630 -- ,RELATED_DOC_DATE
4631 -- ,RELATED_DOC_ENTITY_CODE
4632 -- ,RELATED_DOC_EVENT_CLASS_CODE
4633 -- ,RELATED_DOC_NUMBER
4634 -- ,RELATED_DOC_TRX_ID
4635 -- ,RELATED_DOC_TRX_LEVEL_TYPE
4636 -- ,REPORTING_CURRENCY_CODE
4637 ,REPORTING_ONLY_FLAG
4638 -- ,REPORTING_PERIOD_ID
4639 -- ,ROUNDING_LEVEL_CODE
4640 -- ,ROUNDING_LVL_PARTY_TAX_PROF_ID
4641 -- ,ROUNDING_LVL_PARTY_TYPE
4642 -- ,ROUNDING_RULE_CODE
4643 ,SELF_ASSESSED_FLAG
4644 ,SETTLEMENT_FLAG
4645 -- ,STATUS_RESULT_ID
4646 -- ,SUMMARY_TAX_LINE_ID
4647 -- ,SYNC_WITH_PRVDR_FLAG
4648 ,TAX
4649 ,TAX_AMT
4650 ,TAX_AMT_FUNCL_CURR
4651 ,TAX_AMT_INCLUDED_FLAG
4652 ,TAX_AMT_TAX_CURR
4653 -- ,TAX_APPLICABILITY_RESULT_ID
4654 ,TAX_APPORTIONMENT_FLAG
4655 ,TAX_APPORTIONMENT_LINE_NUMBER
4656 -- ,TAX_BASE_MODIFIER_RATE
4657 ,TAX_CALCULATION_FORMULA
4658 -- ,TAX_CODE
4659 ,TAX_CURRENCY_CODE
4660 ,TAX_CURRENCY_CONVERSION_DATE
4661 ,TAX_CURRENCY_CONVERSION_RATE
4662 ,TAX_CURRENCY_CONVERSION_TYPE
4663 ,TAX_DATE
4664 -- ,TAX_DATE_RULE_ID
4665 ,TAX_DETERMINE_DATE
4666 ,TAX_EVENT_CLASS_CODE
4667 ,TAX_EVENT_TYPE_CODE
4668 -- ,TAX_EXCEPTION_ID
4669 -- ,TAX_EXEMPTION_ID
4670 -- ,TAX_HOLD_CODE
4671 -- ,TAX_HOLD_RELEASED_CODE
4672 ,TAX_ID
4673 -- ,TAX_JURISDICTION_CODE
4674 -- ,TAX_JURISDICTION_ID
4675 ,TAX_LINE_ID
4676 ,TAX_LINE_NUMBER
4677 ,TAX_ONLY_LINE_FLAG
4678 ,TAX_POINT_DATE
4679 -- ,TAX_PROVIDER_ID
4680 ,TAX_RATE
4681 -- ,TAX_RATE_BEFORE_EXCEPTION
4682 -- ,TAX_RATE_BEFORE_EXEMPTION
4683 ,TAX_RATE_CODE
4684 ,TAX_RATE_ID
4685 -- ,TAX_RATE_NAME_BEFORE_EXCEPTION
4686 -- ,TAX_RATE_NAME_BEFORE_EXEMPTION,
4687 -- ,TAX_RATE_TYPE
4688 -- ,TAX_REG_NUM_DET_RESULT_ID
4689 ,TAX_REGIME_CODE
4690 ,TAX_REGIME_ID
4691 -- ,TAX_REGIME_TEMPLATE_ID
4692 -- ,TAX_REGISTRATION_ID
4693 -- ,TAX_REGISTRATION_NUMBER
4694 ,TAX_STATUS_CODE
4695 ,TAX_STATUS_ID
4696 -- ,TAX_TYPE_CODE
4697 -- ,TAXABLE_AMT
4698 -- ,TAXABLE_AMT_FUNCL_CURR
4699 -- ,TAXABLE_AMT_TAX_CURR
4700 ,TAXABLE_BASIS_FORMULA
4701 -- ,TAXING_JURIS_GEOGRAPHY_ID
4702 -- ,THRESH_RESULT_ID
4703 ,TRX_CURRENCY_CODE
4704 ,TRX_DATE
4705 ,TRX_ID
4706 -- ,TRX_ID_LEVEL2
4707 -- ,TRX_ID_LEVEL3
4708 -- ,TRX_ID_LEVEL4
4709 -- ,TRX_ID_LEVEL5
4710 -- ,TRX_ID_LEVEL6
4711 ,TRX_LEVEL_TYPE
4712 ,TRX_LINE_DATE
4713 ,TRX_LINE_ID
4714 -- ,TRX_LINE_INDEX
4715 ,TRX_LINE_NUMBER
4716 ,TRX_LINE_QUANTITY
4717 ,TRX_NUMBER
4718 -- ,TRX_USER_KEY_LEVEL1
4719 -- ,TRX_USER_KEY_LEVEL2
4720 -- ,TRX_USER_KEY_LEVEL3
4721 -- ,TRX_USER_KEY_LEVEL4
4722 -- ,TRX_USER_KEY_LEVEL5
4723 -- ,TRX_USER_KEY_LEVEL6
4724 ,UNIT_PRICE
4725 -- ,UNROUNDED_TAX_AMT
4726 -- ,UNROUNDED_TAXABLE_AMT
4727 ,MULTIPLE_JURISDICTIONS_FLAG
4728 )
4729 SELECT /*+ leading(poh) NO_EXPAND use_nl(fc,pol,poll,ptp,atc,rates,regimes,taxes,status) */
4730 -- NULL DJUSTED_DOC_APPLICATION_ID,
4731 -- NULL DJUSTED_DOC_DATE,
4732 -- NULL ADJUSTED_DOC_ENTITY_CODE,
4733 -- NULL ADJUSTED_DOC_EVENT_CLASS_CODE,
4734 -- NULL ADJUSTED_DOC_LINE_ID,
4735 -- NULL ADJUSTED_DOC_NUMBER,
4736 -- NULL ADJUSTED_DOC_TAX_LINE_ID,
4737 -- NULL AADJUSTED_DOC_TRX_ID,
4738 -- NULL AADJUSTED_DOC_TRX_LEVEL_TYPE,
4739 201 APPLICATION_ID,
4740 -- NULL APPLIED_FROM_APPLICATION_ID,
4741 -- NULL APPLIED_FROM_ENTITY_CODE,
4742 -- NULL APPLIED_FROM_EVENT_CLASS_CODE,
4743 -- NULL APPLIED_FROM_LINE_ID,
4744 -- NULL APPLIED_FROM_TRX_ID,
4745 -- NULL APPLIED_FROM_TRX_LEVEL_TYPE,
4746 -- NULL APPLIED_FROM_TRX_NUMBER,
4747 -- NULL APPLIED_TO_APPLICATION_ID,
4748 -- NULL APPLIED_TO_ENTITY_CODE,
4749 -- NULL APPLIED_TO_EVENT_CLASS_CODE,
4750 -- NULL APPLIED_TO_LINE_ID,
4751 -- NULL APPLIED_TO_TRX_ID,
4752 -- NULL APPLIED_TO_TRX_LEVEL_TYPE,
4753 -- NULL APPLIED_TO_TRX_NUMBER,
4754 'N' ASSOCIATED_CHILD_FROZEN_FLAG,
4755 poll.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,
4756 poll.ATTRIBUTE1 ATTRIBUTE1,
4757 poll.ATTRIBUTE10 ATTRIBUTE10,
4758 poll.ATTRIBUTE11 ATTRIBUTE11,
4759 poll.ATTRIBUTE12 ATTRIBUTE12,
4760 poll.ATTRIBUTE13 ATTRIBUTE13,
4761 poll.ATTRIBUTE14 ATTRIBUTE14,
4762 poll.ATTRIBUTE15 ATTRIBUTE15,
4763 poll.ATTRIBUTE2 ATTRIBUTE2,
4764 poll.ATTRIBUTE3 ATTRIBUTE3,
4765 poll.ATTRIBUTE4 ATTRIBUTE4,
4766 poll.ATTRIBUTE5 ATTRIBUTE5,
4767 poll.ATTRIBUTE6 ATTRIBUTE6,
4768 poll.ATTRIBUTE7 ATTRIBUTE7,
4769 poll.ATTRIBUTE8 ATTRIBUTE8,
4770 poll.ATTRIBUTE9 ATTRIBUTE9,
4771 -- NULL BASIS_RESULT_ID,
4772 -- NULL CAL_TAX_AMT,
4773 -- NULL CAL_TAX_AMT_FUNCL_CURR,
4774 -- NULL CAL_TAX_AMT_TAX_CURR,
4775 -- NULL CALC_RESULT_ID,
4776 'N' CANCEL_FLAG,
4777 -- NULL CHAR1,
4778 -- NULL CHAR10,
4779 -- NULL CHAR2,
4780 -- NULL CHAR3,
4781 -- NULL CHAR4,
4782 -- NULL CHAR5,
4783 -- NULL CHAR6,
4784 -- NULL CHAR7,
4785 -- NULL CHAR8,
4786 -- NULL CHAR9,
4787 'N' COMPOUNDING_DEP_TAX_FLAG,
4788 'N' COMPOUNDING_TAX_FLAG,
4789 'N' COMPOUNDING_TAX_MISS_FLAG,
4790 -- nvl(poh.org_id,-99) CONTENT_OWNER_ID,
4791 ptp.party_tax_profile_id CONTENT_OWNER_ID,
4792 'N' COPIED_FROM_OTHER_DOC_FLAG,
4793 1 CREATED_BY,
4794 SYSDATE CREATION_DATE,
4795 NULL CTRL_TOTAL_LINE_TX_AMT,
4796 poh.rate_date CURRENCY_CONVERSION_DATE,
4797 poh.rate CURRENCY_CONVERSION_RATE,
4798 poh.rate_type CURRENCY_CONVERSION_TYPE,
4799 -- NULL DATE1,
4800 -- NULL DATE10,
4801 -- NULL DATE2,
4802 -- NULL DATE3,
4803 -- NULL DATE4,
4804 -- NULL DATE5,
4805 -- NULL DATE6,
4806 -- NULL DATE7,
4807 -- NULL DATE8,
4808 -- NULL DATE9,
4809 'N' DELETE_FLAG,
4810 -- NULL DIRECT_RATE_RESULT_ID,
4811 -- NULL DOC_EVENT_STATUS,
4812 'N' ENFORCE_FROM_NATURAL_ACCT_FLAG,
4813 -- 'PURCHASE_ORDER' ENTITY_CODE,
4814 NVL2(poll.po_release_id,
4815 'RELEASE','PURCHASE_ORDER') ENTITY_CODE,
4816 -- NULL ESTABLISHMENT_ID,
4817 -- NULL EVAL_EXCPT_RESULT_ID,
4818 -- NULL EVAL_EXMPT_RESULT_ID,
4819 -- 'PO_PA' EVENT_CLASS_CODE,
4820 NVL2(poll.po_release_id,
4821 'RELEASE', 'PO_PA') EVENT_CLASS_CODE,
4822 'PURCHASE ORDER CREATED' EVENT_TYPE_CODE,
4823 -- NULL EXCEPTION_RATE,
4824 -- NULL EXEMPT_CERTIFICATE_NUMBER,
4825 -- NULL EXEMPT_RATE_MODIFIER,
4826 -- NULL EXEMPT_REASON,
4827 -- NULL EXEMPT_REASON_CODE,
4828 'N' FREEZE_UNTIL_OVERRIDDEN_FLAG,
4829 poll.GLOBAL_ATTRIBUTE_CATEGORY, -- GLOBAL_ATTRIBUTE_CATEGORY,
4830 poll.GLOBAL_ATTRIBUTE1, -- GLOBAL_ATTRIBUTE1,
4831 poll.GLOBAL_ATTRIBUTE10, -- GLOBAL_ATTRIBUTE10,
4832 poll.GLOBAL_ATTRIBUTE11, -- GLOBAL_ATTRIBUTE11,
4833 poll.GLOBAL_ATTRIBUTE12, -- GLOBAL_ATTRIBUTE12,
4834 poll.GLOBAL_ATTRIBUTE13, -- GLOBAL_ATTRIBUTE13,
4835 poll.GLOBAL_ATTRIBUTE14, -- GLOBAL_ATTRIBUTE14,
4836 poll.GLOBAL_ATTRIBUTE15, -- GLOBAL_ATTRIBUTE15,
4837 poll.GLOBAL_ATTRIBUTE2, -- GLOBAL_ATTRIBUTE2,
4838 poll.GLOBAL_ATTRIBUTE3, -- GLOBAL_ATTRIBUTE3,
4839 poll.GLOBAL_ATTRIBUTE4, -- GLOBAL_ATTRIBUTE4,
4840 poll.GLOBAL_ATTRIBUTE5, -- GLOBAL_ATTRIBUTE5,
4841 poll.GLOBAL_ATTRIBUTE6, -- GLOBAL_ATTRIBUTE6,
4842 poll.GLOBAL_ATTRIBUTE7, -- GLOBAL_ATTRIBUTE7,
4843 poll.GLOBAL_ATTRIBUTE8, -- GLOBAL_ATTRIBUTE8,
4844 poll.GLOBAL_ATTRIBUTE9, -- GLOBAL_ATTRIBUTE9,
4845 'Y' HISTORICAL_FLAG,
4846 -- NULL HQ_ESTB_PARTY_TAX_PROF_ID,
4847 -- NULL HQ_ESTB_REG_NUMBER,
4848 -- NULL INTERFACE_ENTITY_CODE,
4849 -- NULL INTERFACE_TAX_LINE_ID,
4850 -- NULL NAL_ORG_LOCATION_ID,
4851 nvl(poh.org_id,-99) INTERNAL_ORGANIZATION_ID,
4852 'N' ITEM_DIST_CHANGED_FLAG,
4853 -- NULL LAST_MANUAL_ENTRY,
4854 SYSDATE LAST_UPDATE_DATE,
4855 1 LAST_UPDATE_LOGIN,
4856 1 LAST_UPDATED_BY,
4857 poh.set_of_books_id LEDGER_ID,
4858 NVL(poh.org_information2,-99) LEGAL_ENTITY_ID,
4859 -- NULL LEGAL_ENTITY_TAX_REG_NUMBER ,
4860 -- NULL LEGAL_JUSTIFICATION_TEXT1,
4861 -- NULL LEGAL_JUSTIFICATION_TEXT2,
4862 -- NULL LEGAL_JUSTIFICATION_TEXT3,
4863 -- NULL LEGAL_MESSAGE_APPL_2,
4864 -- NULL LEGAL_MESSAGE_BASIS,
4865 -- NULL LEGAL_MESSAGE_CALC,
4866 -- NULL LEGAL_MESSAGE_EXCPT,
4867 -- NULL LEGAL_MESSAGE_EXMPT,
4868 -- NULL LEGAL_MESSAGE_POS,
4869 -- NULL LEGAL_MESSAGE_RATE,
4870 -- NULL LEGAL_MESSAGE_STATUS,
4871 -- NULL LEGAL_MESSAGE_THRESHOLD,
4872 -- NULL LEGAL_MESSAGE_TRN,
4873 DECODE(pol.purchase_basis,
4874 'TEMP LABOR', NVL(POLL.amount,0),
4875 'SERVICES', DECODE(pol.matching_basis, 'AMOUNT',NVL(POLL.amount,0),
4876 NVL(poll.quantity,0) *
4877 NVL(poll.price_override,NVL(pol.unit_price,0))),
4878 NVL(poll.quantity,0) * NVL(poll.price_override,NVL(pol.unit_price,0)))
4879 LINE_AMT,
4880 -- NULL LINE_ASSESSABLE_VALUE,
4881 'N' MANUALLY_ENTERED_FLAG,
4882 fc.minimum_accountable_unit MINIMUM_ACCOUNTABLE_UNIT,
4883 -- NULL MRC_LINK_TO_TAX_LINE_ID,
4884 'N' MRC_TAX_LINE_FLAG,
4885 -- NULL NREC_TAX_AMT,
4886 -- NULL NREC_TAX_AMT_FUNCL_CURR,
4887 -- NULL NREC_TAX_AMT_TAX_CURR,
4888 -- NULL NUMERIC1,
4889 -- NULL NUMERIC10,
4890 -- NULL NUMERIC2,
4891 -- NULL NUMERIC3,
4892 -- NULL NUMERIC4,
4893 -- NULL NUMERIC5,
4894 -- NULL NUMERIC6,
4895 -- NULL NUMERIC7,
4896 -- NULL NUMERIC8,
4897 -- NULL NUMERIC9,
4898 1 OBJECT_VERSION_NUMBER,
4899 'N' OFFSET_FLAG,
4900 -- NULL OFFSET_LINK_TO_TAX_LINE_ID,
4901 -- NULL OFFSET_TAX_RATE_CODE,
4902 'N' ORIG_SELF_ASSESSED_FLAG,
4903 -- NULL ORIG_TAX_AMT,
4904 -- NULL ORIG_TAX_AMT_INCLUDED_FLAG,
4905 -- NULL ORIG_TAX_AMT_TAX_CURR,
4906 -- NULL ORIG_TAX_JURISDICTION_CODE,
4907 -- NULL ORIG_TAX_JURISDICTION_ID,
4908 -- NULL ORIG_TAX_RATE,
4909 -- NULL ORIG_TAX_RATE_CODE,
4910 -- NULL ORIG_TAX_RATE_ID,
4911 -- NULL ORIG_TAX_STATUS_CODE,
4912 -- NULL ORIG_TAX_STATUS_ID,
4913 -- NULL ORIG_TAXABLE_AMT,
4914 -- NULL ORIG_TAXABLE_AMT_TAX_CURR,
4915 -- NULL OTHER_DOC_LINE_AMT,
4916 -- NULL OTHER_DOC_LINE_TAX_AMT,
4917 -- NULL OTHER_DOC_LINE_TAXABLE_AMT,
4918 -- NULL OTHER_DOC_SOURCE,
4919 'N' OVERRIDDEN_FLAG,
4920 -- NULL PLACE_OF_SUPPLY,
4921 -- NULL PLACE_OF_SUPPLY_RESULT_ID ,
4922 -- NULL PLACE_OF_SUPPLY_TYPE_CODE,
4923 -- NULL PRD_TOTAL_TAX_AMT,
4924 -- NULL PRD_TOTAL_TAX_AMT_FUNCL_CURR,
4925 -- NULL PRD_TOTAL_TAX_AMT_TAX_CURR ,
4926 NVL(fc.precision, 0) PRECISION,
4927 -- fc.precision PRECISION,
4928 'N' PROCESS_FOR_RECOVERY_FLAG,
4929 NULL PRORATION_CODE,
4930 'N' PURGE_FLAG,
4931 -- NULL RATE_RESULT_ID,
4932 -- NULL REC_TAX_AMT,
4933 -- NULL REC_TAX_AMT_FUNCL_CURR,
4934 -- NULL REC_TAX_AMT_TAX_CURR,
4935 'N' RECALC_REQUIRED_FLAG,
4936 'MIGRATED' RECORD_TYPE_CODE,
4937 -- NULL REF_DOC_APPLICATION_ID,
4938 -- NULL REF_DOC_ENTITY_CODE,
4939 -- NULL REF_DOC_EVENT_CLASS_CODE,
4940 -- NULL REF_DOC_LINE_ID,
4941 -- NULL REF_DOC_LINE_QUANTITY,
4942 -- NULL REF_DOC_TRX_ID,
4943 -- NULL REF_DOC_TRX_LEVEL_TYPE,
4944 -- NULL REGISTRATION_PARTY_TYPE,
4945 -- NULL RELATED_DOC_APPLICATION_ID,
4946 -- NULL RELATED_DOC_DATE,
4947 -- NULL RELATED_DOC_ENTITY_CODE,
4948 -- NULL RELATED_DOC_EVENT_CLASS_CODE,
4949 -- NULL RELATED_DOC_NUMBER,
4950 -- NULL RELATED_DOC_TRX_ID,
4951 -- NULL RELATED_DOC_TRX_LEVEL_TYPE,
4952 -- NULL REPORTING_CURRENCY_CODE,
4953 'N' REPORTING_ONLY_FLAG,
4954 -- NULL REPORTING_PERIOD_ID,
4955 -- NULL ROUNDING_LEVEL_CODE,
4956 -- NULL ROUNDING_LVL_PARTY_TAX_PROF_ID,
4957 -- NULL ROUNDING_LVL_PARTY_TYPE,
4958 -- NULL ROUNDING_RULE_CODE,
4959 'N' SELF_ASSESSED_FLAG,
4960 'N' SETTLEMENT_FLAG,
4961 -- NULL STATUS_RESULT_ID,
4962 -- NULL SUMMARY_TAX_LINE_ID,
4963 -- NULL SYNC_WITH_PRVDR_FLAG,
4964 rates.tax TAX ,
4965 decode(FC.Minimum_Accountable_Unit, NULL,
4966 ROUND((NVL(poll.quantity,0) * NVL(poll.price_override, NVL(pol.unit_price,0)))* (nvl(rates.percentage_rate,0)/100), NVL(FC.Precision,0)),
4967 ROUND((NVL(poll.quantity,0) * NVL(poll.price_override, NVL(pol.unit_price,0)))* (nvl(rates.percentage_rate,0)/100)/FC.Minimum_Accountable_Unit)
4968 * FC.Minimum_Accountable_Unit)
4969 TAX_AMT,
4970 decode(FC.Minimum_Accountable_Unit, NULL,
4971 ROUND((NVL(poll.quantity,0) * NVL(poll.price_override, NVL(pol.unit_price,0)))* (nvl(rates.percentage_rate,0)/100), NVL(FC.Precision,0)),
4972 ROUND((NVL(poll.quantity,0) * NVL(poll.price_override, NVL(pol.unit_price,0)))* (nvl(rates.percentage_rate,0)/100)/FC.Minimum_Accountable_Unit)
4973 * FC.Minimum_Accountable_Unit)
4974 TAX_AMT_FUNCL_CURR,
4975 'N' TAX_AMT_INCLUDED_FLAG,
4976 decode(FC.Minimum_Accountable_Unit, NULL,
4977 ROUND((NVL(poll.quantity,0) * NVL(poll.price_override, NVL(pol.unit_price,0)))* (nvl(rates.percentage_rate,0)/100), NVL(FC.Precision,0)),
4978 ROUND((NVL(poll.quantity,0) * NVL(poll.price_override, NVL(pol.unit_price,0)))* (nvl(rates.percentage_rate,0)/100)/FC.Minimum_Accountable_Unit)
4979 * FC.Minimum_Accountable_Unit)
4980 TAX_AMT_TAX_CURR,
4981 -- NULL TAX_APPLICABILITY_RESULT_ID,
4982 'Y' TAX_APPORTIONMENT_FLAG,
4983 1 TAX_APPORTIONMENT_LINE_NUMBER,
4984 -- NULL TAX_BASE_MODIFIER_RATE,
4985 'STANDARD_TC' TAX_CALCULATION_FORMULA,
4986 -- NULL TAX_CODE,
4987 taxes.tax_currency_code TAX_CURRENCY_CODE,
4988 poh.rate_date TAX_CURRENCY_CONVERSION_DATE,
4989 poh.rate TAX_CURRENCY_CONVERSION_RATE,
4990 poh.rate_type TAX_CURRENCY_CONVERSION_TYPE,
4991 poll.last_update_date TAX_DATE,
4992 -- NULL TAX_DATE_RULE_ID,
4993 poll.last_update_date TAX_DETERMINE_DATE,
4994 'PURCHASE_TRANSACTION' TAX_EVENT_CLASS_CODE,
4995 'VALIDATE' TAX_EVENT_TYPE_CODE,
4996 -- NULL TAX_EXCEPTION_ID,
4997 -- NULL TAX_EXEMPTION_ID,
4998 -- NULL TAX_HOLD_CODE,
4999 -- NULL TAX_HOLD_RELEASED_CODE,
5000 taxes.tax_id TAX_ID,
5001 -- NULL TAX_JURISDICTION_CODE,
5002 -- NULL TAX_JURISDICTION_ID,
5003 zx_lines_s.nextval TAX_LINE_ID,
5004 RANK() OVER
5005 (PARTITION BY
5006 NVL(poll.po_release_id,
5007 poh.po_header_id)
5008 ORDER BY
5009 poll.line_location_id,
5010 atc.tax_id) TAX_LINE_NUMBER,
5011 'N' TAX_ONLY_LINE_FLAG,
5012 poll.last_update_date TAX_POINT_DATE,
5013 -- NULL TAX_PROVIDER_ID,
5014 rates.percentage_rate TAX_RATE,
5015 -- NULL TAX_RATE_BEFORE_EXCEPTION,
5016 -- NULL TAX_RATE_BEFORE_EXEMPTION,
5017 rates.tax_rate_code TAX_RATE_CODE,
5018 rates.tax_rate_id TAX_RATE_ID,
5019 -- NULL TAX_RATE_NAME_BEFORE_EXCEPTION,
5020 -- NULL TAX_RATE_NAME_BEFORE_EXEMPTION,
5021 -- NULL TAX_RATE_TYPE,
5022 -- NULL TAX_REG_NUM_DET_RESULT_ID,
5023 rates.tax_regime_code TAX_REGIME_CODE,
5024 regimes.tax_regime_id TAX_REGIME_ID,
5025 -- NULL TAX_REGIME_TEMPLATE_ID,
5026 -- NULL TAX_REGISTRATION_ID,
5027 -- NULL TAX_REGISTRATION_NUMBER,
5028 rates.tax_status_code TAX_STATUS_CODE,
5029 status.tax_status_id TAX_STATUS_ID,
5030 -- NULL TAX_TYPE_CODE,
5031 -- NULL TAXABLE_AMT,
5032 -- NULL TAXABLE_AMT_FUNCL_CURR,
5033 -- NULL TAXABLE_AMT_TAX_CURR,
5034 'STANDARD_TB' TAXABLE_BASIS_FORMULA ,
5035 -- NULL TAXING_JURIS_GEOGRAPHY_ID ,
5036 -- NULL THRESH_RESULT_ID,
5037 NVL(poh.currency_code,
5038 poh.base_currency_code) TRX_CURRENCY_CODE,
5039 poh.last_update_date TRX_DATE,
5040 -- poh.po_header_id TRX_ID,
5041 NVL(poll.po_release_id,
5042 poh.po_header_id) TRX_ID,
5043 -- NULL TRX_ID_LEVEL2,
5044 -- NULL TRX_ID_LEVEL3,
5045 -- NULL TRX_ID_LEVEL4,
5046 -- NULL TRX_ID_LEVEL5,
5047 -- NULL TRX_ID_LEVEL6,
5048 'SHIPMENT' TRX_LEVEL_TYPE,
5049 poll.LAST_UPDATE_DATE TRX_LINE_DATE ,
5050 poll.line_location_id TRX_LINE_ID,
5051 -- NULL TRX_LINE_INDEX,
5052 poll.SHIPMENT_NUM TRX_LINE_NUMBER,
5053 poll.quantity TRX_LINE_QUANTITY ,
5054 poh.segment1 TRX_NUMBER,
5055 -- NULL TRX_USER_KEY_LEVEL1,
5056 -- NULL TRX_USER_KEY_LEVEL2,
5057 -- NULL TRX_USER_KEY_LEVEL3,
5058 -- NULL TRX_USER_KEY_LEVEL4,
5059 -- NULL TRX_USER_KEY_LEVEL5,
5060 -- NULL TRX_USER_KEY_LEVEL6,
5061 NVL(poll.price_override,
5062 pol.unit_price) UNIT_PRICE,
5063 -- pol.unit_price UNIT_PRICE,
5064 -- NULL UNROUNDED_TAX_AMT,
5065 -- NULL UNROUNDED_TAXABLE_AMT,
5066 'N' MULTIPLE_JURISDICTIONS_FLAG
5067 FROM
5068 (SELECT /*+ NO_MERGE NO_EXPAND ROWID(poh) use_hash(fsp) use_hash(aps)
5069 swap_join_inputs(fsp) swap_join_inputs(upd)
5070 swap_join_inputs(aps) swap_join_inputs(oi)*/
5071 poh.* , fsp.org_id fsp_org_id, fsp.set_of_books_id,
5072 aps.base_currency_code, oi.org_information2
5073 FROM po_headers_all poh,
5074 financials_system_params_all fsp,
5075 xla_upgrade_dates upd,
5076 ap_system_parameters_all aps,
5077 hr_organization_information oi
5078 WHERE poh.rowid BETWEEN p_start_rowid AND p_end_rowid
5079 AND NVL(poh.closed_code, 'X') <> 'FINALLY CLOSED'
5080 AND NVL(poh.org_id,-99) = NVL(fsp.org_id,-99)
5081 AND upd.ledger_id = fsp.set_of_books_id
5082 AND NVL(aps.org_id, -99) = NVL(fsp.org_id,-99)
5083 AND aps.set_of_books_id = fsp.set_of_books_id
5084 AND (trunc(poh.last_update_date) between upd.start_date and upd.end_date)
5085 AND oi.organization_id(+) = poh.org_id
5086 AND oi.org_information_context(+) = 'Operating Unit Information'
5087 ) poh,
5088 fnd_currencies fc,
5089 po_lines_all pol,
5090 po_line_locations_all poll,
5091 zx_party_tax_profile ptp,
5092 ap_tax_codes_all atc,
5093 zx_rates_b rates,
5094 zx_regimes_b regimes,
5095 zx_taxes_b taxes,
5096 zx_status_b status
5097 WHERE NVL(poh.currency_code, poh.base_currency_code) = fc.currency_code(+)
5098 AND poh.po_header_id = pol.po_header_id
5099 AND pol.po_header_id = poll.po_header_id
5100 AND pol.po_line_id = poll.po_line_id
5101 AND nvl(atc.org_id,-99)=nvl(poh.fsp_org_id,-99)
5102 AND poll.tax_code_id = atc.tax_id
5103 AND atc.tax_type NOT IN ('TAX_GROUP','USE')
5104 AND ptp.party_id = DECODE(l_multi_org_flag,'N',l_org_id,poll.org_id)
5105 AND ptp.party_type_code = 'OU'
5106 AND rates.source_id = atc.tax_id
5107 AND regimes.tax_regime_code(+) = rates.tax_regime_code
5108 AND taxes.tax_regime_code(+) = rates.tax_regime_code
5109 AND taxes.tax(+) = rates.tax
5110 AND taxes.content_owner_id(+) = rates.content_owner_id
5111 AND status.tax_regime_code(+) = rates.tax_regime_code
5112 AND status.tax(+) = rates.tax
5113 AND status.content_owner_id(+) = rates.content_owner_id
5114 AND status.tax_status_code(+) = rates.tax_status_code
5115 AND NOT EXISTS
5116 (SELECT 1 FROM zx_lines zxl
5117 WHERE zxl.APPLICATION_ID = 201
5118 AND zxl.EVENT_CLASS_CODE = NVL2(poll.po_release_id, 'RELEASE', 'PO_PA')
5119 AND zxl.TRX_ID = NVL(poll.po_release_id, poh.po_header_id)
5120 AND zxl.ENTITY_CODE = NVL2(poll.po_release_id, 'RELEASE','PURCHASE_ORDER'));
5121
5122 -- insert into zx_lines tax group
5123 --
5124 INSERT INTO ZX_LINES(
5125 -- ,ADJUSTED_DOC_APPLICATION_ID
5126 -- ,ADJUSTED_DOC_DATE
5127 -- ,ADJUSTED_DOC_ENTITY_CODE
5128 -- ,ADJUSTED_DOC_EVENT_CLASS_CODE
5129 -- ,ADJUSTED_DOC_LINE_ID
5130 -- ,ADJUSTED_DOC_NUMBER
5131 -- ,ADJUSTED_DOC_TAX_LINE_ID
5132 -- ,ADJUSTED_DOC_TRX_ID
5133 -- ,ADJUSTED_DOC_TRX_LEVEL_TYPE
5134 APPLICATION_ID
5135 -- ,APPLIED_FROM_APPLICATION_ID
5136 -- ,APPLIED_FROM_ENTITY_CODE
5137 -- ,APPLIED_FROM_EVENT_CLASS_CODE
5138 -- ,APPLIED_FROM_LINE_ID
5139 -- ,APPLIED_FROM_TRX_ID
5140 -- ,APPLIED_FROM_TRX_LEVEL_TYPE
5141 -- ,APPLIED_FROM_TRX_NUMBER
5142 -- ,APPLIED_TO_APPLICATION_ID
5143 -- ,APPLIED_TO_ENTITY_CODE
5144 -- ,APPLIED_TO_EVENT_CLASS_CODE
5145 -- ,APPLIED_TO_LINE_ID
5146 -- ,APPLIED_TO_TRX_ID
5147 -- ,APPLIED_TO_TRX_LEVEL_TYPE
5148 -- ,APPLIED_TO_TRX_NUMBER
5149 ,ASSOCIATED_CHILD_FROZEN_FLAG
5150 ,ATTRIBUTE_CATEGORY
5151 ,ATTRIBUTE1
5152 ,ATTRIBUTE10
5153 ,ATTRIBUTE11
5154 ,ATTRIBUTE12
5155 ,ATTRIBUTE13
5156 ,ATTRIBUTE14
5157 ,ATTRIBUTE15
5158 ,ATTRIBUTE2
5159 ,ATTRIBUTE3
5160 ,ATTRIBUTE4
5161 ,ATTRIBUTE5
5162 ,ATTRIBUTE6
5163 ,ATTRIBUTE7
5164 ,ATTRIBUTE8
5165 ,ATTRIBUTE9
5166 -- ,BASIS_RESULT_ID
5167 -- ,CAL_TAX_AMT
5168 -- ,CAL_TAX_AMT_FUNCL_CURR
5169 -- ,CAL_TAX_AMT_TAX_CURR
5170 -- ,CALC_RESULT_ID
5171 ,CANCEL_FLAG
5172 -- ,CHAR1
5173 -- ,CHAR10
5174 -- ,CHAR2
5175 -- ,CHAR3
5176 -- ,CHAR4
5177 -- ,CHAR5
5178 -- ,CHAR6
5179 -- ,CHAR7
5180 -- ,CHAR8
5181 -- ,CHAR9
5182 ,COMPOUNDING_DEP_TAX_FLAG
5183 ,COMPOUNDING_TAX_FLAG
5184 ,COMPOUNDING_TAX_MISS_FLAG
5185 ,CONTENT_OWNER_ID
5186 ,COPIED_FROM_OTHER_DOC_FLAG
5187 ,CREATED_BY
5188 ,CREATION_DATE
5189 ,CTRL_TOTAL_LINE_TX_AMT
5190 ,CURRENCY_CONVERSION_DATE
5191 ,CURRENCY_CONVERSION_RATE
5192 ,CURRENCY_CONVERSION_TYPE
5193 -- ,DATE1
5194 -- ,DATE10
5195 -- ,DATE2
5196 -- ,DATE3
5197 -- ,DATE4
5198 -- ,DATE5
5199 -- ,DATE6
5200 -- ,DATE7
5201 -- ,DATE8
5202 -- ,DATE9
5203 ,DELETE_FLAG
5204 -- ,DIRECT_RATE_RESULT_ID
5205 -- ,DOC_EVENT_STATUS
5206 ,ENFORCE_FROM_NATURAL_ACCT_FLAG
5207 ,ENTITY_CODE
5208 --- ,ESTABLISHMENT_ID
5209 --- ,EVAL_EXCPT_RESULT_ID
5210 --- ,EVAL_EXMPT_RESULT_ID,
5211 ,EVENT_CLASS_CODE
5212 ,EVENT_TYPE_CODE
5213 -- ,EXCEPTION_RATE
5214 -- ,EXEMPT_CERTIFICATE_NUMBER
5215 -- ,EXEMPT_RATE_MODIFIER
5216 -- ,EXEMPT_REASON
5217 -- ,EXEMPT_REASON_CODE
5218 ,FREEZE_UNTIL_OVERRIDDEN_FLAG
5219 ,GLOBAL_ATTRIBUTE_CATEGORY
5220 ,GLOBAL_ATTRIBUTE1
5221 ,GLOBAL_ATTRIBUTE10
5222 ,GLOBAL_ATTRIBUTE11
5223 ,GLOBAL_ATTRIBUTE12
5224 ,GLOBAL_ATTRIBUTE13
5225 ,GLOBAL_ATTRIBUTE14
5226 ,GLOBAL_ATTRIBUTE15
5227 ,GLOBAL_ATTRIBUTE2
5228 ,GLOBAL_ATTRIBUTE3
5229 ,GLOBAL_ATTRIBUTE4
5230 ,GLOBAL_ATTRIBUTE5
5231 ,GLOBAL_ATTRIBUTE6
5232 ,GLOBAL_ATTRIBUTE7
5233 ,GLOBAL_ATTRIBUTE8
5234 ,GLOBAL_ATTRIBUTE9
5235 ,HISTORICAL_FLAG
5236 -- ,HQ_ESTB_PARTY_TAX_PROF_ID
5237 -- ,HQ_ESTB_REG_NUMBER
5238 -- ,INTERFACE_ENTITY_CODE
5239 -- ,INTERFACE_TAX_LINE_ID
5240 -- ,INTERNAL_ORG_LOCATION_ID
5241 ,INTERNAL_ORGANIZATION_ID
5242 ,ITEM_DIST_CHANGED_FLAG
5243 -- ,LAST_MANUAL_ENTRY
5244 ,LAST_UPDATE_DATE
5245 ,LAST_UPDATE_LOGIN
5246 ,LAST_UPDATED_BY
5247 ,LEDGER_ID
5248 ,LEGAL_ENTITY_ID
5249 -- ,LEGAL_ENTITY_TAX_REG_NUMBER
5250 -- ,LEGAL_JUSTIFICATION_TEXT1
5251 -- ,LEGAL_JUSTIFICATION_TEXT2
5252 -- ,LEGAL_JUSTIFICATION_TEXT3
5253 -- ,LEGAL_MESSAGE_APPL_2
5254 -- ,LEGAL_MESSAGE_BASIS
5255 -- ,LEGAL_MESSAGE_CALC
5256 -- ,LEGAL_MESSAGE_EXCPT
5257 -- ,LEGAL_MESSAGE_EXMPT
5258 -- ,LEGAL_MESSAGE_POS
5259 -- ,LEGAL_MESSAGE_RATE
5260 -- ,LEGAL_MESSAGE_STATUS
5261 -- ,LEGAL_MESSAGE_THRESHOLD
5262 -- ,LEGAL_MESSAGE_TRN
5263 ,LINE_AMT
5264 -- ,LINE_ASSESSABLE_VALUE
5265 ,MANUALLY_ENTERED_FLAG
5266 ,MINIMUM_ACCOUNTABLE_UNIT
5267 -- ,MRC_LINK_TO_TAX_LINE_ID
5268 ,MRC_TAX_LINE_FLAG
5269 -- ,NREC_TAX_AMT
5270 -- ,NREC_TAX_AMT_FUNCL_CURR
5271 -- ,NREC_TAX_AMT_TAX_CURR
5272 -- ,NUMERIC1
5273 -- ,NUMERIC10
5274 -- ,NUMERIC2
5275 -- ,NUMERIC3
5276 -- ,NUMERIC4
5277 -- ,NUMERIC5
5278 -- ,NUMERIC6
5279 -- ,NUMERIC7
5280 -- ,NUMERIC8
5281 -- ,NUMERIC9
5282 ,OBJECT_VERSION_NUMBER
5283 ,OFFSET_FLAG
5284 -- ,OFFSET_LINK_TO_TAX_LINE_ID
5285 -- ,OFFSET_TAX_RATE_CODE
5286 ,ORIG_SELF_ASSESSED_FLAG
5287 -- ,ORIG_TAX_AMT
5288 -- ,ORIG_TAX_AMT_INCLUDED_FLAG
5289 -- ,ORIG_TAX_AMT_TAX_CURR
5290 -- ,ORIG_TAX_JURISDICTION_CODE
5291 -- ,ORIG_TAX_JURISDICTION_ID
5292 -- ,ORIG_TAX_RATE
5293 -- ,ORIG_TAX_RATE_CODE
5294 -- ,ORIG_TAX_RATE_ID
5295 -- ,ORIG_TAX_STATUS_CODE
5296 -- ,ORIG_TAX_STATUS_ID
5297 -- ,ORIG_TAXABLE_AMT
5298 -- ,ORIG_TAXABLE_AMT_TAX_CURR
5299 -- ,OTHER_DOC_LINE_AMT
5300 -- ,OTHER_DOC_LINE_TAX_AMT
5301 -- ,OTHER_DOC_LINE_TAXABLE_AMT
5302 -- ,OTHER_DOC_SOURCE
5303 ,OVERRIDDEN_FLAG
5304 -- ,PLACE_OF_SUPPLY
5305 -- ,PLACE_OF_SUPPLY_RESULT_ID
5306 -- ,PLACE_OF_SUPPLY_TYPE_CODE
5307 -- ,PRD_TOTAL_TAX_AMT
5308 -- ,PRD_TOTAL_TAX_AMT_FUNCL_CURR
5309 -- ,PRD_TOTAL_TAX_AMT_TAX_CURR
5310 ,PRECISION
5311 ,PROCESS_FOR_RECOVERY_FLAG
5312 ,PRORATION_CODE
5313 ,PURGE_FLAG
5314 -- ,RATE_RESULT_ID
5315 -- ,REC_TAX_AMT
5316 -- ,REC_TAX_AMT_FUNCL_CURR
5317 -- ,REC_TAX_AMT_TAX_CURR
5318 ,RECALC_REQUIRED_FLAG
5319 ,RECORD_TYPE_CODE
5320 -- ,REF_DOC_APPLICATION_ID
5321 -- ,REF_DOC_ENTITY_CODE
5322 -- ,REF_DOC_EVENT_CLASS_CODE
5323 -- ,REF_DOC_LINE_ID
5324 -- ,REF_DOC_LINE_QUANTITY
5325 -- ,REF_DOC_TRX_ID
5326 -- ,REF_DOC_TRX_LEVEL_TYPE
5327 -- ,REGISTRATION_PARTY_TYPE
5328 -- ,RELATED_DOC_APPLICATION_ID
5329 -- ,RELATED_DOC_DATE
5330 -- ,RELATED_DOC_ENTITY_CODE
5331 -- ,RELATED_DOC_EVENT_CLASS_CODE
5332 -- ,RELATED_DOC_NUMBER
5333 -- ,RELATED_DOC_TRX_ID
5334 -- ,RELATED_DOC_TRX_LEVEL_TYPE
5335 -- ,REPORTING_CURRENCY_CODE
5336 ,REPORTING_ONLY_FLAG
5337 -- ,REPORTING_PERIOD_ID
5338 -- ,ROUNDING_LEVEL_CODE
5339 -- ,ROUNDING_LVL_PARTY_TAX_PROF_ID
5340 -- ,ROUNDING_LVL_PARTY_TYPE
5341 -- ,ROUNDING_RULE_CODE
5342 ,SELF_ASSESSED_FLAG
5343 ,SETTLEMENT_FLAG
5344 -- ,STATUS_RESULT_ID
5345 -- ,SUMMARY_TAX_LINE_ID
5346 -- ,SYNC_WITH_PRVDR_FLAG
5347 ,TAX
5348 ,TAX_AMT
5349 ,TAX_AMT_FUNCL_CURR
5350 ,TAX_AMT_INCLUDED_FLAG
5351 ,TAX_AMT_TAX_CURR
5352 -- ,TAX_APPLICABILITY_RESULT_ID
5353 ,TAX_APPORTIONMENT_FLAG
5354 ,TAX_APPORTIONMENT_LINE_NUMBER
5355 -- ,TAX_BASE_MODIFIER_RATE
5356 ,TAX_CALCULATION_FORMULA
5357 -- ,TAX_CODE
5358 ,TAX_CURRENCY_CODE
5359 ,TAX_CURRENCY_CONVERSION_DATE
5360 ,TAX_CURRENCY_CONVERSION_RATE
5361 ,TAX_CURRENCY_CONVERSION_TYPE
5362 ,TAX_DATE
5363 -- ,TAX_DATE_RULE_ID
5364 ,TAX_DETERMINE_DATE
5365 ,TAX_EVENT_CLASS_CODE
5366 ,TAX_EVENT_TYPE_CODE
5367 -- ,TAX_EXCEPTION_ID
5368 -- ,TAX_EXEMPTION_ID
5369 -- ,TAX_HOLD_CODE
5370 -- ,TAX_HOLD_RELEASED_CODE
5371 ,TAX_ID
5372 -- ,TAX_JURISDICTION_CODE
5373 -- ,TAX_JURISDICTION_ID
5374 ,TAX_LINE_ID
5375 ,TAX_LINE_NUMBER
5376 ,TAX_ONLY_LINE_FLAG
5377 ,TAX_POINT_DATE
5378 -- ,TAX_PROVIDER_ID
5379 ,TAX_RATE
5380 -- ,TAX_RATE_BEFORE_EXCEPTION
5381 -- ,TAX_RATE_BEFORE_EXEMPTION
5382 ,TAX_RATE_CODE
5383 ,TAX_RATE_ID
5384 -- ,TAX_RATE_NAME_BEFORE_EXCEPTION
5385 -- ,TAX_RATE_NAME_BEFORE_EXEMPTION,
5386 -- ,TAX_RATE_TYPE
5387 -- ,TAX_REG_NUM_DET_RESULT_ID
5388 ,TAX_REGIME_CODE
5389 ,TAX_REGIME_ID
5390 -- ,TAX_REGIME_TEMPLATE_ID
5391 -- ,TAX_REGISTRATION_ID
5392 -- ,TAX_REGISTRATION_NUMBER
5393 ,TAX_STATUS_CODE
5394 ,TAX_STATUS_ID
5395 -- ,TAX_TYPE_CODE
5396 -- ,TAXABLE_AMT
5397 -- ,TAXABLE_AMT_FUNCL_CURR
5398 -- ,TAXABLE_AMT_TAX_CURR
5399 ,TAXABLE_BASIS_FORMULA
5400 -- ,TAXING_JURIS_GEOGRAPHY_ID
5401 -- ,THRESH_RESULT_ID
5402 ,TRX_CURRENCY_CODE
5403 ,TRX_DATE
5404 ,TRX_ID
5405 -- ,TRX_ID_LEVEL2
5406 -- ,TRX_ID_LEVEL3
5407 -- ,TRX_ID_LEVEL4
5408 -- ,TRX_ID_LEVEL5
5409 -- ,TRX_ID_LEVEL6
5410 ,TRX_LEVEL_TYPE
5411 ,TRX_LINE_DATE
5412 ,TRX_LINE_ID
5413 -- ,TRX_LINE_INDEX
5414 ,TRX_LINE_NUMBER
5415 ,TRX_LINE_QUANTITY
5416 ,TRX_NUMBER
5417 -- ,TRX_USER_KEY_LEVEL1
5418 -- ,TRX_USER_KEY_LEVEL2
5419 -- ,TRX_USER_KEY_LEVEL3
5420 -- ,TRX_USER_KEY_LEVEL4
5421 -- ,TRX_USER_KEY_LEVEL5
5422 -- ,TRX_USER_KEY_LEVEL6
5423 ,UNIT_PRICE
5424 -- ,UNROUNDED_TAX_AMT
5425 -- ,UNROUNDED_TAXABLE_AMT
5426 ,MULTIPLE_JURISDICTIONS_FLAG
5427 )
5428 SELECT /*+ leading(poh) NO_EXPAND use_nl(fc,pol,poll,ptp,atc,atg,atc1,rates,regimes,taxes,status) */
5429 -- NULL DJUSTED_DOC_APPLICATION_ID,
5430 -- NULL DJUSTED_DOC_DATE,
5431 -- NULL ADJUSTED_DOC_ENTITY_CODE,
5432 -- NULL ADJUSTED_DOC_EVENT_CLASS_CODE,
5433 -- NULL ADJUSTED_DOC_LINE_ID,
5434 -- NULL ADJUSTED_DOC_NUMBER,
5435 -- NULL ADJUSTED_DOC_TAX_LINE_ID,
5436 -- NULL AADJUSTED_DOC_TRX_ID,
5437 -- NULL AADJUSTED_DOC_TRX_LEVEL_TYPE,
5438 201 APPLICATION_ID,
5439 -- NULL APPLIED_FROM_APPLICATION_ID,
5440 -- NULL APPLIED_FROM_ENTITY_CODE,
5441 -- NULL APPLIED_FROM_EVENT_CLASS_CODE,
5442 -- NULL APPLIED_FROM_LINE_ID,
5443 -- NULL APPLIED_FROM_TRX_ID,
5444 -- NULL APPLIED_FROM_TRX_LEVEL_TYPE,
5445 -- NULL APPLIED_FROM_TRX_NUMBER,
5446 -- NULL APPLIED_TO_APPLICATION_ID,
5447 -- NULL APPLIED_TO_ENTITY_CODE,
5448 -- NULL APPLIED_TO_EVENT_CLASS_CODE,
5449 -- NULL APPLIED_TO_LINE_ID,
5450 -- NULL APPLIED_TO_TRX_ID,
5451 -- NULL APPLIED_TO_TRX_LEVEL_TYPE,
5452 -- NULL APPLIED_TO_TRX_NUMBER,
5453 'N' ASSOCIATED_CHILD_FROZEN_FLAG,
5454 poll.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,
5455 poll.ATTRIBUTE1 ATTRIBUTE1,
5456 poll.ATTRIBUTE10 ATTRIBUTE10,
5457 poll.ATTRIBUTE11 ATTRIBUTE11,
5458 poll.ATTRIBUTE12 ATTRIBUTE12,
5459 poll.ATTRIBUTE13 ATTRIBUTE13,
5460 poll.ATTRIBUTE14 ATTRIBUTE14,
5461 poll.ATTRIBUTE15 ATTRIBUTE15,
5462 poll.ATTRIBUTE2 ATTRIBUTE2,
5463 poll.ATTRIBUTE3 ATTRIBUTE3,
5464 poll.ATTRIBUTE4 ATTRIBUTE4,
5465 poll.ATTRIBUTE5 ATTRIBUTE5,
5466 poll.ATTRIBUTE6 ATTRIBUTE6,
5467 poll.ATTRIBUTE7 ATTRIBUTE7,
5468 poll.ATTRIBUTE8 ATTRIBUTE8,
5469 poll.ATTRIBUTE9 ATTRIBUTE9,
5470 -- NULL BASIS_RESULT_ID,
5471 -- NULL CAL_TAX_AMT,
5472 -- NULL CAL_TAX_AMT_FUNCL_CURR,
5473 -- NULL CAL_TAX_AMT_TAX_CURR,
5474 -- NULL CALC_RESULT_ID,
5475 'N' CANCEL_FLAG,
5476 -- NULL CHAR1,
5477 -- NULL CHAR10,
5478 -- NULL CHAR2,
5479 -- NULL CHAR3,
5480 -- NULL CHAR4,
5481 -- NULL CHAR5,
5482 -- NULL CHAR6,
5483 -- NULL CHAR7,
5484 -- NULL CHAR8,
5485 -- NULL CHAR9,
5486 'N' COMPOUNDING_DEP_TAX_FLAG,
5487 'N' COMPOUNDING_TAX_FLAG,
5488 'N' COMPOUNDING_TAX_MISS_FLAG,
5489 -- nvl(poh.org_id,-99) CONTENT_OWNER_ID,
5490 ptp.party_tax_profile_id CONTENT_OWNER_ID,
5491 'N' COPIED_FROM_OTHER_DOC_FLAG,
5492 1 CREATED_BY,
5493 SYSDATE CREATION_DATE,
5494 NULL CTRL_TOTAL_LINE_TX_AMT,
5495 poh.rate_date CURRENCY_CONVERSION_DATE,
5496 poh.rate CURRENCY_CONVERSION_RATE,
5497 poh.rate_type CURRENCY_CONVERSION_TYPE,
5498 -- NULL DATE1,
5499 -- NULL DATE10,
5500 -- NULL DATE2,
5501 -- NULL DATE3,
5502 -- NULL DATE4,
5503 -- NULL DATE5,
5504 -- NULL DATE6,
5505 -- NULL DATE7,
5506 -- NULL DATE8,
5507 -- NULL DATE9,
5508 'N' DELETE_FLAG,
5509 -- NULL DIRECT_RATE_RESULT_ID,
5510 -- NULL DOC_EVENT_STATUS,
5511 'N' ENFORCE_FROM_NATURAL_ACCT_FLAG,
5512 -- 'PURCHASE_ORDER' ENTITY_CODE,
5513 NVL2(poll.po_release_id,
5514 'RELEASE','PURCHASE_ORDER') ENTITY_CODE,
5515 -- NULL ESTABLISHMENT_ID,
5516 -- NULL EVAL_EXCPT_RESULT_ID,
5517 -- NULL EVAL_EXMPT_RESULT_ID,
5518 -- 'PO_PA' EVENT_CLASS_CODE,
5519 NVL2(poll.po_release_id,
5520 'RELEASE', 'PO_PA') EVENT_CLASS_CODE,
5521 'PURCHASE ORDER CREATED' EVENT_TYPE_CODE,
5522 -- NULL EXCEPTION_RATE,
5523 -- NULL EXEMPT_CERTIFICATE_NUMBER,
5524 -- NULL EXEMPT_RATE_MODIFIER,
5525 -- NULL EXEMPT_REASON,
5526 -- NULL EXEMPT_REASON_CODE,
5527 'N' FREEZE_UNTIL_OVERRIDDEN_FLAG,
5528 poll.GLOBAL_ATTRIBUTE_CATEGORY, -- GLOBAL_ATTRIBUTE_CATEGORY,
5529 poll.GLOBAL_ATTRIBUTE1, -- GLOBAL_ATTRIBUTE1,
5530 poll.GLOBAL_ATTRIBUTE10, -- GLOBAL_ATTRIBUTE10,
5531 poll.GLOBAL_ATTRIBUTE11, -- GLOBAL_ATTRIBUTE11,
5532 poll.GLOBAL_ATTRIBUTE12, -- GLOBAL_ATTRIBUTE12,
5533 poll.GLOBAL_ATTRIBUTE13, -- GLOBAL_ATTRIBUTE13,
5534 poll.GLOBAL_ATTRIBUTE14, -- GLOBAL_ATTRIBUTE14,
5535 poll.GLOBAL_ATTRIBUTE15, -- GLOBAL_ATTRIBUTE15,
5536 poll.GLOBAL_ATTRIBUTE2, -- GLOBAL_ATTRIBUTE2,
5537 poll.GLOBAL_ATTRIBUTE3, -- GLOBAL_ATTRIBUTE3,
5538 poll.GLOBAL_ATTRIBUTE4, -- GLOBAL_ATTRIBUTE4,
5539 poll.GLOBAL_ATTRIBUTE5, -- GLOBAL_ATTRIBUTE5,
5540 poll.GLOBAL_ATTRIBUTE6, -- GLOBAL_ATTRIBUTE6,
5541 poll.GLOBAL_ATTRIBUTE7, -- GLOBAL_ATTRIBUTE7,
5542 poll.GLOBAL_ATTRIBUTE8, -- GLOBAL_ATTRIBUTE8,
5543 poll.GLOBAL_ATTRIBUTE9, -- GLOBAL_ATTRIBUTE9,
5544 'Y' HISTORICAL_FLAG,
5545 -- NULL HQ_ESTB_PARTY_TAX_PROF_ID,
5546 -- NULL HQ_ESTB_REG_NUMBER,
5547 -- NULL INTERFACE_ENTITY_CODE,
5548 -- NULL INTERFACE_TAX_LINE_ID,
5549 -- NULL NAL_ORG_LOCATION_ID,
5550 nvl(poh.org_id,-99) INTERNAL_ORGANIZATION_ID,
5551 'N' ITEM_DIST_CHANGED_FLAG,
5552 -- NULL LAST_MANUAL_ENTRY,
5553 SYSDATE LAST_UPDATE_DATE,
5554 1 LAST_UPDATE_LOGIN,
5555 1 LAST_UPDATED_BY,
5556 poh.set_of_books_id LEDGER_ID,
5557 NVL(poh.org_information2,-99) LEGAL_ENTITY_ID,
5558 -- NULL LEGAL_ENTITY_TAX_REG_NUMBER ,
5559 -- NULL LEGAL_JUSTIFICATION_TEXT1,
5560 -- NULL LEGAL_JUSTIFICATION_TEXT2,
5561 -- NULL LEGAL_JUSTIFICATION_TEXT3,
5562 -- NULL LEGAL_MESSAGE_APPL_2,
5563 -- NULL LEGAL_MESSAGE_BASIS,
5564 -- NULL LEGAL_MESSAGE_CALC,
5565 -- NULL LEGAL_MESSAGE_EXCPT,
5566 -- NULL LEGAL_MESSAGE_EXMPT,
5567 -- NULL LEGAL_MESSAGE_POS,
5568 -- NULL LEGAL_MESSAGE_RATE,
5569 -- NULL LEGAL_MESSAGE_STATUS,
5570 -- NULL LEGAL_MESSAGE_THRESHOLD,
5571 -- NULL LEGAL_MESSAGE_TRN,
5572 DECODE(pol.purchase_basis,
5573 'TEMP LABOR', NVL(POLL.amount,0),
5574 'SERVICES', DECODE(pol.matching_basis, 'AMOUNT',NVL(POLL.amount,0),
5575 NVL(poll.quantity,0) *
5576 NVL(poll.price_override,NVL(pol.unit_price,0))),
5577 NVL(poll.quantity,0) * NVL(poll.price_override,NVL(pol.unit_price,0)))
5578 LINE_AMT,
5579 -- NULL LINE_ASSESSABLE_VALUE,
5580 'N' MANUALLY_ENTERED_FLAG,
5581 fc.minimum_accountable_unit MINIMUM_ACCOUNTABLE_UNIT,
5582 -- NULL MRC_LINK_TO_TAX_LINE_ID,
5583 'N' MRC_TAX_LINE_FLAG,
5584 -- NULL NREC_TAX_AMT,
5585 -- NULL NREC_TAX_AMT_FUNCL_CURR,
5586 -- NULL NREC_TAX_AMT_TAX_CURR,
5587 -- NULL NUMERIC1,
5588 -- NULL NUMERIC10,
5589 -- NULL NUMERIC2,
5590 -- NULL NUMERIC3,
5591 -- NULL NUMERIC4,
5592 -- NULL NUMERIC5,
5593 -- NULL NUMERIC6,
5594 -- NULL NUMERIC7,
5595 -- NULL NUMERIC8,
5596 -- NULL NUMERIC9,
5597 1 OBJECT_VERSION_NUMBER,
5598 'N' OFFSET_FLAG,
5599 -- NULL OFFSET_LINK_TO_TAX_LINE_ID,
5600 -- NULL OFFSET_TAX_RATE_CODE,
5601 'N' ORIG_SELF_ASSESSED_FLAG,
5602 -- NULL ORIG_TAX_AMT,
5603 -- NULL ORIG_TAX_AMT_INCLUDED_FLAG,
5604 -- NULL ORIG_TAX_AMT_TAX_CURR,
5605 -- NULL ORIG_TAX_JURISDICTION_CODE,
5606 -- NULL ORIG_TAX_JURISDICTION_ID,
5607 -- NULL ORIG_TAX_RATE,
5608 -- NULL ORIG_TAX_RATE_CODE,
5609 -- NULL ORIG_TAX_RATE_ID,
5610 -- NULL ORIG_TAX_STATUS_CODE,
5611 -- NULL ORIG_TAX_STATUS_ID,
5612 -- NULL ORIG_TAXABLE_AMT,
5613 -- NULL ORIG_TAXABLE_AMT_TAX_CURR,
5614 -- NULL OTHER_DOC_LINE_AMT,
5615 -- NULL OTHER_DOC_LINE_TAX_AMT,
5616 -- NULL OTHER_DOC_LINE_TAXABLE_AMT,
5617 -- NULL OTHER_DOC_SOURCE,
5618 'N' OVERRIDDEN_FLAG,
5619 -- NULL PLACE_OF_SUPPLY,
5620 -- NULL PLACE_OF_SUPPLY_RESULT_ID ,
5621 -- NULL PLACE_OF_SUPPLY_TYPE_CODE,
5622 -- NULL PRD_TOTAL_TAX_AMT,
5623 -- NULL PRD_TOTAL_TAX_AMT_FUNCL_CURR,
5624 -- NULL PRD_TOTAL_TAX_AMT_TAX_CURR ,
5625 NVL(fc.precision, 0) PRECISION,
5626 -- fc.precision PRECISION,
5627 'N' PROCESS_FOR_RECOVERY_FLAG,
5628 NULL PRORATION_CODE,
5629 'N' PURGE_FLAG,
5630 -- NULL RATE_RESULT_ID,
5631 -- NULL REC_TAX_AMT,
5632 -- NULL REC_TAX_AMT_FUNCL_CURR,
5633 -- NULL REC_TAX_AMT_TAX_CURR,
5634 'N' RECALC_REQUIRED_FLAG,
5635 'MIGRATED' RECORD_TYPE_CODE,
5636 -- NULL REF_DOC_APPLICATION_ID,
5637 -- NULL REF_DOC_ENTITY_CODE,
5638 -- NULL REF_DOC_EVENT_CLASS_CODE,
5639 -- NULL REF_DOC_LINE_ID,
5640 -- NULL REF_DOC_LINE_QUANTITY,
5641 -- NULL REF_DOC_TRX_ID,
5642 -- NULL REF_DOC_TRX_LEVEL_TYPE,
5643 -- NULL REGISTRATION_PARTY_TYPE,
5644 -- NULL RELATED_DOC_APPLICATION_ID,
5645 -- NULL RELATED_DOC_DATE,
5646 -- NULL RELATED_DOC_ENTITY_CODE,
5647 -- NULL RELATED_DOC_EVENT_CLASS_CODE,
5648 -- NULL RELATED_DOC_NUMBER,
5649 -- NULL RELATED_DOC_TRX_ID,
5650 -- NULL RELATED_DOC_TRX_LEVEL_TYPE,
5651 -- NULL REPORTING_CURRENCY_CODE,
5652 'N' REPORTING_ONLY_FLAG,
5653 -- NULL REPORTING_PERIOD_ID,
5654 -- NULL ROUNDING_LEVEL_CODE,
5655 -- NULL ROUNDING_LVL_PARTY_TAX_PROF_ID,
5656 -- NULL ROUNDING_LVL_PARTY_TYPE,
5657 -- NULL ROUNDING_RULE_CODE,
5658 'N' SELF_ASSESSED_FLAG,
5659 'N' SETTLEMENT_FLAG,
5660 -- NULL STATUS_RESULT_ID,
5661 -- NULL SUMMARY_TAX_LINE_ID,
5662 -- NULL SYNC_WITH_PRVDR_FLAG,
5663 rates.tax TAX ,
5664 decode(FC.Minimum_Accountable_Unit, NULL,
5665 ROUND((NVL(poll.quantity,0) * NVL(poll.price_override, NVL(pol.unit_price,0)))* (nvl(atc1.tax_rate,0)/100), NVL(FC.Precision,0)),
5666 ROUND((NVL(poll.quantity,0) * NVL(poll.price_override, NVL(pol.unit_price,0)))* (nvl(atc1.tax_rate,0)/100)/FC.Minimum_Accountable_Unit)
5667 * FC.Minimum_Accountable_Unit)
5668 TAX_AMT,
5669 decode(FC.Minimum_Accountable_Unit, NULL,
5670 ROUND((NVL(poll.quantity,0) * NVL(poll.price_override, NVL(pol.unit_price,0)))* (nvl(atc1.tax_rate,0)/100), NVL(FC.Precision,0)),
5671 ROUND((NVL(poll.quantity,0) * NVL(poll.price_override, NVL(pol.unit_price,0)))* (nvl(atc1.tax_rate,0)/100)/FC.Minimum_Accountable_Unit)
5672 * FC.Minimum_Accountable_Unit)
5673 TAX_AMT_FUNCL_CURR,
5674 'N' TAX_AMT_INCLUDED_FLAG,
5675 decode(FC.Minimum_Accountable_Unit, NULL,
5676 ROUND((NVL(poll.quantity,0) * NVL(poll.price_override, NVL(pol.unit_price,0)))* (nvl(atc1.tax_rate,0)/100), NVL(FC.Precision,0)),
5677 ROUND((NVL(poll.quantity,0) * NVL(poll.price_override, NVL(pol.unit_price,0)))* (nvl(atc1.tax_rate,0)/100)/FC.Minimum_Accountable_Unit)
5678 * FC.Minimum_Accountable_Unit)
5679 TAX_AMT_TAX_CURR,
5680 -- NULL TAX_APPLICABILITY_RESULT_ID,
5681 'Y' TAX_APPORTIONMENT_FLAG,
5682 RANK() OVER
5683 (PARTITION BY
5684 NVL(poll.po_release_id,
5685 poh.po_header_id),
5686 poll.line_location_id,
5687 rates.tax_regime_code,
5688 rates.tax
5689 ORDER BY atg.tax_code_id) TAX_APPORTIONMENT_LINE_NUMBER,
5690 -- NULL TAX_BASE_MODIFIER_RATE,
5691 'STANDARD_TC' TAX_CALCULATION_FORMULA,
5692 -- NULL TAX_CODE,
5693 taxes.tax_currency_code TAX_CURRENCY_CODE,
5694 poh.rate_date TAX_CURRENCY_CONVERSION_DATE,
5695 poh.rate TAX_CURRENCY_CONVERSION_RATE,
5696 poh.rate_type TAX_CURRENCY_CONVERSION_TYPE,
5697 poll.last_update_date TAX_DATE,
5698 -- NULL TAX_DATE_RULE_ID,
5699 poll.last_update_date TAX_DETERMINE_DATE,
5700 'PURCHASE_TRANSACTION' TAX_EVENT_CLASS_CODE,
5701 'VALIDATE' TAX_EVENT_TYPE_CODE,
5702 -- NULL TAX_EXCEPTION_ID,
5703 -- NULL TAX_EXEMPTION_ID,
5704 -- NULL TAX_HOLD_CODE,
5705 -- NULL TAX_HOLD_RELEASED_CODE,
5706 taxes.tax_id TAX_ID,
5707 -- NULL TAX_JURISDICTION_CODE,
5708 -- NULL TAX_JURISDICTION_ID,
5709 zx_lines_s.nextval TAX_LINE_ID,
5710 RANK() OVER
5711 (PARTITION BY
5712 NVL(poll.po_release_id,
5713 poh.po_header_id)
5714 ORDER BY
5715 poll.line_location_id,
5716 atg.tax_code_id,
5717 atc.tax_id) TAX_LINE_NUMBER,
5718 'N' TAX_ONLY_LINE_FLAG,
5719 poll.last_update_date TAX_POINT_DATE,
5720 -- NULL TAX_PROVIDER_ID,
5721 rates.percentage_rate TAX_RATE,
5722 -- NULL TAX_RATE_BEFORE_EXCEPTION,
5723 -- NULL TAX_RATE_BEFORE_EXEMPTION,
5724 rates.tax_rate_code TAX_RATE_CODE,
5725 rates.tax_rate_id TAX_RATE_ID,
5726 -- NULL TAX_RATE_NAME_BEFORE_EXCEPTION,
5727 -- NULL TAX_RATE_NAME_BEFORE_EXEMPTION,
5728 -- NULL TAX_RATE_TYPE,
5729 -- NULL TAX_REG_NUM_DET_RESULT_ID,
5730 rates.tax_regime_code TAX_REGIME_CODE,
5731 regimes.tax_regime_id TAX_REGIME_ID,
5732 -- NULL TAX_REGIME_TEMPLATE_ID,
5733 -- NULL TAX_REGISTRATION_ID,
5734 -- NULL TAX_REGISTRATION_NUMBER,
5735 rates.tax_status_code TAX_STATUS_CODE,
5736 status.tax_status_id TAX_STATUS_ID,
5737 -- NULL TAX_TYPE_CODE,
5738 -- NULL TAXABLE_AMT,
5739 -- NULL TAXABLE_AMT_FUNCL_CURR,
5740 -- NULL TAXABLE_AMT_TAX_CURR,
5741 'STANDARD_TB' TAXABLE_BASIS_FORMULA ,
5742 -- NULL TAXING_JURIS_GEOGRAPHY_ID ,
5743 -- NULL THRESH_RESULT_ID,
5744 NVL(poh.currency_code,
5745 poh.base_currency_code) TRX_CURRENCY_CODE,
5746 poh.last_update_date TRX_DATE,
5747 -- poh.po_header_id TRX_ID,
5748 NVL(poll.po_release_id,
5749 poh.po_header_id) TRX_ID,
5750 -- NULL TRX_ID_LEVEL2,
5751 -- NULL TRX_ID_LEVEL3,
5752 -- NULL TRX_ID_LEVEL4,
5753 -- NULL TRX_ID_LEVEL5,
5754 -- NULL TRX_ID_LEVEL6,
5755 'SHIPMENT' TRX_LEVEL_TYPE,
5756 poll.LAST_UPDATE_DATE TRX_LINE_DATE ,
5757 poll.line_location_id TRX_LINE_ID,
5758 -- NULL TRX_LINE_INDEX,
5759 poll.SHIPMENT_NUM TRX_LINE_NUMBER,
5760 poll.quantity TRX_LINE_QUANTITY ,
5761 poh.segment1 TRX_NUMBER,
5762 -- NULL TRX_USER_KEY_LEVEL1,
5763 -- NULL TRX_USER_KEY_LEVEL2,
5764 -- NULL TRX_USER_KEY_LEVEL3,
5765 -- NULL TRX_USER_KEY_LEVEL4,
5766 -- NULL TRX_USER_KEY_LEVEL5,
5767 -- NULL TRX_USER_KEY_LEVEL6,
5768 NVL(poll.price_override,
5769 pol.unit_price) UNIT_PRICE,
5770 -- pol.unit_price UNIT_PRICE,
5771 -- NULL UNROUNDED_TAX_AMT,
5772 -- NULL UNROUNDED_TAXABLE_AMT,
5773 'N' MULTIPLE_JURISDICTIONS_FLAG
5774 FROM
5775 (SELECT /*+ NO_MERGE NO_EXPAND ROWID(poh) use_hash(fsp) use_hash(aps)
5776 swap_join_inputs(fsp) swap_join_inputs(upd)
5777 swap_join_inputs(aps) swap_join_inputs(oi)*/
5778 poh.* , fsp.org_id fsp_org_id, fsp.set_of_books_id,
5779 aps.base_currency_code, oi.org_information2
5780 FROM po_headers_all poh,
5781 financials_system_params_all fsp,
5782 xla_upgrade_dates upd,
5783 ap_system_parameters_all aps,
5784 hr_organization_information oi
5785 WHERE poh.rowid BETWEEN p_start_rowid AND p_end_rowid
5786 AND NVL(poh.closed_code, 'X') <> 'FINALLY CLOSED'
5787 AND NVL(poh.org_id,-99) = NVL(fsp.org_id,-99)
5788 AND upd.ledger_id = fsp.set_of_books_id
5789 AND NVL(aps.org_id, -99) = NVL(fsp.org_id,-99)
5790 AND aps.set_of_books_id = fsp.set_of_books_id
5791 AND (trunc(poh.last_update_date) between upd.start_date and upd.end_date)
5792 AND oi.organization_id(+) = poh.org_id
5793 AND oi.org_information_context(+) = 'Operating Unit Information'
5794 ) poh,
5795 fnd_currencies fc,
5796 po_lines_all pol,
5797 po_line_locations_all poll,
5798 zx_party_tax_profile ptp,
5799 ap_tax_codes_all atc,
5800 ar_tax_group_codes_all atg,
5801 ap_tax_codes_all atc1,
5802 zx_rates_b rates,
5803 zx_regimes_b regimes,
5804 zx_taxes_b taxes,
5805 zx_status_b status
5806 WHERE NVL(poh.currency_code, poh.base_currency_code) = fc.currency_code(+)
5807 AND poh.po_header_id = pol.po_header_id
5808 AND pol.po_header_id = poll.po_header_id
5809 AND pol.po_line_id = poll.po_line_id
5810 AND nvl(atc.org_id,-99)=nvl(poh.fsp_org_id,-99)
5811 AND poll.tax_code_id = atc.tax_id
5812 AND atc.tax_type = 'TAX_GROUP'
5813 AND poll.tax_code_id = atg.tax_group_id
5814 AND atg.start_date <= poll.last_update_date
5815 AND (atg.end_date >= poll.last_update_date OR atg.end_date IS NULL)
5816 AND atg.enabled_flag='Y'
5817 AND atc1.tax_id = atg.tax_code_id
5818 AND atc1.start_date <= poll.last_update_date
5819 AND(atc1.inactive_date >= poll.last_update_date OR atc1.inactive_date IS NULL)
5820 AND ptp.party_id = DECODE(l_multi_org_flag,'N',l_org_id,poll.org_id)
5821 AND ptp.party_type_code = 'OU'
5822 AND rates.source_id = atg.tax_code_id
5823 AND regimes.tax_regime_code(+) = rates.tax_regime_code
5824 AND taxes.tax_regime_code(+) = rates.tax_regime_code
5825 AND taxes.tax(+) = rates.tax
5826 AND taxes.content_owner_id(+) = rates.content_owner_id
5827 AND status.tax_regime_code(+) = rates.tax_regime_code
5828 AND status.tax(+) = rates.tax
5829 AND status.content_owner_id(+) = rates.content_owner_id
5830 AND status.tax_status_code(+) = rates.tax_status_code
5831 AND NOT EXISTS
5832 (SELECT 1 FROM zx_lines zxl
5833 WHERE zxl.APPLICATION_ID = 201
5834 AND zxl.EVENT_CLASS_CODE = NVL2(poll.po_release_id, 'RELEASE', 'PO_PA')
5835 AND zxl.TRX_ID = NVL(poll.po_release_id, poh.po_header_id)
5836 AND zxl.ENTITY_CODE = NVL2(poll.po_release_id, 'RELEASE','PURCHASE_ORDER'));
5837
5838 -- COMMIT;
5839
5840 INSERT INTO ZX_REC_NREC_DIST
5841 (TAX_LINE_ID
5842 ,REC_NREC_TAX_DIST_ID
5843 ,REC_NREC_TAX_DIST_NUMBER
5844 ,APPLICATION_ID
5845 ,CONTENT_OWNER_ID
5846 ,CURRENCY_CONVERSION_DATE
5847 ,CURRENCY_CONVERSION_RATE
5848 ,CURRENCY_CONVERSION_TYPE
5849 ,ENTITY_CODE
5850 ,EVENT_CLASS_CODE
5851 ,EVENT_TYPE_CODE
5852 ,LEDGER_ID
5853 ,MINIMUM_ACCOUNTABLE_UNIT
5854 ,PRECISION
5855 ,RECORD_TYPE_CODE
5856 -- ,REF_DOC_APPLICATION_ID
5857 -- ,REF_DOC_ENTITY_CODE
5858 -- ,REF_DOC_EVENT_CLASS_CODE
5859 -- ,REF_DOC_LINE_ID
5860 -- ,REF_DOC_TRX_ID
5861 -- ,REF_DOC_TRX_LEVEL_TYPE
5862 -- ,SUMMARY_TAX_LINE_ID
5863 ,TAX
5864 ,TAX_APPORTIONMENT_LINE_NUMBER
5865 ,TAX_CURRENCY_CODE
5866 ,TAX_CURRENCY_CONVERSION_DATE
5867 ,TAX_CURRENCY_CONVERSION_RATE
5868 ,TAX_CURRENCY_CONVERSION_TYPE
5869 ,TAX_EVENT_CLASS_CODE
5870 ,TAX_EVENT_TYPE_CODE
5871 ,TAX_ID
5872 ,TAX_LINE_NUMBER
5873 ,TAX_RATE
5874 ,TAX_RATE_CODE
5875 ,TAX_RATE_ID
5876 ,TAX_REGIME_CODE
5877 ,TAX_REGIME_ID
5878 ,TAX_STATUS_CODE
5879 ,TAX_STATUS_ID
5880 ,TRX_CURRENCY_CODE
5881 ,TRX_ID
5882 ,TRX_LEVEL_TYPE
5883 ,TRX_LINE_ID
5884 ,TRX_LINE_NUMBER
5885 ,TRX_NUMBER
5886 ,UNIT_PRICE
5887 -- ,ACCOUNT_CCID
5888 -- ,ACCOUNT_STRING
5889 -- ,ADJUSTED_DOC_TAX_DIST_ID
5890 -- ,APPLIED_FROM_TAX_DIST_ID
5891 -- ,APPLIED_TO_DOC_CURR_CONV_RATE
5892 -- ,AWARD_ID
5893 ,EXPENDITURE_ITEM_DATE
5894 ,EXPENDITURE_ORGANIZATION_ID
5895 ,EXPENDITURE_TYPE
5896 -- ,FUNC_CURR_ROUNDING_ADJUSTMENT
5897 -- ,GL_DATE
5898 -- ,INTENDED_USE
5899 -- ,ITEM_DIST_NUMBER
5900 -- ,MRC_LINK_TO_TAX_DIST_ID
5901 -- ,ORIG_REC_NREC_RATE
5902 -- ,ORIG_REC_NREC_TAX_AMT
5903 -- ,ORIG_REC_NREC_TAX_AMT_TAX_CURR
5904 -- ,ORIG_REC_RATE_CODE
5905 -- ,PER_TRX_CURR_UNIT_NR_AMT
5906 -- ,PER_UNIT_NREC_TAX_AMT
5907 -- ,PRD_TAX_AMT
5908 -- ,PRICE_DIFF
5909 ,PROJECT_ID
5910 -- ,QTY_DIFF
5911 -- ,RATE_TAX_FACTOR
5912 ,REC_NREC_RATE
5913 ,REC_NREC_TAX_AMT
5914 ,REC_NREC_TAX_AMT_FUNCL_CURR
5915 ,REC_NREC_TAX_AMT_TAX_CURR
5916 ,RECOVERY_RATE_CODE
5917 ,RECOVERY_RATE_ID
5918 ,RECOVERY_TYPE_CODE
5919 -- ,RECOVERY_TYPE_ID
5920 -- ,REF_DOC_CURR_CONV_RATE
5921 -- ,REF_DOC_DIST_ID
5922 -- ,REF_DOC_PER_UNIT_NREC_TAX_AMT
5923 -- ,REF_DOC_TAX_DIST_ID
5924 -- ,REF_DOC_TRX_LINE_DIST_QTY
5925 -- ,REF_DOC_UNIT_PRICE
5926 -- ,REF_PER_TRX_CURR_UNIT_NR_AMT
5927 -- ,REVERSED_TAX_DIST_ID
5928 -- ,ROUNDING_RULE_CODE
5929 ,TASK_ID
5930 -- ,TAXABLE_AMT_FUNCL_CURR
5931 -- ,TAXABLE_AMT_TAX_CURR
5932 -- ,TRX_LINE_DIST_AMT
5933 ,TRX_LINE_DIST_ID
5934 -- ,TRX_LINE_DIST_QTY
5935 -- ,TRX_LINE_DIST_TAX_AMT
5936 -- ,UNROUNDED_REC_NREC_TAX_AMT
5937 -- ,UNROUNDED_TAXABLE_AMT
5938 -- ,TAXABLE_AMT
5939 ,ATTRIBUTE_CATEGORY
5940 ,ATTRIBUTE1
5941 ,ATTRIBUTE2
5942 ,ATTRIBUTE3
5943 ,ATTRIBUTE4
5944 ,ATTRIBUTE5
5945 ,ATTRIBUTE6
5946 ,ATTRIBUTE7
5947 ,ATTRIBUTE8
5948 ,ATTRIBUTE9
5949 ,ATTRIBUTE10
5950 ,ATTRIBUTE11
5951 ,ATTRIBUTE12
5952 ,ATTRIBUTE13
5953 ,ATTRIBUTE14
5954 ,ATTRIBUTE15
5955 ,HISTORICAL_FLAG
5956 ,OVERRIDDEN_FLAG
5957 ,SELF_ASSESSED_FLAG
5958 ,TAX_APPORTIONMENT_FLAG
5959 ,TAX_ONLY_LINE_FLAG
5960 ,INCLUSIVE_FLAG
5961 ,MRC_TAX_DIST_FLAG
5962 ,REC_TYPE_RULE_FLAG
5963 ,NEW_REC_RATE_CODE_FLAG
5964 ,RECOVERABLE_FLAG
5965 ,REVERSE_FLAG
5966 ,REC_RATE_DET_RULE_FLAG
5967 ,BACKWARD_COMPATIBILITY_FLAG
5968 ,FREEZE_FLAG
5969 ,POSTING_FLAG
5970 ,LEGAL_ENTITY_ID
5971 ,CREATED_BY
5972 ,CREATION_DATE
5973 ,LAST_MANUAL_ENTRY
5974 ,LAST_UPDATE_DATE
5975 ,LAST_UPDATE_LOGIN
5976 ,LAST_UPDATED_BY
5977 ,OBJECT_VERSION_NUMBER
5978 )
5979 SELECT /*+ NO_EXPAND leading(pohzd) use_nl(fc, rates)*/
5980 pohzd.tax_line_id TAX_LINE_ID,
5981 zx_rec_nrec_dist_s.nextval REC_NREC_TAX_DIST_ID,
5982 RANK() OVER
5983 (PARTITION BY pohzd.trx_id,
5984 pohzd.p_po_distribution_id
5985 ORDER BY pohzd.tax_rate_id,
5986 tmp.rec_flag) REC_NREC_TAX_DIST_NUMBER,
5987 201 APPLICATION_ID,
5988 pohzd.content_owner_id, -- CONTENT_OWNER_ID
5989 pohzd.CURRENCY_CONVERSION_DATE,
5990 pohzd.CURRENCY_CONVERSION_RATE,
5991 pohzd.CURRENCY_CONVERSION_TYPE,
5992 pohzd.ENTITY_CODE, -- ENTITY_CODE,
5993 pohzd.EVENT_CLASS_CODE, -- EVENT_CLASS_CODE,
5994 'PURCHASE ORDER CREATED' EVENT_TYPE_CODE,
5995 pohzd.ledger_id, -- LEDGER_ID,
5996 pohzd.MINIMUM_ACCOUNTABLE_UNIT,
5997 pohzd.PRECISION, -- PRECISION,
5998 'MIGRATED' RECORD_TYPE_CODE,
5999 -- NULL REF_DOC_APPLICATION_ID,
6000 -- NULL REF_DOC_ENTITY_CODE,
6001 -- NULL REF_DOC_EVENT_CLASS_CODE,
6002 -- NULL REF_DOC_LINE_ID,
6003 -- NULL REF_DOC_TRX_ID,
6004 -- NULL REF_DOC_TRX_LEVEL_TYPE,
6005 -- NULL SUMMARY_TAX_LINE_ID,
6006 pohzd.tax TAX,
6007 pohzd.TAX_APPORTIONMENT_LINE_NUMBER,
6008 pohzd.TAX_CURRENCY_CODE, -- TAX_CURRENCY_CODE,
6009 pohzd.TAX_CURRENCY_CONVERSION_DATE, -- TAX_CURRENCY_CONVERSION_DATE,
6010 pohzd.TAX_CURRENCY_CONVERSION_RATE, -- TAX_CURRENCY_CONVERSION_RATE,
6011 pohzd.TAX_CURRENCY_CONVERSION_TYPE, -- TAX_CURRENCY_CONVERSION_TYPE,
6012 'PURCHASE_TRANSACTION' TAX_EVENT_CLASS_CODE,
6013 'VALIDATE' TAX_EVENT_TYPE_CODE,
6014 pohzd.tax_id, -- TAX_ID,
6015 pohzd.tax_line_number, -- TAX_LINE_NUMBER,
6016 pohzd.tax_rate, -- TAX_RATE,
6017 pohzd.tax_rate_code, -- TAX_RATE_CODE,
6018 pohzd.tax_rate_id, -- TAX_RATE_ID,
6019 pohzd.tax_regime_code, -- TAX_REGIME_CODE,
6020 pohzd.tax_regime_id , -- TAX_REGIME_ID,
6021 pohzd.tax_status_code, -- TAX_STATUS_CODE,
6022 pohzd.tax_status_id, -- TAX_STATUS_ID,
6023 pohzd.trx_currency_code, -- TRX_CURRENCY_CODE,
6024 pohzd.trx_id, -- TRX_ID,
6025 'SHIPMENT' TRX_LEVEL_TYPE,
6026 pohzd.trx_line_id, -- TRX_LINE_ID,
6027 pohzd.trx_line_number, -- TRX_LINE_NUMBER,
6028 pohzd.trx_number, -- TRX_NUMBER,
6029 pohzd.unit_price, -- UNIT_PRICE,
6030 -- NULL ACCOUNT_CCID,
6031 -- NULL ACCOUNT_STRING,
6032 -- NULL ADJUSTED_DOC_TAX_DIST_ID,
6033 -- NULL APPLIED_FROM_TAX_DIST_ID,
6034 -- NULL APPLIED_TO_DOC_CURR_CONV_RATE,
6035 -- NULL AWARD_ID,
6036 pohzd.p_expenditure_item_date EXPENDITURE_ITEM_DATE,
6037 pohzd.p_expenditure_organization_id EXPENDITURE_ORGANIZATION_ID,
6038 pohzd.p_expenditure_type EXPENDITURE_TYPE ,
6039 -- NULL FUNC_CURR_ROUNDING_ADJUSTMENT,
6040 -- NULL GL_DATE,
6041 -- NULL INTENDED_USE,
6042 -- NULL ITEM_DIST_NUMBER,
6043 -- NULL MRC_LINK_TO_TAX_DIST_ID,
6044 -- NULL ORIG_REC_NREC_RATE,
6045 -- NULL ORIG_REC_NREC_TAX_AMT,
6046 -- NULL ORIG_REC_NREC_TAX_AMT_TAX_CURR,
6047 -- NULL ORIG_REC_RATE_CODE,
6048 -- NULL PER_TRX_CURR_UNIT_NR_AMT,
6049 -- NULL PER_UNIT_NREC_TAX_AMT,
6050 -- NULL PRD_TAX_AMT,
6051 -- NULL PRICE_DIFF,
6052 pohzd.p_project_id PROJECT_ID,
6053 -- NULL QTY_DIFF,
6054 -- NULL RATE_TAX_FACTOR,
6055 DECODE(tmp.rec_flag,
6056 'Y', NVL(NVL(pohzd.p_recovery_rate, pohzd.d_rec_rate), 0),
6057 'N', 100 - NVL(NVL(pohzd.p_recovery_rate, pohzd.d_rec_rate), 0))
6058 REC_NREC_RATE,
6059 DECODE(tmp.rec_flag,
6060 'N',
6061 DECODE(fc.Minimum_Accountable_Unit,null,
6062 ROUND((NVL(pohzd.unit_price, 0))* (nvl(pohzd.tax_rate,0)/100) * nvl(pohzd.p_quantity_ordered,0) *
6063 (nvl(decode(pohzd.p_recovery_rate,null,(100 - nvl(pohzd.d_rec_rate,0)), (100 - nvl(pohzd.p_recovery_rate,0))),0)/100) ,NVL(FC.precision,0)),
6064 ROUND((NVL(pohzd.unit_price, 0))* (nvl(pohzd.tax_rate,0)/100) *
6065 NVL(pohzd.p_quantity_ordered,0) * (nvl(decode(pohzd.p_recovery_rate,null,(100 - nvl(pohzd.d_rec_rate,0)),
6066 (100 - nvl(pohzd.p_recovery_rate,0))),0)/100)/FC.Minimum_Accountable_Unit)* (FC.Minimum_Accountable_Unit)),
6067 'Y',
6068 DECODE(fc.Minimum_Accountable_Unit,null,
6069 (ROUND((NVL(pohzd.unit_price, 0)) * (NVL(pohzd.tax_rate,0)/100) * NVL(pohzd.p_quantity_ordered,0), NVL(FC.precision,0)) -
6070 ROUND((NVL(pohzd.unit_price, 0))* (nvl(pohzd.tax_rate,0)/100) * nvl(pohzd.p_quantity_ordered,0) *
6071 (nvl(decode(pohzd.p_recovery_rate,null,(100 - nvl(pohzd.d_rec_rate,0)), (100 - nvl(pohzd.p_recovery_rate,0))),0)/100) ,NVL(FC.precision,0))),
6072 (ROUND((NVL(pohzd.unit_price, 0)) * (NVL(pohzd.tax_rate,0)/100) * NVL(pohzd.p_quantity_ordered,0)/FC.Minimum_Accountable_Unit)* (FC.Minimum_Accountable_Unit) -
6073 ROUND((NVL(pohzd.unit_price, 0))* (nvl(pohzd.tax_rate,0)/100) *
6074 NVL(pohzd.p_quantity_ordered,0) * (nvl(decode(pohzd.p_recovery_rate,null,(100 - nvl(pohzd.d_rec_rate,0)),
6075 (100 - nvl(pohzd.p_recovery_rate,0))),0)/100)/FC.Minimum_Accountable_Unit)* (FC.Minimum_Accountable_Unit)))
6076 ) REC_NREC_TAX_AMT,
6077 DECODE(tmp.rec_flag,
6078 'N',
6079 DECODE(fc.Minimum_Accountable_Unit,null,
6080 ROUND((NVL(pohzd.unit_price, 0))* (nvl(pohzd.tax_rate,0)/100) * nvl(pohzd.p_quantity_ordered,0) *
6081 (nvl(decode(pohzd.p_recovery_rate,null,(100 - nvl(pohzd.d_rec_rate,0)), (100 - nvl(pohzd.p_recovery_rate,0))),0)/100) ,NVL(FC.precision,0)),
6082 ROUND((NVL(pohzd.unit_price, 0))* (nvl(pohzd.tax_rate,0)/100) *
6083 nvl(pohzd.p_quantity_ordered,0) * (nvl(decode(pohzd.p_recovery_rate,null,(100 - nvl(pohzd.d_rec_rate,0)),
6084 (100 - nvl(pohzd.p_recovery_rate,0))),0)/100)/FC.Minimum_Accountable_Unit)* (FC.Minimum_Accountable_Unit)),
6085 'Y',
6086 DECODE(fc.Minimum_Accountable_Unit,null,
6087 (ROUND((NVL(pohzd.unit_price, 0)) * (NVL(pohzd.tax_rate,0)/100) * NVL(pohzd.p_quantity_ordered,0), NVL(FC.precision,0)) -
6088 ROUND((NVL(pohzd.unit_price, 0))* (nvl(pohzd.tax_rate,0)/100) * nvl(pohzd.p_quantity_ordered,0) *
6089 (nvl(decode(pohzd.p_recovery_rate,null,(100 - nvl(pohzd.d_rec_rate,0)), (100 - nvl(pohzd.p_recovery_rate,0))),0)/100) ,NVL(FC.precision,0))),
6090 (ROUND((NVL(pohzd.unit_price, 0)) * (NVL(pohzd.tax_rate,0)/100) * NVL(pohzd.p_quantity_ordered,0)/FC.Minimum_Accountable_Unit)* (FC.Minimum_Accountable_Unit) -
6091 ROUND((NVL(pohzd.unit_price, 0))* (nvl(pohzd.tax_rate,0)/100) *
6092 NVL(pohzd.p_quantity_ordered,0) * (nvl(decode(pohzd.p_recovery_rate,null,(100 - nvl(pohzd.d_rec_rate,0)),
6093 (100 - nvl(pohzd.p_recovery_rate,0))),0)/100)/FC.Minimum_Accountable_Unit)* (FC.Minimum_Accountable_Unit)))
6094 ) REC_NREC_TAX_AMT_FUNCL_CURR,
6095 DECODE(tmp.rec_flag,
6096 'N',
6097 DECODE(fc.Minimum_Accountable_Unit,null,
6098 ROUND((NVL(pohzd.unit_price, 0))* (nvl(pohzd.tax_rate,0)/100) * nvl(pohzd.p_quantity_ordered,0) *
6099 (nvl(decode(pohzd.p_recovery_rate,null,(100 - nvl(pohzd.d_rec_rate,0)), (100 - nvl(pohzd.p_recovery_rate,0))),0)/100) ,NVL(FC.precision,0)),
6100 ROUND((NVL(pohzd.unit_price, 0))* (nvl(pohzd.tax_rate,0)/100) *
6101 nvl(pohzd.p_quantity_ordered,0) * (nvl(decode(pohzd.p_recovery_rate,null,(100 - nvl(pohzd.d_rec_rate,0)),
6102 (100 - nvl(pohzd.p_recovery_rate,0))),0)/100)/FC.Minimum_Accountable_Unit)* (FC.Minimum_Accountable_Unit)),
6103 'Y',
6104 DECODE(fc.Minimum_Accountable_Unit,null,
6105 (ROUND((NVL(pohzd.unit_price, 0)) * (NVL(pohzd.tax_rate,0)/100) * NVL(pohzd.p_quantity_ordered,0), NVL(FC.precision,0)) -
6106 ROUND((NVL(pohzd.unit_price, 0))* (nvl(pohzd.tax_rate,0)/100) * nvl(pohzd.p_quantity_ordered,0) *
6107 (nvl(decode(pohzd.p_recovery_rate,null,(100 - nvl(pohzd.d_rec_rate,0)), (100 - nvl(pohzd.p_recovery_rate,0))),0)/100) ,NVL(FC.precision,0))),
6108 (ROUND((NVL(pohzd.unit_price, 0)) * (NVL(pohzd.tax_rate,0)/100) * NVL(pohzd.p_quantity_ordered,0)/FC.Minimum_Accountable_Unit)* (FC.Minimum_Accountable_Unit) -
6109 ROUND((NVL(pohzd.unit_price, 0))* (nvl(pohzd.tax_rate,0)/100) *
6110 NVL(pohzd.p_quantity_ordered,0) * (nvl(decode(pohzd.p_recovery_rate,null,(100 - nvl(pohzd.d_rec_rate,0)),
6111 (100 - nvl(pohzd.p_recovery_rate,0))),0)/100)/FC.Minimum_Accountable_Unit)* (FC.Minimum_Accountable_Unit)))
6112 ) REC_NREC_TAX_AMT_TAX_CURR,
6113 -- Start : Code changes for Bug#13955562 --
6114 --
6115 -- Commented the logic to populate RECOVERY_RATE_CODE and RECOVERY_RATE_ID
6116 -- NVL(rates.tax_rate_code,
6117 -- 'AD_HOC_RECOVERY') RECOVERY_RATE_CODE,
6118 -- rates.tax_rate_id RECOVERY_RATE_ID,
6119 --
6120 -- Added new logic to populate RECOVERY_RATE_CODE and RECOVERY_RATE_ID
6121 DECODE(tmp.rec_flag,'N', NULL,
6122 NVL(rates.tax_rate_code,
6123 'AD_HOC_RECOVERY')
6124 ) RECOVERY_RATE_CODE,
6125 DECODE(tmp.rec_flag,'N', NULL,
6126 NVL(rates.tax_rate_id,
6127 (SELECT tax_rate_id
6128 FROM zx_rates_b zrb
6129 WHERE zrb.tax_rate_code = 'AD_HOC_RECOVERY'
6130 AND zrb.tax_regime_code = pohzd.tax_regime_code
6131 AND zrb.tax = pohzd.tax
6132 AND zrb.content_owner_id = pohzd.content_owner_id
6133 AND zrb.rate_type_code = 'RECOVERY'
6134 AND zrb.record_type_code = 'MIGRATED'
6135 AND zrb.active_flag = 'Y')
6136 )
6137 ) RECOVERY_RATE_ID,
6138 -- End : Code changes for Bug#13955562 --
6139 DECODE(tmp.rec_flag,'N', NULL,
6140 NVL(rates.recovery_type_code,
6141 'STANDARD')) RECOVERY_TYPE_CODE,
6142 -- NULL RECOVERY_TYPE_ID,
6143 -- NULL REF_DOC_CURR_CONV_RATE,
6144 -- NULL REF_DOC_DIST_ID,
6145 -- NULL REF_DOC_PER_UNIT_NREC_TAX_AMT,
6146 -- NULL REF_DOC_TAX_DIST_ID,
6147 -- NULL REF_DOC_TRX_LINE_DIST_QTY,
6148 -- NULL REF_DOC_UNIT_PRICE,
6149 -- NULL REF_PER_TRX_CURR_UNIT_NR_AMT,
6150 -- NULL REVERSED_TAX_DIST_ID,
6151 -- NULL ROUNDING_RULE_CODE,
6152 pohzd.p_task_id TASK_ID,
6153 -- null TAXABLE_AMT_FUNCL_CURR,
6154 -- NULL TAXABLE_AMT_TAX_CURR,
6155 -- NULL TRX_LINE_DIST_AMT,
6156 pohzd.p_po_distribution_id TRX_LINE_DIST_ID,
6157 -- NULL TRX_LINE_DIST_QTY,
6158 -- NULL TRX_LINE_DIST_TAX_AMT,
6159 -- NULL UNROUNDED_REC_NREC_TAX_AMT,
6160 -- NULL UNROUNDED_TAXABLE_AMT,
6161 -- NULL TAXABLE_AMT,
6162 pohzd.p_ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,
6163 pohzd.p_ATTRIBUTE1 ATTRIBUTE1,
6164 pohzd.p_ATTRIBUTE2 ATTRIBUTE2,
6165 pohzd.p_ATTRIBUTE3 ATTRIBUTE3,
6166 pohzd.p_ATTRIBUTE4 ATTRIBUTE4,
6167 pohzd.p_ATTRIBUTE5 ATTRIBUTE5,
6168 pohzd.p_ATTRIBUTE6 ATTRIBUTE6,
6169 pohzd.p_ATTRIBUTE7 ATTRIBUTE7,
6170 pohzd.p_ATTRIBUTE8 ATTRIBUTE8,
6171 pohzd.p_ATTRIBUTE9 ATTRIBUTE9,
6172 pohzd.p_ATTRIBUTE10 ATTRIBUTE10,
6173 pohzd.p_ATTRIBUTE11 ATTRIBUTE11,
6174 pohzd.p_ATTRIBUTE12 ATTRIBUTE12,
6175 pohzd.p_ATTRIBUTE13 ATTRIBUTE13,
6176 pohzd.p_ATTRIBUTE14 ATTRIBUTE14,
6177 pohzd.p_ATTRIBUTE15 ATTRIBUTE15,
6178 'Y' HISTORICAL_FLAG,
6179 'N' OVERRIDDEN_FLAG,
6180 'N' SELF_ASSESSED_FLAG,
6181 'Y' TAX_APPORTIONMENT_FLAG,
6182 'N' TAX_ONLY_LINE_FLAG,
6183 'N' INCLUSIVE_FLAG,
6184 'N' MRC_TAX_DIST_FLAG,
6185 'N' REC_TYPE_RULE_FLAG,
6186 'N' NEW_REC_RATE_CODE_FLAG,
6187 tmp.rec_flag RECOVERABLE_FLAG,
6188 'N' REVERSE_FLAG,
6189 'N' REC_RATE_DET_RULE_FLAG,
6190 'Y' BACKWARD_COMPATIBILITY_FLAG,
6191 'N' FREEZE_FLAG,
6192 'N' POSTING_FLAG,
6193 NVL(pohzd.legal_entity_id,-99) LEGAL_ENTITY_ID,
6194 1 CREATED_BY,
6195 SYSDATE CREATION_DATE,
6196 NULL LAST_MANUAL_ENTRY,
6197 SYSDATE LAST_UPDATE_DATE,
6198 1 LAST_UPDATE_LOGIN,
6199 1 LAST_UPDATED_BY,
6200 1 OBJECT_VERSION_NUMBER
6201 FROM (SELECT /*+ use_nl_with_index(recdist ZX_PO_REC_DIST_N1) */
6202 pohzd.*,
6203 recdist.rec_rate d_rec_rate
6204 FROM (SELECT /*+ NO_EXPAND leading(poh) ordered use_nl_with_index(zxl, ZX_LINES_U1) use_nl(pod) */
6205 poh.po_header_id,
6206 poh.set_of_books_id,
6207 poh.last_update_date poh_last_update_date,
6208 zxl.tax_line_id,
6209 zxl.trx_id,
6210 zxl.tax_rate_id,
6211 zxl.content_owner_id,
6212 zxl.CURRENCY_CONVERSION_DATE,
6213 zxl.CURRENCY_CONVERSION_RATE,
6214 zxl.CURRENCY_CONVERSION_TYPE,
6215 zxl.ENTITY_CODE,
6216 zxl.EVENT_CLASS_CODE,
6217 zxl.ledger_id,
6218 zxl.MINIMUM_ACCOUNTABLE_UNIT,
6219 zxl.PRECISION,
6220 zxl.tax,
6221 zxl.TAX_APPORTIONMENT_LINE_NUMBER,
6222 zxl.TAX_CURRENCY_CODE,
6223 zxl.TAX_CURRENCY_CONVERSION_DATE,
6224 zxl.TAX_CURRENCY_CONVERSION_RATE,
6225 zxl.TAX_CURRENCY_CONVERSION_TYPE,
6226 zxl.tax_id,
6227 zxl.tax_line_number,
6228 zxl.tax_rate,
6229 zxl.tax_rate_code,
6230 zxl.tax_regime_code,
6231 zxl.tax_regime_id ,
6232 zxl.tax_status_code,
6233 zxl.tax_status_id,
6234 zxl.trx_currency_code,
6235 zxl.trx_line_id,
6236 zxl.trx_line_number,
6237 zxl.trx_number,
6238 zxl.unit_price,
6239 zxl.legal_entity_id,
6240 pod.po_distribution_id p_po_distribution_id,
6241 pod.expenditure_item_date p_expenditure_item_date,
6242 pod.expenditure_organization_id p_expenditure_organization_id,
6243 pod.expenditure_type p_expenditure_type,
6244 pod.project_id p_project_id,
6245 pod.task_id p_task_id,
6246 pod.recovery_rate p_recovery_rate,
6247 pod.quantity_ordered p_quantity_ordered,
6248 pod.attribute_category p_attribute_category ,
6249 pod.attribute1 p_attribute1,
6250 pod.attribute2 p_attribute2,
6251 pod.attribute3 p_attribute3,
6252 pod.attribute4 p_attribute4,
6253 pod.attribute5 p_attribute5,
6254 pod.attribute6 p_attribute6,
6255 pod.attribute7 p_attribute7,
6256 pod.attribute8 p_attribute8,
6257 pod.attribute9 p_attribute9,
6258 pod.attribute10 p_attribute10,
6259 pod.attribute11 p_attribute11,
6260 pod.attribute12 p_attribute12,
6261 pod.attribute13 p_attribute13,
6262 pod.attribute14 p_attribute14,
6263 pod.attribute15 p_attribute15
6264 FROM (SELECT /*+ NO_EXPAND leading(upd,fsp,poh) ROWID(poh) use_hash(fsp) swap_join_inputs(fsp)
6265 use_hash(upd) swap_join_inputs(upd) use_nl(poll)*/
6266 poh.po_header_id,
6267 fsp.set_of_books_id,
6268 poh.last_update_date,
6269 poll.line_location_id,
6270 poll.po_release_id,
6271 NVL2(poll.po_release_id, 'RELEASE', 'PURCHASE_ORDER') entity_code,
6272 NVL2(poll.po_release_id, 'RELEASE', 'PO_PA') event_class_code,
6273 NVL(poll.po_release_id, poh.po_header_id) trx_id
6274 FROM po_headers_all poh,
6275 financials_system_params_all fsp,
6276 xla_upgrade_dates upd,
6277 po_line_locations_all poll
6278 WHERE poh.rowid BETWEEN p_start_rowid AND p_end_rowid
6279 AND NVL(poh.closed_code, 'X') <> 'FINALLY CLOSED'
6280 AND NVL(poh.org_id, -99) = NVL(fsp.org_id, -99)
6281 AND upd.ledger_id = fsp.set_of_books_id
6282 AND (trunc(poh.last_update_date) between upd.start_date and upd.end_date)
6283 AND poll.po_header_id = poh.po_header_id
6284 ) poh,
6285 zx_lines zxl,
6286 po_distributions_all pod
6287 WHERE zxl.application_id = 201
6288 AND zxl.entity_code = poh.entity_code
6289 AND zxl.event_class_code = poh.event_class_code
6290 AND zxl.trx_id = poh.trx_id
6291 AND zxl.trx_line_id = poh.line_location_id
6292 AND pod.po_header_id = poh.po_header_id
6293 AND pod.line_location_id = poh.line_location_id
6294 ) pohzd,
6295 zx_po_rec_dist recdist
6296 WHERE recdist.po_header_id(+) = pohzd.trx_id
6297 AND recdist.po_line_location_id(+) = pohzd.trx_line_id
6298 AND recdist.po_distribution_id(+) = pohzd.p_po_distribution_id
6299 AND recdist.tax_rate_id(+) = pohzd.tax_rate_id
6300 ) pohzd,
6301 fnd_currencies fc,
6302 zx_rates_b rates,
6303 (SELECT 'Y' rec_flag FROM dual UNION ALL SELECT 'N' rec_flag FROM dual) tmp
6304 WHERE pohzd.trx_currency_code = fc.currency_code(+)
6305 AND rates.tax_regime_code(+) = pohzd.tax_regime_code
6306 AND rates.tax(+) = pohzd.tax
6307 AND rates.content_owner_id(+) = pohzd.content_owner_id
6308 AND rates.rate_type_code(+) = 'RECOVERY'
6309 AND rates.recovery_type_code(+) = 'STANDARD'
6310 AND rates.active_flag(+) = 'Y'
6311 AND rates.effective_from(+) <= sysdate
6312 --Bug#13955562: Added outer join (+) to rates.effective_from and rates.effective_to
6313 AND pohzd.poh_last_update_date BETWEEN rates.effective_from(+)
6314 AND NVL(rates.effective_to(+), pohzd.poh_last_update_date)
6315 AND rates.record_type_code(+) = 'MIGRATED'
6316 AND rates.percentage_rate(+) = NVL(NVL(pohzd.p_recovery_rate, pohzd.d_rec_rate),0)
6317 AND rates.tax_rate_code(+) NOT LIKE 'AD_HOC_RECOVERY%'
6318 AND NOT EXISTS
6319 (SELECT 1 FROM zx_rec_nrec_dist zxdist
6320 WHERE zxdist.APPLICATION_ID = 201
6321 AND zxdist.ENTITY_CODE = pohzd.ENTITY_CODE
6322 AND zxdist.EVENT_CLASS_CODE = pohzd.EVENT_CLASS_CODE
6323 AND zxdist.TRX_ID = pohzd.trx_id );
6324
6325 x_rows_processed := SQL%ROWCOUNT;
6326
6327 IF g_level_procedure >= g_current_runtime_level THEN
6328 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_PO_TRX_MIG','Worker: '||p_worker_id||' x_rows_processed is ' || x_rows_processed );
6329 FND_LOG.STRING(g_level_procedure,'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_PO_TRX_MIG','Worker: '||p_worker_id||' zx_po_trx_mig (-)' );
6330 END IF;
6331
6332 EXCEPTION
6333 WHEN OTHERS THEN
6334 X_retcode := CONC_FAIL;
6335 IF g_level_unexpected >= g_current_runtime_level THEN
6336 FND_LOG.STRING(g_level_unexpected,
6337 'ZX_ON_DEMAND_TRX_UPGRADE_PKG.ZX_PO_TRX_MIG',
6338 'Worker: '||p_worker_id||'Raised exceptions: '||
6339 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80) );
6340 END IF;
6341 raise;
6342
6343 END zx_po_trx_mig;
6344
6345 END ZX_ON_DEMAND_TRX_UPGRADE_PKG;