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

FORWARDER_OUTACTREP

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_OUTACTREP(
    AEXPORTACTID INTEGER,
    AVTO INTEGER)
RETURNS (
    ACTDATE DATE,
    ACTINCOMENUMBER VARCHAR(10),
    TRANSPORTNUMBER VARCHAR(31),
    INVOICENUMBER VARCHAR(31),
    STATIONFULLNAME VARCHAR(128),
    WEIGHT INTEGER,
    COMMENTS VARCHAR(300),
    EXPORTACTITEMID INTEGER,
    EXPORTACTID INTEGER,
    TRANSPORTSUBTYPEID INTEGER,
    PROVIDERFULLNAME VARCHAR(100),
    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
        EASEC.ACTDATE, EAISEC.EXPORTACTITEMID, EASEC.TRANSPORTSUBTYPEID,
        EASEC.EXPORTACTID, PROVIDER.CLIENTFULLNAME, EASEC.ACTNUMBER ACTINCOMENUMBER,
        case
          when (:AVTO = 0) then
            case
              when (EASEC.TRANSPORTSUBTYPEID = 0) then
                'авто'
              when (EASEC.TRANSPORTSUBTYPEID = 1) then
                ITTRANSPORT.TRANSPORTNUMBER
            end
          ELSE ITTRANSPORT.TRANSPORTNUMBER
        end TRANSPORTNUMBER,
        case
          when ((:AVTO = 1) or (EASEC.TRANSPORTSUBTYPEID = 1)) then
            INVOICES.INVOICENUMBER
          else cast(null as varchar(31))
        end INVOICENUMBER,
        cast((select QUALITYDATA.QUALITYVALUE
                from QUALITYDATA
                where (QUALITYDATA.QUALITYID = IT1.LABQUALITYID)
                  and (QUALITYDATA.QUALITYTYPEID = 6)
             ) as integer) HARVESTYEAR,
        STATIONS.STATIONFULLNAME, EAI.EXPORTACTITEMID, EAI.WEIGHT
      from EXPORTACTS EA
        left join EXPORTACTITEMS EAI on (EAI.EXPORTACTID = EA.EXPORTACTID)
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = EAI.INVENTORYID)
        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 INVOICES on (INVOICES.INVOICEID = IT1.INVOICEID)
        left join STATIONS on (STATIONS.STATIONID = EASEC.STATIONID)
      where (EA.ORDERITEMID = :ORDERITEMID) and (EAI.WEIGHT > 0)
    order by PROVIDER.CLIENTFULLNAME, ACTINCOMENUMBER
    into :ACTDATE, :PARENT, :TRANSPORTSUBTYPEID, :EXPORTACTID, :PROVIDERFULLNAME,
         :ACTINCOMENUMBER, :TRANSPORTNUMBER, :INVOICENUMBER, :HARVESTYEAR,
         :STATIONFULLNAME, :EXPORTACTITEMID, :WEIGHT
  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