Account Tree got mixed up
Impact - Wrong value in Balance sheet and P&l structure report
Reason - Manual error(drag and drop), wrong account type, improper "IsSummary" (folder structure)
Solution:
--All accounts,breakdown,header should have SUMMARY LEVEL yes
select c_elementvalue_id,value,name,elementlevel from c_elementvalue where elementlevel<>'S' and issummary='N'
--check any sub account in summary, if so fix it
select c_elementvalue_id,value,name,elementlevel from c_elementvalue where
elementlevel='S' and issummary='Y'
--subaccount as parent, if so fix it
select node_id,parent_id from ad_treenode where ad_tree_id ='PASS-TREEID-HERE' and parent_id in
(select c_elementvalue_id from c_elementvalue where elementlevel='S')
find tree id using following query
select ad_tree_id,name,treetype from ad_tree where name ilike '%element%';
Product type mixed up
Example : Air ticket. User wrongly created this product as product type "Item" instead of "Expense" and placed Purchase Order. Now user identified error and try to update product as "Expense" .
System won't allow because stock proposal is registered .
Solution:
create or replace function fix_producttype(product_id varchar) returns void as $$
begin
update m_storage_pending set qtyreserved=0.00,qtyordered=0.00 where m_product_id= product_id;
update m_storage_detail set qtyonhand=0.00 where m_product_id= product_id;
update m_product set producttype='E',isstocked='N' where m_product_id= product_id;
end;
$$ LANGUAGE plpgsql;
select m_product_id,value,name from m_product where name ilike '%ticket%' and producttype<>'E'
select producttype(m_product_id) from m_product where name ilike '%ticket%' and producttype<>'E'