Estimate Types

Estimate Types allow you to have different kinds of set or defined Estimates. This allows you to create fast and accurate Estimates that reduce human error.

Discover Estimate Types

Get Started

There are three steps to create a fully functional Estimate Type:
- 1. Create an Estimate Type by making use of Parameters.
  2. Link the Estimate Type to a Transaction Type from the Estimate Type Menu button.
  3. Create a Stored Procedure to automate the Estimating process in FlameRobin.
User Access needs to be granted to access Estimate Types Setup, navigate to Main Menu > Setup > Settings > Users > Select User > Setup Group > Estimate Types - Access Allowed.

Estimate Type Setup

Estimate Types Setup allow you to determine what kind of Estimates can be created, with certain linked functionality, calculations, and defaults, e.g., a Digital Printing Estimate Type will only offer press, paper, and calculation options that are specifically linked to the digital printing Estimate Type. This means an almost automated process can be followed, as opposed to the Estimator having to scroll through the entire paper inventory, as well as all the presses available to the whole business, with finger error implications and more at risk.

Navigation

Main Menu > Setup > Estimate Types

Estimate Types Data Grid- All the Estimate Types created for the database will be listed here. The Estimate Types data grid displays the Status, Group and Name of the Estimate Type. The Estimate Type you select in this field will automatically open on the right for you to Edit.
Status - This allows you to specify the Status of the Estimate Type. Status options include Pending, Active, Closed. Only Active Estimate types can be used in Transactions.
Group - This allows you to link the Estimate Type to a specific Estimate Group.
Name - This is the Name of the Estimate Type.
Add - The Add button allows you to add a new Parameter to the Estimate Type. By clicking on the Add button's drop-down list, a list of Parameter Types will be displayed where you can select the desired Parameter Type you wish to add to the Estimate Type.
Copy - This allows you to copy a Parameter from another Estimate Type. This is done by using the From and To ID; and you can specify where the new Parameter should be inserted. This done by inserting the ID of the Parameter that you wish to be displayed below the new Parameter in the Insert before ID field.
Parameter Data Grid - The Parameter Data Grid lists all the Parameters for the specific Estimate Type and instruct the Estimate on how to behave, and what values / constraints apply. The grid consists of the following fields:
- ID - This is the Parameter's ID number.
- Type - Here you will specify the Parameter Type (more below).
- Name - This shows the Name that is used to reference the Parameter in the Expressions (11) of other Parameters. The Name can consist of capital letters and numbers, spaces are not allowed and duplicates are not allowed. When a Parameter is added, a unique name is automatically assigned and if the Name is cleared it will revert back to it's default Name. Suggested naming convention:
  - - Calculations: CALC_FIELDNAME
    - Input: INP_FIELDNAME
    - Service: PROC_FIELDNAME
    - Material: MAT_FIELDNAME
    - Error: ERROR_FIELDNAME
- Group - This field is used to link a Parameter to any other Parameter which serves as a way of grouping Parameters. This is especially useful when setting up the Estimate Type. Parameters with the same Group number are grouped together even if they're not listed underneath each other.
- Caption - This is what will appear on the Estimate as the label for the Parameter Type.
- Component - This field is used to link a Parameter to any other Parameter which serves as a Component.
- Qty From - This is used to select a numerical Parameter from which the quantity of a Parameter is derived and is applicable to 'Material' and Service Parameter Types.
- Qty Type - This determines whether the quantity is Fixed or linked to the Estimate Qty and is applicable to Material and Service Parameter Types. If the Material or Service Item is for specification purposes only (e.g. when the job is outsourced), set the Qty Type = blank to prevent the Material or Service from being added to the Estimate. Other options include:
  - - Fixed Qty - The Qty From value will be used to set the Material/Service Quantity.
    - Estimate Qty - The Material/Service Quantity will match the Estimate Quantity.
    - Fixed x Estimate Qty - The Qty From value will be multiplied with the Estimate Quantity to set the Material/Service Quantity.
