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

ACC_FORMRN1_2005

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

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

Описание

Бухгалтерия - формы РН1 и РН2

Определение

CREATE PROCEDURE ACC_FORMRN1_2005(
    DATE2 DATE,
    PARTYID INTEGER,
    PARTYDATE DATE,
    FORMTYPE INTEGER,
    SHORTFORM SMALLINT)
RETURNS (
    INCOMETYPE VARCHAR(100),
    TTNS_INCOME INTEGER,
    REB_INCOME INTEGER,
    OUTCOME INTEGER,
    REB_OUTCOME INTEGER,
    INITIALREM INTEGER,
    REM INTEGER)
AS
begin
    reb_income = 0;
    reb_outcome = 0; 
   incometype = 'Державна заставна закупівля(заготівля підприємства)';
  suspend;
   incometype = 'Закупка за кредити та власні кошти';
  suspend;
   incometype = 'Закуплено за регіональні ресурси';
  suspend;
   incometype = 'Зберігання на загальних умовах';


  if (:shortform = 1) then
    initialrem = 0;
  else
   select coalesce(sum(it.net),0) 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 qualitydata region on (it.samplerqualityid = region.qualityid and region.qualitytypeid = 18)
   where
    (((crop.croptypeid in (1,6)) and (:formtype = 1))
     or
    ((crop.croptypeid =5) and (:formtype = 2)))

    and it.regdate <= :date2
    and it.parentid >= 0
    and it.state >=0
    and it.operationid = 45
    and ((it.storingid not in (2,7)) or (it.storingid is null))
    and ((region.qualityvalue <> -10) or (region.qualityvalue is null))
   into
    :initialrem;


  select coalesce(sum(it.net),0) 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 qualitydata region on (it.samplerqualityid = region.qualityid and region.qualitytypeid = 18)
  where
    (((crop.croptypeid in (1,6)) and (:formtype = 1))
     or
    ((crop.croptypeid =5) and (:formtype = 2)))

    and it.regdate <= :date2
    and it.parentid >= 0
    and it.state >=0
    and it.operationid = 1
    and ((it.storingid not in (2,7)) or (it.storingid is null))
    and ((region.qualityvalue <> -10) or (region.qualityvalue is null))
   into
    :ttns_income;

/*  select coalesce(sum(it.net),0) from it
  left join quality on (it.samplerqualityid = quality.qualityid)
  left join crop on (quality.cropid = crop.cropid)
  where
    (((crop.croptypeid in (1,6)) and (:formtype = 1))
     or
    ((crop.croptypeid =5) and (:formtype = 2)))

    and it.partyid = (select partyid from getpartyid(:PARTYID,:PARTYDATE, quality.cropid, quality.classid, it.storageid))
    and it.regdate <= :date2
    and it.parentid >= 0
    and it.state >=0
    and it.operationid = 17
    and it.net > 0
    and ((it.storingid not in (2,7)) or (it.storingid is null))
   into
    :reb_income;


  select coalesce(sum(it.net),0) * -1 from it
  left join quality on (it.samplerqualityid = quality.qualityid)
  left join crop on (quality.cropid = crop.cropid)
  where
    (((crop.croptypeid in (1,6)) and (:formtype = 1))
     or
    ((crop.croptypeid =5) and (:formtype = 2)))

    and it.partyid = (select partyid from getpartyid(:PARTYID,:PARTYDATE, quality.cropid, quality.classid, it.storageid))
    and it.regdate <= :date2
    and it.parentid >= 0
    and it.state >=0
    and it.operationid = 17
    and it.net < 0
    and ((it.storingid not in (2,7)) or (it.storingid is null))
   into
    :reb_outcome;      */

  if (:shortform = 1) then
    outcome = 0;
  else

  select coalesce(sum(it.net),0) 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 operation on (it.operationid = operation.operationid)
    left join qualitydata region on (it.samplerqualityid = region.qualityid and region.qualitytypeid = 18)
  where
    (((crop.croptypeid in (1,6)) and (:formtype = 1))
     or
    ((crop.croptypeid =5) and (:formtype = 2)))

    and it.regdate <= :date2
    and it.parentid >= 0
    and it.state >=0
    and operation.operationsubtype = 1
--    and it.operationid = 5
    and ((it.storingid not in (2,7)) or (it.storingid is null))
    and ((region.qualityvalue <> -10) or (region.qualityvalue is null))
   into
    :outcome;

    initialrem = cast(:initialrem as double precision)/ 1000;
    ttns_income = cast(:ttns_income as double precision) / 1000;
  /*  reb_income = cast(:reb_income as double precision) / 1000;
    reb_outcome = cast(:reb_outcome as double precision) / 1000;    */
    outcome = cast(:outcome as double precision) / 1000;

    if (:shortform = 1) then
     rem = 0;
    else
     rem = :initialrem + :ttns_income + :reb_income - :reb_outcome - :outcome;

  suspend;
    rem = 0;
    initialrem = 0;
    ttns_income = 0;
    reb_income = 0;
    reb_outcome = 0;
    outcome = 0;


   incometype = 'в  т.ч. державна заставна закупівля здійснена іншими підприємствами';
  suspend;

   incometype = 'невитребуване зерно державної заставної закупівлі';
  suspend;

   incometype = 'держресурси';
  suspend;
   incometype = 'держрезерв';
  suspend;


   incometype = 'зерно головного офісу ДАК "Хліб України"';

  if (:shortform = 1) then
    initialrem = 0;
  else

  select coalesce(sum(it.net),0) 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)
  where
    (((crop.croptypeid in (1,6)) and (:formtype = 1))
     or
    ((crop.croptypeid =5) and (:formtype = 2)))
    and clients.clienttypeid = 5
    and it.regdate <= :date2
    and it.parentid >= 0
    and it.state >=0
    and it.operationid = 45
    and ((it.storingid not in (2,7)) or (it.storingid is null))
    and ((region.qualityvalue <> -10) or (region.qualityvalue is null))
   into
    :initialrem;


  select coalesce(sum(it.net),0) 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)
  where
    (((crop.croptypeid in (1,6)) and (:formtype = 1))
     or
    ((crop.croptypeid =5) and (:formtype = 2)))
    and clients.clienttypeid = 5
    and it.regdate <= :date2
    and it.parentid >= 0
    and it.state >=0
    and it.operationid = 1
    and ((it.storingid not in (2,7)) or (it.storingid is null))
    and ((region.qualityvalue <> -10) or (region.qualityvalue is null))
   into
    :ttns_income;

/*  select coalesce(sum(it.net),0) from it
  left join quality on (it.samplerqualityid = quality.qualityid)
  left join crop on (quality.cropid = crop.cropid)
  left join clients on (it.ownerid = clients.clientid)
  where
    (((crop.croptypeid in (1,6)) and (:formtype = 1))
     or
    ((crop.croptypeid =5) and (:formtype = 2)))
    and clients.clienttypeid = 5
    and it.partyid = (select partyid from getpartyid(:PARTYID,:PARTYDATE, quality.cropid, quality.classid, it.storageid))
    and it.regdate <= :date2
    and it.parentid >= 0
    and it.state >=0
    and it.operationid = 17
    and it.net > 0
    and ((it.storingid not in (2,7)) or (it.storingid is null))
   into
    :reb_income;


  select coalesce(sum(it.net),0) * -1 from it
  left join quality on (it.samplerqualityid = quality.qualityid)
  left join crop on (quality.cropid = crop.cropid)
  left join clients on (it.ownerid = clients.clientid)
  where
    (((crop.croptypeid in (1,6)) and (:formtype = 1))
     or
    ((crop.croptypeid =5) and (:formtype = 2)))
    and clients.clienttypeid = 5
    and it.partyid = (select partyid from getpartyid(:PARTYID,:PARTYDATE, quality.cropid, quality.classid, it.storageid))
    and it.regdate <= :date2
    and it.parentid >= 0
    and it.state >=0
    and it.operationid = 17
    and it.net < 0
    and ((it.storingid not in (2,7)) or (it.storingid is null))
   into
    :reb_outcome;*/

  if (:shortform = 1) then
    outcome = 0;
  else

  select coalesce(sum(it.net),0) 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)
  where
    (((crop.croptypeid in (1,6)) and (:formtype = 1))
     or
    ((crop.croptypeid =5) and (:formtype = 2)))
    and clients.clienttypeid = 5
    and it.regdate <= :date2
    and it.parentid >= 0
    and it.state >=0
    and it.operationid = 5
    and ((it.storingid not in (2,7)) or (it.storingid is null))
    and ((region.qualityvalue <> -10) or (region.qualityvalue is null))
   into
    :outcome;


    initialrem = cast(:initialrem as double precision)/ 1000;
    ttns_income = cast(:ttns_income as double precision) / 1000;
  /*  reb_income = cast(:reb_income as double precision) / 1000;
    reb_outcome = cast(:reb_outcome as double precision) / 1000;  */
    outcome = cast(:outcome as double precision) / 1000;

    if (:shortform = 1) then
     rem = 0;
    else
     rem = :initialrem + :ttns_income + :reb_income - :reb_outcome - :outcome;

  suspend;
    rem = 0;
    initialrem = 0;
    ttns_income = 0;
    reb_income = 0;
    reb_outcome = 0;
    outcome = 0;

   incometype = 'Власне виробництво';

  if (:shortform = 1) then
    initialrem = 0;
  else

  select coalesce(sum(it.net),0) 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 qualitydata region on (it.samplerqualityid = region.qualityid and region.qualitytypeid = 18)
  where
    (((crop.croptypeid in (1,6)) and (:formtype = 1))
     or
    ((crop.croptypeid =5) and (:formtype = 2)))
    and it.regdate <= :date2
    and it.parentid >= 0
    and it.state >=0
    and it.operationid = 45
    and it.storingid = 7
    and ((region.qualityvalue <> -10) or (region.qualityvalue is null))
   into
    :initialrem;


  select coalesce(sum(it.net),0) 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 qualitydata region on (it.samplerqualityid = region.qualityid and region.qualitytypeid = 18)
  where
    (((crop.croptypeid in (1,6)) and (:formtype = 1))
     or
    ((crop.croptypeid =5) and (:formtype = 2)))

    and it.regdate <= :date2
    and it.parentid >= 0
    and it.state >=0
    and it.operationid = 1
    and it.storingid = 7
    and ((region.qualityvalue <> -10) or (region.qualityvalue is null))
   into
    :ttns_income;

