In Out Inventory Stock

I’ve just made a Form Application that calculate In Out Inventory Stock,

enter the filter, and press the Calculate button

and this is how I do it

• create a temporary table called GLK_TBL_INOUTINV
  • Table Type : InMemory
  • the Fields are:
    • DateFinancial (extended),
    • DatePhysical (extended),
    • Description,
    • InventLocationId (extended),
    • InventSiteId (extended),
    • ItemId (extended),
    • ItemName (extended),
    • QTYAwal,
    • QTYAkhir,
    • QTYIn,
    • QTYOut,
    • ReferenceId (extended InventTransRefId),
    • ReferenceType (EnumType InventTransType),
    • WMSLocationId (extended)
  • Make sure that this table relation has 3 relation,
    • InventLocation (GLK_TBL_INOUTINV.InventLocationId==InventLocation.InventLocationId)
    • InventSite (GLK_TBL_INOUTINV.InventSiteId==InventSite.SiteId)
    • InventTable (GLK_TBL_INOUTINV.ItemId==InventTable.ItemId)
• Create the forms GLK_FRM_INOUTINV
• Drag the GLK_TBL_INOUTINV to forms Datasource
• Create GroupFilter and Grid on Design Form like the picture below 
• remember to set autodeclaration=yes in StringInventLocation, StringItemId, DateEditTransStart, DateEditTransEnd
• add clicked method on RefreshButton

  void clicked()
  {
      super();
      startLengthyOperation();
      element.populateData();
      endLengthyOperation();
  }

   

• Create 3 methods in the forms, class init, class emptyData, dan class populateData

  public void init()
  {
      GLK_TBL_INOUTINV tmpTable;
      super();
      delete_from tmpTable;
  
  }
  
  GLK_TBL_INOUTINV emptyData()
  {
      GLK_TBL_INOUTINV tmp;
      return tmp;
  }
  
  public void populateData()
  {
      GLK_TBL_INOUTINV _temp;
      Query q;
      QueryRun qr;
      QueryBuildDataSource qbds,qbds2,qbds3;
      str InfoError, GantiItem, GantiLocation, Keterangan;
      InventTrans _inventTrans, _inventTransSA;
      InventDim _inventDim, _inventDimSA;
      InventTransOrigin _inventTransOrigin;
      SalesTable _salesTable;
      PurchTable _purchTable;
      int i;
  
      InventJournalTable _inventJournalTable;
  
      ;
  
      InfoError = (StringInventLocationID.valueStr()=="") ? "Warehouse harus diisi":
      ((StringItemID.valueStr()=="") ? "Item Harus diisi": "");
      if(InfoError != "")
      {
          info(strFmt("%1",InfoError));
      }
      else
      {
          q = new Query("Inventory_Transactions");
          qbds = q.addDataSource(tableNum("InventTrans"));
          qbds.addSelectionField(fieldNum(InventTrans,ItemId));
          qbds.addSelectionField(fieldNum(InventTrans,DatePhysical));
          qbds.addSelectionField(fieldNum(InventTrans,DateFinancial));
          qbds.addSelectionField(fieldNum(InventTrans,Qty));
          qbds.addSelectionField(fieldNum(InventTrans,VoucherPhysical));
          qbds.addSelectionField(fieldNum(InventTrans,InventTransOrigin));
          qbds.addRange(fieldNum(InventTrans,DatePhysical)).value(DateEditTransStart.valueStr() + ".." + DateEditTransEnd.valueStr());
          qbds.addRange(fieldNum(InventTrans,ItemId)).value(StringItemID.valueStr());
  
          qbds2 = qbds.addDataSource(tableNum("InventDim"));
          qbds2.addSelectionField(fieldNum(InventDim,InventSiteId));
          qbds2.addSelectionField(fieldNum(InventDim,InventLocationId));
          qbds2.addSelectionField(fieldNum(InventDim,WMSLocationId));
  
          qbds2.addRange(fieldNum(InventDim, InventLocationId)).value(StringInventLocationID.valueStr());
          qbds2.addLink(fieldNum(InventTrans,InventDimId), fieldNum(InventDim,InventDimId));
  
          qbds2.addOrderByField(fieldNum(InventDim, InventLocationId));
          qbds.addOrderByField(fieldNum(InventTrans, ItemId));
          qbds.addOrderByField(fieldNum(InventTrans, DatePhysical));
          qbds.addOrderByField(fieldNum(InventTrans, RecId));
  
          qr = new QueryRun(q);
  
          GantiItem = "";
          GantiLocation = "";
          i = 0;
  
          while(qr.next())
          {
              _inventDim= qr.get(tableNum(InventDim));
              _inventTrans = qr.get(tableNum(InventTrans));
              _inventTransOrigin = InventTransOrigin::find(_inventTrans.InventTransOrigin);
  
              if(GantiItem != _inventTrans.ItemID
              || GantiLocation != _inventDim.InventLocationId)
              {
                  select sum(Qty) from _inventTransSA
                  join _inventDimSA
                  where _inventDimSA.inventDimId==_inventTransSA.inventDimId
                      && _inventDimSA.InventSiteId==_inventDim.InventSiteId
                      && _inventDimSA.InventLocationId==_inventDim.InventLocationId
                      && _inventDimSA.wMSLocationId==_inventDim.wMSLocationId
                      && _inventTransSA.DatePhysical < DateEditTransStart.dateValue()
                      && _inventTransSA.DatePhysical > str2Date("1900-01-01",321)
                      && _inventTransSA.ItemId==_inventTrans.ItemId
                  ;
  
                  _temp.QTYAWAL=_inventTransSA.Qty;
                  GantiItem = _inventTrans.ItemID;
                  GantiLocation = _inventDim.InventLocationId;
              }
              _temp.InventLocationID = _inventDim.inventLocationId;
              _temp.InventSiteID = _inventDim.InventSiteId;
              _temp.WMSLocationID = _inventDim.wMSLocationId;
              _temp.DateFinancial = _inventTrans.DateFinancial;
              _temp.DatePhysical = _inventTrans.DatePhysical;
              _temp.ItemID = _inventTrans.ItemId;
              _temp.QTYIN = (_inventTrans.qty < 0) ? 0: _inventTrans.Qty;
              _temp.QTYOUT = (_inventTrans.qty > 0) ? 0: (-1 * _inventTrans.Qty);
              _temp.QTYAKHIR = _temp.QTYAWAL + _inventTrans.qty;
              _temp.ItemName = _inventTrans.itemName();
  
              _temp.ReferenceID = _inventTransOrigin.ReferenceId;
              _temp.ReferenceType = _inventTransOrigin.ReferenceCategory;
              _temp.Description = "";
  
              _inventJournalTable = InventJournalTable::find(_temp.ReferenceID);
              if(_inventJournalTable)
              {
                  _temp.Description =_inventJournalTable.Description;
              }
              else
              {
                  _salesTable = SalesTable::find(_temp.ReferenceID);
                  if(_salesTable)
                  {
                      _temp.Description =_salesTable.customerName();
                  }
                  else
                  {
                      _purchTable = PurchTable::find(_temp.ReferenceID);
                      if(_purchTable)
                      {
                          _temp.Description = _purchTable.vendorName();
                      }
                  }
              }
              //_temp.Description += "," + _inventTrans.VoucherPhysical;
              _temp.insert();
              _temp.QTYAWAL= _temp.QTYAKHIR;
              i++;
          }
  
          //info(q.toString());
          GLK_TBL_INOUTINV.setTmpData(_temp);
          GLK_TBL_INOUTINV_ds.executeQuery();
      }
  }

   

you can download the xpo here