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

ACC_INCOME_PROD

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

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

Описание

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

Определение

CREATE PROCEDURE ACC_INCOME_PROD(
    ISCURRENT INTEGER,
    PARTYDATE DATE,
    ADATE DATE,
    AOWNERID INTEGER,
    ATRANSPORTSUBTYPE INTEGER)
RETURNS (
    INVOICEDATE DATE,
    INVOICENUMBER VARCHAR(31),
    OWNERNAME VARCHAR(30),
    PROVIDERNAME VARCHAR(30),
    INVOICENET BIGINT,
    NET BIGINT,
    TRANSPORTNUMBER VARCHAR(64),
    INVOICEID INTEGER,
    CROPFULLNAME VARCHAR(110))
AS
declare variable TEMPNUMBER varchar(31);
BEGIN
  FOR
    select
      ITmain.InvoiceID,
      Invoices.InvoiceDate,
      Invoices.InvoiceNumber,
      Owner.ClientName OwnerName,
      Provider.ClientName ProviderName,
      quality_cropfullname_byid.cropfullname||' ('||coalesce( cast(YearGr.qualityvalue as integer),'')||') ' as CropFullName,
      sum(ITTransport.InvoiceNet) as InvoiceNet,
      sum(ITmain.Net) as Net
    from IT  ITmain
      join GetWorkParties( :IsCurrent, :PartyDate, null, null, null)
        on (ITmain.PartyID = GetWorkParties.PartyID)
      left join Clients Owner on (ITmain.OwnerID = Owner.ClientID)
      left join Clients Provider on (ITmain.ProviderID = Provider.ClientID)
      left join Invoices on (ITmain.InvoiceID = Invoices.InvoiceID)
      left join QualityData YearGr on (ITmain.BuhQualityID = YearGr.QualityID
        and YearGr.qualitytypeid = 6)
      left join quality_cropfullname_byid(ITmain.BuhQualityID,0) on (0=0)
      left join ITTransport on (ITmain.InventoryID = ITTransport.InventoryID)
      left join Transporttype on (ITTransport.TransportTypeID = TransportType.TransportTypeID)
    where ITmain.RegDate = :ADate
      and ITmain.OperationID = 1
      and (ITmain.ownerid = :AOwnerID or (coalesce(:AOwnerID,-1) = -1))
      and Transporttype.TransportSubType = :ATransportSubType
      and ITmain.state >= 0
      and ITmain.parentid <> -1
      and ITmain.Net is not null
      and ITmain.Net <> 0
    group by
      Invoices.InvoiceDate,
      Invoices.InvoiceNumber,
      Owner.ClientName,
      Provider.ClientName,
      quality_cropfullname_byid.cropfullname,
      YearGr.qualityvalue,
      ITTransport.TransportNumber,
      ITmain.InvoiceID
    INTO :InvoiceID,
         :INVOICEDATE,
         :INVOICENUMBER,
         :OWNERNAME,
         :PROVIDERNAME,
         :CropFullName,
         :INVOICENET,
         :NET
  DO
  BEGIN
    Transportnumber = '';
    for
      select ittransport.transportnumber
      from IT
        left join ITTransport on (IT.InventoryID = ITTransport.InventoryID)
       where IT.InvoiceID = :invoiceid
         and IT.State <> -1
       into :TempNumber
      do
      begin
        Transportnumber = Transportnumber || :TempNumber;
      end

    SUSPEND;
  END
END