Exit of Products in ADempiere

-- select * from vw_salidas_productos sp where sp.docstatus = 'CO';

-- drop view vw_salidas_productos;

create view vw_salidas_productos
as
select
    s.m_inventory_id as id_salida,
    s.ad_client_id,
    s.ad_org_id,
    s.isactive,
    s.created,
    s.createdby,
    s.updated,
    s.updatedby,
    s.documentno as num_salida,
    s.description as descripcion,
    w.m_warehouse_id as id_almacen,
    w.name as almacen,
    s.movementdate as fecha_salida,
    s.posted,
    s.processed,
    s.processing,
    s.updateqty,
    s.generatelist,
    s.isapproved,
    s.docstatus,
    s.docaction,
    s.approvalamt,
    dt.c_doctype_id as id_tipo_documento,
    dt.name as tipo_documento,
    s.processedon,
    cast(to_timestamp(s.processedon/1000) as date) as fecha_proceso
from m_inventory s
    join m_warehouse w
    on s.m_warehouse_id = w.m_warehouse_id
    join c_doctype dt
    on s.c_doctype_id = dt.c_doctype_id
where s.ad_client_id = 1000001
    and (select count(ds.m_inventoryline_id) from m_inventoryline ds
    where ds.m_inventory_id = s.m_inventory_id
    and ds.qtyinternaluse <> 0) > 0;

Sales Order Detail in Adempiere

-- select * from vw_detalle_ordenes_ventas dov limit 100;

drop view if exists vw_detalle_ordenes_ventas;

create view vw_detalle_ordenes_ventas
as
select
    ov.*,
    ol.line as linea,
    p.m_product_id as id_producto,
    p.value as cod_producto,
    p.name as producto,
    c.c_charge_id as id_cargo,
    c.name as cargo,
    ia.m_attributesetinstance_id as id_atributos,
    ia.description as atributos,
    u.c_uom_id as id_unidad,
    u.name as unidad_medida,
    ol.qtyentered as cantidad_entrada,
    ol.qtyordered as cantidad_ordenada,
    ol.qtyreserved as cantidad_reservada,
    ol.priceentered as precio_entrada,
    ol.priceactual as precio_actual,
    ol.pricelist as precio_lista,
    ol.linenetamt as neto_linea
from vw_ordenes_ventas ov
    join c_orderline ol
    on ov.id_orden = ol.c_order_id
    left outer join m_product p
    on ol.m_product_id = p.m_product_id
    left outer join c_uom u
    on ol.c_uom_id = u.c_uom_id
    left outer join c_charge c
    on ol.c_charge_id = c.c_charge_id
    left outer join m_attributesetinstance ia
    on ol.m_attributesetinstance_id = ia.m_attributesetinstance_id;

Completed Sales Orders in Adempiere

-- select * from vw_ordenes_ventas_completadas ov limit 100;

drop view if exists vw_ordenes_ventas_completadas;

create view vw_ordenes_ventas_completadas
as
select
    og.ad_org_id as id_organizacion,
    og.name as organizacion,
    o.c_order_id as id_orden,
    o.documentno as num_orden,
    o.dateordered as fecha_orden,
    o.description as descripcion,
    bp.c_bpartner_id as id_tercero,
    bp.value as cod_tercero,
    bp.name as tercero,
    l.c_location_id as id_direccion,
    coalesce(l.address1, l.address2) as direccion,
    u.ad_user_id as id_contacto,
    u.name as contacto,
    w.m_warehouse_id as id_almacen,
    w.name as almacen,
    pl.m_pricelist_id as id_lista_precio,
    pl.name as lista_precio,
    c.c_currency_id as id_moneda,
    c.iso_code as moneda,
    pt.c_paymentterm_id as id_termino_pago,
    pt.name as termino_pago,
    o.totallines as total_lineas,
    o.grandtotal as gran_total,
    o.posted as esta_contabilizado,
    o.processedon as completado,
    cast(to_timestamp(o.processedon/1000) as date) as fecha_completado
from c_order o
    join ad_org og
    on o.ad_org_id = og.ad_org_id
    join c_bpartner bp
    on o.c_bpartner_id = bp.c_bpartner_id
    left outer join c_bpartner_location bl
    on bp.c_bpartner_id = bl.c_bpartner_id
    left outer join c_location l
    on bl.c_location_id = l.c_location_id
    join ad_user u
    on o.ad_user_id = u.ad_user_id
    join m_warehouse w
    on o.m_warehouse_id = w.m_warehouse_id
    join m_pricelist pl
    on o.m_pricelist_id = pl.m_pricelist_id
    join c_currency c
    on o.c_currency_id = c.c_currency_id
    left outer join c_paymentterm pt
    on o.c_paymentterm_id = pt.c_paymentterm_id
where o.ad_client_id = 1000001
    and o.docstatus = 'CO'
    and o.issotrx = 'Y'
    and o.c_doctypetarget_id = 1000039;

List of products in Adempiere

-- select * from vw_listado_productos;

drop view if exists vw_listado_productos;

create view vw_listado_productos
as
select
    p.m_product_id as id_producto,
    p.value as cod_producto,
    p.name as producto,
    p.description as descripcion,
    pc.m_product_category_id as id_categoria,
    pc.name as categoria_producto,
    um.c_uom_id as id_unidad,
    um.name as unidad_medida,
    p.producttype as id_tipo,
    case p.producttype
        when 'E' then 'Tipo de Gasto'
        when 'I' then 'Artículo'
        when 'R' then 'Recurso'
        when 'S' then 'Servicio'
    end as tipo_producto,
    w.m_warehouse_id as id_almacen,
    w.name as almacen,
    l.m_locator_id as id_ubicacion,
    l.name as ubicacion,
    a.m_attributeset_id as id_conjunto,
    a.description as conjunto_atributo
from m_product p
    left outer join m_product_category pc
    on p.m_product_category_id = pc.m_product_category_id
    join c_uom um
    on p.c_uom_id = um.c_uom_id
    left outer join m_locator l
    on p.m_locator_id = l.m_locator_id
    left outer join m_warehouse w
    on l.m_warehouse_id = w.m_warehouse_id
    left outer join m_attributeset a
    on p.m_attributeset_id = a.m_attributeset_id
where p.ad_client_id = 1000001
    and p.isactive = 'Y';

Notas descuadradas en Adempiere

-- Type: notas_descuadradas

-- DROP TYPE notas_descuadradas;

CREATE TYPE notas_descuadradas AS
   (id_lote numeric(10),
    num_lote character varying,
    id_nota numeric(10),
    num_nota character varying,
    debito numeric(10,2),
    credito numeric(10,2),
    diferencia numeric(10,2));

-- Function: rep_notas_descuadradas(numeric, numeric)

-- DROP FUNCTION rep_notas_descuadradas(numeric, numeric);

CREATE OR REPLACE FUNCTION rep_notas_descuadradas(id_periodo numeric, id_comprobante numeric)
  RETURNS SETOF notas_descuadradas AS
$BODY$
declare
 resultado notas_descuadradas%rowtype;
begin

 drop table if exists tmp_notas_descuadradas;

 create table tmp_notas_descuadradas as
 select
 k.id_lote,
 k.num_lote,
 k.id_nota,
 k.num_nota,
 k.debito,
 k.credito,
 (k.debito - k.credito) diferencia
 from
 (
 select
 gb.gl_journalbatch_id id_lote,
 gb.documentno num_lote,
 g.gl_journal_id id_nota,
 g.documentno num_nota,
 sum(gl.amtacctdr) debito,
 sum(gl.amtacctcr) credito
 from gl_journalbatch gb
 join gl_journal g
 on gb.gl_journalbatch_id = g.gl_journalbatch_id
 join gl_journalline gl
 on g.gl_journal_id = gl.gl_journal_id
 where gb.ad_client_id = 1000001
 --and gb.docstatus = 'CO'
 and gb.c_period_id = case when id_periodo = 0 then gb.c_period_id else id_periodo end
 and gb.gl_journalbatch_id = case when id_comprobante = 0 then gb.gl_journalbatch_id else id_comprobante end
 group by
 gb.gl_journalbatch_id,
 gb.documentno,
 g.gl_journal_id,
 g.documentno
 ) k
 where k.debito <> k.credito;

 for resultado in execute
 'select
        t.id_lote,
        t.num_lote,
        t.id_nota,
        t.num_nota,
        t.debito,
        t.credito,
        t.diferencia
    from tmp_notas_descuadradas t;'
 loop
 return next resultado;
 end loop;

