Understanding Peoplesoft Trace

--------------------------------------------------------------------------------




Here are the steps to generate application and Application Engine traces, to troubleshoot any application issues.
Before you start generating traces, you need to make sure that the following parameters are set in the Configuration Files.

TracePCMask=4095
TraceSqlMask=12319

Once you have generated the traces, if you are working with Global Support Center, you can send the zipped trace file to the analyst working your case.

1) Trace for PIA Session -

(i) Generate the application trace for a single user

When logging on to PIA/browser, append &trace=y to the regular URL on the address bar:

Check the following options on the login window:
Under SQL trace Settings
-SQL Statements
-SQL Statement Variables

Under Peoplecode trace settings
-Program starts
-Each Statement

Now log in to the application and reach the page/menu where the problem lies and go and delete the trace that is created thus far(for location of trace see below). This will remove all the non relevant peopletools part for this application troubleshooting. Now, perform exact steps to simulate the error/problem.

A log file will be created on the application server, logs directory(\appserv\\LOGS).
The name of the file usually has the user name used to log in to the application session
followed by the machine name or IPAddress. (Eg:PS_AVARSHNE090.tracesql) with an extension of tracesql or trc.

*********TRACE OPTIONS FOR PERFORMANCE*********************
Under SQL trace Settings
� SQL Trace:
� SQL statement
� SQL statement variables.
� Connect, disconnect, rollback and commit.
� Row Fetch.
� All Other API calls except Bind variables.

Under Peoplecode trace settings
-Program starts

*********TRACE OPTIONS FOR PERFORMANCE*********************

(ii) Generate the trace for all users using the Application.
Trace setting in Application Configuration files, set following parameters for SQL and PeopleCode, respectively
TraceSQL=3
TracePC=2048
Once this is done, you need to bounce the application Server for the values to come in-effect

2) Application Engine traces
The normal application trace (*.tracesql) will not contain the Application Engine Program traces. For that AE Trace parameters (TraceAE, TracePC and TraceSQL) are to be set differently. You can set them either in the configurations files or from PIA.

(i) PIA: This ensures that the Traces will be generated only for specific AE program.
Navigation:
CRM 8.0 -Home > PeopleTools > Process Scheduler Manager > Use > Process Definitions
CRM 8.4, 8.8, 8.9 -PeopleTools> Process Scheduler> Processes
And Pick up the Process you want to Trace like RB_SRCH_BLD
Go to the Override Tab, Set Override Options in Parameter List (Choose Append in drop down) as example

-TRACE 135 -TOOLSTRACEPC 2048 -TOOLSTRACESQL 7
You can set the numbers per your requirement.

(ii) Configuration File: This applies to every User and every Process.
Set below parameters in psappsrv.cfg and psprcs.cfg files
TraceSQL=0,
TraceAE=135,
TracePC=2048.

Configuration files are located under:
\Appserv\\psappsrv.cfg
\Appserv\prcs\\psprcs.cfg
After making these changes you need to restart application server and process scheduler.

You will find the traces under:
From Application - Process Monitor, Details -> View Log/Trace link
Server - \appserv\prcs\\log_output\AE__\.....
If Report Repository is set then under Webserver\psreports_weblogic (for our GSC environments it will be here).


To decide what numbers you want to set for each parameters.
***********************************************************
Add the bits for the type of trace you want, this is applicable to both application as well as AE trace

; SQL Tracing Bitfield
;
; Bit Type of tracing
; --- ---------------
; 1 - SQL statements
; 2 - SQL statement variables
; 4 - SQL connect, disconnect, commit and rollback
; 8 - Row Fetch (indicates that it occurred, not data)
; 16 - All other API calls except ssb
; 32 - Set Select Buffers (identifies the attributes of columns
; to be selected).
; 64 - Database API specific calls
; 128 - COBOL statement timings
; 256 - Sybase Bind information
; 512 - Sybase Fetch information
; 4096 - Manager information
; 8192 - Mapcore information
; Dynamic change allowed for TraceSql and TraceSqlMask
TraceSql=0
TraceSqlMask=12319

;-------------------------------------------------------------------------
; PeopleCode Tracing Bitfield
;
; Bit Type of tracing
; --- ---------------
; 1 - Trace Evaluator instructions (not recommended)
; 2 - List Evaluator program (not recommended)
; 4 - Show assignments to variables
; 8 - Show fetched values
; 16 - Show stack
; 64 - Trace start of programs
; 128 - Trace external function calls
; 256 - Trace internal function calls
; 512 - Show parameter values
; 1024 - Show function return value
; 2048 - Trace each statement in program (recommended)
; Dynamic change allowed for TracePC and TracePCMask
TracePC=0
TracePCMask=4095


Here is how to interpret a trace:
**************************************

The trace on the following page shows two things being traced: SQL and PeopleCode.

SQL Trace:

The SQL Trace traces API calls. While there are many different API calls used, Ill cover the relevant ones. API call description
COM Compile/prepare a SQL statement
CEX Compile and execute a SQL statement
EXE Execute a compiled SQL statement
Fetch Fetch a single row.
Bind Bind a parameter (this is for bind variables)
Connect If this is for cursor 1, then this will result in a physical connection to the database and it will allocate a cursor. If this is for cursor > 1, then this will simply allocate a new cursor.
Disconnect This will close a cursor. If this is for cursor 1, then this will terminate the connection to the database.
Commit Commit the Unit of Work.

PeopleCode Trace:

The PeopleCode trace shows the starting and ending of programs. It also shows the calling of any child programs.
� Start = The start of a Program.
� End = The end of a Program.
� Start-ext = The start of a child Program.
� End-ext = The end of a child program.
.

Trace Examples
This trace is for PeopleTools 8.0.

