BEGIN
mCurrentRoutineQuantity = 0;
mMasterQuantity = 1;
SELECT Sum(O1.QUANTITY) FROM PLMOPERATIONS O1 where O1.JOBORDERSROUTINES_ID = :ARoutine_ID INTO :mCurrentRoutineQuantity;
mFirstroutine = 0;
SELECT MAX(1) FROM PLMJOBORDERSROUTINES R
WHERE R.ID = :AROUTINE_ID
AND (R.PRODUCTPHASE_ID IS NOT NULL OR NOT exists(SELECT 1 FROM PLMJOBORDERSROUTINES R3 WHERE R3.PARENT_ID = R.PARENT_ID AND R3."POSINDEX" > R."POSINDEX"))
AND NOT exists(SELECT 1 FROM PLMJOBORDERSROUTINES R2 WHERE R2.PARENT_ID = R.PARENT_ID AND R2."POSINDEX" < R."POSINDEX" AND R2.PRODUCTPHASE_ID IS NOT NULL)
INTO :mFirstRoutine;
mLastRoutine = 0;
SELECT MAX(1) FROM PLMJOBORDERSROUTINES R
WHERE R.ID = :AROUTINE_ID
AND NOT exists(SELECT 1 FROM PLMJOBORDERSROUTINES R2 WHERE R2.PARENT_ID = R.PARENT_ID AND R2."POSINDEX" > R."POSINDEX")
INTO :mLastRoutine;
mFirstRoutineQuantity = 0;
IF (mLastRoutine = 1) THEN
begin
SELECT R2.ID FROM PLMJOBORDERSROUTINES R
JOIN PLMJOBORDERSROUTINES R2 ON R2.PARENT_ID = R.PARENT_ID AND R2."POSINDEX" = (SELECT MIN(posindex) FROM PLMJOBORDERSROUTINES R3 WHERE R3.PARENT_ID = R.PARENT_ID) --zjednodušení - snad posindex 1 existuje
WHERE
R.id = :AROUTINE_ID INTO :mFirstRoutine_ID;
SELECT Sum(O1.QUANTITY) FROM PLMOPERATIONS O1 where O1.JOBORDERSROUTINES_ID = :mFirstRoutine_ID INTO :mFirstRoutineQuantity;
END
SELECT R2.ID FROM PLMJOBORDERSROUTINES R
JOIN PLMJOBORDERSROUTINES R2 ON R2.PARENT_ID = R.PARENT_ID
AND R2."POSINDEX" = (SELECT MIN("POSINDEX") FROM PLMJOBORDERSROUTINES R3 WHERE R3.PARENT_ID = R.PARENT_ID AND R3.PRODUCTPHASE_ID IS NOT NULL AND R3."POSINDEX" > R."POSINDEX")
WHERE R.ID = :AROUTINE_ID
INTO :mNextRoutine_ID;
--není poslední routine a neexistuje následující routine s pojmenovanou fází - jako next routine se vezme poslední
IF ((mNextRoutine_ID IS NULL) AND (coalesce(mLastRoutine,0) = 0)) THEN
BEGIN
SELECT R2.ID FROM PLMJOBORDERSROUTINES R
JOIN PLMJOBORDERSROUTINES R2 ON R2.PARENT_ID = R.PARENT_ID
AND R2."POSINDEX" = (SELECT MAX("POSINDEX") FROM PLMJOBORDERSROUTINES R3 WHERE R3.PARENT_ID = R.PARENT_ID AND R3."POSINDEX" > R."POSINDEX")
WHERE R.ID = :AROUTINE_ID
INTO :mNextRoutine_ID;
END
mNextRoutineQuantity = 0;
IF (mNextRoutine_ID IS NOT NULL) THEN
SELECT Sum(O1.QUANTITY) FROM PLMOPERATIONS O1 where O1.JOBORDERSROUTINES_ID = :mNextRoutine_ID INTO :mNextRoutineQuantity;
--hledání v nadřazené větvi
IF (mNextRoutine_ID IS NULL) THEN
BEGIN
SELECT COALESCE(SUM(M.QUANTITY * O.QUANTITY),0)
FROM
PLMJOBORDERSROUTINES R
JOIN PLMJOOUTPUTITEMS JOOI ON JOOI.ID = R.PARENT_ID
JOIN PLMJONODES JON ON JON.ID = JOOI.OWNER_ID
JOIN PLMJONODES jon2 ON JON2.PARENT_ID = JON.PARENT_ID
JOIN PLMJOOUTPUTITEMS JOOI2 ON JOOI2.OWNER_ID = JON2.ID
JOIN PLMJOBORDERSROUTINES R2 ON R2.PARENT_ID = JOOI2.ID
JOIN PLMJOROUTINESMAT M ON M.STORECARD_ID = JON.STORECARD_ID AND M.PARENT_ID = R2.ID
JOIN PLMOPERATIONS O ON O.JOBORDERSROUTINES_ID = R2.ID
WHERE
R.ID = :AROUTINE_ID
INTO :mNextRoutineQuantity;
SELECT MIN(R2.ID)
FROM
PLMJOBORDERSROUTINES R
JOIN PLMJOOUTPUTITEMS JOOI ON JOOI.ID = R.PARENT_ID
JOIN PLMJONODES JON ON JON.ID = JOOI.OWNER_ID
JOIN PLMJONODES jon2 ON JON2.PARENT_ID = JON.PARENT_ID
JOIN PLMJOOUTPUTITEMS JOOI2 ON JOOI2.OWNER_ID = JON2.ID
JOIN PLMJOBORDERSROUTINES R2 ON R2.PARENT_ID = JOOI2.ID
JOIN PLMJOROUTINESMAT M ON M.STORECARD_ID = JON.STORECARD_ID AND M.PARENT_ID = R2.ID
WHERE
R.ID = :AROUTINE_ID
INTO :mNextRoutine_ID;
END
mStoreQuantity = 0;
--jedná se o vyráběnou položku na nejvyšší úrovni, poslední pojmenovaná fáze
IF (mNextRoutine_ID IS NULL) THEN
BEGIN
SELECT sum(PHV."QUANTITY") FROM
PLMJOBORDERSROUTINES JOR
JOIN PLMJOOUTPUTITEMS JOOI ON JOOI.ID = JOR.PARENT_ID
JOIN PLMJONODES JON ON JON.ID = JOOI.OWNER_ID
JOIN plmjoborders JO ON JO.id = JON.PARENT_ID
JOIN STOREDOCUMENTS2 PHV ON PHV.FLOWTYPE ='28' AND PHV.ProductionTask_ID = JO.ProductionTask_ID
WHERE jor.id =:ARoutine_ID
INTO :mStoreQuantity;
mNextRoutineQuantity = mStoreQuantity;
mNextRoutine_ID = 'SKLAD';
END
IF (mNextRoutineQuantity IS NULL) THEN
mNextRoutineQuantity = 0;
mRemainingQuantity = mCurrentRoutineQuantity - mNextRoutineQuantity;
--mRemainingQuantity je potřeba rozpočítat do úkonů ARoutine_ID
For
SELECT
WSI.ID AS WSI_ID,
SUM(COALESCE(O."QUANTITY",0)) AS WSIQuantity
FROM
PLMWORKSCHEDULEITEMS WSI
LEFT JOIN PLMOperationWSIs O ON O.WORKSCHEDULEITEM_ID = WSI.ID --tady left join
WHERE JOROUTINE_ID = :ARoutine_ID
GROUP BY WSI.ID
-- HAVING SUM(O."QUANTITY") > 0 --tady zakomentovat
ORDER BY WSI.ID desc
INTO
WSI_ID,
WSIQuantity
do
BEGIN
NEXT_ID = mNextRoutine_ID;
IF (WSIQuantity <= MREMAININGQUANTITY) THEN
BEGIN
MREMAININGQUANTITY = MREMAININGQUANTITY - WSIQuantity;
END
ELSE
BEGIN
WSIQuantity = MREMAININGQUANTITY;
MREMAININGQUANTITY = 0;
END
CurrentRoutineQuantity = mCurrentRoutineQuantity;
NextRoutineQuantity = mNextRoutineQuantity;
ReturnRowType = 1;
SUSPEND;
END
-- přidání řádku se stavem skladu
IF ((mNextRoutine_ID = 'SKLAD') AND (mLastRoutine = 1)) THEN
BEGIN
WSI_ID = NULL;
NEXT_ID = mNextRoutine_ID;
ReturnRowType = 2;
WSIQuantity = mStoreQuantity;
CurrentRoutineQuantity = mStoreQuantity;
NextRoutineQuantity = 0;
SUSPEND;
END
--IF (mLastRoutine=1) THEN--připlácnutí materiálů
--MATERIALY ZAOKROUHLENE
BEGIN
mNextRoutineQuantity = mCurrentRoutineQuantity;
For
SELECT
DISTINCT
JON2.STORECARD_ID,
Round(CAST(M.QUANTITY /
(SELECT sum(M2.Quantity) FROM
PLMJOBORDERSROUTINES R2
JOIN PLMJOROUTINESMAT M2 ON M2.PARENT_ID = R2.ID AND M2.STORECARD_ID = M.STORECARD_ID
WHERE R2.PARENT_ID = R.PARENT_ID
) AS numeric(15,6))
*
(
COALESCE( (SELECT SUM(SD2.QUANTITY)
FROM PLMMIPLMATERIALDISTRIB MD
JOIN STOREDOCUMENTS2 SD2 ON SD2.ID = MD.STOREDOCUMENT2_ID
JOIN STOREDOCUMENTS SD ON SD.ID = SD2.PARENT_ID AND SD.PMSTATE_ID IN (SELECT ID FROM PMSTATES PMS WHERE SYSTEMSTATE = 3)
WHERE MD.PARENT_ID = JOII.ID
),0)
-
COALESCE( (SELECT SUM(SD2.QUANTITY)
FROM PLMMIPLRMATERIALDISTRIB RMD
JOIN STOREDOCUMENTS2 SD2 ON SD2.ID = RMD.STOREDOCUMENT2_ID
JOIN STOREDOCUMENTS SD ON SD.ID = SD2.PARENT_ID AND SD.PMSTATE_ID IN (SELECT ID FROM PMSTATES PMS WHERE SYSTEMSTATE = 3)
WHERE RMD.PARENT_ID = JOII.ID
) ,0)
),
--nic jednodussiho me nenapadlo
CASE WHEN SU.INDIVISIBLEQUANTITY < 0.00001 THEN 6
WHEN SU.INDIVISIBLEQUANTITY BETWEEN 0.00001 AND 0.000099 THEN 5
WHEN SU.INDIVISIBLEQUANTITY BETWEEN 0.0001 AND 0.000999 THEN 4
WHEN SU.INDIVISIBLEQUANTITY BETWEEN 0.001 AND 0.009999 THEN 3
WHEN SU.INDIVISIBLEQUANTITY BETWEEN 0.01 AND 0.099999 THEN 2
WHEN SU.INDIVISIBLEQUANTITY BETWEEN 0.1 AND 0.99999 THEN 1
WHEN SU.INDIVISIBLEQUANTITY BETWEEN 1 AND 9.999999 THEN 0
WHEN SU.INDIVISIBLEQUANTITY BETWEEN 10 AND 99.999999 THEN -1
WHEN SU.INDIVISIBLEQUANTITY BETWEEN 100 AND 999.999999 THEN -2
WHEN SU.INDIVISIBLEQUANTITY > 999.999999 THEN -3
END ),
M.QUANTITY *:mNextRoutineQuantity
FROM PLMJOBORDERSROUTINES R
JOIN PLMJOOUTPUTITEMS JOOI ON JOOI.ID = R.PARENT_ID
JOIN PLMJONODES JON ON JON.ID = JOOI.OWNER_ID
JOIN PLMJONODES JON2 ON JON2.MASTER_ID = JON.ID
JOIN PLMJOINPUTITEMS JOII ON JOII.OWNER_ID = JON2.ID
JOIN PLMJOROUTINESMAT M ON M.PARENT_ID = R.ID AND M.STORECARD_ID = JON2.STORECARD_ID
LEFT JOIN STOREUNITS SU ON SU.PARENT_ID = JON2.STORECARD_ID AND SU."UNITRATE" = 1
WHERE
R.ID = :AROUTINE_ID
AND JON2.ISSUE <> 1 --není to výroba
AND M.Quantity <> 0
AND M.ID <> (SELECT MAX(M3.ID)
FROM PLMJOBORDERSROUTINES R3
JOIN PLMJOROUTINESMAT M3 ON M3.PARENT_ID = R3.ID
WHERE R3.PARENT_ID = R.PARENT_ID AND M3.STORECARD_ID = M.STORECARD_ID)
INTO
WSI_ID,
:mCurrentRoutineQuantity,
:mRemainingQuantity --zneužití proměnné
do
BEGIN
NEXT_ID = mNextRoutine_ID;
ReturnRowType = 5;
CurrentRoutineQuantity = mCurrentRoutineQuantity;
NextRoutineQuantity = mRemainingQuantity;
WSIQuantity = COALESCE(mCurrentRoutineQuantity, 0) - COALESCE(mRemainingQuantity, 0); --rozdíl předchozích dvou
SUSPEND;
END
END
--MATERIAL DOPOCITANY
BEGIN
-- mNextRoutineQuantity = mCurrentRoutineQuantity;
For
SELECT
DISTINCT
JON2.STORECARD_ID,
(
COALESCE( (SELECT SUM(SD2.QUANTITY)
FROM PLMMIPLMATERIALDISTRIB MD
JOIN STOREDOCUMENTS2 SD2 ON SD2.ID = MD.STOREDOCUMENT2_ID
JOIN STOREDOCUMENTS SD ON SD.ID = SD2.PARENT_ID AND SD.PMSTATE_ID IN (SELECT ID FROM PMSTATES PMS WHERE SYSTEMSTATE = 3)
WHERE MD.PARENT_ID = JOII.ID
),0)
-
COALESCE( (SELECT SUM(SD2.QUANTITY)
FROM PLMMIPLRMATERIALDISTRIB RMD
JOIN STOREDOCUMENTS2 SD2 ON SD2.ID = RMD.STOREDOCUMENT2_ID
JOIN STOREDOCUMENTS SD ON SD.ID = SD2.PARENT_ID AND SD.PMSTATE_ID IN (SELECT ID FROM PMSTATES PMS WHERE SYSTEMSTATE = 3)
WHERE RMD.PARENT_ID = JOII.ID
),0)
)
--odecteni souctu zaokrouhlenych mnostvi materialu tak, aby vysledek sedel na storedoc2
-
( SELECT SUM(ROUNDEDQUANTITY)
FROM (
SELECT DISTINCT
M.ID,
JON2.STORECARD_ID,
Round(CAST(M.QUANTITY /
(SELECT sum(M2.Quantity) FROM
PLMJOBORDERSROUTINES R2
JOIN PLMJOROUTINESMAT M2 ON M2.PARENT_ID = R2.ID AND M2.STORECARD_ID = M.STORECARD_ID --AND M2.ID <> M.ID
WHERE R2.PARENT_ID = R.PARENT_ID
) AS numeric(15,6))
*
(
COALESCE( (SELECT SUM(SD2.QUANTITY)
FROM PLMMIPLMATERIALDISTRIB MD
JOIN STOREDOCUMENTS2 SD2 ON SD2.ID = MD.STOREDOCUMENT2_ID
JOIN STOREDOCUMENTS SD ON SD.ID = SD2.PARENT_ID AND SD.PMSTATE_ID IN (SELECT ID FROM PMSTATES PMS WHERE SYSTEMSTATE = 3)
WHERE MD.PARENT_ID = JOII.ID
),0)
-
COALESCE( (SELECT SUM(SD2.QUANTITY)
FROM PLMMIPLRMATERIALDISTRIB RMD
JOIN STOREDOCUMENTS2 SD2 ON SD2.ID = RMD.STOREDOCUMENT2_ID
JOIN STOREDOCUMENTS SD ON SD.ID = SD2.PARENT_ID AND SD.PMSTATE_ID IN (SELECT ID FROM PMSTATES PMS WHERE SYSTEMSTATE = 3)
WHERE RMD.PARENT_ID = JOII.ID
) ,0)
),
--nic jednodussiho me nenapadlo
CASE WHEN SU.INDIVISIBLEQUANTITY < 0.00001 THEN 6
WHEN SU.INDIVISIBLEQUANTITY BETWEEN 0.00001 AND 0.000099 THEN 5
WHEN SU.INDIVISIBLEQUANTITY BETWEEN 0.0001 AND 0.000999 THEN 4
WHEN SU.INDIVISIBLEQUANTITY BETWEEN 0.001 AND 0.009999 THEN 3
WHEN SU.INDIVISIBLEQUANTITY BETWEEN 0.01 AND 0.099999 THEN 2
WHEN SU.INDIVISIBLEQUANTITY BETWEEN 0.1 AND 0.99999 THEN 1
WHEN SU.INDIVISIBLEQUANTITY BETWEEN 1 AND 9.999999 THEN 0
WHEN SU.INDIVISIBLEQUANTITY BETWEEN 10 AND 99.999999 THEN -1
WHEN SU.INDIVISIBLEQUANTITY BETWEEN 100 AND 999.999999 THEN -2
WHEN SU.INDIVISIBLEQUANTITY > 999.999999 THEN -3
END
) AS ROUNDEDQUANTITY
FROM PLMJOBORDERSROUTINES R2
JOIN PLMJOBORDERSROUTINES R ON R.PARENT_ID = R2.PARENT_ID
JOIN PLMJOOUTPUTITEMS JOOI ON JOOI.ID = R.PARENT_ID
JOIN PLMJONODES JON ON JON.ID = JOOI.OWNER_ID
JOIN PLMJONODES JON2 ON JON2.MASTER_ID = JON.ID
JOIN PLMJOINPUTITEMS JOII ON JOII.OWNER_ID = JON2.ID
JOIN PLMJOROUTINESMAT M ON M.PARENT_ID = R.ID AND M.STORECARD_ID = JON2.STORECARD_ID
LEFT JOIN STOREUNITS SU ON SU.PARENT_ID = JON2.STORECARD_ID AND SU."UNITRATE" = 1
WHERE
R2.ID = :AROUTINE_ID
AND JON2.ISSUE <> 1 --není to výroba
AND M.Quantity <> 0
AND M.ID <> (SELECT MAX(M3.ID)
FROM PLMJOBORDERSROUTINES R3
JOIN PLMJOROUTINESMAT M3 ON M3.PARENT_ID = R3.ID
WHERE R3.PARENT_ID = R.PARENT_ID AND M3.STORECARD_ID = M.STORECARD_ID)
) SUBSELECT1
)
--konec sčítání zaokrouhlených množství z jiných routine
,
M.QUANTITY *:mNextRoutineQuantity
FROM PLMJOBORDERSROUTINES R
JOIN PLMJOOUTPUTITEMS JOOI ON JOOI.ID = R.PARENT_ID
JOIN PLMJONODES JON ON JON.ID = JOOI.OWNER_ID
JOIN PLMJONODES JON2 ON JON2.MASTER_ID = JON.ID
JOIN PLMJOINPUTITEMS JOII ON JOII.OWNER_ID = JON2.ID
JOIN PLMJOROUTINESMAT M ON M.PARENT_ID = R.ID AND M.STORECARD_ID = JON2.STORECARD_ID
WHERE
R.ID = :AROUTINE_ID
AND JON2.ISSUE <> 1 --není to výroba
AND M.Quantity <> 0
AND M.ID = (SELECT MAX(M3.ID)
FROM PLMJOBORDERSROUTINES R3
JOIN PLMJOROUTINESMAT M3 ON M3.PARENT_ID = R3.ID
WHERE R3.PARENT_ID = R.PARENT_ID AND M3.STORECARD_ID = M.STORECARD_ID)
INTO
WSI_ID,
:mCurrentRoutineQuantity,
:mRemainingQuantity --zneužití proměnné
do
BEGIN
NEXT_ID = mNextRoutine_ID;
ReturnRowType = 5;
CurrentRoutineQuantity = mCurrentRoutineQuantity;
NextRoutineQuantity = mRemainingQuantity;
WSIQuantity = COALESCE(mCurrentRoutineQuantity, 0) - COALESCE(mRemainingQuantity, 0); --rozdíl předchozích dvou
SUSPEND;
END
END
END;