end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

Asientos Contables de las Facturas por Pagar

-- select cf.* from vw_asientos_factura cf;

drop view if exists vw_asientos_factura;

create view vw_asientos_factura as
with cte_asientos_factura as
(
select
    f.c_invoice_id as id_factura,
    f.documentno as cod_factura,
    f.description as desc_factura,
    f.dateacct as fecha_contable,
    bp.c_bpartner_id as id_proveedor,
    bp.value as cod_proveedor,
    bp.name as proveedor,
    o.ad_org_id as id_centro,
    o.value as cod_centro,
    o.name as centro_costo,
    coalesce(ev.c_elementvalue_id, ev2.c_elementvalue_id) as id_cuenta_mov,
    coalesce(ev.value, ev2.value) as cod_cuenta_mov,
    coalesce(ev.name, ev2.name) as cuenta_contable_mov,
    sum(df.linetotalamt) as monto,
    ev3.c_elementvalue_id as id_cta_proveedor,
    ev3.value as cod_cta_proveedor,
    ev3.name as cta_contable_proveedor
from c_invoice f
    join ad_org o
    on f.ad_org_id = o.ad_org_id
    join c_invoiceline df
    on f.c_invoice_id = df.c_invoice_id
    left outer join m_product p
    on df.m_product_id = p.m_product_id
    left outer join m_product_acct pc
    on p.m_product_id = pc.m_product_id
    left outer join c_validcombination vc
    on pc.p_asset_acct = vc.c_validcombination_id
    left outer join c_elementvalue ev
    on vc.account_id = ev.c_elementvalue_id
    left outer join c_charge c
    on df.c_charge_id = c.c_charge_id
    left outer join c_charge_acct ca
    on c.c_charge_id = ca.c_charge_id
    left outer join c_validcombination vc2
    on ca.ch_expense_acct = vc2.c_validcombination_id
    left outer join c_elementvalue ev2
    on vc2.account_id = ev2.c_elementvalue_id
    join c_bpartner bp
    on f.c_bpartner_id = bp.c_bpartner_id
    join c_bp_vendor_acct va
    on bp.c_bpartner_id = va.c_bpartner_id
    join c_bp_employee_acct ea
    on bp.c_bpartner_id = ea.c_bpartner_id
    join c_validcombination vc3
    on vc3.c_validcombination_id =
    case
        when bp.isvendor = 'Y' then
            va.v_liability_acct
        when bp.isemployee = 'Y' then
            ea.e_expense_acct
    end
    join c_elementvalue ev3
    on vc3.account_id = ev3.c_elementvalue_id
where f.ad_client_id = 1000002
    and f.docstatus = 'CO'
group by
    f.c_invoice_id,
    f.documentno,
    f.description,
    f.dateacct,
    bp.c_bpartner_id,
    bp.value,
    bp.name,
    o.ad_org_id,
    o.value,
    o.name,
    ev.c_elementvalue_id,
    ev.value,
    ev.name,
    ev2.c_elementvalue_id,
    ev2.value,
    ev2.name,
    ev3.c_elementvalue_id,
    ev3.value,
    ev3.name
)
select
    t.id_factura,
    t.cod_factura,
    t.id_centro,
    t.cod_centro,
    t.centro_costo,
    t.cod_cuenta_mov,
    t.cuenta_contable_mov,
    t.fecha_contable,
    t.monto,
    'D' as tipo_mov,
    t.desc_factura,
    t.id_proveedor,
    t.cod_proveedor,
    t.proveedor
from cte_asientos_factura t
union all
select
    t.id_factura,
    t.cod_factura,
    t.id_centro,
    t.cod_centro,
    t.centro_costo,
    t.cod_cta_proveedor,
    t.cta_contable_proveedor,
    t.fecha_contable,
    t.monto,
    'C' as tipo_mov,
    t.desc_factura,
    t.id_proveedor,
    t.cod_proveedor,
    t.proveedor
from cte_asientos_factura t
order by
    8 desc, -- fecha_contable
    1; -- id_factura

