Programmer:Item Opening Maintenance

Technical Specification

1. Quantity of the Item Opening must not be zero
   • When adding a new entry to Item Opening, zero qty entry will be removed before saving to database
   • Positive (+ve) & negative (-ve) quantity are allowed
2. Best to filter the loading of Item Opening
   • Avoid loading entire data of Item Opening, when there might be more than 100k records in Item Opening of different UOM, location, batches and etc.
   • Filters that can be applied to reduce the loading of Item Opening data
     • ItemCode
     • ItemGroup
     • ItemType
     • Location
   • The filtering will have impact on adding new record of an ItemCode that is not in the range of the filtered range.

     If the newly added ItemCode is not in the filter range, system throw exception message of "Item Code is not in Filter range."

3. The value of Seq cannot repeat the same, when the value of below columns are the same, or already exist in the record of Item Opening:
   • ItemCode
   • UOM
   • Location
   • BatchNo

   If there is any occasion when Seq is repeated for the same ItemCode, UOM, Location and BatchNo, below error message is thrown:

   Sequence for Item 'FG00001', UOM 'UNIT', Location 'HQ', BatchNo ' ' is not correct.

4. Seq will affect how the cost of FIFO and LIFO is calculated.

References of AutoCount Accounting version 1.8

BCE.AutoCount.dll
BCE.AutoCount.CommonAccounting.dll
BCE.AutoCount.MainEntry.dll
BCE.Utils.dll
BCE.Utils.UI.dll
BCE.AutoCount.StockMaint.dll

Load Item Opening

Load Item Opening with ItemCode Filtering

public DataTable GetStockOpening(BCE.Data.DBSetting dbSetting)
{
    BCE.AutoCount.Stock.Item.ItemOpeningDataAccess cmd = BCE.AutoCount.Stock.Item.ItemOpeningDataAccess.Create(dbSetting);
    BCE.AutoCount.Stock.Item.ItemOpeningCriteria crit = new BCE.AutoCount.Stock.Item.ItemOpeningCriteria();

    crit.ItemCodeFilter.Type = BCE.AutoCount.SearchFilter.FilterType.ByRange;
    crit.ItemCodeFilter.From = "FG00001";
    crit.ItemCodeFilter.To = "FG00003";

    DataTable dtOpening = cmd.LoadAutoGenerateTable(crit);
    return dtOpening;
}

• This example shows the loading of ItemCode from "FG00001" to "FG00003".

Load Item Opening with Location Filtering

public DataTable GetStockOpening(BCE.Data.DBSetting dbSetting)
{
    BCE.AutoCount.Stock.Item.ItemOpeningDataAccess cmd = BCE.AutoCount.Stock.Item.ItemOpeningDataAccess.Create(dbSetting);
    BCE.AutoCount.Stock.Item.ItemOpeningCriteria crit = new BCE.AutoCount.Stock.Item.ItemOpeningCriteria();

    crit.LocationFilter.Type = BCE.AutoCount.SearchFilter.FilterType.ByRange;
    crit.LocationFilter.From = "HQ";
    crit.LocationFilter.To = "HQ";

    DataTable dtOpening = cmd.LoadAutoGenerateTable(crit);
    return dtOpening;
}

• This example shows the loading of items opening at location "HQ".

Load Item Opening with the combination of filters

public DataTable GetStockOpening(BCE.Data.DBSetting dbSetting)
{
    BCE.AutoCount.Stock.Item.ItemOpeningDataAccess cmd = BCE.AutoCount.Stock.Item.ItemOpeningDataAccess.Create(dbSetting);
    BCE.AutoCount.Stock.Item.ItemOpeningCriteria crit = new BCE.AutoCount.Stock.Item.ItemOpeningCriteria();

    //Filter ItemCode
    crit.ItemCodeFilter.Type = BCE.AutoCount.SearchFilter.FilterType.ByRange;
    crit.ItemCodeFilter.From = "FG00001";
    crit.ItemCodeFilter.To = "FG00003";

    //Filter Location
    crit.LocationFilter.Type = BCE.AutoCount.SearchFilter.FilterType.ByRange;
    crit.LocationFilter.From = "HQ";
    crit.LocationFilter.To = "HQ";

    DataTable dtOpening = cmd.LoadAutoGenerateTable(crit);
    return dtOpening;
}

Add New Entry to Item Opening

Add New Item Opening with the method of NewItemOpening()

