/******************************************************************************/
/***                Generated by IBExpert 05.07.2024 9:18:49                ***/
/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/


CREATE GENERATOR GEN_MARK_DETAIL_KZ_ID;

CREATE TABLE MARK_DETAIL_KZ (
    ID               DM_ID NOT NULL /* DM_ID = BIGINT */,
    DOC_ID           DM_ID /* DM_ID = BIGINT */,
    PART_ID          DM_ID /* DM_ID = BIGINT */,
    MARK_DATA        DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    QUANT            DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    INSERTDT         DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    DOCNUM           DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    DDA_ID           DM_ID /* DM_ID = BIGINT */,
    GTIN             DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    SERIES_NUMBER    DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    ACTIVEPOS        DM_ID /* DM_ID = BIGINT */,
    SERIA            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    KIZ              DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
    EXPIRATION_DATE  DM_DATE /* DM_DATE = DATE */,
    SSCC             DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    STATUS           DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    AGREGATION       DM_ID /* DM_ID = BIGINT */,
    ERRORTEXT        DM_TEXT_BIG /* DM_TEXT_BIG = VARCHAR(10000) */,
    ISNEW            DM_STATUS /* DM_STATUS = INTEGER */
);




/******************************************************************************/
/***                              Primary keys                              ***/
/******************************************************************************/

ALTER TABLE MARK_DETAIL_KZ ADD CONSTRAINT PK_MARK_DETAIL_KZ PRIMARY KEY (ID);


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: MARK_DETAIL_KZ_BI */
CREATE OR ALTER TRIGGER MARK_DETAIL_KZ_BI FOR MARK_DETAIL_KZ
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then new.id = gen_id(gen_mark_detail_KZ_id,1);
  if (new.insertdt is null) then new.insertdt = current_timestamp;
  if (new.doc_id is null) then new.doc_id = 0;
  if (new.part_id is null) then new.part_id = 0;
end
^


/* Trigger: MARK_DETAIL_KZ_BIU0 */
CREATE OR ALTER TRIGGER MARK_DETAIL_KZ_BIU0 FOR MARK_DETAIL_KZ
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
as
begin
  new.SERIES_NUMBER = substring(new.MARK_DATA from 19 for 13);
 -- new.GTIN = substring(new.MARK_DATA from 1 for 14);
  if (new.AGREGATION is null) then
    new.AGREGATION = 0;
end
^

SET TERM ; ^



/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/

SET TERM ^ ;

create or alter procedure PR_MARK_COUNT_KZ (
    DDA_ID DM_ID)
returns (
    CNT DM_TEXT,
    CONCATENATION DM_TEXT)
as
declare variable DOC_TYPE DM_ID;
declare variable DOC_ID DM_ID;
declare variable Q1 DM_DOUBLE;
declare variable Q2 DM_DOUBLE;
declare variable QUANTALL DM_TEXT;
declare variable QUANT DM_DOUBLE;
declare variable QUANTNOTCOR DM_TEXT;
begin
  /*
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!Процедура создается или изменяется программой МЕНЕДЖЕР, изменение невозможно!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*/
  Q2 = 0;
  Q1 = 0;
  QUANT = 0;
--В активном документе
  select (round(QUANT, 2)), DOC_ID
  from DOC_DETAIL_ACTIVE DD
  where DD.ID = :DDA_ID
  into QUANT, :DOC_ID;

  select D.DOC_TYPE
  from DOCS D
  where D.ID = :DOC_ID
  into :DOC_TYPE;
--на приход
  select coalesce(sum(M.QUANT), 0)
  from MARK_DETAIL_KZ M
  where M.DDA_ID = :DDA_ID and
        M.ACTIVEPOS = 1
  into Q1;
--на расход
  select coalesce(sum(M.QUANT), 0)
  from MARK_DETAIL_KZ M
  where M.DDA_ID = :DDA_ID and
        M.ACTIVEPOS = 2
  into Q2;
--Добавлено для больниц
  if (:DOC_TYPE not in (8, 58, 59, 7)) then
  begin
    if (QUANT > 0) then
    begin
      QUANTALL = cast(:QUANT as DM_TEXT);
      QUANTNOTCOR = cast((:QUANT - :Q1) as DM_TEXT);
      CNT = :Q1;
    end
    else
    begin
      QUANTALL = cast((round((:QUANT * -1), 2)) as DM_TEXT);
      QUANTNOTCOR = cast((round((:QUANT * -1) + :Q2, 2)) as DM_TEXT);
      CNT = abs(round((:QUANT * -1) + :Q2, 2) - (round((:QUANT * -1), 2)));
    end
  end
  else
