Установка Единого Окна
Материал из wiki.standart-n.ru
Версия от 15:46, 14 июля 2016; Aleksnick (обсуждение | вклад)
Сжатие товаров в одиночной аптеки перед внедрением единого окна
Лешины скрипты
create or alter procedure PR_AUTO_WARESLINK (
DOC_ID DM_ID_NULL,
ACTIVE_ID DM_ID_NULL)
as
declare variable DDA_BARCODE DM_TEXT1024;
declare variable DDA_WARE_ID DM_UUID_NULL;
declare variable DDA_ORIG_COUNTRYID DM_UUID_NULL;
declare variable DDA_ORIG_IZGID DM_UUID_NULL;
declare variable DDA_ORIG_NAMEID DM_UUID_NULL;
declare variable DDA_COUNTRY_ID DM_UUID_NULL;
declare variable DDA_IZG_ID DM_UUID_NULL;
declare variable DDA_NAME_ID DM_UUID_NULL;
declare variable DDA_Z_ID DM_UUID_NULL;
declare variable DDA_SKLAD_ID DM_UUID_NULL;
declare variable DDALOG_BARCODE DM_TEXT1024;
declare variable DDALOG_WARE_ID DM_UUID_NULL;
declare variable DDALOG_ORIG_COUNTRYID DM_UUID_NULL;
declare variable DDALOG_ORIG_IZGID DM_UUID_NULL;
declare variable DDALOG_ORIG_NAMEID DM_UUID_NULL;
declare variable DDALOG_COUNTRY_ID DM_UUID_NULL;
declare variable DDALOG_IZG_ID DM_UUID_NULL;
declare variable DDALOG_NAME_ID DM_UUID_NULL;
declare variable DDALOG_Z_ID DM_UUID_NULL;
declare variable DDALOG_SKLAD_ID DM_UUID_NULL;
declare variable DDALOG2_BARCODE DM_TEXT1024;
declare variable DDALOG2_WARE_ID DM_UUID_NULL;
declare variable DDALOG2_ORIG_COUNTRYID DM_UUID_NULL;
declare variable DDALOG2_ORIG_IZGID DM_UUID_NULL;
declare variable DDALOG2_ORIG_NAMEID DM_UUID_NULL;
declare variable DDALOG2_COUNTRY_ID DM_UUID_NULL;
declare variable DDALOG2_IZG_ID DM_UUID_NULL;
declare variable DDALOG2_NAME_ID DM_UUID_NULL;
declare variable DDALOG2_Z_ID DM_UUID_NULL;
declare variable DDALOG2_SKLAD_ID DM_UUID_NULL;
begin
select BCODE_IZG,
WARE_ID,
(select VAL_ID
from PR_GETVAL_ID(DDA.SNAME, 0, DDA.PART_TYPE)) as NAME_ID,
(select VAL_ID
from PR_GETVAL_ID(DDA.SIZG, 3, DDA.PART_TYPE)) as IZG_ID,
(select VAL_ID
from PR_GETVAL_ID(DDA.SCOUNTRY, 2, DDA.PART_TYPE)) as COUNTRY_ID,
(select VAL_ID
from PR_GETVAL_ID(DDA.SORIG_NAME, 1, DDA.PART_TYPE)) as ORIG_NAME_ID,
(select VAL_ID
from PR_GETVAL_ID(DDA.SORIG_IZG, 6, DDA.PART_TYPE)) as ORGI_IZG_ID,
(select VAL_ID
from PR_GETVAL_ID(DDA.SORIG_COUNTRY, 5, DDA.PART_TYPE)) as ORIG_COUNTRY_ID,
Z_ID,
SKLAD_ID
from DOC_DETAIL_ACTIVE DDA
where DOC_ID = :DOC_ID
and ID = :ACTIVE_ID
into DDA_BARCODE,
DDA_WARE_ID,
DDA_NAME_ID,
DDA_IZG_ID,
DDA_COUNTRY_ID,
DDA_ORIG_NAMEID,
DDA_ORIG_IZGID,
DDA_ORIG_COUNTRYID,
DDA_Z_ID,
DDA_SKLAD_ID;
select first 1 BCODE_IZG,
WARE_ID,
(select VAL_ID
from PR_GETVAL_ID(DDAL.SNAME, 0, DDAL.PART_TYPE)) as NAME_ID,
(select VAL_ID
from PR_GETVAL_ID(DDAL.SIZG, 3, DDAL.PART_TYPE)) as IZG_ID,
(select VAL_ID
from PR_GETVAL_ID(DDAL.SCOUNTRY, 2, DDAL.PART_TYPE)) as COUNTRY_ID,
(select VAL_ID
from PR_GETVAL_ID(DDAL.SORIG_NAME, 1, DDAL.PART_TYPE)) as ORIG_NAME_ID,
(select VAL_ID
from PR_GETVAL_ID(DDAL.SORIG_IZG, 6, DDAL.PART_TYPE)) as ORGI_IZG_ID,
(select VAL_ID
from PR_GETVAL_ID(DDAL.SORIG_COUNTRY, 5, DDAL.PART_TYPE)) as ORIG_COUNTRY_ID,
Z_ID,
SKLAD_ID
from DOC_DETAIL_ACTIVE_LOG DDAL
where DOC_ID = :DOC_ID
and ID = :ACTIVE_ID
order by LOG_ID
into DDALOG_BARCODE,
DDALOG_WARE_ID,
DDALOG_NAME_ID,
DDALOG_IZG_ID,
DDALOG_COUNTRY_ID,
DDALOG_ORIG_NAMEID,
DDALOG_ORIG_IZGID,
DDALOG_ORIG_COUNTRYID,
DDALOG_Z_ID,
DDALOG_SKLAD_ID;
select first 1 skip 1 BCODE_IZG,
WARE_ID,
(select VAL_ID
from PR_GETVAL_ID(DDAL.SNAME, 0, DDAL.PART_TYPE)) as NAME_ID,
(select VAL_ID
from PR_GETVAL_ID(DDAL.SIZG, 3, DDAL.PART_TYPE)) as IZG_ID,
(select VAL_ID
from PR_GETVAL_ID(DDAL.SCOUNTRY, 2, DDAL.PART_TYPE)) as COUNTRY_ID,
(select VAL_ID
from PR_GETVAL_ID(DDAL.SORIG_NAME, 1, DDAL.PART_TYPE)) as ORIG_NAME_ID,
(select VAL_ID
from PR_GETVAL_ID(DDAL.SORIG_IZG, 6, DDAL.PART_TYPE)) as ORGI_IZG_ID,
(select VAL_ID
from PR_GETVAL_ID(DDAL.SORIG_COUNTRY, 5, DDAL.PART_TYPE)) as ORIG_COUNTRY_ID,
Z_ID,
SKLAD_ID
from DOC_DETAIL_ACTIVE_LOG DDAL
where DOC_ID = :DOC_ID
and ID = :ACTIVE_ID
order by LOG_ID
into DDALOG2_BARCODE,
DDALOG2_WARE_ID,
DDALOG2_NAME_ID,
DDALOG2_IZG_ID,
DDALOG2_COUNTRY_ID,
DDALOG2_ORIG_NAMEID,
DDALOG2_ORIG_IZGID,
DDALOG2_ORIG_COUNTRYID,
DDALOG2_Z_ID,
DDALOG2_SKLAD_ID;
if (((DDA_WARE_ID <> '-1') and
(DDA_WARE_ID <> '0') and
(DDA_WARE_ID is not null)) and
((DDALOG2_WARE_ID <> '-1') and
(DDALOG2_WARE_ID <> '0') and
(DDALOG2_WARE_ID is not null)) and
(DDA_BARCODE = DDALOG2_BARCODE) and
(DDA_WARE_ID <> DDALOG2_WARE_ID)) then
begin
update WARES
set NAME_ID = :DDA_NAME_ID,
IZG_ID = :DDA_IZG_ID,
COUNTRY_ID = :DDA_COUNTRY_ID,
ORIG_NAME_ID = :DDA_ORIG_NAMEID,
ORIG_IZG_ID = :DDA_ORIG_IZGID,
ORIG_COUNTRY_ID = :DDA_ORIG_COUNTRYID,
BARCODE = :DDA_BARCODE
where ID = :DDALOG2_WARE_ID;
exit;
end
if ((DDALOG_WARE_ID = '0') or (DDALOG_WARE_ID = '-1') or (DDALOG_WARE_ID is null)) then
begin
insert into WARES_LOG (ID, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID, BARCODE, Z_ID,
SKLAD_ID)
values (:DDA_WARE_ID, :DDALOG_NAME_ID, :DDALOG_IZG_ID, :DDALOG_COUNTRY_ID, :DDALOG_ORIG_NAMEID, :DDALOG_ORIG_IZGID,
:DDALOG_ORIG_COUNTRYID, :DDALOG_BARCODE, :DDALOG_Z_ID, :DDALOG_SKLAD_ID);
end
else
if (((DDA_WARE_ID <> '0') and
(DDA_WARE_ID <> '-1') and
(DDA_WARE_ID is not null)) and
((DDA_NAME_ID <> DDALOG_NAME_ID) or (DDA_IZG_ID <> DDALOG_IZG_ID) or (DDA_COUNTRY_ID <> DDALOG_COUNTRY_ID) or (DDA_ORIG_NAMEID <> DDALOG_ORIG_NAMEID) or (DDA_ORIG_IZGID <> DDALOG_ORIG_IZGID) or (DDA_ORIG_COUNTRYID <> DDALOG_ORIG_COUNTRYID)) and
(DDA_BARCODE = DDALOG_BARCODE)) then
begin
update WARES
set NAME_ID = :DDA_NAME_ID,
IZG_ID = :DDA_IZG_ID,
COUNTRY_ID = :DDA_COUNTRY_ID,
ORIG_NAME_ID = :DDA_ORIG_NAMEID,
ORIG_IZG_ID = :DDA_ORIG_IZGID,
ORIG_COUNTRY_ID = :DDA_ORIG_COUNTRYID,
BARCODE = :DDA_BARCODE
where ID = :DDALOG_WARE_ID;
end
end;
grant execute
on procedure PR_GETVAL_ID to procedure PR_AUTO_WARESLINK;
grant select on DOC_DETAIL_ACTIVE to procedure PR_AUTO_WARESLINK;
grant select on DOC_DETAIL_ACTIVE_LOG to procedure PR_AUTO_WARESLINK;
grant insert on WARES_LOG to procedure PR_AUTO_WARESLINK;
grant select,
update on WARES to procedure PR_AUTO_WARESLINK;
grant execute
on procedure PR_AUTO_WARESLINK to SYSDBA;
create or alter procedure PR_MAKEGOODWAREVALUES(SNAME type of DM_TEXT, SORIG_NAME type of DM_TEXT, SIZG type of DM_TEXT, SORIG_IZG type of DM_TEXT, SCOUNTRY type of DM_TEXT, SORIG_COUNTRY type of DM_TEXT, ORIG_CODE type of DM_TEXT, ACTIVE_ID type of DM_ID, ALTTYPE DM_STATUS, BARCODE DM_TEXT1024 = '') returns(GOODNAME type of DM_TEXT, GOODIZG type of DM_TEXT, GOODCOUNTRY type of DM_TEXT, GOODBARCODE DM_TEXT)
as
declare variable WARE_ID type of DM_UUID_NULL;
declare variable NAME_ID type of DM_UUID_NULL;
declare variable ORIG_NAME_ID type of DM_UUID_NULL;
declare variable IZG_ID type of DM_UUID_NULL;
declare variable ORIG_IZG_ID type of DM_UUID_NULL;
declare variable COUNTRY_ID type of DM_UUID_NULL;
declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL;
begin
/*
select val_id from pr_getval_id(:sname,0,:alttype) into :name_id;
select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id;
select val_id from pr_getval_id(:sizg,3,:alttype) into :izg_id;
select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id;
select val_id from pr_getval_id(:scountry,2,:alttype) into :country_id;
select val_id from pr_getval_id(:sorig_country,5,:alttype) into :orig_country_id;
*/
select ID
from VALS
where VTYPE = 0
and ALTTYPE = :ALTTYPE
and SVALUE = :SNAME
into :NAME_ID;
select ID
from VALS
where VTYPE = 1
and ALTTYPE = :ALTTYPE
and SVALUE = :SORIG_NAME
into :ORIG_NAME_ID;
select ID
from VALS
where VTYPE = 3
and ALTTYPE = :ALTTYPE
and SVALUE = :SIZG
into :IZG_ID;
select ID
from VALS
where VTYPE = 6
and ALTTYPE = :ALTTYPE
and SVALUE = :SORIG_IZG
into :ORIG_IZG_ID;
select ID
from VALS
where VTYPE = 2
and ALTTYPE = :ALTTYPE
and SVALUE = :SCOUNTRY
into :COUNTRY_ID;
select ID
from VALS
where VTYPE = 5
and ALTTYPE = :ALTTYPE
and SVALUE = :SORIG_COUNTRY
into :ORIG_COUNTRY_ID;
select WARE_ID
from PR_GETWARE_BY_IDS(:NAME_ID, :IZG_ID, :COUNTRY_ID, :ORIG_CODE, :ORIG_NAME_ID, :ORIG_IZG_ID, :ORIG_COUNTRY_ID, 0, 1, :BARCODE)
into :WARE_ID;
if (WARE_ID is not null) then
begin
/* select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id; */
select SNAME,
SIZG,
SCOUNTRY,
BARCODE
from VW_WARES
where ID = :WARE_ID
into :GOODNAME,
:GOODIZG,
:GOODCOUNTRY,
:GOODBARCODE;
/* if ((active_id>0) and ((goodname<>sname) or (goodizg<>sizg) or (goodcountry<>scountry))) then */
/* insert into doc_detail_active_log(id,sname,sizg,scountry,part_type) values (:active_id,:sname,:sizg,:scountry,:alttype); */
/* goodname='2';--sname; */
if (GOODNAME is null) then
GOODNAME = SNAME;
if (GOODIZG is null) then
GOODIZG = SIZG;
if (GOODCOUNTRY is null) then
GOODCOUNTRY = SCOUNTRY;
if (GOODNAME is null) then
GOODNAME = SORIG_NAME;
if (GOODIZG is null) then
GOODIZG = SORIG_IZG;
if (GOODCOUNTRY is null) then
GOODCOUNTRY = SORIG_COUNTRY;
if (GOODBARCODE is null) then
GOODBARCODE = BARCODE;
suspend;
exit;
end
select WARE_ID
from PR_GETWARE_BY_IDS(:NAME_ID, :IZG_ID, :COUNTRY_ID, :ORIG_CODE, :ORIG_NAME_ID, :ORIG_IZG_ID, :ORIG_COUNTRY_ID, 0, null, :BARCODE)
into :WARE_ID;
if (WARE_ID is not null) then
begin
/* select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id; */
/* goodname='1';--sname; */
GOODNAME = SNAME;
GOODIZG = SIZG;
GOODCOUNTRY = SCOUNTRY;
GOODBARCODE = BARCODE;
suspend;
exit;
end
WARE_ID = null;
if (ORIG_NAME_ID is not null) then
begin
select first 1 ID
from WARES
where ORIG_NAME_ID = :ORIG_NAME_ID
order by ID desc
into :WARE_ID;
if (WARE_ID is null) then
select first 1 iif(ACTUAL_WARE_ID is null, ID, ACTUAL_WARE_ID)
from WARES_LOG
where ORIG_NAME_ID = :ORIG_NAME_ID
order by LOG_ID desc
into :WARE_ID;
end
if ((WARE_ID is null) and
(NAME_ID is not null)) then
begin
select first 1 ID
from WARES
where NAME_ID = :NAME_ID
into :WARE_ID;
if (WARE_ID is null) then
select first 1 iif(ACTUAL_WARE_ID is null, ID, ACTUAL_WARE_ID)
from WARES_LOG
where NAME_ID = :NAME_ID
order by LOG_ID desc
into :WARE_ID;
end
if (WARE_ID is null) then
begin
/* goodname='3';--sname; */
GOODNAME = SNAME;
NAME_ID = null;
end
else
begin
/* select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id; */
select W.NAME_ID,
V.SVALUE
from WARES W
left join VALS V on W.NAME_ID = V.ID
where W.ID = :WARE_ID
into :NAME_ID,
:GOODNAME;
end
WARE_ID = null;
if (ORIG_IZG_ID is not null) then
begin
select first 1 ID
from WARES
where ORIG_IZG_ID = :ORIG_IZG_ID
into :WARE_ID;
if (WARE_ID is null) then
select first 1 iif(ACTUAL_WARE_ID is null, ID, ACTUAL_WARE_ID)
from WARES_LOG
where ORIG_IZG_ID = :ORIG_IZG_ID
order by LOG_ID desc
into :WARE_ID;
end
if ((WARE_ID is null) and
(IZG_ID is not null)) then
begin
select first 1 ID
from WARES
where IZG_ID = :IZG_ID
into :WARE_ID;
if (WARE_ID is null) then
select first 1 iif(ACTUAL_WARE_ID is null, ID, ACTUAL_WARE_ID)
from WARES_LOG
where IZG_ID = :IZG_ID
order by LOG_ID desc
into :WARE_ID;
end
if (WARE_ID is null) then
GOODIZG = SIZG;
else
begin
/* select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id; */
select W.IZG_ID,
V.SVALUE
from WARES W
left join VALS V on W.IZG_ID = V.ID
where W.ID = :WARE_ID
into :IZG_ID,
:GOODIZG;
end
WARE_ID = null;
if (ORIG_COUNTRY_ID is not null) then
begin
select first 1 ID
from WARES
where ORIG_COUNTRY_ID = :ORIG_COUNTRY_ID
into :WARE_ID;
if (WARE_ID is null) then
select first 1 iif(ACTUAL_WARE_ID is null, ID, ACTUAL_WARE_ID)
from WARES_LOG
where ORIG_COUNTRY_ID = :ORIG_COUNTRY_ID
order by LOG_ID desc
into :WARE_ID;
end
if ((WARE_ID is null) and
(COUNTRY_ID is not null)) then
begin
select first 1 ID
from WARES
where COUNTRY_ID = :COUNTRY_ID
into :WARE_ID;
if (WARE_ID is null) then
select first 1 iif(ACTUAL_WARE_ID is null, ID, ACTUAL_WARE_ID)
from WARES_LOG
where COUNTRY_ID = :COUNTRY_ID
order by LOG_ID desc
into :WARE_ID;
end
if (WARE_ID is null) then
GOODCOUNTRY = SCOUNTRY;
else
begin
/* select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id; */
select W.COUNTRY_ID,
V.SVALUE
from WARES W
left join VALS V on W.COUNTRY_ID = V.ID
where W.ID = :WARE_ID
into :COUNTRY_ID,
:GOODCOUNTRY;
end
/* if ((active_id>0) and ((goodname<>sname) or (goodizg<>sizg) or (goodcountry<>scountry))) then */
/* insert into doc_detail_active_log(id,sname,sizg,scountry, part_type) values (:active_id,:sname,:sizg,:scountry,:alttype); */
/* goodname='3'; */
GOODBARCODE = BARCODE;
if (GOODNAME is null) then
GOODNAME = SNAME;
if (GOODIZG is null) then
GOODIZG = SIZG;
if (GOODCOUNTRY is null) then
GOODCOUNTRY = SCOUNTRY;
if (GOODNAME is null) then
GOODNAME = SORIG_NAME;
if (GOODIZG is null) then
GOODIZG = SORIG_IZG;
if (GOODCOUNTRY is null) then
GOODCOUNTRY = SORIG_COUNTRY;
suspend;
end;
create or alter procedure PR_GET_WARE (
SNAME type of DM_TEXT,
SIZG type of DM_TEXT,
SCOUNTRY type of DM_TEXT,
ORIG_CODE type of DM_TEXT = '',
SORIG_NAME type of DM_TEXT = '',
SORIG_IZG type of DM_TEXT = '',
SORIG_COUNTRY type of DM_TEXT = '',
BARCODE type of DM_TEXT = '',
Z_ID type of DM_ID = 0,
SKLAD_ID DM_TEXT = '',
ALTTYPE DM_STATUS = 0,
MNN DM_TEXT = '')
returns (
W_ID type of DM_UUID_NULL)
as
declare variable NAME_ID type of DM_UUID_NULL;
declare variable IZG_ID type of DM_UUID_NULL;
declare variable COUNTRY_ID type of DM_UUID_NULL;
declare variable ORIG_NAME_ID type of DM_UUID_NULL;
declare variable ORIG_IZG_ID type of DM_UUID_NULL;
declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL;
begin
select VAL_ID
from PR_GETVAL_ID(:SNAME, 0, :ALTTYPE, :MNN)
into :NAME_ID;
select VAL_ID
from PR_GETVAL_ID(:SIZG, 3, :ALTTYPE)
into :IZG_ID;
select VAL_ID
from PR_GETVAL_ID(:SCOUNTRY, 2, :ALTTYPE)
into :COUNTRY_ID;
select VAL_ID
from PR_GETVAL_ID(:SORIG_NAME, 1, :ALTTYPE)
into :ORIG_NAME_ID;
select VAL_ID
from PR_GETVAL_ID(:SORIG_IZG, 6, :ALTTYPE)
into :ORIG_IZG_ID;
select VAL_ID
from PR_GETVAL_ID(:SORIG_COUNTRY, 5, :ALTTYPE)
into :ORIG_COUNTRY_ID;
/*
select first 1 id from wares where NAME_ID=:name_id and IZG_ID=:izg_id and COUNTRY_ID=:country_id and
ORIG_CODE=:orig_code and ORIG_NAME_ID=:orig_name_id and
ORIG_IZG_ID=:orig_izg_id and ORIG_COUNTRY_ID=:orig_country_id -- into :w_id;
and barcode=:barcode into :w_id;
*/
select ID
from WARES
where NAME_ID = :NAME_ID
and IZG_ID = :IZG_ID
and COUNTRY_ID = :COUNTRY_ID
and BARCODE = :BARCODE
into :W_ID;
if (W_ID is null) then
begin
/* exception EX_WRONG_OPER; */
/* w_id=gen_id(gen_wares_id,1); */
W_ID = uuid_to_char(gen_uuid());
insert into WARES (ID, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID, BARCODE,
Z_ID, SKLAD_ID)
values (:W_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :ORIG_CODE, :ORIG_NAME_ID, :ORIG_IZG_ID, :ORIG_COUNTRY_ID, :BARCODE,
:Z_ID, :SKLAD_ID);
end
suspend;
end;
create or alter procedure PR_GETWARE_BY_IDS (
NAME_ID type of DM_UUID_NULL,
IZG_ID type of DM_UUID_NULL,
COUNTRY_ID type of DM_UUID_NULL,
ORIG_CODE type of DM_TEXT,
ORIG_NAME_ID type of DM_UUID_NULL,
ORIG_IZG_ID type of DM_UUID_NULL,
ORIG_COUNTRY_ID type of DM_UUID_NULL,
DOINSERT type of DM_STATUS,
SEARCHINLOGS type of DM_STATUS,
BARCODE DM_TEXT)
returns (
WARE_ID type of DM_UUID_NULL)
as
begin
if (ORIG_CODE is null) then
ORIG_CODE = '';
if (SEARCHINLOGS is null) then
SEARCHINLOGS = 0;
if (SEARCHINLOGS = 1) then
begin
select first 1 iif(ACTUAL_WARE_ID is null, ID, ACTUAL_WARE_ID)
from WARES_LOG
where NAME_ID = :NAME_ID
and IZG_ID = :IZG_ID
and COUNTRY_ID = :COUNTRY_ID
and BARCODE = :BARCODE
order by LOG_INSERTDT desc
into :WARE_ID;
select ID
from WARES
where ID = :WARE_ID
into :WARE_ID;
end
else
begin
select first 1 ID
from WARES
where NAME_ID = :NAME_ID
and IZG_ID = :IZG_ID
and COUNTRY_ID = :COUNTRY_ID
and BARCODE = :BARCODE
into :WARE_ID;
end
if ((WARE_ID is null) and
(DOINSERT = 1) and
(SEARCHINLOGS <> 1)) then
begin
/* ware_id=gen_id(gen_wares_id,1); */
WARE_ID = uuid_to_char(gen_uuid());
insert into WARES (ID, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID, Z_ID,
SKLAD_ID, BARCODE)
values (:WARE_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :ORIG_CODE, :ORIG_NAME_ID, :ORIG_IZG_ID, :ORIG_COUNTRY_ID, 0, '',
:BARCODE);
end
suspend;
end;
create or alter procedure PR_DOC_COMMIT (
DOC_ID type of DM_ID,
SESSION_ID type of DM_ID)
as
declare variable WRONG_WARES DM_STATUS;
declare variable BASE_TYPE type of DM_STATUS;
declare variable SUMMA type of DM_DOUBLE;
declare variable SUMMA_O type of DM_DOUBLE;
declare variable SUM_NDSO type of DM_DOUBLE;
declare variable DSCSUMMA type of DM_DOUBLE;
declare variable SUM_DSC type of DM_DOUBLE;
declare variable PRICE_TYPE DM_STATUS;
declare variable CONTRACT_ID DM_ID;
declare variable W_PARAM DM_STATUS;
declare variable WARES_MODE DM_STATUS;
begin
select DT.BASE_TYPE,
D.PRICE_TYPE,
D.CONTRACT_ID
from DOCS D
left join DOC_TYPES DT on D.DOC_TYPE = DT.ID
where D.ID = :DOC_ID
into :BASE_TYPE,
:PRICE_TYPE,
:CONTRACT_ID;
if (BASE_TYPE in (1, 3, 6, 8)) then
begin
WRONG_WARES = 0;
WARES_MODE = 1;
select PARAM_VALUE
from PR_GETPARAMVALUE('WARES_MODE', 0)
into WARES_MODE;
if (WARES_MODE = 0) then
begin
select count(ID)
from DOC_DETAIL_ACTIVE
where DOC_ID = :DOC_ID
and ((WARE_ID = '0') or (WARE_ID = '-1') or (WARE_ID = ''))
into WRONG_WARES;
if (WRONG_WARES > 0) then
exception EX_WRONG_OPER;
end
execute procedure PR_DOC_PRIHOD_COMMIT(:DOC_ID, 0);
end
else
if (BASE_TYPE in (2, 7, 9)) then
execute procedure PR_DOC_RASHOD_COMMIT(:DOC_ID, 0);
else
exception EX_WRONGDOC_BASE_TYPE;
delete from DOC_DETAIL_ACTIVE
where DOC_ID = :DOC_ID;
if (BASE_TYPE = 3) then
begin
delete from WAREBASE
where PART_ID in (select PART_ID
from DOC_DETAIL
where DOC_ID = :DOC_ID
and QUANT < 0
and PART_TYPE = 1);
end
select sum(SUMMA),
sum(SUMMA_O),
sum(SUM_NDSO),
sum(PRICE * QUANT / 10000),
sum(SUM_DSC)
from DOC_DETAIL
where DOC_ID = :DOC_ID
into :SUMMA,
:SUMMA_O,
:SUM_NDSO,
:DSCSUMMA,
:SUM_DSC;
DSCSUMMA = DSCSUMMA * 10000;
update DOCS
set COMMITSESSION_ID = :SESSION_ID,
STATUS = 1,
POSTDT = 'now',
CHECKDATA = (select CHECKDATA
from PR_DOCCHECKDATA(:DOC_ID)),
SUMMA = :SUMMA,
SUMMA_O = :SUMMA_O,
SUM_NDSO = :SUM_NDSO,
CALCSUMMA = :DSCSUMMA,
SUM_DSC = :SUM_DSC,
CASHDATA = (iif(:BASE_TYPE = 1, -:SUMMA, null))
where ID = :DOC_ID;
execute procedure PR_UPDBLOCKINFO_BY_DOC(:DOC_ID, 1);
/* if ((base_type = 2) and (contract_id<>0)) then */
if (CONTRACT_ID <> 0) then
begin
if (BASE_TYPE = 2) then
execute procedure PR_CREDITCOMMIT(:CONTRACT_ID);
if (BASE_TYPE in (8, 9)) then
execute procedure PR_CREDITREINITCOMMIT(:CONTRACT_ID, :SESSION_ID);
/* execute procedure PR_CREDITCOMMIT(:contract_id); */
end
execute procedure PR_DOEVENT('EV_DOCCOMMIT');
end;
create or alter trigger DOC_DETAIL_ACTIVE_BI for DOC_DETAIL_ACTIVE
active before insert position 0
as
begin
if (new.ID is null) then
new.ID = gen_id(GEN_DOC_DETAIL_ACTIVE_ID, 1);
new.INSERTDT = 'now';
if (new.BASE_AGENT_ID is null) then
new.BASE_AGENT_ID = 0;
if (new.STATUS is null) then
new.STATUS = 0;
if (new.PARENT_ID is null) then
new.PARENT_ID = 0;
if (new.PART_TYPE is null) then
new.PART_TYPE = 0;
/* первая строчка = до привидения, оригианльные значения накладной */
/* select first 1 ID from WARES where NAME_ID = new.NAME_ID and IZG_ID = new.IZG_ID and COUNTRY_ID = new.COUNTRY_ID and BARCODE = new.BCODE_IZG into new.WARE_ID; */
insert into DOC_DETAIL_ACTIVE_LOG (ID, 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, BCODE_IZG, 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, SKLAD_ID, SNAME, SIZG, SCOUNTRY, SORIG_NAME,
SORIG_IZG, SORIG_COUNTRY, INSERTDT, INFO, KOEF, MOTHERPART_ID, DEP, SUM_DSC,
HUMAN_QUANT, CUSTOMDRAW, PART_TYPE, BASE_AGENT_ID)
values (new.ID, new.PARENT_ID, new.DOC_ID, new.PART_ID, new.PART_PARENT_ID, new.DOC_DETAIL_ID, new.PRICE, new.NAC,
new.QUANT, new.DISCOUNT, new.SUMMA, new.SUMMA_O, new.DCARD, new.WARE_ID, new.PRICE_O, new.PRICE_Z, new.PRICE_R,
new.BARCODE, new.BARCODE1, new.BCODE_IZG, new.GODENDO, new.SERIA, new.NDS, new.SUM_NDSO, new.SERT,
new.DATESERT, new.KEMVSERT, new.SDSERT, new.REGN, new.NGTD, new.EDIZM, new.NAME_ID, new.IZG_ID, new.COUNTRY_ID,
new.ORIG_CODE, new.ORIG_NAME_ID, new.ORIG_IZG_ID, new.ORIG_COUNTRY_ID, new.Z_ID, new.SKLAD_ID, new.SNAME,
new.SIZG, new.SCOUNTRY, new.SORIG_NAME, new.SORIG_IZG, new.SORIG_COUNTRY, new.INSERTDT, new.INFO, new.KOEF,
new.MOTHERPART_ID, new.DEP, new.SUM_DSC, new.HUMAN_QUANT, new.CUSTOMDRAW, new.PART_TYPE, new.BASE_AGENT_ID);
/* -- */
if ((new.PART_ID is null) or (new.PART_ID = 0)) then
begin
/* >cf20110923 */
/* if (new.ware_id<0) then */
if (new.WARE_ID = '-1') then
begin
select GOODNAME,
GOODIZG,
GOODCOUNTRY,
GOODBARCODE
from PR_MAKEGOODWAREVALUES(new.SNAME, new.SORIG_NAME, new.SIZG, new.SORIG_IZG, new.SCOUNTRY, new.SORIG_COUNTRY, new.ORIG_CODE, new.ID, new.PART_TYPE, new.BCODE_IZG)
into new.SNAME,
new.SIZG,
new.SCOUNTRY,
new.BCODE_IZG;
end
/* <cf20110923 */
new.PART_ID = 0;
select ID
from VALS
where VTYPE = 0
and ALTTYPE = new.PART_TYPE
and SVALUE = new.SNAME
into new.NAME_ID;
select ID
from VALS
where VTYPE = 3
and ALTTYPE = new.PART_TYPE
and SVALUE = new.SIZG
into new.IZG_ID;
select ID
from VALS
where VTYPE = 2
and ALTTYPE = new.PART_TYPE
and SVALUE = new.SCOUNTRY
into new.COUNTRY_ID;
select ID
from VALS
where VTYPE = 1
and ALTTYPE = new.PART_TYPE
and SVALUE = new.SORIG_NAME
into new.ORIG_NAME_ID;
select ID
from VALS
where VTYPE = 6
and ALTTYPE = new.PART_TYPE
and SVALUE = new.SORIG_IZG
into new.ORIG_IZG_ID;
select ID
from VALS
where VTYPE = 5
and ALTTYPE = new.PART_TYPE
and SVALUE = new.SORIG_COUNTRY
into new.ORIG_COUNTRY_ID;
new.WARE_ID = 0;
select first 1 ID
from WARES
where NAME_ID = new.NAME_ID
and IZG_ID = new.IZG_ID
and COUNTRY_ID = new.COUNTRY_ID
and BARCODE = new.BCODE_IZG
into new.WARE_ID;
/* вторая строчка = после приведения */
insert into DOC_DETAIL_ACTIVE_LOG (ID, 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, BCODE_IZG, 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, SKLAD_ID, SNAME, SIZG, SCOUNTRY, SORIG_NAME,
SORIG_IZG, SORIG_COUNTRY, INSERTDT, INFO, KOEF, MOTHERPART_ID, DEP, SUM_DSC,
HUMAN_QUANT, CUSTOMDRAW, PART_TYPE, BASE_AGENT_ID)
values (new.ID, new.PARENT_ID, new.DOC_ID, new.PART_ID, new.PART_PARENT_ID, new.DOC_DETAIL_ID, new.PRICE, new.NAC,
new.QUANT, new.DISCOUNT, new.SUMMA, new.SUMMA_O, new.DCARD, new.WARE_ID, new.PRICE_O, new.PRICE_Z,
new.PRICE_R, new.BARCODE, new.BARCODE1, new.BCODE_IZG, new.GODENDO, new.SERIA, new.NDS, new.SUM_NDSO,
new.SERT, new.DATESERT, new.KEMVSERT, new.SDSERT, new.REGN, new.NGTD, new.EDIZM, new.NAME_ID, new.IZG_ID,
new.COUNTRY_ID, new.ORIG_CODE, new.ORIG_NAME_ID, new.ORIG_IZG_ID, new.ORIG_COUNTRY_ID, new.Z_ID, new.SKLAD_ID,
new.SNAME, new.SIZG, new.SCOUNTRY, new.SORIG_NAME, new.SORIG_IZG, new.SORIG_COUNTRY, new.INSERTDT, new.INFO,
new.KOEF, new.MOTHERPART_ID, new.DEP, new.SUM_DSC, new.HUMAN_QUANT, new.CUSTOMDRAW, new.PART_TYPE,
new.BASE_AGENT_ID);
/* -- */
end
if (new.PART_PARENT_ID is null) then
new.PART_PARENT_ID = 0;
if (new.WARE_ID is null) then
new.WARE_ID = 0;
if (new.NAME_ID is null) then
new.NAME_ID = 0;
if (new.IZG_ID is null) then
new.IZG_ID = 0;
if (new.COUNTRY_ID is null) then
new.COUNTRY_ID = 0;
if (new.ORIG_NAME_ID is null) then
new.ORIG_NAME_ID = 0;
if (new.ORIG_IZG_ID is null) then
new.ORIG_IZG_ID = 0;
if (new.ORIG_COUNTRY_ID is null) then
new.ORIG_COUNTRY_ID = 0;
if (new.Z_ID is null) then
new.Z_ID = 0;
if (new.SKLAD_ID is null) then
new.SKLAD_ID = '';
if (new.MOTHERPART_ID is null) then
new.MOTHERPART_ID = 0;
if (new.DOC_DETAIL_ID is null) then
new.DOC_DETAIL_ID = 0;
if (new.PART_ID > 0) then
/* заполняем значения партии */
begin
select WARE_ID,
PRICE,
PRICE_O,
PRICE_Z,
PRICE_R,
/* QUANT, */
/* BARCODE, */
BARCODE1,
/* KRITK, */
GODENDO,
SERIA,
NDS,
/* SUM_NDSO, */
SERT,
DATESERT,
KEMVSERT,
SDSERT,
REGN,
NGTD,
EDIZM,
NAC,
DEP,
PART_TYPE,
BASE_AGENT_ID,
GROUP_ID
from PARTS
where ID = new.PART_ID
into new.WARE_ID,
new.PRICE,
new.PRICE_O,
new.PRICE_Z,
new.PRICE_R,
/* new.QUANT, */
/* new.BARCODE, */
new.BARCODE1,
/* new.KRITK,*/
new.GODENDO,
new.SERIA,
new.NDS,
/* new.SUM_NDSO, */
new.SERT,
new.DATESERT,
new.KEMVSERT,
new.SDSERT,
new.REGN,
new.NGTD,
new.EDIZM,
new.NAC,
new.DEP,
new.PART_TYPE,
new.BASE_AGENT_ID,
new.GROUP_ID;
end
if (new.GROUP_ID is null) then
new.GROUP_ID = 0;
/* if (new.ware_id>0) then -- заполняем значения позиции */
if (new.WARE_ID not in ('-1', '0')) then
/* заполняем значения позиции */
begin
select WW.NAME_ID,
(select SVALUE
from VALS
where ID = WW.NAME_ID),
WW.IZG_ID,
(select SVALUE
from VALS
where ID = WW.IZG_ID),
WW.COUNTRY_ID,
(select SVALUE
from VALS
where ID = WW.COUNTRY_ID),
WW.ORIG_CODE,
WW.ORIG_NAME_ID,
(select SVALUE
from VALS
where ID = WW.ORIG_NAME_ID),
WW.ORIG_IZG_ID,
(select SVALUE
from VALS
where ID = WW.ORIG_IZG_ID),
WW.ORIG_COUNTRY_ID,
(select SVALUE
from VALS
where ID = WW.ORIG_COUNTRY_ID),
WW.BARCODE,
WW.Z_ID,
WW.SKLAD_ID
from WARES WW
where WW.ID = new.WARE_ID
into new.NAME_ID,
new.SNAME,
new.IZG_ID,
new.SIZG,
new.COUNTRY_ID,
new.SCOUNTRY,
new.ORIG_CODE,
new.ORIG_NAME_ID,
new.SORIG_NAME,
new.ORIG_IZG_ID,
new.SORIG_IZG,
new.ORIG_COUNTRY_ID,
new.SORIG_COUNTRY,
new.BCODE_IZG,
new.Z_ID,
new.SKLAD_ID;
end
else
begin
/* if (new.ware_id<0) then */
if (new.WARE_ID = '-1') then
begin
select GOODNAME,
GOODIZG,
GOODCOUNTRY
from PR_MAKEGOODWAREVALUES(new.SNAME, new.SORIG_NAME, new.SIZG, new.SORIG_IZG, new.SCOUNTRY, new.SORIG_COUNTRY, new.ORIG_CODE, new.ID, new.PART_TYPE, new.BCODE_IZG)
into new.SNAME,
new.SIZG,
new.SCOUNTRY;
new.WARE_ID = 0;
end
end
if (new.QUANT is null) then
new.QUANT = 0;
if (new.PRICE is null) then
new.PRICE = 0;
if (new.PRICE_O is null) then
new.PRICE_O = 0;
if (new.NDS is null) then
new.NDS = 0;
if (new.SUMMA is null) then
new.SUMMA = new.QUANT * new.PRICE;
if (new.SUM_DSC is null) then
new.SUM_DSC = 0;
if (new.SUMMA_O is null) then
begin
new.SUMMA_O = new.QUANT * new.PRICE_O;
end
else
begin
if (new.QUANT <> 0) then
new.PRICE_O = new.SUMMA_O / new.QUANT;
end
if (new.SUM_NDSO is null) then
new.SUM_NDSO = new.SUMMA_O * new.NDS / 100;
if (((new.NAC is null) or (new.NAC = 0)) and
new.PRICE_O <> 0) then
new.NAC = (new.PRICE - new.PRICE_O) * 100 / new.PRICE_O;
if (new.DEP is null) then
begin
select first 1 ID
from DEPS
where NDS = new.NDS
order by ID
into new.DEP;
end
if (new.DEP is null) then
new.DEP = 0;
/* -> 20130301-ann gen_barcode_by_name */
if ((new.BARCODE is null) or (trim(new.BARCODE) = '')) then
begin
if ((select PARAM_VALUE
from PR_GETPARAMVALUE('GEN_BARCODE', 0)) = 0) then
select RES_BCODE
from UTPR_GETCHECKSUM_EAN13(new.ID)
into new.BARCODE;
/* else select res_bcode from utpr_getchecksum_ean13(new.name_id,'88') into new.barcode; */
else
select RES_BCODE
from UTPR_GETCHECKSUM_EAN13((select L_ID
from VALS
where D$UUID = new.NAME_ID), '88')
into new.BARCODE;
end
/* -> 20130301-ann gen_barcode_by_name */
if (new.PART_PARENT_ID > 0) then
begin
update DOC_DETAIL_ACTIVE
set QUANT = QUANT - new.QUANT,
SUMMA = SUMMA - new.SUMMA,
SUMMA_O = SUMMA_O - new.SUMMA_O,
SUM_NDSO = SUM_NDSO - new.SUM_NDSO
where ID = new.PART_PARENT_ID;
execute procedure PR_COPY_MEMBERSHIP('DOC_DETAIL_ACTIVE', new.PART_PARENT_ID, new.ID);
end
end;
create or alter trigger DOC_DETAIL_ACTIVE_BU0 for DOC_DETAIL_ACTIVE
active before update position 0
as
begin
/*20160513
if ((new.SNAME <> old.SNAME) or (new.NAME_ID is null) or (new.NAME_ID = '0')) then
select VAL_ID from PR_GETVAL_ID(new.SNAME, 0, new.PART_TYPE, new.MNN) into new.NAME_ID;
*/
if (new.NAME_ID is null) then
new.NAME_ID = 0;
if ((new.SUMMA is null) and
((new.QUANT <> 0) and
(new.PRICE <> 0))) then
begin
new.SUMMA = new.QUANT * new.PRICE;
if (new.SUM_DSC is not null) then
new.SUMMA = new.SUMMA - new.SUM_DSC;
end
if (((new.SUMMA_O = 0) or (new.SUMMA_O is null)) and
((new.QUANT <> 0) and
(new.PRICE_O <> 0))) then
new.SUMMA_O = new.QUANT * new.PRICE_O;
if (new.NAME_ID <> old.NAME_ID) then
execute procedure PR_COPYGROUPS(old.NAME_ID, new.NAME_ID);
if (new.SUM_DSC is null) then
if (new.SUMMA <> old.SUMMA) then
begin
new.SUM_DSC = (1 + new.DISCOUNT / 100) * 100 * new.SUMMA / (100 + old.DISCOUNT) - 100 * new.SUMMA / (100 + old.DISCOUNT);
--new.summa=(1+new.discount/100)*100*old.summa/(100+old.discount);
end
if (new.SUM_DSC is null) then
if (new.DISCOUNT <> old.DISCOUNT) then
begin
new.SUM_DSC = (1 + new.DISCOUNT / 100) * 100 * old.SUMMA / (100 + old.DISCOUNT) - 100 * old.SUMMA / (100 + old.DISCOUNT);
new.SUMMA = (1 + new.DISCOUNT / 100) * 100 * old.SUMMA / (100 + old.DISCOUNT);
end
select ID
from VALS
where VTYPE = 0
and ALTTYPE = new.PART_TYPE
and SVALUE = new.SNAME
into new.NAME_ID;
if (new.NAME_ID is null) then
new.NAME_ID = 0;
select ID
from VALS
where VTYPE = 3
and ALTTYPE = new.PART_TYPE
and SVALUE = new.SIZG
into new.IZG_ID;
if (new.IZG_ID is null) then
new.IZG_ID = 0;
select ID
from VALS
where VTYPE = 2
and ALTTYPE = new.PART_TYPE
and SVALUE = new.SCOUNTRY
into new.COUNTRY_ID;
if (new.COUNTRY_ID is null) then
new.COUNTRY_ID = 0;
select ID
from VALS
where VTYPE = 1
and ALTTYPE = new.PART_TYPE
and SVALUE = new.SORIG_NAME
into new.ORIG_NAME_ID;
if (new.ORIG_NAME_ID is null) then
new.ORIG_NAME_ID = 0;
select ID
from VALS
where VTYPE = 6
and ALTTYPE = new.PART_TYPE
and SVALUE = new.SORIG_IZG
into new.ORIG_IZG_ID;
if (new.ORIG_IZG_ID is null) then
new.ORIG_IZG_ID = 0;
select ID
from VALS
where VTYPE = 5
and ALTTYPE = new.PART_TYPE
and SVALUE = new.SORIG_COUNTRY
into new.ORIG_COUNTRY_ID;
if (new.ORIG_COUNTRY_ID is null) then
new.ORIG_COUNTRY_ID = 0;
insert into DOC_DETAIL_ACTIVE_LOG (ID, 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, BCODE_IZG,
--KRITK,
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, SKLAD_ID, SNAME, SIZG, SCOUNTRY, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY,
INSERTDT, INFO, KOEF, MOTHERPART_ID, DEP, SUM_DSC, HUMAN_QUANT, CUSTOMDRAW,
PART_TYPE, BASE_AGENT_ID)
values (old.ID, old.PARENT_ID, old.DOC_ID, old.PART_ID, old.PART_PARENT_ID, old.DOC_DETAIL_ID, old.PRICE, old.NAC,
old.QUANT, old.DISCOUNT, old.SUMMA, old.SUMMA_O, old.DCARD, old.WARE_ID, old.PRICE_O, old.PRICE_Z, old.PRICE_R,
old.BARCODE, old.BARCODE1, old.BCODE_IZG,
--old.KRITK,
old.GODENDO, old.SERIA, old.NDS, old.SUM_NDSO, old.SERT, old.DATESERT, old.KEMVSERT, old.SDSERT, old.REGN,
old.NGTD, old.EDIZM, old.NAME_ID, old.IZG_ID, old.COUNTRY_ID, old.ORIG_CODE, old.ORIG_NAME_ID, old.ORIG_IZG_ID,
old.ORIG_COUNTRY_ID, old.Z_ID, old.SKLAD_ID, old.SNAME, old.SIZG, old.SCOUNTRY, old.SORIG_NAME, old.SORIG_IZG,
old.SORIG_COUNTRY, old.INSERTDT, old.INFO, old.KOEF, old.MOTHERPART_ID, old.DEP, old.SUM_DSC, old.HUMAN_QUANT,
old.CUSTOMDRAW, old.PART_TYPE, old.BASE_AGENT_ID);
end;
create or alter view VW_WARES (
ID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
ORIG_CODE,
ORIG_NAME_ID,
SORIG_NAME,
ORIG_IZG_ID,
SORIG_IZG,
ORIG_COUNTRY_ID,
SORIG_COUNTRY,
BARCODE,
Z_ID,
SKLAD_ID,
INSERTDT,
PACKET,
MGN_NAME,
MGN_ID,
MGN_SOURCE)
as
select W.ID,
W.NAME_ID,
W.SNAME, --(select svalue from vals where id = w.name_id),
W.IZG_ID,
(select SVALUE
from VALS
where ID = W.IZG_ID),
W.COUNTRY_ID,
(select SVALUE
from VALS
where ID = W.COUNTRY_ID),
W.ORIG_CODE,
W.ORIG_NAME_ID,
(select SVALUE
from VALS
where ID = W.ORIG_NAME_ID),
W.ORIG_IZG_ID,
(select SVALUE
from VALS
where ID = W.ORIG_IZG_ID),
W.ORIG_COUNTRY_ID,
(select SVALUE
from VALS
where ID = W.ORIG_COUNTRY_ID),
W.BARCODE,
W.Z_ID,
W.SKLAD_ID,
W.INSERTDT,
W.PACKET,
W.MGN_NAME,
W.MGN_ID,
W.MGN_SOURCE
from WARES W;
create or alter procedure PR_DOC_DETAIL_ACTIVE_ONCOMMIT (
ACTIVE_ID type of DM_ID,
COMMIT_ID type of DM_ID,
DOC_ID type of DM_ID,
COMMITDATE type of DM_DATETIME)
as
begin
/*
эта процедура выполняется для каждой записи, добавляемой в DOC_DETAIL в процедурах проведения документов
*/
/* Для вречей*/
update DOC_DETAIL_DOCTOR
set DOC_DETAIL_ID = :COMMIT_ID,
DOC_ID = :DOC_ID,
COMMITDATE = :COMMITDATE
where DOC_DETAIL_ACTIVE_ID = :ACTIVE_ID;
/* Старые рецепты*/
update DOC_DETAIL_MAKE
set DOC_DETAIL_ID = :COMMIT_ID,
DOC_ID = :DOC_ID,
COMMITDATE = :COMMITDATE
where DOC_DETAIL_ACTIVE_ID = :ACTIVE_ID;
/* Новые рецепты*/
update RECEPTS
set TABLE_ID = :COMMIT_ID,
TABLE_NAME = 'DOC_DETAIL',
STATUS = 1
where DOC_DETAIL_ACTIVE_ID = :ACTIVE_ID;--для рецептов
execute procedure PR_AUTO_WARESLINK(:DOC_ID, :ACTIVE_ID);
end;
Славины скрипты
set term ^;
create trigger WARES_LOG_BI_COWATMP for WARES_LOG
active before insert position 0
as
begin
if (new.SKLAD_ID is null) then
new.SKLAD_ID = '';
end^
set term; ^
create table TMP_LOG (SKEY DM_TEXT, SVALUE DM_TEXT);
alter table WARES_LOG add ACTUAL_WARE_ID DM_UUID_NULL;
create index WARES_LOG_ACTUAL_WARE_ID on WARES_LOG (ACTUAL_WARE_ID);
create generator GEN_VALS_CARANTINE_ID;
create table VALS_CARANTINE (ID DM_ID not null, CUR_ID DM_UUID, NEW_ID DM_UUID, INSERTDT DM_DATETIME, FROM_PROFILE_ID DM_ID_NULL);
set term ^;
create or alter trigger VALS_CARANTINE_BI for VALS_CARANTINE
active before insert position 0
as
begin
if (new.ID is null) then
new.ID = gen_id(GEN_VALS_CARANTINE_ID, 1);
new.INSERTDT = current_timestamp;
if (new.FROM_PROFILE_ID is null) then
select FROM_PROFILE_ID
from G$DISTRIBUTE
where UUID = new.NEW_ID
into new.FROM_PROFILE_ID;
end^
set term ; ^
set term ^ ;
create or alter trigger VALS_BIU_DODOUBLES for VALS
active before insert or update position 0
as
declare variable ID type of DM_UUID;
begin
select ID
from VALS
where VTYPE = new.VTYPE
and SVALUE = new.SVALUE
and ALTTYPE = new.ALTTYPE
and D$UUID <> new.D$UUID
into :ID;
if (ID is null) then
exit;
/*
0 - приведенное наименование
3 - приведенный производитель
2 - приведенная страна
1 - ориг. наименование
6 - ориг. производитель
5 - ориг. страна
Запускает цепочку апдейтов. При изменении wares проверяются дубли, и, в случае нахождения, правятся партии
*/
if (new.VTYPE = 0) then
update WARES
set NAME_ID = new.ID
where NAME_ID = :ID;
else
if (new.VTYPE = 3) then
update WARES
set IZG_ID = new.ID
where IZG_ID = :ID;
else
if (new.VTYPE = 2) then
update WARES
set COUNTRY_ID = new.ID
where COUNTRY_ID = :ID;
else
if (new.VTYPE = 1) then
update WARES
set ORIG_NAME_ID = new.ID
where ORIG_NAME_ID = :ID;
else
if (new.VTYPE = 6) then
update WARES
set ORIG_IZG_ID = new.ID
where ORIG_IZG_ID = :ID;
else
if (new.VTYPE = 5) then
update WARES
set ORIG_COUNTRY_ID = new.ID
where ORIG_COUNTRY_ID = :ID;
else
exit;
insert into VALS_CARANTINE (CUR_ID, NEW_ID)
values (:ID, new.ID);
delete from VALS
where ID = :ID;
end^
set term;
^
set term ^;
create or alter trigger WARES_BIU_COWA for WARES
active before insert or update position 0
as
declare variable CUR_ID type of DM_UUID;
begin
select D$UUID
from WARES
where NAME_ID = new.NAME_ID
and IZG_ID = new.IZG_ID
and COUNTRY_ID = new.COUNTRY_ID
and BARCODE = new.BARCODE
and D$UUID <> new.D$UUID
into :CUR_ID;
if (CUR_ID is null) then
exit;
if (not exists(select ID
from WARES_LOG
where ID = :CUR_ID
and ACTUAL_WARE_ID = new.ID)) then
begin
insert into WARES_LOG (ID, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID,
BARCODE, Z_ID, SKLAD_ID, INSERTDT, PACKET, L_ID, ACTUAL_WARE_ID)
select ID,
NAME_ID,
IZG_ID,
COUNTRY_ID,
ORIG_CODE,
ORIG_NAME_ID,
ORIG_IZG_ID,
ORIG_COUNTRY_ID,
BARCODE,
Z_ID,
SKLAD_ID,
INSERTDT,
PACKET,
L_ID,
new.D$UUID
from WARES
where D$UUID = :CUR_ID;
end
update PARTS
set WARE_ID = new.D$UUID
where WARE_ID = :CUR_ID;
update DOC_DETAIL_ACTIVE
set WARE_ID = new.D$UUID
where WARE_ID = :CUR_ID;
delete from WARES
where D$UUID = :CUR_ID;
end^
set term;
^
set term ^;
create or alter trigger PARTS_BIU_COWA for PARTS
active before insert or update position 0
as
declare variable WARE_ID type of DM_UUID;
begin
if (exists(select ID
from WARES
where ID = new.WARE_ID)) then
exit;
/* select max(actual_ware_id) from wares_log where id=new.ware_id into :ware_id; */
select max(ACTUAL_WARE_ID)
from WARES_LOG
where ID = new.WARE_ID
and (exists(select ID
from WARES W
where W.D$UUID = ACTUAL_WARE_ID))
into :WARE_ID;
if (WARE_ID is null) then
exit;
/* if (exists(select id from wares where d$uuid=:ware_id)) then */
new.WARE_ID = :WARE_ID;
end^
set term;
^
set term ^;
create or alter trigger WARES_BD_COWA for WARES
active before delete position 0
as
declare variable WARE_ID type of DM_UUID;
begin
if (not exists(select ID
from PARTS
where WARE_ID = old.ID)) then
exit;
/* select max(actual_ware_id) from wares_log where id=old.id into ware_id; */
select max(ACTUAL_WARE_ID)
from WARES_LOG WL
where ID = old.ID
and (exists(select ID
from WARES W
where W.ID = WL.ACTUAL_WARE_ID))
into WARE_ID;
if (WARE_ID is null) then
insert into TMP_LOG (SKEY, SVALUE)
values ('Нет ware по acual_ware!', old.ID);
else
update PARTS
set WARE_ID = :WARE_ID
where WARE_ID = old.ID;
end^
set term;
^
set term ^;
create or alter procedure PR_TMP_CHANGEWARES_CONSTR
as
declare variable UUID type of DM_UUID;
declare variable NAME_ID type of DM_UUID;
declare variable IZG_ID type of DM_UUID;
declare variable COUNTRY_ID type of DM_UUID;
declare variable BARCODE type of DM_TEXT;
declare variable ACTUAL_UUID type of DM_UUID;
declare variable EXISTING_ACTUAL_WARE_ID type of DM_UUID;
begin
/* delete from wares_log wl where (ACTUAL_WARE_ID is null) and (not exists (select id from wares w where w.id=wl.id)); */
for select min(D$UUID),
NAME_ID,
IZG_ID,
COUNTRY_ID,
BARCODE
from WARES
group by NAME_ID, IZG_ID, COUNTRY_ID, BARCODE
having count(1) > 1
into ACTUAL_UUID,
NAME_ID,
IZG_ID,
COUNTRY_ID,
BARCODE
do
begin
EXISTING_ACTUAL_WARE_ID = null;
select max(ACTUAL_WARE_ID)
from WARES_LOG
where NAME_ID = :NAME_ID
and IZG_ID = :IZG_ID
and COUNTRY_ID = :COUNTRY_ID
and BARCODE = :BARCODE
into EXISTING_ACTUAL_WARE_ID;
if (EXISTING_ACTUAL_WARE_ID is not null) then
begin
if (exists(select ID
from WARES
where D$UUID = :EXISTING_ACTUAL_WARE_ID)) then
ACTUAL_UUID = EXISTING_ACTUAL_WARE_ID;
end
for select D$UUID
from WARES
where NAME_ID = :NAME_ID
and IZG_ID = :IZG_ID
and COUNTRY_ID = :COUNTRY_ID
and BARCODE = :BARCODE
and D$UUID <> :ACTUAL_UUID
into UUID
do
begin
insert into WARES_LOG (ID, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID,
BARCODE, Z_ID, SKLAD_ID, INSERTDT, PACKET, L_ID, ACTUAL_WARE_ID)
select ID,
NAME_ID,
IZG_ID,
COUNTRY_ID,
ORIG_CODE,
ORIG_NAME_ID,
ORIG_IZG_ID,
ORIG_COUNTRY_ID,
BARCODE,
Z_ID,
SKLAD_ID,
INSERTDT,
PACKET,
L_ID,
:ACTUAL_UUID
from WARES
where D$UUID = :UUID;
--на триггере update parts set ware_id=:actual_uuid where ware_id=:uuid;
delete from WARES
where D$UUID = :UUID;
end
end
end^
set term;
^
set term ^;
create or alter trigger WARES_LOG_AI_COWATMP for WARES_LOG
active after insert position 0
as
begin
if (new.ACTUAL_WARE_ID is null) then
exit;
if (exists(select ID
from WARES
where ID = new.ACTUAL_WARE_ID)) then
delete from WARES
where ID = new.ID;
else
insert into TMP_LOG (SKEY, SVALUE)
values ('WARES_LOG_AI_COWATMP', new.ACTUAL_WARE_ID);
end^
set term;
^
alter trigger WARES_AD_DISTR inactive;
alter trigger WARES_BU_DISTR inactive;
alter trigger WARES_LOG_BI_DISTR inactive;
alter trigger WARES_LOG_AI0 inactive;
alter trigger PARTS_AU0 inactive;
alter trigger PARTS_BU_DISTR inactive;
alter trigger PARTS_BU_G$SYNC inactive;
alter trigger PARTS_BU0 inactive;
