Datastore functions and procedures

• Functions and procedures starting with f_geko_ and f_tako_ are interface functions for the GEKO and TAKO robots do not belong to the BPS core.
• Functions and procedures starting with cf_ or cp_ respectively, are custom functions and procedures and do not belong to the BPS core. Ask your system integrator for documentation of such objects in case.

f_artattributename(a_key number) 
return varchar

Get the name (column c_name) of an article class. This is a convenience function to avoid joins in relation table routines.

f_articleclassname(a_key number) 
return varchar

Get the full name of an article class. A slash is used to separate parts of the name.

Example:

select f_articleclassname(c_key) 
from t_articleclasses
order by 1

Output:

apples
apples/golden
apples/golden/large
apples/golden/small
apples/granny
bananes
bananes/chiquita
bananes/havelar
oranges
...

f_articlehasattribute (a_articlekey number, a_attributename varchar) 
return number

Returns 1 if the article with the given key has the named attribute, and 0 otherwise.

f_articleid(a_key number) 
return varchar

Get the ID (column c_id) of an article.

f_articleidname(a_key number) 
return varchar

Get the ID and name (c_id || ' ' || c_name) of an article. This is a convenience function to avoid joins in relation table routines.

f_articlename(a_key number) 
return varchar

Get the name (column c_name) of an article. This is a convenience function to avoid joins in relation table routines.

f_articleordering(a_key number) 
return varchar

Get the lowercased ID path (concatenated c_id columns) of an article, where each ID is padded with blanks to 35 characters. This function is typically used in order clauses.

f_articles_propagates(a_key number, a_item varchar) 
return boolean

Check if propagating of a column/options table is enabled in a certain article. The main usage is in the propagate triggers.

Example:

# check if article with key 123 propagates the column c_name
if f_articles_propagates(123, 'c_name') then 
  #do something
end if;

f_articletreeid(a_key number) 
return varchar

Get the tree ID's (column c_id) of an article as a path.

f_availablestock(a_key number, a_unit varchar) 
return number

Get the available stock in the desired order unit for a given article key.

Stocks on locations with status open and drawing are added, and the missing (not yet picked) units in open trips where status is not history are subtracted to get the quantity.

Order units: 'L' = LU, 'T' = TU, 'C' = CU

f_availabletripstock(a_ordertripkey number, a_articlekey number, a_unit varchar) 
return number

Get the stock available for picking in the desired order unit for a given combination of order trip and article key.

Stocks on locations with status open and drawing are added, and the missing (not yet picked) units are subtracted to get the quantity. The missing quantity is calculated of all preceeding, unarchived order trips. “Preceding” are all order trips displayed before the given trip in the tree view of the order trips application, e.g. where order trips are ordered by t_ordertrip.c_date, t_trip.c_id, t_ordertrip.c_key.

Order units: 'L' = LU, 'T' = TU, 'C' = CU

f_consumerprice(a_articlekey number, a_datetime timestamp) 
return number

Returns the consumer price of an article for the given date and time.

Returns 0 if there is no consumer price.

f_currentstock(a_key number, a_unit varchar) 
return number

Get the current stock in the desired order unit for a given article key.

Stocks on locations with status open and drawing are included in the sum. The sum if rounded to the next lower integer value.

Order units: 'L' = LU, 'T' = TU, 'C' = CU

f_days(a_days number) 
return interval

Get the interval of days in a database independent way.

Example:

select * from t_audits where c_time > current_timestamp - f_days(2) 

f_departmentaccess(a_departmentkey number, a_userkey number) 
return number

Check access right of a department/user combination. Returns 1 if access is granted, 0 for no access.

f_departmentname(a_key number) 
return varchar

Get the id (column c_id) of a department. This is a convenience function to avoid joins in relation table routines.

f_departmentidname(a_key number) 
return varchar

Get the id and name (c_id||' '||c_name) of a department. This is a convenience function to avoid joins in relation table routines.

f_departmentname(a_key number) 
return varchar

Get the name (column c_name) of a department. This is a convenience function to avoid joins in relation table routines.

f_findstocklocation(a_path varchar) 
return number

Find the stock location key from a given stock location path. Returns the key if found, or null if no such stock location is found.

This is the inverse of function f_stocklocationid.

f_haspermission(a_permission varchar, a_userkey number) 
return number

Check function access right of a user. Returns 1 if access is granted, 0 for no access.

Example:

# check if user with key 12 has permission to add articles
if f_haspermission('articles.add', 12) then 
  #do something
end if;

f_inarticleattributes(a_article number, a_attribute number) 
return number

Check if the combination of article and attribute exists in table t_articleattributes. Returns 1 when true, 0 when false.

f_inarticleclass(a_testkey number, a_classkey number) 
return number

Check if a_testkey is equal to a_classkey, or is a child class of a_classkey. Returns 1 when true, 0 when false.

f_inarticlecollections(a_article number, a_collection number) 
return number

Check if the combination of article and collection exists in table t_articlecollections. Returns 1 when true, 0 when false.

f_inmergeclass(a_testkey number, a_classkey number) 
return number

Check if a_testkey is equal to a_classkey, or is a child class of a_classkey. Returns 1 when true, 0 when false.

f_inpartnerattributes(a_partner number, a_attribute number) 
return number

Check if the combination of partner and attribute exists in table t_partnerattributes. Returns 1 when true, 0 when false.

f_inpartnerclass(a_testkey number, a_classkey number) 
return number

Check if a_testkey is equal to a_classkey, or is a child class of a_classkey. Returns 1 when true, 0 when false.

f_inpartnerdepartnments(a_partner number, a_department number) 
return number

Check if the combination of partner and department exists in table t_partnerdepartments. Returns 1 when true, 0 when false.

f_inpartnertrips(a_partner number, a_trip number) 
return number

Check if the combination of partner and trip exists in table t_partnertrips. Returns 1 when true, 0 when false.

f_inpromotion(a_key number, a_datetime timestamp) 
return number

Check if the article with the given key has a promotion at the given date and time.

Returns 1 if it is in promotion, 0 if no promotion.

f_insaleclass(a_testkey number, a_classkey number) 
return number

Check if a_testkey is equal to a_classkey, or is a child class of a_classkey. Returns 1 when true, 0 when false.

f_inshipclass(a_testkey number, a_classkey number) 
return number

Check if a_testkey is equal to a_classkey, or is a child class of a_classkey. Returns 1 when true, 0 when false.

f_instockattributes(a_stocklocation number, a_attribute number) 
return number

Check if the combination of stock location and attribute exists in table t_stockattributes. Returns 1 when true, 0 when false.

f_intripclass(a_testkey number, a_classkey number) 
return number

Check if a_testkey is equal to a_classkey, or is a child class of a_classkey. Returns 1 when true, 0 when false.

f_key(a_key varchar) 
return number

Returns the value of a_key in case it is not null, or -1 if it is null. This makes joining of keys, which could be null, more convenient because you need not care about nulls in the clause.

Example:

select c_key from t_settings
where f_key(c_parent)=f_key(?)
and f_key(c_user)=f_key(?)
and lower(c_name)=lower(?)
and c_type='g'

f_languagename(a_key number) 
return varchar

Get the name (column c_name) of a language. This is a convenience function to avoid joins in relation table routines.

f_licensesinuse(a_licenser varchar, a_licgroup varchar) 
return number

Get the number of licenses in use for a certain license group.

Example:

select f_licensesinuse('IBK Software AG', 'Master Data') 

f_mergeclassname(a_key number) 
return varchar

Get the full name of a merge class. A slash is used to separate parts of the name.

Example:

select f_mergeclassname(c_key) 
from t_mergeclasses
order by 1

f_nextkey(a_name varchar) 
return number

Get the next key for a table with name t_||a_name. The key is generated from the sequence s_||a_name. The function makes sure that a key yet unused in the table is returned.

Example:

insert into t_clients(c_key, c_name)
values (f_nextkey('clients'), 'New customer')

f_nextval(a_sequence varchar) 
return number

Get the next value of a sequence in a database independent way.

Example:

insert into t_clients(c_key, c_name)
values (f_nextval('s_clients'), ?)

To make sure that the key is yet unused in the target table, use function f_nextkey instead.

