Dynamický dotaz - Výroba - Rodné listy

ID:

GH5VKAB5VT3OB0UCNT2R2Y5XY0

Datasety

MAIN

Popis:

SQL definice:

SELECT {FIELDS}
FROM PLMGenesis A
{JOIN}
WHERE
(({$StoreCardSelPL_ID} = '') or ({$StoreCardSelPL_ID} IS NULL) or (A.ID IN (SELECT FP2.GENESIS_ID
FROM
PLMJONodes N
  LEFT JOIN GetPLMJOMasterNodes(N.ID) NT ON 1=1
  JOIN PLMJOOUTPUTITEMS JOOI ON JOOI.OWNER_ID = NT.ID
  JOIN PLMFINISHEDPRODUCTS2 FP2 ON FP2.JOOUTPUTITEM_ID = JOOI.ID
 WHERE N.STORECARD_ID IN (select OBJ_ID from SELDAT where SEL_ID = {$StoreCardSelPL_ID}) 
)))
AND
({$StoreBatchSel_ID} = '' or (A.ID IN 
   (
   WITH RECURSIVE CTE_query(STOREBATCH_ID,JOBORDERSSN_ID) AS 
   (  
       SELECT SN.STOREBATCH_ID, SN.ID
       FROM PLMJOBORDERSSN SN
       JOIN PLMJOBORDERSCOMPONENTS JC ON JC.PARENT_ID = SN.ID
       WHERE JC.COMPONENT_ID in (select OBJ_ID from SELDAT where SEL_ID = {$StoreBatchSel_ID})

					
       UNION ALL

					
       SELECT SN2.STOREBATCH_ID, SN2.ID
       FROM PLMJOBORDERSSN SN2
       JOIN PLMJOBORDERSCOMPONENTS JC2 ON JC2.PARENT_ID = SN2.ID
       JOIN CTE_query Q ON Q.STOREBATCH_ID = JC2.COMPONENT_ID 
	)
	,
	CTE_result AS
	(
	  SELECT DISTINCT JOBORDERSSN_ID
	  FROM CTE_query 
	)
	SELECT DISTINCT FP2.GENESIS_ID FROM PLMFINISHEDPRODUCTS2 FP2
	JOIN CTE_result CR ON CR.JOBORDERSSN_ID = FP2.JobOrdersSN_ID
)))
AND 
({$StoreCardSel_ID} = '' or (A.ID IN 
   (
   WITH RECURSIVE CTE_query(STOREBATCH_ID,JOBORDERSSN_ID) AS 
   (
      SELECT SN.STOREBATCH_ID, SN.ID
      FROM PLMJOBORDERSSN SN
      JOIN PLMJOBORDERSCOMPONENTS JC ON JC.PARENT_ID = SN.ID
      JOIN PLMJOINPUTITEMS II ON II.ID = JC.INPUTITEM_ID
      JOIN PLMJONODES JN ON JN.ID = II.OWNER_ID  
      WHERE JN.STORECARD_ID in (select OBJ_ID from SELDAT where SEL_ID = {$StoreCardSel_ID}) 

					
       UNION ALL

					
       SELECT SN2.STOREBATCH_ID, SN2.ID
       FROM PLMJOBORDERSSN SN2
       JOIN PLMJOBORDERSCOMPONENTS JC2 ON JC2.PARENT_ID = SN2.ID
       JOIN CTE_query Q ON Q.STOREBATCH_ID = JC2.COMPONENT_ID 
	)
	,
	CTE_result AS
	(
	  SELECT DISTINCT JOBORDERSSN_ID
	  FROM CTE_query 
	)
	SELECT DISTINCT FP2.GENESIS_ID FROM PLMFINISHEDPRODUCTS2 FP2
	JOIN CTE_result CR ON CR.JOBORDERSSN_ID = FP2.JobOrdersSN_ID
)))
AND
({$GenesisSel_ID} = '' or (A.ID IN 
   (
   WITH RECURSIVE CTE_query(STOREBATCH_ID,JOBORDERSSN_ID) AS 
   (
      SELECT SN.STOREBATCH_ID, SN.ID
      FROM PLMJOBORDERSSN SN
      JOIN PLMJOBORDERSCOMPONENTS JC ON JC.PARENT_ID = SN.ID
      WHERE JC.COMPONENT_ID in 
      (
         SELECT DISTINCT SN2.STOREBATCH_ID FROM PLMFINISHEDPRODUCTS2 FP2
	 JOIN PLMJOBORDERSSN SN2 ON SN2.ID = FP2.JOBORDERSSN_ID
         WHERE FP2.GENESIS_ID in (select OBJ_ID from SELDAT where SEL_ID = {$GenesisSel_ID}) 
      )

					
       UNION ALL

					
       SELECT SN2.STOREBATCH_ID, SN2.ID
       FROM PLMJOBORDERSSN SN2
       JOIN PLMJOBORDERSCOMPONENTS JC2 ON JC2.PARENT_ID = SN2.ID
       JOIN CTE_query Q ON Q.STOREBATCH_ID = JC2.COMPONENT_ID 
	)
	,
	CTE_result AS
	(
	  SELECT DISTINCT JOBORDERSSN_ID
	  FROM CTE_query 
	)
	SELECT DISTINCT FP2.GENESIS_ID FROM PLMFINISHEDPRODUCTS2 FP2
	JOIN CTE_result CR ON CR.JOBORDERSSN_ID = FP2.JobOrdersSN_ID
)))
{ANDWHERE}  
{ORDERBY}

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	UR3L1WH2EJZ4BA3UKUBZJI0IBS
FP2	PXHQP3M5HYI414S2WWZEFIQB2O		Dokončené výrobky - řádky
JO	HTI3OTLGNRPO32EEISEPC0XZ0K	JO	Výrobní příkazy
JOSN	ZDT5FNBXGXT4FGFPTWI3YKZ31C	JOSN	Odkaz na šarže
OI	MQHKS5K0YQZOLJWWMG50U44U0S	OI	Vyráběná položka
OIN	INOP4D2NWLNOZABVT2GSHH131G	OIN	Uzel vyráběné položky
Per	CAQPAYUOXVCL3ACL03KIU0CLP4	Per	Období výrobních příkazů
Que	OFTMKVQH3ZD13ACL03KIU0CLP4	Que	Řada výrobních příkazů
SB	C32QXZWCTVDL342W01C0CX3FCC	SB	Šarže / Sériové číslo
SC	C3V5QDVZ5BDL342M01C0CX3FCC	SC	Skladová karta

Podmínky

Components

Popiska:

Komponenty

Typ/Subtyp:

ctSpecial/3

ckLink:

A.ID IS NOT NULL
{:ANDWHERE}

Dataset:

MAIN

ID

Šablona:

ID

Dataset:

MAIN

StoreCard_ID

Popiska:

Skladová karta

Typ/Subtyp:

ctSpecial/0

ckSingle:

OIN.StoreCard_ID = {:VALUE}

ckList:

OIN.StoreCard_ID IN ({:LIST})

Dataset:

MAIN

Speciální parametry:

