PeopleSoft :: INSTR IN Oracle

Definition:
The Oracle INSTR function searches inside a string for a substring. The Oracle INSTR function works in a way similar to the SUBSTR function, but INSTR returns an integer number indicating theposition of the substring within the string, rather than returning the substring itself.
Note: For more complex string handling functions, you can make use of Regular Expressions by using the extended version of the INSTR function named REGEXPR_INSTR.
Example Syntax:
INSTR( source_string, substring [, start_position [, occurrence ] ] )

source_string is the string to be searched.

substring is the character string to be searched for inside of source_string.

start_position is an optional argument. It is an integer value that tells Oracle where to start searching in thesource_string. If the start_position is negative, then Oracle counts back that number of characters from the end of thesource_string and then searches backwards from that position. If omitted, this defaults to 1.
occurrence is an integer indicating which occurrence of substring Oracle should search for. That is, should INSTR return the first matching substring, the second matching substring, etc. This argument is optional. If omitted, it defaults to 1.

If the substring is not found in source_string, the Oracle INSTR function will return 0.

Notes:

Both source_string and substring can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, orNCLOB. The value returned is of NUMBER datatype.


Both start_position and occurrence must be an integer of datatype NUMBER, or any datatype that can be implicitly converted to NUMBER.

The first position in the source_string is 1, not 0.
The INSTR search is case sensitive.

PeopleSoft :: RTRIM IN Oracle

The RTRIM function can be used in the following versions of Oracle/PLSQL:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle RTRIM function examples and explore how to use the RTRIM function in Oracle/PLSQL.

1)RTRIM('Peoplesoft        ')

Result:Peoplesoft

2)RTRIM('Peoplesoft        ', '    ')

Result:Peoplesoft

3)RTRIM('1230000', '0')

Result:123

4)RTRIM('Peoplesoft123123', '123')

Result:Peoplesoft

5)RTRIM('123Peoplesoft123', '123')

Result:123Peoplesoft

6)RTRIM('Techxyxzyyy', 'xyz')

Result: 'Tech'

7)RTRIM('Tech6372', '0123456789')

Result: 'Tech'

The RTRIM function may appear to remove patterns, but this is not the case as demonstrated in the following example.RTRIM('Techxyxxyzyyyxx', 'xyz') Result: 'Tech'

It actually removes the individual occurrences of 'x', 'y', and 'z', as opposed to the pattern of 'xyz'.
The RTRIM function can also be used to remove all trailing numbers as demonstrated in the next example.RTRIM('Tech6372', '0123456789') Result: 'Tech'

PeopleSoft :: Dynamic Prompting-Edit Table method

PeopleSoft enable us to use the Dynamic Prompting in two ways.
1)DERIVED record fields or popularly known as Edit Table method
2) Dynamic Views.

DERIVED record fields or popularly known as Edit Table method

There can be business case where based on the value on one field, the prompt for other field should change. The change may include the search fields or list box values displayed on the prompt page. It can also demand an entirely different data set to be prompted for the user. In this type of scenarios the action that we require at the back end is to point the prompt of the fields to different tables or views. To tackle this kind of scenarios you need to use derived work record fields.

Consider the case where you have a check box (called effective dated) and a department field on the page. If the check box is checked you need to show only all the effective dated departments on the prompt. Otherwise the prompt should list all the department codes and user should also have a search option to search with the effective date. This is an ideal scenario where you can use derived record field method.

Please follow these steps :
1)Add the field checkbox to your main record
2) Create a view, say PPSFT_DEV0002_VW, which will fetch the effective dated departments.
3) Create another view, say PPSFT_DEV0003_VW, which will fetch all the departments. Make sure to check the alternate search key and list box item property for the EFFDT field (this is for the stated requirement. You may have to create both the views as per your real requirement).
4)On the record field properties of the field department, give the prompt table name as %EDITTABLE (it is not mandatory to give %EDITTABLE all the time. You can give % and any field name present on the table DERIVED).

Please find the code snippet that is to be added to the field change event of the field Check box in your main record

/* If it is checked then use first view else use second view */
WinMessage("Hi", 0);
If Record.PPSFT_DEV0002.CHECK_BOX.Value = "Y" Then
/*Specify the prompt record name here. Appropriate field name should be used in place of EDITTABLE
PPSFT_DEV0002_WRK.DEPTID = GetRecord(Record.PPSFT_DEV0002_VW).GetField(Field.DEPTID).Value;
Else
PPSFT_DEV0002_WRK.DEPTID = GetRecord(Record.PPSFT_DEV0003_VW).GetField(Field.DEPTID).Value;
End-If;*/
DERIVED.EDITTABLE.Value = "PPSFT_DEV0002_VW";
Else
DERIVED.EDITTABLE.Value = "PPSFT_DEV0003_VW";
End-If;
Place the field EDITTABLE (or other fields which you have mentioned in the previous step) to the page where your department field is present. This is mandatory because we will be referencing the field in PeopleCode program in next step. To reference the field in PeopleCode, it is required that the work record field is present on the component buffer. You can hide this field, because it will not make any sense to the business user.
The Edit table that we are placing as invisble on the page should have record name as DERIVED and Field Name as EDITABLE

Even we can try the same code with field default event and see what difference it makes.

PeopleSoft :: Prompt function in Peoplesoft-Dynamic Views.

Dynamic Views can prove to be powerful prompting mechanism when you come across a requirement where the values fetched by the prompt table needs to be changed based on some system variables such as %OperatorId or %EmployeeId. Take a situation where you want to filter the data present in the prompt table based on the logged in user. In this case you need to use dynamic views as a prompt table.
Take a situation where you have an address id as a field on the page. Your requirement will be to bring up the address id’s for the particular logged in user alone. But if a system administrator logs in, then you should display address id’s corresponding to all the users. In this case you have to use up dynamic views. The step should be followed will be as follows.
1. Create a record (say PROMPT_DVW) and add necessary fields that need to be prompted.
2. Set up the key structure in the way you would like the prompt page to be appeared.
3. Save the record as dynamic view type. No need to build or write sql for these kinds of records.
4. On the record field property of the address id field, set the dynamic view as the prompt table.
5. Now on the appropriate event (RowInit, FieldChange etc) write the below code.

/* Note: ADDRESS_ID is the field which requires prompt. We are not writing anything on the dynamic view people code events. */
If %OperatorId = “Admin” Then
RECORD.ADDRESS_ID.SqlText = “select ADDRESS_ID, DESCR from PS_BASE_TABLE”;
Else
RECORD.ADDRESS_ID.SqlText = “select ADDRESS_ID, DESCR from PS_BASE_TABLE where EMPLOYEE_ID = ‘”|%EmployeeId|”’”;
End-If;
The SqlText property will dynamically act as a view sql and bring up the corresponding result in the prompt page.

PeopleSoft :: Unix permissions

Unix Permissions
-------------------

all the objects in unix are files,even a directory is

collection of files

d   rwx      rwx     rwx
s   rwx      rwx     rwx
h   rwx      rwx     rwx
-|  ---   |  ---  |  ---
  Creator   Group    Other


d-directory
s-softlink
h-hardlink
r-read
w-write
x-execute

Number systen for rwx
---------------------
r-4
w-2
x-1

777 means full permission.

PeopleSoft :: Events in detail

