Performance issue with AP Financial Tax Register Report

(Doc ID 2044094.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Payables - Version 12.1.3 and later
Information in this document applies to any platform.
Performance issue with AP Financial Tax Register Report

Symptoms

AP Financial Tax Register report paused status

Elapsed Time (s) Executions Elapsed Time per Exec (s) %Total %CPU %IO SQL Id SQL Module SQL Text
5,653.39 0 10.62 29.52 71.78 3p3su3qa7rbyb RXZXPTEX BEGIN ZX_C_TAX_EXTRACT.POPULAT...
5,602.31 1 5,602.31 10.53 29.64 71.66 a9y8k1fxbvrrv RXZXPTEX SELECT zx_det.application_id, ...
5,139.57 1 5,139.57 9.66 17.92 83.58 978s51qj8jbx3 RXZXPTEX SELECT /*+ leading(dist, lines...


3p3su3qa7rbyb BEGIN ZX_C_TAX_EXTRACT.POPULATE_TAX(:errbuf, :rc, :A0, :A1, :A2, :A3, :A4, :A5, :A6, :A7, :A8, :A9, :A10, :A11, :A12, :A13, :A14, :A15, :A16, :A17, :A18, :A19, :A20, :A21, :A22, :A23, :A24, :A25, :A26, :A27, :A28, :A29, :A30, :A31, :A32, :A33, :A34, :A35, :A36, :A37, :A38, :A39, :A40, :A41, :A42); END;


a9y8k1fxbvrrv SELECT zx_det.application_id, zx_line.event_class_code , zx_det.internal_organization_id, zx_det.doc_event_status, zx_det.application_doc_status, zx_det.line_class, zx_det.doc_seq_id , zx_det.doc_seq_name , zx_det.doc_seq_value, zx_det.establishment_id, zx_det.batch_source_id, zx_det.currency_conversion_date, zx_det.currency_conversion_rate, zx_det.currency_conversion_type, zx_det.minimum_accountable_unit, zx_det.precision, zx_det.trx_communicated_date , zx_det.trx_currency_code, zx_line.trx_id , zx_det.trx_number , zx_det.trx_date, zx_det.trx_description, zx_det.trx_type_description, zx_rate.description, zx_det.trx_due_date, to_char(null), --zx_det.trx_line_description, to_char(null), --zx_line.trx_line_id, to_char(null), --zx_line.trx_line_number, to_char(null), --zx_line.trx_line_quantity, sum(zx_line.line_amt), to_char(NULL), --zx_det.trx_line_type, to_char(NULL), --zx_det.trx_shipping_date, to_char(NULL), --zx_det.uom_code, to_char(NULL), --zx_ det.related_doc_date, to_char(NULL), --zx_det.related_doc_entity_code, to_char(NULL), --zx_det.related_doc_event_class_code, to_char(NULL), --zx_det.related_doc_number, to_number(NULL), --zx_det.related_doc_trx_id, to_number(NULL), --zx_det.applied_from_application_id, to_char(NULL), --zx_line.applied_from_entity_code, to_char(NULL), --zx_line.applied_from_event_class_code, to_number(NULL), --zx_det.applied_from_line_id, to_number(NULL), --zx_line.applied_from_trx_id, to_char(NULL), --zx_line.applied_from_trx_number, to_number(NULL), --zx_det.applied_to_application_id, to_char(NULL), -- zx_line.applied_to_entity_code, to_char(NULL), --zx_line.applied_to_event_class_code, to_number(NULL), --zx_line.applied_to_trx_id, to_number(NULL), --zx_det.applied_to_trx_line_id, to_char(NULL), --zx_det.applied_to_trx_number, to_number(NULL), --zx_det.adjusted_doc_application_id, to_char(NULL), --zx_det.adjusted_doc_date, to_char(NULL), --zx_det.adjusted_doc_enti ty_code, to_char(NULL), --zx_det.adjusted_doc_event_class_code, to_char(NULL), --ZX_DET.ADJUSTED_DOC_NUMBER, --zx_det.country_of_supply, zx_det.default_taxation_country, TO_CHAR(NULL), --ZX_DET.MERCHANT_PARTY_DOCUMENT_NUMBER, TO_CHAR(NULL), --ZX_DET.MERCHANT_PARTY_NAME, TO_CHAR(NULL), --ZX_DET.MERCHANT_PARTY_REFERENCE, TO_CHAR(NULL), --ZX_DET.MERCHANT_PARTY_TAX_REG_NUMBER, TO_CHAR(NULL), --ZX_DET.MERCHANT_PARTY_TAXPAYER_ID, to_number(NULL), --zx_det.ref_doc_application_id, to_char(NULL), --zx_det.ref_doc_entity_code, to_char(NULL), --zx_det.ref_doc_event_class_code, to_number(NULL), --zx_det.ref_doc_line_id, to_number(NULL), --zx_det.ref_doc_line_quantity, to_number(NULL), --zx_det.ref_doc_trx_id, zx_det.start_expense_date, sum(zx_det.assessable_value), zx_det.document_sub_type, to_char(NULL), --zx_det.line_intended_use, to_char(NULL), --zx_det.product_category, to_char(NULL), --zx_det.product_description, to_char(NULL), --zx_det.product_fisc_clas sification, to_number(NULL), --zx_det.product_id, zx_det.supplier_exchange_rate, zx_det.supplier_tax_invoice_date, zx_det.supplier_tax_invoice_number, zx_det.tax_invoice_date, zx_det.tax_invoice_number, zx_det.trx_business_category, zx_det.user_defined_fisc_class, CASE WHEN ('TAX' = 'TAX' AND 'Y' = 'Y') THEN Decode(zx_dist.recoverable_flag , 'N', 0, sum(NVL(zx_dist.rec_nrec_tax_amt_tax_curr, zx_dist.rec_nrec_tax_amt)) ) ELSE sum(NVL(zx_dist.rec_nrec_tax_amt_tax_curr, zx_dist.rec_nrec_tax_amt)) END, zx_line.OFFSET_TAX_RATE_CODE, CASE WHEN ('TAX' = 'TAX' AND 'Y' = 'Y') THEN Decode(zx_dist.recoverable_flag , 'N', 0, sum(zx_dist.orig_rec_nrec_tax_amt) ) ELSE sum(zx_dist.orig_rec_nrec_tax_amt) END, sum(zx_line.orig_tax_amt), sum(zx_line.orig_tax_amt_tax_curr) , sum(zx_line.orig_taxable_amt), sum(zx_line.orig_taxable_amt_tax_curr), CASE WHEN ('TAX' = 'TAX' AND 'Y' = 'Y') THEN Decode(zx_dist.recoverable_flag , 'N', 0, sum(zx_dist.orig_rec_nrec_tax_amt_tax_curr) ) ELSE sum( zx_dist.orig_rec_nrec_tax_amt_tax_curr) END, TO_CHAR(NULL), --ZX_DIST.RECOVERY_RATE_CODE, TO_CHAR(NULL), --ZX_DIST.RECOVERY_TYPE_CODE, zx_line.tax, CASE WHEN ('TAX' = 'TAX' AND 'Y' = 'Y') THEN Decode(zx_dist.recoverable_flag , 'N', 0, sum(zx_dist.rec_nrec_tax_amt) ) ELSE sum(zx_dist.rec_nrec_tax_amt) END, CASE WHEN ('TAX' = 'TAX' AND 'Y' = 'Y') THEN Decode(zx_dist.recoverable_flag , 'N', 0, sum(NVL(zx_dist.rec_nrec_tax_amt_funcl_curr, zx_dist.rec_nrec_tax_amt)) ) ELSE sum(NVL(zx_dist.rec_nrec_tax_amt_funcl_curr, zx_dist.rec_nrec_tax_amt)) END, sum(zx_line.tax_amt_tax_curr), TO_NUMBER(NULL), --zx_line.tax_apportionment_line_number, zx_line.tax_currency_code, zx_line.tax_date, zx_line.tax_determine_date, zx_line.tax_jurisdiction_code, TO_NUMBER(NULL), --ZX_LINE.TAX_LINE_ID , TO_NUMBER(NULL), --ZX_LINE.TAX_LINE_NUMBER , TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE1 , TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE10, TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_AT TRIBUTE11, TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE12, TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE13, TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE14, TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE15, TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE2, TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE3, TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE4, TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE5, TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE6, TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE7, TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE8, TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE9, TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_CATEGORY, DECODE(zx_line.LAST_MANUAL_ENTRY, 'TAX_RATE', zx_line.TAX_RATE, zx_rate.percentage_rate), zx_line.tax_rate_code, zx_line.tax_rate_id, TO_NUMBER(NULL), --ZX_DIST.REC_NREC_RATE, zx_line.tax_regime_code, zx_line.tax_status_id, zx_line.tax_status_code, sum(zx_dist.taxable_amt)/Count(zx_dist.t rx_id), sum(zx_dist.taxable_amt_funcl_curr)/Count(zx_dist.trx_id), -- zx_det.billing_trading_partner_name, -- zx_det.billing_trading_partner_number, zx_det.bill_from_party_tax_prof_id, zx_det.bill_from_site_tax_prof_id, -- zx_det.billing_tp_taxpayer_id, zx_det.ship_to_site_tax_prof_id, zx_det.ship_from_site_tax_prof_id, TO_NUMBER(NULL), -- zx_det.ship_to_party_tax_prof_id, zx_det.ship_from_party_tax_prof_id, ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID, ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID, ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID, ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID, ZX_DET.SHIP_THIRD_PTY_ACCT_ID, ZX_DET.BILL_THIRD_PTY_ACCT_ID, zx_line.hq_estb_reg_number, zx_line.tax_registration_number, zx_line.legal_entity_tax_reg_number, zx_det.own_hq_site_tax_prof_id, zx_det.own_hq_party_tax_prof_id, zx_det.port_of_entry_code, zx_line.registration_party_type, zx_line.cancel_flag, zx_line.historical_flag, zx_line.mrc_tax_line_flag, zx_line.offset_flag, zx_line.reporting_only_flag, zx _dist.self_assessed_flag, zx_line.tax_amt_included_flag, zx_line.tax_only_line_flag, zx_dist.recoverable_flag, zx_dist.posting_flag, zx_dist.reverse_flag, zx_det.trx_id, zx_dist.trx_line_dist_id, zx_det.entity_code , zx_det.ledger_id, ZX_RATE.VAT_TRANSACTION_TYPE_CODE, zx_tax.tax_type_code, ZX_RATE.TAX_RATE_NAME, zx_det.trx_level_type, to_number(NULL), --zx_det.unit_price , zx_dist.gl_date, to_char(NULL) FROM zx_lines zx_line, zx_lines_det_factors zx_det, zx_rec_nrec_dist zx_dist, zx_taxes_vl zx_tax, zx_rates_vl zx_rate WHERE zx_det.internal_organization_id = zx_line.internal_organization_id AND zx_det.application_id = zx_line.application_id AND zx_det.application_id = 200 AND zx_det.tax_reporting_flag = 'Y' AND zx_det.entity_code = zx_line.entity_code AND zx_det.event_class_code = zx_line.event_class_code AND zx_det.trx_id = zx_line.trx_id AND zx_det.trx_line_id = zx_line.trx_line_id AND zx_line.tax_line_id = zx_dist.tax_line_id AND zx_line.tax_id = zx_tax.tax_id AND zx_line.application_id = 200 AND zx_rate.tax_regime_code = zx_tax.tax_regime_code AND zx_rate.tax = zx_tax.tax AND zx_line.tax_regime_code = zx_tax.tax_regime_code AND zx_line.tax_rate_id = zx_rate.tax_rate_id AND 'TRANSACTION' = 'TRANSACTION' AND (DECODE(:g_trx_date_low, NULL, NULL) IS NULL AND DECODE(:g_trx_date_high, NULL, NULL) IS NULL) AND (DECODE(:g_trx_date_low_ln, NULL, NULL) IS NULL AND DECODE(:g_trx_date_high_ln, NULL, NULL) IS NULL) AND (zx_dist.recoverable_flag = 'Y' OR NOT EXISTS (SELECT 'Non Recoverable' FROM zx_rec_nrec_dist zx_dist1 WHERE zx_dist1.trx_id = zx_dist.trx_id AND zx_dist1.application_id = zx_dist.application_id AND zx_dist1.entity_code = zx_dist.entity_code AND zx_dist1.event_class_code = zx_dist.event_class_code AND zx_dist1.recoverable_flag = 'Y' AND zx_dist1.tax_rate_id = zx_dist.tax_rate_id AND zx_dist1.trx_line_id = zx_dist.trx_line_id AND zx_dist1.trx_line_dist_id = zx_dist.trx_line_dist_id GROUP BY zx_dist1.trx_line_dist_id HAVING sum(zx_dist1 .taxable_amt) <> 0 )) AND ZX_DIST.GL_DATE BETWEEN :g_gl_date_low AND :g_gl_date_high AND DECODE(:G_TRX_NUMBER_LOW, NULL, NULL) IS NULL AND DECODE(:G_TRX_NUMBER_HIGH, NULL, NULL) IS NULL AND DECODE(:G_VAT_TRANSACTION_TYPE_CODE, NULL, NULL) IS NULL AND DECODE(:G_TRX_BUSINESS_CATEGORY, NULL, NULL) IS NULL AND DECODE(:G_TAX_INVOICE_DATE_LOW, NULL, NULL) IS NULL AND DECODE(:G_TAX_INVOICE_DATE_HIGH, NULL, NULL) IS NULL AND DECODE(:g_tax_jurisdiction_code, NULL, NULL) IS NULL AND DECODE(:g_first_party_tax_reg_num, NULL, NULL) IS NULL AND ZX_TAX.TAX_REGIME_CODE = :G_TAX_REGIME_CODE AND DECODE(:G_TAX, NULL, NULL) IS NULL AND DECODE(:G_TAX_STATUS_CODE, NULL, NULL) IS NULL AND DECODE(:G_TAX_RATE_CODE_LOW, NULL, NULL) IS NULL AND DECODE(:G_TAX_RATE_CODE_HIGH, NULL, NULL) IS NULL AND DECODE(:G_TAX_TYPE_CODE_LOW, NULL, NULL) IS NULL AND DECODE(:G_TAX_TYPE_CODE_HIGH, NULL, NULL) IS NULL AND ZX_DET.TRX_CURRENCY_CODE BETWEEN :G_CURRENCY_CODE_LOW AND :G_CURRENCY_CODE_HIGH AND DECODE(:G_PARTY_NAM E, NULL, NULL) IS NULL AND ZX_DET.LINE_CLASS IN ( 'STANDARD INVOICES' , 'PREPAY_APPLICATION' , 'AMOUNT_MATCHED' , 'AP_DEBIT_MEMO' , 'AP_CREDIT_MEMO' , 'PREPAYMENT INVOICES' , 'MIXED' , 'EXPENSE REPORTS') AND DECODE(:G_LEGAL_ENTITY_ID, NULL, NULL) IS NULL AND ZX_LINE.LEDGER_ID = :G_LEDGER_ID AND NVL(ZX_DET.INTERNAL_ORGANIZATION_ID, 526 ) = 526 AND zx_dist.POSTING_FLAG = 'A' AND DECODE(:g_adjusted_doc_from, NULL, NULL) IS NULL AND DECODE(:g_adjusted_doc_to, NULL, NULL) IS NULL GROUP BY zx_det.application_id, zx_line.event_class_code , zx_det.internal_organization_id, zx_det.doc_event_status, zx_det.application_doc_status, zx_det.line_class, zx_det.doc_seq_id, zx_det.doc_seq_name , zx_det.doc_seq_value, zx_det.establishment_id, zx_det.batch_source_id, zx_det.currency_conversion_date, zx_det.currency_conversion_rate, zx_det.currency_conversion_type, zx_det.minimum_accountable_unit, zx_det.precision, zx_det.trx_communicated_date, zx_det.trx_currency_code, zx_line.trx_id, zx_det.trx_number, zx_det.trx_date, zx_det.trx_description, zx_det.trx_type_description, zx_rate.description, zx_det.trx_due_date, to_char(null), --zx_det.trx_line_description, to_char(null), --zx_line.trx_line_id, to_char(null), --zx_line.trx_line_number, to_char(null), --zx_line.trx_line_quantity, zx_det.default_taxation_country, zx_det.start_expense_date, zx_det.document_sub_type, zx_det.supplier_exchange_rate, zx_det.supplier_tax_invoice_date, zx_det.supplier_tax_invoice_number, zx_det.tax_invoice_date, zx_det.tax_invoice_number, zx_det.trx_business_category, zx_det.user_defined_fisc_class, zx_line.offset_tax_rate_code, zx_line.tax, zx_line.tax_currency_code, zx_line.tax_date, zx_line.tax_determine_date, zx_line.tax_jurisdiction_code, decode(zx_line.last_manual_entry, 'TAX_RATE', zx_line.tax_rate, zx_rate.percentage_rate), zx_line.tax_rate_code, zx_line.tax_rate_id, to_number(null), --zx_dist.rec_nrec_rate, zx_line.tax_regime_code, zx_line.tax_statu s_id, zx_line.tax_status_code, zx_det.bill_from_party_tax_prof_id, zx_det.bill_from_site_tax_prof_id, zx_det.ship_to_site_tax_prof_id, zx_det.ship_from_site_tax_prof_id, zx_det.ship_from_party_tax_prof_id , zx_det.ship_third_pty_acct_site_id, zx_det.bill_third_pty_acct_site_id, zx_det.ship_to_cust_acct_site_use_id, zx_det.bill_to_cust_acct_site_use_id, zx_det.ship_third_pty_acct_id, zx_det.bill_third_pty_acct_id, zx_line.hq_estb_reg_number, zx_line.tax_registration_number, zx_line.legal_entity_tax_reg_number, zx_det.own_hq_site_tax_prof_id, zx_det.own_hq_party_tax_prof_id, zx_det.port_of_entry_code, zx_line.registration_party_type, zx_line.cancel_flag, zx_line.historical_flag, zx_line.mrc_tax_line_flag, zx_line.offset_flag, zx_line.reporting_only_flag, zx_dist.self_assessed_flag, zx_line.tax_amt_included_fla


978s51qj8jbx3 SELECT /*+ leading(dist, lines, zx_line, zx_det) parallel(dist) */ ZX_DET.LEDGER_ID, ZX_DET.TRX_ID , ZX_DET.DOC_SEQ_ID , ZX_DET.DOC_SEQ_NAME , ZX_DET.RECEIVABLES_TRX_TYPE_ID, ZX_DET.LINE_CLASS, ZX_DET.EVENT_CLASS_CODE , TO_CHAR(NULL) , TO_NUMBER(NULL) , TO_CHAR(NULL) , TO_CHAR(NULL) , TO_NUMBER(NULL) , TO_CHAR(NULL) , ZX_DET.ADJUSTED_DOC_NUMBER , ZX_DET.BATCH_SOURCE_ID, ZX_LINE.TAX_RATE_ID , DECODE(ZX_ACCOUNTS.INTERIM_TAX_CCID, NULL, 'TAX', 'INTERIM'), TO_NUMBER(NULL), TO_CHAR(NULL), TO_CHAR(NULL), TO_NUMBER(NULL), ZX_LINE.TAX_EXEMPTION_ID, ZX_LINE.TAX_EXCEPTION_ID, TO_NUMBER(NULL), TO_NUMBER(NULL), SUM(DIST.AMOUNT), sum(DIST.ACCTD_AMOUNT) , TO_NUMBER(NULL), SUM(DECODE(ZX_LINE.MANUALLY_ENTERED_FLAG, 'Y', DECODE(ZX_LINE.TAX_AMT_INCLUDED_FLAG, 'N', ZX_LINE.LINE_AMT, ZX_LINE.TAXABLE_AMT) , ZX_LINE.TAXABLE_AMT)*(DIST.PERCENT)/100), SUM(NVL(ZX_LINE.TAXABLE_AMT_FUNCL_CURR, ZX_LINE.TAXABLE_AMT) *(DIST.PERCENT)/100) , TO_NUMBER(NULL), TO_NUMBER(NULL), ZX_DET.EXEMPT_CERTIFI CATE_NUMBER, ZX_LINE.EXEMPT_REASON_CODE , TO_CHAR(NULL), --exception_reason_code TO_CHAR(NULL), TO_CHAR(NULL), ZX_DET.INTERNAL_ORGANIZATION_ID , TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), CASE WHEN (ZX_LINE.LAST_MANUAL_ENTRY='TAX_RATE' AND ZX_LINE.OVERRIDDEN_FLAG = 'Y') OR ZX_LINE.TAX_PROVIDER_ID IS NOT NULL THEN ZX_LINE.TAX_RATE ELSE ZX_RATE.PERCENTAGE_RATE END, ZX_LINE.TAX_RATE_CODE, ZX_RATE.VAT_TRANSACTION_TYPE_CODE, ZX_TAX.TAX_TYPE_CODE, ZX_RATE.TAX_RATE_NAME, ZX_DET.TRX_NUMBER, ZX_DET.TRX_DESCRIPTION, ZX_DET.DOC_SEQ_VALUE, ZX_DET.TRX_DATE, TO_DATE(NULL), --Deposit Date ZX_DET.TRX_DUE_DATE , ZX_DET.TRX_SHIPPING_DATE , ZX_DET.TRX_COMMUNICATED_DATE , ZX_DET.TRX_CURRENCY_CODE, ZX_DET.CURRENCY_CONVERSION_TYPE, ZX_DET.CURRENCY_CONVERSION_DATE, ZX_DET.CURRENCY_CONVERSION_RATE, ZX_DET.TRX_SIC_CODE, ZX_DET.FOB_POINT, ZX_DET.TRX_WAYBILL_NUMBER, ZX_DET.BATCH_SOURCE_NAME, ZX_DET.TRX_TYPE_DESCRIPTION, ZX_RATE.DESCRIPTION, TO_CHAR(NULL), TO_NUMBER(NULL), TO_CHA R(NULL), TO_CHAR(NULL), TO_CHAR(NULL), TO_CHAR(NULL), TO_CHAR(NULL), TO_CHAR(NULL), TO_CHAR(NULL), TO_CHAR(NULL), TO_CHAR(NULL), TO_CHAR(NULL), TO_CHAR(NULL), TO_CHAR(NULL), TO_CHAR(NULL), TO_CHAR(NULL), TO_CHAR(NULL), TO_CHAR(NULL), TO_CHAR(NULL), ZX_DET.APPLICATION_ID, ZX_DET.DOC_EVENT_STATUS, ZX_DET.APPLICATION_DOC_STATUS, ZX_DET.ESTABLISHMENT_ID, 'AR', --ZX_DET.FUNCTIONAL_CURRECNY_CODE, --ZX_DET.LEDGER_NAME, ZX_DET.MINIMUM_ACCOUNTABLE_UNIT, ZX_DET.PRECISION, TO_NUMBER(NULL), ZX_DET.APPLIED_TO_APPLICATION_ID, ZX_DET.APPLIED_TO_TRX_LINE_ID, ZX_DET.APPLIED_TO_ENTITY_CODE, ZX_DET.APPLIED_TO_TRX_ID, ZX_DET.APPLIED_TO_EVENT_CLASS_CODE, ZX_DET.APPLIED_TO_TRX_NUMBER, ZX_DET.RELATED_DOC_APPLICATION_ID, ZX_DET.RELATED_DOC_DATE, ZX_DET.RELATED_DOC_ENTITY_CODE, ZX_DET.RELATED_DOC_EVENT_CLASS_CODE, ZX_DET.RELATED_DOC_NUMBER, ZX_DET.RELATED_DOC_TRX_ID, ZX_DET.ADJUSTED_DOC_APPLICATION_ID, ZX_DET.ADJUSTED_DOC_DATE, --ZX_DET.BANKING_TP_TAXPAYER_ID, ZX_DET.REF_ DOC_APPLICATION_ID, ZX_DET.REF_DOC_ENTITY_CODE, ZX_DET.REF_DOC_EVENT_CLASS_CODE, TO_NUMBER(NULL), TO_NUMBER(NULL), ZX_DET.REF_DOC_TRX_ID, ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE, ZX_DET.DEFAULT_TAXATION_COUNTRY, ZX_DET.DOCUMENT_SUB_TYPE, SUM(ZX_DET.ASSESSABLE_VALUE), ZX_DET.TRX_BUSINESS_CATEGORY, ZX_DET.LINE_INTENDED_USE, ZX_DET.PRODUCT_CATEGORY, ZX_DET.PRODUCT_FISC_CLASSIFICATION, ZX_DET.SUPPLIER_EXCHANGE_RATE, ZX_DET.SUPPLIER_TAX_INVOICE_DATE, ZX_DET.SUPPLIER_TAX_INVOICE_NUMBER, ZX_DET.USER_DEFINED_FISC_CLASS, ZX_LINE.EXCEPTION_RATE, ZX_LINE.EXEMPT_RATE_MODIFIER, SUM(ZX_LINE.ORIG_TAX_AMT), SUM(ZX_LINE.ORIG_TAX_AMT_TAX_CURR) , SUM(ZX_LINE.ORIG_TAXABLE_AMT), SUM(ZX_LINE.ORIG_TAXABLE_AMT_TAX_CURR), TO_CHAR(NULL), SUM(ZX_LINE.TAX_AMT_TAX_CURR) , ZX_LINE.TAX_APPORTIONMENT_LINE_NUMBER, ZX_LINE.TAX_CURRENCY_CODE, ZX_LINE.TAX_DATE, ZX_LINE.TAX_DETERMINE_DATE, ZX_DET.TAX_INVOICE_DATE, ZX_DET.TAX_INVOICE_NUMBER, ZX_LINE.TAX_JURISDICTION_CODE, ZX_LINE.TAX_REGIME_COD E, ZX_LINE.TAX_STATUS_CODE, ZX_LINE.TAX_STATUS_ID, ZX_LINE.TAX, ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID , ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID, ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID , ZX_DET.SHIP_TO_SITE_TAX_PROF_ID , ZX_DET.BILL_TO_PARTY_TAX_PROF_ID, ZX_DET.BILL_TO_SITE_TAX_PROF_ID, ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID, ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID, ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID, ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID, ZX_DET.SHIP_THIRD_PTY_ACCT_ID, ZX_DET.BILL_THIRD_PTY_ACCT_ID, ZX_LINE.HQ_ESTB_REG_NUMBER , zx_line.tax_registration_number, ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER, ZX_DET.OWN_HQ_SITE_TAX_PROF_ID, ZX_DET.OWN_HQ_PARTY_TAX_PROF_ID, ZX_DET.PORT_OF_ENTRY_CODE, ZX_LINE.REGISTRATION_PARTY_TYPE, ZX_LINE.HISTORICAL_FLAG, ZX_LINE.MRC_TAX_LINE_FLAG, DECODE(DIST.GL_POSTED_DATE, NULL, 'N', 'Y') POSTED_FLAG, ZX_LINE.REPORTING_ONLY_FLAG, ZX_LINE.TAX_AMT_INCLUDED_FLAG, ZX_LINE.TAX_ONLY_LINE_FLAG, ZX_DET.EVENT_CLASS_MAPPING_ID, ZX_DET.TRX_LEVEL_TYPE, ZX_LINE.P LACE_OF_SUPPLY_TYPE_CODE, ZX_DET.POA_LOCATION_ID, ZX_DET.POO_LOCATION_ID, ZX_TAX.DEF_PLACE_OF_SUPPLY_TYPE_CODE, SUM(ZX_DET.LINE_AMT), ZX_LINE.TAX_PROVIDER_ID, TO_DATE(NULL), TO_CHAR(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_CHAR(NULL), DIST.ACCOUNT_CLASS, TO_CHAR(NULL), ZX_DET.TRX_ID , ZX_DET.ENTITY_CODE, DIST.GL_POSTED_DATE, DIST.GL_DATE, to_number(NULL), --ZX_DET.UNIT_PRICE, TO_NUMBER(NULL), ZX_LINE.OFFSET_TAX_RATE_CODE, ZX_LINE.OFFSET_FLAG FROM ZX_LINES_DET_FACTORS ZX_DET , ZX_LINES ZX_LINE , ZX_EXEMPTIONS ZX_EX , ZX_TAXES_B ZX_TAX , ZX_ACCOUNTS ZX_ACCOUNTS , ZX_RATES_VL ZX_RATE , RA_CUST_TRX_LINE_GL_DIST_ALL DIST , RA_CUSTOMER_TRX_LINES_ALL LINES WHERE ZX_DET.APPLICATION_ID = 222 AND ZX_DET.TAX_REPORTING_FLAG = 'Y' AND ZX_DET.TRX_LEVEL_TYPE = 'LINE' AND ZX_DET.TAX_EVENT_TYPE_CODE IN ('VALIDATE_FOR_TAX', 'FREEZE_FOR_TAX') AND ZX_LINE.APPLICATION_ID = ZX_DET.APPLICATION_ID AND ZX_LINE.ENTITY_CODE = ZX_DET.ENTITY_CODE AND ZX_LINE.EVENT_CLASS_CODE = ZX_DET.EVENT_ CLASS_CODE AND ZX_LINE.TRX_ID = ZX_DET.TRX_ID AND ZX_LINE.TRX_LINE_ID = ZX_DET.TRX_LINE_ID AND ZX_LINE.INTERNAL_ORGANIZATION_ID = ZX_DET.INTERNAL_ORGANIZATION_ID AND ZX_EX.TAX_EXEMPTION_ID(+) = ZX_LINE.TAX_EXEMPTION_ID AND ZX_TAX.TAX_ID = ZX_LINE.TAX_ID AND ZX_ACCOUNTS.TAX_ACCOUNT_ENTITY_ID(+) = ZX_LINE.TAX_RATE_ID AND ZX_ACCOUNTS.INTERNAL_ORGANIZATION_ID(+) = ZX_LINE.INTERNAL_ORGANIZATION_ID AND ZX_ACCOUNTS.LEDGER_ID(+) = ZX_LINE.LEDGER_ID AND ZX_ACCOUNTS.TAX_ACCOUNT_ENTITY_CODE(+) = 'RATES' AND ZX_RATE.TAX_RATE_ID = ZX_LINE.TAX_RATE_ID AND DIST.CUSTOMER_TRX_ID = ZX_DET.TRX_ID AND DIST.ACCOUNT_CLASS = 'TAX' AND DIST.ACCOUNT_SET_FLAG = 'N' AND LINES.CUSTOMER_TRX_LINE_ID = DIST.CUSTOMER_TRX_LINE_ID AND LINES.TAX_LINE_ID = ZX_LINE.TAX_LINE_ID AND NVL(ZX_DET.INTERNAL_ORGANIZATION_ID, 526 ) = 526 AND ZX_DET.LEDGER_ID = :G_LEDGER_ID AND DECODE(:G_LEGAL_ENTITY_ID, NULL, NULL) IS NULL AND ZX_ACCOUNTS.INTERIM_TAX_CCID IS NULL AND ZX_DET.LINE_CLASS in ('INVOICE', 'CREDIT_MEMO', 'DEBIT_MEMO') A ND DECODE(:G_PARTY_NAME, NULL, NULL) IS NULL AND DECODE(:G_BATCH_NAME, NULL, NULL) IS NULL AND DECODE(:G_TRX_NUMBER_LOW, NULL, NULL) IS NULL AND DECODE(:G_TRX_NUMBER_HIGH, NULL, NULL) IS NULL AND DECODE(:G_TRX_DATE_LOW, NULL, NULL) IS NULL AND DECODE(:G_TRX_DATE_HIGH, NULL, NULL) IS NULL AND DIST.GL_DATE BETWEEN :G_GL_DATE_LOW and :G_GL_DATE_HIGH AND DECODE(:G_DOCUMENT_SUB_TYPE, NULL, NULL) IS NULL AND DECODE(:G_TRX_BUSINESS_CATEGORY, NULL, NULL) IS NULL AND :G_TAX_INVOICE_DATE_LOW IS NULL AND :G_TAX_INVOICE_DATE_HIGH IS NULL AND DECODE(:g_tax_jurisdiction_code, NULL, NULL) IS NULL AND DECODE(:g_first_party_tax_reg_num, NULL, NULL) IS NULL AND ZX_LINE.TAX_REGIME_CODE = :G_TAX_REGIME_CODE AND DECODE(:G_TAX, NULL, NULL) IS NULL AND DECODE(:G_TAX_STATUS_CODE, NULL, NULL) IS NULL AND DECODE(:G_TAX_RATE_CODE_LOW, NULL, NULL) IS NULL AND DECODE(:G_TAX_RATE_CODE_HIGH, NULL, NULL) IS NULL AND DECODE(:G_TAX_TYPE_CODE_LOW, NULL, NULL) IS NULL AND DECODE(:G_TAX_TYPE_CODE_HIGH, NULL, NULL) IS NULL AND ZX_DET.TRX_CURRENCY_CODE = :G_CURRENCY_CODE_LOW AND ZX_DET.TRX_CURRENCY_CODE = :G_CURRENCY_CODE_HIGH AND DECODE(:G_AR_EXEMPTION_STATUS, NULL, NULL) is NULL AND DECODE(:G_VAT_TRANSACTION_TYPE_CODE, NULL, NULL) IS NULL AND decode(:G_VAT_TAX, NULL, NULL) is null AND decode(:G_VAT_ADDITIONAL_TAX, NULL, NULL) is null AND decode(:G_VAT_NON_TAXABLE_TAX, NULL, NULL) is null AND decode(:G_VAT_NOT_TAX, NULL, NULL) is null AND decode(:G_VAT_PERCEPTION_TAX, NULL, NULL) is null AND decode(:G_EXCISE_TAX, NULL, NULL) is null AND DIST.GL_POSTED_DATE IS NOT NULL AND 1 = 1 GROUP BY ZX_DET.LEDGER_ID, ZX_DET.TRX_ID , ZX_DET.DOC_SEQ_ID , ZX_DET.DOC_SEQ_NAME , ZX_DET.RECEIVABLES_TRX_TYPE_ID, ZX_DET.LINE_CLASS, ZX_DET.EVENT_CLASS_CODE , ZX_DET.ADJUSTED_DOC_NUMBER , ZX_DET.BATCH_SOURCE_ID, ZX_LINE.TAX_RATE_ID , DECODE(ZX_ACCOUNTS.INTERIM_TAX_CCID, NULL, 'TAX', 'INTERIM'), --ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE, --ZX_LINE.TAX_RATE_REGISTER_TYPE_CODE, --ZX_DET.PRODUCT_ID, --ZX_DET.PRODUCT_D ESCRIPTION, ZX_LINE.TAX_EXEMPTION_ID, ZX_LINE.TAX_EXCEPTION_ID, --ZX_LINE.TAX_AMT, --NVL(ZX_LINE.TAX_AMT_FUNCL_CURR, ZX_LINE.TAX_AMT) , --ZX_LINE.TAXABLE_AMT, --NVL(ZX_LINE.TAXABLE_AMT_FUNCL_CURR, ZX_LINE.TAXABLE_AMT) , ZX_DET.EXEMPT_CERTIFICATE_NUMBER, ZX_LINE.EXEMPT_REASON_CODE , TO_CHAR(NULL), --exception_reason_code --ZX_DET.UOM_CODE, ZX_DET.INTERNAL_ORGANIZATION_ID , --ORG_ID TO_CHAR(NULL), --reverse_date CASE WHEN (ZX_LINE.LAST_MANUAL_ENTRY='TAX_RATE' AND ZX_LINE.OVERRIDDEN_FLAG = 'Y') OR ZX_LINE.TAX_PROVIDER_ID IS NOT NULL THEN ZX_LINE.TAX_RATE ELSE ZX_RATE.PERCENTAGE_RATE END, ZX_LINE.TAX_RATE_CODE, ZX_RATE.VAT_TRANSACTION_TYPE_CODE, --ZX_RATE.RATE_TYPE_CODE, ZX_TAX.TAX_TYPE_CODE, ZX_RATE.TAX_RATE_NAME, ZX_DET.TRX_NUMBER, ZX_DET.TRX_DESCRIPTION, ZX_DET.DOC_SEQ_VALUE, ZX_DET.TRX_DATE, ZX_DET.TRX_DUE_DATE , ZX_DET.TRX_SHIPPING_DATE , ZX_DET.TRX_COMMUNICATED_DATE , ZX_DET.TRX_CURRENCY_CODE, ZX_DET.CURRENCY_CONVERSION_TYPE, ZX_DET.CURRENCY_CONVERSION_DATE , ZX_DET.CURRENCY_CONVERSION_RATE, ZX_DET.TRX_SIC_CODE, ZX_DET.FOB_POINT, ZX_DET.TRX_WAYBILL_NUMBER, ZX_DET.BATCH_SOURCE_NAME, ZX_DET.TRX_TYPE_DESCRIPTION, ZX_RATE.DESCRIPTION, --ZX_DET.TRX_LINE_DESCRIPTION, ZX_DET.APPLICATION_ID, ZX_DET.DOC_EVENT_STATUS, ZX_DET.APPLICATION_DOC_STATUS, ZX_DET.ESTABLISHMENT_ID, --ZX_DET.FUNCTIONAL_CURRECNY_CODE, --ZX_DET.LEDGER_NAME, ZX_DET.MINIMUM_ACCOUNTABLE_UNIT, ZX_DET.PRECISION, ZX_DET.APPLIED_TO_APPLICATION_ID, ZX_DET.APPLIED_TO_TRX_LINE_ID, ZX_DET.APPLIED_TO_ENTITY_CODE, ZX_DET.APPLIED_TO_TRX_ID, ZX_DET.APPLIED_TO_EVENT_CLASS_CODE, ZX_DET.APPLIED_TO_TRX_NUMBER, ZX_DET.RELATED_DOC_APPLICATION_ID, ZX_DET.RELATED_DOC_DATE, ZX_DET.RELATED_DOC_ENTITY_CODE, ZX_DET.RELATED_DOC_EVENT_CLASS_CODE, ZX_DET.RELATED_DOC_NUMBER, ZX_DET.RELATED_DOC_TRX_ID, ZX_DET.ADJUSTED_DOC_APPLICATION_ID, ZX_DET.ADJUSTED_DOC_DATE, --ZX_DET.BANKING_TP_TAXPAYER_ID, ZX_DET.REF_DOC_APPLICATION_ID, ZX_DET.REF_DOC_ENTITY_CODE, ZX_DET.REF_DOC_EVENT_ CLASS_CODE, ZX_DET.REF_DOC_TRX_ID, ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE, ZX_DET.DEFAULT_TAXATION_COUNTRY, ZX_DET.DOCUMENT_SUB_TYPE, --ZX_DET.ASSESSABLE_VALUE, ZX_DET.TRX_BUSINESS_CATEGORY, ZX_DET.LINE_INTENDED_USE, ZX_DET.PRODUCT_CATEGORY, ZX_DET.PRODUCT_FISC_CLASSIFICATION, ZX_DET.SUPPLIER_EXCHANGE_RATE, ZX_DET.SUPPLIER_TAX_INVOICE_DATE, ZX_DET.SUPPLIER_TAX_INVOICE_NUMBER, ZX_DET.USER_DEFINED_FISC_CLASS, ZX_LINE.EXCEPTION_RATE, ZX_LINE.EXEMPT_RATE_MODIFIER, --ZX_LINE.ORIG_TAX_AMT, --ZX_LINE.ORIG_TAX_AMT_TAX_CURR , --ZX_LINE.ORIG_TAXABLE_AMT, --ZX_LINE.ORIG_TAXABLE_AMT_TAX_CURR, --ZX_LINE.TAX_AMT_TAX_CURR , ZX_LINE.TAX_APPORTIONMENT_LINE_NUMBER, ZX_LINE.TAX_CURRENCY_CODE, ZX_LINE.TAX_DATE, ZX_LINE.TAX_DETERMINE_DATE, ZX_DET.TAX_INVOICE_DATE, ZX_DET.TAX_INVOICE_NUMBER, ZX_LINE.TAX_JURISDICTION_CODE, ZX_LINE.TAX_REGIME_CODE, ZX_LINE.TAX_STATUS_CODE, ZX_LINE.TAX_STATUS_ID, ZX_LINE.TAX, ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID , ZX_DET.SHIP_FROM_SITE_TAX_PROF_I D, ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID , ZX_DET.SHIP_TO_SITE_TAX_PROF_ID , ZX_DET.BILL_TO_PARTY_TAX_PROF_ID, ZX_DET.BILL_TO_SITE_TAX_PROF_ID, ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID, ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID, ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID, ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID, ZX_DET.SHIP_THIRD_PTY_ACCT_ID, ZX_DET.BILL_THIRD_PTY_ACCT_ID, ZX_LINE.HQ_ESTB_REG_NUMBER , zx_line.tax_registration_number, ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER, ZX_DET.OWN_HQ_SITE_TAX_PROF_ID, ZX_DET.OWN_HQ_PARTY_TAX_PROF_ID, ZX_DET.PORT_OF_ENTRY_CODE, ZX_LINE.REGISTRATION_PARTY_TYPE, ZX_LINE.HISTORICAL_FLAG, ZX_LINE.MRC_TAX_LINE_FLAG, DECODE(DIST.GL_POSTED_DATE, NULL, 'N', 'Y'), ZX_LINE.REPORTING_ONLY_FLAG, ZX_LINE.TAX_AMT_INCLUDED_FLAG, ZX_LINE.TAX_ONLY_LINE_FLAG, ZX_DET.EVENT_CLASS_MAPPING_ID, ZX_DET.TRX_LEVEL_TYPE, ZX_LINE.PLACE_OF_SUPPLY_TYPE_CODE, ZX_DET.POA_LOCATION_ID, ZX_DET.POO_LOCATION_ID, ZX_TAX.DEF_PLACE_OF_SUPPLY_TYPE_CODE, --ZX_DET.LINE_AMT, ZX_LINE.TAX_PR OVIDER_ID , ZX_DET.ENTITY_CODE, DIST.GL_POSTED_DATE, DIST.ACCOUNT_CLASS, DIST.GL_DATE, --ZX_DET.UNIT_PRICE, ZX_LINE.OFFSET_TAX_RATE_CODE, ZX_LINE.OFFSET_F

Changes

 NONE

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms