Apex Office Print integration

Uploading AOP Template Files and Defining SQL Queries for Report Layouts and Parameters

We have developed a page for uploading AOP template files, typically in .docx format, and defining SQL queries with parameters to streamline the creation and upload of new report layouts and queries.

Configuration of Hidden Items

On Page 0, we defined four hidden items for various purposes. In this use case, we utilize :P0_VALUE_1 to bind reporting parameters, specifically to pass the value for p_bestellnr.

Here’s how the SQL query integrates the parameter:

select * from table(zodis_aop.P_AOP_QUERIES.query_3035_1(p_bestellnr=>:P0_VALUE_1));

Each report query resides in a PL/SQL package, which dynamically returns the SQL query needed for the report. For example:

PL/SQL Package Definition

create or replace PACKAGE           P_AOP_QUERIES AS 

   FUNCTION             "QUERY_3035_1" (p_bestellnr number) return varchar2 sql_macro ;

END P_AOP_QUERIES;
/

create or replace PACKAGE  BODY P_AOP_QUERIES AS 
    FUNCTION QUERY_3035_1 (p_bestellnr NUMBER) RETURN VARCHAR2 SQL_MACRO
IS
BEGIN
   RETURN q'{
      SELECT JSON_ARRAYAGG(
         JSON_OBJECT(
            'filename' VALUE 'Rep3035_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HHMI'),
            'data' VALUE (
               SELECT JSON_ARRAYAGG(
                  JSON_OBJECT(
                     'header' VALUE CASE WHEN ls.lang = 0 THEN 'Header-DE' ELSE 'Header-EN' END,
                     'order_number' VALUE lbs.bestellnr,
                     'order_date' VALUE TO_CHAR(lbs.date, 'DD.MM.YYYY'),
                     'supplier_info' VALUE (
                        SELECT JSON_OBJECT(
                           'name' VALUE ls.name,
                           'address' VALUE sa.address || ', ' || so.city || ', ' || sl.country
                        )
                        FROM suppliers s
                        WHERE s.id = lbs.supplier_id
                     ),
                     'articles' VALUE (
                        SELECT JSON_ARRAYAGG(
                           JSON_OBJECT(
                              'article_number' VALUE a.number,
                              'description' VALUE a.description,
                              'price' VALUE a.price,
                              'quantity' VALUE a.quantity
                           )
                        )
                        FROM articles a
                        WHERE a.order_id = lbs.order_id
                     )
                  )
               )
               FROM orders lbs
               WHERE lbs.order_number = p_bestellnr
            )
         )
      ) AS aop_json
      FROM DUAL
   }';
END QUERY_3035_1;
END P_AOP_QUERIES;
/

Target Page Implementation

On the target page, we trigger this report (Report Nr. 5057) through an action. The workflow includes the following:

1. JavaScript Functionality: (AopWaiter)

   • Opens the newly generated report in an iframe.

   • Refreshes the associated Interactive Grid region after report generation.

2. AOP Plugin:

   • Queries the Blob content from the AOP_QUERIES table where the template and SQL query are stored.

   • Executes the SQL query, e.g., SELECT * FROM TABLE(zodis_aop.P_AOP_QUERIES.query_3035_1(p_bestellnr => :P0_VALUE_1)).

3. Optional Output Storage:

   • The generated output can optionally be saved using P_PRINT_JOB.SAVE_AOP_OUTPUT, which is useful for certain cases.

4. Watermarking PDF Output:

   • The generated PDF is watermarked based on configurations retrieved from a configuration table.

Dynamic Action Configuration:

we develop a little Js that opens on Iframe with the new generated Report and after that it refreshes a Interactive Grid Region

The Aop Call is the AOP DA Plugin we use here..