"localhost:/firebird/data/ILICHEVSK2.FDB". ��������� Previous topic Chapter index Next topic

ORDERLOAD_GETACTIVEITEMS

 

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

��������

���

��������

PROVIDERID

INTEGER

 

ACTIVEORDERLOADID

INTEGER

 

 

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

��������

���

��������

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

 

 

��������

(��� �������� ��� ��������� 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

 

     Previous topic Chapter index Next topic