Nama Entity
Aliases
Kamus Data Attribute Deskripsi
MsCabang
MKT01
KdCabang
MKT01
MKT02
Tipe data
Null
M ultivalued
Kode cabang
Char (5)
No
No
NamaCabang
Nama cabang
Char (30)
No
No
MKT03
Alamat
Alamat cabang
Text
No
No
MKT04
Telp
Telepon cabang
Char (13 )
No
No
MKT05
Fax
Fax cabang
Char (13)
Yes
No
MKT06
NPWP
NPWP
Char (25)
No
No
MKT07
TglCbg
Tanggal berdiri
Date
No
No
MsStaff
MKT01
KdStaff
Kode staff
Char (3)
No
No
MKT02
MKT02
Nama
Nama staff
Char (30)
No
No
MKT03
Sex
Jenis kelamin
Char (1)
No
No
MKT04
TglLhr
Tanggal lahir
Date
No
No
MKT05
Alamat
Alamat staff
Text
No
No
MKT06
Telp
Telepon staff
Char (13)
Yes
No
MKT07
KdCabang
Kode cabang
Char (5)
No
No
MKT08
KdJnsStaff
Kode jenis staff
Char (2)
No
No
MKT09
UserId
Char(1 5)
No
No
MKT10
Pass
Char(8 )
No
No
L1
MsJenisStaff
MKT01
KdJnsStaff
Kode jenis staff
Char(2 No )
No
MKT03
MKT02
JenisStaff
Posisi staff
Char(2 0)
No
No
MsBarang
MKT01
KdBarang
Kode barang
Char (5)
No
No
MKT04
MKT02
NamaBarang
Nama Barang
Char (25)
No
No
MKT03
Desk
Deskripsi
Text
Yes
No
MKT04
Qty
Quantity
Integer
No
No
MKT05
Qtymin
Quantity min
Integer
No
No
MKT06
KdJnsBarang
Depatemen
Char(2 No )
No
MsJenisBarang
MKT01
KdJnsBarang
Kode jenis barang
Char(2 No )
No
MKT05
MKT02
JnsBarang
Jenis barang
Char(3 0)
No
No
MKT03
Desk
Deskripsi
Text
No
No
TrHargaJual
MKT01
KdHarga
Kode harga
Char (8)
No
No
MKT06
MKT02
Harga
Harga
Decim al
No
No
MKT03
KdBarang
Barang
Char (5)
No
No
MKT04
TglIn
Tanggal mulai
Date
No
No
MKT05
TglOut
Tanggal selesai
Date
No
No
MKT06
Status
Status harga
Char (1)
No
No
MsSupplier
MKT01
KdSupplier
Kode supplier
Char (3)
No
No
MKT07
MKT02
Nama
Nama supplier
Char (30)
No
No
L2
MKT03
Alamat
Alamat supplier
Text
No
No
MKT04
Deskripsi
Deskripsi
Text
Yes
No
TrJualHeader
MKT01
KdJual
Kode penjualan
Char (12)
No
No
MKT08
MKT02
TglPenjualan
Tanggal penjualan
Date
No
No
MKT03
KdStaff
Kasir yang melayani
Char (3)
No
No
TrJualDetail
MKT01
KdJual
Kode penjualan
Char (12)
No
No
MKT09
MKT02
KdBarang
Barang yang dijual
Char (5)
No
No
MKT03
Qty
Jumlah barang
Integer
No
No
MKT04
KdHarga
Harga jual
Char(8 No )
No
TrBeliHeader
MKT01
KdBeli
Kode pembelian Char (12)
No
No
MKT10
MKT02
TglPembelian
Tanggal pembelian
Date
No
No
MTK03
KdStaff
Char(3 )
No
No
MKT04
KdSupplier
Char (3)
No
No
MKT05
JnsPembayara Jenis n pembayaran
Char (6)
No
No
MKT06
Jatuhtempo
Jatuh tempo kredit
Date
Yes
No
TrBeliDetail
MKT01
KdBeli
Kode pembelian Char (12)
No
No
MKT11
MKT02
KdBarang
Barang yang dibeli
Char (5)
No
No
MKT03
Qty
Jumlah barang
Integer
No
No
Nama supplier
L3
MKT04
Harga
Harga beli barang
Decim al
No
No
TrRtrJualHeader
MKT01
KdRtrJual
Kode penjualan
Char (12)
No
No
MKT12
MKT03
KdStaff
Kode Staff
Char(3 )
No
No
MKT02
TglRtrJual
Tanggal penjualan
Date
No
No
MKT04
KdJual
Kode faktur jual Char (12)
No
No
TrRtrDetailHead er
MKT01
KdRtrJual
Kode penjualan
Char(1 No 2)
No
MKT13
MKT02
KdBarang
Barang yang dijual
Char (5)
No
No
MKT03
Qty
Jumlah barang
Integer
No
No
TrRtrBeliHeader
MKT01
KdRtrBeli
Kode retur pembelian
Char (12)
No
No
MKT14
MKT03
KdStaff
Kode Staff
Char (3)
MKT02
TglRetur
Tanggal retur
Date
No
No
MKT04
KdBeli
Kode faktur beli
TrRtrBeliDetail
MKT01
KdRtrBeli
Kode retur pembelian
Char (12)
No
No
MKT15
MKT02
KdBarang
Barang yang dibeli
Char (5)
No
No
MKT03
Qty
Jumlah barang
Integer
No
No
TrHlgHeader
MKT01
KdHilang
Kode kehilangan
Char (12)
No
No
MKT16
MKT03
KdStaff
Kode Staff
Char(3 )
No
No
MKT02
Tgl
Tanggal kehilangan
Date
No
No
L4
TrHlgDetail
MKT01
KdHilang
Kode kehilangan
Char (12)
No
No
MKT17
MKT02
KdBarang
Barang hilang
Char (5)
No
No
MKT03
Qty
Jumlah hilang
Integer
No
No
TrPembayaran
MKT02
KodePembaya Kodebayar ran
Char(1 No 2)
No
MKT34
MKT01
KodePembeli an
kodebayar
Char(1 No 2)
No
MKT03
Tanggal
Tanggalbayar
Date
No
No
MKT04
Bayar
Jumlah bayar
Decim al
No
No
TelpCust
MKT01
Kodesuplier
Kode supplier
Char(3 No )
No
MKT33
MKT02
Kdtelp
Kodetelp
Char(3 )
No
No
MKT03
Notelp
Nomor
Char(1 No 3)
No
MKT04
Cp
Contact person
Char(3 No 0)
No
S tored Procedure Laporan Tutup Kasir CREATE PROCEDURE HTUTUPKASIR @ID CHAR(3), @DT DATETIM E AS select A.MKT02, C.MKT02, SUM (A.MKT03), SUM (d.mkt02*A.MKT03),e.MKT02 From MKT09 a, MKT08 b, MKT04 C, M KT06 D, MKT05 E where a.M KT01=B.MKT01 AND A.M KT02=C.MKT01 AND D.M KT01=A.M KT04 AND B.MKT03=@ID AND B.MKT02 = @DT and c.mkt06=e.mkt01 GROUP BY A.MKT02, C.MKT02, e.M KT02 ORDER BY A.MKT02 GO Laporan Persediaan CREATE PROCEDURE HPERSEDIAAN AS select A.MKT01, A.M KT02, A.MKT04,SUM (B.M KT04*B.MKT03)/SUM (B.MKT03) AS A, SUM (B.MKT04*B.MKT03) AS B, F.M KT02 as v, G.MKT02 as g From MKT04 A, M KT11 B, MKT10 C, MKT02 E, M KT01 F, MKT05 G where L5
A.M KT01=B.MKT02 AND C.MKT01=B.M KT01 AND C.MKT03=E.M KT01 AND E.M KT07=F.M KT01 AND A.M KT06=G.MKT01 GROUP BY A.MKT01, A.M KT02, A.MKT04, F.MKT02, G.M KT02 ORDER BY A.MKT01 GO Laporan Penjualan CREATE PROCEDURE HLAPORANJUAL @TGL datetime AS DROP table temp5 select A.MKT01 as A, A.MKT02 as B, A.M KT04 as C,SUM (B.MKT04*B.MKT03)/SUM (B.MKT03) AS D, SUM (B.M KT04*B.MKT03) AS E, F.M KT02 as F, G.MKT02 as G into temp5 From MKT04 A, M KT11 B, MKT10 C, MKT02 E, M KT01 F, MKT05 G where A.M KT01=B.MKT02 AND C.MKT01=B.M KT01 AND C.MKT03=E.M KT01 AND E.M KT07=F.M KT01 AND A.M KT06=G.MKT01 GROUP BY A.MKT01, A.M KT02, A.MKT04, F.MKT02, G.M KT02 ORDER BY A.MKT01 DROP Table Temp2 SELECT C.MKT01 AS A, C.M KT02 AS B, SUM (B.MKT03)AS C,SUM (G.MKT02*B.M KT03)/SUM (B.M KT03) AS D,SUM (G.MKT02*B.M KT03) AS E, E.M KT02 AS F, F.MKT02 AS G, mAX(H.D) AS H,Cast((cast(month(a.mkt02) as char)+'/01/'+cast(year(a.mkt02) as char) )as datetime) as J INTO TEM P2 FROM MKT08 A, M KT09 B, MKT04 C, MKT02 D, MKT01 E, MKT05 F, M KT06 G, TEM P5 H WHERE A.M KT01=B.MKT01 AND B.MKT02=C.M KT01 AND A.M KT03=D.MKT01 AND D.M KT07=E.M KT01 AND C.MKT06=F.MKT01 AND G.M KT01=B.MKT04 AND H.A=C.MKT01 AND month(@tgl)=month(a.MKT02) and year(@tgl)=year(a.MKT02)
L6
GROUP BY C.M KT01, C.MKT02, E.MKT02, F.M KT02, Cast((cast(month(a.mkt02) as char)+'/01/'+cast(year(a.mkt02) as char) )as datetime) ORDER BY C.M KT01 GO Laporan Pembelian CREATE PROCEDURE HLAPORANBELI @TGL datetime AS DROP Table Temp1 SELECT C.MKT01 AS A, C.M KT02 AS B, SUM (B.MKT03)AS C,SUM (B.MKT04*B.MKT03)/SUM (B.MKT03) AS D,SUM (B.M KT04*B.MKT03) AS E, A.M KT05,E.M KT02 AS F, F.MKT02 AS G,Cast((cast(month(a.mkt02) as char)+'/01/'+cast(YEAR(a.mkt02) as char) )as datetime) as H INTO TEM P1 FROM MKT10 A, M KT11 B, MKT04 C, MKT02 D, MKT01 E, MKT05 F WHERE A.M KT01=B.MKT01 AND B.MKT02=C.M KT01 AND A.M KT03=D.MKT01 AND D.M KT07=E.M KT01 AND C.MKT06=F.MKT01 AND month(@tgl)=month(a.MKT02) and year (@tgl)= year(a.MKT02) GROUP BY C.M KT01, C.MKT02, A.M KT05, E.M KT02, F.MKT02, Cast((cast(month(a.mkt02) as char)+'/01/'+cast(YEAR(a.mkt02) as char) )as datetime) ORDER BY C.M KT01 GO Laporan Pembayaran Kredit CREATE PROCEDURE HLAPORANBAYAR @TGL DATETIM E AS DROP TABLE TEM P7 select B.M KT01 AS A, SUM (F.MKT03*F.MKT04) AS B INTO TEM P7 from M KT10 B, MKT11 F WHERE B.MKT01=F.MKT01 AND B.MKT05 LIKE 'KREDIT' AND MONTH(@TGL)=MONTH(B.MKT06) AND YEAR(@TGL)=YEAR(B.M KT06) GROUP BY B.M KT01 DROP TABLE TEM P3 select B.M KT01 AS A, C.MKT02 AS B,F.B C, SUM (A.M KT04) AS D,F.B-SUM (A.M KT04) AS E, E.M KT02 AS F,Cast((cast(month(B.mkt06) as char)+'/01/'+cast(YEAR(B.mkt06) as char) )as datetime) as G INTO TEM P3 from M KT34 A, MKT10 B, M KT07 C, MKT02 D, M KT01 E, TEMP7 F WHERE L7
A.M KT01=B.MKT01 AND B.MKT03=D.MKT01 AND B.MKT04=C.M KT01 AND E.M KT01=D.M KT07 AND F.A=B.MKT01 AND MONTH(@TGL)=MONTH(B.MKT06) AND YEAR(@TGL)=YEAR(B.M KT06) GROUP BY B.M KT01, C.MKT02,E.MKT02,Cast((cast(month(B.mkt06) as char)+'/01/'+cast(YEAR(B.mkt06) as char) )as datetime),F.B GO Laporan Kehilangan Barang CREATE PROCEDURE HLAPORANHILANGAN @TGL DATETIM E AS DROP table temp5 select A.MKT01 as A, A.MKT02 as B, A.M KT04 as C,SUM (B.MKT04*B.MKT03)/SUM (B.MKT03) AS D, SUM (B.M KT04*B.MKT03) AS E, F.M KT02 as F, G.MKT02 as G into temp5 From MKT04 A, M KT11 B, MKT10 C, MKT02 E, M KT01 F, MKT05 G where A.M KT01=B.MKT02 AND C.MKT01=B.M KT01 AND C.MKT03=E.M KT01 AND E.M KT07=F.M KT01 AND A.M KT06=G.MKT01 GROUP BY A.MKT01, A.M KT02, A.MKT04, F.MKT02, G.M KT02 ORDER BY A.MKT01 DROP Table Temp6 SELECT C.MKT01 AS A, C.M KT02 AS B, SUM (B.MKT03)AS C,SUM (G.D*B.M KT03)/SUM (B.M KT03) AS D,SUM (G.D*B.M KT03) AS E, E.MKT02 AS F, F.M KT02 AS G,Cast((cast(month(a.mkt02) as char)+'/01/'+cast(YEAR(a.mkt02) as char) )as datetime) as H INTO TEM P6 FROM MKT16 A, M KT17 B, MKT04 C, MKT02 D, MKT01 E, MKT05 F, TEMP5 G WHERE A.M KT01=B.MKT01 AND B.MKT02=C.M KT01 AND A.M KT03=D.MKT01 AND D.M KT07=E.M KT01 AND C.MKT06=F.MKT01 AND G.A=C.MKT01 AND month(@tgl)=month(a.MKT02) and year (@tgl)= year(a.MKT02) GROUP BY C.M KT01, C.MKT02, E.MKT02, F.M KT02, Cast((cast(month(a.mkt02) as char)+'/01/'+cast(YEAR(a.mkt02) as char) )as datetime) L8
ORDER BY C.M KT01 GO
Pseudocode Pseudocode transaksi penjualan Generate kode penjualan Aa: Input kode barang Input jumlah barang Jika kode barang tidak ada input lagi kode barang dan input barang Jika ada Jika kode harga tidak ada Input lagi kode barang dan input barang Jika kode harga ada Tampilkan data barang beserta harga Hitung jumlah harga barang total Jika belum selesai input barang Kembali ke Aa Jika sudah selesai input barang Tampilkan formulir pembayaran Isi pembayaran Jika pembayaran kurang dari harga barang total Ulangi isi pembayaran Jika tidak Input data pemjualan ke database Cetak struk Kosongkan data Kembali lagi ke Generate kode penjualan Selesai jika Selesai jika Selesai jika Selesai jika Transaksi pembelian Generate kode pembelian Pilih supplier Pilih jenis pembayaran Jika pembayaran kredit Pilih tanggal jatuh tempo Selesai jika Aa: Input kode barang atau cari kode barang Jika kode barang tidak ada L9
input lagi kode barang Jika ada Input jumlah barang Input harga beli Hitung harga satuan Input data Jika belum selesai input barang Kembali ke Aa Jika sudah selesai input barang Input data pembelian ke database Kosongkan data Kembali lagi ke Generate kode penjualan Selesai jika Selesai jika Selesai jika Retur penjualan Generate kode retur Input kode penjualan Jika kode penjualan tidak sesuai dengan tanggal dan waktu retur Tidak dapat retur penjualan Reset retur penjualan Jika kode penjualan sesuai dengan tanggal dan waktu retur Aa: Input kode barang Jika kode barang tidak ada Reset inputan kode barang dan jumlah barang Kembali ke Aa jika kode barang ada input jumlah barang kembali jika jumlah barang tidak sesuai ulangi input jumlah barang jika sesuai jika input sudah selesai masukkan data ke database jika belum kembali ke Aa: selesai jika selesai jika Selesai jika Kembali ke Generate kode retur Selesai jika Retur pembelian Generate kode retur Input barang L10
Pilih supplier Cari kode penjualan Jika tidak ada kode penjualan Reset retur pembelian Jika ada kode penjualan input jumlah barang kembali jika jumlah barang tidak sesuai ulangi input jumlah barang jika sesuai jika input sudah selesai masukkan data ke database jika belum input lagi kode barang selesai jika Selesai jika Kembali ke Generate kode retur Selesai jika Pembayaran Pilih supplier Cari kode pembelian Jika kode pembelian tidak ada Pilih supplier lagi Jika ada Tampilkan total tagihan Tampilkan sisa pembayaran Tampilkan jatuh tempo M asukan pembayaran Input database Selesai jika
L11