Comprobante de diario en Adempiere

drop view if exists vw_comprobante_diario;

create view vw_comprobante_diario as
select
    o.ad_org_id as id_centro,
    o.value as cod_centro,
    o.name as centro_costo,
    ev.c_elementvalue_id as id_cuenta,
    ev.value as cod_cuenta,
    ev.name as cuenta,
    ev.accountsign as id_naturaleza,
    case ev.accountsign
        when 'N' then 'Natural'
        when 'D' then 'Debito'
        when 'C' then 'Credito'
    end as naturaleza,
    ev.accounttype as id_tipo_cuenta,
    case ev.accounttype
        when 'A' then 'Activo'
        when 'L' then 'Pasivo'
        when 'O' then 'Capital'
        when 'R' then 'Ingresos'
        when 'E' then 'Gastos'
        when 'M' then 'Memo'
    end as tipo_cuenta,
    f.fact_acct_id as id_contable,
    f.dateacct as fecha_contable,
    p.c_period_id as id_periodo,
    p.name as periodo,
    gb.gl_journalbatch_id as id_lote,
    gb.documentno as num_lote,
    gb.description as desc_lote,
    g.gl_journal_id as id_nota,
    g.documentno as num_nota,
    g.description as desc_nota,
    gl.gl_journalline_id as id_linea,
    gl."line" as num_linea,
    gl.description as desc_linea,
    f.amtacctdr as debito,
    f.amtacctcr as credito
from fact_acct f
    join ad_org o
    on f.ad_org_id = o.ad_org_id
    join c_elementvalue ev
    on f.account_id = ev.c_elementvalue_id
    join c_period p
    on f.c_period_id = p.c_period_id
    join gl_journal g
    on f.record_id = g.gl_journal_id
    join gl_journalbatch gb
    on g.gl_journalbatch_id = gb.gl_journalbatch_id
    join gl_journalline gl
    on f.line_id = gl.gl_journalline_id
where f.ad_client_id = 1000001
    and f.ad_table_id = 224;

select
    cd.*
from vw_comprobante_diario cd
order by
    cd.id_periodo,
    cd.id_lote,
    cd.id_nota,
    cd.num_linea
limit 1000;

Saldos Actuales y Anteriores de Cuentas Contables

WITH FACT_ACCT AS
  ( SELECT v.nat AS naturaleza,
           f.account_id AS id_cuenta,
           f.dateacct AS fecha_contable,
           f.c_period_id,
           COALESCE(SUM(F.AMTACCTDR), 0) AS DEBITO,
           COALESCE(SUM(F.AMTACCTCR), 0) AS CREDITO
   FROM fact_acct AS f
   JOIN usr_v_vcc AS v ON f.account_id = v.c_elementvalue_id
   WHERE f.ad_client_id = 1000001
     AND f.ad_table_id = 224
     AND f.line_id > 0
   GROUP BY v.nat,
            f.account_id,
            f.dateacct,
            f.c_period_id),
     Movimientos_Periodo AS
  (SELECT id_cuenta,
          SUM(DEBITO) AS DEBITOS,
          SUM(CREDITO) AS CREDITOS
   FROM FACT_ACCT
   WHERE fecha_contable BETWEEN '20121101' AND '20121130'
   GROUP BY id_cuenta),
     Movimientos_Saldos AS
  (SELECT MP.id_cuenta AS CuentaID,
          MP.DEBITOS,
          MP.CREDITOS,
          (CASE WHEN U.Nat = 'D' THEN 
          MP.DEBITOS - MP.CREDITOS 
          WHEN U.Nat = 'C' THEN 
          MP.CREDITOS - MP.DEBITOS 
          ELSE 0.000000000 END) AS SALDO
   FROM Movimientos_Periodo AS MP
   INNER JOIN usr_v_vcc AS U ON U.c_elementvalue_id=MP.id_cuenta) ,
     Movimientos_Periodo_Anterior AS
  (SELECT id_cuenta,
          SUM(DEBITO) AS DEBITOS,
          SUM(CREDITO) AS CREDITOS
   FROM FACT_ACCT
   WHERE fecha_contable BETWEEN '20121001' AND '20121031'
   GROUP BY id_cuenta) ,
     Movimientos_Saldos_Anterior AS
  (SELECT MPA.id_cuenta AS CuentaID,
          MPA.DEBITOS,
          MPA.CREDITOS,
          (CASE WHEN U.Nat = 'D' THEN 
          MPA.DEBITOS - MPA.CREDITOS 
          WHEN U.Nat = 'C' THEN 
          MPA.CREDITOS - MPA.DEBITOS 
          ELSE 0.000000000 END) AS SALDO_ANTERIOR
   FROM Movimientos_Periodo_Anterior AS MPA
   INNER JOIN usr_v_vcc AS U ON U.c_elementvalue_id=MPA.id_cuenta)
