Financial Tax Register

Transaction Tax
The register of tax on transactions — recoverable and non-recoverable tax by tax, rate, and jurisdiction, tied to the source invoice or transaction — the report used to prepare and reconcile the tax return.
Run note · Data latency Several tax reports read the Tax Reporting Ledger extract rather than live transaction tables — run the extract first or the report returns stale or empty results.
Sample build of the Financial Tax Register — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.
Sample build · illustrative
Filters
Cconc Ftr Request Id
1003
Cconc Request Id
1003
Cnls No
1003
Cnls Yes
Sample
12,400
Tax lines
$2.10M
Tax amount
$140K
Non-recoverable
Tax	Jurisdiction	Rate	Taxable Amount	Tax Amount	Recoverable	Transaction
Sample	Sample	Sample	$1,240,500.00	$1,240,500.00	Sample	Sample
—	—	—	$842,150.75	$842,150.75	—	—
Sample	Sample	Sample	$96,400.00	$96,400.00	Sample	Sample
—	—	—	$1,005,233.10	$1,005,233.10	—	—
Sample	Sample	Sample	$58,720.40	$58,720.40	Sample	Sample
Sample	Sample	Sample	$1,240,500.00	$1,240,500.00	Sample	Sample
AI Analyst · active
reading
The report reads ZX_LINES and ZX_REC_NREC_DIST tied to the source invoices, splitting recoverable from non-recoverable tax by jurisdiction.
flag
$140K of input tax is non-recoverable, and part of it sits on a tax rate that should be fully recoverable — a tax-determination setup issue, not a real cost.
root cause & next step
Check the recovery rate on that tax rate; a misconfigured recovery rate turns reclaimable input tax into an expense the company eats.
Illustrative data. The live interactive version — drill-through, filters, export, and the AI Analyst — runs on your warehouse. See it live →
This is the report's BI Publisher data model — the SQL data set BI Publisher runs against Oracle tables to produce the output. The same SQL becomes a dbt model in your warehouse, so one definition drives both the formatted report and the analytics layer.
Data sources

