Query To Find All the Tax Invoices in Payable

Query To Find All the Tax Invoices in Payable

SELECT DISTINCT
--pla.po_line_id,b.line_number,pla.item_description,
    pla.line_num po_line_num,
    a.invoice_num,
    a.description invoice_description,
    a.invoice_date,
    a.source,
    a.invoice_amount,
    b.amount invoice_line_amount,
    zx.tax_amt,
    a.gl_date
--,a.vendor_id,a.vendor_site_id,
   ,
    supp.vendor_name
--,b.line_number,b.line_type_lookup_code,b.amount item_amount 
--,b.po_header_id,b.po_line_id,b.po_line_location_id,b.po_distribution_id
   ,
    poh.segment1 po_number,
    poh.comments,
    prha.segment1 po_requisition,
    (
        SELECT
            per.full_name
        FROM
            apps.per_all_people_f per
        WHERE
            prha.preparer_id = per.person_id
            AND ROWNUM < 2
    ) po_preparer_name,
    (
        SELECT
            per.full_name
        FROM
            apps.per_all_people_f per
        WHERE
            poh.agent_id = per.person_id
            AND ROWNUM < 2
    ) po_buyer_nam,
    (
        SELECT
            per.full_name
        FROM
            apps.per_all_people_f per
        WHERE
            prla.to_person_id = per.person_id
            AND ROWNUM < 2
    ) requestor,
    prla.item_description,
    gcc2.segment1 ledger,
    gcc2.segment2 location,
    gcc2.segment3 dept,
    gcc2.segment4 account
--,(select segment1 from  apps.gl_code_combinations gcc1 where gcc1.code_combination_id= pda.CODE_COMBINATION_ID) Ledger
   ,
    (
        SELECT
            gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,1,gcc.segment1)
        FROM
            gl_code_combinations gcc
        WHERE
            code_combination_id = pda.code_combination_id
    ) ledger_description
--,(select segment2 from  apps.gl_code_combinations gcc1 where gcc1.code_combination_id= pda.CODE_COMBINATION_ID) Location
   ,
    (
        SELECT
            gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,2,gcc.segment2)
        FROM
            gl_code_combinations gcc
        WHERE
            code_combination_id = pda.code_combination_id
    ) location_description
--,(select segment3 from  apps.gl_code_combinations gcc1 where gcc1.code_combination_id= pda.CODE_COMBINATION_ID) Dept
   ,
    (
        SELECT
            gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,3,gcc.segment3)
        FROM
            gl_code_combinations gcc
        WHERE
            code_combination_id = pda.code_combination_id
    ) dept_description
--,(select segment4 from  apps.gl_code_combinations gcc1 where gcc1.code_combination_id= pda.CODE_COMBINATION_ID) Account
   ,
    (
        SELECT
            gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,4,gcc.segment4)
        FROM
            gl_code_combinations gcc
        WHERE
            code_combination_id = pda.code_combination_id
    ) account_description
FROM
    apps.ap_suppliers supp,
    apps.ap_invoices_all a,
    apps.ap_invoice_lines_all b,
    apps.zx_lines_summary zx,
    apps.po_headers_all poh,
    apps.po_lines_all pla,
    apps.po_distributions_all pda,
    apps.po_req_distributions_all prda,
    apps.po_requisition_lines_all prla,
    apps.po_requisition_headers_all prha,
    apps.gl_code_combinations gcc2,
    mtl_categories_b_kfv mc
WHERE
    1 = 1
    AND a.invoice_id = zx.trx_id
    AND zx.application_id = 200
    AND zx.entity_code = 'AP_INVOICES' --and zx.summary_tax_line_number=b.line_number
    AND a.vendor_id = supp.vendor_id
    AND a.invoice_id = b.invoice_id
    AND b.po_header_id = poh.po_header_id (+)
    AND b.po_distribution_id = pda.po_distribution_id (+)
    AND b.po_line_id = pla.po_line_id (+)
    AND pla.category_id = mc.category_id
    AND pda.req_distribution_id = prda.distribution_id (+)
    AND prda.requisition_line_id = prla.requisition_line_id (+)
    AND prla.requisition_header_id = prha.requisition_header_id (+)
    AND gcc2.code_combination_id = pda.code_combination_id 
--and a.creation_date between '1-July-2018' and '31-Oct-2018'
    AND a.invoice_date BETWEEN '1-July-2018' AND '31-Oct-2018'
    AND gcc2.segment1 = '341'
--and pla.item_description not like '%RAW MATERIALS%'
    AND mc.concatenated_segments NOT LIKE '%QUARRY MATERIALS_SAND%'
--and mc.CONCATENATED_SEGMENTS not like '%RAW MATERIALS%'
--and poh.segment1= '5929253' --'5958648' --'5941453'--'5929253'
    AND a.invoice_id = 12780569
    AND zx.tax_amt != 0
    AND b.amount != 0
ORDER BY
    a.invoice_date,
    supp.vendor_name;

Query to Find All Tax Code and Tax Rate used in the Oracle R12

Query To Find All Tax Code and Tax Rate used in the Oracle R12

