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