PeopleSoft Learning

Accessing Browser Reports

To access a browser report double-click on the line in the output window containing "Browser Compare Report Created.”

PeopleSoft Compare Report Viewer

The Viewer menu contains menu options related to navigating within the report. The Definition Types menu enables you to dill down into the compare data for each definition type compared. The Definition Types menu only contains the definitions compared.

Sharing Browser Reports

PeopleSoft Application Designer writes the browser reports to a subdirectory named after the project name within the Report Output Directory specified on the General tab on the Options dialog. For example, if the Report Output Directory is c:\psreports and the project name is “PROJECT,” then the browser reports exist in c:\psreports\PROJECT. To view the compare results, double-click CompareViewer.html and then select the desired project name.

For each type of definition you compare, there is a subdirectory containing XML files storing the compare data for that definition type. For example, if you compared pages, the XML file containing compare data for the page compare exists in c:\psreports\PROJECT\Pages. These files can be used as source data for custom displays of the compare data. The PeopleSoft Compare Report Viewer uses these XML files along with various HTML and XSL files to present your compare data.

To share the browser reports, PeopleSoft recommends:

· Copying the entire high-level report output directory (containing all subdirectories, HTML, and XSL files) and copying it to a shared network drive.

· Compressing the entire directory and sending or posting individual copies as needed.

Article posted by

- Ganesh A.M

Understanding Compare reports status in peoplesoft

Determining Definition Status

PeopleSoft Application Designer defines the status of a definition in the source and the target database.

Note. Compare by release date is determined by finding the highest release label in common between the source and target databases. PSRELASE.RELEASELABEL column is searched on both source and target to find the latest common value between the two. Then PSRELEASE.PSRELEASEDTTM for that label is used for compare by release date for the source and target, respectively. You can override this value to set a different release label, or specific compare by date, on the Upgrade Options interface. Only common release labels (found on both source and target PSRELEASE table) can be used.

- Ganesh A.M

Visual Compare reports in Peoplesoft

The PeopleSoft Application Designer displays a Property Value panel to show details about every difference or each changed page control. To the right, each page is displayed side-by-side enabling the visual comparison.

Note. The pages appear in a read-only state. You cannot make any changes to either page definition from the Visual Compare interface.

Determining Differences between Page Definitions

When there are differences between source and target page definitions, the system illustrates this in the Layout tab by surrounding the page field with a red box with a red 'X' at the top, left-hand corner. The field in which you have placed your cursor has a dotted line surrounding it.

On the Order tab, page controls that have differences are highlighted in red. Also, if you select a page control, you can see easily if its order is different as the highlighted areas do not match.

To launch a visual compare for pages:

1. Open the upgrade project, and make sure that you are in the Upgrade view.

2. Open the Pages window within the upgrade project.

3. Select the page(s) that you want to compare.

4. Right-click on the Pages window to display the context menu.

5. Select Visual Compare, and then select one of these options:

o From Database: select if you are comparing the current definitions with definitions in another database. After doing so, sign on to the appropriate database using the PeopleSoft Signon window.

o From File: select if you are comparing the current definitions with definitions in a file. After doing so, navigate to the appropriate file using the Visual Compare from File: Select Project dialog box.

-Ganesh A.M

Printing the online PeopleSoft Page

To print a particular, we can make use of html area and JavaScript command (Window.print ()).

Steps to be followed:

1. Place an html area on the page.

2. Double click over the html area, property window opens. Choose the value as “constant” and enter the following code: <form><input type = "button" value="print" onclick="window.print () ;">< /input></form>

3. In the page, you will be able to see the print button. By clicking the button, Print dialog box will be opened.

- Ganesh A.M

Why Peoplecode automatically formats itself? - Peoplecode, Behind the Scenes

Just as with any other PeopleSoft object, a Peoplecode program is stored in the database. Table PSPCMPROG contains the actual code. Do a SELECT on this table and you will see that each program has a key that designates its event (Field Change, etc.) and the object (record or component, for example) to which it belongs.

The code itself is stored in a long character field called PROGTXT. Look at that and you will see "garbage". Is this compiled code? Not really--it's an intermediate form of the source code. Peoplecode is an interpreted language and is never actually compiled. When you change and save a PeopleCode program, it is immediately scanned and condensed. Variables are identified and flagged with a special character; extra whitespace is removed; numeric constants are found and translated to a consistent internal format; keywords are replaced with special one-byte codes; and so on. The result is no longer readable to us, but it is more efficient. Some of this code can be run dozens of times during one transaction. We don't want to have to wait as the program is interpreted each time.

We can now answer the question about automatic code formatting. Enter a statement and then either save the program or click on the syntax check (checkmark) button. At that time, the program is reformatted with appropriate indenting, the extra whitespace is removed, and standard capitalization of keywords is applied. This is not just a handy service provided by App Designer. Since the code has been compressed into a more efficient form, it's about the only way the code could be displayed. For example, if a keyword is replaced with a single one-byte code, then the way we entered it initially (all caps, all lower case, or mixed) is lost and it must be displayed using the standard capitalization. Occasionally we may want to format the code in a certain way, but in general, this automatic reformatting is a good thing.

You may occasionally have Peoplecode programs "go bad". An error message may report that the program is invalid. Even before clearing your cache, you might want to try this. Open the program in App Designer, make a small change (such as adding a space at the end of a line), and save. The program is then re-scanned. That often fixes the problem.

One last tip... You might see some custom code and wonder who wrote it and when. The record or component to which the code belongs has its own user ID and date, and that is what is displayed on the properties panel. I don't know of a way to find the user ID and date for a particular PeopleCode program through App Designer, but you can see it by doing a SELECT on PSPCMPROG. The ID and datestamp fields are stored right there with the program.

-Ganesh A.M

Manipulating Data on a Grid: Scroll Peoplecode

When you have a grid that is filled with data; data that you don’t want. How do you clear it out and then fill it with the data set that you do want? Well, here are the simple steps to do just that.

Lets say we have a Grid on a page and the main record on the grid is called COMPETENCIES. Well we would first want to define a Rowset object to manipulate. The code for this would look as follows:

Local Rowset &COMPETENCIES;

Next we will want to instantiate the object. Instantiate just means to represent by an instance. If you notice, I am not creating a new Rowset. I am getting the Rowset from the current grid called competencies, using the GetRowSet function. And, I am defining the Grid object as a Rowset.

&COMPETENCIES = &REVIEW(CurrentRowNumber(1)).GetRowset(Scroll.COMPETENCIES);

Once that is complete, I want to remove all rows of data from the Grid. To do that I simply use the Flush method.

&COMPETENCIES.Flush();

Now lets fill the grid with new data that we really want.

&COMPETENCIES.Select(Record.COMPETENCIES,”WHERE EMPLID = :1 AND EVALUATION_ID = :2 AND COMPETENCY IN ” &IN ” AND EFFDT = %DATEIN(:3)”, &EMPLID, &EVALUATION_ID, &REVIEW_DT);

What I am doing in the above line of code is selecting the data that I want to use to fill the grid. By selecting the record I am in essence asking to select all the fields that are on the grid from the COMPETENCIES table with the where clause.

Thats it! It’s that simple.

Article posted by

- Ganesh A.M

Inserting rows into record using Peoplecode with better performance

When inserting rows using Peoplecode, you can either use the Insert method with a record object, or create a SQL Insert statement using the SQL object.

• If you're doing a single insert, use the Record Insert method

• If you're in a loop, and therefore calling the insert more than once, use the SQL object.

Why? Because the SQL object uses dedicated cursors and if the database you're working with supports it, bulk insert.

A dedicated cursor means that the SQL only gets compiled once on the database, so People Tools only looks for the meta-SQL once. This can mean better performance.

For bulk insert, inserted rows are buffered and only sent to the database server when the buffer is full or a COMMIT occurs. This cuts down on the number of roundtrips to the database. Again, this can mean better performance.

The following is an example of using the record insert method:

&REC = CreateRecord (Record.GREG);

&REC.DESCR.Value = "Y" &I;

&REC.EMPLID.Value = &I;

&REC.Insert();

The following is an example using a SQL object for doing an insert:

&SQL = CreateSQL("%INSERT(:1)");

&REC = CreateRecord(Record.GREG);

&SQL.BulkMode = True;

For &I = 1 to 10

&REC.DESCR.Value = "Y" &I;

&REC.EMPLID.Value = &I;

&SQL.Execute(&REC);

-Ganesh A.M

Useful Site for Oracle Syntax:

