Dear all,
I am stuck in a problem , I have the following tables :
1.composite,
2. stock
3.unit cost
In composite table, columns are parentsku, childsku andratio.
In stock table , coumns are sku , WH , FBA , intransit andPO
In Unit cost ,columns are sku and unit cost
sku unit cost
A 3
X 3
b 2.5
c 2.5
d 2.5
z 2.5
composite table:
parentsku childsku ratio
A b 1
A c 1
A d 1
X z 1
In above table parent sku A have multiple childs(b c d) andX has one.
In stock table , parent and child sku's wh and fba intransitpo value are listed and they are different like as:
stock table:
SKU Warehouse FBA On Purchase In Transit
A 22 3 4 5
b 10 6 0 0
c 11 7 0 0
d 12 8 0 0
I want to make a straight table where I want to show the sku, unitcost , wh , fba , in transit , total
In Sku , both parent and child sku will be listed.
In Wh, if parent sku is there in sku column, then itsconnected childsku's warehouse will be picked and divided by ratio(if parenthas only one child).and if child sku is there then its original warehouse willbe shown. if parent sku have multiple childs then its childsku's warehouse withminimum value will be picked up.
in unitcost column, if parent sku is there in sku columnthen its connected child sku unit cost will be picked and multiply by ratio.andif child sku is there then its original unit cost will be shown.
fba column :if parent FBA value exists in stock tableit will be shown here, same for child sku
In transit column = if parent intransit value exists instock table it will be shown here same for child sku
Total column = if child sku is in sku column then wh qty +intransit qty + fba qty
if parent sku is in sku column then in transit qty + fba qty
please let me know how to achieve this..