/*  select coalesce(sum(it.net),0) from it
  left join quality on (it.samplerqualityid = quality.qualityid)
  left join crop on (quality.cropid = crop.cropid)
  where
    (((crop.croptypeid in (1,6)) and (:formtype = 1))
     or
    ((crop.croptypeid =5) and (:formtype = 2)))

    and it.partyid = (select partyid from getpartyid(:PARTYID,:PARTYDATE, quality.cropid, quality.classid, it.storageid))
    and it.regdate <= :date2
    and it.parentid >= 0
    and it.state >=0
    and it.operationid = 17
    and it.net > 0
    and it.storingid = 7
   into
    :reb_income;


  select coalesce(sum(it.net),0) * -1 from it
  left join quality on (it.samplerqualityid = quality.qualityid)
  left join crop on (quality.cropid = crop.cropid)
  where
    (((crop.croptypeid in (1,6)) and (:formtype = 1))
     or
    ((crop.croptypeid =5) and (:formtype = 2)))

    and it.partyid = (select partyid from getpartyid(:PARTYID,:PARTYDATE, quality.cropid, quality.classid, it.storageid))
    and it.regdate <= :date2
    and it.parentid >= 0
    and it.state >=0
    and it.operationid = 17
    and it.net < 0
    and it.storingid = 7
   into
    :reb_outcome;*/

  if (:shortform = 1) then
    outcome = 0;
  else

  select coalesce(sum(it.net),0) 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 qualitydata region on (it.samplerqualityid = region.qualityid and region.qualitytypeid = 18)
  where
    (((crop.croptypeid in (1,6)) and (:formtype = 1))
     or
    ((crop.croptypeid =5) and (:formtype = 2)))

    and it.regdate <= :date2
    and it.parentid >= 0
    and it.state >=0
    and it.operationid = 5
    and it.storingid = 7
    and ((region.qualityvalue <> -10) or (region.qualityvalue is null))
   into
    :outcome;

    initialrem = cast(:initialrem as double precision)/ 1000;
    ttns_income = cast(:ttns_income as double precision) / 1000;
/*    reb_income = cast(:reb_income as double precision) / 1000;
    reb_outcome = cast(:reb_outcome as double precision) / 1000;    */
    outcome = cast(:outcome as double precision) / 1000;

    if (:shortform = 1) then
     rem = 0;
    else
     rem = :initialrem + :ttns_income + :reb_income - :reb_outcome - :outcome;


  suspend;
    initialrem = 0;
    ttns_income = 0;
    reb_income = 0;
    reb_outcome = 0;
    outcome = 0;
    rem = 0;
    incometype = 'Давальницька пром. переробка';
  suspend;

    incometype = 'в т.ч. зерно головного офісу ДАК "Хліб України"';
  suspend;

end