--Переоценка
  begin
    QUANTALL = cast(:QUANT as DM_TEXT);
    QUANTNOTCOR = cast((:QUANT + :Q2) as DM_TEXT);
    CNT = abs(:Q2);
  end
--Режим нули2
  while (((select substring(:QUANTALL from char_length(:QUANTALL) for 1) from RDB$DATABASE) in ('0', '.')) and (char_length(:QUANTALL) > 1) and (position('.' in :QUANTALL) > 0) and ((select substring(:QUANTALL from char_length(:QUANTALL) - 1 for 1) from RDB$DATABASE) <> ' ')) do
  begin
    QUANTALL = (select substring(:QUANTALL from 1 for char_length(:QUANTALL) - 1)
                from RDB$DATABASE);
  end
  while (((select substring(:QUANTNOTCOR from char_length(:QUANTNOTCOR) for 1) from RDB$DATABASE) in ('0', '.')) and (char_length(:QUANTNOTCOR) > 1) and (position('.' in :QUANTNOTCOR) > 0) and ((select substring(:QUANTNOTCOR from char_length(:QUANTNOTCOR) - 1 for 1) from RDB$DATABASE) <> ' ')) do
  begin
    QUANTNOTCOR = (select substring(:QUANTNOTCOR from 1 for char_length(:QUANTNOTCOR) - 1)
                   from RDB$DATABASE);
  end
  CONCATENATION = 'Кол-во: ' || :QUANTALL || ' / Некорректных: ' || :QUANTNOTCOR;
  suspend;
end^

SET TERM ; ^

/* Following GRANT statements are generated automatically */

GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_MARK_COUNT_KZ;
GRANT SELECT ON DOCS TO PROCEDURE PR_MARK_COUNT_KZ;
GRANT SELECT ON MARK_DETAIL_KZ TO PROCEDURE PR_MARK_COUNT_KZ;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_MARK_COUNT_KZ TO SYSDBA;

SET TERM ^ ;

create or alter procedure PR_UPDATEMARKBYPARTKZ (
    DOC_ID DM_ID,
    PART_ID DM_ID,
    ACTIVE_ID DM_ID)
as
begin
--Если не переоценка то
--Обновляем партию привязанную к SGTIN
  if ((select D.DOC_TYPE
       from DOCS D
       where D.ID = :DOC_ID) not in (8, 7, 58, 59)) then
  begin
    update MARK_DETAIL_KZ
    set PART_ID = :PART_ID
    where DDA_ID = :ACTIVE_ID;
  end
  else
-- При переоценки вставляем записи в mark_detail
  begin
    insert into MARK_DETAIL_KZ (DOC_ID, PART_ID, MARK_DATA, QUANT, DOCNUM,  DDA_ID,
                             GTIN, SERIES_NUMBER, EXPIRATION_DATE, ACTIVEPOS, SERIA, KIZ, SSCC,
                             ERRORTEXT, AGREGATION)
    select :DOC_ID, :PART_ID, MARK_DATA, -QUANT, DOCNUM,
           (select PARENT_ID
            from DOC_DETAIL_ACTIVE
            where ID = :ACTIVE_ID),  GTIN, SERIES_NUMBER, EXPIRATION_DATE, 1, SERIA, KIZ,
           SSCC,   '', AGREGATION
    from MARK_DETAIL_KZ MD
    where MD.DDA_ID = :ACTIVE_ID;
  end
  suspend;
end^

SET TERM ; ^

/* Following GRANT statements are generated automatically */

GRANT SELECT ON DOCS TO PROCEDURE PR_UPDATEMARKBYPARTKZ;
GRANT SELECT,INSERT,UPDATE ON MARK_DETAIL_KZ TO PROCEDURE PR_UPDATEMARKBYPARTKZ;
GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_UPDATEMARKBYPARTKZ;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_UPDATEMARKBYPARTKZ TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_UPDATEMARKBYPARTKZ TO SYSDBA;


SET TERM ^ ;

create or alter procedure PR_DOC_PRIHOD_COMMIT_RECURSE (
    DOC_ID type of DM_ID,
    DOC_PARENT_ID type of DM_ID,
    PART_PARENT_ID type of DM_ID,
    DOC_COMMITDATE type of DM_DATE,
    CONTRACT_ID DM_ID)
