SHARE
TWEET

Untitled

a guest Feb 24th, 2019 68 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DO
  2. $do$
  3. declare
  4.     v_product_product_id int;
  5.     v_product_category_id int;
  6.     v_product_name varchar;
  7.     v_stock_valuation_account int;
  8.     v_company_id int;
  9.     v_stock_qty_with_date float;
  10.     v_stock_value_with_date float;
  11.     v_stock_value_without_date float;
  12.  
  13. begin
  14.     /*create table*/
  15.       DROP TABLE IF EXISTS STOCK_VALUE_DIFFS;
  16.       CREATE TABLE STOCK_VALUE_DIFFS
  17.         (
  18.             ID SERIAL,
  19.             PRODUCT_PRODUCT_ID int,
  20.             PRODUCT_NAME varchar,
  21.             PRODUCT_CATEGORY int,
  22.             COMPANY_ID int,
  23.             STOCK_VALUATION_ACCOUNT int,
  24.             STOCK_QTY_WITH_DATE float,
  25.             STOCK_VALUE_WITH_DATE float,
  26.             STOCK_VALUE_WITHOUT_DATE float,
  27.             IS_VALID bool
  28.         );
  29. -- Find all products with the following configuration:
  30.    -- stockable product
  31.    -- fifo
  32.    -- real price
  33.     for    v_product_product_id, v_product_category_id, v_product_name, v_stock_valuation_account, v_company_id in
  34.     select pp.id pp_id, pc.id pc_id, pt.name,
  35.           coalesce(p1.value_account, p1bis.value_account_default) account,
  36.           coalesce(p1.company_id, p1bis.company_id)
  37.           --,p1.value_account, p1bis.value_account_default  
  38.     from product_product pp
  39.     join product_template pt on pt.id = pp.product_tmpl_id
  40.     join product_category pc on pc.id = pt.categ_id
  41.     left join (SELECT CAST(split_part(res_id, ',', 2) as INTEGER) pc_id,
  42.               CAST(split_part(value_reference, ',', 2) as INTEGER) value_account,*
  43.               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)
  44.     left join (SELECT CAST(split_part(value_reference, ',', 2) as INTEGER) value_account_default,*
  45.               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)
  46.     join (SELECT CAST(split_part(res_id, ',', 2) as INTEGER) pc_id,
  47.               CAST(split_part(value_reference, ',', 2) as INTEGER) value_account,*
  48.               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)
  49.     join (SELECT CAST(split_part(res_id, ',', 2) as INTEGER) pc_id,
  50.               CAST(split_part(value_reference, ',', 2) as INTEGER) value_account,*
  51.               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)
  52.     order by 1
  53.     loop
  54.         raise notice 'processing product %',v_product_product_id;  
  55.  
  56.         -- GENERATES THE QTY/VALUE FOR A PRODUCT IN A COMPANY AT DATE
  57.             EXECUTE format('SELECT sum(quantity) sum_quantity, sum(aml.debit)-sum(aml.credit) sum_value
  58.                  FROM account_move_line AS aml
  59.                 WHERE aml.product_id =$1 and aml.account_id=$2 and company_id =$3  
  60.              GROUP BY aml.product_id' )
  61.              into v_stock_qty_with_date, v_stock_value_with_date
  62.              using v_product_product_id, v_stock_valuation_account, v_company_id
  63.             ;
  64.  
  65.         -- GENERATES THE QTY/VALUE FOR A PRODUCT IN A COMPANY WITHOUT DATE
  66.             EXECUTE format('select sum(remaining_value)
  67.             from (
  68.             SELECT stock_move.remaining_qty, stock_move.remaining_value
  69.             FROM "stock_location" as "stock_move__location_id","stock_location" as "stock_move__location_dest_id","stock_move"
  70.             LEFT JOIN "stock_picking" as "stock_move__picking_id" ON ("stock_move"."picking_id" = "stock_move__picking_id"."id")
  71.             WHERE
  72.             ("stock_move"."location_dest_id"="stock_move__location_dest_id"."id" AND "stock_move"."location_id"="stock_move__location_id"."id") AND
  73.                 (
  74.                     (
  75.                     ("stock_move"."product_id" =$1)  AND  ("stock_move"."state" = ''done'')
  76.                     )  
  77.                 and
  78.                     (
  79.                         ("stock_move__location_id"."company_id" IS NULL   AND ("stock_move__location_dest_id"."company_id" = $2))  
  80.                         OR  
  81.                         (("stock_move__location_id"."company_id" = $3)  AND "stock_move__location_dest_id"."company_id" IS NULL )
  82.                     )
  83.                 )
  84.             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"
  85.             )A')
  86.             into v_stock_value_without_date
  87.             using v_product_product_id, v_company_id, v_company_id;
  88.  
  89.             insert into STOCK_VALUE_DIFFS ( PRODUCT_PRODUCT_ID ,PRODUCT_NAME ,PRODUCT_CATEGORY, COMPANY_ID, STOCK_VALUATION_ACCOUNT,
  90.                             STOCK_QTY_WITH_DATE, STOCK_VALUE_WITH_DATE, STOCK_VALUE_WITHOUT_DATE, IS_VALID )
  91.                 values (v_product_product_id,
  92.                     v_product_name,
  93.                     v_product_category_id,
  94.                     v_company_id,
  95.                     v_stock_valuation_account,
  96.                     v_stock_qty_with_date,
  97.                     v_stock_value_with_date,
  98.                     v_stock_value_without_date,
  99.                         (abs(coalesce(v_stock_value_with_date,0) -coalesce(v_stock_value_without_date,0)) <0.00001));
  100.      END LOOP;
  101. end;
  102. $do$;
  103.  
  104.  
  105.  
  106.  
  107. --   SEE RESULT
  108. /*
  109. select count(*) from STOCK_VALUE_DIFFS where is_valid = false
  110. select * from STOCK_VALUE_DIFFS where is_valid = false
  111. */
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top