Skip to content

Useful PL Methods

This page presents some PL/SQL based utility methods that are part of the Business Reporting & Analysis services framework, supporting some specific development situations.

Use the page when you want to find details about these methods, supporting some scenarios related to Business Reporting & Analysis services contents in technical components:

  1. Installations
  2. Upgrades
  3. Information Sources - special access methods

Installation Specific Methods

The following methods supports installations of BI related information. To simplify for our customers, it is suggested to pre-define some information to reduce administration and configuration time.

Register Data Mart Refresh Category


----------------------------------------------  
-- Package Xlr_Mv_Refresh_Category_API  
----------------------------------------------  
PROCEDURE Register_Refresh_Category(  
   category_id_         IN VARCHAR2,  
   usage_note_          IN VARCHAR2,  
   description_         IN VARCHAR2,  
   gather_statistics_   IN BOOLEAN );  

The method registers a refresh category supposed to be a placeholder for one or more Data Mart sources supposed to be refreshed in the same refresh operation. A Data Mart source is either a Materialized View or an incrementally loaded table. It is also suggested that the Data Mart sources are categorized according to the following:

  1. DIM_ALL Use this category for all dimension related Data Mart sources except for translations. To be used by all components.
  2. TRANSLATIONS Use this category for all translation specific Data Mart sources. To be used by all components. Suggested to be manually refreshed.
  3. IS_<product> Use this category for all Information Source/fact related Data Mart sources. Example of category names are IS_FINANCIALS, IS_SUPPLY_CHAIN.

Registration of common refresh categories is handled centrally, i.e. by the F1 Base Functionality component.

Add Data Mart Source to a Refresh Category


----------------------------------------------  
-- Package Xlr_Mv_Per_Refresh_Cat_API  
----------------------------------------------  
PROCEDURE New_Mv(  
   category_id_         IN VARCHAR2,  
   dm_source_name_      IN VARCHAR2 );   

The method adds a Data Mart source to a refresh category. It is suggested that each technical component has a file that handles its own Data Mart sources. A Data Mart source is either a Materialized View or an incrementally loaded table, specific by the argument dm_source_name_.

To be added to the component specific INS file, named <component>MvRefreshCategoryDetails.ins

Please refer to the the corresponding development template.

Set Default Client Folder Name for an Information Source


----------------------------------------------  
-- Package Xlr_Def_Fact_Nav_Folder_API  
----------------------------------------------  
PROCEDURE Set_Default_Folder_Name(  
   fact_id_             IN VARCHAR2,  
   default_folder_name_ IN VARCHAR2,  
   always_add_          IN BOOLEAN DEFAULT FALSE)  

To simplify administration and and set up of the BR Client Navigator, used by IFS Business Reporter, the above method can be used to set/register a default folder where an Information Source should appear.

It is recommended that each component that contains Information Sources, adds definitions to a component specific INS file named <Component>InfoSourceDefaultFolder.ins.

Note: Skip defining the last parameter always_add_ in the INS file, i.e. the default value is good enough. Only use  always_add_ in a situation where is it necessary to force addition of an Information Source to a specific folder. If an Information Source is removed from the navigator from the BR Client Navigator form, it will not be added by running the INS file unless the always_add_ parameter is TRUE.

Note: The INS file should be deployed after deployment of all BI metadata files.

Please refer to the the corresponding development template.

Register Dimension Reference Relations


----------------------------------------------  
-- Package Xlr_Dim_Def_Relations_API  
----------------------------------------------  
PROCEDURE Reg_Dim_Ref_Relation(  
   ref_name_     IN VARCHAR2,  
   dimension_id_ IN VARCHAR2)  

This method registers a relation between a Logical Unit, argument ref_name_, and a dimension, argument dimension_id_.

The purpose is to define more or less natural connections between a Logical Unit and a dimension to support the Quick Information Source tool.

For more information, please refer to the Develop Dimension section.

Remove Reference Relation


----------------------------------------------  
-- Package Xlr_Dim_Def_Relations_API  
----------------------------------------------  
PROCEDURE Remove_Relation(  
   ref_name_     IN VARCHAR2)  

This method removes a previously defined relation between a Logical Unit, argument ref_name_, and a dimension.

Reference relations between Logical Units and dimensions are used by the Quick Information Source tool. Due to redesign a dimension might get deprecated or even removed and in such cases it is important to remove not valid relations.

