Входные параметры
Параметр | Тип | Описание |
---|---|---|
DATE1 |
DATE |
|
DATE2 |
DATE |
|
STORAGEID |
INTEGER |
|
CLASSID |
INTEGER |
|
SORTID |
INTEGER |
|
DOCROP |
INTEGER |
|
OWNERID |
INTEGER |
|
CONTRACTID |
INTEGER |
|
PARTY_ID |
INTEGER |
|
PARTYDATE |
DATE |
|
CALCFROMSTART |
INTEGER |
|
Выходные параметры
Параметр | Тип | Описание |
---|---|---|
RESULT |
NUMERIC(18,0) |
|
Описание
Хранящийся остаток для Акта расчета
Определение
CREATE PROCEDURE ACC_STORINGREMINDER(
DATE1 DATE,
DATE2 DATE,
STORAGEID INTEGER,
CLASSID INTEGER,
SORTID INTEGER,
DOCROP INTEGER,
OWNERID INTEGER,
CONTRACTID INTEGER,
PARTY_ID INTEGER,
PARTYDATE DATE,
CALCFROMSTART INTEGER)
RETURNS (
RESULT NUMERIC(18,0))
AS
declare variable incomebefore numeric(18,0);
declare variable outcomebefore numeric(18,0);
declare variable income numeric(18,0);
declare variable outcome numeric(18,0);
declare variable incomefromstart numeric(18,0);
declare variable outcomefromstart numeric(18,0);
declare variable rdate date;
declare variable rdate_old date;
declare variable difference numeric(18,0);
declare variable cropid integer;
declare variable reminder integer;
BEGIN
income = 0 ;
outcome = 0;
outcomefromstart = 0;
incomefromstart = 0;
result = 0;
if (:calcfromstart = 2) then
begin
select parties.begindate from parties where partyid = :party_id
into :date1;
end
if (:docrop = 1) then
cropid = :CLASSID;
else
begin
select cropid from class where class.classid = :classid
into
:cropid;
end
DIFFERENCE = 0;
SELECT coalesce(SUM(coalesce(storingreminder_weight.weight0,0)),0), coalesce(SUM(coalesce(storingreminder_weight.weight1, 0)), 0)
FROM IT
join GetWorkParties(:PARTY_ID,:PARTYDATE,null,null,:storageid)
on (IT.PartyID = GetWorkParties.PartyID)
Left join operation on (it.operationid = operation.operationid)
left join quality on (it.BuhQualityid = quality.qualityid)
left join storingreminder_weight(operation.operationsubtype,it.net) on (0=0)
WHERE IT.RegDATE < :date1
and operation.operationsubtype in (0,1)
and ((quality.classid = :classid and :docrop = 0) or (quality.cropid = :classid and :docrop = 1))
and IT.storageid = :storageid
and it.parentid >= 0
and IT.state >= 0
and it.net is not null
and ((It.ownerid = :ownerid) or (:ownerid = 0))
and ((it.contractid = :contractid) or (:contractid = 0))
and ((quality.sortid = :sortid) or (:sortid = 0))
INTO
:incomefromstart,
:outcomefromstart;
for
SELECT
SUM(coalesce(storingreminder_weight.weight0,0)),
SUM(coalesce(storingreminder_weight.weight1, 0)),
IT.regdate
FROM IT
join GetWorkParties(:PARTY_ID,:PARTYDATE,null,null,:storageid)
on (IT.PartyID = GetWorkParties.PartyID)
Left join operation on (it.operationid = operation.operationid)
left join quality on (it.BuhQualityid = quality.qualityid)
left join storingreminder_weight(operation.operationsubtype,it.net) on (0=0)
WHERE IT.RegDATE between :date1 and :date2
and IT.storageid = :storageid
and it.parentid >= 0
and IT.state >= 0
and operation.operationsubtype in (0,1)
and ((quality.classid = :classid and :docrop = 0) or (quality.cropid = :classid and :docrop = 1))
and it.net is not null
and ((It.ownerid = :ownerid) or (:ownerid = 0))
and ((it.contractid = :contractid) or (:contractid = 0))
and ((quality.sortid = :sortid) or (:sortid = 0))
group by IT.RegDATE
INTO :income, :outcome, :rdate
do
begin
while (:RDATE_OLD + 1 < :RDATE) do
begin
if (:result is null) then
result = 0;
if (:DIFFERENCE is null) then DIFFERENCE = 0;
result = :result + :DIFFERENCE;
RDATE_OLD = :RDATE_OLD + 1;
end
outcomefromstart = :OUTCOMEFROMSTART + :OUTCOME;
incomefromstart = :INCOMEFROMSTART + :INCOME;
DIFFERENCE = :INCOMEFROMSTART - :OUTCOMEFROMSTART;
if (:DIFFERENCE < 0) then
difference = 0;
result = :result + :DIFFERENCE;
RDATE_OLD = :RDATE;
if (:RDATE = :DATE1) then begin
incomebefore = :Income;
outcomebefore = :OUTCOME;
end
/* if (:RDATE = :date2) then begin
incomeday = :Income;
outcomeday = :OUTCOME;
end*/
end
while (:RDATE_OLD < :date2) do begin
result = :result + :DIFFERENCE;
RDATE_OLD = :RDATE_OLD + 1;
end
if (:incomebefore is null) then incomebefore = 0;
if (:outcomebefore is null) then outcomebefore = 0;
if (:RDATE_OLD <> :date2) then
begin
result = :result - (:incomebefore - :outcomebefore);
reminder = :INCOMEFROMSTART - :OUTCOMEFROMSTART;
end
SUSPEND;
END