Queries in Project and number of Records

select
b.qryname
, b.descr
, b.descrlong
, (select count(*) from PSQRYRECORD where qryname = b.qryname) as Num_Records
from
psprojectitem a
, psqrydefn b
where a.projectname = 'UQXM1210'
and b.qryname = a.objectvalue1
order by 4

App Engine Comments

Would seem that you can make comments in App Engine SQL Actions if:

#1 You use the /* */ commenting standard,
#2 Comments are after the WHERE clause.
#3 Comments do not contain a "%", such as %Table(RecName).

Field Record Definition for Detailed Design

run, copy and paste (work in progess):

DEFINE RECNAME = 'UQ_BNK_RCN_PAYM';
-- FIELD, KEY, SEARCHKEY, LIST, REQ, EDIT, PROMPT TABLE, DEFAULT
SELECT
FIELDNAME AS FIELD
, ' ' AS KEY
, ' ' AS SEARCHKEY
, ' ' AS LISTT
, ' ' AS REQ
, ' ' AS EDIT
, ' ' AS PROMPTTABLE
, ' ' AS DEFAULTT
FROM PSRECFIELD
WHERE RECNAME = &RECNAME
ORDER BY FIELDNUM
;

Security for Purchase Orders, Create & Approve.

Security for Purchase Orders, Create & Approve.

Requestor:
Peopletools > Security > User Profiles
Role: 'xx C PO Officer'
Route Control: (same as the approver)

Set Up Financials/Supply Chain > Common Definitions > User Preferences > Define User Preferences > (Procurement Link)
Operational Unit: (same as the approver)
Requester: 'userA'

> (Purchase Order Authorisations)
Tick everything (if appropriate for environment)
Buyers User Authorised For: 'userA'


Approver:
Peopletools > Security > User Profiles
Role: 'xx W Approver - Fin Lvl 1'
Route Control: (same as the requester)

Set Up Financials/Supply Chain > Common Definitions > User Preferences > Define User Preferences > (Procurement Link)
Operational Unit: (same as the requester)
> (Purchase Order Authorisations)
Tick everything (if appropriate for environment)

File Attachment Utilities

Found some delivered code that actually made things easier for once. Unbelievable I know.
It even has excellent, descriptive comments!!!

Check the Application Package SCM_ARCH_ATTACH.UI.AttachmentHandler

1. Make sure your record contains the attachment fields SCM_ATTACH_ID and ATT_VERSION (ATTACHUSERFILE would be nice too for the user given filename)

2. Place the fields from Record.PV_ATTACH_WRK on the same scroll level.

3. Enjoy.

Excel-To-CI CI Component Interface PeopleCode

While building a custom Excel-To-CI Interface we discovered that each row in the spreadsheet is passed, one at a time, to the CI. So, if for example you had data with one header and two lines you would have 2 rows on the spreadsheet. A call would be made to the CI twice.

Following is example PeopleCode that will handle juggling of the unique key values needed:

Component.SavePreChange()

/* UQAP0780 - UQ SIR 178, Michael Nitschke 03.Sep.08

Handle multiples voucher lines and distribution lines.
The ExcelToCI spreadsheet posts across each line as a distinct voucher.
This code detects that the voucher already exists and marks the row as one that
has to be queried by PeopleCode in SavePostChange(). */

If %Mode = "A" Then

/* Get level 0 records for component. */
Local Record &recVCHR_HDR_STG = GetLevel0()(1).GetRecord(Record.VCHR_HDR_STG);
Local Record &recVCHR_VNDR_STG = GetLevel0()(1).GetRecord(Record.VCHR_VNDR_STG);
Local Record &recVCHR_BANK_STG = GetLevel0()(1).GetRecord(Record.VCHR_BANK_STG);

/* Generate a unique VCHR_BLD_KEY_C1 value. */
&recVCHR_HDR_STG.VCHR_BLD_KEY_C1.Value = &recVCHR_HDR_STG.INVOICE_ID.Value | &recVCHR_HDR_STG.INVOICE_DT.Value | &recVCHR_HDR_STG.VENDOR_ID.Value | &recVCHR_HDR_STG.GROSS_AMT.Value;
&recVCHR_VNDR_STG.VCHR_BLD_KEY_C1.Value = VCHR_HDR_STG.VCHR_BLD_KEY_C1.Value;
&recVCHR_BANK_STG.VCHR_BLD_KEY_C1.Value = VCHR_HDR_STG.VCHR_BLD_KEY_C1.Value;

