Inventory Management

Author(s)

• Reshmi Karan
• Sanket Mal
• Ayan Ghosh

Last Updated Date

2025-02-02

---

SRS References

---

Version History

Version	Date	Changes	Author
1.0	2024-10-21	Initial draft	Reshmi Karan
2.0	2024-11-15	changes in class and table structures, new class additions, and an API for retrieving SFG details.	Sanket Mal
3.0	2025-02-02	Added UIUX and Frontend Task	Ayan Ghosh

---

Feature Overview

Objective:

To design an inventory management system that tracks products like scooters, bikes, parts, and accessories. The system allows SKU manage, mapping and the creation of semi-finished goods (SFG).

Scope:
The feature is specifically for Fleeto, allowing them to handle and track stock efficiently.

Dependencies:

---

Requirements

1. Batch Addition

Users should be able to add batch, when bulk SKU's add in a single batch. Action: Create an batch table that stores information about each batch.

2. SKU Addition (Saleable/Non-Saleable) and Not recognized Component Handling

Users should be able to upload SKUs, some of which may be saleable or non-saleable (for instance, accessories, parts, controller, etc. ). If any SKU is unrecognized, it should go to a separate unrecognizedcomponent section and store count(like screw, handle, etc). Action: Create an SKU table that stores information about each SKU (e.g., SKUCode, serialnumber, etc.). Users to create SKUs and categorize them as saleable or non-saleable. Unrecognized SKUs should be addition to the UnrecognizedComponent table. SKU Serial number start from manufacturer(manufacturer + number).

2. Create a Bill of Materials (BOM)

Users should be able to create a BOM for any product catalog that defines which components are required to build a specific configuration. Example: The BOM for the red version of Fleeto Magnum may include 1 charger, 5 batteries, 100 screws, etc. Action: Create a BOM table that links the Productcatalog to various components. The BOM will list the quantities of each component required for assembly.

3. Mapping and Creating SFG (Semi-Finished Goods)

Users will be able to map SKUs, unrecognizedcomponents, and catalog products to create an SFG. For example, a specific product configuration (e.g., a red Fleeto Magnum with specific components) will be treated as an SFG. Action:

Create an SFG table where the user can map CatalogProduct, SKU, and unrecognizedcomponents and bom together to create the finished or semi-finished product. The SFG will reference both the BOM and the ProductVersion.

---

Design Specifications

(Provide detailed design specifications, including UI/UX designs, API interfaces, and any other relevant architectural details.)

• UI/UX Design:
  

• Data Models:

  public record Batch
  {
      [XLColumn(Ignore = true)]
      public Guid BatchId { get; set; }
      [XLColumn(Header = "Batch Number")]
      public required string BatchNumber { get; set; }
      [XLColumn(Header = "Date Produced")]
      public System.DateTime? DateProduced { get; set; }
      [XLColumn(Ignore = true)]
      public string? LogUserName { get; set; }
      [XLColumn(Ignore = true)]
      public System.DateTime? LogDts { get; set; } = DateTime.Now;
      [XLColumn(Ignore = true)]
      [System.Text.Json.Serialization.JsonIgnore]
      [Newtonsoft.Json.JsonIgnore]
      public int TotalNumber { get; init; }
  }
  public record SKU
  {
      [XLColumn(Ignore = true)]
      public Guid SkuId { get; set; }
      [XLColumn(Ignore = true)]
      public Guid BatchId { get; init; }
      [XLColumn(Header = "SKU Code")]
      public string? SkuCode { get; set; }
      [XLColumn(Header = "SKU Serial Number")]
      public string? SerialNumber { get; set; }  //Serial number from manufacturer(manufacturer + number)
      [XLColumn(Header = "SKU Type")]
      public SKUType ComponentType { get; init; } //Type (e.g., chassis, plastic body, motor, etc.)
      [XLColumn(Header = "Selling Type")]
      public SellingType SellingType { get; init; }
      [XLColumn(Ignore = true)]
      public string? LogUserName { get; set; }
      [XLColumn(Ignore = true)]
      public System.DateTime? LogDts { get; set; }
      [XLColumn(Header = "Batch Number")]
      [System.Text.Json.Serialization.JsonIgnore]
      [Newtonsoft.Json.JsonIgnore]
      public string? BatchNumber { get; set; } // This property only for show in excel
      [XLColumn(Ignore = true)]
      [System.Text.Json.Serialization.JsonIgnore]
      [Newtonsoft.Json.JsonIgnore]
      public int TotalNumber { get; init; }
  }
  
  public record UnrecognizedComponent
  {
      [XLColumn(Ignore = true)]
      public Guid UnreComponentId { get; set; }           // Component ID
      [XLColumn(Header = "Unrecognized Component Name")]
      public string? Name { get; init; }                // Name of the component
      [XLColumn(Header = "Component Type")]
      public required SKUType UnreComponentType { get; init; }      // Type (e.g., handle, plastic body)which are not regognise like screw,holder
      [XLColumn(Header = "Total Count")]
      public int Count { get; init; }               // Count of the component
      [XLColumn(Header = "Available Count")]
      public int AvailableCount { get; set; }      // Available count
      [XLColumn(Header = "Selling Type")]
      public SellingType SellingType { get; init; }    // Selling type (Salable / Non-salable) Always non saleable
      [XLColumn(Ignore = true)]
      public required Guid BatchId { get; init; }
      [XLColumn(Ignore = true)]
      public string? LogUserName { get; set; }
      [XLColumn(Ignore = true)]
      public System.DateTime? LogDts { get; set; }
  }
  public record UnrecognizedComponentWithBatch : UnrecognizedComponent
  {
      [XLColumn(Header = "Batch Number")]
      public string? BatchNumber { get; init; }
      [XLColumn(Ignore = true)]
      [System.Text.Json.Serialization.JsonIgnore]
      public int TotalNumber { get; init; }
  }
  
  public record BOM
  {
      [XLColumn(Ignore = true)]
      public Guid BOMID { get; set; }
      [XLColumn(Ignore = true)]
      public List<ComponentWithCount> ComponentWithCount { get; set; } = new List<ComponentWithCount>();
      [XLColumn(Ignore = true)]
      public Guid? BaseProductId { get; set; }// Foreign key to BaseProduct
      [XLColumn(Ignore = true)]
      public Guid? CatalogProductId { get; set; }// Foreign key to Product catalog
      [XLColumn(Ignore = true)]
      public required Guid CatalogConfigId { get; init; }// Foreign key to ProductConfig
      [XLColumn(Ignore = true)]
      public string? LogUserName { get; set; }
      [XLColumn(Ignore = true)]
      public System.DateTime? LogDts { get; set; }
  }
  public record BOMInformation : BOM
  {
      [XLColumn(Header = "Config Name", Order = 1)]
      public string? ConfigName { get; init; }
      [XLColumn(Header = "Config Code", Order = 2)]
      public string? ConfigCode { get; init; }
      [XLColumn(Header = "Catalog Name", Order = 3)]
      public string? CatalogName { get; init; }
      [XLColumn(Header = "Catalog Code", Order = 4)]
      public string? CatalogCode { get; init; }
      [XLColumn(Header = "Item Type", Order = 6)]
      public ProductType ItemType { get; init; }
      [XLColumn(Header = "Config Status", Order = 5)]
      public ProductStatus ConfigStatus { get; init; }
      [XLColumn(Header = "Additional Info", Order = 7)]
      [System.Text.Json.Serialization.JsonIgnore]
      public string? ComponentWithCountString { get; set; }
      [XLColumn(Header = "Selling Type", Order = 8)]
      public SellingType SellingType { get; init; }
      [XLColumn(Ignore = true)]
      [System.Text.Json.Serialization.JsonIgnore]
      public int TotalNumber { get; init; }
  }
  public record ComponentWithCount
  {
      public SKUType ComponentType { get; init; }     //Type (e.g., chassis, plastic body, motor, etc.)
      public int Count { get; init; }                 // Count of components in BOM
  }
  public record Inventory
  {
    
      public Guid InventoryId { get; set; } //Inventory Id
      [XLColumn(Ignore = true)]
      public Guid BaseProductId { get; set; } // Foreign key to BaseProduct
      [XLColumn(Ignore = true)]
      public Guid CatalogProductId { get; set; } // Foreign key to ProductCatalog
      [XLColumn(Ignore = true)]
      public Guid CatalogConfigId { get; init; } // Foreign key to ProductConfig
      [XLColumn(Ignore = true)]
      public Guid VersionId { get; set; } // Foreign key to ProductVersion
      [XLColumn(Ignore = true)]
      public string? Name { get; set; } // Name of the semi-finished good
      [XLColumn(Header = "Description")]
      public string? Description { get; set; } // Name of the semi-finished good
      [XLColumn(Header = "Status")]
      public ProductStatus? Status { get; set; }
      [XLColumn(Ignore = true)]
      public required List<InventoryDetails> InventoryDetails { get; set; }
      [XLColumn(Ignore = true)]
      public string? LogUserName { get; set; }
      [XLColumn(Ignore = true)]
      public System.DateTime? LogDts { get; set; }      
  }
  public record InventoryResponse : Inventory
  {
      [XLColumn(Header = "Base Product Name")]
      public string? BaseProductName { get; init; }
      [XLColumn(Header = "Version Name")]
      public string? VersionName { get; set; }
      [XLColumn(Header = "Catalog Name")]
      public string? CatalogName { get; init; }
      [XLColumn(Header = "Catalog Code")]
      public string? CatalogCode { get; init; }
      [XLColumn(Header = "Config Name")]
      public string? CatalogConfigName { get; init; }
      [XLColumn(Header = "Config Code")]
      public string? ConfigCode { get; init; }
      [XLColumn(Ignore = true)]
      [System.Text.Json.Serialization.JsonIgnore]
      [Newtonsoft.Json.JsonIgnore]
      public int TotalNumber { get; init; }
  
  }
  public record InventoryDetails
  {
      public Guid ItemId { get; set; }
      public Guid InventoryId { get; set; } //Inventory Id                            
      public Guid? SkuId { get; init; } // Foreign key to SKU
      public SKUType? SKUType { get; init; }
      public Guid? UnrecognizedComponentId { get; init; } // Foreign key to non-sellable components        
      public int? Count { get; set; } // Number of items
      public string? LogUserName { get; set; }
      public System.DateTime? LogDts { get; set; }
  }
  public record ExtendedInventoryDetails : InventoryDetails
  {
      public string? SkuCode { get; init; }
      public string? SerialNumber { get; init; }
      public string? UnrecognizedComponentName { get; init; }
      public string? UnrecognizedComponentType { get; init; } 
  }
  
  public record InventoryFilter
  {
      public Guid? InventoryId { get; init; }
      public Guid? BaseProductId { get; init; }
      public Guid? VersionId { get; init; }
      public Guid? CatalogProductId { get; init; }
      public string? CatalogName { get; init; }
      public Guid? CatalogConfigId { get; init; }
      public string? CatalogConfigName { get; init; }
      public string? CatalogCode { get; init; }
      public string? ConfigCode { get; init; }
      public int RowsPerPage { get; set; }
      public int PageNumber { get; set; }
      public bool IsExcel { get; init; } = false;
  
  }
  
  public record BatchFilter
  {
      public Guid? BatchId { get; init; }
      public string? BatchNumber {  get; init; }
      public DateTime? StartDate { get; set; }
      public DateTime? EndDate { get; set;}
      public int RowsPerPage {  get; set; }
      public int PageNumber { get; set; }
      public bool IsExcel { get; init; } = false;
  }
  public record SKUFilter
  {
      public Guid? SkuId { get; init; }
      public Guid? BatchId { get; init; }
      public string? SkuCode { get; init; }
      public string? SerialNumber { get; init; }
      public SKUType? ComponentType { get; init; }
      public SellingType? SellingType { get; init; }
      public int RowsPerPage { get; set; }
      public int PageNumber { get; set; }
      public bool IsExcel { get; init; } = false;
  
  }
  public record BOMFilter
  {
      public Guid? BOMId { get; init; }
      public Guid? ConfigId { get; init; }
      public string? ConfigName { get; init; }
      public string? ConfigCode { get; init; }
      public string? CatalogName { get; init; }
      public ProductStatus? ConfigStatus { get; init; }
      public SKUType? SkuType { get; init; }
      public ProductType? ItemType { get; init; }
      public SellingType? SellingType { get; init; }
      public int RowsPerPage { get; set; }
      public int PageNumber { get; set; }
      public bool IsExcel { get; init; } = false;
  }
  public record UnrecognizedComponentFilter
  {
      public Guid? UnreComponentId { get; init; }
      public string? Name { get; init; }
      public SKUType? UnreComponentType { get; init; }
      public SellingType? SellingType { get; init; }
      public Guid? BatchId { get; init; }
      public string? BatchNumber { get; init; }
      public int PageNumber { get; set; }
      public int RowsPerPage { get; set; }
      public bool IsExcel { get; init; } = false;
  }
  public enum SKUType
  {
  
      Battery,
      Charger,
      Motor,
      Controller,
      Chassis,
      Scrow,
      BatteryLead,
      BatteryLithium,
      ChargerLead,
      ChargerLithium,
      ColourParts,
      Electrical,
      Mechanical,
      PlasticParts,
      Small,
      Tyre,
      Handle,
      PlasticBody
  }
  

