Проверка на ЖНВЛС (ошибка в процедуре)
Материал из wiki.standart-n.ru
Версия от 16:01, 5 августа 2016; Agk (обсуждение | вклад)
Ошибка
При запуске аналитического отчета по проверке ЖНВЛС выходит ошибка на PR_GNVLSCHECK, а при попытке её скомпилировать сообщение too many Contexts of Relation/Views. Maximum allowed is 255 Error while parsing procedure PR_GNVLSCHECK's BLR
Выполните скрипт
Скрипт
SET TERM ^ ;
create or alter procedure PR_GNVLSCHECK_SOURCE0 (
ANALYTIC_DOC_ID DM_ID,
PERIODBEG DM_DATE,
PERIODEND DM_DATE,
NAC1 DM_DOUBLE,
NAC2 DM_DOUBLE,
NAC3 DM_DOUBLE,
NAC4 DM_DOUBLE)
as
begin
insert into analytic_GNVLS(
ANALYTIC_DOC_ID,
VID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
PRICE,
MAXPRICE,
SERIA,
PART_ID,
Price_Z,
Price_R,
NAC)
select
:analytic_doc_id,
'Превышение максимальной розничной цены',
w.name_id,
w.sname,
w.izg_id,
w.sizg,
w.country_id,
w.scountry,
w.price,
gb.price,
w.seria,
w.part_id,
w.price_z,
w.price_r,
w.nac
from warebase w left join out$gnvls_barcode gb on ((w.bcode_izg = gb.barcode)or(w.barcode = gb.barcode))
and abs(gb.price_r-w.price_r)<=0.01
where (w.quant>0 and trim(gb.barcode)<>'' and gb.barcode is not null and (w.price>=gb.price)) group by 'Превышение максимальной розничной цены',w.name_id, w.sname,w.izg_id,w.sizg, w.country_id,w.scountry,w.price, gb.price,w.seria,w.part_id,w.price_z,w.price_r,w.nac;
insert into analytic_GNVLS(
ANALYTIC_DOC_ID,
VID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
PRICE,
--MAXPRICE,
SERIA,
PART_ID,
Price_Z,
Price_R,
NAC)
select
:analytic_doc_id,
'Не найден в реестре ЖНВЛС или не верная цена реестра',
w.name_id,
w.sname,
w.izg_id,
w.sizg,
w.country_id,
w.scountry,
w.price,
--0,
w.seria,
w.part_id,
w.price_z,
w.price_r,
w.nac
from warebase w left join out$gnvls_barcode gb on ((w.bcode_izg = gb.barcode)or(w.barcode = gb.barcode))
and abs(gb.price_r-w.price_r)<=0.01
where
exists(select id from group_detail where
grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id) and
w.quant>0 and gb.barcode is null group by 'Не найден в реестре ЖНВЛС или не верная цена реестра'
,w.name_id, w.sname,w.izg_id,w.sizg, w.country_id,w.scountry,w.price, gb.price,w.seria,w.part_id,w.price_z,w.price_r,w.nac;
insert into analytic_GNVLS (
ANALYTIC_DOC_ID,
VID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
PRICE,
--MAXPRICE,
SERIA,
PART_ID,
Price_Z,
Price_R,
NAC)
select
:analytic_doc_id,
'Превышение наценки',
w.name_id,
w.sname,
w.izg_id,
w.sizg,
w.country_id,
w.scountry,
w.price,
--iif(w.price_z>w.price_r*1.1 and w.price_r>0,w.price_r*1.1,w.price_z)*iif(w.price_z*100/110>500,:nac3,iif(w.price_z*100/110>50,:nac2,:nac1))/100+w.price_o,
w.seria ,
w.part_id,
w.price_z,
w.price_r,
w.nac
from vw_warebase w
where
(exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id))
and w.price_r<>0 and w.price_z<>0 and w.price_z>0 and w.quant>0 and
( ( not (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=47 and grouptable_id=w.name_id)) and
( 100*(w.price-w.Price_O)/iif(w.price_z*100/110>w.price_r and w.price_r>0,w.price_r*1.1,w.price_z) >iif(w.price_z*100/110>500,:nac3,iif(w.price_z*100/110>50,:nac2,:nac1))
or
w.nac >iif(w.price_z*100/110>500,:nac3,iif(w.price_z*100/110>50,:nac2,:nac1)) --23.04.2015 А.К. по просьбе Наташи
)
)
or
( (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and
(exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=47 and grouptable_id=w.name_id)) and w.price_r<>0 and w.price_z<>0 and
100*(w.price-w.Price_O)/iif(w.price_z*100/110>w.price_r and w.price_r>0,w.price_r*1.1,w.price_z) > :nac4
)
);
insert into analytic_GNVLS (
ANALYTIC_DOC_ID,
VID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
PRICE,
--MAXPRICE,
SERIA,
PART_ID,
Price_Z,
Price_R,
NAC)
select
:analytic_doc_id,
'Не указана цена завода',
w.name_id,
w.sname,
w.izg_id,
w.sizg,
w.country_id,
w.scountry,
w.price,
--0,
w.seria ,
w.part_id,
w.price_z,
w.price_r,
w.nac
from warebase w
-- where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=3 and grouptable_id=w.name_id)) and (w.price_z=0 or (w.price_z is null) ) and
where (exists(select id from group_detail where
grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and ((w.price_z between -0.000001 and 0.000001) or (w.price_z is null) ) and
w.quant>0 ;
insert into analytic_GNVLS (
ANALYTIC_DOC_ID,
VID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
PRICE,
--MAXPRICE,
SERIA,
PART_ID,
Price_Z,
Price_R,
NAC)
select
:analytic_doc_id,
'Не указана цена рестра',
w.name_id,
w.sname,
w.izg_id,
w.sizg,
w.country_id,
w.scountry,
w.price,
--0,
w.seria ,
w.part_id,
w.price_z,
w.price_r,
w.nac
from warebase w
-- where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=3 and grouptable_id=w.name_id)) and (w.price_r=0 or (w.price_r is null) ) and
where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and ((w.price_r between -0.00001 and 0.000001) or (w.price_r is null) ) and
w.quant>0 ;
insert into analytic_GNVLS (
ANALYTIC_DOC_ID,
VID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
PRICE,
--MAXPRICE,
SERIA,
PART_ID,
Price_Z,
Price_R,
NAC)
select
:analytic_doc_id,
'Укажите группу ЖНВЛС',
w.name_id,
w.sname,
w.izg_id,
w.sizg,
w.country_id,
w.scountry,
w.price,
--0,
w.seria ,
w.part_id,
w.price_z,
w.price_r,
w.nac
from warebase w left join out$gnvls_barcode gb on (w.bcode_izg = gb.barcode)or(w.barcode = gb.barcode)
where (not exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and (trim(gb.barcode)<>'' and gb.barcode is not null) and
w.quant>0 group by 'Укажите группу ЖНВЛС',w.name_id, w.sname,w.izg_id,w.sizg, w.country_id,w.scountry,w.price,w.seria,w.part_id,w.price_z,w.price_r,w.nac;
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT INSERT ON ANALYTIC_GNVLS TO PROCEDURE PR_GNVLSCHECK_SOURCE0;
GRANT SELECT ON WAREBASE TO PROCEDURE PR_GNVLSCHECK_SOURCE0;
GRANT SELECT ON OUT$GNVLS_BARCODE TO PROCEDURE PR_GNVLSCHECK_SOURCE0;
GRANT SELECT ON GROUP_DETAIL TO PROCEDURE PR_GNVLSCHECK_SOURCE0;
GRANT SELECT ON VW_WAREBASE TO PROCEDURE PR_GNVLSCHECK_SOURCE0;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE PR_GNVLSCHECK_SOURCE0 TO SYSDBA;
SET TERM ^ ;
create or alter procedure PR_GNVLSCHECK_SOURCE1 (
ANALYTIC_DOC_ID DM_ID,
PERIODBEG DM_DATE,
PERIODEND DM_DATE,
NAC1 DM_DOUBLE,
NAC2 DM_DOUBLE,
NAC3 DM_DOUBLE,
NAC4 DM_DOUBLE)
as
begin
insert into analytic_GNVLS(
ANALYTIC_DOC_ID,
VID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
PRICE,
--MAXPRICE,
SERIA,
PART_ID,
Price_Z,
Price_R,
NAC)
select
:analytic_doc_id,
'Превышение максимальной розничной цены',
w.name_id,
w.sname,
w.izg_id,
w.sizg,
w.country_id,
w.scountry,
--w.price,
gb.price,
w.seria
,w.part_id,
w.price_z,
w.price_r,
w.nac
from vw_doc_detail w left join docs d on d.id=w.doc_id left join doc_types dt on dt.id=d.doc_type left join out$gnvls_barcode gb on ((w.bcode_izg = gb.barcode)or(w.barcode = gb.barcode))
and abs(gb.price_r-w.price_r)<=0.01
where w.doc_commitdate between :periodbeg and :periodend and dt.base_type=1 and
(trim(gb.barcode)<>'' and gb.barcode is not null and (w.price>=gb.price)) group by 'Превышение максимальной розничной цены',w.name_id, w.sname,w.izg_id,w.sizg, w.country_id,w.scountry,w.price, gb.price,w.seria,w.part_id,w.price_z,w.price_r,w.nac;
insert into analytic_GNVLS(
ANALYTIC_DOC_ID,
VID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
PRICE,
--MAXPRICE,
SERIA,
PART_ID,
Price_Z,
Price_R,
NAC)
select
:analytic_doc_id,
'Не найден в реестре ЖНВЛС или не верная цена реестра',
w.name_id,
w.sname,
w.izg_id,
w.sizg,
w.country_id,
w.scountry,
w.price,
--0,
w.seria,
w.part_id,
w.price_z,
w.price_r,
w.nac
from vw_doc_detail w left join docs d on d.id=w.doc_id left join doc_types dt on dt.id=d.doc_type left join out$gnvls_barcode gb on ((w.bcode_izg = gb.barcode)or(w.barcode = gb.barcode))
and abs(gb.price_r-w.price_r)<=0.01
where w.doc_commitdate between :periodbeg and :periodend and dt.base_type = 1 and
exists(select id from group_detail where
grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id) and
gb.barcode is null group by 'Не найден в реестре ЖНВЛС или не верная цена реестра'
,w.name_id, w.sname,w.izg_id,w.sizg, w.country_id,w.scountry,w.price, gb.price,w.seria,w.part_id,w.price_z,w.price_r,w.nac;
insert into analytic_GNVLS (
ANALYTIC_DOC_ID,
VID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
PRICE,
--MAXPRICE,
SERIA,
PART_ID,
Price_Z,
Price_R,
NAC)
select
:analytic_doc_id,
'Превышение наценки',
w.name_id,
w.sname,
w.izg_id,
w.sizg,
w.country_id,
w.scountry,
w.price,
--iif(w.price_z>w.price_r*1.1 and w.price_r>0,w.price_r*1.1,w.price_z)*iif(w.price_z*100/110>500,:nac3,iif(w.price_z*100/110>50,:nac2,:nac1))/100+w.price_o,
w.seria ,
w.part_id,
w.price_z,
w.price_r,
w.nac
from vw_doc_detail w left join docs d on d.id=w.doc_id left join doc_types dt on dt.id=d.doc_type
where
(exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id))
and w.price_r<>0 and w.price_z<>0 and w.price_z>0 and w.quant>0 and
( ( not (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=47 and grouptable_id=w.name_id)) and
100*(w.price-w.Price_O)/iif(w.price_z*100/110>w.price_r and
w.price_r>0,w.price_r*1.1,w.price_z) >iif(w.price_z*100/110>500,:nac3,iif(w.price_z*100/110>50,:nac2,:nac1))
)
or
( (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and
(exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=47 and grouptable_id=w.name_id)) and w.price_r<>0 and w.price_z<>0 and
100*(w.price-w.Price_O)/iif(w.price_z*100/110>w.price_r and w.price_r>0,w.price_r*1.1,w.price_z) > :nac4
) ) and dt.base_type=1 and w.doc_commitdate between :periodbeg and :periodend;
insert into analytic_GNVLS (
ANALYTIC_DOC_ID,
VID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
PRICE,
--MAXPRICE,
SERIA,
PART_ID,
Price_Z,
Price_R,
NAC)
select
:analytic_doc_id,
'Не указана цена завода',
w.name_id,
w.sname,
w.izg_id,
w.sizg,
w.country_id,
w.scountry,
w.price,
--0,
w.seria ,
w.part_id,
w.price_z,
w.price_r,
w.nac
from vw_doc_detail w left join docs d on d.id=w.doc_id left join doc_types dt on dt.id=d.doc_type
-- where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=3 and grouptable_id=w.name_id)) and (w.price_z=0 or (w.price_z is null) ) and
where (exists(select id from group_detail where
grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and ((w.price_z between -0.000001 and 0.000001) or (w.price_z is null) ) and
w.doc_commitdate between :periodbeg and :periodend and dt.base_type=1;
insert into analytic_GNVLS (
ANALYTIC_DOC_ID,
VID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
PRICE,
--MAXPRICE,
SERIA,
PART_ID,
Price_Z,
Price_R,
NAC)
select
:analytic_doc_id,
'Не указана цена рестра',
w.name_id,
w.sname,
w.izg_id,
w.sizg,
w.country_id,
w.scountry,
w.price,
--0,
w.seria ,
w.part_id,
w.price_z,
w.price_r,
w.nac
from vw_doc_detail w left join docs d on d.id=w.doc_id left join doc_types dt on dt.id=d.doc_type
-- where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=3 and grouptable_id=w.name_id)) and (w.price_r=0 or (w.price_r is null) ) and
where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and ((w.price_r between -0.00001 and 0.000001) or (w.price_r is null) ) and
w.doc_commitdate between :periodbeg and :periodend and dt.base_type=1;
insert into analytic_GNVLS (
ANALYTIC_DOC_ID,
VID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
PRICE,
--MAXPRICE,
SERIA,
PART_ID,
Price_Z,
Price_R,
NAC)
select
:analytic_doc_id,
'Укажите группу ЖНВЛС',
w.name_id,
w.sname,
w.izg_id,
w.sizg,
w.country_id,
w.scountry,
w.price,
--0,
w.seria ,
w.part_id,
w.price_z,
w.price_r,
w.nac
from vw_doc_detail w left join docs d on d.id=w.doc_id left join doc_types dt on dt.id=d.doc_type
left join out$gnvls_barcode gb on (w.bcode_izg = gb.barcode)or(w.barcode = gb.barcode)
where (not exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and (trim(gb.barcode)<>'' and gb.barcode is not null) and
w.doc_commitdate between :periodbeg and :periodend and dt.base_type =1
group by 'Укажите группу ЖНВЛС',w.name_id, w.sname,w.izg_id,w.sizg, w.country_id,w.scountry,w.price,w.seria,w.part_id,w.price_z,w.price_r,w.nac;
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT INSERT ON ANALYTIC_GNVLS TO PROCEDURE PR_GNVLSCHECK_SOURCE1;
GRANT SELECT ON VW_DOC_DETAIL TO PROCEDURE PR_GNVLSCHECK_SOURCE1;
GRANT SELECT ON DOCS TO PROCEDURE PR_GNVLSCHECK_SOURCE1;
GRANT SELECT ON DOC_TYPES TO PROCEDURE PR_GNVLSCHECK_SOURCE1;
GRANT SELECT ON OUT$GNVLS_BARCODE TO PROCEDURE PR_GNVLSCHECK_SOURCE1;
GRANT SELECT ON GROUP_DETAIL TO PROCEDURE PR_GNVLSCHECK_SOURCE1;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE PR_GNVLSCHECK_SOURCE1 TO SYSDBA;
SET TERM ^ ;
create or alter procedure PR_GNVLSCHECK_SOURCE2 (
ANALYTIC_DOC_ID DM_ID,
PERIODBEG DM_DATE,
PERIODEND DM_DATE,
NAC1 DM_DOUBLE,
NAC2 DM_DOUBLE,
NAC3 DM_DOUBLE,
NAC4 DM_DOUBLE)
as
begin
insert into analytic_GNVLS(
ANALYTIC_DOC_ID,
VID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
PRICE,
MAXPRICE,
SERIA,
PART_ID,
Price_Z,
Price_R,
NAC)
select
:analytic_doc_id,
'Превышение максимальной розничной цены',
w.name_id,
w.sname,
w.izg_id,
w.sizg,
w.country_id,
w.scountry,
w.price,
gb.price,
w.seria,
w.part_id,
w.price_z,
w.price_r,
w.nac
from vw_doc_detail w left join docs d on d.id=w.doc_id left join out$gnvls_barcode gb on ((w.bcode_izg = gb.barcode)or(w.barcode = gb.barcode))
and abs(gb.price_r-w.price_r)<=0.01
where w.doc_commitdate between :periodbeg and :periodend and d.doc_type in (6,11,39) and
(trim(gb.barcode)<>'' and gb.barcode is not null and (w.price>=gb.price)) group by 'Превышение максимальной розничной цены',w.name_id, w.sname,w.izg_id,w.sizg, w.country_id,w.scountry,w.price, gb.price,w.seria,w.part_id,w.price_z,w.price_r,w.nac;
insert into analytic_GNVLS(
ANALYTIC_DOC_ID,
VID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
PRICE,
--MAXPRICE,
SERIA,
PART_ID,
Price_Z,
Price_R,
NAC)
select
:analytic_doc_id,
'Не найден в реестре ЖНВЛС или не верная цена реестра',
w.name_id,
w.sname,
w.izg_id,
w.sizg,
w.country_id,
w.scountry,
w.price,
--0,
w.seria,
w.part_id,
w.price_z,
w.price_r,
w.nac
from vw_doc_detail w left join docs d on d.id=w.doc_id left join out$gnvls_barcode gb on ((w.bcode_izg = gb.barcode)or(w.barcode = gb.barcode))
and abs(gb.price_r-w.price_r)<=0.01
where w.doc_commitdate between :periodbeg and :periodend and d.doc_type in (6,11,39) and
exists(select id from group_detail where
grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id) and
gb.barcode is null group by 'Не найден в реестре ЖНВЛС или не верная цена реестра'
,w.name_id, w.sname,w.izg_id,w.sizg, w.country_id,w.scountry,w.price, gb.price,w.seria,w.part_id,w.price_z,w.price_r,w.nac;
insert into analytic_GNVLS (
ANALYTIC_DOC_ID,
VID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
PRICE,
--MAXPRICE,
SERIA,
PART_ID,
Price_Z,
Price_R,
NAC)
select
:analytic_doc_id,
'Превышение наценки',
w.name_id,
w.sname,
w.izg_id,
w.sizg,
w.country_id,
w.scountry,
w.price,
--iif(w.price_z>w.price_r*1.1 and w.price_r>0,w.price_r*1.1,w.price_z)*iif(w.price_z*100/110>500,:nac3,iif(w.price_z*100/110>50,:nac2,:nac1))/100+w.price_o,
w.seria ,
w.part_id,
w.price_z,
w.price_r,
w.nac
from vw_doc_detail w left join docs d on d.id=w.doc_id
where
(exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id))
and w.price_r<>0 and w.price_z<>0 and w.price_z>0 and w.quant>0 and
( ( not (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=47 and grouptable_id=w.name_id)) and
100*(w.price-w.Price_O)/iif(w.price_z*100/110>w.price_r and
w.price_r>0,w.price_r*1.1,w.price_z) >iif(w.price_z*100/110>500,:nac3,iif(w.price_z*100/110>50,:nac2,:nac1))
)
or
( (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and
(exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=47 and grouptable_id=w.name_id)) and w.price_r<>0 and w.price_z<>0 and
100*(w.price-w.Price_O)/iif(w.price_z*100/110>w.price_r and w.price_r>0,w.price_r*1.1,w.price_z) > :nac4
) ) and d.doc_type in (6,11,39) and w.doc_commitdate between :periodbeg and :periodend;
insert into analytic_GNVLS (
ANALYTIC_DOC_ID,
VID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
PRICE,
--MAXPRICE,
SERIA,
PART_ID,
Price_Z,
Price_R,
NAC)
select
:analytic_doc_id,
'Не указана цена завода',
w.name_id,
w.sname,
w.izg_id,
w.sizg,
w.country_id,
w.scountry,
w.price,
--0,
w.seria ,
w.part_id,
w.price_z,
w.price_r,
w.nac
from vw_doc_detail w left join docs d on d.id=w.doc_id
-- where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=3 and grouptable_id=w.name_id)) and (w.price_z=0 or (w.price_z is null) ) and
where (exists(select id from group_detail where
grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and ((w.price_z between -0.000001 and 0.000001) or (w.price_z is null) ) and
w.doc_commitdate between :periodbeg and :periodend and d.doc_type in (6,11,39);
insert into analytic_GNVLS (
ANALYTIC_DOC_ID,
VID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
PRICE,
--MAXPRICE,
SERIA,
PART_ID,
Price_Z,
Price_R,
NAC)
select
:analytic_doc_id,
'Не указана цена рестра',
w.name_id,
w.sname,
w.izg_id,
w.sizg,
w.country_id,
w.scountry,
w.price,
--0,
w.seria ,
w.part_id,
w.price_z,
w.price_r,
w.nac
from vw_doc_detail w left join docs d on d.id=w.doc_id
-- where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=3 and grouptable_id=w.name_id)) and (w.price_r=0 or (w.price_r is null) ) and
where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and ((w.price_r between -0.00001 and 0.000001) or (w.price_r is null) ) and
w.doc_commitdate between :periodbeg and :periodend and d.doc_type in (6,11,39);
insert into analytic_GNVLS (
ANALYTIC_DOC_ID,
VID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
PRICE,
--MAXPRICE,
SERIA,
PART_ID,
Price_Z,
Price_R,
NAC)
select
:analytic_doc_id,
'Превышение наценки',
w.name_id,
w.sname,
w.izg_id,
w.sizg,
w.country_id,
w.scountry,
w.price,
--iif(w.price_z>w.price_r*1.1 and w.price_r>0,w.price_r*1.1,w.price_z)*iif(w.price_z*100/110>500,:nac3,iif(w.price_z*100/110>50,:nac2,:nac1))/100+w.price_o,
w.seria ,
w.part_id,
w.price_z,
w.price_r,
w.nac
from vw_doc_detail w left join docs d on d.id=w.doc_id
where
(exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and
(exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=47 and grouptable_id=w.name_id)) and w.price_r<>0 and w.price_z<>0 and
100*(w.price-w.Price_O)/iif(w.price_z*100/110>w.price_r and w.price_r>0,w.price_r*1.1,w.price_z) >:nac4 and
w.price_z>0 and d.doc_type in (6,11,39) and w.doc_commitdate between :periodbeg and :periodend;
insert into analytic_GNVLS (
ANALYTIC_DOC_ID,
VID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
PRICE,
--MAXPRICE,
SERIA,
PART_ID,
Price_Z,
Price_R,
NAC)
select
:analytic_doc_id,
'Укажите группу ЖНВЛС',
w.name_id,
w.sname,
w.izg_id,
w.sizg,
w.country_id,
w.scountry,
w.price,
--0,
w.seria ,
w.part_id,
w.price_z,
w.price_r,
w.nac
from vw_doc_detail w left join docs d on d.id=w.doc_id
left join out$gnvls_barcode gb on (w.bcode_izg = gb.barcode)or(w.barcode = gb.barcode)
where (not exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and (trim(gb.barcode)<>'' and gb.barcode is not null) and
w.doc_commitdate between :periodbeg and :periodend and d.doc_type in (6,11,39)
group by 'Укажите группу ЖНВЛС',w.name_id, w.sname,w.izg_id,w.sizg, w.country_id,w.scountry,w.price,w.seria,w.part_id,w.price_z,w.price_r,w.nac;
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT INSERT ON ANALYTIC_GNVLS TO PROCEDURE PR_GNVLSCHECK_SOURCE2;
GRANT SELECT ON VW_DOC_DETAIL TO PROCEDURE PR_GNVLSCHECK_SOURCE2;
GRANT SELECT ON DOCS TO PROCEDURE PR_GNVLSCHECK_SOURCE2;
GRANT SELECT ON OUT$GNVLS_BARCODE TO PROCEDURE PR_GNVLSCHECK_SOURCE2;
GRANT SELECT ON GROUP_DETAIL TO PROCEDURE PR_GNVLSCHECK_SOURCE2;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE PR_GNVLSCHECK_SOURCE2 TO SYSDBA;
SET TERM ^ ;
create or alter procedure PR_GNVLSCHECK (
ANALYTIC_DOC_ID DM_ID,
SOURCE DM_STATUS,
PERIODBEG DM_DATE,
PERIODEND DM_DATE)
returns (
VERB DM_TEXT1024)
as
declare variable PARAM_VALUE DM_DOUBLE;
declare variable PARAM_ID DM_TEXT;
declare variable NAC3 DM_DOUBLE;
declare variable NAC2 DM_DOUBLE;
declare variable NAC1 DM_DOUBLE;
declare variable NAC4 DM_DOUBLE;
begin
DELETE FROM analytic_GNVLS ad where ad.analytic_doc_id in (select id from analytic_docs a where a.analytic_type=3 and CURRENT_DATE-Cast(a.insertdt as dm_date)>=31);
DELETE from analytic_docs a where a.analytic_type=3 and CURRENT_DATE-Cast(a.insertdt as dm_date)>=31;
for select param_id,Cast(REPLACE(param_value,',','.') as dm_double) as param_value from PARAMS where parent_id =-50 into :param_id, :param_value do
begin
if (param_id='NAC1') then nac1 = param_value;
if (param_id='NAC2') then nac2 = param_value;
if (param_id='NAC3') then nac3 = param_value;
if (param_id='NAC4') then nac4 = param_value;
end
if (:source=0) then
execute procedure PR_GNVLSCHECK_SOURCE0( :ANALYTIC_DOC_ID, :PERIODBEG, :PERIODEND, :nac1, :nac2, :nac3, :nac4);
if (:source=1) then
execute procedure PR_GNVLSCHECK_SOURCE1( :ANALYTIC_DOC_ID, :PERIODBEG, :PERIODEND, :nac1, :nac2, :nac3, :nac4);
if (:source=2) then
execute procedure PR_GNVLSCHECK_SOURCE2( :ANALYTIC_DOC_ID, :PERIODBEG, :PERIODEND, :nac1, :nac2, :nac3, :nac4);
verb='Готово';
delete from analytic_GNVLS ag where
(select gd.id from group_detail gd left join groups g on g.id=gd.group_id where
gd.grouptable_id=ag.PART_ID and gd.grouptable='PARTS' and g.caption containing 'исклю') is not null;
suspend;
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT SELECT,DELETE ON ANALYTIC_GNVLS TO PROCEDURE PR_GNVLSCHECK;
GRANT SELECT,DELETE ON ANALYTIC_DOCS TO PROCEDURE PR_GNVLSCHECK;
GRANT SELECT ON PARAMS TO PROCEDURE PR_GNVLSCHECK;
GRANT EXECUTE ON PROCEDURE PR_GNVLSCHECK_SOURCE0 TO PROCEDURE PR_GNVLSCHECK;
GRANT EXECUTE ON PROCEDURE PR_GNVLSCHECK_SOURCE1 TO PROCEDURE PR_GNVLSCHECK;
GRANT EXECUTE ON PROCEDURE PR_GNVLSCHECK_SOURCE2 TO PROCEDURE PR_GNVLSCHECK;
GRANT SELECT ON GROUP_DETAIL TO PROCEDURE PR_GNVLSCHECK;
GRANT SELECT ON GROUPS TO PROCEDURE PR_GNVLSCHECK;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE PR_GNVLSCHECK TO SYSDBA;
теги
PR_GNVLSCHECK, проверка на, ЖНВЛС, ЖВ
