-- 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;