May 11th, 2012

Using partition by to write alternative SQL to get effdt and effseq items

Paritioning data is really useful for grouping data prior to data manipulation (as opposed to group by which does grouping after manipulation the data). This is because group by is grouping resulting data while partition by is actually partitioning  the data and returning the appropriate partitioned data. Simply said; a normal query is run and the results are retrieves by the Database, then Analytics are applied to the results and the Analytic function columns are computed.

April 21st, 2012

UPK PeopleTools Reporting & Test FrameWork

Oracle has added UPK’s for PeopleTools 8.52 Reporting Tools besides the already available UPK’s for PeopleTools 8.51 Reporting Tools and Test Framework. Available for download on Oracle Software Delivery Cloud (formally known as eDelivery).

March 30th, 2012

Create Google Maps directions in PeopleSoft using Related Content

In my previous post on setting up predelivered collaborative related services, I was asked to also elaborate on how to create your own custom related content based on field content from the component. In this post I will explain how to incorporate Google Maps directions as related content based on the home address of an employee and the work location. For this example I will be using PeopleSoft HCM9.1 on PeopleTools 8.51.

March 30th, 2012

Use barcode in BI publiser as from PeopleSoft

Workstation

Installing 3of9Font on developer workstation

In order to develop BI Publisher templates with barcodes you need to deploy the barcode font FREE3OF9.TTF on the developer workstation as following steps describe.

Open Zip:

Extract FREE3OF9.TTF to c:\windows\fonts, do not extract FRE3OF9X.TTF.

Server

Deploying font on Server

To be able to generate BI Publisher reports from PeopleSoft the FREE3OF9.TTF needs to be deployed the the application server and process scheduler servers like described below and the xdo.cfg file needs to reflect the font.

Copy FREE3OF9.TTF to folders:

  • C:\windows\Fonts
  • PS_HOME\jre\lib\fonts\

Adjust xdo.cfg

In folder PS_HOME\appserv to can find file xdo.cfg. Open this file and add the following lines:

<!–
; ***************************************************************
;  This software and related documentation are provided under a
;  license agreement containing restrictions on use and
;  disclosure and are protected by intellectual property
;  laws. Except as expressly permitted in your license agreement
;  or allowed by law, you may not use, copy, reproduce,
;  translate, broadcast, modify, license, transmit, distribute,
;  exhibit, perform, publish or display any part, in any form or
;  by any means. Reverse engineering, disassembly, or
;  decompilation of this software, unless required by law for
;  interoperability, is prohibited.
;  The information contained herein is subject to change without
;  notice and is not warranted to be error-free. If you find any
;  errors, please report them to us in writing.
;
;  Copyright (C) 1988, 2010, Oracle and/or its affiliates.
;  All Rights Reserved.
; ***************************************************************
–>


<config version=”1.0.0″ xmlns=”http://xmlns.oracle.com/oxp/config/”>


<properties>
<!– System level properties –>
<property name=”xslt-xdoparser”>true</property>
<property name=”xslt-scalable”>true</property>
<property name=”system-cachepage-size”>50</property>
<property name=”system-temp-dir”></property>
</properties>

<fonts>
<font family=”Free 3 of 9″ style=”normal” weight=”normal”>
<truetype path=”C:\Windows\Fonts\FREE3OF9.TTF”/>
</font>
</fonts>
 </config>

Barcode

In this example we will use the Emplid for Parcode

For our the barcode is consist out of  the employee identifier.

All barcodes needs to be prepended and postpendend with character *. These * character will not be translated to the barcode, but are start and end indicators of the barcode font.

PeopleSoft Prerequisite objects

Prerequisites

Before a BI Publisher Template can be created, the barcode generator function needs to available in PeopleSoft. For this a custom view is created that call the function and this view is added to a custom Query Tree to be able to use this view in PeopleSoft Query as input for the Datasource of BI Publisher.

All PeopleSoft object can be found in Application Designer database HCMD project L_BARCODE.

FIELD

Ad first create a Field, which will store the identifier which will present the barcode

Record L_BARCODE_VW

This record will be based on the vanilla view NAMES_VW, so we save the view as L_BARCODE_VW

View SQL

SELECT
 A.EMPLID
 ,A.NAME_TYPE
 ,A.EFFDT
 ,A.LAST_NAME
 ,A.FIRST_NAME
 ,A.MIDDLE_NAME
 ,A.NAME_PREFIX
 ,A.NAME_SUFFIX
 ,A.LAST_NAME_SRCH
 ,A.FIRST_NAME_SRCH
 ,A.NAME_DISPLAY
 ,'*' %Concat A.EMPLID %Concat '*'
 FROM PS_NAMES A
 WHERE A.EFFDT = (
 SELECT MAX(B.EFFDT)
 FROM PS_NAMES B
 WHERE B.EMPLID = A.EMPLID
 AND B.NAME_TYPE = A.NAME_TYPE
 AND B.EFFDT <= %CurrentDateIn)
 AND A.EMPLID = 'K0G001'

To have predictive result ( for this blog) I will retrieve only the data of  emplid =’K0G001′

Query Tree

In order to use record L_BARCODE_VW in PeopleSoft Query as input for Datasources, this record needs to be added to a Query Tree. For this a new Query Tree L_GENERIC_Tree has been created.

