Sample UDF
This topic provides sample instructions for creating a UDF function. This sample UDF might be used for calculating a tax that varies with the room type. The steps below show how the UDF would be coded, incorporated into the OPERA schema, and then referenced in the setup of the appropriate transaction code generate.
- Make a copy of the sample script and function code below. Name the file something meaningful and save it with the extension of .fun (for example, occ_tax.fun).
- In the "Script" section, replace the function name 'XXXXX' with the filename you selected in Step 1 (without the .fun extension).
- Use your SQL application to locate the column(s) within the reservation_general_view that the tax calculation will be based on.
- In the "Function" section, replace the string 'ZZZZZ' with the column name that the tax calculation will be based on. In the finished example below we are using the column “room_category_label”
- In the "Function" section, replace the string '@@@@@@@@' with the condition that the tax calculation will be based on.
- In the "Function" section, replace the 'VV' with the value of the tax calculation if the condition in Step 5 is met. Replace the '$$' with the value of the tax calculation if the condition in Step 5 is not met.
- Save the file into a customizable reports folder in the directory where the OPERA runtimes are stored.
- Locate the OPERA_SMT.EXE in the tools directory of your OPERA installation, for example, \\Micros\opera\tools and start it. Click on Schema Maintenance.
Note: When schema maintenance is in progress, the OXI Processor will be disabled by the schema maintenance utilities.
- Enter the User name, Password and Oracle Alias that applies to your OPERA installation. Click on the Connect button.
- Click the ellipse button and navigate to the location where you have stored your UDF function – in this case it is the customizable_reports folder. Click on the Apply e- Patch button.
- Click Next to run the Schema Operation.
- After this the Schema Maintenance Tool will install your function and compile it. It is important to read the logfile afterwards to determine if the function has been implemented and compiled successfully.
- Log in to the OPERA application. Locate the transaction code where the UDF function will be running. Select the Generates button and add the transaction code where the tax calculation will be posted, and select the UDF Function radio button.
- In the UDF Function text area type your function name e.g., XXXXX(RESV_NAME_ID, BASE). The XXXXX represents the name you designated in Step 2.
- Steps 10 and 11 need to be completed for each transaction code that you want the tax calculation function to be run on.
Sample UDF Code
--***DO NOT MAKE ANY CHANGES TO THE SCRIPT OR FUNCTION OTHER THAN INDICATED ABOVE.***
----------------------------------------------------------------
--SCRIPT.....
--
Create or Replace FUNCTION XXXXX(RESV_NAME_ID in number, BASE in
number) return number IS
Cursor get_resv (in_resv_name_id in number) is
select * from reservation_general_view where resv_name_id
= in_resv_name_id;
rgvr reservation_general_view%rowtype;
tax number;
in_resv_name_id number := resv_name_id;
begin
open get_resv(in_resv_name_id);
fetch get_resv into rgvr;
close get_resv;
--
--------------------------------------------------------------
--FUNCTION....
--
IF rgvr.ZZZZZ @@@@@@@@@ then
tax := VV;
else
tax := $$;
end if;
--**TAX IS AN INTERNAL VARIABLE, IN WHICH THE END RESULT OF THE
--FUNCTION WILL BE STORED**********DO NOT CHANGE**************
return tax;
end;
Example
Assume the property wants a $2 occupied room tax to be posted for each regular room and $4 for each suite. Also assume that the room types of regular rooms are KG and TW. (Alternatively, you could code this function using the room types for suites, instead.)Follow the steps given above and your actual function will look like this:
IF rgvr.room_category_label in ('KG','TW') then
tax := 2;
else
tax := 4;
end if;
return tax;
end;
In this example, room_category_label is the column name in reservation_general_view that holds the room type. If the room category label is 'KG' or 'TW' post $2, if not post $4.
