====== 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. ===== Reference ===== ==== f_artattributename ==== 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 ==== 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 ==== 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 ==== f_articleid(a_key number) return varchar Get the ID (column c_id) of an article. ==== f_articleidname ==== 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 ==== 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 ==== 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 ==== 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 ==== f_articletreeid(a_key number) return varchar Get the tree ID's (column c_id) of an article as a path. ==== f_availablestock ==== 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 ==== 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 ==== f_consumerprice(a_articlekey number, a_datetime timestamp) return number Returns the consumer price of an article for the given date and time. Returns null if there is no consumer price. ==== f_currentstock ==== 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 is rounded to the next lower integer value. Order units: 'L' = LU, 'T' = TU, 'C' = CU ==== f_departmentaccess ==== 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_departmentid ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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_inpartnerdepartments ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== f_orderitemstatus(a_key varchar) return varchar Returns the status or the order item as: ^ Return ^ Status ^ Description ^ | 'o' | open | No picks are yet present. | | 'p' | picked | Picks are present but some or all have c_delivery = NULL. | | 'd' | delivered | Picks are present, all have c_delivery != NULL but some or all have c_invoice = NULL | | 'i' | invoiced | Picks are present, all have c_delivery != NULL and c_invoice != NULL | ==== f_orderrowlocked ==== 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 ==== 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 ==== 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_packageidnamekg ==== f_packageidnamekg(a_key number) return varchar Get the ID, name and weight in kg of a package, for example ''0101.305 Migros-A 1.850 kg''. ==== f_packagenamekg ==== f_packagenamekg(a_key number) return varchar Get the name (or ID if name is null) and weight in kg of a package, for example ''Migros-A 1.850 kg''. ==== f_partneraccess ==== 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 ==== 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 ==== 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 ==== f_partnerid(a_key number) return varchar Get the ID path (column c_id) of a partner. ==== f_partneridname ==== 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 ==== 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 ==== 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 ==== 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 ==== f_partnertreeid(a_key number) return varchar Get the tree ID's (column c_id) of a partner as a path. ==== f_pickmodename ==== 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_pickartlockers ==== f_pickartlockers(a_ordertrip in number, a_article in number, a_cpr in number, a_tu_lu in number, a_cu_tu in number, a_pu_cu in number, a_ou in varchar, a_zone in number) return numbers Get a list of keys of the the actors holding locks on the items of a distinct ordertrip key, article key, consumer price, lu/tu factor, cu/tu factor, pu/cu factor, order unit and picking zone key. 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: select distinct u.c_name, a.c_nodename" from table(f_pickartlockers(1,2,3,4,5,6,'T',8)) 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: select distinct u.c_name, a.c_nodename" from f_pickartlockers(1,2,3,4,5,6,'T',8) 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_pickartlockers1 ==== f_pickartlockers1(a_ordertrip in number, a_article in number, a_cpr in number, a_tu_lu in number, a_cu_tu in number, a_pu_cu in number, a_ou in varchar) return numbers Get a list of keys of the the actors holding locks on the items of a distinct ordertrip key, article key, consumer price, lu/tu factor, cu/tu factor, pu/cu factor 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: select distinct u.c_name, a.c_nodename" from table(f_pickartlockers1(1,2,3,4,5,6,'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: select distinct u.c_name, a.c_nodename" from f_pickartlockers1(1,2,3,4,5,6,'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_pickartlockers2 ==== f_pickartlockers2(a_ordertrip in number, a_article in number, a_cpr in number, a_prcd in varchar, a_tu_lu in number, a_cu_tu in number, a_pu_cu in number, a_ou in varchar, a_selldays in number, a_expiredays in number, a_zone in number) return numbers Get a list of keys of the the actors holding locks on the items of a distinct ordertrip key, article key, consumer price, price code, lu/tu factor, cu/tu factor, pu/cu factor, order unit, selling days, expire days and picking zone key. 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: select distinct u.c_name, a.c_nodename" from table(f_pickartlockers2(1,2,3,'s',4,5,6,'T',6,3,8)) 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: select distinct u.c_name, a.c_nodename" from f_pickartlockers2(1,2,3,'i',4,5,6,'T',6,38) 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 ==== f_pickcollockers(a_ordercol in number, a_zone in number) return numbers Get a list of keys of the the actors holding locks on the items of a distinct ordercol key and picking zone key. 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: select distinct u.c_name, a.c_nodename" from table(f_pickcollockers(1,2)) 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: select distinct u.c_name, a.c_nodename" from f_pickcollockers(1,2) 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_pickcollockers1 ==== f_pickcollockers1(a_ordercol in number) return numbers Get a list of keys of the the actors holding locks on the items of a distinct ordercol key. 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: select distinct u.c_name, a.c_nodename" from table(f_pickcollockers1(1)) 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: select distinct u.c_name, a.c_nodename" from f_pickcollockers1(1) 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_pickpnrlockers ==== f_pickpnrlockers(a_ordertrip in number, a_partner in number, a_zone in number, a_mergeclass in number) return numbers Get a list of keys of the the actors holding locks on the items of a distinct ordertrip key, partner key, zone key and merge class key. 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: select distinct u.c_name, a.c_nodename" from table(f_pickpnrlockers(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: select distinct u.c_name, a.c_nodename" from f_pickpnrlockers(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_pickrowlockers ==== f_pickrowlockers(a_orderrow in number, a_zone in number, a_mergeclass in number) return numbers Get a list of keys of the the actors holding locks on the items of a distinct orderrow key, picking zone key and merge class key. 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: select distinct u.c_name, a.c_nodename" from table(f_pickrowlockers(1,2,3)) 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: select distinct u.c_name, a.c_nodename" from f_pickrowlockers(1,2,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_pkgattributename ==== 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_pricecode ==== f_pricecode(a_articlekey number, a_datetime timestamp) return varchar Returns the price code an article for the given date and time: ''n'' = regular price\\ ''p'' = promotion\\ ''i'' = introduction\\ ''s'' = sellout Returns null if there is no price. ==== f_reportaccess ==== 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 ==== 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 ==== 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 ==== 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: ^ Info ^ Index ^ Return ^ | minute | 0 ... 59 | 1 if the minute is included, 0 if not | | hour | 0 ... 59 | 1 if the hour is included, 0 if not | | dayofmonth | 1 ... 31 | 1 if the day of month is included, 0 if not | | weekday | 0 ... 7, where 0/7 = sunday, 1 = monday, etc. | 1 if the weekday is included, 0 if not | | month | 1 ... 12 | 1 if the month is included, 0 if not | | firsttime | null | The first schedule time as hour.minute | | lasttime | null | The last schedule time as hour.minute | 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 ==== f_sessionid() return number Get the session id in a database independant manner. Example: select f_sessionid() from t_dual ==== f_shipclassname ==== 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 ==== f_ssccprefix() return varchar Returns the SSCC prefix from the centrals system settings in ''Labeling/LU/SSCC Prefix'' ==== f_stkattributename ==== 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 ==== 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 ==== f_stocklocationoccupancy(a_key varchar) return varchar Check the occupancy of a stock location. Returns the state as: ^ Return ^ Status ^ Description ^ | 'e' | empty | No stocks are assigned to the location. | | 'o' | occupied | One or more stocks is/are assigned to the location. The stock may however be 0, or even below zero (for virtual stocks). | ==== f_stocklocationordering ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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 ==== 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. ==== f_zoneaccess ==== f_zoneaccess(a_zonekey 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_zonename ==== f_zonename(a_key number) return varchar Get the name of a picking zone. ==== f_zonepartnerordering ==== f_zonepartnerordering(a_zone number, a_partner number) return varchar Get the partner ordering within a zone as a string. This function is typically used in order clauses. ==== p_purgeactors ==== 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 ==== p_purgeemptyorders() Removes any empty order trips (table [[dbtables#t_ordertrips]]), rows (table [[dbtables#t_orderrows]]) and columns (table [[dbtables#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 ==== 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 ==== 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()