SELECT
i.MillerAd8,i.AdYear4,(i.MillerAd8+i.AdYear4) AS FullAdNum,
i.PubNumber,i.PubName,i.ClientNum,i.InvoiceNumber,i.InsertDates,i.FirstInsDate,
i.LineType,i.RecordType,i.PriorInvNumber,i.InvoiceDate,i.InvoiceType,i.GrossAmount,a.CheckNumber,
h.NoBill,p.PrepayType,p.ChargeAmount,p.ChargeDate,p.FromAP,
a.CheckDate,a.PaidAmount,c.ClientName,
(SELECT SUM(z.GrossAmount) FROM "invhistd" z WHERE (z.RecordType='S') AND
                                                   (z.LineType='O') AND
                                                   (z.InvoiceNumber=i.InvoiceNumber) AND
                                                   (z.MillerAdNumber=FullAdNum) AND
                                                   NOT(z.AncillaryType IN (0,2,4,5,10,12,15))) AS MiscTotal

FROM "invhistd" i
LEFT OUTER JOIN "apdata" a ON ((i.MillerAd8=a.MillerAdNumber) AND (i.AdYear4=a.EntryYear))
LEFT OUTER JOIN "adheader" h ON ((i.MillerAd8=h.AdNumber) AND (i.AdYear4=h.EntryYear))
LEFT OUTER JOIN "ccads" p ON ((i.MillerAd8=p.AdNumber) AND (i.AdYear4=p.EntryYear))
LEFT OUTER JOIN "client" c ON (i.ClientNum=c.ClientNumber)
WHERE (i.invoicedate>=42005) AND (i.invoicedate<=42290) AND
      (i.LineType='S') AND (i.RecordType='S') AND (a.CheckNumber IS NULL)
      AND NOT(i.PubNumber IN ('0001','0002','0004','0005','0007','0011','0828','8160','8450','8509'))
ORDER BY i.PubNumber,i.ClientNum,i.FirstInsDate