SearchInit
SearchInit:Component processor determines search record, search keys, search mode and displays the search page accordingly in SearchInit event. That is why we can write the code for skipping the search pge on SearchInit event.Following functions can be used in SearchInit: SetSearchEditClearSearchEditSetSearchDefaultClearSearchDefaultSetSearchDialogBehaviorThe 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 allows you programmatically set values of search daialog fields. For example, if you want allow entry of search field for a user and display default values for other users you could write the belowIF (%OPRID =="USERNAME_REQ") THEN   RECORDNAME.SEARCHKEY = "";ELSE   RECORDNAME.SEARCHKEY = "SOMEVALUE";   GRAY(RECORDNAME.SEARCHKEY);END-IF;
SearchInit PeopleCode is performed before the search page appears.Place SearchInit PeopleCode on search key fields or alternate search key fields on a search record or component search record.Do not place errors or warnings in SearchInit.SearchInit PeopleCode can be associated with record fields and component searchrecords.You cannot use DoModal,DoModalComponent,Transfer,TransferPage within SearchInitGiven below are important functions that are used in SearchInitIf you want the SETID to default from the operator defaults table for SETID search field. Add the following PeopleCode to either the Record.Field.SearchInit or Component.Record.SearchInit PeopleCode.SetSearchDefault(RECORD.FIELD);Where RECORD.FIELD refers to the name of the record and field that you set the field default for in the first step.Use the SetSearchDialogBehavior function in SearchInit PeopleCode to set the behavior of search and add dialog boxes before a page is displayed, overriding the default behavior. There are two dialog behavior settings: skip if possible (0) and force display (1).Skip if possible means that the dialog box is skipped if all of the following are true:All required keys have been provided (either by system defaults or by PeopleCode).If this an Add dialog box, then no duplicate key error results from the provided keys; if this error occurs, the processing resets to the default behavior.If this is a Search dialog box, then at least one row is returned based on the provided keys.
Force display means that the dialog box displays even if all required keys have been provided. The default behavior of the search and add dialog boxes is force display.%Mode returns a String value consisting of an uppercase character specifying the action a user selected when starting the current component. The following values can be returned. You can check either for the string value (“A”, “U”, and so on.) or for the constant. It is generally used to check whether the user is in Add Mode or Update/Display Mode.%Mode is often used in Page Activate, SearchInit, but can be used in other peoplecode events too.Some of the other commonly used functions in search init are SetSearchEdit, ClearSearchEdit, IsSearchDialog, ClearSearchDefault.
SearchSave
SearchSave event happens when we click on search button of search page after entering the search values. We can write validations to check whether any search criteria is entered on the search page on SearchSave event. In SearchSave event itself search values appear listed in a grid on search page. If any error is issued on this event using peoplecode processing stops and no search result are listed on the page.Here is an example of a SearchSave PeopleCode event:rem No spaces allowed in User Idrem also do not allow the userid of PPLSOFTif %Mode = "A" Then&find = Find(" ", PSOPRDEFN_SRCH.OPRID);if &find > 0 ThenError MsgGet(48, 54, "Message not found.");End-If;if Upper(PSOPRDEFN_SRCH.OPRID = "PPLSOFT" ThenError MsgGet(48, 235, "Message not found.");End-If;End-If;
SearchSave PeopleCode is performed after the user clicks the Search button or the Add button on the search page.Place SearchSave PeopleCode on search key fields or alternate search key fields on a search record or component search record.Use SearchSave to validate values entered in the search page fields.

PeopleSoft :: People Code Event Execution Sequence

1. Search Init 
2. Search Save 
3. Row Select
4. Prebuild 
5. Field Default 
6. Field Formula
7. Row Init 
8. Postbuild 
9. Activate 
10. Field Edit
11. Field Change
12. Row Insert
13. Row Delete
14. Save Edit
15. SavePrechange
16. Work Flow
17. SavePostchange

PeopleSoft :: Overview about People soft HRMS Module

Human Capital Management (HCM )Module Overview 

• Recruiting Solutions: Where job postings are created and where people can apply online Time & Labor: where time is entered and tracked and eventually paid 
• Payroll: How employees are paid 
• Absence Management: addresses all University leave programs (i.e., Vacation, Sick Leave, Bereavement, Jury Duty, FMLA, etc.) 
• Benefits Administration: administers our benefit programs 
• Employee (ESS) & Manager Self Service (MSS): allows access to both employees and managers for HR actions 
• Payroll and Budget (Commitment Accounting): Will be part of the HCM module in PeopleSoft.

Human Capital Management (HCM) in detail

• Human Capital Management (HCM) is a suite of PeopleSoft modules used to manage employee and human resource functions. The HCM suite is made up of the following modules: Human Resources (Position Management). The HR module maintains position and employee data. Data that is stored and updated in this module relates to employee, job, position, compensation, and benefit information. 
• Recruiting Solutions. The Recruiting Solutions module is comprised of Talent Acquisition 
• Manager (TAM) and Candidate Gateway (CG). Talent Acquisition Manager is used to create position postings, screen, interview, and correspond with applicants, extend job offers electronically (and in hard-copy format), and prepare applicants for hire. Candidate Gateway enables internal applicants and external applicants to search, view and apply for jobs, manage their job application and personal information and submit employment references, cover letters and other documents. 
• Benefits. The benefits module manages multiple benefits programs, including plans for leave, health and life insurance, savings, and dependent care reimbursement. 
• Payroll/Time Keeping. Payroll and Time Keeping are two modules in PeopleSoft that work together to accurately calculate employees’ time and manage employees’ pay.Time Keeping serves as the time reporting system, which is fully integrated with Payroll. 
• Absence Management. The Absence Management module tracks and processes paid and unpaid leave of absences, and the accrual of leave time based on an employee’s years of service. Absence Management is fully integrated with the Payroll and Time Keeping modules. 
• Budget (Commitment Accounting). PeopleSoft Commitment Accounting is the budgeting module for HR, which enables users to allocate salary costs to different funding sources

PeopleSoft :: Application Engine an Overview

Application engine

PeopleSoft Application Engine is a PeopleTool used to carry out background SQL processing. Application Engines are neatly structured into blocks and offer an alternative to writing COBOL or SQR programs for background SQL processing.
Application Engine Program Types
Application Engine has five types of programs. We can specify the type in the Program Properties dialog box for your program definition.
 The types are:
1. Standard, which is a normal entry-point program.
2. Upgrade Only, which is used in PeopleSoft upgrade utilities.
3. Import Only, which is used by PeopleSoft import utilities.
4. Daemon Only, a type of program used as a daemon process.
5. Transform Only, a program type used to support Extensible Style sheet Language Transformations (XSLT).
The key elements of an application engine program are:












Peoplesoft Application Engine Structure



Sections

A PeopleSoft App Engine can have one or more sections. Every App Engine should have at least one section called main section where the execution always start. A section can have multiple steps that get executed one after another.

Steps
Steps in an Section gets executed sequentially. Steps are the smallest unit of work that can be committed within a program. Each Step can have one or more actions.

Actions
Actions, unlike steps have a particular execution order. An Application has 9 Actions out of which two (SQL and Call Section ) are mutually exclusive. These steps are discussed in detail in the Application Engine Action article.
1. Do When
2. Do While
3. Do Until
4. Do Select
5. People Code
6. SQL
7. Call Section
8. Log Message
9. XSLT – found only in Transform Only programs

State Records
State Records are PeopleSoft records that are used to pass values from one action to another. They can be considered as storage locations for an Application engine. Find more about State records here.

Parallel Processing in Application Engines
Application Engine seamlessly supports Parallel Processing which helps faster processing when the data involved is huge. This is achieved by the use of temporary tables and %Table meta-SQL. PeopleSoft highly recommends programmers to make use of this feature.

Running an Application Engine
An application engine can be executed from the following 4 places:
1. From within Application Designer using Run Request
2. From PeopleCode using CallAppEngine()
3. From Process Scheduler
4. Manually from command prompt

PeopleSoft State Record

State records are used in Application Engines as a storage location for data that needs to be passed across actions. An Application Engine can have more than one state records associated with it out of which only one can be marked as a default state record.

Type of Sate Records
A state record can be an SQL table or a temporary table based on the requirement. If you want the data in the state record to be preserved across commits then you need to use an SQL Table as your state record. If you use a temporary record as a state record, it’s contents are wiped out on commit. SQL Tables are also used in restart enabled app engines.

Accessing Values from State Records
you can access values from the state record seamlessly. If you are accessing values from the default state record, you can simply use the fieldname. When accessing values from other state records, you need to use the dot (.) notation (recordname.fieldname). In other words, whenever the record name is not specified, the filed from the default state record would be accessed.
Within People Code you can access a state record field using fieldname. Value. Within SQL actions you can access the state record fields using the %Bind(fieldname)
For assigning values to the fields within a state record, you can use the fieldname. Value notation from within People Code or use %Select when used from within SQL actions.

Key Structure
A physical State record should have Process Instance as the first field and the only key. A state record can hold only one row of data at any point. If you need access to multiple rows of data, use a temporary table.

Naming Convention
A state record name must end with _AET.
Example:
This example assumes that the state record has the following fields apart from the process instance: CONTRACT_NUM, CONTRACT_LINE_NUM, PRODUCT_ID.
The below code fetches and stores into the state record, PRODUCT_ID from CA_DETAIL table based on the CONTRACT_NUM and CONTRACT_LINE_NUM (from the state record) passed on using the %Bind meta.

%Select(PRODUCT_ID)
SELECT PRODUCT_ID
FROM PS_CA_DETAIL
WHERE CONTRACT_NUM = %Bind(CONTRACT_NUM)
AND CONTRACT_LINE_NUM = %Bind(CONTRACT_LINE_NUM)

PeopleSoft Subrecord

PeopleSoft Subrecords are collection fields that are grouped together into one entity so that they can be used as building blocks for multiple records. Subrecords doesn’t have an existence in the database by itself as they are not build.
The reason behind using a subrecord is the easiness it brings to building of records. Suppose you need to use a set of fields in multiple records; instead of manually adding each and every field to the record definition, a good approach would be to create a subrecord with these fields and then use it. You may then use insert the subrecord directly into any number of record definition for faster development.
Another powerful aspect of PeopleSoft subrecords is that they can be combined with subpages to form a deadly bolt-on combination that enables faster development. 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).
Once defined, subrecords can be interted into a record with the same easiness with which you add a filed.
To view the definitions / fields within any subrecord that has been inserted into a record, press the Expand All subrecords button. While the subrecord id expanded, you may not be able to perform standard functions like sorting, insertion of fields etc. on the record. Also, the expanded subrecords are read-only, meaning, you may not be able to open the properties of the fields in the subrecord.
PeopleSoft suports nesting of subrecords to any level. This means that you can have a subrecord inside another subrecord and so on, to any depth. And off course, you can write PeopleCode in the subrecord filed. This will be triggered for all records that the subrecord is used in.

