miércoles, 5 de junio de 2019

Copy Inventory Lines in iDempiere


-- select * from fnt_copy_inventory_lines(1000120, 1000124, 1000315);

create or replace function fnt_copy_inventory_lines(p_inventory_from_id numeric(10,0), p_inventory_to_id numeric(10,0), p_user_id numeric(10,0))
returns boolean
as $$
declare
    v_client_id numeric(10,0);
    v_org_id numeric(10,0);
    v_isactive char;

    v_sequence varchar;
begin
    select
        s.ad_client_id,
        s.ad_org_id,
        s.isactive
    into
        v_client_id,
        v_org_id,
        v_isactive
    from m_inventory s
    where s.m_inventory_id = p_inventory_to_id;
   
    insert into adempiere.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,
        description,
        m_attributesetinstance_id,
        c_charge_id,
        inventorytype,
        processed,
        qtyinternaluse,
        qtycsv
    )
    select
        (select max(il.m_inventoryline_id) from m_inventoryline il) +
        row_number() over(order by ds.m_inventory_id, ds.line) as m_inventoryline_id,
        v_client_id as ad_client_id,
        v_org_id as ad_org_id,
        v_isactive as isactive,
        now()::timestamp without time zone as created,
        p_user_id as createdby,
        now()::timestamp without time zone as updated,
        p_user_id as updatedby,
        p_inventory_to_id as m_inventory_id,
        ds.m_locator_id,
        ds.m_product_id,
        ds.line,
        0::numeric as qtybook,
        0::numeric as qtycount,
        ds.description,
        ds.m_attributesetinstance_id,
        ds.c_charge_id,
        ds.inventorytype,
        'N'::char as processed,
        ds.qtyinternaluse,
        0::numeric as qtycsv
    from m_inventoryline ds
    where ds.m_inventory_id = p_inventory_from_id;

    v_sequence = (select update_sequences());

    return true;
end;
$$ language plpgsql;

No hay comentarios:

Publicar un comentario