Joining multiple queries in Data template XML Publisher

Joining multiple queries in XML Publisher can be done by 2 methods

1) Query Link for below dataQuery

 <link name="LINK1" parentQuery="Q1" parentColumn="VALUE1" childQuery="Q2" childColumn="COLUMN1"/>  
 <link name="LINK2" parentQuery="Q2" parentColumn="COLUMN1" childQuery="Q3" childColumn="COLUMN6"/>  

2) Column link between multiple queries
 Multiple queries can be linked in XML Data Template by passing the column from parent query as parameter to the child sqlStatement. This approach is recommended over using Query link and there is no limit for number of queries to be linked.

 <dataQuery>  
   <sqlStatement name="Q1">  
   <![CDATA[  
           SELECT XT1.VALUE1 from   
                XX_TABLE1 XT1  
                WHERE 1=1  
                     AND XT1.COLUMN1 = :PARAMETER1 <your parameter is optionalter>  
   ]]>  
   </sqlStatement>  
   <sqlStatement name="Q2">  
   <![CDATA[  
           SELECT XT2.COLUMN1, XT2.COLUMN2 from   
                XX_TABLE2 XT2  
                WHERE 1=1  
                     AND XT2.COLUMN1 = :VALUE1  
   ]]>  
   </sqlStatement>       
   <sqlStatement name="Q3">  
   <![CDATA[  
           SELECT XT3.COLUMN5, XT3.COLUMN6 from   
                XX_TABLE3 XT3  
                WHERE 1=1  
                     AND XT3.COLUMN6 = :COLUMN1  
   ]]>  
   </sqlStatement>  
 </dataQuery>       

The same can be represented in datastructure of XML Data template.

 <dataStructure>  
   <group name="G_PARENT"  source="Q1">  
           <element name="VALUE1"                value="VALUE1"/>       
           <group name="G_HDR_DETAILS"  source="Q2">  
                <element name="COLUMN1"                value="COLUMN1"/>  
                <element name="COLUMN2"                value="COLUMN2"/>  
                          <group name="G_LINE_DETAILS"  source="Q3">  
                               <element name="COLUMN5"        value="COLUMN5"/>  
                               <element name="COLUMN6"        value="COLUMN6"/>       
                          </group>                 
           </group>  
      </group>  
 </dataStructure>  

Query to find Workflow Notification Mailer Outbound Server Name

Below is the query to find the Workflow notification Mailer Outbound server details.


SELECT   parameter_value
  FROM fnd_svc_comp_param_vals fscpv
  , fnd_svc_comp_params_b fscp
  WHERE fscpv.parameter_id = fscp.parameter_id
    AND fscp.parameter_name = 'OUTBOUND_SERVER';


You can find the other workflow mailer related attributes using the above tables.

Images in XML Publisher

1. Insert a dummy image in your template.
     In Microsoft Word's Format Picture dialog box select the Web tab. Enter the
     following syntax in the Alternative text region to reference the OA_MEDIA directory:
   url:{'${OA_MEDIA}/image name'}
   For example, enter:
   url:{'${OA_MEDIA}/ORACLE_LOGO.gif'} 



Picture properties from Word 2003.

2. Ensure that the image file is in OA_MEDIA
3. Then, from the XML Publisher Administrator resp, upload the edited template into the Template record.
   
   Also see Dynamic Images in XML Publisher

Install font using XML Publisher Administrator

No longer XML publisher fonts needed to be placed on the server. They can be uploaded and used from XML publisher font file and font mappings.
 

For explanatory purpose IDAutomationHC39M font and font name is used. Make sure to use your own font while defining.
 

IMP***** Font selected while defining XML template MUST match with font name defined in xml publisher.

• Navigate to XML Publisher responsibility.

  Go to Administration Tab.
  Click on Font Files and create font file
  

  

  Font Name: IDAutomationHC39M

  File: Select IDAutomationHC39M.ttf from your saved location.

  Click Apply.

  Click on Font Mappings. Click “Create Font Mapping Set”.

  

  Mapping Name: Barcodes

  Mapping Code: Barcodes

  Type: FO To Pdf

  Click Apply.

  

  Click on Create Font Mapping. Fill the values as below screen shot.

  Font Family: IDAutomationHC39M

  

  In the next page enter

  Font Value: IDAutomationHC39M

  Click Apply.

  Click on Administration Tab

  Expand FO Processing and enter Barcodes as Font Mapping Set value.

  

  Click Save.