T3_Uty2BulkShippingContainerCalc.vb

📋 Overview

This file is a utility software designed to calculate the required number of containers (folding containers, returnable boxes) for bulk shipping (picking shipping). It analyzes bulk volume per shipping destination and determines the optimal container size and required count.

Main Uses:

Calculating required container count during bulk shipping
Volume analysis by shipping destination and item
Optimal selection among container sizes (Type 31, 40, 51, 60)
Practical volume calculation considering the fill rate
EIQ analysis by date and shipping destination

What is Bulk Shipping? It is a shipping method where items are not shipped by the case, but cases are opened and only the required quantities are picked. It is widely used in high-mix low-volume deliveries for retail stores.

🔧 Dependencies

Imports System.Data.OleDb

Imports Microsoft.Office.Interop.Access

Technologies Used:

OleDb: Connection to Microsoft Access Database
Access Interop: Manipulation of Access Database
SQL Queries: Data extraction and aggregation processing

🎯 Key Features

1. Container Volume Calculation

Supports 4 container sizes (Type 31, 40, 51, 60)
Calculates practical volume considering the fill rate
Calculates using user-specified fill rates

2. Shipping Data Analysis

Volume aggregation by shipping date
Bulk volume aggregation by shipping destination
Shipping volume calculation by item
Analysis by EIQ rank

3. Container Count Calculation

Required container count per shipping destination
Calculation of total container count
Calculation of pallet load count
Comparison study of multiple container sizes

4. EIQ Analysis

Aggregation of I-rank (Number of items)
Aggregation of E-rank (Number of shipping destinations)
Analysis of Q-rank (Quantity)

📦 Supported Container Sizes

Type	Inner Volume (m³)	General Use	Features
Type 31	0.0329 m³ (32.9 liters)	Small items / Low volume picking	Small, easy to handle
Type 40	0.0372 m³ (37.2 liters)	Standard picking	Medium, highly versatile
Type 51	0.05 m³ (50 liters)	Medium volume picking	Large, high capacity
Type 60	0.061 m³ (61 liters)	High volume picking	Max size, requires care with heavy items

What is Oricon? A common term for a folding container. Because they can be folded and returned after use, they are widely used as highly efficient returnable boxes in logistics.

🔍 Main Methods Details

1. Form Load Processing

Private Sub TeraCalcReference_EIQFrom_Load(sender As Object, e As EventArgs)

Initialization Process:

Display expiration date and copyright
Check existence of required files
Set database path
Display Container Vessel Dgv
Extract shipping container volume data
Display Aggregation by Shipping Date Dgv
Aggregate volume for the specified shipping date
Calculate shipping container calculated volume
Create Shipping Date Combobox

2. Start Container Volume Calculation

Private Sub ContainerVolumeCalc_Start_Click(sender As Object, e As EventArgs)

Processing Flow:

Input Validation
- Confirm if a shipping date is selected
- Check for missing data date (2022/05/14)
Data Extraction & Aggregation
- Extract shipping container volume
- Aggregate volume for the specified shipping date
- Display bulk volume by shipping destination
- Aggregate container count by shipping destination
EIQ Analysis
- Calculate item count for each I-rank
- Calculate destination count for each E-rank
- Display EIQ results
Container Count Calculation
- Calculate container count
- Calculate pallet load count
Visual Feedback
- Change background color (LightGray ⇔ GreenYellow)

Handling Missing Data: Data for 2022/05/14 is missing, so an error message is displayed when selected, and the calculation is aborted.

3. Shipping Container Calculated Volume

Private Sub ShippingContainerCalcVolume()

Calculation Logic:

Calculated Volume = Inner Volume × (Fill Rate / 100)

Calculation Example (Type 40, Fill Rate 80%):

Inner Volume: 0.0372 m³

Fill Rate: 80%

Calculated Volume = 0.0372 × 0.8 = 0.02976 m³

→ Rounded to 0.0298 m³

4. Create Shipping Date Combobox

Private Sub CreateShippingDateCb()

Processing Details:

Get the list of shipping dates from T230_DailyAggregation table
Add shipping dates to the combobox
Default to selecting the date with the maximum bulk count
Set the background color to PeachPuff (light orange)

Automatic Selection: By automatically selecting the day with the most bulk shipping, the day that requires the most calculation is prioritized for display.

💾 Database Processing

Database Structure

Database: EIQAnalysisUsingSQL.accdb

Location: DatSavePath & "\EIQAnalysisUsingSQL.accdb"

Main Tables

Table Name	Description
T000_ShippingData	Original Data (All shipping data)
T400_EIQProcessedData	Data processed for EIQ analysis
T230_DailyAggregation	Daily aggregated data
TF10_ShippingContainerVolume	Container volume for bulk shipping (Calculation results)
My_ContainerSpecs	Master data for container specifications

Extraction of Shipping Container Volume

Private Sub ExtractShippingContainerVolume()

Processing Steps:

Delete existing queries and tables (QF10_*, TF10_*)
Execute SQL queries in 3 stages

Details of SQL Queries

Step 1: Join Shipping Categories

CREATE VIEW QF10_1 AS
SELECT T400_EIQProcessedData.*, T000_ShippingData.ShippingCategory
FROM T400_EIQProcessedData
LEFT JOIN T000_ShippingData
ON T400_EIQProcessedData.RowSerialNo = T000_ShippingData.RowSerialNo;

Joins the shipping category to the EIQ processed data.

Step 2: Aggregate Bulk Shipping

CREATE VIEW QF10_2 AS
SELECT
ShippingCategory, ShippingDate, BRowERank, BRowIRank,
Item, ShippingDestination,
SUM(BulkShippingRowCount) AS RowCount,
SUM(BulkShippingBulkCount) AS BulkCount,
SUM(BulkShippingCaseConversion) AS CaseConversion,
SUM(BulkShippingPLConversion) AS PLConversion,
SUM(BulkShippingVolumeConversion) AS VolumeConversion,
SUM(BulkShippingWeightConversion) AS WeightConversion
FROM QF10_1
GROUP BY ShippingCategory, ShippingDate, BRowERank, BRowIRank, Item, ShippingDestination
HAVING ShippingCategory="Bulk_ShippingContainer" AND SUM(BulkShippingBulkCount) > 0;

Aggregates bulk shipping data by item and shipping destination.

Step 3: Create Table

SELECT QF10_2.*
INTO TF10_ShippingContainerVolume
FROM QF10_2;

Saves the view as a table.

📊 Display & Aggregation Features

1. Display Container Vessel Dgv

Sub ContainerVesselDgvDisplay()

Display Contents:

Container Type
Outer Dimensions (Width/Depth/Height)
Inner Dimensions (Width/Depth/Height)
Outer Volume
Inner Volume
Folded Dimensions
Folded Area
Remarks

2. Display Aggregation by Shipping Date Dgv

Sub AggregationByShippingDateDgvDisplay()

Display Items:

Shipping Date
Total Rows
Bulk Count
Case Conversion
Volume Conversion
Other aggregated values

3. Display Bulk Volume by Destination for Specified Date Dgv

Private Sub SpecifiedDate_BulkVolumeByDestinationDgvDisplay()

Contents:

Data by destination for the selected date
Bulk volume for each destination
Details per item
EIQ Rank information

4. Display Container Count Aggregation by Destination Dgv

Private Sub ContainerCountAggregationByDestinationDgvDisplay()

Calculations/Display Contents:

Total volume per destination
Required container count
Rounding processing (Round up)
Actual container fill rate

Required Container Count = CEILING(Total Volume / Container Calculated Volume)

5. Display Shipping Container Volume EIQ Dgv

Private Sub ShippingContainerVolumeEIQDgvDisplay()

Display EIQ Analysis Results:

Data by E-rank
Data by I-rank
Analysis of Q-rank (Quantity)
ABC Analysis results

📈 EIQ Analysis Features

What is EIQ Analysis?

E (Entry): Number of shipping destinations (Order count)
I (Item): Number of items (SKU count)
Q (Quantity): Quantity (Shipping volume)

It is a method of analyzing logistics characteristics by combining these three indicators.

1. Item Count by I-Rank

Private Sub ItemCountByIRank()

Calculation Details:

Item count for each I-rank (A, A1, A2, B, B1, B2, C)
Distribution analysis of item counts
Ratio to the total number of items

2. Destination Count by E-Rank

Private Sub DestinationCountByERank()

Calculation Details:

