Navigation:  Business Operations > Cost Centre Rate Scenario >

Sql Routine

Training Manuals Alphabetical Function List Back Print this Topic Previous topicOne level upNext topic
Show/Hide Hidden Text

On this tab the following fields can be viewed:

 

hmtoggle_plus1Calculation Description

A detailed description of the scenario to be calculated has to be specified in this field.  The calculation description is a mandatory alphanumeric field with a minimum length of 3 characters and a maximum length of 255 characters per SQL line (a total of four SQL lines can be specified).

hmtoggle_plus1Delete Sql

This button is used to delete an existing record from the Cost Centre Rate Scenario SQL table.

 

The following rules have to be complied to, to delete a SQL query:

The record (budget scenario code, created by employee, line number, calculation description and sql command) has to exist on the Cost Centre Rate Scenario SQL table.

 

And the signed - on employee has to have a profile linked to the Cost Centre Rate Scenario function with add, change and delete rights.

 

And the created by employee has to be equal to the signed - on employee.

 

And the status of the scenario (Budget Scenario) has to be equal to created.

hmtoggle_plus1Max Rows To Return

This purpose of this counter is used to enable the user to specify the amount of rows to be displayed (Results tab) when the Test button is applied.

 

The default value is thirty but values between zero and all can be selected in increments of one, ten and one hundred.

 

Take not that when a result is to be saved (Save button) to the Cost Centre Rate Scenario table, one of the rules is that All rows have to be selected.

hmtoggle_plus1Save Sql

This button is used to save the sql record to the Cost Centre Rate Scenario SQL table or to update an existing record.

 

The following rules have to be complied to, to save a SQL query:

The signed - on employee has to have a profile linked to the Cost Centre Rate Scenario function with add, change and delete rights.

 

And the created by employee has to be equal to the signed - on employee.

 

And the status of the scenario (Budget Scenario) has to be equal to created.

 

And a financial year has to be specified in the Select text box.

hmtoggle_plus1Select

The column names displayed in the select read only text box, represents the first three fix columns as displayed in the grid (Results tab) The part number and part description are populated from the Part Master table and the financial year is populated from the Part Financial Year Data table.

 

Labour Rate, Overhead Rate, Fixed Overhead Rate and Charge Out Rate:

These maintainable text boxes are used to specify a cost centre scenario.

If the selected scenario code (Scenario combo box) and Created By employee exist on the Cost Centre Rate Scenario SQL table, values will be displayed accordingly.

If no values exist on the Cost Centre Rate Scenario SQL table, the applicable column names will be displayed in each text box.

 

Where:

The joins between the different tables are displayed in this read only text box. Take note that these joins consist of a left outer join between the Cost Centre and Cost Centre Financial Year Data tables meaning that all records will be populated from the Cost Centre table even though no records exist on the Cost Centre Financial Year Data table. This is to enable the user to identify records with no values.

 

And:

The CC.Cost_Centre = "" and CCF.Financial_Year are displayed in this text box to serve as a guideline. Take note that a financial year has to be specified meaning that the calculation of rate values have to be based on an existing financial year value.

If no such value exists the result of this error will be displayed in the Result Errors tab.

hmtoggle_plus1Test

This button is used to enable the user to test the defined SQL query before the result is saved or an existing result is deleted.

 

The following rules have to be complied to, to test a SQL query:

The signed - on employee has to have a profile linked to the Cost Centre Rate Scenario function with at least inquiry rights.

 

And the created by employee has to be equal to the signed - on employee.

 

And the status of the scenario (Budget Scenario) has to be equal to created.

 

And a financial year has to be specified in the Select text box.

 

The SQL syntax will be checked when the SQL query is executed.

 

When the test button is applied the specified SQL query will be executed, the SQL syntax will be checked and if no syntax errors exist, all errors will be displayed in the Result Errors tab but the result can be viewed on the Results tab.

 

If errors do exist, the Result Errors tab will be opened by default. If no errors exist, the Results tab will be opened by default.