Dynamický dotaz - Výroba - Nedokončená výroba, stav k datu (se SPL)

ID:

VWLTVV0PQ0IO1F1BZH2DKLNMMG

Datasety

MAIN

Popis:

Nedokončená výroba

SQL definice:

select max(dq.code) as DQ_Code, max(p.code) as P_Code, max(JO.OrdNumber) as OrdNumber
    {ANDFIELDS} from
     PLMFLOWBYDATEAWT({$DateFrom},{$DateTo}) A
    left join PLMJobOrders JO ON JO.productiontask_ID = A.productiontask_ID
    LEFT JOIN DocQueues DQ ON DQ.ID=JO.DocQueue_ID
    LEFT JOIN Periods P ON P.ID=JO.Period_ID
{WHERE}
group by a.productiontask_id
having sum(A.LOCALAMOUNTOUT) <> 0
{ORDERBY}

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
LocalAmount	dtFloat	13	sum(A.LOCALAMOUNTOUT)		Hodnota nedokončené výroby
OrdNumber	dtInteger	10			Číslo dokladu
productiontask_id	dtString	10	a.productiontask_id		productiontask_id

JOBORDERS

Popis:

Výrobní příkazy

SQL definice:

SELECT {FIELDS} FROM  PLMJobOrders A
WHERE
  A.ProductionTask_ID = :ProductionTask_ID

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	HTI3OTLGNRPO32EEISEPC0XZ0K

STOREDOC2_27

Popis:

Výdej materiálu do výroby

SQL definice:

SELECT {FIELDS}
FROM Storedocuments2 A
     left join Storedocuments SD ON SD.id = A.Parent_ID
WHERE
   A.ProductionTask_ID = :ProductionTask_ID and SD.DocumentType = '27' and
   (SD.DocDate$DATE >= {$DateFrom} and SD.DocDate$DATE < {$DateTo})
order by SD.DocDate$DATE

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	YMPR5ATH032O1H3R4VWVLDLY4S

STOREDOC2_29

Popis:

Vrácení materiálu z výroby

SQL definice:

SELECT {FIELDS}
FROM Storedocuments2 A
     left join Storedocuments SD ON SD.id = A.Parent_ID
WHERE
   A.ProductionTask_ID = :ProductionTask_ID and SD.DocumentType = '29' and
   (SD.DocDate$DATE >= {$DateFrom} and SD.DocDate$DATE < {$DateTo})
order by SD.DocDate$DATE

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	EOL2QZRMNU5ONEYHJAZ2D3BGNC

OPERATIONS

Popis:

Pracovní lístky

SQL definice:

SELECT {FIELDS}
FROM PLMOperations A
LEFT JOIN PLMJobOrdersRoutines RO ON RO.ID = A.JobOrdersRoutines_ID
LEFT JOIN PLMJOOutputItems MI ON MI.ID = RO.Parent_ID
LEFT JOIN PLMJONodes N ON N.ID = MI.Owner_ID
WHERE 
  N.Parent_ID = :ID and 
  A.AggregateWorkTicket_ID is null and
  A.FinishedAt$Date is not null and A.FinishedAt$Date > 0 and
  (A.FinishedAt$Date >= {$DateFrom} and A.FinishedAt$Date < {$DateTo})
GROUP BY A.FinishedAt$Date

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
FinishedAt_Date	dtDate	15	A.FinishedAt$Date		Datum dokončení
LocalAmount	dtFloat	13	sum(A.SalaryCosts + A.OverheadCosts + A.GeneralExpense)		Částka NV

AGGREGATEWT

Popis:

Souhrnné pracovní lístky

SQL definice:

SELECT {FIELDS} FROM PLMAggregateWorkTickets A
WHERE A.JobOrder_ID = :ID and (A.DocDate$DATE >= {$DateFrom} and A.DocDate$DATE < {$DateTo})

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	H0NLJLKS5SMOVIMJFM5FVSKNPS

