AOP Dynamic Action

Overview

Settings

Template Type

AOP Template
AOP will generate a Word document with a starting template based on the data (JSON) that is submitted.
Documentation is also added on the next page(s) that describe the functions AOP will understand.

AOP Report
AOP will generate a report for you based on the Print Attributes specified in the given region (in case multiple regions are specified, the first region is used for the Print Attributes).

APEX Report
From APEX 20.2 onwards, APEX supports the creation of native Excel and PDF of a region. Using APEX Report will use this feature. Note: only 1 region (static id) can be rendered. An alternative to APEX Report is an AOP Report, which uses the AOP rendering and which supports also HTML expressions.

APEX Report (Data only)
From APEX 20.2 onwards, APEX supports the creation of native Excel and PDF of a region. Using APEX Report will use this feature. Data only will remove highlights etc. and just export the data. Note: only 1 region (static id) can be rendered. An alternative to APEX Report is an AOP Report, which uses the AOP rendering and which supports also HTML expressions.

Static Application Files
Enter the filename of the file uploaded to your Shared Components > Static Application Files
e.g. aop_template_d01.docx

Static Workspace Files
Enter the filename of the file uploaded to your Shared Components > Static Workspace Files
e.g. aop_template_d01.docx

SQL
Query that returns two columns: template_type and file (in this order)
- template_type: docx, xlsx, pptx, html, md
- file: blob column

PL/SQL Function (returning SQL)
Enter a PL/SQL procedure that returns a select statement with two columns: template_type and file (in this order)
- template_type: docx, xlsx, pptx, html, md
- file: blob column
Note that you can use bind variables e.g. :PXX_ITEM.

PL/SQL Function (returning JSON)
Return JSON object with following format:

{
  "file":"clob base 64 data",
  "template_type":"docx,xlsx,pptx"
}

JSON
Add the JSON of "template" in here.
e.g.:

{"filename":"aop_template_d01.docx", "template_type":"docx"}

Filename (with path relative to AOP server)
Enter the path and filename of the template which is stored on the same server AOP is running at.

Filename (with database directory)
Enter the Database directory colon separated with the filename.
e.g. AOP_TEMPLATE:my_template.docx

Example how to create Database directory:

CREATE DIRECTORY AOP_TEMPLATE AS '/home/oracle/aop_template';

URL (call from DB)
Enter the url to your template in docx, xlsx or pptx.
e.g. https://www.apexofficeprint.com/templates/aop_template_d01.docx

Always make sure your url ends with the filename. E.g. for Google Drive add to the end of the url &aop=.docx

This call is done from the database, so the database server needs to have access to the url.

URL (call from AOP)
Enter the url to your template in docx, xlsx or pptx.
e.g. https://www.apexofficeprint.com/templates/aop_template_d01.docx

Always make sure your url ends with the filename. E.g. for Google Drive add to the end of the url &aop=.docx

This call is done from AOP, so the AOP server needs to have access to the url.

None
To only append or prepend files or to fill PDFs, set the template type to none.

Template Source

The templates need to be of format: Word (docx), Excel (xlsx), PowerPoint (pptx), HTML (html), Text (txt), CSV (csv) or Markdown (md).

Examples
Reference a file in Shared Components > Static Application Files or Static Workspace Files

aop_template.docx

In case you want to specify the template dynamically, use the SQL option with following:

select case 
        when mime_type = 'application/octet-stream'
        then substr(file_name, instr(file_name,'.',-1)+1) 
        else mime_type
        end as template_type, 
        file_content as template_blob
  from apex_application_static_files
 where file_name    = :PXX_YOUR_ITEM
   and application_id = :APP_ID

Reference a file on the server. Include the path relative to the AOP executable.

Reference a url, accessible from the database.

Reference a url, accessible from the AOP Server.

Specify the region static id where the Print Attributes are specified for the AOP Report.

Reference a database directory and file on the database server. Format: DIRECTORY:FILENAME e.g. AOP_TEMPLATE:my_template.docx

Data Type

SQL

