Queries: Stock

Product

Product grouped by product type

select 
	a.ad_client_id, a.producttype,count(*) 
from 
	m_product a 
group by 
	a.ad_client_id, a.producttype 
order by a.ad_client_id, a.producttype;

Cost

Products with zero cost

drop view if exists zz_product_qtys_v;

create or replace view zz_product_qtys_v as 
SELECT
	l.ad_client_id,
	p.m_product_id,
	p.name AS product_name,
	p.value AS product_code,
	sum(s.qtyonhand) AS qtyonhand,
	sum(s.qtyreserved) AS qtyreserved,
	sum(s.qtyordered) AS qtyordered,
	(sum(s.qtyonhand) - sum(s.qtyreserved)) AS qtyavailable
FROM 
	m_product p, m_storage s, m_locator l, m_warehouse w, ad_org o
WHERE
	p.m_product_id = s.m_product_id AND 
	s.m_locator_id = l.m_locator_id AND 
	l.m_warehouse_id = w.m_warehouse_id AND
     l.ad_org_id = o.ad_org_id
GROUP BY 
	l.ad_client_id, p.m_product_id, p.name, p.value
;

select * from zz_product_qtys_v;

select * from zz_product_qtys_withcosts_v;

drop view if exists zz_product_qtys_withcosts_v;

create or replace view zz_product_qtys_withcosts_v as
SELECT
	p.ad_client_id,
	cl.name as client_name,
	p.m_product_id,
	p.value as product_code,
	p.name as product_name,
	c.m_costelement_id,
	c.m_costelement_name,
	c.currentcostprice,
	c.currentqty,
	c.cumulatedamt,
	c.cumulatedqty,
	q.qtyonhand,
	q.qtyreserved,
	q.qtyordered,
	q.qtyavailable,
	pl.name as pricelist_name,
	plv.name as pricelistversion_name,
	prc.pricelist
from 
	m_product p
left join
	rv_cost c on c.m_product_id = p.m_product_id 
LEFT JOIN 
	zz_product_qtys_v q ON c.m_product_id = q.m_product_id
-- Just to get the price
left join m_productprice prc on 
		prc.ad_client_id = p.ad_client_id and 		/* double check we're in the right client */
		--prc.m_pricelist_version_id = 1000003 and 	/* */ 
		prc.m_product_id = p.m_product_id
left join
		m_pricelist_version plv on plv.m_pricelist_version_id = prc.m_pricelist_version_id
left join
		m_pricelist pl on plv.m_pricelist_id = pl.m_pricelist_id
join 
	ad_client cl on p.ad_client_id = cl.ad_client_id 
;

select * from zz_product_qtys_withcosts_v where product_code = '';

------------------------------------------------------------------
-- Main query to get the zero costs
------------------------------------------------------------------
select 
	client_name as clientname, product_code, product_name, max(qtyonhand) as qtyonhand, max(pricelist) as pricelist
from 
	zz_product_qtys_withcosts_v s 
where 
 	client_name like '%client name%'  and
 	-- Make sure we get at least one record
	--coalesce(pricelistversion_name, '11/08/2013') = '11/08/2013' and
	--m_costelement_name = 'Last Invoice' and
	m_product_id not in (
	-- 1. Products with costs
	select distinct m_product_id from zz_product_qtys_withcosts_v s 
	where 
		s.client_name like '%client name%' and
		-- Make sure we get at least one record
		coalesce(s.pricelistversion_name, '11/08/2013') = '11/08/2013' and
		s.m_costelement_name = 'Last Invoice' and
		coalesce(s.currentcostprice, 0) <> 0
)
group by client_name, product_code, product_name
order by product_code;

------------------------------------------------------------------
-- Some working queries
------------------------------------------------------------------
-- 1. Products with no cost records at all
select * from zz_product_qtys_withcosts_v s 
where 
	s.client_name like '%client name%' and
	-- Make sure we get at least one record
	coalesce(s.pricelistversion_name, '11/08/2013') = '11/08/2013' and
	s.currentcostprice is null;

-- 2. Products with zero cost, but there is something interesting about the product
select * from zz_product_qtys_withcosts_v s 
where 
	s.client_name like '%client name%' and
	-- Make sure we get at least one record
	coalesce(s.pricelistversion_name, '11/08/2013') = '11/08/2013' and
	s.m_costelement_name = 'Last Invoice' and
	s.currentcostprice = 0 and (
		s.qtyonhand <> 0 OR 
		s.qtyreserved <> 0 or 
		s.qtyordered <> 0
	);

Various cost related

select
	a.*
from
	m_cost a
where
	--a.m_product_id = 1035015;
	a.m_product_id = 
		( select m_product_id from m_product where value = 'Test-03' );

update m_cost
set currentcostprice = 50.00
where
	m_product_id = 
		( select m_product_id from m_product where value = 'Test-02' );

select
	a.*
from
	m_costdetail a
where
	--a.m_product_id = 1035015;
	a.m_product_id = 1046609;	

delete from m_cost where m_product_id in ( select m_product_id from m_product where value = 'Test-03' );
delete from m_costdetail where m_product_id in ( select m_product_id from m_product where value = 'Test-03' ) ;

Cost element

select
	count(*)
	--cl.name as clientname,
	--ele.name as costelementname,
	--prod.value as productvalue,
	--prod.name as productname,
	--a.*,
	--a.currentqty, a.currentcostprice
from
	m_cost a
join m_costelement ele on a.m_costelement_id = ele.m_costelement_id
join m_product prod on a.m_product_id = prod.m_product_id
join ad_client cl on a.ad_client_id = cl.ad_client_id
where
	a.ad_client_id = 1000000 and
	ele.name like '%Average%'
--order by
--	prod.value, ele.name ;
	--a.m_product_id = 1035015;
	;

Change costing method

Still in testing.

select * into bk_m_cost_RQ100022 from m_cost;

-- Identify records for "Exclude all items where there is an average cost entry already (81 items)"
--select
--	distinct a.m_product_id
--	--cl.name as clientname,
--	--ele.name as costelementname,
--	--prod.value as productvalue,
--	--prod.name as productname,
--	--a.currentqty, a.currentcostprice
--from
--	m_cost a
--join m_costelement ele on a.m_costelement_id = ele.m_costelement_id
--join m_product prod on a.m_product_id = prod.m_product_id
--join ad_client cl on a.ad_client_id = cl.ad_client_id
--where
--	a.ad_client_id = 1000000 and
--	ele.name like '%Average%';

-- Update
update
	m_cost
set
	m_costelement_id = ( select m_costelement_id from m_costelement where name = 'Average Invoice' and ad_client_id = 1000000 )
where
	ad_client_id = 1000000 and
	-- Exclude all items where there is an average cost entry already (81 items)
	m_product_id not in ( 
		1036146,1037058,1036065,1036427,1045882,1036542,1036747,1038486,1036979,1036615,1037721,1036974,1035066,1037092,1037012,1036742,1046172,1036540,
		1036793,1036756,1036487,1036431,1036632,1046632,1035286,1036940,1035031,1037884,1036962,1036826,1046011,1036456,1046076,1036459,1045894,1036054,
		1036095,1036163,1035074,1036714,1035032,1045866,1038110,1036750,1038118,1037015,1046361,1046135,1035518,1035271,1037014,1045916,1035025,1036429,
		1045933,1037305,1036748,1035302,1036665,1036637,1036517,1036541,1036498,1036343,1035061,1038411,1036843,1036464,1036936,1036818,1038447,1036630,
		1036068,1036098,1036100,1037071,1038501,1036461,1037873,1037013,1036533
	);