M4M Stocktake notes 20240331

  • Check for Grouped / variable published products where have stock quantities or manage stock = yes
  • Check for any set to out of stock as well..
  • Consider: any published Grouped products should be accounted for in manual stock on hand
select * from vProducts where type_status not like 'simple%';
    ;
  • Check for Simple Published products where stock management not set correctly, should all be Manage Stock = yes except for custom services/addons e.g. additional shipping, custom mill/drill
select * from vProducts
where type_status like 'simple%'
	and type_status like '%publish'
    and pm_manage_stock = 'no';
  • Check for Simple products where stock > 0 and no sm_cost_wavg
select * from vProducts
	where type_status like 'simple%'
    and (
		coalesce(sm_cost_wavg, 0) = 0 or sm_cost_wavg = ''
        )
	and pm_stock is not null and pm_stock > 0
order by post_title;;
  • Check products that have a manual replace_cost keyed
  • Note: We won’t use Replace_cost this year for simple products (if ever, still considering if it is needed)
select * from vProducts
	where type_status like 'simple%'
    and replace_cost is not null and replace_cost > 0;;
  • Run the stock valuation for Simple products
  • Dump it into Excel and do the sums
select wp_post_id, type_status, replace(post_title, ',' , '') as post_title , pm_stock, sm_cost_wavg
from vProducts
where type_status like 'simple%'
	and type_status like '%publish'
order by wp_post_id
limit 10000;

All SQL together

-- Stocktake 20240401

select * from vProducts where type_status not like 'simple%';

select * from vProducts
where type_status like 'simple%'
	and type_status like '%publish'
    and pm_manage_stock = 'no'
    ;
    
select * from vProducts
	where type_status like 'simple%'
    and (
		coalesce(sm_cost_wavg, 0) = 0 or sm_cost_wavg = ''
        )
	and pm_stock is not null and pm_stock > 0
order by post_title;

select * from vProducts
	where type_status like 'simple%'
    and replace_cost is not null and replace_cost > 0;