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

AC_STORAGETICKJOURNAL

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

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

Описание

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

Определение

CREATE PROCEDURE AC_STORAGETICKJOURNAL(
    BEGINDATE DATE,
    ENDDATE DATE,
    ACROPID INTEGER,
    ACLASSID INTEGER,
    AOWNERID INTEGER,
    ASTORAGEID INTEGER,
    ASTORINGID INTEGER)
RETURNS (
    STORAGETICKETID INTEGER,
    NUMBER INTEGER,
    BLANKSERIES VARCHAR(2),
    BLANKNUMBER VARCHAR(6),
    REGDATE DATE,
    OWNERID INTEGER,
    CLIENTNAME VARCHAR(30),
    QUALITYID INTEGER,
    CROPFULLNAME VARCHAR(100),
    NET INTEGER,
    WEIGHT INTEGER,
    CONTRACTID INTEGER,
    PROXYPERSON VARCHAR(50),
    CANCELDATE DATE,
    STATE SMALLINT,
    REGISTERNUMBER VARCHAR(11),
    INVOICEREGISTERID INTEGER,
    COMMENTS VARCHAR(255),
    REASON VARCHAR(255),
    NUM INTEGER,
    STORAGEID INTEGER,
    "SIGN" VARCHAR(10),
    STORAGESHORTNAME VARCHAR(15),
    STORINGID INTEGER,
    STORINGNAME VARCHAR(30),
    ANALYSISDATE DATE,
    FINALDATE DATE,
    OPERATIONID INTEGER)
AS
begin
  NUM = 0;

  for
    select distinct STORAGETICKETS.STORAGETICKETID, STORAGETICKETS.NUMBER,
        STORAGETICKETS.BLANKSERIES, STORAGETICKETS.BLANKNUMBER, STORAGETICKETS.REGDATE,
        STORAGETICKETS.OWNERID, CLIENTS.CLIENTNAME, STORAGETICKETS.QUALITYID,
        case
          when (STORAGETICKETS.QUALITYID is null) then
            ITFULLNAME.CROPFULLNAME
          else FULLNAME.CROPFULLNAME
        end CROPFULLNAME,
        STORAGETICKETS.NET, STORAGETICKETS.WEIGHT, STORAGETICKETS.CONTRACTID,
        STORAGETICKETS.PROXYPERSON, STORAGETICKETS.CANCELDATE, STORAGETICKETS.STATE,
        case
          when (IT.OPERATIONID = 17) then
            'переоф.'
          when (IT.OPERATIONID = 45) then
            'инвент.'
          else INVOICEREGISTERS.REGISTERNUMBER
        end REGISTERNUMBER,
        INVOICEREGISTERS.INVOICEREGISTERID, STORAGETICKETS.COMMENTS, STORAGETICKETS.REASON,
        '          ' SIGN, STORAGETICKETS.STORAGEID, STORAGE.STORAGESHORTNAME,
        STORING.STORINGID, STORING.STORINGNAME, Q.ANALYSISDATE,
        STORAGETICKETS.FINALDATE, IT.OPERATIONID
      from STORAGETICKETS
        left join CLIENTS on (CLIENTS.CLIENTID = STORAGETICKETS.OWNERID)
        left join QUALITY Q on (Q.QUALITYID = STORAGETICKETS.QUALITYID)
        left join QUALITY_CROPFULLNAME(Q.CROPID, Q.CLASSID, null, 1) FULLNAME on (0=0)
        left join INVOICEREGISTERS on (INVOICEREGISTERS.STORAGETICKETID = STORAGETICKETS.STORAGETICKETID)
        left join IT on (IT.REGISTERID = INVOICEREGISTERS.INVOICEREGISTERID)
        left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
        left join QUALITY ITQ on (ITQ.QUALITYID = IT.BUHQUALITYID)
        left join STORING on (STORING.STORINGID = IT.STORINGID)
        left join QUALITY_CROPFULLNAME(ITQ.CROPID, ITQ.CLASSID, null, 1) ITFULLNAME on (0=0)
        left join STORAGE on (STORAGE.STORAGEID = STORAGETICKETS.STORAGEID)
      where (STORAGETICKETS.REGDATE between :BEGINDATE and :ENDDATE)
        and (STORAGETICKETS.STATE >= 0) and (coalesce(IT.OPERATIONID,-1) not in (4,9))
        and ((Q.CROPID = :ACROPID) or (coalesce(:ACROPID,-1) = -1))
        and ((Q.CLASSID = :ACLASSID) or (coalesce(:ACLASSID,-1) = -1))
        and ((IT.STORINGID = :ASTORINGID) or (coalesce(:ASTORINGID,-1) = -1))
        and ((STORAGETICKETS.OWNERID = :AOWNERID) or (coalesce(:AOWNERID,-1) = -1))
        and ((STORAGETICKETS.STORAGEID = :ASTORAGEID) or (coalesce(:ASTORAGEID,-1) = -1))
    order by STORAGETICKETS.REGDATE, STORAGETICKETS.NUMBER
    into :STORAGETICKETID, :NUMBER, :BLANKSERIES, :BLANKNUMBER, :REGDATE,
         :OWNERID, :CLIENTNAME, :QUALITYID, :CROPFULLNAME, :NET, :WEIGHT,
         :CONTRACTID, :PROXYPERSON, :CANCELDATE, :STATE, :REGISTERNUMBER,
         :INVOICEREGISTERID, :COMMENTS, :REASON, :sign, :STORAGEID,
         :STORAGESHORTNAME, :STORINGID, :STORINGNAME, :ANALYSISDATE,
         :FINALDATE, :OPERATIONID
  do begin
    NUM = :NUM + 1;

    suspend;
  end
end