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

                           load-lookup
                           name=prods
                           table=products
                           key=product_code
                           return_value=description

               end-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?

READ, WRITE, OPEN, CLOSE

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

    #END-IF

2. DISPLAY and

3. SHOW

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?

Using ‘DECLARE-IMAGE ,  END-DECLARE’ command.
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 LOADF-LOOKUP & LOOKUP

ü  Using ARRAYS

ü  Using MULTIPE REPORTS

ü  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:
Begin-select
<Procedure name> [(report arguments)]
from Dual
End-select

To call from Begin-SQL, the syntax will be
Begin-SQL
<procedure-name>;;
End-SQL

23.Can you connect more than one database using one SQR?

Yes.

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

Scheduler).

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
Environment.

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.

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.

SaveEdit--->SavePreChange--->Workflow--->SavePostChange

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?

                WinMessage

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 Designer Interview Questions

1. What are Popup Menu types available?
                1.Transfer
                2.PeopleCode
                3.Separator

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 

6. What is SET CONTROL TABLE?

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)?

                False 

20. What is default grid occur level?

                one

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)

                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.

PeopleSoft Security