AOP Process
Overview
If you are using APEX 5.1, the Dynamic Action plug-in will always work, whereas the Process plug-in only works if the “Reload on Submit” attribute (of the page) is set to “Always” (note this attribute is new in 5.1). This is due to a change how APEX 5.1 is handling Page Processing. If you would import an APEX 5.0 app in 5.1 by default it’s set to Always reload on submit, but if you create a new app in 5.1 it’s set to “Only for Success” which causes the process type plug-in to fail.
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. Ex:
{"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
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.
Details of a customer e.g. for a letter
Cursor syntax:
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:
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.
Classic and/or Interactive Report/ Grid(s)
Enter a comma separated list of static id of one of following region types:
- Classic Report
- Interactive Report
- Interactive Grid
e.g. my_classic_report,ir1
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.
If you just want to get the data and do the styling yourself, you can use for classic report: {#<staticid>}{column}{/<static_id>} or for interactive report {#aopireportdata_1}{column}{/aopireportdata_1}. For interactive grid use {#aopigridoptions<staticid>} {column} {/aopigridoptions<static_id>}
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).
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.
Output Filename
The filename can be a hard coded string or reference an APEX item. It does not need to include the file extension. If a file extension is defined that is different the the output type selected, a new file extension will be appended to the filename
Examples
Static: my_file
APEX Item: &P1_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.