/* If VCHR_BLD_KEY_C1 value already exists we need to create a 2nd key value for VCHR_BLD_KEY_C2.
> See Component.SavePostChange PeopleCode for where this value/mark is used to cleanup rows. */
Local integer &count = 0;
SQLExec("SELECT count(*) FROM %Table(VCHR_HDR_STG) WHERE VCHR_BLD_KEY_C1 = :1", VCHR_HDR_STG.VCHR_BLD_KEY_C1.Value, &count);
If &count <> 0 Then
VCHR_HDR_STG.VCHR_BLD_KEY_C2.Value = "X" | &count;
&recVCHR_VNDR_STG.VCHR_BLD_KEY_C2.Value = VCHR_HDR_STG.VCHR_BLD_KEY_C2.Value;
&recVCHR_BANK_STG.VCHR_BLD_KEY_C2.Value = VCHR_HDR_STG.VCHR_BLD_KEY_C2.Value;
End-If;

End-If;
/* UQAP0780 - UQ SIR 178 End */


Component.SavePostChange()
/* UQAP0780, UQ SIR 178 Michael Nitschke 03.Sep.2008
Cleanup records that have been inserted as single vouchers but are really*
data for child rows (line and distribution) of already existing vouchers.
These are marked with key field UQ_VCHRHDR_ST_V.VCHR_BLD_KEY_C2 value = 'X'.

NOTE that this cleanup process will also allow duplicate vouchers to be posted
from the spreadsheet to the staging tables, the duplicates will be cleaned up/removed. */

/* Level 0 records are easy, if VCHR_BLD_KEY_C1 = "X" then delete the record.
(b is for buffer) */


Local Record &recVCHR_HDR_STGb = GetLevel0()(1).GetRecord(Record.VCHR_HDR_STG);
If Left(&recVCHR_HDR_STGb.VCHR_BLD_KEY_C2.Value, 1) = "X" Then

Local Record &recVCHR_HDR_STG = CreateRecord(Record.VCHR_HDR_STG);
Local Record &recVCHR_VNDR_STG = CreateRecord(Record.VCHR_VNDR_STG);
Local Record &recVCHR_BANK_STG = CreateRecord(Record.VCHR_BANK_STG);

&recVCHR_HDR_STGb.CopyFieldsTo(&recVCHR_HDR_STG);
&recVCHR_HDR_STGb.CopyFieldsTo(&recVCHR_VNDR_STG);
&recVCHR_HDR_STGb.CopyFieldsTo(&recVCHR_BANK_STG);

&x = &recVCHR_HDR_STG.Delete();
&x = &recVCHR_VNDR_STG.Delete();
&x = &recVCHR_BANK_STG.Delete();

End-If;


/* Line record (b for buffer). */
Local Record &recVCHR_LINE_STGb = GetLevel0()(1).GetRowset(Scroll.VCHR_LINE_STG)(1).GetRecord(Record.VCHR_LINE_STG); /* (There will only ever be one row) */
If Left(&recVCHR_LINE_STGb.VCHR_BLD_KEY_C2.Value, 1) = "X" Then

Local Record &recVCHR_LINE_STG = CreateRecord(Record.VCHR_LINE_STG);
&recVCHR_LINE_STGb.CopyFieldsTo(&recVCHR_LINE_STG);
&tmpKeyVal = &recVCHR_LINE_STG.VCHR_BLD_KEY_C2.Value;
&recVCHR_LINE_STG.VCHR_BLD_KEY_C2.Value = "";

/* If a duplicate row exists (sans the "X" value) then delete.
Else a unique row so update/remove the "X". */
If &recVCHR_LINE_STG.SelectByKey() Then
&recVCHR_LINE_STG.VCHR_BLD_KEY_C2.Value = &tmpKeyVal;
&x = &recVCHR_LINE_STG.Delete();
Else
SQLExec("UPDATE %Table(VCHR_LINE_STG) SET VCHR_BLD_KEY_C2 = ' ' WHERE %KeyEqual(:1)", &recVCHR_LINE_STGb);
End-If;

End-If;


/* Distribution record. (b is for buffer) */
Local Record &recVCHR_DIST_STGb = GetLevel0()(1).GetRowset(Scroll.VCHR_LINE_STG)(1).GetRowset(Scroll.VCHR_DIST_STG)(1).GetRecord(Record.VCHR_DIST_STG); /* (There will only ever be one row) */
If Left(&recVCHR_DIST_STGb.VCHR_BLD_KEY_C2.Value, 1) = "X" Then

