Debet – Kredit – Saldo

Database : MS SQL Server 2000

Untuk mebuat sebuah laporan buku besar yang memuat Debet – Kredit – Saldo, maka dibutuhkan sebuah Store Procedure. Dalam Store Procedure ini diimplementasikan pemakaian CURSOR. Contoh berikut adalah sebuah strore procedure untuk bisnis pulsa, dimana parameter yang dikirim adalah Kode Outlet dan tanggal.

CREATE PROCEDURE sp_LaporanTrx
@OutletID char(3),
@Tgl char(12)

AS
declare @TrxID varchar(15)
Declare @uraian varchar(50)
declare @TglTrx datetime
declare @debet money
declare @kredit money
declare @saldo money
declare @saldosblm money
declare @status varchar(10)
declare @requester  varchar(20)
declare @err_no int

SET @err_no=0
BEGIN TRAN
DECLARE Laporan  CURSOR FOR
SELECT     *
FROM         (SELECT     MKiosID AS TrxID, ‘Trx : ‘ + ProductID + ‘.’ + HPKonsumen AS Uraian, TglTrx, 0 AS Debet,
                                              CASE WHEN Status = 3 THEN 0 ELSE Harga END AS Kredit,
                                              CASE WHEN Status = 3 THEN ‘Gagal’ WHEN Status = 2 THEN ‘Sukses’ ELSE ‘Pending’ END AS Status, Requester
                       FROM          dbo.tblMKios
                       WHERE      (OutletID = @OutletID) AND (CONVERT(char(12), TglTrx, 112) = @Tgl)
                       UNION
                       SELECT     RefID AS TrxID, Description AS Uraian, TglTransaksi AS TglTrx, Jumlah AS Debet, 0 AS Kredit, ‘Sukses’ AS Status,
                                             ‘System’ AS Requester
                       FROM         dbo.tblOutletDeposit
                       WHERE     (OutletID = @OutletID) AND (CONVERT(char(12), TglTransaksi, 112) =@Tgl)
                       UNION
                       SELECT     OutletID + CONVERT(char(12), Tgl, 112) AS TrxID, ‘Saldo Awal ‘ + CONVERT(char(12), Tgl, 106) AS Uraian, Tgl AS TglTrx, Saldo AS Debet,
                                             0 AS Kredit, ‘Sukses’ AS Status, ‘System’ AS Requester
                       FROM         dbo.tblSaldoHarian
                       WHERE     (OutletID = @OutletID) AND (CONVERT(char(12), Tgl, 112) = @Tgl)) X
ORDER BY TglTrx ASC
–End of Declare

set @saldosblm=0
–drop Table #TempLap
CREATE TABLE #TempLap (TrxID varchar(20) PRIMARY KEY, Uraian varchar(160), TglTrx smalldatetime, Debet money, Kredit Money, Saldo Money, Status varchar(20), Requester varchar(20))

OPEN Laporan
–Proses  per Record
FETCH Laporan  INTO @TrxID, @uraian, @TglTrx, @debet, @kredit, @status, @requester

WHILE (@@FETCH_STATUS=0)
BEGIN
 Set @saldo=@debet-@kredit+@saldosblm
 INSERT INTO #TempLap  VALUES (@TrxID, @uraian, @TglTrx, @debet, @kredit, @Saldo, @status, @requester)
 set @saldosblm =@saldo

 FETCH Laporan  INTO @TrxID, @uraian, @TglTrx, @debet, @kredit, @status, @requester
END
Close Laporan
DEALLOCATE  Laporan
Select * from #TempLap order by TglTrx
if @err_no=0
  commit tran
else
begin
    RaisError(‘SQLServer : Penyimpanan  Gagal!’,16,1)
    rollback tran
   return
end
GO