Defining general ledger processes

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.

Note: The examples in this chapter use notations specific to Oracle database and PL/SQL. If your EAM database is Microsoft SQL Server, please use the MS SQL Server equivalent.

To define general ledger processes:

Select Administration > Databridge > GL Process Definitions.
Click New Record.
Specify this information:
GL Process Definition

Specify 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

Specify 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

Specify 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

Specify a unique value identifying the ERP journal entry category for the feed reference group, e.g., trl_JECATEGORY. See your organization's financial manager for the correct value.

Journal Entry Source

Specify a unique value identifying the ERP journal entry source for the feed reference group, e.g., trl_JESOURCE. See your organization's financial manager for the correct value.

Summary

Select to include a summary for the general ledger process.

Set of Books ID

Specify the code identifying the ERP general ledger set of books for the feed reference group, e.g., tra_org. See 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

Specify 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

Specify 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

Specify 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

Specify 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, r5transactions.
Note: Do not include the word FROM in the text of the From Statement.

Where Statement
Specify 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
Specify 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.

See 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
Specify 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. See 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
UPDATE r5glinterface
SET gli_segment1 = r.company, 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.