Designing AOP Templates: Single Header and Multi-Row Detail Records using SQL and Oracle APEX

Introduction: –

This document explains how to create an SQL-based data source for an AOP template that includes a single-value header followed by multi-row detail records. The following technologies are used to retrieve and populate data in the AOP template.

The following technologies have been used to Designing AOP Templates,

  • SQL
  • Oracle APEX
  • APEX Office Print(AOP)
  • Microsoft Word

Why we need to do: –

If there is a business requirement to dynamically create an SQL-based data source for an AOP template containing a single-value header followed by multi-row detail records, the following approach can be used to achieve this.

How do we solve:

The following steps will help to create SQL structured source query for AOP templates,

Step 1 :Install AOP as per their documentation in APEX workspace for using it as native report print functionality.

Link

https://www.apexofficeprint.com/docs/Server/installation/#installation-on-windows

Step 2: Create word template and SQL source code as per the requirement, in this case I have got the design as follows

{today_date}                    ——> Column name from SQL code
{fromhotel} to {tohotel} ——-> Column name from SQL code
{#passengers}                   ——-> Cursor to loop result set from SQL code—-# starts the cursor
{/passenegers}                 ——-> Cursor to loop result set from SQL code—-/ starts the cursor

In your AOP Word or Excel template, you would map the fields as follows:

Header Fields

filename: {{data.filename}}

today_date: {{data.today_date}}

Repeating Section (Detail Table)

  • You’d create a loop for passengers:

{{#data.passengers}} … {{/data.passengers}}

  • Inside the loop, fields like:

{{cars}}, {{car_seat}}, {{booking_no}}, {{passenger_name}}, {{fromhotel}}, etc.

Step 3: Create a button and add dynamic action to call the AOP template with records

Event: Click
Selection type: Button
Button: AOP
Action: UC-APEX OFFICE PRINT(AOP)-DA PLUGIN
Template type: AOP Template
Data source: SQL

Code:

Select
‘file1’ as “filename”,
cursor (select to_char(sysdate, ‘DY, MONTH DD YYYY’) as “today_date”,
cursor(select  rrp.car as “cars”,rrp.car|| ‘ – ‘ || rrp.seat as “car_seat”,
rrp.cc,
get_station_names(substr(rtgi.fromhotel,1,2)) as “fromhotel”,
get_to_station_names(substr(rtgi.tohotel,4,2)) as “tohotel”,
rrp.booking_no as “booking_no”,
rrp.last_name || ‘, ‘ || rrp.titl || ‘ ‘ || rrp.I as “passenger_name”,
rb.BRAND_INITIALS  as “company”
from rp_rail_pass rrp
join rp_car rc on rrp.car = rc.id
join rp_ts_guest_info rtgi on rtgi.booking=rrp.booking_no
join rp_brands rb on substr(rb.brand_initials,1,2)=rrp.company
join rp_segments rs on rrp.train_id=rs.train_id
where rownum<50
order by 1 asc) as “passengers”
from dual) as “data”
from dual;

A single-value header (like the filename and current date),

Followed by a multi-row detail section (like a list of passengers with car and booking info).

filename: A hardcoded string (‘file1’) that might be used in the template for document naming or display.

cursor (…): This cursor returns the “detail” rows and is aliased as “passengers”. It’s embedded under this “header” level.

Conclusion: 

Using SQL cursors with AOP in Oracle APEX allows you to seamlessly combine a single-value header with multi-row detail records, making it easy to generate dynamic, well-structured documents like reports or manifests. This approach streamlines report creation, improves accuracy, and delivers professional outputs with minimal effort.

 

 

 

 

Recent Posts