Tuesday, 8 May 2012

How to build an efficient interface using AE or SQR

While building an Inbound/Outbound interface, most of the time we may be in dilemma whether to use Application Engine (AE) or SQR. Here are the tips to build an efficient interface using either AE or SQR.
If we use Application Engine, below points are to be taken into account
  • If the interface is INBOUND, its better to go for Application Engine
  • If the program fails, we can make use of Restart Control to restart the program from the position where it abends
  • PeopleSoft – Business Process related Validation will be taken care automatically when we go for AE/CI.
  • Parallel Processing can be easily achieved using AE. This can be done by using Temporary Tables and State Records.
  • Reusability of SQLs is very much easy when we go for AE.
  • Bulk Insertion of data and Set Processing of data can be easily done in an AE
  • COMMIT levels can be maintained either at SECTION or STEP levels
  • Generation of Reports using File Layout, Workflow related batch interface can be easily developed using an AE
  • XML related processing is quiet easy when we go for AE
  • When we go for Upgrade of the Current PS System, Retrofitting of AE is done by easily identifying the impacted objects.
If we are going to use SQR, below points can be taken into account to build an interface
  • Formatting of Reports is very much easy in SQR using Begin-Header, Begin-Footer and Begin-Setup commands.
  • If we are going to consider the Performance of the Interface Program, its better to go for SQR.
  • Better Performance can be achieved by using Load-Lookup Arrays and Dynamic SQL
  • Reusability of SQLs can be achieved by creating either generic Procedures or SQCs

PeopleCode to Create XML and XSD file

The below code is useful to create XML and XSD file.  Create Application engine program with below code and run the AE program in App designer to get a file in local. You can get the same in server without specifying any path and give it as relative path instead of file absolute.
PSXP_XMLGEN application package used to create xml file. You can multiple parent and child records. This file is used for data import or export programs.
/*Create Rowsets*/
&RS_ADR = CreateRowset(Record.EMPLOYEES);
&RS_PER = CreateRowset(Record.PERSON, &RS_ADR);
/*Fill Parent*/
&RS_PER.fILL(“WHERE EMPLID = ‘11902′”);
/*Fill Child*/
For &i = 1 To &RS_PER.Activerowcount
&row = &RS_PER.Getrow(&i);
&rs = &row.Getrowset(Scroll.EMPLOYEES);
&rs.fill(“Where emplid = :1″, &row.PERSON.emplid.value);
/* Create XSD */
&rds = create PSXP_XMLGEN:RowSetDS();
&myschema = &rds.Getxsdschema(&RS_PER);
&f1 = GetFile(“C:\temp\rpt01.xsd”, “w”, %FilePath_Absolute);
/*Create Sample XML File */
&myXMLfile = &rds.GetXMLData(&RS_PER, “C:\temp\rpt01.xsd”);
&f2 = GetFile(“C:\temp\rpt01.xml”, “w”, %FilePath_Absolute);

Friday, 6 April 2012

SQR Interview Questions

1. What are the sections and paragraphs available?
There are five sections and three paragraphs available in SQR programming.
The sections include
a.       Begin-setup End-setup
b.      Begin-program End-program
c.        Begin-procedure End-procedure
d.      Begin-heading End-heading
e.      Begin-footing End-footing
And the paragraphs include
a.       Begin-select paragraph
b.      Begin-SQL paragraph
c.       Begin-document paragraph
2. Is it possible to increase the array size once defined?
No, it is not possible to increase the Array size once defined
3. What is Load-lookup? Give the Syntax for Load-Lookup?
The LOAD-LOOKUP command defines an array containing a set of keys and values and loads it into memory.
With LOAD-LOOKUP, you can reduce the number of tables that are joined in one SELECT.
Use this command in conjunction with one or more LOOKUP commands.
Syntax:   begin-setup
4. Can you Call a SQR from another SQR? How?
We can call one SQR program from another SQR program using Callsystem
But this is possible only in UNIX Operating system.
5. Briefly explain Most commonly used SQC’s in SQR programs?
Some of the most commonly used SQC’s are
STDAPI.SQC : This Is Process Scheduler Interface which is used to initiate and terminate some
                                      field values such as Process_instance & Run_cntl_id
