Creating and Configuring the Application Server Domain

Step 1: Configure Application Server Domain

To configure application server domain, first make sure the database FINDEMO is up and running.
Once Database is started, follow below steps:

1. Open the MS DOS Command Prompt, and change the directory to C:\PS_HOME\appserv by executing the command cd C:\PS_HOME\appserv.
2. Execute the command set PS_HOME=C:\PS_HOME and  set PS_CFG_HOME=C:\app to set these two environment variables. There shouldn’t be any space around ‘=’
3. Execute the command psadmin
4. Press 1 and hit enter to configure Application Server
5. Press 2 and hit enter to create domain then it asks for the domain name enter your database name FINDEMO(though its not necessary to enter database name you can enter any name, but makes sense when application domain name and database name both have same names.
6. In the next step, it will ask to select the configuration template. Though you can select any one of them but for now select the fist one – Developer
7. The next dialog will ask for confirmation, press ‘y’ and enter and this is what you see next:
8.  
9. Now we have to change the UserID and UserPswd which appears at 18th and 19th number respectively however they might also appear at 17th and 18th number.
10. Press 18 and hit enter to change the UserID. Enter the User ID as PS in next prompt and press enter.
11. Press 19 and hit enter to change the UserPswd. Enter the User Password as PS in next prompt and press enter.
12. We need to enable WSL, Press 6 and hit enter upon doing so the the option will change from No to Yes.
13. Press 13 and hit enter to load the configuration. It will configure the app server domain once done the next screen will ask to boot the domain.
14. Press 1 and hit enter to boot the domain, in the next screen choose 1 for serial boot for now and hit enter.

Your app server will start running now.

Step 2: Testing the Three-Tier Connection

Now that we have created an application server domain FINDEMO to connect to our demo database FINDEMO (Both have same name) in 3 tier mode, we need to verify this connectivity through app designer.

Before we begin doing so, we need to get the Host Name and WSL port on which Application Server Domain FINDEMO will listen the connection requests. In our case as you can see in the above screenshot, the WSL Port (at number 25) is7000. To find out what will be host name, just start the application server domain and once started see the host name against WSL port 7000 at the bottom:

 So the Host Name or Machine name is WIN-5TI0P2TA3PE.

Launch the configuration manager pscfg from C:\PS_HOME\bin\client\winx86. Create a desktop shortcut so that you don’t have to go to this directory again and again:
1. Select the Profile Tab. Highlight Default and select Edit.
2. On the Edit Profile dialog box, select Application Server as the Connection Type.
3. Enter values for these parameters:

• Application Server Name – FINDEMO
• Machine Name or IP Address – WIN-5TI0P2TA3PE
• Port Number (WSL) – 7000
• Domain Connection Password – PS
• Domain Connection Password (confirm) – PS

5. Click on Set to add the definition to the list and then click on OK to close the dialog box.
6. On the Configuration Manager dialog box, select the Startup tab.
7. Select Application Server from the Database Type list. Your application server name should be displayed.
8. Enter the values for User ID as PS, Connect ID as people, and password as peop1e.
9. Click OK.

New Launch the app designer and login with userid/password as PS/PS.

Setting Up PeopleSoft Pure Internet Architecture (PIA)

Step 1: Installing the PIA

The location where you install the PeopleSoft Pure Internet Architecture is referred to in this documentation
as PIA_HOME. To install the PeopleSoft Pure Internet Architecture on Oracle WebLogic:

1. Go to \setup\PsMpPIAInstall and run setup.bat.
   Click Next on the Welcome to the InstallAnywhere Wizard for PeopleSoft Internet Architecture window.
2. Enter the location where you want to install the PeopleSoft Pure Internet Architecture, referred to in this documentation as PIA_HOME which should be same as PS_CFG_HOME i.e C:\app.
3. Select Oracle WebLogic Server and click Next.
4. Specify the root directory where Oracle WebLogic is installed, WLS_HOME i.e C:\app\WLS1034, and   click Next.
5. Enter the administrator login ID and password for the new domain to be created.
6. The password must be at least 8 alphanumeric characters with at least one number or special character. The default login ID is system, and the default password is Passw0rd (with a capital “P” and zero rather than the letter “o”). It is good practice to change to a password other than the default. Click Next.
7. Enter the Weblogic Domain HRDEMO and click on next.
8. Select the type of domain to Create Single Server
9. Enter a PeopleSoft web site name; the default is ps so don’t change it.
10. Specify your Application Server Machine Name (WIN-5TI0P2TA3PE), its JSL (Jolt Station Listener) port number (9000), its HTTP (80) and HTTPS (443).
11. Enter the web profile DEV, User ID as PS and password as PS then click on next.
12. Keep the default selection in next step and click on next.
13. Take the screenshot of next window and click on Install.

Step 2: Starting the PIA

1. Go to \webserv\HRDEMO\bin i.e C:\app\webserv\HRDEMO\bin
2. Run startPIA.cmd, wait until the PIA is started and server comes in RUNNING mode.
   

Step 3: Login into PIA

1. Open the Internet Explorer and type following address in address bar http://localhost/ps/signon.html
2. Enter the User ID as PS and Password as PS. You will be logged into PIA.

Step 3: Setup Process Scheduler

Parallel Processing using AE

Implementing Parallel Processing using Temporary Record with Application Engine

Step :1 Open the App Designer

Step :2 Create three record definitions BN_EMPLID_WRK, BN_EMPLID1_WRK and BN_JOB_WRK of type “Temporary Table”

Step 3: Create a record BN_AGE50_AET of type “Derived/Work”

Step 4: Create a new Application Engine program “BN_UPD_PRIM” in app designer.
Step 5: Open the Application engine properties, go to “Temp Tables” tab and assign all three temp tables. Give the instance count as 10.

Step 6: Likewise, go to “State Records” tab and add the record BN_AGE50_AET.

Step 7: Build the temporary records BN_EMPLID_WRK, BN_EMPLID1_WRK and BN_JOB_WRK. App designer will create the number of instances for each of these temporary tables equal to given in the Program Properties -> Temp Tab – Instance count box which is “10” in our case which means the tables for each temp record will be created as below:

BN_EMPLID_WRK –  BN_EMPLID_WRK1, BN_EMPLID_WRK2… up to 10 instances
BN_EMPLID1_WRK – BN_EMPLID1_WRK1, BN_EMPLID1_WRK2…. up to 10 instances
BN_JOB_WRK – BN_JOB_WRK1, BN_JOB_WRK2…. up to 10 instances

Step 8:  In the MAIN section of app engine (Or create a new section) add a Step/Action of SQL type, then add below SQL in that.

This SQL will load all the employees into temp table instances BN_EMPLID_WRK1, BN_EMPLID_WRK2… etc, Please make sure that you are using the meta sql %Table with the temp table name.
Lets say we have 100,000 employees in PS_PERSON record and each employee has just one assignment in PS_PER_ORG_ASGN. Since we have instance count for the temp record to 10 so when the program is run, The selected 100000 rows will be divided into like 100000/10 = 10000 and each chunk of 10000 rows will be inserted into one of the available instances BN_EMPLID_WRK1, BN_EMPLID_WRK2 …etc.

Please note that we don’t have to worry about which instance a particular chunk is being inserted into instead, %Tablemetasql does that for us. However, Its not necessary that application engine will process the rows in exactly same way explained above, its just a logical explanation which is trying to convey that the number of selected rows i.e 100,000 will be divided and loaded into appropriate temp table instances to be processed simultaneously which is nothing but parallel processing.
Once above step is processed, the temp table BN_EMPLID_WRK has been loaded with large volume of data (1000,000 rows) of employees eligible to be processed. How many rows have been loaded in which temp table instance (BN_EMPLID_WRK1, BN_EMPLID_WRK2… etc) is none of our concerns.

Step 9: Add below Step/Action to update the statistics of temp table BN_EMPLID_WRK for the indexes to improve the performance when the rows are selected from the instances of this table in further steps.

Step 10: Add below Step/Action to get the JOB data (EFFDT, EFFSEQ and EMPL_STATUS) for each employee loaded in BN_EMPLID_WRK, and insert into another temp table BN_JOB_WRK (BN_JOB_WRK1, BN_JOB_WRK2 … upto 10 instances).

Here again as you notice, we just used %Table metasql with the table BN_JOB_WRK in the SQL which will take care as to how should be the allocation of rows for the instances BN_JOB_WRK1, BN_JOB_WRK2…etc.

Step 11:  Add below Step/Action to update the statistics of temp table BN_JOB_WRK for the indexes to improve the performance when the rows are selected from the instances of this table in further steps.

 

Step 12: Add below Step/Action to select those employees loaded in the temp table BN_EMPLID_WRK who are all don’t have more than one assignments (EMPL_RCD) for a benefit record number (BENEFIT_RCD_NBR), and insert into another temp table BN_EMPLID1_WRK (BN_EMPLID1_WRK1, BN_EMPLID1_WRK2…upto 10 instances).

 

Step 13: Add below Step/Action to update the statistics of temp table BN_EMPLID1_WRK for the indexes to improve the performance when the rows are selected from the instances of this table in further steps.

 

Step 14: Finally, mark the current EMPL_RCD as “Primary Job” in the  table PS_PRIMARY_JOBS for the processed employees by joining all three temp tables.

 

Well, it’s quite evident that the procedure to implement parallel processing appears simpler than we thought. However, if we try to compare it with the non-parallel way of addressing the same request then it would certainly help us understand the concept clearly.

Let’s discuss how would it have been addressed if there was no mechanism called “Parallel Processing”.
1) Record Definition – The number of records will be same but the record type for BN_EMPLID_WRK, BN_EMPLID1_WRK and BN_JOB_WRK will be “Table”.
2) Since there is no temporary record created hence no need to assign the temp records in Program Properties -> Temp Tables. It won’t be applicable.
3) No Need to use %Table metasql while referring to the records.