Destination count for each E-rank (A, A1, A2, B, B1, B2, C)
Distribution analysis of shipping destinations
Ratio to the total number of destinations

Meaning of EIQ Ranks

Rank	Description	Characteristics
Rank A	High Frequency / High Volume	Priority Management Target
Rank A1	Top of Rank A	Most Important
Rank A2	Bottom of Rank A	Important
Rank B	Medium Frequency / Med Volume	Standard Management
Rank B1	Top of Rank B	Somewhat Important
Rank B2	Bottom of Rank B	Standard
Rank C	Low Frequency / Low Volume	Simplified Management

🧮 Container Count Calculation

Display Container Vol Calc Container Count Dgv

Private Sub ContainerVolCalcContainerCountDgvDisplay()

Calculation Logic:

Aggregate total volume per destination
Calculate required container count
Required Count = CEILING(Total Volume / Container Calculated Volume)
Rounding Processing
- Always round up (to prevent shortages)
- Less than 1 is calculated as 1

Container PL Load Count Calculation

Private Sub ContainerPLLoadCountCalc()

Calculation Details:

Container load count per pallet
Required number of pallets
Evaluation of loading efficiency

Pallet Loading: Transporting containers loaded on a pallet improves handling efficiency. Generally, multiple containers are loaded onto one pallet.

🎨 UI Components

Input Controls

Control	Description	Initial Value
ShippingDateCb	Shipping Date Selection Combobox	Day with max bulk count
BulkShipping_ContainerSelectCb	Container Type Selection	Type 40 (Standard)
ShippingContainerFillRateTb	Fill Rate (%)	80%

Display TextBox

Control	Display Content
InnerVolumeTb	Container Inner Volume (m³)
ShippingContainerCalcVolumeTb	Calculated volume considering fill rate (m³)

DataGridView List

ContainerVesselDgv: Container spec master data
AggregationByShippingDateDgv: Daily shipping data aggregation
SpecifiedDate_BulkVolumeByDestinationDgv: Details by destination
ContainerCountAggregationByDestinationDgv: Container count calc results
ShippingContainerVolumeEIQDgv: EIQ analysis results
ContainerVolCalcContainerCountDgv: Total container count
WorkDgv: Temporary working space

Buttons

ContainerVolumeCalc_Start: Execute calculation processing
CloseBt: Close the form

RadioButton

DimensionExplanationRb: Toggle display of container dimension diagram

PictureBox

ShippingContainerPb: Display container images
- OriconDimensions.jpg (Dimension Diagram)
- Oricon.gif (Appearance Diagram)

🎯 Visual Feedback

Changing Background Color

When calculating, the background color of the result display area changes:

State	Background Color	Meaning
Initial State	LightGray	Uncalculated
Calculation Complete	GreenYellow	Calculated / Showing Results

If ContainerCountCalcResultChk = 0 Then

	ContainerCountCalcResultPanel.BackColor = Color.LightGray

	GroupBox23.BackColor = Color.LightGray

	ContainerCountCalcResultChk = 1

Else

	ContainerCountCalcResultPanel.BackColor = Color.GreenYellow

	GroupBox23.BackColor = Color.GreenYellow

	ContainerCountCalcResultChk = 0

End If

Shipping Date Combobox Color

PeachPuff (Light Orange): Background color to improve visibility

🔄 Overall Processing Flowchart

┌─────────────────────────────────────┐ │ Form Load │ └──────────┬──────────────────────────┘ │ ├→ Expiration / Copyright Display ├→ File Verification ├→ Set Database Path ├→ Display ContainerVesselDgv ├→ Extract Container Volume ├→ Display AggregationByShippingDateDgv ├→ Calculate CalcVolume └→ Create ShippingDateCb │ ▼ ┌─────────────────────────────────────┐ │ Select Shipping Date │ └──────────┬──────────────────────────┘ │ ▼ ┌─────────────────────────────────────┐ │ Click Calc Start Button │ └──────────┬──────────────────────────┘ │ ├→ Input Validation (Check Date, Missing Data) │ ▼ ┌─────────────────────────────────────┐ │ Data Extraction & Aggregation │ └──────────┬──────────────────────────┘ │ ├─ Extract Shipping Container Volume │ └→ Execute SQL (3 Stages) │ ├─ Aggregate Volume for Specified Date ├─ Display BulkVolumeByDestinationDgv └─ Display ContainerCountAggregationByDestinationDgv │ ▼ ┌─────────────────────────────────────┐ │ EIQ Analysis │ └──────────┬──────────────────────────┘ │ ├─ Calc Item Count by I-Rank ├─ Calc Destination Count by E-Rank └─ Display EIQResultDgv │ ▼ ┌─────────────────────────────────────┐ │ Container Count Calculation │ └──────────┬──────────────────────────┘ │ ├─ Display ContainerCountDgv │ └→ Total Vol / Calc Vol → Round Up │ └─ Calculate Container PL Load Count │ ▼ ┌─────────────────────────────────────┐ │ Result Display & Visual Feedback │ └──────────┬──────────────────────────┘ │ └→ Change Background Color (GreenYellow)

