CREATE PROCEDURE HitungInventory
(
@xKODEBARANG VARCHAR(30),
@xBULAN INT,
@xTAHUN VARCHAR(4)
)
AS
DECLARE @NOID BIGINT, @PERIODE VARCHAR(6), @NOTRANSAKSI VARCHAR(30), @URUTTRANSAKSI INT, @KODEBARANG VARCHAR(3), @KETERANGAN VARCHAR(50)
DECLARE @TOTALHARGAAWAL MONEY, @MASUK FLOAT, @HARGAMASUK MONEY, @TOTALHARGAMASUK MONEY ,@KELUAR MONEY, @HARGAKELUAR MONEY, @TOTALHARGAKELUAR MONEY
DECLARE @AKHIR FLOAT, @HARGAHPP MONEY, @TOTALHARGAAKHIR MONEY , @BULAN INT, @TAHUN VARCHAR(4), @TANGGAL DATETIME
DECLARE @TOTMASUK FLOAT, @TOTKELUAR FLOAT
DECLARE @xTOTALHARGAAKHIR MONEY ,@xTotalHargaAkhirUpdate MONEY, @xAkhirUpdate FLOAT ,@xHargaHPPUpdate MONEY, @xAKHIR FLOAT, @XHARGAHPP MONEY
DECLARE @xTotalHargaKeluar MONEY
CREATE TABLE #TEMP
(
NOID BIGINT,
PERIODE VARCHAR(6),
TANGGAL DATETIME,
NOTRANSAKSI VARCHAR(30),
URUTTRANSAKSI INT,
KODEBARANG VARCHAR(30),
KETERANGAN VARCHAR(50),
TOTALHARGAAWAL MONEY,
MASUK FLOAT,
HARGAMASUK MONEY,
TOTALHARGAMASUK MONEY,
KELUAR FLOAT,
HARGAKELUAR MONEY,
TOTALHARGAKELUAR MONEY,
AKHIR FLOAT,
HARGAHPP MONEY,
TOTALHARGAAKHIR MONEY,
BULAN INT,
TAHUN VARCHAR(4)
) ON [PRIMARY]
DECLARE OPENPART CURSOR FOR
SELECT NOID,PERIODE, TANGGAL, NOTRANSAKSI, URUTTRANSAKSI, KODEBARANG, KETERANGAN,TOTALHARGAAWAL, MASUK, HARGAMASUK, TOTALHARGAMASUK,
KELUAR, HARGAKELUAR, TOTALHARGAKELUAR, AKHIR, HARGAHPP, TOTALHARGAAKHIR, BULAN, TAHUN
FROM TABELINVENTORY
WHERE KODEBARANG=@XKODEBARANG AND Bulan=@xBULAN and Tahun=@xTAHUN
OPEN OPENPART
FETCH NEXT FROM OPENPART INTO @NOID , @PERIODE, @TANGGAL, @NOTRANSAKSI , @URUTTRANSAKSI, @KODEBARANG , @KETERANGAN , @TOTALHARGAAWAL,
@MASUK, @HARGAMASUK, @TOTALHARGAMASUK ,@KELUAR, @HARGAKELUAR, @TOTALHARGAKELUAR ,
@AKHIR , @HARGAHPP, @TOTALHARGAAKHIR, @BULAN, @TAHUN
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO #TEMP
VALUES (@NOID , @PERIODE, @TANGGAL, @NOTRANSAKSI , @URUTTRANSAKSI, @KODEBARANG , @KETERANGAN , @TOTALHARGAAWAL,
@MASUK, @HARGAMASUK, @TOTALHARGAMASUK ,@KELUAR, @HARGAKELUAR, @TOTALHARGAKELUAR ,
@AKHIR , @HARGAHPP, @TOTALHARGAAKHIR, @BULAN, @TAHUN)
FETCH NEXT FROM OPENPART INTO @NOID , @PERIODE, @TANGGAL, @NOTRANSAKSI , @URUTTRANSAKSI, @KODEBARANG , @KETERANGAN , @TOTALHARGAAWAL,
@MASUK, @HARGAMASUK, @TOTALHARGAMASUK ,@KELUAR, @HARGAKELUAR, @TOTALHARGAKELUAR ,
@AKHIR , @HARGAHPP, @TOTALHARGAAKHIR, @BULAN, @TAHUN
END
CLOSE OPENPART
DEALLOCATE OPENPART
DECLARE OPENPART1 CURSOR FOR
--SELECT NOID,PERIODE, TANGGAL, NOTRANSAKSI, URUTTRANSAKSI, KODEBARANG, KETERANGAN, TOTALHARGAAWAL, MASUK, HARGAMASUK, TOTALHARGAMASUK,
-- KELUAR, HARGAKELUAR, TOTALHARGAKELUAR,
-- AKHIR, HARGAHPP, TOTALHARGAAKHIR, BULAN, TAHUN
--FROM #TEMP ORDER BY Tanggal asc,NoId asc
SELECT NOID,PERIODE, TANGGAL, NOTRANSAKSI, URUTTRANSAKSI, KODEBARANG, KETERANGAN,TOTALHARGAAWAL, MASUK, HARGAMASUK, TOTALHARGAMASUK,
KELUAR, HARGAKELUAR, TOTALHARGAKELUAR,
AKHIR, HARGAHPP, TOTALHARGAAKHIR, BULAN, TAHUN
FROM TABELINVENTORY
WHERE KODEBARANG=@XKODEBARANG AND Bulan=@xBULAN and Tahun=@xTAHUN
OPEN OPENPART1
FETCH NEXT FROM OPENPART1 INTO @NOID , @PERIODE, @TANGGAL, @NOTRANSAKSI , @URUTTRANSAKSI, @KODEBARANG , @KETERANGAN , @TOTALHARGAAWAL,
@MASUK, @HARGAMASUK, @TOTALHARGAMASUK ,@KELUAR, @HARGAKELUAR, @TOTALHARGAKELUAR ,
@AKHIR , @HARGAHPP, @TOTALHARGAAKHIR, @BULAN, @TAHUN
WHILE @@FETCH_STATUS=0
BEGIN
IF @URUTTRANSAKSI=0
BEGIN
IF @HARGAHPP=0
BEGIN
SET @xTOTALHARGAAKHIR=@TOTALHARGAAWAL
SET @xTotalHargaAkhirUpdate=@xTOTALHARGAAKHIR
SET @xAkhirUpdate=@AKHIR
END
ELSE
BEGIN
SET @xTOTALHARGAAKHIR=@TOTALHARGAAKHIR
SET @xTotalHargaAkhirUpdate=@xTOTALHARGAAKHIR
SET @xAkhirUpdate=@AKHIR
SET @xHargaHPPUpdate=@HARGAHPP
END
END
IF @URUTTRANSAKSI=1 AND @KETERANGAN='FAKTURBELI'
BEGIN
IF @HARGAHPP=0
BEGIN
SET @xTOTALHARGAAKHIR=@TOTALHARGAAWAL
SET @xAkhir=@AKHIR
END
ELSE
BEGIN
SET @xTOTALHARGAAKHIR=@xTotalHargaAkhirUpdate
SET @xAkhir=@xAkhirUpdate
END
--NEXT DATA
SET @xAkhir=@xAkhir + @Masuk
SET @xTotalHargaAkhir = @TOTALHARGAMASUK + @XTOTALHARGAAKHIR
IF @xAKHIR<>0
BEGIN
SET @XHARGAHPP=@XTOTALHARGAAKHIR / @XAKHIR
END
SET @xHargaHPPUpdate =@XHargaHPP
SET @xTotalHargaAkhirUpdate = @xTotalHargaAkhir
SET @xAkhirUpdate = @xAkhir
Update #TEMP set Masuk=@MASUK, HargaMasuk=@HARGAMASUK, TotalHargaMasuk=@TOTALHARGAMASUK,
Akhir=@XAKHIR, HargaHPP=@XHARGAHPP, TotalHargaAkhir=@XTOTALHARGAAKHIR
where NOID=@NOID and periode=@PERIODE and NoTransaksi=@NOTRANSAKSI and KodeBarang=@KODEBARANG
END
IF @URUTTRANSAKSI=2 AND @KETERANGAN='FAKTURJUAL'
BEGIN
IF @HARGAHPP=0
BEGIN
SET @xTOTALHARGAAKHIR=@TOTALHARGAAWAL
SET @xAkhir=@AKHIR
END
ELSE
BEGIN
SET @xTOTALHARGAAKHIR=@xTotalHargaAkhirUpdate
SET @xAkhir=@xAkhirUpdate
SET @xHargaHPP = @xHargaHPPUpdate
END
--NEXT DATA
SET @xAkhir=@xAkhir - @Keluar
SET @xTotalHargaKeluar = @KELUAR * @xHargaHPP
SET @xTotalHargaAkhir = @XTOTALHARGAAKHIR - @xTotalHargaKeluar
SET @xAkhirUpdate = @xAkhir
SET @xHargaHPPUpdate =@XHargaHPP
SET @xTotalHargaAkhirUpdate = @xTotalHargaAkhir
Update #TEMP set KELUAR=@KELUAR, HargaKeluar=@xHARGAHPP, TotalHargaKeluar=@xTotalHargaKeluar,
Akhir=@XAKHIR, HargaHPP=@XHARGAHPP, TotalHargaAkhir=@XTOTALHARGAAKHIR
where NOID=@NOID and periode=@PERIODE and NoTransaksi=@NOTRANSAKSI and KodeBarang=@KODEBARANG
END
SELECT @TOTMASUK=SUM(MASUK) , @TOTKELUAR=SUM(KELUAR) FROM #TEMP WHERE KODEBARANG=@KODEBARANG AND PERIODE=@PERIODE GROUP BY KODEBARANG
-- -- UPDATE NILAI STOCK
Update mstbarang SET HargaHPP=@XHARGAHPP,QtyStock=@XAKHIR, MASUK=@TOTMASUK, KELUAR=@TOTKELUAR, NilaiPersediaan=@XTOTALHARGAAKHIR
where kodebarang=@XKODEBARANG
FETCH NEXT FROM OPENPART1 INTO @NOID , @PERIODE, @TANGGAL, @NOTRANSAKSI , @URUTTRANSAKSI, @KODEBARANG , @KETERANGAN , @TOTALHARGAAWAL,
@MASUK, @HARGAMASUK, @TOTALHARGAMASUK ,@KELUAR, @HARGAKELUAR, @TOTALHARGAKELUAR ,
@AKHIR , @HARGAHPP, @TOTALHARGAAKHIR, @BULAN, @TAHUN
END
CLOSE OPENPART1
DEALLOCATE OPENPART1
SELECT * FROM #TEMP ORDER BY Tanggal asc,NoId asc
GO
