Automatic Process SQL Execution

The stored procedures and SQL commands may be run through the routine of automatic process.

The novelty implemented is the running of commands saved in a text file. This option is useful because any Stored Procedures can be registered, since they have to be frequently run on the Database. This option has been implemented specially for the Routine of Data Integration with other systems.

However, some rules must be noticed:

• Each line of this text file is an independent SQL command. Commands broken into two lines will result in error;
• The entire script running occurs within a single transaction. If an error occurs, a rollback will be run, undoing the last changes;
• The semicolon at the end of each command is unnecessary, because even, if it exists, the routine will eliminate it. This is to avoid errors with the database, which does not support this character in the command;
• Commands for blocks, as "start transaction" or "begin", will result in errors;
• The Stored Procedure, SQL File and the SQL Command are mutually excluding, that is, if indicating one, the other will be disabled and deleted. It means that, we have run a SQL Command, or a Stored Procedure or a Script file.

Example:

File of valid script:

UPDATE R034FUN SET NOMFUN='JOÃO' WHERE NUMEMP=1 AND TIPCOL=1 AND NUMCAD=1; INSERT INTO R074BAI VALUES (4202404,5,'ITOUPAVAS',0); DELETE FROM R074CID WHERE CODCID=4204202;

Process code
Report a code and description for the process.

Owner System
Select the abbreviation of the Human Capital Management | HCM module that will be the owner of the task. Thus, when the task is run, it will be destined to the correct system.

Location
Specify which processes are running on a specific server without these processes are executed per the other servers that use this same database.

Periodicity
The system displays information referring to the periodicity of running this process. To change these settings, use the Periodicity button.

Only on Weekdays
It indicates if the Process must be also executed on Saturdays, Sundays and holidays.

Predecessor Process
This field allows to indicate that this process will only be run after the end of running the other process. For this, it is necessary to report the type of predecessor process (if it is a Report process, Calculation, etc.) and its code. The predecessor process is run one minute before the main process.

Example:

Process 1: Evaluation Calculation
Process 2: Evaluation Report

It is desired that the Process 2 only be run after running the Process 1. Then, in the registration of Process 2, the Type of predecessor 4 (Calculations) must be indicated, as well as the Code of predecessor 1. With that, the Process 2 will be run only if the Process 1 has been successfully completed.

Process may Deactivates
Report if there is an error in the running of the automatic process. If there is, report if the process must be deactivated, or if it must continue running in the next periods.

Generates Log Message
Report if the system must generate a log message if any error occur when running the automatic process.

Email of the person responsible
Report the e-mail of the person responsible for the process, to send a log message when an error occurs in the running of automatic process.

Stored Procedure
Runs a stored procedure on the database from its name.

SQL Commands
Report the SQL command to be run on the defined date and time.

Language code
Report the code of the language in which the automatic process will be run.