Local Record &recVCHR_DIST_STG = CreateRecord(Record.VCHR_DIST_STG);
&recVCHR_DIST_STGb.CopyFieldsTo(&recVCHR_DIST_STG);
&tmpKeyVal = &recVCHR_DIST_STG.VCHR_BLD_KEY_C2.Value;
&recVCHR_DIST_STG.VCHR_BLD_KEY_C2.Value = "";

/* If a duplicate row exists (sans the "X" value) then delete.
Else a unique row so update/remove the "X". */
If &recVCHR_DIST_STG.SelectByKey() Then
&recVCHR_DIST_STG.VCHR_BLD_KEY_C2.Value = &tmpKeyVal;
&x = &recVCHR_DIST_STG.Delete();
Else
SQLExec("UPDATE %Table(VCHR_DIST_STG) SET VCHR_BLD_KEY_C2 = ' ' WHERE %KeyEqual(:1)", &recVCHR_DIST_STGb);
End-If;

End-If;

/* UQAP0780, UQ SIR 178 End */

Excel-To-CI Field Processing Order

When using the Excel-To-CI spreadsheets fields are processed in the same order that they appear on the records in the CI. A problem can occur if field A is using the value of field B to populate a key value on its prompt table as field B will be populated after field A. Note again that the order these fields appear on the page is not taken into consideration.

To get around this problem you need to add a Derived Work record field on the page, up at level ) that will copy its value to Field B. This DW field will be populated 1st.

Pause/Sleep/Wait

Elegant way to pause or sleep processing for a while without using CPU time. In PeopleCode:

Local JavaObject &thread = GetJavaClass("java.lang.Thread");
&thread.sleep(2500); // this is in milliseconds

Indexes on Delivered Tables

1. PSMENUITEM
create index psoft.MW_PSMENUITEM on psoft.PSMENUITEM ( PNLGRPNAME, ITEMNAME ) tablespace psindex;

2. PSPNLGROUP
create index psoft.MW_PSPNLGROUP on psoft.PSPNLGROUP ( PNLNAME, ITEMNAME ) tablespace psindex;

3. PSAUTHITEM
create index psoft.MW_PSAUTHITEM on psoft.PSAUTHITEM ( DISPLAYONLY, BARITEMNAME, PNLITEMNAME, CLASSID ) tablespace psindex;

4. PS_VAT_DEFAULT_HDR
create index PSOFT.MW_PS_VAT_DEFAULT_HDR on PSOFT.PS_VAT_DEFAULT_HDR ( VAT_DRIVER, COUNTRY, STATE, VAT_DRIVER_KEY1, VAT_DRIVER_KEY2, VAT_DRIVER_KEY3, EFF_STATUS, EFFDT, END_EFFDT, VAT_DRIVER_KEY4, VAT_DRIVER_KEY5, VAT_SETID );

Selecting CLOB Fields

Can be hard if you are using DISTINCT or LIKE clauses et al in your statement. Try this Oracle function:

SELECT
dbms_lob.substr(j.comments_2000,1,1000)

Publishing Files to Report Manager/RDS

See also: http://capital-it.blogspot.com/2008/02/getting-sqrs-to-publish-to-rds.html

In short the generated files must be created in the same folder that is generated for the process. I've found that App Engines at least are very fussy with filenames and they must follow the following convention:

AE_ProcessName_ProcessInstance.txt/csv

I have found, with App Engines at least, that the RDS only likes extensions txt and csv. Worth trying a few more.

The path can be found here:

SELECT PRCSOUTPUTDIR || '\'
FROM PSPRCSPARMS
WHERE PRCSINSTANCE = 11579


Example code:

Local string &pathNm
SQLExec("select prcsoutputdir || '\' from PSPRCSPARMS where prcsinstance = :1", UQ_UQAP0860_AET.PROCESS_INSTANCE.Value, &pathNm);

Local string &fileNm = "AE_UQAP0860_" | UQ_UQAP0860_AET.PROCESS_INSTANCE.Value | ".csv";

Note: I have noticed that the file does not publish to RDS/Report Manager if... well I'm not sure. Check that filename is not too long, does not contain extra periods (don't use %DateTime in the filename) etc., etc..

Finding Unique Index Keys - Oracle

