Sunday, March 20, 2011

Extract Project PeopleCode and SQL

Sounds Interesting -- Decode PeopleCode from bytecode to text files. Also extract PeopleCode and SQL definitions from PeopleTools projects.

Credits to the original uploader

I have uploaded a modified version of the Zip file for people who use MS SQL Server as their DB Server. For other databases please refer to "ReadMe.txt" in the zip file.

Follow the following steps to extract PeopleCode and SQL definitions

1. Download the attached Zip File

2. Open DecodePC.properties and modify it as shown. You'll have to change the following:
  • user
  • password
  • url
  • DatabaseName
Since, I've MSSQL Server and no Sub Version, I've commented rest of the lines.

3. Ensure that you have sqljdbc.jar in your directory. I've included the sqljdbc.jar in the Zip file.

4. Navigate to the directory via Command Prompt where you have unzipped the earlier zip file.

5. Once done check the output directory in the path provided earlier

6. The files would be created under the output folder in the directory of extraction

Caveat - Does not extract Application Package PeopleCode as yet!!

Saturday, September 11, 2010

PeopleTools 8.51 GA

PeopleTools 8.51 are now available for download through edelivery.oracle.com!!!

Its surprising to note that PeopleTools Patch 8.51.01 is also available.

Thursday, May 13, 2010

How to Retrieve AccessID & AccessPWD for a PeopleSoft Environment

Most of us follow the waterfall model of SDLC within the organizations we work. Various teams are involved at various stages of the model. Each Team has their set of environments where they are the superusers. If we extend this to PeopleSoft, post Unit Testing, developers are not needed/required to have write access in the rest of the environments of SDLC. But what if, you could actually retrieve the ACCESS ID & ACCESS PWD (RDBMS ID which has access to the PeopleSoft schema) without the PSAdmins/DBA being aware. What is needed is explained below.

1. Create a command/batch file with this line - "@echo %1"
Place this file under %PS_HOME%\bin\server\WINX86

2. Create a new Process Type in PeopleSoft. Let's call it - "COMMANDLINE"

3. Create a Process "RETPWD" which will have the Process Type as "COMMANDLINE". I'm using Microsoft as the Database type as the PS DB is hosted on MSSQL 2005 SP2

4. Add this Process Type to your Server definition. Say PSNT
5. Bounce the batch server
6. Run the process.
7. The process may fail, do not panic, check the log if it has been posted to report repository or you could navigate to the prcs log directory for your batch server and locate the log. It should show you something like - "DBNAME/Access ID/Access PWD"



Sunday, May 9, 2010

Integration Technology of PeopleSoft

PeopleSoft offers several application integration solutions to integrate two different applications be it PeopleSoft applications or third party systems. The release of PeopleTools 8.4 saw the advent of a new integration tool called the Integration Broker, which facilitates tightly coupled and loosely coupled integrations. Tightly coupled integration, a feature made available through Synchronous messaging, may be defined as a system that expects a response before continuing further processing. Asynchronous messaging provides loosely coupled integration, where the source system continues with its processing without waiting for a response. The architecture that PeopleSoft adopts for either of the messaging formats (Loosely coupled or Tightly coupled) is where the difference lies. The manner in which the Integration Gateway and Integration Engine interact is almost the same, the difference lies in the content that is exchanged between the two. It is important to understand that, irrespective of the Transaction Type (Synch or Async) PeopleSoft Integration Engine (App Server) and Integration Gateway (Web Server) always communicate in a synchronous fashion. Apart from messaging, Integration Broker also includes Connector development that enables connectors to be customized for a particular integration scenario. Besides Integration Broker, PeopleSoft integration technologies also include Component Interface and Application Engine. The purpose of these integration solutions has been explored here.


The following factors are to be considered before an integration technique is chosen:
  • A large amount of data is to be transferred
  • Whether the data is inbound or outbound.
  • Whether the integration has to be real-time, near-real-time or deferred.
  • The time required to complete a transaction
  • Message structure of data involved
  • Technology of the participating systems.
Each of these factors has been explained in detail in the forthcoming sections.

