"localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры

ACC_FORM1

Входные параметры

Выходные параметры

Описание

Бухгалтерия - форма №1 (На Киев)

Определение

CREATE PROCEDURE ACC_FORM1(
    DATE2 DATE,
    PARTYID INTEGER,
    PARTYDATE DATE)
RETURNS (
    GRAINCROPS_INCOME INTEGER,
    OILCROPS_INCOME INTEGER,
    PROM_INCOME INTEGER)
AS
begin

  select
    sum(it.net)
  from it
    join getworkparties(:partyid,:partydate,null,null,null)
       on (it.partyid = getworkparties.partyid)
    left join quality on (it.samplerqualityid = quality.qualityid)
    left join crop on (quality.cropid = crop.cropid)
    left join invoices on (it.invoiceid = invoices.invoiceid)
    left join clients on (invoices.senderid = clients.clientid)
  where it.regdate <= :date2
    and it.parentid >=0
    and it.state >=0
    and it.operationid in (1,17)
    and it.storingid = 2
    and clients.storageid is null
   into
    :prom_income;

  select
    SUM(it.net)
  from it
   join getworkparties(:partyid,:partydate,null,null,null)
     on (it.partyid = getworkparties.partyid)
   left join quality on (it.samplerqualityid = quality.qualityid)
   left join crop on (quality.cropid = crop.cropid)
   left join invoices on (it.invoiceid = invoices.invoiceid)
   left join clients on (invoices.senderid = clients.clientid)
  where crop.croptypeid in (1,6)
    and it.regdate <= :date2
    and it.parentid >= 0
    and it.state >= 0
    and it.operationid in (1,17)
    and ((it.storingid <> 2) or (it.storingid is null) )
    and clients.storageid is null
   into
    :graincrops_income;

  select SUM(it.net) from it
    join getworkparties(:partyid,:partydate,null,null,null)
       on (it.partyid = getworkparties.partyid)
   left join quality on (it.samplerqualityid = quality.qualityid)
   left join crop on (quality.cropid = crop.cropid)
   left join invoices on (it.invoiceid = invoices.invoiceid)
   left join clients on (invoices.senderid = clients.clientid)
  where crop.croptypeid = 5
    and it.regdate <= :date2
    and it.parentid >=0
    and it.state >=0
    and it.operationid in (1,17)
    and ((it.storingid <> 2) or (it.storingid is null) )
    and clients.storageid is null
   into
    :oilcrops_income;

    graincrops_income = cast(:graincrops_income as double precision)  / 1000;
    oilcrops_income = cast(:oilcrops_income as double precision)  / 1000 ;
    prom_income = cast(:prom_income as double precision)  / 1000;

  suspend;

  select SUM(it.net) from it
    join getworkparties(:partyid,:partydate,null,null,null)
       on (it.partyid = getworkparties.partyid)
   left join quality on (it.samplerqualityid = quality.qualityid)
   left join crop on (quality.cropid = crop.cropid)
   left join clients on (it.ownerid = clients.clientid)
   left join invoices on (it.invoiceid = invoices.invoiceid)
   left join clients cs on (invoices.senderid = cs.clientid)
  where it.regdate <= :date2
    and it.parentid >=0
    and it.state >=0
    and it.operationid in (1,17)
    and it.storingid = 2
    and clients.clienttypeid = 5
    and cs.storageid is null
   into
    :prom_income;


  select
    SUM(it.net)
  from it
    join getworkparties(:partyid,:partydate,null,null,null)
       on (it.partyid = getworkparties.partyid)
   left join quality on (it.samplerqualityid = quality.qualityid)
   left join crop on (quality.cropid = crop.cropid)
   left join clients on (it.ownerid = clients.clientid)
   left join qualitydata region on (it.samplerqualityid = region.qualityid
     and region.qualitytypeid = 18)
   left join invoices on (it.invoiceid = invoices.invoiceid)
   left join clients cs on (invoices.senderid = cs.clientid)
  where crop.croptypeid in (1,6)
    and it.regdate <= :date2
    and it.parentid >=0
    and it.state >=0
    and it.operationid in (1,17)
    and clients.clienttypeid = 5
    and cs.storageid is null
    and ((it.storingid <> 2) or (it.storingid is null) )
   into
    :graincrops_income;

  select
    SUM(it.net)
  from it
    join getworkparties(:partyid,:partydate,null,null,null)
       on (it.partyid = getworkparties.partyid)
   left join quality on (it.samplerqualityid = quality.qualityid)
   left join crop on (quality.cropid = crop.cropid)
   left join clients on (it.ownerid = clients.clientid)
   left join invoices on (it.invoiceid = invoices.invoiceid)
   left join clients cs on (invoices.senderid = cs.clientid)
  where crop.croptypeid = 5
    and it.regdate <= :date2
    and it.parentid >=0
    and it.state >=0
    and it.operationid in (1,17)
    and clients.clienttypeid = 5
    and ((it.storingid <> 2) or (it.storingid is null) )
    and cs.storageid is null
   into
    :oilcrops_income;

    graincrops_income = cast(:graincrops_income as double precision)  / 1000;
    oilcrops_income = cast(:oilcrops_income as double precision)  / 1000 ;
    prom_income = cast(:prom_income as double precision)  / 1000;

  suspend;
end