List of Tables in Lease Administration

Below are the list of basic records that can be used while working with Lease Administration Queries:

• PS_RE_LS
• PS_RE_LSA
• PS_RE_LSA_PPTY
• PS_RE_LSA_TRM
• PS_RE_LS_TRM_SCHED
• PS_RE_LS_INT_SCHED

Queries:

Below queries can be used to retrieve the basic information from the Lease records including Asset accounting data.

SELECT A.LS_KEY,B.AMND_NBR,A.BUSINESS_UNIT,A.LS_NBR,A.LSE_TYPE_CD,A.LEASE_NAME,A.LEASE_STATUS,A.REGION_CD,A.PRINCIPAL_ID,A.VNDR_LOC,A.LEASE_SIGNED_DT,A.LEASE_START_DT,A.LEASE_END_DT,A.LEASE_TERM,A.PRIM_PROPERTY_ID,A.CALENDAR_ID,A.IMPLICIT_RATE,A.BORROW_RATE,A.MLP,A.PV_MLP,A.ROU_ASSET,B.PROPERTY_ID,B.BUSINESS_UNIT_AM,B.ASSET_ID,B.CAP_LEASE_AMT,B.COST,B.CATEGORY,B.EST_LIFE,B.ALLOC_LS_PAYMENTS,B.ALLOC_IDC,B.UNDISCOUNTED_ROU,B.DISCOUNTED_PVLP,B.ADJUSTED_PV_MLP,B.INITIAL_DIRECT_CST,B.SUGGESTED_ALLOC,B.CURRENT_LS_PAYMENT,B.CURRENT_PV_MLP,C.ACCOUNT,C.DISTRIBUTION_TYPE,C.TRANS_TYPE,C.ACCOUNTING_DT,C.DEPTID,C.PRODUCT,C.COST_TYPE,C.APPL_JRNL_ID,C.AMOUNT,C.JOURNAL_ID,C.JOURNAL_DATE,C.JOURNAL_LINE

FROM PS_RE_LS A, PS_RE_LSA_PPTY B, PS_DIST_LN C

WHERE A.LS_KEY = B.LS_KEY

AND B.BUSINESS_UNIT_AM = C.BUSINESS_UNIT

AND B.ASSET_ID = C.ASSET_ID

SELECT DISTINCT A.LS_KEY,B.AMND_NBR,A.BUSINESS_UNIT,A.LS_NBR,A.LSE_TYPE_CD,A.LEASE_NAME,A.LEASE_STATUS,F.PRODUCT,C.PAYMENT_GROUP,A.REGION_CD,A.PRINCIPAL_ID,A.VNDR_LOC,A.LEASE_SIGNED_DT,A.LEASE_START_DT,A.LEASE_END_DT,A.LEASE_TERM,A.PRIM_PROPERTY_ID,A.CALENDAR_ID,A.IMPLICIT_RATE,A.BORROW_RATE,A.MLP,A.PV_MLP,A.ROU_ASSET,B.PROPERTY_ID,B.BUSINESS_UNIT_AM,B.ASSET_ID,B.CAP_LEASE_AMT,B.COST,B.CATEGORY,B.EST_LIFE,B.ALLOC_LS_PAYMENTS,B.ALLOC_IDC,B.UNDISCOUNTED_ROU,B.DISCOUNTED_PVLP,B.ADJUSTED_PV_MLP,B.INITIAL_DIRECT_CST,B.SUGGESTED_ALLOC,B.CURRENT_LS_PAYMENT,B.CURRENT_PV_MLP,C.SCHEDULE,(A.PV_MLP+E.INTEREST_AMOUNT)-SUM(D.OBLIGATION_REDUCE)

FROM PS_RE_LS A, PS_RE_LSA_PPTY B,PS_RE_LSA_TRM C,PS_RE_LS_TRM_SCHED D,(SELECT DISTINCT AA.LS_KEY,C.INTEREST_AMOUNT,C.TERM_ID FROM PS_RE_LS AA,PS_RE_LS_TRM_SCHED C WHERE AA.LS_KEY = C.LS_KEY AND C.START_DT = AA.LEASE_START_DT) AS E,PS_ASSET_ACQ_DET F

where A.LS_KEY = B.LS_KEY

  AND B.LS_KEY = C.LS_KEY

  AND C.LS_KEY = D.LS_KEY

  AND D.LS_KEY = E.LS_KEY

  AND B.BUSINESS_UNIT_AM = F.BUSINESS_UNIT

  AND B.ASSET_ID = F.ASSET_ID

  AND C.TERM_ID = E.TERM_ID

  AND  D.START_DT <= (SELECT MAX(B.END_DT) FROM  PS_RE_LS_TRM_SCHED B WHERE B.LS_KEY = D.LS_KEY  AND  (GETDATE()) BETWEEN B.START_DT AND B.END_DT)

  AND B.AMND_NBR = (SELECT MAX(B_E.AMND_NBR) FROM PS_RE_LSA_PPTY B_E WHERE B.LS_KEY = B_E.LS_KEY)

