Входные параметры
Параметр | Тип | Описание |
---|---|---|
CLASSID |
INTEGER |
|
CROPID |
INTEGER |
|
DOCROP |
INTEGER |
|
SORTID |
INTEGER |
|
PARTYID |
INTEGER |
|
PARTYDATE |
DATE |
|
STORAGEID |
INTEGER |
|
DATE1 |
DATE |
|
DATE2 |
DATE |
|
AOWNERID |
INTEGER |
|
CONTRACTID |
INTEGER |
|
Выходные параметры
Параметр | Тип | Описание |
---|---|---|
NET |
BIGINT |
|
CDIRT |
BIGINT |
|
CHUM |
BIGINT |
|
DIRT |
NUMERIC(9,2) |
|
HUM |
NUMERIC(9,2) |
|
REGDATE |
DATE |
|
ANALIS_ID |
INTEGER |
|
NUMDOC |
VARCHAR(31) |
|
OWNERID |
INTEGER |
|
Описание
(Нет описания для процедуры ACN_305)
Определение
CREATE PROCEDURE ACN_305(
CLASSID INTEGER,
CROPID INTEGER,
DOCROP INTEGER,
SORTID INTEGER,
PARTYID INTEGER,
PARTYDATE DATE,
STORAGEID INTEGER,
DATE1 DATE,
DATE2 DATE,
AOWNERID INTEGER,
CONTRACTID INTEGER)
RETURNS (
NET BIGINT,
CDIRT BIGINT,
CHUM BIGINT,
DIRT NUMERIC(9,2),
HUM NUMERIC(9,2),
REGDATE DATE,
ANALIS_ID INTEGER,
NUMDOC VARCHAR(31),
OWNERID INTEGER)
AS
BEGIN
FOR
select
it.regdate,
case when qd.qualitynumber is null
then it.inventoryid
else qd.qualitynumber
end as analis_id,
it.ownerid,
case
when not(it.operationid in (45,46)) then case
when (ittransport.transporttypeid = 2) then ittransport.transportnumber
when (ittransport.transporttypeid in (0,1) and it.operationid = 5 and it.orderloaditemid is not null)
then it.orderloaditemid
when ((operation.operationtype in (1,3,4,5) or (operation.operationsubtype = 1)))
then invoices.invoicenumber
else it.registerid
end
end as numdoc,
coalesce(Sum(form36aexpense.outnet),0) net,
cast(sum( form36aexpense.outnet *
cast(form36aexpense.outdirt as numeric (3,1)))/100 as integer) as cdirt,
cast(sum( form36aexpense.outnet *
cast(form36aexpense.outhumidity as numeric (3,1)))/100 as integer) as chum,
case coalesce(sum(it.net),0) when 0 then 0 else
cast(sum( form36aexpense.outnet *
form36aexpense.outdirt)/sum(form36aexpense.outnet) as numeric(3,1))
end dirt,
case coalesce(sum(it.net),0) when 0 then 0 else
cast(sum( form36aexpense.outnet *
form36aexpense.outhumidity)/sum(form36aexpense.outnet) as numeric(3,1))
end hum
from it
join getworkparties(:partyid,:partydate, :cropid, :classid, :storageid)
on (it.partyid = getworkparties.partyid)
left join quality q on (it.BuhQualityid = q.qualityid)
left join qualitydata_select_min(it.BuhQualityid) qd on (0 = 0)
left join ittransport on it.inventoryid = ittransport.inventoryid
left join operation on it.operationid = operation.operationid
left join invoices on it.invoiceid = invoices.invoiceid
left join form36aexpense(it.operationid, dirt, humidity,
it.net) on (0=0)
where
((q.classid = :classid and :docrop = 0) or (q.cropid = :cropid and :docrop = 1))
and it.regdate between :date1 and :date2
and it.storageid = :storageid
and operation.operationsubtype = 1
and operation.operationtype not in (2,1,4,5)
and it.state >= 0
and it.parentid >= 0
and ((q.sortid = :sortid) or (:sortid = 0))
and ((it.ownerid = :aownerid) or (coalesce(:aownerid,0) = 0))
and ((it.contractid = :contractid) or (coalesce(:contractid,0) = 0))
group by
it.regdate,
analis_id,
it.ownerid,
numdoc , q.classid
INTO :regdate,
:analis_id,
:ownerid,
:numdoc,
:net,
:cdirt,
:chum,
:dirt,
:hum
DO
BEGIN
SUSPEND;
END
END