FAR700 – GL Transfer Process

This batch report generates export data files that can then be imported into a third-party general ledger. It selects the financial transaction detail records that have not yet been exported and generates an export data file based on an interface mapping setup. The batch process also generates crystal reports that can be used for financial reconciliation and the identification of financial data integrity issues. This process can be used for all third party GL interfaces.

Before this process can be run, you must define the mapping of fields between Personify360 and your organization's GL application. For more information, please see Defining the GL Interface Mapping.

As 7.6.1, you can run the FAR700 to generate the multiple export data files so that you can import them into a third-party general ledger without a need for subsequent modification. To do so, the Split Export File By Company checkbox must be checked on the Interface Mapping Definition screen. If this checkbox is checked for the selected Setup Code, the system will generate the aggregate export data file with all of the records in addition to one or more export data files with each file only containing the financial transaction detail records for a single company without the duplication of a financial transaction detail record in multiple files.

The system identifies that a transaction has been exported to the GL by setting Far_Txn.GL_TXN_NO to a value greater than zero. When transactions are selected by FAR700 to be exported to the GL, all transaction detail records belonging to each selected Far_Txn transaction record are included in the export file. The GL file export also includes all Far_Txn_Detail records that are included in the report.

If a MASTER record is defined in the GL interface mapping, the FAR700 process will generate a unique GL_TXN_NO for each MASTER group during a single process run.

If a user filters on a value in Far_Txn_Detail, such as account number, the export file includes all Far_Txn_Detail records with the same FAR_TXN_NO as any selected Far_Txn_Detail record.

Reconciling Accounting Entries using the Account Summary Report

1.    The GL transfer process will produce an account summary report that contains the GL accounting entries.

2.    This report will be reviewed for account balances and compared to your general ledger system through the conversion date.  

a.    Matching and reconciling these numbers is the bulk of the baseline reconciliation process.  The general approach to reconciliation will be as follows.

i.      Many times your former system works differently from Personify360 from a financial perspective and it may be like “comparing apples to oranges.” The goal is to get it to be like an “apple to apple” comparison as much as possible.  

ii.     When the numbers don’t match, they will be divided into smaller groupings to determine if this is limited to a particular group or a pervasive discrepancy.

iii.   The next step is to review the detail and match the transactions one for one to determine if a pattern is detected. If there is a pattern, sometimes a script can be written to apply a change across the board.

iv.    Sometimes it will be necessary to make changes to Personify360 transactions one-by-one.

v.     For certain accounts, it just might be necessary to perform a reasonableness check on the amount.  A journal entry in your general ledger system to get the amounts in sync might then be necessary.

b.    Receivables

i.      The significant issue in the baseline reconciliation is whether there should be receivable balances and how much they should be.  Almost all associations accept partial payments even if it is an exception rather than a rule.  Being an accrual-based system, the choices in Personify360 are that there will be a receivable balance for the remainder of the order amount or the entire partial payment amount will go into prepaid liability. Therefore, there will typically be a balance in either the receivable account or the ppl account.

ii.     Some examples of a discrepancy in the receivable balance are as follows.

·            Erroneous payments in your old system.  For many clients, the reason you are changing systems is that the financial data from your old system was unreliable.  This may require a carefully review of payments from your former system or a business rule that can be applied to all order lines.

·            If your short pay setup is Reject, orders with partial payments should be converted with a status of Proforma (it is up to you to pass this status if you are converting with templates) for them to conform to the business rules you will be using in Personify360.  If they are converted as Active, the remaining balance will be in the receivable account.

c.    Prepaid liability

i.      The section on Accounts Receivable and Prepaid Liability in this workbook contains information on the reasons for a balance in the PPL account.

ii.     Unapplied receipts are typically used in instances where you do not know what the payment is for.  These types of payments usually wouldn’t be converted.  Therefore, the unapplied receipts account should not have a balance.

d.    Deferred balance

i.      The balance remaining in deferred after running the revenue recognition processes in Personify360 should match the remaining balance in the deferred account in your general ledger.

e.    Real income accounts

i.      These accounts will typically be reviewed for a reasonableness check depending upon how much history was converted.

3.    Run the GL Transaction Analysis (FAR504), ACCOUNT setup, for the particular account to be reconciled.  This will contain the detail transactions for that account through the conversion date.  The grand total should match to the account summary report from FAR700.  

4.    Review the details of the FAR504 run initially researching some of the order lines in Personify360 to determine if you recognize the problem.  If not, compare the details on this report to the report from your general ledger system.

Parameters

Exception Report Logic

GL Transfer is a process in which we are giving the journal entries from Personify360 to the external GL system. For some reason in case the transactions in Personify360 are not appropriate and does not meet some business/integrity rules, it is important that the system exclude such transactions from GL transfer. These transactions are reported in a report format giving details about what is wrong with the transaction.

Following are the possible exceptions:

Data File Generation Logic

GL transfer data files have data from following 3 tables.

1.    FAR_TXN

2.    FAR_TXN_DETAIL

3.    FGL_ACCOUNT_MASTER (to bring the name of account)

Basic flow for the process is to deal with the data in these 3 tables plus some constants provided by users either in the mapping or via process parameters discussed above.

