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

FORWARDER_FIRMGROUPREP

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_FIRMGROUPREP(
    BEGINDATE DATE,
    ENDDATE DATE,
    OWNERID INTEGER,
    CROPID INTEGER,
    ACLASSID INTEGER,
    ISCURRENT INTEGER,
    APARTYDATE DATE)
RETURNS (
    ACTNUMBER VARCHAR(10),
    ACTDATE DATE,
    TRANSPORTNUMBER VARCHAR(50),
    INVOICENUMBER VARCHAR(31),
    ORDERNUMBER VARCHAR(10),
    PROVIDERFULLNAME VARCHAR(100),
    STATIONNAME VARCHAR(30),
    INVOICENET INTEGER,
    INCOMENET INTEGER,
    DEFICITM BIGINT,
    OUTNET BIGINT,
    OPERATIONNAME VARCHAR(36),
    OWNERFULLNAME VARCHAR(100),
    CROPFULLNAME VARCHAR(100),
    REGDATE DATE,
    PID INTEGER,
    CLASSID INTEGER,
    INVENTORYID INTEGER)
AS
BEGIN
  FOR
    select  distinct
      ITm.RegDate,
      case ITm.OperationID
        when 1 then EA.ActNumber
        when 5 then EAs.ActNumber
        when 45 then EA.ActNumber
        when 46 then EA.ActNumber
        when 17 then
                  case when ITm.Net > 0 then  EAs.ActNumber
            else EAf.ActNumber
          end
      end ActNumber,
      case ITm.OperationID
        when 1 then EA.ActDate
        when 5 then EAs.ActDate
        when 45 then EA.ActDate
        when 46 then EA.ActDate
        when 17 then
          case when ITm.Net > 0 then  EAs.ActDate
            else EAf.ActDate
          end
      end ActDate,
      case
        when ITm.operationid = 5 and ITTransport.TransportTypeID = 3
        then (select Ships.ShipName
          from IT ITship
          left join IT ITpship on (ITship.RelationID = ITpship.InventoryID and ITpship.ParentID = -1)
          left join Ships on (ITpship.RelationID = Ships.ShipID)
         where
          ITship.orderloaditemid = OLI.orderloaditemid
           and ITship.operationid = 5 and not ITship.State in (-1,-100))
        when ITm.operationid = 64 then ''
        else case
          when ITm.OperationID = 17 then
           case when EAs.TransportSubTypeID = 0 then 'авто'
                when EAs.TransportSubTypeID = 1 then 'жд'
           end
          else case
            when ITTransport.TransportTypeID = 2 then ITTransport.TransportNumber
            when ITTransport.TransportTypeID in (0,1) then 'авто'
          end
        end
      end TransportNumber,
      case
        when ITm.OperationID in (5,22,70)  then InvoicesM.InvoiceNumber
        when ITm.OperationID = 1 and ittransport.transporttypeid = 2 then InvoicesM.InvoiceNumber
        when ITm.OperationID = 17 then InvoicesS.InvoiceNumber
      end InvoiceNumber,
      OL.OrderNumber,
      case
        when ITm.OperationID  = 64 then 'сдаточный акт №'||EA.ActNumber
        else Provider.ClientFullName
      end as ProviderFullName,
      Stations.StationName,
      (case ITm.OperationID
            when 1 then ITTransport.InvoiceNet
          end) as InvoiceNet,
      (case
            when ITm.OperationID in (1,45) and ITm.Net > 0  then ITm.Net
            when ITm.OperationID = 17 and ITm.Net > 0  then
             (select sum(EAI17.Weight) from ExportActItems EAI17 where EAI17.operationid = 17
               and EAI17.ExportActID = EAs.ExportActID)
          end) as IncomeNet,
      (case ITm.OperationID
            when 1 then coalesce(ITm.Net,0) - coalesce(ITTransport.InvoiceNet,0)
          end) as DeficITm,
      (case
        when ITm.OperationID = 5 then  ITm.Net
        when ITm.OperationID in (22,70)  then ITm.Net
        when ITm.OperationID in (64,65)  then -ITm.Weight
        when ITm.OperationID = 17 and ITm.Net < 0  then
         (select sum(EAI17.Weight) from ExportActs Ea17
            left join ExportActItems EAI17 on (Ea17.ExportActID = EAI17.ExportActID and EAI17.OperationID = 17)
             where EA17.OrderItemID = ITm.OrderLoadItemID and EA17.ExportActID = EAs.ExportActID)
      end) as OutNet,
      case
        when ITm.OperationID  = 5 and OL.TransportSubTypeID  = 2 then Operation.OperationName||' судна'
        when ITm.OperationID  = 22 then 'Отходы 3 кат.'
        else Operation.OperationName
      end OperationName,
      Owner.ClientFullName as OwnerFullName,
      QualIty_CropFullName.CropFullName,
      case
        when ITm.OperationID in (1,17) and ITm.net > 0 then 1
        when ITm.OperationID = 5 then 2
        else 3
      end PID,
      ITm.InventoryID,
      Q1.ClassID
    from IT ITm
      join getworkparties(:IsCurrent,:APartyDate,
        case when :CropID = -1 then null else :CropID end,
        case when :classid = -1 then null else :classid end, 1)
          on (ITm.PartyID = getworkparties.PartyID)
      left join Clients Owner on (ITm.OwnerID = Owner.ClientID)
      left join Clients Provider on (ITm.ProviderID = Provider.ClientID)
      left join Quality Q1 on (ITm.SamplerQualityID = Q1.QualityID)
      left join ExportActItems EAI on (ITm.InventoryID = EAI.InventoryID
        and (EAI.OperationID = ITm.OperationID or (ITm.OperationID in (45,46) and EAI.OperationID in (1,5))))
      left join ExportActs EA on (ITm.OperationID in (1,45,46,22,70,64) and EAI.ExportActID = EA.ExportActID and EA.State in (0,1))
      left join ExportActs EAs on (ITm.OperationID in (5,17) and EAs.OrderItemID = ITm.OrderLoadItemID and EAs.State in (0,1))
      left join ExportActItems EAI64 on (EAI.ParentItemID = EAI64.ExportActItemID)
      left join ExportActs EAt on (EAI64.ExportActID = EAt.ExportActID)
      left join ExportActItems EAIt on (ITm.OperationID = 17 and EAt.ExportActID = EAIt.ExportActID and EAIt.OperationID = 17)
      left join ExportActItems EAIs on (ITm.OperationID = 17 and EAIt.ParentItemID = EAIs.ExportActItemID)
      left join ExportActs EAf on (ITm.OperationID = 17 and EAs.exportactparentid = EAf.ExportActID)
      left join ITTransport on (ITm.InventoryID = ITTransport.InventoryID)
      left join IT ITs on (ITm.OperationID = 17 and EAIs.InventoryID = ITs.InventoryID)
      left join Invoices InvoicesS on (ITs.InvoiceID = InvoicesS.InvoiceID)
      left join Invoices InvoicesM on (ITm.InvoiceID = InvoicesM.InvoiceID)
      left join OrderLoadItems OLI on (ITm.OperationID in (17,5) and ITm.OrderLoadItemID = OLI.OrderLoadItemID)
      left join OrderLoad OL on (OLI.OrderLoadID = OL.OrderLoadID)
      left join Operation on (ITm.OperationID = Operation.OperationID)
      left join Quality_CropFullName(Q1.CropID,Q1.ClassID,null,1)  on (0 = 0)
      left join Stations on ((case ITm.OperationID
                               when 5 then EAs.StationID
                               when 17  then EAt.StationID
                               else InvoicesM.StationID end) = Stations.StationID)
      left join Crop on (Q1.CropID = Crop.CropID)
      left join Quality WasteName on (Itm.OperationParam = WasteName.QualityID)
    where
   --  ITm.RegDate >= Parties.BeginDate
    --and
    (ITm.RegDate between :BeginDate and :EndDate
            or (ITm.RegDate <= :EndDate
                and :BeginDate is null ))
      and ((ITm.OperationID = 1 and not EA.ExportActID is null)
            or (ITm.OperationID in (45,46) and not EA.ExportActID is null)
            or (ITm.OperationID  = 5 and ((EAs.ExportActID is null) or
                 (EAs.State = 1)) )
            or (ITm.OperationID in (70,64,65))
            or (ITm.OperationID = 22 and WasteName.CropID = 9 and WasteName.ClassID in (41,141))
            or (ITm.OperationID = 17
                 and ( (not EAs.ExportActID is null and ITm.Net > 0)
                       or (not EAs.ExportActID is null and ITm.Net < 0)                     )
                  ) )
      and (ITm.State >= 0)
      and ITm.StorageID = 1
      and ITm.OwnerID = :OwnerID
      and Q1.CropID = :CropID
      and (Q1.ClassID = :AClassID or (:AClassID = -1))
      and (not ITm.Net is null or (ITm.OperationID = 64))
 --     and Parties.State = 1
      and Crop.CropTypeID <> 3

    INTO :REGDATE,
         :ACTNUMBER,
         :ACTDATE,
         :TRANSPORTNUMBER,
         :INVOICENUMBER,
         :ORDERNUMBER,
         :PROVIDERFULLNAME,
         :STATIONNAME,
         :INVOICENET,
         :INCOMENET,
         :DEFICITM,
         :OUTNET,
         :OPERATIONNAME,
         :OWNERFULLNAME,
         :CROPFULLNAME,
         :PID,
         :INVENTORYID,
         :CLASSID

  DO
  BEGIN
    SUSPEND;
  END
END