1-23339 18.22.07 0.020 Cur#1.OM800DVL RC=0 Dur=0.010 COM Stmt=SELECT OPRID, NAME1, BUSINESS_UNIT, SETID, LEDGER, LEDGER_GROUP, SOURCE, (CONVERT(CHAR(10),AS_OF_DATE,121)), LC_CNTRY, ALT_CHAR_ENABLED, DR_CR_VISIBLE_FLG FROM PS_OPR_DEF_TBL_FS WHERE OPRID=:1 ORDER BY OPRID
1-23340 18.22.07 0.000 Cur#1.OM800DVL RC=0 Dur=0.000 Bind-1 type=2 length=3 value=VP1
1-23341 18.22.07 0.010 Cur#1.OM800DVL RC=0 Dur=0.010 EXE
1-23342 18.22.07 0.000 Cur#1.OM800DVL RC=0 Dur=0.000 Fetch
1-23343 18.22.07 0.010 >>> start Nest=00 . EG_DISP_ECO_WRK.INV_ITEM_ID.RowInit
1-23344 18.22.07 0.010 >>> start-ext Nest=01 eg_set_eco_fields FUNCLIB_EGINTFC.EG_DISP_ECO.FieldFormula
1-23345 18.22.07 0.000 Cur#1.OM800DVL RC=0 Dur=0.000 COM Stmt=SELECT BUSINESS_UNIT, INV_ITEM_ID, ECO_ID FROM PS_EG_ECO_DISP_FS WHERE BUSINESS_UNIT=:1 AND INV_ITEM_ID=:2 ORDER BY BUSINESS_UNIT, INV_ITEM_ID
1-23346 18.22.07 0.000 Cur#1.OM800DVL RC=0 Dur=0.000 Bind-1 type=2 length=3 value=M04
1-23347 18.22.07 0.000 Cur#1.OM800DVL RC=0 Dur=0.000 Bind-2 type=2 length=5 value=30100
1-23348 18.22.07 0.010 Cur#1.OM800DVL RC=0 Dur=0.010 EXE
1-23349 18.22.07 0.000 Cur#1.OM800DVL RC=1 Dur=0.000 Fetch
1-23350 18.22.07 0.010 Cur#1.OM800DVL RC=0 Dur=0.010 COM Stmt=SELECT SETID, INV_ITEM_ID, DESCR60 FROM PS_MASTER_ITEM_TBL WHERE SETID=:1 AND INV_ITEM_ID=:2 ORDER BY SETID, INV_ITEM_ID
1-23351 18.22.07 0.000 Cur#1.OM800DVL RC=0 Dur=0.000 Bind-1 type=2 length=3 value=MFG
1-23352 18.22.07 0.000 Cur#1.OM800DVL RC=0 Dur=0.000 Bind-2 type=2 length=5 value=30100
1-23353 18.22.07 0.010 Cur#1.OM800DVL RC=0 Dur=0.010 EXE
1-23354 18.22.07 0.000 Cur#1.OM800DVL RC=0 Dur=0.000 Fetch
1-23355 18.22.07 0.000 <<< end-ext Nest=01 eg_set_eco_fields FUNCLIB_EGINTFC.EG_DISP_ECO.FieldFormula Dur=0.030
1-23356 18.22.07 0.000 >>> start-ext Nest=01 eg_disp_eco FUNCLIB_EGINTFC.EG_DISP_ECO.FieldFormula
1-23357 18.22.07 0.000 <<< end-ext Nest=01 eg_disp_eco FUNCLIB_EGINTFC.EG_DISP_ECO.FieldFormula Dur=0.000
1-23358 18.22.07 0.000 <<< end Nest=00 . EG_DISP_ECO_WRK.INV_ITEM_ID.RowInit Dur=0.050
1-23359 18.22.07 0.000 >>> start Nest=00 . EN_REVISION.DATE_IN_EFFECT.RowInit
1-23360 18.22.07 0.000 <<< end Nest=00 . EN_REVISION.DATE_IN_EFFECT.RowInit Dur=0.000
1-23361 18.22.07 0.000 >>> start Nest=00 . DC_PB_WRK.DC_QRY.RowInit
1-23362 18.22.07 0.000 >>> start-ext Nest=01 Init_DC FUNCLIB_DC.DC_SETUP_PANEL.FieldFormula
1-23363 18.22.07 0.000 <<< end-ext Nest=01 Init_DC FUNCLIB_DC.DC_SETUP_PANEL.FieldFormula Dur=0.000
1-23364 18.22.07 0.000 <<< end Nest=00 . DC_PB_WRK.DC_QRY.RowInit Dur=0.000
1-23365 18.22.07 0.000 >>> start Nest=00 . DC_COPY_WRK.DC_COPY_SRC.RowInit
1-23366 18.22.07 0.000 <<< end Nest=00 . DC_COPY_WRK.DC_COPY_SRC.RowInit Dur=0.000


Understanding the Trace:

Heres how to understand the trace. Lets look at this trace line.

SQL Trace:
1-23339 18.22.07 0.020 Cur#1.OM800DVL RC=0 Dur=0.010 COM Stmt=SELECT OPRID, NAME1, BUSINESS_UNIT, SETID, LEDGER, LEDGER_GROUP, SOURCE, (CONVERT(CHAR(10),AS_OF_DATE,121)), LC_CNTRY, ALT_CHAR_ENABLED, DR_CR_VISIBLE_FLG FROM PS_OPR_DEF_TBL_FS WHERE OPRID=:1 ORDER BY OPRID

First column = 1-23339. This is a sequential line counter for that process (exe). If you have a second process running, then its line numbers will start with 2-xxx.

Second column = 18.22.07. Is the timestamp at which the trace line is written. This timestamp comes from the machine in which PeopleTools is running It does not come from the database.

Third column = 0.020. This is the time that has elapsed since the previous trace line was written. This means that line 1-23339 was written 0.020 seconds after line 1-23338. This time will be explained in more detail.

Fourth column = Cur#1. This is the cursor number for the statement. If you open a second cursor it will have a cursor number > 1.

Fifth column = OM800DVL. The PeopleSoft database in which this API call is executing.

Sixth column = RC=0. This is the return code for the associated API call.

Seventh column = Dur=0.010. This is the time to execute the assoicated API call. Depending on what the call is this might be the time to execute the statement. This will be explained in more detail.

Eight column = COM Stmt=SELECT OPRID, NAME1.. This is the database API call.

PeopleCode Trace:
If we look at these lines:
1-23361 18.22.07 0.000 >>> start Nest=00 . DC_PB_WRK.DC_QRY.RowInit
1-23362 18.22.07 0.000 >>> start-ext Nest=01 Init_DC FUNCLIB_DC.DC_SETUP_PANEL.FieldFormula
1-23363 18.22.07 0.000 <<< end-ext Nest=01 Init_DC FUNCLIB_DC.DC_SETUP_PANEL.FieldFormula Dur=0.000
1-23364 18.22.07 0.000 <<< end Nest=00 . DC_PB_WRK.DC_QRY.RowInit Dur=0.000

The first three columns are the same as they would be for a trace of an SQL event so well skip these.

Fourth Column >>> start = This indicates whether or not a PeopleCode program is starting or ending. If there is an ext following the command then this indicates that the PeopleCode program has been called from another PeopleCode program.

Fifth column = Nest=00. This indicates whether or not the PeopleCode has been called from another program. If it is Nest=00, then the PeopleCode has been triggered by an event and not by being called another program. If the numbers after the Nest are > 00, then this indicates that the program has been called by another program.

Sixth column = . DC_PB_WRK.DC_QRY.RowInit. This indicates the location of the Program. This tells you what Record.Field this Program is from (or new in 8.0, what PanelGroup, Record, Menu, etc).If a . Preceeds the name that this indicates that program has been triggered by an Event. If a function name Init preceeds the name than this indicates that this program has been called by another program.



Understanding the Trace Timers:

Understanding the trace timers is very key to diagnosing where performance issues are. There are two main timers to understand.

Seventh column = Dur=0.010. This represents the time for the API associated with the SQL statement to run. In other words, this could be the time to Prepare, Execute,

Third column = 0.020. To reiterate from above, this is the time since the previous trace line was written. So this time could represent any of the following:
� User Think Time. If the value here is very large than most likely this time is User Think Time. If the line prior to this line is a Disconnect, Commit or a Rollback then there is a very good chance that this time is User Think Time. If this is the case then, a large value here should be of no worry.
� PeopleTools processing This would be for an activity that did not result in a Trace line being written. This might be PeopleCode (providing the PeopleCode trace was NOT turned on), this could be Panel Processor, this could be PeopleTools waiting for a Remote Call (a COBOL process which runs as a whole separate process) to complete, etc. A large value due to one of these reasons is something that should definitely looked into.
� SQL Time This includes (is a superset) the SQL time
Manager Information trace

The Manager Information trace can be used to determine the location from which PeopleTools meta-data objects are retrieved. These objects reside within the database but are read into memory by each PSAPPSRV process as they are required. As of PeopleTools 7.5 some (but not all*) memory cached objects are also file cached on the application server. The file-cached objects reduce the time required to cache a newly booted PSAPPSRV process.Whenever a PSAPPSRV requires a new object it first looks in the file-cached objects before retrieving the database object.

It is important to remember that each PSAPPSRV process has its own memory cache.

To determine whether an object is retreived from memory, file cache or database, enable the Manager Information trace flag. The location are as follows:

Object retrieved from Memory:
PSPPRSRV.20997 1-101716 Get : Local RDM(VNDR_PANELS_WRK/POR)0.0.233300

Object retrieved from File -
PSPPRSRV.20997 1-101716 Get : Cache XTM(LANGUAGE_CD/ENG)0.12312.1

Object retrieved from Database -
PSPPRSRV.20997 1-101716 Get : Remote 2130647268.1.0

* Not all objects on the app server are file cached. What this means is the very first access to these objects will be from the database instead of file cache. Subsequent accesses will be from memory cache. The things that are not file cached are the infrequently accessed objects.

CF_SEQNO, ACTIVITY_SW, and RETIRE_SW

CF_SEQNO was added as a key field on COST, DEPRECIATION, .. tables to replace ChartFields as key fields. Each unique CF_SEQNO value represents a unique set of ChartFields on the Business Unit / Asset / Book level. We start numbering CF_SEQNO from 0. Follow the example below for the assignment of the field.

Add asset with ChartField DEPTID = A and Operating Unit = 1
Transfer asset to DEPTID B
Adjust asset
Transfer asset to Operating Unit 2
Transfer asset back to DEPTID A and Operating Unit 2.

ASSET_ID TRANS_TYPE CF_SEQNO DEPTID OPERATING_UNIT
0000000001 ADD 0 A 1
0000000001 TRF - O 0 A 1
0000000001 TRF - I 1 B 1
0000000001 ADJ 1 B 1
0000000001 TRF 2 B 2
0000000001 TRF 0 A 1

ACTIVITY_SW designates which row is active. Usually you will see 0, 1, -1. The 0 row is the active row.

Basically when you add an asset (i.e. it's active), the activity_sw = 0. If you do something to the asset, like recategorize or transfer it, then the RCT out row (or TRF out row) will receive an activity_sw value of 1 and the RCT in row (or TRF in row) will receive an activity_sw value of 0. The -1 activity_sw value comes into play if the asset becomes active again. That is, in the above example, if I were to transfer the asset BACK to the original value, then the TRF in value would get a -1 value, so that the sum of 0, 1 and -1, totals 0 and is hence active again.

RETIRE_SW identifies whether this ChartField combination for the asset book has been retired (fully) or not. This is a numeric field and works the same as the activity switch.

Peoplesoft Solutions

Please post your questions related to any Peoplesoft financial modules or technical questions. We would provide you with answers asap.

Setting up EMAIL for PO DISPATCH

Here is the information from the PeopleBooks:
When you run the PO Dispatch/Print process from the Dispatch Purchase Orders
page, the PeopleSoft Process Scheduler Request page offers you three options:
1. PO Dispatch & Email job
Select to run the PO Dispatch/Print process and then the Email process (PODISP).
2. PO Dispatch/Print process
Select to run only the PO Dispatch/Print process (POPO005).
3. Email process
Select to run only the Email (PO_PO_EMAIL) process.

When you use the email dispatch method, the PO Dispatch/Print process creates
an output file in the location you specify on the File Locations page. The
Email process picks up the output file from the specified location and sends
out an email message with the PO as an attachment. When the dispatch method is
fax, the dispatch process creates a PO file and a fax control file in the
locations you specify on the File Locations page.

To dispatch by email, you first need to run the PO Dispatch/Print process to
create the output files and to update the PO Email queue table. You then run
the Email process (PO_PO_EMAIL).

The Email process is a PeopleSoft Application Engine process that picks up the
email output file created by the PO Dispatch/Print process in the location
specified on the File Locations page and generates an email message through the
SMTP gateway to be delivered to your recipient through the internet. If you
specify a vendor contact on the Vendor Details page on the PO, the Email
process sends the email to the address that you specify in the Email ID field
on the Vendor Information - Contacts page. If you do not specify a vendor
contact on the PO, the Email process sends the email to the address you specify
in the Email ID field on the Vendor Information - Address page. When a message
is sent successfully, it is removed from the email queue table; however, the PO
output files are not deleted.

To simplify dispatching by email you can use the PO Dispatch & Email job on the
PeopleSoft Process Scheduler Request page that you access from the Dispatch
Purchase Orders page. The PO Dispatch & Email job automatically runs the PO
Dispatch/Print process and then the Email process. For online dispatches, the
system always schedules the PO Dispatch & Email job.

An important factor to consider when you select an appropriate output format is
your recipient. When you dispatch by email, you may want to select a dispatch
output format of PDF or HTM, because these formats employ readers that are
commonly found on user systems. When you dispatch by fax, you may want to
consider that most fax software applications are compatible with Postscript-
(PS) or PCL- (HP) encoded files.

The email dispatch method is designed to work with a SMTP gateway.
The settings for the SMTP gateway must be entered when you set up a PeopleSoft
Process Scheduler Server.

Life Cycle of an ASSET

This following addresses a sample Asset Life cycle involving the following steps,

1. Addition of an Asset .
2. Depreciation Calculation process.
3. Accounting Entry Creation.
4. Transfer of an Asset.
5. Retirement.

ADDITION OF AN ASSET.

The main tables which get updated on adding an asset ,

a) PS_COST.
b) PS_ASSET.
c) PS_BOOK.
d) PS_BOOK_HIST.
e) PS_OPEN_TRANS
f) PS_ASSET_ACQ_DET.
g) PS_ASSET_CUSTODIAN.
h) PS_ASSET_LOCATION.

a) PS_COST.
It represents the cost of the transaction. The transaction’s cost for an asset book is summed to provide a balance at a given point of time.

The main fields which are updated ,

1. Asset ID.
This denotes the distinct number which is assigned to the asset, it is automatically incremented when the Asset ID is selected as ‘ NEXT’

2. Category.
Category classify assets by type for accounting entry purposes. Typically these categories reflect how assets are reported on balance sheets. Examples of commonly used asset categories are Furniture and Fixtures , Machinery and Equipment , etc.

3. Trans_Type.
When adding an asset the transaction type is either an ADD or TRF (in).



4. Trans_Date.
The transaction date is the date when the asset is added . It reflects the date when the event has taken place. The transaction date must be earlier or equal to the accounting date.

5. Accounting date.
The accounting date reflects when the asset was booked or sent to accounting in the system.

6. Cost.
The purchase cost.

ASSET_ID CATEGORY TRANS_TYPE TRANS_DT ACCOUNTING_DT COST
100 AUTO ADD 11/22/06 12:00 AM 11/22/06 12:00 AM 5000

b) PS_ASSET.
This table represents the general fields which are populated for Asset.

A few important fields which are populated,

1. Asset Status
The status of the asset, whether it is In-service, Disposed, Transferred, etc.

2. Profile_ID.
The profile ID of the asset is the value the asset was originally added to, e.g. Motor Vehicle , Furniture and Fixtures, etc.

c) PS_BOOK.
It identifies the asset’s depreciation attributes by book. Also some other depreciation related fields as mentioned below are part of this table.

The main fields which are populated,

1. Book.
The Asset Book which are associated to the asset, like CORP, or FEDERAL, or TAX, etc.

