Ошибки SQL-error's

при печати накладной на отправку почты выходит ошибка: "arithmetic exception, numeric overflow, or string truncation" ЧТО ДЕЛАТЬ? бэкап-ресторе не помог.

это косяк в БД. Процентов на 99 уверен что туда записалось какое-нибудь нереальное значение. Тут нужно либо его искать, либо передать запрос разработчикам, либо просто заменить БД. У нас такое уже было, это косяк WinPost.exe и ничего с этим не поделаешь.
Могу посоветовать посмотреть таблицу DISPATCH поля CODSUM.

Спасибо за ответ! Разработчики скрипт прислали завтра прогоню посмотрим

можете скопипастить сюда скрипт, у нас такая же проблема на одном опс вылезла.

1 скрипт:

update RDB$FIELDS set
RDB$FIELD_LENGTH = 15,
RDB$CHARACTER_LENGTH = 15
where RDB$FIELD_NAME in (select f.rdb$field_source from RDB$RELATION_FIELDS f
where f.RDB$RELATION_NAME = 'TMP_INVOICE_TOTAL' and f.RDB$FIELD_NAME = 'N');
commit;

2 скрипт:

SET TERM ^ ;

create or alter procedure exp_dispatch2rpo
returns (
export_file_id integer)
AS
declare variable OPERTYPE integer;
declare variable OPERDATE timestamp;
declare variable BARCODE varchar(15);
declare variable INDEXTO integer;
declare variable MAILDIRECT integer;
declare variable TRANSTYPE integer;
declare variable MAILTYPE integer;
declare variable MAILCTG integer;
declare variable MAILRANK integer;
declare variable SENDCTG integer;
declare variable POSTMARK integer;
declare variable MASS integer;
declare variable PAYMENT integer;
declare variable AVALUE integer;
declare variable PAYTYPE integer;
declare variable MASSRATE integer;
declare variable INSRRATE integer;
declare variable AIRRATE integer;
declare variable ADVALTAX integer;
declare variable SALETAX integer;
declare variable RATE integer;
declare variable OPERATTR integer;
declare variable INDEXOPER integer;
declare variable INDEXNEXT integer;
declare variable COMMENT varchar(500);
declare variable CS$ID integer;
declare variable M_VALUE_DTS double precision;
declare variable M_F103SIGN integer;
declare variable M_RETNREASONID integer;
declare variable M_SENDADDID integer;
declare variable M_RECEIVTYPEID integer;
declare variable M_SERVICEID integer;
declare variable M_DIRECTIONID integer;
declare variable M_OFFEXID integer;
declare variable M_SORTDIRID integer;
declare variable M_CUSTOMER varchar(70);
declare variable M_RECIPIENT varchar(70);
declare variable M_SENDERCOUNTRYCODE integer;
declare variable C_ACCEPT_OPID integer;
declare variable C_RETURN_OPID integer;
declare variable C_ISSUE_OPID integer;
declare variable C_SENDADD_OPID integer;
declare variable C_RUSSIA_NUM_CODE integer;
declare variable C_NDS_TID integer;
declare variable C_NSP_TID integer;
declare variable C_EMS_DOMESTIC_ID integer;
declare variable C_EMS_GOOD_ID integer;
declare variable C_EMS_DECL_VALUE_ID integer;
declare variable C_EMS_DOC_ID integer;
declare variable C_WINPOST_VER varchar(20);
declare variable C_WINPOST_VERSION_PREFIX varchar(10);
declare variable M_EXE_VERSION varchar(40);
declare variable M_MIN integer;
declare variable M_HOUR integer;
declare variable M_MIN_STR varchar(2);
declare variable M_HOUR_STR varchar(2);
declare variable C_PAYMENT_FORWARD integer;
declare variable M_SPECIALMARKID integer;
declare variable V_EQF_IS_RUB smallint;
declare variable V_CST_SUMMA double precision;
declare variable V_CST_TARIFF double precision;
declare variable V_NAME varchar(40);
declare variable V_SUM_TAX integer;
declare variable V_ID integer;
declare variable V_ID_STR varchar(10);
declare variable V_EXIST integer;
declare variable TARIFF_AIRRATE varchar(120);
declare variable TARIFF_INSRATE varchar(120);
declare variable TARIFF_MASSRATE varchar(320);
declare variable V_DISPATCHTYPEID integer;
declare variable V_SENDERINDEX integer;
declare variable V_OURINDEX integer;
declare variable V_COUNT integer;
declare variable V_ADDSERVICEDATAID integer;
declare variable V_ADDSERVICEDATA varchar(60);
declare variable V_CLIENTID integer;
declare variable V_ACNT varchar(20);
declare variable V_DATA timestamp;
declare variable V_BPI varchar(14);
declare variable V_POSTMARK integer;
BEGIN
v_addservicedataid = null;
select id from addservicedata adsd where adsd.text_for_window = 'SMS - уведомления' into :v_addservicedataid;

select count(*) from dispatch d
left join clientservice cs on cs.id = d.clientserviceid
where d.is_rpo_saved = 0 and d.export_rpo_dataid is null
and CS.SERVICETIME + 30 > CURRENT_TIMESTAMP
into :v_exist;
if (:v_exist = 0) then
begin
export_file_id = -1;
select count(*) from inprocessing d
where d.is_rpo_saved = 0
into :v_exist;
if (:v_exist = 0) then
begin
select count(*) from inprocessing_history d
where d.is_rpo_saved = 0
into :v_exist;
if (:v_exist = 0) then
begin
suspend;
exit;
end
end
end

/************************************ определение constant's **************************************/
C_ACCEPT_OPID = 1; /* прием */
C_ISSUE_OPID = 2; /* вручение */
C_RETURN_OPID = 3; /* возврат */
C_SENDADD_OPID = 4; /* досыл */

/* ID DispatchType для EMS */
C_EMS_DECL_VALUE_ID = 45;
C_EMS_DOMESTIC_ID = 44;
C_EMS_GOOD_ID = 43;
C_EMS_DOC_ID = 42;

C_NDS_TID = 1;
C_NSP_TID = 2;

C_PAYMENT_FORWARD = 112;
C_WINPOST_VERSION_PREFIX = 'WP_';
C_RUSSIA_NUM_CODE = 643;
select s.fieldvalue from setup s where upper(s.fieldname) = 'POSTOFFICEID' into :v_ourindex;

