Categories
PeopleSoft Install Guide

A Complete Guide to Install PeopleSoft Tools 8.53 / HCM 9.2 with Oracle Database 11g R2 64bit on Windows 8 64bit

Content Coming Soon.

Categories
PeopleSoft Customization

Forgot Password Configuration in PeopleSoft

Steps to setting up the forgotten password link

1. Create a permission list called MAILPSWD (user define name).

2. On the Pages tab of this new Permission List you will need to authorize the component called EMAIL_PSWD. This is done by adding the Menu named MAINTAIN_SECURITY, edit the menu and find the component called EMAIL_PSWD with the description Forgot My Password. Edit the pages and select all.

3. Next go to the Component Interface tab of this same Permission List, add and give full access to the USERMAINT_SELF component.

4. Next go to the Web Libraries tab of this same Permission List and add the WEBLIB_PORTAL and WEBLIB_PT_NAV web libraries edit and give FULL access to ALL iscripts.

5. Create a Role called Forgot Password (user define name). This role will have only 1 permission list, the MAILPSWD permission list you just created. Add this Permission List and save.

6. Create a new User called FORGOTPASSWORD (user define name). Create a password for this user and remember it, as it is case sensitive, and you will be using it again in step 9. The User ID Type for this user should be none and on the roles tab you will want to add the role you just created called Forgot Password. This user DOES NOT need any permission lists on the general tab a symbolic ID or an email address.

7. Open a Permission List that ALL your users have in common and that is assigned to them through a common role, PeopleSoft delivered PTPT1000. On the General tab, of this permission list, turn on the check box that says Allow Password to be Emailed?

8. Under PeopleTools > Security > User Profile > Forgot Password Hint. Add a new password hint (note the ID doesn’t matter – just use 01, 02, etc.). Pick some question that will have a fairly short answer like what is your pet’s name or mother’s maiden name or whatever you feel is appropriate.

9. You also need to go under PeopleTools > Security > User Profile > Forgot My Password Email Text to add whatever comments you want returned on the email you are sending to users. Something like this:
Please reset your password! This process will also NOT unlock your account if it has been locked out. If your account is locked you need to contact the security administrator <<%PASSWORD>>.

10. Set up a web profile for forgotten passwords. This can be done by two ways of following.
 Clone the current existing Web Profile and make a new Web Profile and do the following setup for that.
• Navigate to PeopleTools > Web Profile > Copy Web Profile. Select the PROD web profile to copy from.
• In the “TO” box, enter PWD (User Define name) and save.
[And following configuration]  Just do the following setup in your current Web Profile.
• General Tab, Change description to “Forgot Password”
• Blank out Authentication Domain.
• Uncheck “Enable New Window”.
• Blank out the help URL.
• Uncheck Enable Report Repository.
• Security Tab, Change Days to Auto Fill User ID to 0.
• Check “Allow Public Access”. Enter the Username and Password for the Forgot Password user that you created earlier.
• Blank out XML Link information.
• Debugging Tab, Uncheck everything except for “Write Dump File”. Leave this checked.

11. This step will add the Forgot your password link to the login page.
The link for forgotten passwords will be: http://.:/psp/ps_pwd/EMPLOYEE/ERP/c/MAINTAIN_SECURITY.EMAIL_PSWD.GBL.

Edit signin.html in .$PS_HOME/webserv//applications/peoplesoft/PORTAL/WEB-INF/psftdocs//signin.html.

Search for the string “pwd”. You get few lines something like following:


Above html code written for “Password” level & password user input. So insert “Forgot your password” link just below Password we have to write following codes just below the above html code.

<Ahref=”http://localhost:90/psp/hc92demo/EMPLOYEE/HRMS/c/MAINTAIN_SECURITY.EMAIL_PSWD.GBL””>Forgot your password?

Note: http://localhost:90/psp/hc92demo/ that is my local environment specific .
If you don’t know the values on your URL that you need to add before MAINTAIN_SECURITY, Navigate to ‘Home > PeopleTools > Portal > Structure & Content’ and drill down on the ‘Root -> Tools – Hidden -> Forgot my password’ and test the content reference. Then copy paste the link.

12. Navigate to ‘Home > PeopleTools > Portal > Structure & Content’ and drill down on the ‘Root -> Tools – Hidden -> Forgot my password’ Content reference and check on the ‘No Template’ to ‘Y’ on the ‘General’ tab.

