Categories
PeopleSoft SQR FAQ

PeopleSoft SQR Frequently Asked Question

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
Begin-select paragraph.
Begin-SQL paragraph.
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 Call.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
1.STDAPI.sqc : This Is Process Scheduler Interface which is used to initiate and terminate some field values such as Process_instance & Run_cntl_id
2.SETENV.sqc : This is used to set the Default Environment based on the Database such Printer Type, Paper Size,Date formats Etc….
3.NUMBER.sqc : This file contains generalized routines to format numbers.
4.DATETIME.sqc : This file contains generalized routines to format dates and times.
6.RESET.sqc : This is an important Footing SQc.

     6. What are the commands used in filehandling of sqr?
Using File Handling we can Import. Steps are
1. Opening a File Using ‘OPEN FOR-READING’ command.
1. Reading Data From File Using ‘READ’ command.
Writing Data into Table Using ‘BEGIN-SQL’ paragraph & ‘INSERT’ command

      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 Redundancy of Printing on a page.

     8. How can you Debug SQR programs?
We can Debug SQR using Debugging Commands such as
2. #IF DEBUGxxx
#END-IF
2. DISPLAY and
3. SHOW

     9. What is the difference between sqr and sqc?
SQR SQC
1.This is nothing but the Actual 1.This is like a function in SQR.
Source program.
2.This consists of Program Section. 2.This does not consists of program Section.
3.This can be Compiled & Executed. 3.This cannot be Compiled and Executed.
4.We cannot call one SQR from 4.We can call one SQC from another SQC or another SQR
or SQC. SQR program.

    10. What are different types of variables in sqr?
Types of variables in SQR are
1. Column Variables.
2. List Variables.
3. System Variables.
4. Document Variables.
5. Substitution Variables.
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 for Execution we can increase the Performance of the program by reduciong 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?
 1. Using LOADF-LOOKUP & LOOKUP.
2 .Using ARRAYS.
3. Using MULTIPE REPORTS.
4. Using SQT Files.
5. Using –Bnn Command line Flag.
6. Running on BATCH SERVER.
7. Using Proper Programming Logic in SQR such as Using BREAK statement in EVALUATE Command.
8. 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
[(report arguments)] from Dual
End-select
To call from Begin-sql, the syntax will be
Begin-SQL
;;
End-SQL

     23. Can you connect more than one database using one Sqr? How?
Yes.

     24. What are the differences between Load lookup and Array?ARRAYS LOAD-LOOKUP
1. Arrays can be declared in any section. 1. Load-Lookup is declared in only in
SETUP section only.
2. Number of rows are not automatically 2. Numbers of rows are automatically added. gives a error if the number of added.
Exceeds the specified number.
3. We should insert data in to Array by 3. Rows are automatically inserted in to
programming. Load-Lookup.
4.We can retrive any number of fields. 4.We can retrive only Two fields from
from a table. a table.
5.We have Length & Data type. 5.We don’t have Length & Data type.
6.We cannot directly print from Array. 6.We can directly print from
Load-Lookup.

     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 the 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. Creating new run control SQC if we create a new run control table.
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 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 Redundancy of Printing on a page.

     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 u Debugging SQR programs?
We can Debug SQR using Debugging Commands such as
3. #IF DEBUGxxx
#END-IF
2. DISPLAY and
3. SHOW

     30. How to refer a global variable in local procedure?
After special character (ex.#,&) we use underscore(_) after 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 are the sections in Sqr? Which one is necessary?
Setup, Heading, Footing, Program & Procedure Sections. Program section is the Mandatory section.

    35. What is -debug command line flag?
This is used for Debugging in SQR.
36. How to place an corporate logo in sqr?
Using “Declare Image” command
37. what are the debugging commands in sqr?
Show & Display are debugging commands
38. 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.

1) What is the difference between:
Print $some_var (1,1,0) and
Print $some_var (+1,1,0)
2) What are some possible reasons for seeing an error like this:
(SQR 5528) DB2 SQL PREPARE/DECLARE error -204 in cursor 1:
SQL0204N “DRO.DEALERSHIP” is an undefined name. SQLSTATE=42704
SQL: SELECT orgHier.GEOG_NM,
dlrshp.DLR_CD,
dlrshp.dlrshp_nm,
agmt.APPNTD_DT,
agmt.CNSCTV_TERM_NO
FROM
DRO.DEALERSHIP dlrshp,
…..
….
Error on line 227:
(SQR 3716) Error in SQL statement.
Errors were found in the program file.
SQR: Program Aborting.
3) What is the Load-Lookup structure in SQR useful for and when might you use it.
4) How is a Load-lookup table different from an Array
5) What is the difference between an ASK and an INPUT parameter.
6)
let $var = “SQR RULES”
print $var (1,1,5)
print $var (+1,5,0)
What will the output of these print statements be? (Type in the answer below)
7) For this questions assume you are given the following report to build. The requirements are that after each break in diversity type the total net profit and total number of dealers in the diversity type should be printed (the bolded values inside the squares). And at the end of the report a grand total should be printed. The diversity type should only be printed for the first dealer of that diversity type. Assume you are connecting to the following table:
Diversity_Dealers
dd_id INTEGER
diversity_type VARCHAR
region_cd VARCHAR
start_dt DATE
dealer_cd VARCHAR
dealer_nm VARCHAR
address VARCHAR
city VARCHAR
state_abr VARCHAR(2)
zip_code VARCHAR(5)
area_code VARCHAR(3)
phone_nbr VARCHAR
first_nm VARCHAR
middle_ini VARCHAR
last_name VARCHAR
net_profit INTEGER
Refer the mock up of the report below and write an SQR program that will use the above table to generate this report. (Note: don’t worry about minor errors in syntax or getting the print placement parameters exactly correct. The idea here is to test your ability to use SQR to generate the report and meet the requirements.)
7) True or False, the SQR syntax does allow for a GOTO control structure.
8) What do the RTRIM and RPAD functions do?
9) Write an SQR program that offers a local procedure that allows a user to form a single string that will contain the value of a person’s full name, “Darrin Alan Miller” for example. The SQR program should:
  1. Prompt the user for a person’s first name, middle name, and last name
  2. Allow the user to enter the aforementioned values in upper, lower, or mixed case
  3. Prompt the user for the “style” of name they want to see (“LFM”, “FML”, “LF”, “FL”)
  4. Prompt the user with a choice whether or not they want the “middle name” portion of the resulting name to be abbreviated or not. If the user wants the “abbreviated” version of the middle name, then that name must be followed with a period.
  5. Return the person’s full name with each word having its first character capitalized.
  6. If a person’s last name is the first portion of the full name string created, then that last name should be followed by a comma and appropriately spaced
  7. Although the user may be requesting to see the person’s middle name as part of the full name variable that is created, if the middle name is null (and it can be), then the logic for making this name part of the full name variable should be omitted.
First Name
Middle Name
Last Name
Style
Abbr
MN
Result
DARRIN
Alan
Miller
LFM
Y
Miller, Darrin A.
Darrin
Miller
FML
Y
Darrin Miller
Darrin
AlAN
miller
FML
Y
Darrin A. Miller
10) True or False, the attributes associated with a chart as declared within the DECLARE-CHART definition of an SQR program may be overridden by corresponding attributes within the PRINT-CHART command.
11). The creation of charts within an SQR program requires the use of what type of in memory data type:
A. An array
B. A load-lookup
C. A file
D. A cursor
E. A select statement
12) True or False, the use of arrays within an SQR program should be avoided because they can not be dynamically sized and therefore may obtain more OS memory than needed.
13) What are the methodologies available to pass user parameters into an SQR program?
  1. INPUT
  2. PROMPT
  3. ASK
  4. Choices A and B above
  5. Choices B and C above
  6. Choices A and C above
  7. All of the above
