Issue with BI Publisher/XML Publisher Word Add-in

Came across a problem yesterday with the Oracle BI Publisher (XML Publisher) add-in for Microsoft Word. The main issue was receiving a Macro error when using any of the Oracle BI Publisher menu items (such as Data, Load Sample XML Data…) I've found references on website forums stating that corrupt .exd files might be the culprit. This seemed to be the case for me. If you come across such an issue, here are the steps that I followed to fix the issue:

1. Close all open Word windows. (Sometimes I had to shut down the PC completely.)
2. Open My Computer and from the Tools menu choose Folder Options… On the View Tab, scroll down through the Advanced Settings: list and ensure the Hide protected operating systems files (Recommended) is NOT ticked. Press OK. 
3. Now go Start, Run… and enter the following %userprofile%\Application Data\Microsoft\Forms and press the OK button. 
4. In the window that opens up find the file that ends with .exd. Right click the file and choose Rename. Keep the filename as is and add .old to the end of it. (The next time this file is needed a new version will get placed here.) If there is more than one file that ends with .exd then rename all of these. Close the Window.
5. If you had Hide protected operating systems files (Recommended) ticked all along, follow the instructions in step 3 to go back and tick that setting again. 
6. Now start up Word and see if BI Publisher/XML Publisher is working. If not, there may possibly be other .exd files in other locations that are corrupt. Seek those out and try renaming them. Or there might be another unrelated problem. I'd start by reinstalling the Oracle BI Publisher Desktop.

Hope this helps.

Using WITH Clause to mimic App Engine Temp Tables

I've been working on an App Engine that makes use of Temp tables and uses the %Table meta-sql. It's sometimes difficult to debug your App Engine SQL because the data in the temp table gets deleted at the end of the run. So I get around this in my SQL client by using the WITH clause

WITH  AS 
(subquery_sql_statement),
 AS
(sql_statement_from_alias_one)

That doesn’t make a lot of sense, so here’s a simple example you can run.

with NY_SSR_TSC_TMP AS
(
SELECT '0' PROCESS_INSTANCE,    1 REPORT_REQUEST_NBR,    1 REQUEST_SEQ_NBR,    1 REPORT_NUMBER,    '9999991' EMPLID,    'PGT' ACAD_CAREER  FROM dual
union
SELECT '1' PROCESS_INSTANCE,    1 REPORT_REQUEST_NBR,    1 REQUEST_SEQ_NBR,    1 REPORT_NUMBER,    '9999992' EMPLID,    'UG' ACAD_CAREER  FROM dual  
)
select * from NY_SSR_TSC_TMP;

