1. Where do you run a Query?
To run a query:
a) Select Reporting Tools, Query, Query Manager.
Query Manager Search page appears.
b) Search for a query using the basic or advanced search function. Both search types allow you to use the following search by criteria: access group name, description, folder name, owner, query name, type, uses field name, and uses record name. The basic search allows you to search using the begins with condition. The advanced search allows you to perform a progressively narrower search by using one or more search by criteria and selecting from a broad choice of conditions.
c) To perform an advanced search, click the Advanced Search link on the Query Manager Search page. On the Advanced Search page, select the appropriate search by criteria and conditions, then enter a search string in each of the corresponding fields.
d) Click the Search button to display a list of queries that match your search criteria. The Search Results page appears. The results list all the queries that match the search criteria. The following information appears:
· Query name.
· Query description.
· Owner (public or private).
· Folder.
e) Scroll to the name of the query that you want to run.
By default, only the first 30 queries appear on the page. To see more of the list, click the navigation buttons and links located on the header bar.
To display 100 of the queries, select View 100 and use the scrollbar to view the rest of the list.
f) To run a query, click the query name. You may also select from the following links corresponding to the query name:
- Run to HTML: Click to generate an HTML version of the query.
- Run to Excel: Click to download the query to an Excel spreadsheet.
- Schedule: Click to schedule when the query will run
- What are the different types of Records? Which of these can we build?
The different Types:
a) SQL Table*
b) SQL View*
c) Dynamic View
d) Derived/Work
e) SubRecord
f) Query View*
g) Temporary Table*
The ones with asterisks (*) can be build.
- What is a Sub-Record?
A SubRecord is a cluster/logical grouping of fields. You can edit PeopleCode that is attached to a subrecord field by double-clicking the appropriate field as it appears in the expanded record. Any PeopleCode changes that you make apply to all records that contain that subrecord.
- Does a Sub-Record need a key?
No.
- Do you need to build a Sub-Record?
No
- What are the different types of keys of CI?
| Key Type | Key Characteristics |
| These keys automatically map to search key fields in the search record for the underlying component. You must change Get keys only if you modify the keys of the component after you create a component interface. | |
| These map to both search key fields and alternate search key fields in the search record for the underlying component. You may remove any Find keys based on alternate search key fields that you don’t want to make available for searching. | |
| If the underlying component allows the Add action, then Create keys are generated for the component interface automatically. They map to fields marked as Srch (search) in the search record for the component (or the add search record, if one is specified). |
- When was CI introduced?
Version 8.4
- How do you run a SQR or Crystal Report?
Process Scheduler
- What is the difference between Field Change and Field Edit?
FieldChange fires whenever the value of the field is changed. FieldEdit validates the value of the field and fires during the time of save to the database. FieldChange fires prior to FieldEdit.
- What is SearchEdit?
If this property is set to True, SearchEdit enables system edits (edits specified in the record field definition) for the field, for the life of the search dialog box. Setting SearchDefault to False disables system edits. In the Add mode search dialog box, the following edits are performed when the end-user clicks the Add button. In any other mode, the following edits are performed when the end-user clicks the Search button:
· Formatting
· Required Field
· Yes/No Table
· 1/0 Table
· Translate Table
· Prompt Table
- What type of code is put in SearchInit and SearchSave?
The SearchInit event is generated just before a search, add, or data-entry dialog box is displayed. SearchInit triggers associated PeopleCode in the search key fields of the search record. This enables you to control processing before a user enters values for search keys in the dialog box. In some cases, you may want to set the value of the search dialog fields programmatically. For example, the following program in SearchInit PeopleCode on the component search key record field EMPLID sets the search key page field to the user’s employee ID, makes the page field unavailable for entry, and enables the user to modify the user’s own data in the component:
EMPLID = %EmployeeId;
Gray (EMPLID);
AllowEmplIdChg(true);
SearchSave PeopleCode is executed for all search key fields on a search, add, or data-entry dialog box after a user clicks Search. This enables you to control processing after search key values are entered, but before the search based on these keys is executed. A typical use of this feature is to provide cross-field edits for selecting a minimum set of key information. This event is also used to force a user to enter a value in at least one field, even if it’s a partial value, to help narrow a search for tables with many rows.
- What are the types of variables in SQR? What are their scopes?
Syntax
DECLARE-VARIABLE
[DEFAULT-NUMERIC={DECIMAL[(prec_lit)]|FLOAT|INTEGER}]
[DECIMAL[(prec_lit)]num_var[(prec_lit)][num_var
[(prec_lit)]]...]
[FLOAT num_var[num_var]...]
[DATE date_var[date_var]...]
[INTEGER num_var[num_var]...]
[TEXT string_var[string_var]...]
END-DECLARE
Description
Enables the user to explicitly declare a variable type.
- How to declare variables in SQR?
begin-setup
declare-variable
default-numeric=float
decimal #decimal(10)
integer #counter
date $date
end-declare
end-setup
LET dst_var=expression
Assigns the value of an expression to a string, numeric, or date variable
- How to generate multiple files from a single SQR?
Reviewing the Sample Program for Multiple Reports
The following sample program, ex18a.sqr, shows how SQR for PeopleSoft enables you to write multiple reports with different layouts and different heading and footing sections. The sample program prints three reports: the labels from Printing Mailing Labels, the form letter from Creating Form Letters,and the listing report from Selecting Data from the Database. All three reports are based on the same data.
Program ex17b.sqr
#define MAX_LABEL_LINES 10
#define LINES_BETWEEN_LABELS 3
Begin-setup
declare-layout labels
paper-size=(10,11) left-margin=0.33
end-declare
declare-layout form_letter
end-declare
declare-layout listing
end-declare
declare-report labels
layout=labels
end-declare
declare-report form_letter
layout=form_letter
end-declare
declare-report listing
layout=listing
end-declare
End-setup
begin-program
do main
end-program
begin-procedure main
do init_mailing_labels
begin-select
name
addr1
addr2
city
state
zip
move &zip to $zip xxxxx-xxxx
phone
do print_label
do print_letter
do print_listing
from customers
end-select
do end_mailing_labels
end-procedure ! main
begin-procedure init_mailing_labels
let #label_count = 0
let #label_lines = 0
use-report labels
columns 1 29 57 ! enable columns
alter-printer font=5 point-size=10
end-procedure ! init_mailing_labels
begin-procedure print_label
use-report labels
print &name (1,1,30)
print &addr1 (2,1,30)
let $last_line = &city || ', ' || &state || ' ' || $zip
print $last_line (3,1,30)
next-column at-end=newline
add 1 to #label_count
if #current-column = 1
add 1 to #label_lines
if #label_lines = {MAX_LABEL_LINES}
new-page
let #label_lines = 0
else
next-listing no-advance skiplines={LINES_BETWEEN_LABELS}
end-if
end-if
end-procedure ! print_label
begin-procedure end_mailing_labels
use-report labels
use-column 0 ! disable columns
new-page
print 'Labels printed on ' (,1)
print $current-date ()
print 'Total labels printed = ' (+1,1)
print #label_count () edit 9,999,999
end-procedure ! end_mailing_labels
begin-procedure print_letter
use-report form_letter
begin-document (1,1)
&name
&addr1
&addr2
@city_state_zip
.b
.b
$current-date
Dear Sir or Madam:
.b
Thank you for your recent purchases from ACME Inc. We would
like to tell you about our limited time offer. During this month,
our entire inventory is marked down by 25%. Yes, you can buy your
favorite merchandise and save too.
To place an order simply dial 800-555-ACME.
Delivery is free too, so don't wait.
.b
.b
Sincerely,
Clark Axelotle
ACME Inc.
end-document
position () @city_state_zip
print &city ()
print ', ' ()
print &state ()
print ' ' ()
move &zip to $zip xxxxx-xxxx
print $zip ()
new-page
end-procedure ! print_letter
begin-heading 4 for-reports=(listing)
print 'Customer Listing' (1) center
print 'Name' (3,1)
print 'City' (,32)
print 'State' (,49)
print 'Phone' (,55)
end-heading
begin-footing 1 for-reports=(listing)
! Print "Page n of m" in the footing
page-number (1,1) 'Page '
last-page () ' of '
end-footing
begin-procedure print_listing
use-report listing
print &name (,1)
print &city (,32)
print &state (,49)
print &phone (,55)
position (+1)
end-procedure ! print_listing
The SETUP section defines three layouts and three different reports that use these layouts. The labels report requires a layout that is different from the default. The other two reports use a layout that is identical to the default layout. You can save the last layout declaration and use the form-letter layout for the listing. However, unless there is a logical reason why the two layouts should be the same, it is better to keep separate layouts. The name of the layout indicates which report uses it.
The main procedure performs the Select. It is performed only once and includes all of the columns for all of the reports. The phone column is used only in the listing report and the addr2 column is used only in the form-letter report. The other columns are used in more than one report.
For each record that is selected, three procedures are run. Each procedure processes one record for its corresponding report. The print_label procedure prints one label, print_letter prints one letter, and print_listing prints one line into the listing report. Each procedure begins by setting the SQR printing context to its corresponding report. SQR sets the printing context with the USE-REPORT command.
- What is the difference between a File and a Report?
A File is generated for further processing of the data.It is .txt,.dat etc.
A Report is usually for the end-user,like .pdf.
- How to have input parameters in SQR?
ask my_order
…
…
order by {my_order}
The ASK command prompts the user for the value
- What are SQCs? Name some. Where are they stored?
SQCs are stored procedures.
#Include 'curdttim.sqc' !Get-Current-DateTime procedure
#Include 'taxrnctl.sqc' !Get-Tax-Reporting-Run-Controls procedure
#Include 'getcodta.sqc' !Get-Company-Data procedure
#Include 'getstdta.sqc' !Get-State-Tax-Data procedure
#Include 'datetime.sqc' !Routines for date and time formatting
#Include 'number.sqc' !Routines to format numbers
#Include 'rotname9.sqc' !Get-Hours procedure
#Include 'rptsmmry.sqc' !Print-Summary-Data procedure
#Include 'getfrmat.sqc' !Get-Diskette-Format procedure
#Include 'getbalid.sqc' !Get-Calendar-Year-Id
#Include 'stdapi.sqc' !Update Process API
#Include 'txrnctl1.sqc' !Get-Run-Control procedure
#Include 'getweksa.sqc' !Get-Weeks procedure
#Include 'datemath.sqc' !Date math procedures
#Include 'gethours.sqc' !Get-Hours procedure
They are stored in SQR folder in the server.
- What is the difference between Exit (0) and Exit (1)?
Use the On Return value to determine how your Application Engine program reacts based on the return of your PeopleCode program. The On Return setting takes effect if your PeopleCode program issues a “return 1” or “exit 1.” You can use the True keyword in place of a non-zero numeric return.
When you specify the On Return property, you choose from the following values:
| Abort | The program issues an error and exits immediately. |
| Break | The program exits the current step and section, and control returns to the calling step. |
| Skip Step | The program exits the current step, and continues processing at the next step in the section. If this is the last step in the section, the calling step resumes control of the processing. |
- What is the difference Standard page, Sub-page and Secondary page?
| Adds an invisible control that associates a secondary page with the primary page. You then associate the secondary page with a command push button or link or a pop-up menu. Secondary pages gather or display supplemental information that is related to the data in a primary page but less frequently referenced or updated. Secondary pages are displayed using the DoModal PeopleCode function. | |
| Adds a predefined, presized group of controls, such as address controls, that are defined on a separate subpage definition. During design time, you add only the subpage control to represent all of the controls in the subpage. You maintain those controls in only one place—the subpage definition. At runtime, you see all of the controls that are defined in the subpage on the page. | |
| Standard Page | Normal Page |
- How to attach a sub-page in a Standard page?
In order to support the use of subrecords with subpages, the system uses a record name substitution mechanism for subpage-type page fields. Each subpage has a "from" record, determined dynamically by the page definition in PeopleSoft Application Designer and by the PeopleSoft Internet Architecture (PIA). The "from" record is a record associated with one of the page fields in the subpage. Which page field depends upon the page field ordering, the types of page fields on the subpage, and whether it's record is a subrecord. The subpage itself must be fully expanded in order to make this determination. (This is important to keep in mind when working with subpages that contain other subpages.)
The "to" record is specified by you in the SubPage Record Name Substitution group box in the Insert Subpage dialog box that displays after you insert the subpage into a page (and on the Subpage tab of the Subpage Properties dialog). At runtime, when a subpage-type page field is expanded, each page field that is associated with the dynamically determined "from" record is instead associated with the "to" record you specify. This means, for example, that a subpage can be populated with page fields that are associated with a subrecord so that the subrecord is determined to be the "from" record. Then when that subpage is used as a subpage-type page field, you can specify the "to" record as one of the records that contains that subrecord. This enables you to associate a single set of page fields (through the subpage) with a variety of different records (each containing the subrecord).
Inserting a Subpage Into a Page
After you create the subpage definition, insert a subpage control on your primary page and associate it with the subpage definition.
To insert a subpage into a page:
- Open the page on which you want to insert the subpage.
- Select Insert, SubPage.
The Insert Subpage dialog box appears.
- Select a subpage definition.
Specify the name of the subpage that you want to insert into the open page definition.
- Select a record definition in the SubPage Record Name Substitution group box.
The subpage writes its fields to a generic record. Specify the application-specific record name to which you want the information in the subpage written.
.
Note. All of the fields in a subpage must be associated with fields of a subrecord in the specified record definition.
- Click OK.
The subpage appears on the page. Its size reflects the size of the page control group, and it is identified by the subpage definition name. During design time, the fields within the subpage are not visible. At runtime, pages containing subpages are dynamically expanded. Be sure to leave a few pixels of space between the left edge of the main page and the subpage page field to ensure sufficient padding at runtime.
When saving a page with multiple subpages, be aware that you may receive a warning message if duplicate fields are present.
Inserting a Subpage Into a Grid
You can insert a subpage into a grid as you would any other column.
- What are the different Actions in AE? What is the order of execution?
The following diagram depicts the sequence and level of execution for each type of action:
You can only define XSLT type actions for programs defined as Transformation types
- Which actions are mutually exclusive?
You cannot have a SQL action and a Call Section action within the same step
- What is the difference between “Do Select”, “Do While”, “Do When” and “Do until”?
When using a Do When action, consider the following:
- The Do When action is a Select statement that allows subsequent actions to be executed if any rows of data are returned.
- This action is similar to a COBOL If statement.
A Do When statement runs before any other actions in the step. If the Do When statement returns any rows, the next action is executed. If the Do When conditions are not met, the remaining actions within that step are not executed. Your program executes a Do When action only once when the owning step executes.
- The only property that you can specify for the Do When action is the ReUse Statement property, which applies to all SQL-based actions.
The Do While action is a Select statement that, if present, runs before subsequent actions of the step. If the Do While does not return any rows of data, the action terminates. The Do While is identical to the COBOL While statement. Subsequent actions within the step are executed in a loop as long as at least one row is returned by the Select statement for the Do While action. If the Do While does not return any rows, the step is complete.
The only property that you can specify for the Do While action is the ReUse Statement property, which applies to all SQL-based actions.
A Do Until action is a Select statement that runs after each action when a step completes. If the Select statement returns any rows of data, the step terminates.
- Use a Do Until action if you want the processing actions to execute at least once, and to execute over and over until a certain condition is true, such as until a Select statement returns some rows.
- You can also use a Do Until action to stop a Do Select action prematurely.
For example, if the Select statement for the Do Until action does not return any rows, then the actions in the step are repeated (except if a Do When action appears in the step). Normally, a Do Select action continues until no rows are returned. If any rows of data are returned, the Do Select action stops and the step is not repeated.
- The only property that you can specify for the Do Until action is the ReUse Statement property, which applies to all SQL-based actions.
The Do Select action is a Select statement that executes subsequent actions once for every row of data that the Do Select returns. For instance, a Do Select can execute a SQL statement for each row returned from the Select statement. The subsequent actions within the step are executed in a loop based on the results of the Select statement. The type of the Do Select determines the specific looping rules.
- What is the difference between SQLExec and CreateSQL and SELECT ?
The SQL object should subsequently be the subject of a series of Fetch method calls to retrieve the selected rows.
If you want to fetch only a single row, use the SQLExec function instead
&SQL = CreateSQL("%SelectAll(:1) where EMPLID = :2", RECORD.ABSENCE_HIST, &EMPLID);
If you want to fetch a single row into a PeopleCode record object, use the record Select method.
&LEVEL0.Select(RECORD.PERSONAL_DATA, "WHERE. . .");
- How can we bring a Record in a Component Buffer?
| Type and Location of Record | Presence in Component Buffer |
| Primary record on scroll levels greater than zero | On scroll levels greater than zero, all record fields from the primary scroll record are in the component buffer. PeopleCode can refer to any record field on the primary scroll record, even if it is not associated with a page control. |
| Primary record on scroll level zero | If scroll level zero of a page contains only controls associated with primary scroll record fields that are search keys or alternate search keys, then only the search key and alternate search key fieldsare in the component buffer, not the entire record. The values for the fields come from the keylist, and the record cannot run RowInit PeopleCode. If level zero contains at least one record field from the primary scroll record that is not a search key or alternate search key, then all the record fields from the primary scroll record are available in the buffer. (For this reason, you may sometimes need to add one such record field at level zero of the page to make sure that all the record fields of the level-zero primary record can be referenced from PeopleCode.) |
| Related display record fields | The buffer contains the related display record field, plus any record fields from the related display record that are referenced by PeopleCode programs. You can reference any record field in a related display record. |
| Derived/work record fields | Only derived/work record fields associated with page controls are in the component buffer. Other record fields from the derived/work record cannot be referenced from PeopleCode. |
| Translate table record fields | Only Translate table fields associated with page controls are available in the component buffer. Other fields from the Translate table cannot be referenced from PeopleCode. |
- What is the difference between State Record and Temp Record?
A state record is a PeopleSoft record that must be created and maintained by the Application Engine developer. This record defines the fields a program uses to pass values from one action to another. An Application Engine state record can be either a physical record or a work record, and any number of state records can be associated with a program. Physical state records must be keyed by process instance.
Temporary Tables:
Because Application Engine programs run in batch mode, multiple instances of the same program often execute in parallel. When this happens, there is a significant risk of data contention and deadlocks on tables. To avoid this, you can dedicate specific instances of temporary tables for each program run. Any number of programs, not just Application Engine programs, can use the temporary table definitions Insert the PROCESS_INSTANCE field as a key on any temporary tables that you intend to use with PeopleSoft Application Engine. PeopleSoft Application Engine expects Temporary Table records to contain the PROCESS_INSTANCE field.
- What is parallel processing?
Parallel processing is used when considerable amounts of data must be updated or processed within a limited amount of time, or batch window. In most cases, parallel processing is more efficient in environments containing multiple CPUs and partitioned data.
To use parallel processing, partition the data between multiple concurrent runs of a program, each with its own dedicated version of a temporary table (for example, PS_MYAPPLTMP). If you have a payroll batch process, you could divide the employee data by last name. For example, employees with last names beginning with A through M get inserted into PS_MYAPPLTMP1; employees with last names beginning with N-Z get inserted into PS_MYAPPLTMP2.
- What are the functions and methods of RowSet Class?
Functions: CreateRowset,GetLevel0,GetRowset
Methods: CopyTo, DeleteRow, Fill, FillAppend, Flush, FlushRow etc
- Why do we need CI?
To fire PeopleCode associated with the Component.
- What is the difference between Interactive Mode and Deferred Mode and Allow Expert Entry?
| Interactive | Select to send messages to the server whenever logic must be run. When the user presses Tab to exit a field-level event, a transmission to the application server occurs to run that field-level event and the page is refreshed. |
| Deferred | Selected as the default. This mode reduces transmissions to the application server. |
| Allow Expert Entry | This is available for both processing modes. |
- Which are the places we can set Interactive and Deferred Mode?
Component, Page and Page Field.
- What is the Interactive Mode in CI?
In CI if the parent component is in Deferred mode, InteractiveMode = True will not have any effect,but if it is in Interactive Mode then it functions accordingly.
- What is BootStrap Mode and
Operating modes determine how you are connected to the database. PeopleSoft Data Mover modes are:
Most of the time, you use regular mode. To sign in to regular mode, enter your PeopleSoft user ID and password during sign-in. In regular mode, all commands are valid.
In bootstrap mode, you use a database access ID and password when signing in. Typically, you use bootstrap mode for database loading, because no PeopleSoft security tables (like PSOPRDEFN) are established yet. You also use bootstrap mode for running some security commands, such as ENCRYPT_PASSWORD
- Explain permission List.
- Explain the process of Application Designer.
Field à Record à Page à Component à Permission Access à Menu à Registering a menu Access
- How to write dynamic WHERE clause in SQR?
- What is the PeopleSoft Architecture?
Logical application server configuration
- What are EffDt and EffSeq?
EffDt is required to maintain the history data.
EffSeq is used to store multiple rows of data entered in the system on the same day.
- What is DoModal?
Use the DoModal function to display a secondary page. Secondary pages are modal, meaning that the user must dismiss the secondary page before continuing work in the page from which the secondary page was called.
- What is Parent-Child relationship?
Record B is the child of Record A when the keys of B are same and in the identical order of the keys of A plus an extra key field.
- What is the difference between Scroll and Grid?
A Scroll can have grid within it but a grid cannot have a scroll within it.
We use a scroll when quite a lot of values of a particular record have to be displayed.
We use grid when many rows of the record have to be displayed.
- How many (Scroll) Levels are there?
0,1,2,3.
- What is the difference between a Dynamic View and a SQL View?
If you are creating a SQL view or dynamic view record definition, you must enter a SQL view Select statement, to indicate which field values you want to join from which tables. The only difference between the standard view and dynamic view is that the dynamic view is not defined as a view to the database—it is stored on the client and run as a Select at runtime
- How many types of Audits are there?
a) Record Level: To audit at the record level, you must create a record definition and SQL table in which you store audit information. When you create a new audit record definition, name it with an AUDIT_ prefix
b)Field Level: Value stored in PSAUDIT.
- How to schedule a job/process?
- What are Process Groups?
Make the process definition a member of the group. A process definition can be a member of multiple process groups. Process groups are then assigned to security profiles in PeopleSoft Security Administrator, which enables you to specify the process requests that classes of users can run.
- Can you call SQR from AE and vice-versa? How?
We can call an SQR from AE but not an AE from an SQR.
&TBS_RQST1 = CreateProcessRequest();
/* Set all the Required Properties */
&TBS_RQST1.RunControlID = &TBS_RUN_CONTROL;
&TBS_RQST1.ProcessType = "PSJob";
&TBS_RQST1.RunLocation = "Server";
&TBS_RQST1.JobName = "TBSCIRC1";
/* Schedule the Process */
&TBS_RQST1.Schedule();
- What is the major difference AE and SQR?
AE is much structured. CI can be called from AE.
- Why do you need transformation?
To convert Incoimg Messages to PeopleSoft formatted -PSCAMA capped message for further processing.
To convert Outgoing Messages from PeopleSoft formatted -PSCAMA capped message to the desired/required Message format for further processing.
- Without using the wizard how can we register a menu?
- How will you give “insert” and “update” statements in SQR?
Begin-SQl
….
….
End-Sql.
- How do you use “For-Loop” in SQR?
There is no FOR-loop in SQR.
- What is Meta-SQL?
This property returns the SQL that represents the query, unresolved for any platform.
- How many maximum fields can a record have?
255
- What is the basic structure required to send and receive message?
Listening connectors receive message requests from integration participants, send them to the gateway manager, and deliver responses back to the integration participants. The following diagram shows the flow of an inbound message from an external system into the integration engine through a listening connector:
Inbound message flow
Target connectors generate message requests, send them to integration participants, wait for responses from participants, and deliver the responses back to the gateway manager, as shown in the following diagram:
Message flow through a target connector
- What is display control field and related display field?
- What are EditHistoryItem and GetHistoryItems in CI?
This property works with the EditHistoryItems property to specify what data is accessed, and whether you can edit that data:
- If EditHistoryItems is False (the default) and GetHistoryItems is True, you access the data in the Component Interface in a similar manner as if you were accessing a component in Update/Display All mode. This means all history rows are returned, however, you can edit only rows with a date set in the future.
- If EditHistoryItems is True and GetHistoryItems is True, you access the data in the Component Interface in a similar manner as if you were accessing a component in Correction mode. This means all history rows are returned, and you can edit them.
- If GetHistoryItems is False, you access the data in the Component Interface in a similar manner as if you were accessing a component in Update mode. The EditHistoryItems has no effect when GetHistoryItems is False.
- What are the different types of queries?
Create and run queries to retrieve data from the database directly from Windows-based Query Designer, or the web-based Query Manager/Query Viewer applications.
Note. Because of the range of possible circumstances in which you might run an ad hoc query, there are no special considerations or requirements that apply to all of them.
- Reporting queries.
Reporting queries are essentially the same as user queries, except that they are designed to be used by another reporting tool. Reporting queries can be used as data sources for ad hoc queries, scheduled queries, Crystal Reports, PS/nVision, or Cube Manager.
- Process queries.
Write queries that are intended to run periodically by batch processes, most likely using PeopleSoft Application Engine and the Query API (application programming interface).
- Role queries.
Write queries that PeopleSoft Workflow uses to determine to whom to send emails, forms, or worklist entries.
- Archive queries.
You can save a query as this type if you have access to workflow queries. These queries are generally only used by the PeopleSoft Data Archive Manager.
- What do you do if there is any error in Begin-Select ?
begin-select on-error=give_warning
…
…
begin-procedure give_warning
- What are the properties of CI ?
This table contains the component interface properties.
- What is the different SQR Process and SQR Report?
A SQR Process is when update/insert to tables are involved.That is, a process is involved.
A SQR Report is generation of summary of the process (for end user).
- What is Search Record and Add Search Record ?
Access the Component Properties dialog box. Select the Use tab.
Component Properties dialog box: Use tab
Access
| Search record | Specify the search record for this component. |
| Add search record | Specify if you want a different search record specifically for add actions. For example, if you selected the auto-numbering option for employee IDs (EMPLID), don’t include EMPLID in the search record. Likewise, you might want to create special security views for add actions that limit the rows that users can add, based on specific search criteria. The system default is the standard search record if you don’t specify an add search record. |
| Force Search Processing | Select to always run search logic (SearchInit PeopleCode) for this component. The default is cleared. |
- What is file relative and file absolute ?
&MYFILE = GetFile("c:\temp\item*.txt", "N", %FilePath_Absolute);
Stored in the Application Server.
&TBS_ERR_FILE = "TBS_BILLING_ERR_" | &TBS_DateTime | ".log";
&TBS_fileLOG = GetFile(&TBS_ERR_FILE, "w", %FilePath_Relative);
- How do you open and close a file in SQR?
- What do you write at the beginning of CI ?
a) Function name_errorHandler()
b) Try – catch
- Name some SQCs required in SQRs.
#Include 'curdttim.sqc' Ã Get-Current-DateTime procedure
#Include 'taxrnctl.sqc' Ã Get-Tax-Reporting-Run-Controls procedure
#Include 'getcodta.sqc' Ã Get-Company-Data procedure
#Include 'getstdta.sqc' Ã Get-State-Tax-Data procedure
#Include 'datetime.sqc' Ã Routines for date and time formatting
#Include 'number.sqc' Ã Routines to format numbers
#Include 'rotname9.sqc' Ã Get-Hours procedure #Include 'rptsmmry.sqc' Ã Print-Summary-Data procedure
#Include 'getfrmat.sqc' Ã Get-Diskette-Format procedure
#Include 'getbalid.sqc' Ã Get-Calendar-Year-Id
#Include 'stdapi.sqc' Ã Update Process API
#Include 'txrnctl1.sqc' Ã Get-Run-Control procedure
#Include 'getweksa.sqc' Ã Get-Weeks procedure
#Include 'datemath.sqc' Ã Date math procedures
#Include 'gethours.sqc' Ã Get-Hours procedure
- Where are .dms scripts stored?
- What is User Id, Access Id, Symbolic Id and Connect Id?
A PeopleSoft user ID is the ID you enter at the PeopleSoft sign-in dialog box. You assign each PeopleSoft user a user ID and password. The combination of these two items grants users online access to the PeopleSoft system. The system can also use a user ID stored within an LDAP directory server.
The user ID is the key used to distinctly identify the user profile definition.
The connect ID performs the initial connection to the database.
Note. PeopleSoft no longer creates users at the database level.
A connect ID is a valid user ID that, when used during sign-in, takes the place of PeopleSoft user IDs. Using a connect ID means you don’t have to create a new database user for every PeopleSoft user that you add to the system.
Note. A connect ID is required for a direct connection (two-tier connection) to the database. Application servers and two-tier Microsoft Windows clients require a connect ID. You specify the connect ID for an application server in the Signon section of the PSADMIN utility. For Microsoft Windows clients, you specify the connect ID in the Startup tab of PeopleSoft Configuration Manager. You can create a connect ID by running the Connect.SQL and Grant.SQL scripts.
Warning! Without a connect ID specified, the system assumes that workstation is accessing PeopleSoft through an application server. The option to override the database type is disabled.
When you create any user ID, you must assign it an access profile, which specifies an access ID and password.
The PeopleSoft access ID is the RDBMS ID with which PeopleSoft applications are ultimately connected to your database after the PeopleSoft system connects using the connect ID and validates the user ID and password. An access ID typically has all the RDBMS privileges necessary to access and manipulate data for an entire PeopleSoft application. The access ID should have Select, Update, and Delete access.
Users do not know their corresponding access IDs. They just sign inwith their user IDs and passwords. Behind the scenes, the system signs them into the database using the access ID.
If users try to access the database directly with a query tool using their user or connect IDs, they have limited access. User and connect IDs only have access to the few PeopleSoft tables used during sign-in, and that access is Select-level only. Furthermore, PeopleSoft encrypts all sensitive data that resides in those tables.
Note. Access profiles are used when an application server connects to the database, when a Microsoft Windows workstation connects directly to the database, and when a batch job connects directly to the database. Access profiles are not used when end users access applications through PIA. During a PIA transaction, the application server maintains a persistent connection to the database, and the end users leverage the access ID that the application server domain used to sign in to the database.
Note. PeopleSoft suggests that you only use one access ID for your system. Some RDBMS do not permit more than one database table owner. If you create more than one access ID, it may require further steps to ensure that this ID has the correct rights to all PeopleSoft system tables.
PeopleSoft encrypts the access ID when it is stored in the PeopleTools security tables. Consequently, an encrypted value can’t be readily referenced nor accessed. So when the access ID, which is stored in PSACCESSPRFL, must be retrieved or referenced, the query selects the appropriate access ID by using the symbolic ID as a search key.
The symbolic ID acts as an intermediary entity between the user ID and the access ID.All the user IDs are associated with a symbolic ID, which in turn is associated with an access ID. If you change the access ID, you need to update only the reference of the access ID to the symbolic ID in the PSACCESSPRFL table. You do not need to update every user profile in the PSOPRDEFN table.
- What is the difference Business Unit and SetId?
Eg: For Hexaware we need 4 SetIds (SHARE,HEXMUM,HEXCHE,HEXBAN) and 3 BUSINESS UNITS (1 BU for each location).
- What is the difference PS_JOB and PS_EMPLOYMENT?
PS_JOB is Effective dated, while PS_EMPLOYMENT is not.
PS_JOB:
PS_EMPLOYMENT:
- How can you restart an App Engine? What is to be kept in mind while restarting an App Engine?
One key feature of PeopleSoft Application Engine is its built-in checkpoint and restart capabilities. If there is an abnormal termination or failure on a step in the program, you can restart the request from the last successful checkpoint, or the step immediately preceding the step that failed. You restart the program from the process request page.
This section provides an overview of restart and discusses how to:
- Determine when to use restart.
- Control abnormal terminations.
- Restart Application Engine programs.
- Start Application Engine programs from the beginning.
- Enable and disable restart.
Application Engine programs save to the database (perform a commit) only when an entire program successfully completes. You must set any individual commits where appropriate.
At the section level, you can set a commit after each step in that section. At the step level, you can require or defer commits for individual steps, or you can increase the commit frequency within a step to N iterations of a looping action within a step, such as a Do Select or Do While action.
The commit level that you select plays a major role in how restart works in a program. Each time that PeopleSoft Application Engine issues a commit with restart enabled, it records the current state of the program. The recording of the current state that PeopleSoft Application Engine performs is referred to as a checkpoint.
Using the restart feature enables you to perform commits more often in a program. Restart reduces the overall impact on other users and processes while the background program is running, because it reduces the amount of rows that are locked by the program, allowing multiple instances of the program to run concurrently (parallel processing), which may be useful for high-volume solutions.
With restart, if a failure occurs at any point in the process, the user can restart the program and expect the program to behave in the following manner:
- Ignore the steps that have already completed up to the last successful commit.
- Begin processing at the next step after the last successful commit.
The ability for PeopleSoft Application Engine to“remember completed steps depends on a record called AERUNCONTROL, which is keyed by process instance.
When a program runs, each time PeopleSoft Application Engine issues a commit it also saves all of the information required for a program restart in the AERUNCONTROL record.
Usually, you want to develop programs to take advantage of PeopleSoft Application Engine restart capabilities. Programs that are good candidates for restart do a lot of preparation work up front, like joining tables and loading data into temporary work tables. Also, programs that might put data in an unstable state if they terminate abnormally during a run should be considered to take advantage of restart. As a general rule, restart is essential for programs that primarily do set-based processing.
However, if your program has one the following characteristics, you may want to disable restart:
- It’s mainly row-by-row processing.
- The overhead involved with PeopleSoft Application Engine performing a checkpoint during the program run is not desirable.
- The program commits after N iterations of a looping construct within a step, and the Select statement driving the loop is composed in such a way that if the program terminated and then started again, it would ignore transactions that were already processed in the previous program run. In this sense, the program processes the restart internally, in that PeopleSoft Application Engine treats each start of a program as a fresh start, instead of restarting a previous instance.
When developing for restart, consider the consequences if a program fails and you can’t restart the program. Given the commit structure that you’ve defined for your Application Engine program, would your data remain in an usual state if a failure were to occur after any of the commits? Would it be easy to recover from such a case?
Using Restart at the Program Level
PeopleSoft Application Engine automatically performs all state record updates. When an Application Engine program starts, it inserts a row in the state record for the assigned process instance. Then the system updates the state record whenever the program performs a commit to store changed values into the database. Finally, the state record row is deleted upon successful completion of the application.
However, if the state record the program uses is a work record, no database updates can be made to the record. Consequently, if you restart the program, you might get unexpected results, because the memory was lost when the program terminated. In fact, the system reinitializes any state records that are work records at each commit, to ensure consistent behavior during a normal run and a restarted run. Therefore, you may need to make at least one of your state records a SQL table to contain values that must be retained across commits or in case of termination.
Finally, the other consideration for programming for restart at the program level is to check both the PeopleSoft Application Engine Program Properties dialog box and PeopleSoft Configuration Manager to make sure that Disable Restart check box is not selected.
Using Restart at the Section Level
The section level property associated with restart is section type, which has the options Prepare Only and Critical Updates.
If a section is only preparing data, as in selecting it, populating temporary tables, or updating temporary tables, then set the section type to Prepare Only. However, if the section updates permanent application tables in the database, set the option to Critical Updates.
During runtime, when the system arrives at the first section set to Critical Updates, it sets the AE_CRITICAL_PHASE value in the AERUNCONTROL record to Y. Once set, the value of AE_CRITICAL_PHASE remains Y until the program completes successfully. When the program completes, the corresponding row in AERUNCONTROL is deleted. Therefore, a Prepare Only section following the Critical Updates section won’t reset the AE_CRITICAL_PHASE value to N.
If your program terminates, the user can check the AE_CRITICAL_PHASE value. If it’s Y. the user knows that the section that failed is critical and that the program should be restarted to ensure data integrity. If AE_CRITICAL_PHASE is N, restarting may not be necessary; however, as a general rule, you should restart even if AE_CRITICAL_PHASE is set to N.
Using Restart at the Step Level
In your program’s Where clause of a Do Select action, you should include conditions that reduce the answer set returned from the Select statement.
For example,
SELECT RECNAME, FIELDNAME
FROM PS_AERECFIELD
ORDER BY RECNAME, FIELDNAME
If you ran this Select statement as part of a Do Select action with Restartable selected as the Do Select type, the system might process some of the rows twice after a restart. Also, if you have specified Reselect, the program could execute in an infinite loop, because there’s nothing to reduce the answer set. However, if you modified the Select statement to look more like the following, you could make it Restartable.
SELECT RECNAME, FIELDNAME
FROM PS_AE_RECFIELD
WHERE RECNAME > %Bind(RECNAME)
OR (RECNAME = %Bind(RECNAME) AND FIELDNAME > %Bind(FIELDNAME))
ORDER BY RECNAME, FIELDNAME
A Do Select action that has been coded for Restartable can be converted to Select/Fetch, but the opposite is not true.
The previous example shows the use of a key column to reduce the answer set. This can be convenient if your record has only one or two key fields. However, if your record has three or four keys, your SQL would become overly complex.
Instead of matching key fields, you could add a switch to the selected table, and then have the processing of the called section modify the switch as it processes the row. In this example, your Select statement could look like the following:
SELECT COLUMN1, COLUMN2, . . .
FROM PS_TABLE1
WHERE PROCESSING_SWITCH=’N’. . .
A controlled abnormal termination (sometimes called an abend) means that Application Engine exits gracefully because of a calculated error condition. Some examples of controlled abends are:
- SQL errors while you have set On Error toAbort.
- A PeopleCode return value, when On Return is set to Abort.
- A SQL statement that affects no rows, when you have set On No Rows to Abort.
In these situations (when PeopleSoft Application Engine is in control) the Run Status field in Process Monitor reads Error.
An uncontrolled termination occurs when there is a memory violation or a user terminates a process. In these cases, the Run Status field in Process Monitor shows Processing.
There are two ways to restart an Application Engine program:
- From the command line.
- From a process request page.
Note. The following procedures for restarting a failed Application Engine program assume that you have rectified the error that caused the program to fail in the first place. For instance, suppose the name of a referenced table has changed. Regardless of how many times you restart the program, it will continue to fail until you’ve modified references to the old table name.
Restarting from the Command Line
Normally, only developers and system administrators use the command line for restarting Application Engine programs. Users, in most cases, should not be expected to use this method.
You can use the command line option to restart programs that run on the client or the server. PeopleSoft Application Engine references only the process instance of the failed process. Therefore, if you run a process on the client and it fails, you can restart it from the server using the server command line. Likewise, if you run a process from the server and it fails, you could restart it from the client using the command line.
To restart an Application Engine program from the command line:
- Collect the command line values associated with the failed program.
These values include database type, database name, user ID and password, run control ID, program name, and the process instance. You can find these variables on the Process Details dialog box, the corresponding state record, or the Application Engine Run Control table. Where the values reside depends on how you invoked the program. For instance, if you invoked the program using the command line, or outside of PeopleSoft Process Scheduler, then you cannot view details associated with the program run on the Process Details dialog box.
- Enter the following command line syntax at the command prompt substituting the values from the previous step.
PSAE.EXE −CT DB_TYPE -CD DB_NAME -CO OPRID -CP PASSWORD -R RUN_CONTROL -AI PROGRAM_NAME -I PROCESS_INSTANCE
Note. Some database platforms, such as Sybase, also require that you include a server name in the argument list.
Restarting from the Process Request Page
You can restart programs from a process request page only for those programs that run on the server.
To restart an Application Engine program from a process requests page:
1. Open PeopleSoft Process Scheduler by selecting PeopleTools, Process Scheduler, System Process Requests.
2. Locate the run control ID number of the program to restart.
3. To display the details of the failed process, click the Process Detail link.
4. On the Process Request Details page, select Restart Request, and click OK.
If you attempt to restart what PeopleSoft Application Engine believes to be a process that completed successfully, you receive a bad restart message. You can also get this message if your Application Engine application is defined with restart disabled.
When an Application Engine program ends abnormally, you may have to decide whether you should restart the process or just start it from the beginning. As your Application Engine program ran at least part way through, starting over may leave your data in an unknown state. Also, application logic might need to be undone, depending on what stage the program was at when it failed, what data the program had committed, and so on.
However, if restart is enabled and you attempt to start a new process that matches the run control ID and user ID for another process, you receive a suspend error. Because the process instance for these two processes is different, the new request fails. This usually occurs when a user tries to run the program again after receiving an error on the previous attempt.
To start the program over from the beginning, you can use SQL to delete the row that corresponds to the failed program from the Application Engine run control table and your state record.
To restart an Application Engine program from the beginning:
1. Open your native SQL editor and manually delete the row in PS_AE_RUN_CONTROL that corresponds to the program you want to start from the beginning.
Use the following SQL to accomplish this step:
DELETE FROM PS_AE_RUN_CONTROL
WHERE OPRID=OPRID
AND RUN_CNTL_ID=Run_Control_ID
2. Delete from your state record the row that corresponds to the failed program run.
Use the following SQL to accomplish this step:
DELETE FROM PS_MY_AET
WHERE PROCESS_INSTANCE=Process_Instance
Note. To restart the program, you can also select Restart Request from the Process Request Details dialog box.
To disable restart, use any of these methods:
· Select the Disable Restart check box on the PeopleSoft Application Engine Program Properties dialog box.
To access program properties, select File, Definition properties, and select the Advanced tab.
· Select the Disable Restart check box in the Configuration Manager profile.
To access the profile, start Configuration Manager, select the Profile tab, and click Edit. Then select the Process Scheduler tab.
· Include the −DR Y option in the command line of PSAE.EXE.
If you’ve disabled restart in any of these three places, restart is disabled.
Therefore, if you want the program to restart in a production environment, while still keeping a restart condition from getting in the way during development and testing, you can clear the Disable Restart check box in the Application Engine program properties. Then during development you can select the Disable Restart check box in Configuration Manager, or invoke your program from the command line with the -DR Y option, without having to reconfigure the program for testing.
0 comments:
Post a Comment
Phaniraavi@gmail.com