Untitled
unknown
plain_text
3 years ago
12 kB
6
Indexable
DROP TABLE IF EXISTS inventory_adjustment_notification;
CREATE TABLE inventory_adjustment_notification(
unique_key varchar,
db_name varchar,
db_port varchar,
db_host varchar,
db_user varchar,
db_password varchar,
inventory_adjustment_store_code varchar
);
INSERT INTO inventory_adjustment_notification (unique_key) VALUES ('inventory_adjustment_notification');
DROP TABLE IF EXISTS inventory_adjustment_transactions;
CREATE TABLE inventory_adjustment_transactions(
inventory_date timestamp without time zone,
qty float,
diff_qty float,
inventory_adjustment_id int,
product_code varchar,
store_code varchar,
move_id int,
move_line_id int,
product_id int,
uom_id int,
temp_location_id int,
temp_location_dest_id int,
classification_categ_id int,
dept_code_categ_id int,
product_group_categ_id int,
variety_categ_id int
);
DROP FUNCTION IF EXISTS generate_materialized_view_inventory_adjustment();
CREATE OR REPLACE FUNCTION generate_materialized_view_inventory_adjustment()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
trans_date date;
BEGIN
PERFORM dblink_connect('dbname='||NEW.db_name||' port='||NEW.db_port||' host='||NEW.db_host||' user='||NEW.db_user||' password='||NEW.db_password||'');
RAISE notice '%', NEW.inventory_adjustment_store_code;
INSERT INTO inventory_adjustment_transactions
SELECT rows.*, nextval('stock_move_id_seq') as move_id, nextval('stock_move_line_id_seq') as move_line_id, pp.id as product_id,
pt.uom_id as uom_id, 0, 0, pt.classification_categ_id, pt.dept_code_categ_id, pt.product_group_categ_id, pt.variety_categ_id
FROM
dblink('dbname='||NEW.db_name||' port='||NEW.db_port||' host='||NEW.db_host||' user='||NEW.db_user||' password='||NEW.db_password||'',
format('SELECT inventory_date::timestamp without time zone - interval ''9 hours'', qty as qty, 0 as diff_qty, id as inventory_adjustment_id, product_code, store_code
FROM inventory_adjustment_temp
WHERE store_code = %L', NEW.inventory_adjustment_store_code))
AS rows (inventory_date timestamp without time zone, qty float, diff_qty float, inventory_adjustment_id int, product_code varchar, store_code varchar)
JOIN product_product pp ON pp.default_code = rows.product_code
JOIN product_template pt ON pt.id = pp.product_tmpl_id
WHERE pp.active = True;
PERFORM dblink_disconnect();
FOR trans_date IN SELECT inventory_date FROM inventory_adjustment_transactions GROUP BY inventory_date
LOOP
with stock_quant_backdate AS(
SELECT pp.id as product_id,
sum(case when src_loc.usage in ('transit', 'internal') and
dest_loc.usage not in ('internal', 'view', 'transit')
then move.qty_done else 0 end) -
sum(case when src_loc.usage not in ('internal', 'view', 'transit') and
dest_loc.usage in ('transit', 'internal')
then move.qty_done else 0 end) as qty
FROM stock_move_line move
join product_product pp on pp.id = product_id
join stock_location src_loc on src_loc.id = move.location_id
join stock_location dest_loc on dest_loc.id = move.location_dest_id
WHERE
((move.location_id in (select id from stock_location where usage not in ('internal', 'view', 'transit')) or
move.location_id in (select id from stock_location where usage in ('transit', 'internal'))) or
(move.location_dest_id in (select id from stock_location where usage not in ('internal', 'view', 'transit')) or
move.location_id in (select id from stock_location where usage in ('transit', 'internal')))) and
move.state = 'done' and date <= (trans_date::date - interval '9 hours')
GROUP BY
pp.id,
pp.default_code
),
stock_quant_diff AS(
SELECT adj.product_id, coalesce(adj.qty, 0) - coalesce(quant.qty, 0) as qty
FROM inventory_adjustment_transactions adj
FULL OUTER JOIN stock_quant_backdate quant ON quant.product_id = adj.product_id
)
UPDATE inventory_adjustment_transactions adj SET diff_qty = coalesce(quant.qty, 0)
FROM stock_quant_diff quant WHERE quant.product_id = adj.product_id ;
END LOOP;
PERFORM inventory_adjustment_main_process(NEW.inventory_adjustment_store_code);
RETURN NEW;
END;
$$;
DROP FUNCTION IF EXISTS mapping_stock_move_inventory_adjustment(integer, integer, integer, varchar, varchar);
CREATE OR REPLACE FUNCTION public.mapping_stock_move_inventory_adjustment(location_id integer,
location_dest_id integer,
picking_type_id integer,
unique_key character varying,
store_code_name character varying)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE inventory_adjustment_transactions SET temp_location_id = location_id, temp_location_dest_id = location_dest_id;
INSERT INTO stock_move(id, name, sequence, priority, date, company_id,
date_expected, product_id, product_qty, product_uom_qty, product_uom,
location_id, location_dest_id, note, state,
price_unit, origin, procure_method, scrapped, picking_type_id,
classification_categ_id, dept_code_categ_id, product_group_categ_id, variety_categ_id,
create_uid, create_date, write_uid, write_date, middleware_id
)
SELECT move_id, '' as name, 1, 1, inventory_date::timestamp, 1,
inventory_date::timestamp, rec.product_id, rec.qty, rec.qty, rec.uom_id,
location_id, location_dest_id, '', 'done',
0, 'INV Adjustment', 'make_to_stock', False, picking_type_id,
classification_categ_id, dept_code_categ_id, product_group_categ_id, variety_categ_id,
1, now(), 1, now(), inventory_adjustment_id
FROM inventory_adjustment_transactions rec
WHERE store_code = store_code_name;
END;
$$;
DROP FUNCTION IF EXISTS update_stock_quant_inventory_adjustment;
CREATE OR REPLACE FUNCTION public.update_stock_quant_inventory_adjustment()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE stock_quant sq SET quantity = quantity - sml.qty, write_uid = 1, write_date = now()
FROM (SELECT sum(qty) as qty, product_id, temp_location_id FROM inventory_adjustment_transactions sml GROUP BY product_id, temp_location_id) AS sml
WHERE sq.product_id = sml.product_id AND
sq.location_id = sml.temp_location_id;
INSERT INTO stock_quant
(product_id, company_id, location_id, quantity, reserved_quantity,
classification_categ_id, dept_code_categ_id, product_group_categ_id, variety_categ_id,
create_uid, create_date, write_uid, write_date)
SELECT dt.product_id, 1, dt.temp_location_id, -1 * sum(dt.qty), 0,
dt.classification_categ_id, dt.dept_code_categ_id, dt.product_group_categ_id, dt.variety_categ_id,
1, NOW(), 1, NOW()
FROM inventory_adjustment_transactions dt LEFT JOIN stock_quant sq ON sq.product_id = dt.product_id AND sq.location_id = dt.temp_location_id
WHERE sq.id IS NULL
GROUP BY
dt.product_id,
dt.temp_location_id,
dt.classification_categ_id,
dt.dept_code_categ_id,
dt.product_group_categ_id,
dt.variety_categ_id;
END;
$$;
DROP FUNCTION IF EXISTS mapping_stock_move_line_inventory_adjustment(integer, integer, varchar, varchar);
CREATE OR REPLACE FUNCTION public.mapping_stock_move_line_inventory_adjustment(location_id integer,
location_dest_id integer,
unique_key character varying,
store_code_name character varying)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO stock_move_line(id, move_id, company_id, product_id, product_uom_id,
product_qty, product_uom_qty, qty_done, date, location_id,
location_dest_id, state, reference, update_quant,
classification_categ_id, dept_code_categ_id, product_group_categ_id, variety_categ_id,
create_uid, create_date, write_uid, write_date, middleware_id, transaction_code
)
SELECT move_line_id, move_id, 1, rec.product_id, rec.uom_id,
0, 0, rec.qty, inventory_date::timestamp,
location_id, location_dest_id, 'done', 'INV Adjustment', True,
classification_categ_id, dept_code_categ_id, product_group_categ_id, variety_categ_id,
1, now(), 1, now(), inventory_adjustment_id, 1
FROM inventory_adjustment_transactions rec
WHERE store_code = store_code_name;
END;
$$;
DROP FUNCTION IF EXISTS inventory_adjustment_main_process(varchar);
CREATE OR REPLACE FUNCTION public.inventory_adjustment_main_process(store_code_name varchar)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
operation_type_rec record;
unique_key varchar := to_char(now(), 'YYYYMMDDHHMMSS');
BEGIN
SELECT spt.id as picking_type_id, CONCAT('ir_sequence_', sequence_id::varchar) as seq_name, sequence_id, seq.prefix, seq.suffix, padding,
spt.default_location_src_id as location_src_id,
spt.default_location_dest_id as location_dest_id
FROM stock_warehouse sw
INNER JOIN stock_picking_type spt on spt.id = sw.inventory_adjustment_picking_type_id
INNER JOIN ir_sequence seq ON seq.id = spt.sequence_id
WHERE sw.store_code = store_code_name
INTO operation_type_rec;
--RAISE notice 'Create stock picking';
--PERFORM mapping_stock_picking_inventory_adjustment(operation_type_rec.location_src_id,
-- operation_type_rec.location_dest_id,
-- operation_type_rec.picking_type_id,
-- unique_key,
-- store_code_name);
RAISE notice 'Create stock move';
PERFORM mapping_stock_move_inventory_adjustment(operation_type_rec.location_src_id,
operation_type_rec.location_dest_id,
operation_type_rec.picking_type_id,
unique_key,
store_code_name);
RAISE notice 'Create stock move line';
PERFORM mapping_stock_move_line_inventory_adjustment(operation_type_rec.location_src_id,
operation_type_rec.location_dest_id,
unique_key,
store_code_name);
RAISE notice 'update stock quant';
PERFORM update_stock_quant_inventory_adjustment();
END;
$$;
DROP TRIGGER IF EXISTS trigger_inventory_adjustment_integration ON inventory_adjustment_notification CASCADE;
CREATE TRIGGER trigger_inventory_adjustment_integration
AFTER UPDATE ON inventory_adjustment_notification
FOR EACH ROW
EXECUTE FUNCTION generate_materialized_view_inventory_adjustment();
Editor is loading...