Обновление процедур проведения документа - маркировка — различия между версиями

Версия 14:25, 26 ноября 2020

SET TERM ^ ;

create or alter procedure PR_DOC_DETAIL_INSERT (
    PARENT_ID bigint,
    DOC_ID bigint,
    PART_ID bigint,
    QUANT type of DM_DOUBLE,
    DISCOUNT double precision,
    SUMMA double precision,
    SUMMA_O double precision,
    PRICE numeric(15,4),
    SUM_NDSO numeric(15,4),
    SUM_NDSR DM_DOUBLE,
    NAC numeric(15,4),
    COMMITDATE type of DM_DATE,
    HUMAN_QUANT type of DM_TEXT,
    SUM_DSC type of DM_DOUBLE,
    DCARD type of DM_TEXT,
    ACTIVE_ID type of DM_ID,
    PART_TYPE DM_STATUS,
    MAKE_ID DM_ID_NULL = null,
    ACCEPTANCE DM_TEXT = null,
    AGENT_FIRST DM_TEXT = null,
    DATE_FIRST_PRIHOD DM_DATETIME = null,
    MARKING DM_ID_NULL = null,
    DATEPROD DM_DATE = null,
    GTIN_SUBJ DM_TEXT = null,
    ACCEPTTYPE DM_ID = null)
as
declare variable NEW_ID type of DM_ID;
begin
  new_id=gen_id(gen_doc_detail_id,1);
  insert into doc_detail
    (id,PARENT_ID,DOC_ID,PART_ID,QUANT,DISCOUNT,SUMMA,summa_o, sum_ndso,SUM_NDSR, price, nac, doc_commitdate, human_quant,SUM_DSC,dcard, part_type,make_id,
    acceptance,agent_first,date_first_prihod
/*26/06/2020 AVO Добавлены поля для маркировки*/
    ,MARKING,DATEPROD,GTIN_SUBJ,ACCEPTTYPE
/* end 26/06/2020 AVO*/
    )
    /* (PARENT_ID,DOC_ID,PART_ID,QUANT,DISCOUNT,SUMMA,summa_o) */
   values
    /* (:PARENT_ID,:DOC_ID,:PART_ID,:QUANT,:DISCOUNT,:SUMMA,:summa_o); */
    (:new_id,:PARENT_ID,:DOC_ID,:PART_ID,:QUANT,:DISCOUNT,:SUMMA,:summa_o, :sum_ndso,:SUM_NDSR, :price, :nac, :commitdate, :human_quant,:SUM_DSC,:dcard, :part_type,:make_id,
    :acceptance,:agent_first,:DATE_FIRST_PRIHOD
/*26/06/2020 AVO Добавлены поля для маркировки*/
    ,:MARKING,:DATEPROD,:GTIN_SUBJ,:ACCEPTTYPE
/* end 26/06/2020 AVO*/
    );
   execute procedure PR_DOC_DETAIL_ACTIVE_ONCOMMIT(:active_id,:new_id,:doc_id,:commitdate);
end^

SET TERM ; ^

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

GRANT INSERT ON DOC_DETAIL TO PROCEDURE PR_DOC_DETAIL_INSERT;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_ACTIVE_ONCOMMIT TO PROCEDURE PR_DOC_DETAIL_INSERT;

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

GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO PROCEDURE PR_DOC_RASHOD_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT 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;
declare variable DATE_FIRST_PRIHOD DM_DATETIME = null;
declare variable AGENT_FIRST DM_TEXT = null;
declare variable ACCEPTANCE DM_TEXT = null;
declare variable DATEPROD DM_DATE;
declare variable MARKING DM_ID_NULL;
declare variable GTIN_SUBJ DM_TEXT;
declare variable ACCEPTTYPE DM_ID_NULL;
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,acceptance,agent_first,DATE_FIRST_PRIHOD
/*26/06/2020 AVO Добавлены поля для маркировки*/
    ,MARKING,DATEPROD,GTIN_SUBJ,ACCEPTTYPE
/* end 26/06/2020 AVO*/
    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,:acceptance,:agent_first,:DATE_FIRST_PRIHOD
/*26/06/2020 AVO Добавлены поля для маркировки*/
    ,:MARKING,:DATEPROD,GTIN_SUBJ,ACCEPTTYPE
/* end 26/06/2020 AVO*/
    do
    begin
    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,: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,:date_first_prihod,:agent_first) 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,:acceptance,:agent_first,:DATE_FIRST_PRIHOD
/*26/06/2020 AVO Добавлены поля для маркировки*/
    ,:MARKING,:DATEPROD,:GTIN_SUBJ,:ACCEPTTYPE
/* end 26/06/2020 AVO*/
    );
    -- обновляем членство в группах
    execute procedure PR_UPDGROUPDETAIL_ACTIVE2PARTS(:active_id,:part_id);


    --25/11/2020 Обновляем партию привязанную к SGTIN
    execute procedure PR_UPDATEMARKBYPART(:DOC_ID, :PART_ID, :ACTIVE_ID);
    --update  MARK_DETAIL   set part_id=:part_id
    --           where DDA_ID = :ACTIVE_ID;
    -- 24/03/2020 <--

    -- Обновляем атрибут для партии
    -- 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_UPDATEMARKBYPART 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;

SET TERM ^ ;

create or alter procedure PR_DOC_RASHOD_COMMIT (
    DOC_ID type of DM_ID,
    DBG type of DM_STATUS)
as
declare variable PARENT_ID type of DM_ID;
declare variable PART_ID type of DM_ID;
declare variable PRICE type of DM_DOUBLE;
declare variable QUANT type of DM_DOUBLE;
declare variable DISCOUNT type of DM_DOUBLE;
declare variable SUMMA type of DM_DOUBLE;
declare variable SUMMA_O type of DM_DOUBLE;
declare variable SUM_NDSO type of DM_DOUBLE;
declare variable SUM_NDSR DM_DOUBLE;
declare variable NAC type of DM_DOUBLE;
declare variable DOC_COMMITDATE type of DM_DATE;
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 ACTIVE_ID type of DM_ID;
declare variable PART_TYPE DM_STATUS;
declare variable BASE_AGENT_ID DM_ID;
declare variable MAKE_ID DM_ID_NULL;
declare variable ACCEPTTYPE DM_ID_NULL;
declare variable MARKING DM_ID_NULL;
declare variable DATEPROD DM_DATE;
declare variable GTIN_SUBJ DM_TEXT;
begin
  select cast(commitdate as date) from docs where id=:doc_id into :doc_commitdate;
  for select PARENT_ID,DOC_ID,PART_ID,PRICE,QUANT,DISCOUNT,SUMMA,summa_o, sum_ndso,SUM_NDSR, nac, human_quant, SUM_DSC, dcard, part_type, id, make_id
/*26/06/2020 AVO Добавлены поля для маркировки*/
   ,MARKING,DATEPROD,GTIN_SUBJ,ACCEPTTYPE
/* end 26/06/2020 AVO*/
  from doc_detail_active where doc_id=:doc_id
  into :PARENT_ID,:DOC_ID,:PART_ID,:PRICE,:QUANT,:DISCOUNT,:SUMMA,:summa_o, :sum_ndso,:SUM_NDSR, :nac, :human_quant, :SUM_DSC, :dcard, :part_type, :active_id, :make_id
/*26/06/2020 AVO Добавлены поля для маркировки*/
  ,:MARKING,:DATEPROD,:GTIN_SUBJ,:ACCEPTTYPE
/* end 26/06/2020 AVO*/
  do
   execute procedure PR_DOC_DETAIL_INSERT(:PARENT_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
   /*26/06/2020 AVO Добавлены поля для маркировки*/
   ,null, null, null, :MARKING,:DATEPROD,:GTIN_SUBJ,:ACCEPTTYPE
/* end 26/06/2020 AVO*/
    );
  execute procedure GM$PR_PRIHOD_DOC_TREB(:doc_id);
end^

SET TERM ; ^

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

GRANT SELECT ON DOCS TO PROCEDURE PR_DOC_RASHOD_COMMIT;
GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_DOC_RASHOD_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO PROCEDURE PR_DOC_RASHOD_COMMIT;
GRANT EXECUTE ON PROCEDURE GM$PR_PRIHOD_DOC_TREB TO PROCEDURE PR_DOC_RASHOD_COMMIT;

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

GRANT EXECUTE ON PROCEDURE PR_DOC_RASHOD_COMMIT TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_RASHOD_COMMIT TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_DOC_RASHOD_COMMIT TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_DOC_RASHOD_COMMIT TO SYSDBA;