Database Function Calls

Sometimes database functions need to be accessed from the Xephr® entities. To create a database function call, first determine the needed database function, and then determine the package name in which it is located.

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.