Will give you a list of indexes used by the table:
select * from all_indexes where table_name = 'T931_ACCOUNT_SWITCHING';

Will return fields (or keys) used by the index:
select * from all_ind_columns where table_name = 'T931_ACCOUNT_SWITCHING'
and index_name = 'T931_ACCOUNT_SWITCHINGP1'
order by 1,2,3,4,6

VBA example - Handling Import and Export Files

Because sometimes you have to use VBA. This example was for a 3rd party's interface.




Option Compare Database

Public Sub ifcHR185GL_Actuals()

DoCmd.SetWarnings False
stAurion = CheckEnviron() ' Get & set Env

locIn = "C:\Temp\"
locOut = "C:\Temp\"

stFilePathIn = locIn & "gl_actuals.txt"
stFilePathOutHdr = locOut & "JACT" & Format(Date, "YYMMDD") & "_H.txt"
stFilePathOutLn = locOut & "JACT" & Format(Date, "YYMMDD") & "_L.txt"
stFilePathOut = locOut & "JACT" & Format(Date, "YYMMDD") & ".txt"

' stFilePathIn = "U:\INTERFACE\CURRENT\IN\gl_interface_"
' stFilePathOut = "U:\INTERFACE\CURRENT\OUT\JACT" & Format(Date, "YYMMDD") & ".txt"

'Delete the existing records
DoCmd.OpenQuery "qryHR185Actuals_Aurion_Del", acNormal, acEdit
DoCmd.OpenQuery "qryHR185Journal_Line_Actuals_Del", , acEdit

'Import the Aurion text file and create a new table
DoCmd.TransferText acImportDelim, "HR185Gl_actuals Import Specification", "tblHR185Actuals_Aurion", _
stFilePathIn, False, ""

'Update header record with system date and PPEDN date to be used for journal lines
'may be commented out when the file has a PPEND
'but it requires import spec changes and qry_App changes
DoCmd.OpenQuery "qryHR185HeaderRecord_Upd", acNormal, acEdit

'Create output table
''DoCmd.OpenQuery "qryHR185Journal_Header_Actuals_App", acNormal, acEdit
DoCmd.OpenQuery "qryHR185Journal_Line_Actuals_App", acNormal, acEdit
DoCmd.OpenQuery "qryHR185Journal_Line_Actuals_UpdPPEND", acNormal, acEdit

' Delete old copy of output files if found
stScratch = Dir(stFilePathOutHdr)
If stScratch <> "" Then
Kill stFilePathOutHdr
End If
stScratch = Dir(stFilePathOutLn)
If stScratch <> "" Then
Kill stFilePathOutLn
End If
stScratch = Dir(stFilePathOut)
If stScratch <> "" Then
Kill stFilePathOut
End If

'Export the Header and Line tables
DoCmd.TransferText acExportFixed, "tblHR185Journal_Header_Actuals Export Specification", _
"tblHR185Journal_Header_Actuals", stFilePathOutHdr
DoCmd.TransferText acExportFixed, "tblHR185Journal_Line_Actuals Export Specification", _
"qryHR185Journal_Line_Actuals_Export", stFilePathOutLn

' Merge Header and Line files into final export file.
Set fso = CreateObject("Scripting.FileSystemObject")
Set fStrmH = fso.OpenTextFile(stFilePathOutHdr)
Set fStrmL = fso.OpenTextFile(stFilePathOutLn)
Set fStrmM = fso.CreateTextFile(stFilePathOut, True)
Do Until fStrmH.AtEndOfStream
strLine = fStrmH.ReadLine
fStrmM.writeline strLine
Loop
Do Until fStrmL.AtEndOfStream
strLine = fStrmL.ReadLine
fStrmM.writeline strLine
Loop

fStrmH.Close
fStrmL.Close
fStrmM.Close

' Cleanup Header and Line files.
Kill stFilePathOutHdr
Kill stFilePathOutLn
DoCmd.SetWarnings True

'!!! COMMENT OUT THE LINE BELOW LATER
MsgBox "completed"

End Sub

Simple Example of using an Application Package

Because I'm always forgetting the syntax:

import APP_PACKAGE:AppClass;

Local APP_PACKAGE:AppClass &objAppClass = create APP_PACKAGE:AppClass();

&x = &objAppClass.Method();

For Loop Gotcha

From a recent email from a collegue. It seems that For loops only evaluate the conditions once, when the loop is entered:

Hi all,

