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

FORWARDER_OUTCOMEJOURNAL

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_OUTCOMEJOURNAL(
    BEGINDATE DATE,
    ENDDATE DATE,
    OPERATIONID INTEGER,
    ACROPID INTEGER,
    ACLASSID INTEGER,
    AOWNERID INTEGER,
    ISCURRENT INTEGER,
    APARTYDATE DATE,
    ASTORAGEID INTEGER)
RETURNS (
    REGDATE DATE,
    FORWARDERFULLNAME VARCHAR(100),
    PROVIDERID INTEGER,
    INVENTORYID INTEGER,
    INVOICEID INTEGER,
    INVOICENUMBER VARCHAR(31),
    TRANSPORTNUMBER VARCHAR(31),
    DRIVERS VARCHAR(30),
    CROPFULLNAME VARCHAR(100),
    NET INTEGER,
    GROSS INTEGER,
    TARE INTEGER,
    PROVIDERFULLNAME VARCHAR(100),
    FORWARDERID INTEGER,
    ACTOPERATIONID INTEGER,
    ACTNUMBER VARCHAR(10),
    OWNERID INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER,
    EXPORTACTID INTEGER,
    OWNERFULLNAME VARCHAR(100),
    PARENTITEMID INTEGER,
    REGISTERNET INTEGER,
    STORAGEID INTEGER,
    STATE INTEGER,
    SAMPLERQUALITYID INTEGER,
    LABQUALITYID INTEGER,
    WEIGHT INTEGER,
    STORAGENAME VARCHAR(100),
    INUSE INTEGER,
    ISWEIGHT SMALLINT,
    PARTYID INTEGER)
AS
BEGIN
  for
    select
        IT.REGDATE, FORWARDER.CLIENTFULLNAME FORWARDERFULLNAME,
        PROVIDER.CLIENTFULLNAME PROVIDERFULLNAME, OWNER.CLIENTFULLNAME OWNERFULLNAME,
        IT.PROVIDERID, IT.INVENTORYID, IT.INVOICEID, INVOICES.INVOICENUMBER,
        ITTRANSPORT.TRANSPORTNUMBER, INVOICES.DRIVERS, QUALITY_CROPFULLNAME.CROPFULLNAME,
        IT.NET, IT.GROSS, IT.TARE, IT.STORAGEID, IT.STATE, IT.SAMPLERQUALITYID,
        IT.LABQUALITYID, IT.WEIGHT, INVOICES.FORWARDERID, IT.OPERATIONID ACTOPERATIONID,
        EXPORTACTS.ACTNUMBER, IT.OWNERID, Q1.CROPID, Q1.CLASSID, EXPORTACTS.EXPORTACTID,
        EXPORTACTITEMS.PARENTITEMID,
        (coalesce((select sum (-EXPORTACTITEMS.WEIGHT)
            from EXPORTACTITEMS
            where (EXPORTACTITEMS.OPERATIONID = :OPERATIONID)
              and (EXPORTACTITEMS.INVENTORYID = IT.INVENTORYID)
              and (EXPORTACTITEMS.OPERATIONID <> 5)), 0) +
        (IT.NET - IT.WEIGHT)) REGISTERNET,
        coalesce((select count(EXPORTACTID)
            from EXPORTACTITEMS
            where (EXPORTACTITEMS.INVENTORYID = :INVENTORYID)
              and (EXPORTACTITEMS.OPERATIONID <> 5)), 0
        ) INUSE,
        STORAGE.STORAGESHORTNAME, OWNER.ISWEIGHT, IT.PARTYID
      from IT
        join GETWORKPARTIES(:ISCURRENT, :APARTYDATE,
            case
              when (coalesce(:ACROPID,-1) = -1) then
                null
              else :ACROPID
            end,
            case
              when (coalesce(:ACLASSID,-1) = -1) then
                null
              else :ACLASSID
            end,
            case
              when (coalesce(:ASTORAGEID,-1) = -1) then
                null
              else :ASTORAGEID
            end)
            on (GETWORKPARTIES.PARTYID = IT.PARTYID)
        left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
        left join CLIENTS PROVIDER on (PROVIDER.CLIENTID = IT.PROVIDERID)
        left join CLIENTS FORWARDER on (FORWARDER.CLIENTID = INVOICES.FORWARDERID)
        left join CLIENTS OWNER on (IT.OWNERID = OWNER.CLIENTID)
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
        left join QUALITY Q1 on (Q1.QUALITYID = IT.SAMPLERQUALITYID)
        left join QUALITY_CROPFULLNAME (Q1.CROPID, Q1.CLASSID, null, 1) on (0 = 0)
        left join EXPORTACTITEMS on (EXPORTACTITEMS.INVENTORYID = IT.INVENTORYID)
        left join QUALITY WASTENAME on (WASTENAME.QUALITYID = IT.OPERATIONPARAM)
        left join EXPORTACTS on (EXPORTACTS.EXPORTACTID = EXPORTACTITEMS.EXPORTACTID)
        left join STORAGE on (STORAGE.STORAGEID = IT.STORAGEID)
      where (IT.REGDATE between :BEGINDATE and :ENDDATE)
        and (IT.OPERATIONID = :OPERATIONID)
        and ((Q1.CROPID = :ACROPID) or (coalesce(:ACROPID,-1) = -1))
        and ((Q1.CLASSID = :ACLASSID) or (coalesce(:ACLASSID,-1) = -1))
        and ((IT.OWNERID = :AOWNERID) or (coalesce(:AOWNERID,-1) = -1))
        and ((IT.STORAGEID = :ASTORAGEID) or (coalesce(:ASTORAGEID,-1) = -1))
        and ((IT.STATE >= 0) or (IT.STATE = -10))
        and ((IT.NET > 0) or ((IT.NET < 0) and (:OPERATIONID = 65)))
        and ((IT.OPERATIONID <> 22)
             or ((IT.OPERATIONID = 22)
                 and ((WASTENAME.CROPID = 9) and (WASTENAME.CLASSID in (41,141)))))
    order by IT.REGDATE, QUALITY_CROPFULLNAME.CROPFULLNAME
    into :REGDATE, :FORWARDERFULLNAME, :PROVIDERFULLNAME, :OWNERFULLNAME,
         :PROVIDERID, :INVENTORYID, :INVOICEID, :INVOICENUMBER, :TRANSPORTNUMBER,
         :DRIVERS, :CROPFULLNAME, :NET, :GROSS, :TARE, :STORAGEID, :STATE,
         :SAMPLERQUALITYID, :LABQUALITYID, :WEIGHT, :FORWARDERID, :ACTOPERATIONID,
         :ACTNUMBER, :OWNERID, :CROPID, :CLASSID, :EXPORTACTID, :PARENTITEMID,
         :REGISTERNET, :INUSE, :STORAGENAME, :ISWEIGHT, :PARTYID

  do suspend;

end