Query to get AP Invoice lines Tax details in Oracle Fusion

 Query to get AP Invoice lines Tax details in Oracle Fusion 

SELECT 

  aia.invoice_num, 

  xep.name Legal_Entity_Name, 

  psv.vendor_name party, 

  aila.line_number Line_Ref, 

  aila.line_type_lookup_code, 

  gcc.segment1 Distribution_entity, 

  zl.tax_amt Tax_amount, 

  zl.tax_regime_code Tax_regime, 

  zl.tax_jurisdiction_code Tax_Juridiction, 

  zl.taxable_amt TaxableAmt, 

  ZE.exception_reason_code Exemption_Reason, 

  ZE.tax_rate_code Exemption_Rate, 

  zrb.country_code Country_of_taxation, 

  zl.tax_line_number Tax_Line_Ref, 

  zl.tax, 

  zl.tax_rate_code, 

  zl.tax_rate 

FROM 

  ap_invoices_all aia, 

  poz_suppliers_v psv, 

  xle_entity_profiles xep, 

  ap_invoice_lines_all aila, 

  gl_code_combinations gcc, 

  ap_invoice_distributions_all aida, 

  zx_lines zl, 

  zx_status_b zsb, 

  zx_exceptions ze, 

  zx_regimes_b zrb 

WHERE 

  1 = 1 

  AND psv.vendor_id = aia.vendor_id 

  AND aia.legal_entity_id = xep.legal_entity_id(+) 

  AND aia.invoice_id = aila.invoice_id 

  AND aila.invoice_id = aida.invoice_id 

  AND aila.line_number = aida.invoice_line_number 

  AND aia.invoice_id = zl.trx_id(+) 

  AND aila.line_number = zl.trx_line_number(+) 

  AND zl.application_id(+) = 200 

  AND ze.tax_exception_id(+) = zl.tax_exception_id 

  AND zsb.tax_status_id = zl.tax_status_id 

  AND zl.tax_regime_id = zrb.tax_regime_id 

  AND aida.dist_code_combination_id = gcc.code_combination_id 

  AND (

    aida.line_type_lookup_code = 'MISCELLANEOUS' 

    OR aida.line_type_lookup_code = 'FREIGHT' 

    OR aida.line_type_lookup_code = 'ITEM'

  ) 

  AND zsb.tax_status_code = 'EXEMPT'