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

FORWARDER_QUALITY_NET

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

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

Описание

(Нет описания для процедуры FORWARDER_QUALITY_NET)

Определение

CREATE PROCEDURE FORWARDER_QUALITY_NET(
    AEXPORTACTID INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER,
    ISCURRENT INTEGER,
    APARTYDATE DATE,
    ASTORAGEID INTEGER)
RETURNS (
    HUMIDITY NUMERIC(9,2),
    DIRT NUMERIC(9,2),
    SCREENING NUMERIC(9,2),
    ACTNET INTEGER,
    SUMDEFICIT INTEGER,
    SUMINVOICENET INTEGER,
    STORAGEID INTEGER)
AS
begin
  for
    select IT1.STORAGEID,
        sum(EAIV.WEIGHT) ACTNET,
        sum(case EXPORTACTS.OPERATIONID
              when 1 then ITTRANSPORT1.INVOICENET
            end) SUMINVOICENET,
        sum(case
              when ((EAIV.OPERATIONID = 1) and (ITTRANSPORT1.TRANSPORTTYPEID in (0,2))
                    and (EAIV.WEIGHT > 0)) then
                IT1.NET - ITTRANSPORT1.INVOICENET
              when ((EAIV.OPERATIONID = 1) and (ITTRANSPORT1.TRANSPORTTYPEID = 1)
                    and ((select IT2.NET - ITTRANSPORT2.INVOICENET
                               from IT IT2
                                 left join ITTRANSPORT ITTRANSPORT2 on (ITTRANSPORT2.INVENTORYID = IT2.INVENTORYID)
                               where (IT2.INVOICEID = IT1.INVOICEID)
                                 and (IT2.INVENTORYID <> IT1.INVENTORYID)
                                 and (IT2.STATE >= 0)
                                 and (IT2.OPERATIONID = IT1.OPERATIONID)
                         ) is not null)
                   ) then
                coalesce(IT1.NET,0) - coalesce(ITTRANSPORT1.INVOICENET,0)
            end) SUMDEFICIT,
        cast(sum(QUALITY_SELECT.HUMIDITY * EAIV.WEIGHT) / sum(nullif(EAIV.WEIGHT,0) + QUALITY_SELECT.HUMIDITY - QUALITY_SELECT.HUMIDITY) as numeric(9,2)) HUMIDITY,
        cast(sum(QUALITY_SELECT.DIRT * EAIV.WEIGHT) / sum(nullif(EAIV.WEIGHT,0) + QUALITY_SELECT.DIRT - QUALITY_SELECT.DIRT) as numeric(9,2)) DIRT,
        cast(sum(QUALITY_SELECT.SCREENING * EAIV.WEIGHT) / sum(nullif(EAIV.WEIGHT,0) + QUALITY_SELECT.SCREENING - QUALITY_SELECT.SCREENING) as numeric(9,2)) SCREENING
      from EXPORTACTS
        left join EXPORTACTITEMS_VIEW EAIV on (EAIV.EXPORTACTID = EXPORTACTS.EXPORTACTID)
        left join IT IT1 on (IT1.INVENTORYID = EAIV.INVENTORYID)
        join GETWORKPARTIES(:ISCURRENT, :APARTYDATE,
                            case
                              when (coalesce(:CROPID,-1) = -1)
                                then null
                              else :CROPID
                            end,
                            case
                              when (coalesce(:CLASSID,-1) = -1) then
                                null
                              else :CLASSID
                            end,
                            case
                              when (coalesce(:ASTORAGEID,-1) = -1) then
                                null
                              else :ASTORAGEID
                            end
                           ) on (IT1.PARTYID = GETWORKPARTIES.PARTYID)
        left join ITTRANSPORT ITTRANSPORT1 on (ITTRANSPORT1.INVENTORYID = EAIV.INVENTORYID)
        left join QUALITY_SELECT(IT1.LABQUALITYID) on (0=0)
      where (EXPORTACTS.EXPORTACTID = :AEXPORTACTID)
        and ((EAIV.WEIGHT > 0) or (EAIV.WEIGHT is null))
    group by IT1.STORAGEID
    into :STORAGEID, :ACTNET, :SUMINVOICENET, :SUMDEFICIT,
         :HUMIDITY, :DIRT, :SCREENING
  do suspend;
END