COOPERATIONS

Popis:

Kooperace

SQL definice:

SELECT {FIELDS} FROM PLMCooperations A
WHERE A.JobOrder_ID = :ID and (((ReturnedAt$DATE is null or ReturnedAt$DATE = 0) and
      (A.DocDate$DATE >= {$DateFrom} and A.DocDate$DATE < {$DateTo})) or
      ((ReturnedAt$DATE is not null or ReturnedAt$DATE > 0) and
      (ReturnedAt$DATE >= {$DateFrom} and ReturnedAt$DATE < {$DateTo})))

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	ZNJNWH3UCWTOPHPQZM2UZTEOG4

STOREDOC2_28

Popis:

Příjem hotových výrobků

SQL definice:

SELECT {FIELDS}
FROM Storedocuments2 A
     left join Storedocuments SD ON SD.id = A.Parent_ID
WHERE
   A.ProductionTask_ID = :ProductionTask_ID and SD.DocumentType = '28' and
   (SD.DocDate$DATE >= {$DateFrom} and SD.DocDate$DATE < {$DateTo})
order by SD.DocDate$DATE

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	GJU2P1XJKZF41IBONBO5OUXCX4

BALANCEIPP

Popis:

Vyrovnání nedokončené výroby

SQL definice:

SELECT {FIELDS} FROM plmbalanceinprocessproduces A
WHERE A.JobOrder_ID = :ID and (A.DocDate$DATE >= {$DateFrom} and A.DocDate$DATE < {$DateTo})

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	BJPYQBANGYCOBAZL20Q40EWIBS

FINPRODUCT

Popis:

Dokončené výrobky

SQL definice:

SELECT {FIELDS} FROM plmfinishedproducts A
WHERE A.JobOrder_ID = :ID and (A.DocDate$DATE >= {$DateFrom} and A.DocDate$DATE < {$DateTo})

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	VXZNUMLJMCTOPC12DQELHBUYFO

JOBORDERSAMOUNTS

Popis:

Výrobní příkazy s částkami

SQL definice:

SELECT   
(SELECT SUM(CASE WHEN (VMV.DocumentType = '27') THEN LocalTAmount ELSE -LocalTAmount END)
   FROM Storedocuments2 VMV2
     INNER JOIN Storedocuments VMV ON (VMV.id = VMV2.Parent_ID  AND (VMV.DocumentType = '27' or VMV.DocumentType = '29'))
   WHERE VMV2.ProductionTask_ID = A.ProductionTask_ID
     AND VMV.DocDate$DATE >= {$DateFrom} and VMV.DocDate$DATE < {$DateTo}
) AS MaterialAmount,
(SELECT MIN(VMV2.closingorder)
   FROM Storedocuments2 VMV2
     INNER JOIN Storedocuments VMV ON (VMV.id = VMV2.Parent_ID  AND (VMV.DocumentType = '27' or VMV.DocumentType = '29'))
   WHERE VMV2.ProductionTask_ID = A.ProductionTask_ID
     AND VMV.DocDate$DATE >= {$DateFrom} and VMV.DocDate$DATE < {$DateTo}
) AS MaterialCompletePrices,
(SELECT SUM(FP2.MaterialExpenseAmount + FP2.ConsumablesAmount + FP2.PriceAmount)
   FROM  plmfinishedproducts FP
    inner join plmfinishedproducts2 FP2 ON (FP2.Parent_ID = FP.ID)
   WHERE (FP.JobOrder_ID = A.ID)
     AND FP.DocDate$DATE >= {$DateFrom} and FP.DocDate$DATE < {$DateTo}
) AS FPAmount,
(SELECT SUM(FP2.materialexpenseamount)
   FROM  plmfinishedproducts FP
    inner join plmfinishedproducts2 FP2 ON (FP2.Parent_ID = FP.ID)
   WHERE (FP.JobOrder_ID = A.ID)
     AND FP.DocDate$DATE >= {$DateFrom} and FP.DocDate$DATE < {$DateTo}
) AS FPMaterialExpense,
(SELECT SUM(FP2.consumablesamount)
   FROM  plmfinishedproducts FP
    inner join plmfinishedproducts2 FP2 ON (FP2.Parent_ID = FP.ID)
   WHERE (FP.JobOrder_ID = A.ID)
     AND FP.DocDate$DATE >= {$DateFrom} and FP.DocDate$DATE < {$DateTo}
) AS FPConsumables,
(SELECT SUM(FP2.priceamount)
   FROM  plmfinishedproducts FP
    inner join plmfinishedproducts2 FP2 ON (FP2.Parent_ID = FP.ID)
   WHERE (FP.JobOrder_ID = A.ID)
     AND FP.DocDate$DATE >= {$DateFrom} and FP.DocDate$DATE < {$DateTo}
) AS FPPrice,
(SELECT SUM(OP.SalaryCosts)
   FROM PLMJONodes N 
    inner join PLMJOOutputItems MI on (N.ID = MI.Owner_ID)
    inner join PLMJobOrdersRoutines RO on (MI.id = RO.parent_ID)
      INNER JOIN PLMOperations OP on (RO.ID = OP.JobOrdersRoutines_ID)
   WHERE (N.Parent_ID = A.ID)
     AND OP.FinishedAt$Date >= {$DateFrom} and OP.FinishedAt$Date < {$DateTo}
) AS OPPrice,
(SELECT SUM(OP.OverheadCosts)
   FROM PLMJONodes N 
    inner join PLMJOOutputItems MI on (N.ID = MI.Owner_ID)
    inner join PLMJobOrdersRoutines RO on (MI.id = RO.parent_ID)
      INNER JOIN PLMOperations OP on (RO.ID = OP.JobOrdersRoutines_ID)
   WHERE (N.Parent_ID = A.ID)
     AND OP.FinishedAt$Date >= {$DateFrom} and OP.FinishedAt$Date < {$DateTo}
) AS OPOverheadCosts,
(SELECT SUM(OP.GeneralExpense)
   FROM PLMJONodes N 
    inner join PLMJOOutputItems MI on (N.ID = MI.Owner_ID)
    inner join PLMJobOrdersRoutines RO on (MI.id = RO.parent_ID)
      INNER JOIN PLMOperations OP on (RO.ID = OP.JobOrdersRoutines_ID)
   WHERE (N.Parent_ID = A.ID)
     AND OP.FinishedAt$Date >= {$DateFrom} and OP.FinishedAt$Date < {$DateTo}
) AS OPGeneralExpense,
(SELECT COUNT(OP.ID)
   FROM PLMJONodes N 
    inner join PLMJOOutputItems MI on (N.ID = MI.Owner_ID)
    inner join PLMJobOrdersRoutines RO on (MI.id = RO.parent_ID)
      INNER JOIN PLMOperations OP on (RO.ID = OP.JobOrdersRoutines_ID)
   WHERE (N.Parent_ID = A.ID)
     AND OP.FinishedAt$Date >= {$DateFrom} and OP.FinishedAt$Date < {$DateTo}
     AND OP.aggregateworkticket_id is null
) AS OPCountNotAWT,
(SELECT SUM(AWT.amount)
   FROM PLMAggregateWorkTickets AWT
   WHERE (AWT.JobOrder_ID = A.ID)
     AND AWT.DocDate$DATE >= {$DateFrom} and AWT.DocDate$DATE < {$DateTo}
) AS AWTAmount,
(SELECT SUM(BIP.amount)
   FROM PLMBalanceinProcessProduces BIP
   WHERE (BIP.JobOrder_ID = A.ID)
     AND BIP.DocDate$DATE >= {$DateFrom} and BIP.DocDate$DATE < {$DateTo}
) AS BIPAmount,
(SELECT SUM(COO.RealMaterialPrice)+SUM(COO.RealWorkPrice)
   FROM PLMCooperations COO
   WHERE (COO.JobOrder_ID = A.ID)
     AND COO.DocDate$DATE >= {$DateFrom} and COO.DocDate$DATE < {$DateTo}
) AS COOAmount,
(SELECT SUM(PHV2.LocalTAmount)
   FROM Storedocuments2 PHV2
     INNER JOIN Storedocuments PHV ON (PHV.id = PHV2.Parent_ID  AND PHV.DocumentType = '28')
   WHERE PHV2.ProductionTask_ID = A.ProductionTask_ID
     AND PHV.DocDate$DATE >= {$DateFrom} and PHV.DocDate$DATE < {$DateTo}
) AS ProductReceptionsAmount,
(SELECT MIN(PHV2.closingorder)
   FROM Storedocuments2 PHV2
     INNER JOIN Storedocuments PHV ON (PHV.id = PHV2.Parent_ID  AND PHV.DocumentType = '28')
   WHERE PHV2.ProductionTask_ID = A.ProductionTask_ID
     AND PHV.DocDate$DATE >= {$DateFrom} and PHV.DocDate$DATE < {$DateTo}
) AS ProductReceptionsClosing
{ANDFIELDS}
 FROM
  PLMJobOrders A