General Metadata Supportive Methods

Please refer to the following package for Business Reporting & Analysis services related meta data support methods. The methods supports both installation as well as upgrade/patch scenarios


----------------------------------------------  
-- Package Xlr_Meta_Util_API  
----------------------------------------------

Upgrade Specific Methods

The following methods can typically be used in different upgrade scenarios.

Materialized View Supportive Methods in Installation_SYS


----------------------------------------------  
-- Package Installation_SYS  
----------------------------------------------  
PROCEDURE Remove_Materialized_View(  
   mv_name_     IN VARCHAR2)  

The method Installation_SYS.Remove_Materialized_View can be used to remove a Materialized View from the application owner schema.

Consider the following:

  1. The method is used in files that creates Materialized Views since it is not possible to replace a Materialized View definition as is the case with a view. The existing Materialized View must be removed/deleted first.
  2. Using this method will not affect parameter definitions or refresh categories related to the Materialized View.

Materialized View Supportive Methods in Xlr_Mv_Criteria_API


----------------------------------------------  
-- Package Xlr_Mv_Criteria_API  
----------------------------------------------  
PROCEDURE Remove_Parameters_For_Mv(  
   mv_name_     IN VARCHAR2)  

The method Xlr_Mv_Util_API.Remove_Parameters_For_Mv can be used to remove all parameters related to a specific Materialized View, argument mv_name_. It is required that the Materialized View is removed before removing the parameters.

Data Mart Source Supportive Methods in Xlr_Mv_Refresh_Category_API


----------------------------------------------  
-- Package Xlr_Mv_Refresh_Category_API  
----------------------------------------------  
PROCEDURE Remove_Batch_Schedule(  
   category_list_     IN VARCHAR2)  

PROCEDURE Remove_Refresh_Category(  
   category_id_ IN VARCHAR2 );  

The method Xlr_Mv_Refresh_Category_API.Remove_Batch_Schedule can be used to remove one or many batch schedules based on one or more Data Mart source specific refresh categories.

The method Xlr_Mv_Refresh_Category_API.Remove_Refresh_Category can be used to remove a specific Data Mart source refresh category.

Data Mart Source Supportive Methods in Xlr_Mv_Per_Refresh_Cat_API


----------------------------------------------  
-- Package Xlr_Mv_Per_Refresh_Cat_API  
----------------------------------------------  
PROCEDURE Remove_Mv_In_All_Categories(  
   dm_source_name_     IN VARCHAR2)  

PROCEDURE Replace_Mv(  
   category_id_    IN VARCHAR2,  
   dm_source_from_ IN VARCHAR2,  
   dm_source_to_   IN VARCHAR2);  
     
PROCEDURE Replace_Mv_In_All_Categories(  
   dm_source_name_from_   IN VARCHAR2,  
   dm_sourcename_to_      IN VARCHAR2)  

The method Xlr_Mv_Per_Refresh_Cat_API.Remove_Mv_In_All_Categories can be used to remove a Data Mart source from all refresh categories where it has been added.

The method Xlr_Mv_Per_Refresh_Cat_API.Replace_Mv can be used to replace one Data Mart source with another in a specific refresh category.

The method Xlr_Mv_Per_Refresh_Cat_API.Replace_Mv_In_All_Categories can be used to replace one Data Mart source with another in all refresh categories where mv_name_from_ is present.

Data Mart Source Supportive Methods in Xlr_Mv_Util_API

The methods in this package were initially created to support Materialized Views. Due to reason some of the methods now also support incrementally loaded snapshot tables.


----------------------------------------------  
-- Package Xlr_Mv_Util_API  
----------------------------------------------  

PROCEDURE Activate_Facts (  
   source_name_list_  IN VARCHAR2,  
   relation_          IN VARCHAR2,  
   gather_statistics_ IN BOOLEAN DEFAULT FALSE);  

PROCEDURE Activate_Unusable_Mviews (  
   dm_source_list_    IN VARCHAR2,  
   gather_statistics_ IN BOOLEAN DEFAULT FALSE);  
     
PROCEDURE Refresh_Facts (  
   source_name_list_  IN VARCHAR2,  
   relation_          IN VARCHAR2,  
   gather_statistics_ IN BOOLEAN DEFAULT FALSE);  