So I’ll turn an App Engine sql statement like this (from the delivered SSR_TSC_LIB App Engine - it's a library used by the Campus Solutions Transcript Generation Process):

SELECT Z.REPORT_REQUEST_NBR 
,Z.REQUEST_SEQ_NBR 
,Z.REPORT_NUMBER 
,%Bind(SEQNO) 
,%Bind(PRINT_AREA_CODE) 
,%Bind(SSR_ROWTYPE) 
,A.AID_YEAR 
,A.ITEM_TYPE 
,A.ACAD_CAREER 
,B.DESCR 
,A.DISBURSED_AMOUNT 
,A.ACCEPT_AMOUNT 
,%Bind(TSCRPT_TYPE_AET.AWARD_AID_YEAR_PRT) 
,%Bind(TSCRPT_TYPE_AET.AWARD_AMOUNT_PRT) 
FROM %Table(SSR_TSC_TMP) Z 
, PS_STDNT_AWARDS A 
, PS_ITEM_TYPE_FA B 
WHERE Z.PROCESS_INSTANCE = %ProcessInstance 
AND A.EMPLID = Z.EMPLID 
AND A.INSTITUTION = %Bind(INSTITUTION) 
AND A.ACAD_CAREER = Z.ACAD_CAREER 
AND A.ACCEPT_AMOUNT > 0 
AND A.ITEM_TYPE = B.ITEM_TYPE 
AND A.SETID = B.SETID 
AND A.AID_YEAR = B.AID_YEAR 
AND B.INCLUDE_IN_TSCRPT = 'Y' 
AND A.AWARD_STATUS = 'A' 
AND B.EFF_STATUS = 'A' 
AND B.EFFDT = ( 
SELECT MAX(B1.EFFDT) 
FROM PS_ITEM_TYPE_FA B1 
WHERE B.SETID = B1.SETID 
AND B.ITEM_TYPE = B1.ITEM_TYPE 
AND B.AID_YEAR = B1.AID_YEAR);

into something like this (%Table(SSR_TSC_TMP) changed to NY_SSR_TSC_TMP, %ProcessInstance changed to 0, some %Bind’s removed and the %Bind(INSTITUTION) in the WHERE clause changed to 'ZZZZZ'):

with NY_SSR_TSC_TMP AS
(
SELECT '0' PROCESS_INSTANCE,
1 REPORT_REQUEST_NBR,
1 REQUEST_SEQ_NBR,
1 REPORT_NUMBER,
'9999991' EMPLID,
'PGT' ACAD_CAREER
FROM dual 
)
SELECT Z.REPORT_REQUEST_NBR 
,Z.REQUEST_SEQ_NBR 
,Z.REPORT_NUMBER  
,A.AID_YEAR 
,A.ITEM_TYPE 
,A.ACAD_CAREER 
,B.DESCR 
FROM NY_SSR_TSC_TMP Z 
, PS_STDNT_AWARDS A 
, PS_ITEM_TYPE_FA B 
WHERE Z.PROCESS_INSTANCE = 0 
AND A.EMPLID = Z.EMPLID 
AND A.INSTITUTION = 'ZZZZZ'
AND A.ACAD_CAREER = Z.ACAD_CAREER 
AND A.ACCEPT_AMOUNT > 0 
AND A.ITEM_TYPE = B.ITEM_TYPE 
AND A.SETID = B.SETID 
AND A.AID_YEAR = B.AID_YEAR 
AND B.INCLUDE_IN_TSCRPT = 'Y' 
AND A.AWARD_STATUS = 'A' 
AND B.EFF_STATUS = 'A' 
AND B.EFFDT = ( 
SELECT MAX(B1.EFFDT) 
FROM PS_ITEM_TYPE_FA B1 
WHERE B.SETID = B1.SETID 
AND B.ITEM_TYPE = B1.ITEM_TYPE 
AND B.AID_YEAR = B1.AID_YEAR);

The power of the WITH clause really proves itself out whenever the new dummy table is referenced more than once in your SQL statement.

Problems with Pagelet Wizard Data Source Parameters

A fellow developer ran into a problem recently when he tried creating a pagelet using the Pagelet Wizard. For the purposes of this post I'll simplify what he was doing in order to get to the heart of the problem. The underlying query he used for the pagelet had 2 prompts. The second prompt used the value of the first prompt to limit the values that are returned. You often see prompts like this where you first select a Business Unit first and then another value from a table where Business Unit is a key. And you need to select the Business Unit first or the 2nd prompt will return no values. When he ran this Query from Query Manager or Query Viewer, the prompts worked as expected.

Later he created the pagelet using this query in the Pagelet Wizard. The prompts from the query were picked up without any problem as Data Source parameters (Step 3 of 6 in the Pagelet Wizard.) By specifying them as "User Specified" for the Usage Type, you get the Customize Pagelet link (the pencil icon on the pagelet border) when it is published to the homepage. On the homepage he could click the pencil icon and the prompts were displayed correctly, but they didn't work. The first prompt could be selected. But the value returned had no impact on the second prompt. After we looked into it we discovered that it was an issue with this customize pagelet component (PTPPB_USER_PREF).

To get around this issue I suggested that a new component needed to be displayed whenever the pencil icon was clicked. Luckily this can be easily done. Whenever a homepage pagelet has been created using the Pagelet Wizard, a new content reference is added to the Portal Registry. One of the options that is available for a pagelet content reference is the content reference for the Edit URL Information. By default the Pagelet Wizard sets this to the delivered component (the one we discovered that doesn't work when using prompts where the first value should be used to limit the second prompt values.) So by overwriting this content reference, we can set it to anything we want. So that's what we did.

The new component was built using derived records and fields. So we could make the prompts work the way we needed them to. We had to write the values selected back to the table that is used to store Customise pagelet values for Pagelet Wizard built pagelets. This was easy enough to figure out. It's the PTPPB_USER_PRMS table, and is simply keyed on the Pagelet name, Pagelet Field and OPRID.

So problem solved, right? Not quite. The client we were working for had decided to allow quite a few users access to the Pagelet Wizard, not just developers or analysts. They figured if users could have access to Query Manager to build Queries, they could also have access to the Pagaelet Wizard to let them display their query results in a nice, easy to view format. This means that almost any Pagelet Wizard user could modify and redeploy this Pagelet. Each time this happens, the Customize content reference would get set to the default, over-writing our custom component's content reference.

This solution really is only an option where Pagelet Wizard is used by developers or analysts and is subject to change control, which is how it was at my previous employer. In this situation support documentation can be created to highlight the fact that if the pagelet is modified, the Customise content reference needs to be reset to our custom component.

In the end, my fellow developer had to customise the delivered Customise component, to make it work correctly. Sometimes you just have to customise, don't you?