Добрый день!
Имеем в базе Супермага оперативные чеки с неправильной датой, возможности заново выгрузить данные с касс и поправить выгрузку нет (данные потеряны).
Возможно с помощью Sql-запроса поменять дату в оперативных чеках за определенную смену? Чтобы потом перевести их в закрытые следующим скриптом:
Код:
DECLARE
-- Номер места хранения. Берем из Разделы->Склады и магазины.
vlocid NUMBER := 2;
-- Номер кассы
vdesknum NUMBER := 2;
-- Номер смены
vznum NUMBER := 1834;
-- Дата закрытия смены. Заменить на нужную.
vclosedate DATE := TO_DATE ('13.04.2013', 'DD.MM.YYYY');
BEGIN
-- Чистим таблицы
DELETE FROM smcashz
WHERE locid = vlocid AND desknum = vdesknum AND znum = vznum;
DELETE FROM smcashdisccard
WHERE locid = vlocid AND desknum = vdesknum AND znum = vznum;
DELETE FROM smcashdisc
WHERE locid = vlocid AND desknum = vdesknum AND znum = vznum;
DELETE FROM smcashcredcardchecks
WHERE locid = vlocid AND desknum = vdesknum AND znum = vznum;
DELETE FROM smcashchecksprinted
WHERE locid = vlocid AND desknum = vdesknum AND znum = vznum;
DELETE FROM smcashcheckcomplexitems
WHERE locid = vlocid AND desknum = vdesknum AND znum = vznum;
DELETE FROM smcashcheckitems
WHERE locid = vlocid AND desknum = vdesknum AND znum = vznum;
DELETE FROM smcashchecks
WHERE locid = vlocid AND desknum = vdesknum AND znum = vznum;
-- Добавляем смену
INSERT INTO smcashz (locid, desknum, znum, closedate, zready, doccreated)
VALUES (vlocid, vdesknum, vznum, vclosedate, 1, 0);
-- Переносим оперативные чеки
INSERT INTO smcashchecks (locid, desknum, znum, checknum, printtime, cashier, totalsum, opcode, depid)
SELECT o.locid, o.desknum, o.znum, o.checknum, o.printtime, o.cashier, o.totalsum, o.opcode, 0
FROM smonlinechecks o
WHERE o.locid = vlocid AND o.desknum = vdesknum AND o.znum = vznum;
-- Переносим товары из оперативных чеков
INSERT INTO smcashcheckitems (locid, desknum, znum, checknum, item, article, quantity, itemprice, totalsum, credcardbrand, seller, depid, subarticle)
SELECT o.locid, o.desknum, o.znum, o.checknum, o.item, o.article, o.quantity, o.itemprice, o.totalsum,
o.credcardbrand, o.seller, o.depid, o.subarticle
FROM smonlinecheckitems o
WHERE o.locid = vlocid AND o.desknum = vdesknum AND o.znum = vznum;
-- Переносим скидки по дисконтам
insert into smcashdisсcard(locid, desknum, znum, checknum, code, discsum)
select o.locid, o.desknum, o.znum, o.checknum, o.code, o.discsum
from smonlinedisccard o
where o.locid = vLocID and o.desknum = vDeskNum and o.znum = vZNum;
-- Удаляем оперативные чеки
DELETE FROM smonlinechecksprinted
WHERE locid = vlocid AND desknum = vdesknum AND znum = vznum;
DELETE FROM smonlinedisccard
WHERE locid = vlocid AND desknum = vdesknum AND znum = vznum;
DELETE FROM smonlinechecks
WHERE locid = vlocid AND desknum = vdesknum AND znum = vznum;
DELETE FROM smonlinecheckitems
WHERE locid = vlocid AND desknum = vdesknum AND znum = vznum;
COMMIT;
END;
/
Версия Супермаг 1.025.1, Оракл 8.1.6