TULISAN

-- 1. Cek di table PMDMAT0004 yang stockstatusnya 0004 dan ambil seluruh data yang dibutuhkan

DECLARE @MaterialID VARCHAR(50), @CompanyID INT, @Warehouse VARCHAR(50), @SORDID VARCHAR(50), @Qty FLOAT, @TotalValuation FLOAT;

DECLARE @ChangeDate VARCHAR(14) = FORMAT(GETDATE(), 'yyyyMMddHHmmss');  -- Format tanggal sekarang sesuai yang diminta

DECLARE @ChangeBy VARCHAR(50) = 'System Job Allocated';

DECLARE cursor_material CURSOR FOR

SELECT MaterialID, CompanyID, Warehouse, SORDID, Qty, TotalValuation

FROM PMDMAT0004

WHERE StockStatus = '0004';

-- Mulai looping untuk setiap material

OPEN cursor_material;

FETCH NEXT FROM cursor_material INTO @MaterialID, @CompanyID, @Warehouse, @SORDID, @Qty, @TotalValuation;

WHILE @@FETCH_STATUS = 0

BEGIN

    -- 2. Cek di PTRSALESHEADER berdasarkan SORDID

    DECLARE @DocStatus VARCHAR(10), @ProcessStatus VARCHAR(10);

    SELECT @DocStatus = DocStatus, @ProcessStatus = ProcessStatus

    FROM PTRSALESHEADER

    WHERE DocNo = @SORDID and DocStatus = '7';

    -- 3. Update DocStatus menjadi 9 dan ProcessStatus menjadi 'C' di PTRSALESHEADER

    UPDATE PTRSALESHEADER

    SET DocStatus = '9', ProcessStatus = 'C', ChangeBy = @ChangeBy, ChangeDate = @ChangeDate

    WHERE DocNo = @SORDID and DocStatus = '7';

    -- 4. Update QTY dan TotalValuation pada PMDMAT0004 dengan menambahkan jumlah QTY dan TotalValuation dari StockStatus 0004 ke StockStatus 0001

    DECLARE @ExistingQty FLOAT, @ExistingTotalValuation FLOAT;

    -- Ambil data yang ada di StockStatus 0001

    SELECT @ExistingQty = Qty, @ExistingTotalValuation = TotalValuation

    FROM PMDMAT0004

    WHERE MaterialID = @MaterialID AND CompanyID = @CompanyID AND Warehouse = @Warehouse AND StockStatus = '0001';

    -- Hitung QTY dan TotalValuation baru

    SET @ExistingQty = ISNULL(@ExistingQty, 0) + @Qty;

    SET @ExistingTotalValuation = ISNULL(@ExistingTotalValuation, 0) + @TotalValuation;

    -- Update data dengan jumlah baru

    UPDATE PMDMAT0004

    SET Qty = @ExistingQty, TotalValuation = @ExistingTotalValuation, ChangeBy = @ChangeBy, ChangeDate = @ChangeDate

    WHERE MaterialID = @MaterialID AND CompanyID = @CompanyID AND Warehouse = @Warehouse AND StockStatus = '0001';

    -- 6. Hapus data dari PMDMAT0004 yang memiliki StockStatus 0004 setelah dipindahkan ke StockStatus 0001

    DELETE FROM PMDMAT0004

    WHERE MaterialID = @MaterialID AND CompanyID = @CompanyID AND Warehouse = @Warehouse AND StockStatus = '0004';

    -- Lanjut ke data berikutnya

    FETCH NEXT FROM cursor_material INTO @MaterialID, @CompanyID, @Warehouse, @SORDID, @Qty, @TotalValuation;

END

-- Tutup cursor setelah selesai

CLOSE cursor_material;

DEALLOCATE cursor_material;