http://www.iselfschooling.com/syntax/OracleSyntax.htm

Click on the image to view a larger picture...

-Sandeep G

File layouts for inserting data into parent-child records. (File layouts for inserting data in to multiple records).

Define the file layout with the child record as sub-segment. In File layout segment properties define the file record id. A number that should appear as a first column in the input file to identify that it contains the data for its corresponding record.

Since we have more than one record, we need to set this file record id for the both records / segments.

888 – File record id - This record contains only one field and its value is A.

000 – File record id – Contains the data for the other record.

Sample file layout structure.

888 A

000 8001 VAC 1981-09-12 1981-09-26 14 .0 P Y

888 A

000 8001 VAC 1983-03-02 1983-03-07 5 .0 P Y

888 A

000 8001 VAC 1983-08-26 1983-09-10 13 .0 P Y

888 A

000 8516 MAT 1986-06-06 1986-08-01 56 .0 P Y

- Ganesh A.M

Syntax to Find Total Size of Database

Size in Megabytes:

Select sum(bytes)/1024/1024 from dba_data_files;

Syntax to Find Used Size of Database

Select sum(bytes)/1024/1024 from dba_segments;

Syntax to Find Free Size of Database

Select sum(bytes)/1024/1024 from dba_free_space;

Thanks

D.Sasi Kumar

10 steps for Upgrading Oracle from 8.1.7 to 10.1.0.2.0

1. Install 10-G in new ORACLE_HOME path.
2. Change to 10-G Path as below:

cd $ORACLE_HOME/rdbms/admin

copy utlu101i.sql to new dir

3. In 8.1.7 database run utlu101i.sql, which gives the output about system requirements, the Parameter should be updated etc.

SQL> spool upgr.lst

SQL>@ utlu101i.sql;

SQL>spool off;

From the upgr.lst, note the changes to be made.

4. Copy init file from 8.1.7 dbs to <newpath> make changes to init parameters as mentioned in upgr.lst
5. Startup the database in upgrade mode:

SQL> startup pfile=’<newpath>/inittest.ora’ upgrade;

6. Create sysaux tablespace

SQL> create tablespace sysaux

Datafile ‘<newpath>/sysaux.dbf’ size 500M

Extent management local

Segment space management auto online;

7. Run the upgrade script.

SQL> @ ‘$ORACLE_HOME/rdbms/admin/u0801070.sql’;

8. Shutdown the database.

SQL>shutdown immediate;

9. Startup the database

SQL> startup;

10. Recompile the Invalid objects:

SQL> @’$ORACLE_HOME/rdbms/admin/utlrp.sql’

Thanks and Regards

D.Sasi kumar

Is it possible to create a trigger using “CREATE OR REPLACE TRIGGER TRIGGER_NAME”, where the trigger_name is already being used by another trigger under some other table ?. If your answer is YES, then this article may help you.

In general we used to say that, if we create a trigger using Create or replace statement, then the trigger will be altered with the new trigger code. But if use CREATE OR REPLACE statement to create a trigger and if the trigger name is already being used by a trigger under another table, then ORACLE will raise an ORA-04095 exception.

The following code will give you some more clarity on the above explanation:

Table 1 Creation :

create table trig1(id number(3));

Trigger 1 creation for the above table :

create or replace trigger trg_trig1

before insert on trig1

begin

null;

end;

/

Table 2 Creation :

create table trig1(id number(3));

Trigger 2 creation for the above table :

create or replace trigger trg_trig1

before insert on trig2

begin

null;

end;

/

create or replace trigger trg_trig1

*

ERROR at line 1:

ORA-04095: trigger 'TRG_TRIG1' already exists on another table, cannot replace

It

Explaination :

Thus the above code creates two table called trig1 and trig2. Trig1 has a before insert trigger called trg_trig1.Then we have the code for trg_trig1 trigger on trig2. Oracle doesn’t allow you to create the trigger by raising ORA-04095 exception.

Thanks

Vivek Member – C9

