Dynamic Bursting ... no really!
- by Tim Dexter
If any of you have seen me or my colleagues present BI Publisher to you then we have hopefully mentioned 'bursting.' You may have even seen a demo where we talk about being able to take a batch of data, say invoices. Then split them by some criteria, say customer id; format them with a template; generate the output and then deliver the documents to the recipients with a click. We and especially I, always say this can be completely dynamic! By this I mean, that you could store customer preferences in a database. What layout would each customer like; what output format they would like and how they would like the document delivered. We (I) talk a good talk, but typically don't do the walk in a demo. We hard code everything in the bursting query or bursting control file to get the concept across. But no more peeps! I have finally put together a dynamic bursting demo! Its been minutes in the making but its been tough to find those minutes! Read on ...
It's nothing amazing in terms of making the burst dynamic. I created a CUSTOMER_PREFS table with some simple UI in an APEX application so that I can maintain their requirements. In EBS you have descriptive flexfields that could do the same thing or probably even 'contact' fields to store most of the info.
Here's my table structure:
Name Type
------------------------------ --------
CUSTOMER_ID NUMBER(6)
TEMPLATE_TYPE VARCHAR2(20)
TEMPLATE_NAME VARCHAR2(120)
OUTPUT_FORMAT VARCHAR2(20)
DELIVERY_CHANNEL VARCHAR2(50)
EMAIL VARCHAR2(255)
FAX VARCHAR2(20)
ATTACH VARCHAR2(20)
FILE_LOC VARCHAR2(255)
Simple enough right? Just need CUSTOMER_ID as the key for the bursting engine to join it to the customer data at burst time. I have not covered the full delivery options, just email, fax and file location. Remember, its a demo people :0) However the principal is exactly the same for each delivery type. They each have a set of attributes that need to be provided and you will need to handle that in your bursting query. On a side note, in EBS, you use a bursting control file, you can apply the same principals that I'm laying out here you just need to get the customer bursting info into the XML data stream so that you can refer to it in the control file using XPATH expressions.
Next, we need to look up what attributes or parameters are required for each delivery method. that can be found in the documentation here.
Now we know the combinations of parameters and delivery methods we can construct the query using a series a decode statements:
select distinct cp.customer_id "KEY",
cp.template_name TEMPLATE,
cp.template_type TEMPLATE_FORMAT,
'en-US' LOCALE,
cp.output_format OUTPUT_FORMAT,
'false' SAVE_FORMAT,
cp.delivery_channel DEL_CHANNEL,
decode(cp.delivery_channel,'FILE', cp.file_loc
, 'EMAIL', cp.email
, 'FAX', cp.fax) PARAMETER1,
decode(cp.delivery_channel,'FILE', c.cust_last_name||'_orders.pdf'
,'EMAIL','[email protected]'
,'FAX', 'faxserver.com') PARAMETER2,
decode(cp.delivery_channel,'FILE',NULL
,'EMAIL','[email protected]'
,'FAX', null) PARAMETER3,
decode(cp.delivery_channel,'FILE',NULL
,'EMAIL','Your current orders'
,'FAX',NULL) PARAMETER4,
decode(cp.delivery_channel,'FILE',NULL
,'EMAIL','Please find attached a copy of your current orders with BI Publisher, Inc'
,'FAX',NULL) PARAMETER5,
decode(cp.delivery_channel,'FILE',NULL
,'EMAIL','false'
,'FAX',NULL) PARAMETER6,
decode(cp.delivery_channel,'FILE',NULL
,'EMAIL','[email protected]'
,'FAX',NULL) PARAMETER7
from cust_prefs cp,
customers c,
orders_view ov
where cp.customer_id = c.customer_id
and cp.customer_id = ov.customer_id
order by cp.customer_id
Pretty straightforward, just need to test, test, test, the query and ensure it's bringing back the correct data based on each customers preferences. Notice the NULL values for parameters that are not relevant for a given delivery channel.
You should end up with bursting control data that the bursting engine can use:
Now, your users can run the burst and documents will be formatted, generated and delivered based on the customer prefs.
If you're interested in the example, I have used the sample OE schema data for the base report. The report files and CUST_PREFS table are zipped up here. The zip contains the data model (.xdmz), the report and templates (.xdoz) and the sql scripts to create and load data to the CUST_PREFS table. Once you load the report into the catalog, you'll need to create the OE data connection and point the data model at it. You'll probably need to re-point the report to the data model too.
Happy Bursting!