SETENV.SQC : This is used to set the Default Environment based on the Database such Printer
                                      Type, Paper Size, Date formats Etc….
NUMBER.SQC :  This file contains generalized routines to format numbers.
DATETIME.SQC :  This file contains generalized routines to format dates and times.
RESET.SQC :  This is an important Footing SQC
6.  What are the commands used in file handling of SQR?
7. What are On-break parameters?
ON-BREAK causes the specified action to perform in a tabular report when the value of a field changes.  The default action prints the field only when its value changes (PRINT=CHANGE)  Mainly used for to avoid Redundancy of Printing on a page.
8. How can you Debug SQR programs?
We can Debug SQR using Debugging Commands such as
1. #IF  DEBUGxxx
2. DISPLAY and
9. What are diff b/w SQR and Appln Engine ?
                1. App engine is belongs to PeopleSoft whereas SQR is third party application.
                2. App engine are easy to maintain from support prospective as it's reside inside PeopleSoft (PS) applications. For SQR we need to separately take care as its file server objects and hence difficult to maintain & upgrade.
                3. App engine are very systematic to Develop and Debug. SQR are difficult debug.
                4. SQR is mainly used to create Reports. By App Engine we can not create reports.
                5. AE flow is easy to read and understand, while SQR is not that easy.
                6. SetProcessing the major difference between Application Engine and SQR. There is no such facility in SQR which  makes Application Engine a winner for data validations.                                                           