as
declare variable PART_ID type of DM_ID;
declare variable WARE_ID type of DM_UUID_NULL;
declare variable SNAME type of DM_TEXT;
declare variable SIZG type of DM_TEXT;
declare variable SCOUNTRY type of DM_TEXT;
declare variable ORIG_CODE type of DM_TEXT;
declare variable SORIG_NAME type of DM_TEXT;
declare variable SORIG_IZG type of DM_TEXT;
declare variable SORIG_COUNTRY type of DM_TEXT;
declare variable BARCODE type of DM_TEXT;
declare variable Z_ID type of DM_ID;
declare variable SKLAD_ID DM_TEXT;
declare variable PRICE type of DM_DOUBLE;
declare variable PRICE_O type of DM_DOUBLE;
declare variable PRICE_Z type of DM_DOUBLE;
declare variable PRICE_R type of DM_DOUBLE;
declare variable QUANT type of DM_DOUBLE;
declare variable BARCODE1 type of DM_TEXT;
declare variable DEP type of DM_ID;
declare variable KRITK type of DM_DOUBLE;
declare variable GODENDO type of DM_DATETIME;
declare variable SERIA type of DM_TEXT;
declare variable NDS type of DM_DOUBLE;
declare variable SUM_NDSO type of DM_DOUBLE;
declare variable SUM_NDSR DM_DOUBLE;
declare variable SERT type of DM_TEXT;
declare variable DATESERT type of DM_DATETIME;
declare variable KEMVSERT type of DM_TEXT;
declare variable SDSERT type of DM_DATETIME;
declare variable REGN type of DM_TEXT;
declare variable NGTD type of DM_TEXT;
declare variable EDIZM type of DM_TEXT;
declare variable PARENT_ID type of DM_ID;
declare variable SUMMA type of DM_DOUBLE;
declare variable SUMMA_O type of DM_DOUBLE;
declare variable DISCOUNT type of DM_DOUBLE;
declare variable ACTIVE_ID type of DM_ID;
declare variable NAC type of DM_DOUBLE;
declare variable DOC_DETAIL_ID type of DM_ID;
declare variable MOTHERPART_ID type of DM_ID;
declare variable BCODE_IZG type of DM_TEXT;
declare variable HUMAN_QUANT type of DM_TEXT;
declare variable SUM_DSC type of DM_DOUBLE;
declare variable DCARD type of DM_TEXT;
declare variable PART_TYPE DM_STATUS;
declare variable BASE_AGENT_ID DM_ID;
declare variable GROUP_ID DM_ID;
declare variable MAKE_ID DM_ID_NULL;
declare variable MNN DM_TEXT;
declare variable MOTHERPART_UUID DM_UUID_NULL;
declare variable EGAIS_ID DM_TEXT1024;
declare variable EGAIS_BREGID DM_TEXT1024;
declare variable EGAIS_REGID DM_TEXT1024;
declare variable EGAIS_ALCCODE DM_TEXT1024;
declare variable CAPACITY DM_DOUBLE;
declare variable ALC_VOLUME DM_DOUBLE;
declare variable EGAIS_PRODUCER_ID DM_TEXT1024;
declare variable EGAIS_QUANT DM_DOUBLE;
declare variable EGAIS_BARCODE DM_TEXT1024;
declare variable EGAIS_TYPE DM_TEXT;
declare variable PRODUCER_INN DM_TEXT1024;
declare variable PRODUCER_KPP DM_TEXT1024;
declare variable BOTTLINGDATE DM_DATETIME;
declare variable EGAIS_PRODUCT_VCODE DM_TEXT;
begin
    --whith SUM_NDSR
    for
    select
    /*ware_id*/  sname,sizg,scountry,orig_code,sorig_name,sorig_izg,sorig_country,barcode,z_id,sklad_id,
    /*part_id*/  PRICE,PRICE_O,PRICE_Z,PRICE_R,QUANT,BARCODE1,/*KRITK,*/GODENDO,SERIA,NDS,SUM_NDSO,SUM_NDSR,SERT,DATESERT,KEMVSERT,SDSERT,REGN,NGTD,EDIZM,
    PARENT_ID,DISCOUNT,SUMMA,summa_o, id, part_id, nac, doc_detail_id, motherpart_id, dep, bcode_izg, human_quant, SUM_DSC, dcard,
    part_type, BASE_AGENT_ID, group_id, make_id, mnn, motherpart_uuid,
    EGAIS_ID, EGAIS_BREGID, EGAIS_REGID, EGAIS_ALCCODE, CAPACITY, ALC_VOLUME, EGAIS_PRODUCER_ID, EGAIS_QUANT, EGAIS_BARCODE,
    EGAIS_TYPE, PRODUCER_INN, PRODUCER_KPP, BOTTLINGDATE, EGAIS_PRODUCT_VCODE
    from doc_detail_active where doc_id=:doc_id and part_parent_id=:doc_parent_id
    into
    :sname,:sizg,:scountry,:orig_code,:sorig_name,:sorig_izg,:sorig_country,:barcode,:z_id,:sklad_id,
    :PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE1,/*:KRITK,*/:GODENDO,:SERIA,:NDS,:SUM_NDSO,:SUM_NDSR,:SERT,:DATESERT,:KEMVSERT,:SDSERT,:REGN,:NGTD,:EDIZM,
    :PARENT_ID,:DISCOUNT,:SUMMA,:summa_o, :active_id, :part_id, :nac, :doc_detail_id, :motherpart_id, :dep, :bcode_izg, :human_quant, :SUM_DSC, :dcard,
    :part_type, :BASE_AGENT_ID, :group_id,:make_id, :mnn, :motherpart_uuid,
    :EGAIS_ID, :EGAIS_BREGID, :EGAIS_REGID, :EGAIS_ALCCODE, :CAPACITY, :ALC_VOLUME, :EGAIS_PRODUCER_ID, :EGAIS_QUANT, :EGAIS_BARCODE,
    :EGAIS_TYPE, :PRODUCER_INN, :PRODUCER_KPP, :BOTTLINGDATE, :EGAIS_PRODUCT_VCODE
    do
    begin
    --    if ((price<0.001) or (price is null)) then exception EX_PRICE;
    if ((price<0) or (price is null)) then exception EX_PRICE;
    if (/*(price_o<0.001) or */(price_o is null)) then exception EX_PRICE_O;
    if (/*(quant<0.00001) or */(quant is null)) then exception EX_QUANT;
    if (/*(summa<0.00001) or */(summa is null)) then exception EX_SUMMA;
    if (/*(summa_o<0.00001) or */(summa_o is null)) then exception EX_SUMMA_O;
    --    if ((NDS<0.00001) or (NDS is null)) then exception EX_NDS;
    if (/*(SUM_NDSO<0) or */(SUM_NDSO is null or SUM_NDSR is null )) then exception EX_SUM_NDSO;
    select w_id from pr_get_ware(:sname,:sizg,:scountry,:orig_code,:sorig_name,:sorig_izg,:sorig_country,:bcode_izg,:z_id,:sklad_id,:part_type,:mnn)
    into :ware_id;
    if ((part_id=0) or (part_id is NULL)) then
    select p_id from pr_new_part(:DOC_ID,:part_parent_id,:WARE_ID,:PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE,:BARCODE1,:DEP,/*:KRITK,*/:GODENDO,:SERIA,:NDS,
    :SUM_NDSO,:SERT,:DATESERT,:KEMVSERT,:SDSERT,:REGN,:NGTD,:EDIZM,:NAC,:motherpart_id, :part_type,:BASE_AGENT_ID, :sklad_id, :contract_id, :active_id, :group_id,
    :motherpart_uuid, :EGAIS_ID, :EGAIS_REGID, :EGAIS_BREGID, :EGAIS_ALCCODE, :EGAIS_QUANT, :CAPACITY, :ALC_VOLUME, :EGAIS_PRODUCER_ID, :EGAIS_BARCODE,
    :EGAIS_TYPE, :PRODUCER_INN, :PRODUCER_KPP, :BOTTLINGDATE, :EGAIS_PRODUCT_VCODE) into :part_id;
    execute procedure PR_DOC_DETAIL_INSERT(:doc_detail_id,:DOC_ID,:PART_ID,:QUANT,:DISCOUNT,:SUMMA,:summa_o,:price,:sum_ndso,:sum_ndsr,:nac,:doc_commitdate,:human_quant,:SUM_DSC,:dcard,:active_id,:part_type,:make_id);
    -- обновляем членство в группах
    execute procedure PR_UPDGROUPDETAIL_ACTIVE2PARTS(:active_id,:part_id);
    execute procedure pr_updatemarkbypartkz(:doc_id,:part_id,:active_id);
    -- Обновляем атрибут для партии
    -- execute procedure PR_UPGRADE_ATTR(:active_id,:part_id);
    -- присваиваем ценам партии
    update prices set part_id=:part_id where dda_id=:active_id;
    update or insert into prices (part_id,price_type,currency_id, price,dda_id) values (:part_id,0,0,:price,:active_id) matching (PART_ID,PRICE_TYPE);
    execute procedure PR_DOC_PRIHOD_COMMIT_RECURSE(:doc_id,:active_id,:part_id,:doc_commitdate,:contract_id);
    end
    end^

