EFT Reconciler - Financials (Accounts Receivable) - P12

INDEX

• 01. OVERVIEW
• 02. RULES AND DEFINITIONS
• 03.[ ROUTINES AND FEATURES |ConciliadorTEF-Financeiro(ContasaRecebe]
• 04. PARAMETERS
• 05. ENTRY POINTS
• 06. Tables

01. OVERVIEW

This process aims to reconcile sales records and/or payments made through card (debit and/or credit) companies, with their respective bills generated in the Protheus Financials Module.
The files sent by the card companies are imported and and processed by the import routine, ensuring data integrity for later reconciliation.
After importing we can reconcile the transactions, in accordance with the automatic rules. We can make manual adjustments, if needed.
The reconciliation is divided in two simplified stages.

...

Still, there are reports for checking import criticisms and following up on the reconciliations made. 
 
Note:
For bills natively generated by the SIGALOJA module, no adjustment is needed. For bills generated by other modules or customized in Financials, the NSU (field E1_NSUTEF) and Authorization Code (field E1_CARTAUT) data are required for the routine to run correctly. Besides this information, depending on system parameter settings (MV_USAMEP), the table SITEF Installments (MEP) must also be updated.
This feature is available from the version 12.1.27.

02. RULES AND DEFINITIONS

Operational Flow 

Description of processes

• Make sale:  Through the source module, the system records the sales transactions, allowing even the use of multiple cards for the payment.
• Integration with Financials:  Generation of bills receivable of respective sales, with specific transaction information: NSU Code - Exclusive Sequential Number of the operation and Authorization Code of sales operation.   
• Receive file: Receipt of files generated by card companies.
• Make file available in folder: Saving of files in specific directories:

...

Utilization Rules and good practices.

• Sharing of tables: The sharing of tables SE1 and MEP must be identical. You may or may not use table MEP for data reconciliation. In which we also have the Identification field between tables FIF and SE1 (FIF->NSUTEF/SE1->NSUTEF)

...

• Record 016 - Summary of sales in installments IATA- Record 017 - AVS- Record 018 - Certificate of sale in installments IATA- Record 019 - Serasa- Record 020 - Installment of transaction in installments IATA- Record 021 - SecureCode- Record 022 - Summary of sales in USD- Record 024 - Certificate of sale in USD- Record 033 - Notification of Request (E-Commerce)- Record 034 - Rotating CV/NSU (E-Commerce)- Record 035 - Interest Free CV/NSU In Installments (E-Commerce)- Record 040 – Serasa – Identifies Serasa query details- Record 041 – AVS – Identifies AVS query details- Record 042 – SecureCode – Identifies SecureCode query details- Record 044 – Debits Pending- Record 045 – Cleared Debits- Record 049 – Unscheduling of Installments with complementary information- Record 053 – Net Adjustments and Unscheduling (E-Commerce)- Record 054 – Debit Adjustments (via Bank) - (E-Commerce)- Record 055 – Debits Pending (E-Commerce)- Record 056 – Cleared Debits (E-Commerce)- Record 057 – Unscheduling of Installments with complementary information (E-Commerce)- Record 058 – Gateway- Record 059 – Bank Bill- Record 060 – Risk Analysis- Record 061 - Manual Review
  Layout: - Documentation Version approved up to this point: 13 and 14
  Support documents:   CIELO Extrato Eletronico - Manual - Versão 13.pdf
  Cielo_Comunicado Extrato EDI_Marco 2017.pdf
  CIELO Extrato Eletronico - Manual - Versao 14.pdf
  Records not processed by reconciler: 
  07 - ASSIGNMENT OF RECEIVABLES08 - INSTALLMENTS PENDING09 - BALANCE PENDING

  03. ROUTINES AND FEATURES FEATURES 

   
  Justification - EFT - FINA911
  Enables the addition of standardized justifications to be used on the reconciliation screen. 
   
  Enter a code and a description. 
   
  The justification will be required when making effective the reconciliation processes of tabs: Partially Reconciled, Bill without Sale, Sales Without Bill and Divergent. 
  Adjustments Reasons - EFT - FINA912
  Enables the addition of standardized reasons for utilization in divergences tab of reconciliation routines. 
   
  Enter company, a code and a description for this reason.
   
  Reasons are documented in the manuals of each company.
  Branches to Import - FINA913
  It enables only some branches to use the reconciliation process. Use it directly in the file import process. 
   
  Enter the code of the branch enabled to import the files. 
   
   
  If you do not register any branch in this option, all branches will be enabled to import the file. When you use the register, the import process will only be completed successfully for the branches registered in it.
  If you do not register a branch and find a record for it, the import log will inform that the branch is not enabled for import.  
  Import Wizard - FINA914
  The file import process considers only layouts approved for Software Express, Cielo, Rede and Amex. This process ensures data integrity, storing criticisms for users to analyze and take measures.
  Premises

...

Correspond to imported sales the counterparts of which (financial bills in Protheus) were found in accordance with the relationship key, Branch, Establishment, NSU, installment, issue date, authorization code, type (Debit/Credit) and tolerance percentage (the tolerance percentage is set in routine parameters).
Tolerance Percentage: Consider that the net sale value must be greater than or equal to the bill balance minus the specified percentage. 
Example:  Sale

Use the following calculation to define whether to fully reconcile it. 
Net Value >= Balance - ( Balance * Percentage) 
198 >= 195 - (195 * 10%) 
198 >= 195 - 19.5 
198 >= 175.5  
 
 
 
The upper grid shows sales imported through the issuer file, the lower grid shows data from the bill found in the system. The records already appear selected to make the reconciliation effective. You can select item by item or use option "Select All" to change the current selection. 

...

Enter the reconciliation type already done to change the status in table, so this record is no longer displayed in future reconciliations.
Correspond to imported sales the counterparts of which (financial bills in Protheus) were found in accordance with the relationship key, Branch, Establishment, NSU, installment, issue date, authorization code, type (Debit/Credit) although "outside" the set tolerance percentage.
Tolerance Percentage: Consider that the net sale value must be smaller than the bill balance minus the specified percentage. 
Example:  Sale

Use the following calculation to define whether to fully reconcile it. 
Net Value >= Balance - ( Balance * Percentage) 
198 < 225 - (225 * 10%) 
198 < 225 - 22.5 
198 < 202.5
 
 
 
The upper grid shows sales imported through the issuer file, the lower grid shows data from the bill found in the system. The records already appear selected to make the reconciliation effective. You can select item by item or use option "Select All" to change the current selection. 

...

To correctly control the reconciliation, enter a justification code and a description so you can continue the activation. (you may create the justifications register according to company needs) 
 
Enter type of reconciliation made and save the justification data of said reconciliation, considering it was made outside regular parameters. This update ensures this record is no longer displayed in future reconciliations.
Totals: display quantity of records with gross and net values in BRL by date for each tab.
 
 
 
The process of reconciliation activation only labels the sale as reconciled, no longer showing this sale in future reconciliations. 

Unreconcile 

The process removes the reconciliation data from the sales record, allowing the sale to be reconciled in future processes.  
This process can only be executed if the payment reconciliation has not been executed.
 
 
Reconciliation Report - FINR916 
The reconciliation report shows sales in detail with their corresponding reconciliation "statuses".
Report Parameters: 
 
*Issuer?* Select the issuer.
1 – Soft. Express.2 – Others (Amex, Cielo, Rede).
*Card Brand – Software Express?* Select the Card Brand to filter Software Express data. Use the \[F3\] key to query the Card Brand register.                                                             
*Card Company – Others ?* Select the Brand to filter data from other card companies (Amex, Cielo, Rede). Use the \[F3\] key to query the Card Company register.
*Select Branches?* Select option "Yes" to filter branches for the report or "No" to display records from the branch logged.
*From Customer?* Select the initial code of the range of customers to be considered for the report generation. Use the \[F3\] key to query the Customers register.
*From Store?* Select the initial code of the range of customer stores to be considered for the report generation.
 
*From Customer?* Select the final code of the range of customers to be considered for report generation. Use the \[F3\] key to query the Customers register.
*To Store?* Select the initial code of the range of customer stores to be considered for the report generation.
*From NSU No.?* Select the initial NSU code to generate the report.
*To NSU No.?* Select the final NSU code to generate the report.
*Credit Start Date?* Select the start date of credit generate the report.
*Credit End Date?* Select the end date of credit to generate the report.
 
*Paymt Reconciliation Status?* Select the payment reconciliation statuses to be considered for report generation. Use the \[F3\] key to query the rules.
*From Paymt Reconciliation Date?* Select the payment reconciliation start date to be considered for report generation. Sales records (FIF) will only contain this date after the payment reconciliation becomes effective.
*To Paymt Reconciliation Date?* Select the payment reconciliation end date to be considered for report generation. Sales records (FIF) will only contain this date after the payment reconciliation becomes effective.
*Sales Reconciliation User?* Select the user who executed the sales reconciliation for report generation. Sales records (FIF) will only contain this date after the sales reconciliation becomes effective. Leave the field blank (empty) if you want all users.
 
*From Sales Reconciliation Date?* Select the payment reconciliation start date to be considered for report generation. Sales records (FIF) will only contain this date after the sales reconciliation becomes effective.
*To Sales Reconciliation Date?* Select the payment reconciliation end date to be considered for report generation. Sales records (FIF) will only contain this date after the sales reconciliation becomes effective.
*Sales Reconciliation User?* Select the user who executed the sales reconciliation for report generation. Sales records (FIF) will only contain this date after the sales reconciliation becomes effective.
*Print Justification?  * Select option "Yes" to print reconciliation justifications in report, if filled out at reconciliation activation.  Select "No" to not show the justification.
*Print Reconciliation Data?* Select the sales reconciliation start date to be considered for report generation. Sales records (FIF) will only contain this date after the sales reconciliation becomes effective.
*Printing Order?* Select the printing order to show in the report. Records ordered by:
1 – Recon. / NSU =   Reconciliation Order2 – NSU / Recon.=   NSU Prder
 
 
 
 
 
Import Log - FINA917 
*Import Log* 
After you finish importing the files through routine Wizard (FINA914), the system allows you to evaluate records disregarded in the import through routine Import Logs "EFT (FINA917)".
The possible statuses of each import are:
* Caption*
* *

• Successfully Imported - All file lines read, and the sales/payment records successfully created.
• File already imported - An import of a file of same name has already been attempted. 
• Incompatible file version - File layout version not approved for import.
• Error when opening file - Unable to read file in configured directory. 
• Partially imported - At least one sales/payment record was successfully imported.
• Inconsistent File - Only criticisms regarding SERASA are found in file.
• File Not Imported - No sales/payment record has been imported

...

From Branch? : Initial branch to search sales.
To Branch? : Final branch to search sales. 
From Credit Date: Start Date of sale reconciliation. (evaluate the issue date of sale).
To Credit Date: End Date of sale reconciliation. (evaluate the issue date of sale). 
From NSU No?: NSU Number (Single Sequential Number) that starts the sale reconciliation.  (with zeros to the left). 
To NSU No.?: NSU Number (Single Sequential Number) that ends the sale reconciliation. (with zeros to the left). 
 
Generation?: Individual - Manually posts bill by bill.  By Batch - Automatically posts by batch of bills.
Type?: Select the type to be reconciled. Options: Debit/Credit or Both.
Search Prev Days?: Subtract days from start of selected search. Example: Credit Date 8/20/2018 with this option equal to 2, sales are selected from 8/18/2018.
Tolerance in %?: Percentage of the difference in the bill balances between the net sale value of the imported file and the remaining bill balance in the system
From Financial Company?: Code of initial financial company for the filter. 
To Financial Company?: Code of final financial company for the filter. 
 
 
 
Posting Date?: Set the date of bill posting. Base Date - day of reconciliation execution and SITEF Credit - Credit date sent in file. 
Validate NSU f/ Not Rec.?: Validate in tab "Not Reconciled" whether the relationship between a sale and a bill in financials must have the same NSU.
 
 
Select Branches: You can select one or more branches. The field is multiple choice for reconciliation of multiple branches.
From Credit Date: Start Date of sale reconciliation. (evaluate the issue date of sale)  
To Credit Date: End Date of sale reconciliation. (evaluate the issue date of sale) 
From NSU No?: NSU Number (Single Sequential Number) that starts the sale reconciliation.  (with zeros to the left) 
To NSU No.?: NSU Number (Single Sequential Number) that ends the sale reconciliation. (with zeros to the left) 
Generation?: Individual - Manually posts bill by bill.  By Batch - Automatically posts by batch of bills. 
 
 
 
Type: Select the type to be reconciled. Options: Debit/Credit or Both.
Search Prev Days?: Subtract days from start of selected search. Example: Credit Date 8/20/2018 with this option equal to 2, sales are selected from 8/18/2018.
Tolerance in %: Percentage of the difference in the bill balances between the net sale value of the imported file and the remaining bill balance in the system
Card Company: Click the "magnifying glass" icon to display card companies. You may select one or more card companies to reconcile sales.
Posting Date? : Set the date of bill posting. Base Date - day of reconciliation execution and SITEF Credit - Credit date sent in file. 
Validate NSU f/ Not Rec.? : Validate in tab "Not Reconciled" whether the relationship between a sale and a bill in financials must have the same NSU. 
 
 
Processing Type?: Option created for you to choose the data processing type (by tab →  display records of tab selected only) or (General → display records of all tabs) 
Tab for Processing?: Tab to be used along with previous parameter. (1-Reconciled / 2-Partially Reconciled / 3-Bills Without Sales / 4-Sales Without Bills / 5-Divergent)
 
 
 

...

• Reconciled
• Partially Reconciled
• Manually Reconciled
• Not Reconciled
• Divergent
• Totals

Correspond to imported sales the counterparts of which (financial bills in Protheus) were found in accordance with the relationship key: Branch, Establishment, NSU, installment, issue date, authorization code, type (Debit/Credit) and tolerance percentage (the tolerance percentage is set in routine parameters).
Tolerance Percentage: Consider that the net sale value must be greater than or equal to the bill balance minus the specified percentage. 
Example:  Sale

Use the following calculation to define whether to fully reconcile it. 
Net Value >= Balance - ( Balance * Percentage) 
198 >= 195 - (195 * 10%) 
198 >= 195 - 19.5 
198 >= 175.5  
 
 
 
The grid above shows sales, imported through the issuer file, already related with bills found in the system. The records already become fit to be reconciled through button "Activate Reconciliation". Reset item selections by clicking item by item.

...

When the reconciliation becomes effective, the bills are posted and, consequently, a bank transaction is made based on the option selected in question Generation (Individual or Batch) and the bank balance is updated with the value posted. 
Correspond to imported sales the counterparts of which (financial bills in Protheus) were found in accordance with the relationship key, Branch, Establishment, NSU, installment, issue date, type (Debit/Credit) although "outside" the set tolerance percentage.
Tolerance Percentage: Consider that the net sale value must be smaller than the bill balance minus the specified percentage. 
Example:  Sale

Use the following calculation to define whether to fully reconcile it. 
Net Value >= Balance - ( Balance * Percentage) 
198 < 225 - (225 * 10%) 
198 < 225 - 22.5 
198 < 202.5
 
 
 
The upper grid shows sales imported through the file from the card issuer and card companies, whereas the lower grid shows data from the bill found in the system. The records already appear selected to make the reconciliation effective. You can select item by item or use option "Select All" to change the current selection. 

...

Use option "View Log" to evaluate who executed the payment reconciliation routine. Find the name of the user who executed the process in column "User".
 
 

04.  PARAMETERS

 
Parameters In Use.
    
Set the parameters below for the reconciler to operate correctly. 
MV_EMPTEF - Establishment code configured for the Software Express card company
MV_EMPTCIE - Establishment code configured for the Cielo card issuer
MV_EMPTAME - Establishment code configured for the AMEX card issuer
MV_EMPTRED - Establishment code configured for the REDE card issuer
All these parameters must be exclusive, representing the Branch to be used in reconciler. 
 
Other parameters used by the routine: 
MV_USAMEP - Enter whether the system must use table MEP to reconcile SITEF files (T = uses it; F = does not use it).
MV_1DUP - Define initialization of 1st installment of generated bill. Example: A -> for numeric sequence.  
MV_LJGERTX - Generate accounts payable for financial company with rate value, not deducting this rate from accounts receivable.
MV_BXCNAB - Define whether to group bills posted in CNAB return. (S)yes or (N)o.
MV_BLATHD - Quantity of Threads for Payment Reconciliation processing. Maximum of 20.
MV_BLADOC - Define whether to reconcile through field NSU SITEF (FIF_NSUTEF) or Certificate Number (FIF_NUCOMP)
 

05. ENTRY POINTS

ENTRY POINTS
    
Entry points used in the payment file import process (Software Express specific)
FINFIF - To add fields when importing the Software Express file - Support Document: {+}http://tdn.totvs.com/x/SX4CE+
FIN910FIL - To edit the contents of file Branch when importing the Software Express file - Support Document: {+}http://tdn.totvs.com/x/s4wDCw+ 
Entry points used in the payment reconciliation process. 
FINA910F - Define bank data when posting bills - Support Document: {+}http://tdn.totvs.com/x/X6Vc+ 
FA110TOT - To save data complementary to the totalizer record of automatic posting of accounts receivable (FINA110) - Support Document: {+}http://tdn.totvs.com/x/2YQDCw+
 

06. 

...

TABLES

• Tables

• Fields

  Table	Description
  Acronym	FIF
  Name	SITEF Reconciliation File

  Table	Description
  Acronym	FV3
  Name	EFT Import Log Detail

   

  Table	Description
  Acronym	FVR
  Name	SITEF Import Logs

  Table	Description
  Acronym	FVX
  Name	Justifications Register

   

  Table	Description
  Acronym	FVY
  Name	Operator Reasons Register

  Table	Description
  Acronym	FVZ
  Name	Branches to Consider

   

  Table	Description
  Acronym	MDE
  Name	SITEF Card Company

  Table	Description
  Acronym	MEP
  Name	SITEF Payment in Installments

  For payment reconciliation to work properly, create the fields below.
   

  Field	E1_MSFIL
  Name	Origin branch

  Field	MEP_MSFIL
  Name	Origin branch

   

  Field	FIF_MSFIL
  Name	Origin branch