Версия 17:15, 11 августа 2016

Поиск расхождений по чекам в базе

Расхождения в базе между 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
  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)
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 <> 1
        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;

• Устанавливаем триггер на закрытие смены DOCS_BIU0_CHECK_DOCS

SET SQL DIALECT 3;

SET TERM ^ ;

CREATE OR ALTER TRIGGER DOCS_BIU0_CHECK_DOCS FOR DOCS
ACTIVE BEFORE INSERT OR UPDATE POSITION 999
as

declare variable DEVICE_NUM DM_TEXT;
declare variable VSHIFT DM_ID;
declare variable COMMITDATE DM_DATETIME;
declare variable ZSUMM1 DM_DOUBLE;
declare variable ZSUMM2 DM_DOUBLE;
declare variable ZSUMM3 DM_DOUBLE;
declare variable ZSUMM4 DM_DOUBLE;
declare variable CSUMM1 DM_DOUBLE;
declare variable CSUMM2 DM_DOUBLE;
declare variable CSUMM3 DM_DOUBLE;
declare variable CSUMM4 DM_DOUBLE;
declare variable VSUMM1 DM_DOUBLE;
declare variable VSUMM2 DM_DOUBLE;
declare variable VSUMM3 DM_DOUBLE;
declare variable VSUMM4 DM_DOUBLE;
declare variable HSUMM DM_DOUBLE;
declare variable RASH_NAL DM_DOUBLE;
declare variable RASH_BEZNAL DM_DOUBLE;
declare variable DOC_ID DM_ID;
declare variable OUT_DOC_ID DM_ID;

begin
 if (new.DOC_TYPE = 13) then
  select DEVICE_NUM,
         VSHIFT,
         COMMITDATE,
         ZSUMM1,
         ZSUMM2,
         ZSUMM3,
         ZSUMM4,
         CSUMM1,
         CSUMM2,
         CSUMM3,
         CSUMM4,
         VSUMM1,
         VSUMM2,
         VSUMM3,
         VSUMM4,
         HSUMM,
         RASH_NAL,
         RASH_BEZNAL,
         DOC_ID,
         OUT_DOC_ID
  from PR_CHECK_ZDOCS(new.ID)
  into :DEVICE_NUM,
       :VSHIFT,
       :COMMITDATE,
       :ZSUMM1,
       :ZSUMM2,
       :ZSUMM3,
       :ZSUMM4,
       :CSUMM1,
       :CSUMM2,
       :CSUMM3,
       :CSUMM4,
       :VSUMM1,
       :VSUMM2,
       :VSUMM3,
       :VSUMM4,
       :HSUMM,
       :RASH_NAL,
       :RASH_BEZNAL,
       :DOC_ID,
       :OUT_DOC_ID;
end
^

SET TERM ; ^