Sometimes database functions need to be accessed from the Xephr
Once you have this information, determine the variables that need to be passed to the database function.
Functions return a single value where a procedure returns multiple values.
The function definition might look something like this:
function new_order(t_cust_no in varchar2,
t_cust_po in varchar2,
t_request_dt in varchar2,
t_initials in varchar2,
t_status in varchar2) return varchar2;
Anything that has an 'in' after it needs to be passed in through the database function call.
Hyperlinks and xephr functions should always use single quotes ( ' ) instead of double quotes ( " ).
Create the database function call as follows:
When creating this link, the Hyperlink To field will contain the function call, and the Hyperlink Scripted pop-list needs to be set to true.
Add the return variable that will receive the returning value from the database function. This is a user-defined variable that must be assigned, even if the variable is not used. The variable is assigned by typing the word var, a space, and then the variable name. var variable_name
Add an equal sign =
Add the word CallFunction
Add an open parenthesis (
Enter the package name in which the database function is defined followed by a period followed by the database function name, in quotes, followed by a comma. 'package_name.function',
For each variable listed that must be passed to the function, enter the code to obtain the field value that will be passed followed by a comma $$BLOCK.FIELD$$, or $$BLOCK.FIELD.first$$,
These must be in the same order as they are listed in the function definition.
If the value being passed is being hard-coded instead of coming from the entity, you can instead enter the hard-coded value in quotes followed by a comma. 'value'
If the value being passed in is the system date from the database, the value is entered as a pound sign followed by the word sysdate, in quotes followed by a comma. '#sysdate'
Repeat 1 thorough 7 for any additional variables and values that you are passing. The final value will NOT be followed by a comma.
When all values have been entered, add a close parenthesis )
Always close with a semi-colon ;
As an example, when creating a sales order from the custmast_list entity, call the database function new_order, which is located in the package procoapi.sql.
The database function looks like this:
function new_order(t_cust_no in varchar2,
t_cust_po in varchar2,
t_request_dt in varchar2,
t_initials in varchar2,
t_status in varchar2) return varchar2;
Which means that t_cust_no, t_cust_po, t_request_dt, t_initials, and t_status are passed in, and the return variable passed back will be a number.
Add the word var followed by our user-defined return variable
var new_order
Add the equal sign =
var new_order=
Add the word CallFunction
var new_order=CallFunction
Add the open parenthesis
var new_order=CallFunction(
Add the package name, period, and database function name in quotes followed by a comma.
var new_order=CallFunction('procoapi.new_order',
Add the variable value for the first variable that will be passed to the database function, either hard coded or from a block and field on the entity, followed by a comma. In this case, the t_cust_no value comes from the custmast block in the CUST_NO field, so no quotes.
var new_order=CallFunction('procoapi.new_order', $$CUSTMAST.CUST_NO$$,
Add the variable value for the next variable that will be passed, either hard coded or from a block and field on the entity, followed by a comma. In this case, the t_cust_po value is hard-coded to be WEB, so add quotes.
var new_order=CallFunction('procoapi.new_order', $$CUSTMAST.CUST_NO$$, 'WEB',
Add the variable value for the next variable that will be passed, either hard coded or from a block and field on the entity, followed by a comma. In this case, the t_request_dt value is the system date from the database, so add quotes. Only the system date is coded as a pound sign followed by sysdate.
var new_order=CallFunction('procoapi.new_order', $$CUSTMAST.CUST_NO$$, WEB, '#sysdate',
Add the variable value for the next variable that will be passed, either hard coded or from a block and field on the entity, followed by a comma. In this case, the t_initials value is hard-coded as WEB, so add quotes.
var new_order=CallFunction('procoapi.new_order', $$CUSTMAST.CUST_NO$$, 'WEB', '#sysdate', 'WEB',
Add the final variable value for the next variable that will be passed, either hard coded or from a block and field on the entity. In this case, the t_status value is hard-coded as O, so add quotes. Because it is the final variable value, it is NOT followed by a comma.
var new_order=CallFunction('procoapi.new_order', $$CUSTMAST.CUST_NO$$, 'WEB', '#sysdate', 'WEB', 'O'
Add the close parenthesis.
var new_order=CallFunction('procoapi.new_order', $$CUSTMAST.CUST_NO$$, 'WEB', '#sysdate', 'WEB', 'O')
End the statement with a semi-colon.
var new_order=CallFunction('procoapi.new_order', $$CUSTMAST.CUST_NO$$, 'WEB', '#sysdate', 'WEB', 'O');
Variable |
xephr function |
package_name.database function |
Value of t_cust_no variable in block.field format from the CUST_NO field in the CUSTMAST block |
Value of t_cust_po variable, hard coded in quotes |
Value of t_request_dt variable, taken from the database hard coded in quotes |
Value of t_initials variable, hard coded in quotes |
Value of t_status variable, hard coded in quotes |
var new_order= |
CallFunction |
('procoapi.new_order', |
$$CUSTMAST.CUST_NO$$, |
'WEB', |
'#sysdate', |
'WEB', |
'O'); |
Xephr is a registered trademark of NDS Systems, LC.
Copyright