Defining general ledger processes
Define general ledger processes to export transaction data to an external ERP general ledger and create journal entries in the corporate accounting system for the processes defined.
Define parameters to enable general ledger processes to dynamically construct an SQL select statement to transfer transaction information from Infor EAM to an ERP application. Dynamic SQL enables you to configure the selection and processing of Infor EAM transactions based on the needs of your organization.
To define general ledger processes:
- Open the GL Process Definition form. The system displays the List View page.
- Click New Record. The system inserts a new record and displays the Record View page.
-
GL Process
Definition—Enter a unique code identifying the GL process
definition, and then enter a description in the adjacent field, e.g., DS
ISSUES.
Note: The process you are creating should determine the unique code you enter. Commonly, the unique code refers to the combination of the process and group. For example, if you are creating an issue from store process for the GVL store, enter ISSUE_GVL.
-
Row Identity—Enter a unique record identifier
for the row in the source database table for the general ledger process, e.g.,
r5translines.ROWID.
Note: You must use the token row identifier ROWID to identify the source row in the database table.
-
Scheduling Group—Enter the table column name
identifying the grouping value for the general ledger process, e.g. TRL_TYPE.
Note: Scheduling groups often share relevant common values that are used in building the Where Statement for the process definition. Scheduling Group is also used on the GL References form to establish the financial application changes within the group.
- Journal Entry Category—Enter a unique value identifying the ERP journal entry category for the feed reference group, e.g., trl_JECATEGORY. Refer to your organization's financial manager for the correct value.
- Journal Entry Source—Enter a unique value identifying the ERP journal entry source for the feed reference group, e.g., trl_JESOURCE. . Refer to your organization's financial manager for the correct value.
- Summary—Select to include a summary for the general ledger process.
-
Set of Books ID—Enter the code identifying the
ERP general ledger set of books for the feed reference group, e.g.,
tra_org. Refer to your organization's financial
manager for the correct code.
If you use multiple sets of books, there must be a relationship between the process definition and the reference group that classifies the correct set of transactions.
- Date—Enter the date to use for the general ledger journal entries. You can enter either the date of the original transaction, e.g., TRL_DATE, the date of the transaction transfer, e.g., SYSDATE, or the date identifying the end of the financial period for the transaction.
-
Amount—Enter the calculation for the monetary
value of the transaction, e.g.,
TRL_PRICE* TRL_QTY or
TRL_PRICE*TRL_QTY*1.15 if an overhead factor is used
in costing.
Note: Use positive and negative values as necessary.
-
Segments—Enter the account code segments for
the journal entries. If you have multiple segments activated on the ERP
Accounting Definition, you must enter all the segments in this field, e.g.,
ACD_SEGMENT1, ACD_SEGMENT2, ACD_SEGMENT3,
ACD_SEGMENT4,ACD_SEGMENT5, ACD_SEGMENT6,ACD_SEGMENT7.
Note: If you enter more than one segment, do not include conjunctive statements such as "and."
-
From Statement—Enter the Infor EAM source
table(s) required to meet all of the process definition constraints. You must
enter at least the
R5ACCOUNTDETAIL table and one transaction table,
e.g.:
r5translines, r5accountdetail, r5transactionsNote: Do not include the word FROM in the text of the From Statement.
-
Where Statement—Enter the "where" condition
clause. The "where" condition clause is dependent on the process definition.
You must enter constraints for the join statement of all tables listed and for
the values for XXX_GLTRANSFER and XXX_GLTRANSFERFLAG, which are used to prevent
records from being submitted more than once, e.g.:
trl_acd = acd_code AND NVL(trl_gltransferflag, '-' ) = '-' AND tra_code = trl_trans AND tra_rstatus = 'A' and tra_type = 'I' and trl_type = 'I' and trl_event is not null and trl_QTY >0
The XXX_GLTRANSFER and XXX_GRTRANSFERFLAG columns are updated using the Source Update Statement. Therefore, you should limit the usage of the Source Update Statement within the Where Statement condition clause. For example, for a basic Where Statement for an issuance process that requires R5TRANSLINES and R5ACCOUNTDETAIL, you would enter trl_acd = acd_code and nvl(trl_gltransferflag,'+') != '+' and trl_rtype = 'I'.
Note: Do not use a semicolon as the terminator or include the word WHERE in the text of the Where Statement. -
Source Update Statement—Enter an SQL statement
to update the row in the transaction source table.
This statement flags a transaction record as processed so that it is not processed again. You must include the token :ROWID or :rowid in the statement.
Refer to the following example of a source update statement that flags a transaction record as having been processed so that it is not processed again.
UPDATE r5translines SET trl_gltransferflag = '+', trl_gltransfer = sysdate WHERE rowid = :rowid
-
Destination Update Statement—Enter an SQL
statement to update the
R5GLINTERFACE table to
contain the reference information required on the journal import.
The Destination Update Statement populates reference fields in the R5GLINTERFACE table with custom information required for the installation. For example, include the stock code, description, and work order number for an inventory issue transaction in a reference field. This reference information is then included in the journal entry.
Refer to the following example of a destination update statement that uses two tokens. One is :transid, which references the appropriate row in R5GLINTERFACE. The second is :rowid, which references the transaction source row identifier. The transaction source row identifier is used to retrieve additional transaction reference information.
Declare
Cursor C1 is
SELECT substr(v.PRV_VALUE,1,3) company, substr(v.PRV_VALUE,5,4) GLS, substr(v.PRV_VALUE,10,5) DEP,
substr(v.PRV_VALUE,16,6) EXP, substr(v.PRV_VALUE,23,4) PL , substr(v.PRV_VALUE,28,3) IC, substr(v.PRV_VALUE,32,6) FU
from
R5TRANSLINES L,
R5PROPERTYVALUES V
where l.rowid = :rowid
and v.prv_property (+) = 'INVOFF'
and v.PRV_RENTITY (+) = 'STOR'
and v.PRV_CODE (+) = l.trl_store;
BEGIN
FOR r IN C1 LOOP
SET gli_segment1 = r.company,
UPDATE r5glinterface
gli_segment2 = r.GLS,
gli_segment3 = r.DEP,
gli_segment4 = r.EXP
WHERE gli_transid = :transid
and gli_segment2 = '****' ;
END LOOP;
end;
- Click Save Record. The system saves the record.