Journal Entry Description - EPIC HB Interface

Legacy Contributor

We are building our general ledger interface from Epic HB to Infor Lawson S3.  Our current (non-Epic) interface provides some info from the source system that helps users to track down transaction detail on that system when they have a question.  Sample descriptions from the current system read: 

120117 DPRV PA Daily Jour

120217 DPRV PA Revenue Re

The people that we are working with haven't provided insight on what information from Epic might be useful in the Lawson GL Entry Description.  What are other customers using from Epic?  Is there any piece of data from Epic that might assist when an analyst is reviewing GL transactions?  Or is a simple "Epic Interface" description about as useful as it gets?

Thank you in advance!

dhuskey1

We use the simple "EPIC Interface" as our journal description and drill-back to the detail by concatenating GLTRANS.COMPANY, GLTRANS.ACCT_UNIT, GLTRANS.ACCOUNT, and GLTRANS.SUB_ACCOUNT into one string. We then join that to CLARITY.HSP_TRANSACTIONS on its GL_CREDIT_NUM and GL_DEBIT_NUM columns which allows us to see the detail behind the JE.



There have been occasions when the HB GL rules generate an invalid GLSTRING for GL_CREDIT_NUM and GL_DEBIT_NUM. These are typically caused by edge cases that we missed when building our rules.



When the file is produced at month-end, these invalid GLSTRINGs will appear on the GL165 error report. When they do, we correct the transaction on GL65 and place the delivered GLSTRING into the JE Description, allowing us to join the journal via description.



There's some sample code below that will allow you to tie your journal back to the HB detail.



SELECT glt.company,

glt.fiscal_year,

glt.acct_period,

glt.control_group,

glt.r_system,

glt.je_type glt.je_sequence,

glt.line_nbr,

glt.acct_unit,

glt.account,

glt.sub_account,

glt.tran_amount,

ht.tx_id,

CASE ht.tx_type_ha_c

WHEN 1 THEN

'Charge'

WHEN 2 THEN

'Payment'

WHEN 3 THEN

'Debit Adj'

WHEN 4 THEN

'Credit Adj'

ELSE

'Undefined'

END AS tran_type,

ht.hsp_account_id,

ht.gl_credit_num,

ht.gl_debit_num,

ht.procedure_desc,

ht.quantity,

ht.tx_amount

FROM lawson.gltrans glt

INNER JOIN clarity.hsp_transactions@your_clarity_server ht

ON CASE

WHEN glt.description NOT LIKE 'E%' THEN

--GLSTRING did not error on GL165, use the GLSTRING from the JE

lpad(glt.company, 4, '0') || TRIM(glt.acct_unit) ||

lpad(glt.account, 5, '0') || '-' lpad(glt.sub_account, 4, '0')

ELSE

--GLSTRING errored on GL165, use the original GLSTRING

TRIM(glt.description)

END IN (ht.gl_credit_num, ht.gl_debit_num)

AND ht.tx_post_date BETWEEN

to_date(glt.fiscal_year * 100 + glt.acct_period, 'YYYYMM') AND

last_day(to_date(glt.fiscal_year * 100 + glt.acct_period, 'YYYYMM'))

WHERE glt.company > 0

AND glt.fiscal_year = 2017

AND glt.acct_period = 12

AND glt.control_group > 0

AND glt.r_system = 'HB'

AND glt.je_type = 'I'

AND glt.je_sequence > -1

AND glt.line_nbr > -1

AND glt.r_status = 9

Wichtige Lösungen in der Wissensdatenbank Februar 2012.pdf