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

FORWARDER_OWNGRAINJOURNAL

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_OWNGRAINJOURNAL(
    BEGINDATA DATE,
    ENDDATA DATE,
    ACROPID INTEGER,
    ACLASSID INTEGER,
    APROVIDERID INTEGER,
    ISCURRENT INTEGER,
    APARTYDATE DATE,
    ASTORAGEID INTEGER,
    TRSUBTYPE INTEGER)
RETURNS (
    REGDATE DATE,
    FORWARDERFULLNAME VARCHAR(100),
    PROVIDERID INTEGER,
    INVENTORYID INTEGER,
    INVOICEID INTEGER,
    INVOICENUMBER VARCHAR(31),
    TRANSPORTNUMBER VARCHAR(31),
    DRIVERS VARCHAR(30),
    CROPFULLNAME VARCHAR(100),
    BUNKERS NUMERIC(2,1),
    NET INTEGER,
    GROSS INTEGER,
    TARE INTEGER,
    STORAGENAME VARCHAR(100),
    GRAINFIELDOUTTIME TIMESTAMP,
    PROVIDERFULLNAME VARCHAR(100),
    FORWARDERID INTEGER,
    OWNERFULLNAME VARCHAR(100),
    CHECKPOINTOUTTIME TIMESTAMP,
    CHECKPOINTINTIME TIMESTAMP,
    OWNERFIELDID INTEGER)
AS
declare variable enterpriseid integer;
BEGIN
  select Clients.ClientID
  from Clients
    where Clients.IsEnterprise = 1
  into :ENTERPRISEID;
  for
    select
      IT.RegDate,
      Forwarder.ClientFullName as ForwarderFullName,
      Provider.Clientfullname as ProviderFullName,
      IT.ProviderID,
      IT.InventoryID,
      IT.InvoiceID,
      Invoices.InvoiceNumber,
      ITTransport.TransportNumber, --!!!!!!!!
      Invoices.Drivers,
      ITTransport.CheckPointInTime,
      ITTransport.CheckPointOutTime,
      Quality_CropFullName.CropFullName,
      ITTransport.OwnerFieldID,
      ITTransport.Bunkers,
      IT.Net,
      IT.Gross,
      IT.Tare,
      Storage.StorageName,
      ITTransport.GrainFieldOutTime,
      Invoices.ForwarderID,
      Owner.ClientFullName as OwnerFullName
from IT
      left join GetWorkParties(:IsCurrent,:APartyDate,
        case when coalesce(:ACropID,-1) = -1 then null else coalesce(:ACropID,-1) end,
        case when coalesce(:AClassID,-1) = -1 then null else coalesce(:AClassID,-1) end,
        case when coalesce(:Astorageid,-1) = -1 then null else coalesce(:Astorageid,-1) end )
        on (IT.PartyID = GetWorkParties.PartyID)
      left join Invoices on (IT.InvoiceID = Invoices.InvoiceID)
      left join Clients Provider on (IT.ProviderID = Provider.ClientID)
      left join Clients Forwarder on (Invoices.ForwarderID = Forwarder.ClientID)
      left join Clients Owner on (IT.OwnerID = Owner.ClientID)
      left join ITTransport on (IT.InventoryID = ITTransport.InventoryID)
      left join Storage on (IT.StorageID = Storage.StorageID)
      left join Quality on (IT.SamplerQualityID = Quality.QualityID)
      left join Quality_CropFullName (Quality.CropID, ClassID, null, 1) on (0=0)
      left join Parties on (IT.PartyID = Parties.PartyID)
    where IT.OperationID = 1
      and IT.RegDate between :BeginData and :EndData
      and (IT.ProviderID = :AProviderID or (coalesce(:AProviderID, -1) = -1))
      and (Quality.CropID = :ACropID or (coalesce(:ACropID, -1) = -1))
      and (Quality.ClassID = :AClassID or (coalesce(:AClassID, -1) = -1))
      and IT.State >= 0
      and IT.ParentID >= 0
      and (IT.StorageID = :AStorageID or (coalesce(:AStorageID, -1) = -1))
      and Parties.State = 1
      and :ENTERPRISEID = IT.OwnerID
    order by IT.RegDate, Invoices.InvoiceNumber, IT.InvoiceID
    into
         :REGDATE,
         :FORWARDERFULLNAME,
         :PROVIDERFULLNAME,
         :PROVIDERID,
         :INVENTORYID,
         :INVOICEID,
         :INVOICENUMBER,
         :TRANSPORTNUMBER,
         :DRIVERS,
         :CHECKPOINTINTIME,
         :CHECKPOINTOUTTIME,
         :CROPFULLNAME,
         :OWNERFIELDID,
         :BUNKERS,
         :NET,
         :GROSS,
         :TARE,
         :STORAGENAME,
         :GRAINFIELDOUTTIME,
         :FORWARDERID,
         :OWNERFULLNAME
  do
  begin
    suspend;
  end
END