SELECT MS.CuentaID,
       MS.DEBITOS,
       MS.CREDITOS,
       MS.SALDO,
       MSA.SALDO_ANTERIOR
FROM Movimientos_Saldos AS MS
INNER JOIN Movimientos_Saldos_Anterior AS MSA ON MS.CuentaID=MSA.CuentaID

Fixed Asset Code

-- select * from vw_codigo_activo ca;

CREATE VIEW vw_codigo_activo AS
SELECT a.u_activo_id AS id_activo,
       a.codigo AS cod_activo,
       a.nombre AS activo,
       ga.u_grupoactivo_id AS id_grupo,
       ga.codigo AS cod_grupo,
       ga.nombre AS grupo,
       o.ad_org_id AS id_sucursal,
       o.name AS sucursal,
       cs.codigo AS cod_sucursal,
       row_number() over(partition BY cast(cs.codigo AS integer)
                         ORDER BY cast(cs.codigo AS integer) ASC, cast(ga.codigo AS integer) ASC, a.u_activo_id ASC) AS consecutivo,
       ga.codigo || cs.codigo || row_number() over(partition BY cast(cs.codigo AS integer)
                                                   ORDER BY cast(cs.codigo AS integer) ASC, cast(ga.codigo AS integer) ASC, a.u_activo_id ASC) AS nuevo_codigo
FROM u_activo a
JOIN u_grupoactivo ga ON a.u_grupoactivo_id = ga.u_grupoactivo_id
JOIN ad_org o ON ga.ad_org_id = o.ad_org_id
JOIN nic_codigo_sucursal cs ON o.ad_org_id = cs.ad_org_id
WHERE a.ad_client_id = 1000001
  AND a.isactive = 'Y'
ORDER BY cast(cs.codigo AS integer) ASC, cast(ga.codigo AS integer) ASC, a.u_activo_id ASC;

Fixed Assets

-- select * from vw_activos_fijos af;
 -- drop view vw_activos_fijos

CREATE VIEW vw_activos_fijos AS
SELECT a.ad_client_id,
       o.ad_org_id AS id_centro_costo,
       o.name AS centro_costo,
       a.u_activo_id AS id_activo,
       a.codigo AS cod_activo,
       a.nombre AS activo,
       a.descripcion,
       a.serialno,
       ma.nic_marca_id AS id_marca,
       ma.name AS marca,
       mo.nic_modelo_id AS id_modelo,
       mo.name AS modelo,
       ga.u_grupoactivo_id AS id_grupo,
       ga.codigo AS cod_grupo,
       ga.nombre AS grupo,
       og.ad_org_id AS grupo_org_id,
       og.name AS grupo_org,
       vc.c_validcombination_id AS id_combinacion,
       vc.combination AS combinacion,
       vc.description AS desc_combinacion,
       ca.u_claseactivo_id AS id_clase,
       ca.codigo AS cod_clase,
       ca.nombre AS clase,
       ca.descripcion AS desc_clase,
       a.area_terreno,
       a.registro_catastral,
       a.registro_publico,
       a.num_factura,
       bp.c_bpartner_id AS id_comprador,
       bp.value AS cod_comprador,
       bp.name AS comprador,
       a.num_chasis,
       a.num_placa,
       a.bueno,
       a.regular,
       a.depreciado,
       a.baja,
       a.fechacompra,
       a.fechabaja,
       a.totalmeses,
       a.valoractual,
       a.valordepreciado,
       a.valorinicial,
       a.periodofinal,
       a.periodoinicial
