Impact List for Projects





-- 1. Copy the results
-- 2. Paste into Word then: Table/Convert/Text to Table...
-- 3. You may need copy and paste formatting from other cells.

define projectName = 'GUFNGL018'

--select object, type, ' ', 'New', 'Easy', 0, 0, ' ', ' ' from (
select * from (
SELECT
a.projectname,
A.OBJECTVALUE1 || decode(A.OBJECTVALUE2,' ','','.'||A.OBJECTVALUE2) || decode(A.OBJECTVALUE3,' ','','.')|| A.OBJECTVALUE3 || decode(A.OBJECTVALUE4,' ','','.') || A.OBJECTVALUE4 as Object,
decode(a.objecttype,
'0', 'Record',
'1', 'Index',
'2', 'Field',
'3', 'Field Format',
'4', 'Translate value',
'5', 'Page',
'6', 'Menu',
'7', 'Component',
'8', 'Record Peoplecode',
'9', 'Unknown Object Type',
'10','Query',
'11','Tree Structure',
'12','Tree',
'13','Access Group',
'14','Colours',
'15','Style',
'16','Unknown Object Type',
'17','Business Processes',
'18','Activities',
'19','Roles',
'20','Process Defintions',
'21','Server Definition',
'22','Process Type Definitions',
'23','Job Definitions',
'24','Recurrence Definition',
'25','Message Catalog Entries',
'26','Dimensions',
'27','Cube Definitions',
'28','Cube Instance Definitions',
'29','Business Interlink',
'30','Record SQL',
'31','File Layout Definitions',
'32','Component Interface',
'33','Application Engine Programs',
'34','Application Engine Sections',
'35','Message Node',
'36','Message Channels',
'37','Messages',
'38','Approval Rule Sets',
'39','Unknown Object Type',
'40','Unknown Object Type',
'41','Unknown Object Type',
'42','Unknown Object Type',
'43','Unknown Object Type',
'44','Page Peoplecode',
'45','Unknown Object Type',
'46','Component Peoplecode',
'47','Unknown Object Type',
'48','Component Rec Fld Peoplecode',
'49','Images',
'50','Style Sheet',
'51','HTML',
'52','File Reference',
'53','Permission Lists',
'54','Portal Registry Definitions',
'55','Portal Registry Structures',
'56','URL Definition',
'57','Application Packages',
'58','Unknown Object Type',
'59','Unknown Object Type',
'60','Unknown Object Type',
'61','Archive Template',
'62','Unknown Object Type',
'63','Portal registry User Favourites',
'64','Mobile page',
'PeopleSoft are being smart again with - ' || a.objecttype ) as Type,
decode(UPGRADEACTION,0,'Copy',1,'Delete',2,'Manual',3,'Copy Properties') as Action
from sysadm.PSPROJECTITEM A
where a.objecttype <> '52'
and not (objecttype=0 and objectid2 = 2)
and not (objecttype=2 and objectid2 = 102)
union
SELECT
a.projectname,
decode(A.OBJECTVALUE2,' ','',A.OBJECTVALUE2) || decode(A.OBJECTVALUE1,' ','',A.OBJECTVALUE1) as Object,
'File Reference' as Type,
decode(UPGRADEACTION,0,'Copy',1,'Delete',2,'Manual',3,'Copy Properties') as Action
from sysadm.PSPROJECTITEM A
where a.objecttype = '52'
)
where projectname like &projectName
--order by 2,1
order by 3,2

Getting SQRs to Publish to the RDS/Report Manager Everytime

See Also: http://capital-it.blogspot.com/2008/09/publishing-files-to-report-managerrds.html

In short if you want a process's output to appear in the RDS it has to end up residing in the same folder that is created for the process at run-time.

For this example we are going to be producing a .ltr file using an SQR process called 'PROCNAME'. In the end we want a file path like this, where 501 is the Process Instance number:

/opt/psoft8/HE900D9A/appserv/prcs/HE90D9A/log_output/SQR_PROCNAME_501/SQR_PROCNAME_501.LTR

Note that up to "log_output' is most likely going to be a system variable, set in something like setfile.sqc so it can be modified for each environment.

Note that the folder name (SQR_PROCNAME_501) must be in this format, where 'SQR' is the Process Type, 'PROCNAME' is the Process Name and '501' is the Process Instance Number.

The file name syntax is the same as the folder name (although at time of writing I think you may actually be able to get away with whatever file name you please).

Update: It appears App Engines require the files to be in the strict format "AE_ProcName_ProcInstance.*" where * is an extension. This was working when output type was Web/TXT.


The SQR code looks something like this:

#DEFINE FILEPREFIX e:\psoft8\he900d9a\appserv\prcs\HE90D9A\log_output\

! Folder
Move '{FILEPREFIX}' to $fileName
Concat 'SQR_PROCNAME_' with $fileName
Concat $prcs_process_instance with $fileName
Concat '\' with $fileName

! File.
Concat 'PROCNAME_' with $fileName
Concat $prcs_process_instance with $fileName
Concat '.LTR' with $fileName

New-Report $fileName