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.