Автопроверка ЖВ и Фальсификаты — различия между версиями

Текущая версия на 15:47, 14 ноября 2016

Версия менеджера

Не ниже 2.272.85

Окно подсказок

В окне с подсказками отражается: отложенные документы, проверка ЖВ, детского питания, фальсификатов
Ф-ия срабатывает при каждом входе в программу менеджер.

1. Выполняем скрипт

ALTER TABLE ANALYTIC_DOCS DROP CONSTRAINT FK_ANALYTIC_DOCS_1;

delete from analytic_gnvls;
delete from analytic_falsificat;
delete from analytic_docs a where a.analytic_type in ( 3,6);
update analytics set id=-1 where id=3;
update analytics set id=-3 where id=6;
commit;

ALTER TABLE ANALYTIC_GNVLS
ADD PRICE_O DM_DOUBLE;

ALTER TABLE ANALYTIC_GNVLS
ADD PRICE_Z DM_DOUBLE;

ALTER TABLE ANALYTIC_GNVLS
ADD PRICE_R DM_DOUBLE;

ALTER TABLE ANALYTIC_GNVLS
ADD NAC DM_DOUBLE;


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,
    NAC5 DM_DOUBLE,
    REP_TYPE DM_STATUS)
as
begin
   if (REP_TYPE=1) then
   begin
   insert into analytic_GNVLS(ANALYTIC_DOC_ID,VID,MAXPRICE,PART_ID,NAC)
   select :analytic_doc_id,'Превышение максимальной розничной цены',gb.price,w.part_id,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,MAXPRICE,PART_ID,NAC)
   select :analytic_doc_id,'Не найден в реестре ЖНВЛС или не верная цена реестра',0,w.part_id,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,MAXPRICE,PART_ID,NAC)
   select :analytic_doc_id,'Превышение наценки', 0,w.part_id,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)))) 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,MAXPRICE,PART_ID,NAC)
   select :analytic_doc_id, 'Не указана цена завода',0,w.part_id,w.nac 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 between -0.000001 and 0.000001)  or (w.price_z is null) ) and w.quant>0 ;

   insert into analytic_GNVLS (ANALYTIC_DOC_ID,VID,MAXPRICE,PART_ID,NAC)
   select :analytic_doc_id,'Не указана цена рестра',0,w.part_id,w.nac 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 between -0.00001 and 0.000001)  or (w.price_r is null) ) and w.quant>0 ;

   insert into analytic_GNVLS (ANALYTIC_DOC_ID,VID,MAXPRICE,PART_ID,NAC)
   select :analytic_doc_id, 'Укажите группу ЖНВЛС',0,w.part_id,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

  if (REP_TYPE=2) then
  begin
  insert into analytic_GNVLS (ANALYTIC_DOC_ID,VID,MAXPRICE,PART_ID,NAC)
  select :analytic_doc_id,'Превышение наценки ДП',0,w.part_id,100*(w.price-w.Price_O)/(w.price_z*100/110)
     from vw_warebase w  where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=2  and grouptable_id=w.name_id))
      and w.price_z>0 and w.quant>0 and 100*(w.price-w.Price_O)/(w.price_z*100/110)> :nac5;

  insert into analytic_GNVLS (ANALYTIC_DOC_ID,VID,MAXPRICE,PART_ID,NAC)
  select :analytic_doc_id, 'Не указана цена завода ДП', 0,w.part_id,w.nac
     from warebase w where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=2 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 ;
  end
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 PROCEDURE PR_GNVLSCHECK;
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,
    NAC5 DM_DOUBLE,
    REP_TYPE DM_STATUS)