I have just encountered an ‘interesting’ property of for loops.

It appears that they hard-code the number that they loop through until, and there doesn’t seem to be any way to affect this number.

I.e. If you have a rowset and are looping through it via a for loop - &I = 1 to &rowset.activerowcount, and you add a row to the rowset, the for loop will only go through the original number of rows – the added row will not be part of the &rowset.activerowcount of the loop. (If there were originally 10 rows in the rowset, and I add another 5 rows to the rowset as part of the for loop, &I will only ever get to 10 – not 15).


If you wish to loop through a rowset (or any variable number loops) you should use a while or a repeat loop, as the conditions are re-evaluated each time the loop comes to the conditional part (the top of the while statement, and the until of the repeat statement).


Hope that helps people.

Easy Compare Differences in Tables

SELECT * FROM TABLE_A
MINUS
SELECT * FROM TABLE_B

This can be expanded out to include the actual rows that are different between the two tables, or missing:




SELECT * FROM (

SELECT
'T906_TIME_CODE' AS TABLE_NAME
, '@AURPREP' AS DATABASE_NAME
, DECODE( -- Check if rows are different or missing. Missing if no row found with matching keys.
(SELECT 1 FROM T906_TIME_CODE B
WHERE NVL(B.T906F370_TRAN_CODE_TERM, 'X') = NVL(A.T906F370_TRAN_CODE_TERM, 'X')
AND NVL(B.T906F005_AWARD_CODE, 'X') = NVL(A.T906F005_AWARD_CODE, 'X')
AND NVL(B.T906F010_TIME_CODE, 'X') = NVL(A.T906F010_TIME_CODE, 'X')
AND NVL(B.T906F075_COSTING_ACCOUNT, 'X') = NVL(A.T906F075_COSTING_ACCOUNT, 'X')
AND NVL(B.T906F310_ACCOUNT, 'X') = NVL(A.T906F310_ACCOUNT, 'X')
AND NVL(B.T906F074_PC, 'X') = NVL(A.T906F074_PC, 'X')
AND NVL(B.T906F020_LEAVE_TYPE_AWARD, 'X') = NVL(A.T906F020_LEAVE_TYPE_AWARD, 'X')
AND NVL(B.T906F025_LEAVE_TYPE_CODE, 'X') = NVL(A.T906F025_LEAVE_TYPE_CODE, 'X')
AND NVL(B.T906F060_SECURITY_TASK, 'X') = NVL(A.T906F060_SECURITY_TASK, 'X')
) , 1, 'DIFFERENCES'
, 'MISSING IN CURRENT') AS STATUS
, A.*
FROM
(SELECT * FROM T906_TIME_CODE@AURPREP
MINUS
SELECT * FROM T906_TIME_CODE) A

UNION

SELECT
'T906_TIME_CODE' AS TABLE_NAME
, 'CURRENT' AS DATABASE_NAME
, DECODE( -- Check if rows are different or missing. Missing if no row found with matching keys.
(SELECT 1 FROM T906_TIME_CODE@AURPREP B
WHERE NVL(B.T906F370_TRAN_CODE_TERM, 'X') = NVL(A.T906F370_TRAN_CODE_TERM, 'X')
AND NVL(B.T906F005_AWARD_CODE, 'X') = NVL(A.T906F005_AWARD_CODE, 'X')
AND NVL(B.T906F010_TIME_CODE, 'X') = NVL(A.T906F010_TIME_CODE, 'X')
AND NVL(B.T906F075_COSTING_ACCOUNT, 'X') = NVL(A.T906F075_COSTING_ACCOUNT, 'X')
AND NVL(B.T906F310_ACCOUNT, 'X') = NVL(A.T906F310_ACCOUNT, 'X')
AND NVL(B.T906F074_PC, 'X') = NVL(A.T906F074_PC, 'X')
AND NVL(B.T906F020_LEAVE_TYPE_AWARD, 'X') = NVL(A.T906F020_LEAVE_TYPE_AWARD, 'X')
AND NVL(B.T906F025_LEAVE_TYPE_CODE, 'X') = NVL(A.T906F025_LEAVE_TYPE_CODE, 'X')
AND NVL(B.T906F060_SECURITY_TASK, 'X') = NVL(A.T906F060_SECURITY_TASK, 'X')
) , 1, 'DIFFERENCES'
, 'MISSING IN @AURPREP') AS STATUS
, A.*
FROM
(SELECT * FROM T906_TIME_CODE
MINUS
SELECT * FROM T906_TIME_CODE@AURPREP) A

)
ORDER BY -- (keys)
T906F370_TRAN_CODE_TERM
, T906F005_AWARD_CODE
, T906F010_TIME_CODE
, T906F075_COSTING_ACCOUNT
, T906F310_ACCOUNT
, T906F074_PC
, T906F020_LEAVE_TYPE_AWARD
, T906F025_LEAVE_TYPE_CODE
, T906F060_SECURITY_TASK
, 2
;