Everything else will remain exactly same. We also need to understand how different it will behave without parallel processing.
In this case, When we run the program, The system will  store all the 100,000 rows into the table BN_EMPLID_WRK during the execution of Step 1 explained earlier unlike, into various temp table instances BN_EMPLID_WRK1, BN_EMPLID_WRK2..etc. So, when execution goes further then all the 100,000 rows are fetched from one table and processed but in case of parallel processing the chunk of rows i.e 10000 if fetched from each temp table instance and processed all simultaneously.
Hence, If time taken to complete the processing is 20 Mins then it will be 20/10 ~ 2 to 5 Mins if parallel processing is used.

Optional Prompting in PS Query

Sample SQL

SELECT A.BUSINESS_UNIT, A.VOUCHER_ID, A.VOUCHER_STYLE, 
A.INVOICE_ID, TO_CHAR(A.INVOICE_DT,'YYYY-MM-DD'), 
A.VENDOR_SETID, A.VENDOR_ID, A.VNDR_LOC
FROM PS_VOUCHER A, PS_VOUCHER_LINE B
WHERE ( A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.VOUCHER_ID = B.VOUCHER_ID
AND ( A.BUSINESS_UNIT = :1
OR ' ' = :1) )

Installing PeopleSoft 9.2 with PeopleTools 8.54

PeopleSoft 9.2 Installation Videos

How to Send an HTML Email Using MCF Class

Sample Program

This PeopleCode sends out an HTML email similar to the one we saw in the SendMail example.

Firstly you need to import the MCFOutboundEmail class and instantiate it. Then use the delivered properties to assign the sender, recipients etc.
You can then build an HTML text and assign it to the Text property. Finally use the Send() method to send the email.

import PT_MCF_MAIL:MCFOutboundEmail;

Local PT_MCF_MAIL:MCFOutboundEmail &oEmail = create PT_MCF_MAIL:MCFOutboundEmail();

&oEmail.From = %EmailAddress;
&oEmail.Recipients = “harish.mahanta@gmail.com”;

/* Building the HTML text */
&sNL = “<br>”;
&sGreetLine = “<font face =’Arial’>Dear ” | %OperatorId | “,” | &sNL | &sNL;

&sMailLine1 = “This is a test email that contains an HTML table.” | &sNL | &sNL;
&sMailLine2 = “<table border=’1′><tr bgcolor = ‘#AAAA00’ ><th>Name</th><th>State</th><th>Country</th></tr><tr><td>Rojer Alex</td><td>VA</td><td>USA</td></tr></table>” | &sNL;

&sFooterText1 = “Your footer line(s) goes here.” | &sNL | &sNL;
&sFooterText2 = “Regards,” | &sNL | “Harish”;
&sFooter = &sFooterText1 | &sFooterText2 | “</font>”;

&sEmailBody = &sGreetLine | &sMailLine1 | &sMailLine2 | &sFooter;
&oEmail.Subject = “Test Email”;
&oEmail.Text = &sEmailBody;

/* Setting the Content type */
&oEmail.ContentType = “text/html; charset=US-ASCII”;

/* Sending the email */
&nStatus = &oEmail.Send();

Error Checking

The return value from the Send() method can provide some insight into what has happened during the send operation. The below shows all the return values.

Return Value	Description
-1	Email was sent but whether it was successful or not is not known.
0	Email failed before being sent.
1	Email was delivered.
2	Email delivery not attempted.
3	Email has only been partially delivered. Only some of the addresses in the list of addresses were delivered to.

You can evaluate the return value to show the user a message – a basic one is shown below.

Evaluate &nStatus
When 1
   WinMessage("Email successfully sent.", 0);
   Break;
When-Other
   WinMessage("Error(s) Encountered.", 0);
   Break;
End-Evaluate;

PeopleSoft Integration Broker

Architecture which shows the elements of Integration:-

This architecture clearly depicts the integration between PeopleSoft Finance and PeopleSoft HR system represented by Node A and Node B respectively where the changes made in former would be transmitted to latter through Integration Broker.

Lets assume that the integration is setup for the Bank Account changes done on Finance System which should ultimately be transmitted to HR System to keep both in sync. Now our rest of the discussion would be based on this scenario.

The very first thing we must talk about is the various elements involved in this Integration Setup.

Elements of Integration

Integration Engine

The integration engine as you can see in the architecture above, is something that ties all the elements like Service Operation, Message, Routing etc… together which ultimately creates a mechanism for integrating two systems.

The integration engine runs on the PeopleSoft application server. Rather than communicating directly with other applications, the integration engine sends and receives messages through one or more separately installed integration gateways.

The integration engine:

• Uses a modular architecture, so it can treat gateways as black boxes and communicate with them using standard connectors.
• Handles messages containing data in a variety of formats. Formats include the PeopleSoft rowset-based message format, and nonrowset-based message structures including, XML document object model messages, Simple Object Access Protocol (SOAP) messages, and non-XML files.
• Sends and receives messages asynchronously (like email) or synchronously (suspending activity to wait for a response).
• Applies message transmission type and routing based on specifications that you define in a PeopleSoft Pure Internet Architecture component.

Gateway

The integration gateway is a platform that manages the receipt and delivery of messages passed among systems through PeopleSoft Integration Broker. It supports the leading TCP/IP application protocols used in the marketplace today and provides extensible interfaces to develop new connectors for communication with legacy, enterprise resource planning, and internet-based systems.

Additional features include:

• Listening connectors and target connectors that transport messages between integration participants and the integration engine.
• Basic logging information concerning message receipt, delivery, and errors.
• Connection persistence with continuous open feeds to external systems through connectors, with full failover capabilities.
• Transport protocol and message format management so that when messages reach the integration engine, they have a PeopleSoft-compatible message format.

Connectors

Listening Connectors

Listening connectors receive requests from integration participants, send them to the gateway manager, and deliver responses back to the integration participants.

Target Connectors

Target connectors generate requests, send them to integration participants, wait for responses from participants, and deliver the responses back to the gateway manager.

Message

Message definitions provide the physical description of the data that is being sent, including fields, field types, and field lengths. You create message definitions in the PeopleSoft Internet Architecture. In other words Messages are shapes that describe the contents of a service operation transaction.

In our scenario the message definition would contain the information about the table structure used in the component ‘Bank Account’ i.e the records used in the component and their hierarchy. This information will be used by Integration Engine to transmit the data when there is a change in employee’s bank accounts.

Message Type	When to Use	Details
Rowset-based messages	All PeopleSoft-to-PeopleSoft integrations	For hierarchical data that is based on PeopleSoft records, you create a message definition by assembling records, organizing them into a hierarchy, and selecting fields from those records to include in the message.
Nonrowset-based messages	Integrations with third-party systems	These messages can have virtually any structure and content. You create a message definition, but you do not insert any records. The message definition serves as a placeholder for the actual message.

Handler

It contains the processing logic for finally writing the data into tables/components of the receiving node. It is combination of Application Package, Class, Method created in the application designer.

In our scenario the handler defined on HR Node would contain the business logic written in PeopleCode to write the Bank Account changes received from Finance Node, to the corresponding tables/components.

Routing

A routing definition specifies the direction of the integration (inbound or outbound), routing alias names, transformations, and more.