14) True or False, user parameters passed to an SQR program via the INPUT command retrieve a compile-time substitution variable.
15) Within a BEGIN-SQL paragraph various DDL and DML commands such as “creates”, “drops”, “updates”, etc. can be issued against the RDBMS. The ability to issue these types of commands is determined and constrained by the…
  1. RDBMS privileges assigned to the database administrator
  2. RDBMS privileges assigned to Dan Thornhill
  3. RDBMS privileges assigned to the user executing the SQR program
  4. roles, grants, and synonyms assigned to the user executing the SQR program
  5. SQR program designer
16) Which SQR section is responsible for defining and controlling information that is to be printed at the top of each output page?
17) An “&” variable within SQR…
A. May represent and store a numeric value selected from the RDBMS
B. May represent and store a string value selected from the RDBMS
C. Is a read only variable
D. A, B, and C above
E. None of the above
18) Referring to the excerpt of SQR code that follows and assuming that the procedure queried 10 rows of data and that each row possessed a value of 10.00 for the PROD_PRICE column, what will be the value of the #product_total variable that is displayed?
BEGIN-PROCEDURE CALC_FIGURES
LET #PRODUCT_TOTAL = 100
BEGIN-SELECT
PROD_CODE &PROD_CODE
PROD_PRICE &PROD_PRICE
LET #PRODUCT_TOTAL = (#PRODUCT_TOTAL + &PROD_PRICE)
FROM PRODUCTS
WHERE PROD_CODE = 1709
END-SELECT
DISPLAY #product_total
END-PROCEDURE
19) Referring to the excerpts of SQR code that follow, will the contents of #PROD_TOTAL in both excerpts be the same after program execution has completed?
BEGIN-PROCEDURE CALC_FIGURES
BEGIN-SELECT
PROD_CODE &PROD_CODE
PROD_PRICE &PROD_PRICE
LET #PROD_TOTAL = (#PROD_TOTAL + &PROD_PRICE)
FROM PRODUCTS
WHERE PROD_CODE = 1709
END-SELECT
END-PROCEDURE
BEGIN-PROCEDURE CALC_FIGURES
BEGIN-SELECT
PROD_CODE &PROD_CODE
SUM(PROD_PRICE) &PROD_TOTAL
MOVE &PROD_TOTAL TO #PROD_TOTAL
FROM PRODUCTS
WHERE PROD_CODE = 1709
END-SELECT
END-PROCEDURE
20) “&” variables within SQR are assigned during the processing of what type of SQR paragraph?
A. BEGIN-SQL
B. BEGIN-PROCEDURE
C. BEGIN-ERROR
D. BEGIN-SELECT
E. BEGIN-PROGRAM
Use the following excerpts of SQR code to answer following questions.
Program Excerpt A:
BEGIN-PROCEDURE GET_CUSTOMERS
BEGIN-SELECT
C.NAME (1,1)
O.ORDER_DATE (1,35)
O.ORDER_NUM (1,50)
NEXT-LISTING
FROM CUSTOMERS C,
ORDERS O
WHERE C.CUST_NUM = O.CUST_NUM
ORDER BY C.NAME
END-SELECT
END-PROCEDURE
Program Excerpt B:
BEGIN-PROCEDURE GET_CUSTOMERS
BEGIN-SELECT
C.NAME (+1,1)
C.CUST_NUM &LE_NUMBER
DO GET_ORDERS
FROM CUSTOMERS C
ORDER BY C.NAME
END-SELECT
END-PROCEDURE
BEGIN-PROCEDURE GET_ORDERS
BEGIN-SELECT
O.ORDER_DATE (0,35)
O.ORDER_NUM (0,50)
FROM ORDERS
WHERE CUST_NUM = &LE_NUMBER
END-SELECT
END-PROCEDURE
21) The “&LE_NUMBER” portion of code is known as a:
  1. blind variable
  2. bound variable
  3. flexible variable
  4. bind variable
  5. substitution variable
22) If the CUSTOMERS table contains 20 rows, and the ORDERS table contains 20 rows, and these tables have 10 rows with matching CUST_NUM values, will both programs achieve the same results?
23) The code within “Program Excerpt B” can be described as:
  1. a correlated sub-query
  2. a cascading query
  3. a join query
  4. a cluster query
  5. a goto query