Enter a select statement in which you can use a cursor to do nested records. Use "" as alias for column names to force lower case column names.

You can also use SQL that generates JSON.

Note that you can use bind variables e.g. :PXX_ITEM.

Cursor syntax:

Details of a customer e.g. for a letter

select
    'file1' as "filename",
    cursor
    (select 
         c.cust_first_name as "cust_first_name",
         c.cust_last_name as "cust_last_name",
         c.cust_city as "cust_city"
       from demo_customers c
      where c.customer_id = :PXX_ITEM 
    ) as "data"
from dual

Native JSON database functionality:

Details of a customer e.g. for a letter

select 
  json_arrayagg( 
    json_object( 
      'filename' value 'file1', 
      'data'     value (select 
                          json_arrayagg(
                            json_object( 
                              'cust_first_name' value c.cust_first_name, 
                              'cust_last_name'  value c.cust_last_name,
                              'cust_city'       value c.cust_city 
                            )
                          )  
                          from demo_customers c
                          where c.customer_id = :PXX_ITEM
                        )  
    )
  ) as aop_json
  from dual 

PL/SQL Function (returning SQL)

Enter a PL/SQL procedure that returns as select statement in which you can use a cursor to do nested records. Use "" as alias for column names to force lower case column names.

Note that you can use bind variables e.g. :PXX_ITEM.

PL/SQL Function (returning JSON)
Return JSON as defined in the URL example above. (see example in help of Data Source)

URL (returning JSON)
The Source should point to a URL that returns a JSON object with following format: { "filename": "file1", "data":[{...}] } If the URL is using an APEX/ORDS REST call it will automatically be wrapped with additional JSON: {"items":[...]} This is ok as the plugin removes it for you.

Region(s): Classic Report, Interactive Report/Grid, SVG, Canvas, HTML, Other
Enter a comma separated list of static id of one of following region types:

  • Classic Report
  • Interactive Report
  • Interactive Grid
  • JET Chart
  • Any other region (static html, div, plugin) which you want to include

e.g. my_classic_report,ir1,jet2,my_div

In your template you can include the entire Interactive Report by using the tag {&interactive_1} for the first interactive report, {&interactive_2} for the second etc.

To include an Interactive Grid as you see on the screen you use the tag {&interactive_}.