2. In Service date.
The date the asset was placed in service.

3. Begin Depr. Date.
The date when the asset begins depreciating. This is calculated by the Depreciation Calculation process.

4. Depr. Status.
The depreciation status whether it is Depreciable or Non-Depreciable.

5. Convention.
Depreciation convention in combination with asset in-service date determines when the depreciation starts.

BOOK IN_SERVICE_DT BEGIN_DEPR_DT DEPR_STATUS CONVENTION
CORP 11/22/06 12:00 AM NULL D FM


d) PS_BOOK_HIST.
This represents, the history of book transactions which have happened in the life of an asset.

The main fields which are populated,

1. Book_Change_Type.
It represents the rows of transactions which affected the book (e.g. ADD, RES, SUS,OTH, LFE).
2. Transaction date.
3. Accounting date.
4. In Service date.
5. Life.
6. Method.
The depreciation methods are, Straight line, Declining Balance, etc.
The other table where the Depreciation method is referenced is , PS_BOOK.


BOOK_CHANGE_TYPE TRANS_DT ACCOUNTING_DT IN_SERVICE_DT LIFE METHOD
ADD 11/22/06 12:00 AM 11/22/06 12:00 AM 11/22/06 12:00 AM 60 SL


e) PS_OPEN_TRANS.
The open_trans table is a ‘to do’ list for several programs , such as AMDPCALC, and AMAEDIST. An entry is made to the OPEN_TRANS table. Also, the rows on open_trans represent the history of an asset’s transactions which affected the cost and the book.

The main fields which are populated,

1. Trans_Type.
The transaction type can be ADD, TRF, RET, RAD, ADJ, REI, RCT, BKS, SUS, RES,etc.

2. Calc_Depr_Status.
This field determines whether depreciation needs to be calculated on this transaction. It has the following 3 status, Pending, Completed, Never and Held for transfer in.
The meaning of these statuses are,

Pending  Depreciation has not been calculated for this transaction.

Completed Depreciation has been calculated for this transaction.

Never  The Depreciation calculation process will ignore this transaction.

In process  The Depreciation calculation process thinks its still processing this transaction.

Held for transfer in  When we process inter –unit transfers, the out and the in transactions are processed at the same time . This status indicates that the transaction is being held until its corresponding Transfer out can be processed.

3. Calc_Dist_Status.
This field determines whether accounting entries need to be created for this transaction.

Completed  This transaction has had amaedist run against it.

Never  The distribution process will ignore this transaction.

Pending  This transaction has not had amaedist run against it.

4. Calc_Rept_Status.

Completed  The Load Depreciation Reporting table process AMDPREPT, has been run and populated depr_rpt. Now reports can be run.

Pending  The process AMDPREPT has not been run on the Asset.


TRANS_TYPE CALC_DEPR_STATUS CALC_DIST_STATUS CALC_REPT_STATUS
ADD P P P

f) PS_ASSET_ACQ_DET.
This table represents the details of acquisition for an asset.

The main fields which are populated,

A) System_Source :--- The Source System from which the acquisition detail was derived for Asset Management. The most common values are, PAP ( Came from AP) , PPO ( Came from Purchasing ), CNV (came from Conversion ), and if it is blank, then asset was added online and not interfaced.

If the System Source is PAP, then you will have the following fields,

1. Voucher ID.
The Voucher which has created this asset. It happens when the asset is interfaced from AP.

2. Amount.
The cost of the asset.

VOUCHER_ID AMOUNT
5000
The voucher ID will be blank since the asset is entered through Express Add, and not interfaced from AP.

If the System Source is PPO, then you will have the following fields,

PO_ID PO_LINE_NBR


If the System Source is Blank, then it means the Asset was added online.

SYSTEM_SOURCE DESCR
ADDITION


g) PS_ASSET_CUSTODIAN.
This table represents the custodian, for an asset.

The main fields which are populated,

1. Custodian.
2. Emp ID.

h) PS_ASSET_LOCATION.
This gives the Location for an Asset.

The main fields which are populated,

2. Location.

To summarize following main tables get updated after doing addition,

PS_COST.

BOOK CATEGORY TRANS_TYPE TRANS_DT ACCOUNTING_DT QUANTITY TXN_COST COST
CORP AUTO ADD 11/22/06 0:00 11/22/06 0:00 1 5000 5000


PS_ASSET.

DESCR DESCRSHORT ASSET_STATUS FINANCIAL_ASSET_SW ACQUISITION_CD PROFILE_ID
ADDITION ADDITION I Y P AUTO

PS_BOOK.

BOOK IN_SERVICE_DT IN_SERVICE_PD IN_SERVICE_FY BEGIN_DEPR_DT BEGIN_DEPR_PD
CORP 11/22/06 12:00 AM 0 0 NULL 0

PS_OPEN_TRANS.


BOOK TRANS_TYPE CONVENTION CALC_DEPR_STATUS CALC_DIST_STATUS CALC_REPT_STATUS
CORP ADD FM P P P




DEPRECIATION CALCULATION PROCESS.

This process is run after the asset is added and is used to calculate the depreciation of the asset, based on the convention, and the in –service date.

The main tables which get updated on running the Depreciation calculation process,

1. PS_DEPRECIATION.
3. PS_BOOK.
4. PS_OPEN_TRANS.

a) PS_DEPRECIATION.
This table is populated after the Depreciation Calculation process is run, whenever there is a new transaction for the asset, e.g. BKS, ADD, TRF, RET, etc.

The main fields which are getting updated are,

1. Fiscal_Year
The year when the depreciation was taken.

2. Start_Pd.
The start period for which the depreciation was taken.

3. End_Pd.
Represents the end period for which the depreciation was taken.

4. Trans_type.
The transaction type here will be DPR.

5. Depreciation Amount.
The amount of Depreciation for the asset.

FISCAL_YEAR START_PD END_PD TRANS_TYPE DEPR
2006 12 12 DPR 50
2007 1 12 DPR 600


b) PS_BOOK.

The following fields gets updated on running the Depreciation calculation process,

1. In_Service_Pd.
The calendar period in which the in-service date falls.

2. In_Service_Fy.
The calendar fiscal year in which the in-service date falls.

3. Begin_Depr_Dt.
The date when the asset begins Depreciating.

4. End_Depr_Date.
This represents the date when the depreciation will stop for the asset.

IN_SERVICE_PD IN_SERVICE_FY BEGIN_DEPR_DT END_DEPR_DT
11 2006 12/1/06 12:00 AM 11/30/11 12:00 AM

c) PS_OPEN_TRANS.

The following fields gets updated on running the Depreciation calculation process,

1. Calc_Depr_Status.
The above field becomes Complete, from its earlier status of Pending.

CALC_DEPR_STATUS
C


To summarize following main tables get updated or inserted after running Depreciation calculation process.

PS_BOOK

BOOK IN_SERVICE_DT IN_SERVICE_PD IN_SERVICE_FY BEGIN_DEPR_DT BEGIN_DEPR_PD
CORP 11/22/06 12:00 AM 11 2006 12/1/06 12:00 AM 12


PS_OPEN_TRANS.


BOOK TRANS_TYPE CONVENTION CALC_DEPR_STATUS CALC_DIST_STATUS CALC_REPT_STATUS
CORP ADD FM C P P


PS_DEPRECIATION.

