Třídová akce

Popis:

Nabízené komponenty - Sér.čísla vs. šarže; jen vydané vs. všechny

Kód:

6

Tělo:

--vydane komponenty sériová čísla
SELECT SC.ID, SB.ID, JORM.QUANTITY, JORM.QUNIT, SC.CODE, SB.NAME, SC.NAME, 1 AS UNIONPART 
   FROM
   PLMJobOrdersRoutines JOR
   JOIN PLMJOROUTINESMAT JORM ON JORM.PARENT_ID = JOR.ID
   JOIN STORECARDS SC ON SC.ID = JORM.STORECARD_ID   
   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 SD2 ON SD2.PRODUCTIONTASK_ID = JO.PRODUCTIONTASK_ID AND SD2.STORECARD_ID = JORM.STORECARD_ID  AND SD2.FLOWTYPE = 27
   JOIN DOCROWBATCHES DRB ON DRB.PARENT_ID = SD2.ID
   JOIN STOREBATCHES SB ON SB.ID = DRB.STOREBATCH_ID
   LEFT JOIN PLMJOBORDERSCOMPONENTS JOC ON JOC.COMPONENT_ID = SB.ID
   WHERE
     (
     (:RoutineOID <> '0000000000' AND JOR.ID = :RoutineOID) 
      OR
     (:RoutineOID = '0000000000' AND JOR.PARENT_ID = :JOOIOID)
     )       
     AND SC.CATEGORY = 1
     AND JOC.ID IS NULL
     AND SB.ExpirationDate$Date >= :DateFrom
UNION     
--vydane komponenty šarže
SELECT SC.ID, SB.ID, 0 AS QUANTITY,
	DRB.QUNIT, SC.CODE, SB.NAME, SC.NAME, 2 AS UNIONPART 
   FROM
   PLMJOBORDERSROUTINES JOR
   JOIN PLMJOROUTINESMAT JORM ON JORM.PARENT_ID = JOR.ID
   JOIN STORECARDS SC ON SC.ID = JORM.STORECARD_ID   
   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 SD2 ON SD2.PRODUCTIONTASK_ID = JO.PRODUCTIONTASK_ID AND SD2.STORECARD_ID = JORM.STORECARD_ID  AND SD2.FLOWTYPE = 27
   JOIN DOCROWBATCHES DRB ON DRB.PARENT_ID = SD2.ID
   JOIN STOREBATCHES SB ON SB.ID = DRB.STOREBATCH_ID
   WHERE
     (
     (:RoutineOID <> '0000000000' AND JOR.ID = :RoutineOID) 
      OR
     (:RoutineOID = '0000000000' AND JOR.PARENT_ID = :JOOIOID)
     )       
     AND SC.CATEGORY = 2
     AND SB.ExpirationDate$Date >= :DateFrom
UNION -- všechny komponenty - materiály i polotovary - sériová čísla
SELECT SC.ID, SB.ID, JORM.QUANTITY as QUANTITY, JORM.QUNIT, SC.CODE, SB.NAME, SC.NAME, 3 AS UNIONPART 
   FROM
   PLMJOBORDERSROUTINES JOR
   JOIN PLMJOROUTINESMAT JORM ON JORM.PARENT_ID = JOR.ID
   JOIN STORECARDS SC ON SC.ID = JORM.STORECARD_ID   
   JOIN STOREBATCHES SB ON SB.STORECARD_ID = JORM.STORECARD_ID
   LEFT JOIN PLMJOBORDERSCOMPONENTS JOC ON JOC.COMPONENT_ID = SB.ID
   LEFT JOIN PLMJOOUTPUTITEMS JOOI ON JOOI.ID  = JOR.PARENT_ID 
   LEFT JOIN PLMJONODES JON ON JON.ID = JOOI.OWNER_ID
   LEFT JOIN PLMJONODES JON2 ON JON2.PARENT_ID = JON.PARENT_ID AND JON2.ID <> JON.ID
   LEFT JOIN PLMJOOUTPUTITEMS JOOI2 ON  JOOI2.OWNER_ID = JON2.ID
   LEFT JOIN PLMJOBORDERSSN SN ON SN.PARENT_ID = JOOI2.ID AND SN.STOREBATCH_ID = SB.ID  
   WHERE 
     (
     (:RoutineOID <> '0000000000' AND JOR.ID = :RoutineOID) 
      OR
     (:RoutineOID = '0000000000' AND JOR.PARENT_ID = :JOOIOID)
     )       
   AND JOC.ID IS NULL
   AND SC.CATEGORY = 1
   AND (SN.ID IS NOT NULL OR :OnlyDistributed = 'N')
   AND SB.ExpirationDate$Date >= :DateFrom
UNION -- všechny komponenty - materiály i polotovary - šarže
SELECT SC.ID, SB.ID,  0 AS QUANTITY, 
JORM.QUNIT, SC.CODE, SB.NAME, SC.NAME, 4 AS UNIONPART
   FROM
   PLMJobOrdersRoutines JOR
   JOIN PLMJOROUTINESMAT JORM ON JORM.PARENT_ID = JOR.ID
   JOIN STORECARDS SC ON SC.ID = JORM.STORECARD_ID   
   JOIN STOREBATCHES SB ON SB.STORECARD_ID = JORM.STORECARD_ID
   LEFT JOIN PLMJOOUTPUTITEMS JOOI ON JOOI.ID  = JOR.PARENT_ID 
   LEFT JOIN PLMJONODES JON ON JON.ID = JOOI.OWNER_ID
   LEFT JOIN PLMJONODES JON2 ON JON2.PARENT_ID = JON.PARENT_ID AND JON2.ID <> JON.ID
   LEFT JOIN PLMJOOUTPUTITEMS JOOI2 ON  JOOI2.OWNER_ID = JON2.ID
   LEFT JOIN PLMJOBORDERSSN SN ON SN.PARENT_ID = JOOI2.ID AND SN.STOREBATCH_ID = SB.ID  
   WHERE 
     (
     (:RoutineOID <> '0000000000' AND JOR.ID = :RoutineOID) 
      OR
     (:RoutineOID = '0000000000' AND JOR.PARENT_ID = :JOOIOID)
     )       
   AND SC.CATEGORY = 2
   AND (SN.ID IS NOT NULL OR :OnlyDistributed = 'N')
  AND SB.ExpirationDate$Date >= :DateFrom

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