Peoplecode to Read and Write a File - Send email within Peoplecode using SendMail() function

Here is some sample code to read and write a file. You will have to design your code to handle your environment paths...

/*** Read a file ***/

Local File &ASU_ADDR_WRK;
Local Rowset &rsInput_Rowset;
Local Record &Rec1;
Local SQL &SQL1;
&Rec1 = CreateRecord(Record.ASU_ADDR_WRK);
&SQL1 = CreateSQL("%Insert(:1)");
If ASU_ADDR_AET.PROCESS_INSTANCE > 0 Then
&pshome = GetEnv("PS_HOME");
&InboundDirectory = &pshome "/datafiles/interfacein/";
&Filename = &InboundDirectory “asu_addr.csv";
End-If;
If FileExists(&Filename, %FilePath_Absolute) Then
&ASU_ADDR_WRK = GetFile(&Filename, "R", "A", %FilePath_Absolute);
End-If;
&ASU_ADDR_WRK.SetFileLayout(FileLayout.ASU_ADDR_WRK);
&rsInput_Rowset = CreateRowset(Record.ASU_ADDR_WRK);
&rsInput_Rowset = &ASU_ADDR_WRK.ReadRowset();
While &rsInput_Rowset <> Null
&rsInput_Rowset.GetRow(1).ASU_ADDR_WRK.CopyFieldsTo(&Rec1);
&SQL1.Execute(&Rec1);
&rsInput_Rowset = &ASU_ADDR_WRK.ReadRowset();
End-While;
&ASU_ADDR_WRK.Close();


/*** Write to a file ***/

Local File &ASU_ADDR_DUP;
Local Record &Rec3;
Local SQL &SQL3;
&Rec3 = CreateRecord(Record.ASU_ADDR_DUP);
&SQL3 = CreateSQL("%SelectAll (:1) ORDER BY EMPLID, ADDRESS_TYPE", &Rec3);
If ASU_ADDR_AET.PROCESS_INSTANCE > 0 Then
&pshome = GetEnv("PS_HOME");
&OutboundDirectory = &pshome "/ datafiles/interfaceout/"; ";
&FileName2 = &OutboundDirectory “asu_addr_dup.csv";
End-If;
&ASU_ADDR_DUP = GetFile(&FileName2, "W", "A", %FilePath_Absolute);
If &ASU_ADDR_DUP.IsOpen Then
If &ASU_ADDR_DUP.SetFileLayout(FileLayout.ASU_ADDR_DUP) Then;
While &SQL3.Fetch(&Rec3)
&ASU_ADDR_DUP.WriteRecord(&Rec3);
End-While;
End-If;
End-If;
&ASU_ADDR_DUP.Close();


/*** Send and email ***/

&MAIL_FLAGS = 0;
&MAIL_TO = “Jeromy.McMahon@asu.edu";
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = “ASU Application Engine Training Class";
&MAIL_TEXT = "See attachment for the file created in AE class";
&pshome = GetEnv("PS_HOME");
&pshome "/datafiles/interfaceout/";
&MAIL_FILES = &pshome "asu_addr_dup.csv";
&MAIL_TITLES = “asu_addr_dup.csv";
&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES);

Find Employees who are terminated and not locked out of the system

Here is a rough query that will display all the employees who have a max termed row and are not locked out of the system.

SELECT DISTINCT A.OPRID, A.EMPLID, C.ALTER_EMPLID, A.ACCTLOCK, B.NAME, A.LASTUPDDTTM, A.LASTPSWDCHANGE FROM PSOPRDEFN A, PS_NAMES B, PS_PERS_DATA_EFFDT C, PS_JOB JOBWHERE A.ACCTLOCK <> 1 AND A.EMPLID = B.EMPLID AND A.EMPLID = C.EMPLID AND JOB.EMPLID = A.EMPLID AND JOB.EFFDT = (SELECT MAX(JOB1.EFFDT) FROM PS_JOB JOB1 WHERE JOB1.EMPLID = JOB.EMPLID AND JOB1.EFFSEQ = JOB.EFFSEQ AND JOB1.EFFDT <= SYSDATE) AND JOB.EFFSEQ = (SELECT MAX(JOB2.EFFSEQ) FROM PS_JOB JOB2 WHERE JOB2.EMPLID = JOB.EMPLID AND JOB2.EFFDT = JOB.EFFDT) AND JOB.ACTION = 'TER'

Updating the PS_PAY_CALENDAR

Here is some sample code that will set the update flags for PS_PAY_CALENDAR.

update ps_pay_calendar
set PAY_SHEETS_RUN = 'Y', PAY_PRECALC_RUN = 'Y', PAY_CALC_RUN = 'Y', PAY_CONFIRM_START = 'Y', PAY_CONFIRM_RUN = 'Y', SINGLE_CHECK = 'Y', PAY_OFF_CYCLE_CLS = 'Y'
where run_id = '20070805' -- You will have to replace with your specific run_id

update ps_pay_calendar
set PAY_CONFIRM_START = 'Y', PAY_CONFIRM_RUN = 'Y'
where run_id = '20070729'

DBA Extents Segments - Sample SQL

select s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocksfrom sys.v_$session s, sys.v_$sort_usage u where s.saddr = u.session_addr


SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free) FROM V$temp_space_header GROUP BY tablespace_name


select s.osuser, s.process, s.username, s.serial#, sum(u.blocks)*vp.value/1024 sort_sizefrom sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vpwhere s.saddr = u.session_addr and vp.name = 'db_block_size' --and s.osuser like '&1'group by s.osuser, s.process, s.username, s.serial#, vp.value

-- Free Table Space

