INDEX
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:
- Sales reconciliation: the recognition of the sales record versus confirmation of the sale sent in the card company file.
- Payments reconciliation: is the identification of the payment record versus confirmation of payment sent in the card company file. Upon this recognition and confirmation of the reconciliation, the bill is posted in the system.
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:
\baseline\cartao\Import – Directory in which the files are saved for reading/processing.
\baseline\cartao\Process – Directory in which successfully processed files are automatically saved.
\baseline\cartao\falha – Directory in which files with errors are automatically saved.
To run this process in the SIGALOJA module, register the card companies that must have the "RD - Network" data.
- Import Payments/Sales file: Saving successfully processed records. This process only embraces ratified layouts: Software Express, Cielo, Rede and Amex. Occasional import criticisms will be available through a log, for future analysis and to take measures.
- Import log: View import criticisms detailing each occurrence, with option to print.
- Sales Reconciliation: Reconcile sales records (imported from files) with financial records of accounts receivable. For reconciliation, configure routine questions. From the configuration of these questions, records are displayed in tabs onscreen to be reconciled, as explained below:
- Reconciled: Correspond to imported sales the counterparts of which (financial bills) were found in accordance with the relationship key: Branch, Site, NSU, Installment, Issue Date, Authorization Code, type "CD" (Automatic Debit Card) or "CC" (Automatic Credit Card) and whether the difference between the value of the sale imported and the value of the financial bill balance is within the tolerance percentage set in routine processing questions.
- Partially Reconciled: Correspond to imported sales the counterparts of which (financial bills) were found in accordance with the relationship key: Branch, Site, NSU, Installment, Issue Date, Authorization Code, type "CD" (Automatic Debit Card) or "CC" (Automatic Credit Card) and whether the difference between the value of the sale imported and the value of the financial bill balance is outside the tolerance percentage set in routine processing questions.
- Bills Without Sales: Correspond to all bills in the database in the period entered in routine processing questions not found in card issuer files.
- Sales Without Bills: Correspond to sales imported from the card issuer and/or card company file the counterparts of which were not found as financial bills, enabling the manual reconciliation of bills available in the database.
- Divergences: Correspond to information available in card issuer or card company files, for manual action of the system user. For example: Total Refund, Partial Refund, Charge Back, Modality Change.
- Totals: Display quantity of records and values processed by date for each of the respective tabs.
- Reconciliation Report: For the evaluation of sales reconciliations.
- Payment Reconciliation: Reconcile payment records (imported from files) with financial records. For reconciliation, configure routine questions. From the configuration of these questions, records are displayed in tabs onscreen to be reconciled, as explained below:
- Reconciled: Correspond to imported payments the counterparts of which (financial bills) were found in accordance with the relationship key: Branch, Site, NSU, Installment, Issue Date, type "CD" (Automatic Debit Card) or "CC" (Automatic Credit Card) and whether the difference between the value of the sale imported and the value of the financial bill balance is within the tolerance percentage set in routine processing questions.
- Partially Reconciled: Correspond to imported payments the counterparts of which (financial bills) were found in accordance with the relationship key: Branch, Site, NSU, Installment, Issue Date, Type "CD" (Automatic Debit Card) or "CC" (Automatic Credit Card) and whether the difference between the value of the sale imported and the value of the financial bill balance is outside the tolerance percentage set in routine processing questions.
- Manually Reconciled: Correspond to payments the user has manually reconciled while running this routine.
- Not Reconciled: Correspond to imported payments the counterparts of which (financial bills in Protheus) were not found, enabling manual reconciliation with bills available in the database.
- Divergences: Correspond to information available in card issuer or card company files, for manual action of the system user. For example: Total Refund, Partial Refund, Charge Back, Modality Change.
- Totals: display quantity of records and values processed by date for each of the respective tabs.
- Postings Report: You may use default system reports to evaluate postings made by the reconciliation process, such as:
- Postings Report
- Bank Statement
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)
- Fields NSUTEF: Use the fields below to save the single summary number (NSUTEF) generated in sale of Protheus and responsible for identifying the sale with the bill. We advise that these fields should always have equivalent sized for the reconciliation to work properly. Preferably, by respecting the size of 12 places, which is the size recommended by the card companies serviced by TOTVS reconciler. AMEX/ CIELO/ REDECARD/ SOFTWARE EXPRESS.
- FIF->FIF_NSUTEF
- SE1→E1_NSUTEF
- SL1->L1_NSUTEF
- SL4->L4_NSUTEF
- MDK->MDK_NSUTEF
We currently have the following layouts approved for importing:
03. ROUTINES AND FEATURES
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.
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.
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.
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.
- Card Issuer correctly registered in table "SITEF Administrators Code" with directory parameters duly set.
Eft.FileDir indicates the directory in which the file will be read.
Proc.FileDir indicates the directory to which the file will be moved after successful processing.
Proc.Inc.Dir indicates the directory to which the file will be moved after processing with error.
Note: to enable fields for input, Card Type must be RD - Rede
- Branch enabled for execution. See documentation about "Branch Registers to Consider" - FINA913.
- Import screen for payment/sales files from Card Companies.
- Detail on directories from which the files will be read.
The import does not consider the branch in which the process is taking place, given that files may have more than one establishment. Thus, branch control does not depend on the sharing of the table at issue.
- After importing it, a detailed log of criticisms per file is issued for the identification of problems.
Description of columns:
The Captions possible for this screen. The text of field Status follows caption details
File name: Provides details on the file imported. (the file name is used as a control method to avoid duplication of processing)
Processing Date: Date on which the operation was performed
Processing Time: Time in which the operation was performed
Rec. Added: Quantity of records added to sales/payments control table. (depending on the layout/card issuer, we may have sets of lines to form a sale/payment)
Not Processed: Quantity of lines not processed by problems/criticisms. (you may evaluate details on this processing through the import log - FINA917)
Lines Read: Quantity of lines read, sum of all lines evaluated by the program, considering even the header, trailer or any other line not processed by the routine.
Fi Li Tot: File lines total, the sum of lines not processed plus lines read.
User Code: Code of user who processed the import.
Co. Code: Code of Credit Card Company/Issuer involved in the operation. (this information is defined in accordance with the folder in which the file was made available)
Use this process to reconcile sales imported with bills saved in the system.
Main browser displays all sales records this routine has imported and enabled.
Captions:
Captions are updated in accordance with user actions and the comparative results between Protheus and credit card company files.
When files are imported, all records start with "Not Processed", changing status as information is processed.
Reconcile
When you select option reconcile, we have the following parameters screen
At this point you need to choose between reconciling sales from the Software Express layout or from Others, which are comprised of Cielo, Rede and Amex:
These options differ in the selection of Card Issuer x Card Company. For Software Express, you can select between one option of various card companies, whereas Others offers only the Cielo, Rede and Amex options.
After selection, a screen is displayed with parameters you can select:
Select Branch: You can select one or more branches. The field is multiple choice for reconciliation of multiple branches.
From Date: Start Date of sale reconciliation. (evaluate the issue date of sale)
To Date: End Date of sale reconciliation. (evaluate 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)
Type: Select the type to be reconciled. Options: Debit/Credit or Both.
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. (query yields data specific to your selection, whether Software Express or Others)
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)
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.
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.
To 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 Order
2 – NSU / Recon.= NSU Prder
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
On the EFT Imports Log screen you can:
View
Other Actions → Print Browser
Statuses Partially Imported, File Not Imported allow viewing problem details in accordance with the import line.
When the status is "Partially Imported", view the problem detail in the option below:
View
Header:
Status = Occurrence found in file in accordance with the caption.
File name = Name of selected file.
Process Date = Date of processing day.
Process Time = Time of processing.
Added Rec = Quantity of sales/payment records added (FIF).
Does Not Process = Quantity of file lines not processed.
Lines Read = Quantity of files lines read (including lines that formed a sales/payment record).
Fi Li Tot = Total quantity of file lines. Sum of lines not processed with lines read.
User Name = Name of user logged during the import.
Co Name = Name of Card Company/Issuer related to the folder in which the file became available.
Details:
Proc Line = Line of file for analysis of criticism.
Estab Code = Code of establishment - Use it to relate the code at the company with the system branch, set in parameter (*see parameters).
Certificate = Number of sales certificate. However, the record criticized may not have this information. It is only displayed if found in the line evaluated.
Reason = Occurrence found in file.
Some particularities are related to the reasons.
When the criticism is related to not finding the Branch or establishment registered, the system criticizes only one line and totals the other lines with problems, grouping this error in only one line to make viewing easier.
Example: as the image shows, we have 18 lines of the file with the same establishment code, the parameters of which are not set for any branch; thus, all these lines cannot be processed.
The other criticisms are displayed line by line or in a group of lines, depending on the layout of each card issuer.
In View you can also Print the Import Logs Report FINR917 with the details onscreen with no need to select parameters. Just click "Other Actions - Print"
Use it to print the Import Logs Report FINR917, setting parameters and including all import details.
Print Browser
Standard feature to print the current browser.
Printing via Import Logs routine (menu)
The EFT import log report provides details on the logs generated when importing the "EFT (FINA914)" reconciliation files.
You may call the report via menu, through routine Import Logs (FINR917)
Option Print.
Report Parameters.
From File Name? Enter the name of a given import file to print (Start).
To File Name? You can enter the name of a given import file for printing (End).
From Processing Date? Select the date in which you wish to print the report or period (Start).
To Processing Date? Select the date in which you wish to print the report or period (End).
Card Company? Select the card company.
User of Import? Filter by user who executed the file import.
Top header displays the Import Log table data (FVR)
Co Name: Display card company name.
File Name: Display imported file name.
Bottom header displays the Import Log Details table data (FVR)
Proc Line: File line that generated log.
Estab Code: Code of logged line establishment.
Certificate: Number of certificate.
Reason: Display the reason, in detail, why the file line was not included in the import.
Process Date: Display the date of import file processing.
Pro Time: Display the time of import file processing.
Pr.Rec.Qty: Display the quantity of sales/payment records entered in the system that generate EFT reconciliation files (FIF).
Not Pr.Qty: Display the quantity of lines not processed and registered in table FV3
Pr.Ln.Qty: Display the quantity of lines processed. Example: Header, Trailer, sales details, adjustments, etc.
Lines Qty: Shows the sum of lines processed and not processed, adding up to the total of lines in the file. Sum of Not Pr.Qty and Pr.Ln.Qty.
User Name: Display the name of the user responsible for importing the file.
Status: Display the status of file import.
Main browser displays all sales/payment records this routine has imported and enabled. From this browser the reconciliation process begins.
The process reconciles payments, which are records from file imports of the card issuers: CIELO, REDECARD, AMEX and from the Card Company SOFTWARE EXPRESS, with financial records of accounts receivable, which had as payment methods: credit or debit card.
When a payment is reconciled, the bill is posted and its value enters the bank transactions.
Caption:
Captions are updated in accordance with user actions and the comparative results between Protheus and files from the issuers: CIELO, REDECARD, AMEX and from the Credit Card Company SOFTWARE EXPRESS
When files are imported, all records start with "Not Processed", changing status as information is processed.
Edit
If needed, you can edit the "status" field of a sales bill. The statuses you can change are:
Posted: This status occurs when payments are reconciled, because this process posts the bills.
Divergent: This status occurs when there are full returns, partial returns, charge back or modality changes.
Not Processed: This status occurs when bills are not reconciled.
It must be emphasized that status adjustments do not reverse bill postings.
Reconciliation
When you select option reconcile, we have this parameters screen: Software Express or Others:
Software Express: reconciliation of sales from the Layout of Software Express.
Others: reconciliation of sales from the Layout Others, which are: Cielo, Rede and Amex.
These options differ in the selection of Card Issuer x Card Company. For Software Express, you can select various card companies, whereas Others offers only the Cielo, Rede and Amex options.
After selection, a screen is displayed with parameters you can fill out:
04. PARAMETERS
05. ENTRY POINTS
06. TABLES
Visão Geral
Conteúdo das Ferramentas