Менеджер: Проверка ЖНВЛС без НДС
Материал из wiki.standart-n.ru
Версия от 14:54, 21 октября 2015; BeTePoK (обсуждение | вклад)
Исправленная проверка ЖНВЛС из Аналитических отчётов, считается без НДС
После выполнения действий описанных ниже, получите:
- изменена таблица аналитики по ЖНВЛС - добавлена цена изготовителя и цена оптовая, всё без ндс
- изменена процедура заполнения проверки ЖНВЛС - заполняются вышеназванные поля и изменена цена на цену без ндс, + поправлен рассчёт максимальной розничной цены
- добавлена строка в GROUPS, - ТМСка 'Назначить наценку по приходу' на активный документ переоценки
После выполнение скрипта надо открыть поле DATA в редакторе блоб полей IBExpert строку с наименованием 'Назначить наценку по приходу', открыть файл Файл:GROUPS -213.TXT. после чего применить изменения и подтвердить транзакцию. В появившейся подпрограмме назначения наценки по приходу для активных документов корректировки проверить и подправить наценки
ALTER TABLE ANALYTIC_GNVLS ADD PRICE_Z DM_DOUBLE;
ALTER TABLE ANALYTIC_GNVLS ADD PRICE_O DM_DOUBLE;
UPDATE OR INSERT INTO ANALYTICS (ID, PARENT_ID, CAPTION, STATUS, DESCRIPTION, DETAIL_TABLENAME, DATA, USERDATA,BINDATA)
VALUES (3, 0, 'Проверка ЖНВЛС', 0, NULL, ' ', ';createsql - запрос создания документа аналитики
;----:session_id - предустановленный параметр - передается программой менеджер - номер текущей сессии
;:analytic_doc_id - предустановленный параметр - передается программой менеджер - код заранее созданного документа аналитики
; остальные параметры - создаются во время выполнения на основании ANALYTICS.USERDATA
[createsql]
select verb from PR_GNVLSCHECK(:analytic_doc_id,:SOURCE,:periodbeg,:periodend)
;в таблице детализации должно быть поле ANALYTIC_DOC_ID = ANALYTIC_DOCS.id
[selectsql]
select * from ANALYTIC_GNVLS order by id,sname
[deletesql]
delete from ANALYTIC_GNVLS
[main]
SelectFieldExpression=sname
;cfFRtmpl - путь к шаблону пакетов cfFastReport
cfFRtmpl=analytics_ProverkaGV.cffr3
tablename=ANALYTIC_GNVLS
report_id=31', ';NOW|STARTOFMONTH|STARTOFWEEK
[SOURCE]
type=list
caption=Поиск среди
mode=FIXEDITEMS SHOWONLYVALUES RETURNNAME
default=0
0=Остатков
1=Приходов
2=Расходов перемещений
[periodbeg]
type=date
caption=Начало периода
mode=
default=STARTOFMONTH
[periodend]
type=date
caption=Конец периода
mode=
default=NOW
[end]', NULL) MATCHING (ID);
COMMIT WORK;
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;
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;
end
--Поиск среди приходов
if (:source=1) then
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_o)
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/1.1,
w.price_o
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_o;
if (:source=0) then
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_o)
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/1.1,
w.price_o
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_o;
if (:source=2) then
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_o)
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/1.1,
w.price_o
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_o;
if (:source=0) then
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_o)
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/1.1,
w.price_o
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_o;
if (:source=1) then
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_o)
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/1.1,
w.price_o
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_o;
if (:source=2) then
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_o)
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/1.1,
w.price_o
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_o;
if (:source=0) then
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_o)
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))/110+w.price_o,
w.seria
,w.part_id,
w.price_z/1.1,
w.price_o
from 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
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)) and //20151013
w.price_r>0,w.price_r,w.price_z*100/110) >iif(w.price_z*100/110>=500,:nac3,iif(w.price_z*100/110>=50,:nac2,:nac1)) and
w.price_z>0 and w.quant>0 and w.mmbsh not containing 'Сильно';
if (:source=1) then
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_o)
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))/110+w.price_o,
w.seria
,w.part_id,
w.price_z/1.1,
w.price_o
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
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)) and //20151013
w.price_r>0,w.price_r,w.price_z*100/110) >iif(w.price_z*100/110>=500,:nac3,iif(w.price_z*100/110>=50,:nac2,:nac1)) and
w.price_z>0 and dt.base_type=1 and w.doc_commitdate between :periodbeg and :periodend and w.mmbsh not containing 'Сильно';
if (:source=2) then
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_o)
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))/110+w.price_o,
w.seria
,w.part_id,
w.price_z/1.1,
w.price_o
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<>0and 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) >iif(w.price_z*100/110>=500,:nac3,iif(w.price_z*100/110>=50,:nac2,:nac1)) and //20151013
w.price_r>0,w.price_r,w.price_z*100/110) >iif(w.price_z*100/110>=500,:nac3,iif(w.price_z*100/110>=50,:nac2,:nac1)) and
w.price_z>0 and d.doc_type in (6,11,39) and w.doc_commitdate between :periodbeg and :periodend and w.mmbsh not containing 'Сильно';
if (:source=0) then
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_o)
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/1.1,
w.price_o
from 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_z=0
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 ;
if (:source=1) then
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_o)
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/1.1,
w.price_o
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_z=0
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;
if (:source=2) then
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_o)
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/1.1,
w.price_o
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_z=0
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);
if (:source=0) then
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_o)
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/1.1,
w.price_o
from 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
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 ;
if (:source=1) then
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_o)
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/1.1,
w.price_o
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
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;
if (:source=2) then
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_o)
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/1.1,
w.price_o
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
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);
if (:source=0) then
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_o)
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/1.1,
w.price_o
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_o;
if (:source=1) then
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_o)
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/1.1,
w.price_o
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_o;
if (:source=2) then
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_o)
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/1.1,
w.price_o
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_o;
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,INSERT,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 SELECT ON VW_DOC_DETAIL TO PROCEDURE PR_GNVLSCHECK;
GRANT SELECT ON DOCS TO PROCEDURE PR_GNVLSCHECK;
GRANT SELECT ON DOC_TYPES TO PROCEDURE PR_GNVLSCHECK;
GRANT SELECT ON OUT$GNVLS_BARCODE TO PROCEDURE PR_GNVLSCHECK;
GRANT SELECT ON WAREBASE 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;
SET TERM ^ ;
CREATE OR ALTER VIEW VW_WAREBASE(
PART_ID,
WARE_ID,
SNAME,
SIZG,
SCOUNTRY,
ORIG_CODE,
SORIG_NAME,
SORIG_IZG,
SORIG_COUNTRY,
BCODE_IZG,
PRICE,
PRICE_O,
PRICE_Z,
PRICE_R,
QUANT,
BARCODE,
BARCODE1,
DEP,
KRITK,
GODENDO,
SERIA,
SUM_NDSO,
SERT,
DATESERT,
KEMVSERT,
SDSERT,
REGN,
NGTD,
EDIZM,
DOC_ID,
DOCNUM,
DOCDATE,
DOCCAPTION,
DOCAGENT,
DOCVNUM,
DOCVSHIFT,
INSERTDT,
UPDATEDT,
ENDDT,
NDS,
REALQUANT,
PART_PARENT_ID,
NAME_ID,
IZG_ID,
COUNTRY_ID,
NAC,
BLOCK_QUANT,
BLOCK_COUNT,
MMBSH,
PART_TYPE,
PRICES,
BASE_AGENT_ID,
SBASE_AGENT_ID,
CONTRACT_ID,
SCONTRACT_ID,
GROUP_ID,
SGROUP_ID,
VPART_ID,
SKLAD_ID,
MNN,
MGN_NAME,
PRICE_Z_WITHOUTNDS)
AS
select
PART_ID,
WARE_ID,
SNAME,
SIZG,
SCOUNTRY,
ORIG_CODE,
SORIG_NAME,
SORIG_IZG,
SORIG_COUNTRY,
BCODE_IZG,
PRICE,
PRICE_O,
PRICE_Z,
PRICE_R,
QUANT,
BARCODE,
BARCODE1,
DEP,
(select intvalue from vals where id=name_id), --KRITK,
GODENDO,
SERIA,
SUM_NDSO,
SERT,
DATESERT,
KEMVSERT,
SDSERT,
REGN,
NGTD,
EDIZM,
DOC_ID, /*(select pr.doc_id from pr_getmotherpart(part_id) pr), */
DOCNUM, /*(select (select ddd.docnum from docs ddd where ddd.id=pr.doc_id) from pr_getmotherpart(part_id) pr),*/
cast (DOCDATE as dm_date), /*(select (select ddd.docdate from docs ddd where ddd.id=pr.doc_id) from pr_getmotherpart(part_id) pr),*/
DOCCAPTION, /*(select (select ddd.caption from docs ddd where ddd.id=pr.doc_id) from pr_getmotherpart(part_id) pr),*/
DOCAGENT, /*(select (select ddd.sagent from vw_docs ddd where ddd.id=pr.doc_id) from pr_getmotherpart(part_id) pr),*/
DOCVNUM, /*(select (select ddd.vnum from docs ddd where ddd.id=pr.doc_id) from pr_getmotherpart(part_id) pr),*/
DOCVSHIFT, /*(select (select ddd.vshift from docs ddd where ddd.id=pr.doc_id) from pr_getmotherpart(part_id) pr),*/
INSERTDT,
UPDATEDT,
ENDDT,
NDS,
REALQUANT,
PART_PARENT_ID,
NAME_ID,
IZG_ID,
COUNTRY_ID,
NAC,
BLOCK_QUANT,
BLOCK_COUNT,
(select membership from PR_MEMBERSHIPS('PARTS=' || wb.part_id || ';PARTS.NAME_ID=' || wb.name_id || ';PARTS.IZG_ID=' || wb.izg_id
||';',ascii_char(13)||ascii_char(10),1)),
part_type,
(select s from PR_GETPARTPRICES(wb.part_id,0)),
BASE_AGENT_ID,
(select caption from agents where id=wb.BASE_AGENT_ID),
CONTRACT_ID,
SCONTRACT_ID,
GROUP_ID,
(select gr.caption from groups gr where gr.id=wb.group_id),
VPART_ID,
(select sklad_id from parts where parts.id=wb.part_id),
(select preparedvalue from vals where id=name_id), --mnn
(select mgn_name from wares where id = wb.ware_id),
iif(coalesce(NDS,0)<>0,(PRICE_Z*(100/(100+NDS))),0)
from WAREBASE wb;
^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT SELECT,INSERT,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 SELECT ON VW_DOC_DETAIL TO PROCEDURE PR_GNVLSCHECK;
GRANT SELECT ON DOCS TO PROCEDURE PR_GNVLSCHECK;
GRANT SELECT ON DOC_TYPES TO PROCEDURE PR_GNVLSCHECK;
GRANT SELECT ON OUT$GNVLS_BARCODE TO PROCEDURE PR_GNVLSCHECK;
GRANT SELECT ON WAREBASE 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;
