Входные параметры
Параметр | Тип | Описание |
---|---|---|
BEGINDATE |
DATE |
|
ENDDATE |
DATE |
|
AOWNERID |
INTEGER |
|
ACROPID |
INTEGER |
|
ACLASSID |
INTEGER |
|
ISCURRENT |
INTEGER |
|
APARTYDATE |
DATE |
|
PLUGINUNDERWORK |
INTEGER |
|
APROVIDERID |
INTEGER |
|
ASTATIONID |
INTEGER |
|
ASTORAGEID |
INTEGER |
|
TRSUBTYPE |
INTEGER |
|
Выходные параметры
Параметр | Тип | Описание |
---|---|---|
ACTDATE |
DATE |
|
ACTNUMBER |
VARCHAR(10) |
|
PROVIDERFULLNAME |
VARCHAR(100) |
|
REGDATE |
DATE |
|
CROPFULLNAME |
VARCHAR(100) |
|
OWNERFULLNAME |
VARCHAR(100) |
|
SUMWEIGHT |
BIGINT |
|
EXPORTACTID |
INTEGER |
|
OWNERID |
INTEGER |
|
CROPID |
INTEGER |
|
CLASSID |
INTEGER |
|
PROVIDERID |
INTEGER |
|
STORAGEID |
INTEGER |
|
STATE |
INTEGER |
|
ORDERITEMID |
INTEGER |
|
ORDERNUMBER |
VARCHAR(10) |
|
ORDERLOADID |
INTEGER |
|
STATIONID |
INTEGER |
|
WASTENET |
INTEGER |
|
ORDERNET |
INTEGER |
|
SAMPLENET |
INTEGER |
|
WASTE02NET |
INTEGER |
|
ALLNET |
INTEGER |
|
TRANSPORTSUBTYPEID |
INTEGER |
|
SHIPNAME |
VARCHAR(50) |
|
STATIONNAME |
VARCHAR(30) |
|
TYPESTATION |
VARCHAR(10) |
|
ISSHIP |
VARCHAR(10) |
|
EXPORTACTIDCALC |
INTEGER |
|
PREWASTENET |
INTEGER |
|
STORAGENAME |
VARCHAR(100) |
|
PARTYID |
INTEGER |
|
OPERATIONID |
INTEGER |
|
CONTRACTID |
INTEGER |
|
Описание
(Нет описания для процедуры FORWARDER_OUTCOMEACT)
Определение
CREATE PROCEDURE FORWARDER_OUTCOMEACT(
BEGINDATE DATE,
ENDDATE DATE,
AOWNERID INTEGER,
ACROPID INTEGER,
ACLASSID INTEGER,
ISCURRENT INTEGER,
APARTYDATE DATE,
PLUGINUNDERWORK INTEGER,
APROVIDERID INTEGER,
ASTATIONID INTEGER,
ASTORAGEID INTEGER,
TRSUBTYPE INTEGER)
RETURNS (
ACTDATE DATE,
ACTNUMBER VARCHAR(10),
PROVIDERFULLNAME VARCHAR(100),
REGDATE DATE,
CROPFULLNAME VARCHAR(100),
OWNERFULLNAME VARCHAR(100),
SUMWEIGHT BIGINT,
EXPORTACTID INTEGER,
OWNERID INTEGER,
CROPID INTEGER,
CLASSID INTEGER,
PROVIDERID INTEGER,
STORAGEID INTEGER,
STATE INTEGER,
ORDERITEMID INTEGER,
ORDERNUMBER VARCHAR(10),
ORDERLOADID INTEGER,
STATIONID INTEGER,
WASTENET INTEGER,
ORDERNET INTEGER,
SAMPLENET INTEGER,
WASTE02NET INTEGER,
ALLNET INTEGER,
TRANSPORTSUBTYPEID INTEGER,
SHIPNAME VARCHAR(50),
STATIONNAME VARCHAR(30),
TYPESTATION VARCHAR(10),
ISSHIP VARCHAR(10),
EXPORTACTIDCALC INTEGER,
PREWASTENET INTEGER,
STORAGENAME VARCHAR(100),
PARTYID INTEGER,
OPERATIONID INTEGER,
CONTRACTID INTEGER)
AS
begin
for
select EXPORTACTS.ACTDATE, EXPORTACTS.EXPORTACTID, EXPORTACTS.ACTNUMBER,
EXPORTACTS.REGDATE, EXPORTACTS.OWNERID, EXPORTACTS.PROVIDERID, EXPORTACTS.STORAGEID,
EXPORTACTS.ORDERITEMID, EXPORTACTS.STATE, QUALITY.CROPID, QUALITY.CLASSID,
QUALITY_CROPFULLNAME_BYID.CROPFULLNAME, OWNER.CLIENTFULLNAME OWNERFULLNAME,
PROVIDER.CLIENTFULLNAME PROVIDERFULLNAME, ORDERLOAD.ORDERNUMBER, ORDERLOAD.ORDERLOADID,
EXPORTACTS.STATIONID, EXPORTACTS.TRANSPORTSUBTYPEID, ORDERLOADITEMS.NET ORDERNET,
case
when (EXPORTACTS.TRANSPORTSUBTYPEID = 2) then
(select first 1 SHIPS.SHIPNAME
from IT ITSH
left join IT ITS on ((ITS.INVENTORYID = ITSH.RELATIONID) and (ITS.PARENTID = -1))
left join SHIPS on (SHIPS.SHIPID = ITS.RELATIONID)
where (ITSH.ORDERLOADITEMID = EXPORTACTS.ORDERITEMID)
and (ITSH.OPERATIONID in (5,1005)) and (not (ITSH.STATE in (-1,-100))))
end,
case
when (EXPORTACTS.TRANSPORTSUBTYPEID = 1) then
ST.STATIONNAME
when (EXPORTACTS.TRANSPORTSUBTYPEID = 2) then
(select first 1 ST2.STATIONNAME
from IT ITSH
left join IT ITS on ((ITS.INVENTORYID = ITSH.RELATIONID) and (ITS.PARENTID = -1))
left join INVOICES on (INVOICES.INVOICEID = ITS.INVOICEID)
left join STATIONS ST2 on (ST2.STATIONID = INVOICES.STATIONID)
where (ITSH.ORDERLOADITEMID = EXPORTACTS.ORDERITEMID)
and (ITSH.OPERATIONID in (5,1005)) and (not (ITSH.STATE in (-1,-100))))
end STATIONNAME,
case
when (EXPORTACTS.TRANSPORTSUBTYPEID = 1) then
'Станция:'
when (EXPORTACTS.TRANSPORTSUBTYPEID = 2) then
'Порт:'
end TYPESTATION,
case
when (EXPORTACTS.TRANSPORTSUBTYPEID = 2) then
'Судно:'
end ISSHIP,
EXPORTACTQUALITY.EXPORTACTID EXPORTACTIDCALC, STORAGE.STORAGESHORTNAME,
sum(case
when (EAI.WEIGHT > 0) then
EAI.WEIGHT
end) SUMWEIGHT,
sum(case
when ((EAI.WEIGHT < 0) and (ITM.OPERATIONID = 22) and (ITM.STATE = 0)) then
-EAI.WEIGHT
end) WASTENET,
sum(case
when ((EAI.WEIGHT < 0) and (ITM.OPERATIONID = 22) and (ITM.STATE = -10)) then
-EAI.WEIGHT
end) PREWASTENET,
sum(case
when ((EAI.WEIGHT < 0) and (ITM.OPERATIONID = 70))
then -EAI.WEIGHT
end) SAMPLENET,
sum(case
when ((EAI.WEIGHT < 0) and (EAI.OPERATIONID = 64))
then -EAI.WEIGHT
end) WASTE02NET,
sum(case
when (EAI.WEIGHT < 0) then
-EAI.WEIGHT
else EAI.WEIGHT
end) ALLNET,
min(ORDERLOADQUALITY.PARTYID), min(ORDERLOAD.OPERATIONID),
min(ORDERLOAD.CONTRACTID)
from EXPORTACTS
left join CLIENTS OWNER on ((case
when ((EXPORTACTS.TRANSPORTSUBTYPEID = 2) and (:PLUGINUNDERWORK = 5)) then
EXPORTACTS.OWNERID
else EXPORTACTS.PROVIDERID
end) = OWNER.CLIENTID)
left join CLIENTS PROVIDER on ((case
when ((EXPORTACTS.TRANSPORTSUBTYPEID = 2) and (:PLUGINUNDERWORK = 5)) then
EXPORTACTS.PROVIDERID
else EXPORTACTS.OWNERID
end) = PROVIDER.CLIENTID)
left join STORAGE on (STORAGE.STORAGEID = EXPORTACTS.STORAGEID)
left join STATIONS ST on ((case
when (EXPORTACTS.TRANSPORTSUBTYPEID = 1) then
EXPORTACTS.STATIONID
end) = ST.STATIONID)
left join EXPORTACTITEMS EAI on (EAI.EXPORTACTID = EXPORTACTS.EXPORTACTID)
left join IT ITM on (ITM.INVENTORYID = EAI.INVENTORYID)
join GETWORKPARTIES(:ISCURRENT, :APARTYDATE,
case
when (coalesce(:CROPID,-1) = -1) then
null
else coalesce(:CROPID,-1)
end,
case
when (coalesce(:CLASSID,-1) = -1) then
null
else coalesce(:CLASSID,-1)
end,
case
when (coalesce(:STORAGEID,-1) = -1) then
null
else coalesce(:STORAGEID,-1)
end) on (GETWORKPARTIES.PARTYID = ITM.PARTYID)
left join QUALITY on (QUALITY.QUALITYID = EXPORTACTS.QUALITYID)
left join QUALITY_CROPFULLNAME_BYID (EXPORTACTS.QUALITYID, 1) on (0=0)
left join ORDERLOADITEMS on (ORDERLOADITEMS.ORDERLOADITEMID = EXPORTACTS.ORDERITEMID)
left join ORDERLOAD on (ORDERLOAD.ORDERLOADID = ORDERLOADITEMS.ORDERLOADID)
left join EXPORTACTQUALITY on (EXPORTACTQUALITY.EXPORTACTID = EXPORTACTS.EXPORTACTID)
left join QUALITY ORDERLOADQUALITY on (ORDERLOADQUALITY.QUALITYID = ORDERLOADITEMS.QUALITYID)
where (EXPORTACTS.ACTDATE between :BEGINDATE and :ENDDATE)
and (EXPORTACTS.OPERATIONID in (5,1005)) and (EXPORTACTS.STATE <> -1) and (EAI.OPERATIONID <> -64)
and ((EXPORTACTS.OWNERID = :AOWNERID) or (coalesce(:AOWNERID,-1) = -1))
and ((EXPORTACTS.PROVIDERID = :APROVIDERID) or (coalesce(:APROVIDERID,-1) = -1))
and ((EXPORTACTS.STATIONID = :ASTATIONID) or (coalesce(:ASTATIONID,-1) = -1))
and ((EXPORTACTS.STORAGEID = :ASTORAGEID) or (coalesce(:ASTORAGEID,-1) = -1))
and (EXPORTACTS.EXPORTACTPARENTID is null)
and ((EXPORTACTS.TRANSPORTSUBTYPEID = :TRSUBTYPE) or (coalesce(:TRSUBTYPE,-1) = -1))
and ((QUALITY.CROPID = :ACROPID) or (coalesce(:ACROPID,-1) = -1))
and ((QUALITY.CLASSID = :ACLASSID) or (coalesce(:ACLASSID,-1) = -1))
group by EXPORTACTS.ACTDATE, EXPORTACTS.REGDATE, EXPORTACTS.EXPORTACTID,
EXPORTACTS.ACTNUMBER, EXPORTACTS.OWNERID, EXPORTACTS.PROVIDERID, EXPORTACTS.STORAGEID,
EXPORTACTS.TRANSPORTSUBTYPEID, EXPORTACTS.ORDERITEMID, EXPORTACTS.STATE, QUALITY.CROPID,
QUALITY.CLASSID, QUALITY_CROPFULLNAME_BYID.CROPFULLNAME, OWNER.CLIENTFULLNAME,
PROVIDER.CLIENTFULLNAME, ORDERLOAD.ORDERNUMBER, ORDERLOAD.ORDERLOADID,
EXPORTACTS.STATIONID, ORDERLOADITEMS.NET, EXPORTACTQUALITY.EXPORTACTID,
ST.STATIONNAME, STORAGE.STORAGESHORTNAME
union
select ORDERLOAD.BEGINDATE, cast (null as integer), cast (null as varchar(10)),
cast (null as date), ORDERLOAD.OWNERID, ORDERLOAD.PROVIDERID, ORDERLOAD.STORAGEID,
ORDERLOADITEMS.ORDERLOADITEMID, cast(0 as integer) STATE, QUALITY.CROPID,
QUALITY.CLASSID, QUALITY_CROPFULLNAME.CROPFULLNAME, OWNER.CLIENTFULLNAME OWNERFULLNAME,
PROVIDER.CLIENTFULLNAME PROVIDERFULLNAME, ORDERLOAD.ORDERNUMBER, ORDERLOAD.ORDERLOADID,
ORDERLOAD.DESTINATIONPOINTID, ORDERLOAD.TRANSPORTSUBTYPEID, ORDERLOADITEMS.NET ORDERNET,
SHIPS.SHIPNAME, STATIONS.STATIONNAME,
case
when (ORDERLOAD.TRANSPORTSUBTYPEID = 2) then
'Порт:'
when (ORDERLOAD.TRANSPORTSUBTYPEID = 1) then
'Станция:'
end as TYPESTATION,
case
when (ORDERLOAD.TRANSPORTSUBTYPEID = 2) then
'Судно:'
end ISSHIP,
cast(null as integer) EXPORTACTIDCALC, STORAGE.STORAGESHORTNAME,
sum(cast(null as integer)) SUMWEIGHT, sum(cast(null as integer)) WASTENET,
sum(cast(null as integer)) PREWASTENET, sum(cast(null as integer)) SAMPLENET,
sum(cast(null as integer)) WASTE02NET, sum(cast(null as integer)) ALLNET,
min(QUALITY.PARTYID), min(ORDERLOAD.OPERATIONID), min(ORDERLOAD.CONTRACTID)
from ORDERLOAD
left join CLIENTS OWNER on ((case
when ((ORDERLOAD.TRANSPORTSUBTYPEID = 2) and (:PLUGINUNDERWORK = 5)) then
ORDERLOAD.OWNERID
else ORDERLOAD.PROVIDERID
end) = OWNER.CLIENTID)
left join CLIENTS PROVIDER on ((case
when ((ORDERLOAD.TRANSPORTSUBTYPEID = 2) and (:PLUGINUNDERWORK = 5)) then
ORDERLOAD.PROVIDERID
else ORDERLOAD.OWNERID
end) = PROVIDER.CLIENTID)
left join STORAGE on (STORAGE.STORAGEID = ORDERLOAD.STORAGEID)
left join ORDERLOADITEMS on (ORDERLOADITEMS.ORDERLOADID = ORDERLOAD.ORDERLOADID)
left join QUALITY on (QUALITY.QUALITYID = ORDERLOADITEMS.QUALITYID)
left join QUALITY_CROPFULLNAME (QUALITY.CROPID, QUALITY.CLASSID, null, 1) on (0=0)
left join IT ITSH on ((ITSH.ORDERLOADITEMID = ORDERLOADITEMS.ORDERLOADITEMID) and (ITSH.OPERATIONID in (5,1005)) and (ITSH.STATE not in (0,-1,-5,-100)))
left join GETWORKPARTIES(:ISCURRENT, :APARTYDATE,
case
when (coalesce(:CROPID,-1) = -1) then
null
else coalesce(:CROPID,-1)
end,
case
when (coalesce(:CLASSID,-1) = -1) then
null
else coalesce(:CLASSID,-1)
end,
case
when (coalesce(:STORAGEID,-1) = -1) then
null
else coalesce(:STORAGEID,-1)
end) on (GETWORKPARTIES.PARTYID = ITSH.PARTYID)
left join IT ITS on ((ITS.INVENTORYID = ITSH.RELATIONID) and (ITS.PARENTID = -1))
left join SHIPS on (SHIPS.SHIPID = ITS.RELATIONID)
left join INVOICES on (INVOICES.INVOICEID = ITS.INVOICEID)
left join STATIONS on ((case
when ORDERLOAD.TRANSPORTSUBTYPEID = 2
then INVOICES.STATIONID
when ORDERLOAD.TRANSPORTSUBTYPEID = 1
then ORDERLOAD.DESTINATIONPOINTID
end) = STATIONS.STATIONID)
left join CROP on (CROP.CROPID = QUALITY.CROPID)
where (ORDERLOAD.BEGINDATE between :BEGINDATE and :ENDDATE)
and (not (exists (select EXPORTACTID from EXPORTACTS
where (EXPORTACTS.ORDERITEMID = ORDERLOADITEMS.ORDERLOADITEMID)
and (EXPORTACTS.STATE > 0))))
and ((ORDERLOAD.OWNERID = :AOWNERID) or (coalesce(:AOWNERID,-1) = -1))
and ((ORDERLOAD.PROVIDERID = :APROVIDERID) or (coalesce(:APROVIDERID,-1) = -1))
and ((ORDERLOAD.TRANSPORTSUBTYPEID = :TRSUBTYPE) or (coalesce(:TRSUBTYPE,-1) = -1))
and ((STATIONS.STATIONID = :ASTATIONID) or (coalesce(:ASTATIONID,-1) = -1))
and ((ORDERLOAD.STORAGEID = :ASTORAGEID) or (coalesce(:ASTORAGEID,-1) = -1))
and (ORDERLOAD.STATE > 0) and (ORDERLOAD.OPERATIONID in (5,1005)) and (CROP.CROPTYPEID <> 3)
and ((QUALITY.CROPID = :ACROPID) or (coalesce(:ACROPID,-1) = -1))
and ((QUALITY.CLASSID = :ACLASSID) or (coalesce(:ACLASSID,-1) = -1))
group by ORDERLOAD.BEGINDATE, ORDERLOAD.OWNERID, ORDERLOAD.PROVIDERID,
ORDERLOAD.STORAGEID, ORDERLOADITEMS.ORDERLOADITEMID, ORDERLOAD.STATE,
QUALITY.CROPID, QUALITY.CLASSID, QUALITY_CROPFULLNAME.CROPFULLNAME,
OWNER.CLIENTFULLNAME, PROVIDER.CLIENTFULLNAME, ORDERLOAD.ORDERNUMBER,
ORDERLOAD.ORDERLOADID, ORDERLOAD.DESTINATIONPOINTID, ORDERLOAD.TRANSPORTSUBTYPEID,
ORDERLOADITEMS.NET, SHIPS.SHIPNAME, STATIONS.STATIONNAME, STORAGE.STORAGESHORTNAME,
STATIONS.REGIONID
order by 1, 15, 3, 12, 13
into :ACTDATE, :EXPORTACTID, :ACTNUMBER, :REGDATE, :OWNERID, :PROVIDERID,
:STORAGEID, :ORDERITEMID, :STATE, :CROPID, :CLASSID, :CROPFULLNAME,
:OWNERFULLNAME, :PROVIDERFULLNAME, :ORDERNUMBER, :ORDERLOADID,
:STATIONID, :TRANSPORTSUBTYPEID, :ORDERNET, :SHIPNAME, :STATIONNAME,
:TYPESTATION, :ISSHIP, :EXPORTACTIDCALC, :STORAGENAME, :SUMWEIGHT,
:WASTENET, :PREWASTENET, :SAMPLENET, :WASTE02NET, :ALLNET, :PARTYID,
:OPERATIONID, :CONTRACTID
do begin
suspend;
end
end