💡 Use Cases

1. Daily Operations

Calculating required container counts for the next day's shipping
Planning container preparation and layout
Determining the picking area layout

2. Planning

Estimating the total required number of containers
Evaluating container size optimization
Investment planning (Number of containers to purchase)

3. Analysis and Improvement

Understanding logistics characteristics through EIQ analysis
Characteristics analysis by shipping destination
Proposing picking efficiency improvements

4. Simulation

Impact analysis when changing fill rates
Comparing different container sizes
Confirming operational capacity for busy days

📊 DataGridView Settings

Common Settings

RowHeadersVisible = False: Hide row headers
AllowUserToAddRows = False: Disable user row addition
AutoSizeColumnsMode = AllCells: Auto-adjust column width
AutoSizeRowsMode = AllCells: Auto-adjust row height

Numeric Column Settings

For j = 2 To 19

	.Columns(j).DefaultCellStyle.Alignment = _

		DataGridViewContentAlignment.MiddleRight

Next

Columns from the 2nd onwards (numeric data) are right-aligned.

⚠️ Important Notes & Error Handling

Handling Missing Data

Regarding data for 2022/05/14:
Data for this day is missing, so an error message is displayed when selected.

If ShippingDateCb.Text = "2022/05/14" Then

	MsgBox("Data missing. Please select another shipping date.")

	Exit Sub

End If

Input Validation

If a shipping date is not selected, processing is aborted.
If the fill rate is empty, calculations are skipped.

Notes on Calculation

Realistic Fill Rate: 80-90% is typical. 100% is difficult in practice.
Rounding: Always round up (to prevent shortages).
Container Size: Select according to destination characteristics.
Weight Limits: Consideration of weight is needed, not just volume.

🔗 Related Modules

This utility integrates with the following Tera Calculation Systems:

T000_ShippingData: Source of original input data
T400_EIQProcessedData: Base data for EIQ analysis
T3_3 PackagingVolume: Integration with packaging calculations
T3_4 ShippingSpace: Integration with shipping space calculations

💾 File & Path Structure

Database File

MyDB = DatSavePath & "\EIQAnalysisUsingSQL.accdb"

Image Files

MyPath = Environment.CurrentDirectory

OriconDimensions.jpg: Dimension Diagram

Oricon.gif: Appearance Diagram

SoftData Path

SoftDataPath = DatSavePath & "\SoftData"

📚 Summary

T3_Uty2BulkShippingContainerCalc.vb is a specialized tool that precisely calculates the required number of containers for bulk shipping.

Key Features

✅ Supports 4 container sizes (Type 31 to 60)
✅ Practical volume calculation considering fill rates
✅ Detailed analysis by destination and item
✅ Understanding logistics characteristics via EIQ analysis
✅ Fast data processing using SQL
✅ Visual feedback functionality

Application Areas

Bulk shipping for retail (store delivery)
Operational planning for picking centers
Requirement management for returnable boxes (Oricon)
Logistics cost analysis and reduction

Points to Improve Calculation Accuracy

Setting fill rates based on historical actuals
Selecting container sizes suited to destination characteristics
Periodic reviews via EIQ analysis

Practical Note: Calculation results are theoretical. In actual operation, securing spare containers (10-20%) is recommended.

📞 Support Information

Document Date: Auto-generated

Target File: T3_Uty2BulkShippingContainerCalc.vb

Language: Visual Basic .NET (VB.NET)

Total Lines: 637 lines

Core Technologies: Windows Forms, Access Database, SQL, EIQ Analysis