If you just want to get the data and do the styling yourself, you can use for classic report: {#}{column}{/} or for interactive report {#aopireportdata_1}{column}{/aopireportdata_1}. For interactive grid use {#aopigridoptions_} {column} {/aopigridoptions_}

To include the svg(s) in the div use {%region} and specify in the Custom Attributes of the region: aop-region-as="client_svg"

To include a canvas in the div use {%region} and specify in the Custom Attributes of the region: aop-region-as="client_canvas"

To include the html in the div use {_region} and specify in the Custom Attributes of the region: aop-region-as="server_html" or aop-region-as="client_html". AOP will translate the html into native Word styling either by passing the HTML defined in the Region Source (server_html) or defined after rendering on the page (client_html).

To include a screenshot of the div use {%region}, you don't have to specify anything or you can specify in the Custom Attributes of the region: aop-region-as="client_screenshot".

XML (data part)

Data in XML format. No need to specify files or filename.

JSON (data part)

Data in JSON format. No need to specify files or filename.

JSON (REST, GraphQL, Raw)

Enter JSON in format

[ 
  { 
    "filename": "xxx.docx", 
    "datasource": "graphql", 
    "query": "{human(id:\"1000\"){name height}", 
    "endpoint": "https://api.graph.cool/simple/v1/", 
    "headers": [{"Content-Type":"application/json"},{"Custom-Auth-Token":"xyz"}] 
  } 
]

The files part of the AOP structure will be filled in with the value specified here.

None

To only append or prepend files or to work with PDFs, set the data type to none.

Data Source

SQL

A SQL statement is the easiest to use, either by using the cursor syntax or native JSON database functionality.

Images need to be base64 encoded. You can reference items by using :ITEM

Examples
Cursor syntax

List of all customers e.g. to send letter to all

select
    'file1' as "filename",
    cursor
    (select 
       cursor(select
                  c.cust_first_name as "cust_first_name",
                  c.cust_last_name as "cust_last_name",
                  c.cust_city as "cust_city" 
                from demo_customers c) as "customers"
       from dual) as "data"
from dual 

Details of all orders of a customer e.g. for invoices

select
  'file1' as "filename", 
  cursor(
    select
      c.cust_first_name as "cust_first_name",
      c.cust_last_name as "cust_last_name",
      c.cust_city as "cust_city",
      cursor(select o.order_total as "order_total", 
                    'Order ' || rownum as "order_name",
                cursor(select p.product_name as "product_name", 
                              i.quantity as "quantity",
                              i.unit_price as "unit_price", 
                              APEX_WEB_SERVICE.BLOB2CLOBBASE64(p.product_image) as "image"
                         from demo_order_items i, demo_product_info p
                        where o.order_id = i.order_id
                          and i.product_id = p.product_id
                      ) "product"
               from demo_orders o
              where c.customer_id = o.customer_id
            ) "orders"
    from demo_customers c
    where customer_id = :P1_CUSTOMER_ID
  ) as "data"
from dual
Native JSON database functionality

Details of a customer e.g. for a letter

select 
  json_arrayagg( 
    json_object( 
      'filename' value 'file1', 
      'data'     value (select 
                          json_arrayagg(
                            json_object( 
                              'cust_first_name' value c.cust_first_name, 
                              'cust_last_name'  value c.cust_last_name,
                              'cust_city'       value c.cust_city 
                            )
                          )  
                          from demo_customers c
                          where c.customer_id = 1
                        )  
    )
  ) as aop_json
  from dual 

Details of all orders of a customer e.g. for invoices

select 
json_arrayagg( 
json_object( 
'filename' value 'file1', 
'data'     value (
  select 
    json_arrayagg(
      json_object( 
        'cust_first_name' value c.cust_first_name, 
        'cust_last_name'  value c.cust_last_name,
        'cust_city'       value c.cust_city, 
        'orders'          value (
            select 
              json_arrayagg(
                json_object(                               
                  'order_total' value o.order_total, 
                  'order_name'  value 'Order ' || rownum,
                  'order_lines' value (
                      select 
                        json_arrayagg(
                          json_object(                               
                            'product_name' value p.product_name, 
                            'quantity'     value i.quantity,
                            'unit_price'   value i.unit_price
                          )
                        returning clob)      
                        from demo_order_items i, demo_product_info p
                        where o.order_id = i.order_id
                          and i.product_id = p.product_id
                    )
                )
              returning clob)      
              from demo_orders o
            where o.customer_id = c.customer_id
          )
      )
    returning clob)  
    from demo_customers c
    where c.customer_id = :P1_CUSTOMER_ID
  )
)
returning clob) as aop_json
from dual

PL/SQL Function (returning SQL)

By using PL/SQL to create your own SQL or JSON, you're more flexible. You can use bind variables and page items.

declare
  l_return clob;
begin
  l_return := q'[
    select
      'file1' as "filename",
      cursor(
        select
          c.cust_first_name as "cust_first_name",
          c.cust_last_name as "cust_last_name",
          c.cust_city as "cust_city",
          cursor(
            select
              o.order_total as "order_total",
              'Order ' || rownum as "order_name",
              cursor(
                select
                  p.product_name as "product_name",
                  i.quantity as "quantity",
                  i.unit_price as "unit_price",
                  APEX_WEB_SERVICE.BLOB2CLOBBASE64(p.product_image) as "image"
                from
                  demo_order_items i, demo_product_info p
                where
                  o.order_id = i.order_id
                  and i.product_id = p.product_id
                    ) "product"
            from
              demo_orders o
            where
              c.customer_id = o.customer_id
                ) "orders"
        from
          demo_customers c
        where
          customer_id = :P4_CUSTOMER_ID
            ) as "data"
    from dual
  ]';
  return l_return;
end;

PL/SQL Function (returning JSON)

