Identify what Data model to use for Load Allocation & Planning in SCM Logistics

[centaur87]

hi folks,


I am trying to solve trailer load allocation planning.This would have been easier if i had w*h*d in my data set, but i have a combination of products for a particular trailer type.And i have multiple dealers/destination spreaded across the region. Have attached below a scenario which explains :1) assume there are 22 product item named as ( R, V, X , B, )

[TABLE="class: grid, width: 50, align: center"]
<tbody>[TR]
[TD]Sr No[/TD]
[TD]Billing Date[/TD]
[TD]Product Code[/TD]
[TD]Dealr code[/TD]
[TD]Dealr City[/TD]
[/TR]
[TR]
[TD]194[/TD]
[TD]01-08-2019[/TD]
[TD]R[/TD]
[TD]S441[/TD]
[TD]Manchester[/TD]
[/TR]
[TR]
[TD]195[/TD]
[TD]01-08-2019[/TD]
[TD]R[/TD]
[TD]S441[/TD]
[TD]Manchester[/TD]
[/TR]
[TR]
[TD]196[/TD]
[TD]01-08-2019[/TD]
[TD]R[/TD]
[TD]S441[/TD]
[TD]Manchester[/TD]
[/TR]
[TR]
[TD]197[/TD]
[TD]01-08-2019[/TD]
[TD]R[/TD]
[TD]S441[/TD]
[TD]Manchester[/TD]
[/TR]
[TR]
[TD]198[/TD]
[TD]01-08-2019[/TD]
[TD]R[/TD]
[TD]S441[/TD]
[TD]Manchester[/TD]
[/TR]
[TR]
[TD]199[/TD]
[TD]02-08-2019[/TD]
[TD]R[/TD]
[TD]S441[/TD]
[TD]Manchester[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]03-08-2019[/TD]
[TD]R[/TD]
[TD]S441[/TD]
[TD]Manchester[/TD]
[/TR]
[TR]
[TD]201[/TD]
[TD]03-08-2019[/TD]
[TD]R[/TD]
[TD]S441[/TD]
[TD]Manchester[/TD]
[/TR]
[TR]
[TD]202[/TD]
[TD]03-08-2019[/TD]
[TD]V[/TD]
[TD]S441[/TD]
[TD]Manchester[/TD]
[/TR]
[TR]
[TD]203[/TD]
[TD]03-08-2019[/TD]
[TD]V[/TD]
[TD]S441[/TD]
[TD]Manchester[/TD]
[/TR]
[TR]
[TD]204[/TD]
[TD]03-08-2019[/TD]
[TD]V[/TD]
[TD]S441[/TD]
[TD]Manchester[/TD]
[/TR]
[TR]
[TD]205[/TD]
[TD]03-08-2019[/TD]
[TD]X[/TD]
[TD]S431[/TD]
[TD]Leeds[/TD]
[/TR]
[TR]
[TD]206[/TD]
[TD]03-08-2019[/TD]
[TD]X[/TD]
[TD]S431[/TD]
[TD]Leeds[/TD]
[/TR]
[TR]
[TD]207[/TD]
[TD]03-08-2019[/TD]
[TD]X[/TD]
[TD]S431[/TD]
[TD]Leeds[/TD]
[/TR]
[TR]
[TD]208[/TD]
[TD]04-08-2019[/TD]
[TD]B[/TD]
[TD]S871[/TD]
[TD]Nottingham[/TD]
[/TR]
[TR]
[TD]209[/TD]
[TD]04-08-2019[/TD]
[TD]B[/TD]
[TD]S871[/TD]
[TD]Nottingham[/TD]
[/TR]
[TR]
[TD]210[/TD]
[TD]04-08-2019[/TD]
[TD]B[/TD]
[TD]S871[/TD]
[TD]Nottingham[/TD]
[/TR]
[TR]
[TD]211[/TD]
[TD]04-08-2019[/TD]
[TD]B[/TD]
[TD]S871[/TD]
[TD]Nottingham[/TD]
[/TR]
[TR]
[TD]212[/TD]
[TD]04-08-2019[/TD]
[TD]B[/TD]
[TD]S871[/TD]
[TD]Nottingham[/TD]
[/TR]
[TR]
[TD]213[/TD]
[TD]04-08-2019[/TD]
[TD]V[/TD]
[TD]S401[/TD]
[TD]Melbourne[/TD]
[/TR]
[TR]
[TD]214[/TD]
[TD]03-08-2019[/TD]
[TD]V[/TD]
[TD]S401[/TD]
[TD]Melbourne[/TD]
[/TR]
[TR]
[TD]215[/TD]
[TD]03-08-2019[/TD]
[TD]V[/TD]
[TD]S871[/TD]
[TD]Nottingham[/TD]
[/TR]
</tbody>[/TABLE]
2) I have some dealers to which i have to send combinations (listed below) of these 22 products

3) Dealer/Destination list :
[TABLE="class: grid, width: 50, align: center"]
<tbody>[TR]
[TD]Dealr Code[/TD]
[TD]Dealr City[/TD]
[/TR]
[TR]
[TD]S441[/TD]
[TD]Manchester[/TD]
[/TR]
[TR]
[TD]S431[/TD]
[TD]Leeds[/TD]
[/TR]
[TR]
[TD]S871[/TD]
[TD]Nottingham[/TD]
[/TR]
[TR]
[TD]S401[/TD]
[TD]Melbourne[/TD]
[/TR]
</tbody>[/TABLE]
4) to send this product from origin to different dealers i have 2 types of truck

• Type 1 Truck : can have a combination of loading product -

• 1st Combination : 6 numbers of R product as { 6R }
• 2nd Combination : 3 numbers of V product and 2 numbers of R product as { 3V, 2R }
• 3rd Combination : 5 numbers of V product as { 5V }

• Type 2 Truck : can have a combination of loading product :

• 4th Combination{ 3V, 3X }
• 5th Combination{ 5B,1V }

**the specified combinations actually utilizes full space of truck}

Product Combination Pool to fit in Truck
[TABLE="class: grid, width: 50, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]Truck Type 1[/TD]
[TD][/TD]
[TD]Truck Type 2[/TD]
[/TR]
[TR]
[TD]Comb1[/TD]
[TD]6R[/TD]
[TD]Comb4[/TD]
[TD]3V,3X[/TD]
[/TR]
[TR]
[TD]Comb2[/TD]
[TD]3V,2R[/TD]
[TD]Comb5[/TD]
[TD]5B,1V[/TD]
[/TR]
[TR]
[TD]Comb3[/TD]
[TD]5V[/TD]
[TD]Comb6[/TD]
[TD] -[/TD]
[/TR]
</tbody>[/TABLE]

5) Also we have possible routes of plying as :

• ROUTE A: Manchester , Leeds
• ROUTE B: Nottingham, Melbourne

• I want to figure out what model i need prepare my data set to achieve below:
• How to identify combinations stock (mark them as Allocated in data set after allocation) and idntify what number of trucks i will require ?

**We can use FIFO methodology to prioritize the product combination on basis on bill date.

Regards
Centaur.