What are all the Various SQCs available ?

What are all the various SQCs available for use? Some of the times I end up writing my own procedures(absolute waste of time) due to lack of knowledge on various SQCs. Here is the list of some of the SQCs available. 

1. Askftd.sqc - This asks from/thru date for reporting of heading
2. Askaod.sqc - Procedure to call is Ask-As-of-Date
3. CurDtTim.sqc - Gets the Current Date Time from System
4. DateTime.sqc - Convert date from system format calling InitDate-Time procedure to set format.
5. DateMath.sqc - Converts Native Date format to YYYY-MM-DD.
6. Eoj.sqc - determines End of job for an SQR report.
7. FSHdg01.sqc, FSHdg04.sqc - for Standard Headers for PS Financials.
8. HRHdg01.sqc, HRHdg04.sqc - for Standard Headers for PS HCM.
9. Number.sqc     - Provides Number related Functions
10. Opsys.sqc - Operating system settings.
11. PrcsApi.Sqc - Used in Process Scheduler SQR Programs, to get Run control parameters 
12. RDBMS.sqc - Database Settings. These files uses #DEFINE command to set the variables.
13. ReadXlat.sqc - Read the Translate Table for the desired values (Very Important)
14. Reset.sqc - Used for Footing Section that prints ‘End-of-Report’.
15. SetEnv.sqc - Sets Environment for Country, Printer-Paper, Language, Date,  Platform specific parameters, It also contains Opsys.sqc, RDBMS.sqc
16. Setup01.sqc - This is generally called from Begin-Setup section for Portrait Size 
17. Setup02.sqc - This is generally called from Begin-Setup section for Landscape Size
18. STDHDGTR.sqc  - Performs Language Conversion
19. STDHDG01.sqc - Provides Standard heading for language conversion
20. StdApi.sqc - Gets Std API calls 
21. Trancntrl.sqc - Purpose is to COMMIT, ROLLBACK etc. across DB platforms
22. Convertcurrency.sqc - To convert currencies
23. AllMaxes.max - It is used for dynamic memory allocations before the execution of SQR.

Read More»

One PeopleSoft Audit record for multiple tables

Is it possible to have one single audit record for multiple PeopleSoft records(tables)?
Yes, today I came to know a feature that a single audit record can be used to audit multiple PeopleSoft records. As I write about this, I have also specified all about a PeopleSoft Audit record.

The easiest way to create an audit record is to open the record definition of the base record that you wish to audit. Remove the all edit and key attributes from the newly saved audit record. Also remove any attributes such as PARENT records, Query Security Records, and PeopleCode must be removed for the audit record. The audit record must be saved with the prefix AUDIT_.

To the top of this new audit record the following key fields have to be added in the same order and they should also be made as key fields.

• AUDIT_OPRID
• AUDIT_STAMP
• AUDIT_ACTN
• AUDIT_RECNAME**

In most cases we include only AUDIT_OPRID, AUDIT_STAMP, and AUDIT_ACTN. The AUDIT_STAMP must be given the attribute AUTOUPDATE attribute must be checked for the field AUDIT_STAMP. AUDIT_STAMP field must also be made a descending key field.

• AUDIT_OPRID - Identifies the user who caused the system to trigger the audits
• AUDIT_STAMP -Identifies the date and time the audit was triggered 
• AUDIT_ACTN - Indicates the type of action that the system audited.

         Possible audit actions include:

• A: Row inserted. 
• D: Row deleted.
• C: Row changed (updated), but no key fields changed. The system writes old values to the audit table.
• K: Row changed (updated), and at least one key field changed. The system writes old values to the audit table.
• N: Row changed (updated), and at least one key field changed. The system writes new values to the audit table.

** - AUDIT_RECNAME must be added only if the audit table needs to audit more than one record definition.

The audit table need not have to include all the columns of the base table. In fact, for performance reasons, it’s best to only to include those fields in the audit record that are deemed to be changed.

In the record to be audited go to Record Properties -> Record Audit, specify the below options

Record Name – Specify the user-defined audit record.

Specify the required Audit Options – following are the audit options to choose for auditing the record.

• Add - Inserts an audit table row whenever a new row is added to the table underlying this record definition.
• Change - Inserts one or two audit table rows whenever a row is changed on the table underlying this record definition.
• Selective - Inserts one or two audit table rows whenever a field that is also included in the record definition for the audit table is changed.
• Delete - Inserts an audit table row whenever a row is deleted from the table underlying this record definition.

Everytime the record is being inserted, deleted, updated a corresponding row gets inserted in the audit table as per the options specified.

Read More»

Sample code to read a file using file layouts and to insert data into a record

Sample code to read a file using file layouts and to insert data read into a record.

Local File &MYFILE;
Local Record &REC;
Local array of string &ARRAY;
Local string &FILE_DIRECTORY, &FileName;

