PSFT_PP

Extract Project PeopleCode and SQL

2011-03-20T19:30:00.006+05:30

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!!

PeopleTools 8.51 GA

2010-09-11T01:05:00.002+05:30

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.

How to Retrieve AccessID & AccessPWD for a PeopleSoft Environment

2010-05-13T16:56:00.019+05:30

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"

Integration Technology of PeopleSoft

2010-05-09T15:47:00.004+05:30

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.

Extract All Application Engine PeopleCode

2010-05-08T16:19:00.008+05:30

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;

Integration Broker 8.50 - UserName not defined in the database

2010-05-05T09:51:00.003+05:30

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.

Error Handling for Online and Application Engine PeopleCode

2010-05-02T17:41:00.007+05:30

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.

Improve and Performance Tune your Application Engine Programs

2010-04-26T08:42:00.003+05:30

Writing Efficient App Engine Programs

a) Apply Set Processing wherever it can be applied:

Use Group by, Having, Exists clauses effectively
Take extra care while writing sub queries and complex joins
Don't forget to join PROCESS_INSTANCE, in case you've made it a key
Fine tune the SQL ( Refer: Oracle Documentation)

b) Use Temp Table

It improves AE performance significantly- Best Suited for Set Based Processing
Facilitates parallel processing
Custom Indexes can be created to achieve faster result
Practice to make PROCESS_INSTANCE a key and employ the %Table meta-SQL
Automatic data cleanse
Dedicated Temp Table is preferred

c) Use Meta-SQL

Make a habit of using Meta-SQL like %Join, %CurrentDateIn, %Table. It gives program more flexibility.
It makes AE program more robust and platform independent and improves performance.
Be aware of the limitation of these Meta-SQL, e.g. %EffdtCheck doesn't work well with PS_JOB table
%TruncateTable is faster than bulk delete statement. In case of Temp table use %TruncateTable(%Table(XXX_TAO))

d) Drop/Rebuild Indexes:

If you're planning for massive Insert, you may wish to drop indexes and triggers from the table first and recreate them once the insert is done.
It makes AE processing much faster. Just be careful that dropping indexes makes it prone to duplicate rows. (You may leave primary key untouched).

e) Refrain from using PeopleCode

If the goal can be achieved using SQL, do not use PeopleCode.

f) Setting Commits

It's recommended to use frequent and early Commit in case of Set based processing. It reduces load from Database server and enhance performance.

g) Reuse Statement

Valid Only for SQL actions
By dedicating a persistent cursor to that statement we can reuse the SQL Statement.
When we select the ReUse property for a SQL action, %BIND fields is converted into real bind variables (like :1,:2, etc). This enables PeopleSoft Application Engine to compile the statement only once and dedicate a cursor, and re-execute it with new data multiple times. This reduction in compile time can result in dramatic improvements to performance.

h) Use %UpdateStats

For better performance, refresh statistics on temp tables using %UpdateStats(record name ,[HIGH/LOW]) after each bulk load of data.

i) Use CollectGarbage()

Specific to AE using App Classes.
Use the CollectGarbage function to remove any unreachable application objects created by the Application Classes and hence release the memory. ( Also Refer: Unix Process Limit for PeopleSoft)
However, my tests suggest that this function does not cause any memory to be released by the process.

j) Use SQL Hints (Tuning SQL)

SQL Hints e.g. /* +APPEND */,/* +FIRST_ROWS(n), provides a mechanism which instruct the CBO to choose a certain query execution plan based on the specific criteria. ( Refer: Oracle Documentation)

k) Streaming

Streaming is a process in which a large chunk of data is divided into multiple streams and all the stream processes in parallel.

l) Use Trace File

Use trace file generated by Trace/TOOLSTRACESQL/TOOLSTRACEPC advisably. Find out areas where process is taking longer processing-time or where performance can be improved.

Performance Considerations in Application Engine

It is always important to consider performance during design and development, but it is especially important when dealing with Application Engine programs. That is because AE programs often process a large number of transactions, and also because there are a number of fundamental design decisions that can have a dramatic affect on performance.

Performance tuning is often more of an art than a science, and there are exceptions to every rule. Still, the following general guidelines can be useful in achieving optimum performance.

Priority Considerations

These points should be on every designer’s and developer’s mind. They are given

priority because they can have a tremendous impact, and because it is much easier to implement them in the original design than it is to add them later.

Set-Based Processing: Instead of loop constructs such as Do Select, Do When, Do While, or Do Until, consider using UPDATE or INSERT statements that will affect many rows at once.
Temporary Tables: There are several reasons to consider using temporary tables in your program:

o The transaction tables may be too large to process directly against them with efficiency. A temporary table can hold an extract of the necessary data.

o The transaction tables may not have the correct index structure to process joins efficiently. A temporary table can be created with a more optimal index structure.

o The normalized data structure of the transaction tables may make it

difficult to access the required information. A temporary table can be

used to denormalize or “flatten” the data.

o It may not be possible to construct a single SQL statement to implement set-based processing. The temporary table can be used to store intermediate results.

o Although a single SQL statement might be possible, it may have such

complex joins that it performs badly. A temporary table again can be used to store intermediate results.

Write efficient SQL: Set-based processing often leads to SQL statements with lots of joins. There are several techniques to keep in mind when writing or tuning such statements, so that they run quickly:

o Use as many indexes as possible in the join criteria. In particular, don’t

skip high-level keys.

o Avoid unnecessary joins. For example, when deriving SetID values from the business unit, use a separate select statement to find the SetID, and store it in the state record. Then it can be used as a bind variable in the main SQL statement.

o Avoid unnecessary subselects. For example, if all the rows selected from a table will have the same effective date, then use a separate select statement to find the effective date, and store it in the state record. Then it can be used as a bind variable in the main select.

o Join to the smallest table possible. For example, if effective-dated records exist in a parent-child relationship, then use the parent record for the effective date subselect.

o Use Oracle hints. This is appropriate when tuning a slow SQL statement. See the Oracle documentation for a complete list of hints, although two particularly useful ones are RULE (to force a statement to ignore statistics) and USE_INDEX (to force a statement to use a particular index). Generating the optimizer plan for the statement (see below) may help in selecting an appropriate hint.

Other Considerations

These points can be important in certain contexts, but they either do not carry as universal or dramatic an impact as the above points, or they are easier to implement by “tweaking” an existing program.

SQL in PeopleCode: When possible, avoid using SQL in PeopleCode. It carries less overhead to use SQL actions instead.

Mathematical Calculations in PeopleCode: When possible, avoid using SQL to perform mathematical calculations. For example, it is more efficient to use PeopleCode to increment a counter. The exception to this is if the same calculation must be performed on many rows of a table – in that case, use setbased processing.

Commits: When and where commits are done should primarily be driven by restart logic. However, if doing commits within loop structures, it is a good idea to specify the commit frequency, so that commits may be done in sizable “chunks”.

ReUse Statement: For SQL actions in loop structures, turn on the “ReUse

Statement” option. The exception to this is if dynamic SQL is being used.

Bulk Insert: For INSERT statements in loop structures, use the Bulk Insert option. Note that this will not help if commits are done too frequently in the loop.

%UpdateStats: After inserting a large amount of data into a temporary table, use the %UpdateStats command. This will help the Oracle optimizer process joins more intelligently. Note that this will only work if commits are enabled.
Tracing: To identify where a performance problem is, run a trace of the

program, using the timing options. There are two important points to consider:

o When trying to pinpoint the problem turn off all tracing options other than the timing options. The overhead involved in tracing every statement can skew your results.

o If the problem has been narrowed down to a particularly complex SQL, it can help to generate the optimizer plan for that statement, using the DB Optimizer tracing option.

Java Logging for Application Engine Programs

2010-04-14T11:39:00.007+05:30

Well we all write a lot of custom batch programs, and since, a lot goes on behind the scene, we as developers need to ensure that sufficient logging takes place to help identify the problem.
For debugging, we normally make use of either a (Global or Component) file object or the famous but dreaded messagebox(). The problem with file object approach is that file handling adds an additional overhead of checking or making sure that we have opened it in the write mode and using the right charset.
Another approach most commonly used be developers is by adding numerous messageboxes to the PeopleCode, but most of them are unaware of the inherent danger, as in most of the PeopleTools versions, messagebox() fires and implicit COMMIT. This can create havoc if you have a restart enabled AE.

(Credits to the Original Uploader) Jim Marion - http://jjmpsj.blogspot.com/2008/05/appengine-output-tricks-reporting.html
came up with a wonderful solution of using the preloaded JVM on the Batch or App Server to serve as logging alternative. Please go through the above link for a detailed explanation.

The only modifications that I did to that piece of code was to paste in an Application Package and use that in all custom built Application Engines. This approach has loads of advantages on the ones discussed above (File Object or messagebox)

--The actual Application Package PeopleCode
class AE_BATCH_LOG
method AE_BATCH_LOG();

method println_to_stdout(&message As string);
method println_to_stderr(&message As string);
rem This method may be used at the discretion of the User;
method redirect_stdout(&fileName As string);
rem Use the Redirect for stderr as PS by default creates an stdout for each process;
method redirect_stderr(&fileName As string);

end-class;

/*
* The Constructor takes no arguments
*/
method AE_BATCH_LOG
end-method;

/**
* The println_to_stdout method is used to Print a line of text to Stdout.
*
* @param &message - Text to be written to Stdout.
*/
method println_to_stdout
/+ &message as String +/

try
Local string &sMethodName = "AE_BATCH_LOG:println_to_stdout";
Local JavaObject &jSystem = GetJavaClass("java.lang.System");
Local JavaObject &jOutStream = &jSystem.out;
Local JavaObject &jCls = GetJavaClass("java.lang.Class");
Local JavaObject &jStringClass = &jCls.forName("java.lang.String");
Local JavaObject &jPrintStreamCls = &jOutStream.getClass();
Local JavaObject &jPrintlnArgTypes = CreateJavaObject("java.lang.Class[]", &jStringClass);

Local JavaObject &jPrintlnMethod = &jPrintStreamCls.getDeclaredMethod("println", &jPrintlnArgTypes);

&jPrintlnMethod.invoke(&jOutStream, CreateJavaObject("java.lang.Object[]", &message));
rem ** I didn't find flushing necessary, but here is where you would flush the buffer if desired;
rem &jOutStream.flush();

catch Exception &ex
&ex.Output();
Error (MsgGetText(18130, 15001, "Unhandled exception occurred in %1.", &sMethodName));
end-try;

end-method;

/**
* The println_to_stderr method is used to Print a line of text to Stderr.
* This method should be used with a combination of redirect_stderr as PS
* maintains a single Stderr file for all processes.
* @param &message - Text to be written to Stdout.
*/
method println_to_stderr
/+ &message as String +/

try
Local string &sMethodName = "AE_BATCH_LOG:println_to_stderr";
Local JavaObject &jSystem = GetJavaClass("java.lang.System");
Local JavaObject &jOutStream = &jSystem.err;
Local JavaObject &jCls = GetJavaClass("java.lang.Class");
Local JavaObject &jStringClass = &jCls.forName("java.lang.String");
Local JavaObject &jPrintStreamCls = &jOutStream.getClass();
Local JavaObject &jPrintlnArgTypes = CreateJavaObject("java.lang.Class[]", &jStringClass);

Local JavaObject &jPrintlnMethod = &jPrintStreamCls.getDeclaredMethod("println", &jPrintlnArgTypes);

&jPrintlnMethod.invoke(&jOutStream, CreateJavaObject("java.lang.Object[]", &message));
rem ** I didn't find flushing necessary, but here is where you would flush the buffer if desired;
rem &jOutStream.flush();

catch Exception &ex
&ex.Output();
Error (MsgGetText(18130, 15001, "Unhandled exception occurred in %1.", &sMethodName));
end-try;

end-method;

/**
* The redirect_stdout method is used to redirect a line of text to a Custom file (Not a PS File Object) for general message logs.
* Use the this method prior to calling the println_to_stdout method.
*
* @param &fileName - File name of Redirected File.
*/
method redirect_stdout
/+ &fileName as String +/

try
Local string &sMethodName = "AE_BATCH_LOG:redirect_stdout";
Local JavaObject &jSystem = GetJavaClass("java.lang.System");
Local JavaObject &jfos_out = CreateJavaObject("java.io.FileOutputStream", &fileName, True);
Local JavaObject &jps_out = CreateJavaObject("java.io.PrintStream", &jfos_out, True);
&jSystem.setOut(&jps_out);

catch Exception &ex
&ex.Output();
Error (MsgGetText(18130, 15001, "Unhandled exception occurred in %1.", &sMethodName));
end-try;
end-method;

/**
* The redirect_stderr method is used to redirect a line of text to a Custom file (Not a PS File Object) for error logging.
* Use the this method prior to calling the println_to_stderr method.
*
* @param &fileName - File name of Redirected File.
*/
method redirect_stderr
/+ &fileName as String +/

try
Local string &sMethodName = "AE_BATCH_LOG:redirect_stderr";
Local JavaObject &jSystem = GetJavaClass("java.lang.System");
Local JavaObject &jfos_out = CreateJavaObject("java.io.FileOutputStream", &fileName, True);
Local JavaObject &jps_out = CreateJavaObject("java.io.PrintStream", &jfos_out, True);
&jSystem.setErr(&jps_out);

catch Exception &ex
&ex.Output();
Error (MsgGetText(18130, 15001, "Unhandled exception occurred in %1.", &sMethodName));
end-try;
end-method;
-----------------------------------
To use the logging feature all you need to do is to include the below lines in any PeopleCode action of yours in your Application Engine
-----------------------------------
import AE_BATCH_LOG:*;

/* Create Class Object for Logging */
Local AE_BATCH_LOG:AE_BATCH_LOG &AEmsgLog_
/* PeopleSoft prefers the Singleton apporach */
If None(&AEmsgLog_) Then
&AEmsgLog_ = create AE_BATCH_LOG:AE_BATCH_LOG();
------------------------------------
Once you have the class object, we need to redirect the stdout and stderr files. Its a good practice to redirect stdout, but you need to redirect the stderr always as the stderr is shared across processes.
------------------------------------
/* Create the error file in the following path */
rem Better to retrieve the path in an SQL action and store it in AET;
SQLExec("SELECT PRCSOUTPUTDIR FROM PSPRCSPARMS WHERE PRCSINSTANCE = :1", &YourPIValueFromAET, &FileDir_);
rem PS does not attach all extension types under view log trace. extension *.err being one of them. Hence using *.log;
&ErrSub_ = "AE_AENAME_" | &YourPIValuefromAET | ".log";
/* Determine the file path separator character. First check for backslash then forwardslash. */
&iTempPos = Find("\", &FileDir_);
If &iTempPos = 0 Then
&iTempPos = Find("/", &FileDir_);
&strSlash = "/";
Else
&strSlash = "\";
End-If;
&ErrFile_ = &FileDir_ | &strSlash | &ErrSub_;
/* redirect stderr */
&AEmsgLog_.redirect_stderr(&ErrFile_);
&AEmsgLog_.println_to_stderr("=============================================");

Handy SQLs & PeopleCode

2010-04-14T11:21:00.012+05:30

Hi,
Below are few SQL's which one might find handy

--Drill down for a given Component
SELECT DISTINCT C.PNLNAME, D.ADDSRCHRECNAME, D.SEARCHRECNAME ,R.RECNAME, DB.SUBRECORD AS 'IsSubRecord', P.OCCURSLEVEL AS 'REC OccursLevel', R.SQLTABLENAME AS 'Non STD SQL Name', R.BUILDSEQNO,
R.AUDITRECNAME, DB.RECNAME_PARENT, P.FIELDNAME, P.OCCURSLEVEL AS 'FLD OccursLevel', DB.DEFFIELDNAME As 'Default Value', P.FIELDTYPE,
(CASE FIELDTYPE WHEN 0 THEN 'Label' WHEN 6 THEN 'LongEditBox' WHEN 12 THEN 'PushButton' WHEN 1 THEN 'Frame' WHEN 7 THEN 'CheckBox' WHEN 13 THEN 'SecondaryPage (SUBPNLNAME)'
WHEN 2 THEN 'GroupBox' WHEN 8 THEN 'RadioButton' WHEN 14 THEN 'TreeControl' WHEN 3 THEN 'StaticImage' WHEN 9 THEN 'Image' WHEN 15 THEN 'Gridcontrol' WHEN 4 THEN 'EditBox'
WHEN 10 THEN 'ScrollBar' WHEN 16 THEN 'PushButton (GoToMenu, PNLGRPNAME)' WHEN 5 THEN 'ComboBox' WHEN 11 THEN 'SubPage' WHEN 18 THEN 'SecPage (SUBPNLNAME)' WHEN 19 THEN 'PSGrid'
WHEN 20 THEN 'Tree' WHEN 21 THEN 'PushButton (calls SecondaryPage)' WHEN 23 THEN 'HorzRule (Horizontal Line)' WHEN 24 THEN 'TabSeperator' WHEN 25 THEN 'HTML Area'
WHEN 26 THEN 'PushButton (Hyperlink to PromptAction)' WHEN 27 THEN 'Scroll Area' END) AS 'FLDType Descr',
DB.EDITTABLE, DB.SETCNTRLFLD, P.LABEL_ID AS 'Label ID', P.LBLTEXT AS 'Label Text', P.SUBPNLNAME
FROM PSPNLGROUP C INNER JOIN PSPNLFIELD P
ON C.PNLNAME = P.PNLNAME
INNER JOIN PSRECDEFN R
ON R.RECNAME = P.RECNAME
FULL OUTER JOIN PSRECFIELDDB DB
ON R.RECNAME = DB.RECNAME
AND P.FIELDNAME = DB.FIELDNAME
INNER JOIN PSPNLGRPDEFN D
ON C.PNLGRPNAME = D.PNLGRPNAME
AND C.MARKET = D.MARKET
WHERE C.PNLGRPNAME = '%YOUR COMPONENT NAME%'
ORDER BY P.OCCURSLEVEL

--Retrieve Project Objects ... The Query below is for MSSQL
SELECT (CASE OBJECTTYPE
When '0' then 'Records'
When '1' then 'Indexes'
When '2' then 'Fields'
When '3' then 'Field Formats'
When '4' then 'Translate Values'
When '5' then 'Pages'
When '6' then 'Menus'
When '7' then 'Components'
When '8' then 'Record PeopleCode'
When '9' then 'Menu PeopleCode'
When '10' then 'Queries'
When '11' then 'Tree Structures'
When '12' then 'Trees'
When '13' then 'Access Groups'
When '14' then 'Colors'
When '15' then 'Styles'
When '16' then 'Business Process Maps'
When '17' then 'Business Processes'
When '18' then 'Activities'
When '19' then 'Roles'
When '20' then 'Process Definitions'
When '21' then 'Server Definitions'
When '22' then 'Process Type Definitions'
When '23' then 'Job Definitions'
When '24' then 'Recurrence Definitions'
When '25' then 'Message Catalog Entries'
When '26' then 'Dimensions'
When '27' then 'Cube Definitions'
When '28' then 'Cube Instance Definitions'
When '29' then 'Business Interlink'
When '30' then 'SQL'
When '31' then 'File Layout Definitions'
When '32' then 'Component Interfaces'
When '33' then 'Application Engine Programs'
When '34' then 'Application Engine Sections'
When '35' then 'Message Nodes'
When '36' then 'Message Channels'
When '37' then 'Messages'
When '38' then 'Approval Rule Sets'
When '39' then 'Message PeopleCode'
When '40' then 'Subscription PeopleCode'
When '41' then 'unused'
When '42' then 'Comp. Interface PeopleCode'
When '43' then 'Application Engine PeopleCode'
When '44' then 'Page PeopleCode'
When '45' then 'Page Field PeopleCode'
When '46' then 'Component PeopleCode'
When '47' then 'Component Record PeopleCode'
When '48' then 'Component Rec Fld PeopleCode'
When '49' then 'Images'
When '50' then 'Style Sheets'
When '51' then 'HTML'
When '52' then 'unused'
When '53' then 'Permission Lists'
When '54' then 'Portal Registry Definitions'
When '55' then 'Portal Registry Structures'
When '56' then 'URL Definitions'
When '57' then 'Application Packages'
When '58' then 'Application Package PeopleCode'
When '59' then 'Portal Registry User Homepages'
When '60' then 'Analytic Types'
When '61' then 'Archive Templates'
When '62' then 'XSLT'
When '63' then 'Portal Registry User Favorites'
When '64' then 'Mobile Pages'
When '65' then 'Relationships'
When '66' then 'CI Property PeopleCode'
When '67' then 'Optimization Models'
When '68' then 'File References'
When '69' then 'File Type Codes'
When '70' then 'Archive Object Definitions'
When '71' then 'Archive Templates (Type 2)'
When '72' then 'Diagnostic Plug-Ins'
When '73' then 'Analytic Models'
When '74' then 'unused'
When '75' then 'Java Portlet User Preferences'
When '76' then 'WSRP Remote Producers'
When '77' then 'WSRP Remote Portlets'
When '78' then 'WSRP Cloned Portlets Handles'
When '79' then 'Services'
When '80' then 'Service Operations'
When '81' then 'Service Operation Handlers'
When '82' then 'Service Operation Versions'
When '83' then 'Service Operation Routings'
When '84' then 'IB Queues'
When '85' then 'XMLP Template Defn'
When '86' then 'XMLP Report Defn'
When '87' then 'XMLP File Defn'
When '88' then 'XMLP Data Src Defn'
Else 'Unknown'
END) As 'Object Type', R.OBJECTVALUE1, R.OBJECTVALUE2, R.OBJECTVALUE3, R.OBJECTVALUE4,
(CASE UPGRADEACTION WHEN 0 THEN 'Upgrade Action is Copy' WHEN 1 THEN 'Upgrade Action is Delete' WHEN 2 THEN 'Upgrade Action is None' WHEN 3 THEN 'Upgrade Action is CopyProp'
ELSE 'Unknown' END) AS 'Upgrade Action',
(CASE SOURCESTATUS WHEN 0 THEN 'Unknown' WHEN 1 THEN 'Absent' WHEN 2 THEN 'Changed' WHEN 3 THEN 'Unchanged' WHEN 4 THEN '*Changed' WHEN 5 THEN '*Unchanged' WHEN 6 THEN 'Same'
ELSE 'Unknown' END) As 'SourceStatus',
(CASE TARGETSTATUS WHEN 0 THEN 'Unknown' WHEN 1 THEN 'Absent' WHEN 2 THEN 'Changed' WHEN 3 THEN 'Unchanged' WHEN 4 THEN '*Changed' WHEN 5 THEN '*Unchanged' WHEN 6 THEN 'Same'
ELSE 'Unknown' END) As 'TargetStatus'
FROM PSPROJECTITEM R WHERE PROJECTNAME LIKE '%YOUR PROJECT NAME%'
ORDER BY OBJECTTYPE

---- Finding the Top n Tables in a Database as per Used Space - MSSQL ONLY
--Set @Count to whatever number you would like
--Set @ORDERBY to any of the four options - USED, DATA, RC - Row Count, RESD - Reserved
DECLARE @COUNT INT;
DECLARE @ORDERBY VARCHAR(4);
SET @COUNT = 50;
SET @ORDERBY = 'USED';
BEGIN try
IF ((SELECT CASE WHEN @ORDERBY in ('USED','DATA','RC','RESD') THEN 1 ELSE 0 END) = 0)
BEGIN
-- abort if invalid @ORDERBY parameter entered
RAISERROR('@ORDERBY parameter not USED, DATA, RC or RESD',11,1)
RETURN
END
SELECT TOP (@COUNT)
--(row_number() over(order by a3.name, a2.name))%2 as l1,
a2.name AS [TableName],
sum(a1.rows) as row_count,
sum((a1.reserved + ISNULL(a4.reserved,0))* 8) AS reserved,
sum(a1.used * 8)[Used],
sum(a1.data * 8) AS data,
sum((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN
(a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 )AS index_size,
sum((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN
(a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8) AS unused
FROM
(SELECT ps.object_id, SUM (CASE WHEN (ps.index_id < object_id =" ps.object_id)" parent_id =" a1.object_id)" object_id =" a2.object_id" schema_id =" a3.schema_id)"> 'S' and a2.type <> 'IT'
AND a1.used <> 0
GROUP BY a2.name
--ORDER BY a2.name
ORDER BY CASE
WHEN @ORDERBY = 'USED' THEN sum(a1.used * 8)
WHEN @ORDERBY = 'DATA' THEN sum(a1.data * 8)
WHEN @ORDERBY = 'RC' THEN sum(a1.rows)
WHEN @ORDERBY = 'RESD' THEN sum((a1.reserved + ISNULL(a4.reserved,0))* 8)
END DESC
end try
begin catch
select
-100 as l1
, 1 as schemaname
, ERROR_NUMBER() as tablename
, ERROR_SEVERITY() as row_count
, ERROR_STATE() as reserved
, ERROR_MESSAGE() as data
, 1 as index_size
, 1 as unused
end catch

--rem One of the better ways to write SaveEdit Validation for a Field. Only for Contextual Reference though;
&err_style = "PSERROR";
&normal_style = "PSEDITBOX";
&field = GetField();
If Not All(&field.Value) Then
&label = &field.GetLongLabel(&field.Name);
&field.Style = &err_style;
rem Press SHIFT + ^ for the FieldName to appear in the SetCursorPos Function Below. Field shown is for reference ONLY;
rem you only need to change the line below
SetCursorPos(%Page, USE SHIFT + 6 for the field);
Error MsgGet(5930, 1, "", &label);
Else
&field.Style = &normal_style;
End-If;

-- Read an encrypted file and decrypt it. Good for stealing any document
import TR:FileUtilities:*;

Local JavaObject &psCYPHER = CreateJavaObject("psft.pt8.pshttp.PSCipher");
&PPFile_ = GetFile("D:\temp\EncryptedFile.out", "R", "A", %FilePath_Absolute);

Local TR:FileUtilities:Files &PPsSingleton_;
If None(&PPsSingleton_) Then
&PPsSingleton_ = create TR:FileUtilities:Files();
End-If;
&encPassword = &PPsSingleton_.GetFileAsString(&PPFile_);
&DecPwd = &psCYPHER.decodePassword(&encPassword);

&PPOutFile_ = GetFile("D:\temp\ActualDecryptedFile.out", "W", "A", %FilePath_Absolute);
&PPOutFile_.WriteString(&DecPwd);

&psCYPHER = Null;

-- Nice use of MetaSQL in Application Engine Programs
It's handy to keep a log of your batch programs, as the process scheduler tables get purged frequently. The SQL below shows a nice way of using the MetaSQL, and this can be used for any/all your custom App Engines. This can be used in the last SQL action before your Application Engine finishes.

The MetaSQL - AE$LOG$SQL only consists of '%P(1)'

INSERT INTO PS_AE_LOG_TBL (PROCESS_INST_STG
,OPRID
,RUN_CNTL_ID
,AE_APPLID
,ACTIONDTTM
,ENDDTTM
,DURATION_TIME)
VALUES (%Sql(AE$LOG$SQL,%Bind(YOURAET.PROCESS_INST_STG) ,10)
,%Sql(AE$LOG$SQL,%Bind(YOURAET.OPRID),30)
,%Sql(AE$LOG$SQL,%Bind(YOURAET.RUN_CNTL_ID),30)
,%AEProgram
,%Sql(AE$LOG$SQL,%Bind(YOURAET.ACTIONDTTM),26)
,%CurrentDateTimeIn
,%DateTimeDiff(%Sql(AE$LOG$SQL,%Bind(YOURAET.ACTIONDTTM),26), %CurrentDateTimeIn))

-- Find the Navigation of a Process (AE & SQR)
SELECT DISTINCT LTRIM(RTRIM(B.MENUGROUP)) +
' > ' + LTRIM(RTRIM(B.MENULABEL)) +
' > ' + LTRIM(RTRIM(BARLABEL)) +
' > ' + LTRIM(RTRIM(A.ITEMLABEL)) +
' > ' + LTRIM(RTRIM(D.PNLGRPNAME)) +
' – ' + LTRIM(RTRIM(PRCSTYPE)) +
' – ' + LTRIM(RTRIM(PRCSNAME)) NAVIGATION
FROM PSMENUITEM A, PSMENUDEFN B, PS_PRCSDEFNPNL C, PSPNLGROUP D
WHERE A.MENUNAME = B.MENUNAME
AND A.PNLGRPNAME = C.PNLGRPNAME
AND A.PNLGRPNAME = D.PNLGRPNAME
-- Substitute the PRCSNAME value with the Process Name that you are looking for
AND PRCSNAME = 'POPO005'
GROUP BY B.MENUNAME, B.MENUGROUP, B.MENULABEL, BARLABEL, A.ITEMLABEL,
D.PNLGRPNAME, PRCSTYPE, PRCSNAME

Caveat - No rows DOES NOT MEAN that the process is not used. It could either be called from an AE or be a part of a PSJob.

--Projects Items List via SQL for MSSQL
Credits to the Original uploader
SELECT OBJECTTYPE,
CASE OBJECTTYPE
WHEN 0 THEN CASE ISNULL((SELECT RECTYPE FROM PSRECDEFN WHERE RECNAME = I.OBJECTVALUE1), 99)
WHEN 0 THEN 'SQL Table in DB'
WHEN 1 THEN 'SQL View in DB'
WHEN 2 THEN 'Work Record'
WHEN 3 THEN 'Sub Record'
WHEN 5 THEN 'Dynamic View'
WHEN 6 THEN 'Query View'
WHEN 7 THEN 'Temporary Table'
ELSE 'Other Record or Deleted' END

WHEN 1 THEN 'Index'
WHEN 2 THEN 'Field'
WHEN 3 THEN 'Field Format'
WHEN 4 THEN 'Translate Value'
WHEN 5 THEN 'Page'
WHEN 6 THEN 'Menu'
WHEN 7 THEN 'Component'
WHEN 8 THEN 'Record PeopleCode'
WHEN 9 THEN 'Menu PeopleCode'
WHEN 10 THEN 'Query'
WHEN 11 THEN 'Tree Structure'
WHEN 12 THEN 'Tree'
WHEN 13 THEN 'Access Group'
WHEN 14 THEN 'Color'
WHEN 15 THEN 'Style'
WHEN 16 THEN 'Business Process Map'
WHEN 17 THEN 'Business Process'
WHEN 18 THEN 'Activity'
WHEN 19 THEN 'Role'
WHEN 20 THEN 'Process Definition'
WHEN 21 THEN 'Process Server Definition'
WHEN 22 THEN 'Process Type Definition'
WHEN 23 THEN 'Process Job Definition'
WHEN 24 THEN 'Process Recurrence Definition'
WHEN 25 THEN 'Message Catalog'
WHEN 26 THEN 'Dimension'
WHEN 27 THEN 'Cube Definition'
WHEN 28 THEN 'Cube Instance Definition'
WHEN 29 THEN 'Business Interlink'
WHEN 30 THEN CASE OBJECTVALUE2
WHEN '0' THEN 'SQL Object'
WHEN '1' THEN 'App Engine SQL'
WHEN '2' THEN 'Record View SQL'
WHEN '5' THEN 'Query for DDAUDIT or SYSAUDIT'
WHEN '6' THEN 'App Engine XML SQL'
ELSE 'SQL' END
WHEN 31 THEN 'File Layout'
WHEN 32 THEN 'Component Interface'
WHEN 33 THEN 'Application Engine Program'
WHEN 34 THEN 'Application Engine Section'
WHEN 35 THEN 'Message Node'
WHEN 36 THEN 'Message Channel'
WHEN 37 THEN 'Message'
WHEN 38 THEN 'Approval rule set'
WHEN 39 THEN 'Message PeopleCode'
WHEN 40 THEN 'Subscription PeopleCode'
WHEN 41 THEN 'N/A'
WHEN 42 THEN 'Component Interface PeopleCode'
WHEN 43 THEN 'Application Engine PeopleCode'
WHEN 44 THEN 'Page PeopleCode'
WHEN 45 THEN 'Page Field PeopleCode'
WHEN 46 THEN 'Component PeopleCode'
WHEN 47 THEN 'Component Record PeopleCode'
WHEN 48 THEN 'Component Record Field PeopleCode'
WHEN 49 THEN 'Image'
WHEN 50 THEN 'Style sheet'
WHEN 51 THEN 'HTML'
WHEN 52 THEN 'Not used'
WHEN 53 THEN 'Permission List'
WHEN 54 THEN 'Portal Registry Definitions'
WHEN 55 THEN 'Portal Registry Structure'
WHEN 56 THEN 'URL Definitions'
WHEN 57 THEN 'Application Packages'
WHEN 58 THEN 'Application Package PeopleCode'
WHEN 59 THEN 'Portal Registry User Homepage'
WHEN 60 THEN 'Problem Type'
WHEN 61 THEN 'Archive Templates'
WHEN 62 THEN 'XSLT'
WHEN 63 THEN 'Portal Registry User Favorite'
WHEN 64 THEN 'Mobile Page'
WHEN 65 THEN 'Relationships'
WHEN 66 THEN 'Component Interface Property PeopleCode'
WHEN 67 THEN 'Optimization Models'
WHEN 68 THEN 'File References'
WHEN 69 THEN 'File Type Codes'
WHEN 70 THEN 'Archive Object Definitions'
WHEN 71 THEN 'Archive Templates (Type 2)'
WHEN 72 THEN 'Diagnostic Plug In'
WHEN 73 THEN 'Analytic Model'
WHEN 74 THEN 'unused'
WHEN 75 THEN 'Java Portlet User Preferences'
WHEN 76 THEN 'WSRP Remote Producers'
WHEN 77 THEN 'WSRP Remote Portlets'
WHEN 78 THEN 'WSRP Cloned Portlets Handles'
WHEN 79 THEN 'Service'
WHEN 80 THEN 'Service Operation'
WHEN 81 THEN 'Service Operation Handler'
WHEN 82 THEN 'Service Operation Version'
WHEN 83 THEN 'Service Operation Routing'
WHEN 84 THEN 'Info Broker Queues'
WHEN 85 THEN 'XLMP Template Definition'
WHEN 86 THEN 'XLMP Report Definition'
WHEN 87 THEN 'XMLP File Definition'
WHEN 88 THEN 'XMPL Data Source Definition'
WHEN 89 THEN 'WSDL'
WHEN 90 THEN 'Schema'
WHEN 91 THEN 'Connected Queries'
WHEN 92 THEN 'Logical Schema'
WHEN 93 THEN 'Physical Schema'
WHEN 94 THEN 'Relational Schema'
WHEN 95 THEN 'Logical Schema Dependancy'
WHEN 96 THEN 'Document Schema'
WHEN 97 THEN 'Essbase Cube Dimensions'
WHEN 98 THEN 'Essbase Cube Outlines'
WHEN 99 THEN 'Essbase Cube Connections'
WHEN 100 THEN 'Essbase Cube Templates'
ELSE 'Unknown ' END AS Object_Type,
CASE OBJECTTYPE
WHEN 12 THEN OBJECTVALUE3
WHEN 30 THEN CASE WHEN OBJECTVALUE2 = 0 THEN OBJECTVALUE1
WHEN OBJECTVALUE2 = 1 THEN SUBSTRING(OBJECTVALUE1, 1, 12)
WHEN OBJECTVALUE2 = 2 THEN OBJECTVALUE1
ELSE ' ' END
WHEN 34 THEN ltrim(RTRIM(OBJECTVALUE1)) + '.' + ltrim(RTRIM(OBJECTVALUE2))
WHEN 62 THEN ltrim(rTRIM(SUBSTRING(OBJECTVALUE1, 1, 12)))
ELSE OBJECTVALUE1 END AS NAME,
CASE
WHEN OBJECTTYPE = 1 THEN 'Index: ' + OBJECTVALUE2
WHEN OBJECTTYPE = 4 THEN 'XLAT: ' + OBJECTVALUE2 + '; Date: ' + OBJECTVALUE3 + '; ' +
ISNULL((SELECT 'ShortName: ' + XLATSHORTNAME + '; LongName: ' +
XLATLONGNAME + '; Status: ' + EFF_STATUS
FROM PSXLATITEM
WHERE FIELDNAME = I.OBJECTVALUE1 AND FIELDVALUE = I.OBJECTVALUE2
AND EFFDT = I.OBJECTVALUE3), 'XLAT Deleted')
WHEN OBJECTTYPE = 7 THEN 'Market: ' + OBJECTVALUE2
WHEN OBJECTTYPE = 8 THEN OBJECTVALUE1 + '.' + OBJECTVALUE2 + '.' + OBJECTVALUE3
WHEN OBJECTTYPE = 9 THEN OBJECTVALUE2 + '.' + OBJECTVALUE3 + '.' + OBJECTVALUE4
WHEN OBJECTTYPE = 12 THEN 'EFFDT: ' + OBJECTVALUE4
WHEN OBJECTTYPE = 20 THEN 'Process Name: ' + OBJECTVALUE2
WHEN OBJECTTYPE IN(22, 40) THEN OBJECTVALUE2 + '.' + OBJECTVALUE3
WHEN OBJECTTYPE = 25 THEN 'Message: ' + OBJECTVALUE2 + '(Message Set Descr: ' + ltrim(rtrim(OBJECTVALUE3)) + ')'
WHEN OBJECTTYPE = 30 THEN
CASE WHEN OBJECTVALUE2 = 0 THEN ' '
WHEN OBJECTVALUE2 = 1 THEN 'AE Progam: ' +
SUBSTRING(OBJECTVALUE1, 1, 12) + ' Section: ' +
SUBSTRING(OBJECTVALUE1, 13, 8) + ' Step: ' +
SUBSTRING(OBJECTVALUE1, 21, 8) + ' Type: ' +
CASE WHEN SUBSTRING(OBJECTVALUE1, 29, 1) = 'S' THEN 'SQL'
WHEN SUBSTRING(OBJECTVALUE1, 29, 1) = 'D' THEN 'Do Select'
WHEN SUBSTRING(OBJECTVALUE1, 29, 1) = 'W' THEN 'Do While'
WHEN SUBSTRING(OBJECTVALUE1, 29, 1) = 'H' THEN 'Do When'
WHEN SUBSTRING(OBJECTVALUE1, 29, 1) = 'N' THEN 'Do Until'
Else 'Unknown' END
WHEN OBJECTVALUE2 = 2 THEN ' '
ELSE ' ' END
WHEN OBJECTTYPE = 38 THEN 'EFFDT: ' + OBJECTVALUE2
WHEN OBJECTTYPE IN(39, 42, 44) THEN OBJECTVALUE2
WHEN OBJECTTYPE = 43 THEN
CASE WHEN ltrim(rTRIM(OBJECTVALUE4)) = 'OnExecute' THEN
'Section: ' + SUBSTRING(OBJECTVALUE2, 1, 8) + '; Step: ' +
OBJECTVALUE3 + '; Market: ' + SUBSTRING(OBJECTVALUE2, 9, 3) +
'; Database: ' + ltrim(rTRIM(SUBSTRING(OBJECTVALUE2, 12, 8))) +
'; EFFDT: ' + ltrim(rTRIM(SUBSTRING(OBJECTVALUE2, 21, 10)))
ELSE 'Section: ' + OBJECTVALUE2 + '; Market: ' + OBJECTVALUE3
+ '; Database: ' + ltrim(rTRIM(SUBSTRING(OBJECTVALUE4, 12, 8))) +
'; EFFDT: ' + ltrim(rTRIM(SUBSTRING(OBJECTVALUE4, 21, 10))) END
WHEN OBJECTTYPE = 46 THEN 'Market: ' + OBJECTVALUE2 + '; Event: ' + OBJECTVALUE3
WHEN OBJECTTYPE = 47 THEN 'Market: ' + OBJECTVALUE2 + '; Record: ' + OBJECTVALUE3 + '; Event: ' + OBJECTVALUE4
WHEN OBJECTTYPE = 48 THEN 'Market: ' + OBJECTVALUE2 + '; Record: ' + OBJECTVALUE3
+ '; Field: ' + ltrim(rTRIM(SUBSTRING(OBJECTVALUE4, 1, 18)))
+ '; Event: ' + ltrim(rTRIM(SUBSTRING(OBJECTVALUE4, 19, 16)))
WHEN OBJECTTYPE = 55 THEN
CASE WHEN ltrim(rtrim(OBJECTVALUE2)) = 'C' THEN 'CREF: ' ELSE 'Folder: ' END + OBJECTVALUE3
WHEN OBJECTTYPE = 57 THEN
CASE WHEN ltrim(rTRIM(OBJECTVALUE4)) NOT IN(' ', ':', '.') THEN
'Subclass: ' + ltrim(rTRIM(OBJECTVALUE2)) + ':' + ltrim(rTRIM(OBJECTVALUE3))
+ ':' + ltrim(rTRIM(OBJECTVALUE4))
ELSE
CASE WHEN ltrim(rTRIM(OBJECTVALUE3)) NOT IN(' ', ':', '.') THEN
'Subclass: ' + ltrim(rTRIM(OBJECTVALUE2)) + ':' +
ltrim(rTRIM(OBJECTVALUE3))
ELSE
CASE WHEN ltrim(rTRIM(OBJECTVALUE2)) NOT IN(' ', ':', '.') THEN
'Subclass: ' + ltrim(rTRIM(OBJECTVALUE2))
ELSE ' '
END
END
END
WHEN OBJECTTYPE IN(58, 63, 68, 81, 82, 83, 87, 88) THEN
CASE WHEN ltrim(rTRIM(OBJECTVALUE4)) IS NOT NULL THEN
ltrim(rTRIM(OBJECTVALUE2)) + '.' + ltrim(rTRIM(OBJECTVALUE3)) + '.'
+ ltrim(rTRIM(OBJECTVALUE4))
ELSE
CASE WHEN ltrim(rTRIM(OBJECTVALUE3)) IS NOT NULL THEN
ltrim(rTRIM(OBJECTVALUE2)) + '.' + ltrim(rTRIM(OBJECTVALUE3))
ELSE
CASE WHEN ltrim(rTRIM(OBJECTVALUE2)) IS NOT NULL THEN
ltrim(rTRIM(OBJECTVALUE2))
ELSE ' '
END
END
END
WHEN OBJECTTYPE = 59 THEN ltrim(rTRIM(OBJECTVALUE2))
WHEN OBJECTTYPE = 62 THEN 'AE Progam: ' + SUBSTRING(OBJECTVALUE1, 1, 12) + ' Section: ' +
SUBSTRING(OBJECTVALUE1, 13, 8) + ' Step: ' + SUBSTRING(OBJECTVALUE1, 21, 8)
ELSE ' ' END AS EXTENDED_OBJ_NAME,
CASE OBJECTTYPE
WHEN 0 THEN ISNULL((SELECT RECDESCR FROM PSRECDEFN WHERE RECNAME = I.OBJECTVALUE1), ' ')
WHEN 1 THEN ISNULL((SELECT IDXCOMMENTS FROM PSINDEXDEFN WHERE RECNAME = I.OBJECTVALUE1 AND INDEXID = I.OBJECTVALUE2), ' ')
WHEN 2 THEN ISNULL((SELECT DESCRLONG FROM PSDBFIELD WHERE FIELDNAME = I.OBJECTVALUE1), ' ')
WHEN 3 THEN ISNULL((SELECT DESCR FROM PSFMTDEFN WHERE FORMATFAMILY = I.OBJECTVALUE1), ' ')
WHEN 5 THEN ISNULL((SELECT DESCR FROM PSPNLDEFN WHERE PNLNAME = I.OBJECTVALUE1), ' ')
WHEN 6 THEN ISNULL((SELECT DESCR FROM PSMENUDEFN WHERE MENUNAME = I.OBJECTVALUE1), ' ')
WHEN 7 THEN ISNULL((SELECT DESCR FROM PSPNLGRPDEFN WHERE PNLGRPNAME = I.OBJECTVALUE1 AND MARKET = I.OBJECTVALUE2), ' ')
WHEN 10 THEN ISNULL((SELECT DESCR FROM PSQRYDEFN WHERE QRYNAME = I.OBJECTVALUE1), ' ')
WHEN 12 THEN ISNULL((SELECT DESCR FROM PSTREEDEFN WHERE TREE_NAME = I.OBJECTVALUE3), ' ')
WHEN 13 THEN ISNULL((SELECT DESCR FROM PS_ACCESS_GRP_TBL WHERE ACCESS_GROUP = I.OBJECTVALUE1), ' ')
WHEN 17 THEN ISNULL((SELECT DESCR60 FROM PSBUSPROCDEFN WHERE BUSPROCNAME = I.OBJECTVALUE1), ' ')
WHEN 18 THEN ISNULL((SELECT DESCR60 FROM PSACTIVITYDEFN WHERE ACTIVITYNAME = I.OBJECTVALUE1), ' ')
WHEN 19 THEN ISNULL((SELECT DESCR FROM PSROLEDEFN WHERE ROLENAME = I.OBJECTVALUE1), ' ')
WHEN 20 THEN ISNULL((SELECT DESCR FROM PS_PRCSDEFN WHERE PRCSTYPE = I.OBJECTVALUE1 AND PRCSNAME = I.OBJECTVALUE2), ' ')
WHEN 23 THEN ISNULL((SELECT DESCR FROM PS_PRCSJOBDEFN WHERE PRCSJOBNAME = I.OBJECTVALUE1), ' ')
WHEN 21 THEN ISNULL((SELECT DESCR FROM PS_SERVERDEFN WHERE SERVERNAME = I.OBJECTVALUE1), ' ')
WHEN 25 THEN ISNULL((SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = I.OBJECTVALUE1 AND MESSAGE_NBR = I.OBJECTVALUE2), ' ')
WHEN 31 THEN ISNULL((SELECT DESCR FROM PSFLDDEFN WHERE FLDDEFNNAME = I.OBJECTVALUE1), ' ')
WHEN 32 THEN ISNULL((SELECT DESCR FROM PSBCDEFN WHERE BCNAME = I.OBJECTVALUE1), ' ')
WHEN 33 THEN ISNULL((SELECT DESCR FROM PSAEAPPLDEFN WHERE AE_APPLID = I.OBJECTVALUE1), ' ')
WHEN 34 THEN ISNULL((SELECT TOP 1 DESCR FROM PSAESECTDTLDEFN WHERE AE_APPLID = I.OBJECTVALUE1 AND AE_SECTION = I.OBJECTVALUE2), ' ')
WHEN 53 THEN ISNULL((SELECT CLASSDEFNDESC FROM PSCLASSDEFN WHERE CLASSID = I.OBJECTVALUE1), ' ')
WHEN 56 THEN ISNULL((SELECT URL FROM PSURLDEFN WHERE URL_ID = I.OBJECTVALUE1), ' ')
ELSE ' ' END AS DESCR
FROM PSPROJECTITEM I
WHERE PROJECTNAME = 'PPLTLS84CUR'
ORDER BY OBJECTTYPE, 2, OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4

RealTime Message Log

2009-07-20T00:02:00.008+05:30

Most of us have gone through the pain of switching windows while debugging our Batch programs especially Application Engines (Actual Sequence - Click Run -> Click OK -> Click Process Monitor -> Now Like a !#! keep clicking the Refresh Button or switch between details/message log and Process Monitor - So frustrating). It would have been a lot easier had PS provided a refresh button on the message log page.

The Vanilla run control page for Voucher Build process has a solution to this problem. It has 2 delivered pages which the user can use to view the message log and actually refresh it whenever he/she wants it. Better than navigating to the Process Monitor page and waiting for the process to finish.

I also came across PS delivered Component AE_LOG which is on the same lines. The design is far better than the existing one. It can even show you the Trace/Message Log for the current and previous process instances for the OPRID/RUN_CNTL_ID combination, but the only limitation I feel is that, it presently only looks at the AE Request component.

I'm currently working on a page that combines both these functionalities. Once completed this can be extended to all custom run control components we create. Presently I'm testing this for Application Engines only (I HATE SQR's).

All that the developer needs to ensure is that:

1. Copy a bit of PeopleCode to import this functionality, and

2. Insert this page in his/her Run Control Component. All that is now required is to run the process and navigate to the this page rather than the process monitor page, as this will show you Real Time Message Logs.

Also trying to identify whether these reports will be accessible from the Run Control Page after the PRCSYSPURGE is run.

I'll post more on this once I'm done

Cheerz!!

The App Package TR

2009-07-19T23:54:00.006+05:30

This is by far one of the better App Packages that PS delivers in FSCM. Under the Package FileUtilities there are 2 App Classes FTP & Files.

The app class Files contains certain methods which are written in a very refined and polished manner. For example the method GetFileAsString is used to read a file in exactly the same manner in which it is stored in the File System. To do this PS does not use PeopleCode. Instead it uses JAVA, since it can retrieve the file in the same exact manner. Also, what one must note is that the method does take into account of reading/opening of large files as it has a check for the chunk size to prevent the dreaded Out of memory error.

What is also strange is that even though PS provides a Function FindFiles() to retrieve all the files Names as string from a directory, it does not use it in the method GetFileList.

The method LoadCleanFile is also useful when you are trying to read through plain text files with out using a File Layout.

This app package also contains certain interesting utilities under the Package Utilities.

Run DMS in Regular/Bootstrap mode from PIA

2009-07-19T23:39:00.008+05:30

Ever wondered if you could run DMS scripts from PIA in regular mode.

Well PS does deliver a Process Type of Data Mover, but I've rarely seen it being used.
PS itself delivers a page to run the DMS from PIA in regular mode, but it can only be a single DMS file.

I extended that solution to suit my needs. Have the user upload (DMS) as many as he/she wants and can then run DMS scripts in Regular/Bootstrap mode through the PIA. The DAT generated can then be downloaded from the View/Log Trace link and then can be emailed through the same page to the PSAdmins (You could even send it through your official mail. But what the @#@ I wanted the user to send it from the same page). The attachment functionality though commonly used by many people, is always written by the developers. Rarely have I seen people using the delivered functionality. In FSCM PS delivers a generic Attachment functionality which covers the Life cycle of attachments. The App Class that has to use to achieve this is SCM_ARCH_ATTACH:UI:AttachmentHandler. The exhaustive comments in the class clearly detail what needs to be done by the developers to use it. It absolutely eliminates the need for users to write their own code. I really wouldn't repeat the steps out in my blog. I would suggest you go through the class and try it out.

The process type DATA Mover will always run in Regular mode since the command line always takes the %%OPRID%% and %%OPRPSWD%%, however, if you choose %%ACCESSID%% and %%ACCESSPSWD%%, then you can essentially log in in Bootstrap mode through the PIA. For this I suggest create a new Process Type rather than changing the existing one and for the new Process Type set the command line as
"-CT %%DBTYPE%% -CD %%DBNAME%% -CO %%ACCESSID%% -CP %%ACCESSPSWD%% -I %%INSTANCE%%"

Once done open the Process Definition and set the following for the PARMLIST - "-FP :Your run control record name.FieldName - which will store the file name"

One very good thing about all this is that the user need not write any code for error logging as internally PS calls psdmtx.exe, hence all the error logging will be identical to the way Data mover logs error.

And the best part - The DAT & Log file(s) are attached automatically in the View/Log Trace. So the user irrespective of the SET OUTPUT & SET LOG commands can view/download the DAT files from the process monitor.

Another good part about all this is that the page allows the user to simulate the DMS. This is what the SET NO DATA command does. It will run through the entire script but will neither export nor import, hence, no DB changes. So in case you have huge DMS files uploaded by the user and you are not too comfortable with the DDL and DML statements, you can Simulate the DMS

PeopleSoft's metadata API

2009-07-19T23:11:00.008+05:30

Ever stumbled across a familiar statement
import %metadata:*;

Well PS itself rarely uses this. I was lucky enough to realize its potential during a Apps/Tools upgrade. We were moving from FSCM 8/8.21 to FSCM 8.9/8.48.

This metadata API has not been documented anywhere in PeopleBooks. I feel this is by far the most robust API that PS has delivered till date. I actually went ahead and used this API as pilot to see the real benefits of it. The custom solution was designed to create PS Projects and migrate them from PIA itself. Though the script used to compare (Pre/Post) and migrate the project was courtesy Written by Praj Basnet & Copyright 2008 the custom solution is totally original :)

The API can be extended as follows

import %metadata:ProjectDefn:*;
import %metadata:RecordDefn:*;

I only tried it for a few more objects, but you realize it can be similarly extended for all other DEFN Tables (PeopleTools definition tables used for PS Objects)

What is important to note is that to use this API you need to make sure you keep the following in mind

For all imports like above, you need to define a manager object:

For example - &mgr = create %metadata:ProjectDefn:ProjectDefn_Manager();
&mgrRec_ = create %metadata:RecordDefn:RecordDefn_Manager();
2. A similar property also needs to be defined:

For example - property %metadata:ProjectDefn:ProjectDefn_Manager mgr;
property %metadata:RecordDefn:RecordDefn_Manager mgrRec_;

You can then define your own methods like

1. insertAppPackage
2. insertRecord
3. insertAE

and so on to cover all/some PS Objects

But the most important of all methods is the insertItemToProject

Every method for an object will ultimately call this method. PS stores the values in PSPROJECTITEM as OBJECTVALUE#'s. The exhaustive list of the objects till date can be found at

http://jmcmahon33.blogspot.com/2007/12/objects-within-peoplesoft-projects.html

Some very basic PeopleCode to get you started -
/******************************************/
import %metadata:*;
import %metadata:ProjectDefn:*;
import %metadata:RecordDefn:*;

class PPProject
method PPProject();
method CreateProject();
method insertItemToProject(&iObjectType As integer, &iObjectID0 As integer, &sObjectValue0 As string, &iObjectID1 As integer, &sObjectValue1 As string, &iObjectID2 As integer, &sObjectValue2 As string, &iObjectID3 As integer, &sObjectValue3 As string, &iUpgradeAction As integer, &defn As %metadata:ProjectDefn:ProjectDefn);
method insertAE(&sAEProgramName As string);
method insertRecord(&sRecordName As string);
method insertAppPackage(&sAppPackage As string);
method initializeProjects();
method getProject(&sProjectName As string) Returns %metadata:ProjectDefn:ProjectDefn;
method getRecordDoNotUse(&sRecordName As string) Returns %metadata:RecordDefn:RecordDefn;
property string sCreateProject;
property %metadata:ProjectDefn:ProjectDefn_Manager mgr;
property %metadata:RecordDefn:RecordDefn_Manager mgrRec_;
end-class;

method PPProject
&mgr = create %metadata:ProjectDefn:ProjectDefn_Manager();
&mgrRec_ = create %metadata:RecordDefn:RecordDefn_Manager();
rem &sCreateProject = "PTPATCHIBCONVERT";
%This.CreateProject();
end-method;

method CreateProject

rem this class will be used to create Projects in App Designer;
&sCreateProject = Z_LONG_TBL.EMPLID.Value;
MessageBox(0, "", 0, 0, "&ProjectName_ " | &sCreateProject);

end-method;

method insertItemToProject
/+ &iObjectType as Integer, +/
/+ &iObjectID0 as Integer, +/
/+ &sObjectValue0 as String, +/
/+ &iObjectID1 as Integer, +/
/+ &sObjectValue1 as String, +/
/+ &iObjectID2 as Integer, +/
/+ &sObjectValue2 as String, +/
/+ &iObjectID3 as Integer, +/
/+ &sObjectValue3 as String, +/
/+ &iUpgradeAction as Integer, +/
/+ &defn as %Metadata:ProjectDefn:ProjectDefn +/

Local string &sProjectName;

/*Check to see if the item already exists in the project , modified the select to query only basd on keys*/
rem SQLExec("SELECT PROJECTNAME FROM PSPROJECTITEM WHERE PROJECTNAME = :1 AND OBJECTTYPE = :2 AND OBJECTID1 = :3 AND OBJECTVALUE1 = :4 AND OBJECTID2 = :5 AND OBJECTVALUE2 = :6 AND OBJECTID3 = :7 AND OBJECTVALUE3 = :8", &defn.ProjectName, &iObjectType, &iObjectID0, &sObjectValue0, &iObjectID1, &sObjectValue1, &iObjectID2, &sObjectValue2, &sProjectName);
SQLExec("SELECT PROJECTNAME FROM PSPROJECTITEM WHERE PROJECTNAME = :1 AND OBJECTTYPE = :2 AND OBJECTVALUE1 = :3 AND OBJECTVALUE2 = :4 AND OBJECTVALUE3 = :5", &defn.ProjectName, &iObjectType, &sObjectValue0, &sObjectValue1, &sObjectValue2, &sProjectName);

If &sProjectName <> "" Then
Return;
End-If;

Local %metadata:ProjectDefn:PjmPit &newPit;

Local integer &iCount = &defn.Count_Pit;

&newPit = &defn.Append_Pit(&iCount);
&newPit.ObjectType = &iObjectType;
&newPit.ObjectID#0# = &iObjectID0;
&newPit.ObjectValue#0# = &sObjectValue0;
&newPit.ObjectID#1# = &iObjectID1;
&newPit.ObjectValue#1# = &sObjectValue1;
&newPit.ObjectID#2# = &iObjectID2;
&newPit.ObjectValue#2# = &sObjectValue2;
&newPit.ObjectID#3# = &iObjectID3;
&newPit.ObjectValue#3# = &sObjectValue3;
&newPit.TakeAction = True;
/* 1 for Delete, 0 for Copy */
&newPit.UpgradeAction = &iUpgradeAction;

If Not (&defn.UpdateDefn()) Then
/*Todo throw error */
End-If;
end-method;

method insertAE
/+ &sAEProgramName as String +/

If &sCreateProject <> "" Then
Local %metadata:ProjectDefn:ProjectDefn &defn;
&defn = %This.getProject(&sCreateProject);
rem By default insert all related definitions;
%This.insertItemToProject(33, 66, &sAEProgramName, 0, "", 0, "", 0, "", 0, &defn);
%This.insertItemToProject(34, 66, &sAEProgramName, 77, "MAIN", 0, "", 0, "", 0, &defn);
%This.insertItemToProject(43, 66, &sAEProgramName, 77, "MAIN GBLdefault 1900-01-01", 78, "Step02", 12, "OnExecute", 0, &defn);
End-If;

end-method;

method insertRecord
/+ &sRecordName as String +/

If &sCreateProject <> "" Then
Local %metadata:ProjectDefn:ProjectDefn &defn;
Local %metadata:RecordDefn:RecordDefn &Recdefn_;
&defn = %This.getProject(&sCreateProject);
&Recdefn_ = %This.getRecordDoNotUse(&sRecordName);
rem &Recdefn_.RecDescr = "AE REN State Record";
%This.insertItemToProject(0, 1, &sRecordName, 0, "", 0, "", 0, "", 0, &defn);
%This.insertItemToProject(8, 1, &sRecordName, 0, "", 0, "", 0, "", 0, &defn);
End-If;

end-method;

method insertAppPackage
/+ &sAppPackage as String +/

Local string &AppClassID_, &PckgRoot_, &QlfyPath_, &QlfyPath1_;
If &sCreateProject <> "" Then
Local %metadata:ProjectDefn:ProjectDefn &defn;
&defn = %This.getProject(&sCreateProject);
rem Ideally queries should be used to retrieve the defns from Object Defn Tables;
Local string &AppPackageSQL_ = "SELECT PACKAGEROOT, QUALIFYPATH FROM PSPACKAGEDEFN WHERE PACKAGEID = :1";
Local string &AppClassSQL_ = "SELECT APPCLASSID, PACKAGEROOT, QUALIFYPATH FROM PSAPPCLASSDEFN WHERE PACKAGEROOT = :1";
SQLExec(&AppPackageSQL_, &sAppPackage, &PckgRoot_, &QlfyPath_);
SQLExec(&AppClassSQL_, &sAppPackage, &AppClassID_, &PckgRoot_, &QlfyPath1_);
%This.insertItemToProject(57, 104, &sAppPackage, 116, &PckgRoot_, 117, &QlfyPath_, 0, "", 0, &defn);
%This.insertItemToProject(58, 104, &sAppPackage, 105, &QlfyPath1_, 107, &AppClassID_, 0, "", 0, &defn);
/*
%This.insertItemToProject(57, 104, &sAppPackage, 116, "Z_PSTOKEN", 117, ".", 0, "", 0, &defn);
%This.insertItemToProject(58, 104, &sAppPackage, 105, "PSToken", 107, "PSTOKEN", 0, "", 0, &defn);
*/
End-If;

end-method;

method initializeProjects
Local %metadata:ProjectDefn:ProjectDefn &oCreateDefn, &oDeleteDefn;
&oCreateDefn = %This.getProject(&sCreateProject);
end-method;

/*******************************************************************************
method: getProject
purpose: Checks to see if the project already exists, if not it creates it
*******************************************************************************/
method getProject
/+ &sProjectName as String +/
/+ Returns %Metadata:ProjectDefn:ProjectDefn +/

Local %metadata:Key &key = create %metadata:Key(Key:Class_Project, &sProjectName);
Local %metadata:ProjectDefn:ProjectDefn &defn = &mgr.GetDefn(&key);

If Not (&mgr.DefnExists(&key)) Then

&defn = &mgr.CreateDefn();
&defn.ProjectName = &sProjectName;
&defn.ProjectDescr = "PPs code created project";
&defn.ReleaseDttm = "2009-04-13 15:52:19.000";
&defn.ObjectOwnerId = "PPT";
&defn.DescrLong = "This Project is created by PP's App Package to create Projects in Application Designer";
&defn.CommitLimit = 50;
If Not (&defn.SaveNewDefn()) Then
/*ToDo log error */
End-If;
Return &defn;
Else
Local %metadata:ProjectDefn:ProjectDefn &ProjectDefn = &mgr.GetPrivateDefn(&key);

Return &ProjectDefn;
End-If;
end-method;
/******************************************/

FYI

There is a case on metalink3 about %metadata
https://metalink3.oracle.com/od/faces/secure/km/DocumentDisplay.jspx?id=849911.1

Thanks!!

Yahoo Maps for PeopleSoft

2008-06-19T22:30:00.004+05:30

Its been some time since i visited my own blog...funny but true!!!.
I recently stumbled upon this new feature in FSCM 9.0 for Customers/Vendors, where one can actually view the Customers/vendors address using Yahoo Maps from PeopleSoft...Let us see how...

Yahoo resolves the address provided, you have entered these 5 fields for an address of any country which can possibly be located by Yahoo...
1. Address1
2. State
3. City
4. Country
5. Postal

Once this is done you can either place your custom hyper link or check this on Customers General Info page in FSCM 9.0...
Click on View Map and it opens a New Window of Yahoo Maps with the Customers Address...Also trying to see, if i can make use of Google maps in a similar fashion.

Use Web Services in PS without creating Service/Service Operation/Message and Routings

2008-05-09T23:39:00.005+05:30

Hi,
I just stumbled upon this very interesting App Package in FSCM 9.0 > "SCM_UTILITIES"...
Its a collection of some really amazing application classes, and over the last few days i was busy trying to use a few them...Of the couple of classes that i used, the best i found was to Integrate with external Web Services (implementing SOAP 1.1/1.2) without having the need to create
1. Service
2. Service Operation
3. Message Defn
4. Node Defn

One can actually skip creating all these definitions and still integrate with External Web Services.
The trick is to use the delivered App Class "XMLPoster"...
All that is required is...
1. URL (WSDL End Point...SOAP Address Location in WSDL...normally is same for both SOAP1.1/1.2)
2. Request SOAP Structure
3. HTML - This will store the SOAP Request (GETCOUNTRYNAME)
Use the following piece of code to test it out!!!
import SCM_UTILITIES:Integration:XMLPoster;
Local SCM_UTILITIES:Integration:XMLPoster &XMLPost_ = create SCM_UTILITIES:Integration:XMLPoster();
/* SOAPPost */
Local string &TargetUrl_ = "http://www.oorsprong.org/websamples.countryinfo/CountryInfoService.wso;
Local string &soapstr = GetHTMLText(HTML.GETCOUNTRYNAME);
Local string &SOAPResp_ = &XMLPost_.XMLPost(&TargetUrl_, &soapstr);
MessageBox(0, "", 0, 0, "&SOAPResp_ " &SOAPResp_);

!!!Happy Integrating!!!

Consume WSDL's via URL/UDDI - Tools 8.48/8.49

2008-04-22T13:43:00.006+05:30

Over the past few days i was busy setting up a UDDI Repository for PeopleSoft...When the Repository was finally setup (BEA's Aqualogic Service Registry 3.0), i was excited to consume WSDL's via UDDI...But to my amazement i was unable to consume the WSDL (http://ws.soatrader.com/easycfm.com/1.0/Captcha?WSDL) either via URL/UDDI...After further analysis...
This issue is caused by that we don't support all aspects of schema referencing. -
1. "wsdl:import" statements that point to a external schema doc -
2. "xsd:include" -
3. "xsd:import".
The import wizard will not recurse across nested import statements.

This however has been refined in Tools 8.50...Interestingly the same WSDL which errored out while i was trying to consume it through UDDI...can be successfully consumed via URL in tools 8.46.19...
Further analysis pointed it out that PeopleSoft uses different App Package(s) for WSDL in tools 8.48...
So the bottom line is - "You would not be able to import WSDL's either via URL/UDDI which reference additional import statements like the ones that have been mentioned above. "

PeopleSoft & UDDI Config

2008-04-09T23:21:00.004+05:30

With the release of PeopleTools 8.48, many of us have come across the Service Configuration page under Integration Broker. Though (Quote) PeopleSoft does not recommend any UDDI server(s) to be used (Unquote), but one can make definitely make use of the wide variety of Open Source (jUDDI) and bundled (Aqualogic Service Registry 3.0) UDDI Registeries.
I did have initial problems configuring jUDDI & BEA's Aqualogic Service Registry, but once configured, we can post the WSDL's to the UDDI respository...
If you also use JDev, then one can discover the installed UDDI Repositories and consume/provide WSDL's from JDev...Its a good option to start with BEA's Aqualogic, but it has a license limitation, the executable avialable for download from http://www.bea.com/ can have a maximum of 150 Business Services registered, and since this being a trial version I'hv another 63 days of free usage. So make sure you choose the best in the lot WSDL's to be posted to the repository...
Happy Discovery and Integration...
Stay tuned for new updates on WSRP 1.0/2.0 in PeopleSoft

PeopleSoft's Integration - ConnectorRequest

2008-03-02T11:58:00.002+05:30

One can actually skip the integration Engine to integrate with Web Services (SOAP 1.1/1.2). PS internally does use it for both Tools and Apps (SCM). A few examples - PS UDDI Config, IB Pagelet config.

Pros
1. One can avoid setting up Nodes/Routings. Routings being the major culprit in Integration Setups.
2. Also, one does not have to bother about Sync Logging..
3. Best of All, difficult for someone to identify

Cons
1. Difficult for a person to troubleshoot. But, thats exactly the reason "ConnectorRequest" has been made.

I have this code running for most of the Web Services available on XMethods and StrikeIron, and works like a Gem

PSFT_PP

2008-03-02T00:05:00.000+05:30

Hi,

!!Welcome!! to my Blog...