Версия 12:05, 14 декабря 2015

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

Расхождения в базе между 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,
doc.vnum as vnum,
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 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
)
 

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

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

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

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

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

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

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_CREATE_DUPLICATE_CHECK TO SYSDBA;
 

Как использовать

Запустите процедуру, укажите ID чека продажи или возврата и кол-во дублей. Выполните процедуру, подтвердите транзакцию

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