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;