ALGEN

What is ALGEN?

ALGEN stands for ApexLayoutGenerator and is an application designed by the ApexNinjas.com team to generate customized Report Layouts, in the XSF-FO format. These reports are used to print PDF documents from APEX application using the OC4J application server and the FOP formater.

What are Report Layouts?

Report Layouts are used in conjunction with a report region or report query to render data in a printer-friendly format, such as PDF, Word or Excel. A report layout can be designed using the Template Builder Word plug-in and uploaded as a file of type RTF or XSL-FO. Report regions use a generic XSL-FO layout, which is customizable. A Report Query is a list of one or more data sources, usually SQL statements. Each report query has an associated template, or Report Layout, the default APEX Generic Report Layout, but also user-defined Report Layouts.

You can manage Report Layouts and Queries from Application Builder -> Shared Components:

How to setup APEX PDF printing?

There are multiple ways to setup APEX PDF printing, either using BI Publisher or using a web server with FOP formatting capabilities, such as Cocoon or OC4J. Out choice was OC4J. How to setup APEX PDF printing with OC4J, here. Further information on how to customize PDF printing, here and here.

After folowing the tutorials mentioned above, your APEX instance should be able to print PDF documents using customized Report Layouts. Besides manually customizing the XSL-FO layouts, one could use ALGEN for this purpose.

How does ALGEN work?

ALGEN is an APEX application that is deployed in it’s own workspace and schema, called SCH_ALGEN. During installation time, SCH_ALGEN must be granted some rights, in order to query the APEX views. Also, before creating a layout, the report layouts must be created, with some restrictions. Using the created report layouts, the user is able to generate report layouts, by adding default objects (images, tables, text) to the layout header, footer and body, and by manipulating the CSS.

The Report Layout is generated in the final screen. The layout must be copied from there and pasted in the corresponding application Report Layout, in the “Page Template” region.

Step 1. Create a new Layout for the available workspaces and applications

Step 2. Customize the layout

Step 3. Generate the XSL-FO layout

Step 4. Copy the generated layout into the APEX Report Layout

ALGEN installation

Download the ALGEN application from the download page mentioned at the end of this page.  ALGEN works only for APEX version 4.0. For other version, see the Known Issues and Limitations section, subsection “Adapting ALGEN to other APEX versions”. Unzip algen.zip to a convenient folder.

Log in sqlplus with the sys user in sqlplus:

1. Edit create_tablespaces.sql with your custom paths for the tablespaces. Don’t modify the tablespaces names! Execute create_tablespaces.sql

2. Edit create_user.sql and modify the desired password. Don’t modify the schema name! Execute create_user.sql

3. Execute grants.sql

Log in as sch_algen in sqlplus:

Execute export.sql

Execute insert_table_data.sql

Log in Application Express Administration Services (f?p=4550:10)

Create the workspace ALGEN and assign to it the SCH_ALGEN schema.

Log in the Application Express Login (f?p=4550:1) using for the workspace ALGEN and the user and password created previously

import f900.sql

If you are installing ALGEN in the runtime environment, log in as sys in sqlplus and execute install_apex_app.sql and then f900_runtime.sql.

ALGEN authentication. Create an user

The ALGEN application uses the standard APEX authentication schema. To login the application, first create an user, or remove the authentication from the ALGEN 900 application.

To create an user, login the APEX Administration Services (f?p=4550:10) and create a user for the previously created workspace ALGEN. (Manage Workspaces -> Manage Developers & Users -> Create/Edit User).

Use this user to login ALGEN: http://yourserver:port/dadbase/f?p=900:10

Limit the access to ALGEN

The default installation setup will make only the Oracle demo HR schema to SCH_ALGEN. This means that you will only be able to see applications and report queries generated for the workspace that has the first provisioned schema, the HR schema. To enable/disable ALGEN access to other schemas (that act as first provisioned schemas to workspaces), modify the views:

VD_WORKSPACES, VD_WORKSPACES_LOV

For example, ALGEN can see all Report Queries from all the applications from DEMO1 and DEMO2 workspaces, whic have the first provisioned schemas SCH_DEMO1 and SCH_DEMO2:

create or replace view sch_algen.vd_workspaces as
select
  fc.ID
 ,fc.PROVISIONING_COMPANY_ID
 ,fc.SHORT_NAME
 ,fc.FIRST_SCHEMA_PROVISIONED
from APEX_040000.WWV_FLOW_COMPANIES fc
where fc.FIRST_SCHEMA_PROVISIONED in ('DEMO1','DEMO2');
CREATE OR REPLACE VIEW sch_algen.VD_WORKSPACES_LOV AS
select
  fc.SHORT_NAME as DISPLAY_NAME
 ,fc.provisioning_company_id as SELECT_VALUE
from APEX_040000.WWV_FLOW_COMPANIES fc
where fc.FIRST_SCHEMA_PROVISIONED in ('DEMO1','DEMO2');

Known Issues and Limitations

Adapting ALGEN to other APEX versions

If your are installing ALGEN on a different version of APEX, follow the steps:

1. Modify the views and run the sql statements in the SCH_ALGEN schema. Just modify the SQL statements replacing the APEX_040000 schema namw with the corresponding schema to your APEX version.

VD_FLOWS_LOV, VD_FLOWS, VD_WORKSPACES, VD_WORKSPACES_LOV

2. Give the appropriate grants (xxxxx stands for your Apex version):

grant select on APEX_xxxxxx.WWV_FLOW_COMPANIES to sch_algen;
grant select on APEX_xxxxxx.WWV_FLOW_SHARED_QUERIES to sch_algen;
grant select on APEX_xxxxxx0.WWV_FLOW_REPORT_LAYOUTS to sch_algen;
grant select on APEX_xxxxxx.WWV_FLOW_SHARED_QRY_SQL_STMTS to sch_algen;
grant select on APEX_xxxxxx.WWV_FLOWS to sch_algen;

Report Query limitations

The Report Queries can only be used by ALGEN if they strictly follow the rules:

1. They are prefixed by the schema name

ex: HR.EMPLOYEES instead just EMPLOYEES

2. They have no joins in the sql statement. Use views to encapsulate joins or other complex queries

For example, the following Report Query will not be read by ALGEN and the application will result in an error:

select a.*, b.*
from HR.EMPLOYEES.a, HR.DEPARTMENTS.b
where a.DEPARTMENT_ID = b.DEPARTMENT_ID;

Instead, create a view like this:

create or replace view VW_JOIN as select * from
(select a.*, b.*
from HR.EMPLOYEES.a, HR.DEPARTMENTS.b
where a.DEPARTMENT_ID = b.DEPARTMENT_ID)

And modify the Report Query like this:

select * from HR.VW_JOIN