Oracle Change/Modify PO Print Logo

Changing the Logo on PO Print is a little tricky. I say tricky purely because even though the PO Print program is a XML Publisher based report the layout part of the report is XSL-FO type. So all the details are in XML.

--------------------------------------------------------
Note: For doing this we need to have XML Publisher template builder installed. Pls follow the below instructions

Download the patch from Metalink. Latest version as of today is Patch 12395372: UPDATE FOR BI PUBLISHER DESKTOP 10.1.3.2.1 (5.6.3) size = 95.9 MB Product=BI Publisher (formerly XML Publisher)

Unzip this patch and then run BIPublisherDesktop.exe to install in a directory
--------------------------------------------------------
So lets get to the task.

Step 1: Create an RTF Document and embed the image into the document. I create a MSWord document and 'Save As' RTF document. And in Word (Menu)Insert -> Picture -> From File -> Select the image.

Step 2: Update the size of image to approx the size it appears in the document.

Step 3: Once you have template builder installed, the plug-in adds new menu options in Word. Tools -> Export -> XSL-FO Style Sheet. Screenshot attached

Step 4: An XML file is generated, Open the file and search for tag 'fo:instream-foreign-object' tag which is of our interest.

Step 5: Copy the tag contents from the beginning to the end of the tag 'fo:instream-foreign-object'. So this is our image xdofo:uid

Step 6: Open XML Publisher, Search for Template 'Standard Purchase Order Stylesheet', Duplicate the template entering required details. Download PO_STANDARD_XSLFO.xsl and rename to XXC_PO_STANDARD_XSLFO.xsl

Step 7: Open XXC_PO_STANDARD_XSLFO.xsl and search for 'fo:inline' which contains the Logo info. Uncomment the inline tags and replace the 'fo:external-graphic content' with the tags contents we copied in step 5.

The file should look like this.

Step 8: Save XXC_PO_STANDARD_XSLFO.xsl and attach the file to the template we created in Step 6. Also update the xsl:style sheet definition(at the beginning of the document) to refer to xdo namespace as follows.

'xsl:stylesheet version="1.0" xmlns:fo="http://www.w3.org/1999/XSL/Format" xmlns:ora="http://www.oracle.com/XSL/Transform/java/" xmlns:xdofo="http://xmlns.oracle.com/oxp/fo/extensions" xmlns:xdoxliff="urn:oasis:names:tc:xliff:document:1.1" xmlns:xdoxslt="http://www.oracle.com/XSL/Transform/java/oracle.apps.xdo.template.rtf.XSLTFunctions" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"'

Step 9: Attach the new template to PO Document Types. (Document Type Layout), so the new template will be picked for the PO document creation

Step 10: For this article purpose lets simply the process and see if we can get the PO Document with the new Logo, by using the preview button in XML Publisher(for the new template we created in Step 6).Here we have it.

Leave me a comment if you have any questions.

Oracle Open Multiple Forms

In Oracle we have an option under Tools -> Close Other Forms which when unchecked allows us to open multiple forms.

Sometimes this option is not update-able. This is controlled by a function.

If it is not in a certain responsibility we need to add an menu exclusion in the resp definition.

Add Function -> Navigator: Disable Multiform

Oracle Database Version

Ever wondered what Oracle database version you are running on, use the following query

Oracle Version :
-----
select * from v$version
where banner like 'Oracle%';

v$version also have versions for PL/SQL version and TNS version etc.

Oracle Workflow Miscellaneous

Some miscellaneous workflow things I encountered.

Workflow can be started by two different ways
1. Itemtype (launch workflow)
2. wf_event.RAISE

So to search for the workflow in the code you have to keep in mind the above.

Deferred Events:

To find if you event or workflow is struck in the deferred Q, use the following query.
SELECT a.user_data.event_name event, a.msg_state status,a.user_data.send_date
FROM applsys.aq$wf_deferred a
WHERE a.user_data.event_name like 'oracle%write%'

Unix Commands.

Some commands which I came across during my experience.

To Search for some piece of code or message text in Oracle_TOP or any directory in UNIX

find . | xargs grep -s 'XXC_MESSAGE'

Unix Command History: /bin/bash

Zip/Unzip in UNIX : zipinfo datazip.zip

Using TAR
---------
To ZIP : tar czf itemdata1.tar.gz datafiles
to Unzip : tar xvzf itemdata.tar.gz

Filesize in MB: ls -lh (in MB)

Count files/entries in a directory: ls -1 | wc -l

