Introduction
This Post illustrates the steps required to getting Sales Tax Transactions Query in Oracle EBS R12.
Script to getting Sales Tax Transactions Query
SELECT DISTINCT aia.invoice_date transaction_date,
aia.invoice_num transaction_number, hou.NAME company_name,
zl.tax_rate_code tax_code, zl.tax_rate tax_rate,
(SELECT meaning
FROM fnd_lookup_values_vl
WHERE lookup_code =
UPPER (zl.event_class_code)
AND lookup_type = ‘ZX_TRL_TAXABLE_TRX_TYPE’
AND enabled_flag = ‘Y’) transaction_class,
aia.gl_date accounting_date, psv.vendor_name third_party,
pss.vendor_site_code site, zl.tax_amt tax_amount,
zl.trx_currency_code entered_curr,
zl.taxable_amt_funcl_curr tax_base_amount,
NVL (zl.tax_currency_code,
zl.trx_currency_code
) functional_currency,
tax_amt_funcl_curr tax_amount_func, xle.accounting_status,
xle.tgl gl_transfer_status
FROM ap_invoices_all aia,
ap_invoice_lines_all ail,
poz_suppliers_v psv,
poz_supplier_sites_v pss,
zx_lines zl,
xle_entity_profiles hou,
(SELECT CASE
WHEN xae.gl_transfer_date IS NULL
THEN ‘Not Transferred’
ELSE ‘Transferred’
END tgl,
xte.source_id_int_1,
CASE
WHEN xae.accounting_date IS NULL
THEN ‘Draft’
ELSE ‘Accounted’
END accounting_status
FROM xla_transaction_entities xte, xla_ae_headers xae
WHERE xte.entity_id = xae.entity_id) xle
WHERE aia.invoice_id = ail.invoice_id
AND aia.vendor_id = psv.vendor_id
AND aia.vendor_site_id = pss.vendor_site_id
AND ail.invoice_id = zl.trx_id(+)
AND ail.line_number = zl.trx_line_number(+)
AND hou.legal_entity_id(+) = aia.legal_entity_id
AND zl.cancel_flag <> ‘Y’
AND aia.cancelled_date IS NULL
UNION
SELECT DISTINCT rct.trx_date transaction_date,
rct.trx_number transaction_number, hou.NAME company_name,
zl.tax_rate_code tax_code, zl.tax_rate tax_rate,
(SELECT meaning
FROM fnd_lookup_values_vl
WHERE lookup_code = zl.event_class_code
AND lookup_type = ‘ZX_TRL_TAXABLE_TRX_TYPE’
AND enabled_flag = ‘Y’) transaction_class,
gl_date accounting_date, hca.account_name third_party,
NVL (hps.party_site_name, hps.party_site_number) site,
zl.tax_amt tax_amount, zl.trx_currency_code entered_curr,
zl.taxable_amt_funcl_curr tax_base_amount,
NVL (zl.tax_currency_code,
zl.trx_currency_code
) functional_currency,
tax_amt_funcl_curr tax_amount_func, xle.accounting_status,
xle.tgl gl_transfer_status
FROM ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
zx_lines zl,
xle_entity_profiles hou,
hz_cust_site_uses_all hcsua,
hz_cust_acct_sites_all hcasa,
hz_cust_accounts hca,
hz_party_sites hps,
(SELECT DISTINCT customer_trx_line_id, gl_date,
customer_trx_id
FROM ra_cust_trx_line_gl_dist_all) dist_tab,
(SELECT CASE
WHEN xae.gl_transfer_date IS NULL
THEN ‘Not Transferred’
ELSE ‘Transferred’
END tgl,
xte.source_id_int_1,
CASE
WHEN xae.accounting_date IS NULL
THEN ‘Draft’
ELSE ‘Accounted’
END accounting_status
FROM xla_transaction_entities xte, xla_ae_headers xae
WHERE xte.entity_id = xae.entity_id) xle
WHERE rct.customer_trx_id = rctl.customer_trx_id
AND rct.customer_trx_id = zl.trx_id(+)
AND rctl.line_number = zl.trx_line_number(+)
AND hou.legal_entity_id(+) = rct.legal_entity_id
AND zl.entity_code = ‘TRANSACTIONS’
AND zl.cancel_flag <> ‘Y’
AND rctl.line_type = ‘LINE’
AND hcsua.site_use_id = rct.bill_to_site_use_id
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.cust_account_id = hca.cust_account_id
AND hps.party_site_id = hcasa.party_site_id
AND dist_tab.customer_trx_id = rct.customer_trx_id
AND dist_tab.customer_trx_line_id = rctl.customer_trx_line_id
What we expect in the Script.
This Script helps us to comprehend how getting Sales Tax Transactions Query. A couple of tables which are being used in the scripts are ra_customer_trx_all,ra_customer_trx_lines_all,zx_lines, xle_entity_profiles,hz_cust_site_uses_all, hz_cust_acct_sites_all,hz_cust_accounts,hz_party_sites etc.
Summary
This Post described the Script getting Sales Tax Transactions Query in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