v_exist = 0;

/** выбираем номер версии программы WinPost **/
select F_LRTRIM(moduleversion)
from moduleversion where upper(modulename) = 'WINPOST.EXE' INTO :C_WINPOST_VER;

/** выбираем тарифы для авиа сборов(airrate), страховой сбор за ценность(insrate), доплаты связанные с массой(massrate) **/
select tariff_airrate, tariff_insrate, tariff_massrate from get_qft into :tariff_airrate, :tariff_insrate, :tariff_massrate;

/** формируем запись в таблицее export_rpo_registr **/
export_file_id = gen_id(gen_export_rpo_registr_id,1);
select FILE_NAME from EXP_GET_FILENAME into :v_name;
v_name = f_lrtrim(:v_name);
insert into export_rpo_registr (id, file_name) values( :export_file_id, :v_name);
execute procedure EXP_SET_FILENUMB;
/*** execute procedure exp_write_err; ***/

/***** выбираем исходные данные ******/
FOR SELECT
CS.ID, /* :M_CSID */
CS.SERVICEID, /* :M_SERVICEID */
DS.DIRECTIONID, /* :M_DIRECTIONID */
D.OFFEXID, /* :M_OFFEXID */
DS.DISPATCHOPERATIONID, /* :OPERTYPE */
CS.SERVICETIME, /* :OPERDATE */
D.N, /* :BARCODE */
D.POSTOFFICEID, /* :INDEXTO */
D.COUNTRYCODE, /* :MAILDIRECT */
DS.SENDINGTYPEID, /* :TRANSTYPE */
DT.rpo_dispatchtypeid, /* :MAILTYPE */
/* DT.DISPATCHCATEGORYID, :MAILCTG */
Dk.rpo_dispatchcategoryid, /*:MAILCTG */
dg.rpo_dispatchgradeid, /* :MAILRANK */
ct.rpo_clienttypeid, /* :SENDCTG */
D.WEIGHT, /* :MASS */
D.CODSUM, /* :PAYMENT */
D.AVALUE, /* :AVALUE */
D.AVALUEDTS, /* :M_VALUE_DTS */
pt.rpo_paytypeid, /* :PAYTYPE */
CST.TARIFF, /* :MASSRATE */
R.WINDOWID / 100, /* :INDEXOPER */
D.F103SIGN, /* :M_F103SIGN */
D.RETNREASONID, /* :M_RETNREASONID */
D.SENDADDID, /* :M_SENDADDID */
D.RECEIVERTYPEID, /* :M_RECEIVTYPEID */
D.SORTDIRECTIONID, /* :M_SORTDIRID */
S.CUSTOMER, /* :M_CUSTOMER */
D.RECIPIENT, /* :M_RECIPIENT */
R.EXE_VERSION, /* :M_EXE_VERSION */
D.SENDERCOUNTRYCODE, /* M_SENDERCOUNTRYCODE */
ds.dispatchtypeid, /* V_SENDERINDEX */
d.senderindex,
s.clientid,
d.bpi
FROM RELAY R
JOIN SESSION S ON R.ID = S.RELAYID
JOIN CLIENTSERVICE CS ON S.ID = CS.SESSIONID
JOIN DISPATCH D ON CS.ID = D.CLIENTSERVICEID and d.is_rpo_saved = 0
JOIN SERVICE SRV ON cs.serviceid = SRV.ID
JOIN DISPATCHSERVICE DS ON srv.id = DS.SERVICEID
JOIN DISPATCHTYPE DT ON DS.DISPATCHTYPEID = DT.ID
left join CLIENTSERVICETARIFF CST ON CST.CLIENTSERVICEID = d.clientserviceid
left join paytype PT ON pt.id = DS.PAYTYPEID
left join clienttype ct on ct.id = ds.clienttypeid
left join dispatchgrade dg on dg.id = d.dispatchgradeid
left join dispatchcategory dk on dt.dispatchcategoryid = dk.id
WHERE d.export_rpo_dataid is null
and CLIENTSERVICESTATEID = 0
INTO
:CS$ID,
:M_SERVICEID,
:M_DIRECTIONID,
:M_OFFEXID,
:OPERTYPE,
:OPERDATE,
:BARCODE,
:INDEXTO,
:MAILDIRECT,
:TRANSTYPE,
:MAILTYPE,
:MAILCTG,
:MAILRANK,
:SENDCTG,
:MASS,
:PAYMENT,
:AVALUE,
:M_VALUE_DTS,
:PAYTYPE,
:MASSRATE,
:INDEXOPER,
:M_F103SIGN,
:M_RETNREASONID,
:M_SENDADDID,
:M_RECEIVTYPEID,
:M_SORTDIRID,
:M_CUSTOMER,
:M_RECIPIENT,
:M_EXE_VERSION,
:M_SENDERCOUNTRYCODE,
:v_dispatchtypeid,
:v_senderindex,
:v_clientid,
:v_bpi
DO
BEGIN
if (:M_RECIPIENT is null) then M_RECIPIENT = ' ';
v_exist = 1;
IF (MAILRANK IS NULL) THEN MAILRANK = 0;
insrrate = 0;
rate = 0;
airrate = 0;

/***** обработка специфики, связанных с почтовыми отправлениями ******/
/* indexto = null; */
IF (OPERTYPE = C_ACCEPT_OPID) /* Прием почтовых отправлений */
THEN
BEGIN
IF (M_DIRECTIONID IN (2, 3)) THEN /* Международная */
BEGIN
AVALUE = M_VALUE_DTS;
SELECT POSTINDEX FROM OFFEX WHERE ID = :M_OFFEXID INTO :INDEXTO;
END
/* С объявленной ценностью и наложенным платежом */
IF (MAILCTG = 2 AND PAYMENT > 0) THEN MAILCTG = 4;
OPERATTR = M_F103SIGN + 1;
END
ELSE
/* Вручение почтовых отправлений */
IF (OPERTYPE = C_ISSUE_OPID) THEN
BEGIN
OPERATTR = M_RECEIVTYPEID;
/* С объявленной ценностью и наложенным платежом */
IF (MAILCTG = 2) THEN
FOR SELECT CSM.SPECIALMARKID FROM CLIENTSERVICEMARK CSM
WHERE CSM.CLIENTSERVICEID = :CS$ID
INTO M_SPECIALMARKID DO
IF (M_SPECIALMARKID = C_PAYMENT_FORWARD) THEN
BEGIN
MAILCTG = 4;
LEAVE;
END
END
ELSE
/* Возврат */
IF (OPERTYPE = C_RETURN_OPID) THEN
begin
OPERATTR = M_RETNREASONID; /* возврат */
if (:m_directionid > 1) then
SELECT POSTINDEX FROM OFFEX WHERE ID = :M_OFFEXID INTO :INDEXTO;
end
ELSE
/* Досыл */
begin
IF (OPERTYPE = C_SENDADD_OPID) THEN OPERATTR = M_SENDADDID; /* досыл */
if (:m_directionid > 1) then
SELECT POSTINDEX FROM OFFEX WHERE ID = :M_OFFEXID INTO :INDEXTO;
end

/* fill IndexTo */
/* Исправлено 16.08.2010 */
if (((:indexto is null) or (:indexto = 0)) and (M_DIRECTIONID IN (2, 3)) and
((:opertype = :C_ACCEPT_OPID) or (:opertype = :C_RETURN_OPID) or (:opertype = :C_SENDADD_OPID)
)) then indexto = 104000;

/***** Маска особых отметок POSTMARK *****/
SELECT sum(spm.rpo_specialmarkid) FROM clientservice cs
left join CLIENTSERVICEMARK CSM on cs.id = csm.clientserviceid
left join specialmark spm on spm.id = csm.specialmarkid
WHERE (not spm.rpo_specialmarkid is null) and
((CS.PARENTID = :CS$ID) or (cs.id = :CS$ID))
INTO :postmark;
if (:postmark is null) then POSTMARK = 0;
SELECT sum(spm.rpo_specialmarkid) FROM clientservice cs
left join servicemark sm on sm.serviceid = cs.serviceid
left join specialmark spm on spm.id = sm.specialmarkid and not exists
(select * from CLIENTSERVICEMARK csm where csm.clientserviceid = :CS$ID and csm.specialmarkid = spm.id)
WHERE (not spm.rpo_specialmarkid is null) and
(spm.isobligatory = 1) and ((CS.PARENTID = :CS$ID) or (cs.id = :CS$ID))
INTO :v_postmark;
if (not :v_postmark is null) then postmark = :postmark + :v_postmark;

/**** Налоги *****/
ADVALTAX = 0;
SALETAX = 0;
v_id = null;
SELECT SUMMA, TAXID FROM CLIENTSERVICETAX
WHERE CLIENTSERVICEID = :CS$ID
INTO :v_sum_tax,
:v_id;
IF (not :v_id IS NULL) THEN
if (:v_id = :c_nsp_tid) then
SALETAX = :v_sum_tax; /* с продаж */
else
ADVALTAX = :v_sum_tax; /* НДС */

/**** определение INDEXNEXT ***/
SELECT NEXTINDEX FROM SORTDIRECTION
WHERE ID = :M_SORTDIRID
INTO :INDEXNEXT;
IF (INDEXNEXT IS NULL) THEN INDEXNEXT = 0;

/**** специфика PAYTYPE ****/
IF (MAILRANK = 1) THEN PAYTYPE = -1; /* Служебное или правительственное */
ELSE
IF (EXISTS (SELECT PS.PAYSERVICETYPEID FROM PAYSERVICE PS
JOIN CLIENTSERVICE CS ON CS.SERVICEID = PS.SERVICEID AND
CS.ID = :CS$ID AND PS.ADDSERVICE = 1 and ps.payservicetypeid < 3))
THEN PAYTYPE = 16;
if ((:mailrank = 3) and (:M_DIRECTIONID = 1)) then /* 04.02.2010 служебное и заказное */
PAYTYPE = 4;

COMMENT = C_WINPOST_VERSION_PREFIX || M_EXE_VERSION;

/* специфика формирования коментария для EMS */
If (MAILTYPE = 7) THEN
BEGIN
M_HOUR = EXTRACT(HOUR FROM OPERDATE);
M_MIN = EXTRACT(MINUTE FROM OPERDATE);

M_HOUR_STR = M_HOUR;
M_MIN_STR = M_MIN;

IF (M_HOUR < 10) THEN M_HOUR_STR = '0' || M_HOUR_STR;
IF (M_MIN < 10) THEN M_MIN_STR = '0' || M_MIN_STR;

COMMENT = COMMENT || ';' ||
'time=' || M_HOUR_STR || ':' || M_MIN_STR || ';';

if (opertype = C_ACCEPT_OPID) then
begin
if (not :indexoper is null) then
COMMENT = COMMENT || 'IndexFrom=' || INDEXOPER;
if (not :M_CUSTOMER is null) then
COMMENT = COMMENT || ';Sndr=' || F_MID(M_CUSTOMER,0,60);
COMMENT = COMMENT || ';Rcpn=' || F_MID(M_RECIPIENT,0,60);
end
else if (opertype = C_ISSUE_OPID) then
begin
if (not :v_senderindex is null) then
COMMENT = COMMENT||'IndexFrom='||:v_senderindex;
end

if ((:sendctg > 1) and (:v_clientid > 0)) then
begin
v_data = null;
select max(a.agreement_date) from agreement a
where a.clientid = :v_clientid
into :v_data;
if (not :v_data is null) then
begin
select a.agreement_number from agreement a
where a.clientid = :v_clientid and a.agreement_date = :v_data
into :v_acnt;
if (not :v_acnt is null) then
COMMENT = :COMMENT || ';Acnt=' ||f_lrtrim(:v_acnt);
end
end
END

IF ((OPERTYPE = C_ISSUE_OPID) AND (not :m_recipient is null)) then
COMMENT = COMMENT||';rcpn='||F_MID(M_RECIPIENT,0,60);

IF (OPERTYPE = C_RETURN_OPID) then
if (NOT M_SENDERCOUNTRYCODE IS NULL) THEN
COMMENT = COMMENT || ';countryfrom=' || M_SENDERCOUNTRYCODE;
else
COMMENT = COMMENT || ';countryfrom=643';