High Volume of Data
The volume of data involved in a transaction is a crucial factor in deciding the communication technology used. If a large volume of data is to be transferred, the preferred integration techniques are Asynchronous messaging and Application Engine.
Asynchronous messaging mode allows a large volume of data to be pushed into the subscribing systems. Since this does not wait for a response, it can just publish the information and continue with its own processing.
In a scenario that requires a large volume of data to be transferred in batch mode from an external system into the PeopleSoft Application, Application Engine would be the preferred solution. It is quite often used in combination with File interface provided by the PeopleCode in Application Engine. A typical situation would be that of integrating legacy systems with the PeopleSoft Application. As the amount of data involved is huge, the information from the legacy systems is stored temporarily in files. This data is then retrieved via the PeopleCode section of the Application Engine using file operations and the application can be populated using Component Interface.

Direction of Data
The flow of data is classified as inbound or outbound depending on whether the information flows into the system or away from the system. If the flow of information were purely outbound, Asynchronous messaging would be an ideal solution. An example would be that of keeping in sync, the customer information present in Financials (FDM) with PeopleSoft CRM. When a new customer is added in FDM, the information is sent to PeopleSoft CRM. Here information only flows out of the Financials system, hence Asynchronous messaging may be used for this scenario.
When the flow of information is purely inbound, the business validations are to be considered to ensure that the data entering the system is correct. Component
Interface (CI) provides this feature, as it invokes the business logic before saving the data in the database. Thus CI takes care of the necessary validations before committing the transaction. A typical example where Component Interface is used is when the leads are loaded in the sales module from an excel sheet. In certain cases Component Interface is also used in combination with other technologies such as Asynchronous messaging and Application Engine.
In a scenario, where the integration involves both outbound as well as inbound messaging, Asynchronous messaging, Synchronous messaging or Application Engine may be used depending on whether the transaction is loosely, tightly coupled or file based respectively. An example of tightly coupled two way messaging would be the integration of Field Service Order module of PeopleSoft CRM with the Inventory module of the Supply Chain Management product line. In this integration, whenever the inventory level for a product is required a request is issued with the product information from the Field Service Module to the Inventory module. The response message contains the inventory level of the requested product. Thus Synchronous Message would cater to the needs of such a transaction.

Application Behavior
It depends on whether the integration is real-time or near-real-time or Offline. Real-time integration may be achieved through Synchronous Messaging and Component Interfaces. This type of integration is preferred for critical applications such as billing involving credit card authorization and crediting the amount. Before a bill is issued by PeopleSoft Billing module the credit card number is authorized by third party systems like CyberCash and eventually the amount is debited from the customer’s account. The source system has to ensure that the data is updated in all the participating systems. Thus the source system has to wait for a response from each of the systems before committing the transaction in its own system.
The most common tool used for offline or deferred processing is Application Engine using the File Interface.

Response Time
If the processing of the received messages is going to consume considerable amount of time it is advisable to go in for Asynchronous messaging. If the processing time is small, then Synchronous Messaging may be used. Nevertheless while designing Synchronous messaging it is necessary to keep in mind the response time.

Compatible formats
In any messaging, the data is passed through record structures embedded in the messages. The receiving system may or may not require all the fields contained in the message. Thus it is necessary to map the message structures to those required by the destination system. This is where connector development comes in, where custom connectors can be developed for systems based on the record structure used by the destination system. Moreover middleware software also aid in the mapping of data. A common example is MQ Series an IBM product that uses a queue system to pass information between PeopleSoft Application and any third party system.

APIs available
An important aspect of connector development depends on the APIs available in a third party system. It also depends on the integration technology supported by the third party system. For example HTTP Connectors may be used for a third party system that supports web services.
Enterprise Application Integration is gaining importance in the ERP industry. It is a major cause of concern for companies that goes in for application integration. PeopleSoft has ensured that it is not left out of the race by providing several integration solutions. These technologies cover almost all the integration scenarios.
Therefore the choice of the right integration technology for the right scenario lies at the discretion of the enterprises.

Saturday, May 8, 2010

Extract All Application Engine PeopleCode

Most of us do come across situations where we would like to see the actual usage of a function in PeopleCode. The standard feature of Find In with the Save PeopleCode to File check-box checked is the most commonly used approach. However, I prefer the below approach. I'm guessing that this function is available from Tools 8.50 as I came across this in latest PeopleBooks. The function GetProgText() can retrieve PeopleCode from a step for an Application Engine program. This function, however, cannot be used online. Hence, I went ahead and created an Application Engine program to retrieve all the PeopleCode written in all the Application Engine programs in FSCM 91. Though I was surprised to see that there were certain Application Engine programs with PeopleCode actions, but no PeopleCode. What is also nice about the function is that while extracting, it actually compiles the PeopleCode. This was evident, when I received compilation errors for
1. Fields not defined on State Records
2. Incorrect reference of an Import statement
etc.,

----Sample Program----
Do Select SQL - Please be mindful, that the Do Select Type for this Action has to be set to Reselect
%Select(AE_APPLID, AE_SECTION, MARKET, EFFDT, AE_STEP)
SELECT AE_APPLID
, AE_SECTION
, A.MARKET
, A.EFFDT
, AE_STEP
FROM PSAESTMTDEFN A
WHERE AE_APPLID > %Bind(AE_APPLID)
AND AE_STMT_TYPE = 'P'

PeopleCode Action
import SCM_UTILITIES:ExceptionUtilities:ExceptionHandler;

try
If FileExists("D:\Temp\AllAEPC.out", %FilePath_Absolute) Then
REM *** Coming in for the nth time ***;
&fAEPCFile_ = GetFile("D:\Temp\AllAEPC.out", "A", "UTF8", %FilePath_Absolute);
Else
REM *** Coming in for the first time ***;
&fAEPCFile_ = GetFile("D:\Temp\AllAEPC.out", "W", "UTF8", %FilePath_Absolute);
End-If;
&PeopleCodeText = GetProgText(PPAEPCEXT_AET.AE_APPLID.Value, PPAEPCEXT_AET.AE_SECTION.Value, PPAEPCEXT_AET.MARKET.Value, "default", PPAEPCEXT_AET.EFFDT.Value, PPAEPCEXT_AET.AE_STEP.Value, "OnExecute");

&fAEPCFile_.WriteLine("REM *** Application Engine - " | PPAEPCEXT_AET.AE_APPLID.Value | " *** " | " Section - " | PPAEPCEXT_AET.AE_SECTION.Value | " *** " | " Step - " | PPAEPCEXT_AET.AE_STEP.Value | " ***");

&fAEPCFile_.WriteLine(">---------------------------------------------------------------<"); REM *** Write only if there is something to write ***;
If All(&PeopleCodeText) Then
&fAEPCFile_.WriteLine(&PeopleCodeText);
End-If;

&fAEPCFile_.WriteLine(">---------------------------------------------------------------<"); REM *** Generic Exception Handling ***;

catch Exception &ex
Local SCM_UTILITIES:ExceptionUtilities:ExceptionHandler &handler = create SCM_UTILITIES:ExceptionUtilities:ExceptionHandler();
REM *** Display the Message Text and Message Explain Text and then FAIL it!!! ***;
REM Error (&handler.getMessageText(&ex));
REM Be careful, since you’re using the messagebox and it causes an implicit COMMIT in certain tools version;
MessageBox(0, "", 0, 0, "Exception Logged > " | &handler.getMessageText(&ex));
Error (&handler.getMessageExplainText(&ex));
end-try;

Wednesday, May 5, 2010

Integration Broker 8.50 - UserName not defined in the database

While trying to integrate 8.49.xx and 8.50.01 - 07, I came across an unusual error "UserName not defined in the database" which I had never seen earlier. At that point I thought it was something to do with the PSFT Authentication, but did not see this in either 8.48.xx or 8.49.xx. This only came to light, since I was integrating a Tools only DB (8.50.01) and FSCM 9.0/8.49.27.
Though the passwords and UserIDs for the four nodes were same, I still got this error. The reason for this anomaly was, that the UserID I was using to log in to the FSCM DB was VP1 and since, VP1 was not present in the tools only DB, the ping failed. The ping worked when I logged in with PSADMIN on the FSCM side, as this UserID existed in the Target DB.

This error is the result of the signed on UserID not being a valid UserID on the target database. The Authentication token passed includes the signon UserId and after decryption it is validated on the target database.

This is different than what happened on PeopleTools Release 8.48 and 8.49 where the UserID is not validated after the Authentication Token is decrypted.

Oracle claims to have fixed this in the latest tools patch 8.50.08. But even though, it has been fixed, It does rattle your basics around how a PING functions, and hence, Oracle has recently come out with a Support Case, which talks in detail about how a PING would/should work in Tools 8.50.08 onwards.

Sunday, May 2, 2010

Error Handling for Online and Application Engine PeopleCode

Its a common practice across programming languages to protect ones' code (PeopleCode). However, the so called scenarios that one can think of is limited because of various reasons. Hence, developers (including me till now :)) would catch the exception(s) and display something like "Unhandled exceptions encountered in ... ..."

Now coming to PeopleCode, be it the Application Engine or Online PeopleCode, we do, protect the PeopleCode we write appropriately. But wouldn't it be better if we allow PeopleCode itself to capture exceptions and display relevant text, since, PeopleSoft itself has captured all the possible scenarios that could occur, either while compiling or during execution.

The exception handling that I'm referring to is already delivered by PeopleSoft as most of the exception classes in PeopleSoft extend Exception

I created a sample Application Engine Program to demonstrate this further...I tried out code below in a PeopleCode Action ...

import SCM_UTILITIES:ExceptionUtilities:ExceptionHandler;

try
&FileName_Path_ = "D:\Temp\PSOPTIONS_" | %Date | ".xls";
/* Opening a CSV file */
&GL_LEDFile_ = GetFile(&FileName_Path_, "W", %FilePath_Absolute);
/* Checking whether the File if Opened Successfully */
If &GL_LEDFile_.IsOpen Then
&GL_QryObj_ = %Session.GetQuery();
/* Initialising the Rowset & Values */
&GL_LEDRS_ = CreateRowset(Record.PSOPTIONS);
&GL_LEDRS_.Fill();
&GL_CSVString_ = &GL_QryObj_.FormatResultString(&GL_LEDRS_, %Query_XLS, 1, &GL_LEDRS_.ActiveRowCount);
/* Writing the file with the Location Data */
&GL_LEDFile_.WriteLine(&GL_CSVString_);
Else
Error ("Error: Opening of File Failed");
End-If;
REM *** I've changed the variable name on purpose, so that a run time exception is generated as there is no file object as &GL_LEDFil_ ***;
REM *** the correct statement is &GL_LEDFile_.Close(); ***;
&GL_LEDFil_.Close();

catch Exception &ex
Local SCM_UTILITIES:ExceptionUtilities:ExceptionHandler &handler = create SCM_UTILITIES:ExceptionUtilities:ExceptionHandler();
REM *** Display the Message Text and Message Explain Text and then FAIL it!!! ***;
REM Error (&handler.getMessageText(&ex));
REM Be careful, since you’re using the messagebox and it causes an implicit COMMIT in certain tools version;

MessageBox(0, "", 0, 0, "Exception Logged > " | &handler.getMessageText(&ex));
Error (&handler.getMessageExplainText(&ex));
end-try;

The Application Engine did fail as expected, and the stdout showed the following error
This is the messageText --> Exception Logged > First operand of . is not an object, so cannot access member Close. (0,0)

This is the explanation for the messageText --> The first operand of the dot operator must be a value of some object type to access the method or property, but the value found was not an object type.

Process 10356 ABENDED at Step PP_TEST_AE.MAIN.Step02 (PeopleCode) -- RC = 4 (108,524)
It also servers another purpose, we need not use the Exit(1) feature and then set the Action On Return property to Abort
The same PeopleCode can be used for online customizations as well.