This time we will discuss about the computerization of inventory in the warehouse (location) is done automatically at the time the transaction is entered into a purchase transaction table, into the sales transaction table, as well as mutations in the transaction table movement of goods between warehouses (locations). The assumption of this discussion is about the stock of drugs in the hospital pharmacy, the location of the drug in some places.
For the above, the following is the syntax of SQL that is run on Microsoft SQL Server 2000 Personal Edition.
CREATE TABLE [dbo].[Master_Obat] (
[Kode_Kelompok] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Kode_Obat] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Nama_Obat] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Satuan] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Harga_beli] [money] NULL ,
[Harga_Jual] [money] NULL ,
[Kode_Pabrik] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Master_Obat] ADD
CONSTRAINT [PK_Master_Obat] PRIMARY KEY CLUSTERED
(
[Kode_Obat]
) ON [PRIMARY]
GO
---------------------------------------------------
Master_obat tables are used to store master data medicine overall. In this table has a field that is drug code, drug name, unit, purchase price, sales price, and several other fields as needed.
---------------------------------------------------
CREATE TABLE [dbo].[Gudang] (
[kode_gudang] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Nama_gudang] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Is_GudangUtama] [bit] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Master_Obat] ADD
CONSTRAINT [PK_Master_Obat] PRIMARY KEY CLUSTERED
(
[Kode_gudang]
) ON [PRIMARY]
GO
---------------------------------------------------
CREATE TABLE [dbo].[StokGudang] (
[kode_gudang] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[kode_obat] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[stok] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Master_Obat] ADD
CONSTRAINT [PK_Master_Obat] PRIMARY KEY CLUSTERED
(
[Kode_gudang]
[Kode_obat]
) ON [PRIMARY]
GO
----------------------------------------------
Both of the above table is used to store information drug stocks at each location.
----------------------------------------------
Next is a purchase transaction table as shown below. At the time of the purchase transaction information is stored in two tables, namely, purchase table and table purchase details.
CREATE TABLE [dbo].[Pembelian] (
[Nomor_Pembelian] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Nomor_Faktur] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tanggal_Pembelian] [datetime] NULL ,
[pemasok] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Master_Obat] ADD
CONSTRAINT [PK_Master_Obat] PRIMARY KEY CLUSTERED
(
[Nomor_Pembelian]
) ON [PRIMARY]
GO
-------------------------------------------------------
CREATE TABLE [dbo].[Pembelian_Detail] (
[Nomor_Pembelian] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Kode_Obat] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[quantity] [int] NOT NULL ,
[Harga_beli] [money] NULL ,
[harga_jual] [money] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Pembelian_Detail] ADD
CONSTRAINT [PK_Pembelian_Detail] PRIMARY KEY CLUSTERED
(
[Nomor_Pembelian],
[Kode_Obat]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Pembelian_Detail] ADD
CONSTRAINT [FK_Pembelian_Detail_Pembelian] FOREIGN KEY
(
[Nomor_Pembelian]
) REFERENCES [dbo].[Pembelian] (
[Nomor_Pembelian]
)
GO
Drug stocks counting mechanism through trigger on the table purchase details, as described below. Trigger occurs when the INSERT, DELETE, and UPDATE.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER tbl_PembelianDetail_Trigger1
ON dbo.Pembelian_Detail
FOR INSERT
AS
declare @no_beli varchar(12)
declare @kod_obt nvarchar(8)
declare @qty int
declare @stk int
declare @hrg_beli money
declare @hrg_jual money
SELECT @no_beli = ins. nomor_pembelian,
@kod_obt = ins.kode_obat,
@qty = ins.quantity,
@hrg_beli = ins.harga_beli,
@hrg_jual = ins.harga_jual
FROM inserted ins
SELECT @stk = Stok
FROM StokGudang
WHERE (kode_gudang = (SELECT Kode_Gudang
FROM Gudang
WHERE Is_gudangUtama = 1))
AND (kode_obat = @kod_obt)
UPDATE StokGudang
SET stok = @stk + @qty
WHERE (kode_gudang = (SELECT Kode_Gudang
FROM Gudang
WHERE Is_gudangUtama = 1))
AND (kode_obat = @kod_obt)
UPDATE master_obat
SET harga_beli = @hrg_beli, harga_jual = @hrg_jual
WHERE kode_obat = @kod_obt
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER tbl_PembelianDetail_Trigger2
ON dbo.Pembelian_Detail
FOR DELETE
AS
declare @no_beli varchar(12)
declare @kod_obt nvarchar(8)
declare @qty int
declare @stk int
SELECT @no_beli = del. nomor_pembelian,
@kod_obt = del.kode_obat,
@qty = del.quantity
FROM deleted del
SELECT @stk = Stok
FROM StokGudang
WHERE (kode_gudang = (SELECT Kode_Gudang
FROM Gudang
WHERE Is_gudangUtama = 1))
AND (kode_obat = @kod_obt)
UPDATE StokGudang
SET stok = @stk - @qty
WHERE (kode_gudang = (SELECT Kode_Gudang
FROM Gudang
WHERE Is_gudangUtama = 1))
AND (kode_obat = @kod_obt)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER tbl_PembelianDetail_Trigger3
ON dbo.Pembelian_Detail
FOR UPDATE
AS
declare @no_beli varchar(12)
declare @kod_obtIns nvarchar(8)
declare @kod_obtDel nvarchar(8)
declare @qtyIns int
declare @qtyDel int
declare @stk int
declare @hrg_beliIns money
declare @hrg_jualIns money
declare @hrg_beliDel money
declare @hrg_jualDel money
SELECT @no_beli = ins. nomor_pembelian,
@kod_obtDel = del.kode_obat,
@kod_obtIns = ins.kode_obat,
@qtyDel = del.quantity,
@qtyIns = ins.quantity,
@hrg_beliDel = del.harga_beli,
@hrg_beliIns = ins.harga_beli,
@hrg_jualDel = del.harga_jual,
@hrg_jualIns = ins.harga_jual
FROM deleted del, inserted ins
SELECT @stk = Stok
FROM StokGudang
WHERE (kode_gudang = (SELECT Kode_Gudang
FROM Gudang
WHERE Is_gudangUtama = 1))
AND (kode_obat = @kod_obtDel)
UPDATE StokGudang
SET stok = @stk - @qtyDel
WHERE (kode_gudang = (SELECT Kode_Gudang
FROM Gudang
WHERE Is_gudangUtama = 1))
AND (kode_obat = @kod_obtDel)
SELECT @stk = Stok
FROM StokGudang
WHERE (kode_gudang = (SELECT Kode_Gudang
FROM Gudang
WHERE Is_gudangUtama = 1))
AND (kode_obat = @kod_obtIns)
UPDATE StokGudang
SET stok = @stk + @qtyIns
WHERE (kode_gudang = (SELECT Kode_Gudang
FROM Gudang
WHERE Is_gudangUtama = 1))
AND (kode_obat = @kod_obtIns)
UPDATE master_obat
SET harga_beli = @hrg_beliDel, harga_jual = @hrg_jualDel
WHERE kode_obat = @kod_obtDel
UPDATE master_obat
SET harga_beli = @hrg_beliIns, harga_jual = @hrg_jualIns
WHERE kode_obat = @kod_obtIns
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----------------------------------------------
