T SQL procedure to update data

-- Counter
DECLARE @pos_cnt int
DECLARE @ok_cnt int
DECLARE @error_cnt int
DECLARE @cntry_code_start_pos int
DECLARE @cntry_code_len int

-- Converted Phone
DECLARE @new_phone varchar(24)
-- Valid Phone Flag
DECLARE @phone_data_ok varchar(1)
-- Valid Country Code Flag
DECLARE @valid_coutry_code varchar(1)

DECLARE @emplid varchar(11)
DECLARE @phone_type varchar(4)
DECLARE @phone varchar(24)
DECLARE @local_phone varchar(24)
DECLARE @country_code varchar(6)
DECLARE @country_code_to_check varchar(6)

-- Declare a temp table
DECLARE @phone_table TABLE (
EMPLID varchar(11),
PHONE_TYPE varchar(4),
NGB_LOCAL_PHONE varchar(18),
MESSAGE varchar(100)
)

SET NOCOUNT ON

declare PhoneData cursor
for select EMPLID, PHONE_TYPE, PHONE, NGB_LOCAL_PHONE, NGB_INTL_DIAL_CD from PS_PERSONAL_PHONE
WHERE PHONE <> ' ' AND PHONE NOT LIKE '%@%'
--AND EMPLID = '16933'
for update

open PhoneData
fetch next from PhoneData INTO @emplid, @phone_type, @phone, @local_phone, @country_code

-- initialise counters
set @ok_cnt = 0
set @error_cnt = 0
while @@fetch_status = 0
begin
-- Check each position of the NGB_LOCAL_PHONE field.
SET @pos_cnt = 1
SET @new_phone = ''
SET @phone_data_ok = 'Y'
SET @phone = LTRIM(@phone)
-- process each position from the Phone field
WHILE @pos_cnt <= 24 BEGIN
-- if it is a number or space, then it is OK
IF CHARINDEX(SUBSTRING(ltrim(@phone), @pos_cnt, 1),' 0123456789') > 0
OR (SUBSTRING(LTRIM(@phone),@pos_cnt,1) = '+' AND @pos_cnt = 1)
OR SUBSTRING(LTRIM(@phone),@pos_cnt,1) = ' ' begin
set @new_phone = @new_phone + SUBSTRING(LTRIM(@phone),@pos_cnt,1)
end
else begin
-- if it is ./()- or non breaking space then replace it with space
IF CHARINDEX(SUBSTRING(ltrim(@phone), @pos_cnt, 1),'./()-') > 0 or
ascii(SUBSTRING(ltrim(@phone), @pos_cnt, 1)) = 160 begin
set @new_phone = @new_phone + ' '
end
-- otherwise it is an error
else begin
SET @phone_data_ok = 'N'
print 'Invalid character ' + SUBSTRING(ltrim(@phone), @pos_cnt, 1) + ' / ASCII ' + convert(char(3), ascii(SUBSTRING(ltrim(@phone), @pos_cnt, 1))) + ' for EMPLID ' + @emplid + ' PHONE_TYPE ' + @phone_type + ' on Pos# ' + convert(char(2), @pos_cnt)
set @pos_cnt = 25
end
end
-- print '@pos_cnt ' + convert(char(2),@pos_cnt) + ' @new_phone ' + @new_phone
set @pos_cnt = @pos_cnt + 1
END
-- remove any leading spaces
set @new_phone = ltrim(@new_phone)
set @country_code = ' '
-- check if the Phone has leading + or 00
set @cntry_code_start_pos = 0
if left(@new_phone,1) = '+' begin
set @cntry_code_start_pos = 2
end
if left(@new_phone,2) = '00' begin
set @cntry_code_start_pos = 3
end
-- if there was an international dialing code present, then try to validate
if @cntry_code_start_pos > 0 begin
set @cntry_code_len = 5
WHILE @cntry_code_len >= 1 BEGIN
set @country_code_to_check = SUBSTRING(@new_phone ,@cntry_code_start_pos,@cntry_code_len)
-- print 'Validating Country code ' + @country_code_to_check + ' for @new_phone ' + @new_phone
set @valid_coutry_code = 'N'
select @valid_coutry_code = 'Y', @country_code = NGB_INTL_DIAL_CD from PS_NGB_INTL_DIALCD B
WHERE @country_code_to_check = B.NGB_INTL_DIAL_CD
AND LEN(B.NGB_INTL_DIAL_CD) = @cntry_code_len
if @valid_coutry_code = 'Y' begin
set @new_phone = ltrim(SUBSTRING(@new_phone, @cntry_code_start_pos + @cntry_code_len , 24))
-- print 'Valid Country code for @new_phone ' + @new_phone + ' @country_code ' + @country_code + ' @new_phone ' + @new_phone + ' @cntry_code_start_pos ' + convert(char(2), @cntry_code_start_pos) + ' @cntry_code_len ' + convert(char(2), @cntry_code_len)
set @cntry_code_len = 0
end
set @cntry_code_len = @cntry_code_len - 1
end
end
if @phone_data_ok = 'Y' and len(@new_phone) <= 17 begin
-- print 'Updating for EMPLID ' + @emplid + ' PHONE_TYPE ' + @phone_type + ' from PHONE ' + @phone + ' to NGB_LOCAL_PHONE ' + @new_phone + ' NGB_INTL_DIAL_CD ' + @country_code
update PS_PERSONAL_PHONE SET NGB_LOCAL_PHONE = @new_phone, NGB_INTL_DIAL_CD = @country_code
WHERE EMPLID = @emplid
and PHONE_TYPE = @phone_type