public void NewStockOpening(BCE.Data.DBSetting dbSetting)
{
    BCE.AutoCount.Stock.Item.ItemOpeningDataAccess cmd = BCE.AutoCount.Stock.Item.ItemOpeningDataAccess.Create(dbSetting);

    //Create and define ItemOpeningCriteria
    BCE.AutoCount.Stock.Item.ItemOpeningCriteria crit = new BCE.AutoCount.Stock.Item.ItemOpeningCriteria();

    //Filter ItemCode by each item
    crit.ItemCodeFilter.Type = BCE.AutoCount.SearchFilter.FilterType.ByIndividual;
    crit.ItemCodeFilter.Add("FG00001");
    crit.ItemCodeFilter.Add("FG00003");

    //Filter only location to "HQ"
    crit.LocationFilter.Type = BCE.AutoCount.SearchFilter.FilterType.ByRange;
    crit.LocationFilter.From = "HQ";
    crit.LocationFilter.To = "HQ";

    //Load records of existing Items
    BCE.AutoCount.Stock.Item.ItemOpeningEntities entities = cmd.LoadItemOpening(crit);

    //Create new Item Opening Entry
    BCE.AutoCount.Stock.Item.ItemOpeningEntity entity1 = entities.NewItemOpening();
    entity1.ItemCode = "FG00001";
    entity1.Uom = "UNIT";
    entity1.Location = "HQ";
    entity1.Qty = 1;
    entity1.Cost = 5.01M;

    //Create new Item Opening Entry
    BCE.AutoCount.Stock.Item.ItemOpeningEntity entity2 = entities.NewItemOpening();
    entity1.ItemCode = "FG00003";
    entity1.Uom = "UNIT";
    entity1.Location = "HQ";
    entity1.Qty = 2;
    entity1.Cost = 6M;

    try
    {
        cmd.Save(entities);
        BCE.Application.AppMessage.ShowMessage("Item Opening Saved.");
    }
    catch (BCE.Application.AppException ex)
    {
        BCE.Application.AppMessage.ShowErrorMessage(ex.Message);
    }
}

Adding two entries of same ItemCode to Item Opening

Following example shows the increase of Seq when 2nd entry of Item Opening has the same ItemCode, UOM, Location and BatchNo from the 1st entry.

public void NewTwoEntriesOfSameItem(BCE.Data.DBSetting dbSetting)
{
    BCE.AutoCount.Stock.Item.ItemOpeningDataAccess cmd = BCE.AutoCount.Stock.Item.ItemOpeningDataAccess.Create(dbSetting);

    //Create and define ItemOpeningCriteria
    BCE.AutoCount.Stock.Item.ItemOpeningCriteria crit = new BCE.AutoCount.Stock.Item.ItemOpeningCriteria();

    //Filter ItemCode by each item
    crit.ItemCodeFilter.Type = BCE.AutoCount.SearchFilter.FilterType.ByIndividual;
    crit.ItemCodeFilter.Add("FG00001");

    //Filter only location to "HQ"
    crit.LocationFilter.Type = BCE.AutoCount.SearchFilter.FilterType.ByRange;
    crit.LocationFilter.From = "HQ";
    crit.LocationFilter.To = "HQ";

    //Load records of existing Items
    BCE.AutoCount.Stock.Item.ItemOpeningEntities entities = cmd.LoadItemOpening(crit);

    //Create new Item Opening Entry
    BCE.AutoCount.Stock.Item.ItemOpeningEntity entity = entities.NewItemOpening();
    entity.ItemCode = "FG00001";
    entity.Uom = "UNIT";
    entity.Location = "HQ";
    entity.Qty = 2;
    entity.Cost = 5.01M;
    entity.Seq = 0;

    //Increase the Seq of 2nd Entry of same item
    entity = entities.NewItemOpening();
    entity.ItemCode = "FG00001";
    entity.Uom = "UNIT";
    entity.Location = "HQ";
    entity.Qty = 5;
    entity.Cost = 5.02M;
    entity.Seq = 1;

    try
    {
        cmd.Save(entities);
        BCE.Application.AppMessage.ShowMessage("Item Opening Saved.");
    }
    catch (BCE.Application.AppException ex)
    {
        BCE.Application.AppMessage.ShowErrorMessage(ex.Message);
    }
}

Find and Delete Item Opening entry

