begin
/* Výdejky materiálu do výroby */
FOR
SELECT sd.documenttype, sd.docqueue_id, sd.ordnumber, sd.period_id, sd.docdate$date, a.productiontask_id, LocalTAmount
FROM Storedocuments2 A
left join Storedocuments SD ON SD.id = A.Parent_ID
WHERE
SD.DocumentType = '27' and (SD.DocDate$DATE >= :datefrom and SD.DocDate$DATE < :dateto)
into :doctype, :docqueue_ID, :ordnumber, :period_id, :docdate, :productiontask_ID, :LOCALAMOUNTOUT
DO suspend;
/* Příjemky výrobků a Vratky materiálu z výroby */
For
SELECT sd.documenttype, sd.docqueue_id, sd.ordnumber, sd.period_id, sd.docdate$date, a.productiontask_id,
LocalTAmount * -1
FROM Storedocuments2 A
left join Storedocuments SD ON SD.id = A.Parent_ID
WHERE
SD.DocumentType in ('28','29') and (SD.DocDate$DATE >= :datefrom and SD.DocDate$DATE < :dateto)
into :doctype, :docqueue_ID, :ordnumber, :period_id, :docdate, :productiontask_ID, :LOCALAMOUNTOUT
DO suspend;
/* Pracovní lístky */
For
SELECT qu.documenttype, aw.docqueue_id, aw.ordnumber, aw.period_id, a.FinishedAt$Date, JO.productiontask_id, sum(A.SalaryCosts + A.OverheadCosts + A.GeneralExpense) as LocalAmount
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 JON on MI.Owner_ID = JON.ID
left join PLMJobOrders JO on JO.ID = JON.Parent_ID
left join PLMAggregateWorkTickets AW ON AW.ID = a.AggregateWorkTicket_ID
left join docqueues QU ON QU.id = aw.docqueue_id
WHERE A.FinishedAt$Date is not null and A.FinishedAt$Date > 0 and (A.FinishedAt$Date >= :datefrom and A.FinishedAt$Date < :dateto)
group by qu.documenttype,aw.docqueue_id, aw.ordnumber, aw.period_id, a.FinishedAt$Date, JO.productiontask_id
into :doctype, :docqueue_ID, :ordnumber, :period_id, :docdate, :productiontask_ID, :LOCALAMOUNTOUT
DO suspend;
/* Kooperace */
For
SELECT qu.documenttype, a.docqueue_id, a.ordnumber, a.period_id,
case when a.ReturnedAt$DATE > 0 then a.ReturnedAt$DATE else a.docdate$date end,
JO.productiontask_id, A.RealMaterialPrice + A.RealWorkPrice as LocalAmount
FROM plmcooperations A
left join plmjoborders JO on JO.ID = A.JobOrder_ID
left join docqueues QU ON QU.id = a.docqueue_id
where (ReturnedAt$DATE = 0 and (A.DocDate$DATE >= :datefrom and A.DocDate$DATE < :dateto))
or (ReturnedAt$DATE > 0 and (ReturnedAt$DATE >= :datefrom and ReturnedAt$DATE < :dateto))
into :doctype, :docqueue_ID, :ordnumber, :period_id, :docdate, :productiontask_ID, :LOCALAMOUNTOUT
DO suspend;
/* Dokončené výrobky */
For
SELECT qu.documenttype, a.docqueue_id, a.ordnumber, a.period_id,
a.docdate$date,
JO.productiontask_id, A.Amount as LocalAmount
FROM plmfinishedproducts A
left join plmjoborders JO on JO.ID = A.JobOrder_ID
left join docqueues QU ON QU.id = a.docqueue_id
where A.DocDate$DATE is not null and A.DocDate$DATE > 0 and (A.DocDate$DATE >= :datefrom and A.DocDate$DATE < :dateto)
into :doctype, :docqueue_ID, :ordnumber, :period_id, :docdate, :productiontask_ID, :LOCALAMOUNTOUT
DO suspend;
/* Vyrovnání nedokončené výroby */
FOR
SELECT qu.documenttype, a.docqueue_id, a.ordnumber, a.period_id, a.DocDate$DATE, JO.productiontask_id, -A.Amount as LocalAmount
FROM plmbalanceinprocessproduces A
left join plmjoborders JO on JO.ID = A.joborder_id
left join docqueues QU ON QU.id = a.docqueue_id
WHERE A.DocDate$DATE is not null and A.DocDate$DATE > 0 and (A.DocDate$DATE >= :datefrom and A.DocDate$DATE < :dateto)
into :doctype, :docqueue_ID, :ordnumber, :period_id, :docdate, :productiontask_ID, :LOCALAMOUNTOUT
Do Suspend;
End;