10.What are different types of variables in SQR?
1.       Column Variables
2.       List Variables
3.       System Variables
4.       Document Variables
5.       Substitution Variables
6.       User Defined Variables
11. Is there any read-only variable in SQR?
Column Variables & System Variables are Read-Only Variables.
12. How to pass Inputs for SQR program while running from windows?
Using ASK & INPUT Commands.
13. How to pass Inputs to the SQR while running through the Process Scheduler?
Using  Procedures  & SQC’s to access data on Run Control Page (Run Control Fields).
14. What is the purpose of Stdapi.SQC
This is a Process Scheduler Interface SQC which is used to initiate and terminate some field values such as Process_instance & Run_cntl_id.
15. What is the SQT file? What are the advantages of SQT files?
SQT’ file is nothing but resultant file after the compilation (i.e.) Runtime File. By using this SQT file , we can increase the Performance of the program by reducing the Compilation Time.
16. What is the SQC used to read data from translate table?
‘READXLAT.SQC’ is the SQC used to read data from Translate Table.
17. Which section is mandatory in an SQR program?
‘BEGIN-PROGRAM’ Section is Mandatory for an SQR program.
18. How will you display an image in SQR?
19. What is use of SETENV.SQC?
This is used to set the Default Environment based on the Database such Printer Type, Paper Size, Date formats Etc….
20.How will you perform ERROR handling in SQR?
We can do Error handling in SQR using some command line flags such as –O, -L,-ZMF,-XMB and
Using Error handling commands such as
ON-ERROR = Skip/Warn/Stop (for Compile time errors)
ON-ERROR = Procedure Name (for execution stage errors).
21. How many ways of performance tuning are there in SQR?
ü  Using ARRAYS
ü  Using SQT Files
ü  Using –Bnn Command line Flag
ü  Running on BATCH SERVER
ü  Using Proper Programming Logic in SQR such as Using BREAK statement in EVALUATE Command.
ü  Proper SQL tuning such as using Sub queries instead of Joins.
22. Can you call procedure in oracle from SQR ?  How?
To call a database procedure using Begin-select paragraph, the syntax is as follows:
<Procedure name> [(report arguments)]
from Dual
To call from Begin-SQL, the syntax will be
23.Can you connect more than one database using one SQR?
24. Program flow of SQR ?
When  SQR Program starts executing. There are two stages in the Program Flow.
1) Compile stage     2) Execution Stage
à Compile Stage :
All the Preprocessor directives are compiled ( which starts with #include).
Ex: All the SQC are run.
Check for the syntax errors for the conditions.
Ex: if, for loop, while loop are properly ended with the respective syntax.
Allocates memory structure , if you are using the Arrays and load look up .
à Execution stage:
Starts interpreting the code line by line.
Check for the begin -program body.
Begin -heading.
Begin- footer.
setup sectionàheading section àfooting section àprogram section àprocedure section

25. What are the steps required to run the SQR from PeopleSoft environment?
Three steps we have to fallow to run the SQR program from the PeopleSoft environment (Process
These steps include
a. Making the SQR program API aware by calling two procedures (stdapi-init, stdapi-term) from
     Begin-program section and including the Stdapi.SQC in the bottom of the program.
b. Create new run control table or using existing run control table and creating run control page,
    component  and register the component.
c. Giving the permission to the user by giving process groups.
26. How to pass command line flags to a SQR report running through process scheduler?
Using override options in the process definition in the Process Scheduler in the People Tools.
27. what is hierarchy in Arrays in SQR ? 
There are 3 steps
 First we have to  Initiate Array  & then  Process Array  and  Print Array .
28. How does PeopleSoft Process monitor knows that the process (Say SQR report) is at what stage such as initiated, in process, completed etc?
From the Field Value of ‘RUNSTATUSDESCR’ field from ‘PS_PMN_PRCSLIST’ table.
29. How can you send emails using SQR ?
using  call system  command.
30. How to refer a global variable in local procedure?
After special character (ex.#,&) we use underscore(_) before that variable name.
31. What is -Bnn flag?
This is used to specify the number of rows that are to be taken from Dbase to the Buffer because
default the system takes 10 rows at a time.
32. What is -Tnn flag?
This is mainly used for testing purpose. We can restrict the system to generate only a specific
number of pages instead of generating all pages for testing. This is used in Windows
33. What is -RS & -RT Flags?
RS is used to generate SPF file ie; stopping the program after the comilation. RT is used to run
the SPF file .This is also one kind of Performance increasing technique by saving the compilation time.
34. What is -debug command line flag?
This is used for Debugging in SQR.
35. How to place an corporate logo in SQR?
Using “Declare Image” command
36. what are the debugging commands in SQR?
Show & Display are debugging commands
37.  what is the difference between them?
Show is used to display more than one variable at a time.
Display is used to display only one variable at a time.

Component Interface Interview Questions

1. What is Component Interface meant for?
It enables synchronous access to a PeopleSoft component from another application (third party application like Java,C++,COM etc) including all business rules and data.
2.What are the main elements in the Component Interface ?
Main elements of Component Interface:
ü  Component Interface name
ü  Keys
ü  Properties and collections
ü  Methods
3.What are the standard properties when a Component Interface is created?
Standard properties of CI are:
ü  Interactive mode
ü  GetHistoryItem
ü  EditHistoryItem
4. Can we map more than one component at a time from the CI ?
No, A Component Interface can be mapped to only one component, but one component can be mapped to multiple CI’s.
5. Explain what  are the various steps that describes the PeopleCode logic while implementing a Component Interface?
1.       Establish a user session
2.       Get the Component Interface
3.       Populate the Create Keys
4.       CREATE an Instance of the CI
5.       Populate the required fields
6.       Save the CI .
6.What is method? What are the different types of method?
A method is an object that performs a very specific function on a CI  at runtime.
                Types Methods are Standard methods and User-defined methods
Standard methods: - Automatically generated upon the creation of a new component Interface in Application
7. Which KEYS are mapped as find keys for a Component Interface?
                Search key and alternate search key.
8. How do you login in correction mode in the Component Interface?
Get History Items and Edit History items property to should be set to true.
9. If you are PS developer then in what scenarios Component Interface will have advantages over component?
A component exposes your pages to PeopleSoft only. Moreover it helps us to define common properties for a set of related pages.
A Component Interface is a set of application programming interfaces (APIs) that you can use to access and modify PeopleSoft database information programmatically.
PeopleSoft Component Interfaces expose a PeopleSoft component (a set of pages grouped for a business purpose) for synchronous access from another application (PeopleCode, Java, C/C++, or Component Object Model [COM]).
10. What happens when a Component Interface is created on component that has add Action enabled?
Create method and create keys are created. The create method will not be present if the Add mode for the Component is not enabled.
11. Can we create get and find keys manually ?
No, These keys are created based on the search record definition for the underlying component.

PeopleCode Interview Questions

1. Where PeopleCode get stored?
 In Database Server (PSPCMPROG table)
2. Suppose if there is same component X in two different menus, menu1 and menu2 and if you want to assign a different search record for each menu, then which PeopleCode function do you use and where do u write the code?
Instead of using PeopleCode, Directly we can assign the search record at the menu level which will override the search Record specified at the component level.
 3. What is Component Processor?
 The Component Processor is the PeopleTools runtime engine that controls processing of an application from the time that a user requests a component from an application menu until the database is updated and processing of the component is complete.
4. Explain How to send email to the all employee from the PeopleSoft application when a certain event is true? What is the event used to trigger?
Create a workflow. In the workflow, use a query with active employees and their email ids as inputs. On the successful occurrence of the event, trigger this workflow using TriggerBusinessEvent.
5. Explain the advantage and disadvantage of SQLExec in PeopleCode?
By using SQLExec function we can do the manipulation to the database. We can write insert, update and delete SQL commands. But drawback while selecting the data using SQLExec, it will return only one at a time another drawback, if the name of the records changed, then you have to make the changes in the code as the query will in the quotes inside SQLExec. 
6. Tell me where can we write the PeopleCode?
1.       Record field level
2.       Page people code
3.       Component level people code
4.       Component record people code
5.       Component record field people code
6.       menu item people code
7.       Application engine people code
8.       Component interface people code
9.       Messaging people code
10.   Application package people code 
7. Tell me is there any function exist in PeopleCode which stops the processing of whole component?
It is "Error" Function. Use the Error function in FieldEdit or SaveEdit PeopleCode to stop whole processing and display an error message. It is distinct from Warning, which displays a warning message, but does not stop processing. Error is also used in RowDelete and RowSelect PeopleCode events. 
8. Tell me what is the difference between SQL Object and SQLExec?
SQL Object is a people tool definition which can be created using app designer as well as using PeopleCode command CREATESQL( ). It is mainly used for reusability of the SQL statements. So whenever this statement is required no need to hard code, just create an SQL object of the same and use where ever it is required.
SQLExec is a PeopleSoft delivered function used to retrieve only one row and u can update multiple rows of data. It directly interacts with the database.
9. Tell me what is the diff between component level PeopleCode and record level PeopleCode?
Record level PeopleCode :
The PeopleCode written on the Record level PeopleCode will effect all the Components in which that particular record is used.
Component Level PeopleCode :
The component level PeopleCode works only for that particular component and if any record in the component is referenced by component level PeopleCode then it would not effect any other component where same record is used.
Sequence: first Record level PeopleCode will fire and then Component level PeopleCode.
10. Can you explain about component Buffer?
                When you open any page in a component, the system retrieves all of the data records for the entire component and stores them in one set of record buffers, organized by scroll level and then by page level. PeopleCode frequently must refer to data in the Component Buffer, that is, the area in memory that stores data for the currently active component.
There are two methods of specifying a piece of data in the Component Buffer from within PeopleCode
-------- Contextual references, which refer to data relative to the location of the currently executing PeopleCode program.
-------- References using scroll path syntax, which provide a complete, or absolute, path through the Component Buffer to the referenced component.
The component buffer contains current active component data it is temporary memory area. 
12. What is the sequence of events fired when a page is saved.
13. How to store output of SQL query in a variable using PeopleCode?
Using SQLExec() function
                SQLExec(“select EMPLID, NAME from PS_PERSONAL_DATA”, &emplid, &name);
14. What is an array in people code?
An Array is a collection of data storage locations, each of which holds the same type of data.
The maximum depth of a PeopleCode array is 15 dimensions. Push and UnShift are the functions of the array used to add the elements into the array one from the end of the array and one from the    beginning. Pop is a function of array used to select and delete an element from the end of the array.
15. PeopleCode attached with Push Button can be associated with which events?
                Field Edit, Field Change
16. Which PeopleCode debugging tool automatically converts values of any data type?
17. What is Differed processing and its advantage?
                Postpones some user actions to reduce the number of trips to the database.
                Advantages:       1) Reduces network traffic
                                                2) Increase performance
18. What is Rowset?
Rowset is data structure representing the hierarchical data. Rowset consists of set of rows, where each row consists of set of records and child Rowsets. We can retrieve data from data buffer and component buffer using Rowset.
19. Difference between SavePreChange and SavePostChange?
SavePreChange is fired when the component buffer is not cleared. It provides one final opportunity to manipulate data before the component processor updates the DB where as SavePostChange is fired when the buffer is cleared (i.e., component processor updates the DB) and before the commit is issued by the database.
20. How to change prompt table dynamically?
Using PeopleCode function “%EditTable”
21. What are Think time functions?
Think-time functions suspend the processing of the whole of component either until the user has taken some action (such as clicking a button in message box) or until an external process has run to completion.
Ex:  DoCancel, DoModal, DoComponent, WinMessage, WinMessageBox etc..
22. What is the difference between RowInit and RowInsert?
RowInit event fires the first time the Component process encounters a row of data.
RowInsert event fires when the end-user adds a row of data. Don’t put PeopleCode in RowInsert that is already exists in RowInit, because a RowInit always fires before the RowInsert event, which will cause your code to be run twice.

Application engine Interview Questions

1. What is  application engine ?
                Application Engine programs are PeopleSoft's batch processing technology. They are developed in application designer and consist of blocks of PeopleCode and SQL. Application engine programs can also use application classes, Component Interfaces, XML publisher reports, and call SQRs and COBOLs through PeopleCode.
2. What is application library?
                Application library is a program that contains collection of callable sections.  We can’t define application library with standalone program.
3. What are the Different types of Application Engine?
                Standard: Standard entry-point program.
                Upgrade Only: Used by PeopleSoft Upgrade utilities only.
                Import Only: Used by PeopleSoft Import utilities only
                Daemon Only: Use for daemon type programs.
                Transform Only: Support for XSLT Transform programs.
4.What is the difference between the %select and %selectinit meta SQL functions?
                %select        : If any values have not selected then previous values will be there
                %selectinit   : If any value have not selected then previous value reinitiate to null.
 5.What are the different types of  temporary tables in application engine program?
                Dedicated - They have lock management. (continue)
                Undedicated- There is no lock management on these tables. (Abort)
6. what are the different types of variables in the Application Engine people code?
Local variable – These are available for the duration of the program in which they are declared.
Global / Component – These variables are available while the Application Engine program is running. They are saved at commits and checkpoints, So they can be used for restarts. Component variables are same as Global incase of the AE.
7. What are the Different ways pass data between the steps?
                State records – One row can be passed and can have many state records.
                Component/Global People code variables
                Temporary records – Multiple rows of data can be passed.
8. What is Prerequisite for the State record?
                i)Should be either Derived work record Or SQL Table.
                ii)Name of the record must end in AET
                iii)PROCESS_INSTANCE must be a key.
                iv)Any data types except character or numeric must not be required fields.