When delete existing item opening entry, "ItemOpeningKey" is required.
This example uses a function to find the ItemOpeningKey.
Highlighted shows the function to get the list of keys and deletion of existing keys.

Get "ItemOpeningKey" can be also applied to check the existing record when it can avoid the error of
"Sequence for Item 'FG00001', UOM 'UNIT', Location 'HQ', BatchNo ' ' is not correct."

public void CheckNewEntryBeforeAdding(BCE.Data.DBSetting dbSetting)
{
    BCE.AutoCount.Stock.Item.ItemOpeningDataAccess cmd = BCE.AutoCount.Stock.Item.ItemOpeningDataAccess.Create(dbSetting);

    //Create and define ItemOpeningCriteria
    BCE.AutoCount.Stock.Item.ItemOpeningCriteria crit = new BCE.AutoCount.Stock.Item.ItemOpeningCriteria();

    //Filter ItemCode by each item
    crit.ItemCodeFilter.Type = BCE.AutoCount.SearchFilter.FilterType.ByIndividual;
    crit.ItemCodeFilter.Add("FG00001");

    //Filter only location to "HQ"
    crit.LocationFilter.Type = BCE.AutoCount.SearchFilter.FilterType.ByRange;
    crit.LocationFilter.From = "HQ";
    crit.LocationFilter.To = "HQ";

    //Load records of existing Items
    BCE.AutoCount.Stock.Item.ItemOpeningEntities entities = cmd.LoadItemOpening(crit);

    //Delete any existing entries that has same ItemCode, UOM, Location and BatchNo,
    //before adding new entries
    List<long> listKeys = GetExistItemOpeningKey(entities.ItemOpeningTable, "FG00001", "UNIT", "HQ", null);
    listKeys.ForEach(key => entities.DeleteItemOpening(key));

    //Create new Item Opening Entry
    BCE.AutoCount.Stock.Item.ItemOpeningEntity entity = entities.NewItemOpening();
    entity.ItemCode = "FG00001";
    entity.Uom = "UNIT";
    entity.Location = "HQ";
    entity.Qty = 2;
    entity.Cost = 5.01M;
    entity.Seq = 0;

    //Increase the Seq of 2nd Entry of same item
    entity = entities.NewItemOpening();
    entity.ItemCode = "FG00001";
    entity.Uom = "UNIT";
    entity.Location = "HQ";
    entity.Qty = 5;
    entity.Cost = 5.02M;
    entity.Seq = 1;

    try
    {
        cmd.Save(entities);
        BCE.Application.AppMessage.ShowMessage("Item Opening Saved.");
    }
    catch (BCE.Application.AppException ex)
    {
        BCE.Application.AppMessage.ShowErrorMessage(ex.Message);
    }
}

Get ItemOpeningKey

Function to find a list of keys that is/are matching.

public List<long> GetExistItemOpeningKey(DataTable table, string itemCode, string uom, string location, string batchNo)
{
    return table.AsEnumerable().Where(r => r.RowState != DataRowState.Deleted &&
        r.Field<string>("ItemCode") == itemCode &&
        r.Field<string>("UOM") == uom &&
        r.Field<string>("Location") == location &&
        r.Field<string>("BatchNo") == batchNo
    ).Select(r => r.Field<long>("ItemOpeningKey"))
    .ToList();
}

Another function to specially handle Deleting matching Item Opening

public void DeleteAllMatchingItemOpening(IEnumerable<DataRow> itemOpenings, string itemCode, string uom, string location, string batchNo, Action<long> deleteOpening)
{
    itemOpenings.Where(r => r.RowState != DataRowState.Deleted &&
        r.Field<string>("ItemCode") == itemCode &&
        r.Field<string>("UOM") == uom &&
        r.Field<string>("Location") == location &&
        r.Field<string>("BatchNo") == batchNo
    ).Select(r => r.Field<long>("ItemOpeningKey"))
    .ToList().ForEach(key => deleteOpening(key));
}

To apply this function code;

DeleteAllMatchingItemOpening(entities.ItemOpeningTable.AsEnumerable(), "FG00001", "UNIT", "HQ", null, entities.DeleteItemOpening);

See Also

• Item Group
• Stock Item (Item Maintenance)
• Stock Adjustment
• Stock Transfer
• Stock Assembly
• Stock Balance with Costing - [1.8, 1.9] [2.0]
• Item Opening Balance Maintenance - [1.8, 1.9] [2.0]

Go to menu

Go to top

Resources For AutoCount Software Developers