BEGIN
grStoreCard = '?';
For
SELECT
RM.ID as OUT_ROUTINESMAT_ID,
(CAST(RM.Quantity AS DOUBLE PRECISION)
* (CASE WHEN(
(SELECT MAX(A.DoNotMultiply)
FROM PLMJOInputItems A
JOIN PLMJONodes N ON (A.Owner_ID = N.ID)
WHERE (N.StoreCard_ID = RM.StoreCard_ID) AND (N.MASTER_ID=NMI.ID))
='A') THEN 1.000 ELSE CAST(MI.Quantity AS DOUBLE PRECISION) END)
* CAST((1 + (
(SELECT MAX(A.WastePercentage)
FROM PLMJOInputItems A
JOIN PLMJONodes N ON (A.Owner_ID = N.ID)
WHERE (N.StoreCard_ID = RM.StoreCard_ID) AND (N.MASTER_ID=NMI.ID))
/ 100)) AS DOUBLE PRECISION)
) AS RMQuantity,
RM.STORECARD_ID AS OUT_MaterialStoreCardID,
CASE
WHEN
( SELECT Min(PV2.BeginTime$date) FROM CRPPlanVariants2 PV2
JOIN CRPPlanVariants PV ON (PV2.Parent_ID = PV.ID)
JOIN PLMWorkScheduleItems WSI ON (PV2.WorkScheduleItem_ID = WSI.ID)
WHERE WSI.JORoutine_ID = RO.ID AND PV.IsMajor = 'A'
) > 0 THEN
( SELECT Min(PV2.BeginTime$date) FROM CRPPlanVariants2 PV2
JOIN CRPPlanVariants PV ON (PV2.Parent_ID = PV.ID)
JOIN PLMWorkScheduleItems WSI ON (PV2.WorkScheduleItem_ID = WSI.ID)
WHERE WSI.JORoutine_ID = RO.ID AND PV.IsMajor = 'A'
)
ELSE 0
END as OUT_NEEDSDATE,
NMI.ID AS OUT_MasterNode
FROM PLMJOROUTINESMAT RM
JOIN PLMJobOrdersRoutines RO ON RO.ID=RM.PARENT_ID
JOIN PLMJOOutputItems MI ON MI.ID = RO.PARENT_ID
JOIN PLMJONodes NMI ON NMI.ID=MI.Owner_ID
JOIN PLMJOBORDERS JO On JO.ID = NMI.PArent_ID
WHERE (JO.FinishedAt$DATE = 0) AND (RM.UNUSEDMAT='N')
AND (RM.ID = :AROUTINESMAT_ID)
ORDER BY NMI.StoreCard_ID, RM.STORECARD_ID, OUT_NEEDSDATE
INTO
OUT_ROUTINESMAT_ID ,
OUT_NEEDSQUANTITY ,
OUT_MaterialStoreCardID ,
OUT_NEEDSDATE ,
OUT_MasterNode
DO BEGIN
IF (grStoreCard != :OUT_MasterNode || :OUT_MaterialStoreCardID) THEN
begin
grStoreCard = :OUT_MasterNode || :OUT_MaterialStoreCardID;
issuedQuantity = 0;
returnedQuantity = 0;
SELECT COALESCE(SUM(CAST((SD2.Quantity) AS DOUBLE PRECISION)), 0)
FROM StoreDocuments2 SD2
JOIN PLMMIPLMaterialDistrib MD ON MD.StoreDocument2_ID = SD2.ID
JOIN PLMJOINPUTITEMS II ON MD.Parent_ID = II.ID
JOIN PLMJONodes N ON N.ID = II.OWNER_ID
WHERE (N.Master_ID = :OUT_MasterNode) AND (N.STORECARD_ID = :OUT_MaterialStoreCardID)
GROUP BY N.STORECARD_ID
INTO issuedQuantity;
SELECT COALESCE(SUM(CAST((SD2.Quantity) AS DOUBLE PRECISION)), 0)
FROM StoreDocuments2 SD2
JOIN PLMMIPLRMaterialDistrib RMD ON RMD.StoreDocument2_ID = SD2.ID
JOIN PLMJOINPUTITEMS II ON RMD.Parent_ID = II.ID
JOIN PLMJONodes N ON N.ID = II.OWNER_ID
WHERE (N.Master_ID = :OUT_MasterNode) AND (N.STORECARD_ID = :OUT_MaterialStoreCardID)
GROUP BY N.STORECARD_ID
INTO returnedQuantity;
remainingQuantity = issuedQuantity - returnedQuantity;
end
if (OUT_NEEDSQUANTITY <= remainingQuantity) then
begin
OUT_NEEDSQUANTITY = 0;
end
else
begin
OUT_NEEDSQUANTITY = OUT_NEEDSQUANTITY - remainingQuantity;
end
Suspend;
end
END