- Visible - This determines whether or not and how the Parameter will be displayed on the Input tab of a General Estimate. Because of the overhead it places on the system, only Input Parameters as well as some meaningful calculation Parameters should be set to Visible. The following options are available on the Visible property of Group Header Parameters:
  - - Show: The Header will be displayed on an Estimate without the option to Expand or Collapse.
    - Hide: The Header will never be displayed on an Estimate and also won't affect the visible property of Items below the Group Header.
    - Collapse / Expand: The Header will be collapsed or expanded by default.

The following options are available on the Visible property of Parameters other than Group Headers:

- - - Show: The Parameter will be displayed on an Estimate if the Group Header is Expanded or if it does not sort under a Group Header.
    - Hide: The Parameter will never be displayed on an Estimate.
    - Group: This allows you to indicate if the Parameter is shown on its own, or displayed along with the Group Header.

- Layout - This determines the location of the Parameter's control when it is displayed in an Estimate. Options include: Below (default) and Right. This can be used to show multiple parameters next to each other.
- Size - This sets the Width of the Parameter's control when it is displayed in an Estimate. 0 = Default Width; -1 = Full width (this matches the widest Parameter's width). The Size field works with the following Parameter Types:
  - - Input - Number
    - Input - Select Number
    - Material
    - Service
- Default - When the value of a Parameter Quantity is null, the Default value will then be assigned to the Quantity. The Default field works with the following Parameter Types:
  - - Input - Number
    - Input - Select Number
    - Input - Select Text Num Value
    - Lookup
    - Service
    - Material
- Decimals (0.00) - This determines how many Decimals may be used on numeric Parameter Types and is applicable to the Input - Number and Calculation Parameter Types. Max decimals = 4.

8. Name - This shows the Name that is used to reference the Parameter in the Expressions (11) of other Parameters.

9. Caption - The Caption is displayed on the Estimate next to the Parameter's corresponding control. A maximum of 50 characters may be used.

10. Caption Set from - This sets the caption from another parameter such as a Calc - Text parameter. This parameter can be set above the Expression Editor and enables users to generate a Caption based on other Parameter values. If the Parameter is set to visible, but the generated caption is blank, the control will not be displayed. E.g., for use on a Printing Estimate where Ink Colours are selected at the top and then Usage for each selected colour is set for each Section of the printed job. So if you have a possible 8 colours to select, but only two colours are selected, only two usage fields will be displayed at each Part/Section if the usage field captions are derived from the selected colours .

11. Expression - The Expression field is used in various ways for different Parameter Types. The Editor enables users to add Functions, Operators as well as valid Parameters by clicking on the lists provided. The Expression field is used as follows:

- Calculation - The 'Expression' contains a formula (similar to a spreadsheet formula) in which the 'Names' of other numerical type Parameters can be embedded as variables using [] brackets. E.g., [PARAMNAME]. To prevent circular links, the 'Expression' may only contain links to numerical Parameters that are higher up in the list. When the Estimate calculates, the Parameter Names are substituted with the linked Parameter's value. Fixed parameters include
  - - [ESTIMATE.QTY] can be used to add the Estimate Qty to a calculation.
    - [VATITEMID] can be used to extract information about the Transaction or the Transaction Type

12. Parameter Names -This shows the list of other Parameter Type names available to be referenced in the Expression.

13. Variables - This is a list of available mathematical functions used in SQL.

14. Calculators - This is a list of available mathematical operators used in SQL calculations.

15. Menu - The Menu button provides the following additional functionality:

- Test Estimate - This will Test the Estimate.
- Open Notes - This will open a text field, allowing you to add or edit notes regarding the Estimate Type.
- Link Transaction Types - Estimate Types must be linked to Transaction Types in order for them to appear in a Transactions Estimate Menu. This allows you to create different Estimate Types for different Transaction Types. Once you've clicked Link Transaction Types a window will appear, allowing you to select the desired Transaction Types you with to link the Estimate Type to.

