"localhost:/firebird/data/ILICHEVSK2.FDB". ���������

ORDERLOAD_GETACTIVEITEMS

������� ���������

�������� ���������

��������

(��� �������� ��� ��������� ORDERLOAD_GETACTIVEITEMS)

�����������

CREATE PROCEDURE ORDERLOAD_GETACTIVEITEMS(
    PROVIDERID INTEGER,
    ACTIVEORDERLOADID INTEGER)
RETURNS (
    ORDERLOADITEMID INTEGER,
    ORDERLOADNUMBER VARCHAR(20),
    CROPCLASSNAME VARCHAR(150),
    QUALITYID INTEGER,
    PARTYID INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER,
    CLIENTID INTEGER,
    SORTID INTEGER,
    CLIENTFULLNAME VARCHAR(100),
    ORDERLOADID INTEGER,
    STORAGEID INTEGER,
    TRANSPORTSUBTYPEID INTEGER,
    OPERATIONID INTEGER,
    FORAGECROPID INTEGER,
    FORAGECLASSID INTEGER,
    FORAGEPARTYID INTEGER)
AS
begin
  for
    select ORDERLOAD.PROVIDERID, ORDERLOADITEMS.QUALITYID, QUALITY.PARTYID,
        QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, CLIENTS.CLIENTFULLNAME,
        ORDERLOAD.ORDERLOADID, ORDERLOAD.ORDERNUMBER, ORDERLOAD.STORAGEID,
        QUALITY_CROPFULLNAME.CROPFULLNAME || ' ' || coalesce(CROP.CROPNAME,'') || ' ' || coalesce(CLASS.CLASSNAME,''),
        ORDERLOADITEMS.ORDERLOADITEMID, ORDERLOAD.TRANSPORTSUBTYPEID,
        ORDERLOAD.OPERATIONID, QUALITY_SELECT.FORAGECROPID, QUALITY_SELECT.FORAGECLASSID,
        QUALITY_SELECT.FORAGEPARTYID
      from ORDERLOAD
        left join ORDERLOADITEMS on (ORDERLOADITEMS.ORDERLOADID = ORDERLOAD.ORDERLOADID)
        left join QUALITY on (QUALITY.QUALITYID = ORDERLOADITEMS.QUALITYID)
        left join CLIENTS on (CLIENTS.CLIENTID = ORDERLOAD.PROVIDERID)
        left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 1) on (0=0)
        left join QUALITY_SELECT(ORDERLOADITEMS.QUALITYID) on (0=0)
        left join CROP on (CROP.CROPID = QUALITY_SELECT.FORAGECROPID)
        left join CLASS on (CLASS.CLASSID = QUALITY_SELECT.FORAGECLASSID)
      where (ORDERLOAD.PROVIDERID = :PROVIDERID)
        and ((ORDERLOAD.STATE > 0) or (ORDERLOAD.ORDERLOADID = :ACTIVEORDERLOADID))
        and (ORDERLOADITEMS.ORDERLOADITEMID is not null)
    into :CLIENTID, :QUALITYID,
         :PARTYID, :CROPID, :CLASSID, :SORTID, :CLIENTFULLNAME,
         :ORDERLOADID, :ORDERLOADNUMBER, :STORAGEID,
         :CROPCLASSNAME,
         :ORDERLOADITEMID, :TRANSPORTSUBTYPEID,
         :OPERATIONID, :FORAGECROPID, :FORAGECLASSID, :FORAGEPARTYID
  do suspend;

  CLIENTID = null;
  QUALITYID = null;
  SORTID = null;
  CLIENTFULLNAME = null;
  ORDERLOADID = null;
  ORDERLOADNUMBER = '  ';
  STORAGEID = null;

  for
    select
        -CLASS.CLASSID, coalesce(CROP.CROPNAME, '') || ' ' || coalesce(CLASS.CLASSNAME, ''),
        CLASS.CROPID, CLASS.CLASSID
      from CLASS
        left join CROP on (CROP.CROPID = CLASS.CROPID)
      where (CLASS.ORDERLESS = 1)
    into :ORDERLOADITEMID, :CROPCLASSNAME, :CROPID, :CLASSID
  do suspend;
end