Třídová akce

Popis:

Vrátí data z pracovních lístků komponent výroby

Kód:

10

Tělo:

SELECT * from (
WITH RECURSIVE CTE_query(JOBORDERSSN_ID) AS 
(
  SELECT SN2.ID
  FROM PLMJOBORDERSSN SN
  JOIN PLMJOBORDERSCOMPONENTS JC ON JC.PARENT_ID = SN.ID
  JOIN PLMJOBORDERSSN SN2 ON SN2.STOREBATCH_ID = JC.COMPONENT_ID 
  WHERE SN.ID = :JobOrdersSN_ID
	
  UNION ALL
	
   SELECT SN3.ID
   FROM PLMJOBORDERSCOMPONENTS JC2
   JOIN PLMJOBORDERSSN SN3 ON SN3.STOREBATCH_ID = JC2.COMPONENT_ID  
   JOIN CTE_query Q ON Q.JOBORDERSSN_ID = JC2.PARENT_ID 
)
SELECT OP.PERFORMEDBY_ID, OP.WORKPLACE_ID, OP.JOBORDERSROUTINES_ID,JR.TITLE, WP.NAME, P.FIRSTNAME, P.LASTNAME, SUM(OP.QUANTITY) as suma,MIN(OP.STARTEDAT$DATE) as startdate,MAX(OP.FINISHEDAT$DATE) as finishdate,SUM(OP.TOTALTIME) as duration, SB.NAME as batchname, SC.NAME as storecardname, JO.ORDNUMBER, DQ.CODE as quecode, PR.CODE,JR.BATCH FROM PLMJOBORDERSSN SN4
JOIN CTE_query CR ON CR.JOBORDERSSN_ID = SN4.ID
JOIN PLMJOOUTPUTITEMS OI ON OI.ID = SN4.PARENT_ID
JOIN PLMJONODES JN ON JN.ID = OI.OWNER_ID
JOIN PLMJOBORDERSROUTINES JR ON JR.PARENT_ID = OI.ID 
JOIN PLMOPERATIONS OP ON OP.JOBORDERSROUTINES_ID = JR.ID
JOIN PLMWORKPLACES WP ON WP.ID = OP.WORKPLACE_ID
JOIN PLMWORKERS WO ON WO.ID = OP.PERFORMEDBY_ID
JOIN PERSONS P ON P.ID = WO.PERSON_ID
JOIN STORECARDS SC ON SC.ID = JN.STORECARD_ID
JOIN PLMJOBORDERS JO ON JO.ID = JN.PARENT_ID
JOIN STOREBATCHES SB ON SB.ID = SN4.STOREBATCH_ID
JOIN PERIODS PR ON PR.ID = JO.PERIOD_ID
JOIN DOCQUEUES DQ ON DQ.ID = JO.DOCQUEUE_ID
WHERE (OP.JOBORDERSSN_ID = SN4.ID) OR (JR.BATCH = 'A')
GROUP BY OP.PERFORMEDBY_ID, OP.WORKPLACE_ID, OP.JOBORDERSROUTINES_ID, JR.POSINDEX, JR.TITLE, WP.NAME, P.FIRSTNAME, P.LASTNAME,SB.NAME, SC.NAME, JO.ORDNUMBER, DQ.CODE, PR.CODE,JR.BATCH
)

Generated by ABRA Software a.s. 27.10.2021 16:34:49