This can also be done from Main Menu > Setup > Transaction Types > Transaction Types tab > Select the desired Transaction Type > Estimating Settings (on the right) > Estimate Types - click on the Ellipses.

- Load and Save to File - This allows you to Load Estimate Types from an external source as well as to Save the Estimate Type.
- Delete Estimate Type - This will delete the selected Estimate Type, removing it from the Estimate Types data grid (1).

Parameter Types

Parameters are used to create Estimate Types and can be used to accept custom User input, perform calculations, and generate a Quote Description, when linked to a Transaction Type. Each Estimate Type contains an ordered list of Parameters.

Navigation

Main Menu > Setup > Estimate Types

Following is a list and explanation of the Parameter Types.

Group Header - This is a header (A on image below) for a group of parameters. It can be expanded / collapsed and is useful if there is a long list of parameters under a specific group that might look better if it is hidden, e.g., Printing Section. Also shows as a bold caption when viewed on a Estimate.

Repeat Header - This functions like a Group Header (B on image above), except it allow you to duplicate whatever is set-up under that Repeat Header. Buttons with functionality appear when Repeat Header is selected.
- - Add - This adds another set of Parameters identical to the original group.
  - Copy - Each set of Parameter has a check box on the left of it. If this is selected, and Copy is clicked, this set of Parameters - along with their values - will be duplicated beneath it.
  - Delete - This will delete the selected set of Parameters.
  - Up - This will move the selected set of Parameters up.
  - Down - This will move the selected set of Parameters down the list.
Input - Est. Qty - This is used to input a single Estimate Quantity. When used, the Estimate cannot have multiple quantities.
Input - Number - This is used to input any numerical value up to a specified number of decimals - no more than four. Calculations can be used to populate a value by default that can be overwritten.
Input - Select Number - This is used to select a numeric value from a list of hard-coded values.

Note

For Input - Select Number to work correctly, you must have a default value of 0. If this is left empty the Parameter will return an error.

Input - Text - This is used to input text values. List can also be set to have a pre-defined list of text options in a lookup list format. Can still be overwritten on an Estimate.
Input - Memo - This is used to input multi line text values such as a extra notes for the Customer.
Input - Size - DxW - This is used to put in a flat size (depth x width). A lookup can also be created in the format description,depth,width (A4,297,210).
Input - Select Text - Num Value - This is used to populate a text value that is tied to a numeric value. This is to hard code text lookups that determine how other Parameters are calculated. The numeric value is called by using [PARAMNAME.QTY].
Button - Clear - This adds a button that can be clicked to clear a specified Parameter.
Calc - Numerical - This is used to calculate a numeric value.
Calc - Estimate Qty - This is used to calculate the Estimate quantity. This restricts the user from being able to enter multiple quantities on the Estimate.

Note

Calc - Estimate Qty will not work with multiple Estimate Quantities nor Input - Est Qty Parameter.

Calc - Text - This is used to calculate and return a text value. Can also be used to display as a error message when applicable.
Lookup - This is used to populate a lookup list based on specific values from the database from a normal lookup list. The SQL must contain a ID and a DESCRIPTION column.

Note

For the Lookup to work, an ID and a Description field are required.

Item - This Parameter is used to add Items to the Estimate. It replaces the Material,Material (BoM) and Service Types.
Specification - This populates values to the Specification tab on a Estimate. Normally you would have a few Calc - Text's that calculate and formats the information before passing it to the Specification Parameter.
Description - This populates values to the Description tab on an Estimate. Normally you would have a few Calc - Text's that calculate and formats the information before passing it to the Description Parameter.
Item Description - This is used to set the Estimate Item's description on a Transaction. Can also be calculated with a Calc - Text parameter.

Stored Procedures

Stored Procedures are the brains behind the Estimating process. Stored Procedures are created in FlameRobin SQL Editor (image below).

Contact QuickEasy Helpdesk for assistance with Stored Procedures and SQL Scripts.

For more about installing FlameRobin, click here.

