新手问题,关于OE6.1rc中POS模块订单确认后对应产品库存没有变化的问题[已解决]
-
现附上解决办法, 如果你还在使用6.1RC1的话可以尝试, 6.1正式版已经解决了
解决办法:
第一步: 没有修改代码
第二步: 加了触发器, 即判断当通过POS触摸屏下单后,自动触发生成相应的出入库记录,这样产品的库存数据就减少了.
先看看相关表<br /><br />/*<br />pos触发器实现- 当pos触摸屏下单后,自动出库.<br />*/<br /><br />TABLE pos_order (<br /> id integer NOT NULL,<br /> create_uid integer,<br /> create_date timestamp without time zone,<br /> write_date timestamp without time zone,<br /> write_uid integer,<br /> sale_journal integer NOT NULL,<br /> account_move integer,<br /> date_order timestamp without time zone,<br /> partner_id integer,<br /> nb_print integer,<br /> user_id integer,<br /> name character varying(64) NOT NULL,<br /> invoice_id integer,<br /> company_id integer NOT NULL,<br /> note text,<br /> state character varying,<br /> shop_id integer NOT NULL,<br /> pricelist_id integer NOT NULL,<br /> picking_id integer<br />);<br /><br /><br /><br />TABLE pos_order_line (<br /> id integer NOT NULL,<br /> create_uid integer,<br /> create_date timestamp without time zone,<br /> write_date timestamp without time zone,<br /> write_uid integer,<br /> notice character varying(128),<br /> product_id integer NOT NULL,<br /> order_id integer,<br /> price_unit numeric,<br /> price_subtotal numeric,<br /> company_id integer NOT NULL,<br /> price_subtotal_incl numeric,<br /> qty numeric,<br /> discount numeric,<br /> name character varying(32) NOT NULL<br />);<br /><br />TABLE stock_picking (<br /> id integer NOT NULL,<br /> create_uid integer,<br /> create_date timestamp without time zone,<br /> write_date timestamp without time zone,<br /> write_uid integer,<br /> origin character varying(64),<br /> address_id integer,<br /> date_done timestamp without time zone,<br /> min_date timestamp without time zone,<br /> date timestamp without time zone,<br /> location_id integer,<br /> stock_journal_id integer,<br /> backorder_id integer,<br /> name character varying(64),<br /> partner_id integer,<br /> move_type character varying NOT NULL,<br /> company_id integer NOT NULL,<br /> invoice_state character varying NOT NULL,<br /> note text,<br /> state character varying,<br /> location_dest_id integer,<br /> max_date timestamp without time zone,<br /> auto_picking boolean,<br /> type character varying NOT NULL,<br /> sale_id integer,<br /> purchase_id integer<br />);<br /><br />TABLE stock_move (<br /> id integer NOT NULL,<br /> create_uid integer,<br /> create_date timestamp without time zone,<br /> write_date timestamp without time zone,<br /> write_uid integer,<br /> origin character varying(64),<br /> product_uos_qty numeric,<br /> address_id integer,<br /> product_uom integer NOT NULL,<br /> price_unit numeric,<br /> date_expected timestamp without time zone NOT NULL,<br /> date timestamp without time zone NOT NULL,<br /> prodlot_id integer,<br /> move_dest_id integer,<br /> product_qty numeric NOT NULL,<br /> product_uos integer,<br /> location_id integer NOT NULL,<br /> name character varying(250) NOT NULL,<br /> note text,<br /> product_id integer NOT NULL,<br /> auto_validate boolean,<br /> price_currency_id integer,<br /> partner_id integer,<br /> company_id integer NOT NULL,<br /> picking_id integer,<br /> priority character varying,<br /> state character varying,<br /> location_dest_id integer NOT NULL,<br /> tracking_id integer,<br /> product_packaging integer,<br /> sale_line_id integer,<br /> purchase_line_id integer,<br /> production_id integer<br />);<br /><br />[code]<br /><br /><br />这是第二遍写,重新码字. -
触发器 建立在表 pos_order上
会影响 表stock_picking ,插入相关记录
create or replace function pos_order_auto_picking_for_touch()
returns trigger as
$pos_order_auto_picking_for_touch$
DECLARE
pos_order_id integer;
pos_order_create_uid integer;
pos_order_create_date timestamp;
pos_order_name character varying(64);
pos_order_company_id integer;
pos_order_shop_id integer;
rec record;
pos_order_stock_picking_picking_id integer;
pos_order_stock_picking_stock_journal_id integer;
pos_order_stock_picking_name character varying(64);
pos_order_stock_picking_name_seq integer;
BEGIN
--stock_picking
IF (TG_OP = 'INSERT') THEN
pos_order_id := NEW.id;
pos_order_create_uid := NEW.create_uid;
pos_order_create_date := NEW.create_date;
pos_order_name := NEW.name;
pos_order_company_id := NEW.company_id;
pos_order_shop_id := NEW.shop_id;
/
length(pos_order_name) < 15 : pos backend
length(pos_order_name) > 15 : pos
/
IF (length(pos_order_name) > 0) THEN
BEGIN
--pos_order_stock_picking_name
SELECT nextval('ir_sequence_016') into pos_order_stock_picking_name_seq;
select 'OUT/' || to_char( pos_order_stock_picking_name_seq ,'FM09999') into pos_order_stock_picking_name;
--pos_order_stock_picking_stock_journal_id
select id from stock_journal into pos_order_stock_picking_stock_journal_id where user_id = pos_order_company_id;
update pos_order set note = pos_order_stock_picking_name where id = pos_order_id;
--update pos_order_line set notice = pos_order_stock_picking_name where order_id = pos_order_id;
INSERT INTO stock_picking (create_uid, create_date, write_date, write_uid, origin, address_id, date_done, min_date, date, location_id, stock_journal_id, backorder_id, name, partner_id, move_type, company_id, invoice_state, note, state, location_dest_id, max_date, auto_picking, type, sale_id, purchase_id)
VALUES (pos_order_create_uid, pos_order_create_date, pos_order_create_date, pos_order_create_uid, pos_order_name, NULL, pos_order_create_date, pos_order_create_date, pos_order_create_date, NULL, pos_order_stock_picking_stock_journal_id, NULL, pos_order_stock_picking_name, NULL, 'direct', pos_order_company_id, 'none', '', 'done', NULL, pos_order_create_date, true, 'out', NULL, NULL);
select id from stock_picking into pos_order_stock_picking_picking_id order by id desc limit 1;
update pos_order set picking_id = pos_order_stock_picking_picking_id where id = pos_order_id;
END;
END IF;
END IF;
RETURN NULL;
END;
$pos_order_auto_picking_for_touch$ LANGUAGE 'plpgsql' VOLATILE;
--正式创建触发器trigger --INSERT
create trigger pos_order_auto_picking_for_touch
AFTER insert or update
on public.pos_order
for each row
execute procedure pos_order_auto_picking_for_touch(); -
触发器二 建立在表pos_order_line上
影响表pos_order ,stock_move,或更新或插入相关记录.
create or replace function pos_order_line_auto_picking_for_touch()
returns trigger as
$pos_order_line_auto_picking_for_touch$
DECLARE
pos_order_line_id integer;
pos_order_line_create_uid integer;
pos_order_line_create_date timestamp;
pos_order_line_product_id integer;
pos_order_line_order_id integer;
pos_order_line_price_unit numeric;
pos_order_line_price_subtotal numeric;
pos_order_line_company_id integer ;
pos_order_line_price_subtotal_incl numeric;
pos_order_line_qty numeric;
pos_order_line_name character varying(32);
rec record;
pos_order_line_pos_order_id_name character varying(64);
pos_order_line_pos_order_id_picking_id integer;
pos_order_line_product_id_product_uom_id integer;
pos_order_line_product_id_location_id integer;
pos_order_line_product_id_location_dest_id integer;
pos_order_line_product_id_location_dest_id_tmp integer;
BEGIN
--stock_picking
IF (TG_OP = 'INSERT') THEN
pos_order_line_id := NEW.id;
pos_order_line_create_uid := NEW.create_uid;
pos_order_line_create_date := NEW.create_date;
pos_order_line_product_id := NEW.product_id;
pos_order_line_order_id := NEW.order_id;
pos_order_line_price_unit := NEW.price_unit;
pos_order_line_price_subtotal := NEW.price_subtotal;
pos_order_line_company_id := NEW.company_id;
pos_order_line_price_subtotal_incl := NEW.price_subtotal_incl;
pos_order_line_qty := NEW.qty;
pos_order_line_name := NEW.name;
--pos_order_line_pos_order_id_name
select name from pos_order into pos_order_line_pos_order_id_name where id = pos_order_line_order_id;
IF (length(pos_order_line_pos_order_id_name) > 0) THEN
BEGIN
--pos_order_line_pos_order_id_picking_id
select picking_id from pos_order into pos_order_line_pos_order_id_picking_id where id = pos_order_line_order_id limit 1;
select uom_id from product_template into pos_order_line_product_id_product_uom_id where id = pos_order_line_product_id limit 1;
--stock_warehouse
select lot_stock_id from stock_warehouse into pos_order_line_product_id_location_id where company_id = pos_order_line_company_id limit 1;
--stock_location
select lot_output_id from stock_warehouse into pos_order_line_product_id_location_dest_id_tmp where company_id = pos_order_line_company_id limit 1;
if(pos_order_line_product_id_location_dest_id_tmp is not null) then
begin
select chained_location_id from stock_location into pos_order_line_product_id_location_dest_id where id = pos_order_line_product_id_location_dest_id_tmp limit 1;
end;
end if;
if(pos_order_line_product_id_location_dest_id is null) then
begin
select id from stock_location into pos_order_line_product_id_location_dest_id where usage='customer' limit 1;
end;
end if;
INSERT INTO stock_move (create_uid, create_date, write_date, write_uid, origin, product_uos_qty, address_id, product_uom, price_unit, date_expected , date , prodlot_id, move_dest_id, product_qty, product_uos, location_id, name , note , product_id, auto_validate, price_currency_id, partner_id, company_id, picking_id, priority , state , location_dest_id, tracking_id, product_packaging, sale_line_id, purchase_line_id, production_id)
VALUES (pos_order_line_create_uid, pos_order_line_create_date, pos_order_line_create_date, pos_order_line_create_uid, pos_order_line_pos_order_id_name, pos_order_line_qty, NULL, pos_order_line_product_id_product_uom_id, NULL, pos_order_line_create_date, pos_order_line_create_date, NULL, NULL, pos_order_line_qty, pos_order_line_product_id_product_uom_id, pos_order_line_product_id_location_id, pos_order_line_name, NULL, pos_order_line_product_id, false, NULL, NULL, pos_order_line_company_id, pos_order_line_pos_order_id_picking_id, '1', 'done', pos_order_line_product_id_location_dest_id, NULL, NULL, NULL, NULL, NULL);
END;
END IF;
END IF;
RETURN NULL;
END;
$pos_order_line_auto_picking_for_touch$ LANGUAGE 'plpgsql' VOLATILE;
--正式创建触发器trigger --INSERT
create trigger pos_order_line_auto_picking_for_touch
AFTER insert or update
on public.pos_order_line
for each row
execute procedure pos_order_line_auto_picking_for_touch();