/******* специфика MAILDIRECT ********/
/* Вручение, международная */
IF ((OPERTYPE = C_ISSUE_OPID) AND (M_DIRECTIONID IN (2, 3))) THEN
/* Адресат */
IF (M_RECEIVTYPEID = 1)
THEN
BEGIN
IF (M_SENDERCOUNTRYCODE IS NOT NULL) THEN
COMMENT = COMMENT || ';countryfrom=' || CAST(M_SENDERCOUNTRYCODE AS VARCHAR (10));
/* Россия */
MAILDIRECT = C_RUSSIA_NUM_CODE;
END
/* Отправитель */
ELSE
IF (M_RECEIVTYPEID = 2)
THEN
BEGIN
/* Россия */
IF (C_RUSSIA_NUM_CODE IS NOT NULL) THEN
COMMENT = COMMENT || ';countryfrom=' || CAST(C_RUSSIA_NUM_CODE AS VARCHAR (10));
MAILDIRECT = M_SENDERCOUNTRYCODE;
END

/* 19.11.2010 */
if (indexto is null) then
if ((opertype = 2) and (M_DIRECTIONID = 1)) then indexto = :V_OURINDEX;
IF (INDEXTO IS NULL) THEN INDEXTO = 0;

/******** расчет агрегированных реквизитов *********/
if (:opertype < 3) then
begin
for select CST.tariff, CS.SUMMA, st.tariffid from CLIENTSERVICE CS
left outer join clientservicetariff CST on CST.CLIENTSERVICEID = CS.ID
left join servicetariff st on st.serviceid = cs.serviceid
where (CS.PARENTID = :cs$id) and (not CST.tariff is null)
into :v_cst_tariff,
:v_cst_summa,
:v_id
do
begin
v_id_str = :v_id;
v_id_str = f_lrtrim(:v_id_str);
if (:opertype = 1) then
begin
if (F_SUBSTR(:tariff_insrate, :v_id_str) >= 0) then insrrate = :insrrate + :v_cst_tariff;
else
if ((F_SUBSTR(:tariff_airrate, :v_id_str) >= 0) or (:TRANSTYPE = 2)) then airrate = :airrate + :v_cst_tariff;
else
if (F_SUBSTR(:tariff_massrate, :v_id_str) >= 0) then massrate = :massrate + :v_cst_tariff;
else rate = :rate + :v_cst_tariff;
end
else
if (:opertype = 2) then rate = :rate + :v_cst_tariff;
end
end

/* специфика для Уведомлений о вручении */
if (:opertype = 13) then
begin
if (not :barcode is null) then
comment = :comment||';BarcodeLinkedMail='||:barcode;
barcode = :v_bpi;
end

/* вычисление не стандартных ситуаций, то что было заложено в поле Script таблицы ExportQuery */
if ((PAYTYPE = 3) and (TRANSTYPE = 2)) then PAYTYPE = 2;
if (PAYTYPE < 0) then PAYTYPE = 4;
MASSRATE = :MASSRATE + :AIRRATE; /* 09.02.2010 */
If (MAILTYPE = 12) then MAILCTG = 1; /* уведомление */
if (:paytype = 4) then /* 04.02.2010 */
begin
massrate = 0;
rate = 0;
end
if (:opertype = :C_RETURN_OPID) then /* 12.02.2010 */
begin
massrate = 0;
rate = 0;
end

/**** 17.02.2010 по замечаниям ЦАИТС ****/
if (:opertype = :C_SENDADD_OPID) then
massrate = 0;
if ((:mailrank = 3) and (:avalue > 0)) then
begin
insrrate = 0;
airrate = 0;
end

/* For Sekogramma 12.04.2010 */
if (:m_directionid = 1 and :v_dispatchtypeid = 54) then paytype = 4;

/* специфика SMS уведомлений, в программе жестко заложено, что данный коментарий должен быть ПОСЛЕДНИМ */
if (not :v_addservicedataid is null) then
if (exists (select * from clientserviceadddata csad
where csad.clientserviceid in (select id from clientservice cs2 where cs2.parentid = :cs$id) and
csad.addservicedataid = :v_addservicedataid)) then
comment = :comment||';SmsInfo='||:cs$id;

/**** вставка данных в таблицу EXPORT_RPO_DATA ****/
v_id = gen_id(GEN_EXPORT_RPO_DATA_ID,1);
insert into export_rpo_data (id, export_rpo_registrid, opertype, operdate, barcode, indexto,
maildirect, transtype, mailtype, mailctg, mailrank, sendctg,
postmark, mass, payment, avalue, paytype, massrate, insrrate,
airrate, advaltax, saletax, rate, operattr, indexoper,
indexnext, comment)
values( :v_id, :export_file_id, :opertype, :operdate, :barcode, :indexto, :maildirect, :transtype,
:mailtype, :mailctg, :mailrank, :sendctg, :postmark, :mass, :payment, :avalue,
:paytype, :massrate, :insrrate, :airrate, :advaltax, :saletax, :rate, :operattr,
:indexoper, :indexnext, :comment);

/*** обновление в таблице Dispatch ***/
update dispatch set export_rpo_dataid = :v_id, IS_RPO_SAVED = 1 where clientserviceid = :cs$id;

INDEXTO = NULL;
INDEXNEXT = NULL;
POSTMARK = NULL;
OPERATTR = NULL;
M_EXE_VERSION = NULL;
END
SUSPEND;
execute procedure EXPORT_INPROCESSING_NEW(:export_file_id);

END^

SET TERM ; ^

GRANT SELECT ON ADDSERVICEDATA TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT,UPDATE ON DISPATCH TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON CLIENTSERVICE TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON INPROCESSING TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON INPROCESSING_HISTORY TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON SETUP TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON MODULEVERSION TO PROCEDURE EXP_DISPATCH2RPO;

GRANT EXECUTE ON PROCEDURE GET_QFT TO PROCEDURE EXP_DISPATCH2RPO;

GRANT EXECUTE ON PROCEDURE EXP_GET_FILENAME TO PROCEDURE EXP_DISPATCH2RPO;

GRANT INSERT ON EXPORT_RPO_REGISTR TO PROCEDURE EXP_DISPATCH2RPO;

GRANT EXECUTE ON PROCEDURE EXP_SET_FILENUMB TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON RELAY TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON SESSION TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON SERVICE TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON DISPATCHSERVICE TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON DISPATCHTYPE TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON CLIENTSERVICETARIFF TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON PAYTYPE TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON CLIENTTYPE TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON DISPATCHGRADE TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON DISPATCHCATEGORY TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON OFFEX TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON CLIENTSERVICEMARK TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON SPECIALMARK TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON SERVICEMARK TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON CLIENTSERVICETAX TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON SORTDIRECTION TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON PAYSERVICE TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON AGREEMENT TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON SERVICETARIFF TO PROCEDURE EXP_DISPATCH2RPO;

GRANT SELECT ON CLIENTSERVICEADDDATA TO PROCEDURE EXP_DISPATCH2RPO;

GRANT INSERT ON EXPORT_RPO_DATA TO PROCEDURE EXP_DISPATCH2RPO;

GRANT EXECUTE ON PROCEDURE EXPORT_INPROCESSING_NEW TO PROCEDURE EXP_DISPATCH2RPO;

GRANT EXECUTE ON PROCEDURE EXP_DISPATCH2RPO TO SYSDBA;

3 скрипт:

SET TERM ^ ;
create or alter procedure GET_QFT
returns (
TARIFF_AIRRATE varchar(240),
TARIFF_INSRATE varchar(240),
TARIFF_MASSRATE varchar(420))
as
declare variable TARIFF_ID integer;
begin
tariff_airrate = '';
tariff_insrate = '';
tariff_massrate = '';

/*AirRate*/
for select id from tariff where (upper(formula) like '%AVIA%')
or (upper(formula) like '%АВИА%')
into :tariff_id
do tariff_airrate = :tariff_airrate||:tariff_id||';';

/*INSRRATE*/
for select id from tariff where upper(name) like '%ЦЕНН%' or
name = 'Письмо и бандероль 1 класса - страховой сбор' into :tariff_id
do tariff_insrate = :tariff_insrate||:tariff_id||';';

/*MASSRATE*/
for select id from tariff where
upper(name) like '%ТЯЖЕЛОВЕСНАЯ%' or
upper(name) like '%ОСТОРОЖНО%' or
upper(name) like '%НЕСТАНДАРТНАЯ%' or
upper(name) like '%КРУПНОГАБАРИТНАЯ%' or
upper(name) like '%ПЕРЕГРУЗКИ%' or
upper(name) like '%НАЗЕМН%' or
upper(name) like '%РУЧНОЙ ВВОД%'
into :tariff_id
do tariff_massrate = :tariff_massrate||:tariff_id||';';
tariff_massrate = :tariff_massrate||'10160;10750;700006;700007;700008;10161;11950;10402;';
suspend;
end^

create or alter procedure EXP_DISPATCH2RPO
returns (
EXPORT_FILE_ID integer)
as
declare variable OPERTYPE integer;
declare variable OPERDATE timestamp;
declare variable BARCODE varchar(15);
declare variable INDEXTO integer;
declare variable MAILDIRECT integer;
declare variable TRANSTYPE integer;
declare variable MAILTYPE integer;
declare variable MAILCTG integer;
declare variable MAILRANK integer;
declare variable SENDCTG integer;
declare variable POSTMARK integer;
declare variable MASS integer;
declare variable PAYMENT integer;
declare variable AVALUE integer;
declare variable PAYTYPE integer;
declare variable MASSRATE integer;
declare variable INSRRATE integer;
declare variable AIRRATE integer;
declare variable ADVALTAX integer;
declare variable SALETAX integer;
declare variable RATE integer;
declare variable OPERATTR integer;
declare variable INDEXOPER integer;
declare variable INDEXNEXT integer;
declare variable COMMENT varchar(500);
declare variable CS$ID integer;
declare variable M_VALUE_DTS double precision;
declare variable M_F103SIGN integer;
declare variable M_RETNREASONID integer;
declare variable M_SENDADDID integer;
declare variable M_RECEIVTYPEID integer;
declare variable M_SERVICEID integer;
declare variable M_DIRECTIONID integer;
declare variable M_OFFEXID integer;
declare variable M_SORTDIRID integer;
declare variable M_CUSTOMER varchar(70);
declare variable M_RECIPIENT varchar(70);
declare variable M_SENDERCOUNTRYCODE integer;
declare variable C_ACCEPT_OPID integer;
declare variable C_RETURN_OPID integer;
declare variable C_ISSUE_OPID integer;
declare variable C_SENDADD_OPID integer;
declare variable C_RUSSIA_NUM_CODE integer;
declare variable C_NDS_TID integer;
declare variable C_NSP_TID integer;
declare variable C_EMS_DOMESTIC_ID integer;
declare variable C_EMS_GOOD_ID integer;
declare variable C_EMS_DECL_VALUE_ID integer;
declare variable C_EMS_DOC_ID integer;
declare variable C_WINPOST_VER varchar(20);
declare variable C_WINPOST_VERSION_PREFIX varchar(10);
declare variable M_EXE_VERSION varchar(40);
declare variable M_MIN integer;
declare variable M_HOUR integer;
declare variable M_MIN_STR varchar(2);
declare variable M_HOUR_STR varchar(2);
declare variable C_PAYMENT_FORWARD integer;
declare variable M_SPECIALMARKID integer;
declare variable V_EQF_IS_RUB smallint;
declare variable V_CST_SUMMA double precision;
declare variable V_CST_TARIFF double precision;
declare variable V_NAME varchar(40);
declare variable V_SUM_TAX integer;
declare variable V_ID integer;
declare variable V_ID_STR varchar(10);
declare variable V_EXIST integer;
declare variable TARIFF_AIRRATE varchar(240);
declare variable TARIFF_INSRATE varchar(240);
declare variable TARIFF_MASSRATE varchar(420);
declare variable V_DISPATCHTYPEID integer;
declare variable V_SENDERINDEX integer;
declare variable V_OURINDEX integer;
declare variable V_COUNT integer;
declare variable V_ADDSERVICEDATAID integer;
declare variable V_ADDSERVICEDATA varchar(60);
declare variable V_CLIENTID integer;
declare variable V_ACNT varchar(20);
declare variable V_DATA timestamp;
declare variable V_BPI varchar(15);
declare variable V_POSTMARK integer;
BEGIN
v_addservicedataid = null;
select id from addservicedata adsd where adsd.text_for_window = 'SMS - уведомления' into :v_addservicedataid;

select count(*) from dispatch d
left join clientservice cs on cs.id = d.clientserviceid
where d.is_rpo_saved = 0 and d.export_rpo_dataid is null
and CS.SERVICETIME + 30 > CURRENT_TIMESTAMP
into :v_exist;
if (:v_exist = 0) then
begin
export_file_id = -1;
select count(*) from inprocessing d
where d.is_rpo_saved = 0
into :v_exist;
if (:v_exist = 0) then
begin
select count(*) from inprocessing_history d
where d.is_rpo_saved = 0
into :v_exist;
if (:v_exist = 0) then
begin
suspend;
exit;
end
end
end

/************************************ определение constant's **************************************/
C_ACCEPT_OPID = 1; /* прием */
C_ISSUE_OPID = 2; /* вручение */
C_RETURN_OPID = 3; /* возврат */
C_SENDADD_OPID = 4; /* досыл */

/* ID DispatchType для EMS */
C_EMS_DECL_VALUE_ID = 45;
C_EMS_DOMESTIC_ID = 44;
C_EMS_GOOD_ID = 43;
C_EMS_DOC_ID = 42;

C_NDS_TID = 1;
C_NSP_TID = 2;

C_PAYMENT_FORWARD = 112;
C_WINPOST_VERSION_PREFIX = 'WP_';
C_RUSSIA_NUM_CODE = 643;
select s.fieldvalue from setup s where upper(s.fieldname) = 'POSTOFFICEID' into :v_ourindex;

v_exist = 0;

/** выбираем номер версии программы WinPost **/
select F_LRTRIM(moduleversion)
from moduleversion where upper(modulename) = 'WINPOST.EXE' INTO :C_WINPOST_VER;

/** выбираем тарифы для авиа сборов(airrate), страховой сбор за ценность(insrate), доплаты связанные с массой(massrate) **/
select tariff_airrate, tariff_insrate, tariff_massrate from get_qft into :tariff_airrate, :tariff_insrate, :tariff_massrate;

/** формируем запись в таблицее export_rpo_registr **/
export_file_id = gen_id(gen_export_rpo_registr_id,1);
select FILE_NAME from EXP_GET_FILENAME into :v_name;
v_name = f_lrtrim(:v_name);
insert into export_rpo_registr (id, file_name) values( :export_file_id, :v_name);
execute procedure EXP_SET_FILENUMB;
/*** execute procedure exp_write_err; ***/

/***** выбираем исходные данные ******/
FOR SELECT
CS.ID, /* :M_CSID */
CS.SERVICEID, /* :M_SERVICEID */
DS.DIRECTIONID, /* :M_DIRECTIONID */
D.OFFEXID, /* :M_OFFEXID */
DS.DISPATCHOPERATIONID, /* :OPERTYPE */
CS.SERVICETIME, /* :OPERDATE */
D.N, /* :BARCODE */
D.POSTOFFICEID, /* :INDEXTO */
D.COUNTRYCODE, /* :MAILDIRECT */
DS.SENDINGTYPEID, /* :TRANSTYPE */
DT.rpo_dispatchtypeid, /* :MAILTYPE */
/* DT.DISPATCHCATEGORYID, :MAILCTG */
Dk.rpo_dispatchcategoryid, /*:MAILCTG */
dg.rpo_dispatchgradeid, /* :MAILRANK */
ct.rpo_clienttypeid, /* :SENDCTG */
D.WEIGHT, /* :MASS */
D.CODSUM, /* :PAYMENT */
D.AVALUE, /* :AVALUE */
D.AVALUEDTS, /* :M_VALUE_DTS */
pt.rpo_paytypeid, /* :PAYTYPE */
CST.TARIFF, /* :MASSRATE */
R.WINDOWID / 100, /* :INDEXOPER */
D.F103SIGN, /* :M_F103SIGN */
D.RETNREASONID, /* :M_RETNREASONID */
D.SENDADDID, /* :M_SENDADDID */
D.RECEIVERTYPEID, /* :M_RECEIVTYPEID */
D.SORTDIRECTIONID, /* :M_SORTDIRID */
S.CUSTOMER, /* :M_CUSTOMER */
D.RECIPIENT, /* :M_RECIPIENT */
R.EXE_VERSION, /* :M_EXE_VERSION */
D.SENDERCOUNTRYCODE, /* M_SENDERCOUNTRYCODE */
ds.dispatchtypeid, /* V_SENDERINDEX */
d.senderindex,
s.clientid,
d.bpi
FROM RELAY R
JOIN SESSION S ON R.ID = S.RELAYID
JOIN CLIENTSERVICE CS ON S.ID = CS.SESSIONID
JOIN DISPATCH D ON CS.ID = D.CLIENTSERVICEID and d.is_rpo_saved = 0
JOIN SERVICE SRV ON cs.serviceid = SRV.ID
JOIN DISPATCHSERVICE DS ON srv.id = DS.SERVICEID
JOIN DISPATCHTYPE DT ON DS.DISPATCHTYPEID = DT.ID
left join CLIENTSERVICETARIFF CST ON CST.CLIENTSERVICEID = d.clientserviceid
left join paytype PT ON pt.id = DS.PAYTYPEID
left join clienttype ct on ct.id = ds.clienttypeid
left join dispatchgrade dg on dg.id = d.dispatchgradeid
left join dispatchcategory dk on dt.dispatchcategoryid = dk.id
WHERE d.export_rpo_dataid is null
and CLIENTSERVICESTATEID = 0
INTO
:CS$ID,
:M_SERVICEID,
:M_DIRECTIONID,
:M_OFFEXID,
:OPERTYPE,
:OPERDATE,
:BARCODE,
:INDEXTO,
:MAILDIRECT,
:TRANSTYPE,
:MAILTYPE,
:MAILCTG,
:MAILRANK,
:SENDCTG,
:MASS,
:PAYMENT,
:AVALUE,
:M_VALUE_DTS,
:PAYTYPE,
:MASSRATE,
:INDEXOPER,
:M_F103SIGN,
:M_RETNREASONID,
:M_SENDADDID,
:M_RECEIVTYPEID,
:M_SORTDIRID,
:M_CUSTOMER,
:M_RECIPIENT,
:M_EXE_VERSION,
:M_SENDERCOUNTRYCODE,
:v_dispatchtypeid,
:v_senderindex,
:v_clientid,
:v_bpi
DO
BEGIN
if (:M_RECIPIENT is null) then M_RECIPIENT = ' ';
v_exist = 1;
IF (MAILRANK IS NULL) THEN MAILRANK = 0;
insrrate = 0;
rate = 0;
airrate = 0;