Estimate Type Checklist

This is an Estimate Type example created on the BOSEnterprise 2019.04 Demo Database. Follow the Checklist below to assist you with setting-up new Estimate Types:

BOSEnterprise Demo 2019.04

Estimate Type = Swimming Pool

Stored Procedure = CUSTOM_EST_SWIMMING_POOL

The image left is a 3D modeled sketch of the concrete swimming pool the Estimate Type is based on.

This Estimate Type is created to calculate the volume of concrete and amount of steel reinforcement needed as well as labor for installing the swimming pool.

Estimate Type

Follow the steps below to complete the Estimate Types Setups (see image below):

- Add Input Params - Prefix the name with INP_ - eg. INP_DEPTH
- Add an Error Message for Input Params - Type = Calc-Text, Visible = Error
- Add a Repeat Group for Materials with 1x Type = Items Param
- Add an Error Message for Materials - Type = Calc-Text, Visible = Error
- Add a Repeat Group for Services with 1x Type = Items Param
- Add an Error Message for Services - Type = Calc-Text, Visible = Error
- Add a Calculation Param to Execute Stored Procedure - Type = Calc-Numerical
- After testing, hide all the params except for the Input params.

Stored Procedure

Follow the steps below to complete the Stored Procedure SQL:

- Select the Input Parameters
- Calculate the Quantities required for Materials and Services
- Delete all the Material Repeat Items
- Insert Material Repeat Items using EST_INSERT_REPEAT_ITEM
- Update/Clear Material Error Message
- Delete all the Services Repeat Items
- Insert Service Repeat Items using EST_INSERT_REPEAT_ITEM
- Update/Clear Services Error Message
- Delete Description and Specification Params
- Insert Description Params
- Insert Specification Params (Optional)
- Update Item Description (Optional)
- Re-order Params
- Calc Estimate
- Set Result + Suspend to make the procedure selectable
- Add Error Trapping + Suspend

Click here for the Swimming Pool's Stored Procedure.

SET TERM ^ ;

ALTER PROCEDURE CUSTOM_EST_SWIMMING_POOL (

VATITEMID Integer )

RETURNS (

RESULTID Integer )

declare variable INP_DEPTH float;

declare variable INP_WIDTH float;

declare variable INP_HEIGHT float;

declare variable INP_EDGE float;

declare variable INP_THICKNESS float;

declare variable AREA_FLOOR float;

declare variable AREA_WALLS float;

declare variable AREA_EDGE float;

declare variable AREA_TOTAL float;

declare variable VOL_FLOOR float;

declare variable VOL_WALLS float;

declare variable VOL_EDGE float;

declare variable VOL_HOLE float;

declare variable VOL_TOTAL float;

declare variable PARAM_NAME varchar(50);

declare variable PARAM_QTY float;

declare variable QUOTEPARAMID integer;

declare variable STEEL_QTY float;

declare variable MESSAGE_ERROR varchar(100);

declare variable ORDERID integer;

declare variable UPDATEID integer;

begin

QTYTYPEID

0=Fixed Qty

1=Est Qty

5=Fixed x Est Qty

/***** Exit when EST_CALCPARAMS executed a second time *****/

select first 1 Q.UPDATEID from QUOTEPARAMS Q where Q.VATITEMID = :VATITEMID and Q.NAME = 'CALC_PROC' into UPDATEID;

if (:UPDATEID = 1) then Exit;

/***** Input Params *****/

--Select the Input Parameters - Params where name starts with 'INP'

for

select

Q.NAME,

Q.QTY

from QUOTEPARAMS Q

where Q.VATITEMID = :VATITEMID

and Q.NAME starting with 'INP'

into

PARAM_NAME,

PARAM_QTY

begin

if (:PARAM_NAME = 'INP_DEPTH') then :INP_DEPTH = :PARAM_QTY;

if (:PARAM_NAME = 'INP_WIDTH') then :INP_WIDTH = :PARAM_QTY;