PL/SQL Array example




declare

type table_list is table of varchar(128) index by binary_integer;
tabs table_list;
i number;

begin

tabs(1) := 'aaa';
tabs(2) := 'bbb';
tabs(3) := 'ccc';

for i in 1..3
loop
dbms_output.put_line(tabs(i));
end loop;

end;

Simple Select and Loop in PL/SQL

Simple example of using PL/SQL to select and loop through a cursor.
Note: Results are output to the output window.





CREATE OR REPLACE PROCEDURE test(p_table_name in user_tables.table_name%type) IS

TYPE ref_cursor IS REF CURSOR;
l_cursor ref_cursor;

v_query varchar2(5000);
v_name varchar2(64);

BEGIN
v_query := 'select T120F005_COST_CATEGORY_CODE from ' || p_table_name;
OPEN l_cursor FOR v_query;
LOOP
FETCH l_cursor INTO v_name;
EXIT WHEN l_cursor%NOTFOUND;
dbms_output.put_line('Category Code: ' || v_name);
END LOOP;
CLOSE l_cursor;
END;
/

-- Test.
EXEC test('T120_COSTING_CATEGORY');

Access to HE90PS PS

(little note to self)

GU_DEVELOPER_BSS_D0

Viewing Keys (indexes) at Oracle Level

select * from system.dba_ind_columns where table_name='ps_table_name';

Application Engine Tracing

You can turn on tracing for a distinct Application Engine process by appending AE parameters. Append parameter "-TRACE 3" to enable the tracing.

Oracle 10

From email:

Oracle (since version 8 ? ) has been recommending to all that we move toward the Cost Based Optimizer (CBO). Prior to this Oracle used (and still can use) the RULE based Optimizer, which pretty much just uses predicate logic and knowledge of indexes to do its job. (Also, the order of tables in the FROM clause is important, and the order of the WHERE clauses)

The CBO attempts to come up with the cheapest row access plan based on the actual cost (CPU and IO) of the plan. To do this, Oracle needs accurate statistics on the tables and indexes involved. These statistics are gathered using the Oracle supplied PLSQL procedures within the DBMS_STATS package. These procedures gather a bunch of different information about the tables and indexes, and can also gather histogram information, which can be invaluable on (mainly indexed) columns where there is highly skewed data. Stats gathering is becoming more automagic as time goes on. Everytime Oracle release a new version, there are new and improved auto options for stats gathering. there is also the possibility (we are not doing this presently) of gathering "system stats" as well. These are separate to the data object stats, and must be gathered by monitoring a live system. These stats attempt to place real time values on IO based on system load. If these are available, the CBO correlates its IO estimates with these real time estimates.

The CBO is not perfect though, and occasionally it will choose a less than optimal access path. This happens mainly because stats usually contain imprecise estimates made during stats gathering. It is possible to collect complete and accurate stats, but this is extremely expensive. To give you an example, it takes over 30 hours to gather stats in HE90U5 (using the auto options, which will estimate some things).

Portal Navigation Structure

select t.portal_name, t.portal_reftype, LPAD (' ', 10 * (LEVEL - 1)) || t.portal_objname as portal_objname, LPAD (' ', 10 * (LEVEL - 1)) || t.portal_label as portal_label, t.portal_ispublic, t.portal_seq_num, t.lastupdoprid, level lev, t.portal_urltext
from sysadm.psprsmdefn t
where t.portal_name = 'EMPLOYEE'
start with t.portal_prntobjname = 'PORTAL_ROOT_OBJECT'
CONNECT BY PRIOR t.portal_objname = t.portal_prntobjname and PRIOR t.portal_name = t.portal_name and PRIOR t.portal_reftype = 'F';

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

Using Outer Joins AND Effective Dated Logic

How can you use outer joins on a table and still use effective dated logic? Something like this.

Note that the outer join will either return no rows, and thus a NULL value for EFFDT, or it will return a date and subsequent effective dated logic can be used.