SET TERM ; ^

/* Следующие операторы GRANT сгенерированы автоматически */

GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_UPDGROUPDETAIL_ACTIVE2PARTS TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_UPDATEMARKBYPARTKZ TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT SELECT,INSERT,UPDATE ON PRICES TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;

/* Существующие привилегии на эту процедуру */

GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE TO SYSDBA;

CREATE OR ALTER VIEW VW_DOC_DETAIL_ACTIVE(
    OKPD2,
    IZG_BARCODE,
    GTIN_SUBJ,
    ACCEPTTYPE,
    MARK_BAD,
    MARK_WATER,
    GTIN,
    MARKING,
    MARK_BARCODE,
    MARK_BARCODE_KZ,
    ACCEPTED,
    ACCEPTANCE,
    AGENT_FIRST,
    DATE_FIRST_PRIHOD,
    REP_ID,
    ID,
    PARENT_ID,
    DOC_ID,
    PART_ID,
    PART_PARENT_ID,
    DOC_DETAIL_ID,
    PRICE,
    NAC,
    QUANT,
    DISCOUNT,
    SUMMA,
    SUMMA_O,
    DCARD,
    WARE_ID,
    PRICE_O,
    PRICE_Z,
    PRICE_R,
    BARCODE,
    BARCODE1,
    BCODE_IZG,
    KRITK,
    GODENDO,
    SERIA,
    NDS,
    SUM_NDSO,
    SUM_NDSR,
    SERT,
    DATESERT,
    KEMVSERT,
    SDSERT,
    REGN,
    NGTD,
    EDIZM,
    NAME_ID,
    IZG_ID,
    COUNTRY_ID,
    ORIG_CODE,
    ORIG_NAME_ID,
    ORIG_IZG_ID,
    ORIG_COUNTRY_ID,
    Z_ID,
    SKLAD_ID,
    SNAME,
    SIZG,
    SCOUNTRY,
    SORIG_NAME,
    SORIG_IZG,
    SORIG_COUNTRY,
    INSERTDT,
    INFO,
    KOEF,
    MOTHERPART_ID,
    DEP,
    MMBSH,
    NDSBYDEP,
    REALQUANT,
    PRICE_DSC,
    BLOCK_QUANT,
    SUM_DSC,
    HUMAN_QUANT,
    CUSTOMDRAW,
    STATUS,
    OLDPRICE,
    PART_TYPE,
    PRICES,
    BASE_AGENT_ID,
    SBASE_AGENT_ID,
    GNVLS,
    GROUP_ID,
    MAKE_ID,
    MNN,
    FIO,
    STORE_STRING,
    BARC_LABEL_COUNT,
    SGROUP_ID,
    MGN_NAME,
    MGN_ID,
    MGN_SOURCE,
    MOTHERPART_UUID,
    EGAIS_ID,
    EGAIS_BREGID,
    EGAIS_REGID,
    EGAIS_ALCCODE,
    CAPACITY,
    ALC_VOLUME,
    EGAIS_PRODUCER_ID,
    EGAIS_QUANT,
    BOTTLINGDATE,
    EGAIS_BARCODE,
    D$UUID,
    MARK_COUNT,
    MARK_COUNT_KZ,
    FIRST_SAGENT)
