This blog is created for holding my quick reference notes. At the same time these could be helpful for visitors if they want to get the Component Interface working via Application Engine – Peoplecode real fast without going through too much detailed documentation. Once you have a working example file in your development environment, you can go back to peoplebooks and other documentation for understanding additional related topics and experiment with your creative ideas. I believe, after getting the example working first, the related peoplebooks were understood faster too.
The example has been created and tested on the following software environment:
Peoplesoft Application: FSCM 9.2
Database: Oracle
The major steps will be:
Create a new component working
Create new records, pages. component to be used in this example
Load sample data
Validate that the component is accessible on line
Insert the some data and validate saving
Create a Component Interface based on the Component and test
Create new Application Engine program that will
Read the run control table to get the input file name and saves in the state record
Read rows from the input data file and save in temporary table
Process all records using Component Interface
Assumptions:
1) The users have the knowledge of peopletools particularly creating records, pages, components, Peoplecode, Registering component, SQL, etc..
2) The users have working knowledge of Application Engine, creating process definition and attaching to a component tor executing from Web.
I will be logged in as VP1 so I do not have to spend too much time in security. Security in not the focus here.
Let us create new records, pages. component to be used in this example. We will also load sample data in appropriate tables.
Insert the following data in table PS_IBU_GROUP_INV using following SQLs.
insert into PS_IBU_GROUP_INV values (‘NV89123′,’Las Vegas, NV Unit 89123′,’Las Vegas’);
insert into PS_IBU_GROUP_INV values (‘CA90701′,’Artesia, CA Unit 90701′,’Artesia’);
We will start with two Business Unit groups. Additional Business Unit Groups will have to be maintained separately.
Create a new record PS_AJ_TABLE_TYPES and build the record.
Insert the following data in table PS_AJ_TABLE_TYPES using following SQLs.
insert into ps_aj_table_types values (‘NV89123′,’PAYMENT_TERMS’,’Payment Terms Translation’);
insert into ps_aj_table_types values (‘NV89123′,’CURRENCY’,’Currency Code Translation’);
insert into ps_aj_table_types values (‘CA90701′,’PAYMENT_TERMS’,’Payment Terms Translation’);
insert into ps_aj_table_types values (‘CA90701′,’ACCOUNT’,’Account Translation’);
insert into ps_aj_table_types values (‘CA90701′,’UOM’,’Unit of Measure Translation’);
insert into ps_aj_table_types values (‘CA90701′,’CURRENCY’,’Currency Code Translation’);
Create a new record PS_AJ_MAP_XLAT_TBL and build the record.
Create two pages like below.
and
Create a component as shown below.
Register the component, logout and log back in to see if the page is accessible online. Navigate to the location where you have added the new component.
Select Advanced Search, Select CA90701 Group and click on Search again. Select PAYMENT_TERMS.
Select the next tab Map Xlated Items.
Insert the following data using following SQLs.
INSERT INTO PS_AJ_MAP_XLAT_TBL VALUES (‘CA90701′,’PAYMENT_TERMS’,’CIA’,’ ‘,’ ‘,’SRC CIA’,’ADV’,’PSFT ADV’,SYSDATE,’AGGARWAA’);
INSERT INTO PS_AJ_MAP_XLAT_TBL VALUES (‘CA90701′,’PAYMENT_TERMS’,’COD’,’ ‘,’ ‘,’SRC COD’,’COD’,’PSFT COD’,SYSDATE,’AGGARWAA’);
INSERT INTO PS_AJ_MAP_XLAT_TBL VALUES (‘CA90701′,’PAYMENT_TERMS’,’DA’,’ ‘,’ ‘,’SRC DA’,’30day DA30′,’PSFT 30day DA30′,SYSDATE,’AGGARWAA’);
INSERT INTO PS_AJ_MAP_XLAT_TBL VALUES (‘CA90701′,’PAYMENT_TERMS’,’EOM45′,’ ‘,’ ‘,’SRC EOM45′,’M45′,’PSFT M45′,SYSDATE,’AGGARWAA’);
INSERT INTO PS_AJ_MAP_XLAT_TBL VALUES (‘CA90701′,’PAYMENT_TERMS’,’EOM75′,’ ‘,’ ‘,’SRC EOM75′,’M75′,’PSFT M75′,SYSDATE,’AGGARWAA’);
INSERT INTO PS_AJ_MAP_XLAT_TBL VALUES (‘CA90701′,’PAYMENT_TERMS’,’LC90′,’ ‘,’ ‘,’SRC LC90′,’LC90′,’PSFT LC90′,SYSDATE,’AGGARWAA’);
INSERT INTO PS_AJ_MAP_XLAT_TBL VALUES (‘CA90701′,’PAYMENT_TERMS’,’M45′,’ ‘,’ ‘,’SRC M45′,’M45′,’PSFT M45′,SYSDATE,’AGGARWAA’);
INSERT INTO PS_AJ_MAP_XLAT_TBL VALUES (‘CA90701′,’PAYMENT_TERMS’,’M75′,’ ‘,’ ‘,’SRC M75′,’M75′,’PSFT M75′,SYSDATE,’AGGARWAA’);
INSERT INTO PS_AJ_MAP_XLAT_TBL VALUES (‘CA90701′,’PAYMENT_TERMS’,’N00′,’ ‘,’ ‘,’SRC N00′,’N00′,’PSFT N00′,SYSDATE,’AGGARWAA’);
INSERT INTO PS_AJ_MAP_XLAT_TBL VALUES (‘CA90701′,’PAYMENT_TERMS’,’N10′,’ ‘,’ ‘,’SRC N10′,’N30′,’PSFT N30′,SYSDATE,’AGGARWAA’);
Refresh the page and come back to the tab Map Xlated Items.
Let us add one row (11th row) as shown below and save.
select * from PS_AJ_MAP_XLAT_TBL WHERE IBU_GROUP = ‘CA90701’ AND TABLE_TYPE = ‘PAYMENT TERMS’;
We have 11 rows now. The addition of 11th row for N1M serves two purposes.
First, it shows the component and all its parts are working fine.
Second, this very row exists in our sample file so it will cause an abend. We will not delete this row now and let AE abend. We will then fix the file and rerun the AE to complete success.
Now that we have a working component, we will create a Component Interface based on this Component. To create a new Component Interface based on Component AJ_MAP_XLAT_TBL, from the File Menu, select New, select Component Interface from the New Definition list, and select component AJ_MAP_XLAT_TBL. Click Yes on the following dialog box.
Save the new Component Interface as AJ_MAP_XLAT_TBL_CI.
The security administration needs to be performed for the newly created Component Interface.
Let us test the CI. Open the CI in Application designer. From the Tools menu, select “Test Component Interface” menu item. The following dialog box appears.
To the left of “Get Existing” button, type IBU_GROUP as CA90701 and TABLE_TYPE as PAYMENT_TERMS and click on “Get Existing” button. You should see the data as shown below.
We need to create a new Application Engine program that will read the data from a CSV file and load the data to the Translate mapping table PS_AJ_MAP_XLAT_TBL using the Component Interface AJ_MAP_XLAT_TBL_CI. Before creating AE, let us create the objects to be used in AE.
Create a state record AJ_MAP_XLAT_AET as shown below.
Create temporary table record AJ_MAP_XLAT_T as shown below. Note the duplicate index key.
Create a file layout XLAT_LD that matches the layout of the input data file XLAT_DATA.CSV saved on application server. It will be used to read the data from the sample input file.
We will create an Application Engine which will read data from a CSV file, stores in a temporary table and uses CI driven by peoplecode in AE to process one record at a time from that temporary table. The structure of the AE AJ_XLAT_LD looks like the following.
The properties of AE are illustrated below.
The five actions in the AE are
1) Section=MAIN, Step=Step00, Action=SQL
This step reads the run control table to get the input file name and saves in the state record. The following SQL is used.
%Select(OPRID , RUN_CNTL_ID , FILE_NAME)
SELECT OPRID , RUN_CNTL_ID , FILE_NAME
FROM PS_AJ_RC_CI
WHERE OPRID = %OperatorId AND RUN_CNTL_ID = %RUNCONTROL
2) Section=MAIN, Step=Step02, Action=Peoplecode
This step
a) Gets the input file name from state record.
b) Opens a log file for saving messages.
c) Reads rows from the input data file.
d) Saves the input data in temporary table PS_AJ_MAP_XLAT_T.
The complete peoplecode is pasted below.
Global File &fileLog;
Local File &FILE1;
Local Record &AJ_TMP_TBL, &AJ_FileLayout_TBL;
Local SQL &SQL1;
Local Rowset &RS1, &RS2;
&FILE_DIRECTORY = “/home/psadm2/psft/pt/8.53/appserv/prcs/PRCSDOM/files/”;
&FileName = AJ_MAP_XLAT_AET.FILE_NAME;
&FILE1 = GetFile(&FILE_DIRECTORY | &FileName, “r”, “a”, %FilePath_Absolute);
&FILE1.SetFileLayout(FileLayout.XLAT_LD);
&fileLog = GetFile(&FILE_DIRECTORY | “/AJ_MAP_XLAT_TBL_CI.log”, “w”, “a”, %FilePath_Absolute);
&fileLog.WriteLine(“&FileName = ” | &FileName);
&RS1 = &FILE1.CreateRowset();
&RS2 = CreateRowset(Record.AJ_MAP_XLAT_T);
&SQL1 = CreateSQL(“%Insert(:1)”);
&RS1 = &FILE1.ReadRowset();
While &RS1 <> Null;
&AJ_TMP_TBL = &RS2(1).GetRecord(Record.AJ_MAP_XLAT_T);
&AJ_FileLayout_TBL = &RS1(1).GetRecord(Record.AJ_MAP_XLAT_TBL);
&AJ_TMP_TBL.GetField(Field.PROCESS_INSTANCE).Value = AJ_MAP_XLAT_AET.PROCESS_INSTANCE;
&AJ_TMP_TBL.GetField(Field.IBU_GROUP).Value = &AJ_FileLayout_TBL.GetField(Field.IBU_GROUP).Value;
&AJ_TMP_TBL.GetField(Field.TABLE_TYPE).Value = &AJ_FileLayout_TBL.GetField(Field.TABLE_TYPE).Value;
&AJ_TMP_TBL.GetField(Field.MAP_FROM).Value = &AJ_FileLayout_TBL.GetField(Field.MAP_FROM).Value;
&AJ_TMP_TBL.GetField(Field.SUBDETAIL1).Value = &AJ_FileLayout_TBL.GetField(Field.SUBDETAIL1).Value;
&AJ_TMP_TBL.GetField(Field.SUBDETAIL2).Value = &AJ_FileLayout_TBL.GetField(Field.SUBDETAIL2).Value;
&AJ_TMP_TBL.GetField(Field.DESCR_FROM).Value = &AJ_FileLayout_TBL.GetField(Field.DESCR_FROM).Value;
&AJ_TMP_TBL.GetField(Field.TO_VALUE).Value = &AJ_FileLayout_TBL.GetField(Field.TO_VALUE).Value;
&AJ_TMP_TBL.GetField(Field.DESCR_TO).Value = &AJ_FileLayout_TBL.GetField(Field.DESCR_TO).Value;
&SQL1.Execute(&AJ_TMP_TBL);
&RS1 = &FILE1.ReadRowset();
End-While;
&FILE1.Close();
3) Section=MAIN, Step=Step03, Action=Call Section
This step calls the section PROC_STG which will process all records loaded in the temporary table PS_AJ_MAP_XLAT_T.
4) Section=PROC_STG, Step=Step01, Action=Do Select
This step will read all records from the temporary table PS_AJ_MAP_XLAT_T for the current process_instance number. For each row retrieved, the saved in the state record and the next peoplecode is executed.
5) Section=PROC_STG, Step=Step01, Action=Peoplecode
This step will is the core part of this blog entree. It processes each record read from the temporary table PS_AJ_MAP_XLAT_T in the previous DO-SELECT action, one at a time. For each iteration. The record is grabbed from the state record and the peoplecode for processing the data through CI is executed.
Open the peoplecode text window related to Section=PROC_STG, Step=Step01, Action=Peoplecode.
Drag the CI to peoplecode text window and review the template generated.
The template peoplecode generated by peoplesoft is listed below. The code highlighted in red will likely need to change.
The template peoplecode generated by peoplesoft is listed below. The code highlighted in red will likely need to change.
/* ===>
This is a dynamically generated PeopleCode template to be used only as a helper
to the application developer.
You need to replace all references to ‘[*]’ OR default values with references to
PeopleCode variables and/or a Rec.Fields. */
Local File &fileLog;
Local ApiObject &oSession, &oAjMapXlatTblCi;
Local ApiObject &oAjMapXlatTblCollection, &oAjMapXlatTbl;
Function errorHandler()
Local ApiObject &oPSMessageCollection, &oPSMessage;
Local number &i;
Local String &sErrMsgSetNum, &sErrMsgNum, &sErrMsgText, &sErrType;
&oPSMessageCollection = &oSession.PSMessages;
For &i = 1 To &oPSMessageCollection.Count
&oPSMessage = &oPSMessageCollection.Item(&i);
&sErrMsgSetNum = &oPSMessage.MessageSetNumber;
&sErrMsgNum = &oPSMessage.MessageNumber;
&sErrMsgText = &oPSMessage.Text;
&fileLog.WriteLine(&sErrType | ” (” | &sErrMsgSetNum | “,” | &sErrMsgNum | “) – ” | &sErrMsgText);
End-For;
rem ***** Delete the Messages from the collection *****;
&oPSMessageCollection.DeleteAll();
End-Function;
try
rem ***** Set the Log File *****;
&fileLog = GetFile(“C:\Users\it\AppData\Local\Temp\AJ_MAP_XLAT_TBL_CI.log”, “w”, “a”, %FilePath_Absolute);
&fileLog.WriteLine(“Begin”);
rem ***** Get current PeopleSoft Session *****;
&oSession = %Session;
rem ***** Set the PeopleSoft Session Error Message Mode *****;
rem ***** 0 – None *****;
rem ***** 1 – PSMessage Collection only (default) *****;
rem ***** 2 – Message Box only *****;
rem ***** 3 – Both collection and message box *****;
&oSession.PSMessagesMode = 1;
rem ***** Get the Component Interface *****;
&oAjMapXlatTblCi = &oSession.GetCompIntfc(CompIntfc.AJ_MAP_XLAT_TBL_CI);
If &oAjMapXlatTblCi = Null Then
errorHandler();
throw CreateException(0, 0, “GetCompIntfc failed”);
End-If;
rem ***** Set the Component Interface Mode *****;
&oAjMapXlatTblCi.InteractiveMode = False;
&oAjMapXlatTblCi.GetHistoryItems = True;
&oAjMapXlatTblCi.EditHistoryItems = False;
rem ***** Set Component Interface Get/Create Keys *****;
&oAjMapXlatTblCi.IBU_GROUP = [*];
&oAjMapXlatTblCi.TABLE_TYPE = [*]; rem ***** Execute Get *****;
If Not &oAjMapXlatTblCi.Get() Then
rem ***** No rows exist for the specified keys.*****;
errorHandler();
throw CreateException(0, 0, “Get failed”);
End-If;
rem ***** Execute Create ******;
rem If Not &oAjMapXlatTblCi.Create() Then;
rem ***** Unable to Create Component Interface for the Add keys provided. *****;
rem errorHandler();
rem throw CreateException(0, 0, “Create failed”);
rem End-If;
rem ***** Begin: Get/Set Component Interface Properties *****;
rem ***** Get/Set Level 0 Field Properties *****;
&fileLog.WriteLine(“&oAjMapXlatTblCi.TABLE_TYPE = ” | &oAjMapXlatTblCi.TABLE_TYPE);
rem &oAjMapXlatTblCi.TABLE_TYPE = [*];
&fileLog.WriteLine(“&oAjMapXlatTblCi.TABLE_NAME = ” | &oAjMapXlatTblCi.TABLE_NAME);
rem &oAjMapXlatTblCi.TABLE_NAME = [*];
rem ***** Set/Get AJ_MAP_XLAT_TBL Collection Field Properties — Parent: PS_ROOT Collection *****;
&oAjMapXlatTblCollection = &oAjMapXlatTblCi.AJ_MAP_XLAT_TBL;
Local integer &i19;
For &i19 = 1 To &oAjMapXlatTblCollection.Count;
&oAjMapXlatTbl = &oAjMapXlatTblCollection.Item(&i19);
&fileLog.WriteLine(“&oAjMapXlatTbl.MAP_FROM = ” | &oAjMapXlatTbl.MAP_FROM);
rem &oAjMapXlatTbl.MAP_FROM = [*];
&fileLog.WriteLine(“&oAjMapXlatTbl.SUBDETAIL1 = ” | &oAjMapXlatTbl.SUBDETAIL1);
rem &oAjMapXlatTbl.SUBDETAIL1 = [*];
&fileLog.WriteLine(“&oAjMapXlatTbl.SUBDETAIL2 = ” | &oAjMapXlatTbl.SUBDETAIL2);
rem &oAjMapXlatTbl.SUBDETAIL2 = [*];
&fileLog.WriteLine(“&oAjMapXlatTbl.DESCR_FROM = ” | &oAjMapXlatTbl.DESCR_FROM);
rem &oAjMapXlatTbl.DESCR_FROM = [*];
&fileLog.WriteLine(“&oAjMapXlatTbl.TO_VALUE = ” | &oAjMapXlatTbl.TO_VALUE);
rem &oAjMapXlatTbl.TO_VALUE = [*];
&fileLog.WriteLine(“&oAjMapXlatTbl.DESCR_TO = ” | &oAjMapXlatTbl.DESCR_TO);
rem &oAjMapXlatTbl.DESCR_TO = [*];
&fileLog.WriteLine(“&oAjMapXlatTbl.LASTUPDDTTM = ” | &oAjMapXlatTbl.LASTUPDDTTM);
rem &oAjMapXlatTbl.LASTUPDDTTM = [*];
&fileLog.WriteLine(“&oAjMapXlatTbl.LASTUPDOPRID = ” | &oAjMapXlatTbl.LASTUPDOPRID);
rem &oAjMapXlatTbl.LASTUPDOPRID = [*];
End-For; rem ***** End: Get/Set Component Interface Properties *****;
rem ***** Execute Save *****;
rem If Not &oAjMapXlatTblCi.Save() Then;
rem errorHandler();
rem throw CreateException(0, 0, “Save failed”);
rem End-If;
rem ***** Execute Cancel *****;
rem If Not &oAjMapXlatTblCi.Cancel() Then;
rem errorHandler();
rem throw CreateException(0, 0, “Cancel failed”);
rem End-If;
catch exception &ex
rem Handle the exception;
&fileLog.WriteLine(&ex.ToString());
end-try;
&fileLog.WriteLine(“End”);
&fileLog.Close();
After making changes to peoplecode for our CI processing, the final peoplecode is pasted below.
/* ===>
This is a dynamically generated PeopleCode template to be used only as a helper
to the application developer.
You need to replace all references to ‘[*]’ OR default values with references to
PeopleCode variables and/or a Rec.Fields. */
Global File &fileLog;
Local ApiObject &oSession, &oAjMapXlatTblCi;
Local ApiObject &oAjMapXlatTblCollection, &oAjMapXlatTbl;
Function errorHandler()
Local ApiObject &oPSMessageCollection, &oPSMessage;
Local number &i;
Local string &sErrMsgSetNum, &sErrMsgNum, &sErrMsgText, &sErrType;
&oPSMessageCollection = &oSession.PSMessages;
For &i = 1 To &oPSMessageCollection.Count
&oPSMessage = &oPSMessageCollection.Item(&i);
&sErrMsgSetNum = &oPSMessage.MessageSetNumber;
&sErrMsgNum = &oPSMessage.MessageNumber;
&sErrMsgText = &oPSMessage.Text;
&fileLog.WriteLine(&sErrType | ” (” | &sErrMsgSetNum | “,” | &sErrMsgNum | “) – ” | &sErrMsgText);
End-For;
rem ***** Delete the Messages from the collection *****;
&oPSMessageCollection.DeleteAll();
End-Function;
try
&fileLog.WriteLine(“Begin”);
rem ***** Get current PeopleSoft Session *****;
&oSession = %Session;
rem ***** Set the PeopleSoft Session Error Message Mode *****;
rem ***** 0 – None *****;
rem ***** 1 – PSMessage Collection only (default) *****;
rem ***** 2 – Message Box only *****;
rem ***** 3 – Both collection and message box *****;
&oSession.PSMessagesMode = 1;
rem ***** Get the Component Interface *****;
&oAjMapXlatTblCi = &oSession.GetCompIntfc(CompIntfc.AJ_MAP_XLAT_TBL_CI);
If &oAjMapXlatTblCi = Null Then
errorHandler();
throw CreateException(0, 0, “GetCompIntfc failed”);
End-If;
rem ***** Set the Component Interface Mode *****;
&oAjMapXlatTblCi.InteractiveMode = False;
&oAjMapXlatTblCi.GetHistoryItems = True;
&oAjMapXlatTblCi.EditHistoryItems = False;
rem ***** Set Component Interface Get/Create Keys *****;
&oAjMapXlatTblCi.IBU_GROUP = AJ_MAP_XLAT_AET.IBU_GROUP;
&oAjMapXlatTblCi.TABLE_TYPE = AJ_MAP_XLAT_AET.TABLE_TYPE;
&fileLog.WriteLine(“TEST &oAjMapXlatTblCi.IBU_GROUP = ” | &oAjMapXlatTblCi.IBU_GROUP);
&fileLog.WriteLine(“TEST &oAjMapXlatTblCi.TABLE_TYPE = ” | &oAjMapXlatTblCi.TABLE_TYPE);
rem ***** Execute Get *****;
If Not &oAjMapXlatTblCi.Get() Then
rem ***** No rows exist for the specified keys.*****;
errorHandler();
throw CreateException(0, 0, “Get failed”);
End-If;
rem ***** Execute Find *****;
rem If Not &oAjMapXlatTblCi.find() Then
rem ***** No rows exist for the specified keys.*****;
rem errorHandler();
rem throw CreateException(0, 0, “find failed”);
rem End-If;
rem ***** Execute Create ******;
rem If Not &oAjMapXlatTblCi.Create() Then;
rem ***** Unable to Create Component Interface for the Add keys provided. *****;
rem errorHandler();
rem throw CreateException(0, 0, “Create failed”);
rem End-If;
rem ***** Begin: Get/Set Component Interface Properties *****;
rem ***** Get/Set Level 0 Field Properties *****;
&oAjMapXlatTblCi.TABLE_TYPE = AJ_MAP_XLAT_AET.TABLE_TYPE;
&oAjMapXlatTblCi.TABLE_NAME = “PAYMENT_TERMS”;
&fileLog.WriteLine(“&oAjMapXlatTblCi.TABLE_TYPE = ” | &oAjMapXlatTblCi.TABLE_TYPE);
&fileLog.WriteLine(“&oAjMapXlatTblCi.TABLE_NAME = ” | &oAjMapXlatTblCi.TABLE_NAME);
rem ***** Set/Get AJ_MAP_XLAT_TBL Collection Field Properties — Parent: PS_ROOT Collection *****;
&oAjMapXlatTblCollection = &oAjMapXlatTblCi.AJ_MAP_XLAT_TBL;
&fileLog.WriteLine(“TEST1 &oAjMapXlatTblCollection.Count = ” | &oAjMapXlatTblCollection.Count);
&oAjMapXlatTbl = &oAjMapXlatTblCollection.InsertItem(1);
&oAjMapXlatTbl.MAP_FROM = AJ_MAP_XLAT_AET.MAP_FROM;
&oAjMapXlatTbl.SUBDETAIL1 = AJ_MAP_XLAT_AET.SUBDETAIL1;
&oAjMapXlatTbl.SUBDETAIL2 = AJ_MAP_XLAT_AET.SUBDETAIL2;
&oAjMapXlatTbl.DESCR_FROM = AJ_MAP_XLAT_AET.DESCR_FROM;
&oAjMapXlatTbl.TO_VALUE = AJ_MAP_XLAT_AET.TO_VALUE;
&oAjMapXlatTbl.DESCR_TO = AJ_MAP_XLAT_AET.DESCR_TO;
rem &oAjMapXlatTbl.LASTUPDDTTM = AJ_MAP_XLAT_AET.LASTUPDDTTM;
rem &oAjMapXlatTbl.LASTUPDOPRID = AJ_MAP_XLAT_AET.LASTUPDOPRID;
&fileLog.WriteLine(“&oAjMapXlatTbl.MAP_FROM = ” | &oAjMapXlatTbl.MAP_FROM);
&fileLog.WriteLine(“&oAjMapXlatTbl.SUBDETAIL1 = ” | &oAjMapXlatTbl.SUBDETAIL1);
&fileLog.WriteLine(“&oAjMapXlatTbl.SUBDETAIL2 = ” | &oAjMapXlatTbl.SUBDETAIL2);
&fileLog.WriteLine(“&oAjMapXlatTbl.DESCR_FROM = ” | &oAjMapXlatTbl.DESCR_FROM);
&fileLog.WriteLine(“&oAjMapXlatTbl.TO_VALUE = ” | &oAjMapXlatTbl.TO_VALUE);
&fileLog.WriteLine(“&oAjMapXlatTbl.DESCR_TO = ” | &oAjMapXlatTbl.DESCR_TO);
rem &fileLog.WriteLine(“&oAjMapXlatTbl.LASTUPDDTTM = ” | &oAjMapXlatTbl.LASTUPDDTTM);
rem &fileLog.WriteLine(“&oAjMapXlatTbl.LASTUPDOPRID = ” | &oAjMapXlatTbl.LASTUPDOPRID);
rem ***** End: Get/Set Component Interface Properties *****;
rem ***** Execute Save *****;
If Not &oAjMapXlatTblCi.Save() Then;
errorHandler();
throw CreateException(0, 0, “Save failed”);
End-If;
rem ***** Execute Cancel *****;
rem If Not &oAjMapXlatTblCi.Cancel() Then;
rem errorHandler();
rem throw CreateException(0, 0, “Cancel failed”);
rem End-If;
catch Exception &ex
rem Handle the exception;
&fileLog.WriteLine(&ex.ToString());
end-try;
&fileLog.WriteLine(“End”);
Create a process definition for the AE and Attach AE to a component from where you can execute it.
Create sample data file and specify the location.
First 2 records will be good, 3rd will be duplicate. Remaining 4 will be good. So the CI will stop on 3rd record. Let us execute the AE.
The AE did not complete successfully. No record was loaded. The following log was created.
&FileName = XLAT_DATA.CSV
Begin
TEST &oAjMapXlatTblCi.IBU_GROUP = CA90701
TEST &oAjMapXlatTblCi.TABLE_TYPE = PAYMENT_TERMS
&oAjMapXlatTblCi.TABLE_TYPE = PAYMENT_TERMS
&oAjMapXlatTblCi.TABLE_NAME = PAYMENT_TERMS
TEST1 &oAjMapXlatTblCollection.Count = 11
&oAjMapXlatTbl.MAP_FROM = N14
&oAjMapXlatTbl.SUBDETAIL1 =
&oAjMapXlatTbl.SUBDETAIL2 =
&oAjMapXlatTbl.DESCR_FROM = SRC N14
&oAjMapXlatTbl.TO_VALUE = N14
&oAjMapXlatTbl.DESCR_TO = PSFT N14
End
Begin
TEST &oAjMapXlatTblCi.IBU_GROUP = CA90701
TEST &oAjMapXlatTblCi.TABLE_TYPE = PAYMENT_TERMS
&oAjMapXlatTblCi.TABLE_TYPE = PAYMENT_TERMS
&oAjMapXlatTblCi.TABLE_NAME = PAYMENT_TERMS
TEST1 &oAjMapXlatTblCollection.Count = 12
&oAjMapXlatTbl.MAP_FROM = N15
&oAjMapXlatTbl.SUBDETAIL1 =
&oAjMapXlatTbl.SUBDETAIL2 =
&oAjMapXlatTbl.DESCR_FROM = SRC N15
&oAjMapXlatTbl.TO_VALUE = N15
&oAjMapXlatTbl.DESCR_TO = PSFT N15
End
Begin
TEST &oAjMapXlatTblCi.IBU_GROUP = CA90701
TEST &oAjMapXlatTblCi.TABLE_TYPE = PAYMENT_TERMS
&oAjMapXlatTblCi.TABLE_TYPE = PAYMENT_TERMS
&oAjMapXlatTblCi.TABLE_NAME = PAYMENT_TERMS
TEST1 &oAjMapXlatTblCollection.Count = 13
&oAjMapXlatTbl.MAP_FROM = N1M
&oAjMapXlatTbl.SUBDETAIL1 =
&oAjMapXlatTbl.SUBDETAIL2 =
&oAjMapXlatTbl.DESCR_FROM = SRC N1M
&oAjMapXlatTbl.TO_VALUE = N30
&oAjMapXlatTbl.DESCR_TO = PSFT N30
(18,2) – Data being added conflicts with existing data. (18,2)
(91,37) – Error saving Component Interface. {AJ_MAP_XLAT_TBL_CI} (91,37)
Save failed (0,0) AJ_XLAT_LD.PROC_STG.GBL.default.1900-01-01.Step01.OnExecute PCPC:4096 Statement:56
End
First 2 records were good but the 3rd will was already existing. So the CI stopped on 3rd record (This 3rd record is the same 11th record we added online earlier). Nothing is saved in the PS_AJ_MAP_XLAT_TBL table as there was no commit in the Application Engine. By default commit occurs at only the successful completion of Application Engine.
Delete the 3rd record from the data file. So we have 6 records in the data file now. Delete Process instance in Process Monitor. Rerun the AE.
AE completes successfully. All 6 records are processed. Let us review the data loaded.
Let see the data on the web page as well.
That is it.
When you are comfortable with this much information about Component Interfaces, you may consider the following additional related items:
1) The CSV file can have first column indicating Insert, Update or Delete. Accordingly the code will have to be modified.
2) Have Effective dated rows and see the additional handling requirements.
3) Once the file is successfully processed, it should be moved to an archive folder with proper datetime stamp.
4) Add some validation to the component and see how the Component Interface behaves when those validations fail.
5) The following queries might be useful.
a) SQL Query to find search records in a CI
SELECT SEARCHRECNAME, ADDSRCHRECNAME
FROM PSBCDEFN
WHERE BCNAME = ‘AJ_MAP_XLAT_TBL_CI’;
b) SQL Query to find out the records exposed by a CI
SELECT distinct RECNAME
FROM PSBCITEM
WHERE BCNAME = ‘AJ_MAP_XLAT_TBL_CI’;
c) SQL Query to find out the Component associated with a Component Interface
SELECT BCPGNAME,MARKET,MENUNAME
FROM PSBCDEFN
WHERE BCNAME = ‘AJ_MAP_XLAT_TBL_CI’;
d) Identify which USER, ROLE and PERMISSION LIST has access to a particular Component Interface
SELECT DISTINCT R.ROLEUSER AS USER_IDS, C.ROLENAME as ROLE, P.CLASSID AS PERMISSION_LIST
FROM PSROLEUSER R, PSROLECLASS C, PSAUTHBUSCOMP P
WHERE R.ROLENAME = C.ROLENAME
AND P.CLASSID = C.CLASSID
AND P.BCNAME = ‘AJ_MAP_XLAT_TBL_CI’
ORDER BY 1,2,3;
Recent Comments