WHERE
  A.ProductionTask_ID = :ProductionTask_ID

Aliasy:

Alias	CLSID objektu	Prefix	Popis
A	HTI3OTLGNRPO32EEISEPC0XZ0K

Fields:

Jméno	Typ	Velikost	Výraz	CLSID	Popis
AWTAmount	dtFloat	10			Cena souhrnných prac. lístků
BIPAmount	dtFloat	10			Cena vyrovnání nedokončené výroby
COOAmount	dtFloat	10			Cena kooperace
FPAmount	dtFloat	10			Cena dokončených výrobků
FPConsumables	dtFloat	10			Spotřební materiál
FPMaterialExpense	dtFloat	10			Materiálová režie
FPPrice	dtFloat	10			Pevná cena
MaterialAmount	dtFloat	10			Cena materiálu
MaterialCompletePrices	dtInteger	10			Materiál je oceněn
OPCountNotAWT	dtInteger	10			Prac. lístků bez souhrnných
OPGeneralExpense	dtFloat	10			Správní režie
OPOverheadCosts	dtFloat	10			Výrobní režie
OPPrice	dtFloat	10			Cena prac. lístků
ProductReceptionsAmount	dtFloat	10			Cena příjmu hotových výrobků
ProductReceptionsClosing	dtInteger	10			Příjmy hotových výrobků jsou oceněny

Podmínky

Date

Popiska:

Datum

Typ/Subtyp:

ctDateTime/0

ckRange:

{:LOW}{:HIGH}

Dataset:

MAIN

Speciální parametry:

AlwaysUsed=True
ReplacementLink=True
{$DateFrom}=VALUE;0
{$DateTo}=VALUEHIGH;0

StoreCard_ID

Šablona:

StoreCard_ID

Dataset:

MAIN

Speciální parametry:

IsProduct=True

DocQueue_ID

Šablona:

DocQueue_ID

Dataset:

MAIN

Speciální parametry:

FilterDocumentType=JO

OrdNumber

Šablona:

OrdNumber

Dataset:

MAIN

Period_ID

Šablona:

Period_ID

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

Division_ID

Šablona:

Division_ID

Dataset:

MAIN

BusOrder_ID

Šablona:

BusOrder_ID

Dataset:

MAIN

BusTransaction_ID

Šablona:

BusTransaction_ID

Dataset:

MAIN

BusProject_ID

Šablona:

BusProject_ID

Dataset:

MAIN

Klíče

DisplayName

Popiska:

Číslo výrobního příkazu (složené)

SQL:

1 {DIR},2 {DIR},3

Dataset:

MAIN

LocalAmount

Popiska:

Hodnota NV

SQL:

4

Dataset:

MAIN

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