(just enter your Access group name and press save)

PermissionList & Role

PermissionList

In order to have user  have access to this Query Tree, this has to added to a Permissionlist. For this a new PermissionList and Role has been created.

PermissionList L_BARCODE_PERM_LIST

Add access group to the permissionlist

Role

Role L_BARCODE_ROLE

And assign the L_BARCODE_ROLE to your user

Create a BI Publisher Template Example

Prior to creating a BI Publisher Report Template, a Datasource has to be created. For this example the Datasource is based on a PeopleSoft Query.

Query

Datasource

Now a datasource needs to be created based on above Query

Generate the XML and the XSD

Click on L_BARCODE_QRY.XML  link to view the generated XML

  <?xml version=”1.0″ ?>- <query numrows=”10″ queryname=”L_BARCODE_QRY” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xsi:noNamespaceSchemaLocation=”">- <row rownumber=”1″><EMPLID>EMPLID samp</EMPLID><NAME>NAME sample data</NAME>
<L_BARCODE>L_BARCODE sample data</L_BARCODE>
</row>
- <row rownumber=”2″>
<EMPLID>EMPLID samp</EMPLID>
<NAME>NAME sample data</NAME>
<L_BARCODE>L_BARCODE sample data</L_BARCODE>
</row>
- <row rownumber=”3″>
<EMPLID>EMPLID samp</EMPLID>
<NAME>NAME sample data</NAME>
<L_BARCODE>L_BARCODE sample data</L_BARCODE>
</row>
- <row rownumber=”4″>
<EMPLID>EMPLID samp</EMPLID>
<NAME>NAME sample data</NAME>
<L_BARCODE>L_BARCODE sample data</L_BARCODE>
</row>
- <row rownumber=”5″>
<EMPLID>EMPLID samp</EMPLID>
<NAME>NAME sample data</NAME>
<L_BARCODE>L_BARCODE sample data</L_BARCODE>
</row>
- <row rownumber=”6″>
<EMPLID>EMPLID samp</EMPLID>
<NAME>NAME sample data</NAME>
<L_BARCODE>L_BARCODE sample data</L_BARCODE>
</row>
- <row rownumber=”7″>
<EMPLID>EMPLID samp</EMPLID>
<NAME>NAME sample data</NAME>
<L_BARCODE>L_BARCODE sample data</L_BARCODE>
</row>
- <row rownumber=”8″>
<EMPLID>EMPLID samp</EMPLID>
<NAME>NAME sample data</NAME>
<L_BARCODE>L_BARCODE sample data</L_BARCODE>
</row>
- <row rownumber=”9″>
<EMPLID>EMPLID samp</EMPLID>
<NAME>NAME sample data</NAME>
<L_BARCODE>L_BARCODE sample data</L_BARCODE>
</row>
- <row rownumber=”10″>
<EMPLID>EMPLID samp</EMPLID>
<NAME>NAME sample data</NAME>
<L_BARCODE>L_BARCODE sample data</L_BARCODE>
</row>
</query>

 

Save XML locally to be used as input for the BI Publisher Template.

Template

Open Word and click on start and choose Word Option.  After this choose Add-Ins and Manage Templates and click on GO

After this choose Add-Ins and Manage Templates and click on GO

You will now see a Add-Ins menu in the XMLP Desktop menu ribbon.

From the menu choose Data – Load XML Data and select the XML file that was locally saved from the Datasource.

From the menu choose Insert – Field and drag field L Barcode String to the Word area twice.

Now set one of the L Barcode String fields to font 3 of 9.

The field will be shown as barcode and on the runtime the barcode string from the datasource will be translated to the barcode.

Save the template as L_BARCODE.rtf

Report Definition

In order to run the report from PeopleSoft, a Report Definition has to be created.

Use the Upload button to upload the RTF template.

Run the report

output:

March 29th, 2012

Fusion Applications – How to get Sizing Requirement?

In the last year many of us struggled with the unknown sizing requirements for Fusion Applications. I have had many comments on the my posts and read tons of other posts from different people stating what the hardware requirements for Oracle Fusion Applications are. None of the documentation state the overall hardware requirements needed for Oracle Fusion Applications. You can find the hardware requirements for the individual components, but you can not simply sum up these requirements.

Some say 60GB RAM, some say 90GB RAM, latest I have heard was 128GB RAM.

I have heard sounds like minimum of three servers up to 14 servers.

Well let’s set it clear by what Oracle is saying on Oracle Support document 1379929.1

Oracle wants to ensure that each customer is successful with Fusion Applications and as it has over a hundred different components giving a sizing estimate has proven to be a bit of a challenge. So instead of giving a one-size fits no-one estimate Oracle has a special process in place that will lead to a proper sizing advice. It consists of contacting your Oracle Sales / Account person who in turn engages the Oracle Fusion Applications environment sizing experts team. Based up on your inputs they will provide a proper sizing report recommending the disk and memory requirements and CPU sizes for your configurations and usage level.

So let’s stop guessing, if you want to know the hardware requirements, you need to contact Oracle.

One thing is sure, Oracle Fusion Applications is not like any other ERP system and consists of many components. Creating a local sandbox is not something that can be done on a single “simple” server.