ALTER TABLE INV_BARCODES_DETAIL
ADD ALCCODE DM_TEXT;

CREATE INDEX INV_BARCODES_DETAIL_IDX3
ON INV_BARCODES_DETAIL (ALCCODE);


/******************************************************************************/

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



/******************************************************************************/
/***                                 Views                                  ***/
/******************************************************************************/


/* View: VW_INV_BARCODES_DETAIL */
CREATE OR ALTER VIEW VW_INV_BARCODES_DETAIL(
    ID,
    INV_ID,
    BARCODE,
    QUANT_BASE,
    QUANT_REAL,
    PART_ID,
    INSERTDT,
    PART_OST,
    PART_OST_ALL,
    PART_MARKS,
    PART_SNAME,
    PART_OST_REAL)
AS
select
ID,
INV_ID,
BARCODE,
coalesce((select sum(quant) from egais_detail_barcodes where egais_barcode = ibd.barcode and part_id > 0 and doc_detail_id > 0),0),
QUANT_REAL,
PART_ID,
INSERTDT,
iif(part_id > 0, coalesce((select quant from warebase w where ibd.part_id = w.part_id), 0),0),
iif(part_id > 0, coalesce((select realquant from warebase w where ibd.part_id = w.part_id), 0),0),
coalesce((select sum(quant) from egais_detail_barcodes where egais_barcode = ibd.barcode and part_id = ibd.part_id and doc_detail_id > 0),0),
iif(part_id > 0, (select first 1 sname from vw_wares vw where id = (select ware_id from parts where id = ibd.part_id)), ''),
iif(part_id > 0, (select sum(iif(quant_real - quant_base = 0,iif(quant_real=0,0,1),quant_real - quant_base)) from vw_inv_barcodes_detail where part_id = ibd.part_id and inv_id = ibd.inv_id), 0)
from inv_barcodes_detail ibd
;




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

SET TERM ^ ;

create or alter procedure PR_DEL_SALE_FROM_INV_BARCODES (
    INV_ID DM_ID)
returns (
    EGAIS_BARCODE DM_TEXT)
as
declare variable FROM_DATE DM_DATE;
declare variable PART_ID DM_ID;
declare variable ALCCODE DM_TEXT;
declare variable AK DM_TEXT;
declare variable QUANT DM_DOUBLE;
declare variable QUANT_WB DM_DOUBLE;
begin
  update inv_barcodes_detail set part_id=0 where inv_id=:inv_id;

  select cast(min(i.insertdt) as dm_date) from inv_barcodes_detail i where i.inv_id=:inv_id into  :from_date;
  for select  e.egais_barcode from egais_detail e
  join doc_detail dd on dd.id=e.doc_detail_id
where EGAIS_BARCODE <> '-' AND EGAIS_BARCODE<>''
and (doc_commitdate >= :from_date) into :egais_barcode do
  begin
    update  inv_barcodes_detail set QUANT_REAL=0 where barcode=:egais_barcode and inv_id=:inv_id;
    --suspend;
  end


  update EGAIS_DETAIL_BARCODES e set e.doc_detail_id = (select dd.id from doc_detail dd where dd.doc_id=e.doc_id
  and dd.part_id=e.part_id) where e.doc_detail_id is null;

        for select barcode,alccode from inv_barcodes_detail i  where i.part_id=0 and inv_id=:inv_id into :ak,:alccode do
   begin
     for select w.part_id,w.quant from warebase w
     join parts p on p.id=w.part_id and egais_alccode=:alccode where w.part_id is not null
      order by w.part_id into :part_id,:quant_wb do
     begin
       quant=0;
       select coalesce(sum(QUANT_REAL),0) from inv_barcodes_detail i where part_id=:part_id and inv_id=:inv_id  into :quant;
       if (:quant<:quant_wb) then
       begin
         update  inv_barcodes_detail set part_id=:part_id where barcode=:ak and inv_id=:inv_id ;
         break;
       end


     end
   end


   for select barcode,alccode from inv_barcodes_detail i  where i.part_id=0 and inv_id=:inv_id into :ak,:alccode do
   begin
     for select first 1 w.part_id,w.quant from warebase w
     join parts p on p.id=w.part_id and egais_alccode=:alccode where w.part_id is not null
      order by w.part_id desc into :part_id,:quant_wb do
     begin
       update  inv_barcodes_detail set part_id=:part_id where barcode=:ak and inv_id=:inv_id ;
         break;
     end
   end
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT,UPDATE ON INV_BARCODES_DETAIL TO PROCEDURE PR_DEL_SALE_FROM_INV_BARCODES;
GRANT SELECT ON EGAIS_DETAIL TO PROCEDURE PR_DEL_SALE_FROM_INV_BARCODES;
GRANT SELECT ON DOC_DETAIL TO PROCEDURE PR_DEL_SALE_FROM_INV_BARCODES;
GRANT SELECT,UPDATE ON EGAIS_DETAIL_BARCODES TO PROCEDURE PR_DEL_SALE_FROM_INV_BARCODES;
GRANT SELECT ON WAREBASE TO PROCEDURE PR_DEL_SALE_FROM_INV_BARCODES;
GRANT SELECT ON PARTS TO PROCEDURE PR_DEL_SALE_FROM_INV_BARCODES;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_DEL_SALE_FROM_INV_BARCODES TO SYSDBA;