PROCEDURE Refresh_Dimensions (  
   source_name_list_   IN VARCHAR2,  
   relation_           IN VARCHAR2,  
   gather_statistics_  IN BOOLEAN DEFAULT FALSE);  
     
PROCEDURE Recreate_Mviews(  
   mview_list_ IN VARCHAR2);  
     
PROCEDURE Mv_Exist(  
   mview_name_    IN  VARCHAR2);  

FUNCTION Get_Ifs_Mv_Staleness(  
   dm_source_name_  IN  VARCHAR2,  
   db_staleness_    IN  VARCHAR2) RETURN VARCHAR2;  

FUNCTION Get_Mv_Staleness_Client(  
   dm_source_name_  IN  VARCHAR2,  
   db_staleness_    IN  VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;  
     
PROCEDURE Remove_Mv(  
   mv_name_     IN VARCHAR2)  

The method Xlr_Mv_Util_API.Activate_Facts can be used to active, i.e. run the first controlled refresh, of all Data Mart sources related to a Fact.

The method Xlr_Mv_Util_API.Activate_Unusable_Mviews can be used to activate one or more Data Mart sources. Activation means setting the source as active and to perform the first refresh (creating the first snapshot).

The method Xlr_Mv_Util_API.Refresh_Facts can be used to refresh all Data Mart sources related to one or more Facts. Only referenced Data Mart source that are active will be considered.

The method Xlr_Mv_Util_API.Refresh_Dimensions can be used to refresh all Data Mart sources related to one or more Dimensions. Only referenced Data Mart source that are active will be considered.

The method Xlr_Mv_Util_API.Recreate_Mviews can be used to recreate one or more Materialized Views based on the contents in the dictionary.

The method Xlr_Mv_Util_API.Mv_Exist ``can be used to find out if a Materialized View exists in the database

The function Xlr_Mv_Util_API.Get_Ifs_Mv_Staleness can be used to get the IFS specific staleness or refresh state for a Data Mart source

The function Xlr_Mv_Util_API.Get_Mv_Staleness_Client ``can be used to get the client staleness/refresh state for a Data Mart source

The method Xlr_Mv_Util_API.Remove_Mv can be used to remove a Materialized View and associated information. The following actions are performed:

  • Remove the Materialized View from the database
  • Remove all parameters related to the Materialized View
  • Remove the Materialized View from all possible refresh categories

Supportive Methods in Is_Mv_Util_API

These methods can only be used for entities that support incrementally load of snapshot table


----------------------------------------------  
-- Package Is_Mv_Util_API  
----------------------------------------------  

PROCEDURE Create_Mvs_View(  
   entity_id_      IN VARCHAR2,  
   fact_entity_    IN BOOLEAN,  
   mvs_view_name_  IN VARCHAR2);  

PROCEDURE Create_Mvt_Table_Gen_Stmt(  
   mvb_view_name_  IN VARCHAR2,  
   mv_table_name_  IN VARCHAR2);  

FUNCTION Get_Staleness(  
   mv_table_name_  IN VARCHAR2)RETURN VARCHAR2;  
     
PROCEDURE Remove_Mv_Table(  
   entity_id_     IN VARCHAR2,  
   fact_entity_   IN BOOLEAN);  
     
PROCEDURE Refresh_Mvt_List(  
   mv_table_names_ IN VARCHAR2);  

The method Is_Mv_Util_API.Create_Mvs_View can be used to create the MVS (source) view that is used when performing incremental load. Requires that the snapshot table (MVT) and the base view (MVB) already exist.

The method Is_Mv_Util_API.Create_Mvt_Table_Gen_Stmt can be used to generate the create statement of the snapshot (MVT) table.

The function Is_Mv_Util_API.Get_Staleness can be used to get the current staleness/refresh state of a incrementally loaded source.

The method Is_Mv_Util_API.Remove_Mv_Table can be used to remove a snapshot (MVT) table from the database. The method makes sure to also remove the table from any refresh category.

The method Is_Mv_Util_API.Refresh_MVt_List can be used to refresh one or more snapshot tables (MVTs). This method will always refresh regardless of refresh state, i.e. activate is not considered at all, something that is only considered in the client and in methods in Xlr_Mv_Util_API

General Meta Data Supportive Methods

Please refer to the following package for BI services related meta data support methods.


----------------------------------------------  
-- Package Xlr_Meta_Util_API  
----------------------------------------------

Two of the available methods will be mentioned since they might be used in upgrade scenarios.


----------------------------------------------  
-- Package Xlr_Meta_Util_API  
---------------------------------------------- PROCEDURE Remove_Dimension(    dimension_id_ IN VARCHAR2 );

Method Xlr_Meta_Util_API.Remove_Dimension completely removes a dimension from the metadata repository. Typically to be used when removing a dimension that is no longer supported. However always make sure that the dimension is not used by any Information Source before removal.


----------------------------------------------  
-- Package Xlr_Meta_Util_API  
---------------------------------------------- PROCEDURE Remove_Fact(    fact_id_ IN VARCHAR2 );

Method Xlr_Meta_Util_API.Remove_Fact completely removes a fact/Information Source from the metadata repository.

The following methods can be used to handle special cases related to the Business Reporter client navigator


----------------------------------------------  
-- Package Xlr_Source_Navigator_API  
----------------------------------------------  
PROCEDURE Remove_Fact(  
   fact_id_    IN VARCHAR2)  

The method removes all occurrences of an Information Source (Fact)  in the Business Reporter client navigator. An Information Source is normally added to a deafult folder in this navigator during installation by the method Xlr_Def_Fact_Nav_Folder_API.Set_Default_Folder_Name. The default navigator folder information will also be removed.

In most cases this method should not have to be used since the general Remove_Fact method should handle everything.


----------------------------------------------  
-- Package Xlr_Def_Fact_Nav_Folder_API  
----------------------------------------------  
PROCEDURE Remove_Fact(  
   fact_id_    IN VARCHAR2)  

The method removes the default source navigator information related to an Information Source.

In most cases this method should not have to be used since the general Remove_Fact method should handle everything. Also using Xlr_Source_Navigator_API.Remove_Fact has the same effect.

Removing Presentation Object Security Object

The following method can be used to remove an object, typically a view, from a Presentation Object duing upgrade.


----------------------------------------------  
-- Package Pres_Object_Util_API  
----------------------------------------------  
PROCEDURE Remove_Pres_Obj_Sec (  
   po_id_      IN VARCHAR2,  
   sec_object_ IN VARCHAR2 );  

The method is meant to support the following scenario:

  1. During installation of Information Source metadata, i.e. Fact or Dimension specific metadata, all access views will be connected to a component specific Presentation Object
  2. The view will be connected to the Presentation Object as manual entries, which means that when at a later stage the component specific presentation object security file is generated, all the manual entries will be there.
  3. Now if a Fact or Dimension is removed or if e.g. an access view is removed (the view is replaced by a new view with another name or removed completely) then the removal is performed in the UPG file, for next release, and in a CDB file to correct the development environments.
  4. However, the manual entry will still be there in the presentation object repository, i.e. the view is still manually connected to the Presentation Object. Automated tests will find out that there is a Presentation Object (PO) that has a reference to a view that does not exist and errors will be generated.
  5. The remedy will be to, in the UPG and CDB scripts, add a call to Pres_Object_Util_API.Remove_Pres_Obj_Sec in order to permanently remove the view from the PO. This means that next time the presentation object security file is generated, the access view will not be listed anymore.

Information Source Access Methods

The following methods can be used when building Information Sources, representing special access methods.

Latest Refresh Date


----------------------------------------------  
-- Package Xlr_Mv_Util_API  
----------------------------------------------  
FUNCTION Get_Latest_Refresh_Date(  
   fact_id_     IN VARCHAR2) RETURN DATE  

The function Xlr_Mv_Util_API.Get_Latest_Refresh_Date returns the latest refresh date for an Information Source identified by the argument fact_id_. The following applies:

  1. The function will find all directly related Materialized Views referenced by the Information Source. This means e.g. that dimension references will not be considered.
  2. The latest refresh data for all found Materialized View candidates is returned.
  3. The function will return NULL`` if one of the Materialized View candidates does not have a latest refresh date defined. This can e.g. happen if a Materialized View has not yet been activated.

The following methods gives programmatic support related to incremental load.

Remove Entity Criteria


----------------------------------------------  
-- Package Is_Entity_Mv_Criteria_API  
----------------------------------------------  
PROCEDURE Remove(  
   IS_ENTITY_MV_CRITERIA_API.New_Rec)  

The Is_Entity_Mv_Criteria_API.Remove method can be used to remove a criteria associated with an entity supporting incremental load.

Remove Entity Criteria Detail


----------------------------------------------  
-- Package Is_Entity_Mv_Criteria_Det_API  
----------------------------------------------  
PROCEDURE Remove(  
   rem_rec_     IN IS_ENTITY_MV_CRITERIA_DET_API.New_Rec)  

The Is_Entity_Mv_Criteria_Det_API.Remove method can be used to remove a criteria detail associated with an entity supporting incremental load.

Remove Entity Source


----------------------------------------------  
-- Package Is_Entity_Mv_Source_API  
----------------------------------------------  
PROCEDURE Remove(  
   rem_rec_     IN IS_ENTITY_MV_SOURCE_API.New_Rec);  

The Is_Entity_Mv_Source_API.Remove method can be used to remove a source associated with an entity supporting incremental load.

Remove Entity Source Detail


----------------------------------------------  
-- Package Is_Entity_Mv_Source_Detail_API  
----------------------------------------------  
PROCEDURE Remove(  
 rem_rec_     IN IS_ENTITY_MV_SOURCE_DETAIL_API.New_Rec);    

The Is_Entity_Mv_Source_Detail_API.Remove method can be used to remove source details associated with an entity supporting incremental load.

Remove Entity Source View


----------------------------------------------  
-- Package Is_Entity_Mv_Source_View_API  
----------------------------------------------  
PROCEDURE Remove(  
   rem_rec_     IN IS_ENTITY_MV_SOURCE_VIEW_API.New_Rec);  

The Is_Entity_Mv_Source_View_API.Remove method can be used to remove a source view associated with an entity supporting incremental load.

Remove Source Master Column


----------------------------------------------  
-- Package Is_Entity_Mv_Src_Mast_Col_API  
----------------------------------------------  
PROCEDURE Remove(  
   rem_rec_     IN IS_ENTITY_MV_SRC_MAST_COL_API.New_Rec);

The Is_Entity_Mv_Src_Mast_Col_API.Remove method can be used to remove master source column info associated with an entity supporting incremental load.

Remove Entity


----------------------------------------------  
-- Package Is_Entity_Mv_Info_API  
----------------------------------------------  
PROCEDURE Remove(  
   rem_rec_     IN IS_ENTITY_MV_INFO_API.New_Rec);  

The Is_Entity_Mv_Info_API.Remove method can be used to remove an entity supporting incremental load.

Define Entity as Unusable


----------------------------------------------  
-- Package Is_Mv_Util_API  
----------------------------------------------  
PROCEDURE Set_Unusable(  
   entity_id_     IN VARCHAR2,  
   fact_entity_   IN BOOLEAN);  

The call will make sure that the status of the entity is defined as UNUSABLE but without changing the refresh status of referenced tables. This means that all referenced source tables may have the refresh status FRESH even if the entity is UNUSABLE. Since the entity was active before the call was made, the Activate option will not be enabled. The Refresh option will however be available and leads to a complete refresh.

Remove a Registered CMV Definition


----------------------------------------------  
-- Package Is_Entity_Dynamic_Cmv_API  
----------------------------------------------  
PROCEDURE Remove_Cmv_Definition(  
   entity_id_    IN VARCHAR2,  
   fact_entity_  IN BOOLEAN,  
   source_table_ IN VARCHAR2,  
   cmv_name_     IN VARCHAR2);  

The method can be used to remove a registered CMV, check Materialized View, definition. If an entity supporting incremental load is dependent of a source table in a dynamic component, a component that might not be there at installation time, then the Materialized View that checks max rowversion/timestamp cannot be created directly. Instead the definition is stored and will during the installation process be created if the dynamic component is available.

Now suppose that a change is done in the entity that leads to that there is no longer any reference to the dynamic source table. It will be necessary to remove the CMV definition and the Remove_Cmv_Definition can be used to do this removal.

Note: The call will only remove the definition in the internal table, not the Materialized View itself. Even if the definition is removed there might be another entity that is dependent on exactly the same Materialized View definition. This happens when more than one entity is dependent on the same source table. The Materialized View definition itself can only be removed when there are no more entities using it.