• API Interfaces:

  Endpoint	Method	Parameters	Response	Response Status Codes
  /product/add/batch	POST	Batch	Batch	200, 400, 403, 404, 500
  /product/get/batch	POST	BatchFilter	ServerPaginatedData<Batch>	200, 400, 403, 404, 500
  /product/add/sku	POST	SKU	SKU	200, 400, 403, 404, 500
  /product/get/sku	POST	SKUFilter	ServerPaginatedData<SKU>	200, 400, 403, 404, 500
  /product/add/unrecognizedcomponent	POST	UnrecognizedComponent	UnrecognizedComponent	200, 400, 403, 404, 500
  /product/get/unrecognizedcomponent	POST	UnrecognizedComponentFilter	ServerPaginatedData<UnrecognizedComponent>	200, 400, 403, 404, 500
  /product/add/bom	POST	BOM	BOM	200, 400, 403, 404, 500
  /product/get/bom	POST	BOMFilter	ServerPaginatedData<BOMInformation>	200, 400, 403, 404, 500
  /product/add/sfg	POST	Inventory	Inventory	200, 400, 403, 404, 500
  /product/get/sfg	POST	InventoryFilter	ServerPaginatedData<InventoryResponse>	200, 400, 403, 404, 500
  /product/get/sfgdetails/{sfgid}	GET	sfgid	List<ExtendedInventoryDetails>	200, 400, 403, 404, 500

• Third-Party Integrations:
  (List any third-party services or tools that need to be integrated.)

• Workflow:

  FlowChart --

---

Development Tasks & Estimates

No	Task Name	Estimate (Hours)	Dependencies	Notes
1	Backend Create inventory models	2 hours
2	Backend Create Database schema	2 hours
3	Backend Api for add batch	3 hours
4	Backend Api for get batch	4 hours
5	Backend Api for add SKU	4 hours
6	Backend Api for get paginated SKU	5 hours
7	Backend Api for add BOM	3 hours
8	Backend Api for get BOM	4 hours
9	Backend Api for add SFG(semi finish goods)	5 hours	1-8
10	Backend Api for get paginated SFG	6 hours
11	Backend Api for get SFG details	3 hours
12	Backend Api for add Unrecognized Components	3 hours
13	Backend Api for get Unrecognized Components	4 hours
14	Backend Testing	8 hours
15	Backend Integration Testing	7 hours
Frontend
16	Batch List	3 hours
17	Add Batch	3 hours
18	Filter Batch	2 hours
19	SKU List	3 hours
20	Add SKU	3 hours
21	Filter SKU	2 hours
22	BOM List	3 hours
23	Add BOM	3 hours
24	Filter BOM	2 hours
25	SFG List	3 hours
26	Add SFG	3 hours
27	Filter SFG	2 hours
28	SFG Details	3 hours
29	Add unrecognized components	4 hours
30	List unrecognized components	4 hours
31	Filter unrecognized components	3 hours
	Total	107 hours

