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
);
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 );
