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

FORWARDER_OWNGRAINREP

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_OWNGRAINREP(
    BEGINDATA DATE,
    ENDDATE DATE)
RETURNS (
    INVOICEID INTEGER,
    REGDATE DATE,
    FORWARDERFULLNAME VARCHAR(100),
    PROVIDERFULLNAME VARCHAR(100),
    PROVIDERID INTEGER,
    INVOICENUMBER VARCHAR(31),
    TRANSPORTNUMBER VARCHAR(31),
    DRIVERS VARCHAR(30),
    CROPFULLNAME VARCHAR(100),
    OWNERFIELDID VARCHAR(10),
    BUNKERS VARCHAR(10),
    NET INTEGER,
    GROSS INTEGER,
    TARE INTEGER,
    STORAGENAME VARCHAR(100),
    GRAINFIELDOUTTIME TIMESTAMP,
    FORWARDERID INTEGER,
    CHECKPOINTOUTTIME TIMESTAMP,
    CHECKPOINTINTIME TIMESTAMP)
AS
DECLARE VARIABLE tmp_transportnumber VARCHAR(31);
DECLARE VARIABLE tmp_net INTEGER;
DECLARE VARIABLE tmp_gross INTEGER;
DECLARE VARIABLE tmp_tare INTEGER;
DECLARE VARIABLE tmp_bunkers VARCHAR(10);
DECLARE VARIABLE tmp_ownerfieldid VARCHAR(10);
BEGIN
  FOR
    select distinct
      it.invoiceid
      from it
        left join ittransport on (it.inventoryid = ittransport.inventoryid)
        left join clients on (it.ownerid = clients.clientid)
        left join transporttype on (ittransport.transporttypeid = transporttype.transportsubtype)
        left join quality on (it.samplerqualityid = quality.qualityid)
        left join parties on (it.partyid = parties.partyid)
      where it.operationid = 1
        and it.regdate between :begindata and :enddate
        and it.state >= 0
        and clients.isenterprise = 1
        and it.parentid >=0
        and it.storageid = 1
        and parties.state = 1
    INTO :invoiceid
  DO
  BEGIN
    transportnumber = null;
    bunkers = null;
    ownerfieldid = null;
    net = 0;
    gross = 0;
    tare = 0;
      for
        select
          it.regdate,
          forwarder.clientfullname as forwarderfullname,
          provider.clientfullname as providerfullname,
          it.providerid,
          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
        from it
          left join invoices on (it.invoiceid = invoices.invoiceid)
          left join clients owner on (it.ownerid = owner.clientid)
          left join clients provider on (it.providerid = provider.clientid)
          left join clients forwarder on (invoices.forwarderid = forwarder.clientid)
          left join ittransport on (it.inventoryid = ittransport.inventoryid)
          left join transporttype on (ittransport.transporttypeid = transporttype.transporttypeid)
          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 clientfields ownerfield on
            (ittransport.ownerfieldid = ownerfield.clientfieldid)
         where it.invoiceid = :invoiceid
           and it.state >= 0
           and it.operationid = 1
           and owner.isenterprise = 1
           and it.parentid >= 0
           and not it.regdate is null
        into
             :regdate,
             :forwarderfullname,
             :providerfullname,
             :providerid,
             :invoicenumber,
             :tmp_transportnumber,
             :drivers,
             :checkpointintime,
             :checkpointouttime,
             :cropfullname,
             :tmp_ownerfieldid,
             :tmp_bunkers,
             :tmp_net,
             :tmp_gross,
             :tmp_tare,
             :storagename,
             :grainfieldouttime,
             :forwarderid
      do
      begin
        if (:transportnumber is null) then
        begin
          transportnumber = coalesce(tmp_transportnumber,'') ;
          net = coalesce(tmp_net,0);
          gross = coalesce(tmp_gross,0);
          tare = coalesce(tmp_tare,0);
        end
        else
        begin
          transportnumber = coalesce(transportnumber,'') ||'\'|| tmp_transportnumber;
          net = net+coalesce(tmp_net,0);
          gross = gross+coalesce(tmp_gross,0);
          tare = tare+coalesce(tmp_tare,0);
        end
        if (:bunkers is null) then
          begin bunkers = coalesce(tmp_bunkers,''); end
        else
          if (not tmp_bunkers is null) then
          begin bunkers = coalesce(bunkers,'') ||'\'|| tmp_bunkers; end
        if (:ownerfieldid is null) then
          begin ownerfieldid = coalesce(tmp_ownerfieldid,''); end
        else
          if (not tmp_ownerfieldid is null) then
        ownerfieldid = coalesce(ownerfieldid,'') ||'\'|| tmp_ownerfieldid;
       end
    SUSPEND;
  END
END