Note: - No People code fires on an Application Engine state record.
No Validation of translate values or Prompt tables are done.
9.What is the difference b/w %select, %bind?
                %Select—used for inserting
                %Bind----- used for retrieving.
10.How do you execute Application Engine through Push Button?
                a.CreateProcessRequest () , Schedule ()
11. What is the syntax for CallAppEngine?
CallAppEngine (applid [, state record]);
12. What are the 3 trace parameters you can pass to your psae.exe?
13. Which Trace option is the best place to start for general performance information?
- TRACE 384 – mostly used trace value.
14. What is the difference between a Trace value and Trace parameters?
A Trace parameter determines which type of trace is turned on.
A Trace parameter determines what type of data is recorded in your trace files (s).
15. What are the 3 common ways to pass a trace parameter and value to your program psae.exe?
Configuration manager, Process Definition, Command prompt.
16.What is the difference between the exit(0),exit(1) when we are using this function in AE?
Exit(1) causes immediate termination of  a PeopleCode program. We use this parameter to rollback database changes.
Exit(0) causes immediate termination of a PeopleCode program but it doesn’t make rollback in the database.
17. What is Set Processing? 
Set Processing uses SQL to process groups or sets of rows at one time rather than processing each row individually. With set processing, you only select those rows that meet the filtering criteria and then run the rule once again all the affected rows. (With row by row processing you following a repetitive loop that selects a row, determines if it meets a given criteria, if so, apply rule x to row Update row, commit.)