BOOK FISCAL_YEAR START_PD END_PD TRANS_TYPE TRANS_DT ACTUAL_END_PD CATEGORY
CORP 2006 12 12 DPR 12/1/06 12:00 AM 12 AUTO







ACCOUNTING ENTRY CREATION PROCESS.

The Accounting Entry creation process (AMAEDIST), creates asset transaction accounting entries, by selecting the appropriate accounting entry template. The selection is dependent on the acctg entry template ID, category, cost type, transaction type, and transaction code of the asset transaction. At the end of each accounting period , depreciation accounting entries are created by running the Depreciation Close process AMDPCLOS., which applies the depreciation (DPR), and the prior period depreciation (PDP), accounting entry templates, to the asset depreciation tables.

The main table which get updated on running the accounting entry creation process,

1. PS_DIST_LN.
2. PS_OPEN_TRANS.

a) PS_DIST_LN.
This table accumulates information from creating accounting entries.

The following fields gets updated on running the AMAEDIST process,

1. Account.
It classifies the nature of a transaction.

2. Distribution Type.
The accounting entries are created by using standard accounting entry types (distribution type ), based on the asset category, cost type, and action that is specified.e,g, FA means Fixed Asset and AP means Contra asset.

3. Ledger_Group.
The Ledger templates and detail ledgers are combined in an appropriate Ledger group.

4. Ledger.


ACCOUNT DISTRIBUTION_TYPE LEDGER_GROUP LEDGER
155000 FA RECORDING LOCAL
674000 AP RECORDING LOCAL


b) PS_OPEN_TRANS.

The following fields gets updated on running the AMAEDIST process,

1. Calc_Dist_Status.
The above field becomes Complete, from its earlier status of Pending.

To summarize following main tables get updated or inserted after running AMAEDIST process.



PS_DIST_LN.

BOOK ACCOUNT DISTRIBUTION_TYPE TRANS_TYPE CATEGORY LEDGER_GROUP LEDGER
CORP 155000 FA ADD AUTO RECORDING LOCAL
CORP 674000 AP ADD AUTO RECORDING LOCAL

PS_OPEN_TRANS.

BOOK TRANS_TYPE CONVENTION CALC_DEPR_STATUS CALC_DIST_STATUS CALC_REPT_STATUS
CORP ADD FM C C P



DEPRECIATION CLOSE PROCESS.

The Depreciation Close process generates period depreciation accounting entries for all depreciable assets in a particular accounting period. This process should be run only for an accounting period that has not yet been closed. It updates the dist_ln table similarly to AMAEDIST process. You specify the year and period to run the AMDPCLOS process. If there are rows already existing for ps_depreciation for that year / period , then DPR and /or PDP rows will be written to dist_ln.


TRANSFER OF AN ASSET.

Transfers can be briefly summed up as , changes to asset records, or to add newly received information. Modifications can be made to cost, quantity, chartfields like department, location, etc.

The following tables gets updated on transferring an asset.

a) PS_ASSET.
The status becomes T, when there is a full Transfer.
ASSET_STATUS
T



b) PS_OPEN_TRANS.
The following fields also get populated while doing an inter unit transfer,

Transfer_BU.
Transfer_Asset_ID.
Transfer_Book.
Trans_In_Out.

TRANS_TYPE TRANSFER_BU TRANSFER_ASSET_ID TRANS_IN_OUT
TRF US002 2 O

c) PS_COST.

TRANS_TYPE TRANS_IN_OUT
TRF O

d) PS_ACTIVITY_SW.

The activity_sw changes to 1 from 0 for a transfer.

BOOK DEPTID CATEGORY TRANS_TYPE TRANS_IN_OUT ACTIVITY_SW RETIRE_SW QUANTITY
CORP AUTO ADD 0 0 1
CORP AUTO TRF O 1 0 -1
CORP 10000 AUTO TRF I 0 0 1
CORP 10000 AUTO TRF O 1 0 -1


To summarize following main tables get updated or inserted after doing an intra unit and an inter unit transfer.

PS_ASSET.

DESCR DESCRSHORT ASSET_STATUS FINANCIAL_ASSET_SW ACQUISITION_CD PROFILE_ID
ADDITION ADDITION T Y P AUTO

PS_OPEN_TRANS.

BOOK TRANS_TYPE TRANSFER_BU TRANSFER_ASSET_ID TRANS_IN_OUT CALC_DEPR_STATUS CALC_DIST_STATUS
CORP ADD C C
CORP TRF P P
CORP TRF US002 2 O P P


PS_COST
BOOK DEPTID CATEGORY TRANS_TYPE TRANS_IN_OUT QUANTITY TXN_COST COST
CORP AUTO ADD 1 5000 5000
CORP AUTO TRF O -1 -5000 -5000
CORP 10000 AUTO TRF I 1 5000 5000
CORP 10000 AUTO TRF O -1 -5000 -5000


RETIREMENT OF AN ASSET.

Assets are retired when they are either disposed or no longer in use. They can be retired fully or partially in the past, present or the future.

The following tables gets updated or inserted on retiring an asset.

1. PS_BOOK.
2. PS_ASSET.
3. PS_COST
4. PS_RETIREMENT
5. PS_OPEN-TRANS.

1. PS_BOOK.
The change which is evident after retiring an asset is, the Retired_SW field becomes Y.

RETIRED_SW
Y

2. PS_ASSET.

The asset status becomes Disposed on retiring an Asset, only if a full retirement was done.

ASSET_STATUS
D

3. PS_COST.

The trans_type changes to RET, and the quantity and cost becomes Negative, signifying that the asset has been retired, and the retire_sw changes from 0 to 1.


TRANS_TYPE QUANTITY
RET -1

4. PS_RETIREMENT.
The retirement table gets inserted only when an asset is retired or disposed off.

The main fields which gets populated,

1. RETIREMENT_DT.
2. END_DEPR_DT (after amdpcalc runs)
3. DISPOSAL CODE.
4. QUANTITY.
5. RETIREMENT_AMOUNT.
6. GAIN_LOSS. (after amdpcalc runs)
7. RETIREMENT_RSV (after amdpcalc runs)

RETIREMENT_DT END_DEPR_DT DISPOSAL_CODE QUANTITY GAIN_LOSS
NULL NULL S -1 0

5. PS_OPEN_TRANS

The trans_type is changed to RET, on retiring the asset.

TRANS_TYPE
RET

To summarize following main tables get updated or inserted after retiring the asset.

PS_BOOK.

BOOK DEPR_STATUS METHOD RETIRED_SW RETIRE_CONVENTION
CORP D SL Y FM

PS_COST.

BOOK TRANS_TYPE RETIRE_SW QUANTITY
CORP RET 1 -1

PS_RETIREMENT.

RETIREMENT_DT END_DEPR_DT DISPOSAL_CODE QUANTITY GAIN_LOSS
NULL NULL S -1 0

PS_OPEN_TRANS

TRANS_TYPE CONVENTION CALC_DEPR_STATUS CALC_DIST_STATUS CALC_REPT_STATUS
RET FM P P P

Troubleshooting AR UPDATE Process

Instructions for the Reader
A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.



Overview
The recovery from an ARUPDATE abend depends on WHERE the abend occurred in the lifecycle of the ARUPDATE multiprocess job. Hence, it is important to appreciate the 'structure' of the ARUPDATE job and the processes, executed steps and their sequence.

The Receivable Update multi-process job (ARUPDATE) consists of up to eight steps, but these 4 are always executed:

1. AR_UPDATE AE process partitions the data and, if needed, calls AE AR_POSTING to create Payment, Maintenance, Transfer, Direct Debit and/or Draft Worksheets GROUP_CONTROL and PENDING_ITEM records.

2. AR_PGG job contains AE process AR_PGG1 that calls AE process AR_PGG_SERV to generate PENDING_DST accounting entries and, if needed, VAT and IU entries for any groups that are set to post.

3. AR_POST job contains AE process AR_POST1 that calls AE process AR_POSTING to post the transactions in each selected group.

4. AR_UPDATE2 AE process performs cleanup tasks.

The AR_UPDATE, AR_PGG1, AR_POST1 and AR_UPDATE2 will generate Message Log and Application Engine (AE) trace files. Hence, when ARUPDATE is run, you can examine the following traces (produced in HTM but you can also save as TXT files) but please note the sequence of allocating Process Instances for each AE is not sequential. :

AE_AR_UPDATE__AET, AE_AR_PGG1__AET, AE_AR_POST1__AET & AE_AR_UPDATE2__AET.

ARUPDATE Main Processes
1. AR_UPDATE
1. Builds groups of pending items (GROUP_CONTROL, PENDING_ITEM) from Payment, Maintenance, Transfer, Direct Debit and Drafts worksheets and
2. Executes the parallel preprocessor to add group records to RP_PARALLEL and partition all groups set to post among the specified parallel processes.

2. AR_PGG
The AR_PGG parallel job calls the AR_PGG1 AE Process (As delivered in DEMO. At customer sites you may also have AR_PGG2, AR_PGG3, etc.). The AR_PGG1 AE Process sets the partition to #1 and then calls AR_PGG_SERV to actually process the data; it is only 1 Step. The AR_PGG_SERV main operations are:

1. Copies group records from RP_PARALLEL to a Temp table
2. Locks the PENDING_ITEM by setting the records PROCESS_INSTANCE number and updates DST_BAL_ITEM =’O’
3. Locks GROUP_CONTROL records for each group and updates DST_BAL_STATUS=’O’
4. Creates and inserts the new User then System accounting entry rows in a Temp table PS_PG_PENDST_TAOn
5. Retrieves Customer records for each pending item
6. Starts the IU processor, if needed, to add Inter/Intra rows to PENDING_DST
7. Inserts into PENDING_DST from PS_PG_PENDST_TAOn
8. Updates GROUP_CONTROL DST_BAL_STATUS=’I’ and unlocks group records
9. Updates PENDING_ITEM DST_BAL_ITEM=’I’ and unlocks pending item records


3. AR_POST
The AR_POST parallel job calls AR_POST1. The AR_POST1 AE Process contains a MAIN Section with only 1 POSTING Step and 2 Actions that sets the partition and RP_ACTION to ‘P’ and then calls the AR_POSTING AE Process. The AR_POSTING AE is called twice:

1. The 1st time from AR_UPDATE to generate any Payment, Maintenance, Transfer, Draft or Direct Debit Groups

2. The 2nd time from AR_POST1 to post the generated group pending items

The AR_POSTING logic executed depends on RP_ACTION when the program is called. If the RP_ACTION = ‘W’ then AR_POSTING will generate Worksheet groups and pending items. This was covered in the AR_UPDATE analysis section. If the RP_ACTION =’P’ then AR_POSTING will post the generated groups pending items.


4. AR_UPDATE2
The AR_UPDATE2 AE consists of a MAIN calling the UPDATE2 Section. The UPDATE2 Section calls AR_POSTING, AR_REV_EST and AR_JGEN as needed. The HISTORY Step calls AR_POSTING.UPDATE2 Section if the Run Control Parameters include any History flags. The ARREVEST Step calls AR_REV_EST.MAIN Section if Commitment Control is switched ON for AR. The FS_JGEN Step calls AR_JGEN.MAIN Section if the Run Control Process to GL flag is ‘Y’.


Troubleshooting Steps
Step 1: Verify If Online Recovery is Feasible
Navigate to: Accounts Receivable > Receivables Update > Reset Process page.

If the process does not appear on the 'Process Reset' page, click 'Refresh' or Search (magnifying glass) to ensure the page has the latest updates. If the process appears under the 'Not Successful Process Requests' list, then either or both of the 'Reset' and 'Restart' (yellow) buttons will be highlighted. This verifies whether the process can be reset or restarted online (hence avoiding external SQL updates and ensuring data integrity). If there is a need to log a GCS SR later on, ensure this page is attached to SR.

Step 2: Verify Where the Abend Occured
1.1 Which process abended: AR_UPDATE, AR_PGG [AR_PGG_SERV], AR_POST [AR_POSTING] or AR_UPDATE2?

1.1.1 If the Abend occured in AR_UPDATE (1st process) then the recovery is relatively simple as no accounting entries were generated (AR_PGG) and no posting/table updating occured (AR_POST). Once the reason for the abend is resolved (which naturally varies according to the Abend message), the job can be Restarted or Reset online (if feasible).

1.1.2 If the Abend occcured in AR_PGG/AR_PGG_SERV, then application data records have been 'locked' by the abending process (all relevant table records PROCESS_INSTANCE field updated with the running/abended Process Instance number) and will need to be unlocked (otherwise, they cannot be used by any other process). This 'unlocking' is part of the 'Reset' online process (if feasible) and effectively does 2 main updates:

1. Updates PROCESS_INSTANCE field in all relevant data records to Zero (the value '0', to allow data records to be selected again)

2. Deletes all records from RP_PARALLEL (to allow groups to be re-selected again)

The exact SQLs used by the online 'Reset' process can be found by executing this SQL:

select * from PS_AR_RESETSQL_TBL where PRCSNAME='ARUPDATE'

1.1.3 If the Abend occcured in AR_POST/AR_POSTING, then application data records could have been updated (e.g. new ITEMs inserted, new ITEM_DST accounting records added, CUST_DATA customer balances updated, etc.). If the database records updates were not Rolled Back (reveresed or not Committed) then this would constitute a major effort as it could mean data corruption (e.g. Related parent-child tables are out-of-synch, totals incorrect, etc.). The determination of the extent of the data corruption, if any, would be the first task in this (thankfully rare) situation.

1.1.4 If the Abend occured in AR_UPDATE2, then it would mean that database application records have bee updated but Customer History may have not. Depending on the Abend, this could be recovered in the next run of ARUDPATE (as Customer History updates are Run Time Options driven) but it would need in-depth investigation.

Step 3: Verify Data Status after ARUPDATE Abend
1. Verify any existing records locked by Process Instance = Abending Process Instance PI# .

(some of the tables below, e.g VAT, SUBCUST, DRAFT may not be relevant to your site):

SELECT * FROM PS_GROUP_CONTROL WHERE PROCESS_INSTANCE =’PI#’
SELECT * FROM PS_PENDING_ITEM WHERE PROCESS_INSTANCE =’PI#’
SELECT * FROM PS_PENDING_DST WHERE PROCESS_INSTANCE =’PI#’
SELECT * FROM PS_PENDING_VAT WHERE PROCESS_INSTANCE =’PI#’

SELECT * FROM PS_ITEM WHERE PROCESS_INSTANCE =’PI#’
SELECT * FROM PS_ITEM_ACTIVITY WHERE PROCESS_INSTANCE =’PI#’
SELECT * FROM PS_ITEM_ACT_VAT WHERE PROCESS_INSTANCE =’PI#’