Oracle Set application Context

Below are tiny SQL's which can be used to set the Apps context.

BEGIN
dbms_application_info.set_client_info('103');
END;

BEGIN
FND_GLOBAL.Apps_Initialize (user_id => 5992 ,resp_id => 51498 ,resp_appl_id => 660 );
END;

In SQL developer sometimes the views don't fetch any data. Set the NLS Lang using the following SQL.

alter session set nls_language = 'AMERICAN';

Debug Oracle Applications.

Below is the piece of code I have been using for quite a while for creating a good debug process in Oracle Applications development environment. It is good enough to be in PROD if we can convince the team too.


Steps:
1.Create a sequence
2.Create a debug table
3.Create a procedure
---
CREATE SEQUENCE APPS.XXC_SEQ
  START WITH 1
  MAXVALUE 99999999
  MINVALUE 1
  NOCYCLE
  CACHE 2
  NOORDER;
/

CREATE TABLE APPS.XXC_DEBUG_TBL(
  SEQ    NUMBER,  MESG   VARCHAR2(1000 BYTE),  DTIME  DATE)

/

CREATE OR REPLACE procedure APPS.XXC_debug(mesg1 in XXC_debug_tbl.mesg%type)
is
    PRAGMA AUTONOMOUS_TRANSACTION;
begin

    insert into XXC_debug_tbl(Seq,mesg,dtime)
    values(XXC_seq.nextval,mesg1,sysdate);
    commit;
      
end;



To call the procedure : xxc_debug('Test message')

And all debug messages are available in xxc_debug_tbl

Oracle Inventory - Get Item Cost

Below is the Oracle Standard API to fetch the Item cost.
Having a look at API We can see that the derivation is in various stages. Based on the Cost Method.

Standard cost method id is 1
Average cost method id is 2

-----
If the costing method is 1 the costs come from cst_item_costs
If not the costs come from cst_quantity_layers which will be based on the cost group for the item.

Pls download the attached code for more info.
Download File

PA Patchset level.

To find the current PA Patch set level you are on use the attached script.
Download File

Oracle SQL - GREATEST and LEAST

To select max from 2 columns
select greatest(4,2) from dual

To select min from 2 columns
select least(1,3) from dual

Search for Quote character in SQL

Ever wanted to find strings which contain Quote character. Using CHR(39) is the way.

Ex:
select table_name||CHR(39) from all_tables where table_name like '%'||CHR(39)||'%' and rownum < 5;

I am here trying to find table names which contain a quote char.

Fetch Environment variables in PL/SQL

How to get the value of environment variables in PL/SQL code
-------------------------------------------------------------
1.For eBusiness Suite, you can also look at fnd_env_context table in your pl/sql code to get the path to the custom top variables as well.


2.A well coded program avoids hard-coding of paths and uses variables. If you want to get the value of unix environment variables in your programs, you can use the DBMS_SYSTEM.GET_ENV procedure. For applying it in E-Business Suite, you would need to make sure that the variables are defined in RDBMS $ORACLE_HOME/custom$CONTEXT_NAME.env

For example, I defined environment variable CUSTOM_TOP in a new file $ORACLE_HOME/custom$CONTEXT_NAME.env. This file is called automatically by your database environment file $CONTEXT_NAME.env.

To test, whether the value appears simply do this in an sql session:
SQL> set autoprint on
SQL> var CUSTOM_TOP varchar2(255)
SQL> exec dbms_system.get_env('CUSTOM_TOP',:CUSTOM_TOP);

PL/SQL procedure successfully completed.

XML Publisher/Template Builder compile error

Symptoms:
When building a template or loading an BI Publisher, (XML Publisher), sample file, the system throws error:

"Compile error in hidden module: Module_starter"
Cause:
This error is due to the installation of the Microsoft Security Update KB936021. Solution

To workaround this issue, follow these steps:

1. Go to the Start Menu on the affected machine: Program Files > Oracle> XML Publisher Desktop > Template Builder for Word -Bin.
2. Double click on the file "ChangeUILang.exe".
3. Select the appropriate language and click ok. This will register the User Interface language.
4. Test.
5. Migrate to other machines as needed.

Enable Help -> Diagnostics in Oracle Applications

We just need to set a profile to enable Help - Diagnostics in Oracle Applications. From this we can Examine forms value, personalizations etc.Pls see the below screenshot.

Set the following profile options.
1. Hide Diagnostics menu entry - No
2. FND: Diagnostics - Yes
3. Utilities:Diagnostics - Yes