How to Sum/Aggregate Quantity or Amount using JDE API

Purpose of Document

How can I sum/aggregate amounts or quantities 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 SUM(Col_Quantity) FROM Schema.TableID WHERE (ColA = 'a' AND ColB = 'b') GROUP BY ColA, ColB;

 

Note:


Implementation:

SQL> SELECT SUM(LIPQOH) FROM PRODDTA.F41021 WHERE (LIMCU="Input MCU" AND LIITM ="Input Item -Short") GROUP BY LIMCU, LIITM;

To handle this, at least three parameters are needed:

Example:

JDEBFRTN(ID) JDEBFWINAPI SUMPQOH (LPBHVRCOM lpBhvrCom,
                                  LPVOID lpVoid,
                                  LPDSSUMDS lpDS)
{
/************************************************************************
* Variable declarations
************************************************************************/
ID idJDBReturn = JDEDB_PASSED;
HREQUEST hRequest = (HREQUEST) NULL;
ushort uGroupBy = 2;
ushort uKeys = 2;
NID szColumns2[2] = {NID_ITM, NID_MCU};
/************************************************************************
* Declare Structures
************************************************************************/
KEY1_F41021 dsF41021Key1 = {0};
DBREF dsF41021GroupBy[2] = {0};
AGGRGFUNCSTRUCT dsF41021Aggregate[1] = {0};
MATH_NUMERIC dsAggregateBuffer[1] = {0};

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

idJDBReturn = JDB_OpenTable(hUser, NID_F41021, ID_F41021_ITEM__BRANCH___A,
                            szColumns2, (ushort)2, (JCHAR *)NULL, &hRequest);

if (idJDBReturn == JDEDB_PASSED)
{
  /* 1. Group the F41021 records by ITM and MCU */
  jdeNIDcpy(dsF41021GroupBy[0].szDict, NID_ITM);
  jdeNIDcpy(dsF41021GroupBy[0].szTable, NID_F41021);
  dsF41021GroupBy[0].idInstance = 0;

  jdeNIDcpy(dsF41021GroupBy[1].szDict, NID_MCU);
  jdeNIDcpy(dsF41021GroupBy[1].szTable, NID_F41021);
  dsF41021GroupBy[1].idInstance = 0;

  /* 2. Set the F41021 Group By */
  idJDBReturn = JDB_SetGroupBy(hRequest, dsF41021GroupBy, uGroupBy);

  if (idJDBReturn == JDEDB_PASSED)
  {
    /* 3. Define F41021 Aggregate as SUM of PQOH */
    jdeNIDcpy(dsF41021Aggregate[0].Item.szDict, NID_PQOH);
    jdeNIDcpy(dsF41021Aggregate[0].Item.szTable, NID_F41021);
    dsF41021Aggregate[0].Item.idInstance = 0;
    dsF41021Aggregate[0].nFunction = JDB_AGGRGFUNC_SUM;

    /* 4. Set the F41021 Aggregate */
    idJDBReturn = JDB_SetAggregate(hRequest, dsF41021Aggregate,
                                   1, JDB_AGGRGFUNCTION_OPT_FUNC_COLUMNQUERY);
    if (idJDBReturn == JDEDB_PASSED)
    {
      MathCopy(&dsF41021Key1.liitm, &lpdsSUMDS->mnInputITM);
      jdeStrcpy((JCHAR *)dsF41021Key1.limcu, (const JCHAR *)lpdsSUMDS->szInputMCU);

      /* 5. If the set aggregate worked, select the correct F41021 records to sum */
      idJDBReturn = JDB_SelectKeyed(hRequest, 0, &dsF41021Key1, uKeys);
      if (idJDBReturn == JDEDB_PASSED)
      {
        /* 6. Continue Fetch Aggregate based on Key Selected */
        idJDBReturn = JDB_FetchAggregate(hRequest, (void *)NULL,
                                         dsAggregateBuffer);
        if (idJDBReturn == JDEDB_PASSED)
        {
          /* 7. Return sum(itlqoh) */
          MathCopy(&lpdsSUMDS->mnQtySum, &dsAggregateBuffer[0]);
        }
      }
    }
   }
 }
 if (hRequest != (HREQUEST)NULL)
 {
   JDB_CloseTable(hRequest);
 }
  return;
}


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.

    mnQtySum = 0
    mnQtySum_perLine = 0

    F41021.SELECT
      ILMCU = szInputMCU
      ILITM = mnInputITM
    F41021.FETCH NEXT
      ILPQOH -> mnQtySum_perLine
    WHILE SV File IO is CO SUCESS
       mnQtySum = mnQtySum + mnQtySum_perLine
      F41021.FETCH NEXT
       ILPQOH -> mnQtySum_perLine
    END WHILE


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


Note: