/*ALIAS>prop_db*/

/*DLLStart>1550*/
DLLName=upd115.dll    
DLLProc=mvsmet
/*DLLStop>1550*/

/*SQLStart>1551*/
alter table payment add p_tmpnum smallint;
/*SQLStop>1551*/

/*SQLStart>1552*/
create procedure filltmpnum
as
declare variable p_id integer;
declare variable p_num smallint;
declare variable doc_id integer;
begin
  for select distinct doc_id from documents inner join Payment on (doc_id = p_id) where (p_status = 2)
      into :doc_id
  do begin
    for select p_id, p_num from Payment where p_id = :doc_id
        into :p_id, :p_num
    do begin
      update payment set p_tmpnum = :p_num where p_id = :p_id and p_num = :p_num;
      update payment set p_num = :p_num + 10000 where p_id = :p_id and p_num = :p_num and p_num <> 0;
      update paydetails set pd_num = :p_num + 10000 where pd_id = :p_id and pd_num = :p_num and pd_num <> 0;
    end
  end
end;
/*SQLStop>1552*/

/*SQLStart>1553*/
execute procedure filltmpnum;
/*SQLStop>1553*/

/*SQLStart>1554*/
drop procedure filltmpnum;
/*SQLStop>1554*/

/*SQLStart>1555*/
create procedure sortpaymenttable
as
declare variable p_bignum smallint;
declare variable p_tmpnum smallint;
declare variable P_status smallint;
declare variable p_masternum smallint;
declare variable p_newmasternum smallint;
declare variable cnt integer;
declare variable doc_id integer;
begin
  for select distinct doc_id from documents inner join Payment on (doc_id = p_id) where (p_status = 2)
      into :doc_id
  do begin
    cnt = 1;
    for select p_tmpnum, p_num, p_status, p_masternum
        from payment where p_id = :doc_id and p_tmpnum > 0
        order by p_data, p_flag, p_tmpnum
        into :p_tmpnum, :p_bignum, :p_status, :p_masternum
    do begin
      update payment set p_num = :cnt where p_id = :doc_id and p_tmpnum = :p_tmpnum;
      update paydetails set pd_num = :cnt where pd_id = :doc_id and pd_num = :p_bignum;
      if (p_status = 3) then
      begin
        select p_num from payment where p_id = :doc_id and p_tmpnum = :p_masternum
        into :p_newmasternum;

        update payment set p_masternum = :p_newmasternum where p_id = :doc_id and p_num = :cnt;
      end
      cnt = cnt + 1;
    end
  end
end;
/*SQLStop>1555*/

/*SQLStart>1556*/
execute procedure sortpaymenttable;
/*SQLStop>1556*/

/*SQLStart>1557*/
drop procedure sortpaymenttable;
/*SQLStop>1557*/

/*SQLStart>1558*/
alter table payment drop p_tmpnum;
/*SQLStop>1558*/