However, the key is to arrange data in the desired format which can be in flat simple form or in some cases Master Detail form with a grouping i.e. by BATCH, TXN_DATE or Just by BATCH etc. The approach taken in the process is that it will always fetch data from these three tables via a view called FAR_GL_INTERFACE_VW.

This view will return Data from FAR_TXN and FAR_TXN_DETAIL tables joined on FAR_TXN_NO. The process will form an order by clause based on the Group Order specified for each column in the mapping. Thus fetching all the rows in the desired order.

The process then will loop through these rows one by one maintain the unique account and amount array. Thus, at the end of each grouping, the process will have one master row and multiple rows for unique accounts with the amount.

If there is no grouping mentioned, it is going to print the accounts for each FAR_TXN_NO level as this is the lowest grouping available for the accounts.

Select FT. *,FTD.FAR_TXN_NO FTD.ORG_IDFTD.ACCOUNT FTD.TXN_FUNCTION_CODEFTD.ORDER_NO FTD.ORDER_LINE_NOFTD.COMPANY_NUMBER FTD.BASE_AMOUNT   AS Base_AmountCase when FTD.base_amount < 0  then FTD.base_amount else 0 end as CR_BASE_AMOUNT,Case when FTD.base_amount > 0  then FTD.base_amount else 0 end as DR_BASE_AMOUNT,ACCT.Dscription FROMFAR_TXN FT, FAR_TXN_DETAIL FTD, FGL_ACCOUNT_MASTER ACCTWhere ft.far_txn_no = ftd.far_txn_no And      ftd.account = acct.account and       Exclude all records which are in exception

The process will add additional “Where” clause, as mentioned below.

Processing Logic

This process performs general validations to make sure all the parameters are proper (per definition in parameter section).

The process will check to see if there is an entry for the SETUP CODE given as parameter. If not, the process will report an error.

Select * from

FAR_INTERFACE_MAP_MASTER

WHERE SETUP_CODE  = Parameter value and Valid_flag = ‘Y’

Prepare Main Cursor

1.    Prepare Order by Clause:

2.    Select All  DB columns from  flg_map_interface_detail table order by grouping order.

3.    Make a real order by string using these columns separated by commas.

4.    If a DB column is supposed to be fetched using SUBstring include this column with substr or substring clause. Make sure this column is also included as is in the select list of columns.

5.    Continue with Main Cursor preparation and add the where clause, as mentioned below:
Select * from FAR_GL_INTERFACE_VW
Where
Posted_flag <> Null And TXN_DATE < = Cutoff Date

6.    And any additional Filter given in Parameter. Plus add following where clause as per the mode:

·            EDIT Mode: AND GL_TXN_NO IS NULL

·            REGENERATE Mode: AND GL_TXN_NO Between Start GL_TXN_NO and End GL_TXN_NO

7.    Order by the column list prepared in above step.

8.    See if one data file or two Data files are required.

9.    Select MASTER_DETAIL_FILE_CODE from FAR_INTERFACE_MAP_MASTER for the setup code.

a.    Select SPLIT_EXPORT_FILE_FLAG from FAR_INTERFACE_MAP_MASTER to determine if additional files should be generated for each company.

10.  If single, create one data file from the data of main cursor.

11.  If multiple, create two data file splitting the records for MASTER and DETAIL based on the RECORD_TYPE value.

12.  Open Main Cursor (Recordset).

13.  Hold the values of All columns used in ORDER by Clause – This becomes the Unique Group.

14.  Loop until Any of the value changes (I.e. the grouping changes).

UNIQUE Group Loop

1.    Read the Data Fields information for master record from MAP_DETAIL Table.

2.    Prepare an Array of Account and specified amount (i.e. Base Amount or CR/DR base Amount).
This array will hold sum of Amount for each account with in the group.

3.    IF Mode is PROD Generate GL_TXN_NO   - Once per Unique Group Only.

Though an update is shown here, make sure record gets updated through recordset at the very end of processing and rolls back if there is an error.

4.    Prepare one Record for the Master using Data + constants + Fixed value.

5.    Write a record in Master file

6.    Prepare multiple record using the Account array for DETAIL.

7.    Write the detail records.

8.    Set GL_TXN_NO of All of the above transaction = (The Next no generated above from sequence) (All transactions of the previous grouping) Continue until the end of main Cursor.

9.    Format the master record and detail record based on the mapping.
For example, if FAR_INTERFACE_MAP_DETAIL.FIELD_TYPE_CODE = DBFIELD then read the value from the voucher recordset, and create recordset for file master or detail record. Else if FAR_INTERFACE_MAP_DETAIL.FIELD_TYPE_CODE = FIXED then read FAR_INTERFACE_MAP_DETAIL.FIXED_VALUE and put the value as it is in the master or detail recordset. Else read FAR_INTERFACE_MAP_DETAIL.PARAMETER_CODE then read FAR_INTERFACE_MAP_DETAIL.PARAMETER_NAME and put the value as it is in the master or detail recordset.

10.  Feed the same data to Reports passing GL_TXN_NO if it is in PROD mode.

11.  If SPLIT_EXPORT_FILE_FLAG = ‘Y’, repeat steps 4 through 10 for each company that was selected as part of the initial query.

Sample Report

FAR700

FAR700_SUMMARY

FAR700_EXCEPTION