GROUP BY A.LS_KEY,B.AMND_NBR,A.BUSINESS_UNIT,A.LS_NBR,A.LSE_TYPE_CD,A.LEASE_NAME,A.LEASE_STATUS,F.PRODUCT,C.PAYMENT_GROUP,A.REGION_CD,A.PRINCIPAL_ID,A.VNDR_LOC,A.LEASE_SIGNED_DT,A.LEASE_START_DT,A.LEASE_END_DT,A.LEASE_TERM,A.PRIM_PROPERTY_ID,A.CALENDAR_ID,A.IMPLICIT_RATE,A.BORROW_RATE,A.MLP,A.PV_MLP,A.ROU_ASSET,B.PROPERTY_ID,B.BUSINESS_UNIT_AM,B.ASSET_ID,B.CAP_LEASE_AMT,B.COST,B.CATEGORY,B.EST_LIFE,B.ALLOC_LS_PAYMENTS,B.ALLOC_IDC,B.UNDISCOUNTED_ROU,B.DISCOUNTED_PVLP,B.ADJUSTED_PV_MLP,B.INITIAL_DIRECT_CST,B.SUGGESTED_ALLOC,B.CURRENT_LS_PAYMENT,B.CURRENT_PV_MLP,C.SCHEDULE,(A.PV_MLP+E.INTEREST_AMOUNT) --This query is for future pending payment

 

List of Tables in PeopleSoft Financials

Below are the list of tables used in PeopleSoft Financial modules

1. Accounts Payable

• PS_VOUCHER
• PS_VOUCHER_LINE
• PS_DISTRIB_LINE
• PS_PYMNT_VCHR_XREF
• PS_PAYMENT_TBL
• PS_VCHR_ACCTG_LINE
• PS_PYMNT_ADVICE
• PS_VCHR_PPAY_XREF

2. Asset Management

• PS_INTFC_FIN
• PS_INTFC_PHY_A
• PS_ASSET
• PS_BOOK
• PS_COST
• PS_DEPRECIATION
• PS_BOOK_HIST
• PS_DIST_LN
• PS_ASSET_ACQ_DETAIL
• PS_ASSET_LOCATION
• PS_OPEN_TRANS
• PS_DEPR_RPT
• PS_ASSET_NBV_TBL
• PS_RETIREMENT

3. General Ledger

• PS_JRNL_LN
• PS_JRNL_HEADER
• PS_LEDGER

4. Purchase Order

• PS_PO_HDR
• PS_PO_LINE
• PS_PO_LINE_DISTRIB
• PS_PO_LINE_SHIP

     Purchase Order Staging Tables:

• PS_PO_HDR_STG
• PS_PO_ITM_STG
• PS_PO_DISTRIB_STG

5. Receipts

• PS_RECV_HDR
• PS_RECV_LN_SHIP
• PS_RECV_LN_ACCTG

6. Vendors

• PS_VENDOR
• PS_VENDOR_ADDR
• PS_VENDOR_LOC
• PS_VENDOR_PAY
• PS_VENDOR_INVOICE
• PS_VENDOR_CNTCT
• PS_VNDR_BANK_ACCT
• PS_VNDR_IBANK_ACCT
• PS_VNDR_WTHD_JUR

7. Accounts Receivables

• PS_ITEM
• PS_ITEM_ACTIVITY
• PS_ITEM_DST
• PS_CUST_DATA
• PS_CUST_AGING
• PS_CUSTOMER

8. Billing

• PS_BI_HDR
• PS_BI_LN
• PS_BI_ACCT_ENTRY

Record Definitions in PeopleTools

A Record in PeopleSoft is a collection of Fields and which is similar to a table in Database terms. But in PeopleSoft a Records is categorized into 7 types. 

Before explaining the Record type in PeopleSoft will show you the structure of how a record looks like:

As shown below those are the 7 Record type exists in PeopleSoft

 

Record Types:

1. SQL Table: This definition will have a Physical SQL Table in the database.

Build Option: Yes (Create Tables/Alter Tables)

For New tables: use Create Tables in Build Options

For existing Table: use Alter Tables in the Build Options

2. SQL View: This record definition corresponds to a SQL view, which is not a physical SQL table in the database but fields from one or more SQL tables that are reorganized into a different sequence. SQL view provides an alternate view of information that is stored in tables.

If you select SQL View, please enter a SQL select statement on the Right side "Click to Open SQL Editor".

Build Option: Yes (Create Views)