SELECT DISTINCT
    a.tax_id,
    a.tax tax_code,
    b.tax_full_name,
    a.tax_regime_code,
    a.tax_type_code,
    c.tax_rate_code,
    d.tax_rate_name,
    c.tax_status_code,
    c.rate_type_code,
    c.percentage_rate
--,c.DESCRIPTION,e.ENTITY_CODE,e.EVENT_CLASS_CODE,e.TRX_ID,e.TRX_NUMBER
FROM
    zx_taxes_b a,
    zx_taxes_tl b,
    zx_rates_b c,
    zx_rates_tl d,
--zx_lines_summary e,
    zx_lines_v f
WHERE
    1 = 1
    AND a.tax_id = b.tax_id
    AND c.tax = a.tax
    AND c.tax_rate_id = d.tax_rate_id
--and e.TAX = a.TAX
--and e.TAX_RATE_ID = c.TAX_RATE_ID
    AND f.tax = a.tax
    AND f.tax_rate_id = c.tax_rate_id
--and a.TAX = 'AU GST TAX'
ORDER BY
    a.tax,
    c.tax_rate_code;

To Find All Tax Code and Tax Rate defined in the Oracle R12

SELECT DISTINCT
    a.tax_id,
    a.tax tax_code,
    b.tax_full_name,
    a.tax_regime_code,
    a.tax_type_code,
    c.tax_rate_code,
    d.tax_rate_name,
    c.tax_status_code,
    c.rate_type_code,
    c.percentage_rate,
    c.description
FROM
    zx_taxes_b a,
    zx_taxes_tl b,
    zx_rates_b c,
    zx_rates_tl d
WHERE
    1 = 1
    AND a.tax_id = b.tax_id
    AND c.tax = a.tax
    AND c.tax_rate_id = d.tax_rate_id
--and a.TAX = 'AU GST TAX'
ORDER BY
    a.tax,
    c.tax_rate_code;;

Relevant Tables :

a. Tax Regimes: ZX_REGIMES_B

b. Taxes: ZX_TAXES_B

c. Tax Status: ZX_STATUS_B

d. Tax Rates: ZX_RATES_B

e. Tax Jurisdictions: ZX_JURISDICTIONS_B

f. Tax Rules: ZX_RULES_B

select * from ZX_LINES_V where ENTITY_CODE = 'AP_INVOICE';
select * from zx_lines_summary  ;
select * from ZX_TAXES_B where TAX = 'AU GST TAX';
select * from ZX_TAXES_TL where TAX_ID = 209333;
select * from ZX_RATES_B WHERE tax = 'AU GST TAX';
select * from ZX_RATES_TL;
select * from AP_TAX_CODES_ALL;

SELECT * FROM zx_regimes_b
WHERE tax_regime_code = '&tax_regime_code';

SELECT * FROM zx_taxes_b
WHERE DECODE('&tax_name',null,'xxx',tax) = nvl('&tax_name','xxx')
AND tax_regime_code = '&tax_regime_code';

SELECT * FROM zx_status_b
WHERE tax = '&tax_name'
AND tax_regime_code = '&tax_regime_code';

SELECT * FROM zx_rates_b
WHERE tax = '&tax_name'
AND tax_regime_code = '&tax_regime_code';

SELECT * FROM zx_jurisdictions_b
WHERE DECODE('&tax_name',null,'xxx',tax) = nvl('&tax_name','xxx')
AND tax_regime_code = '&tax_regime_code';

SELECT * FROM zx_rules_b
WHERE tax = '&tax_name'
AND tax_regime_code = '&tax_regime_code';

EBTAX TRANSACTION TABLES 

Following are the main E-Business tax tables that will contain the transaction information that will have the tax details after tax is calculated.

a. ZX_LINES: This table will have the tax lines for associated with PO/Release schedules.

TRX_ID: Transaction ID. This is linked to the

PO_HEADERS_ALL.PO_HEADER_ID

TRX_LINE_ID: Transaction Line ID. This is linked to the

PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID

b. ZX_REC_NREC_DIST: This table will have the tax distributions for associated with PO/Release distributions.

TRX_ID: Transaction ID. This is linked to the

PO_HEADERS_ALL.PO_HEADER_ID

TRX_LINE_ID: Transaction Line ID. This is linked to the

PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID

TRX_LINE_DIST_ID: Transaction Line Distribution ID. This is linked to the

PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID

RECOVERABLE_FLAG: Recoverable Flag. If the distribution is recoverable then the flag will be set to Y and there will be values in the RECOVERY_TYPE_CODE and RECOVERY_RATE_CODE.

c. PO_REQ_DISTRIBUTIONS_ALL: This table will have the tax distributions for associated with Requisition distribution.

RECOVERABLE_TAX: Recoverable tax amount

NONRECOVERABLE_TAX: Non Recoverable tax amount

d. ZX_LINES_DET_FACTORS: This table holds all the information of the tax line transaction for both the requisitions as well as the purchase orders/releases.

TRX_ID: Transaction ID. This is linked to the

PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID /

PO_HEADERS_ALL.PO_HEADER_ID

TRX_LINE_ID: Transaction Line ID. This is linked to the

PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID /

PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID