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

FORWARDER_OUTACTREPVAGON

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_OUTACTREPVAGON(
    AEXPORTACTID INTEGER,
    AVTO INTEGER)
RETURNS (
    INVOICENET INTEGER,
    DEFICIT INTEGER,
    TRANSPORTNUMBER VARCHAR(31),
    NET INTEGER,
    STATIONFULLNAME VARCHAR(128),
    WEIGHT INTEGER,
    COMMENTS VARCHAR(300),
    EXPORTACTITEMID INTEGER,
    EXPORTACTID INTEGER,
    ACTNUMBER VARCHAR(10),
    REGDATE DATE,
    PROVIDERFULLNAME VARCHAR(100),
    TRANSPORTSUBTYPE INTEGER,
    HARVESTYEAR INTEGER)
AS
declare variable ORDERITEMID integer;
declare variable OPERATIONID integer;
declare variable TMP_WEIGHT integer;
declare variable TMP_OPERATIONNAME varchar(30);
declare variable WEXPORTACTITEMID integer;
declare variable PARENT integer;
begin
  select EXPORTACTS.ORDERITEMID, EXPORTACTS.OPERATIONID
    from EXPORTACTS
    where (EXPORTACTS.EXPORTACTID = :AEXPORTACTID)
  into :ORDERITEMID, :OPERATIONID;

  for
    select
        PROVIDER.CLIENTFULLNAME, EASEC.REGDATE, EASEC.ACTNUMBER,
        case
          when ((ITTRANSPORT.TRANSPORTTYPEID in (0,1)) and (:AVTO = 0)) then
            'авто'
          else ITTRANSPORT.TRANSPORTNUMBER
        end TRANSPORTNUMBER,
        case
          when ((ITTRANSPORT.TRANSPORTTYPEID = 2)  or (:AVTO = 1)) then
            EAI.EXPORTACTITEMID
        end EXPORTACTITEMID,
        case
          when ((ITTRANSPORT.TRANSPORTTYPEID = 2) or (:AVTO = 1)) then
            EAISEC.EXPORTACTITEMID
        end PARENT,
        STATIONS.STATIONFULLNAME, EASEC.EXPORTACTID, sum(EAI.WEIGHT),
        sum(coalesce(ITTRANSPORT.INVOICENET,0)), sum(coalesce(IT1.NET,0)),
        sum(case
              when (coalesce(ITTRANSPORT.INVOICENET,0) > 0) then
                (select sum(coalesce(IT.NET,0))
                   from IT
                   where (IT.INVOICEID = IT1.INVOICEID)
                ) - coalesce(ITTRANSPORT.INVOICENET,0)
              else 0
            end) DEFICIT,
        min(TRANSPORTTYPE.TRANSPORTSUBTYPE) TRANSPORTSUBTYPE,
        min(cast((select QUALITYDATA.QUALITYVALUE
                    from QUALITYDATA
                    where (QUALITYDATA.QUALITYID = IT1.LABQUALITYID)
                      and (QUALITYDATA.QUALITYTYPEID = 6)
                 ) as integer)
           ) HARVESTYEAR
      from EXPORTACTS EA
        left join EXPORTACTITEMS EAI on (EAI.EXPORTACTID = EA.EXPORTACTID)
        left join EXPORTACTITEMS EAISEC on (EAISEC.EXPORTACTITEMID = EAI.PARENTITEMID)
        left join EXPORTACTS EASEC on (EASEC.EXPORTACTID = EAISEC.EXPORTACTID)
        left join CLIENTS PROVIDER on (PROVIDER.CLIENTID = EASEC.PROVIDERID)
        left join IT IT1 on (IT1.INVENTORYID = EAISEC.INVENTORYID)
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = EAI.INVENTORYID)
        left join STATIONS on (STATIONS.STATIONID = EASEC.STATIONID)
        left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
      where (EA.ORDERITEMID = :ORDERITEMID)
    --  and EA.TransportSubTypeID = 2
            and (EAI.OPERATIONID in (5,17,1005)) and (EAI.WEIGHT > 0)
    group by PROVIDER.CLIENTFULLNAME, EASEC.REGDATE, EASEC.ACTNUMBER,
        TRANSPORTNUMBER, EXPORTACTITEMID, PARENT, STATIONS.STATIONFULLNAME,
        EASEC.EXPORTACTID
    into :PROVIDERFULLNAME, :REGDATE, :ACTNUMBER, :TRANSPORTNUMBER,:EXPORTACTITEMID,
         :PARENT, :STATIONFULLNAME, :EXPORTACTID, :WEIGHT, :INVOICENET, :NET,
         :DEFICIT, :TRANSPORTSUBTYPE, :HARVESTYEAR
  do begin
    COMMENTS = null;

    for
      select -EAIW.WEIGHT, EAIW.EXPORTACTITEMID,
          case
            when (EAIW.OPERATIONID = 64) then
              'Пред. списания'
            when ((IT2.OPERATIONID = 22) and (IT2.STATE = -10)) then
              OPERATION.OPERATIONNAME || '.'
            else OPERATION.OPERATIONNAME
          end
        from EXPORTACTITEMS EAIW
          left join IT IT2 on (IT2.INVENTORYID = EAIW.INVENTORYID)
          left join EXPORTACTITEMS EAIWSEC on (EAIWSEC.EXPORTACTITEMID = EAIW.PARENTITEMID)
          left join EXPORTACTITEMS EAIWTH on (EAIWTH.EXPORTACTITEMID = EAIWSEC.PARENTITEMID)
          left join EXPORTACTS EAS on (EAS.EXPORTACTID = EAIWTH.EXPORTACTID)
          left join OPERATION on (OPERATION.OPERATIONID = IT2.OPERATIONID)
        where (EAIW.EXPORTACTID = :AEXPORTACTID)
              and (((:OPERATIONID <> -17)
                    and (((:TRANSPORTNUMBER = 'авто') and (EAIWTH.EXPORTACTID = :EXPORTACTID))
                         or ((:TRANSPORTNUMBER <> 'авто') and (EAIW.PARENTITEMID = :EXPORTACTITEMID))
                         or ((EAIW.OPERATIONID = 64)
                             and (((EAIWSEC.EXPORTACTID = :EXPORTACTID) and (:TRANSPORTNUMBER = 'авто'))
                                  or ((:TRANSPORTNUMBER <> 'авто') and (EAIW.PARENTITEMID = :PARENT)))
                            )
                        )
                   )
                   or ((:OPERATIONID = -17)
                       and (((:TRANSPORTNUMBER = 'авто') and (EAIWTH.EXPORTACTID = :EXPORTACTID))
                            or ((:TRANSPORTNUMBER <> 'авто') and (EAIWSEC.PARENTITEMID = :PARENT))
                            or ((EAIW.OPERATIONID = 64)
                                and (((EAIWSEC.EXPORTACTID = :EXPORTACTID) and (:TRANSPORTNUMBER = 'авто'))
                                     or ((:TRANSPORTNUMBER <> 'авто') and (EAIW.PARENTITEMID = :PARENT)))
                               )
                           )
                      )
                  )
              and (EAIW.WEIGHT < 0)
      into :TMP_WEIGHT, :WEXPORTACTITEMID, :TMP_OPERATIONNAME
    do begin
      if (:COMMENTS is null) then
        COMMENTS = coalesce(TMP_OPERATIONNAME,'')||': '||coalesce(TMP_WEIGHT,'') ;
      else COMMENTS = coalesce(COMMENTS,'') || ascii_char(13) || coalesce(TMP_OPERATIONNAME,'') || ': ' || coalesce(TMP_WEIGHT,'');
    end

    suspend;

  end
end