3. Dynamic View: A record definition that can be used like a view in pages and PeopleCode, but is not actually stored as a SQL view in the database. Instead, the system uses the view text as a base for the SQL Select that is performed at runtime. Dynamic views can provide superior performance in some situations, such as search records and in PeopleCode Selects, because they are optimized more efficiently than normal SQL views.

Build Option: No

4. Derived/Work Record: The record definition as a temporary workspace to use during online page processing. A derived or work record is not stored in the database, so you do not build it.

Build Option: No

5. SubRecord:  Group of fields that is commonly used in multiple record definitions and that you can add to other record definitions. This way, you can change a group of fields in one place, as opposed to changing each record definition in which the group of fields is used.

Build Option: No

6. Query View: This record definition as a view is constructed using the PeopleSoft Query tool. Before you can create the view, PeopleSoft Application Designer prompts you to save the definition.

Build Option: No

7. Temporary Tables: Temporary record is the database record for temporary purpose. It is used to store the data specific to the business criteria for update without risking the main table.

Build Option: Yes

Non Standard SQL Table Name: This option is used for overriding the general naming convention of peopletools i.e. PS_*. You can give any name you like:P

Build Sequence No. : This field is available when the record type is a SQL or Query view. Set the order in which the view is to be created. The default value is 1 when the record or view is initially created. Views that must be created first can be set to 0, while views that you want created last can be set to any number from 1 to 99. The build sequence number is stored with the other details of the record or view in the database.

Application Engine

Application Engine Programs are the Batch program which are developed in Application Designer. This can be developed using PeopleCode and SQL. Application Engine programs can be used in Data Loading, Component Interfaces, Application Classes, Report Generation, Calling SQR and COBOL.

Reuse Business Logic - Application Engine programs can now invoke PeopleCode. This means that from Application Engine you can call common PeopleCode functions that you use throughout your PeopleSoft system. Conversely, PeopleCode can now invoke an Application Engine program.

Built-In Restart Logic - Within each Application Engine program, you must define how 

frequently your program will issue a COMMIT. After doing so, each COMMIT becomes a 

"checkpoint" that Application Engine uses to locate where within a program to restart after 

an abend.

This type of built-in logic does not exist in COBOL or SQR. You need to code any restart 

logic for your programs yourself.  

Meta-SQL Support - Application Engine supports PeopleSoft meta-SQL. This language is designed to replace RDBMS-specific SQL syntax with a standard syntax, called meta-strings.

AE Structure

Application Engine Programs are Hierarchy based which consists of Sections, Steps and Actions. Each Section will have Steps and each Step will have Actions. 

Program Flow of Actions - A Program Flow Action contains a SQL SELECT statement designed to return results on which subsequent Actions depend.

There are the four types of Program Flow Actions:

1. DoWhen
2. DoWhile
3. DoUntil
4. DoSelect

SQL Action - Contains a single SQL statement that performs either a SELECT, INSERT,  

UPDATE or DELETE.

PeopleCode Action - This action is where we can write all the Peoplecode required. This 

action provides access to the PeopleSoft Internet Architecture's integration technologies 

such as Application Messaging, Business Interlinks, Component Interfaces, and XML File 

Processing.

Call Section - This is an Action that calls another Section. The "called" Section can be in 

the same program as the calling Section, or it can be in an external program.

Log Message - A Log Message Action can be used to write a message to the 

MESSAGE_LOG based on a particular condition in your program. Could help in debugging 

or in providing multi-language capability.

Rules

1. The actions SQL and Call Section cannot be part of the same Step.

2. Cannot have the same Action type twice in a single step.

3. Due to the above reasons, the maximum number of actions within a step can be 7 

    with the following execution sequence

Type of Application Engine

There several different types of application engine programs:

• Standard, which is for normal program.
• Upgrade Only, which is used in PeopleSoft upgrade utilities.
• Import Only, which is used by PeopleSoft import utilities.
• Daemon Only, a type of program used as a daemon process.
• Transform Only, a program type used to support Extensible Stylesheet Language Transformations (XSLT).

Each Application Engine will have State Record and Temporary Table included under the Properties which are important for every program.

State Record

A state record is a PeopleSoft record object that contains one or more fields used by the program to pass values between steps and actions. This State Record can be a Work Record or a SQL table which is based on the requirement of your program. If you want your program to be a restartable program then you should select SQL table as the record type and must be keyed by process instance.

• Must be saved with _AET
• Should be keyed by Process_Instance
• Either be a Work Record or SQL table based on the program logic 
• To refer a variable, use %BIND meta SQL

Temporary Tables

These are also PeopleSoft Record objects which are used to stored the data temporarily.  These tables are used to improve the performance by processing sets of data during a process run.

• Must be saved with _TMP or _TAO
• Multiple instances can be derived for a table
• Mostly used in Row by row processing. 

Restartable AE

