How to Get MAX/Aggregate using JDE API

Purpose of Document

How to get max value using EnterpriseOne APIs through a C Business Function?

To implement this, SQL, Business Function and C programming knowledge is required.

For this example, query to be constructed is,

SQL> SELECT MAX(Col_LineNumber) FROM Schema.TableID WHERE (ColA = 'a' AND ColB = 'b');

 

Note:


Implementation:
sql> SELECT MAX(ILLNID) FROM PDCURYDTA.F4111 WHERE ( ILDOC = 0.000000 AND ILDCT = 'II' AND ILMCU = ' 1720' AND ILKCO = '01717' )

Example of Codes:


To handle this, at least three parameters are needed:

 

#include <maxlnid.h>
#include <f4111.h>

JDEBFRTN (ID) JDEBFWINAPI maxlnid(LPBHVRCOM lpBhvrCom, LPVOID lpVoid, LPDSMAXDS lpDS)

{
/************************************************************************
* Variable declarations
************************************************************************/
ID idJDEDBReturn = JDEDB_PASSED;
ID idJDBReturn = JDEDB_PASSED;
HUSER hUser = (HUSER)NULL;
NID szTableF4111ID = NID_F4111;
ID idIndexF4111ID = ID_F4111_UNIQUE_KEY_ID;
HREQUEST hRequest = (HREQUEST)NULL;

/************************************************************************
* Declare structures
************************************************************************/
SELECTSTRUCT lpdsSelect[4] = {0} ;
AGGRGFUNCSTRUCT dsF4111Aggregate[1] = {0};
MATH_NUMERIC dsAggregateBuffer[1] = {0};

/* Check for NULL pointers */
/* Initialize Behavior */

/************************************************************************
* Main Processing
************************************************************************/
memset((void *)dsF4111Aggregate, (int)_J('\0'), sizeof(dsF4111Aggregate));
memset((void *)&dsAggregateBuffer, 0x00, sizeof(dsAggregateBuffer));

idJDEDBReturn = JDB_OpenTable(hUser, szTableF4111ID, idIndexF4111ID,
                              (NULL), (ushort)(0), (JCHAR *) NULL, &hRequest);

if (idJDEDBReturn != JDEDB_FAILED )
{
     /* 1. Define F4111 Aggregate as MAX of LNID */
     jdeNIDcpy(dsF4111Aggregate[0].Item.szDict, NID_LNID);
     jdeNIDcpy(dsF4111Aggregate[0].Item.szTable, NID_F4111);
     dsF4111Aggregate[0].Item.idInstance = 0;
     dsF4111Aggregate[0].nFunction = JDB_AGGRGFUNC_MAX;

     /* 2. Set the F4111 Aggregate */
     idJDBReturn = JDB_SetAggregate(hRequest, dsF4111Aggregate,
                               1, JDB_AGGRGFUNCTION_OPT_FUNC_ONLY);

     if (idJDBReturn == JDEDB_PASSED)
     {
       /* 3. ILDOC = lpDS->mnDocVoucherInvoiceE */
        jdeNIDcpy(lpdsSelect[0].Item1.szTable, NID_F4111);
        jdeNIDcpy(lpdsSelect[0].Item1.szDict, NID_DOC);
        lpdsSelect[0].Item1.idInstance = (ID)0;
        jdeNIDcpy(lpdsSelect[0].Item2.szDict, _J(""));
        jdeNIDcpy(lpdsSelect[0].Item2.szTable, _J(""));
        lpdsSelect[0].Item2.idInstance = (ID)0;
        lpdsSelect[0].lpValue = (void *) &lpDS->mnDocVoucherInvoiceE;
        lpdsSelect[0].nValues = 1;
        lpdsSelect[0].nAndOr = JDEDB_ANDOR_AND;
        lpdsSelect[0].nCmp = JDEDB_CMP_EQ;

      /* 4. And ILDCT = lpDS->szDocumentType */
        jdeNIDcpy(lpdsSelect[1].Item1.szTable, NID_F4111);
        jdeNIDcpy(lpdsSelect[1].Item1.szDict, NID_DCT);
        lpdsSelect[1].Item1.idInstance = (ID)0;
        jdeNIDcpy(lpdsSelect[1].Item2.szDict, _J(""));
        jdeNIDcpy(lpdsSelect[1].Item2.szTable, _J(""));
        lpdsSelect[1].Item2.idInstance = (ID)0;
        lpdsSelect[1].lpValue = (void *) lpDS->szDocumentType;
        lpdsSelect[1].nValues = 1;
        lpdsSelect[1].nAndOr = JDEDB_ANDOR_AND;
        lpdsSelect[1].nCmp = JDEDB_CMP_EQ;

        idJDBReturn = JDB_SetSelection(hRequest, lpdsSelect, 2,JDEDB_SET_APPEND);
        JDB_SelectKeyed( hRequest, (ID) 0, (void *) NULL, (short) 0);

        if (idJDBReturn == JDEDB_PASSED)
        {
             idJDBReturn = JDB_FetchAggregate(hRequest, (void *)NULL,
                                              dsAggregateBuffer);
             /* 5. Return max value to mnLineNumber */
             MathCopy(&lpDS->mnLineNumber, &dsAggregateBuffer[0]);
        }
     }
}
if (hRequest != (HREQUEST)NULL)
{
    JDB_CloseTable(hRequest);
}


Alternative Methods:

An alternative method is to create a Named Event Rule (NER) business function or event rules within a Report which may yield the same result.

A. WHILE LOOP - The last row

mnInputDOC
szInputDCT
mnOutputLNID_Last

/* The last row from data which meets select test */
VA evt_LineNumber_last = "0"
F4111.Select (Case (order by Primary Key UKID Ascending))
     VA mnInputDOC = TK Document (Voucher, Invoice, etc.)
     VA evt_szInputDCT = TK Document Type
F4111.Fetch Next
   VA mnOutputLNID_Last <- TK Line Number
While SV File_IO_Status is equal to CO SUCCESS
       F4111.Fetch Next
             mnOutputLNID_Last <- TK Line Number
End While

B. Select/Fetch Next - to get the 1st row
/* choose/create index which is descending order of data */
VA evt_LineNumber_last = "0"
F4111.Select (Case (order by Foreign Key UKID Descending))
      VA mnInputDOC = TK Document (Voucher, Invoice, etc.)
      VA evt_szInputDCT = TK Document Type
If SV File_IO_Status is equal to CO SUCCESS
      F4111.Fetch Next (same index with SELECT)
           VA evt_LineNumber_last <- TK Line Number
End If



Or, make use of Aggregation option through Report Design Aid within a Level Break Footer. Refer to <Note 636697.1> - E1: RDA: Aggregate Function in Level Break Footer


Note: