Thursday Sep 13, 2012

Dynamic Bursting ... no really!

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','cc@BIPublisher.com'
      ,'FAX', 'faxserver.com') PARAMETER2,
     decode(cp.delivery_channel,'FILE',NULL
      ,'EMAIL','customer_service@bipublisher.com'
      ,'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','reply@bipublisher.com'
      ,'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!

Friday Aug 26, 2011

Bursting and Hairloss

After last week's post on small successes after much pain and misery. Still pulling your hair out over minor things? You should be so lucky to have some to pull. I lost mine years ago, I blame my kids. In fact here's one of my kids unconsciously rubbing it in on a recent'ish diving expedition. He's the one on the right, in case you were wondering who the wrinkled old man was on the left ... yes, that's me :0( I've heard of sucking your gut in for a photograph; I now need to remember to pull the skin tight on the back of my head too :0)

However, I take comfort in the fact that:

1. He's mine
2. Both of his grandfathers and 3 of his great grandfathers are/were follicly challenged.
So he and his brother will be bald as coots by the time they hit their 20s ... ha, join the club boys!

The reason for my hair pulling? Getting bursting working in 11g. Now, Leslie, 'Super Documenter' to the rescue. It's there in black and white, just RTFM Tim! My words, not hers!

My bursting query in 10g for a simple demo starts:

select  d.department_name KEY,
...
...

But this throws an error in 11g along the lines of:

Syntax error: Encountered "KEY" at line 2, column 24

Simple fix and it is documented in the 11g user docs.

select  d.department_name as "KEY",
...
...

Not sure if this is a BIP or database choke, I'm suspecting the db. Anyhoo, change the select statement and my bursting is working again!

Tuesday Apr 29, 2008

EBS Bursting 101 from NZ

[Read More]

Tuesday Sep 25, 2007

Bursting Barcodes

[Read More]
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today