set @ok_cnt = @ok_cnt + 1
end
else begin
set @error_cnt = @error_cnt + 1
end
fetch next from PhoneData INTO @emplid, @phone_type, @phone, @local_phone, @country_code
end
print 'Update Count ' + convert(char(7), @ok_cnt) + '. Error Count ' + convert(char(7), @error_cnt)
close PhoneData
deallocate PhoneData

Find out who last updated a PeopleCode

The following SQL is an example of finding out who last updated a specific Component Record Field PeopleCode but can be modified to check the audit on other types of PeopleCode events as well.

select * FROM PSPCMPROG
WHERE OBJECTID1 = 10
AND OBJECTID2 = 39
AND OBJECTID3 = 1
AND OBJECTID4 = 2
AND OBJECTVALUE1 = ' '
AND OBJECTVALUE3 = ' '
AND OBJECTVALUE4 = ' '
AND OBJECTVALUE5 = 'FieldChange'

PSPROJECTITEM OBJECTTYPE values

The translate values are stored in PS_WHEREUSEDOBJTBL table from Tools ver 8.49.

For older tools version, the list is:

0 Record
1 Index
2 Field
3 Format definition
4 Translate
5 Page
6 Menu
7 Components
8 Record PeopleCode
9 Menu PeopleCode
10 Query
11 Tree structure
12 Tree*
13 Access group*
14 Color
15 Style
16 Not used
17 Business process
18 Activity
19 Role*
20 Process definition
21 Process server
22 Process type
23 Process job
24 Process recurrence
25 Message catalog entries*
26 Dimension*
27 Cube definitions*
28 Cube instance definitions*
29 Business interlink
30 SQL definition
31 File layout definition
32 Component interfaces
33 Application Engine program
34 Application Engine section
35 Message node
36 Message channel
37 Message definition
38 Approval rule set
39 Message PeopleCode
40 Subscription PeopleCode
41 Not used
42 Component interface PeopleCode
43 Application engine PeopleCode
44 Page PeopleCode
45 Page field PeopleCode
46 Component PeopleCode
47 Component record PeopleCode
48 Component record field PeopleCode
49 Image
50 Style sheet
51 HTML
52 Not used
53 Permission list
54 Portal registry definitions
55 Portal registry structures
56 URL definitions
57 Application Packages
58 Application Package PeopleCode
59 Portal Registry User homepage
60 Problem type definition
61 Archive templates (deprecated in PeopleTools release 8.44 and
above).
62 XSLT
63 Portal Registry User Favorite
64 Mobile page
65 Relationships
66 Component Interface Property PeopleCode**
67 Optimization Models***
68 File References***
69 File Reference Type Code***
70 Archive object definitions***
71 Archive Templates (Type 2)***
72 Diagnostic Plug-Ins***

Content Reference (CREF) with link to external site

Navigate to PeopleTools >
Portal > Structure and Content. Chose the folder you would like your
link to be in or you can add a new folder just for this new link if
you'd like.

Now scroll all the way to the bottom and click "Add Content Reference".

Name: Add what ever name you would like here. Users will not see this link.
Label: This will end up being the link the users will click on.
Long Description: Long desc for your link - this will show just below your actual link.
Usage Type: Target
No Template Check Box: Make sure you CHECK this one so the portal template wont wrap around your page.

URL Information

URL Type: Non-PeopleSoft URL
Portal URL: The website you are trying to open (example: http://www.CompShack.com) :)

Now in the Content Reference Attributes of the Content Ref Administration page add the following to get your page to open in a new window:

Name: NAVNEWWIN
Label: You can leave this one blank
Attribute value: true
Translate Check Box: Make sure this is UNCHECKED