Reset Inventory of Product with Attributes in ADempiere

-- drop function sp_reset_inventory_with_attributes(p_fecha_corte date, p_usuario numeric(10,0));

create or replace function sp_reset_inventory_with_attributes(p_almacen numeric(10,0), p_fecha_corte date, p_usuario numeric(10,0))
returns boolean
as $$
declare
    p_inventario numeric(10,0);
    secuencia varchar;
begin
    drop table if exists tmp_inventario;

    create table tmp_inventario as
    with cte_inventario as
    (
        select
            w.m_warehouse_id as id_almacen,
            w.name as almacen,
            l.m_locator_id as id_ubicacion,
            l.value as ubicacion,
            p.m_product_id as id_producto,
            p.value as cod_producto,
            p.name as producto,
            ia.description as modelo,
            sum(coalesce(t.movementqty, 0)) cantidad
        from m_transaction t
            join m_product p
            on t.m_product_id = p.m_product_id
            join m_product_category pc
            on p.m_product_category_id = pc.m_product_category_id
            join m_locator l
            on t.m_locator_id = l.m_locator_id
            join m_warehouse w
            on l.m_warehouse_id = w.m_warehouse_id
            join m_attributesetinstance ia
            on t.m_attributesetinstance_id = ia.m_attributesetinstance_id
        where t.ad_client_id = 1000001
            and w.m_warehouse_id = p_almacen
            and t.movementdate <= p_fecha_corte
        group by
            w.m_warehouse_id,
            w.name,
            l.m_locator_id,
            l.value,
            p.m_product_id,
            p.value,
            p.name,
            ia.description
    )
    select
        ia.id_almacen,
        ia.almacen,
        ia.id_ubicacion,
        ia.ubicacion,
        ia.id_producto,
        ia.cod_producto,
        ia.producto,
        ia.modelo,
        ia.cantidad
    from cte_inventario ia
    where ia.cantidad <> 0;

    insert into m_inventory
    (
        m_inventory_id,
        ad_client_id,
        ad_org_id,
        isactive,
        created,
        createdby,
        updated,
        updatedby,
        documentno,
        description,
        m_warehouse_id,
        movementdate,
        posted,
        processed,
        processing,
        updateqty,
        generatelist,
        isapproved,
        docstatus,
        docaction,
        approvalamt,
        c_doctype_id
    )
    select
        (select max(i.m_inventory_id) from m_inventory i) + 1 as m_inventory_id,
        1000001::numeric(10,0) as ad_client_id,
        1000001::numeric(10,0) as ad_org_id,
        'Y'::char as isactive,
        now()::timestamp without time zone as created,
        p_usuario as createdby,
        now()::timestamp without time zone as updated,
        p_usuario as updatedby,
        ('AjusteInventario_' || p_fecha_corte)::varchar as documentno,
        ('Ajuste Inventario Productos con Atributos al ' || p_fecha_corte)::varchar as description,
        1000036::numeric(10,0) as m_warehouse_id,
        now()::timestamp without time zone as movementdate,
        'N'::char as posted,
        'N'::char as processed,
        'N'::char as processing,
        'N'::char as updateqty,
        'N'::char as generatelist,
        'N'::char as isapproved,
        'DR'::char(2) as docstatus,
        'CO'::char(2) as docaction,
        0::numeric as approvalamt,
        1000023::numeric(10,0) as c_doctype_id;

    p_inventario =
    (
        select max(i.m_inventory_id) from m_inventory i
        where i.documentno = ('AjusteInventario_' || p_fecha_corte)::varchar
    );

    insert into m_inventoryline
    (
        m_inventoryline_id,
        ad_client_id,
        ad_org_id,
        isactive,
        created,
        createdby,
        updated,
        updatedby,
        m_inventory_id,
        m_locator_id,
        m_product_id,
        line,
        qtybook,
        qtycount,
        m_attributesetinstance_id,
        c_charge_id,
        inventorytype,
        processed,
        qtyinternaluse,
        qtycsv
    )
    select
        ((select max(ds.m_inventoryline_id) from m_inventoryline ds) +
        row_number() over(order by t.ubicacion, t.producto, t.modelo))::numeric(10,0) as m_inventoryline_id,
        1000001::numeric(10,0) as ad_client_id,
        1000001::numeric(10,0) as ad_org_id,
        'Y'::char as isactive,
        now()::timestamp without time zone as created,
        p_usuario as createdby,
        now()::timestamp without time zone as updated,
        p_usuario as updatedby,
        p_inventario::numeric(10,0) as m_inventory_id,
        t.id_ubicacion as m_locator_id,
        t.id_producto as m_product_id,
        (row_number() over(order by t.ubicacion, t.producto, t.modelo) * 10)::numeric(10,0) as line,
        t.cantidad as qtybook,
        0::numeric as qtycount,
        (select max(ia.m_attributesetinstance_id) from m_attributesetinstance ia
        where ia.description = t.modelo)::numeric(10,0) as m_attributesetinstance_id,
        1000033::numeric(10,0) as c_charge_id,
        'D'::char as inventorytype,
        'N'::char as processed,
        0::numeric as qtyinternaluse,
        0::numeric as qtycsv
    from tmp_inventario t;

    secuencia = (select update_sequences());

    return true;
end;
$$ language plpgsql;