Untitled

mail@pastecode.io avatar
unknown
plain_text
2 years ago
12 kB
3
Indexable
Never
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();