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

FORWARDER_INVENTORY

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_INVENTORY(
    AOWNERID INTEGER,
    AEXPORACTID INTEGER,
    ACROPID INTEGER,
    ACLASSID INTEGER,
    APROVIDERID INTEGER,
    ASTORAGEID INTEGER,
    APARTYID INTEGER)
RETURNS (
    ACTDATE DATE,
    ACTNUMBER VARCHAR(10),
    EXPORTACTID INTEGER,
    TRANSPORTSUBTYPEID INTEGER,
    READONLYREC INTEGER,
    ISCHECKED INTEGER,
    SUMACTNET BIGINT,
    CUSTOMNET BIGINT,
    SUMTRWEIGHT BIGINT,
    PROVIDERFULLNAME VARCHAR(30))
AS
declare variable MINDATE date;
begin
  select first 1 IT.REGDATE
    from IT
      left join QUALITY on (QUALITY.QUALITYID = IT.BUHQUALITYID)
    where (IT.OWNERID = :AOWNERID)
      and (IT.OPERATIONID in (1,17,45))
      and (IT.STATE <> -1)
      and (IT.PARTYID = :APARTYID)
      and (IT.STORAGEID = :ASTORAGEID) --нвс
      and (QUALITY.CROPID = :ACROPID)
      and (QUALITY.CLASSID = :ACLASSID)
  into :MINDATE;

  for
    select EA.ACTDATE, PROVIDERS.CLIENTNAME PROVIDERFULLNAME, EA.ACTNUMBER,
        EA.EXPORTACTID, EA.TRANSPORTSUBTYPEID, 0 READONLYREC, 0 ISCHECKED
      from EXPORTACTS EA
        left join CLIENTS PROVIDERS on (PROVIDERS.CLIENTID = EA.PROVIDERID)
        left join QUALITY on (QUALITY.QUALITYID = EA.QUALITYID)
        left join EXPORTACTITEMS on (EXPORTACTITEMS.EXPORTACTID = EA.EXPORTACTID)
        left join IT on (IT.INVENTORYID = EXPORTACTITEMS.INVENTORYID)
      where (EA.REGDATE >= :MINDATE)
        and (EA.OWNERID = :AOWNERID)
        and (EA.OPERATIONID in (1,17))
        and (EA.STATE = 0)
        and (IT.PARTYID = :APARTYID)
        and (QUALITY.CROPID = :ACROPID)
        and (QUALITY.CLASSID = :ACLASSID)
        and (IT.STORAGEID = coalesce(:ASTORAGEID,-1) or (coalesce(:ASTORAGEID,-1) = -1))
    group by  EA.ACTDATE, PROVIDERS.CLIENTNAME, EA.ACTNUMBER, EA.EXPORTACTID,
      EA.TRANSPORTSUBTYPEID
    into :ACTDATE, :PROVIDERFULLNAME, :ACTNUMBER, :EXPORTACTID,
         :TRANSPORTSUBTYPEID, :READONLYREC, :ISCHECKED

  do begin
    for
      select sum(RESTWEIGHT) SUMACTNET, sum(INV_WEIGHT) CUSTOMNET, sum(INCOMEWEIGHT) SUMTRWEIGHT
        from EXPORTACTITEMS_VIEW EAIMAIN
          left join FORWARDER_WEIGHTITEM_INVENTORY(:AEXPORACTID, EAIMAIN.EXPORTACTITEMID, EAIMAIN.EXPORTACTID) on (0=0)
        where ((EAIMAIN.EXPORTACTID = :EXPORTACTID) and (EAIMAIN.STATE <> 10))
      into :SUMACTNET, :CUSTOMNET, :SUMTRWEIGHT
    do begin
      if ((:CUSTOMNET <> 0) or (SUMACTNET > 0))  then
        suspend;
    end
  end
end