AND C.BUSINESS_UNIT (+) = A.BUSINESS_UNIT
AND C.COMMON_ID (+) = A.COMMON_ID
AND C.SA_ID_TYPE (+) = A.SA_ID_TYPE
AND C.ITEM_NBR_CHARGE (+) = A.ITEM_NBR
AND
(NULL = C.EFFDT
OR
(C.EFFDT =
(SELECT MAX(C_ED.EFFDT)
FROM sysadm.ps_ITEM_XREF C_ED
WHERE C_ED.BUSINESS_UNIT = C.BUSINESS_UNIT
AND C_ED.COMMON_ID = C.COMMON_ID
AND C_ED.SA_ID_TYPE = C.SA_ID_TYPE
AND C_ED.ITEM_NBR_CHARGE = C.ITEM_NBR_CHARGE
AND C_ED.ITEM_NBR_PAYMENT = C.ITEM_NBR_PAYMENT
AND C_ED.EFFDT <= SYSDATE)))

XML Publisher Example

Example of using PeopleCode to generate XML and then merge with a predefined RTF template.
/* GUSAFN02 - HEUP Project, Michael Nitschke 27.11.2007 */
import PSXP_RPTDEFNMANAGER:*;
import GU_EN_ENROLMENTS:*;

Declare Function DeleteLocalFile PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;
Declare Function GetDirSeparator PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;

/* Function to populate a node based on given SQL.
&node: Create the node in the location you want and pass through to be populated.
&recName: Record that node data will be based on. Must also match data structure returned from &sql.
&sql: SQL that will populate &rec and the node. */
Function AddNodeFromSQL(&node As XmlNode, &recName As
string, &sqlName As string)
Local Record &rec = CreateRecord(@(
"Record." | &recName));
Local SQL &sql = GetSQL(@(
"SQL." | &sqlName), &emplid);
While &sql.Fetch(&rec)
Local XmlNode &nodeRow = &node.AddElement(&node.NodeName |
"Row");
For &f = 1 To &rec.FieldCount
Local XmlNode &nodeFld = &nodeRow.AddElement(&node.NodeName |
"." | &rec.GetField(&f).Name);
&nodeFld.NodeValue = &rec.GetField(&f).Value;
End-For;
End-While;
End-Function;

/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */

&emplid = %EmployeeId;
Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;
Local XmlDoc &xmlDoc;
&xmlDoc = CreateXmlDoc(
"<?xml version='1.0'?><ACADEMIC_CHARGES/>");

/* Create header, config, programs, charges, unapplied charges and discounts xml elements, including data. */
Local XmlNode &nodeHeader = &xmlDoc.DocumentElement.AddElement(
"Header");
AddNodeFromSQL(&nodeHeader,
"GU_STMT_HDR_DW", "GU_STMT_HDR");
Local XmlNode &nodeConfig = &xmlDoc.DocumentElement.AddElement(
"Config");
AddNodeFromSQL(&nodeConfig,
"GU_STMT_CFG_DTL", "GU_STMT_CFG_DTL_AC");
Local XmlNode &nodeProgram = &xmlDoc.DocumentElement.AddElement(
"Program");
AddNodeFromSQL(&nodeProgram,
"GU_STMT_PROG_DW", "GU_STMT_PROG");
Local XmlNode &nodeCharges = &xmlDoc.DocumentElement.AddElement(
"Charges");
AddNodeFromSQL(&nodeCharges,
"GU_STMT_CHRG_DW", "GU_STMT_CHARGES");
Local XmlNode &nodeChargesGST = &xmlDoc.DocumentElement.AddElement(
"ChargesGST");
AddNodeFromSQL(&nodeChargesGST,
"GU_STMT_CGST_DW", "GU_STMT_CHARGES_GST");
Local XmlNode &nodeUnappliedCharges = &xmlDoc.DocumentElement.AddElement(
"UnappliedPayments");
AddNodeFromSQL(&nodeUnappliedCharges,
"GU_STMT_CHRG_DW", "GU_STMT_UNAPPLIED_PAYMENTS");
Local XmlNode &nodeDiscounts = &xmlDoc.DocumentElement.AddElement(
"Discounts");
AddNodeFromSQL(&nodeDiscounts,
"GU_STMT_CHRG_DW", "GU_STMT_DISCOUNTS");
Local XmlNode &nodeMessages = &xmlDoc.DocumentElement.AddElement(
"Messages");
AddNodeFromSQL(&nodeMessages,
"GU_STMT_MSG_DW", "GU_STMT_MESSAGES");