In our scenario, the routing on HR Node would be Inbound and on Finance Node it would be Outbound.

Service Operations

This element forms for overall processing logic by combing following elements:

• Message
• Handler
• Routing

Node

Nodes represent any organization, application or system that will play a part in integration.

In our scenario there are two nodes:

1. One that represents PeopleSoft FIN System
2. Another that represents PeopleSoft HR System

Because an application can send messages to itself, a default local node definition that represents the application is delivered as part of the integration engine.

Each PeopleSoft installation must have one, and only one, default local node.

Each PeopleSoft Integration Broker database involved in an integration must contain a default local node definition for itself, and a remote node definition for each of the other nodes involved.

Local and remote nodes are concepts relative to the database in which the nodes are defined. If you’re signed on to Database A which has Node A defined, then Node A is local. If you’re signed on to Database B, Node A is defined as remote.

In our scenario if we are in the Node A – PeopleSoft FIN System then:

• Node A (default local)
• Node B (remote)

The following definitions must exist in the Node B – PeopleSoft HR Systen for it to integrate with Node A:

• NODE A (remote)
• NODE B (default local)

In practice, only portals use nodes designated simply as Local. The only local node definition used by PeopleSoft Integration Broker is the one designated Default Local, which represents the database onto which you are signed.

What is an Unpost Sequence?

This field is present in many Voucher related tables, requisition tables, VAT, Project costing, receiving and journal related tables.

One must be careful when making queries with these tables that unpost seq must be join criteria. For example, During a join of journal header and journal line tables we must also include the unpost sequence criteria apart from having the business_unit , journal_id, journal_line and journal dates.

How is it created?

The system creates reversing entries and archives the original entries. The UNPOST_SEQ for the archived entry is one higher than for the reversal entry. Nothing is deleted.

Therefore, Unpost Seq just equal to the number of times that the Event Generator has been run. For Standard entries, the value will always be 0.

PeopleSoft Procure to Pay Tables

PeopleSoft Procure to Pay links Purchasing and Payables modules to provide control and visibility over the entire life-cycle of a transaction – right from the way an item is ordered to the way that the final invoice is processed.

If you are starting out as a Procure to Pay consultant, this list of tables will be a quick reference for you. The main application tables used in the Procure to Pay cycle are listed here.

Master data tables

Module	Table Name	Description
AP	VENDOR	Vendor Header
PO, IN	ITEM	Items
GL	GL_ACCOUNT_TBL	Accounts
GL	DEPT_TBL	Departments
GL	PRODUCT_TBL	Products
GL	PROJECT_FS	Projects
GL	OPER_UNIT_TBL	Operating Units
PO	MASTER_ITEM_TBL	Item Master Header
PO, IN	INV_ITEMS	Inventory Item master
IN	STOR_LOC_INV	Storage Location Definition

Transaction tables

Module	Table Name	Description
AP	VOUCHER	AP Voucher Header
AP	DISTRIB_LINE	Voucher Distribution Lines
AP	PAYMENT_TBL	AP Disbursements
AP	PYMNT_ADVICE	AP payment Advice Information
AP	VENDOR	Vendor Header
AP	BUS_UNIT_TBL_AP	AP Business Unit table
AM	ASSET	Asset general Information
AM	ASSET_ACQ_DET	Asset Acquisition Detail
AM	DEPRECIATION	Depreciation transactions
AM	COST	Asset Cost Transactions
PO	REQ_HDR	Requisition header
PO	REQ_LN	Requisition line
PO	PO_HDR	Purchase order header
PO	PO_LINE	Purchase order line
AR	ITEM	Customer Items
GL	JRNL_HEADER	Journal Headers
GL	JRNL_LN	Journal Line Data
IN	CM_ONHAND_VW	On hand quantity
IN	CM_INTRANSIT	Inventory in transit
IN	TRANSACTION_INV	Inventory Transactions
IN	IN_DEMAND	Order Request
IN	PHYSICAL_INV	Physical Inventory

Accounting tables

Module	Table Name	Description
AP	VCHR_ACCTG_LINE	AP Accounting entries
AM	DIST_LN	AM Accounting entries
GL	LEDGER	Ledger Data
AR	ITEM_DST	AR Accounting entries
PO	PO_LINE_DISTRIB	POline accounting entries
IN	CM_ACCTG_LINE	INV Accounting Distribution