PeopleSoft :: Insert into Table using Select statement(illegal use of long datatype Error)

This note explains the inability to perform SQL: INSERT INTO...SELECT FROM when columns inserted/selected are of type LONG.
In this case we have multiple option to solve the issue,here we are refering to the PL/SQl using the cursor method to overcome this issue.
There are several workarounds we can use to solve our sample problem. We will examine each of the following in turn:
TO_LOB;
PL/SQL;
DBMS_XMLGEN;
Dictionary Long Application (oracle-developer.net utility).


Error::ORA-00997: illegal use of LONG datatype error

INSERT INTO TABLE_B

 (

ROW_ID,

LONG_COLUMN

)

SELECT ROW_ID,

LONG_COLUMN

FROM TABLE_A;

 COMMIT;

Where::

TABLE_A has following fields

- ROW_ID (VARCHAR2(15)

- LONG_COLUMN (LONG)

TABLE_B has following fields

- ROW_ID (VARCHAR2(15)

- LONG_COLUMN2 (LONG)

Solution

DECLARE

 CURSOR c IS

 SELECT ROW_ID, LONG_COLUMN

 FROM TABLE_A;

 rc c%ROWTYPE;

 BEGIN

 OPEN c;

 LOOP

 FETCH c INTO rc;

 EXIT WHEN c%NOTFOUND;

 INSERT INTO TABLE_B

 (

 ROW_ID,

 LONG_COLUMN2

 )

 VALUES

 (

 rc.ROW_ID,

 rc.LONG_COLUMN

 );

 END LOOP;

 COMMIT;

 END;

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