Дублирование чека — различия между версиями
Материал из wiki.standart-n.ru
Aleksnick (обсуждение | вклад) |
Agk (обсуждение | вклад) (→Автоматическое исправление отмененных чеков) |
||
| Строка 870: | Строка 870: | ||
create or alter procedure PR_CHECK_ZDOCS ( | create or alter procedure PR_CHECK_ZDOCS ( | ||
| − | ZDOC_ID DM_ID) | + | ZDOC_ID DM_ID, |
| + | STATUS integer = -1) | ||
returns ( | returns ( | ||
DEVICE_NUM DM_TEXT, | DEVICE_NUM DM_TEXT, | ||
| Строка 976: | Строка 977: | ||
and DS.DEVICE_NUM = :DEVICE_NUM | and DS.DEVICE_NUM = :DEVICE_NUM | ||
and DS.DOC_TYPE in (3) | and DS.DOC_TYPE in (3) | ||
| − | and DS.STATUS | + | and DS.STATUS = :STATUS |
and cast(DS.COMMITDATE as DM_DATE) between cast(:COMMITDATE as DM_DATE) - 7 and cast(:COMMITDATE as DM_DATE) + 7 | and cast(DS.COMMITDATE as DM_DATE) between cast(:COMMITDATE as DM_DATE) - 7 and cast(:COMMITDATE as DM_DATE) + 7 | ||
and ((abs(DS.SUMM1 + :RASH_NAL) < 1 | and ((abs(DS.SUMM1 + :RASH_NAL) < 1 | ||
| Строка 1002: | Строка 1003: | ||
GRANT EXECUTE ON PROCEDURE PR_CHECK_ZDOCS TO SYSDBA; | GRANT EXECUTE ON PROCEDURE PR_CHECK_ZDOCS TO SYSDBA; | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
</pre> | </pre> | ||
Версия 17:03, 12 августа 2016
Содержание
Ситуации которые бывают с чеками
#1. ККМ при отбитии выдал ошибку и АРМ Кассир решил, что чек не прошел и отменил его, а на самом деле чек в ккм прошел и суммы обновились. Возможно, была незавершенка, в которой нажали отменить. В z-отчете сумма больше, чем по базе. Если отбивали по безналу, то по терминалу этот чек тоже должен быть проведенный. В итоге:
база - 0, ккм - 1, банк - 1
Решение: в базе должен быть отмененный чек на сумму расхождений, который нужно продублировать. Ситуация должна выправиться:
база - 1, ккм - 1, банк - 1
#2. В ккм чек прошел дважды, а по базе - 1. Скорей всего возникла ситуация, как в первом случае, вышла незавершенка, но в ней на этот раз нажали завершить. Если отбивали по безналу, то по терминалу этот чек тоже должен быть проведенный. В итоге:
база - 1, ккм - 2, банк - 1
Решение: сначала нужно продублировать чек. Получится:
база - 2, ккм - 2, банк - 1
Затем нужно сделать возврат с включенной ккм, но с несвязным терминалом. Получится:
база - 1, ккм - 1, банк - 1
#3. При оплате по безналу чек по банку не прошел, но в базе и ккм он записался как проведенный. Скорей всего была незавершенка на банковской операции, при которой нажали пропустить. В итоге:
база - 1, ккм - 1, банк - 0
Получается, что данный товар продавец отдал покупателю бесплатно.
Решение: сначала нужно сделать возврат с включенной ккм, но с несвязным терминалом. Получится:
база - 0, ккм - 0, банк - 0
Вроде все сходится, но т.к. как мы отдали товар покупателю, то нужно списать его с остатков. Для этого пробиваем данный чек по безналу в режиме без кмм и с несвязным терминалом.
#4. При оплает по безналу чек по банку прошел, а при проведении по ккм возникла незавершенка, в которой нажали пропустить. В результате чек по банку и по базе прошел, с остатков списался, а по ккм не прошел. Сумма по чекам будет больше, чем по z-отчету на сумму проблемного чека. Т.е.:
база - 1, ккм - 0, банк - 1
Решение: сначала нужно сделать возврат по безналу в режиме без ккм и несвязного терминала. Получится:
база - 0, ккм - 0, банк - 1
Затем нужно пробить чек по безналу с включенной ккм, но с несвязным терминалом. Ситуация разрешится:
база - 1, ккм - 1, банк - 1
#5. В ккм не настроено обнуление денежного ящика. Из-за этого в z-отчете сумма будет больше, чем по чекам на суммы предыдущих смен.
Решение: предыдущие z-отчеты в базе исправлять не нужно. Нужно изменить настройки ккм, чтобы таких ситуаций больше не было.
#6. В ккм неверное настроено округление. Ситуация характерна для Казахстана, т.к. именно там нужно делать данную настройку, о которой иногда забывают. В итоге z-отчеты расходятся с базой на копейки или несколько тенге.
Решение: предыдущие смены исправляются спомощью процедуры PR_REPAIR_DIFFERENCE_BY_DOC, которая расхождения в скидку. Чтобы в дальнейшем не было расхождений, нужно изменить настройки ккм.
#7. В ккм не фискализирована. В результате у смен не будет меняться номер и в программе будет неправильно определяться принадлежность чеков к смене. Из-за этого будут выходить расхождения.
Решение: предыдущие смены исправлять в программе не нужно. Нужно фискализировать ккм, чтобы таких ситуаций больше не было. При выгрузке в бухгалтерию номера смен редактируются вручную.
Поиск расхождений по чекам в базе
Расхождения в базе между z-отчетами и тов. отчетами можно найти спомощью следующего запроса. Если проблемный чек удален, то скорей всего нужно его продублировать. Если проблемный чек не удален, то скорей всего по ккм он прошел дважды. Нужно его продублировать, а затем сделать возврат.
Процедура для клиентской базы
SET TERM ^ ;
create or alter procedure PR_GET_DIFFERENCE_BY_DOCS
returns (
ZDATE DM_DATE,
ZID DM_ID,
RASH_NAL DM_DOUBLE,
RASH_BEZNAL DM_DOUBLE,
VNUM DM_TEXT,
VSHIFT DM_TEXT,
DEVICE_NUM DM_TEXT,
DOC_ID DM_ID,
NAL DM_DOUBLE,
BEZNAL DM_DOUBLE,
STATUS DM_TEXT,
comment DM_TEXT,
CMD DM_TEXT)
as
begin
for
-- для сортировки
select * from (
-- расхождения м/д чеками и z-отчетами
select
r.zdate as zdate,
r.zid as zid,
abs(r.zsumm1 - (r.csumm1 + r.summ_cash)) as rash_nal,
abs(r.zsumm2 - r.csumm2) - abs(r.zsumm1 - (r.csumm1 + r.summ_cash)) as rash_beznal,
r.vnum as vnum,
r.vshift as vshift,
r.device_num as device_num,
doc.id as doc_id,
doc.summ1 as nal,
doc.summ2 as beznal,
iif(doc.status is not null, iif(doc.status = 1, 'Не удален', 'Удален'), null) as status,
iif((doc.id is not null) or (abs(r.zsumm1 - (r.csumm1 + r.summ_cash))> 10) or (abs(r.zsumm2 - r.csumm2) - abs(r.zsumm1 - (r.csumm1 + r.summ_cash)) > 10) ,
'execute procedure PR_CREATE_DUPLICATE_CHECK('||(cast(doc.id as VARCHAR(255)))||');',
'execute procedure PR_REPAIR_DIFFERENCE_BY_DOC('||(cast(r.zid as VARCHAR(255)))||', '||(cast(coalesce(doc.summ1,0) as VARCHAR(255)))||');'
) as cmd,
iif((doc.id is not null) or (abs(r.zsumm1 - (r.csumm1 + r.summ_cash))> 10) or (abs(r.zsumm2 - r.csumm2) - abs(r.zsumm1 - (r.csumm1 + r.summ_cash)) > 10) ,
'Проблема с чеком',
'Неверное округление'
) as cmt
from (
select
cast(d.commitdate as dm_date) as zdate,
d.id as zid,
d.vnum as vnum,
d.vshift as vshift,
d.device_num as device_num,
d.summ1 as zsumm1,
d.summ2 as zsumm2,
coalesce((select sum(-c1.summ1) from docs c1 where 1=1
and c1.doc_type in (3,9)
and c1.device_num=d.device_num
and c1.vshift=d.vshift
and c1.status=1
and cast(c1.commitdate as dm_date) between cast(d.commitdate as dm_date) -7 and cast(d.commitdate as dm_date) +7
), 0) as csumm1,
coalesce((select sum(-(c2.summ1+c2.summ2)) from docs c2 where 1=1
and c2.doc_type in (3)
and c2.device_num=d.device_num
and c2.vshift=d.vshift
and c2.status=1
and cast(c2.commitdate as dm_date) between cast(d.commitdate as dm_date) -7 and cast(d.commitdate as dm_date) +7
), 0) as csumm2,
coalesce((select sum(-h.summa) from cash_docs h where 1=1
and h.doc_type in (1,2)
and h.summa < d.summ1
and h.device_num=d.device_num
and h.vshift=d.vshift
and h.status=1
and cast(h.commitdate as dm_date) between cast(d.commitdate as dm_date) -7 and cast(d.commitdate as dm_date) +7
), 0) as summ_cash
from docs d
where 1=1
and d.doc_type=13
and d.status=1
and cast(d.commitdate as dm_date) between current_date - 365 and current_date + 1
--order by d.commitdate desc
) r
left join docs doc on 1=1
and doc.doc_type in (3)
and doc.vshift=r.vshift
and doc.device_num=r.device_num
and cast(doc.commitdate as dm_date) between r.zdate -7 and r.zdate +7
and (((abs(abs(doc.summ1) - abs(r.zsumm1 - (r.csumm1 + r.summ_cash))) < 0.1 ) and (abs(doc.summ1)>0.01)) or ((abs(abs(doc.summ2) - abs(r.zsumm2 - (r.csumm2))) < 0.1 ) and abs(doc.summ2)>0.01))
where
(
abs(r.zsumm1 - (r.csumm1 + r.summ_cash)) > 0.01
or
abs(r.zsumm2 - r.csumm2) > 0.01
)
union all
-- Расходжение сумм м/д документами и детализацией
select
cast(z.commitdate as dm_date) as zdate,
z.id as zid,
0.01 as rash_nal,
0.01 as rash_beznal,
z.vnum as zvnum,
z.vshift as vshift,
z.device_num as device_num,
d.id as doc_id,
d.summ1 as nal,
d.summ2 as beznal,
iif(d.status is not null, iif(d.status = 1, 'Не удален', 'Удален'), null) as status,
'update docs d set d.summa = (select sum(dd.price*dd.quant + dd.sum_dsc) from doc_detail dd where dd.doc_id = d.id) where d.id = '||cast(d.id as dm_text)||'' as cmd,
'Расходжение сумм м/д документами и детализацией' as cmt
from docs d
left join docs z on 1=1
and z.doc_type=13
and z.vshift=d.vshift
and z.device_num=d.device_num
and cast(z.commitdate as dm_date) between cast(d.commitdate as dm_date) -7 and cast(d.commitdate as dm_date) +7
where 1=1
and d.doc_type in (1,2,20,3,9,6,4,11)
and d.status = 1
and abs(d.summa - (select sum(dd.price*dd.quant + dd.SUM_DSC) from doc_detail dd where dd.doc_id = d.id)) > 0.01
and cast(d.commitdate as dm_date) between current_date - 365 and current_date + 1
) q order by q.zdate desc
into
:zdate, :zid, :rash_nal, :rash_beznal, :vnum, :vshift, :device_num, :doc_id, :nal, :beznal, :status, :cmd, :comment
do
suspend;
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT SELECT ON DOCS TO PROCEDURE PR_GET_DIFFERENCE_BY_DOCS;
GRANT SELECT ON CASH_DOCS TO PROCEDURE PR_GET_DIFFERENCE_BY_DOCS;
GRANT SELECT ON DOC_DETAIL TO PROCEDURE PR_GET_DIFFERENCE_BY_DOCS;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE PR_GET_DIFFERENCE_BY_DOCS TO SYSDBA;
Процедура для сводной базы
SET SQL DIALECT 3;
DROP TABLE DIFFERENCE_BY_DOCS;
CREATE TABLE DIFFERENCE_BY_DOCS (
CMT DM_TEXT /* DM_TEXT = VARCHAR(250) */,
PROFILE_ID DM_ID /* DM_ID = BIGINT NOT NULL */,
SPROFILE DM_TEXT /* DM_TEXT = VARCHAR(250) */,
ZDATE DM_DATE /* DM_DATE = DATE */,
ZID DM_ID_NULL /* DM_ID_NULL = BIGINT */,
RASH_NAL DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
RASH_BEZNAL DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
VNUM DM_TEXT /* DM_TEXT = VARCHAR(250) */,
VSHIFT DM_TEXT /* DM_TEXT = VARCHAR(250) */,
DEVICE_NUM DM_TEXT /* DM_TEXT = VARCHAR(250) */,
DOC_ID DM_ID_NULL /* DM_ID_NULL = BIGINT */,
NAL DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
BEZNAL DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
STATUS DM_TEXT /* DM_TEXT = VARCHAR(250) */,
CMD DM_TEXT /* DM_TEXT = VARCHAR(250) */,
DT DM_DATETIME /* DM_DATETIME = TIMESTAMP */
);
SET TERM ^ ;
create or alter procedure PR_GET_DIFFERENCE_BY_DOCS (
DAYS_AGO DM_ID = 31)
returns (
CMT DM_TEXT,
PROFILE_ID DM_ID,
SPROFILE DM_TEXT,
ZDATE DM_DATE,
ZID DM_ID_NULL,
RASH_NAL DM_DOUBLE,
RASH_BEZNAL DM_DOUBLE,
VNUM DM_TEXT,
VSHIFT DM_TEXT,
DEVICE_NUM DM_TEXT,
DOC_ID DM_ID_NULL,
NAL DM_DOUBLE,
BEZNAL DM_DOUBLE,
STATUS DM_TEXT,
CMD DM_TEXT)
as
begin
delete from difference_by_docs;
for
select * from (
/* несхождение чеков и z-отчетов */
select
r.profile_id,
r.sprofile,
r.zdate,
r.zid,
round(abs(r.zsumm1 - (r.csumm1 + r.summ_cash)), 2) as rash_nal,
round(abs(r.zsumm2 - r.csumm2) - abs(r.zsumm1 - (r.csumm1 + r.summ_cash)), 2) as rash_beznal,
r.zvnum as vnum,
r.vshift as vshift,
r.device_num as device_num,
doc.id as doc_id,
round(doc.summ1, 2) as nal,
round(doc.summ2, 2) as beznal,
iif(doc.status is not null, iif(doc.status = 1, 'Не удален', 'Удален'), null) as status,
iif((doc.id is not null) or (abs(r.zsumm1 - (r.csumm1 + r.summ_cash)) > 10) or (abs(r.zsumm2 - r.csumm2) - abs(r.zsumm1 - (r.csumm1 + r.summ_cash)) > 10),
'insert into g$tasks (PROFILE_ID,TASK_TYPE,CAPTION,data) values ('||r.profile_id||',0,''Дублирование чека '||(cast(doc.id as VARCHAR(255)))||''',''execute procedure PR_CREATE_DUPLICATE_CHECK('''''||(cast(doc.id as VARCHAR(255)))||''''');'');',
'insert into g$tasks (PROFILE_ID,TASK_TYPE,CAPTION,data) values ('||r.profile_id||',0,''Исправление скидки чека'',''execute procedure PR_REPAIR_DIFFERENCE_BY_DOC ('''''||(cast(r.zid as VARCHAR(255)))||''''', '''''||(cast(coalesce(doc.summ1,0) as VARCHAR(255)))||''''');'');'
) as cmd,
iif((doc.id is not null) or (abs(r.zsumm1 - (r.csumm1 + r.summ_cash)) > 10) or (abs(r.zsumm2 - r.csumm2) - abs(r.zsumm1 - (r.csumm1 + r.summ_cash)) > 10),
'Проблема с чеком',
'Неверное округление'
)
as cmt
from (
select
d.g$profile_id as profile_id,
(select g.caption from g$profiles g where g.id=d.g$profile_id) as sprofile,
cast(d.commitdate as dm_date) as zdate,
d.id as zid,
d.vnum as zvnum,
d.vshift as vshift,
d.device_num as device_num,
d.summ1 as zsumm1,
d.summ2 as zsumm2,
coalesce((select sum(-c1.summ1) from docs c1 where 1=1
and c1.doc_type in (3,9)
and c1.device_num=d.device_num
and c1.vshift=d.vshift
and c1.status=1
and c1.g$profile_id=d.g$profile_id
and cast(c1.commitdate as dm_date) between cast(d.commitdate as dm_date) -7 and cast(d.commitdate as dm_date) +7
), 0) as csumm1,
coalesce((select sum(-(c2.summ1+c2.summ2)) from docs c2 where 1=1
and c2.doc_type in (3)
and c2.device_num=d.device_num
and c2.vshift=d.vshift
and c2.status=1
and c2.g$profile_id=d.g$profile_id
and cast(c2.commitdate as dm_date) between cast(d.commitdate as dm_date) -7 and cast(d.commitdate as dm_date) +7
), 0) as csumm2,
coalesce((select sum(-h.summa) from cash_docs h where 1=1
and h.doc_type in (1,2)
and h.summa < d.summ1
and h.device_num=d.device_num
and h.vshift=d.vshift
and h.status=1
and h.g$profile_id=d.g$profile_id
and cast(h.commitdate as dm_date) between cast(d.commitdate as dm_date) -7 and cast(d.commitdate as dm_date) +7
), 0) as summ_cash
from docs d
where 1=1
and d.doc_type=13
and d.status=1
and cast(d.commitdate as dm_date) between current_date - :days_ago and current_date + 1
) r
left join docs doc on 1=1
and doc.doc_type in (3)
and doc.vshift=r.vshift
and doc.device_num=r.device_num
and doc.g$profile_id=r.profile_id
and cast(doc.commitdate as dm_date) between r.zdate -7 and r.zdate +7
and (((abs(abs(doc.summ1) - abs(r.zsumm1 - (r.csumm1 + r.summ_cash))) < 0.1 ) and (abs(doc.summ1)>0.01)) or ((abs(abs(doc.summ2) - abs(r.zsumm2 - (r.csumm2))) < 0.1 ) and abs(doc.summ2)>0.01))
where
(
abs(r.zsumm1 - (r.csumm1 + r.summ_cash)) > 0.01
or
abs(r.zsumm2 - r.csumm2) > 0.01
)
union all
/* z-отчеты которые почему то со статусом -1 */
select
z.g$profile_id as profile_id,
(select g.caption from g$profiles g where g.id=z.g$profile_id) as sprofile,
cast(z.commitdate as dm_date) as zdate,
z.id as zid,
z.summ1 as rash_nal,
z.summ2 as rash_beznal,
z.vnum as vnum,
z.vshift as vshift,
z.device_num as device_num,
null as doc_id,
null as nal,
null as beznal,
null as status,
'insert into g$tasks (PROFILE_ID,TASK_TYPE,CAPTION,data) values ('||z.g$profile_id||',0,''Исправление z-отчета '||(cast(z.id as VARCHAR(255)))||''',''update docs set status=1 where id='||(cast(z.id as VARCHAR(255)))||';'');' as cmd,
'Неверный статус z-отчета' as cmt
from docs z
where 1=1
and z.doc_type=13
and z.status=-1
and (select count(*)
from docs d
where 1=1
and d.doc_type=3
and d.status=1
and d.vshift=z.vshift
and d.device_num=z.device_num
and d.g$profile_id=z.g$profile_id
and d.commitdate between cast(z.commitdate as dm_date) - 7 and cast(z.commitdate as dm_date) + 7
)>0
and (select count(*)
from docs d where 1=1
and d.doc_type=13
and d.status=1
and d.vshift=z.vshift
and d.device_num=z.device_num
and d.g$profile_id=z.g$profile_id
and d.commitdate between cast(z.commitdate as dm_date) - 7 and cast(z.commitdate as dm_date) + 7
)=0
and z.commitdate between current_date - :days_ago and current_date + 2
union all
/* чеки без z-отчета */
select
d.g$profile_id as profile_id,
(select g.caption from g$profiles g where g.id=d.g$profile_id) as sprofile,
cast(d.commitdate as dm_date) as zdate,
null as zid,
null as rash_nal,
null as rash_beznal,
null as vnum,
d.vshift as vshift,
d.device_num as device_num,
null as doc_id,
null as nal,
null as beznal,
null as status,
'' as cmd,
cast(sum(c) as VARCHAR(255))||' чеков без z-отчета' as cmt
from (
select
count(r.id) as c,
r.doc_type as doc_type,
r.status as status,
r.g$profile_id as g$profile_id,
r.vshift as vshift,
r.device_num as device_num,
cast(r.commitdate as dm_date) as commitdate
from docs r
where 1=1
and r.doc_type=3
and r.status=1
and r.summa<>0
and r.vnum is not null
and r.device_num is not null
and r.commitdate between current_date - :days_ago and current_date-1
group by r.status, r.vshift, r.device_num, r.g$profile_id, r.doc_type, cast(r.commitdate as dm_date)
) d
where 1=1
and (select count(*)
from docs z
where 1=1
and z.doc_type=13
and z.status=1
and z.vshift=d.vshift
and z.device_num=d.device_num
and z.g$profile_id=d.g$profile_id
and z.commitdate between cast(d.commitdate as dm_date) - 7 and cast(d.commitdate as dm_date) + 7
)=0
group by cast(d.commitdate as dm_date), d.device_num, d.vshift, d.g$profile_id
union all
/* Расходжение сумм м/д документами и детализацией */
select
d.g$profile_id as profile_id,
(select g.caption from g$profiles g where g.id=d.g$profile_id) as sprofile,
cast(d.commitdate as dm_date) as zdate,
d.id as zid,
round(abs(d.summa - (select sum(dd.price*dd.quant + dd.SUM_DSC) from doc_detail dd where dd.doc_id = d.id)), 2) as rash_nal,
null as rash_beznal,
null as zvnum,
null as vshift,
null as device_num,
d.id as doc_id,
d.summ1 as nal,
d.summ2 as beznal,
iif(d.status is not null, iif(d.status = 1, 'Не удален', 'Удален'), null) as status,
'insert into g$tasks (PROFILE_ID,TASK_TYPE,CAPTION,data) values ('||d.g$profile_id||',0,''Расходжение документа '||(cast(d.id as VARCHAR(255)))||''',''update docs d set d.summa = (select sum(dd.price*dd.quant + dd.sum_dsc) from doc_detail dd where dd.doc_id = d.id) where d.id='||(cast(d.id as VARCHAR(255)))||';'');' as cmd,
'Расходжение сумм м/д документами и детализацией' as cmt
from docs d
where 1=1
and d.doc_type in (1,2,20,3,9,6,4,11)
and d.status = 1
and abs(d.summa - (select sum(dd.price*dd.quant + dd.SUM_DSC) from doc_detail dd where dd.doc_id = d.id and dd.g$profile_id=d.g$profile_id)) > 0.01
and cast(d.commitdate as dm_date) between current_date - :days_ago and current_date + 1
and d.g$profile_id not in (57) /* аптеки удмуртии склад */
) q
where 1=1
and q.profile_id not in (100) /* сводная база */
and q.profile_id not in (2,3) /* фармаком тестовые профили */
and (select pr.dbsecurekey from g$profiles pr where pr.id=q.profile_id) is not null /* закрытые аптеки */
order by q.zdate desc
into
:profile_id, :sprofile, :zdate, :zid, :rash_nal, :rash_beznal, :vnum, :vshift, :device_num, :doc_id, :nal, :beznal, :status, :cmd, :cmt
do
begin
insert into difference_by_docs (profile_id, sprofile, zdate, zid, rash_nal, rash_beznal, vnum, vshift, device_num, doc_id, nal, beznal, status, cmd, cmt, dt)
values (:profile_id, :sprofile, :zdate, :zid, :rash_nal, :rash_beznal, :vnum, :vshift, :device_num, :doc_id, :nal, :beznal, :status, :cmd, :cmt, current_timestamp);
/* suspend; */
end
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT SELECT,INSERT,DELETE ON DIFFERENCE_BY_DOCS TO PROCEDURE PR_GET_DIFFERENCE_BY_DOCS;
GRANT SELECT ON G$PROFILES TO PROCEDURE PR_GET_DIFFERENCE_BY_DOCS;
GRANT SELECT ON DOCS TO PROCEDURE PR_GET_DIFFERENCE_BY_DOCS;
GRANT SELECT ON CASH_DOCS TO PROCEDURE PR_GET_DIFFERENCE_BY_DOCS;
GRANT SELECT ON DOC_DETAIL TO PROCEDURE PR_GET_DIFFERENCE_BY_DOCS;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE PR_GET_DIFFERENCE_BY_DOCS TO SYSDBA;
Исправление расхождений из-за отмененных чеков
Дублирование чека
Иногда возникает необходимость полностью задублировать чек в базе, при этом не проводя его ни по ККМ ни по банковскому терминалу. Для этого выполните скрипт ниже
Создание процедуры
SET TERM ^ ;
create or alter procedure PR_CREATE_DUPLICATE_CHECK (
DOC_ID DM_ID,
DOC_QUANT DM_ID = 1)
returns (
O$DOC_ID DM_ID)
as
declare variable I integer;
declare variable DOC_TYPE DM_STATUS;
declare variable AGENT_ID DM_ID;
declare variable PARENT_ID DM_ID;
declare variable DOCNUM DM_TEXT;
declare variable DOCDATE DM_DATETIME;
declare variable COMMITDATE DM_DATETIME;
declare variable SESSION_ID DM_ID;
declare variable PARAM_VALUE DM_TEXT1024;
declare variable COMMENTS DM_BLOBTEXT;
declare variable SUMM1 DM_DOUBLE;
declare variable SUMM2 DM_DOUBLE;
declare variable SUMM3 DM_DOUBLE;
declare variable SUMM4 DM_DOUBLE;
declare variable VSHIFT DM_ID;
declare variable DEVICE_NUM DM_TEXT;
declare variable VNUM DM_ID;
declare variable BONUS DM_DOUBLE;
declare variable CAPTION DM_TEXT;
declare variable STATUS DM_STATUS;
declare variable SUMMA DM_DOUBLE;
declare variable SUMMA_O DM_DOUBLE;
begin
I = 0;
/* Включаем режим расход в минус, предварительно запоминая значение режима 1-Разрешен, 0-Запрещен */
select PARAM_VALUE
from PARAMS
where PARAM_ID = 'RASHOD_MINUS'
into :PARAM_VALUE;
update PARAMS
set PARAM_VALUE = '1'
where PARAM_ID = 'RASHOD_MINUS';
select STATUS,
DOC_TYPE,
AGENT_ID,
PARENT_ID,
DOCNUM,
DOCDATE,
COMMITDATE,
AUDIT_ID,
coalesce(COMMENTS, ''),
SUMM1,
SUMM2,
SUMM3,
SUMM4,
VSHIFT,
DEVICE_NUM,
VNUM,
BONUS,
CAPTION,
SUMMA,
SUMMA_O
from DOCS
where ID = :DOC_ID
into :STATUS,
:DOC_TYPE,
:AGENT_ID,
:PARENT_ID,
:DOCNUM,
:DOCDATE,
:COMMITDATE,
:SESSION_ID,
:COMMENTS,
:SUMM1,
SUMM2,
SUMM3,
SUMM4,
VSHIFT,
DEVICE_NUM,
VNUM,
BONUS,
CAPTION,
:SUMMA,
:SUMMA_O;
while (:DOC_QUANT > I) do
begin
I = I + 1;
/* Новый документ */
select DOC_ID
from PR_NEWDOC(:DOC_TYPE, :AGENT_ID, :PARENT_ID, :DOCNUM, :DOCDATE, :SESSION_ID)
into :O$DOC_ID;
/* Детализация */
if (:STATUS = 1) then
insert into DOC_DETAIL_ACTIVE (PARENT_ID, DOC_ID, PART_ID, PART_PARENT_ID, DOC_DETAIL_ID, PRICE, NAC, QUANT,
DISCOUNT, SUMMA, SUMMA_O, DCARD, WARE_ID, PRICE_O, PRICE_Z, PRICE_R, BARCODE,
BARCODE1, GODENDO, SERIA, NDS, SUM_NDSO, SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD,
EDIZM, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID,
ORIG_COUNTRY_ID, Z_ID, SNAME, SIZG, SCOUNTRY, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY,
INSERTDT, KOEF, MOTHERPART_ID, DEP, BCODE_IZG, HUMAN_QUANT, SUM_DSC, CUSTOMDRAW,
STATUS, PART_TYPE, BASE_AGENT_ID, PACKET, VPART_ID)
select PARENT_ID,
:O$DOC_ID,
PART_ID,
PART_PARENT_ID,
0,
PRICE,
NAC,
QUANT,
DISCOUNT,
SUMMA,
SUMMA_O,
DCARD,
WARE_ID,
PRICE_O,
PRICE_Z,
PRICE_R,
DD.BARCODE,
BARCODE1,
GODENDO,
SERIA,
NDS,
SUM_NDSO,
SERT,
DATESERT,
KEMVSERT,
SDSERT,
REGN,
NGTD,
EDIZM,
DD.NAME_ID,
DD.IZG_ID,
DD.COUNTRY_ID,
DD.ORIG_CODE,
ORIG_NAME_ID,
ORIG_IZG_ID,
ORIG_COUNTRY_ID,
Z_ID,
DD.SNAME,
SIZG,
SCOUNTRY,
SORIG_NAME,
SORIG_IZG,
SORIG_COUNTRY,
DD.INSERTDT,
0,
0,
DEP,
BCODE_IZG,
HUMAN_QUANT,
SUM_DSC,
null,
0,
PART_TYPE,
BASE_AGENT_ID,
DD.PACKET,
0
from VW_DOC_DETAIL DD
left join WARES W on DD.WARE_ID = W.ID
where DOC_ID = :DOC_ID;
else
insert into DOC_DETAIL_ACTIVE (PARENT_ID, DOC_ID, PART_ID, PART_PARENT_ID, DOC_DETAIL_ID, PRICE, NAC, QUANT,
DISCOUNT, SUMMA, SUMMA_O, DCARD, WARE_ID, PRICE_O, PRICE_Z, PRICE_R, BARCODE,
BARCODE1, GODENDO, SERIA, NDS, SUM_NDSO, SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD,
EDIZM, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID,
ORIG_COUNTRY_ID, Z_ID, SNAME, SIZG, SCOUNTRY, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY,
INSERTDT, KOEF, MOTHERPART_ID, DEP, BCODE_IZG, HUMAN_QUANT, SUM_DSC, CUSTOMDRAW,
STATUS, PART_TYPE, BASE_AGENT_ID, PACKET, VPART_ID)
select DD.PARENT_ID,
:O$DOC_ID,
PART_ID,
PART_PARENT_ID,
0,
DD.PRICE,
DD.NAC,
DD.QUANT,
DISCOUNT,
SUMMA,
SUMMA_O,
DCARD,
DD.WARE_ID,
DD.PRICE_O,
DD.PRICE_Z,
DD.PRICE_R,
DD.BARCODE,
DD.BARCODE1,
DD.GODENDO,
DD.SERIA,
DD.NDS,
DD.SUM_NDSO,
DD.SERT,
DD.DATESERT,
DD.KEMVSERT,
DD.SDSERT,
DD.REGN,
DD.NGTD,
DD.EDIZM,
DD.NAME_ID,
DD.IZG_ID,
DD.COUNTRY_ID,
DD.ORIG_CODE,
DD.ORIG_NAME_ID,
W.ORIG_IZG_ID,
W.ORIG_COUNTRY_ID,
DD.Z_ID,
DD.SNAME,
SIZG,
SCOUNTRY,
SORIG_NAME,
SORIG_IZG,
SORIG_COUNTRY,
DD.INSERTDT,
0,
0,
DD.DEP,
W.BARCODE,
DD.QUANT,
SUM_DSC,
null,
0,
P.PART_TYPE,
DD.BASE_AGENT_ID,
P.PACKET,
0
from VW_DOC_DETAIL_DELETED DD
left join WARES W on DD.WARE_ID = W.ID
left join PARTS P on DD.PART_ID = P.ID
where DD.DOC_ID = :DOC_ID;
/* Проводим */
execute procedure PR_DOC_COMMIT(:O$DOC_ID, :SESSION_ID);
update DOCS
set COMMENTS = :COMMENTS || '(Дубль чека ID=' || cast(:DOC_ID as DM_TEXT) || ')',
SUMM1 = :SUMM1,
SUMM2 = :SUMM2,
SUMM3 = :SUMM3,
SUMM4 = :SUMM3,
VSHIFT = :VSHIFT,
DEVICE_NUM = :DEVICE_NUM,
VNUM = :VNUM,
BONUS = :BONUS,
CAPTION = :CAPTION,
SUMMA = :SUMMA,
SUMMA_O = :SUMMA_O,
COMMITDATE = :COMMITDATE
where ID = :O$DOC_ID;
if (abs((select abs(sum(SUMMA))
from DOC_DETAIL
where DOC_ID = :O$DOC_ID) - abs(:SUMMA)) > 0.01) then
update DOC_DETAIL
set SUMMA = SUMMA + SUM_DSC
where DOC_ID = :O$DOC_ID;
suspend;
end
/* Возвращаем режим как было */
update PARAMS
set PARAM_VALUE = :PARAM_VALUE
where PARAM_ID = 'RASHOD_MINUS';
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT SELECT,UPDATE ON PARAMS TO PROCEDURE PR_CREATE_DUPLICATE_CHECK;
GRANT SELECT,UPDATE ON DOCS TO PROCEDURE PR_CREATE_DUPLICATE_CHECK;
GRANT EXECUTE ON PROCEDURE PR_NEWDOC TO PROCEDURE PR_CREATE_DUPLICATE_CHECK;
GRANT INSERT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_CREATE_DUPLICATE_CHECK;
GRANT SELECT ON VW_DOC_DETAIL TO PROCEDURE PR_CREATE_DUPLICATE_CHECK;
GRANT SELECT ON WARES TO PROCEDURE PR_CREATE_DUPLICATE_CHECK;
GRANT SELECT ON VW_DOC_DETAIL_DELETED TO PROCEDURE PR_CREATE_DUPLICATE_CHECK;
GRANT SELECT ON PARTS TO PROCEDURE PR_CREATE_DUPLICATE_CHECK;
GRANT EXECUTE ON PROCEDURE PR_DOC_COMMIT TO PROCEDURE PR_CREATE_DUPLICATE_CHECK;
GRANT SELECT,UPDATE ON DOC_DETAIL TO PROCEDURE PR_CREATE_DUPLICATE_CHECK;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE PR_CREATE_DUPLICATE_CHECK TO SYSDBA;
Как использовать
Запустите процедуру, укажите ID чека продажи или возврата и кол-во дублей. Выполните процедуру, подтвердите транзакцию
Исправление расхождений из-за неправильного округления
Процедура для испраления расхождения путем исправления суммы скидки чека
SET TERM ^ ;
create or alter procedure PR_REPAIR_DIFFERENCE_BY_DOC (
ZDOC_ID DM_ID,
DIFFERENCE DM_DOUBLE)
returns (
DOC_DETAIL_ID DM_ID,
DOC_ID DM_ID,
SUMMA DM_DOUBLE,
TEST DM_DOUBLE,
SUM_DSC DM_DOUBLE)
as
declare variable COMMITDATE DM_DATETIME;
declare variable VSHIFT DM_ID;
declare variable DEVICE_NUM DM_TEXT;
begin
select z.commitdate, z.device_num, z.vshift from docs z where z.id=:zdoc_id into
:commitdate, :device_num, :vshift;
select first 1 d.id, d.summa, d.sum_dsc
from docs d where 1=1
and d.doc_type in (3)
and d.device_num=:device_num
and d.vshift=:vshift
and d.status=1
and cast(d.commitdate as dm_date) between cast(:commitdate as dm_date) -7 and cast(:commitdate as dm_date) +7
and (abs(d.summa) - abs(d.sum_dsc) - abs(coalesce(:difference,0))) > 0.01
order by abs(d.sum_dsc) desc
into
:doc_id, :summa, :sum_dsc;
select first 1 dd.id from doc_detail dd where 1=1
and dd.doc_id = :doc_id
and (abs(dd.summa) - abs(dd.sum_dsc) - abs(coalesce(:difference,0))) > 0.01
order by abs(dd.sum_dsc) desc
into
:doc_detail_id;
if ((:doc_id is not null) and (:doc_detail_id is not null)) then
begin
update docs ud set
ud.summa=ud.summa+coalesce(:difference,0),
ud.summ1=ud.summ1+coalesce(:difference,0),
ud.sum_dsc=ud.sum_dsc+coalesce(:difference,0)
where ud.id=:doc_id;
update doc_detail udd set
udd.summa=udd.summa+coalesce(:difference,0),
udd.sum_dsc=udd.sum_dsc+coalesce(:difference,0)
where udd.id=:doc_detail_id;
end
suspend;
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT SELECT,UPDATE ON DOCS TO PROCEDURE PR_REPAIR_DIFFERENCE_BY_DOC;
GRANT SELECT,UPDATE ON DOC_DETAIL TO PROCEDURE PR_REPAIR_DIFFERENCE_BY_DOC;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE PR_REPAIR_DIFFERENCE_BY_DOC TO SYSDBA;
Как использовать
Нужно указать doc_id z-отчета по которому есть расхождение и сумму, на которую нужно это расхождение исправить.
Теги: дублирование чека, дубль, задублировать чек
Автоматическое исправление отмененных чеков
- Устанавливаем в клиентской базе процедуру PR_CREATE_DUPLICATE_CHECK, описанную выше.
- Устанавливаем процедуру PR_CHECK_ZDOCS
SET TERM ^ ;
create or alter procedure PR_CHECK_ZDOCS (
ZDOC_ID DM_ID,
STATUS integer = -1)
returns (
DEVICE_NUM DM_TEXT,
VSHIFT DM_ID,
COMMITDATE DM_DATETIME,
ZSUMM1 DM_DOUBLE,
ZSUMM2 DM_DOUBLE,
ZSUMM3 DM_DOUBLE,
ZSUMM4 DM_DOUBLE,
CSUMM1 DM_DOUBLE,
CSUMM2 DM_DOUBLE,
CSUMM3 DM_DOUBLE,
CSUMM4 DM_DOUBLE,
VSUMM1 DM_DOUBLE,
VSUMM2 DM_DOUBLE,
VSUMM3 DM_DOUBLE,
VSUMM4 DM_DOUBLE,
HSUMM DM_DOUBLE,
RASH_NAL DM_DOUBLE,
RASH_BEZNAL DM_DOUBLE,
DOC_ID DM_ID,
OUT_DOC_ID DM_ID)
as
begin
/* данные z-отчету */
select DZ.DEVICE_NUM,
DZ.VSHIFT,
DZ.COMMITDATE,
coalesce(DZ.SUMM1, 0),
coalesce(DZ.SUMM2, 0),
coalesce(DZ.SUMM3, 0),
coalesce(DZ.SUMM4, 0)
from DOCS DZ
where 1 = 1
and DZ.ID = :ZDOC_ID
and DZ.DOC_TYPE in (13)
into :DEVICE_NUM,
:VSHIFT,
:COMMITDATE,
:ZSUMM1,
:ZSUMM2,
:ZSUMM3,
:ZSUMM4;
/* суммы по чекам */
select coalesce(-sum(DC.SUMM1), 0),
coalesce(-sum(DC.SUMM2), 0),
coalesce(-sum(DC.SUMM3), 0),
coalesce(-sum(DC.SUMM4), 0)
from DOCS DC
where 1 = 1
and DC.VSHIFT = :VSHIFT
and DC.DEVICE_NUM = :DEVICE_NUM
and DC.DOC_TYPE in (3)
and DC.STATUS = 1
and cast(DC.COMMITDATE as DM_DATE) between cast(:COMMITDATE as DM_DATE) - 7 and cast(:COMMITDATE as DM_DATE) + 7
into :CSUMM1,
:CSUMM2,
:CSUMM3,
:CSUMM4;
/* суммы по возвратам */
select coalesce(sum(DV.SUMM1), 0),
coalesce(sum(DV.SUMM2), 0),
coalesce(sum(DV.SUMM3), 0),
coalesce(sum(DV.SUMM4), 0)
from DOCS DV
where 1 = 1
and DV.VSHIFT = :VSHIFT
and DV.DEVICE_NUM = :DEVICE_NUM
and DV.DOC_TYPE in (9)
and DV.STATUS = 1
and cast(DV.COMMITDATE as DM_DATE) between cast(:COMMITDATE as DM_DATE) - 7 and cast(:COMMITDATE as DM_DATE) + 7
into :VSUMM1,
:VSUMM2,
:VSUMM3,
:VSUMM4;
/* суммы по документам кассы*/
select coalesce(-sum(H.SUMMA), 0)
from CASH_DOCS H
where 1 = 1
and H.VSHIFT = :VSHIFT
and H.DEVICE_NUM = :DEVICE_NUM
and H.DOC_TYPE in (1, 2)
and H.STATUS = 1
and H.SUMMA < :ZSUMM1 /* чтобы z-отчеты не попали */
and cast(H.COMMITDATE as DM_DATE) between cast(:COMMITDATE as DM_DATE) - 7 and cast(:COMMITDATE as DM_DATE) + 7
into :HSUMM;
RASH_NAL = :ZSUMM1 - (:CSUMM1 - :VSUMM1 + :HSUMM);
RASH_BEZNAL = :ZSUMM3 - (:CSUMM1 + :CSUMM2 - :VSUMM1 - :VSUMM2) - :RASH_NAL;
/*
summ1 - наличность (ПРОДАЖИ_НАЛ - ВОЗВРАТЫ_НАЛ + ВНЕСЕНИЯ - ИЗЪЯТИЯ)
summ2 - сменный итог (ПРОДАЖИ_ВСЕ_ВИДЫ_ОПЛАТ)
summ3 - выручка (ПРОДАЖИ_ВСЕ_ВИДЫ_ОПЛАТ - ВОЗВРАТЫ_ВСЕ_ВИДЫ_ОПЛАТ)
*/
if ((:RASH_NAL > 0.1) or (:RASH_BEZNAL > 0.1)) then
select first 1 DS.ID
from DOCS DS
where 1 = 1
and DS.VSHIFT = :VSHIFT
and DS.DEVICE_NUM = :DEVICE_NUM
and DS.DOC_TYPE in (3)
and DS.STATUS = :STATUS
and cast(DS.COMMITDATE as DM_DATE) between cast(:COMMITDATE as DM_DATE) - 7 and cast(:COMMITDATE as DM_DATE) + 7
and ((abs(DS.SUMM1 + :RASH_NAL) < 1
and abs(DS.SUMM1) > 0.1) or (abs(DS.SUMM2 + :RASH_BEZNAL) < 1
and abs(DS.SUMM2) > 0.1))
into :DOC_ID;
if (:DOC_ID is not null) then
select O$DOC_ID
from PR_CREATE_DUPLICATE_CHECK(:DOC_ID, 1)
into :OUT_DOC_ID;
suspend;
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT SELECT ON DOCS TO PROCEDURE PR_CHECK_ZDOCS;
GRANT SELECT ON CASH_DOCS TO PROCEDURE PR_CHECK_ZDOCS;
GRANT EXECUTE ON PROCEDURE PR_CREATE_DUPLICATE_CHECK TO PROCEDURE PR_CHECK_ZDOCS;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE PR_CHECK_ZDOCS TO SYSDBA;