AS
select OKPD2,   (select CONCATENATION  from PR_IZG_BARCODE_COUNT(da.quant,da.id,da.doc_id)),  (select first 1 md.place_id_in from mark_detail md where md.part_id=da.part_id),  ACCEPTTYPE,  MARK_BAD,  MARK_WATER,  GTIN,  MARKING,
         (select concatenation  from pr_mark_count(da.id)),--MARK_BARCODE
         iif( char_length(da.gtin)=14 , (select concatenation  from pr_mark_count_kz(da.id)),''),-- MARK_BARCODE_KZ
         (--ACCEPTED
         case
           when char_length(DA.ACCEPTANCE) > 0 then 1
           else 0
         end
         ),
         ACCEPTANCE, DA.AGENT_FIRST, DA.DATE_FIRST_PRIHOD, REP_ID, DA.ID, PARENT_ID, DOC_ID, PART_ID, PART_PARENT_ID,
         DOC_DETAIL_ID, PRICE, cast(NAC as numeric(15,2)), QUANT,
         iif(((da.price=0) or (da.quant=0)),0,iif(abs(DISCOUNT)>0.01,DISCOUNT,-da.SUM_DSC/(da.price*da.quant)*100)),
         SUMMA, SUMMA_O, DCARD, WARE_ID, PRICE_O, PRICE_Z, PRICE_R, BARCODE, BARCODE1, BCODE_IZG,
         (select INTVALUE
          from VALS
          where ID = DA.NAME_ID), --KRITK,
         GODENDO, SERIA, DA.NDS, SUM_NDSO, SUM_NDSR, SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD, EDIZM, NAME_ID,
         IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID, Z_ID, SKLAD_ID, SNAME, SIZG,
         SCOUNTRY, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY, DA.INSERTDT, INFO, KOEF, MOTHERPART_ID, DEP,
         (select MEMBERSHIP
          from PR_MEMBERSHIPS('PARTS=' || DA.PART_ID || ';DOC_DETAIL_ACTIVE=' || DA.ID || ';PARTS.NAME_ID=' || DA.NAME_ID || ';PARTS.WARE_ID=' || da.ware_ID || ';PARTS.IZG_ID=' || DA.IZG_ID || ';', ascii_char(13) || ascii_char(10), 1)),
         DEPS.NDS,
         (select REALQUANT
          from WAREBASE W
          where W.PART_ID = DA.PART_ID), iif(coalesce(DA.QUANT, 0) = 0, 0, cast(DA.SUMMA / DA.QUANT as numeric(15,4))),
         (select BLOCK_QUANT
          from WAREBASE
          where PART_ID = DA.PART_ID), DA.SUM_DSC, DA.HUMAN_QUANT, DA.CUSTOMDRAW, DA.STATUS, iif(DA.PARENT_ID <> 0,
         (select DDD.PRICE
          from DOC_DETAIL_ACTIVE DDD
          where DDD.ID = DA.PARENT_ID), 0), PART_TYPE,
         (select S
         from PR_GETPARTPRICES(DA.PART_ID, DA.ID)), DA.BASE_AGENT_ID, iif(DA.BASE_AGENT_ID = 0, 'Основной склад',
         (select CAPTION
          from AGENTS
          where ID = DA.BASE_AGENT_ID)), DA.GNVLS, DA.GROUP_ID, DA.MAKE_ID, DA.MNN,
         (select OD.FIO
          from DOC_DETAIL_DOCTOR DOCDOC
          left join OUT$TRADERS OD on DOCDOC.DOCTOR_ID = OD.ID
          where DOCDOC.DOC_DETAIL_ACTIVE_ID = DA.ID),
         (select SVALUE
          from PR_GETATTRIBUTE('store_string', DA.NAME_ID)),
         (select SVALUE
          from PR_GETATTRIBUTE('barc_label_count', DA.NAME_ID)),
         --(select svalue from pr_getattribute('store_float', da.name_id)),
         (select GR.CAPTION
          from GROUPS GR
          where GR.ID = DA.GROUP_ID),
         (select MGN_NAME
          from WARES
          where ID = DA.WARE_ID) as MGN_NAME,
         (select MGN_ID
          from WARES
          where ID = DA.WARE_ID) as MGN_ID,
         (select MGN_SOURCE
          from WARES
          where ID = DA.WARE_ID) as MGN_SOURCE, DA.MOTHERPART_UUID, EGAIS_ID, EGAIS_BREGID, EGAIS_REGID, EGAIS_ALCCODE,
         CAPACITY, ALC_VOLUME, EGAIS_PRODUCER_ID, EGAIS_QUANT, BOTTLINGDATE, EGAIS_BARCODE, DA.D$UUID,
         (select CNT
          from PR_MARK_COUNT(DA.ID)),
          (select CNT
          from PR_MARK_COUNT_kz(DA.ID)),
          --add avo 09/12/19 контрагент первичного прихода (нужен для ценников)
          (select d.sagent from vw_docs d where d.id=(select max(dd.doc_id) from doc_detail dd where dd.part_id=da.part_id and dd.agent_first is not null))
from DOC_DETAIL_ACTIVE DA
left join DEPS on DA.DEP = DEPS.ID
order by DA.PARENT_ID, DA.SNAME, DA.ID
;