if (:PARAM_NAME = 'INP_EDGE') then :INP_EDGE = :PARAM_QTY;

if (:PARAM_NAME = 'INP_HEIGHT') then :INP_HEIGHT = :PARAM_QTY;

if (:PARAM_NAME = 'INP_THICKNESS') then :INP_THICKNESS = :PARAM_QTY;

end

--Error Message - Inputs

MESSAGE_ERROR = '';

if (coalesce(:INP_DEPTH, 0) = 0) then

MESSAGE_ERROR = iif(:MESSAGE_ERROR > '', :MESSAGE_ERROR||', Depth', 'Depth');

if (coalesce(:INP_WIDTH, 0) = 0) then

MESSAGE_ERROR = iif(:MESSAGE_ERROR > '', :MESSAGE_ERROR||', Width', 'Width');

if (coalesce(:INP_HEIGHT, 0) = 0) then

MESSAGE_ERROR = iif(:MESSAGE_ERROR > '', :MESSAGE_ERROR||', Height', 'Height');

if (coalesce(:INP_EDGE, 0) = 0) then

MESSAGE_ERROR = iif(:MESSAGE_ERROR > '', :MESSAGE_ERROR||', Edge', 'Edge');

if (coalesce(:INP_THICKNESS, 0) = 0) then

MESSAGE_ERROR = iif(:MESSAGE_ERROR > '', :MESSAGE_ERROR||', Thickness', 'Thickness');

if (:MESSAGE_ERROR > '') then

begin

--Set Error Message

update QUOTEPARAMS Q set

Q.FORMULA_PARSED = 'Invalid values for: '||:MESSAGE_ERROR

where Q.VATITEMID = :VATITEMID

and Q.NAME = 'ERROR_INP';

end

else

begin

--Clear Error Message

update QUOTEPARAMS Q set

Q.FORMULA_PARSED = null

where Q.VATITEMID = :VATITEMID

and Q.NAME = 'ERROR_INP';

end

/***** Calculations *****/

--Calculate the Quantities required for Materials and Services

--Calc Area - sq.m

AREA_FLOOR = (:INP_DEPTH / 1000) * (:INP_WIDTH / 1000);

AREA_WALLS = ((2 * (:INP_DEPTH / 1000)) + (2 * (:INP_WIDTH / 1000))) * (:INP_HEIGHT / 1000);

AREA_EDGE = ((2 * (:INP_DEPTH / 1000)) + (2 * (:INP_WIDTH / 1000)) + (4 * (:INP_EDGE / 1000))) * (:INP_EDGE / 1000);

AREA_TOTAL = :AREA_FLOOR + :AREA_WALLS + :AREA_EDGE;

--Calc Volume - cb.m

VOL_FLOOR = :AREA_FLOOR * (:INP_THICKNESS / 1000);

VOL_WALLS = :AREA_WALLS * (:INP_THICKNESS / 1000);

VOL_EDGE = :AREA_EDGE * (:INP_THICKNESS / 1000);

VOL_HOLE = :AREA_FLOOR * (:INP_HEIGHT / 1000);

VOL_TOTAL = :VOL_FLOOR + :VOL_WALLS + :VOL_EDGE;

--Calc Rebar qty - Usage = 10m Rebar / sq.m

STEEL_QTY = (:AREA_TOTAL * 1000) * 10;

/***** Materials *****/

--Delete all Materials

delete from QUOTEPARAMS Q

where Q.VATITEMID = :VATITEMID

and Q.GROUPID = 1

and Q.REPEATID is not null;

MESSAGE_ERROR = '';

--Add Steel Rebar

QUOTEPARAMID = null;

if (:STEEL_QTY > 0) then

begin

execute procedure EST_INSERT_REPEAT_ITEM

(:VATITEMID, 1 /*GROUPID*/, 'RBR-12' /*CODE*/, 5 /*QTYTYPEID*/, :STEEL_QTY /*QTY*/)

returning_values QUOTEPARAMID;

