AR: Update Sales Costs from Product Standard Cost

From KeystoneIntranet
Jump to navigation Jump to search

Sales reports record costs at the time of invoicing. Sometimes these costs get set at a later point. This process allows you to rewrite the cost recorded in sales with the current standard costs in ARTPROD.

The example below is based on Mixes (product_type='M'). It can be adjusted to different product types or product classes etc. In the case of mixes the best process is to update all ingredient costs and then run Product Cost Rollup.


DataScope Preview Query

This reports all calculated costs compared to the current costs n the sales file. It's great to see indicate how much costs will change. The report can be made available to end users.

Either this (written in the style of the update query) 

select Product_code,
(select p.standard_cost from artprod p where p.product_code=sd.product_code) "StdCost",
(select p.standard_cost*sd.qty_sold from artprod p where p.product_code=sd.product_code) "ExtCostCalc",
EXT_COST "ExtCostCurrent",
sd.* from artslsdt sd
where 
 EXISTS (select p.product_type from artprod p where p.product_code=sd.product_code AND P.PRODUCT_TYPE='M')
AND EXISTS (SELECT 1 FROM  artslshd sh WHERE sh.session_no=sd.session_no and sh.trans_no=sd.trans_no  AND sh.ticket_date between :Begin and :End)

select Product_code,
p.standard_cost "StdCost",
p.standard_cost*sd.qty_sold "ExtCostCalc",
EXT_COST "ExtCostCurrent",
sd.*,p.base_product "Mix" from artslsdt sd
inner join  artprod p on p.product_code=sd.product_code AND P.PRODUCT_TYPE='M'
inner join artslshd sh on sh.session_no=sd.session_no and sh.trans_no=sd.trans_no  
where 
 sh.ticket_date between :Begin and :End


Preview and Export Changes Query

Good to get list of records that will get updated. Export to a .CSV file for archive. Filter matches the update query. 

select Product_code,
(select p.standard_cost from artprod p where p.product_code=sd.product_code) "StdCost",
(select  p.standard_cost*sd.qty_sold from artprod p where p.product_code=sd.product_code) "ExtCostCalcRaw",
(select  floor(.5+(100*p.standard_cost*sd.qty_sold))/100  from artprod p where p.product_code=sd.product_code) "ExtCostCalc",
EXT_COST "ExtCostCurrent",
sd.* from artslsdt sd
where 
 EXISTS (select p.product_type from artprod p where p.product_code=sd.product_code AND P.PRODUCT_TYPE='M' and p.standard_cost<>0 and floor(.5+(100*p.standard_cost*sd.qty_sold))/100<>sd.ext_cost)
AND EXISTS (SELECT 1 FROM  artslshd sh WHERE sh.session_no=sd.session_no and sh.trans_no=sd.trans_no  AND sh.ticket_date between :Begin and :End)

Update Query

Run this in IB Console after exporting the preview. 


update artslsdt sd
set sd.ext_cost=(select  floor(.5+(100*p.standard_cost*sd.qty_sold))/100  from artprod p where p.product_code=sd.product_code)
where
 EXISTS (select p.product_type from artprod p where p.product_code=sd.product_code AND P.PRODUCT_TYPE='M' and p.standard_cost<>0 and floor(.5+(100*p.standard_cost*sd.qty_sold))/100<>sd.ext_cost)
AND EXISTS (SELECT 1 FROM  artslshd sh WHERE sh.session_no=sd.session_no and sh.trans_no=sd.trans_no  AND sh.ticket_date between :Begin and :End)
Retrieved from "http://in.compucrete.com/index.php?title=AR:_Update_Sales_Costs_from_Product_Standard_Cost&oldid=22"