/***** обработка специфики, связанных с почтовыми отправлениями ******/
/* indexto = null; */
IF (OPERTYPE = C_ACCEPT_OPID) /* Прием почтовых отправлений */
THEN
BEGIN
IF (M_DIRECTIONID IN (2, 3)) THEN /* Международная */
BEGIN
AVALUE = M_VALUE_DTS;
SELECT POSTINDEX FROM OFFEX WHERE ID = :M_OFFEXID INTO :INDEXTO;
END
/* С объявленной ценностью и наложенным платежом */
IF (MAILCTG = 2 AND PAYMENT > 0) THEN MAILCTG = 4;
OPERATTR = M_F103SIGN + 1;
END
ELSE
/* Вручение почтовых отправлений */
IF (OPERTYPE = C_ISSUE_OPID) THEN
BEGIN
OPERATTR = M_RECEIVTYPEID;
/* С объявленной ценностью и наложенным платежом */
IF (MAILCTG = 2) THEN
FOR SELECT CSM.SPECIALMARKID FROM CLIENTSERVICEMARK CSM
WHERE CSM.CLIENTSERVICEID = :CS$ID
INTO M_SPECIALMARKID DO
IF (M_SPECIALMARKID = C_PAYMENT_FORWARD) THEN
BEGIN
MAILCTG = 4;
LEAVE;
END
END
ELSE
/* Возврат */
IF (OPERTYPE = C_RETURN_OPID) THEN
begin
OPERATTR = M_RETNREASONID; /* возврат */
if (:m_directionid > 1) then
SELECT POSTINDEX FROM OFFEX WHERE ID = :M_OFFEXID INTO :INDEXTO;
end
ELSE
/* Досыл */
begin
IF (OPERTYPE = C_SENDADD_OPID) THEN OPERATTR = M_SENDADDID; /* досыл */
if (:m_directionid > 1) then
SELECT POSTINDEX FROM OFFEX WHERE ID = :M_OFFEXID INTO :INDEXTO;
end

/* fill IndexTo */
/* Исправлено 16.08.2010 */
if (((:indexto is null) or (:indexto = 0)) and (M_DIRECTIONID IN (2, 3)) and
((:opertype = :C_ACCEPT_OPID) or (:opertype = :C_RETURN_OPID) or (:opertype = :C_SENDADD_OPID)
)) then indexto = 104000;

/***** Маска особых отметок POSTMARK *****/
SELECT sum(spm.rpo_specialmarkid) FROM clientservice cs
left join CLIENTSERVICEMARK CSM on cs.id = csm.clientserviceid
left join specialmark spm on spm.id = csm.specialmarkid
WHERE (not spm.rpo_specialmarkid is null) and
((CS.PARENTID = :CS$ID) or (cs.id = :CS$ID))
INTO :postmark;
if (:postmark is null) then POSTMARK = 0;
SELECT sum(spm.rpo_specialmarkid) FROM clientservice cs
left join servicemark sm on sm.serviceid = cs.serviceid
left join specialmark spm on spm.id = sm.specialmarkid and not exists
(select * from CLIENTSERVICEMARK csm where csm.clientserviceid = :CS$ID and csm.specialmarkid = spm.id)
WHERE (not spm.rpo_specialmarkid is null) and
(spm.isobligatory = 1) and ((CS.PARENTID = :CS$ID) or (cs.id = :CS$ID))
INTO :v_postmark;
if (not :v_postmark is null) then postmark = :postmark + :v_postmark;

/**** Налоги *****/
ADVALTAX = 0;
SALETAX = 0;
v_id = null;
SELECT SUMMA, TAXID FROM CLIENTSERVICETAX
WHERE CLIENTSERVICEID = :CS$ID
INTO :v_sum_tax,
:v_id;
IF (not :v_id IS NULL) THEN
if (:v_id = :c_nsp_tid) then
SALETAX = :v_sum_tax; /* с продаж */
else
ADVALTAX = :v_sum_tax; /* НДС */

/**** определение INDEXNEXT ***/
SELECT NEXTINDEX FROM SORTDIRECTION
WHERE ID = :M_SORTDIRID
INTO :INDEXNEXT;
IF (INDEXNEXT IS NULL) THEN INDEXNEXT = 0;

/**** специфика PAYTYPE ****/
IF (MAILRANK = 1) THEN PAYTYPE = -1; /* Служебное или правительственное */
ELSE
IF (EXISTS (SELECT PS.PAYSERVICETYPEID FROM PAYSERVICE PS
JOIN CLIENTSERVICE CS ON CS.SERVICEID = PS.SERVICEID AND
CS.ID = :CS$ID AND PS.ADDSERVICE = 1 and ps.payservicetypeid < 3))
THEN PAYTYPE = 16;
if ((:mailrank = 3) and (:M_DIRECTIONID = 1)) then /* 04.02.2010 служебное и заказное */
PAYTYPE = 4;

COMMENT = C_WINPOST_VERSION_PREFIX || :M_EXE_VERSION;

/* специфика формирования коментария для EMS */
If (MAILTYPE = 7) THEN
BEGIN
M_HOUR = EXTRACT(HOUR FROM OPERDATE);
M_MIN = EXTRACT(MINUTE FROM OPERDATE);

M_HOUR_STR = M_HOUR;
M_MIN_STR = M_MIN;

IF (M_HOUR < 10) THEN M_HOUR_STR = '0' || M_HOUR_STR;
IF (M_MIN < 10) THEN M_MIN_STR = '0' || M_MIN_STR;

COMMENT = COMMENT || ';' ||
'time=' || M_HOUR_STR || ':' || M_MIN_STR || ';';

if (opertype = C_ACCEPT_OPID) then
begin
if (not :indexoper is null) then
COMMENT = COMMENT || 'IndexFrom=' || INDEXOPER;
if (not :M_CUSTOMER is null) then
COMMENT = COMMENT || ';Sndr=' || F_MID(M_CUSTOMER,0,60);
COMMENT = COMMENT || ';Rcpn=' || F_MID(M_RECIPIENT,0,60);
end
else if (opertype = C_ISSUE_OPID) then
begin
if (not :v_senderindex is null) then
COMMENT = COMMENT||'IndexFrom='||:v_senderindex;
end

