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

ACC_INCOME_PROD_ADDON

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

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

Описание

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

Определение

CREATE PROCEDURE ACC_INCOME_PROD_ADDON(
    ISCURRENT INTEGER,
    PARTYDATE DATE,
    BEGINDATE DATE,
    ENDDATE DATE,
    AOWNERID INTEGER,
    ATRANSPORTSUBTYPE INTEGER,
    ACROPID INTEGER,
    ACLASSID INTEGER)
RETURNS (
    INVOICEDATE DATE,
    INVOICENUMBER VARCHAR(31),
    OWNERNAME VARCHAR(30),
    CROPFULLNAME VARCHAR(110),
    TRANSPORTNUMBER VARCHAR(64),
    TRTYPE VARCHAR(5),
    NET BIGINT,
    WEIGHT BIGINT,
    INVOICENET BIGINT,
    INVOICEID INTEGER,
    HUMIDITY NUMERIC(5,2),
    DIRT NUMERIC(5,2),
    SCREENING NUMERIC(5,2),
    PROTEIN NUMERIC(5,2),
    GRAINUNIT NUMERIC(5,2),
    ENTERPRISENAME VARCHAR(255),
    PARTYID INTEGER)
AS
declare variable TEMPNUMBER varchar(31);
begin
  for
    select
      ITMAIN.INVOICEID, INVOICES.INVOICEDATE, INVOICES.INVOICENUMBER,
      OWNER.CLIENTNAME OWNERNAME,
      case TRANSPORTTYPE.TRANSPORTSUBTYPE
        when 0 then
          'авто'
        else 'ж/д'
      end,
      QUALITY_SELECT.HUMIDITY, QUALITY_SELECT.DIRT,
      case
        when (CROP.CROPTYPEID <> 5) then
          QUALITY_SELECT.SCREENING
        else QUALITY_SELECT.OILSEED
      end SCREENING,
      QUALITY_SELECT.GRAINUNIT, QUALITY_SELECT.PROTEIN,
      QUALITY_CROPFULLNAME_BYID.CROPFULLNAME || ' (' || coalesce(cast(YEARGR.QUALITYVALUE as integer), '') || ') ' CROPFULLNAME,
      sum(ITTRANSPORT.INVOICENET) INVOICENET, sum(ITMAIN.NET) NET, sum(ITMAIN.WEIGHT) WEIGHT,
      ENTERPRISE.ENTERPRISENAME, ITMAIN.PARTYID
    from IT  ITMAIN
        join GETWORKPARTIES(:ISCURRENT, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = ITMAIN.PARTYID)
        left join CLIENTS OWNER on (OWNER.CLIENTID = ITMAIN.OWNERID)
        left join QUALITYDATA YEARGR on ((YEARGR.QUALITYID = ITMAIN.BUHQUALITYID) and (YEARGR.QUALITYTYPEID = 6))
        left join INVOICES on (INVOICES.INVOICEID = ITMAIN.INVOICEID)
        left join QUALITY_SELECT(ITMAIN.BUHQUALITYID) on (0 = 0)
        left join CROP on (CROP.CROPID = QUALITY_SELECT.CROPID)
        left join QUALITY_CROPFULLNAME_BYID(ITMAIN.BUHQUALITYID, 0) on (0=0)
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = ITMAIN.INVENTORYID)
        left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
        left join ENTERPRISE(ITMAIN.STORAGEID) on (0 = 0)
      where (ITMAIN.REGDATE between :BEGINDATE and :ENDDATE)
        and (ITMAIN.OPERATIONID = 1)
        and ((ITMAIN.OWNERID = :AOWNERID) or (coalesce(:AOWNERID, -1) = -1))
        and ((TRANSPORTTYPE.TRANSPORTSUBTYPE = :ATRANSPORTSUBTYPE) or (coalesce(:ATRANSPORTSUBTYPE, -1) = -1))
        and ((QUALITY_SELECT.CROPID = :ACROPID) or (coalesce(:ACROPID, -1) = -1))
        and ((QUALITY_SELECT.CLASSID = :ACLASSID) or (coalesce(:ACLASSID, -1) = -1))
        and ((ITMAIN.STATE >= 0) and (ITMAIN.PARENTID <> -1))
        and ((ITMAIN.NET is not null) and (ITMAIN.NET <> 0))
    group by OWNER.CLIENTNAME, QUALITY_CROPFULLNAME_BYID.CROPFULLNAME,
      TRANSPORTTYPE.TRANSPORTSUBTYPE, YEARGR.QUALITYVALUE, INVOICES.INVOICEDATE,
      INVOICES.INVOICENUMBER, QUALITY_SELECT.HUMIDITY, QUALITY_SELECT.DIRT,
      SCREENING, QUALITY_SELECT.GRAINUNIT, QUALITY_SELECT.PROTEIN,
      ITTRANSPORT.TRANSPORTNUMBER, ITMAIN.INVOICEID, ENTERPRISE.ENTERPRISENAME,
      ITMAIN.PARTYID
    into :INVOICEID, :INVOICEDATE, :INVOICENUMBER, :OWNERNAME, :TRTYPE,
         :HUMIDITY, :DIRT, :SCREENING, :GRAINUNIT, :PROTEIN, :CROPFULLNAME,
         :INVOICENET, :NET, :WEIGHT, :ENTERPRISENAME, :PARTYID
  do begin
    TRANSPORTNUMBER = '';
    for
      select ITTRANSPORT.TRANSPORTNUMBER
        from IT
          left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
        where (IT.INVOICEID = :INVOICEID)
          and ((IT.OPERATIONID = 1) and (IT.STATE <> -1))
      into :TEMPNUMBER
    do begin
      TRANSPORTNUMBER = TRANSPORTNUMBER || ' ' || :TEMPNUMBER;
    end

    suspend;
  end
end