as
begin
   if (REP_TYPE=1) then
   begin
    insert into analytic_GNVLS(ANALYTIC_DOC_ID,VID,MAXPRICE,PART_ID,NAC)
   select :analytic_doc_id,'Превышение максимальной розничной цены',gb.price,w.part_id,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,MAXPRICE,PART_ID,NAC)
   select :analytic_doc_id,'Не найден в реестре ЖНВЛС или не верная цена реестра',0,w.part_id,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,MAXPRICE,PART_ID,NAC)
   select :analytic_doc_id,'Превышение наценки', 0,w.part_id,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,MAXPRICE,PART_ID,NAC)
   select :analytic_doc_id, 'Не указана цена завода',0,w.part_id,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,MAXPRICE,PART_ID,NAC)
   select :analytic_doc_id,'Не указана цена рестра',0,w.part_id,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,MAXPRICE,PART_ID,NAC)
   select :analytic_doc_id, 'Укажите группу ЖНВЛС',0,w.part_id,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

  if (REP_TYPE=2) then
  begin
  insert into analytic_GNVLS (ANALYTIC_DOC_ID,VID,MAXPRICE,PART_ID,NAC)
  select :analytic_doc_id,'Превышение наценки ДП',0,w.part_id,100*(w.price-w.Price_O)/(w.price_z*100/110)
     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 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=2  and grouptable_id=w.name_id))
      and w.price_z>0 and w.quant>0 and
      100*(w.price-w.Price_O)/(w.price_z*100/110)> :nac5;


    insert into analytic_GNVLS (ANALYTIC_DOC_ID,VID,MAXPRICE,PART_ID,NAC)
  select :analytic_doc_id, 'Не указана цена завода ДП', 0,w.part_id,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 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=2 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 ;
  end
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 PROCEDURE PR_GNVLSCHECK;
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,
    NAC5 DM_DOUBLE,
    REP_TYPE DM_STATUS)
as
begin
  if (REP_TYPE=1) then
  begin
    insert into analytic_GNVLS(ANALYTIC_DOC_ID,VID,MAXPRICE,PART_ID,NAC)
   select :analytic_doc_id,'Превышение максимальной розничной цены',gb.price,w.part_id,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,MAXPRICE,PART_ID,NAC)
   select :analytic_doc_id,'Не найден в реестре ЖНВЛС или не верная цена реестра',0,w.part_id,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,MAXPRICE,PART_ID,NAC)
   select :analytic_doc_id,'Превышение наценки', 0,w.part_id,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,MAXPRICE,PART_ID,NAC)
   select :analytic_doc_id, 'Не указана цена завода',0,w.part_id,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,MAXPRICE,PART_ID,NAC)
   select :analytic_doc_id,'Не указана цена рестра',0,w.part_id,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,MAXPRICE,PART_ID,NAC)
   select :analytic_doc_id,'Превышение наценки', 0,w.part_id,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,MAXPRICE,PART_ID,NAC)
   select :analytic_doc_id, 'Укажите группу ЖНВЛС',0,w.part_id,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


   if (REP_TYPE=2) then
   begin
   insert into analytic_GNVLS (ANALYTIC_DOC_ID,VID,MAXPRICE,PART_ID,NAC)
  select :analytic_doc_id,'Превышение наценки ДП',0,w.part_id,100*(w.price-w.Price_O)/(w.price_z*100/110)

      from vw_doc_detail w left join docs d on d.id=w.doc_id
      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=2  and grouptable_id=w.name_id))
      and w.price_z>0 and w.quant>0 and
      100*(w.price-w.Price_O)/(w.price_z*100/110)> :nac5;


   insert into analytic_GNVLS (ANALYTIC_DOC_ID,VID,MAXPRICE,PART_ID,NAC)
  select :analytic_doc_id, 'Не указана цена завода ДП', 0,w.part_id,w.nac
      from vw_doc_detail w left join docs d on d.id=w.doc_id
      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=2 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 ;
  end
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 PROCEDURE PR_GNVLSCHECK;
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,
    REP_TYPE DM_STATUS)
