Namespace prefix ‘ref’ used but not declared in XML Publisher


Caused by: oracle.xdo.parser.v2.XPathException: Namespace prefix 'ref' used but not declared.
	at oracle.xdo.parser.v2.XSLProcessor.reportException(
	at oracle.xdo.parser.v2.XSLProcessor.newXSLStylesheet(

This Error generally due to higher BI Publisher versions.

If Word Properties of the XML Field has source something like ‘<?ref:xdo0040?>’ you will face this error


Make BI Publisher backward compatible.


Go to Options


Build -> Form field size  -> Set to Backward Compatible.




BOM Explosion Query

SELECT level ,
msi1.segment1 AS "Parent",
case when msi1.PLANNING_MAKE_BUY_CODE=1 then
when msi1.PLANNING_MAKE_BUY_CODE=2 then
LPAD (' ', 10 * (LEVEL - 1)) || msi2.segment1 AS "Component",
bic.COMPONENT_QUANTITY AS "Component Quantity",
case when msi2.PLANNING_MAKE_BUY_CODE=1 then
when msi2.PLANNING_MAKE_BUY_CODE=2 then
--LEVEL + 1,
SYS_CONNECT_BY_PATH (msi1.segment1, ' / ') || ' / ' || msi2.segment1
"Full Structure"
FROM apps.bom_inventory_components bic,
(select * from apps.bom_bill_of_materials where organization_id =&Organization_id )bom,
apps.mtl_system_items msi1,
apps.mtl_system_items msi2
WHERE bom.bill_sequence_id = bic.bill_sequence_id
AND msi1.inventory_item_id = bom.assembly_item_id
AND msi1.organization_id = bom.organization_id
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = bom.organization_id
START WITH bom.assembly_item_id IN
(SELECT msib.inventory_item_id
FROM apps.mtl_system_items_b msib
WHERE msib.segment1=&Item_number
AND msib.organization_id = &Organization_id )
CONNECT BY NOCYCLE PRIOR bic.component_item_id = bom.assembly_item_id

XML Publisher Add On Not working in Office 2013 / Office 2016 / Office 365

If you are unable to add XML Publisher add-on to MS Word. Most Probably it is due to 32 bit version of Office.

Follow the below instructions.

Check your MS Office Bit version . MS Word -> File -> Account -> About Word



If it is 32 bit version install 32 bit version of XML Publisher . Once you install 32 bit version It should automatically be visible on MS Word.

OutOfMemory Error with XML Publisher Reports

We see this error (java.lang.OutOfMemoryError) generally when there is a huge data to process for OPP.

There are multiple options to resolve this error.

  1. Increased profile option value Concurrent:OPP Process Timeout to a higher value.   If increasing the profile option value doesn’t work do the following
  2.  Increase OPP memory size from default  to 2 GB

Use the below query to find out the size. if it is less than 2GB , increase the value to 2GB.

SELECT developer_parameters

  FROM fnd_cp_services s, fnd_concurrent_queues q

WHERE q.concurrent_queue_name = ‘FNDCPOPP’ and s.service_id = q.manager_type

Change the value to ‘J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx2048m

BI Publisher Add-In does not show up in Office 2010/Windows 7

If you are installing BI Publisher for the first time in your Laptop .If you have a Windows 7 (64 bit) Laptop and if your MS office is a 32 bit version, you will face this issue.

Where can i see my office version


The XML Publisher installation will be smooth, but if you open the MS Word you will not see the Add in Option in the tool bar.


Because you are installing a software that’s compatible on a 32 bit version on a 64 bit machine, Microsoft makes this Add in as an Inactive Add in

Open MS Word.go to File–> Options – Word options box opens and in that go to Add Ins


Under the Inactive application Add on you will see “BI Publisher Template for Word” as a COM Add in. In the above screen shot it shows active because i have fixed the issue

If you go to Manage: COM Add in at the bottom and try to enable this Add in, because its considered malicious Microsoft is going to make it Inactive again.


The only way out is You need to install “Visual Studio 2010 Tools for Office Run time” from Microsoft site, its a free executable.  Visual Studio 2010 download link The moment you install this “BI Publisher Template for Word” will become an active Add on and then bing!, you will see the BI Publisher Add in show up once you restart your word.


Caution: Close all MS Office related stuff during this installation process .



List Of Responsibilities to which a concurrent program is Attached

This simple query can help you to identify the list of responsibilities to which a concurrent program is attached.

SELECT DISTINCT responsibility_name
FROM apps.fnd_responsibility_vl frv,
apps.fnd_request_group_units frg,
apps.fnd_concurrent_programs_tl fcp
WHERE frv.request_group_id = frg.request_group_id
AND frg.request_unit_id = fcp.concurrent_program_id
AND fcp.user_concurrent_program_name = ‘<Concurrent Program Name>’

How to preserve Leading zeros in XML Publisher Excel Ouputs

In XML Pub Excel Reports excel removes the leading Zeros and prints the whole number. There are many instances where we need to print the zeros for eg item numbers starting with 0.

There are many ways to achieve this, one of the simple ways is to convert the number to character by adding space at the end.

concatenate two spaces after the xml tag as shown below in your template.

<?concat(<XMLTag>,’  ‘)?>

Downside of it is you will not be able to use excel features like sum , count etc on this specific column.

Query to Get Profile Values From Oracle Backend

SELECT fpos.profile_option_name ,
fpots.user_profile_option_name ,
DECODE (fpovs.level_id,
10001, ‘Site’,
10002, ‘Application’,
10003, ‘Responsibility’,
10004, ‘User ‘,
10005, ‘Server’,
10006, ‘OU’,
DECODE (TO_CHAR (fpovs.level_id),
‘10001’, ‘None’,
‘10002’, fa.application_short_name,
‘10003’, frt.responsibility_name,
‘10004’, fu.user_name,
‘10005’, fn.node_name,
FROM fnd_profile_options fpos,
fnd_profile_option_values fpovs,
fnd_profile_options_tl fpots,
fnd_user fu,
fnd_application fa,
fnd_responsibility_tl frt,
fnd_nodes fn,
hr_operating_units hou
WHERE fpots.profile_option_name = ‘<Enter Your Profile Option Name here>’
AND fpos.profile_option_id = fpovs.profile_option_id(+)
AND fpos.profile_option_name = fpots.profile_option_name
AND fu.user_id(+) = fpovs.level_value
AND frt.application_id(+) = fpovs.level_value_application_id
AND frt.responsibility_id(+) = fpovs.level_value
AND frt.language(+) = ‘US’
AND fa.application_id(+) = fpovs.level_value
AND fn.node_id(+) = fpovs.level_value
AND hou.organization_id(+) = fpovs.level_value
AND fpots.language = ‘US’

How to generate Session Trace from Toad or SQL Plus

alter session set max_dump_file_size = 50000000;

alter session set sql_trace = true;

Run your SQL’s
alter session set sql_trace = false;

— Run the following query to find the trc file

select vpar.value || ‘/’ || instance || ‘_ora_’ || ltrim(to_char(vp.spid,’fm99999′)) || ‘.trc’
from v$process vp, v$session vs, v$parameter vpar, v$thread vthr
where vp.addr = vs.paddr
and vs.audsid = userenv(‘sessionid’)
and = ‘user_dump_dest’
Once you have the trc file use the standard tkprof procedure to get the tkp / tkprof file