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'
No comments:
Post a Comment