By default, Application Engine doesn't perform a COMMIT until an entire program successfully completes. It's up to program developers to set any individual Commits where appropriate.

• At the Section level, you can choose to Commit after each Step in that Section.
• At the Step level, you can require or defer COMMITs for individual Steps, or you can increase the COMMIT frequency within a Step to n iterations of a looping action within a Step, such as a Do Select or Do While.
• PS_AERUNCONTROL - record where last commit point stored. Keyed by PROCESS_INSTANCE

Trace Parameters

Application Engine Trace

TRACEAE = 255. Traces every SQL in the App Engine and shows the buffer values.

TRACEAE = 128. Only shows the timing codes for each statement. Useful for checking performance.

Peoplecode Trace

          TRACEPC = 108. This option is useful when setting trace for all Peoplecode in the Application Engine.

Using Process Scheduler

          Append in Parameter List -TRACE 255 -TOOLSTRACEPC 108

Tableset Sharing

Tableset Sharing

Use tableset sharing to filter prompt table choices based on business rules. In PeopleSoft applications, a large percentage of the tables are keyed with one of two fields:

• Business Unit is the key field for transaction tables.
  
• SetID is the key field for control tables. 

In tableset sharing, a single table can offer different values based on underlying business rules. The prompt table values will have SetID as a high-level key. That key is used to direct data to the appropriate business unit. 

PeopleSoft Pure Internet Architecture

Elements in PIA

Browser:  An application that provides a user a way to view and interact with information on the internet.

Web server: A server program that presents HTML files that form web pages of a PeopleSoft application.

Application server: A server program that provides the business logic for the PeopleSoft application.

Database server: A server program that collects and organizes information so that you can access and update it. 

In PeopleSoft Pure Internet Architecture, no PeopleSoft executables are on the client. The client can be an internet device that uses standard internet technologies, such as HTTP, HTML, and XML, to communicate with the PeopleSoft application. 

Web Server

When a client connects to the server, the system displays a sign-in window, sent to the browser in HTML. Web services software manages communication with the browser.

Web services software displays HTML, but the HTML has to exist first. The database server has definitions, not HTML (usually), which are stored in tables. You must have one more piece to complete PeopleSoft Pure Internet Architecture . 

Application Server

The application server then completes the connection, managing communication with the database and deliver HTML to the web server for display.

Flow of Data in PeopleSoft Pure Internet Architecture

When a user navigates through a PeopleSoft application using a browser, a number of things happen. For example a request for a page of data about a student, Mr. X, goes through these architecture elements to the browser:

PeopleTools tables.

All the raw data that is required to assemble the student data component is contained in PeopleTools tables. This includes definitions of the component, page, fields, navigation, and other elements. This raw data goes to the application server for assembly as HTML.

Application data tables.

After the request is made, the information about Mr. X is retrieved and sent to

the application server.

PeopleSoft services.

Dozens of individual processes are running. One (PSAPPSRV) takes the PeopleTools data and application data about Mr. X and assembles it into HTML for display. 

BEA Tuxedo and Jolt.

BEA Tuxedo manages the multiple connections to the database. Jolt is a Java-based communication protocol. BEA Jolt and Tuxedo send the assembled HTML to the web server.

Java servlets.

Individual programs manage the state of each user session, including a buffer that allows the use of the browser Back button. Servlets pass the HTML to web services for display. 

PeopleSoft Database

PeopleSoft Databases Tables

Tables in PeopleSoft databases comes under three Categories: 

1. System Catalog Tables
2. PeopleTools Tables
3. Application Data Tables

System Catalog Tables

These tables record information about the database and its contents. The type of information that is stored in these tables includes table names, column names, and index names and contains database metadata. These tables:

• Track all objects in the entire database.
  
• Are analogous to a table of contents.
  
• Vary based on platform. 
  

Ex:

DBA_OBJECTS, DBA_INDEXES in Oracle DB

SYSIBM.SYSTABLES, SYSIBM.SYSINDEXES in DB2

sysobjects,sysindexes in SQL Server

PeopleTools Tables

These tables record information about the definitions that make up a PeopleSoft application. The type of information that is stored in these tables includes page names, record names, and component names. 

PeopleTools tables contain metadata about the PeopleSoft application that is installed on the database. PeopleTools tables:

• Are structured consistently between applications.
• Can be modified by PeopleTools such as PeopleSoft Application Designer.
• Use the naming convention PS*; for example, PSRECDEFN.  

Application Data Tables

These tables store data that is directly pertinent to an individual company. The type of information that is stored in these tables includes ledger names, inventory items, employee names, and payroll data. 

• Application data tables hold the data that users enter into the database.
• Specific tables are determined by the application. Developers can create more of these tables.
• Content is modified through the user interface.
• Tables use the naming convention PS_*; for example, PS_VOUCHER.