I would like to share, few more details on “Mutating and Constraining table in Oracle” written by Sasi. Mutating error won’t occur when we go for statement level trigger. In general, it is said, that Mutating error will occur, if we refer the mutating table at the trigger body, in a row level trigger. There is an exceptional case, where the mutating error won’t occur, if we refer the mutating table in the trigger body. It is for before insert, at row level. The concept here is, if we perform a single row insert (by using Values clause), and if we refer the same table (the mutating table) at the trigger body, then we won’t get any mutating table error. Also the insert should be a single row one. So if you perform an multiple row insert(say : insert into tableA select * from table B), then Mutating error will occur. Also if multiple row insert statement, inserts only one record, we will get the mutating error. So in general, only for the before insert statement (with single row) at row level, won’t raise the mutating error.

The following table will explain you about the occurrences of mutating error.

-----------------------------------------------------------------

OPERATION TYPE MUTATING?

-----------------------------------------------------------------

insert before/statement-level No

insert after/statement-level No

update before/statement-level No

update after/statement-level No

delete before/statement-level No

delete after/statement-level No

insert before/row-level Single row Multi-row

No Yes

insert after/row-level Yes

update before/row-level Yes

update after/row-level Yes

delete before/row-level Yes

delete after/row-level Yes

-----------------------------------------------------------------

Example :

Table Creation :

create table tab_trig(id number(3));

Trigger Creation:

create or replace trigger trig1

before insert on tab_trig

for each row

declare

v_mx tab_trig.id%type;

begin

select nvl(max(id),0) into v_mx from tab_trig;

dbms_output.put_line('Max Value : 'v_mx);

update tab_trig set id=:new.id+7;

select nvl(max(id),0) into v_mx from tab_trig;

dbms_output.put_line('Max Value After Incrementing Id by 7 : 'v_mx);

update tab_trig set id=:new.id-7;

select nvl(max(id),0) into v_mx from tab_trig;

dbms_output.put_line('Max Value After Decrementing Id by 7: 'v_mx);

end;

/

Sample Execution:

SQL> insert into tab_trig values(&id);

Enter value for id: 1

old 1: insert into tab_trig values(&id)

new 1: insert into tab_trig values(1)

Max Value : 0

Max Value After Incrementing Id by 7 : 0

Max Value After Decrementing Id by 7: 0

1 row created.

SQL> /

Enter value for id: 2

old 1: insert into tab_trig values(&id)

new 1: insert into tab_trig values(2)

Max Value : 1

Max Value After Incrementing Id by 7 : 9

Max Value After Decrementing Id by 7: -5

1 row created.

SQL> /

Enter value for id: 3

old 1: insert into tab_trig values(&id)

new 1: insert into tab_trig values(3)

Max Value : 2

Max Value After Incrementing Id by 7 : 10

Max Value After Decrementing Id by 7: -4

1 row created.

Thanks

Vivek Member - Cloud Nine

How to use Query Parameter in exp command line:

When your column in the where clause is of TYPE VARCHAR

exp file=partial1.dmp log=partial2.log tables=PS_JOB,PS_JOB_JR,PS_COMPENSATION,PS_AS_AUDIT_JOB query=\"where emplid=\'220627489\'\" userid=scott/tiger

When your column in the where clause is of TYPE NUMBER

exp file=partial1.dmp log=partial2.log tables=PS_JOB,PS_JOB_JR,PS_COMPENSATION,PS_AS_AUDIT_JOB query=\"where empno=10\" userid=scott/tiger

Thanks

D.Sasi Kumar

Some of Frequently Used Oracle Hints:

Thanks

D.Sasi Kumar

We all know, that the default format support by Oracle (from 9i) is DD-Mon-RR.

Does this mean, we need to give the same format, while inserting records to the table?.

If your answer is Yes, then continue reading the article.

In general, oracle assumes that the date needs to be given in dd, followed by Mon followed by RR/YYYY.

So in between the data components, we are allowed to use any character. So the following insert statements

will insert records in to the table dat1(with 1 column, called col1 of type date), without any error.

Note: FYI, In all the cases, to_date( ) function is not used.

insert into dat1 values('12?Jan?2007')

insert into dat1 values('12/Jan?2007')

insert into dat1 values('12!Jan#2007')

insert into dat1 values('12$jan%2007')

insert into dat1 values('12^Jan&2007')

insert into dat1 values('12/Jan?2007')

SQL> select * from dat1;

COL1

---------

12-JAN-07

12-JAN-07

12-JAN-07

12-JAN-07

12-JAN-07

12-JAN-07

6 rows selected.

Thanks & Regards

Vivek Member - Cloud Nine