&FileName = "MY_FILE_NAME.txt";
&FILE_DIRTORY = "/MYDIRECTORY/"
/*open file for reading*/
&MYFILE = GetFile(&FILE_DIRECTORY &FileName, "R", %FilePath_Absolute);
/*create record object*/
&REC = CreateRecord(Record.MY_RECORD);
&ARRAY = CreateArrayRept("", 0);
/*check if file is open*/
If &MYFILE.IsOpen Then
/*The SetFileLayout method is a file layout method. It associates a specific file layout definition with the file object executing this method, providing easy access to rowset data.(PeopleBooks)*/
If &MYFILE.SetFileLayout(FileLayout.FILE_LAYOUT_NAME) Then
/*read line into &STRING*/
While &MYFILE.ReadLine(&STRING);
&ARRAY = Split(&STRING, ",");
For &I = 1 To &REC.FieldCount
&REC.GetField(&I).Value = RTrim(LTrim(&ARRAY [&I]));
End-For;
/* do additional processing here for converting values */
&REC.Insert();
/*count rows inserted into record*/
&COUNT = &COUNT + 1;
End-While;
Else
/* do error processing - filelayout not correct */
End-If;
Else
/* do error processing - file not open */
End-If;

Read More»

How Interactive and Deferred mode works in PeopleSoft?

Quite often many of us get confused on Interactive and Deferred mode settings as to whether the component level setting takes precedence or the record/field setting in a page takes precedence.
PeopleSoft interactive processing mode follows a different approach compared to deferred mode processing.

How to set interactive mode or deferred mode in PeopleSoft?
Simple isn't it? Modes can be chosen at three levels - Rec/Field in a page, Page and Component. Deferred processing is the default mode at all - the field, page, and component levels.

How to make a field or a page in a component work in Deferred Mode? 
For deferred mode processing to work for a field in a page, Deferred Mode must be the only permissible mode at all levels - Rec/Field, Page and Component. Selecting deferred mode at each of these levels will allow a field in the component to run in deferred mode. For a page to run in deferred mode, deferred mode must be selected at the page and component levels.

How to make a field or a page in a component work in Interactive Mode? 
If a component is using deferred-processing mode, pages and fields within that component can be made interactive on an individual basis. However, if a component is interactive, then all pages and fields on that component are interactive as well.

Below is the table which shows the behavior of a  record/field with respect to the various options chosen at all the three levels - rec/field, page and component.

Component	Page	Record.Field	Resulting Behavior
Interactive	Interactive	Interactive	Interactive
Interactive	Interactive	Deferred	Interactive
Interactive	Deferred	Interactive	Interactive
Interactive	Deferred	Deferred	Interactive
Deferred	Interactive	Interactive	Interactive
Deferred	Interactive	Deferred	Interactive
Deferred	Deferred	Interactive	Interactive
Deferred	Deferred	Deferred	Deferred

Table1: Interactive mode vs Deferred mode in PeopleSoft Component, Page and Rec/Field

Different Settings and results:
In a nut shell, if interactive mode is chosen at the highest level it gets inherited up to the lowest level. When deferred mode is chosen, the individual settings of the lower level objects will be the resulting behavior.

1. Choosing a component interactive makes all objects in the component interactive irrespective of the individual settings.
2. Choosing a page interactive makes all objects in that page interactive irrespective of the individual settings.
3. Choosing a rec/field in a page as interactive then that field becomes interactive irrespective of the settings at page and component levels.
4. If deferred mode has to be chosen then deferred mode must be chosen at all levels. 

The following table from Peoplebook shows the events that cause the PeopleTools run-time environment to process logic on the application server when operating in deferred or interactive processing mode:

Event	Deferred Processing	Interactive Processing
Entering data in fields with PeopleCode (for example, FieldEdit and FieldChange) associated with them.	NA	X
Entering data in fields that have prompt table edits.	NA	X
Entering data in fields that have related displays.	NA	X
Inserting a row or series of rows in a grid or scroll area.	X	X
Deleting a row from a grid or scroll area.	X	X
Using the grid or scroll area controls to move forward or back.	X	X
Selecting another page tab.	X	X
Selecting another grid tab.	X	X
Expanding or collapsing a collapsible section.	X	X
Clicking any button or icon on the page.	X	X
Clicking any link on the page.	X	X
Clicking the Refresh toolbar button.	X	X
Using the Refresh hot key.	X	X

 Table2: Triggering Server Processing

Read More»

What is the difference between differed processing mode and interactive mode?

What is the difference between differed processing mode and interactive mode?

Answer: Every time you access and navigate through the fields on a page in PeopleSoft there are events such as Field Edit, Field Insert, etc... that get triggered. For example if I am on a page and enter a letter in a numeric field, the field edit peoplecode can fire (to check if the value is valid) when I tab out of that field. When you turn Deferred processing on, the field edit peoplecode will be ignored until you press the save button on the page. Interactive mode means that the peoplecode will fire immediately when you tab out of the field. Some pages have intensive peoplecode field edit's on them and turning deferred processing on can save a lot of time when you are doing data entry.

Read More»

How to Convert a Legacy System Data into Peoplesoft

Method 1

Method 2



























Follow the steps:

1) Format the Input file into a particular format

2)Create a Temp Table to store the data, we usually call it as Staging Table

3)Create a File layout to store it into the staging Table.

4)Use necessary Execute Edit or Component Interface Validation to Validate the data.

Read More»