if ((:sendctg > 1) and (:v_clientid > 0)) then
begin
v_data = null;
select max(a.agreement_date) from agreement a
where a.clientid = :v_clientid
into :v_data;
if (not :v_data is null) then
begin
select a.agreement_number from agreement a
where a.clientid = :v_clientid and a.agreement_date = :v_data
into :v_acnt;
if (not :v_acnt is null) then
COMMENT = :COMMENT || ';Acnt=' ||f_lrtrim(:v_acnt);
end
end
END

IF ((OPERTYPE = C_ISSUE_OPID) AND (not :m_recipient is null)) then
COMMENT = COMMENT||';rcpn='||F_MID(M_RECIPIENT,0,60);

IF (OPERTYPE = C_RETURN_OPID) then
if (NOT M_SENDERCOUNTRYCODE IS NULL) THEN
COMMENT = COMMENT || ';countryfrom=' || M_SENDERCOUNTRYCODE;
else
COMMENT = COMMENT || ';countryfrom=643';

/******* специфика MAILDIRECT ********/
/* Вручение, международная */
IF ((OPERTYPE = C_ISSUE_OPID) AND (M_DIRECTIONID IN (2, 3))) THEN
/* Адресат */
IF (M_RECEIVTYPEID = 1)
THEN
BEGIN
IF (M_SENDERCOUNTRYCODE IS NOT NULL) THEN
COMMENT = COMMENT || ';countryfrom=' || CAST(M_SENDERCOUNTRYCODE AS VARCHAR (10));
/* Россия */
MAILDIRECT = C_RUSSIA_NUM_CODE;
END
/* Отправитель */
ELSE
IF (M_RECEIVTYPEID = 2)
THEN
BEGIN
/* Россия */
IF (C_RUSSIA_NUM_CODE IS NOT NULL) THEN
COMMENT = COMMENT || ';countryfrom=' || CAST(C_RUSSIA_NUM_CODE AS VARCHAR (10));
MAILDIRECT = M_SENDERCOUNTRYCODE;
END

/* 19.11.2010 */
if (indexto is null) then
if ((opertype = 2) and (M_DIRECTIONID = 1)) then indexto = :V_OURINDEX;
IF (INDEXTO IS NULL) THEN INDEXTO = 0;

/******** расчет агрегированных реквизитов *********/
if (:opertype < 3) then
begin
for select CST.tariff, CS.SUMMA, st.tariffid from CLIENTSERVICE CS
left outer join clientservicetariff CST on CST.CLIENTSERVICEID = CS.ID
left join servicetariff st on st.serviceid = cs.serviceid
where (CS.PARENTID = :cs$id) and (not CST.tariff is null)
into :v_cst_tariff,
:v_cst_summa,
:v_id
do
begin
v_id_str = :v_id;
v_id_str = f_lrtrim(:v_id_str);
if (:opertype = 1) then
begin
if (F_SUBSTR(:tariff_insrate, :v_id_str) >= 0) then insrrate = :insrrate + :v_cst_tariff;
else
if ((F_SUBSTR(:tariff_airrate, :v_id_str) >= 0) or (:TRANSTYPE = 2)) then airrate = :airrate + :v_cst_tariff;
else
if (F_SUBSTR(:tariff_massrate, :v_id_str) >= 0) then massrate = :massrate + :v_cst_tariff;
else rate = :rate + :v_cst_tariff;
end
else
if (:opertype = 2) then rate = :rate + :v_cst_tariff;
end
end

/* специфика для Уведомлений о вручении */
if (:opertype = 13) then comment = :comment||';BarcodeLinkedMail='||:v_bpi;

/* вычисление не стандартных ситуаций, то что было заложено в поле Script таблицы ExportQuery */
if ((PAYTYPE = 3) and (TRANSTYPE = 2)) then PAYTYPE = 2;
if (PAYTYPE < 0) then PAYTYPE = 4;
MASSRATE = :MASSRATE + :AIRRATE; /* 09.02.2010 */
If (MAILTYPE = 12) then MAILCTG = 1; /* уведомление */
if (:paytype = 4) then /* 04.02.2010 */
begin
massrate = 0;
rate = 0;
end
if (:opertype = :C_RETURN_OPID) then /* 12.02.2010 */
begin
massrate = 0;
rate = 0;
end

/**** 17.02.2010 по замечаниям ЦАИТС ****/
if (:opertype = :C_SENDADD_OPID) then
massrate = 0;
if ((:mailrank = 3) and (:avalue > 0)) then
begin
insrrate = 0;
airrate = 0;
end

/* For Sekogramma 12.04.2010 */
if (:m_directionid = 1 and :v_dispatchtypeid = 54) then paytype = 4;

/* специфика SMS уведомлений, в программе жестко заложено, что данный коментарий должен быть ПОСЛЕДНИМ */
if (not :v_addservicedataid is null) then
if (exists (select * from clientserviceadddata csad
where csad.clientserviceid in (select id from clientservice cs2 where cs2.parentid = :cs$id) and
csad.addservicedataid = :v_addservicedataid)) then
comment = :comment||';SmsInfo='||:cs$id;

/**** вставка данных в таблицу EXPORT_RPO_DATA ****/
v_id = gen_id(GEN_EXPORT_RPO_DATA_ID,1);
insert into export_rpo_data (id, export_rpo_registrid, opertype, operdate, barcode, indexto,
maildirect, transtype, mailtype, mailctg, mailrank, sendctg,
postmark, mass, payment, avalue, paytype, massrate, insrrate,
airrate, advaltax, saletax, rate, operattr, indexoper,
indexnext, comment)
values( :v_id, :export_file_id, :opertype, :operdate, :barcode, :indexto, :maildirect, :transtype,
:mailtype, :mailctg, :mailrank, :sendctg, :postmark, :mass, :payment, :avalue,
:paytype, :massrate, :insrrate, :airrate, :advaltax, :saletax, :rate, :operattr,
:indexoper, :indexnext, :comment);

/*** обновление в таблице Dispatch ***/
update dispatch set export_rpo_dataid = :v_id, IS_RPO_SAVED = 1 where clientserviceid = :cs$id;

INDEXTO = NULL;
INDEXNEXT = NULL;
POSTMARK = NULL;
OPERATTR = NULL;
M_EXE_VERSION = NULL;
END
SUSPEND;
execute procedure EXPORT_INPROCESSING_NEW(:export_file_id);

END^

SET TERM ; ^

Commit work;

что-то мне подсказывает что 99% кода здесь лишние =)

Спасибо за скрипты! Помогло!