SELECT
GL.JRNENTRY,
G5.ACTNUMST,
CASE WHEN G0.ACCTTYPE = 1 THEN 'Posting Account'
WHEN G0.ACCTTYPE = 2 THEN 'Unit Account'
WHEN G0.ACCTTYPE = 3 THEN 'Posting Allocation Account'
WHEN G0.ACCTTYPE = 4 THEN 'Unit Allocation Account'
ELSE '' END AS AccountType,
CASE WHEN G0.PSTNGTYP = 0 THEN 'Balance Sheet'
ELSE 'Profit and Loss' END AS PostingType,
G2.ACCATDSC AS AccountCategory,
GL.OPENYEAR AS Year,
GL.PERIODID,
GL.DEBITAMT,
GL.CRDTAMNT,
GL.ORTRXTYP AS OriginatingTRXType,
GL.TRXDATE,
G0.ACTDESCR AS AccountDescription,
GL.REFRENCE AS Reference,
GL.DSCRIPTN AS Description,
GL.SOURCDOC AS SourceDocument,
GL.ORMSTRID AS OriginatingMasterID,
GL.ORMSTRNM AS OriginatingMasterName,
GL.ORDOCNUM AS OriginatingDocumentNumber,
GL.LASTUSER AS UserWhoPosted
FROM GL20000 GL
JOIN GL00105 G5 on G5.ACTINDX = GL.ACTINDX
JOIN GL00100 G0 on G0.ACTINDX = GL.ACTINDX
LEFT JOIN GL00102 G2 ON G2.ACCATNUM = G0.ACCATNUM