13. For PeopleTools 8.44 and 8.45, you’ll need to modify Component PeopleCode to be able to see the confirmation page after the password has been e-mailed. Open component EMAIL_PSWD view the SavePostChange Peoplecode.

Replace
Transfer(Page.EMAIL_CONFIRM);
with
Transfer( False, MenuName.MAINTAIN_SECURITY, BarName.USE, ItemName.EMAIL_PSWD, Page.EMAIL_CONFIRM, “U”);

Save the component.

NOTE: Passwords that are generated by the Forgot Password function are always in UPPER CASE.

WARNING: If you use a “super user” account for your database (such as PS or VP1) be sure that you have another account that can access the security area before setting this process up because you may end up changing the password for that super user account by accident. This could not only lock yourself out of your database while experimenting with this setup but everyone else too if that user is used to start your application server.

Categories
PeopleSoft CI FAQ

PeopleSoft Component Interface Frequently Asked Question

1) What are the main elements in the component Interface ?
Main elements of component Interface
• Component interface name
• Keys
• Properties and collections
• Methods

2) Difference between Get keys, find keys and Create Keys ?
Get keys: – These are mapped to the fields marked as scrh in the component’s search record. Automatically “Search key” fields in search record become Get keys.
We cannot change it.

Find keys: – These are mapped to fields marked as Alt or Srch in the component search record. You may remove Find keys that you do not wish to make available for searching.
Create Keys: – It is generated from the key fields for the search record. If Add search record is present then its key fields are taken. We cannot change it.

3) How do you provide security for the component interface?
• Open the Permission list
• On the Component Interface tab
• Add row and select the newly created Component Interface
• Edit the permissions to give permission for the standard methods
• Get, Create, Save, cancel, find.

4) What the steps that you need to do in people code to invoke Component Interface?
• Establish a user section
• Get the component interface definition
• Populate the create keys
• Create an instance of the component interface
• Populate the required fields
• Save the component Interface.

&Session = GetSession();
&CI = &Session.GetcompIntfc(CompIntfc.INTERFACE_NAME)
&CI.KEY_FILED_NAME = ‘NEW’
If not &CI.Create () Then

Else
Populate other fields
End-if;

Populate the other fields

If not &CI.Save () Then
Else
End-if;

5) How do you test Component Interface?
• Using the Component Interface tester
• Give values in the tester for options
• Get Existing, Create new, Find and perform the operation from the CI Tester

6) Catching error message in the component Interface? Or
Use of PSMessages in the CI ?
This function needs to be called when ever methods like Find, Save, Create methods return false.
Error text and Error type can be printed in the log message for any other action in to the log messge.

Function CheckErrorCodes()

&PSMessages = &Session.PSMessages;
&ErrorCount = &PSMessages.Count;
For &i = 1 To &ErrorCount
&ErrorText = &PSMessages.Item(&i).Text;
&ErrorType = &PSMessages.Item(&i).Type;
End-For;

End-Function;

7) What is method? What are the different types of method?
Methods: – A method is an object that performs a very specific function on a component interface at run-time.
Standard methods and user-defined methods.
Standard methods: – Automatically generated upon the creation of a new component Interface in Application.
Apart from the Standard methods there are Standard methods available for the use with any collection.
User-Defined methods: – User-defined methods are those that you can create to meet the requirements of an individual component interface.

8) What are properties?
The Fields in the level 0 in the component are the properties of the component.

Standard properties User-Defined properties
Createkeyinfocollection Developer can further control the exposed Getkeyinfocollection field properties.
Findkeyinfocollection
Property Info collection
GetHistoryItems (Update/Display mode or Correction mode)
EditHistory Items
InteractiveMode.

9) Traversing the Collections in the Component Interface?
COLL_JOB – Collection
Coll_JOBItm – Row in the collection.
&COLL_JOBCol = &CI_JOB_DATA.COLL_JOB;
For &i = 1 to &COLL_JOBCol.Count
&COLL_JOBItm = &COLL_JOBCol.Item (&i);
&COLL_JOB_JRCol = &COLL_JOBItm.COLL_JOB_JR;
For &J = 1 to &COLL_JOB_JRCol.Count
&COLL_JOB_JRItm = &COLL_JOB_JRCol.Item (&j);
&COLL_JOB_JRItm.KEYPROP_EFFDT =;

10) How do you login in correction mode in the Component Interface?
Get History Items and Edit History items property to should be set to true.
Get History Items alone: – Update display all – modes will be used.

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.