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

ACC_INCOME_PROD_MARIUPOL

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

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

Описание

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

Определение

CREATE PROCEDURE ACC_INCOME_PROD_MARIUPOL(
    ISCURRENT INTEGER,
    PARTYDATE DATE,
    BEGINDATE DATE,
    ENDDATE DATE,
    AOWNERID INTEGER,
    ATRANSPORTSUBTYPE INTEGER,
    ACROPID INTEGER,
    ACLASSID INTEGER,
    AOPERATIONID INTEGER)
RETURNS (
    INVOICEDATE DATE,
    INVOICENUMBER VARCHAR(31),
    OWNERNAME VARCHAR(30),
    PROVIDERNAME VARCHAR(30),
    INVOICEGROSS BIGINT,
    INVOICETARE BIGINT,
    INVOICENET BIGINT,
    GROSS BIGINT,
    TARE BIGINT,
    NET BIGINT,
    DISCREPANCY INTEGER,
    TRANSPORTNUMBER VARCHAR(64),
    INVOICEID INTEGER,
    CROPFULLNAME VARCHAR(110),
    CROPFORAGEFULLNAME VARCHAR(250),
    REGDATE DATE,
    PROVIDERID INTEGER)
AS
declare variable TEMPNUMBER varchar(31);
begin
  for
    select IT.INVOICEID, INVOICES.INVOICEDATE, INVOICES.INVOICENUMBER,
        OWNER.CLIENTNAME OWNERNAME, PROVIDER.CLIENTNAME PROVIDERNAME,
        QUALITY_CROPFULLNAME_BYID.CROPFULLNAME || ' (' || coalesce(QUALITY_SELECT.HARVESTYEAR,'') || ') ' CROPFULLNAME,
        min(QUALITY_CROPFULLNAME_FORAGE.CROPFULLNAME || ' (' || coalesce(QUALITY_SELECT.HARVESTYEAR,'') || ') ') CROPFORAGEFULLNAME,
        sum(ITTRANSPORT.INVOICEGROSS) INVOICEGROSS, sum(ITTRANSPORT.INVOICETARE) INVOICETARE,
        sum(ITTRANSPORT.INVOICENET) INVOICENET, sum(IT.GROSS) GROSS,
        sum(IT.TARE) TARE, sum(IT.NET) NET, IT.REGDATE, IT.PROVIDERID
      from IT
        join GETWORKPARTIES(:ISCURRENT, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
        left join CLIENTS OWNER on (OWNER.CLIENTID = IT.OWNERID)
        left join CLIENTS PROVIDER on (PROVIDER.CLIENTID = IT.PROVIDERID)
        left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
        left join QUALITY on (QUALITY.QUALITYID = IT.BUHQUALITYID)
        left join QUALITY_SELECT(IT.BUHQUALITYID) on (0=0)
        left join QUALITY_CROPFULLNAME_BYID(IT.BUHQUALITYID, 0) on (0=0)
        left join QUALITY_CROPFULLNAME_FORAGE(QUALITY_SELECT.CROPID, QUALITY_SELECT.CLASSID, QUALITY_SELECT.SORTID, QUALITY_SELECT.FORAGECROPID, QUALITY_SELECT.FORAGECLASSID, 0) on (0=0)
        left join ITTRANSPORT on (IT.INVENTORYID = ITTRANSPORT.INVENTORYID)
        left join TRANSPORTTYPE on (ITTRANSPORT.TRANSPORTTYPEID = TRANSPORTTYPE.TRANSPORTTYPEID)
      where (IT.REGDATE between :BEGINDATE and :ENDDATE)
        and (IT.OPERATIONID = :AOPERATIONID) and (IT.STATE >= 0)
        and ((IT.OWNERID = :AOWNERID) or (coalesce(:AOWNERID,-1) = -1))
        and ((QUALITY.CROPID = :ACROPID) or (coalesce(:ACROPID,-1) = -1))
        and ((QUALITY.CLASSID = :ACLASSID) or (coalesce(:ACLASSID,-1) = -1))
        and (TRANSPORTTYPE.TRANSPORTSUBTYPE = :ATRANSPORTSUBTYPE)
        and (IT.PARENTID <> -1) and (IT.NET is not null) and (IT.NET <> 0)
    group by INVOICES.INVOICEDATE, INVOICES.INVOICENUMBER, OWNER.CLIENTNAME,
             PROVIDER.CLIENTNAME, QUALITY_CROPFULLNAME_BYID.CROPFULLNAME,
             QUALITY_SELECT.HARVESTYEAR, IT.INVOICEID, IT.REGDATE, IT.PROVIDERID
    into :INVOICEID, :INVOICEDATE, :INVOICENUMBER, :OWNERNAME, :PROVIDERNAME,
         :CROPFULLNAME, :CROPFORAGEFULLNAME, :INVOICEGROSS, :INVOICETARE,
         :INVOICENET, :GROSS, :TARE, :NET, :REGDATE, :PROVIDERID
  do begin
    TRANSPORTNUMBER = '';

    for
      select ITTRANSPORT.TRANSPORTNUMBER
        from IT
          left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
         where IT.INVOICEID = :INVOICEID
           and IT.STATE <> -1
      into :TEMPNUMBER
    do begin
      if (TRANSPORTNUMBER <> '') then
        TRANSPORTNUMBER = TRANSPORTNUMBER || ' : ';

      TRANSPORTNUMBER = TRANSPORTNUMBER || coalesce(:TEMPNUMBER, '');
    end

    DISCREPANCY = :NET - :INVOICENET;

    suspend;
  end
END