You can use the notes for GL entries. I do this for initial implementation of trial balance detail with respect to documentation, or when performing GL sensitive entries such as those for working capital adjustments, etc...
The note field is not something that I would put into a trial balance because your account balances will be aggregated, but a detail GL TB is definitely doable.
The caveat is there are no default GP reports that I know of which give you this information with each transaction. To get around this, I had to write my own GL JE report to replace the default Cross Reference report. Below is the SQL script for it. Yes, you can use this in SSRS reports which is what I would recommend.
select
gl.JRNENTRY 'Journal Entry',
gl.TRXDATE 'Transaction Date',
gl.FISCALYEAR 'Fiscal Year',
case
when len(rtrim(convert(char(2), gl.PERIODID))) = 1 then '0' + rtrim(convert(char(2), gl.PERIODID))
else rtrim(convert(char(2), gl.PERIODID))
end 'Fiscal Period',
rtrim(gl.ORGNTSRC) 'Orig Transaction Source',
rtrim(gl.ORCTRNUM) 'Orig Control Number',
rtrim(gl.ORMSTRID) 'Orig Master Id',
rtrim(gl.ORMSTRNM) 'Orig Master Name',
rtrim(gl.ORDOCNUM) 'Orig Document Number',
rtrim(gl.REFRENCE) 'Reference',
rtrim(gl.DSCRIPTN) 'Description',
rtrim(a3.ACTNUMST) 'Account',
rtrim(a1.ACTDESCR) 'Account Description',
rtrim(a2.ACCATDSC) 'Account Category',
rtrim(a3.ACTNUMBR_1) 'Segment 1',
rtrim(a3.ACTNUMBR_2) 'Segment 2',
rtrim(a3.ACTNUMBR_3) 'Segment 3',
rtrim(a3.ACTNUMBR_4) 'Segment 4',
rtrim(gl.USWHPSTD) 'Posting User',
case gl.SERIES
when 1 then 'All'
when 2 then 'Financial'
when 3 then 'Sales'
when 4 then 'Purchasing'
when 5 then 'Inventory'
when 6 then 'Payroll'
when 7 then 'Project'
when 10 then '3rd Party'
end Series,
rtrim(gl.CURNCYID) Currency,
gl.ORDBTAMT 'Orig DR',
gl.ORCRDAMT 'Orig CR',
case when gl.EXCHDATE = '1900-01-01 00:00:00.000' then GETDATE() else gl.EXCHDATE end 'FX Date',
case when gl.XCHGRATE = 0 then 1 else gl.XCHGRATE end 'FX Rate',
gl.DEBITAMT 'DR',
gl.CRDTAMNT 'CR',
isnull(note.TXTFIELD, '') 'Notes'
from (
select
o.JRNENTRY,
o.TRXDATE,
o.OPENYEAR FISCALYEAR,
o.PERIODID,
o.ORGNTSRC,
o.ORCTRNUM,
o.ORMSTRID,
o.ORMSTRNM,
o.ORDOCNUM,
o.REFRENCE,
o.DSCRIPTN,
o.ACTINDX,
o.USWHPSTD,
o.SERIES,
o.CURNCYID,
o.ORDBTAMT,
o.ORCRDAMT,
o.EXCHDATE,
o.XCHGRATE,
o.DEBITAMT,
o.CRDTAMNT,
o.NOTEINDX
from GL20000 o
union all
select
h.JRNENTRY,
h.TRXDATE,
h.HSTYEAR FISCALYEAR,
h.PERIODID,
h.ORGNTSRC,
h.ORCTRNUM,
h.ORMSTRID,
h.ORMSTRNM,
h.ORDOCNUM,
h.REFRENCE,
h.DSCRIPTN,
h.ACTINDX,
h.USWHPSTD,
h.SERIES,
h.CURNCYID,
h.ORDBTAMT,
h.ORCRDAMT,
h.EXCHDATE,
h.XCHGRATE,
h.DEBITAMT,
h.CRDTAMNT,
h.NOTEINDX
from GL30000 h
) gl
left join SY03900 note on gl.NOTEINDX = note.NOTEINDX
inner join GL00100 a1 on gl.ACTINDX = a1.ACTINDX
inner join GL00102 a2 on a1.ACCATNUM = a2.ACCATNUM
inner join GL00105 a3 on gl.ACTINDX = a3.ACTINDX
Share
Report
All responses (
Answers (
2,895
