"localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры Previous topic Chapter index Next topic

FORWARDER_OUTCOMEACT

 

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

Параметр

Тип

Описание

BEGINDATE

DATE

 

ENDDATE

DATE

 

AOWNERID

INTEGER

 

ACROPID

INTEGER

 

ACLASSID

INTEGER

 

ISCURRENT

INTEGER

 

APARTYDATE

DATE

 

PLUGINUNDERWORK

INTEGER

 

APROVIDERID

INTEGER

 

ASTATIONID

INTEGER

 

ASTORAGEID

INTEGER

 

TRSUBTYPE

INTEGER

 

 

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

Параметр

Тип

Описание

ACTDATE

DATE

 

ACTNUMBER

VARCHAR(10)

 

PROVIDERFULLNAME

VARCHAR(100)

 

REGDATE

DATE

 

CROPFULLNAME

VARCHAR(100)

 

OWNERFULLNAME

VARCHAR(100)

 

SUMWEIGHT

BIGINT

 

EXPORTACTID

INTEGER

 

OWNERID

INTEGER

 

CROPID

INTEGER

 

CLASSID

INTEGER

 

PROVIDERID

INTEGER

 

STORAGEID

INTEGER

 

STATE

INTEGER

 

ORDERITEMID

INTEGER

 

ORDERNUMBER

VARCHAR(10)

 

ORDERLOADID

INTEGER

 

STATIONID

INTEGER

 

WASTENET

INTEGER

 

ORDERNET

INTEGER

 

SAMPLENET

INTEGER

 

WASTE02NET

INTEGER

 

ALLNET

INTEGER

 

TRANSPORTSUBTYPEID

INTEGER

 

SHIPNAME

VARCHAR(50)

 

STATIONNAME

VARCHAR(30)

 

TYPESTATION

VARCHAR(10)

 

ISSHIP

VARCHAR(10)

 

EXPORTACTIDCALC

INTEGER

 

PREWASTENET

INTEGER

 

STORAGENAME

VARCHAR(100)

 

PARTYID

INTEGER

 

OPERATIONID

INTEGER

 

CONTRACTID

INTEGER

 

 

Описание

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

 

Определение

CREATE PROCEDURE FORWARDER_OUTCOMEACT(
    BEGINDATE DATE,
    ENDDATE DATE,
    AOWNERID INTEGER,
    ACROPID INTEGER,
    ACLASSID INTEGER,
    ISCURRENT INTEGER,
    APARTYDATE DATE,
    PLUGINUNDERWORK INTEGER,
    APROVIDERID INTEGER,
    ASTATIONID INTEGER,
    ASTORAGEID INTEGER,
    TRSUBTYPE INTEGER)
RETURNS (
    ACTDATE DATE,
    ACTNUMBER VARCHAR(10),
    PROVIDERFULLNAME VARCHAR(100),
    REGDATE DATE,
    CROPFULLNAME VARCHAR(100),
    OWNERFULLNAME VARCHAR(100),
    SUMWEIGHT BIGINT,
    EXPORTACTID INTEGER,
    OWNERID INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER,
    PROVIDERID INTEGER,
    STORAGEID INTEGER,
    STATE INTEGER,
    ORDERITEMID INTEGER,
    ORDERNUMBER VARCHAR(10),
    ORDERLOADID INTEGER,
    STATIONID INTEGER,
    WASTENET INTEGER,
    ORDERNET INTEGER,
    SAMPLENET INTEGER,
    WASTE02NET INTEGER,
    ALLNET INTEGER,
    TRANSPORTSUBTYPEID INTEGER,
    SHIPNAME VARCHAR(50),
    STATIONNAME VARCHAR(30),
    TYPESTATION VARCHAR(10),
    ISSHIP VARCHAR(10),
    EXPORTACTIDCALC INTEGER,
    PREWASTENET INTEGER,
    STORAGENAME VARCHAR(100),
    PARTYID INTEGER,
    OPERATIONID INTEGER,
    CONTRACTID INTEGER)
AS
begin
  for
    select EXPORTACTS.ACTDATE, EXPORTACTS.EXPORTACTID, EXPORTACTS.ACTNUMBER,
        EXPORTACTS.REGDATE, EXPORTACTS.OWNERID, EXPORTACTS.PROVIDERID, EXPORTACTS.STORAGEID,
        EXPORTACTS.ORDERITEMID, EXPORTACTS.STATE, QUALITY.CROPID, QUALITY.CLASSID,
        QUALITY_CROPFULLNAME_BYID.CROPFULLNAME, OWNER.CLIENTFULLNAME OWNERFULLNAME,
        PROVIDER.CLIENTFULLNAME PROVIDERFULLNAME, ORDERLOAD.ORDERNUMBER, ORDERLOAD.ORDERLOADID,
        EXPORTACTS.STATIONID, EXPORTACTS.TRANSPORTSUBTYPEID, ORDERLOADITEMS.NET ORDERNET,
        case
          when (EXPORTACTS.TRANSPORTSUBTYPEID = 2) then
            (select first 1 SHIPS.SHIPNAME
               from IT ITSH
                 left join IT ITS on ((ITS.INVENTORYID = ITSH.RELATIONID) and (ITS.PARENTID = -1))
                 left join SHIPS on (SHIPS.SHIPID = ITS.RELATIONID)
               where (ITSH.ORDERLOADITEMID = EXPORTACTS.ORDERITEMID)
                 and (ITSH.OPERATIONID in (5,1005)) and (not (ITSH.STATE in (-1,-100))))
        end,
        case
          when (EXPORTACTS.TRANSPORTSUBTYPEID = 1) then
            ST.STATIONNAME
          when (EXPORTACTS.TRANSPORTSUBTYPEID = 2) then
            (select first 1 ST2.STATIONNAME
               from IT ITSH
                 left join IT ITS on ((ITS.INVENTORYID = ITSH.RELATIONID) and (ITS.PARENTID = -1))
                 left join INVOICES on (INVOICES.INVOICEID = ITS.INVOICEID)
                 left join STATIONS ST2 on (ST2.STATIONID = INVOICES.STATIONID)
               where (ITSH.ORDERLOADITEMID = EXPORTACTS.ORDERITEMID)
                 and (ITSH.OPERATIONID in (5,1005)) and (not (ITSH.STATE in (-1,-100))))
        end STATIONNAME,
        case
          when (EXPORTACTS.TRANSPORTSUBTYPEID = 1) then
            'Станция:'
          when (EXPORTACTS.TRANSPORTSUBTYPEID = 2) then
            'Порт:'
        end TYPESTATION,
        case
          when (EXPORTACTS.TRANSPORTSUBTYPEID = 2) then
            'Судно:'
        end ISSHIP,
        EXPORTACTQUALITY.EXPORTACTID EXPORTACTIDCALC, STORAGE.STORAGESHORTNAME,
        sum(case
              when (EAI.WEIGHT > 0) then
                EAI.WEIGHT
            end) SUMWEIGHT,
        sum(case
              when ((EAI.WEIGHT < 0) and (ITM.OPERATIONID = 22) and (ITM.STATE = 0)) then
                -EAI.WEIGHT
            end) WASTENET,
        sum(case
              when ((EAI.WEIGHT < 0) and (ITM.OPERATIONID = 22) and (ITM.STATE = -10)) then
                -EAI.WEIGHT
            end) PREWASTENET,
        sum(case
              when ((EAI.WEIGHT < 0) and (ITM.OPERATIONID = 70))
                then -EAI.WEIGHT
            end) SAMPLENET,
        sum(case
              when ((EAI.WEIGHT < 0) and (EAI.OPERATIONID = 64))
                then -EAI.WEIGHT
            end) WASTE02NET,
        sum(case
              when (EAI.WEIGHT < 0) then
                -EAI.WEIGHT
              else EAI.WEIGHT
            end) ALLNET,
        min(ORDERLOADQUALITY.PARTYID), min(ORDERLOAD.OPERATIONID),
        min(ORDERLOAD.CONTRACTID)
      from EXPORTACTS
        left join CLIENTS OWNER on ((case
                                       when ((EXPORTACTS.TRANSPORTSUBTYPEID = 2) and (:PLUGINUNDERWORK = 5)) then
                                         EXPORTACTS.OWNERID
                                       else EXPORTACTS.PROVIDERID
                                     end) = OWNER.CLIENTID)
        left join CLIENTS PROVIDER on ((case
                                          when ((EXPORTACTS.TRANSPORTSUBTYPEID = 2) and (:PLUGINUNDERWORK = 5)) then
                                            EXPORTACTS.PROVIDERID
                                          else EXPORTACTS.OWNERID
                                        end) = PROVIDER.CLIENTID)
        left join STORAGE on (STORAGE.STORAGEID = EXPORTACTS.STORAGEID)
        left join STATIONS ST on ((case
                                     when (EXPORTACTS.TRANSPORTSUBTYPEID = 1) then
                                       EXPORTACTS.STATIONID
                                   end) = ST.STATIONID)
        left join EXPORTACTITEMS EAI on (EAI.EXPORTACTID = EXPORTACTS.EXPORTACTID)
        left join IT ITM on (ITM.INVENTORYID = EAI.INVENTORYID)
        join GETWORKPARTIES(:ISCURRENT, :APARTYDATE,
                            case
                              when (coalesce(:CROPID,-1) = -1) then
                                null
                              else coalesce(:CROPID,-1)
                            end,
                            case
                              when (coalesce(:CLASSID,-1) = -1) then
                                null
                              else coalesce(:CLASSID,-1)
                            end,
                            case
                              when (coalesce(:STORAGEID,-1) = -1) then
                                null
                              else coalesce(:STORAGEID,-1)
                            end) on (GETWORKPARTIES.PARTYID = ITM.PARTYID)
        left join QUALITY on (QUALITY.QUALITYID = EXPORTACTS.QUALITYID)
        left join QUALITY_CROPFULLNAME_BYID (EXPORTACTS.QUALITYID, 1) on (0=0)
        left join ORDERLOADITEMS on (ORDERLOADITEMS.ORDERLOADITEMID = EXPORTACTS.ORDERITEMID)
        left join ORDERLOAD on (ORDERLOAD.ORDERLOADID = ORDERLOADITEMS.ORDERLOADID)
        left join EXPORTACTQUALITY on (EXPORTACTQUALITY.EXPORTACTID = EXPORTACTS.EXPORTACTID)
        left join QUALITY ORDERLOADQUALITY on (ORDERLOADQUALITY.QUALITYID = ORDERLOADITEMS.QUALITYID)
      where (EXPORTACTS.ACTDATE between :BEGINDATE and :ENDDATE)
        and (EXPORTACTS.OPERATIONID in (5,1005)) and (EXPORTACTS.STATE <> -1) and (EAI.OPERATIONID <> -64)
        and ((EXPORTACTS.OWNERID = :AOWNERID) or (coalesce(:AOWNERID,-1) = -1))
        and ((EXPORTACTS.PROVIDERID = :APROVIDERID) or (coalesce(:APROVIDERID,-1) = -1))
        and ((EXPORTACTS.STATIONID = :ASTATIONID) or (coalesce(:ASTATIONID,-1) = -1))
        and ((EXPORTACTS.STORAGEID = :ASTORAGEID) or (coalesce(:ASTORAGEID,-1) = -1))
        and (EXPORTACTS.EXPORTACTPARENTID is null)
        and ((EXPORTACTS.TRANSPORTSUBTYPEID = :TRSUBTYPE) or (coalesce(:TRSUBTYPE,-1) = -1))
        and ((QUALITY.CROPID = :ACROPID) or (coalesce(:ACROPID,-1) = -1))
        and ((QUALITY.CLASSID = :ACLASSID) or (coalesce(:ACLASSID,-1) = -1))
    group by EXPORTACTS.ACTDATE, EXPORTACTS.REGDATE, EXPORTACTS.EXPORTACTID,
        EXPORTACTS.ACTNUMBER, EXPORTACTS.OWNERID, EXPORTACTS.PROVIDERID, EXPORTACTS.STORAGEID,
        EXPORTACTS.TRANSPORTSUBTYPEID, EXPORTACTS.ORDERITEMID, EXPORTACTS.STATE, QUALITY.CROPID,
        QUALITY.CLASSID, QUALITY_CROPFULLNAME_BYID.CROPFULLNAME, OWNER.CLIENTFULLNAME,
        PROVIDER.CLIENTFULLNAME, ORDERLOAD.ORDERNUMBER, ORDERLOAD.ORDERLOADID,
        EXPORTACTS.STATIONID, ORDERLOADITEMS.NET, EXPORTACTQUALITY.EXPORTACTID,
        ST.STATIONNAME, STORAGE.STORAGESHORTNAME
    union
      select ORDERLOAD.BEGINDATE, cast (null as integer), cast (null as varchar(10)),
          cast (null as date), ORDERLOAD.OWNERID, ORDERLOAD.PROVIDERID, ORDERLOAD.STORAGEID,
          ORDERLOADITEMS.ORDERLOADITEMID, cast(0 as integer) STATE, QUALITY.CROPID,
          QUALITY.CLASSID, QUALITY_CROPFULLNAME.CROPFULLNAME, OWNER.CLIENTFULLNAME OWNERFULLNAME,
          PROVIDER.CLIENTFULLNAME PROVIDERFULLNAME, ORDERLOAD.ORDERNUMBER, ORDERLOAD.ORDERLOADID,
          ORDERLOAD.DESTINATIONPOINTID, ORDERLOAD.TRANSPORTSUBTYPEID, ORDERLOADITEMS.NET ORDERNET,
          SHIPS.SHIPNAME, STATIONS.STATIONNAME,
          case
            when (ORDERLOAD.TRANSPORTSUBTYPEID = 2) then
              'Порт:'
            when (ORDERLOAD.TRANSPORTSUBTYPEID = 1) then
              'Станция:'
          end as TYPESTATION,
          case
            when (ORDERLOAD.TRANSPORTSUBTYPEID = 2) then
              'Судно:'
          end ISSHIP,
          cast(null as integer) EXPORTACTIDCALC, STORAGE.STORAGESHORTNAME,
          sum(cast(null as integer)) SUMWEIGHT, sum(cast(null as integer)) WASTENET,
          sum(cast(null as integer)) PREWASTENET, sum(cast(null as integer)) SAMPLENET,
          sum(cast(null as integer)) WASTE02NET, sum(cast(null as integer)) ALLNET,
          min(QUALITY.PARTYID), min(ORDERLOAD.OPERATIONID), min(ORDERLOAD.CONTRACTID)
        from ORDERLOAD
          left join CLIENTS OWNER on ((case
                                         when ((ORDERLOAD.TRANSPORTSUBTYPEID = 2) and (:PLUGINUNDERWORK = 5)) then
                                           ORDERLOAD.OWNERID
                                         else ORDERLOAD.PROVIDERID
                                       end) = OWNER.CLIENTID)
          left join CLIENTS PROVIDER on ((case
                                            when ((ORDERLOAD.TRANSPORTSUBTYPEID = 2) and (:PLUGINUNDERWORK = 5)) then
                                              ORDERLOAD.PROVIDERID
                                            else ORDERLOAD.OWNERID
                                          end) = PROVIDER.CLIENTID)
          left join STORAGE on (STORAGE.STORAGEID = ORDERLOAD.STORAGEID)
          left join ORDERLOADITEMS on (ORDERLOADITEMS.ORDERLOADID = ORDERLOAD.ORDERLOADID)
          left join QUALITY on (QUALITY.QUALITYID = ORDERLOADITEMS.QUALITYID)
          left join QUALITY_CROPFULLNAME (QUALITY.CROPID, QUALITY.CLASSID, null, 1) on (0=0)
          left join IT ITSH on ((ITSH.ORDERLOADITEMID = ORDERLOADITEMS.ORDERLOADITEMID) and (ITSH.OPERATIONID in (5,1005)) and (ITSH.STATE not in (0,-1,-5,-100)))
          left join GETWORKPARTIES(:ISCURRENT, :APARTYDATE,
                                   case
                                     when (coalesce(:CROPID,-1) = -1) then
                                       null
                                     else coalesce(:CROPID,-1)
                                   end,
                                   case
                                     when (coalesce(:CLASSID,-1) = -1) then
                                       null
                                     else coalesce(:CLASSID,-1)
                                   end,
                                   case
                                     when (coalesce(:STORAGEID,-1) = -1) then
                                       null
                                     else coalesce(:STORAGEID,-1)
                                   end) on (GETWORKPARTIES.PARTYID = ITSH.PARTYID)
          left join IT ITS on ((ITS.INVENTORYID = ITSH.RELATIONID) and (ITS.PARENTID = -1))
          left join SHIPS on (SHIPS.SHIPID = ITS.RELATIONID)
          left join INVOICES on (INVOICES.INVOICEID = ITS.INVOICEID)
          left join STATIONS on ((case
                                    when ORDERLOAD.TRANSPORTSUBTYPEID = 2
                                      then INVOICES.STATIONID
                                    when ORDERLOAD.TRANSPORTSUBTYPEID = 1
                                      then ORDERLOAD.DESTINATIONPOINTID
                                  end) = STATIONS.STATIONID)
          left join CROP on (CROP.CROPID = QUALITY.CROPID)
        where (ORDERLOAD.BEGINDATE between :BEGINDATE and :ENDDATE)
          and (not (exists (select EXPORTACTID from EXPORTACTS
                              where (EXPORTACTS.ORDERITEMID = ORDERLOADITEMS.ORDERLOADITEMID)
                                and (EXPORTACTS.STATE > 0))))
          and ((ORDERLOAD.OWNERID = :AOWNERID) or (coalesce(:AOWNERID,-1) = -1))
          and ((ORDERLOAD.PROVIDERID = :APROVIDERID) or (coalesce(:APROVIDERID,-1) = -1))
          and ((ORDERLOAD.TRANSPORTSUBTYPEID = :TRSUBTYPE) or (coalesce(:TRSUBTYPE,-1) = -1))
          and ((STATIONS.STATIONID = :ASTATIONID) or (coalesce(:ASTATIONID,-1) = -1))
          and ((ORDERLOAD.STORAGEID = :ASTORAGEID) or (coalesce(:ASTORAGEID,-1) = -1))
          and (ORDERLOAD.STATE > 0) and (ORDERLOAD.OPERATIONID in (5,1005)) and (CROP.CROPTYPEID <> 3)
          and ((QUALITY.CROPID = :ACROPID) or (coalesce(:ACROPID,-1) = -1))
          and ((QUALITY.CLASSID = :ACLASSID) or (coalesce(:ACLASSID,-1) = -1))
      group by ORDERLOAD.BEGINDATE, ORDERLOAD.OWNERID, ORDERLOAD.PROVIDERID,
          ORDERLOAD.STORAGEID, ORDERLOADITEMS.ORDERLOADITEMID, ORDERLOAD.STATE,
          QUALITY.CROPID, QUALITY.CLASSID, QUALITY_CROPFULLNAME.CROPFULLNAME,
          OWNER.CLIENTFULLNAME, PROVIDER.CLIENTFULLNAME, ORDERLOAD.ORDERNUMBER,
          ORDERLOAD.ORDERLOADID, ORDERLOAD.DESTINATIONPOINTID, ORDERLOAD.TRANSPORTSUBTYPEID,
          ORDERLOADITEMS.NET, SHIPS.SHIPNAME, STATIONS.STATIONNAME, STORAGE.STORAGESHORTNAME,
          STATIONS.REGIONID
    order by 1, 15, 3, 12, 13
    into :ACTDATE, :EXPORTACTID, :ACTNUMBER, :REGDATE, :OWNERID, :PROVIDERID,
         :STORAGEID, :ORDERITEMID, :STATE, :CROPID, :CLASSID, :CROPFULLNAME,
         :OWNERFULLNAME, :PROVIDERFULLNAME, :ORDERNUMBER, :ORDERLOADID,
         :STATIONID, :TRANSPORTSUBTYPEID, :ORDERNET, :SHIPNAME, :STATIONNAME,
         :TYPESTATION, :ISSHIP, :EXPORTACTIDCALC, :STORAGENAME, :SUMWEIGHT,
         :WASTENET, :PREWASTENET, :SAMPLENET, :WASTE02NET, :ALLNET, :PARTYID,
         :OPERATIONID, :CONTRACTID
  do begin
    suspend;
  end
end

 

     Previous topic Chapter index Next topic