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 */

2 comments:

chucher said...

Hi
I have a problem with an excelToCI And i think is related to this topic. I have to insert one header and 2 lines but each of the 2 lines have a at least one child.

This send me this error with the child of the child

#1 - El campo resaltado es obligatorio. Introduzca un valor en él antes de seguir.{CI_ASSET_ENTRY.AM_DOC_SEQ(1).BOOK} (15,54)
#2 - El campo resaltado es obligatorio. Introduzca un valor en él antes de seguir.{CI_ASSET_ENTRY.AM_DOC_SEQ(2).BOOK} (15,54)

I think i have to save or activate some event or something to make this work

Is any way to make the ExcelToCI work?

Is it better avoid the validation?

Can you help me?

thanks

Paz said...

Hi Michael! I read a post in your blog and i'd like to ask some questions about PeopleSoft if it's possible. I'm from Brazil, nice to meet you! Can you send me your email? Thanks