Дублирование чека
Материал из wiki.standart-n.ru
Версия от 14:10, 15 июля 2016; Aleksnick (обсуждение | вклад)
Содержание
Поиск расхождений по чекам в базе
Расхождения в базе между 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 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; 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-отчета по которому есть расхождение и сумму, на которую нужно это расхождение исправить.
Теги: дублирование чека, дубль, задублировать чек