FROM u_activo a
JOIN ad_org o ON a.ad_org_id = o.ad_org_id
LEFT OUTER JOIN nic_marca ma ON a.nic_marca_id = ma.nic_marca_id
LEFT OUTER JOIN nic_modelo mo ON a.nic_modelo_id = mo.nic_modelo_id
JOIN u_grupoactivo ga ON a.u_grupoactivo_id = ga.u_grupoactivo_id
JOIN ad_org og ON ga.ad_org_id = og.ad_org_id
LEFT OUTER JOIN c_validcombination vc ON ga.c_validcombination_id = vc.c_validcombination_id
JOIN u_claseactivo ca ON ga.u_claseactivo_id = ca.u_claseactivo_id
LEFT OUTER JOIN c_bpartner bp ON a.c_bpartner_id = bp.c_bpartner_id
WHERE a.ad_client_id = 1000001
  AND a.isactive = 'Y';

Asset History

-- select * from vw_historial_activo ha;
 -- drop view vw_historial_activo;

CREATE VIEW vw_historial_activo AS
SELECT a.ad_client_id,
       o.ad_org_id AS id_centro_costo,
       o.name AS centro_costo,
       a.u_activo_id AS id_activo,
       a.codigo AS cod_activo,
       a.nombre AS activo,
       a.descripcion,
       a.serialno,
       ma.nic_marca_id AS id_marca,
       ma.name AS marca,
       mo.nic_modelo_id AS id_modelo,
       mo.name AS modelo,
       ga.u_grupoactivo_id AS id_grupo,
       ga.codigo AS cod_grupo,
       ga.nombre AS grupo,
       og.ad_org_id AS grupo_org_id,
       og.name AS grupo_org,
       vc.c_validcombination_id AS id_combinacion,
       vc.combination AS combinacion,
       vc.description AS desc_combinacion,
       vc.ad_org_id AS combinacion_org_id,
       vc.account_id AS combinacion_cta_id,
       ca.u_claseactivo_id AS id_clase,
       ca.codigo AS cod_clase,
       ca.nombre AS clase,
       ca.descripcion AS desc_clase,
       a.area_terreno,
       a.registro_catastral,
       a.registro_publico,
       a.num_factura,
       bp.c_bpartner_id AS id_comprador,
       bp.value AS cod_comprador,
       bp.name AS comprador,
       a.num_chasis,
       a.num_placa,
       a.bueno,
       a.regular,
       a.depreciado,
       a.baja,
       a.fechacompra,
       a.fechabaja,
       a.totalmeses,
       a.valoractual,
       a.valordepreciado,
       a.valorinicial,
       a.periodofinal,
       a.periodoinicial,
       ha.u_historialactivo_id AS id_historial_activo,
       e.c_bpartner_id AS id_empleado,
       e.value AS cod_empleado,
       e.name AS empleado,
       u.u_ubicacion_id AS id_ubicacion,
       u.codigo AS cod_ubicacion,
       u.nombre AS ubicacion,
       ed.u_edificio_id AS id_edificio,
       ed.codigo AS cod_edificio,
       ed.nombre AS edificio
FROM u_activo a
JOIN ad_org o ON a.ad_org_id = o.ad_org_id
LEFT OUTER JOIN nic_marca ma ON a.nic_marca_id = ma.nic_marca_id
LEFT OUTER JOIN nic_modelo mo ON a.nic_modelo_id = mo.nic_modelo_id
JOIN u_grupoactivo ga ON a.u_grupoactivo_id = ga.u_grupoactivo_id
JOIN ad_org og ON ga.ad_org_id = og.ad_org_id
LEFT OUTER JOIN c_validcombination vc ON ga.c_validcombination_id = vc.c_validcombination_id
JOIN u_claseactivo ca ON ga.u_claseactivo_id = ca.u_claseactivo_id
LEFT OUTER JOIN c_bpartner bp ON a.c_bpartner_id = bp.c_bpartner_id
LEFT OUTER JOIN u_historialactivo ha ON a.u_activo_id = ha.u_activo_id
LEFT OUTER JOIN c_bpartner e ON ha.c_bpartner_id = e.c_bpartner_id
LEFT OUTER JOIN u_ubicacion u ON ha.u_ubicacion_id = u.u_ubicacion_id
LEFT OUTER JOIN u_edificio ed ON u.u_edificio_id = ed.u_edificio_id
WHERE a.ad_client_id = 1000001
  AND a.isactive = 'Y';