18. Advantages of Set Processing?
Improved Performance, Minimized SQL Overhead, Easy Maintenance, Leveraging the RDBMS
19. Why call section and SQL are mutually exclusive?
Both CallSection and SQL are used to perform DML operations, To avoid Locking we will use either SQL or CallSection.
20. What is the use of temporary records and state record in application engine ?
Temporary Tables: Used to store the immediate values during the processing of the AE.
Help in batch processing by creating multiple instances and there by facilitating parallel processing.
State Records: Used to pass on values between sections/steps/programs.
21.How can you integrate workflow with A.E?
A.E programs triggers business events indirectly, by passing their queries to CI. If the component has associated with workflow, A business process is  trigger when the CI saves the  page.
22.What are the PeopleCode events used for CallAppEngine function.
SavePreChange ,SavePostChange, WorkFlow , FieldChange.
23.What is the Order of Action Events?
Do When ,Do while , Do select  ,People Code  , SQL/Call Section, Log-Message, Do Until.
24.What are the prerequisites for Temporary Record ?
Process instance as  a key and  name should end with _TAO
25. What are different types Do Select ?

Application Designer Interview Questions

1. What are Popup Menu types available?
2. What views are available in Application Designer project workspace?
                Development and Upgrade
3. If you change the long name of translate field what object to be altered?
                Page definition