PeopleSoft :: Record data to file conversion using Peoplecode

Local SQL &MYSQL; 
Local string &EFFDT1; 

SQLExec("SELECT COUNT(*) FROM PS_YOUR_TABLE", &COUNT); 
&MYSQL = CreateSQL("SELECT EMPLID,NAME,to_char(EFFDT,'YYYY-MM-DD HH:MI'),to_char(BIRTHDATE,'YYYY-MM-DD HH:MI')
,SUPERVISOR_NAME,DEPT_DESC,LOCATION FROM PS_PPSFT_ATST_EMP"); 
&fileLog = GetFile("Output_Report.csv", "w", "a", %FilePath_Relative); 
If &COUNT = 0 Then 
&fileLog.WriteLine("No data found"); 
Else/*Write Heading for the file columns 
&fileLog.WriteLine("EMPLID" | "," | "NAME" | "," | "EFFDT" | "," | " 
BIRTHDATE" | "," | "SUPERVISOR_NAME" | "," | "DEPT_DESC" | "," | "LOCATION"); 
End-If; 
While &fileLog.WriteLine(&EMPLID,&NAME ,&EFFDT,&BIRTHDATE,,&SUPERVISOR_NAME,&DEPT_DESC,& 
LOCATION) 
&EMPLID | "," | &NAME | "," | &EFFDT | "," | &BIRTHDATE" | "," | & SUPERVISOR_NAME | 
"," | &DEPT_DESC | "," | &LOCATION) 
End-While; 


where  PS_PPSFT_ATST_EMP is the staging table

Copy and Paste not working between local machine and Remote Desktop Connection

Fixing the issue is pretty straightforward and involves a few simple steps.

• Load up task manager (right click taskbar and select Task Manager)
• Go to the Processes Tab
• Select rdpclip.exe
• Click End Process
• Go to the Application Tab
• Click New Process
• Type rdpclip
• Click Ok

There, copy and paste should now work normally again.

To fix it permanently

• Create a new bat file and call it whatever you want say, clipboard.bat.
• Write the following two commands on separate lines in the new bat file
• Taskkill.exe /im rdpclip.exe
• Rdpclip.exe
• Save the bat file and drag it into the toolbar quick launch section

PeopleSoft :: Navigation for a processing PIA if the respective SQR name is provided.

let the SQR name be dev0001.sqr

1. Navigate to Peoplesoft HCMPIA
2. Main Menu > PeopleTools >  Process Scheduler > Processes
3. Enter the process name as dev0001.
4. Go to the Process definition option tab,get the component name.
5. Navigate to the Portal page-PeopleTools > Portal > View Menu Item Detail
6. Enter the component name.
7. Get the Menu Path of the process

8. 

   Process

Component Name

Grep function in unix

The grep command is used to search text or searches the given file for
lines containing a match to the given strings or words. By default,
grep displays the matching lines. Use grep to search for lines of text that match
one or many regular expressions, and outputs only the matching lines.
grep is considered as one of the most useful commands on Unix and other
 Linux operating systems.

The name, "grep", derives from the command used to perform a similar operation,
 using the Unix/Linux text editor ed:
g/re/p

Grep command syntax

Destination folder $grep 'search object' *.*

The syntax is as follows:
grep 'word' filename
grep 'word' file1 file2 file3
grep 'string1 string2'  filename
cat otherfile | grep 'something'
command | grep 'something'
command option1 | grep 'data'
grep --color 'data' fileName

How do I use grep command to search a file?

Search /etc/passwd file for boo user, enter:
$ grep boo /etc/passwd

PeopleSoft :: SQL query to retrieve current and previous department

SQL query to retrieve current and previous department

SELECT JOBS.EMPLID AS PSID,PER.NAME ,JOBS.DEPTID AS "Old Dept ID"
,DEP.DEPTID AS "New Dept ID",DEP.EFFDT AS "New Dept ID Effectice Date"
,JOBS.EFFDT AS "Old Dept ID Effectice Date",JOBS.EMPL_RCD
FROM JOBTABLE1 JOBS,NAMETABLE PER ,JOBTABLE2 DEP
WHERE JOBS.EMPLID=PER.EMPLID
AND JOBS.EMPLID=DEP.EMPLID
AND JOBS.EMPL_RCD=DEP.EMPL_RCD
AND DEP.EMPLID=PER.EMPLID
AND JOBS.EFFDT  =   (SELECT MAX(JOB1.EFFDT)                         -- Returning the previous Effective Date
                     FROM  JOBTABLE3 JOB1
                     WHERE JOB1.EMPLID=JOBS.EMPLID
                     AND JOB1.EMPL_RCD=JOBS.EMPL_RCD
                     AND JOB1.EFFDT < DEP.EFFDT)
AND JOBS.EFFSEQ   = (SELECT MAX(JOB2.EFFSEQ)
                    FROM JOBTABLE4 JOB2
                    WHERE JOB2.EMPLID=JOBS.EMPLID
                    AND JOB2.EMPL_RCD=JOBS.EMPL_RCD
                    AND JOB2.EFFDT = JOBS.EFFDT)
AND DEP.EFFDT  =   (SELECT MAX(JOB12.EFFDT)                         -- Returning the current Effective Date
                    FROM JOBTABLE5  JOB12
                    WHERE DEP.EMPLID=JOB12.EMPLID
                    AND DEP.EMPL_RCD=JOB12.EMPL_RCD
                    AND DEP.EFFDT <=SYSDATE)
AND DEP.EFFSEQ   = (SELECT MAX(JOB21.EFFSEQ)
                    FROM JOBTABLE6 JOB21
                    WHERE JOB21.EMPLID=DEP.EMPLID
                    AND JOB21.EMPL_RCD=DEP.EMPL_RCD
                    AND JOB21.EFFDT = DEP.EFFDT)
AND DEP.EMPL_STATUS IN ('A','L','P','S')  
AND JOBS.DEPTID <> DEP.DEPTID                                          --Selecting difference in department
AND JOBS.ACTION <>'TER'                                                           -- Termination
AND DEP.ACTION <> 'REH'                                                      -- Rehire
ORDER BY PER.NAME ASC;

PeopleSoft :: INSERT MULTIPLE ROWS WITH A SINGLE INSERT STATEMENT

Insert multiple rows of explicit data in one SQL command in Oracle::

INSERT ALL
  INTO mytable (column1, column2, column3) VALUES ('val1.1', 'val1.2', 'val1.3')
  INTO mytable (column1, column2, column3) VALUES ('val2.1', 'val2.2', 'val2.3')
  INTO mytable (column1, column2, column3) VALUES ('val3.1', 'val3.2', 'val3.3')
SELECT * FROM dual;

EXAMPLE #1
Insert 3 rows into the suppliers table, you could run the following SQL statement::

INSERT ALL
  INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
  INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
  INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;

EXAMPLE #2
Insert multiple rows into multiple tables:: For example, if you wanted to insert into both the suppliers and customers table, you could run the following SQL statement:

INSERT ALL
  INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
  INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
  INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York')
SELECT * FROM dual;

This example will insert 2 rows into the suppliers table and 1 row into the customers table.

PeopleSoft :: Run control Page for running an sqr

People soft Run control Page for running an sqr

There are two methods of running an SQR which are: 
1. Running through process scheduler: In this case SQR uses run control parameters which are passed from run control page and are stored in run control table. 
2. Running from command prompt: In this case SQR uses the run control parameters which are passed to command prompt and for this user is asked to enter parameters using INPUT command of SQR. This method is used when we are in need of running the program from outside the system.

By checking the value of $prcs_process_instance which a key field of run control table (PROCESS_INSTANCE) program comes to know whether SQR is being run from process scheduler (in the system) or from outside the system. Empty value of $prcs_process_instance specifies that SQR program is being run from command prompt (Outside the system).

if $prcs_process_instance = '' 
! Write INPUT commands and procedures to run from command prompt 
! using sqrw or other tools. 
else 
! Call procedure or write statements to read run control parameters 
! from run control table and process the SQR procedures. 
end-if

Now we have a question: Where does this $prcs_process_instance come from? 
For this we include prcsapi.sqc and call the procedures DEFINE-PRCS-VARS and Get-Run-Control-Parms which get this value from Process Scheduler when the program is being run from Process scheduler. 

Note that different procedures are used for this in different systems. For example, in HRMS system stdapi.sqc is included and STDAPI-INIT procedure is called for this. The procedure also sets the values of $prcs_oprid and $prcs_run_cntl_id. These values are used as the keys for getting run control parameters from run control table.

PeopleSoft Custom Run Control Pages

Every PeopleSoft developer will eventually need to create an SQR or App Engine process which can be executed from a Run Control page with custom parameters.  Here is a quick cheat sheet for creating the Run Control page, and how to retrieve the Run Control parameters from within an SQR or App Engine program.

Creating a Custom Run Control Page

This step is the same regardless of whether you will ultimately use the Run Control page with an SQR or App Engine program.  Do the following in Application Designer:

Creating the Run Control Record

1. Open Record definition PRCSRUNCNTL and make a copy by using "Save As" and giving the Record a new name (for example, "MY_PRCS_RC").
• When prompted to save a copy of the PeopleCode associated with PRCSRUNCNTL, choose "Yes".
2. Update the following Record PeopleCode definitions on your new Run Control Record, replacing references to PRCSRUNCNTL with your custom Run Control Record name:  a) OPRID.RowInit     b) RUN_CNTL_ID.SaveEdit
3. Add any desired additional fields to your custom Run Control Record (for example, "STRM").
4. Build the Record.

Creating the Run Control Page

1. Create a new Page definition and save it with a new name (for example, "MY_PRCS_RC").
2. Insert a Subpage onto your page, and choose "PRCSRUNCNTL_SBP".
• On the Insert Subpage dialog, change the "Subpage Record Name Substitution" value to your custom Run Control Record (i.e., "MY_PRCS_RC").
3. Drag your custom Run Control fields onto the Page (i.e., "STRM").
• Note:  You do not need to add the default Run Control fields onto the page (OPRID, RUN_CNTL_ID, etc).  These values will be populated automatically through PeopleCode

Run Control Record

Run Control Page

Creating the Run Control Component

1. Create a new Component definition.
2. Set the Component Search Record to your custom Run Control Record (i.e., "MY_PRCS_RC").
3. Save the Component with a new name (for example, "My PRCS_RC").
4. Register your Component using the Registration Wizard (Tools > Register Component).

Creating the Process Definition

In this step, we create a Process definition entry for our SQR or App Engine program, and associate the process definition with our custom Run Control component.

1. In the PIA, navigate to:  PeopleTools, Process Scheduler, Processes
2. Click the "Add a New Value" tab and enter the Process Type and Process Name.
• Note:  The Process Name must exactly match the name of your App Engine program, or in the case of an SQR, must exactly match the file name of your SQR file (without the ".sqr" extension suffix).
3. On the "Process Definition Options" tab:
1. Add your custom Run Control Component under the "Component" grid area on the bottom left of the page.
2. Add whichever Process Groups are appropriate to grant process security to the appropriate persons.

You should now be able to navigate to your Run Control page in the PIA, fill out the Run Control parameters, and schedule your process to run on the Process Scheduler.

Retrieving Run Control Parameters

Now that we have a way to provide Run Control parameters via a Run Control page, we need to be able retrieve and use those parameters from within a program.  This part of the process is different for App Engine and SQR programs.

Run Control Parameters in App Engine Programs

The standard way to store Run Control parameters in an App Engine program is to use a State Record.  To set up the State Record:

1. A requirement in naming State Records is that they must have "_AET" as their suffix.  Make a copy of your Run Control record, giving it a new name (for example, "MY_PRCS_AET").
1. When prompted to save a copy of the PeopleCode associated with the original Record, choose "No".
2. Change the Record Type from "SQL Table" to "Derived/Work".  A Derived/Work Record doesn't persist any data to the database.  Instead, it acts as an in-memory data structure while the program is running on the Process Scheduler.
3. Open your App Engine program, and navigate to: File, Definition Properties
4. On the "State Records" tab search for your State Record definition, and click the "Add" button to move the definition from the left-hand panel to the right-hand panel.

Now that the State Record is created and associated with the App Engine program, we need to populate it.

1. Add an SQL step as the first step in the App Engine program.  To populate the State Record with values from the Run Control Record, we use the Meta-SQL %Select function.  For example:
   
   %Select(OPRID, RUN_CNTL_ID, STRM)
   FROM PS_MY_PRCS_RC
   WHERE OPRID = %OperatorId
   AND RUN_CNTL_ID = %RunControl

Now that the Run Control parameters have been stored into the State Record, they can be referenced from PeopleCode via the State Record.  For example, the following would write the STRM Run Control parameter value to the message log:

MessageBox(0, "", 0 , 0, "STRM=" | MY_PRCS_AET.STRM);

Run Control Parameters in SQR Programs

In an SQR program, there are no State Records.  Typically, Run Control parameter values are simply stored using variables.  The following steps will allow you to retrieve the Run Control parameters in an SQR program.  Please refer to the SQR code sample following the step descriptions to see this in action.

1. Make sure you have added an include statement to include "stdapi.sqc" in your SQR program.  This delivered SQC provides routines which update the Process Scheduler with process status, as well as facilitating the retrieval of the Operator ID and Run Control ID values for the currently running SQR process instance.
2. Ensure that the very first step of your program is to run the "Stdapi-Init" procedure, and that the very last step of your program is to run the "Stdapi-Term" procedure.
3. Create a procedure to retrieve Run Control parameters, referencing the Operator ID and Run Control ID as "$prcs_oprid" and "$prcs_run_cntl_id" variables respectively.  The "Stdapi-Init" procedure initializes these variables.

The following code sample illustrates the steps above:

! Program Name:  MYPRCS.SQR

! Program Descr: My SQR Process

! Author:        Mark Stramaglia

#include 'stdapi.sqc'   ! Update status and get system parameters

BEGIN-PROGRAM

  DO Stdapi-Init

  DO Get-Run-Control-Parameters

  DO Main-Process

  DO Stdapi-Term

END-PROGRAM

BEGIN-PROCEDURE Get-Run-Control-Paramters

  BEGIN-SELECT

  RC.STRM

    LET $strm = &RC.STRM

  FROM PS_MY_PRCS_RC RC

  WHERE RC.OPRID = $prcs_oprid

  AND RC.RUN_CNTL_ID = $prcs_run_cntl_id

  END-SELECT

END-PROCEDURE Get-Run-Control-Parameters

BEGIN-PROCEDURE Main-Process

  SHOW 'STRM=' $strm

END-PROCEDURE Main-Process

DDL, DML and DCL commands

What are the difference between DDL, DML and DCL commands?

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

1. CREATE - to create objects in the database
2. ALTER - alters the structure of the database
3. DROP - delete objects from the database
4. TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
5. COMMENT - add comments to the data dictionary
6. RENAME - rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

1. SELECT - retrieve data from the a database
2. INSERT - insert data into a table
3. UPDATE - updates existing data within a table
4. DELETE - deletes all records from a table, the space for the records remain
5. MERGE - UPSERT operation (insert or update)
6. CALL - call a PL/SQL or Java subprogram
7. EXPLAIN PLAN - explain access path to data
8. LOCK TABLE - control concurrency

DCL

Data Control Language (DCL) statements. Some examples:

1. GRANT - gives user's access privileges to database
2. REVOKE - withdraw access privileges given with the GRANT command

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

1. COMMIT - save work done
2. SAVEPOINT - identify a point in a transaction to which you can later roll back
3. ROLLBACK - restore database to original since the last COMMIT
4. SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use