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

FORWARDER_REGRID

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_REGRID(
    AOWNERID INTEGER,
    ACROPID INTEGER,
    ACLASSID INTEGER,
    AORDERLOADITEMID INTEGER,
    APROVIDERID INTEGER,
    ASTORAGEID INTEGER,
    ISCURRENT INTEGER,
    APARTYDATE DATE,
    ISWEIGHT SMALLINT)
RETURNS (
    ACTDATE DATE,
    ACTNUMBER VARCHAR(10),
    EXPORTACTID INTEGER,
    TRANSPORTSUBTYPEID INTEGER,
    READONLYREC INTEGER,
    ISCHECKED INTEGER,
    SUMACTNET BIGINT,
    CUSTOMRENET BIGINT,
    SUMTRWEIGHT BIGINT,
    PROVIDERFULLNAME VARCHAR(30),
    PARTYID INTEGER,
    OUTCOMEWEIGHT INTEGER)
AS
declare variable MINDATE date;
declare variable OUTEXPORTACTID integer;
begin
  select first 1 EXPORTACTS.ACTDATE
    from EXPORTACTS
      left join EXPORTACTITEMS on (EXPORTACTITEMS.EXPORTACTID = EXPORTACTS.EXPORTACTID)
      left join IT on (IT.INVENTORYID = EXPORTACTITEMS.INVENTORYID)
      join GETWORKPARTIES(:ISCURRENT, :APARTYDATE, :ACROPID, :ACLASSID, :ASTORAGEID) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
      left join QUALITY on (QUALITY.QUALITYID = EXPORTACTS.QUALITYID)
    where (EXPORTACTS.OWNERID = :AOWNERID) and (EXPORTACTS.OPERATIONID in (1,17,45))
      and (EXPORTACTS.STATE <> -1) and (QUALITY.CROPID = :ACROPID)
      and (QUALITY.CLASSID = :ACLASSID)
  order by 1
  into :MINDATE;

  select EXPORTACTS.EXPORTACTID
    from EXPORTACTS
    where ((EXPORTACTS.ORDERITEMID = :AORDERLOADITEMID) and (EXPORTACTS.OPERATIONID = -17))
  into :OUTEXPORTACTID;

  for
    select
        EA.ACTDATE, min(PROVIDERS.CLIENTNAME) PROVIDERFULLNAME, EA.ACTNUMBER,
        EA.EXPORTACTID, EA.TRANSPORTSUBTYPEID, 0 READONLYREC, 0 ISCHECKED,
        min(IT.PARTYID) PARTYID
      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)
        join GETWORKPARTIES(:ISCURRENT, :APARTYDATE, :ACROPID, :ACLASSID, :ASTORAGEID) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
      where (EA.REGDATE >= :MINDATE) and (EA.OWNERID = :AOWNERID)
        and (EA.OPERATIONID in (1,17)) and (EA.STATE = 0)
        and (QUALITY.CROPID = :ACROPID) and (QUALITY.CLASSID = :ACLASSID)
        and ((IT.STORAGEID = coalesce(:ASTORAGEID,-1)) or (coalesce(:ASTORAGEID,-1) = -1))
        and ((not ((EA.ORDERITEMID = :AORDERLOADITEMID) and (EA.OWNERID = :APROVIDERID)))
             or (EA.ORDERITEMID is null))
    group by EA.ACTDATE, EA.PROVIDERID, EA.ACTNUMBER, EA.EXPORTACTID, EA.TRANSPORTSUBTYPEID
    into :ACTDATE, :PROVIDERFULLNAME, :ACTNUMBER, :EXPORTACTID,
         :TRANSPORTSUBTYPEID, :READONLYREC, :ISCHECKED, :PARTYID
  do begin
    for
      select
          sum(FORWARDER_WEIGHTITEM_STORAGE.RESTWEIGHT) SUMACTNET,
          sum(FORWARDER_WEIGHTITEM_STORAGE.REWEIGHT) CUSTOMRENET,
          sum(FORWARDER_WEIGHTITEM_STORAGE.INCOMEWEIGHT) SUMTRWEIGHT,
          sum(coalesce(FORWARDER_WEIGHTITEM_STORAGE.OUTCOMEWEIGHT,0)) OUTCOMEWEIGHT
        from EXPORTACTITEMS_VIEW EAIMAIN
          left join FORWARDER_WEIGHTITEM_STORAGE(:AORDERLOADITEMID, EAIMAIN.EXPORTACTITEMID,
              EAIMAIN.EXPORTACTID, :ISWEIGHT) on (0=0)
        where ((EAIMAIN.EXPORTACTID = :EXPORTACTID)
               or ((EAIMAIN.EXPORTACTID = :OUTEXPORTACTID)
                   and (EAIMAIN.OPERATIONID not in (-64,17))
                   and ((select EXPORTACTITEMS.EXPORTACTID
                           from EXPORTACTITEMS
                           where (EXPORTACTITEMS.EXPORTACTITEMID = EAIMAIN.PARENTITEMID)) = :EXPORTACTID)))
              and (EAIMAIN.OPERATIONID in (1,17,45)) and (EAIMAIN.STATE <> 10)
      into :SUMACTNET, :CUSTOMRENET, :SUMTRWEIGHT, :OUTCOMEWEIGHT
    do begin
      if (((:CUSTOMRENET <> 0) or (:SUMACTNET > 0)) and (SUMTRWEIGHT >= OUTCOMEWEIGHT)) then
        suspend;
    end
  end
end