---- Script for Purchase Order (PO) with Tax Query In oracle apps EBS R12 ---
SELECT aa.order_no1,
aa.tax_rate, SUM (NVL (aa.tax_amount, 0)) tax_amount,
aa.tax_name, aa.tax_line_no
FROM (SELECT h.segment1 order_no1, jpt.line_location_id,
jpt.tax_rate tax_rate,
jpt.tax_amount,
RTRIM (NVL (jct.tax_descr, NULL)) tax_name,
jpt.tax_line_no
FROM po.po_headers_all h,
po_lines_all l,
jai_po_taxes jpt,
jai_cmn_taxes_all jct
WHERE jpt.tax_id = jct.tax_id(+)
AND jct.end_date IS NULL
AND l.po_line_id = jpt.po_line_id(+)
AND l.po_header_id = jpt.po_header_id(+)
AND h.po_header_id = l.po_header_id(+)
AND jpt.tax_amount < > 0
AND h.type_lookup_code = 'STANDARD'
AND h.segment1 = :po_order
ORDER BY jpt.tax_line_no) aa
GROUP BY aa.order_no1, aa.tax_rate, aa.tax_name, aa.tax_line_no
ORDER BY tax_line_no
SELECT aa.order_no1,
aa.tax_rate, SUM (NVL (aa.tax_amount, 0)) tax_amount,
aa.tax_name, aa.tax_line_no
FROM (SELECT h.segment1 order_no1, jpt.line_location_id,
jpt.tax_rate tax_rate,
jpt.tax_amount,
RTRIM (NVL (jct.tax_descr, NULL)) tax_name,
jpt.tax_line_no
FROM po.po_headers_all h,
po_lines_all l,
jai_po_taxes jpt,
jai_cmn_taxes_all jct
WHERE jpt.tax_id = jct.tax_id(+)
AND jct.end_date IS NULL
AND l.po_line_id = jpt.po_line_id(+)
AND l.po_header_id = jpt.po_header_id(+)
AND h.po_header_id = l.po_header_id(+)
AND jpt.tax_amount < > 0
AND h.type_lookup_code = 'STANDARD'
AND h.segment1 = :po_order
ORDER BY jpt.tax_line_no) aa
GROUP BY aa.order_no1, aa.tax_rate, aa.tax_name, aa.tax_line_no
ORDER BY tax_line_no
No comments:
Post a Comment