IAL Objects Development

The concept of IAL Objects is introduced in About Information Access Layer (IAL).

All development of IAL-Objects requires that an IAL have been installed and configured. The goal of all IAL-Objects should be to separate them from the rest of the database in order for external tools to have easier access to the information, to prepare data for presentation (include as much info as possible) and to preprocess the data so it is easier to use.

Contents

• Process for Developing IAL Objects
• IAL Object Sample
• Creating the IAL file
• Writing Select -statements for IAL views
• Table and Index Declaration
• Object Registration
• Creating a Partially Replicable IAL Object
• Deploy Object
• Localization
• Deployment

Process for Developing IAL Objects

To develop IAL-Objects the process is made up of the following activities.

1. Decide which data is required from the application.
2. Create an .ial-file that defines this object
3. Deploy the object using Sql File Executor.
4. Set security using Security-tool, making it available to the end user

IAL Objects should be developed for access through any client based reporting tool.

IAL Object Sample

IAL objects are defined in .ial-file, A sample is given below.

-----------------------------------------------------------------------------
--
-- Component: FNDBAS
--
-- Purpose: Creates the IAL Object TST_IAL_USER
--
--
--
-- Date Sign History
-- ------ ------ -----------------------------------------------------------
-- 170306 IFSAPP Created.
-----------------------------------------------------------------------------

DEFINE OBJECT = TST_IAL_USER
DEFINE MODULE = FNDBAS
DEFINE AO = &AO
DEFINE IAL_OWNER = &IAL_OWNER

PROMPT Creating IAL Object &OBJECT

-----------------------------------------------------------------------------
---------------------------- DISABLE OBJECT ---------------------------------
-----------------------------------------------------------------------------

BEGIN
&AO..IAL_Object_API.Disable('&OBJECT');
END;
/

-----------------------------------------------------------------------------
-------------------------------- IAL VIEW -----------------------------------
-----------------------------------------------------------------------------

CREATE OR REPLACE VIEW &IAL_OWNER..&OBJECT._IAL AS
SELECT description, identity, oracle_user FROM &AO..FND_USER_TAB
WHERE identity='IFSAPP'
WITH read only;

GRANT SELECT ON &IAL_OWNER..&OBJECT._IAL TO &AO WITH GRANT OPTION
/

-----------------------------------------------------------------------------
----------------------- TABLE FOR DATA REPLICATION --------------------------
-----------------------------------------------------------------------------

DECLARE
dummy_ NUMBER := 0;
CURSOR check_table IS
SELECT 1
FROM user_tables
WHERE table_name = UPPER('&OBJECT._TAB');
BEGIN
OPEN check_table;
FETCH check_table INTO dummy_;
IF NOT (check_table%FOUND) THEN
EXECUTE IMMEDIATE ('CREATE TABLE &IAL_OWNER..&OBJECT._TAB ' || 
' TABLESPACE &ial_data ' || 
' AS ( SELECT * FROM &IAL_OWNER..&OBJECT._IAL WHERE 1=2 )' );
END IF;
CLOSE check_table;
END;
/

-----------------------------------------------------------------------------
--------------------------- OBJECT REGISTRATION -----------------------------
-----------------------------------------------------------------------------

DECLARE
ial_where_clause_ VARCHAR2(400) := 'Security_SYS.Has_System_Privilege(''ADMINISTRATOR'') = ''TRUE''';
ial_object_desc_ VARCHAR2(100) := 'Test IAL for fnd user';
BEGIN
&AO..IAL_Object_API.Enable('&OBJECT');
&AO..IAL_Object_API.Add_Where_Clause('&OBJECT', ial_where_clause_);
&AO..IAL_Object_API.Add_Description('&OBJECT', ial_object_desc_);
END;
/
COMMIT
/

Creating the IAL file

Configure Information Access Layer

Ial-files can be created manually, but it is recommended to generate the first file using the form IAL Object Developer. This will help with the syntactic disposition of the file, which will aid in the development.

The left side includes some hints about things to remember when developing.

• Open IAL Object Developer
• Give the object a name. Max 26 characters, underscores accepted but not spaces. This name is base for all database objects that are generated. The name should be in plural, ex CUSTOMERS
• Write down the component for documentation purposes, ex FNDBAS
• Write or insert a valid select statement. This will represent the "Transfer Part" of the IAL-Object which describes what data will be included. Full SQL syntax is supported and column aliases used with preference. The 'WHERE clause' to filter out data should also be included with the select statement in this section. This is described in more detail below.
• Enter a valid where clause. This will be applied to the end user view if the IAL is schedule as “full” or “partial” replication. Include all the columns that are used in the where clause in the select statement. Note that this section should not be used to add 'where clauses' to filter out data. That should be added along with the select statement above. You should add the row base security 'where clause' in this section. This will make sure that the correct data is shown to the user according to the security privileges.
• Save and/or deploy the file into the database and begin testing.