if (:QUOTEPARAMID = -1) then MESSAGE_ERROR = 'RBR-12';

end

--Add Concrete

QUOTEPARAMID = null;

if (:VOL_TOTAL > 0) then

begin

execute procedure EST_INSERT_REPEAT_ITEM

(:VATITEMID, 1 /*GROUPID*/, 'CON-240' /*CODE*/, 5 /*QTYTYPEID*/, :VOL_TOTAL /*QTY*/)

returning_values QUOTEPARAMID;

if (:QUOTEPARAMID = -1) then MESSAGE_ERROR = iif(:MESSAGE_ERROR = '', 'CON-240', :MESSAGE_ERROR||', CON-240');

end

--Add Formwork

QUOTEPARAMID = null;

if (:AREA_WALLS > 0) then

begin

execute procedure EST_INSERT_REPEAT_ITEM

(:VATITEMID, 1 /*GROUPID*/, 'FWK-AREA' /*CODE*/, 5 /*QTYTYPEID*/, :AREA_WALLS /*QTY*/)

returning_values QUOTEPARAMID;

if (:QUOTEPARAMID = -1) then MESSAGE_ERROR = iif(:MESSAGE_ERROR = '', 'FWK-AREA', :MESSAGE_ERROR||', FWK-AREA');

end

--Error Message - Materials

if (:MESSAGE_ERROR > '') then

begin

--Set Error Message

update QUOTEPARAMS Q set

Q.FORMULA_PARSED = 'Materials: '||:MESSAGE_ERROR||' not found in Items'

where Q.VATITEMID = :VATITEMID

and Q.NAME = 'ERROR_MAT';

end

else

begin

--Clear Error Message

update QUOTEPARAMS Q set

Q.FORMULA_PARSED = null

where Q.VATITEMID = :VATITEMID

and Q.NAME = 'ERROR_MAT';

end

/***** Services *****/

--Delete all Services

delete from QUOTEPARAMS Q

where Q.VATITEMID = :VATITEMID

and Q.GROUPID = 2

and Q.REPEATID is not null;

MESSAGE_ERROR = '';

--Add Digging

QUOTEPARAMID = null;

if (:VOL_HOLE > 0) then

begin

execute procedure EST_INSERT_REPEAT_ITEM

(:VATITEMID, 2 /*GROUPID*/, 'LAB-DIGGING' /*CODE*/, 5 /*QTYTYPEID*/, :VOL_HOLE /*QTY*/)

returning_values QUOTEPARAMID;

if (:QUOTEPARAMID = -1) then MESSAGE_ERROR = iif(:MESSAGE_ERROR = '', 'LAB-DIGGING', :MESSAGE_ERROR||', LAB-DIGGING');

end

--Add Labour re-bar

QUOTEPARAMID = null;

if (:AREA_TOTAL > 0) then

begin

execute procedure EST_INSERT_REPEAT_ITEM

(:VATITEMID, 2 /*GROUPID*/, 'LAB-REBAR' /*CODE*/, 5 /*QTYTYPEID*/, :AREA_TOTAL /*QTY*/)

returning_values QUOTEPARAMID;

if (:QUOTEPARAMID = -1) then MESSAGE_ERROR = iif(:MESSAGE_ERROR = '', 'LAB-REBAR', :MESSAGE_ERROR||', LAB-REBAR');

end

--Add Labour Formwork

QUOTEPARAMID = null;

if (:AREA_TOTAL > 0) then

begin

execute procedure EST_INSERT_REPEAT_ITEM

(:VATITEMID, 2 /*GROUPID*/, 'LAB-FORMWORK' /*CODE*/, 5 /*QTYTYPEID*/, :AREA_TOTAL /*QTY*/)

returning_values QUOTEPARAMID;

if (:QUOTEPARAMID = -1) then MESSAGE_ERROR = iif(:MESSAGE_ERROR = '', 'LAB-FORMWORK', :MESSAGE_ERROR||', LAB-FORMWORK');

end

--Add Labour Pour Concrete