SELECT * FROM PS_CUST_DATA WHERE PROCESS_INSTANCE =’PI#’
SELECT * FROM PS_CUST_HISTORY WHERE PROCESS_INSTANCE =’PI#’
SELECT * FROM PS_SUBCUST_DATA WHERE PROCESS_INSTANCE =’PI#’
SELECT * FROM PS_SUBCUST_HISTORY WHERE PROCESS_INSTANCE =’PI#’

SELECT * FROM PS_PAYMENT WHERE PROCESS_INSTANCE =’PI#’
SELECT * FROM PS_DRAFT_CONTROL WHERE PROCESS_INSTANCE =’PI#’

2. Verify the Abended Process selected Groups:

SELECT * FROM PS_RP_PARALLEL WHERE RUN_CNTL_ID =’%’ and OPRID = ‘%’

3. Verify the Abended PI# Run Control ID and TAO status and data:

(where '%' is your actual Run Control ID or Operator ID)

SELECT * FROM PS_AERUNCONTROL WHERE PROCESS_INSTANCE=’PI#’ AND RUN_CNTL_ID =’%’ and OPRID = ‘%’
SELECT * FROM PS_AETEMPTBLMGR WHERE PROCESS_INSTANCE=’PI#’ AND RUN_CNTL_ID =’%’ and OPRID = ‘%’

SELECT * FROM PS_PGG_SERVICE_AET WHERE RUN_CNTL_ID =’%’ and OPRID = ‘%’
SELECT * FROM PS_RP_POSTING_AET WHERE RUN_CNTL_ID =’%’ and OPRID = ‘%’'
SELECT * FROM PS_AR_UPDATE_AET WHERE RUN_CNTL_ID =’%’ and OPRID = ‘%’
SELECT * FROM PS_AE_REQUEST WHERE RUN_CNTL_ID =’%’ and OPRID = ‘%’'
SELECT * FROM PS_AE_REQUEST_OPT WHERE RUN_CNTL_ID =’%’ and OPRID = ‘%’
Step 4: Evaluate Recovery Options
Recovery Option 1 [Online Data Correction/Isolation]

If the 'offending' source of data (e.g. Online Group, External Billing Group, Maintenance Worksheet, etc) has been identfied, then this specific data source could either be corrected or 'isolated' using online pages. This allows further processing of ARUDPATE (Restart or Reset, below), especially if this Abend occurs in a Production environment with tight nightly schedules.

Example 1 : Online Group 123 is identified as containing data corruption, hence, if feasbile using online navigation path, it's Posting Status would be set to Do Not Post so it will be bypassed by the next run of ARUPDATE or it could just be completely deleted online and re-added again.

Example 2: If a missing setup is causing the abend (e.g. missing currency rate, missing Entry Type, missing Payment Terms code to calculate Due Date, etc.) then the missing setup data could be added/updated online before the Reset or Restart of the abended process.

In summary: If the identified data causing the abend can be corrected online then this would always be the preferred approach, otherwise external SQL updates may be necessary (see below).

Recovery Option 2 [Online Restart]

If the online page Accounts Receivable > Receivables Update > Reset Process shows the abended process and allows (highlighted yellow button) online Restart, and the reason for the abend is known and is transient (not permanent) and fixed then the ARUPDATE job can be Restarted (resumed) online. This will execute ARUPDATE again starting from the abended step, but this time, as the cause of the abend has been resolved externally, the step will hence execute correctly. Naturally, this assumes that the Abend cause has been identified and resolved (setup changed, currency rate added, etc.). If the Abend cause has not been identified, then Restart is not an effective action as the job will simply abend again on the exact same step.

Recovery Option 3 [Online Reset]

If the online page Accounts Receivable > Receivables Update > Reset Process shows the abended process and allows (highlighted yellow button) online Reset, the Abend cause needs more in-depth further investigation or does not allow Restart (missing setup that will adversely impact futher processing if job allowed to resume), and the database records need to be 'unlocked' then the online Reset will execute the SQL Updates of PS_AR_RESETSQL_TBL and 'release' all relevant locked records. This implies the next run of ARUPDATE will start from a 'clean slate' and all relevant records (online groups, worksheets, etc.), as per Run Time Options, will be re-selected for processing.

References to the above Restart/Reset processes are as follows: (different release levels may have different chapter numbers):

PeopleSoft Enterprise Receivables 9.0 PeopleBook > Chapter 29 Posting and Unposting Groups > Section Understanding How to Restart or Reset Receivable Update. Here is the text:
"Accounts Receivable, Receivables Update, Reset Process, Process Reset
Access the Process Reset page. Enter your search criteria for the process instance and click Refresh.
In the Not Successful Process Requests grid, select the process instance that you want"


Recovery Option 4 [Offline SQL to Unlock Records]

Usually, this Option should not be attempted if any of the Online Options are feasible. However, in some situations this could be the only feasible option. In most instances, but NOT all, an AR_PGG_SERV abend could be recovered by unlocking the records locked by the abended process (or, in other scenarios, another process impacting the abended process).

Locking simply means the records PROCESS_INSTANCE field has been set to the abended Process Instance Number. Step 3: Verify Data Status after ARUPDATE Abend above would have identified the locked records and hence the SQL updates to unlock them would take the form of (using Group_Control as an example, but you will need to repeat for all tables):

UPDATE PS_GROUP_CONTROL SET PROCESS_INSTANCE = '0' WHERE GROUP_BU ='Group Business U:nit' AND GROUP_ID ='Group ID' AND PROCESS_INSTANCE =’PI#’

After all relevant tables (see Step 3 Verify above) has been unlocked, the RP_PARALLEL table (where selected Groups are stored during each process run) will need to be cleared, otherwise any group whose record still exists on this table will NOT be selected in the next run of ARUPDATE. The SQL code will take this form:

DELETE FROM PS_RP_PARALLEL WHERE RUN_CNTL_ID =’%’ and OPRID = ‘%’
Recovery Option 5 [Offline SQL to Correct Data]

There could be instances where SQL updates to data tables (whether temporary _TAO or application) could resolve the abend and allow a Restart (resume processing) from the Abended step. This scenario typically occurs in duplicate INSERT abends (where a record with an identical key to the Source table record already exists on the Target table). The approach could be to remove the existing record from the Target table or the new record from the Source table, depending which record is erroneous.

Naturally, the offline SQLs would depend on which tables are impacted and after careful examination of subsequent impact on processing. Hence, any such SQLs must be tested on a COPY of Production database first (this, as previously stated, is advised for all external SQLs).

Example 1: An abend at AR_POSTING.DISTRIB.INSERT where INSERT INTO PS_ITEM_DST abends due to duplicate records on the PS_RP_PNDDST_TAO4 source temporary table.

1. Examine if the PS_RP_PNDDST_TAO4 source table contained multiple duplicate records. A sample SQL would be to select rows with same KEY field values:

SELECT DISTINCT BUSINESS_UNIT, CUST_ID, ITEM, ITEM_LINE,
ITEM_SEQ_NUM, LEDGER_GROUP, LEDGER, DST_SEQ_NUM
FROM PS_RP_PNDDST_TAO4 WHERE PROCESS_INSTANCE = 'PI#'

2. Examine if ITEM_DST already has records that are similar (same Key fields) as the incoming records from PS_RP_PNDDST_TAO4. A sample SQL would be to select from both tables the records that share the same KEY field values:

SELECT A.BUSINESS_UNIT, A.CUST_ID, A.GROUP_ID, A.GROUP_BU,
A.ITEM, A.ITEM_LINE, A.ITEM_SEQ_NUM, A.LEDGER_GROUP, A.LEDGER, A.DST_SEQ_NUM
FROM PS_RP_PNDDST_TAO4 A, PS_ITEM_DST B
WHERE A.PROCESS_INSTANCE = 'PI#'
AND A.BUSINESS_UNIT=B.BUSINESS_UNIT
AND A.CUST_ID=B.CUST_ID
AND A.ITEM=B.ITEM
AND A.ITEM_LINE=B.ITEM_LINE
AND A.ITEM_SEQ_NUM=B.ITEM_SEQ_NUM
AND A.LEDGER=B.LEDGER
AND A.LEDGER_GROUP=B.LEDGER_GROUP
AND A.DST_SEQ_NUM=B.DST_SEQ_NUM

Based on the results above, and approach could be to Update the PROCESS_INSTANCE = '0' on PS_RP_PNDDST_TAO4 or Delete the (duplicate) rows from PS_RP_PNDDST_TAO4 table and then Restart the process.

Example 2: An abend at AR_PGG_SERV.MNT_SUSP.INSCFBAL with error message "ORA-00947: not enough values" is traced to the online ChartField Inheritance page for SetID+Receivables (SHARE + AR_BI) not displaying the correct number of ChartFields (say displaying 4 instead of the defined 5). This is maniftested itself in the INSERT and SELECT not having corresponding number of fields ("not enough values"). The resolution would be to SQL Delete the 'SHARE'+'AR_BI' records in PS_CF_INHERIT_TBL (the table 'behind' the online page) and then re-creating the records (all 5 of them, in this example) again using the online ChartField Inheritance page.

Example 3: Abends can sometimes occur due to incorrect data values in pre-defined fields, such as special characters in Customer Names or lower-case letters in Item IDs. This is usually shown by such error messages as "ORA-01756: quoted string not properly terminated" (or similar). The recovery is to update the data with the correct values that correspond to the Field Type (e.g UPPERCASE).

Recovery Option 6 [External Table, Program or Run Control Fixes]

In some cases, the resolution could be by external actions on impacted objects (tables, programs, etc.) that does NOT involved SQL updates to data. This would include re-building tables, re-compiling code, etc.

Example 1: A rare abend at AR_POSTING.ITEM_UYT.PNDRULES with error message "ORA-00600: internal error code" (which does not have any prior references on the GCS database) was due to corrupt temporary table PS_RP_TERMS_TAO. Rebuilding the table resolved the issue, without any further SQL data manipulation.

Example 2: Abends referring to COBOL calls/references could be resolved by ensuring the COBOL directory is correct and/or re-compiling COBOL source objects.

Example 3: A one-time event (e.g. 1st run after an upgrade) caused an abend due to execution of a process step that should not be executed for upgraded data missing information needed by new setup. A possible course of action would be to inactivate the Step, upgrade, then re-activate the Step for new data.

Example 4: ARUPDATE abended due to Deadlock, hence the process can be Restarted (once the Deadlock is resolved). However, this would need manipulation of the Run Control status of the Parent and Child processes. For a detailed example refer to KM Doc ID: EAR8.8SP1+: Abended ARUPDATE Job Stays in QUEUED Status Even After Pressing Restart Button. (Doc ID 865335.1) Note 865335.1

Recovery Option 7 [Add/Fix Run Control]

Sometimes running ARUPDATE with a New Run Control record (different ID/Name to the abended process Run Control) will resolve the issue if the existing Run Control records have been corrupted in any way. However, if the existing abended job Run Control ID needs to be used again it would mean deleting its records from the control tables. This is the list of tbles where the Run Control may exist and needs to be deleted (verify first then delete if/as needed):


DELETE FROM PS_AERUNCONTROL WHERE OPRID = '<>' AND RUN_CNTL_ID = '<>'
DELETE FROM PS_AETEMPTBLMGR WHERE OPRID = '<>' AND RUN_CNTL_ID = '<>'

DELETE FROM PS_AE_REQUEST WHERE OPRID = '<>' AND RUN_CNTL_ID = '<>'
DELETE FROM PS_AE_REQUEST_OPT WHERE OPRID = '<>' AND RUN_CNTL_ID = '<>'
DELETE FROM PS_PGG_SERVICE_AET WHERE OPRID = '<>' AND RUN_CNTL_ID = '<>'
DELETE FROM PS_RP_POSTING_AET WHERE OPRID = '<>' AND RUN_CNTL_ID = '<>'

(+)DELETE FROM PS_PRCSRUNCNTL WHERE OPRID = '<>' AND RUN_CNTL_ID = '<>'
(+)DELETE FROM PS_POSTING_REQUEST WHERE OPRID = '<>' AND RUN_CNTL_ID = '<>'
('+' exclude if you wish to retain Run Control, but reset AR Update)
(* where '<>' is your actual OPRID and actual Run Control ID)

Recovery Option 8 [Cleanup of Temporary Tables]

[Optional] Clean up the abended Process Instance TAO tables. The complete list exists on PS_AETEMPTBLMGR keyed by the PROCESS_INSTANCE and RUN_CNTL_ID. Delete from all _TAO tables EXCEPT for PS_PGG_SERVICE_TAO, which should NEVER be deleted. Refer to above Step 3 '3. Verify the Abended PI# Run Control ID and TAO'

SELECT * FROM PS_AETEMPTBLMGR

Recovery Option 9 [Apply Known Resolution]

The abend could be the result of a code error, hence no other fix would ultimately work in preventing it in the future. In this situation the abended process will be recovered by the relevant Option above, any corresponding changes to setup or data if/as needed, the Resolution (whether standalone fix or delivered in a Bundle) applied and then the process re-run.

Peoplecode Tips

DYNAMIC PROMPTS

1. Create a dynamic view.
2. In the required event PeopleCode, do the following
   &SQL1 = "select field1 from Record1 where Eff_date = "|pagefield.effdt;
   &getfield(Record.Fieldname).sqltext = &SQL1
   where getfield will instantiate the field for which the dynamic view is used as a prompt record.

TRANSFER TO ANOTHER PAGE

Local Record &TXFR_ADD_KEYS;

&TXFR_ADD_KEYS = CreateRecord(Record.VCHR_SRCH_VW1);

&TXFR_ADD_KEYS.GetField(Field.BUSINESS_UNIT).Value = PYMNT_ADVICE.BUSINESS_UNIT;

&TXFR_ADD_KEYS.GetField(Field.VOUCHER_ID).Value = PYMNT_ADVICE.PAY_DOC_ID;

Transfer (False, MenuName.CREATE_PAYMENTS, BarName.INQUIRE, ItemName.VOUCHER_INQUIRY, Panel.VOUCHER_INQUIRY, "U", &TXFR_ADD_KEYS);

*******************************************************************************

Transfer to Process Monitor & Application Message Monitor

*******************************************************************************

Transfer ( True, MenuName.PROCESSMONITOR, BarName."INQUIRE", ItemName."PROCESSMONITOR", Panel."PMN_PRCSLIST", "U");

Transfer ( True, MenuName.APPMSGMONITOR, BarName."USE", ItemName."APPMSGMONITOR", Panel."AMM_OVERVIEW", "U");