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

FORWARDER_OUTACTSELECT

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_OUTACTSELECT(
    SEXPORTACTID INTEGER,
    AEXPORTACTID INTEGER,
    ORDERITEMID INTEGER,
    ISWEIGHT INTEGER)
RETURNS (
    ACTNUMBER VARCHAR(10),
    TRANSPORTNUMBER VARCHAR(31),
    INVOICEID INTEGER,
    PID INTEGER,
    EXPORTACTID INTEGER,
    PARINV INTEGER,
    EXPORTACTITEMID INTEGER,
    INVENTORYID INTEGER,
    PARENTITEMID INTEGER,
    OPERATIONID INTEGER,
    CROPFULLNAME VARCHAR(101),
    TRANSPORTWEIGHT BIGINT,
    CUSTOMWEIGHT BIGINT,
    HUMIDITY NUMERIC(9,2),
    DIRT NUMERIC(9,2),
    ISCHECKED INTEGER,
    STATE INTEGER,
    SCREENING NUMERIC(9,2),
    GRAINUNIT NUMERIC(9,2),
    REGDATE DATE,
    INVOICENUMBER VARCHAR(31))
AS
declare variable TEXPORTACTITEMID integer;
declare variable TOPERATONID integer;
declare variable TREGDATE date;
begin
  for
    select
        EAIM.EXPORTACTITEMID, EAIM.OPERATIONID,
        case
          when (EAIM.OPERATIONID = 17) then
            EXPORTACTS.REGDATE
          else IT.REGDATE
        end REGDATE/*,
        IT.WEIGHT*/
      from EXPORTACTITEMS EAIM
        left join EXPORTACTS on (EXPORTACTS.EXPORTACTID = EAIM.EXPORTACTID)
        left join IT on (IT.INVENTORYID = EAIM.INVENTORYID)
      where ((EAIM.EXPORTACTID = :SEXPORTACTID) and (EAIM.OPERATIONID in (1,17,45)))
    into :TEXPORTACTITEMID, :TOPERATONID, :TREGDATE
  do begin
    for
      select
          case
            when (EAI.OPERATIONID = 64) then
              (selecT ACTNUMBER from EXPORTACTS where EXPORTACTS.EXPORTACTID = EAISEC.EXPORTACTID)
            else EXPORTACTS.ACTNUMBER
          end ACTNUMBER,
          case
            when ((EAI.OPERATIONID in (1,17)) and ((EAI.WEIGHT > 0) or (EAI.WEIGHT is null))) then
              T1.TRANSPORTNUMBER
            else T2.TRANSPORTNUMBER
          end TRANSPORTNUMBER,
          case
            when (ITM.OPERATIONID in (1,17)) then
              ITM.INVOICEID
          end,
          case
            when (ITM.OPERATIONID in (1,17)) then
              INVOICES.INVOICENUMBER
          end,
          case
            when (EAI.WEIGHT is null) then
              1
            else 0
          end PID,
          case
            when (EAI.OPERATIONID = 64) then
              :SEXPORTACTID
            else EXPORTACTS.EXPORTACTID
          end,
          case
            when ((EAI.OPERATIONID in (1,17)) and ((EAI.WEIGHT > 0) or (EAI.WEIGHT is null))) then
              EAI.INVENTORYID
            else EAISEC.INVENTORYID
          end PARINV,
          EAI.EXPORTACTITEMID, EAI.INVENTORYID, EAI.PARENTITEMID,
          case
            when (ITM.OPERATIONID = 45) then
              1
            when (EAI.OPERATIONID = 64) then
              64
            else ITM.OPERATIONID
          end OPERATIONID,
          case
            when ((EAI.OPERATIONID in (1,17)) and (EAI.EXPORTACTID = :SEXPORTACTID)
                and ((EAI.WEIGHT > 0) or (EAI.WEIGHT is null))) then
              QUALITY_CROPFULLNAME.CROPFULLNAME
            when ((EAI.OPERATIONID = 70) and (ITM.STATE = 0)) then
              OPERATION.OPERATIONNAME
            when ((EAI.OPERATIONID = 22) and (ITM.STATE = 0)) then
              'Отходы 3 кат.'
            when ((EAI.OPERATIONID = 22) and (ITM.STATE = -10)) then
              'Отходы 3 кат. ПР'
            when ((EAI.OPERATIONID = 70) and (ITM.STATE = -10)) then
              OPERATION.OPERATIONNAME || ' ПР'
            when (EAI.OPERATIONID = 64) then
              'пред. списания'
            when (EAI.OPERATIONID = 17) then
              '*' || (select distinct
                          case
                            when (EAIT.OPERATIONID = 1) then
                              QUALITY_CROPFULLNAME.CROPFULLNAME
                            else OPERATION.OPERATIONNAME
                          end
                        from EXPORTACTITEMS EAIT
                          left join OPERATION on (OPERATION.OPERATIONID = EAIT.OPERATIONID)
                        where ((EAIT.INVENTORYID = EAI.INVENTORYID) and (EAIT.OPERATIONID in (1,22,70))))
          end CROPFULLNAME,
          case
             when (EAI.OPERATIONID = 64) then
               -EAI.WEIGHT
             else (FWO.TRANSPORTWEIGHT + coalesce(FWO.CUSTOMOUTNET, 0))
          end TRANSPORTWEIGHT,
          case
            when (EAI.OPERATIONID = 64) then
              -EAI.WEIGHT
            else FWO.CUSTOMOUTNET
          end CUSTOMWEIGHT,
          case
            when (EAI.OPERATIONID in (1,17)) then
              QS.HUMIDITY
            when (EAI.OPERATIONID = 64) then
              (select QUALITY_SELECT.HUMIDITY
                 from IT IT64
                   left join QUALITY_SELECT(IT64.LABQUALITYID) on (0=0)
                 where (IT64.INVENTORYID = EAISEC.INVENTORYID))
          end HUMIDITY,
          case
            when (EAI.OPERATIONID in (1,17)) then
              QS.DIRT
            when (EAI.OPERATIONID = 64) then
              (selecT QUALITY_SELECT.DIRT
                 from IT IT64
                   left join QUALITY_SELECT(IT64.LABQUALITYID) on (0=0)
                 where (IT64.INVENTORYID = EAISEC.INVENTORYID))
          end DIRT,
          QS.SCREENING, QS.GRAINUNIT,
          case
            when EAI.OPERATIONID = 64 then
              1
            else 0
          end as ISCHECKED,
          ITM.STATE, :TREGDATE
        from EXPORTACTITEMS EAI
          left join EXPORTACTS on (EXPORTACTS.EXPORTACTID = EAI.EXPORTACTID)
          left join EXPORTACTITEMS_VIEW EAISEC on (EAISEC.EXPORTACTITEMID = EAI.PARENTITEMID)
          left join FORWARDER_WEIGHTOUT(EAI.PARENTITEMID, :ORDERITEMID, EAI.INVENTORYID,
             EAI.OPERATIONID, EAI.EXPORTACTITEMID, :AEXPORTACTID, :ISWEIGHT) FWO on (0=0)
          left join IT ITM on (ITM.INVENTORYID = EAI.INVENTORYID)
          left join INVOICES on (INVOICES.INVOICEID = ITM.INVOICEID)
          left join OPERATION on (OPERATION.OPERATIONID = EAI.OPERATIONID)
          left join ITTRANSPORT T1 on (T1.INVENTORYID = EAI.INVENTORYID)
          left join ITTRANSPORT T2 on (T2.INVENTORYID = EAISEC.INVENTORYID)
          left join QUALITY on (QUALITY.QUALITYID = EXPORTACTS.QUALITYID)
          left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, null, 1) on (0=0)
          left join QUALITY_SELECT(ITM.LABQUALITYID) QS on (0=0)
  --    left join forwarder_QUALITY_neT(EXPORTACTS.EXPORTACTID) on (0=0)
        where ((EAI.EXPORTACTITEMID = :TEXPORTACTITEMID)
                or ((EAI.PARENTITEMID = :TEXPORTACTITEMID) and (not (EAI.OPERATIONID = 17))))
              and ((EXPORTACTS.STATE >= 0)
                   or ((EXPORTACTS.STATE = 10) and (EXPORTACTS.ORDERITEMID = :ORDERITEMID)))
              and ((EAI.OPERATIONID = 64 and EXPORTACTS.ORDERITEMID = :ORDERITEMID)
                   or (EAI.OPERATIONID in (1,17,22,45,70)))
              and ((EAISEC.STATE <> 10) or (EAISEC.STATE is null))
              and ((FWO.TRANSPORTWEIGHT + coalesce(FWO.CUSTOMOUTNET,0) <> 0)
                   or (FWO.CUSTOMOUTNET <> 0) or (EAI.OPERATIONID = 64))
      order by 1, 3, 2, 10, 4
      into :ACTNUMBER, :TRANSPORTNUMBER, :INVOICEID, :INVOICENUMBER, :PID, :EXPORTACTID,
           :PARINV, :EXPORTACTITEMID, :INVENTORYID, :PARENTITEMID, :OPERATIONID,
           :CROPFULLNAME, :TRANSPORTWEIGHT, :CUSTOMWEIGHT,
           :HUMIDITY, :DIRT, :SCREENING, :GRAINUNIT, :ISCHECKED, :STATE, :REGDATE
    do begin
      suspend;
    end
  end
end