QUOTEPARAMID = null;

if (:VOL_TOTAL > 0) then

begin

execute procedure EST_INSERT_REPEAT_ITEM

(:VATITEMID, 2 /*GROUPID*/, 'LAB-CONCRETE' /*CODE*/, 5 /*QTYTYPEID*/, :VOL_TOTAL /*QTY*/)

returning_values QUOTEPARAMID;

if (:QUOTEPARAMID = -1) then MESSAGE_ERROR = iif(:MESSAGE_ERROR = '', 'LAB-CONCRETE', :MESSAGE_ERROR||', LAB-CONCRETE');

end

--Error Message - Services

if (:MESSAGE_ERROR > '') then

begin

--Set Error Message

update QUOTEPARAMS Q set

Q.FORMULA_PARSED = 'Services: '||:MESSAGE_ERROR||' not found in Items'

where Q.VATITEMID = :VATITEMID

and Q.NAME = 'ERROR_SER';

end

else

begin

--Clear Error Message

update QUOTEPARAMS Q set

Q.FORMULA_PARSED = null

where Q.VATITEMID = :VATITEMID

and Q.NAME = 'ERROR_SER';

end

/***** Description & Spec *****/

--Delete Description & Spec

delete from QUOTEPARAMS Q

where Q.VATITEMID = :VATITEMID

and Q.TYPEID in (9,14);

--Description - Swimming Pool size

insert into QUOTEPARAMS

(

QUOTEPARAMID,

VATITEMID,

TYPEID,

CAPTION,

FORMULA

)

values

(

gen_id(GENQUOTEPARAMID, 1),

:VATITEMID,

9, --Description

'Swimming Pool', --Max 50 char.

'Size (mm): '||cast(:INP_DEPTH as Integer)||'x'||cast(:INP_WIDTH as integer)||'x'||cast(:INP_HEIGHT as integer)||ascii_char(13)||ascii_char(10)||

'Edge: '||cast(:INP_EDGE as integer)||ascii_char(13)||ascii_char(10)||

'Wall thickness: '||cast(:INP_THICKNESS as integer)

);

--Add Specification - Concrete Volumes

insert into QUOTEPARAMS

(

QUOTEPARAMID,

VATITEMID,

TYPEID,

CAPTION,

FORMULA

)

values

(

gen_id(GENQUOTEPARAMID, 1),

:VATITEMID,

14, --Specification

'Concrete Volumes (cb.m)', --Max 50 char.

'Floor: '||cast(:VOL_FLOOR as numeric(18,2))||ascii_char(13)||ascii_char(10)||

'Walls: '||cast(:VOL_WALLS as numeric(18,2))||ascii_char(13)||ascii_char(10)||

'Edge: '||cast(:VOL_EDGE as numeric(18,2))

);

--Add Item Description

update VATITEMS V set

V.DESCRIPTION = 'Swimming Pool - Re-inforced Concrete'

where V.VATITEMID = :VATITEMID;

--Re-Order Params

ORDERID = 0;

for

select QUOTEPARAMID

from QUOTEPARAMS Q

where Q.VATITEMID = :VATITEMID

and PARAMID <> -1

order by Q.ORDERID

into QUOTEPARAMID

begin

ORDERID = :ORDERID + 1;

update QUOTEPARAMS Q set

Q.ORDERID = :ORDERID

where Q.QUOTEPARAMID = :QUOTEPARAMID;

end

--Set Result = True

RESULTID = 1;

suspend;

--Error Trapping - Set Result = False;

when any do

begin

RESULTID = 0;

suspend;

end

end^

SET TERM ; ^

GRANT EXECUTE

ON PROCEDURE CUSTOM_EST_SWIMMING_POOL TO SYSDBA WITH GRANT OPTION;

Notes

- Don’t use fixed ID’s in your Stored Procedure - rather use Item Codes that can be managed by end users.
- Add Error messages to clearly inform users when Input parameters are incomplete or Items are not found. See image below:

Report abuse