---

Testing & Quality Assurance

(Outline the testing strategy and quality assurance measures for the feature.)

• Unit Tests:
  (List the unit tests that will be written for this feature.)

  1. Batch Management

  Objective: Ensure Batch addition is handled correctly. Key Tests: Successfully add Batch.

  2. SKU Management

  Objective: Ensure SKU addition is handled correctly, including marking items as saleable or non-saleable. Key Tests: Successfully add SKUs. Ensure correct categorization of saleable vs non-saleable items.

  3. BOM (Bill of Materials) Creation

  Objective: Validate the creation of BOMs, mapping components (e.g., chargers, batteries) to specific product versions. Key Tests: Ensure BOM creation with correct component mapping. Validate SKU and component association with the catalog products.

  4. SFG (Semi-Finished Goods) Mapping

  Objective: Test the mapping of SKUs and components to SFG products. Key Tests: Validate that SFG products are created with the correct mappings of catalog products and components.

• Integration Tests:
  (Describe how integration testing will be conducted.)

  1. Batch
  2. SKU
  3. Component
  4. BOM
  5. SFG(Inventory)

• Acceptance Criteria:
  (Define the criteria that must be met for the feature to be considered complete.)

• Testing Tools:
  (List any tools that will be used for testing.)

---

Deployment Considerations

(Describe any deployment considerations, including environment configurations, feature toggles, or migration steps.)