f_ordercollocked(a_key varchar) 
return number

Checks if an order column, the trip it belongs to, or any of its items is locked. Also checks if any rows are locked belonging to the items of this column.

Returns 1 if any locks are present, and 0 if no locks are present.

f_orderitemlocker(a_key varchar) 
return number

Returns key of the actor holding a lock on this order item, or NULL if no lock is present.

f_orderitemstatus(a_key varchar) 
return varchar

Returns the status or the order item as:

f_orderrowlocked(a_key varchar) 
return number

Checks if an order row, the trip it belongs to, or any of its items is locked. Also checks if any columns are locked belonging to the items of this row.

Returns 1 if any locks are present, and 0 if no locks are present.

f_ordertriplocked(a_key varchar) 
return number

Checks if an open trip or any of its rows, columns or items is locked.

Returns 1 if any locks are present, and 0 if no locks are present.

f_packageidname(a_key number)
return varchar

Get the ID and name (c_id || ' ' || c_name) of a package. This is a convenience function to avoid joins in relation table routines.

f_partneraccess(a_partnerkey number, a_userkey number) 
return number

Check access right of a partner/user combination. Returns 1 if access is granted indirectly via department, 0 for no access.

f_partnerclassname(a_key number) 
return varchar

Get the full name of a partner class. A slash is used to separate parts of the name.

Example:

select f_partnerclassname(c_key) 
from t_partnerclasses
order by 1

f_partnerhasattribute(a_partnerkey number, a_attributename varchar) 
return number

Returns 1 if the partner with the given key has the named attribute, and 0 otherwise.

f_partnerid(a_key number) 
return varchar

Get the ID path (column c_id) of a partner.

f_partneridname(a_key number) 
return varchar

Get the ID and name (c_id||' '||c_name) of a partner. This is a convenience function to avoid joins in relation table routines.

f_partnername(a_key number) 
return varchar

Get the name (column c_name) of a partner. This is a convenience function to avoid joins in relation table routines.

f_partnerordering(a_key number) 
return varchar

Get the lowercased ID path (concatenated c_id columns) of a partner, where each ID is padded with blanks to 35 characters. This function is typically used in order clauses.

f_partners_propagates(a_key number, a_item varchar) 
return boolean

Check if propagating of a column/options table is enabled in a certain partner. The main usage is in the propagate triggers.

Example:

# check if partner with key 123 propagates the column c_name
if f_partners_propagates(123, 'c_name') then 
  #do something
end if;

f_partnertreeid(a_key number) 
return varchar

Get the tree ID's (column c_id) of a partner as a path.

f_pickmodename(a_key number) 
return varchar

Get the name (column c_name) of a picking mode. This is a convenience function to avoid joins in relation table routines.

f_pickcollockers(a_ordertrip number, a_article number, a_ou varchar, a_pickzone) 
return numbers

Get a list of keys of the the actors holding locks on the items of a distinct ordertrip, article, order unit and pickzone. All levels are checked, so also trip locks or row locks belonging to the items are listed.

The return type is t_keytable (table of number) for oracle, and setof numeric for PostgreSQL.

Oracle example:

// t_ordertrip.c_key = 1
// t_article.c_key = 2
// ou = T
// t_pickzones.c_key = 3
select distinct u.c_name, a.c_nodename"
from table(f_pickcollockers(1,2,'T',4)) l"
inner join t_actors a on l.column_value=a.c_key"
inner join t_users u on a.c_user=u.c_key"
order by 1,2

PostgreSQL example:

// t_ordertrip.c_key = 1
// t_article.c_key = 2
// ou = T
// t_pickzones.c_key = 3
select distinct u.c_name, a.c_nodename"
from f_pickcollockers(1,2,'T',3) l"
inner join t_actors a on l=a.c_key"
inner join t_users u on a.c_user=u.c_key"
order by 1,2

f_pickcollockers(a_ordertrip number, a_article number, a_ou varchar) 
return numbers

Get a list of keys of the the actors holding locks on the items of a distinct ordertrip, article and order unit. All levels are checked, so also trip locks or row locks belonging to the items are listed.

The return type is t_keytable (table of number) for oracle, and setof numeric for PostgreSQL.

Oracle example:

// t_ordertrip.c_key = 1
// t_article.c_key = 2
// ou = T
select distinct u.c_name, a.c_nodename"
from table(f_pickcollockers1(1,2,'T')) l"
inner join t_actors a on l.column_value=a.c_key"
inner join t_users u on a.c_user=u.c_key"
order by 1,2

PostgreSQL example:

// t_ordertrip.c_key = 1
// t_article.c_key = 2
// ou = T
select distinct u.c_name, a.c_nodename"
from f_pickrowlockers(1,2,'T') l"
inner join t_actors a on l=a.c_key"
inner join t_users u on a.c_user=u.c_key"
order by 1,2

f_pickrowlockers(a_ordertrip number, a_partner number, a_pickzone number, a_mergeclass number) 
return numbers

Get a list of keys of the the actors holding locks on the items of a distinct ordertrip, partner, pickzone and merge class. All levels are checked, so also trip locks or column locks belonging to the items are listed.

The return type is t_keytable (table of number) for oracle, and setof numeric for PostgreSQL.

Oracle example:

// t_ordertrip.c_key = 1
// t_partner.c_key = 2
// t_pickzones.c_key = 3
// t_mergeclasses.c_key = 4
select distinct u.c_name, a.c_nodename"
from table(f_pickrowlockers(1,2,3,4)) l"
inner join t_actors a on l.column_value=a.c_key"
inner join t_users u on a.c_user=u.c_key"
order by 1,2

PostgreSQL example:

// t_ordertrip.c_key = 1
// t_partner.c_key = 2
// t_pickzones.c_key = 3
// t_mergeclasses.c_key = 4
select distinct u.c_name, a.c_nodename"
from f_pickrowlockers(1,2,3,4) l"
inner join t_actors a on l=a.c_key"
inner join t_users u on a.c_user=u.c_key"
order by 1,2

f_pickzoneaccess(a_pickzonekey number, a_userkey number) 
return number

Check access right of a picking zone/user combination. Returns 1 if access is granted, 0 for no access.

f_pickzonename(a_key number) 
return varchar

Get the name of a picking zone.

f_pickzonepartnerordering(a_pickzone number, a_partner number) 
return varchar

Get the partner ordering within a pickzone as a string. This function is typically used in order clauses.

f_pkgattributename(a_key number) 
return varchar

Get the name (column c_name) of a package attribute. This is a convenience function to avoid joins in relation table routines.

f_reportaccess(a_reportkey number, a_userkey number) 
return number

Check access right of a report/user combination.

Returns:

0 = no access
1 = read only
2 = read and write

f_reportpath(a_key in number) 
return varchar

Get the full path name of a report. The slash is used to separate parts of the path, similar to file system paths on linux.

Example:

select f_reportpath(c_key), c_type from t_reports

f_saleclassname(a_key number) 
return varchar

Get the full name of a sale class. A slash is used to separate parts of the name.

Example:

select f_saleclassname(c_key) 
from t_saleclasses
order by 1

f_scheduleinfo(a_schedule varchar, a_info varchar, a_index number) 
return number

Extract information from a schedule field.

The following parts can be extracted:

Example:

select f_scheduleinfo('* 7 * mon-fri *', 'weekday', 2) from t_dual
// returns 1 because tuesday is included

select f_scheduleinfo('15,45 7-18 * mon-fri *', 'firsttime', null) from t_dual
// returns 7.15 

select f_scheduleinfo('15,45 7-18 * mon-fri *', 'lasttime', null) from t_dual
// returns 18.45 

f_sessionid() 
return number

Get the session id in a database independant manner.

Example:

select f_sessionid() from t_dual

f_shipclassname(a_key number) 
return varchar

Get the full name of a shipping class. A slash is used to separate parts of the name.

Example:

select f_shipclassname(c_key) 
from t_shipclasses
order by 1

f_ssccprefix() 
return varchar

Returns the SSCC prefix from the centrals system settings in Labeling/LU/SSCC Prefix

f_stkattributename(a_key number) 
return varchar

Get the name (column c_name) of a stock attribute. This is a convenience function to avoid joins in relation table routines.

f_stocklocationid(a_key number) 
return varchar

Get the tree path (column c_id) of a stock location. A slash is used to separate parts of the path.

f_stocklocationoccupancy(a_key varchar) 
return varchar

Check the occupancy of a stock location.

Returns the state as:

f_stocklocationordering(a_key number) 
return varchar

Get the lowercased ID path (concatenated c_id columns) of a stock location, where each ID is padded with blanks to 35 characters. This function is typically used in order clauses.

f_stocklocations_propagates(a_key number, a_item varchar) 
return boolean

Check if propagating of a column/options table is enabled in a certain stock location. The main usage is in the propagate triggers.

Example:

# check if stock location with key 123 propagates the column c_status
if f_stocklocations_propagates(123, 'c_status') then 
  #do something
end if;

f_textline (a_text varchar, a_line number) 
return varchar

Extract a distinct line from a multiline text. The line breaks are assumed to be CR or CR/LF. The line numbering starts at 1.

Example:

# Get first 2 lines of label text:
select 
  f_textline(c_labeltext,1) as line1, 
  f_textline(c_labeltext,2) as line2 
from t_partners;

f_thisactor() 
return number

Get the actor key (t_actors.c_key) of the current session.

Works only if checked in to the datastore, and returns NULL otherwise.

f_thisuser() 
return number

Get the user key (t_users.c_key) of the current session.

Works only if checked in to the datastore, and returns NULL otherwise.

f_tripclassname(a_key number) 
return varchar

Get the full name of a trip class. A slash is used to separate parts of the name.

Example:

select f_tripclassname(c_key) 
from t_tripclasses
order by 1

f_tripidname(a_key number) 
return varchar

Get the ID and name (c_id||' '||c_name) of a trip. This is a convenience function to avoid joins in relation table routines.

f_tripname(a_key number) 
return varchar

Get the name (column c_name) of a trip. This is a convenience function to avoid joins in relation table routines.

f_usernameid(a_name varchar, a_userid number)
return varchar

• Returns a_userid enclosed in square brackets if a_name is null.
• Returns a_name followed by a blank and a_userid in square brackets otherwise.

Examples:

f_usernameid(null, 10)  /* [10] */

f_usernameid('John', 20) /* John [20] */

select f_usernameid(c_name, c_userid) from t_users

f_vatname(a_key number) 
return varchar

Get the name (column c_name) of a value added tax. This is a convenience function to avoid joins in relation table routines.

p_purgeactors()

Removes any actors for which the database session no longer exists.

Since PostgreSQL does not have procedures, it is implemented as function without return value.

Oracle example:

begin p_purgeactors(); end;

PostgreSQL example:

select f_purgeactors()

p_purgeemptyorders()

Removes any empty order trips (table t_ordertrips), rows (table t_orderrows) and columns (table t_ordercols). Typically this procedure will be executed by applications after deleting any order columns, rows or items to clean up.

Since PostgreSQL does not have procedures, it is implemented as function without return value.

Oracle example:

begin p_purgeemptyorders(); end;

PostgreSQL example:

select f_purgeemptyorders()

p_rebuildordershadow()

Rebuilds the order shadow columns in t_orderitems, t_ordercols and t_orderrows. The order shadow is made up by replication of redundant values and sums that are present for performance reasons, and are updated automatically by triggers. The shadow could get out of sync when triggers are disabled or somebody accidently updates a shadow column. In this dase, the procedure will fix the issue. Depending on the amount of orders in the database, the execution time can be very long.

Since PostgreSQL does not have procedures, it is implemented as function without return value.

Oracle example:

begin p_rebuildordershadow(); end;

PostgreSQL example:

select p_rebuildordershadow()

p_rebuildstockshadow()

Rebuilds the stock shadow columns in t_articles and t_stock. The order shadow is made up by replication of redundant values and sums that are present for performance reasons, and are updated automatically by triggers. The shadow could get out of sync when triggers are disabled or somebody accidently updates a shadow column. In this dase, the procedure will fix the issue.

Since PostgreSQL does not have procedures, it is implemented as function without return value.

Oracle example:

begin p_rebuildstockshadow(); end;

PostgreSQL example:

select p_rebuildstockshadow()