Дублирование чека — различия между версиями

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
(→‎Процедура для клиентской базы)
(→‎Запрос для сводной базы)
Строка 111: Строка 111:
 
</nowiki>
 
</nowiki>
  
−
==Запрос для сводной базы==
+
==Процедура для сводной базы==
 
  <nowiki>  
 
  <nowiki>  
−
select
+
SET TERM ^ ;
−
r.g$profile_id,
+
 
−
r.caption,
+
create or alter procedure PR_GET_DIFFERENCE_BY_DOCS (
 +
    DAYS_AGO DM_ID = 31)
 +
returns (
 +
    PROFILE_ID DM_ID,
 +
    SPROFILE DM_TEXT,
 +
    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,
 +
    CMD DM_TEXT)
 +
as
 +
begin
 +
 
 +
for select
 +
r.profile_id,
 +
r.sprofile,
 
r.zdate,
 
r.zdate,
 
r.zid,
 
r.zid,
Строка 126: Строка 148:
 
doc.summ1 as nal,
 
doc.summ1 as nal,
 
doc.summ2 as beznal,
 
doc.summ2 as beznal,
−
iif(doc.status is not null, iif(doc.status = 1, 'Не удален', 'Удален'), null)
+
iif(doc.status is not null, iif(doc.status = 1, 'Не удален', 'Удален'), null) as status,
 +
iif(doc.id is null,
 +
  '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)))||''''');'');',
 +
  '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)))||''''');'');'
 +
  )
 +
as cmd
 
from (
 
from (
 
   select
 
   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,
 
   cast(d.commitdate as dm_date) as zdate,
 
   d.id as zid,
 
   d.id as zid,
Строка 136: Строка 165:
 
   d.summ1 as zsumm1,
 
   d.summ1 as zsumm1,
 
   d.summ2 as zsumm2,
 
   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
 
   coalesce((select sum(-c1.summ1) from docs c1 where 1=1
 
     and c1.doc_type in (3,9)
 
     and c1.doc_type in (3,9)
Строка 167: Строка 194:
 
   and d.doc_type=13
 
   and d.doc_type=13
 
   and d.status=1
 
   and d.status=1
−
   and cast(d.commitdate as dm_date) between current_date - 31 and current_date + 1
+
   and cast(d.commitdate as dm_date) between current_date - :days_ago and current_date + 1
 
   order by d.commitdate desc
 
   order by d.commitdate desc
 
) r
 
) r
Строка 174: Строка 201:
 
   and doc.vshift=r.vshift
 
   and doc.vshift=r.vshift
 
   and doc.device_num=r.device_num
 
   and doc.device_num=r.device_num
−
   and doc.g$profile_id=r.g$profile_id
+
   and doc.g$profile_id=r.profile_id
 
   and cast(doc.commitdate as dm_date) between r.zdate -7 and r.zdate +7
 
   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))
 
   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))
Строка 183: Строка 210:
 
   abs(r.zsumm2 - r.csumm2) > 0.01
 
   abs(r.zsumm2 - r.csumm2) > 0.01
 
)
 
)
 +
into
 +
  :profile_id, :sprofile, :zdate, :zid, :rash_nal, :rash_beznal, :vnum, :vshift, :device_num, :doc_id, :nal, :beznal, :status, :cmd
 +
do
 +
 +
  suspend;
 +
end^
 +
 +
SET TERM ; ^
 +
 +
/* Following GRANT statetements are generated automatically */
 +
 +
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;
 +
 +
/* Existing privileges on this procedure */
 +
 +
GRANT EXECUTE ON PROCEDURE PR_GET_DIFFERENCE_BY_DOCS TO SYSDBA;
 
</nowiki>
 
</nowiki>
  

Версия 13:08, 15 января 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,
    CMD1 DM_TEXT,
    CMD2 DM_TEXT)
as
begin

for 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) as status,
('execute procedure PR_REPAIR_DIFFERENCE_BY_DOC('||(cast(r.zid as VARCHAR(255)))||', '||(cast(coalesce(doc.summ1,0) as VARCHAR(255)))||');') as cmd1,
iif(doc.id is not null, 'execute procedure PR_CREATE_DUPLICATE_CHECK('||(cast(doc.id as VARCHAR(255)))||');','') as cmd2
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
)
into
  :zdate, :zid, :rash_nal, :rash_beznal, :vnum, :vshift, :device_num, :doc_id, :nal, :beznal, :status, :cmd1, :cmd2
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;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_GET_DIFFERENCE_BY_DOCS TO SYSDBA;

Процедура для сводной базы

 
SET TERM ^ ;

create or alter procedure PR_GET_DIFFERENCE_BY_DOCS (
    DAYS_AGO DM_ID = 31)
returns (
    PROFILE_ID DM_ID,
    SPROFILE DM_TEXT,
    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,
    CMD DM_TEXT)
as
begin

for select
r.profile_id,
r.sprofile,
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) as status,
iif(doc.id is null,
  '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)))||''''');'');',
  '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)))||''''');'');'
  )
as cmd
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
  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.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
)
into
  :profile_id, :sprofile, :zdate, :zid, :rash_nal, :rash_beznal, :vnum, :vshift, :device_num, :doc_id, :nal, :beznal, :status, :cmd
do

  suspend;
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

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;

/* 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 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)
    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-отчета по которому есть расхождение и сумму, на которую нужно это расхождение исправить. 

Теги: дублирование чека, дубль, задублировать чек
Источник — «http://wiki.standart-n.ru/index.php?title=Дублирование_чека&oldid=3405»