Версия 18:11, 13 января 2016

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

Расхождения в базе между z-отчетами и тов. отчетами можно найти спомощью следующего запроса. Если проблемный чек удален, то скорей всего нужно его продублировать. Если проблемный чек не удален, то скорей всего ккм его продублирован. Нужно его продублировать, а затем сделать возврат.

Запрос для клиентской базы

 
select
r.zdate,
r.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.zvnum 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)
from (
  select
  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 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
)

Запрос для сводной базы

 
select
r.g$profile_id,
r.caption,
r.zdate,
r.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.zvnum 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)
from (
  select
  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,
  d.g$profile_id as g$profile_id,
  (select g.caption from g$profiles g where g.id=d.g$profile_id) as caption,
  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 - 31 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 doc.g$profile_id=r.g$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
)

Исправление расхождений

Дублирование чека

Иногда возникает необходимость полностью задублировать чек в базе, при этом не проводя его ни по ККМ ни по банковскому терминалу. Для этого выполните скрипт ниже

Создание процедуры

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

    -- убрала это, не правильно встает summa в doc_detail, 20160104 Лена Е.
    -- 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,9) 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 d.summa 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 dd.summa 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-отчета по которому есть расхождение и сумму, на которую нужно это расхождение исправить.

Теги: дублирование чека, дубль, задублировать чек