/* Create report definition and connect to the generated XML document. */
&rptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(
"GU_ACAD_CHRG");
&rptDefn.Get();
&rptDefn.SetRuntimeDataXMLDoc(&xmlDoc);

/* Create output directories. */
&sDirSep = GetDirSeparator();
&RptOutputDir = GetEnv(
"PS_SERVDIR") | &sDirSep | "files" | &sDirSep | "XMLP" | &sDirSep | UuidGen();
&OutputDir = &RptOutputDir | &sDirSep |
"RptInst";
&DataDir = &RptOutputDir | &sDirSep |
"Data";
CreateDirectory(&OutputDir, %FilePath_Absolute);
CreateDirectory(&DataDir, %FilePath_Absolute);

/* Process. */
&rptDefn.OutDestination = &RptOutputDir;
&rptDefn.ProcessReport(
"", %Language_User, %Date, "");
CommitWork();

/* Display. */
&rptDefn.DisplayOutput();

/* Cleanup. */
DeleteLocalFile(&sOutputFile, %FilePath_Absolute);

Array example

Nice little example of using arrays in PeopleCode. Probably a more elegant, 2 dimension solution but this works just fine.

This example was required to handle overriding GL distribution by Elements, which are on level 3. and element can appear on level 3 under many level 2 rows. Code loops through all possible rows on level 3 and sums total for each unique Element found. Must add to 100.

/* Check that sum of each given element, across all pin rows, adds to 100%. */

/* 2 arrays to hold all elements and sum of their percentages. */
&arrElements = CreateArray();
&arrPercent = CreateArray();

/* Populate the arrays. */
Local Rowset &rsGP_GL_DIST_DTL = GetRowset(Scroll.GP_GL_DIST_DTL);
For &i = 1 To &rsGP_GL_DIST_DTL.ActiveRowCount
Local Rowset &rsGP_GL_DIST_PIN = &rsGP_GL_DIST_DTL(&i).GetRowset(Scroll.GP_GL_DIST_PIN);

/* Check there are actually any element rows (if one does they all do, vice-versa). Populate arrays if found.*/
If &rsGP_GL_DIST_PIN(1).GP_GL_DIST_PIN.PIN_NUM.Value = 0 Then
&hasElements = False;
Break;
Else
&hasElements = True;
For &j = 1 To &rsGP_GL_DIST_PIN.ActiveRowCount
&element = &rsGP_GL_DIST_PIN(&j).GP_PIN_DESCR_VW.DESCR.Value;
&percent = &rsGP_GL_DIST_PIN(&j).GP_GL_DIST_PIN.PCT_TO_ALLOCATE.Value;
&found = &arrElements.Find(&element);
If &found = 0 Then
&arrElements.Push(&element);
&arrPercent.Push(&percent);
Else
&arrPercent [&found] = &arrPercent [&found] + &percent;
End-If;
End-For;
End-If;

End-For;

/* Check for any elements that are <> 100% and advise with warning msg & highlighting the field. */
If &hasElements Then
For &k = 1 To &arrPercent.Len

&err = False;
If &arrPercent [&k] <> 100 Then
&err = True;
End-If;

For &i = 1 To &rsGP_GL_DIST_DTL.ActiveRowCount
&rsGP_GL_DIST_PIN = &rsGP_GL_DIST_DTL(&i).GetRowset(Scroll.GP_GL_DIST_PIN);
For &j = 1 To &rsGP_GL_DIST_PIN.ActiveRowCount
If &rsGP_GL_DIST_PIN(&j).GP_PIN_DESCR_VW.DESCR.Value = &arrElements [&k] Then
If &err Then
&rsGP_GL_DIST_PIN(&j).GP_GL_DIST_PIN.PCT_TO_ALLOCATE.SetCursorPos(%Page);
&rsGP_GL_DIST_PIN(&j).GP_GL_DIST_PIN.PCT_TO_ALLOCATE.Style =
"PSERROR";
Error
"Sum percentages of Element '" | &arrElements [&k] | "' do not total 100%.";
Else
&rsGP_GL_DIST_PIN(&j).GP_GL_DIST_PIN.PCT_TO_ALLOCATE.Style =
"PSEDITBOX";
End-If;
End-If;
End-For;
End-For;

End-For;
End-If;