Просмотр исходного текста страницы Проверка на ЖНВЛС (ошибка в процедуре)

==Ошибка==
 При запуске аналитического отчета по проверке ЖНВЛС выходит ошибка на PR_GNVLSCHECK, 
 а при попытке её скомпилировать сообщение
 <nowiki>
too many Contexts of Relation/Views. Maximum allowed is 255
Error while parsing procedure PR_GNVLSCHECK's BLR
 </nowiki>

Выполните скрипт 

==Скрипт==
 <nowiki>
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;
 </nowiki>

==теги==
 PR_GNVLSCHECK, проверка на, ЖНВЛС, ЖВ