Oh yeah, I forgot to say how much Trans4M makes me want to pull my hair out. Ahh well, at least I'm writing good reports (that they would charge $600 for, but I'm donating to the public for free). Yes, that's Trans4M the ERP system that blows.

Anybody have a better way to optimize this query?

SELECT
V_AR_TRANS_PART.CUST_PART_NUM,
MAX(V_AR_TRANS_PART.CUST_PART_NUM_DESC) PART_DESC,
SUM(V_AR_TRANS_PART.EXT_AMT) EXT_AMT,
SUM(V_AR_TRANS_PART.QTY) QUANTITY,
V_AR_TRANS_HDR.CUST_SHIPTO,
V_AR_TRANS_HDR.FCS_CUST_NUM,
AVG(V_AR_TRANS_HDR.FUNC_EQUIV) EXCHANGE_RATE,
V_AR_TRANS_HDR.SHIPTO_CITY,
MAX(V_AR_TRANS_HDR.AR_TRANS_NUM) INVOICE_NUMBER,
(V_AR_TRANS_HDR_TAX_DATA.TAX_PCT * 1) TAXABLE_RATE
from
V_AR_TRANS_PART,
V_AR_TRANS_HDR,
V_AR_TRANS_HDR_TAX_DATA
WHERE
V_AR_TRANS_PART.CREATE_DATE >= ?
AND V_AR_TRANS_PART.CREATE_DATE <= ?
AND HDR_KEY NOT LIKE 'C%'
AND V_AR_TRANS_PART.HDR_KEY = V_AR_TRANS_HDR.AR_TRANS_NUM
AND V_AR_TRANS_PART.HDR_KEY = V_AR_TRANS_HDR_TAX_DATA.KEY_ID
GROUP BY
V_AR_TRANS_PART.CUST_PART_NUM,
V_AR_TRANS_HDR.CUST_SHIPTO,
V_AR_TRANS_HDR.FCS_CUST_NUM,
V_AR_TRANS_HDR.FUNC_EQUIV,
V_AR_TRANS_HDR.SHIPTO_CITY,
V_AR_TRANS_HDR_TAX_DATA.TAX_PCT
UNION
SELECT
V_AR_TRANS_PART.CUST_PART_NUM,
MAX(V_AR_TRANS_PART.CUST_PART_NUM_DESC) PART_DESC,
SUM(V_AR_TRANS_PART.EXT_AMT * -1) EXT_AMT,
SUM(V_AR_TRANS_PART.QTY) QUANTITY,
V_AR_TRANS_HDR.CUST_SHIPTO,
V_AR_TRANS_HDR.FCS_CUST_NUM,
AVG(V_AR_TRANS_HDR.FUNC_EQUIV) EXCHANGE_RATE,
V_AR_TRANS_HDR.SHIPTO_CITY,
MAX(V_AR_TRANS_HDR.AR_TRANS_NUM) INVOICE_NUMBER,
(V_AR_TRANS_HDR_TAX_DATA.TAX_PCT * 1) TAXABLE_RATE
from
V_AR_TRANS_PART,
V_AR_TRANS_HDR,
V_AR_TRANS_HDR_TAX_DATA
WHERE
V_AR_TRANS_PART.CREATE_DATE >= ?
AND V_AR_TRANS_PART.CREATE_DATE <= ?
AND HDR_KEY LIKE 'C%'
AND V_AR_TRANS_PART.HDR_KEY = V_AR_TRANS_HDR.AR_TRANS_NUM
AND V_AR_TRANS_PART.HDR_KEY = V_AR_TRANS_HDR_TAX_DATA.KEY_ID
GROUP BY
V_AR_TRANS_PART.CUST_PART_NUM,
V_AR_TRANS_HDR.CUST_SHIPTO,
V_AR_TRANS_HDR.FCS_CUST_NUM,
V_AR_TRANS_HDR.FUNC_EQUIV,
V_AR_TRANS_HDR.SHIPTO_CITY,
V_AR_TRANS_HDR_TAX_DATA.TAX_PCT
UNION
Select
V_AR_TRANS_MISC.ITEM_NUM,
MAX(V_AR_TRANS_MISC.ITEM_DESC) PART_DESC,
SUM(V_AR_TRANS_MISC.ITEM_PRICE * V_AR_TRANS_MISC.ITEM_QTY) EXT_AMT,
SUM(V_AR_TRANS_MISC.ITEM_QTY) QUANTITY,
V_AR_TRANS_HDR.CUST_SHIPTO,
V_AR_TRANS_HDR.FCS_CUST_NUM,
AVG(V_AR_TRANS_HDR.FUNC_EQUIV) EXCHANGE_RATE,
V_AR_TRANS_HDR.SHIPTO_CITY,
MAX(V_AR_TRANS_HDR.AR_TRANS_NUM) INVOICE_NUMBER,
(V_AR_TRANS_HDR_TAX_DATA.TAX_PCT * 1) TAXABLE_RATE
FROM
V_AR_TRANS_MISC,
V_AR_TRANS_HDR,
V_AR_TRANS_HDR_TAX_DATA
WHERE
V_AR_TRANS_MISC.REC_CREAT_DATE >= ?
AND V_AR_TRANS_MISC.REC_CREAT_DATE <= ?
AND V_AR_TRANS_MISC.HDR_KEY NOT LIKE 'C%'
AND V_AR_TRANS_MISC.HDR_KEY = V_AR_TRANS_HDR.AR_TRANS_NUM
AND V_AR_TRANS_MISC.HDR_KEY = V_AR_TRANS_HDR_TAX_DATA.KEY_ID
GROUP BY
V_AR_TRANS_MISC.ITEM_NUM,
V_AR_TRANS_HDR.CUST_SHIPTO,
V_AR_TRANS_HDR.FCS_CUST_NUM,
V_AR_TRANS_HDR.FUNC_EQUIV,
V_AR_TRANS_HDR.SHIPTO_CITY,
V_AR_TRANS_HDR_TAX_DATA.TAX_PCT
UNION
Select
V_AR_TRANS_MISC.ITEM_NUM,
MAX(V_AR_TRANS_MISC.ITEM_DESC) PART_DESC,
SUM(V_AR_TRANS_MISC.ITEM_PRICE * V_AR_TRANS_MISC.ITEM_QTY * -1) EXT_AMT,
SUM(V_AR_TRANS_MISC.ITEM_QTY) QUANTITY,
V_AR_TRANS_HDR.CUST_SHIPTO,
V_AR_TRANS_HDR.FCS_CUST_NUM,
AVG(V_AR_TRANS_HDR.FUNC_EQUIV) EXCHANGE_RATE,
V_AR_TRANS_HDR.SHIPTO_CITY,
MAX(V_AR_TRANS_HDR.AR_TRANS_NUM) INVOICE_NUMBER,
(V_AR_TRANS_HDR_TAX_DATA.TAX_PCT * 1) TAXABLE_RATE
FROM
V_AR_TRANS_MISC,
V_AR_TRANS_HDR,
V_AR_TRANS_HDR_TAX_DATA
WHERE
V_AR_TRANS_MISC.REC_CREAT_DATE >= ?
AND V_AR_TRANS_MISC.REC_CREAT_DATE <= ?
AND V_AR_TRANS_MISC.HDR_KEY LIKE 'C%'
AND V_AR_TRANS_MISC.HDR_KEY = V_AR_TRANS_HDR.AR_TRANS_NUM
AND V_AR_TRANS_MISC.HDR_KEY = V_AR_TRANS_HDR_TAX_DATA.KEY_ID
GROUP BY
V_AR_TRANS_MISC.ITEM_NUM,
V_AR_TRANS_HDR.CUST_SHIPTO,
V_AR_TRANS_HDR.FCS_CUST_NUM,
V_AR_TRANS_HDR.FUNC_EQUIV,
V_AR_TRANS_HDR.SHIPTO_CITY,
V_AR_TRANS_HDR_TAX_DATA.TAX_PCT

I knew I should have updated the rest of my trip story. Long story short, San Diego is boring, saw Jimmy Kimmel live and San Diego is boring. One day I'll finish off that post...one day.