v11_check_stock_valuations.sql

DO
$do$
declare
	v_product_product_id int;
	v_product_category_id int;
    v_product_name varchar;
	v_stock_valuation_account int;
	v_company_id int;
	v_stock_qty_with_date float;
	v_stock_value_with_date float;
	v_stock_value_without_date float;

begin
	/*create table*/
	  DROP TABLE IF EXISTS STOCK_VALUE_DIFFS;
      CREATE TABLE STOCK_VALUE_DIFFS
        (
            ID SERIAL,
            PRODUCT_PRODUCT_ID int,
            PRODUCT_NAME varchar,
            PRODUCT_CATEGORY int,
            COMPANY_ID int,
            STOCK_VALUATION_ACCOUNT int,
            STOCK_QTY_WITH_DATE float,
            STOCK_VALUE_WITH_DATE float,
            STOCK_VALUE_WITHOUT_DATE float,
            IS_VALID bool
        );
-- Find all products with the following configuration:
   -- stockable product
   -- fifo
   -- real price
	for    v_product_product_id, v_product_category_id, v_product_name, v_stock_valuation_account, v_company_id in 
	select pp.id pp_id, pc.id pc_id, pt.name, 
	      coalesce(p1.value_account, p1bis.value_account_default) account, 
	      coalesce(p1.company_id, p1bis.company_id)
	      --,p1.value_account, p1bis.value_account_default  
	from product_product pp 
	join product_template pt on pt.id = pp.product_tmpl_id
	join product_category pc on pc.id = pt.categ_id
	left join (SELECT CAST(split_part(res_id, ',', 2) as INTEGER) pc_id,
			  CAST(split_part(value_reference, ',', 2) as INTEGER) value_account,* 
			  from ir_property ip_account where name = 'property_stock_valuation_account_id' )p1 on (p1.pc_id = pc.id and pt.company_id= p1.company_id)
	left join (SELECT CAST(split_part(value_reference, ',', 2) as INTEGER) value_account_default,* 
			  from ir_property ip_account where name = 'property_stock_valuation_account_id' and res_id is NULL order by id limit 1 )p1bis on (pt.company_id= p1bis.company_id)
	join (SELECT CAST(split_part(res_id, ',', 2) as INTEGER) pc_id,
			  CAST(split_part(value_reference, ',', 2) as INTEGER) value_account,* 
			  from ir_property ip_account where name = 'property_cost_method'  and value_text = 'fifo' )p2 on (p2.pc_id = pc.id and pt.company_id= p2.company_id)
	join (SELECT CAST(split_part(res_id, ',', 2) as INTEGER) pc_id,
			  CAST(split_part(value_reference, ',', 2) as INTEGER) value_account,* 
			  from ir_property ip_account where name = 'property_valuation'  and value_text = 'real_time' )p3 on (p3.pc_id = pc.id and pt.company_id= p3.company_id)
	order by 1
	loop 
		raise notice 'processing product %',v_product_product_id;  

		-- GENERATES THE QTY/VALUE FOR A PRODUCT IN A COMPANY AT DATE
			SELECT sum(quantity) sum_quantity, sum(aml.debit)-sum(aml.credit) sum_value
			     FROM account_move_line AS aml
			    WHERE aml.product_id =v_product_product_id and aml.account_id=v_stock_valuation_account and company_id =v_company_id
			 GROUP BY aml.product_id 
			 into v_stock_qty_with_date, v_stock_value_with_date
			;

		-- GENERATES THE QTY/VALUE FOR A PRODUCT IN A COMPANY WITHOUT DATE 
			select sum(remaining_value)
			from (
			SELECT stock_move.remaining_qty, stock_move.remaining_value 
			FROM "stock_location" as "stock_move__location_id","stock_location" as "stock_move__location_dest_id","stock_move" 
			LEFT JOIN "stock_picking" as "stock_move__picking_id" ON ("stock_move"."picking_id" = "stock_move__picking_id"."id") 
			WHERE 
			("stock_move"."location_dest_id"="stock_move__location_dest_id"."id" AND "stock_move"."location_id"="stock_move__location_id"."id") AND 
				(
					(
					("stock_move"."product_id" =v_product_product_id)  AND  ("stock_move"."state" = 'done')
					)  
				and
					(
						("stock_move__location_id"."company_id" IS NULL   AND ("stock_move__location_dest_id"."company_id" = v_company_id))  
						OR  
						(("stock_move__location_id"."company_id" = v_company_id)  AND "stock_move__location_dest_id"."company_id" IS NULL )
					)
				) 
			ORDER BY "stock_move__picking_id"."priority" DESC,"stock_move__picking_id"."date" ASC,"stock_move__picking_id"."id" DESC,"stock_move"."sequence" ,"stock_move"."id"
			)A
			into v_stock_value_without_date;

			insert into STOCK_VALUE_DIFFS ( PRODUCT_PRODUCT_ID ,PRODUCT_NAME ,PRODUCT_CATEGORY, COMPANY_ID, STOCK_VALUATION_ACCOUNT,
							STOCK_QTY_WITH_DATE, STOCK_VALUE_WITH_DATE, STOCK_VALUE_WITHOUT_DATE, IS_VALID ) 
				values (v_product_product_id, 
					v_product_name, 
					v_product_category_id,
					v_company_id,
					v_stock_valuation_account,
					v_stock_qty_with_date,
					v_stock_value_with_date, 
					v_stock_value_without_date,
					    (abs(coalesce(v_stock_value_with_date,0) -coalesce(v_stock_value_without_date,0)) <0.00001));
     END LOOP;
end;
$do$;




--   SEE RESULT
/*
select count(*) from STOCK_VALUE_DIFFS where is_valid = false
select * from STOCK_VALUE_DIFFS where is_valid = false
*/