eBS BIP Bursting Control files presentation from STR Software

If you're still struggling with bursting control files in spite of  the detailed BIP documentation, here's a quick primer (and a plug for their software) from Brent Lowe of STR Software.


http://www.strsoftware.com/resources/generate-documents-from-my-erp/understanding-bi-publisher-bursting-control-files-in-oracle-ebs-r12/

Sortable Excel Output - Rows to Columns using RTF templates in BI Publisher

I had a math professor who said stuff like "The problem is always tough until you find the solution", which makes sense, if you don't think about it. 


I was tasked with solving what seemed like a tough problem. An existing BIP report used this XML structure to generate MS Excel output using an RTF template.


G_MAIN
  G_CUSTOMER
    CUSTOMER
    TOTAL_SALES
    G_PRODUCTS
       G_PRODUCT
           PCT_TOTAL
           SALES_AMT


The output from a BIP report needed to be changed so that it went from this

Customer	Total $		Breakdown by Product
		Product	% of Total	Sales $
Cust1	100,000.00	Prod1	25.00%	25,000.00
		Prod2	30.00%	30,000.00
		Prod4	45.00%	45,000.00
Cust2	200,000.00	Prod2	40.00%	80,000.00
		Prod3	25.00%	50,000.00
		Prod5	35.00%	70,000.00
Cust3	1,000,000.00	Prod1	30.00%	300,000.00
		Prod3	40.00%	400,000.00
		Prod4	15.00%	150,000.00
		Prod5	15.00%	150,000.00
Cust4	2,000,000.00	Prod1	20.00%	400,000.00
		Prod2	20.00%	400,000.00
		Prod3	20.00%	400,000.00
		Prod4	20.00%	400,000.00
		Prod5	20.00%	400,000.00

to this

Customer	Total $	Prod1		Prod2		Prod3		Prod4		Prod5
		% of Total	Sales $	% of Total	Sales $	% of Total	Sales $	% of Total	Sales $	% of Total	Sales $
Cust1	100,000.00	25.00%	25000	30.00%	30000	0.00%	0	45.00%	45000	0.00%	0
Cust2	200,000.00	0.00%	0	40.00%	80000	25.00%	50000	0.00%	0	35.00%	70000
Cust3	1,000,000.00	30.00%	300000	0.00%	0	40.00%	400000	15.00%	150000	15.00%	150000
Cust4	2,000,000.00	20.00%	400000	20.00%	400000	20.00%	400000	20.00%	400000	20.00%	400000

Getting the XML data to include all products, even ones that the customer did not purchase, was easy - good old data densification. In addition, an independent group of all products was required to display product names in the labels. Effectively, the XML structure changed (a little) to



G_MAIN
 G_ALLPRODUCTS
  G_CUSTOMER
    CUSTOMER
    TOTAL_SALES
    G_PRODUCTS
       G_PRODUCT
           PCT_TOTAL
           SALES_AMT



To get the XML rows to appear as columns wasn't too bad either. All that was needed was a <?for-each@cell:grpname?> directive and the BIP engine takes care of the rest.

 The RTF template looks like this:

Customer #	Total $	F PRODUCT		end
		F % of Total	Sales $	end
FECust1	9,999.99	F 0.00% 99.99		end  E

where F = <?for-each@cell:grpname?> , end = <?end for-each-group?> 

FE = <?for-each:groupname?>, E =  <?end for-each?>

Free X Windows Emulator to run Oracle Installer

After getting frustrated with 30-day trial licences for X windows emulators like X-Win32, Hummngbird etc, I googled "Oracle Installer xwindows emulator" and the first hit was to Andrew Fraser's blog post.  Nifty little DSL (Damn Small Linux) tool that makes installing Oracle products that much easier. Thanks Andrew!