ZX_LINES
ZX_REC_NREC_DIST
AP_INVOICES_ALL
AP_INVOICE_LINES_ALL
ZX_RATES_B
How it interconnects: this data set reads the physical tables above. Those same tables surface in OTBI as subject areas and in BICC as PVOs — three lenses on one source. Open any table to trace its subject areas and View Objects.
SQL data set · genericized · parameterized · no hardcoded segments
Show / hide SQL
(SELECT  /*+parallel(12)*/ DET.REQUEST_ID,
   DET.CREATED_BY,
   TO_CHAR(DET.CREATION_DATE,'MM/DD/YYYY"T"HH24:MI:SS') CREATION_DATE,
   DET.LAST_UPDATED_BY,
   TO_CHAR(DET.LAST_UPDATE_DATE,'MM/DD/YYYY"T"HH24:MI:SS') LAST_UPDATE_DATE,
   DET.LAST_UPDATE_LOGIN,
   DET.LEDGER_ID,
   DET.CHART_OF_ACCOUNTS_ID,
   DET.DETAIL_TAX_LINE_ID,
   DET.EXTRACT_SOURCE_LEDGER,
   DET.EXTRACT_REPORT_LINE_NUMBER,
   CON.REP_ENTITY_ID,
   DET.LEGAL_ENTITY_TAX_REG_NUMBER,
   CON.ORG_INFORMATION2,
   CON.REP_CONTEXT_LVL_CODE,
   CON.REP_CONTEXT_LVL_MNG,
   CON.TAXPAYER_ID,
   CON.REP_CONTEXT_ENTITY_NAME,
   CON.REP_CONTEXT_ENTITY_LOCATION_ID,
   CON.REP_CONTEXT_ENTITY_CITY,
   CON.REP_CONTEXT_ENTITY_COUNTY,
   CON.REP_CONTEXT_ENTITY_STATE,
   CON.REP_CONTEXT_ENTITY_PROVINCE,
   CON.REP_CONTEXT_ENTITY_ADDRESS1,
   CON.REP_CONTEXT_ENTITY_ADDRESS2,
   CON.REP_CONTEXT_ENTITY_ADDRESS3,
   CON.REP_CONTEXT_ENTITY_COUNTRY,
   CON.REP_CONTEXT_ENTITY_POSTAL_CODE,
   CON.REP_CONTEXT_ENTITY_TEL_NUMBER,
   DET.LEDGER_NAME,
   DET.FUNCTIONAL_CURRENCY_CODE,
   DET.TRX_ID,
   DET.TRX_NUMBER,
   DET.TRX_DESCRIPTION,
   DET.DOC_SEQ_ID,
   DET.DOC_SEQ_NAME,
   DET.DOC_SEQ_VALUE,
   TO_CHAR(DET.TRX_DATE,'MM/DD/YYYY') TRX_DATE,
   TO_CHAR(DET.TRX_DATE,'DD/MM/YYYY') TRX_DATE_INDIA,
   DET.TRX_LINE_CLASS,
   DET.TRX_CLASS_MNG,
   DET.TRX_TYPE_SEQ_ID,
   DET.TRX_TYPE_MNG,
   DET.APPLIED_FROM_ENTITY_CODE,
   DET.APPLIED_FROM_TRX_ID,
   DET.APPLIED_FROM_EVENT_CLASS_CODE,
   DET.TRX_APPLIED_FROM_CLASS_MNG,
   DET.APPLIED_FROM_TRX_NUMBER,
   DET.ADJUSTED_DOC_ENTITY_CODE,
   DET.ADJUSTED_DOC_TRX_ID,
   DET.ADJUSTED_DOC_EVENT_CLASS_CODE,
   DET.TRX_APPLIED_TO_CLASS_MNG,
   DET.APPLIED_TO_TRX_NUMBER,
   DET.TRX_TYPE_DESCRIPTION,
   TO_CHAR(DET.TRX_DUE_DATE,'MM/DD/YYYY') TRX_DUE_DATE,
   TO_CHAR(DET.TRX_SHIPPING_DATE,'MM/DD/YYYY') TRX_SHIPPING_DATE,
   TO_CHAR(DET.TRX_SHIPPING_DATE,'DD/MM/YYYY') TRX_SHIPPING_DATE_INDIA,
   TO_CHAR(DET.TRX_COMMUNICATED_DATE,'MM/DD/YYYY') TRX_COMMUNICATED_DATE,
   DET.TRX_CURRENCY_CODE,
   DET.CURRENCY_CONVERSION_TYPE,
   TO_CHAR(DET.CURRENCY_CONVERSION_DATE,'MM/DD/YYYY') CURRENCY_CONVERSION_DATE,
   NVL(DET.CURRENCY_CONVERSION_RATE,1) CURRENCY_CONVERSION_RATE ,
   DET.RECONCILIATION_FLAG,
   ACT.BALANCING_SEGMENT_VALUE,
   ACT.TRX_CONTROL_ACCOUNT_FLEXFIELD,
   NVL(NVL(NVL(billing_tp_site_tax_reg_num, DET.shipping_tp_site_tax_reg_num), DET.billing_tp_tax_reg_num),DET.shipping_tp_tax_reg_num)    C_TP_TAX_REG_NUM,
   DET.BILLING_TRADING_PARTNER_ID,
   DET.BILLING_TP_SITE_ID,
   DET.BILLING_TP_NUMBER,
   DET.BILLING_TP_TAX_REG_NUM,
   DET.BILLING_TP_TAXPAYER_ID,
   DET.BILLING_TP_SITE_NAME,
   DET.BILLING_TP_SITE_NAME_ALT,
   DET.BILLING_TP_SITE_TAX_REG_NUM,
   DET.BILLING_TP_TAX_REPORTING_FLAG,
   DET.BILLING_TP_NAME,
   DET.BILLING_TP_NAME_ALT,
   DET.BILLING_TP_SIC_CODE,
   DET.BILLING_TP_ADDRESS_ID,
   DET.BILLING_TP_CITY,
   DET.BILLING_TP_COUNTY,
   DET.BILLING_TP_STATE,
   DET.BILLING_TP_PROVINCE,
   DET.BILLING_TP_ADDRESS1,
   DET.BILLING_TP_ADDRESS2,
   DET.BILLING_TP_ADDRESS3,
   DET.BILLING_TP_ADDRESS_LINES_ALT,
   DET.BILLING_TP_COUNTRY,
   DET.BILLING_TP_POSTAL_CODE,
   DET.SHIPPING_TRADING_PARTNER_ID,
   DET.SHIPPING_TP_SITE_ID,
   DET.SHIPPING_TP_NUMBER,
   NVL(DET.SHIPPING_TP_NUMBER,DET.BILLING_TP_NUMBER) SHIPPING_TP_NUMBER_INDIA,
   DET.SHIPPING_TP_TAX_REG_NUM,
   DET.SHIPPING_TP_TAXPAYER_ID,
   DET.SHIPPING_TP_SITE_NAME,
   DET.SHIPPING_TP_SITE_TAX_REG_NUM,
   NVL(NVL(NVL(DET.shipping_tp_site_tax_reg_num,
                   DET.billing_tp_site_tax_reg_num),
                   DET.shipping_tp_tax_reg_num),
                   DET.billing_tp_tax_reg_num)    SHIPPING_TP_SITE_TAX_REG_NUM_INDIA,
   DET.SHIPPING_TP_NAME,
   NVL(DET.SHIPPING_TP_NAME, DET.BILLING_TP_NAME)  SHIPPING_TP_NAME_INDIA,
   DET.SHIPPING_TP_NAME_ALT,
   DET.SHIPPING_TP_SIC_CODE,
   DET.SHIPPING_TP_ADDRESS_ID,
   DET.SHIPPING_TP_CITY,
   DET.SHIPPING_TP_COUNTY,
   DET.SHIPPING_TP_STATE,
   NVL(DET.SHIPPING_TP_STATE,DET.BILLING_TP_STATE)  SHIPPING_TP_STATE_INDIA,
   DET.SHIPPING_TP_PROVINCE,
   DET.SHIPPING_TP_ADDRESS1,
   DET.SHIPPING_TP_ADDRESS2,
   DET.SHIPPING_TP_ADDRESS3,
   DET.SHIPPING_TP_ADDRESS_LINES_ALT,
   DET.SHIPPING_TP_COUNTRY,
   DET.SHIPPING_TP_POSTAL_CODE,
   DET.BANKING_TP_NAME,
   DET.BANKING_TP_TAXPAYER_ID,
   DET.TRX_BATCH_SOURCE_NAME,
   DET.TRX_SIC_CODE,
   DET.FOB_POINT,
   DET.TRX_WAYBILL_NUMBER,
   DET.POSTED_FLAG,
   DET.TAX_RATE_ID,
   DET.TAX_RATE_CODE,
   DET.TAX_RATE,
   DET.TAX_RATE_VAT_TRX_TYPE_CODE,
   DET.TAX_RATE_CODE_VAT_TRX_TYPE_MNG,
   DET.TAX_RATE_VAT_TRX_TYPE_DESC,
   DET.TAX_RATE_REGISTER_TYPE_CODE,
   DET.TAX_RATE_CODE_REG_TYPE_MNG TAX_RATE_CODE_REG_TYPE_MEANING,
   DET.TAX_TYPE_CODE,
   DET.TAX_TYPE_MNG,
   DET.TAX_RATE_CODE_NAME,
   DET.TAX_RATE_CODE_DESCRIPTION,
   DET.TAX_RECOVERABLE_FLAG TAX_RECOVERABLE_FLAG,
   DET.TAX_RECOVERY_RATE,
   DET.MERCHANT_PARTY_DOCUMENT_NUMBER,
   DET.MERCHANT_PARTY_NAME,
   DET.MERCHANT_PARTY_REFERENCE,
   DET.MERCHANT_PARTY_TAX_REG_NUMBER,
   DET.MERCHANT_PARTY_TAXPAYER_ID,
   DET.COUNTRY_OF_SUPPLY,
   TO_CHAR(DET.START_EXPENSE_DATE,'MM/DD/YYYY') START_EXPENSE_DATE,
   DET.TAX_EXEMPTION_ID,
   DET.TAX_EXCEPTION_ID,
   DET.EXEMPT_CERTIFICATE_NUMBER,
   DET.EXEMPT_REASON_CODE,
   DET.TAX_EXEMPT_REASON_MNG,
   DET.TAX_EXCEPTION_REASON_CODE,
   DET.TAX_EXCEPTION_REASON_MNG,
   DET.TAX_LINE_USER_CATEGORY,
   DET.TAX_LINE_USER_ATTRIBUTE1,
   DET.TAX_LINE_USER_ATTRIBUTE2,
   DET.TAX_LINE_USER_ATTRIBUTE3,
   DET.TAX_LINE_USER_ATTRIBUTE4,
   DET.TAX_LINE_USER_ATTRIBUTE5,
   DET.TAX_LINE_USER_ATTRIBUTE6,
   DET.TAX_LINE_USER_ATTRIBUTE7,
   DET.TAX_LINE_USER_ATTRIBUTE8,
   DET.TAX_LINE_USER_ATTRIBUTE9,
   DET.TAX_LINE_USER_ATTRIBUTE10,
   DET.TAX_LINE_USER_ATTRIBUTE11,
   DET.TAX_LINE_USER_ATTRIBUTE12,
   DET.TAX_LINE_USER_ATTRIBUTE13,
   DET.TAX_LINE_USER_ATTRIBUTE14,
   DET.TAX_LINE_USER_ATTRIBUTE15,
   NVL(DET.TAX_AMT, 0) TAX_AMT, 
   NVL(DET.TAX_AMT_FUNCL_CURR, 0) TAX_AMT_FUNCL_CURR, 
   DET.TAX_LINE_NUMBER TAX_LINE_NUMBER, 
   NVL(DET.TAXABLE_AMT,0) LINE_AMOUNT, 
    NVL(DET.TAXABLE_AMT, 0) TAXABLE_AMT, 
   NVL(DET.TAXABLE_AMT_FUNCL_CURR,0)   			TAXABLE_AMT_FUNCL_CURR,
   NVL(DET.TAXABLE_AMT_FUNCL_CURR, 0)  TAXABLE_ACC_AMOUNT, 
   NVL(DET.TAX_AMT, 0) + NVL(DET.TAXABLE_AMT,0) TOTAL_ENTERED_AMOUNT, 
   --NVL(DET.TAX_AMT_FUNCL_CURR, 0) + NVL((DET.TRX_LINE_AMT * (nvl(DET.CURRENCY_CONVERSION_RATE,1))), 0) TOTAL_ACCOUNTED_AMOUNT,
   NVL(DET.TAX_AMT_FUNCL_CURR, 0) + NVL(DET.TAXABLE_AMT_FUNCL_CURR,0) TOTAL_ACCOUNTED_AMOUNT,
   NVL(DET.TAXABLE_DISC_AMT, 0) TAXABLE_DISC_AMT, 
   NVL(DET.TAXABLE_DISC_AMT_FUNCL_CURR, 0) TAXABLE_DISC_AMT_FUNCL_CURR, 
   NVL(DET.TAX_DISC_AMT, 0) TAX_DISC_AMT, 
   NVL(DET.TAX_DISC_AMT_FUNCL_CURR, 0) TAX_DISC_AMT_FUNCL_CURR,
   DET.TRX_LINE_NUMBER,
   DET.TAXABLE_LINE_SOURCE_TABLE,
   DET.TRX_LINE_DESCRIPTION,
   DET.PRODUCT_ID,
   DET.TAXABLE_LINE_ITEM_FLEXFIELD,
   DET.PRODUCT_DESCRIPTION,
   DET.TRX_LINE_QUANTITY,
   DET.UOM_CODE,
   DET.TRX_QUANTITY_UOM_MNG,
   DET.TRX_LINE_TYPE,
   ACT.ACTG_EVENT_ID,
   ACT.ACTG_EVENT_TYPE_CODE,
   ACT.ACTG_EVENT_TYPE_MNG,
   ACT.ACTG_EVENT_NUMBER,
   ACT.ACTG_EVENT_STATUS_FLAG,
   ACT.ACTG_EVENT_STATUS_MNG,
   ACT.ACTG_SOURCE_TABLE,
   ACT.ACTG_SOURCE_ID,
   ACT.ACTG_HEADER_ID,
   ACT.ACTG_CATEGORY_CODE,
   ACT.ACTG_CATEGORY_MNG,
   ACT.PERIOD_NAME,
   ACT.GL_TRANSFER_FLAG,
   ACT.GL_TRANSFER_RUN_ID,
   ACT.ACTG_HEADER_DESCRIPTION,
   ACT.ACTG_LINE_NUM,
   ACT.ACTG_LINE_TYPE_CODE,
   ACT.ACTG_LINE_TYPE_MNG,
   ACT.ACTG_LINE_CCID,
   ACT.ACCOUNT_FLEXFIELD,
   ACT.ACCOUNT_DESCRIPTION,
   ACT.ACTG_LINE_DESCRIPTION,
   ACT.ACTG_STAT_AMT,
   ACT.ACTG_ERROR_CODE,
   ACT.GL_TRANSFER_CODE,
   ACT.ACTG_DOC_SEQUENCE_ID,
   ACT.ACTG_DOC_SEQUENCE_NAME,
   ACT.ACTG_DOC_SEQUENCE_VALUE,
   ACT.ACTG_PARTY_ID,
   ACT.ACTG_PARTY_SITE_ID,
   ACT.TRX_ARAP_BALANCING_SEGMENT,
   ACT.TRX_ARAP_NATURAL_ACCOUNT,
   ACT.TRX_TAXABLE_BALANCING_SEGMENT,
   ACT.TRX_TAXABLE_NATURAL_ACCOUNT,
   ACT.TRX_TAX_BALANCING_SEGMENT,
   ACT.TRX_TAX_NATURAL_ACCOUNT,
   ACT.TRX_TAXABLE_ACCOUNT,
   ACT.TRX_TAXABLE_ACCOUNT_DESC,
   ACT.TRX_TAXABLE_BALSEG_DESC,
   ACT.TRX_TAXABLE_NATACCT_SEG_DESC,
   EXT.NUMERIC1,
   EXT.NUMERIC2,
   EXT.NUMERIC3,
   EXT.NUMERIC4,
   EXT.NUMERIC5,
   EXT.NUMERIC6,
   EXT.NUMERIC7,
   EXT.NUMERIC8,
   EXT.NUMERIC9,
   EXT.NUMERIC10,
   EXT.NUMERIC11,
   EXT.NUMERIC12,
   EXT.NUMERIC13,
   EXT.NUMERIC14,
   EXT.NUMERIC15,
   EXT.NUMERIC16,
   EXT.ATTRIBUTE1,
   EXT.ATTRIBUTE2,
   EXT.ATTRIBUTE3,
   EXT.ATTRIBUTE4,
   EXT.ATTRIBUTE5,
   EXT.ATTRIBUTE6,
   EXT.ATTRIBUTE7,
   EXT.ATTRIBUTE8,
   EXT.ATTRIBUTE9,
   EXT.ATTRIBUTE10,
   EXT.ATTRIBUTE11,
   EXT.ATTRIBUTE12,
   EXT.ATTRIBUTE13,
   EXT.ATTRIBUTE14,
   EXT.ATTRIBUTE15,
   EXT.ATTRIBUTE16,
   EXT.ATTRIBUTE17,
   EXT.ATTRIBUTE18,
   EXT.ATTRIBUTE2 || ' ' || EXT.ATTRIBUTE3 || ' ' || EXT.ATTRIBUTE4 || ' ' || EXT.ATTRIBUTE8 || ' ' || EXT.ATTRIBUTE9 BUSINESS_LOC_ADDRESS,
   EXT.DOCUMENT_SUB_TYPE_MNG,
   EXT.TAX_STATUS_MNG,
   EXT.TRX_BUSINESS_CATEGORY_MNG,
   EXT.GDF_AP_INVOICES_ATT2,
   EXT.GDF_AP_INVOICES_ATT3,
   EXT.GDF_AP_INVOICES_ATT4,
   EXT.GDF_AP_INVOICES_ATT11,
   EXT.GDF_AP_INVOICES_ATT12,
   EXT.GDF_AP_INVOICES_ATT13,
   EXT.GDF_AP_INVOICE_DIST_ATT1,
   EXT.GDF_PO_VENDOR_SITES_ATT17,
   EXT.GDF_RA_CUST_BILL_ATT10,
   EXT.GDF_RA_CUST_BILL_ATT12,
   EXT.GDF_PARTY_SITES_BILL_ATT8,
   EXT.GDF_PARTY_SITES_BILL_ATT9,
   EXT.GDF_RA_CUST_TRX_ATT3,
   EXT.GDF_RA_CUST_TRX_ATT4,
   EXT.GDF_RA_CUST_TRX_ATT5,
   EXT.GDF_RA_CUST_TRX_ATT6,
   EXT.GDF_RA_CUST_TRX_ATT7,
   EXT.GDF_RA_CUST_TRX_ATT8,
   EXT.GDF_RA_CUST_TRX_ATT9,
   EXT.GDF_RA_CUST_TRX_ATT19,
   EXT.GDF_RA_CUST_TRX_LINES_ATT1,
   EXT.GDF_RA_CUST_TRX_LINES_ATT2,
   EXT.GDF_RA_CUST_TRX_TYPES_ATT5,
   EXT.GDF_RA_CUST_TRX_TYPES_ATT6,
   EXT.GDF_RA_CUST_TRX_TYPES_ATT7,
   EXT.GDF_RA_BATCH_SOURCES_ATT1,
   EXT.GDF_RA_BATCH_SOURCES_ATT2,
   EXT.GDF_RA_BATCH_SOURCES_ATT3,
   EXT.GDF_RA_BATCH_SOURCES_ATT4,
   EXT.GDF_RA_BATCH_SOURCES_ATT7,
   EXT.GDF_RA_ADDRESSES_BILL_ATT9,
   EXT.GDF_RA_ADDRESSES_SHIP_ATT9,
   DET.ADJUSTED_DOC_NUMBER,
   DET.TAX_POINT_BASIS,
   TO_CHAR(DET.TAX_POINT_DATE,'MM/DD/YYYY')TAX_POINT_DATE,
   DET.TAX_RATE_EFFECTIVE_FROM,
   DET.TAX_RATE_EFFECTIVE_TO,
   DET.THIRD_PTY_REG_ID,
   DET.THIRD_PTY_REG_NUMBER,
   DET.PAYMENT_METHOD_CODE,
   DET.COUNTRY_OF_PAYMENT,
   DET.PRODUCT_TYPE,
   DET.DETAIL_TAX_LINE_ID  C_DETAIL_TAX_LINE_ID,
   TO_CHAR(DET.GL_DATE,'MM/DD/YYYY') GL_DATE,
   TO_CHAR(DET.GL_DATE,'DD/MM/YYYY') GL_DATE_INDIA,
   TO_CHAR(DET.GL_DATE,'MM-YY') PERIOD_NAME_1,
   DET.SELF_ASSESSED_FLAG,
   DET.SHIP_TO_LOCATION_ID,
   HL.LOCATION_CODE SHIP_TO_LOCATION_CODE,
   HL.DESCRIPTION SHIP_TO_LOCATION_DESCRIPTION,
   HL.ADDRESS_LINE_1 SHIP_TO_ADDRESS_LINE_1,
   HL.ADDRESS_LINE_2 SHIP_TO_ADDRESS_LINE_2,
   HL.ADDRESS_LINE_3 SHIP_TO_ADDRESS_LINE_3,
   HL.ADDRESS_LINE_4 SHIP_TO_ADDRESS_LINE_4,
   HL.COUNTRY SHIP_TO_COUNTRY,
   HL.POSTAL_CODE SHIP_TO_POSTAL_CODE,
   HL.REGION_1 SHIP_TO_REGION_1,
   HL.REGION_2 SHIP_TO_REGION_2,
   HL.REGION_3 SHIP_TO_REGION_3,
   HL.TOWN_OR_CITY SHIP_TO_TOWN_OR_CITY,
   decode(DET.LEGAL_REPORTING_STATUS, '000000000000000', :CP_NLS_NO, '111111111111111', :CP_NLS_YES, NULL) FINALLY_REPORTED,
   HG.geography_element1_code         C_LEVEL_1,
   ZX_REP_FIN_TAXREGISTER.cf_jurisdiction_levels(     DET.TRX_ID,      DET.TRX_LINE_ID,      DET.TAX_LINE_ID,      :CP_CONC_REQUEST_ID,    decode(HG.geography_element4, null,   decode(HG.geography_element3, null,   decode(HG.geography_element2, null       , 1 , 2), 3), 4))            C_LEVEL_1_JURIS,
   nvl(HG.geography_element2_code,       ZX_REP_FIN_TAXREGISTER.CP_LEVEL_2_VAL)       C_LEVEL_2,
   ZX_REP_FIN_TAXREGISTER.CP_JURIS_2_VAL            C_LEVEL_2_JURIS,
   nvl(HG.geography_element3,       ZX_REP_FIN_TAXREGISTER.CP_LEVEL_3_VAL)       C_LEVEL_3,
   ZX_REP_FIN_TAXREGISTER.CP_JURIS_3_VAL            C_LEVEL_3_JURIS,
   nvl(HG.geography_element4,       ZX_REP_FIN_TAXREGISTER.CP_LEVEL_4_VAL)       C_LEVEL_4,
   ZX_REP_FIN_TAXREGISTER.CP_JURIS_4_VAL            C_LEVEL_4_JURIS,
SUBSTR(hg.geography_element1_code,1,2)||'-'||Decode(hg.geography_element2_code,NULL,'',SUBSTR(hg.geography_element2_code,1,2)||'-')||DECODE(hg.geography_element3,null,'',SUBSTR(hg.geography_element3,1,30)||'-')||DECODE(hg.geography_element4,null,'',SUBSTR(hg.geography_element4,1,30)) C_TAX_AUTHORITY,
   P.PARTY_NAME                       C_TAX_AUTHORITY_DISP,
   decode(HG.geography_element4, null,   decode(HG.geography_element3, null,   decode(HG.geography_element2, null, 1 , 2), 3), 4)             C_LEVEL,
   to_char(DET.TRX_DATE,'MM/DD/YYYY') C_TRX_DATE,
   NVL(BILLING_TP_NAME, SHIPPING_TP_NAME)  C_CUST_NAME,
   DET.TAX_JURISDICTION_CODE          C_TAX_JURISDICTION_CODE,
   DET.TRX_LINE_DESCRIPTION        C_TRX_LINE_DESC,
   (DET.TRX_LINE_AMT * (nvl(DET.CURRENCY_CONVERSION_RATE,1))) C_LINE_FUN_AMT,
   nvl(DET.TAX_AMT_FUNCL_CURR ,det.tax_amt)  C_TAX_ACCOUNTED_AMOUNT,
-- Summary requirements (from Tax Register)
ZX_REP_FIN_TAXREGISTER.cf_ex_taxable_acc_amtformula(
  NVL (DET.HISTORICAL_FLAG , 'N'), 
  DET.TRX_ID, 
  DET.trx_line_id, 
  ACT.ACTG_LINE_CCID, 
  nvl ( DET.TAXABLE_AMT_FUNCL_CURR , det.taxable_amt ), 
 DET.TRX_LINE_AMT ,
  DET.TAX_AMT, 
  nvl ( DET.TAX_AMT_FUNCL_CURR , det.tax_amt ), 
  DET.TAXABLE_AMT, 
  DET.EVENT_CLASS_CODE, 
  DET.TRX_LINE_CLASS, 
  DET.TAX_RATE_ID, 
  ( DET.TRX_LINE_AMT * ( nvl ( DET.CURRENCY_CONVERSION_RATE , 1 ) ) ), 
  tax_exemption_id, 
  DET.EXEMPT_CERTIFICATE_NUMBER, 
  DET.EXEMPT_REASON_CODE, 
  DET.TAX_LINE_ID, 
  DET.TAX_ONLY_FLAG,
  DET.TAX_EXCEPTION_ID,
  DET.TAX_EXCEPTION_REASON_CODE) CF_TAXABLE_ACC_AMT,
ZX_REP_FIN_TAXREGISTER.cf_ex_taxable_amtformula(     DET.TRX_ID, DET.trx_line_id, DET.TAX_RATE_ID,      tax_exemption_id, DET.EXEMPT_CERTIFICATE_NUMBER,      DET.EXEMPT_REASON_CODE, DET.TAXABLE_AMT,      DET.TRX_CURRENCY_CODE,     DET.TAX_EXCEPTION_ID,     DET.TAX_EXCEPTION_REASON_CODE      ) CF_TAXABLE_AMT,
ZX_REP_FIN_TAXREGISTER.CP_LINE_FUN_AMT_p CP_LINE_FUN_AMT,
ZX_REP_FIN_TAXREGISTER.CP_LINE_AMOUNT_p CP_LINE_AMOUNT,
ZX_REP_FIN_TAXREGISTER.CP_exempt_acct_amt_p CP_exempt_acct_amt,
ZX_REP_FIN_TAXREGISTER.CP_exempt_amt_p CP_exempt_amt,
ZX_REP_FIN_TAXREGISTER.CP_exception_acct_amt_p CP_exception_acct_amt,
ZX_REP_FIN_TAXREGISTER.CP_exception_amt_p CP_exception_amt,
(SELECT   to_char(rtrim( xmlserialize( content extract( xmlagg( xmlelement("e",  REPORTING_TYPE_CODE ||',') ) , '//text()' ) ) , '|' ) )||'|'||
 to_char(rtrim( xmlserialize( content extract( xmlagg( xmlelement("e",  ENTITY_CODE ||',') ) , '//text()' ) ) , '|' ) )||'|'||
 to_char(rtrim( xmlserialize( content extract( xmlagg( xmlelement("e",  REPORTING_CODE_VALUE ||',') ) , '//text()' ) ) , '|' ) )
                                                FROM (SELECT  
--  RPT.ENTITY_CODE ENTITY_CODE,
  RPT.REPORTING_TYPE_CODE  ,RPT.ENTITY_CODE ,RPT.REPORTING_CODE_VALUE
--  RPT.REPORTING_CODE_VALUE  C_REPORTING_CODE_VALUE 
FROM
  fusion.ZX_REP_TRX_RPT_CODE_ASSOC_V RPT
WHERE 
 RPT.DETAIL_TAX_LINE_ID = DET.DETAIL_TAX_LINE_ID
 AND RPT.REQUEST_ID = DET.REQUEST_ID
 AND &LP_REP_CODE
 ))  C_REPORTING_TYPE_CODE ,
(SELECT ACA.check_date
FROM AP_INVOICES_ALL AIA,
     AP_INVOICE_LINES_ALL AP, 
     AP_INVOICE_PAYMENTS_ALL AIPA,
     AP_CHECKS_ALL ACA,
	 ZX_LINES ZL
WHERE 1=1	 
AND AIPA.INVOICE_ID = AIA.INVOICE_ID
AND AIPA.CHECK_ID = ACA.CHECK_ID
AND ACA.VOID_DATE IS NULL
AND AIA.INVOICE_ID = AP.INVOICE_ID
AND ZL.TRX_ID = AP.INVOICE_ID
AND ZL.TRX_LINE_ID = AP.LINE_NUMBER
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID
AND AIA.INVOICE_ID =DET.TRX_ID) INV_PMT_DATE,
DET.TAX_TYPE_CODE	TAX_CODE,
DET.CURRENCY_CONVERSION_RATE TRANSACTION_EXRATE,
NVL(DET.TAX_AMT_TAX_CURR,0) REPORTED_TAX_AMOUNT,
NVL(DET.TAXABLE_AMT_TAX_CURR,0) REPORTED_TAXABLE_AMOUNT,
NVL((Select ZL.REC_NREC_TAX_AMT_FUNCL_CURR 
FROM ZX_REC_NREC_DIST ZL
WHERE  ZL.TAX_LINE_ID = DET.TAX_LINE_ID
AND ZL.REC_NREC_TAX_DIST_ID=DET.REC_NREC_TAX_DIST_ID 
AND ZL.RECOVERABLE_FLAG=DET.TAX_RECOVERABLE_FLAG),0)	REC_TAX_AMT_1,
xle.NAME COMPANY_NAME ,
(Select ZL.TAX_REGIME_CODE 
FROM ZX_LINES ZL
WHERE  ZL.TAX_LINE_ID = DET.TAX_LINE_ID) TAX_REGIME_CODE_1,
(Select ZL.TAX 
FROM ZX_LINES ZL
WHERE  ZL.TAX_LINE_ID = DET.TAX_LINE_ID) TAX_TYPE_CODE_1,
(Select ZFCV.CLASSIFICATION_NAME 
FROM AP_INVOICE_LINES_ALL AP,
     ZX_LINES ZL,
	 ZX_FC_CODES_VL ZFCV
WHERE 1=1
AND ZL.TRX_ID = AP.INVOICE_ID
AND ZL.TRX_LINE_ID = AP.LINE_NUMBER
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID 
AND ZL.ENTITY_CODE = 'AP_INVOICES'
AND AP.PRODUCT_CATEGORY = ZFCV.CLASSIFICATION_CODE
AND ZFCV.CLASSIFICATION_TYPE_CODE = 'PRODUCT_CATEGORY'
AND AP.INVOICE_ID =DET.TRX_ID )  PRODUCT_TYPE_1,
(Select ZL.TAX_RATE 
FROM ZX_LINES ZL,
     ZX_RATES_B ZRB
WHERE  ZL.TAX_RATE_ID = ZRB.TAX_RATE_ID
--AND ZL.ORIG_TAX_RATE_ID = ZRB.ORIG_TAX_RATE_ID
AND ZRB.TAX = 'COUNTRY CGST'
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID) CGST,
(Select ZL.TAX_RATE 
FROM ZX_LINES ZL,
     ZX_RATES_B ZRB
WHERE  ZL.TAX_RATE_ID = ZRB.TAX_RATE_ID
--AND ZL.ORIG_TAX_RATE_ID = ZRB.ORIG_TAX_RATE_ID
AND ZRB.TAX = 'STATE SGST'
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID) SGST,
(Select ZL.TAX_RATE 
FROM ZX_LINES ZL,
     ZX_RATES_B ZRB
WHERE ZL.TAX_RATE_ID = ZRB.TAX_RATE_ID
--AND ZL.ORIG_TAX_RATE_ID = ZRB.ORIG_TAX_RATE_ID
AND ZRB.TAX = 'COUNTRY IGST'
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID) IGST,
TO_CHAR((SELECT ACA.check_date
FROM AP_INVOICES_ALL AIA,
     AP_INVOICE_LINES_ALL AP, 
     AP_INVOICE_PAYMENTS_ALL AIPA,
     AP_CHECKS_ALL ACA,
	 ZX_LINES ZL
WHERE 1=1	 
AND AIPA.INVOICE_ID = AIA.INVOICE_ID
AND AIPA.CHECK_ID = ACA.CHECK_ID
AND ACA.VOID_DATE IS NULL
AND AIA.INVOICE_ID = AP.INVOICE_ID
AND ZL.TRX_ID = AP.INVOICE_ID
AND ZL.TRX_LINE_ID = AP.LINE_NUMBER
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID
AND AIA.INVOICE_ID =DET.TRX_ID),'DD/MM/YYYY') PAYMENT_DATE_INDIA,
(SELECT ZFCV.CLASSIFICATION_NAME 
FROM 
     RA_CUSTOMER_TRX_LINES_ALL RCTLA,
	 RA_CUSTOMER_TRX_ALL RCTA,
     ZX_LINES ZL,
	 ZX_FC_CODES_VL ZFCV
WHERE 1=1
AND RCTLA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
AND ZL.TRX_ID = RCTLA.CUSTOMER_TRX_ID 
AND DET.TRX_ID = RCTLA.CUSTOMER_TRX_ID 
AND ZL.TRX_LINE_ID = RCTLA.CUSTOMER_TRX_LINE_ID
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID 
AND RCTLA.PRODUCT_CATEGORY = ZFCV.CLASSIFICATION_CODE
AND ZFCV.CLASSIFICATION_TYPE_CODE = 'PRODUCT_CATEGORY'
--AND RCTA.TRX_NUMBER  = '101003' 
) PRODUCT_TYPE_REC ,
(SELECT EXTENDED_AMOUNT
FROM 
     RA_CUSTOMER_TRX_LINES_ALL RCTLA,
     ZX_LINES ZL
WHERE 1=1
AND ZL.TRX_ID = RCTLA.CUSTOMER_TRX_ID 
AND DET.TRX_ID = RCTLA.CUSTOMER_TRX_ID 
AND ZL.TRX_LINE_ID  = RCTLA.CUSTOMER_TRX_LINE_ID
AND RCTLA.LINE_TYPE='TAX'
AND ZL.TAX_LINE_ID=DET.TAX_LINE_ID) TOTAL_TAX_AMOUNT,
(SELECT  RCTA.TRX_NUMBER
FROM 
AR_RECEIVABLE_APPLICATIONS_ALL APP,
AR_PAYMENT_SCHEDULES_ALL     PS_CM,
RA_CUSTOMER_TRX_ALL RCTA
WHERE APP.APPLIED_CUSTOMER_TRX_ID = DET.TRX_ID --'300000010743311'
AND APP.PAYMENT_SCHEDULE_ID = PS_CM.PAYMENT_SCHEDULE_ID
AND PS_CM.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID 
AND APP.APPLICATION_TYPE  = 'CM' -- 16001 - CREDIT MEMO
GROUP BY RCTA.TRX_NUMBER) CREDIT_TRX_NUMBER,
(SELECT  TO_CHAR(RCTA.TRX_DATE,'DD/MM/YYYY')
FROM 
AR_RECEIVABLE_APPLICATIONS_ALL APP,
AR_PAYMENT_SCHEDULES_ALL     PS_CM,
RA_CUSTOMER_TRX_ALL RCTA
WHERE APP.APPLIED_CUSTOMER_TRX_ID = DET.TRX_ID --'300000010743311'
AND APP.PAYMENT_SCHEDULE_ID = PS_CM.PAYMENT_SCHEDULE_ID
AND PS_CM.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID 
AND APP.APPLICATION_TYPE  = 'CM' -- 16001 - CREDIT MEMO
GROUP BY RCTA.TRX_DATE) CREDIT_TRX_DATE_INDIA,
(SELECT  RCTA.TRX_NUMBER
FROM 
AR_RECEIVABLE_APPLICATIONS_ALL APP,
AR_PAYMENT_SCHEDULES_ALL     PS_CM,
RA_CUSTOMER_TRX_ALL RCTA
WHERE APP.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
AND  APP.PAYMENT_SCHEDULE_ID = PS_CM.PAYMENT_SCHEDULE_ID
AND PS_CM.CUSTOMER_TRX_ID = DET.TRX_ID --'300000010743329'
AND APP.APPLICATION_TYPE  = 'CM' -- 16000 - ORGINAL INVOICE
GROUP BY RCTA.TRX_NUMBER) ORIGINAL_INV_NUM,
(SELECT  TO_CHAR(RCTA.TRX_DATE,'DD/MM/YYYY')
FROM 
AR_RECEIVABLE_APPLICATIONS_ALL APP,
AR_PAYMENT_SCHEDULES_ALL     PS_CM,
RA_CUSTOMER_TRX_ALL RCTA
WHERE APP.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
AND  APP.PAYMENT_SCHEDULE_ID = PS_CM.PAYMENT_SCHEDULE_ID
AND PS_CM.CUSTOMER_TRX_ID = DET.TRX_ID --'300000010743329'
GROUP BY RCTA.TRX_DATE) ORIGINAL_TRX_DATE_INDIA,
(Select SUBSTR(AP.ATTRIBUTE2,1,INSTR(AP.ATTRIBUTE2,'|')-1) 
FROM AP_INVOICE_LINES_ALL AP,
     ZX_LINES ZL
WHERE 1=1
AND ZL.TRX_ID = AP.INVOICE_ID
AND ZL.TRX_LINE_ID = AP.LINE_NUMBER
AND ZL.TAX_LINE_ID= DET.TAX_LINE_ID  
AND ZL.ENTITY_CODE = 'AP_INVOICES'
AND AP.INVOICE_ID =DET.TRX_ID) PO_NUMBER,
(Select AP.ATTRIBUTE5
FROM AP_INVOICE_LINES_ALL AP,
     ZX_LINES ZL
WHERE 1=1
AND ZL.TRX_ID = AP.INVOICE_ID
AND ZL.TRX_LINE_ID = AP.LINE_NUMBER
AND ZL.TAX_LINE_ID= DET.TAX_LINE_ID 
AND ZL.ENTITY_CODE = 'AP_INVOICES'
AND AP.INVOICE_ID =DET.TRX_ID) HSN_CODE,
(SELECT RCTA.WAYBILL_NUMBER 
FROM RA_CUSTOMER_TRX_ALL RCTA, 
RA_CUSTOMER_TRX_LINES_ALL RCTLA,
ZX_LINES ZL
WHERE 1=1
AND RCTLA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
AND RCTA.CUSTOMER_TRX_ID = DET.TRX_ID
AND ZL.TRX_ID  = RCTLA.CUSTOMER_TRX_ID 
AND ZL.TRX_LINE_ID  = RCTLA.CUSTOMER_TRX_LINE_ID
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID 
--RCTA.TRX_NUMBER  = '92003'
) SHIPPING_REFERENCE, 
(SELECT GCC.CONCATENATED_SEGMENTS 
FROM AP_INVOICES_ALL AIA,
     AP_INVOICE_LINES_ALL AP, 
	 AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
	 GL_CODE_COMBINATIONS GCC,
	 ZX_LINES ZL
WHERE 1=1	 
AND AIA.INVOICE_ID = AP.INVOICE_ID
AND AIA.INVOICE_ID = AIDA.INVOICE_ID
AND AIDA.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND ZL.TRX_ID = AP.INVOICE_ID
AND ZL.TRX_LINE_ID = AP.LINE_NUMBER
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID
AND ROWNUM = 1
--AND INVOICE_NUM = 'VKM UT1'
) GL_CODE,
(SELECT AIA.DOC_CATEGORY_CODE
FROM AP_INVOICES_ALL AIA,
     AP_INVOICE_LINES_ALL AP, 
	 ZX_LINES ZL
WHERE 1=1	 
AND AIA.INVOICE_ID = AP.INVOICE_ID
AND ZL.TRX_ID = AP.INVOICE_ID
AND ZL.TRX_LINE_ID = AP.LINE_NUMBER
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID
AND AIA.INVOICE_ID =DET.TRX_ID) DOC_CAT_INDIA,
(Select DISTINCT ZL.APPLICATION_ID 
FROM ZX_LINES ZL
WHERE ZL.TAX_LINE_ID = DET.TAX_LINE_ID) APP_ID,
--(DET.TRX_LINE_AMT * (nvl(DET.CURRENCY_CONVERSION_RATE,1)))	TAXABLE_ACC_AMOUNT,
ROUND(CASE WHEN (DET.TRX_LINE_AMT * (nvl(DET.CURRENCY_CONVERSION_RATE,1))) <>0 THEN (NVL(DET.TAXABLE_AMT_FUNCL_CURR,0)/(CASE WHEN NVL(DET.TAX_AMT_INCLUDED_FLAG,'N')='Y' THEN NVL(DET.TAXABLE_AMT_FUNCL_CURR,0) ELSE (DET.TRX_LINE_AMT * (nvl(DET.CURRENCY_CONVERSION_RATE,1))) END))*100 ELSE 0 END) TAX_REC_RATE,
NVL(DET.REC_TAX_AMT_TAX_CURR, 0)	REC_TAX_AMT_TAX_CURR,
--DET.TRX_LINE_AMT AS LINE_AMOUNT,
(SELECT DECODE(AIA.APPROVAL_STATUS, 'APPROVED', 'Validated', 
									'NEVER APPROVED', 'Not Validated', 
									'NEEDS REAPPROVAL', 'Needs revalidation',
									'UNPAID','Unpaid', 
									'UNAPPROVED','Never Validated', 
									'FULL','Fully Applied', 
									'CANCELLED','Cancelled',
									'AVAILABLE','Available',
									'PERMANENT','Permanent Prepayment',AIA.APPROVAL_STATUS)
FROM AP_INVOICES_ALL AIA,
     AP_INVOICE_LINES_ALL AP, 
	 ZX_LINES ZL
WHERE 1=1	 
AND AIA.INVOICE_ID = AP.INVOICE_ID
AND ZL.TRX_ID = AP.INVOICE_ID
AND ZL.TRX_LINE_ID = AP.LINE_NUMBER
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID
AND AIA.INVOICE_ID =DET.TRX_ID)	AP_INV_STATUS,
(SELECT TL.NAME
FROM AP_INVOICES_ALL AIA,
     AP_INVOICE_LINES_ALL AP, 
	 AP_TERMS_TL TL,
	 ZX_LINES ZL
WHERE 1=1	 
AND AIA.INVOICE_ID = AP.INVOICE_ID
AND TL.TERM_ID		=AIA.TERMS_ID	
AND TL.LANGUAGE='US'
AND ZL.TRX_ID = AP.INVOICE_ID
AND ZL.TRX_LINE_ID = AP.LINE_NUMBER
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID
AND AIA.INVOICE_ID =DET.TRX_ID)	PAY_TERMS,
(SELECT ZR.REGISTRATION_NUMBER
FROM AP_INVOICES_ALL AIA,
	 ZX_REGISTRATIONS ZR,
     AP_INVOICE_LINES_ALL AP, 
	 ZX_LINES ZL
WHERE 1=1	 
AND AIA.INVOICE_ID = AP.INVOICE_ID
AND AIA.FIRST_PARTY_REGISTRATION_ID=ZR.REGISTRATION_ID
AND ZL.TRX_ID = AP.INVOICE_ID
AND ZL.TRX_LINE_ID = AP.LINE_NUMBER
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID
AND AIA.INVOICE_ID =DET.TRX_ID)	FIRST_TAX_REG
 

FROM      
    ZX_REP_TRX_JX_EXT_T   EXT,
    ZX_REP_CONTEXT_T CON,
    ZX_REP_TRX_DETAIL_T   DET,
    ZX_REP_ACTG_EXT_T     ACT,
    HZ_GEOGRAPHIES HG,
    ZX_JURISDICTIONS_B JUR,
    ZX_PARTY_TAX_PROFILE PP,
    HZ_PARTIES P,
    HR_LOCATIONS HL	,
	XLE_ENTITY_PROFILES XLE
WHERE DET.REQUEST_ID =:CP_CONC_FTR_REQUEST_ID -- :CP_CONC_REQUEST_ID
AND DET.REQUEST_ID = CON.REQUEST_ID
AND NOT Exists ( select lookup_code 
     From fnd_lookups
   Where lookup_type = 'JE_ENABLE_FEATURE'  
     and lookup_code = 'JE_FTR_TRIM'
     and nvl(enabled_flag,'N') = 'Y'
    and (trunc(sysdate) BETWEEN nvl(start_date_active, trunc(sysdate)) AND nvl(end_date_active, trunc(sysdate ))) )
AND NVL(DET.REP_CONTEXT_ID ,CON.REP_CONTEXT_ID) = CON.REP_CONTEXT_ID
and  ACT.DETAIL_TAX_LINE_ID(+) = DET.DETAIL_TAX_LINE_ID 
AND EXT.DETAIL_TAX_LINE_ID(+) = DET.DETAIL_TAX_LINE_ID
AND DET.SHIP_TO_LOCATION_ID = HL.LOCATION_ID(+)
AND DET.TAX_JURISDICTION_CODE = JUR.TAX_JURISDICTION_CODE(+)
AND JUR.ZONE_GEOGRAPHY_ID = HG.GEOGRAPHY_ID(+)
AND DET.TAX_REGIME_CODE = JUR.TAX_REGIME_CODE(+)
AND DET.TAX = JUR.TAX(+) 
AND JUR.REP_TAX_AUTHORITY_ID = PP.PARTY_TAX_PROFILE_ID(+)
AND PP.PARTY_ID = P.PARTY_ID(+)
AND XLE.LEGAL_ENTITY_ID = DET.LEGAL_ENTITY_ID
AND (CASE WHEN DET.TAX_RECOVERABLE_FLAG='N' AND NVL(DET.TAX_RECOVERY_RATE,0)=0 THEN 'N' ELSE 'Y' END)='Y'
AND NVL(DET.REVERSE_FLAG,'N') !='Y'
AND &LP_REP_COND
--ORDER BY 
--DET.TRX_NUMBER,
--DET.TRX_LINE_NUMBER,
--DET.TAX_LINE_NUMBER,
--c_tax_jurisdiction_code
)
Union
(SELECT  /*+parallel(12)*/   DET.REQUEST_ID,
   Null  CREATED_BY,
   Null CREATION_DATE,
   Null LAST_UPDATED_BY,
   Null  LAST_UPDATE_DATE,
   Null LAST_UPDATE_LOGIN,
   DET.LEDGER_ID,
   DET.CHART_OF_ACCOUNTS_ID,
   Null  DETAIL_TAX_LINE_ID,
   DET.EXTRACT_SOURCE_LEDGER,
   DET.EXTRACT_REPORT_LINE_NUMBER,
   CON.REP_ENTITY_ID,
   DET.LEGAL_ENTITY_TAX_REG_NUMBER,
   Null ORG_INFORMATION2,
   Null REP_CONTEXT_LVL_CODE,
   Null REP_CONTEXT_LVL_MNG,
   Null TAXPAYER_ID,
   Null REP_CONTEXT_ENTITY_NAME,
   Null  REP_CONTEXT_ENTITY_LOCATION_ID,
   Null REP_CONTEXT_ENTITY_CITY,
   Null REP_CONTEXT_ENTITY_COUNTY,
   Null REP_CONTEXT_ENTITY_STATE,
   Null REP_CONTEXT_ENTITY_PROVINCE,
   Null REP_CONTEXT_ENTITY_ADDRESS1,
   Null REP_CONTEXT_ENTITY_ADDRESS2,
   Null REP_CONTEXT_ENTITY_ADDRESS3,
   Null REP_CONTEXT_ENTITY_COUNTRY,
   Null REP_CONTEXT_ENTITY_POSTAL_CODE,
   Null REP_CONTEXT_ENTITY_TEL_NUMBER,
   DET.LEDGER_NAME,
   DET.FUNCTIONAL_CURRENCY_CODE,
   DET.TRX_ID,
   DET.TRX_NUMBER,
   DET.TRX_DESCRIPTION,
   DET.DOC_SEQ_ID,
   DET.DOC_SEQ_NAME,
   DET.DOC_SEQ_VALUE,
   TO_CHAR(DET.TRX_DATE,'MM/DD/YYYY') TRX_DATE,
   TO_CHAR(DET.TRX_DATE,'DD/MM/YYYY') TRX_DATE_INDIA,
   DET.TRX_LINE_CLASS,
   DET.TRX_CLASS_MNG,
   DET.TRX_TYPE_SEQ_ID,
   DET.TRX_TYPE_MNG,
   Null APPLIED_FROM_ENTITY_CODE,
   Null APPLIED_FROM_TRX_ID,
   Null APPLIED_FROM_EVENT_CLASS_CODE,
   Null TRX_APPLIED_FROM_CLASS_MNG,
   Null APPLIED_FROM_TRX_NUMBER,
   Null ADJUSTED_DOC_ENTITY_CODE,
   Null ADJUSTED_DOC_TRX_ID,
   Null ADJUSTED_DOC_EVENT_CLASS_CODE,
   Null TRX_APPLIED_TO_CLASS_MNG,
   Null APPLIED_TO_TRX_NUMBER,
   DET.TRX_TYPE_DESCRIPTION,
   TO_CHAR(DET.TRX_DUE_DATE,'MM/DD/YYYY') TRX_DUE_DATE,
   TO_CHAR(DET.TRX_SHIPPING_DATE,'MM/DD/YYYY') TRX_SHIPPING_DATE,
   TO_CHAR(DET.TRX_SHIPPING_DATE,'DD/MM/YYYY') TRX_SHIPPING_DATE_INDIA,
   Null  TRX_COMMUNICATED_DATE,
   DET.TRX_CURRENCY_CODE,
   DET.CURRENCY_CONVERSION_TYPE,
   Null CURRENCY_CONVERSION_DATE,
   NVL(Null,1) CURRENCY_CONVERSION_RATE,
   Null RECONCILIATION_FLAG,
   ACT.BALANCING_SEGMENT_VALUE,
   ACT.TRX_CONTROL_ACCOUNT_FLEXFIELD,
   NVL(NVL(NVL(billing_tp_site_tax_reg_num,
                   DET.shipping_tp_site_tax_reg_num),
                   DET.billing_tp_tax_reg_num),
                   DET.shipping_tp_tax_reg_num)    C_TP_TAX_REG_NUM,
  Null BILLING_TRADING_PARTNER_ID,
  Null BILLING_TP_SITE_ID,
  Null BILLING_TP_NUMBER,
  Null BILLING_TP_TAX_REG_NUM,
  Null BILLING_TP_TAXPAYER_ID,
  BILLING_TP_SITE_NAME,
  Null BILLING_TP_SITE_NAME_ALT,
  Null BILLING_TP_SITE_TAX_REG_NUM,
  Null BILLING_TP_TAX_REPORTING_FLAG,
  BILLING_TP_NAME,
  Null BILLING_TP_NAME_ALT,
  Null   BILLING_TP_SIC_CODE,
   Null BILLING_TP_ADDRESS_ID,
  Null BILLING_TP_CITY,
  Null BILLING_TP_COUNTY,
  Null BILLING_TP_STATE,
  Null BILLING_TP_PROVINCE,
  Null BILLING_TP_ADDRESS1,
  Null BILLING_TP_ADDRESS2,
  Null BILLING_TP_ADDRESS3,
  Null BILLING_TP_ADDRESS_LINES_ALT,
  Null BILLING_TP_COUNTRY,
  Null BILLING_TP_POSTAL_CODE,
  Null SHIPPING_TRADING_PARTNER_ID,
  Null SHIPPING_TP_SITE_ID,
  Null SHIPPING_TP_NUMBER,
  Null SHIPPING_TP_NUMBER_INDIA,
  Null SHIPPING_TP_TAX_REG_NUM,
  Null SHIPPING_TP_TAXPAYER_ID,
  Null SHIPPING_TP_SITE_NAME,
  Null SHIPPING_TP_SITE_TAX_REG_NUM,
  NULL SHIPPING_TP_SITE_TAX_REG_NUM_INDIA,
  Null SHIPPING_TP_NAME,
  NULL SHIPPING_TP_NAME_INDIA,
  Null SHIPPING_TP_NAME_ALT,
  Null SHIPPING_TP_SIC_CODE,
  Null SHIPPING_TP_ADDRESS_ID,
  Null SHIPPING_TP_CITY,
  Null SHIPPING_TP_COUNTY,
  Null SHIPPING_TP_STATE,
  Null SHIPPING_TP_STATE_INDIA,
  Null SHIPPING_TP_PROVINCE,
  Null SHIPPING_TP_ADDRESS1,
  Null SHIPPING_TP_ADDRESS2,
  Null SHIPPING_TP_ADDRESS3,
  Null SHIPPING_TP_ADDRESS_LINES_ALT,
  Null SHIPPING_TP_COUNTRY,
  Null SHIPPING_TP_POSTAL_CODE,
  Null BANKING_TP_NAME,
  Null BANKING_TP_TAXPAYER_ID,
  Null TRX_BATCH_SOURCE_NAME,
  Null TRX_SIC_CODE,
  Null FOB_POINT,
  Null TRX_WAYBILL_NUMBER,
  Null POSTED_FLAG,
   Null TAX_RATE_ID,
   DET.TAX_RATE_CODE,
   DET.TAX_RATE,
   DET.TAX_RATE_VAT_TRX_TYPE_CODE,
   DET.TAX_RATE_CODE_VAT_TRX_TYPE_MNG,
  Null TAX_RATE_VAT_TRX_TYPE_DESC,
   Null TAX_RATE_REGISTER_TYPE_CODE,
   Null  TAX_RATE_CODE_REG_TYPE_MEANING,
   Null TAX_TYPE_CODE,
   Null TAX_TYPE_MNG,
   Null TAX_RATE_CODE_NAME,
   Null TAX_RATE_CODE_DESCRIPTION,
  Null TAX_RECOVERABLE_FLAG,
   Null TAX_RECOVERY_RATE,
   Null MERCHANT_PARTY_DOCUMENT_NUMBER,
   Null MERCHANT_PARTY_NAME,
   Null MERCHANT_PARTY_REFERENCE,
   Null MERCHANT_PARTY_TAX_REG_NUMBER,
   Null MERCHANT_PARTY_TAXPAYER_ID,
   Null COUNTRY_OF_SUPPLY,
   Null  START_EXPENSE_DATE,
   Null TAX_EXEMPTION_ID,
   Null TAX_EXCEPTION_ID,
   Null EXEMPT_CERTIFICATE_NUMBER,
     DET.EXEMPT_REASON_CODE,
     DET.TAX_EXEMPT_REASON_MNG,
     DET.TAX_EXCEPTION_REASON_CODE,
     DET.TAX_EXCEPTION_REASON_MNG,
   Null TAX_LINE_USER_CATEGORY,
   Null TAX_LINE_USER_ATTRIBUTE1,
   Null TAX_LINE_USER_ATTRIBUTE2,
   Null TAX_LINE_USER_ATTRIBUTE3,
   Null TAX_LINE_USER_ATTRIBUTE4,
   Null TAX_LINE_USER_ATTRIBUTE5,
   Null TAX_LINE_USER_ATTRIBUTE6,
   Null TAX_LINE_USER_ATTRIBUTE7,
   Null TAX_LINE_USER_ATTRIBUTE8,
   Null TAX_LINE_USER_ATTRIBUTE9,
   Null TAX_LINE_USER_ATTRIBUTE10,
   Null TAX_LINE_USER_ATTRIBUTE11,
   Null TAX_LINE_USER_ATTRIBUTE12,
   Null TAX_LINE_USER_ATTRIBUTE13,
   Null TAX_LINE_USER_ATTRIBUTE14,
   Null TAX_LINE_USER_ATTRIBUTE15,
   NVL(DET.TAX_AMT, 0) TAX_AMT, 
   NVL(DET.TAX_AMT_FUNCL_CURR, 0) TAX_AMT_FUNCL_CURR, 
   DET.TAX_LINE_NUMBER TAX_LINE_NUMBER, 
   NVL(DET.TAXABLE_AMT,0) LINE_AMOUNT, 
   NVL(DET.TAXABLE_AMT, 0) TAXABLE_AMT, 
   NVL(DET.TAXABLE_AMT_FUNCL_CURR,0)   			TAXABLE_AMT_FUNCL_CURR,
   NVL(DET.TAXABLE_AMT_FUNCL_CURR,0)   			TAXABLE_ACC_AMOUNT,
   NVL(DET.TAX_AMT,0) + NVL(DET.TAXABLE_AMT,0) 	TOTAL_ENTERED_AMOUNT, 
   --NVL(DET.TAX_AMT_FUNCL_CURR, 0) + NVL((DET.TRX_LINE_AMT * (nvl(DET.CURRENCY_CONVERSION_RATE,1))), 0) TOTAL_ACCOUNTED_AMOUNT,
    NVL(DET.TAX_AMT_FUNCL_CURR, 0) + NVL(DET.TAXABLE_AMT_FUNCL_CURR,0) TOTAL_ACCOUNTED_AMOUNT,
   NVL(DET.TAXABLE_DISC_AMT, 0) TAXABLE_DISC_AMT, 
   NVL(DET.TAXABLE_DISC_AMT_FUNCL_CURR, 0) TAXABLE_DISC_AMT_FUNCL_CURR, 
   NVL(DET.TAX_DISC_AMT, 0) TAX_DISC_AMT, 
   NVL(DET.TAX_DISC_AMT_FUNCL_CURR, 0) TAX_DISC_AMT_FUNCL_CURR,
  DET.TRX_LINE_NUMBER,
   DET.TAXABLE_LINE_SOURCE_TABLE,
   DET.TRX_LINE_DESCRIPTION,
   Null PRODUCT_ID,
   DET.TAXABLE_LINE_ITEM_FLEXFIELD,
   DET.PRODUCT_DESCRIPTION,
   Null TRX_LINE_QUANTITY,
   Null UOM_CODE,
   Null TRX_QUANTITY_UOM_MNG,
   Null TRX_LINE_TYPE,
   Null ACTG_EVENT_ID,
   Null ACTG_EVENT_TYPE_CODE,
   Null ACTG_EVENT_TYPE_MNG,
   Null ACTG_EVENT_NUMBER,
   Null ACTG_EVENT_STATUS_FLAG,
   Null ACTG_EVENT_STATUS_MNG,
   Null ACTG_SOURCE_TABLE,
   Null ACTG_SOURCE_ID,
  Null ACTG_HEADER_ID,
   Null ACTG_CATEGORY_CODE,
   Null ACTG_CATEGORY_MNG,
   ACT.PERIOD_NAME,
   ACT.GL_TRANSFER_FLAG,
   Null GL_TRANSFER_RUN_ID,
   ACT.ACTG_HEADER_DESCRIPTION,
   Null ACTG_LINE_NUM,
   Null ACTG_LINE_TYPE_CODE,
   Null ACTG_LINE_TYPE_MNG,
   ACT.ACTG_LINE_CCID,
   ACT.ACCOUNT_FLEXFIELD,
   ACT.ACCOUNT_DESCRIPTION,
   ACT.ACTG_LINE_DESCRIPTION,
   ACT.ACTG_STAT_AMT,
   ACT.ACTG_ERROR_CODE,
   ACT.GL_TRANSFER_CODE,
   Null ACTG_DOC_SEQUENCE_ID,
   Null ACTG_DOC_SEQUENCE_NAME,
   Null ACTG_DOC_SEQUENCE_VALUE,
   Null ACTG_PARTY_ID,
   Null ACTG_PARTY_SITE_ID,
   ACT.TRX_ARAP_BALANCING_SEGMENT,
   ACT.TRX_ARAP_NATURAL_ACCOUNT,
   ACT.TRX_TAXABLE_BALANCING_SEGMENT,
   ACT.TRX_TAXABLE_NATURAL_ACCOUNT,
   ACT.TRX_TAX_BALANCING_SEGMENT,
   ACT.TRX_TAX_NATURAL_ACCOUNT,
   ACT.TRX_TAXABLE_ACCOUNT,
   ACT.TRX_TAXABLE_ACCOUNT_DESC,
   ACT.TRX_TAXABLE_BALSEG_DESC,
   ACT.TRX_TAXABLE_NATACCT_SEG_DESC,
   Null NUMERIC1,
   Null NUMERIC2,
   Null NUMERIC3,
   Null NUMERIC4,
   Null NUMERIC5,
   Null NUMERIC6,
   Null NUMERIC7,
   Null NUMERIC8,
   Null NUMERIC9,
   Null NUMERIC10,
   Null NUMERIC11,
   Null NUMERIC12,
   Null NUMERIC13,
   Null NUMERIC14,
   Null NUMERIC15,
   Null NUMERIC16,
   Null ATTRIBUTE1,
   Null ATTRIBUTE2,
   Null ATTRIBUTE3,
   Null ATTRIBUTE4,
   Null ATTRIBUTE5,
   Null ATTRIBUTE6,
   Null ATTRIBUTE7,
   Null ATTRIBUTE8,
   Null ATTRIBUTE9,
   Null ATTRIBUTE10,
   Null ATTRIBUTE11,
   Null ATTRIBUTE12,
   Null ATTRIBUTE13,
   Null ATTRIBUTE14,
   Null ATTRIBUTE15,
   Null ATTRIBUTE16,
   Null ATTRIBUTE17,
   Null ATTRIBUTE18,
   Null  BUSINESS_LOC_ADDRESS,
   Null DOCUMENT_SUB_TYPE_MNG,
   Null TAX_STATUS_MNG,
   Null TRX_BUSINESS_CATEGORY_MNG,
   Null GDF_AP_INVOICES_ATT2,
   Null GDF_AP_INVOICES_ATT3,
   Null GDF_AP_INVOICES_ATT4,
   Null GDF_AP_INVOICES_ATT11,
   Null GDF_AP_INVOICES_ATT12,
   Null GDF_AP_INVOICES_ATT13,
   Null GDF_AP_INVOICE_DIST_ATT1,
   Null GDF_PO_VENDOR_SITES_ATT17,
   Null GDF_RA_CUST_BILL_ATT10,
   Null GDF_RA_CUST_BILL_ATT12,
   Null GDF_PARTY_SITES_BILL_ATT8,
   Null GDF_PARTY_SITES_BILL_ATT9,
   Null GDF_RA_CUST_TRX_ATT3,
   Null GDF_RA_CUST_TRX_ATT4,
   Null GDF_RA_CUST_TRX_ATT5,
   Null GDF_RA_CUST_TRX_ATT6,
   Null GDF_RA_CUST_TRX_ATT7,
   Null GDF_RA_CUST_TRX_ATT8,
   Null GDF_RA_CUST_TRX_ATT9,
   Null GDF_RA_CUST_TRX_ATT19,
   Null GDF_RA_CUST_TRX_LINES_ATT1,
   Null GDF_RA_CUST_TRX_LINES_ATT2,
   Null GDF_RA_CUST_TRX_TYPES_ATT5,
   Null GDF_RA_CUST_TRX_TYPES_ATT6,
   Null GDF_RA_CUST_TRX_TYPES_ATT7,
   Null GDF_RA_BATCH_SOURCES_ATT1,
   Null GDF_RA_BATCH_SOURCES_ATT2,
   Null GDF_RA_BATCH_SOURCES_ATT3,
   Null GDF_RA_BATCH_SOURCES_ATT4,
   Null GDF_RA_BATCH_SOURCES_ATT7,
   Null GDF_RA_ADDRESSES_BILL_ATT9,
   Null GDF_RA_ADDRESSES_SHIP_ATT9,
   DET.ADJUSTED_DOC_NUMBER,
   DET.TAX_POINT_BASIS,
   TO_CHAR(DET.TAX_POINT_DATE,'MM/DD/YYYY')TAX_POINT_DATE,
   Null TAX_RATE_EFFECTIVE_FROM,
   Null TAX_RATE_EFFECTIVE_TO,
   Null THIRD_PTY_REG_ID,
   DET.THIRD_PTY_REG_NUMBER,
   Null PAYMENT_METHOD_CODE,
   Null COUNTRY_OF_PAYMENT,
   Null PRODUCT_TYPE,
   Null  C_DETAIL_TAX_LINE_ID,
   TO_CHAR(DET.GL_DATE,'MM/DD/YYYY') GL_DATE,
   TO_CHAR(DET.GL_DATE,'DD/MM/YYYY') GL_DATE_INDIA,
   TO_CHAR(DET.GL_DATE,'MM-YY') PERIOD_NAME_1,
  Null SELF_ASSESSED_FLAG,
   Null SHIP_TO_LOCATION_ID,
   Null  SHIP_TO_LOCATION_CODE,
   Null  SHIP_TO_LOCATION_DESCRIPTION,
   Null  SHIP_TO_ADDRESS_LINE_1,
   Null  SHIP_TO_ADDRESS_LINE_2,
   Null  SHIP_TO_ADDRESS_LINE_3,
   Null  SHIP_TO_ADDRESS_LINE_4,
   Null  SHIP_TO_COUNTRY,
   Null  SHIP_TO_POSTAL_CODE,
   Null  SHIP_TO_REGION_1,
   Null  SHIP_TO_REGION_2,
   Null  SHIP_TO_REGION_3,
   Null  SHIP_TO_TOWN_OR_CITY,
   decode(DET.LEGAL_REPORTING_STATUS, '000000000000000', :CP_NLS_NO, '111111111111111', :CP_NLS_YES, NULL) FINALLY_REPORTED,
   Null         C_LEVEL_1,
   Null C_LEVEL_1_JURIS,
   Null C_LEVEL_2,
   Null C_LEVEL_2_JURIS,
   Null C_LEVEL_3,
   Null C_LEVEL_3_JURIS,
   null C_LEVEL_4,
   null C_LEVEL_4_JURIS,
   Null C_TAX_AUTHORITY,
   Null C_TAX_AUTHORITY_DISP,
   Null C_LEVEL,
   /*ZX_REP_FIN_TAXREGISTER.cf_jurisdiction_levels(
     DET.TRX_ID, 
     DET.TRX_LINE_ID, 
     DET.TAX_LINE_ID, 
     :CP_CONC_REQUEST_ID, 
   decode(HG.geography_element4, null,
   decode(HG.geography_element3, null,
   decode(HG.geography_element2, null
         , 1 , 2), 3), 4))            C_LEVEL_1_JURIS,
   nvl(HG.geography_element2_code,
       ZX_REP_FIN_TAXREGISTER.CP_LEVEL_2_VAL)       C_LEVEL_2,
   ZX_REP_FIN_TAXREGISTER.CP_JURIS_2_VAL            C_LEVEL_2_JURIS,
   nvl(HG.geography_element3,
       ZX_REP_FIN_TAXREGISTER.CP_LEVEL_3_VAL)       C_LEVEL_3,
   ZX_REP_FIN_TAXREGISTER.CP_JURIS_3_VAL            C_LEVEL_3_JURIS,
   nvl(HG.geography_element4,
       ZX_REP_FIN_TAXREGISTER.CP_LEVEL_4_VAL)       C_LEVEL_4,
   ZX_REP_FIN_TAXREGISTER.CP_JURIS_4_VAL            C_LEVEL_4_JURIS,
SUBSTR(hg.geography_element1_code,1,2)||'-'||
Decode(hg.geography_element2_code,NULL,'',SUBSTR(hg.geography_element2_code,1,2)||'-')||
DECODE(hg.geography_element3,null,'',SUBSTR(hg.geography_element3,1,30)||'-')||
DECODE(hg.geography_element4,null,'',SUBSTR(hg.geography_element4,1,30)) C_TAX_AUTHORITY,
   P.PARTY_NAME                       C_TAX_AUTHORITY_DISP,
   decode(HG.geography_element4, null,
   decode(HG.geography_element3, null,
   decode(HG.geography_element2, null, 1 , 2), 3), 4)             C_LEVEL,*/
   to_char(DET.TRX_DATE,'MM/DD/YYYY') C_TRX_DATE,
   NVL(BILLING_TP_NAME, SHIPPING_TP_NAME)  C_CUST_NAME,
   DET.TAX_JURISDICTION_CODE          C_TAX_JURISDICTION_CODE,
   DET.TRX_LINE_DESCRIPTION        C_TRX_LINE_DESC,
   (DET.TRX_LINE_AMT * (nvl(DET.CURRENCY_CONVERSION_RATE,1))) C_LINE_FUN_AMT,
   nvl(DET.TAX_AMT_FUNCL_CURR ,det.tax_amt)  C_TAX_ACCOUNTED_AMOUNT,
   --Null  Summary requirements (from Tax Register)
ZX_REP_FIN_TAXREGISTER.cf_ex_taxable_acc_amtformula(
  NVL (DET.HISTORICAL_FLAG , 'N'), 
  DET.TRX_ID, 
  DET.trx_line_id, 
  ACT.ACTG_LINE_CCID, 
  nvl ( DET.TAXABLE_AMT_FUNCL_CURR , det.taxable_amt ), 
  DET.TRX_LINE_AMT , 
  DET.TAX_AMT, 
  nvl ( DET.TAX_AMT_FUNCL_CURR , det.tax_amt ), 
  DET.TAXABLE_AMT, 
  DET.EVENT_CLASS_CODE, 
  DET.TRX_LINE_CLASS, 
  DET.TAX_RATE_ID, 
  ( DET.TRX_LINE_AMT * ( nvl ( DET.CURRENCY_CONVERSION_RATE , 1 ) ) ), 
  tax_exemption_id, 
  DET.EXEMPT_CERTIFICATE_NUMBER, 
  DET.EXEMPT_REASON_CODE, 
  DET.TAX_LINE_ID, 
  DET.TAX_ONLY_FLAG,
  DET.TAX_EXCEPTION_ID,
  DET.TAX_EXCEPTION_REASON_CODE) CF_TAXABLE_ACC_AMT,
ZX_REP_FIN_TAXREGISTER.cf_ex_taxable_amtformula(
     DET.TRX_ID, DET.trx_line_id, DET.TAX_RATE_ID, 
     tax_exemption_id, DET.EXEMPT_CERTIFICATE_NUMBER, 
     DET.EXEMPT_REASON_CODE, DET.TAXABLE_AMT, 
     DET.TRX_CURRENCY_CODE,
     DET.TAX_EXCEPTION_ID,
     DET.TAX_EXCEPTION_REASON_CODE
     ) CF_TAXABLE_AMT,
ZX_REP_FIN_TAXREGISTER.CP_LINE_FUN_AMT_p CP_LINE_FUN_AMT,
ZX_REP_FIN_TAXREGISTER.CP_LINE_AMOUNT_p CP_LINE_AMOUNT,
ZX_REP_FIN_TAXREGISTER.CP_exempt_acct_amt_p CP_exempt_acct_amt,
ZX_REP_FIN_TAXREGISTER.CP_exempt_amt_p CP_exempt_amt,
ZX_REP_FIN_TAXREGISTER.CP_exception_acct_amt_p CP_exception_acct_amt,
ZX_REP_FIN_TAXREGISTER.CP_exception_amt_p CP_exception_amt,
(SELECT   to_char(rtrim( xmlserialize( content extract( xmlagg( xmlelement("e",  REPORTING_TYPE_CODE ||',') ) , '//text()' ) ) , '|' ) )||'|'||
to_char(rtrim( xmlserialize( content extract( xmlagg( xmlelement("e",  ENTITY_CODE ||',') ) , '//text()' ) ) , '|' ) )||'|'||
to_char(rtrim( xmlserialize( content extract( xmlagg( xmlelement("e",  REPORTING_CODE_VALUE ||',') ) , '//text()' ) ) , '|' ) )
                                                FROM (SELECT  

  RPT.REPORTING_TYPE_CODE  ,RPT.ENTITY_CODE ,RPT.REPORTING_CODE_VALUE

FROM
  fusion.ZX_REP_TRX_RPT_CODE_ASSOC_V RPT
WHERE 
 RPT.DETAIL_TAX_LINE_ID = DET.DETAIL_TAX_LINE_ID
AND RPT.REQUEST_ID = DET.REQUEST_ID
AND &LP_REP_CODE
))  C_REPORTING_TYPE_CODE ,
(SELECT ACA.check_date
FROM AP_INVOICES_ALL AIA,
     AP_INVOICE_LINES_ALL AP, 
     AP_INVOICE_PAYMENTS_ALL AIPA,
     AP_CHECKS_ALL ACA,
	 ZX_LINES ZL
WHERE 1=1	 
AND AIPA.INVOICE_ID = AIA.INVOICE_ID
AND AIPA.CHECK_ID = ACA.CHECK_ID
AND ACA.VOID_DATE IS NULL
AND AIA.INVOICE_ID = AP.INVOICE_ID
AND ZL.TRX_ID = AP.INVOICE_ID
AND ZL.TRX_LINE_ID = AP.LINE_NUMBER
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID
AND AIA.INVOICE_ID = DET.TRX_ID) INV_PMT_DATE,
DET.TAX_TYPE_CODE	TAX_CODE,
DET.CURRENCY_CONVERSION_RATE TRANSACTION_EXRATE,
NVL(DET.TAX_AMT_TAX_CURR,0) 	REPORTED_TAX_AMOUNT,
NVL(DET.TAXABLE_AMT_TAX_CURR,0) REPORTED_TAXABLE_AMOUNT,
NVL((Select ZL.REC_NREC_TAX_AMT_FUNCL_CURR 
FROM ZX_REC_NREC_DIST ZL
WHERE  ZL.TAX_LINE_ID = DET.TAX_LINE_ID 
AND ZL.REC_NREC_TAX_DIST_ID=DET.REC_NREC_TAX_DIST_ID
AND ZL.RECOVERABLE_FLAG=DET.TAX_RECOVERABLE_FLAG),0)	REC_TAX_AMT_1,
xle.NAME COMPANY_NAME,
(Select ZL.TAX_REGIME_CODE 
FROM ZX_LINES ZL
WHERE  ZL.TAX_LINE_ID = DET.TAX_LINE_ID) TAX_REGIME_CODE_1,
(Select ZL.TAX 
FROM ZX_LINES ZL
WHERE  ZL.TAX_LINE_ID = DET.TAX_LINE_ID) TAX_TYPE_CODE_1,
(Select ZFCV.CLASSIFICATION_NAME 
FROM AP_INVOICE_LINES_ALL AP,
     ZX_LINES ZL,
	 ZX_FC_CODES_VL ZFCV
WHERE 1=1
AND ZL.TRX_ID = AP.INVOICE_ID
AND ZL.TRX_LINE_ID = AP.LINE_NUMBER
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID 
AND ZL.ENTITY_CODE = 'AP_INVOICES'
AND AP.PRODUCT_CATEGORY = ZFCV.CLASSIFICATION_CODE
AND ZFCV.CLASSIFICATION_TYPE_CODE = 'PRODUCT_CATEGORY'
AND AP.INVOICE_ID =DET.TRX_ID) PRODUCT_TYPE_1,
(Select ZL.TAX_RATE 
FROM ZX_LINES ZL,
     ZX_RATES_B ZRB
WHERE  ZL.TAX_RATE_ID = ZRB.TAX_RATE_ID
--AND ZL.ORIG_TAX_RATE_ID = ZRB.ORIG_TAX_RATE_ID
AND ZRB.TAX = 'COUNTRY CGST'
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID) CGST,
(Select ZL.TAX_RATE 
FROM ZX_LINES ZL,
     ZX_RATES_B ZRB
WHERE  ZL.TAX_RATE_ID = ZRB.TAX_RATE_ID
--AND ZL.ORIG_TAX_RATE_ID = ZRB.ORIG_TAX_RATE_ID
AND ZRB.TAX = 'STATE SGST'
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID) SGST,
(Select ZL.TAX_RATE 
FROM ZX_LINES ZL,
     ZX_RATES_B ZRB
WHERE ZL.TAX_RATE_ID = ZRB.TAX_RATE_ID
--AND ZL.ORIG_TAX_RATE_ID = ZRB.ORIG_TAX_RATE_ID
AND ZRB.TAX = 'COUNTRY IGST'
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID) IGST,
TO_CHAR((SELECT ACA.check_date
FROM AP_INVOICES_ALL AIA,
     AP_INVOICE_LINES_ALL AP, 
     AP_INVOICE_PAYMENTS_ALL AIPA,
     AP_CHECKS_ALL ACA,
	 ZX_LINES ZL
WHERE 1=1	 
AND AIPA.INVOICE_ID = AIA.INVOICE_ID
AND AIPA.CHECK_ID = ACA.CHECK_ID
AND ACA.VOID_DATE IS NULL
AND AIA.INVOICE_ID = AP.INVOICE_ID
AND ZL.TRX_ID = AP.INVOICE_ID
AND ZL.TRX_LINE_ID = AP.LINE_NUMBER
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID
AND AIA.INVOICE_ID =DET.TRX_ID),'DD/MM/YYYY') PAYMENT_DATE_INDIA,
(SELECT ZFCV.CLASSIFICATION_NAME 
FROM 
     RA_CUSTOMER_TRX_LINES_ALL RCTLA,
	 RA_CUSTOMER_TRX_ALL RCTA,
     ZX_LINES ZL,
	 ZX_FC_CODES_VL ZFCV
WHERE 1=1
AND RCTLA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
AND ZL.TRX_ID  = RCTLA.CUSTOMER_TRX_ID 
AND DET.TRX_ID  = RCTLA.CUSTOMER_TRX_ID 
AND ZL.TRX_LINE_ID = RCTLA.CUSTOMER_TRX_LINE_ID
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID 
AND RCTLA.PRODUCT_CATEGORY = ZFCV.CLASSIFICATION_CODE
AND ZFCV.CLASSIFICATION_TYPE_CODE = 'PRODUCT_CATEGORY'
--AND RCTA.TRX_NUMBER  = '101003'
 ) PRODUCT_TYPE_REC,
(SELECT EXTENDED_AMOUNT
FROM 
     RA_CUSTOMER_TRX_LINES_ALL RCTLA,
     ZX_LINES ZL
WHERE 1=1
AND ZL.TRX_ID = RCTLA.CUSTOMER_TRX_ID 
AND RCTLA.CUSTOMER_TRX_ID=DET.TRX_ID
AND ZL.TRX_LINE_ID  = RCTLA.CUSTOMER_TRX_LINE_ID
AND RCTLA.LINE_TYPE='TAX'
AND ZL.TAX_LINE_ID=DET.TAX_LINE_ID) TOTAL_TAX_AMOUNT,
(SELECT RCTA.TRX_NUMBER
FROM 
AR_RECEIVABLE_APPLICATIONS_ALL APP,
AR_PAYMENT_SCHEDULES_ALL     PS_CM,
RA_CUSTOMER_TRX_ALL RCTA
WHERE APP.APPLIED_CUSTOMER_TRX_ID = DET.TRX_ID --'300000010743311'
AND APP.PAYMENT_SCHEDULE_ID = PS_CM.PAYMENT_SCHEDULE_ID
AND PS_CM.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID 
AND APP.APPLICATION_TYPE  = 'CM' -- 16001 - CREDIT MEMO
GROUP BY RCTA.TRX_NUMBER
) CREDIT_TRX_NUMBER,
(SELECT TO_CHAR(RCTA.TRX_DATE,'DD/MM/YYYY')
FROM 
AR_RECEIVABLE_APPLICATIONS_ALL APP,
AR_PAYMENT_SCHEDULES_ALL     PS_CM,
RA_CUSTOMER_TRX_ALL RCTA
WHERE APP.APPLIED_CUSTOMER_TRX_ID = DET.TRX_ID --'300000010743311'
AND APP.PAYMENT_SCHEDULE_ID = PS_CM.PAYMENT_SCHEDULE_ID
AND PS_CM.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID 
AND APP.APPLICATION_TYPE  = 'CM' -- 16001 - CREDIT MEMO
GROUP BY RCTA.TRX_DATE) CREDIT_TRX_DATE_INDIA,
(SELECT RCTA.TRX_NUMBER
FROM 
AR_RECEIVABLE_APPLICATIONS_ALL APP,
AR_PAYMENT_SCHEDULES_ALL     PS_CM,
RA_CUSTOMER_TRX_ALL RCTA
WHERE APP.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
AND  APP.PAYMENT_SCHEDULE_ID = PS_CM.PAYMENT_SCHEDULE_ID
AND PS_CM.CUSTOMER_TRX_ID = DET.TRX_ID --'300000010743329'
AND APP.APPLICATION_TYPE  = 'CM' -- 16000 - ORGINAL INVOICE
GROUP BY RCTA.TRX_NUMBER) ORIGINAL_INV_NUM,
(SELECT TO_CHAR(RCTA.TRX_DATE,'DD/MM/YYYY')
FROM 
AR_RECEIVABLE_APPLICATIONS_ALL APP,
AR_PAYMENT_SCHEDULES_ALL     PS_CM,
RA_CUSTOMER_TRX_ALL RCTA
WHERE APP.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
AND  APP.PAYMENT_SCHEDULE_ID = PS_CM.PAYMENT_SCHEDULE_ID
AND PS_CM.CUSTOMER_TRX_ID = DET.TRX_ID --'300000010743329'
GROUP BY RCTA.TRX_DATE) ORIGINAL_TRX_DATE_INDIA,
(Select SUBSTR(AP.ATTRIBUTE2,1,INSTR(AP.ATTRIBUTE2,'|')-1) 
FROM AP_INVOICE_LINES_ALL AP,
     ZX_LINES ZL
WHERE 1=1
AND ZL.TRX_ID = AP.INVOICE_ID
AND ZL.TRX_LINE_ID = AP.LINE_NUMBER
AND ZL.TAX_LINE_ID= DET.TAX_LINE_ID  
AND ZL.ENTITY_CODE = 'AP_INVOICES'
AND AP.INVOICE_ID =DET.TRX_ID) PO_NUMBER,
(Select AP.ATTRIBUTE5
FROM AP_INVOICE_LINES_ALL AP,
     ZX_LINES ZL
WHERE 1=1
AND ZL.TRX_ID = AP.INVOICE_ID
AND ZL.TRX_LINE_ID = AP.LINE_NUMBER
AND ZL.TAX_LINE_ID= DET.TAX_LINE_ID 
AND ZL.ENTITY_CODE = 'AP_INVOICES'
AND AP.INVOICE_ID =DET.TRX_ID) HSN_CODE,
(SELECT RCTA.WAYBILL_NUMBER 
FROM RA_CUSTOMER_TRX_ALL RCTA, 
RA_CUSTOMER_TRX_LINES_ALL RCTLA,
ZX_LINES ZL
WHERE 1=1
AND RCTLA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
AND RCTA.CUSTOMER_TRX_ID = DET.TRX_ID
AND ZL.TRX_ID = RCTLA.CUSTOMER_TRX_ID 
AND ZL.TRX_LINE_ID  = RCTLA.CUSTOMER_TRX_LINE_ID
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID 
--RCTA.TRX_NUMBER  = '92003'
) SHIPPING_REFERENCE, 
(SELECT GCC.CONCATENATED_SEGMENTS 
FROM AP_INVOICES_ALL AIA,
     AP_INVOICE_LINES_ALL AP, 
	 AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
	 GL_CODE_COMBINATIONS GCC,
	 ZX_LINES ZL
WHERE 1=1	 
AND AIA.INVOICE_ID = AP.INVOICE_ID
AND AIA.INVOICE_ID = AIDA.INVOICE_ID
AND AIDA.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND ZL.TRX_ID = AP.INVOICE_ID
AND ZL.TRX_LINE_ID = AP.LINE_NUMBER
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID
AND ROWNUM = 1
--AND INVOICE_NUM = 'VKM UT1'
) GL_CODE,
(SELECT AIA.DOC_CATEGORY_CODE
FROM AP_INVOICES_ALL AIA,
     AP_INVOICE_LINES_ALL AP, 
	 ZX_LINES ZL
WHERE 1=1	 
AND AIA.INVOICE_ID = AP.INVOICE_ID
AND ZL.TRX_ID = AP.INVOICE_ID
AND ZL.TRX_LINE_ID = AP.LINE_NUMBER
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID
AND AIA.INVOICE_ID =DET.TRX_ID) DOC_CAT_INDIA,
(Select DISTINCT ZL.APPLICATION_ID 
FROM ZX_LINES ZL
WHERE ZL.TAX_LINE_ID = DET.TAX_LINE_ID) APP_ID,
--(DET.TRX_LINE_AMT * (nvl(DET.CURRENCY_CONVERSION_RATE,1)))	TAXABLE_ACC_AMOUNT,
ROUND(CASE WHEN (DET.TRX_LINE_AMT * (nvl(DET.CURRENCY_CONVERSION_RATE,1))) <>0 THEN (NVL(DET.TAXABLE_AMT_FUNCL_CURR,0)/(CASE WHEN NVL(DET.TAX_AMT_INCLUDED_FLAG,'N')='Y' THEN NVL(DET.TAXABLE_AMT_FUNCL_CURR,0) ELSE (DET.TRX_LINE_AMT * (nvl(DET.CURRENCY_CONVERSION_RATE,1))) END))*100 ELSE 0 END) TAX_REC_RATE,
NVL(DET.REC_TAX_AMT_TAX_CURR, 0)	REC_TAX_AMT_TAX_CURR,
--DET.TRX_LINE_AMT AS LINE_AMOUNT,
(SELECT DECODE(AIA.APPROVAL_STATUS, 'APPROVED', 'Validated', 
									'NEVER APPROVED', 'Not Validated', 
									'NEEDS REAPPROVAL', 'Needs revalidation',
									'UNPAID','Unpaid', 
									'UNAPPROVED','Never Validated', 
									'FULL','Fully Applied', 
									'CANCELLED','Cancelled',
									'AVAILABLE','Available',
									'PERMANENT','Permanent Prepayment',AIA.APPROVAL_STATUS)
FROM AP_INVOICES_ALL AIA,
     AP_INVOICE_LINES_ALL AP, 
	 ZX_LINES ZL
WHERE 1=1	 
AND AIA.INVOICE_ID = AP.INVOICE_ID
AND ZL.TRX_ID = AP.INVOICE_ID
AND ZL.TRX_LINE_ID =AP.LINE_NUMBER
AND ZL.TAX_LINE_ID =DET.TAX_LINE_ID
AND AIA.INVOICE_ID =DET.TRX_ID)	AP_INV_STATUS,
(SELECT TL.NAME
FROM AP_INVOICES_ALL AIA,
     AP_INVOICE_LINES_ALL AP, 
	 AP_TERMS_TL TL,
	 ZX_LINES ZL
WHERE 1=1	 
AND AIA.INVOICE_ID = AP.INVOICE_ID
AND TL.TERM_ID		=AIA.TERMS_ID	
AND TL.LANGUAGE='US'
AND ZL.TRX_ID = AP.INVOICE_ID
AND ZL.TRX_LINE_ID = AP.LINE_NUMBER
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID
AND AIA.INVOICE_ID =DET.TRX_ID)	PAY_TERMS,
(SELECT ZR.REGISTRATION_NUMBER
FROM AP_INVOICES_ALL AIA,
	 ZX_REGISTRATIONS ZR,
     AP_INVOICE_LINES_ALL AP, 
	 ZX_LINES ZL
WHERE 1=1	 
AND AIA.INVOICE_ID = AP.INVOICE_ID
AND AIA.FIRST_PARTY_REGISTRATION_ID=ZR.REGISTRATION_ID
AND ZL.TRX_ID = AP.INVOICE_ID
AND ZL.TRX_LINE_ID = AP.LINE_NUMBER
AND ZL.TAX_LINE_ID = DET.TAX_LINE_ID
AND AIA.INVOICE_ID =DET.TRX_ID)	FIRST_TAX_REG

FROM      
    ZX_REP_TRX_JX_EXT_T   EXT,
    ZX_REP_CONTEXT_T CON,
    ZX_REP_TRX_DETAIL_T   DET,
    ZX_REP_ACTG_EXT_T     ACT,
    HZ_GEOGRAPHIES HG,
    ZX_JURISDICTIONS_B JUR,
    ZX_PARTY_TAX_PROFILE PP,
    HZ_PARTIES P,
    HR_LOCATIONS HL ,
	XLE_ENTITY_PROFILES XLE
	
WHERE DET.REQUEST_ID =:CP_CONC_FTR_REQUEST_ID 
AND DET.REQUEST_ID = CON.REQUEST_ID
AND Exists ( select lookup_code 
     From fnd_lookups
   Where lookup_type = 'JE_ENABLE_FEATURE'  
     and lookup_code = 'JE_FTR_TRIM'
     and enabled_flag = 'Y'
    and (trunc(sysdate) BETWEEN nvl(start_date_active, trunc(sysdate)) AND nvl(end_date_active, trunc(sysdate ))) )
AND NVL(DET.REP_CONTEXT_ID ,CON.REP_CONTEXT_ID) = CON.REP_CONTEXT_ID
and  ACT.DETAIL_TAX_LINE_ID(+) = DET.DETAIL_TAX_LINE_ID 
AND EXT.DETAIL_TAX_LINE_ID(+) = DET.DETAIL_TAX_LINE_ID
AND DET.SHIP_TO_LOCATION_ID = HL.LOCATION_ID(+)
AND DET.TAX_JURISDICTION_CODE = JUR.TAX_JURISDICTION_CODE(+)
AND JUR.ZONE_GEOGRAPHY_ID = HG.GEOGRAPHY_ID(+)
AND DET.TAX_REGIME_CODE = JUR.TAX_REGIME_CODE(+)
AND DET.TAX = JUR.TAX(+) 
AND JUR.REP_TAX_AUTHORITY_ID = PP.PARTY_TAX_PROFILE_ID(+)
AND PP.PARTY_ID = P.PARTY_ID(+)
AND XLE.LEGAL_ENTITY_ID = DET.LEGAL_ENTITY_ID
AND NVL(DET.REVERSE_FLAG,'N') !='Y'
AND (CASE WHEN DET.TAX_RECOVERABLE_FLAG='N' AND NVL(DET.TAX_RECOVERY_RATE,0)=0 THEN 'N' ELSE 'Y' END)='Y'
--AND DET.TRX_NUMBER='2038'
AND &LP_REP_COND
--ORDER BY  
--DET.TRX_NUMBER,
--DET.TRX_LINE_NUMBER,
--DET.TAX_LINE_NUMBER,
--c_tax_jurisdiction_code
)
ORDER BY  
TRX_NUMBER,
TRX_LINE_NUMBER,
TAX_LINE_NUMBER,
c_tax_jurisdiction_code
:CP_CONC_FTR_REQUEST_ID :CP_CONC_REQUEST_ID :CP_NLS_NO :CP_NLS_YES :MI :SS
The data-warehouse model — one fact surrounded by conformed dimensions (what you slice by) and measures (what you aggregate), expressed as dbt so it migrates with you. Grain: one row per source transaction.
●— fact → dimension join
Element	Type	Definition
ZX_REC_NREC_DIST	dimension	dimension
AP_INVOICES_ALL	dimension	dimension
AP_INVOICE_LINES_ALL	dimension	dimension
ZX_RATES_B	dimension	dimension
Taxable Amount	measure	measure
Tax Amount	measure	measure
Runs on your cloud warehouse — Snowflake, BigQuery, Redshift, or Synapse on AWS, Google Cloud, Azure, or any provider. Reconciled to the source control total — 0% variance by design. You own the code, the model, and the data.
How the data gets here: a BICC bulk extract of the source tables above, on the same pattern for every report. See the extraction pattern & data flow →
See the complete model
How this report's fact and dimensions fit the full picture, via conformed keys.
Transaction Tax data model →Enterprise model →
Every source object behind this report. Each linked table has its own page with full column descriptions, drawn from the Oracle BICC lineage and articulated for practitioners.
Table	Reporting columns	Subject areas
ZX_LINES	Setup / configuration table — joined for reference, not exposed for analytics
ZX_REC_NREC_DIST	Setup / configuration table — joined for reference, not exposed for analytics
AP_INVOICES_ALL	63	15
AP_INVOICE_LINES_ALL	58	19
ZX_RATES_B	Setup / configuration table — joined for reference, not exposed for analytics
Reporting columns = fields the report selects that are exposed as analytics attributes; subject areas = the OTBI subject areas the table appears in. Setup and configuration tables (master data, ledger and book setup, lookups) are referenced by the report's joins but aren't exposed as analytics columns or subject areas — that's expected, not a gap.