Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- EXECUTE format('SELECT sum(quantity) sum_quantity, sum(aml.debit)-sum(aml.credit) sum_value
- FROM account_move_line AS aml
- WHERE aml.product_id =$1 and aml.account_id=$2 and company_id =$3
- GROUP BY aml.product_id' )
- into v_stock_qty_with_date, v_stock_value_with_date
- using v_product_product_id, v_stock_valuation_account, v_company_id
- ;
- -- GENERATES THE QTY/VALUE FOR A PRODUCT IN A COMPANY WITHOUT DATE
- EXECUTE format('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" =$1) AND ("stock_move"."state" = ''done'')
- )
- and
- (
- ("stock_move__location_id"."company_id" IS NULL AND ("stock_move__location_dest_id"."company_id" = $2))
- OR
- (("stock_move__location_id"."company_id" = $3) 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
- using v_product_product_id, v_company_id, v_company_id;
- 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
- */
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement