begin
/* Vydejky materialu do vyroby */
for
SELECT
a.productiontask_id as productiontask_id,
a.LocalTAmount as LocalAmount,
a.Division_ID as Division_ID,
a.busorder_id as busorder_id,
a.bustransaction_id as bustransaction_id,
a.busproject_id as busproject_id,
A.storecard_id as INPUTSTORECARD_ID,
M.storecard_id as OUTPUTSTORECARD_ID,
'' as WORKER_ID,
'' as WORKPLACE_ID,
Case When (select count(ID) from Relations where rel_def = 39 and leftside_id = SD.ID) = 0 then 0 else 1 end as ACC_RECORD,
SD.Firm_ID as Firm_ID,
QS.Code || '-' || SD.OrdNumber || '/' || PS.Code as Source_DOCDN,
JO.MaterialInIncome as MaterialInIncome,
0 as Acc_Date,
SD.DocDate$DATE as Doc_date
FROM Storedocuments2 A
join Storedocuments SD ON SD.id = A.Parent_ID
join PLMMIPLMaterialDistrib MD On MD.StoreDocument2_ID = A.ID
join PLMJOInputItems it On IT.id = MD.Parent_ID
join PLMJONodes N On N.ID = IT.owner_id
join PLMJONodes m On M.ID = N.master_id
join plmjoborders JO On JO.ID = M.Parent_ID
join Periods PS On PS.ID = SD.Period_ID
join DocQueues QS On QS.ID = SD.DocQueue_ID
WHERE
(SD.DocumentType = '27') and (SD.DocDate$DATE >= :ADateFrom and SD.DocDate$DATE < :ADateTo) and (JO.Balanced = 'N')
INTO
Out_ProductionTask_ID,
Out_LocalAmount,
Out_Division_ID,
Out_BusOrder_ID,
Out_BusTransaction_ID,
Out_BusProject_ID,
Out_InputStoreCard_ID,
Out_OutputStoreCard_ID,
Out_Worker_ID,
Out_WorkPlace_ID,
Out_Acc_Record,
Out_Firm_ID,
Out_Source_DocDN,
mInIncome,
Out_Acc_Date,
Out_Doc_Date
do
begin
Out_ValueType = 'VMV';
if ((mInIncome = 'A') Or (AAcceptJOSetting = 0)) then
begin
Suspend;
end
end
/* Vratky materialu do vyroby */
for
SELECT
a.productiontask_id as productiontask_id,
a.LocalTAmount * -1 as LocalAmount,
a.Division_ID as Division_ID,
a.busorder_id as busorder_id,
a.bustransaction_id as bustransaction_id,
a.busproject_id as busproject_id,
A.storecard_id as INPUTSTORECARD_ID,
M.storecard_id as OUTPUTSTORECARD_ID,
'' as WORKER_ID,
'' as WORKPLACE_ID,
Case When (select count(ID) from Relations where rel_def = 40 and leftside_id = SD.ID) = 0 then 0 else 1 end as ACC_RECORD,
SD.Firm_ID as Firm_ID,
QS.Code || '-' || SD.OrdNumber || '/' || PS.Code as Source_DOCDN,
JO.MaterialInIncome as MaterialInIncome,
0 as Acc_Date,
SD.DocDate$DATE as Doc_date
FROM Storedocuments2 A
join Storedocuments SD ON SD.id = A.Parent_ID
join PLMMIPLMaterialDistrib MD On MD.StoreDocument2_ID = A.ID
join PLMJOInputItems it On IT.id = MD.Parent_ID
join PLMJONodes N On N.ID = IT.owner_id
join PLMJONodes m On M.ID = N.master_id
join plmjoborders JO On JO.ID = M.Parent_ID
join Periods PS On PS.ID = SD.Period_ID
join DocQueues QS On QS.ID = SD.DocQueue_ID
WHERE
(SD.DocumentType = '29') and (SD.DocDate$DATE >= :ADateFrom and SD.DocDate$DATE < :ADateTo) and (JO.Balanced = 'N')
Into
Out_ProductionTask_ID,
Out_LocalAmount,
Out_Division_ID,
Out_BusOrder_ID,
Out_BusTransaction_ID,
Out_BusProject_ID,
Out_InputStoreCard_ID,
Out_OutputStoreCard_ID,
Out_Worker_ID,
Out_WorkPlace_ID,
Out_Acc_Record,
Out_Firm_ID,
Out_Source_DocDN,
mInIncome,
Out_Acc_Date,
Out_Doc_Date
do
begin
Out_ValueType = 'VRM';
If ((mInIncome = 'A') Or (AAcceptJOSetting = 0)) then
begin
Suspend;
end
end
/* Pracovni listky - spravni rezie */
for
SELECT
JO.productiontask_id as productiontask_id,
A.GeneralExpense as LocalAmount,
a.Division_ID as Division_ID,
a.busorder_id as busorder_id,
a.bustransaction_id as bustransaction_id,
a.busproject_id as busproject_id,
'' as INPUTSTORECARD_ID,
JON.storecard_id as OUTPUTSTORECARD_ID,
a.PerformedBy_ID as WORKER_ID,
a.WorkPlace_ID as WORKPLACE_ID,
Case When (select count(ID) from Relations where rel_def = 44 and leftside_id = AWT.ID) = 0 then 0 else 1 end as ACC_RECORD,
JO.Firm_ID as Firm_ID,
QS.Code || '-' || AWT.OrdNumber || '/' || PS.Code as Source_DOCDN,
JO.GeneralExpenseInIncome as GeneralExpenseInIncome,
0 as Acc_Date,
A.FinishedAt$Date as Doc_date
FROM PLMOperations A
join PLMJobOrdersRoutines RO on RO.ID = A.JobOrdersRoutines_ID
join PLMJOOutputItems MI on MI.ID = RO.parent_ID
join PLMJONodes JON on MI.Owner_ID = JON.ID
join plmjoborders JO on JO.ID = JON.Parent_ID
left join PLMAggregateWorkTickets AWT On AWT.ID = A.AggregateWorkTicket_ID
left join Periods PS On PS.ID = AWT.Period_ID
left join DocQueues QS On QS.ID = AWT.DocQueue_ID
WHERE
(A.FinishedAt$Date is not null) and A.FinishedAt$Date > 0 and (A.FinishedAt$Date >= :ADateFrom and A.FinishedAt$Date < :ADateTo) and (JO.Balanced = 'N')
Into
Out_ProductionTask_ID,
Out_LocalAmount,
Out_Division_ID,
Out_BusOrder_ID,
Out_BusTransaction_ID,
Out_BusProject_ID,
Out_InputStoreCard_ID,
Out_OutputStoreCard_ID,
Out_Worker_ID,
Out_WorkPlace_ID,
Out_Acc_Record,
Out_Firm_ID,
Out_Source_DocDN,
mInIncome,
Out_Acc_Date,
Out_Doc_Date
do
begin
Out_ValueType = 'SPR';
If ((mInIncome = 'A') Or (AAcceptJOSetting = 0)) then
begin
Suspend;
end
end
/* Pracovni listky - vyrobni rezie */
for
SELECT
JO.productiontask_id as productiontask_id,
A.OverheadCosts as LocalAmount,
a.Division_ID as Division_ID,
a.busorder_id as busorder_id,
a.bustransaction_id as bustransaction_id,
a.busproject_id as busproject_id,
'' as INPUTSTORECARD_ID,
JON.storecard_id as OUTPUTSTORECARD_ID,
a.PerformedBy_ID as WORKER_ID,
a.WorkPlace_ID as WORKPLACE_ID,
Case When (select count(ID) from Relations where rel_def = 44 and leftside_id = AWT.ID) = 0 then 0 else 1 end as ACC_RECORD,
JO.Firm_ID as Firm_ID,
QS.Code || '-' || AWT.OrdNumber || '/' || PS.Code as Source_DOCDN,
JO.OverheadCostsInIncome as OverheadCostsInIncome,
0 as Acc_Date,
A.FinishedAt$Date as Doc_date
FROM PLMOperations A
join PLMJobOrdersRoutines RO on RO.ID = A.JobOrdersRoutines_ID
join PLMJOOutputItems MI on MI.ID = RO.parent_ID
join PLMJONodes JON on MI.Owner_ID = JON.ID
join plmjoborders JO on JO.ID = JON.Parent_ID
left join PLMAggregateWorkTickets AWT On AWT.ID = A.AggregateWorkTicket_ID
left join Periods PS On PS.ID = AWT.Period_ID
left join DocQueues QS On QS.ID = AWT.DocQueue_ID
WHERE
(A.FinishedAt$Date is not null) and (A.FinishedAt$Date > 0) and (A.FinishedAt$Date >= :ADateFrom and A.FinishedAt$Date < :ADateTo) and (JO.Balanced = 'N')
Into
Out_ProductionTask_ID,
Out_LocalAmount,
Out_Division_ID,
Out_BusOrder_ID,
Out_BusTransaction_ID,
Out_BusProject_ID,
Out_InputStoreCard_ID,
Out_OutputStoreCard_ID,
Out_Worker_ID,
Out_WorkPlace_ID,
Out_Acc_Record,
Out_Firm_ID,
Out_Source_DocDN,
mInIncome,
Out_Acc_Date,
Out_Doc_Date
do
begin
Out_ValueType = 'VYR';
If ((mInIncome = 'A') Or (AAcceptJOSetting = 0)) then
begin
Suspend;
end
end
/* Pracovni listky - prace */
for
SELECT
JO.productiontask_id as productiontask_id,
A.SalaryCosts as LocalAmount,
a.Division_ID as Division_ID,
a.busorder_id as busorder_id,
a.bustransaction_id as bustransaction_id,
a.busproject_id as busproject_id,
'' as INPUTSTORECARD_ID,
JON.storecard_id as OUTPUTSTORECARD_ID,
a.PerformedBy_ID as WORKER_ID,
a.WorkPlace_ID as WORKPLACE_ID,
Case When (select count(ID) from Relations where rel_def = 44 and leftside_id = AWT.ID) = 0 then 0 else 1 end as ACC_RECORD,
JO.Firm_ID as Firm_ID,
QS.Code || '-' || AWT.OrdNumber || '/' || PS.Code as Source_DOCDN,
JO.SalariesInIncome as SalariesInIncome,
0 as Acc_Date,
A.FinishedAt$Date as Doc_date
FROM PLMOperations A
join PLMJobOrdersRoutines RO on RO.ID = A.JobOrdersRoutines_ID
join PLMJOOutputItems MI on MI.ID = RO.parent_ID
join PLMJONodes JON on MI.Owner_ID = JON.ID
join plmjoborders JO on JO.ID = JON.Parent_ID
left join PLMAggregateWorkTickets AWT On AWT.ID = A.AggregateWorkTicket_ID
left join Periods PS On PS.ID = AWT.Period_ID
left join DocQueues QS On QS.ID = AWT.DocQueue_ID
WHERE
(A.FinishedAt$Date is not null) and (A.FinishedAt$Date > 0) and (A.FinishedAt$Date >= :ADateFrom and A.FinishedAt$Date < :ADateTo) and (JO.Balanced = 'N')
Into
Out_ProductionTask_ID,
Out_LocalAmount,
Out_Division_ID,
Out_BusOrder_ID,
Out_BusTransaction_ID,
Out_BusProject_ID,
Out_InputStoreCard_ID,
Out_OutputStoreCard_ID,
Out_Worker_ID,
Out_WorkPlace_ID,
Out_Acc_Record,
Out_Firm_ID,
Out_Source_DocDN,
mInIncome,
Out_Acc_Date,
Out_Doc_Date
do
begin
Out_ValueType = 'PRL';
If ((mInIncome = 'A') Or (AAcceptJOSetting = 0)) then
begin
Suspend;
end
end
/* Kooperace */
for
SELECT
JO.productiontask_id as productiontask_id,
A.RealMaterialPrice + A.RealWorkPrice as LocalAmount,
a.Division_ID as Division_ID,
a.busorder_id as busorder_id,
a.bustransaction_id as bustransaction_id,
a.busproject_id as busproject_id,
'' as INPUTSTORECARD_ID,
'' as OUTPUTSTORECARD_ID,
'' as WORKER_ID,
'' as WORKPLACE_ID,
Case When (select count(ID) from Relations where rel_def = 45 and leftside_id = A.ID) = 0 then 0 else 1 end as ACC_RECORD,
JO.Firm_ID as Firm_ID,
QS.Code || '-' || A.OrdNumber || '/' || PS.Code as Source_DOCDN,
JO.CooperationInIncome as CooperationInIncome,
0 as Acc_Date,
A.DocDate$DATE as Doc_date
FROM plmcooperations A
join plmjoborders JO on JO.ID = A.JobOrder_ID
join Periods PS On PS.ID = A.Period_ID
join DocQueues QS On QS.ID = A.DocQueue_ID
where (JO.Balanced = 'N') and ((ReturnedAt$DATE = 0 and (A.DocDate$DATE >= :ADateFrom and A.DocDate$DATE < :ADateTo))
or (ReturnedAt$DATE > 0 and (ReturnedAt$DATE >= :ADateFrom and ReturnedAt$DATE < :ADateTo)))
Into
Out_ProductionTask_ID,
Out_LocalAmount,
Out_Division_ID,
Out_BusOrder_ID,
Out_BusTransaction_ID,
Out_BusProject_ID,
Out_InputStoreCard_ID,
Out_OutputStoreCard_ID,
Out_Worker_ID,
Out_WorkPlace_ID,
Out_Acc_Record,
Out_Firm_ID,
Out_Source_DocDN,
mInIncome,
Out_Acc_Date,
Out_Doc_Date
do
begin
Out_ValueType = 'KOO';
If ((mInIncome = 'A') Or (AAcceptJOSetting = 0)) then
begin
Suspend;
end
end
/* Dokoncene vyrobky - materialova rezie */
for
SELECT
JO.productiontask_id as productiontask_id,
b.MATERIALEXPENSEAMOUNT as LocalAmount,
JO.Division_ID as Division_ID,
JO.busorder_id as busorder_id,
JO.bustransaction_id as bustransaction_id,
JO.busproject_id as busproject_id,
'' as INPUTSTORECARD_ID,
JON.storecard_id as OUTPUTSTORECARD_ID,
'' as WORKER_ID,
'' as WORKPLACE_ID,
Case When (select count(ID) from Relations where rel_def = 55 and leftside_id = A.ID) = 0 then 0 else 1 end as ACC_RECORD,
JO.Firm_ID as Firm_ID,
QS.Code || '-' || A.OrdNumber || '/' || PS.Code as Source_DOCDN,
JO.MaterialExpenseInIncome as MaterialExpenseInIncome,
0 as Acc_Date,
A.DocDate$DATE as Doc_date
FROM PLMFinishedProducts2 B
join plmfinishedproducts A On A.ID = B.Parent_ID
join plmjoborders JO on JO.ID = A.JobOrder_ID
join plmJOOutputItems OI on OI.ID = B.JOOutputItem_ID
join PLMJONodes JON on OI.Owner_ID = JON.ID
join Periods PS On PS.ID = A.Period_ID
join DocQueues QS On QS.ID = A.DocQueue_ID
where (A.DocDate$DATE is not null) and (A.DocDate$DATE > 0) and (A.DocDate$DATE >= :ADateFrom and A.DocDate$DATE < :ADateTo) and (JO.Balanced = 'N')
Into
Out_ProductionTask_ID,
Out_LocalAmount,
Out_Division_ID,
Out_BusOrder_ID,
Out_BusTransaction_ID,
Out_BusProject_ID,
Out_InputStoreCard_ID,
Out_OutputStoreCard_ID,
Out_Worker_ID,
Out_WorkPlace_ID,
Out_Acc_Record,
Out_Firm_ID,
Out_Source_DocDN,
mInIncome,
Out_Acc_Date,
Out_Doc_Date
do
begin
Out_ValueType = 'MAR';
If ((mInIncome = 'A') Or (AAcceptJOSetting = 0)) then
begin
Suspend;
end
end
/* Dokoncene vyrobky - pevna cena */
for
SELECT
JO.productiontask_id as productiontask_id,
b.PRICEAMOUNT as LocalAMount,
JO.Division_ID as Division_ID,
JO.busorder_id as busorder_id,
JO.bustransaction_id as bustransaction_id,
JO.busproject_id as busproject_id,
'' as INPUTSTORECARD_ID,
JON.storecard_id as OUTPUTSTORECARD_ID,
'' as WORKER_ID,
'' as WORKPLACE_ID,
Case When (select count(ID) from Relations where rel_def = 55 and leftside_id = A.ID) = 0 then 0 else 1 end as ACC_RECORD,
JO.Firm_ID as Firm_ID,
QS.Code || '-' || A.OrdNumber || '/' || PS.Code as Source_DOCDN,
JO.PriceInIncome as PriceInIncome,
0 as Acc_Date,
A.DocDate$DATE as Doc_date
FROM PLMFinishedProducts2 B
join plmfinishedproducts A On A.ID = B.Parent_ID
join plmjoborders JO on JO.ID = A.JobOrder_ID
join plmJOOutputItems OI on OI.ID = B.JOOutputItem_ID
join PLMJONodes JON on OI.Owner_ID = JON.ID
join Periods PS On PS.ID = A.Period_ID
join DocQueues QS On QS.ID = A.DocQueue_ID
where (A.DocDate$DATE is not null) and (A.DocDate$DATE > 0) and (A.DocDate$DATE >= :ADateFrom and A.DocDate$DATE < :ADateTo) and (JO.Balanced = 'N')
Into
Out_ProductionTask_ID,
Out_LocalAmount,
Out_Division_ID,
Out_BusOrder_ID,
Out_BusTransaction_ID,
Out_BusProject_ID,
Out_InputStoreCard_ID,
Out_OutputStoreCard_ID,
Out_Worker_ID,
Out_WorkPlace_ID,
Out_Acc_Record,
Out_Firm_ID,
Out_Source_DocDN,
mInIncome,
Out_Acc_Date,
Out_Doc_Date
do
begin
Out_ValueType = 'CEN';
If ((mInIncome <> 1) Or (AAcceptJOSetting = 0)) then
begin
Suspend;
end
end
/* Dokoncene vyrobky - spotreba */
for
SELECT
JO.productiontask_id as productiontask_id,
b.CONSUMABLESAMOUNT as LocalAmount,
JO.Division_ID as Division_ID,
JO.busorder_id as busorder_id,
JO.bustransaction_id as bustransaction_id,
JO.busproject_id as busproject_id,
'' as INPUTSTORECARD_ID,
JON.storecard_id as OUTPUTSTORECARD_ID,
'' as WORKER_ID,
'' as WORKPLACE_ID,
Case When (select count(ID) from Relations where rel_def = 55 and leftside_id = A.ID) = 0 then 0 else 1 end as ACC_RECORD,
JO.Firm_ID as Firm_ID,
QS.Code || '-' || A.OrdNumber || '/' || PS.Code as Source_DOCDN,
JO.ConsumablesInIncome as ConsumablesInIncome,
0 as Acc_Date,
A.DocDate$DATE as Doc_date
FROM PLMFinishedProducts2 B
join plmfinishedproducts A On A.ID = B.Parent_ID
join plmjoborders JO on JO.ID = A.JobOrder_ID
join plmJOOutputItems OI on OI.ID = B.JOOutputItem_ID
join PLMJONodes JON on OI.Owner_ID = JON.ID
join Periods PS On PS.ID = A.Period_ID
join DocQueues QS On QS.ID = A.DocQueue_ID
where (A.DocDate$DATE is not null) and (A.DocDate$DATE > 0) and (A.DocDate$DATE >= :ADateFrom and A.DocDate$DATE < :ADateTo) and (JO.Balanced = 'N')
Into
Out_ProductionTask_ID,
Out_LocalAmount,
Out_Division_ID,
Out_BusOrder_ID,
Out_BusTransaction_ID,
Out_BusProject_ID,
Out_InputStoreCard_ID,
Out_OutputStoreCard_ID,
Out_Worker_ID,
Out_WorkPlace_ID,
Out_Acc_Record,
Out_Firm_ID,
Out_Source_DocDN,
mInIncome,
Out_Acc_Date,
Out_Doc_Date
do
begin
Out_ValueType = 'SPM';
If ((mInIncome > 0) Or (AAcceptJOSetting = 0)) then
begin
Suspend;
end
end
/* Vyrovnani nedokoncene vyroby */
for
SELECT
JO.productiontask_id as productiontask_id,
A.Amount as LocalAmount,
JO.Division_ID as Division_ID,
JO.busorder_id as busorder_id,
JO.bustransaction_id as bustransaction_id,
JO.busproject_id as busproject_id,
'' as INPUTSTORECARD_ID,
'' as OUTPUTSTORECARD_ID,
'' as WORKER_ID,
'' as WORKPLACE_ID,
Case When (select count(ID) from Relations where rel_def = 54 and leftside_id = A.ID) = 0 then 0 else 1 end as ACC_RECORD,
JO.Firm_ID as Firm_ID,
QS.Code || '-' || A.OrdNumber || '/' || PS.Code as Source_DOCDN,
0 as Acc_Date,
A.DocDate$DATE as Doc_date
FROM plmbalanceinprocessproduces A
join plmjoborders JO on JO.ID = A.joborder_id
join Periods PS On PS.ID = A.Period_ID
join DocQueues QS On QS.ID = A.DocQueue_ID
WHERE (A.DocDate$DATE is not null) and (A.DocDate$DATE > 0) and (A.DocDate$DATE >= :ADateFrom and A.DocDate$DATE < :ADateTo) and (JO.Balanced = 'N')
Into
Out_ProductionTask_ID,
Out_LocalAmount,
Out_Division_ID,
Out_BusOrder_ID,
Out_BusTransaction_ID,
Out_BusProject_ID,
Out_InputStoreCard_ID,
Out_OutputStoreCard_ID,
Out_Worker_ID,
Out_WorkPlace_ID,
Out_Acc_Record,
Out_Firm_ID,
Out_Source_DocDN,
Out_Acc_Date,
Out_Doc_Date
do
begin
Out_ValueType = 'VNV';
Suspend;
end
end