with poRct as (
select rctLine.POPRCTNM, rctLine.PONUMBER, rctLine.ITEMNMBR, rctLine.ITEMDESC, rctLine.UNITCOST, rctLine.EXTDCOST, rctLine.UOFM, poHdr.VENDORID, poHdr.CONFIRM1 from (
select POPRCTNM, PONUMBER, ITEMNMBR, ITEMDESC, UNITCOST, EXTDCOST, UOFM from POP30310
union all
select POPRCTNM, PONUMBER, ITEMNMBR, ITEMDESC, UNITCOST, EXTDCOST, UOFM from POP10310
) rctLine left outer join (
SELECT PONUMBER, VENDORID, CONFIRM1 FROM POP30100
UNION ALL
SELECT PONUMBER, VENDORID, CONFIRM1 FROM POP10100
) poHdr on (rctLine.PONUMBER = poHdr.PONUMBER)
),
glTrx as (
select 'Work' as Source, a.JRNENTRY, b.TRXDATE, a.ACTINDX, a.DEBITAMT, a.CRDTAMNT, a.ORMSTRID, a.ORDOCNUM from GL10001 a
LEFT OUTER JOIN GL10000 b ON (a.JRNENTRY = b.JRNENTRY)
UNION ALL
select 'Open' as Source, JRNENTRY, TRXDATE, ACTINDX, DEBITAMT, CRDTAMNT, ORMSTRID, ORDOCNUM FROM GL20000
UNION ALL
select 'Hist' as Source, JRNENTRY, TRXDATE, ACTINDX, DEBITAMT, CRDTAMNT, ORMSTRID, ORDOCNUM FROM GL30000
)
SELECT gl.Source, gl.JRNENTRY, gl.TRXDATE, a.ACTNUMST, b.ACTDESCR, c.ACCATDSC, gl.DEBITAMT, gl.CRDTAMNT, gl.ORMSTRID, gl.ORDOCNUM, po.CONFIRM1
FROM glTrx gl
LEFT OUTER JOIN poRct po ON (gl.ORMSTRID = po.VENDORID) AND (gl.ORDOCNUM = po.POPRCTNM)
LEFT OUTER JOIN GL00105 a ON (gl.ACTINDX = a.ACTINDX)
LEFT OUTER JOIN GL00100 b ON (gl.ACTINDX = b.ACTINDX)
LEFT OUTER JOIN GL00102 c ON (b.ACCATNUM = c.ACCATNUM)
You may also want to consider restricting your GL info to the purchasing series only.