Accounting View Post

CREATE VIEW "adempiere"."vw_nota_contabilidad" ( ad_client_id,
                                                 ad_org_id,
                                                 isactive,
                                                 created,
                                                 createdby,
                                                 updated,
                                                 updatedby,
                                                 gl_journalbatch_id,
                                                 num_lote,
                                                 docstatus,
                                                 docaction,
                                                 processed,
                                                 fecha_lote,
                                                 fecha_conta_lote,
                                                 c_period_id,
                                                 lote_cr,
                                                 lote_dr,
                                                 gl_journal_id,
                                                 num_nota,
                                                 posted,
                                                 nota_cr,
                                                 nota_dr,
                                                 fecha_nota,
                                                 fecha_conta_nota,
                                                 linea,
                                                 credito,
                                                 debito,
                                                 id_cuenta,
                                                 cod_cuenta,
                                                 cuenta) AS
SELECT gb.ad_client_id,
       gb.ad_org_id,
       gb.isactive,
       gb.created,
       gb.createdby,
       gb.updated,
       gb.updatedby,
       gb.gl_journalbatch_id,
       gb.documentno AS num_lote,
       gb.docstatus,
       gb.docaction,
       gb.processed,
       gb.datedoc AS fecha_lote,
       gb.dateacct AS fecha_conta_lote,
       gb.c_period_id,
       gb.totalcr AS lote_cr,
       gb.totaldr AS lote_dr,
       g.gl_journal_id,
       g.documentno AS num_nota,
       g.posted,
       g.totalcr AS nota_cr,
       g.totaldr AS nota_dr,
       g.datedoc AS fecha_nota,
       g.dateacct AS fecha_conta_nota,
       gl.line AS linea,
       gl.amtacctcr AS credito,
       gl.amtacctdr AS debito,
       ev.c_elementvalue_id AS id_cuenta,
       ev.value AS cod_cuenta,
       ev.name AS cuenta
FROM gl_journalbatch gb
JOIN c_period p ON gb.c_period_id = p.c_period_id
JOIN gl_journal g ON gb.gl_journalbatch_id = g.gl_journalbatch_id
JOIN gl_journalline gl ON g.gl_journal_id = gl.gl_journal_id
JOIN c_validcombination vc ON gl.c_validcombination_id = vc.c_validcombination_id
JOIN c_elementvalue ev ON vc.account_id = ev.c_elementvalue_id
WHERE gb.ad_client_id = 1000001::numeric;

Movement Type in Adempiere

-- select * from vw_tipos_mov_materiales tm;

drop view if exists vw_tipos_mov_materiales;

create view vw_tipos_mov_materiales
as
select
    'C-'::varchar(2) as codigo,
    'Embarque a Clientes'::varchar as nombre
union all
select
    'C+'::varchar(2) as codigo,
    'Devoluciones de Cliente'::varchar as nombre
union all
select
    'I-'::varchar(2) as codigo,
    'Salida de Inventario'::varchar as nombre
union all
select
    'I+'::varchar(2) as codigo,
    'Entrada de Inventario'::varchar as nombre
union all
select
    'M-'::varchar(2) as codigo,
    'Movimiento Desde'::varchar as nombre
union all
select
    'M+'::varchar(2) as codigo,
    'Movimiento Hacia'::varchar as nombre
union all
select
    'P-'::varchar(2) as codigo,
    'Producción -'::varchar as nombre
union all
select
    'P+'::varchar(2) as codigo,
    'Producción +'::varchar as nombre
union all
select
    'V-'::varchar(2) as codigo,
    'Devoluciones al Proveedor'::varchar as nombre
union all
select
    'V+'::varchar(2) as codigo,
    'Recibos del Proveedor'::varchar as nombre
union all
select
    'W-'::varchar(2) as codigo,
    'Orden de Trabajo -'::varchar as nombre
union all
select
    'W+'::varchar(2) as codigo,
    'Orden de Trabajo +'::varchar as nombre;