By using PL/SQL to create your own SQL or JSON, you're more flexible. You can use bind variables and page items.

declare
  l_cursor sys_refcursor;
  l_return clob;
begin
  apex_json.initialize_clob_output(dbms_lob.call, true, 2) ;
  open l_cursor for 
  select 'file1' as "filename",
  cursor
    (select
        c.cust_first_name as "cust_first_name",
        c.cust_last_name  as "cust_last_name" ,
        c.cust_city       as "cust_city"      ,
        cursor
        (select
            o.order_total      as "order_total",
            'Order ' || rownum as "order_name" ,
            cursor
            (select
                p.product_name                                    as "product_name",
                i.quantity                                        as "quantity"    ,
                i.unit_price                                      as "unit_price"  ,
                apex_web_service.blob2clobbase64(p.product_image) as "image"
              from
                demo_order_items i,
                demo_product_info p
              where
                o.order_id       = i.order_id
                and i.product_id = p.product_id
            ) "product"
        from
          demo_orders o
        where
          c.customer_id = o.customer_id
        ) "orders"
      from
        demo_customers c
      where
        customer_id = :P4_CUSTOMER_ID
    ) as "data" 
  from dual;
  apex_json.write(l_cursor) ;
  l_return := apex_json.get_clob_output;
  return l_return;
end

URL (returning JSON)

Specify the XML or JSON.

For type URL:

Create (in for example ORDS) a new REST web service with a GET, source type "Query" and format "JSON".

Put the url in this text box, e.g. https://www.apexofficeprint.com/ords/aop/get_data/1

Here's an example of a query which contains a parameter too:

select 
  'file1' as filename,  
  cursor( 
    select
      c.cust_first_name, 
      c.cust_last_name, 
      c.cust_city, 
      cursor(select o.order_total, 'Order ' || rownum as order_name,
                cursor(select p.product_name, i.quantity, i.unit_price, APEX_WEB_SERVICE.BLOB2CLOBBASE64(p.product_image) as image 
                         from demo_order_items i, demo_product_info p 
                        where o.order_id = i.order_id 
                          and i.product_id = p.product_id 
                      ) product                 
               from demo_orders o 
              where c.customer_id = o.customer_id 
            ) orders 
    from demo_customers c 
    where customer_id = :id 
  ) as data 
from dual 

XML (data part)

Specify the XML or JSON.

For type URL:

Create (in for example ORDS) a new REST web service with a GET, source type "Query" and format "JSON".

Put the url in this text box, e.g. https://www.apexofficeprint.com/ords/aop/get_data/1

Here's an example of a query which contains a parameter too:

select 
  'file1' as filename,  
  cursor( 
    select
      c.cust_first_name, 
      c.cust_last_name, 
      c.cust_city, 
      cursor(select o.order_total, 'Order ' || rownum as order_name,
                cursor(select p.product_name, i.quantity, i.unit_price, APEX_WEB_SERVICE.BLOB2CLOBBASE64(p.product_image) as image 
                         from demo_order_items i, demo_product_info p 
                        where o.order_id = i.order_id 
                          and i.product_id = p.product_id 
                      ) product                 
               from demo_orders o 
              where c.customer_id = o.customer_id 
            ) orders 
    from demo_customers c 
    where customer_id = :id 
  ) as data 
from dual 

JSON (data part)

Specify the XML or JSON.

For type URL:

Create (in for example ORDS) a new REST web service with a GET, source type "Query" and format "JSON".

Put the url in this text box, e.g. https://www.apexofficeprint.com/ords/aop/get_data/1

Here's an example of a query which contains a parameter too:

select 
  'file1' as filename,  
  cursor( 
    select
      c.cust_first_name, 
      c.cust_last_name, 
      c.cust_city, 
      cursor(select o.order_total, 'Order ' || rownum as order_name,
                cursor(select p.product_name, i.quantity, i.unit_price, APEX_WEB_SERVICE.BLOB2CLOBBASE64(p.product_image) as image 
                         from demo_order_items i, demo_product_info p 
                        where o.order_id = i.order_id 
                          and i.product_id = p.product_id 
                      ) product                 
               from demo_orders o 
              where c.customer_id = o.customer_id 
            ) orders 
    from demo_customers c 
    where customer_id = :id 
  ) as data 
from dual 

JSON (REST, GraphQL, Raw)

Specify the XML or JSON.

For type URL:

Create (in for example ORDS) a new REST web service with a GET, source type "Query" and format "JSON".

Put the url in this text box, e.g. https://www.apexofficeprint.com/ords/aop/get_data/1

Here's an example of a query which contains a parameter too:

select 
  'file1' as filename,  
  cursor( 
    select
      c.cust_first_name, 
      c.cust_last_name, 
      c.cust_city, 
      cursor(select o.order_total, 'Order ' || rownum as order_name,
                cursor(select p.product_name, i.quantity, i.unit_price, APEX_WEB_SERVICE.BLOB2CLOBBASE64(p.product_image) as image 
                         from demo_order_items i, demo_product_info p 
                        where o.order_id = i.order_id 
                          and i.product_id = p.product_id 
                      ) product                 
               from demo_orders o 
              where c.customer_id = o.customer_id 
            ) orders 
    from demo_customers c 
    where customer_id = :id 
  ) as data 
from dual 

Region Static Id(s)

Define one or more Static Id(s) of the report region. Static ids should be separated by a comma. e.g. ir1,ir2

You can set the Static ID of the region in the region settings (Advanced section). Under Static ID in the Custom Attributes you can define how AOP should behave: aop-region-as="server_html / client_canvas / client_svg / client_html / client_screenshot". Depending this setting, AOP will render the HTML from the server or it will take from client the canvas, svg, html or take a screenshot of the region.

Special

Specific features of APEX Office Print

Available options include:

Treat all numbers as strings

There's a limitation in APEX with the cursor() statement in SQL that it doesn't remember which datatype the column is in. So when doing to_char(0.9,'990D00') it will return 0.9 as number instead of as string '0.90'. To resolve this, enable this checkbox and concatenate your number with '!FMT!' e.g. '!FMT!'||to_char(35, '990D00') - !FMT! stands for format.

Alternatively if you format your number with the currency sign to_char(35,'FML990D00') Oracle will recognise it as a string and you don't need to use this checkbox.

Report as Labels

Check this box in case you want to use the Classic or Interactive Report data source but print them as Labels (using the Mailings feature in Word).

IR/IG: Show Filters on top

When there're filters applied to the Interactive Report, this checkbox will print them above the report.

IR/IG: Show Highlights on top

When there're highlights applied to the Interactive Report, this checkbox will print them above the report.

IR/IG: Show header with filter (Excel)

When exporting the Interactive Report to Excel, show the header with filter option.

IR/IG: Use Saved Report instead of Report in Session

When defining the Interactive Report source ir1|my_saved_report, the "my_saved_report" will be used, even when the person is looking at a different report in his session session.

IR/IG: Repeat header on every page

When the table spans multiple pages, the header row will be repeated on every page.

Obfuscate data

Obfuscate data, so it's easy to send examples to others.

Init PL/SQL Code

You can define global variables of the aop_api_pkg in this area.

Available variables:

-- Global variables
g_output_filename         varchar2(100) := null;
g_language                varchar2(2)   := 'en';  -- Language can be: en, fr, nl, de
g_rpt_header_font_name    varchar2(50)  := '';    -- Arial - see https://www.microsoft.com/typography/Fonts/product.aspx?PID=163
g_rpt_header_font_size    varchar2(3)   := '';    -- 14
g_rpt_header_font_color   varchar2(50)  := '';    -- #071626
g_rpt_header_back_color   varchar2(50)  := '';    -- #FAFAFA
g_rpt_header_border_width varchar2(50)  := '';    -- 1 ; '0' = no border
g_rpt_header_border_color varchar2(50)  := '';    -- #000000
g_rpt_data_font_name      varchar2(50)  := '';    -- Arial - see https://www.microsoft.com/typography/Fonts/product.aspx?PID=163
g_rpt_data_font_size      varchar2(3)   := '';    -- 14
g_rpt_data_font_color     varchar2(50)  := '';    -- #000000
g_rpt_data_back_color     varchar2(50)  := '';    -- #FFFFFF
g_rpt_data_border_width   varchar2(50)  := '';    -- 1 ; '0' = no border 
g_rpt_data_border_color   varchar2(50)  := '';    -- #000000
g_rpt_data_alt_row_color  varchar2(50)  := '';    -- #FFFFFF for no alt row color, use same color as g_rpt_data_back_color

