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..