BEGIN
SELECT Result FROM GetParameterValue('FOLNYX32RHV4P5CP55PSBN2214', 0) INTO mProcessManagementOnSD;
mRecOrderConfirmedValue = 'N';
if (ARecOrderConfirmed = 1) then mRecOrderConfirmedValue = 'A';
mRecOrderClosedValue = 'N';
if (ARecOrderClosed = 1) then mRecOrderClosedValue = 'A';
mIssOrderConfirmedValue = 'N';
if (AIssOrderConfirmed = 1) then mIssOrderConfirmedValue = 'A';
mIssOrderIssuedValue = 'N';
if (AIssOrderIssued = 1) then mIssOrderIssuedValue = 'A';
mIssOrderClosedValue = 'N';
if (AIssOrderClosed = 1) then mIssOrderClosedValue = 'A';
mUseRecOrderDate = 'N';
mUseOrderBusTransaction = 'N';
mUseOrderBusOrder = 'N';
mUseOrderDivision = 'N';
mUseResPriority = 'N';
mUseResDates = 'N';
mUseRecOrderDeliveryDate = 'N';
mUseIssOrderDeliveryDate = 'N';
if (ARODateFrom > 0 and ARODateTo > 0) then mUseRecOrderDate = 'A';
if (AbusTransactionSel_ID <> '') then mUseOrderBusTransaction = 'A';
if (ABusOrderSel_ID <> '') then mUseOrderBusOrder = 'A';
if (ADivisionSel_ID <> '') then mUseOrderDivision = 'A';
if (APriorityFrom > -1 or APriorityTo > -1) then mUseResPriority = 'A';
if (ADateFrom > 0 or ADateTo > 0) then mUseResDates = 'A';
if (ARODeliveryDateFrom > 0 and ARODeliveryDateTo > 0) then mUseRecOrderDeliveryDate = 'A';
if (AIODeliveryDateFrom > 0 and AIODeliveryDateTo > 0) then mUseIssOrderDeliveryDate = 'A';
FOR
select A.Obj_ID, B.Obj_ID
from SelDat A, SelDat B
where A.Sel_ID = :AStoreSel_ID and B.Sel_ID = :AStoreCardSel_ID
into :Store_ID, :StoreCard_ID
DO BEGIN
Supplier_ID = null;
mTargetQuantity = 0;
mStoreQuantity = 0;
mMinusQuantity = 0;
mIssmMinusQuantity = 0;
Quantity = 0;
mLowLimitQuantityV = 0;
mHighLimitQuantityV = 0;
-- Ve výchozím stavu při načítání množství skladu nezohledňujeme množství na nevyřízených procesních skladových dokladech (- BookedQuantity + AcceptedQuantity)
select ID, Quantity, LowLimitQuantity, HighLimitQuantity
from StoreSubCards
where Store_ID = :Store_ID and StoreCard_ID = :StoreCard_ID
into mStoreSubCard_ID, mStoreQuantity, mLowLimitQuantityV, mHighLimitQuantityV;
-- Při zapnutém procesním řízení k množství skladu připočítáme množství na všech procesních skladových dokladech se stavy "V přípravě" a "Plánováno", pokud maji na procesním stavu nastaven příznak CountStatus
if (mProcessManagementOnSD = 1) then begin
SELECT Coalesce(Sum(SD2.Quantity * SD2.FlowSign), 0) FROM
StoreDocuments SD
JOIN StoreDocuments2 SD2 ON SD2.Parent_ID = SD.ID
JOIN PMStates PMS ON SD2.PMState_ID = PMS.ID
WHERE
SD2.RowType = 3 AND SD2.StoreCard_ID = :StoreCard_ID AND SD2.Store_ID = :Store_ID AND
(PMS.SystemState = 1 or PMS.SystemState = 2) AND PMS.CountStatus = 'A'
INTO mFlowStoreQuantity;
IF (mFlowStoreQuantity IS NULL) THEN
mFlowStoreQuantity = 0;
mStoreQuantity = mStoreQuantity + mFlowStoreQuantity;
end
if (ATarget = 3 or ATarget = 4) then
mStoreQuantity = 0;
if (ATarget = 1 or ATarget = 3) then begin
select SUM(
CASE
WHEN A.Quantity - A.DeliveredQuantity > 0 THEN A.Quantity - A.DeliveredQuantity
ELSE 0
END
)
from
ReceivedOrders2 A
JOIN ReceivedOrders RO ON A.Parent_ID = RO.ID AND (RO.DocQueue_ID IN (SELECT OBJ_ID FROM SELDAT WHERE SEL_ID = :ARecOrderDocQueueSel_ID))
where (A.Quantity > 0) AND (A.RowType = 3) AND
(:ARecOrderConfirmed = 0 or (A.Parent_ID in (select ID from ReceivedOrders where Revided_ID is null and (Confirmed = :mRecOrderConfirmedValue)))) and
(:ARecOrderClosed = 0 or (A.Parent_ID in (select ID from ReceivedOrders where Revided_ID is null and (Closed = :mRecOrderClosedValue)))) and
(:mUseRecOrderDate = 'N' or A.Parent_ID in (select ID from ReceivedOrders where Revided_ID is null and (DocDate$Date BETWEEN :ARODateFrom and :ARODateTo))) and
(:mUseRecOrderDeliveryDate = 'N' or (A.DeliveryDate$Date BETWEEN :ARODeliveryDateFrom and :ARODeliveryDateTo)) and
( (:mUseOrderDivision = 'N') OR
(A.Division_ID IN (SELECT Bx.ID FROM Divisions Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :ADivisionSel_ID)) OR
((:ADivisionsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$Divisions2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :ADivisionSel_ID)))))) )
) AND
( (:mUseOrderBusOrder = 'N') OR
(A.BusOrder_ID IN (SELECT Bx.ID FROM BusOrders Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :ABusOrderSel_ID)) OR
((:ABusOrdersWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusOrders2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :ABusOrderSel_ID)))))) )
) AND
( (:mUseOrderBusTransaction = 'N') OR
(A.BusTransaction_ID IN (SELECT Bx.ID FROM BusTransactions Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :AbusTransactionSel_ID)) OR
((:ABusTransactionsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusTransactions2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :AbusTransactionSel_ID)))))) )
) AND
( (:AExcludeConnectedToIO = 'N') OR NOT EXISTS(SELECT * FROM ReceivedOrdersToIssuedOrders RIO WHERE RIO.Source_ID = A.ID)
) AND
A.Store_ID = :Store_ID and A.StoreCard_ID = :StoreCard_ID
into mTargetQuantity;
end
if (ATarget = 2 or ATarget = 4) then begin
select Sum(Reserved - Supplied)
from Reservations R
where (Reserved > Supplied) AND
(:mUseResPriority = 'N' or (R.Priority BETWEEN :APriorityFrom and :APriorityTo)) and
(:mUseResDates = 'N' or
(
/* dolni mez je v intervalu */
((R.DateFrom$Date >= :ADateFrom) AND (:ADateFrom < R.DateTo$Date) AND (:ADateTo > R.DateFrom$Date)) OR
/* horni mez je uvnitr */
((:ADateFrom <= R.DateTo$Date) AND (R.DateTo$Date < :ADateTo)) OR
/* rezervace je okolo meho intervalu */
((R.DateFrom$Date < :ADateFrom) AND (:ADateTo <= R.DateTo$Date)) OR
/* horni mez rezervace neni omezena */
((R.DateTo$Date = 0) AND (R.DateFrom$Date < :ADateTo))
) ) AND
R.Store_ID = :Store_ID and R.StoreCard_ID = :StoreCard_ID and
R.Revided_ID is null
into mTargetQuantity;
end
IF (mTargetQuantity IS NULL) THEN
mTargetQuantity = 0;
Quantity = mTargetQuantity - mStoreQuantity;
if (ARegardIssuedOrders <> 'N') then begin
select SUM(
CASE
WHEN A.Quantity - A.DeliveredQuantity > 0 THEN A.Quantity - A.DeliveredQuantity
ELSE 0
END
)
from
IssuedOrders2 A
JOIN IssuedOrders IO ON A.Parent_ID = IO.ID AND (IO.DocQueue_ID IN (SELECT OBJ_ID FROM SELDAT WHERE SEL_ID = :AIssOrderDocQueueSel_ID))
where (A.Quantity > 0) AND (A.RowType = 3) AND
(:AIssOrderConfirmed = 0 or (A.Parent_ID in (select ID from IssuedOrders where Revided_ID is null and (Confirmed = :mIssOrderConfirmedValue)))) and
(:AIssOrderIssued = 0 or (A.Parent_ID in (select ID from IssuedOrders where Revided_ID is null and (Issued = :mIssOrderIssuedValue)))) and
(:AIssOrderClosed = 0 or (A.Parent_ID in (select ID from IssuedOrders where Revided_ID is null and (Closed = :mIssOrderClosedValue)))) and
(:mUseIssOrderDeliveryDate = 'N' or (A.DeliveryDate$Date BETWEEN :AIODeliveryDateFrom and :AIODeliveryDateTo)) and
( (:mUseOrderDivision = 'N') OR
(A.Division_ID IN (SELECT Bx.ID FROM Divisions Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :ADivisionSel_ID)) OR
((:ADivisionsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$Divisions2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :ADivisionSel_ID)))))) )
) AND
( (:mUseOrderBusOrder = 'N') OR
(A.BusOrder_ID IN (SELECT Bx.ID FROM BusOrders Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :ABusOrderSel_ID)) OR
((:ABusOrdersWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusOrders2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :ABusOrderSel_ID)))))) )
) AND
( (:mUseOrderBusTransaction = 'N') OR
(A.BusTransaction_ID IN (SELECT Bx.ID FROM BusTransactions Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :AbusTransactionSel_ID)) OR
((:ABusTransactionsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusTransactions2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :AbusTransactionSel_ID)))))) )
) AND
( (:AExcludeConnectedToIO = 'N') OR NOT EXISTS(SELECT * FROM ReceivedOrdersToIssuedOrders RIO WHERE RIO.Target_ID = A.ID)
) AND
A.Store_ID = :Store_ID and A.StoreCard_ID = :StoreCard_ID
into mIssmMinusQuantity;
IF (mIssmMinusQuantity IS NULL) THEN
mIssmMinusQuantity = 0;
if (mIssmMinusQuantity > 0) then Quantity = Quantity - mIssmMinusQuantity;
end
SELECT SUM(O.RequestedQuantity)
FROM OrdersRequests O
JOIN PMStates PMS ON PMS.ID = O.PMState_ID
WHERE O.Store_ID = :Store_ID AND O.StoreCard_ID = :StoreCard_ID AND O.RequestedQuantity > 0 AND (PMS.SystemState = 8)
INTO mMinusQuantity;
IF (mMinusQuantity IS NULL) THEN
mMinusQuantity = 0;
if (mMinusQuantity > 0) then
Quantity = Quantity - mMinusQuantity;
mLimitQuantityRate = ALimitQuantity/100;
if (ALowLimit = 'A' and AHighLimit = 'N') then begin
mLimitAuxQuantity = mLowLimitQuantityV * mLimitQuantityRate;
Quantity = Cast((Quantity + mLimitAuxQuantity) as Numeric(15,6));
end
if (AHighLimit = 'A' and ALowLimit = 'N') then begin
mLimitAuxQuantity = mHighLimitQuantityV * mLimitQuantityRate;
Quantity = Cast((Quantity + mLimitAuxQuantity) as Numeric(15,6));
end
if (AHighLimit = 'A' and ALowLimit = 'A') then begin
mLimitAuxQuantity = ((mLowLimitQuantityV + mHighLimitQuantityV)/2) * mLimitQuantityRate;
Quantity = Cast((Quantity + mLimitAuxQuantity) as Numeric(15,6));
end
if (AMainSupplier = 'A') then begin
select ID
from Suppliers S
where S.ID in (select MainSupplier_ID from StoreCards where MainSupplier_ID is not null and ID = :StoreCard_ID) and S.StoreCard_ID = :StoreCard_ID
into Supplier_ID;
end
if (ALastSupplier = 'A') then begin
select Max(PurchaseDate$Date)
from Suppliers S
where S.StoreCard_ID = :StoreCard_ID
into mMaxDate;
select Max(ID) /* select muze vratit vice nez jeden zaznam pokud zadam vice dodavatelu ve stejny cas */
from Suppliers S
where PurchaseDate$Date = :mMaxDate and S.StoreCard_ID = :StoreCard_ID
into Supplier_ID;
end
if (Quantity > 0) then
Suspend;
END
end;