Examples

aop_api_pkg.g_output_filename      := 'output';
aop_api_pkg.g_output_filename      := v('P1_FILENAME');
aop_api_pkg.g_rpt_header_font_size := '12';
aop_api_pkg.g_rpt_header_font_size := '12';
aop_api_pkg.g_prepend_files_sql    := q'[select filename, mime_type, template_blob as file_blob from aop_template where filename like 'PREPEND%' order by filename]';
aop_api_pkg.g_append_files_sql     := q'[select filename, mime_type, template_blob as file_blob from aop_template where filename like 'APPEND%' order by filename]';

Output Type

Available options include:

  • Word (docx)
  • Excel (xlsx)
  • PowerPoint (pptx)
  • PDF (pdf)
  • HTML (html)
  • Markdown (md)
  • Text (txt)
  • Rich Text Format (rtf)
  • CSV (csv) - Comma separated values file. Text file containing information separated by commas.
  • One Page PDF (pdf)
  • PDF Form Fields (pdf) - Get the form fields defined on a PDF.
  • Word with macros (docm)
  • Excel with macros (xlsm)
  • PowerPoint with macros (pptm)
  • Calendar (ics)
  • Calendar (ifb)
  • OpenDocument Text (odt) - OpenOffice Word
  • OpenDocument Spreadsheet (ods) - OpenOffice Excel
  • OpenDocument Presentation (odp) - OpenOffice PowerPoint
  • XML (xml) - Extensible Markup Language is a markup language that defines a set of rules for encoding documents in a format that is both human-readable and machine-readable.
  • JSON (json) - JSON stands for JavaScript Object Notation. JSON is a lightweight data-interchange format.
  • Defined by APEX Item

Output To

By default the file that's generated by AOP, will be downloaded by the Browser and saved on your harddrive.

Available options include:

Browser (file)

Procedure

This option will call a procedure in a specific format. This option is useful in case you don't need the file on your own harddrive, but for example you want to mail the document automatically. In that case you can create a procedure that adds the generated document as an attachment to your apex_mail.send.

Procedure and Browser (file)

This option allows you to call a procedure first and next download the file to your harddrive. An example is when you first want to store the generated document in a table before letting the browser to download it.

Inline Region (pdf/html/md/txt only)

add data-aop-inline-pdf="Name of Dynamic Action" or data-aop-inline-txt="Name of Dynamic Action" to a region, div, textarea of other.

Procedure and Inline Region (pdf/html/md/txt only)

This option allows you to call a procedure first and next show the output in a region. Add data-aop-inline-pdf="Name of Dynamic Action" or data-aop-inline-txt="Name of Dynamic Action" to a region, div, textarea of other.

Directory (on AOP Server)

Save the file to a directory specified with g_output_directory. The default directory on the AOP Server is outputfiles.

Directory (on Database Server)

Save the file to a database directory specified with g_output_directory.

Example how to create Database directory:

CREATE DIRECTORY AOP_TEMPLATE AS '/home/oracle/aop_output';

Cloud (Dropbox, Google Drive, OneDrive, Amazon S3)

Save the output straight in a directory on the cloud.

Use following global variables to define the provider and location. - g_cloud_provider (dropbox, gdrive, onedrive, amazon_s3) - g_cloud_location (directory, or bucket with directory on Amazon) - g_cloud_access_token (oauth token)

Defined in Post Process (e.g. Printer)

Use when an external post-process command is specified and the output is only needed to be passed to that process. For example when you want to print directly to a Printer or call an OS command.