SELECT /* + RULE */ df.tablespace_name tspace,df.bytes/(1024*1024) tot_ts_size,sum(fs.bytes)/(1024*1024) free_ts_size,nvl(round(sum(fs.bytes)*100/df.bytes),1) ts_pct,round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1 FROM dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) dfWHERE fs.tablespace_name(+) = df.tablespace_name GROUP BY df.tablespace_name, df.bytesunion all

SELECT /* + RULE */ df.tablespace_name tspace, fs.bytes/(1024*1024) tot_ts_size, sum(df.bytes_free)/(1024*1024) free_ts_size, nvl(round((sum(fs.bytes)- df.bytes_used) *100/fs.bytes),1) ts_pct, round((sum(fs.BYTES) - df.BYTES_free )*100/fs.bytes) ts_pct1FROM dba_temp_files fs , (select tablespace_name, bytes_free, bytes_used from V$temp_space_header group by tablespace_name, bytes_free, bytes_used ) dfWHERE fs.tablespace_name(+) = df.tablespace_nameGROUP BY df.tablespace_name, fs.bytes, df.bytes_free,df.BYTES_usedorder by 4 desc

--Table spaces

SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_freeFROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total

--Rows for each statement using sort segment space:

SELECT S.sid ',' S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, T.sqladdr address, Q.hash_value, Q.sql_textFROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.sqladdr = Q.address (+)AND T.tablespace = TBS.tablespace_name ORDER BY S.sid

Peoplesoft perfomance

Monitoring Tools - Oracle Database


Written by David Vandiver
(Topic formerly known as "Active Sessions with SQL Statements")
This is a collection of SQL statements that you can run to see activity and performance on your PeopleSoft system.
You will need read permission to the following tables:
v$session
psoprdefn
v$sqlarea
v$sql_cursor
v$sql_bind_data
v%sql

The first SQL gives you active sessions, including the operator's name from the PSOPRDEFN table. There is also a "Kill Statement" built as one of the columns. This allows you to copy and paste the "Kill SQL" column and send it to the dba quickly.
-- Sessions and PSOPRDEFN

select nvl(b.oprdefndesc,'Blank'), a.CLIENT_INFO,a.OSUSER, a.machine, a.PROGRAM,a.LOCKWAIT, a.LOGON_TIME,'ALTER SYSTEM KILL SESSION ''' a.SID ',' a.SERIAL# ''';' "Kill SQL"from v$session a, psoprdefn bwhere status = 'ACTIVE'and a.USERNAME is not nulland substr(a.client_info, 1, 7) = b.oprid (+);


The last three are still being tweaked, but should work.

-- For statistics use:

SELECT * FROM (SELECT hash_value , sum(disk_reads) , sum(buffer_gets) ,sum(rows_processed), sum(buffer_gets)/greatest(sum(rows_processed),1) , sum(executions) , sum(buffer_gets)/greatest(sum(executions), 1) from V$SQL where command_type in (2,3,6,7) group by hash_value order by 5 desc) WHERE rownum <= 10;

--To get the SQL text for the above statistics use the following:

select t.SQL_TEXT from v$sqlarea t where t.HASH_VALUE in (SELECT hash_value FROM (SELECT hash_value , sum(disk_reads) , sum(buffer_gets) ,sum(rows_processed), sum(buffer_gets)/greatest(sum(rows_processed),1) , sum(executions) , sum(buffer_gets)/greatest(sum(executions), 1) from V$SQL where command_type in (2,3,6,7) group by hash_value order by 5 desc) WHERE rownum <= 10);

--To find the top ten run sql’s:

SELECT * FROM (SELECT hash_value,address,substr(sql_text,1,40) sql, buffer_gets, executions, buffer_gets/executions "Gets/Exec", sql_text "Full SQL Text" FROM V$SQLAREA WHERE buffer_gets > 100000 AND executions > 10 ORDER BY buffer_gets DESC)WHERE rownum <= 10;

Hidden Folders and Content References

I wanted to post this on my blog as well. This was orginally posted by peoplesoftexperts.blogspot.com

• If you do not see a folder or content Reference (Menu Item) in Left hand side navigation, (Applicable to 8.4x ) then you must check to see if the folder or content reference is not marked as hidden. Other reasons could be security. Here is a SQL to find out all the objects that are hidden.
  To find all the folders which are hidden from Portal Navigation.
  select * from PSPRSMSYSATTRVL where portal_name = 'EMPLOYEE' and PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and portal_Reftype = 'F'
• 
  select a.portal_objname,b.portal_label,b.portal_prntobjname,c.portal_labelfrom PSPRSMSYSATTRVL a,psprsmdefn b,psprsmdefn c where a.portal_name = 'EMPLOYEE' and a.PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and b.portal_Reftype = 'F' and a.portal_name = b.portal_name and a.portal_objname = b.portal_objnameand b.portal_name = c.portal_nameand b.portal_prntobjname = c.portal_objname
  
• To find all the content references which are hidden from Portal Navigation.
  select * from PSPRSMSYSATTRVL where portal_name = 'EMPLOYEE' and PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and portal_Reftype = 'C'
  

select a.portal_objname,b.portal_label,b.portal_prntobjname,c.portal_labelfrom PSPRSMSYSATTRVL a,psprsmdefn b,psprsmdefn c where a.portal_name = 'EMPLOYEE' and a.PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and b.portal_Reftype = 'C' and a.portal_name = b.portal_name and a.portal_objname = b.portal_objnameand b.portal_name = c.portal_nameand b.portal_prntobjname = c.portal_objname

Replace EMPLOYEE with Your portal name.
Some of the Other names are.
CUSTOMER
DEMOSITE
EMPLOYEE
MOBILE
PORTAL
PS_SITETEMPLATE
SUPPLIER

Portal Content Reference/folder Attributes are stored in
PSPRSMSYSATTR
PSPRSMSYSATTRVL