BEGIN
For
SELECT
RM.ID as OUT_ROUTINESMAT_ID,
(CAST(RM.Quantity AS DOUBLE PRECISION)
* (CASE WHEN(
(SELECT MAX(A.DoNotMultiply)
FROM PLMReqInputItems A
JOIN PLMReqNodes 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 PLMReqInputItems A
JOIN PLMReqNodes 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.ReqRoutine_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.ReqRoutine_ID = RO.ID AND PV.IsMajor = 'A'
)
ELSE 0
END as OUT_NEEDSDATE,
NMI.ID AS OUT_MasterNode
FROM PLMReqROUTINESMAT RM
JOIN PLMReqRoutines RO ON RO.ID=RM.PARENT_ID
JOIN PLMReqOutputItems MI ON MI.ID = RO.PARENT_ID
JOIN PLMReqNodes NMI ON NMI.ID=MI.Owner_ID
JOIN PLMProduceRequests Req On Req.ID = NMI.Parent_ID
WHERE ((Req.JobOrder_ID IS NULL) OR (Req.JobOrder_ID = '')) 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 (OUT_NEEDSQUANTITY < 0) then
begin
OUT_NEEDSQUANTITY = 0;
end
Suspend;
end
END