1) Below is the query to get tax details from invoice:
SELECT lines.TAX_AMT,lines.tax_rate
FROM zx_lines lines,ra_customer_trx_all rct,ra_customer_trx_lines_all rl,
ZX_TAXES_B ZTB,GL_DAILY_CONVERSION_TYPES gc
where rct.trx_number=:TRX_NUMBER and rct.customer_trx_id=lines.trx_id
and rct.customer_trx_id = rl.customer_trx_id
and ZTB.Exchange_Rate_Type=gc.conversion_type
and ztb.tax_id=lines.tax_id
AND RL.LINE_TYPE='LINE'
and rct.org_id=:P_ORG_ID
and lines.trx_line_id=rl.customer_trx_line_id
and rl.customer_trx_line_id=:customer_trx_line_id;
2) For Standard and Blanket PO:
---for tax rate and amount
SELECT tax_rate , tax_amt
(SELECT lines.tax_rate ,lines.tax_amt
FROM po_headers_all poh,
po_lines_all pol , po_line_locations_all plla ,zx_lines Lines
--WHERE (poh.segment1 = :p_po_no OR :p_po_no IS NULL)
WHERE poh.PO_HEADER_ID=:po_header_id1
AND POL.PO_LINE_ID=:L_PO_LINE_ID
AND poh.org_id = :p_org_id
AND pol.po_header_id = poh.po_header_id
AND pol.org_id = poh.org_id
and lines.trx_id=poh.po_header_id and lines.trx_line_id=plla.line_location_id
and pol.po_line_id=plla.po_line_id
--AND poh.authorization_status = 'APPROVED'
AND :p_report_type = 'STANDARD'
AND pol.quantity > 0 --- version 115.2 added this condition to show only those lines which are open/not fully cancelled
UNION ALL
SELECT lines.tax_rate ,lines.tax_amt
FROM po_headers_all poh,
po_lines_all pol,
po_distributions_all pod,
po_releases_all prl,po_line_locations_all pll,
zx_lines lines
--WHERE (poh.segment1 = :p_po_no OR :p_po_no IS NULL)
WHERE poh.PO_HEADER_ID=:po_header_id1
AND poh.org_id = :p_org_id
--AND poh.authorization_status = 'APPROVED'
AND pol.po_header_id = poh.po_header_id
AND pol.org_id = poh.org_id
AND pod.po_header_id = pol.po_header_id
AND pod.po_line_id = pol.po_line_id
AND pod.org_id = pol.org_id
AND prl.po_release_id = pod.po_release_id
AND prl.po_header_id = pod.po_header_id
AND prl.org_id = pod.org_id
and lines.trx_id=prl.po_release_id
and lines.trx_line_id=pll.line_location_id
AND :p_report_type = 'BLANKET'
AND ( prl.po_release_id =NVL( :po_release_id1,prl.po_release_id))
and pol.po_line_id =:L_PO_LINE_ID
AND PRL.RELEASE_NUM =NVL(:P_RELEASE_NO,PRL.RELEASE_NUM)
and pll.po_line_id=pol.PO_LINE_ID
and pll.PO_HEADER_ID=pol.PO_HEADER_ID
and pll.LINE_LOCATION_ID=pod.line_location_id
and NVL(pll.CANCEL_FLAG,'N')='N'
--and not exists (select 1 from PO_LINE_LOCATIONS_RELEASE_V pllr
--where pllr.PO_RELEASE_ID=prl.PO_RELEASE_ID
--and nvl(QUANTITY_CANCELLED,0)<>0)
) a;