24) How many distinct queries are being issued to the RDBMS within “Program Excerpt B”
25) Modify the excerpts of SQR code above related to the previous questions so that possible errors encountered from the RDBMS will be handled.
sqr
1. Explain what is on-break in SQR with options and their use?
Ans: On-break is a very powerful SQR feature that will allow you to control what happens when values in a column break, or change. Specifically, you will use on-break to:
• Suppress duplicate values
• Produce subtotals
• Produce subheadings
• Control the flow of your SQR
Syntax: print {field} (a, b, c) on-break {print = change |
print = change/top-page |
print = always |
print = never}
before = procedure_name
after = procedure_name
save = $text_var
skiplines = nn
level = nn
2. What is the load look up concept in SQR?
Ans: A common function within an SQR program is to join tables in order to get the long description of some data code.
For instance, in a given table, you may have a COURSE or DEPT_ID but the corresponding description is in another table.
This situation requires the SQR program to retrieve subordinate data and can be done via a join, or by performing a procedure that performs a
separate SELECT from within the main SELECT statement. However, for large tables with large amounts of data, a table join can be a slow process.
There is a more efficient way of retrieving this data in SQR. You can use lookup tables to save static data in a dynamic structure providing you more
efficient data retrieval during the execution of an SQR program. Lookup tables are implemented in SQR programs using the load-lookup and lookup commands.
load-lookup
Example: load-lookup
name=EmployeeDetails
rows=12
table=PSJOB
key=EMPLID
return_value=NAME
where=DEPT=’’Company’’’
and (effective dating logic) … (this is the syntax we gve in set-up section)
lookup EmployeeDetails &EMPLID $NAME(this is how we call from a procedure)
3. How do you pass parameters among procedures, what is the alternative to do that?
Ans: We usually pass parameters from one procedure to another using variables prefixed by $ or # depending on the data type.
<******************************* I doubt even arrays ****************************************>
4. How do you control page breaks?
Ans: You can prevent page breaks within a record by following four simple rules:
1. Place ON-BREAK columns ahead of other columns in the select paragraph.
2. Place the lower-level ON-BREAK columns ahead of the higher-level ON-BREAK columns in the select paragraph.
3. Use the same line positions for all ON-BREAK columns.
4. Avoid using WRAP and ON-BREAK together on one column.
5. How do you influence report output to landscape or portrait
Ans: PeopleSoft has standardized the Setup section using SQC files references by the #include command. Examples
setup31.sqc Portrait mode
setup32.sqc Landscape mode
6. How you debug SQRs
Ans. 1. By displaying data when the query runs using show/display so that isolate problems areas by temporarily skipping the program that is not working.
Show is used to show any number of variables. Display is also speified for any variable, column or literal but it displays only one value.
2. SQR provides #debug command to make temporary changes to the code.
7. Explain Complier directives in SQR
Ans. Compiler directives are #IF, #ELSE, #END-IF, #IFDEF, #IFNDEF used before the substitution variables declared by #DEFINE.
1. #if: Compiles the commands followed by #if if the condition satisfies.
2. #else: compiles the commands followed by else if the #if condition doesn’t satisfy.
3. #end-if : Ends the #if.
4. #ifdef : Compliles the commands under #ifdef if the substitution variable is defined.
5. #ifndef : Compliles the commands under #ifdef if the substitution variable is not defined.
8. How many flat files can be opened through SQRs and what is the limitations in reading or write those files?
Ans. As many but the maximum number of files is 99.
9. What is a difference between Ask & Input command?
Ans. 1. Ask : Retrives values from compile time variables, and can be written in begin-setup section only. The retrieval can be either from userinput, command line, or from a file.
2. Input : Accept dat entered by the user into the variable specified.
10. How do we work on Effective dated rows on SQR?
Ans. We add the criteria in the sql command the effective date must be equal to current date.
EFFDT = (SELECT MAX(E.EFFDT) FROM PS_JOB A
WHERE A1.EMPLID = A.EMPLID
AND A1.EMPL-RCD = A.EMPL-RCD
AND A1.EFFDT <= DATENOW()
11. How to Refer Global variable in a local procedure through SQR?
Ans: <**********************not sure of ans **************************************>
12. How do you declare a local procedure and global procedures in SQR?
Ans: SQR procedures that contain variables that are visible throughout the program are called global procedures. These procedures can also directly reference any program variable.
In contrast, procedures that take arguments, such as the spell number procedure in this section’s check-printing sample program, are local procedures. In SQR for PeopleSoft, any procedure that takes arguments is automatically considered local.
13. What is the difference between Begin-Select & Begin-SQL?
Ans: 1. The Begin-SQL paragraph allows you to execute any non-select SQL statements (Insert, Update, Delete) DML
2. The Begin-Select paragraph is the heart of the SQR program.
example: begin-SELECT [DISTINCT]
COLUMN_NAME
[SQR commands]
FROM TABLE/VIEW
[WHERE…]
[Order by …]
end-SELECT
14. Can multiple columns be retrieved in Load-Lookup command in SQR?
Ans: <**************************** I wud like to know this ans**************************************>
15. How do you control the number of iterations or fetch records firing SQL statement in SQR?
Ans: By looping
16. What is a difference between EDIT & Format in SQR?
Ans: Edit is used to edit any data type and format is used to edit only date fields in input command.
17. How do you Execute SQR using Process Scheduler?
Ans: BEGIN-SELECT
FROM PS_
WHERE OPRID = $PRCS_OPRID
AND RUN_CNTL_ID = $PRCS_RUN_CNTL_ID
END-SELECT
18. Are SQR commands and variables case sensitive?
Ans: SQR commands and variables are not case sensitive.
19. Do SQR programs require a dot(.) sqr extension? Do SQR runtime files require a dot (.) sqt extension?
Ans: Yes a SQR needs to be saved as (.sqr) extension and runtime file need (.sqt).
20. How can I read invariable length delimited fields from a sequential file? An example format would look like this: field1|field2|field3|…
Ans: With the field separator as in above example “|” is the delimiter.
21. How can I insert a null value into a numeric field in the database?
Ans: By inserting zero (‘0’) into the field we can give null values to numeric field.
22. What does the SQR variable “#sql-count” holds?
Ans: #sql-count holds the count of rows that are affected by a begin-select paragraph(INSERT, UPDATE, or DELETE).
23. How do you reset the page number printed on a PAGE?
Ans:
24. For multiple reports is it compulsory to use command “declare layout”? Or can we create multiple reports without “declare layout”?
Categories
PeopleSoft BI Publisher FAQ

PeopleSoft Business intelligence ( XML ) Publisher Frequently Asked Question

1.When creating a report definition can we select a psquery as data source that has not been registered?
When creating a report definition, you can select a PeopleSoft Query data source that has not yet been registered and that data source is registered automatically once the report definition is saved.
However, all other types of data sources must be registered before they can be associated with a report definition.
2.What is the name of the page used for registering the data source?
navigation: Reporting Tools, XML Publisher, Data Source
PSXPDATASRC
3.What is schema file consists of?
To create the schema we could simply follow the structure in the XML document and define each element as we find it.
4.Can we include images in reports using Xml publisher?
Yes, in many ways
Based on this option we will remove the Report from Report repository and archive the Data to the report Archive table. The maximum limit is 999 days.
5.What is meant by purge in XMLP and what is the limit?
6.I Have a Template, and I done some customizations in that template, those customizations are reflecting in the output?
7.What is the difference between XML file and XML doc?
 8.From which peopletools onwards the XML publisher is applicable?
Ans: 8.48 onwards.
9.What are the data sources available in XML Publisher?
Ans: PS Query,Rowset,Xml and XmlDoc.
10.How do we setup add-ins for MS-Word?
Ans: Go to XmlPublisher–>setup–>Designer Help–>Plug-in for MS Word
11.What are the different output formats in XML Publisher?
Ans:PDF,RTF,Excel and HTML
12.What is the meaning of Bursting?Is it mandatory option in XMLP?
Ans:Bursting is just like Group by caluse in SQL. Its optional in XMLP.
13.Where can we maintain security in XMLP?
Ans: Report Definition
14.How to run the XMLP using data source as PS Query?How to schedule the XMLP?
Ans: We can run XMLP using Query Report Viewer and Scheduling is done with Query Report Scheduler.
15.What are the advantages of XMLP over other reporting tools?
Ans: XMLP have different datasources to get the data.
In XMLP, we can easily design output template through MS Word.
We can easily maintain security in XMLP
16.Identify the below script?
a. HTML
b. Java Script
c. Java
d. People code
XML – Correct


17.What is different the vb.net and XML?
Vb.net is product of micro Soft Corporation. it is for developing application programs. Xml is an open standard one. It is for representing data. Xml is not restricted to the particular language. We can use to represent data of any type of application. XML is common one. For business to business communication XML is Best one.
18.Does XML Publisher have an ability to use the switching feature of multi-language data entry?
Because XML Publisher uses managed object functionality, you do not have the ability to use the switching feature of multi-language data entry. You can populate related language tables by logging in and establishing a different session language. The related language table for that session can then be populated.
19.What are the formats supported by XML Publisher?
It can generate reports in many formats (PDF, RTF, Excel, HTML, and so on) in many languages.
20.Briefly Describe XML Publisher?
Oracle has developed a standalone Java-based reporting technology called XML Publisher (XMLP) that streamlines report and form generation. A primary feature of Oracle’s XML Publisher product is the separation of the data extraction process from the report layout. XML Publisher provides the ability to design and create report layout templates with the more common desktop applications of Microsoft Word and Adobe Acrobat, and renders XML data based on those templates.
21.What are the implementation steps involved in XML Publisher?
XML Publisher implementation can be divided into the following phases:
  • Configure XML Publisher.
  • Register data sources.
  • Create templates.
  • Define XML Publisher reports.
  • Run XML Publisher reports.
  • Locate and view published XML Publisher reports.
22.What are the categories involved XML Publisher security?
XML Publisher security can be separated into three categories:
  1. Defining reports.
  2. Running reports.
  3. Viewing reports.
23.What is XML Publisher Business Processes?
XML Publisher menu access is permission list driven and depends on permission list and role assignment. PeopleTools delivers permission list security and roles for XML Publisher report developers and XML Publisher power users.
Permission list PTPT2600 is intended for report developers.
Permission list PTPT2500 is intended for power users and provides access to Query data sources for ad hoc reporting through Query Report Viewer and Query Report Scheduler.
Users assigned to other permission lists and roles, such as permission list PTPT1000, may only have access to the XML Publisher Report Repository.
24.File setting relative to the application server?
The -Dxdo.ConfigFile setting is relative to the application server or process scheduler domain directory, which is the current directory when the domain starts up.
25.Why Report Category is required?
Report Category is a required attribute on all report definitions and Content Library sub-templates. By assigning a report category, you are actually applying row level security to the data on those components.
26.The PeopleCode XML Publisher classes also respect report category settings and read-only access rights.
True
27.When do XML Publisher report definition output options are reflected in the output type and output format prompts on the Process Scheduler Request page?
Only when the application process that runs the report is assigned the process type of XML Publisher.
28.Use of Report definitions?
Report definitions associate a data source with template files. A data source registers the schema and sample data design files. It is the extracted application fields from the data source files that are placed into the template files to create the final report.
29.What is mainly based on Report definition access?
Report definition access is based on user permission list security and roles.
30.How is registering Process in Report definition?
When creating a report definition, you can select a PeopleSoft Query data source that has not yet been registered and that data source is registered automatically once the report definition is saved. However, all other types of data sources must be registered before they can be associated with a report definition.
31.How can u check the format of an XML output file?
You can check the format of an XML output file by opening it using Microsoft Internet Explorer (IE). IE opens the file and alerts you to any problems, such as unclosed tags.
32.What does Bursting feature do?
Bursting is an optional advanced feature that is only available when reports are run through Process Scheduler and is not intended for real-time online viewing. It is typically used when you are repeating the generation of a templated report layout many times for multiple like sets of data.
For example, generating a batch run on vendor purchase orders or customer invoices. With bursting, you can generate individual report files resulting in separate secured output. For example, generating a file for each vendor, customer or employee.
33.From where, Bursted reports must be runned?
Bursted reports must be run from the Query Report Scheduler component.
34.What is to be done in order for a schema field to take advantage of the bursting features?
It must be registered with the data source of the report definition.
35.What are Sub-templates?
Sub-templates are secondary RTF or XSL templates that are imported by primary RTF or XSL report templates. The primary template accesses the sub-template through the XSL import style sheet feature.
The sub-template files are independently stored and are not registered in association with a data source or primary template.
36.Template Translation feature is based upon?
The Template Translation feature is based upon standard Localization Interchange File Format (XLIFF) .xlf file processing. Each report template or sub-template file can have related translation XLIFF files. These XLIFF files include translation units for each content element to be translated.
37.Does a template must exist before it can be translated?
Yes,
template must exist before it can be translated.
Template translations are not available for template types other than RTF. For a PDF report, there must be multiple PDF templates registered to the report, one for each locale or language as required.
38.Do related prompts appear?
If a query is run through Reporting Tools, Query, Schedule Query, the XML Publisher-related prompts do not appear. Only the basic table-formatted query results are generated.
39.How to Insert the Image in XML Publisher?
It is advised to use the Microsoft Word Insert menu option to insert the image, as the additional properties that need to be set for the RTF template to correctly generate reports with those images are automatically set by using this method. Additionally, dragging and dropping an image onto a template creates a link to the local machine being used and may cause problems when the report is generated.
The user has to be online to access the image referenced in the URL.
40.XMLP report data sources can be?
PSQuery
XMLDoc
Rowset
XML file
41.XMPL reports cannot have multiple templates
False
42.What are the Template types?
PDF
RTF
E-text
XSL
43.What are the Report output types?
HTML
PDF
RTF
XLS
44.What is the file which Xml publishers’ global engine settings are defined?
Xml publishers’ global engine settings are defined in the xdo.cfg file
45.What are the steps involved in creating XML?
Data sourceà generate xml and schema filesà load xml file to templateà create report definition (declare template type)àupload template fileà setting the properties for security purpose (setting report categories ID)à viewing output
46.How do mail your report output?
Reporting toolà xml publisherà query report schedulerà Run button (Distribution) we can view the mail.
47.What is E-text?
An E-Text template is an RTF-based template that is used to generate text output for Electronic Funds Transfer (EFT) and Electronic Data Interchange (EDI). At runtime, BI Publisher applies this template to an input XML data file to create an output text file that can be transmitted to a bank or other customer.
48.How to generate E-text report and what are the steps involved in it?
Data sourceà generate xml and schema filesà load xml file to templateà create report definition (declare E-text template type)àupload template fileà setting the properties for security purpose (setting report categories ID)à viewing output
49.What is Header record in E-text why it is used?
50.How do you schedule your query, other than Query Report Scheduling?
Through App engine or Peoplecode we scheduling.
Import Application Packages, PSXP_MALGEN this is used for generate xml scheme file and xml date file.
51.Did u use charts in your template?
Add-In à temple builder à Insert àcharts
52.What is an EFT?
An EFT is an electronic transmission of financial data and payments to banks in a specific fixed-position format flat file (text).
53.Define few E-text template Reports?
IBYDE_D_DEENà German Domestic payment
IBYDE_FR_ENà French EFT Payments
IBYDE_F_DEENà German International EFT payments
IBYDE_F_NLENà Netherlands International EFT
IBYDE_Z_JPENà Japanese Zengin Format
54.Where is the xml publisher report output available?
All the xml publisher report outputs are available in Report Manger, not in Process Monitor.
55.The Primary feature of Oracle XMPL Separation of the Data Interaction process from report layout.
True
56.The presentation logic and data logic need not to be same
True
It can dramatically reduces the report maintenance; it enables the power business users to adjust the report templates.
57.What are the steps involved in Setup XML publisher?
Define XMLP settings
Setup XMLP permission list security
Setup Report manager
Define Report categories, including report definition editor security
Download design plug-ins to facilitate the offline template design activity.
58.What are the Data sources that we create and register?
The data sources can be:
PS-Query, Rowset, peoplecode, XMLDoc and XML file.
In case of PS-Query, the system by default generates the XML file and schema file.
59.How do we create and Upload Report Template?
Here we can use either MS-Word or adode to develop and maintain custom report formats.
60.What are the template formats?
The template formats are: .RFT, PDF, .XLS, .XSLT
After the creation of template files, we need to load the “XML file”, and then only we can design our report.
61.Define XML Publisher report?
In the report definition Tab, we need to set the report status should be active, report category ID and object owner ID and we need to choose the template types.
62.What are the processes preferred for XML file?
XML file is the preferred data source for lager process such as paychecks, rowset and xml doc have performance/memory limitations, and we can use this only for small reports.
63.What are the Application packages of xml publishers?
PSXP- RPTDEFMANAGER
PSXP -REPORTMGR
PSXP -XMLGEN
PSXP –ENGINE
64.What are the options for creating fields in RTF & PDF Format?
RTF: For- each syntax repeating the group of elements
Template – Field
Form – Field
Form – Field
Xml – data field
PDF: Two options for creating fields here
  1. Manual creation and we can map that fields top XML data.
  2. Create fields with same name as XML elements.
65.What are the Publisher Classes?
Report Manager Definition class
Report Manager Search class
XML Generation class
XML publisher engine class
Note:-The Xml publisher report output
66.XMLP Global Engine settings are defined in which file?
XMLP Global Engine settings are defined in xdo.cfg file, which is located at
$PSHOME / APP SERVER Directory
67.What are the Permission lists for XML Publisher?
PTPT2600 this only for Developer, which will give an access to all XMLP Component including buffing feature also.
PTPT 2500 this is only for power users, it will provide the access to query data sources for Ad-hoc reporting, this also provide the access to report definition, Bursting feature is view only.
PTPT1000: This will give the access to access the report repository.
Categories
PeopleSoft App Engine FAQ

PeopleSoft Application Engine Frequently Asked Question

Categories
PeopleSoft PeopleCode FAQ

PeopleSoft PeopleCode Frequently Asked Question

What is component processor?
The Component Processor is the PeopleTools runtime engine that controls processing of the application from the time the end-user requests a component from an application menu through the time that the database is updated and processing of the component is complete.
How does the component processor allocate buffers to hold data?
The component processor starts allocating buffers at occurs level 0 and then works its way down. It uses the page and record definitions to determine the data it needs. Generally, if there is one field from the record definition on the page, the entire row will be brought into the buffers. The exceptions are fields that are in the search dialog box, derived work fields, and related display fields.
How to access the Peoplecode Debugger?
Peoplecode debugger can be used through Application designer. Open the program. Choose Debug, Enter Debug mode. The local variables watch window opens.
Choose, Debug, View Component buffers. The component buffer pane opens. We can view visible current line of execution, visible breakpoints. We can also place the cursor on the fields to see their current value.
What is Win-message built in function?
The Win-message built in function can also be used for debugging. It is used to display an information message to the user without performing error and warning processing.
What is people code trace?
Peoplecode Trace can also be used for debugging. To turn on the trace, go to PeopleTools, Utilities, Debug, Trace Peoplecode. We can see number of check boxes to set the detail level of the trace. As we select a combination of check boxes and click on Save, the system starts creating the trace file. Add &Trace=y to your browser’s URL. All the trace setting options will appear on login page. Using the Configuration Manager select the check boxes and select Apply. Then log off Peoplesoft and log back to start the trace.
How many data types we have in people code?
The conventional data types available in previous releases are the core of PeopleCode functionality. The object data types are used to instantiate objects from the PeopleTools classes. The appropriate use of each data type is demonstrated where the documentation discusses PeopleCode that uses that data type.
PeopleSoft recommends that you declare your variables before you use them.
In this section, we discuss the following topics:
·         Conventional data types.
·         Object data types.
Conventional Data Types
·         ANY
When variables and function return values are declared as ANY, the data type is indeterminate, enabling PeopleTools to determine the appropriate type of value based on context. Undeclared local variables are ANY by default.
·         BOOLEAN
·         DATE
·         DATETIME
·         FLOAT
·         INTEGER
Note. The FLOAT and INTEGER data types should be used only where a performance analysis indicates that the increased speed is useful and an application analysis indicates that the different representations won’t affect the results of the computations.
·         NUMBER
·         OBJECT
·         STRING
·         TIME
Object Data Types
For most classes in PeopleTools, you need a corresponding object data type to instantiate objects from that class.
The following are the data buffer access types:
·         Field
·         Record
·         Row
·         Rowset
The following are the page display types:
·         Grid
·         GridColumn
·         Page
The following are the Internet Script types:
·         Cookie
·         Request
·         Response
The following are the miscellaneous object types:
·         AESection
·         Array
·         File
·         Interlink
·         BIDocs
Note. BIDocs and Interlink objects used in PeopleCode programs run on the application server can only be declared as type Local. You can declare Interlinks as Global only in an Application Engine program
·         JavaObject
Note. JavaObject objects can only be declared as type Local.
·         Message
·         OptEngine
·         ProcessRequest
·         SoapDoc
·         SQL
·         SyncServer
·         TransformData
Note. TransformData objects can only be declared as type Local
·         XmlDoc
·         XmlNode
Note. XmlNode objects can only be declared as type Local.
API Object Type
The API object type has some additional considerations.
Use this data type for any API object, such as a session object, a tree object, a Component Interface, a PortalRegistry, and so on.
The following ApiObject data type objects can be declared as Global:
·         Session
·         PSMessages collection
·         PSMessages
·         All Tree classes (trees, tree structures, nodes, levels, and so on.)
·         All Query classes
All other ApiObject data type objects must be declared as Local .
How many types of variables are there in peoplecode?
4 types of variables are there
local
global
component
system
What is the scope of variables, local, global, component, derived work field, system?
Local: local variables created automatically by assigning a value to the variable. We can declare anywhere with in a people code program
Global variables: To use these variables we must declare it at the beginning of the program. Once declared it can be referenced the operator logs off peoplesoft. Only comments or other declarations are allowed top of these declaration. These variables should be used to transfer values between components only.
At what time system varaiable are created?
System variables are automatically created  when an operator logs on to peoplesoft.
Where we can declare variables?
We can declare variables  in any peoplecode event
PeopleCode supports how many types of functions?
PeopleCode supports the 2 types of functions:
                          i.    Built-in Functions:
Declaration is not at all required for built-in functions, and we can call directly.
Built-in functions are divided into different types based on their purpose.
                         ii.    User-Defined Functions
We have 3 types of User-Defined Functions.
Internal PeopleCode Functions: Peoplecode common routines contained within the same program where they are used. These functions are defined by the Function statement. Declaration is not at all required for Internal PeopleCode Functions. Internal PeopleCode Functions are defined within the program from which program we are calling the function.
External PeopleCode Functions: Peoplecode functions contained in a different program from where they are used. These functions are defined by the Declare… Peoplecode statement in the programs where used. Declaration is required to call this function. External PeopleCode functions are defined outside the calling program. Most External PeopleCode functions are stored on record definitions called function libraries.
External non-PeopleCode Functions: Common routines written in C or another language and loaded from a DLL. These are defined by the PeopleCode Declare… Library statement. Declaration is required to call this function, External non-PeopleCode Functions are stored in external libraries. And the convention is, place the all external functions in the Field Formula Event
How you declare a function?
Internal peoplecode functions: In this peoplecode identifies function by the Function statement.Each function must end with a corresponding End-function
Syntax: Function (,…………)
             Statement
             Statement
             End-function
External peoplecode functions: By using Declare function statement at the beginning of the program.
Syntax: Declare function peoplecode field formula;
The keyword peoplecode identify the function as an external peoplecode function. Once an external people code function is declared in a program it can then be called multiple times.
External Non peoplecode functions: A common c++ routine that is loaded from a DLL. It defines by a Declare statement with the keyword library.
What is a Rowset, row, record, field object?
Rowset: It is a data structure used to describe hierarchical data. It is made up of a collection of rows. It can contain all of the scroll’s data. A level 0 rowset contains all the data for the entire component. A rowset contains one or more rows. A rowset can contain the entire contents of a Component Buffer, or the contents of any lower level scroll plus all of its subordinate buffer data.
Row: Row represents a single row in a component scroll. A row contains one or more records or child rowsets.
Record: Record is a single instance of data within a row and is based on a record definition. A record contains one or more fields.
Field: Field is a single instance of data within records and is based on a field definition.
What is a class and an object?
A class is the formal definition of an object and acts as a template from which an instance of an object is created at runtime. The class defines the properties of the object and the methods used to control the object’s behavior. PeopleSoft delivers predefined classes (such as Array, File, Field, SQL, and so on.) You can create your own classes using the Application Class.
An object is a data structure in the memory. An instance of an object is created from its class.
In how many ways an object can be instantiated?
There are 3 ways to instantiate and object-
Createxxx built in functions- create an empty data structure in the data buffer.
Getxxx built-in functions- creates a data structure that refer to already existing data in the buffer.
Getxxx methods- Create a child object of the parent object executing the method.
What is SQLExec?
The SQLExec function executes a SQL command from within a PeopleCode program by passing a SQL command string. We can use SQLExec with insert, delete, update commands to modify data in tables that have not been loaded into the component buffer. If you want to delete, insert, or update a single record, use the corresponding PeopleCode record object method.
 If you want to delete, insert, or update a series of records, all of the same type, use the CreateSQL or GetSQL functions then the Execute SQL object method.
Syntax: SQLExec (“SQL statement”, );
What is Limitation of SQLExec SELECT Statement?
SQLExec can only SELECT a single row of data. If your SQL statement (or your SQL.sqlname statement) retrieves more than one row of data, SQLExec sends only the first row to its output variables. Any subsequent rows are discarded. This means if you want to fetch only a single row, SQLExec can perform better than the other SQL functions, because only a single row is fetched. Another limitation is that PeopleTools won’t maintain quoted literals. It means if any changes are made to definitions that are referenced in a SQLExec quoted literal, Peoplesoft cannot apply the changes to the code. If you need to SELECT multiple rows of data, we can use SQL class. First a SQL object has to be instantiated using the CreateSQL built-in function
SQLExec statements that result in a database update (specifically, UPDATE, INSERT, and DELETE) can only be issued in the following events:
SavePreChange, WorkFlow, SavePostChange, FieldChange
Remember that SQLExec UPDATEs, INSERTs, and DELETEs go directly to the database server, not to the Component Processor (although SQLExec can look at data in the buffer using bind variables included in the SQL string). If a SQLExec assumes that the database has been updated based on changes made in the component, that SQLExec can be issued only in the SavePostChange event, because before SavePostChange none of the changes made to page data has actually been written back to the database.
Syntax: Local SQL &SQL          (declaring the SQL object from SQL class)
            &SQL= CreateSQL (“SQL statement”, );
What is the fetch method?
The fetch method can be used to retrieve the next row from the cursor. Fetch takes as its argument the output variables to receive values from the Select.
What is SQLdefinition?
SQL definition- A SQL definition is an Application Designer definition that is used to store and maintain a SQL statement. We can create the SQL definition using the application designer and edit the SQL in SQL editor. Benefits of SQL definition- Reusable, Upgradeable, platform independent, Validated.
To reference a SQL definition, you will instantiate a SQL object with the GetSQL built-in function.
Syntax: GetSQL (SQL. sqlname, , );
SQL.sqlname is the name you gave the definition when it was saved.
Use CreateSQL(“SQL string”) when you are passing a text string to your SQL object.
Use GetSQL (SQL.sqlname) when you are getting the SQL from a SQL definition.
In which peoplecode events we can place SQLExec updates, deletes and inserts?
FieldChange
SavePreChange
WorkFlow
SavePostChange
Peoplecode Events:
SearchInit– This event is performed before the search page is displayed to the user. It’s a way to control processing before the user enters any value in the search page. Placed on search record field or component search record.
SearchSave- This event is performed when the user clicks on the search button in the search dialog. Error and warning messages can be placed on this event to prompt the user to enter at least one value in the search dialog. Placed on the search record or component search record.
RowSelect- This event is performed as the component processor reads data into the buffer. This peoplecode can prevent the component processor to load any specific data into the component by using the DiscardRow and StopFetching functions. Placed on record field or component record.
PreBuild- This event is performed once before the rest of the component build events. It can be used to hide or unhide pages and to set component variables. It can also be used to validate data entered in the search page. Placed on a component.
FieldDefault- This event is performed when a field has no value. It is used to set the default value to the field. Placed on record field or component record.
FieldFormula- After the FieldDefault is performed successfully, FieldFormula is performed. But it increases the performance overhead as it processes unconditionally every time the page is displayed. Now this event is used to store the web function libraries. Placed on a record field.
RowInit- This event is performed for every new row encountered by the component buffer. It is used to control the initial appearance of the fields. Placed on record field or component record.
PostBuild- This is performed after the component processor builds the page and before the page is displayed to the user. It is used to hide or unhide pages and set component variables. Placed only on the component.
Activate- This is performed when the page is displayed to the user. This is the only page event. It occurs each time the user selects a page. Placed on a page.
FieldEdit- This is performed once the value of the field is changed and the new value satisfies the system edits. It is used to validate the new value of the field. Error and warning messages can be placed. Placed on record field or component record.
FieldChange- After the FieldEdit is performed successfully, this event is performed but it is not used for validation. It is used for any additional processing based on the new value of the changed field. Placed on record field or component record.
RowInsert- This is performed when the user inserts a new row in the scroll. Placed on record field or component record.
RowDelete- It occurs when a row is deleted from the scroll. Error and warning messages can be placed to prevent the user to delete any row. Placed on record field or component record.
SaveEdit- It is performed once the user tries to save the component. It is used to validate data before it is updated on the database. This applies to all the rows of data and all pages in the component. Placed on record field and component record.
SavePreChange- It occurs after the SaveEdit is performed successfully. It provides one last chance to manipulate data before the database is updated. Placed on record field, component record or component.
WorkFlow- It occurs when a component is saved. Use Workflow to trigger a business event. Workflow PeopleCode is not field-specific: it triggers PeopleCode on all fields and on all rows of data in the component buffer. Placed on record field, component record or a component.
SavePostChange- After SavePreChange completes successfully, and the component processor issues the SQL statement to update the database, SavePostChange peoplecode is applied. It occurs after the system updates the database. Code in this event is used to update the data not in the buffer but in the database. Placed on record field, component record or component.
ItemSelected-  The ItemSelected event fires whenever the end-user selects a menu item from a pop-up menu. In pop-up menus ItemSelected PeopleCode executes in the context of the page field from where the pop-up menu is attached, which means that you can freely reference and change page fields, just as you could from a pushbutton. This event and all its associated peoplecode will not fire if run from a component interface.
ItemSelected PeopleCode is only associated with pop-up menu items.
PrePopup-  The PrePopup event fires just before the display of a pop-up menu.
You can use PrePopup PeopleCode to control the appearance of the Pop-up menu. This event and all its associated peoplecode will not fire if run from a component interface.
PrePopup PeopleCode can be associated with record fields and component record fields
Record Field
Component Record Field
Component Record
Component
Page
Menu
FieldChange
FieldDefault
FieldEdit
FieldFormula
PrePopup
RowDelete
RowInit
RowInsert
RowSelect
SaveEdit
SavePostChg
SavePreChg
SearchInit
SearchSave
Workflow
FieldChange
FieldDefault
FieldEdit
PrePopup
RowDelete
RowInit
RowInsert
RowSelect
SaveEdit
SavePostChg
SavePreChg
SearchInit
SearchSave
PostBuild
PreBuild
SavePostChg
SavePreChg
Workflow
Activate
ItemSele
What are message events?
These events are not considered part of the Component Processor flow, so they’re documented separately from the majority of PeopleCode events. The following events are associated with messages:
·         OnPublishTransform Event
·         OnSubscribeTransform Event
·         OnRouteSend Event
·         OnRouteReceive Event
·         OnRequest Event
In a message definition, you can associate a PeopleCode program with the message itself and with each message subscription included in the definition.
Sequence of  events firing:
Field level default processing:
Field default
Component level default processing:
Field formula
Search processing in update Mode:
Search Init
Search Save
Search processing in Add Mode:
Row init
Search Init
Field Edit
Field Change
Save Edit
Search Save
Component Build processing in Update Mode:
Row select
Pre build
Row init
Post build
Activate
Component Build processing in Add Mode:
Row init
Post build
Activate
Save processing:
Save edit
Save prechange
Workflow
Save postchange
Row insert processing:
Row insert
Row init
Post build
Field Modification process:
Field edit
Field change
Row select processing:
Row select
Row delete processing;
Row delete
Popup menu display:
Prepopup
Itemselected processing:
Prepopup
Difference between Field Edit and Field Change?
We can write error messages in field edit and we can not write in field change.
Differenct between prebuild and postbuild?
Prebuild: The PreBuild event fires before the rest of the component build events. This event is often used to hide or unhide pages. It’s also used to set component variables. The PreBuild event is also used to validate data entered in the search dialog, after a prompt list is displayed
Post Build : The PostBuild event fires after all the other component build events have fired. This event is often used to hide or unhide pages. It’s also used to set component variables.

What is the Difference between save pre change and save post change?

It provides one last chance to manipulate data before the database is updated.
It occurs after the system updates the database. By using this event we can update the data directly in the database.

What is Importance of row insert?

To restrict some users to insert new rows into scrolls. It is valid only if the component is having scrolls (means child records). The program never executes unless there is scrolls, not valid for 0 level records.
Push button- Push buttons are used to trigger peoplecode programs. The advantage is that the user determines when the program should be performed by clicking on the push button. A push button will perform any FieldEdit and FieldChange events where the push button is attached.
The component processor flow changes for which events in add mode?
Search Record and Page display.
Traversing the data buffer- Accessing specific data by working through the data buffer hierarchy is known as traversing the data buffer. We can traverse the data buffer from the top down, starting from level 0 rowset and working the way down to a specific field.
Step 1:  Declare the data buffer objects
Component Rowset       &RS_level0, &RS_level1;
Component Row           &Row_level0, &Row_level1;
Component Record       &Record;
Component Field           &Field;
Step 2: Get the level 0 rowset and level 0 row
&RS_level0 = Getlevel0();
&Row_level0 = &RS_level0.GetRow(1);
Step3: Get the level 1 rowset
&RS_level1 = &Row_level0.GetRowset(SCROLL.Empl_Checklist);
Step 4: Get the level 1 row
&Row_level1 = &RS_level1.GetRow(2);
Step 5: Get the level 1 record
&Record = &Row_level1.GetRecord(Record.Empl_Checklist);
Step 6: Get the level 1 field
&Field = &Record.GetField(Field.Briefing_status);
Traversing multiple occurs level-
Rowset                         &RS_level0 = Getlevel0();
Row                             &Row_level0 = &RS_level0(1);
Rowset                         &RS_level1 = &Row_level0.GetRowset(SCROLL.Empl_Checklist);
                                    For &I = 1 To &RS_level1.ActiveRowCount
Row                             &Row_level1 = &RS_level1(&I);
Rowset                         &RS_level2 = &Row_level1.GetRowset(SCROLL.Empl.Chklst_Itm);
                                    For &J = 1 To &RS_level2.ActiveRowCount
Row                             &Row_level2 = &RS_level2(&J);
Record                         &Record = &Row_level2.Empl_Chklst_Itm;
Field                            &Field = &Record.Briefing_status;
                                    End For;
                                    End For;
Using Shortcut-
&Field = GetLevel0() (1).Empl.Checklist(1).Empl_Chklst_Itm(1).Empl_Chklst_Itm.Briefing_status;

Traversing the Data Buffer Hierarchy Example

Suppose you want to access the BRIEFING_STATUS field at level 2 of the following page:
The first thing to ask is where is your code running? Where are you starting from? For this example, the code is starting at a field on a record at level 0. However, you do not always have to start at level 0.
After you start with level 0, you must traverse the data hierarchy, through the level 1 rowset, to the level 2 rowset, before you can access the record that contains the field. Here’s the hierarchy again:
A rowset contains one or more rows, a row contains one or more records and zero or more child rowsets, and a record contains one or more fields.
Rowset
You first obtain the level 0 rowset, which is the PERSONAL_DATA rowset. However, you do not need to know the name of the level 0 rowset to access it.
&LEVEL0 = GetLevel0();
Rowsets Contain Rows
The next object to get is a row. As this code is working with data that is loaded from a page, there will only ever be one row at level 0. However, if you have rowsets that are populated with data that is not based on Component Buffers (for example, an Application Message) you may have more than one row at level 0.
&LEVEL0_ROW = &LEVEL0(1);
Rows Can Contain Child Rowsets
We need to get to the level 2 rowset. To do that, we need to traverse through the level 1 rowset first. Therefore, the next object we want to get is the level 1 rowset.
&LEVEL1 = &LEVEL0_ROW.GetRowset(SCROLL.EMPL_CHECKLIST);
Rowsets Contain Rows
If you’re traversing a page, the first thing to always do after you get a rowset is to get the appropriate row. Because we want to process all the rows of the rowset, we set this up in a loop.
For &I = 1 to &LEVEL1.ActiveRowCount
   &LEVEL1_ROW = &LEVEL1(&I);
   . . .
End-For;
Rows Can Contain Child Rowsets, Rowsets Contain Rows
We need to traverse down another level in our page structure. This means accessing the second level rowset. Then we need to access the rows in the second level rowset, in another loop.
Because we’re processing all the rows at the level 1, we’re just adding code to the previous For loop. As we’re processing through all the rows at level 2, we’re adding a second For loop. The new code is in bold.
For &I = 1 to &LEVEL1.ActiveRowCount
   &LEVEL1_ROW = &LEVEL1(&I);
   &LEVEL2 = &LEVEL1_ROW.GetRowset(SCROLL.EMPL_CHKLST_ITM);
   For &J = 1 to &LEVEL2.ActiveRowCount
      &LEVEL2_ROW = &LEVEL2(&J);
   . . .
   End-For;
End-For;
Rows Contain Records
Rows also contain records. In fact, a row will always contain a record, and may contain only a child rowset, depending on how your page is set up. GetRecord is the default method for a row, so all you have to specify is the record name.
Because we’re processing all the rows at the level 2, we’re just adding code to the previous For loops. The new code is in bold.
For &I = 1 to &LEVEL1.ActiveRowCount
   &LEVEL1_ROW = &LEVEL1(&I);
   &LEVEL2 = &LEVEL1_ROW.GetRowset(SCROLL.EMPL_CHKLST_ITM);
   For &J = 1 to &LEVEL2.ActiveRowCount
      &LEVEL2_ROW = &LEVEL2(&J);
      &RECORD = &LEVEL2_ROW.EMPL_CHKLST_ITM;
   . . .
   End-For;
End-For;
Records Contain Fields
Records are made up of fields. GetField is the default method for a record, so all you have to specify is the field name.
Because we’re processing all the rows at the level 1, we’re just adding code to the previous For loops. The new code is in bold.
For &I = 1 to &LEVEL1.ActiveRowCount
   &LEVEL1_ROW = &LEVEL1(&I);
   &LEVEL2 = &LEVEL1_ROW.GetRowset(SCROLL.EMPL_CHKLST_ITM);
   For &J = 1 to &LEVEL2.ActiveRowCount
      &LEVEL2_ROW = &LEVEL2(&J);
      &RECORD = &LEVEL2_ROW.EMPL_CHKLST_ITM;
      &FIELD = &RECORD.BRIEFING_STATUS;
      /* Do processing */
   End-For;
End-For;
Using Shortcuts
The previous code is the long way of accessing this field. What if you wanted to use all the shortcuts, and access the field in one line of code? Here it is! The following code assumes all rows are 1.
What is current context?
Referencing that portion of the data buffer on which a program is executing is referred to as current context. Most PeopleCode programs run in a current context. The current context determines which buffer fields can be contextually referenced from PeopleCode, and which row of data is the “current row” on each scroll level at the time a PeopleCode program is running.
Can we update the table, which is not in component buffer?
Yes
Data Buffer methods-
Sort method- Using the sort rowset method, it is possible to sort the rows within the rowset based on custom criteria.
Syntax- &Rowset = GetRowSet (SCROLL.);
            &RowSet.Sort (, “A | D” [, “A | D”])
Select method- This method is used to select the rows into a rowset from the select record. The select method is used only with rowsets that reference the component buffers, instantiated using the GetRowSet method or function.
Syntax- &RowSet.Select (RECORD.selrecord, “where…” [,]);
Fill method- This method is used to fill the rowset which does not reference the component buffers. It is used with standalone rowsets instantiated using the CreateRowSet function.
Syntax- Fill (“where..” , );
Flush method- It is used to remove the rows from the buffer before using a Select to bring new rows in. It clears all the rows from the buffer without deleting them from the database.
What are think time functions? What are they?
Think time functions suspends action or processing until the user has taken some action.(such as clicking  ok button in message box).
Do cancel
Do model
Do model component
Exec (when synchronous)
File attach functions
Insert image
We should not write these functions in following events:
Save prechange
Workflow
Row select
Save postchange
What is the difference between functions and procedures?
Functions returns a value, procedures not return any value, package is the combination of functions and procedures
What is Data Buffer?
Data buffer contains multiple component data.
What is a Component Buffer?
Component buffer contains active component data.
What data stores in Component buffer?
Only search record fields are available, If we put one non search key value in 0 level then all levels data will available.
All field values of level 0record are available
Primary record fields are available
Derived work record placed on a component is available
Declared translate fields are available

Ways of debugging in 2 tire and 3 tire

Debugging in two-tier connections involves connecting directly to the database, not through the application server. Use this method to debug two-tier Windows applications.
Use three-tier debugging to debug three-tier Windows applications and PIA applications. For three-tier debugging, use PSADMIN to make sure that the following items are set.
·         The appropriate PSDBGSRV Listener Port is specified in the PeopleCode Debugger section of PSADMIN.
·         At least two PSAPPSRV processes configured to boot in the domain with the Service Timeout parameter set to 0 (zero).
·         You indicate y for Yes at the “Enable PSDBGSRV Server Process” prompt at the end of the PSADMIN interface.
You set these parameters as you configure or create a domain

Tell me few peoplecode functions you worked with?

Hyde
Unhyde
Grey
Ungrey
Substring
Sql execute
All
None
Win message
Win escape
Fetch value
%page
%component
Msgbox
Setlable,
Get field lable
Row count
Active Row count
Field changed
Get rowset
is changed
discord row
prior value
get sql
create sql.
Difference Between Get Record and Create Record?
CreateRecord creates a freestanding record definition and its component set of field objects. The specified record must have been defined previously, that is, it must have a record definition. However, if you are calling this function from PeopleCode associated with a page, the record does not have to be included on the current page.
This function returns a record object that references a new record buffer and set of fields.
EX:  Local Record &REC2;
&REC2 = CreateRecord(RECORD.OPC_METH);
GetRecord creates a reference to a record object for the current context, that is, from the row containing the currently executing program. GetRecord returns a record object.
The following code:
&REC = Get Record ();
Is equivalent to:
&REC = GetRow().GetRecord(Record.recname);
Or
&REC = GetRow().recname;
What is CreateSQL and GetSQL and how they differ with SQL EXEC?
The GetSQL function instantiates a SQL object and associates it with the SQL definition specified by sqlname. The SQL definition must already exist, either created using PeopleSoft Application Designer or the StoreSQL function.
Processing of the SQL definition is the same as for a SQL statement created by the CreateSQL function.
The CreateSQL function instantiates a SQL object from the SQL class and opens it on the given sqlstring and input values. sqlstring is a PeopleCode string value giving the SQL statement.
Any errors in the SQL processing cause the PeopleCode program to be terminated with an error message.
You can use CreateSQL with no parameters to create an empty SQL object that can be used to assign properties before being populated and executed.
HOW WE ACCESSS THE RECORD IN LEVEL 2?
HOW WE ACESS THE DATA ON SECONDARY PAGE WHICH IS ON LEVEL 3?
WHAT IS THE IMPORTANCE OF ROWINIT, ROWSELECT & FIELD CHANGE? IS THERE ANY RELATION SHIP B/W THESE EVENTS?
HOW WE DISPLAY THE SECONDARY PAGE BY USING PEOPLECODE FUNTION
By using DoModel function we can display secondary page
HOW CAN WE OPEN DIFFERNET COMPONENT IN A NEW WINDOW BY CLICKING OF A HYPERLINK?
Yes
HOW WE KNOW WE ARE IN CURRENT PAGE?
%Page
IF WE ARE RUNNING AE PROGRAM THROUH ONLINE PAGE CAN WE USE COMPONENT BUFFER DATA IN AE PEOPLECODE?
IF AN ERROR OCCURS IN SAVE POST CHANGE PEOPLECODE WILL THE DATA MODIFIED BY THE USER IN COMPONENT BUFFER UPDATES IN DATABASE? WHY?
No it will not update the database
If there is same Component X in two different menus, menu1 and menu2 and if u want to assign a different search record for each menu, then which peoplecode function do u use and where do u write the code?
Explain with an example, where you used peoplecode extensively?
What is File Clause?
Difference between component rec field events and record field events?
From Level 1 how can we access level 2 3rd row record field?
Can you assign values to a table, which is not in a Component Buffer?
How can you get a different component by clicking the hyperlink on a page? How can you open a different component in a new window by clicking on a hyperlink?
Tell me component buffer and scroll buffer functions
What is the scope of variables local global derived work field and system
Some Meta strings
If you are running AE through online page can you use component buffer data in ae peoplecode
Data mover commands
Categories
PeopleCode

PeopleCode Examples for Traversing Level 0 to Level 3

Here is the Example Code , Please do modifications based on your requirements.

/*** Demo Code For Traversing Level 0 to Level 3 ***/

Local Rowset &rsLevel0, &rsLevel1, &rsLevel2, &rsLevel3;
Local Row &rowLevel0, &rowLevel1, &rowLevel2, &rowLevel3;
Local Field &fldLevel0, &fldLevel1, &fldLevel2, &fldLevel3;
Local integer &i, &j, &k;

&rsLevel0 = GetLevel0();

/** Get Level 0 Field **/
&fldLevel0 = &rsLevel0.GetRow(1).GetRecord(Record.L0_REC).GetField(Field.L0_FLD);
/** Get Level 0 Field Value **/
&Level0FldValue = &fldLevel0.Value;

&rowLevel0 = &rsLevel0.GetRow(1);
&rsLevel1 = &rowLevel0.GetRowset(Scroll.LEVEL1_RECORD);
For &i = 1 To &rsLevel1.ActiveRowCount

   /** Get Level 1 Field **/
   &fldLevel1 = &rsLevel1.GetRow(&i).GetRecord(Record.L1_REC).GetField(Field.L1_FLD);
   /** Get Level 1 Field Value **/
   &Level1FldValue = &fldLevel1.Value;

   &rowLevel1 = &rsLevel1.GetRow(&i);
   &rsLevel2 = &rowLevel1.GetRowset(Scroll.LEVEL2_RECORD);
   For &j = 1 To &rsLevel2.ActiveRowCount

      /** Get Level 2 Field **/
      &fldLevel2 = &rsLevel2.GetRow(&j).GetRecord(Record.L2_REC).GetField(Field.L2_FLD);
      /** Get Level 2 Field Value **/
      &Level2FldValue = &fldLevel2.Value;

      &rowLevel2 = &rsLevel2.GetRow(&j);
      &rsLevel3 = &rowLevel2.GetRowset(Scroll.LEVEL3_RECORD);
      For &k = 1 To &rs_level3.ActiveRowCount

         /** Get Level 3 Field **/
         &fldLevel3 = &rsLevel3.GetRow(&k).GetRecord(Record.L3_REC).GetField(Field.L3_FLD);
         /** Get Level 3 Field Value **/
         &Level3FldValue = &fldLevel3.Value;
         &rowLevel3 = &rsLevel3.GetRow(&k);

         /* Do Processing  **/
         /* If Any Summation Calculation */
         /* &sum = &sum + &Level3FldValue */

      End-For;
   End-For;
End-For;