4 .Can you place Sub page into Grid? If yes How?
                yes, we can insert subpage using insert subpage. After insert subpage into main page, drag the subpage into the grid. When we save the page we are successfully able to save the page showing that we can insert a subpage into a grid.
5.Limitations of translate table?
ü  Field type should be character
ü  Minimum filed length should be 1 to 4 characters
ü  Field value should be small and static 
Set control table default value is Business unit. It contains record groups and its default value is SetID. So it is used to access data from different Business unit.
7. Define auditing in PS? Types of auditing and explain each?
There is two types of audits available PS. Record level audit and record field level Audit.
8. Which PS object can’t be blocked by developers as a part of change control locking system?
Process Definition
9. Define Scroll? How many types of scrolls are there?
A Scroll is used to enter multiple rows and retrieve multiple row data. 
1. Nested scrolls – For each repeating data and for each entry with other set of repeating set of data is called a nested scroll. Maximum 3 nested levels are allowed. The nested scrolls share same high level keys.
2. Independent Scrolls: At each scroll level a maximum of 15 screens can be included.
10. What is mutually exclusive build execute option with the alter build option?
                Execute SQL now
11. How to view report from application designer?
                fileàreport from fileàview report
12. What is registering a component?
                We will add the component to menu and give permissions and security.
13. How to run AE program from Application Designer?
                open AEàeditàRun Program
14. Difference between search record and add search record?
                No diff b/n both the records, but add search record should be used in add mode.
15. Define component buffer and data buffer?
Component buffer consists of active component data, but data buffer contains multiple component data and other PeopleSoft applications data also.
16. What is the use of owner id in any PS object general properties?
                It is useful to restrict the PS object to the certain module.
17. What is the use of default page control in record field properties?
Specify the default appearance of a field as it appears on the page that Corresponds with the record field that you are creating.
18. How to set 3tier execution location of a component?
Component properties--àuse-à3tier execution location
19. When registering a component to menu, menu bar name is not mandatory (true/false)?
20. What is default grid occur level?
21. How do you bring advanced search page before normal search page?
                Component propertiesàinternetàdefault search/look up typeàbasic or advanced.
22. What are the numbers of sub pages and secondary pages that can be created in one level?
multiple number of subpages can be created in one scroll level but only one secondary page can created in one scroll level using push/hyperlink.
23. How to combine two projects?
                fileàmerge projectsàInsert project nameàinsert
24. Define update ids in project properties?
                These are useful, when change project option is activated.
25. The default processing mode of the component?
                Deferred Mode
26. Define Record?
                A record definition is definition of what your underlying SQL database tables will look like, and how they will process data.
27. Record types that are not going to store in to Database/types that can't be build?
                Derived/work record, dynamic view and Sub record.
28. “Approval rule set” is not PeopleSoft object? (true/false)
29. How to disable the appearance of prompt button on a page?
                Page field propertiesàrecordàdisplay optionsàshow prompt button (UN check)
30.  What happened when select “build script file” option?
                Table is not going to create into database but it’s just creating Script file.