returns (
    VERB DM_TEXT)
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;
declare variable NAC5 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 = -:rep_type 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;
      if (param_id='NAC5') then nac5 = param_value;
     end

    if (:source=0) then
     execute procedure PR_GNVLSCHECK_SOURCE0( :ANALYTIC_DOC_ID, :PERIODBEG, :PERIODEND, :nac1, :nac2, :nac3, :nac4, :nac5,:REP_TYPE);

    if (:source=1) then
     execute procedure PR_GNVLSCHECK_SOURCE1( :ANALYTIC_DOC_ID, :PERIODBEG, :PERIODEND, :nac1, :nac2, :nac3, :nac4, :nac5,:REP_TYPE);

    if (:source=2) then
     execute procedure PR_GNVLSCHECK_SOURCE2( :ANALYTIC_DOC_ID, :PERIODBEG, :PERIODEND, :nac1, :nac2, :nac3, :nac4, :nac5, :REP_TYPE);


     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;
  verb='Готово';
   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;


SET TERM ^ ;

create or alter procedure PR_ANALYTIC_DEFAULT (
    SESSION_ID DM_ID)
returns (
    REP_CAPTION DM_TEXT,
    REP_COUNT DM_ID_NULL)
as
declare variable A_ID DM_ID;
declare variable OWNER_ID DM_ID;
declare variable DEL_A_ID DM_ID_NULL;
declare variable VERB DM_TEXT;
begin
  select s.user_id from sessions s where id=:session_id into :owner_id;

  for select id from  ANALYTIC_DOCS where ANALYTIC_TYPE in (-1,-2,-3) and OWNER_ID=:owner_id into del_a_id do
  begin
    delete from ANALYTIC_GNVLS a where a.analytic_doc_id=:del_a_id;
    delete from analytic_falsificat a where a.analytic_doc_id=:del_a_id;
    delete from ANALYTIC_DOCS where id=:del_a_id;
  end

  A_ID=gen_id(gen_analytic_docs_id,1);
  insert into ANALYTIC_DOCS(ID,ANALYTIC_TYPE,OWNER_ID,SESSION_ID) values (:a_id,-1,:OWNER_ID,:SESSION_ID);
  select verb from PR_GNVLSCHECK(:A_ID,0,current_date,current_date,1) into :verb;
  rep_count=(select count(1) as c_n from ANALYTIC_GNVLS where ANALYTIC_DOC_ID=:A_ID);
  if (rep_count>0) then
  begin
    REP_CAPTION='Проверка "ЖНВЛС"';
    suspend;
  end

  A_ID=gen_id(gen_analytic_docs_id,1);
  insert into ANALYTIC_DOCS(ID,ANALYTIC_TYPE,OWNER_ID,SESSION_ID) values (:a_id,-2,:OWNER_ID,:SESSION_ID);
  select verb from PR_GNVLSCHECK(:A_ID,0,current_date,current_date,2) into :verb;
  rep_count=(select count(1) as c_n from ANALYTIC_GNVLS where ANALYTIC_DOC_ID=:A_ID);
  if (rep_count>0) then
  begin
    REP_CAPTION='Проверка "Детское питание"';
    suspend;
  end

  A_ID=gen_id(gen_analytic_docs_id,1);
  insert into ANALYTIC_DOCS(ID,ANALYTIC_TYPE,OWNER_ID,SESSION_ID) values (:a_id,-3,:OWNER_ID,:SESSION_ID);
  select verb from pr_falsecheck(:A_ID,0) into :verb;
  rep_count=(select count(1) as c_n from analytic_falsificat where ANALYTIC_DOC_ID=:A_ID);
  if (rep_count>0) then
  begin
    REP_CAPTION='Проверка "Фальсификаты"';
    suspend;
  end
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON SESSIONS TO PROCEDURE PR_ANALYTIC_DEFAULT;
GRANT SELECT,INSERT,DELETE ON ANALYTIC_DOCS TO PROCEDURE PR_ANALYTIC_DEFAULT;
GRANT SELECT,DELETE ON ANALYTIC_GNVLS TO PROCEDURE PR_ANALYTIC_DEFAULT;
GRANT SELECT,DELETE ON ANALYTIC_FALSIFICAT TO PROCEDURE PR_ANALYTIC_DEFAULT;
GRANT EXECUTE ON PROCEDURE PR_GNVLSCHECK TO PROCEDURE PR_ANALYTIC_DEFAULT;
GRANT EXECUTE ON PROCEDURE PR_FALSECHECK TO PROCEDURE PR_ANALYTIC_DEFAULT;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_ANALYTIC_DEFAULT TO SYSDBA;