Note: The Sql Query Tool is very useful for writing initial select statements and for testing deployed IAL-Objects. In order to use SQL Query Tool you must log in as Appowner.

Writing Select -statements for IAL views

The full Oracle syntax for views are available when writing the IAL-view, but there are a few things that need to be remembered to make the file deployable.

• The IAL view selects data from the <IFSAPP> user, but the view itself should be deployed in <IFSINFO> user. All references to packages, views or tables etc. should therefore include schema prefix. In order to avoid coding in this, the prefix &AO. should be used, which is replaced at deploy time with the correct value.
• The view should be deployed in <IFSINFO>. The prefix &IAL. is used to prefix the creation of the view. Ex 'CREATE OR REPLACE VIEW &IAL..CUSTOMERS AS…'
• There are also several considerations that should be made.
• Performance is an issue, write the IAL-view against tables, rather than views whenever there may be lots of data.
• Use foreign key joins, rather than Get_-methods. This is especially important since it is good practice to include any and all foreign-key descriptions in the IAL-view. Remember, the view should 'show' as much information as possible. Sometimes it is necessary to use methods, because some calculations etc. may be necessary, and that is fine, but avoid unnecessary use of 'public' Get_-methods.
•  Column aliases should always be used, in order to show better names and to simplify translations.

Table and Index Declaration

It is not necessary to use a table. If it is not used, the replication options are disabled. If a table is used, it must follow the naming convention <OBJECTNAME>_TAB.

The CREATE TABLE is used to declare a table, primarily to assign properties as TABLESPACE and INDEXES. The table should be created in the form 'CREATE TABLE AS SELECT * FROM … WHERE 1=2' and the default is to add a WHERE expression that results in 'no hits'. This means that the table is not filled with data at installation time.

Indexes may be added freely whenever a good index is obvious. It is recommended to include at least primary key index. Use the naming standard <OBJECTNAME>_PK for primary key index, and <OBJECTNAME>_IXn for miscellaneous indexes.

Simple column indexes can be added and removed by the Administrator using the IAL Administration window, but if they are to be deployed as standard, use name convention <OBJECTNAME>_Xnn (where nn is the numeric order for columns, starting with 1). These indexes are optional.

It is not recommended to add constraints to the table since this can lead to unwanted results. Constraints should and are maintained in the application database only.

Object Registration

The first active part of the file contains the following statement.

begin
    &AO..IAL_Object_API.Disable('&OBJECT');
end;
/

That statement is there to make it possible to redeploy the file without having to drop the table.

Last in the file, the IAL-object should be enabled when installed, to make it available from the IAL Administration form etc. This is done by the section

begin
    &AO..IAL_Object_API.Enable('&OBJECT');
end;
/

which will initialize the object.

Creating a Partially Replicable IAL Object

A standard IAL Object, when using a table for replication, refreshes all data (full replication) whenever it is replicated. This may lead to a great deal of overhead. For that reason, it is possible to have an IAL Object as partially replicable.

The implementation of a partially replicable IAL Object requires,

• That there is a DATE column called OBJDATE in the IAL view (i.e. CUSTOMER_IAL has a column OBJDATE)
• That this column has a physical representation in the IFS-database that makes it possible to evaluate OBJDATE by comparing the time-stamp with last replication and by that deciding which data is newer. The IAL Object itself then remembers the last time of replication.

The IAL Object may then be refreshed with only the data that is new since last update by adding a where condition using column OBJDATE. Modified data is not replicated (this only works for new data). It is highly recommended that the source column in the IFS database is indexed for quicker refresh.

When the IAL Object is installed (IAL_Object_API.Enable()) the column OBJDATE is used to detect whether or not it is a partially replicable object or not. A partially replicable IAL object can of course also be used in a full replication manner, as determined by the administrator. Default mode when installed is, as always, to use live data (not replicated).

Deploy Object

The Sql File Executor can also be used for installing IAL-Objects. Whenever an .ial-file is deployed, it tries to connect to the configured IAL User (<IFSINFO>  instead of <IFSAPP>).

Localization

Localization of IAL object is done by creating a different set of files, one set for each language, and storing them in the distribution tree - under a top node folder named "IAL Objects" (see deployment below)
The parts of the IAL object definition file (.IAL) that should be translated are,

• Object name: the define statement for OBJECT at the top of the file. If the definition file is developed according to standards, this change should automatically change the name of the view, table, and indexes.
• Column alias: all column aliases used in the definition of the view should be translated.

Deployment

IAL Files should be placed together with the rest of the server files (API/APY). Build merges all ial-files into <component>.ial when building a distribution.