CLASSID=S3WZQKDB5FDL342M01C0CX3FCC
TextField=Code

StoreBatch_ID

Popiska:

Šarže / sériové číslo

Typ/Subtyp:

ctSpecial/0

ckSingle:

SB.ID = {:VALUE}

ckList:

SB.ID IN ({:LIST})

Dataset:

MAIN

Speciální parametry:

CLASSID=C2BQY04KTVDL342W01C0CX3FCC
TextField=Name

StoreBatch_Name

Popiska:

Název šarže / sér. č.

Typ/Subtyp:

ctString/0

ckSingle:

SB.Name LIKE {:VALUE} ESCAPE '~'

Dataset:

MAIN

Speciální parametry:

ConvertWildChars=True
IgnoreCase=True

ProductionDate

Popiska:

Datum výroby

Typ/Subtyp:

ctDateTime/0

ckRange:

((SELECT MAX(ProductionDate$DATE) FROM PLMFINISHEDPRODUCTS2 WHERE GENESIS_ID = A.ID) >= {:LOW} 
AND (SELECT MAX(ProductionDate$DATE) FROM PLMFINISHEDPRODUCTS2 WHERE GENESIS_ID = A.ID) <= {:HIGH})

Dataset:

MAIN

ExpirationDate

Popiska:

Datum expirace

Typ/Subtyp:

ctDateTime/0

ckRange:

SB.ExpirationDate$DATE >= {:LOW} and
SB.ExpirationDate$DATE < {:HIGH}

Dataset:

MAIN

JobOrder_ID

Popiska:

Výrobní příkaz

Typ/Subtyp:

ctSpecial/13

ckSingle:

JO.ID = {:VALUE}

ckList:

JO.ID IN ({:LIST})

Dataset:

MAIN

Speciální parametry:

CLASSID=HTI3OTLGNRPO32EEISEPC0XZ0K
SITECLASSID=Q4EISOIDDKMOLF12IC3WFS0GDS
DisplayField=ID
DisplayLength=15

JobOrder_Name

Popiska:

Výrobní příkaz - název

Typ/Subtyp:

ctString/0

ckSingle:

QUE.Code||'-'||JO.OrdNumber||'/'||Per.Code LIKE {:VALUE} ESCAPE '~'

Dataset:

MAIN

Speciální parametry:

ConvertWildChars=True
IgnoreCase=True

JODocQueue_ID

Popiska:

Řada výrobního příkazu

Typ/Subtyp:

ctSpecial/0

ckSingle:

JO.DocQueue_ID = {:VALUE}

ckList:

JO.DocQueue_ID IN ({:LIST})

Dataset:

MAIN

Speciální parametry:

CLASSID=W2XNBCJK3ZD13ACL03KIU0CLP4
TextField=Code
FilterDocumentType=JO

JOPeriod_ID

Popiska:

Období výrobního příkazu

Typ/Subtyp:

ctSpecial/0

ckSingle:

JO.Period_ID = {:VALUE}

ckList:

JO.Period_ID IN ({:LIST})

Dataset:

MAIN

Speciální parametry:

CLASSID=W5Y335IS3JD13BYP02K2DBYMG4
TextField=Code

JOBusOrder_ID

Popiska:

Zakázka

Typ/Subtyp:

ctSpecial/8

ckSingle:

/* Omezeni za Zakazky s moznosti vsech podrzizenych krome skrytych */
(
  /* timhle omezime vsechny prime nadrizene */
  (JO.BusOrder_ID = (select Bx.ID from BusOrders Bx where Bx.ID = {:VALUE} and Bx.Hidden = 'N'))
  or
  /* a jestli mame omezeit za podrizene tak to udelame takhle */
  ({WithChildren} = 1 AND
    (JO.BusOrder_ID in
      (select Bx.ID from BusOrders Bx where
        Bx.ID in (select B2x.ID from SYS$BusOrders2 B2x where B2x.Superior_ID = {:VALUE}) and
        Bx.Hidden = 'N'
      )
    )
  )
)

ckList:

/* Omezeni za Zakazky s moznosti vsech podrzizenych krome skrytych */
(
  /* timhle omezime vsechny prime nadrizene */
  (JO.BusOrder_ID in (select Bx.ID from BusOrders Bx where Bx.ID in ({:LIST}) and Bx.Hidden = 'N'))
  or
  /* a jestli mame omezeit za podrizene tak to udelame takhle */
  ({WithChildren} = 1 AND
    (JO.BusOrder_ID in
      (select Bx.ID from BusOrders Bx where
        Bx.ID in (select B2x.ID from SYS$BusOrders2 B2x where B2x.Superior_ID in ({:LIST})) and
        Bx.Hidden = 'N'
      )
    )
  )
)

ckNull:

JO.BusOrder_ID IS NULL

Dataset:

MAIN

Speciální parametry:

CLASSID=03OXHKRF4VD13ACL03KIU0CLP4
TextField=Code

JOBusProject_ID

Popiska:

Projekt

Typ/Subtyp:

ctSpecial/8

ckSingle:

/* Omezeni za projekty s moznosti vsech podrizenych */
(
  /* timhle omezime vsechny prime nadrizene */
  (JO.BusProject_ID = (select Bx.ID from BusProjects Bx where Bx.ID = {:VALUE} and BX.Hidden = 'N'))
  or
  /* a jestli mame omezeit za podrizene tak to udelame takhle */
  ({WithChildren} = 1 AND
    (JO.BusProject_ID in
      (select Bx.ID from BusProjects Bx where
        Bx.ID in (select B2x.ID from SYS$BusProjects2 B2x where B2x.Superior_ID = {:VALUE}) and
        BX.Hidden = 'N'
      )
    )
  )
)

ckList:

/* Omezeni za projekty s moznosti vsech podrizenych */
(
  /* timhle omezime vsechny prime nadrizene */
  (JO.BusProject_ID in (select Bx.ID from BusProjects Bx where Bx.ID in ({:LIST}) and Bx.Hidden = 'N'))
  or
  /* a jestli mame omezeit za podrizene tak to udelame takhle */
  ({WithChildren} = 1 AND
    (JO.BusProject_ID in
      (select Bx.ID from BusProjects Bx where
        Bx.ID in (select B2x.ID from SYS$BusProjects2 B2x where B2x.Superior_ID in ({:LIST})) and
        Bx.Hidden = 'N'
      )
    )
  )
)

ckNull:

JO.BusProject_ID IS NULL

Dataset:

MAIN

Speciální parametry:

CLASSID=ZX20VMNR1NV4N30K2MRDAXLRN4
TextField=Code

JOBusTransaction_ID

Popiska:

Obchodní případ

Typ/Subtyp:

ctSpecial/8

ckSingle:

/* Omezeni za Obch. pripady s moznosti vsech podrzizenych krome skrytych */
(
  /* timhle omezime vsechny prime nadrizene */
  (JO.BusTransaction_ID = (select Bx.ID from BusTransactions Bx where Bx.ID = {:VALUE} and Bx.Hidden = 'N'))
  or
  /* a jestli mame omezeit za podrizene tak to udelame takhle */
  ({WithChildren} = 1 AND
    (JO.BusTransaction_ID in
      (select Bx.ID from BusTransactions Bx where
        Bx.ID in (select B2x.ID from SYS$BusTransactions2 B2x where B2x.Superior_ID = {:VALUE}) and
        Bx.Hidden = 'N'
      )
    )
  )
)

ckList:

/* Omezeni za Obch. pripady s moznosti vsech podrzizenych krome skrytych */
(
  /* timhle omezime vsechny prime nadrizene */
  (JO.BusTransaction_ID in (select Bx.ID from BusTransactions Bx where Bx.ID in ({:LIST}) and Bx.Hidden = 'N'))
  or
  /* a jestli mame omezeit za podrizene tak to udelame takhle */
  ({WithChildren} = 1 AND
    (JO.BusTransaction_ID in
      (select Bx.ID from BusTransactions Bx where
        Bx.ID in (select B2x.ID from SYS$BusTransactions2 B2x where B2x.Superior_ID in ({:LIST})) and
        Bx.Hidden = 'N'
      )
    )
  )
)

ckNull:

JO.BusTransaction_ID IS NULL

Dataset:

MAIN

Speciální parametry:

CLASSID=0BOXHKRF4VD13ACL03KIU0CLP4
TextField=Code

JOFinishedAt

Popiska:

Uzavřené výrobní příkazy

Typ/Subtyp:

ctBoolean/0

ckSingle:

(
(({:VALUE} = 'N') and (JO.FinishedAt$DATE = 0)) or
(({:VALUE} = 'A') and (JO.FinishedAt$DATE <> 0))
)

Dataset:

MAIN

Speciální parametry:

StoreBatchComp

Popiska:

Šarže / sériové číslo

Typ/Subtyp:

ctSpecial/0

ckSingle:

A.ID = {:VALUE}

ckList:

A.ID IN ({:LIST})

Dataset:

MAIN

Nadřízený:

Components

Speciální parametry:

CLASSID=C2BQY04KTVDL342W01C0CX3FCC
TextField=Name
AlwaysList=True
ReplacementLink=True
ForceSupportNegation=0
{$StoreBatchSel_ID}=SELECTIONID;''

StoreCardComp

Popiska:

Skladové karty

Typ/Subtyp:

ctSpecial/0

ckSingle:

A.ID = {:VALUE}

ckList:

A.ID IN ({:LIST})

Dataset:

MAIN

Nadřízený:

Components

Speciální parametry:

CLASSID=S3WZQKDB5FDL342M01C0CX3FCC
TextField=Code
AlwaysList=True
ReplacementLink=True
ForceSupportNegation=0
{$StoreCardSel_ID}=SELECTIONID;''

GenesisComp

Popiska:

Rodné listy

Typ/Subtyp:

ctSpecial/13

ckSingle:

A.ID = {:VALUE}

ckList:

A.ID IN ({:LIST})

Dataset:

MAIN

Nadřízený:

Components

Speciální parametry:

CLASSID=UR3L1WH2EJZ4BA3UKUBZJI0IBS
SITECLASSID=X0QEWWFGSCEORBVO14SPA1D0WC
DisplayField=ID
DisplayLength=15
AlwaysList=True
ReplacementLink=True
ForceSupportNegation=0
{$GenesisSel_ID}=SELECTIONID;''

GenesisName

Popiska:

Rodný list - název

Typ/Subtyp:

ctString/0

ckSingle:

SB.Name || '.' || QUE.Code||'-'||JO.OrdNumber||'/'||Per.Code || '.' || A.ID like {:VALUE} ESCAPE '~'

Dataset:

MAIN

Speciální parametry:

ConvertWildChars=True
IgnoreCase=True

IsSemiFP

Popiska:

Polotovar spojité výroby

Typ/Subtyp:

ctBoolean/0

ckSingle:

FP2.IsSemiFP = {:VALUE}

Dataset:

MAIN

HasServices

Popiska:

Neshodná výroba

Typ/Subtyp:

ctBoolean/0

ckSingle:

(('N' = {:VALUE} )
OR
((SELECT MIN('A')
    FROM PLMProduceServices S
    JOIN PLMJOInputItems II ON (S.JOInputItem_ID = II.ID)
    JOIN PLMJONodes N ON (II.Owner_Id = N.Id)
    JOIN PLMJobOrders JO ON (N.Parent_ID = JO.ID)
    JOIN PLMJONodes ONd ON (ONd.Parent_ID = JO.ID)
    JOIN PLMJOOutputItems OI on (OI.Owner_ID = ONd.ID)
    JOIN PLMFinishedProducts2 FP ON(FP.JOOutputItem_ID = OI.ID)
    WHERE FP.Genesis_ID = A.ID
      AND ((S.Sn_Id IS NULL) OR (S.Sn_Id = FP.Joborderssn_Id) )
  ) = {:VALUE} ))

Dataset:

MAIN

PieceList

Popiska:

Kusovník

Typ/Subtyp:

ctSpecial/3

ckLink:

A.ID IS NOT NULL
{:ANDWHERE}

Dataset:

MAIN

StoreCardPL

Popiska:

Skladové karty

Typ/Subtyp:

ctSpecial/0

ckSingle:

A.ID = {:VALUE}

ckList:

A.ID IN ({:LIST})

Dataset:

MAIN

Nadřízený:

PieceList

Speciální parametry:

CLASSID=S3WZQKDB5FDL342M01C0CX3FCC
TextField=Code
AlwaysList=True
ReplacementLink=True
ForceSupportNegation=0
{$StoreCardSelPL_ID}=SELECTIONID;''

IsComponent

Popiska:

Je komponentou

Typ/Subtyp:

ctBoolean/0

ckSingle:

( ('N' = {:VALUE}
  AND NOT EXISTS (SELECT 1
    FROM PLMJobOrdersComponents JC
    WHERE JC.Component_ID = JOSN.StoreBatch_ID
  ))
  OR 
  ('A' = {:VALUE}
  AND EXISTS (SELECT 1
    FROM PLMJobOrdersComponents JC
    WHERE JC.Component_ID = JOSN.StoreBatch_ID
  ))
  )

					

				

Dataset:

MAIN

DataIOT

Popiska:

Faktory prostředí

Typ/Subtyp:

ctSpecial/3

ckLink:

A.ID IS NOT NULL
{:ANDWHERE}

Dataset:

MAIN

Production

Popiska:

Výroba - pracoviště

Typ/Subtyp:

ctSpecial/3

ckLink:

A.ID IS NOT NULL
{:ANDWHERE}

Dataset:

MAIN

Nadřízený:

DataIOT

SensorQuantityProd

Popiska:

Veličina

Typ/Subtyp:

ctString/0

ckSingle:

EXISTS
(
SELECT 1
FROM 
(SELECT FP2.GENESIS_ID, WP.ID, WP.NAME, MIN(O.STARTEDAT$DATE) AS MinDate,MAX(O.FINISHEDAT$DATE) AS MaxDate  FROM PLMFINISHEDPRODUCTS2 FP2
  LEFT JOIN PLMGETCHILDJOBORDERSSN(FP2.JOBORDERSSN_ID) T ON 1=1
JOIN PLMJOBORDERSSN SN ON SN.ID = T.ID
JOIN PLMJOOUTPUTITEMS JOO ON JOO.ID = SN.PARENT_ID
JOIN PLMJOBORDERSROUTINES JR ON JR.PARENT_ID = JOO.ID 
JOIN PLMOPERATIONS O ON O.JOBORDERSROUTINES_ID = JR.ID
JOIN PLMWORKPLACES WP ON WP.ID = O.WORKPLACE_ID
WHERE ((O.JOBORDERSSN_ID = SN.ID) OR (O.JOBORDERSSN_ID IS NULL) OR (O.JOBORDERSSN_ID = ''))
GROUP BY WP.ID, WP.NAME, FP2.GENESIS_ID) S
JOIN IOTDATA IOT ON IOT.OBJECTCLASS = '5XGE0QFOGIWOB5ANMTYRIVKJ0O' AND IOT.OBJECT_ID = S.ID AND IOT.RECORDEDAT$DATE BETWEEN S.MinDate AND S.MaxDate
AND <<IOT.SENSORQUANTITY>> like {:VALUE} ESCAPE '~'
WHERE S.GENESIS_ID = A.ID
)

Dataset:

MAIN

Nadřízený:

Production

Speciální parametry:

ConvertWildChars=True
IgnoreCase=True

NumericValueProd

Popiska:

Číslo

Typ/Subtyp:

ctExtended/0

ckRange:

EXISTS
(
SELECT 1
FROM 
(SELECT FP2.GENESIS_ID, WP.ID, WP.NAME, MIN(O.STARTEDAT$DATE) AS MinDate,MAX(O.FINISHEDAT$DATE) AS MaxDate  FROM PLMFINISHEDPRODUCTS2 FP2
  LEFT JOIN PLMGETCHILDJOBORDERSSN(FP2.JOBORDERSSN_ID) T ON 1=1
JOIN PLMJOBORDERSSN SN ON SN.ID = T.ID
JOIN PLMJOOUTPUTITEMS JOO ON JOO.ID = SN.PARENT_ID
JOIN PLMJOBORDERSROUTINES JR ON JR.PARENT_ID = JOO.ID 
JOIN PLMOPERATIONS O ON O.JOBORDERSROUTINES_ID = JR.ID
JOIN PLMWORKPLACES WP ON WP.ID = O.WORKPLACE_ID
WHERE ((O.JOBORDERSSN_ID = SN.ID) OR (O.JOBORDERSSN_ID IS NULL) OR (O.JOBORDERSSN_ID = ''))
GROUP BY WP.ID, WP.NAME, FP2.GENESIS_ID) S
JOIN IOTDATA IOT ON IOT.OBJECTCLASS = '5XGE0QFOGIWOB5ANMTYRIVKJ0O' AND IOT.OBJECT_ID = S.ID AND IOT.RECORDEDAT$DATE BETWEEN S.MinDate AND S.MaxDate
AND IOT.VALUEDATATYPE = 0 AND IOT.NUMERICVALUE  BETWEEN {:LOW} and {:HIGH}
WHERE S.GENESIS_ID = A.ID
)

Dataset:

MAIN

Nadřízený:

Production

ShortStringValueProd

Popiska:

Krátký řetězec

Typ/Subtyp:

ctString/0

ckSingle:

EXISTS
(
SELECT 1
FROM 
(SELECT FP2.GENESIS_ID, WP.ID, WP.NAME, MIN(O.STARTEDAT$DATE) AS MinDate,MAX(O.FINISHEDAT$DATE) AS MaxDate  FROM PLMFINISHEDPRODUCTS2 FP2
  LEFT JOIN PLMGETCHILDJOBORDERSSN(FP2.JOBORDERSSN_ID) T ON 1=1
JOIN PLMJOBORDERSSN SN ON SN.ID = T.ID
JOIN PLMJOOUTPUTITEMS JOO ON JOO.ID = SN.PARENT_ID
JOIN PLMJOBORDERSROUTINES JR ON JR.PARENT_ID = JOO.ID 
JOIN PLMOPERATIONS O ON O.JOBORDERSROUTINES_ID = JR.ID
JOIN PLMWORKPLACES WP ON WP.ID = O.WORKPLACE_ID
WHERE ((O.JOBORDERSSN_ID = SN.ID) OR (O.JOBORDERSSN_ID IS NULL) OR (O.JOBORDERSSN_ID = ''))
GROUP BY WP.ID, WP.NAME, FP2.GENESIS_ID) S
JOIN IOTDATA IOT ON IOT.OBJECTCLASS = '5XGE0QFOGIWOB5ANMTYRIVKJ0O' AND IOT.OBJECT_ID = S.ID AND IOT.RECORDEDAT$DATE BETWEEN S.MinDate AND S.MaxDate
AND IOT.VALUEDATATYPE = 1 AND <<IOT.SHORTSTRINGVALUE>> like {:VALUE} ESCAPE '~'
WHERE S.GENESIS_ID = A.ID
)

Dataset:

MAIN

Nadřízený:

Production

Speciální parametry:

ConvertWildChars=True
IgnoreCase=True

NumberOfYesProd

Popiska:

Pozitivních výskytů

Typ/Subtyp:

ctInteger/0

ckRange:

exists
(
SELECT 1
FROM 
(SELECT FP2.GENESIS_ID, WP.ID, WP.NAME, MIN(O.STARTEDAT$DATE) AS MinDate,MAX(O.FINISHEDAT$DATE) AS MaxDate  FROM PLMFINISHEDPRODUCTS2 FP2
  LEFT JOIN PLMGETCHILDJOBORDERSSN(FP2.JOBORDERSSN_ID) T ON 1=1
JOIN PLMJOBORDERSSN SN ON SN.ID = T.ID
JOIN PLMJOOUTPUTITEMS JOO ON JOO.ID = SN.PARENT_ID
JOIN PLMJOBORDERSROUTINES JR ON JR.PARENT_ID = JOO.ID 
JOIN PLMOPERATIONS O ON O.JOBORDERSROUTINES_ID = JR.ID
JOIN PLMWORKPLACES WP ON WP.ID = O.WORKPLACE_ID
WHERE ((O.JOBORDERSSN_ID = SN.ID) OR (O.JOBORDERSSN_ID IS NULL) OR (O.JOBORDERSSN_ID = ''))
GROUP BY WP.ID, WP.NAME, FP2.GENESIS_ID) S
JOIN IOTDATA IOT ON IOT.OBJECTCLASS = '5XGE0QFOGIWOB5ANMTYRIVKJ0O' AND IOT.OBJECT_ID = S.ID AND IOT.RECORDEDAT$DATE BETWEEN S.MinDate AND S.MaxDate
AND IOT.VALUEDATATYPE = 5
WHERE S.GENESIS_ID = A.ID
GROUP BY S.ID, S.NAME, S.MinDate, S.MaxDate, IOT.SENSORQUANTITY 
HAVING SUM(case when IOT.BOOLEANVALUE = 'A' then 1 else 0 end) BETWEEN {:LOW} and {:HIGH}
)

Dataset:

MAIN

Nadřízený:

Production

NumberOfNoProd

Popiska:

Negativních výskytů

Typ/Subtyp:

ctInteger/0

ckRange:

exists
(
SELECT 1
FROM 
(SELECT FP2.GENESIS_ID, WP.ID, WP.NAME, MIN(O.STARTEDAT$DATE) AS MinDate,MAX(O.FINISHEDAT$DATE) AS MaxDate  FROM PLMFINISHEDPRODUCTS2 FP2
  LEFT JOIN PLMGETCHILDJOBORDERSSN(FP2.JOBORDERSSN_ID) T ON 1=1
JOIN PLMJOBORDERSSN SN ON SN.ID = T.ID
JOIN PLMJOOUTPUTITEMS JOO ON JOO.ID = SN.PARENT_ID
JOIN PLMJOBORDERSROUTINES JR ON JR.PARENT_ID = JOO.ID 
JOIN PLMOPERATIONS O ON O.JOBORDERSROUTINES_ID = JR.ID
JOIN PLMWORKPLACES WP ON WP.ID = O.WORKPLACE_ID
WHERE ((O.JOBORDERSSN_ID = SN.ID) OR (O.JOBORDERSSN_ID IS NULL) OR (O.JOBORDERSSN_ID = ''))
GROUP BY WP.ID, WP.NAME, FP2.GENESIS_ID) S
JOIN IOTDATA IOT ON IOT.OBJECTCLASS = '5XGE0QFOGIWOB5ANMTYRIVKJ0O' AND IOT.OBJECT_ID = S.ID AND IOT.RECORDEDAT$DATE BETWEEN S.MinDate AND S.MaxDate
AND IOT.VALUEDATATYPE = 5
WHERE S.GENESIS_ID = A.ID
GROUP BY S.ID, S.NAME, S.MinDate, S.MaxDate, IOT.SENSORQUANTITY
HAVING SUM(case when IOT.BOOLEANVALUE = 'N' then 1 else 0 end) BETWEEN {:LOW} and {:HIGH}
)

Dataset:

MAIN

Nadřízený:

Production

Machine

Popiska:

Výroba - kapacitní jednotka

Typ/Subtyp:

ctSpecial/3

ckLink:

A.ID IS NOT NULL
{:ANDWHERE}

Dataset:

MAIN

Nadřízený:

DataIOT

SensorQuantityMachine

Popiska:

Veličina

Typ/Subtyp:

ctString/0

ckSingle:

EXISTS
(
SELECT 1
FROM 
(SELECT FP2.GENESIS_ID, WP.ID, WP.NAME, MIN(O.STARTEDAT$DATE) AS MinDate,MAX(O.FINISHEDAT$DATE) AS MaxDate  FROM PLMFINISHEDPRODUCTS2 FP2
  LEFT JOIN PLMGETCHILDJOBORDERSSN(FP2.JOBORDERSSN_ID) T ON 1=1
JOIN PLMJOBORDERSSN SN ON SN.ID = T.ID
JOIN PLMJOOUTPUTITEMS JOO ON JOO.ID = SN.PARENT_ID
JOIN PLMJOBORDERSROUTINES JR ON JR.PARENT_ID = JOO.ID 
JOIN PLMOPERATIONS O ON O.JOBORDERSROUTINES_ID = JR.ID
JOIN PLMMachines WP ON WP.ID = O.Machine_ID
WHERE ((O.JOBORDERSSN_ID = SN.ID) OR (O.JOBORDERSSN_ID IS NULL) OR (O.JOBORDERSSN_ID = ''))
GROUP BY WP.ID, WP.NAME, FP2.GENESIS_ID) S
JOIN IOTDATA IOT ON IOT.OBJECTCLASS = 'XHEO4QSZPVXOJBRVPMOL4FGLWG' AND IOT.OBJECT_ID = S.ID AND IOT.RECORDEDAT$DATE BETWEEN S.MinDate AND S.MaxDate
AND <<IOT.SENSORQUANTITY>> like {:VALUE} ESCAPE '~'
WHERE S.GENESIS_ID = A.ID
)

Dataset:

MAIN

Nadřízený:

Machine

Speciální parametry:

ConvertWildChars=True
IgnoreCase=True

NumericValueMachine

Popiska:

Číslo

Typ/Subtyp:

ctExtended/0

ckRange:

EXISTS
(
SELECT 1
FROM 
(SELECT FP2.GENESIS_ID, WP.ID, WP.NAME, MIN(O.STARTEDAT$DATE) AS MinDate,MAX(O.FINISHEDAT$DATE) AS MaxDate  FROM PLMFINISHEDPRODUCTS2 FP2
  LEFT JOIN PLMGETCHILDJOBORDERSSN(FP2.JOBORDERSSN_ID) T ON 1=1
JOIN PLMJOBORDERSSN SN ON SN.ID = T.ID
JOIN PLMJOOUTPUTITEMS JOO ON JOO.ID = SN.PARENT_ID
JOIN PLMJOBORDERSROUTINES JR ON JR.PARENT_ID = JOO.ID 
JOIN PLMOPERATIONS O ON O.JOBORDERSROUTINES_ID = JR.ID
JOIN PLMMachines WP ON WP.ID = O.Machine_ID
WHERE ((O.JOBORDERSSN_ID = SN.ID) OR (O.JOBORDERSSN_ID IS NULL) OR (O.JOBORDERSSN_ID = ''))
GROUP BY WP.ID, WP.NAME, FP2.GENESIS_ID) S
JOIN IOTDATA IOT ON IOT.OBJECTCLASS = 'XHEO4QSZPVXOJBRVPMOL4FGLWG' AND IOT.OBJECT_ID = S.ID AND IOT.RECORDEDAT$DATE BETWEEN S.MinDate AND S.MaxDate
AND IOT.VALUEDATATYPE = 0 AND IOT.NUMERICVALUE  BETWEEN {:LOW} and {:HIGH}
WHERE S.GENESIS_ID = A.ID
)

Dataset:

MAIN

Nadřízený:

Machine

ShortStringValueMachine

Popiska:

Krátký řetězec

Typ/Subtyp:

ctString/0

ckSingle:

EXISTS
(
SELECT 1
FROM 
(SELECT FP2.GENESIS_ID, WP.ID, WP.NAME, MIN(O.STARTEDAT$DATE) AS MinDate,MAX(O.FINISHEDAT$DATE) AS MaxDate  FROM PLMFINISHEDPRODUCTS2 FP2
  LEFT JOIN PLMGETCHILDJOBORDERSSN(FP2.JOBORDERSSN_ID) T ON 1=1
JOIN PLMJOBORDERSSN SN ON SN.ID = T.ID
JOIN PLMJOOUTPUTITEMS JOO ON JOO.ID = SN.PARENT_ID
JOIN PLMJOBORDERSROUTINES JR ON JR.PARENT_ID = JOO.ID 
JOIN PLMOPERATIONS O ON O.JOBORDERSROUTINES_ID = JR.ID
JOIN PLMMachines WP ON WP.ID = O.Machine_ID
WHERE ((O.JOBORDERSSN_ID = SN.ID) OR (O.JOBORDERSSN_ID IS NULL) OR (O.JOBORDERSSN_ID = ''))
GROUP BY WP.ID, WP.NAME, FP2.GENESIS_ID) S
JOIN IOTDATA IOT ON IOT.OBJECTCLASS = 'XHEO4QSZPVXOJBRVPMOL4FGLWG' AND IOT.OBJECT_ID = S.ID AND IOT.RECORDEDAT$DATE BETWEEN S.MinDate AND S.MaxDate
AND IOT.VALUEDATATYPE = 1 AND <<IOT.SHORTSTRINGVALUE>> like {:VALUE} ESCAPE '~'
WHERE S.GENESIS_ID = A.ID
)

Dataset:

MAIN

Nadřízený:

Machine

Speciální parametry:

ConvertWildChars=True
IgnoreCase=True

NumberOfYesMachine

Popiska:

Pozitivních výskytů

Typ/Subtyp:

ctInteger/0

ckRange:

exists
(
SELECT 1
FROM 
(SELECT FP2.GENESIS_ID, WP.ID, WP.NAME, MIN(O.STARTEDAT$DATE) AS MinDate,MAX(O.FINISHEDAT$DATE) AS MaxDate  FROM PLMFINISHEDPRODUCTS2 FP2
  LEFT JOIN PLMGETCHILDJOBORDERSSN(FP2.JOBORDERSSN_ID) T ON 1=1
JOIN PLMJOBORDERSSN SN ON SN.ID = T.ID
JOIN PLMJOOUTPUTITEMS JOO ON JOO.ID = SN.PARENT_ID
JOIN PLMJOBORDERSROUTINES JR ON JR.PARENT_ID = JOO.ID 
JOIN PLMOPERATIONS O ON O.JOBORDERSROUTINES_ID = JR.ID
JOIN PLMMachines WP ON WP.ID = O.Machine_ID
WHERE ((O.JOBORDERSSN_ID = SN.ID) OR (O.JOBORDERSSN_ID IS NULL) OR (O.JOBORDERSSN_ID = ''))
GROUP BY WP.ID, WP.NAME, FP2.GENESIS_ID) S
JOIN IOTDATA IOT ON IOT.OBJECTCLASS = 'XHEO4QSZPVXOJBRVPMOL4FGLWG' AND IOT.OBJECT_ID = S.ID AND IOT.RECORDEDAT$DATE BETWEEN S.MinDate AND S.MaxDate
AND IOT.VALUEDATATYPE = 5
WHERE S.GENESIS_ID = A.ID
GROUP BY S.ID, S.NAME, S.MinDate, S.MaxDate, IOT.SENSORQUANTITY 
HAVING SUM(case when IOT.BOOLEANVALUE = 'A' then 1 else 0 end) BETWEEN {:LOW} and {:HIGH}
)

Dataset:

MAIN

Nadřízený:

Machine

NumberOfNoMachine

Popiska:

Negativních výskytů

Typ/Subtyp:

ctInteger/0

ckRange:

exists
(
SELECT 1
FROM 
(SELECT FP2.GENESIS_ID, WP.ID, WP.NAME, MIN(O.STARTEDAT$DATE) AS MinDate,MAX(O.FINISHEDAT$DATE) AS MaxDate  FROM PLMFINISHEDPRODUCTS2 FP2
  LEFT JOIN PLMGETCHILDJOBORDERSSN(FP2.JOBORDERSSN_ID) T ON 1=1
JOIN PLMJOBORDERSSN SN ON SN.ID = T.ID
JOIN PLMJOOUTPUTITEMS JOO ON JOO.ID = SN.PARENT_ID
JOIN PLMJOBORDERSROUTINES JR ON JR.PARENT_ID = JOO.ID 
JOIN PLMOPERATIONS O ON O.JOBORDERSROUTINES_ID = JR.ID
JOIN PLMMachines WP ON WP.ID = O.Machine_ID
WHERE ((O.JOBORDERSSN_ID = SN.ID) OR (O.JOBORDERSSN_ID IS NULL) OR (O.JOBORDERSSN_ID = ''))
GROUP BY WP.ID, WP.NAME, FP2.GENESIS_ID) S
JOIN IOTDATA IOT ON IOT.OBJECTCLASS = 'XHEO4QSZPVXOJBRVPMOL4FGLWG' AND IOT.OBJECT_ID = S.ID AND IOT.RECORDEDAT$DATE BETWEEN S.MinDate AND S.MaxDate
AND IOT.VALUEDATATYPE = 5
WHERE S.GENESIS_ID = A.ID
GROUP BY S.ID, S.NAME, S.MinDate, S.MaxDate, IOT.SENSORQUANTITY
HAVING SUM(case when IOT.BOOLEANVALUE = 'N' then 1 else 0 end) BETWEEN {:LOW} and {:HIGH}
)

Dataset:

MAIN

Nadřízený:

Machine

Storage

Popiska:

Skladování

Typ/Subtyp:

ctSpecial/3

ckLink:

A.ID IS NOT NULL
{:ANDWHERE}

Dataset:

MAIN

Nadřízený:

DataIOT

SensorQuantityStore

Popiska:

Veličina

Typ/Subtyp:

ctString/0

ckSingle:

EXISTS 
(
SELECT 1
FROM 
(  SELECT DISTINCT
         FP2.GENESIS_ID,
   	 SD2.STORE_ID, 
   	 SD.DOCDATE$DATE AS DateFrom,
   	 COALESCE(SDA.DOCDATE$DATE, 100000) AS DateTo
   	 FROM PLMFINISHEDPRODUCTS2 FP2
     LEFT JOIN PLMGETCHILDJOBORDERSSN(FP2.JOBORDERSSN_ID) T ON 1=1
     JOIN PLMJOBORDERSSN SN ON SN.ID = T.ID
     JOIN DOCROWBATCHES DRB ON DRB.STOREBATCH_ID = SN.STOREBATCH_ID
     JOIN STOREDOCUMENTS2 SD2 ON SD2.ID = DRB.PARENT_ID 
     JOIN STOREDOCUMENTS SD ON SD.DOCUMENTTYPE IN ('28', '24') AND SD.ID = SD2.PARENT_ID
     LEFT JOIN DOCROWBATCHES DRBA ON DRBA.STOREBATCH_ID = SN.STOREBATCH_ID AND DRBA.ID <> DRB.ID
     LEFT JOIN STOREDOCUMENTS2 SD2A ON SD2A.ID = DRBA.PARENT_ID AND SD2A.FLOWTYPE IN ('22')  
     LEFT JOIN STOREDOCUMENTS SDA ON SDA.ID = SD2A.PARENT_ID AND SDA."DOCDATE$DATE" > SD."DOCDATE$DATE"
     LEFT JOIN DOCROWBATCHES DRBB ON DRBB.STOREBATCH_ID = SN.STOREBATCH_ID AND DRBB.ID <> DRB.ID AND DRBB.ID <> DRBA.ID
     LEFT JOIN STOREDOCUMENTS2 SD2B ON SD2B.ID = DRBB.PARENT_ID 
     LEFT JOIN STOREDOCUMENTS SDB ON SDB.DOCUMENTTYPE IN ('22') AND SDB.ID = SD2B.PARENT_ID AND SDB."DOCDATE$DATE" < SDA."DOCDATE$DATE"  
   WHERE 
   	SDB.ID IS NULL    
) S
JOIN IOTDATA IOT ON IOT.OBJECTCLASS = 'SFMSB5W15BDL3CL100C4RHECN0' AND IOT.OBJECT_ID = S.Store_ID AND IOT.RECORDEDAT$DATE BETWEEN S.DateFrom AND S.DateTo 
AND <<IOT.SENSORQUANTITY>> like {:VALUE} ESCAPE '~'
WHERE S.GENESIS_ID = A.ID
)

Dataset:

MAIN

Nadřízený:

Storage

Speciální parametry:

ConvertWildChars=True
IgnoreCase=True

NumericValueStore

Popiska:

Číslo

Typ/Subtyp:

ctExtended/0

ckRange:

EXISTS 
(
SELECT 1
FROM 
(  SELECT DISTINCT
         FP2.GENESIS_ID,
   	 SD2.STORE_ID, 
   	 SD.DOCDATE$DATE AS DateFrom,
   	 COALESCE(SDA.DOCDATE$DATE, 100000) AS DateTo
   	 FROM PLMFINISHEDPRODUCTS2 FP2
     LEFT JOIN PLMGETCHILDJOBORDERSSN(FP2.JOBORDERSSN_ID) T ON 1=1
     JOIN PLMJOBORDERSSN SN ON SN.ID = T.ID
     JOIN DOCROWBATCHES DRB ON DRB.STOREBATCH_ID = SN.STOREBATCH_ID
     JOIN STOREDOCUMENTS2 SD2 ON SD2.ID = DRB.PARENT_ID 
     JOIN STOREDOCUMENTS SD ON SD.DOCUMENTTYPE IN ('28', '24') AND SD.ID = SD2.PARENT_ID
     LEFT JOIN DOCROWBATCHES DRBA ON DRBA.STOREBATCH_ID = SN.STOREBATCH_ID AND DRBA.ID <> DRB.ID
     LEFT JOIN STOREDOCUMENTS2 SD2A ON SD2A.ID = DRBA.PARENT_ID AND SD2A.FLOWTYPE IN ('22')  
     LEFT JOIN STOREDOCUMENTS SDA ON SDA.ID = SD2A.PARENT_ID AND SDA."DOCDATE$DATE" > SD."DOCDATE$DATE"
     LEFT JOIN DOCROWBATCHES DRBB ON DRBB.STOREBATCH_ID = SN.STOREBATCH_ID AND DRBB.ID <> DRB.ID AND DRBB.ID <> DRBA.ID
     LEFT JOIN STOREDOCUMENTS2 SD2B ON SD2B.ID = DRBB.PARENT_ID 
     LEFT JOIN STOREDOCUMENTS SDB ON SDB.DOCUMENTTYPE IN ('22') AND SDB.ID = SD2B.PARENT_ID AND SDB."DOCDATE$DATE" < SDA."DOCDATE$DATE"  
   WHERE 
   	SDB.ID IS NULL    
) S
JOIN IOTDATA IOT ON IOT.OBJECTCLASS = 'SFMSB5W15BDL3CL100C4RHECN0' AND IOT.OBJECT_ID = S.Store_ID AND IOT.RECORDEDAT$DATE BETWEEN S.DateFrom AND S.DateTo 
AND IOT.VALUEDATATYPE = 0 AND IOT.NUMERICVALUE  BETWEEN {:LOW} and {:HIGH}
WHERE S.GENESIS_ID = A.ID
)

Dataset:

MAIN

Nadřízený:

Storage

ShortStringValueStore

Popiska:

Krátký řetězec

Typ/Subtyp:

ctString/0

ckSingle:

EXISTS 
(
SELECT 1
FROM 
(  SELECT DISTINCT
         FP2.GENESIS_ID,
   	 SD2.STORE_ID, 
   	 SD.DOCDATE$DATE AS DateFrom,
   	 COALESCE(SDA.DOCDATE$DATE, 100000) AS DateTo
   	 FROM PLMFINISHEDPRODUCTS2 FP2
     LEFT JOIN PLMGETCHILDJOBORDERSSN(FP2.JOBORDERSSN_ID) T ON 1=1
     JOIN PLMJOBORDERSSN SN ON SN.ID = T.ID
     JOIN DOCROWBATCHES DRB ON DRB.STOREBATCH_ID = SN.STOREBATCH_ID
     JOIN STOREDOCUMENTS2 SD2 ON SD2.ID = DRB.PARENT_ID 
     JOIN STOREDOCUMENTS SD ON SD.DOCUMENTTYPE IN ('28', '24') AND SD.ID = SD2.PARENT_ID
     LEFT JOIN DOCROWBATCHES DRBA ON DRBA.STOREBATCH_ID = SN.STOREBATCH_ID AND DRBA.ID <> DRB.ID
     LEFT JOIN STOREDOCUMENTS2 SD2A ON SD2A.ID = DRBA.PARENT_ID AND SD2A.FLOWTYPE IN ('22')  
     LEFT JOIN STOREDOCUMENTS SDA ON SDA.ID = SD2A.PARENT_ID AND SDA."DOCDATE$DATE" > SD."DOCDATE$DATE"
     LEFT JOIN DOCROWBATCHES DRBB ON DRBB.STOREBATCH_ID = SN.STOREBATCH_ID AND DRBB.ID <> DRB.ID AND DRBB.ID <> DRBA.ID
     LEFT JOIN STOREDOCUMENTS2 SD2B ON SD2B.ID = DRBB.PARENT_ID 
     LEFT JOIN STOREDOCUMENTS SDB ON SDB.DOCUMENTTYPE IN ('22') AND SDB.ID = SD2B.PARENT_ID AND SDB."DOCDATE$DATE" < SDA."DOCDATE$DATE"  
   WHERE 
   	SDB.ID IS NULL    
) S
JOIN IOTDATA IOT ON IOT.OBJECTCLASS = 'SFMSB5W15BDL3CL100C4RHECN0' AND IOT.OBJECT_ID = S.Store_ID AND IOT.RECORDEDAT$DATE BETWEEN S.DateFrom AND S.DateTo 
AND IOT.VALUEDATATYPE = 1 AND <<IOT.SHORTSTRINGVALUE>> like {:VALUE} ESCAPE '~'
WHERE S.GENESIS_ID = A.ID
)

Dataset:

MAIN

Nadřízený:

Storage

Speciální parametry:

ConvertWildChars=True
IgnoreCase=True

NumberOfYesStore

Popiska:

Pozitivních výskytů

Typ/Subtyp:

ctInteger/0

ckRange:

EXISTS 
(
SELECT 1
FROM 
(  SELECT DISTINCT
         FP2.GENESIS_ID,
   	 SD2.STORE_ID, 
   	 SD.DOCDATE$DATE AS DateFrom,
   	 COALESCE(SDA.DOCDATE$DATE, 100000) AS DateTo
   	 FROM PLMFINISHEDPRODUCTS2 FP2
     LEFT JOIN PLMGETCHILDJOBORDERSSN(FP2.JOBORDERSSN_ID) T ON 1=1
     JOIN PLMJOBORDERSSN SN ON SN.ID = T.ID
     JOIN DOCROWBATCHES DRB ON DRB.STOREBATCH_ID = SN.STOREBATCH_ID
     JOIN STOREDOCUMENTS2 SD2 ON SD2.ID = DRB.PARENT_ID 
     JOIN STOREDOCUMENTS SD ON SD.DOCUMENTTYPE IN ('28', '24') AND SD.ID = SD2.PARENT_ID
     LEFT JOIN DOCROWBATCHES DRBA ON DRBA.STOREBATCH_ID = SN.STOREBATCH_ID AND DRBA.ID <> DRB.ID
     LEFT JOIN STOREDOCUMENTS2 SD2A ON SD2A.ID = DRBA.PARENT_ID AND SD2A.FLOWTYPE IN ('22')  
     LEFT JOIN STOREDOCUMENTS SDA ON SDA.ID = SD2A.PARENT_ID AND SDA."DOCDATE$DATE" > SD."DOCDATE$DATE"
     LEFT JOIN DOCROWBATCHES DRBB ON DRBB.STOREBATCH_ID = SN.STOREBATCH_ID AND DRBB.ID <> DRB.ID AND DRBB.ID <> DRBA.ID
     LEFT JOIN STOREDOCUMENTS2 SD2B ON SD2B.ID = DRBB.PARENT_ID 
     LEFT JOIN STOREDOCUMENTS SDB ON SDB.DOCUMENTTYPE IN ('22') AND SDB.ID = SD2B.PARENT_ID AND SDB."DOCDATE$DATE" < SDA."DOCDATE$DATE"  
   WHERE 
   	SDB.ID IS NULL    
) S
JOIN IOTDATA IOT ON IOT.OBJECTCLASS = 'SFMSB5W15BDL3CL100C4RHECN0' AND IOT.OBJECT_ID = S.Store_ID AND IOT.RECORDEDAT$DATE BETWEEN S.DateFrom AND S.DateTo 
AND IOT.VALUEDATATYPE = 5
WHERE S.GENESIS_ID = A.ID
GROUP BY S.Store_ID,S.DateFrom, S.DateTo, IOT.SENSORQUANTITY 
HAVING SUM(case when IOT.BOOLEANVALUE = 'A' then 1 else 0 end) BETWEEN {:LOW} and {:HIGH}
)

Dataset:

MAIN

Nadřízený:

Storage

NumberOfNoStore

Popiska:

Negativních výskytů

Typ/Subtyp:

ctInteger/0

ckRange:

EXISTS 
(
SELECT 1
FROM 
(  SELECT DISTINCT
         FP2.GENESIS_ID,
   	 SD2.STORE_ID, 
   	 SD.DOCDATE$DATE AS DateFrom,
   	 COALESCE(SDA.DOCDATE$DATE, 100000) AS DateTo
   	 FROM PLMFINISHEDPRODUCTS2 FP2
     LEFT JOIN PLMGETCHILDJOBORDERSSN(FP2.JOBORDERSSN_ID) T ON 1=1
     JOIN PLMJOBORDERSSN SN ON SN.ID = T.ID
     JOIN DOCROWBATCHES DRB ON DRB.STOREBATCH_ID = SN.STOREBATCH_ID
     JOIN STOREDOCUMENTS2 SD2 ON SD2.ID = DRB.PARENT_ID 
     JOIN STOREDOCUMENTS SD ON SD.DOCUMENTTYPE IN ('28', '24') AND SD.ID = SD2.PARENT_ID
     LEFT JOIN DOCROWBATCHES DRBA ON DRBA.STOREBATCH_ID = SN.STOREBATCH_ID AND DRBA.ID <> DRB.ID
     LEFT JOIN STOREDOCUMENTS2 SD2A ON SD2A.ID = DRBA.PARENT_ID AND SD2A.FLOWTYPE IN ('22')  
     LEFT JOIN STOREDOCUMENTS SDA ON SDA.ID = SD2A.PARENT_ID AND SDA."DOCDATE$DATE" > SD."DOCDATE$DATE"
     LEFT JOIN DOCROWBATCHES DRBB ON DRBB.STOREBATCH_ID = SN.STOREBATCH_ID AND DRBB.ID <> DRB.ID AND DRBB.ID <> DRBA.ID
     LEFT JOIN STOREDOCUMENTS2 SD2B ON SD2B.ID = DRBB.PARENT_ID 
     LEFT JOIN STOREDOCUMENTS SDB ON SDB.DOCUMENTTYPE IN ('22') AND SDB.ID = SD2B.PARENT_ID AND SDB."DOCDATE$DATE" < SDA."DOCDATE$DATE"  
   WHERE 
   	SDB.ID IS NULL    
) S
JOIN IOTDATA IOT ON IOT.OBJECTCLASS = 'SFMSB5W15BDL3CL100C4RHECN0' AND IOT.OBJECT_ID = S.Store_ID AND IOT.RECORDEDAT$DATE BETWEEN S.DateFrom AND S.DateTo 
AND IOT.VALUEDATATYPE = 5
WHERE S.GENESIS_ID = A.ID
GROUP BY S.Store_ID,S.DateFrom, S.DateTo, IOT.SENSORQUANTITY 
HAVING SUM(case when IOT.BOOLEANVALUE = 'N' then 1 else 0 end) BETWEEN {:LOW} and {:HIGH}
)

Dataset:

MAIN

Nadřízený:

Storage

Klíče

StoreCardCode

Popiska:

Kód skl. karty

SQL:

SC.Code

Dataset:

MAIN

StoreCardName

Popiska:

Název skl. karty

SQL:

SC.Name

Dataset:

MAIN

ProductionDate

Popiska:

Datum výroby

SQL:

(SELECT MAX(PRODUCTIONDATE$DATE) FROM PLMFINISHEDPRODUCTS2 WHERE GENESIS_ID = A.ID) 

Dataset:

MAIN

JobOrderDisplayName

Popiska:

Výrobní příkaz

SQL:

Que.Code Collate PXW_CSY {DIR}, 

Per.Code Collate PXW_CSY {DIR},

JO.OrdNumber

Dataset:

MAIN

Quantity

Popiska:

Množství

SQL:

(SELECT SUM(QUANTITY) FROM PLMFINISHEDPRODUCTS2 WHERE GENESIS_ID = A.ID) 

Dataset:

MAIN

DisplayName

Popiska:

Název rodného listu

SQL:

SB.Name {DIR}, 

Que.Code Collate PXW_CSY {DIR}, 

Per.Code Collate PXW_CSY {DIR},

JO.OrdNumber {DIR},

A.ID

Dataset:

MAIN

Generated by ABRA Software a.s. 27.10.2021 16:36