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!