SET TERM ^ ;

CREATE OR ALTER procedure PR_FALSECHECK (
    ANALYTIC_DOC_ID bigint,
    SUSPEND_MODE integer)
returns (
    VERB DM_TEXT)
as
begin

    DELETE FROM analytic_falsificat ad where ad.analytic_doc_id in (select id from analytic_docs a where a.analytic_type=6 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;

    insert into analytic_FALSIFICAT (
      ANALYTIC_DOC_ID,
      VID,
      NAME_ID,
      SNAME,
      SNAME_FALSE,
      IZG_ID,
      SIZG,
      IZG_FALSE,
      COUNTRY_ID,
      SCOUNTRY,
      QUANT,
      PRICE,
      NUMLETTER,
      SERIA,
      SERIA_FALSE,
      COMMENTS,
      PART_ID,
      MMBSH
      )
    select
      :analytic_doc_id,
      'Подозрение на фальсификат',
      w.name_id,
      w.sname,
      gb.tovar,
      w.izg_id,
      w.sizg,
      gb.izg,
      w.country_id,
      w.scountry,
      w.quant,
      w.price,
      gb.numletter,
      w.seria,
      gb.seria,
      gb.comments,
      w.part_id,
      (select membership from PR_MEMBERSHIPS('PARTS=' || w.part_id || ';PARTS.NAME_ID=' || w.name_id || ';PARTS.NAME_ID=' || w.izg_id ||';',ascii_char(13)||ascii_char(10),1))
     from warebase w left join out$false gb on (w.seria = gb.seria)and(UPPER(Left(w.sname,4)) = UPPER(Left(gb.tovar,4)))
      where w.quant>0 and trim(gb.seria)<>'' and gb.seria is not null group by 'Подозрение на фальсификат',w.name_id, w.sname,gb.tovar,w.izg_id,w.sizg,gb.izg, w.country_id,w.scountry,w.quant, w.price, gb.numletter,w.seria,gb.seria,gb.comments,w.part_id;
   verb='Готово';
   suspend;
end^

SET TERM ; ^



update or INSERT INTO ANALYTICS (ID, PARENT_ID, CAPTION, STATUS, DESCRIPTION, DETAIL_TABLENAME, DATA, USERDATA, BINDATA)
               VALUES (-2, 0, 'Проверка ДП', 0, NULL, NULL, ';createsql - запрос создания документа аналитики
;----:session_id - предустановленный параметр - передается программой менеджер - номер текущей сессии
;:analytic_doc_id - предустановленный параметр - передается программой менеджер - код заранее созданного документа аналитики
; остальные параметры - создаются во время выполнения на основании ANALYTICS.USERDATA
[createsql]
select verb from PR_GNVLSCHECK(:analytic_doc_id,:SOURCE,:periodbeg,:periodend,2)

;в таблице детализации должно быть поле 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;

INSERT INTO PARAMS (ID, PARENT_ID, PARAM_ID, PARAM_CAPTION, PARAM_TYPE, PARAM_VALUE, AUDIT_ID, IMAGEINDEX, STATUS, INSERTDT, SORTING, PARAM_TYPE_DATA, PACKET, D$UUID, D$SRVUPDDT)
            VALUES (-201, 67, 'GV_FALS_CHECK', 'Проверять ЖВ и фальсификаты при старте', 14, '1', 0, NULL, 0, '2016-11-14 13:41:01', NULL, 'FIXEDITEMS SHOWONLYVALUES RETURNNAME
0=Не проверять
1=Проверять', 27426, 'B3EA712C-37E9-4BB4-8AB3-1193C2CD3934', '2000-01-01 00:00:00');

COMMIT WORK;

INSERT INTO GROUPS (ID, PARENT_ID, CAPTION, GROUPTABLE, STATUS, INSERTDT, SYSTEMFLAG, DESCRIPTION, IMAGEINDEX, COLOR, SORTING, BASE_AGENT_ID, SID)
            VALUES (-750, -430, 'Проверка наценок', 'TMS                            ', 0, '2016-11-14 10:41:32', 0, NULL, -1, NULL, NULL, 0, NULL);

INSERT INTO GROUPS (ID, PARENT_ID, CAPTION, GROUPTABLE, STATUS, INSERTDT, SYSTEMFLAG, DESCRIPTION, IMAGEINDEX, COLOR, SORTING, BASE_AGENT_ID, SID)
            VALUES (-700, -430, 'Проверка отложенных', 'TMS                            ', 0, '2016-11-14 10:41:32', 0, NULL, -1, NULL, NULL, 0, NULL);

UPDATE ANALYTICS SET 
    PARENT_ID = 0,
    CAPTION = 'Проверка ДП',
    STATUS = 0,
    DESCRIPTION = NULL,
    DETAIL_TABLENAME = NULL,
    DATA = ';createsql - запрос создания документа аналитики
;----:session_id - предустановленный параметр - передается программой менеджер - номер текущей сессии
;:analytic_doc_id - предустановленный параметр - передается программой менеджер - код заранее созданного документа аналитики
; остальные параметры - создаются во время выполнения на основании ANALYTICS.USERDATA
[createsql]
select verb from PR_GNVLSCHECK(:analytic_doc_id,:SOURCE,:periodbeg,:periodend,2)

;в таблице детализации должно быть поле 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',
    USERDATA = ';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]',
    BINDATA = NULL
WHERE (ID = -2);

UPDATE ANALYTICS SET 
    PARENT_ID = 0,
    CAPTION = 'Проверка ЖНВЛС',
    STATUS = 0,
    DESCRIPTION = NULL,
    DETAIL_TABLENAME = '                               ',
    DATA = ';createsql - запрос создания документа аналитики
;----:session_id - предустановленный параметр - передается программой менеджер - номер текущей сессии
;:analytic_doc_id - предустановленный параметр - передается программой менеджер - код заранее созданного документа аналитики
; остальные параметры - создаются во время выполнения на основании ANALYTICS.USERDATA
[createsql]
select verb from PR_GNVLSCHECK(:analytic_doc_id,:SOURCE,:periodbeg,:periodend,1)

;в таблице детализации должно быть поле 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',
    USERDATA = ';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]',
    BINDATA = NULL
WHERE (ID = -1);

UPDATE ANALYTICS SET 
    PARENT_ID = 0,
    CAPTION = 'Проверка фальсификат',
    STATUS = 0,
    DESCRIPTION = NULL,
    DETAIL_TABLENAME = '                               ',
    DATA = ';createsql - запрос создания документа аналитики
;----:session_id - предустановленный параметр - передается программой менеджер - номер текущей сессии
;:analytic_doc_id - предустановленный параметр - передается программой менеджер - код заранее созданного документа аналитики
; остальные параметры - создаются во время выполнения на основании ANALYTICS.USERDATA
[createsql]
select verb from PR_FALSECHECK(:analytic_doc_id,:SUSPEND_MODE)

;в таблице детализации должно быть поле ANALYTIC_DOC_ID = ANALYTIC_DOCS.id
[selectsql]
select * from analytic_FALSIFICAT order by id,sname

[deletesql]
delete from analytic_FALSIFICAT


[main]
SelectFieldExpression=sname
;cfFRtmpl - путь к шаблону пакетов cfFastReport
cfFRtmpl=analytics_proverka_false.cffr3
tablename=analytic_FALSIFICAT
report_id=34',
    USERDATA = ';NOW|STARTOFMONTH|STARTOFWEEK


[end]',
    BINDATA = NULL
WHERE (ID = -3);


COMMIT WORK;

2. Заполняем DATA

Выполняем запрос:
select * from groups where ((ID = -443) OR (ID = -750) or (ID=-700))
В поле DATA вставляем соответственно фалы
Файл:443.rar и
Файл:750.rar И
Файл:700.rar

3. Сетки

Заходим в менеджер создаем аналит. отчет по проверке ЖВ, проверке ДП и проверке фальсификат
Файл:Сетка фальс.rar и
Файл:Сетка ЖВ и ДП.rar