• Configuration Changes:
  (Detail any configuration changes required for this feature.)

  CREATE TABLE tblBatch (
    BatchId UUID PRIMARY KEY,               -- Primary key for Batch
    BatchNumber VARCHAR(100) NOT NULL,      -- Batch number for tracking
    DateProduced DATE,                      -- Date when the batch was produced
    LogDTS TIMESTAMP WITH TIME ZONE,                        -- Timestamp for logging
    LogUserName VARCHAR(50)                -- Assigned user or owner
  );
  
  CREATE TABLE tblSKU (
    skuId UUID PRIMARY KEY,                  -- SKU_ID (PK)
    BatchID UUID,                             -- Foreign key reference to batch (FK)
    ComponentType VARCHAR(50),                -- Type (e.g., chassis, battery)
    SKU_Code VARCHAR(50) UNIQUE,              -- Unique SKU code
    SerialNumber VARCHAR(50),                 -- Serial number from manufacturer(manufacturer + number)
    SellingType TEXT,                         -- Selling type (Salable / Non-salable)
    LogDTS TIMESTAMP WITH TIME ZONE,
    LogUserName VARCHAR(50),                  -- Assigned user or owner
    CONSTRAINT FK_Batch FOREIGN KEY (batchId) REFERENCES tblBatch(batchId)
  );
  
  CREATE TABLE tblUnrecognizedComponent (
    unrecognizedComponentId UUID PRIMARY KEY,   -- Component ID (PK)
    BatchID UUID,                   -- Foreign key reference to batch (FK)
    Name VARCHAR(100),              -- Name of the component
    UnreComponentType VARCHAR(50),               -- Type (e.g., handle, plastic body)
    Count INT,                      -- Count of the component
    AvailableCount INT,             -- Available count
    SellingType VARCHAR(50),         -- Selling type (Salable / Non-salable)
    LogDTS TIMESTAMP WITH TIME ZONE,                -- Timestamp for logging
    LogUserName VARCHAR(50),          -- Assigned user or owner
    CONSTRAINT FK_ComponentBatch FOREIGN KEY (BatchId) REFERENCES tblBatch(BatchId)
  );
  
  CREATE TABLE tblBOM (
    BOMID UUID PRIMARY KEY,                  -- BOM ID (PK)
    ComponentWithCount JSONB,       -- JSONB column to store component type and count
    BaseProductID UUID,                      -- Foreign key linking to BaseProduct
    CatalogProductId UUID,                   -- Foreign key to product catalog (FK)
    ConfigId UUID,                    -- Foreign key to catalog config product (FK)
    LogDTS TIMESTAMP WITH TIME ZONE,                         -- Timestamp for logging
    LogUserName VARCHAR(50),                 -- Assigned user or owner
    CONSTRAINT FK_BaseProduct FOREIGN KEY (BaseProductID) REFERENCES tblBaseProduct(BaseProductID),
    CONSTRAINT FK_CatalogProduct FOREIGN KEY (CatalogProductId) REFERENCES tblProductCatalog(CatalogProductId),
    CONSTRAINT FK_CatalogConfig FOREIGN KEY (ConfigId) REFERENCES tblcatalogconfig(ConfigId)
  );
  
  CREATE TABLE tblProductInventory (
    InventoryId UUID PRIMARY KEY,      -- Inventory ID (PK)
    BaseProductId UUID,                 -- Foreign key to base product (FK)
    CatalogProductId UUID,              -- Foreign key to catalog product (FK)
    CatalogConfigId UUID,              -- Foreign key to catalog config product (FK)
    VersionId UUID,                    -- Foreign key to product version (FK)
    CreationDate TIMESTAMP WITH TIME ZONE,
    Status text,
    LogDTS TIMESTAMP WITH TIME ZONE,                -- Timestamp for logging
    LogUserName VARCHAR(50),           -- Assigned user or owner
    CONSTRAINT FK_BaseProduct_Inv FOREIGN KEY (BaseProductId) REFERENCES tblBaseProduct(BaseProductId),
    CONSTRAINT FK_Version_Inv FOREIGN KEY (VersionId) REFERENCES tblproductversion(VersionId),
    CONSTRAINT FK_Catalog_Inv FOREIGN KEY (CatalogProductId) REFERENCES tblproductcatalog(catalogProductId),
    CONSTRAINT FK_CatalogConfig_Inv FOREIGN KEY (CatalogConfigId) REFERENCES tblcatalogconfig(ConfigId)
  );
  
  CREATE TABLE tblInventoryDetails (
  ItemId UUID PRIMARY KEY,
    InventoryId UUID NOT NULL,               -- Primary key for semi-finished goods
    SkuId UUID,                     -- Foreign key to SKU table
  unrecognizedComponentId UUID,                    -- Foreign key to non-sellable components table
    skuType VARCHAR(100),                       -- Type of semi-finished goods
    Count INT,                               -- Number of items
  LogDTS TIMESTAMP WITH TIME ZONE,                -- Timestamp for logging
    LogUserName VARCHAR(50),          -- Assigned user or owner
  FOREIGN KEY (InventoryId) REFERENCES tblProductInventory(inventoryid),  
    FOREIGN KEY (SkuId) REFERENCES tblSKU(SkuId),             -- Reference to SKU table
    FOREIGN KEY (unrecognizedComponentId) REFERENCES tblUnrecognizedComponent(unrecognizedComponentId) -- Reference to non-sellable components
  );
  

• Rollout Plan:
  (Outline the plan for rolling out the feature, including any phased releases.)

---

Risks & Mitigations

(Identify potential risks and the strategies to mitigate them.)

Risk	Impact	Likelihood	Mitigation Strategy
Product for distibutor	High	Medium	Maintain a ConsumerId. Determine the appropriate stage in the workflow for adding it, considering the involvement of the distributor.

---

Review & Approval

(Include a section for review and approval by stakeholders.)

• Reviewer:
  Abhishak Kumar Roy

• Approval Date:
  2025-02-15

---

Notes
(Add any additional notes or considerations related to the feature development here.)