first in first out stock inventory costing methods using postgreSQL function

Fifo box

create table first called tbl_pembelian_dtl and it's sequence (auto increment)

id	id_tr	barang	harga_beli	qty	jumlah	op_inp	tgl_inp	qty_out	qty_sisa	merk
1	1	item name	5000	10	10	1	2019-03-15	0	0	merk

CREATE SEQUENCE public.tbl_pembelian_dtl_id_seq;
CREATE TABLE public.tbl_pembelian_dtl
(
id integer NOT NULL DEFAULT nextval('tbl_pembelian_dtl_id_seq'::regclass),
id_tr integer NOT NULL,
barang integer,
harga_beli double precision,
qty numeric(6,2),
jumlah numeric(14,2),
op_inp integer,
tgl_inp timestamp without time zone,
qty_out numeric(6,2),
qty_sisa numeric(6,2),
merk character varying(20) COLLATE pg_catalog."default",
CONSTRAINT tbl_pembelian_dtl_pkey PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;

then create table called tbl_penjualan_dtl and it's sequence (auto increment)

id	id_tr	barang	harga_jual	qty	jumlah	op_inp	tgl_inp	harga_beli	id_dtl_beli

CREATE SEQUENCE public.tbl_penjualan_dtl_id_seq;
CREATE TABLE public.tbl_penjualan_dtl
(
id integer NOT NULL DEFAULT nextval('tbl_penjualan_dtl_id_seq'::regclass),
id_tr integer NOT NULL,
barang integer,
harga_jual double precision,
qty numeric(8,2),
jumlah numeric(16,2),
op_inp integer,
tgl_inp timestamp without time zone,
harga_beli numeric(10,2),
id_dtl_beli integer,
CONSTRAINT tbl_penjualan_dtl_pkey PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;

after that create function called ins_penjualan_dtl

CREATE OR REPLACE FUNCTION ins_penjualan_dtl(_tr integer, _brg integer, _hj numeric, _qty numeric, _op integer)
RETURNS void AS $$
BEGIN
  DECLARE JKELUAR numeric(8,2);
  DECLARE KODE int;
  DECLARE IDS INT;
  DECLARE SISA numeric(6,2);
  DECLARE v_hb numeric(10,2);
  begin
    JKELUAR := _qty;
    IDS := (SELECT id FROM tbl_pembelian_dtl WHERE barang=_brg and qty_sisa>0 order by id limit 1 offset 0);
    SISA := (SELECT qty_sisa FROM tbl_pembelian_dtl WHERE id=IDS);
    v_hb := (SELECT harga_beli FROM tbl_pembelian_dtl WHERE id=IDS);
    KODE := _brg;
    LOOP
    IF(SISA>JKELUAR) THEN
      UPDATE tbl_pembelian_dtl SET qty_sisa=SISA-JKELUAR , qty_out=qty_out+JKELUAR WHERE id=IDS;
      insert into tbl_penjualan_dtl (id_tr,barang,harga_jual,qty,jumlah, id_dtl_beli, harga_beli, op_inp, tgl_inp) values (_tr, _brg, _hj, JKELUAR, _hj * JKELUAR, IDS, v_hb, _op, current_timestamp);
      JKELUAR := 0;
    elsIF (SISA       UPDATE tbl_pembelian_dtl SET qty_sisa=0, qty_out = qty_out + SISA WHERE id=IDS;
      insert into tbl_penjualan_dtl (id_tr,barang,harga_jual,qty,jumlah, id_dtl_beli, harga_beli, op_inp, tgl_inp) values (_tr, _brg, _hj, SISA, _hj * SISA, IDS, v_hb, _op, current_timestamp);
      JKELUAR := JKELUAR - SISA;
      IDS := (SELECT id FROM tbl_pembelian_dtl WHERE barang = KODE and qty_sisa>0 order by id LIMIT 1 offset 0);
      SISA := (SELECT qty_sisa FROM tbl_pembelian_dtl WHERE id = IDS);
      v_hb := (SELECT harga_beli FROM tbl_pembelian_dtl WHERE id=IDS);
    elsIF(SISA=JKELUAR) THEN
      UPDATE tbl_pembelian_dtl SET qty_sisa=0, qty_out = qty_out+JKELUAR WHERE id=IDS;
      insert into tbl_penjualan_dtl (id_tr,barang,harga_jual,qty,jumlah, id_dtl_beli, harga_beli, op_inp, tgl_inp) values (_tr, _brg, _hj, SISA, _hj * SISA, IDS, v_hb, _op, current_timestamp);
      JKELUAR := 0;
    else
      JKELUAR := 0;
    END IF;
    EXIT WHEN JKELUAR=0 ;
    END LOOP;
  end;
END;
$$ LANGUAGE plpgsql;

How it works?

• Make sure tbl_pembelian_dtl filled first with stock (buy items)
• execute function ins_penjualan_dtl (sell items)
• check tbl_penjualan_dtl to make sure the data is inserted (sell item)

Share this post